Intransit time conversion

CREATE OR REPLACE PACKAGE BODY APPS.xxcofi_time_conv_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_time_conv_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Chandra Sekhar */
/* */
/* DATE : 22-Apr-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will extract Intransit Time Conversion details*/
/* and generates flatfile */
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 22/04/10 Chandra 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
PROCEDURE main(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
)
AS
/*****************************************************************************************/
--- Procedure to create report outbound File
/*****************************************************************************************/
-----Variables Declaration-----
report_name VARCHAR2(70) := 'XXCOFI_INTRANSIT_TIME_CONVERSION_EXTRACT';
col01e_desc VARCHAR2(70) := 'From_Organization';
col02e_desc VARCHAR2(70) := 'To_Organization';
col03e_desc VARCHAR2(70) := 'Operation';
col04e_desc VARCHAR2(70) := 'Transit_Days';
v_file_dc UTL_FILE.file_type;
buff_size NUMBER := 24576;
l_record_no NUMBER := 0;
l_top VARCHAR2(200) := NULL;
l_date VARCHAR2(60) := NULL;
l_mode VARCHAR2(5) := NULL;
l_time_seq_num NUMBER;
l_extract_flag VARCHAR2(30);
l_update_count NUMBER := 0;

-- Cursor to Extract the intransit Time conversion Information
CURSOR cur_time_insert
IS
SELECT mism.ship_method f01
,mism.intransit_time f02
,mism.default_flag f03
,mism.from_organization_id f04
,ood.organization_name f05
,mism.to_organization_id f06
,ood1.organization_name f07
,mism.from_location_id f08
,mism.to_location_id f09
,mism.last_update_date f10
,mism.last_updated_by f11
,mism.creation_date f12
,mism.created_by f13
,mism.last_update_login f14
,mism.attribute_category f15
,mism.to_region_id f16
,mism.destination_type f17
,'CREATE' f18
FROM
MTL_INTERORG_SHIP_METHODS mism
,ORG_ORGANIZATION_DEFINITIONS ood
,ORG_ORGANIZATION_DEFINITIONS ood1
WHERE mism.from_organization_id=ood.organization_id
AND mism.to_organization_id=ood1.organization_id
AND NOT EXISTS (SELECT 'X'
FROM xxcofi_time_extract_tbl xxtime
WHERE mism.from_organization_id=xxtime.from_organization_id
AND mism.to_organization_id=xxtime.to_organization_id
AND mism.ship_method=xxtime.ship_method
AND mism.intransit_time=xxtime.intransit_time)
-- To exclude the records which alre already processed in staging table
;
--fnd_file.put_line (fnd_file.LOG,'Out Bound Extract Started');
CURSOR cur_record_time
IS
SELECT DISTINCT ship_method f01
,intransit_time f02
,default_flag f03
,from_organization_id f04
,from_organization_name f05
,to_organization_id f06
,to_organization_name f07
,from_location_id f08
,to_location_id f09
,to_date(last_update_date,'DD-MON-YYYY') f10
,last_updated_by f11
,creation_date f12
,created_by f13
,last_update_login f14
,attribute_category f15
,to_region_id f16
,destination_type f17
,'CREATE' f18
FROM xxcofi_time_extract_tbl
WHERE extract_flag = 'TBE' AND time_seq_num = 1
ORDER BY from_organization_id
,to_organization_id
;
BEGIN
FOR rec_cur_time_insert IN cur_time_insert
LOOP
BEGIN
l_time_seq_num := 1;
l_extract_flag := 'TBE';

