[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE MTL_LOTATT_SYNC_REPORT_TEMP WHERE group_id=P_CONC_REQUEST_ID;
SELECT organization_code
into P_SRC_ORG_CODE
FROM mtl_parameters
WHERE organization_id = P_SOURCE_ORG;
all available lot numbers will to be updated.*/
IF (nvl(P_ITEM_LO,'@@X@@') <> nvl(P_ITEM_HI,'@@X@@')) THEN
P_LOT_LO := null;
select organization_code
into P_DEST_ORG_CODE_LO
from mtl_parameters
where organization_id = P_DEST_ORG_LO;
select organization_code
into P_DEST_ORG_CODE_HI
from mtl_parameters
where organization_id = P_DEST_ORG_HI;
INSERT INTO MTL_LOTATT_SYNC_REPORT_TEMP
(organization_id,
inventory_item_id,
group_id,
lot_number,
grade_code,
origination_date,
expiration_date,
maturity_date,
expiration_action_date,
expiration_action_code,
hold_date,
retest_date,
flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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)
SELECT organization_id,
inventory_item_id,
P_CONC_REQUEST_ID,--using request id as groupid
lot_number,
grade_code,
origination_date,
expiration_date,
maturity_date,
expiration_action_date,
expiration_action_code,
hold_date,
retest_date,
1,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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_lot_numbers mln
WHERE organization_id=P_SOURCE_ORG
AND mln.lot_number >= nvl(P_LOT_LO,mln.lot_number)
AND mln.lot_number <= nvl(P_LOT_HI,mln.lot_number)
/*Item should be lot controlled,
Lot should exists in source and destination */
AND inventory_item_id in(select inventory_item_id
from mtl_system_items_b
where (organization_id = P_SOURCE_ORG)
and (segment1 >= nvl(P_ITEM_LO,segment1) and segment1<=nvl(P_ITEM_HI,segment1))
and (lot_control_code = 2))
AND exists(select 'x' from mtl_lot_numbers mln1,mtl_parameters mp
where mln1.organization_id = mp.organization_id
and mp.organization_id <> P_SOURCE_ORG
and mp.organization_code >= P_DEST_ORG_CODE_LO
and mp.organization_code <= nvl(P_DEST_ORG_CODE_HI,mp.organization_code)
and mln1.lot_number = mln.lot_number
and mln1.inventory_item_id = mln.inventory_item_id)
/*Skip the lot if pending transaction exists */
AND not exists(select 'x'
from mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
where mmtt.transaction_temp_id = mtlt.transaction_temp_id
and mmtt.organization_id = mln.organization_id
and mmtt.inventory_item_id = mln.inventory_item_id
and mtlt.lot_number = mln.lot_number)
AND not exists(select 'x'
from mtl_transactions_interface mti,
mtl_transaction_lots_interface mtli
where mti.transaction_interface_id = mtli.transaction_interface_id
and mti.organization_id = mln.organization_id
and mti.inventory_item_id = mln.inventory_item_id
and mtli.lot_number = mln.lot_number)
/*Skip the lot if reservation exists */
AND not exists(select 'x' from mtl_reservations mr
where mr.organization_id = mln.organization_id
and mr.inventory_item_id = mln.inventory_item_id
and mr.lot_number = mln.lot_number);
INSERT INTO MTL_LOTATT_SYNC_REPORT_TEMP
(organization_id,
inventory_item_id,
group_id,
lot_number,
grade_code,
origination_date,
expiration_date,
maturity_date,
expiration_action_date,
expiration_action_code,
hold_date,
retest_date,
flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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)
SELECT mln.organization_id,
mln.inventory_item_id,
P_CONC_REQUEST_ID,--using request id as group id
mln.lot_number,
mln.grade_code,
mln.origination_date,
mln.expiration_date,
mln.maturity_date,
mln.expiration_action_date,
mln.expiration_action_code,
mln.hold_date,
mln.retest_date,
2,
mln.attribute_category,
mln.attribute1,
mln.attribute2,
mln.attribute3,
mln.attribute4,
mln.attribute5,
mln.attribute6,
mln.attribute7,
mln.attribute8,
mln.attribute9,
mln.attribute10,
mln.attribute11,
mln.attribute12,
mln.attribute13,
mln.attribute14,
mln.attribute15,
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,mtl_parameters mp
WHERE mln.organization_id = mp.organization_id
AND mp.organization_id <> P_SOURCE_ORG
AND mp.organization_code >= P_DEST_ORG_CODE_LO
AND mp.organization_code <= nvl(P_DEST_ORG_CODE_HI,mp.organization_code)
AND mln.lot_number >= nvl(P_LOT_LO,mln.lot_number)
AND mln.lot_number <= nvl(P_LOT_HI,mln.lot_number)
/*Item should be lot controlled,
Lot should exists in source and destination */
AND inventory_item_id in(select inventory_item_id
from mtl_system_items_b msib,mtl_parameters mp
where msib.organization_id = mp.organization_id
and mp.organization_id <> P_SOURCE_ORG
and mp.organization_code >= P_DEST_ORG_CODE_LO
and mp.organization_code <= nvl(P_DEST_ORG_CODE_HI,mp.organization_code)
and (msib.segment1 >= nvl(P_ITEM_LO,msib.segment1) and msib.segment1<=nvl(P_ITEM_HI,msib.segment1))
and msib.lot_control_code = 2)
AND exists(select 'x' from mtl_lot_numbers mln1
where mln1.organization_id = P_SOURCE_ORG
and mln1.lot_number = mln.lot_number
and mln1.inventory_item_id = mln.inventory_item_id)
/*Skip the lot if pending transaction exists */
AND not exists(select 'x'
from mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
where mmtt.transaction_temp_id = mtlt.transaction_temp_id
and mmtt.organization_id = mln.organization_id
and mmtt.inventory_item_id = mln.inventory_item_id
and mtlt.lot_number = mln.lot_number)
AND not exists(select 'x'
from mtl_transactions_interface mti,
mtl_transaction_lots_interface mtli
where mti.transaction_interface_id = mtli.transaction_interface_id
and mti.organization_id = mln.organization_id
and mti.inventory_item_id = mln.inventory_item_id
and mtli.lot_number = mln.lot_number)
/*Skip the lot if reservation exists */
AND not exists(select 'x' from mtl_reservations mr
where mr.organization_id = mln.organization_id
and mr.inventory_item_id = mln.inventory_item_id
and mr.lot_number = mln.lot_number);