Using QP_INTERFACE_LIST_HEADERS , QP_INTERFACE_LIST_LINES
Sample script to import price list using interface tables QP_INTERFACE_LIST_HEADERS QP_INTERFACE_QUALIFIERS
create or replace package idc_bulk_Pricelist_pkg as
procedure IDC_BulkUpload
(p_PriceList_Name in varchar2,
p_item in varchar2,
p_description in varchar2,
p_UOM in varchar2,
p_price in VARCHAR2,
p_eff_date in VARCHAR2,
p_sumbit_import in varchar2 :='X');
END idc_bulk_Pricelist_pkg;
/
create or replace package body idc_bulk_Pricelist_pkg as
procedure IDC_BulkUpload
(p_PriceList_Name in varchar2,
p_item in varchar2,
p_description in varchar2,
p_UOM in varchar2,
p_price in VARCHAR2,
p_eff_date in VARCHAR2,
p_sumbit_import in varchar2 :='X') is
V_SEQ NUMBER;
ln_request_id number;
v_count number;
v_org_sys_head_ref VARCHAR2(100);
v_org_sys_line_ref VARCHAR2(100);
v_org_sys_pr_att VARCHAR2(100);
v_orig_system_header_PL VARCHAR2(100);
v_desc VARCHAR2(200);
v_plist_name varchar2(100);
begin
SELECT IDC_SEQ.NEXTVAL INTO V_SEQ FROM DUAL;
SELECT count(*) into v_count
FROM qp_pricing_attributes QPA, qp_list_lines QPL,qp_list_headers QPH, mtl_system_items_b mib
WHERE qpa.list_line_id= qpl.list_line_id
AND QPH.LIST_HEADER_ID = qpl.list_header_id
--AND QPL.list_header_id =245163
AND QPH.NAME=p_PriceList_Name
and to_char(mib.inventory_item_id) =qpa.product_attr_value
and mib.organization_id = 204
and mib.segment1 =p_item;
if (v_count =1 and p_PriceList_Name != 'SUBMIT_UPLOAD') then
-- update
begin
select orig_system_header_ref,description into v_orig_system_header_PL, v_desc from qp_list_headers where name =p_PriceList_Name;
SELECT qpa.orig_sys_header_ref,qpa.orig_sys_line_ref,qpa.orig_sys_pricing_attr_ref into v_org_sys_head_ref, v_org_sys_line_ref, v_org_sys_pr_att
FROM qp_pricing_attributes QPA, qp_list_lines QPL,qp_list_headers QPH, mtl_system_items_b mib
WHERE qpa.list_line_id= qpl.list_line_id
AND QPH.LIST_HEADER_ID = qpl.list_header_id
--AND QPL.list_header_id =245163
AND QPH.NAME=p_PriceList_Name
and to_char(mib.inventory_item_id) =qpa.product_attr_value
and mib.organization_id = 204
and mib.segment1 = p_item;
INSERT INTO QP_INTERFACE_LIST_HEADERS(
ORIG_SYS_HEADER_REF,
LIST_TYPE_CODE,
NAME,
DESCRIPTION,
CURRENCY_CODE,
ACTIVE_FLAG,
CURRENCY_HEADER_ID,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ROUNDING_FACTOR,
SOURCE_LANG,
LANGUAGE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
v_orig_system_header_PL, /* Unique identification of the price list in the external application from where the price list is imported. */
'PRL', /* List Type Code. PRL for standard price list */
p_PriceList_Name, /* Price List Name */
v_desc,/* Description */
'USD', /* Currency Code. The currency of the price list.*/
'Y', /* Active Flag */
86, /* Currency Header Id. Identifies Multi-Currency List associated with a Price List.Alternatively, populate CURRENCY_HEADER column with Multi-Currency List Name. */
'1996/01/01', /* Start Date Active */
NULL, /* End Date Active */
-2, /* Rounding Factor */
'US', /* Source Language */
'US', /* Language */
'UPDATE', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'I' /* Process Status Flag for transaction */
);
/* Insert Price list line(PLL) details into QP_INTERFACE_LIST_LINES table. */
INSERT INTO QP_INTERFACE_LIST_LINES (
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
LIST_LINE_TYPE_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ARITHMETIC_OPERATOR,
OPERAND,
PRIMARY_UOM_FLAG,
PRODUCT_PRECEDENCE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
v_org_sys_line_ref, /* The combination of this and ORIG_SYS_HEADER_REF is the primary key of the equivalent of the price list line table of the external system from where the price data is loaded.*/
v_org_sys_head_ref, /* orig_sys_header_ref */
'PLL', /* List Line Type Code. PLL for price list line. */
p_eff_date, /* Start date active */
NULL, /* End date active */
'UNIT_PRICE', /* Arithmetic operator */
p_price, /* operand */
'Y', /* Primary UOM Flag */
230, /* Product Precedence */
'UPDATE', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
);
/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */
INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (
ORIG_SYS_PRICING_ATTR_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTR_CODE,
PRODUCT_ATTR_VAL_DISP,
PRODUCT_UOM_CODE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(v_org_sys_pr_att, /* The combination of ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, ORIG_SYS_PRICING_ATTR_REF
is the primary key of the equivalent of the pricing attribute table of the external
system from where the price data is loaded.*/
v_org_sys_line_ref, /* orig_sys_line_ref */
v_org_sys_head_ref, /* orig_sys_header_ref */
'ITEM', /* Product Attribute Context */
'INVENTORY_ITEM_ID', /* Product Attribute Code */
p_item,--'U-Z101' , /* Product Name. Alternatively populate PRODUCT_ATTR_CODE with inventory item id of the item.*/
'Ea', /* Product UOM */
'UPDATE', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
);
COMMIT;
end;
--- update
end if;
--insert into idc_msgs values ( 'i m after 1');
if v_count =0 then
if p_PriceList_Name != 'SUBMIT_UPLOAD' then
begin
select orig_system_header_ref,description,name into v_orig_system_header_PL, v_desc,v_plist_name from qp_list_headers where name =p_PriceList_Name;
INSERT INTO QP_INTERFACE_LIST_HEADERS(
ORIG_SYS_HEADER_REF,
LIST_TYPE_CODE,
NAME,
DESCRIPTION,
CURRENCY_CODE,
ACTIVE_FLAG,
CURRENCY_HEADER_ID,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ROUNDING_FACTOR,
SOURCE_LANG,
LANGUAGE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(v_orig_system_header_PL, /* Unique identification of the price list in the external application from where the price list is imported. */
'PRL', /* List Type Code. PRL for standard price list */
p_PriceList_Name, /* Price List Name */
v_desc,/* Description */
'USD', /* Currency Code. The currency of the price list.*/
'Y', /* Active Flag */
86, /* Currency Header Id. Identifies Multi-Currency List associated with a Price List.
Alternatively, populate CURRENCY_HEADER column with Multi-Currency List Name. */
'1996/01/01', /* Start Date Active */
NULL, /* End Date Active */
-2, /* Rounding Factor */
'US', /* Source Language */
'US', /* Language */
'UPDATE', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'I' /* Process Status Flag for transaction */
);
/* Insert Price list line(PLL) details into QP_INTERFACE_LIST_LINES table. */
INSERT INTO QP_INTERFACE_LIST_LINES (
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
LIST_LINE_TYPE_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ARITHMETIC_OPERATOR,
OPERAND,
PRIMARY_UOM_FLAG,
PRODUCT_PRECEDENCE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
('CORPORATE_LINE-'||V_SEQ, /* The combination of this and ORIG_SYS_HEADER_REF is the primary key of the
equivalent of the price list line table of the external system from where
the price data is loaded.*/
v_orig_system_header_PL, /* orig_sys_header_ref */
'PLL', /* List Line Type Code. PLL for price list line. */
p_eff_date,--'02-JUN-08', /* Start date active */
NULL, /* End date active */
'UNIT_PRICE', /* Arithmetic operator */
p_price,--150, /* operand */
'Y', /* Primary UOM Flag */
230, /* Product Precedence */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
);
/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */
INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (
ORIG_SYS_PRICING_ATTR_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTR_CODE,
PRODUCT_ATTR_VAL_DISP,
PRODUCT_UOM_CODE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
('CORPORATE_PATTR'||V_SEQ, /* The combination of ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, ORIG_SYS_PRICING_ATTR_REF
is the primary key of the equivalent of the pricing attribute table of the external system from where the price data is loaded.*/
'CORPORATE_LINE-'||V_SEQ, /* orig_sys_line_ref */
v_orig_system_header_PL, /* orig_sys_header_ref */
'ITEM', /* Product Attribute Context */
'INVENTORY_ITEM_ID', /* Product Attribute Code */
p_item,--'U-Z101' , /* Product Name. Alternatively populate PRODUCT_ATTR_CODE with inventory item id of the item.*/
'Ea', /* Product UOM */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
);
end ;
END IF;
end if;
--insert into idc_msgs values ( 'i m after 0');
if P_PriceList_Name = 'SUBMIT_UPLOAD' then
begin
--insert into idc_msgs values ( 'i m inside submit upload');
fnd_global.apps_initialize(1000049 ,64438,661);
mo_global.init('QP');
ln_request_id := FND_REQUEST.SUBMIT_REQUEST
(application => 'QP', -- Application Name
program => 'QPXVBLK', -- Program
description => 'QP: Bulk Import of Price List' ,
start_time => NULL, -- START_TIME
sub_request => FALSE, -- SUB_REQUEST
argument1 => 'PRL',
argument2 => 'Corporate',
argument3 => '',
argument4 => NULL,
argument5 => 'Y',
argument6 => 1,
argument7 => 'N',
argument8 => '',
argument9 => 'Y',
argument10 => 'Y');
dbms_output.put_line('Req id ' || ln_request_id);
COMMIT;
end;
end if;
COMMIT;
end;
end idc_bulk_Pricelist_pkg;
/
select * from user_errors where lower(name)='idc_bulk_Pricelist_pkg'
/
create sequence idc_seq start with 1 increment by 1
/
Sample CSV file to be uploaded using WebADi Integrator
Price List Name,Item,description,uom,Price,effDate
IDC PriceList,U-Z101,Usage Item for Zeo Laser Printer II,Ea,333,12-NOV-2009
IDC PriceList,CM31556,Monitor - 19" Flat,Ea,135,12-NOV-2009
SUBMIT_UPLOAD,,,,,
