[Home] [Help]
1495:
1496: UNION ALL
1497:
1498: SELECT refresh_number
1499: FROM msc_resource_requirements
1500: WHERE plan_id = p_plan_id
1501: );
1502:
1503: IF l_new_refresh_number IS NULL THEN
8181: p_sys_date creation_date,
8182: l_user_id created_by
8183: FROM MSC_UOM_CONVERSIONS MUC,
8184: (SELECT -- hint for better performance.
8185: /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) */
8186: DR.PLAN_ID plan_id,
8187: NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
8188: DR.RESOURCE_ID resource_id,
8189: DR.organization_id organization_id,
8200: NVL(DR.BATCHABLE_FLAG,2) BATCHABLE_FLAG,
8201: DECODE(DR.UOM_CLASS_TYPE,1 , I.WEIGHT_UOM, 2, I.VOLUME_UOM) UOM_CODE
8202: FROM MSC_DEPARTMENT_RESOURCES DR,
8203: MSC_TRADING_PARTNERS P,
8204: MSC_RESOURCE_REQUIREMENTS REQ,
8205: MSC_SYSTEM_ITEMS I,
8206: MSC_SUPPLIES S,
8207: ---- re-ordered tables for performance
8208: MSC_CALENDAR_DATES C
8333: p_sys_date creation_date,
8334: l_user_id created_by
8335: FROM MSC_UOM_CONVERSIONS MUC,
8336: (SELECT -- hint for better performance.
8337: /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) */
8338: DR.PLAN_ID plan_id,
8339: NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
8340: DR.RESOURCE_ID resource_id,
8341: DR.organization_id organization_id,
8352: NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY)), 1) SD_QTY,
8353: NVL(DR.BATCHABLE_FLAG,2) BATCHABLE_FLAG,
8354: DECODE(DR.UOM_CLASS_TYPE,1 , I.WEIGHT_UOM, 2, I.VOLUME_UOM) UOM_CODE
8355: FROM MSC_DEPARTMENT_RESOURCES DR,
8356: MSC_RESOURCE_REQUIREMENTS REQ,
8357: MSC_SYSTEM_ITEMS I,
8358: MSC_SUPPLIES S
8359: ---- re-ordered tables for performance
8360: WHERE DR.PLAN_ID = REQ.PLAN_ID
8464: creation_date,
8465: created_by
8466: FROM
8467: (
8468: SELECT /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) index(C MSC_CALENDAR_DATES_U1) */
8469: DR.PLAN_ID plan_id,
8470: NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
8471: DR.RESOURCE_ID resource_id,
8472: DR.organization_id organization_id,
8482: p_sys_date creation_date,
8483: l_user_id created_by
8484: FROM MSC_DEPARTMENT_RESOURCES DR,
8485: MSC_TRADING_PARTNERS P,
8486: MSC_RESOURCE_REQUIREMENTS REQ,
8487: MSC_SYSTEM_ITEMS I, -- summary enhancement change for CTO ODR
8488: MSC_CALENDAR_DATES C
8489: ---- re-ordered tables for performance
8490: WHERE DR.PLAN_ID = REQ.PLAN_ID
8581: creation_date,
8582: created_by
8583: FROM
8584: (
8585: SELECT /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) index(C MSC_CALENDAR_DATES_U1) */
8586: DR.PLAN_ID plan_id,
8587: NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
8588: DR.RESOURCE_ID resource_id,
8589: DR.organization_id organization_id,
8599: l_user_id last_updated_by,
8600: p_sys_date creation_date,
8601: l_user_id created_by
8602: FROM MSC_DEPARTMENT_RESOURCES DR,
8603: MSC_RESOURCE_REQUIREMENTS REQ,
8604: MSC_SYSTEM_ITEMS I -- summary enhancement change for CTO ODR
8605: WHERE DR.PLAN_ID = REQ.PLAN_ID
8606: AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID -- summary enhancement: made consistent
8607: AND DR.RESOURCE_ID = REQ.RESOURCE_ID
8685: -- TRUNC(REQ.START_DATE) SD_DATE,
8686: -- End Bug 3348095
8687: SUM((-1) * REQ.RESOURCE_HOURS) SD_QTY -- ATP always populates resource_hours
8688: FROM MSC_DEPARTMENT_RESOURCES DR,
8689: MSC_RESOURCE_REQUIREMENTS REQ
8690: WHERE DR.PLAN_ID = p_plan_id
8691: AND REQ.PLAN_ID = DR.PLAN_ID
8692: AND REQ.SR_INSTANCE_ID = DR.sr_instance_id
8693: AND REQ.RESOURCE_ID = DR.resource_id