ORACLEEBSASN入库事务处理开发接口案例

篇首语：本文由编程笔记#小编为大家整理，主要介绍了ORACLE EBS ASN入库事务处理开发接口案例相关的知识，希望对你有一定的参考价值。

DECLARE
CURSOR cur_rcvs IS
SELECT rt.shipment_header_id,
rsh.receipt_source_code,
rt.vendor_id,
rt.vendor_site_id,
rt.source_document_code,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
rt.po_release_id,
rt.shipment_line_id,
rt.transaction_id,
rt.po_distribution_id,
rsl.category_id,
rsl.item_id,
rt.quantity,
rt.unit_of_measure,
rt.routing_header_id,
rt.lpn_id,
rt.location_id
FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions rt
WHERE 1 &＃61; 1
AND rsh.shipment_header_id &＃61; rsl.shipment_header_id
AND rsh.shipment_header_id &＃61; rt.shipment_header_id
AND rsh.ship_to_org_id &＃61; rt.organization_id
AND rsl.shipment_line_id &＃61; rt.shipment_line_id
AND rt.transaction_type &＃61; &＃39;RECEIVE&＃39;
AND NOT EXISTS (SELECT &＃39;X&＃39;
FROM rcv_transactions rt2
WHERE 1 &＃61; 1
AND rsh.shipment_header_id &＃61; rt2.shipment_header_id
AND rsl.shipment_line_id &＃61; rt2.shipment_line_id
AND rt2.transaction_type &＃61; &＃39;DELIVER&＃39;)
AND rt.transaction_id &＃61; &transaction_id;
l_group_id NUMBER;
l_iface_rcv_rec po.rcv_transactions_interface%ROWTYPE;
l_request_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
BEGIN
SELECT po.rcv_interface_groups_s.nextval INTO l_group_id FROM dual;
FOR rec_rcvs IN cur_rcvs LOOP

-- insert rcv txn interface
l_iface_rcv_rec.last_update_date :&＃61; SYSDATE;
l_iface_rcv_rec.last_updated_by :&＃61; 2414;
l_iface_rcv_rec.creation_date :&＃61; SYSDATE;
l_iface_rcv_rec.created_by :&＃61; 2414;
l_iface_rcv_rec.last_update_login :&＃61; -1;

SELECT rcv_transactions_interface_s.nextval
INTO l_iface_rcv_rec.interface_transaction_id
FROM dual;

l_iface_rcv_rec.group_id :&＃61; l_group_id;

l_iface_rcv_rec.shipment_header_id :&＃61; rec_rcvs.shipment_header_id; --Shipment Header
l_iface_rcv_rec.processing_status_code :&＃61; &＃39;PENDING&＃39;;
l_iface_rcv_rec.transaction_status_code :&＃61; &＃39;PENDING&＃39;;
l_iface_rcv_rec.processing_mode_code :&＃61; &＃39;BATCH&＃39;;
l_iface_rcv_rec.validation_flag :&＃61; &＃39;Y&＃39;;

l_iface_rcv_rec.receipt_source_code :&＃61; rec_rcvs.receipt_source_code;
l_iface_rcv_rec.vendor_id :&＃61; rec_rcvs.vendor_id;
l_iface_rcv_rec.vendor_site_id :&＃61; rec_rcvs.vendor_site_id; --Optional

l_iface_rcv_rec.source_document_code :&＃61; rec_rcvs.source_document_code;
l_iface_rcv_rec.po_header_id :&＃61; rec_rcvs.po_header_id;
l_iface_rcv_rec.po_line_id :&＃61; rec_rcvs.po_line_id;
l_iface_rcv_rec.po_line_location_id :&＃61; rec_rcvs.po_line_location_id;

l_iface_rcv_rec.transaction_type :&＃61; &＃39;DELIVER&＃39;;
l_iface_rcv_rec.auto_transact_code :&＃61; NULL;
l_iface_rcv_rec.destination_type_code :&＃61; &＃39;INVENTORY&＃39;;
l_iface_rcv_rec.shipment_line_id :&＃61; rec_rcvs.shipment_line_id;
l_iface_rcv_rec.parent_transaction_id :&＃61; rec_rcvs.transaction_id;
l_iface_rcv_rec.po_distribution_id :&＃61; rec_rcvs.po_distribution_id;

l_iface_rcv_rec.category_id :&＃61; rec_rcvs.category_id;
l_iface_rcv_rec.item_id :&＃61; rec_rcvs.item_id;
l_iface_rcv_rec.transaction_date :&＃61; SYSDATE;
l_iface_rcv_rec.quantity :&＃61; rec_rcvs.quantity;
l_iface_rcv_rec.unit_of_measure :&＃61; rec_rcvs.unit_of_measure; --Not Code
l_iface_rcv_rec.routing_header_id :&＃61; rec_rcvs.routing_header_id;
l_iface_rcv_rec.lpn_id :&＃61; rec_rcvs.lpn_id;
l_iface_rcv_rec.subinventory :&＃61; &＃39;RPK&＃39;;
l_iface_rcv_rec.locator :&＃61; &＃39;RPK-RPK-RPK&＃39;;

l_iface_rcv_rec.to_organization_id :&＃61; 709;
l_iface_rcv_rec.location_id :&＃61; rec_rcvs.location_id;

l_iface_rcv_rec.interface_source_code :&＃61; &＃39;BATCH RCV&＃39;;

INSERT INTO po.rcv_transactions_interface VALUES l_iface_rcv_rec;

dbms_output.put_line(&＃39;l_group_id:&＃39; || l_group_id);
END LOOP;
apps.fnd_global.apps_initialize(user_id &＃61;> 2414, resp_id &＃61;> 51173, resp_appl_id &＃61;> 20003);
-- submit RVCTP request, and wait for complete the request.
wtwms_004extb_rvctp_prc(p_api_version &＃61;> 1.0,
p_init_msg_list &＃61;> fnd_api.g_true,
x_return_status &＃61;> l_return_status,
x_msg_count &＃61;> l_msg_count,
x_msg_data &＃61;> l_msg_data,
x_request_id &＃61;> l_request_id,
p_group_id &＃61;> l_group_id,
p_interval &＃61;> 10,
p_wait_for_request &＃61;> &＃39;Y&＃39;);
dbms_output.put_line(&＃39;l_request_id:&＃39; || l_request_id || &＃39;,l_return_status:&＃39; || l_return_status ||
&＃39;,l_msg_data&＃39; || l_msg_data);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(&＃39;Exception:&＃39; || SQLCODE || SQLERRM);

END;