[Home] [Help]
677: trunc(nvl(s.firm_date, s.new_schedule_date))),*/
678: NULL, 1,
679: 0)) SD_QTY
680: FROM -- MSC_CALENDAR_DATES C, -- 2859130
681: MSC_SUPPLIES S,MSC_ITEM_HIERARCHY_MV MIHM
682: WHERE S.PLAN_ID = p_plan_id
683: AND S.SR_INSTANCE_ID = p_instance_id
684: AND S.INVENTORY_ITEM_ID = p_item_id
685: AND S.ORGANIZATION_ID = p_org_id
1110: -- C.NEXT_DATE)
1111: --AND C.NEXT_DATE < NVL(p_itf,
1112: -- C.NEXT_DATE + 1)
1113: )CS,
1114: MSC_ITEM_HIERARCHY_MV MIHM
1115: WHERE
1116: --4365873
1117: CS.INVENTORY_ITEM_ID = MIHM.INVENTORY_ITEM_ID(+)
1118: AND CS.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
1617: trunc(nvl(s.firm_date, s.new_schedule_date))),*/
1618: NULL, 1,
1619: 0)) SD_QTY
1620: FROM -- MSC_CALENDAR_DATES C,
1621: MSC_SUPPLIES S,MSC_ITEM_HIERARCHY_MV MIHM
1622: WHERE S.PLAN_ID = p_plan_id
1623: AND S.SR_INSTANCE_ID = p_instance_id
1624: AND S.INVENTORY_ITEM_ID = p_item_id
1625: AND S.ORGANIZATION_ID = p_org_id
2049: 28, TRUNC(SYSDATE),
2050: C.CALENDAR_DATE)
2051: AND C.CALENDAR_DATE < NVL(p_itf,
2052: C.CALENDAR_DATE + 1))CS,
2053: MSC_ITEM_HIERARCHY_MV MIHM
2054: WHERE
2055: --4365873
2056: CS.INVENTORY_ITEM_ID = MIHM.INVENTORY_ITEM_ID(+)
2057: AND CS.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
2694: null, --bug3263368 ORIG_CUSTOMER_NAME
2695: null, --bug3263368 ORIG_DEMAND_CLASS
2696: null --bug3263368 ORIG_REQUEST_DATE
2697: FROM -- MSC_CALENDAR_DATES C, -- 2859130
2698: MSC_SUPPLIES S,MSC_ITEM_HIERARCHY_MV MIHM
2699: WHERE S.PLAN_ID = p_plan_id
2700: AND S.SR_INSTANCE_ID = p_instance_id
2701: AND S.INVENTORY_ITEM_ID = p_item_id
2702: AND S.ORGANIZATION_ID = p_org_id
3277: 28, TRUNC(SYSDATE),
3278: C.CALENDAR_DATE)
3279: AND C.CALENDAR_DATE < NVL(p_itf,
3280: C.CALENDAR_DATE + 1))CS,
3281: MSC_ITEM_HIERARCHY_MV MIHM
3282: WHERE
3283: --4365873
3284: CS.INVENTORY_ITEM_ID = MIHM.INVENTORY_ITEM_ID(+)
3285: AND CS.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
3935: msc_sch_wb.atp_debug('Item_Alloc_Cum_Atp: ' || 'l_level_id = '||l_level_id);
3936: END IF;
3937: SELECT demand_class, priority, allocation_percent
3938: BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab, l_allocation_percent_tab
3939: FROM msc_item_hierarchy_mv
3940: WHERE inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
3941: AND organization_id = p_organization_id
3942: AND sr_instance_id = p_instance_id
3943: AND p_request_date BETWEEN effective_date AND disable_date
3951: msc_sch_wb.atp_debug('Item_Alloc_Cum_Atp: ' || 'l_level_id = '||l_level_id);
3952: END IF;
3953: SELECT demand_class, priority, allocation_percent
3954: BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab, l_allocation_percent_tab
3955: FROM msc_item_hierarchy_mv
3956: WHERE inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
3957: AND organization_id = p_organization_id
3958: AND sr_instance_id = p_instance_id
3959: AND p_request_date BETWEEN effective_date AND disable_date
3967: END IF;
3968: SELECT mv1.demand_class, mv1.priority, mv1.class, mv1.partner_id, 0, mv1.allocation_percent
3969: BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab,
3970: l_class_tab, l_partner_tab, l_fw_consume_tab, l_allocation_percent_tab
3971: FROM msc_item_hierarchy_mv mv1
3972: WHERE mv1.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
3973: AND mv1.organization_id = p_organization_id
3974: AND mv1.sr_instance_id = p_instance_id
3975: AND p_request_date BETWEEN mv1.effective_date AND mv1.disable_date
3988: END IF;
3989: SELECT mv1.demand_class, mv1.priority, mv1.class, mv1.partner_id, 0, mv1.allocation_percent
3990: BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab,
3991: l_class_tab, l_partner_tab, l_fw_consume_tab, l_allocation_percent_tab
3992: FROM msc_item_hierarchy_mv mv1
3993: WHERE mv1.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
3994: AND mv1.organization_id = p_organization_id
3995: AND mv1.sr_instance_id = p_instance_id
3996: AND p_request_date BETWEEN mv1.effective_date AND mv1.disable_date
8774: msc_sch_wb.atp_debug('Supplier_Alloc_Cum_Atp: ' || 'l_allocation_percent = '||l_allocation_percent);
8775: END IF;
8776: SELECT demand_class, priority
8777: BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab
8778: FROM msc_item_hierarchy_mv
8779: --WHERE inventory_item_id = p_sup_atp_info_rec.inventory_item_id
8780: WHERE inventory_item_id = l_inv_item_id
8781: AND organization_id = p_sup_atp_info_rec.organization_id -- Ship To org
8782: AND sr_instance_id = l_instance_id
8791: msc_sch_wb.atp_debug('Supplier_Alloc_Cum_Atp: ' || 'l_level_id = '||l_level_id);
8792: END IF;
8793: SELECT demand_class, priority
8794: BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab
8795: FROM msc_item_hierarchy_mv
8796: --WHERE inventory_item_id = p_sup_atp_info_rec.inventory_item_id
8797: WHERE inventory_item_id = l_inv_item_id
8798: AND organization_id = p_sup_atp_info_rec.organization_id
8799: AND sr_instance_id = l_instance_id
8810: END IF;
8811: SELECT mv1.demand_class, mv1.priority, mv1.class, mv1.partner_id
8812: BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab,
8813: l_class_tab, l_partner_tab
8814: FROM msc_item_hierarchy_mv mv1
8815: --WHERE mv1.inventory_item_id = p_sup_atp_info_rec.inventory_item_id
8816: WHERE inventory_item_id = l_inv_item_id
8817: AND mv1.organization_id = p_sup_atp_info_rec.organization_id
8818: AND mv1.sr_instance_id = l_instance_id
8834: END IF;
8835: SELECT mv1.demand_class, mv1.priority, mv1.class, mv1.partner_id
8836: BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab,
8837: l_class_tab, l_partner_tab
8838: FROM msc_item_hierarchy_mv mv1
8839: --WHERE mv1.inventory_item_id = p_sup_atp_info_rec.inventory_item_id
8840: WHERE mv1.inventory_item_id = l_inv_item_id
8841: AND mv1.organization_id = p_sup_atp_info_rec.organization_id
8842: AND mv1.sr_instance_id = l_instance_id
9030: AND c.calendar_code = l_calendar_code
9031: AND c.exception_set_id = l_calendar_exception_set_id
9032: AND c.sr_instance_id = l_instance_id
9033: AND c.calendar_date >= NVL(p_sup_atp_info_rec.sup_cap_cum_date, l_plan_start_date))CS,
9034: msc_item_hierarchy_mv mihm
9035: WHERE
9036: --4365873
9037: CS.INVENTORY_ITEM_ID = MIHM.INVENTORY_ITEM_ID(+)
9038: AND CS.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
9104: ))),*/
9105: NULL, 1,
9106: 0)) quantity
9107: -- Supplier Capacity (SCLT) Changes Begin
9108: FROM msc_supplies p,msc_item_hierarchy_mv MIHM
9109: WHERE (p.order_type IN (5, 2)
9110: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = trunc(MSC_ATP_REQ.G_PROMISE_DATE) --4135752
9111: AND p.order_type = 1 AND p.promised_date IS NULL))
9112: -- Supplier Capacity (SCLT) Accumulation Ignore Purchase Orders
9189: AND c.calendar_code = l_calendar_code
9190: AND c.exception_set_id = l_calendar_exception_set_id
9191: AND c.sr_instance_id = l_instance_id
9192: AND c.calendar_date >= NVL(p_sup_atp_info_rec.sup_cap_cum_date, l_plan_start_date))CS,
9193: msc_item_hierarchy_mv mihm
9194: WHERE
9195: --4365873
9196: CS.INVENTORY_ITEM_ID = MIHM.INVENTORY_ITEM_ID(+)
9197: AND CS.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
9265: ))), */
9266: NULL, 1,
9267: 0)) quantity
9268: -- Supplier Capacity (SCLT) Changes Begin
9269: FROM msc_supplies p,msc_item_hierarchy_mv MIHM
9270: WHERE (p.order_type IN (5, 2)
9271: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
9272: AND p.order_type = 1 AND p.promised_date IS NULL))
9273: -- Supplier Capacity (SCLT) Accumulation Ignore Purchase Orders
9430: AND c.calendar_code = l_calendar_code
9431: AND c.exception_set_id = l_calendar_exception_set_id
9432: AND c.sr_instance_id = l_instance_id
9433: AND c.calendar_date >= NVL(p_sup_atp_info_rec.sup_cap_cum_date, l_plan_start_date))CS,
9434: msc_item_hierarchy_mv MIHM
9435: WHERE
9436: --4365873
9437: cs.INVENTORY_ITEM_ID = MIHM.INVENTORY_ITEM_ID(+)
9438: AND cs.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
9548: -- Unallocated_Quantity
9549: -- ship_rec_cal rearrange signs to get rid of multiply times -1
9550: (NVL(p.implement_quantity,0) - p.new_order_quantity)
9551: -- Supplier Capacity (SCLT) Changes Begin
9552: FROM msc_supplies p,msc_item_hierarchy_mv MIHM
9553: WHERE (p.order_type IN (5, 2)
9554: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
9555: AND p.order_type = 1 AND p.promised_date IS NULL))
9556:
9713: AND c.calendar_code = l_calendar_code
9714: AND c.exception_set_id = l_calendar_exception_set_id
9715: AND c.sr_instance_id = l_instance_id
9716: AND c.calendar_date >= NVL(p_sup_atp_info_rec.sup_cap_cum_date, l_plan_start_date))CS,
9717: msc_item_hierarchy_mv MIHM
9718: WHERE
9719: --4365873
9720: cs.INVENTORY_ITEM_ID = MIHM.INVENTORY_ITEM_ID(+)
9721: AND cs.SR_INSTANCE_ID = MIHM.SR_INSTANCE_ID (+)
9831: -- Unallocated_Quantity
9832: -- ship_rec_cal rearrange signs to get rid of multiply times -1
9833: (NVL(p.implement_quantity,0) - p.new_order_quantity)
9834: -- Supplier Capacity (SCLT) Changes Begin
9835: FROM msc_supplies p ,msc_item_hierarchy_mv MIHM
9836: WHERE (p.order_type IN (5, 2)
9837: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
9838: AND p.order_type = 1 AND p.promised_date IS NULL))
9839:
10408: --SELECT distinct allocation_rule_name, time_phase_id
10409: --bug3948494 removed distinct and introduced rownum
10410: SELECT allocation_rule_name, time_phase_id
10411: INTO l_rule_name, l_time_phase
10412: FROM msc_item_hierarchy_mv
10413: WHERE inventory_item_id = p_inv_item_id
10414: AND organization_id = p_org_id
10415: AND sr_instance_id = p_instance_id
10416: AND p_request_date between effective_date and disable_date
10442: WHEN NO_DATA_FOUND THEN
10443: IF G_HIERARCHY_PROFILE = 1 THEN
10444: SELECT DECODE(count(allocation_percent), 0, NULL, 0)
10445: INTO x_alloc_percent
10446: FROM msc_item_hierarchy_mv
10447: WHERE inventory_item_id = p_inv_item_id
10448: AND organization_id = p_org_id
10449: AND sr_instance_id = p_instance_id
10450: AND p_request_date between effective_date and disable_date
11273: IF G_HIERARCHY_PROFILE = 1 THEN
11274:
11275: SELECT mv.priority, mv.level_id, mv.class, mv.partner_id
11276: INTO l_priority, l_level_id, l_class, l_partner_id
11277: FROM msc_item_hierarchy_mv mv
11278: WHERE mv.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
11279: AND mv.organization_id = p_atp_record.organization_id
11280: AND mv.sr_instance_id = p_atp_record.instance_id
11281: AND p_atp_record.requested_ship_date BETWEEN effective_date
11286: ELSE
11287:
11288: SELECT mv.priority, mv.level_id, mv.class, mv.partner_id
11289: INTO l_priority, l_level_id, l_class, l_partner_id
11290: FROM msc_item_hierarchy_mv mv
11291: WHERE mv.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
11292: AND mv.organization_id = p_atp_record.organization_id
11293: AND mv.sr_instance_id = p_atp_record.instance_id
11294: AND p_atp_record.requested_ship_date BETWEEN effective_date
11324: END IF;
11325:
11326: SELECT mv.demand_class, mv.priority, mv.allocation_percent
11327: BULK COLLECT INTO l_demand_class_tab_new, l_dmd_class_priority_tab_new, l_allocation_percent_tab --6359986
11328: FROM msc_item_hierarchy_mv mv
11329: WHERE mv.inventory_item_id = l_item_to_use -- time_phased_atp
11330: AND mv.organization_id = p_atp_record.organization_id
11331: AND mv.sr_instance_id = p_atp_record.instance_id
11332: AND p_atp_record.requested_ship_date BETWEEN effective_date
11341: END IF;
11342:
11343: SELECT mv.demand_class, mv.priority, mv.allocation_percent
11344: BULK COLLECT INTO l_demand_class_tab_new, l_dmd_class_priority_tab_new, l_allocation_percent_tab --6359986
11345: FROM msc_item_hierarchy_mv mv
11346: WHERE mv.inventory_item_id = l_item_to_use -- time_phased_atp
11347: AND mv.organization_id = p_atp_record.organization_id
11348: AND mv.sr_instance_id = p_atp_record.instance_id
11349: AND p_atp_record.requested_ship_date BETWEEN effective_date
11358: msc_sch_wb.atp_debug('Stealing: ' || 'l_level_id = '||l_level_id);
11359: END IF;
11360: SELECT mv1.demand_class, mv1.priority, mv1.allocation_percent
11361: BULK COLLECT INTO l_demand_class_tab_new, l_dmd_class_priority_tab_new,l_allocation_percent_tab --6359986
11362: FROM msc_item_hierarchy_mv mv1
11363: WHERE mv1.inventory_item_id = l_item_to_use -- time_phased_atp
11364: AND mv1.organization_id = p_atp_record.organization_id
11365: AND mv1.sr_instance_id = p_atp_record.instance_id
11366: AND p_atp_record.requested_ship_date BETWEEN mv1.effective_date
11379: msc_sch_wb.atp_debug('Stealing: ' || 'l_level_id = '||l_level_id);
11380: END IF;
11381: SELECT mv1.demand_class, mv1.priority, mv1.allocation_percent
11382: BULK COLLECT INTO l_demand_class_tab_new, l_dmd_class_priority_tab_new,l_allocation_percent_tab --6359986
11383: FROM msc_item_hierarchy_mv mv1
11384: WHERE mv1.inventory_item_id = l_item_to_use -- time_phased_atp
11385: AND mv1.organization_id = p_atp_record.organization_id
11386: AND mv1.sr_instance_id = p_atp_record.instance_id
11387: AND p_atp_record.requested_ship_date BETWEEN mv1.effective_date