VIEW: APPS.MSC_PDR_RESOURCE_DETAILS_V

Source

View Text - Preformatted

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))

View Text - HTML Formatted

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))