Bulk Price List Upload in Oracle EBS

pricelist-import-oracle-ebs

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,,,,,

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Cloud Technologies Blog
Logo
Enable registration in settings - general
Compare items
  • Total (0)
Compare
0
Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Shopping cart