/* Thanks to Hareesha for providing this Script */
CREATE OR REPLACE PACKAGE APPS.XXCOFI_SEC_SUPP_RETEK_PKG
AS
/* -------------------------------------------------------------------------- */
/* Program Name : XXCOFI_SEC_SUPP_RETEK_PKG */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- errbuf, retcode */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Hareesha Rodda */
/* */
/* DATE : 06-DEC-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will be used for creation of outbound file */
/* for secondary suppliers,which will be imported to Retek */
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 06/12/2010 Hareesha Rodda 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
PROCEDURE XXCOFI_SEC_SUPP_EXTRACT(
ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2
);
END XXCOFI_SEC_SUPP_RETEK_PKG;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXCOFI_SEC_SUPP_RETEK_PKG
IS
/* -------------------------------------------------------------------------- */
/* Program Name : XXCOFI_SEC_SUPP_RETEK_PKG */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- errbuf, retcode */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Hareesha Rodda */
/* */
/* DATE : 06-DEC-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will be used for creation of outbound file */
/* for secondary suppliers,which will be imported to Retek */
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 06/12/2010 Hareesha Rodda 1.0 Initial creation */
/* 02-Jun-2011 Yogesh 1.1 Changed output path */
/* -------------------------------------------------------------------------- */
PROCEDURE XXCOFI_SEC_SUPP_EXTRACT (
errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
)
IS
CURSOR out_record_c
IS
SELECT DISTINCT
si.segment1 SKU
,vs.vendor_site_code Sec_supplier
,decode(round(si.list_price_per_unit,2)*100,null,'0',round(si.list_price_per_unit,2)*100) Cost_per_unit
,mde3.element_value VPN
,mde4.element_value Supp_Colour
,mde5.element_value Supp_Size
,decode(round(si.unit_length,4)*10000,null,'0',round(si.unit_length,4)*10000) Item_unit_len
,decode(round(si.unit_height,4)*10000,null,'0',round(si.unit_height,4)*10000) Item_unit_ht
,decode(round(si.unit_weight,4)*10000,null,'0',round(si.unit_weight,4)*10000) Item_unit_wt
,decode(round(si.unit_width,4)*10000,null,'0',round(si.unit_width,4) *10000) Item_unit_wid
,decode (round(to_number(sl.attribute3),4)*10000,null,'0',round(to_number(sl.attribute3),4)*10000) ELC_AMT
FROM mtl_system_items_b si
,inv.mtl_descr_element_values mde3
,inv.mtl_descr_element_values mde4
,inv.mtl_descr_element_values mde5
,po_vendors vn
,po_vendor_sites_all vs
,po_approved_supplier_list sl
WHERE 1=1
AND si.organization_id =22
---AND si.segment1 in('03004863')
AND si.inventory_item_id = mde3.inventory_item_id
AND si.inventory_item_id = mde4.inventory_item_id
AND si.inventory_item_id = mde5.inventory_item_id
AND mde3.element_name = 'Model/Style Number'
AND mde4.element_name = 'Colour'
AND mde5.element_name = 'Size'
AND sl.attribute5 <> 'Primary'
AND sl.item_id = si.inventory_item_id
AND vs.vendor_id = sl.vendor_id
AND vs.vendor_site_id = sl.vendor_site_id
AND vn.vendor_id = sl.vendor_id
AND NVL(sl.disable_flag,'N') = 'N'
ORDER BY 1,2
;
v_file_dc UTL_FILE.file_type;
buff_size NUMBER := 20000;
l_top VARCHAR2(200) := NULL;
l_top1 VARCHAR2(200) := NULL;
l_record_no NUMBER := 0;
v_file_name varchar2(50);
v_file_name_arc varchar2(50);
BEGIN
v_file_name := 'sec_supplier.dat' ;
fnd_profile.get('XXCOFIDATA_OUT', l_top);
fnd_profile.get('XXHBC_APARCHIVE',l_top1);
l_top:= l_top||'/ap/retek'; -- Yogesh for Retek
fnd_file.put_line(fnd_file.log,'Extract File path is : '||l_top);
fnd_file.put_line(fnd_file.log,'Extract File name is : '||v_file_name);
-- Yogesh 2-Jun-2011
v_file_dc :=
UTL_FILE.fopen (l_top, v_file_name,'w',buff_size);
FOR out_rec IN out_record_c LOOP
UTL_FILE.put_line (v_file_dc
, LPAD (nvl(out_rec.SKU,'0'), 8, '0')
|| LPAD (nvl(out_rec.Sec_supplier,'0'), 10, '0')
|| LPAD (out_rec.cost_per_unit, 10, '0')
|| RPAD (nvl(out_rec.VPN,' '), 30, ' ')
|| RPAD (nvl(out_rec.Supp_Colour,' '), 30, ' ')
|| RPAD (nvl(out_rec.Supp_Size,' '), 30, ' ')
|| LPAD (out_rec.Item_unit_len, 10, '0')
|| LPAD (out_rec.Item_unit_ht, 10, '0')
|| LPAD (out_rec.Item_unit_wt, 10, '0')
|| LPAD (out_rec.Item_unit_wid, 10, '0')
|| LPAD (out_rec.ELC_AMT, 7, '0'));
l_record_no := l_record_no + 1;
END LOOP;
fnd_file.put_line(fnd_file.log, 'Number of Records created to Secondary Vendor Extract File' || ' : ' || l_record_no);
UTL_FILE.fflush(v_file_dc);
UTL_FILE.fclose(v_file_dc);
v_file_name_arc := 'sec_supplier' || TO_CHAR (SYSDATE, 'yyyymmddhh24miss') || '.dat';
-- archive the file for future reference in archive/ap directory
UTL_FILE.fcopy (l_top, v_file_name, 'XXHBC_APARCHIVE', v_file_name_arc);
fnd_file.put_line(fnd_file.log,'Archive File path is : '||'XXHBC_APARCHIVE');
fnd_file.put_line(fnd_file.log,'Archive File name is : '||v_file_name_arc );
errbuf := 'SECONDARY SUPPLIERS EXTRACT COMPLETED SUCCESSFULLY';
retcode := '0';
fnd_file.put_line (fnd_file.LOG, 'JOB COMPLETED SUCCESSFULLY');
fnd_file.CLOSE; -- (FND_FILE.OUTPUT);
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;
END XXCOFI_SEC_SUPP_RETEK_PKG;
/
SHOW ERROR
/
EXIT
No comments:
Post a Comment