View: FLM_MMM_DEPT_RESOURCES_V

Product:	FLM - Flow Manufacturing
Description:
Implementation/DBA Data:	APPS.FLM_MMM_DEPT_RESOURCES_V

View Text

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)

Columns