[Home] [Help]
385: --- add table for resource batching
386: --- these tables are added to determine how much apacity has already been consumed by the
387: --- existing supplies
388: MSC_SYSTEM_ITEMS I,
389: MSC_SUPPLIES S,
390: MSC_UOM_CONVERSIONS MUC
391: -- Bug 2675504, 2665805,
392: --bug3394866
393: WHERE DR.PLAN_ID = p_plan_id
511: --- add table for resource batching
512: --- these tables are added to determine how much apacity has already been consumed by the
513: --- existing supplies
514: MSC_SYSTEM_ITEMS I,
515: MSC_SUPPLIES S,
516: MSC_UOM_CONVERSIONS MUC
517: -- Bug 2675504, 2665805,
518: --bug3394866
519: WHERE DR.PLAN_ID = p_plan_id
1116: MSC_RESOURCE_REQUIREMENTS REQ,
1117: -- 2859130 MSC_CALENDAR_DATES C,
1118: ---tables added for resource batching
1119: MSC_SYSTEM_ITEMS I,
1120: MSC_SUPPLIES S,
1121: MSC_UOM_CONVERSIONS MUC
1122: -- Bug 2675504, 2665805,
1123: --bug3394866
1124: WHERE DR.PLAN_ID = p_plan_id
1339: MSC_RESOURCE_REQUIREMENTS REQ,
1340: MSC_CALENDAR_DATES C,
1341: ---tables added for resource batching
1342: MSC_SYSTEM_ITEMS I,
1343: MSC_SUPPLIES S,
1344: MSC_UOM_CONVERSIONS MUC
1345: -- Bug 2675504, 2665805,
1346: --bug3394866
1347: WHERE DR.PLAN_ID = p_plan_id
1991: UNION ALL
1992:
1993: SELECT TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
1994: NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
1995: FROM MSC_SUPPLIES S,
1996: MSC_SYSTEM_ITEMS I,
1997: MSC_PLANS P -- For summary enhancement
1998: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
1999: AND I.ORGANIZATION_ID = p_org_id
2153: S.Daily_rate),
2154: 5, NVL(S.DAILY_RATE, NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)),
2155: (NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) - NVL(S.NON_NETTABLE_QTY, 0)) )SD_QTY
2156: FROM MSC_CALENDAR_DATES C,
2157: MSC_SUPPLIES S,
2158: MSC_ATP_RULES R,
2159: MSC_SYSTEM_ITEMS I,
2160: MSC_SUB_INVENTORIES MSI
2161: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2319: UNION ALL
2320: SELECT -- C.NEXT_DATE SD_DATE, -- 2859130
2321: TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
2322: NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
2323: FROM MSC_SUPPLIES S,
2324: MSC_SYSTEM_ITEMS I
2325: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2326: AND I.ORGANIZATION_ID = p_org_id
2327: AND I.SR_INSTANCE_ID = p_instance_id
2418: SELECT -- C.NEXT_DATE SD_DATE, -- 2859130
2419: C.CALENDAR_DATE SD_DATE,
2420: NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
2421: FROM MSC_CALENDAR_DATES C,
2422: MSC_SUPPLIES S,
2423: MSC_SYSTEM_ITEMS I
2424: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2425: AND I.ORGANIZATION_ID = p_org_id
2426: AND I.SR_INSTANCE_ID = p_instance_id
2757: l_null_char, --bug3814584
2758: l_null_char, --bug3814584
2759: l_null_date --bug3814584
2760: FROM MSC_CALENDAR_DATES C,
2761: MSC_SUPPLIES S,
2762: MSC_ATP_RULES R,
2763: MSC_SYSTEM_ITEMS I,
2764: MSC_SUB_INVENTORIES MSI
2765: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
3042: l_null_char, --bug3814584
3043: l_null_char, --bug3814584
3044: l_null_date --bug3814584
3045: FROM MSC_SYSTEM_ITEMS I,
3046: MSC_SUPPLIES S
3047: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
3048: AND I.ORGANIZATION_ID = p_org_id
3049: AND I.SR_INSTANCE_ID = p_instance_id
3050: AND I.PLAN_ID = p_plan_id
3262: l_null_char, --bug3814584
3263: l_null_char, --bug3814584
3264: l_null_date --bug3814584
3265: FROM MSC_SYSTEM_ITEMS I,
3266: MSC_SUPPLIES S,
3267: MSC_CALENDAR_DATES C
3268: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
3269: AND I.ORGANIZATION_ID = p_org_id
3270: AND I.SR_INSTANCE_ID = p_instance_id
11554: SELECT TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
11555: 1, p.new_ship_date,
11556: p.new_dock_date)) l_date, -- For ship_rec_cal
11557: (NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
11558: FROM msc_supplies p,
11559: msc_plans pl -- For summary enhancement
11560: WHERE (p.order_type IN (5, 2,60)
11561: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11562: AND p.order_type = 1 AND p.promised_date IS NULL))
11612: SELECT TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
11613: 1, p.new_ship_date,
11614: p.new_dock_date)) l_date, -- For ship_rec_cal
11615: (NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
11616: FROM msc_supplies p,
11617: msc_plans pl -- For summary enhancement
11618: WHERE (p.order_type IN (5, 2, 60)
11619: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11620: AND p.order_type = 1 AND p.promised_date IS NULL))
11694: 1, p.new_ship_date,
11695: p.new_dock_date)) l_date, -- For ship_rec_cal
11696: -- performance dsting rearrange signs to get rid of multiply times -1
11697: (NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
11698: FROM msc_supplies p
11699: WHERE (p.order_type IN (5, 2, 60)
11700: --include purchase orders based on profile option
11701: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11702: AND p.order_type = 1 AND p.promised_date IS NULL))
11780: 1, p.new_ship_date,
11781: p.new_dock_date)) l_date, -- For ship_rec_cal
11782: -- performance dsting rearrange signs to get rid of multiply times -1
11783: (NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
11784: FROM msc_supplies p
11785: WHERE (p.order_type IN (5, 2, 60)
11786: --include purchase orders based on profile option
11787: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11788: AND p.order_type = 1 AND p.promised_date IS NULL))
11964: l_sysdate,
11965: FND_GLOBAL.User_ID,
11966: FND_GLOBAL.User_ID
11967: -- Supplier Capacity (SCLT) Changes Begin
11968: FROM msc_supplies p
11969: WHERE (p.order_type IN (5, 2, 60)
11970: --include purchase orders based on profile option
11971: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11972: AND p.order_type = 1 AND p.promised_date IS NULL))
12134: l_sysdate,
12135: FND_GLOBAL.User_ID,
12136: FND_GLOBAL.User_ID
12137: -- Supplier Capacity (SCLT) Changes Begin
12138: FROM msc_supplies p
12139: WHERE (p.order_type IN (5, 2, 60)
12140: --include purchase orders based on profile option
12141: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
12142: AND p.order_type = 1 AND p.promised_date IS NULL))
12615: l_atp_period2 := l_null_atp_period;
12616: l_atp_supply_demand2 := l_null_atp_supply_demand;
12617:
12618: -- Planning will be populating the 'weight_capacitiy_used' and
12619: -- 'volume_capacity_used' fields in msc_supplies. For now, we need
12620: -- to multiply the quantitiy by the unit volume and unit capacity
12621: -- to obtain capacity used.
12622:
12623: SELECT NVL(unit_weight,1), NVL(unit_volume,1)
12661: UNION ALL
12662: SELECT sup.new_schedule_date l_date,
12663: -1*(sup.new_order_quantity)*l_unit_weight weight,
12664: -1*(sup.new_order_quantity)*l_unit_volume volume
12665: FROM msc_supplies sup
12666: WHERE sup.plan_id = p_plan_id
12667: AND sup.organization_id = p_to_organization_id
12668: AND sup.sr_instance_id = p_dest_org_instance_id
12669: AND sup.source_organization_id is not null
13086: null col26, -- disposition_name
13087: p_scenario_id col27, -- scenairo_id
13088: null col28, -- pegging_id
13089: null col29 -- end_pegging_id
13090: FROM msc_supplies sup,
13091: msc_interorg_ship_methods s
13092: WHERE sup.plan_id = p_plan_id
13093: AND sup.organization_id = p_to_organization_id
13094: AND sup.sr_instance_id = p_dest_org_instance_id
13246: null col26, -- disposition_name
13247: p_scenario_id col27, -- scenairo_id
13248: null col28, -- pegging_id
13249: null col29 -- end_pegging_id
13250: FROM msc_supplies sup,
13251: msc_interorg_ship_methods s
13252: WHERE sup.plan_id = p_plan_id
13253: AND sup.organization_id = p_to_organization_id
13254: AND sup.sr_instance_id = p_dest_org_instance_id