[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* update the receiving onhand for the lots and serials (RCV_LOTS_SUPPLY and
* RCV_SERIALS_SUPPLY)
*/
PROCEDURE process_lot_serial_intf(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_lot_control_code IN NUMBER
, p_serial_control_code IN NUMBER
, p_lot_number IN VARCHAR2
, p_txn_qty IN NUMBER
, p_primary_qty IN NUMBER
, p_serial_number IN VARCHAR2
, p_product_transaction_id IN NUMBER
, p_lpn_id IN NUMBER
, p_sec_txn_qty IN NUMBER --OPM Convergence
) IS
l_txn_if_id NUMBER;
SELECT expiration_date
, status_id
, origination_type --OPM Convergence
, expiration_action_code --OPM Convergence
, expiration_action_date --OPM Convergence
, hold_date --OPM Convergence
INTO l_lot_expiration_date
, l_lot_status_id
, l_origination_type --OPM Convergence
, l_expiration_action_code --OPM Convergence
, l_expiration_action_date --OPM Convergence
, l_hold_date --OPM Convergence
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
inv_rcv_integration_apis.insert_mtli(
p_api_version => 1.0
, p_init_msg_lst => l_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_transaction_interface_id => l_txn_if_id
, p_lot_number => p_lot_number
, p_transaction_quantity => p_txn_qty
, p_primary_quantity => p_primary_qty
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_expiration_date => l_lot_expiration_date
, p_status_id => l_lot_status_id
, x_serial_transaction_temp_id => l_serial_temp_id
, p_product_transaction_id => l_product_txn_id
, p_product_code => l_prod_code
, p_att_exist => l_yes
, p_update_mln => l_no
, p_origination_type => l_origination_type--OPM Convergence
, p_expiration_action_code => l_expiration_action_code--OPM Convergence
, p_expiration_action_date => l_expiration_action_date--OPM Convergence
, p_hold_date => l_hold_date);--OPM Convergence
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = l_product_txn_id
AND product_code = 'RCV';
SELECT status_id
INTO l_serial_status_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id;
inv_rcv_integration_apis.insert_msni(
p_api_version => 1.0
, p_init_msg_lst => l_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_transaction_interface_id => l_txn_if_id
, p_fm_serial_number => p_serial_number
, p_to_serial_number => p_serial_number
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_status_id => l_serial_status_id
, p_product_transaction_id => l_product_txn_id
, p_product_code => l_prod_code
, p_att_exist => l_yes
, p_update_msn => l_no);
select quantity into l_mol_qty
from mtl_txn_request_lines
where line_id = l_mol_line_id;
l_rt_serial_tbl.DELETE; -- 12942776
l_mmtt_ids.DELETE;
l_transaction_quantities.DELETE;
l_secondary_txn_quantities.DELETE;
l_primary_quantities.DELETE;
print_debug ( 'Select mmtt records based on move_order_line_id'
|| 'order by transaction_temp_id',
4
); --6160359,6189438
SELECT transaction_temp_id, primary_quantity,
DECODE (transaction_uom,
l_uom_code, transaction_quantity
/*Bug6133345*/
,
inv_rcv_cache.convert_qty (l_inventory_item_id,
transaction_quantity,
transaction_uom,
l_uom_code,
NULL
)
) quantity,
secondary_transaction_quantity --OPM Convergence
BULK COLLECT INTO l_mmtt_ids, l_primary_quantities,
l_transaction_quantities,
l_secondary_txn_quantities --OPM Convergence
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_new_mol_id;
print_debug ( 'inserting RTI for MMTT:'
|| NVL (l_mmtt_ids (i), -1)
|| ' quantity:'
|| l_transaction_quantities (i)
|| ' uom:'
|| l_uom,
4
);
insert_inspect_rec_rti (x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rcv_transaction_id => l_rcv_transaction_id,
p_quantity => l_transaction_quantities
(i),
p_uom => l_uom,
p_inspection_code => l_inspection_code,
p_quality_code => l_quality_code,
p_transaction_date => l_transaction_date,
p_transaction_type => l_transaction_type,
p_vendor_lot => l_vendor_lot,
p_reason_id => l_reason_id,
p_primary_qty => l_primary_quantities
(i),
p_organization_id => l_organization_id,
p_comments => l_comments,
p_attribute_category => l_attribute_category,
p_attribute1 => l_attribute1,
p_attribute2 => l_attribute2,
p_attribute3 => l_attribute3,
p_attribute4 => l_attribute4,
p_attribute5 => l_attribute5,
p_attribute6 => l_attribute6,
p_attribute7 => l_attribute7,
p_attribute8 => l_attribute8,
p_attribute9 => l_attribute9,
p_attribute10 => l_attribute10,
p_attribute11 => l_attribute11,
p_attribute12 => l_attribute12,
p_attribute13 => l_attribute13,
p_attribute14 => l_attribute14,
p_attribute15 => l_attribute15,
p_qa_collection_id => l_qa_collection_id,
p_lpn_id => l_rti_lpn_id,
p_transfer_lpn_id => l_rti_transfer_lpn_id,
p_mmtt_temp_id => l_mmtt_ids (i),
p_sec_uom => l_sec_uom,
p_secondary_qty => l_rtv_sec_qty
); --OPM Convergence
SELECT lot_control_code, serial_number_control_code
INTO l_lot_control_code, l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT rt.transaction_id,
rss.serial_num
BULK COLLECT INTO l_rt_serial_tbl
FROM rcv_transactions rt, rcv_serials_supply rss
WHERE rt.transaction_id = l_rcv_transaction_id
AND rss.transaction_id = rt.transaction_id
AND rss.serial_num IN (SELECT *
FROM TABLE (list_serials));
SELECT expiration_date, status_id,
origination_type --OPM Convergence
,
expiration_action_code --OPM Convergence
,
expiration_action_date --OPM Convergence
,
hold_date --OPM Convergence
INTO l_lot_expiration_date, l_lot_status_id,
l_origination_type --OPM Convergence
,
l_expiration_action_code --OPM Convergence
,
l_expiration_action_date --OPM Convergence
,
l_hold_date --OPM Convergence
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
inv_rcv_integration_apis.insert_mtli
(p_api_version => 1.0,
p_init_msg_lst => l_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_transaction_interface_id => l_txn_if_id,
p_lot_number => p_lot_number,
p_transaction_quantity => l_transaction_quantities
(i),
p_primary_quantity => l_primary_quantities
(i),
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_expiration_date => l_lot_expiration_date,
p_status_id => l_lot_status_id,
x_serial_transaction_temp_id => l_serial_temp_id,
p_product_transaction_id => g_interface_transaction_id,
p_product_code => l_prod_code,
p_att_exist => l_yes,
p_update_mln => l_no,
p_origination_type => l_origination_type
--OPM Convergence
,
p_expiration_action_code => l_expiration_action_code
--OPM Convergence
,
p_expiration_action_date => l_expiration_action_date
--OPM Convergence
,
p_hold_date => l_hold_date
); --OPM Convergence
SELECT status_id
INTO l_serial_status_id
FROM mtl_serial_numbers
WHERE serial_number = l_rt_serial_tbl (i).serial_number
AND inventory_item_id = p_inventory_item_id;
inv_rcv_integration_apis.insert_msni
(p_api_version => 1.0,
p_init_msg_lst => l_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_transaction_interface_id => l_txn_if_id,
p_fm_serial_number => l_rt_serial_tbl (i).serial_number,
p_to_serial_number => l_rt_serial_tbl (i).serial_number,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_status_id => l_serial_status_id,
p_product_transaction_id => g_interface_transaction_id,
p_product_code => l_prod_code,
p_att_exist => l_yes,
p_update_msn => l_no
);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 2
WHERE line_id = l_new_mol_id;
SELECT line_id, header_id, uom_code,
quantity - NVL (quantity_delivered, 0),
secondary_quantity
- NVL (secondary_quantity_delivered, 0), --OPM Convergence
REFERENCE, REFERENCE_TYPE_CODE,REFERENCE_ID,
TXN_SOURCE_ID, inspection_status
FROM mtl_txn_request_lines
WHERE inventory_item_id = k_inventory_item_id
AND organization_id = k_organization_id
AND lpn_id = k_lpn_id
AND (revision = k_revision OR revision IS NULL AND p_revision IS NULL
)
AND ( lot_number = k_lot_number
OR lot_number IS NULL AND p_lot_number IS NULL
)
AND inspection_status IS NOT NULL
AND line_status = 7
AND quantity - NVL (quantity_delivered, 0) > 0
AND wms_process_flag = 1
order by inspection_status, abs(quantity - l_quantity);
SELECT rs.rcv_transaction_id, rsh.receipt_source_code,
rs.unit_of_measure,
rs.secondary_unit_of_measure --OPM Convergence
FROM rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
WHERE rs.rcv_transaction_id = rt.transaction_id
AND rsh.shipment_header_id = rs.shipment_header_id
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND rt.routing_header_id = g_inspection_routing
AND rs.item_id = k_item_id
AND ( k_item_revision IS NULL -- Bug : 6139900
OR NVL (rs.item_revision, '@#*') = NVL (k_item_revision, '@#*')
)
AND rs.lpn_id = k_lpn_id; --l_lpn_id should always be NOT NULL
SELECT rs.rcv_transaction_id, rsh.receipt_source_code,
rs.unit_of_measure,
rs.secondary_unit_of_measure --OPM Convergence
,
rls.quantity quantity
FROM rcv_supply rs,
rcv_lots_supply rls,
rcv_transactions rt,
rcv_shipment_headers rsh
WHERE rs.rcv_transaction_id = rt.transaction_id
AND rsh.shipment_header_id = rs.shipment_header_id
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND rt.routing_header_id = g_inspection_routing
AND rs.item_id = k_item_id
AND ( k_item_revision IS NULL -- Bug : 6139900
OR NVL (rs.item_revision, '@#*') = NVL (k_item_revision, '@#*')
)
AND rs.lpn_id = k_lpn_id --l_lpn_id should always be NOT NULL
AND rls.transaction_id = rs.rcv_transaction_id
AND rls.lot_num = k_lot_number;
t_tmp_tbl.DELETE;
SELECT TO_NUMBER (hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information';
SELECT unit_of_measure
INTO l_uom
FROM mtl_units_of_measure
WHERE uom_code = l_uom_code;
SELECT unit_of_measure
INTO l_sec_uom
FROM mtl_units_of_measure
WHERE uom_code = l_sec_uom_code;
SELECT rs.rcv_transaction_id,
rsh.receipt_source_code,
rs.unit_of_measure,
rs.secondary_unit_of_measure,
COUNT (rs.rcv_transaction_id)
BULK COLLECT INTO t_tmp_tbl
FROM rcv_supply rs,
rcv_serials_supply rss,
rcv_transactions rt,
rcv_shipment_headers rsh
WHERE rs.rcv_transaction_id = rt.transaction_id
and rs.rcv_transaction_id = nvl(l_mol_txn_src_id, rs.rcv_transaction_id)
and decode(l_mol_ref_code,4,Decode(l_mol_ref, 'PO_LINE_LOCATION_ID',rs.po_line_location_id,'SHIPMENT_LINE_ID',rs.shipment_line_id), -- 13597819
6,rs.shipment_line_id, -- 13597819 For Intransit
7,rs.oe_order_line_id,
8,rs.shipment_line_id) = l_mol_ref_id
and decode(rt.INSPECTION_STATUS_CODE, 'ACCEPTED', 2,
'REJECTED', 3,
'NOT INSPECTED',1) = l_mol_inspect_status
AND rsh.shipment_header_id = rs.shipment_header_id
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND rt.routing_header_id = g_inspection_routing
AND rs.item_id = l_inventory_item_id
AND ( l_revision IS NULL -- Bug : 6139900
OR NVL (rs.item_revision, '@#*') = NVL (l_revision, '@#*')
)
AND rs.lpn_id = l_lpn_id
--l_lpn_id should always be NOT NULL
AND rss.transaction_id = rs.rcv_transaction_id
AND rss.serial_num IN (SELECT *
FROM TABLE (list_serials))
AND rss.supply_type_code = 'RECEIVING'
AND NVL (rss.lot_num, '@#@') = NVL (l_lot_number, '@#@')
GROUP BY rs.rcv_transaction_id,
rsh.receipt_source_code,
rs.unit_of_measure,
rs.secondary_unit_of_measure
order by 5 desc;
SELECT SUM (NVL (mtli.primary_quantity, 0))
INTO l_processed_lot_prim_qty
FROM mtl_transaction_lots_interface mtli,
rcv_transactions_interface rti
WHERE mtli.product_code = 'RCV'
AND mtli.product_transaction_id =
rti.interface_transaction_id
AND mtli.lot_number = l_lot_number
AND rti.parent_transaction_id = l_rcv_transaction_id
AND rti.transaction_status_code = 'PENDING'
AND rti.processing_status_code <> 'ERROR';
SELECT uom_code
INTO l_rtv_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rtv_uom;
SELECT uom_code
INTO l_rtv_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rtv_uom;
SELECT uom_code
INTO l_rtv_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = t_tmp_tbl (i).unit_of_measure;
* If WMS and PO J are installed, then the move order line quantity updates
* will be handled by the receiving TM. The logic for MO handling would be:
* If MOL quantity > Inspection Quantity Then
* Do not update quantity. Set the process_flag to 2 so that
* this line does not get picked up again.
* Split the move order line to create one for the uninspected quantity
* Else
* Do not update quantity. Set the process_flag to 2 so that this line
* does not get picked up again.
* End If
* If either WMS or PO J are not installed, retain the original processing
* So am opening the cursor with a new parameter k_wms_po_j_higher.
* If this flag is set, then filter the move order lines on process_flag (=1)
* If this flag is not set, then filter lines on quantity
*/
CURSOR mol_cursor(
k_inventory_item_id NUMBER
, k_organization_id NUMBER
, k_lpn_id NUMBER
, k_revision VARCHAR2
, k_lot_number VARCHAR2
) IS
SELECT line_id
, header_id
, uom_code
, quantity - NVL(quantity_delivered,0)
, secondary_quantity - NVL(secondary_quantity_delivered,0) --OPM Convergence
, REFERENCE, REFERENCE_TYPE_CODE,REFERENCE_ID
, TXN_SOURCE_ID, inspection_status,
PRIMARY_QUANTITY--Bug 13484877
FROM mtl_txn_request_lines
WHERE inventory_item_id = k_inventory_item_id
AND organization_id = k_organization_id
AND lpn_id = k_lpn_id
AND (revision = k_revision
OR revision IS NULL
AND p_revision IS NULL)
AND (lot_number = k_lot_number
OR lot_number IS NULL
AND p_lot_number IS NULL)
AND inspection_status is not null --8405606
AND line_status = 7
AND quantity - Nvl(quantity_delivered,0) > 0
AND wms_process_flag = 1
order by inspection_status -- added 12942776
;
SELECT rs.rcv_transaction_id
, rsh.receipt_source_code
, rs.unit_of_measure
, rs.secondary_unit_of_measure --OPM Convergence
FROM rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh,rcv_shipment_lines rsl --BugFix:14554730
WHERE rs.rcv_transaction_id = rt.transaction_id
AND rsl.shipment_line_id = rs.shipment_line_id --BugFix:14554730
AND rsh.shipment_header_id = rs.shipment_header_id
AND ((rsl.shipment_line_id = k_reference_id AND k_reference='SHIPMENT_LINE_ID') --BugFix:14554730
OR
(rsl.po_line_location_id = k_reference_id AND k_reference='PO_LINE_LOCATION_ID')
OR
(rsl.oe_order_line_id = k_reference_id AND k_reference='ORDER_LINE_ID')
)
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND rt.routing_header_id = g_inspection_routing
AND rs.item_id = k_item_id
AND (k_item_revision IS NULL -- Bug : 6139900
OR nvl(rs.item_revision,'@#*') = nvl(k_item_revision,'@#*'))
AND rs.lpn_id = k_lpn_id; --l_lpn_id should always be NOT NULL
SELECT rs.rcv_transaction_id
, rsh.receipt_source_code
, rs.unit_of_measure
, rs.secondary_unit_of_measure --OPM Convergence
, Sum(rls.quantity) quantity --Bug 14117094
FROM rcv_supply rs, rcv_lots_supply rls, rcv_transactions rt, rcv_shipment_headers rsh
WHERE rs.rcv_transaction_id = rt.transaction_id
AND rsh.shipment_header_id = rs.shipment_header_id
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND rt.routing_header_id = g_inspection_routing
AND rs.item_id = k_item_id
AND (k_item_revision IS NULL -- Bug : 6139900
OR nvl(rs.item_revision,'@#*') = nvl(k_item_revision,'@#*'))
AND rs.lpn_id = k_lpn_id --l_lpn_id should always be NOT NULL
AND rls.transaction_id = rs.rcv_transaction_id
AND rls.lot_num = k_lot_number
GROUP BY rs.rcv_transaction_id, rsh.receipt_source_code, rs.unit_of_measure, rs.secondary_unit_of_measure; --Bug 14117094
SELECT rs.rcv_transaction_id
, rsh.receipt_source_code
, rs.unit_of_measure
, rs.secondary_unit_of_measure --OPM Convergence
FROM rcv_supply rs, rcv_serials_supply rss, rcv_transactions rt, rcv_shipment_headers rsh
WHERE rs.rcv_transaction_id = rt.transaction_id
-- added 12942776 start
and rs.rcv_transaction_id = nvl(l_mol_txn_src_id, rs.rcv_transaction_id)
AND DECODE(l_mol_ref_code,4, Decode(l_mol_ref, 'PO_LINE_LOCATION_ID' , rs.po_line_location_id,
'SHIPMENT_LINE_ID' , rs.shipment_line_id),
6, rs.shipment_line_id,
7, rs.oe_order_line_id,
8, rs.shipment_line_id) = l_mol_ref_id --Modified the condition for 14109506. Added the above Decode and condition for intransit
and decode(rt.INSPECTION_STATUS_CODE, 'ACCEPTED', 2,
'REJECTED', 3,
'NOT INSPECTED',1) = l_mol_inspect_status
-- added 12942776 end
AND rsh.shipment_header_id = rs.shipment_header_id
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND rt.routing_header_id = g_inspection_routing
AND rs.item_id = k_item_id
AND (k_item_revision IS NULL -- Bug : 6139900
OR nvl(rs.item_revision,'@#*') = nvl(k_item_revision,'@#*'))
AND rs.lpn_id = k_lpn_id --l_lpn_id should always be NOT NULL
AND rss.transaction_id = rs.rcv_transaction_id
AND rss.serial_num = k_serial_number
AND rss.supply_type_code = 'RECEIVING'
AND Nvl(rss.lot_num,'@#@') = Nvl(k_lot_number,'@#@');
SELECT TO_NUMBER(hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information' ;
SELECT unit_of_measure
INTO l_uom
FROM mtl_units_of_measure
WHERE uom_code = l_uom_code;
SELECT unit_of_measure
INTO l_sec_uom
FROM mtl_units_of_measure
WHERE uom_code = l_sec_uom_code;
SELECT SUM(Nvl(mtli.primary_quantity,0))
INTO l_processed_lot_prim_qty
FROM mtl_transaction_lots_interface mtli
, rcv_transactions_interface rti
WHERE mtli.product_code = 'RCV'
AND mtli.product_transaction_id = rti.interface_transaction_id
AND mtli.lot_number = l_lot_number
AND rti.parent_transaction_id = l_rcv_transaction_id
AND rti.transaction_status_code = 'PENDING'
AND rti.processing_status_code <> 'ERROR';
SELECT uom_code
INTO l_rtv_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rtv_uom;
SELECT uom_code
INTO l_rtv_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rtv_uom;
-- Insert Lot/Serials Interface record for each of these RTI
-- Call ATF API
IF (l_debug = 1) THEN
print_debug('main_process : inside RTV cursor Loop, before split_mo', 4);
l_mmtt_ids.DELETE;
l_transaction_quantities.DELETE;
l_secondary_txn_quantities.DELETE;
l_primary_quantities.DELETE;
print_debug('Select mmtt records based on move_order_line_id order by transaction_temp_id', 4); --6160359,6189438
SELECT transaction_temp_id
, primary_quantity
, DECODE(l_uom_code,ITEM_PRIMARY_UOM_CODE,primary_quantity,transaction_uom--added for Bug 13484877
--commented for Bug 13484877, l_uom_code
, transaction_quantity /*Bug6133345*/
, inv_rcv_cache.convert_qty
(l_inventory_item_id
,transaction_quantity
,transaction_uom
,l_uom_code
,NULL)
) quantity
, secondary_transaction_quantity --OPM Convergence
BULK COLLECT INTO
l_mmtt_ids
, l_primary_quantities
, l_transaction_quantities
, l_secondary_txn_quantities --OPM Convergence
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_new_mol_id;
-- IF there are no mmtts, then insert RTI with no MMTT id
-- with l_rtv_qty and l_primary_qty
IF (l_mmtt_ids.COUNT = 0) THEN
l_mmtt_ids(1) := NULL;
print_debug('Main process inserting RTI for MMTT:' || NVL(l_mmtt_ids(i), -1)
|| ' quantity:' || l_transaction_quantities(i) || ' uom:' || l_uom, 4);
insert_inspect_rec_rti(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rcv_transaction_id => l_rcv_transaction_id
, p_quantity => l_transaction_quantities(i)
, p_uom => l_uom
, p_inspection_code => l_inspection_code
, p_quality_code => l_quality_code
, p_transaction_date => l_transaction_date
, p_transaction_type => l_transaction_type
, p_vendor_lot => l_vendor_lot
, p_reason_id => l_reason_id
, p_primary_qty => l_primary_quantities(i)
, p_organization_id => l_organization_id
, p_comments => l_comments
, p_attribute_category => l_attribute_category
, p_attribute1 => l_attribute1
, p_attribute2 => l_attribute2
, p_attribute3 => l_attribute3
, p_attribute4 => l_attribute4
, p_attribute5 => l_attribute5
, p_attribute6 => l_attribute6
, p_attribute7 => l_attribute7
, p_attribute8 => l_attribute8
, p_attribute9 => l_attribute9
, p_attribute10 => l_attribute10
, p_attribute11 => l_attribute11
, p_attribute12 => l_attribute12
, p_attribute13 => l_attribute13
, p_attribute14 => l_attribute14
, p_attribute15 => l_attribute15
, p_qa_collection_id => l_qa_collection_id
, p_lpn_id => l_rti_lpn_id
, p_transfer_lpn_id => l_rti_transfer_lpn_id
, p_mmtt_temp_id => l_mmtt_ids(i)
, p_sec_uom => l_sec_uom --OPM Convergence
, p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
SELECT lot_control_code
, serial_number_control_code
INTO l_lot_control_code
, l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
--Update the wms_process_flag for the current MOL so that one else
--messes with it
UPDATE mtl_txn_request_lines
SET wms_process_flag = 2
WHERE line_id = l_new_mol_id;
l_serial_tbl.DELETE;
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND serial_number_control_code IN (2, 5, 6);
SELECT lot_number
INTO l_lot_number
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = l_cur_serial_number;
select
rcv_transaction_id
, receipt_source_code
, unit_of_measure
from rcv_transactions_v
where po_header_id = k_po_header_id
and to_organization_id = k_organization_id
and item_id = k_inventory_item_id
and (item_revision = k_revision OR
item_revision is null and p_revision is null)
and inspection_status_code = 'NOT INSPECTED'
and routing_id = g_inspection_routing;
select
rs.rcv_transaction_id
, rsh.receipt_source_code
, rs.unit_of_measure
, rs.secondary_unit_of_measure --OPM Convergence
from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
where rs.po_header_id = k_po_header_id
and rs.to_organization_id = k_organization_id
and rs.item_id = k_inventory_item_id
and (k_revision_control = 2
and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
OR k_revision_control = 1)
-- Changed the above for bug 3134272
and rs.rcv_transaction_id = rt.transaction_id
and rsh.shipment_header_id = rs.shipment_header_id
and rs.supply_type_code = 'RECEIVING'
and rt.transaction_type <> 'UNORDERED'
and rt.routing_header_id = g_inspection_routing;
SELECT TO_NUMBER(hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information' ;
select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
from mtl_system_items
where organization_id = l_organization_id
and inventory_item_id = l_inventory_item_id;
SELECT unit_of_measure
INTO l_uom
FROM mtl_units_of_measure
WHERE uom_code = l_uom_code;
SELECT unit_of_measure
INTO l_sec_uom
FROM mtl_units_of_measure
WHERE uom_code = l_sec_uom_code;
SELECT uom_code
INTO l_rtv_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rtv_uom;
insert_inspect_rec_rti (
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rcv_transaction_id => l_rcv_transaction_id
, p_quantity => l_rtv_qty
, p_uom => l_uom
, p_inspection_code => l_inspection_code
, p_quality_code => l_quality_code
, p_transaction_date => l_transaction_date
, p_transaction_type => l_transaction_type
, p_vendor_lot => l_vendor_lot
, p_reason_id => l_reason_id
, p_primary_qty => l_primary_qty
, p_organization_id => l_organization_id
, p_comments => l_comments
, p_attribute_category => l_attribute_category
, p_attribute1 => l_attribute1
, p_attribute2 => l_attribute2
, p_attribute3 => l_attribute3
, p_attribute4 => l_attribute4
, p_attribute5 => l_attribute5
, p_attribute6 => l_attribute6
, p_attribute7 => l_attribute7
, p_attribute8 => l_attribute8
, p_attribute9 => l_attribute9
, p_attribute10 => l_attribute10
, p_attribute11 => l_attribute11
, p_attribute12 => l_attribute12
, p_attribute13 => l_attribute13
, p_attribute14 => l_attribute14
, p_attribute15 => l_attribute15
, p_qa_collection_id => l_qa_collection_id
, p_sec_uom => l_sec_uom --OPM Convergence
, p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
select
rcv_transaction_id
, receipt_source_code
, unit_of_measure
from rcv_transactions_v
where receipt_source_code <> 'VENDOR'
and shipment_header_id = k_shipment_header_id
and to_organization_id = k_organization_id
and item_id = k_inventory_item_id
and (item_revision = k_revision OR
item_revision is null and p_revision is null)
and inspection_status_code = 'NOT INSPECTED'
and routing_id = g_inspection_routing;
select
rs.rcv_transaction_id
, rsh.receipt_source_code
, rs.unit_of_measure
, rs.secondary_unit_of_measure --OPM Convergence
from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
where rsh.receipt_source_code <> 'VENDOR'
and rs.shipment_header_id = k_shipment_header_id
and rs.to_organization_id = k_organization_id
and rs.item_id = k_inventory_item_id
and (k_revision_control = 2
and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
OR k_revision_control = 1)
-- Changed the above for bug 3134272
and rs.rcv_transaction_id = rt.transaction_id
and rsh.shipment_header_id = rs.shipment_header_id
and rs.supply_type_code = 'RECEIVING'
and rt.transaction_type <> 'UNORDERED'
and rt.routing_header_id = g_inspection_routing;
SELECT TO_NUMBER(hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information' ;
select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
from mtl_system_items
where organization_id = l_organization_id
and inventory_item_id = l_inventory_item_id;
SELECT unit_of_measure
INTO l_uom
FROM mtl_units_of_measure
WHERE uom_code = l_uom_code;
SELECT unit_of_measure
INTO l_sec_uom
FROM mtl_units_of_measure
WHERE uom_code = l_sec_uom_code;
SELECT uom_code
INTO l_rtv_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rtv_uom;
insert_inspect_rec_rti (
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rcv_transaction_id => l_rcv_transaction_id
, p_quantity => l_rtv_qty
, p_uom => l_uom
, p_inspection_code => l_inspection_code
, p_quality_code => l_quality_code
, p_transaction_date => l_transaction_date
, p_transaction_type => l_transaction_type
, p_vendor_lot => l_vendor_lot
, p_reason_id => l_reason_id
, p_primary_qty => l_primary_qty
, p_organization_id => l_organization_id
, p_comments => l_comments
, p_attribute_category => l_attribute_category
, p_attribute1 => l_attribute1
, p_attribute2 => l_attribute2
, p_attribute3 => l_attribute3
, p_attribute4 => l_attribute4
, p_attribute5 => l_attribute5
, p_attribute6 => l_attribute6
, p_attribute7 => l_attribute7
, p_attribute8 => l_attribute8
, p_attribute9 => l_attribute9
, p_attribute10 => l_attribute10
, p_attribute11 => l_attribute11
, p_attribute12 => l_attribute12
, p_attribute13 => l_attribute13
, p_attribute14 => l_attribute14
, p_attribute15 => l_attribute15
, p_qa_collection_id => l_qa_collection_id
, p_sec_uom => l_sec_uom --OPM Convergence
, p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
select
rcv_transaction_id
, receipt_source_code
, unit_of_measure
from rcv_transactions_v
where receipt_source_code = 'CUSTOMER'
and oe_order_header_id = k_oe_order_header_id
and to_organization_id = k_organization_id
and item_id = k_inventory_item_id
and (item_revision = k_revision OR
item_revision is null and p_revision is null)
and inspection_status_code = 'NOT INSPECTED'
and routing_id = g_inspection_routing;
select
rs.rcv_transaction_id
, rsh.receipt_source_code
, rs.unit_of_measure
, rs.secondary_unit_of_measure --OPM Convergence
from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
where rsh.receipt_source_code = 'CUSTOMER'
and rs.oe_order_header_id = k_oe_order_header_id
and rs.to_organization_id = k_organization_id
and rs.item_id = k_inventory_item_id
and (k_revision_control = 2
and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
OR k_revision_control = 1)
-- Changed the above for bug 3134272
and rs.rcv_transaction_id = rt.transaction_id
and rsh.shipment_header_id = rs.shipment_header_id
and rs.supply_type_code = 'RECEIVING'
and rt.transaction_type <> 'UNORDERED'
and rt.routing_header_id = g_inspection_routing;
SELECT TO_NUMBER(hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information' ;
select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
from mtl_system_items
where organization_id = l_organization_id
and inventory_item_id = l_inventory_item_id;
SELECT unit_of_measure
INTO l_uom
FROM mtl_units_of_measure
WHERE uom_code = l_uom_code;
SELECT unit_of_measure
INTO l_sec_uom
FROM mtl_units_of_measure
WHERE uom_code = l_sec_uom_code;
SELECT uom_code
INTO l_rtv_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rtv_uom;
insert_inspect_rec_rti (
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rcv_transaction_id => l_rcv_transaction_id
, p_quantity => l_rtv_qty
, p_uom => l_uom
, p_inspection_code => l_inspection_code
, p_quality_code => l_quality_code
, p_transaction_date => l_transaction_date
, p_transaction_type => l_transaction_type
, p_vendor_lot => l_vendor_lot
, p_reason_id => l_reason_id
, p_primary_qty => l_primary_qty
, p_organization_id => l_organization_id
, p_comments => l_comments
, p_attribute_category => l_attribute_category
, p_attribute1 => l_attribute1
, p_attribute2 => l_attribute2
, p_attribute3 => l_attribute3
, p_attribute4 => l_attribute4
, p_attribute5 => l_attribute5
, p_attribute6 => l_attribute6
, p_attribute7 => l_attribute7
, p_attribute8 => l_attribute8
, p_attribute9 => l_attribute9
, p_attribute10 => l_attribute10
, p_attribute11 => l_attribute11
, p_attribute12 => l_attribute12
, p_attribute13 => l_attribute13
, p_attribute14 => l_attribute14
, p_attribute15 => l_attribute15
, p_qa_collection_id => l_qa_collection_id
, p_sec_uom => l_sec_uom --OPM Convergence
, p_secondary_qty => l_rtv_sec_qty); --OPM Convergence);
select
rcv_transaction_id
, receipt_source_code
, unit_of_measure
from rcv_transactions_v
where receipt_num = k_receipt_num
and to_organization_id = k_organization_id
and item_id = k_inventory_item_id
and (item_revision = k_revision OR
item_revision is null and p_revision is null)
and inspection_status_code = 'NOT INSPECTED'
and routing_id = g_inspection_routing;
select
rs.rcv_transaction_id
, rsh.receipt_source_code
, rs.unit_of_measure
, rs.secondary_unit_of_measure --OPM Convergence
from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
where rsh.receipt_num = k_receipt_num
and rs.to_organization_id = k_organization_id
and rs.item_id = k_inventory_item_id
and (k_revision_control = 2
and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
OR k_revision_control = 1)
-- Changed the above for bug 3134272
and rs.rcv_transaction_id = rt.transaction_id
and rsh.shipment_header_id = rs.shipment_header_id
and rs.supply_type_code = 'RECEIVING'
and rt.transaction_type <> 'UNORDERED'
and rt.routing_header_id = g_inspection_routing;
SELECT TO_NUMBER(hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information' ;
select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
from mtl_system_items
where organization_id = l_organization_id
and inventory_item_id = l_inventory_item_id;
SELECT unit_of_measure
INTO l_uom
FROM mtl_units_of_measure
WHERE uom_code = l_uom_code;
SELECT unit_of_measure
INTO l_sec_uom
FROM mtl_units_of_measure
WHERE uom_code = l_sec_uom_code;
SELECT uom_code
INTO l_rtv_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rtv_uom;
insert_inspect_rec_rti (
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rcv_transaction_id => l_rcv_transaction_id
, p_quantity => l_rtv_qty
, p_uom => l_uom
, p_inspection_code => l_inspection_code
, p_quality_code => l_quality_code
, p_transaction_date => l_transaction_date
, p_transaction_type => l_transaction_type
, p_vendor_lot => l_vendor_lot
, p_reason_id => l_reason_id
, p_primary_qty => l_primary_qty
, p_organization_id => l_organization_id
, p_comments => l_comments
, p_attribute_category => l_attribute_category
, p_attribute1 => l_attribute1
, p_attribute2 => l_attribute2
, p_attribute3 => l_attribute3
, p_attribute4 => l_attribute4
, p_attribute5 => l_attribute5
, p_attribute6 => l_attribute6
, p_attribute7 => l_attribute7
, p_attribute8 => l_attribute8
, p_attribute9 => l_attribute9
, p_attribute10 => l_attribute10
, p_attribute11 => l_attribute11
, p_attribute12 => l_attribute12
, p_attribute13 => l_attribute13
, p_attribute14 => l_attribute14
, p_attribute15 => l_attribute15
, p_qa_collection_id => l_qa_collection_id ,
p_sec_uom => l_sec_uom --OPM Convergence
, p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
procedure insert_inspect_rec_rti (
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_rcv_transaction_id IN NUMBER
, p_quantity IN NUMBER
, p_uom IN VARCHAR2
, p_inspection_code IN VARCHAR2
, p_quality_code IN VARCHAR2
, p_transaction_date IN DATE
, p_transaction_type IN VARCHAR2
, p_vendor_lot IN VARCHAR2
, p_reason_id IN NUMBER
, p_primary_qty IN NUMBER
, p_organization_id IN NUMBER
, p_comments IN VARCHAR2 DEFAULT NULL
, p_attribute_category IN VARCHAR2 DEFAULT NULL
, p_attribute1 IN VARCHAR2 DEFAULT NULL
, p_attribute2 IN VARCHAR2 DEFAULT NULL
, p_attribute3 IN VARCHAR2 DEFAULT NULL
, p_attribute4 IN VARCHAR2 DEFAULT NULL
, p_attribute5 IN VARCHAR2 DEFAULT NULL
, p_attribute6 IN VARCHAR2 DEFAULT NULL
, p_attribute7 IN VARCHAR2 DEFAULT NULL
, p_attribute8 IN VARCHAR2 DEFAULT NULL
, p_attribute9 IN VARCHAR2 DEFAULT NULL
, p_attribute10 IN VARCHAR2 DEFAULT NULL
, p_attribute11 IN VARCHAR2 DEFAULT NULL
, p_attribute12 IN VARCHAR2 DEFAULT NULL
, p_attribute13 IN VARCHAR2 DEFAULT NULL
, p_attribute14 IN VARCHAR2 DEFAULT NULL
, p_attribute15 IN VARCHAR2 DEFAULT NULL
, p_qa_collection_id IN NUMBER DEFAULT NULL
, p_lpn_id IN NUMBER DEFAULT NULL
, p_transfer_lpn_id IN NUMBER DEFAULT NULL
, p_mmtt_temp_id IN NUMBER DEFAULT NULL
, p_sec_uom IN VARCHAR2 DEFAULT NULL --OPM Convergenc
, p_secondary_qty IN NUMBER DEFAULT NULL
) --OPM Convergence)
is
l_interface_transaction_id NUMBER;
savepoint insert_rti_sp;
SELECT
rsh.RECEIPT_SOURCE_CODE
, rt.SOURCE_DOCUMENT_CODE
, rsup.SHIPMENT_HEADER_ID
, rsup.SHIPMENT_LINE_ID
, rt.SUBSTITUTE_UNORDERED_CODE
, rsup.RCV_TRANSACTION_ID
, rsup.PO_HEADER_ID
, rsup.PO_RELEASE_ID
, rsup.PO_LINE_ID
, rsup.PO_LINE_LOCATION_ID
, rt.PO_REVISION_NUM
, NVL(PLL.PRICE_OVERRIDE, POL.UNIT_PRICE)
, rt.CURRENCY_CODE
, rt.CURRENCY_CONVERSION_RATE
, rt.CURRENCY_CONVERSION_DATE
, rt.CURRENCY_CONVERSION_TYPE
, rsup.REQ_LINE_ID
, rsl.REQ_DISTRIBUTION_ID
, rt.ROUTING_header_ID
, rt.ROUTING_STEP_ID
, rt.LOCATION_ID
, rsl.CATEGORY_ID
, rt.PRIMARY_Unit_of_measure
, rsup.ITEM_ID
, rsup.ITEM_REVISION
, rsh.VENDOR_ID
, msi.LOT_CONTROL_CODE
, msi.SERIAL_NUMBER_CONTROL_CODE
, rt.ROUTING_HEADER_ID
, rt.QA_COLLECTION_ID
, rsl.USSGL_TRANSACTION_CODE
, rsl.GOVERNMENT_CONTEXT
, rt.VENDOR_SITE_ID
, rsup.OE_ORDER_HEADER_ID
, rsup.OE_ORDER_LINE_ID
, rsh.CUSTOMER_ID
, rsh.CUSTOMER_SITE_ID
, decode(oel.item_identifier_type, 'CUST', MCI.CUSTOMER_ITEM_NUMBER, '')
INTO
l_receipt_source_code
, l_source_document_code
, l_shipment_hdr_id
, l_shipment_line_id
, l_substitute_code
, l_transaction_id
, l_po_hdr_id
, l_po_release_id
, l_po_line_id
, l_po_line_location_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_req_line_id
, l_req_dist_id
, l_routing_id
, l_routing_step_id
, l_location_id
, l_category_id
, l_primary_uom
, l_item_id
, l_item_revision
, l_vendor_id
, l_mtl_lot
, l_mtl_serial
, l_routing_header_id
, l_qa_collection_id
, l_USSGL_TRANSACTION_CODE
, l_GOVERNMENT_CONTEXT
, l_vendor_site_id
, l_oe_order_header_id
, l_oe_order_line_id
, l_customer_id
, l_customer_site_id
, l_customer_item_number
FROM rcv_supply rsup
,rcv_shipment_headers rsh
,rcv_shipment_lines rsl
,rcv_transactions rt
,po_line_locations pll
,po_lines pol
,mtl_system_items msi
,mtl_customer_items mci
,oe_order_lines_all oel
WHERE rt.transaction_id = l_rcv_transaction_id
AND rt.transaction_type <> 'UNORDERED'
AND rsup.supply_type_code = 'RECEIVING'
AND rsup.rcv_transaction_id = rt.transaction_id
AND rsh.shipment_header_id = rsup.shipment_header_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND pll.line_location_id(+) = rsup.po_line_location_id
AND pol.po_line_id(+) = rsup.po_line_id
AND msi.organization_id (+) = rsup.to_organization_id
AND msi.inventory_item_id (+) = rsup.item_id
AND oel.line_id(+) = rsup.oe_order_line_id
AND oel.ordered_item_id = mci.customer_item_id(+);
print_debug('IN INSERT_INSPECT_REC_RTF ',9);
SELECT receipt_num
INTO l_receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = l_shipment_hdr_id
AND ship_to_org_id = p_organization_id;
SELECT rcv_interface_groups_s.nextval
INTO l_group_id FROM dual;
select rcv_transactions_interface_s.nextval
into l_interface_transaction_id from dual;
SELECT RT.MOVEMENT_ID
INTO l_movement_id
FROM RCV_TRANSACTIONS RT
WHERE RT.TRANSACTION_ID = l_rcv_transaction_id;
SELECT lpn_context
, subinventory_code
, locator_id
INTO l_xfer_lpn_ctxt
, l_xfer_lpn_sub
, l_xfer_lpn_loc_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_transfer_lpn_id;
SELECT subinventory_code
, locator_id
INTO l_lpn_sub
, l_lpn_loc_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
print_debug('insert_inspect_rec_rti: Before calculating project ' , 4);
SELECT project_id
, task_id
INTO l_rti_project_id
, l_rti_task_id
FROM mtl_txn_request_lines
WHERE lpn_id = p_lpn_id
AND inventory_item_id = l_item_id
-- Bug 3366617
-- The following check was not needed as the process_flag is not yet updated.
-- AND wms_process_flag = 2
AND ROWNUM < 2;
print_debug('insert_inspect_rec_rti: In the exception of calculating project ' , 4);
print_debug('insert_inspect_rec_rti: validation_flag : ' || l_validation_flag || ', lpn_group_id: ' || l_lpn_group_id, 4);
print_debug('insert_inspect_rec_rti: subinventory : ' || l_rti_sub_code || ', locator_id: ' || l_rti_loc_id, 4);
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,
interface_source_code,
source_document_code,
destination_type_code,
transaction_date,
quantity,
unit_of_measure,
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,
requisition_line_id,
req_distribution_id,
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,
use_mtl_lot,
use_mtl_serial,
movement_id,
currency_conversion_date,
currency_conversion_type,
qa_collection_id,
ussgl_transaction_code,
government_context,
vendor_site_id,
oe_order_header_id,
oe_order_line_id,
customer_id,
customer_site_id,
lpn_id,
transfer_lpn_id,
mobile_txn,
validation_flag,
lpn_group_id,
mmtt_temp_id,
subinventory,
locator_id,
project_id,
task_id,
secondary_quantity, --OPM Convergence
secondary_unit_of_measure, --OPM Convergence
org_id --
)
values
(
l_receipt_source_code,
l_interface_transaction_id,
l_group_id,
SYSDATE,
l_user_id,
l_user_id,
SYSDATE,
l_logon_id,
'RCV',
l_source_document_code,
l_dest_type_code,
l_transaction_date,
l_quantity,
l_uom,
l_shipment_hdr_id,
l_shipment_line_id,
l_substitute_code,
l_employee_id,
l_transaction_id,
l_inspection_type,
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_req_line_id,
l_req_dist_id,
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', -- Formerly INSPECTION
l_processor_value,
'PENDING', -- Formerly INSPECTION
l_category_id,
l_vendor_lot,
l_reason_id,
l_primary_qty,
l_primary_uom,
l_item_id,
l_item_revision,
l_organization_id,
l_deliver_to_location_id,
l_dest_context,
l_vendor_id,
l_mtl_lot,
l_mtl_serial,
l_movement_id,
Trunc(l_currency_conv_date),
l_currency_conv_type,
l_qa_collection_id,
l_ussgl_transaction_code,
l_government_context,
l_vendor_site_id,
l_oe_order_header_id,
l_oe_order_line_id,
l_customer_id,
l_customer_site_id,
l_lpn_id,
l_transfer_lpn_id,
'Y',
l_validation_flag,
l_lpn_group_id,
l_mmtt_temp_id,
l_rti_sub_code,
l_rti_loc_id,
l_rti_project_id,
l_rti_task_id,
l_secondary_qty, --OPM Convergence
l_sec_uom, --OPM Convergence
l_operating_unit_id --
);
SELECT mp.lcm_enabled_flag
INTO v_lcm_enabled_org
FROM mtl_parameters mp
WHERE mp.organization_id = l_organization_id;
SELECT rp.pre_receive
INTO v_pre_receive
FROM rcv_parameters rp
WHERE rp.organization_id = l_organization_id;
SELECT LCM_SHIPMENT_LINE_ID, UNIT_LANDED_COST
INTO v_lcm_ship_line_id, v_unit_landed_cost
FROM rcv_shipment_lines
WHERE shipment_line_id = l_shipment_line_id;
UPDATE rcv_transactions_interface
SET lcm_shipment_line_id = v_lcm_ship_line_id,
unit_landed_cost = v_unit_landed_cost
WHERE interface_transaction_id = l_interface_transaction_id
AND to_organization_id = l_organization_id;
rollback to insert_rti_sp;
rollback to insert_rti_sp;
rollback to insert_rti_sp;
, 'insert_inspect_rec_rti'
);
end insert_inspect_rec_rti;
SELECT rti.interface_transaction_id
FROM rcv_transactions_interface rti
WHERE rti.GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
SELECT uom_code, (quantity - Nvl(quantity_delivered,0)) quantity --bug#12663552
FROM mtl_txn_request_lines
WHERE inspection_status is not null --8405606
AND organization_id = p_organization_id
AND line_status = 7 --bug#12663552
AND inventory_item_id = p_item_id
AND lpn_id = p_lpn_id;
SELECT rs.rcv_transaction_id
FROM rcv_supply rs
, rcv_transactions rt
WHERE rs.item_id = p_item_id
AND rs.po_header_id = p_po_header_id
AND nvl(rs.po_release_id,-1) = nvl(p_po_release_id,nvl(rs.po_release_id,-1))
AND nvl(rs.po_line_id,-1) = nvl(p_po_line_id, nvl(rs.po_line_id,-1))
AND rs.rcv_transaction_id = rt.transaction_id
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND rt.routing_header_id = 2
--BUG 4103743: Need to query on org id also
AND rs.to_organization_id = p_organization_id; /* Inspection routing */
SELECT rs.rcv_transaction_id
FROM rcv_supply rs
, rcv_transactions rt
WHERE rs.item_id = p_item_id
AND rs.shipment_header_id = p_shipment_header_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND rt.routing_header_id = 2; /* Inspection routing */
SELECT rs.rcv_transaction_id
FROM rcv_supply rs
, rcv_transactions rt
WHERE rs.item_id = p_item_id
AND rs.oe_order_header_id = p_oe_order_header_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND rt.routing_header_id = 2; /* Inspection routing */
select uom_code
into l_cur_uom_code
from mtl_units_of_measure
where unit_of_measure = l_cur_uom_code;
select uom_code
into l_cur_uom_code
from mtl_units_of_measure
where unit_of_measure = l_cur_uom_code;
select uom_code
into l_cur_uom_code
from mtl_units_of_measure
where unit_of_measure = l_cur_uom_code;
SELECT COUNT(DISTINCT pha.po_header_id)
INTO v_count_po
FROM mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha
WHERE reference = 'PO_LINE_LOCATION_ID'
AND mtrl.reference_id = plla.line_location_id
AND plla.po_header_id = pha.po_header_id
AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
AND mtrl.lpn_id = p_lpn_id
AND mtrl.organization_id = p_organization_id
AND mtrl.inventory_item_id = p_inventory_item_id ;
select distinct pha.po_header_id, pha.segment1, pv.vendor_id, pv.vendor_name, plla.po_line_id
into x_po_id, x_po_number, x_vendor_id, x_vendor_name, v_po_line_id
from mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha, po_vendors pv
where reference = 'PO_LINE_LOCATION_ID'
and mtrl.reference_id = plla.line_location_id
and plla.po_header_id = pha.po_header_id
and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
and pha.vendor_id = pv.vendor_id
and mtrl.lpn_id = p_lpn_id
and mtrl.organization_id = p_organization_id
and mtrl.inventory_item_id = p_inventory_item_id;
SELECT pasv.asl_status_id, pasv.asl_status_dsp
into x_asl_status_id, x_asl_status_dsp
FROM po_asl_suppliers_v pasv, po_lines pl, po_headers ph
WHERE pl.item_id = pasv.item_id
AND pl.po_line_id = v_po_line_id -- here use the variable from above
AND pl.po_header_id = ph.po_header_id
AND ph.vendor_id(+) = pasv.vendor_id
AND ph.vendor_site_id(+) = pasv.vendor_site_id
AND (p_organization_id = pasv.using_organization_id
OR pasv.using_organization_id = -1);
select distinct rsh.receipt_num, '0'
into x_receipt_number, x_receipt_return_status
from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
where reference = 'PO_LINE_LOCATION_ID'
and mtrl.reference_id = rt.po_line_location_id
and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
and rt.shipment_header_id = rsh.shipment_header_id
and mtrl.lpn_id = p_lpn_id
and mtrl.lpn_id = rt.transfer_lpn_id --Bug#7390895
and mtrl.organization_id = p_organization_id
and mtrl.inventory_item_id = p_inventory_item_id;
SELECT COUNT(DISTINCT oeh.header_id)
INTO v_count_rma
FROM mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh
WHERE reference = 'ORDER_LINE_ID'
AND mtrl.reference_id = oel.line_id
AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
AND oel.header_id = oeh.header_id
AND mtrl.lpn_id = p_lpn_id
AND mtrl.organization_id = p_organization_id
AND mtrl.inventory_item_id = p_inventory_item_id;
select distinct oeh.header_id, oeh.order_number, oest.customer_id, oest.customer_number, oest.name
into x_rma_id, x_rma_number, x_customer_id, x_customer_number, x_customer_name
from mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh, oe_sold_to_orgs_v oest
where reference = 'ORDER_LINE_ID'
and mtrl.reference_id = oel.line_id
and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
and oel.header_id = oeh.header_id
and oeh.sold_to_org_id = oest.customer_id
and mtrl.lpn_id = p_lpn_id
and mtrl.organization_id = p_organization_id
and mtrl.inventory_item_id = p_inventory_item_id;
select distinct rsh.receipt_num, '0'
into x_receipt_number, x_receipt_return_status
from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
where reference = 'ORDER_LINE_ID'
and mtrl.reference_id = rt.oe_order_line_id
and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
and rt.shipment_header_id = rsh.shipment_header_id
and mtrl.lpn_id = p_lpn_id
and mtrl.organization_id = p_organization_id
and mtrl.inventory_item_id = p_inventory_item_id;
SELECT COUNT(DISTINCT rsl.shipment_header_id)
INTO v_count_intshp
FROM mtl_txn_request_lines mtrl, rcv_shipment_lines rsl
WHERE reference = 'SHIPMENT_LINE_ID'
AND mtrl.reference_id = rsl.shipment_line_id
AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
AND mtrl.lpn_id = p_lpn_id
AND mtrl.organization_id = p_organization_id
AND mtrl.inventory_item_id = p_inventory_item_id;
select distinct rsl.shipment_header_id, rsh.shipment_num, rsh.receipt_num
into x_intshp_id, x_intshp_number, x_receipt_number
from mtl_txn_request_lines mtrl, rcv_shipment_lines rsl, rcv_shipment_headers rsh
where reference = 'SHIPMENT_LINE_ID'
and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
and mtrl.reference_id = rsl.shipment_line_id
and rsl.shipment_header_id = rsh.shipment_header_id
and mtrl.lpn_id = p_lpn_id
and mtrl.organization_id = p_organization_id
and mtrl.inventory_item_id = p_inventory_item_id;
SELECT 1
INTO l_count
FROM rcv_supply rs
, rcv_transactions rt
WHERE rs.item_id = p_item_id
AND rs.item_revision IS NULL
AND rs.po_header_id = p_source_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rt.inspection_status_code = 'NOT INSPECTED'
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND ROWNUM < 2;
SELECT 1
INTO l_count
FROM rcv_supply rs
, rcv_transactions rt
WHERE rs.item_id = p_item_id
AND rs.item_revision IS NULL
AND rs.shipment_header_id = p_source_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rt.inspection_status_code = 'NOT INSPECTED'
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND ROWNUM < 2;
SELECT 1
INTO l_count
FROM rcv_supply rs
, rcv_transactions rt
WHERE rs.item_id = p_item_id
AND rs.item_revision IS NULL
AND rs.oe_order_header_id = p_source_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rt.inspection_status_code = 'NOT INSPECTED'
AND rs.supply_type_code = 'RECEIVING'
AND rt.transaction_type <> 'UNORDERED'
AND ROWNUM < 2;