[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
inventory_item_id ,
inventory_org_id
FROM ahl_mtl_items_ou_v
WHERE organization_name = p_org_name
AND concatenated_segments = p_item_name
AND inventory_item_flag = p_item_flag
AND NVL(start_date_active, sysdate) <= sysdate
AND NVL(end_date_active, sysdate + 1) > sysdate
AND DECODE(p_item_flag,'N',purchasing_enabled_flag,'Y') = 'Y'
AND DECODE(p_item_flag,'N',NVL(outside_operation_flag, 'N'),'N') = 'N';
SELECT
mtl.inventory_item_id,
mtl.organization_id inventory_org_id
FROM mtl_system_items_kfv mtl, inv_organization_info_v org, hr_all_organization_units hou
WHERE hou.name = p_org_name
AND mtl.concatenated_segments = p_item_name
AND mtl.organization_id = org.organization_id
AND hou.organization_id = org.organization_id
AND NVL (org.operating_unit, mo_global.get_current_org_id ()) = mo_global.get_current_org_id()
AND mtl.inventory_item_flag = p_item_flag
AND NVL(mtl.start_date_active, sysdate) <= sysdate
AND NVL(mtl.end_date_active, sysdate + 1) > sysdate
AND DECODE(p_item_flag,'N',mtl.purchasing_enabled_flag,'Y') = 'Y'
AND DECODE(p_item_flag,'N',NVL(mtl.outside_operation_flag, 'N'),'N') = 'N';
SELECT
inventory_item_id ,
inventory_org_id
FROM ahl_mtl_items_ou_v
WHERE inventory_org_id = p_org_id
AND inventory_item_id = p_item_id
AND inventory_item_flag = p_item_flag
AND NVL(start_date_active, sysdate) <= sysdate
AND NVL(end_date_active, sysdate + 1) > sysdate
AND DECODE(p_item_flag,'N',purchasing_enabled_flag,'Y') = 'Y'
AND DECODE(p_item_flag,'N',NVL(outside_operation_flag, 'N'),'N') = 'N';
SELECT
mtl.inventory_item_id,
mtl.organization_id inventory_org_id
FROM mtl_system_items_b mtl, inv_organization_info_v org
WHERE mtl.organization_id = p_org_id
AND mtl.inventory_item_id = p_item_id
AND mtl.organization_id = org.organization_id
AND NVL (org.operating_unit, mo_global.get_current_org_id ()) = mo_global.get_current_org_id()
AND mtl.inventory_item_flag = p_item_flag
AND NVL(mtl.start_date_active, sysdate) <= sysdate
AND NVL(mtl.end_date_active, sysdate + 1) > sysdate
AND DECODE(p_item_flag,'N',mtl.purchasing_enabled_flag,'Y') = 'Y'
AND DECODE(p_item_flag,'N',NVL(mtl.outside_operation_flag, 'N'),'N') = 'N';
SELECT
inv_service_item_rel_id ,
object_version_number ,
inv_item_id ,
inv_org_id ,
service_item_id ,
rank ,
active_start_date ,
active_end_date
FROM ahl_inv_service_item_rels
WHERE inv_service_item_rel_id = p_ser_item_rel_id
FOR UPDATE;
SELECT 'X'
FROM ahl_inv_service_item_rels
WHERE inv_org_id = p_inv_org_id
AND inv_item_id = p_inv_item_id
AND service_item_id = p_service_item_id;
SELECT 'X'
FROM ahl_inv_service_item_rels
WHERE inv_org_id = p_inv_org_id
AND inv_item_id = p_inv_item_id
AND rank = p_rank
AND INV_SERVICE_ITEM_REL_ID <> NVL(p_serv_rel_id,-99);
SELECT
isirv.inv_org_id,
isirv.inv_org_name,
isirv.inv_item_number,
isirv.service_item_number,
isirv.rank
/* Removed the OU filtering here, as the view ahl_inv_service_item_rels_v itself is OU filtered with the Bug
fix 5350882, mpothuku 26-Jul-06 */
FROM ahl_inv_service_item_rels_v isirv,
mtl_system_items_b mtl
/*
inv_organization_info_v org
*/
WHERE isirv.inv_item_id = p_inv_item_id
AND isirv.service_item_id <> p_service_item_id
/*
If p_service_item_id is not assigned to the Org thats being considered
there is no need to throw the validation, the creation simply escapes it anyway if the
service item does not belong to the Org thats being considered
*/
AND mtl.inventory_item_id = p_service_item_id
AND mtl.organization_id = isirv.inv_org_id
AND isirv.rank = p_rank;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Inserting Relationships across Multiple Orgs');
DELETE FROM AHL_INV_SERVICE_ITEM_RELS
WHERE INV_ITEM_ID = p_x_Inv_serv_item_rec.inv_item_id
AND SERVICE_ITEM_ID = p_x_Inv_serv_item_rec.service_item_id;
INSERT INTO AHL_INV_SERVICE_ITEM_RELS
(INV_SERVICE_ITEM_REL_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
INV_ITEM_ID ,
INV_ORG_ID ,
SERVICE_ITEM_ID ,
RANK ,
ACTIVE_START_DATE ,
ACTIVE_END_DATE ,
SECURITY_GROUP_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 )
SELECT AHL_INV_SERVICE_ITEM_RELS_S.NEXTVAL,
1,
sysdate,
Fnd_Global.USER_ID,
sysdate,
Fnd_Global.USER_ID,
Fnd_Global.LOGIN_ID,
Inv.INVENTORY_ITEM_ID,
Inv.ORGANIZATION_ID,
Serv.INVENTORY_ITEM_ID,
p_x_Inv_serv_item_rec.rank,
p_x_Inv_serv_item_rec.active_start_date,
p_x_Inv_serv_item_rec.active_end_date,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL
FROM mtl_system_items_kfv Inv,
mtl_system_items_kfv Serv,
--Modified by mpothuku on 17-Jan-05 to fix the performance Bug 4919315
inv_organization_info_v org
WHERE Inv.ORGANIZATION_ID = Serv.ORGANIZATION_ID
AND org.organization_id = Inv.ORGANIZATION_ID
AND Inv.inventory_item_flag = 'Y'
AND Serv.inventory_item_flag = 'N'
AND Inv.inventory_item_id = p_x_Inv_serv_item_rec.inv_item_id
AND Serv.inventory_item_id = p_x_Inv_serv_item_rec.service_item_id
-- mpothuku start
AND org.organization_id not in
(select INV_ORG_ID from AHL_INV_SERVICE_ITEM_RELS
where inv_item_id = p_x_Inv_serv_item_rec.inv_item_id
AND service_item_id = p_x_Inv_serv_item_rec.service_item_id)
-- mpothuku end
AND NVL(org.operating_unit, mo_global.get_current_org_id()) =
mo_global.get_current_org_id();
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Inserting Relationship in Single Org');
INSERT INTO AHL_INV_SERVICE_ITEM_RELS
(INV_SERVICE_ITEM_REL_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
INV_ITEM_ID ,
INV_ORG_ID ,
SERVICE_ITEM_ID ,
RANK ,
ACTIVE_START_DATE ,
ACTIVE_END_DATE ,
SECURITY_GROUP_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 )
VALUES
(AHL_INV_SERVICE_ITEM_RELS_S.NEXTVAL,
1,
sysdate,
Fnd_Global.USER_ID,
sysdate,
Fnd_Global.USER_ID,
Fnd_Global.LOGIN_ID,
p_x_Inv_serv_item_rec.inv_item_id,
p_x_Inv_serv_item_rec.inv_org_id,
p_x_Inv_serv_item_rec.service_item_id,
p_x_Inv_serv_item_rec.rank,
p_x_Inv_serv_item_rec.active_start_date,
p_x_Inv_serv_item_rec.active_end_date,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ) RETURN INV_SERVICE_ITEM_REL_ID INTO p_x_Inv_serv_item_rec.inv_ser_item_rel_id;
UPDATE ahl_inv_service_item_rels
SET active_start_date = p_x_inv_serv_item_rec.active_start_date,
active_end_date = p_x_inv_serv_item_rec.active_end_date,
rank = p_x_inv_serv_item_rec.rank,
object_version_number = object_version_number +1
WHERE inv_service_item_rel_id = p_x_Inv_serv_item_rec.inv_ser_item_rel_id
AND object_version_number = l_Item_Ser_rel_det.object_version_number;
DELETE FROM ahl_inv_service_item_rels
WHERE inv_service_item_rel_id = p_x_Inv_serv_item_rec.inv_ser_item_rel_id
AND object_version_number = l_Item_Ser_rel_det.object_version_number;
DELETE FROM ahl_item_vendor_rels
WHERE inv_service_item_rel_id = p_x_Inv_serv_item_rec.inv_ser_item_rel_id;