[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 dual
WHERE exists (SELECT '1'
FROM wms_lpn_contents wlc
, wms_license_plate_numbers wlpn
, mtl_system_items msi
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 wlpn.organization_id = p_org_id
AND msi.mtl_transactions_enabled_flag <> p_transactions_enabled_flag
AND wlpn.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 = p_lpn_id
UNION ALL
SELECT lpn_id
FROM wms_license_plate_numbers wlpn3
CONNECT BY PRIOR wlpn3.parent_lpn_id = wlpn3.lpn_id
START WITH wlpn3.parent_lpn_id = p_lpn_id));
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_number
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 product_transaction_id = l_wlpnci_rec.interface_transaction_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
-- 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 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
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
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','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) 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 curr capacity
IF (l_debug = 1) THEN
print_debug('MAINTAIN_MO_CON - Calling update_loc_curr_capacity_nauto FOR CURRENT 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 => 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);
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
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);
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)
, 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))
into l_backorder_delivery_detail_id
,l_split_qty
,l_split_sec_qty
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(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
, 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_nauto FOR CURRENT 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 => 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);
/* 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 Ltrim(Rtrim(lot_number)) lot_number,
transaction_quantity,
primary_quantity,
-- OPMConvergence
secondary_quantity
-- OPMConvergence
FROM mtl_transaction_lots_temp
WHERE product_code = 'RCV'
AND product_transaction_id = p_product_txn_id;
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
-- Bug# 7154105
,req_distribution_id
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','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_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,
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;
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 ) ;
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 );
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) ;
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;
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
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
FROM wms_lpn_interface wlpni
WHERE wlpni.license_plate_number = p_license_plate_number
AND wlpni.source_group_id = p_lpn_group_id;
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
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
-- THERE IS a PROBLEM HERE IF DIFFERENT UOM_CODES EXIST in WLC FOR THE SAME ITEM. ***********
-- NOT YET CONVERTED UOM
IF l_lpn_contents.lot_number IS NULL THEN
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
from rcv_transactions_interface rti
where rti.lpn_group_id = p_lpn_group_id
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(lot_number)) lot_number,
transaction_quantity,
primary_quantity,
serial_transaction_temp_id,
ROWID
FROM mtl_transaction_lots_interface
WHERE product_code = 'RCV'
AND product_transaction_id = l_old_rti_id;
SELECT fm_serial_number,
to_serial_number,
ROWID
FROM mtl_serial_numbers_interface
WHERE product_code = 'RCV'
AND 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;
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));
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;
-- 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;
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;
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,
PRIMARY_QUANTITY,
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);
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);
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
, 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
, 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
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
FROM RCV_TRANSACTIONS_INTERFACE
WHERE INTERFACE_TRANSACTION_ID = L_RTI_ID;
--SELECT Nvl(asn_line_flag, 'N')
-- INTO l_asn_line_flag
-- FROM rcv_shipment_lines
-- WHERE shipment_line_id = l_shipment_line_id;
select decode(ASN_TYPE,'ASN','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 '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 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
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 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 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;
--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 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;
--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 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;
--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 => 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);
--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_great_grand_parent_txn_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' );
--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 => 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);
--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' );
--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 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;
--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 => 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);
--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_great_grand_parent_txn_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' );
--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 => 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);
--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' );
--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' );
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;
--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' );
--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_grand_parent_txn_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' );
--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 => 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);
--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 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;
--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' );
--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 => 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);
--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 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;
--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' );
--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_grand_parent_txn_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' );
--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 => 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);
--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 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;
--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' );
--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 => 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);
--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' );