[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)
6684: last_updated_by,
6685: creation_date,
6686: created_by
6687: --Bug 6046524 added index hint for performance improvement.
6688: from (SELECT /*+ ORDERED index(C,MSC_CALENDAR_DATES_U1)*/
6689: I.plan_id plan_id,
6690: I.sr_instance_id,
6691: I.organization_id,
6692: I.inventory_item_id,
6702: l_user_id created_by
6703: FROM MSC_SYSTEM_ITEMS I,
6704: MSC_TRADING_PARTNERS P,
6705: MSC_DEMANDS D,
6706: MSC_CALENDAR_DATES C
6707: WHERE I.ATP_FLAG = 'Y'
6708: AND I.PLAN_ID = p_plan_id
6709: AND D.PLAN_ID = I.PLAN_ID
6710: AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
6740:
6741: UNION ALL
6742:
6743: --Bug 6046524 added index hint for performance improvement.
6744: SELECT /*+ ORDERED index(C,MSC_CALENDAR_DATES_U1)*/
6745: I.plan_id plan_id,
6746: I.sr_instance_id,
6747: I.organization_id,
6748: I.inventory_item_id,
6756: l_user_id created_by
6757: FROM MSC_SYSTEM_ITEMS I,
6758: MSC_TRADING_PARTNERS P,
6759: MSC_SUPPLIES S,
6760: MSC_CALENDAR_DATES C
6761: WHERE I.ATP_FLAG = 'Y'
6762: AND I.PLAN_ID = p_plan_id
6763: AND S.PLAN_ID = I.PLAN_ID
6764: AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
6841: l_user_id created_by
6842: FROM MSC_SYSTEM_ITEMS I,
6843: MSC_TRADING_PARTNERS P,
6844: MSC_DEMANDS D,
6845: MSC_CALENDAR_DATES C
6846: WHERE I.ATP_FLAG = 'Y'
6847: AND I.PLAN_ID = p_plan_id
6848: AND D.PLAN_ID = I.PLAN_ID
6849: AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
6893: l_user_id created_by
6894: FROM MSC_SYSTEM_ITEMS I,
6895: MSC_TRADING_PARTNERS P,
6896: MSC_SUPPLIES S,
6897: MSC_CALENDAR_DATES C
6898: WHERE I.ATP_FLAG = 'Y'
6899: AND I.PLAN_ID = p_plan_id
6900: AND S.PLAN_ID = I.PLAN_ID
6901: AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
7792: p_sys_date last_update_date,
7793: l_user_id last_updated_by,
7794: p_sys_date creation_date,
7795: l_user_id created_by
7796: FROM msc_calendar_dates c,
7797: (SELECT /*+ LEADING (I) */
7798: I.plan_id plan_id,
7799: I.sr_instance_id,
7800: I.inventory_item_id inventory_item_id,
7876: p_sys_date creation_date,
7877: l_user_id created_by
7878: FROM msc_supplies p,
7879: -- msc_trading_partners tp,
7880: -- msc_calendar_dates c,
7881: -- msc_calendar_dates c1,
7882: msc_trading_partner_sites tps,
7883: msc_system_items I
7884: WHERE I.plan_id = p_plan_id
7877: l_user_id created_by
7878: FROM msc_supplies p,
7879: -- msc_trading_partners tp,
7880: -- msc_calendar_dates c,
7881: -- msc_calendar_dates c1,
7882: msc_trading_partner_sites tps,
7883: msc_system_items I
7884: WHERE I.plan_id = p_plan_id
7885: AND I.atp_components_flag in ( 'Y', 'C')
7961: DECODE(tps.shipping_control,'BUYER',p.new_ship_date,p.new_dock_date),
7962: sum(NVL(p.implement_quantity,0) - p.new_order_quantity) sd_qty
7963: FROM msc_supplies p,
7964: -- msc_trading_partners tp,
7965: -- msc_calendar_dates c,
7966: -- msc_calendar_dates c1,
7967: msc_trading_partner_sites tps,
7968: msc_system_items I
7969: WHERE I.plan_id = p_plan_id
7962: sum(NVL(p.implement_quantity,0) - p.new_order_quantity) sd_qty
7963: FROM msc_supplies p,
7964: -- msc_trading_partners tp,
7965: -- msc_calendar_dates c,
7966: -- msc_calendar_dates c1,
7967: msc_trading_partner_sites tps,
7968: msc_system_items I
7969: WHERE I.plan_id = p_plan_id
7970: AND I.atp_components_flag in ( 'Y', 'C')
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
8180: WHERE DR.PLAN_ID = REQ.PLAN_ID
8181: AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID
8182: AND DR.RESOURCE_ID = REQ.RESOURCE_ID
8183: AND DR.SR_INSTANCE_ID = REQ.SR_INSTANCE_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,
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
8462: AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID -- summary enhancement - made consistent
8463: AND DR.RESOURCE_ID = REQ.RESOURCE_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,