[Home] [Help]
685: else
686: l_date1 := g_bkt_start_date(bktIndex);
687: l_date2 := g_bkt_end_date(bktIndex);
688: end if;
689: insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
690: values (g_plan_bkts_query_id , sysdate, -1, sysdate, -1, g_bkt_type(bktIndex), l_date1, l_date2);
691: end loop;
692:
693: --get week dates
1442: if l_start_date is null then
1443: l_start_date := g_bkt_start_date(bktIndex);
1444: else
1445: l_end_date := g_bkt_start_date(bktIndex);
1446: insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
1447: values (g_fcst_bkt_mfq_id , sysdate, -1, sysdate, -1, l_bkt_index, l_start_date, l_end_date);
1448: l_start_date := l_end_date;
1449: l_bkt_index := l_bkt_index + 1;
1450: end if;
1450: end if;
1451: end loop;
1452:
1453: if l_end_date is not null then
1454: insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
1455: values (g_fcst_bkt_mfq_id , sysdate, -1, sysdate, -1, l_bkt_index, l_start_date, l_end_date);
1456: end if;
1457: end populate_fcst_bkts_to_mfq;
1458: */
1489: null)) ,1) ,1)) new_quantity
1490: from
1491: msc_demands md,
1492: msc_analysis_query maq,
1493: msc_form_query mfq2, -- item-list
1494: msc_plans mp,
1495: msc_form_query mfq3 --g_plan_bkts_query_id
1496: where maq.query_id = p_query_id
1497: and maq.region_id = c_global_reg_type
1491: msc_demands md,
1492: msc_analysis_query maq,
1493: msc_form_query mfq2, -- item-list
1494: msc_plans mp,
1495: msc_form_query mfq3 --g_plan_bkts_query_id
1496: where maq.query_id = p_query_id
1497: and maq.region_id = c_global_reg_type
1498: and mfq2.query_id = g_chain_query_id
1499: and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
1578: mss.period_start_date,
1579: sum(mss.achieved_days_of_supply) achieved_days_of_supply,
1580: sum(mss.safety_stock_quantity) safety_stock_quantity
1581: from msc_safety_stocks mss,
1582: msc_form_query mfq1, -- org-list
1583: msc_form_query mfq2 -- item-list
1584: where mss.plan_id= g_plan_id
1585: and mfq1.query_id = g_org_query_id
1586: and mfq1.number2 = mss.sr_instance_id
1579: sum(mss.achieved_days_of_supply) achieved_days_of_supply,
1580: sum(mss.safety_stock_quantity) safety_stock_quantity
1581: from msc_safety_stocks mss,
1582: msc_form_query mfq1, -- org-list
1583: msc_form_query mfq2 -- item-list
1584: where mss.plan_id= g_plan_id
1585: and mfq1.query_id = g_org_query_id
1586: and mfq1.number2 = mss.sr_instance_id
1587: and mfq1.number3 = mss.organization_id
1618: date1 bkt_start_date,
1619: date2 bkt_end_date,
1620: to_number(null) achieved_days_of_supply,
1621: to_number(null) safety_stock_quantity
1622: from msc_form_query
1623: where query_id = g_plan_bkts_query_id
1624: order by 1,2;
1625:
1626: lx_plan_id msc_sda_utils.number_arr;
1658: end loop; --}
1659:
1660: forall i in lx_bkt_type.first .. lx_bkt_type.last
1661: --loop
1662: insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by,
1663: number1, number2, number3, number4,
1664: date1, number5, number6)
1665: values (g_ss_query_id , sysdate, -1, sysdate, -1,
1666: lx_plan_id(i), lx_inst_id(i), lx_org_id(i), lx_item_id(i),
1759: sum(nvl(ms.old_order_quantity,0)) old_quantity
1760: from
1761: msc_supplies ms,
1762: msc_analysis_query maq,
1763: msc_form_query mfq1, -- org-list
1764: msc_form_query mfq2, -- item-list
1765: msc_plans mp,
1766: msc_form_query mfq3, --g_plan_bkts_query_id
1767: msc_system_items msi
1760: from
1761: msc_supplies ms,
1762: msc_analysis_query maq,
1763: msc_form_query mfq1, -- org-list
1764: msc_form_query mfq2, -- item-list
1765: msc_plans mp,
1766: msc_form_query mfq3, --g_plan_bkts_query_id
1767: msc_system_items msi
1768: where maq.query_id = p_query_id
1762: msc_analysis_query maq,
1763: msc_form_query mfq1, -- org-list
1764: msc_form_query mfq2, -- item-list
1765: msc_plans mp,
1766: msc_form_query mfq3, --g_plan_bkts_query_id
1767: msc_system_items msi
1768: where maq.query_id = p_query_id
1769: and maq.parent_row_index = g_next_rowset_index
1770: and mfq1.query_id = g_org_query_id
1818: 0 old_quantity
1819: from
1820: msc_demands md,
1821: msc_analysis_query maq,
1822: msc_form_query mfq1, -- org-list
1823: msc_form_query mfq2, -- item-list
1824: msc_plans mp,
1825: msc_form_query mfq3, --g_plan_bkts_query_id
1826: msc_instance_orgs mio,
1819: from
1820: msc_demands md,
1821: msc_analysis_query maq,
1822: msc_form_query mfq1, -- org-list
1823: msc_form_query mfq2, -- item-list
1824: msc_plans mp,
1825: msc_form_query mfq3, --g_plan_bkts_query_id
1826: msc_instance_orgs mio,
1827: msc_system_items msi
1821: msc_analysis_query maq,
1822: msc_form_query mfq1, -- org-list
1823: msc_form_query mfq2, -- item-list
1824: msc_plans mp,
1825: msc_form_query mfq3, --g_plan_bkts_query_id
1826: msc_instance_orgs mio,
1827: msc_system_items msi
1828: where maq.query_id = p_query_id
1829: and maq.parent_row_index = g_next_rowset_index
1864: 0 old_quantity
1865: from
1866: msc_inventory_levels mil,
1867: msc_analysis_query maq,
1868: msc_form_query mfq1, -- org-list
1869: msc_form_query mfq2 -- item-list
1870: where maq.query_id = p_query_id
1871: and maq.parent_row_index = g_next_rowset_index
1872: and mfq1.query_id = g_org_query_id
1865: from
1866: msc_inventory_levels mil,
1867: msc_analysis_query maq,
1868: msc_form_query mfq1, -- org-list
1869: msc_form_query mfq2 -- item-list
1870: where maq.query_id = p_query_id
1871: and maq.parent_row_index = g_next_rowset_index
1872: and mfq1.query_id = g_org_query_id
1873: and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1897: mss.date1 old_date,
1898: sum(mss.number5) new_quantity,
1899: sum(mss.number6) old_quantity
1900: from
1901: msc_form_query mss,
1902: msc_analysis_query maq,
1903: msc_form_query mfq1, -- org-list
1904: msc_form_query mfq2 -- item-list
1905: where maq.query_id = p_query_id
1899: sum(mss.number6) old_quantity
1900: from
1901: msc_form_query mss,
1902: msc_analysis_query maq,
1903: msc_form_query mfq1, -- org-list
1904: msc_form_query mfq2 -- item-list
1905: where maq.query_id = p_query_id
1906: and maq.parent_row_index = g_next_rowset_index
1907: and mfq1.query_id = g_org_query_id
1900: from
1901: msc_form_query mss,
1902: msc_analysis_query maq,
1903: msc_form_query mfq1, -- org-list
1904: msc_form_query mfq2 -- item-list
1905: where maq.query_id = p_query_id
1906: and maq.parent_row_index = g_next_rowset_index
1907: and mfq1.query_id = g_org_query_id
1908: and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1935: from
1936: msc_analysis_aggregate maa,
1937: msc_analysis_query maq,
1938: msc_plan_buckets mpb,
1939: msc_form_query mfq1, -- org-list
1940: msc_form_query mfq2 -- item-list
1941: where maq.query_id = p_query_id
1942: and maq.parent_row_index = g_next_rowset_index
1943: and mfq1.query_id = g_org_query_id
1936: msc_analysis_aggregate maa,
1937: msc_analysis_query maq,
1938: msc_plan_buckets mpb,
1939: msc_form_query mfq1, -- org-list
1940: msc_form_query mfq2 -- item-list
1941: where maq.query_id = p_query_id
1942: and maq.parent_row_index = g_next_rowset_index
1943: and mfq1.query_id = g_org_query_id
1944: and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1976: from
1977: msc_analysis_aggregate maa,
1978: msc_analysis_query maq,
1979: msc_plan_buckets mpb,
1980: msc_form_query mfq1, -- org-list
1981: msc_form_query mfq2 -- item-list
1982: where maq.query_id = p_query_id
1983: and maq.parent_row_index = g_next_rowset_index
1984: and mfq1.query_id = g_org_query_id
1977: msc_analysis_aggregate maa,
1978: msc_analysis_query maq,
1979: msc_plan_buckets mpb,
1980: msc_form_query mfq1, -- org-list
1981: msc_form_query mfq2 -- item-list
1982: where maq.query_id = p_query_id
1983: and maq.parent_row_index = g_next_rowset_index
1984: and mfq1.query_id = g_org_query_id
1985: and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
2040: sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
2041: from
2042: msc_demands md,
2043: msc_analysis_query maq,
2044: msc_form_query mfq1, -- region-to-org-list
2045: msc_form_query mfq2, -- item-list
2046: msc_plans mp,
2047: msc_form_query mfq3 --g_plan_bkts_query_id
2048: where maq.query_id = p_query_id
2041: from
2042: msc_demands md,
2043: msc_analysis_query maq,
2044: msc_form_query mfq1, -- region-to-org-list
2045: msc_form_query mfq2, -- item-list
2046: msc_plans mp,
2047: msc_form_query mfq3 --g_plan_bkts_query_id
2048: where maq.query_id = p_query_id
2049: and maq.parent_row_index = g_next_rowset_index
2043: msc_analysis_query maq,
2044: msc_form_query mfq1, -- region-to-org-list
2045: msc_form_query mfq2, -- item-list
2046: msc_plans mp,
2047: msc_form_query mfq3 --g_plan_bkts_query_id
2048: where maq.query_id = p_query_id
2049: and maq.parent_row_index = g_next_rowset_index
2050: and mfq1.query_id = g_org_query_id
2051: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2119: sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
2120: from
2121: msc_demands md,
2122: msc_analysis_query maq,
2123: msc_form_query mfq1, -- region-to-org-list
2124: msc_form_query mfq2, -- item-list
2125: msc_plans mp,
2126: msc_form_query mfq3 --g_plan_bkts_query_id
2127: where maq.query_id = p_query_id
2120: from
2121: msc_demands md,
2122: msc_analysis_query maq,
2123: msc_form_query mfq1, -- region-to-org-list
2124: msc_form_query mfq2, -- item-list
2125: msc_plans mp,
2126: msc_form_query mfq3 --g_plan_bkts_query_id
2127: where maq.query_id = p_query_id
2128: and maq.parent_row_index = g_next_rowset_index
2122: msc_analysis_query maq,
2123: msc_form_query mfq1, -- region-to-org-list
2124: msc_form_query mfq2, -- item-list
2125: msc_plans mp,
2126: msc_form_query mfq3 --g_plan_bkts_query_id
2127: where maq.query_id = p_query_id
2128: and maq.parent_row_index = g_next_rowset_index
2129: and mfq1.query_id = g_org_query_id
2130: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2196: sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
2197: from
2198: msc_demands md,
2199: msc_analysis_query maq,
2200: msc_form_query mfq1, -- region-to-org-list
2201: msc_form_query mfq2, -- item-list
2202: msc_form_query mfq4, -- msc_demands duplicate rows
2203: msc_plans mp,
2204: msc_form_query mfq3 --g_plan_bkts_query_id
2197: from
2198: msc_demands md,
2199: msc_analysis_query maq,
2200: msc_form_query mfq1, -- region-to-org-list
2201: msc_form_query mfq2, -- item-list
2202: msc_form_query mfq4, -- msc_demands duplicate rows
2203: msc_plans mp,
2204: msc_form_query mfq3 --g_plan_bkts_query_id
2205: where maq.query_id = p_query_id
2198: msc_demands md,
2199: msc_analysis_query maq,
2200: msc_form_query mfq1, -- region-to-org-list
2201: msc_form_query mfq2, -- item-list
2202: msc_form_query mfq4, -- msc_demands duplicate rows
2203: msc_plans mp,
2204: msc_form_query mfq3 --g_plan_bkts_query_id
2205: where maq.query_id = p_query_id
2206: and maq.parent_row_index = g_next_rowset_index
2200: msc_form_query mfq1, -- region-to-org-list
2201: msc_form_query mfq2, -- item-list
2202: msc_form_query mfq4, -- msc_demands duplicate rows
2203: msc_plans mp,
2204: msc_form_query mfq3 --g_plan_bkts_query_id
2205: where maq.query_id = p_query_id
2206: and maq.parent_row_index = g_next_rowset_index
2207: and mfq1.query_id = g_org_query_id
2208: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2259: sum(mfu.overconsumption_qty) old_quantity
2260: from
2261: msc_demands md,
2262: msc_analysis_query maq,
2263: msc_form_query mfq1, -- region-to-org-list
2264: msc_form_query mfq2, -- item-list
2265: msc_plans mp,
2266: msc_forecast_updates mfu,
2267: msc_form_query mfq3 --g_plan_bkts_query_id
2260: from
2261: msc_demands md,
2262: msc_analysis_query maq,
2263: msc_form_query mfq1, -- region-to-org-list
2264: msc_form_query mfq2, -- item-list
2265: msc_plans mp,
2266: msc_forecast_updates mfu,
2267: msc_form_query mfq3 --g_plan_bkts_query_id
2268: where maq.query_id = p_query_id
2263: msc_form_query mfq1, -- region-to-org-list
2264: msc_form_query mfq2, -- item-list
2265: msc_plans mp,
2266: msc_forecast_updates mfu,
2267: msc_form_query mfq3 --g_plan_bkts_query_id
2268: where maq.query_id = p_query_id
2269: and maq.parent_row_index = g_next_rowset_index
2270: and mfq1.query_id = g_org_query_id
2271: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2319: sum(mfu.overconsumption_qty) old_quantity
2320: from
2321: msc_demands md,
2322: msc_analysis_query maq,
2323: msc_form_query mfq1, -- region-to-org-list
2324: msc_form_query mfq2, -- item-list
2325: msc_plans mp,
2326: msc_forecast_updates mfu,
2327: msc_form_query mfq3 --g_plan_bkts_query_id
2320: from
2321: msc_demands md,
2322: msc_analysis_query maq,
2323: msc_form_query mfq1, -- region-to-org-list
2324: msc_form_query mfq2, -- item-list
2325: msc_plans mp,
2326: msc_forecast_updates mfu,
2327: msc_form_query mfq3 --g_plan_bkts_query_id
2328: where maq.query_id = p_query_id
2323: msc_form_query mfq1, -- region-to-org-list
2324: msc_form_query mfq2, -- item-list
2325: msc_plans mp,
2326: msc_forecast_updates mfu,
2327: msc_form_query mfq3 --g_plan_bkts_query_id
2328: where maq.query_id = p_query_id
2329: and maq.parent_row_index = g_next_rowset_index
2330: and mfq1.query_id = g_org_query_id
2331: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2376: sum(mfu.overconsumption_qty) old_quantity
2377: from
2378: msc_demands md,
2379: msc_analysis_query maq,
2380: msc_form_query mfq1, -- region-to-org-list
2381: msc_form_query mfq2, -- item-list
2382: msc_plans mp,
2383: msc_forecast_updates mfu,
2384: msc_form_query mfq3 --g_plan_bkts_query_id
2377: from
2378: msc_demands md,
2379: msc_analysis_query maq,
2380: msc_form_query mfq1, -- region-to-org-list
2381: msc_form_query mfq2, -- item-list
2382: msc_plans mp,
2383: msc_forecast_updates mfu,
2384: msc_form_query mfq3 --g_plan_bkts_query_id
2385: where maq.query_id = p_query_id
2380: msc_form_query mfq1, -- region-to-org-list
2381: msc_form_query mfq2, -- item-list
2382: msc_plans mp,
2383: msc_forecast_updates mfu,
2384: msc_form_query mfq3 --g_plan_bkts_query_id
2385: where maq.query_id = p_query_id
2386: and maq.parent_row_index = g_next_rowset_index
2387: and mfq1.query_id = g_org_query_id
2388: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2445: 0 old_quantity
2446: from
2447: msc_supplies ms,
2448: msc_analysis_query maq,
2449: msc_form_query mfq1, -- region-to-org-list
2450: msc_form_query mfq2, -- item-list
2451: msc_plans mp,
2452: msc_system_items msi,
2453: msc_form_query mfq3 --g_plan_bkts_query_id
2446: from
2447: msc_supplies ms,
2448: msc_analysis_query maq,
2449: msc_form_query mfq1, -- region-to-org-list
2450: msc_form_query mfq2, -- item-list
2451: msc_plans mp,
2452: msc_system_items msi,
2453: msc_form_query mfq3 --g_plan_bkts_query_id
2454: where maq.query_id = p_query_id
2449: msc_form_query mfq1, -- region-to-org-list
2450: msc_form_query mfq2, -- item-list
2451: msc_plans mp,
2452: msc_system_items msi,
2453: msc_form_query mfq3 --g_plan_bkts_query_id
2454: where maq.query_id = p_query_id
2455: and maq.parent_row_index = g_next_rowset_index
2456: and mfq1.query_id = g_org_query_id
2457: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2516: 0 old_quantity
2517: from
2518: msc_supplies ms,
2519: msc_analysis_query maq,
2520: msc_form_query mfq1, -- region-to-org-list
2521: msc_form_query mfq2, -- item-list
2522: msc_plans mp,
2523: msc_system_items msi,
2524: msc_form_query mfq3 --g_plan_bkts_query_id
2517: from
2518: msc_supplies ms,
2519: msc_analysis_query maq,
2520: msc_form_query mfq1, -- region-to-org-list
2521: msc_form_query mfq2, -- item-list
2522: msc_plans mp,
2523: msc_system_items msi,
2524: msc_form_query mfq3 --g_plan_bkts_query_id
2525: where maq.query_id = p_query_id
2520: msc_form_query mfq1, -- region-to-org-list
2521: msc_form_query mfq2, -- item-list
2522: msc_plans mp,
2523: msc_system_items msi,
2524: msc_form_query mfq3 --g_plan_bkts_query_id
2525: where maq.query_id = p_query_id
2526: and maq.parent_row_index = g_next_rowset_index
2527: and mfq1.query_id = g_org_query_id
2528: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2584: 0 old_quantity
2585: from
2586: msc_supplies ms,
2587: msc_analysis_query maq,
2588: msc_form_query mfq1, -- region-to-org-list
2589: msc_form_query mfq2, -- item-list
2590: msc_form_query mfq4, -- msc_supplies duplicate rows
2591: msc_plans mp,
2592: msc_system_items msi,
2585: from
2586: msc_supplies ms,
2587: msc_analysis_query maq,
2588: msc_form_query mfq1, -- region-to-org-list
2589: msc_form_query mfq2, -- item-list
2590: msc_form_query mfq4, -- msc_supplies duplicate rows
2591: msc_plans mp,
2592: msc_system_items msi,
2593: msc_form_query mfq3 --g_plan_bkts_query_id
2586: msc_supplies ms,
2587: msc_analysis_query maq,
2588: msc_form_query mfq1, -- region-to-org-list
2589: msc_form_query mfq2, -- item-list
2590: msc_form_query mfq4, -- msc_supplies duplicate rows
2591: msc_plans mp,
2592: msc_system_items msi,
2593: msc_form_query mfq3 --g_plan_bkts_query_id
2594: where maq.query_id = p_query_id
2589: msc_form_query mfq2, -- item-list
2590: msc_form_query mfq4, -- msc_supplies duplicate rows
2591: msc_plans mp,
2592: msc_system_items msi,
2593: msc_form_query mfq3 --g_plan_bkts_query_id
2594: where maq.query_id = p_query_id
2595: and maq.parent_row_index = g_next_rowset_index
2596: and mfq1.query_id = g_org_query_id
2597: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2637: order by 1;
2638:
2639: cursor hist_min_max_dates is
2640: select min(date1), max(date2)
2641: from msc_form_query
2642: where query_id = g_hist_cal_query_id;
2643:
2644: l_min_date date;
2645: l_max_date date;
2645: l_max_date date;
2646:
2647: cursor hist_bucket_dates is
2648: select date1, date2
2649: from msc_form_query
2650: where query_id = g_hist_cal_query_id
2651: order by 1;
2652:
2653: ll_bkt_start_date msc_sda_utils.date_arr;
2670: 0 old_quantity
2671: from
2672: msc_msd_history_v mmhv,
2673: msc_analysis_query maq,
2674: msc_form_query mfq1, -- region-to-org-list
2675: msc_form_query mfq2, -- item-list
2676: msc_form_query mfq3 -- history calendar
2677: where maq.query_id = p_query_id
2678: and maq.parent_row_index = g_next_rowset_index
2671: from
2672: msc_msd_history_v mmhv,
2673: msc_analysis_query maq,
2674: msc_form_query mfq1, -- region-to-org-list
2675: msc_form_query mfq2, -- item-list
2676: msc_form_query mfq3 -- history calendar
2677: where maq.query_id = p_query_id
2678: and maq.parent_row_index = g_next_rowset_index
2679: and mfq1.query_id = g_org_query_id
2672: msc_msd_history_v mmhv,
2673: msc_analysis_query maq,
2674: msc_form_query mfq1, -- region-to-org-list
2675: msc_form_query mfq2, -- item-list
2676: msc_form_query mfq3 -- history calendar
2677: where maq.query_id = p_query_id
2678: and maq.parent_row_index = g_next_rowset_index
2679: and mfq1.query_id = g_org_query_id
2680: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2709: 0 old_quantity
2710: from
2711: msc_supplies ms,
2712: msc_analysis_query maq,
2713: msc_form_query mfq1, -- region-to-org-list
2714: msc_form_query mfq2, -- item-list
2715: msc_form_query mfq3 -- history calendar
2716: where maq.query_id = p_query_id
2717: and maq.parent_row_index = g_next_rowset_index
2710: from
2711: msc_supplies ms,
2712: msc_analysis_query maq,
2713: msc_form_query mfq1, -- region-to-org-list
2714: msc_form_query mfq2, -- item-list
2715: msc_form_query mfq3 -- history calendar
2716: where maq.query_id = p_query_id
2717: and maq.parent_row_index = g_next_rowset_index
2718: and mfq1.query_id = g_org_query_id
2711: msc_supplies ms,
2712: msc_analysis_query maq,
2713: msc_form_query mfq1, -- region-to-org-list
2714: msc_form_query mfq2, -- item-list
2715: msc_form_query mfq3 -- history calendar
2716: where maq.query_id = p_query_id
2717: and maq.parent_row_index = g_next_rowset_index
2718: and mfq1.query_id = g_org_query_id
2719: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2747: 0 old_quantity
2748: from
2749: msc_demands md,
2750: msc_analysis_query maq,
2751: msc_form_query mfq1, -- region-to-org-list
2752: msc_form_query mfq2, -- item-list
2753: msc_form_query mfq3 -- history calendar
2754: where maq.query_id = p_query_id
2755: and maq.parent_row_index = g_next_rowset_index
2748: from
2749: msc_demands md,
2750: msc_analysis_query maq,
2751: msc_form_query mfq1, -- region-to-org-list
2752: msc_form_query mfq2, -- item-list
2753: msc_form_query mfq3 -- history calendar
2754: where maq.query_id = p_query_id
2755: and maq.parent_row_index = g_next_rowset_index
2756: and mfq1.query_id = g_org_query_id
2749: msc_demands md,
2750: msc_analysis_query maq,
2751: msc_form_query mfq1, -- region-to-org-list
2752: msc_form_query mfq2, -- item-list
2753: msc_form_query mfq3 -- history calendar
2754: where maq.query_id = p_query_id
2755: and maq.parent_row_index = g_next_rowset_index
2756: and mfq1.query_id = g_org_query_id
2757: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2969: mfq.number2 inst_id,
2970: mfq.number3 org_id,
2971: mfq.char4 org_code,
2972: mfq.number4 sort_column
2973: from msc_form_query mfq
2974: where mfq.query_id = g_org_query_id
2975: order by sort_column;
2976:
2977: cursor c_items_cur is
2980: char1 top_item_name,
2981: number2 item_id,
2982: char2 item_name,
2983: number3 sort_column
2984: from msc_form_query
2985: where query_id = g_chain_query_id
2986: order by sort_column desc;
2987:
2988: l_row_check number;
3062: select distinct
3063: mfq.number2 region_id,
3064: mfq.char2 region_code,
3065: mfq.number3 sort_column
3066: from msc_form_query mfq
3067: where mfq.query_id = g_region_query_id
3068: and ( (p_view_type = c_fcstview)
3069: or (p_view_type = c_histview and mfq.number2 <> c_global_reg_type))
3070: order by sort_column desc;
3074: mfq.number2 inst_id,
3075: mfq.number3 org_id,
3076: mfq.char1 org_code,
3077: mfq.number4 sort_column
3078: from msc_form_query mfq
3079: where mfq.query_id = g_org_query_id
3080: and mfq.number3 <> -1
3081: and ( (p_view_type = c_fcstview and nvl(p_region_id, c_mbp_null_value) <> c_local_reg_type)
3082: or (p_view_type = c_fcstview and nvl(p_region_id, c_mbp_null_value) = c_local_reg_type
3090: char1 top_item_name,
3091: number2 item_id,
3092: char2 item_name,
3093: number3 sort_column
3094: from msc_form_query
3095: where query_id = g_chain_query_id
3096: order by sort_column desc;
3097:
3098: l_row_check number;
3322:
3323: --we need to duplicate forecast rows into msc_demands, so we can get manual forecast also from one select in forecast view cursor
3324: g_md_dup_rows_qid := msc_sda_utils.getNewFormQueryId;
3325:
3326: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3327: values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_net_fcst);
3328:
3329: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3330: values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_manual_fcst);
3325:
3326: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3327: values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_net_fcst);
3328:
3329: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3330: values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_manual_fcst);
3331:
3332: --we need to duplicate returns forecast rows into msc_supplies, so we can get returns manual forecast also from one select in forecast view cursor
3333: g_ms_dup_rows_qid := msc_sda_utils.getNewFormQueryId;
3331:
3332: --we need to duplicate returns forecast rows into msc_supplies, so we can get returns manual forecast also from one select in forecast view cursor
3333: g_ms_dup_rows_qid := msc_sda_utils.getNewFormQueryId;
3334:
3335: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3336: values (g_ms_dup_rows_qid, sysdate, -1, -1, sysdate, c_sup2_rtns_fcst, c_sup2_rtns_fcst);
3337:
3338: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3339: values (g_ms_dup_rows_qid, sysdate, -1, -1, sysdate, c_sup2_rtns_fcst, c_sup2_rtns_manual_fcst);
3334:
3335: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3336: values (g_ms_dup_rows_qid, sysdate, -1, -1, sysdate, c_sup2_rtns_fcst, c_sup2_rtns_fcst);
3337:
3338: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3339: values (g_ms_dup_rows_qid, sysdate, -1, -1, sysdate, c_sup2_rtns_fcst, c_sup2_rtns_manual_fcst);
3340:
3341:
3342: end if;
3429: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_bucket_count out nocopy number) is
3430:
3431: cursor c_hist_date_cur is
3432: select date1
3433: from msc_form_query
3434: where query_id = g_hist_cal_query_id
3435: order by 1;
3436:
3437: cursor c_hist_date_count_cur is
3435: order by 1;
3436:
3437: cursor c_hist_date_count_cur is
3438: select count(*)
3439: from msc_form_query
3440: where query_id = g_hist_cal_query_id;
3441: l_count number;
3442: l_out_data_index number := 1;
3443: l_one_record varchar2(2000) := null;
3453: else
3454: l_cal_type := c_owning_org_cal;
3455: end if;
3456:
3457: insert into msc_form_query (query_id, creation_date, created_by,
3458: last_updated_by, last_update_date,
3459: date1, date2, number1)
3460: select g_hist_cal_query_id, sysdate, -1, -1, sysdate, start_date, end_date,1
3461: from (
3657: flushAndSendAddlData(p_view_type, l_query_id, p_out_data2);
3658: end if;
3659:
3660: --delete from msc_analysis_query_temp; insert into msc_analysis_query_temp select * from msc_analysis_query; commit;
3661: --delete from msc_form_query_temp; insert into msc_form_query_temp select * from msc_form_query; commit;
3662: end sendFCSTHistoryData;
3663:
3664: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3665: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3726: procedure getRegionList(p_out_data out nocopy varchar2) is
3727: cursor c_regions_cur is
3728: select distinct
3729: number2 region_id
3730: from msc_form_query
3731: where query_id = g_region_query_id;
3732: begin
3733: for c_regions in c_regions_cur
3734: loop
3743: procedure getOrgList(p_out_data out nocopy varchar2) is
3744: cursor c_orgs_cur is
3745: select distinct
3746: '('||number2||','||number3||')' org_id
3747: from msc_form_query
3748: where query_id = g_org_query_id
3749: and number2 is not null
3750: and number3 is not null;
3751: begin
3762: procedure getItemList(p_out_data out nocopy varchar2) is
3763: cursor c_items_cur is
3764: select distinct
3765: number2 item_id
3766: from msc_form_query
3767: where query_id = g_chain_query_id;
3768: begin
3769: for c_items in c_items_cur
3770: loop
4078: return;
4079: end if;
4080:
4081: p_mfq_id := msc_sda_utils.getNewFormQueryId;
4082: sql_stmt := 'insert into msc_form_query ('||
4083: ' query_id, last_update_date, last_updated_by, creation_date, created_by,number1) '||
4084: ' select distinct '|| p_mfq_id ||', sysdate, 1, sysdate, 1, ';
4085:
4086: if (p_from_table = 'msc_supplies') then