[Home] [Help]
PACKAGE BODY: APPS.MSC_SDA_PKG
Source
1 package body MSC_SDA_PKG as
2 /* $Header: MSCSDAPB.pls 120.48.12010000.2 2008/06/03 05:39:14 pabram ship $ */
3
4 --constants
5 c_field_seperator constant varchar2(5) := msc_sda_utils.c_field_seperator;
6 c_record_seperator constant varchar2(5) := msc_sda_utils.c_record_seperator;
7 c_bang_separator constant varchar2(20) := msc_sda_utils.c_bang_separator;
8 c_comma_separator CONSTANT VARCHAR2(20) := msc_sda_utils.c_comma_separator;
9 c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
10 c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
11 c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
12 c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
13 c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
14 c_mbp_not_null_value constant number := msc_sda_utils.c_mbp_not_null_value;
15 c_null_space constant varchar2(1):= msc_sda_utils.c_null_space;
16 c_sys_yes constant integer := msc_sda_utils.c_sys_yes;
17 c_sys_no constant integer := msc_sda_utils.c_sys_no;
18
19
20 c_field_tech_org constant integer := 2;
21
22 --item part condition id and values from msc tables
23 c_part_cond_id constant integer := msc_sda_utils.c_part_cond_id;
24 c_part_good constant integer := msc_sda_utils.c_part_good;
25 c_part_bad constant integer := msc_sda_utils.c_part_bad;
26
27 -- relationship type values in msc_item_substitutes table
28 c_mis_substitute_type constant number := msc_sda_utils.c_mis_substitute_type;
29 c_mis_supersession_type constant number := msc_sda_utils.c_mis_supersession_type;
30 c_mis_repair_to_type constant number := msc_sda_utils.c_mis_repair_to_type;
31 c_mis_service_type constant number := msc_sda_utils.c_mis_service_type;
32
33
34 --forms tokens
35 c_sdview_rowtypes constant varchar2(80) := msc_sda_utils.c_sdview_rowtypes;
36 c_fcstview_rowtypes constant varchar2(80) := msc_sda_utils.c_fcstview_rowtypes;
37 c_histview_rowtypes constant varchar2(80) := msc_sda_utils.c_histview_rowtypes;
38 c_sdview_prefset_data constant varchar2(80) := msc_sda_utils.c_sdview_prefset_data;
39 c_sdview_nls_messages constant varchar2(80) := msc_sda_utils.c_sdview_nls_messages;
40
41 c_sdview_comments_data constant varchar2(80) := msc_sda_utils.c_sdview_comments_data;
42 c_sdview_comments_data_ref constant varchar2(80) := msc_sda_utils.c_sdview_comments_data_ref;
43 c_sdview_items_data constant varchar2(80) := msc_sda_utils.c_sdview_items_data;
44 c_sdview_excp_data constant varchar2(80) := msc_sda_utils.c_sdview_excp_data;
45
46 c_sdview_bucket_data constant varchar2(80) := msc_sda_utils.c_sdview_bucket_data;
47 c_sdview_week_data constant varchar2(80) := msc_sda_utils.c_sdview_week_data;
48 c_sdview_period_data constant varchar2(80) := msc_sda_utils.c_sdview_period_data;
49 c_sdview_rheader_data constant varchar2(80) := msc_sda_utils.c_sdview_rheader_data;
50 c_sdview_data constant varchar2(80) := msc_sda_utils.c_sdview_data;
51
52 c_fcstview_bucket_data constant varchar2(80) := msc_sda_utils.c_fcstview_bucket_data;
53 c_fcstview_week_data constant varchar2(80) := msc_sda_utils.c_fcstview_week_data;
54 c_fcstview_period_data constant varchar2(80) := msc_sda_utils.c_fcstview_period_data;
55 c_fcstview_rheader_data constant varchar2(80) := msc_sda_utils.c_fcstview_rheader_data;
56 c_fcstview_data constant varchar2(80) := msc_sda_utils.c_fcstview_data;
57 c_fcstview_addl_data constant varchar2(80) := msc_sda_utils.c_fcstview_addl_data;
58
59 c_histview_bucket_data constant varchar2(80) := msc_sda_utils.c_histview_bucket_data;
60 c_histview_rheader_data constant varchar2(80) := msc_sda_utils.c_histview_rheader_data;
61 c_histview_data constant varchar2(80) := msc_sda_utils.c_histview_data;
62
63 c_sdview_items_messages constant varchar2(80) := msc_sda_utils.c_sdview_items_messages;
64 c_sdview_comments_messages constant varchar2(80) := msc_sda_utils.c_sdview_comments_messages;
65 c_sdview_excp_messages constant varchar2(80) := msc_sda_utils.c_sdview_excp_messages;
66
67 --msc_demands table will be populated with following values for global region data
68 c_global_reg_id constant number := -1;
69 c_global_inst_id constant number := -1;
70 c_global_org_id constant number := -1;
71
72 c_sda_save_item_folder constant varchar2(50) := msc_sda_utils.c_sda_save_item_folder;
73 c_sda_save_settings constant varchar2(50) := msc_sda_utils.c_sda_save_settings;
74
75 --calendar types to show
76 c_owning_org_cal constant number := 1;
77 c_profile_cal constant number := 2;
78
79 --plan bucket type
80 c_day_bucket constant number := 1;
81 c_week_bucket constant number := 2;
82 c_period_bucket constant number := 3;
83
84 --constants for supply or demand
85 c_supply_type constant number := 1;
86 c_demand_type constant number := 2;
87
88 --constants for view type
89 c_sdview constant number := 1;
90 c_fcstview constant number := 2;
91 c_histview constant number := 3;
92
93 --constants p_region_type
94 c_reg_list_view constant number := msc_sda_utils.c_reg_list_view;
95 c_reg_view constant number := msc_sda_utils.c_reg_view;
96
97 --constants p_org_type
98 c_org_list_view constant number := msc_sda_utils.c_org_list_view;
99 c_org_view constant number := msc_sda_utils.c_org_view;
100
101 --constants p_item_view_type
102 c_item_view constant number := msc_sda_utils.c_item_view;
103 c_prime_view constant number := msc_sda_utils.c_prime_view;
104 c_supersession_view constant number := msc_sda_utils.c_supersession_view;
105
106 --constants for node type
107 c_region_node constant number := 1;
108 c_org_node constant number := 2;
109 c_item_node constant number := 3;
110 c_regionlist_node constant number := 4;
111 c_orglist_node constant number := 5;
112 c_itemlist_node constant number := 6;
113 c_all_regs_node constant number := 7;
114 c_all_orgs_node constant number := 8;
115
116 --constant for regions
117 c_all_region_type constant number := msc_sda_utils.c_all_region_type;
118 c_all_org_type constant number := msc_sda_utils.c_all_org_type;
119 c_global_reg_type constant number := msc_sda_utils.c_global_reg_type;
120 c_local_reg_type constant number := msc_sda_utils.c_local_reg_type;
121
122 c_global_reg_type_text varchar2(300) := msc_sda_utils.c_global_reg_type_text;
123 c_local_reg_type_text varchar2(300) := msc_sda_utils.c_local_reg_type_text;
124 c_all_region_type_text varchar2(300) := msc_sda_utils.c_all_region_type_text;
125 c_all_org_type_text varchar2(300) := msc_sda_utils.c_all_org_type_text;
126
127 --constants for p_souce_type
128 c_msc_supplies constant number := 1;
129 c_msc_demands constant number := 2;
130
131 --constants for p_type_flag
132 c_row_type_flag constant number := 1;
133 c_offset_flag constant number := 2;
134
135 --constants misc
136 c_first_row_index constant number := 1;
137 c_dflt_groupby_num constant number := null;
138 c_dflt_groupby_char constant varchar2(10) := null;
139
140 --constants row-types count
141 c_sd_total_row_types constant number := msc_sda_utils.c_sd_total_row_types;
142 c_fcst_total_row_types constant number := msc_sda_utils.c_fcst_total_row_types;
143 c_hist_total_row_types constant number := msc_sda_utils.c_hist_total_row_types;
144
145 --constants node_state
146 c_expanded_state constant number := 1;
147 c_collapsed_state constant number := 2;
148 c_nodrill_state constant number := 3;
149
150 c_discard constant integer := -99; --supplies which will not be shown
151 c_row_discard constant integer := -99; --supplies which will not be shown
152
153 --
154 -- supply/demand view begins
155 --
156 --supply order types
157 c_sup_intrnst_shpmt constant integer := 11; -- Intransit shipment
158 c_sup_intrnst_rec constant integer := 12; -- Intransit receipt
159 c_sup_onhand constant integer := 18; -- On Hand
160 c_sup_plnd_inbnd_ship constant integer := 51; --Planned Inbound shipment
161 c_sup_new_buy_po constant integer := 1; --New Buy Purchase Order
162 c_sup_new_buy_po_req constant integer := 2; --New Buy Purchase Requisitions
163 c_sup_intrnl_rpr_ordr constant integer := 73; --Internal Repair Order
164 c_sup_xtrnl_rpr_ordr constant integer := 74; --External Repair Order
165 c_sup_rpr_wo constant integer := 75; --Repair Work Order-Internal Depot Org
166 c_sup_plnd_new_buy_ordr constant integer := 76; --Planned New Buy Order
167 c_sup_plnd_intrnl_rpr_ordr constant integer := 77; --Planned Internal Repair Order
168 c_sup_plnd_xtrnl_rpr_ordr constant integer := 78; --Planned External Repair Order
169 c_sup_plnd_rpr_wo constant integer := 79; --Planned Repair Work Order
170 c_sup_rpr_wo_ext_rep_supp constant integer := 86; --Repair Work Order-External Repair Supplier
171 c_sup_ext_rep_req constant integer := 87; --External Repair Requisition
172
173 c_sup_defc_onhand constant integer := 18; --defective on hand (bad)
174 c_sup_defc_returns constant integer := 81; --defective returns (bad)
175 --changed the order type from 32 to 81, bug 6501264
176 --will show returns forecast in returns row type
177 c_sup_defc_inbnd_ship constant integer := c_sup_new_buy_po_req; --Defective Inbound shipment (bad)
178 c_sup_defc_plnd_inbnd_ship constant integer := 51; --planned Defective Inbound shipment (bad)
179 c_sup_defc_transit constant integer := 11; --Defectives in-transit (bad)
180 c_sup_defc_rec constant integer := 12; --Defective in-receiving (bad)
181
182 --supply rowtypes
183 c_srow_intrnst_shpmt constant integer := 10;
184 c_srow_intrnst_rec constant integer := 20;
185 c_srow_onhand constant integer := 30;
186 c_srow_plnd_inbnd_ship constant integer := 40;
187 c_srow_new_buy_po constant integer := 50;
188 c_srow_new_buy_po_req constant integer := 60;
189 c_srow_intrnl_rpr_ordr constant integer := 70;
190 c_srow_xtrnl_rpr_ordr constant integer := 80;
191 c_srow_rpr_wo constant integer := 90;
192 c_srow_plnd_new_buy_ordr constant integer := 100;
193 c_srow_plnd_intrnl_rpr_ordr constant integer := 110;
194 c_srow_plnd_xtrnl_rpr_ordr constant integer := 120;
195 c_srow_plnd_rpr_wo constant integer := 130;
196
197 c_srow_defc_onhand constant integer := 140;
198 c_srow_defc_returns constant integer := 150;
199 c_srow_defc_inbnd_ship constant integer := 160;
200 c_srow_defc_plnd_inbnd_ship constant integer := 170;
201 c_srow_defc_transit constant integer := 180;
202 c_srow_defc_rec constant integer := 190;
203
204 --demand order types
205 c_dmd_pod constant integer := 1; -- Planned order demand
206 c_dmd_so_mds constant integer := 6; -- Sales order MDS
207 c_dmd_manual_mds constant integer := 8; -- Manual MDS
208 c_dmd_mps constant integer := 12; -- MPS demand
209 c_dmd_fcst constant integer := 29; -- Forecast
210 c_dmd_so constant integer := 30; -- Sales Orders
211 c_dmd_defc_iso constant integer := 30; --ISO, bad, defective outbound shipment
212 c_dmd_defc_pod constant integer := 1; --Planned order demand, bad
213 c_dmd_defc_part_dmd constant integer := 77; --Defective Part demand, bad
214 c_dmd_defc_plnd_part_dmd constant integer := 78; --Defective planned Part demand, bad
215
216 --demand rowtypes
217 c_drow_fcst constant integer := 500;
218 c_drow_so constant integer := 510;
219 c_drow_iso_field_org constant integer := 520;
220 c_drow_iso constant integer := 540;
221 c_drow_pod constant integer := 550;
222 c_drow_other_dmd constant integer := 570;
223 c_drow_defc_iso constant integer := 580;
224 c_drow_defc_pod integer := 590;
225 c_drow_defc_part_dmd integer := 600;
226
227 --misc order types
228 c_max_level constant integer := 1000; --max level
229 c_ss_supply constant integer:= 1010; --Safety Stock (Days of Supply)
230 c_ss_level constant integer:= 1020; --Safety Stock Level
231 c_target_level constant integer:= 1030; --Target Level
232
233 -- pivot row types
234 c_row_net_fcst constant integer:= 1;
235 -- Net Forecast, demand-order-types 29
236 c_row_so constant integer:= 2;
237 --Sales Orders, demand-order-types 6,30
238 c_row_iso_field_org constant integer:= 3;
239 --Internal Sales Order (Field Org), demand-order-types 6,30
240 c_row_indepndt_dmd constant integer:= 4;
241 --Independent Demand, sum of pivot-row-types 1,2,3
242 c_row_iso constant integer:= 5;
243 --Internal Sales Order, demand-order-types 6,30
244 c_row_pod constant integer:= 6;
245 --Planned Outbound shipment, (planned order demand) demand-order-types 1
246 c_row_dependnt_dmd constant integer:= 7;
247 --Dependent Demand, sum of pivot-row-types 5,6
248 c_row_other_dmd constant integer:= 8;
249 --Other Demand, demand-order-types 8,12
250 c_row_total_dmd constant integer:= 9;
251 --Total Demand, sum of pivot-row-types 4,7,8
252 c_row_onhand constant integer:= 10;
253 --Beginning On-hand, supply-order-types 18(good)
254 c_row_transit constant integer:= 11;
255 --In - Transit, supply-order-types 11(good)
256 c_row_receiving constant integer:= 12;
257 --In - Receiving, supply-order-types 12(good)
258 c_row_new_buy_po constant integer:= 13;
259 --New Buy Purchase Order, supply-order-types 71(good)
260 c_row_new_buy_po_req constant integer:= 14;
261 --New Buy Purchase Requisitions, supply-order-types 72(good)
262 c_row_intrnl_rpr_ordr constant integer:= 15;
263 --Internal Repair Order, supply-order-types 73(good)
264 c_row_xtrnl_rpr_ordr constant integer:= 16;
265 --External Repair Order, supply-order-types 74(good)
266 --c_row_inbnd_ship constant integer:= 17;
267 --Inbound shipment, supply-order-types 11(good)
268 --same as c_row_transit
269 c_row_rpr_wo constant integer:= 18;
270 --Repair Work Order, supply-order-types 75(good)
271 c_row_plnd_new_buy_ordr constant integer:= 19;
272 --Planned New Buy Order, supply-order-types 76(good)
273 c_row_plnd_intrnl_rpr_ordr constant integer:= 20;
274 --Planned Internal Repair Order, supply-order-types 77(good)
275 c_row_plnd_xtrnl_rpr_ordr constant integer:= 21;
276 --Planned External Repair Order, supply-order-types 78(good)
277 c_row_plnd_inbnd_ship constant integer:= 22;
278 --Planned Inbound shipment, supply-order-types 51(good)
279 c_row_plnd_rpr_wo constant integer:= 23;
280 --Planned Repair Work Order, supply-order-types 79(good)
281 c_row_plnd_warr_ordr constant integer:= 24;
282 --Planned Warranty order, ?pabram....????????
283 c_row_total_supply constant integer:= 25;
284 --Total Supply, sum of pivot-row-types 11 thru 24
285 c_row_ss_supply constant integer:= 26;
286 --Safety Stock (Days of Supply), msc_safety_stocks.achieved_days_of_supply
287 --c_row_total_uncons_dmd constant integer:= 27;
288 --Total unconstrained demand --out of design
289 c_row_ss_level constant integer:= 28;
290 --Safety Stock Level, msc_safety_stocks.achieved_service_level
291 c_row_target_level constant integer:= 29;
292 --Target Level, msc_safety_stocks.target_service_level
293 c_row_max_level constant integer:= 30;
294 --Maximum Level, msc_inventory_levels.max_quantity
295 c_row_pab constant integer:= 31;
296 --Projected Available Balance,
297 --pivot-row-types c_row_pab(i-1) + c_row_total_supply(i) - c_row_total_dmd(i)
298 c_row_poh constant integer:= 32;
299 --Projected On Hand,
300 --pivot-row-types c_row_poh(i-1) + c_row_onhand(i)+ supply-order-type 13 - c_row_total_dmd(i)
301 --pabram..need to verify
302 c_row_defc_iso constant integer:= 33;
303 --Defective Outbound shipment, Defective ISO, demand-order-types 54(bad)
304 c_row_plnd_defc_pod constant integer:= 34;
305 --Planned Defective outbound shipment, (planned order demand) demand-order-types 1(bad)
306 c_row_defc_part_dmd constant integer:= 35;
307 --Defective Part demand, demand-order-types 77(bad)
308 c_row_total_defc_part_dmd constant integer:= 36;
309 --Total Defective Part demand, sum of pivot-row-types c_row_defc_part_dmd
310 --pabram..need to check, this is a duplicate
311 c_row_defc_onhand constant integer:= 37;
312 --Defective On-hand, supply-order-types 18(bad)
313 c_row_returns constant integer:= 38;
314 --Returns, supply-order-types 32(bad)
315 c_row_defc_inbnd_ship constant integer:= 39;
316 --Defective Inbound shipment, supply-order-types 11(bad)
317 c_row_defc_plnd_inbnd_ship constant integer:= 40;
318 --Planned defective Inbound shipment, supply-order-types 51(bad)
319 c_row_defc_transit constant integer:= 41;
320 --Defectives in transit, supply-order-types 11(bad)
321 c_row_defc_rec constant integer:= 42;
322 --Defectives in receiving, supply-order-types 12(bad)
323 c_row_total_defc_supply constant integer:= 43;
324 --Total Defective Supply, pivot-row-types sum of 38 thru 42
325 c_row_defc_pab constant integer:= 44;
326 --Projected Available Balance (Defective)
327 --pivot-row-types c_row_defc_pab(i-1) + c_row_total_defc_supply(i) - ( 33,34,35,36)
328 c_row_ss_qty constant integer:= 45;
329 --safety stock qty
330
331 --
332 -- supply/demand view ends
333 --
334
335 --
336 -- forecast view begins -- pabram need to work on this
337 --
338
339 --demand order types
340 c_dmd2_orig_fcst constant integer := 29; -- original forecast
341 c_dmd2_net_fcst constant integer := 29; -- net forecast
342 c_dmd2_manual_fcst constant integer := 63; -- manual fcst
343 c_dmd2_dmd_schd constant integer := 29; -- demand schedule
344 c_dmd2_bestfit_fcst constant integer := 65; -- bestfit forecast
345 c_dmd2_consm_qty constant integer := -1; -- consumed_qty
346 c_dmd2_overconsm_qty constant integer := -2; -- over consumed_qty
347 c_dmd2_popu_fcst constant integer := 29; -- population based forecast
348 c_dmd2_usage_fcst constant integer := 29; -- usage forecast
349
350 c_sup2_rtns_fcst constant integer := 81; -- Returns Forecast
351 c_sup2_rtns_manual_fcst constant integer := 83; -- Returns Manual Forecast
352 c_sup2_rtns_dmd_schd constant integer := 82; -- Returns Demand Schedule
353 c_sup2_rtns_bestfit_fcst constant integer := 84; -- Returns Best Fit Forecast
354
355 --demand rowtypes
356 c_drow2_net_fcst constant integer := 500;
357 c_drow2_manual_fcst constant integer := 510;
358 c_drow2_dmd_schd constant integer := 520;
359 c_drow2_bestfit_fcst constant integer := 530;
360 c_drow2_consm_qty constant integer := 540;
361 c_drow2_overconsm_qty constant integer := 550;
362 c_drow2_popu_fcst constant integer := 560;
363 c_drow2_usage_fcst constant integer := 570;
364
365 c_drow2_rtns_fcst constant integer := 600;
366 c_drow2_rtns_manual_fcst constant integer := 610;
367 c_drow2_rtns_dmd_schd constant integer := 620;
368 c_drow2_rtns_bestfit_fcst constant integer := 630;
369
370
371 -- pivot row types
372 c_row2_total_fcst constant integer:= 1;
373 -- nvl(manual forecast, net forecast + demand schedule)
374 c_row2_orig_fcst constant integer:= 2;
375 -- msc_demands --old_using_requirement_quantity, old_demand_quantity, original_quantity //pabram..need to check
376 c_row2_consumed_fcst constant integer:= 3;
377 -- msc_forecast_updates.consumed_qty //pabram..need to check
378 c_row2_net_fcst constant integer:= 4;
379 -- msc_demands origination_type 29 //pabram..need to check
380 c_row2_over_consmptn constant integer:= 5;
381 -- msc_forecast_updates.overconsumption_qty //pabram..need to check
382 c_row2_manual_fcst constant integer:= 6;
383 -- msc_demands origination_type 63 //pabram..need to check
384 c_row2_dmd_schd constant integer:= 7;
385 -- msc_demands origination_type 64 //pabram..need to check
386 c_row2_bestfit_fcst constant integer:= 8;
387 -- msc_demands origination_type 65 //pabram..need to check
388 c_row2_total_ret_fcst constant integer:= 9;
389 -- nvl(returns manual forecast, returns fcst + returns dmd schedule) //pabram..need to check
390 c_row2_ret_fcst constant integer:= 10;
391 -- msc_supplies order type 81
392 c_row2_ret_dmd_schd constant integer:= 11;
393 -- msc_supplies order type 82
394 c_row2_ret_manual_fcst constant integer:= 12;
395 -- msc_supplies order type 83
396 c_row2_ret_bestfit_fcst constant integer:= 13;
397 -- msc_supplies order type 84
398 c_row2_usage_fcst constant integer:= 14;
399 -- Usage Forecast
400 c_row2_popultn_fcst constant integer:= 15;
401 -- Population based Forecast
402 c_row2_type_16 constant integer:= 16;
403 -- dummy
404 c_row2_type_17 constant integer:= 17;
405 -- dummy 2
406 --
407 -- forecast view ends
408 --
409
410
411 --
412 -- history view begins -- pabram need to work on this
413 --
414 c_dmd_hist constant integer := 67; -- Demand History
415 c_returns_hist constant integer := 66; -- Returns History
416
417 c_drow_dmd_hist constant integer := 20;
418 c_drow_returns_hist constant integer := 10;
419
420 -- pivot row types
421 c_row_dmd_hist constant integer:= 1;
422 -- demand history ,msc_demands
423 c_row_returns_hist constant integer:= 2;
424 -- returns history ,msc_supplies
425 --
426 -- history view ends
427 --
428
429 -- global
430 g_sd_query_id number;
431 g_fcst_query_id number;
432 g_hist_query_id number;
433 g_hist_cal_query_id number;
434 g_view_type number;
435 g_region_query_id number;
436 g_chain_query_id number;
437 g_org_query_id number;
438 g_plan_bkts_query_id number;
439 g_region_type number;
440 g_region_list varchar2(250);
441 g_region_list_name varchar2(250);
442 g_org_type number;
443 g_org_list varchar2(250);
444 g_item_list number;
445 g_item_list_name varchar2(250);
446 g_item_view_type number;
447 g_row_index number := 1;
448 g_next_rowset_index number := 1;
449 g_fcst_bkt_mfq_id number;
450
451 g_plan_id number;
452 g_plan_type number;
453 g_plan_name varchar2(80);
454 g_owning_inst_id number;
455 g_owning_org_id number;
456 g_plan_start_date date;
457 g_plan_end_date date;
458
459 g_day_buckets number;
460 g_week_buckets number;
461 g_period_buckets number;
462 g_week_start_date date;
463 g_period_start_date date;
464
465 g_bkt_start_date msc_sda_utils.date_arr;
466 g_bkt_end_date msc_sda_utils.date_arr;
467 g_bkt_type msc_sda_utils.number_arr;
468 g_week_start_dates msc_sda_utils.date_arr;
469 g_period_start_dates msc_sda_utils.date_arr;
470
471 g_num_of_buckets number;
472 g_sd_num_of_buckets number;
473 g_fcst_num_of_buckets number;
474 g_hist_num_of_buckets number;
475
476 type bkttype_data is table of varchar2(80) index by binary_integer;
477 type rowtype_data is table of bkttype_data index by binary_integer;
478
479 g_data_grid rowtype_data;
480
481 --preferences
482 g_pref_id number;
483 g_pref_hist_start_date date;
484
485 --------
486 -------- cursors begin
487 --------
488 cursor c_row_values_cur (p_query_id number, p_row_index number, p_next_rowset_index number) is
489 select
490 row_index,
491 region_list_id,
492 region_list,
493 region_list_state,
494 region_id,
495 region_code,
496 org_list_id,
497 org_list,
498 org_list_state,
499 inst_id,
500 org_id,
501 org_code,
502 top_item_id,
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;
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(*)
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...
525 --------
526
527 procedure setUserPrefInfo is
528 begin
529 --pabram..need to change this later based on user pref code/values
530 g_pref_id := -1;
531 g_pref_hist_start_date := sysdate - 700;
532 end setUserPrefInfo;
533
534 procedure setPlanInfo is
535 cursor c_plan_info_cur is
536 select
537 compile_designator,
538 sr_instance_id,
539 organization_id,
540 decode(plan_id, -1, sysdate, trunc(curr_start_date)) curr_start_date,
541 decode(plan_id, -1, sysdate+365, trunc(curr_cutoff_date)) curr_cutoff_date
542 from msc_plans
543 where plan_id = g_plan_id;
544
545 cursor c_plan_bucket_info_cur is
546 select sum(decode(bucket_type, 1, 1,0)) day_buckets,
547 sum(decode(bucket_type, 2, 1,0)) week_buckets,
548 sum(decode(bucket_type, 3, 1,0)) period_buckets,
549 min(decode(bucket_type, 2, bkt_start_date)) week_start_date,
550 min(decode(bucket_type, 3, bkt_start_date)) pr_start_date
551 from msc_plan_buckets
552 where plan_id = g_plan_id
553 and sr_instance_id = g_owning_inst_id
554 and organization_id = g_owning_org_id;
555
556 cursor c_plan_bucket_dates_cur is
557 select bkt_start_date,
558 bkt_end_date,
559 bucket_type
560 from msc_plan_buckets
561 where plan_id = g_plan_id
562 and sr_instance_id = g_owning_inst_id
563 and organization_id = g_owning_org_id
564 union all
565 select trunc(curr_start_date)-1,
566 trunc(curr_start_date)-1,
567 -99
568 from msc_plans
569 where plan_id = g_plan_id
570 union all
571 select trunc(curr_cutoff_date)+1,
572 trunc(curr_cutoff_date)+1,
573 -99
574 from msc_plans
575 where plan_id = g_plan_id
576 order by 1;
577
578 cursor c_week_start_dates_cur(p_cal_type number, p_cal_code varchar2) is
579 select week_start_date
580 from msc_trading_partners mtp,
581 msc_cal_week_start_dates wsd
582 where p_cal_type = c_owning_org_cal
583 and mtp.sr_tp_id = g_owning_org_id
584 and mtp.sr_instance_id = g_owning_inst_id
585 and mtp.partner_type = 3
586 and mtp.calendar_code = wsd.calendar_code
587 and mtp.calendar_exception_set_id = wsd.exception_set_id
588 and mtp.sr_instance_id = wsd.sr_instance_id
589 and wsd.week_start_date >= g_plan_start_date
590 and wsd.week_start_date <= g_plan_end_date
591 union all
592 select mcwsd.week_start_date
593 from msc_cal_week_start_dates mcwsd
594 where p_cal_type = c_profile_cal
595 and mcwsd.calendar_code = p_cal_code
596 and mcwsd.week_start_date >= g_plan_start_date
597 and mcwsd.week_start_date <= g_plan_end_date
598 order by 1;
599
600 cursor c_period_start_dates_cur(p_cal_type number, p_cal_code varchar2) is
601 select mpsd.period_start_date
602 from msc_trading_partners mtp,
603 msc_period_start_dates mpsd
604 where p_cal_type = c_owning_org_cal
605 and mpsd.calendar_code = mtp.calendar_code
606 and mpsd.sr_instance_id = mtp.sr_instance_id
607 and mpsd.exception_set_id = mtp.calendar_exception_set_id
608 and mtp.sr_instance_id = g_owning_inst_id
609 and mtp.sr_tp_id = g_owning_org_id
610 and mtp.partner_type =3
611 and mpsd.period_start_date >= g_plan_start_date
612 and mpsd.period_start_date <= g_plan_end_date
613 union all
614 select mpsd.period_start_date
615 from msc_period_start_dates mpsd
616 where p_cal_type = c_profile_cal
617 and mpsd.calendar_code = p_cal_code
618 and mpsd.period_start_date >= g_plan_start_date
619 and mpsd.period_start_date <= g_plan_end_date
620 order by 1;
621
622 l_cal_code varchar2(250) := fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR');
623 l_cal_type number;
624
625 l_date1 date;
626 l_date2 date;
627
628 begin
629 if (g_view_type = c_sdview) then
630 l_cal_type := c_owning_org_cal;
631 else
632 if (l_cal_code is not null) then
633 l_cal_type := c_profile_cal;
634 else
635 l_cal_type := c_owning_org_cal;
636 end if;
637 end if;
638
639 --get plan info
640 open c_plan_info_cur;
641 fetch c_plan_info_cur into g_plan_name, g_owning_inst_id, g_owning_org_id,
642 g_plan_start_date, g_plan_end_date;
643 close c_plan_info_cur;
644
645 --get plan buckets info
646 open c_plan_bucket_info_cur;
647 fetch c_plan_bucket_info_cur into g_day_buckets, g_week_buckets,
648 g_period_buckets, g_week_start_date, g_period_start_date;
649 close c_plan_bucket_info_cur;
650
651 -- 2 is to store past and future values which are not part of current plan buckets
652 g_num_of_buckets := g_day_buckets + g_week_buckets + g_period_buckets + 1;
653
654 msc_sda_utils.println('setPlanInfo buckets - day week period total '||
655 g_day_buckets ||'-'|| g_week_buckets ||'-'|| g_period_buckets ||'-'|| g_num_of_buckets);
656
657 --get plan bucket dates
658 open c_plan_bucket_dates_cur;
659 fetch c_plan_bucket_dates_cur bulk collect
660 into g_bkt_start_date, g_bkt_end_date, g_bkt_type;
661 close c_plan_bucket_dates_cur;
662
663 --populate plan buckets into mfq
664 g_plan_bkts_query_id := msc_sda_utils.getNewFormQueryId;
665 for bktIndex in 1..g_bkt_type.count
666 loop
667 if ( g_bkt_type(bktIndex) = -99 and trunc(g_bkt_end_date(bktIndex)) = trunc(g_plan_start_date-1) ) then
668 l_date1 := g_plan_start_date - 1000; --approx 3 years from plan start
669 l_date2 := g_plan_start_date -1;
670 elsif ( g_bkt_type(bktIndex) = -99 and trunc(g_bkt_start_date(bktIndex)) = trunc(g_plan_end_date+1) ) then
671 l_date1 := g_plan_end_date + 1;
672 l_date2 := g_plan_end_date + 1000; --approx 3 years from plan end;
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
682 open c_week_start_dates_cur(l_cal_type, l_cal_code);
683 fetch c_week_start_dates_cur bulk collect into g_week_start_dates;
684 close c_week_start_dates_cur;
685
686 --get period dates
687 open c_period_start_dates_cur(l_cal_type, l_cal_code);
688 fetch c_period_start_dates_cur bulk collect into g_period_start_dates;
689 close c_period_start_dates_cur;
690
691 end setPlanInfo;
692
693 function isRowChanged(p_row_index number, p_prev_row_index number) return boolean is
694 l_flag boolean := true;
695 begin
696 if (p_row_index = p_prev_row_index ) then
697 l_flag := false;
698 end if;
699 return l_flag;
700 end isRowChanged;
701
702 function getFcstStartDate(p_bkt_start_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
703 begin
704 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
705 return trunc(p_plan_start_date)-1;
706 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
707 return trunc(p_plan_end_date)+1;
708 end if;
709 return trunc(p_bkt_start_date);
710 end getFcstStartDate;
711
712 function getFcstEndDate(p_bkt_end_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
713 begin
714 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
715 return trunc(p_plan_start_date)-1;
716 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
717 return trunc(p_plan_end_date)+1;
718 end if;
719 return trunc(p_bkt_end_date);
720 end getFcstEndDate;
721
722 function getFcstRowTypeOffset(p_supply_demand_flag number, p_order_type number,
723 p_item_type_id number, p_item_type_value number, p_type_flag number,
724 p_inst_id number, p_org_id number, p_region_id number, p_schedule_designator_id number, p_item_id number) return number is
725 l_row_type number;
726 l_offset number;
727 cursor c_usage_based_fcst_cur is
728 select count(*)
729 from msc_system_items msi,
730 msc_forecast_rules mfr
731 where msi.plan_id = g_plan_id
732 and msi.sr_instance_id = p_inst_id
733 and msi.organization_id = p_org_id
734 and msi.inventory_item_id = p_item_id
735 and msi.forecast_rule_for_demands = mfr.forecast_rule_id
736 and nvl(mfr.enable_usage_ship_fcst, 2) = 1
737 and nvl(mfr.history_basis,-1) in (3,4);
738 l_usage_type number;
739 begin
740 if (p_supply_demand_flag = c_demand_type) then --{
741 if (p_order_type = c_dmd2_dmd_schd and p_schedule_designator_id is not null) then --{
742 l_row_type := c_drow2_dmd_schd;
743 l_offset := c_row2_dmd_schd;
744 elsif (p_order_type = c_dmd2_manual_fcst ) then
745 l_row_type := c_drow2_manual_fcst;
746 l_offset := c_row2_manual_fcst;
747 elsif (p_order_type = c_dmd2_net_fcst) then
748 if (p_region_id is not null) then
749 l_row_type := c_drow2_usage_fcst;
750 l_offset := c_row2_usage_fcst;
751 elsif (p_region_id is null) then
752 open c_usage_based_fcst_cur;
753 fetch c_usage_based_fcst_cur into l_usage_type;
754 close c_usage_based_fcst_cur;
755 if (l_usage_type = 0) then
756 l_row_type := c_drow2_popu_fcst;
757 l_offset := c_row2_popultn_fcst;
758 else
759 l_row_type := c_drow2_usage_fcst;
760 l_offset := c_row2_usage_fcst;
761 end if;
762 else
763 l_row_type := c_drow2_net_fcst;
764 l_offset := c_row2_net_fcst;
765 end if;
766 elsif (p_order_type = c_dmd2_bestfit_fcst) then
767 --removed from the row type
768 l_row_type := c_drow2_bestfit_fcst;
769 l_offset := c_row2_bestfit_fcst;
770 else
771 l_row_type := c_discard;
772 l_offset := c_discard;
773 end if; --}
774 end if; --}
775
776 if (p_supply_demand_flag = c_supply_type) then --{
777 if (p_order_type = c_sup2_rtns_fcst) then --{
778 l_row_type := c_drow2_rtns_fcst;
779 l_offset := c_row2_ret_fcst;
780 elsif (p_order_type = c_sup2_rtns_manual_fcst) then --{
781 l_row_type := c_drow2_rtns_manual_fcst;
782 l_offset := c_row2_ret_manual_fcst;
783 elsif (p_order_type = c_sup2_rtns_dmd_schd) then --{
784 l_row_type := c_drow2_rtns_dmd_schd;
785 l_offset := c_row2_ret_dmd_schd;
786 elsif (p_order_type = c_sup2_rtns_bestfit_fcst) then --{
787 l_row_type := c_drow2_rtns_bestfit_fcst;
788 l_offset := c_row2_ret_bestfit_fcst;
789 else
790 l_row_type := c_discard;
791 l_offset := c_discard;
792 end if; --}
793 end if; --}
794
795 if (p_type_flag = c_row_type_flag) then
796 return l_row_type;
797 else
798 return l_offset;
799 end if;
800 end getFcstRowTypeOffset;
801
802 function getHistStartDate(p_bkt_start_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
803 begin
804 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
805 return trunc(p_plan_start_date)-1;
806 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
807 return trunc(p_plan_end_date)+1;
808 end if;
809 return trunc(p_bkt_start_date);
810 end getHistStartDate;
811
812 function getHistEndDate(p_bkt_end_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
813 begin
814 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
815 return trunc(p_plan_start_date)-1;
816 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
817 return trunc(p_plan_end_date)+1;
818 end if;
819 return trunc(p_bkt_end_date);
820 end getHistEndDate;
821
822 function getHistRowTypeOffset(p_order_type number, p_item_type_id number, p_item_type_value number,
823 p_type_flag number) return number is
824 l_row_type number;
825 l_offset number;
826 begin
827 if (p_order_type = c_returns_hist) then --{
828 l_row_type := c_drow_returns_hist;
829 l_offset := c_row_returns_hist;
830 elsif (p_order_type = c_dmd_hist) then --{
831 l_row_type := c_drow_dmd_hist;
832 l_offset := c_row_dmd_hist;
833 else
834 l_row_type := c_discard;
835 l_offset := c_discard;
836 end if; --}
837 if (p_type_flag = c_row_type_flag) then
838 return l_row_type;
839 else
840 return l_offset;
841 end if;
842 end getHistRowTypeOffset;
843
844 function getSDStartDate(p_bkt_start_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
845 begin
846 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
847 return trunc(p_plan_start_date)-1;
848 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
849 return trunc(p_plan_end_date)+1;
850 end if;
851 return trunc(p_bkt_start_date);
852 end getSDStartDate;
853
854 function getSDEndDate(p_bkt_end_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
855 begin
856 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
857 return trunc(p_plan_start_date)-1;
858 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
859 return trunc(p_plan_end_date)+1;
860 end if;
861 return trunc(p_bkt_end_date);
862 end getSDEndDate;
863
864 function getSupplyRowTypeOffset(p_order_type number, p_item_type_id number, p_item_type_value number,
865 p_type_flag number, p_source_organization_id number) return number is
866 l_row_type number;
867 l_offset number;
868 begin
869 if (p_order_type = c_sup_onhand
870 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then --{
871 l_row_type := c_srow_onhand;
872 l_offset := c_row_onhand;
873 elsif (p_order_type = c_sup_intrnst_shpmt
874 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
875 l_row_type := c_srow_intrnst_shpmt;
876 l_offset := c_row_transit;
877 elsif (p_order_type = c_sup_intrnst_rec
878 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
879 l_row_type := c_srow_intrnst_rec;
880 l_offset := c_row_receiving;
881 elsif (p_order_type = c_sup_new_buy_po
882 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
883 l_row_type := c_srow_new_buy_po;
884 l_offset := c_row_new_buy_po;
885 elsif (p_order_type = c_sup_new_buy_po_req
886 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good) in (c_part_good, c_part_bad) ) then
887 if (p_source_organization_id is null and nvl(p_item_type_value,c_part_good) = c_part_good ) then
888 l_row_type := c_srow_new_buy_po_req;
889 l_offset := c_row_new_buy_po_req;
890 elsif (p_source_organization_id is not null and nvl(p_item_type_value,c_part_good) = c_part_bad ) then
891 l_row_type := c_srow_defc_inbnd_ship;
892 l_offset := c_row_defc_inbnd_ship;
893 else
894 l_row_type := c_discard;
895 l_offset := c_discard;
896 end if;
897 elsif (p_order_type in (c_sup_intrnl_rpr_ordr)
898 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
899 l_row_type := c_srow_intrnl_rpr_ordr;
900 l_offset := c_row_intrnl_rpr_ordr;
901 elsif (p_order_type in (c_sup_xtrnl_rpr_ordr, c_sup_ext_rep_req)
902 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
903 l_row_type := c_srow_xtrnl_rpr_ordr;
904 l_offset := c_row_xtrnl_rpr_ordr;
905 elsif (p_order_type in ( c_sup_rpr_wo, c_sup_rpr_wo_ext_rep_supp)
906 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
907 l_row_type := c_srow_rpr_wo;
908 l_offset := c_row_rpr_wo;
909 elsif (p_order_type = c_sup_plnd_new_buy_ordr
910 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
911 l_row_type := c_srow_plnd_new_buy_ordr;
912 l_offset := c_row_plnd_new_buy_ordr;
913 elsif (p_order_type = c_sup_plnd_intrnl_rpr_ordr
914 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
915 l_row_type := c_srow_plnd_intrnl_rpr_ordr;
916 l_offset := c_row_plnd_intrnl_rpr_ordr;
917 elsif (p_order_type = c_sup_plnd_xtrnl_rpr_ordr
918 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
919 l_row_type := c_srow_plnd_xtrnl_rpr_ordr;
920 l_offset := c_row_plnd_xtrnl_rpr_ordr;
921 elsif (p_order_type = c_sup_plnd_inbnd_ship
922 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
923 l_row_type := c_srow_plnd_inbnd_ship;
924 l_offset := c_row_plnd_inbnd_ship;
925 elsif (p_order_type = c_sup_plnd_rpr_wo
926 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
927 l_row_type := c_srow_plnd_rpr_wo;
928 l_offset := c_row_plnd_rpr_wo;
929 elsif (p_order_type = c_sup_defc_plnd_inbnd_ship
930 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
931 l_row_type := c_srow_defc_plnd_inbnd_ship;
932 l_offset := c_row_defc_plnd_inbnd_ship;
933 elsif (p_order_type = c_sup_defc_onhand
934 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
935 l_row_type := c_srow_defc_onhand;
936 l_offset := c_row_defc_onhand;
937 elsif (p_order_type = c_sup_defc_returns
938 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
939 l_row_type := c_srow_defc_returns;
940 l_offset := c_row_returns;
941 --elsif (p_order_type = c_sup_defc_inbnd_ship
942 --and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
943 --l_row_type := c_srow_defc_inbnd_ship;
944 --l_offset := c_row_defc_inbnd_ship;
945 elsif (p_order_type = c_sup_defc_transit
946 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
947 l_row_type := c_srow_defc_transit;
948 l_offset := c_row_defc_transit;
949 elsif (p_order_type = c_sup_defc_rec
950 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
951 l_row_type := c_srow_defc_rec;
952 l_offset := c_row_defc_rec;
953 else
954 l_row_type := c_discard;
955 l_offset := c_discard;
956 end if; --}
957 if (p_type_flag = c_row_type_flag) then
958 return l_row_type;
959 else
960 return l_offset;
961 end if;
962 end getSupplyRowTypeOffset;
963
964 function getDemandRowTypeOffset(p_order_type number, p_item_type_id number, p_item_type_value number,
965 p_type_flag number, p_disposition_id number, p_org_type number) return number is
966 l_row_type number;
967 l_offset number;
968 begin
969 if (p_order_type = c_dmd_fcst
970 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good) then --{
971 l_row_type := c_drow_fcst;
972 l_offset := c_row_net_fcst;
973 elsif (p_order_type in (c_dmd_so, c_dmd_so_mds)
974 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
975 if (p_disposition_id is null) then
976 l_row_type := c_drow_so;
977 l_offset := c_row_so;
978 else
979 --6737751 bugfix, no dependency
980 if ( nvl(p_org_type,-1) = c_field_tech_org ) then
981 l_row_type := c_drow_iso_field_org;
982 l_offset := c_row_iso_field_org;
983 else
984 l_row_type := c_drow_iso;
985 l_offset := c_row_iso;
986 end if;
987 end if;
988 elsif (p_order_type in (c_dmd_mps, c_dmd_manual_mds)
989 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
990 l_row_type := c_drow_other_dmd;
991 l_offset := c_row_other_dmd;
992 elsif (p_order_type = c_dmd_pod
993 and nvl(p_item_type_id,c_part_cond_id)=c_part_cond_id and nvl(p_item_type_value,c_part_good)=c_part_good ) then
994 l_row_type := c_drow_pod;
995 l_offset := c_row_pod;
996 elsif (p_order_type = c_dmd_defc_iso
997 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
998 l_row_type := c_drow_defc_iso;
999 l_offset := c_row_defc_iso;
1000 elsif (p_order_type = c_dmd_defc_pod
1001 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
1002 l_row_type := c_drow_defc_pod;
1003 l_offset := c_row_plnd_defc_pod;
1004 elsif (p_order_type in (c_dmd_defc_part_dmd, c_dmd_defc_plnd_part_dmd)
1005 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
1006 l_row_type := c_drow_defc_part_dmd;
1007 l_offset := c_row_defc_part_dmd;
1008 else
1009 l_row_type := c_discard;
1010 l_offset := c_discard;
1011 end if; --}
1012 if (p_type_flag = c_row_type_flag) then --{
1013 return l_row_type;
1014 else
1015 return l_offset;
1016 end if; --}
1017 end getDemandRowTypeOffset;
1018
1019 procedure getTotalRowTypesBuckets(p_view_type number,
1020 p_total_row_types out nocopy number, p_total_buckets out nocopy number) is
1021 begin
1022 if (p_view_type = c_sdview) then
1023 p_total_row_types := c_sd_total_row_types;
1024 p_total_buckets := g_sd_num_of_buckets;
1025 elsif (p_view_type = c_fcstview) then
1026 p_total_row_types := c_fcst_total_row_types;
1027 p_total_buckets := g_fcst_num_of_buckets;
1028 elsif (p_view_type = c_histview) then
1029 p_total_row_types := c_hist_total_row_types;
1030 p_total_buckets := g_hist_num_of_buckets;
1031 end if;
1032 end getTotalRowTypesBuckets;
1033
1034 function getAnalysisQueryId(p_view_type number) return number is
1035 l_query_id number;
1036 begin
1037 if (p_view_type = c_sdview) then
1038 l_query_id := g_sd_query_id;
1039 elsif (p_view_type = c_fcstview) then
1040 l_query_id := g_fcst_query_id;
1041 elsif (p_view_type = c_histview) then
1042 l_query_id := g_hist_query_id;
1043 end if;
1044 return l_query_id;
1045 end getAnalysisQueryId;
1046
1047 procedure getDataStreamLabel(p_view_type number, p_stream_name out nocopy varchar2) is
1048 begin
1049 if (p_view_type = c_sdview) then
1050 p_stream_name := c_sdview_data;
1051 elsif (p_view_type = c_fcstview) then
1052 p_stream_name := c_fcstview_data;
1053 elsif (p_view_type = c_histview) then
1054 p_stream_name := c_histview_data;
1055 end if;
1056 end getDataStreamLabel;
1057
1058 procedure initGrid (p_view_type number) is
1059 l_total_row_types number;
1060 l_total_buckets number;
1061 begin
1062 getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1063 -- initialize the bucket to zeros for new item
1064 for rowTypeIndex in 1..l_total_row_types
1065 loop --{
1066 for bktIndex in 1..l_total_buckets
1067 loop
1068 g_data_grid(rowTypeIndex)(bktIndex) := to_number(null);
1069 end loop;
1070 end loop; --}
1071 end initGrid;
1072
1073 procedure addDataToGrid(p_grid_row_index number, p_grid_column_index number, p_qty number,
1074 p_view_type number) is
1075 l_cur_qty number;
1076 l_total_row_types number;
1077 l_total_buckets number;
1078 begin
1079 msc_sda_utils.println(' addDataToGrid p_grid_row_index p_grid_column_index '
1080 || p_grid_row_index ||' - '|| p_grid_column_index );
1081
1082 getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1083
1084 --6606958 there was data beyond plan end_date, stopping that here
1085 if (p_grid_column_index >l_total_buckets) then
1086 return;
1087 end if;
1088 l_cur_qty := g_data_grid(p_grid_row_index)(p_grid_column_index);
1089 if (l_cur_qty is not null or p_qty is not null) then
1090 g_data_grid(p_grid_row_index)(p_grid_column_index) :=
1091 nvl(g_data_grid(p_grid_row_index)(p_grid_column_index), 0) + nvl(p_qty, 0);
1092 end if;
1093 end addDataToGrid;
1094
1095 procedure flushToStream(p_row_index number, p_out_data_index in out nocopy number,
1096 p_out_data in out nocopy msc_sda_utils.maxCharTbl, p_view_type number) is
1097
1098 l_one_record varchar2(32000) := null;
1099 l_total_row_types number;
1100 l_total_buckets number;
1101 l_stream_name varchar2(100);
1102
1103 begin
1104 msc_sda_utils.println('flushToStream in');
1105
1106 getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1107 getDataStreamLabel(p_view_type, l_stream_name);
1108
1109 --prepare the data stream
1110 for bktIndex in 1..l_total_buckets
1111 loop --{
1112 for rowTypeIndex in 1..l_total_row_types
1113 loop
1114 declare
1115 ll_grid_data_char varchar2(200);
1116 begin
1117 ll_grid_data_char := nvl(to_char(fnd_number.number_to_canonical(g_data_grid(rowTypeIndex)(bktIndex))), c_null_space);
1118
1119 if (l_one_record is null) then
1120 l_one_record := p_row_index || c_field_seperator || ll_grid_data_char;
1121 else
1122 if ( nvl(length(l_one_record || c_field_seperator || ll_grid_data_char),0) < 31980 ) then
1123 l_one_record := l_one_record || c_field_seperator || ll_grid_data_char;
1124 end if;
1125 end if;
1126
1127 if ( nvl(length(l_one_record || c_field_seperator || ll_grid_data_char),0) >= 31980
1128 or (rowTypeIndex = l_total_row_types and bktIndex = l_total_buckets) ) then
1129 if (nvl(length(p_out_data(1)),0) = 1) then
1130 l_one_record := l_stream_name || c_bang_separator || c_record_seperator || l_one_record;
1131 else
1132 l_one_record := c_record_seperator || l_one_record;
1133 end if;
1134 msc_sda_utils.addToOutStream(l_one_record, p_out_data_index, p_out_data, 1);
1135 end if;
1136 end;
1137 end loop;
1138 end loop; --}
1139 msc_sda_utils.println('flushToStream out');
1140 end flushToStream;
1141
1142 procedure calculateFcstTotals is
1143 l_total_row_types number;
1144 l_total_buckets number;
1145 l_row2_manual_fcst number;
1146 l_row2_net_fcst number;
1147
1148 l_row2_ret_manual_fcst number;
1149 l_row2_ret_fcst number;
1150 begin
1151 msc_sda_utils.println('calculateFcstTotals in');
1152 getTotalRowTypesBuckets(c_fcstview, l_total_row_types, l_total_buckets);
1153
1154 for bktIndex in 1..l_total_buckets
1155 loop --{
1156
1157 -- calculate totals
1158
1159 -- total forecast
1160 l_row2_manual_fcst := g_data_grid(c_row2_manual_fcst)(bktIndex);
1161 l_row2_net_fcst := nvl(g_data_grid(c_row2_net_fcst)(bktIndex), 0)
1162 + nvl(g_data_grid(c_row2_dmd_schd)(bktIndex), 0)
1163 + nvl(g_data_grid(c_row2_usage_fcst)(bktIndex), 0)
1164 + nvl(g_data_grid(c_row2_popultn_fcst)(bktIndex), 0);
1165
1166 --6904437 bugfix..
1167 g_data_grid(c_row2_net_fcst)(bktIndex) := l_row2_net_fcst;
1168
1169 if ( nvl(l_row2_manual_fcst, l_row2_net_fcst) <> 0 ) then
1170 g_data_grid(c_row2_total_fcst)(bktIndex) := nvl(l_row2_manual_fcst, l_row2_net_fcst);
1171 end if;
1172
1173 -- total returns forecast
1174 l_row2_ret_manual_fcst := g_data_grid(c_row2_ret_manual_fcst)(bktIndex);
1175 l_row2_ret_fcst := nvl(g_data_grid(c_row2_ret_fcst)(bktIndex), 0)
1176 + nvl(g_data_grid(c_row2_ret_dmd_schd)(bktIndex), 0);
1177 if ( nvl(l_row2_ret_manual_fcst, l_row2_ret_fcst) <> 0 ) then
1178 g_data_grid(c_row2_total_ret_fcst)(bktIndex) := nvl(l_row2_ret_manual_fcst, l_row2_ret_fcst);
1179 end if;
1180
1181 end loop; --}
1182 msc_sda_utils.println('calculateFcstTotals out');
1183 end calculateFcstTotals;
1184
1185 procedure calculateSDTotals is
1186 l_total_row_types number;
1187 l_total_buckets number;
1188 l_ss_last_bucket_index number;
1189 begin
1190 msc_sda_utils.println('calculateSDTotals in');
1191
1192 getTotalRowTypesBuckets(c_sdview, l_total_row_types, l_total_buckets);
1193
1194 for bktIndex in 1..l_total_buckets
1195 loop --{
1196
1197 -- calculate totals
1198
1199 -- demand totals
1200
1201 -- Independent Demand -- ISO-field org + Sales orders + Net forecast
1202 g_data_grid(c_row_indepndt_dmd)(bktIndex) :=
1203 nvl(g_data_grid(c_row_net_fcst)(bktIndex), 0)
1204 + nvl(g_data_grid(c_row_so)(bktIndex), 0)
1205 + nvl(g_data_grid(c_row_iso_field_org)(bktIndex), 0);
1206
1207 -- Dependent Demand -- ISO + planned order demand
1208 g_data_grid(c_row_dependnt_dmd)(bktIndex) :=
1209 nvl(g_data_grid(c_row_iso)(bktIndex), 0)
1210 + nvl(g_data_grid(c_row_pod)(bktIndex), 0);
1211
1212 -- Total Demand -- c_row_indepndt_dmd + c_row_dependnt_dmd + c_row_other_dmd
1213 g_data_grid(c_row_total_dmd)(bktIndex) :=
1214 nvl(g_data_grid(c_row_indepndt_dmd)(bktIndex), 0)
1215 + nvl(g_data_grid(c_row_dependnt_dmd)(bktIndex), 0)
1216 + nvl(g_data_grid(c_row_other_dmd)(bktIndex), 0);
1217
1218 -- Total supply -- row-types 11 thru 24
1219 g_data_grid(c_row_total_supply)(bktIndex) :=
1220 nvl(g_data_grid(c_row_transit)(bktIndex), 0)
1221 + nvl(g_data_grid(c_row_receiving)(bktIndex), 0)
1222 + nvl(g_data_grid(c_row_new_buy_po)(bktIndex), 0)
1223 + nvl(g_data_grid(c_row_new_buy_po_req)(bktIndex), 0)
1224 + nvl(g_data_grid(c_row_intrnl_rpr_ordr)(bktIndex), 0)
1225 + nvl(g_data_grid(c_row_xtrnl_rpr_ordr)(bktIndex), 0)
1226 -- + nvl(g_data_grid(c_row_inbnd_ship)(bktIndex), 0)
1227 + nvl(g_data_grid(c_row_rpr_wo)(bktIndex), 0)
1228 + nvl(g_data_grid(c_row_plnd_new_buy_ordr)(bktIndex), 0)
1229 + nvl(g_data_grid(c_row_plnd_intrnl_rpr_ordr)(bktIndex), 0)
1230 + nvl(g_data_grid(c_row_plnd_xtrnl_rpr_ordr)(bktIndex), 0)
1231 + nvl(g_data_grid(c_row_plnd_inbnd_ship)(bktIndex), 0)
1232 + nvl(g_data_grid(c_row_plnd_rpr_wo)(bktIndex), 0)
1233 + nvl(g_data_grid(c_row_plnd_warr_ordr)(bktIndex), 0)
1234 + nvl(g_data_grid(c_row_onhand)(bktIndex), 0);
1235
1236 --Total Defective Part demand - c_row_defc_part_dmd pabram..need to check
1237 g_data_grid(c_row_total_defc_part_dmd)(bktIndex) :=
1238 nvl(g_data_grid(c_row_defc_part_dmd)(bktIndex), 0);
1239
1240 --Total Defective Supply, pivot-row-types sum of 38 thru 42
1241 g_data_grid(c_row_total_defc_supply)(bktIndex) :=
1242 nvl(g_data_grid(c_row_returns)(bktIndex), 0)
1243 + nvl(g_data_grid(c_row_defc_inbnd_ship)(bktIndex), 0)
1244 + nvl(g_data_grid(c_row_defc_plnd_inbnd_ship)(bktIndex), 0)
1245 + nvl(g_data_grid(c_row_defc_transit)(bktIndex), 0)
1246 + nvl(g_data_grid(c_row_defc_rec)(bktIndex), 0)
1247 + nvl(g_data_grid(c_row_defc_onhand)(bktIndex), 0);
1248
1249 --PAB - PAB(i-1) + onhand(i) + total-supply(i) - total-demand(i)
1250 if (bktIndex = 1) then
1251 g_data_grid(c_row_pab)(bktIndex) :=
1252 nvl(g_data_grid(c_row_total_supply)(bktIndex), 0)
1253 - nvl(g_data_grid(c_row_total_dmd)(bktIndex), 0);
1254
1255 -- nvl(g_data_grid(c_row_onhand)(bktIndex), 0) +
1256 --c_row_onhand is already part of c_row_total_supply
1257 --bug 6527725 fix
1258 else
1259 g_data_grid(c_row_pab)(bktIndex) :=
1260 nvl(g_data_grid(c_row_pab)(bktIndex-1), 0)
1261 + nvl(g_data_grid(c_row_total_supply)(bktIndex), 0)
1262 - nvl(g_data_grid(c_row_total_dmd)(bktIndex), 0);
1263
1264 -- + nvl(g_data_grid(c_row_onhand)(bktIndex), 0)
1265 --c_row_onhand is already part of c_row_total_supply
1266 --bug 6527725 fix
1267 end if;
1268
1269 --POH - POH(i-1) + onhand(i) + total-supply-without-planned-order-types(i) - total-demand(i)
1270 if (bktIndex = 1) then
1271 g_data_grid(c_row_poh)(bktIndex) :=
1272 nvl(g_data_grid(c_row_onhand)(bktIndex), 0)
1273 + nvl(g_data_grid(c_row_transit)(bktIndex), 0)
1274 + nvl(g_data_grid(c_row_receiving)(bktIndex), 0)
1275 + nvl(g_data_grid(c_row_new_buy_po)(bktIndex), 0)
1276 + nvl(g_data_grid(c_row_new_buy_po_req)(bktIndex), 0)
1277 + nvl(g_data_grid(c_row_intrnl_rpr_ordr)(bktIndex), 0)
1278 + nvl(g_data_grid(c_row_xtrnl_rpr_ordr)(bktIndex), 0)
1279 -- + nvl(g_data_grid(c_row_inbnd_ship)(bktIndex), 0)
1280 + nvl(g_data_grid(c_row_rpr_wo)(bktIndex), 0)
1281 - nvl(g_data_grid(c_row_total_dmd)(bktIndex), 0);
1282 else
1283 g_data_grid(c_row_poh)(bktIndex) :=
1284 nvl(g_data_grid(c_row_poh)(bktIndex-1), 0)
1285 + nvl(g_data_grid(c_row_onhand)(bktIndex), 0)
1286 + nvl(g_data_grid(c_row_transit)(bktIndex), 0)
1287 + nvl(g_data_grid(c_row_receiving)(bktIndex), 0)
1288 + nvl(g_data_grid(c_row_new_buy_po)(bktIndex), 0)
1289 + nvl(g_data_grid(c_row_new_buy_po_req)(bktIndex), 0)
1290 + nvl(g_data_grid(c_row_intrnl_rpr_ordr)(bktIndex), 0)
1291 + nvl(g_data_grid(c_row_xtrnl_rpr_ordr)(bktIndex), 0)
1292 -- + nvl(g_data_grid(c_row_inbnd_ship)(bktIndex), 0)
1293 + nvl(g_data_grid(c_row_rpr_wo)(bktIndex), 0)
1294 - nvl(g_data_grid(c_row_total_dmd)(bktIndex), 0);
1295 end if;
1296
1297 --PAB (Defective) - pab-defc(i-1) + onhand-defc(i) + total-supply-defc(i)
1298 -- - (c_row_defc_iso + c_row_plnd_defc_pod + c_row_defc_part_dmd + c_row_total_defc_part_dmd),
1299 if (bktIndex = 1) then
1300 g_data_grid(c_row_defc_pab)(bktIndex) :=
1301 nvl(g_data_grid(c_row_total_defc_supply)(bktIndex), 0)
1302 - (nvl(g_data_grid(c_row_defc_iso)(bktIndex), 0)
1303 + nvl(g_data_grid(c_row_plnd_defc_pod)(bktIndex), 0)
1304 + nvl(g_data_grid(c_row_total_defc_part_dmd)(bktIndex), 0));
1305 else
1306 g_data_grid(c_row_defc_pab)(bktIndex) :=
1307 nvl(g_data_grid(c_row_defc_pab)(bktIndex-1), 0)
1308 + nvl(g_data_grid(c_row_total_defc_supply)(bktIndex), 0)
1309 - (nvl(g_data_grid(c_row_defc_iso)(bktIndex), 0)
1310 + nvl(g_data_grid(c_row_plnd_defc_pod)(bktIndex), 0)
1311 + nvl(g_data_grid(c_row_total_defc_part_dmd)(bktIndex), 0));
1312 end if;
1313
1314 /*
1315 --find the valid safety stock qty entered by user
1316 if (g_data_grid(c_row_ss_qty)(bktIndex) is not null) then
1317 l_ss_last_bucket_index := bktIndex;
1318 end if;
1319 */
1320 --6400965 bugfix
1321 --6867580 bugfix, if the qty 0, show prev bucket value
1322 if (bktIndex > 1) then
1323 if ( nvl(g_data_grid(c_row_ss_qty)(bktIndex),0) = 0) then
1324 g_data_grid(c_row_ss_qty)(bktIndex) := g_data_grid(c_row_ss_qty)(bktIndex-1);
1325 end if;
1326 end if;
1327
1328 end loop; --}
1329
1330 /*
1331 if ( nvl(l_ss_last_bucket_index,c_mbp_null_value) > 0) then
1332 --special logic for safety stock qty - filling in the gaps
1333 for bktIndex in 1..l_ss_last_bucket_index
1334 loop --{
1335 if (bktIndex > 1) then
1336 if ( nvl(g_data_grid(c_row_ss_qty)(bktIndex),c_mbp_null_value) = c_mbp_null_value ) then
1337 g_data_grid(c_row_ss_qty)(bktIndex) := g_data_grid(c_row_ss_qty)(bktIndex-1);
1338 end if;
1339 end if;
1340 end loop; --}
1341 end if;
1342 */
1343
1344 msc_sda_utils.println('calculateSDTotals out');
1345 end calculateSDTotals;
1346
1347 function get_fcst_bucket_index(p_bucket_date date) return number is
1348 l_bkt_index number := -1;
1349 l_found boolean := false;
1350 begin
1351 for bktIndex in 2..(g_bkt_start_date.count-1)
1352 loop
1353 msc_sda_utils.println('get_fcst_bucket_index bktIndex '||bktIndex
1354 ||' :: bucket_date '|| to_char(p_bucket_date,c_datetime_format)
1355 ||' :: bkt_start_date '|| to_char(g_bkt_start_date(bktIndex),c_datetime_format) );
1356
1357 if bktIndex = 2 and trunc(p_bucket_date) <= g_bkt_start_date(bktIndex) then
1358 l_found := true;
1359 l_bkt_index := 1;
1360 end if;
1361 if bktIndex = (g_bkt_start_date.count-1) and trunc(p_bucket_date) >= g_bkt_start_date(bktIndex) then
1362 l_found := true;
1363 l_bkt_index := g_bkt_start_date.count;
1364 end if;
1365 if trunc(p_bucket_date) >= g_bkt_start_date(bktIndex) and trunc(p_bucket_date) < g_bkt_start_date(bktIndex+1) then
1366 l_found := true;
1367 l_bkt_index := bktIndex;
1368 end if;
1369 if (l_found) then
1370 msc_sda_utils.println(' found index '||l_bkt_index);
1371 exit;
1372 end if;
1373 end loop;
1374 return l_bkt_index;
1375 end get_fcst_bucket_index;
1376
1377 /*
1378 procedure populate_fcst_bkts_to_mfq is
1379 l_start_date date;
1380 l_end_date date;
1381 l_bkt_index number := 1;
1382 begin
1383 g_fcst_bkt_mfq_id := msc_sda_utils.getNewFormQueryId;
1384 msc_sda_utils.println('populate_fcst_bkts_to_mfq query_id '||g_fcst_bkt_mfq_id);
1385
1386 for bktIndex in 1..g_bkt_start_date.count
1387 loop
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;
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 */
1405
1406 procedure flushAndSendAddlData(p_view_type number, p_query_id number,
1407 p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1408
1409 --------------------------------------
1410 --- FORECAST VIEW ADDL ROWS CUSROR ---
1411 --------------------------------------
1412 cursor fcst_addl_snapshot_cur is
1413 select
1414 maq.row_index,
1415 c_row2_type_16 row_type,
1416 md.using_assembly_demand_date new_date,
1417 md.original_item_id due_item_id,
1418 msc_get_name.item_name(md.original_item_id, null, null, null) due_item_name,
1419 sum(decode(md.assembly_demand_comp_date,
1420 null, decode(md.origination_type,
1421 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1422 31, 0,
1423 md.using_requirement_quantity),
1424 decode(md.origination_type,
1425 29,(nvl(md.probability,1)* md.daily_demand_rate),
1426 31, 0,
1427 md.daily_demand_rate)))/
1428 decode(nvl(least(sum(decode(md.origination_type,
1429 29,nvl(md.probability,0),
1430 null)),
1431 1) ,1),
1432 0,1,
1433 nvl(least(sum(decode(md.origination_type,
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
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)
1446 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1447 and md.plan_id = g_plan_id
1448 --and md.sr_instance_id = c_global_inst_id
1449 and md.organization_id = c_global_org_id
1450 and md.inventory_item_id = mfq2.number2
1451 and md.zone_id = c_global_reg_id
1452 and md.plan_id = mp.plan_id
1453 and mfq3.query_id = g_plan_bkts_query_id
1454 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
1455 and md.origination_type in (c_dmd2_popu_fcst)
1456 group by
1457 maq.row_index,
1458 c_row2_type_16,
1459 md.using_assembly_demand_date,
1460 md.original_item_id,
1461 msc_get_name.item_name(md.original_item_id, null, null, null)
1462 order by 1,2,3,4;
1463
1464 l_one_record varchar2(2000);
1465 l_out_data_index number := 1;
1466
1467 ll_row_index msc_sda_utils.number_arr;
1468 ll_row_type msc_sda_utils.number_arr;
1469 ll_new_date msc_sda_utils.date_arr;
1470 ll_due_item_id msc_sda_utils.number_arr;
1471 ll_due_item_name msc_sda_utils.char_arr;
1472 ll_new_quantity msc_sda_utils.number_arr;
1473
1474 l_cur_row_index number := c_mbp_null_value;
1475 hasRowChanged boolean;
1476 firstRecord boolean := false;
1477
1478 l_bkt_index number;
1479 begin
1480 open fcst_addl_snapshot_cur;
1481 fetch fcst_addl_snapshot_cur bulk collect into ll_row_index,
1482 ll_row_type, ll_new_date, ll_due_item_id, ll_due_item_name, ll_new_quantity;
1483 close fcst_addl_snapshot_cur;
1484
1485 for rIndex in 1 .. ll_row_index.count
1486 loop --{
1487 l_bkt_index := get_fcst_bucket_index(ll_new_date(rIndex));
1488 l_one_record := nvl(to_char(ll_row_index(rIndex)),c_null_space)
1489 || c_field_seperator || nvl(to_char(ll_row_type(rIndex)),c_null_space)
1490 --|| c_field_seperator || nvl(to_char(ll_new_date(rIndex), c_date_format), c_null_space)
1491 || c_field_seperator || nvl(to_char(l_bkt_index), c_null_space)
1492 || c_field_seperator || nvl(to_char(ll_new_quantity(rIndex)), c_null_space)
1493 || c_field_seperator || nvl(to_char(ll_due_item_id(rIndex)), c_null_space)
1494 || c_field_seperator || nvl(msc_sda_utils.escapeSplChars(ll_due_item_name(rIndex)), c_null_space);
1495
1496 msc_sda_utils.println(' l_one_record '||l_one_record);
1497 if (rIndex = 1) then --{
1498 if (l_cur_row_index = c_mbp_null_value) then
1499 l_cur_row_index := ll_row_index(rIndex);
1500 end if;
1501 l_one_record := c_fcstview_addl_data || c_bang_separator || c_record_seperator || l_one_record;
1502 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1503 else
1504 hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
1505 if (hasRowChanged ) then
1506 l_one_record := ll_row_index(rIndex) || c_record_seperator || l_one_record;
1507 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1508 l_cur_row_index := ll_row_index(rIndex);
1509 else
1510 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1511 end if;
1512 end if; --}
1513 end loop; --}
1514 end flushAndSendAddlData;
1515
1516 procedure flushAndSendData(p_view_type number, p_query_id number,
1517 p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1518
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
1528 ---------------------------
1529 --- SUPPLY VIEW CUSROR ---
1530 ---------------------------
1531 cursor sd_snapshot_cur is
1532 select
1533 maq.row_index,
1534 msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id) row_type,
1535 msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id) offset,
1536 msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) new_date,
1537 msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date) old_date,
1538 sum(decode(msi.base_item_id,
1539 null, decode(ms.disposition_status_type,
1540 2, 0,
1541 decode(ms.last_unit_completion_date,
1542 null, ms.new_order_quantity,
1543 ms.daily_rate) ),
1544 decode(ms.last_unit_completion_date,
1545 null, ms.new_order_quantity,
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
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
1559 and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1560 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1561 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1562 and mfq2.query_id = g_chain_query_id
1563 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1564 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1565 and ms.plan_id = g_plan_id
1566 and ms.sr_instance_id = mfq1.number2
1567 and ms.organization_id = mfq1.number3
1568 and ms.inventory_item_id = mfq2.number2
1569 and ms.plan_id = msi.plan_id
1570 and ms.inventory_item_id = msi.inventory_item_id
1571 and ms.organization_id = msi.organization_id
1572 and ms.sr_instance_id = msi.sr_instance_id
1573 and ms.plan_id = mp.plan_id
1574 and mfq3.query_id = g_plan_bkts_query_id
1575 and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
1576 group by
1577 maq.row_index,
1578 msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id),
1579 msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id),
1580 msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
1581 msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
1582 union all
1583 select
1584 maq.row_index,
1585 msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type) row_type,
1586 msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type) offset,
1587 msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) new_date,
1588 msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date) old_date,
1589 sum(decode(md.assembly_demand_comp_date,
1590 null, decode(md.origination_type,
1591 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1592 31, 0,
1593 md.using_requirement_quantity),
1594 decode(md.origination_type,
1595 29,(nvl(md.probability,1)* md.daily_demand_rate),
1596 31, 0,
1597 md.daily_demand_rate)))/
1598 decode(nvl(least(sum(decode(md.origination_type,
1599 29,nvl(md.probability,0),
1600 null)),
1601 1) ,1),
1602 0,1,
1603 nvl(least(sum(decode(md.origination_type,
1604 29,nvl(md.probability,0),
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
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
1618 and mfq1.query_id = g_org_query_id
1619 and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1620 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1621 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1622 and mfq2.query_id = g_chain_query_id
1623 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1624 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1625 and md.plan_id = g_plan_id
1626 and md.sr_instance_id = mfq1.number2
1627 and md.organization_id = mfq1.number3
1628 and md.inventory_item_id = mfq2.number2
1629 and md.plan_id = msi.plan_id
1630 and md.inventory_item_id = msi.inventory_item_id
1631 and md.organization_id = msi.organization_id
1632 and md.sr_instance_id = msi.sr_instance_id
1633 and md.plan_id = mp.plan_id
1634 and mfq3.query_id = g_plan_bkts_query_id
1635 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2))
1636 and md.sr_instance_id = mio.sr_instance_id
1637 and md.organization_id = mio.organization_id
1638 group by
1639 maq.row_index,
1640 msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type),
1641 msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type),
1642 msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1643 msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1644 union all
1645 select
1646 maq.row_index,
1647 c_max_level row_type,
1648 c_row_max_level offset,
1649 mil.inventory_date new_date,
1650 mil.inventory_date old_date,
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
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)
1662 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1663 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1664 and mfq2.query_id = g_chain_query_id
1665 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1666 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1667 and mil.plan_id = g_plan_id
1668 and mil.sr_instance_id = mfq1.number2
1669 and mil.organization_id = mfq1.number3
1670 and mil.inventory_item_id = mfq2.number2
1671 and mil.inventory_date <= g_plan_end_date
1672 and nvl(mil.max_quantity,mil.max_quantity_dos) is not null
1673 group by
1674 maq.row_index,
1675 c_max_level,
1676 c_row_max_level,
1677 mil.inventory_date,
1678 mil.inventory_date
1679 union all
1680 select
1681 maq.row_index,
1682 c_ss_supply row_type,
1683 c_row_ss_supply offset,
1684 mss.period_start_date new_date,
1685 mss.period_start_date old_date,
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
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)
1697 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1698 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1699 and mfq2.query_id = g_chain_query_id
1700 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1701 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1702 and mss.plan_id = g_plan_id
1703 and mss.sr_instance_id = mfq1.number2
1704 and mss.organization_id = mfq1.number3
1705 and mss.inventory_item_id = mfq2.number2
1706 and mss.period_start_date <= g_plan_end_date
1707 group by
1708 maq.row_index,
1709 c_ss_supply,
1710 c_row_ss_supply,
1711 mss.period_start_date,
1712 mss.period_start_date
1713 union all
1714 select
1715 maq.row_index,
1716 c_target_level row_type,
1717 c_row_target_level offset,
1718 nvl(maa.week_start_date, maa.period_start_date) new_date,
1719 nvl(maa.week_start_date, maa.period_start_date) old_date,
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
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)
1732 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1733 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1734 and mfq2.query_id = g_chain_query_id
1735 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1736 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1737 and maa.plan_id = g_plan_id
1738 and maa.sr_instance_id = mfq1.number2
1739 and maa.organization_id = mfq1.number3
1740 and maa.inventory_item_id = mfq2.number2
1741 and maa.record_type = 3
1742 and maa.period_type = 1
1743 and mpb.plan_id = maa.plan_id
1744 and ( (mpb.bucket_type = 2 and maa.week_start_date = mpb.bkt_start_date) or
1745 (mpb.bucket_type = 3 and maa.period_start_date = mpb.bkt_start_date))
1746 group by
1747 maq.row_index,
1748 c_target_level,
1749 c_row_target_level,
1750 nvl(maa.week_start_date, maa.period_start_date),
1751 nvl(maa.week_start_date, maa.period_start_date)
1752 union all
1753 select
1754 maq.row_index,
1755 c_ss_level row_type,
1756 c_row_ss_level offset,
1757 nvl(maa.week_start_date, maa.period_start_date) new_date,
1758 nvl(maa.week_start_date, maa.period_start_date) old_date,
1759 sum(maa.achieved_service_level_qty1)
1760 / sum(decode(maa.achieved_service_level_qty2,
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
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)
1773 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1774 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1775 and mfq2.query_id = g_chain_query_id
1776 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1777 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1778 and maa.plan_id = g_plan_id
1779 and maa.sr_instance_id = mfq1.number2
1780 and maa.organization_id = mfq1.number3
1781 and maa.inventory_item_id = mfq2.number2
1782 and maa.record_type = 3
1783 and maa.period_type = 1
1784 and mpb.plan_id = maa.plan_id
1785 and ( (mpb.bucket_type = 2 and maa.week_start_date = mpb.bkt_start_date) or
1786 (mpb.bucket_type = 3 and maa.period_start_date = mpb.bkt_start_date))
1787 group by
1788 maq.row_index,
1789 c_target_level,
1790 c_row_target_level,
1791 nvl(maa.week_start_date, maa.period_start_date),
1792 nvl(maa.week_start_date, maa.period_start_date)
1793 order by 1;
1794
1795 ----------------------------
1796 --- FORECAST VIEW CUSROR ---
1797 ----------------------------
1798 cursor fcst_snapshot_cur is
1799 --for region based demands
1800 select
1801 maq.row_index,
1802 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1803 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1804 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1805 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1806 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1807 md.using_assembly_demand_date) new_date,
1808 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1809 md.using_assembly_demand_date) old_date,
1810 sum(decode(md.assembly_demand_comp_date,
1811 null, decode(md.origination_type,
1812 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1813 31, 0,
1814 md.using_requirement_quantity),
1815 decode(md.origination_type,
1816 29,(nvl(md.probability,1)* md.daily_demand_rate),
1817 31, 0,
1818 md.daily_demand_rate)))/
1819 decode(nvl(least(sum(decode(md.origination_type,
1820 29,nvl(md.probability,0),
1821 null)),
1822 1) ,1),
1823 0,1,
1824 nvl(least(sum(decode(md.origination_type,
1825 29,nvl(md.probability,0),
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
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,
1839 c_global_reg_type, mfq1.number1, maq.region_id),
1840 mfq1.number1), mfq1.number1)
1841 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
1842 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1843 and mfq2.query_id = g_chain_query_id
1844 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
1845 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1846 and md.plan_id = g_plan_id
1847 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
1848 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
1849 and md.inventory_item_id = mfq2.number2
1850 and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
1851 and md.plan_id = mp.plan_id
1852 and mfq3.query_id = g_plan_bkts_query_id
1853 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
1854 and md.origination_type in (c_dmd2_manual_fcst, c_dmd2_net_fcst)
1855 and nvl(maq.region_id, -1) not in (c_global_reg_type, c_local_reg_type)
1856 and ( ( nvl(maq.region_id,-1) = -1
1857 and (md.organization_id = -1
1858 or (nvl(maq.org_id, c_mbp_null_value) = md.organization_id
1859 and (md.original_demand_id is null
1860 or md.original_demand_id in (select demand_id
1861 from msc_demands md2
1862 where md2.plan_id = g_plan_id
1863 and md2.origination_type = c_dmd2_net_fcst
1864 and md2.organization_id = -1
1865 and md2.inventory_item_id = md.inventory_item_id))
1866 )))
1867 or (nvl(maq.region_id,-1) <> -1 and md.organization_id <> -1) )
1868 group by
1869 maq.row_index,
1870 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1871 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1872 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1873 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1874 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1875 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1876 union all
1877 --for global based demands
1878 select
1879 maq.row_index,
1880 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1881 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1882 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1883 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1884 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1885 md.using_assembly_demand_date) new_date,
1886 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1887 md.using_assembly_demand_date) old_date,
1888 sum(decode(md.assembly_demand_comp_date,
1889 null, decode(md.origination_type,
1890 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1891 31, 0,
1892 md.using_requirement_quantity),
1893 decode(md.origination_type,
1894 29,(nvl(md.probability,1)* md.daily_demand_rate),
1895 31, 0,
1896 md.daily_demand_rate)))/
1897 decode(nvl(least(sum(decode(md.origination_type,
1898 29,nvl(md.probability,0),
1899 null)),
1900 1) ,1),
1901 0,1,
1902 nvl(least(sum(decode(md.origination_type,
1903 29,nvl(md.probability,0),
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
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,
1917 c_global_reg_type, mfq1.number1, maq.region_id),
1918 mfq1.number1), mfq1.number1)
1919 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
1920 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1921 and mfq2.query_id = g_chain_query_id
1922 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
1923 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1924 and md.plan_id = g_plan_id
1925 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
1926 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
1927 and md.inventory_item_id = mfq2.number2
1928 and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
1929 and md.zone_id is null
1930 and md.plan_id = mp.plan_id
1931 and mfq3.query_id = g_plan_bkts_query_id
1932 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
1933 and md.origination_type in (c_dmd2_manual_fcst, c_dmd2_net_fcst)
1934 and nvl(maq.region_id, -1) in (c_global_reg_type)
1935 and ( nvl(maq.org_id,-1) = md.organization_id)
1936 and ( nvl(maq.org_id,-1) = -1
1937 or (md.original_demand_id is null or
1938 md.original_demand_id in (select demand_id
1939 from msc_demands md2
1940 where md2.plan_id = g_plan_id
1941 and md2.origination_type = c_dmd2_net_fcst
1942 and md2.organization_id = -1
1943 and md2.inventory_item_id = md.inventory_item_id)) )
1944 group by
1945 maq.row_index,
1946 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1947 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1948 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1949 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
1950 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
1951 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
1952 union all
1953 --for local based demands
1954 select
1955 maq.row_index,
1956 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
1957 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
1958 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
1959 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
1960 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
1961 md.using_assembly_demand_date) new_date,
1962 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
1963 md.using_assembly_demand_date) old_date,
1964 sum(decode(md.assembly_demand_comp_date,
1965 null, decode(md.origination_type,
1966 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1967 31, 0,
1968 md.using_requirement_quantity),
1969 decode(md.origination_type,
1970 29,(nvl(md.probability,1)* md.daily_demand_rate),
1971 31, 0,
1972 md.daily_demand_rate)))/
1973 decode(nvl(least(sum(decode(md.origination_type,
1974 29,nvl(md.probability,0),
1975 null)),
1976 1) ,1),
1977 0,1,
1978 nvl(least(sum(decode(md.origination_type,
1979 29,nvl(md.probability,0),
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
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)
1993 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
1994 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1995 and mfq2.query_id = g_chain_query_id
1996 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
1997 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1998 and md.plan_id = g_plan_id
1999 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2000 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2001 and md.inventory_item_id = mfq2.number2
2002 --and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2003 and md.zone_id is null
2004 and md.plan_id = mp.plan_id
2005 and mfq3.query_id = g_plan_bkts_query_id
2006 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2007 and md.origination_type in (c_dmd2_manual_fcst, c_dmd2_net_fcst)
2008 and nvl(maq.region_id, -1) in (c_local_reg_type, -1)
2009 and mfq1.number1 = c_local_reg_type
2010 and md.organization_id <> -1
2011 and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
2012 --and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
2013 and ( (md.original_demand_id is null or
2014 md.original_demand_id not in (select demand_id
2015 from msc_demands md2
2016 where md2.plan_id = g_plan_id
2017 and md2.origination_type = c_dmd2_net_fcst
2018 and md2.organization_id = -1
2019 and md2.inventory_item_id = md.inventory_item_id)) )
2020 group by
2021 maq.row_index,
2022 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
2023 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2024 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
2025 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2026 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
2027 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
2028 union all
2029 --for region based demands - consumption
2030 select
2031 maq.row_index,
2032 c_drow2_consm_qty row_type,
2033 c_row2_consumed_fcst offset,
2034 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2035 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
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,
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,
2050 c_global_reg_type, mfq1.number1, maq.region_id),
2051 mfq1.number1), mfq1.number1)
2052 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2053 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2054 and mfq2.query_id = g_chain_query_id
2055 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2056 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2057 and md.plan_id = g_plan_id
2058 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2059 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2060 and md.inventory_item_id = mfq2.number2
2061 and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2062 and md.plan_id = mp.plan_id
2063 and mfq3.query_id = g_plan_bkts_query_id
2064 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2065 and md.origination_type in (c_dmd2_net_fcst)
2066 and nvl(maq.region_id, -1) not in (c_global_reg_type, c_local_reg_type)
2067 and ( ( nvl(maq.region_id,-1) = -1
2068 and (md.organization_id = -1
2069 or (nvl(maq.org_id, c_mbp_null_value) = md.organization_id
2070 and (md.original_demand_id is null
2071 or md.original_demand_id in (select demand_id
2072 from msc_demands md2
2073 where md2.plan_id = g_plan_id
2074 and md2.origination_type = c_dmd2_net_fcst
2075 and md2.organization_id = -1
2076 and md2.inventory_item_id = md.inventory_item_id))
2077 )))
2078 or (nvl(maq.region_id,-1) <> -1 and md.organization_id <> -1) )
2079 and md.plan_id = mfu.plan_id
2080 and md.demand_id = mfu.forecast_demand_id
2081 group by
2082 maq.row_index,
2083 c_drow2_consm_qty,
2084 c_row2_consumed_fcst,
2085 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2086 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2087 union all
2088 --for global based demands - consumption
2089 select
2090 maq.row_index,
2091 c_drow2_consm_qty row_type,
2092 c_row2_consumed_fcst offset,
2093 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2094 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
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,
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,
2109 c_global_reg_type, mfq1.number1, maq.region_id),
2110 mfq1.number1), mfq1.number1)
2111 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2112 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2113 and mfq2.query_id = g_chain_query_id
2114 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2115 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2116 and md.plan_id = g_plan_id
2117 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2118 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2119 and md.inventory_item_id = mfq2.number2
2120 and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2121 and md.zone_id is null
2122 and md.plan_id = mp.plan_id
2123 and mfq3.query_id = g_plan_bkts_query_id
2124 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2125 and md.origination_type in (c_dmd2_net_fcst)
2126 and nvl(maq.region_id, -1) in (c_global_reg_type)
2127 and ( nvl(maq.org_id,-1) = md.organization_id)
2128 and ( nvl(maq.org_id,-1) = -1
2129 or (md.original_demand_id is null or
2130 md.original_demand_id in (select demand_id
2131 from msc_demands md2
2132 where md2.plan_id = g_plan_id
2133 and md2.origination_type = c_dmd2_net_fcst
2134 and md2.organization_id = -1
2135 and md2.inventory_item_id = md.inventory_item_id)) )
2136 and md.plan_id = mfu.plan_id
2137 and md.demand_id = mfu.forecast_demand_id
2138 group by
2139 maq.row_index,
2140 c_drow2_consm_qty,
2141 c_row2_consumed_fcst,
2142 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2143 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2144 union all
2145 --for local based demands - consumption
2146 select
2147 maq.row_index,
2148 c_drow2_consm_qty row_type,
2149 c_row2_consumed_fcst offset,
2150 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2151 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
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,
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)
2166 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2167 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2168 and mfq2.query_id = g_chain_query_id
2169 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2170 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2171 and md.plan_id = g_plan_id
2172 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2173 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2174 and md.inventory_item_id = mfq2.number2
2175 --and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2176 and md.zone_id is null
2177 and md.plan_id = mp.plan_id
2178 and mfq3.query_id = g_plan_bkts_query_id
2179 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2180 and md.origination_type in (c_dmd2_net_fcst)
2181 and nvl(maq.region_id, -1) in (c_local_reg_type, -1)
2182 and mfq1.number1 = c_local_reg_type
2183 and md.organization_id <> -1
2184 and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
2185 --and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
2186 and ( (md.original_demand_id is null or
2187 md.original_demand_id not in (select demand_id
2188 from msc_demands md2
2189 where md2.plan_id = g_plan_id
2190 and md2.origination_type = c_dmd2_net_fcst
2191 and md2.organization_id = -1
2192 and md2.inventory_item_id = md.inventory_item_id)) )
2193 and md.plan_id = mfu.plan_id
2194 and md.demand_id = mfu.forecast_demand_id
2195 group by
2196 maq.row_index,
2197 c_drow2_consm_qty,
2198 c_row2_consumed_fcst,
2199 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2200 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2201 union all
2202 --supplies
2203 select
2204 maq.row_index,
2205 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2206 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
2207 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2208 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
2209 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2210 ms.new_schedule_date) new_date,
2211 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2212 ms.new_schedule_date) old_date,
2213 sum(decode(msi.base_item_id,
2214 null, decode(ms.disposition_status_type,
2215 2, 0,
2216 decode(ms.last_unit_completion_date,
2217 null, ms.new_order_quantity,
2218 ms.daily_rate) ),
2219 decode(ms.last_unit_completion_date,
2220 null, ms.new_order_quantity,
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
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
2234 and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2235 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
2236 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2237 and mfq2.query_id = g_chain_query_id
2238 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2239 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2240 and ms.plan_id = g_plan_id
2241 and ms.sr_instance_id = nvl(maq.inst_id, mfq1.number2)
2242 and ms.organization_id = nvl(maq.org_id, mfq1.number3)
2243 and ms.inventory_item_id = mfq2.number2
2244 and nvl(ms.zone_id, c_local_reg_type) = mfq1.number1
2245 and ms.plan_id = msi.plan_id
2246 and ms.inventory_item_id = msi.inventory_item_id
2247 and ms.organization_id = msi.organization_id
2248 and ms.sr_instance_id = msi.sr_instance_id
2249 and ms.plan_id = mp.plan_id
2250 and mfq3.query_id = g_plan_bkts_query_id
2251 and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2252 and ms.order_type in (c_sup2_rtns_fcst,
2253 c_sup2_rtns_manual_fcst,
2254 c_sup2_rtns_dmd_schd,
2255 c_sup2_rtns_bestfit_fcst)
2256 group by
2257 maq.row_index,
2258 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2259 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2260 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2261 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2262 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
2263 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
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;
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;
2281 ll_bkt_end_date msc_sda_utils.date_arr;
2282
2283 ---------------------------
2284 --- HISTORY VIEW CUSROR ---
2285 ---------------------------
2286
2287 --engine will not flush demand history and returns history into msc_supplies/msc_demands
2288 --ui needs to look at msd views to get this information
2289 cursor hist_snapshot_cur is
2290 select
2291 maq.row_index,
2292 msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag) row_type,
2293 msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag) offset,
2294 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date) new_date,
2295 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date) old_date,
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
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)
2308 and nvl(mfq1.number2, c_mbp_not_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_not_null_value))
2309 and nvl(mfq1.number3, -1) = nvl(maq.org_id, nvl(mfq1.number3, -1))
2310 and mfq2.query_id = g_chain_query_id
2311 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2312 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2313 and nvl(mmhv.sr_instance_id, c_mbp_not_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_not_null_value))
2314 and nvl(mmhv.organization_id, -1) = nvl(maq.org_id, nvl(mfq1.number3, -1))
2315 and mmhv.inventory_item_id = mfq2.number2
2316 and nvl(mmhv.zone_id, c_local_reg_type) = mfq1.number1
2317 and mfq3.query_id = g_hist_cal_query_id
2318 and trunc(mmhv.anchor_date) between mfq3.date1 and mfq3.date2
2319 group by
2320 maq.row_index,
2321 msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag),
2322 msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag),
2323 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date),
2324 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date)
2325 order by 1;
2326
2327 /*
2328 cursor hist_snapshot_cur is
2329 select
2330 maq.row_index,
2331 msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag) row_type,
2332 msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag) offset,
2333 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date) new_date,
2334 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date) old_date,
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
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)
2347 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
2348 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2349 and mfq2.query_id = g_chain_query_id
2350 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2351 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2352 and ms.plan_id = g_plan_id
2353 and ms.sr_instance_id = nvl(maq.inst_id, mfq1.number2)
2354 and ms.organization_id = nvl(maq.org_id, mfq1.number3)
2355 and ms.inventory_item_id = mfq2.number2
2356 and nvl(ms.zone_id, c_local_reg_type) = mfq1.number1
2357 and mfq3.query_id = g_hist_cal_query_id
2358 and trunc(ms.new_schedule_date) between mfq3.date1 and mfq3.date2
2359 and ms.order_type = c_returns_hist
2360 group by
2361 maq.row_index,
2362 msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag),
2363 msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag),
2364 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date),
2365 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date)
2366 union all
2367 select
2368 maq.row_index,
2369 msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag) row_type,
2370 msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag) offset,
2371 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date) new_date,
2372 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date) old_date,
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
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)
2385 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
2386 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2387 and mfq2.query_id = g_chain_query_id
2388 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2389 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2390 and md.plan_id = g_plan_id
2391 and md.sr_instance_id = nvl(maq.inst_id, mfq1.number2)
2392 and md.organization_id = nvl(maq.org_id, mfq1.number3)
2393 and md.inventory_item_id = mfq2.number2
2394 and nvl(md.zone_id, md.schedule_designator_id, c_local_reg_type) = mfq1.number1
2395 and mfq3.query_id = g_hist_cal_query_id
2396 and trunc(md.using_assembly_demand_date) between mfq3.date1 and mfq3.date2
2397 and md.origination_type = c_dmd_hist
2398 group by
2399 maq.row_index,
2400 msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag),
2401 msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag),
2402 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date),
2403 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date)
2404 order by 1;
2405 */
2406 /*
2407 QUERY_ID NUMBER1 NUMBER2 NUMBER3
2408 ---------- ---------- ---------- ----------
2409 235631 -400 21 207 --org_reg-query
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
2419 755 3 2 -100 -400 -200 49956 -- localRegion-allOrgs
2420
2421 755 4 3 -100 407 -200 21 207 49956 -- validRegion-validOrgs
2422
2423 755 5 4 -100 -200 21 207 49956 --allRegions-validOrgs
2424
2425 755 6 5 -100 -400 -200 21 207 49956 --localRegion-validOrgs
2426 */
2427
2428 ll_row_index msc_sda_utils.number_arr;
2429 ll_row_type msc_sda_utils.number_arr;
2430 ll_offset msc_sda_utils.number_arr;
2431 ll_new_date msc_sda_utils.date_arr;
2432 ll_old_date msc_sda_utils.date_arr;
2433 ll_new_quantity msc_sda_utils.number_arr;
2434 ll_old_quantity msc_sda_utils.number_arr;
2435
2436 l_cur_row_index number := c_mbp_null_value;
2437 hasRowChanged boolean;
2438
2439 l_out_data_index number := 1;
2440 begin
2441 msc_sda_utils.println('flushAndSendData in');
2442 msc_sda_utils.println('g_plan_id p_query_id g_next_rowset_index g_org_query_id g_chain_query_id '||
2443 g_plan_id ||' - '|| p_query_id ||' - '|| g_next_rowset_index ||' - '|| g_org_query_id ||' - '|| g_chain_query_id );
2444
2445 --fetch rows from snapshot cursor
2446 if (p_view_type = c_sdview) then
2447 open sd_snapshot_cur;
2448 fetch sd_snapshot_cur bulk collect into ll_row_index,
2449 ll_row_type, ll_offset, ll_new_date, ll_old_date,
2450 ll_new_quantity, ll_old_quantity;
2451 close sd_snapshot_cur;
2452 msc_sda_utils.println('sd view row count '||ll_row_index.count);
2453 elsif (p_view_type = c_fcstview) then
2454 open fcst_snapshot_cur;
2455 fetch fcst_snapshot_cur bulk collect into ll_row_index,
2456 ll_row_type, ll_offset, ll_new_date, ll_old_date,
2457 ll_new_quantity, ll_old_quantity;
2458 close fcst_snapshot_cur;
2459 msc_sda_utils.println('fcst view row count '||ll_row_index.count);
2460 elsif (p_view_type = c_histview) then
2461 open hist_min_max_dates;
2462 fetch hist_min_max_dates into l_min_date, l_max_date;
2463 close hist_min_max_dates;
2464
2465 open hist_bucket_dates;
2466 fetch hist_bucket_dates bulk collect into ll_bkt_start_date, ll_bkt_end_date;
2467 close hist_bucket_dates;
2468
2469 open hist_snapshot_cur;
2470 fetch hist_snapshot_cur bulk collect into ll_row_index,
2471 ll_row_type, ll_offset, ll_new_date, ll_old_date,
2472 ll_new_quantity, ll_old_quantity;
2473 close hist_snapshot_cur;
2474 msc_sda_utils.println('hist view row count '||ll_row_index.count||' - bucket count '||ll_bkt_start_date.count);
2475 end if;
2476
2477 if (msc_sda_utils.g_log_flag) then
2478 msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2479 for rIndex in 1 .. ll_row_index.count
2480 loop --{
2481 msc_sda_utils.println(ll_row_index(rIndex)
2482 || ' - '|| ll_row_type(rIndex)
2483 || ' - '|| ll_offset(rIndex)
2484 || ' - '|| to_char(ll_new_date(rIndex), c_date_format)
2485 || ' - '|| to_char(ll_old_date(rIndex), c_date_format)
2486 || ' - '|| ll_new_quantity(rIndex)
2487 || ' - '|| ll_old_quantity(rIndex) );
2488 end loop;
2489 end if;
2490
2491 --for all items fetched from snapshot_cur, flush to bucket and then flush to table
2492 for rIndex in 1 .. ll_row_index.count
2493 loop --{
2494 msc_sda_utils.println('snapshot_cur row_index '|| ll_row_index(rIndex)
2495 || ' bktdate '|| to_char(ll_new_date(rIndex), c_date_format) ||' qty '
2496 || ll_new_quantity(rIndex) ||' offset '|| ll_offset(rIndex) );
2497
2498 if (rIndex = 1) then
2499 initGrid(p_view_type); -- init/reinit grid to zeros
2500 if (l_cur_row_index = c_mbp_null_value) then
2501 l_cur_row_index := ll_row_index(rIndex);
2502 end if;
2503 else
2504 hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
2505 if (hasRowChanged ) then
2506 msc_sda_utils.println('row has changed ');
2507 if (p_view_type = c_sdview) then
2508 calculateSDTotals;
2509 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2510 elsif (p_view_type = c_fcstview) then
2511 calculateFcstTotals;
2512 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2513 elsif (p_view_type = c_histview) then
2514 --calculateHistTotals;
2515 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2516 end if;
2517 initGrid(p_view_type); -- init/reinit grid to zeros
2518 l_cur_row_index := ll_row_index(rIndex);
2519 end if;
2520 end if;
2521
2522 --flush the values to right bucket
2523 if (p_view_type in (c_sdview, c_fcstview)) then
2524 for bktIndex in 1..g_bkt_start_date.count
2525 loop --{
2526 if ( trunc(ll_new_date(rIndex)) >= g_bkt_start_date(bktIndex)
2527 and trunc(ll_new_date(rIndex)) <= g_bkt_end_date(bktIndex)
2528 and ( ll_new_quantity(rIndex) <> 0 or ll_old_quantity(rIndex) <> 0
2529 or (p_view_type = c_sdview and ll_offset(rIndex) = c_row_ss_supply) )
2530 and ll_offset(rIndex) <> c_row_discard ) then
2531 addDataToGrid(ll_offset(rIndex), bktIndex, ll_new_quantity(rIndex), p_view_type);
2532
2533 msc_sda_utils.println('safety stock qty '|| ll_offset(rIndex) ||' - '|| c_row_ss_supply);
2534 if (ll_offset(rIndex) = c_row_ss_supply) then
2535 addDataToGrid(c_row_ss_qty, bktIndex, ll_old_quantity(rIndex), p_view_type);
2536 end if;
2537
2538 if (p_view_type = c_fcstview) then
2539 if (ll_offset(rIndex) = c_row2_consumed_fcst) then
2540 addDataToGrid(c_row2_over_consmptn, bktIndex, ll_old_quantity(rIndex), p_view_type);
2541 end if;
2542 if (ll_offset(rIndex) in (c_row2_net_fcst, c_row2_dmd_schd, c_row2_usage_fcst, c_row2_popultn_fcst) ) then
2543 addDataToGrid(c_row2_orig_fcst, bktIndex, ll_old_quantity(rIndex), p_view_type);
2544 end if;
2545 end if;
2546 end if;
2547 end loop; --}
2548
2549 elsif (p_view_type = c_histview) then
2550 for bktIndex in 1..ll_bkt_start_date.count
2551 loop --{
2552 if ( trunc(ll_new_date(rIndex)) >= ll_bkt_start_date(bktIndex)
2553 and trunc(ll_new_date(rIndex)) <= ll_bkt_end_date(bktIndex)
2554 and ll_new_quantity(rIndex) <> 0
2555 and ll_offset(rIndex) <> c_row_discard ) then
2556 addDataToGrid(ll_offset(rIndex), bktIndex, ll_new_quantity(rIndex), p_view_type);
2557 msc_sda_utils.println('addDataToGrid rindex bktindex qty '||ll_row_index(rIndex)||' - '||bktIndex||' - '||ll_new_quantity(rIndex));
2558 end if;
2559 end loop; --}
2560 end if;
2561
2562 if (rIndex = ll_row_index.count) then --{
2563 if (rIndex = ll_row_index.count) then
2564 msc_sda_utils.println(' last row');
2565 if (p_view_type = c_sdview) then
2566 calculateSDTotals;
2567 flushToStream(ll_row_index(rIndex), l_out_data_index, p_out_data, p_view_type);
2568 elsif (p_view_type = c_fcstview) then
2569 calculateFcstTotals;
2570 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2571 elsif (p_view_type = c_histview) then
2572 --calculateHistTotals;
2573 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2574 end if;
2575 end if;
2576 end if; --}
2577
2578 l_cur_row_index := ll_row_index(rIndex);
2579 end loop; --}
2580
2581 msc_sda_utils.println('flushAndSendData out');
2582 end flushAndSendData;
2583
2584 procedure flushSDRows(p_query_id number, p_row_index number,
2585 p_orglist_action number, p_itemlist_action number, p_action_node number) is
2586
2587 l_orglist_action number;
2588 l_itemlist_action number;
2589 l_row c_row_values_cur%rowtype;
2590
2591 cursor c_orgs_cur is
2592 select distinct
2593 mfq.number1 org_list_id,
2594 mfq.char1 org_list,
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
2604 select distinct
2605 number1 top_item_id,
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;
2615 begin
2616 msc_sda_utils.println('flushSDRows in');
2617 --msc_sda_utils.println('p_row_index - p_orglist_action - p_itemlist_action '|| p_row_index
2618 --||' -'|| p_orglist_action ||' -'|| p_itemlist_action );
2619
2620 if (p_row_index = 1 and p_action_node is null ) then
2621 msc_sda_utils.println('flushSDRows out 1');
2622 return;
2623 end if;
2624
2625 open c_row_values_cur(p_query_id, p_row_index, to_number(null));
2626 fetch c_row_values_cur into l_row;
2627 close c_row_values_cur;
2628
2629 if (l_row.item_id is null) then
2630 l_itemlist_action := c_collapsed_state;
2631 else
2632 l_itemlist_action := p_itemlist_action;
2633 end if;
2634
2635 if (l_row.org_id is null) then
2636 l_orglist_action := c_collapsed_state;
2637 else
2638 l_orglist_action := p_orglist_action;
2639 end if;
2640
2641 open c_next_rowset_index_cur(p_query_id);
2642 fetch c_next_rowset_index_cur into g_next_rowset_index;
2643 close c_next_rowset_index_cur;
2644 g_next_rowset_index := g_next_rowset_index + 1;
2645
2646 if (p_action_node = c_org_node) then --{
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,
2656 c_orgs.org_list_id, c_orgs.org_list, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, l_orglist_action,
2657 l_row.top_item_id, l_row.top_item_name, l_row.item_id, l_row.item_name, l_itemlist_action);
2658 end loop; -- }
2659 end if; -- }
2660
2661 if (p_action_node = c_item_node) then --{
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,
2671 l_row.org_list_id, l_row.org_list, l_row.inst_id, l_row.org_id, l_row.org_code, l_orglist_action,
2672 c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
2673 end loop; --}
2674 end if; -- }
2675
2676 msc_sda_utils.println('flushSDRows out');
2677 end flushSDRows;
2678
2679 procedure flushFcstHistoryRows(p_view_type number, p_query_id number, p_row_index number,
2680 p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number) is
2681
2682 l_reglist_action number;
2683 l_orglist_action number;
2684 l_itemlist_action number;
2685 l_row c_row_values_cur%rowtype;
2686
2687 cursor c_regs_cur is
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;
2697
2698 cursor c_orgs_cur(p_region_id number) is
2699 select distinct
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
2709 and nvl(mfq.number2,c_mbp_null_value) <> c_mbp_null_value)
2710 or (p_view_type = c_histview and nvl(mfq.number2,c_mbp_null_value) <> c_mbp_null_value))
2711 order by sort_column;
2712
2713 cursor c_items_cur is
2714 select distinct
2715 number1 top_item_id,
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;
2725 begin
2726 msc_sda_utils.println('flushFcstHistoryRows in');
2727
2728 if (p_row_index = 1 and p_action_node is null ) then
2729 return;
2730 end if;
2731
2732 open c_row_values_cur(p_query_id, p_row_index, to_number(null));
2733 fetch c_row_values_cur into l_row;
2734 close c_row_values_cur;
2735
2736 if (l_row.region_id is null) then
2737 l_reglist_action := c_collapsed_state;
2738 else
2739 l_reglist_action := p_reglist_action;
2740 end if;
2741
2742 if (l_row.org_id is null) then
2743 l_orglist_action := c_collapsed_state;
2744 else
2745 l_orglist_action := p_orglist_action;
2746 end if;
2747
2748 if (l_row.item_id is null) then
2749 l_itemlist_action := c_collapsed_state;
2750 else
2751 l_itemlist_action := p_itemlist_action;
2752 end if;
2753
2754 open c_next_rowset_index_cur(p_query_id);
2755 fetch c_next_rowset_index_cur into g_next_rowset_index;
2756 close c_next_rowset_index_cur;
2757 g_next_rowset_index := g_next_rowset_index + 1;
2758
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)
2768 values (p_query_id, g_row_index, g_next_rowset_index,
2769 l_row.region_list_id, l_row.region_list, c_regs.region_id, c_regs.region_code, l_reglist_action,
2770 l_row.org_list_id, l_row.org_list, l_row.inst_id, l_row.org_id, l_row.org_code, l_orglist_action,
2771 l_row.top_item_id, l_row.top_item_name, l_row.item_id, l_row.item_name, l_itemlist_action);
2772 end loop; -- }
2773 end if; -- }
2774
2775 if (p_action_node = c_org_node) then --{
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)
2785 values (p_query_id, g_row_index, g_next_rowset_index,
2786 l_row.region_list_id, l_row.region_list, l_row.region_id, l_row.region_code, l_reglist_action,
2787 l_row.org_list_id, l_row.org_list, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, l_orglist_action,
2788 l_row.top_item_id, l_row.top_item_name, l_row.item_id, l_row.item_name, l_itemlist_action);
2789 end loop; -- }
2790 end if; -- }
2791
2792 if (p_action_node = c_item_node) then --{
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)
2802 values (p_query_id, g_row_index, g_next_rowset_index,
2803 l_row.region_list_id, l_row.region_list, l_row.region_id, l_row.region_code, l_reglist_action,
2804 l_row.org_list_id, l_row.org_list, l_row.inst_id, l_row.org_id, l_row.org_code, l_orglist_action,
2805 c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
2806 end loop; --}
2807 end if; -- }
2808
2809 msc_sda_utils.println('flushFcstHistoryRows out');
2810 end flushFcstHistoryRows;
2811
2812 procedure SdFCSTHistoryView(p_query_id in out nocopy number,
2813 p_view_type number,
2814 p_plan_id number,
2815 p_org_type number, p_org_list varchar2,
2816 p_region_type number, p_region_list varchar2,
2817 p_item_list number, p_item_view_type number,
2818 p_refresh_view boolean default false,
2819 p_error_code out nocopy varchar2,
2820 p_item_folder number,
2821 p_items_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2822 p_comments_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2823 p_excp_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
2824
2825 ll_reg_list_id number;
2826 ll_reg_list varchar2(250);
2827 ll_region_id number;
2828 ll_region_code varchar2(250);
2829 ll_org_list_id number;
2830 ll_org_list varchar2(250);
2831 ll_inst_id number;
2832 ll_org_id number;
2833 ll_org_code varchar2(10);
2834 ll_top_item_id number;
2835 ll_top_item_name varchar2(250);
2836 ll_item_id number;
2837 ll_item_name varchar2(250);
2838
2839 ll_reglist_action number;
2840 ll_orglist_action number;
2841 ll_itemlist_action number;
2842 begin
2843 msc_sda_utils.println('SdFCSTHistoryView in');
2844 msc_sda_utils.println('p_query_id - p_view_type - p_plan_id - '
2845 || ' p_org_type - p_org_list - p_region_type - p_region_list - '
2846 || ' p_item_list - p_item_view_type - p_item_folder ');
2847 msc_sda_utils.println(p_query_id ||' - '|| p_view_type ||' - '|| p_plan_id ||' - '||
2848 p_org_type ||' - '|| p_org_list ||' - '|| p_region_type ||' - '|| p_region_list ||' - '||
2849 p_item_list ||' - '|| p_item_view_type ||' - '|| p_item_folder );
2850
2851 g_view_type := p_view_type;
2852 g_plan_id := p_plan_id;
2853
2854 --capture region info in global variables
2855 g_region_type := p_region_type;
2856 g_region_list := p_region_list;
2857
2858 --capture org info in global variables
2859 g_org_type := p_org_type;
2860 g_org_list := p_org_list;
2861
2862 --capture item info in global variables
2863 g_item_list := p_item_list;
2864 g_item_view_type := p_item_view_type;
2865
2866 setPlanInfo;
2867 setUserPrefInfo;
2868
2869 g_row_index := 1; --initialize row_index to 1
2870
2871 if (g_view_type = c_sdview) then -- SD VIEW
2872 g_sd_query_id := msc_sda_utils.getNewAnalysisQueryId;
2873
2874 msc_sda_utils.getOrgListValues(g_org_list, g_org_type, ll_org_list_id, ll_org_list, ll_inst_id, ll_org_id, ll_org_code);
2875 if (g_org_type = c_org_view) then
2876 ll_orglist_action := c_nodrill_state;
2877 else
2878 ll_orglist_action := c_collapsed_state;
2879 end if;
2880
2881 msc_sda_utils.getItemListValues(g_item_list, g_item_view_type, ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name);
2882 g_item_list_name := ll_top_item_name;
2883
2884 if (g_item_view_type = c_item_view) then
2885 ll_itemlist_action := c_nodrill_state;
2886 msc_sda_utils.println(' item no drill state ');
2887 else
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);
2897
2898 g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
2899 msc_sda_utils.println('g_org_query_id '||g_org_query_id);
2900
2901 g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
2902 msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
2903
2904 else -- FORECAST VIEW AND HISTORY VIEW
2905 g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
2906 g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
2907
2908 msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
2909 ll_reglist_action := c_collapsed_state;
2910 ll_orglist_action := c_collapsed_state;
2911
2912 msc_sda_utils.getItemListValues(g_item_list, g_item_view_type, ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name);
2913 g_item_list_name := ll_top_item_name;
2914 if (g_item_view_type = c_item_view) then
2915 ll_itemlist_action := c_nodrill_state;
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)
2925 values (g_fcst_query_id, c_first_row_index, g_next_rowset_index,
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)
2935 values (g_hist_query_id, c_first_row_index, g_next_rowset_index,
2936 c_all_region_type, c_all_region_type_text, to_number(null), null, c_collapsed_state,
2937 c_all_org_type, c_all_org_type_text, to_number(null), to_number(null), null, c_collapsed_state,
2938 ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_itemlist_action);
2939
2940 --flush regions/orgs into mfq
2941 --flush supersession chain into mfq..pabram..this needs to be region specific
2942 msc_sda_utils.flushRegsOrgsIntoMfq(g_plan_id, g_region_type, g_region_list,
2943 g_org_type, g_org_list, g_region_query_id, g_org_query_id);
2944
2945 g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
2946 msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
2947 end if;
2948
2949 msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
2950 msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
2951 msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
2952
2953 msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
2954
2955 p_query_id := g_sd_query_id;
2956 msc_sda_utils.println('SdFCSTHistoryView out');
2957 end SdFCSTHistoryView;
2958
2959 -----
2960 ----- send apis
2961 -----
2962
2963 procedure sendSdFcstTimeBucket(p_view_type number,
2964 p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2965 p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2966 p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2967 p_bucket_count out nocopy number) is
2968 l_one_record varchar2(32000) := null;
2969 l_token varchar2(1000);
2970 l_out_data_index number := 1;
2971 begin
2972 --days
2973 for i in 1..g_bkt_start_date.count
2974 loop
2975 l_token := g_bkt_type(i) || c_field_seperator || to_char(g_bkt_start_date(i), c_date_format);
2976 if (l_one_record is null) then
2977 if (p_view_type = c_sdview) then
2978 l_one_record := c_sdview_bucket_data || c_bang_separator || g_bkt_start_date.count || c_record_seperator || l_token;
2979 elsif (p_view_type = c_fcstview) then
2980 l_one_record := c_fcstview_bucket_data || c_bang_separator || g_bkt_start_date.count || c_record_seperator || l_token;
2981 end if;
2982 else
2983 l_one_record := c_record_seperator || l_token;
2984 end if;
2985 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_bucket_data);
2986 end loop;
2987
2988 --weeks
2989 l_one_record := null;
2990 l_out_data_index := 1;
2991 for i in 1..g_week_start_dates.count
2992 loop
2993 l_token := to_char(g_week_start_dates(i), c_date_format);
2994 if (l_one_record is null) then
2995 if (p_view_type = c_sdview) then
2996 l_one_record := c_sdview_week_data || c_bang_separator || g_week_start_dates.count || c_record_seperator || l_token;
2997 elsif (p_view_type = c_fcstview) then
2998 l_one_record := c_fcstview_week_data || c_bang_separator || g_week_start_dates.count || c_record_seperator || l_token;
2999 end if;
3000 else
3001 l_one_record := c_record_seperator || l_token;
3002 end if;
3003 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_week_data);
3004 end loop;
3005
3006 --periods
3007 l_one_record := null;
3008 l_out_data_index := 1;
3009 for i in 1..g_period_start_dates.count
3010 loop
3011 l_token := to_char(g_period_start_dates(i), c_date_format);
3012 if (l_one_record is null) then
3013 if (p_view_type = c_sdview) then
3014 l_one_record := c_sdview_period_data || c_bang_separator || g_period_start_dates.count || c_record_seperator || l_token;
3015 elsif (p_view_type = c_fcstview) then
3016 l_one_record := c_fcstview_period_data || c_bang_separator || g_period_start_dates.count || c_record_seperator || l_token;
3017 end if;
3018 else
3019 l_one_record := c_record_seperator || l_token;
3020 end if;
3021 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_period_data);
3022 end loop;
3023
3024 if (p_view_type = c_sdview) then
3025 g_sd_num_of_buckets := g_num_of_buckets;
3026 p_bucket_count := g_sd_num_of_buckets;
3027 elsif (p_view_type = c_fcstview) then
3028 g_fcst_num_of_buckets := g_num_of_buckets;
3029 p_bucket_count := g_fcst_num_of_buckets;
3030 end if;
3031 end sendSdFcstTimeBucket;
3032
3033 procedure sentHistTimeBucket(p_start_date date, p_end_date date,
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
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;
3049 l_token varchar2(2000);
3050 begin
3051 g_hist_cal_query_id := msc_sda_utils.createHistCalInMfq(g_pref_hist_start_date, g_plan_start_date);
3052
3053 open c_hist_date_count_cur;
3054 fetch c_hist_date_count_cur into l_count;
3055 close c_hist_date_count_cur;
3056
3057 l_one_record := null;
3058 l_out_data_index := 1;
3059 for c_hist_date in c_hist_date_cur
3060 loop
3061 l_token := to_char(c_hist_date.date1, c_date_format);
3062 if (l_one_record is null) then
3063 l_one_record := c_histview_bucket_data || c_bang_separator || l_count || c_record_seperator || l_token;
3064 else
3065 l_one_record := c_record_seperator || l_token;
3066 end if;
3067 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_bucket_data);
3068 end loop;
3069 p_bucket_count := l_count;
3070 end sentHistTimeBucket;
3071
3072 procedure sendTimeBucket(p_view_type number,
3073 p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3074 p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3075 p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3076 begin
3077 if (p_view_type = c_sdview) then
3078 sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_sd_num_of_buckets);
3079 elsif (p_view_type = c_fcstview) then
3080 sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_fcst_num_of_buckets);
3081 elsif (p_view_type = c_histview) then
3082 sentHistTimeBucket(g_pref_hist_start_date, g_plan_start_date, p_bucket_data, g_hist_num_of_buckets);
3083 end if;
3084 msc_sda_utils.println('g_hist_cal_query_id '||g_hist_cal_query_id);
3085 end sendTimeBucket;
3086
3087 procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3088 p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3089 p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3090 p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3091 begin
3092 g_view_type := p_view_type;
3093 g_plan_id := p_plan_id;
3094 setPlanInfo;
3095 setUserPrefInfo;
3096 sendTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data);
3097 end sendTimeBucketEng;
3098
3099
3100 procedure sendRows(p_view_type number, p_query_id number, p_row_index number, p_parent_row_index number,
3101 p_rheader_data in out nocopy msc_sda_utils.maxCharTbl) is
3102
3103 l_out_data_index number := 1;
3104 l_one_record varchar2(2000) := null;
3105 l_token varchar2(2000);
3106 l_token1 varchar2(2000);
3107 l_token2 varchar2(2000);
3108 l_count number;
3109
3110 l_org_list_id number;
3111 l_org_list varchar2(200);
3112 l_org_list_state number;
3113 l_inst_id number;
3114 l_org_id number;
3115 l_org_code varchar2(100);
3116 begin
3117 msc_sda_utils.println('sendRows in ');
3118 open c_child_row_count(p_query_id, g_next_rowset_index);
3119 fetch c_child_row_count into l_count;
3120 close c_child_row_count;
3121
3122 for c_child_row in c_row_values_cur(p_query_id, to_number(null), g_next_rowset_index)
3123 loop
3124 l_org_list_id := c_child_row.org_list_id;
3125 l_org_list := c_child_row.org_list;
3126 l_org_list_state := c_child_row.org_list_state;
3127 l_inst_id := c_child_row.inst_id;
3128 l_org_id := c_child_row.org_id;
3129 l_org_code := c_child_row.org_code;
3130
3131 --6736491 bugfix, global will also contain orgs now
3132 /*
3133 if (p_view_type = c_fcstview and c_child_row.region_id = c_global_reg_type) then
3134 l_org_list_id := c_mbp_null_value;
3135 l_org_list := c_null_space;
3136 l_org_list_state := c_nodrill_state;
3137 l_inst_id := null;
3138 l_org_id := null;
3139 l_org_code := null;
3140 end if;
3141 */
3142 l_token := to_char(c_child_row.row_index);
3143 l_token1 := c_field_seperator || nvl(to_char(c_child_row.region_list_id), c_null_space)
3144 || c_field_seperator || nvl(to_char(c_child_row.region_list), c_null_space)
3145 || c_field_seperator || nvl(to_char(c_child_row.region_list_state), c_null_space)
3146 || c_field_seperator || nvl(to_char(c_child_row.region_id), c_null_space)
3147 || c_field_seperator || nvl(to_char(c_child_row.region_code), c_null_space);
3148 l_token2 := c_field_seperator || nvl(to_char(l_org_list_id), c_null_space)
3149 || c_field_seperator || nvl(l_org_list, c_null_space)
3150 || c_field_seperator || nvl(to_char(l_org_list_state), c_null_space)
3151 || c_field_seperator || nvl(to_char(l_inst_id), c_null_space)
3152 || c_field_seperator || nvl(to_char(l_org_id), c_null_space)
3153 || c_field_seperator || nvl(l_org_code, c_null_space)
3154 || c_field_seperator || nvl(to_char(c_child_row.top_item_id), c_null_space)
3155 || c_field_seperator || nvl(c_child_row.top_item_name, c_null_space)
3156 || c_field_seperator || nvl(to_char(c_child_row.top_item_name_state), c_null_space)
3157 || c_field_seperator || nvl(to_char(c_child_row.item_id), c_null_space)
3158 || c_field_seperator || nvl(c_child_row.item_name, c_null_space);
3159
3160 if (g_view_type = c_sdview) then
3161 l_token := l_token || l_token2;
3162 elsif (p_view_type = c_fcstview) then
3163 l_token := l_token || l_token1 || l_token2;
3164 elsif (p_view_type = c_histview) then
3165 l_token := l_token || l_token1 || l_token2;
3166 end if;
3167
3168 if (l_one_record is null) then
3169 if (p_view_type = c_sdview) then
3170 l_one_record := c_sdview_rheader_data || c_bang_separator || l_count || c_record_seperator || l_token;
3171 elsif (p_view_type = c_fcstview) then
3172 l_one_record := c_fcstview_rheader_data || c_bang_separator || l_count || c_record_seperator || l_token;
3173 elsif (p_view_type = c_histview) then
3174 l_one_record := c_histview_rheader_data || c_bang_separator || l_count || c_record_seperator || l_token;
3175 end if;
3176 else
3177 l_one_record := c_record_seperator || l_token;
3178 end if;
3179 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_rheader_data);
3180 end loop;
3181 msc_sda_utils.println('sendRows out');
3182 end sendRows;
3183
3184 procedure sendSDData(p_row_index number,
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);
3194 flushAndSendData(c_sdview, g_sd_query_id, p_out_data);
3195 else
3196 sendRows(c_sdview, g_sd_query_id, p_row_index, null, p_rheader_data);
3197 flushAndSendData(c_sdview, g_sd_query_id, p_out_data);
3198 end if;
3199 --p_out_data := msc_sda_utils.maxCharTbl(0); --pabram..testing purpose only..need to remove later
3200 end sendSDData;
3201
3202 procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3203 p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3204 p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3205 p_out_data in out nocopy msc_sda_utils.maxCharTbl,
3206 p_out_data2 in out nocopy msc_sda_utils.maxCharTbl) is
3207
3208 l_query_id number;
3209 begin
3210 if (p_view_type = c_fcstview) then
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);
3220 flushAndSendData(p_view_type, l_query_id, p_out_data);
3221 else
3222 sendRows(p_view_type, l_query_id, p_row_index, null, p_rheader_data);
3223 flushAndSendData(p_view_type, l_query_id, p_out_data);
3224 end if;
3225
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,
3235 p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3236 p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3237 begin
3238 msc_sda_utils.sendSDRowTypes(p_sd_data);
3239 msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3240 msc_sda_utils.sendHistRowTypes(p_hist_data);
3241 end sendRowTypes;
3242
3243 procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3244 begin
3245 msc_sda_utils.getWorkSheetPrefData(p_out_data, p_refresh_flag);
3246 end getWorkSheetPrefData;
3247
3248 procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3249 begin
3250 msc_sda_utils.sendNlsMessages(p_out_data);
3251 end sendNlsMessages;
3252
3253 procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3254 begin
3255 msc_sda_utils.spreadTableMessages(p_out_data);
3256 end spreadTableMessages;
3257
3258 procedure set_shuttle_from_to(p_lookup_type varchar2, p_lookup_code_list varchar2,
3259 p_from_list out nocopy varchar2, p_to_list out nocopy varchar2) is
3260 begin
3261 msc_sda_utils.set_shuttle_from_to(p_lookup_type, p_lookup_code_list, p_from_list, p_to_list);
3262 end set_shuttle_from_to;
3263
3264 procedure update_pref_set (p_name varchar2, p_desc varchar2,
3265 p_days number, p_weeks number, p_periods number,
3266 p_factor number, p_decimal_places number,
3267 p_sd_row_list varchar2, p_fcst_row_list varchar2) is
3268 begin
3269 msc_sda_utils.update_pref_set(p_name, p_desc, p_days, p_weeks, p_periods,
3270 p_factor, p_decimal_places, p_sd_row_list, p_fcst_row_list);
3271 end update_pref_set;
3272
3273 procedure save_item_folder(p_folder_name varchar, p_folder_value varchar,
3274 p_default_flag number, p_public_flag number) is
3275 begin
3276 msc_sda_utils.save_item_folder(p_folder_name, p_folder_value,
3277 p_default_flag, p_public_flag);
3278 end save_item_folder;
3279
3280 procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
3281 begin
3282 msc_sda_utils.update_close_settings(p_event, p_event_list);
3283 end update_close_settings;
3284
3285 procedure send_close_settings(p_item_folder_save_list out nocopy varchar2,
3286 p_save_settings_list out nocopy varchar2) is
3287 begin
3288 msc_sda_utils.send_close_settings(p_item_folder_save_list, p_save_settings_list);
3289 end send_close_settings;
3290
3291 procedure getCommentsData(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3292 begin
3293 msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_out_data, c_sdview_comments_data_ref);
3294 end getCommentsData;
3295
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
3305 if p_out_data is null then
3306 p_out_data := to_char(c_regions.region_id);
3307 else
3308 p_out_data := p_out_data || c_comma_separator|| to_char(c_regions.region_id);
3309 end if;
3310 end loop;
3311 end getRegionList;
3312
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
3322 for c_orgs in c_orgs_cur
3323 loop
3324 if p_out_data is null then
3325 p_out_data := to_char(c_orgs.org_id);
3326 else
3327 p_out_data := p_out_data || c_comma_separator|| to_char(c_orgs.org_id);
3328 end if;
3329 end loop;
3330 end getOrgList;
3331
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
3341 if p_out_data is null then
3342 p_out_data := to_char(c_items.item_id);
3343 else
3344 p_out_data := p_out_data || c_comma_separator|| to_char(c_items.item_id);
3345 end if;
3346 end loop;
3347 end getItemList;
3348
3349 procedure getOrderTypesList(p_view_type number, p_row_offset number,
3350 p_order_type_list out nocopy varchar2, p_from_table out nocopy varchar2,
3351 p_part_condition out nocopy number) is
3352 begin
3353 if (p_view_type = c_histview) then --{
3354 p_from_table := 'msc_demands';
3355 if (p_row_offset = c_row_returns_hist) then
3356 p_order_type_list := c_returns_hist;
3357 elsif (p_row_offset = c_row_dmd_hist) then
3358 p_order_type_list := c_dmd_hist;
3359 end if;
3360 return;
3361 end if; --}
3362
3363 if (p_view_type = c_fcstview) then --{
3364 if (p_row_offset = c_row2_total_fcst) then
3365 p_from_table := 'msc_demands';
3366 p_order_type_list := to_char(c_dmd2_manual_fcst) ||','|| to_char(c_dmd2_net_fcst) ||','|| to_char(c_dmd2_dmd_schd);
3367 elsif (p_row_offset = c_row2_orig_fcst) then
3368 p_from_table := 'msc_demands';
3369 p_order_type_list := c_dmd2_net_fcst;
3370 elsif (p_row_offset = c_row2_consumed_fcst) then
3371 p_from_table := 'msc_forecast_updates';
3372 p_order_type_list := null;
3373 elsif (p_row_offset = c_row2_net_fcst) then
3374 p_from_table := 'msc_demands';
3375 p_order_type_list := c_dmd2_net_fcst;
3376 elsif (p_row_offset = c_row2_over_consmptn) then
3377 p_from_table := 'msc_forecast_updates';
3378 p_order_type_list := null;
3379 elsif (p_row_offset = c_row2_manual_fcst) then
3380 p_from_table := 'msc_demands';
3381 p_order_type_list := c_dmd2_manual_fcst;
3382 elsif (p_row_offset = c_row2_dmd_schd) then
3383 p_from_table := 'msc_demands';
3384 p_order_type_list := c_dmd2_dmd_schd;
3385 elsif (p_row_offset = c_row2_bestfit_fcst) then
3386 p_from_table := 'msc_demands';
3387 p_order_type_list := c_dmd2_bestfit_fcst;
3388 elsif (p_row_offset = c_row2_total_ret_fcst) then
3389 p_from_table := 'msc_supplies';
3390 p_order_type_list := to_char(c_sup2_rtns_manual_fcst) ||','|| to_char(c_sup2_rtns_fcst) ||','|| to_char(c_sup2_rtns_dmd_schd);
3391 elsif (p_row_offset = c_row2_ret_fcst) then
3392 p_from_table := 'msc_supplies';
3393 p_order_type_list := c_sup2_rtns_fcst;
3394 elsif (p_row_offset = c_row2_ret_dmd_schd) then
3395 p_from_table := 'msc_supplies';
3396 p_order_type_list := c_sup2_rtns_dmd_schd;
3397 elsif (p_row_offset = c_row2_ret_manual_fcst) then
3398 p_from_table := 'msc_supplies';
3399 p_order_type_list := c_sup2_rtns_manual_fcst;
3400 elsif (p_row_offset = c_row2_ret_bestfit_fcst) then
3401 p_from_table := 'msc_supplies';
3402 p_order_type_list := c_sup2_rtns_bestfit_fcst;
3403 elsif (p_row_offset = c_row2_usage_fcst) then
3404 p_from_table := 'msc_demands';
3405 p_order_type_list := c_dmd2_usage_fcst;
3406 elsif (p_row_offset = c_row2_popultn_fcst) then
3407 p_from_table := 'msc_demands';
3408 p_order_type_list := c_dmd2_popu_fcst;
3409 elsif (p_row_offset = c_row2_type_16) then
3410 p_from_table := 'msc_demands';
3411 p_order_type_list := c_dmd2_popu_fcst;
3412 end if;
3413 return;
3414 end if; --}
3415
3416 if (p_view_type = c_sdview) then --{
3417 if (p_row_offset = c_row_net_fcst) then --1;
3418 p_from_table := 'msc_demands';
3419 p_order_type_list := c_dmd_fcst;
3420 elsif (p_row_offset = c_row_so) then --2;
3421 p_from_table := 'msc_demands';
3422 p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3423 elsif (p_row_offset = c_row_iso_field_org) then --3;
3424 p_from_table := 'msc_demands';
3425 p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3426 elsif (p_row_offset = c_row_indepndt_dmd) then --4;
3427 p_from_table := 'msc_demands';
3428 p_order_type_list := to_char(c_dmd_fcst) ||', '|| to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3429 elsif (p_row_offset = c_row_iso) then --5;
3430 p_from_table := 'msc_demands';
3431 p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3432 elsif (p_row_offset = c_row_pod) then --6;
3433 p_from_table := 'msc_demands';
3434 p_order_type_list := c_dmd_pod;
3435 elsif (p_row_offset = c_row_dependnt_dmd) then --7;
3436 p_from_table := 'msc_demands';
3437 p_order_type_list := to_char(c_dmd_pod);
3438 elsif (p_row_offset = c_row_other_dmd) then --8;
3439 p_from_table := 'msc_demands';
3440 p_order_type_list := to_char(c_dmd_mps) ||', '|| to_char(c_dmd_manual_mds);
3441 elsif (p_row_offset = c_row_total_dmd) then --9;
3442 p_from_table := 'msc_demands';
3443 p_order_type_list := to_char(c_dmd_fcst) ||', '|| to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds)
3444 ||', '|| to_char(c_dmd_pod)
3445 ||', '|| to_char(c_dmd_mps) ||', '|| to_char(c_dmd_manual_mds);
3446 elsif (p_row_offset = c_row_onhand) then --10;
3447 p_from_table := 'msc_supplies';
3448 p_order_type_list := c_sup_onhand;
3449 elsif (p_row_offset = c_row_transit) then --11;
3450 p_from_table := 'msc_supplies';
3451 p_order_type_list := c_sup_intrnst_shpmt;
3452 elsif (p_row_offset = c_row_receiving) then --12;
3453 p_from_table := 'msc_supplies';
3454 p_order_type_list := c_sup_intrnst_rec;
3455 elsif (p_row_offset = c_row_new_buy_po) then --13;
3456 p_from_table := 'msc_supplies';
3457 p_order_type_list := c_sup_new_buy_po;
3458 elsif (p_row_offset = c_row_new_buy_po_req) then --14;
3459 p_from_table := 'msc_supplies';
3460 p_order_type_list := c_sup_new_buy_po_req;
3461 elsif (p_row_offset = c_row_intrnl_rpr_ordr) then --15;
3462 p_from_table := 'msc_supplies';
3463 p_order_type_list := to_char(c_sup_intrnl_rpr_ordr);
3464 elsif (p_row_offset = c_row_xtrnl_rpr_ordr) then --16;
3465 p_from_table := 'msc_supplies';
3466 p_order_type_list := to_char(c_sup_xtrnl_rpr_ordr)||', '|| to_char(c_sup_ext_rep_req);
3467 --elsif (p_row_offset = c_row_inbnd_ship) then --17;
3468 --p_from_table := 'msc_supplies';
3469 --p_order_type_list := null;
3470 elsif (p_row_offset = c_row_rpr_wo) then --18;
3471 p_from_table := 'msc_supplies';
3472 p_order_type_list := c_sup_rpr_wo ||', '|| to_char(c_sup_rpr_wo_ext_rep_supp) ;
3473 elsif (p_row_offset = c_row_plnd_new_buy_ordr) then --19;
3474 p_from_table := 'msc_supplies';
3475 p_order_type_list := c_sup_plnd_new_buy_ordr;
3476 elsif (p_row_offset = c_row_plnd_intrnl_rpr_ordr) then --20;
3477 p_from_table := 'msc_supplies';
3478 p_order_type_list := c_sup_plnd_intrnl_rpr_ordr;
3479 elsif (p_row_offset = c_row_plnd_xtrnl_rpr_ordr) then --21;
3480 p_from_table := 'msc_supplies';
3481 p_order_type_list := c_sup_plnd_xtrnl_rpr_ordr;
3482 elsif (p_row_offset = c_row_plnd_inbnd_ship) then --22;
3483 p_from_table := 'msc_supplies';
3484 p_order_type_list := c_sup_plnd_inbnd_ship;
3485 elsif (p_row_offset = c_row_plnd_rpr_wo) then --23;
3486 p_from_table := 'msc_supplies';
3487 p_order_type_list := c_sup_plnd_rpr_wo;
3488 elsif (p_row_offset = c_row_plnd_warr_ordr) then --24;
3489 p_from_table := null;
3490 p_order_type_list := null;
3491 elsif (p_row_offset = c_row_total_supply) then --25;
3492 p_from_table := 'msc_supplies';
3493 p_order_type_list := to_char(c_sup_intrnst_shpmt) ||','|| to_char(c_sup_intrnst_rec) ||','|| to_char(c_sup_new_buy_po) ||','||
3494 to_char(c_sup_new_buy_po_req) ||','|| to_char(c_sup_intrnl_rpr_ordr) ||','|| to_char(c_sup_xtrnl_rpr_ordr) ||','||
3495 to_char(c_sup_rpr_wo) ||','|| to_char(c_row_plnd_new_buy_ordr) ||','|| to_char(c_sup_plnd_intrnl_rpr_ordr) ||','||
3496 to_char(c_sup_plnd_xtrnl_rpr_ordr) ||','|| to_char(c_sup_plnd_inbnd_ship) ||','|| to_char(c_sup_plnd_rpr_wo)
3497 ||','|| to_char(c_sup_plnd_new_buy_ordr) ||','|| to_char(c_sup_onhand)
3498 ||','|| to_char(c_sup_rpr_wo) ||', '|| to_char(c_sup_rpr_wo_ext_rep_supp) ||', '|| to_char(c_sup_ext_rep_req);
3499 elsif (p_row_offset = c_row_ss_supply) then --26;
3500 p_from_table := null;
3501 p_order_type_list := '-1';
3502 --elsif (p_row_offset = c_row_total_uncons_dmd) then --27;
3503 --p_from_table := null;
3504 --p_order_type_list := '-1';
3505 elsif (p_row_offset = c_row_ss_level) then --28;
3506 p_from_table := null;
3507 p_order_type_list := '-1';
3508 elsif (p_row_offset = c_row_target_level) then --29;
3509 p_from_table := 'msc_demands';
3510 p_order_type_list := c_dmd_fcst;
3511 p_part_condition := c_part_good;
3512 elsif (p_row_offset = c_row_max_level) then --30;
3513 p_from_table := null;
3514 p_order_type_list := '-1';
3515 elsif (p_row_offset = c_row_pab) then --31;
3516 p_from_table := 'msc_orders_v';
3517 p_order_type_list := c_mbp_null_value;
3518 p_part_condition := c_part_good;
3519 elsif (p_row_offset = c_row_poh) then --32;
3520 p_from_table := 'msc_orders_v';
3521 p_order_type_list := c_mbp_null_value;
3522 p_part_condition := c_part_good;
3523 elsif (p_row_offset = c_row_defc_iso) then --33;
3524 p_from_table := 'msc_demands';
3525 p_order_type_list := c_dmd_defc_iso;
3526 p_part_condition := c_part_bad;
3527 elsif (p_row_offset = c_row_plnd_defc_pod) then --34;
3528 p_from_table := 'msc_demands';
3529 p_order_type_list := c_dmd_defc_pod;
3530 p_part_condition := c_part_bad;
3531 elsif (p_row_offset = c_row_defc_part_dmd) then --35;
3532 p_from_table := 'msc_demands';
3533 p_order_type_list := to_char(c_dmd_defc_part_dmd)||','||to_char(c_dmd_defc_plnd_part_dmd);
3534 p_part_condition := c_part_bad;
3535 elsif (p_row_offset = c_row_total_defc_part_dmd) then --36;
3536 p_from_table := 'msc_demands';
3537 p_order_type_list := to_char(c_dmd_defc_part_dmd)||','||to_char(c_dmd_defc_plnd_part_dmd);
3538 p_part_condition := c_part_bad;
3539 elsif (p_row_offset = c_row_defc_onhand) then --37;
3540 p_from_table := 'msc_supplies';
3541 p_order_type_list := c_sup_defc_onhand;
3542 p_part_condition := c_part_bad;
3543 elsif (p_row_offset = c_row_returns) then --38;
3544 p_from_table := 'msc_supplies';
3545 p_order_type_list := c_sup_defc_returns;
3546 p_part_condition := c_part_bad;
3547 elsif (p_row_offset = c_row_defc_inbnd_ship) then --39;
3548 p_from_table := 'msc_supplies';
3549 p_order_type_list := c_sup_defc_inbnd_ship;
3550 p_part_condition := c_part_bad;
3551 elsif (p_row_offset = c_row_defc_plnd_inbnd_ship) then --40;
3552 p_from_table := 'msc_supplies';
3553 p_order_type_list := c_sup_defc_plnd_inbnd_ship;
3554 p_part_condition := c_part_bad;
3555 elsif (p_row_offset = c_row_defc_transit) then --41;
3556 p_from_table := 'msc_supplies';
3557 p_order_type_list := c_sup_defc_transit;
3558 p_part_condition := c_part_bad;
3559 elsif (p_row_offset = c_row_defc_rec) then --42;
3560 p_from_table := 'msc_supplies';
3561 p_order_type_list := c_sup_defc_rec;
3562 p_part_condition := c_part_bad;
3563 elsif (p_row_offset = c_row_total_defc_supply) then --43;
3564 p_from_table := 'msc_supplies';
3565 p_order_type_list := to_char(c_sup_defc_returns) ||','|| to_char(c_sup_defc_inbnd_ship) ||','|| to_char(c_sup_defc_onhand)
3566 ||','|| to_char(c_row_defc_plnd_inbnd_ship) ||','|| to_char(c_sup_defc_transit) ||','|| to_char(c_sup_defc_rec);
3567 p_part_condition := c_part_bad;
3568 elsif (p_row_offset = c_row_defc_pab) then --44;
3569 p_from_table := 'msc_orders_v';
3570 p_order_type_list := c_mbp_null_value;
3571 p_part_condition := c_part_bad;
3572 end if;
3573
3574 end if; -- }
3575
3576 return;
3577 end getOrderTypesList;
3578
3579 procedure getDrillDownDetails(p_view_type number, p_row_index number, p_row_offset number,
3580 p_date1 varchar2, p_date2 varchar2, p_from_table out nocopy varchar2, p_mfq_id out nocopy number) is
3581 l_row c_row_values_cur%rowtype;
3582 l_query_id number;
3583 l_order_type_list varchar2(1000);
3584
3585 l_reg_id_list varchar2(1000);
3586 l_org_id_list varchar2(1000);
3587 l_item_id_list varchar2(1000);
3588 sql_stmt varchar2(10000);
3589
3590 l_date1 date;
3591 l_date2 date;
3592 l_part_condition number;
3593 begin
3594 msc_sda_utils.println(' getDrillDownDetails in');
3595
3596 l_date1 := to_date(p_date1,c_date_format);
3597 l_date2 := to_date(p_date2,c_date_format);
3598
3599 l_query_id := getAnalysisQueryId(p_view_type);
3600 open c_row_values_cur(l_query_id, p_row_index, to_number(null));
3601 fetch c_row_values_cur into l_row;
3602 close c_row_values_cur;
3603
3604 if l_row.region_id is null then
3605 --getRegionList(l_reg_id_list);
3606 if ( l_row.region_list_id = c_all_region_type) then
3607 l_reg_id_list := ' and 1=1 ';
3608 end if;
3609 else
3610 l_reg_id_list := l_row.region_id;
3611 if ( l_row.region_id = c_global_reg_type) then
3612 l_reg_id_list := ' and organization_id in ('|| c_global_org_id ||') ';
3613 elsif ( l_row.region_id = c_local_reg_type) then
3614 l_reg_id_list := ' and zone_id is null ';
3615 else
3616 l_reg_id_list := ' and zone_id in ('|| l_reg_id_list ||') ';
3617 end if;
3618 end if;
3619
3620 if (l_row.inst_id is null) then
3621 getOrgList(l_org_id_list);
3622 else
3623 l_org_id_list := '('||l_row.inst_id||','||l_row.org_id||')';
3624 end if;
3625
3626 if (l_row.item_id is null) then
3627 getItemList(l_item_id_list);
3628 else
3629 l_item_id_list := l_row.item_id;
3630 end if;
3631
3632 getOrderTypesList(p_view_type, p_row_offset , l_order_type_list, p_from_table, l_part_condition);
3633 if (l_part_condition is null) then
3634 l_part_condition := c_part_good;
3635 end if;
3636
3637 if (p_from_table = 'msc_forecast_updates') then
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
3647 sql_stmt := sql_stmt || ' transaction_id from '|| p_from_table;
3648 elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
3649 sql_stmt := sql_stmt || ' demand_id from '|| p_from_table;
3650 elsif (p_from_table = 'msc_orders_v') then
3651 sql_stmt := sql_stmt || ' transaction_id from '|| p_from_table;
3652 end if;
3653
3654 sql_stmt := sql_stmt || ' where plan_id = '||g_plan_id ;
3655 if (l_reg_id_list is not null) then
3656 sql_stmt := sql_stmt || l_reg_id_list ; --special handling for region
3657 end if;
3658 if (l_org_id_list is not null) then
3659 if l_row.region_id = c_global_reg_type then
3660 null;
3661 else
3662 sql_stmt := sql_stmt || ' and (sr_instance_id,organization_id) in ('|| l_org_id_list ||') ';
3663 end if;
3664 end if;
3665 if (l_item_id_list is not null) then
3666 sql_stmt := sql_stmt || ' and inventory_item_id in ('|| l_item_id_list ||') ';
3667 end if;
3668
3669 if (p_from_table = 'msc_supplies') then
3670 sql_stmt := sql_stmt || ' and order_type in ('|| l_order_type_list ||')';
3671 sql_stmt := sql_stmt || ' and trunc(new_schedule_date) between trunc(:l_date1) and trunc(:l_date2) ';
3672 sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id
3673 ||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
3674
3675 msc_sda_utils.println('msc_supplies '||sql_stmt);
3676 execute immediate sql_stmt using l_date1, l_date2;
3677 elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
3678 sql_stmt := sql_stmt || ' and origination_type in ('|| l_order_type_list ||')';
3679 sql_stmt := sql_stmt || ' and trunc(using_assembly_demand_date) between trunc(:l_date1) and trunc(:l_date2) ';
3680 sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id
3681 ||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
3682
3683 if (p_view_type = c_sdview) then
3684 if (p_row_offset = c_row_so) then
3685 sql_stmt := sql_stmt || ' and disposition_id is null ';
3686 elsif (p_row_offset = c_row_iso_field_org) then
3687 sql_stmt := sql_stmt || ' and disposition_id is not null ';
3688 elsif (p_row_offset = c_row_iso) then
3689 sql_stmt := sql_stmt || ' and disposition_id is not null ';
3690 end if;
3691 end if;
3692
3693 msc_sda_utils.println('msc_demands, msc_forecast_updates '||sql_stmt);
3694 execute immediate sql_stmt using l_date1, l_date2;
3695 elsif (p_from_table = 'msc_orders_v') then
3696 sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id
3697 ||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
3698 if (l_order_type_list = c_mbp_null_value) then
3699 sql_stmt := sql_stmt || ' and trunc(new_due_date) <= trunc(:l_date2) ';
3700
3701 msc_sda_utils.println('msc_orders_v '||sql_stmt);
3702 execute immediate sql_stmt using l_date2;
3703 else
3704 sql_stmt := sql_stmt || ' and order_type in ('|| l_order_type_list ||')';
3705 sql_stmt := sql_stmt || ' and trunc(new_due_date) between trunc(:l_date1) and trunc(:l_date2) ';
3706 msc_sda_utils.println('msc_orders_v 2 '||sql_stmt);
3707 execute immediate sql_stmt using l_date1, l_date2;
3708 end if;
3709 else
3710 msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
3711 return;
3712 end if;
3713 msc_sda_utils.println('out '||sql_stmt);
3714 msc_sda_utils.println(' getDrillDownDetails out');
3715 end getDrillDownDetails;
3716
3717 -----
3718 ----- send apis done
3719 -----
3720
3721 function populateSupersessionChain(p_plan number, p_item number) return number is
3722 l_query_id number;
3723 begin
3724 l_query_id := msc_sda_utils.flushSupersessionChain(p_plan, p_item);
3725 return l_query_id;
3726 end populateSupersessionChain;
3727 end MSC_SDA_PKG;