[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,
6131: AND RTG.ORGANIZATION_ID = p_organization_id
6132: AND RTG.ROUTING_SEQUENCE_ID = l_routing_seq_id
6133: -- AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id -- Local var for common and others
6134: -- Bug 3432530
6135: -- Comment out join conditions for msc_system_items
6136: -- 3358981 Eliminate semi cartesian product, streamline query.
6137: AND RTG.assembly_item_id = mcbom1.inventory_item_id
6138: -- Join to system items
6139: -- AND I.PLAN_ID = RTG.PLAN_ID
6175: AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
6176: AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
6177: AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
6178: AND C1.CALENDAR_DATE = p_requested_date
6179: -- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
6180: AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
6181: AND C1.CALENDAR_CODE = l_calendar_code
6182: AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
6183: -- Bug 3432530 Use local variables.
6282: FROM
6283: MSC_CTO_BOM mcbom1,
6284: MSC_PROCESS_EFFECTIVITY proc,
6285: MSC_CTO_BOM mcbom2,
6286: -- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
6287: MSC_ROUTINGS RTG,
6288: MSC_ROUTING_OPERATIONS OP,
6289: MSC_BOM_COMPONENTS mbc,
6290: MSC_OPERATION_COMPONENTS moc,
6348: AND RTG.ORGANIZATION_ID = proc.organization_id
6349: AND RTG.ROUTING_SEQUENCE_ID = NVL(proc.routing_sequence_id,
6350: RTG.ROUTING_SEQUENCE_ID)
6351: -- Bug 3432530
6352: -- Comment out join conditions for msc_system_items
6353: -- 3358981 Eliminate semi cartesian product, streamline query.
6354: AND RTG.assembly_item_id = DECODE (proc.routing_sequence_id, NULL,
6355: proc.item_id, l_inventory_item_id )
6356: --(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
6425: AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
6426: AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
6427: AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
6428: AND C1.CALENDAR_DATE = p_requested_date
6429: -- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
6430: AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
6431: AND C1.CALENDAR_CODE = l_calendar_code
6432: AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
6433: -- Bug 3432530 Use local variables.
6575: -- krajan : 2408696
6576: --agilent chnages: since plan already store the data in right uom, we dont need to convert it
6577: -- MSC_UOM_CONVERSIONS MUC2,
6578: -- MSC_UOM_CONVERSIONS MUC1,
6579: MSC_SYSTEM_ITEMS I,
6580: MSC_ROUTINGS RTG,
6581: MSC_ROUTING_OPERATIONS OP,
6582: MSC_OPERATION_RESOURCE_SEQS SEQ,
6583: MSC_OPERATION_RESOURCES RES,
6776: DR.Department_code Department_code,
6777: DR.Resource_code Resource_code
6778: FROM
6779: MSC_CTO_BOM mcbom1,
6780: -- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
6781: MSC_ROUTINGS RTG,
6782: MSC_ROUTING_OPERATIONS OP,
6783: MSC_OPERATION_RESOURCE_SEQS SEQ,
6784: MSC_OPERATION_RESOURCES RES,
6801: AND RTG.SR_INSTANCE_ID = mcbom1.sr_instance_id
6802: AND RTG.ORGANIZATION_ID = p_organization_id
6803: AND RTG.ROUTING_SEQUENCE_ID = l_routing_seq_id -- For common routing this will be null.
6804: -- Bug 3432530
6805: -- Comment out join conditions for msc_system_items
6806: -- 3358981 Eliminate semi cartesian product, streamline query.
6807: AND RTG.assembly_item_id = mcbom1.inventory_item_id
6808: -- Join to system items
6809: -- AND I.PLAN_ID = RTG.PLAN_ID
6845: AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
6846: AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
6847: AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
6848: AND C1.CALENDAR_DATE = p_requested_date
6849: -- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
6850: AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
6851: AND C1.CALENDAR_CODE = l_calendar_code
6852: AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
6853: -- Bug 3432530 Use local variables.
6926: FROM
6927: MSC_CTO_BOM mcbom1,
6928: MSC_PROCESS_EFFECTIVITY proc,
6929: MSC_CTO_BOM mcbom2,
6930: -- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
6931: MSC_ROUTINGS RTG,
6932: MSC_ROUTING_OPERATIONS OP,
6933: MSC_BOM_COMPONENTS mbc,
6934: MSC_OPERATION_COMPONENTS moc,
6991: AND RTG.ORGANIZATION_ID = proc.organization_id
6992: AND RTG.ROUTING_SEQUENCE_ID = NVL(proc.routing_sequence_id,
6993: RTG.ROUTING_SEQUENCE_ID)
6994: -- Bug 3432530
6995: -- Comment out join conditions for msc_system_items
6996: -- 3358981 Eliminate semi cartesian product, streamline query.
6997: AND RTG.assembly_item_id = DECODE (proc.routing_sequence_id, NULL,
6998: proc.item_id, l_inventory_item_id )
6999: -- Join to system items
7068: AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
7069: AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
7070: AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
7071: AND C1.CALENDAR_DATE = p_requested_date
7072: -- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
7073: AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
7074: AND C1.CALENDAR_CODE = l_calendar_code
7075: AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
7076: -- Bug 3432530 Use local variables.
7124: l_null_char resource_Code
7125: FROM MSC_CALENDAR_DATES C2,
7126: MSC_CALENDAR_DATES C1,
7127: MSC_ROUTINGS RTG,
7128: MSC_SYSTEM_ITEMS I
7129: WHERE I.PLAN_ID = RTG.PLAN_ID
7130: AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
7131: AND I.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
7132: AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
7243: DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD, 1)) reverse_cumulative_yield,--4694958
7244: DR.Department_code Department_code,
7245: DR.Resource_code Resource_code
7246: FROM
7247: MSC_SYSTEM_ITEMS I,
7248: MSC_ROUTINGS RTG,
7249: MSC_ROUTING_OPERATIONS OP,
7250: MSC_OPERATION_RESOURCE_SEQS SEQ,
7251: MSC_OPERATION_RESOURCES RES,
7338: l_null_char resource_Code
7339: FROM MSC_CALENDAR_DATES C2,
7340: MSC_CALENDAR_DATES C1,
7341: MSC_ROUTINGS RTG,
7342: MSC_SYSTEM_ITEMS I
7343: WHERE I.PLAN_ID = RTG.PLAN_ID
7344: AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
7345: AND I.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
7346: AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
7541: AND BOMS.SR_INSTANCE_ID = p_instance_id
7542: AND BOMS.ORGANIZATION_ID = p_organization_id
7543: AND BOMS.BILL_SEQUENCE_ID = p_bill_seq_id;
7544: --(ssurendr) Bug 2865389 Removed condition for Alternate bom designator as
7545: --we are accessing bom by bill sequance id. Also removed MSC_SYSTEM_ITEMS from the SQL
7546: --AND BOMS.ALTERNATE_BOM_DESIGNATOR IS NULL;
7547: EXCEPTION
7548: WHEN OTHERS THEN
7549: l_assembly_quantity := 1;
9371: mbc.scale_rounding_variance scale_rounding_variance,
9372: mbc.rounding_direction rounding_direction,
9373: mbc.component_yield_factor component_yield_factor,
9374: MBC.USAGE_QUANTITY*mbc.component_yield_factor usage_qty --4775920
9375: FROM MSC_SYSTEM_ITEMS I2,
9376: MSC_CALENDAR_DATES C2,
9377: MSC_CALENDAR_DATES C1,
9378: MSC_BOM_COMPONENTS MBC,
9379: MSC_BOMS BOMS,
9376: MSC_CALENDAR_DATES C2,
9377: MSC_CALENDAR_DATES C1,
9378: MSC_BOM_COMPONENTS MBC,
9379: MSC_BOMS BOMS,
9380: MSC_SYSTEM_ITEMS I,
9381: MSC_OPERATION_COMPONENTS OPC,
9382: MSC_ROUTING_OPERATIONS OP
9383: WHERE I.PLAN_ID = BOMS.PLAN_ID
9384: AND I.SR_INSTANCE_ID = BOMS.SR_INSTANCE_ID
9519: mbc.scale_rounding_variance ,
9520: mbc.rounding_direction,
9521: mbc.component_yield_factor, --4570421
9522: MBC.USAGE_QUANTITY*mbc.component_yield_factor --4775920
9523: FROM MSC_SYSTEM_ITEMS I2,
9524: MSC_CALENDAR_DATES C2,
9525: MSC_CALENDAR_DATES C1,
9526: MSC_BOM_COMPONENTS MBC,
9527: MSC_BOMS BOMS
11164: l_uom_code varchar2(10);
11165: l_instance_id number;
11166: l_org_id number;
11167: l_processing_lead_time number;
11168: l_postprocessing_lead_time NUMBER; -- SCLT new variable to remove join with msc_system_items
11169:
11170: l_default_atp_rule_id NUMBER;
11171: l_calendar_code VARCHAR2(14);
11172: l_calendar_exception_set_id NUMBER;
11537: AND least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date) --4055719
11538: and sd_qty <> 0
11539: AND (inventory_item_id = l_inv_item_id OR
11540: inventory_item_id in
11541: (select inventory_item_id from msc_system_items msi
11542: where msi.base_item_id = l_inv_item_id
11543: and msi.plan_id = p_sup_atp_info_rec.plan_id
11544: and msi.organization_id = p_sup_atp_info_rec.organization_id
11545: and msi.base_item_id = l_inv_item_id))
11566: AND NVL(p.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
11567: AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2
11568: AND (p.inventory_item_id = l_inv_item_id OR
11569: p.inventory_item_id in
11570: (select inventory_item_id from msc_system_items msi
11571: where msi.sr_instance_id = p_sup_atp_info_rec.instance_id
11572: and msi.plan_id = p_sup_atp_info_rec.plan_id
11573: and msi.organization_id = p_sup_atp_info_rec.organization_id
11574: and msi.base_item_id = l_inv_item_id))
11713: ---only consider ATP inserted POs
11714: --- Plan POs are tied to forecast.
11715: AND ((p.inventory_item_id = l_inv_item_id and p.record_source=2) OR
11716: p.inventory_item_id in
11717: (select inventory_item_id from msc_system_items msi
11718: where msi.sr_instance_id = p_sup_atp_info_rec.instance_id
11719: and msi.plan_id = p_sup_atp_info_rec.plan_id
11720: and msi.organization_id = p_sup_atp_info_rec.organization_id
11721: and msi.base_item_id = l_inv_item_id))
11983: ---we only consider ATP inserted PO for MOdels.
11984: --Ignore Planning inserted POs for models as they would be tied to forecats
11985: AND ((p.inventory_item_id = l_inv_item_id and p.record_source=2) OR
11986: p.inventory_item_id in
11987: (select inventory_item_id from msc_system_items msi
11988: where msi.sr_instance_id = p_sup_atp_info_rec.instance_id
11989: and msi.plan_id = p_sup_atp_info_rec.plan_id
11990: and msi.organization_id = p_sup_atp_info_rec.organization_id
11991: and msi.base_item_id = l_inv_item_id))
12621: -- to obtain capacity used.
12622:
12623: SELECT NVL(unit_weight,1), NVL(unit_volume,1)
12624: INTO l_unit_weight, l_unit_volume
12625: FROM msc_system_items
12626: WHERE plan_id = p_plan_id
12627: AND organization_id = p_to_organization_id
12628: AND inventory_item_id = p_inventory_item_id
12629: AND sr_instance_id = p_dest_org_instance_id;