[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Relationship(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_rel_type_code IN VARCHAR2 ,
p_description IN VARCHAR2 := NULL ,
p_start_date_active IN DATE := NULL ,
p_end_date_active IN DATE := NULL
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Relationship';
SAVEPOINT Insert_Relationship_PVT;
IBE_UTIL.debug('IBE_Prod_Relation_PVT.Insert_Relationship(+)');
fnd_lookup_values_pkg.insert_row
(
X_ROWID => l_rowid,
X_LOOKUP_TYPE => 'IBE_RELATIONSHIP_TYPES',
X_VIEW_APPLICATION_ID => l_view_application_id,
X_LOOKUP_CODE => p_rel_type_code,
X_TAG => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => p_start_date_active,
X_END_DATE_ACTIVE => p_end_date_active,
X_TERRITORY_CODE => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_MEANING => p_rel_type_code,
X_DESCRIPTION => p_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
);
IBE_UTIL.debug('Insert statement failed.');
IBE_UTIL.debug('Insert statement failed.');
IBE_UTIL.debug('IBE_Prod_Relation_PVT.Insert_Relationship(-)');
ROLLBACK TO Insert_Relationship_PVT;
ROLLBACK TO Insert_Relationship_PVT;
ROLLBACK TO Insert_Relationship_PVT;
END Insert_Relationship;
PROCEDURE Update_Relationship(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_rel_type_code IN VARCHAR2 ,
p_description IN VARCHAR2 ,
p_start_date IN DATE ,
p_end_date IN DATE
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Relationship';
SAVEPOINT Update_Relationship_PVT;
IBE_UTIL.debug('IBE_Prod_Relation_PVT.Update_Relationship(+)');
fnd_lookup_values_pkg.update_row
(
X_LOOKUP_TYPE => 'IBE_RELATIONSHIP_TYPES',
X_VIEW_APPLICATION_ID => l_view_application_id,
X_LOOKUP_CODE => p_rel_type_code,
X_TAG => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => p_start_date,
X_END_DATE_ACTIVE => p_end_date,
X_TERRITORY_CODE => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_MEANING => p_rel_type_code,
X_DESCRIPTION => p_description,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
);
IBE_UTIL.debug('Update statement failed.');
IBE_UTIL.debug('Update statement failed.');
IBE_UTIL.debug('IBE_Prod_Relation_PVT.Update_Relationship(-)');
ROLLBACK TO Update_Relationship_PVT;
ROLLBACK TO Update_Relationship_PVT;
ROLLBACK TO Update_Relationship_PVT;
END Update_Relationship;
PROCEDURE Update_Relationship_Detail(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_rel_type_code IN VARCHAR2 ,
p_meaning IN VARCHAR2 ,
p_description IN VARCHAR2 ,
p_start_date IN DATE ,
p_end_date IN DATE
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Relationship_Detail';
SAVEPOINT Update_Relationship_Detail_PVT;
IBE_UTIL.debug('IBE_Prod_Relation_PVT.Update_Relationship_Detail(+)');
fnd_lookup_values_pkg.update_row
(
X_LOOKUP_TYPE => 'IBE_RELATIONSHIP_TYPES',
X_VIEW_APPLICATION_ID => l_view_application_id,
X_LOOKUP_CODE => p_rel_type_code,
X_TAG => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => p_start_date,
X_END_DATE_ACTIVE => p_end_date,
X_TERRITORY_CODE => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_MEANING => p_meaning,
X_DESCRIPTION => p_description,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
);
IBE_UTIL.debug('Update statement failed.');
IBE_UTIL.debug('Update statement failed.');
IBE_UTIL.debug('IBE_Prod_Relation_PVT.Update_Relationship_Detail(-)');
ROLLBACK TO Update_Relationship_Detail_PVT;
ROLLBACK TO Update_Relationship_Detail_PVT;
ROLLBACK TO Update_Relationship_Detail_PVT;
END Update_Relationship_Detail;
PROCEDURE Delete_Relationships(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_rel_type_code_tbl IN JTF_Varchar2_Table_100
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Relationships';
SAVEPOINT Delete_Relationship_PVT;
IBE_UTIL.debug('IBE_Prod_Relation_PVT.Delete_Relationship(+)');
fnd_lookup_values_pkg.delete_row
(
X_LOOKUP_TYPE => 'IBE_RELATIONSHIP_TYPES',
X_VIEW_APPLICATION_ID => l_view_application_id,
X_LOOKUP_CODE => p_rel_type_code_tbl(i)
);
IBE_UTIL.debug('Delete statement failed.');
IBE_UTIL.debug('Delete statement failed.');
DELETE
FROM ibe_ct_relation_rules
WHERE relation_type_code = p_rel_type_code_tbl(i);
DELETE
FROM ibe_ct_related_items
WHERE relation_type_code = p_rel_type_code_tbl(i);
DELETE
FROM ibe_ct_rel_exclusions
WHERE relation_type_code = p_rel_type_code_tbl(i);
IBE_UTIL.debug('IBE_Prod_Relation_PVT.Delete_Relationship(-)');
ROLLBACK TO Delete_Relationship_PVT;
ROLLBACK TO Delete_Relationship_PVT;
ROLLBACK TO Delete_Relationship_PVT;
END Delete_Relationships;
INSERT INTO IBE_CT_REL_EXCLUSIONS(
organization_id, relation_type_code, inventory_item_id,
related_item_id, object_version_number, created_by,
creation_date, last_updated_by, last_update_date
)
VALUES(
L_ORGANIZATION_ID, p_rel_type_code, p_inventory_item_id_tbl(i),
p_related_item_id_tbl(i), 1, L_USER_ID,
SYSDATE, L_USER_ID, SYSDATE
);
DELETE
FROM IBE_CT_REL_EXCLUSIONS
WHERE relation_type_code = p_rel_type_code
AND inventory_item_id = p_inventory_item_id_tbl(i)
AND related_item_id = p_related_item_id_tbl(i)
AND organization_id = L_ORGANIZATION_ID; --Bug 2922902
PROCEDURE Insert_Related_Items_Rows(
p_rel_type_code IN VARCHAR2,
p_rel_rule_id IN NUMBER ,
p_origin_object_type IN VARCHAR2,
p_dest_object_type IN VARCHAR2,
p_origin_object_id IN NUMBER ,
p_dest_object_id IN NUMBER
) IS
BEGIN
Insert_Related_Items_Rows(
p_rel_type_code,
p_rel_rule_id,
p_origin_object_type,
p_dest_object_type,
p_origin_object_id,
p_dest_object_id,
L_ORGANIZATION_ID
);
END Insert_Related_Items_Rows;
PROCEDURE Insert_Related_Items_Rows(
p_rel_type_code IN VARCHAR2,
p_rel_rule_id IN NUMBER ,
p_origin_object_type IN VARCHAR2,
p_dest_object_type IN VARCHAR2,
p_origin_object_id IN NUMBER ,
p_dest_object_id IN NUMBER ,
p_organization_id IN NUMBER
)
IS
L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
INSERT INTO IBE_CT_RELATED_ITEMS(
organization_id, relation_type_code, relation_rule_id, inventory_item_id,
related_item_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date
)
SELECT JDSI1.organization_id, p_rel_type_code, p_rel_rule_id, JDSI1.inventory_item_id,
JDSI2.inventory_item_id, 1, L_USER_ID, SYSDATE,
L_USER_ID, SYSDATE
FROM IBE_DSP_SECTION_ITEMS JDSI1,
IBE_DSP_SECTION_ITEMS JDSI2
WHERE JDSI1.section_id = p_origin_object_id
AND JDSI2.section_id = p_dest_object_id
AND JDSI1.organization_id = JDSI2.organization_id; --Bug 2922902
INSERT INTO IBE_CT_RELATED_ITEMS(
organization_id, relation_type_code, relation_rule_id, inventory_item_id,
related_item_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date
)
SELECT DISTINCT MIC.organization_id, p_rel_type_code, p_rel_rule_id,
JDSI.inventory_item_id, MIC.inventory_item_id, 1,
L_USER_ID, SYSDATE, L_USER_ID, SYSDATE
FROM IBE_DSP_SECTION_ITEMS JDSI,
MTL_ITEM_CATEGORIES MIC
WHERE JDSI.section_id = p_origin_object_id
AND MIC.organization_id = JDSI.organization_id --Bug 2922902
AND MIC.category_id = p_dest_object_id
AND MIC.category_set_id = l_category_set_id_str; --Bug 2630696
INSERT INTO IBE_CT_RELATED_ITEMS(
organization_id, relation_type_code, relation_rule_id, inventory_item_id,
related_item_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date
)
SELECT p_organization_id, p_rel_type_code, p_rel_rule_id, JDSI.inventory_item_id,
p_dest_object_id, 1, L_USER_ID, SYSDATE,
L_USER_ID, SYSDATE
FROM IBE_DSP_SECTION_ITEMS JDSI
WHERE JDSI.section_id = p_origin_object_id
AND JDSI.organization_id = p_organization_id; --Bug 2922902
INSERT INTO IBE_CT_RELATED_ITEMS(
organization_id, relation_type_code, relation_rule_id, inventory_item_id,
related_item_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date
)
SELECT DISTINCT MIC.organization_id, p_rel_type_code, p_rel_rule_id, MIC.inventory_item_id,
JDSI.inventory_item_id, 1, L_USER_ID, SYSDATE,
L_USER_ID, SYSDATE
FROM MTL_ITEM_CATEGORIES MIC,
IBE_DSP_SECTION_ITEMS JDSI
WHERE MIC.organization_id = JDSI.organization_id --Bug 2922902
AND MIC.category_id = p_origin_object_id
AND JDSI.section_id = p_dest_object_id
AND MIC.category_set_id = l_category_set_id_str; --Bug 2630696
INSERT INTO IBE_CT_RELATED_ITEMS(
organization_id, relation_type_code, relation_rule_id, inventory_item_id,
related_item_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date
)
SELECT DISTINCT MIC1.organization_id, p_rel_type_code, p_rel_rule_id, MIC1.inventory_item_id,
MIC2.inventory_item_id, 1, L_USER_ID, SYSDATE,
L_USER_ID, SYSDATE
FROM MTL_ITEM_CATEGORIES MIC1,
MTL_ITEM_CATEGORIES MIC2
WHERE MIC1.organization_id = MIC2.organization_id --Bug 2922902
AND MIC1.category_id = p_origin_object_id
AND MIC2.category_id = p_dest_object_id
AND MIC1.organization_id = MIC2.organization_id --Bug 2630696
AND MIC1.category_set_id = MIC2.category_set_id --Bug 2630696
AND MIC1.category_set_id = l_category_set_id_str; --bug 2630696
INSERT INTO IBE_CT_RELATED_ITEMS(
organization_id, relation_type_code, relation_rule_id, inventory_item_id,
related_item_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date
)
SELECT DISTINCT p_organization_id, p_rel_type_code, p_rel_rule_id, MIC.inventory_item_id,
p_dest_object_id, 1, L_USER_ID, SYSDATE,
L_USER_ID, SYSDATE
FROM MTL_ITEM_CATEGORIES MIC
WHERE MIC.organization_id = p_organization_id
AND MIC.category_id = p_origin_object_id
AND MIC.category_set_id = l_category_set_id_str; --Bug 2630696
INSERT INTO IBE_CT_RELATED_ITEMS(
organization_id, relation_type_code, relation_rule_id, inventory_item_id,
related_item_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date
)
SELECT p_organization_id, p_rel_type_code, p_rel_rule_id, p_origin_object_id,
JDSI.inventory_item_id, 1, L_USER_ID, SYSDATE,
L_USER_ID, SYSDATE
FROM IBE_DSP_SECTION_ITEMS JDSI
WHERE JDSI.section_id = p_dest_object_id
AND JDSI.organization_id = p_organization_id; --Bug 2922902
INSERT INTO IBE_CT_RELATED_ITEMS(
organization_id, relation_type_code, relation_rule_id, inventory_item_id,
related_item_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date
)
SELECT DISTINCT p_organization_id, p_rel_type_code, p_rel_rule_id, p_origin_object_id,
MIC.inventory_item_id, 1, L_USER_ID, SYSDATE,
L_USER_ID, SYSDATE
FROM MTL_ITEM_CATEGORIES MIC
WHERE MIC.organization_id = p_organization_id
AND MIC.category_id = p_dest_object_id
AND MIC.category_set_id = l_category_set_id_str; --Bug 2630696
INSERT INTO IBE_CT_RELATED_ITEMS(
organization_id, relation_type_code, relation_rule_id, inventory_item_id,
related_item_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date
)
VALUES(
p_organization_id, p_rel_type_code, p_rel_rule_id, p_origin_object_id,
p_dest_object_id, 1, L_USER_ID, SYSDATE,
L_USER_ID, SYSDATE
);
END Insert_Related_Items_Rows;
DELETE
FROM IBE_CT_REL_EXCLUSIONS ICRE
WHERE NOT EXISTS( SELECT NULL
FROM IBE_CT_RELATED_ITEMS ICRI
WHERE ICRI.relation_type_code = ICRE.relation_type_code
AND ICRI.inventory_item_id = ICRE.inventory_item_id
AND ICRI.organization_id = ICRE.organization_id --Bug 2922902
AND ICRI.related_item_id = ICRE.related_item_id );
PROCEDURE Item_Category_Inserted(
p_category_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
IS
TYPE rel_rule_csr_type IS REF CURSOR;
OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, dest_object_type, dest_object_id ' ||
'FROM ibe_ct_relation_rules ' ||
'WHERE origin_object_type = ''C'' ' ||
'AND origin_object_id = :category_id '
USING p_category_id;
Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
p_rel_rule_id => l_rel_rule_id ,
p_origin_object_type => 'I' ,
p_dest_object_type => l_object_type ,
p_origin_object_id => p_inventory_item_id,
p_dest_object_id => l_object_id ,
/*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
p_rel_rule_id => l_rel_rule_id ,
p_origin_object_type => 'I' ,
p_dest_object_type => l_object_type ,
p_origin_object_id => p_inventory_item_id,
p_dest_object_id => l_object_id ,
/*Bug 2922902*/ p_organization_id => p_organization_id);
OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, origin_object_type, origin_object_id ' ||
'FROM ibe_ct_relation_rules ' ||
'WHERE dest_object_type = ''C'' ' ||
'AND dest_object_id = :category_id '
USING p_category_id;
Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
p_rel_rule_id => l_rel_rule_id ,
p_origin_object_type => l_object_type ,
p_dest_object_type => 'I' ,
p_origin_object_id => l_object_id ,
p_dest_object_id => p_inventory_item_id ,
/*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
p_rel_rule_id => l_rel_rule_id ,
p_origin_object_type => l_object_type ,
p_dest_object_type => 'I' ,
p_origin_object_id => l_object_id ,
p_dest_object_id => p_inventory_item_id ,
/*Bug 2922902*/ p_organization_id => p_organization_id);
END Item_Category_Inserted;
PROCEDURE Item_Section_Inserted(
p_section_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
IS
TYPE rel_rule_csr_type IS REF CURSOR;
OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, dest_object_type, dest_object_id ' ||
'FROM ibe_ct_relation_rules ' ||
'WHERE origin_object_type = ''S'' ' ||
'AND origin_object_id = :section_id ' ||
'AND relation_type_code <> ''AUTOPLACEMENT'' '
USING p_section_id;
Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
p_rel_rule_id => l_rel_rule_id ,
p_origin_object_type => 'I' ,
p_dest_object_type => l_object_type ,
p_origin_object_id => p_inventory_item_id,
p_dest_object_id => l_object_id ,
/*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
p_rel_rule_id => l_rel_rule_id ,
p_origin_object_type => 'I' ,
p_dest_object_type => l_object_type ,
p_origin_object_id => p_inventory_item_id,
p_dest_object_id => l_object_id ,
/*Bug 2922902*/ p_organization_id => p_organization_id);
OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, origin_object_type, origin_object_id ' ||
'FROM ibe_ct_relation_rules ' ||
'WHERE dest_object_type = ''S'' ' ||
'AND dest_object_id = :section_id '
USING p_section_id;
Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
p_rel_rule_id => l_rel_rule_id ,
p_origin_object_type => l_object_type ,
p_dest_object_type => 'I' ,
p_origin_object_id => l_object_id ,
p_dest_object_id => p_inventory_item_id,
/*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
p_rel_rule_id => l_rel_rule_id ,
p_origin_object_type => l_object_type ,
p_dest_object_type => 'I' ,
p_origin_object_id => l_object_id ,
p_dest_object_id => p_inventory_item_id,
/*Bug 2922902*/ p_organization_id => p_organization_id);
END Item_Section_Inserted;
PROCEDURE Item_Category_Deleted(
p_category_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
IS
TYPE rel_rule_csr_type IS REF CURSOR;
OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, ' ||
'relation_rule_id ' ||
'FROM ibe_ct_relation_rules ' ||
'WHERE origin_object_type = ''C'' ' ||
'AND origin_object_id = :category_id '
USING p_category_id;
DELETE IBE_CT_RELATED_ITEMS
WHERE relation_type_code = l_relation_type_code
AND relation_rule_id = l_rel_rule_id
AND inventory_item_id = p_inventory_item_id
AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
DELETE IBE_CT_RELATED_ITEMS
WHERE relation_type_code = l_relation_type_code
AND relation_rule_id = l_rel_rule_id
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id; --Bug 2922902
OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, ' ||
'relation_rule_id ' ||
'FROM ibe_ct_relation_rules ' ||
'WHERE dest_object_type = ''C'' ' ||
'AND dest_object_id = :category_id '
USING p_category_id;
DELETE IBE_CT_RELATED_ITEMS
WHERE relation_type_code = l_relation_type_code
AND relation_rule_id = l_rel_rule_id
AND related_item_id = p_inventory_item_id
AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
DELETE IBE_CT_RELATED_ITEMS
WHERE relation_type_code = l_relation_type_code
AND relation_rule_id = l_rel_rule_id
AND related_item_id = p_inventory_item_id
AND organization_id = p_organization_id; --Bug 2922902
END Item_Category_Deleted;
PROCEDURE Item_Section_Deleted(
p_section_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
IS
TYPE rel_rule_csr_type IS REF CURSOR;
OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, '||
'relation_rule_id ' ||
'FROM ibe_ct_relation_rules ' ||
'WHERE origin_object_type = ''S'' ' ||
'AND origin_object_id = :section_id ' ||
'AND relation_type_code <> ''AUTOPLACEMENT'' '
USING p_section_id;
DELETE IBE_CT_RELATED_ITEMS
WHERE relation_type_code = l_relation_type_code
AND relation_rule_id = l_rel_rule_id
AND inventory_item_id = p_inventory_item_id
AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
DELETE IBE_CT_RELATED_ITEMS
WHERE relation_type_code = l_relation_type_code
AND relation_rule_id = l_rel_rule_id
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id; --Bug 2922902
OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, ' ||
'relation_rule_id ' ||
'FROM ibe_ct_relation_rules ' ||
'WHERE dest_object_type = ''S'' ' ||
'AND dest_object_id = :section_id '
USING p_section_id;
DELETE IBE_CT_RELATED_ITEMS
WHERE relation_type_code = l_relation_type_code
AND relation_rule_id = l_rel_rule_id
AND related_item_id = p_inventory_item_id
AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
DELETE IBE_CT_RELATED_ITEMS
WHERE relation_type_code = l_relation_type_code
AND relation_rule_id = l_rel_rule_id
AND related_item_id = p_inventory_item_id
AND organization_id = p_organization_id; --Bug 2922902
END Item_Section_Deleted;
PROCEDURE Category_Deleted(
p_category_id IN NUMBER)
IS
BEGIN
-- 1. Delete all the rules where origin_object_type is Category
-- and origin_object_id is p_section_id
DELETE IBE_CT_RELATION_RULES
WHERE origin_object_type = 'C'
AND origin_object_id = p_category_id;
DELETE IBE_CT_RELATION_RULES
WHERE dest_object_type = 'C'
AND dest_object_id = p_category_id;
END Category_Deleted;
PROCEDURE Section_Deleted(p_section_id IN NUMBER)
IS
BEGIN
-- 1. Delete all the rules where origin_object_type is Section
-- and origin_object_id is p_section_id
DELETE IBE_CT_RELATION_RULES
WHERE origin_object_type = 'S'
AND origin_object_id = p_section_id;
DELETE IBE_CT_RELATION_RULES
WHERE dest_object_type = 'S'
AND dest_object_id = p_section_id;
END Section_Deleted;
PROCEDURE Item_Inserted(p_inventory_item_id IN NUMBER)
IS
BEGIN
NULL;
END Item_Inserted;
PROCEDURE Item_Deleted(
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER
)
IS
BEGIN
-- 1. Remove all the rules that have the deleted item
-- as an origin object
DELETE
FROM ibe_ct_relation_rules
WHERE origin_object_type = 'I'
AND origin_object_id = p_inventory_item_id;
DELETE
FROM ibe_ct_relation_rules
WHERE dest_object_type = 'I'
AND dest_object_id = p_inventory_item_id;
DELETE
FROM IBE_CT_RELATED_ITEMS
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
DELETE
FROM IBE_CT_RELATED_ITEMS
WHERE related_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
END Item_Deleted;