Home » Create sales order using interface OE_HEADERS_IFACE_ALL ,OE_LINES_IFACE_ALL
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;
