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.FLM_MMM_DEPT_RESOURCES_V

Source

View Text - Preformatted


SELECT BR.ORGANIZATION_ID, BD.DEPARTMENT_ID, BD.DEPARTMENT_CODE, BR.RESOURCE_ID, BR.RESOURCE_CODE, BSO.OPERATION_TYPE, BDR.CAPACITY_UNITS, SUM(FMOR.RESOURCE_ASSIGNED) FROM BOM_RESOURCES BR, BOM_DEPARTMENTS BD, BOM_DEPARTMENT_RESOURCES BDR, BOM_STANDARD_OPERATIONS BSO, FLM_MMM_OP_RESOURCES FMOR WHERE FMOR.PLAN_ID = -1 AND FMOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND FMOR.RESOURCE_ID = BR.RESOURCE_ID AND FMOR.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID AND BD.DEPARTMENT_ID = BSO.DEPARTMENT_ID AND BDR.DEPARTMENT_ID = BD.DEPARTMENT_ID AND BR.RESOURCE_ID = BDR.RESOURCE_ID GROUP BY BR.ORGANIZATION_ID, BD.DEPARTMENT_ID, BD.DEPARTMENT_CODE, BR.RESOURCE_ID, BR.RESOURCE_CODE, BSO.OPERATION_TYPE, BDR.CAPACITY_UNITS UNION ALL (SELECT BR.ORGANIZATION_ID, BD.DEPARTMENT_ID, BD.DEPARTMENT_CODE, BR.RESOURCE_ID, BR.RESOURCE_CODE, 2, BDR.CAPACITY_UNITS, 0 FROM BOM_RESOURCES BR, BOM_DEPARTMENTS BD, BOM_DEPARTMENT_RESOURCES BDR WHERE BR.ORGANIZATION_ID = BD.ORGANIZATION_ID AND BR.RESOURCE_ID = BDR.RESOURCE_ID AND BD.DEPARTMENT_ID = BDR.DEPARTMENT_ID AND NOT EXISTS ( SELECT 1 FROM BOM_STANDARD_OPERATIONS BSO, FLM_MMM_OP_RESOURCES FMOR WHERE FMOR.PLAN_ID = -1 AND FMOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND FMOR.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID AND FMOR.RESOURCE_ID = BR.RESOURCE_ID AND BSO.OPERATION_TYPE = 2 AND BSO.DEPARTMENT_ID = BDR.DEPARTMENT_ID )) UNION ALL (SELECT BR.ORGANIZATION_ID, BD.DEPARTMENT_ID, BD.DEPARTMENT_CODE, BR.RESOURCE_ID, BR.RESOURCE_CODE, 3, BDR.CAPACITY_UNITS, 0 FROM BOM_RESOURCES BR, BOM_DEPARTMENTS BD, BOM_DEPARTMENT_RESOURCES BDR WHERE BR.ORGANIZATION_ID = BD.ORGANIZATION_ID AND BR.RESOURCE_ID = BDR.RESOURCE_ID AND BD.DEPARTMENT_ID = BDR.DEPARTMENT_ID AND NOT EXISTS ( SELECT 1 FROM BOM_STANDARD_OPERATIONS BSO, FLM_MMM_OP_RESOURCES FMOR WHERE FMOR.PLAN_ID = -1 AND FMOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND FMOR.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID AND FMOR.RESOURCE_ID = BR.RESOURCE_ID AND BSO.OPERATION_TYPE = 3 AND BSO.DEPARTMENT_ID = BDR.DEPARTMENT_ID )) UNION ALL (SELECT BR.ORGANIZATION_ID, BD.DEPARTMENT_ID, BD.DEPARTMENT_CODE, BR.RESOURCE_ID, BR.RESOURCE_CODE, BSO.OPERATION_TYPE, 0, SUM(FMOR.RESOURCE_ASSIGNED) FROM BOM_RESOURCES BR, BOM_DEPARTMENTS BD, BOM_STANDARD_OPERATIONS BSO, FLM_MMM_OP_RESOURCES FMOR WHERE FMOR.PLAN_ID = -1 AND BR.ORGANIZATION_ID = FMOR.ORGANIZATION_ID AND BR.RESOURCE_ID = FMOR.RESOURCE_ID AND FMOR.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID AND BD.DEPARTMENT_ID = BSO.DEPARTMENT_ID AND NOT EXISTS ( SELECT 1 FROM BOM_DEPARTMENT_RESOURCES BDR WHERE BDR.DEPARTMENT_ID = BD.DEPARTMENT_ID AND BDR.RESOURCE_ID = FMOR.RESOURCE_ID ) GROUP BY BR.ORGANIZATION_ID, BD.DEPARTMENT_ID, BD.DEPARTMENT_CODE, BR.RESOURCE_ID, BR.RESOURCE_CODE, BSO.OPERATION_TYPE)

