eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MTH_SRC_EQUIP_HIERARCHY_V

Source

View Text - Preformatted


SELECT HIERARCHY_NAME, LEVEL_NUM, LEVEL_FK, PARENT_FK, SYSTEM_FK, EFFECTIVE_DATE, LAST_UPDATE_DATE, SOURCE_ORG_CODE, PARENT_SOURCE_ORG_CODE FROM (SELECT 'Department Hierarchy' HIERARCHY_NAME, 7 LEVEL_NUM, mp.ORGANIZATION_CODE LEVEL_FK, CAST(NULL AS VARCHAR2(120)) PARENT_FK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, to_date('01-JAN-1900','DD-MON-YYYY') EFFECTIVE_DATE, mp.CREATION_DATE LAST_UPDATE_DATE, mp.ORGANIZATION_CODE SOURCE_ORG_CODE, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE FROM MTL_PARAMETERS mp UNION ALL SELECT 'Department Hierarchy' HIERARCHY_NAME, 8 LEVEL_NUM, to_char(bd.DEPARTMENT_ID) LEVEL_FK, mp.ORGANIZATION_CODE PARENT_FK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, bd.CREATION_DATE EFFECTIVE_DATE, bd.CREATION_DATE LAST_UPDATE_DATE, mp.ORGANIZATION_CODE SOURCE_ORG_CODE, mp.ORGANIZATION_CODE PARENT_SOURCE_ORG_CODE FROM BOM_DEPARTMENTS bd, MTL_PARAMETERS MP WHERE bd.ORGANIZATION_ID = MP.ORGANIZATION_ID UNION ALL SELECT 'Department Hierarchy' HIERARCHY_NAME, 8 LEVEL_NUM, To_Char(res.GROUP_RESOURCE) LEVEL_FK, res.ORGANIZATION_CODE PARENT_FK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, to_date('01-JAN-1900','DD-MON-YYYY') EFFECTIVE_DATE, res.LAST_UPDATE_DATE LAST_UPDATE_DATE, res.ORGANIZATION_CODE SOURCE_ORG_CODE, res.ORGANIZATION_CODE PARENT_SOURCE_ORG_CODE FROM (SELECT mtp.ORGANIZATION_CODE, crd.GROUP_RESOURCE, GREATEST(Min(crd.LAST_UPDATE_DATE),Min(mst.LAST_UPDATE_DATE)) LAST_UPDATE_DATE FROM CR_RSRC_DTL crd, CR_RSRC_MST_VL mst, MTL_PARAMETERS mtp WHERE crd.RESOURCES = mst.RESOURCES AND mtp.ORGANIZATION_ID = crd.ORGANIZATION_ID GROUP BY mtp.ORGANIZATION_CODE, crd.GROUP_RESOURCE) res UNION ALL SELECT 'Department Hierarchy' HIERARCHY_NAME, 9 LEVEL_NUM, br.RESOURCE_ID || '-' || bdr.DEPARTMENT_ID LEVEL_FK, to_char(bdr.DEPARTMENT_ID) PARENT_FK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, bdr.CREATION_DATE EFFECTIVE_DATE, bdr.CREATION_DATE LAST_UPDATE_DATE, mp.ORGANIZATION_CODE SOURCE_ORG_CODE, mp.ORGANIZATION_CODE PARENT_SOURCE_ORG_CODE FROM BOM_RESOURCES br, BOM_DEPARTMENT_RESOURCES bdr, MTL_PARAMETERS mp WHERE bdr.RESOURCE_ID = br.RESOURCE_ID AND br.RESOURCE_TYPE IN (1,2) AND br.ORGANIZATION_ID = mp.ORGANIZATION_ID UNION ALL SELECT 'Department Hierarchy' HIERARCHY_NAME, 9 LEVEL_NUM, To_Char(crd.resource_id) LEVEL_FK, crd.GROUP_RESOURCE PARENT_FK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, to_date('01-JAN-1900','DD-MON-YYYY') EFFECTIVE_DATE, GREATEST(crd.LAST_UPDATE_DATE,mst.LAST_UPDATE_DATE ) LAST_UPDATE_DATE, mtp.ORGANIZATION_CODE SOURCE_ORG_CODE, mtp.ORGANIZATION_CODE PARENT_SOURCE_ORG_CODE FROM CR_RSRC_DTL crd, CR_RSRC_MST_VL mst, MTL_PARAMETERS mtp WHERE crd.RESOURCES = mst.RESOURCES AND mtp.ORGANIZATION_ID = crd.ORGANIZATION_ID UNION ALL SELECT 'Resource Group Hierarchy' HIERARCHY_NAME, 8 LEVEL_NUM, fnd_crp.lookup_code LEVEL_FK, CAST(NULL AS VARCHAR2(120)) PARENT_FK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, to_date('01-JAN-1900','DD-MON-YYYY') EFFECTIVE_DATE, Max(bdr.LAST_UPDATE_DATE) LAST_UPDATE_DATE, CAST(NULL AS VARCHAR2(15)) SOURCE_ORG_CODE, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE FROM fnd_lookup_values fnd_crp, BOM_DEPARTMENT_RESOURCES bdr WHERE fnd_crp.LOOKUP_TYPE ='CRP_RESOURCE_GROUPS' AND fnd_crp.LANGUAGE = USERENV('LANG') AND fnd_crp.lookup_code = bdr.resource_group_name GROUP BY fnd_crp.lookup_code UNION ALL SELECT 'Resource Group Hierarchy' HIERARCHY_NAME, 8 LEVEL_NUM, mst.resource_class LEVEL_FK, CAST(NULL AS VARCHAR2(120)) PARENT_FK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, to_date('01-JAN-1900','DD-MON-YYYY') EFFECTIVE_DATE, Max(GREATEST(crd.LAST_UPDATE_DATE,mst.LAST_UPDATE_DATE )) LAST_UPDATE_DATE, CAST(NULL AS VARCHAR2(15)) SOURCE_ORG_CODE, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE FROM CR_RSRC_DTL crd, CR_RSRC_MST_VL mst, MTL_PARAMETERS mtp WHERE crd.RESOURCES = mst.RESOURCES AND mtp.ORGANIZATION_ID = crd.ORGANIZATION_ID GROUP BY mst.resource_class UNION ALL SELECT 'Resource Group Hierarchy' HIERARCHY_NAME, 9 LEVEL_NUM, br.RESOURCE_ID || '-' || bdr.DEPARTMENT_ID LEVEL_FK, RESOURCE_GROUP_NAME PARENT_FK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, bdr.LAST_UPDATE_DATE EFFECTIVE_DATE, bdr.LAST_UPDATE_DATE LAST_UPDATE_DATE, mp.ORGANIZATION_CODE SOURCE_ORG_CODE, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE FROM BOM_RESOURCES br, BOM_DEPARTMENT_RESOURCES bdr, MTL_PARAMETERS mp WHERE bdr.RESOURCE_ID = br.RESOURCE_ID AND br.RESOURCE_TYPE IN (1,2) AND br.ORGANIZATION_ID = mp.ORGANIZATION_ID UNION ALL SELECT 'Resource Group Hierarchy' HIERARCHY_NAME, 9 LEVEL_NUM, To_Char(crd.resource_id) LEVEL_FK, mst.resource_class PARENT_FK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, mst.LAST_UPDATE_DATE EFFECTIVE_DATE, GREATEST(crd.LAST_UPDATE_DATE,mst.LAST_UPDATE_DATE ) LAST_UPDATE_DATE, mtp.ORGANIZATION_CODE SOURCE_ORG_CODE, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE FROM CR_RSRC_DTL crd, CR_RSRC_MST_VL mst, MTL_PARAMETERS mtp WHERE crd.RESOURCES = mst.RESOURCES AND mtp.ORGANIZATION_ID = crd.ORGANIZATION_ID)

