[Home] [Help]
503: top_item_name,
504: top_item_name_state,
505: item_id,
506: item_name
507: from msc_analysis_query maq
508: where maq.query_id = p_query_id
509: and ( (p_row_index is not null and maq.row_index = p_row_index) or
510: (p_next_rowset_index is not null and maq.parent_row_index = p_next_rowset_index) )
511: order by row_index;
511: order by row_index;
512:
513: cursor c_next_rowset_index_cur (p_query_id number) is
514: select nvl(max(parent_row_index),0)
515: from msc_analysis_query
516: where query_id = p_query_id;
517:
518: cursor c_child_row_count (p_query_id number, p_next_rowset_index number) is
519: select count(*)
516: where query_id = p_query_id;
517:
518: cursor c_child_row_count (p_query_id number, p_next_rowset_index number) is
519: select count(*)
520: from msc_analysis_query
521: where query_id = p_query_id
522: and parent_row_index = p_next_rowset_index;
523: --------
524: -------- cursors end...
1434: 29,nvl(md.probability,0),
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
1519: cursor rowcount_cur is
1520: select
1521: count(*)
1522: from
1523: msc_analysis_query maq
1524: where maq.query_id = p_query_id
1525: and maq.parent_row_index = g_next_rowset_index;
1526: l_row_count number;
1527:
1546: ms.daily_rate) )) new_quantity,
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
1605: null)) ,1) ,1)) new_quantity,
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
1651: max(mil.max_quantity) new_quantity,
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
1686: sum(mss.achieved_days_of_supply) new_quantity,
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
1720: avg(maa.target_service_level) new_quantity,
1721: 0 old_quantity
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
1761: 0, 1, maa.achieved_service_level_qty2)) new_quantity,
1762: 0 old_quantity
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
1826: null)) ,1) ,1)) new_quantity,
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
1904: null)) ,1) ,1)) new_quantity,
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
1980: null)) ,1) ,1)) new_quantity,
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
2036: sum(mfu.consumed_qty) new_quantity,
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,
2095: sum(mfu.consumed_qty) new_quantity,
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,
2152: sum(mfu.consumed_qty) new_quantity,
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,
2221: ms.daily_rate) )) new_quantity,
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
2296: sum(mmhv.quantity) new_quantity,
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
2335: sum(ms.new_order_quantity) new_quantity,
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
2373: sum(md.using_requirement_quantity) new_quantity,
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
2410: 235631 -300 21 207 --org_reg-query
2411: 235631 407 21 207 --org_reg-query
2412:
2413: QUERY RIDX PIDX RLID RID OLID INST ORG TOP_ITEM_ID ITEM_ID
2414: ----- ---- ---- ----- ----- ---------- ---- ---- ----------- ---------- -- msc_analysis_query
2415:
2416: 755 1 1 -100 -200 49956 -- allRegions-allOrgs
2417:
2418: 755 2 2 -100 407 -200 49956 -- validRegion-allOrgs
2647: for c_orgs in c_orgs_cur --{
2648: loop
2649: msc_sda_utils.println(' orgs +');
2650: g_row_index := g_row_index +1;
2651: insert into msc_analysis_query
2652: (query_id, row_index, parent_row_index,
2653: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2654: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
2655: values (p_query_id, g_row_index, g_next_rowset_index,
2662: for c_item in c_items_cur --{
2663: loop
2664: msc_sda_utils.println(' items +');
2665: g_row_index := g_row_index +1;
2666: insert into msc_analysis_query
2667: (query_id, row_index, parent_row_index,
2668: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2669: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
2670: values (p_query_id, g_row_index, g_next_rowset_index,
2759: if (p_action_node = c_region_node) then --{
2760: for c_regs in c_regs_cur --{
2761: loop
2762: g_row_index := g_row_index +1;
2763: insert into msc_analysis_query
2764: (query_id, row_index, parent_row_index,
2765: region_list_id, region_list, region_id, region_code, region_list_state,
2766: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2767: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
2776: for c_orgs in c_orgs_cur(l_row.region_id) --{
2777: loop
2778: g_row_index := g_row_index +1;
2779: --msc_sda_utils.println( 'row_index '||g_row_index ||' - '|| c_orgs.org_list_id ||' '|| c_orgs.org_list ||' '|| c_orgs.inst_id ||' '|| c_orgs.org_id ||' '|| c_orgs.org_code ||' '|| c_orgs.sort_column);
2780: insert into msc_analysis_query
2781: (query_id, row_index, parent_row_index,
2782: region_list_id, region_list, region_id, region_code, region_list_state,
2783: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2784: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
2793: for c_item in c_items_cur --{
2794: loop
2795: g_row_index := g_row_index +1;
2796: --msc_sda_utils.println('row_index '||g_row_index ||' - '|| c_item.top_item_id ||' '|| c_item.top_item_name ||' '|| c_item.item_id ||' '|| c_item.item_name);
2797: insert into msc_analysis_query
2798: (query_id, row_index, parent_row_index,
2799: region_list_id, region_list, region_id, region_code, region_list_state,
2800: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2801: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
2888: ll_itemlist_action := c_collapsed_state;
2889: msc_sda_utils.println(' item collapsed state ');
2890: end if;
2891:
2892: insert into msc_analysis_query
2893: (query_id, row_index, parent_row_index, org_list_id, org_list, inst_id, org_id, org_code,
2894: top_item_id, top_item_name, item_id, item_name, org_list_state, top_item_name_state)
2895: values (g_sd_query_id, c_first_row_index, g_next_rowset_index, ll_org_list_id, ll_org_list, ll_inst_id, ll_org_id, ll_org_code,
2896: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_orglist_action, ll_itemlist_action);
2916: else
2917: ll_itemlist_action := c_collapsed_state;
2918: end if;
2919:
2920: insert into msc_analysis_query
2921: (query_id, row_index, parent_row_index,
2922: region_list_id, region_list, region_id, region_code,region_list_state,
2923: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2924: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
2926: c_all_region_type, c_all_region_type_text, to_number(null), null, c_collapsed_state,
2927: c_all_org_type, c_all_org_type_text, to_number(null), to_number(null), null, c_collapsed_state,
2928: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_itemlist_action);
2929:
2930: insert into msc_analysis_query
2931: (query_id, row_index, parent_row_index,
2932: region_list_id, region_list, region_id, region_code,region_list_state,
2933: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2934: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3185: p_orglist_action number, p_itemlist_action number, p_action_node number,
3186: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3187: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3188: begin
3189: --flushs msc_analysis_query with row_header_information
3190: flushSDRows(g_sd_query_id, p_row_index, p_orglist_action, p_itemlist_action, p_action_node);
3191:
3192: if ( p_orglist_action = c_expanded_state or p_itemlist_action = c_expanded_state ) then
3193: sendRows(c_sdview, g_sd_query_id, null, p_row_index, p_rheader_data);
3211: l_query_id := g_fcst_query_id;
3212: elsif (p_view_type = c_histview) then
3213: l_query_id := g_hist_query_id;
3214: end if;
3215: --flushes msc_analysis_query with row_header_information
3216: flushFcstHistoryRows(p_view_type, l_query_id, p_row_index, p_reglist_action, p_orglist_action, p_itemlist_action, p_action_node);
3217:
3218: if ( p_reglist_action = c_expanded_state or p_orglist_action = c_expanded_state or p_itemlist_action = c_expanded_state ) then
3219: sendRows(p_view_type, l_query_id, null, p_row_index, p_rheader_data);
3226: if ( p_view_type = c_fcstview and p_row_index = c_first_row_index and p_reglist_action = c_expanded_state) then
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,