[Home] [Help]
515: top_item_name,
516: top_item_name_state,
517: item_id,
518: item_name
519: from msc_analysis_query maq
520: where maq.query_id = p_query_id
521: and ( (p_row_index is not null and maq.row_index = p_row_index) or
522: (p_next_rowset_index is not null and maq.parent_row_index = p_next_rowset_index) )
523: order by row_index;
523: order by row_index;
524:
525: cursor c_next_rowset_index_cur (p_query_id number) is
526: select nvl(max(parent_row_index),0)
527: from msc_analysis_query
528: where query_id = p_query_id;
529:
530: cursor c_child_row_count (p_query_id number, p_next_rowset_index number) is
531: select count(*)
528: where query_id = p_query_id;
529:
530: cursor c_child_row_count (p_query_id number, p_next_rowset_index number) is
531: select count(*)
532: from msc_analysis_query
533: where query_id = p_query_id
534: and parent_row_index = p_next_rowset_index;
535: --------
536: -------- cursors end...
1488: 29,nvl(md.probability,0),
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
1731: cursor rowcount_cur is
1732: select
1733: count(*)
1734: from
1735: msc_analysis_query maq
1736: where maq.query_id = p_query_id
1737: and maq.parent_row_index = g_next_rowset_index;
1738: l_row_count number;
1739:
1758: ms.daily_rate) )) new_quantity,
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
1817: null)) ,1) ,1)) new_quantity,
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
1863: max(mil.max_quantity) new_quantity,
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
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
1906: and maq.parent_row_index = g_next_rowset_index
1933: avg(maa.target_service_level) new_quantity,
1934: 0 old_quantity
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
1974: 0, 1, maa.achieved_service_level_qty2)) new_quantity,
1975: 0 old_quantity
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
2039: null)) ,1) ,1)) new_quantity,
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
2118: null)) ,1) ,1)) new_quantity,
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
2195: null)) ,1) ,1)))) new_quantity,
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,
2258: sum(mfu.consumed_qty) new_quantity,
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,
2318: sum(mfu.consumed_qty) new_quantity,
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,
2375: sum(mfu.consumed_qty) new_quantity,
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,
2444: ms.daily_rate) )) new_quantity,
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,
2515: ms.daily_rate) )) new_quantity,
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,
2583: ms.daily_rate) ))) new_quantity,
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,
2669: sum(mmhv.quantity) new_quantity,
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
2708: sum(ms.new_order_quantity) new_quantity,
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
2746: sum(md.using_requirement_quantity) new_quantity,
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
2783: 235631 -300 21 207 --org_reg-query
2784: 235631 407 21 207 --org_reg-query
2785:
2786: QUERY RIDX PIDX RLID RID OLID INST ORG TOP_ITEM_ID ITEM_ID
2787: ----- ---- ---- ----- ----- ---------- ---- ---- ----------- ---------- -- msc_analysis_query
2788:
2789: 755 1 1 -100 -200 49956 -- allRegions-allOrgs
2790:
2791: 755 2 2 -100 407 -200 49956 -- validRegion-allOrgs
3021: for c_orgs in c_orgs_cur --{
3022: loop
3023: msc_sda_utils.println(' orgs +');
3024: g_row_index := g_row_index +1;
3025: insert into msc_analysis_query
3026: (query_id, row_index, parent_row_index,
3027: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3028: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3029: values (p_query_id, g_row_index, g_next_rowset_index,
3036: for c_item in c_items_cur --{
3037: loop
3038: msc_sda_utils.println(' items +');
3039: g_row_index := g_row_index +1;
3040: insert into msc_analysis_query
3041: (query_id, row_index, parent_row_index,
3042: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3043: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3044: values (p_query_id, g_row_index, g_next_rowset_index,
3133: if (p_action_node = c_region_node) then --{
3134: for c_regs in c_regs_cur --{
3135: loop
3136: g_row_index := g_row_index +1;
3137: insert into msc_analysis_query
3138: (query_id, row_index, parent_row_index,
3139: region_list_id, region_list, region_id, region_code, region_list_state,
3140: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3141: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3150: for c_orgs in c_orgs_cur(l_row.region_id) --{
3151: loop
3152: g_row_index := g_row_index +1;
3153: --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);
3154: insert into msc_analysis_query
3155: (query_id, row_index, parent_row_index,
3156: region_list_id, region_list, region_id, region_code, region_list_state,
3157: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3158: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3167: for c_item in c_items_cur --{
3168: loop
3169: g_row_index := g_row_index +1;
3170: --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);
3171: insert into msc_analysis_query
3172: (query_id, row_index, parent_row_index,
3173: region_list_id, region_list, region_id, region_code, region_list_state,
3174: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3175: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3262: ll_itemlist_action := c_collapsed_state;
3263: msc_sda_utils.println(' item collapsed state ');
3264: end if;
3265:
3266: insert into msc_analysis_query
3267: (query_id, row_index, parent_row_index, org_list_id, org_list, inst_id, org_id, org_code,
3268: top_item_id, top_item_name, item_id, item_name, org_list_state, top_item_name_state)
3269: 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,
3270: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_orglist_action, ll_itemlist_action);
3290: else
3291: ll_itemlist_action := c_collapsed_state;
3292: end if;
3293:
3294: insert into msc_analysis_query
3295: (query_id, row_index, parent_row_index,
3296: region_list_id, region_list, region_id, region_code,region_list_state,
3297: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3298: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3300: c_all_region_type, c_all_region_type_text, to_number(null), null, c_collapsed_state,
3301: c_all_org_type, c_all_org_type_text, to_number(null), to_number(null), null, c_collapsed_state,
3302: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_itemlist_action);
3303:
3304: insert into msc_analysis_query
3305: (query_id, row_index, parent_row_index,
3306: region_list_id, region_list, region_id, region_code,region_list_state,
3307: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3308: top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3615: p_orglist_action number, p_itemlist_action number, p_action_node number,
3616: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3617: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3618: begin
3619: --flushs msc_analysis_query with row_header_information
3620: flushSDRows(g_sd_query_id, p_row_index, p_orglist_action, p_itemlist_action, p_action_node);
3621:
3622: if ( p_orglist_action = c_expanded_state or p_itemlist_action = c_expanded_state ) then
3623: sendRows(c_sdview, g_sd_query_id, null, p_row_index, p_rheader_data);
3641: l_query_id := g_fcst_query_id;
3642: elsif (p_view_type = c_histview) then
3643: l_query_id := g_hist_query_id;
3644: end if;
3645: --flushes msc_analysis_query with row_header_information
3646: flushFcstHistoryRows(p_view_type, l_query_id, p_row_index, p_reglist_action, p_orglist_action, p_itemlist_action, p_action_node);
3647:
3648: if ( p_reglist_action = c_expanded_state or p_orglist_action = c_expanded_state or p_itemlist_action = c_expanded_state ) then
3649: sendRows(p_view_type, l_query_id, null, p_row_index, p_rheader_data);
3656: if ( p_view_type = c_fcstview and p_row_index = c_first_row_index and p_reglist_action = c_expanded_state) then
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,