[Home] [Help]
673: else
674: l_date1 := g_bkt_start_date(bktIndex);
675: l_date2 := g_bkt_end_date(bktIndex);
676: end if;
677: insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
678: values (g_plan_bkts_query_id , sysdate, -1, sysdate, -1, g_bkt_type(bktIndex), l_date1, l_date2);
679: end loop;
680:
681: --get week dates
1388: if l_start_date is null then
1389: l_start_date := g_bkt_start_date(bktIndex);
1390: else
1391: l_end_date := g_bkt_start_date(bktIndex);
1392: insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
1393: values (g_fcst_bkt_mfq_id , sysdate, -1, sysdate, -1, l_bkt_index, l_start_date, l_end_date);
1394: l_start_date := l_end_date;
1395: l_bkt_index := l_bkt_index + 1;
1396: end if;
1396: end if;
1397: end loop;
1398:
1399: if l_end_date is not null then
1400: insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
1401: values (g_fcst_bkt_mfq_id , sysdate, -1, sysdate, -1, l_bkt_index, l_start_date, l_end_date);
1402: end if;
1403: end populate_fcst_bkts_to_mfq;
1404: */
1435: null)) ,1) ,1)) new_quantity
1436: from
1437: msc_demands md,
1438: msc_analysis_query maq,
1439: msc_form_query mfq2, -- item-list
1440: msc_plans mp,
1441: msc_form_query mfq3 --g_plan_bkts_query_id
1442: where maq.query_id = p_query_id
1443: and maq.region_id = c_global_reg_type
1437: msc_demands md,
1438: msc_analysis_query maq,
1439: msc_form_query mfq2, -- item-list
1440: msc_plans mp,
1441: msc_form_query mfq3 --g_plan_bkts_query_id
1442: where maq.query_id = p_query_id
1443: and maq.region_id = c_global_reg_type
1444: and mfq2.query_id = g_chain_query_id
1445: and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
1547: sum(nvl(ms.old_order_quantity,0)) old_quantity
1548: from
1549: msc_supplies ms,
1550: msc_analysis_query maq,
1551: msc_form_query mfq1, -- org-list
1552: msc_form_query mfq2, -- item-list
1553: msc_plans mp,
1554: msc_form_query mfq3, --g_plan_bkts_query_id
1555: msc_system_items msi
1548: from
1549: msc_supplies ms,
1550: msc_analysis_query maq,
1551: msc_form_query mfq1, -- org-list
1552: msc_form_query mfq2, -- item-list
1553: msc_plans mp,
1554: msc_form_query mfq3, --g_plan_bkts_query_id
1555: msc_system_items msi
1556: where maq.query_id = p_query_id
1550: msc_analysis_query maq,
1551: msc_form_query mfq1, -- org-list
1552: msc_form_query mfq2, -- item-list
1553: msc_plans mp,
1554: msc_form_query mfq3, --g_plan_bkts_query_id
1555: msc_system_items msi
1556: where maq.query_id = p_query_id
1557: and maq.parent_row_index = g_next_rowset_index
1558: and mfq1.query_id = g_org_query_id
1606: 0 old_quantity
1607: from
1608: msc_demands md,
1609: msc_analysis_query maq,
1610: msc_form_query mfq1, -- org-list
1611: msc_form_query mfq2, -- item-list
1612: msc_plans mp,
1613: msc_form_query mfq3, --g_plan_bkts_query_id
1614: msc_instance_orgs mio,
1607: from
1608: msc_demands md,
1609: msc_analysis_query maq,
1610: msc_form_query mfq1, -- org-list
1611: msc_form_query mfq2, -- item-list
1612: msc_plans mp,
1613: msc_form_query mfq3, --g_plan_bkts_query_id
1614: msc_instance_orgs mio,
1615: msc_system_items msi
1609: msc_analysis_query maq,
1610: msc_form_query mfq1, -- org-list
1611: msc_form_query mfq2, -- item-list
1612: msc_plans mp,
1613: msc_form_query mfq3, --g_plan_bkts_query_id
1614: msc_instance_orgs mio,
1615: msc_system_items msi
1616: where maq.query_id = p_query_id
1617: and maq.parent_row_index = g_next_rowset_index
1652: 0 old_quantity
1653: from
1654: msc_inventory_levels mil,
1655: msc_analysis_query maq,
1656: msc_form_query mfq1, -- org-list
1657: msc_form_query mfq2 -- item-list
1658: where maq.query_id = p_query_id
1659: and maq.parent_row_index = g_next_rowset_index
1660: and mfq1.query_id = g_org_query_id
1653: from
1654: msc_inventory_levels mil,
1655: msc_analysis_query maq,
1656: msc_form_query mfq1, -- org-list
1657: msc_form_query mfq2 -- item-list
1658: where maq.query_id = p_query_id
1659: and maq.parent_row_index = g_next_rowset_index
1660: and mfq1.query_id = g_org_query_id
1661: and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1687: sum(mss.safety_stock_quantity) old_quantity
1688: from
1689: msc_safety_stocks mss,
1690: msc_analysis_query maq,
1691: msc_form_query mfq1, -- org-list
1692: msc_form_query mfq2 -- item-list
1693: where maq.query_id = p_query_id
1694: and maq.parent_row_index = g_next_rowset_index
1695: and mfq1.query_id = g_org_query_id
1688: from
1689: msc_safety_stocks mss,
1690: msc_analysis_query maq,
1691: msc_form_query mfq1, -- org-list
1692: msc_form_query mfq2 -- item-list
1693: where maq.query_id = p_query_id
1694: and maq.parent_row_index = g_next_rowset_index
1695: and mfq1.query_id = g_org_query_id
1696: and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1722: from
1723: msc_analysis_aggregate maa,
1724: msc_analysis_query maq,
1725: msc_plan_buckets mpb,
1726: msc_form_query mfq1, -- org-list
1727: msc_form_query mfq2 -- item-list
1728: where maq.query_id = p_query_id
1729: and maq.parent_row_index = g_next_rowset_index
1730: and mfq1.query_id = g_org_query_id
1723: msc_analysis_aggregate maa,
1724: msc_analysis_query maq,
1725: msc_plan_buckets mpb,
1726: msc_form_query mfq1, -- org-list
1727: msc_form_query mfq2 -- item-list
1728: where maq.query_id = p_query_id
1729: and maq.parent_row_index = g_next_rowset_index
1730: and mfq1.query_id = g_org_query_id
1731: and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1763: from
1764: msc_analysis_aggregate maa,
1765: msc_analysis_query maq,
1766: msc_plan_buckets mpb,
1767: msc_form_query mfq1, -- org-list
1768: msc_form_query mfq2 -- item-list
1769: where maq.query_id = p_query_id
1770: and maq.parent_row_index = g_next_rowset_index
1771: and mfq1.query_id = g_org_query_id
1764: msc_analysis_aggregate maa,
1765: msc_analysis_query maq,
1766: msc_plan_buckets mpb,
1767: msc_form_query mfq1, -- org-list
1768: msc_form_query mfq2 -- item-list
1769: where maq.query_id = p_query_id
1770: and maq.parent_row_index = g_next_rowset_index
1771: and mfq1.query_id = g_org_query_id
1772: and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1827: sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
1828: from
1829: msc_demands md,
1830: msc_analysis_query maq,
1831: msc_form_query mfq1, -- region-to-org-list
1832: msc_form_query mfq2, -- item-list
1833: msc_plans mp,
1834: msc_form_query mfq3 --g_plan_bkts_query_id
1835: where maq.query_id = p_query_id
1828: from
1829: msc_demands md,
1830: msc_analysis_query maq,
1831: msc_form_query mfq1, -- region-to-org-list
1832: msc_form_query mfq2, -- item-list
1833: msc_plans mp,
1834: msc_form_query mfq3 --g_plan_bkts_query_id
1835: where maq.query_id = p_query_id
1836: and maq.parent_row_index = g_next_rowset_index
1830: msc_analysis_query maq,
1831: msc_form_query mfq1, -- region-to-org-list
1832: msc_form_query mfq2, -- item-list
1833: msc_plans mp,
1834: msc_form_query mfq3 --g_plan_bkts_query_id
1835: where maq.query_id = p_query_id
1836: and maq.parent_row_index = g_next_rowset_index
1837: and mfq1.query_id = g_org_query_id
1838: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
1905: sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
1906: from
1907: msc_demands md,
1908: msc_analysis_query maq,
1909: msc_form_query mfq1, -- region-to-org-list
1910: msc_form_query mfq2, -- item-list
1911: msc_plans mp,
1912: msc_form_query mfq3 --g_plan_bkts_query_id
1913: where maq.query_id = p_query_id
1906: from
1907: msc_demands md,
1908: msc_analysis_query maq,
1909: msc_form_query mfq1, -- region-to-org-list
1910: msc_form_query mfq2, -- item-list
1911: msc_plans mp,
1912: msc_form_query mfq3 --g_plan_bkts_query_id
1913: where maq.query_id = p_query_id
1914: and maq.parent_row_index = g_next_rowset_index
1908: msc_analysis_query maq,
1909: msc_form_query mfq1, -- region-to-org-list
1910: msc_form_query mfq2, -- item-list
1911: msc_plans mp,
1912: msc_form_query mfq3 --g_plan_bkts_query_id
1913: where maq.query_id = p_query_id
1914: and maq.parent_row_index = g_next_rowset_index
1915: and mfq1.query_id = g_org_query_id
1916: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
1981: sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
1982: from
1983: msc_demands md,
1984: msc_analysis_query maq,
1985: msc_form_query mfq1, -- region-to-org-list
1986: msc_form_query mfq2, -- item-list
1987: msc_plans mp,
1988: msc_form_query mfq3 --g_plan_bkts_query_id
1989: where maq.query_id = p_query_id
1982: from
1983: msc_demands md,
1984: msc_analysis_query maq,
1985: msc_form_query mfq1, -- region-to-org-list
1986: msc_form_query mfq2, -- item-list
1987: msc_plans mp,
1988: msc_form_query mfq3 --g_plan_bkts_query_id
1989: where maq.query_id = p_query_id
1990: and maq.parent_row_index = g_next_rowset_index
1984: msc_analysis_query maq,
1985: msc_form_query mfq1, -- region-to-org-list
1986: msc_form_query mfq2, -- item-list
1987: msc_plans mp,
1988: msc_form_query mfq3 --g_plan_bkts_query_id
1989: where maq.query_id = p_query_id
1990: and maq.parent_row_index = g_next_rowset_index
1991: and mfq1.query_id = g_org_query_id
1992: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2037: sum(mfu.overconsumption_qty) old_quantity
2038: from
2039: msc_demands md,
2040: msc_analysis_query maq,
2041: msc_form_query mfq1, -- region-to-org-list
2042: msc_form_query mfq2, -- item-list
2043: msc_plans mp,
2044: msc_forecast_updates mfu,
2045: msc_form_query mfq3 --g_plan_bkts_query_id
2038: from
2039: msc_demands md,
2040: msc_analysis_query maq,
2041: msc_form_query mfq1, -- region-to-org-list
2042: msc_form_query mfq2, -- item-list
2043: msc_plans mp,
2044: msc_forecast_updates mfu,
2045: msc_form_query mfq3 --g_plan_bkts_query_id
2046: where maq.query_id = p_query_id
2041: msc_form_query mfq1, -- region-to-org-list
2042: msc_form_query mfq2, -- item-list
2043: msc_plans mp,
2044: msc_forecast_updates mfu,
2045: msc_form_query mfq3 --g_plan_bkts_query_id
2046: where maq.query_id = p_query_id
2047: and maq.parent_row_index = g_next_rowset_index
2048: and mfq1.query_id = g_org_query_id
2049: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2096: sum(mfu.overconsumption_qty) old_quantity
2097: from
2098: msc_demands md,
2099: msc_analysis_query maq,
2100: msc_form_query mfq1, -- region-to-org-list
2101: msc_form_query mfq2, -- item-list
2102: msc_plans mp,
2103: msc_forecast_updates mfu,
2104: msc_form_query mfq3 --g_plan_bkts_query_id
2097: from
2098: msc_demands md,
2099: msc_analysis_query maq,
2100: msc_form_query mfq1, -- region-to-org-list
2101: msc_form_query mfq2, -- item-list
2102: msc_plans mp,
2103: msc_forecast_updates mfu,
2104: msc_form_query mfq3 --g_plan_bkts_query_id
2105: where maq.query_id = p_query_id
2100: msc_form_query mfq1, -- region-to-org-list
2101: msc_form_query mfq2, -- item-list
2102: msc_plans mp,
2103: msc_forecast_updates mfu,
2104: msc_form_query mfq3 --g_plan_bkts_query_id
2105: where maq.query_id = p_query_id
2106: and maq.parent_row_index = g_next_rowset_index
2107: and mfq1.query_id = g_org_query_id
2108: and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2153: sum(mfu.overconsumption_qty) old_quantity
2154: from
2155: msc_demands md,
2156: msc_analysis_query maq,
2157: msc_form_query mfq1, -- region-to-org-list
2158: msc_form_query mfq2, -- item-list
2159: msc_plans mp,
2160: msc_forecast_updates mfu,
2161: msc_form_query mfq3 --g_plan_bkts_query_id
2154: from
2155: msc_demands md,
2156: msc_analysis_query maq,
2157: msc_form_query mfq1, -- region-to-org-list
2158: msc_form_query mfq2, -- item-list
2159: msc_plans mp,
2160: msc_forecast_updates mfu,
2161: msc_form_query mfq3 --g_plan_bkts_query_id
2162: where maq.query_id = p_query_id
2157: msc_form_query mfq1, -- region-to-org-list
2158: msc_form_query mfq2, -- item-list
2159: msc_plans mp,
2160: msc_forecast_updates mfu,
2161: msc_form_query mfq3 --g_plan_bkts_query_id
2162: where maq.query_id = p_query_id
2163: and maq.parent_row_index = g_next_rowset_index
2164: and mfq1.query_id = g_org_query_id
2165: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2222: 0 old_quantity
2223: from
2224: msc_supplies ms,
2225: msc_analysis_query maq,
2226: msc_form_query mfq1, -- region-to-org-list
2227: msc_form_query mfq2, -- item-list
2228: msc_plans mp,
2229: msc_form_query mfq3, --g_plan_bkts_query_id
2230: msc_system_items msi
2223: from
2224: msc_supplies ms,
2225: msc_analysis_query maq,
2226: msc_form_query mfq1, -- region-to-org-list
2227: msc_form_query mfq2, -- item-list
2228: msc_plans mp,
2229: msc_form_query mfq3, --g_plan_bkts_query_id
2230: msc_system_items msi
2231: where maq.query_id = p_query_id
2225: msc_analysis_query maq,
2226: msc_form_query mfq1, -- region-to-org-list
2227: msc_form_query mfq2, -- item-list
2228: msc_plans mp,
2229: msc_form_query mfq3, --g_plan_bkts_query_id
2230: msc_system_items msi
2231: where maq.query_id = p_query_id
2232: and maq.parent_row_index = g_next_rowset_index
2233: and mfq1.query_id = g_org_query_id
2264: order by 1;
2265:
2266: cursor hist_min_max_dates is
2267: select min(date1), max(date2)
2268: from msc_form_query
2269: where query_id = g_hist_cal_query_id;
2270:
2271: l_min_date date;
2272: l_max_date date;
2272: l_max_date date;
2273:
2274: cursor hist_bucket_dates is
2275: select date1, date2
2276: from msc_form_query
2277: where query_id = g_hist_cal_query_id
2278: order by 1;
2279:
2280: ll_bkt_start_date msc_sda_utils.date_arr;
2297: 0 old_quantity
2298: from
2299: msc_msd_history_v mmhv,
2300: msc_analysis_query maq,
2301: msc_form_query mfq1, -- region-to-org-list
2302: msc_form_query mfq2, -- item-list
2303: msc_form_query mfq3 -- history calendar
2304: where maq.query_id = p_query_id
2305: and maq.parent_row_index = g_next_rowset_index
2298: from
2299: msc_msd_history_v mmhv,
2300: msc_analysis_query maq,
2301: msc_form_query mfq1, -- region-to-org-list
2302: msc_form_query mfq2, -- item-list
2303: msc_form_query mfq3 -- history calendar
2304: where maq.query_id = p_query_id
2305: and maq.parent_row_index = g_next_rowset_index
2306: and mfq1.query_id = g_org_query_id
2299: msc_msd_history_v mmhv,
2300: msc_analysis_query maq,
2301: msc_form_query mfq1, -- region-to-org-list
2302: msc_form_query mfq2, -- item-list
2303: msc_form_query mfq3 -- history calendar
2304: where maq.query_id = p_query_id
2305: and maq.parent_row_index = g_next_rowset_index
2306: and mfq1.query_id = g_org_query_id
2307: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2336: 0 old_quantity
2337: from
2338: msc_supplies ms,
2339: msc_analysis_query maq,
2340: msc_form_query mfq1, -- region-to-org-list
2341: msc_form_query mfq2, -- item-list
2342: msc_form_query mfq3 -- history calendar
2343: where maq.query_id = p_query_id
2344: and maq.parent_row_index = g_next_rowset_index
2337: from
2338: msc_supplies ms,
2339: msc_analysis_query maq,
2340: msc_form_query mfq1, -- region-to-org-list
2341: msc_form_query mfq2, -- item-list
2342: msc_form_query mfq3 -- history calendar
2343: where maq.query_id = p_query_id
2344: and maq.parent_row_index = g_next_rowset_index
2345: and mfq1.query_id = g_org_query_id
2338: msc_supplies ms,
2339: msc_analysis_query maq,
2340: msc_form_query mfq1, -- region-to-org-list
2341: msc_form_query mfq2, -- item-list
2342: msc_form_query mfq3 -- history calendar
2343: where maq.query_id = p_query_id
2344: and maq.parent_row_index = g_next_rowset_index
2345: and mfq1.query_id = g_org_query_id
2346: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2374: 0 old_quantity
2375: from
2376: msc_demands md,
2377: msc_analysis_query maq,
2378: msc_form_query mfq1, -- region-to-org-list
2379: msc_form_query mfq2, -- item-list
2380: msc_form_query mfq3 -- history calendar
2381: where maq.query_id = p_query_id
2382: and maq.parent_row_index = g_next_rowset_index
2375: from
2376: msc_demands md,
2377: msc_analysis_query maq,
2378: msc_form_query mfq1, -- region-to-org-list
2379: msc_form_query mfq2, -- item-list
2380: msc_form_query mfq3 -- history calendar
2381: where maq.query_id = p_query_id
2382: and maq.parent_row_index = g_next_rowset_index
2383: and mfq1.query_id = g_org_query_id
2376: msc_demands md,
2377: msc_analysis_query maq,
2378: msc_form_query mfq1, -- region-to-org-list
2379: msc_form_query mfq2, -- item-list
2380: msc_form_query mfq3 -- history calendar
2381: where maq.query_id = p_query_id
2382: and maq.parent_row_index = g_next_rowset_index
2383: and mfq1.query_id = g_org_query_id
2384: and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2595: mfq.number2 inst_id,
2596: mfq.number3 org_id,
2597: mfq.char4 org_code,
2598: mfq.number4 sort_column
2599: from msc_form_query mfq
2600: where mfq.query_id = g_org_query_id
2601: order by sort_column;
2602:
2603: cursor c_items_cur is
2606: char1 top_item_name,
2607: number2 item_id,
2608: char2 item_name,
2609: number3 sort_column
2610: from msc_form_query
2611: where query_id = g_chain_query_id
2612: order by sort_column desc;
2613:
2614: l_row_check number;
2688: select distinct
2689: mfq.number2 region_id,
2690: mfq.char2 region_code,
2691: mfq.number3 sort_column
2692: from msc_form_query mfq
2693: where mfq.query_id = g_region_query_id
2694: and ( (p_view_type = c_fcstview)
2695: or (p_view_type = c_histview and mfq.number2 <> c_global_reg_type))
2696: order by sort_column desc;
2700: mfq.number2 inst_id,
2701: mfq.number3 org_id,
2702: mfq.char1 org_code,
2703: mfq.number4 sort_column
2704: from msc_form_query mfq
2705: where mfq.query_id = g_org_query_id
2706: and mfq.number3 <> -1
2707: and ( (p_view_type = c_fcstview and nvl(p_region_id, c_mbp_null_value) <> c_local_reg_type)
2708: or (p_view_type = c_fcstview and nvl(p_region_id, c_mbp_null_value) = c_local_reg_type
2716: char1 top_item_name,
2717: number2 item_id,
2718: char2 item_name,
2719: number3 sort_column
2720: from msc_form_query
2721: where query_id = g_chain_query_id
2722: order by sort_column desc;
2723:
2724: l_row_check number;
3034: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_bucket_count out nocopy number) is
3035:
3036: cursor c_hist_date_cur is
3037: select date1
3038: from msc_form_query
3039: where query_id = g_hist_cal_query_id
3040: order by 1;
3041:
3042: cursor c_hist_date_count_cur is
3040: order by 1;
3041:
3042: cursor c_hist_date_count_cur is
3043: select count(*)
3044: from msc_form_query
3045: where query_id = g_hist_cal_query_id;
3046: l_count number;
3047: l_out_data_index number := 1;
3048: l_one_record varchar2(2000) := null;
3227: flushAndSendAddlData(p_view_type, l_query_id, p_out_data2);
3228: end if;
3229:
3230: --delete from msc_analysis_query_temp; insert into msc_analysis_query_temp select * from msc_analysis_query; commit;
3231: --delete from msc_form_query_temp; insert into msc_form_query_temp select * from msc_form_query; commit;
3232: end sendFCSTHistoryData;
3233:
3234: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3235: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3296: procedure getRegionList(p_out_data out nocopy varchar2) is
3297: cursor c_regions_cur is
3298: select distinct
3299: number2 region_id
3300: from msc_form_query
3301: where query_id = g_region_query_id;
3302: begin
3303: for c_regions in c_regions_cur
3304: loop
3313: procedure getOrgList(p_out_data out nocopy varchar2) is
3314: cursor c_orgs_cur is
3315: select distinct
3316: '('||number2||','||number3||')' org_id
3317: from msc_form_query
3318: where query_id = g_org_query_id
3319: and number2 is not null
3320: and number3 is not null;
3321: begin
3332: procedure getItemList(p_out_data out nocopy varchar2) is
3333: cursor c_items_cur is
3334: select distinct
3335: number2 item_id
3336: from msc_form_query
3337: where query_id = g_chain_query_id;
3338: begin
3339: for c_items in c_items_cur
3340: loop
3638: return;
3639: end if;
3640:
3641: p_mfq_id := msc_sda_utils.getNewFormQueryId;
3642: sql_stmt := 'insert into msc_form_query ('||
3643: ' query_id, last_update_date, last_updated_by, creation_date, created_by,number1) '||
3644: ' select distinct '|| p_mfq_id ||', sysdate, 1, sysdate, 1, ';
3645:
3646: if (p_from_table = 'msc_supplies') then