Script to OE_ORDER_PUB.PROCESS_ORDER ( Sample Script Create Sales Order )
Script
PROCEDURE xx_bdg_so_interface (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_org_id NUMBER
)
IS
v_api_version_number NUMBER := 1;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
x_msg_dat VARCHAR2 (4000);
l_out_index NUMBER;
l_price_list_id NUMBER;
l_customer_id NUMBER;
l_salesrep_id NUMBER;
l_err_flag VARCHAR2 (1) := ‘N’;
l_count NUMBER := 0;
l_batch_no NUMBER;
l_ship_to_location NUMBER;
l_bill_to_location NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_item_identifier_type hz_cust_site_uses_all.item_cross_ref_pref%TYPE;
g_debug_point VARCHAR2 (100);
g_debug VARCHAR2 (5)
:= fnd_profile.VALUE (‘AFLOG_LEVEL’);
— IN Variables —
v_header_rec oe_order_pub.header_rec_type;
v_line_tbl oe_order_pub.line_tbl_type;
v_action_request_tbl oe_order_pub.request_tbl_type;
v_line_adj_tbl oe_order_pub.line_adj_tbl_type;
v_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
— OUT Variables —
v_header_rec_out oe_order_pub.header_rec_type;
v_header_val_rec_out oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out oe_order_pub.line_tbl_type;
v_line_val_tbl_out oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out oe_order_pub.request_tbl_type;
lv_process_count1 number:=0;
lv_process_count2 number:=0;
CURSOR c1 (
P_order_type_id number,
P_price_list_id number,
P_productgroup varchar2,
P_customer_id number,
P_depot_id number,
P_SALES_PERSON_ID number,
P_approval_ref_id varchar2
)
IS
SELECT stg.inventory_item_id item_id,
stg.organization_id depot_id, org.organization_id,
msib.segment1 item_no, msib.description item_description,
org.organization_name org_name,
org.organization_code org_code, stg.quantity trans_qty,
stg.uom trans_uom, bill_to_site_id, ship_to_site_id, uom,
product_group productgroup, customer_id, price_list_id,
salesrep_id sales_person_id, stg.stg_id, lot_number,activity,narration,NC_TRX_QTY,NC_PROPOSAL_AMT
FROM apps.xxx_bdg_so_stg stg,
apps.org_organization_definitions org,
apps.mtl_system_items_b msib
WHERE stg.inventory_item_id = msib.inventory_item_id
AND stg.organization_id = msib.organization_id
AND stg.organization_id = org.organization_id
AND status IN (‘O’)
AND order_type_id= P_order_type_id
AND price_list_id = P_price_list_id
AND product_group = P_productgroup
AND customer_id = P_customer_id
AND stg.organization_id= p_depot_id
AND SALESREP_ID= P_SALES_PERSON_ID
AND approval_ref_no=P_approval_ref_id
ORDER BY stg.stg_id DESC;
CURSOR c2
IS
SELECT order_type_id, price_list_id, productgroup, customer_id,
depot_id, approval_ref_id,
sales_person_id
FROM xapex.xxx_bgt_cdt_dbt_dtls_tb stg,
apps.org_organization_definitions org,
apps.mtl_system_items_b msib
WHERE stg.item_id = msib.inventory_item_id
AND stg.depot_id = msib.organization_id
AND stg.depot_id = org.organization_id
AND upload_type = ‘NC’
AND validated_flag = ‘VS’
AND stg.depot_id = p_org_id
GROUP BY order_type_id,
price_list_id,
productgroup,
customer_id,
depot_id,SALES_PERSON_ID,
approval_ref_id;
BEGIN
DBMS_OUTPUT.put_line (‘Starting of script’);
— Setting the Enviroment —
mo_global.init (‘ONT’);
fnd_file.put_line (fnd_file.LOG, ‘Start Sales Order script’);
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘XAVIER’;
SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘India Local Order Management’
AND NVL (TRUNC (end_date), TRUNC (SYSDATE)) >= TRUNC (SYSDATE);
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_appl_id
);
mo_global.set_policy_context (‘S’, 82);
DBMS_OUTPUT.put_line (‘Error Flag:’ || l_err_flag);
apps.fnd_request.set_org_id (’82’);
FOR c2_rec IN c2
LOOP
update xapex.xxx_bgt_cdt_dbt_dtls_tb stg
set validated_flag = ‘Order START’
WHERE 1=1
AND upload_type = ‘NC’
AND validated_flag = ‘VS’
AND stg.depot_id = p_org_id;
lv_process_count2:=lv_process_count2+1;
BEGIN
SELECT
hcsua.site_use_id
INTO
l_ship_to_location
FROM HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS_ALL HCAA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSUA
WHERE
HP.PARTY_ID = HPS.PARTY_ID
and hcaa.cust_account_id = c2_rec.customer_id
AND HP.PARTY_ID = HCAA.PARTY_ID
AND HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND HCSUA.SITE_USE_CODE = ‘SHIP_TO’
AND HCSUA.LOCATION = c2_rec.productgroup;
EXCEPTION
WHEN OTHERS THEN
l_err_flag := ‘E’;
dbms_output.put_line(‘Ship To Error. ‘||SQLERRM);
END;
BEGIN
SELECT hcsua.site_use_id
INTO l_bill_to_location
FROM HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS_ALL HCAA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSUA
WHERE HP.PARTY_ID = HPS.PARTY_ID
and hcaa.cust_account_id =c2_rec.customer_id
AND HP.PARTY_ID = HCAA.PARTY_ID
AND HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND HCSUA.SITE_USE_CODE = ‘BILL_TO’
AND HCSUA.LOCATION = c2_rec.productgroup;
EXCEPTION
WHEN OTHERS THEN
l_err_flag := ‘E’;
dbms_output.put_line(‘Ship To Error. ‘||SQLERRM);
END;
DBMS_OUTPUT.put_line (‘Order Type ID ‘ || v_header_rec.order_type_id);
fnd_file.put_line (fnd_file.LOG,
‘Start Outer Loop Sales Order script’
);
v_header_rec := oe_order_pub.g_miss_header_rec;
v_header_rec.operation := oe_globals.g_opr_create;
v_header_rec.order_type_id := c2_rec.order_type_id;
v_header_rec.sold_to_org_id := c2_rec.customer_id;
v_header_rec.ship_to_org_id := l_ship_to_location;
v_header_rec.invoice_to_org_id := l_bill_to_location;
v_header_rec.order_source_id := 0;
v_header_rec.booked_flag := ‘N’;
v_header_rec.price_list_id := c2_rec.price_list_id;
v_header_rec.pricing_date := SYSDATE;
v_header_rec.flow_status_code := ‘ENTERED’;
v_header_rec.salesrep_id := c2_rec.sales_person_id;
v_header_rec.transactional_curr_code := ‘INR’;
v_header_rec.sold_from_org_id := c2_rec.depot_id;
v_header_rec.ship_from_org_id := c2_rec.depot_id;
v_line_tbl.DELETE;
l_count := 0;
lv_process_count1:=0;
FOR c1_rec IN c1 (
c2_rec.order_type_id,
c2_rec.price_list_id,
c2_rec.productgroup,
c2_rec.customer_id,
c2_rec.depot_id,
c2_rec.SALES_PERSON_ID,
c2_rec.approval_ref_id
)
LOOP
if lv_process_count1 = 0 then
update apps.xxx_bdg_so_stg stg
set status=’OSTART’
where order_type_id= c2_rec.order_type_id
AND price_list_id = c2_rec.price_list_id
AND product_group = c2_rec.productgroup
AND customer_id = c2_rec.customer_id
AND stg.organization_id= c2_rec.depot_id
AND SALESREP_ID= c2_rec.SALES_PERSON_ID
AND approval_ref_no=c2_rec.approval_ref_id
and status=’O’;
end if;
DBMS_OUTPUT.put_line (‘Inside Loop’);
fnd_file.put_line (fnd_file.LOG,
‘Start Inner Loop Sales Order script’
);
l_count := l_count + 1;
v_action_request_tbl (l_count) := oe_order_pub.g_miss_request_rec;
v_line_tbl (l_count) := oe_order_pub.g_miss_line_rec;
v_line_tbl (l_count).operation := oe_globals.g_opr_create;
v_line_tbl (l_count).inventory_item_id := c1_rec.item_id;
v_line_tbl (l_count).ship_from_org_id := c1_rec.organization_id;
v_line_tbl (l_count).order_quantity_uom := c1_rec.uom;
v_line_tbl (l_count).ordered_quantity := c1_rec.trans_qty;
v_line_tbl (l_count).price_list_id := c1_rec.price_list_id;
v_line_tbl (l_count).ordered_item := c1_rec.item_no;
v_line_tbl (l_count).attribute10 := c1_rec.stg_id;
v_line_tbl (l_count).item_identifier_type := c1_rec.productgroup;
DBMS_OUTPUT.put_line ( l_ship_to_location
|| ‘ ‘
|| l_bill_to_location
|| ‘ ‘
— || l_batch_no
|| ‘ ‘
–|| c1_rec.item_id
|| ‘ ‘
|| l_customer_id
|| ‘ ‘
|| ‘c1_rec.lot_number’
|| ‘ ‘
–|| c1_rec.organization_id
|| ‘ ‘
|| l_price_list_id
|| ‘ ‘
|| l_salesrep_id
|| ‘ ‘
|| v_header_rec.sold_from_org_id
|| ‘ ‘
|| v_header_rec.ship_from_org_id
|| ‘ ‘
|| v_line_tbl (l_count).ordered_quantity
|| ‘ ‘
|| v_line_tbl (l_count).order_quantity_uom
);
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line (‘Starting of API’);
fnd_file.put_line (fnd_file.LOG, ‘Start API Sales Order script’);
— Calling the API to create an Order —
BEGIN
oe_order_pub.process_order
(p_api_version_number => v_api_version_number,
p_header_rec => v_header_rec,
p_line_tbl => v_line_tbl,
p_line_price_att_tbl => v_line_price_att_tbl,
p_action_request_tbl => v_action_request_tbl,
p_line_adj_tbl => v_line_adj_tbl
— OUT variables
,
x_header_rec => v_header_rec_out,
x_header_val_rec => v_header_val_rec_out,
x_header_adj_tbl => v_header_adj_tbl_out,
x_header_adj_val_tbl => v_header_adj_val_tbl_out,
x_header_price_att_tbl => v_header_price_att_tbl_out,
x_header_adj_att_tbl => v_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => v_header_adj_assoc_tbl_out,
x_header_scredit_tbl => v_header_scredit_tbl_out,
x_header_scredit_val_tbl => v_header_scredit_val_tbl_out,
x_line_tbl => v_line_tbl_out,
x_line_val_tbl => v_line_val_tbl_out,
x_line_adj_tbl => v_line_adj_tbl_out,
x_line_adj_val_tbl => v_line_adj_val_tbl_out,
x_line_price_att_tbl => v_line_price_att_tbl_out,
x_line_adj_att_tbl => v_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => v_line_adj_assoc_tbl_out,
x_line_scredit_tbl => v_line_scredit_tbl_out,
x_line_scredit_val_tbl => v_line_scredit_val_tbl_out,
x_lot_serial_tbl => v_lot_serial_tbl_out,
x_lot_serial_val_tbl => v_lot_serial_val_tbl_out,
x_action_request_tbl => v_action_request_tbl_out,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘API raised error ‘ || SQLERRM);
END;
fnd_file.put_line (fnd_file.LOG, ‘Complete Sales Order script’);
DBMS_OUTPUT.put_line (‘Completion of API’);
DBMS_OUTPUT.put_line (‘Return Status: ‘ || v_return_status);
DBMS_OUTPUT.put_line (v_line_tbl_out.COUNT);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ( ‘Order Import Success : ‘
|| v_header_rec_out.header_id
|| ‘ ‘
|| v_header_rec_out.order_number
);
UPDATE apps.xxx_bdg_so_stg stg
SET interface_date = SYSDATE,
interface_status = fnd_api.g_ret_sts_success,
status = ‘SO CREATED’,
order_number = v_header_rec_out.order_number,
so_creation_date = SYSDATE,
so_header_id = v_header_rec_out.header_id
WHERE pre_stage_trx_no in ( select PRE_STAGE_TRX_NO from apps.xxx_bdg_so_stg
where order_type_id= c2_rec.order_type_id
AND price_list_id = c2_rec.price_list_id
AND product_group = c2_rec.productgroup
AND customer_id = c2_rec.customer_id
— bill_to_site_id,
AND stg.organization_id= c2_rec.depot_id
AND SALESREP_ID= c2_rec.SALES_PERSON_ID
AND approval_ref_no=c2_rec.approval_ref_id
and status=’OSTART’
);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ( ‘Order Import failed:’
|| v_msg_data
|| ‘-‘
|| v_msg_count
);
ROLLBACK;
IF v_msg_count = 1
THEN
DBMS_OUTPUT.put_line (‘Count 1: ‘ || v_msg_data);
ELSE
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data :=
oe_msg_pub.get (p_msg_index => i,
p_encoded => ‘F’);
DBMS_OUTPUT.put_line (v_msg_data);
END LOOP;
END IF;
UPDATE apps.xxx_bdg_so_stg
SET interface_status = v_return_status,
error_message = v_msg_data
WHERE pre_stage_trx_no in ( select PRE_STAGE_TRX_NO from apps.xxx_bdg_so_stg stg
where order_type_id= c2_rec.order_type_id
AND price_list_id = c2_rec.price_list_id
AND product_group = c2_rec.productgroup
AND customer_id = c2_rec.customer_id
AND stg.organization_id= c2_rec.depot_id
AND SALESREP_ID= c2_rec.SALES_PERSON_ID
AND approval_ref_no=c2_rec.approval_ref_id
and status=’OSTART’
);
COMMIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Unexceped Error:’ || SQLERRM);
END;
