Create sales order using interface OE_HEADERS_IFACE_ALL ,OE_LINES_IFACE_ALL

Sample PL Sql Package for creating Order in Oracle EBS

create or replace PROCEDURE idc_create_so_from_sa_prc1(p_header_id IN NUMBER)
 IS
 --declare
 v_header_id             NUMBER;
 v_org_id                        NUMBER;
 v_order_type_id                 NUMBER;
 v_price_list_id                 NUMBER;
 v_ordered_date                  DATE;
 v_request_date                  DATE;
 v_agreement_id                  NUMBER;
 v_cust_po_number                VARCHAR2(50);
 v_sold_to_org_id                NUMBER;
 v_sold_from_org_id              NUMBER;
 v_salesrep_id                   NUMBER;
 v_transactional_curr_code       VARCHAR2(10);
 v_customer_number               VARCHAR2(30);
 v_agreement_number              NUMBER;
 v_no_lines                      NUMBER;
 v_created_by                    NUMBER;
 v_last_updated_by               NUMBER;
 var_rows                        NUMBER;
 var_loop                        NUMBER;
 var_mod                    NUMBER;
 v_req_id                        NUMBER;
 --*** parametrised cursor to select lines using header id
 CURSOR c_lines(p_header_id NUMBER) IS SELECT obla.line_id,
                       obla.org_id,
                       obla.header_id,
                       obla.line_number,
                       obla.ordered_item,
                       obla.ship_to_org_id,
                       obla.invoice_to_org_id,
                       obla.sold_to_org_id,
                        obla.inventory_item_id,
                       obla.attribute4  first_request_ship_date,
                           obla.attribute5  days_between_shipments,
                           oble.blanket_max_quantity,
                           oble.max_release_quantity,
                       obla.created_by,
                           obla.last_updated_by
                                FROM   oe_blanket_lines_all obla,
                                       oe_blanket_lines_ext oble
                                WHERE  obla.line_id = oble.line_id
                                AND    obla.header_id = p_header_id;
 tbl_line   c_lines%ROWTYPE;
 BEGIN
 SELECT oobha.header_id,
        oobha.org_id,
        oobha.order_type_id,
        oobha.order_number agreement_number,
        oobha.price_list_id,
        oobha.ordered_date,
        oobha.request_date,
        oobha.agreement_id,
        oobha.cust_po_number,
        oobha.sold_to_org_id,
        oobha.sold_from_org_id,
        oobha.salesrep_id,
        oobha.transactional_curr_code,
        cust_acct.account_number customer_number,
        oobha.created_by,
        oobha.last_updated_by
 INTO   v_header_id,
        v_org_id,
        v_order_type_id,
        v_agreement_number,
        v_price_list_id,
        v_ordered_date,
        v_request_date,
        v_agreement_id,
        v_cust_po_number,
        v_sold_to_org_id,
        v_sold_from_org_id,
        v_salesrep_id,
        v_transactional_curr_code,
        v_customer_number,
        v_created_by,
        v_last_updated_by
 FROM   hz_cust_accounts        cust_acct,
        oe_blanket_headers_all  oobha
 WHERE  oobha.sold_to_org_id = cust_acct.cust_account_id
 AND    oobha.user_status_code IN ('CREATE SCHEDULE', 'RECREATE SCHEDULE')
 --AND    oobha.order_number = 1069
 AND    oobha.header_id = p_header_id;
 SELECT COUNT(*)
 INTO   v_no_lines
 FROM   oe_blanket_lines_all
 WHERE  header_id = v_header_id
 AND    org_id = v_org_id;
 INSERT INTO OE_HEADERS_IFACE_ALL (
 order_source_id
 ,orig_sys_document_ref
 ,creation_date
 ,created_by
 ,last_update_date
 ,last_updated_by
 ,operation_code
 ,sold_to_org_id
 ,org_id
 ,order_type_id
 ,price_list_id
 ,ordered_date
 ,customer_po_number
 ,sold_from_org_id
 ,salesrep_id
 ,transactional_curr_code
 ,customer_number
 )
 VALUES (
 1023 --order_source_id (Custom Order Entry)
 ,('IDCSO2SA-'|| idc_ord_seq.NEXTVAL)--orig_sys_document_ref
 ,SYSDATE --creation_date
 ,NVL(v_created_by , -1) --created_by
 ,SYSDATE --last_update_date
 ,NVL(v_last_updated_by , -1) --last_updated_by
 ,'INSERT' --operation_code
 ,v_sold_to_org_id --sold_to_org_id
 ,v_org_id --,
 ,1437--,order_type_id (Mixed)
 ,v_price_list_id--,
 ,SYSDATE--,-ordered_date
 ,v_cust_po_number--,
 ,v_sold_from_org_id--,
 ,v_salesrep_id--,
 ,v_transactional_curr_code--,
 ,v_customer_number
 );
 IF SQL%NOTFOUND THEN
     dbms_output.put_line('Order Header Iface not updated');
 ELSIF SQL%FOUND THEN
     var_rows := SQL%ROWCOUNT;
     dbms_output.put_line('Order Header Iface updated with ' || var_rows || 'rows');
 END IF;
  var_rows :=0;
 COMMIT;
 --*** Inserting line records --
 /IF v_no_lines = 1 THEN/ 
 FOR j IN 1 .. v_no_lines LOOP
 dbms_output.put_line('ENTERED J LOOP');
 OPEN c_lines(v_header_id);
 FETCH c_lines INTO tbl_line;
 dbms_output.put_line('inside  c_lines cur');
 IF tbl_line.blanket_max_quantity <= tbl_line.max_release_quantity THEN dbms_output.put_line('inside  tbl_line.blanket_max_quantity if condition');   INSERT INTO OE_LINES_IFACE_ALL(   order_source_id,   orig_sys_document_ref,   orig_sys_line_ref,   org_id,   inventory_item_id,   inventory_item,   ordered_quantity,   sold_to_org_id,   ship_to_org_id,   created_by,   creation_date,   last_updated_by,   last_update_date,   operation_code,   request_date   )   VALUES(   1023 --order_source_id (Custom Order Entry)   ,('IDCSO2SA-'|| idc_ord_seq.CURRVAL) --orig_sys_document_ref,   ,idc_ordline_seq.NEXTVAL--orig_sys_line_ref,   ,tbl_line.org_id--org_id,   ,tbl_line.inventory_item_id--inventory_item_id,   ,tbl_line.ordered_item--inventory_item,   ,tbl_line.max_release_quantity--ordered_quantity,   ,tbl_line.sold_to_org_id--sold_to_org_id,   ,tbl_line.ship_to_org_id--ship_to_org_id,   ,tbl_line.created_by--created_by,   ,SYSDATE   ,tbl_line.last_updated_by--last_updated_by,   ,SYSDATE   ,'INSERT'--operation_code,   --,TO_DATE(tbl_line.first_request_ship_date,'DD-MON-RRRR')--request_date   ,TO_DATE(tbl_line.first_request_ship_date,'RRRR/MM/DD HH24:MI:SS')   );
 IF SQL%NOTFOUND THEN
     dbms_output.put_line('Line Iface not updated as max_qty <= max_release_qty');
 ELSIF SQL%FOUND THEN
     var_rows := SQL%ROWCOUNT;
     dbms_output.put_line('Line Iface updated with ' || var_rows || 'rows as max_qty <= max_release_qty');
 END IF;
  var_rows :=0;
 COMMIT;
 ELSIF tbl_line.blanket_max_quantity > tbl_line.max_release_quantity THEN dbms_output.put_line('inside  elsif condition');            var_mod := MOD(tbl_line.blanket_max_quantity,tbl_line.max_release_quantity);            IF  var_mod = 0 THEN            dbms_output.put_line('inside var_mod = 0 if condition');                var_loop := (tbl_line.blanket_max_quantity/tbl_line.max_release_quantity) ;      FOR i IN 1 .. var_loop LOOP            dbms_output.put_line('inside loop of var_mod = 0 if condition');             INSERT INTO OE_LINES_IFACE_ALL(              order_source_id,              orig_sys_document_ref,              orig_sys_line_ref,              org_id,              inventory_item_id,              inventory_item,              ordered_quantity,              sold_to_org_id,              ship_to_org_id,              created_by,              creation_date,              last_updated_by,              last_update_date,              operation_code,              request_date              )              VALUES(              1023 --order_source_id (Custom Order Entry)              ,('IDCSO2SA-'|| idc_ord_seq.CURRVAL) --orig_sys_document_ref,              ,idc_ordline_seq.NEXTVAL--orig_sys_line_ref,              ,tbl_line.org_id--org_id,              ,tbl_line.inventory_item_id--inventory_item_id,              ,tbl_line.ordered_item--inventory_item,              ,tbl_line.max_release_quantity--ordered_quantity,              ,tbl_line.sold_to_org_id--sold_to_org_id,              ,tbl_line.ship_to_org_id--ship_to_org_id,              ,tbl_line.created_by--created_by,              ,SYSDATE              ,tbl_line.last_updated_by--last_updated_by,              ,SYSDATE              ,'INSERT'--operation_code,              ,TO_DATE(tbl_line.first_request_ship_date,'RRRR/MM/DD HH24:MI:SS') + (tbl_line.days_between_shipments*i)            );            IF SQL%NOTFOUND THEN            dbms_output.put_line('Line Iface not updated as max_qty > max_release_qty (1)');        ELSIF SQL%FOUND THEN            var_rows := SQL%ROWCOUNT;            dbms_output.put_line('Line Iface updated with ' || var_rows || 'rows as max_qty > max_release_qty (1)');        END IF;         var_rows :=0;            COMMIT;      END LOOP;            ELSE                dbms_output.put_line('inside  var_mod = 0 else condition');                var_loop := ((tbl_line.blanket_max_quantity-var_mod)/tbl_line.max_release_quantity);                               INSERT INTO OE_LINES_IFACE_ALL(                      order_source_id,                      orig_sys_document_ref,                      orig_sys_line_ref,                      org_id,                      inventory_item_id,                      inventory_item,                      ordered_quantity,                      sold_to_org_id,                      ship_to_org_id,                      created_by,                      creation_date,                      last_updated_by,                      last_update_date,                      operation_code,                      request_date                      )                      VALUES(                      1023 --order_source_id (Custom Order Entry)                      ,('IDCSO2SA-'|| idc_ord_seq.CURRVAL) --orig_sys_document_ref,                      ,idc_ordline_seq.NEXTVAL--orig_sys_line_ref,                      ,tbl_line.org_id--org_id,                      ,tbl_line.inventory_item_id--inventory_item_id,                      ,tbl_line.ordered_item--inventory_item,                      ,var_mod--ordered_quantity,                      ,tbl_line.sold_to_org_id--sold_to_org_id,                      ,tbl_line.ship_to_org_id--ship_to_org_id,                      ,tbl_line.created_by--created_by,                      ,SYSDATE                      ,tbl_line.last_updated_by--last_updated_by,                      ,SYSDATE                      ,'INSERT'--operation_code,                     -- ,TO_DATE(tbl_line.first_request_ship_date,'DD-MON-RRRR') --request_date                 ,TO_DATE(tbl_line.first_request_ship_date,'RRRR/MM/DD HH24:MI:SS')                      );            IF SQL%NOTFOUND THEN            dbms_output.put_line('Line Iface not updated as max_qty > max_release_qty (1)');        ELSIF SQL%FOUND THEN            var_rows := SQL%ROWCOUNT;            dbms_output.put_line('Line Iface updated with ' || var_rows || 'rows as max_qty > max_release_qty (1)');        END IF;         var_rows :=0;            COMMIT;       FOR i IN 1 .. var_loop LOOP       dbms_output.put_line('inside loop of var_mod <> 0 condition');             INSERT INTO OE_LINES_IFACE_ALL(              order_source_id,              orig_sys_document_ref,              orig_sys_line_ref,              org_id,              inventory_item_id,              inventory_item,              ordered_quantity,              sold_to_org_id,              ship_to_org_id,              created_by,              creation_date,              last_updated_by,              last_update_date,              operation_code,              request_date              )              VALUES(              1023 --order_source_id (Custom Order Entry)              ,('IDCSO2SA-'|| idc_ord_seq.CURRVAL) --orig_sys_document_ref,              ,idc_ordline_seq.NEXTVAL--orig_sys_line_ref,              ,tbl_line.org_id--org_id,              ,tbl_line.inventory_item_id--inventory_item_id,              ,tbl_line.ordered_item--inventory_item,              ,tbl_line.max_release_quantity--ordered_quantity,              ,tbl_line.sold_to_org_id--sold_to_org_id,              ,tbl_line.ship_to_org_id--ship_to_org_id,              ,tbl_line.created_by--created_by,              ,SYSDATE              ,tbl_line.last_updated_by--last_updated_by,              ,SYSDATE              ,'INSERT'--operation_code,             , TO_DATE(tbl_line.first_request_ship_date,'RRRR/MM/DD HH24:MI:SS') + (tbl_line.days_between_shipments*i)              );            IF SQL%NOTFOUND THEN            dbms_output.put_line('Line Iface not updated as max_qty > max_release_qty (1)');        ELSIF SQL%FOUND THEN            var_rows := SQL%ROWCOUNT;            dbms_output.put_line('Line Iface updated with ' || var_rows || 'rows as max_qty > max_release_qty (1)');        END IF;         var_rows :=0;            COMMIT;      END LOOP;   END IF; END IF; CLOSE c_lines;
 /END IF;/ END LOOP;
 commit;
 --** submit order import program
 fnd_global.apps_initialize(1318,21623,660,0);
 v_req_id:=FND_REQUEST.SUBMIT_REQUEST(
 application => 'ONT',
 program => 'OEOIMP',
 description => 'Order Import Concurrent Program',
 start_time => NULL,
 sub_request => FALSE,
 argument1 => 204 ,--Operating Unit
 argument2 => 1023, -- Order Source
 argument3 => NULL,--Order Reference
 argument4 => NULL,
 argument5 => 'N', -- Validate Only
 argument6 => 1,
 argument7 => 4, --Instances
 argument8 => 1004,--
 argument9 => NULL,
 argument10 => NULL,
 argument11 => 'Y', -- Validate Descriptive Flexfield,
 argument12 => 'Y',--Trim  Trailing Blanks
 argument13 => 'Y',
 argument14 => 204,
 argument15 => 'Y'
 );
 dbms_output.put_line('Request ID ' || v_req_id || ' has been submitted');
 COMMIT;
 END;
 select * from user_errors where lower(name)='idc_create_so_from_sa_prc1'
 DECLARE
 BEGIN
 idc_create_so_from_sa_prc1(186710);
 END;

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
      Shopping cart