[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_epc_sql := 'SELECT ' || l_epc_column;
l_epc_sql := 'SELECT ' || l_epc_column;
select 1 into x_exists
from wms_lpn_interface
where ( ( license_plate_number = p_license_plate_number ) or ( lpn_id = p_lpn_id ))
and source_group_id = p_lpn_group_id
and rownum = 1;
SELECT '1'
INTO l_dummy
FROM dual
WHERE exists (SELECT '1'
FROM mtl_txn_request_lines mtrl
WHERE mtrl.lpn_id = p_lpn_id
AND mtrl.organization_id = p_org_id
AND (Nvl(mtrl.project_id,-1) <> Nvl(p_project_id,-1)
OR Nvl(mtrl.task_id,-1) <> Nvl(p_task_id,-1))
AND line_status <> inv_globals.G_TO_STATUS_CLOSED
AND (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) > 0);
SELECT '1' INTO l_dummy
FROM wms_lpn_contents wlc
, mtl_system_items msi
, (SELECT lpn_id
FROM wms_license_plate_numbers wlpn2
WHERE wlpn2.organization_id = p_org_id
AND wlpn2.lpn_context <> G_LPN_CONTEXT_STORES
CONNECT BY PRIOR wlpn2.lpn_id = wlpn2.parent_lpn_id
START WITH wlpn2.lpn_id = p_lpn_id
UNION ALL
SELECT lpn_id
FROM wms_license_plate_numbers wlpn3
WHERE wlpn3.organization_id = p_org_id
AND wlpn3.lpn_context <> G_LPN_CONTEXT_STORES
CONNECT BY PRIOR wlpn3.parent_lpn_id = wlpn3.lpn_id
START WITH wlpn3.parent_lpn_id = p_lpn_id) wlpn
WHERE wlc.parent_lpn_id = wlpn.lpn_id
AND wlc.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_org_id
AND msi.mtl_transactions_enabled_flag <> p_transactions_enabled_flag
AND ROWNUM<2;
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_mol_id
AND ((transaction_source_type_id = 1 AND transaction_action_id = 27)
OR (transaction_source_type_id = 7 AND transaction_action_id = 12)
OR (transaction_source_type_id = 12 AND transaction_action_id = 27)
OR (transaction_source_type_id = 13 AND transaction_action_id = 12)
OR (transaction_source_type_id = 4 AND transaction_action_id = 27));
SELECT msi.lot_control_code
, msi.serial_number_control_code
, msi.revision_qty_control_code
INTO x_lot_control_code
, x_serial_control_code
, x_revision_control_code
FROM mtl_system_items msi, rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = p_shipment_line_id
AND rsl.to_organization_id = p_org_id
AND msi.inventory_item_id = rsl.item_id
AND msi.organization_id = rsl.from_organization_id;
SELECT
msnt.transaction_temp_id transaction_temp_id
,msnt.last_updated_by last_updated_by
,msnt.created_by created_by
,rti.item_id inventory_item_id
,rti.to_organization_id organization_id
,msnt.fm_serial_number fm_serial_number
,msnt.to_serial_number to_serial_number
,rti.transaction_date transaction_date
,Decode(rti.source_document_code,
'INVENTORY',
13,
Decode(rti.source_document_code,
'PO',
1,
Decode(rti.source_document_code,
'REQ',
7,
Decode(rti.source_document_code,
'RMA',
12,
-1)))) transaction_source_type_id
-- What to do in error condition?
,rti.source_document_code transaction_source_name
,Decode(rti.transaction_type,
'RETURN TO RECEIVING',
1,
Decode(rti.transaction_type,
'RETURN TO VENDOR',
1,
Decode(rti.transaction_type,
'RETURN TO CUSTOMER',
1,
2))) receipt_issue_type
,msnt.serial_attribute_category serial_attribute_category
,msnt.origination_date origination_date
,msnt.c_attribute1 c_attribute1
,msnt.c_attribute2 c_attribute2
,msnt.c_attribute3 c_attribute3
,msnt.c_attribute4 c_attribute4
,msnt.c_attribute5 c_attribute5
,msnt.c_attribute6 c_attribute6
,msnt.c_attribute7 c_attribute7
,msnt.c_attribute8 c_attribute8
,msnt.c_attribute9 c_attribute9
,msnt.c_attribute10 c_attribute10
,msnt.c_attribute11 c_attribute11
,msnt.c_attribute12 c_attribute12
,msnt.c_attribute13 c_attribute13
,msnt.c_attribute14 c_attribute14
,msnt.c_attribute15 c_attribute15
,msnt.c_attribute16 c_attribute16
,msnt.c_attribute17 c_attribute17
,msnt.c_attribute18 c_attribute18
,msnt.c_attribute19 c_attribute19
,msnt.c_attribute20 c_attribute20
,msnt.d_attribute1 d_attribute1
,msnt.d_attribute2 d_attribute2
,msnt.d_attribute3 d_attribute3
,msnt.d_attribute4 d_attribute4
,msnt.d_attribute5 d_attribute5
,msnt.d_attribute6 d_attribute6
,msnt.d_attribute7 d_attribute7
,msnt.d_attribute8 d_attribute8
,msnt.d_attribute9 d_attribute9
,msnt.d_attribute10 d_attribute10
,msnt.n_attribute1 n_attribute1
,msnt.n_attribute2 n_attribute2
,msnt.n_attribute3 n_attribute3
,msnt.n_attribute4 n_attribute4
,msnt.n_attribute5 n_attribute5
,msnt.n_attribute6 n_attribute6
,msnt.n_attribute7 n_attribute7
,msnt.n_attribute8 n_attribute8
,msnt.n_attribute9 n_attribute9
,msnt.n_attribute10 n_attribute10
,msnt.status_id status_id
,msnt.territory_code territory_code
,msnt.time_since_new time_since_new
,msnt.cycles_since_new cycles_since_new
,msnt.time_since_overhaul time_since_overhaul
,msnt.cycles_since_overhaul cycles_since_overhaul
,msnt.time_since_repair time_since_repair
,msnt.cycles_since_repair cycles_since_repair
,msnt.time_since_visit time_since_visit
,msnt.cycles_since_visit cycles_since_visit
,msnt.time_since_mark time_since_mark
,msnt.cycles_since_mark cycles_since_mark
,msnt.number_of_repairs number_of_repairs
from
mtl_serial_numbers_temp msnt
,rcv_transactions_interface rti
where
msnt.product_code = 'RCV'
AND msnt.product_transaction_id = p_prod_txn_tmp_id
AND rti.interface_transaction_id = msnt.product_transaction_id;
INSERT INTO mtl_transaction_lot_numbers
(
transaction_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,inventory_item_id
,organization_id
,transaction_date
,transaction_source_id
,transaction_source_type_id
,transaction_source_name
,transaction_quantity
,primary_quantity
,lot_number
,serial_transaction_id
,description
,vendor_name
,supplier_lot_number
,origination_date
,date_code
,grade_code
,change_date
,maturity_date
,status_id
,retest_date
,age
,item_size
,color
,volume
,volume_uom
,place_of_origin
,best_by_date
,length
,length_uom
,width
,width_uom
,recycled_content
,thickness
,thickness_uom
,curl_wrinkle_fold
,lot_attribute_category
,c_attribute1
,c_attribute2
,c_attribute3
,c_attribute4
,c_attribute5
,c_attribute6
,c_attribute7
,c_attribute8
,c_attribute9
,c_attribute10
,c_attribute11
,c_attribute12
,c_attribute13
,c_attribute14
,c_attribute15
,c_attribute16
,c_attribute17
,c_attribute18
,c_attribute19
,c_attribute20
,d_attribute1
,d_attribute2
,d_attribute3
,d_attribute4
,d_attribute5
,d_attribute6
,d_attribute7
,d_attribute8
,d_attribute9
,d_attribute10
,n_attribute1
,n_attribute2
,n_attribute3
,n_attribute4
,n_attribute5
,n_attribute6
,n_attribute7
,n_attribute8
,n_attribute9
,n_attribute10
,vendor_id
,territory_code
,product_code
,product_transaction_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15 )
SELECT
mtlt.transaction_temp_id
,l_sysdate
,mtlt.last_updated_by
,l_sysdate
,mtlt.created_by
,-1
,rti.item_id
,rti.to_organization_id
,rti.transaction_date
,NULL
,Decode(rti.source_document_code,
'INVENTORY',
13,
Decode(rti.source_document_code,
'PO',
1,
Decode(rti.source_document_code,
'REQ',
7,
Decode(rti.source_document_code,
'RMA',
12,
-1))))
,rti.source_document_code
,mtlt.transaction_quantity
,mtlt.primary_quantity
,Ltrim(Rtrim(mtlt.lot_number))
,mtlt.serial_transaction_temp_id
,mtlt.description
,mtlt.vendor_name
,mtlt.supplier_lot_number
,mtlt.origination_date
,mtlt.date_code
,mtlt.grade_code
,mtlt.change_date
,mtlt.maturity_date
,mtlt.status_id
,mtlt.retest_date
,mtlt.age
,mtlt.item_size
,mtlt.color
,mtlt.volume
,mtlt.volume_uom
,mtlt.place_of_origin
,mtlt.best_by_date
,mtlt.length
,mtlt.length_uom
,mtlt.recycled_content
,mtlt.thickness
,mtlt.thickness_uom
,mtlt.width
,mtlt.width_uom
,mtlt.curl_wrinkle_fold
,mtlt.lot_attribute_category
,mtlt.c_attribute1
,mtlt.c_attribute2
,mtlt.c_attribute3
,mtlt.c_attribute4
,mtlt.c_attribute5
,mtlt.c_attribute6
,mtlt.c_attribute7
,mtlt.c_attribute8
,mtlt.c_attribute9
,mtlt.c_attribute10
,mtlt.c_attribute11
,mtlt.c_attribute12
,mtlt.c_attribute13
,mtlt.c_attribute14
,mtlt.c_attribute15
,mtlt.c_attribute16
,mtlt.c_attribute17
,mtlt.c_attribute18
,mtlt.c_attribute19
,mtlt.c_attribute20
,mtlt.d_attribute1
,mtlt.d_attribute2
,mtlt.d_attribute3
,mtlt.d_attribute4
,mtlt.d_attribute5
,mtlt.d_attribute6
,mtlt.d_attribute7
,mtlt.d_attribute8
,mtlt.d_attribute9
,mtlt.d_attribute10
,mtlt.n_attribute1
,mtlt.n_attribute2
,mtlt.n_attribute3
,mtlt.n_attribute4
,mtlt.n_attribute5
,mtlt.n_attribute6
,mtlt.n_attribute7
,mtlt.n_attribute8
,mtlt.n_attribute9
,mtlt.n_attribute10
,mtlt.vendor_id
,mtlt.territory_code
,'RCV'
,p_prod_txn_id
,mtlt.attribute_category
,mtlt.attribute1
,mtlt.attribute2
,mtlt.attribute3
,mtlt.attribute4
,mtlt.attribute5
,mtlt.attribute6
,mtlt.attribute7
,mtlt.attribute8
,mtlt.attribute9
,mtlt.attribute10
,mtlt.attribute11
,mtlt.attribute12
,mtlt.attribute13
,mtlt.attribute14
,mtlt.attribute15
FROM
mtl_transaction_lots_temp mtlt
,rcv_transactions_interface rti
WHERE
mtlt.product_transaction_id = p_prod_txn_tmp_id AND
mtlt.product_code = 'RCV' AND
rti.interface_transaction_id = p_prod_txn_tmp_id;
print_debug('CREATE_LOT_SERIAL_HISTORY: MTLNs inserted.',1);
l_serial_numbers.DELETE;
-- concatenate the serial number to be inserted
if (l_from_ser_number = -1
and l_to_ser_number = -1) then
l_cur_serial_number := l_msnt.fm_serial_number;
INSERT INTO mtl_unit_transactions
( transaction_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,serial_number
,inventory_item_id
,organization_id
,subinventory_code
,locator_id
,transaction_date
,transaction_source_id
,transaction_source_type_id
,transaction_source_name
,receipt_issue_type
,customer_id
,ship_id
,serial_attribute_category
,origination_date
,c_attribute1
,c_attribute2
,c_attribute3
,c_attribute4
,c_attribute5
,c_attribute6
,c_attribute7
,c_attribute8
,c_attribute9
,c_attribute10
,c_attribute11
,c_attribute12
,c_attribute13
,c_attribute14
,c_attribute15
,c_attribute16
,c_attribute17
,c_attribute18
,c_attribute19
,c_attribute20
,d_attribute1
,d_attribute2
,d_attribute3
,d_attribute4
,d_attribute5
,d_attribute6
,d_attribute7
,d_attribute8
,d_attribute9
,d_attribute10
,n_attribute1
,n_attribute2
,n_attribute3
,n_attribute4
,n_attribute5
,n_attribute6
,n_attribute7
,n_attribute8
,n_attribute9
,n_attribute10
,status_id
,territory_code
,time_since_new
,cycles_since_new
,time_since_overhaul
,cycles_since_overhaul
,time_since_repair
,cycles_since_repair
,time_since_visit
,cycles_since_visit
,time_since_mark
,cycles_since_mark
,number_of_repairs
,product_code
,product_transaction_id )
VALUES
( l_msnt.transaction_temp_id
,l_sysdate
,l_msnt.last_updated_by
,l_sysdate
,l_msnt.created_by
,-1
,l_serial_numbers(j)
,l_msnt.inventory_item_id
,l_msnt.organization_id
,NULL -- sub
,NULL -- loc
,l_msnt.transaction_date
,NULL -- txn souce id
,l_msnt.transaction_source_type_id
,l_msnt.transaction_source_name
,l_msnt.receipt_issue_type
,NULL -- customer id
,NULL -- ship_id
,l_msnt.serial_attribute_category
,l_msnt.origination_date
,l_msnt.c_attribute1
,l_msnt.c_attribute2
,l_msnt.c_attribute3
,l_msnt.c_attribute4
,l_msnt.c_attribute5
,l_msnt.c_attribute6
,l_msnt.c_attribute7
,l_msnt.c_attribute8
,l_msnt.c_attribute9
,l_msnt.c_attribute10
,l_msnt.c_attribute11
,l_msnt.c_attribute12
,l_msnt.c_attribute13
,l_msnt.c_attribute14
,l_msnt.c_attribute15
,l_msnt.c_attribute16
,l_msnt.c_attribute17
,l_msnt.c_attribute18
,l_msnt.c_attribute19
,l_msnt.c_attribute20
,l_msnt.d_attribute1
,l_msnt.d_attribute2
,l_msnt.d_attribute3
,l_msnt.d_attribute4
,l_msnt.d_attribute5
,l_msnt.d_attribute6
,l_msnt.d_attribute7
,l_msnt.d_attribute8
,l_msnt.d_attribute9
,l_msnt.d_attribute10
,l_msnt.n_attribute1
,l_msnt.n_attribute2
,l_msnt.n_attribute3
,l_msnt.n_attribute4
,l_msnt.n_attribute5
,l_msnt.n_attribute6
,l_msnt.n_attribute7
,l_msnt.n_attribute8
,l_msnt.n_attribute9
,l_msnt.n_attribute10
,l_msnt.status_id
,l_msnt.territory_code
,l_msnt.time_since_new
,l_msnt.cycles_since_new
,l_msnt.time_since_overhaul
,l_msnt.cycles_since_overhaul
,l_msnt.time_since_repair
,l_msnt.cycles_since_repair
,l_msnt.time_since_visit
,l_msnt.cycles_since_visit
,l_msnt.time_since_mark
,l_msnt.cycles_since_mark
,l_msnt.number_of_repairs
,'RCV'
,p_prod_txn_id );
DELETE FROM mtl_transaction_lots_temp
WHERE product_code = 'RCV'
AND product_transaction_id = p_prod_txn_tmp_id;
DELETE FROM mtl_serial_numbers_temp
WHERE product_code = 'RCV'
AND product_transaction_id = p_prod_txn_tmp_id;
SELECT
rsl.asn_lpn_id asn_lpn_id
,rsl.item_id item_id
,rsl.item_revision revision
,rsl.to_organization_id organization_id
,rsl.unit_of_measure unit_of_measure
,msi.primary_uom_code prim_uom_code
,(Nvl(rsl.quantity_shipped,0) - Nvl(rsl.quantity_received,0)) quantity_to_unpack
-- OPMConvergence
,rsl.secondary_unit_of_measure sec_unit_of_measure
,msi.secondary_uom_code sec_uom_code
,(Nvl(rsl.secondary_quantity_shipped,0) - Nvl(rsl.secondary_quantity_received,0)) sec_quantity_to_unpack
-- OPMConvergence
,Nvl(rsl.quantity_received,0)
,msi.lot_control_code lot_control_code
,msi.serial_number_control_code serial_number_control_code
-- ,wlpn.lpn_context lpn_context
,rsl.po_line_location_id
,rsl.po_header_id
INTO
l_asn_lpn_id
,l_item_id
,l_revision
,l_org_id
,l_unit_of_measure
,l_prim_uom_code
,l_quantity_to_unpack
-- OPMConvergence
,l_sec_unit_of_measure
,l_sec_uom_code
,l_sec_quantity_to_unpack
-- OPMConvergence
,l_quantity_received
,l_lot_control_code
,l_serial_number_control_code
-- ,l_lpn_context
,l_po_line_location_id
,l_po_header_id
FROM
rcv_shipment_lines rsl
,mtl_system_items msi
WHERE
rsl.shipment_line_id = p_shipment_line_id
AND rsl.shipment_header_id = p_shipment_header_id
AND msi.inventory_item_id = rsl.item_id
AND msi.organization_id = rsl.to_organization_id;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_asn_lpn_id;
SELECT
serial_num
,lot_num
BULK collect INTO
l_serial_numbers
,l_lot_numbers
FROM
rcv_serials_supply
WHERE
shipment_line_id = p_shipment_line_id
AND supply_type_code = 'SHIPMENT';
SELECT uom_code
into l_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id
AND unit_of_measure = l_unit_of_measure;
SELECT
lot_num
,SUM(quantity)
-- OPMConvergence
,SUM(secondary_quantity)
-- OPMConvergence
bulk collect INTO
l_lot_numbers
,l_quantitys
-- OPMConvergence
,l_sec_lot_qty
-- OPMConvergence
FROM
rcv_lots_supply
WHERE
shipment_line_id = p_shipment_line_id
AND supply_type_code = 'SHIPMENT'
GROUP BY lot_num;
SELECT uom_code
into l_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id
AND unit_of_measure = l_unit_of_measure;
SELECT uom_code
INTO l_sec_uom_code_2
FROM mtl_item_uoms_view
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id
AND unit_of_measure = l_sec_unit_of_measure;
SELECT uom_code
into l_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id
AND unit_of_measure = l_unit_of_measure;
SELECT uom_code
INTO l_sec_uom_code_2
FROM mtl_item_uoms_view
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id
AND unit_of_measure = l_sec_unit_of_measure;
SELECT Count(*)
INTO l_serials_count
FROM rcv_serials_supply
WHERE shipment_line_id = p_shipment_line_id
AND supply_type_code = 'SHIPMENT';
SELECT Count(*)
INTO l_lots_count
FROM rcv_lots_supply
WHERE shipment_line_id = p_shipment_line_id
AND supply_type_code = 'SHIPMENT';
UPDATE mtl_serial_numbers
SET current_status = 1,
group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
WHERE inventory_item_id = l_item_id
AND current_organization_id = l_org_id
AND serial_number IN (SELECT serial_num
FROM rcv_serials_supply
WHERE shipment_line_id = p_shipment_line_id
AND supply_type_code = 'SHIPMENT')
AND current_status = 5;
DELETE FROM rcv_serials_supply
WHERE shipment_line_id = p_shipment_line_id
AND supply_type_code = 'SHIPMENT';
DELETE FROM rcv_lots_supply
WHERE shipment_line_id = p_shipment_line_id
AND supply_type_code = 'SHIPMENT';
SELECT Nvl(SUM(transaction_quantity),0)
INTO l_total_lot_qty
FROM mtl_transaction_lots_temp
WHERE product_code = p_product_code
AND product_transaction_id = p_product_txn_id;
SELECT Nvl(SUM(inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number)),0)
INTO l_total_ser_qty
FROM mtl_serial_numbers_temp
WHERE product_code = p_product_code
AND product_transaction_id = p_product_txn_id;
-- Loop Through MTLT MSNT and INSERT
l_progress := 'WMSINB-11342';
For l_lot_rec in ( select Ltrim(Rtrim(lot_number)) lot_number
, primary_quantity
, serial_transaction_temp_id
from mtl_transaction_lots_temp mtlt
where mtlt.product_transaction_id = p_product_txn_id
and mtlt.product_code = 'RCV'
)
Loop
l_progress := 'WMSINB-11351';
For l_serial_rec in ( select fm_serial_number
, to_serial_number
, inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number) serial_quantity
from mtl_serial_numbers_temp msnt
where msnt.transaction_temp_id = l_lot_rec.serial_transaction_temp_id
and msnt.product_transaction_id = p_product_txn_id
and msnt.product_code = 'RCV'
)
Loop
l_progress := 'WMSINB-11361';
-- Loop Through MTLT And INSERT
l_progress := 'WMSINB-11404';
For l_lot_rec in ( select Ltrim(Rtrim(lot_number)) lot_number
, primary_quantity
-- R12
, transaction_quantity
-- R12
-- OPMConvergence
, secondary_quantity
-- OPMConvergence
from mtl_transaction_lots_temp mtlt
where mtlt.product_transaction_id = p_product_txn_id
and mtlt.product_code = 'RCV'
)
Loop
l_progress := 'WMSINB-11412';
For l_serial_rec in ( select fm_serial_number
,to_serial_number
,inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number) serial_quantity
from mtl_serial_numbers_temp msnt
where msnt.product_transaction_id = p_product_txn_id
and msnt.product_code = 'RCV'
)
Loop
-- Calculate the Quantity
IF (l_debug = 1) THEN
print_debug('PACKUNPACK_WRAPPER: packing fm serial :'||l_serial_rec.fm_serial_number,1);
select lpn_id,
license_plate_number,
lpn_context,
parent_lpn_id,
source_header_id
into x_lpn_id,
x_license_plate_number,
x_lpn_context,
x_parent_lpn_id,
x_source_header_id
from wms_license_plate_numbers
where lpn_id = x_lpn_id
and license_plate_number = x_license_plate_number
;
select lpn_id,
license_plate_number,
lpn_context,
parent_lpn_id,
source_header_id
into x_lpn_id,
x_license_plate_number,
x_lpn_context,
x_parent_lpn_id,
x_source_header_id
from wms_license_plate_numbers
where lpn_id = x_lpn_id
;
select lpn_id,
license_plate_number,
lpn_context,
parent_lpn_id,
source_header_id
into x_lpn_id,
x_license_plate_number,
x_lpn_context,
x_parent_lpn_id,
x_source_header_id
from wms_license_plate_numbers
where license_plate_number = x_license_plate_number
;
SELECT
interface_transaction_id
,to_organization_id
,to_organization_code
,item_id
,item_num
,lpn_group_id
,quantity
,primary_quantity
,document_num
FROM
rcv_transactions_interface
WHERE
group_id = l_grp_id AND
transaction_type = 'SHIP';
SELECT
wmslpnci.interface_transaction_id interface_transaction_id,
wmslpn.lpn_id lpn_id,
wmslpnci.license_plate_number license_plate_number,
wmslpn.organization_id organization_id,
wmslpnci.serial_transaction_intf_id,
wmslpnci.lot_number lot_number,
wmslpnci.quantity quantity,
wmslpnci.uom_code uom_code,
wmslpnci.expiration_date expiration_date,
wmslpnci.status_id status_id,
wmslpnci.item_description item_description,
wmslpnci.vendor_item_num vendor_item_num,
wmslpnci.supplier_lot_number supplier_lot_number,
wmslpnci.origination_date origination_date,
wmslpnci.date_code date_code,
wmslpnci.grade_code grade_code,
wmslpnci.change_date change_date,
wmslpnci.maturity_date maturity_date,
wmslpnci.retest_date retest_date,
wmslpnci.age age,
wmslpnci.item_size item_size,
wmslpnci.color color,
wmslpnci.volume volume,
wmslpnci.place_of_origin place_of_origin,
wmslpnci.best_by_date best_by_date,
wmslpnci.length length,
wmslpnci.length_uom_code length_uom_code,
wmslpnci.recycled_content recycled_content,
wmslpnci.thickness thickness,
wmslpnci.thickness thickness_uom_code,
wmslpnci.width width,
wmslpnci.width_uom_code width_uom_code,
wmslpnci.curl_wrinkle_fold curl_wrinkle_fold,
wmslpnci.c_attribute1 c_attribute1,
wmslpnci.c_attribute2 c_attribute2,
wmslpnci.c_attribute3 c_attribute3,
wmslpnci.c_attribute4 c_attribute4,
wmslpnci.c_attribute5 c_attribute5,
wmslpnci.c_attribute6 c_attribute6,
wmslpnci.c_attribute7 c_attribute7,
wmslpnci.c_attribute8 c_attribute8,
wmslpnci.c_attribute9 c_attribute9,
wmslpnci.c_attribute10 c_attribute10,
wmslpnci.c_attribute11 c_attribute11,
wmslpnci.c_attribute12 c_attribute12,
wmslpnci.c_attribute13 c_attribute13,
wmslpnci.c_attribute14 c_attribute14,
wmslpnci.c_attribute15 c_attribute15,
wmslpnci.c_attribute16 c_attribute16,
wmslpnci.c_attribute17 c_attribute17,
wmslpnci.c_attribute18 c_attribute18,
wmslpnci.c_attribute19 c_attribute19,
wmslpnci.c_attribute20 c_attribute20,
wmslpnci.d_attribute1 d_attribute1,
wmslpnci.d_attribute2 d_attribute2,
wmslpnci.d_attribute3 d_attribute3,
wmslpnci.d_attribute4 d_attribute4,
wmslpnci.d_attribute5 d_attribute5,
wmslpnci.d_attribute6 d_attribute6,
wmslpnci.d_attribute7 d_attribute7,
wmslpnci.d_attribute8 d_attribute8,
wmslpnci.d_attribute9 d_attribute9,
wmslpnci.d_attribute10 d_attribute10,
wmslpnci.n_attribute1 n_attribute1,
wmslpnci.n_attribute2 n_attribute2,
wmslpnci.n_attribute3 n_attribute3,
wmslpnci.n_attribute4 n_attribute4,
wmslpnci.n_attribute5 n_attribute5,
wmslpnci.n_attribute6 n_attribute6,
wmslpnci.n_attribute7 n_attribute7,
wmslpnci.n_attribute8 n_attribute8,
wmslpnci.n_attribute9 n_attribute9,
wmslpnci.n_attribute10 n_attribute10,
wmslpnci.attribute_category attribute_category,
wmslpnci.attribute1 attribute1,
wmslpnci.attribute2 attribute2,
wmslpnci.attribute3 attribute3,
wmslpnci.attribute4 attribute4,
wmslpnci.attribute5 attribute5,
wmslpnci.attribute6 attribute6,
wmslpnci.attribute7 attribute7,
wmslpnci.attribute8 attribute8,
wmslpnci.attribute9 attribute9,
wmslpnci.attribute10 attribute10,
wmslpnci.attribute11 attribute11,
wmslpnci.attribute12 attribute12,
wmslpnci.attribute13 attribute13,
wmslpnci.attribute14 attribute14,
wmslpnci.attribute15 attribute15
FROM
wms_lpn_contents_interface wmslpnci,
wms_license_plate_numbers wmslpn
WHERE
wmslpnci.interface_transaction_id = l_intf_txn_id AND
wmslpn.license_plate_number (+)= wmslpnci.license_plate_number;
SELECT
fm_serial_number,
to_serial_number
FROM
mtl_serial_numbers_interface
WHERE l_intf_txn_id = transaction_interface_id;
SELECT DISTINCT pll.ship_to_organization_id
INTO l_rti_rec.to_organization_id
FROM po_line_locations pll, po_headers ph, po_lines pl,
mtl_system_items_kfv msik
WHERE ph.segment1 = l_rti_rec.document_num
AND ((msik.inventory_item_id = l_rti_rec.item_id)
OR (msik.concatenated_segments = l_rti_rec.item_num
AND l_rti_rec.item_id IS NULL))
AND pl.item_id = msik.inventory_item_id
AND pll.po_line_id = pl.po_line_id
AND pl.po_header_id = ph.po_header_id
AND pll.po_header_id = ph.po_header_id
AND ph.type_lookup_code in ('STANDARD','BLANKET','PLANNED')
AND ROWNUM = 1;
SELECT DISTINCT msik.primary_uom_code,
msik.inventory_item_id,
mp.organization_id
INTO l_prim_uom_code,
l_item_id,
l_to_organization_id
FROM mtl_system_items_kfv msik,
mtl_parameters mp
WHERE mp.organization_id = l_rti_rec.to_organization_id
AND msik.inventory_item_id = l_rti_rec.item_id
AND mp.organization_id = msik.organization_id;
SELECT DISTINCT msik.primary_uom_code,
msik.inventory_item_id,
mp.organization_id
INTO l_prim_uom_code,
l_item_id,
l_to_organization_id
FROM mtl_system_items_kfv msik,
mtl_parameters mp
WHERE mp.organization_id = l_rti_rec.to_organization_id
AND msik.concatenated_segments = l_rti_rec.item_num
AND mp.organization_id = msik.organization_id;
SELECT DISTINCT msik.primary_uom_code,
msik.inventory_item_id,
mp.organization_id
INTO l_prim_uom_code,
l_item_id,
l_to_organization_id
FROM mtl_system_items_kfv msik,
mtl_parameters mp
WHERE mp.organization_code = l_rti_rec.to_organization_code
AND msik.inventory_item_id = l_rti_rec.item_id
AND mp.organization_id = msik.organization_id;
SELECT DISTINCT msik.primary_uom_code,
msik.inventory_item_id,
mp.organization_id
INTO l_prim_uom_code,
l_item_id,
l_to_organization_id
FROM mtl_system_items_kfv msik,
mtl_parameters mp
WHERE mp.organization_code = l_rti_rec.to_organization_code
AND msik.concatenated_segments = l_rti_rec.item_num
AND mp.organization_id = msik.organization_id;
UPDATE wms_lpn_interface
SET source_group_id = l_rti_rec.lpn_group_id
,organization_id = l_rti_rec.to_organization_id
WHERE source_group_id = p_group_id
AND EXISTS (SELECT 1 --Only for EDI TXN we are doing this
--And EDI TXN will have WLPNCI
FROM wms_lpn_contents_interface
WHERE group_id = p_group_id);
print_debug('MODIFY_EDI_XML_ASN: Error update source_group_id OF WLPNI',1);
-- Only insert wlpni if lpn_id is NULL
IF (l_wlpnci_rec.lpn_id IS NULL) THEN
check_lpn_in_wlpni
(p_license_plate_number => l_wlpnci_rec.license_plate_number
,p_lpn_id => l_wlpnci_rec.lpn_id
,p_lpn_group_id => l_rti_rec.lpn_group_id
,x_exists => l_exists);
-- All wlpnci will have the same lpn_id. So just insert_wlpni once
IF (l_debug = 1) THEN
print_debug('MODIFY_EDI_XML_ASN: Calling insert_wlpni(', 1);
inv_rcv_integration_apis.insert_wlpni
(p_api_version => 1.0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => l_rti_rec.to_organization_id
,p_lpn_id => l_wlpnci_rec.lpn_id
,p_license_plate_number => l_wlpnci_rec.license_plate_number
--,p_lpn_group_id => p_group_id --??
,p_lpn_group_id => l_rti_rec.lpn_group_id --Changed to pass the lpn_group_id otherwise it would fail in validate_lpn_info
);
print_debug('MODIFY_EDI_XML_ASN: Error in insert_wlpni', 1);
l_wlpnci_rec.lpn_id ||' inserted successfully', 1);
UPDATE rcv_transactions_interface
SET lpn_id = l_wlpnci_rec.lpn_id,
license_plate_number = l_wlpnci_rec.license_plate_number
WHERE interface_transaction_id = l_rti_rec.interface_transaction_id;
-- l_serial_txn_intf_id will be updated if item is lot controlled
-- otherwise, use the same one in wlpnci
l_serial_txn_intf_id := l_wlpnci_rec.serial_transaction_intf_id;
print_debug('MODIFY_EDI_XML_ASN: Lot number is not null in WLPNCI. inserting MTLI',1);
inv_rcv_integration_apis.insert_mtli
(p_api_version => 1.0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_transaction_interface_id => l_txn_intf_id
,p_lot_number => ltrim(rtrim(l_wlpnci_rec.lot_number))
,p_transaction_quantity => l_wlpnci_rec.quantity
,p_primary_quantity => l_prim_qty
,p_organization_id => l_rti_rec.to_organization_id -- l_wlpnci_rec.organization_id
,p_inventory_item_id => l_rti_rec.item_id
,p_expiration_date => l_wlpnci_rec.expiration_date
,p_status_id => l_wlpnci_rec.status_id
,x_serial_transaction_temp_id => l_tmp_ser_txn_id -- update
,p_product_transaction_id => l_rti_rec.interface_transaction_id
,p_product_code => 'RCV'
,p_description => l_wlpnci_rec.item_description --??
,p_vendor_name => l_wlpnci_rec.vendor_item_num --??
,p_supplier_lot_number => l_wlpnci_rec.supplier_lot_number
,p_origination_date => l_wlpnci_rec.origination_date
,p_date_code => l_wlpnci_rec.date_code
,p_grade_code => l_wlpnci_rec.grade_code
,p_change_date => l_wlpnci_rec.change_date
,p_maturity_date => l_wlpnci_rec.maturity_date
,p_retest_date => l_wlpnci_rec.retest_date
,p_age => l_wlpnci_rec.age
,p_item_size => l_wlpnci_rec.item_size
,p_color => l_wlpnci_rec.color
,p_volume => l_wlpnci_rec.volume
,p_place_of_origin => l_wlpnci_rec.place_of_origin
,p_best_by_date => l_wlpnci_rec.best_by_date
,p_length => l_wlpnci_rec.length
,p_length_uom => l_wlpnci_rec.length_uom_code
,p_recycled_content => l_wlpnci_rec.recycled_content
,p_thickness => l_wlpnci_rec.thickness
,p_thickness_uom => l_wlpnci_rec.thickness_uom_code
,p_width => l_wlpnci_rec.width
,p_width_uom => l_wlpnci_rec.width_uom_code
,p_curl_wrinkle_fold => l_wlpnci_rec.curl_wrinkle_fold
,p_c_attribute1 => l_wlpnci_rec.c_attribute1
,p_c_attribute2 => l_wlpnci_rec.c_attribute2
,p_c_attribute3 => l_wlpnci_rec.c_attribute3
,p_c_attribute4 => l_wlpnci_rec.c_attribute4
,p_c_attribute5 => l_wlpnci_rec.c_attribute5
,p_c_attribute6 => l_wlpnci_rec.c_attribute6
,p_c_attribute7 => l_wlpnci_rec.c_attribute7
,p_c_attribute8 => l_wlpnci_rec.c_attribute8
,p_c_attribute9 => l_wlpnci_rec.c_attribute9
,p_c_attribute10 => l_wlpnci_rec.c_attribute10
,p_c_attribute11 => l_wlpnci_rec.c_attribute11
,p_c_attribute12 => l_wlpnci_rec.c_attribute12
,p_c_attribute13 => l_wlpnci_rec.c_attribute13
,p_c_attribute14 => l_wlpnci_rec.c_attribute14
,p_c_attribute15 => l_wlpnci_rec.c_attribute15
,p_c_attribute16 => l_wlpnci_rec.c_attribute16
,p_c_attribute17 => l_wlpnci_rec.c_attribute17
,p_c_attribute18 => l_wlpnci_rec.c_attribute18
,p_c_attribute19 => l_wlpnci_rec.c_attribute19
,p_c_attribute20 => l_wlpnci_rec.c_attribute20
,p_d_attribute1 => l_wlpnci_rec.d_attribute1
,p_d_attribute2 => l_wlpnci_rec.d_attribute2
,p_d_attribute3 => l_wlpnci_rec.d_attribute3
,p_d_attribute4 => l_wlpnci_rec.d_attribute4
,p_d_attribute5 => l_wlpnci_rec.d_attribute5
,p_d_attribute6 => l_wlpnci_rec.d_attribute6
,p_d_attribute7 => l_wlpnci_rec.d_attribute7
,p_d_attribute8 => l_wlpnci_rec.d_attribute8
,p_d_attribute9 => l_wlpnci_rec.d_attribute9
,p_d_attribute10 => l_wlpnci_rec.d_attribute10
,p_n_attribute1 => l_wlpnci_rec.n_attribute1
,p_n_attribute2 => l_wlpnci_rec.n_attribute2
,p_n_attribute3 => l_wlpnci_rec.n_attribute3
,p_n_attribute4 => l_wlpnci_rec.n_attribute4
,p_n_attribute5 => l_wlpnci_rec.n_attribute5
,p_n_attribute6 => l_wlpnci_rec.n_attribute6
,p_n_attribute7 => l_wlpnci_rec.n_attribute7
,p_n_attribute8 => l_wlpnci_rec.n_attribute8
,p_n_attribute9 => l_wlpnci_rec.n_attribute9
,p_n_attribute10 => l_wlpnci_rec.n_attribute10
,p_attribute_category => l_wlpnci_rec.attribute_category
,p_attribute1 => l_wlpnci_rec.attribute1
,p_attribute2 => l_wlpnci_rec.attribute2
,p_attribute3 => l_wlpnci_rec.attribute3
,p_attribute4 => l_wlpnci_rec.attribute4
,p_attribute5 => l_wlpnci_rec.attribute5
,p_attribute6 => l_wlpnci_rec.attribute6
,p_attribute7 => l_wlpnci_rec.attribute7
,p_attribute8 => l_wlpnci_rec.attribute8
,p_attribute9 => l_wlpnci_rec.attribute9
,p_attribute10 => l_wlpnci_rec.attribute10
,p_attribute11 => l_wlpnci_rec.attribute11
,p_attribute12 => l_wlpnci_rec.attribute12
,p_attribute13 => l_wlpnci_rec.attribute13
,p_attribute14 => l_wlpnci_rec.attribute14
,p_attribute15 => l_wlpnci_rec.attribute15
);
print_debug('MODIFY_EDI_XML_ASN: Error in insert_mtli', 1);
-- Now update the product code and product_intf_id
IF (l_msni_total_qty > 0) THEN
BEGIN
UPDATE mtl_serial_numbers_interface
SET product_code = 'RCV',
product_transaction_id=l_rti_rec.interface_transaction_id,
transaction_interface_id = l_serial_txn_intf_id
--WHERE transaction_interface_id = l_wlpnci_rec.interface_transaction_id;
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = l_serial_txn_intf_id
WHERE product_code = 'RCV'
AND serial_transaction_temp_id=l_tmp_ser_txn_id;
*********************** update the lpn_group_id for all RTIs with LPNs*
for l_error_row in ( select interface_transaction_id,
group_id
from rcv_transactions_interface
where lpn_group_id is null
and ( lpn_id is not null
or license_plate_number is not null
or transfer_lpn_id is not null
or transfer_license_plate_number is not null
)
and processing_status_code in ('RUNNING')
AND (processing_request_id IS NULL
OR processing_request_id = p_request_id)
AND group_id = Decode(p_group_id,0,group_id,NULL,group_id,p_group_id)
) Loop
-- Call PO's API to mark the Error
IF (l_debug = 1) THEN
print_debug('Explode_LPN failed for interface_transaction_id : '|| l_error_row.interface_transaction_id ,1);
FOR l_lpn_grp_null IN (SELECT interface_transaction_id
FROM rcv_transactions_interface
WHERE Nvl(item_id, -1) = -1
AND item_num IS NULL
AND item_description IS NULL
AND lpn_group_id IS NULL
AND (lpn_id IS NOT NULL
OR license_plate_number IS NOT NULL)
AND transaction_type <> 'SHIP'
AND processing_status_code in ('RUNNING')
AND (processing_request_id IS NULL
OR processing_request_id = p_request_id)
AND group_id = Decode(p_group_id,0,group_id,NULL,group_id,p_group_id))
LOOP
IF (l_debug = 1) THEN
print_debug('EXPLODE_LPN - Case for explosion without LPN_GROUP_ID. Updating for RTI: '||l_lpn_grp_null.interface_transaction_id,1);
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO l_lpn_grp_id
FROM DUAL;
UPDATE rcv_transactions_interface
SET lpn_group_id = l_lpn_grp_id
WHERE interface_transaction_id = l_lpn_grp_null.interface_transaction_id;
--UPDATE rcv_transactions_interface SET lpn_group_id = group_id
--WHERE lpn_group_id IS NULL
--AND (lpn_id IS NOT NULL
-- OR license_plate_number IS NOT NULL
-- OR transfer_lpn_id IS NOT NULL
-- OR transfer_license_plate_number IS NOT NULL
-- )
-- AND processing_status_code = 'RUNNING'
-- AND (processing_request_id IS NULL
-- OR processing_request_id = p_request_id)
-- AND group_id = Decode(p_group_id,0,group_id,NULL,group_id,p_group_id);
FOR l_rti_rec IN ( SELECT DISTINCT lpn_group_id, group_id
FROM rcv_transactions_interface rti
WHERE lpn_group_id IS NOT NULL
AND Nvl(item_id, -1) = -1
AND item_num IS NULL
AND item_description IS NULL
AND transaction_type <> 'SHIP'
AND processing_status_code in ('RUNNING')
AND (processing_request_id IS NULL
OR processing_request_id = p_request_id)
AND group_id = Decode(p_group_id,0,group_id,NULL,group_id,p_group_id)) LOOP
IF (l_debug = 1) THEN
print_debug('Explode_LPN_contents called for LPN_GROUP = '||l_rti_rec.lpn_group_id ,1);
--FOR l_asn_lpn_grp_null IN (SELECT DISTINCT shipment_num
FOR l_asn_lpn_grp_null IN (SELECT DISTINCT header_interface_id,
shipment_num
FROM rcv_transactions_interface rti
WHERE rti.lpn_group_id IS NULL
AND ( ( (rti.lpn_id IS NOT NULL
OR rti.license_plate_number IS NOT NULL)
) or
(exists ( select 'x' from wms_lpn_contents_interface wlc
where wlc.interface_transaction_id =
rti.interface_transaction_id
)
)
)
AND rti.processing_status_code = 'RUNNING'
AND (rti.processing_request_id IS NULL
OR rti.processing_request_id = p_request_id)
AND rti.transaction_type = 'SHIP'
AND rti.source_document_code = 'PO'
AND (rti.item_id IS NOT NULL
OR rti.item_num IS NOT NULL)
AND rti.group_id = Decode(p_group_id,0,rti.group_id,NULL,rti.group_id,p_group_id))
LOOP
IF (l_debug = 1) THEN
print_debug('EXPLODE_LPN - SHIP Updating lpn_group for SHIPMENT: '||l_asn_lpn_grp_null.shipment_num,1);
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO l_lpn_grp_id
FROM DUAL;
UPDATE rcv_transactions_interface
SET lpn_group_id = l_lpn_grp_id
-- don't use shipment_num see update above ****
-- WHERE shipment_num = l_asn_lpn_grp_null.shipment_num
WHERE header_interface_id = l_asn_lpn_grp_null.header_interface_id
AND lpn_group_id is null
AND processing_status_code = 'RUNNING'
AND (processing_request_id IS NULL
OR processing_request_id = p_request_id)
AND transaction_type = 'SHIP'
AND source_document_code = 'PO'
AND group_id = Decode(p_group_id,0,group_id,NULL,group_id,p_group_id);
FOR l_asn_rec IN ( SELECT DISTINCT group_id,processing_mode_code
FROM rcv_transactions_interface rti
WHERE processing_status_code = 'RUNNING'
AND (processing_request_id IS NULL
OR processing_request_id = p_request_id)
AND transaction_type = 'SHIP'
AND source_document_code = 'PO'
AND lpn_group_id IS NOT NULL
AND (item_id IS NOT NULL OR
item_num IS NOT NULL)
AND group_id = Decode(p_group_id,0,group_id,NULL,group_id,p_group_id)) LOOP
IF (l_debug = 1) THEN
print_debug('Modify_edi_xml_asn called for GROUP = '||l_asn_rec.group_id ,1);
FOR l_err_row IN (SELECT interface_transaction_id
FROM rcv_transactions_interface
WHERE group_id = l_asn_rec.group_id) LOOP
-- Call PO's API to mark the Error
rcv_roi_preprocessor.explode_lpn_failed
(x_interface_txn_id => l_err_row.interface_transaction_id,
x_group_id => l_asn_rec.group_id,
x_lpn_group_id => NULL);
-- Delete the Original row for Explosion
-- Bug 3618348. The code below is changed from performance reasons
/*
DELETE FROM rcv_transactions_interface
WHERE Nvl(item_id, -1) = -1
AND item_description IS NULL
AND Nvl(quantity,0) = 0
AND lpn_group_id IS NOT NULL
AND processing_status_code = 'RUNNING'
AND (processing_request_id IS NULL
OR processing_request_id = p_request_id)
AND group_id = Decode(p_group_id,0,group_id,NULL,group_id,p_group_id);
DELETE FROM rcv_transactions_interface
WHERE Nvl(item_id, -1) = -1
AND item_description IS NULL
AND Nvl(quantity,0) = 0
AND lpn_group_id IS NOT NULL
AND processing_status_code = 'RUNNING'
-- Bug 3714354
and ( lpn_id is not null or license_plate_number is not null )
AND (processing_request_id IS NULL
OR processing_request_id = p_request_id) ;
DELETE FROM rcv_transactions_interface
WHERE Nvl(item_id, -1) = -1
AND item_description IS NULL
AND Nvl(quantity,0) = 0
AND lpn_group_id IS NOT NULL
AND processing_status_code = 'RUNNING'
-- Bug 3714354
and ( lpn_id is not null or license_plate_number is not null )
AND (processing_request_id IS NULL
OR processing_request_id = p_request_id)
AND group_id = p_group_id;
PROCEDURE update_serial_status ( p_product_txn_id IN NUMBER
,p_item_id IN NUMBER
,p_organization_id IN NUMBER
,p_serial_status IN NUMBER
,p_inspection_status IN NUMBER
,p_sub IN VARCHAR2 default null
,p_locator_id IN NUMBER default null
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
select count(*)
into l_count
from mtl_serial_numbers_temp
where product_code = 'RCV'
and product_transaction_id = p_product_txn_id;
print_debug('update_serial_status : l_count = '||l_count, 1);
SELECT Nvl(comms_nl_trackable_flag,'N')
INTO l_comms_nl_trackable_flag
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id= p_organization_id;
SELECT nvl(destination_type_code,'dummy')
INTO l_destination_type
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_product_txn_id;
update mtl_serial_numbers msn
set msn.current_status = p_serial_status
, inspection_status = p_inspection_status
, group_mark_id = NULL
, line_mark_id = decode(l_clear_line_mark_id, 1, NULL, line_mark_id)
, lot_line_mark_id = decode(l_clear_line_mark_id, 1, NULL, lot_line_mark_id)
, current_organization_id = p_organization_id
, owning_organization_id = decode(owning_tp_type, 2, p_organization_id, owning_organization_id)
, planning_organization_id = decode(planning_tp_type, 2, p_organization_id, planning_organization_id)
, current_subinventory_code = p_sub
, current_locator_id = p_locator_id
where
-- msn.inventory_item_id = p_item_id
-- and msn.current_organization_id = p_organization_id -- this part is not needed as this was causing INTSHIP RECEIVE to fail
-- BUG 5611567
msn.ROWID in (
select msn1.ROWID
from mtl_serial_numbers msn1
, mtl_serial_numbers_temp msnt
where msn1.inventory_item_id = p_item_id
and msnt.product_code = 'RCV'
and msnt.product_transaction_id = p_product_txn_id
and msn1.serial_number between msnt.fm_serial_number and msnt.to_serial_number
AND Length(msn1.serial_number) = Length(msnt.fm_serial_number)
AND length(msnt.fm_serial_number)=Length(nvl(msnt.to_serial_number,msnt.fm_serial_number)) --BUG 3818544
);
print_debug('update_serial_status : - other exception:'|| l_progress || ' ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')|| 'Error =' || SQLCODE, 1);
inv_mobile_helper_functions.sql_error('inv_rcv_integration_pvt.update_serial_status',l_progress, SQLCODE);
End update_serial_status;
PROCEDURE update_lpn_location_context ( p_organization_id IN NUMBER
,p_sub IN VARCHAR2
,p_locator IN NUMBER
,p_lpn_context IN NUMBER
,p_lpn_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_source_name IN VARCHAR2 DEFAULT NULL
,p_source_header_id IN NUMBER DEFAULT NULL
,p_source_type_id IN NUMBER DEFAULT NULL) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
print_debug(' update_lpn_location_context : lpn_id = '||p_lpn_id,1);
print_debug(' update_lpn_location_context : lpn_context = '|| p_lpn_context , 1);
print_debug(' update_lpn_location_context : sub = '|| p_sub , 1);
print_debug(' update_lpn_location_context : locator = '|| p_locator , 1);
print_debug(' update_lpn_location_context : organization_id = '|| p_organization_id , 1);
print_debug(' update_lpn_location_context : source_header_id = '|| p_source_header_id , 1);
print_debug(' update_lpn_location_context : source_name = '|| p_source_name , 1);
print_debug(' update_lpn_location_context : source_type_id = '|| p_source_type_id , 1);
print_debug('update_lpn_location_context : - other exception:'|| l_progress || ' ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')|| 'Error =' || SQLCODE, 1);
inv_mobile_helper_functions.sql_error('inv_rcv_integration_pvt.update_lpn_location_context',l_progress, SQLCODE);
END update_lpn_location_context ;
select 1
into l_same_doc
from PO_LINE_LOCATIONS_ALL POLL
where POLL.LINE_LOCATION_ID = p_old_reference_id
and POLL.PO_HEADER_ID in ( select POLL1.PO_HEADER_ID
from PO_LINE_LOCATIONS_ALL POLL1
where POLL1.LINE_LOCATION_ID
= p_new_reference_id )
and rownum < 2;
select 1
into l_same_doc
from OE_ORDER_LINES_ALL OEL
where OEL.LINE_ID = p_old_reference_id
and OEL.HEADER_ID in ( select OEL1.HEADER_ID
from OE_ORDER_LINES_ALL OEL1
where OEL1.LINE_ID
= p_new_reference_id )
and rownum < 2;
select nvl(asn_line_flag,'N')
into l_asn_line_flag
from rcv_shipment_lines rsl
where rsl.shipment_line_id = p_old_reference_id
;
SELECT 1
INTO l_same_doc
FROM rcv_shipment_lines rsl, po_line_locations_all poll
WHERE rsl.shipment_line_id = p_old_reference_id
AND poll.line_location_id = rsl.po_line_location_id
AND poll.po_header_id in ( select poll1.po_header_id
from rcv_shipment_lines rsl1, po_line_locations_all poll1
where rsl1.shipment_line_id = p_new_reference_id
and poll1.line_location_id = rsl1.po_line_location_id )
AND rownum < 2;
SELECT 1
INTO l_same_doc
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = p_old_reference_id
AND rsl.shipment_header_id in ( select rsl1.shipment_header_id
from rcv_shipment_lines rsl1
where rsl1.shipment_line_id = p_new_reference_id
)
AND rownum < 2;
SELECT DISTINCT mtrl.line_id
, 1 UOM_ORDERING
, mmtt.transaction_temp_id
, mtrl.wms_process_flag
, 1 quantity_ordering
, (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) quantity
, mtrl.primary_quantity
, mtrl.uom_code
, mtrl.lpn_id
, mtrl.inventory_item_id
, mtrl.lot_number
-- OPMConvergence
, (mtrl.secondary_quantity - Nvl(mtrl.secondary_quantity_delivered, 0)) secondary_quantity_2
, mtrl.secondary_quantity
, mtrl.secondary_uom_code
, mtrl.crossdock_type
-- OPMConvergence
, mtrl.backorder_delivery_detail_id
, mmtt.wip_supply_type
, mtrl.reference
, mtrl.reference_type_code
, mtrl.reference_id
, mtrl.quantity txn_qty
, mtrl.quantity_detailed quantity_detailed
, mtrl.quantity_delivered quantity_delivered
FROM mtl_txn_request_lines mtrl
, mtl_material_transactions_temp mmtt
WHERE mtrl.organization_id = p_org_id
AND nvl(mtrl.from_subinventory_code,'@$#_') = nvl(p_from_sub,'@$#_')
AND nvl(mtrl.from_locator_id,-1) = nvl(p_from_locator_id,-1)
AND ( nvl(mtrl.project_id,-1) = nvl(p_project_id,-1) OR p_project_id IS NULL )
AND ( nvl(mtrl.task_id,-1) = nvl(p_task_id,-1) OR p_task_id IS NULL )
AND Nvl(inspection_status,-1) = Nvl(p_inspection_status,-1)
AND mtrl.inventory_item_id = p_item
AND Nvl(mtrl.revision, Nvl(p_rev, '@@@@')) = Nvl(p_rev, '@@@@')
AND Nvl(mtrl.lpn_id, -1) = Nvl(p_lpn, -1)
AND Nvl(mtrl.lot_number, Nvl(p_lot,'@$#_')) = Nvl(p_lot, '@$#_')
AND mmtt.transaction_temp_id = p_mmtt_id
AND mmtt.move_order_line_id = mtrl.line_id
AND (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) > 0
AND mtrl.line_status <> inv_globals.G_TO_STATUS_CLOSED
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mtrl.header_id)
ORDER BY 3 DESC;
SELECT DISTINCT mtrl.line_id
, Decode(p_uom_code,mtrl.uom_code,2,1)
uom_ordering --changed the order
-- we are doing a order by desc
, Decode(mmtt.transaction_source_type_id||'#'||mmtt.transaction_action_id,'1#27',1,
'7#12',1,'12#27',1,'13#12',1,'4#27',1, null) transaction_temp_id
, Nvl(mtrl.wms_process_flag,1)--when ordering by DESC, NULL would come first;
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mtrl.header_id)
ORDER BY 2, 3 DESC, 4 DESC, 5;--order on 3 should be DESC because,
SELECT
mtrl.line_id line_id
,mtrl.uom_code uom_code --added for bug8283511
-- Added columns below to make ordering same for both
-- from and transfer cursor
, Decode(p_uom_code,mtrl.uom_code,2,1) uom_ordering
, mtrl.wms_process_flag
, Decode(mtrl.primary_quantity
,p_primary_quantity
,1
,2) quantity_ordering
,mtrl.reference reference
,mtrl.reference_type_code reference_type_code
,mtrl.reference_id reference_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.organization_id = p_org_id
AND mtrl.inventory_item_id = p_item
AND mtrl.uom_code = p_uom_code
AND Nvl(mtrl.revision, Nvl(p_rev, '@@@')) = Nvl(p_rev, '@@@')
AND Nvl(mtrl.lpn_id, -1) = Nvl(p_lpn, -1)
AND Nvl(mtrl.lot_number, Nvl(p_lot,'@$#_')) = Nvl(p_lot, '@$#_')
AND (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) > 0
--
AND Nvl(inspection_status,-1) =
Nvl(p_transfer_inspection_status,-1)
AND Nvl(from_subinventory_code,'@$#_') = Nvl(p_from_sub,'@$#_')
AND Nvl(from_locator_id,-1) = Nvl(p_from_locator_id,-1)
AND mtrl.reference = p_reference --13935217
AND mtrl.reference_id = p_reference_id --13935217
AND Nvl(project_id,-1) = Nvl(p_project_id,-1)
AND Nvl(task_id,-1) = Nvl(p_task_id,-1)
AND Nvl(backorder_delivery_detail_id,-1) = Nvl(p_backorder_delivery_detail_id,-1)
AND Nvl(crossdock_type,-1) = Nvl(p_crossdock_type,-1)
-- AND Nvl(from_cost_group_id,-1) = Nvl(p_cost_group_id,-1) ????
AND mtrl.line_status <> inv_globals.G_TO_STATUS_CLOSED
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mtrl.header_id)
ORDER BY 2,3 DESC, 4 ;
L_UPDATE_OR_CLOSE VARCHAR2(1);
SELECT transaction_id
, transaction_type
, parent_transaction_id
, source_document_code
, INSPECTION_STATUS_CODE
, requisition_line_id
, po_header_id
, reason_id -- Bug No.9099299
INTO l_transaction_id
, l_transaction_type
, l_parent_transaction_id
, l_source_document_code
, l_rti_inspection_status_code
, l_requisition_line_id
, l_po_header_id
, l_reason_id -- Bug No.9099299
FROM rcv_transactions
WHERE interface_transaction_id = p_rti_id;
SELECT parent_transaction_id
, transaction_type
, subinventory
, locator_id
, inspection_status_code
, routing_header_id
INTO l_grand_parent_txn_id
, l_parent_txn_type
, l_from_sub
, l_from_loc_id
, l_pt_inspection_status_code
, l_pt_routing_id
FROM rcv_transactions
WHERE transaction_id = l_parent_transaction_id;
SELECT parent_transaction_id
, transaction_type
, inspection_status_code
, routing_header_id
INTO l_great_grand_parent_txn_id
, l_grand_parent_txn_type
, l_grand_pt_insp_status_code
, l_grand_pt_routing_id
FROM rcv_transactions
WHERE transaction_id = l_grand_parent_txn_id;
select inspection_status_code
,routing_header_id
into l_grt_gr_parent_insp_stat_code
,l_grt_gr_routing_id
from rcv_transactions
where transaction_id = l_great_grand_parent_txn_id;
SELECT parent_transaction_id
, transaction_type
, subinventory
, locator_id
INTO l_grand_parent_txn_id
, l_parent_txn_type
, l_from_sub
, l_from_loc_id
FROM rcv_transactions
WHERE transaction_id = l_parent_transaction_id;
UPDATE mtl_material_transactions_temp
SET rcv_transaction_id = l_transaction_id
, transfer_to_location = Decode(transfer_to_location,NULL,transfer_to_location,p_locator_id)
, transfer_subinventory = Decode(transfer_to_location,NULL,transfer_subinventory,p_subinventory)
, locator_id = Decode(transfer_to_location,NULL,p_locator_id,locator_id)
, subinventory_code = Decode(transfer_to_location,NULL,p_subinventory,subinventory_code)
--Bug 4082607.. commenting the following line as it is causing data
--corruption AND IS NOT required.
-- , lpn_id = Decode(l_transaction_type,'TRANSFER',Decode(p_transfer_lpn_id,NULL,lpn_id,p_transfer_lpn_id),lpn_id)
, transfer_lpn_id = decode(l_transaction_type,'TRANSFER',p_transfer_lpn_id,'DELIVER',p_transfer_lpn_id,transfer_lpn_id)
WHERE transaction_temp_id = p_mmtt_temp_id;
SELECT rsl.po_line_location_id,rsl.po_distribution_id,
Decode(rsh.asn_type,'ASN','Y', 'ASBN','Y','N'),Decode(rsh.receipt_source_code,'INTERNAL ORDER','Y','N')
INTO l_po_line_location_id,l_po_distribution_id,l_is_asn,l_is_req
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
WHERE rsl.shipment_line_id = p_shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id;
SELECT project_id,
task_id -- bug 7218830
INTO l_project_id,
l_task_id
FROM po_distributions_all
WHERE po_distribution_id = p_po_distribution_id;
SELECT project_id,
task_id -- bug 7218830
INTO l_project_id
, l_task_id
FROM oe_order_lines_all
WHERE line_id = p_oe_order_line_id;
select project_id, task_id
into l_project_id, l_task_id
from po_req_distributions_all
where distribution_id = p_req_distribution_id;
update mtl_txn_request_lines
set reference = l_reference
,reference_type_code = l_reference_type_code
,reference_id = l_reference_id
,quantity = quantity + l_mol_res_out(l_loop_index).quantity
--Review how are you ensuring that this quantity is in the
--right uom
,primary_quantity = primary_quantity + l_mol_res_out(l_loop_index).primary_qty
,secondary_quantity = secondary_quantity + l_mol_res_out(l_loop_index).secondary_quantity
,transaction_type_id = nvl(l_transaction_type_id,transaction_type_id)
,transaction_source_type_id = nvl(l_txn_source_type_id,transaction_source_type_id)
where line_id = l_transfer_mol_rec.line_id
;
SELECT 1
INTO l_loaded
FROM dual
WHERE exists
(SELECT 1
FROM wms_dispatched_tasks wdt
, mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_transfer_mol_rec.line_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.status IN (3, 4) -- dispached or loaded
AND wdt.task_type = 2 -- putaway
);
UPDATE mtl_txn_request_lines
SET from_subinventory_code = Nvl(p_subinventory,from_subinventory_code)
, from_locator_id = Nvl(p_locator_id, from_locator_id)
, backorder_delivery_detail_id = l_mol_res_out(l_loop_index).backorder_delivery_detail_id
, crossdock_type = l_mol_res_out(l_loop_index).crossdock_type
WHERE header_id = l_move_order_header_id
and line_id = l_move_order_line_id
and organization_id = p_org_id
AND inventory_item_id = p_item_id;
-- Call to UPDATE WDD
--Review. Shoudn't this be outside the if mol not found block???
--{{
--Create xdock reservation for the item. Do receipt. Make
--sure that WDD is updated with the MOL}}
IF (l_mol_res_out(l_loop_index).backorder_delivery_detail_id IS NOT NULL
and l_mol_res_out(l_loop_index).crossdock_type = 1) THEN --bug 9706800 No need to update wdd for OPM batch/WIP; Update only for Sales/Internal Orders
inv_rcv_reservation_util.update_wdd
(x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_wdd_id => l_mol_res_out(l_loop_index).backorder_delivery_detail_id
,p_released_status => null
,p_mol_id => l_move_order_line_id
);
print_debug('MAINTAIN_MO_CON - After calling update_wdd '||x_return_status||':'||l_progress,1);
-- update curr capacity
IF (l_debug = 1) THEN
print_debug('MAINTAIN_MO_CON - Calling update_loc_curr_capacity FOR CURRENT sub',1);--bug13451495
inv_loc_wms_utils.update_loc_current_capacity
( x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => p_org_id
,p_inventory_location_id => p_locator_id
,p_inventory_item_id => p_item_id
,p_primary_uom_flag => 'N'
,p_transaction_uom_code => p_uom_code
,p_quantity => p_qty
,p_issue_flag => 'N'
);
SELECT project_id,
Nvl(task_id, -1)
INTO l_project_id,
l_task_id
FROM po_distributions_all
WHERE po_distribution_id = p_po_distribution_id;
l_update_or_close := 'N';
print_debug('MAINTAIN_MO_CON - UPDATE OR CLOSE: '||l_update_or_close,1);
IF l_update_or_close = 'N' THEN
IF (l_debug = 1) THEN
print_debug('MAINTAIN_MO_CON - NO NEED TO PROCESS FOR THIS CASE',1);
l_update_or_close := 'C';
SELECT 1
INTO l_loaded
FROM dual
WHERE exists
(SELECT 1
FROM wms_dispatched_tasks wdt
, mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_transfer_mol_rec.line_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.status IN (3, 4) -- dispached or loaded
AND wdt.task_type = 2 -- putaway
);
l_update_or_close := 'U';
END IF; -- IF l_update_or_close = 'N' THEN
print_debug('MAINTAIN_MO_CON - UPDATE OR CLOSE: '||l_update_or_close,1);
IF l_update_or_close = 'N' THEN
--
IF (l_debug = 1) THEN
print_debug('MAINTAIN_MO_CON - NO PROCESSING!!',1);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = l_mol_rec.line_id;
print_debug('Reject xdock materials. Calling inv_reservation_pub.delete_reservation',4);
inv_reservation_pub.delete_reservation
(p_api_version_number => 1.0
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv_rec
, p_serial_number => l_dummy_serial
);
print_debug('Returned from inv_reservation_pub.delete_reservation',4);
--update the mol
IF (l_debug = 1) THEN
print_debug('MAINTAIN_MO_CON - l_backorder_delivery_detail_id = '|| l_backorder_delivery_detail_id,1);
--Update quantity with the quantity converted to the UOM in the RTI. This is changed so
--that the we honor the UOM entered by the user during
--Transfer
IF (l_mol_rec.uom_code <> p_uom_code) THEN
l_tmp_qty := inv_rcv_cache.convert_qty(l_mol_rec.inventory_item_id
,l_mol_rec.txn_qty
,l_mol_rec.uom_code
,p_uom_code);
UPDATE mtl_txn_request_lines
SET inspection_status = Decode(l_transaction_type,'ACCEPT',2,'REJECT',3,inspection_status)
, lpn_id = p_transfer_lpn_id
, quantity = l_tmp_qty
, quantity_detailed = Decode(l_transaction_type,'TRANSFER',l_tmp_qty_dtld,NULL)
, quantity_delivered = Decode(l_transaction_type,'TRANSFER',l_tmp_qty_dlvd,NULL)
, uom_code = l_tmp_uom_code
-- OPMConvergence
, secondary_quantity_detailed = Decode(l_transaction_type,'TRANSFER',secondary_quantity_detailed,NULL)
-- OPMConvergence
, lot_number = p_lot_number
, revision = p_revision
, from_subinventory_code = Nvl(p_subinventory, from_subinventory_code)
, from_locator_id = Nvl(p_locator_id, from_locator_id)
, wms_process_flag = 1
WHERE line_id = l_mol_rec.line_id;
--updated just like the crossdock mols.
--Add an extra parameter to transfer_mol cursor. This new
--parameter will be the current mol id. Transfer mol cursor
--should not pickup the lines which have line_id = new
--prameter. Open the transfer mol cursor
IF (l_update_or_close = 'C') THEN
IF (l_debug = 1) THEN
print_debug('MAINTAIN_MO_CON - before updating mol update or close = C ',1);
UPDATE mtl_txn_request_lines
SET line_status = inv_globals.G_TO_STATUS_CLOSED
, quantity = Nvl(quantity_delivered,0)
, quantity_detailed = quantity_delivered
, secondary_quantity_detailed = SECONDARY_QUANTITY_DELIVERED
--BDD_ID will get NULL out for REJECT cases
, backorder_delivery_detail_id = l_backorder_delivery_detail_id
, crossdock_type = Decode(l_backorder_delivery_detail_id,NULL,NULL,crossdock_type)
, wms_process_flag = 1
WHERE line_id = l_mol_rec.line_id;
--Update quantity with the quantity converted to the UOM in the RTI. This is changed so
--that the we honor the UOM entered by the user during
--Transfer
IF (l_mol_rec.uom_code <> p_uom_code) THEN
-- Bug 13484877 Start
IF (p_primary_uom_code = p_uom_code) THEN
l_tmp_qty := l_mol_rec.primary_quantity;
print_debug('MAINTAIN_MO_CON - before updating mol update or clode <> C ',1);
UPDATE mtl_txn_request_lines
SET inspection_status = Decode(l_transaction_type,'ACCEPT',2,'REJECT',3,inspection_status)
, lpn_id = p_transfer_lpn_id
, quantity = l_tmp_qty
, quantity_detailed = Decode(l_transaction_type,'TRANSFER',l_tmp_qty_dtld,NULL)
, quantity_delivered = l_tmp_qty_dlvd
, uom_code = l_tmp_uom_code
-- OPMConvergence
, secondary_quantity_detailed = Decode(l_transaction_type,'TRANSFER',secondary_quantity_detailed,NULL)
-- OPMConvergence
, lot_number = p_lot_number
, revision = p_revision
, from_subinventory_code = Nvl(p_subinventory, from_subinventory_code)
, from_locator_id = Nvl(p_locator_id, from_locator_id)
--BDD_ID will get NULL out for REJECT cases
, backorder_delivery_detail_id = l_backorder_delivery_detail_id
, crossdock_type = Decode(l_backorder_delivery_detail_id,NULL,NULL,crossdock_type)
, reason_id = l_reason_id -- Bug No. 9099299
, wms_process_flag = 1
WHERE line_id = l_mol_rec.line_id;
select backorder_delivery_detail_id
,(mtrl.quantity - Nvl(mtrl.quantity_delivered, 0))
,(mtrl.secondary_quantity - Nvl(mtrl.secondary_quantity_delivered, 0))
,uom_code -- bug8283511
into l_backorder_delivery_detail_id
,l_split_qty
,l_split_sec_qty
,l_uom_code -- bug8283511
from MTL_TXN_REQUEST_LINES mtrl
where mtrl.line_id = l_mo_split_tb(1).line_id;
print_debug('Calling inv_reservation_pub.delete_reservation',4);
inv_reservation_pub.delete_reservation
(p_api_version_number => 1.0
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv_rec
, p_serial_number => l_dummy_serial
);
print_debug('Returned from inv_reservation_pub.delete_reservation',4);
-- update the txn_source_id and inspection_status,
-- lpn_id, wms_process_flag for the new row.
-- and wms_process_flag for the old row.
--Update quantity with the quantity converted to the UOM in the RTI. This is changed so
--that the we honor the UOM entered by the user during
--Transfer
UPDATE mtl_txn_request_lines
SET inspection_status = Decode(l_transaction_type,'ACCEPT',2,'REJECT',3,inspection_status)
, lpn_id = p_transfer_lpn_id
, quantity = Decode(uom_code
,p_uom_code
,quantity
,inv_rcv_cache.convert_qty(inventory_item_id
,quantity
,uom_code
,p_uom_code)
)
, quantity_detailed = Decode(l_transaction_type
,'TRANSFER'
,Decode(uom_code
,p_uom_code
,quantity_detailed
,inv_rcv_cache.convert_qty(inventory_item_id
,quantity_detailed
,uom_code
,p_uom_code))
,NULL)
, quantity_delivered = Decode(l_transaction_type
,'TRANSFER'
,Decode(uom_code
,p_uom_code
,quantity_delivered
,inv_rcv_cache.convert_qty(inventory_item_id
,quantity_delivered
,uom_code
,p_uom_code))
,NULL)
, uom_code = p_uom_code
-- OPMConvergence
, secondary_quantity_detailed = Decode(l_transaction_type,'TRANSFER',secondary_quantity_detailed,NULL)
-- OPMConvergence
, lot_number = p_lot_number
, revision = p_revision
, from_subinventory_code = Nvl(p_subinventory, from_subinventory_code)
, from_locator_id = Nvl(p_locator_id, from_locator_id)
, wms_process_flag = 1
WHERE line_id = l_mo_split_tb(1).line_id;
--???? Should we also update the primary qty here????
--If p_mmtt_temp_id is not null then the move order line should
--not be closed because this unnecessarily leads to
--cancelling of operation plan. The line should be closed
--if and only if there is a transfer mol present with the
--same consolidation criteria. Otherwise it should be
--updated just like the crossdock mols.
IF (l_update_or_close = 'C') THEN
UPDATE mtl_txn_request_lines
SET line_status = inv_globals.G_TO_STATUS_CLOSED
, quantity = quantity_delivered
, quantity_detailed = quantity_delivered
, secondary_quantity_detailed = SECONDARY_QUANTITY_DELIVERED
, wms_process_flag = 1
, backorder_delivery_detail_id = l_backorder_delivery_detail_id
, crossdock_type = Decode(l_backorder_delivery_detail_id,NULL,NULL,crossdock_type)
WHERE line_id = l_mo_split_tb(1).line_id;
UPDATE mtl_txn_request_lines
SET inspection_status = Decode(l_transaction_type,'ACCEPT',2,'REJECT',3,inspection_status)
, lpn_id = p_transfer_lpn_id
, quantity = Decode(p_uom_code,p_primary_uom_code,primary_quantity,uom_code--bug 13484877
--commenting for bug 13484877,p_uom_code
,quantity
,inv_rcv_cache.convert_qty(inventory_item_id
,quantity
,uom_code
,p_uom_code)
)
, quantity_detailed = Decode(l_transaction_type
,'TRANSFER'
,Decode(uom_code
,p_uom_code
,quantity_detailed
,inv_rcv_cache.convert_qty(inventory_item_id
,quantity_detailed
,uom_code
,p_uom_code))
,NULL)
, quantity_delivered = Decode(l_transaction_type
,'TRANSFER'
,Decode(uom_code
,p_uom_code
,quantity_delivered
,inv_rcv_cache.convert_qty(inventory_item_id
,quantity_delivered
,uom_code
,p_uom_code))
,NULL)
, uom_code = p_uom_code
-- OPMConvergence
, secondary_quantity_detailed = Decode(l_transaction_type,'TRANSFER',secondary_quantity_detailed,NULL)
-- OPMConvergence
, lot_number = p_lot_number
, revision = p_revision
, from_subinventory_code = Nvl(p_subinventory, from_subinventory_code)
, from_locator_id = Nvl(p_locator_id, from_locator_id)
, wms_process_flag = 1
, backorder_delivery_detail_id = l_backorder_delivery_detail_id
, crossdock_type = Decode(l_backorder_delivery_detail_id,NULL,NULL,crossdock_type)
WHERE line_id = l_mo_split_tb(1).line_id;
* to update the wms_process_flag MOL with 4 the first time
* time you reach here. You should only update it when the
* qty being processing match the MOL qty (which will done
* when maintain_mo_con is called with qty 1 */
--UPDATE mtl_txn_request_lines
--SET wms_process_flag = 1
--WHERE line_id = l_mol_rec.line_id;
-- UPDATE THE MOVE ORDER LINE HERE
--
IF (p_mmtt_temp_id IS NOT NULL) THEN
UPDATE mtl_material_transactions_temp
SET move_order_line_id = l_transfer_mol_rec.line_id
WHERE transaction_temp_id = p_mmtt_temp_id;
print_debug('NUMBER OF MMTT UPDATED: '||SQL%rowcount,1);
update mtl_txn_request_lines
set reference = l_reference
,reference_type_code = l_reference_type_code
,reference_id = l_reference_id
,quantity = quantity + l_mol_res_in(1).quantity
,quantity_detailed = quantity_detailed + l_qty_detailed
,primary_quantity = primary_quantity + l_mol_res_in(1).primary_qty
,secondary_quantity = secondary_quantity + l_mol_res_in(1).secondary_quantity
,transaction_type_id = nvl(l_transaction_type_id,transaction_type_id)
,transaction_source_type_id = nvl(l_txn_source_type_id,transaction_source_type_id)
,wms_process_flag = 1
where line_id = l_transfer_mol_rec.line_id
;
--there was no transfer MOL, we would have updated the orig
--mol
-- Else
-- Move Order Line Not Found Create Move Order
-- IF (l_debug = 1) THEN
--print_debug('MAINTAIN_MOC_CON - Move Order Line Not Found line Id ',1);
-- --update mol for the sub and loc
-- UPDATE mtl_txn_request_lines
-- SET from_subinventory_code = Nvl(p_subinventory,from_subinventory_code)
-- , from_locator_id = Nvl(p_locator_id, from_locator_id)
-- WHERE header_id = l_move_order_header_id
-- and organization_id = p_org_id
-- AND inventory_item_id = p_item_id;
-- update curr capacity
IF (l_debug = 1) THEN
print_debug('MAINTAIN_MO - Calling update_loc_curr_capacity FOR CURRENT sub',1); --bug13451495
inv_loc_wms_utils.update_loc_current_capacity
( x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => p_org_id
,p_inventory_location_id => p_locator_id
,p_inventory_item_id => p_item_id
,p_primary_uom_flag => 'N'
,p_transaction_uom_code => p_uom_code
,p_quantity => p_qty
,p_issue_flag => 'N'
);
-- update curr capacity
IF (l_debug = 1) THEN
print_debug('MAINTAIN_MO - Calling update_loc_curr_capacity_nauto FOR FROM sub',1);
inv_loc_wms_utils.update_loc_curr_capacity_nauto
( x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => p_org_id
,p_inventory_location_id => l_from_loc_id
,p_inventory_item_id => p_item_id
,p_primary_uom_flag => 'N'
,p_transaction_uom_code => p_uom_code
,p_quantity => p_qty
,p_issue_flag => 'Y'
);
END IF;-- IF l_update_or_close = 'N' THEN
* We need to pass in the transfer_lpn_id to update the reservation record.
*/
l_mol_res_in(1).transaction_type := l_transaction_type;
UPDATE mtl_txn_request_lines
SET quantity_delivered = Decode(p_mmtt_temp_id,NULL,
Nvl(quantity_delivered,0)+l_quantity_delivered,quantity_delivered)
,secondary_quantity_delivered = Decode(p_mmtt_temp_id,NULL,
Nvl(secondary_quantity_delivered,0)+l_sec_quantity_delivered,quantity_delivered)
, line_status = Decode(p_mmtt_temp_id,NULL,
Decode((Nvl(quantity_delivered,0)+l_quantity_delivered),quantity,
inv_globals.G_TO_STATUS_CLOSED,line_status),line_status)
, wms_process_flag = 1
WHERE line_id = l_mo_split_tb(1).line_id;
* We need to pass in the transfer_lpn_id to update the reservation record.
*/
l_mol_res_in(1).transaction_type := l_transaction_type;
UPDATE mtl_txn_request_lines
SET quantity_delivered = Decode(p_mmtt_temp_id,NULL,quantity,quantity_delivered)
-- OPMConvergence
, secondary_quantity_delivered = Decode(p_mmtt_temp_id,NULL,secondary_quantity,secondary_quantity_delivered)
-- OPMConvergence
, line_status = Decode(p_mmtt_temp_id,NULL,inv_globals.g_to_status_closed,line_status)
, wms_process_flag = 1
WHERE line_id = l_mol_rec.line_id;
-- update curr capacity
IF (l_debug = 1) THEN
print_debug('MAINTAIN_MO_CON - Calling update_loc_curr_capacity_nauto FOR FROM sub',1);
inv_loc_wms_utils.update_loc_curr_capacity_nauto
( x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => p_org_id
,p_inventory_location_id => l_from_loc_id
,p_inventory_item_id => p_item_id
,p_primary_uom_flag => 'N'
,p_transaction_uom_code => p_uom_code
,p_quantity => p_qty
,p_issue_flag => 'Y'
);
--We need to update the wms_process_flag on mol for all the lines for the given lpn as
--we update all the mols for the given lpn to 2 in mark_returns
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE (lpn_id = p_lpn_id
OR lpn_id = p_transfer_lpn_id);
SELECT project_id,
Nvl(task_id, '') -- Bug 7355205
INTO l_project_id,
l_task_id
FROM po_distributions_all
WHERE po_distribution_id = p_po_distribution_id;
update mtl_txn_request_lines
set reference = l_reference
,reference_type_code = l_reference_type_code
,reference_id = l_reference_id
,quantity = quantity + ABS(l_mol_res_out(l_loop_index).quantity)
,primary_quantity = primary_quantity + ABS(l_mol_res_out(l_loop_index).primary_qty)
,secondary_quantity = secondary_quantity + ABS(l_mol_res_out(l_loop_index).secondary_quantity)
,transaction_type_id = nvl(l_transaction_type_id,transaction_type_id)
,transaction_source_type_id = nvl(l_txn_source_type_id,transaction_source_type_id)
where line_id = l_transfer_mol_rec.line_id
;
SELECT 1
INTO l_loaded
FROM dual
WHERE exists
(SELECT 1
FROM wms_dispatched_tasks wdt
, mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_transfer_mol_rec.line_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.status IN (3, 4) -- dispached or loaded
AND wdt.task_type = 2 -- putaway
);
UPDATE mtl_txn_request_lines
SET from_subinventory_code = Nvl(l_transfer_sub,from_subinventory_code)
, from_locator_id = Nvl(l_transfer_locator_id, from_locator_id)
WHERE header_id = l_move_order_header_id
and line_id = l_move_order_line_id
and organization_id = p_org_id
AND inventory_item_id = p_item_id;
-- Call to UPDATE WDD
IF (l_mol_res_out(l_loop_index).backorder_delivery_detail_id IS NOT NULL) THEN
inv_rcv_reservation_util.update_wdd
(x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_wdd_id => l_mol_res_out(l_loop_index).backorder_delivery_detail_id
,p_released_status => null
,p_mol_id => l_move_order_line_id
);
print_debug('MAINTAIN_MO_CON - After calling update_wdd '||x_return_status||':'||l_progress,1);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE (lpn_id = p_lpn_id
OR lpn_id = p_transfer_lpn_id);
SELECT 'Y', mtrl.from_subinventory_code, mtrl.from_locator_id,
mtrl.quantity
INTO l_discrepancy_exist, l_mol_sub, l_mol_loc_id,
l_mol_qty_in_puom
FROM wms_license_plate_numbers wlpn, mtl_txn_request_lines mtrl
WHERE wlpn.lpn_id = p_transfer_lpn_id
AND wlpn.lpn_context = 3
AND wlpn.organization_id = p_org_id
AND wlpn.organization_id = mtrl.organization_id
AND wlpn.lpn_id = mtrl.lpn_id
AND mtrl.line_status = 7
AND ( ( NVL (wlpn.subinventory_code, '@@@') <>
NVL (mtrl.from_subinventory_code, '@@@')
AND NVL (wlpn.locator_id, -999) <>
NVL (mtrl.from_locator_id,
-999)
)
OR mtrl.quantity <= 0
)
AND ROWNUM = 1;
/* You do not want to update the wms_process_flag here because
another RTI within the same group may want to process the
same MOL, which has been marked by the UI. Updating the MOL
should have been performed in the logic above. Note that this
assumes that the UI have marked the correct MOL for processing
--Update the wms_process_flag for all MOLs for the fiven lpns
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE lpn_id = p_lpn_id;
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE lpn_id = p_transfer_lpn_id;
SELECT NVL(wms_enabled_flag,'N')
INTO l_wms_enabled
FROM mtl_parameters
WHERE organization_id = p_org_id ;
SELECT Ltrim(Rtrim(lot_number)) lot_number,
Sum(transaction_quantity) transaction_quantity, --Sum the three qty for Bug 14117094
Sum(primary_quantity) primary_quantity,
-- OPMConvergence
Sum(secondary_quantity) secondary_quantity
-- OPMConvergence
FROM mtl_transaction_lots_temp
WHERE product_code = 'RCV'
AND product_transaction_id = p_product_txn_id
GROUP BY lot_number;
select interface_transaction_id
,transaction_type
,item_id
,item_description
,item_revision
,lpn_id
,transfer_lpn_id
,license_plate_number
,transfer_license_plate_number
,quantity
,uom_code
,unit_of_measure
,shipment_header_id
,shipment_num
,routing_header_id
,to_organization_id
,from_organization_id
,subinventory
,locator_id
,from_subinventory
,from_locator_id
,parent_transaction_id
,source_document_code
,group_id
,primary_quantity
,mmtt_temp_id
,po_line_location_id
,po_distribution_id
,shipment_line_id
,oe_order_line_id
,auto_transact_code
,Nvl(validation_flag,'N') validation_flag
,project_id
,task_id
,Nvl(mobile_txn, 'N') mobile_txn
,inv_transaction_id
,processing_mode_code
-- OPMConvergence
,secondary_quantity
,secondary_unit_of_measure
,secondary_uom_code
-- OPMConvergence
,requisition_line_id
,EXPRESS_TRANSACTION --for bug 14235799
-- Bug# 7154105
,req_distribution_id
,lpn_group_id --14408061
from rcv_transactions_interface rti
where rti.interface_transaction_id = p_txn_id;
SELECT intransit_type
INTO l_intransit_type
FROM mtl_interorg_parameters
WHERE from_organization_id = l_rti_rec.from_organization_id
AND to_organization_id = l_rti_rec.to_organization_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = l_rti_rec.item_id
AND organization_id = l_rti_rec.to_organization_id;
select transaction_type,
parent_transaction_id,
project_id,
task_id
into l_parent_txn_type,
l_parent_parent_txn_id,
l_parent_project_id,
l_parent_task_id
from rcv_transactions rt
where rt.transaction_id = l_rti_rec.parent_transaction_id;
SELECT 'Y'
INTO l_sec_txn_for_rtv
FROM rcv_transactions
WHERE transaction_type = 'RETURN TO RECEIVING'
AND interface_transaction_id = p_txn_id
AND exists (SELECT 1
FROM rcv_transactions
WHERE transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
AND interface_transaction_id = p_txn_id);
select transaction_type
into l_parent_parent_txn_type
from rcv_transactions rt
where rt.transaction_id = l_parent_parent_txn_id;
select decode(ASN_TYPE,'ASN','Y', 'ASBN','Y','N')
into l_asn_line_flag
from rcv_shipment_headers
WHERE shipment_header_id = l_rti_rec.shipment_header_id;
SELECT primary_uom_code,
primary_unit_of_measure,
mtl_transactions_enabled_flag,
lot_control_code,
serial_number_control_code,
decode(revision_qty_control_code
,1, 'N'
,2, 'Y'
,'N') item_rev_control_flag,
secondary_uom_code
INTO l_primary_uom,
l_primary_unit_of_measure,
l_transactions_enabled_flag,
l_lot_control_code,
l_serial_control_code,
l_revision_control_flag,
-- OPMConvergence
l_sec_uom_code
-- OPMConvergence
FROM mtl_system_items
WHERE inventory_item_id = l_rti_rec.item_id
AND organization_id = l_rti_rec.to_organization_id;
select organization_id
into l_lpn_org
from wms_license_plate_numbers wlpn
where wlpn.lpn_id = l_rti_rec.lpn_id;
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => l_rti_rec.SUBINVENTORY
,p_locator => l_rti_rec.LOCATOR_ID
,p_lpn_context => l_lpn_context
,p_lpn_id => l_rti_rec.transfer_lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => l_rti_rec.SUBINVENTORY
,p_locator => l_rti_rec.LOCATOR_ID
,p_lpn_context => l_lpn_context
,p_lpn_id => l_rti_rec.transfer_lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
update_serial_status ( p_product_txn_id => p_txn_id
,p_item_id => l_rti_rec.item_id
,p_organization_id => l_rti_rec.to_organization_id
,p_serial_status => l_serial_status
,p_inspection_status => l_inspection_status
,p_sub => l_rti_rec.subinventory
,p_locator_id => l_rti_rec.locator_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
SELECT Nvl(SUM(transaction_quantity),0)
INTO l_total_lot_qty
FROM mtl_transaction_lots_temp
WHERE product_code = 'RCV'
AND product_transaction_id = l_rti_rec.interface_transaction_id;
SELECT Nvl(SUM(inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number)),0)
INTO l_total_serial_qty
FROM mtl_serial_numbers_temp
WHERE product_code = 'RCV'
AND product_transaction_id = l_rti_rec.interface_transaction_id;
select organization_id
into l_lpn_org
from wms_license_plate_numbers wlpn
where wlpn.lpn_id = l_rti_rec.lpn_id;
UPDATE wms_lpn_contents
SET lot_number = NULL,
SERIAL_SUMMARY_ENTRY=2,--15951100
revision = NULL
WHERE parent_lpn_id = l_rti_rec.lpn_id
AND inventory_item_id = l_rti_rec.item_id;
UPDATE mtl_serial_numbers
SET lpn_id = NULL
WHERE inventory_item_id = l_rti_rec.item_id
AND lpn_id = l_rti_rec.lpn_id
AND current_status <> 7; -- Bug 6902368
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => l_rti_rec.SUBINVENTORY
,p_locator => l_rti_rec.LOCATOR_ID
,p_lpn_context => l_lpn_context
,p_lpn_id => l_rti_rec.transfer_lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
--Bug 6168447:Update the org of the FROM LPN to that of receiving org.
--Also null out source_name and source_header_id.
--This needs to be done only if the LPN is getting unpacked completely.
SELECT lpn_context
INTO l_current_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_rti_rec.lpn_id;
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => NULL
,p_locator => NULL
,p_lpn_context => 5
,p_source_name => FND_API.G_MISS_CHAR
,p_lpn_id => l_rti_rec.lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
update_serial_status ( p_product_txn_id => p_txn_id
,p_item_id => l_rti_rec.item_id
,p_organization_id => l_rti_rec.to_organization_id
,p_serial_status => l_serial_status
,p_inspection_status => l_inspection_status
,p_sub => l_rti_rec.subinventory
,p_locator_id => l_rti_rec.locator_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
print_debug('Do not call Pack Unpack as it is express receipt case and LPN orgs have already been updated by ModifyLPN call Express Receipt - '||l_express_receipt_flag,1);
SELECT uom_code
into l_rti_rec.uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_rti_rec.to_organization_id
AND inventory_item_id = l_rti_rec.item_id
AND unit_of_measure = l_rti_rec.unit_of_measure;
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => l_rti_rec.SUBINVENTORY
,p_locator => l_rti_rec.LOCATOR_ID
,p_lpn_context => l_lpn_context
,p_lpn_id => l_rti_rec.transfer_lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
update_serial_status ( p_product_txn_id => p_txn_id
,p_item_id => l_rti_rec.item_id
,p_organization_id => l_rti_rec.to_organization_id
,p_serial_status => l_serial_status
,p_inspection_status => l_inspection_status
,p_sub => l_rti_rec.subinventory
,p_locator_id => l_rti_rec.locator_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => l_rti_rec.SUBINVENTORY
,p_locator => l_rti_rec.LOCATOR_ID
,p_lpn_context => l_lpn_context
,p_lpn_id => l_rti_rec.transfer_lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
update_serial_status ( p_product_txn_id => p_txn_id
,p_item_id => l_rti_rec.item_id
,p_organization_id => l_rti_rec.to_organization_id
,p_serial_status => l_serial_status
,p_inspection_status => l_inspection_status
,p_sub => l_rti_rec.subinventory
,p_locator_id => l_rti_rec.locator_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
update_serial_status ( p_product_txn_id => p_txn_id
,p_item_id => l_rti_rec.item_id
,p_organization_id => l_rti_rec.to_organization_id
,p_serial_status => l_serial_status
,p_inspection_status => l_inspection_status
,p_sub => l_rti_rec.subinventory
,p_locator_id => l_rti_rec.locator_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
update mtl_serial_numbers msn
set msn.current_status = 4
where msn.inventory_item_id = l_rti_rec.item_id
and msn.current_organization_id = l_rti_rec.to_organization_id
and exists ( select 1
from mtl_serial_numbers_temp msnt
where msnt.product_code = 'RCV'
and msnt.product_transaction_id = p_txn_id
and msn.serial_number between msnt.fm_serial_number and msnt.to_serial_number
AND Length(msn.serial_number) = Length(msnt.fm_serial_number)
AND length(msnt.fm_serial_number)=Length(Nvl(msnt.to_serial_number,msnt.fm_serial_number)) --BUG 3818544
)
;
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => l_rti_rec.SUBINVENTORY
,p_locator => l_rti_rec.LOCATOR_ID
,p_lpn_context => l_lpn_context
,p_lpn_id => l_rti_rec.lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_source_name => l_rti_rec.shipment_num
,p_source_header_id => l_rti_rec.shipment_header_id) ;
--bug10298326 for serial control items where serial number is not defined, serial_summary_entry is set as 2...need to update to 1
--this happens in case of ASN import.
update wms_lpn_contents
set serial_summary_entry = 1
where parent_lpn_id = l_rti_rec.lpn_id
and inventory_item_id = l_rti_rec.item_id
and organization_id = l_rti_rec.to_organization_id
and serial_summary_entry = 2;
--Serial status update is required at this point - bug 3487843
l_serial_status := get_serial_status(p_transaction_type => l_rti_rec.transaction_type
, p_auto_transact_code => l_rti_rec.auto_transact_code
, p_parent_transaction_type => l_parent_txn_type
, p_parent_parent_txn_type => l_parent_parent_txn_type
, p_quantity => l_rti_rec.primary_quantity);
update_serial_status ( p_product_txn_id => p_txn_id
,p_item_id => l_rti_rec.item_id
,p_organization_id => l_rti_rec.to_organization_id
,p_serial_status => l_serial_status
,p_inspection_status => l_inspection_status
,p_sub => l_rti_rec.subinventory
,p_locator_id => l_rti_rec.locator_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
SELECT po_header_id
INTO l_po_header_id
FROM po_line_locations_all
WHERE line_location_id = l_rti_rec.po_line_location_id;
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => l_rti_rec.SUBINVENTORY
,p_locator => l_rti_rec.LOCATOR_ID
,p_lpn_context => l_lpn_context
,p_lpn_id => l_rti_rec.lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
update_serial_status ( p_product_txn_id => p_txn_id
,p_item_id => l_rti_rec.item_id
,p_organization_id => l_rti_rec.to_organization_id
,p_serial_status => l_serial_status
,p_inspection_status => l_inspection_status
,p_sub => l_rti_rec.subinventory
,p_locator_id => l_rti_rec.locator_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => l_rti_rec.SUBINVENTORY
,p_locator => l_rti_rec.LOCATOR_ID
,p_lpn_context => l_lpn_context
,p_lpn_id => l_rti_rec.transfer_lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
update_serial_status ( p_product_txn_id => p_txn_id
,p_item_id => l_rti_rec.item_id
,p_organization_id => l_rti_rec.to_organization_id
,p_serial_status => l_serial_status
,p_inspection_status => l_inspection_status
,p_sub => l_rti_rec.subinventory
,p_locator_id => l_rti_rec.locator_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
update_lpn_location_context(p_organization_id => l_rti_rec.to_organization_id
,p_sub => l_rti_rec.SUBINVENTORY
,p_locator => l_rti_rec.LOCATOR_ID
,p_lpn_context => l_lpn_context
,p_lpn_id => l_rti_rec.lpn_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
SELECT uom_code
into l_rti_rec.uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_rti_rec.to_organization_id
AND inventory_item_id = l_rti_rec.item_id
AND unit_of_measure = l_rti_rec.unit_of_measure;
SELECT uom_code
INTO l_rti_rec.secondary_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_rti_rec.to_organization_id
AND inventory_item_id = l_rti_rec.item_id
AND unit_of_measure = l_rti_rec.secondary_unit_of_measure;
SELECT requisition_line_id
INTO l_requisition_line_id
FROM rcv_transactions
WHERE interface_transaction_id = p_txn_id;
SELECT po_header_id
INTO l_po_header_id
FROM po_line_locations_all
WHERE line_location_id = l_rti_rec.po_line_location_id;
select LOT_CONTROL_CODE into l_wms_lot
from mtl_system_items
where inventory_item_id = l_rti_rec.item_id
and organization_id = l_rti_rec.to_organization_id
and rownum = 1;
SELECT lot_number into l_wms_lot_number
FROM mtl_transaction_lots_temp
WHERE PRODUCT_TRANSACTION_ID = p_txn_id;
PROCEDURE insert_msni(p_product_transaction_id IN NUMBER,
p_product_code IN VARCHAR2,
p_interface_id IN NUMBER,
p_item_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_fm_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
print_debug('Inside INSERT_MSNI', 4);
Insert into MTL_SERIAL_NUMBERS_INTERFACE
(
transaction_interface_id,
Source_Code,
Source_Line_Id,
Process_flag, --Is this the same process_flag as above?
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Fm_Serial_Number,
To_Serial_Number,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
Values
(
p_interface_id, -- transaction_interface_id
1, -- Source_Code,
-1, -- Source_Line_Id,
1, -- Process_flag,
sysdate, -- Last_Update_Date,
l_User_Id, -- Last_Updated_By,
sysdate, -- Creation_Date,
l_User_Id, -- Created_By,
p_fm_Serial_number, -- from_Serial_Number,
p_to_Serial_number, -- To_Serial_Number
p_product_code,
p_product_transaction_id
);
print_debug('INSERT_MSNI Complete without Error', 4);
print_debug('insert_msni : - other exception:'|| l_progress || ' ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')|| 'Error =' || SQLCODE, 1);
inv_mobile_helper_functions.sql_error('INV_RCV_INTEGRATION_PVT.insert_msni',l_progress, SQLCODE);
END insert_msni;
PROCEDURE insert_mtli(p_product_transaction_id IN NUMBER,
p_product_code IN VARCHAR2,
p_interface_id IN NUMBER,
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_transaction_quantity IN NUMBER,
p_primary_quantity IN NUMBER,
p_serial_interface_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_sec_qty IN NUMBER DEFAULT NULL
) is
cursor mln_csr(p_lot_number VARCHAR2, p_item_id NUMBER, p_org_id in NUMBER ) IS
select mln.inventory_item_id,
mln.lot_number, mln.expiration_date, mln.description, mln.vendor_id,mln.vendor_name,
mln.supplier_lot_number, mln.territory_code, mln.grade_code, mln.origination_date,
mln.date_code, mln.status_id, mln.change_date, mln.age, mln.retest_date, mln.maturity_date,
mln.lot_attribute_category, mln.item_size, mln.color, mln.volume, mln.volume_uom,
mln.place_of_origin, mln.best_by_date, mln.length, mln.length_uom, mln.recycled_content,
mln.thickness, mln.thickness_uom, mln.width, mln.width_uom, mln.curl_wrinkle_fold,
mln.c_attribute1, mln.c_Attribute2, mln.c_attribute3, mln.c_attribute4, mln.c_attribute5,
mln.c_attribute6, mln.c_attribute7, mln.c_attribute8, mln.c_attribute9, mln.c_attribute10,
mln.c_attribute11, mln.c_attribute12, mln.c_attribute13, mln.c_attribute14, mln.c_attribute15,
mln.c_attribute16, mln.c_attribute17, mln.c_attribute18, mln.c_attribute19, mln.c_attribute20,
mln.d_attribute1, mln.d_attribute2, mln.d_attribute3, mln.d_attribute4, mln.d_attribute5,
mln.d_attribute6, mln.d_attribute7, mln.d_attribute8, mln.d_attribute9, mln.d_attribute10,
mln.n_attribute1, mln.n_attribute2, mln.n_attribute3, mln.n_attribute4, mln.n_attribute5,
mln.n_attribute6, mln.n_attribute7, mln.n_attribute8, mln.n_attribute9, mln.n_attribute10
FROM MTL_LOT_NUMBERS MLN
WHERE mln.lot_number = Ltrim(Rtrim(p_lot_number))
AND mln.organization_id = p_org_id
AND mln.inventory_item_id = p_item_id
;
print_debug('Inside INSERT_MTLI' , 4);
insert into mtl_transaction_lots_interface (
TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_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_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
ERROR_CODE,
PROCESS_FLAG,
DESCRIPTION,
VENDOR_NAME,
SUPPLIER_LOT_NUMBER,
ORIGINATION_DATE,
DATE_CODE,
GRADE_CODE,
CHANGE_DATE,
MATURITY_DATE,
STATUS_ID,
RETEST_DATE,
AGE,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
WIDTH,
WIDTH_UOM,
CURL_WRINKLE_FOLD,
LOT_ATTRIBUTE_CATEGORY,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
VENDOR_ID,
TERRITORY_CODE,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID,
SECONDARY_TRANSACTION_QUANTITY
) values
(
p_interface_id,
1,
-1,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
null, -- REQUEST_ID
null, -- PROGRAM_APPLICATION_ID
null, -- PROGRAM_ID
null, -- PROGRAM_UPDATE_DATE
Ltrim(Rtrim(p_lot_number)),
l_mln_rec.EXPIRATION_DATE,
P_TRANSACTION_QUANTITY,
P_PRIMARY_QUANTITY,
P_serial_interface_id, -- serial_transaction_temp_id
null, -- ERROR_CODE
null, -- PROCESS_FLAG,
l_mln_rec.DESCRIPTION,
l_mln_rec.VENDOR_NAME,
l_mln_rec.SUPPLIER_LOT_NUMBER,
l_mln_rec.ORIGINATION_DATE,
l_mln_rec.DATE_CODE,
l_mln_rec.GRADE_CODE ,
l_mln_rec.CHANGE_DATE,
l_mln_rec.MATURITY_DATE,
l_mln_rec.STATUS_ID,
l_mln_rec.RETEST_DATE,
l_mln_rec.AGE,
l_mln_rec.ITEM_SIZE,
l_mln_rec.COLOR,
l_mln_rec.VOLUME,
l_mln_rec.VOLUME_UOM,
l_mln_rec.PLACE_OF_ORIGIN,
l_mln_rec.BEST_BY_DATE,
l_mln_rec.LENGTH,
l_mln_rec.LENGTH_UOM,
l_mln_rec.RECYCLED_CONTENT,
l_mln_rec.THICKNESS,
l_mln_rec.THICKNESS_UOM,
l_mln_rec.WIDTH,
l_mln_rec.WIDTH_UOM,
l_mln_rec.CURL_WRINKLE_FOLD,
l_mln_rec.LOT_ATTRIBUTE_CATEGORY,
l_mln_rec.C_ATTRIBUTE1,
l_mln_rec.C_ATTRIBUTE2,
l_mln_rec.C_ATTRIBUTE3,
l_mln_rec.C_ATTRIBUTE4,
l_mln_rec.C_ATTRIBUTE5,
l_mln_rec.C_ATTRIBUTE6,
l_mln_rec.C_ATTRIBUTE7,
l_mln_rec.C_ATTRIBUTE8,
l_mln_rec.C_ATTRIBUTE9,
l_mln_rec.C_ATTRIBUTE10,
l_mln_rec.C_ATTRIBUTE11,
l_mln_rec.C_ATTRIBUTE12,
l_mln_rec.C_ATTRIBUTE13,
l_mln_rec.C_ATTRIBUTE14,
l_mln_rec.C_ATTRIBUTE15,
l_mln_rec.C_ATTRIBUTE16,
l_mln_rec.C_ATTRIBUTE17,
l_mln_rec.C_ATTRIBUTE18,
l_mln_rec.C_ATTRIBUTE19,
l_mln_rec.C_ATTRIBUTE20,
l_mln_rec.D_ATTRIBUTE1 ,
l_mln_rec.D_ATTRIBUTE2,
l_mln_rec.D_ATTRIBUTE3,
l_mln_rec.D_ATTRIBUTE4,
l_mln_rec.D_ATTRIBUTE5,
l_mln_rec.D_ATTRIBUTE6,
l_mln_rec.D_ATTRIBUTE7,
l_mln_rec.D_ATTRIBUTE8,
l_mln_rec.D_ATTRIBUTE9,
l_mln_rec.D_ATTRIBUTE10,
l_mln_rec.N_ATTRIBUTE1,
l_mln_rec.N_ATTRIBUTE2,
l_mln_rec.N_ATTRIBUTE3,
l_mln_rec.N_ATTRIBUTE4,
l_mln_rec.N_ATTRIBUTE5,
l_mln_rec.N_ATTRIBUTE6,
l_mln_rec.N_ATTRIBUTE7,
l_mln_rec.N_ATTRIBUTE8,
l_mln_rec.N_ATTRIBUTE9,
l_mln_rec.N_ATTRIBUTE10,
l_mln_rec.VENDOR_ID,
l_mln_rec.TERRITORY_CODE,
p_PRODUCT_CODE,
p_PRODUCT_TRANSACTION_ID,
p_sec_qty
);
print_debug('INSERT_MTLI Commplete without Error ' , 4);
print_debug('insert_mtli : - other exception:'|| l_progress || ' ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')|| 'Error =' || SQLCODE, 1);
inv_mobile_helper_functions.sql_error('INV_RCV_INTEGRATION_PVT.insert_mtli',l_progress, SQLCODE);
END insert_mtli;
l_serial_interface_inserted BOOLEAN;
l_num_serial_inserted NUMBER;
SELECT primary_uom_code,
primary_unit_of_measure,
lot_control_code,
serial_number_control_code
INTO l_primary_uom,
l_primary_unit_of_measure,
l_lot_control_code,
l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_to_org_id;
SELECT unit_of_measure
into l_unit_of_measure
FROM mtl_item_uoms_view
WHERE organization_id = p_to_org_id
AND inventory_item_id = p_item_id
AND uom_code = p_txn_uom_code;
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_interface_transaction_id
FROM DUAL;
SELECT rt.source_document_code
, rsh.receipt_source_code
, rt.po_header_id
, rt.requisition_line_id
, rt.oe_order_header_id
INTO l_source_document_code
, l_receipt_source_code
, l_po_header_id
, l_req_line_id
, l_oe_order_header_id
FROM rcv_transactions rt
, rcv_shipment_headers rsh
WHERE rt.transaction_id = p_txn_source_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND rt.organization_id = p_to_org_id
AND exists (SELECT '1' FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rt.organization_id = rsl.to_organization_id);
SELECT rsh.receipt_source_code,
Decode(rsh.receipt_source_code,'INTERNAL
ORDER','REQ','INVENTORY','INVENTORY','VENDOR','PO','CUSTOMER','RMA')
INTO l_receipt_source_code
, l_source_document_code
FROM rcv_shipment_headers rsh, wms_license_plate_numbers wlpn
WHERE RSH.SHIPMENT_HEADER_ID = WLPN.SOURCE_HEADER_ID
AND WLPN.SOURCE_HEADER_ID IS NOT NULL
AND wlpn.lpn_id = p_lpn_id
AND exists (SELECT '1' FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND wlpn.organization_id = rsl.to_organization_id);
SELECT rsh.receipt_source_code,
Decode(rsh.receipt_source_code,'INTERNAL
ORDER','REQ','INVENTORY','INVENTORY','VENDOR','PO','CUSTOMER','RMA')
INTO l_receipt_source_code
, l_source_document_code
FROM rcv_shipment_headers rsh, wms_license_plate_numbers wlpn
WHERE RSH.SHIPMENT_NUM = WLPN.SOURCE_NAME
AND WLPN.SOURCE_NAME IS NOT NULL
AND wlpn.lpn_id = p_lpn_id
AND exists (SELECT '1' FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND wlpn.organization_id = rsl.to_organization_id);
select group_id ,
lpn_group_id ,
transaction_type ,
processing_mode_code ,
processing_request_id ,
item_category ,
quantity ,
unit_of_measure ,
uom_code ,
employee_id ,
auto_transact_code ,
shipment_header_id ,
shipment_line_id ,
ship_to_location_id ,
primary_quantity ,
primary_unit_of_measure ,
receipt_source_code ,
vendor_id ,
vendor_site_id ,
from_Organization_Id ,
to_Organization_Id ,
Routing_Header_Id ,
Routing_Step_Id ,
Source_Document_Code ,
Parent_Transaction_Id ,
Po_Header_Id ,
Po_Revision_Num ,
Po_Release_Id ,
Po_Line_Id ,
Po_Line_Location_Id ,
Po_Unit_Price ,
Currency_Code ,
Currency_Conversion_Type ,
Currency_Conversion_Rate ,
Currency_Conversion_Date ,
Po_Distribution_Id ,
Requisition_Line_Id ,
Req_Distribution_Id ,
Charge_Account_Id ,
Substitute_Unordered_Code ,
Receipt_Exception_Flag ,
Accrual_Status_Code ,
Inspection_Status_Code ,
Inspection_Quality_Code ,
Destination_Type_Code ,
Deliver_To_Person_Id ,
Location_Id ,
Deliver_To_Location_Id ,
Subinventory ,
Locator_Id ,
Wip_Entity_Id ,
Wip_Line_Id ,
Department_Code ,
Wip_Repetitive_Schedule_Id ,
Wip_Operation_Seq_Num ,
Wip_Resource_Seq_Num ,
Bom_Resource_Id ,
Shipment_Num ,
Freight_Carrier_Code ,
Bill_Of_Lading ,
Packing_Slip ,
Shipped_Date ,
Expected_Receipt_Date ,
Actual_Cost ,
Transfer_Cost ,
Transportation_Cost ,
Transportation_Account_Id ,
Num_Of_Containers ,
Waybill_Airbill_Num ,
Vendor_Item_Num ,
Vendor_Lot_Num ,
Rma_Reference ,
Comments ,
Attribute_Category ,
Attribute1 ,
Attribute2 ,
Attribute3 ,
Attribute4 ,
Attribute5 ,
Attribute6 ,
Attribute7 ,
Attribute8 ,
Attribute9 ,
Attribute10 ,
Attribute11 ,
Attribute12 ,
Attribute13 ,
Attribute14 ,
Attribute15 ,
Ship_Head_Attribute_Category ,
Ship_Head_Attribute1 ,
Ship_Head_Attribute2 ,
Ship_Head_Attribute3 ,
Ship_Head_Attribute4 ,
Ship_Head_Attribute5 ,
Ship_Head_Attribute6 ,
Ship_Head_Attribute7 ,
Ship_Head_Attribute8 ,
Ship_Head_Attribute9 ,
Ship_Head_Attribute10 ,
Ship_Head_Attribute11 ,
Ship_Head_Attribute12 ,
Ship_Head_Attribute13 ,
Ship_Head_Attribute14 ,
Ship_Head_Attribute15 ,
Ship_Line_Attribute_Category ,
Ship_Line_Attribute1 ,
Ship_Line_Attribute2 ,
Ship_Line_Attribute3 ,
Ship_Line_Attribute4 ,
Ship_Line_Attribute5 ,
Ship_Line_Attribute6 ,
Ship_Line_Attribute7 ,
Ship_Line_Attribute8 ,
Ship_Line_Attribute9 ,
Ship_Line_Attribute10 ,
Ship_Line_Attribute11 ,
Ship_Line_Attribute12 ,
Ship_Line_Attribute13 ,
Ship_Line_Attribute14 ,
Ship_Line_Attribute15 ,
Ussgl_Transaction_Code ,
Government_Context ,
Reason_Id ,
Destination_Context ,
Source_Doc_Quantity ,
Source_Doc_Unit_Of_Measure ,
use_mtl_lot ,
use_mtl_serial ,
QA_Collection_Id ,
Country_of_Origin_Code ,
oe_order_header_id ,
oe_order_line_id ,
customer_item_num ,
customer_id ,
customer_site_id ,
put_away_rule_id ,
put_away_strategy_id ,
lpn_id ,
transfer_lpn_id ,
license_plate_number ,
transfer_license_plate_number ,
cost_group_id ,
mmtt_temp_id ,
mobile_txn ,
transfer_cost_group_id ,
secondary_quantity ,
secondary_unit_of_measure ,
org_id --
from rcv_transactions_interface rti
where rti.interface_transaction_id = p_interface_transaction_id )
Loop
-- Insert the row
IF (l_debug = 1) THEN
print_debug('create_rti_from_lpn : - Before Inserting into RTI' , 1);
rcv_trx_interface_insert_pkg.insert_row ( l_rti_rowid,
l_interface_transaction_id,
l_rcv_transaction_rec.group_id,
l_sysdate,
l_user_id,
l_sysdate, /* Created Date */
l_user_id, /* Created By */
l_login_id, /* last Update Login */
l_rcv_transaction_rec.transaction_type, /* transaction type */
l_sysdate , /* transaction date */
'RUNNING', /* Processing status code */
l_rcv_transaction_rec.processing_mode_code,
/* Processing Request id Debug: Not sure how this is used */
l_rcv_transaction_rec.processing_request_id ,
'PENDING', /* Transaction status code */
NULL, /* item_category */
p_quantity,
l_unit_of_measure, /* unit_of_measure */
'RCV', /* interface source code */
NULL, /* interface source line id */
NULL, /* inv_transaction id */
p_item_id,
p_item_desc,
p_item_revision,
p_txn_uom_code, /* uom code */
l_rcv_transaction_rec.employee_id,
l_rcv_transaction_rec.auto_transact_code, /* Auto transact code */
l_rcv_transaction_rec.shipment_header_id, /* shipment header id */
l_rcv_transaction_rec.shipment_line_id, /* shipment line id */
l_rcv_transaction_rec.ship_to_location_id,
l_primary_qty, /* primary quantity */
l_primary_unit_of_measure, /* primary uom */
l_receipt_source_code, /* receipt source code */
l_rcv_transaction_rec.vendor_id,
l_rcv_transaction_rec.vendor_site_id,
l_rcv_transaction_rec.from_organization_id, /* from org id */
l_rcv_transaction_rec.to_organization_id, /* to org id */
l_rcv_transaction_rec.routing_header_id,
l_rcv_transaction_rec.routing_step_id, /* routing step id */
l_source_document_code, /* source document code */
nvl(p_txn_source_id,-1) , /* Parent trx id */
l_rcv_transaction_rec.po_header_id,
NULL, /* PO Revision number */
l_rcv_transaction_rec.po_release_id,
l_rcv_transaction_rec.po_line_id,
l_rcv_transaction_rec.po_line_location_id,
l_rcv_transaction_rec.po_unit_price,
l_rcv_transaction_rec.currency_code, /* Currency_Code */
l_rcv_transaction_rec.currency_conversion_type,
l_rcv_transaction_rec.currency_conversion_rate,
l_rcv_transaction_rec.currency_conversion_date,
l_rcv_transaction_rec.po_distribution_id,
l_rcv_transaction_rec.Requisition_Line_Id,
l_rcv_transaction_rec.req_distribution_id,
l_rcv_transaction_rec.charge_account_id, /* Charge_Account_Id */
l_rcv_transaction_rec.substitute_unordered_code, /* Substitute_Unordered_Code */
l_rcv_transaction_rec.receipt_exception_flag, /* Receipt_Exception_Flag forms check box?*/
l_rcv_transaction_rec.Accrual_Status_Code, /* Accrual_Status_Code */
l_rcv_transaction_rec.Inspection_Status_Code, /* Inspection_Status_Code */
l_rcv_transaction_rec.Inspection_Quality_Code, /* Inspection_Quality_Code */
l_rcv_transaction_rec.destination_type_code, /* Destination_Type_Code */
l_rcv_transaction_rec.deliver_to_person_id, /* Deliver_To_Person_Id */
l_rcv_transaction_rec.location_id, /* Location_Id */
l_rcv_transaction_rec.deliver_to_location_id, /* Deliver_To_Location_Id */
l_rcv_transaction_rec.subinventory, /* Subinventory */
l_rcv_transaction_rec.locator_id, /* Locator_Id */
l_rcv_transaction_rec.wip_entity_id, /* Wip_Entity_Id */
l_rcv_transaction_rec.wip_line_id, /* Wip_Line_Id */
l_rcv_transaction_rec.department_code, /* Department_Code */
l_rcv_transaction_rec.Wip_Repetitive_Schedule_Id, /* Wip_Repetitive_Schedule_Id */
l_rcv_transaction_rec.Wip_Operation_Seq_Num, /* Wip_Operation_Seq_Num */
l_rcv_transaction_rec.Wip_Resource_Seq_Num,
l_rcv_transaction_rec.Bom_Resource_Id ,
l_rcv_transaction_rec.Shipment_Num ,
l_rcv_transaction_rec.Freight_Carrier_Code ,
l_rcv_transaction_rec.Bill_Of_Lading ,
l_rcv_transaction_rec.Packing_Slip ,
l_rcv_transaction_rec.Shipped_Date ,
l_rcv_transaction_rec.Expected_Receipt_Date ,
l_rcv_transaction_rec.Actual_Cost ,
l_rcv_transaction_rec.Transfer_Cost ,
l_rcv_transaction_rec.Transportation_Cost ,
l_rcv_transaction_rec.Transportation_Account_Id ,
l_rcv_transaction_rec.Num_Of_Containers ,
l_rcv_transaction_rec.Waybill_Airbill_Num ,
l_rcv_transaction_rec.Vendor_Item_Num ,
l_rcv_transaction_rec.Vendor_Lot_Num ,
l_rcv_transaction_rec.Rma_Reference ,
l_rcv_transaction_rec.Comments ,
l_rcv_transaction_rec.Attribute_Category ,
l_rcv_transaction_rec.Attribute1 ,
l_rcv_transaction_rec.Attribute2 ,
l_rcv_transaction_rec.Attribute3 ,
l_rcv_transaction_rec.Attribute4 ,
l_rcv_transaction_rec.Attribute5 ,
l_rcv_transaction_rec.Attribute6 ,
l_rcv_transaction_rec.Attribute7 ,
l_rcv_transaction_rec.Attribute8 ,
l_rcv_transaction_rec.Attribute9 ,
l_rcv_transaction_rec.Attribute10 ,
l_rcv_transaction_rec.Attribute11 ,
l_rcv_transaction_rec.Attribute12 ,
l_rcv_transaction_rec.Attribute13 ,
l_rcv_transaction_rec.Attribute14 ,
l_rcv_transaction_rec.Attribute15 ,
l_rcv_transaction_rec.Ship_Head_Attribute_Category ,
l_rcv_transaction_rec.Ship_Head_Attribute1 ,
l_rcv_transaction_rec.Ship_Head_Attribute2 ,
l_rcv_transaction_rec.Ship_Head_Attribute3 ,
l_rcv_transaction_rec.Ship_Head_Attribute4 ,
l_rcv_transaction_rec.Ship_Head_Attribute5 ,
l_rcv_transaction_rec.Ship_Head_Attribute6 ,
l_rcv_transaction_rec.Ship_Head_Attribute7 ,
l_rcv_transaction_rec.Ship_Head_Attribute8 ,
l_rcv_transaction_rec.Ship_Head_Attribute9 ,
l_rcv_transaction_rec.Ship_Head_Attribute10 ,
l_rcv_transaction_rec.Ship_Head_Attribute11 ,
l_rcv_transaction_rec.Ship_Head_Attribute12 ,
l_rcv_transaction_rec.Ship_Head_Attribute13 ,
l_rcv_transaction_rec.Ship_Head_Attribute14 ,
l_rcv_transaction_rec.Ship_Head_Attribute15 ,
l_rcv_transaction_rec.Ship_Line_Attribute_Category ,
l_rcv_transaction_rec.Ship_Line_Attribute1 ,
l_rcv_transaction_rec.Ship_Line_Attribute2 ,
l_rcv_transaction_rec.Ship_Line_Attribute3 ,
l_rcv_transaction_rec.Ship_Line_Attribute4 ,
l_rcv_transaction_rec.Ship_Line_Attribute5 ,
l_rcv_transaction_rec.Ship_Line_Attribute6 ,
l_rcv_transaction_rec.Ship_Line_Attribute7 ,
l_rcv_transaction_rec.Ship_Line_Attribute8 ,
l_rcv_transaction_rec.Ship_Line_Attribute9 ,
l_rcv_transaction_rec.Ship_Line_Attribute10 ,
l_rcv_transaction_rec.Ship_Line_Attribute11 ,
l_rcv_transaction_rec.Ship_Line_Attribute12 ,
l_rcv_transaction_rec.Ship_Line_Attribute13 ,
l_rcv_transaction_rec.Ship_Line_Attribute14 ,
l_rcv_transaction_rec.Ship_Line_Attribute15 ,
l_rcv_transaction_rec.Ussgl_Transaction_Code ,
l_rcv_transaction_rec.Government_Context ,
l_rcv_transaction_rec.Reason_Id ,
l_rcv_transaction_rec.Destination_Context ,
l_rcv_transaction_rec.Source_Doc_Quantity ,
l_rcv_transaction_rec.Source_Doc_Unit_Of_Measure ,
l_rcv_transaction_rec.use_mtl_lot ,
l_rcv_transaction_rec.use_mtl_serial ,
l_rcv_transaction_rec.QA_Collection_Id ,
l_rcv_transaction_rec.Country_of_Origin_Code ,
l_rcv_transaction_rec.oe_order_header_id ,
l_rcv_transaction_rec.oe_order_line_id ,
l_rcv_transaction_rec.customer_item_num ,
l_rcv_transaction_rec.customer_id ,
l_rcv_transaction_rec.customer_site_id ,
l_rcv_transaction_rec.put_away_rule_id ,
l_rcv_transaction_rec.put_away_strategy_id ,
p_lpn_id ,
p_transfer_lpn_id ,
l_rcv_transaction_rec.cost_group_id ,
p_mmtt_temp_id ,
l_rcv_transaction_rec.mobile_txn ,
l_rcv_transaction_rec.transfer_cost_group_id ,
l_rcv_transaction_rec.secondary_quantity ,
l_rcv_transaction_rec.secondary_unit_of_measure ,
l_rcv_transaction_rec.lpn_group_id,
nvl(l_operating_unit_id,l_rcv_transaction_rec.org_id) --
);
-- is inserted by insert_row itself.
-- added update to validation_flag
-- *******************
update rcv_transactions_interface
set transfer_license_plate_number = p_transfer_lpn,
validation_flag = 'Y',
project_id = p_project_id,
task_id = p_task_id
where interface_transaction_id = l_interface_transaction_id;
l_serial_interface_inserted := FALSE;
print_debug('create_rti_from_lpn : - after Inserting into RTI' , 1);
print_debug('create_rti_from_lpn : - Before Inserting Lots from WLC ' , 1);
For l_lot_rec in ( select lot_number,
uom_code,
sum(quantity) quantity
from wms_lpn_contents wlc
where wlc.inventory_item_id = p_item_id
and wlc.organization_id = p_org_id
and wlc.parent_lpn_id = p_lpn_id
group by lot_number, uom_code )
Loop
IF (l_lot_rec.uom_code <> l_primary_uom) THEN
l_primary_lot_qty := inv_rcv_cache.convert_qty(p_inventory_item_id => p_item_id
,p_from_qty => l_lot_rec.quantity
,p_from_uom_code => l_lot_rec.uom_code
,p_to_uom_code => l_primary_uom
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_lot_Interface_Id
FROM DUAL;
-- Call insert MSNI for linked Serials Here
For l_serial_rec in ( select msn.serial_number
from mtl_serial_numbers msn
where msn.lpn_id = p_lpn_id
and msn.current_organization_id = p_org_id
and msn.lot_number = l_lot_rec.lot_number
and msn.inventory_item_id = p_item_id
)
Loop
-- Generate the serial interface id here
if l_serial_interface_id is null then
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_Interface_Id
FROM DUAL;
insert_msni(p_product_transaction_id => l_interface_transaction_id,
p_product_code => 'RCV',
p_interface_id => l_serial_Interface_Id,
p_item_id => p_item_id,
p_lot_number => l_lot_rec.lot_number,
p_fm_serial_number => l_serial_rec.serial_number,
p_to_serial_number => l_serial_rec.serial_number,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_serial_interface_inserted := TRUE;
insert_mtli(p_product_transaction_id => l_interface_transaction_id,
p_product_code => 'RCV',
p_interface_id => l_lot_Interface_Id,
p_org_id => p_org_id,
p_item_id => p_item_id,
p_lot_number => l_lot_rec.lot_number,
p_transaction_quantity => l_lot_rec.quantity,
p_primary_quantity => l_primary_lot_qty,
p_serial_interface_id => l_serial_interface_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
if (l_serial_interface_inserted <> TRUE ) then
For l_serial_rec in ( select msn.serial_number
from mtl_serial_numbers msn
where msn.lpn_id = p_lpn_id
and msn.current_organization_id = p_org_id
and msn.inventory_item_id = p_item_id
)
Loop
-- Generate the serial interface id here
if l_serial_interface_id is null then
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_Interface_Id
FROM DUAL;
insert_msni(p_product_transaction_id => l_interface_transaction_id,
p_product_code => 'RCV',
p_interface_id => l_serial_Interface_Id,
p_item_id => p_item_id,
p_lot_number => null,
p_fm_serial_number => l_serial_rec.serial_number,
p_to_serial_number => l_serial_rec.serial_number,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_serial_interface_inserted := TRUE;
SELECT mmtt.transaction_temp_id txn_tmp_id
, mtrl.lpn_id
, mtrl.inventory_item_id
, mtrl.organization_id
, mtrl.revision
, mtrl.lot_number
, mtrl.project_id
, mtrl.task_id
, mtrl.inspection_status
, Nvl(mmtt.primary_quantity,mtrl.primary_quantity) prim_qty
, Nvl(mmtt.transaction_quantity,mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) txn_qty
, Nvl(mmtt.transaction_uom,mtrl.uom_code) txn_uom_code
, Nvl(mmtt.secondary_transaction_quantity
, mtrl.secondary_quantity-Nvl(mtrl.secondary_quantity_delivered,0)) sec_qty --OPM Convergence
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
WHERE mmtt.move_order_line_id (+)= mtrl.line_id
AND mtrl.lpn_id = v_lpn_id
AND mtrl.line_status = 7
AND mtrl.quantity-Nvl(mtrl.quantity_delivered,0) > 0
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mtrl.header_id);
SELECT rs.rcv_transaction_id transaction_id
, rs.item_id inventory_item_id
, rs.to_organization_id organization_id
, rs.item_revision revision
, Decode(rls.lot_num
,NULL
,rs.quantity
,rls.quantity) quantity
, rs.unit_of_measure unit_of_measure
, rls.lot_num lot_number
, NULL serial_number
, Decode(rt.routing_header_id
,2
,Decode(rt.inspection_status_code
-- Modified for the bug #: 6598429
-- ,'ACCEPT'
,'ACCEPTED'
-- End of bug #: 6598429
,2
,Decode(rt.inspection_status_code
-- Modified for the bug #: 6598429
-- ,'REJECT'
,'REJECTED'
-- End of bug #: 6598429
,3
,Decode(rt.inspection_status_code
,'NOT INSPECTED'
,1
,0)
)
)
, NULL) inspection_status
, rt.project_id project_id
, rt.task_id task_id
FROM rcv_supply rs
, rcv_lots_supply rls
, rcv_transactions rt
WHERE rs.lpn_id = v_lpn_id
AND rs.rcv_transaction_id = rls.transaction_id (+)
AND rs.supply_type_code = 'RECEIVING'
AND rs.rcv_transaction_id = rt.transaction_id
AND NOT exists (SELECT 1
FROM rcv_serials_supply rss
WHERE rss.transaction_id = rs.rcv_transaction_id)
UNION
SELECT rs.rcv_transaction_id transaction_id
, rs.item_id inventory_item_id
, rs.to_organization_id organization_id
, rs.item_revision revision
, 1
, rs.unit_of_measure unit_of_measure
, rss.lot_num lot_number
, rss.serial_num serial_number
, Decode(rt.routing_header_id
,2
,Decode(rt.inspection_status_code
-- Modified for the bug #: 6598429
-- ,'ACCEPT'
,'ACCEPTED'
-- End of bug #: 6598429
,2
,Decode(rt.inspection_status_code
-- Modified for the bug #: 6598429
-- ,'REJECT'
,'REJECTED'
-- End of bug #: 6598429
,3
,Decode(rt.inspection_status_code
,'NOT INSPECTED'
,1
,0)
)
)
, NULL) inspection_status
, rt.project_id project_id
, rt.task_id task_id
FROM rcv_supply rs
, rcv_serials_supply rss
, rcv_transactions rt
WHERE rs.lpn_id = v_lpn_id
AND rs.rcv_transaction_id = rss.transaction_id
AND rs.supply_type_code = 'RECEIVING'
AND rs.rcv_transaction_id = rt.transaction_id;
l_mmtt_to_insert NUMBER;
l_qty_to_insert NUMBER;
l_xfer_lpn_id_to_insert NUMBER;
l_xfer_lpn_to_insert VARCHAR2(30);
l_prim_qty_to_insert NUMBER;
l_delete_rs NUMBER;
l_rti_tb.DELETE;
l_rti_serial_tb.DELETE;
l_delete_rs := 0;
SELECT uom_code
into l_rs_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_rs_tb(l_rs_index).organization_id
AND inventory_item_id = l_rs_tb(l_rs_index).inventory_item_id
AND unit_of_measure = l_rs_tb(l_rs_index).unit_of_measure;
,x_new_mmtt_id => l_mmtt_to_insert
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_mmtt_to_insert := NULL;
l_qty_to_insert := l_avail_rs_qty;
l_delete_rs := 1;
l_mmtt_to_insert := l_mol_mmtt_ctnt_tb(i).txn_tmp_id;
l_qty_to_insert := l_qty_to_match;
l_delete_rs := 0;
l_xfer_lpn_id_to_insert := p_lpn_id;
l_xfer_lpn_to_insert := NULL;
l_xfer_lpn_id_to_insert := p_rti_xfer_lpn_id;
l_xfer_lpn_to_insert := p_rti_xfer_lpn;
p_quantity => l_qty_to_insert,
p_txn_uom_code => l_mol_mmtt_ctnt_tb(i).txn_uom_code,
p_transfer_lpn_id => l_xfer_lpn_id_to_insert,
p_transfer_lpn => l_xfer_lpn_to_insert,
p_txn_source_id => l_rs_tb(l_rs_index).transaction_id,
p_mmtt_temp_id => l_mmtt_to_insert,
p_project_id => l_mol_mmtt_ctnt_tb(i).project_id,
p_task_id => l_mol_mmtt_ctnt_tb(i).task_id,
x_interface_transaction_id => l_transaction_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_lot_interface_id
FROM DUAL;
l_prim_qty_to_insert := inv_rcv_cache.convert_qty(l_rs_tb(l_rs_index).inventory_item_id
,l_qty_to_insert
,l_mol_mmtt_ctnt_tb(i).txn_uom_code
,l_prim_uom_code);
l_prim_qty_to_insert := l_qty_to_insert;
insert_mtli(p_product_transaction_id => l_transaction_id,
p_product_code => 'RCV',
p_interface_id => l_lot_Interface_Id,
p_org_id => l_mol_mmtt_ctnt_tb(i).organization_id,
p_item_id => l_mol_mmtt_ctnt_tb(i).inventory_item_id,
p_lot_number => l_mol_mmtt_ctnt_tb(i).lot_number,
p_transaction_quantity => l_qty_to_insert,
p_primary_quantity => l_prim_qty_to_insert,
p_serial_interface_id => NULL ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_delete_rs := 1;
IF (l_delete_rs = 1) THEN
l_next_rs_index := l_rs_tb.next(l_rs_index);
l_rs_tb.DELETE(l_rs_index);
print_debug('Insert rti with txn_id: '||l_rti_index,4);
,x_new_mmtt_id => l_mmtt_to_insert
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_mmtt_to_insert := NULL;
l_mmtt_to_insert := l_mol_mmtt_ctnt_tb(i).txn_tmp_id;
l_xfer_lpn_id_to_insert := p_lpn_id;
l_xfer_lpn_to_insert := NULL;
l_xfer_lpn_id_to_insert := p_rti_xfer_lpn_id;
l_xfer_lpn_to_insert := p_rti_xfer_lpn;
p_transfer_lpn_id => l_xfer_lpn_id_to_insert,
p_transfer_lpn => l_xfer_lpn_to_insert,
p_txn_source_id => l_rti_index,
p_mmtt_temp_id => l_mmtt_to_insert,
p_project_id => l_mol_mmtt_ctnt_tb(i).project_id,
p_task_id => l_mol_mmtt_ctnt_tb(i).task_id,
x_interface_transaction_id => l_transaction_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_Interface_Id
FROM DUAL;
print_debug('Inserting MSNI for serial: '||l_rti_serial_tb(l_rti_index)(j),4);
insert_msni(p_product_transaction_id => l_transaction_id,
p_product_code => 'RCV',
p_interface_id => l_serial_interface_id,
p_item_id => l_mol_mmtt_ctnt_tb(i).inventory_item_id,
p_lot_number => l_mol_mmtt_ctnt_tb(i).lot_number,
p_fm_serial_number => l_rti_serial_tb(l_rti_index)(j),
p_to_serial_number => l_rti_serial_tb(l_rti_index)(j),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_lot_interface_id
FROM DUAL;
print_debug('Inserting MTLI for lot: '||l_mol_mmtt_ctnt_tb(i).lot_number||
' qty: '||l_rti_tb(l_rti_index).quantity,4);
insert_mtli(p_product_transaction_id => l_transaction_id,
p_product_code => 'RCV',
p_interface_id => l_lot_Interface_Id,
p_org_id => l_mol_mmtt_ctnt_tb(i).organization_id,
p_item_id => l_mol_mmtt_ctnt_tb(i).inventory_item_id,
p_lot_number => l_mol_mmtt_ctnt_tb(i).lot_number,
p_transaction_quantity => l_rti_tb(l_rti_index).quantity,
p_primary_quantity => l_rti_tb(l_rti_index).quantity,
p_serial_interface_id => l_serial_interface_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
PROCEDURE insert_wlpni( p_LPN_ID IN NUMBER ,
p_LICENSE_PLATE_NUMBER IN VARCHAR2 DEFAULT NULL,
p_PARENT_LPN_ID IN NUMBER,
p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2 DEFAULT NULL,
p_REQUEST_ID IN NUMBER DEFAULT NULL,
p_INVENTORY_ITEM_ID IN NUMBER DEFAULT NULL,
p_REVISION IN VARCHAR2 DEFAULT NULL,
p_LOT_NUMBER IN VARCHAR2 DEFAULT NULL,
p_SERIAL_NUMBER IN VARCHAR2 DEFAULT NULL,
p_ORGANIZATION_ID IN NUMBER ,
p_SUBINVENTORY_CODE IN VARCHAR2 DEFAULT NULL,
p_LOCATOR_ID IN NUMBER DEFAULT NULL,
p_GROSS_WEIGHT_UOM_CODE IN VARCHAR2 DEFAULT NULL,
p_GROSS_WEIGHT IN NUMBER DEFAULT NULL,
p_CONTENT_VOLUME_UOM_CODE IN VARCHAR2 DEFAULT NULL,
p_CONTENT_VOLUME IN NUMBER DEFAULT NULL,
p_TARE_WEIGHT_UOM_CODE IN VARCHAR2 DEFAULT NULL,
p_TARE_WEIGHT IN NUMBER DEFAULT NULL,
p_STATUS_ID IN NUMBER DEFAULT NULL,
p_SEALED_STATUS IN NUMBER 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_COST_GROUP_ID IN NUMBER DEFAULT NULL,
p_LPN_CONTEXT IN NUMBER DEFAULT NULL,
p_LPN_REUSABILITY IN NUMBER DEFAULT NULL,
p_OUTERMOST_LPN_ID IN NUMBER DEFAULT NULL,
p_outermost_lpn IN VARCHAR2 DEFAULT NULL,
p_HOMOGENEOUS_CONTAINER IN NUMBER DEFAULT NULL,
p_SOURCE_TYPE_ID IN NUMBER DEFAULT NULL,
p_SOURCE_HEADER_ID IN NUMBER DEFAULT NULL,
p_SOURCE_LINE_ID IN NUMBER DEFAULT NULL,
p_SOURCE_LINE_DETAIL_ID IN NUMBER DEFAULT NULL,
p_SOURCE_NAME IN VARCHAR2 DEFAULT NULL,
p_LPN_GROUP_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
is
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
print_debug('insert_wlpni : - WLPNI for LPNID = ' || p_lpn_id || ' LPN =' ||
p_license_plate_number || ' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
insert into wms_lpn_interface (
LPN_ID ,
LICENSE_PLATE_NUMBER ,
INVENTORY_ITEM_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
REVISION ,
LOT_NUMBER ,
SERIAL_NUMBER ,
ORGANIZATION_ID ,
SUBINVENTORY_CODE ,
LOCATOR_ID ,
PARENT_LPN_ID ,
PARENT_LICENSE_PLATE_NUMBER ,
GROSS_WEIGHT_UOM_CODE ,
GROSS_WEIGHT ,
CONTENT_VOLUME_UOM_CODE ,
CONTENT_VOLUME ,
TARE_WEIGHT_UOM_CODE ,
TARE_WEIGHT ,
STATUS_ID ,
SEALED_STATUS ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
COST_GROUP_ID ,
LPN_CONTEXT ,
LPN_REUSABILITY ,
OUTERMOST_LPN_ID ,
HOMOGENEOUS_CONTAINER ,
SOURCE_TYPE_ID ,
SOURCE_HEADER_ID ,
SOURCE_LINE_ID ,
SOURCE_LINE_DETAIL_ID ,
SOURCE_NAME ,
SOURCE_GROUP_ID )
values
(
p_LPN_ID ,
p_LICENSE_PLATE_NUMBER ,
p_INVENTORY_ITEM_ID ,
l_sysdate ,
l_user ,
l_sysdate ,
l_user ,
l_login_id ,
p_REQUEST_ID ,
null, --PROGRAM_APPLICATION_ID
null, -- PROGRAM_ID
null, -- PROGRAM_UPDATE_DATE
p_REVISION ,
p_LOT_NUMBER ,
p_SERIAL_NUMBER ,
p_ORGANIZATION_ID ,
p_SUBINVENTORY_CODE ,
p_LOCATOR_ID ,
p_PARENT_LPN_ID ,
p_PARENT_LICENSE_PLATE_NUMBER ,
p_GROSS_WEIGHT_UOM_CODE ,
p_GROSS_WEIGHT ,
p_CONTENT_VOLUME_UOM_CODE ,
p_CONTENT_VOLUME ,
p_TARE_WEIGHT_UOM_CODE ,
p_TARE_WEIGHT ,
p_STATUS_ID ,
p_SEALED_STATUS ,
p_ATTRIBUTE_CATEGORY ,
p_ATTRIBUTE1 ,
p_ATTRIBUTE2 ,
p_ATTRIBUTE3 ,
p_ATTRIBUTE4 ,
p_ATTRIBUTE5 ,
p_ATTRIBUTE6 ,
p_ATTRIBUTE7 ,
p_ATTRIBUTE8 ,
p_ATTRIBUTE9 ,
p_ATTRIBUTE10 ,
p_ATTRIBUTE11 ,
p_ATTRIBUTE12 ,
p_ATTRIBUTE13 ,
p_ATTRIBUTE14 ,
p_ATTRIBUTE15 ,
p_COST_GROUP_ID ,
p_LPN_CONTEXT ,
p_LPN_REUSABILITY ,
p_OUTERMOST_LPN_ID ,
p_HOMOGENEOUS_CONTAINER ,
p_SOURCE_TYPE_ID ,
p_SOURCE_HEADER_ID ,
p_SOURCE_LINE_ID ,
p_SOURCE_LINE_DETAIL_ID ,
p_SOURCE_NAME ,
p_LPN_GROUP_ID );
print_debug('insert_wlpni : - End of Insertioin in WLPNI for LPNID = ' || p_lpn_id || ' LPN =' ||
p_license_plate_number || ' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('insert_wlpni : - other exception:'|| l_progress || ' ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')|| 'Error =' || SQLCODE, 1);
inv_mobile_helper_functions.sql_error('INV_RCV_INTEGRATION_PVT.insert_wlpni',l_progress, SQLCODE);
END insert_wlpni;
SELECT DISTINCT
license_plate_number,
subinventory_code,
locator_id,
cost_group_id,
source_type_id,
source_header_id,
source_name,
source_line_id,
source_line_detail_id,
parent_lpn_id,
/*Bug 6600013 */
gross_weight_uom_code,
gross_weight,
content_volume_uom_code,
content_volume,
tare_weight_uom_code,
tare_weight ,
inventory_item_id
/*Bug 6600013 */
INTO
l_license_plate_number,
l_subinventory_code,
l_locator_id,
l_cost_group_id,
l_source_type_id,
l_source_header_id,
l_source_name,
l_source_line_id,
l_source_line_detail_id,
l_parent_lpn_id,
/*Bug 6600013 */
l_gross_wt_uom,
l_gross_wt,
l_vol_uom ,
l_cont_vol,
l_tare_wt_uom,
l_tare_wt,
l_container_item_id
/*Bug 6600013 */
FROM wms_lpn_interface wlpni
WHERE wlpni.license_plate_number = p_license_plate_number
AND wlpni.source_group_id = p_lpn_group_id;
print_debug('CREATE_LPN_FROM_WLPNI - Updated LPN with weight,volume details ',1);
select lpn_id
into x_lpn_id
from rcv_supply rs
where rs.supply_source_id = p_txn_id
and ( (p_lpn_id is not null and rs.lpn_id = p_lpn_id) or (p_lpn_id is null) )
and rownum = 1 ;
PROCEDURE delete_wlpni(p_lpn_group_id IN NUMBER,
p_lpn_id IN NUMBER,
p_license_plate_number IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
is
Begin
-- Deletes rows from WLPNI for the lpn_group
-- Initialize API return status to success
x_return_status := g_ret_sts_success;
delete from wms_lpn_interface where source_group_id = p_lpn_group_id
and ( ( lpn_id = p_lpn_id ) or (license_plate_number = p_license_plate_number) ) ;
End delete_wlpni;
select interface_transaction_id
,transaction_type
,item_id
,item_description
,lpn_id
,transfer_lpn_id
,license_plate_number
,transfer_license_plate_number
,quantity
,shipment_header_id
,routing_header_id
,to_organization_id
,from_organization_id
from rcv_transactions_interface rti
where rti.lpn_group_id = p_lpn_group_id
and ( Nvl(rti.item_id,-1) = -1 AND rti.item_description is null)
-- Bug 3714354
and ( lpn_id is not null or license_plate_number is not null )
order by interface_transaction_id;
select distinct lpn_id
,license_plate_number
from wms_license_plate_numbers
connect by prior lpn_id = parent_lpn_id
start with lpn_id = p_child_lpn_id
;
select wlpn.lpn_id lpn_id
,wlc.inventory_item_id inventory_item_id
,wlc.revision item_revision
,wlc.uom_code mol_uom_code
,NULL mmtt_uom_code
,wlc.organization_id organization_id
,wlc.item_description item_description
,null txn_source_id
,null transaction_temp_id
,NULL project_id
,NULL task_id
,0 mmtt_quantity
,sum(nvl(wlc.quantity,0)) mol_quantity
from wms_license_plate_numbers wlpn,
wms_lpn_contents wlc
where wlc.parent_lpn_id = wlpn.lpn_id
and wlpn.lpn_id = p_child_lpn_id1
group by
wlpn.lpn_id
,wlc.inventory_item_id
,wlc.revision
,wlc.uom_code
,wlc.organization_id
,wlc.item_description
;
select lpn_id
,license_plate_number
,parent_lpn_id
,organization_id
from wms_license_plate_numbers
where parent_lpn_id = p_lpn_id1;
select count(distinct decode(rti.TRANSACTION_TYPE,'ACCEPT','INSPECT TXN','REJECT','INSPECT TXN',rti.TRANSACTION_TYPE))
into l_other_txn_exists
from rcv_transactions_interface rti
where rti.lpn_group_id = p_lpn_group_id
-- Bug 3714354
and ( lpn_id is not null or license_plate_number is not null )
;
insert_wlpni( p_LPN_ID => l_immediate_child_rec.lpn_id,
p_LICENSE_PLATE_NUMBER => l_immediate_child_rec.license_plate_number,
p_PARENT_LPN_ID => l_lpn_rec.transfer_lpn_id,
p_PARENT_LICENSE_PLATE_NUMBER => l_lpn_rec.transfer_license_plate_number,
p_organization_id => l_lpn_rec.to_organization_id,
p_LPN_GROUP_ID => p_LPN_GROUP_ID,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
print_debug('Explode LPN: Before insert_wlpni else ', 1);
insert_wlpni( p_LPN_ID => l_lpn_rec.lpn_id,
p_LICENSE_PLATE_NUMBER => l_lpn_rec.license_plate_number,
p_PARENT_LPN_ID => null,
p_PARENT_LICENSE_PLATE_NUMBER => null,
p_organization_id => l_lpn_rec.to_organization_id,
p_LPN_GROUP_ID => p_LPN_GROUP_ID,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT 'Y'
INTO l_sub_match
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = p_lpn_id
AND ((wlpn.lpn_context <> 5
AND (Nvl(wlpn.subinventory_code,'@@@') = Nvl(p_subinventory,'@@@'))
AND (Nvl(wlpn.locator_id,-1) = Nvl(p_locator_id,-1)))
OR wlpn.lpn_context = 5)
AND wlpn.organization_id = p_organization_id
AND ROWNUM = 1
;
select PARENT_LPN_ID
into x_parent_lpn_id
from wms_license_plate_numbers
where license_plate_number = p_license_plate_number
;
select PARENT_LPN_ID,
PARENT_LICENSE_PLATE_NUMBER
into x_parent_lpn_id,
x_parent_lpn
from wms_lpn_interface
where lpn_id = p_lpn_id
and source_group_id = p_lpn_group_id
;
select PARENT_LPN_ID,
PARENT_LICENSE_PLATE_NUMBER
into x_parent_lpn_id,
x_parent_lpn
from wms_lpn_interface
where license_plate_number = p_license_plate_number
and source_group_id = p_lpn_group_id;
FOR l_outermost_lpn IN (SELECT lpn_id, license_plate_number,
Nvl(parent_lpn_id, outermost_lpn_id) parent_lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_from_lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id)
LOOP
l_outermost_lpn_id := l_outermost_lpn.lpn_id;
SELECT '1'
INTO l_dummy
FROM wms_lpn_interface
WHERE source_group_id = p_lpn_group_id
AND (lpn_id = l_outermost_lpn.lpn_id
OR license_plate_number = l_outermost_lpn.license_plate_number)
AND Nvl(parent_lpn_id, -1) <> l_outermost_lpn.parent_lpn_id;
select lpn_id lpn_id,
license_plate_number license_plate_number
from wms_license_plate_numbers wlpn
connect by prior wlpn.lpn_id = wlpn.parent_lpn_id
start with wlpn.lpn_id = l_outermost_lpn_id
)
Loop
For l_lpn_contents in (
select
wlc.parent_lpn_id lpn_id
,l_wln_csr.license_plate_number license_plate_number
,wlc.inventory_item_id inventory_item_id
,wlc.revision item_revision
,wlc.organization_id organization_id
--,wlc.item_description item_description
,sum(get_primary_qty(wlc.organization_id, wlc.inventory_item_id, wlc.uom_code,nvl(wlc.quantity,0)))
primary_quantity
from wms_lpn_contents wlc
where wlc.parent_lpn_id = l_wln_csr.lpn_id
-- 4507808
-- Commented the exists statement below
-- by the l_wln_csr above.
-- where wlc.parent_lpn_id = wln.lpn_id
-- AND exists ( select '1' from wms_license_plate_numbers wlpn
-- where wlc.parent_lpn_id = wlpn.lpn_id
-- connect by prior wlpn.lpn_id = wlpn.parent_lpn_id
-- start with wlpn.lpn_id = l_outermost_lpn_id
--union all
--select '1' from wms_license_plate_numbers wlpn1
--where wlc.parent_lpn_id = wlpn1.lpn_id
--and not exists ( select 1 from wms_lpn_interface wlpni
-- where wlpni.lpn_id = p_from_lpn_id and
-- Nvl(wlpni.parent_lpn_id, -1) <> Nvl(p_parent_lpn_id, -1)
-- )
--connect by prior wlpn1.parent_lpn_id = wlpn1.lpn_id
--start with wlpn1.lpn_id = p_from_lpn_id
-- )
group by
wlc.parent_lpn_id
,l_wln_csr.license_plate_number
,wlc.inventory_item_id
,wlc.revision
,wlc.organization_id
--,wlc.item_description
)
Loop
IF (l_debug = 1) THEN
print_debug('VALIDATE_TOTAL_QTY: Inventory_Item_Id = '||l_lpn_contents.inventory_item_id,1);
select sum(primary_quantity)
into l_rti_qty
from rcv_transactions_interface rti
where rti.lpn_group_id = p_lpn_group_id
and (rti.lpn_id = l_lpn_contents.lpn_id
AND rti.PROCESSING_STATUS_CODE <> 'WSH_INTERFACED' --RTV Change 16197273
OR rti.license_plate_number = l_lpn_contents.license_plate_number)
-- 3397823
-- and rti.transfer_lpn_id = l_lpn_contents.lpn_id
and rti.item_id = l_lpn_contents.inventory_item_id
and decode(rti.transaction_type,'ACCEPT','INSPECT','REJECT','INSPECT',rti.transaction_type)
= decode(p_transaction_type,'ACCEPT','INSPECT','REJECT','INSPECT',p_transaction_type)
and ( (l_lpn_contents.item_revision is null) or (rti.item_revision = l_lpn_contents.item_revision))
--and ( (l_lpn_contents.item_description is null) or (rti.item_description = l_lpn_contents.item_description ))
;
select lpn_id lpn_id,
license_plate_number license_plate_number
from wms_license_plate_numbers wlpn
connect by prior wlpn.lpn_id = wlpn.parent_lpn_id
start with wlpn.lpn_id = l_outermost_lpn_id
)
Loop
For l_lpn_contents in (
select
wlc.parent_lpn_id lpn_id
,l_wln_csr.license_plate_number license_plate_number
,wlc.inventory_item_id inventory_item_id
,wlc.revision item_revision
,wlc.organization_id organization_id
--,wlc.item_description item_description
,wlc.lot_number lot_number
,sum(get_primary_qty(wlc.organization_id, wlc.inventory_item_id, wlc.uom_code,nvl(wlc.quantity,0)))
primary_quantity
from wms_lpn_contents wlc
where wlc.parent_lpn_id = l_wln_csr.lpn_id
-- 4507808
-- Commented the exists statement below
-- and replaced by the outer cursor l_wln_csr
-- where wlc.parent_lpn_id = wln.lpn_id
-- AND exists ( select '1' from wms_license_plate_numbers wlpn
-- where wlc.parent_lpn_id = wlpn.lpn_id
-- connect by prior wlpn.lpn_id = wlpn.parent_lpn_id
-- start with wlpn.lpn_id = l_outermost_lpn_id
--union all
--select '1' from wms_license_plate_numbers wlpn1
--where wlc.parent_lpn_id = wlpn1.lpn_id
--and not exists ( select 1 from wms_lpn_interface wlpni
-- where wlpni.lpn_id = p_from_lpn_id and
-- Nvl(wlpni.parent_lpn_id, -1) <> Nvl(p_parent_lpn_id, -1)
-- )
--connect by prior wlpn1.parent_lpn_id = wlpn1.lpn_id
--start with wlpn1.lpn_id = p_from_lpn_id
-- )
group by
wlc.parent_lpn_id
,l_wln_csr.license_plate_number
,wlc.inventory_item_id
,wlc.revision
,wlc.organization_id
--,wlc.item_description
,wlc.lot_number
)
Loop
IF inv_cache.set_item_rec(l_lpn_contents.organization_id , l_lpn_contents.inventory_item_id) THEN --15959206
l_lot_code := inv_cache.item_rec.lot_control_code;
select sum(primary_quantity)
into l_rti_qty
from rcv_transactions_interface rti
where rti.lpn_group_id = p_lpn_group_id
and (rti.lpn_id = l_lpn_contents.lpn_id
OR rti.license_plate_number = l_lpn_contents.license_plate_number)
-- 3397823
--and rti.transfer_lpn_id = l_lpn_contents.lpn_id
and rti.item_id = l_lpn_contents.inventory_item_id
and decode(rti.transaction_type,'ACCEPT','INSPECT','REJECT','INSPECT',rti.transaction_type)
= decode(p_transaction_type,'ACCEPT','INSPECT','REJECT','INSPECT',p_transaction_type)
and ( (l_lpn_contents.item_revision is null) or (rti.item_revision = l_lpn_contents.item_revision))
--and ( (l_lpn_contents.item_description is null) or (rti.item_description = l_lpn_contents.item_description ))
AND l_lpn_contents.lot_number IS NULL;
select sum(mtli.primary_quantity)
into l_rti_qty
from rcv_transactions_interface rti
, mtl_transaction_lots_interface mtli
where rti.lpn_group_id = p_lpn_group_id
and (rti.lpn_id = l_lpn_contents.lpn_id
OR rti.license_plate_number = l_lpn_contents.license_plate_number)
-- 3397823
-- and rti.transfer_lpn_id = l_lpn_contents.lpn_id
and rti.item_id = l_lpn_contents.inventory_item_id
and decode(rti.transaction_type,'ACCEPT','INSPECT','REJECT','INSPECT',rti.transaction_type)
= decode(p_transaction_type,'ACCEPT','INSPECT','REJECT','INSPECT',p_transaction_type)
and ( (l_lpn_contents.item_revision is null) or (rti.item_revision = l_lpn_contents.item_revision))
--and ( (l_lpn_contents.item_description is null) or (rti.item_description = l_lpn_contents.item_description ))
AND l_lpn_contents.lot_number = mtli.lot_number
AND mtli.product_code = 'RCV'
AND mtli.product_transaction_id = rti.interface_transaction_id
;
select serial_number
from mtl_serial_numbers msn
WHERE msn.lpn_id = l_lpn_contents.lpn_id
AND msn.inventory_item_id = l_lpn_contents.inventory_item_id
)
Loop
BEGIN
SELECT 1
INTO l_serial_disc
FROM mtl_serial_numbers_interface msni
, rcv_transactions_interface rti
where rti.lpn_group_id = p_lpn_group_id
and (rti.lpn_id = l_lpn_contents.lpn_id
OR rti.license_plate_number = l_lpn_contents.license_plate_number)
-- 3397823
-- and rti.transfer_lpn_id = l_lpn_contents.lpn_id
and rti.item_id = l_lpn_contents.inventory_item_id
AND msni.product_code = 'RCV'
AND msni.product_transaction_id = rti.interface_transaction_id
and l_test_serial_rec.serial_number between msni.fm_serial_number and msni.to_serial_number
AND Length(l_test_serial_rec.serial_number) = Length(msni.fm_serial_number)
AND length(msni.fm_serial_number)=Length(Nvl(msni.to_serial_number,msni.fm_serial_number)) --BUG 3818544
and rownum = 1
;
select interface_transaction_id
,transaction_type
,item_id
,item_description
,lpn_id
,transfer_lpn_id
,license_plate_number
,transfer_license_plate_number
,quantity
,shipment_header_id
,shipment_num
,routing_header_id
,to_organization_id
,nvl(from_organization_id,to_organization_id) from_organization_id -- This is needed
,subinventory -- as for some txns like ASN Receive, TRANSFER, DELIVER
,locator_id -- the from organization_id is null in rti
,from_subinventory -- so we have to take the to_organization_id
,from_locator_id
,parent_transaction_id
,source_document_code
,auto_transact_code
,Nvl(mobile_txn, 'N') mobile_txn
,processing_mode_code
,inv_transaction_id
,mmtt_temp_id
,Nvl(express_transaction,'N') express_transaction --Bug 5550783
,item_revision -- bug 13938607
,uom_code
,validation_flag
,interface_source_line_id --RTV Change 16197273
from rcv_transactions_interface rti
where rti.lpn_group_id = p_lpn_group_id
AND PROCESSING_STATUS_CODE <> 'WSH_INTERFACED' --RTV Change 16197273
order by interface_transaction_id;
select lpn_id
,license_plate_number
from wms_license_plate_numbers
where outermost_lpn_id = p_child_lpn_id;
select lpn_id
,license_plate_number
from wms_license_plate_numbers
where parent_lpn_id = p_child_lpn_id;
select parent_lpn_id
,inventory_item_id
,quantity
from wms_lpn_contents
where parent_lpn_id = p_child_lpn_id;
l_update_lpn_id BOOLEAN := FALSE;
SELECT intransit_type
INTO l_intransit_type
FROM mtl_interorg_parameters
WHERE from_organization_id = l_lpn_rec.from_organization_id
AND to_organization_id = l_lpn_rec.to_organization_id;
-- See if we need to update the lpn_id or transfer_lpn_id in rti.
IF ((l_lpn_rec.lpn_id IS NULL AND l_lpn_rec.license_plate_number IS NOT NULL)
OR (l_lpn_rec.transfer_lpn_id IS NULL AND l_lpn_rec.transfer_license_plate_number IS NOT NULL)) THEN
l_update_lpn_id := TRUE;
select count(distinct interface_transaction_id)
into l_lpn_already_processed
from rcv_transactions_interface rti
where rti.lpn_group_id = p_lpn_group_id
and ( ( rti.lpn_id = l_lpn_rec.lpn_id) or (rti.license_plate_number = l_lpn_rec.license_plate_number))
and rti.interface_transaction_id < l_lpn_rec.interface_transaction_id ;
SELECT wms_epc_s2.NEXTVAL
INTO g_epc_group_id
FROM dual;
select RSH.shipment_header_id ,
RSH.ASN_TYPE,
WLPN.LPN_CONTEXT
into l_source_header_id ,
l_asn_type,
l_lpn_context
from WMS_LICENSE_PLATE_NUMBERS WLPN,
RCV_SHIPMENT_HEADERS RSH
where WLPN.lpn_id = l_lpn_rec.lpn_id
AND (
((rsh.receipt_source_code IN ('INVENTORY','INTERNAL ORDER')
AND rsh.organization_id = wlpn.organization_id) --BUG 4730474
and wlpn.source_header_id is null
and wlpn.source_name=rsh.shipment_num)
OR
(Nvl(rsh.receipt_source_code,'#$#') NOT IN ('INVENTORY','INTERNAL ORDER')
and rsh.shipment_header_id=wlpn.source_header_id)
)
and rownum = 1 ; --bug 5749200 rearranged the where clause of the query
SELECT shipment_header_id
INTO l_lpn_rec.shipment_header_id
FROM rcv_shipment_headers
WHERE shipment_num = l_lpn_rec.shipment_num;
select count(*) into l_lpn_Count
from rcv_transactions_interface
where lpn_id = l_lpn_rec.lpn_id
and transaction_type ='DELIVER'
and interface_transaction_id < l_lpn_rec.interface_transaction_id
and lpn_group_id = p_lpn_group_id;
-- select 1
-- into l_insp_mat_exists
-- from mtl_txn_request_lines mol
-- where mol.lpn_id = l_lpn_rec.lpn_id
-- and nvl(mol.inspection_status,2) = 1
-- and rownum = 1;
SELECT subinventory_code
, locator_id
INTO l_validate_sub
, l_validate_loc_id
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_rec.transfer_lpn_id
OR license_plate_number = l_lpn_rec.transfer_license_plate_number;
select distinct
parent_lpn_id,
parent_license_plate_number
into l_PTLPN_ID,
l_PTLPN
from wms_lpn_interface wlpni
where ( nvl(wlpni.license_plate_number,'-1') = nvl(l_WLPNIQLPN,'-1') or
nvl(wlpni.lpn_id,'-1') = nvl(l_WLPNIQLPN_ID,'-1') )
and wlpni.source_group_id = p_lpn_group_id;
--we need to update the lpn_context and sub/loc for the
--transfer lpn right here so that it has the right context.
--we need to do it before the packunpack
--otherwise packunpack fails
update_lpn_location_context(p_organization_id => NULL
,p_sub => l_lpn_rec.subinventory
,p_locator => l_lpn_rec.locator_id
,p_lpn_context => l_parent_lpn_context
,p_lpn_id => l_WLPNIQLPN_ID
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_source_name => l_parent_source_name
,p_source_header_id => l_parent_source_header_id
,p_source_type_id => l_parent_source_type_id);
--we need to update the lpn_context for the
--parent right here so that it has the right context.
update_lpn_location_context(p_organization_id => NULL
,p_sub => l_lpn_rec.subinventory
,p_locator => l_lpn_rec.locator_id
,p_lpn_context => l_parent_lpn_context
,p_lpn_id => l_PTLPN_ID
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_source_name => l_parent_source_name
,p_source_header_id => l_parent_source_header_id
,p_source_type_id => l_parent_source_type_id);
--we need to update the lpn_context and sub/loc for the
--transfer lpn right here so that it has the right context.
--we need to do it before the packunpack
--otherwise packunpack fails
IF (l_debug = 1) THEN
print_debug('Going to update the lpn:'|| l_WLPNIQLPN_ID,1);
update_lpn_location_context(p_organization_id => NULL
,p_sub => l_lpn_rec.subinventory
,p_locator => l_lpn_rec.locator_id
,p_lpn_context => l_parent_lpn_context
,p_lpn_id => l_WLPNIQLPN_ID
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_source_name => l_parent_source_name
,p_source_header_id => l_parent_source_header_id
,p_source_type_id => l_parent_source_type_id);
--we need to update the lpn_context for the
--parent right here so that it has the right context.
update_lpn_location_context(p_organization_id => NULL
,p_sub => l_lpn_rec.subinventory
,p_locator => l_lpn_rec.locator_id
,p_lpn_context => l_parent_lpn_context
,p_lpn_id => l_PTLPN_ID
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_source_name => l_parent_source_name
,p_source_header_id => l_parent_source_header_id
,p_source_type_id => l_parent_source_type_id);
delete_wlpni(p_lpn_group_id,l_WLPNIQLPN_ID,l_WLPNIQLPN,x_return_status,x_msg_count,x_msg_data);
select transaction_type
into l_parent_txn_type
from rcv_transactions rt
where rt.transaction_id = l_lpn_rec.parent_transaction_id;
select parent_transaction_id
into l_parent_parent_txn_id
from rcv_transactions rt
where rt.transaction_id = l_lpn_rec.parent_transaction_id;
select transaction_type
into l_parent_parent_txn_type
from rcv_transactions rt
where rt.transaction_id = l_parent_parent_txn_id;
select parent_transaction_id
into l_parent_parent_parent_txn_id
from rcv_transactions rt
where rt.transaction_id = l_parent_parent_txn_id;
IF (l_update_lpn_id = TRUE) THEN
UPDATE rcv_transactions_interface
SET lpn_id = l_lpn_rec.lpn_id
, transfer_lpn_id = l_lpn_rec.transfer_lpn_id
WHERE interface_transaction_id = l_lpn_rec.interface_transaction_id;
For c_wlpni_rec in ( select lpn_id,
license_plate_number,
parent_lpn_id,
parent_license_plate_number,
organization_id,
rowid
from wms_lpn_interface
where source_group_id = p_lpn_group_id)
Loop
l_progress := 'WMSINB-20081';
SELECT rti.subinventory,
rti.locator_id,
rti.to_organization_id,
rti.shipment_header_id,
rti.shipment_num,
rti.transaction_type
INTO l_parent_sub,
l_parent_locator_id,
l_parent_to_organization_id,
l_parent_source_header_id,
l_parent_source_name,
l_parent_txn_type
FROM rcv_transactions_interface rti
WHERE rti.lpn_id = rti.transfer_lpn_id
AND rti.lpn_group_id = p_lpn_group_id
AND rti.lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers wlpn2
CONNECT BY PRIOR wlpn2.lpn_id = wlpn2.parent_lpn_id
START WITH wlpn2.lpn_id = c_wlpni_rec.LPN_ID
)
AND NOT exists ( SELECT 'x' FROM wms_lpn_contents
WHERE parent_lpn_id = c_wlpni_rec.LPN_ID
)
AND ROWNUM = 1
;
update_lpn_location_context(p_organization_id => l_parent_to_organization_id
,p_sub => l_parent_sub
,p_locator => l_parent_locator_id
,p_lpn_context => null
,p_lpn_id => c_wlpni_rec.lpn_id
,p_source_name => l_parent_source_name
,p_source_header_id => l_parent_source_header_id
,p_source_type_id => l_parent_source_type_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
--We also need to update sub/loc/sourc_name etc for parent
update_lpn_location_context(p_organization_id => l_parent_to_organization_id
,p_sub => l_parent_sub
,p_locator => l_parent_locator_id
,p_lpn_context => null
,p_lpn_id => c_wlpni_rec.parent_lpn_id
,p_source_name => l_parent_source_name
,p_source_header_id => l_parent_source_header_id
,p_source_type_id => l_parent_source_type_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
delete from wms_lpn_interface where source_group_id = p_lpn_group_id;
PROCEDURE insert_mtli_rowid (p_rowid IN ROWID,
p_product_txn_id IN NUMBER,
p_new_serial_txn_temp_id IN NUMBER,
p_quantity IN NUMBER,
p_primary_quantity IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
print_debug('insert_mtli_rowid:010: Entry Stamp :' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
INSERT INTO mtl_transaction_lots_interface
(TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_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_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
ERROR_CODE,
PROCESS_FLAG,
DESCRIPTION,
VENDOR_NAME,
SUPPLIER_LOT_NUMBER,
ORIGINATION_DATE,
DATE_CODE,
GRADE_CODE,
CHANGE_DATE,
MATURITY_DATE,
STATUS_ID,
RETEST_DATE,
AGE,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
WIDTH,
WIDTH_UOM,
CURL_WRINKLE_FOLD,
LOT_ATTRIBUTE_CATEGORY,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
VENDOR_ID,
TERRITORY_CODE,
/*INVCONV, Remove sublot_num, add other attributes. Punit Kumar */
---sublot_num,
PARENT_LOT_NUMBER ,
ORIGINATION_TYPE ,
EXPIRATION_ACTION_DATE ,
EXPIRATION_ACTION_CODE ,
HOLD_DATE ,
REASON_ID ,
/* end INVCONV */
reason_code,
secondary_transaction_quantity,
PRODUCT_CODE,
product_transaction_id)
(SELECT TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
Ltrim(Rtrim(lot_number)),
LOT_EXPIRATION_DATE,
p_quantity,
p_primary_quantity, -- Calculate
p_new_serial_txn_temp_id, -- New sequence
ERROR_CODE,
PROCESS_FLAG,
DESCRIPTION,
VENDOR_NAME,
SUPPLIER_LOT_NUMBER,
ORIGINATION_DATE,
DATE_CODE,
GRADE_CODE,
CHANGE_DATE,
MATURITY_DATE,
STATUS_ID,
RETEST_DATE,
AGE,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
WIDTH,
WIDTH_UOM,
CURL_WRINKLE_FOLD,
LOT_ATTRIBUTE_CATEGORY,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
VENDOR_ID,
TERRITORY_CODE,
/*INVCONV , remove sublot_num, add other attributes.Punit Kumar*/
----sublot_num,
PARENT_LOT_NUMBER,
ORIGINATION_TYPE ,
EXPIRATION_ACTION_DATE ,
EXPIRATION_ACTION_CODE ,
HOLD_DATE ,
REASON_ID ,
/* end INVCONV */
reason_code,
NULL,
PRODUCT_CODE,
p_product_txn_id
FROM mtl_transaction_lots_interface
WHERE ROWID = p_rowid);
print_debug('insert_mtli_rowid:020: Exit Stamp :' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('INVCONV, removed sublot_num, Added some more parameters in INSERT INTO mtl_transaction_lots_interface (insert_mtli_rowid)', 1);
print_debug('insert_mtli_rowid - other exception: '||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
inv_mobile_helper_functions.sql_error('INV_RCV_INTEGRATION_PVT.insert_mtli_rowid',Sqlerrm,SQLCODE);
END insert_mtli_rowid;
PROCEDURE insert_msni_rowid (p_rowid IN ROWID,
p_product_txn_id IN NUMBER,
p_new_serial_txn_temp_id IN NUMBER DEFAULT NULL,
p_new_fm_ser_num IN VARCHAR2 DEFAULT NULL,
p_new_to_ser_num IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
print_debug('insert_msni_rowid:010: Entry Stamp :' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
INSERT INTO mtl_serial_numbers_interface
(TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
VENDOR_SERIAL_NUMBER,
VENDOR_LOT_NUMBER,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
ERROR_CODE,
PROCESS_FLAG,
PARENT_SERIAL_NUMBER,
SERIAL_ATTRIBUTE_CATEGORY,
ORIGINATION_DATE,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
STATUS_ID,
TERRITORY_CODE,
TIME_SINCE_NEW,
CYCLES_SINCE_NEW,
TIME_SINCE_OVERHAUL,
CYCLES_SINCE_OVERHAUL,
TIME_SINCE_REPAIR,
CYCLES_SINCE_REPAIR,
TIME_SINCE_VISIT,
CYCLES_SINCE_VISIT,
TIME_SINCE_MARK,
CYCLES_SINCE_MARK,
NUMBER_OF_REPAIRS,
STATUS_NAME,
C_ATTRIBUTE17,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)
(SELECT Nvl(p_new_serial_txn_temp_id, transaction_interface_id),
SOURCE_CODE,
SOURCE_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
VENDOR_SERIAL_NUMBER,
VENDOR_LOT_NUMBER,
Nvl(p_new_fm_ser_num,fm_serial_number),
Nvl(p_new_to_ser_num, to_serial_number),
ERROR_CODE,
PROCESS_FLAG,
PARENT_SERIAL_NUMBER,
SERIAL_ATTRIBUTE_CATEGORY,
ORIGINATION_DATE,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
STATUS_ID,
TERRITORY_CODE,
TIME_SINCE_NEW,
CYCLES_SINCE_NEW,
TIME_SINCE_OVERHAUL,
CYCLES_SINCE_OVERHAUL,
TIME_SINCE_REPAIR,
CYCLES_SINCE_REPAIR,
TIME_SINCE_VISIT,
CYCLES_SINCE_VISIT,
TIME_SINCE_MARK,
CYCLES_SINCE_MARK,
NUMBER_OF_REPAIRS,
STATUS_NAME,
C_ATTRIBUTE17,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PRODUCT_CODE,
p_product_txn_id
FROM mtl_serial_numbers_interface
WHERE ROWID = p_rowid);
print_debug('insert_msni_rowid:020: Exit Stamp :' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('insert_msni_rowid - other exception: '||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
inv_mobile_helper_functions.sql_error('INV_RCV_INTEGRATION_PVT.insert_msni_rowid',Sqlerrm, SQLCODE);
END insert_msni_rowid;
SELECT Ltrim(Rtrim(mtli.lot_number)) lot_number,
mtli.transaction_quantity,
mtli.primary_quantity,
mtli.serial_transaction_temp_id,
mtli.ROWID
FROM mtl_transaction_lots_interface mtli, rcv_lots_supply rls
WHERE mtli.product_code = 'RCV'
AND mtli.product_transaction_id = l_old_rti_id
AND mtli.lot_number = rls.lot_num --14573375 removed outer join
AND NVL(rls.shipment_line_id,l_shipment_line_id) = l_shipment_line_id
ORDER BY rls.shipment_line_id;
SELECT Ltrim(Rtrim(mtli.lot_number)) lot_number,
mtli.transaction_quantity,
mtli.primary_quantity,
mtli.serial_transaction_temp_id,
mtli.ROWID
FROM mtl_transaction_lots_interface mtli
WHERE mtli.product_code = 'RCV'
AND mtli.product_transaction_id = l_old_rti_id;
SELECT msni.fm_serial_number,
msni.to_serial_number,
msni.ROWID
FROM mtl_serial_numbers_interface msni,rcv_serials_supply rss
WHERE msni.product_code = 'RCV'
AND msni.product_transaction_id = l_old_rti_id
AND msni.fm_serial_number = rss.serial_num(+)
AND NVL(rss.shipment_line_id,l_shipment_line_id) = l_shipment_line_id --10248599
ORDER BY rss.shipment_line_id;
SELECT msni.fm_serial_number,
msni.to_serial_number,
msni.ROWID
FROM mtl_serial_numbers_interface msni
WHERE msni.product_code = 'RCV'
AND msni.product_transaction_id = l_old_rti_id;
SELECT fm_serial_number,
to_serial_number,
ROWID
FROM mtl_serial_numbers_interface
WHERE transaction_interface_id = l_serial_transaction_temp_id;
SELECT SUM(transaction_quantity)
INTO l_total_lot_qty
FROM mtl_transaction_lots_interface
WHERE product_code = 'RCV'
AND product_transaction_id = l_orig_interface_trx_id;
SELECT SUM(inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number))
INTO l_total_serial_qty
FROM mtl_serial_numbers_interface
WHERE product_code = 'RCV'
AND product_transaction_id = l_orig_interface_trx_id;
select uom_code,
unit_of_measure,
item_id,
to_organization_id
into l_uom_code,
l_unit_of_measure,
l_item_id,
l_to_organization_id
from rcv_transactions_interface rti
where rti.interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
SELECT uom_code
into l_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_to_organization_id
AND inventory_item_id = l_item_id
AND unit_of_measure = l_unit_of_measure;
SELECT SUM(mtli.transaction_quantity)
INTO l_lot_ser_qty
FROM mtl_transaction_lots_interface mtli
WHERE mtli.product_code = 'RCV'
AND mtli.product_transaction_id = l_orig_interface_trx_id
AND (mtli.serial_transaction_temp_id IS NULL
OR nvl(mtli.primary_quantity,
get_primary_qty(l_to_organization_id,l_item_id,l_uom_code,
mtli.transaction_quantity)
)
= (SELECT SUM(inv_serial_number_pub.get_serial_diff(msni.fm_serial_number,
msni.to_serial_number))
FROM mtl_serial_numbers_interface msni
WHERE msni.transaction_interface_id = mtli.serial_transaction_temp_id));
select uom_code,
unit_of_measure,
item_id,
to_organization_id
into l_uom_code,
l_unit_of_measure,
l_item_id,
l_to_organization_id
from rcv_transactions_interface rti
where rti.interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
SELECT uom_code
into l_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_to_organization_id
AND inventory_item_id = l_item_id
AND unit_of_measure = l_unit_of_measure;
SELECT SUM(transaction_quantity)
INTO l_total_lot_qty
FROM mtl_transaction_lots_interface
WHERE product_code = 'RCV'
AND product_transaction_id = l_orig_interface_trx_id
AND (p_rti_tb(newrti).lot_number IS NULL OR (p_rti_tb(newrti).lot_number IS NOT NULL AND lot_number=p_rti_tb(newrti).lot_number)); -- for 13972742
SELECT SUM(inv_serial_number_pub.get_serial_diff(fm_serial_number,to_serial_number))
INTO l_total_serial_qty
FROM mtl_serial_numbers_interface
WHERE product_code = 'RCV'
AND product_transaction_id = l_orig_interface_trx_id;
SELECT SUM(mtli.transaction_quantity)
INTO l_lot_ser_qty
FROM mtl_transaction_lots_interface mtli
WHERE mtli.product_code = 'RCV'
AND mtli.product_transaction_id = l_orig_interface_trx_id
AND (mtli.serial_transaction_temp_id IS NULL
OR nvl(mtli.primary_quantity,
get_primary_qty(l_to_organization_id,l_item_id,l_uom_code,
mtli.transaction_quantity)
) = (SELECT SUM(inv_serial_number_pub.get_serial_diff(msni.fm_serial_number,
msni.to_serial_number))
FROM mtl_serial_numbers_interface msni
WHERE msni.transaction_interface_id = mtli.serial_transaction_temp_id))
AND (p_rti_tb(newrti).lot_number IS NULL OR (p_rti_tb(newrti).lot_number IS NOT NULL AND lot_number=p_rti_tb(newrti).lot_number)); -- for 13972742
select uom_code,
unit_of_measure,
item_id,
to_organization_id
into l_uom_code,
l_unit_of_measure,
l_item_id,
l_to_organization_id
from rcv_transactions_interface rti
where rti.interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
SELECT uom_code
into l_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = l_to_organization_id
AND inventory_item_id = l_item_id
AND unit_of_measure = l_unit_of_measure;
select shipment_line_id
into l_shipment_line_id
from rcv_transactions_interface
where interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
MTLIs are not updated with the correct product_transaction_id and the transaction fails
since RTP is not able to find the Lot.
Changes done: Wrote an outer loop and run the same maximum twice or till the time
the remaining quantity is <=0.
Note: If the shipment_line_id is not null and the the outer loop runs second time,
this means that the cursor either didnt find any rows or the matching did not happen perfectly.
Hence the second time the loop runs, we null out the shipment_line_id and open the mtli
cursor without shipment line.
*/
--14573375
l_remaining_quantity := p_rti_tb(newrti).quantity;
-- update the mtli with new rti id.
UPDATE mtl_transaction_lots_interface
SET product_transaction_id = p_rti_tb(newrti).new_interface_trx_id
WHERE ROWID = l_mtli_rec.ROWID;
-- update the serial records if exist.
IF (l_mtli_rec.serial_transaction_temp_id IS NOT NULL) THEN
OPEN msni_recs_lotserial(l_mtli_rec.serial_transaction_temp_id);
print_debug('split_lot_serial:170: update msni ', 1);
-- update the msni with new rti id.
UPDATE mtl_serial_numbers_interface
SET product_transaction_id = p_rti_tb(newrti).new_interface_trx_id
WHERE ROWID = l_msni_rec.ROWID;
-- insert a new row with transaction_quantity = l_remaining
-- quantity and new rti id. And update the original row
-- with transaction_quantity =
-- l_mtli_rec.transaction_quantity - l_remaining_quantity
IF (l_mtli_rec.serial_transaction_temp_id IS NOT NULL)
THEN
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_txn_temp_id
FROM DUAL;
print_debug('split_lot_serial:180: insert mtli rowid ', 1);
insert_mtli_rowid (p_rowid => l_mtli_rec.ROWID,
p_product_txn_id => p_rti_tb(newrti).new_interface_trx_id,
p_new_serial_txn_temp_id => l_new_serial_txn_temp_id,
p_quantity => l_remaining_quantity,
p_primary_quantity => l_primary_rem_qty,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('split_lot_serial:185: insert mtli rowid failed ', 1);
print_debug('split_lot_serial:190: update mtli ', 1);
-- update the original row
-- For OPM null out the secondary_transaction_quantity
UPDATE mtl_transaction_lots_interface
SET transaction_quantity = transaction_quantity -
l_remaining_quantity,
primary_quantity = primary_quantity - l_primary_rem_qty,
secondary_transaction_quantity = NULL
WHERE ROWID = l_mtli_rec.ROWID;
-- update the msni record with new transaction
-- id and new serial_transaction_temp_id
IF (l_debug = 1) THEN
print_debug('split_lot_serial:210: update msni ', 1);
UPDATE mtl_serial_numbers_interface
SET product_transaction_id = p_rti_tb(newrti).new_interface_trx_id,
transaction_interface_id = l_new_serial_txn_temp_id
WHERE ROWID = l_msni_rec.ROWID;
print_debug('split_lot_serial:220: insert msni rowid', 1);
--insert new msni row.
insert_msni_rowid (p_rowid =>l_msni_rec.ROWID,
p_product_txn_id => p_rti_tb(newrti).new_interface_trx_id,
p_new_serial_txn_temp_id => l_new_serial_txn_temp_id,
p_new_fm_ser_num => null,
p_new_to_ser_num => l_new_to_ser_num,
x_return_status =>x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('split_lot_serial:225: insert msni rowid failed ', 1);
-- update the original msni
IF (l_debug = 1) THEN
print_debug('split_lot_serial:230: update msni ', 1);
UPDATE mtl_serial_numbers_interface
SET fm_serial_number = l_new_from_ser_num
WHERE ROWID = l_msni_rec.ROWID;
select shipment_line_id
into l_shipment_line_id
from rcv_transactions_interface
where interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
print_debug('split_lot_serial:240: update msni ', 1);
UPDATE mtl_serial_numbers_interface
SET product_transaction_id = p_rti_tb(newrti).new_interface_trx_id
WHERE ROWID = l_msni_rec.ROWID;
print_debug('split_lot_serial:250: insert msni rowid ', 1);
--insert new msni row.
insert_msni_rowid (p_rowid =>l_msni_rec.ROWID,
p_product_txn_id => p_rti_tb(newrti).new_interface_trx_id,
p_new_serial_txn_temp_id => NULL,
p_new_fm_ser_num => NULL,
p_new_to_ser_num => l_new_to_ser_num,
x_return_status =>x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('split_lot_serial:255: insert msni rowid failed ', 1);
-- update the original msni
IF (l_debug = 1) THEN
print_debug('split_lot_serial:260: update msni ', 1);
UPDATE mtl_serial_numbers_interface
SET fm_serial_number = l_new_from_ser_num
WHERE ROWID = l_msni_rec.ROWID;
SELECT '1'
INTO l_dummy
FROM rcv_serials_supply
WHERE transaction_id = p_parent_txn_id
AND ROWNUM = 1;
SELECT '1'
INTO l_dummy
FROM rcv_lots_supply
WHERE transaction_id = p_parent_txn_id
AND ROWNUM = 1;
SELECT '1'
INTO l_dummy
FROM rcv_serials_supply rss
, rcv_shipment_lines rsl
WHERE rss.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = p_shipment_header_id
AND rsl.item_id = p_item_id
AND rss.supply_type_code = 'SHIPMENT'
AND ROWNUM = 1;
FUNCTION update_group_mark_id(p_item_id IN NUMBER,
p_serial_number IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
UPDATE mtl_serial_numbers
SET group_mark_id = -7937
WHERE inventory_item_id = p_item_id
AND serial_number = p_serial_number;
SELECT item_id,to_organization_id,uom_code,unit_of_measure
INTO l_item_id,l_org_id,l_uom_code,l_unit_of_measure
FROM RCV_TRANSACTIONS_INTERFACE
WHERE interface_transaction_id=p_rti_id;
SELECT uom_code
INTO l_uom_code
FROM MTL_UNITS_OF_MEASURE_VL
WHERE unit_of_measure = l_unit_of_measure;
INSERT INTO mtl_transaction_lots_temp (TRANSACTION_TEMP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
ERROR_CODE,
SERIAL_TRANSACTION_TEMP_ID,
DESCRIPTION,
VENDOR_NAME,
SUPPLIER_LOT_NUMBER,
ORIGINATION_DATE,
DATE_CODE,
GRADE_CODE,
CHANGE_DATE,
MATURITY_DATE,
STATUS_ID,
RETEST_DATE,
AGE,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
WIDTH,
WIDTH_UOM,
CURL_WRINKLE_FOLD,
LOT_ATTRIBUTE_CATEGORY,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
VENDOR_ID,
TERRITORY_CODE,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID,
/*INVCONV , Remove sublot_num and add parent parent_lot_number and other attributes, Punit Kumar */
/** OPM change Bug# 3061052**/
--SUBLOT_NUM ,
PARENT_LOT_NUMBER ,
ORIGINATION_TYPE ,
EXPIRATION_ACTION_DATE ,
EXPIRATION_ACTION_CODE ,
HOLD_DATE ,
REASON_ID ,
/* end INVCONV */
SECONDARY_QUANTITY ,
REASON_CODE )
SELECT transaction_interface_id ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
TRANSACTION_QUANTITY,
--get_primary_qty(l_org_id,l_item_id,l_uom_code,transaction_quantity), --Bug 9458159
get_primary_qty(l_org_id,l_item_id,l_uom_code,transaction_quantity,Ltrim(Rtrim(lot_number))), --Bug 13981381, pass lot_number in order to consider lot specific UOM conversion
Ltrim(Rtrim(lot_number)),
LOT_EXPIRATION_DATE,
ERROR_CODE,
SERIAL_TRANSACTION_TEMP_ID,
DESCRIPTION,
VENDOR_NAME,
SUPPLIER_LOT_NUMBER,
ORIGINATION_DATE,
DATE_CODE,
GRADE_CODE,
CHANGE_DATE,
MATURITY_DATE,
STATUS_ID,
RETEST_DATE,
AGE,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
WIDTH,
WIDTH_UOM,
CURL_WRINKLE_FOLD,
LOT_ATTRIBUTE_CATEGORY,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
VENDOR_ID,
TERRITORY_CODE,
PRODUCT_CODE,
product_transaction_id,
/*INVCONV , Remove sublot_num and add parent parent_lot_number and other attributes,
ORIGINATION_TYPE defaulted to 'PURCHASING'. Punit Kumar */
/** OPM change Bug# 3061052**/
---sublot_num,
PARENT_LOT_NUMBER ,
3 , /* defaulting the value of ORIGINATION_TYPE to 'PURCHASING'*/
EXPIRATION_ACTION_DATE ,
EXPIRATION_ACTION_CODE ,
HOLD_DATE ,
REASON_ID ,
/* end INVCONV */
secondary_transaction_quantity,
reason_code
FROM mtl_transaction_lots_interface
WHERE product_code = 'RCV'
AND product_transaction_id = p_rti_id;
print_debug('move_lot_serial_info - Number of MTLT Inserted =: '||SQL%ROWCOUNT ||
' with product transaction_id = ' || p_rti_id, 1);
print_debug('INVCONV, removed sublot_num, Added some more parameters in INSERT INTO mtl_transaction_lots_temp (move_lot_serial_info)', 1);
/* Bug 12329840 Modified the code so that we are not inserting into MSNT
if the item's serial control code is 1
*/
/* BEGIN
SELECT msi.serial_number_control_code
INTO x_serial_number_control_code
FROM mtl_system_items msi, rcv_transactions_interface rti
WHERE msi.inventory_item_id = rti.item_id
AND msi.organization_id = rti.to_organization_id
AND rti.interface_transaction_id = p_rti_id;
INSERT INTO mtl_serial_numbers_temp (TRANSACTION_TEMP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
VENDOR_SERIAL_NUMBER,
VENDOR_LOT_NUMBER,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
ERROR_CODE,
PARENT_SERIAL_NUMBER,
SERIAL_ATTRIBUTE_CATEGORY,
ORIGINATION_DATE,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
STATUS_ID,
TERRITORY_CODE,
TIME_SINCE_NEW,
CYCLES_SINCE_NEW,
TIME_SINCE_OVERHAUL,
CYCLES_SINCE_OVERHAUL,
TIME_SINCE_REPAIR,
CYCLES_SINCE_REPAIR,
TIME_SINCE_VISIT,
CYCLES_SINCE_VISIT,
TIME_SINCE_MARK,
CYCLES_SINCE_MARK,
NUMBER_OF_REPAIRS,
PRODUCT_CODE,
product_transaction_id)
SELECT transaction_interface_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
VENDOR_SERIAL_NUMBER,
VENDOR_LOT_NUMBER,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
ERROR_CODE,
PARENT_SERIAL_NUMBER,
SERIAL_ATTRIBUTE_CATEGORY,
ORIGINATION_DATE,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
STATUS_ID,
TERRITORY_CODE,
TIME_SINCE_NEW,
CYCLES_SINCE_NEW,
TIME_SINCE_OVERHAUL,
CYCLES_SINCE_OVERHAUL,
TIME_SINCE_REPAIR,
CYCLES_SINCE_REPAIR,
TIME_SINCE_VISIT,
CYCLES_SINCE_VISIT,
TIME_SINCE_MARK,
CYCLES_SINCE_MARK,
NUMBER_OF_REPAIRS,
PRODUCT_CODE,
product_transaction_id
FROM mtl_serial_numbers_interface
WHERE product_code = 'RCV'
AND product_transaction_id = p_rti_id;
print_debug('move_lot_serial_info - Number of MSNT Inserted =: '||SQL%ROWCOUNT ||
' with product transaction_id =' || p_rti_id, 1);
UPDATE mtl_transaction_lots_temp mtlt
set mtlt.serial_transaction_temp_id =
(Select msni.transaction_interface_id
from mtl_serial_numbers_interface msni where
msni.product_code = 'RCV'
AND msni.product_transaction_id = p_rti_id
and rownum=1)
where mtlt.product_code = 'RCV'
AND mtlt.product_transaction_id = p_rti_id
and mtlt.serial_transaction_temp_id is null;
DELETE FROM mtl_transaction_lots_interface
WHERE product_code = 'RCV'
AND product_transaction_id = p_rti_id;
DELETE FROM mtl_serial_numbers_interface
WHERE product_code = 'RCV'
AND product_transaction_id = p_rti_id;
SELECT Ltrim(Rtrim(lot_number)) lot_number
, SERIAL_TRANSACTION_TEMP_ID
, TRANSACTION_QUANTITY
, primary_quantity
, lot_expiration_date
, lot_attribute_category
, C_ATTRIBUTE1
, C_ATTRIBUTE2
, C_ATTRIBUTE3
, C_ATTRIBUTE4
, C_ATTRIBUTE5
, C_ATTRIBUTE6
, C_ATTRIBUTE7
, C_ATTRIBUTE8
, C_ATTRIBUTE9
, C_ATTRIBUTE10
, C_ATTRIBUTE11
, C_ATTRIBUTE12
, C_ATTRIBUTE13
, C_ATTRIBUTE14
, C_ATTRIBUTE15
, C_ATTRIBUTE16
, C_ATTRIBUTE17
, C_ATTRIBUTE18
, C_ATTRIBUTE19
, C_ATTRIBUTE20
, D_ATTRIBUTE1
, D_ATTRIBUTE2
, D_ATTRIBUTE3
, D_ATTRIBUTE4
, D_ATTRIBUTE5
, D_ATTRIBUTE6
, D_ATTRIBUTE7
, D_ATTRIBUTE8
, D_ATTRIBUTE9
, D_ATTRIBUTE10
, N_ATTRIBUTE1
, N_ATTRIBUTE2
, N_ATTRIBUTE3
, N_ATTRIBUTE4
, N_ATTRIBUTE5
, N_ATTRIBUTE6
, N_ATTRIBUTE7
, N_ATTRIBUTE8
, N_ATTRIBUTE9
, n_attribute10
, grade_code
, origination_date
, date_code
, Decode(l_lot_status_enabled,'Y',status_id,1) status_id --Bug 4066234
, change_date
, age
, retest_date
, maturity_date
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, Length
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, territory_code
, supplier_lot_number
, vendor_name
/** INVCONV,remove sublot_number, add Parent lot number and SECONDARY_UNIT_OF_MEASURE etc,
Punit Kumar**/
---, Ltrim(Rtrim(sublot_num)) sublot_num
,Ltrim(Rtrim(PARENT_LOT_NUMBER)) PARENT_LOT_NUMBER
----,SECONDARY_UNIT_OF_MEASURE
,REASON_ID
,LAST_UPDATED_BY
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,DESCRIPTION
,CURL_WRINKLE_FOLD
,VENDOR_ID
,ORIGINATION_TYPE
,EXPIRATION_ACTION_DATE
,EXPIRATION_ACTION_CODE
,HOLD_DATE
----,TRANSACTION_TEMP_ID
/* end INVCONV */
, secondary_quantity
, reason_code
, rowid
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM MTL_TRANSACTION_LOTS_TEMP
WHERE PRODUCT_CODE = 'RCV'
AND PRODUCT_TRANSACTION_ID = L_RTI_ID;
SELECT FM_SERIAL_NUMBER
, to_serial_number
, vendor_serial_number
, transaction_temp_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ROWID
FROM MTL_SERIAL_NUMBERS_TEMP
WHERE PRODUCT_CODE = 'RCV'
AND PRODUCT_TRANSACTION_ID = L_RTI_ID;
SELECT FM_SERIAL_NUMBER
, to_serial_number
, vendor_serial_number
, transaction_temp_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ROWID
FROM MTL_SERIAL_NUMBERS_TEMP
WHERE PRODUCT_CODE = 'RCV'
AND transaction_temp_id = L_SERIAL_TXN_ID;
SELECT TRANSACTION_TYPE
, AUTO_TRANSACT_CODE
, SOURCE_DOCUMENT_CODE
, item_id
, item_revision
, LPN_ID
, TRANSFER_LPN_ID
, FROM_ORGANIZATION_ID
, TO_ORGANIZATION_ID
, SHIPMENT_LINE_ID
, PARENT_TRANSACTION_ID
, SUBINVENTORY
, locator_id
, routing_header_id
, Nvl(primary_quantity, quantity)
, quantity -- Bug# 4225766
, shipment_header_id
, destination_type_code
, Nvl(mobile_txn, 'N')
, inv_transaction_id
, processing_mode_code
/* INVCONV , added following , Punit Kumar */
,OE_ORDER_HEADER_ID
,OE_ORDER_LINE_ID
,SECONDARY_QUANTITY
,PRIMARY_UNIT_OF_MEASURE
,UOM_CODE
,SECONDARY_UOM_CODE
,SOURCE_DOC_UNIT_OF_MEASURE
,UNIT_OF_MEASURE
,SECONDARY_UNIT_OF_MEASURE
/* end INVCONV */
,lpn_group_id
,from_subinventory -- Bug 14269152
,from_locator_id -- Bug 14269152
INTO L_TRANSACTION_TYPE
, L_AUTO_TRANSACT_CODE
, L_SOURCE_DOCUMENT_CODE
, l_item_id
, l_item_revision
, L_LPN_ID
, L_TRANSFER_LPN_ID
, L_FROM_ORG_ID
, L_ORG_ID
, L_SHIPMENT_LINE_ID
, L_PARENT_TRANSACTION_ID
, L_SUB_CODE
, l_loc_id
, l_routing_header_id
, l_rti_primary_qty
, l_rti_trans_qty -- Bug# 4225766
, l_shipment_header_id
, l_destination_type_code
, l_mobile_txn
, l_inv_txn_id
, l_proc_mode_code
/* INVCONV , added following , Punit Kumar */
, l_OE_ORDER_HEADER_ID
, l_OE_ORDER_LINE_ID
, l_rti_SECONDARY_QUANTITY
, l_rti_PRIMARY_UNIT_OF_MEASURE
, l_rti_UOM_CODE
, l_rti_SECONDARY_UOM_CODE
, l_sourcedoc_unit_of_measure
, l_rti_UNIT_OF_MEASURE
, l_rti_SEC_UNIT_OF_MEASURE
/* end INVCONV */
, l_lpn_grp_id
, l_from_subinventory -- Bug 14269152
, l_from_locator_id -- Bug 14269152
FROM RCV_TRANSACTIONS_INTERFACE
WHERE INTERFACE_TRANSACTION_ID = L_RTI_ID;
select decode(ASN_TYPE,'ASN','Y', 'ASBN','Y','N')
into l_asn_line_flag
from rcv_shipment_headers
WHERE shipment_header_id = l_shipment_header_id;
SELECT intransit_type
INTO l_intransit_type
FROM mtl_interorg_parameters
WHERE from_organization_id = l_from_org_id
AND to_organization_id = l_org_id;
SELECT LOT_CONTROL_CODE
, SERIAL_NUMBER_CONTROL_CODE
, lot_status_enabled --Bug 4066234
INTO L_LOT_CONTROL_CODE
, L_SERIAL_NUMBER_CONTROL_CODE
, l_lot_status_enabled --Bug 4066234
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = L_ITEM_ID
AND ORGANIZATION_ID = L_ORG_ID;
select iim.lot_ctl,iim.sublot_ctl,iim.item_id,iim.item_no
into l_opm_lot_ctl,l_opm_sublot_ctl,l_opm_item_id,l_item_no
from mtl_system_items msi , ic_item_mst iim
where msi.inventory_item_id = l_item_id
and msi.organization_id = l_org_id
and msi.segment1 = iim.item_no ;
SELECT '1'
INTO l_dummy
FROM dual
WHERE exists (SELECT 1
FROM mtl_transaction_lots_temp
WHERE product_code = 'RCV'
AND product_transaction_id = l_rti_id);
select transaction_type
into l_parent_transaction_type
from rcv_transactions
where transaction_id=l_parent_transaction_id;
SELECT '1'
INTO l_dummy
FROM dual
WHERE exists (SELECT 1
FROM mtl_serial_numbers_temp
WHERE product_code = 'RCV'
AND product_transaction_id = l_rti_id);
l_mln_rec.LAST_UPDATE_DATE := SYSDATE ;
l_mln_rec.LAST_UPDATED_BY := L_MTLT_REC.LAST_UPDATED_BY ;
l_mln_rec.LAST_UPDATE_LOGIN := L_MTLT_REC.LAST_UPDATE_LOGIN ;
l_mln_rec.PROGRAM_UPDATE_DATE := L_MTLT_REC.PROGRAM_UPDATE_DATE ;
SELECT uom_code
INTO l_rti_uom_code
FROM MTL_UNITS_OF_MEASURE_VL
WHERE unit_of_measure = l_rti_UNIT_OF_MEASURE;
SELECT 1,parent_lot_number
INTO L_LOT_EXISTS ,l_parent_lot_number
FROM MTL_LOT_NUMBERS
WHERE ORGANIZATION_ID = L_ORG_ID
AND INVENTORY_ITEM_ID = L_ITEM_ID
AND LOT_NUMBER = Ltrim(Rtrim(L_MTLT_REC.lot_number));
SELECT 1, LOT_ID
INTO L_LOT_EXISTS , l_opm_lot_id
FROM IC_LOTS_MST
WHERE ITEM_ID = l_opm_item_id
AND LOT_NO = Ltrim(Rtrim(L_MTLT_REC.lot_number))
AND SUBLOT_NO = Ltrim(Rtrim(L_MTLT_REC.sublot_num)) ;
SELECT 1 , LOT_ID
INTO L_LOT_EXISTS , l_opm_lot_id
FROM IC_LOTS_MST
WHERE ITEM_ID = l_opm_item_id
AND LOT_NO = Ltrim(Rtrim(L_MTLT_REC.lot_number))
AND SUBLOT_NO IS NULL ;
print_debug('INVCONV, Removing OPM specific fork and selecting parent lot number :'||l_progress||':'||l_parent_lot_number,1);
SELECT nvl(SUM(rls.primary_quantity),0)--Bug:5489462
INTO L_RLS_PRIMARY_QUANTITY
FROM rcv_lots_supply rls
, rcv_shipment_lines rsl
WHERE rsl.SHIPMENT_LINE_ID = rls.SHIPMENT_LINE_ID
AND rsl.shipment_header_id = l_shipment_header_id
--Bug 12348470 Reverted the fix 94466813
--AND rls.SHIPMENT_LINE_ID = L_SHIPMENT_LINE_ID --9466813
AND rsl.item_id = l_item_id
AND rls.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND rls.LOT_NUM = Ltrim(Rtrim(L_MTLT_REC.lot_number));
SELECT '1'
INTO l_dummy
FROM dual
WHERE EXISTS (SELECT 'comingling exists'
from mtl_onhand_quantities_detail moqd,
rcv_transactions_interface rti
WHERE moqd.organization_id = rti.to_organization_id
AND moqd.inventory_item_id = rti.item_id
and moqd.subinventory_code = rti.subinventory
and nvl(moqd.locator_id, -9999) = nvl(rti.locator_id, -9999)
and nvl(moqd.lot_number, '@@@@') = nvl(L_MTLT_REC.lot_number, '@@@@')
and nvl(moqd.lpn_id, -9999) = nvl(rti.lpn_id, -9999)
and nvl(moqd.status_id, -9999) <> nvl(L_MTLT_REC.status_id, -9999)
and rti.interface_transaction_id = L_RTI_ID
);
SELECT Sum(primary_quantity) -- Bug 14117094
INTO l_rls_primary_quantity
FROM rcv_lots_supply
WHERE transaction_id = l_parent_transaction_id
AND lot_num = Ltrim(Rtrim(l_mtlt_rec.lot_number)) ;
SELECT nvl(SUM(rls.primary_quantity),0) --Bug:5489462
INTO L_RLS_PRIMARY_QUANTITY
FROM rcv_lots_supply rls
, rcv_shipment_lines rsl
WHERE rls.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
AND rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rls.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND rls.LOT_NUM = Ltrim(Rtrim(L_MTLT_REC.lot_number));
SELECT CURRENT_ORGANIZATION_ID
, current_status
, lot_number
, Decode(lpn_id,0,NULL,lpn_id)
, inspection_status
, group_mark_id
INTO L_CURR_ORG_ID
, l_curr_status
, l_curr_lot_num
, l_curr_lpn_id
, l_inspection_status
, l_group_mark_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND inventory_item_id = l_item_id;
SELECT '1'
INTO l_dummy
FROM rcv_serials_supply rss
, rcv_shipment_lines rsl
WHERE rss.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rss.supply_type_code = 'SHIPMENT'
AND rss.serial_num = l_serial_number;
SELECT '1'
INTO L_DUMMY
FROM rcv_serials_supply rss
, rcv_shipment_lines rsl
WHERE rss.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
AND rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rss.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND rss.serial_num = l_serial_number;
SELECT from_organization_id INTO l_from_org_id
FROM rcv_shipment_lines
WHERE shipment_line_id = L_SHIPMENT_LINE_ID;
--validate and update the attributes.
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => l_mtlt_rec.lot_number
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => l_mtlt_rec.supplier_lot_number
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => 27
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
--validate and update the attributes.
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO l_dummy
FROM dual
WHERE exists (SELECT '1'
FROM mtl_serial_numbers
WHERE inventory_item_id = l_item_id
AND current_organization_id = l_org_id
AND current_status IN (1, 4) --Do we need 4 here
AND serial_number = l_serial_number
AND LAST_TXN_SOURCE_TYPE_ID = 12);
--validate and update the attributes.
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => l_mtlt_rec.lot_number
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => l_mtlt_rec.supplier_lot_number
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => 27
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
--validate and update the attributes.
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = L_PARENT_TRANSACTION_ID
AND SERIAL_NUM = L_SERIAL_NUMBER;
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT CURRENT_ORGANIZATION_ID
, current_status
, lot_number
, Decode(lpn_id,0,NULL,lpn_id)
, inspection_status
, group_mark_id
, last_transaction_id --bug 5168883
INTO L_CURR_ORG_ID
, l_curr_status
, l_curr_lot_num
, l_curr_lpn_id
, l_inspection_status
, l_group_mark_id
, l_last_transaction_id --bug 5168883
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND inventory_item_id = l_item_id;
SELECT '1'
INTO l_dummy
FROM rcv_serials_supply rss
, rcv_shipment_lines rsl
WHERE rss.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
AND rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rss.supply_type_code = 'SHIPMENT'
AND rss.serial_num = l_serial_number;
--update the serial row
IF (l_debug = 1) THEN
print_debug('VALIDATE_LOT_SERIAL_INFO: UPDATING LOT_NUMBER to null as destination org is not lot controlled', 1);
update mtl_serial_numbers msn
set lot_number = null
where msn.serial_number = l_serial_number
AND inventory_item_id = l_item_id;
SELECT '1'
INTO L_DUMMY
FROM rcv_serials_supply rss
, rcv_shipment_lines rsl
WHERE rss.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
AND rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rss.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND rss.serial_num = l_serial_number;
SELECT from_organization_id
INTO l_from_org_id
FROM rcv_shipment_lines
WHERE shipment_line_id = L_SHIPMENT_LINE_ID;
select transaction_type_id
into l_last_transaction_type_id
from mtl_material_transactions
where transaction_id = l_last_transaction_id;
--validate and update the attributes.
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
l_progress := 'WMSINB-23558';
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => NULL
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => NULL
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => 27
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
--validate and update the attributes.
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
l_progress := 'WMSINB-23622';
SELECT '1'
INTO l_dummy
FROM dual
WHERE exists (SELECT '1'
FROM mtl_serial_numbers
WHERE inventory_item_id = l_item_id
AND current_organization_id = l_org_id
AND current_status IN (1, 4) --Do we need 4 here
AND serial_number = l_serial_number
AND LAST_TXN_SOURCE_TYPE_ID = 12);
--validate and update the attributes.
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => NULL
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => NULL
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => 27
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
--validate and update the attributes.
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = L_PARENT_TRANSACTION_ID
AND SERIAL_NUM = L_SERIAL_NUMBER;
--UPDATE GROUP_MARK_ID TO -7937
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
l_mln_rec.LAST_UPDATE_DATE := SYSDATE ;
l_mln_rec.LAST_UPDATED_BY := L_MTLT_REC.LAST_UPDATED_BY ;
l_mln_rec.LAST_UPDATE_LOGIN := L_MTLT_REC.LAST_UPDATE_LOGIN ;
l_mln_rec.PROGRAM_UPDATE_DATE := L_MTLT_REC.PROGRAM_UPDATE_DATE ;
SELECT 1,parent_lot_number
INTO L_LOT_EXISTS ,l_parent_lot_number
FROM MTL_LOT_NUMBERS
WHERE ORGANIZATION_ID = L_ORG_ID
AND INVENTORY_ITEM_ID = L_ITEM_ID
AND LOT_NUMBER = Ltrim(Rtrim(L_MTLT_REC.lot_number));
SELECT 1, LOT_ID
INTO L_LOT_EXISTS , l_opm_lot_id
FROM IC_LOTS_MST
WHERE ITEM_ID = l_opm_item_id
AND LOT_NO = Ltrim(Rtrim(L_MTLT_REC.lot_number))
AND SUBLOT_NO = Ltrim(Rtrim(L_MTLT_REC.sublot_num)) ;
SELECT 1, LOT_ID
INTO L_LOT_EXISTS , l_opm_lot_id
FROM IC_LOTS_MST
WHERE ITEM_ID = l_opm_item_id
AND LOT_NO = Ltrim(Rtrim(L_MTLT_REC.lot_number))
AND SUBLOT_NO IS NULL ;
SELECT CURRENT_ORGANIZATION_ID
, current_status
, lot_number
, Decode(lpn_id,0,NULL,lpn_id)
, inspection_status
, group_mark_id
INTO L_CURR_ORG_ID
, l_curr_status
, l_curr_lot_num
, l_curr_lpn_id
, l_inspection_status
, l_group_mark_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND inventory_item_id = l_item_id;
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => l_mtlt_rec.lot_number
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => l_mtlt_rec.supplier_lot_number
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => 27
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT wms_epc_s2.NEXTVAL
INTO g_epc_group_id
FROM dual;
SELECT CURRENT_ORGANIZATION_ID
, current_status
, lot_number
, Decode(lpn_id,0,NULL,lpn_id)
, inspection_status
, group_mark_id
INTO L_CURR_ORG_ID
, l_curr_status
, l_curr_lot_num
, l_curr_lpn_id
, l_inspection_status
, l_group_mark_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND inventory_item_id = l_item_id;
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => NULL
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => NULL
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => 27
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT wms_epc_s2.NEXTVAL
INTO g_epc_group_id
FROM dual;
SELECT transaction_type
, parent_transaction_id
INTO l_parent_txn_type
, l_grand_parent_txn_id
FROM rcv_transactions
WHERE transaction_id = l_parent_transaction_id;
SELECT transaction_type
, parent_transaction_id
INTO l_grand_parent_txn_type
, l_great_grand_parent_txn_id
FROM rcv_transactions
WHERE transaction_id = l_grand_parent_txn_id;
l_mln_rec.LAST_UPDATE_DATE := SYSDATE ;
l_mln_rec.LAST_UPDATED_BY := L_MTLT_REC.LAST_UPDATED_BY ;
l_mln_rec.LAST_UPDATE_LOGIN := L_MTLT_REC.LAST_UPDATE_LOGIN ;
l_mln_rec.PROGRAM_UPDATE_DATE := L_MTLT_REC.PROGRAM_UPDATE_DATE ;
SELECT 1,parent_lot_number
INTO L_LOT_EXISTS ,l_parent_lot_number
FROM MTL_LOT_NUMBERS
WHERE ORGANIZATION_ID = L_ORG_ID
AND INVENTORY_ITEM_ID = L_ITEM_ID
AND LOT_NUMBER = Ltrim(Rtrim(L_MTLT_REC.lot_number));
SELECT 1, LOT_ID
INTO L_LOT_EXISTS , l_opm_lot_id
FROM IC_LOTS_MST
WHERE ITEM_ID = l_opm_item_id
AND LOT_NO = Ltrim(Rtrim(L_MTLT_REC.lot_number))
AND SUBLOT_NO = Ltrim(Rtrim(L_MTLT_REC.sublot_num)) ;
SELECT 1, LOT_ID
INTO L_LOT_EXISTS , l_opm_lot_id
FROM IC_LOTS_MST
WHERE ITEM_ID = l_opm_item_id
AND LOT_NO = Ltrim(Rtrim(L_MTLT_REC.lot_number))
AND SUBLOT_NO IS NULL ;
SELECT primary_quantity
INTO l_rls_primary_quantity
FROM rcv_lots_supply
WHERE transaction_id = l_great_grand_parent_txn_id
AND lot_num = Ltrim(Rtrim(l_mtlt_rec.lot_number)) ;
SELECT primary_quantity
INTO l_rls_primary_quantity
FROM rcv_lots_supply
WHERE transaction_id = l_parent_transaction_id
AND lot_num = Ltrim(Rtrim(l_mtlt_rec.lot_number));
SELECT CURRENT_ORGANIZATION_ID
, current_status
, lot_number
, Decode(lpn_id,0,NULL,lpn_id)
, inspection_status
, group_mark_id
INTO L_CURR_ORG_ID
, l_curr_status
, l_curr_lot_num
, l_curr_lpn_id
, l_inspection_status
, l_group_mark_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND inventory_item_id = l_item_id;
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => l_mtlt_rec.lot_number
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => l_mtlt_rec.supplier_lot_number
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => l_transaction_action_id
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = l_great_grand_parent_txn_id
AND SERIAL_NUM = L_SERIAL_NUMBER;
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => l_mtlt_rec.lot_number
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => l_mtlt_rec.supplier_lot_number
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => l_transaction_action_id
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = l_parent_transaction_id
AND SERIAL_NUM = L_SERIAL_NUMBER;
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT CURRENT_ORGANIZATION_ID
, current_status
, lot_number
, Decode(lpn_id,0,NULL,lpn_id)
, inspection_status
, group_mark_id
INTO L_CURR_ORG_ID
, l_curr_status
, l_curr_lot_num
, l_curr_lpn_id
, l_inspection_status
, l_group_mark_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND inventory_item_id = l_item_id;
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => NULL
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => NULL
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => l_transaction_action_id
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = l_great_grand_parent_txn_id
AND SERIAL_NUM = L_SERIAL_NUMBER;
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => NULL
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => NULL
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => l_transaction_action_id
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = l_parent_transaction_id
AND SERIAL_NUM = L_SERIAL_NUMBER;
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
l_mln_rec.LAST_UPDATE_DATE := SYSDATE ;
l_mln_rec.LAST_UPDATED_BY := L_MTLT_REC.LAST_UPDATED_BY ;
l_mln_rec.LAST_UPDATE_LOGIN := L_MTLT_REC.LAST_UPDATE_LOGIN ;
l_mln_rec.PROGRAM_UPDATE_DATE := L_MTLT_REC.PROGRAM_UPDATE_DATE ;
SELECT 1,parent_lot_number
INTO L_LOT_EXISTS ,l_parent_lot_number
FROM MTL_LOT_NUMBERS
WHERE ORGANIZATION_ID = L_ORG_ID
AND INVENTORY_ITEM_ID = L_ITEM_ID
AND LOT_NUMBER = Ltrim(Rtrim(L_MTLT_REC.lot_number));
SELECT 1, LOT_ID
INTO L_LOT_EXISTS , L_OPM_LOT_ID
FROM IC_LOTS_MST
WHERE ITEM_ID = l_opm_item_id
AND LOT_NO = Ltrim(Rtrim(L_MTLT_REC.lot_number))
AND SUBLOT_NO = Ltrim(Rtrim(L_MTLT_REC.sublot_num)) ;
SELECT 1 , lot_id
INTO L_LOT_EXISTS , L_OPM_LOT_ID
FROM IC_LOTS_MST
WHERE ITEM_ID = l_opm_item_id
AND LOT_NO = Ltrim(Rtrim(L_MTLT_REC.lot_number))
AND SUBLOT_NO IS NULL ;
SELECT primary_quantity
INTO l_rls_primary_quantity
FROM rcv_lots_supply
WHERE transaction_id = l_grand_parent_txn_id
AND lot_num = Ltrim(Rtrim(l_mtlt_rec.lot_number)) ;
SELECT primary_quantity
INTO l_rls_primary_quantity
FROM rcv_lots_supply
WHERE transaction_id = l_grand_parent_txn_id
AND lot_num = Ltrim(Rtrim(l_mtlt_rec.lot_number));
SELECT nvl(SUM(rls.primary_quantity),0) --Bug:5489462
INTO L_RLS_PRIMARY_QUANTITY
FROM rcv_lots_supply rls
, rcv_shipment_lines rsl
WHERE rsl.SHIPMENT_LINE_ID = rls.SHIPMENT_LINE_ID
AND rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rls.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND rls.LOT_NUM = Ltrim(Rtrim(L_MTLT_REC.lot_number));
SELECT CURRENT_ORGANIZATION_ID
, current_status
, lot_number
, Decode(lpn_id,0,NULL,lpn_id)
, inspection_status
, group_mark_id
INTO L_CURR_ORG_ID
, l_curr_status
, l_curr_lot_num
, l_curr_lpn_id
, l_inspection_status
, l_group_mark_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND inventory_item_id = l_item_id;
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = l_grand_parent_txn_id
AND SERIAL_NUM = L_SERIAL_NUMBER;
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = l_grand_parent_txn_id
AND SERIAL_NUM = L_SERIAL_NUMBER;
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => l_mtlt_rec.lot_number
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => l_mtlt_rec.supplier_lot_number
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => l_transaction_action_id
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM rcv_serials_supply rss
, rcv_shipment_lines rsl
WHERE rss.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
AND rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rss.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND rss.serial_num = l_serial_number;
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => l_mtlt_rec.lot_number
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => l_mtlt_rec.supplier_lot_number
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => l_transaction_action_id
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT CURRENT_ORGANIZATION_ID
, current_status
, lot_number
, Decode(lpn_id,0,NULL,lpn_id)
, inspection_status
, group_mark_id
INTO L_CURR_ORG_ID
, l_curr_status
, l_curr_lot_num
, l_curr_lpn_id
, l_inspection_status
, l_group_mark_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND inventory_item_id = l_item_id;
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = l_grand_parent_txn_id
AND SERIAL_NUM = L_SERIAL_NUMBER;
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM RCV_SERIALS_SUPPLY
WHERE TRANSACTION_ID = l_grand_parent_txn_id
AND SERIAL_NUM = L_SERIAL_NUMBER;
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => NULL
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => NULL
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => l_transaction_action_id
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT '1'
INTO L_DUMMY
FROM rcv_serials_supply rss
, rcv_shipment_lines rsl
WHERE rss.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
AND rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rss.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND rss.serial_num = l_serial_number;
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
inv_serial_number_pub.insert_range_serial(p_api_version => 1.0
, p_inventory_item_id => l_item_id
, p_organization_id => l_org_id
, p_from_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => l_item_revision
, p_lot_number => NULL
, p_current_locator_id => l_loc_id
, p_subinventory_code => l_sub_code
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => NULL
, p_vendor_serial_number => l_msnt_rec.vendor_serial_number
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => 1
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => l_transaction_action_id
, p_transaction_temp_id => l_msnt_rec.transaction_temp_id
, p_status_id => NULL
, p_inspection_status => NULL
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
IF NOT (update_group_mark_id(l_item_id,l_serial_number)) THEN
--raise error
fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
SELECT
1
FROM
WMS_DISPATCHED_TASKS WDT,
MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE
NVL(MMTT.content_lpn_id, MMTT.lpn_id) = p_lpn_id
AND MMTT.transaction_temp_id = WDT.transaction_temp_id
AND WDT.STATUS = 4 ;
SELECT
lpn_context, license_plate_number
FROM
WMS_LICENSE_PLATE_NUMBERS WLPN
WHERE
WLPN.LPN_ID = p_lpn_id ;
SELECT license_plate_number
INTO l_lpn
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = p_lpn_id ;*/
SELECT uom_code
INTO l_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_uom ;
SELECT Sum(rsl.quantity_shipped) quantity_shipped ,
Sum(rsl.quantity_received) quantity_received,
rsl.unit_of_measure
FROM rcv_shipment_lines rsl
WHERE rsl.asn_lpn_id = p_lpn_id
AND rsl.to_organization_id = p_organization_id
AND rsl.item_id = p_item_id
GROUP BY rsl.item_id , rsl.to_organization_id, rsl.asn_lpn_id, rsl.unit_of_measure;
SELECT SUM(rti.primary_quantity) primary_quantity,
SUM(rti.quantity) quantity,
rti.unit_of_measure
FROM rcv_transactions_interface rti
WHERE rti.lpn_id = p_lpn_id
AND rti.item_id = p_item_id
AND rti.to_organization_id = p_organization_id
AND(rti.transaction_status_code = 'PENDING'
AND rti.processing_status_code <> 'ERROR')
AND rti.transaction_type = 'RECEIVE'
GROUP BY rti.lpn_id , rti.to_organization_id, rti.item_id , rti.unit_of_measure;
SELECT primary_uom_code
INTO l_pr_UoM_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id ;