[Home] [Help]
279: FROM
280: MSC_SYSTEM_ITEMS I,
281: MSC_ATP_RULES R,
282: MSC_SALES_ORDERS D,
283: MSC_CALENDAR_DATES C,
284: MSC_CALENDAR_DATES C2,
285: MSC_TRADING_PARTNERS TP
286: WHERE I.ATP_FLAG = 'Y'
287: AND I.ORGANIZATION_ID = TP.SR_TP_ID
280: MSC_SYSTEM_ITEMS I,
281: MSC_ATP_RULES R,
282: MSC_SALES_ORDERS D,
283: MSC_CALENDAR_DATES C,
284: MSC_CALENDAR_DATES C2,
285: MSC_TRADING_PARTNERS TP
286: WHERE I.ATP_FLAG = 'Y'
287: AND I.ORGANIZATION_ID = TP.SR_TP_ID
288: AND I.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
372: MSC_SYSTEM_ITEMS I,
373: MSC_SYSTEM_ITEMS I2,
374: MSC_ATP_RULES R,
375: MSC_DEMANDS D,
376: MSC_CALENDAR_DATES C,
377: MSC_CALENDAR_DATES C2,
378: MSC_TRADING_PARTNERS TP
379: WHERE I.ATP_FLAG = 'Y'
380: AND I.ORGANIZATION_ID = TP.SR_TP_ID
373: MSC_SYSTEM_ITEMS I2,
374: MSC_ATP_RULES R,
375: MSC_DEMANDS D,
376: MSC_CALENDAR_DATES C,
377: MSC_CALENDAR_DATES C2,
378: MSC_TRADING_PARTNERS TP
379: WHERE I.ATP_FLAG = 'Y'
380: AND I.ORGANIZATION_ID = TP.SR_TP_ID
381: AND I.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
435: MSC_SYSTEM_ITEMS I2,
436: MSC_ATP_RULES R,
437: MSC_SUPPLIES S,
438: MSC_SUB_INVENTORIES MSI,
439: MSC_CALENDAR_DATES C,
440: MSC_CALENDAR_DATES C2,
441: MSC_TRADING_PARTNERS TP
442: WHERE I.ATP_FLAG = 'Y'
443: AND I.ORGANIZATION_ID = TP.SR_TP_ID
436: MSC_ATP_RULES R,
437: MSC_SUPPLIES S,
438: MSC_SUB_INVENTORIES MSI,
439: MSC_CALENDAR_DATES C,
440: MSC_CALENDAR_DATES C2,
441: MSC_TRADING_PARTNERS TP
442: WHERE I.ATP_FLAG = 'Y'
443: AND I.ORGANIZATION_ID = TP.SR_TP_ID
444: AND I.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
2067: ((R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1) or
2068: (R.INCLUDE_NONSTD_WIP_RECEIPTS = 1))))
2069: AND EXISTS
2070: (SELECT 1
2071: FROM msc_calendar_dates c
2072: WHERE C.PRIOR_SEQ_NUM >=
2073: DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
2074: NULL, C.PRIOR_SEQ_NUM,
2075: MSC_ATP_FUNC.NEXT_WORK_DAY_SEQNUM
2131: AND S.NETTING_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
2132: 2, 1, S.NETTING_TYPE)))
2133: AND EXISTS
2134: (SELECT 1
2135: FROM msc_calendar_dates c
2136: WHERE C.PRIOR_SEQ_NUM >=
2137: DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
2138: NULL, C.PRIOR_SEQ_NUM,
2139: MSC_ATP_FUNC.NEXT_WORK_DAY_SEQNUM(D.ORGANIZATION_ID,
2230: ((R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1) or
2231: (R.INCLUDE_NONSTD_WIP_RECEIPTS = 1))))
2232: AND EXISTS
2233: (SELECT 1
2234: FROM msc_calendar_dates c
2235: WHERE C.PRIOR_SEQ_NUM >=
2236: DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
2237: NULL, C.PRIOR_SEQ_NUM,
2238: MSC_ATP_FUNC.NEXT_WORK_DAY_SEQNUM(D.ORGANIZATION_ID,
2308: ((R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1) or
2309: (R.INCLUDE_NONSTD_WIP_RECEIPTS = 1))))
2310: AND EXISTS
2311: (SELECT 1
2312: FROM msc_calendar_dates c
2313: WHERE C.PRIOR_SEQ_NUM >=
2314: DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
2315: NULL, C.PRIOR_SEQ_NUM,
2316: MSC_ATP_FUNC.NEXT_WORK_DAY_SEQNUM(D.ORGANIZATION_ID,
2407: msc_util.msc_log('l_default_demand_class := ' || l_default_demand_class);
2408: BEGIN
2409: SELECT cal.next_date
2410: INTO l_sys_next_date
2411: FROM msc_calendar_dates cal
2412: WHERE cal.exception_set_id = l_calendar_exception_set_id
2413: AND cal.calendar_code = l_calendar_code
2414: AND cal.calendar_date = TRUNC(l_sysdate)
2415: AND cal.sr_instance_id = p_instance_id ;
2885: NVL(l_default_demand_class,'@@@')), '@@@') demand_class,
2886: C.PRIOR_DATE SD_DATE,
2887: -1* D.USING_REQUIREMENT_QUANTITY SD_QTY
2888: --2 SD_TYPE
2889: FROM MSC_CALENDAR_DATES C,
2890: MSC_DEMANDS D,
2891: MSC_ATP_RULES R,
2892: MSC_SYSTEM_ITEMS I,
2893: MSC_SYSTEM_ITEMS I2
2940: NVL(l_default_demand_class,'@@@')), '@@@') demand_class,
2941: C.PRIOR_DATE SD_DATE,
2942: NVL(D.OLD_USING_REQUIREMENT_QUANTITY,0) SD_QTY
2943: --2 SD_TYPE
2944: FROM MSC_CALENDAR_DATES C,
2945: MSC_DEMANDS D,
2946: MSC_ATP_RULES R,
2947: MSC_SYSTEM_ITEMS I,
2948: MSC_SYSTEM_ITEMS I2
2986: NULL, C.PRIOR_SEQ_NUM,
2987: l_sysdate_seq_num - NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
2988: )
2989: UNION ALL -- new supplies information
2990: (SELECT /*+ ordered index(C,MSC_CALENDAR_DATES_U1) */I.inventory_item_id, -- 5098576/5199686
2991: Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
2992: NVL(DECODE(S.ORDER_TYPE,
2993: 5, MSC_ATP_FUNC.Get_MPS_Demand_Class(S.SCHEDULE_DESIGNATOR_ID),
2994: S.DEMAND_CLASS), NVL(l_default_demand_class, '@@@')), '@@@'),
3005: MSC_SYSTEM_ITEMS I2,
3006: MSC_SUPPLIES S,
3007: MSC_ATP_RULES R,
3008: MSC_SUB_INVENTORIES MSI,
3009: MSC_CALENDAR_DATES C
3010:
3011: /* FROM MSC_CALENDAR_DATES C,
3012: MSC_SUPPLIES S,
3013: MSC_ATP_RULES R,
3007: MSC_ATP_RULES R,
3008: MSC_SUB_INVENTORIES MSI,
3009: MSC_CALENDAR_DATES C
3010:
3011: /* FROM MSC_CALENDAR_DATES C,
3012: MSC_SUPPLIES S,
3013: MSC_ATP_RULES R,
3014: MSC_SYSTEM_ITEMS I,
3015: --- bug 2162571 add to another table to get info about product family
3092: AND NVL(MSI.inventory_atp_code,1) <> 2
3093: -- filter out non-atpable subinventories
3094: )
3095: UNION ALL -- with old supplies information
3096: (SELECT /*+ ordered index(C,MSC_CALENDAR_DATES_U1) */I.inventory_item_id, -- 5098576/5199686
3097: Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
3098: NVL(DECODE(S.ORDER_TYPE,
3099: 5, MSC_ATP_FUNC.Get_MPS_Demand_Class(S.SCHEDULE_DESIGNATOR_ID),
3100: S.DEMAND_CLASS), NVL(l_default_demand_class, '@@@')), '@@@'),
3108: TRUNC(S.OLD_First_Unit_Start_date) +1) -
3109: NVL(S.OLD_qty_completed,0), NVL(S.OLD_Daily_rate,0)),
3110: NVL( NVL(S.OLD_FIRM_QUANTITY,S.OLD_NEW_ORDER_QUANTITY),0) ) SD_QTY
3111:
3112: /*FROM MSC_CALENDAR_DATES C,
3113: MSC_SUPPLIES S,
3114: MSC_ATP_RULES R,
3115: MSC_SYSTEM_ITEMS I,
3116: MSC_SYSTEM_ITEMS I2,
3121: MSC_SYSTEM_ITEMS I2,
3122: MSC_SUPPLIES S,
3123: MSC_ATP_RULES R,
3124: MSC_SUB_INVENTORIES MSI,
3125: MSC_CALENDAR_DATES C
3126:
3127: WHERE I.ATP_FLAG = 'Y'
3128: AND I.ORGANIZATION_ID = l_organization_id
3129: AND I.SR_INSTANCE_ID = p_instance_id
3289:
3290: BEGIN
3291: SELECT cal.next_seq_num
3292: INTO l_sysdate_seq_num
3293: FROM msc_calendar_dates cal
3294: WHERE cal.exception_set_id = l_calendar_exception_set_id
3295: AND cal.calendar_code = l_calendar_code
3296: AND cal.calendar_date = TRUNC(l_sysdate)
3297: AND cal.sr_instance_id = p_instance_id ;
4305: msc_util.msc_log('After deleting data from MSC_ALLOC_SUPPLIES table');
4306:
4307: /* --------------------------------------------------------------- */
4308: -- 2566795
4309: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
4310: -- to move demand on non-working day to prior working day.
4311:
4312: l_sql_stmt_1 := 'INSERT INTO MSC_ALLOC_DEMANDS(
4313: plan_id,
4413: msc_demands d1
4414: -- time_phased_atp
4415: -- msc_trading_partners tp
4416: -- 2859130
4417: -- msc_calendar_dates cal
4418: WHERE peg2.plan_id = peg1.plan_id
4419: AND peg2.end_pegging_id = peg1.pegging_id
4420: AND peg2.sr_instance_id = peg1.sr_instance_id
4421: AND d1.plan_id = peg1.plan_id
4489:
4490: msc_util.msc_log('rows processed: ' || rows_processed);
4491: msc_util.msc_log('After inserting in msc_alloc_demands part 1');
4492:
4493: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
4494: -- to move demand on non-working day to prior working day.
4495:
4496: /* time_phased_atp - project atp forward port
4497: * If the profile is set to 'Yes' then:
4676: FROM msc_full_pegging peg2,
4677: msc_full_pegging peg1,
4678: msc_demands d
4679: -- time_phased_atp msc_trading_partners tp
4680: -- 2859130 msc_calendar_dates cal
4681: WHERE peg1.plan_id = :p_plan_id
4682: AND peg2.plan_id = peg1.plan_id
4683: AND peg2.pegging_id = peg1.end_pegging_id
4684: AND peg2.demand_id IN (-1, -2)
4741: msc_util.msc_log('after inserting item data into MSC_ALLOC_DEMANDS tables');
4742:
4743: /* ------------------------------------------------------------------ */
4744:
4745: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
4746: -- to move demand on non-working day to prior working day.
4747:
4748: l_sql_stmt_1 := 'INSERT INTO MSC_ALLOC_SUPPLIES(
4749: plan_id,
4805: msc_full_pegging peg2,
4806: msc_full_pegging peg1,
4807: msc_demands d
4808: -- time_phased_atp msc_trading_partners tp
4809: -- 2859130 msc_calendar_dates cal
4810: WHERE peg2.plan_id = peg1.plan_id
4811: AND peg2.end_pegging_id = peg1.pegging_id
4812: AND d.plan_id = peg1.plan_id
4813: AND d.demand_id = peg1.demand_id
4874: -- parallel hint can't be used with union all. Use two query instead
4875:
4876: --UNION ALL
4877:
4878: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
4879: -- to move demand on non-working day to prior working day.
4880:
4881: /* time_phased_atp - project atp forward port
4882: * If the profile is set to 'Yes' then:
5017: FROM msc_full_pegging peg2,
5018: msc_full_pegging peg1,
5019: msc_supplies s
5020: -- time_phased_atp msc_trading_partners tp
5021: -- 2859130 msc_calendar_dates cal
5022: WHERE peg1.plan_id = :p_plan_id
5023: AND peg2.plan_id = peg1.plan_id
5024: AND peg2.pegging_id = peg1.end_pegging_id
5025: AND peg2.demand_id IN (-1, -2)
5183: OBJECT_NAME => l_temp_table);
5184: msc_util.msc_log('After exception create table : ' ||l_temp_table);
5185: END;
5186:
5187: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
5188: -- to move demand on non-working day to prior working day.
5189:
5190: -- cannot use append with other hints
5191: --l_insert_stmt := 'INSERT /*+ APPEND */ INTO ' || l_temp_table || '(
5278: msc_full_pegging peg2,
5279: msc_full_pegging peg1,
5280: msc_demands d1
5281: -- time_phased_atp msc_trading_partners tp
5282: -- 2859130 msc_calendar_dates cal
5283: WHERE peg2.plan_id = peg1.plan_id
5284: AND peg2.end_pegging_id = peg1.pegging_id
5285: AND peg2.sr_instance_id = peg1.sr_instance_id
5286: AND d1.plan_id = peg1.plan_id
5352: -- parallel hint can't be used with union all. Use two query instead */
5353:
5354: -- UNION ALL
5355:
5356: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
5357: -- to move demand on non-working day to prior working day.
5358:
5359: /* time_phased_atp - project atp forward port
5360: * If the profile is set to 'Yes' then:
5527: FROM msc_full_pegging peg2,
5528: msc_full_pegging peg1,
5529: msc_demands d
5530: -- time_phased_atp msc_trading_partners tp
5531: -- 2859130 msc_calendar_dates cal
5532: WHERE peg1.plan_id = :p_plan_id
5533: AND peg2.plan_id = peg1.plan_id
5534: AND peg2.pegging_id = peg1.end_pegging_id
5535: AND peg2.demand_id IN (-1, -2)
5736: OBJECT_NAME => l_temp_table);
5737: msc_util.msc_log('After exception create table : ' ||l_temp_table);
5738: END;
5739:
5740: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
5741: -- to move demand on non-working day to prior working day.
5742:
5743: -- cannot use append with other hints
5744: --l_insert_stmt := 'INSERT INTO /*+ APPEND */ ' || l_temp_table || '(
5802: msc_full_pegging peg2,
5803: msc_full_pegging peg1,
5804: msc_demands d
5805: -- time_phased_atp msc_trading_partners tp
5806: -- 2859130 msc_calendar_dates cal
5807: WHERE peg2.plan_id = peg1.plan_id
5808: AND peg2.end_pegging_id = peg1.pegging_id
5809: AND d.plan_id = peg1.plan_id
5810: AND d.demand_id = peg1.demand_id
5869: -- parallel hint can't be used with union all. Use two query instead */
5870:
5871: -- UNION ALL
5872:
5873: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
5874: -- to move demand on non-working day to prior working day.
5875:
5876: /* time_phased_atp - project atp forward port
5877: * If the profile is set to 'Yes' then:
6014: FROM msc_full_pegging peg2,
6015: msc_full_pegging peg1,
6016: msc_supplies s
6017: -- msc_trading_partners tp
6018: -- msc_calendar_dates cal
6019: WHERE peg1.plan_id = :p_plan_id
6020: AND peg2.plan_id = peg1.plan_id
6021: AND peg2.pegging_id = peg1.end_pegging_id
6022: AND peg2.demand_id IN (-1, -2)
6713: last_updated_by,
6714: creation_date,
6715: created_by
6716: --Bug 6046524 added index hint for performance improvement.
6717: from (SELECT /*+ ORDERED index(C,MSC_CALENDAR_DATES_U1)*/
6718: I.plan_id plan_id,
6719: I.sr_instance_id,
6720: I.organization_id,
6721: I.inventory_item_id,
6731: l_user_id created_by
6732: FROM MSC_SYSTEM_ITEMS I,
6733: MSC_TRADING_PARTNERS P,
6734: MSC_DEMANDS D,
6735: MSC_CALENDAR_DATES C
6736: WHERE I.ATP_FLAG = 'Y'
6737: AND I.PLAN_ID = p_plan_id
6738: AND D.PLAN_ID = I.PLAN_ID
6739: AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
6769:
6770: UNION ALL
6771:
6772: --Bug 6046524 added index hint for performance improvement.
6773: SELECT /*+ ORDERED index(C,MSC_CALENDAR_DATES_U1)*/
6774: I.plan_id plan_id,
6775: I.sr_instance_id,
6776: I.organization_id,
6777: I.inventory_item_id,
6785: l_user_id created_by
6786: FROM MSC_SYSTEM_ITEMS I,
6787: MSC_TRADING_PARTNERS P,
6788: MSC_SUPPLIES S,
6789: MSC_CALENDAR_DATES C
6790: WHERE I.ATP_FLAG = 'Y'
6791: AND I.PLAN_ID = p_plan_id
6792: AND S.PLAN_ID = I.PLAN_ID
6793: AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
6870: l_user_id created_by
6871: FROM MSC_SYSTEM_ITEMS I,
6872: MSC_TRADING_PARTNERS P,
6873: MSC_DEMANDS D,
6874: MSC_CALENDAR_DATES C
6875: WHERE I.ATP_FLAG = 'Y'
6876: AND I.PLAN_ID = p_plan_id
6877: AND D.PLAN_ID = I.PLAN_ID
6878: AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
6922: l_user_id created_by
6923: FROM MSC_SYSTEM_ITEMS I,
6924: MSC_TRADING_PARTNERS P,
6925: MSC_SUPPLIES S,
6926: MSC_CALENDAR_DATES C
6927: WHERE I.ATP_FLAG = 'Y'
6928: AND I.PLAN_ID = p_plan_id
6929: AND S.PLAN_ID = I.PLAN_ID
6930: AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
7821: p_sys_date last_update_date,
7822: l_user_id last_updated_by,
7823: p_sys_date creation_date,
7824: l_user_id created_by
7825: FROM msc_calendar_dates c,
7826: (SELECT /*+ LEADING (I) */
7827: I.plan_id plan_id,
7828: I.sr_instance_id,
7829: I.inventory_item_id inventory_item_id,
7905: p_sys_date creation_date,
7906: l_user_id created_by
7907: FROM msc_supplies p,
7908: -- msc_trading_partners tp,
7909: -- msc_calendar_dates c,
7910: -- msc_calendar_dates c1,
7911: msc_trading_partner_sites tps,
7912: msc_system_items I
7913: WHERE I.plan_id = p_plan_id
7906: l_user_id created_by
7907: FROM msc_supplies p,
7908: -- msc_trading_partners tp,
7909: -- msc_calendar_dates c,
7910: -- msc_calendar_dates c1,
7911: msc_trading_partner_sites tps,
7912: msc_system_items I
7913: WHERE I.plan_id = p_plan_id
7914: AND I.atp_components_flag in ( 'Y', 'C')
7990: DECODE(tps.shipping_control,'BUYER',p.new_ship_date,p.new_dock_date),
7991: sum(NVL(p.implement_quantity,0) - p.new_order_quantity) sd_qty
7992: FROM msc_supplies p,
7993: -- msc_trading_partners tp,
7994: -- msc_calendar_dates c,
7995: -- msc_calendar_dates c1,
7996: msc_trading_partner_sites tps,
7997: msc_system_items I
7998: WHERE I.plan_id = p_plan_id
7991: sum(NVL(p.implement_quantity,0) - p.new_order_quantity) sd_qty
7992: FROM msc_supplies p,
7993: -- msc_trading_partners tp,
7994: -- msc_calendar_dates c,
7995: -- msc_calendar_dates c1,
7996: msc_trading_partner_sites tps,
7997: msc_system_items I
7998: WHERE I.plan_id = p_plan_id
7999: AND I.atp_components_flag in ( 'Y', 'C')
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
8209: WHERE DR.PLAN_ID = REQ.PLAN_ID
8210: AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID
8211: AND DR.RESOURCE_ID = REQ.RESOURCE_ID
8212: AND DR.SR_INSTANCE_ID = REQ.SR_INSTANCE_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,
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
8491: AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID -- summary enhancement - made consistent
8492: AND DR.RESOURCE_ID = REQ.RESOURCE_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,