[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
6729: l_user_id last_updated_by,
6730: p_sys_date creation_date,
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
6783: l_user_id last_updated_by,
6784: p_sys_date creation_date,
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
6868: l_user_id last_updated_by,
6869: p_sys_date creation_date,
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
6920: l_user_id last_updated_by,
6921: p_sys_date creation_date,
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
7045: l_user_id last_updated_by,
7046: p_sys_date creation_date,
7047: l_user_id created_by
7048: FROM MSC_SYSTEM_ITEMS I,
7049: -- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
7050: -- Not required as calendar has been removed
7051: MSC_DEMANDS D
7052: WHERE I.ATP_FLAG = 'Y'
7053: AND I.PLAN_ID = p_plan_id
7075: l_user_id last_updated_by,
7076: p_sys_date creation_date,
7077: l_user_id created_by
7078: FROM MSC_SYSTEM_ITEMS I,
7079: -- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
7080: -- Not required as calendar has been removed
7081: MSC_SUPPLIES S
7082: WHERE I.ATP_FLAG = 'Y'
7083: AND I.PLAN_ID = p_plan_id
7153: l_user_id last_updated_by,
7154: p_sys_date creation_date,
7155: l_user_id created_by
7156: FROM MSC_SYSTEM_ITEMS I,
7157: -- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
7158: -- Not required as calendar has been removed
7159: MSC_DEMANDS D
7160: WHERE I.ATP_FLAG = 'Y'
7161: AND I.PLAN_ID = p_plan_id
7184: l_user_id last_updated_by,
7185: p_sys_date creation_date,
7186: l_user_id created_by
7187: FROM MSC_SYSTEM_ITEMS I,
7188: -- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
7189: -- Not required as calendar has been removed
7190: MSC_SUPPLIES S
7191: WHERE I.ATP_FLAG = 'Y'
7192: AND I.PLAN_ID = p_plan_id
7780: l_org_id,
7781: l_cutoff_date,
7782: l_calendar_code
7783: FROM msc_plans p,
7784: msc_trading_partners tp
7785: WHERE p.plan_id = p_plan_id
7786: AND p.organization_id = tp.sr_tp_id
7787: AND p.sr_instance_id = tp.sr_instance_id
7788: AND tp.partner_type = 3;
7904: l_user_id last_updated_by,
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
7989: P.supplier_site_id,
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
8199: NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY)), 1) SD_QTY,
8200: NVL(DR.BATCHABLE_FLAG,2) BATCHABLE_FLAG,
8201: DECODE(DR.UOM_CLASS_TYPE,1 , I.WEIGHT_UOM, 2, I.VOLUME_UOM) UOM_CODE
8202: FROM MSC_DEPARTMENT_RESOURCES DR,
8203: MSC_TRADING_PARTNERS P,
8204: MSC_RESOURCE_REQUIREMENTS REQ,
8205: MSC_SYSTEM_ITEMS I,
8206: MSC_SUPPLIES S,
8207: ---- re-ordered tables for performance
8481: l_user_id last_updated_by,
8482: p_sys_date creation_date,
8483: l_user_id created_by
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