----- Inserting Intransit Time Information into a staging table
INSERT INTO xxcofi_time_extract_tbl
(time_seq_num
,ship_method
,intransit_time
,default_flag
,from_organization_id
,from_organization_name
,to_organization_id
,to_organization_name
,from_location_id
,to_location_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,attribute_category
,to_region_id
,destination_type
,operation
,extract_flag
,extract_date

)
VALUES (l_time_seq_num
,rec_cur_time_insert.f01
,rec_cur_time_insert.f02
,rec_cur_time_insert.f03
,rec_cur_time_insert.f04
,rec_cur_time_insert.f05
,rec_cur_time_insert.f06
,rec_cur_time_insert.f07
,rec_cur_time_insert.f08
,rec_cur_time_insert.f09
,rec_cur_time_insert.f10
,rec_cur_time_insert.f11
,rec_cur_time_insert.f12
,rec_cur_time_insert.f13
,rec_cur_time_insert.f14
,rec_cur_time_insert.f15
,rec_cur_time_insert.f16
,rec_cur_time_insert.f17
,rec_cur_time_insert.f18
,l_extract_flag
,SYSDATE
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, SQLERRM || 'Error in Inserting the values into staging table for FROM_ORGANIZATION_ID:' || rec_cur_time_insert.f04);
END;
END LOOP;
fnd_profile.get('XXCOFIDATA_OUT', l_top); --outbound file top
l_date := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI');
fnd_file.put_line(fnd_file.LOG, 'START RUNNING ' || report_name || '_SS' || ' REPORT PROGRAM.');
fnd_file.put_line(fnd_file.output, RPAD('HUDSON''S BAY COMPANY', 50) || 'DATE:' || SYSDATE);
fnd_file.put_line(fnd_file.output, RPAD('Pricing Extract Report', 50) || 'TIME:' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
fnd_file.put_line(fnd_file.LOG, 'Outputing ' || report_name || '_' || l_date || 'TO ' || l_top);
v_file_dc := UTL_FILE.fopen(l_top, 'transit_time.dat', 'w', buff_size);
-- CREATE THE REPORT HEADER RECORD FOR REPORT OUTPUT FILE
-- PUT START MESSAGE IN THE LOG FILE
fnd_file.put_line(fnd_file.output
, col01e_desc
|| ','
|| col02e_desc
|| ','
|| col03e_desc
|| ','
|| col04e_desc
);
FOR rec_time IN cur_record_time
LOOP
l_record_no := l_record_no + 1;
UTL_FILE.put_line(v_file_dc
, RPAD(rec_time.f04, 4)
|| RPAD(rec_time.f06, 4)
|| RPAD(rec_time.f18, 6)
|| RPAD(rec_time.f02, 2)

);
fnd_file.put_line(fnd_file.output
, rec_time.f04
|| ','
|| rec_time.f06
|| ','
|| rec_time.f18
|| ','
|| rec_time.f02

);
BEGIN
l_update_count := l_update_count + 1;
UPDATE xxcofi_time_extract_tbl
SET extract_flag = 'E'
WHERE from_organization_id = rec_time.f04
AND to_organization_id=rec_time.f06
AND ship_method=rec_time.f01
AND intransit_time=rec_time.f02;
-- fnd_file.put_line(fnd_file.LOG, 'rowcount :' || sql%rowcount);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, 'update failed ORG ID :' || rec_time.f04);
END;
COMMIT;
--------------------------------------------------------------------------------
END LOOP;
fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
----- print corresponding information in the log file---------
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, '------------------------------------------------------------------');
fnd_file.put_line(fnd_file.LOG, 'COFI PRICE LIST OUTBOUND EXTRACT');
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, '**************************************************************************');
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, 'TOTAL NUMBER of RECORDS with flag as "TBE" initially : ' || l_record_no);
fnd_file.put_line(fnd_file.LOG, 'NUMBER of RECORDS extracted into OUTBOUND FILE : ' || l_record_no);
fnd_file.put_line(fnd_file.LOG, 'NUMBER of RECORDS updated with flag "E" after extracting : ' || l_update_count);
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, '**************************************************************************');
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
fnd_file.put_line(fnd_file.output, ' ');
--------------------------------------------------------------------------------
-- BEGINNING OF ERROR Pricing Extract Report
--------------------------------------------------------------------------------
-- CREATE THE TRAILER RECORD FOR OUTPUT REPORT FILE
UTL_FILE.fflush(v_file_dc);
UTL_FILE.fclose(v_file_dc);
--******************************************************************************
errbuf := report_name || ' REPORT FILE JOB COMPLETED SUCCESSFULLY';
retcode := '0';
-- Output Number Of Record Output
fnd_file.put_line(fnd_file.LOG, '------------------*********************************************----------------+');
fnd_file.put_line(fnd_file.LOG, 'Pricing Extract REPORT ');
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, 'JOB COMPLETED SUCCESSFULLY');
-- FND_FILE.CLOSE;
EXCEPTION
WHEN UTL_FILE.invalid_filehandle
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INVALID FILE HANDLE';
retcode := '1';
fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID FILE HANDLE : ' || SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_path
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INVALID PATH';
retcode := '2';
fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID PATH : ' || SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_mode
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INVALID MODE';
retcode := '3';
fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID MODE : ' || SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INVALID OPERATION';
retcode := '4';
fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID OPERATION : ' || SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.read_error
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'READ ERROR';
retcode := '5';
fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is READ ERROR : ' || SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.write_error
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'WRITE ERROR';
retcode := '6';
fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is WRITE ERROR : ' || SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.internal_error
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INTERNAL ERROR';
retcode := '7';
fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INTERNAL ERROR : ' || SQLERRM);
fnd_file.CLOSE;
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'NO DATA FOUND';
retcode := '8';
fnd_file.put_line(fnd_file.LOG, SQLCODE || ' Error is NO DATA FOUND :' || SQLERRM);
fnd_file.CLOSE;
WHEN OTHERS
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'OTHERS ';
retcode := '9';
fnd_file.put_line(fnd_file.LOG, SQLCODE || ' Error is OTHERS : ' || SQLERRM);
fnd_file.CLOSE;
END main;
END xxcofi_time_conv_pkg;
/