[Home] [Help]
2186: BULK COLLECT INTO x_atp_dates, x_atp_qtys
2187: FROM (
2188: SELECT C.PRIOR_DATE SD_DATE,
2189: -1* D.USING_REQUIREMENT_QUANTITY SD_QTY
2190: FROM MSC_CALENDAR_DATES C,
2191: MSC_DEMANDS D,
2192: MSC_ATP_RULES R,
2193: MSC_SYSTEM_ITEMS I,
2194: MSC_SYSTEM_ITEMS I0
2237: -1*(D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),
2238: D.COMPLETED_QUANTITY)) SD_QTY
2239: FROM
2240: -- Bug 1756263, performance fix, use EXISTS subquery instead.
2241: --MSC_CALENDAR_DATES C,
2242: MSC_SALES_ORDERS D,
2243: MSC_ATP_RULES R,
2244: MSC_SYSTEM_ITEMS I,
2245: MSC_SYSTEM_ITEMS I0,
2242: MSC_SALES_ORDERS D,
2243: MSC_ATP_RULES R,
2244: MSC_SYSTEM_ITEMS I,
2245: MSC_SYSTEM_ITEMS I0,
2246: MSC_CALENDAR_DATES C
2247: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2248: AND I0.ORGANIZATION_ID = p_org_id
2249: AND I0.SR_INSTANCE_ID = p_instance_id
2250: AND I0.PLAN_ID = p_plan_id
2305: -- non_nettable_qty and need to be excluded from ATP calculations.
2306:
2307: (NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) - NVL(S.NON_NETTABLE_QTY, 0)) )SD_QTY
2308: -- NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) )SD_QTY
2309: FROM MSC_CALENDAR_DATES C,
2310: MSC_SUPPLIES S,
2311: MSC_ATP_RULES R,
2312: MSC_SYSTEM_ITEMS I,
2313: MSC_SUB_INVENTORIES MSI
2717: MTP.PARTNER_NAME, --bug3263368
2718: D.DEMAND_CLASS, --bug3263368
2719: DECODE(D.ORDER_DATE_TYPE_CODE,2,D.REQUEST_DATE,
2720: D.REQUEST_SHIP_DATE) --bug3263368
2721: FROM MSC_CALENDAR_DATES C,
2722: MSC_DEMANDS D,
2723: MSC_ATP_RULES R,
2724: MSC_SYSTEM_ITEMS I,
2725: MSC_SYSTEM_ITEMS I0,
2812: MSC_SALES_ORDERS D,
2813: MSC_ATP_RULES R,
2814: MSC_SYSTEM_ITEMS I,
2815: MSC_SYSTEM_ITEMS I0,
2816: MSC_CALENDAR_DATES C,
2817: MSC_TRADING_PARTNERS MTP,--bug3263368
2818: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2819: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2820: AND I0.ORGANIZATION_ID = p_org_id
2935: l_null_char, --bug3814584
2936: l_null_char, --bug3814584
2937: l_null_char, --bug3814584
2938: l_null_date --bug3814584
2939: FROM MSC_CALENDAR_DATES C,
2940: MSC_SUPPLIES S,
2941: MSC_ATP_RULES R,
2942: MSC_SYSTEM_ITEMS I,
2943: MSC_SUB_INVENTORIES MSI
5458: AND P.PLAN_ID = AD.PLAN_ID
5459: AND (AD.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
5460: OR AD.REFRESH_NUMBER = p_refresh_number)
5461: -- since repetitive schedule demand is not supported in this case
5462: -- join to msc_calendar_dates is not needed.
5463:
5464: UNION ALL
5465:
5466: SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
8893: msc_plan_organizations po,
8894: msc_system_items i2,
8895: msc_trading_partners tp,
8896: msc_atp_rules r,
8897: msc_calendar_dates c1,
8898: msc_calendar_dates c2
8899: WHERE mp.plan_id = p_plan_id
8900: AND po.plan_id = mp.plan_id
8901: AND i2.organization_id = po.organization_id
8894: msc_system_items i2,
8895: msc_trading_partners tp,
8896: msc_atp_rules r,
8897: msc_calendar_dates c1,
8898: msc_calendar_dates c2
8899: WHERE mp.plan_id = p_plan_id
8900: AND po.plan_id = mp.plan_id
8901: AND i2.organization_id = po.organization_id
8902: AND i2.sr_instance_id = po.sr_instance_id
8955: -- populate ATF date for PF items
8956: UPDATE msc_system_items i
8957: SET aggregate_time_fence_date =
8958: (SELECT c2.calendar_date
8959: FROM msc_calendar_dates c2,
8960: msc_calendar_dates c1,
8961: msc_atp_rules r,
8962: msc_trading_partners tp,
8963: msc_plans mp,
8956: UPDATE msc_system_items i
8957: SET aggregate_time_fence_date =
8958: (SELECT c2.calendar_date
8959: FROM msc_calendar_dates c2,
8960: msc_calendar_dates c1,
8961: msc_atp_rules r,
8962: msc_trading_partners tp,
8963: msc_plans mp,
8964: msc_system_items i2