[Home] [Help]
2193: BULK COLLECT INTO x_atp_dates, x_atp_qtys
2194: FROM (
2195: SELECT C.PRIOR_DATE SD_DATE,
2196: -1* D.USING_REQUIREMENT_QUANTITY SD_QTY
2197: FROM MSC_CALENDAR_DATES C,
2198: MSC_DEMANDS D,
2199: MSC_ATP_RULES R,
2200: MSC_SYSTEM_ITEMS I,
2201: MSC_SYSTEM_ITEMS I0
2244: -1*(D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),
2245: D.COMPLETED_QUANTITY)) SD_QTY
2246: FROM
2247: -- Bug 1756263, performance fix, use EXISTS subquery instead.
2248: --MSC_CALENDAR_DATES C,
2249: MSC_SALES_ORDERS D,
2250: MSC_ATP_RULES R,
2251: MSC_SYSTEM_ITEMS I,
2252: MSC_SYSTEM_ITEMS I0,
2249: MSC_SALES_ORDERS D,
2250: MSC_ATP_RULES R,
2251: MSC_SYSTEM_ITEMS I,
2252: MSC_SYSTEM_ITEMS I0,
2253: MSC_CALENDAR_DATES C
2254: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2255: AND I0.ORGANIZATION_ID = p_org_id
2256: AND I0.SR_INSTANCE_ID = p_instance_id
2257: AND I0.PLAN_ID = p_plan_id
2312: -- non_nettable_qty and need to be excluded from ATP calculations.
2313:
2314: (NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) - NVL(S.NON_NETTABLE_QTY, 0)) )SD_QTY
2315: -- NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) )SD_QTY
2316: FROM MSC_CALENDAR_DATES C,
2317: MSC_SUPPLIES S,
2318: MSC_ATP_RULES R,
2319: MSC_SYSTEM_ITEMS I,
2320: MSC_SYSTEM_ITEMS I0, --Bug 13072988
2729: MTP.PARTNER_NAME, --bug3263368
2730: D.DEMAND_CLASS, --bug3263368
2731: DECODE(D.ORDER_DATE_TYPE_CODE,2,D.REQUEST_DATE,
2732: D.REQUEST_SHIP_DATE) --bug3263368
2733: FROM MSC_CALENDAR_DATES C,
2734: MSC_DEMANDS D,
2735: MSC_ATP_RULES R,
2736: MSC_SYSTEM_ITEMS I,
2737: MSC_SYSTEM_ITEMS I0,
2824: MSC_SALES_ORDERS D,
2825: MSC_ATP_RULES R,
2826: MSC_SYSTEM_ITEMS I,
2827: MSC_SYSTEM_ITEMS I0,
2828: MSC_CALENDAR_DATES C,
2829: MSC_TRADING_PARTNERS MTP,--bug3263368
2830: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2831: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2832: AND I0.ORGANIZATION_ID = p_org_id
2947: l_null_char, --bug3814584
2948: l_null_char, --bug3814584
2949: l_null_char, --bug3814584
2950: l_null_date --bug3814584
2951: FROM MSC_CALENDAR_DATES C,
2952: MSC_SUPPLIES S,
2953: MSC_ATP_RULES R,
2954: MSC_SYSTEM_ITEMS I,
2955: MSC_SYSTEM_ITEMS I0, --Bug 13072988
5484: AND P.PLAN_ID = AD.PLAN_ID
5485: AND (AD.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
5486: OR AD.REFRESH_NUMBER = p_refresh_number)
5487: -- since repetitive schedule demand is not supported in this case
5488: -- join to msc_calendar_dates is not needed.
5489:
5490: UNION ALL
5491:
5492: SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
8919: msc_plan_organizations po,
8920: msc_system_items i2,
8921: msc_trading_partners tp,
8922: msc_atp_rules r,
8923: msc_calendar_dates c1,
8924: msc_calendar_dates c2
8925: WHERE mp.plan_id = p_plan_id
8926: AND po.plan_id = mp.plan_id
8927: AND i2.organization_id = po.organization_id
8920: msc_system_items i2,
8921: msc_trading_partners tp,
8922: msc_atp_rules r,
8923: msc_calendar_dates c1,
8924: msc_calendar_dates c2
8925: WHERE mp.plan_id = p_plan_id
8926: AND po.plan_id = mp.plan_id
8927: AND i2.organization_id = po.organization_id
8928: AND i2.sr_instance_id = po.sr_instance_id
8981: -- populate ATF date for PF items
8982: UPDATE msc_system_items i
8983: SET aggregate_time_fence_date =
8984: (SELECT c2.calendar_date
8985: FROM msc_calendar_dates c2,
8986: msc_calendar_dates c1,
8987: msc_atp_rules r,
8988: msc_trading_partners tp,
8989: msc_plans mp,
8982: UPDATE msc_system_items i
8983: SET aggregate_time_fence_date =
8984: (SELECT c2.calendar_date
8985: FROM msc_calendar_dates c2,
8986: msc_calendar_dates c1,
8987: msc_atp_rules r,
8988: msc_trading_partners tp,
8989: msc_plans mp,
8990: msc_system_items i2