/* Thanks to Hareesha for Sharing this */
CREATE OR REPLACE PACKAGE APPS.xxcofi_off_reserve_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_off_reserve_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Hareesha Rodda */
/* */
/* DATE : 11-Oct-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package extracts the Off-Reserve Purchases and */
/* displays a report */
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 11/10/10 Hareesha Rodda 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
PROCEDURE main(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_period_name IN VARCHAR2
);
END xxcofi_off_reserve_pkg;
/
CREATE OR REPLACE PACKAGE BODY xxcofi_off_reserve_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_off_reserve_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Hareesha Rodda */
/* */
/* DATE : 11-Oct-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package extracts the Off-Reserve Purchases and */
/* displays a report */
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 11/10/10 Hareesha Rodda 1.0 Initial creation */
/* 01/11/10 Hareesha Rodda 1.1 Modified query,restricted to mainorg */
/* 11/1/11 Hareesha Rodda 1.2 Modified out filename to match */
/* conc-prog name */
/* 21/1/11 Hareesha Rodda 1.3 Appended date,timestamp to filename */
/* 24/2/11 Hareesha Rodda 1.4 Added glp.period_set_name condition */
/* to query */
/* -------------------------------------------------------------------------- */
PROCEDURE main(
errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_period_name IN VARCHAR2
)
AS
/*****************************************************************************************/
--- Procedure to create report
/*****************************************************************************************/
-----Variables Declaration-----
l_report_name VARCHAR2 (70) := 'XXCOFI_NATIVE_TAX_RPT_'||TO_CHAR(SYSDATE,'MMDDYYYYHH24MISS');
col01e_desc VARCHAR2 (30) := 'Selling_Store';
col02e_desc VARCHAR2 (30) := 'Order_num';
col03e_desc VARCHAR2 (30) := 'Line_num';
col04e_desc VARCHAR2 (30) := 'SKU';
col05e_desc VARCHAR2 (30) := 'SKU_Description';
col06e_desc VARCHAR2 (30) := 'Unit_Selling_Price';
col07e_desc VARCHAR2 (30) := 'Quantity_Sold';
col08e_desc VARCHAR2 (30) := 'Tax_Value';
col09e_desc VARCHAR2 (30) := 'Transaction_Paid_Date';
buff_size NUMBER := 24576;
l_record_no NUMBER := 0;
l_top VARCHAR2 (200) := NULL;
l_date VARCHAR2 (60) := NULL;
l_mode VARCHAR2 (5) := NULL;
v_file_handle UTL_FILE.file_type;
l_buff_size NUMBER := 32000;
-- Cursor to Extract the Native Tax
CURSOR cur_off_reserve IS
SELECT substr(hor.name,1,4) selling_store,
ooh.order_number order_num,
ool.line_number line_num,
msi.segment1 sku,
msi.description sku_description,
ool.unit_selling_price unit_selling_price,
ool.fulfilled_quantity qty_sold,
ool.tax_value tax_value,
acr.creation_date trans_paid_date
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
mtl_system_items_b msi,
hr_all_organization_units hor,
ar_cash_receipts_all acr,
Gl_periods glp
WHERE ooh.header_id = ool.header_id
AND ool.tax_code = '201009HST5NOPST'
AND ool.inventory_item_id = msi.inventory_item_id
And TO_NUMBER(ooh.attribute1) = hor.organization_id
And TO_NUMBER(ool.attribute7)= acr.cash_receipt_id
And acr.creation_date BETWEEN glp.start_date AND glp.end_date
And ooh.creation_date > TO_DATE('01-09-2010','DD-MM-YYYY')
AND msi.organization_id = 22
AND glp.period_set_name='HBC'
And glp.period_name = p_period_name
Order by ooh.order_number;
BEGIN
fnd_profile.get('XXCOFIDATA_OUT',l_top);
v_file_handle :=
UTL_FILE.fopen (l_top,
l_report_name || '.' || 'csv',
'W',
l_buff_size
);
fnd_file.put_line (fnd_file.LOG,
'START RUNNING '
|| l_report_name
|| ' PROGRAM.'
);
fnd_file.put_line (fnd_file.output,
RPAD ('HUDSON''S BAY COMPANY', 50)
|| 'DATE:'
|| SYSDATE
);
fnd_file.put_line (fnd_file.output,
RPAD ('Native Tax Report', 50)
|| 'TIME:'
|| TO_CHAR (SYSDATE, 'HH24:MI:SS')
);
fnd_file.put_line
(fnd_file.output,
'-----------------------------------------------------------------------'
);
utl_file.put_line (v_file_handle,
col01e_desc
|| ','
|| col02e_desc
|| ','
|| col03e_desc
|| ','
|| col04e_desc
|| ','
|| col05e_desc
|| ','
|| col06e_desc
|| ','
|| col07e_desc
|| ','
|| col08e_desc
|| ','
|| col09e_desc
);
fnd_file.put_line
(fnd_file.output,
'-----------------------------------------------------------------------'
);
FOR rec_off_reserve IN cur_off_reserve
LOOP
l_record_no := l_record_no + 1;
UTL_FILE.put_line
(v_file_handle,
rec_off_reserve.selling_store
|| ','
|| rec_off_reserve.order_num
|| ','
|| rec_off_reserve.line_num
|| ','
|| rec_off_reserve.sku
|| ','
|| rec_off_reserve.sku_description
|| ','
|| rec_off_reserve.unit_selling_price
|| ','
|| rec_off_reserve.qty_sold
|| ','
|| rec_off_reserve.tax_value
|| ','
|| TO_CHAR(rec_off_reserve.trans_paid_date)
);
COMMIT;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
'TOTAL NUMBER of RECORDS PROCESSED : ' || l_record_no
);
errbuf := l_report_name || ' REPORT FILE JOB COMPLETED SUCCESSFULLY';
retcode := '0';
fnd_file.put_line (fnd_file.LOG, 'JOB COMPLETED SUCCESSFULLY');
UTL_FILE.fflush (v_file_handle);
UTL_FILE.fclose (v_file_handle);
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - invalid path'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - invalid path';
retcode := 2;
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Invalid Mode'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - invalid mode';
retcode := 2;
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Invalid Operation'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - invalid operation';
retcode := 2;
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Invalid Filehandle'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - invalid filehandle';
retcode := 2;
WHEN UTL_FILE.write_error
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Write Error');
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - write error';
retcode := 2;
WHEN UTL_FILE.read_error
THEN
fnd_file.put_line (fnd_file.LOG, 'EXCEPTION RAISED - Read Error');
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - read error';
retcode := 2;
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Internal Error'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - internal error';
retcode := 2;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Other Error'
|| SQLCODE
|| SQLERRM
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - other error';
retcode := 2;
END main;
END xxcofi_off_reserve_pkg;
/
CREATE OR REPLACE PACKAGE APPS.xxcofi_off_reserve_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_off_reserve_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Hareesha Rodda */
/* */
/* DATE : 11-Oct-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package extracts the Off-Reserve Purchases and */
/* displays a report */
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 11/10/10 Hareesha Rodda 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
PROCEDURE main(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_period_name IN VARCHAR2
);
END xxcofi_off_reserve_pkg;
/
CREATE OR REPLACE PACKAGE BODY xxcofi_off_reserve_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_off_reserve_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Hareesha Rodda */
/* */
/* DATE : 11-Oct-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package extracts the Off-Reserve Purchases and */
/* displays a report */
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 11/10/10 Hareesha Rodda 1.0 Initial creation */
/* 01/11/10 Hareesha Rodda 1.1 Modified query,restricted to mainorg */
/* 11/1/11 Hareesha Rodda 1.2 Modified out filename to match */
/* conc-prog name */
/* 21/1/11 Hareesha Rodda 1.3 Appended date,timestamp to filename */
/* 24/2/11 Hareesha Rodda 1.4 Added glp.period_set_name condition */
/* to query */
/* -------------------------------------------------------------------------- */
PROCEDURE main(
errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_period_name IN VARCHAR2
)
AS
/*****************************************************************************************/
--- Procedure to create report
/*****************************************************************************************/
-----Variables Declaration-----
l_report_name VARCHAR2 (70) := 'XXCOFI_NATIVE_TAX_RPT_'||TO_CHAR(SYSDATE,'MMDDYYYYHH24MISS');
col01e_desc VARCHAR2 (30) := 'Selling_Store';
col02e_desc VARCHAR2 (30) := 'Order_num';
col03e_desc VARCHAR2 (30) := 'Line_num';
col04e_desc VARCHAR2 (30) := 'SKU';
col05e_desc VARCHAR2 (30) := 'SKU_Description';
col06e_desc VARCHAR2 (30) := 'Unit_Selling_Price';
col07e_desc VARCHAR2 (30) := 'Quantity_Sold';
col08e_desc VARCHAR2 (30) := 'Tax_Value';
col09e_desc VARCHAR2 (30) := 'Transaction_Paid_Date';
buff_size NUMBER := 24576;
l_record_no NUMBER := 0;
l_top VARCHAR2 (200) := NULL;
l_date VARCHAR2 (60) := NULL;
l_mode VARCHAR2 (5) := NULL;
v_file_handle UTL_FILE.file_type;
l_buff_size NUMBER := 32000;
-- Cursor to Extract the Native Tax
CURSOR cur_off_reserve IS
SELECT substr(hor.name,1,4) selling_store,
ooh.order_number order_num,
ool.line_number line_num,
msi.segment1 sku,
msi.description sku_description,
ool.unit_selling_price unit_selling_price,
ool.fulfilled_quantity qty_sold,
ool.tax_value tax_value,
acr.creation_date trans_paid_date
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
mtl_system_items_b msi,
hr_all_organization_units hor,
ar_cash_receipts_all acr,
Gl_periods glp
WHERE ooh.header_id = ool.header_id
AND ool.tax_code = '201009HST5NOPST'
AND ool.inventory_item_id = msi.inventory_item_id
And TO_NUMBER(ooh.attribute1) = hor.organization_id
And TO_NUMBER(ool.attribute7)= acr.cash_receipt_id
And acr.creation_date BETWEEN glp.start_date AND glp.end_date
And ooh.creation_date > TO_DATE('01-09-2010','DD-MM-YYYY')
AND msi.organization_id = 22
AND glp.period_set_name='HBC'
And glp.period_name = p_period_name
Order by ooh.order_number;
BEGIN
fnd_profile.get('XXCOFIDATA_OUT',l_top);
v_file_handle :=
UTL_FILE.fopen (l_top,
l_report_name || '.' || 'csv',
'W',
l_buff_size
);
fnd_file.put_line (fnd_file.LOG,
'START RUNNING '
|| l_report_name
|| ' PROGRAM.'
);
fnd_file.put_line (fnd_file.output,
RPAD ('HUDSON''S BAY COMPANY', 50)
|| 'DATE:'
|| SYSDATE
);
fnd_file.put_line (fnd_file.output,
RPAD ('Native Tax Report', 50)
|| 'TIME:'
|| TO_CHAR (SYSDATE, 'HH24:MI:SS')
);
fnd_file.put_line
(fnd_file.output,
'-----------------------------------------------------------------------'
);
utl_file.put_line (v_file_handle,
col01e_desc
|| ','
|| col02e_desc
|| ','
|| col03e_desc
|| ','
|| col04e_desc
|| ','
|| col05e_desc
|| ','
|| col06e_desc
|| ','
|| col07e_desc
|| ','
|| col08e_desc
|| ','
|| col09e_desc
);
fnd_file.put_line
(fnd_file.output,
'-----------------------------------------------------------------------'
);
FOR rec_off_reserve IN cur_off_reserve
LOOP
l_record_no := l_record_no + 1;
UTL_FILE.put_line
(v_file_handle,
rec_off_reserve.selling_store
|| ','
|| rec_off_reserve.order_num
|| ','
|| rec_off_reserve.line_num
|| ','
|| rec_off_reserve.sku
|| ','
|| rec_off_reserve.sku_description
|| ','
|| rec_off_reserve.unit_selling_price
|| ','
|| rec_off_reserve.qty_sold
|| ','
|| rec_off_reserve.tax_value
|| ','
|| TO_CHAR(rec_off_reserve.trans_paid_date)
);
COMMIT;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
'TOTAL NUMBER of RECORDS PROCESSED : ' || l_record_no
);
errbuf := l_report_name || ' REPORT FILE JOB COMPLETED SUCCESSFULLY';
retcode := '0';
fnd_file.put_line (fnd_file.LOG, 'JOB COMPLETED SUCCESSFULLY');
UTL_FILE.fflush (v_file_handle);
UTL_FILE.fclose (v_file_handle);
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - invalid path'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - invalid path';
retcode := 2;
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Invalid Mode'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - invalid mode';
retcode := 2;
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Invalid Operation'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - invalid operation';
retcode := 2;
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Invalid Filehandle'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - invalid filehandle';
retcode := 2;
WHEN UTL_FILE.write_error
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Write Error');
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - write error';
retcode := 2;
WHEN UTL_FILE.read_error
THEN
fnd_file.put_line (fnd_file.LOG, 'EXCEPTION RAISED - Read Error');
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - read error';
retcode := 2;
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Internal Error'
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - internal error';
retcode := 2;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'EXCEPTION RAISED - Other Error'
|| SQLCODE
|| SQLERRM
);
UTL_FILE.fclose (v_file_handle);
errbuf := 'EXCEPTION RAISED - other error';
retcode := 2;
END main;
END xxcofi_off_reserve_pkg;
/
No comments:
Post a Comment