[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * INTO l_opm_lot
FROM ic_lots_mst
WHERE item_id = p_item_id and
lot_id = p_lot_id;
SELECT l_opm_lot.lot_no || DECODE (l_opm_lot.sublot_no, NULL, NULL,
(SELECT lot_sublot_delimiter FROM gmi_migration_parameters)) ||
l_opm_lot.sublot_no,
DECODE(sublot_ctl, 1, DECODE(l_opm_lot.sublot_no, NULL, NULL,
l_opm_lot.lot_no)),
status_ctl, lot_status
INTO x_lot_number, x_parent_lot_number, l_status_ctl, l_default_status
FROM ic_item_mst_b
WHERE
item_id = p_item_id;
SELECT lot_status
INTO l_lot_status
FROM ic_loct_inv
WHERE
item_id = p_item_id and
lot_id = p_lot_id and
whse_code = p_whse_code and
rownum = 1;
-- insert it into ic_lots_mst_mig table
IF (l_lot_status is NULL) THEN
l_lot_status := l_default_status;
INSERT into ic_lots_mst_mig (
item_id,
lot_id,
organization_id,
whse_code,
location,
status,
parent_lot_number,
lot_number,
migrated_ind,
additional_status_lot,
user_updated_ind,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (
p_item_id,
p_lot_id,
p_organization_id,
p_whse_code,
p_location, --rlnagara 2 Material Status Migration ME - dont know why NULL was passed here even though we had location value.
l_lot_status, --rlnagara 2 Material Status Migration ME - dont know why NULL was passed here even though we had lot status value.
x_parent_lot_number,
x_lot_number,
0,
0,
-1,
sysdate,
0,
sysdate,
0,
NULL);
SELECT ic_matr_date, ic_hold_date
INTO l_maturity_date, l_hold_date
FROM ic_lots_cpg
WHERE
item_id = p_item_id AND
lot_id = p_lot_id;
SELECT status_id
INTO l_status_id
FROM ic_lots_sts
WHERE
lot_status = l_lot_status;
IF (l_opm_lot.inactive_ind = 1 or l_opm_lot.delete_mark = 1) THEN
l_lot_rec.DISABLE_FLAG := 1;
l_lot_rec.PROGRAM_UPDATE_DATE := NULL;
l_lot_rec.LAST_UPDATE_DATE := SYSDATE;
l_lot_rec.LAST_UPDATED_BY := l_opm_lot.last_updated_by;
l_lot_rec.LAST_UPDATE_LOGIN := -1;
SELECT count(*)
INTO l_count
FROM mtl_lot_numbers
WHERE
organization_id = p_organization_id AND
inventory_item_id = l_inventory_item_id AND
lot_number = x_lot_number;
SELECT orgn_code INTO l_whse_orgn_code
FROM ic_whse_mst
WHERE whse_code = p_whse_code;
UPDATE ic_lots_mst_mig
SET
organization_id = p_organization_id,
migrated_ind = 1,
last_update_date = sysdate,
last_updated_by = 0
WHERE
( organization_id = p_organization_id OR
(organization_id IS NULL AND -- whse mapped to subinventory
whse_mapping_code = l_whse_orgn_code) OR
(organization_id IS NULL AND nvl(whse_mapping_code, ' ') <> l_whse_orgn_code AND
whse_code = p_whse_code)) AND -- whse not a subinventory
lot_number = g_lot_number and
item_id = p_item_id and -- Added this to use index.
lot_id = p_lot_id; -- Added this to use index.
SELECT col.descriptive_flex_context_code,
col.application_column_name,
col.end_user_column_name
FROM fnd_descr_flex_column_usages col,
fnd_descr_flex_contexts cont
WHERE
col.application_id = 551 and
col.descriptive_flexfield_name = 'LOTS_FLEX' and
col.enabled_flag = 'Y' and
col.application_id = cont.application_id and
col.descriptive_flexfield_name = cont.descriptive_flexfield_name and
col.descriptive_flex_context_code = cont.descriptive_flex_context_code and
cont.enabled_flag = 'Y' and
col.application_column_name in (
SELECT col2.application_column_name
FROM fnd_descr_flex_column_usages col2,
fnd_descr_flex_contexts cont2
WHERE
col2.application_id = 401 and
col2.descriptive_flexfield_name = 'MTL_LOT_NUMBERS' and
col2.enabled_flag = 'Y' and
col.application_id = cont2.application_id and
col.descriptive_flexfield_name = cont2.descriptive_flexfield_name and
col.descriptive_flex_context_code = cont2.descriptive_flex_context_code and
cont2.enabled_flag = 'Y' );
SELECT col.descriptive_flex_context_code,
col.application_column_name,
col.end_user_column_name
FROM fnd_descr_flex_column_usages col,
fnd_descr_flex_contexts cont
WHERE
col.application_id = 551 and
col.descriptive_flexfield_name = 'LOTS_FLEX' and
col.enabled_flag = 'Y' and
col.application_id = cont.application_id and
col.descriptive_flexfield_name = cont.descriptive_flexfield_name and
col.descriptive_flex_context_code = cont.descriptive_flex_context_code and
cont.enabled_flag = 'Y';
SELECT cont.descriptive_flex_context_code
INTO l_opm_context
FROM fnd_descr_flex_contexts cont
WHERE cont.application_id = 551 and
cont.descriptive_flexfield_name = 'LOTS_FLEX' and
cont.enabled_flag = 'Y' and
cont.global_flag = 'N' and
rownum = 1;
SELECT cont.descriptive_flex_context_code
INTO l_odm_context
FROM fnd_descr_flex_contexts cont
WHERE cont.application_id = 401 and
cont.descriptive_flexfield_name = 'MTL_LOT_NUMBERS' and
cont.enabled_flag = 'Y' and
cont.global_flag = 'N' and
rownum = 1;
SELECT i.item_id
INTO l_item_id
FROM ic_item_mst_b i, mtl_system_items_b d
WHERE
d.organization_id = p_organization_id AND
d.inventory_item_id = p_inventory_item_id AND
d.segment1 = i.item_no;
SELECT lot_id
INTO l_lot_id
FROM ic_lots_mst
WHERE
item_id = l_item_id AND
lot_no = p_lot_no AND
nvl(sublot_no, ' ') = nvl(p_sublot_no, ' ');
SELECT whse_code
INTO l_whse_code
FROM ic_whse_mst
WHERE
organization_id = p_organization_id AND
migrated_ind = 1 AND
ROWNUM = 1;
SELECT l.whse_code, l.location, w.organization_id
INTO l_location_whse, l_location, l_loc_organization_id
FROM ic_loct_mst l, ic_whse_mst w
WHERE
inventory_location_id = p_locator_id AND
l.whse_code = w.whse_code;
SELECT lot_id
INTO l_lot_id
FROM ic_lots_mst
WHERE
item_id = p_item_id AND
lot_no = p_lot_no AND
nvl(sublot_no, ' ') = nvl(p_sublot_no, ' ');
SELECT count(*)
INTO l_count
FROM ic_lots_mst_mig
WHERE
item_id = p_item_id AND
whse_code = p_whse_code AND
parent_lot_number = p_lot_no AND
migrated_ind = 1;
SELECT organization_id, migrated_ind
INTO l_organization_id, l_whse_migrated_ind
FROM ic_whse_mst
WHERE
whse_code = p_whse_code;
SELECT organization_id, migrated_ind
INTO l_organization_id, l_orgn_migrated_ind
FROM sy_orgn_mst_b
WHERE
orgn_code = p_orgn_code;
SELECT lot_number, parent_lot_number, status, nvl(migrated_ind, 0)
INTO g_lot_number, g_parent_lot_number, l_lot_status, l_migrated_ind
FROM ic_lots_mst_mig
WHERE
item_id = g_item_id AND
lot_id = g_lot_id AND
whse_code = g_whse_code AND -- only OPM whse
location = g_location AND
ROWNUM = 1;
SELECT lot_number, parent_lot_number, status, migrated_ind
INTO g_lot_number, g_parent_lot_number, l_lot_status, l_migrated_ind
FROM ic_lots_mst_mig
WHERE
item_id = g_item_id AND
lot_id = g_lot_id AND
organization_id = g_organization_id AND -- for OPM whse or orgn
additional_status_lot = 0 AND
ROWNUM = 1;