[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ACCT_PERIOD_ID, TRUNC(SCHEDULE_CLOSE_DATE)
INTO v_transaction_period_id,v_scheduled_close_date
FROM ORG_ACCT_PERIODS
WHERE PERIOD_CLOSE_DATE IS NULL
AND ORGANIZATION_ID = org_id
AND TRUNC(SCHEDULE_CLOSE_DATE) >=
TRUNC(INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Nvl(transaction_date,Sysdate),org_id))
AND TRUNC(PERIOD_START_DATE) <=
TRUNC(INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Nvl(transaction_date,Sysdate),org_id));
/* Check to see if the selected period id falls within the current
period or is in a past period.
*/
begin
if (open_past_period) then
if( l_debug = 1 ) then
inv_log_util.trace('open_past_period is true', 'tdatechk', 9);
SELECT ACCT_PERIOD_ID
INTO v_current_period_id
FROM ORG_ACCT_PERIODS
WHERE PERIOD_CLOSE_DATE IS NULL
AND ORGANIZATION_ID = org_id
AND TRUNC(INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,org_id))
BETWEEN TRUNC(PERIOD_START_DATE) and
TRUNC(SCHEDULE_CLOSE_DATE);
/* Bug:5154903. For the following two select statements added
conditions to check transaction_type_id and transaction_action_id
to query for only Transaction Type Inter-Org Transfer*/
SELECT shipment_number
INTO found_row
FROM mtl_transactions_interface m
WHERE m.shipment_number = vall
AND m.transaction_type_id = INV_GLOBALS.G_SOURCETYPE_INVENTORY
AND m.transaction_action_id= INV_GLOBALS.G_ACTION_INTRANSITSHIPMENT
AND ROWNUM = 1 ;
SELECT shipment_number
INTO found_row
FROM mtl_material_transactions_temp m
WHERE m.shipment_number = vall
AND m.transaction_type_id = INV_GLOBALS.G_SOURCETYPE_INVENTORY
AND m.transaction_action_id= INV_GLOBALS.G_ACTION_INTRANSITSHIPMENT
AND ROWNUM = 1 ;
SELECT shipment_num
INTO found_row
FROM rcv_shipment_headers m
WHERE m.shipment_num = vall
AND m.receipt_source_code = 'INVENTORY'
AND ROWNUM = 1 ;
select quantity_tracked,
asset_inventory,
locator_type,
material_account
from mtl_subinventories_all_v
where organization_id = c_org_id
and secondary_inventory_name = c_subinventory;
select meaning
from mfg_lookups
where lookup_type = 'WIP_TRANSACTION_DIRECTION'
and lookup_code = decode(c_transaction_action_id,
1, 1 /* return */,
2 /* otherwise, issue */);
select reason_name
from mtl_transaction_reasons
where reason_id = c_reason_id;
cursor c1 is select group_mark_id from
mtl_serial_numbers
where group_mark_id = v_trx_header_id
and current_status = 6
for update of group_mark_id nowait;
delete mtl_serial_numbers
where group_mark_id = v_trx_header_id
and current_status = 6;
cursor c2 is select group_mark_id from
mtl_serial_numbers
where group_mark_id = v_trx_header_id
for update of group_mark_id nowait;
update mtl_serial_numbers
set group_mark_id = null,
line_mark_id = null,
lot_line_mark_id = null
where group_mark_id = v_trx_header_id;
cursor c3 is select group_header_id from
mtl_serial_numbers_temp
where group_header_id = v_trx_header_id
for update of group_header_id nowait;
delete mtl_serial_numbers_temp
where group_header_id = v_trx_header_id;
cursor c4 is select group_header_id from
mtl_transaction_lots_temp
where group_header_id = v_trx_header_id
for update of group_header_id nowait;
delete mtl_transaction_lots_temp
where group_header_id = v_trx_header_id;
delete mtl_material_transactions_temp
where transaction_header_id = trx_header_id;
DELETE FROM mtl_transaction_lots_temp
WHERE group_header_id = hdr_id AND
transaction_temp_id NOT IN
(SELECT mmtt.transaction_temp_id FROM
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = hdr_id AND mmtt.transaction_temp_id
IS NOT NULL AND mmtt.transaction_header_id IS NOT NULL);
DELETE FROM mtl_serial_numbers_temp
WHERE group_header_id = hdr_id AND
transaction_temp_id NOT IN
(SELECT mmtt.transaction_temp_id FROM
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = hdr_id AND mmtt.transaction_temp_id
IS NOT NULL) AND transaction_temp_id NOT IN
( SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.group_header_id = hdr_id
AND mtlt.serial_transaction_temp_id IS NOT NULL);
select mmtt.transaction_action_id, mmtt.transaction_source_type_id,
mmtt.organization_id, mmtt.inventory_item_id,
mmtt.transaction_date, mmtt.lot_expiration_date
into l_transaction_Action_id, l_transaction_source_type_id,
l_organization_id, l_inventory_item_id,
l_transaction_date, l_expiration_date
from mtl_material_transactions_temp mmtt
where mmtt.transaction_header_id = hdr_id
AND mmtt.lot_number IS NOT NULL
AND mmtt.transaction_header_id IS NOT NULL
AND rownum = 1;
/* 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, group_header_id,
serial_transaction_temp_id, status_id)
(select mmtt.transaction_temp_id, mmtt.last_update_date,
mmtt.last_updated_by, mmtt.creation_date,
mmtt.created_by, mmtt.last_update_login, mmtt.request_id,
mmtt.program_application_id,
mmtt.program_id, mmtt.program_update_date,
mmtt.transaction_quantity, mmtt.primary_quantity, mmtt.lot_number,
mmtt.lot_expiration_date, mmtt.transaction_header_id,
mmtt.transaction_temp_id, msi.default_lot_status_id
FROM mtl_material_transactions_temp mmtt,
mtl_system_items msi
WHERE mmtt.transaction_header_id = hdr_id AND mmtt.lot_number IS
NOT NULL AND mmtt.transaction_header_id IS NOT NULL
AND msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id) ; */
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, group_header_id,
serial_transaction_temp_id, status_id
, lot_attribute_category
, 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
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, grade_code
, origination_date
, date_code
, change_date
, age
, retest_date
, maturity_date
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, length
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, territory_code
, supplier_lot_number
, vendor_name
/* INVCONV Anand Thiyagarajan 22-Oct-2004 Start */
, secondary_quantity
, parent_lot_number
, origination_type
, expiration_action_code
, expiration_action_date
, hold_date
, reason_id
/* INVCONV Anand Thiyagarajan 22-Oct-2004 End */
)
(select mmtt.transaction_temp_id, mmtt.last_update_date,
mmtt.last_updated_by, mmtt.creation_date,
mmtt.created_by, mmtt.last_update_login, mmtt.request_id,
mmtt.program_application_id,
mmtt.program_id, mmtt.program_update_date,
mmtt.transaction_quantity, mmtt.primary_quantity, mmtt.lot_number,
nvl(mmtt.lot_expiration_date,decode(msi.shelf_life_code,2,NVL(mln.origination_date, mmtt.transaction_date) + shelf_life_days,null)), /* Jalaj Srivastava Bug 5527373*/
mmtt.transaction_header_id,
mmtt.transaction_temp_id, NVL(mln.status_id, msi.default_lot_status_id)
,mln.lot_attribute_category
,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.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
,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
, nvl(mln.grade_code, decode(msi.grade_control_flag,'Y',msi.default_grade,null)) /* Jalaj Srivastava Bug 5527373*/ /* INVCONV Anand Thiyagarajan 22-Oct-2004 Start */
, NVL(mln.origination_date, mmtt.transaction_date) /* Jalaj Srivastava Bug 5527373*/
, mln.date_code
, mln.change_date
, mln.age
, nvl(mln.retest_date, NVL(mln.origination_date, mmtt.transaction_date) + msi.retest_interval) /* Jalaj Srivastava Bug 5527373*/
, nvl(mln.maturity_date, NVL(mln.origination_date, mmtt.transaction_date) + msi.maturity_days) /* Jalaj Srivastava Bug 5527373*/
, mln.item_size
, mln.color
, mln.volume
, mln.volume_uom
, mln.place_of_origin
, mln.best_by_date
, mln.length
, mln.length_uom
, mln.recycled_content
, mln.thickness
, mln.thickness_uom
, mln.width
, mln.width_uom
, mln.territory_code
, mln.supplier_lot_number
, mln.vendor_name
/* INVCONV Anand Thiyagarajan 22-Oct-2004 Start */
, mmtt.secondary_transaction_quantity
, mln.parent_lot_number
, NVL(mln.origination_type, decode(mmtt.transaction_source_type_id,1,3,7,3,13,4,6,4,12,4,31,1,6)) /* Jalaj Srivastava Bug 5527373*/
, NVL(mln.expiration_action_code, decode(msi.shelf_life_code,1,null,msi.expiration_action_code)) /* Jalaj Srivastava Bug 5527373*/
, NVL(mln.expiration_action_date,
decode(msi.shelf_life_code,1,null,nvl(mmtt.lot_expiration_date,
decode(msi.shelf_life_code,2,NVL(mln.origination_date, mmtt.transaction_date) + shelf_life_days,null)) + msi.expiration_action_interval)) /* Jalaj Srivastava Bug 5527373*/
, NVL(mln.hold_date, NVL(mln.origination_date, mmtt.transaction_date) + hold_days) /* Jalaj Srivastava Bug 5527373*/
, mmtt.reason_id
/* INVCONV Anand Thiyagarajan 22-Oct-2004 End */
FROM mtl_material_transactions_temp mmtt,
mtl_system_items msi,
mtl_lot_numbers mln
WHERE mmtt.transaction_header_id = hdr_id AND mmtt.lot_number IS
NOT NULL AND mmtt.transaction_header_id IS NOT NULL
AND msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id
and mln.inventory_item_id(+) = mmtt.inventory_item_id
and mln.organization_id(+) = mmtt.organization_id
and mln.lot_number(+) =mmtt.lot_number);
UPDATE mtl_transaction_lots_temp
SET primary_quantity = -1 * primary_quantity ,
transaction_quantity = -1 * transaction_quantity ,
secondary_quantity = -1 * secondary_quantity /* INVCONV Anand Thiyagarajan 22-Oct-2004*/
WHERE transaction_temp_id in
(select mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = hdr_id AND mmtt.lot_number IS
NOT NULL AND mmtt.transaction_header_id IS NOT NULL)
AND ( primary_quantity < 0 OR transaction_quantity < 0 OR secondary_quantity < 0); /* INVCONV Anand Thiyagarajan 22-Oct-2004 */
UPDATE mtl_material_transactions_temp
SET lot_number = NULL, lot_expiration_date = NULL
WHERE transaction_header_id = hdr_id AND process_flag = 'Y';
DELETE /*+ INDEX(MSN MTL_SERIAL_NUMBERS_N2) */
FROM mtl_serial_numbers MSN
WHERE current_status = 6
AND group_mark_id = -1
AND (MSN.inventory_item_id, MSN.current_organization_id) in
(select inventory_item_id,ORGANIZATION_ID
FROM mtl_material_transactions_temp
WHERE transaction_header_id = hdr_id);