View: MSC_PDR_RESOURCE_DETAILS_V

Product:	MSC - Advanced Supply Chain Planning
Description:
Implementation/DBA Data:	APPS.MSC_PDR_RESOURCE_DETAILS_V

View Text

SELECT MSC_GET_NAME.ORG_CODE(PP.ORGANIZATION_ID
, PP.SR_INSTANCE_ID)
, PP.RESOURCE_GROUP_NAME
, PP.DEPARTMENT_CODE
, PP.RESOURCE_CODE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE'
, PP.RESOURCE_TYPE)
, TO_CHAR(NULL)
, PP.RESOURCE_DESCRIPTION
, DECODE(PP.RESOURCE_ID
, -1
, NULL
, MSC_GET_NAME.DEPARTMENT_CODE(2
, PP.OWNING_DEPARTMENT_ID
, PP.ORGANIZATION_ID
, PP.PLAN_ID
, PP.SR_INSTANCE_ID))
, PP.DEPARTMENT_CLASS
, PP.MIN_RATE
, PP.MAX_RATE
, PP.RESOURCE_COST
, MSC_GET_NAME.RESOURCE_OVER_UTIL_COST(PP.RESOURCE_ID
, PP.DEPARTMENT_ID
, PP.ORGANIZATION_ID
, PP.PLAN_ID
, PP.SR_INSTANCE_ID)
, PP.UTILIZATION
, PP.EFFICIENCY
, PP.UNIT_OF_MEASURE
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TRUNC(SYSDATE)
, PP.PLAN_ID
, PP.ORGANIZATION_ID
, PP.SR_INSTANCE_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PP.DEPARTMENT_ID
, PP.RESOURCE_ID
, NULL
FROM MSC_DEPARTMENT_RESOURCES PP
WHERE PP.PLAN_ID IN (SELECT PLAN_ID
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID)
AND ( (NVL(PP.ORGANIZATION_ID
, -1)
, NVL(PP.SR_INSTANCE_ID
, -1)) IN (SELECT NVL(ORGANIZATION_ID
, NVL(PP.ORGANIZATION_ID
, -1))
, NVL(SR_INSTANCE_ID
, NVL(PP.SR_INSTANCE_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(ORGANIZATION_ID
, -2) <> -1 ) )
AND (NVL(PP.RESOURCE_GROUP_NAME
, '-1') IN (SELECT NVL(RESOURCE_GROUP_ID
, NVL(PP.RESOURCE_GROUP_NAME
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(RESOURCE_GROUP_ID
, '-2') <> '-1'))
AND (NVL(PP.DEPARTMENT_ID
, -1) IN (SELECT NVL(DEPT_LINE_ID
, NVL(PP.DEPARTMENT_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(DEPT_LINE_ID
, -2) <> -1))
AND (NVL(PP.RESOURCE_ID
, -1) IN (SELECT NVL(RESOURCE_ID
, NVL(PP.RESOURCE_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(RESOURCE_ID
, -2) <> -1)) UNION ALL SELECT MSC_GET_NAME.ORG_CODE( PP.ORGANIZATION_ID
, PP.SR_INSTANCE_ID)
, PP.RESOURCE_GROUP_NAME
, PP.DEPARTMENT_CODE
, PP.RESOURCE_CODE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE'
, PP.RESOURCE_TYPE )
, NULL
, PP.RESOURCE_DESCRIPTION
, DECODE(PP.RESOURCE_ID
, -1
, NULL
, MSC_GET_NAME.DEPARTMENT_CODE(2
, PP.OWNING_DEPARTMENT_ID
, PP.ORGANIZATION_ID
, PP.PLAN_ID
, PP.SR_INSTANCE_ID))
, PP.DEPARTMENT_CLASS
, PP.MIN_RATE
, PP.MAX_RATE
, PP.RESOURCE_COST
, 0
, PP.UTILIZATION
, PP.EFFICIENCY
, PP.UNIT_OF_MEASURE
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TRUNC(SYSDATE)
, PP.PLAN_ID
, PP.ORGANIZATION_ID
, PP.SR_INSTANCE_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PP.DEPARTMENT_ID
, PP.RESOURCE_ID
, NULL
FROM MSC_OPERATION_RESOURCES MOR
, MSC_OPERATION_RESOURCE_SEQS MORS
, MSC_DEPARTMENT_RESOURCES PP
WHERE PP.PLAN_ID = MORS.PLAN_ID
AND PP.SR_INSTANCE_ID = MORS.SR_INSTANCE_ID
AND PP.DEPARTMENT_ID = MORS.DEPARTMENT_ID
AND PP.RESOURCE_ID = MOR.RESOURCE_ID
AND MOR.PLAN_ID = MORS.PLAN_ID
AND MOR.ROUTING_SEQUENCE_ID = MORS.ROUTING_SEQUENCE_ID
AND MOR.OPERATION_SEQUENCE_ID = MORS.OPERATION_SEQUENCE_ID
AND MOR.RESOURCE_SEQ_NUM = MORS.RESOURCE_SEQ_NUM
AND MOR.SR_INSTANCE_ID = MORS.SR_INSTANCE_ID
AND MORS.ROUTING_SEQUENCE_ID IS NULL
AND PP.PLAN_ID IN (SELECT PLAN_ID
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID)
AND ( (NVL(PP.ORGANIZATION_ID
, -1)
, NVL(PP.SR_INSTANCE_ID
, -1)) IN (SELECT NVL(ORGANIZATION_ID
, NVL(PP.ORGANIZATION_ID
, -1))
, NVL(SR_INSTANCE_ID
, NVL(PP.SR_INSTANCE_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(ORGANIZATION_ID
, -2) <> -1 ) )
AND (NVL(PP.RESOURCE_GROUP_NAME
, '-1') IN (SELECT NVL(RESOURCE_GROUP_ID
, NVL(PP.RESOURCE_GROUP_NAME
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(RESOURCE_GROUP_ID
, '-2') <> '-1'))
AND (NVL(PP.DEPARTMENT_ID
, -1) IN (SELECT NVL(DEPT_LINE_ID
, NVL(PP.DEPARTMENT_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(DEPT_LINE_ID
, -2) <> -1))
AND (NVL(PP.RESOURCE_ID
, -1) IN (SELECT NVL(RESOURCE_ID
, NVL(PP.RESOURCE_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(RESOURCE_ID
, -2) <> -1))

Columns