[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM mtl_related_items
WHERE
INVENTORY_ITEM_ID = c_inventory_item_id
AND RELATED_ITEM_ID = c_related_item_id
AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
AND ORGANIZATION_ID = c_organization_id;
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,RELATED_ITEM_ID
,RELATIONSHIP_TYPE_ID
,RECIPROCAL_FLAG
,START_DATE
,END_DATE
,ATTR_CONTEXT
,ATTR_CHAR1
,ATTR_CHAR2
,ATTR_CHAR3
,ATTR_CHAR4
,ATTR_CHAR5
,ATTR_CHAR6
,ATTR_CHAR7
,ATTR_CHAR8
,ATTR_CHAR9
,ATTR_CHAR10
,ATTR_NUM1
,ATTR_NUM2
,ATTR_NUM3
,ATTR_NUM4
,ATTR_NUM5
,ATTR_NUM6
,ATTR_NUM7
,ATTR_NUM8
,ATTR_NUM9
,ATTR_NUM10
,ATTR_DATE1
,ATTR_DATE2
,ATTR_DATE3
,ATTR_DATE4
,ATTR_DATE5
,ATTR_DATE6
,ATTR_DATE7
,ATTR_DATE8
,ATTR_DATE9
,ATTR_DATE10
,PLANNING_ENABLED_FLAG
FROM MTL_RELATED_ITEMS
WHERE INVENTORY_ITEM_ID = c_inventory_item_id
AND RELATED_ITEM_ID = c_related_item_id
AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
AND ORGANIZATION_ID = c_organization_id
FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT; --??
SELECT *
FROM mtl_related_items_pln_info
WHERE
INVENTORY_ITEM_ID = c_inventory_item_id
AND RELATED_ITEM_ID = c_related_item_id
AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
AND ORGANIZATION_ID = c_organization_id;
SELECT *
FROM mtl_related_items_pln_info
WHERE
INVENTORY_ITEM_ID = c_inventory_item_id
AND RELATED_ITEM_ID = c_related_item_id
AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
AND ORGANIZATION_ID = c_organization_id
AND PLN_INFO_ID = c_pln_info_id;
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,RELATED_ITEM_ID
,RELATIONSHIP_TYPE_ID
,PLN_INFO_ID
,START_DATE
,END_DATE
,SUBSTITUTION_SET
,PARTIAL_FULFILLMENT_FLAG
,ALL_CUSTOMERS_FLAG
FROM MTL_RELATED_ITEMS_PLN_INFO
WHERE
INVENTORY_ITEM_ID = c_inventory_item_id
AND RELATED_ITEM_ID = c_related_item_id
AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
AND ORGANIZATION_ID = c_organization_id
AND PLN_INFO_ID = c_pln_info_id
FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT; --??
SELECT *
FROM mtl_related_items_cust_ref
WHERE
INVENTORY_ITEM_ID = c_inventory_item_id
AND RELATED_ITEM_ID = c_related_item_id
AND RELATIONSHIP_TYPE_ID = C_RELATIONSHIP_TYPE_ID
AND ORGANIZATION_ID = c_organization_id;
SELECT *
FROM mtl_related_items_cust_ref
WHERE
INVENTORY_ITEM_ID = c_inventory_item_id
AND RELATED_ITEM_ID = c_related_item_id
AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
AND ORGANIZATION_ID = c_organization_id
AND PLN_INFO_ID = c_pln_info_id
AND CUSTOMER_ID = c_customer_id
AND SITE_USE_ID = c_site_use_id;
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,RELATED_ITEM_ID
,RELATIONSHIP_TYPE_ID
,PLN_INFO_ID
,CUSTOMER_ID
,SITE_USE_ID
,START_DATE
,END_DATE
FROM MTL_RELATED_ITEMS_CUST_REF
WHERE
INVENTORY_ITEM_ID = c_inventory_item_id
AND RELATED_ITEM_ID = c_related_item_id
AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
AND ORGANIZATION_ID = c_organization_id
AND PLN_INFO_ID = c_pln_info_id
AND CUSTOMER_ID = c_customer_id
AND SITE_USE_ID = c_site_use_id
FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT; --??
l_Rel_Item_Rec.Transaction_Type <> 'UPDATE' AND
l_Rel_Item_Rec.Transaction_Type <> 'DELETE') THEN
x_return_status := FND_API.G_RET_STS_ERROR;
SELECT 'x' INTO l_prod_exists
FROM fnd_grants
WHERE object_id IN
(SELECT object_id FROM fnd_objects
WHERE obj_name = 'EGO_ITEM');
SELECT status INTO l_pim_exists
FROM FND_PRODUCT_INSTALLATIONS
WHERE application_id = 431;
p_message_name => 'INV_IOI_ITEM_UPDATE_PRIV'
,p_application_id => 'INV'
,p_message_type => 'E'
,p_entity_code => G_Entity_Code
,p_entity_index => 1
,p_table_name => 'MTL_RELATED_ITEMS'
);
SELECT 'x' INTO l_master_org_exists
FROM mtl_parameters
WHERE ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID
AND MASTER_ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID;
SELECT 'x' INTO l_inv_item_exists
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID;
SELECT 'x' INTO l_rel_item_exists
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID
AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID;
SELECT 'x' INTO l_rel_item_exists
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL
AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID;
SELECT 'x' INTO l_rel_type_exists
FROM mfg_lookups
WHERE lookup_type = 'MTL_RELATIONSHIP_TYPES'
AND enabled_flag = 'Y'
AND lookup_code >= 1
AND lookup_code = l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID;
SELECT 'x' INTO l_rel_type_exists
FROM mfg_lookups
WHERE lookup_type = 'MTL_RELATIONSHIP_TYPES'
AND enabled_flag = 'Y'
AND lookup_code > =1
AND lookup_code = l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL;
IF l_Rel_Item_Rec.TRANSACTION_TYPE = 'CREATE' OR l_Rel_Item_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
-- item cannot be related to self
-- bug 14403205
IF (l_Rel_Item_Rec.INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID) OR (l_Rel_Item_Rec.INVENTORY_ITEM_ID =
l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
p_message_name => 'EGO_SELECTED_DATE_INVALID'
,p_application_id => 'EGO'
,p_message_type => 'E'
,p_entity_code => G_Entity_Code
,p_entity_index => 1
,p_table_name => G_Table_Name
);
p_message_name => 'EGO_SELECTED_DATE_INVALID'
,p_application_id => 'EGO'
,p_message_type => 'E'
,p_entity_code => G_Entity_Code
,p_entity_index => 1
,p_table_name => G_Table_Name
);
END IF; -- if create/update
SELECT 'x' INTO l_rel_exists
FROM mtl_related_items
WHERE (INVENTORY_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
AND RELATIONSHIP_TYPE_ID= l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
AND RELATED_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID
AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID)
OR (INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID -- checking for derived related items
AND RELATED_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
AND RELATIONSHIP_TYPE_ID= l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID
AND RECIPROCAL_FLAG = 'Y');
SELECT Decode(l_Rel_Item_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM, NULL, l_Rel_Item_Rec.Inventory_Item_Id),
Decode(l_Rel_Item_Rec.Organization_Id,FND_API.G_MISS_NUM, NULL, l_Rel_Item_Rec.Organization_Id),
Decode(l_Rel_Item_Rec.Related_Item_Id,FND_API.G_MISS_NUM, NULL, l_Rel_Item_Rec.Related_Item_Id),
Decode(l_Rel_Item_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, NULL, l_Rel_Item_Rec.Relationship_Type_Id),
Decode(l_Rel_Item_Rec.Reciprocal_Flag,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Reciprocal_Flag),
Decode(l_Rel_Item_Rec.Start_Date,FND_API.G_MISS_DATE, NULL, l_Rel_Item_Rec.Start_Date),
Decode(l_Rel_Item_Rec.End_Date,FND_API.G_MISS_DATE, NULL, l_Rel_Item_Rec.End_Date),
Decode(l_Rel_Item_Rec.Attr_Context,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Context),
Decode(l_Rel_Item_Rec.Attr_Char1,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char1),
Decode(l_Rel_Item_Rec.Attr_Char2,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char2),
Decode(l_Rel_Item_Rec.Attr_Char3,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char3),
Decode(l_Rel_Item_Rec.Attr_Char4,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char4),
Decode(l_Rel_Item_Rec.Attr_Char5,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char5),
Decode(l_Rel_Item_Rec.Attr_Char6,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char6),
Decode(l_Rel_Item_Rec.Attr_Char7,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char7),
Decode(l_Rel_Item_Rec.Attr_Char8,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char8),
Decode(l_Rel_Item_Rec.Attr_Char9,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char9),
Decode(l_Rel_Item_Rec.Attr_Char10,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char10),
Decode(l_Rel_Item_Rec.Attr_Num1,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num1),
Decode(l_Rel_Item_Rec.Attr_Num2,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num2),
Decode(l_Rel_Item_Rec.Attr_Num3,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num3),
Decode(l_Rel_Item_Rec.Attr_Num4,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num4),
Decode(l_Rel_Item_Rec.Attr_Num5,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num5),
Decode(l_Rel_Item_Rec.Attr_Num6,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num6),
Decode(l_Rel_Item_Rec.Attr_Num7,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num7),
Decode(l_Rel_Item_Rec.Attr_Num8,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num8),
Decode(l_Rel_Item_Rec.Attr_Num9,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num9),
Decode(l_Rel_Item_Rec.Attr_Num10,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num10),
Decode(l_Rel_Item_Rec.Attr_Date1,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date1),
Decode(l_Rel_Item_Rec.Attr_Date2,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date2),
Decode(l_Rel_Item_Rec.Attr_Date3,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date3),
Decode(l_Rel_Item_Rec.Attr_Date4,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date4),
Decode(l_Rel_Item_Rec.Attr_Date5,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date5),
Decode(l_Rel_Item_Rec.Attr_Date6,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date6),
Decode(l_Rel_Item_Rec.Attr_Date7,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date7),
Decode(l_Rel_Item_Rec.Attr_Date8,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date8),
Decode(l_Rel_Item_Rec.Attr_Date9,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date9),
Decode(l_Rel_Item_Rec.Attr_Date10,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date10),
Decode(l_Rel_Item_Rec.Planning_Enabled_Flag,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Planning_Enabled_Flag)--,
--Decode(l_Rel_Item_Rec.Last_Update_Date,FND_API.G_MISS_DATE,NULL,l_Rel_Item_Rec.Last_Update_Date),
--Decode(l_Rel_Item_Rec.Last_Updated_By,FND_API.G_MISS_NUM,NULL,l_Rel_Item_Rec.Last_Updated_By),
--Decode(l_Rel_Item_Rec.Creation_Date,FND_API.G_MISS_DATE,NULL,l_Rel_Item_Rec.Creation_Date),
--Decode(l_Rel_Item_Rec.Created_By,FND_API.G_MISS_NUM,NULL,l_Rel_Item_Rec.Created_By),
--Decode(l_Rel_Item_Rec.Last_Update_Login,FND_API.G_MISS_NUM,NULL,l_Rel_Item_Rec.Last_Update_Login)
INTO l_Rel_Item_Rec.Inventory_Item_Id, l_Rel_Item_Rec.Organization_Id, l_Rel_Item_Rec.Related_Item_Id,
l_Rel_Item_Rec.Relationship_Type_Id, l_Rel_Item_Rec.Reciprocal_Flag, l_Rel_Item_Rec.Start_Date,
l_Rel_Item_Rec.End_Date, l_Rel_Item_Rec.Attr_Context, l_Rel_Item_Rec.Attr_Char1, l_Rel_Item_Rec.Attr_Char2,
l_Rel_Item_Rec.Attr_Char3, l_Rel_Item_Rec.Attr_Char4, l_Rel_Item_Rec.Attr_Char5, l_Rel_Item_Rec.Attr_Char6,
l_Rel_Item_Rec.Attr_Char7, l_Rel_Item_Rec.Attr_Char8, l_Rel_Item_Rec.Attr_Char9, l_Rel_Item_Rec.Attr_Char10,
l_Rel_Item_Rec.Attr_Num1, l_Rel_Item_Rec.Attr_Num2, l_Rel_Item_Rec.Attr_Num3, l_Rel_Item_Rec.Attr_Num4,
l_Rel_Item_Rec.Attr_Num5, l_Rel_Item_Rec.Attr_Num6, l_Rel_Item_Rec.Attr_Num7, l_Rel_Item_Rec.Attr_Num8,
l_Rel_Item_Rec.Attr_Num9, l_Rel_Item_Rec.Attr_Num10, l_Rel_Item_Rec.Attr_Date1, l_Rel_Item_Rec.Attr_Date2,
l_Rel_Item_Rec.Attr_Date3, l_Rel_Item_Rec.Attr_Date4, l_Rel_Item_Rec.Attr_Date5, l_Rel_Item_Rec.Attr_Date6,
l_Rel_Item_Rec.Attr_Date7, l_Rel_Item_Rec.Attr_Date8, l_Rel_Item_Rec.Attr_Date9, l_Rel_Item_Rec.Attr_Date10,
l_Rel_Item_Rec.Planning_Enabled_Flag--, l_Rel_Item_Rec.Last_Update_Date, l_Rel_Item_Rec.Last_Updated_By,
--l_Rel_Item_Rec.Creation_Date,l_Rel_Item_Rec.Created_By, l_Rel_Item_Rec.Last_Update_Login
FROM dual;
MTL_RELATED_ITEMS_PKG.INSERT_ROW(
X_ROWID => RETURNED_ROW_ID
,X_INVENTORY_ITEM_ID => L_REL_ITEM_REC.INVENTORY_ITEM_ID
,X_ORGANIZATION_ID => L_REL_ITEM_REC.ORGANIZATION_ID
,X_RELATED_ITEM_ID => L_REL_ITEM_REC.RELATED_ITEM_ID
,X_RELATIONSHIP_TYPE_ID => L_REL_ITEM_REC.RELATIONSHIP_TYPE_ID
,X_RECIPROCAL_FLAG => L_REL_ITEM_REC.RECIPROCAL_FLAG
,X_START_DATE => L_REL_ITEM_REC.START_DATE
,X_END_DATE => L_REL_ITEM_REC.END_DATE
,X_ATTR_CONTEXT => L_REL_ITEM_REC.ATTR_CONTEXT
,X_ATTR_CHAR1 => L_REL_ITEM_REC.ATTR_CHAR1
,X_ATTR_CHAR2 => L_REL_ITEM_REC.ATTR_CHAR2
,X_ATTR_CHAR3 => L_REL_ITEM_REC.ATTR_CHAR3
,X_ATTR_CHAR4 => L_REL_ITEM_REC.ATTR_CHAR4
,X_ATTR_CHAR5 => L_REL_ITEM_REC.ATTR_CHAR5
,X_ATTR_CHAR6 => L_REL_ITEM_REC.ATTR_CHAR6
,X_ATTR_CHAR7 => L_REL_ITEM_REC.ATTR_CHAR7
,X_ATTR_CHAR8 => L_REL_ITEM_REC.ATTR_CHAR8
,X_ATTR_CHAR9 => L_REL_ITEM_REC.ATTR_CHAR9
,X_ATTR_CHAR10 => L_REL_ITEM_REC.ATTR_CHAR10
,X_ATTR_NUM1 => L_REL_ITEM_REC.ATTR_NUM1
,X_ATTR_NUM2 => L_REL_ITEM_REC.ATTR_NUM2
,X_ATTR_NUM3 => L_REL_ITEM_REC.ATTR_NUM3
,X_ATTR_NUM4 => L_REL_ITEM_REC.ATTR_NUM4
,X_ATTR_NUM5 => L_REL_ITEM_REC.ATTR_NUM5
,X_ATTR_NUM6 => L_REL_ITEM_REC.ATTR_NUM6
,X_ATTR_NUM7 => L_REL_ITEM_REC.ATTR_NUM7
,X_ATTR_NUM8 => L_REL_ITEM_REC.ATTR_NUM8
,X_ATTR_NUM9 => L_REL_ITEM_REC.ATTR_NUM9
,X_ATTR_NUM10 => L_REL_ITEM_REC.ATTR_NUM10
,X_ATTR_DATE1 => L_REL_ITEM_REC.ATTR_DATE1
,X_ATTR_DATE2 => L_REL_ITEM_REC.ATTR_DATE2
,X_ATTR_DATE3 => L_REL_ITEM_REC.ATTR_DATE3
,X_ATTR_DATE4 => L_REL_ITEM_REC.ATTR_DATE4
,X_ATTR_DATE5 => l_Rel_Item_Rec.Attr_Date5
,X_ATTR_DATE6 => L_REL_ITEM_REC.ATTR_DATE6
,X_ATTR_DATE7 => L_REL_ITEM_REC.ATTR_DATE7
,X_ATTR_DATE8 => L_REL_ITEM_REC.ATTR_DATE8
,X_ATTR_DATE9 => L_REL_ITEM_REC.ATTR_DATE9
,X_ATTR_DATE10 => L_REL_ITEM_REC.ATTR_DATE10
,X_PLANNING_ENABLED_FLAG => L_REL_ITEM_REC.PLANNING_ENABLED_FLAG
--,X_CREATION_DATE => Nvl(l_Rel_Item_Rec.Creation_Date, SYSDATE)
--,X_CREATED_BY => NVL(L_REL_ITEM_REC.CREATED_BY, FND_GLOBAL.USER_ID)
--,X_LAST_UPDATE_DATE => NVL(L_REL_ITEM_REC.LAST_UPDATE_DATE, sysdate)
--,X_LAST_UPDATED_BY => NVL(L_REL_ITEM_REC.LAST_UPDATED_BY, FND_GLOBAL.USER_ID)
--,X_LAST_UPDATE_LOGIN => NVL(L_REL_ITEM_REC.LAST_UPDATE_LOGIN, FND_GLOBAL.LOGIN_ID)
,X_CREATION_DATE => SYSDATE
,X_CREATED_BY =>FND_GLOBAL.USER_ID
,X_LAST_UPDATE_DATE => sysdate
,X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
,X_OBJECT_VERSION_NUMBER => NULL
);
IF l_Rel_Item_Rec.Transaction_Type = 'UPDATE' THEN
-- null columns have already been checked earlier
-- getting original values and checking for existance of record
OPEN mtl_rel_item_cur(l_Rel_Item_Rec.INVENTORY_ITEM_ID
,l_Rel_Item_Rec.RELATED_ITEM_ID
,l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
,l_Rel_Item_Rec.ORGANIZATION_ID);
SELECT Decode(Nvl(l_Rel_Item_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Inventory_Item_Id, -999999,NULL, l_Rel_Item_Rec.Inventory_Item_Id),
Decode(Nvl(l_Rel_Item_Rec.Organization_Id,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Organization_Id, -999999,NULL, l_Rel_Item_Rec.Organization_Id),
Decode(Nvl(l_Rel_Item_Rec.Related_Item_Id,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Related_Item_Id, -999999,NULL, l_Rel_Item_Rec.Related_Item_Id),
Decode(Nvl(l_Rel_Item_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Relationship_Type_Id, -999999,NULL, l_Rel_Item_Rec.Relationship_Type_Id),
-- using to replace new values.
Decode(Nvl(l_Rel_Item_Rec.Related_Item_Id_upd_val,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Related_Item_Id, -999999,NULL, l_Rel_Item_Rec.Related_Item_Id_upd_val),
Decode(Nvl(l_Rel_Item_Rec.Relationship_Type_Id_upd_val,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Relationship_Type_Id, -999999,NULL, l_Rel_Item_Rec.Relationship_Type_Id_upd_val),
Decode(Nvl(l_Rel_Item_Rec.Reciprocal_Flag,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Reciprocal_Flag, '!',NULL, l_Rel_Item_Rec.Reciprocal_Flag),
Decode(Nvl(l_Rel_Item_Rec.Start_Date,FND_API.G_MISS_DATE),FND_API.G_MISS_DATE, l_mtl_Rel_Item_rec.Start_Date, to_date('31-12-9999','DD-MM-YYYY'),NULL, l_Rel_Item_Rec.Start_Date),
Decode(Nvl(l_Rel_Item_Rec.End_Date,FND_API.G_MISS_DATE),FND_API.G_MISS_DATE, l_mtl_Rel_Item_rec.End_Date, to_date('31-12-9999','DD-MM-YYYY'),NULL, l_Rel_Item_Rec.End_Date),
Decode(Nvl(l_Rel_Item_Rec.Attr_Context,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Context, '!',NULL, l_Rel_Item_Rec.Attr_Context),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char1,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char1, '!',NULL, l_Rel_Item_Rec.Attr_Char1),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char2,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char2, '!',NULL, l_Rel_Item_Rec.Attr_Char2),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char3,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char3, '!',NULL, l_Rel_Item_Rec.Attr_Char3),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char4,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char4, '!',NULL, l_Rel_Item_Rec.Attr_Char4),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char5,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char5, '!',NULL, l_Rel_Item_Rec.Attr_Char5),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char6,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char6, '!',NULL, l_Rel_Item_Rec.Attr_Char6),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char7,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char7, '!',NULL, l_Rel_Item_Rec.Attr_Char7),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char8,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char8, '!',NULL, l_Rel_Item_Rec.Attr_Char8),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char9,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char9, '!',NULL, l_Rel_Item_Rec.Attr_Char9),
Decode(Nvl(l_Rel_Item_Rec.Attr_Char10,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char10, '!',NULL, l_Rel_Item_Rec.Attr_Char10),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num1,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num1, '!',NULL, l_Rel_Item_Rec.Attr_Num1),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num2,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num2, '!',NULL, l_Rel_Item_Rec.Attr_Num2),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num3,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num3, '!',NULL, l_Rel_Item_Rec.Attr_Num3),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num4,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num4, '!',NULL, l_Rel_Item_Rec.Attr_Num4),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num5,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num5, '!',NULL, l_Rel_Item_Rec.Attr_Num5),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num6,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num6, '!',NULL, l_Rel_Item_Rec.Attr_Num6),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num7,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num7, '!',NULL, l_Rel_Item_Rec.Attr_Num7),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num8,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num8, '!',NULL, l_Rel_Item_Rec.Attr_Num8),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num9,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num9, '!',NULL, l_Rel_Item_Rec.Attr_Num9),
Decode(Nvl(l_Rel_Item_Rec.Attr_Num10,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num10, '!',NULL, l_Rel_Item_Rec.Attr_Num10),
Decode(Nvl(l_Rel_Item_Rec.Attr_Date1,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date1, '!',NULL, l_Rel_Item_Rec.Attr_Date1),
Decode(Nvl(l_Rel_Item_Rec.Attr_Date2,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date2, '!',NULL, l_Rel_Item_Rec.Attr_Date2),
Decode(Nvl(l_Rel_Item_Rec.Attr_Date3,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date3, '!',NULL, l_Rel_Item_Rec.Attr_Date3),
Decode(Nvl(l_Rel_Item_Rec.Attr_Date4,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date4, '!',NULL, l_Rel_Item_Rec.Attr_Date4),
Decode(Nvl(l_Rel_Item_Rec.Attr_Date5,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date5, '!',NULL, l_Rel_Item_Rec.Attr_Date5),
Decode(Nvl(l_Rel_Item_Rec.Attr_Date6,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date6, '!',NULL, l_Rel_Item_Rec.Attr_Date6),
Decode(Nvl(l_Rel_Item_Rec.Attr_Date7,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date7, '!',NULL, l_Rel_Item_Rec.Attr_Date7),
Decode(Nvl(l_Rel_Item_Rec.Attr_Date8,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date8, '!',NULL, l_Rel_Item_Rec.Attr_Date8),
Decode(Nvl(l_Rel_Item_Rec.Attr_Date9,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date9, '!',NULL, l_Rel_Item_Rec.Attr_Date9),
DECODE(Nvl(L_REL_ITEM_REC.ATTR_DATE10,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, L_MTL_REL_ITEM_REC.ATTR_DATE10, '!',NULL, L_REL_ITEM_REC.ATTR_DATE10),
Decode(Nvl(l_Rel_Item_Rec.Planning_Enabled_Flag,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Planning_Enabled_Flag, '!',NULL, l_Rel_Item_Rec.Planning_Enabled_Flag)--,
-- Decode(Nvl(l_Rel_Item_Rec.Last_Update_Date,FND_API.G_MISS_DATE),FND_API.G_MISS_DATE,NULL,l_Rel_Item_Rec.Last_Update_Date),
-- Decode(Nvl(l_Rel_Item_Rec.Last_Updated_By,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,NULL,-999999,NULL,l_Rel_Item_Rec.Last_Updated_By),
-- DECODE(Nvl(L_REL_ITEM_REC.CREATION_DATE,FND_API.G_MISS_DATE),FND_API.G_MISS_DATE,L_MTL_REL_ITEM_REC.CREATION_DATE,l_Rel_Item_Rec.CREATION_DATE),
-- DECODE(Nvl(l_Rel_Item_Rec.Created_By,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_mtl_Rel_Item_rec.Created_By,-999999,NULL,l_Rel_Item_Rec.Created_By),
-- Decode(Nvl(l_Rel_Item_Rec.Last_Update_Login,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,NULL,-999999,NULL,l_Rel_Item_Rec.Last_Update_Login)
INTO l_Rel_Item_Rec.Inventory_Item_Id, l_Rel_Item_Rec.Organization_Id, l_Rel_Item_Rec.Related_Item_Id,
l_Rel_Item_Rec.Relationship_Type_Id,l_Rel_Item_Rec.Related_Item_Id_upd_val,l_Rel_Item_Rec.Relationship_Type_Id_upd_val,
l_Rel_Item_Rec.Reciprocal_Flag, l_Rel_Item_Rec.Start_Date,
l_Rel_Item_Rec.End_Date, l_Rel_Item_Rec.Attr_Context, l_Rel_Item_Rec.Attr_Char1, l_Rel_Item_Rec.Attr_Char2,
l_Rel_Item_Rec.Attr_Char3, l_Rel_Item_Rec.Attr_Char4, l_Rel_Item_Rec.Attr_Char5, l_Rel_Item_Rec.Attr_Char6,
l_Rel_Item_Rec.Attr_Char7, l_Rel_Item_Rec.Attr_Char8, l_Rel_Item_Rec.Attr_Char9, l_Rel_Item_Rec.Attr_Char10,
l_Rel_Item_Rec.Attr_Num1, l_Rel_Item_Rec.Attr_Num2, l_Rel_Item_Rec.Attr_Num3, l_Rel_Item_Rec.Attr_Num4,
l_Rel_Item_Rec.Attr_Num5, l_Rel_Item_Rec.Attr_Num6, l_Rel_Item_Rec.Attr_Num7, l_Rel_Item_Rec.Attr_Num8,
l_Rel_Item_Rec.Attr_Num9, l_Rel_Item_Rec.Attr_Num10, l_Rel_Item_Rec.Attr_Date1, l_Rel_Item_Rec.Attr_Date2,
l_Rel_Item_Rec.Attr_Date3, l_Rel_Item_Rec.Attr_Date4, l_Rel_Item_Rec.Attr_Date5, l_Rel_Item_Rec.Attr_Date6,
L_REL_ITEM_REC.ATTR_DATE7, L_REL_ITEM_REC.ATTR_DATE8, L_REL_ITEM_REC.ATTR_DATE9, L_REL_ITEM_REC.ATTR_DATE10,
l_Rel_Item_Rec.Planning_Enabled_Flag--, l_Rel_Item_Rec.Last_Update_Date, l_Rel_Item_Rec.Last_Updated_By,
-- l_Rel_Item_Rec.Creation_Date,l_Rel_Item_Rec.Created_By, l_Rel_Item_Rec.Last_Update_Login
FROM dual;
SELECT 'x' INTO l_rel_exists
FROM mtl_related_items
WHERE (INVENTORY_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
AND RELATIONSHIP_TYPE_ID= l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL
AND RELATED_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL
AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID)
OR (INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL -- checking for derived related items
AND RELATED_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
AND RELATIONSHIP_TYPE_ID= l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL
AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID
AND RECIPROCAL_FLAG = 'Y');
BEGIN SELECT ROWID INTO Returned_Row_Id
FROM MTL_RELATED_ITEMS
WHERE INVENTORY_ITEM_ID = L_REL_ITEM_REC.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = L_REL_ITEM_REC.ORGANIZATION_ID
AND RELATED_ITEM_ID = L_REL_ITEM_REC.RELATED_ITEM_ID
AND RELATIONSHIP_TYPE_ID = L_REL_ITEM_REC.RELATIONSHIP_TYPE_ID;
MTL_RELATED_ITEMS_PKG.UPDATE_ROW(
X_ROWID => Returned_Row_Id
,X_INVENTORY_ITEM_ID => l_Rel_Item_Rec.Inventory_Item_Id
,X_ORGANIZATION_ID => l_Rel_Item_Rec.Organization_Id
,X_RELATED_ITEM_ID => l_Rel_Item_Rec.Related_Item_Id
,X_RELATIONSHIP_TYPE_ID => l_Rel_Item_Rec.Relationship_Type_Id
,X_RECIPROCAL_FLAG => l_Rel_Item_Rec.Reciprocal_Flag
,X_START_DATE => l_Rel_Item_Rec.Start_Date
,X_END_DATE => l_Rel_Item_Rec.End_Date
,X_ATTR_CONTEXT => l_Rel_Item_Rec.Attr_Context
,X_ATTR_CHAR1 => l_Rel_Item_Rec.Attr_Char1
,X_ATTR_CHAR2 => l_Rel_Item_Rec.Attr_Char2
,X_ATTR_CHAR3 => l_Rel_Item_Rec.Attr_Char3
,X_ATTR_CHAR4 => l_Rel_Item_Rec.Attr_Char4
,X_Attr_Char5 => L_Rel_Item_Rec.Attr_Char5
,X_ATTR_CHAR6 => l_Rel_Item_Rec.Attr_Char6
,X_ATTR_CHAR7 => l_Rel_Item_Rec.Attr_Char7
,X_ATTR_CHAR8 => l_Rel_Item_Rec.Attr_Char8
,X_ATTR_CHAR9 => l_Rel_Item_Rec.Attr_Char9
,X_ATTR_CHAR10 => l_Rel_Item_Rec.Attr_Char10
,X_ATTR_NUM1 => l_Rel_Item_Rec.Attr_Num1
,X_ATTR_NUM2 => l_Rel_Item_Rec.Attr_Num2
,X_ATTR_NUM3 => l_Rel_Item_Rec.Attr_Num3
,X_ATTR_NUM4 => l_Rel_Item_Rec.Attr_Num4
,X_ATTR_NUM5 => l_Rel_Item_Rec.Attr_Num5
,X_Attr_Num6 => l_Rel_Item_Rec.Attr_Num6
,X_ATTR_NUM7 => l_Rel_Item_Rec.Attr_Num7
,X_ATTR_NUM8 => l_Rel_Item_Rec.Attr_Num8
,X_ATTR_NUM9 => l_Rel_Item_Rec.Attr_Num9
,X_ATTR_NUM10 => l_Rel_Item_Rec.Attr_Num10
,X_ATTR_DATE1 => l_Rel_Item_Rec.Attr_Date1
,X_ATTR_DATE2 => l_Rel_Item_Rec.Attr_Date2
,X_ATTR_DATE3 => l_Rel_Item_Rec.Attr_Date3
,X_ATTR_DATE4 => l_Rel_Item_Rec.Attr_Date4
,X_ATTR_DATE5 => l_Rel_Item_Rec.Attr_Date5
,X_ATTR_DATE6 => l_Rel_Item_Rec.Attr_Date6
,X_ATTR_DATE7 => l_Rel_Item_Rec.Attr_Date7
,X_ATTR_DATE8 => l_Rel_Item_Rec.Attr_Date8
,X_ATTR_DATE9 => l_Rel_Item_Rec.Attr_Date9
,X_ATTR_DATE10 => l_Rel_Item_Rec.Attr_Date10
,X_PLANNING_ENABLED_FLAG => l_Rel_Item_Rec.Planning_Enabled_Flag
--,X_LAST_UPDATE_DATE => Nvl(l_Rel_Item_Rec.Last_Update_Date, SYSDATE)
--,X_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
--,X_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
);
,p_dml_type => 'UPDATE'
,p_inventory_item_id => L_REL_ITEM_REC.Inventory_Item_Id
,p_organization_id => L_REL_ITEM_REC.Organization_Id
,p_related_item_id => L_REL_ITEM_REC.Related_Item_Id
,p_relationship_type_id => L_REL_ITEM_REC.Relationship_Type_Id
);
IF l_Rel_Item_Rec.Transaction_Type = 'DELETE' THEN
BEGIN
-- check for record existance --
OPEN mtl_rel_item_cur(l_Rel_Item_Rec.INVENTORY_ITEM_ID
,l_Rel_Item_Rec.RELATED_ITEM_ID
,l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
,l_Rel_Item_Rec.ORGANIZATION_ID);
BEGIN SELECT ROWID INTO Returned_Row_Id
FROM MTL_RELATED_ITEMS
WHERE INVENTORY_ITEM_ID = L_REL_ITEM_REC.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = L_REL_ITEM_REC.ORGANIZATION_ID
AND RELATED_ITEM_ID = L_REL_ITEM_REC.RELATED_ITEM_ID
AND RELATIONSHIP_TYPE_ID = L_REL_ITEM_REC.RELATIONSHIP_TYPE_ID;
Mtl_Related_Items_Pkg.Delete_Row(
X_ROWID => RETURNED_ROW_ID);
,p_dml_type => 'DELETE'
,p_inventory_item_id => L_REL_ITEM_REC.Inventory_Item_Id
,p_organization_id => L_REL_ITEM_REC.Organization_Id
,p_related_item_id => L_REL_ITEM_REC.Related_Item_Id
,p_relationship_type_id => L_REL_ITEM_REC.Relationship_Type_Id
);
MTL_RELATED_ITEMS_PLN_INFO_PKG.DELETE_ROW(
P_PLN_INFO_ID => l_mtl_Rel_Item_Pln_Info_rec.Pln_Info_Id
,P_INVENTORY_ITEM_ID => l_mtl_Rel_Item_Pln_Info_rec.Inventory_Item_Id
,P_ORGANIZATION_ID => l_mtl_Rel_Item_Pln_Info_rec.Organization_Id
,P_RELATED_ITEM_ID => l_mtl_Rel_Item_Pln_Info_rec.Related_Item_Id
,P_RELATIONSHIP_TYPE_ID => l_mtl_Rel_Item_Pln_Info_rec.Relationship_Type_Id);
MTL_RELATED_ITEMS_CUST_REF_PKG.DELETE_ROW(
P_PLN_INFO_ID => l_mtl_Rel_Item_Cust_Ref_rec.Pln_Info_Id
,P_INVENTORY_ITEM_ID => l_mtl_Rel_Item_Cust_Ref_rec.Inventory_Item_Id
,P_ORGANIZATION_ID => l_mtl_Rel_Item_Cust_Ref_rec.Organization_Id
,P_RELATED_ITEM_ID => l_mtl_Rel_Item_Cust_Ref_rec.Related_Item_Id
,P_RELATIONSHIP_TYPE_ID => l_mtl_Rel_Item_Cust_Ref_rec.Relationship_Type_Id
,P_CUSTOMER_ID => l_mtl_Rel_Item_Cust_Ref_rec.Customer_Id
,P_SITE_USE_ID => l_mtl_Rel_Item_Cust_Ref_rec.Site_Use_Id);
l_Rel_Item_Rec.Transaction_Type = 'UPDATE' THEN
-- Planning info and customer reference create/update/delete
IF l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 2 THEN
IF p_Pln_Info_Tbl.FIRST IS NOT NULL THEN
Write_Debug('Checking for associated substitution set...');
l_Pln_Info_Rec.Transaction_Type <> 'UPDATE' AND
l_Pln_Info_Rec.Transaction_Type <> 'DELETE' THEN
x_return_status := FND_API.G_RET_STS_ERROR;
l_Pln_Info_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'CREATE' THEN
BEGIN
SELECT Decode(l_Pln_Info_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,NULL,l_Pln_Info_Rec.Inventory_Item_Id),
Decode(l_Pln_Info_Rec.Organization_Id,FND_API.G_MISS_NUM, NULL, l_Pln_Info_Rec.Organization_Id),
Decode(l_Pln_Info_Rec.Related_Item_Id,FND_API.G_MISS_NUM, NULL, l_Pln_Info_Rec.Related_Item_Id),
Decode(l_Pln_Info_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, NULL, l_Pln_Info_Rec.Relationship_Type_Id),
Decode(l_Pln_Info_Rec.Substitution_Set,FND_API.G_MISS_CHAR, NULL, l_Pln_Info_Rec.Substitution_Set),
Decode(l_Pln_Info_Rec.Partial_Fulfillment_Flag,FND_API.G_MISS_CHAR, NULL, l_Pln_Info_Rec.Partial_Fulfillment_Flag),
Decode(L_Pln_Info_Rec.Start_Date,Fnd_Api.G_Miss_Date, Null, L_Pln_Info_Rec.Start_Date),
Decode(l_Pln_Info_Rec.End_Date,FND_API.G_MISS_DATE, NULL, l_Pln_Info_Rec.End_Date),
Decode(l_Pln_Info_Rec.All_Customers_Flag,FND_API.G_MISS_CHAR, NULL, l_Pln_Info_Rec.All_Customers_Flag)
INTO l_Pln_Info_Rec.Inventory_Item_Id,l_Pln_Info_Rec.Organization_Id,l_Pln_Info_Rec.Related_Item_Id,
l_Pln_Info_Rec.Relationship_Type_Id,l_Pln_Info_Rec.Substitution_Set,l_Pln_Info_Rec.Partial_Fulfillment_Flag,
l_Pln_Info_Rec.Start_Date,l_Pln_Info_Rec.End_Date,l_Pln_Info_Rec.All_Customers_Flag
FROM dual;
IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
-- must have id if updating
IF l_Pln_Info_Rec.PLN_INFO_ID = FND_API.G_MISS_NUM THEN
x_return_status:=FND_API.g_RET_STS_ERROR;
SELECT Decode(l_Pln_Info_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,l_mtl_Rel_Item_Pln_Info_rec.Inventory_Item_Id,l_Pln_Info_Rec.Inventory_Item_Id),
Decode(l_Pln_Info_Rec.Organization_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Pln_Info_rec.Organization_Id, l_Pln_Info_Rec.Organization_Id),
Decode(l_Pln_Info_Rec.Related_Item_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Pln_Info_rec.Related_Item_Id, l_Pln_Info_Rec.Related_Item_Id),
Decode(l_Pln_Info_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Pln_Info_rec.Relationship_Type_Id, l_Pln_Info_Rec.Relationship_Type_Id),
Decode(L_Pln_Info_Rec.Substitution_Set,Fnd_Api.G_Miss_Char, L_Mtl_Rel_Item_Pln_Info_Rec.Substitution_Set, L_Pln_Info_Rec.Substitution_Set),
Decode(l_Pln_Info_Rec.Partial_Fulfillment_Flag,FND_API.G_MISS_CHAR, l_mtl_Rel_Item_Pln_Info_rec.Partial_Fulfillment_Flag, l_Pln_Info_Rec.Partial_Fulfillment_Flag),
Decode(l_Pln_Info_Rec.Start_Date,FND_API.G_MISS_DATE, l_mtl_Rel_Item_Pln_Info_rec.Start_Date, l_Pln_Info_Rec.Start_Date),
Decode(l_Pln_Info_Rec.End_Date,FND_API.G_MISS_DATE, l_mtl_Rel_Item_Pln_Info_rec.End_Date, l_Pln_Info_Rec.End_Date),
Decode(l_Pln_Info_Rec.All_Customers_Flag,FND_API.G_MISS_CHAR, l_mtl_Rel_Item_Pln_Info_rec.All_Customers_Flag, l_Pln_Info_Rec.All_Customers_Flag)
INTO l_Pln_Info_Rec.Inventory_Item_Id,l_Pln_Info_Rec.Organization_Id,l_Pln_Info_Rec.Related_Item_Id,l_Pln_Info_Rec.Relationship_Type_Id,
l_Pln_Info_Rec.Substitution_Set,l_Pln_Info_Rec.Partial_Fulfillment_Flag,l_Pln_Info_Rec.Start_Date,l_Pln_Info_Rec.End_Date,
l_Pln_Info_Rec.All_Customers_Flag
FROM dual;
p_message_name => 'EGO_SELECTED_DATE_INVALID'
,p_application_id => 'EGO'
,p_message_type => 'E'
,p_entity_code => G_Entity_Code
,p_entity_index => l_Pln_Info_Indx
,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
);
p_message_name => 'EGO_SELECTED_DATE_INVALID'
,p_application_id => 'EGO'
,p_message_type => 'E'
,p_entity_code => G_Entity_Code
,p_entity_index => l_Pln_Info_Indx
,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
);
MTL_RELATED_ITEMS_PLN_INFO_PKG.INSERT_ROW(
P_INVENTORY_ITEM_ID => l_Pln_Info_Rec.Inventory_Item_Id
,P_ORGANIZATION_ID => l_Pln_Info_Rec.Organization_Id
,P_RELATED_ITEM_ID => l_Pln_Info_Rec.Related_Item_Id
,P_RELATIONSHIP_TYPE_ID => l_Pln_Info_Rec.Relationship_Type_Id
,P_SUBSTITUTION_SET => l_Pln_Info_Rec.Substitution_Set
,P_PARTIAL_FULFILLMENT_FLAG => l_Pln_Info_Rec.Partial_Fulfillment_Flag
,P_START_DATE => l_Pln_Info_Rec.Start_Date
,P_END_DATE => l_Pln_Info_Rec.End_Date
,P_ALL_CUSTOMERS_FLAG => l_Pln_Info_Rec.All_Customers_Flag
--,P_CREATION_DATE => Nvl(l_Rel_Item_Rec.Creation_Date, SYSDATE)
--,P_CREATED_BY => Nvl(l_Rel_Item_Rec.Created_By, FND_GLOBAL.USER_ID)
--,P_LAST_UPDATE_DATE => Nvl(l_Rel_Item_Rec.Last_Update_Date, SYSDATE)
--,P_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
--,P_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
,P_CREATION_DATE => SYSDATE
,P_CREATED_BY => FND_GLOBAL.USER_ID
,P_LAST_UPDATE_DATE => SYSDATE
,P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
,X_PLN_INFO_ID => returned_pln_info_id
);
IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
-- Note: pln info does not need to check for duplicates since
-- all columns except for pln_info_id can be the same as other rows
BEGIN
-- locking the row
OPEN mtl_rel_item_pln_info_lock_b(l_Pln_Info_Rec.INVENTORY_ITEM_ID
,l_Pln_Info_Rec.RELATED_ITEM_ID
,l_Pln_Info_Rec.RELATIONSHIP_TYPE_ID
,l_Pln_Info_Rec.ORGANIZATION_ID
,l_Pln_Info_Rec.PLN_INFO_ID);
MTL_RELATED_ITEMS_PLN_INFO_PKG.UPDATE_ROW(
P_PLN_INFO_ID => l_Pln_Info_Rec.Pln_Info_Id
,P_INVENTORY_ITEM_ID => l_Pln_Info_Rec.Inventory_Item_Id
,P_ORGANIZATION_ID => l_Pln_Info_Rec.Organization_Id
,P_RELATED_ITEM_ID => l_Pln_Info_Rec.Related_Item_Id
,P_RELATIONSHIP_TYPE_ID => l_Pln_Info_Rec.Relationship_Type_Id
,P_SUBSTITUTION_SET => l_Pln_Info_Rec.Substitution_Set
,P_PARTIAL_FULFILLMENT_FLAG => l_Pln_Info_Rec.Partial_Fulfillment_Flag
,P_START_DATE => l_Pln_Info_Rec.Start_Date
,P_END_DATE => l_Pln_Info_Rec.End_Date
,P_ALL_CUSTOMERS_FLAG => l_Pln_Info_Rec.All_Customers_Flag
-- ,P_LAST_UPDATE_DATE => Nvl(l_Rel_Item_Rec.Last_Update_Date, SYSDATE)
-- ,P_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
-- ,P_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
,P_LAST_UPDATE_DATE =>SYSDATE
,P_LAST_UPDATED_BY =>FND_GLOBAL.USER_ID
,P_LAST_UPDATE_LOGIN =>FND_GLOBAL.LOGIN_ID
);
l_Cust_Ref_Rec.Transaction_Type <> 'UPDATE' AND
l_Cust_Ref_Rec.Transaction_Type <> 'DELETE' THEN
x_return_status := FND_API.G_RET_STS_ERROR;
l_Cust_Ref_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'CREATE' THEN
BEGIN
/* Addition as part of bug#12668577 */
l_Cust_Ref_Rec.Pln_Info_Id:=returned_pln_info_id;
SELECT
Decode(l_Cust_Ref_Rec.Pln_Info_Id,FND_API.G_MISS_NUM,NULL,l_Cust_Ref_Rec.Pln_Info_Id),
Decode(l_Cust_Ref_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,NULL,l_Cust_Ref_Rec.Inventory_Item_Id),
Decode(l_Cust_Ref_Rec.Organization_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Organization_Id),
Decode(l_Cust_Ref_Rec.Related_Item_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Related_Item_Id),
Decode(l_Cust_Ref_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Relationship_Type_Id),
Decode(l_Cust_Ref_Rec.Customer_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Customer_Id),
Decode(l_Cust_Ref_Rec.Site_Use_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Site_Use_Id),
Decode(l_Cust_Ref_Rec.Start_Date,FND_API.G_MISS_DATE, NULL, l_Cust_Ref_Rec.Start_Date),
Decode(l_Cust_Ref_Rec.End_Date,FND_API.G_MISS_DATE, NULL, l_Cust_Ref_Rec.End_Date)
INTO l_Cust_Ref_Rec.Pln_Info_Id,l_Cust_Ref_Rec.Inventory_Item_Id,l_Cust_Ref_Rec.Organization_Id,l_Cust_Ref_Rec.Related_Item_Id,
l_Cust_Ref_Rec.Relationship_Type_Id,l_Cust_Ref_Rec.Customer_Id,l_Cust_Ref_Rec.Site_Use_Id,
l_Cust_Ref_Rec.Start_Date,l_Cust_Ref_Rec.End_Date
FROM dual;
SELECT 'x' INTO l_cust_ref_exists
FROM mtl_related_items_cust_ref
WHERE Inventory_Item_Id = l_Cust_Ref_Rec.INVENTORY_ITEM_ID
AND Related_Item_Id = l_Cust_Ref_Rec.RELATED_ITEM_ID
AND Relationship_Type_Id = l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
AND Pln_Info_Id = l_Cust_Ref_Rec.PLN_INFO_ID
AND Organization_Id = l_Cust_Ref_Rec.ORGANIZATION_ID
AND Customer_Id = l_Cust_Ref_Rec.CUSTOMER_ID
AND Site_Use_Id = l_Cust_Ref_Rec.SITE_USE_ID;
IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
BEGIN
OPEN mtl_cust_ref_cur(l_Cust_Ref_Rec.INVENTORY_ITEM_ID
,l_Cust_Ref_Rec.RELATED_ITEM_ID
,l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
,l_Cust_Ref_Rec.ORGANIZATION_ID
,l_Cust_Ref_Rec.PLN_INFO_ID
,l_Cust_Ref_Rec.CUSTOMER_ID
,l_Cust_Ref_Rec.SITE_USE_ID);
SELECT Decode(l_Cust_Ref_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,l_mtl_Rel_Item_Cust_Ref_rec.Inventory_Item_Id,l_Cust_Ref_Rec.Inventory_Item_Id),
Decode(l_Cust_Ref_Rec.Organization_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Organization_Id, l_Cust_Ref_Rec.Organization_Id),
Decode(l_Cust_Ref_Rec.Related_Item_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Related_Item_Id, l_Cust_Ref_Rec.Related_Item_Id),
Decode(l_Cust_Ref_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Relationship_Type_Id, l_Cust_Ref_Rec.Relationship_Type_Id),
Decode(l_Cust_Ref_Rec.Pln_Info_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Pln_Info_Id, l_Cust_Ref_Rec.Pln_Info_Id),
Decode(l_Cust_Ref_Rec.Customer_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Customer_Id, l_Cust_Ref_Rec.Customer_Id),
Decode(l_Cust_Ref_Rec.Site_Use_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Site_Use_Id, l_Cust_Ref_Rec.Site_Use_Id),
Decode(l_Cust_Ref_Rec.Start_Date,FND_API.G_MISS_DATE, l_mtl_Rel_Item_Cust_Ref_rec.Start_Date, l_Cust_Ref_Rec.Start_Date),
Decode(l_Cust_Ref_Rec.End_Date,FND_API.G_MISS_DATE, l_mtl_Rel_Item_Cust_Ref_rec.End_Date, l_Cust_Ref_Rec.End_Date)
Into l_Cust_Ref_Rec.Inventory_Item_Id,l_Cust_Ref_Rec.Organization_Id,l_Cust_Ref_Rec.Related_Item_Id,l_Cust_Ref_Rec.Relationship_Type_Id,
l_Cust_Ref_Rec.Pln_Info_Id,l_Cust_Ref_Rec.Customer_Id,l_Cust_Ref_Rec.Site_Use_Id,l_Cust_Ref_Rec.Start_Date,l_Cust_Ref_Rec.End_Date
FROM dual;
SELECT 'x' INTO l_cust_ref_rel_exists
FROM
(Select PARTY_NAME customer_name, cust_Account_id customer_id
from hz_parties p , hz_cust_Accounts a
where a.party_id = p.party_id),
hz_cust_site_uses_all su,
hz_party_sites party_site,
hz_loc_assignments loc_assign,
hz_locations loc,
hz_cust_acct_sites_all acct_site
WHERE customer_id = l_Cust_Ref_Rec.CUSTOMER_ID AND
site_use_id = l_Cust_Ref_Rec.SITE_USE_ID AND
acct_site.cust_account_id = customer_id AND
acct_site.party_site_id = party_site.party_site_id AND
loc.location_id = party_site.location_id AND
loc.location_id = loc_assign.location_id AND
NVL ( acct_site.org_id, -99 ) = NVL (loc_assign.org_id, -99) AND
acct_site.cust_acct_site_id = su.cust_acct_site_id AND
su.site_use_code = 'SHIP_TO';
p_message_name => 'EGO_SELECTED_DATE_INVALID'
,P_Application_Id => 'EGO'
,p_token_tbl => l_Token_Tbl
,p_message_type => 'E'
,p_entity_code => G_Entity_Code
,p_entity_index => l_Cust_Ref_Indx
,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
);
p_message_name => 'EGO_SELECTED_DATE_INVALID'
,p_application_id => 'EGO'
,p_message_type => 'E'
,p_entity_code => G_Entity_Code
,p_entity_index => l_Cust_Ref_Indx
,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
);
MTL_RELATED_ITEMS_CUST_REF_PKG.INSERT_ROW(
P_PLN_INFO_ID => l_Cust_Ref_Rec.Pln_Info_Id
,P_INVENTORY_ITEM_ID => l_Cust_Ref_Rec.Inventory_Item_Id
,P_ORGANIZATION_ID => l_Cust_Ref_Rec.Organization_Id
,P_RELATED_ITEM_ID => l_Cust_Ref_Rec.Related_Item_Id
,P_RELATIONSHIP_TYPE_ID => l_Cust_Ref_Rec.Relationship_Type_Id
,P_CUSTOMER_ID => l_Cust_Ref_Rec.Customer_Id
,P_SITE_USE_ID => l_Cust_Ref_Rec.Site_Use_Id
,P_START_DATE => l_Cust_Ref_Rec.Start_Date
,P_END_DATE => l_Cust_Ref_Rec.End_Date
--,P_CREATION_DATE => Nvl(l_Rel_Item_Rec.Creation_Date, SYSDATE)
--,P_CREATED_BY => Nvl(l_Rel_Item_Rec.Created_By, FND_GLOBAL.USER_ID)
--,P_Last_Update_Date => Nvl(L_Rel_Item_Rec.Last_Update_Date, Sysdate)
--,P_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
--,P_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
,P_CREATION_DATE => SYSDATE
,P_CREATED_BY => FND_GLOBAL.USER_ID
,P_Last_Update_Date => Sysdate
,P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
);
IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
BEGIN
-- locking the cust ref row
OPEN mtl_rel_item_cust_ref_lock_b(l_Cust_Ref_Rec.INVENTORY_ITEM_ID
,l_Cust_Ref_Rec.RELATED_ITEM_ID
,l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
,l_Cust_Ref_Rec.ORGANIZATION_ID
,l_Cust_Ref_Rec.PLN_INFO_ID
,l_Cust_Ref_Rec.CUSTOMER_ID
,l_Cust_Ref_Rec.SITE_USE_ID);
MTL_RELATED_ITEMS_CUST_REF_PKG.UPDATE_ROW(
P_PLN_INFO_ID => l_Cust_Ref_Rec.Pln_Info_Id
,P_INVENTORY_ITEM_ID => l_Cust_Ref_Rec.Inventory_Item_Id
,P_ORGANIZATION_ID => l_Cust_Ref_Rec.Organization_Id
,P_RELATED_ITEM_ID => l_Cust_Ref_Rec.Related_Item_Id
,P_RELATIONSHIP_TYPE_ID => l_Cust_Ref_Rec.Relationship_Type_Id
,P_CUSTOMER_ID => l_Cust_Ref_Rec.Customer_Id
,P_SITE_USE_ID => l_Cust_Ref_Rec.Site_Use_Id
,P_START_DATE => l_Cust_Ref_Rec.Start_Date
,P_END_DATE => l_Cust_Ref_Rec.End_Date
--,P_LAST_UPDATE_DATE => Nvl(l_Rel_Item_Rec.Last_Update_Date, SYSDATE)
--,P_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
--,P_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
,P_LAST_UPDATE_DATE => SYSDATE
,P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
);
IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'DELETE' THEN
BEGIN
OPEN mtl_cust_ref_cur(l_Cust_Ref_Rec.INVENTORY_ITEM_ID
,l_Cust_Ref_Rec.RELATED_ITEM_ID
,l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
,l_Cust_Ref_Rec.ORGANIZATION_ID
,l_Cust_Ref_Rec.PLN_INFO_ID
,l_Cust_Ref_Rec.CUSTOMER_ID
,l_Cust_Ref_Rec.SITE_USE_ID);
MTL_RELATED_ITEMS_CUST_REF_PKG.DELETE_ROW(
P_PLN_INFO_ID => l_Cust_Ref_Rec.Pln_Info_Id
,P_INVENTORY_ITEM_ID => l_Cust_Ref_Rec.Inventory_Item_Id
,P_ORGANIZATION_ID => l_Cust_Ref_Rec.Organization_Id
,P_RELATED_ITEM_ID => l_Cust_Ref_Rec.Related_Item_Id
,P_RELATIONSHIP_TYPE_ID => l_Cust_Ref_Rec.Relationship_Type_Id
,P_CUSTOMER_ID => l_Cust_Ref_Rec.Customer_Id
,P_SITE_USE_ID => l_Cust_Ref_Rec.Site_Use_Id);
END IF; -- cust ref delete transaction type
END IF; -- end if update/create planning info rec
IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'DELETE' THEN
-- must check whether cust ref exists for this substitution set
-- if so then, cust ref must be delete first prior to deletion of pln info rec
-- must have id if deleting
IF l_Pln_Info_Rec.PLN_INFO_ID = FND_API.G_MISS_NUM THEN
x_return_status:=FND_API.g_RET_STS_ERROR;
SELECT 'x' INTO l_cust_ref_exists
FROM mtl_related_items_cust_ref
WHERE (INVENTORY_ITEM_ID = l_Pln_Info_Rec.INVENTORY_ITEM_ID
AND RELATIONSHIP_TYPE_ID= l_Pln_Info_Rec.RELATIONSHIP_TYPE_ID
AND RELATED_ITEM_ID = l_Pln_Info_Rec.RELATED_ITEM_ID
AND ORGANIZATION_ID = l_Pln_Info_Rec.ORGANIZATION_ID
AND PLN_INFO_ID = l_Pln_Info_Rec.PLN_INFO_ID
);
MTL_RELATED_ITEMS_PLN_INFO_PKG.DELETE_ROW(
P_PLN_INFO_ID => l_Pln_Info_Rec.Pln_Info_Id
,P_INVENTORY_ITEM_ID => l_Pln_Info_Rec.Inventory_Item_Id
,P_ORGANIZATION_ID => l_Pln_Info_Rec.Organization_Id
,P_RELATED_ITEM_ID => l_Pln_Info_Rec.Related_Item_Id
,P_RELATIONSHIP_TYPE_ID => l_Pln_Info_Rec.Relationship_Type_Id);
End If; -- create or update of related item
SELECT A.ROWID,A.*
FROM MTL_RELATED_ITEMS_INTERFACE A
WHERE DECODE(C_DATA_SET_ID, NULL, -1, A.SET_PROCESS_ID) = NVL(C_DATA_SET_ID, -1)
AND a.PROCESS_FLAG = 1;
UPDATE MTL_RELATED_ITEMS_INTERFACE
SET TRANSACTION_ID = MTL_RELATED_ITEMS_INTERFACE_S.NEXTVAL,
REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
WHERE ROWID = l_mtl_Rel_Item_rec.ROWID;
SELECT mtp.organization_id into p_organization_id
FROM mtl_parameters mtp
WHERE mtp.organization_code = l_mtl_Rel_Item_rec.ORGANIZATION_CODE;
SELECT msk.inventory_item_id into p_related_item_id
FROM mtl_system_items_b_kfv msk
WHERE msk.concatenated_segments = l_mtl_Rel_Item_rec.RELATED_ITEM_NUMBER
and rownum=1;
SELECT msk.inventory_item_id into p_inventory_item_id
FROM mtl_system_items_b_kfv msk
WHERE msk.concatenated_segments = l_mtl_Rel_Item_rec.ITEM_NUMBER
and organization_id=l_mtl_Rel_Item_rec.ORGANIZATION_ID;
UPDATE MTL_RELATED_ITEMS_INTERFACE
SET process_flag = 3
WHERE Transaction_Id = l_mtl_Rel_Item_rec.Transaction_Id;
UPDATE MTL_RELATED_ITEMS_INTERFACE
SET process_flag = 7
WHERE Transaction_Id = l_mtl_Rel_Item_rec.Transaction_Id;
DELETE FROM MTL_RELATED_ITEMS_INTERFACE
WHERE process_flag = l_process_flag_7
AND set_process_id=p_data_set_id
AND rownum < l_rownum;