[Home] [Help]
2091: D.SD_DATE SD_DATE,
2092: -1* D.SD_QTY SD_QTY
2093: FROM MSC_ATP_SUMMARY_SO D,
2094: MSC_ATP_RULES R,
2095: MSC_SYSTEM_ITEMS I,
2096: MSC_SYSTEM_ITEMS I0
2097: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2098: AND I0.ORGANIZATION_ID = p_org_id
2099: AND I0.SR_INSTANCE_ID = p_instance_id
2092: -1* D.SD_QTY SD_QTY
2093: FROM MSC_ATP_SUMMARY_SO D,
2094: MSC_ATP_RULES R,
2095: MSC_SYSTEM_ITEMS I,
2096: MSC_SYSTEM_ITEMS I0
2097: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2098: AND I0.ORGANIZATION_ID = p_org_id
2099: AND I0.SR_INSTANCE_ID = p_instance_id
2100: AND I0.PLAN_ID = p_plan_id
2121: S.SD_DATE SD_DATE,
2122: S.SD_QTY SD_QTY
2123: FROM MSC_ATP_SUMMARY_SD S,
2124: MSC_ATP_RULES R,
2125: MSC_SYSTEM_ITEMS I
2126: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2127: AND I.ORGANIZATION_ID = p_org_id
2128: AND I.SR_INSTANCE_ID = p_instance_id
2129: AND I.PLAN_ID = p_plan_id
2189: -1* D.USING_REQUIREMENT_QUANTITY SD_QTY
2190: FROM MSC_CALENDAR_DATES C,
2191: MSC_DEMANDS D,
2192: MSC_ATP_RULES R,
2193: MSC_SYSTEM_ITEMS I,
2194: MSC_SYSTEM_ITEMS I0
2195: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2196: AND I0.ORGANIZATION_ID = p_org_id
2197: AND I0.SR_INSTANCE_ID = p_instance_id
2190: FROM MSC_CALENDAR_DATES C,
2191: MSC_DEMANDS D,
2192: MSC_ATP_RULES R,
2193: MSC_SYSTEM_ITEMS I,
2194: MSC_SYSTEM_ITEMS I0
2195: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2196: AND I0.ORGANIZATION_ID = p_org_id
2197: AND I0.SR_INSTANCE_ID = p_instance_id
2198: AND I0.PLAN_ID = p_plan_id
2240: -- Bug 1756263, performance fix, use EXISTS subquery instead.
2241: --MSC_CALENDAR_DATES C,
2242: MSC_SALES_ORDERS D,
2243: MSC_ATP_RULES R,
2244: MSC_SYSTEM_ITEMS I,
2245: MSC_SYSTEM_ITEMS I0,
2246: MSC_CALENDAR_DATES C
2247: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2248: AND I0.ORGANIZATION_ID = p_org_id
2241: --MSC_CALENDAR_DATES C,
2242: MSC_SALES_ORDERS D,
2243: MSC_ATP_RULES R,
2244: MSC_SYSTEM_ITEMS I,
2245: MSC_SYSTEM_ITEMS I0,
2246: MSC_CALENDAR_DATES C
2247: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2248: AND I0.ORGANIZATION_ID = p_org_id
2249: AND I0.SR_INSTANCE_ID = p_instance_id
2308: -- NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) )SD_QTY
2309: FROM MSC_CALENDAR_DATES C,
2310: MSC_SUPPLIES S,
2311: MSC_ATP_RULES R,
2312: MSC_SYSTEM_ITEMS I,
2313: MSC_SUB_INVENTORIES MSI
2314: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2315: AND I.ORGANIZATION_ID = p_org_id
2316: AND I.SR_INSTANCE_ID = p_instance_id
2447: FROM (
2448: SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
2449: SD_DATE, SD_QTY
2450: FROM MSC_ATP_SUMMARY_SD S,
2451: MSC_SYSTEM_ITEMS I
2452: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2453: AND I.ORGANIZATION_ID = p_org_id
2454: AND I.SR_INSTANCE_ID = p_instance_id
2455: AND I.PLAN_ID = p_plan_id
2465: decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
2466: 0, OLD_ALLOCATED_QUANTITY, -- For summary enhancement
2467: -1 * AD.ALLOCATED_QUANTITY) SD_QTY
2468: FROM MSC_ALLOC_DEMANDS AD,
2469: MSC_SYSTEM_ITEMS I,
2470: MSC_PLANS P -- For summary enhancement
2471: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2472: AND I.ORGANIZATION_ID = p_org_id
2473: AND I.SR_INSTANCE_ID = p_instance_id
2486: --bug3700564 added trunc
2487: SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
2488: SA.ALLOCATED_QUANTITY SD_QTY
2489: FROM MSC_ALLOC_SUPPLIES SA,
2490: MSC_SYSTEM_ITEMS I,
2491: MSC_PLANS P -- For summary enhancement
2492: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2493: AND I.ORGANIZATION_ID = p_org_id
2494: AND I.SR_INSTANCE_ID = p_instance_id
2552: --bug3700564 added trunc
2553: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
2554: -1 * AD.ALLOCATED_QUANTITY SD_QTY
2555: FROM MSC_ALLOC_DEMANDS AD,
2556: MSC_SYSTEM_ITEMS I
2557: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2558: AND I.ORGANIZATION_ID = p_org_id
2559: AND I.SR_INSTANCE_ID = p_instance_id
2560: AND I.PLAN_ID = p_plan_id
2570: --bug3700564 added trunc
2571: SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
2572: SA.ALLOCATED_QUANTITY SD_QTY
2573: FROM MSC_ALLOC_SUPPLIES SA,
2574: MSC_SYSTEM_ITEMS I
2575: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2576: AND I.ORGANIZATION_ID = p_org_id
2577: AND I.SR_INSTANCE_ID = p_instance_id
2578: AND I.PLAN_ID = p_plan_id
2720: D.REQUEST_SHIP_DATE) --bug3263368
2721: FROM MSC_CALENDAR_DATES C,
2722: MSC_DEMANDS D,
2723: MSC_ATP_RULES R,
2724: MSC_SYSTEM_ITEMS I,
2725: MSC_SYSTEM_ITEMS I0,
2726: MSC_TRADING_PARTNERS MTP,--bug3263368
2727: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2728: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2721: FROM MSC_CALENDAR_DATES C,
2722: MSC_DEMANDS D,
2723: MSC_ATP_RULES R,
2724: MSC_SYSTEM_ITEMS I,
2725: MSC_SYSTEM_ITEMS I0,
2726: MSC_TRADING_PARTNERS MTP,--bug3263368
2727: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2728: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2729: AND I0.ORGANIZATION_ID = p_org_id
2810: D.REQUEST_SHIP_DATE) --bug3263368
2811: FROM
2812: MSC_SALES_ORDERS D,
2813: MSC_ATP_RULES R,
2814: MSC_SYSTEM_ITEMS I,
2815: MSC_SYSTEM_ITEMS I0,
2816: MSC_CALENDAR_DATES C,
2817: MSC_TRADING_PARTNERS MTP,--bug3263368
2818: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2811: FROM
2812: MSC_SALES_ORDERS D,
2813: MSC_ATP_RULES R,
2814: MSC_SYSTEM_ITEMS I,
2815: MSC_SYSTEM_ITEMS I0,
2816: MSC_CALENDAR_DATES C,
2817: MSC_TRADING_PARTNERS MTP,--bug3263368
2818: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2819: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2938: l_null_date --bug3814584
2939: FROM MSC_CALENDAR_DATES C,
2940: MSC_SUPPLIES S,
2941: MSC_ATP_RULES R,
2942: MSC_SYSTEM_ITEMS I,
2943: MSC_SUB_INVENTORIES MSI
2944: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2945: AND I.ORGANIZATION_ID = p_org_id
2946: AND I.SR_INSTANCE_ID = p_instance_id
3173: AD.DEMAND_CLASS, --bug3263368
3174: AD.REQUEST_DATE, --bug3263368
3175: I2.Item_Name --bug3579625
3176:
3177: FROM MSC_SYSTEM_ITEMS I,
3178: MSC_SYSTEM_ITEMS I2, --bug3579625
3179: MSC_ALLOC_DEMANDS AD,
3180: MSC_TRADING_PARTNERS MTP,--bug3263368
3181: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
3174: AD.REQUEST_DATE, --bug3263368
3175: I2.Item_Name --bug3579625
3176:
3177: FROM MSC_SYSTEM_ITEMS I,
3178: MSC_SYSTEM_ITEMS I2, --bug3579625
3179: MSC_ALLOC_DEMANDS AD,
3180: MSC_TRADING_PARTNERS MTP,--bug3263368
3181: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
3182: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
3186: AND AD.PLAN_ID = I.PLAN_ID
3187: AND AD.SR_INSTANCE_ID = I.SR_INSTANCE_ID
3188: AND AD.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
3189: AND AD.ORGANIZATION_ID = I.ORGANIZATION_ID
3190: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I2)
3191: AND AD.PLAN_ID = I2.PLAN_ID
3192: AND AD.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
3193: AND AD.ORIGINAL_ITEM_ID = I2.INVENTORY_ITEM_ID
3194: AND AD.ORGANIZATION_ID = I2.ORGANIZATION_ID
3248: l_null_char, --bug3814584
3249: l_null_date, --bug3814584
3250: I2.Item_Name --bug3579625
3251: FROM MSC_ALLOC_SUPPLIES SA,
3252: MSC_SYSTEM_ITEMS I,
3253: MSC_SYSTEM_ITEMS I2 --bug3579625
3254: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
3255: AND I.ORGANIZATION_ID = p_org_id
3256: AND I.SR_INSTANCE_ID = p_instance_id
3249: l_null_date, --bug3814584
3250: I2.Item_Name --bug3579625
3251: FROM MSC_ALLOC_SUPPLIES SA,
3252: MSC_SYSTEM_ITEMS I,
3253: MSC_SYSTEM_ITEMS I2 --bug3579625
3254: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
3255: AND I.ORGANIZATION_ID = p_org_id
3256: AND I.SR_INSTANCE_ID = p_instance_id
3257: AND I.PLAN_ID = p_plan_id
3258: AND SA.PLAN_ID = I.PLAN_ID
3259: AND SA.SR_INSTANCE_ID = I.SR_INSTANCE_ID
3260: AND SA.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
3261: AND SA.ORGANIZATION_ID = I.ORGANIZATION_ID
3262: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I2)
3263: AND SA.PLAN_ID = I2.PLAN_ID
3264: AND SA.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
3265: AND SA.ORIGINAL_ITEM_ID = I2.INVENTORY_ITEM_ID
3266: AND SA.ORGANIZATION_ID = I2.ORGANIZATION_ID
5127: I.Item_Name -- bug3579625
5128: FROM MSC_ALLOC_DEMANDS AD,
5129: MSC_TRADING_PARTNERS MTP,--bug3263368
5130: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
5131: MSC_SYSTEM_ITEMS I --bug3579625
5132: WHERE AD.PLAN_ID = p_plan_id
5133: AND AD.SR_INSTANCE_ID = p_instance_id
5134: AND AD.INVENTORY_ITEM_ID in (p_member_id,p_family_id)
5135: AND AD.ORGANIZATION_ID = p_org_id
5138: --bug3700564 added trunc
5139: AND TRUNC(AD.DEMAND_DATE) < NVL(p_itf, TRUNC(AD.DEMAND_DATE) + 1)
5140: AND AD.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
5141: AND AD.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
5142: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I)
5143: AND AD.PLAN_ID = I.PLAN_ID
5144: AND AD.SR_INSTANCE_ID = I.SR_INSTANCE_ID
5145: AND AD.ORIGINAL_ITEM_ID = I.INVENTORY_ITEM_ID
5146: AND AD.ORGANIZATION_ID = I.ORGANIZATION_ID
5278: SA.ALLOCATED_QUANTITY,
5279: SA.PARENT_TRANSACTION_ID,
5280: SA.ORDER_TYPE
5281: FROM MSC_ALLOC_SUPPLIES SA,
5282: MSC_SYSTEM_ITEMS I
5283: WHERE SA.PLAN_ID = p_plan_id
5284: AND SA.SR_INSTANCE_ID = p_instance_id
5285: AND SA.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5286: AND SA.ORGANIZATION_ID = p_org_id
5290: 28, TRUNC(SYSDATE),
5291: TRUNC(SA.SUPPLY_DATE))
5292: --bug3700564 added trunc
5293: AND TRUNC(SA.SUPPLY_DATE) < NVL(p_itf, TRUNC(SA.SUPPLY_DATE) + 1)
5294: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I)
5295: AND SA.PLAN_ID = I.PLAN_ID
5296: AND SA.SR_INSTANCE_ID = I.SR_INSTANCE_ID
5297: AND SA.ORIGINAL_ITEM_ID = I.INVENTORY_ITEM_ID
5298: AND SA.ORGANIZATION_ID = I.ORGANIZATION_ID) CSA,
5635: I.Item_Name --bug3579625
5636: FROM MSC_ALLOC_DEMANDS AD,
5637: MSC_TRADING_PARTNERS MTP,--bug3263368
5638: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
5639: MSC_SYSTEM_ITEMS I --bug3579625
5640: WHERE AD.PLAN_ID = p_plan_id
5641: AND AD.SR_INSTANCE_ID = p_instance_id
5642: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5643: AND AD.ORIGINATION_TYPE <> 52
5646: AND AD.DEMAND_CLASS = NVL(p_demand_class, AD.DEMAND_CLASS)
5647: AND TRUNC(AD.DEMAND_DATE) < p_itf
5648: AND AD.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
5649: AND AD.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
5650: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I)
5651: AND AD.PLAN_ID = I.PLAN_ID
5652: AND AD.SR_INSTANCE_ID = I.SR_INSTANCE_ID
5653: AND AD.ORIGINAL_ITEM_ID = I.INVENTORY_ITEM_ID
5654: AND AD.ORGANIZATION_ID = I.ORGANIZATION_ID
5704: l_null_date, --bug3814584
5705: I.Item_Name --bug3579625
5706:
5707: FROM MSC_ALLOC_SUPPLIES SA,
5708: MSC_SYSTEM_ITEMS I, --bug3579625
5709: MSC_TRADING_PARTNERS MTP,--bug3684383
5710: MSC_TRADING_PARTNER_SITES MTPS --bug3684383
5711:
5712: WHERE SA.PLAN_ID = p_plan_id
5719: AND SA.ORGANIZATION_ID = p_org_id
5720: AND SA.ALLOCATED_QUANTITY <> 0
5721: AND SA.DEMAND_CLASS = NVL(p_demand_class, SA.DEMAND_CLASS )
5722: AND TRUNC(SA.SUPPLY_DATE) < p_itf
5723: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I)
5724: AND SA.PLAN_ID = I.PLAN_ID
5725: AND SA.SR_INSTANCE_ID = I.SR_INSTANCE_ID
5726: AND SA.ORIGINAL_ITEM_ID = I.INVENTORY_ITEM_ID
5727: AND SA.ORGANIZATION_ID = I.ORGANIZATION_ID
5966: p_family_id IN NUMBER,
5967: p_sr_member_id IN NUMBER,
5968: p_sr_family_id IN NUMBER,
5969: p_org_id IN NUMBER,
5970: --bug3671294 now we donot need this as we will join with msc_system_items
5971: --p_inv_item_name IN VARCHAR2,
5972: p_org_code IN VARCHAR2,
5973: p_instance_id IN NUMBER,
5974: p_plan_id IN NUMBER,
6068: MSC_ALLOC_DEMANDS AD,
6069: MSC_ALLOC_TEMP TEMP,
6070: MSC_TRADING_PARTNERS MTP,--bug3263368
6071: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
6072: MSC_SYSTEM_ITEMS msi --bug3671294
6073: WHERE
6074: AD.PLAN_ID = p_plan_id
6075: AND AD.SR_INSTANCE_ID = p_instance_id
6076: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
6131: l_null_date col30 --bug3684383 -- Bug 3875786 - local variable used for NULL
6132: FROM
6133: MSC_ALLOC_SUPPLIES SA,
6134: MSC_ALLOC_TEMP TEMP,
6135: MSC_SYSTEM_ITEMS msi, --bug3671294
6136: MSC_TRADING_PARTNER_SITES MTPS, --bug3684383
6137: MSC_TRADING_PARTNERS MTP --bug3684383
6138: WHERE
6139: SA.PLAN_ID = p_plan_id
6628: MSC_ALLOC_DEMANDS AD,
6629: MSC_ALLOC_TEMP TEMP,
6630: MSC_TRADING_PARTNERS MTP,--bug3263368
6631: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
6632: MSC_SYSTEM_ITEMS msi --bug3671294
6633: WHERE
6634: AD.PLAN_ID = p_plan_id
6635: AND AD.SR_INSTANCE_ID = p_instance_id
6636: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
6736:
6737: FROM
6738: MSC_ALLOC_SUPPLIES SA,
6739: MSC_ALLOC_TEMP TEMP,
6740: MSC_SYSTEM_ITEMS msi --bug3671294
6741: WHERE
6742: SA.PLAN_ID = p_plan_id
6743: AND SA.SR_INSTANCE_ID = p_instance_id
6744: AND SA.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
7139: MSC_ALLOC_DEMANDS AD,
7140: MSC_ALLOC_HIERARCHY_TEMP TEMP,
7141: MSC_TRADING_PARTNERS MTP,--bug3263368
7142: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
7143: MSC_SYSTEM_ITEMS msi --bug3671294
7144: WHERE
7145: AD.PLAN_ID = p_plan_id
7146: AND AD.SR_INSTANCE_ID = p_instance_id
7147: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
7244: l_null_date col36 --bug3263368 ORIG_REQUEST_DATE -- Bug 3875786 - local variable used for NULL
7245: FROM
7246: MSC_ALLOC_SUPPLIES SA,
7247: MSC_ALLOC_HIERARCHY_TEMP TEMP,
7248: MSC_SYSTEM_ITEMS msi --bug3671294
7249: WHERE
7250: SA.PLAN_ID = p_plan_id
7251: AND SA.SR_INSTANCE_ID = p_instance_id
7252: AND SA.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
8492: i2.sr_inventory_item_id),
8493: i1.sr_inventory_item_id
8494: )
8495: INTO l_pf_atp_item_id
8496: FROM msc_system_items i2,
8497: msc_system_items i1
8498: WHERE i1.sr_inventory_item_id = p_inventory_item_id
8499: AND i1.organization_id = p_organization_id
8500: AND i1.plan_id = p_plan_id
8493: i1.sr_inventory_item_id
8494: )
8495: INTO l_pf_atp_item_id
8496: FROM msc_system_items i2,
8497: msc_system_items i1
8498: WHERE i1.sr_inventory_item_id = p_inventory_item_id
8499: AND i1.organization_id = p_organization_id
8500: AND i1.plan_id = p_plan_id
8501: AND i1.sr_instance_id = p_instance_id
8543: END IF;
8544:
8545: SELECT i.aggregate_time_fence_date
8546: INTO l_atf_date
8547: FROM msc_system_items i
8548: WHERE i.plan_id = p_plan_id
8549: AND i.sr_instance_id = p_instance_id
8550: AND i.organization_id = p_organization_id
8551: AND i.sr_inventory_item_id = p_inventory_item_id;
8589: 3, i2.planning_time_fence_days,
8590: 4, r.aggregate_time_fence
8591: )
8592: INTO l_atf_days
8593: FROM msc_system_items i2,
8594: msc_system_items i1,
8595: msc_atp_rules r,
8596: msc_trading_partners tp
8597: WHERE i1.inventory_item_id = p_inventory_item_id
8590: 4, r.aggregate_time_fence
8591: )
8592: INTO l_atf_days
8593: FROM msc_system_items i2,
8594: msc_system_items i1,
8595: msc_atp_rules r,
8596: msc_trading_partners tp
8597: WHERE i1.inventory_item_id = p_inventory_item_id
8598: AND i1.organization_id = p_organization_id
8674: INTO p_family_id,
8675: p_sr_family_id,
8676: p_atf_date,
8677: p_family_name --bug3700564
8678: FROM msc_system_items i2,
8679: msc_system_items i1
8680: WHERE i1.inventory_item_id = p_inventory_item_id
8681: AND i1.organization_id = p_organization_id
8682: AND i1.plan_id = p_plan_id
8675: p_sr_family_id,
8676: p_atf_date,
8677: p_family_name --bug3700564
8678: FROM msc_system_items i2,
8679: msc_system_items i1
8680: WHERE i1.inventory_item_id = p_inventory_item_id
8681: AND i1.organization_id = p_organization_id
8682: AND i1.plan_id = p_plan_id
8683: AND i1.sr_instance_id = p_instance_id
8705: INTO p_family_id,
8706: p_sr_family_id,
8707: p_atf_date,
8708: p_family_name
8709: FROM msc_system_items i1
8710: WHERE i1.inventory_item_id = p_inventory_item_id
8711: AND i1.organization_id = p_organization_id
8712: AND i1.plan_id = p_plan_id
8713: AND i1.sr_instance_id = p_instance_id;
8890: SELECT c2.calendar_date,i2.organization_id,i2.sr_instance_id,i2.inventory_item_id
8891: BULK COLLECT INTO l_atf_date,l_organization_id,l_sr_instance_id,l_inventory_item_id
8892: FROM msc_plans mp,
8893: msc_plan_organizations po,
8894: msc_system_items i2,
8895: msc_trading_partners tp,
8896: msc_atp_rules r,
8897: msc_calendar_dates c1,
8898: msc_calendar_dates c2
8929:
8930: IF l_inventory_item_id IS NOT NULL AND l_inventory_item_id.COUNT > 0 THEN
8931:
8932: FORALL j IN l_inventory_item_id.first.. l_inventory_item_id.last
8933: UPDATE msc_system_items i
8934: SET aggregate_time_fence_date = l_atf_date(j)
8935: WHERE i.plan_id = p_plan_id
8936: AND i.ORGANIZATION_ID = l_organization_id(j)
8937: AND i.SR_INSTANCE_ID = l_sr_instance_id(j)
8937: AND i.SR_INSTANCE_ID = l_sr_instance_id(j)
8938: AND i.inventory_item_id = l_inventory_item_id(j);
8939:
8940: FORALL k IN l_inventory_item_id.first.. l_inventory_item_id.last
8941: UPDATE msc_system_items i
8942: SET aggregate_time_fence_date = l_atf_date(k)
8943: WHERE i.plan_id = p_plan_id
8944: AND i.ORGANIZATION_ID = l_organization_id(k)
8945: AND i.SR_INSTANCE_ID = l_sr_instance_id(k)
8952:
8953: ----bug3663487 code commented for Performance fix
8954: /*
8955: -- populate ATF date for PF items
8956: UPDATE msc_system_items i
8957: SET aggregate_time_fence_date =
8958: (SELECT c2.calendar_date
8959: FROM msc_calendar_dates c2,
8960: msc_calendar_dates c1,
8960: msc_calendar_dates c1,
8961: msc_atp_rules r,
8962: msc_trading_partners tp,
8963: msc_plans mp,
8964: msc_system_items i2
8965: WHERE i2.inventory_item_id = i.inventory_item_id
8966: AND i2.organization_id = i.organization_id
8967: AND i2.sr_instance_id = i.sr_instance_id
8968: AND i2.plan_id = -1
8991: AND i.bom_item_type = 5
8992: AND i.atp_flag = 'Y';
8993:
8994: -- populate ATF date for atpable member items
8995: UPDATE msc_system_items i
8996: SET aggregate_time_fence_date =
8997: (SELECT i2.aggregate_time_fence_date
8998: FROM msc_system_items i2
8999: WHERE i2.inventory_item_id = i.product_family_id
8994: -- populate ATF date for atpable member items
8995: UPDATE msc_system_items i
8996: SET aggregate_time_fence_date =
8997: (SELECT i2.aggregate_time_fence_date
8998: FROM msc_system_items i2
8999: WHERE i2.inventory_item_id = i.product_family_id
9000: AND i2.sr_instance_id = i.sr_instance_id
9001: AND i2.organization_id = i.organization_id
9002: AND i2.plan_id = i.plan_id
9009: i.product_family_id)
9010: AND i.bom_item_type <> 5
9011: AND i.atp_flag = 'Y'
9012: AND EXISTS (SELECT 1
9013: FROM msc_system_items i2
9014: WHERE i2.inventory_item_id = i.product_family_id
9015: AND i2.sr_instance_id = i.sr_instance_id
9016: AND i2.organization_id = i.organization_id
9017: AND i2.plan_id = i.plan_id
9096: sum(peg1.allocated_quantity)
9097: from
9098: (select distinct d.disposition_id transaction_id
9099: from msc_demands d,
9100: msc_system_items msi
9101: where msi.plan_id = p_plan_id
9102: and msi.bom_item_type <> 5
9103: and NVL(msi.product_family_id, -23453) <> -23453 -- Bug 3629191
9104: --and msi.product_family_id is not null
10284: NULL, msi.inventory_item_id,
10285: msi.product_family_id) product_family_id ,
10286: decode(d.order_date_type_code,2,d.request_date,
10287: d.request_ship_date)request_date --bug3263368
10288: FROM msc_system_items msi,
10289: msc_demands d,
10290: msc_full_pegging peg,
10291: ' || p_alloc_temp_table || ' mat,
10292: msc_supplies s
10442: d.request_ship_date)request_date --bug3263368
10443: FROM msc_full_pegging peg1,
10444: msc_demands d,
10445: msc_supplies s,
10446: msc_system_items msi
10447: WHERE d.demand_id = peg1.demand_id
10448: AND d.plan_id = peg1.plan_id
10449: AND d.sr_instance_id = peg1.sr_instance_id
10450: AND d.organization_id= peg1.organization_id --bug3453289
10657: s.ship_to_site_id,
10658: decode(msi.aggregate_time_fence_date,
10659: NULL, msi.inventory_item_id,
10660: msi.product_family_id) product_family_id
10661: FROM msc_system_items msi,
10662: msc_supplies s,
10663: msc_full_pegging peg,
10664: ' || p_alloc_temp_table || ' mat
10665: WHERE msi.plan_id = :p_plan_id
10746: :l_sysdate,
10747: :l_user_id,
10748: :l_sysdate
10749: from msc_supplies s,
10750: msc_system_items i
10751: where i.aggregate_time_fence_date is not null
10752: and i.bom_item_type <> 5
10753: and i.plan_id = :p_plan_id
10754: and i.atp_flag = :l_yes
10963: FROM msc_full_pegging peg2,
10964: msc_full_pegging peg1,
10965: msc_demands d,
10966: msc_supplies s,
10967: msc_system_items msi
10968: WHERE peg1.plan_id = :p_plan_id
10969: AND peg2.plan_id = peg1.plan_id
10970: AND peg2.pegging_id = peg1.end_pegging_id
10971: AND peg2.demand_id IN (-1, -2)
11130: FROM msc_full_pegging peg2,
11131: msc_full_pegging peg1,
11132: msc_demands d,
11133: msc_supplies s,
11134: msc_system_items msi
11135: WHERE peg1.plan_id = :p_plan_id
11136: AND peg2.plan_id = peg1.plan_id
11137: AND peg2.pegging_id = peg1.end_pegging_id
11138: AND peg2.demand_id IN (-1, -2)
11317: :l_sysdate
11318: FROM msc_full_pegging peg1,
11319: msc_demands d,
11320: msc_supplies s,
11321: msc_system_items msi
11322: WHERE d.demand_id = peg1.demand_id
11323: AND d.plan_id = peg1.plan_id
11324: AND d.sr_instance_id = peg1.sr_instance_id
11325: AND d.origination_type NOT IN (5,7,8,9,11,15,22,28,29,31)
11571: msc_full_pegging peg2,
11572: msc_full_pegging peg1 ,
11573: msc_demands d1,
11574: msc_supplies s,
11575: msc_system_items msi
11576: WHERE peg2.plan_id = :p_plan_id
11577: AND peg2.plan_id = peg1.plan_id
11578: AND peg2.end_pegging_id = peg1.pegging_id
11579: AND peg2.sr_instance_id = peg1.sr_instance_id
11759: msi.product_family_id) product_family_id
11760: FROM msc_full_pegging peg2,
11761: msc_full_pegging peg1,
11762: msc_supplies s,
11763: msc_system_items msi
11764: WHERE peg1.plan_id = :p_plan_id
11765: AND peg2.plan_id = peg1.plan_id
11766: AND peg2.pegging_id = peg1.end_pegging_id
11767: AND peg2.demand_id IN (-1, -2)
11859: :l_sysdate last_update_date
11860: FROM msc_full_pegging peg2,
11861: msc_full_pegging peg1,
11862: msc_supplies s,
11863: msc_system_items msi
11864: WHERE peg1.plan_id = :p_plan_id
11865: AND peg2.plan_id = peg1.plan_id
11866: AND peg2.pegging_id = peg1.end_pegging_id
11867: AND peg2.demand_id IN (-1, -2)
11942: :l_sysdate,
11943: :l_user_id,
11944: :l_sysdate
11945: from msc_supplies s,
11946: msc_system_items i
11947: where i.aggregate_time_fence_date is not null
11948: and i.bom_item_type <> 5
11949: and i.plan_id = :p_plan_id
11950: and s.inventory_item_id = i.inventory_item_id
12071: FROM msc_supplies s,
12072: msc_full_pegging peg2,
12073: msc_full_pegging peg1,
12074: msc_demands d,
12075: msc_system_items msi
12076: WHERE peg2.plan_id = :p_plan_id
12077: AND peg2.plan_id = peg1.plan_id
12078: AND peg2.end_pegging_id = peg1.pegging_id
12079: AND d.plan_id = peg1.plan_id
12159: WHERE AD2.plan_id = p_plan_id
12160: GROUP BY AD2.parent_demand_id, AD2.demand_class
12161: )
12162: AND EXISTS (SELECT 1
12163: FROM MSC_SYSTEM_ITEMS I
12164: WHERE I.inventory_item_id = AD.inventory_item_id
12165: AND I.organization_id = AD.organization_id
12166: AND I.sr_instance_id = AD.sr_instance_id
12167: AND I.plan_id = AD.plan_id
12179: AND AD.demand_class = AD2.demand_class
12180: GROUP BY AD2.parent_demand_id, AD2.demand_class
12181: HAVING count(*) = 1)
12182: AND EXISTS (SELECT 1
12183: FROM MSC_SYSTEM_ITEMS I
12184: WHERE I.inventory_item_id = AD.inventory_item_id
12185: AND I.organization_id = AD.organization_id
12186: AND I.sr_instance_id = AD.sr_instance_id
12187: AND I.plan_id = AD.plan_id