The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_UPDATE_FLAG VARCHAR2(1) := 'N';
procedure INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_STATUS_ID in NUMBER,
X_ATTRIBUTE15 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_LOCATOR_CONTROL in NUMBER,
X_LOT_CONTROL in NUMBER,
X_SERIAL_CONTROL in NUMBER,
X_ONHAND_CONTROL in NUMBER, -- Onhand Material Status Support Bug #6633612
X_ZONE_CONTROL in NUMBER,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_REQUEST_ID in NUMBER,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ENABLED_FLAG in NUMBER,
X_STATUS_CODE in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_LPN_CONTROL in NUMBER,
--INVCONV KKILLAMS
X_inventory_atp_code IN NUMBER,
X_reservable_type IN NUMBER,
X_availability_type IN NUMBER
--END INVCONV KKILLAMS
) is
cursor C is select ROWID from MTL_MATERIAL_STATUSES_B
where STATUS_ID = X_STATUS_ID
;
insert into MTL_MATERIAL_STATUSES_B (
ATTRIBUTE15,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
LOCATOR_CONTROL,
LOT_CONTROL,
SERIAL_CONTROL,
ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
STATUS_ID,
ZONE_CONTROL,
ATTRIBUTE1,
ATTRIBUTE14,
REQUEST_ID,
ATTRIBUTE_CATEGORY,
ENABLED_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LPN_CONTROL,
--INVCONV KKILLAMS
INVENTORY_ATP_CODE,
RESERVABLE_TYPE,
AVAILABILITY_TYPE
--END INVCONV KKILLAMS
) values (
X_ATTRIBUTE15,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_LOCATOR_CONTROL,
X_LOT_CONTROL,
X_SERIAL_CONTROL,
X_ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
X_STATUS_ID,
X_ZONE_CONTROL,
X_ATTRIBUTE1,
X_ATTRIBUTE14,
X_REQUEST_ID,
X_ATTRIBUTE_CATEGORY,
X_ENABLED_FLAG,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_LPN_CONTROL,
--INVCONV KKILLAMS
X_INVENTORY_ATP_CODE,
X_RESERVABLE_TYPE,
X_AVAILABILITY_TYPE
--END INVCONV KKILLAMS
);
insert into MTL_MATERIAL_STATUSES_TL (
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
STATUS_CODE,
DESCRIPTION,
STATUS_ID,
LAST_UPDATED_BY,
LANGUAGE,
SOURCE_LANG
) select
X_LAST_UPDATE_DATE,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATE_LOGIN,
X_STATUS_CODE,
X_DESCRIPTION,
X_STATUS_ID,
X_LAST_UPDATED_BY,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from MTL_MATERIAL_STATUSES_TL T
where T.STATUS_ID = X_STATUS_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
procedure INSERT_TL_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_STATUS_ID in NUMBER,
X_STATUS_CODE in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER)
IS
cursor C is select ROWID from MTL_MATERIAL_STATUSES_TL
where STATUS_ID = X_STATUS_ID
;
insert into MTL_MATERIAL_STATUSES_TL (
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
STATUS_CODE,
DESCRIPTION,
STATUS_ID,
LAST_UPDATED_BY,
LANGUAGE,
SOURCE_LANG
) select
X_LAST_UPDATE_DATE,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATE_LOGIN,
X_STATUS_CODE,
X_DESCRIPTION,
X_STATUS_ID,
X_LAST_UPDATED_BY,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from MTL_MATERIAL_STATUSES_TL T
where T.STATUS_ID = X_STATUS_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_TL_ROW;
cursor c is select
ATTRIBUTE15,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
LOCATOR_CONTROL,
LOT_CONTROL,
SERIAL_CONTROL,
ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
ZONE_CONTROL,
ATTRIBUTE1,
ATTRIBUTE14,
REQUEST_ID,
ATTRIBUTE_CATEGORY,
ENABLED_FLAG,
LPN_CONTROL,
--INVCONV KKILLAMS
INVENTORY_ATP_CODE,
RESERVABLE_TYPE,
AVAILABILITY_TYPE
--END INVCONV KKILLAMS
from MTL_MATERIAL_STATUSES_B
where STATUS_ID = X_STATUS_ID
for update of STATUS_ID nowait;
cursor c1 is select
STATUS_CODE,
DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from MTL_MATERIAL_STATUSES_TL
where STATUS_ID = X_STATUS_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of STATUS_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_STATUS_ID in NUMBER,
X_ATTRIBUTE15 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_LOCATOR_CONTROL in NUMBER,
X_LOT_CONTROL in NUMBER,
X_SERIAL_CONTROL in NUMBER,
X_ONHAND_CONTROL in NUMBER, -- Onhand Material Status Support Bug #6633612
X_ZONE_CONTROL in NUMBER,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_REQUEST_ID in NUMBER,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ENABLED_FLAG in NUMBER,
X_STATUS_CODE in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_LPN_CONTROL in NUMBER,
--INVCONV KKILLAMS
X_INVENTORY_ATP_CODE IN NUMBER,
X_RESERVABLE_TYPE IN NUMBER,
X_AVAILABILITY_TYPE IN NUMBER
--END INVCONV KKILLAMS
) is
--INVCONV KKILLAMS
--Cursor is to verify the atp,reservable and available flags are getting modified or not.
CURSOR cur_status IS SELECT 1 FROM MTL_MATERIAL_STATUSES_B
WHERE STATUS_ID = X_STATUS_ID
AND (INVENTORY_ATP_CODE <> X_INVENTORY_ATP_CODE
OR RESERVABLE_TYPE <> X_RESERVABLE_TYPE
OR AVAILABILITY_TYPE <> X_AVAILABILITY_TYPE );
update MTL_MATERIAL_STATUSES_B set
ATTRIBUTE15 = X_ATTRIBUTE15,
ATTRIBUTE2 = X_ATTRIBUTE2,
ATTRIBUTE3 = X_ATTRIBUTE3,
ATTRIBUTE4 = X_ATTRIBUTE4,
ATTRIBUTE5 = X_ATTRIBUTE5,
ATTRIBUTE6 = X_ATTRIBUTE6,
ATTRIBUTE7 = X_ATTRIBUTE7,
ATTRIBUTE8 = X_ATTRIBUTE8,
ATTRIBUTE9 = X_ATTRIBUTE9,
ATTRIBUTE10 = X_ATTRIBUTE10,
ATTRIBUTE11 = X_ATTRIBUTE11,
ATTRIBUTE12 = X_ATTRIBUTE12,
ATTRIBUTE13 = X_ATTRIBUTE13,
LOCATOR_CONTROL = X_LOCATOR_CONTROL,
LOT_CONTROL = X_LOT_CONTROL,
SERIAL_CONTROL = X_SERIAL_CONTROL,
ONHAND_CONTROL = X_ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
ZONE_CONTROL = X_ZONE_CONTROL,
ATTRIBUTE1 = X_ATTRIBUTE1,
ATTRIBUTE14 = X_ATTRIBUTE14,
REQUEST_ID = X_REQUEST_ID,
ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
ENABLED_FLAG = X_ENABLED_FLAG,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
LPN_CONTROL = X_LPN_CONTROL,
--INVCONV KKILLAMS
INVENTORY_ATP_CODE = X_INVENTORY_ATP_CODE,
RESERVABLE_TYPE = X_RESERVABLE_TYPE,
AVAILABILITY_TYPE = X_AVAILABILITY_TYPE
--END INVCONV KKILLAMS
where STATUS_ID = X_STATUS_ID;
update MTL_MATERIAL_STATUSES_TL set
STATUS_CODE = X_STATUS_CODE,
DESCRIPTION = X_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where STATUS_ID = X_STATUS_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
X_UPDATE_FLAG := 'T';
UPDATE MTL_LOT_NUMBERS SET INVENTORY_ATP_CODE =X_INVENTORY_ATP_CODE,
AVAILABILITY_TYPE =X_RESERVABLE_TYPE,
RESERVABLE_TYPE =X_AVAILABILITY_TYPE
WHERE STATUS_ID = X_STATUS_ID;
UPDATE MTL_ITEM_LOCATIONS SET INVENTORY_ATP_CODE =X_INVENTORY_ATP_CODE,
AVAILABILITY_TYPE =X_RESERVABLE_TYPE,
RESERVABLE_TYPE =X_AVAILABILITY_TYPE
WHERE STATUS_ID = X_STATUS_ID;
UPDATE MTL_SECONDARY_INVENTORIES SET INVENTORY_ATP_CODE =X_INVENTORY_ATP_CODE,
AVAILABILITY_TYPE =X_RESERVABLE_TYPE,
RESERVABLE_TYPE =X_AVAILABILITY_TYPE
WHERE STATUS_ID = X_STATUS_ID;
end UPDATE_ROW;
procedure DELETE_ROW (
X_STATUS_ID in NUMBER
) is
begin
delete from MTL_MATERIAL_STATUSES_TL
where STATUS_ID = X_STATUS_ID;
delete from MTL_MATERIAL_STATUSES_B
where STATUS_ID = X_STATUS_ID;
end DELETE_ROW;
update mtl_material_statuses_tl set
status_code = X_STATUS_CODE,
description = X_DESCRIPTION,
last_update_date = sysdate,
last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
last_update_login = 0,
source_lang = userenv('LANG')
where status_id = fnd_number.canonical_to_number(x_status_id)
and userenv('LANG') IN (language, source_lang);
SELECT SYSDATE INTO l_sysdate FROM dual;
MTL_MATERIAL_STATUSES_PKG.update_row
(
X_STATUS_ID => l_status_id,
X_ATTRIBUTE15 => X_ATTRIBUTE15,
X_ATTRIBUTE2 => X_ATTRIBUTE2,
X_ATTRIBUTE3 => X_ATTRIBUTE3,
X_ATTRIBUTE4 => X_ATTRIBUTE4,
X_ATTRIBUTE5 => X_ATTRIBUTE5,
X_ATTRIBUTE6 => X_ATTRIBUTE6,
X_ATTRIBUTE7 => X_ATTRIBUTE7,
X_ATTRIBUTE8 => X_ATTRIBUTE8,
X_ATTRIBUTE9 => X_ATTRIBUTE9,
X_ATTRIBUTE10 => X_ATTRIBUTE10,
X_ATTRIBUTE11 => X_ATTRIBUTE11,
X_ATTRIBUTE12 => X_ATTRIBUTE12,
X_ATTRIBUTE13 => X_ATTRIBUTE13,
X_LOCATOR_CONTROL => l_locator_control,
X_LOT_CONTROL => l_lot_control,
X_SERIAL_CONTROL => l_serial_control,
X_ONHAND_CONTROL => l_onhand_control, -- Onhand Material Status Support Bug #6633612
X_ZONE_CONTROL => l_zone_control,
X_ATTRIBUTE1 => X_ATTRIBUTE1,
X_ATTRIBUTE14 => X_ATTRIBUTE14,
X_REQUEST_ID => NULL,
X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE15,
X_ENABLED_FLAG => l_enabled_flag,
X_STATUS_CODE => x_status_code,
X_DESCRIPTION => x_description,
X_LAST_UPDATE_DATE => l_sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0,
X_LPN_CONTROL => l_lpn_control,
--INVCONV kkillams
X_INVENTORY_ATP_CODE => l_inventory_atp_code,
X_RESERVABLE_TYPE => l_reservable_type,
X_AVAILABILITY_TYPE => l_availability_type
--END INVCONV kkillams
);
IF X_UPDATE_FLAG = 'T' THEN
MTL_MATERIAL_STATUSES_PKG.insert_tl_row
(
X_ROWID => l_row_id,
X_STATUS_ID => l_status_id,
X_STATUS_CODE => x_status_code,
X_DESCRIPTION => x_description,
X_CREATION_DATE => l_sysdate,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => l_sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0
);
MTL_MATERIAL_STATUSES_PKG.insert_row
(
X_ROWID => l_row_id,
X_STATUS_ID => l_status_id,
X_ATTRIBUTE15 => X_ATTRIBUTE15,
X_ATTRIBUTE2 => X_ATTRIBUTE2,
X_ATTRIBUTE3 => X_ATTRIBUTE3,
X_ATTRIBUTE4 => X_ATTRIBUTE4,
X_ATTRIBUTE5 => X_ATTRIBUTE5,
X_ATTRIBUTE6 => X_ATTRIBUTE6,
X_ATTRIBUTE7 => X_ATTRIBUTE7,
X_ATTRIBUTE8 => X_ATTRIBUTE8,
X_ATTRIBUTE9 => X_ATTRIBUTE9,
X_ATTRIBUTE10 => X_ATTRIBUTE10,
X_ATTRIBUTE11 => X_ATTRIBUTE11,
X_ATTRIBUTE12 => X_ATTRIBUTE12,
X_ATTRIBUTE13 => X_ATTRIBUTE13,
X_LOCATOR_CONTROL => l_locator_control,
X_LOT_CONTROL => l_lot_control,
X_SERIAL_CONTROL => l_serial_control,
X_ONHAND_CONTROL => l_onhand_control, -- Onhand Material Status Support Bug #6633612
X_ZONE_CONTROL => l_zone_control,
X_ATTRIBUTE1 => X_ATTRIBUTE1,
X_ATTRIBUTE14 => X_ATTRIBUTE14,
X_REQUEST_ID => NULL,
X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE15,
X_ENABLED_FLAG => l_enabled_flag,
X_STATUS_CODE => x_status_code,
X_DESCRIPTION => x_description,
X_CREATION_DATE => l_sysdate,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => l_sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0,
X_LPN_CONTROL => l_lpn_control,
--INVCONV kkillams
X_INVENTORY_ATP_CODE => l_inventory_atp_code,
X_RESERVABLE_TYPE => l_reservable_type,
X_AVAILABILITY_TYPE => l_availability_type
--END INVCONV kkillams
);
delete from MTL_MATERIAL_STATUSES_TL T
where not exists
(select NULL
from MTL_MATERIAL_STATUSES_B B
where B.STATUS_ID = T.STATUS_ID
);
update MTL_MATERIAL_STATUSES_TL T set (
STATUS_CODE,
DESCRIPTION
) = (select
B.STATUS_CODE,
B.DESCRIPTION
from MTL_MATERIAL_STATUSES_TL B
where B.STATUS_ID = T.STATUS_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.STATUS_ID,
T.LANGUAGE
) in (select
SUBT.STATUS_ID,
SUBT.LANGUAGE
from MTL_MATERIAL_STATUSES_TL SUBB, MTL_MATERIAL_STATUSES_TL SUBT
where SUBB.STATUS_ID = SUBT.STATUS_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.STATUS_CODE <> SUBT.STATUS_CODE
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into MTL_MATERIAL_STATUSES_TL (
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
STATUS_CODE,
DESCRIPTION,
STATUS_ID,
LAST_UPDATED_BY,
LANGUAGE,
SOURCE_LANG
) select
B.LAST_UPDATE_DATE,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATE_LOGIN,
B.STATUS_CODE,
B.DESCRIPTION,
B.STATUS_ID,
B.LAST_UPDATED_BY,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from MTL_MATERIAL_STATUSES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from MTL_MATERIAL_STATUSES_TL T
where T.STATUS_ID = B.STATUS_ID
and T.LANGUAGE = L.LANGUAGE_CODE);