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

We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Logo
      Compare items
      • Total (0)
      Compare
      0
      Shopping cart