Chandra Sekhar Kadali - Oracle Apps Knowledge Sharing: Price List

Showing posts with label Price List. Show all posts

Saturday, March 06, 2010

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;

/

Thursday, March 04, 2010

To Get Active Price list,Modifiers,

SELECT distinct qlh.comments "Modifier Name"

,qqv.rule_name "Qualifier Group"

,ou.name "Store Id"

--,qlhv.name "Price list name"

,qms.product_attr_value "SKU"

,qms.list_line_no "Modifier Line No"

,qms.start_date_active "Start Date"

,qms.end_date_active "End Date"

,qms.arithmetic_operator_type "Application Method"

,qms.operand "Value"

,qms.product_precedence "Precedence"

,qms.incompatibility_grp "Incompatibility Group"

,qms.pricing_group_sequence "Bucket"

FROM

qp_modifier_summary_v qms

, qp_list_headers_b qlh

,qp_list_headers_tl qlt

,qp_qualifiers_v qqv

,mtl_system_items_b msi

,hr_all_organization_units ou

--,qp_list_headers_v qlhv

WHERE

qlh.list_header_id = qms.list_header_id

--and qms.list_header_id=qlhv.list_header_id

and qlh.list_header_id =qqv.list_header_id

and to_char(msi.inventory_item_id)=qms.product_attr_val

AND ou.organization_id = msi.organization_id

and to_char(ou.organization_id)= qqv.qualifier_attr_value

and sysdate between qms.start_date_active and qms.end_date_active

and qlt.LIST_HEADER_ID=qlh.LIST_HEADER_ID

AND exists

(select 1

from mtl_system_items_b a

where a.organization_id=22

and to_char(a.inventory_item_id)=qms.product_attr_val

and a.segment1 in('61054243'))

Active Price list query:

SELECT msi.segment1 "Sku",

msi.description "Sku Description",

qph.name "Price list name",

qpl.START_DATE_ACTIVE "Start Date",

qpl.END_DATE_ACTIVE "End date"

---qph.list_header_id

FROM

qp_list_headers_v qph,

qp.qp_list_lines qpl,

qp_pricing_attributes qpa,

mtl_system_items_b msi

WHERE

---source_lang = 'US'

qph.list_header_id = qpl.list_header_id

AND qpa.list_line_id = qpl.list_line_id

AND qpa.list_header_id = qph.list_header_id

AND qpa.product_attribute_context = 'ITEM'

AND product_attribute = 'PRICING_ATTRIBUTE1'

AND msi.organization_id = 22

AND msi.inventory_item_id = qpa.product_attr_value

--AND qpl.end_date_active IS NULL

--AND qph.LANGUAGE = 'US'

and ((sysdate between qpl.START_DATE_ACTIVE

and qpl.END_DATE_ACTIVE)

or (qpl.START_DATE_ACTIVE < sysdate

and qpl.END_DATE_ACTIVE is null ))

AND msi.segment1 in ('61054243')

Active Modifiers:

SELECT distinct qlh.comments "Modifier Name"

,qms.start_date_active "Start Date"

,qms.end_date_active "End Date"

,qlh.active_flag

,qlh.automatic_flag

FROM

qp_modifier_summary_v qms,

qp_list_headers_b qlh

WHERE

qlh.list_header_id = qms.list_header_id

and sysdate between qms.start_date_active and qms.end_date_active

and trunc(qms.end_date_active) <'12-JAN-2009'

To Get all the Active price list Information

Select MSI.SEGMENT1 "SKU"

,MSI.DESCRIPTION "Description"

,MSI.INVENTORY_ITEM_STATUS_CODE "Item Status"

,PLH.NAME "Price List Name"

,PLL.LIST_PRICE "Price"

,PLL.LAST_UPDATE_DATE "List Price Last

Updated"

,PLL.START_DATE_ACTIVE "Start Date"

,PLL.END_DATE_ACTIVE "End Date"

,PLL.ATTRIBUTE1 "Price Type"

,CICT.ITEM_COST "Frozen Cost"

,CICT.LAST_UPDATE_DATE "Frozen Cost Last

Updated"

From QP_PRICE_LISTS_V PLH

,QP_PRICE_LIST_LINES_V PLL

,MTL_SYSTEM_ITEMS_B MSI

----,CST_ITEM_COST_TYPE_V CICT

Where MSI.INVENTORY_ITEM_ID =

PLL.INVENTORY_ITEM_ID

and PLH.PRICE_LIST_ID = PLL.PRICE_LIST_ID

and MSI.INVENTORY_ITEM_ID =

CICT.INVENTORY_ITEM_ID

and MSI.ORGANIZATION_ID = 22

-----and CICT.ORGANIZATION_ID = 22

----and CICT.COST_TYPE = 'Frozen'

and MSI.INVENTORY_ITEM_FLAG = 'Y'

and MSI.ITEM_TYPE <> 'VM'

and MSI.INVENTORY_ITEM_STATUS_CODE <>

'Suppressed'

and PLL.END_DATE_ACTIVE is null

and (PLL.START_DATE_ACTIVE is null or

trunc(PLL.start_date_active) <=

trunc(SYSDATE))

and (PLL.END_DATE_ACTIVE is null or

trunc(PLL.end_date_active) >= trunc(SYSDATE))

----and CICT.ITEM_COST <> PLL.LIST_PRICE

-----nd PLL.LIST_PRICE <> 0

----and CICT.ITEM_COST <> 0

------and (trunc(CICT.LAST_UPDATE_DATE) <

trunc(SYSDATE))

Chandra Sekhar Kadali - Oracle Apps Knowledge Sharing

Visitors

Saturday, March 06, 2010

Price List Extract

Thursday, March 04, 2010

To Get Active Price list,Modifiers,

To Get all the Active price list Information

Price List Extract

Translate

Viewers

Labels