Sales Order Import Sample

CREATE OR REPLACE PROCEDURE xx_sales_import_Api IS

l_process_flag VARCHAR2(1);

l_ERROR_MESSAGE VARCHAR2(60);

l_COUNT NUMBER number(2);

l_cust_no               hz_cust_accounts_all.account_number%TYPE;

l_rep_id                hz_cust_accounts_all.primary_salesrep_id%TYPE;

l_sold_to_org_id        hz_cust_accounts_all.cust_account_id%TYPE;

l_inv_to_org_id         hz_cust_site_uses_all.site_use_id%TYPE;

l_ship_to_org_id        hz_cust_site_uses_all.site_use_id%TYPE;

l_item_id               mtl_system_items_b.inventory_item_id%TYPE;

l_uom                   mtl_system_items_b.primary_uom_code%TYPE;

   

CURSOR c_header IS SELECT * FROM xx_so_headers_stg where process_flag = 'Y';

CURSOR c_line IS 

SELECT  l.*

FROM xx_so_header_stg h, xx_so_lines_stg l

where process_flag = 'Y' 

AND l.ref_number = h.ref_number;   

 BEGIN 

    FOR h1 IN c_header

  LOOP

    l_error_message := null;

l_count :=0;

      BEGIN

       SELECT ACCOUNT_NUMBER into l_cust_no

       FROM HZ_CUST_ACCOUNTS

       WHERE ACCOUNT_NUMBER = l_cust_no;

      EXCEPTION

      WHEN OTHERS THEN

      L_PROCESS_FLAG := 'N';

      L_ERROR_MESSAGE := 'INVALID ACCOUNT' ;

     END; 

   

     IF l_process_flag <> 'N' THEN

 begin

 savepoint a;

     INSERT INTO OE_HEADERS_IFACE_ALL (order_source_id,

     orig_sys_document_ref,

     orig_sys_line_ref,

     org_id,

     inventory_item,

     ordered_quantity,

     sold_to_org_id,

     unit_list_price,

     unit_selling_price,

     calculate_price_flag,

     attribute2,

     created_by,

     creation_date,

     last_updated_by,

     last_update_date,

     operation_code) 

     VALUES  (1043,

              100,

              100,

               87,

'M049',

1,

4252,

87,

87,

'N',

'',

0,

'24-MAR-2015',

0,

'24-MAR-2015',

'INSERT');

FOR l1 IN c_lines LOOP

BEGIN

SELECT  inventory_item_id item_id

,primary_uom_code uom 

INTO    l_item_id

,l_uom

FROM   mtl_system_items_b 

WHERE  segment1 = l1.inventory_item      

AND     organization_id = 41;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_process_flag := 'Y';

l_error_message := l_error_message||'Item is not existing';

END;

IF l_process_flag <> 'Y' THEN

        begin

INSERT INTO OE_LINES_IFACE_ALL (order_source_id,

orig_sys_document_ref,

orig_sys_line_ref,

org_id,

inventory_item,

ordered_quantity,

sold_to_org_id,

unit_list_price,

unit_selling_price,

calculate_price_flag,

attribute2,

created_by,

creation_date,

last_updated_by,

last_update_date,

operation_code) 

VALUES  (1043,

100,

100,

87,

'M049',

1,

4252,

87,

87,

'N',

'',

0,

'19-MAR-2015',

0,

'19-MAR-2015',

'INSERT');

UPDATE xx_so_line_stg

SET process_flag = 'N'

WHERE line_ref_num = l1.line_ref_num 

AND process_flag = 'Y';

EXCEPTION

WHEN OTHERS THEN

ROLLBACK TO a;

l_error_message := sqlerrm;

    end;

    end if;

    end loop;

    end if;

end loop;

 end;

end  xx_sales_import_Api;

/