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;

wpsadmin
We will be happy to hear your thoughts

Leave a reply

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
Cloud Technologies Blog
Logo
Compare items
  • Total (0)
Compare
0
Shopping cart