--以下脚本于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) := NULL;
CURSOR csr_poh IS
SELECT poh.vendor_id,
poh.vendor_site_id,
poh.po_header_id,
poh.org_id,
fsp.inventory_organization_id,
poh.segment1 po_num
FROM po_headers_all poh,
--po_headers_interface pohi,
financials_system_params_all fsp
WHERE 1 = 1
----begin 劳务采购订单如果接收后再退货,就不会自动再接收了,如果需要再接收,只能手工来接收。
AND poh.po_header_id NOT IN
(SELECT t.po_header_id FROM rcv_transactions t)
----end
AND poh.org_id = fsp.org_id
AND nvl(poh.approved_flag, 'N') = 'Y'
AND nvl(poh.cancel_flag, 'N') = 'N' --added by liangjie.yuan 20140120 [32289]
AND decode(nvl(poh.closed_code, 'OPEN'), 'OPEN', 'N', 'Y') = 'N'
--AND po_headers_sv3.get_po_status(poh.po_header_id) = 'Approved'
AND EXISTS
(SELECT 1
FROM po_line_locations_all pll
WHERE nvl(pll.quantity, 0) - nvl(pll.quantity_received, 0) -
nvl(pll.quantity_cancelled, 0) > 0
AND pll.po_header_id = poh.po_header_id)
--AND poh.po_header_id = pohi.po_header_id
--AND pohi.batch_id = p_batch_id;
AND poh.org_id = 82
and poh.segment1 = '1021';
CURSOR csr_po(p_po_header_id IN NUMBER) IS
SELECT pd.po_header_id,
pd.po_line_id,
pd.line_location_id,
pd.po_distribution_id,
pll.ship_to_location_id,
pl.item_description,
pll.quantity - nvl(pll.quantity_received, 0) -
nvl(pll.quantity_cancelled, 0) quantity,
pl.unit_meas_lookup_code
FROM po_lines_all pl,
po_line_locations_all pll,
po_distributions_all pd
WHERE pd.line_location_id = pll.line_location_id
AND pll.po_line_id = pl.po_line_id
AND pl