View Text - HTML Formatted

SELECT HIERARCHY_NAME
, LEVEL_NUM
, LEVEL_FK
, PARENT_FK
, SYSTEM_FK
, EFFECTIVE_DATE
, LAST_UPDATE_DATE
, SOURCE_ORG_CODE
, PARENT_SOURCE_ORG_CODE
FROM (SELECT 'DEPARTMENT HIERARCHY' HIERARCHY_NAME
, 7 LEVEL_NUM
, MP.ORGANIZATION_CODE LEVEL_FK
, CAST(NULL AS VARCHAR2(120)) PARENT_FK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, TO_DATE('01-JAN-1900'
, 'DD-MON-YYYY') EFFECTIVE_DATE
, MP.CREATION_DATE LAST_UPDATE_DATE
, MP.ORGANIZATION_CODE SOURCE_ORG_CODE
, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE
FROM MTL_PARAMETERS MP UNION ALL SELECT 'DEPARTMENT HIERARCHY' HIERARCHY_NAME
, 8 LEVEL_NUM
, TO_CHAR(BD.DEPARTMENT_ID) LEVEL_FK
, MP.ORGANIZATION_CODE PARENT_FK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, BD.CREATION_DATE EFFECTIVE_DATE
, BD.CREATION_DATE LAST_UPDATE_DATE
, MP.ORGANIZATION_CODE SOURCE_ORG_CODE
, MP.ORGANIZATION_CODE PARENT_SOURCE_ORG_CODE
FROM BOM_DEPARTMENTS BD
, MTL_PARAMETERS MP
WHERE BD.ORGANIZATION_ID = MP.ORGANIZATION_ID UNION ALL SELECT 'DEPARTMENT HIERARCHY' HIERARCHY_NAME
, 8 LEVEL_NUM
, TO_CHAR(RES.GROUP_RESOURCE) LEVEL_FK
, RES.ORGANIZATION_CODE PARENT_FK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, TO_DATE('01-JAN-1900'
, 'DD-MON-YYYY') EFFECTIVE_DATE
, RES.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RES.ORGANIZATION_CODE SOURCE_ORG_CODE
, RES.ORGANIZATION_CODE PARENT_SOURCE_ORG_CODE
FROM (SELECT MTP.ORGANIZATION_CODE
, CRD.GROUP_RESOURCE
, GREATEST(MIN(CRD.LAST_UPDATE_DATE)
, MIN(MST.LAST_UPDATE_DATE)) LAST_UPDATE_DATE
FROM CR_RSRC_DTL CRD
, CR_RSRC_MST_VL MST
, MTL_PARAMETERS MTP
WHERE CRD.RESOURCES = MST.RESOURCES
AND MTP.ORGANIZATION_ID = CRD.ORGANIZATION_ID GROUP BY MTP.ORGANIZATION_CODE
, CRD.GROUP_RESOURCE) RES UNION ALL SELECT 'DEPARTMENT HIERARCHY' HIERARCHY_NAME
, 9 LEVEL_NUM
, BR.RESOURCE_ID || '-' || BDR.DEPARTMENT_ID LEVEL_FK
, TO_CHAR(BDR.DEPARTMENT_ID) PARENT_FK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, BDR.CREATION_DATE EFFECTIVE_DATE
, BDR.CREATION_DATE LAST_UPDATE_DATE
, MP.ORGANIZATION_CODE SOURCE_ORG_CODE
, MP.ORGANIZATION_CODE PARENT_SOURCE_ORG_CODE
FROM BOM_RESOURCES BR
, BOM_DEPARTMENT_RESOURCES BDR
, MTL_PARAMETERS MP
WHERE BDR.RESOURCE_ID = BR.RESOURCE_ID
AND BR.RESOURCE_TYPE IN (1
, 2)
AND BR.ORGANIZATION_ID = MP.ORGANIZATION_ID UNION ALL SELECT 'DEPARTMENT HIERARCHY' HIERARCHY_NAME
, 9 LEVEL_NUM
, TO_CHAR(CRD.RESOURCE_ID) LEVEL_FK
, CRD.GROUP_RESOURCE PARENT_FK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, TO_DATE('01-JAN-1900'
, 'DD-MON-YYYY') EFFECTIVE_DATE
, GREATEST(CRD.LAST_UPDATE_DATE
, MST.LAST_UPDATE_DATE ) LAST_UPDATE_DATE
, MTP.ORGANIZATION_CODE SOURCE_ORG_CODE
, MTP.ORGANIZATION_CODE PARENT_SOURCE_ORG_CODE
FROM CR_RSRC_DTL CRD
, CR_RSRC_MST_VL MST
, MTL_PARAMETERS MTP
WHERE CRD.RESOURCES = MST.RESOURCES
AND MTP.ORGANIZATION_ID = CRD.ORGANIZATION_ID UNION ALL SELECT 'RESOURCE GROUP HIERARCHY' HIERARCHY_NAME
, 8 LEVEL_NUM
, FND_CRP.LOOKUP_CODE LEVEL_FK
, CAST(NULL AS VARCHAR2(120)) PARENT_FK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, TO_DATE('01-JAN-1900'
, 'DD-MON-YYYY') EFFECTIVE_DATE
, MAX(BDR.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, CAST(NULL AS VARCHAR2(15)) SOURCE_ORG_CODE
, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE
FROM FND_LOOKUP_VALUES FND_CRP
, BOM_DEPARTMENT_RESOURCES BDR
WHERE FND_CRP.LOOKUP_TYPE ='CRP_RESOURCE_GROUPS'
AND FND_CRP.LANGUAGE = USERENV('LANG')
AND FND_CRP.LOOKUP_CODE = BDR.RESOURCE_GROUP_NAME GROUP BY FND_CRP.LOOKUP_CODE UNION ALL SELECT 'RESOURCE GROUP HIERARCHY' HIERARCHY_NAME
, 8 LEVEL_NUM
, MST.RESOURCE_CLASS LEVEL_FK
, CAST(NULL AS VARCHAR2(120)) PARENT_FK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, TO_DATE('01-JAN-1900'
, 'DD-MON-YYYY') EFFECTIVE_DATE
, MAX(GREATEST(CRD.LAST_UPDATE_DATE
, MST.LAST_UPDATE_DATE )) LAST_UPDATE_DATE
, CAST(NULL AS VARCHAR2(15)) SOURCE_ORG_CODE
, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE
FROM CR_RSRC_DTL CRD
, CR_RSRC_MST_VL MST
, MTL_PARAMETERS MTP
WHERE CRD.RESOURCES = MST.RESOURCES
AND MTP.ORGANIZATION_ID = CRD.ORGANIZATION_ID GROUP BY MST.RESOURCE_CLASS UNION ALL SELECT 'RESOURCE GROUP HIERARCHY' HIERARCHY_NAME
, 9 LEVEL_NUM
, BR.RESOURCE_ID || '-' || BDR.DEPARTMENT_ID LEVEL_FK
, RESOURCE_GROUP_NAME PARENT_FK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, BDR.LAST_UPDATE_DATE EFFECTIVE_DATE
, BDR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MP.ORGANIZATION_CODE SOURCE_ORG_CODE
, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE
FROM BOM_RESOURCES BR
, BOM_DEPARTMENT_RESOURCES BDR
, MTL_PARAMETERS MP
WHERE BDR.RESOURCE_ID = BR.RESOURCE_ID
AND BR.RESOURCE_TYPE IN (1
, 2)
AND BR.ORGANIZATION_ID = MP.ORGANIZATION_ID UNION ALL SELECT 'RESOURCE GROUP HIERARCHY' HIERARCHY_NAME
, 9 LEVEL_NUM
, TO_CHAR(CRD.RESOURCE_ID) LEVEL_FK
, MST.RESOURCE_CLASS PARENT_FK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, MST.LAST_UPDATE_DATE EFFECTIVE_DATE
, GREATEST(CRD.LAST_UPDATE_DATE
, MST.LAST_UPDATE_DATE ) LAST_UPDATE_DATE
, MTP.ORGANIZATION_CODE SOURCE_ORG_CODE
, CAST(NULL AS VARCHAR2(15)) PARENT_SOURCE_ORG_CODE
FROM CR_RSRC_DTL CRD
, CR_RSRC_MST_VL MST
, MTL_PARAMETERS MTP
WHERE CRD.RESOURCES = MST.RESOURCES
AND MTP.ORGANIZATION_ID = CRD.ORGANIZATION_ID)