[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
11521: SELECT TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
11522: 1, p.new_ship_date,
11523: p.new_dock_date)) l_date, -- For ship_rec_cal
11524: (NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
11525: FROM msc_supplies p,
11526: msc_plans pl -- For summary enhancement
11527: WHERE (p.order_type IN (5, 2,60)
11528: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11529: AND p.order_type = 1 AND p.promised_date IS NULL))
11579: SELECT TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
11580: 1, p.new_ship_date,
11581: p.new_dock_date)) l_date, -- For ship_rec_cal
11582: (NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
11583: FROM msc_supplies p,
11584: msc_plans pl -- For summary enhancement
11585: WHERE (p.order_type IN (5, 2, 60)
11586: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11587: AND p.order_type = 1 AND p.promised_date IS NULL))
11661: 1, p.new_ship_date,
11662: p.new_dock_date)) l_date, -- For ship_rec_cal
11663: -- performance dsting rearrange signs to get rid of multiply times -1
11664: (NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
11665: FROM msc_supplies p
11666: WHERE (p.order_type IN (5, 2, 60)
11667: --include purchase orders based on profile option
11668: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11669: AND p.order_type = 1 AND p.promised_date IS NULL))
11747: 1, p.new_ship_date,
11748: p.new_dock_date)) l_date, -- For ship_rec_cal
11749: -- performance dsting rearrange signs to get rid of multiply times -1
11750: (NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
11751: FROM msc_supplies p
11752: WHERE (p.order_type IN (5, 2, 60)
11753: --include purchase orders based on profile option
11754: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11755: AND p.order_type = 1 AND p.promised_date IS NULL))
11931: l_sysdate,
11932: FND_GLOBAL.User_ID,
11933: FND_GLOBAL.User_ID
11934: -- Supplier Capacity (SCLT) Changes Begin
11935: FROM msc_supplies p
11936: WHERE (p.order_type IN (5, 2, 60)
11937: --include purchase orders based on profile option
11938: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
11939: AND p.order_type = 1 AND p.promised_date IS NULL))
12101: l_sysdate,
12102: FND_GLOBAL.User_ID,
12103: FND_GLOBAL.User_ID
12104: -- Supplier Capacity (SCLT) Changes Begin
12105: FROM msc_supplies p
12106: WHERE (p.order_type IN (5, 2, 60)
12107: --include purchase orders based on profile option
12108: OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
12109: AND p.order_type = 1 AND p.promised_date IS NULL))
12582: l_atp_period2 := l_null_atp_period;
12583: l_atp_supply_demand2 := l_null_atp_supply_demand;
12584:
12585: -- Planning will be populating the 'weight_capacitiy_used' and
12586: -- 'volume_capacity_used' fields in msc_supplies. For now, we need
12587: -- to multiply the quantitiy by the unit volume and unit capacity
12588: -- to obtain capacity used.
12589:
12590: SELECT NVL(unit_weight,1), NVL(unit_volume,1)
12628: UNION ALL
12629: SELECT sup.new_schedule_date l_date,
12630: -1*(sup.new_order_quantity)*l_unit_weight weight,
12631: -1*(sup.new_order_quantity)*l_unit_volume volume
12632: FROM msc_supplies sup
12633: WHERE sup.plan_id = p_plan_id
12634: AND sup.organization_id = p_to_organization_id
12635: AND sup.sr_instance_id = p_dest_org_instance_id
12636: AND sup.source_organization_id is not null
13053: null col26, -- disposition_name
13054: p_scenario_id col27, -- scenairo_id
13055: null col28, -- pegging_id
13056: null col29 -- end_pegging_id
13057: FROM msc_supplies sup,
13058: msc_interorg_ship_methods s
13059: WHERE sup.plan_id = p_plan_id
13060: AND sup.organization_id = p_to_organization_id
13061: AND sup.sr_instance_id = p_dest_org_instance_id
13213: null col26, -- disposition_name
13214: p_scenario_id col27, -- scenairo_id
13215: null col28, -- pegging_id
13216: null col29 -- end_pegging_id
13217: FROM msc_supplies sup,
13218: msc_interorg_ship_methods s
13219: WHERE sup.plan_id = p_plan_id
13220: AND sup.organization_id = p_to_organization_id
13221: AND sup.sr_instance_id = p_dest_org_instance_id