CREATE OR REPLACE PACKAGE APPS.cux_rcv_return_pkg IS
/**************************************************************************
File name : cux_rcv_return_pkg
Doc Ref(s) :
Project :
Description : for RTV process
Change History Information
--------------------------
Version Date Author Change Reference / Description
------- ----------- --------------- ------------------------------------
1.0 2009-10-20 Randolph First Version
**************************************************************************/
--return available return primary quantity
TYPE cur_reference IS REF CURSOR;
FUNCTION get_avai_qty
(
p_transaction_id IN NUMBER,
p_transaction_ty IN VARCHAR2,
p_receipt_scode IN VARCHAR2,
p_item_id IN NUMBER,
p_primary_uom IN VARCHAR2
) RETURN NUMBER;
PROCEDURE process_return
(
p_org_code IN VARCHAR2,
p_po_number IN VARCHAR2,
p_receipt_num IN VARCHAR2,
p_line_num IN VARCHAR2,
p_shipment_num IN VARCHAR2,
p_release_num IN VARCHAR2,
p_quantity IN NUMBER,
p_uom_code IN VARCHAR2,
p_subinv IN VARCHAR2,
p_locator IN VARCHAR2,
p_reason_code IN VARCHAR2,
p_rma_number IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
);
END cux_rcv_return_pkg;
/
CREATE OR REPLACE PACKAGE BODY APPS.cux_rcv_return_pkg IS
--insert int rcv_transaction interface
PROCEDURE ins_interface
(
p_rcv_row IN rcv_transactions%ROWTYPE,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_paraent_id IN NUMBER,
p_item_id IN NUMBER,
p_item_rev IN VARCHAR2,
p_category_id IN NUMBER,
p_group_id IN NUMBER,
p_lot_code IN NUMBER,
x_iface_id OUT NUMBER,
x_success_flag OUT VARCHAR2,
x_error_mssg OUT VARCHAR2
) IS
l_receipt_source_code VARCHAR2(25) := 'VENDOR';
l_interface_transaction_id NUMBER := p_rcv_row.interface_transaction_id;
l_group_id NUMBER := p_group_id;
l_user_id NUMBER := fnd_global.user_id;
l_logon_id NUMBER := fnd_global.login_id;
l_source_document_code VARCHAR2(25) := 'PO';
l_dest_type_code VARCHAR2(25) := p_rcv_row.destination_type_code;
l_quantity NUMBER;
l_uom VARCHAR2(25) := NULL;
l_uom_code VARCHAR2(3) := p_uom;
l_shipment_hdr_id NUMBER := p_rcv_row.shipment_header_id;
l_shipment_line_id NUMBER := p_rcv_row.shipment_line_id;
l_substitute_code VARCHAR2(25) := p_rcv_row.substitute_unordered_code;
l_employee_id NUMBER;
l_parent_transaction_id NUMBER := p_paraent_id;
l_inspection_code VARCHAR2(25) := p_rcv_row.inspection_status_code;
l_quality_code VARCHAR2(25) := p_rcv_row.inspection_quality_code;
l_po_hdr_id NUMBER := p_rcv_row.po_header_id;
l_po_release_id NUMBER := p_rcv_row.po_release_id;
l_po_line_id NUMBER := p_rcv_row.po_line_id;
l_po_line_location_id NUMBER := p_rcv_row.po_line_location_id;
l_po_dist_id NUMBER := p_rcv_row.po_distribution_id;
l_po_rev_num NUMBER := p_rcv_row.po_revision_num;
l_po_unit_price NUMBER := p_rcv_row.po_unit_price;
l_currency_code VARCHAR2(15) := p_rcv_row.currency_code;
l_currency_conv_rate NUMBER := p_rcv_row.currency_conversion_rate;
l_currency_conv_date DATE := p_rcv_row.currency_conversion_date;
l_currency_conv_type VARCHAR2(30) := p_rcv_row.currency_conversion_type;
l_routing_id NUMBER := p_rcv_row.routing_header_id;
l_routing_step_id NUMBER := p_rcv_row.routing_step_id;
l_comments VARCHAR2(240) := p_rcv_row.comments;
l_rma_reference VARCHAR2(30) := p_rcv_row.rma_reference;
l_attribute_category VARCHAR2(30) := p_rcv_row.attribute_category;
l_attribute1 VARCHAR2(150) := p_rcv_row.attribute1;
l_attribute2 VARCHAR2(150) := p_rcv_row.attribute2;
l_attribute3 VARCHAR2(150) := p_rcv_row.attribute3;
l_attribute4 VARCHAR2(150) := p_rcv_row.attribute4;
l_attribute5 VARCHAR2(150) := p_rcv_row.attribute5;
l_attribute6 VARCHAR2(150) := p_rcv_row.attribute6;
l_attribute7 VARCHAR2(150) := p_rcv_row.attribute7;
l_attribute8 VARCHAR2(150) := p_rcv_row.attribute8;
l_attribute9 VARCHAR2(150) := p_rcv_row.attribute9;
l_attribute10 VARCHAR2(150) := p_rcv_row.attribute10;
l_attribute11 VARCHAR2(150) := p_rcv_row.attribute11;
l_attribute12 VARCHAR2(150) := p_rcv_row.attribute12;
l_attribute13 VARCHAR2(150) := p_rcv_row.attribute13;
l_attribute14 VARCHAR2(150) := p_rcv_row.attribute14;
l_attribute15 VARCHAR2(150) := p_rcv_row.attribute15;
l_transaction_type VARCHAR2(30) := 'RETURN TO VENDOR';
l_location_id NUMBER := NULL;
l_processor_value VARCHAR2(10) := 'IMMEDIATE'; --,IMMEDIATE
l_category_id NUMBER := p_category_id;
l_vendor_lot VARCHAR2(30) := p_rcv_row.vendor_lot_num;
l_reason_id NUMBER := p_rcv_row.reason_id;
l_item_id NUMBER := p_item_id;
l_item_revision VARCHAR2(3) := p_item_rev;
l_to_org_id NUMBER := p_rcv_row.organization_id;
l_deliver_to_location_id NUMBER := p_rcv_row.deliver_to_location_id;
l_dest_context VARCHAR2(30) := p_rcv_row.destination_context;
l_vendor_id NUMBER := p_rcv_row.vendor_id;
l_deliver_to_person_id NUMBER := p_rcv_row.deliver_to_person_id;
l_subinventory VARCHAR2(30) := p_rcv_row.subinventory;
l_locator_id NUMBER := p_rcv_row.locator_id;
l_wip_entity_id NUMBER := p_rcv_row.wip_entity_id;
l_wip_line_id NUMBER := p_rcv_row.wip_line_id;
l_wip_repetitive_schd_id NUMBER := p_rcv_row.wip_repetitive_schedule_id;
l_wip_operation_seq_num VARCHAR2(30) := p_rcv_row.wip_operation_seq_num;
l_wip_resource_seq_num VARCHAR2(30) := p_rcv_row.wip_resource_seq_num;
l_department_code VARCHAR2(30) := p_rcv_row.department_code;
l_bom_resource_id NUMBER := p_rcv_row.bom_resource_id;
l_from_org_id NUMBER := p_rcv_row.organization_id;
l_receipt_exception_flag VARCHAR2(1) := p_rcv_row.receipt_exception_flag;
l_item_description VARCHAR2(240);
l_movement_id NUMBER(30) := p_rcv_row.movement_id;
l_mtl_lot NUMBER;
l_mtl_serial NUMBER;
l_transaction_date DATE := SYSDATE; --p_rcv_row.transaction_date;
l_ussgl_transaction_code VARCHAR2(30);
l_government_context VARCHAR2(30);
l_vendor_site_id NUMBER := p_rcv_row.vendor_site_id;
--debit memo code
l_debit_memo_flag VARCHAR2(1) := 'N';
-- WMS Returns/Corrections Changes
l_lpn_id NUMBER := p_rcv_row.lpn_id;
l_transfer_lpn_id NUMBER := p_rcv_row.transfer_lpn_id;
l_secondary_qty NUMBER := p_rcv_row.secondary_quantity;
l_secondary_unit_of_measure VARCHAR2(25) := p_rcv_row.secondary_unit_of_measure;
/* FPJ WMS */
l_lpn_group_id NUMBER; -- FPJ WMS
l_from_subinventory VARCHAR2(30);
l_from_locator_id NUMBER;
--l_to_subinventory VARCHAR2(30);
--l_to_locator_id NUMBER;
BEGIN
x_success_flag := 'Y';
x_error_mssg := NULL;
--assume do not use serial control
IF nvl(p_lot_code,
1) = 2 THEN
l_mtl_lot := 2;
l_mtl_serial := 1;
ELSE
l_mtl_lot := NULL;
l_mtl_serial := NULL;
END IF;
--
IF (p_quantity IS NULL) OR
p_quantity < 0 THEN
NULL;
ELSE
l_quantity := p_quantity;
END IF;
--validate employee
BEGIN
SELECT employee_id
INTO l_employee_id
FROM fnd_user
WHERE user_id = fnd_global.user_id;
IF l_employee_id IS NULL THEN
x_success_flag := 'N';
x_error_mssg := 'FND user must setup employee.';
RETURN ;
END IF;
EXCEPTION
WHEN OTHERS THEN
x_success_flag := 'N';
x_error_mssg := 'Can not get user ID.' ||
fnd_global.user_id;
RETURN ;
END;
--
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_interface_transaction_id
FROM dual;
x_iface_id := l_interface_transaction_id;
dbms_output.put_line('x_iface_id=' || x_iface_id);
dbms_output.put_line('grpup id=' || p_group_id);
IF (l_transaction_type = 'RETURN TO RECEIVING') THEN
l_dest_type_code := 'RECEIVING';
l_dest_context := 'RECEIVING';
ELSE
l_dest_type_code := 'INVENTORY';
l_dest_context := 'INVENTORY';
END IF;
SELECT muom.unit_of_measure
INTO l_uom
FROM mtl_units_of_measure muom
WHERE muom.uom_code = l_uom_code;
/* FPJ WMS.
* From FPJ we have to have lpn_group_id column populated in rti if the
* row has any lpn info.
*/
IF ((l_lpn_id IS NOT NULL) OR (l_transfer_lpn_id IS NOT NULL)) THEN
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_lpn_group_id
FROM dual;
END IF;
--l_from_subinventory := l_subinventory;
--l_from_locator_id := l_locator_id;
INSERT INTO rcv_transactions_interface
(receipt_source_code,
interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
source_document_code,
destination_type_code,
transaction_date,
quantity,
unit_of_measure,
uom_code,
shipment_header_id,
shipment_line_id,
substitute_unordered_code,
employee_id,
parent_transaction_id,
inspection_status_code,
inspection_quality_code,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
po_revision_num,
po_unit_price,
currency_code,
currency_conversion_rate,
currency_conversion_date,
currency_conversion_type,
routing_header_id,
routing_step_id,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
transaction_type,
location_id,
processing_status_code,
processing_mode_code,
transaction_status_code,
category_id,
vendor_lot_num,
reason_id,
primary_quantity,
primary_unit_of_measure,
item_id,
item_revision,
to_organization_id,
deliver_to_location_id,
destination_context,
vendor_id,
deliver_to_person_id,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
from_organization_id,
receipt_exception_flag,
department_code,
item_description,
movement_id,
use_mtl_lot,
use_mtl_serial,
rma_reference,
ussgl_transaction_code,
government_context,
vendor_site_id,
oe_order_header_id,
oe_order_line_id,
customer_id,
customer_site_id,
create_debit_memo_flag,
lpn_id, -- WMS Returns/Corrections Changes
transfer_lpn_id,
secondary_unit_of_measure,
secondary_quantity,
-- End of Modification for Bug # 1548597.
lpn_group_id,
from_subinventory,
from_locator_id,
subinventory,
locator_id)
VALUES
(l_receipt_source_code,
l_interface_transaction_id,
l_group_id,
SYSDATE,
l_user_id,
l_user_id,
SYSDATE,
l_logon_id,
l_source_document_code,
l_dest_type_code,
l_transaction_date,
l_quantity,
l_uom,
l_uom_code,
l_shipment_hdr_id,
l_shipment_line_id,
l_substitute_code,
l_employee_id,
l_parent_transaction_id,
l_inspection_code,
l_quality_code,
l_po_hdr_id,
l_po_release_id,
l_po_line_id,
l_po_line_location_id,
l_po_dist_id,
l_po_rev_num,
l_po_unit_price,
l_currency_code,
l_currency_conv_rate,
l_currency_conv_date,
l_currency_conv_type,
l_routing_id,
l_routing_step_id,
l_comments,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_transaction_type,
l_location_id,
'PENDING',
l_processor_value,
'PENDING',
l_category_id,
l_vendor_lot,
l_reason_id,
l_quantity,
l_uom,
l_item_id,
l_item_revision,
l_to_org_id,
l_deliver_to_location_id,
l_dest_context,
l_vendor_id,
l_deliver_to_person_id,
l_wip_entity_id,
l_wip_line_id,
l_wip_repetitive_schd_id,
l_wip_operation_seq_num,
l_wip_resource_seq_num,
l_bom_resource_id,
l_from_org_id,
l_receipt_exception_flag,
l_department_code,
l_item_description,
l_movement_id,
l_mtl_lot,
l_mtl_serial,
l_rma_reference,
l_ussgl_transaction_code,
l_government_context,
l_vendor_site_id,
NULL, --l_oe_order_header_id,
NULL, --l_oe_order_line_id,
NULL, --l_customer_id,
NULL, --l_customer_site_id,
l_debit_memo_flag,
l_lpn_id, -- WMS Returns/Corrections Changes
l_transfer_lpn_id,
l_secondary_unit_of_measure,
l_secondary_qty,
-- End of Modification for Bug # 1548597.
l_lpn_group_id,
l_subinventory,
l_locator_id,
NULL,
NULL);
EXCEPTION
WHEN OTHERS THEN
x_success_flag := 'N';
x_error_mssg := 'Insert Iface error.' || SQLCODE;
END ins_interface;
PROCEDURE ins_rcv_lot_iface
(
p_transaction_id IN NUMBER,
p_iface_id IN NUMBER,
p_group_id IN NUMBER,
p_quantity IN NUMBER,
x_success_flag OUT VARCHAR2,
x_error_mssg OUT VARCHAR2
) IS
l_lot_num VARCHAR2(30);
l_expiration_date DATE;
BEGIN
x_success_flag := 'Y';
x_error_mssg := NULL;
--get lot number and expiration date
SELECT DISTINCT lot_num,
expiration_date
INTO l_lot_num,
l_expiration_date
FROM rcv_lot_transactions
WHERE transaction_id = p_transaction_id;
--insert into mtl lot temp
INSERT INTO mtl_transaction_lots_temp
(transaction_temp_id,
group_header_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
transaction_quantity,
primary_quantity,
lot_number,
lot_expiration_date,
product_code,
product_transaction_id)
VALUES
(p_iface_id,
p_group_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
-1,
-1,
-1,
p_quantity,
p_quantity,
l_lot_num,
l_expiration_date,
'RCV',
p_iface_id);
INSERT INTO rcv_lots_interface
(interface_transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
lot_num,
quantity,
transaction_date,
expiration_date,
primary_quantity,
item_id,
shipment_line_id,
secondary_quantity,
sublot_num,
reason_code)
SELECT rti.interface_transaction_id,
rti.last_update_date,
rti.last_updated_by,
rti.creation_date,
rti.created_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.program_update_date,
mtlt.lot_number,
mtlt.transaction_quantity,
rti.transaction_date,
mtlt.lot_expiration_date,
mtlt.primary_quantity,
rti.item_id,
rti.shipment_line_id,
mtlt.secondary_quantity,
mtlt.sublot_num,
mtlt.reason_code
FROM rcv_transactions_interface rti,
mtl_transaction_lots_temp mtlt
WHERE rti.interface_transaction_id = p_iface_id
AND mtlt.transaction_temp_id = rti.interface_transaction_id;
EXCEPTION
WHEN OTHERS THEN
x_success_flag := 'N';
x_error_mssg := 'Insert RCV lot Iface error.' || SQLERRM;
END ins_rcv_lot_iface;
PROCEDURE del_interface(p_group_id IN NUMBER) IS
BEGIN
NULL;
/*FOR r IN (SELECT interface_transaction_id
FROM rcv_transactions_interface
WHERE group_id = p_group_id) LOOP
DELETE rcv_lots_interface
WHERE interface_transaction_id = r.interface_transaction_id;
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = r.interface_transaction_id;
END LOOP;
DELETE rcv_transactions_interface WHERE group_id = p_group_id;
DELETE po_interface_errors WHERE batch_id = p_group_id;*/
--COMMIT ;
END del_interface;
PROCEDURE get_iface_error
(
p_group_id IN NUMBER,
x_error_mssg OUT VARCHAR2
) IS
BEGIN
FOR r IN (SELECT error_message
FROM po_interface_errors
WHERE batch_id = p_group_id) LOOP
x_error_mssg := x_error_mssg || ',' || r.error_message;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
x_error_mssg := NULL;
END get_iface_error;
PROCEDURE submit_req
(
p_group_id IN NUMBER,
x_success_flag OUT VARCHAR2,
x_error_mssg OUT VARCHAR2
) IS
l_req_id NUMBER;
lv_chr_phase VARCHAR2(200);
lv_chr_status VARCHAR2(200);
lv_chr_dev_phase VARCHAR2(200);
lv_chr_dev_status VARCHAR2(200);
lv_chr_message VARCHAR2(200);
ln_interval CONSTANT NUMBER := 15; --Watch interval(second)
ln_max_wait CONSTANT NUMBER := 0;
lb_result BOOLEAN;
BEGIN
x_success_flag := 'Y';
x_error_mssg := NULL;
--submit request
l_req_id := fnd_request.submit_request('PO',
'RVCTP',
NULL,
NULL,
FALSE,
'IMMEDIATE', --IMMEDIATE
p_group_id);
IF l_req_id > 0 THEN
COMMIT;
ELSE
--delete interface records
del_interface(p_group_id);
x_error_mssg := 'Submit request error.';
RAISE fnd_api.g_exc_error;
END IF;
--wait for request complete
lb_result := fnd_concurrent.wait_for_request(l_req_id,
ln_interval,
ln_max_wait,
lv_chr_phase,
lv_chr_status,
lv_chr_dev_phase,
lv_chr_dev_status,
lv_chr_message);
lb_result := fnd_concurrent.get_request_status(l_req_id,
NULL,
NULL,
lv_chr_phase,
lv_chr_status,
lv_chr_dev_phase,
lv_chr_dev_status,
lv_chr_message);
IF NOT
(lv_chr_dev_phase = 'COMPLETE' AND lv_chr_dev_status = 'NORMAL') THEN
--get interface error
get_iface_error(p_group_id,
x_error_mssg);
--request failure , delete interface records
--delete interface records
del_interface(p_group_id);
IF x_error_mssg IS NULL THEN
x_error_mssg := 'Request error,pls see log file.Request ID:' ||
l_req_id;
END IF;
RAISE fnd_api.g_exc_error;
ELSE
--request success
NULL;
END IF;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
x_success_flag := 'N';
WHEN OTHERS THEN
x_success_flag := 'N';
x_error_mssg := 'Error:' || SQLERRM;
END submit_req;
--get organization ID
FUNCTION get_org_id(p_org_code IN VARCHAR2) RETURN NUMBER IS
l_return NUMBER;
BEGIN
SELECT organization_id
INTO l_return
FROM org_organization_definitions
WHERE organization_code = p_org_code;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_org_id;
--return available return primary quantity
FUNCTION get_avai_qty
(
p_transaction_id IN NUMBER,
p_transaction_ty IN VARCHAR2,
p_receipt_scode IN VARCHAR2,
p_item_id IN NUMBER,
p_primary_uom IN VARCHAR2
) RETURN NUMBER IS
l_ava_qty NUMBER;
l_tol_qty NUMBER;
l_uom VARCHAR2(20);
l_primary_qty NUMBER;
BEGIN
rcv_quantities_s.get_available_quantity(p_transaction_type => 'RETURN TO VENDOR',
p_parent_id => p_transaction_id,
p_receipt_source_code => p_receipt_scode,
p_parent_transaction_type => p_transaction_ty,
p_grand_parent_id => NULL,
p_correction_type => 'NEGATIVE',
p_available_quantity => l_ava_qty,
p_tolerable_quantity => l_tol_qty,
p_unit_of_measure => l_uom);
IF l_ava_qty = 0 THEN
l_primary_qty := 0;
ELSE
--convert to primary quantity
l_primary_qty := inv_convert.inv_um_convert(item_id => p_item_id,
PRECISION => NULL,
from_quantity => l_ava_qty,
from_unit => NULL,
to_unit => NULL,
from_name => l_uom,
to_name => p_primary_uom);
END IF;
RETURN l_primary_qty;
EXCEPTION
WHEN OTHERS THEN
--dbms_output.put_line(SQLCODE ||'='||SQLERRM);
--RAISE;
RETURN 0;
END get_avai_qty;
FUNCTION get_item_att_qty
(
p_item_id NUMBER,
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_location_id NUMBER DEFAULT NULL,
p_item_rev VARCHAR2 DEFAULT NULL,
p_lot_number VARCHAR2 DEFAULT NULL
) RETURN NUMBER IS
l_qoh NUMBER;
l_rqoh NUMBER;
l_qr NUMBER;
l_qs NUMBER;
l_att NUMBER;
l_atr NUMBER;
l_tree_mode NUMBER;
l_msg_count VARCHAR2(100);
l_msg_data VARCHAR2(1000);
l_return_status VARCHAR2(1);
l_rev_control BOOLEAN;
l_lot_control BOOLEAN;
--l
l_lot_ctrl NUMBER;
l_rev_ctrl NUMBER;
l_lot_number VARCHAR2(30);
l_item_rev VARCHAR2(10);
BEGIN
-- Transact mode
l_tree_mode := 2;
inv_quantity_tree_pub.clear_quantity_cache;
SELECT nvl(lot_control_code,
1),
nvl(revision_qty_control_code,
1)
INTO l_lot_ctrl,
l_rev_ctrl
FROM mtl_system_items_b
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
IF l_lot_ctrl = 2 THEN
l_lot_control := TRUE;
l_lot_number := p_lot_number;
ELSE
l_lot_control := FALSE;
l_lot_number := NULL;
END IF;
IF l_rev_ctrl = 2 THEN
l_rev_control := TRUE;
l_item_rev := p_item_rev;
ELSE
l_rev_control := FALSE;
l_item_rev := NULL;
END IF;
--
IF l_lot_control THEN
IF l_lot_number IS NULL THEN
l_lot_control := FALSE;
END IF;
END IF;
IF l_rev_control THEN
IF l_item_rev IS NULL THEN
l_rev_control := NULL;
END IF;
END IF;
inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => p_organization_id,
p_inventory_item_id => p_item_id,
p_tree_mode => l_tree_mode,
p_is_revision_control => l_rev_control,
p_is_lot_control => l_lot_control,
p_is_serial_control => FALSE,
p_revision => l_item_rev,
p_lot_number => l_lot_number,
p_lot_expiration_date => NULL,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_location_id,
p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att, --¿¿¿
x_atr => l_atr); --¿¿¿¿
RETURN l_att;
END;
PROCEDURE po_return
(
p_transaction_id IN NUMBER,
p_org_id IN NUMBER,
p_quantity IN NUMBER,
p_uom_code IN VARCHAR2,
p_group_id IN NUMBER,
p_inventory IN VARCHAR2,
p_loctor_id IN NUMBER,
p_reason_id IN NUMBER,
p_rma_num IN VARCHAR2,
p_item_revision IN VARCHAR2,
x_success_flag OUT VARCHAR2,
x_error_mssg OUT VARCHAR2
) IS
l_error_msgg VARCHAR2(2000);
l_flag VARCHAR2(1);
l_rcv_row rcv_transactions%ROWTYPE;
l_item_id NUMBER;
l_item_rev VARCHAR2(10);
l_category_id NUMBER;
l_lot_ctrl_code NUMBER;
l_iface_id NUMBER;
l_ava_qty NUMBER;
l_error_loc VARCHAR2(10);
BEGIN
x_success_flag := 'Y';
x_error_mssg := '';
--get return row
l_error_loc := '10';
SELECT r.*
INTO l_rcv_row
FROM rcv_transactions r
WHERE organization_id = p_org_id
AND transaction_id = p_transaction_id;
l_error_loc := '20';
--get Po lines
SELECT item_id,
item_revision,
category_id,
m.lot_control_code
INTO l_item_id,
l_item_rev,
l_category_id,
l_lot_ctrl_code
FROM po_lines_all pl,
mtl_system_items_b m
WHERE po_line_id = l_rcv_row.po_line_id
AND m.inventory_item_id = pl.item_id
AND m.organization_id = p_org_id;
l_error_loc := '30';
l_item_rev := p_item_revision;
--validate onhand qty
dbms_output.put_line('l_item_id:' || l_item_id);
dbms_output.put_line('p_org_id:' || p_org_id);
dbms_output.put_line('p_inventory:' || p_inventory);
dbms_output.put_line('p_loctor_id:' || p_loctor_id);
dbms_output.put_line('l_item_rev:' || l_item_rev);
l_ava_qty := get_item_att_qty(p_item_id => l_item_id,
p_organization_id => p_org_id,
p_subinventory_code => p_inventory,
p_location_id => p_loctor_id,
p_item_rev => l_item_rev);
IF p_quantity > l_ava_qty THEN
l_error_msgg := 'Return qty ' || p_quantity ||
' exceeds available subinventory qty ' ||
l_ava_qty;
RAISE fnd_api.g_exc_error;
END IF;
--change values
l_rcv_row.subinventory := p_inventory;
l_rcv_row.locator_id := p_loctor_id;
l_rcv_row.rma_reference := p_rma_num;
l_rcv_row.reason_id := p_reason_id;
--insert into RCV interface
l_error_loc := '40';
ins_interface(p_rcv_row => l_rcv_row,
p_quantity => p_quantity,
p_uom => p_uom_code,
p_paraent_id => p_transaction_id,
p_item_id => l_item_id,
p_item_rev => l_item_rev,
p_category_id => l_category_id,
p_group_id => p_group_id,
p_lot_code => l_lot_ctrl_code,
x_iface_id => l_iface_id,
x_success_flag => l_flag,
x_error_mssg => l_error_msgg);
IF l_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
l_error_loc := '50';
dbms_output.put_line(l_lot_ctrl_code || '-' ||
l_rcv_row.transaction_type);
IF l_lot_ctrl_code = 2 AND
l_rcv_row.transaction_type = 'DELIVER' THEN
--need to insert rcv_lot_interface
ins_rcv_lot_iface(p_transaction_id => p_transaction_id,
p_iface_id => l_iface_id,
p_group_id => p_group_id,
p_quantity => p_quantity,
x_success_flag => l_flag,
x_error_mssg => l_error_msgg);
IF l_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
END IF;
l_error_loc := '60';
EXCEPTION
WHEN fnd_api.g_exc_error THEN
x_success_flag := 'N';
x_error_mssg := l_error_msgg || l_error_loc;
WHEN OTHERS THEN
x_success_flag := 'N';
x_error_mssg := 'Error [po_return]:At:' || l_error_loc ||
SQLERRM;
END po_return;
--Main process
PROCEDURE process_return
(
p_org_code IN VARCHAR2,
p_po_number IN VARCHAR2,
p_receipt_num IN VARCHAR2,
p_line_num IN VARCHAR2,
p_shipment_num IN VARCHAR2,
p_release_num IN VARCHAR2,
p_quantity IN NUMBER,
p_uom_code IN VARCHAR2,
p_subinv IN VARCHAR2,
p_locator IN VARCHAR2,
p_reason_code IN VARCHAR2,
p_rma_number IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_org_id NUMBER;
l_error_msgg VARCHAR2(2000);
l_flag VARCHAR2(1);
l_group_id NUMBER;
l_exists NUMBER;
l_quantity NUMBER;
l_index NUMBER;
l_loctor_id NUMBER;
l_reason_id NUMBER;
l_error_loc VARCHAR2(10);
TYPE t_transactions IS RECORD(
quantity NUMBER,
transaction_id NUMBER,
item_revision VARCHAR2(10));
TYPE t_tran_tbl IS TABLE OF t_transactions INDEX BY BINARY_INTEGER;
l_tran_tbl t_tran_tbl;
CURSOR cur_return_row IS
SELECT rt.transaction_id rcv_transaction_id,
rt.transaction_type,
rt.transaction_date,
rt.quantity,
rt.unit_of_measure,
rt.source_document_code,
rt.destination_type_code,
rt.primary_quantity,
rt.primary_unit_of_measure,
rt.subinventory,
mil.concatenated_segments loctor,
rt.organization_id,
ph.segment1 po_number,
rt.po_revision_num,
rsh.receipt_num,
rsl.item_revision,
pr.release_num po_release_num,
pl.line_num po_line_number,
pll.shipment_num po_shipment_number,
get_avai_qty(rt.transaction_id,
rt.transaction_type,
rsh.receipt_source_code,
rsl.item_id,
rt.primary_unit_of_measure) ava_qty
FROM rcv_transactions rt,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
po_requisition_lines prl,
mtl_system_items_b msi,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll,
po_releases_all pr,
financials_system_params_all fsp,
org_organization_definitions og,
mtl_item_locations_kfv mil
WHERE ((((rt.transaction_type IN
('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')) OR
(rt.transaction_type = 'UNORDERED' AND NOT EXISTS
(SELECT 'PROCESSED MATCH ROWS'
FROM rcv_transactions rt2
WHERE rt2.parent_transaction_id = rt.transaction_id
AND rt2.transaction_type = 'MATCH') AND
NOT EXISTS
(SELECT 'UNPROCESSED MATCH ROWS'
FROM rcv_transactions_interface rti
WHERE rti.parent_transaction_id = rt.transaction_id
AND rti.transaction_type = 'MATCH'))) AND
EXISTS
(SELECT 'POSTIVE RCV SUPPLY'
FROM rcv_supply rs
WHERE rs.rcv_transaction_id = rt.transaction_id
AND rs.to_org_primary_quantity >
(SELECT nvl(SUM(rti.primary_quantity),
0)
FROM rcv_transactions_interface rti
WHERE rti.parent_transaction_id =
rt.transaction_id
AND rti.transaction_status_code = 'PENDING'))) OR
(rt.transaction_type = 'DELIVER' AND
rt.source_document_code <> 'RMA'))
AND rt.source_document_code IN ('PO', 'RMA')
AND NOT EXISTS
(SELECT 'PURCHASE ORDER SHIPMENT CANCELLED OR FC'
FROM po_line_locations pll
WHERE pll.line_location_id = rt.po_line_location_id
AND (nvl(pll.cancel_flag,
'N') = 'Y' OR
nvl(pll.closed_code,
'OPEN') = 'FINALLY CLOSED' OR
nvl(pll.approved_flag,
'N') <> 'Y'))
AND NOT EXISTS
(SELECT 'REQUISITION LINE CANCELLED OR FC'
FROM po_requisition_lines prl
WHERE prl.requisition_line_id = rt.requisition_line_id
AND (nvl(prl.cancel_flag,
'N') = 'Y' OR
nvl(prl.closed_code,
'OPEN') = 'FINALLY CLOSED'))
AND (msi.inventory_item_id(+) = rsl.item_id AND
nvl(msi.organization_id,
rt.organization_id) = rt.organization_id)
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.requisition_line_id = prl.requisition_line_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id
AND (ph.po_header_id(+) = rt.po_header_id)
AND (pl.po_line_id(+) = rt.po_line_id)
AND (pll.line_location_id(+) = rt.po_line_location_id)
AND (pr.po_release_id(+) = rt.po_release_id)
AND nvl(pl.matching_basis(+),
'QUANTITY') <> 'AMOUNT'
AND ((nvl(fsp.purch_encumbrance_flag,
'N') = 'Y' AND
nvl(pll.encumbered_flag,
'N') = 'Y') OR
nvl(fsp.purch_encumbrance_flag,
'N') = 'N' OR rt.source_document_code != 'PO' OR
(nvl(fsp.purch_encumbrance_flag,
'N') = 'Y' AND
nvl(pll.encumbered_flag,
'N') = 'N' AND rt.source_document_code = 'PO' AND
EXISTS
(SELECT 1
FROM po_distributions
WHERE line_location_id = pll.line_location_id
AND destination_type_code = 'SHOP FLOOR')))
AND (rt.po_header_id IS NULL OR
((rt.wip_entity_id IS NULL) OR EXISTS
(SELECT 'Jobs not related to EAM WO'
FROM wip_entities we
WHERE rt.wip_entity_id = we.wip_entity_id
AND we.entity_type NOT IN (6, 7)) OR EXISTS
(SELECT 'Open EAM WO Receipts'
FROM wip_entities we,
wip_discrete_jobs wdj
WHERE rt.wip_entity_id = we.wip_entity_id
AND we.wip_entity_id = wdj.wip_entity_id
AND we.entity_type = 6
AND wdj.status_type IN (3, 4, 6))))
AND og.organization_code = p_org_code
AND og.organization_id = rt.organization_id
--Parameter
AND rsh.receipt_num = p_receipt_num
AND pl.line_num = p_line_num
AND ph.segment1 = p_po_number
AND pll.shipment_num = p_shipment_num
AND nvl(pr.release_num,
-1) = nvl(p_release_num,
nvl(pr.release_num,
-1))
AND get_avai_qty(rt.transaction_id,
rt.transaction_type,
rsh.receipt_source_code,
rsl.item_id,
rt.primary_unit_of_measure) > 0
--end Parameter
AND fsp.org_id = ph.org_id
AND mil.inventory_location_id(+) =
nvl(rt.locator_id,
-1)
AND mil.organization_id(+) = rt.organization_id
ORDER BY rt.transaction_date;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
l_error_loc := '10';
--get organization ID
l_org_id := get_org_id(p_org_code);
IF l_org_id IS NULL THEN
l_error_msgg := 'Invalid Org code.' || p_org_code;
RAISE fnd_api.g_exc_error;
END IF;
--
l_error_loc := 20;
IF p_uom_code IS NULL THEN
l_error_msgg := 'UOM can not be null.' || p_uom_code;
RAISE fnd_api.g_exc_error;
END IF;
--
l_error_loc := 30;
IF p_quantity IS NULL THEN
l_error_msgg := 'Qty can not be null.';
RAISE fnd_api.g_exc_error;
ELSE
IF p_quantity < 0 THEN
l_error_msgg := 'Qty must >0.';
RAISE fnd_api.g_exc_error;
END IF;
END IF;
--
l_error_loc := 40;
IF p_reason_code IS NOT NULL THEN
BEGIN
SELECT reason_id
INTO l_reason_id
FROM mtl_transaction_reasons
WHERE reason_name = p_reason_code;
EXCEPTION
WHEN OTHERS THEN
l_error_msgg := 'Invalid reason code.';
RAISE fnd_api.g_exc_error;
END;
ELSE
l_reason_id := NULL;
END IF;
--
l_error_loc := 50;
IF p_locator IS NOT NULL THEN
BEGIN
SELECT inventory_location_id
INTO l_loctor_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments = p_locator
AND organization_id = l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_error_msgg := 'Invalid locator.';
RAISE fnd_api.g_exc_error;
END;
ELSE
l_loctor_id := NULL;
END IF;
--
--get return transaction ID
l_exists := 0;
l_quantity := p_quantity;
l_index := 1;
l_error_loc := 50;
FOR r_data IN cur_return_row LOOP
IF p_subinv IS NOT NULL THEN
--not null :transaction type must be DELIVER
IF r_data.transaction_type <> 'DELIVER' THEN
GOTO next_row;
END IF;
ELSE
--null :transaction type must not DELIVER
IF r_data.transaction_type = 'DELIVER' THEN
GOTO next_row;
END IF;
END IF;
l_exists := 1;
IF r_data.ava_qty >= l_quantity THEN
IF l_quantity = 0 THEN
EXIT;
END IF;
l_tran_tbl(l_index).quantity := l_quantity;
l_tran_tbl(l_index).transaction_id := r_data.rcv_transaction_id;
l_tran_tbl(l_index).item_revision := r_data.item_revision;
l_quantity := l_quantity - r_data.ava_qty;
EXIT;
ELSE
IF l_quantity = 0 THEN
EXIT;
END IF;
l_quantity := l_quantity - r_data.ava_qty;
l_tran_tbl(l_index).quantity := r_data.ava_qty;
l_tran_tbl(l_index).transaction_id := r_data.rcv_transaction_id;
l_tran_tbl(l_index).item_revision := r_data.item_revision;
END IF;
l_index := l_index + 1;
<>
NULL;
END LOOP;
l_error_loc := 60;
--
IF l_exists = 0 THEN
l_error_msgg := 'Not exists return transactions.';
RAISE fnd_api.g_exc_error;
END IF;
l_error_loc := 70;
--
IF l_quantity > 0 THEN
l_error_msgg := 'Return qty exceeds available qty.';
RAISE fnd_api.g_exc_error;
END IF;
--
l_error_loc := 80;
SELECT rcv_interface_groups_s.NEXTVAL INTO l_group_id FROM dual;
dbms_output.put_line('l_group_id=' || l_group_id);
dbms_output.put_line('Total Rows=' || l_tran_tbl.COUNT);
l_error_loc := 90;
FOR i IN l_tran_tbl.FIRST .. l_tran_tbl.LAST LOOP
dbms_output.put_line('Qty=' || l_tran_tbl(i).quantity);
dbms_output.put_line('ID=' || l_tran_tbl(i).transaction_id);
po_return(p_transaction_id => l_tran_tbl(i).transaction_id,
p_org_id => l_org_id,
p_quantity => l_tran_tbl(i).quantity,
p_uom_code => p_uom_code,
p_group_id => l_group_id,
p_inventory => p_subinv,
p_loctor_id => l_loctor_id,
p_reason_id => l_reason_id,
p_rma_num => p_rma_number,
p_item_revision => l_tran_tbl(i).item_revision,
x_success_flag => l_flag,
x_error_mssg => l_error_msgg);
IF l_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
END LOOP;
COMMIT;
l_error_loc := '100';
--submit interface rows
submit_req(p_group_id => l_group_id,
x_success_flag => l_flag,
x_error_mssg => l_error_msgg);
l_error_loc := '110';
IF l_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
l_error_loc := '120';
EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_mssg := l_error_loc || '-' || l_error_msgg || SQLERRM;
del_interface(l_group_id);
COMMIT;
WHEN app_exception.record_lock_exception THEN
o_success_flag := 'N';
o_error_mssg := 'Record locked failure.Try later.';
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := l_error_loc || '-' || SQLERRM;
del_interface(l_group_id);
COMMIT;
END process_return;
END cux_rcv_return_pkg;
/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11536986/viewspace-620210/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/11536986/viewspace-620210/
1050
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
