[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM mtl_lot_numbers
WHERE rowid = X_Rowid
FOR UPDATE of Organization_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Inventory_Item_Id NUMBER,
X_Organization_Id NUMBER,
X_Lot_Number VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Expiration_Date DATE,
X_Disable_Flag NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Status_ID NUMBER,
X_Description VARCHAR2,
X_Vendor_Id NUMBER,
X_Grade_Code VARCHAR2,
X_Origination_Date DATE,
X_Date_Code VARCHAR2,
X_Change_Date DATE,
X_Age NUMBER,
X_Retest_Date DATE,
X_Maturity_Date DATE,
X_Lot_Attribute_Category VARCHAR2,
X_Item_Size NUMBER,
X_Color VARCHAR2,
X_Volume NUMBER,
X_Volume_UOM VARCHAR2,
X_Place_of_Origin VARCHAR2,
X_Best_by_Date DATE,
X_Length NUMBER,
X_Length_UOM VARCHAR2,
X_Recycled_Content NUMBER,
X_Thickness NUMBER,
X_Thickness_UOM VARCHAR2,
X_Width NUMBER,
X_Width_UOM VARCHAR2,
X_Curl_Wrinkle_Fold VARCHAR2,
X_C_Attribute1 VARCHAR2,
X_C_Attribute2 VARCHAR2,
X_C_Attribute3 VARCHAR2,
X_C_Attribute4 VARCHAR2,
X_C_Attribute5 VARCHAR2,
X_C_Attribute6 VARCHAR2,
X_C_Attribute7 VARCHAR2,
X_C_Attribute8 VARCHAR2,
X_C_Attribute9 VARCHAR2,
X_C_Attribute10 VARCHAR2,
X_C_Attribute11 VARCHAR2,
X_C_Attribute12 VARCHAR2,
X_C_Attribute13 VARCHAR2,
X_C_Attribute14 VARCHAR2,
X_C_Attribute15 VARCHAR2,
X_C_Attribute16 VARCHAR2,
X_C_Attribute17 VARCHAR2,
X_C_Attribute18 VARCHAR2,
X_C_Attribute19 VARCHAR2,
X_C_Attribute20 VARCHAR2,
X_D_Attribute1 DATE,
X_D_Attribute2 DATE,
X_D_Attribute3 DATE,
X_D_Attribute4 DATE,
X_D_Attribute5 DATE,
X_D_Attribute6 DATE,
X_D_Attribute7 DATE,
X_D_Attribute8 DATE,
X_D_Attribute9 DATE,
X_D_Attribute10 DATE,
X_N_Attribute1 NUMBER,
X_N_Attribute2 NUMBER,
X_N_Attribute3 NUMBER,
X_N_Attribute4 NUMBER,
X_N_Attribute5 NUMBER,
X_N_Attribute6 NUMBER,
X_N_Attribute7 NUMBER,
X_N_Attribute8 NUMBER,
X_N_Attribute10 NUMBER,
X_Supplier_Lot_Number VARCHAR2,
X_N_Attribute9 NUMBER,
X_Territory_Code VARCHAR2,
X_Parent_Lot_Number VARCHAR2,
X_Origination_Type NUMBER,
X_Expiration_Action_Date DATE,
X_Expiration_Action_Code VARCHAR2,
X_Hold_Date DATE,
X_Sampling_Event_ID NUMBER DEFAULT NULL
) IS
l_status_id NUMBER;
to support call to update grade history.
========================================*/
l_grade_code MTL_LOT_NUMBERS.GRADE_CODE%TYPE;
the record get updated with modified values. */
SELECT status_id, grade_code
INTO l_status_id, l_grade_code
FROM mtl_lot_numbers
WHERE rowid = X_Rowid;
S Feinstein - removed new select code
and incorporated it in select above
===================================
SELECT grade_code
INTO l_grade_code
FROM mtl_lot_numbers
WHERE rowid = X_Rowid;
Added Sampling_Event_ID to columns updated
========================================*/
UPDATE mtl_lot_numbers
SET
inventory_item_id = X_Inventory_Item_Id,
organization_id = X_Organization_Id,
lot_number = X_Lot_Number,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
expiration_date = X_Expiration_Date,
disable_flag = X_Disable_Flag,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
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,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
status_id = X_Status_ID,
description = X_Description,
vendor_id = X_Vendor_Id,
grade_code = X_Grade_Code,
origination_date = X_Origination_Date,
date_code = X_Date_Code,
change_date = X_Change_date,
age = X_Age,
retest_date = X_Retest_Date,
maturity_date = X_Maturity_Date,
lot_attribute_category = X_Lot_Attribute_Category,
item_size = X_Item_Size,
color = X_Color,
volume = X_volume,
volume_uom = X_Volume_UOM,
place_of_origin = X_Place_Of_Origin,
best_by_date = X_Best_BY_Date,
length = X_Length,
length_uom = X_Length_UOM,
recycled_content = X_Recycled_Content,
thickness = X_Thickness,
thickness_uom = X_Thickness_UOM,
width = X_Width,
width_uom = X_Width_UOM,
curl_wrinkle_fold = X_Curl_Wrinkle_Fold,
c_attribute1 = X_C_Attribute1,
c_attribute2 = X_C_Attribute2,
c_attribute3 = X_C_Attribute3,
c_attribute4 = X_C_Attribute4,
c_attribute5 = X_C_Attribute5,
c_attribute6 = X_C_Attribute6,
c_attribute7 = X_C_Attribute7,
c_attribute8 = X_C_Attribute8,
c_attribute9 = X_C_Attribute9,
c_attribute10 = X_C_Attribute10,
c_attribute11 = X_C_Attribute11,
c_attribute12 = X_C_Attribute12,
c_attribute13 = X_C_Attribute13,
c_attribute14 = X_C_Attribute14,
c_attribute15 = X_C_Attribute15,
c_attribute16 = X_C_Attribute16,
c_attribute17 = X_C_Attribute17,
c_attribute18 = X_C_Attribute18,
c_attribute19 = X_C_Attribute19,
c_attribute20 = X_C_Attribute20,
d_attribute1 = X_D_Attribute1,
d_attribute2 = X_D_Attribute2,
d_attribute3 = X_D_Attribute3,
d_attribute4 = X_D_Attribute4,
d_attribute5 = X_D_Attribute5,
d_attribute6 = X_D_Attribute6,
d_attribute7 = X_D_Attribute7,
d_attribute8 = X_D_Attribute8,
d_attribute9 = X_D_Attribute9,
d_attribute10 = X_D_Attribute10,
n_attribute1 = X_N_Attribute1,
n_attribute2 = X_N_Attribute2,
n_attribute3 = X_N_Attribute3,
n_attribute4 = X_N_Attribute4,
n_attribute5 = X_N_Attribute5,
n_attribute6 = X_N_Attribute6,
n_attribute7 = X_N_Attribute7,
n_attribute8 = X_N_Attribute8,
n_attribute10 = X_N_Attribute10,
supplier_lot_number = X_Supplier_Lot_Number,
n_attribute9 = X_N_Attribute9,
territory_code = X_Territory_code,
parent_lot_number = X_Parent_Lot_Number,
origination_type = X_Origination_Type,
expiration_action_date = X_Expiration_Action_Date,
expiration_action_code = X_Expiration_Action_Code,
hold_date = X_Hold_Date,
Sampling_Event_ID = X_Sampling_Event_ID
WHERE rowid = X_Rowid;
This Procedure Caters to the insertion of records in the
table MTL_MATERIAL_STATUS_HISTORY. */
--BUG 7258237 For updating status wms install is not required
IF --(INV_INSTALL.ADV_INV_INSTALLED(P_Organization_ID => NULL)) AND
(X_Status_ID IS NOT NULL) AND
(X_Status_ID <> l_status_id) THEN
MTL_SECONDARY_INVENTORIES_PKG.Status_History
( X_Organization_ID,
X_Inventory_Item_ID,
X_Lot_Number,
NULL,
2,
X_Status_ID,
NULL,
NULL,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Updated_By,
X_Last_Update_Date,
X_Last_Update_Login);
INSERT INTO MTL_LOT_GRADE_HISTORY
(
GRADE_UPDATE_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LOT_NUMBER,
UPDATE_METHOD,
NEW_GRADE_CODE,
OLD_GRADE_CODE,
PRIMARY_QUANTITY,
SECONDARY_QUANTITY,
UPDATE_REASON_ID,
INITIAL_GRADE_FLAG,
FROM_MOBILE_APPS_FLAG,
GRADE_UPDATE_DATE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
MTL_LOT_GRADE_HISTORY_S.NEXTVAL,
X_INVENTORY_ITEM_ID,
X_ORGANIZATION_ID,
X_LOT_NUMBER,
INV_MATERIAL_STATUS_PUB.g_update_method_manual,/* Jalaj Srivastava Bug 4998256 pass update_method as manual instead of null */
X_GRADE_CODE,
l_grade_code,
l_qoh, /* Jalaj Srivastava Bug 4998256 pass primary onhand */
l_sqoh, /* Jalaj Srivastava Bug 4998256 pass secondary onhand */
NULL,
'N',
'N',
SYSDATE,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
);
END Update_Row;
PROCEDURE Insert_Row(X_Inventory_Item_Id NUMBER,
X_Organization_Id NUMBER,
X_Lot_Number VARCHAR2,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Request_Id NUMBER,
X_Program_Application_Id NUMBER,
X_Program_Id NUMBER,
X_Program_Update_Date DATE,
X_Expiration_Date DATE,
X_Disable_Flag NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Status_ID NUMBER,
X_Description VARCHAR2,
X_Vendor_Id NUMBER,
X_Grade_Code VARCHAR2,
X_Origination_Date DATE,
X_Date_Code VARCHAR2,
X_Change_Date DATE,
X_Age NUMBER,
X_Retest_Date DATE,
X_Maturity_Date DATE,
X_Lot_Attribute_Category VARCHAR2,
X_Item_Size NUMBER,
X_Color VARCHAR2,
X_Volume NUMBER,
X_Volume_UOM VARCHAR2,
X_Place_of_Origin VARCHAR2,
X_Best_by_Date DATE,
X_Length NUMBER,
X_Length_UOM VARCHAR2,
X_Recycled_Content NUMBER,
X_Thickness NUMBER,
X_Thickness_UOM VARCHAR2,
X_Width NUMBER,
X_Width_UOM VARCHAR2,
X_Curl_Wrinkle_Fold VARCHAR2,
X_C_Attribute1 VARCHAR2,
X_C_Attribute2 VARCHAR2,
X_C_Attribute3 VARCHAR2,
X_C_Attribute4 VARCHAR2,
X_C_Attribute5 VARCHAR2,
X_C_Attribute6 VARCHAR2,
X_C_Attribute7 VARCHAR2,
X_C_Attribute8 VARCHAR2,
X_C_Attribute9 VARCHAR2,
X_C_Attribute10 VARCHAR2,
X_C_Attribute11 VARCHAR2,
X_C_Attribute12 VARCHAR2,
X_C_Attribute13 VARCHAR2,
X_C_Attribute14 VARCHAR2,
X_C_Attribute15 VARCHAR2,
X_C_Attribute16 VARCHAR2,
X_C_Attribute17 VARCHAR2,
X_C_Attribute18 VARCHAR2,
X_C_Attribute19 VARCHAR2,
X_C_Attribute20 VARCHAR2,
X_D_Attribute1 DATE,
X_D_Attribute2 DATE,
X_D_Attribute3 DATE,
X_D_Attribute4 DATE,
X_D_Attribute5 DATE,
X_D_Attribute6 DATE,
X_D_Attribute7 DATE,
X_D_Attribute8 DATE,
X_D_Attribute9 DATE,
X_D_Attribute10 DATE,
X_N_Attribute1 NUMBER,
X_N_Attribute2 NUMBER,
X_N_Attribute3 NUMBER,
X_N_Attribute4 NUMBER,
X_N_Attribute5 NUMBER,
X_N_Attribute6 NUMBER,
X_N_Attribute7 NUMBER,
X_N_Attribute8 NUMBER,
X_N_Attribute9 NUMBER,
X_N_Attribute10 NUMBER,
X_Supplier_Lot_Number VARCHAR2,
X_Territory_Code VARCHAR2,
X_Parent_Lot_Number VARCHAR2,
X_Origination_Type NUMBER,
X_Expiration_Action_Date DATE,
X_Expiration_Action_Code VARCHAR2,
X_Hold_Date DATE ,
X_SAMPLING_EVENT_ID NUMBER DEFAULT NULL
)
IS
BEGIN
INSERT INTO MTL_LOT_NUMBERS (
Inventory_Item_Id,
Organization_Id,
Lot_Number,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date,
Expiration_Date,
Disable_Flag,
Attribute_Category,
Attribute1, Attribute2, Attribute3, Attribute4,
Attribute5, Attribute6, Attribute7, Attribute8,
Attribute9, Attribute10, Attribute11, Attribute12,
Attribute13, Attribute14, Attribute15,
Status_ID,
Description,
Vendor_Id,
Grade_Code,
Origination_Date,
Date_Code,
Change_Date,
Age,
Retest_Date,
Maturity_Date,
Lot_Attribute_Category,
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,
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,
Supplier_Lot_Number,
Territory_Code,
Parent_Lot_Number,
Origination_Type,
Expiration_Action_Date,
Expiration_Action_Code,
Hold_Date
,gen_object_id -- NSRIVAST, INVCONV
,sampling_event_id -- Bug 4115021 OPM Inventory Convergence
)
VALUES
(X_Inventory_Item_Id,
X_Organization_Id,
X_Lot_Number,
X_Creation_Date,
X_Created_By,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login,
X_Request_Id,
X_Program_Application_Id,
X_Program_Id,
X_Program_Update_Date,
X_Expiration_Date,
X_Disable_Flag,
X_Attribute_Category,
X_Attribute1, 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_Attribute14, X_Attribute15,
X_Status_ID,
X_Description,
X_Vendor_Id,
X_Grade_Code,
X_Origination_Date,
X_Date_Code,
X_Change_Date,
X_Age,
X_Retest_Date,
X_Maturity_Date,
X_Lot_Attribute_Category,
X_Item_Size,
X_Color,
X_Volume,
X_Volume_UOM,
X_Place_of_Origin,
X_Best_by_Date,
X_Length,
X_Length_UOM,
X_Recycled_Content,
X_Thickness,
X_Thickness_UOM,
X_Width,
X_Width_UOM,
X_Curl_Wrinkle_Fold,
X_C_Attribute1, X_C_Attribute2, X_C_Attribute3, X_C_Attribute4,
X_C_Attribute5, X_C_Attribute6, X_C_Attribute7, X_C_Attribute8,
X_C_Attribute9, X_C_Attribute10, X_C_Attribute11, X_C_Attribute12,
X_C_Attribute13, X_C_Attribute14, X_C_Attribute15, X_C_Attribute16,
X_C_Attribute17, X_C_Attribute18, X_C_Attribute19, X_C_Attribute20,
X_D_Attribute1, X_D_Attribute2, X_D_Attribute3, X_D_Attribute4,
X_D_Attribute5, X_D_Attribute6, X_D_Attribute7, X_D_Attribute8,
X_D_Attribute9, X_D_Attribute10,
X_N_Attribute1, X_N_Attribute2, X_N_Attribute3, X_N_Attribute4,
X_N_Attribute5, X_N_Attribute6, X_N_Attribute7, X_N_Attribute8,
X_N_Attribute9, X_N_Attribute10,
X_Supplier_Lot_Number,
X_Territory_Code,
X_Parent_Lot_Number,
X_Origination_Type,
X_Expiration_Action_Date,
X_Expiration_Action_Code,
X_Hold_Date
,mtl_gen_object_id_s.NEXTVAL -- NSRIVAST, INVCONV
,x_sampling_event_id
);
This Procedure Caters to the insertion of records in the
table MTL_MATERIAL_STATUS_HISTORY. */
-- Bug 7502482 WMS installation is not required For Inserting Lot History.
IF --(INV_INSTALL.ADV_INV_INSTALLED(P_Organization_ID => NULL)) AND
(X_Status_ID IS NOT NULL) THEN
/*=================================================
BUG#4222397 = Changed to pass Y for value of
initial_status_flag and named the parameters.
=================================================*/
MTL_SECONDARY_INVENTORIES_PKG.Status_History
(x_organization_id => X_Organization_ID
, x_inventory_item_id => X_Inventory_Item_ID
, x_lot_number => X_Lot_Number
, x_serial_number => NULL
, x_update_method => 2
, x_status_id => X_Status_ID
, x_zone_code => NULL
, x_locator_id => NULL
, x_creation_date => X_Last_Update_Date
, x_created_by => X_Last_Updated_By
, x_last_updated_by => X_Last_Updated_By
, x_last_update_date => X_Last_Update_Date
, x_last_update_login => X_Last_Update_Login
, x_initial_status_flag => 'Y');
END Insert_Row;