View Text - HTML Formatted

SELECT BR.ORGANIZATION_ID
, BD.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, BSO.OPERATION_TYPE
, BDR.CAPACITY_UNITS
, SUM(FMOR.RESOURCE_ASSIGNED)
FROM BOM_RESOURCES BR
, BOM_DEPARTMENTS BD
, BOM_DEPARTMENT_RESOURCES BDR
, BOM_STANDARD_OPERATIONS BSO
, FLM_MMM_OP_RESOURCES FMOR
WHERE FMOR.PLAN_ID = -1
AND FMOR.ORGANIZATION_ID = BR.ORGANIZATION_ID
AND FMOR.RESOURCE_ID = BR.RESOURCE_ID
AND FMOR.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID
AND BD.DEPARTMENT_ID = BSO.DEPARTMENT_ID
AND BDR.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND BR.RESOURCE_ID = BDR.RESOURCE_ID GROUP BY BR.ORGANIZATION_ID
, BD.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, BSO.OPERATION_TYPE
, BDR.CAPACITY_UNITS UNION ALL (SELECT BR.ORGANIZATION_ID
, BD.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, 2
, BDR.CAPACITY_UNITS
, 0
FROM BOM_RESOURCES BR
, BOM_DEPARTMENTS BD
, BOM_DEPARTMENT_RESOURCES BDR
WHERE BR.ORGANIZATION_ID = BD.ORGANIZATION_ID
AND BR.RESOURCE_ID = BDR.RESOURCE_ID
AND BD.DEPARTMENT_ID = BDR.DEPARTMENT_ID
AND NOT EXISTS ( SELECT 1
FROM BOM_STANDARD_OPERATIONS BSO
, FLM_MMM_OP_RESOURCES FMOR
WHERE FMOR.PLAN_ID = -1
AND FMOR.ORGANIZATION_ID = BR.ORGANIZATION_ID
AND FMOR.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID
AND FMOR.RESOURCE_ID = BR.RESOURCE_ID
AND BSO.OPERATION_TYPE = 2
AND BSO.DEPARTMENT_ID = BDR.DEPARTMENT_ID )) UNION ALL (SELECT BR.ORGANIZATION_ID
, BD.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, 3
, BDR.CAPACITY_UNITS
, 0
FROM BOM_RESOURCES BR
, BOM_DEPARTMENTS BD
, BOM_DEPARTMENT_RESOURCES BDR
WHERE BR.ORGANIZATION_ID = BD.ORGANIZATION_ID
AND BR.RESOURCE_ID = BDR.RESOURCE_ID
AND BD.DEPARTMENT_ID = BDR.DEPARTMENT_ID
AND NOT EXISTS ( SELECT 1
FROM BOM_STANDARD_OPERATIONS BSO
, FLM_MMM_OP_RESOURCES FMOR
WHERE FMOR.PLAN_ID = -1
AND FMOR.ORGANIZATION_ID = BR.ORGANIZATION_ID
AND FMOR.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID
AND FMOR.RESOURCE_ID = BR.RESOURCE_ID
AND BSO.OPERATION_TYPE = 3
AND BSO.DEPARTMENT_ID = BDR.DEPARTMENT_ID )) UNION ALL (SELECT BR.ORGANIZATION_ID
, BD.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, BSO.OPERATION_TYPE
, 0
, SUM(FMOR.RESOURCE_ASSIGNED)
FROM BOM_RESOURCES BR
, BOM_DEPARTMENTS BD
, BOM_STANDARD_OPERATIONS BSO
, FLM_MMM_OP_RESOURCES FMOR
WHERE FMOR.PLAN_ID = -1
AND BR.ORGANIZATION_ID = FMOR.ORGANIZATION_ID
AND BR.RESOURCE_ID = FMOR.RESOURCE_ID
AND FMOR.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID
AND BD.DEPARTMENT_ID = BSO.DEPARTMENT_ID
AND NOT EXISTS ( SELECT 1
FROM BOM_DEPARTMENT_RESOURCES BDR
WHERE BDR.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND BDR.RESOURCE_ID = FMOR.RESOURCE_ID ) GROUP BY BR.ORGANIZATION_ID
, BD.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, BSO.OPERATION_TYPE)