[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION insert_lot_serial(p_transaction_temp_id IN NUMBER
,p_organization_id IN NUMBER
,p_item_id IN NUMBER
,x_return_status OUT nocopy VARCHAR2
,x_msg_count OUT nocopy NUMBER
,x_msg_data OUT nocopy VARCHAR2)
RETURN NUMBER IS
l_group_id NUMBER;
SELECT
fm_serial_number
,to_serial_number
,transaction_temp_id
,vendor_serial_number
,vendor_lot_number
,parent_serial_number
,origination_date
,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
,serial_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
FROM
mtl_serial_numbers_temp
WHERE
transaction_temp_id = l_txn_tmp_id;
print_debug('INSERT_LOT_SERIAL: Entering...');
SELECT
lot_number
,transaction_quantity
,primary_quantity
,serial_transaction_temp_id
, lot_expiration_date
, status_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, 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
, vendor_id
, territory_code
, 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
INTO
l_lot_number
,l_transaction_quantity
,l_primary_quantity
,l_serial_txn_tmp_id
,l_lot_expiration_date
,l_lot_status_id
,l_lot_description
,l_lot_vendor_name
,l_lot_supplier_lot_number
,l_lot_origination_date
,l_lot_date_code
,l_lot_grade_code
,l_lot_change_date
,l_lot_maturity_date
,l_lot_retest_date
,l_lot_age
,l_lot_item_size
,l_lot_color
,l_lot_volume
,l_lot_volume_uom
,l_lot_place_of_origin
,l_lot_best_by_date
,l_lot_length
,l_lot_length_uom
,l_lot_recycled_content
,l_lot_thickness
,l_lot_thickness_uom
,l_lot_width
,l_lot_width_uom
,l_lot_curl_wrinkle_fold
,l_lot_vendor_id
,l_lot_territory_code
,l_lot_lot_attribute_category
,l_lot_c_attribute1
,l_lot_c_attribute2
,l_lot_c_attribute3
,l_lot_c_attribute4
,l_lot_c_attribute5
,l_lot_c_attribute6
,l_lot_c_attribute7
,l_lot_c_attribute8
,l_lot_c_attribute9
,l_lot_c_attribute10
,l_lot_c_attribute11
,l_lot_c_attribute12
,l_lot_c_attribute13
,l_lot_c_attribute14
,l_lot_c_attribute15
,l_lot_c_attribute16
,l_lot_c_attribute17
,l_lot_c_attribute18
,l_lot_c_attribute19
,l_lot_c_attribute20
,l_lot_d_attribute1
,l_lot_d_attribute2
,l_lot_d_attribute3
,l_lot_d_attribute4
,l_lot_d_attribute5
,l_lot_d_attribute6
,l_lot_d_attribute7
,l_lot_d_attribute8
,l_lot_d_attribute9
,l_lot_d_attribute10
,l_lot_n_attribute1
,l_lot_n_attribute2
,l_lot_n_attribute3
,l_lot_n_attribute4
,l_lot_n_attribute5
,l_lot_n_attribute6
,l_lot_n_attribute7
,l_lot_n_attribute8
,l_lot_n_attribute9
,l_lot_n_attribute10
,l_lot_attribute_category
,l_lot_attribute1
,l_lot_attribute2
,l_lot_attribute3
,l_lot_attribute4
,l_lot_attribute5
,l_lot_attribute6
,l_lot_attribute7
,l_lot_attribute8
,l_lot_attribute9
,l_lot_attribute10
,l_lot_attribute11
,l_lot_attribute12
,l_lot_attribute13
,l_lot_attribute14
,l_lot_attribute15
FROM
mtl_transaction_lots_temp
WHERE
transaction_temp_id = p_transaction_temp_id;
print_debug('INSERT_LOT_SERIAL: ERROR - More than 1 row of MTLT associated with MMTT');
print_debug('INSERT_LOT_SERIAL: MTLT exists. ID:'||l_lot_number);
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_intf_id
FROM dual;
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_att_exist => 'N'
,p_transaction_interface_id => l_transaction_temp_id
,p_lot_number => l_lot_number
,p_transaction_quantity => l_transaction_quantity
,p_primary_quantity => l_primary_quantity
,p_organization_id => p_organization_id
,p_inventory_item_id => p_item_id -- from tmp mmtt
,p_expiration_date => l_lot_expiration_date
,p_status_id => l_lot_status_id
,x_serial_transaction_temp_id => l_new_ser_txn_id
,p_product_transaction_id => l_intf_id
,p_product_code => 'RCV'
,p_description => l_lot_description
,p_vendor_name => l_lot_vendor_name
,p_supplier_lot_number => l_lot_supplier_lot_number
,p_origination_date => l_lot_origination_date
,p_date_code => l_lot_date_code
,p_grade_code => l_lot_grade_code
,p_change_date => l_lot_change_date
,p_maturity_date => l_lot_maturity_date
,p_retest_date => l_lot_retest_date
,p_age => l_lot_age
,p_item_size => l_lot_item_size
,p_color => l_lot_color
,p_volume => l_lot_volume
,p_volume_uom => l_lot_volume_uom
,p_place_of_origin => l_lot_place_of_origin
,p_best_by_date => l_lot_best_by_date
,p_length => l_lot_length
,p_length_uom => l_lot_length_uom
,p_recycled_content => l_lot_recycled_content
,p_thickness => l_lot_thickness
,p_thickness_uom => l_lot_thickness_uom
,p_width => l_lot_width
,p_width_uom => l_lot_width_uom
,p_curl_wrinkle_fold => l_lot_curl_wrinkle_fold
,p_vendor_id => l_lot_vendor_id
,p_territory_code => l_lot_territory_code
,p_lot_attribute_category => l_lot_lot_attribute_category
,p_c_attribute1 => l_lot_c_attribute1
,p_c_attribute2 => l_lot_c_attribute2
,p_c_attribute3 => l_lot_c_attribute3
,p_c_attribute4 => l_lot_c_attribute4
,p_c_attribute5 => l_lot_c_attribute5
,p_c_attribute6 => l_lot_c_attribute6
,p_c_attribute7 => l_lot_c_attribute7
,p_c_attribute8 => l_lot_c_attribute8
,p_c_attribute9 => l_lot_c_attribute9
,p_c_attribute10 => l_lot_c_attribute10
,p_c_attribute11 => l_lot_c_attribute11
,p_c_attribute12 => l_lot_c_attribute12
,p_c_attribute13 => l_lot_c_attribute13
,p_c_attribute14 => l_lot_c_attribute14
,p_c_attribute15 => l_lot_c_attribute15
,p_c_attribute16 => l_lot_c_attribute16
,p_c_attribute17 => l_lot_c_attribute17
,p_c_attribute18 => l_lot_c_attribute18
,p_c_attribute19 => l_lot_c_attribute19
,p_c_attribute20 => l_lot_c_attribute20
,p_d_attribute1 => l_lot_d_attribute1
,p_d_attribute2 => l_lot_d_attribute2
,p_d_attribute3 => l_lot_d_attribute3
,p_d_attribute4 => l_lot_d_attribute4
,p_d_attribute5 => l_lot_d_attribute5
,p_d_attribute6 => l_lot_d_attribute6
,p_d_attribute7 => l_lot_d_attribute7
,p_d_attribute8 => l_lot_d_attribute8
,p_d_attribute9 => l_lot_d_attribute9
,p_d_attribute10 => l_lot_d_attribute10
,p_n_attribute1 => l_lot_n_attribute1
,p_n_attribute2 => l_lot_n_attribute2
,p_n_attribute3 => l_lot_n_attribute3
,p_n_attribute4 => l_lot_n_attribute4
,p_n_attribute5 => l_lot_n_attribute5
,p_n_attribute6 => l_lot_n_attribute6
,p_n_attribute7 => l_lot_n_attribute7
,p_n_attribute8 => l_lot_n_attribute8
,p_n_attribute9 => l_lot_n_attribute9
,p_n_attribute10 => l_lot_n_attribute10
,p_attribute_category => l_lot_attribute_category
,p_attribute1 => l_lot_attribute1
,p_attribute2 => l_lot_attribute2
,p_attribute3 => l_lot_attribute3
,p_attribute4 => l_lot_attribute4
,p_attribute5 => l_lot_attribute5
,p_attribute6 => l_lot_attribute6
,p_attribute7 => l_lot_attribute7
,p_attribute8 => l_lot_attribute8
,p_attribute9 => l_lot_attribute9
,p_attribute10 => l_lot_attribute10
,p_attribute11 => l_lot_attribute11
,p_attribute12 => l_lot_attribute12
,p_attribute13 => l_lot_attribute13
,p_attribute14 => l_lot_attribute14
,p_attribute15 => l_lot_attribute15
);
print_debug('INSERT_LOT_SERIAL: ERROR - insert_mtli Fail');
FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
print_debug('INSERT_LOT_SERIAL: MSNI Exists');
inv_rcv_integration_apis.insert_msni
(p_api_version => 1.0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_att_exist => 'N'
,p_transaction_interface_id => l_new_ser_txn_id
,p_fm_serial_number => l_msnt_rec.fm_serial_number
,p_to_serial_number => l_msnt_rec.to_serial_number
,p_organization_id => p_organization_id
,p_inventory_item_id => p_item_id -- from tmp mmtt
,p_status_id => 0 --l_msnt_rec.status_id
,p_product_transaction_id => l_intf_id
,p_product_code => 'RCV'
,p_vendor_serial_number => l_msnt_rec.vendor_serial_number
,p_vendor_lot_number => l_msnt_rec.vendor_lot_number
,p_parent_serial_number => l_msnt_rec.parent_serial_number
,p_origination_date => l_msnt_rec.origination_date
,p_territory_code => l_msnt_rec.territory_code
,p_time_since_new => l_msnt_rec.time_since_new
,p_cycles_since_new => l_msnt_rec.cycles_since_new
,p_time_since_overhaul => l_msnt_rec.time_since_overhaul
,p_cycles_since_overhaul => l_msnt_rec.cycles_since_overhaul
,p_time_since_repair => l_msnt_rec.time_since_repair
,p_cycles_since_repair => l_msnt_rec.cycles_since_repair
,p_time_since_visit => l_msnt_rec.time_since_visit
,p_cycles_since_visit => l_msnt_rec.cycles_since_visit
,p_time_since_mark => l_msnt_rec.time_since_mark
,p_cycles_since_mark => l_msnt_rec.cycles_since_mark
,p_number_of_repairs => l_msnt_rec.number_of_repairs
,p_serial_attribute_category => l_msnt_rec.serial_attribute_category
,p_c_attribute1 => l_msnt_rec.c_attribute1
,p_c_attribute2 => l_msnt_rec.c_attribute2
,p_c_attribute3 => l_msnt_rec.c_attribute3
,p_c_attribute4 => l_msnt_rec.c_attribute4
,p_c_attribute5 => l_msnt_rec.c_attribute5
,p_c_attribute6 => l_msnt_rec.c_attribute6
,p_c_attribute7 => l_msnt_rec.c_attribute7
,p_c_attribute8 => l_msnt_rec.c_attribute8
,p_c_attribute9 => l_msnt_rec.c_attribute9
,p_c_attribute10 => l_msnt_rec.c_attribute10
,p_c_attribute11 => l_msnt_rec.c_attribute11
,p_c_attribute12 => l_msnt_rec.c_attribute12
,p_c_attribute13 => l_msnt_rec.c_attribute13
,p_c_attribute14 => l_msnt_rec.c_attribute14
,p_c_attribute15 => l_msnt_rec.c_attribute15
,p_c_attribute16 => l_msnt_rec.c_attribute16
,p_c_attribute17 => l_msnt_rec.c_attribute17
,p_c_attribute18 => l_msnt_rec.c_attribute18
,p_c_attribute19 => l_msnt_rec.c_attribute19
,p_c_attribute20 => l_msnt_rec.c_attribute20
,p_d_attribute1 => l_msnt_rec.d_attribute1
,p_d_attribute2 => l_msnt_rec.d_attribute2
,p_d_attribute3 => l_msnt_rec.d_attribute3
,p_d_attribute4 => l_msnt_rec.d_attribute4
,p_d_attribute5 => l_msnt_rec.d_attribute5
,p_d_attribute6 => l_msnt_rec.d_attribute6
,p_d_attribute7 => l_msnt_rec.d_attribute7
,p_d_attribute8 => l_msnt_rec.d_attribute8
,p_d_attribute9 => l_msnt_rec.d_attribute9
,p_d_attribute10 => l_msnt_rec.d_attribute10
,p_n_attribute1 => l_msnt_rec.n_attribute1
,p_n_attribute2 => l_msnt_rec.n_attribute2
,p_n_attribute3 => l_msnt_rec.n_attribute3
,p_n_attribute4 => l_msnt_rec.n_attribute4
,p_n_attribute5 => l_msnt_rec.n_attribute5
,p_n_attribute6 => l_msnt_rec.n_attribute6
,p_n_attribute7 => l_msnt_rec.n_attribute7
,p_n_attribute8 => l_msnt_rec.n_attribute8
,p_n_attribute9 => l_msnt_rec.n_attribute9
,p_n_attribute10 => l_msnt_rec.n_attribute10
);
print_debug('INSERT_LOT_SERIAL: ERROR- insert_msni Fail');
print_debug('INSERT_LOT_SERIAL: No MTLT exists. Check MSNT');
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_intf_id
FROM dual;
inv_rcv_integration_apis.insert_msni
(p_api_version => 1.0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_att_exist => 'N'
,p_transaction_interface_id => l_msnt_rec.transaction_temp_id
,p_fm_serial_number => l_msnt_rec.fm_serial_number
,p_to_serial_number => l_msnt_rec.to_serial_number
,p_organization_id => p_organization_id
,p_inventory_item_id => p_item_id -- from tmp mmtt
,p_status_id => 0 --l_msnt_rec.status_id
,p_product_transaction_id => l_intf_id
,p_product_code => 'RCV'
,p_vendor_serial_number => l_msnt_rec.vendor_serial_number
,p_vendor_lot_number => l_msnt_rec.vendor_lot_number
,p_parent_serial_number => l_msnt_rec.parent_serial_number
,p_origination_date => l_msnt_rec.origination_date
,p_territory_code => l_msnt_rec.territory_code
,p_time_since_new => l_msnt_rec.time_since_new
,p_cycles_since_new => l_msnt_rec.cycles_since_new
,p_time_since_overhaul => l_msnt_rec.time_since_overhaul
,p_cycles_since_overhaul => l_msnt_rec.cycles_since_overhaul
,p_time_since_repair => l_msnt_rec.time_since_repair
,p_cycles_since_repair => l_msnt_rec.cycles_since_repair
,p_time_since_visit => l_msnt_rec.time_since_visit
,p_cycles_since_visit => l_msnt_rec.cycles_since_visit
,p_time_since_mark => l_msnt_rec.time_since_mark
,p_cycles_since_mark => l_msnt_rec.cycles_since_mark
,p_number_of_repairs => l_msnt_rec.number_of_repairs
,p_serial_attribute_category => l_msnt_rec.serial_attribute_category
,p_c_attribute1 => l_msnt_rec.c_attribute1
,p_c_attribute2 => l_msnt_rec.c_attribute2
,p_c_attribute3 => l_msnt_rec.c_attribute3
,p_c_attribute4 => l_msnt_rec.c_attribute4
,p_c_attribute5 => l_msnt_rec.c_attribute5
,p_c_attribute6 => l_msnt_rec.c_attribute6
,p_c_attribute7 => l_msnt_rec.c_attribute7
,p_c_attribute8 => l_msnt_rec.c_attribute8
,p_c_attribute9 => l_msnt_rec.c_attribute9
,p_c_attribute10 => l_msnt_rec.c_attribute10
,p_c_attribute11 => l_msnt_rec.c_attribute11
,p_c_attribute12 => l_msnt_rec.c_attribute12
,p_c_attribute13 => l_msnt_rec.c_attribute13
,p_c_attribute14 => l_msnt_rec.c_attribute14
,p_c_attribute15 => l_msnt_rec.c_attribute15
,p_c_attribute16 => l_msnt_rec.c_attribute16
,p_c_attribute17 => l_msnt_rec.c_attribute17
,p_c_attribute18 => l_msnt_rec.c_attribute18
,p_c_attribute19 => l_msnt_rec.c_attribute19
,p_c_attribute20 => l_msnt_rec.c_attribute20
,p_d_attribute1 => l_msnt_rec.d_attribute1
,p_d_attribute2 => l_msnt_rec.d_attribute2
,p_d_attribute3 => l_msnt_rec.d_attribute3
,p_d_attribute4 => l_msnt_rec.d_attribute4
,p_d_attribute5 => l_msnt_rec.d_attribute5
,p_d_attribute6 => l_msnt_rec.d_attribute6
,p_d_attribute7 => l_msnt_rec.d_attribute7
,p_d_attribute8 => l_msnt_rec.d_attribute8
,p_d_attribute9 => l_msnt_rec.d_attribute9
,p_d_attribute10 => l_msnt_rec.d_attribute10
,p_n_attribute1 => l_msnt_rec.n_attribute1
,p_n_attribute2 => l_msnt_rec.n_attribute2
,p_n_attribute3 => l_msnt_rec.n_attribute3
,p_n_attribute4 => l_msnt_rec.n_attribute4
,p_n_attribute5 => l_msnt_rec.n_attribute5
,p_n_attribute6 => l_msnt_rec.n_attribute6
,p_n_attribute7 => l_msnt_rec.n_attribute7
,p_n_attribute8 => l_msnt_rec.n_attribute8
,p_n_attribute9 => l_msnt_rec.n_attribute9
,p_n_attribute10 => l_msnt_rec.n_attribute10
);
print_debug('INSERT_LOT_SERIAL: ERROR - insert_msni Fail');
FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
print_debug('INSERT_LOT_SERIAL: Exception occured');
print_debug('INSERT_LOT_SERIAL: Exception occured');
END insert_lot_serial;
SELECT
transaction_temp_id
BULK COLLECT INTO
l_txn_tmp_id_tb
FROM
mtl_material_transactions_temp
WHERE
( move_order_line_id = p_move_order_line_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)));
print_debug('ABORT_MMTTS: Unexpected Exception Raised in Bulk Select');
SELECT
mmtt.transaction_temp_id
BULK COLLECT INTO
l_txn_tmp_id_tb
FROM
mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl
WHERE
( ( mmtt.transaction_source_type_id = 1 AND
mmtt.transaction_action_id = 27) OR
( mmtt.transaction_source_type_id = 7 AND
mmtt.transaction_action_id = 12) OR
( mmtt.transaction_source_type_id = 12 AND
mmtt.transaction_action_id = 27) OR
( mmtt.transaction_source_type_id = 13 AND
mmtt.transaction_action_id = 12) OR
( mmtt.transaction_source_type_id = 4 AND
mmtt.transaction_action_id = 27) ) AND
mmtt.move_order_line_id = mtrl.line_id AND
mmtt.organization_id = p_organization_id AND
mtrl.organization_id = p_organization_id AND
mtrl.lpn_id IN (SELECT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_lpn_id
CONNECT BY wlpn.parent_lpn_id = PRIOR wlpn.lpn_id ) ;
print_debug('ABORT_MMTTS: Unexpected Exception Raised in Bulk Select');
SELECT
line_id
,txn_source_id
,reference_id
,reference
,reference_type_code
,revision
,lot_number
,inspection_status
FROM mtl_txn_request_lines
WHERE line_id = l_line_id;
SELECT
mmtt.transaction_temp_id transaction_temp_id,
mmtt.organization_id organization_id,
mmtt.transfer_organization transfer_organization,
mmtt.inventory_item_id inventory_item_id,
mmtt.lpn_id lpn_id,
mmtt.content_lpn_id content_lpn_id,
mmtt.transfer_lpn_id transfer_lpn_id,
mmtt.subinventory_code subinventory_code,
mmtt.transfer_subinventory transfer_subinventory,
mmtt.locator_id locator_id,
mmtt.transfer_to_location transfer_to_location,
mmtt.move_order_line_id move_order_line_id,
mmtt.transaction_quantity transaction_quantity,
mmtt.primary_quantity primary_quantity,
mmtt.transaction_uom transaction_uom,
decode(mmtt.inventory_item_id
,-1
,Decode(mmtt.lpn_id
,NULL
,Decode(mmtt.content_lpn_id
,NULL
,'UNKNOWN'
,Decode(mmtt.transfer_lpn_id
,NULL
,'LPN_MOVE'
,'LPN_PACK'))
,Decode(mmtt.content_lpn_id
,NULL
,'UNKNOWN'
,Decode(mmtt.transfer_lpn_id
,NULL
,'LPN_UNPACK'
,'LPN_SPLIT')))
,Decode(mmtt.lpn_id
,NULL
,Decode(mmtt.content_lpn_id
,NULL
,Decode(mmtt.transfer_lpn_id
,NULL
,'UNKNOWN'
,'ITEM_PACK')
,'UNKNOWN')
,Decode(mmtt.content_lpn_id
,NULL
,Decode(mmtt.transfer_lpn_id
,NULL
,'ITEM_UNPACK'
,'ITEM_SPLIT')
,'UNKNOWN'))) txn_type,
Decode(mmtt.subinventory_code
,mmtt.transfer_subinventory
,Decode(Nvl(mmtt.locator_id, -1)
,Nvl(mmtt.transfer_to_location, -1)
,0
,1)
,1) sub_loc_changed,
msi.lot_control_code lot_control_code,
msi.serial_number_control_code serial_control_code,
msi.primary_uom_code primary_uom_code,
-- OPM Convergance
mmtt.secondary_uom_code secondary_uom_code,
mmtt.secondary_transaction_quantity secondary_transaction_quantity
-- OPM Convergance
FROM
mtl_material_transactions_temp mmtt,
mtl_system_items msi
WHERE
l_header_id = mmtt.transaction_header_id AND
mmtt.inventory_item_id = msi.inventory_item_id (+) AND
mmtt.organization_id = msi.organization_id (+);
SELECT
mmtt.transaction_temp_id transaction_temp_id,
mmtt.organization_id organization_id,
mmtt.transfer_organization transfer_organization,
mmtt.inventory_item_id inventory_item_id,
mmtt.lpn_id lpn_id,
mmtt.content_lpn_id content_lpn_id,
mmtt.transfer_lpn_id transfer_lpn_id,
mmtt.subinventory_code subinventory_code,
mmtt.transfer_subinventory transfer_subinventory,
mmtt.locator_id locator_id,
mmtt.transfer_to_location transfer_to_location,
mmtt.move_order_line_id move_order_line_id,
mmtt.transaction_quantity transaction_quantity,
mmtt.primary_quantity primary_quantity,
mmtt.transaction_uom transaction_uom,
decode(mmtt.inventory_item_id
,-1
,Decode(mmtt.lpn_id
,NULL
,Decode(mmtt.content_lpn_id
,NULL
,'UNKNOWN'
,Decode(mmtt.transfer_lpn_id
,NULL
,'LPN_MOVE'
,'LPN_PACK'))
,Decode(mmtt.content_lpn_id
,NULL
,'UNKNOWN'
,Decode(mmtt.transfer_lpn_id
,NULL
,'LPN_UNPACK'
,'LPN_SPLIT')))
,Decode(mmtt.lpn_id
,NULL
,Decode(mmtt.content_lpn_id
,NULL
,Decode(mmtt.transfer_lpn_id
,NULL
,'UNKNOWN'
,'ITEM_PACK')
,'UNKNOWN')
,Decode(mmtt.content_lpn_id
,NULL
,Decode(mmtt.transfer_lpn_id
,NULL
,'ITEM_UNPACK'
,'ITEM_SPLIT')
,'UNKNOWN'))) txn_type,
Decode(mmtt.subinventory_code
,mmtt.transfer_subinventory
,Decode(Nvl(mmtt.locator_id, -1)
,Nvl(mmtt.transfer_to_location, -1)
,0
,1)
,1) sub_loc_changed,
msi.lot_control_code lot_control_code,
msi.serial_number_control_code serial_control_code,
msi.primary_uom_code primary_uom_code,
-- OPM Convergance
mmtt.secondary_uom_code secondary_uom_code,
mmtt.secondary_transaction_quantity secondary_transaction_quantity
-- OPM Convergance
FROM
mtl_material_transactions_temp mmtt,
mtl_system_items msi
WHERE
l_txn_id = mmtt.transaction_temp_id AND
mmtt.inventory_item_id = msi.inventory_item_id (+) AND
mmtt.organization_id = msi.organization_id (+);
l_uom_to_insert VARCHAR2(3);
l_qty_to_insert NUMBER;
l_mo_splt_tb.DELETE;
FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
wms_putaway_utils.insert_rti
(p_from_org => l_tmp_mmtt_rec.organization_id
,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_to_org => l_tmp_mmtt_rec.organization_id
,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_first_time => l_first_time
,p_mobile_txn => 'Y'
,p_txn_mode_code => p_txn_mode_code
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_debug('PACK_UNPACK_SPLIT: ERROR - insert_rti Fail');
FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') RTI inserted');
print_debug(' Calling insert_wlpni(');
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_tmp_mmtt_rec.organization_id
,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_license_plate_number => NULL
,p_lpn_group_id => l_group_id
,p_parent_lpn_id => NULL
);
print_debug('PACK_UNPACK_SPLIT: ERROR - insert_wlpni Fail');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNI successfully inserted');
FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
wms_putaway_utils.insert_rti
(p_from_org => l_tmp_mmtt_rec.organization_id
,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_to_org => l_tmp_mmtt_rec.organization_id
,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_first_time => l_first_time
,p_mobile_txn => 'Y'
,p_txn_mode_code => p_txn_mode_code
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_debug('ERROR: insert_rti Fail');
FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
print_debug('PACK_UNPACK_SPLIT: Calling insert_wlpni');
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_tmp_mmtt_rec.organization_id
,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_license_plate_number => NULL
,p_lpn_group_id => l_group_id
,p_parent_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
);
print_debug('PACK_UNPACK_SPLIT: ERROR - Insert WLPN Fail');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNI inserted successfully');
FND_MESSAGE.SET_NAME('WMS','WMS_CONT_PACK_UPDATE_ERR');
FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
wms_putaway_utils.insert_rti
(p_from_org => l_tmp_mmtt_rec.organization_id
,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_to_org => l_tmp_mmtt_rec.organization_id
,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_first_time => l_first_time
,p_mobile_txn => 'Y'
,p_txn_mode_code => p_txn_mode_code
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_debug('ERROR: insert_rti Fail');
FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') RTI inserted successfully');
print_debug('PACK_UNPACK_SPLIT: Calling insert_wlpni');
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_tmp_mmtt_rec.organization_id
,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_license_plate_number => NULL
,p_lpn_group_id => l_group_id
,p_parent_lpn_id => NULL
);
print_debug('PACK_UNPACK_SPLIT: ERROR - insert_wlpni Fail');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNI inserted successfully');
FND_MESSAGE.SET_NAME('WMS','WMS_CONT_UNPACK_UPDATE_ERR');
FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
wms_putaway_utils.insert_rti
(p_from_org => l_tmp_mmtt_rec.organization_id
,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_to_org => l_tmp_mmtt_rec.organization_id
,p_to_sub => l_tmp_mmtt_rec.transfer_subinventory
,p_to_loc => l_tmp_mmtt_rec.transfer_to_location
,p_xfer_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_first_time => l_first_time
,p_mobile_txn => 'Y'
,p_txn_mode_code => p_txn_mode_code
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_debug('PACK_UNPACK_SPLIT: ERROR - insert_rti FAIL');
FND_MESSAGE.SET_NAME('INV','INV_CANNOT_INSERT');
') RTIs inserted successfully');
print_debug('PACK_UNPACK_SPLIT: Calling insert_wlpni');
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_tmp_mmtt_rec.organization_id
,p_lpn_id => l_tmp_mmtt_rec.content_lpn_id
,p_license_plate_number => NULL
,p_lpn_group_id => l_group_id
,p_parent_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
);
print_debug('PACK_UNPACK_SPLIT: ERROR - insert_wlpni FAIL');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') WLPNIs inserted successfully');
FND_MESSAGE.SET_NAME('WMS','WMS_CONT_UNPACK_UPDATE_ERR');
FND_MESSAGE.SET_NAME('WMS','WMS_CONT_PACK_UPDATE_ERR');
FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
--Update the MOL with unused qty to 1, because the TM will only
--update wms_process_flag for the marked lines and not all
--lines in a LPN as done in 11.5.10
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
print_debug('PACK_UNPACK_SPLIT: Calling insert_lot_serial(');
insert_lot_serial
(p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
,p_organization_id => l_tmp_mmtt_rec.organization_id
,p_item_id => l_tmp_mmtt_rec.inventory_item_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_debug('PACK_UNPACK_SPLIT: ERROR: insert_lot_serial FAIL',
9);
print_debug('PACK_UNPACK_SPLIT: insert_lot_serial returns successfully WITH interface id = ' || l_old_intf_id);
--Update the MOL with unused qty to 1, because the TM will only
--update wms_process_flag for the marked lines and not all
--lines in a LPN as done in 11.5.10
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling insert_lot_serial');
insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
,p_organization_id => l_tmp_mmtt_rec.organization_id
,p_item_id => l_tmp_mmtt_rec.inventory_item_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_debug('PACK_UNPACK_SPLIT: ERROR - insert_lot_serial FAIL', 9);
--Update the MOL with unused qty to 1, because the TM will only
--update wms_process_flag for the marked lines and not all
--lines in a LPN as done in 11.5.10
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') split_mo sucessfully returns. Calling insert_lot_serial');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') Calling insert_lot_serial');
insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
,p_organization_id => l_tmp_mmtt_rec.organization_id
,p_item_id => l_tmp_mmtt_rec.inventory_item_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_debug('ERROR: insert_lot_serial FAIL', 9);
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') insert_lot_serail sucessfully returns. Starts TO LOOP mmtts');
-- Look at each new mmtt, and insert rti for each of them
-- Bug 5231114: Added the condition on transaction_source_type_id and
-- transaction_action_id for the following combinations:13/12 and 4/27
FOR l_mmtt_rec IN
( SELECT
transaction_temp_id
,transaction_quantity
,primary_quantity
,transaction_uom
,secondary_transaction_quantity
,secondary_uom_code
FROM
mtl_material_transactions_temp
WHERE
( move_order_line_id = l_mo_splt_tb(1).line_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))) )
LOOP
-- keep track of remaining quantity
l_progress := '6.2.5';
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_new_intf_id
FROM dual;
l_rti_tb.DELETE;
SELECT 1
INTO l_xfer_lpn_loaded
FROM dual
WHERE EXISTS (SELECT /*+ INDEX (wdt, WMS_DISPATCHED_TASKS_N2) */ 'LOADED'
FROM mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt
WHERE mmtt.organization_id = l_tmp_mmtt_rec.organization_id
AND mmtt.transaction_temp_id = wdt.transaction_temp_id
AND wdt.organization_id = l_tmp_mmtt_rec.organization_id
AND wdt.task_type = 2
AND wdt.status = 4
AND mmtt.lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = l_tmp_mmtt_rec.transfer_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
);
--Update the MOL with unused qty to 1, because the TM will only
--update wms_process_flag for the marked lines and not all
--lines in a LPN as done in 11.5.10
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
FND_MESSAGE.SET_NAME('WMS','WMS_TASK_DELETE_ERROR');
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') abort_mmtts returns sucessfully. Calling insert_lot_serial');
insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
,p_organization_id => l_tmp_mmtt_rec.organization_id
,p_item_id => l_tmp_mmtt_rec.inventory_item_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_debug('PACK_UNPACK_SPLIT: ERROR - insert_lot_serial FAIL', 9);
print_debug('PACK_UNPACK_SPLIT: ('||l_progress||') insert_lot_serial returns sucessfully.');
--Update the MOL with unused qty to 1, because the TM will only
--update wms_process_flag for the marked lines and not all
--lines in a LPN as done in 11.5.10
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = l_tmp_mmtt_rec.move_order_line_id;
') Calling insert_lot_serial');
insert_lot_serial (p_transaction_temp_id => l_tmp_mmtt_rec.transaction_temp_id
,p_organization_id => l_tmp_mmtt_rec.organization_id
,p_item_id => l_tmp_mmtt_rec.inventory_item_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_debug('PACK_UNPACK_SPLIT: ERROR - insert_lot_serail FAIL');
-- Look at each new mmtt, and insert rti for each of them
-- Bug 5231114: Added the condition on transaction_source_type_id and
-- transaction_action_id for the following combinations:13/12 and 4/27
FOR l_mmtt_rec IN
( SELECT
transaction_temp_id
,primary_quantity
,transaction_quantity
,transaction_uom
,secondary_transaction_quantity
,secondary_uom_code
FROM
mtl_material_transactions_temp
WHERE
( move_order_line_id = l_mo_splt_tb(1).line_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))) )
LOOP
l_progress := '7.2.6';
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_new_intf_id
FROM dual;
l_rti_tb.DELETE;
l_qty_to_insert := inv_rcv_cache.convert_qty
(p_inventory_item_id => l_tmp_mmtt_rec.inventory_item_id
,p_from_qty => l_mmtt_rec.transaction_quantity
,p_from_uom_code => l_mmtt_rec.transaction_uom
,p_to_uom_code => l_tmp_mmtt_rec.transaction_uom);
l_uom_to_insert := l_tmp_mmtt_rec.transaction_uom ;
l_uom_to_insert := l_mmtt_rec.transaction_uom;
l_qty_to_insert := l_mmtt_rec.transaction_quantity;
,p_transfer_quantity => l_qty_to_insert
,p_transfer_uom_code => l_uom_to_insert
,p_lot_control_code => l_tmp_mmtt_rec.lot_control_code
,p_serial_control_code => l_tmp_mmtt_rec.serial_control_code
,p_original_rti_id => l_new_intf_id
,p_original_temp_id => l_mmtt_rec.transaction_temp_id
,p_lot_number => l_mol_rec.lot_number
,p_lpn_id => l_tmp_mmtt_rec.lpn_id
,p_transfer_lpn_id => l_tmp_mmtt_rec.transfer_lpn_id
-- OPM Convergance
,p_sec_transfer_quantity => l_mmtt_rec.secondary_transaction_quantity
,p_sec_transfer_uom_code => l_mmtt_rec.secondary_uom_code
-- OPM Convergance
,p_primary_uom_code => l_tmp_mmtt_rec.primary_uom_code
,p_inspection_status => l_mol_rec.inspection_status
);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_mmtt_ids(i)
OR transaction_temp_id IN
(SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_mmtt_ids(i));
print_debug('# OF MSNT DELETED: ' || SQL%rowcount);
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_mmtt_ids(i);
print_debug('# OF MTLT DELETED: ' || SQL%rowcount);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_mmtt_ids(i);
print_debug('# OF MMTT DELETED: ' || SQL%rowcount);
COMMIT; --Need to commit the delete