[Home] [Help]
1915: V_TAB V_TAB_TYPE;
1916: Cursor queries is SELECT Distinct Mpers.query_type||' - '||Mpq.Applied_to
1917: FROM
1918: MSC_PLAN_QUERIES mpq,
1919: MSC_PQ_RESULTS mpr,
1920: MSC_PERSONAL_QUERIES mpers
1921: Where
1922: mpers.query_type in (1,4,5,9)
1923: AND mpq.plan_id = pPlan_id
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
1956: and ms.plan_id = mpr.plan_id
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
1978: and ms.plan_id = mpr.plan_id
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
2001: where mpr.plan_id = pPlan_id
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
2028: where mpr.plan_id = pPlan_id
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
2058: where mpr.plan_id = pPlan_id
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
2087: where mpr.plan_id = pPlan_id
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,
2118: MSC_PERSONAL_QUERIES mpers
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,
2152: MSC_PERSONAL_QUERIES mpers
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
2189: where mpr.plan_id = pPlan_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
2209: where mpr.plan_id = pPlan_id
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
2235: where mpr.plan_id = pPlan_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
2258: where mpr.plan_id = pPlan_id
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,
2286: MSC_PERSONAL_QUERIES mpers
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,
2314: MSC_PERSONAL_QUERIES mpers
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,
2346: MSC_PERSONAL_QUERIES mpers
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,
2375: MSC_PERSONAL_QUERIES mpers
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
2409: and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
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,
2434: MSC_PERSONAL_QUERIES mpers
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,
2462: MSC_PERSONAL_QUERIES mpers
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
2492: ,MSC_PLAN_QUERIES mpq,
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
2524: ,MSC_PLAN_QUERIES mpq,