[Home] [Help]
96:
97:
98: -- ========================================================================
99: --
100: -- Selects the rows in MSC_SUPPLIES for all orgs in a given plan
101: -- that meet the auto-release criteria and release those planned orders and reschedules
102: --
103: -- ========================================================================
104: PROCEDURE msc_auto_release(
275: msc_planners mpl,
276: msc_trading_partners mparam,
277: msc_system_items msi,
278: msc_system_items rsi,
279: msc_supplies mr,
280: msc_plan_organizations_v mpo,
281: msc_plans mps,
282: msc_trading_partners mt
283: WHERE -- mpo.organization_id = arg_org_id
333: AND rsi.inventory_item_id = mr.inventory_item_id
334: AND ( NVL(rsi.in_source_plan, 2) <> 1
335: OR
336: mr.transaction_id in (
337: select a.transaction_id from msc_supplies a, msc_supplies b,MSC_DESIGNATORS d, msc_system_items e
338: where a.schedule_designator_id is not null
339: and a.schedule_designator_id = b.schedule_designator_id
340: and a.organization_id = mr.organization_id
341: and a.organization_id = b.organization_id
459: ms.order_type,
460: ms.sr_instance_id ,
461: ms.plan_id
462: from
463: msc_supplies ms,
464: msc_plans mps,
465: msc_system_items msi,
466: msc_trading_partners mtp,
467: msc_calendar_dates cal1,
509: ms.order_type,
510: ms.sr_instance_id ,
511: ms.plan_id
512: from
513: msc_supplies ms,
514: msc_plans mps,
515: msc_system_items msi,
516: msc_trading_partners mtp,
517: msc_calendar_dates cal1,
554: and ms.batch_id is null
555: and
556: not exists (select 1 from msc_full_pegging mfp,
557: msc_demands md,
558: msc_supplies ms1
559: where mfp.sr_instance_id = ms.sr_instance_id and
560: mfp.plan_id = ms.plan_id and
561: mfp.transaction_id =ms.transaction_id and
562: mfp.demand_id =md.demand_id and
576: --decode(ms.order_type,)
577: ms.sr_instance_id ,
578: ms.plan_id
579: from
580: msc_supplies ms,
581: msc_plans mps,
582: msc_system_items msi,
583: msc_trading_partners mtp,
584: msc_calendar_dates cal1,
895: fnd_file.put_line(FND_FILE.LOG, var_buf);
896:
897: END IF; --10325294
898:
899: UPDATE msc_supplies
900: SET batch_id = MSC_Rel_Plan_PUB.g_batch_id
901: WHERE transaction_id = var_transaction_id
902: AND sr_instance_id = var_sr_instance_id
903: AND plan_id = var_plan_id ;
1118:
1119: select
1120: mb.alternate_bom_designator
1121: into var_alternate_bom
1122: from msc_supplies ms,
1123: msc_process_effectivity mpe,
1124: msc_boms mb
1125: where
1126: ms.plan_id = var_plan_id -- Plan_id
1147:
1148: select
1149: mr.alternate_routing_designator
1150: into var_alternate_routing
1151: from msc_supplies ms,
1152: msc_process_effectivity mpe,
1153: msc_routings mr
1154: where
1155: ms.plan_id = var_plan_id -- Plan_id
1170: ROLLBACK;
1171: end;
1172:
1173:
1174: UPDATE msc_supplies
1175: SET old_order_quantity = new_order_quantity,
1176: quantity_in_process = new_order_quantity,
1177: implement_date = new_schedule_date,
1178: implement_dock_date = new_dock_date, -- Added for Ferring Enhancement - bug 14751383 / 14358365
1281: msi.planner_code
1282: INTO var_org_code, var_item, var_planner_code
1283: FROM msc_system_items msi,
1284: msc_trading_partners param,
1285: msc_supplies mr
1286: WHERE mr.transaction_id = var_transaction_id
1287: AND mr.sr_instance_id = var_sr_instance_id
1288: AND mr.plan_id = var_plan_id
1289: AND msi.organization_id = mr.organization_id
1319: -- Update PO Reqs planned orders and reschedules
1320: -- ------------------------------------------------------------------
1321: var_entity := 'PO Planned Orders';
1322:
1323: UPDATE msc_supplies
1324: SET old_order_quantity = new_order_quantity,
1325: quantity_in_process = new_order_quantity,
1326: implement_date = new_schedule_date,
1327:
1398: p_error_rows
1399: );
1400:
1401:
1402: UPDATE msc_supplies
1403: SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
1404: load_type =decode(var_order_type,PLANNED_ERO,ERO_LOAD,IRO_LOAD)
1405: WHERE transaction_id = var_transaction_id
1406: AND sr_instance_id = var_sr_instance_id
1431: p_total_rows ,
1432: p_succ_rows ,
1433: p_error_rows
1434: );
1435: UPDATE msc_supplies
1436: SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
1437: load_type =TRANSFER_LOAD
1438: WHERE transaction_id = var_transaction_id
1439: AND sr_instance_id = var_sr_instance_id
1454: FETCH eam_cmro_orders_to_release INTO var_transaction_id,var_order_type,var_sr_instance_id,
1455: var_plan_id ;
1456: EXIT WHEN eam_cmro_orders_to_release%NOTFOUND;
1457:
1458: UPDATE msc_supplies
1459: SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
1460: /* update rest of the implement columns here */
1461: old_order_quantity = new_order_quantity,
1462: quantity_in_process = new_order_quantity,
1474: WHERE transaction_id = var_transaction_id
1475: AND sr_instance_id = var_sr_instance_id
1476: AND plan_id = var_plan_id ;
1477:
1478: /*????update other columns in msc_supplies if UI is not doing it....
1479: wht abt the wip class code , job name etc.. who will populate this*/
1480:
1481: END LOOP;
1482: CLOSE eam_cmro_orders_to_release;
1485: -- ------------------------------------------------------------------------
1486: -- Release the planned orders and reschedules
1487: -- ------------------------------------------------------------------------
1488: var_entity := 'Release';
1489: update msc_supplies
1490: set releasable= null where releasable = 99 ;
1491:
1492: /*purchase order reschedules require separate function call */
1493: msc_rel_wf.reschedule_purchase_orders
1945: FOR x IN 1..V_TAB.Count LOOP
1946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,V_TAB(x));
1947: IF (V_TAB(x) = '1 - 1' ) THEN
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-1 ****** ');
1949: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
1950: ( select ms.transaction_id
1951: from MSC_SUPPLIES ms,
1952: MSC_PQ_RESULTS mpr,
1953: MSC_PLAN_QUERIES mpq,
1947: IF (V_TAB(x) = '1 - 1' ) THEN
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-1 ****** ');
1949: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
1950: ( select ms.transaction_id
1951: from MSC_SUPPLIES ms,
1952: MSC_PQ_RESULTS mpr,
1953: MSC_PLAN_QUERIES mpq,
1954: MSC_PERSONAL_QUERIES mpers
1955: where mpr.plan_id = pPlan_id
1967:
1968: --Item Query/Applied to Item-org
1969: ElsIF (V_TAB(x) = '1 - 2' ) THEN
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-2 ****** ');
1971: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
1972: (Select ms.transaction_id
1973: from MSC_SUPPLIES ms,
1974: MSC_PQ_RESULTS mpr,
1975: MSC_PLAN_QUERIES mpq,
1969: ElsIF (V_TAB(x) = '1 - 2' ) THEN
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-2 ****** ');
1971: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
1972: (Select ms.transaction_id
1973: from MSC_SUPPLIES ms,
1974: MSC_PQ_RESULTS mpr,
1975: MSC_PLAN_QUERIES mpq,
1976: MSC_PERSONAL_QUERIES mpers
1977: where mpr.plan_id = pPlan_id
1990:
1991: --Item Query/Applied to Supersession
1992: ElsIF (V_TAB(x) = '1 - 3' ) THEN
1993: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-3 ****** ');
1994: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
1995: (Select ms.transaction_id
1996: from MSC_SUPPLIES ms,
1997: MSC_PQ_RESULTS mpr,
1998: MSC_ITEM_SUBSTITUTES mis
1992: ElsIF (V_TAB(x) = '1 - 3' ) THEN
1993: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-3 ****** ');
1994: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
1995: (Select ms.transaction_id
1996: from MSC_SUPPLIES ms,
1997: MSC_PQ_RESULTS mpr,
1998: MSC_ITEM_SUBSTITUTES mis
1999: ,MSC_PLAN_QUERIES mpq,
2000: MSC_PERSONAL_QUERIES mpers
2017:
2018: --Item Query/Applied to Supersession-org
2019: ElsIF (V_TAB(x) = '1 - 4' ) THEN
2020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-4 ****** ');
2021: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2022: (Select ms.transaction_id
2023: from MSC_SUPPLIES ms,
2024: MSC_PQ_RESULTS mpr,
2025: MSC_ITEM_SUBSTITUTES mis
2019: ElsIF (V_TAB(x) = '1 - 4' ) THEN
2020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-4 ****** ');
2021: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2022: (Select ms.transaction_id
2023: from MSC_SUPPLIES ms,
2024: MSC_PQ_RESULTS mpr,
2025: MSC_ITEM_SUBSTITUTES mis
2026: ,MSC_PLAN_QUERIES mpq,
2027: MSC_PERSONAL_QUERIES mpers
2047: ---*********************** Query Type = Exception ***********************
2048: -- Exceptions Query/Applied to Item
2049: ElsIF (V_TAB(x) = '4 - 1' ) THEN
2050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-1 ****** ');
2051: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2052: (Select ms.transaction_id
2053: from MSC_SUPPLIES ms,
2054: MSC_PQ_RESULTS mpr,
2055: MSC_EXCEPTION_DETAILS med
2049: ElsIF (V_TAB(x) = '4 - 1' ) THEN
2050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-1 ****** ');
2051: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2052: (Select ms.transaction_id
2053: from MSC_SUPPLIES ms,
2054: MSC_PQ_RESULTS mpr,
2055: MSC_EXCEPTION_DETAILS med
2056: ,MSC_PLAN_QUERIES mpq,
2057: MSC_PERSONAL_QUERIES mpers
2076: ) ;
2077:
2078: ElsIF (V_TAB(x) = '4 - 2' ) THEN
2079: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-2 ****** ');
2080: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2081: ( Select ms.transaction_id
2082: from MSC_SUPPLIES ms,
2083: MSC_PQ_RESULTS mpr,
2084: MSC_EXCEPTION_DETAILS med
2078: ElsIF (V_TAB(x) = '4 - 2' ) THEN
2079: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-2 ****** ');
2080: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2081: ( Select ms.transaction_id
2082: from MSC_SUPPLIES ms,
2083: MSC_PQ_RESULTS mpr,
2084: MSC_EXCEPTION_DETAILS med
2085: ,MSC_PLAN_QUERIES mpq,
2086: MSC_PERSONAL_QUERIES mpers
2107:
2108:
2109: ElsIF (V_TAB(x) = '4 - 3' ) THEN
2110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-3 ****** ');
2111: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2112: ( Select ms.transaction_id
2113: from MSC_SUPPLIES ms,
2114: MSC_PQ_RESULTS mpr,
2115: MSC_EXCEPTION_DETAILS med,
2109: ElsIF (V_TAB(x) = '4 - 3' ) THEN
2110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-3 ****** ');
2111: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2112: ( Select ms.transaction_id
2113: from MSC_SUPPLIES ms,
2114: MSC_PQ_RESULTS mpr,
2115: MSC_EXCEPTION_DETAILS med,
2116: MSC_ITEM_SUBSTITUTES mis
2117: ,MSC_PLAN_QUERIES mpq,
2141: );
2142:
2143: ElsIF (V_TAB(x) = '4 - 4' ) THEN
2144: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-4 ****** ');
2145: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2146: (Select ms.transaction_id
2147: from MSC_SUPPLIES ms,
2148: MSC_PQ_RESULTS mpr,
2149: MSC_EXCEPTION_DETAILS med,
2143: ElsIF (V_TAB(x) = '4 - 4' ) THEN
2144: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-4 ****** ');
2145: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2146: (Select ms.transaction_id
2147: from MSC_SUPPLIES ms,
2148: MSC_PQ_RESULTS mpr,
2149: MSC_EXCEPTION_DETAILS med,
2150: MSC_ITEM_SUBSTITUTES mis
2151: ,MSC_PLAN_QUERIES mpq,
2178: ---*********************** Query Type = Order ***********************
2179:
2180: ElsIF (V_TAB(x) = '9 - 1' ) THEN
2181: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-1 ****** ');
2182: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2183: (Select ms.transaction_id
2184: from MSC_SUPPLIES ms,
2185: MSC_PQ_RESULTS mpr,
2186: MSC_SUPPLIES ms2
2180: ElsIF (V_TAB(x) = '9 - 1' ) THEN
2181: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-1 ****** ');
2182: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2183: (Select ms.transaction_id
2184: from MSC_SUPPLIES ms,
2185: MSC_PQ_RESULTS mpr,
2186: MSC_SUPPLIES ms2
2187: ,MSC_PLAN_QUERIES mpq,
2188: MSC_PERSONAL_QUERIES mpers
2182: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2183: (Select ms.transaction_id
2184: from MSC_SUPPLIES ms,
2185: MSC_PQ_RESULTS mpr,
2186: MSC_SUPPLIES ms2
2187: ,MSC_PLAN_QUERIES mpq,
2188: MSC_PERSONAL_QUERIES mpers
2189: where mpr.plan_id = pPlan_id
2190: and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2186: MSC_SUPPLIES ms2
2187: ,MSC_PLAN_QUERIES mpq,
2188: MSC_PERSONAL_QUERIES mpers
2189: where mpr.plan_id = pPlan_id
2190: and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2191: and ms2.ROWID = mpr.row_id
2192: and mpr.plan_id= ms2.plan_id
2193: and ms.plan_id= ms2.plan_id
2194: and ms.sr_instance_id = ms2.sr_instance_id
2200: AND mpr.query_id = mpq.query_id
2201: and Mpq.Applied_to = 1
2202: union all
2203: Select ms.transaction_id
2204: from MSC_SUPPLIES ms,
2205: MSC_PQ_RESULTS mpr,
2206: MSC_DEMANDS md
2207: ,MSC_PLAN_QUERIES mpq,
2208: MSC_PERSONAL_QUERIES mpers
2224:
2225:
2226: ElsIF (V_TAB(x) = '9 - 2' ) THEN
2227: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-2 ****** ');
2228: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2229: (Select ms.transaction_id
2230: from MSC_SUPPLIES ms,
2231: MSC_PQ_RESULTS mpr,
2232: MSC_SUPPLIES ms2
2226: ElsIF (V_TAB(x) = '9 - 2' ) THEN
2227: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-2 ****** ');
2228: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2229: (Select ms.transaction_id
2230: from MSC_SUPPLIES ms,
2231: MSC_PQ_RESULTS mpr,
2232: MSC_SUPPLIES ms2
2233: ,MSC_PLAN_QUERIES mpq,
2234: MSC_PERSONAL_QUERIES mpers
2228: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2229: (Select ms.transaction_id
2230: from MSC_SUPPLIES ms,
2231: MSC_PQ_RESULTS mpr,
2232: MSC_SUPPLIES ms2
2233: ,MSC_PLAN_QUERIES mpq,
2234: MSC_PERSONAL_QUERIES mpers
2235: where mpr.plan_id = pPlan_id
2236: and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2232: MSC_SUPPLIES ms2
2233: ,MSC_PLAN_QUERIES mpq,
2234: MSC_PERSONAL_QUERIES mpers
2235: where mpr.plan_id = pPlan_id
2236: and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2237: and ms2.ROWID = mpr.row_id
2238: and mpr.plan_id= ms2.plan_id
2239: and mpr.sr_instance_id= ms2.sr_instance_id
2240: and ms.plan_id= ms2.plan_id
2249: AND mpr.query_id = mpq.query_id
2250: and Mpq.Applied_to = 2
2251: union all
2252: Select ms.transaction_id
2253: from MSC_SUPPLIES ms,
2254: MSC_PQ_RESULTS mpr,
2255: MSC_DEMANDS md
2256: ,MSC_PLAN_QUERIES mpq,
2257: MSC_PERSONAL_QUERIES mpers
2275:
2276:
2277: ElsIF (V_TAB(x) = '9 - 3' ) THEN
2278: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-3 ****** ');
2279: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2280: (Select ms.transaction_id
2281: from MSC_SUPPLIES ms,
2282: MSC_PQ_RESULTS mpr,
2283: MSC_SUPPLIES ms2,
2277: ElsIF (V_TAB(x) = '9 - 3' ) THEN
2278: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-3 ****** ');
2279: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2280: (Select ms.transaction_id
2281: from MSC_SUPPLIES ms,
2282: MSC_PQ_RESULTS mpr,
2283: MSC_SUPPLIES ms2,
2284: MSC_ITEM_SUBSTITUTES mis
2285: ,MSC_PLAN_QUERIES mpq,
2279: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2280: (Select ms.transaction_id
2281: from MSC_SUPPLIES ms,
2282: MSC_PQ_RESULTS mpr,
2283: MSC_SUPPLIES ms2,
2284: MSC_ITEM_SUBSTITUTES mis
2285: ,MSC_PLAN_QUERIES mpq,
2286: MSC_PERSONAL_QUERIES mpers
2287: where mpr.plan_id = pPlan_id
2284: MSC_ITEM_SUBSTITUTES mis
2285: ,MSC_PLAN_QUERIES mpq,
2286: MSC_PERSONAL_QUERIES mpers
2287: where mpr.plan_id = pPlan_id
2288: and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2289: and ms2.ROWID = mpr.row_id
2290: and mpr.plan_id= ms2.plan_id
2291: and mpr.sr_instance_id= ms2.sr_instance_id
2292: and (ms2.inventory_item_id = mis.lower_item_id OR ms2.inventory_item_id = mis.higher_item_id )
2305: AND mpr.query_id = mpq.query_id
2306: and Mpq.Applied_to = 3
2307: union all
2308: Select ms.transaction_id
2309: from MSC_SUPPLIES ms,
2310: MSC_PQ_RESULTS mpr,
2311: MSC_DEMANDS md,
2312: MSC_ITEM_SUBSTITUTES mis
2313: ,MSC_PLAN_QUERIES mpq,
2335: ) ;
2336:
2337: ElsIF (V_TAB(x) = '9 - 4' ) THEN
2338: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-4 ****** ');
2339: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2340: (Select ms.transaction_id
2341: from MSC_SUPPLIES ms,
2342: MSC_PQ_RESULTS mpr,
2343: MSC_SUPPLIES ms2,
2337: ElsIF (V_TAB(x) = '9 - 4' ) THEN
2338: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-4 ****** ');
2339: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2340: (Select ms.transaction_id
2341: from MSC_SUPPLIES ms,
2342: MSC_PQ_RESULTS mpr,
2343: MSC_SUPPLIES ms2,
2344: MSC_ITEM_SUBSTITUTES mis
2345: ,MSC_PLAN_QUERIES mpq,
2339: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2340: (Select ms.transaction_id
2341: from MSC_SUPPLIES ms,
2342: MSC_PQ_RESULTS mpr,
2343: MSC_SUPPLIES ms2,
2344: MSC_ITEM_SUBSTITUTES mis
2345: ,MSC_PLAN_QUERIES mpq,
2346: MSC_PERSONAL_QUERIES mpers
2347: where mpr.plan_id = pPlan_id
2344: MSC_ITEM_SUBSTITUTES mis
2345: ,MSC_PLAN_QUERIES mpq,
2346: MSC_PERSONAL_QUERIES mpers
2347: where mpr.plan_id = pPlan_id
2348: and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2349: and ms2.ROWID = mpr.row_id
2350: and mpr.plan_id= ms2.plan_id
2351: and mpr.sr_instance_id= ms2.sr_instance_id
2352: and (ms2.inventory_item_id = mis.lower_item_id OR ms2.inventory_item_id = mis.higher_item_id )
2366: AND mpr.query_id = mpq.query_id
2367: and Mpq.Applied_to = 4
2368: union all
2369: Select ms.transaction_id
2370: from MSC_SUPPLIES ms,
2371: MSC_PQ_RESULTS mpr,
2372: MSC_DEMANDS md,
2373: MSC_ITEM_SUBSTITUTES mis
2374: ,MSC_PLAN_QUERIES mpq,
2398:
2399:
2400: ElsIF (V_TAB(x) = '9 - 5' ) THEN
2401: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-5 ****** ');
2402: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2403: (Select ms.transaction_id
2404: from MSC_SUPPLIES ms,
2405: MSC_PQ_RESULTS mpr
2406: ,MSC_PLAN_QUERIES mpq,
2400: ElsIF (V_TAB(x) = '9 - 5' ) THEN
2401: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-5 ****** ');
2402: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2403: (Select ms.transaction_id
2404: from MSC_SUPPLIES ms,
2405: MSC_PQ_RESULTS mpr
2406: ,MSC_PLAN_QUERIES mpq,
2407: MSC_PERSONAL_QUERIES mpers
2408: where mpr.plan_id = pPlan_id
2405: MSC_PQ_RESULTS mpr
2406: ,MSC_PLAN_QUERIES mpq,
2407: MSC_PERSONAL_QUERIES mpers
2408: where mpr.plan_id = pPlan_id
2409: and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2410: and ms.ROWID = mpr.row_id
2411: and mpr.plan_id= ms.plan_id
2412: and mpr.sr_instance_id= ms.sr_instance_id
2413: and mpr.query_id =Mpq.Query_id
2423: ---*********************** Query Type = Supplier ***********************
2424:
2425: ElsIF (V_TAB(x) = '5 - 1' ) THEN
2426: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-1 ****** ');
2427: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2428: (select ms.transaction_id
2429: from MSC_SUPPLIES ms,
2430: MSC_PQ_RESULTS mpr,
2431: --MSC_SUPPLIES ms2
2425: ElsIF (V_TAB(x) = '5 - 1' ) THEN
2426: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-1 ****** ');
2427: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2428: (select ms.transaction_id
2429: from MSC_SUPPLIES ms,
2430: MSC_PQ_RESULTS mpr,
2431: --MSC_SUPPLIES ms2
2432: MSC_ITEM_SUPPLIERS ms2
2433: ,MSC_PLAN_QUERIES mpq,
2427: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2428: (select ms.transaction_id
2429: from MSC_SUPPLIES ms,
2430: MSC_PQ_RESULTS mpr,
2431: --MSC_SUPPLIES ms2
2432: MSC_ITEM_SUPPLIERS ms2
2433: ,MSC_PLAN_QUERIES mpq,
2434: MSC_PERSONAL_QUERIES mpers
2435: where mpr.plan_id = pPlan_id
2451: ) ;
2452:
2453: ElsIF (V_TAB(x) = '5 - 2' ) THEN
2454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-2 ****** ');
2455: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2456: (select ms.transaction_id
2457: from MSC_SUPPLIES ms,
2458: MSC_PQ_RESULTS mpr,
2459: --MSC_SUPPLIES ms2
2453: ElsIF (V_TAB(x) = '5 - 2' ) THEN
2454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-2 ****** ');
2455: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2456: (select ms.transaction_id
2457: from MSC_SUPPLIES ms,
2458: MSC_PQ_RESULTS mpr,
2459: --MSC_SUPPLIES ms2
2460: MSC_ITEM_SUPPLIERS ms2
2461: ,MSC_PLAN_QUERIES mpq,
2455: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2456: (select ms.transaction_id
2457: from MSC_SUPPLIES ms,
2458: MSC_PQ_RESULTS mpr,
2459: --MSC_SUPPLIES ms2
2460: MSC_ITEM_SUPPLIERS ms2
2461: ,MSC_PLAN_QUERIES mpq,
2462: MSC_PERSONAL_QUERIES mpers
2463: where mpr.plan_id = pPlan_id
2481: );
2482:
2483: ElsIF (V_TAB(x) = '5 - 3' ) THEN
2484: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-3 ****** ');
2485: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2486: ( select ms.transaction_id
2487: from MSC_SUPPLIES ms,
2488: MSC_PQ_RESULTS mpr,
2489: MSC_ITEM_SUBSTITUTES mis,
2483: ElsIF (V_TAB(x) = '5 - 3' ) THEN
2484: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-3 ****** ');
2485: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2486: ( select ms.transaction_id
2487: from MSC_SUPPLIES ms,
2488: MSC_PQ_RESULTS mpr,
2489: MSC_ITEM_SUBSTITUTES mis,
2490: --MSC_SUPPLIES ms2
2491: MSC_ITEM_SUPPLIERS ms2
2486: ( select ms.transaction_id
2487: from MSC_SUPPLIES ms,
2488: MSC_PQ_RESULTS mpr,
2489: MSC_ITEM_SUBSTITUTES mis,
2490: --MSC_SUPPLIES ms2
2491: MSC_ITEM_SUPPLIERS ms2
2492: ,MSC_PLAN_QUERIES mpq,
2493: MSC_PERSONAL_QUERIES mpers
2494: where mpr.plan_id = pPlan_id
2513: ) ;
2514:
2515: ElsIF (V_TAB(x) = '5 - 4' ) THEN
2516: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-4 ****** ');
2517: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2518: (select ms.transaction_id
2519: from MSC_SUPPLIES ms,
2520: MSC_PQ_RESULTS mpr,
2521: MSC_ITEM_SUBSTITUTES mis,
2515: ElsIF (V_TAB(x) = '5 - 4' ) THEN
2516: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-4 ****** ');
2517: Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2518: (select ms.transaction_id
2519: from MSC_SUPPLIES ms,
2520: MSC_PQ_RESULTS mpr,
2521: MSC_ITEM_SUBSTITUTES mis,
2522: --MSC_SUPPLIES ms2
2523: MSC_ITEM_SUPPLIERS ms2
2518: (select ms.transaction_id
2519: from MSC_SUPPLIES ms,
2520: MSC_PQ_RESULTS mpr,
2521: MSC_ITEM_SUBSTITUTES mis,
2522: --MSC_SUPPLIES ms2
2523: MSC_ITEM_SUPPLIERS ms2
2524: ,MSC_PLAN_QUERIES mpq,
2525: MSC_PERSONAL_QUERIES mpers
2526: where mpr.plan_id = pPlan_id
2554:
2555: IF ( lv_auto_release_method = 3) THEN -- IF Auto Rel method is Release
2556:
2557: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* In IF ****** ');
2558: UPDATE MSC_SUPPLIES
2559: set releasable = 99
2560: where NVL(releasable,-1) = 9999
2561: and plan_id = pPlan_id ;
2562:
2561: and plan_id = pPlan_id ;
2562:
2563: ElsIF( lv_auto_release_method = 4) THEN -- IF Auto Rel method is Block
2564: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* In Else ****** ');
2565: UPDATE MSC_SUPPLIES
2566: set releasable = 99
2567: where NVL(releasable,-1) <> 9999
2568: and plan_id = pPlan_id ;
2569:
2566: set releasable = 99
2567: where NVL(releasable,-1) <> 9999
2568: and plan_id = pPlan_id ;
2569:
2570: UPDATE MSC_SUPPLIES
2571: set releasable = NULL
2572: where NVL(releasable,-1) = 9999
2573: and plan_id = pPlan_id ;
2574: ELSE