[Home] [Help]
160: MSC_RESOURCE_REQUIREMENTS REQ,
161: -- CTO Option Dependent Resources ODR
162: -- Option Dependent Resources Capacity Check
163: -- Add Link to Items
164: MSC_SYSTEM_ITEMS I
165: -- 2859130
166: -- MSC_CALENDAR_DATES C
167: -- Bug 2675504, 2665805,
168: --bug3394866
266: MSC_RESOURCE_REQUIREMENTS REQ,
267: -- CTO Option Dependent Resources ODR
268: -- Option Dependent Resources Capacity Check
269: -- Add Link to Items
270: MSC_SYSTEM_ITEMS I,
271: MSC_CALENDAR_DATES C
272: -- Bug 2675504, 2665805,
273: --bug3394866
274: WHERE DR.PLAN_ID = p_plan_id
384: -- 2859130 MSC_CALENDAR_DATES C,
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
510: MSC_CALENDAR_DATES C,
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
699: MSC_RESOURCE_REQUIREMENTS REQ,
700: -- CTO Option Dependent Resources
701: -- Option Dependent Resources Capacity Check
702: -- Add Link to Items
703: MSC_SYSTEM_ITEMS I
704: -- 2859130 MSC_CALENDAR_DATES C
705: -- Bug 2675504, 2665805,
706: --bug3394866
707: WHERE DR.PLAN_ID = p_plan_id
904: MSC_RESOURCE_REQUIREMENTS REQ,
905: -- CTO Option Dependent Resources
906: -- Option Dependent Resources Capacity Check
907: -- Add Link to Items
908: MSC_SYSTEM_ITEMS I,
909: MSC_CALENDAR_DATES C
910: -- Bug 2675504, 2665805,
911: --bug3394866
912: WHERE DR.PLAN_ID = p_plan_id
1115: FROM MSC_DEPARTMENT_RESOURCES DR,
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
1338: FROM MSC_DEPARTMENT_RESOURCES DR,
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
1495: -- Summary enhancement : differences from non summary SQL:
1496: -- 1. No union with MSC_NET_RES_AVAIL
1497: -- 2. Get the hours always from RESOURCE_HOURS - never from LOAD_RATE or DAILY_RESOURCE_HOURS
1498: -- 3. PARENT_ID removed from where clause. No difference between constrained and unconstrained plans
1499: -- 4. MSC_SYSTEM_ITEMS not included in the join because the filters on items is not applied for ATP records
1500: -- 5. MSC_PLANS included in the join to get latest refresh number
1501: -- 6. Filter records based on refresh_number
1502: -- Bug 3348095
1503: -- For ATP created records use end_date otherwise start_date
1879: trunc(D.SD_DATE) SD_DATE, --4135752
1880: -1* D.SD_QTY SD_QTY
1881: FROM MSC_ATP_SUMMARY_SO D,
1882: MSC_ATP_RULES R,
1883: MSC_SYSTEM_ITEMS I
1884: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
1885: AND I.ORGANIZATION_ID = p_org_id
1886: AND I.SR_INSTANCE_ID = p_instance_id
1887: AND I.PLAN_ID = p_plan_id
1904: trunc(S.SD_DATE) SD_DATE, --4135752
1905: S.SD_QTY SD_QTY
1906: FROM MSC_ATP_SUMMARY_SD S,
1907: MSC_ATP_RULES R,
1908: MSC_SYSTEM_ITEMS I
1909: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
1910: AND I.ORGANIZATION_ID = p_org_id
1911: AND I.SR_INSTANCE_ID = p_instance_id
1912: AND I.PLAN_ID = p_plan_id
1949: FROM (
1950: SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
1951: SD_DATE, SD_QTY
1952: FROM MSC_ATP_SUMMARY_SD S,
1953: MSC_SYSTEM_ITEMS I
1954: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
1955: AND I.ORGANIZATION_ID = p_org_id
1956: AND I.SR_INSTANCE_ID = p_instance_id
1957: AND I.PLAN_ID = p_plan_id
1967: decode(D.USING_REQUIREMENT_QUANTITY, -- Consider unscheduled orders as dummy supplies
1968: 0, nvl(D.OLD_DEMAND_QUANTITY,0), --4658238 -- For summary enhancement
1969: -1 * D.USING_REQUIREMENT_QUANTITY) SD_QTY
1970: FROM MSC_DEMANDS D,
1971: MSC_SYSTEM_ITEMS I,
1972: MSC_PLANS P -- For summary enhancement
1973: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
1974: AND I.ORGANIZATION_ID = p_org_id
1975: AND I.SR_INSTANCE_ID = p_instance_id
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
2000: AND I.SR_INSTANCE_ID = p_instance_id
2044: -1* D.USING_REQUIREMENT_QUANTITY SD_QTY
2045: FROM MSC_CALENDAR_DATES C,
2046: MSC_DEMANDS D,
2047: MSC_ATP_RULES R,
2048: MSC_SYSTEM_ITEMS I
2049: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2050: AND I.ORGANIZATION_ID = p_org_id
2051: AND I.SR_INSTANCE_ID = p_instance_id
2052: AND I.PLAN_ID = p_plan_id
2101: -- Bug 1756263, performance fix, use EXISTS subquery instead.
2102: --MSC_CALENDAR_DATES C,
2103: MSC_SALES_ORDERS D,
2104: MSC_ATP_RULES R,
2105: MSC_SYSTEM_ITEMS I,
2106: MSC_CALENDAR_DATES C
2107: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2108: AND I.ORGANIZATION_ID = p_org_id
2109: AND I.SR_INSTANCE_ID = p_instance_id
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
2162: AND I.ORGANIZATION_ID = p_org_id
2163: AND I.SR_INSTANCE_ID = p_instance_id
2283: --plan by requestdate,promisedate,scheduledate
2284: -- -1*D.USING_REQUIREMENT_QUANTITY SD_QTY
2285: -1*(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)) SD_QTY --5027568
2286: FROM MSC_DEMANDS D,
2287: MSC_SYSTEM_ITEMS I
2288: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2289: AND I.ORGANIZATION_ID = p_org_id
2290: AND I.SR_INSTANCE_ID = p_instance_id
2291: AND I.PLAN_ID = p_plan_id
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
2328: AND I.PLAN_ID = p_plan_id
2369: --D.USING_REQUIREMENT_QUANTITY) SD_QTY
2370: (D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0))) SD_QTY --5027568
2371: FROM MSC_CALENDAR_DATES C,
2372: MSC_DEMANDS D,
2373: MSC_SYSTEM_ITEMS I
2374: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2375: AND I.ORGANIZATION_ID = p_org_id
2376: AND I.SR_INSTANCE_ID = p_instance_id
2377: AND I.PLAN_ID = p_plan_id
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
2427: AND I.PLAN_ID = p_plan_id
2562: D.REQUEST_SHIP_DATE)) --bug3263368
2563: FROM MSC_CALENDAR_DATES C,
2564: MSC_DEMANDS D,
2565: MSC_ATP_RULES R,
2566: MSC_SYSTEM_ITEMS I,
2567: MSC_TRADING_PARTNERS MTP,--bug3263368
2568: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2569: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
2570: AND I.ORGANIZATION_ID = p_org_id
2645: D.REQUEST_SHIP_DATE)) --bug3263368
2646: FROM
2647: MSC_SALES_ORDERS D,
2648: MSC_ATP_RULES R,
2649: MSC_SYSTEM_ITEMS I,
2650: MSC_CALENDAR_DATES C,
2651: MSC_TRADING_PARTNERS MTP,--bug3263368
2652: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2653: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
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
2766: AND I.ORGANIZATION_ID = p_org_id
2767: AND I.SR_INSTANCE_ID = p_instance_id
2959: MTP.PARTNER_NAME, --bug3263368
2960: D.DEMAND_CLASS, --bug3263368
2961: DECODE(D.ORDER_DATE_TYPE_CODE,2,D.REQUEST_DATE,
2962: D.REQUEST_SHIP_DATE) --bug3263368
2963: FROM MSC_SYSTEM_ITEMS I,
2964: MSC_DEMANDS D,
2965: MSC_TRADING_PARTNERS MTP,--bug3263368
2966: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
2967: WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
3041: l_null_char, --bug3814584
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
3166: MTP.PARTNER_NAME, --bug3263368
3167: D.DEMAND_CLASS, --bug3263368
3168: DECODE(D.ORDER_DATE_TYPE_CODE,2,D.REQUEST_DATE,
3169: D.REQUEST_SHIP_DATE) --bug3263368
3170: FROM MSC_SYSTEM_ITEMS I,
3171: MSC_DEMANDS D,
3172: MSC_CALENDAR_DATES C,
3173: MSC_TRADING_PARTNERS MTP,--bug3263368
3174: MSC_TRADING_PARTNER_SITES MTPS --bug3263368
3261: l_null_char, --bug3814584
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
3890: ,comp.scale_rounding_variance scale_rounding_variance
3891: ,comp.rounding_direction rounding_direction
3892: ,comp.component_yield_factor component_yield_factor
3893:
3894: FROM msc_system_items msi, msc_component_substitutes sub,
3895: msc_bom_components comp, msc_boms bom, msc_system_items ch,
3896: msc_system_items pt, mrp_atp_details_temp peg
3897: WHERE peg.session_id = MSC_ATP_PVT.G_SESSION_ID
3898: AND peg.pegging_id = p_parent_pegging_id
3891: ,comp.rounding_direction rounding_direction
3892: ,comp.component_yield_factor component_yield_factor
3893:
3894: FROM msc_system_items msi, msc_component_substitutes sub,
3895: msc_bom_components comp, msc_boms bom, msc_system_items ch,
3896: msc_system_items pt, mrp_atp_details_temp peg
3897: WHERE peg.session_id = MSC_ATP_PVT.G_SESSION_ID
3898: AND peg.pegging_id = p_parent_pegging_id
3899: AND pt.sr_instance_id = p_instance_id
3892: ,comp.component_yield_factor component_yield_factor
3893:
3894: FROM msc_system_items msi, msc_component_substitutes sub,
3895: msc_bom_components comp, msc_boms bom, msc_system_items ch,
3896: msc_system_items pt, mrp_atp_details_temp peg
3897: WHERE peg.session_id = MSC_ATP_PVT.G_SESSION_ID
3898: AND peg.pegging_id = p_parent_pegging_id
3899: AND pt.sr_instance_id = p_instance_id
3900: AND pt.organization_id = peg.organization_id
3966: ,comp.rounding_direction rounding_direction
3967: ,comp.component_yield_factor component_yield_factor
3968: ,sub.usage_quantity/(comp.usage_quantity*comp.component_yield_factor) usage_qty --4775920
3969:
3970: FROM msc_system_items msi, msc_component_substitutes sub,
3971: msc_bom_components comp, msc_system_items ch
3972: WHERE ch.plan_id = p_plan_id
3973: AND ch.organization_id = p_organization_id
3974: AND ch.sr_instance_id = p_instance_id
3967: ,comp.component_yield_factor component_yield_factor
3968: ,sub.usage_quantity/(comp.usage_quantity*comp.component_yield_factor) usage_qty --4775920
3969:
3970: FROM msc_system_items msi, msc_component_substitutes sub,
3971: msc_bom_components comp, msc_system_items ch
3972: WHERE ch.plan_id = p_plan_id
3973: AND ch.organization_id = p_organization_id
3974: AND ch.sr_instance_id = p_instance_id
3975: AND ch.sr_inventory_item_id = p_inventory_item_id
6105: DR.resource_code resource_code
6106:
6107: FROM
6108: MSC_CTO_BOM mcbom1,
6109: -- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
6110: MSC_ROUTINGS RTG,
6111: MSC_ROUTING_OPERATIONS OP,
6112: MSC_OPERATION_RESOURCE_SEQS SEQ,
6113: MSC_OPERATION_RESOURCES RES,
6130: AND RTG.SR_INSTANCE_ID = mcbom1.sr_instance_id
6131: AND RTG.ORGANIZATION_ID = p_organization_id
6132: AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id -- Local var for common and others
6133: -- Bug 3432530
6134: -- Comment out join conditions for msc_system_items
6135: -- 3358981 Eliminate semi cartesian product, streamline query.
6136: AND RTG.assembly_item_id = mcbom1.inventory_item_id
6137: -- Join to system items
6138: -- AND I.PLAN_ID = RTG.PLAN_ID
6174: AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
6175: AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
6176: AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
6177: AND C1.CALENDAR_DATE = p_requested_date
6178: -- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
6179: AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
6180: AND C1.CALENDAR_CODE = l_calendar_code
6181: AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
6182: -- Bug 3432530 Use local variables.
6279: FROM
6280: MSC_CTO_BOM mcbom1,
6281: MSC_PROCESS_EFFECTIVITY proc,
6282: MSC_CTO_BOM mcbom2,
6283: -- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
6284: MSC_ROUTINGS RTG,
6285: MSC_ROUTING_OPERATIONS OP,
6286: MSC_BOM_COMPONENTS mbc,
6287: MSC_OPERATION_COMPONENTS moc,
6344: AND RTG.ORGANIZATION_ID = proc.organization_id
6345: AND RTG.ROUTING_SEQUENCE_ID = NVL(proc.routing_sequence_id,
6346: RTG.ROUTING_SEQUENCE_ID)
6347: -- Bug 3432530
6348: -- Comment out join conditions for msc_system_items
6349: -- 3358981 Eliminate semi cartesian product, streamline query.
6350: AND RTG.assembly_item_id = DECODE (proc.routing_sequence_id, NULL,
6351: proc.item_id, l_inventory_item_id )
6352: --(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
6421: AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
6422: AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
6423: AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
6424: AND C1.CALENDAR_DATE = p_requested_date
6425: -- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
6426: AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
6427: AND C1.CALENDAR_CODE = l_calendar_code
6428: AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
6429: -- Bug 3432530 Use local variables.
6571: -- krajan : 2408696
6572: --agilent chnages: since plan already store the data in right uom, we dont need to convert it
6573: -- MSC_UOM_CONVERSIONS MUC2,
6574: -- MSC_UOM_CONVERSIONS MUC1,
6575: MSC_SYSTEM_ITEMS I,
6576: MSC_ROUTINGS RTG,
6577: MSC_ROUTING_OPERATIONS OP,
6578: MSC_OPERATION_RESOURCE_SEQS SEQ,
6579: MSC_OPERATION_RESOURCES RES,
6772: DR.Department_code Department_code,
6773: DR.Resource_code Resource_code
6774: FROM
6775: MSC_CTO_BOM mcbom1,
6776: -- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
6777: MSC_ROUTINGS RTG,
6778: MSC_ROUTING_OPERATIONS OP,
6779: MSC_OPERATION_RESOURCE_SEQS SEQ,
6780: MSC_OPERATION_RESOURCES RES,
6797: AND RTG.SR_INSTANCE_ID = mcbom1.sr_instance_id
6798: AND RTG.ORGANIZATION_ID = p_organization_id
6799: AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id -- For common routing this will be null.
6800: -- Bug 3432530
6801: -- Comment out join conditions for msc_system_items
6802: -- 3358981 Eliminate semi cartesian product, streamline query.
6803: AND RTG.assembly_item_id = mcbom1.inventory_item_id
6804: -- Join to system items
6805: -- AND I.PLAN_ID = RTG.PLAN_ID
6841: AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
6842: AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
6843: AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
6844: AND C1.CALENDAR_DATE = p_requested_date
6845: -- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
6846: AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
6847: AND C1.CALENDAR_CODE = l_calendar_code
6848: AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
6849: -- Bug 3432530 Use local variables.
6922: FROM
6923: MSC_CTO_BOM mcbom1,
6924: MSC_PROCESS_EFFECTIVITY proc,
6925: MSC_CTO_BOM mcbom2,
6926: -- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
6927: MSC_ROUTINGS RTG,
6928: MSC_ROUTING_OPERATIONS OP,
6929: MSC_BOM_COMPONENTS mbc,
6930: MSC_OPERATION_COMPONENTS moc,
6987: AND RTG.ORGANIZATION_ID = proc.organization_id
6988: AND RTG.ROUTING_SEQUENCE_ID = NVL(proc.routing_sequence_id,
6989: RTG.ROUTING_SEQUENCE_ID)
6990: -- Bug 3432530
6991: -- Comment out join conditions for msc_system_items
6992: -- 3358981 Eliminate semi cartesian product, streamline query.
6993: AND RTG.assembly_item_id = DECODE (proc.routing_sequence_id, NULL,
6994: proc.item_id, l_inventory_item_id )
6995: -- Join to system items
7064: AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
7065: AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
7066: AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
7067: AND C1.CALENDAR_DATE = p_requested_date
7068: -- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
7069: AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
7070: AND C1.CALENDAR_CODE = l_calendar_code
7071: AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
7072: -- Bug 3432530 Use local variables.
7120: l_null_char resource_Code
7121: FROM MSC_CALENDAR_DATES C2,
7122: MSC_CALENDAR_DATES C1,
7123: MSC_ROUTINGS RTG,
7124: MSC_SYSTEM_ITEMS I
7125: WHERE I.PLAN_ID = RTG.PLAN_ID
7126: AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
7127: AND I.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
7128: AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
7239: DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD, 1)) reverse_cumulative_yield,--4694958
7240: DR.Department_code Department_code,
7241: DR.Resource_code Resource_code
7242: FROM
7243: MSC_SYSTEM_ITEMS I,
7244: MSC_ROUTINGS RTG,
7245: MSC_ROUTING_OPERATIONS OP,
7246: MSC_OPERATION_RESOURCE_SEQS SEQ,
7247: MSC_OPERATION_RESOURCES RES,
7334: l_null_char resource_Code
7335: FROM MSC_CALENDAR_DATES C2,
7336: MSC_CALENDAR_DATES C1,
7337: MSC_ROUTINGS RTG,
7338: MSC_SYSTEM_ITEMS I
7339: WHERE I.PLAN_ID = RTG.PLAN_ID
7340: AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
7341: AND I.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
7342: AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
7537: AND BOMS.SR_INSTANCE_ID = p_instance_id
7538: AND BOMS.ORGANIZATION_ID = p_organization_id
7539: AND BOMS.BILL_SEQUENCE_ID = p_bill_seq_id;
7540: --(ssurendr) Bug 2865389 Removed condition for Alternate bom designator as
7541: --we are accessing bom by bill sequance id. Also removed MSC_SYSTEM_ITEMS from the SQL
7542: --AND BOMS.ALTERNATE_BOM_DESIGNATOR IS NULL;
7543: EXCEPTION
7544: WHEN OTHERS THEN
7545: l_assembly_quantity := 1;
9366: mbc.scale_rounding_variance scale_rounding_variance,
9367: mbc.rounding_direction rounding_direction,
9368: mbc.component_yield_factor component_yield_factor,
9369: MBC.USAGE_QUANTITY*mbc.component_yield_factor usage_qty --4775920
9370: FROM MSC_SYSTEM_ITEMS I2,
9371: MSC_CALENDAR_DATES C2,
9372: MSC_CALENDAR_DATES C1,
9373: MSC_BOM_COMPONENTS MBC,
9374: MSC_BOMS BOMS,
9371: MSC_CALENDAR_DATES C2,
9372: MSC_CALENDAR_DATES C1,
9373: MSC_BOM_COMPONENTS MBC,
9374: MSC_BOMS BOMS,
9375: MSC_SYSTEM_ITEMS I,
9376: MSC_OPERATION_COMPONENTS OPC,
9377: MSC_ROUTING_OPERATIONS OP
9378: WHERE I.PLAN_ID = BOMS.PLAN_ID
9379: AND I.SR_INSTANCE_ID = BOMS.SR_INSTANCE_ID
9513: mbc.scale_rounding_variance ,
9514: mbc.rounding_direction,
9515: mbc.component_yield_factor, --4570421
9516: MBC.USAGE_QUANTITY*mbc.component_yield_factor --4775920
9517: FROM MSC_SYSTEM_ITEMS I2,
9518: MSC_CALENDAR_DATES C2,
9519: MSC_CALENDAR_DATES C1,
9520: MSC_BOM_COMPONENTS MBC,
9521: MSC_BOMS BOMS
11139: l_uom_code varchar2(10);
11140: l_instance_id number;
11141: l_org_id number;
11142: l_processing_lead_time number;
11143: l_postprocessing_lead_time NUMBER; -- SCLT new variable to remove join with msc_system_items
11144:
11145: l_default_atp_rule_id NUMBER;
11146: l_calendar_code VARCHAR2(14);
11147: l_calendar_exception_set_id NUMBER;
11504: AND least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date) --4055719
11505: and sd_qty <> 0
11506: AND (inventory_item_id = l_inv_item_id OR
11507: inventory_item_id in
11508: (select inventory_item_id from msc_system_items msi
11509: where msi.base_item_id = l_inv_item_id
11510: and msi.plan_id = p_sup_atp_info_rec.plan_id
11511: and msi.organization_id = p_sup_atp_info_rec.organization_id
11512: and msi.base_item_id = l_inv_item_id))
11533: AND NVL(p.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
11534: AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2
11535: AND (p.inventory_item_id = l_inv_item_id OR
11536: p.inventory_item_id in
11537: (select inventory_item_id from msc_system_items msi
11538: where msi.sr_instance_id = p_sup_atp_info_rec.instance_id
11539: and msi.plan_id = p_sup_atp_info_rec.plan_id
11540: and msi.organization_id = p_sup_atp_info_rec.organization_id
11541: and msi.base_item_id = l_inv_item_id))
11680: ---only consider ATP inserted POs
11681: --- Plan POs are tied to forecast.
11682: AND ((p.inventory_item_id = l_inv_item_id and p.record_source=2) OR
11683: p.inventory_item_id in
11684: (select inventory_item_id from msc_system_items msi
11685: where msi.sr_instance_id = p_sup_atp_info_rec.instance_id
11686: and msi.plan_id = p_sup_atp_info_rec.plan_id
11687: and msi.organization_id = p_sup_atp_info_rec.organization_id
11688: and msi.base_item_id = l_inv_item_id))
11950: ---we only consider ATP inserted PO for MOdels.
11951: --Ignore Planning inserted POs for models as they would be tied to forecats
11952: AND ((p.inventory_item_id = l_inv_item_id and p.record_source=2) OR
11953: p.inventory_item_id in
11954: (select inventory_item_id from msc_system_items msi
11955: where msi.sr_instance_id = p_sup_atp_info_rec.instance_id
11956: and msi.plan_id = p_sup_atp_info_rec.plan_id
11957: and msi.organization_id = p_sup_atp_info_rec.organization_id
11958: and msi.base_item_id = l_inv_item_id))
12588: -- to obtain capacity used.
12589:
12590: SELECT NVL(unit_weight,1), NVL(unit_volume,1)
12591: INTO l_unit_weight, l_unit_volume
12592: FROM msc_system_items
12593: WHERE plan_id = p_plan_id
12594: AND organization_id = p_to_organization_id
12595: AND inventory_item_id = p_inventory_item_id
12596: AND sr_instance_id = p_dest_org_instance_id;