VIEW: APPS.AHL_ROUTE_EFFECTIVITIES_V

Source

View Text - Preformatted

SELECT DISTINCT re.route_effectivity_id, icb.item_composition_id, re.route_id, rmb.route_no, rmt.title route_title, rmb.revision_number, rmb.start_date_active, rmb.end_date_active, re.inventory_item_id, re.inventory_master_org_id, mtl.concatenated_segments, mtl.description, mp.organization_code organization_code, re.mc_id, mc.NAME, DECODE (re.mc_header_id, NULL, NULL, mc.version_number) mc_version_number, DECODE (re.mc_header_id, NULL, NULL, mc.revision) mc_revision, mc.description, re.mc_header_id, re.last_update_date, re.last_updated_by, re.creation_date, re.created_by, re.last_update_login, re.object_version_number, re.security_group_id, re.attribute_category, re.attribute1, re.attribute2, re.attribute3, re.attribute4, re.attribute5, re.attribute6, re.attribute7, re.attribute8, re.attribute9, re.attribute10, re.attribute11, re.attribute12, re.attribute13, re.attribute14, re.attribute15, NVL (mtl.description, mc.description) FROM ahl_route_effectivities re, ahl_routes_b rmb, ahl_routes_tl rmt, mtl_system_items_kfv mtl, mtl_parameters mp, ( SELECT mcb.mc_header_id, mcb.NAME, mcb.mc_id, mcb.version_number, mcb.revision, mctl.description FROM ahl_mc_headers_b mcb, ahl_mc_headers_tl mctl WHERE mcb.mc_header_id = mctl.mc_header_id AND mctl.LANGUAGE = USERENV ('LANG') AND mcb.config_status_code = 'COMPLETE' ) mc, ( SELECT item_composition_id, inventory_item_id, inventory_master_org_id FROM ahl_item_compositions WHERE approval_status_code = 'COMPLETE' AND TRUNC (NVL (effective_end_date, SYSDATE + 1)) > tRUNC (SYSDATE) ) icb WHERE re.route_id = rmb.route_id AND rmb.route_id = rmt.route_id AND rmt.LANGUAGE = USERENV ('LANG') AND rmb.application_usg_code = fnd_profile.VALUE ('AHL_APPLN_USAGE') AND re.inventory_item_id = mtl.inventory_item_id(+) AND re.inventory_master_org_id = mtl.organization_id(+) AND mp.organization_id(+) = mtl.organization_id AND NVL (re.mc_header_id, re.mc_id) = mc.mc_header_id(+) AND re.inventory_item_id = icb.inventory_item_id(+) AND re.inventory_master_org_id = icb.inventory_master_org_id(+)

View Text - HTML Formatted

SELECT DISTINCT RE.ROUTE_EFFECTIVITY_ID
, ICB.ITEM_COMPOSITION_ID
, RE.ROUTE_ID
, RMB.ROUTE_NO
, RMT.TITLE ROUTE_TITLE
, RMB.REVISION_NUMBER
, RMB.START_DATE_ACTIVE
, RMB.END_DATE_ACTIVE
, RE.INVENTORY_ITEM_ID
, RE.INVENTORY_MASTER_ORG_ID
, MTL.CONCATENATED_SEGMENTS
, MTL.DESCRIPTION
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, RE.MC_ID
, MC.NAME
, DECODE (RE.MC_HEADER_ID
, NULL
, NULL
, MC.VERSION_NUMBER) MC_VERSION_NUMBER
, DECODE (RE.MC_HEADER_ID
, NULL
, NULL
, MC.REVISION) MC_REVISION
, MC.DESCRIPTION
, RE.MC_HEADER_ID
, RE.LAST_UPDATE_DATE
, RE.LAST_UPDATED_BY
, RE.CREATION_DATE
, RE.CREATED_BY
, RE.LAST_UPDATE_LOGIN
, RE.OBJECT_VERSION_NUMBER
, RE.SECURITY_GROUP_ID
, RE.ATTRIBUTE_CATEGORY
, RE.ATTRIBUTE1
, RE.ATTRIBUTE2
, RE.ATTRIBUTE3
, RE.ATTRIBUTE4
, RE.ATTRIBUTE5
, RE.ATTRIBUTE6
, RE.ATTRIBUTE7
, RE.ATTRIBUTE8
, RE.ATTRIBUTE9
, RE.ATTRIBUTE10
, RE.ATTRIBUTE11
, RE.ATTRIBUTE12
, RE.ATTRIBUTE13
, RE.ATTRIBUTE14
, RE.ATTRIBUTE15
, NVL (MTL.DESCRIPTION
, MC.DESCRIPTION)
FROM AHL_ROUTE_EFFECTIVITIES RE
, AHL_ROUTES_B RMB
, AHL_ROUTES_TL RMT
, MTL_SYSTEM_ITEMS_KFV MTL
, MTL_PARAMETERS MP
, ( SELECT MCB.MC_HEADER_ID
, MCB.NAME
, MCB.MC_ID
, MCB.VERSION_NUMBER
, MCB.REVISION
, MCTL.DESCRIPTION
FROM AHL_MC_HEADERS_B MCB
, AHL_MC_HEADERS_TL MCTL
WHERE MCB.MC_HEADER_ID = MCTL.MC_HEADER_ID
AND MCTL.LANGUAGE = USERENV ('LANG')
AND MCB.CONFIG_STATUS_CODE = 'COMPLETE' ) MC
, ( SELECT ITEM_COMPOSITION_ID
, INVENTORY_ITEM_ID
, INVENTORY_MASTER_ORG_ID
FROM AHL_ITEM_COMPOSITIONS
WHERE APPROVAL_STATUS_CODE = 'COMPLETE'
AND TRUNC (NVL (EFFECTIVE_END_DATE
, SYSDATE + 1)) > TRUNC (SYSDATE) ) ICB
WHERE RE.ROUTE_ID = RMB.ROUTE_ID
AND RMB.ROUTE_ID = RMT.ROUTE_ID
AND RMT.LANGUAGE = USERENV ('LANG')
AND RMB.APPLICATION_USG_CODE = FND_PROFILE.VALUE ('AHL_APPLN_USAGE')
AND RE.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID(+)
AND RE.INVENTORY_MASTER_ORG_ID = MTL.ORGANIZATION_ID(+)
AND MP.ORGANIZATION_ID(+) = MTL.ORGANIZATION_ID
AND NVL (RE.MC_HEADER_ID
, RE.MC_ID) = MC.MC_HEADER_ID(+)
AND RE.INVENTORY_ITEM_ID = ICB.INVENTORY_ITEM_ID(+)
AND RE.INVENTORY_MASTER_ORG_ID = ICB.INVENTORY_MASTER_ORG_ID(+)