Price List Extract

CREATE OR REPLACE PACKAGE BODY APPS.xxcofi_price_ext_pkg AS


/* -------------------------------------------------------------------------- */

/* Program Name : xxcofi_price_ext_pkg */

/* */

/* TYPE : PL/SQL Package */

/* */

/* Input Parms : */

/* */

/* Output Parms : -- */

/* */

/* Table Access : -- */

/* */

/* AUTHOR : Chandra Sekhar */

/* */

/* DATE : 12-Dec-2009 */

/* */

/* VERSION : 1.0 */

/* */

/* DESCRIPTION : This package will extract item prices and generate flatfile*/

/* */

/* */

/* */

/* CHANGE HISTORY */

/* -------------------------------------------------------------------------- */

/* DATE AUTHOR VERSION REASON */

/* -------------------------------------------------------------------------- */

/* 25/11/09 Chandra 1.0 Initial creation */

/* -------------------------------------------------------------------------- */

PROCEDURE main (errbuf OUT VARCHAR2,

retcode OUT VARCHAR2)

AS

/*****************************************************************************************/

--- Procedure to create report outbound File

/*****************************************************************************************/

report_name VARCHAR2 (70) := 'XXCOFI_Pricing_Extract';

col01e_desc VARCHAR2 (11) := 'Bay';

col02e_desc VARCHAR2 (8) := 'SKU Number';

col03e_desc VARCHAR2 (9) := 'Retail Price';

col04e_desc VARCHAR2 (6) := 'Manage Flag';

col05e_desc VARCHAR2 (6) := 'Item Status';

col06e_desc VARCHAR2 (10) := 'UOM';

col07e_desc VARCHAR2 (20) := 'Bay Price List';

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 out_record_price IS

SELECT

'Bay' f01

,msib.segment1 f02

,qll.list_price f03

,'Manage' f04

,msib.inventory_item_status_code f05

,msib.primary_unit_of_measure f06

,'BayPrice_List' f07

FROM

mtl_system_items_b msib,

qp_price_list_lines_v qll,

hr_all_organization_units hou

WHERE msib.inventory_item_id = qp_price_list_pvt.Get_Inventory_Item_Id(qll.price_list_line_id)

AND msib.organization_id= hou.organization_id

AND msib.organization_id = 22 -- for HBC organization

AND msib.inventory_item_status_code !='Suppressed'

AND (qll.end_date_active IS NULL OR qll.end_date_active>=SYSDATE)

AND SUBSTR (hou.NAME, 1, 1) !='0' -- Only for Bay Organizations

AND NOT exists (SELECT 1 FROM XXCOFI_DEFERRED_PLAN_V where segment1=msib.segment1)

AND msib.attribute_category<>'EFEE';

BEGIN

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.LOG,'Outputing '

report_name

'_'

l_date

'TO '

l_top);

v_file_dc := UTL_FILE.FOPEN (l_top, 'pricelist.dat','w', buff_size);

-- CREATE THE REPORT HEADER RECORD FOR REPORT OUTPUT FILE

UTL_FILE.PUT_LINE (v_file_dc,

lpad(col01e_desc,11,' ')



lpad(col02e_desc,8,' ')



lpad(col03e_desc,9,' ')



lpad(col04e_desc,6,' ')



lpad(col05e_desc,6,' ')



lpad(col06e_desc,10,' ')



lpad(col07e_desc,20,' ');

-- PUT START MESSAGE IN THE LOG FILE

FOR out_rec_dc IN out_record_price

LOOP

l_record_no := l_record_no + 1;

UTL_FILE.PUT_LINE (v_file_dc,

lpad(out_rec_dc.f01,11,' ')



lpad(out_rec_dc.f02,8,' ')



lpad(out_rec_dc.f03,9,' ')



lpad(out_rec_dc.f04,6,' ')



lpad(out_rec_dc.f05,6,' ')



lpad(out_rec_dc.f06,10,' ')



lpad(out_rec_dc.f07,20,' ');

END LOOP;

fnd_file.put_line (fnd_file.LOG,

'Number of Records Processed'

' : '

l_record_no);

--------------------------------------------------------------------------------

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_price_ext_pkg;

/