[Home] [Help]
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
289: AND I.PLAN_ID = -1
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
382: AND I.PLAN_ID = -1
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
445: AND I.PLAN_ID = -1
2375: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2376:
2377: /* SELECT sr_tp_id
2378: BULK COLLECT INTO l_org_ids
2379: FROM msc_trading_partners
2380: WHERE sr_instance_id = p_instance_id and partner_type = 3;*/
2381:
2382: SELECT ORGANIZATION_ID ---bug3049003
2383: BULK COLLECT INTO l_org_ids
3255: END IF;
3256:
3257: /*SELECT sr_tp_id
3258: BULK COLLECT INTO l_org_ids
3259: FROM msc_trading_partners
3260: WHERE sr_instance_id = p_instance_id and partner_type = 3;*/
3261:
3262:
3263: SELECT ORGANIZATION_ID ---bug3049003
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,
4411: msc_full_pegging peg2,
4412: msc_full_pegging peg1 ,
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
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:
4675: d.request_ship_date)request_date --bug3263368
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
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,
4804: FROM msc_supplies s,
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
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:
5016: -- repeating for the same transaction_id
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
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 || '(
5277: FROM msc_demands d2,
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
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:
5526: d.request_ship_date) request_date -- Bug 3370201
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
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 || '(
5801: FROM msc_supplies s,
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
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:
6013: -- repeating for the same transaction_id
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
6700: l_user_id last_updated_by,
6701: p_sys_date creation_date,
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
6754: l_user_id last_updated_by,
6755: p_sys_date creation_date,
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
6839: l_user_id last_updated_by,
6840: p_sys_date creation_date,
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
6891: l_user_id last_updated_by,
6892: p_sys_date creation_date,
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
7016: l_user_id last_updated_by,
7017: p_sys_date creation_date,
7018: l_user_id created_by
7019: FROM MSC_SYSTEM_ITEMS I,
7020: -- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
7021: -- Not required as calendar has been removed
7022: MSC_DEMANDS D
7023: WHERE I.ATP_FLAG = 'Y'
7024: AND I.PLAN_ID = p_plan_id
7046: l_user_id last_updated_by,
7047: p_sys_date creation_date,
7048: l_user_id created_by
7049: FROM MSC_SYSTEM_ITEMS I,
7050: -- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
7051: -- Not required as calendar has been removed
7052: MSC_SUPPLIES S
7053: WHERE I.ATP_FLAG = 'Y'
7054: AND I.PLAN_ID = p_plan_id
7124: l_user_id last_updated_by,
7125: p_sys_date creation_date,
7126: l_user_id created_by
7127: FROM MSC_SYSTEM_ITEMS I,
7128: -- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
7129: -- Not required as calendar has been removed
7130: MSC_DEMANDS D
7131: WHERE I.ATP_FLAG = 'Y'
7132: AND I.PLAN_ID = p_plan_id
7155: l_user_id last_updated_by,
7156: p_sys_date creation_date,
7157: l_user_id created_by
7158: FROM MSC_SYSTEM_ITEMS I,
7159: -- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
7160: -- Not required as calendar has been removed
7161: MSC_SUPPLIES S
7162: WHERE I.ATP_FLAG = 'Y'
7163: AND I.PLAN_ID = p_plan_id
7751: l_org_id,
7752: l_cutoff_date,
7753: l_calendar_code
7754: FROM msc_plans p,
7755: msc_trading_partners tp
7756: WHERE p.plan_id = p_plan_id
7757: AND p.organization_id = tp.sr_tp_id
7758: AND p.sr_instance_id = tp.sr_instance_id
7759: AND tp.partner_type = 3;
7875: l_user_id last_updated_by,
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
7960: P.supplier_site_id,
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
8170: NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY)), 1) SD_QTY,
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
8452: l_user_id last_updated_by,
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