[Home] [Help]
2098: D.SD_DATE SD_DATE,
2099: -1* D.SD_QTY SD_QTY
2100: FROM MSC_ATP_SUMMARY_SO D,
2101: MSC_ATP_RULES R,
2102: MSC_SYSTEM_ITEMS I,
2103: MSC_SYSTEM_ITEMS I0
2104: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2105: AND I0.ORGANIZATION_ID = p_org_id
2106: AND I0.SR_INSTANCE_ID = p_instance_id
2099: -1* D.SD_QTY SD_QTY
2100: FROM MSC_ATP_SUMMARY_SO D,
2101: MSC_ATP_RULES R,
2102: MSC_SYSTEM_ITEMS I,
2103: MSC_SYSTEM_ITEMS I0
2104: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2105: AND I0.ORGANIZATION_ID = p_org_id
2106: AND I0.SR_INSTANCE_ID = p_instance_id
2107: AND I0.PLAN_ID = p_plan_id
2128: S.SD_DATE SD_DATE,
2129: S.SD_QTY SD_QTY
2130: FROM MSC_ATP_SUMMARY_SD S,
2131: MSC_ATP_RULES R,
2132: MSC_SYSTEM_ITEMS I
2133: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2134: AND I.ORGANIZATION_ID = p_org_id
2135: AND I.SR_INSTANCE_ID = p_instance_id
2136: AND I.PLAN_ID = p_plan_id
2196: -1* D.USING_REQUIREMENT_QUANTITY SD_QTY
2197: FROM MSC_CALENDAR_DATES C,
2198: MSC_DEMANDS D,
2199: MSC_ATP_RULES R,
2200: MSC_SYSTEM_ITEMS I,
2201: MSC_SYSTEM_ITEMS I0
2202: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2203: AND I0.ORGANIZATION_ID = p_org_id
2204: AND I0.SR_INSTANCE_ID = p_instance_id
2197: FROM MSC_CALENDAR_DATES C,
2198: MSC_DEMANDS D,
2199: MSC_ATP_RULES R,
2200: MSC_SYSTEM_ITEMS I,
2201: MSC_SYSTEM_ITEMS I0
2202: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2203: AND I0.ORGANIZATION_ID = p_org_id
2204: AND I0.SR_INSTANCE_ID = p_instance_id
2205: AND I0.PLAN_ID = p_plan_id
2247: -- Bug 1756263, performance fix, use EXISTS subquery instead.
2248: --MSC_CALENDAR_DATES C,
2249: MSC_SALES_ORDERS D,
2250: MSC_ATP_RULES R,
2251: MSC_SYSTEM_ITEMS I,
2252: MSC_SYSTEM_ITEMS I0,
2253: MSC_CALENDAR_DATES C
2254: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2255: AND I0.ORGANIZATION_ID = p_org_id
2248: --MSC_CALENDAR_DATES C,
2249: MSC_SALES_ORDERS D,
2250: MSC_ATP_RULES R,
2251: MSC_SYSTEM_ITEMS I,
2252: MSC_SYSTEM_ITEMS I0,
2253: MSC_CALENDAR_DATES C
2254: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2255: AND I0.ORGANIZATION_ID = p_org_id
2256: AND I0.SR_INSTANCE_ID = p_instance_id
2315: -- NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) )SD_QTY
2316: FROM MSC_CALENDAR_DATES C,
2317: MSC_SUPPLIES S,
2318: MSC_ATP_RULES R,
2319: MSC_SYSTEM_ITEMS I,
2320: MSC_SYSTEM_ITEMS I0, --Bug 13072988
2321: MSC_SUB_INVENTORIES MSI
2322: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id -- Bug 13072988, aggregate the supply of member-items
2323: AND I0.ORGANIZATION_ID = p_org_id -- as we are aggregating the demands also.
2316: FROM MSC_CALENDAR_DATES C,
2317: MSC_SUPPLIES S,
2318: MSC_ATP_RULES R,
2319: MSC_SYSTEM_ITEMS I,
2320: MSC_SYSTEM_ITEMS I0, --Bug 13072988
2321: MSC_SUB_INVENTORIES MSI
2322: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id -- Bug 13072988, aggregate the supply of member-items
2323: AND I0.ORGANIZATION_ID = p_org_id -- as we are aggregating the demands also.
2324: AND I0.SR_INSTANCE_ID = p_instance_id
2459: FROM (
2460: SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
2461: SD_DATE, SD_QTY
2462: FROM MSC_ATP_SUMMARY_SD S,
2463: MSC_SYSTEM_ITEMS I
2464: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2465: AND I.ORGANIZATION_ID = p_org_id
2466: AND I.SR_INSTANCE_ID = p_instance_id
2467: AND I.PLAN_ID = p_plan_id
2477: decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
2478: 0, OLD_ALLOCATED_QUANTITY, -- For summary enhancement
2479: -1 * AD.ALLOCATED_QUANTITY) SD_QTY
2480: FROM MSC_ALLOC_DEMANDS AD,
2481: MSC_SYSTEM_ITEMS I,
2482: MSC_PLANS P -- For summary enhancement
2483: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2484: AND I.ORGANIZATION_ID = p_org_id
2485: AND I.SR_INSTANCE_ID = p_instance_id
2498: --bug3700564 added trunc
2499: SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
2500: SA.ALLOCATED_QUANTITY SD_QTY
2501: FROM MSC_ALLOC_SUPPLIES SA,
2502: MSC_SYSTEM_ITEMS I,
2503: MSC_PLANS P -- For summary enhancement
2504: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2505: AND I.ORGANIZATION_ID = p_org_id
2506: AND I.SR_INSTANCE_ID = p_instance_id
2564: --bug3700564 added trunc
2565: SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
2566: -1 * AD.ALLOCATED_QUANTITY SD_QTY
2567: FROM MSC_ALLOC_DEMANDS AD,
2568: MSC_SYSTEM_ITEMS I
2569: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2570: AND I.ORGANIZATION_ID = p_org_id
2571: AND I.SR_INSTANCE_ID = p_instance_id
2572: AND I.PLAN_ID = p_plan_id
2582: --bug3700564 added trunc
2583: SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
2584: SA.ALLOCATED_QUANTITY SD_QTY
2585: FROM MSC_ALLOC_SUPPLIES SA,
2586: MSC_SYSTEM_ITEMS I
2587: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
2588: AND I.ORGANIZATION_ID = p_org_id
2589: AND I.SR_INSTANCE_ID = p_instance_id
2590: AND I.PLAN_ID = p_plan_id
2732: D.REQUEST_SHIP_DATE) --bug3263368
2733: FROM MSC_CALENDAR_DATES C,
2734: MSC_DEMANDS D,
2735: MSC_ATP_RULES R,
2736: MSC_SYSTEM_ITEMS I,
2737: MSC_SYSTEM_ITEMS I0,
2738: MSC_TRADING_PARTNERS MTP,--bug3263368
2739: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2740: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2733: FROM MSC_CALENDAR_DATES C,
2734: MSC_DEMANDS D,
2735: MSC_ATP_RULES R,
2736: MSC_SYSTEM_ITEMS I,
2737: MSC_SYSTEM_ITEMS I0,
2738: MSC_TRADING_PARTNERS MTP,--bug3263368
2739: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2740: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2741: AND I0.ORGANIZATION_ID = p_org_id
2822: D.REQUEST_SHIP_DATE) --bug3263368
2823: FROM
2824: MSC_SALES_ORDERS D,
2825: MSC_ATP_RULES R,
2826: MSC_SYSTEM_ITEMS I,
2827: MSC_SYSTEM_ITEMS I0,
2828: MSC_CALENDAR_DATES C,
2829: MSC_TRADING_PARTNERS MTP,--bug3263368
2830: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2823: FROM
2824: MSC_SALES_ORDERS D,
2825: MSC_ATP_RULES R,
2826: MSC_SYSTEM_ITEMS I,
2827: MSC_SYSTEM_ITEMS I0,
2828: MSC_CALENDAR_DATES C,
2829: MSC_TRADING_PARTNERS MTP,--bug3263368
2830: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2831: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id
2950: l_null_date --bug3814584
2951: FROM MSC_CALENDAR_DATES C,
2952: MSC_SUPPLIES S,
2953: MSC_ATP_RULES R,
2954: MSC_SYSTEM_ITEMS I,
2955: MSC_SYSTEM_ITEMS I0, --Bug 13072988
2956: MSC_SUB_INVENTORIES MSI
2957: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id -- Bug 13072988, aggregate the supply of member-items
2958: AND I0.ORGANIZATION_ID = p_org_id -- as we are aggregating the demands also.
2951: FROM MSC_CALENDAR_DATES C,
2952: MSC_SUPPLIES S,
2953: MSC_ATP_RULES R,
2954: MSC_SYSTEM_ITEMS I,
2955: MSC_SYSTEM_ITEMS I0, --Bug 13072988
2956: MSC_SUB_INVENTORIES MSI
2957: WHERE I0.SR_INVENTORY_ITEM_ID = p_item_id -- Bug 13072988, aggregate the supply of member-items
2958: AND I0.ORGANIZATION_ID = p_org_id -- as we are aggregating the demands also.
2959: AND I0.SR_INSTANCE_ID = p_instance_id
3190: AD.DEMAND_CLASS, --bug3263368
3191: AD.REQUEST_DATE, --bug3263368
3192: I2.Item_Name --bug3579625
3193:
3194: FROM MSC_SYSTEM_ITEMS I,
3195: MSC_SYSTEM_ITEMS I2, --bug3579625
3196: MSC_ALLOC_DEMANDS AD,
3197: MSC_TRADING_PARTNERS MTP,--bug3263368
3198: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
3191: AD.REQUEST_DATE, --bug3263368
3192: I2.Item_Name --bug3579625
3193:
3194: FROM MSC_SYSTEM_ITEMS I,
3195: MSC_SYSTEM_ITEMS I2, --bug3579625
3196: MSC_ALLOC_DEMANDS AD,
3197: MSC_TRADING_PARTNERS MTP,--bug3263368
3198: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
3199: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
3203: AND AD.PLAN_ID = I.PLAN_ID
3204: AND AD.SR_INSTANCE_ID = I.SR_INSTANCE_ID
3205: AND AD.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
3206: AND AD.ORGANIZATION_ID = I.ORGANIZATION_ID
3207: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I2)
3208: AND AD.PLAN_ID = I2.PLAN_ID
3209: AND AD.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
3210: AND AD.ORIGINAL_ITEM_ID = I2.INVENTORY_ITEM_ID
3211: AND AD.ORGANIZATION_ID = I2.ORGANIZATION_ID
3265: l_null_char, --bug3814584
3266: l_null_date, --bug3814584
3267: I2.Item_Name --bug3579625
3268: FROM MSC_ALLOC_SUPPLIES SA,
3269: MSC_SYSTEM_ITEMS I,
3270: MSC_SYSTEM_ITEMS I2 --bug3579625
3271: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
3272: AND I.ORGANIZATION_ID = p_org_id
3273: AND I.SR_INSTANCE_ID = p_instance_id
3266: l_null_date, --bug3814584
3267: I2.Item_Name --bug3579625
3268: FROM MSC_ALLOC_SUPPLIES SA,
3269: MSC_SYSTEM_ITEMS I,
3270: MSC_SYSTEM_ITEMS I2 --bug3579625
3271: WHERE I.SR_INVENTORY_ITEM_ID in (p_sr_member_id, p_sr_family_id)
3272: AND I.ORGANIZATION_ID = p_org_id
3273: AND I.SR_INSTANCE_ID = p_instance_id
3274: AND I.PLAN_ID = p_plan_id
3275: AND SA.PLAN_ID = I.PLAN_ID
3276: AND SA.SR_INSTANCE_ID = I.SR_INSTANCE_ID
3277: AND SA.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
3278: AND SA.ORGANIZATION_ID = I.ORGANIZATION_ID
3279: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I2)
3280: AND SA.PLAN_ID = I2.PLAN_ID
3281: AND SA.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
3282: AND SA.ORIGINAL_ITEM_ID = I2.INVENTORY_ITEM_ID
3283: AND SA.ORGANIZATION_ID = I2.ORGANIZATION_ID
5150: I.Item_Name -- bug3579625
5151: FROM MSC_ALLOC_DEMANDS AD,
5152: MSC_TRADING_PARTNERS MTP,--bug3263368
5153: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
5154: MSC_SYSTEM_ITEMS I --bug3579625
5155: WHERE AD.PLAN_ID = p_plan_id
5156: AND AD.SR_INSTANCE_ID = p_instance_id
5157: AND AD.INVENTORY_ITEM_ID in (p_member_id,p_family_id)
5158: AND AD.ORGANIZATION_ID = p_org_id
5161: --bug3700564 added trunc
5162: AND TRUNC(AD.DEMAND_DATE) < NVL(p_itf, TRUNC(AD.DEMAND_DATE) + 1)
5163: AND AD.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
5164: AND AD.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
5165: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I)
5166: AND AD.PLAN_ID = I.PLAN_ID
5167: AND AD.SR_INSTANCE_ID = I.SR_INSTANCE_ID
5168: AND AD.ORIGINAL_ITEM_ID = I.INVENTORY_ITEM_ID
5169: AND AD.ORGANIZATION_ID = I.ORGANIZATION_ID
5301: SA.ALLOCATED_QUANTITY,
5302: SA.PARENT_TRANSACTION_ID,
5303: SA.ORDER_TYPE
5304: FROM MSC_ALLOC_SUPPLIES SA,
5305: MSC_SYSTEM_ITEMS I
5306: WHERE SA.PLAN_ID = p_plan_id
5307: AND SA.SR_INSTANCE_ID = p_instance_id
5308: AND SA.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5309: AND SA.ORGANIZATION_ID = p_org_id
5313: 28, TRUNC(SYSDATE),
5314: TRUNC(SA.SUPPLY_DATE))
5315: --bug3700564 added trunc
5316: AND TRUNC(SA.SUPPLY_DATE) < NVL(p_itf, TRUNC(SA.SUPPLY_DATE) + 1)
5317: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I)
5318: AND SA.PLAN_ID = I.PLAN_ID
5319: AND SA.SR_INSTANCE_ID = I.SR_INSTANCE_ID
5320: AND SA.ORIGINAL_ITEM_ID = I.INVENTORY_ITEM_ID
5321: AND SA.ORGANIZATION_ID = I.ORGANIZATION_ID) CSA,
5661: I.Item_Name --bug3579625
5662: FROM MSC_ALLOC_DEMANDS AD,
5663: MSC_TRADING_PARTNERS MTP,--bug3263368
5664: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
5665: MSC_SYSTEM_ITEMS I --bug3579625
5666: WHERE AD.PLAN_ID = p_plan_id
5667: AND AD.SR_INSTANCE_ID = p_instance_id
5668: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
5669: AND AD.ORIGINATION_TYPE <> 52
5672: AND AD.DEMAND_CLASS = NVL(p_demand_class, AD.DEMAND_CLASS)
5673: AND TRUNC(AD.DEMAND_DATE) < p_itf
5674: AND AD.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
5675: AND AD.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
5676: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I)
5677: AND AD.PLAN_ID = I.PLAN_ID
5678: AND AD.SR_INSTANCE_ID = I.SR_INSTANCE_ID
5679: AND AD.ORIGINAL_ITEM_ID = I.INVENTORY_ITEM_ID
5680: AND AD.ORGANIZATION_ID = I.ORGANIZATION_ID
5730: l_null_date, --bug3814584
5731: I.Item_Name --bug3579625
5732:
5733: FROM MSC_ALLOC_SUPPLIES SA,
5734: MSC_SYSTEM_ITEMS I, --bug3579625
5735: MSC_TRADING_PARTNERS MTP,--bug3684383
5736: MSC_TRADING_PARTNER_SITES MTPS --bug3684383
5737:
5738: WHERE SA.PLAN_ID = p_plan_id
5745: AND SA.ORGANIZATION_ID = p_org_id
5746: AND SA.ALLOCATED_QUANTITY <> 0
5747: AND SA.DEMAND_CLASS = NVL(p_demand_class, SA.DEMAND_CLASS )
5748: AND TRUNC(SA.SUPPLY_DATE) < p_itf
5749: -- bug3579625 Addition join with MSC_SYSTEM_ITEMS (I)
5750: AND SA.PLAN_ID = I.PLAN_ID
5751: AND SA.SR_INSTANCE_ID = I.SR_INSTANCE_ID
5752: AND SA.ORIGINAL_ITEM_ID = I.INVENTORY_ITEM_ID
5753: AND SA.ORGANIZATION_ID = I.ORGANIZATION_ID
5992: p_family_id IN NUMBER,
5993: p_sr_member_id IN NUMBER,
5994: p_sr_family_id IN NUMBER,
5995: p_org_id IN NUMBER,
5996: --bug3671294 now we donot need this as we will join with msc_system_items
5997: --p_inv_item_name IN VARCHAR2,
5998: p_org_code IN VARCHAR2,
5999: p_instance_id IN NUMBER,
6000: p_plan_id IN NUMBER,
6094: MSC_ALLOC_DEMANDS AD,
6095: MSC_ALLOC_TEMP TEMP,
6096: MSC_TRADING_PARTNERS MTP,--bug3263368
6097: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
6098: MSC_SYSTEM_ITEMS msi --bug3671294
6099: WHERE
6100: AD.PLAN_ID = p_plan_id
6101: AND AD.SR_INSTANCE_ID = p_instance_id
6102: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
6157: l_null_date col30 --bug3684383 -- Bug 3875786 - local variable used for NULL
6158: FROM
6159: MSC_ALLOC_SUPPLIES SA,
6160: MSC_ALLOC_TEMP TEMP,
6161: MSC_SYSTEM_ITEMS msi, --bug3671294
6162: MSC_TRADING_PARTNER_SITES MTPS, --bug3684383
6163: MSC_TRADING_PARTNERS MTP --bug3684383
6164: WHERE
6165: SA.PLAN_ID = p_plan_id
6654: MSC_ALLOC_DEMANDS AD,
6655: MSC_ALLOC_TEMP TEMP,
6656: MSC_TRADING_PARTNERS MTP,--bug3263368
6657: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
6658: MSC_SYSTEM_ITEMS msi --bug3671294
6659: WHERE
6660: AD.PLAN_ID = p_plan_id
6661: AND AD.SR_INSTANCE_ID = p_instance_id
6662: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
6762:
6763: FROM
6764: MSC_ALLOC_SUPPLIES SA,
6765: MSC_ALLOC_TEMP TEMP,
6766: MSC_SYSTEM_ITEMS msi --bug3671294
6767: WHERE
6768: SA.PLAN_ID = p_plan_id
6769: AND SA.SR_INSTANCE_ID = p_instance_id
6770: AND SA.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
7165: MSC_ALLOC_DEMANDS AD,
7166: MSC_ALLOC_HIERARCHY_TEMP TEMP,
7167: MSC_TRADING_PARTNERS MTP,--bug3263368
7168: MSC_TRADING_PARTNER_SITES MTPS, --bug3263368
7169: MSC_SYSTEM_ITEMS msi --bug3671294
7170: WHERE
7171: AD.PLAN_ID = p_plan_id
7172: AND AD.SR_INSTANCE_ID = p_instance_id
7173: AND AD.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
7270: l_null_date col36 --bug3263368 ORIG_REQUEST_DATE -- Bug 3875786 - local variable used for NULL
7271: FROM
7272: MSC_ALLOC_SUPPLIES SA,
7273: MSC_ALLOC_HIERARCHY_TEMP TEMP,
7274: MSC_SYSTEM_ITEMS msi --bug3671294
7275: WHERE
7276: SA.PLAN_ID = p_plan_id
7277: AND SA.SR_INSTANCE_ID = p_instance_id
7278: AND SA.INVENTORY_ITEM_ID in (p_member_id, p_family_id)
8518: i2.sr_inventory_item_id),
8519: i1.sr_inventory_item_id
8520: )
8521: INTO l_pf_atp_item_id
8522: FROM msc_system_items i2,
8523: msc_system_items i1
8524: WHERE i1.sr_inventory_item_id = p_inventory_item_id
8525: AND i1.organization_id = p_organization_id
8526: AND i1.plan_id = p_plan_id
8519: i1.sr_inventory_item_id
8520: )
8521: INTO l_pf_atp_item_id
8522: FROM msc_system_items i2,
8523: msc_system_items i1
8524: WHERE i1.sr_inventory_item_id = p_inventory_item_id
8525: AND i1.organization_id = p_organization_id
8526: AND i1.plan_id = p_plan_id
8527: AND i1.sr_instance_id = p_instance_id
8569: END IF;
8570:
8571: SELECT i.aggregate_time_fence_date
8572: INTO l_atf_date
8573: FROM msc_system_items i
8574: WHERE i.plan_id = p_plan_id
8575: AND i.sr_instance_id = p_instance_id
8576: AND i.organization_id = p_organization_id
8577: AND i.sr_inventory_item_id = p_inventory_item_id;
8615: 3, i2.planning_time_fence_days,
8616: 4, r.aggregate_time_fence
8617: )
8618: INTO l_atf_days
8619: FROM msc_system_items i2,
8620: msc_system_items i1,
8621: msc_atp_rules r,
8622: msc_trading_partners tp
8623: WHERE i1.inventory_item_id = p_inventory_item_id
8616: 4, r.aggregate_time_fence
8617: )
8618: INTO l_atf_days
8619: FROM msc_system_items i2,
8620: msc_system_items i1,
8621: msc_atp_rules r,
8622: msc_trading_partners tp
8623: WHERE i1.inventory_item_id = p_inventory_item_id
8624: AND i1.organization_id = p_organization_id
8700: INTO p_family_id,
8701: p_sr_family_id,
8702: p_atf_date,
8703: p_family_name --bug3700564
8704: FROM msc_system_items i2,
8705: msc_system_items i1
8706: WHERE i1.inventory_item_id = p_inventory_item_id
8707: AND i1.organization_id = p_organization_id
8708: AND i1.plan_id = p_plan_id
8701: p_sr_family_id,
8702: p_atf_date,
8703: p_family_name --bug3700564
8704: FROM msc_system_items i2,
8705: msc_system_items i1
8706: WHERE i1.inventory_item_id = p_inventory_item_id
8707: AND i1.organization_id = p_organization_id
8708: AND i1.plan_id = p_plan_id
8709: AND i1.sr_instance_id = p_instance_id
8731: INTO p_family_id,
8732: p_sr_family_id,
8733: p_atf_date,
8734: p_family_name
8735: FROM msc_system_items i1
8736: WHERE i1.inventory_item_id = p_inventory_item_id
8737: AND i1.organization_id = p_organization_id
8738: AND i1.plan_id = p_plan_id
8739: AND i1.sr_instance_id = p_instance_id;
8916: SELECT c2.calendar_date,i2.organization_id,i2.sr_instance_id,i2.inventory_item_id
8917: BULK COLLECT INTO l_atf_date,l_organization_id,l_sr_instance_id,l_inventory_item_id
8918: FROM msc_plans mp,
8919: msc_plan_organizations po,
8920: msc_system_items i2,
8921: msc_trading_partners tp,
8922: msc_atp_rules r,
8923: msc_calendar_dates c1,
8924: msc_calendar_dates c2
8955:
8956: IF l_inventory_item_id IS NOT NULL AND l_inventory_item_id.COUNT > 0 THEN
8957:
8958: FORALL j IN l_inventory_item_id.first.. l_inventory_item_id.last
8959: UPDATE msc_system_items i
8960: SET aggregate_time_fence_date = l_atf_date(j)
8961: WHERE i.plan_id = p_plan_id
8962: AND i.ORGANIZATION_ID = l_organization_id(j)
8963: AND i.SR_INSTANCE_ID = l_sr_instance_id(j)
8963: AND i.SR_INSTANCE_ID = l_sr_instance_id(j)
8964: AND i.inventory_item_id = l_inventory_item_id(j);
8965:
8966: FORALL k IN l_inventory_item_id.first.. l_inventory_item_id.last
8967: UPDATE msc_system_items i
8968: SET aggregate_time_fence_date = l_atf_date(k)
8969: WHERE i.plan_id = p_plan_id
8970: AND i.ORGANIZATION_ID = l_organization_id(k)
8971: AND i.SR_INSTANCE_ID = l_sr_instance_id(k)
8978:
8979: ----bug3663487 code commented for Performance fix
8980: /*
8981: -- populate ATF date for PF items
8982: UPDATE msc_system_items i
8983: SET aggregate_time_fence_date =
8984: (SELECT c2.calendar_date
8985: FROM msc_calendar_dates c2,
8986: msc_calendar_dates c1,
8986: msc_calendar_dates c1,
8987: msc_atp_rules r,
8988: msc_trading_partners tp,
8989: msc_plans mp,
8990: msc_system_items i2
8991: WHERE i2.inventory_item_id = i.inventory_item_id
8992: AND i2.organization_id = i.organization_id
8993: AND i2.sr_instance_id = i.sr_instance_id
8994: AND i2.plan_id = -1
9017: AND i.bom_item_type = 5
9018: AND i.atp_flag = 'Y';
9019:
9020: -- populate ATF date for atpable member items
9021: UPDATE msc_system_items i
9022: SET aggregate_time_fence_date =
9023: (SELECT i2.aggregate_time_fence_date
9024: FROM msc_system_items i2
9025: WHERE i2.inventory_item_id = i.product_family_id
9020: -- populate ATF date for atpable member items
9021: UPDATE msc_system_items i
9022: SET aggregate_time_fence_date =
9023: (SELECT i2.aggregate_time_fence_date
9024: FROM msc_system_items i2
9025: WHERE i2.inventory_item_id = i.product_family_id
9026: AND i2.sr_instance_id = i.sr_instance_id
9027: AND i2.organization_id = i.organization_id
9028: AND i2.plan_id = i.plan_id
9035: i.product_family_id)
9036: AND i.bom_item_type <> 5
9037: AND i.atp_flag = 'Y'
9038: AND EXISTS (SELECT 1
9039: FROM msc_system_items i2
9040: WHERE i2.inventory_item_id = i.product_family_id
9041: AND i2.sr_instance_id = i.sr_instance_id
9042: AND i2.organization_id = i.organization_id
9043: AND i2.plan_id = i.plan_id
9122: sum(peg1.allocated_quantity)
9123: from
9124: (select /*+ NO_MERGE */ distinct d.disposition_id transaction_id --13021736 perf issue
9125: from msc_demands d,
9126: msc_system_items msi
9127: where msi.plan_id = p_plan_id
9128: and msi.bom_item_type <> 5
9129: and NVL(msi.product_family_id, -23453) <> -23453 -- Bug 3629191
9130: --and msi.product_family_id is not null
10310: NULL, msi.inventory_item_id,
10311: msi.product_family_id) product_family_id ,
10312: decode(d.order_date_type_code,2,d.request_date,
10313: d.request_ship_date)request_date --bug3263368
10314: FROM msc_system_items msi,
10315: msc_demands d,
10316: msc_full_pegging peg,
10317: ' || p_alloc_temp_table || ' mat,
10318: msc_supplies s
10468: d.request_ship_date)request_date --bug3263368
10469: FROM msc_full_pegging peg1,
10470: msc_demands d,
10471: msc_supplies s,
10472: msc_system_items msi
10473: WHERE d.demand_id = peg1.demand_id
10474: AND d.plan_id = peg1.plan_id
10475: AND d.sr_instance_id = peg1.sr_instance_id
10476: AND d.organization_id= peg1.organization_id --bug3453289
10683: s.ship_to_site_id,
10684: decode(msi.aggregate_time_fence_date,
10685: NULL, msi.inventory_item_id,
10686: msi.product_family_id) product_family_id
10687: FROM msc_system_items msi,
10688: msc_supplies s,
10689: msc_full_pegging peg,
10690: ' || p_alloc_temp_table || ' mat
10691: WHERE msi.plan_id = :p_plan_id
10772: :l_sysdate,
10773: :l_user_id,
10774: :l_sysdate
10775: from msc_supplies s,
10776: msc_system_items i
10777: where i.aggregate_time_fence_date is not null
10778: and i.bom_item_type <> 5
10779: and i.plan_id = :p_plan_id
10780: and i.atp_flag = :l_yes
10989: FROM msc_full_pegging peg2,
10990: msc_full_pegging peg1,
10991: msc_demands d,
10992: msc_supplies s,
10993: msc_system_items msi
10994: WHERE peg1.plan_id = :p_plan_id
10995: AND peg2.plan_id = peg1.plan_id
10996: AND peg2.pegging_id = peg1.end_pegging_id
10997: AND peg2.demand_id IN (-1, -2)
11156: FROM msc_full_pegging peg2,
11157: msc_full_pegging peg1,
11158: msc_demands d,
11159: msc_supplies s,
11160: msc_system_items msi
11161: WHERE peg1.plan_id = :p_plan_id
11162: AND peg2.plan_id = peg1.plan_id
11163: AND peg2.pegging_id = peg1.end_pegging_id
11164: AND peg2.demand_id IN (-1, -2)
11343: :l_sysdate
11344: FROM msc_full_pegging peg1,
11345: msc_demands d,
11346: msc_supplies s,
11347: msc_system_items msi
11348: WHERE d.demand_id = peg1.demand_id
11349: AND d.plan_id = peg1.plan_id
11350: AND d.sr_instance_id = peg1.sr_instance_id
11351: AND d.origination_type NOT IN (5,7,8,9,11,15,22,28,29,31,70)
11597: msc_full_pegging peg2,
11598: msc_full_pegging peg1 ,
11599: msc_demands d1,
11600: msc_supplies s,
11601: msc_system_items msi
11602: WHERE peg2.plan_id = :p_plan_id
11603: AND peg2.plan_id = peg1.plan_id
11604: AND peg2.end_pegging_id = peg1.pegging_id
11605: AND peg2.sr_instance_id = peg1.sr_instance_id
11785: msi.product_family_id) product_family_id
11786: FROM msc_full_pegging peg2,
11787: msc_full_pegging peg1,
11788: msc_supplies s,
11789: msc_system_items msi
11790: WHERE peg1.plan_id = :p_plan_id
11791: AND peg2.plan_id = peg1.plan_id
11792: AND peg2.pegging_id = peg1.end_pegging_id
11793: AND peg2.demand_id IN (-1, -2)
11885: :l_sysdate last_update_date
11886: FROM msc_full_pegging peg2,
11887: msc_full_pegging peg1,
11888: msc_supplies s,
11889: msc_system_items msi
11890: WHERE peg1.plan_id = :p_plan_id
11891: AND peg2.plan_id = peg1.plan_id
11892: AND peg2.pegging_id = peg1.end_pegging_id
11893: AND peg2.demand_id IN (-1, -2)
11968: :l_sysdate,
11969: :l_user_id,
11970: :l_sysdate
11971: from msc_supplies s,
11972: msc_system_items i
11973: where i.aggregate_time_fence_date is not null
11974: and i.bom_item_type <> 5
11975: and i.plan_id = :p_plan_id
11976: and s.inventory_item_id = i.inventory_item_id
12097: FROM msc_supplies s,
12098: msc_full_pegging peg2,
12099: msc_full_pegging peg1,
12100: msc_demands d,
12101: msc_system_items msi
12102: WHERE peg2.plan_id = :p_plan_id
12103: AND peg2.plan_id = peg1.plan_id
12104: AND peg2.end_pegging_id = peg1.pegging_id
12105: AND d.plan_id = peg1.plan_id
12185: WHERE AD2.plan_id = p_plan_id
12186: GROUP BY AD2.parent_demand_id, AD2.demand_class
12187: )
12188: AND EXISTS (SELECT 1
12189: FROM MSC_SYSTEM_ITEMS I
12190: WHERE I.inventory_item_id = AD.inventory_item_id
12191: AND I.organization_id = AD.organization_id
12192: AND I.sr_instance_id = AD.sr_instance_id
12193: AND I.plan_id = AD.plan_id
12205: AND AD.demand_class = AD2.demand_class
12206: GROUP BY AD2.parent_demand_id, AD2.demand_class
12207: HAVING count(*) = 1)
12208: AND EXISTS (SELECT 1
12209: FROM MSC_SYSTEM_ITEMS I
12210: WHERE I.inventory_item_id = AD.inventory_item_id
12211: AND I.organization_id = AD.organization_id
12212: AND I.sr_instance_id = AD.sr_instance_id
12213: AND I.plan_id = AD.plan_id