--以下脚本于20171230在tony上成功实现
--费用类采购订单退货成功_20171230_只需要输入一个采购订单号即可
--初始化
/*
begin
fnd_global.apps_initialize(user_id => 1110,
resp_id => 50597,
resp_appl_id => 20003);
mo_global.init('M');
end;
*/
declare
g_pkg_name CONSTANT VARCHAR2(30) := 'CUX_PO_WPR_HEADER_PUB';
g_error_count NUMBER := 0;
g_unexp_error_count NUMBER := 0;
g_created_by NUMBER := fnd_global.user_id;
g_last_updated_by NUMBER := fnd_global.user_id;
g_last_update_login NUMBER := fnd_global.login_id;
g_prog_appl_id NUMBER := fnd_global.prog_appl_id;
g_prog_id NUMBER := fnd_global.conc_program_id;
l_api_name CONSTANT VARCHAR2(30) := 'rcv_txn_process';
l_savepoint_name VARCHAR2(30) := 'rcv_txn_process_001';
CURSOR csr_line_dis(p_po_num varchar2) IS
SELECT *
FROM (SELECT h1.po_header_id,
h1.revision_num,
h2.po_line_id,
h3.line_location_id,
h4.po_distribution_id,
h2.unit_price,
h1.currency_code,
h1.vendor_id,
h1.vendor_site_id,
h1.org_id,
(select fsp.inventory_organization_id
from financials_system_params_all fsp
where fsp.org_id = h1.org_id) inventory_organization_id,
----
t1.shipment_header_id,
t1.shipment_line_id,
t1.transaction_id AS transaction_id_deliver,
t1.parent_transaction_id AS transaction_id_receive,
t1.quantity -
(SELECT nvl(SUM(t2.quantity), 0)
FROM rcv_transactions t2
WHERE t2.parent_transaction_id =
t1.parent_transaction_id
AND t2.transaction_type = 'RETURN TO VENDOR') AS quantity,
t1.project_id,
t1.task_id,
h2.category_id,
----
h3.ship_to_location_id,
h2.item_description,
h2.unit_meas_lookup_code
FROM po_headers_all h1,
po_lines_all h2,
po_line_locations_all h3,
po_distributions_all h4,
rcv_transactions t1
WHERE 1 = 1
----
AND t1.transaction_type = 'DELIVER'
----and t1.interface_source_code = 'WORK_PROGRESS'
----
AND t1.po_header_id = h4.po_header_id
AND t1.po_line_id = h4.po_line_id
AND t1.po_line