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';
col05e_desc VARCHAR2(70) := 'EffectiveFromDate';
col06e_desc VARCHAR2(70) := 'EffectiveToDate';
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.intransit_time = xxtime.intransit_time)
-- To exclude the records which alre already processed in staging table
UNION
SELECT DISTINCT NULL
, 1
, NULL
, TO_NUMBER(vendor_site_code) f05
, vendor_site_code f05
, organization_id F06
, substr(dest_org_code,1,4) f07
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'Create'
FROM xxcofi_sourcing_dc_to_vendor xxsource
WHERE NOT EXISTS(
SELECT 'X'
FROM xxcofi_time_extract_tbl xxtime
WHERE xxsource.vendor_site_code = xxtime.from_organization_name
AND xxsource.organization_id = xxtime.to_organization_id
AND 1 = xxtime.intransit_time);
-- To exclude the records which alre already processed in staging table
--cursor to get ship methods and respective intransit times----
CURSOR cur_record_time
IS
SELECT 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';
-- BEGIN
-- fnd_file.put_line(fnd_file.LOG , 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);
-- EXCEPTION
-- WHEN OTHERS THEN
-- fnd_file.put_line(fnd_file.LOG , 'error in getting base values');
-- END;
----- 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
,effective_from_date
,effective_to_date
)
VALUES (l_time_seq_num
, rec_cur_time_insert.f01 ---ship_method
, rec_cur_time_insert.f02 ---intransit_time
, rec_cur_time_insert.f03 ---default_flag
, rec_cur_time_insert.f04 ---from_organization_id
, rec_cur_time_insert.f05 ---from_organization_name
, rec_cur_time_insert.f06 ---to_organization_id
, rec_cur_time_insert.f07 ---to_organization_name
, rec_cur_time_insert.f08 ---from_location_id
, rec_cur_time_insert.f09 ---to_location_id
, rec_cur_time_insert.f10 ---last_update_date
, rec_cur_time_insert.f11 ---last_updated_by
, rec_cur_time_insert.f12 ---creation_date
, rec_cur_time_insert.f13 ---created_by
, rec_cur_time_insert.f14 ---last_update_login
, rec_cur_time_insert.f15 ---attribute_category
, rec_cur_time_insert.f16 ---to_region_id
, rec_cur_time_insert.f17 ---destination_type
, rec_cur_time_insert.f18 ---operation
, l_extract_flag ---extract_flag
, SYSDATE ---extract_date
,to_date('2010-01-01','RRRR-MM-DD')
,to_date('2999-01-01','RRRR-MM-DD')
);
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('Intransit Time Conversion Report', 50) || 'TIME:' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
fnd_file.put_line(fnd_file.LOG, 'Outputing transit_time.dat 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
|| ',' || col05e_desc
|| ',' || col06e_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.f05, 6) ---from_organization_name
|| RPAD(rec_time.f07, 4) ---to_organization_name
|| '2001-01-01'--effective from date
|| '2999-01-01' --effe
|| RPAD(rec_time.f18, 6) ---operation
|| RPAD(rec_time.f02, 2));---intransit_time
fnd_file.put_line(fnd_file.output, rec_time.f05---from_organization_name
||','|| rec_time.f07 ---to_organization_name
||','|| '2001-01-01'--effective fromdate
||','|| '2999-01-01'--effective to date
||','|| rec_time.f18 ---operation
||','|| rec_time.f02);---intransit_time
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;
-----call the extract procedure to create STORE-PRIMARY DC FILE-----
create_store_dc_file;
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 Intransit Time Conversion');
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 intransit time Conversion
--------------------------------------------------------------------------------
-- 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, 'Intransit Time Conversion ');
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;
------------------------------------
----this procedure creates a data file for store-primary dc combinations---
PROCEDURE create_store_dc_file
IS
col01e_desc VARCHAR2(70) := 'From_Organization';
col02e_desc VARCHAR2(70) := 'To_Organization';
col03e_desc VARCHAR2(70) := 'Relationship_type';
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;
CURSOR cur_records
IS
SELECT SUBSTR(dc_name,1,4) F01,
SUBSTR(store_name,1,4) F02,
'PrimaryDC' F03
FROM xxcofi_sourcing_store_dc;
BEGIN
----------
fnd_profile.get('XXCOFIDATA_OUT', l_top); --outbound file top
l_date := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI');
fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
fnd_file.put_line(fnd_file.output, 'STORE-PRIMARY DC RELATIONSHIP EXTRACT');
fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
fnd_file.put_line(fnd_file.log, '-----------------------------------------------------------------------');
fnd_file.put_line(fnd_file.log, 'creating store-primary dc extract file');
fnd_file.put_line(fnd_file.LOG, 'Outputing transit_rel.dat TO ' || l_top);
v_file_dc := UTL_FILE.fopen(l_top, 'transit_rel.dat', 'w', buff_size);
fnd_file.put_line(fnd_file.output, col01e_desc
|| ',' || col02e_desc
|| ',' || col03e_desc
);
FOR rec_records IN cur_records
LOOP
l_record_no := l_record_no + 1;
UTL_FILE.put_line(v_file_dc, RPAD(rec_records.f01, 4) ---from_organization_name DC
|| RPAD(rec_records.f02, 4) ---to_organization_name STORE
|| RPAD(rec_records.f03, 9));---relationship id ----1
fnd_file.put_line(fnd_file.output, rec_records.f01 ---from_organization_name DC
||','|| rec_records.f02 ---to_organization_name STORE
||','|| rec_records.f03);---relationship id ----1
--------------------------------------------------------------------------------
END LOOP;
fnd_file.put_line(fnd_file.LOG, '------------------------------------------------------------------');
fnd_file.put_line(fnd_file.LOG, 'COFI STORE - Primary DC Relationship Extract');
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, 'NO. of Records Extracted: '|| l_REcord_no);
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, '------------------------------------------------------------------');
END;
END 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';
col05e_desc VARCHAR2(70) := 'EffectiveFromDate';
col06e_desc VARCHAR2(70) := 'EffectiveToDate';
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.intransit_time = xxtime.intransit_time)
-- To exclude the records which alre already processed in staging table
UNION
SELECT DISTINCT NULL
, 1
, NULL
, TO_NUMBER(vendor_site_code) f05
, vendor_site_code f05
, organization_id F06
, substr(dest_org_code,1,4) f07
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'Create'
FROM xxcofi_sourcing_dc_to_vendor xxsource
WHERE NOT EXISTS(
SELECT 'X'
FROM xxcofi_time_extract_tbl xxtime
WHERE xxsource.vendor_site_code = xxtime.from_organization_name
AND xxsource.organization_id = xxtime.to_organization_id
AND 1 = xxtime.intransit_time);
-- To exclude the records which alre already processed in staging table
--cursor to get ship methods and respective intransit times----
CURSOR cur_record_time
IS
SELECT 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';
-- BEGIN
-- fnd_file.put_line(fnd_file.LOG , 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);
-- EXCEPTION
-- WHEN OTHERS THEN
-- fnd_file.put_line(fnd_file.LOG , 'error in getting base values');
-- END;
----- 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
,effective_from_date
,effective_to_date
)
VALUES (l_time_seq_num
, rec_cur_time_insert.f01 ---ship_method
, rec_cur_time_insert.f02 ---intransit_time
, rec_cur_time_insert.f03 ---default_flag
, rec_cur_time_insert.f04 ---from_organization_id
, rec_cur_time_insert.f05 ---from_organization_name
, rec_cur_time_insert.f06 ---to_organization_id
, rec_cur_time_insert.f07 ---to_organization_name
, rec_cur_time_insert.f08 ---from_location_id
, rec_cur_time_insert.f09 ---to_location_id
, rec_cur_time_insert.f10 ---last_update_date
, rec_cur_time_insert.f11 ---last_updated_by
, rec_cur_time_insert.f12 ---creation_date
, rec_cur_time_insert.f13 ---created_by
, rec_cur_time_insert.f14 ---last_update_login
, rec_cur_time_insert.f15 ---attribute_category
, rec_cur_time_insert.f16 ---to_region_id
, rec_cur_time_insert.f17 ---destination_type
, rec_cur_time_insert.f18 ---operation
, l_extract_flag ---extract_flag
, SYSDATE ---extract_date
,to_date('2010-01-01','RRRR-MM-DD')
,to_date('2999-01-01','RRRR-MM-DD')
);
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('Intransit Time Conversion Report', 50) || 'TIME:' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
fnd_file.put_line(fnd_file.LOG, 'Outputing transit_time.dat 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
|| ',' || col05e_desc
|| ',' || col06e_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.f05, 6) ---from_organization_name
|| RPAD(rec_time.f07, 4) ---to_organization_name
|| '2001-01-01'--effective from date
|| '2999-01-01' --effe
|| RPAD(rec_time.f18, 6) ---operation
|| RPAD(rec_time.f02, 2));---intransit_time
fnd_file.put_line(fnd_file.output, rec_time.f05---from_organization_name
||','|| rec_time.f07 ---to_organization_name
||','|| '2001-01-01'--effective fromdate
||','|| '2999-01-01'--effective to date
||','|| rec_time.f18 ---operation
||','|| rec_time.f02);---intransit_time
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;
-----call the extract procedure to create STORE-PRIMARY DC FILE-----
create_store_dc_file;
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 Intransit Time Conversion');
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 intransit time Conversion
--------------------------------------------------------------------------------
-- 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, 'Intransit Time Conversion ');
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;
------------------------------------
----this procedure creates a data file for store-primary dc combinations---
PROCEDURE create_store_dc_file
IS
col01e_desc VARCHAR2(70) := 'From_Organization';
col02e_desc VARCHAR2(70) := 'To_Organization';
col03e_desc VARCHAR2(70) := 'Relationship_type';
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;
CURSOR cur_records
IS
SELECT SUBSTR(dc_name,1,4) F01,
SUBSTR(store_name,1,4) F02,
'PrimaryDC' F03
FROM xxcofi_sourcing_store_dc;
BEGIN
----------
fnd_profile.get('XXCOFIDATA_OUT', l_top); --outbound file top
l_date := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI');
fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
fnd_file.put_line(fnd_file.output, 'STORE-PRIMARY DC RELATIONSHIP EXTRACT');
fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
fnd_file.put_line(fnd_file.log, '-----------------------------------------------------------------------');
fnd_file.put_line(fnd_file.log, 'creating store-primary dc extract file');
fnd_file.put_line(fnd_file.LOG, 'Outputing transit_rel.dat TO ' || l_top);
v_file_dc := UTL_FILE.fopen(l_top, 'transit_rel.dat', 'w', buff_size);
fnd_file.put_line(fnd_file.output, col01e_desc
|| ',' || col02e_desc
|| ',' || col03e_desc
);
FOR rec_records IN cur_records
LOOP
l_record_no := l_record_no + 1;
UTL_FILE.put_line(v_file_dc, RPAD(rec_records.f01, 4) ---from_organization_name DC
|| RPAD(rec_records.f02, 4) ---to_organization_name STORE
|| RPAD(rec_records.f03, 9));---relationship id ----1
fnd_file.put_line(fnd_file.output, rec_records.f01 ---from_organization_name DC
||','|| rec_records.f02 ---to_organization_name STORE
||','|| rec_records.f03);---relationship id ----1
--------------------------------------------------------------------------------
END LOOP;
fnd_file.put_line(fnd_file.LOG, '------------------------------------------------------------------');
fnd_file.put_line(fnd_file.LOG, 'COFI STORE - Primary DC Relationship Extract');
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, 'NO. of Records Extracted: '|| l_REcord_no);
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, '------------------------------------------------------------------');
END;
END xxcofi_time_conv_pkg;
/
No comments:
Post a Comment