[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mtlt.ROWID mtlt_rowid,
mtlt.*
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_transaction_temp_id;
SELECT msnt.* ,
msnt.ROWID msnt_rowid
FROM mtl_serial_numbers_temp msnt
WHERE transaction_temp_id = cp_transaction_temp_id;
SELECT cost_group_id,
serial_number
FROM mtl_serial_numbers
WHERE serial_number
BETWEEN cp_fm_serial_number AND Nvl(cp_to_serial_number, cp_fm_serial_number)
AND Length(serial_number)=cp_length
AND serial_number LIKE (cp_prefix||'%')
AND inventory_item_id = cp_inventory_item_id
AND current_organization_id = cp_organization_id;
inv_log_util.trace(p_message, 'INV_COST_GROUP_UPDATE', 9);
PROCEDURE proc_update_mmtt(p_transaction_temp_id IN NUMBER,
p_transfer_wms_org IN BOOLEAN,
p_fob_point IN NUMBER,
p_tfr_primary_cost_method IN NUMBER,
p_tfr_org_cost_group_id IN NUMBER,
p_transaction_action_id IN NUMBER,
p_transfer_organization IN NUMBER := NULL,
p_transfer_subinventory IN VARCHAR2,
p_cost_group_id IN NUMBER,
p_transfer_cost_group_id IN NUMBER,
p_primary_quantity IN NUMBER := NULL,
p_transaction_quantity IN NUMBER := NULL,
p_from_project_id IN NUMBER := NULL,
p_to_project_id IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_transfer_cost_group_id NUMBER := NULL;
print_debug('in proc_update_mmtt .. p_transaction_temp_id : '|| p_transaction_temp_id );
print_debug('in proc_update_mmtt .. p_cost_group_id : '|| p_cost_group_id );
print_debug('in proc_update_mmtt .. p_transaction_action_id : '|| p_transaction_action_id );
print_debug('in proc_update_mmtt .. p_transfer_organization : '|| p_transfer_organization );
print_debug('proc_update_mmtt .. l_transfer_cost_group_id: ' ||
l_transfer_cost_group_id || ':' );
UPDATE mtl_material_transactions_temp
SET cost_group_id = Nvl(p_cost_group_id, cost_group_id),
transfer_cost_group_id = Nvl(l_transfer_cost_group_id, transfer_cost_group_id),
primary_quantity = Nvl(p_primary_quantity, primary_quantity),
transaction_quantity = Nvl(p_transaction_quantity, transaction_quantity)
WHERE transaction_temp_id = p_transaction_temp_id;
fnd_message.set_name('INV', 'INV_UPDATE_ERROR');
print_debug('proc_update_mmtt .. nodatafound OTHERS : ' );
print_debug('proc_update_mmtt .. EXCEP G_EXC_ERROR : ' );
print_debug('proc_update_mmtt .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
print_debug('proc_update_mmtt .. UNEXCEP OTHERS : ' );
END proc_update_mmtt ;
PROCEDURE proc_update_msnt(p_rowid IN ROWID,
p_new_transaction_temp_id IN NUMBER,
p_from_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
x_valid VARCHAR2(1) := 'Y';
print_debug('in proc_update_msnt .. p_rowid: '|| p_rowid );
print_debug('in proc_update_msnt .. p_new_transaction_temp_id: '|| p_new_transaction_temp_id );
print_debug('in proc_update_msnt .. p_from_serial_number: '|| p_from_serial_number);
print_debug('in proc_update_msnt .. p_to_serial_number: '|| p_to_serial_number);
UPDATE mtl_serial_numbers_temp
SET
transaction_temp_id = p_new_transaction_temp_id,
fm_serial_number = p_from_serial_number,
to_serial_number = p_to_serial_number
WHERE ROWID = p_rowid;
fnd_message.set_name('INV', 'INV_UPDATE_ERROR');
print_debug('proc_update_msnt .. nodatafound OTHERS : ');
print_debug('proc_update_msnt .. UNEXCEP OTHERS : ');
END proc_update_msnt;
PROCEDURE proc_update_mtlt(p_rowid IN ROWID,
p_new_transaction_temp_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_primary_quantity IN NUMBER,
p_transaction_quantity IN NUMBER,
p_new_serial_trx_temp_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
x_valid VARCHAR2(1) := 'Y';
print_debug('in proc_update_mtlt .. p_rowid: '|| p_rowid);
print_debug('in proc_update_mtlt .. p_new_transaction_temp_id: '|| p_new_transaction_temp_id );
print_debug('in proc_update_mtlt .. p_lot_number: '|| p_lot_number);
print_debug('in proc_update_mtlt .. p_primary_quantity: '|| p_primary_quantity);
print_debug('in proc_update_mtlt .. p_transaction_quantity: '|| p_transaction_quantity);
print_debug('in proc_update_mtlt .. p_new_serial_trx_temp_id: '|| p_new_serial_trx_temp_id);
UPDATE mtl_transaction_lots_temp
SET
transaction_temp_id = p_new_transaction_temp_id,
lot_number = Nvl(p_lot_number, lot_number),
primary_quantity = Nvl(p_primary_quantity, primary_quantity),
transaction_quantity = Nvl(p_transaction_quantity, transaction_quantity),
serial_transaction_temp_id = p_new_serial_trx_temp_id
WHERE ROWID = p_rowid;
fnd_message.set_name('INV', 'INV_UPDATE_ERROR');
print_debug('proc_update_mtlt .. nodatafound OTHERS : ');
print_debug('proc_update_mtlt .. UNEXCEP OTHERS : ');
END proc_update_mtlt;
SELECT 1 INTO l_onhand FROM dual
WHERE exists
(SELECT organization_id
FROM mtl_onhand_quantities_detail moq
WHERE (moq.lot_number = p_lot_number
OR (p_lot_number IS NULL AND moq.lot_number IS NULL))
AND (moq.revision = p_revision
OR (p_revision IS NULL AND moq.revision IS NULL))
AND moq.inventory_item_id = p_inventory_item_id
AND Nvl(moq.locator_id, -1) = Nvl(p_locator_id, -1)
AND moq.subinventory_code = p_subinventory_code
AND moq.organization_id = p_organization_id
AND Nvl(moq.containerized_flag, 2) = 2); -- Loose Items only
SELECT 1 INTO l_onhand FROM dual
WHERE exists
(SELECT current_organization_id
FROM mtl_serial_numbers msn
WHERE (msn.lot_number = p_lot_number
OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
AND (msn.revision = p_revision
OR (p_revision IS NULL AND msn.revision IS NULL))
AND msn.inventory_item_id = p_inventory_item_id
AND Nvl(msn.current_locator_id, -1) = Nvl(p_locator_id, -1)
AND msn.current_subinventory_code = p_subinventory_code
AND msn.lpn_id IS NULL
AND msn.current_status = 3
AND msn.serial_number = p_serial_number
AND msn.current_organization_id = p_organization_id);
SELECT 1 INTO l_onhand FROM dual
WHERE exists
(SELECT wlpn.organization_id
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
WHERE (wlc.lot_number = p_lot_number
OR (p_lot_number IS NULL AND wlc.lot_number IS NULL))
AND (wlc.revision = p_revision
OR (p_revision IS NULL AND wlc.revision IS NULL))
AND wlc.inventory_item_id = p_inventory_item_id
AND Nvl(wlpn.locator_id, -1) = Nvl(p_locator_id, -1)
AND wlpn.subinventory_code = p_subinventory_code
AND wlpn.lpn_context IN (1,11) -- onhand, picked
AND wlc.parent_lpn_id = wlpn.lpn_id
AND wlc.organization_id = p_organization_id
AND wlc.parent_lpn_id = p_lpn_id);
SELECT 1 INTO l_onhand FROM dual
WHERE exists
(SELECT current_organization_id
FROM mtl_serial_numbers msn
WHERE (msn.lot_number = p_lot_number
OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
AND (msn.revision = p_revision
OR (p_revision IS NULL AND msn.revision IS NULL))
AND msn.lpn_id = p_lpn_id
AND msn.current_status = 3
AND msn.inventory_item_id = p_inventory_item_id
AND Nvl(msn.current_locator_id, -1) = Nvl(p_locator_id, -1)
AND msn.current_subinventory_code = p_subinventory_code
AND msn.serial_number = p_serial_number
AND msn.current_organization_id = p_organization_id);
SELECT 'Y' INTO l_valid FROM dual
WHERE
EXISTS
(SELECT ccgA.cost_group_id FROM
cst_cost_group_accounts CCGA
WHERE
ccga.cost_group_id = p_cost_group_id
AND ccga.organization_id = p_organization_id);
SELECT primary_cost_method, negative_inv_receipt_code
INTO l_primary_cost_method, l_negative_balances_allowed
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT default_cost_group_id
INTO x_cost_group_id
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT default_cost_group_id
INTO x_cost_group_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT default_cost_group_id
INTO x_cost_group_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
Select cost_group_id INTO x_cost_group_id FROM (
SELECT mmtt.cost_group_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_organization = p_organization_id
AND mmtt.transfer_subinventory = p_subinventory_code
AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
AND mmtt.inventory_item_id = p_inventory_item_id
AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
AND mmtt.transaction_action_id IN (inv_globals.g_action_subxfr,
inv_globals.g_action_orgxfr,
inv_globals.g_action_stgxfr)
UNION
-- No control
SELECT mmtt.cost_group_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = p_subinventory_code
AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
AND mmtt.inventory_item_id = p_inventory_item_id
AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
AND mmtt.transaction_action_id IN (inv_globals.g_action_cyclecountadj,
inv_globals.g_action_physicalcountadj,
inv_globals.g_action_intransitreceipt,
inv_globals.g_action_receipt,
inv_globals.g_action_assycomplete,
/*3199679inv_globals.g_action_assyreturn,*/
inv_globals.g_action_inv_lot_split,
inv_globals.g_action_inv_lot_merge,
inv_globals.g_action_inv_lot_translate))
WHERE ROWNUM = 1;
Select cost_group_id INTO x_cost_group_id FROM (
SELECT mmtt.cost_group_id
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE mmtt.transfer_organization = p_organization_id
AND mmtt.transfer_subinventory = p_subinventory_code
AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
AND mmtt.inventory_item_id = p_inventory_item_id
AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
AND mmtt.transaction_action_id IN (inv_globals.g_action_subxfr,
inv_globals.g_action_orgxfr,
inv_globals.g_action_stgxfr)
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_lot_number
UNION
SELECT mmtt.cost_group_id
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = p_subinventory_code
AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
AND mmtt.inventory_item_id = p_inventory_item_id
AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
AND mmtt.transaction_action_id IN (inv_globals.g_action_cyclecountadj,
inv_globals.g_action_physicalcountadj,
inv_globals.g_action_intransitreceipt,
inv_globals.g_action_receipt,
inv_globals.g_action_assycomplete,
/*3199679 inv_globals.g_action_assyreturn,*/
inv_globals.g_action_inv_lot_split,
inv_globals.g_action_inv_lot_merge,
inv_globals.g_action_inv_lot_translate)
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_lot_number)
WHERE ROWNUM = 1;
Select cost_group_id INTO x_cost_group_id FROM (
SELECT mmtt.cost_group_id
FROM mtl_material_transactions_temp mmtt,
mtl_serial_numbers_temp msnt
WHERE mmtt.transfer_organization = p_organization_id
AND mmtt.transfer_subinventory = p_subinventory_code
AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
AND mmtt.inventory_item_id = p_inventory_item_id
AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
AND mmtt.transaction_action_id IN (inv_globals.g_action_subxfr,
inv_globals.g_action_orgxfr,
inv_globals.g_action_stgxfr)
AND mmtt.transaction_temp_id = msnt.transaction_temp_id
AND msnt.fm_serial_number <= p_serial_number
AND msnt.to_serial_number >= p_serial_number
UNION
SELECT mmtt.cost_group_id
FROM mtl_material_transactions_temp mmtt,
mtl_serial_numbers_temp msnt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = p_subinventory_code
AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
AND mmtt.inventory_item_id = p_inventory_item_id
AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
AND mmtt.transaction_action_id IN (inv_globals.g_action_cyclecountadj,
inv_globals.g_action_physicalcountadj,
inv_globals.g_action_intransitreceipt,
inv_globals.g_action_receipt,
inv_globals.g_action_assycomplete,
/*3199679inv_globals.g_action_assyreturn,*/
inv_globals.g_action_inv_lot_split,
inv_globals.g_action_inv_lot_merge,
inv_globals.g_action_inv_lot_translate)
AND mmtt.transaction_temp_id = msnt.transaction_temp_id
AND msnt.fm_serial_number <= p_serial_number
AND msnt.to_serial_number >= p_serial_number)
WHERE ROWNUM = 1;
Select cost_group_id INTO x_cost_group_id FROM (
SELECT mmtt.cost_group_id
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
WHERE mmtt.transfer_organization = p_organization_id
AND mmtt.transfer_subinventory = p_subinventory_code
AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
AND mmtt.inventory_item_id = p_inventory_item_id
AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
AND mmtt.transaction_action_id IN (inv_globals.g_action_subxfr,
inv_globals.g_action_orgxfr,
inv_globals.g_action_stgxfr)
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_lot_number
AND mtlt.transaction_temp_id = msnt.transaction_temp_id
AND msnt.fm_serial_number <= p_serial_number
AND msnt.to_serial_number >= p_serial_number
UNION
SELECT mmtt.cost_group_id
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = p_subinventory_code
AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
AND mmtt.inventory_item_id = p_inventory_item_id
AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
AND mmtt.transaction_action_id IN (inv_globals.g_action_cyclecountadj,
inv_globals.g_action_physicalcountadj,
inv_globals.g_action_intransitreceipt,
inv_globals.g_action_receipt,
inv_globals.g_action_assycomplete,
/*3199679inv_globals.g_action_assyreturn,*/
inv_globals.g_action_inv_lot_split,
inv_globals.g_action_inv_lot_merge,
inv_globals.g_action_inv_lot_translate)
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_lot_number
AND mtlt.transaction_temp_id = msnt.transaction_temp_id
AND msnt.fm_serial_number <= p_serial_number
AND msnt.to_serial_number >= p_serial_number)
WHERE ROWNUM = 1;
SELECT moq.cost_group_id -- Loose material, no serial control
INTO x_cost_group_id
FROM mtl_onhand_quantities_detail moq
WHERE (moq.lot_number = p_lot_number
OR (p_lot_number IS NULL AND moq.lot_number IS NULL))
AND (moq.revision = p_revision
OR (p_revision IS NULL AND moq.revision IS NULL))
AND moq.inventory_item_id = p_inventory_item_id
AND nvl(moq.locator_id, -1) = Nvl(p_locator_id, -1)
AND moq.subinventory_code = p_subinventory_code
AND moq.organization_id = p_organization_id
AND Nvl(moq.containerized_flag, 2) = 2 -- Loose Items only
AND ROWNUM = 1;
SELECT msn.cost_group_id -- Loose material, serial control
INTO x_cost_group_id
FROM mtl_serial_numbers msn
WHERE (msn.lot_number = p_lot_number
OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
AND (msn.revision = p_revision
OR (p_revision IS NULL AND msn.revision IS NULL))
AND msn.inventory_item_id = p_inventory_item_id
AND Nvl(msn.current_locator_id, -1) = Nvl(p_locator_id, -1)
AND msn.current_subinventory_code = p_subinventory_code
AND msn.current_status = 3
AND msn.serial_number = p_serial_number
AND msn.current_organization_id = p_organization_id
AND ROWNUM = 1;
SELECT cost_group_id INTO x_cost_group_id
FROM (
SELECT wlc.cost_group_id
FROM wms_lpn_contents wlc,
wms_license_plate_numbers wlpn
WHERE (wlc.lot_number = p_lot_number
OR (p_lot_number IS NULL AND wlc.lot_number IS NULL))
AND (wlc.revision = p_revision
OR (p_revision IS NULL AND wlc.revision IS NULL))
AND wlc.inventory_item_id = p_inventory_item_id
AND wlc.parent_lpn_id = wlpn.lpn_id
-- Bug 2393441 - During ship confirmation, an LPN may
-- have blank sub and loc if some lines belonging to the LPN
-- are shipped out. To prevent the API from erroring out the
-- following checks are commented out
-- AND Nvl(wlpn.locator_id, -1) = nvl(p_locator_id, -1)
-- AND wlpn.subinventory_code = p_subinventory_code
AND wlpn.organization_id = p_organization_id
AND wlpn.lpn_id = p_lpn_id
AND ROWNUM = 1
UNION --Bug#6133411.Added the UNION and outer SELECT as well.
SELECT moq.cost_group_id
FROM mtl_onhand_quantities_detail moq
WHERE (moq.lot_number = p_lot_number
OR (p_lot_number IS NULL AND moq.lot_number IS NULL))
AND (moq.revision = p_revision
OR (p_revision IS NULL AND moq.revision IS NULL))
AND moq.inventory_item_id = p_inventory_item_id
AND moq.locator_id = p_locator_id
AND moq.subinventory_code = p_subinventory_code
AND moq.organization_id = p_organization_id
AND moq.containerized_flag = 1
AND moq.lpn_id = p_lpn_id
AND ROWNUM < 2 )
WHERE ROWNUM < 2 ;
SELECT msn.cost_group_id -- Packed material, serial control
INTO x_cost_group_id
FROM mtl_serial_numbers msn
WHERE (msn.lot_number = p_lot_number
OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
AND (msn.revision = p_revision
OR (p_revision IS NULL AND msn.revision IS NULL))
AND msn.lpn_id = p_lpn_id
AND msn.current_status = 3
AND msn.inventory_item_id = p_inventory_item_id
AND Nvl(msn.current_locator_id, -1) = Nvl(p_locator_id, -1)
AND msn.current_subinventory_code = p_subinventory_code
AND msn.serial_number = p_serial_number
AND msn.current_organization_id = p_organization_id
AND ROWNUM = 1;
PROCEDURE proc_insert_msnt(p_msnt_rec IN cur_msnt%ROWTYPE,
p_from_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
p_new_txn_temp_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(100) := 'proc_insert_msnt';
SAVEPOINT sp_proc_insert_msnt;
print_debug('proc_insert_msnt.. FSN: ' || p_from_serial_number);
print_debug('proc_insert_msnt.. TSN: ' || p_to_serial_number);
print_debug('proc_insert_msnt.. Txn temp id: ' || p_new_txn_temp_id);
INSERT INTO mtl_serial_numbers_temp
(TRANSACTION_TEMP_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,VENDOR_SERIAL_NUMBER
,VENDOR_LOT_NUMBER
,FM_SERIAL_NUMBER
,TO_SERIAL_NUMBER
,SERIAL_PREFIX
,ERROR_CODE
,PARENT_SERIAL_NUMBER
,GROUP_HEADER_ID
,END_ITEM_UNIT_NUMBER
,SERIAL_ATTRIBUTE_CATEGORY
,TERRITORY_CODE
,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
,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
,OBJECT_TYPE2 -- R12 Genealogy Enhancements
,OBJECT_NUMBER2 -- R12 Genealogy Enhancements
,PARENT_OBJECT_TYPE -- R12 Genealogy Enhancements
,PARENT_OBJECT_ID -- R12 Genealogy Enhancements
,PARENT_OBJECT_NUMBER -- R12 Genealogy Enhancements
,PARENT_ITEM_ID -- R12 Genealogy Enhancements
,PARENT_OBJECT_TYPE2 -- R12 Genealogy Enhancements
,PARENT_OBJECT_ID2 -- R12 Genealogy Enhancements
,PARENT_OBJECT_NUMBER2) -- R12 Genealogy Enhancements
VALUES (p_new_txn_temp_id
,p_msnt_rec.LAST_UPDATE_DATE
,p_msnt_rec.LAST_UPDATED_BY
,p_msnt_rec.CREATION_DATE
,p_msnt_rec.CREATED_BY
,p_msnt_rec.LAST_UPDATE_LOGIN
,p_msnt_rec.REQUEST_ID
,p_msnt_rec.PROGRAM_APPLICATION_ID
,p_msnt_rec.PROGRAM_ID
,p_msnt_rec.PROGRAM_UPDATE_DATE
,p_msnt_rec.VENDOR_SERIAL_NUMBER
,p_msnt_rec.VENDOR_LOT_NUMBER
,p_from_serial_number
,p_to_serial_number
,p_msnt_rec.SERIAL_PREFIX
,p_msnt_rec.ERROR_CODE
,p_msnt_rec.PARENT_SERIAL_NUMBER
,p_msnt_rec.GROUP_HEADER_ID
,p_msnt_rec.END_ITEM_UNIT_NUMBER
,p_msnt_rec.SERIAL_ATTRIBUTE_CATEGORY
,p_msnt_rec.TERRITORY_CODE
,p_msnt_rec.ORIGINATION_DATE
,p_msnt_rec.C_ATTRIBUTE1
,p_msnt_rec.C_ATTRIBUTE2
,p_msnt_rec.C_ATTRIBUTE3
,p_msnt_rec.C_ATTRIBUTE4
,p_msnt_rec.C_ATTRIBUTE5
,p_msnt_rec.C_ATTRIBUTE6
,p_msnt_rec.C_ATTRIBUTE7
,p_msnt_rec.C_ATTRIBUTE8
,p_msnt_rec.C_ATTRIBUTE9
,p_msnt_rec.C_ATTRIBUTE10
,p_msnt_rec.C_ATTRIBUTE11
,p_msnt_rec.C_ATTRIBUTE12
,p_msnt_rec.C_ATTRIBUTE13
,p_msnt_rec.C_ATTRIBUTE14
,p_msnt_rec.C_ATTRIBUTE15
,p_msnt_rec.C_ATTRIBUTE16
,p_msnt_rec.C_ATTRIBUTE17
,p_msnt_rec.C_ATTRIBUTE18
,p_msnt_rec.C_ATTRIBUTE19
,p_msnt_rec.C_ATTRIBUTE20
,p_msnt_rec.D_ATTRIBUTE1
,p_msnt_rec.D_ATTRIBUTE2
,p_msnt_rec.D_ATTRIBUTE3
,p_msnt_rec.D_ATTRIBUTE4
,p_msnt_rec.D_ATTRIBUTE5
,p_msnt_rec.D_ATTRIBUTE6
,p_msnt_rec.D_ATTRIBUTE7
,p_msnt_rec.D_ATTRIBUTE8
,p_msnt_rec.D_ATTRIBUTE9
,p_msnt_rec.D_ATTRIBUTE10
,p_msnt_rec.N_ATTRIBUTE1
,p_msnt_rec.N_ATTRIBUTE2
,p_msnt_rec.N_ATTRIBUTE3
,p_msnt_rec.N_ATTRIBUTE4
,p_msnt_rec.N_ATTRIBUTE5
,p_msnt_rec.N_ATTRIBUTE6
,p_msnt_rec.N_ATTRIBUTE7
,p_msnt_rec.N_ATTRIBUTE8
,p_msnt_rec.N_ATTRIBUTE9
,p_msnt_rec.N_ATTRIBUTE10
,p_msnt_rec.STATUS_ID
,p_msnt_rec.TIME_SINCE_NEW
,p_msnt_rec.CYCLES_SINCE_NEW
,p_msnt_rec.TIME_SINCE_OVERHAUL
,p_msnt_rec.CYCLES_SINCE_OVERHAUL
,p_msnt_rec.TIME_SINCE_REPAIR
,p_msnt_rec.CYCLES_SINCE_REPAIR
,p_msnt_rec.TIME_SINCE_VISIT
,p_msnt_rec.CYCLES_SINCE_VISIT
,p_msnt_rec.TIME_SINCE_MARK
,p_msnt_rec.CYCLES_SINCE_MARK
,p_msnt_rec.number_of_repairs
,p_msnt_rec.OBJECT_TYPE2 -- R12 Genealogy Enhancements
,p_msnt_rec.OBJECT_NUMBER2 -- R12 Genealogy Enhancements
,p_msnt_rec.PARENT_OBJECT_TYPE -- R12 Genealogy Enhancements
,p_msnt_rec.PARENT_OBJECT_ID -- R12 Genealogy Enhancements
,p_msnt_rec.PARENT_OBJECT_NUMBER -- R12 Genealogy Enhancements
,p_msnt_rec.PARENT_ITEM_ID -- R12 Genealogy Enhancements
,p_msnt_rec.PARENT_OBJECT_TYPE2 -- R12 Genealogy Enhancements
,p_msnt_rec.PARENT_OBJECT_ID2 -- R12 Genealogy Enhancements
,p_msnt_rec.PARENT_OBJECT_NUMBER2); -- R12 Genealogy Enhancements
print_debug( 'proc_insert_msnt .. EXCEP others : ' );
ROLLBACK TO sp_proc_insert_msnt;
END proc_insert_msnt;
PROCEDURE proc_insert_mtlt (p_mtlt_rec IN cur_mtlt%ROWTYPE,
p_new_txn_temp_id IN NUMBER,
p_prim_qty IN NUMBER,
p_txn_qty IN NUMBER,
p_new_serial_trx_temp_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
SAVEPOINT sp_proc_insert_mtlt;
print_debug( 'proc_insert_mtlt ..p_new_txn_temp_id :'|| p_new_txn_temp_id );
print_debug( 'proc_insert_mtlt ..p_prim_qty'|| p_prim_qty );
print_debug( 'proc_insert_mtlt ..Lot : '|| p_mtlt_rec.lot_number );
print_debug( 'proc_insert_mtlt ..p_new_serial_trx_temp_id: '|| p_new_serial_trx_temp_id );
print_debug( 'proc_insert_mtlt ..RowId : '|| p_mtlt_rec.mtlt_RowId );
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
,GROUP_HEADER_ID
,PUT_AWAY_RULE_ID
,PICK_RULE_ID
,DESCRIPTION
,VENDOR_ID
,SUPPLIER_LOT_NUMBER
,TERRITORY_CODE
,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_name
,PARENT_OBJECT_TYPE -- R12 Genealogy Enhancements
,PARENT_OBJECT_ID -- R12 Genealogy Enhancements
,PARENT_OBJECT_NUMBER -- R12 Genealogy Enhancements
,PARENT_ITEM_ID -- R12 Genealogy Enhancements
,PARENT_OBJECT_TYPE2 -- R12 Genealogy Enhancements
,PARENT_OBJECT_ID2 -- R12 Genealogy Enhancements
,PARENT_OBJECT_NUMBER2) -- R12 Genealogy Enhancements
VALUES (p_new_txn_temp_id
,p_mtlt_rec.LAST_UPDATE_DATE
,p_mtlt_rec.LAST_UPDATED_BY
,p_mtlt_rec.CREATION_DATE
,p_mtlt_rec.CREATED_BY
,p_mtlt_rec.LAST_UPDATE_LOGIN
,p_mtlt_rec.REQUEST_ID
,p_mtlt_rec.PROGRAM_APPLICATION_ID
,p_mtlt_rec.PROGRAM_ID
,p_mtlt_rec.PROGRAM_UPDATE_DATE
,p_txn_qty
,p_prim_qty
,p_mtlt_rec.LOT_NUMBER
,p_mtlt_rec.LOT_EXPIRATION_DATE
,p_mtlt_rec.ERROR_CODE
,p_new_serial_trx_temp_id
,p_mtlt_rec.GROUP_HEADER_ID
,p_mtlt_rec.PUT_AWAY_RULE_ID
,p_mtlt_rec.PICK_RULE_ID
,p_mtlt_rec.DESCRIPTION
,p_mtlt_rec.VENDOR_ID
,p_mtlt_rec.SUPPLIER_LOT_NUMBER
,p_mtlt_rec.TERRITORY_CODE
,p_mtlt_rec.ORIGINATION_DATE
,p_mtlt_rec.DATE_CODE
,p_mtlt_rec.GRADE_CODE
,p_mtlt_rec.CHANGE_DATE
,p_mtlt_rec.MATURITY_DATE
,p_mtlt_rec.STATUS_ID
,p_mtlt_rec.RETEST_DATE
,p_mtlt_rec.AGE
,p_mtlt_rec.ITEM_SIZE
,p_mtlt_rec.COLOR
,p_mtlt_rec.VOLUME
,p_mtlt_rec.VOLUME_UOM
,p_mtlt_rec.PLACE_OF_ORIGIN
,p_mtlt_rec.BEST_BY_DATE
,p_mtlt_rec.LENGTH
,p_mtlt_rec.LENGTH_UOM
,p_mtlt_rec.RECYCLED_CONTENT
,p_mtlt_rec.THICKNESS
,p_mtlt_rec.THICKNESS_UOM
,p_mtlt_rec.WIDTH
,p_mtlt_rec.WIDTH_UOM
,p_mtlt_rec.CURL_WRINKLE_FOLD
,p_mtlt_rec.LOT_ATTRIBUTE_CATEGORY
,p_mtlt_rec.C_ATTRIBUTE1
,p_mtlt_rec.C_ATTRIBUTE2
,p_mtlt_rec.C_ATTRIBUTE3
,p_mtlt_rec.C_ATTRIBUTE4
,p_mtlt_rec.C_ATTRIBUTE5
,p_mtlt_rec.C_ATTRIBUTE6
,p_mtlt_rec.C_ATTRIBUTE7
,p_mtlt_rec.C_ATTRIBUTE8
,p_mtlt_rec.C_ATTRIBUTE9
,p_mtlt_rec.C_ATTRIBUTE10
,p_mtlt_rec.C_ATTRIBUTE11
,p_mtlt_rec.C_ATTRIBUTE12
,p_mtlt_rec.C_ATTRIBUTE13
,p_mtlt_rec.C_ATTRIBUTE14
,p_mtlt_rec.C_ATTRIBUTE15
,p_mtlt_rec.C_ATTRIBUTE16
,p_mtlt_rec.C_ATTRIBUTE17
,p_mtlt_rec.C_ATTRIBUTE18
,p_mtlt_rec.C_ATTRIBUTE19
,p_mtlt_rec.C_ATTRIBUTE20
,p_mtlt_rec.D_ATTRIBUTE1
,p_mtlt_rec.D_ATTRIBUTE2
,p_mtlt_rec.D_ATTRIBUTE3
,p_mtlt_rec.D_ATTRIBUTE4
,p_mtlt_rec.D_ATTRIBUTE5
,p_mtlt_rec.D_ATTRIBUTE6
,p_mtlt_rec.D_ATTRIBUTE7
,p_mtlt_rec.D_ATTRIBUTE8
,p_mtlt_rec.D_ATTRIBUTE9
,p_mtlt_rec.D_ATTRIBUTE10
,p_mtlt_rec.N_ATTRIBUTE1
,p_mtlt_rec.N_ATTRIBUTE2
,p_mtlt_rec.N_ATTRIBUTE3
,p_mtlt_rec.N_ATTRIBUTE4
,p_mtlt_rec.N_ATTRIBUTE5
,p_mtlt_rec.N_ATTRIBUTE6
,p_mtlt_rec.N_ATTRIBUTE7
,p_mtlt_rec.N_ATTRIBUTE8
,p_mtlt_rec.N_ATTRIBUTE9
,p_mtlt_rec.N_ATTRIBUTE10
,p_mtlt_rec.vendor_name
,p_mtlt_rec.PARENT_OBJECT_TYPE -- R12 Genealogy Enhancements
,p_mtlt_rec.PARENT_OBJECT_ID -- R12 Genealogy Enhancements
,p_mtlt_rec.PARENT_OBJECT_NUMBER -- R12 Genealogy Enhancements
,p_mtlt_rec.PARENT_ITEM_ID -- R12 Genealogy Enhancements
,p_mtlt_rec.PARENT_OBJECT_TYPE2 -- R12 Genealogy Enhancements
,p_mtlt_rec.PARENT_OBJECT_ID2 -- R12 Genealogy Enhancements
,p_mtlt_rec.PARENT_OBJECT_NUMBER2); -- R12 Genealogy Enhancements
print_debug( 'proc_insert_mtlt .. EXCEP others : ' );
ROLLBACK TO sp_proc_insert_mtlt;
END proc_insert_mtlt;
PROCEDURE proc_insert_mmtt(p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE,
p_transfer_wms_org IN BOOLEAN,
p_fob_point IN NUMBER,
p_tfr_primary_cost_method IN NUMBER,
p_tfr_org_cost_group_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_transfer_cost_group_id IN NUMBER,
p_prim_qty IN NUMBER,
p_txn_qty IN NUMBER,
p_new_txn_temp_id IN NUMBER,
p_from_project_id IN NUMBER,
p_to_project_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_transfer_cost_group_id NUMBER := NULL;
print_debug( 'Begin proc_insert_mmtt: action_id: ' || p_mmtt_rec.transaction_action_id );
SAVEPOINT sp_proc_insert_mmtt;
print_debug('inserting the transfer_cost_group to null as the dest'||
'locator is proj enabled');
print_debug( 'Begin proc_insert_mmtt: l_Transfer_cost_group_id: ' || l_Transfer_cost_group_id);
INSERT INTO
mtl_material_transactions_temp
(
TRANSACTION_HEADER_ID ,
TRANSACTION_TEMP_ID ,
SOURCE_CODE ,
SOURCE_LINE_ID ,
TRANSACTION_MODE ,
LOCK_FLAG ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
INVENTORY_ITEM_ID ,
REVISION ,
ORGANIZATION_ID ,
SUBINVENTORY_CODE ,
LOCATOR_ID ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
TRANSACTION_UOM ,
TRANSACTION_COST ,
TRANSACTION_TYPE_ID ,
TRANSACTION_ACTION_ID ,
TRANSACTION_SOURCE_TYPE_ID ,
TRANSACTION_SOURCE_ID ,
TRANSACTION_SOURCE_NAME ,
TRANSACTION_DATE ,
ACCT_PERIOD_ID ,
DISTRIBUTION_ACCOUNT_ID ,
TRANSACTION_REFERENCE ,
REQUISITION_LINE_ID ,
REQUISITION_DISTRIBUTION_ID ,
REASON_ID ,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
SERIAL_NUMBER ,
RECEIVING_DOCUMENT ,
DEMAND_ID ,
RCV_TRANSACTION_ID ,
MOVE_TRANSACTION_ID ,
COMPLETION_TRANSACTION_ID ,
WIP_ENTITY_TYPE ,
SCHEDULE_ID ,
REPETITIVE_LINE_ID ,
EMPLOYEE_CODE ,
PRIMARY_SWITCH ,
SCHEDULE_UPDATE_CODE ,
SETUP_TEARDOWN_CODE ,
ITEM_ORDERING ,
NEGATIVE_REQ_FLAG ,
OPERATION_SEQ_NUM ,
PICKING_LINE_ID ,
TRX_SOURCE_LINE_ID ,
TRX_SOURCE_DELIVERY_ID ,
PHYSICAL_ADJUSTMENT_ID ,
CYCLE_COUNT_ID ,
RMA_LINE_ID ,
CUSTOMER_SHIP_ID ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_RATE ,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
USSGL_TRANSACTION_CODE ,
VENDOR_LOT_NUMBER ,
ENCUMBRANCE_ACCOUNT ,
ENCUMBRANCE_AMOUNT ,
SHIP_TO_LOCATION ,
SHIPMENT_NUMBER ,
TRANSFER_COST ,
TRANSPORTATION_COST ,
TRANSPORTATION_ACCOUNT ,
FREIGHT_CODE ,
CONTAINERS ,
WAYBILL_AIRBILL ,
EXPECTED_ARRIVAL_DATE ,
TRANSFER_SUBINVENTORY ,
TRANSFER_ORGANIZATION ,
TRANSFER_TO_LOCATION ,
NEW_AVERAGE_COST ,
VALUE_CHANGE ,
PERCENTAGE_CHANGE ,
MATERIAL_ALLOCATION_TEMP_ID ,
DEMAND_SOURCE_HEADER_ID ,
DEMAND_SOURCE_LINE ,
DEMAND_SOURCE_DELIVERY ,
ITEM_SEGMENTS ,
ITEM_DESCRIPTION ,
ITEM_TRX_ENABLED_FLAG ,
ITEM_LOCATION_CONTROL_CODE ,
ITEM_RESTRICT_SUBINV_CODE ,
ITEM_RESTRICT_LOCATORS_CODE ,
ITEM_REVISION_QTY_CONTROL_CODE ,
ITEM_PRIMARY_UOM_CODE ,
ITEM_UOM_CLASS ,
ITEM_SHELF_LIFE_CODE ,
ITEM_SHELF_LIFE_DAYS ,
ITEM_LOT_CONTROL_CODE ,
ITEM_SERIAL_CONTROL_CODE ,
ITEM_INVENTORY_ASSET_FLAG ,
ALLOWED_UNITS_LOOKUP_CODE ,
DEPARTMENT_ID ,
DEPARTMENT_CODE ,
WIP_SUPPLY_TYPE ,
SUPPLY_SUBINVENTORY ,
SUPPLY_LOCATOR_ID ,
VALID_SUBINVENTORY_FLAG ,
VALID_LOCATOR_FLAG ,
LOCATOR_SEGMENTS ,
CURRENT_LOCATOR_CONTROL_CODE ,
NUMBER_OF_LOTS_ENTERED ,
WIP_COMMIT_FLAG ,
NEXT_LOT_NUMBER ,
LOT_ALPHA_PREFIX ,
NEXT_SERIAL_NUMBER ,
SERIAL_ALPHA_PREFIX ,
SHIPPABLE_FLAG ,
POSTING_FLAG ,
REQUIRED_FLAG ,
PROCESS_FLAG ,
ERROR_CODE ,
ERROR_EXPLANATION ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
MOVEMENT_ID ,
RESERVATION_QUANTITY ,
SHIPPED_QUANTITY ,
TRANSACTION_LINE_NUMBER ,
TASK_ID ,
TO_TASK_ID ,
SOURCE_TASK_ID ,
PROJECT_ID ,
SOURCE_PROJECT_ID ,
PA_EXPENDITURE_ORG_ID ,
TO_PROJECT_ID ,
EXPENDITURE_TYPE ,
FINAL_COMPLETION_FLAG ,
TRANSFER_PERCENTAGE ,
TRANSACTION_SEQUENCE_ID ,
MATERIAL_ACCOUNT ,
MATERIAL_OVERHEAD_ACCOUNT ,
RESOURCE_ACCOUNT ,
OUTSIDE_PROCESSING_ACCOUNT ,
OVERHEAD_ACCOUNT ,
FLOW_SCHEDULE ,
COST_GROUP_ID ,
TRANSFER_COST_GROUP_ID ,
DEMAND_CLASS ,
QA_COLLECTION_ID ,
KANBAN_CARD_ID ,
OVERCOMPLETION_TRANSACTION_QTY ,
OVERCOMPLETION_PRIMARY_QTY ,
OVERCOMPLETION_TRANSACTION_ID ,
END_ITEM_UNIT_NUMBER ,
SCHEDULED_PAYBACK_DATE ,
LINE_TYPE_CODE ,
PARENT_TRANSACTION_TEMP_ID ,
PUT_AWAY_STRATEGY_ID ,
PUT_AWAY_RULE_ID ,
PICK_STRATEGY_ID ,
PICK_RULE_ID ,
MOVE_ORDER_LINE_ID ,
TASK_GROUP_ID ,
PICK_SLIP_NUMBER ,
RESERVATION_ID ,
COMMON_BOM_SEQ_ID ,
COMMON_ROUTING_SEQ_ID ,
ORG_COST_GROUP_ID ,
COST_TYPE_ID ,
TRANSACTION_STATUS ,
STANDARD_OPERATION_ID ,
TASK_PRIORITY ,
WMS_TASK_TYPE ,
PARENT_LINE_ID ,
LPN_ID ,
TRANSFER_LPN_ID ,
WMS_TASK_STATUS ,
CONTENT_LPN_ID ,
CONTAINER_ITEM_ID ,
CARTONIZATION_ID ,
PICK_SLIP_DATE ,
REBUILD_ITEM_ID ,
REBUILD_SERIAL_NUMBER ,
REBUILD_ACTIVITY_ID ,
REBUILD_JOB_NAME ,
ORGANIZATION_TYPE ,
TRANSFER_ORGANIZATION_TYPE ,
OWNING_ORGANIZATION_ID ,
OWNING_TP_TYPE ,
XFR_OWNING_ORGANIZATION_ID ,
TRANSFER_OWNING_TP_TYPE ,
PLANNING_ORGANIZATION_ID ,
PLANNING_TP_TYPE ,
XFR_PLANNING_ORGANIZATION_ID ,
TRANSFER_PLANNING_TP_TYPE ,
SECONDARY_UOM_CODE ,
SECONDARY_TRANSACTION_QUANTITY ,
TRANSACTION_BATCH_ID ,
TRANSACTION_BATCH_SEQ ,
ALLOCATED_LPN_ID ,
SCHEDULE_NUMBER ,
SCHEDULED_FLAG ,
CLASS_CODE ,
SCHEDULE_GROUP ,
BUILD_SEQUENCE ,
BOM_REVISION ,
ROUTING_REVISION ,
BOM_REVISION_DATE ,
ROUTING_REVISION_DATE ,
ALTERNATE_BOM_DESIGNATOR ,
ALTERNATE_ROUTING_DESIGNATOR ,
OPERATION_PLAN_ID ,
fob_point ,
intransit_account ,
relieve_reservations_flag , /*** {{ R12 Enhanced reservations code changes ***/
relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
)
values
(p_mmtt_rec.TRANSACTION_HEADER_ID ,
p_new_txn_temp_id ,
p_mmtt_rec.SOURCE_CODE ,
p_mmtt_rec.SOURCE_LINE_ID ,
p_mmtt_rec.TRANSACTION_MODE ,
p_mmtt_rec.LOCK_FLAG ,
p_mmtt_rec.LAST_UPDATE_DATE ,
p_mmtt_rec.LAST_UPDATED_BY ,
p_mmtt_rec.CREATION_DATE ,
p_mmtt_rec.CREATED_BY ,
p_mmtt_rec.LAST_UPDATE_LOGIN ,
p_mmtt_rec.REQUEST_ID ,
p_mmtt_rec.PROGRAM_APPLICATION_ID ,
p_mmtt_rec.PROGRAM_ID ,
p_mmtt_rec.PROGRAM_UPDATE_DATE ,
p_mmtt_rec.INVENTORY_ITEM_ID ,
p_mmtt_rec.REVISION ,
p_mmtt_rec.ORGANIZATION_ID ,
p_mmtt_rec.SUBINVENTORY_CODE ,
p_mmtt_rec.LOCATOR_ID ,
p_txn_qty ,
p_prim_qty ,
p_mmtt_rec.TRANSACTION_UOM ,
p_mmtt_rec.TRANSACTION_COST ,
p_mmtt_rec.TRANSACTION_TYPE_ID ,
p_mmtt_rec.TRANSACTION_ACTION_ID ,
p_mmtt_rec.TRANSACTION_SOURCE_TYPE_ID ,
p_mmtt_rec.TRANSACTION_SOURCE_ID ,
p_mmtt_rec.TRANSACTION_SOURCE_NAME ,
p_mmtt_rec.TRANSACTION_DATE ,
p_mmtt_rec.ACCT_PERIOD_ID ,
p_mmtt_rec.DISTRIBUTION_ACCOUNT_ID ,
p_mmtt_rec.TRANSACTION_REFERENCE ,
p_mmtt_rec.REQUISITION_LINE_ID ,
p_mmtt_rec.REQUISITION_DISTRIBUTION_ID ,
p_mmtt_rec.REASON_ID ,
p_mmtt_rec.LOT_NUMBER ,
p_mmtt_rec.LOT_EXPIRATION_DATE ,
p_mmtt_rec.SERIAL_NUMBER ,
p_mmtt_rec.RECEIVING_DOCUMENT ,
p_mmtt_rec.DEMAND_ID ,
p_mmtt_rec.RCV_TRANSACTION_ID ,
p_mmtt_rec.MOVE_TRANSACTION_ID ,
p_mmtt_rec.COMPLETION_TRANSACTION_ID ,
p_mmtt_rec.WIP_ENTITY_TYPE ,
p_mmtt_rec.SCHEDULE_ID ,
p_mmtt_rec.REPETITIVE_LINE_ID ,
p_mmtt_rec.EMPLOYEE_CODE ,
p_mmtt_rec.PRIMARY_SWITCH ,
p_mmtt_rec.SCHEDULE_UPDATE_CODE ,
p_mmtt_rec.SETUP_TEARDOWN_CODE ,
p_mmtt_rec.ITEM_ORDERING ,
p_mmtt_rec.NEGATIVE_REQ_FLAG ,
p_mmtt_rec.OPERATION_SEQ_NUM ,
p_mmtt_rec.PICKING_LINE_ID ,
p_mmtt_rec.TRX_SOURCE_LINE_ID ,
p_mmtt_rec.TRX_SOURCE_DELIVERY_ID ,
p_mmtt_rec.PHYSICAL_ADJUSTMENT_ID ,
p_mmtt_rec.CYCLE_COUNT_ID ,
p_mmtt_rec.RMA_LINE_ID ,
p_mmtt_rec.CUSTOMER_SHIP_ID ,
p_mmtt_rec.CURRENCY_CODE ,
p_mmtt_rec.CURRENCY_CONVERSION_RATE ,
p_mmtt_rec.CURRENCY_CONVERSION_TYPE ,
p_mmtt_rec.CURRENCY_CONVERSION_DATE ,
p_mmtt_rec.USSGL_TRANSACTION_CODE ,
p_mmtt_rec.VENDOR_LOT_NUMBER ,
p_mmtt_rec.ENCUMBRANCE_ACCOUNT ,
p_mmtt_rec.ENCUMBRANCE_AMOUNT ,
p_mmtt_rec.SHIP_TO_LOCATION ,
p_mmtt_rec.SHIPMENT_NUMBER ,
p_mmtt_rec.TRANSFER_COST ,
p_mmtt_rec.TRANSPORTATION_COST ,
p_mmtt_rec.TRANSPORTATION_ACCOUNT ,
p_mmtt_rec.FREIGHT_CODE ,
p_mmtt_rec.CONTAINERS ,
p_mmtt_rec.WAYBILL_AIRBILL ,
p_mmtt_rec.EXPECTED_ARRIVAL_DATE ,
p_mmtt_rec.TRANSFER_SUBINVENTORY ,
p_mmtt_rec.TRANSFER_ORGANIZATION ,
p_mmtt_rec.TRANSFER_TO_LOCATION ,
p_mmtt_rec.NEW_AVERAGE_COST ,
p_mmtt_rec.VALUE_CHANGE ,
p_mmtt_rec.PERCENTAGE_CHANGE ,
p_mmtt_rec.MATERIAL_ALLOCATION_TEMP_ID ,
p_mmtt_rec.DEMAND_SOURCE_HEADER_ID ,
p_mmtt_rec.DEMAND_SOURCE_LINE ,
p_mmtt_rec.DEMAND_SOURCE_DELIVERY ,
p_mmtt_rec.ITEM_SEGMENTS ,
p_mmtt_rec.ITEM_DESCRIPTION ,
p_mmtt_rec.ITEM_TRX_ENABLED_FLAG ,
p_mmtt_rec.ITEM_LOCATION_CONTROL_CODE ,
p_mmtt_rec.ITEM_RESTRICT_SUBINV_CODE ,
p_mmtt_rec.ITEM_RESTRICT_LOCATORS_CODE ,
p_mmtt_rec.ITEM_REVISION_QTY_CONTROL_CODE ,
p_mmtt_rec.ITEM_PRIMARY_UOM_CODE ,
p_mmtt_rec.ITEM_UOM_CLASS ,
p_mmtt_rec.ITEM_SHELF_LIFE_CODE ,
p_mmtt_rec.ITEM_SHELF_LIFE_DAYS ,
p_mmtt_rec.ITEM_LOT_CONTROL_CODE ,
p_mmtt_rec.ITEM_SERIAL_CONTROL_CODE ,
p_mmtt_rec.ITEM_INVENTORY_ASSET_FLAG ,
p_mmtt_rec.ALLOWED_UNITS_LOOKUP_CODE ,
p_mmtt_rec.DEPARTMENT_ID ,
p_mmtt_rec.DEPARTMENT_CODE ,
p_mmtt_rec.WIP_SUPPLY_TYPE ,
p_mmtt_rec.SUPPLY_SUBINVENTORY ,
p_mmtt_rec.SUPPLY_LOCATOR_ID ,
p_mmtt_rec.VALID_SUBINVENTORY_FLAG ,
p_mmtt_rec.VALID_LOCATOR_FLAG ,
p_mmtt_rec.LOCATOR_SEGMENTS ,
p_mmtt_rec.CURRENT_LOCATOR_CONTROL_CODE ,
p_mmtt_rec.NUMBER_OF_LOTS_ENTERED ,
p_mmtt_rec.WIP_COMMIT_FLAG ,
p_mmtt_rec.NEXT_LOT_NUMBER ,
p_mmtt_rec.LOT_ALPHA_PREFIX ,
p_mmtt_rec.NEXT_SERIAL_NUMBER ,
p_mmtt_rec.SERIAL_ALPHA_PREFIX ,
p_mmtt_rec.SHIPPABLE_FLAG ,
p_mmtt_rec.POSTING_FLAG ,
p_mmtt_rec.REQUIRED_FLAG ,
p_mmtt_rec.PROCESS_FLAG ,
p_mmtt_rec.ERROR_CODE ,
p_mmtt_rec.ERROR_EXPLANATION ,
p_mmtt_rec.ATTRIBUTE_CATEGORY ,
p_mmtt_rec.ATTRIBUTE1 ,
p_mmtt_rec.ATTRIBUTE2 ,
p_mmtt_rec.ATTRIBUTE3 ,
p_mmtt_rec.ATTRIBUTE4 ,
p_mmtt_rec.ATTRIBUTE5 ,
p_mmtt_rec.ATTRIBUTE6 ,
p_mmtt_rec.ATTRIBUTE7 ,
p_mmtt_rec.ATTRIBUTE8 ,
p_mmtt_rec.ATTRIBUTE9 ,
p_mmtt_rec.ATTRIBUTE10 ,
p_mmtt_rec.ATTRIBUTE11 ,
p_mmtt_rec.ATTRIBUTE12 ,
p_mmtt_rec.ATTRIBUTE13 ,
p_mmtt_rec.ATTRIBUTE14 ,
p_mmtt_rec.ATTRIBUTE15 ,
p_mmtt_rec.MOVEMENT_ID ,
p_mmtt_rec.RESERVATION_QUANTITY ,
p_mmtt_rec.SHIPPED_QUANTITY ,
p_mmtt_rec.TRANSACTION_LINE_NUMBER ,
p_mmtt_rec.TASK_ID ,
p_mmtt_rec.TO_TASK_ID ,
p_mmtt_rec.SOURCE_TASK_ID ,
p_mmtt_rec.PROJECT_ID ,
p_mmtt_rec.SOURCE_PROJECT_ID ,
p_mmtt_rec.PA_EXPENDITURE_ORG_ID ,
p_mmtt_rec.TO_PROJECT_ID ,
p_mmtt_rec.EXPENDITURE_TYPE ,
p_mmtt_rec.FINAL_COMPLETION_FLAG ,
p_mmtt_rec.TRANSFER_PERCENTAGE ,
p_mmtt_rec.TRANSACTION_SEQUENCE_ID ,
p_mmtt_rec.MATERIAL_ACCOUNT ,
p_mmtt_rec.MATERIAL_OVERHEAD_ACCOUNT ,
p_mmtt_rec.RESOURCE_ACCOUNT ,
p_mmtt_rec.OUTSIDE_PROCESSING_ACCOUNT ,
p_mmtt_rec.OVERHEAD_ACCOUNT ,
p_mmtt_rec.FLOW_SCHEDULE ,
p_cost_group_id ,
l_transfer_cost_group_id ,
p_mmtt_rec.DEMAND_CLASS ,
p_mmtt_rec.QA_COLLECTION_ID ,
p_mmtt_rec.KANBAN_CARD_ID ,
p_mmtt_rec.OVERCOMPLETION_TRANSACTION_QTY ,
p_mmtt_rec.OVERCOMPLETION_PRIMARY_QTY ,
p_mmtt_rec.OVERCOMPLETION_TRANSACTION_ID ,
p_mmtt_rec.END_ITEM_UNIT_NUMBER ,
p_mmtt_rec.SCHEDULED_PAYBACK_DATE ,
p_mmtt_rec.LINE_TYPE_CODE ,
p_mmtt_rec.PARENT_TRANSACTION_TEMP_ID ,
p_mmtt_rec.PUT_AWAY_STRATEGY_ID ,
p_mmtt_rec.PUT_AWAY_RULE_ID ,
p_mmtt_rec.PICK_STRATEGY_ID ,
p_mmtt_rec.PICK_RULE_ID ,
p_mmtt_rec.MOVE_ORDER_LINE_ID ,
p_mmtt_rec.TASK_GROUP_ID ,
p_mmtt_rec.PICK_SLIP_NUMBER ,
p_mmtt_rec.RESERVATION_ID ,
p_mmtt_rec.COMMON_BOM_SEQ_ID ,
p_mmtt_rec.COMMON_ROUTING_SEQ_ID ,
p_mmtt_rec.ORG_COST_GROUP_ID ,
p_mmtt_rec.COST_TYPE_ID ,
p_mmtt_rec.TRANSACTION_STATUS ,
p_mmtt_rec.STANDARD_OPERATION_ID ,
p_mmtt_rec.TASK_PRIORITY ,
p_mmtt_rec.WMS_TASK_TYPE ,
p_mmtt_rec.PARENT_LINE_ID ,
p_mmtt_rec.LPN_ID ,
p_mmtt_rec.TRANSFER_LPN_ID ,
p_mmtt_rec.WMS_TASK_STATUS ,
p_mmtt_rec.CONTENT_LPN_ID ,
p_mmtt_rec.CONTAINER_ITEM_ID ,
p_mmtt_rec.CARTONIZATION_ID ,
p_mmtt_rec.PICK_SLIP_DATE ,
p_mmtt_rec.REBUILD_ITEM_ID ,
p_mmtt_rec.REBUILD_SERIAL_NUMBER ,
p_mmtt_rec.REBUILD_ACTIVITY_ID ,
p_mmtt_rec.REBUILD_JOB_NAME ,
p_mmtt_rec.ORGANIZATION_TYPE ,
p_mmtt_rec.TRANSFER_ORGANIZATION_TYPE ,
p_mmtt_rec.OWNING_ORGANIZATION_ID ,
p_mmtt_rec.OWNING_TP_TYPE ,
p_mmtt_rec.XFR_OWNING_ORGANIZATION_ID ,
p_mmtt_rec.TRANSFER_OWNING_TP_TYPE ,
p_mmtt_rec.PLANNING_ORGANIZATION_ID ,
p_mmtt_rec.PLANNING_TP_TYPE ,
p_mmtt_rec.XFR_PLANNING_ORGANIZATION_ID ,
p_mmtt_rec.TRANSFER_PLANNING_TP_TYPE ,
p_mmtt_rec.SECONDARY_UOM_CODE ,
p_mmtt_rec.SECONDARY_TRANSACTION_QUANTITY ,
p_mmtt_rec.TRANSACTION_BATCH_ID ,
p_mmtt_rec.TRANSACTION_BATCH_SEQ ,
p_mmtt_rec.ALLOCATED_LPN_ID ,
p_mmtt_rec.SCHEDULE_NUMBER ,
p_mmtt_rec.SCHEDULED_FLAG ,
p_mmtt_rec.CLASS_CODE ,
p_mmtt_rec.SCHEDULE_GROUP ,
p_mmtt_rec.BUILD_SEQUENCE ,
p_mmtt_rec.BOM_REVISION ,
p_mmtt_rec.ROUTING_REVISION ,
p_mmtt_rec.BOM_REVISION_DATE ,
p_mmtt_rec.ROUTING_REVISION_DATE ,
p_mmtt_rec.ALTERNATE_BOM_DESIGNATOR ,
p_mmtt_rec.ALTERNATE_ROUTING_DESIGNATOR ,
p_mmtt_rec.OPERATION_PLAN_ID ,
p_mmtt_rec.fob_point ,
p_mmtt_rec.intransit_account ,
p_mmtt_rec.relieve_reservations_flag , /*** {{ R12 Enhanced reservations code changes ***/
p_mmtt_rec.relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
) ;
print_debug('proc_insert_mmtt .. comigling occurs : ' );
print_debug('proc_insert_mmtt .. EXCEP G_EXC_ERROR : ' );
print_debug('proc_insert_mmtt .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
print_debug('proc_insert_mmtt .. EXCEP others: ' || SQLERRM(SQLCODE) );
END proc_insert_mmtt;
print_debug('call :proc_update_mmtt :p_transaction_temp_id: ' || p_mmtt_rec.transaction_temp_id);
print_debug('call :proc_update_mmtt :l_cost_group_id: ' || l_cost_group_id);
proc_update_mmtt(p_transaction_temp_id => p_mmtt_rec.transaction_temp_id,
p_transfer_wms_org => p_transfer_wms_org,
p_fob_point => p_fob_point,
p_tfr_primary_cost_method => p_tfr_primary_cost_method,
p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
p_transaction_action_id => p_mmtt_rec.transaction_action_id,
p_transfer_organization => p_mmtt_rec.transfer_organization,
p_transfer_subinventory => p_mmtt_rec.transfer_subinventory,
p_cost_group_id => l_cost_group_id,
p_transfer_cost_group_id => NULL,
p_primary_quantity => NULL,
p_transaction_quantity => NULL,
p_from_project_id => p_from_project_id,
p_to_project_id => p_to_project_id,
x_return_status => x_return_status);
print_debug('proc_update_mmtt return : ' || x_return_status);
update_mmtt BOOLEAN);
l_cg_quantity_table(l_cost_group_id).update_mmtt := TRUE;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_temp_id
FROM dual;
l_cg_quantity_table(l_cost_group_id).update_mmtt := FALSE;
print_debug('proc_process_lots..Inserting records INTO MMTT ');
IF l_cg_quantity_table(i).update_mmtt = FALSE THEN
proc_insert_mmtt(p_mmtt_rec,
p_transfer_wms_org,
p_fob_point,
p_tfr_primary_cost_method,
p_tfr_org_cost_group_id,
i, -- Remember that i is also the cost_group_id of the record
NULL,
l_cg_quantity_table(i).primary_quantity,
l_cg_quantity_table(i).transaction_quantity,
l_cg_quantity_table(i).new_transaction_temp_id,
p_from_project_id,
p_to_project_id,
x_return_status);
print_debug('proc_insert_mmtt return : ' || x_return_status);
proc_update_mmtt(p_mmtt_rec.transaction_temp_id,
p_transfer_wms_org,
p_fob_point,
p_tfr_primary_cost_method,
p_tfr_org_cost_group_id,
p_mmtt_rec.transaction_action_id,
p_mmtt_rec.transfer_organization,
p_mmtt_rec.transfer_subinventory,
i, -- Remember that i is also the cost_group_id of the record
NULL,
l_cg_quantity_table(i).primary_quantity,
l_cg_quantity_table(i).transaction_quantity,
p_from_project_id,
p_to_project_id,
x_return_status);
print_debug('proc_update_mmtt return : ' || x_return_status);
-- Update the MTLT records
IF (l_debug = 1) THEN
print_debug('updating MTLT ');
proc_update_mtlt(l_lots_table(i).mtlt_rowid,
l_cg_quantity_table(l_lots_table(i).cost_group_id).new_transaction_temp_id,
NULL,
NULL,
NULL,
NULL,
x_return_status);
print_debug('proc_update_mtlt return : ' || x_return_status);
update_msnt BOOLEAN);
update_mmtt BOOLEAN);
l_serial_table(l_sti).update_msnt := TRUE;
-- the previous record otherwise insert a new record
IF rec_msn.cost_group_id = l_serial_table(l_sti-1).cost_group_id THEN
l_serial_table(l_sti-1).to_serial_number := rec_msn.serial_number;
l_serial_table(l_sti).update_msnt := FALSE;
l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := TRUE;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_temp_id
FROM dual;
l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := FALSE;
print_debug('proc_process_serial..Inserting records INTO MMTT ');
IF l_cg_quantity_table(i).update_mmtt = FALSE THEN
IF (l_debug = 1) THEN
print_debug('trx_temp_id: ' || l_cg_quantity_table(i).new_transaction_temp_id);
proc_insert_mmtt(p_mmtt_rec,
p_transfer_wms_org,
p_fob_point,
p_tfr_primary_cost_method,
p_tfr_org_cost_group_id,
i, -- Remember that i is also the cost_group_id of the record
NULL,
l_cg_quantity_table(i).quantity * l_quantity_sign,
l_transaction_quantity,
l_cg_quantity_table(i).new_transaction_temp_id,
p_from_project_id,
p_to_project_id,
x_return_status);
print_debug('proc_insert_mmtt return : ' || x_return_status);
proc_update_mmtt(p_mmtt_rec.transaction_temp_id,
p_transfer_wms_org,
p_fob_point,
p_tfr_primary_cost_method,
p_tfr_org_cost_group_id,
p_mmtt_rec.transaction_action_id,
p_mmtt_rec.transfer_organization,
p_mmtt_rec.transfer_subinventory,
i, -- Remember that i is also the cost_group_id of the record
NULL,
l_cg_quantity_table(i).quantity * l_quantity_sign,
l_transaction_quantity,
p_from_project_id,
p_to_project_id,
x_return_status);
print_debug('proc_update_mmtt return : ' || x_return_status);
print_debug('IN proc_process_serial..Inserting records INTO MSNT');
IF l_serial_table(i).update_msnt = TRUE THEN -- Update the MSNT records
IF (l_debug = 1) THEN
print_debug('updating MSNT ');
proc_update_msnt(l_msnt_rowid_table(i),
l_cg_quantity_table(l_serial_table(i).cost_group_id).new_transaction_temp_id,
l_serial_table(i).from_serial_number,
l_serial_table(i).to_serial_number,
x_return_status);
print_debug('proc_update_msnt return : ' || x_return_status);
ELSE -- Insert into MSNT to create new records
IF (l_debug = 1) THEN
print_debug('inserting into MSNT ');
proc_insert_msnt(l_msnt_table(i),
l_serial_table(i).from_serial_number,
l_serial_table(i).to_serial_number,
l_cg_quantity_table(l_serial_table(i).cost_group_id).new_transaction_temp_id,
x_return_status);
print_debug('proc_insert_msnt return : ' || x_return_status);
--update_mtlt BOOLEAN,
update_msnt BOOLEAN);
update_mmtt BOOLEAN);
update_mtlt boolean);
l_lot_table.DELETE;
lot_cg_qty_table.DELETE;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_temp_id
FROM dual;
l_serial_table(l_sti).update_msnt := TRUE;
--l_serial_table(l_sti).update_mtlt := TRUE;
--l_serial_table(l_sti).update_mtlt := FALSE;
-- the previous record otherwise insert a new record
IF rec_msn.cost_group_id = l_serial_table(l_sti-1).cost_group_id THEN
l_serial_table(l_sti-1).to_serial_number := rec_msn.serial_number;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_temp_id
FROM dual;
l_serial_table(l_sti).update_msnt := FALSE;
-- l_serial_table(l_sti).update_mtlt := FALSE;
l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := TRUE;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_temp_id
FROM dual;
l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := FALSE;
l_lot_table(l_lti).update_mtlt := TRUE;
l_lot_table(l_lti).update_mtlt := FALSE;
print_debug('proc_process_lot_serial..Inserting records INTO MMTT ');
IF l_cg_quantity_table(i).update_mmtt = FALSE THEN
proc_insert_mmtt(p_mmtt_rec,
p_transfer_wms_org,
p_fob_point,
p_tfr_primary_cost_method,
p_tfr_org_cost_group_id,
i, -- Remember that i is also the cost_group_id of the record
NULL,
l_cg_quantity_table(i).quantity * l_quantity_sign,
l_transaction_quantity,
l_cg_quantity_table(i).new_transaction_temp_id,
p_from_project_id,
p_to_project_id,
x_return_status);
print_debug('proc_insert_mmtt return : ' || x_return_status);
proc_update_mmtt(p_mmtt_rec.transaction_temp_id,
p_transfer_wms_org,
p_fob_point,
p_tfr_primary_cost_method,
p_tfr_org_cost_group_id,
p_mmtt_rec.transaction_action_id,
p_mmtt_rec.transfer_organization,
p_mmtt_rec.transfer_subinventory,
i, -- Remember that i is also the cost_group_id of the record
NULL,
l_cg_quantity_table(i).quantity * l_quantity_sign,
l_transaction_quantity,
p_from_project_id,
p_to_project_id,
x_return_status);
print_debug('proc_update_mmtt return : ' || x_return_status);
print_debug('IN proc_process_lot_serial..Inserting records INTO MTLT');
IF l_lot_table(lot_i).update_mtlt THEN
proc_update_mtlt(l_lot_table(lot_i).mtlt_rowid,
l_cg_quantity_table(l_lot_table(lot_i).cost_group_id).new_transaction_temp_id,
l_lot_table(lot_i).lot_number,
l_lot_table(lot_i).quantity,
l_transaction_quantity,
l_lot_table(lot_i).serial_transaction_temp_id,
x_return_status);
proc_insert_mtlt(l_mtlt_table(lot_i),
l_cg_quantity_table(l_lot_table(lot_i).cost_group_id).new_transaction_temp_id,
l_lot_table(lot_i).quantity,
l_transaction_quantity,
l_lot_table(lot_i).serial_transaction_temp_id,
x_return_status);
print_debug('IN proc_process_lot_serial..Inserting records INTO MSNT');
IF l_serial_table(i).update_msnt = TRUE THEN -- Update the MSNT records
IF (l_debug = 1) THEN
print_debug('updating MSNT ');
proc_update_msnt(l_msnt_rowid_table(i),
l_serial_table(i).new_serial_transaction_temp_id,
l_serial_table(i).from_serial_number,
l_serial_table(i).to_serial_number,
x_return_status);
print_debug('proc_update_msnt return : ' || x_return_status);
ELSE -- Insert into MSNT to create new records
IF (l_debug = 1) THEN
print_debug('inserting into MSNT ');
proc_insert_msnt(l_msnt_table(i),
l_serial_table(i).from_serial_number,
l_serial_table(i).to_serial_number,
l_serial_table(i).new_serial_transaction_temp_id,
x_return_status);
print_debug('proc_insert_msnt return : ' || x_return_status);
PROCEDURE cost_group_update
(p_transaction_rec IN mtl_material_transactions_temp%ROWTYPE,
p_fob_point IN mtl_interorg_parameters.fob_point%TYPE DEFAULT NULL,
p_transfer_wms_org IN BOOLEAN DEFAULT TRUE,
p_tfr_primary_cost_method IN NUMBER,
p_tfr_org_cost_group_id IN NUMBER,
p_from_project_id IN NUMBER DEFAULT NULL,
p_to_project_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_api_name CONSTANT VARCHAR2(50) := 'cost_group_update';
SAVEPOINT API_updatecostgroups;
SELECT *
INTO l_mmtt_rec
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_mmtt_rec.transaction_temp_id;
SELECT lot_control_code, serial_number_control_code, primary_uom_code
INTO l_mmtt_rec.item_lot_control_code,
l_mmtt_rec.item_serial_control_code, l_mmtt_rec.item_primary_uom_code
FROM mtl_system_items
WHERE organization_id = l_mmtt_rec.organization_id
AND inventory_item_id = l_mmtt_rec.inventory_item_id;
proc_update_mmtt(l_mmtt_rec.transaction_temp_id,
p_transfer_wms_org,
p_fob_point,
p_tfr_primary_cost_method,
p_tfr_org_cost_group_id,
l_mmtt_rec.transaction_action_id,
l_mmtt_rec.transfer_organization,
l_mmtt_rec.transfer_subinventory,
l_cost_group_id,
NULL,
NULL,
NULL,
p_from_project_id,
p_to_project_id,
x_return_status);
print_debug('proc_update_mmtt : ' || x_return_status);
SELECT lot_number
INTO l_lot_number
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_mmtt_rec.transaction_temp_id;
proc_update_mmtt(l_mmtt_rec.transaction_temp_id,
p_transfer_wms_org,
p_fob_point,
p_tfr_primary_cost_method,
p_tfr_org_cost_group_id,
l_mmtt_rec.transaction_action_id,
l_mmtt_rec.transfer_organization,
l_mmtt_rec.transfer_subinventory,
l_cost_group_id,
NULL,
NULL,
NULL,
p_from_project_id,
p_to_project_id,
x_return_status);
print_debug('proc_update_mmtt : ' || x_return_status);
-- Mrana inv_cost_group_update.proc_process_serials
proc_process_serials
(p_mmtt_rec => l_mmtt_rec,
p_fob_point => p_fob_point,
p_transfer_wms_org => p_transfer_wms_org,
p_tfr_primary_cost_method => p_tfr_primary_cost_method,
p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
p_from_project_id => p_from_project_id,
p_to_project_id => p_to_project_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('inv_cost_group_update .. EXCEP G_EXC_ERROR : ' );
ROLLBACK TO API_updatecostgroups;
print_debug('inv_cost_group_update .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
ROLLBACK TO API_updatecostgroups;
print_debug('inv_cost_group_update .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
ROLLBACK TO API_updatecostgroups;
END cost_group_update;