EBS开发_采购订单退货_只需传入poheaderid即可

最新推荐文章于 2024-08-31 10:53:40 发布

蜗牛先生快跑

最新推荐文章于 2024-08-31 10:53:40 发布

阅读量1.6k

点赞数

文章标签： EBS

本文链接：https://blog.csdn.net/u013711561/article/details/79274859

版权

该博客介绍了如何在Oracle EBS系统中通过调用脚本和存储过程，根据PO_HEADER_ID进行采购订单的退货操作。详细展示了涉及的表格和字段，以及如何筛选符合条件的采购订单，并更新相关状态。

摘要由CSDN通过智能技术生成

CREATE OR REPLACE PACKAGE cux_po_return_pub IS

PROCEDURE po_wpr_main(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
x_po_num IN NUMBER);
END cux_po_return_pub;

CREATE OR REPLACE PACKAGE BODY cux_po_return_pub IS
---------------------------------
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;

PROCEDURE raise_exception(x_return_status VARCHAR2) IS
BEGIN
IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN
RAISE fnd_api.g_exc_error;
END IF;
END raise_exception;
---------------------------------
PROCEDURE dbms_lock_request(p_locked IN OUT BOOLEAN,
x_lockhandle OUT VARCHAR2) IS
l_lockname VARCHAR2(200);
l_lock_output NUMBER;
BEGIN
l_lockname := g_pkg_name || '_001';
dbms_lock.allocate_unique(lockname => l_lockname,
lockhandle => x_lockhandle);
l_lock_output := dbms_lock.request(lockhandle => x_lockhandle,
lockmode => 6,
timeout => 60,
release_on_commit => FALSE);
IF l_lock_output <> 0 THEN
cux_api.set_message(p_app_name => 'CUX',
p_msg_name => 'CUX_PO_CRT_PO_RCV_TXN_ERR');
RAISE fnd_api.g_exc_error;
END IF;
p_locked := TRUE;
END dbms_lock_request;
---------------------------------
PROCEDURE dbms_lock_release(p_locked IN BOOLEAN,
p_lockhandle IN VARCHAR2) IS
l_lock_output NUMBER;
BEGIN
IF p_locked THEN
l_lock_output := dbms_lock.release(p_lockhandle);
END IF;
END dbms_lock_release;
---------------------------------

PROCEDURE rcv_txn_process(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_po_num IN NUMBER) IS

l_api_name CONSTANT VARCHAR2(30) := 'rcv_txn_process';
l_savepoint_name VARCHAR2(30) := 'rcv_txn_process_001';

CURSOR csr_header(p_po_num IN VARCHAR2) IS
SELECT ship.shipment_line_id,

ship.shipment_header_id,

ph.*,

pl.item_id,

pl.category_id,

pl.quantity,

pl.unit_meas_lookup_code,

pl.po_line_id,

(select RT.PARENT_TRANSACTION_ID
from RCV_TRANSACTIONS RT
WHERE RT.PO_HEADER_ID = PH.PO_HEADER_ID
AND RT.PO_LINE_ID = PL.PO_LINE_ID
AND RT.PO_LINE_LOCATION_ID = PLA.LINE_LOCATION_ID
AND RT.TRANSACTION_TYPE = 'DELIVER') parent_transaction_id, --注意这里一定是交货事务处理ID

pla.line_location_id,
(select pda.po_distribution_id
from po_distributions_all pda
where pda.po_line_id = pla.po_line_id) po_distribution_id,

(select fsp.inventory_organization_id
from financials_system_params_all fsp
where fsp.org_id = ph.org_id) inventory_organization_id

FROM rcv_shipment_lines ship,

po_headers_all ph,

po_lines_all pl,

po_line_locations_all pla

WHERE ph.po_header_id = ship.po_header_id

AND ph.po_header_id = pl.po_header_id

AND pl.po_line_id = pla.po_line_id

and ship.po_line_id = pl.po_line_id

AND ph.po_header_id = p_po_num;

/* SELECT h1.wpr_header_id
,h1.project_id
,h1.vendor_id
,h1.workgroup_id
,h2.org_id
,(SELECT MAX(h3.vendor_site_id)
FROM cux.cux_po_work_contracts h3
WHERE h3.project_id = h1.project_id
AND h3.vendor_id = h1.vendor_id
AND h3.org_id = h2.org_id) AS vendor_site_id
,fsp.inventory_organization_id
,

(SELECT p.start_date
FROM gl_periods p
WHERE p.period_set_name = 'GT_CALENDAR'
AND p.period_name = h1.period_name) AS period_start_date
,

(SELECT p.end_date
FROM gl_periods p
WHERE p.period_set_name = 'GT_CALENDAR'
AND p.period_name = h1.period_name) AS period_end_date
,h1.wpr_header_num
FROM cux.cux_po_wpr_header_all h1
,pa_projects_all h2
,financials_system_params_all fsp
WHERE 1 = 1
AND h2.org_id = fsp.org_id
AND h1.project_id = h2.project_id
AND nvl(p_project_id
,h1.project_id) = h1.project_id
AND h1.wpr_header_id NOT IN (SELECT wpr_header_id
FROM cux_po_wpr_err_temp)
AND nvl(p_wpr_header_num
,h1.wpr_header_num) = h1.wpr_header_num
AND h1.status_code IN ('APPROVED')

AND 1 = 1;*/

/* CURSOR csr_line(p_wpr_header_id IN NUMBER) IS
SELECT h1.wpr_header_id,
h1.wpr_line_id,
h1.work_progress_id,
h2.po_header_id,
h1.return_amount
FROM cux.cux_po_wpr_line_all h1, cux_po_work_progress h2
WHERE 1 = 1
AND h1.work_progress_id = h2.work_progress_id
AND h1.wpr_header_id = p_wpr_header_id
AND h1.wpr_header_id NOT IN
(SELECT wpr_header_id FROM cux_po_wpr_err_temp)
AND 1 = 1
AND h1.return_amount > 0;*/

CURSOR csr_line_dis(p_po_header_id IN NUMBER) 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,
----
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,