[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
8152: p_sys_date creation_date,
8153: l_user_id created_by
8154: FROM MSC_UOM_CONVERSIONS MUC,
8155: (SELECT -- hint for better performance.
8156: /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) */
8157: DR.PLAN_ID plan_id,
8158: NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
8159: DR.RESOURCE_ID resource_id,
8160: DR.organization_id organization_id,
8171: NVL(DR.BATCHABLE_FLAG,2) BATCHABLE_FLAG,
8172: DECODE(DR.UOM_CLASS_TYPE,1 , I.WEIGHT_UOM, 2, I.VOLUME_UOM) UOM_CODE
8173: FROM MSC_DEPARTMENT_RESOURCES DR,
8174: MSC_TRADING_PARTNERS P,
8175: MSC_RESOURCE_REQUIREMENTS REQ,
8176: MSC_SYSTEM_ITEMS I,
8177: MSC_SUPPLIES S,
8178: ---- re-ordered tables for performance
8179: MSC_CALENDAR_DATES C
8304: p_sys_date creation_date,
8305: l_user_id created_by
8306: FROM MSC_UOM_CONVERSIONS MUC,
8307: (SELECT -- hint for better performance.
8308: /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) */
8309: DR.PLAN_ID plan_id,
8310: NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
8311: DR.RESOURCE_ID resource_id,
8312: DR.organization_id organization_id,
8323: NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY)), 1) SD_QTY,
8324: NVL(DR.BATCHABLE_FLAG,2) BATCHABLE_FLAG,
8325: DECODE(DR.UOM_CLASS_TYPE,1 , I.WEIGHT_UOM, 2, I.VOLUME_UOM) UOM_CODE
8326: FROM MSC_DEPARTMENT_RESOURCES DR,
8327: MSC_RESOURCE_REQUIREMENTS REQ,
8328: MSC_SYSTEM_ITEMS I,
8329: MSC_SUPPLIES S
8330: ---- re-ordered tables for performance
8331: WHERE DR.PLAN_ID = REQ.PLAN_ID
8435: creation_date,
8436: created_by
8437: FROM
8438: (
8439: SELECT /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) index(C MSC_CALENDAR_DATES_U1) */
8440: DR.PLAN_ID plan_id,
8441: NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
8442: DR.RESOURCE_ID resource_id,
8443: DR.organization_id organization_id,
8453: p_sys_date creation_date,
8454: l_user_id created_by
8455: FROM MSC_DEPARTMENT_RESOURCES DR,
8456: MSC_TRADING_PARTNERS P,
8457: MSC_RESOURCE_REQUIREMENTS REQ,
8458: MSC_SYSTEM_ITEMS I, -- summary enhancement change for CTO ODR
8459: MSC_CALENDAR_DATES C
8460: ---- re-ordered tables for performance
8461: WHERE DR.PLAN_ID = REQ.PLAN_ID
8552: creation_date,
8553: created_by
8554: FROM
8555: (
8556: SELECT /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) index(C MSC_CALENDAR_DATES_U1) */
8557: DR.PLAN_ID plan_id,
8558: NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
8559: DR.RESOURCE_ID resource_id,
8560: DR.organization_id organization_id,
8570: l_user_id last_updated_by,
8571: p_sys_date creation_date,
8572: l_user_id created_by
8573: FROM MSC_DEPARTMENT_RESOURCES DR,
8574: MSC_RESOURCE_REQUIREMENTS REQ,
8575: MSC_SYSTEM_ITEMS I -- summary enhancement change for CTO ODR
8576: WHERE DR.PLAN_ID = REQ.PLAN_ID
8577: AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID -- summary enhancement: made consistent
8578: AND DR.RESOURCE_ID = REQ.RESOURCE_ID
8656: -- TRUNC(REQ.START_DATE) SD_DATE,
8657: -- End Bug 3348095
8658: SUM((-1) * REQ.RESOURCE_HOURS) SD_QTY -- ATP always populates resource_hours
8659: FROM MSC_DEPARTMENT_RESOURCES DR,
8660: MSC_RESOURCE_REQUIREMENTS REQ
8661: WHERE DR.PLAN_ID = p_plan_id
8662: AND REQ.PLAN_ID = DR.PLAN_ID
8663: AND REQ.SR_INSTANCE_ID = DR.sr_instance_id
8664: AND REQ.RESOURCE_ID = DR.resource_id