CREATE OR REPLACE PACKAGE APPS.xxcofi_related_items_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_related_items_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Chandra Sekhar */
/* */
/* DATE : 01-Jun-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will extract related items and generate flatfile*/
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 01/06/10 Chandra 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
PROCEDURE main(
errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_number IN NUMBER
);
END xxcofi_related_items_pkg;
/
/
CREATE OR REPLACE PACKAGE BODY APPS.xxcofi_related_items_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_related_items_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Chandra Sekhar */
/* */
/* DATE : 01-Jun-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will extract related items and generate flatfile*/
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 01/06/10 Chandra 1.0 Initial creation */
/* 04/20/11 Ashwini 1.1 Convert relationship only for converted items */
/* -------------------------------------------------------------------------- */
PROCEDURE main(
errbuf OUT VARCHAR2
, retcode OUT NUMBER
,p_number in number
)
AS
/*****************************************************************************************/
--- Procedure to create report outbound File
/*****************************************************************************************/
-----Variables Declaration-----
report_name VARCHAR2(70) := 'XXCOFI_RELATED_ITEMS_EXTRACT';
col01e_desc VARCHAR2(30) := 'SKU_Number';
col02e_desc VARCHAR2(30) := 'Enterprise_Code';
col03e_desc VARCHAR2(30) := 'UOM';
col04e_desc VARCHAR2(30) := 'Action';
col05e_desc VARCHAR2(30) := 'Associated_quantity';
col06e_desc VARCHAR2(30) := 'Relation_Type';
col07e_desc VARCHAR2(30) := 'Effective_From';
col08e_desc VARCHAR2(30) := 'Effective_To';
col09e_desc VARCHAR2(30) := 'Related_SKU';
col10e_desc VARCHAR2(30) := 'Enterprise_Code';
col11e_desc VARCHAR2(30) := 'UOM';
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_item_seq_num NUMBER;
l_extract_flag VARCHAR2(30);
l_update_count NUMBER := 0;
l_total_insert_count NUMBER := 0;
l_additional_insert_count NUMBER := 0;
-- Cursor to Extract the Related Items Information
CURSOR cur_item_insert
IS
SELECT DISTINCT msi.segment1 f01,
decode(SUBSTR(TRIM (msi.primary_uom_code), -1, 1),'Y','DAYS',muomt.unit_of_measure_tl) f04, --muomt.unit_of_measure_tl f04,
r_msi.segment1 f02,
decode(SUBSTR(TRIM (r_msi.primary_uom_code), -1, 1),'Y','DAYS',r_muomt.unit_of_measure_tl) f05, -- r_muomt.unit_of_measure_tl f05,
flv.meaning f03,
mri.reciprocal_flag f06
FROM mtl_related_items mri,
mtl_system_items_b msi,
mtl_system_items_b r_msi,
mtl_units_of_measure_tl muomt,
mtl_units_of_measure_tl r_muomt,
fnd_lookup_values_vl flv
WHERE mri.inventory_item_id = msi.inventory_item_id
AND mri.related_item_id = r_msi.inventory_item_id
AND msi.organization_id = 22
AND r_msi.organization_id = 22
AND msi.inventory_item_status_code <> 'Suppressed'
AND r_msi.inventory_item_status_code <> 'Suppressed'
AND msi.primary_unit_of_measure = muomt.unit_of_measure
AND muomt.LANGUAGE = 'US'
AND r_msi.primary_unit_of_measure = r_muomt.unit_of_measure
AND r_muomt.LANGUAGE = 'US'
AND flv.lookup_type = 'MTL_RELATIONSHIP_TYPES'
AND flv.lookup_code = mri.relationship_type_id
AND msi.item_type IN
('CON', 'CUS', 'NC', 'SB', 'SPE', 'STD', 'VM', 'STANDARD')
AND EXISTS (SELECT 1
FROM xxcofi_replacement_item_ext xri
WHERE file_seq_num = 1 AND xri.sku = msi.segment1)
AND EXISTS (SELECT 1
FROM xxcofi_replacement_item_ext r_xri
WHERE file_seq_num = 1 AND r_xri.sku = r_msi.segment1)
AND ROWNUM <= p_number
AND NOT EXISTS (
SELECT 1
FROM xxcofi_related_item_tbl xxitem
WHERE xxitem.sku_number = msi.segment1
AND xxitem.related_sku = r_msi.segment1
AND flv.meaning = xxitem.relation_type)
--************ Added by Yogesh to convert relationshuip for 802 converted items only
--**** AND EXISTS (SELECT 1
--**** FROM xxcofi.sku_list_yog sl
--**** WHERE comments IS NULL AND sl.segment1 = msi.segment1)
ORDER BY msi.segment1;
-- Commented by Yogesh to modify as above
/* SELECT distinct
xri.sku f01
, xri.uom f04
, xri1.uom f05
, xri1.sku f02
, flv.meaning f03
, mri.reciprocal_flag f06
FROM mtl_related_items mri
, xxcofi_replacement_item_ext xri
, xxcofi_replacement_item_ext xri1
, fnd_lookup_values_vl flv
WHERE mri.inventory_item_id = xri.inventory_item_id
AND mri.related_item_id = xri1.inventory_item_id
AND flv.lookup_type = 'MTL_RELATIONSHIP_TYPES'
AND flv.lookup_code = mri.relationship_type_id
AND xri.itemtype in('Consignment','Custom','Non-COFI','Sell/Buy','Special','Standard','Vendor Model')
AND NOT EXISTS(SELECT 'X'
FROM xxcofi_related_item_tbl xxitem
WHERE xri.sku= xxitem.sku_number
AND xri1.sku=xxitem.related_sku
AND flv.meaning=xxitem.relation_type)
and exists ( select * from xxcofi.sku_list_yog sl
where comments is null
and sl.segment1=xri.sku ) --************ Added by Yogesh to convert relationshuip for 803 converted items only
AND ROWNUM<=p_number
AND xri.file_seq_num=1
AND xri1.file_seq_num=1
---and xri1.sku in ('03014642')
AND xri.item_status<>'Suppressed'
AND xri1.item_status<>'Suppressed'
ORDER BY xri.sku; */
--fnd_file.put_line (fnd_file.LOG,'Out Bound Extract Started');
CURSOR cur_record_item
IS
SELECT DISTINCT sku_number f01
, enterprise_code f02
, uom f03
, action f04
, associated_quantity f05
, relation_type f06
, to_CHAR(effective_from ,'DD/MM/YYYY') f07
, to_CHAR(effective_to ,'DD/MM/YYYY') f08
, related_sku f09
, enterprise_code2 f10
, uom2 f11
FROM xxcofi_related_item_tbl
WHERE extract_flag = 'TBE'
AND item_seq_num = 1
-- AND ROWNUM <=p_number --commneted by ashwini
ORDER BY sku_number;
BEGIN
FOR rec_cur_item_insert IN cur_item_insert
LOOP
l_item_seq_num := 1;
l_extract_flag := 'TBE';
----- Inserting Related Items Information into a staging table
BEGIN
INSERT INTO xxcofi_related_item_tbl
(item_seq_num
, sku_number
, enterprise_code
, uom
, action
, associated_quantity
, relation_type
, effective_from
, effective_to
, related_sku
, enterprise_code2
, uom2
, extract_flag
)
VALUES (l_item_seq_num
, rec_cur_item_insert.f01
, 'BAY'
,rec_cur_item_insert.f04
, 'Create'
, '1'
, rec_cur_item_insert.f03
, to_DATE('1/1/2010','DD/MM/YYYY')
, to_date('12/12/2999','DD/MM/YYYY')
, rec_cur_item_insert.f02
, 'BAY'
, rec_cur_item_insert.f05
, l_extract_flag
);
COMMIT;
l_total_insert_count := l_total_insert_count + 1;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, SQLERRM || 'Error in Inserting the values into staging table for SKU:' || rec_cur_item_insert.f01);
END;
-- Changes by Ashwini to create reciprocal records
-- Apr 08, 2011 Requestd by James
if rec_cur_item_insert.f06 = 'Y'
then
BEGIN
INSERT INTO xxcofi_related_item_tbl
(item_seq_num
, sku_number
, enterprise_code
, uom
, action
, associated_quantity
, relation_type
, effective_from
, effective_to
, related_sku
, enterprise_code2
, uom2
, extract_flag
)
VALUES (l_item_seq_num
, rec_cur_item_insert.f02
, 'BAY'
,rec_cur_item_insert.f05
, 'Create'
, '1'
, rec_cur_item_insert.f03
, to_DATE('1/1/2010','DD/MM/YYYY')
, to_date('12/12/2999','DD/MM/YYYY')
, rec_cur_item_insert.f01
, 'BAY'
, rec_cur_item_insert.f04
, l_extract_flag
);
COMMIT;
l_additional_insert_count := l_additional_insert_count + 1;
l_total_insert_count := l_total_insert_count +1;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, SQLERRM || 'Error in Inserting the values into staging table for SKU:' || rec_cur_item_insert.f02);
END;
end if;
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('COFI Related Item 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
, 'related_items.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
|| ','
|| col05e_desc
|| ','
|| col06e_desc
|| ','
|| col07e_desc
|| ','
|| col08e_desc
|| ','
|| col09e_desc
|| ','
|| col10e_desc
|| ','
|| col11e_desc);
FOR rec_item IN cur_record_item
LOOP
l_record_no := l_record_no + 1;
UTL_FILE.put_line(v_file_dc
, RPAD(rec_item.f01, 8)
|| RPAD(rec_item.f02, 3)
|| RPAD(rec_item.f03, 4)
|| RPAD(rec_item.f04, 6)
|| RPAD(rec_item.f05, 1)
|| RPAD(rec_item.f06, 21)
|| RPAD(rec_item.f07, 10)
|| RPAD(rec_item.f08, 10)
|| RPAD(rec_item.f09, 8)
|| RPAD(rec_item.f10, 3)
|| RPAD(rec_item.f11, 4));
fnd_file.put_line(fnd_file.output
, rec_item.f01
|| ','
|| rec_item.f02
|| ','
|| rec_item.f03
|| ','
|| rec_item.f04
|| ','
|| rec_item.f05
|| ','
|| rec_item.f06
|| ','
|| rec_item.f07
|| ','
|| rec_item.f08
|| ','
|| rec_item.f09
|| ','
|| rec_item.f10
|| ','
|| rec_item.f11);
BEGIN
l_update_count := l_update_count + 1;
UPDATE xxcofi_related_item_tbl
SET extract_flag = 'E'
WHERE sku_number = rec_item.f01;
-- fnd_file.put_line(fnd_file.LOG, 'rowcount :' || sql%rowcount);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, 'update failed SKU :' || rec_item.f01);
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 RELATED ITEM 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 inserted in custom table : ' || l_total_insert_count);
fnd_file.put_line(fnd_file.LOG, 'ADDITONAL NUMBER of RECORDS inserted in custom table : ' || l_additional_insert_count);
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, 'Related Item 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_related_items_pkg;
/
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_related_items_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Chandra Sekhar */
/* */
/* DATE : 01-Jun-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will extract related items and generate flatfile*/
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 01/06/10 Chandra 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
PROCEDURE main(
errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_number IN NUMBER
);
END xxcofi_related_items_pkg;
/
/
CREATE OR REPLACE PACKAGE BODY APPS.xxcofi_related_items_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_related_items_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Chandra Sekhar */
/* */
/* DATE : 01-Jun-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will extract related items and generate flatfile*/
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 01/06/10 Chandra 1.0 Initial creation */
/* 04/20/11 Ashwini 1.1 Convert relationship only for converted items */
/* -------------------------------------------------------------------------- */
PROCEDURE main(
errbuf OUT VARCHAR2
, retcode OUT NUMBER
,p_number in number
)
AS
/*****************************************************************************************/
--- Procedure to create report outbound File
/*****************************************************************************************/
-----Variables Declaration-----
report_name VARCHAR2(70) := 'XXCOFI_RELATED_ITEMS_EXTRACT';
col01e_desc VARCHAR2(30) := 'SKU_Number';
col02e_desc VARCHAR2(30) := 'Enterprise_Code';
col03e_desc VARCHAR2(30) := 'UOM';
col04e_desc VARCHAR2(30) := 'Action';
col05e_desc VARCHAR2(30) := 'Associated_quantity';
col06e_desc VARCHAR2(30) := 'Relation_Type';
col07e_desc VARCHAR2(30) := 'Effective_From';
col08e_desc VARCHAR2(30) := 'Effective_To';
col09e_desc VARCHAR2(30) := 'Related_SKU';
col10e_desc VARCHAR2(30) := 'Enterprise_Code';
col11e_desc VARCHAR2(30) := 'UOM';
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_item_seq_num NUMBER;
l_extract_flag VARCHAR2(30);
l_update_count NUMBER := 0;
l_total_insert_count NUMBER := 0;
l_additional_insert_count NUMBER := 0;
-- Cursor to Extract the Related Items Information
CURSOR cur_item_insert
IS
SELECT DISTINCT msi.segment1 f01,
decode(SUBSTR(TRIM (msi.primary_uom_code), -1, 1),'Y','DAYS',muomt.unit_of_measure_tl) f04, --muomt.unit_of_measure_tl f04,
r_msi.segment1 f02,
decode(SUBSTR(TRIM (r_msi.primary_uom_code), -1, 1),'Y','DAYS',r_muomt.unit_of_measure_tl) f05, -- r_muomt.unit_of_measure_tl f05,
flv.meaning f03,
mri.reciprocal_flag f06
FROM mtl_related_items mri,
mtl_system_items_b msi,
mtl_system_items_b r_msi,
mtl_units_of_measure_tl muomt,
mtl_units_of_measure_tl r_muomt,
fnd_lookup_values_vl flv
WHERE mri.inventory_item_id = msi.inventory_item_id
AND mri.related_item_id = r_msi.inventory_item_id
AND msi.organization_id = 22
AND r_msi.organization_id = 22
AND msi.inventory_item_status_code <> 'Suppressed'
AND r_msi.inventory_item_status_code <> 'Suppressed'
AND msi.primary_unit_of_measure = muomt.unit_of_measure
AND muomt.LANGUAGE = 'US'
AND r_msi.primary_unit_of_measure = r_muomt.unit_of_measure
AND r_muomt.LANGUAGE = 'US'
AND flv.lookup_type = 'MTL_RELATIONSHIP_TYPES'
AND flv.lookup_code = mri.relationship_type_id
AND msi.item_type IN
('CON', 'CUS', 'NC', 'SB', 'SPE', 'STD', 'VM', 'STANDARD')
AND EXISTS (SELECT 1
FROM xxcofi_replacement_item_ext xri
WHERE file_seq_num = 1 AND xri.sku = msi.segment1)
AND EXISTS (SELECT 1
FROM xxcofi_replacement_item_ext r_xri
WHERE file_seq_num = 1 AND r_xri.sku = r_msi.segment1)
AND ROWNUM <= p_number
AND NOT EXISTS (
SELECT 1
FROM xxcofi_related_item_tbl xxitem
WHERE xxitem.sku_number = msi.segment1
AND xxitem.related_sku = r_msi.segment1
AND flv.meaning = xxitem.relation_type)
--************ Added by Yogesh to convert relationshuip for 802 converted items only
--**** AND EXISTS (SELECT 1
--**** FROM xxcofi.sku_list_yog sl
--**** WHERE comments IS NULL AND sl.segment1 = msi.segment1)
ORDER BY msi.segment1;
-- Commented by Yogesh to modify as above
/* SELECT distinct
xri.sku f01
, xri.uom f04
, xri1.uom f05
, xri1.sku f02
, flv.meaning f03
, mri.reciprocal_flag f06
FROM mtl_related_items mri
, xxcofi_replacement_item_ext xri
, xxcofi_replacement_item_ext xri1
, fnd_lookup_values_vl flv
WHERE mri.inventory_item_id = xri.inventory_item_id
AND mri.related_item_id = xri1.inventory_item_id
AND flv.lookup_type = 'MTL_RELATIONSHIP_TYPES'
AND flv.lookup_code = mri.relationship_type_id
AND xri.itemtype in('Consignment','Custom','Non-COFI','Sell/Buy','Special','Standard','Vendor Model')
AND NOT EXISTS(SELECT 'X'
FROM xxcofi_related_item_tbl xxitem
WHERE xri.sku= xxitem.sku_number
AND xri1.sku=xxitem.related_sku
AND flv.meaning=xxitem.relation_type)
and exists ( select * from xxcofi.sku_list_yog sl
where comments is null
and sl.segment1=xri.sku ) --************ Added by Yogesh to convert relationshuip for 803 converted items only
AND ROWNUM<=p_number
AND xri.file_seq_num=1
AND xri1.file_seq_num=1
---and xri1.sku in ('03014642')
AND xri.item_status<>'Suppressed'
AND xri1.item_status<>'Suppressed'
ORDER BY xri.sku; */
--fnd_file.put_line (fnd_file.LOG,'Out Bound Extract Started');
CURSOR cur_record_item
IS
SELECT DISTINCT sku_number f01
, enterprise_code f02
, uom f03
, action f04
, associated_quantity f05
, relation_type f06
, to_CHAR(effective_from ,'DD/MM/YYYY') f07
, to_CHAR(effective_to ,'DD/MM/YYYY') f08
, related_sku f09
, enterprise_code2 f10
, uom2 f11
FROM xxcofi_related_item_tbl
WHERE extract_flag = 'TBE'
AND item_seq_num = 1
-- AND ROWNUM <=p_number --commneted by ashwini
ORDER BY sku_number;
BEGIN
FOR rec_cur_item_insert IN cur_item_insert
LOOP
l_item_seq_num := 1;
l_extract_flag := 'TBE';
----- Inserting Related Items Information into a staging table
BEGIN
INSERT INTO xxcofi_related_item_tbl
(item_seq_num
, sku_number
, enterprise_code
, uom
, action
, associated_quantity
, relation_type
, effective_from
, effective_to
, related_sku
, enterprise_code2
, uom2
, extract_flag
)
VALUES (l_item_seq_num
, rec_cur_item_insert.f01
, 'BAY'
,rec_cur_item_insert.f04
, 'Create'
, '1'
, rec_cur_item_insert.f03
, to_DATE('1/1/2010','DD/MM/YYYY')
, to_date('12/12/2999','DD/MM/YYYY')
, rec_cur_item_insert.f02
, 'BAY'
, rec_cur_item_insert.f05
, l_extract_flag
);
COMMIT;
l_total_insert_count := l_total_insert_count + 1;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, SQLERRM || 'Error in Inserting the values into staging table for SKU:' || rec_cur_item_insert.f01);
END;
-- Changes by Ashwini to create reciprocal records
-- Apr 08, 2011 Requestd by James
if rec_cur_item_insert.f06 = 'Y'
then
BEGIN
INSERT INTO xxcofi_related_item_tbl
(item_seq_num
, sku_number
, enterprise_code
, uom
, action
, associated_quantity
, relation_type
, effective_from
, effective_to
, related_sku
, enterprise_code2
, uom2
, extract_flag
)
VALUES (l_item_seq_num
, rec_cur_item_insert.f02
, 'BAY'
,rec_cur_item_insert.f05
, 'Create'
, '1'
, rec_cur_item_insert.f03
, to_DATE('1/1/2010','DD/MM/YYYY')
, to_date('12/12/2999','DD/MM/YYYY')
, rec_cur_item_insert.f01
, 'BAY'
, rec_cur_item_insert.f04
, l_extract_flag
);
COMMIT;
l_additional_insert_count := l_additional_insert_count + 1;
l_total_insert_count := l_total_insert_count +1;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, SQLERRM || 'Error in Inserting the values into staging table for SKU:' || rec_cur_item_insert.f02);
END;
end if;
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('COFI Related Item 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
, 'related_items.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
|| ','
|| col05e_desc
|| ','
|| col06e_desc
|| ','
|| col07e_desc
|| ','
|| col08e_desc
|| ','
|| col09e_desc
|| ','
|| col10e_desc
|| ','
|| col11e_desc);
FOR rec_item IN cur_record_item
LOOP
l_record_no := l_record_no + 1;
UTL_FILE.put_line(v_file_dc
, RPAD(rec_item.f01, 8)
|| RPAD(rec_item.f02, 3)
|| RPAD(rec_item.f03, 4)
|| RPAD(rec_item.f04, 6)
|| RPAD(rec_item.f05, 1)
|| RPAD(rec_item.f06, 21)
|| RPAD(rec_item.f07, 10)
|| RPAD(rec_item.f08, 10)
|| RPAD(rec_item.f09, 8)
|| RPAD(rec_item.f10, 3)
|| RPAD(rec_item.f11, 4));
fnd_file.put_line(fnd_file.output
, rec_item.f01
|| ','
|| rec_item.f02
|| ','
|| rec_item.f03
|| ','
|| rec_item.f04
|| ','
|| rec_item.f05
|| ','
|| rec_item.f06
|| ','
|| rec_item.f07
|| ','
|| rec_item.f08
|| ','
|| rec_item.f09
|| ','
|| rec_item.f10
|| ','
|| rec_item.f11);
BEGIN
l_update_count := l_update_count + 1;
UPDATE xxcofi_related_item_tbl
SET extract_flag = 'E'
WHERE sku_number = rec_item.f01;
-- fnd_file.put_line(fnd_file.LOG, 'rowcount :' || sql%rowcount);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, 'update failed SKU :' || rec_item.f01);
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 RELATED ITEM 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 inserted in custom table : ' || l_total_insert_count);
fnd_file.put_line(fnd_file.LOG, 'ADDITONAL NUMBER of RECORDS inserted in custom table : ' || l_additional_insert_count);
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, 'Related Item 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_related_items_pkg;
/
No comments:
Post a Comment