[Home] [Help]
PACKAGE BODY: APPS.MSC_SDA_PKG
Source
1 package body MSC_SDA_PKG as
2 /* $Header: MSCSDAPB.pls 120.64 2011/03/14 21:24:23 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
88 --constants for view type
85 c_supply_type constant number := 1;
86 c_demand_type constant number := 2;
87
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)
184 c_srow_intrnst_rec constant integer := 20;
181
182 --supply rowtypes
183 c_srow_intrnst_shpmt constant integer := 10;
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 c_srow_inbnd_ship constant integer := 200;
204
205
206 --demand order types
207 c_dmd_pod constant integer := 1; -- Planned order demand
208 c_dmd_so_mds constant integer := 6; -- Sales order MDS
209 c_dmd_manual_mds constant integer := 8; -- Manual MDS
210 c_dmd_mps constant integer := 12; -- MPS demand
211 c_dmd_fcst constant integer := 29; -- Forecast
212 c_dmd_so constant integer := 30; -- Sales Orders
213 c_dmd_defc_iso constant integer := 30; --ISO, bad, defective outbound shipment
214 c_dmd_defc_pod constant integer := 1; --Planned order demand, bad
215 c_dmd_defc_part_dmd constant integer := 77; --Defective Part demand, bad
216 c_dmd_defc_plnd_part_dmd constant integer := 78; --Defective planned Part demand, bad
217 c_dmd_uncons_dmd constant integer := 83; --unconstrained demands
218
219 --demand rowtypes
220 c_drow_fcst constant integer := 500;
221 c_drow_so constant integer := 510;
222 c_drow_iso_field_org constant integer := 520;
223 c_drow_iso constant integer := 540;
224 c_drow_pod constant integer := 550;
225 c_drow_other_dmd constant integer := 570;
226 c_drow_defc_iso constant integer := 580;
227 c_drow_defc_pod integer := 590;
228 c_drow_defc_part_dmd integer := 600;
229 c_drow_uncons_dmd constant integer := 610;
230
231 --misc order types
232 c_max_level constant integer := 1000; --max level
233 c_ss_supply constant integer:= 1010; --Safety Stock (Days of Supply)
234 c_ss_level constant integer:= 1020; --Safety Stock Level
235 c_target_level constant integer:= 1030; --Target Level
236
237 -- pivot row types
238 c_row_net_fcst constant integer:= 1;
239 -- Net Forecast, demand-order-types 29
240 c_row_so constant integer:= 2;
241 --Sales Orders, demand-order-types 6,30
242 c_row_iso_field_org constant integer:= 3;
243 --Internal Sales Order (Field Org), demand-order-types 6,30
244 c_row_indepndt_dmd constant integer:= 4;
245 --Independent Demand, sum of pivot-row-types 1,2,3
246 c_row_iso constant integer:= 5;
247 --Internal Sales Order, demand-order-types 6,30
248 c_row_pod constant integer:= 6;
249 --Planned Outbound shipment, (planned order demand) demand-order-types 1
250 c_row_dependnt_dmd constant integer:= 7;
251 --Dependent Demand, sum of pivot-row-types 5,6
252 c_row_other_dmd constant integer:= 8;
253 --Other Demand, demand-order-types 8,12
254 c_row_total_dmd constant integer:= 9;
255 --Total Demand, sum of pivot-row-types 4,7,8
256 c_row_onhand constant integer:= 10;
257 --Beginning On-hand, supply-order-types 18(good)
258 c_row_transit constant integer:= 11;
259 --In - Transit, supply-order-types 11(good)
260 c_row_receiving constant integer:= 12;
261 --In - Receiving, supply-order-types 12(good)
262 c_row_new_buy_po constant integer:= 13;
263 --New Buy Purchase Order, supply-order-types 71(good)
264 c_row_new_buy_po_req constant integer:= 14;
265 --New Buy Purchase Requisitions, supply-order-types 72(good)
266 c_row_intrnl_rpr_ordr constant integer:= 15;
267 --Internal Repair Order, supply-order-types 73(good)
268 c_row_xtrnl_rpr_ordr constant integer:= 16;
269 --External Repair Order, supply-order-types 74(good)
270 c_row_inbnd_ship constant integer:= 17;
271 --Inbound shipment, supply-order-types 11(good)
272 --same as c_row_transit
273 -- pabram 05072010 will contain internal reqs
274 c_row_rpr_wo constant integer:= 18;
275 --Repair Work Order, supply-order-types 75(good)
276 c_row_plnd_new_buy_ordr constant integer:= 19;
277 --Planned New Buy Order, supply-order-types 76(good)
278 c_row_plnd_intrnl_rpr_ordr constant integer:= 20;
279 --Planned Internal Repair Order, supply-order-types 77(good)
280 c_row_plnd_xtrnl_rpr_ordr constant integer:= 21;
281 --Planned External Repair Order, supply-order-types 78(good)
282 c_row_plnd_inbnd_ship constant integer:= 22;
283 --Planned Inbound shipment, supply-order-types 51(good)
284 c_row_plnd_rpr_wo constant integer:= 23;
285 --Planned Repair Work Order, supply-order-types 79(good)
286 c_row_plnd_warr_ordr constant integer:= 24;
287 --Planned Warranty order, ?pabram....????????
288 c_row_total_supply constant integer:= 25;
289 --Total Supply, sum of pivot-row-types 11 thru 24
290 c_row_ss_supply constant integer:= 26;
291 --Safety Stock (Days of Supply), msc_safety_stocks.achieved_days_of_supply
292 --c_row_total_uncons_dmd constant integer:= 27;
293 --Total unconstrained demand --out of design
294 c_row_ss_level constant integer:= 28;
295 --Safety Stock Level, msc_safety_stocks.achieved_service_level
296 c_row_target_level constant integer:= 29;
297 --Target Level, msc_safety_stocks.target_service_level
298 c_row_max_level constant integer:= 30;
299 --Maximum Level, msc_inventory_levels.max_quantity
300 c_row_pab constant integer:= 31;
304 --Projected On Hand,
301 --Projected Available Balance,
302 --pivot-row-types c_row_pab(i-1) + c_row_total_supply(i) - c_row_total_dmd(i)
303 c_row_poh constant integer:= 32;
305 --pivot-row-types c_row_poh(i-1) + c_row_onhand(i)+ supply-order-type 13 - c_row_total_dmd(i)
306 --pabram..need to verify
307 c_row_defc_iso constant integer:= 33;
308 --Defective Outbound shipment, Defective ISO, demand-order-types 54(bad)
309 c_row_plnd_defc_pod constant integer:= 34;
310 --Planned Defective outbound shipment, (planned order demand) demand-order-types 1(bad)
311 c_row_defc_part_dmd constant integer:= 35;
312 --Defective Part demand, demand-order-types 77(bad)
313 c_row_total_defc_part_dmd constant integer:= 36;
314 --Total Defective Part demand, sum of pivot-row-types c_row_defc_part_dmd
315 --pabram..need to check, this is a duplicate
316 c_row_defc_onhand constant integer:= 37;
317 --Defective On-hand, supply-order-types 18(bad)
318 c_row_returns constant integer:= 38;
319 --Returns, supply-order-types 32(bad)
320 c_row_defc_inbnd_ship constant integer:= 39;
321 --Defective Inbound shipment, supply-order-types 11(bad)
322 c_row_defc_plnd_inbnd_ship constant integer:= 40;
323 --Planned defective Inbound shipment, supply-order-types 51(bad)
324 c_row_defc_transit constant integer:= 41;
325 --Defectives in transit, supply-order-types 11(bad)
326 c_row_defc_rec constant integer:= 42;
327 --Defectives in receiving, supply-order-types 12(bad)
328 c_row_total_defc_supply constant integer:= 43;
329 --Total Defective Supply, pivot-row-types sum of 38 thru 42
330 c_row_defc_pab constant integer:= 44;
331 --Projected Available Balance (Defective)
332 --pivot-row-types c_row_defc_pab(i-1) + c_row_total_defc_supply(i) - ( 33,34,35,36)
333 c_row_ss_qty constant integer:= 45;
334 --safety stock qty
335 c_row_uncons_dmd constant integer:= 46;
336 --uncons demand
337
338 --
339 -- supply/demand view ends
340 --
341
342 --
343 -- forecast view begins -- pabram need to work on this
344 --
345
346 --demand order types
347 c_dmd2_orig_fcst constant integer := 29; -- original forecast
348 c_dmd2_net_fcst constant integer := 29; -- net forecast
349 c_dmd2_manual_fcst constant integer := 63; -- manual fcst
350 c_dmd2_dmd_schd constant integer := 29; -- demand schedule
351 c_dmd2_bestfit_fcst constant integer := 65; -- bestfit forecast
352 c_dmd2_consm_qty constant integer := -1; -- consumed_qty
353 c_dmd2_overconsm_qty constant integer := -2; -- over consumed_qty
354 c_dmd2_popu_fcst constant integer := 29; -- population based forecast
355 c_dmd2_usage_fcst constant integer := 29; -- usage forecast
356
357 c_sup2_rtns_fcst constant integer := 81; -- Returns Forecast
358 c_sup2_rtns_manual_fcst constant integer := 83; -- Returns Manual Forecast
359 c_sup2_rtns_dmd_schd constant integer := 82; -- Returns Demand Schedule
360 c_sup2_rtns_bestfit_fcst constant integer := 84; -- Returns Best Fit Forecast
361
362 --demand rowtypes
363 c_drow2_net_fcst constant integer := 500;
364 c_drow2_manual_fcst constant integer := 510;
365 c_drow2_dmd_schd constant integer := 520;
366 c_drow2_bestfit_fcst constant integer := 530;
367 c_drow2_consm_qty constant integer := 540;
368 c_drow2_overconsm_qty constant integer := 550;
369 c_drow2_popu_fcst constant integer := 560;
370 c_drow2_usage_fcst constant integer := 570;
371
372 c_drow2_rtns_fcst constant integer := 600;
373 c_drow2_rtns_manual_fcst constant integer := 610;
374 c_drow2_rtns_dmd_schd constant integer := 620;
375 c_drow2_rtns_bestfit_fcst constant integer := 630;
376
377
378 -- pivot row types
379 c_row2_total_fcst constant integer:= 1;
380 -- nvl(manual forecast, net forecast + demand schedule)
381 c_row2_orig_fcst constant integer:= 2;
382 -- msc_demands --old_using_requirement_quantity, old_demand_quantity, original_quantity //pabram..need to check
383 c_row2_consumed_fcst constant integer:= 3;
384 -- msc_forecast_updates.consumed_qty //pabram..need to check
385 c_row2_net_fcst constant integer:= 4;
386 -- msc_demands origination_type 29 //pabram..need to check
387 c_row2_over_consmptn constant integer:= 5;
388 -- msc_forecast_updates.overconsumption_qty //pabram..need to check
389 c_row2_manual_fcst constant integer:= 6;
390 -- msc_demands origination_type 63 //pabram..need to check
391 c_row2_dmd_schd constant integer:= 7;
392 -- msc_demands origination_type 64 //pabram..need to check
393 c_row2_bestfit_fcst constant integer:= 8;
394 -- msc_demands origination_type 65 //pabram..need to check
395 c_row2_total_ret_fcst constant integer:= 9;
396 -- nvl(returns manual forecast, returns fcst + returns dmd schedule) //pabram..need to check
397 c_row2_ret_fcst constant integer:= 10;
398 -- msc_supplies order type 81
399 c_row2_ret_dmd_schd constant integer:= 11;
400 -- msc_supplies order type 82
401 c_row2_ret_manual_fcst constant integer:= 12;
402 -- msc_supplies order type 83
403 c_row2_ret_bestfit_fcst constant integer:= 13;
404 -- msc_supplies order type 84
405 c_row2_usage_fcst constant integer:= 14;
406 -- Usage Forecast
407 c_row2_popultn_fcst constant integer:= 15;
408 -- Population based Forecast
409 c_row2_type_16 constant integer:= 16;
410 -- dummy
411 c_row2_type_17 constant integer:= 17;
412 -- dummy 2
413 --
414 -- forecast view ends
415 --
416
417
418 --
419 -- history view begins -- pabram need to work on this
420 --
421 c_dmd_hist constant integer := 67; -- Demand History
422 c_returns_hist constant integer := 66; -- Returns History
423
427 -- pivot row types
424 c_drow_dmd_hist constant integer := 20;
425 c_drow_returns_hist constant integer := 10;
426
428 c_row_dmd_hist constant integer:= 1;
429 -- demand history ,msc_demands
430 c_row_returns_hist constant integer:= 2;
431 -- returns history ,msc_supplies
432 --
433 -- history view ends
434 --
435
436 -- global
437 g_sd_query_id number;
438 g_fcst_query_id number;
439 g_hist_query_id number;
440 g_hist_cal_query_id number;
441 g_view_type number;
442 g_region_query_id number;
443 g_chain_query_id number;
444 g_org_query_id number;
445 g_plan_bkts_query_id number;
446 g_region_type number;
447 g_region_list varchar2(250);
448 g_region_list_name varchar2(250);
449 g_org_type number;
450 g_org_list varchar2(250);
451 g_item_list number;
452 g_item_list_name varchar2(250);
453 g_item_view_type number;
454 g_row_index number := 1;
455 g_next_rowset_index number := 1;
456 g_fcst_bkt_mfq_id number;
457
458 g_md_dup_rows_qid number;
459 g_ms_dup_rows_qid number;
460
461 g_ss_query_id number;
462
463 g_plan_id number;
464 g_plan_type number;
465 g_plan_name varchar2(80);
466 g_owning_inst_id number;
467 g_owning_org_id number;
468 g_plan_start_date date;
469 g_plan_end_date date;
470
471 g_day_buckets number;
472 g_week_buckets number;
473 g_period_buckets number;
474 g_week_start_date date;
475 g_period_start_date date;
476
477 g_bkt_start_date msc_sda_utils.date_arr;
478 g_bkt_end_date msc_sda_utils.date_arr;
479 g_bkt_type msc_sda_utils.number_arr;
480 g_week_start_dates msc_sda_utils.date_arr;
481 g_period_start_dates msc_sda_utils.date_arr;
482
483 g_num_of_buckets number;
484 g_sd_num_of_buckets number;
485 g_fcst_num_of_buckets number;
486 g_hist_num_of_buckets number;
487
488 type bkttype_data is table of varchar2(80) index by binary_integer;
489 type rowtype_data is table of bkttype_data index by binary_integer;
490
491 g_data_grid rowtype_data;
492
493 --preferences
494 g_pref_id number;
495 g_pref_hist_start_date date;
496
497 --------
498 -------- cursors begin
499 --------
500 cursor c_row_values_cur (p_query_id number, p_row_index number, p_next_rowset_index number) is
501 select
502 row_index,
503 region_list_id,
504 region_list,
505 region_list_state,
506 region_id,
507 region_code,
508 org_list_id,
509 org_list,
510 org_list_state,
511 inst_id,
512 org_id,
513 org_code,
514 top_item_id,
515 top_item_name,
516 top_item_name_state,
517 item_id,
518 item_name
519 from msc_analysis_query maq
520 where maq.query_id = p_query_id
521 and ( (p_row_index is not null and maq.row_index = p_row_index) or
522 (p_next_rowset_index is not null and maq.parent_row_index = p_next_rowset_index) )
523 order by row_index;
524
525 cursor c_next_rowset_index_cur (p_query_id number) is
526 select nvl(max(parent_row_index),0)
527 from msc_analysis_query
528 where query_id = p_query_id;
529
530 cursor c_child_row_count (p_query_id number, p_next_rowset_index number) is
531 select count(*)
532 from msc_analysis_query
533 where query_id = p_query_id
534 and parent_row_index = p_next_rowset_index;
535 --------
536 -------- cursors end...
537 --------
538
539 procedure setUserPrefInfo is
540 begin
541 --pabram..need to change this later based on user pref code/values
542 g_pref_id := -1;
543 g_pref_hist_start_date := sysdate - 700;
544 end setUserPrefInfo;
545
546 procedure setPlanInfo is
547 cursor c_plan_info_cur is
548 select
549 compile_designator,
550 sr_instance_id,
551 organization_id,
552 decode(plan_id, -1, sysdate, trunc(curr_start_date)) curr_start_date,
553 decode(plan_id, -1, sysdate+365, trunc(curr_cutoff_date)) curr_cutoff_date
554 from msc_plans
555 where plan_id = g_plan_id;
556
557 cursor c_plan_bucket_info_cur is
558 select sum(decode(bucket_type, 1, 1,0)) day_buckets,
559 sum(decode(bucket_type, 2, 1,0)) week_buckets,
560 sum(decode(bucket_type, 3, 1,0)) period_buckets,
561 min(decode(bucket_type, 2, bkt_start_date)) week_start_date,
562 min(decode(bucket_type, 3, bkt_start_date)) pr_start_date
563 from msc_plan_buckets
564 where plan_id = g_plan_id
565 and sr_instance_id = g_owning_inst_id
566 and organization_id = g_owning_org_id;
567
568 cursor c_plan_bucket_dates_cur is
569 select bkt_start_date,
570 bkt_end_date,
571 bucket_type
572 from msc_plan_buckets
573 where plan_id = g_plan_id
574 and sr_instance_id = g_owning_inst_id
575 and organization_id = g_owning_org_id
576 union all
577 select trunc(curr_start_date)-1,
578 trunc(curr_start_date)-1,
579 -99
580 from msc_plans
581 where plan_id = g_plan_id
582 union all
583 select trunc(curr_cutoff_date)+1,
584 trunc(curr_cutoff_date)+1,
585 -99
586 from msc_plans
587 where plan_id = g_plan_id
588 order by 1;
589
590 cursor c_week_start_dates_cur(p_cal_type number, p_cal_code varchar2) is
591 select week_start_date
592 from msc_trading_partners mtp,
593 msc_cal_week_start_dates wsd
597 and mtp.partner_type = 3
594 where p_cal_type = c_owning_org_cal
595 and mtp.sr_tp_id = g_owning_org_id
596 and mtp.sr_instance_id = g_owning_inst_id
598 and mtp.calendar_code = wsd.calendar_code
599 and mtp.calendar_exception_set_id = wsd.exception_set_id
600 and mtp.sr_instance_id = wsd.sr_instance_id
601 and wsd.week_start_date >= g_plan_start_date
602 and wsd.week_start_date <= g_plan_end_date
603 union all
604 select mcwsd.week_start_date
605 from msc_cal_week_start_dates mcwsd
606 where p_cal_type = c_profile_cal
607 and mcwsd.calendar_code = p_cal_code
608 and mcwsd.week_start_date >= g_plan_start_date
609 and mcwsd.week_start_date <= g_plan_end_date
610 order by 1;
611
612 cursor c_period_start_dates_cur(p_cal_type number, p_cal_code varchar2) is
613 select mpsd.period_start_date
614 from msc_trading_partners mtp,
615 msc_period_start_dates mpsd
616 where p_cal_type = c_owning_org_cal
617 and mpsd.calendar_code = mtp.calendar_code
618 and mpsd.sr_instance_id = mtp.sr_instance_id
619 and mpsd.exception_set_id = mtp.calendar_exception_set_id
620 and mtp.sr_instance_id = g_owning_inst_id
621 and mtp.sr_tp_id = g_owning_org_id
622 and mtp.partner_type =3
623 and mpsd.period_start_date >= g_plan_start_date
624 and mpsd.period_start_date <= g_plan_end_date
625 union all
626 select mpsd.period_start_date
627 from msc_period_start_dates mpsd
628 where p_cal_type = c_profile_cal
629 and mpsd.calendar_code = p_cal_code
630 and mpsd.period_start_date >= g_plan_start_date
631 and mpsd.period_start_date <= g_plan_end_date
632 order by 1;
633
634 l_cal_code varchar2(250) := fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR');
635 l_cal_type number;
636
637 l_date1 date;
638 l_date2 date;
639
640 begin
641 if (g_view_type = c_sdview) then
642 l_cal_type := c_owning_org_cal;
643 else
644 if (l_cal_code is not null) then
645 l_cal_type := c_profile_cal;
646 else
647 l_cal_type := c_owning_org_cal;
648 end if;
649 end if;
650
651 --get plan info
652 open c_plan_info_cur;
653 fetch c_plan_info_cur into g_plan_name, g_owning_inst_id, g_owning_org_id,
654 g_plan_start_date, g_plan_end_date;
655 close c_plan_info_cur;
656
657 --get plan buckets info
658 open c_plan_bucket_info_cur;
659 fetch c_plan_bucket_info_cur into g_day_buckets, g_week_buckets,
660 g_period_buckets, g_week_start_date, g_period_start_date;
661 close c_plan_bucket_info_cur;
662
663 -- 2 is to store past and future values which are not part of current plan buckets
664 g_num_of_buckets := g_day_buckets + g_week_buckets + g_period_buckets + 1;
665
666 msc_sda_utils.println('setPlanInfo buckets - day week period total '||
667 g_day_buckets ||'-'|| g_week_buckets ||'-'|| g_period_buckets ||'-'|| g_num_of_buckets);
668
669 --get plan bucket dates
670 open c_plan_bucket_dates_cur;
671 fetch c_plan_bucket_dates_cur bulk collect
672 into g_bkt_start_date, g_bkt_end_date, g_bkt_type;
673 close c_plan_bucket_dates_cur;
674
675 --populate plan buckets into mfq
676 g_plan_bkts_query_id := msc_sda_utils.getNewFormQueryId;
677 for bktIndex in 1..g_bkt_type.count
678 loop
679 if ( g_bkt_type(bktIndex) = -99 and trunc(g_bkt_end_date(bktIndex)) = trunc(g_plan_start_date-1) ) then
680 l_date1 := g_plan_start_date - 1000; --approx 3 years from plan start
681 l_date2 := g_plan_start_date -1;
682 elsif ( g_bkt_type(bktIndex) = -99 and trunc(g_bkt_start_date(bktIndex)) = trunc(g_plan_end_date+1) ) then
683 l_date1 := g_plan_end_date + 1;
684 l_date2 := g_plan_end_date + 1000; --approx 3 years from plan end;
685 else
686 l_date1 := g_bkt_start_date(bktIndex);
687 l_date2 := g_bkt_end_date(bktIndex);
688 end if;
689 insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
690 values (g_plan_bkts_query_id , sysdate, -1, sysdate, -1, g_bkt_type(bktIndex), l_date1, l_date2);
691 end loop;
692
693 --get week dates
694 open c_week_start_dates_cur(l_cal_type, l_cal_code);
695 fetch c_week_start_dates_cur bulk collect into g_week_start_dates;
696 close c_week_start_dates_cur;
697
698 --get period dates
699 open c_period_start_dates_cur(l_cal_type, l_cal_code);
700 fetch c_period_start_dates_cur bulk collect into g_period_start_dates;
701 close c_period_start_dates_cur;
702
703 end setPlanInfo;
704
705 function isRowChanged(p_row_index number, p_prev_row_index number) return boolean is
706 l_flag boolean := true;
707 begin
708 if (p_row_index = p_prev_row_index ) then
709 l_flag := false;
710 end if;
711 return l_flag;
712 end isRowChanged;
713
714 function getFcstStartDate(p_bkt_start_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
715 begin
716 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
717 return trunc(p_plan_start_date)-1;
718 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
719 return trunc(p_plan_end_date)+1;
720 end if;
721 return trunc(p_bkt_start_date);
722 end getFcstStartDate;
723
724 function getFcstEndDate(p_bkt_end_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
725 begin
726 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
727 return trunc(p_plan_start_date)-1;
728 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
729 return trunc(p_plan_end_date)+1;
730 end if;
731 return trunc(p_bkt_end_date);
732 end getFcstEndDate;
733
734 function getFcstRowTypeOffset(p_supply_demand_flag number, p_order_type number,
735 p_item_type_id number, p_item_type_value number, p_type_flag number,
736 p_inst_id number, p_org_id number, p_region_id number, p_schedule_designator_id number, p_item_id number) return number is
737 l_row_type number;
738 l_offset number;
739 cursor c_usage_based_fcst_cur is
740 select count(*)
741 from msc_system_items msi,
742 msc_forecast_rules mfr
743 where msi.plan_id = g_plan_id
744 and msi.sr_instance_id = p_inst_id
745 and msi.organization_id = p_org_id
746 and msi.inventory_item_id = p_item_id
747 and msi.forecast_rule_for_demands = mfr.forecast_rule_id
748 and nvl(mfr.enable_usage_ship_fcst, 2) = 1
749 and nvl(mfr.history_basis,-1) in (3,4);
750 l_usage_type number;
751 begin
752 if (p_supply_demand_flag = c_demand_type) then --{
753 if (p_order_type = c_dmd2_dmd_schd and p_schedule_designator_id is not null) then --{
754 l_row_type := c_drow2_dmd_schd;
755 l_offset := c_row2_dmd_schd;
756 elsif (p_order_type = c_dmd2_manual_fcst ) then
757 l_row_type := c_drow2_manual_fcst;
758 l_offset := c_row2_manual_fcst;
759 elsif (p_order_type = c_dmd2_net_fcst) then
760 if (p_region_id is not null) then
761 l_row_type := c_drow2_usage_fcst;
762 l_offset := c_row2_usage_fcst;
763 elsif (p_region_id is null) then
764 open c_usage_based_fcst_cur;
765 fetch c_usage_based_fcst_cur into l_usage_type;
766 close c_usage_based_fcst_cur;
767 if (l_usage_type = 0) then
768 l_row_type := c_drow2_popu_fcst;
769 l_offset := c_row2_popultn_fcst;
770 else
771 l_row_type := c_drow2_usage_fcst;
772 l_offset := c_row2_usage_fcst;
773 end if;
774 else
775 l_row_type := c_drow2_net_fcst;
776 l_offset := c_row2_net_fcst;
777 end if;
778 elsif (p_order_type = c_dmd2_bestfit_fcst) then
779 --removed from the row type
780 l_row_type := c_drow2_bestfit_fcst;
781 l_offset := c_row2_bestfit_fcst;
782 else
783 l_row_type := c_discard;
784 l_offset := c_discard;
785 end if; --}
786 end if; --}
787
788 if (p_supply_demand_flag = c_supply_type) then --{
789 if (p_order_type = c_sup2_rtns_fcst) then --{
790 l_row_type := c_drow2_rtns_fcst;
791 l_offset := c_row2_ret_fcst;
792 elsif (p_order_type = c_sup2_rtns_manual_fcst) then --{
793 l_row_type := c_drow2_rtns_manual_fcst;
794 l_offset := c_row2_ret_manual_fcst;
795 elsif (p_order_type = c_sup2_rtns_dmd_schd) then --{
796 l_row_type := c_drow2_rtns_dmd_schd;
797 l_offset := c_row2_ret_dmd_schd;
798 elsif (p_order_type = c_sup2_rtns_bestfit_fcst) then --{
799 l_row_type := c_drow2_rtns_bestfit_fcst;
800 l_offset := c_row2_ret_bestfit_fcst;
801 else
802 l_row_type := c_discard;
803 l_offset := c_discard;
804 end if; --}
805 end if; --}
806
807 if (p_type_flag = c_row_type_flag) then
808 return l_row_type;
809 else
810 return l_offset;
811 end if;
812 end getFcstRowTypeOffset;
813
814 function getHistStartDate(p_bkt_start_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
815 begin
816 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
817 return trunc(p_plan_start_date)-1;
818 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
819 return trunc(p_plan_end_date)+1;
820 end if;
821 return trunc(p_bkt_start_date);
822 end getHistStartDate;
823
824 function getHistEndDate(p_bkt_end_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
825 begin
826 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
827 return trunc(p_plan_start_date)-1;
828 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
829 return trunc(p_plan_end_date)+1;
830 end if;
831 return trunc(p_bkt_end_date);
832 end getHistEndDate;
833
834 function getHistRowTypeOffset(p_order_type number, p_item_type_id number, p_item_type_value number,
835 p_type_flag number) return number is
836 l_row_type number;
837 l_offset number;
838 begin
839 if (p_order_type = c_returns_hist) then --{
840 l_row_type := c_drow_returns_hist;
841 l_offset := c_row_returns_hist;
842 elsif (p_order_type = c_dmd_hist) then --{
846 l_row_type := c_discard;
843 l_row_type := c_drow_dmd_hist;
844 l_offset := c_row_dmd_hist;
845 else
847 l_offset := c_discard;
848 end if; --}
849 if (p_type_flag = c_row_type_flag) then
850 return l_row_type;
851 else
852 return l_offset;
853 end if;
854 end getHistRowTypeOffset;
855
856 function getSDStartDate(p_bkt_start_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
857 begin
858 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
859 return trunc(p_plan_start_date)-1;
860 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
861 return trunc(p_plan_end_date)+1;
862 end if;
863 return trunc(p_bkt_start_date);
864 end getSDStartDate;
865
866 function getSDEndDate(p_bkt_end_date date, p_plan_start_date date, p_plan_end_date date, p_sd_date date) return date is
867 begin
868 if ( trunc(p_sd_date) < trunc(p_plan_start_date) ) then
869 return trunc(p_plan_start_date)-1;
870 elsif ( trunc(p_sd_date) > trunc(p_plan_end_date) ) then
871 return trunc(p_plan_end_date)+1;
872 end if;
873 return trunc(p_bkt_end_date);
874 end getSDEndDate;
875
876 function getSupplyRowTypeOffset(p_order_type number, p_item_type_id number, p_item_type_value number,
877 p_type_flag number, p_source_organization_id number) return number is
878 l_row_type number;
879 l_offset number;
880 begin
881 if (p_order_type = c_sup_onhand
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_onhand;
884 l_offset := c_row_onhand;
885 elsif (p_order_type = c_sup_intrnst_shpmt
886 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
887 l_row_type := c_srow_intrnst_shpmt;
888 l_offset := c_row_transit;
889 elsif (p_order_type = c_sup_intrnst_rec
890 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
891 l_row_type := c_srow_intrnst_rec;
892 l_offset := c_row_receiving;
893 elsif (p_order_type = c_sup_new_buy_po
894 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
895 l_row_type := c_srow_new_buy_po;
896 l_offset := c_row_new_buy_po;
897 elsif (p_order_type = c_sup_new_buy_po_req
898 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
899 if (p_source_organization_id is null and nvl(p_item_type_value,c_part_good) = c_part_good ) then
900 l_row_type := c_srow_new_buy_po_req;
901 l_offset := c_row_new_buy_po_req;
902 elsif (p_source_organization_id is not null and nvl(p_item_type_value,c_part_good) = c_part_good ) then
903 l_row_type := c_srow_inbnd_ship;
904 l_offset := c_row_inbnd_ship;
905 --elsif (p_source_organization_id is null and nvl(p_item_type_value,c_part_good) = c_part_bad ) then
906 --l_row_type := c_srow_defc_inbnd_ship;
907 --l_offset := c_row_defc_inbnd_ship;
908 -- defective items will not have new buy purchase reqs
909 elsif (p_source_organization_id is not null and nvl(p_item_type_value,c_part_good) = c_part_bad ) then
910 l_row_type := c_srow_defc_inbnd_ship;
911 l_offset := c_row_defc_inbnd_ship;
912 else
913 l_row_type := c_discard;
914 l_offset := c_discard;
915 end if;
916 elsif (p_order_type in (c_sup_intrnl_rpr_ordr)
917 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
918 l_row_type := c_srow_intrnl_rpr_ordr;
919 l_offset := c_row_intrnl_rpr_ordr;
920 elsif (p_order_type in (c_sup_xtrnl_rpr_ordr, c_sup_ext_rep_req)
921 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
922 l_row_type := c_srow_xtrnl_rpr_ordr;
923 l_offset := c_row_xtrnl_rpr_ordr;
924 elsif (p_order_type in ( c_sup_rpr_wo, c_sup_rpr_wo_ext_rep_supp)
925 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
926 l_row_type := c_srow_rpr_wo;
927 l_offset := c_row_rpr_wo;
928 elsif (p_order_type = c_sup_plnd_new_buy_ordr
929 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
930 l_row_type := c_srow_plnd_new_buy_ordr;
931 l_offset := c_row_plnd_new_buy_ordr;
932 elsif (p_order_type = c_sup_plnd_intrnl_rpr_ordr
933 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
934 l_row_type := c_srow_plnd_intrnl_rpr_ordr;
935 l_offset := c_row_plnd_intrnl_rpr_ordr;
936 elsif (p_order_type = c_sup_plnd_xtrnl_rpr_ordr
937 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
938 l_row_type := c_srow_plnd_xtrnl_rpr_ordr;
939 l_offset := c_row_plnd_xtrnl_rpr_ordr;
940 elsif (p_order_type = c_sup_plnd_inbnd_ship
941 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
942 l_row_type := c_srow_plnd_inbnd_ship;
943 l_offset := c_row_plnd_inbnd_ship;
944 elsif (p_order_type = c_sup_plnd_rpr_wo
948 elsif (p_order_type = c_sup_defc_plnd_inbnd_ship
945 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
946 l_row_type := c_srow_plnd_rpr_wo;
947 l_offset := c_row_plnd_rpr_wo;
949 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
950 l_row_type := c_srow_defc_plnd_inbnd_ship;
951 l_offset := c_row_defc_plnd_inbnd_ship;
952 elsif (p_order_type = c_sup_defc_onhand
953 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
954 l_row_type := c_srow_defc_onhand;
955 l_offset := c_row_defc_onhand;
956 elsif (p_order_type = c_sup_defc_returns
957 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
958 l_row_type := c_srow_defc_returns;
959 l_offset := c_row_returns;
960 --elsif (p_order_type = c_sup_defc_inbnd_ship
961 --and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
962 --l_row_type := c_srow_defc_inbnd_ship;
963 --l_offset := c_row_defc_inbnd_ship;
964 elsif (p_order_type = c_sup_defc_transit
965 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
966 l_row_type := c_srow_defc_transit;
967 l_offset := c_row_defc_transit;
968 elsif (p_order_type = c_sup_defc_rec
969 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
970 l_row_type := c_srow_defc_rec;
971 l_offset := c_row_defc_rec;
972 else
973 l_row_type := c_discard;
974 l_offset := c_discard;
975 end if; --}
976 if (p_type_flag = c_row_type_flag) then
977 return l_row_type;
978 else
979 return l_offset;
980 end if;
981 end getSupplyRowTypeOffset;
982
983 function getDemandRowTypeOffset(p_order_type number, p_item_type_id number, p_item_type_value number,
984 p_type_flag number, p_disposition_id number, p_org_type number) return number is
985 l_row_type number;
986 l_offset number;
987 begin
988 if (p_order_type = c_dmd_fcst
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_fcst;
991 l_offset := c_row_net_fcst;
992 elsif (p_order_type in (c_dmd_so, c_dmd_so_mds)
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 if (p_disposition_id is null) then
995 l_row_type := c_drow_so;
996 l_offset := c_row_so;
997 else
998 --6737751 bugfix, no dependency
999 if ( nvl(p_org_type,-1) = c_field_tech_org ) then
1000 l_row_type := c_drow_iso_field_org;
1001 l_offset := c_row_iso_field_org;
1002 else
1003 l_row_type := c_drow_iso;
1004 l_offset := c_row_iso;
1005 end if;
1006 end if;
1007 elsif (p_order_type in (c_dmd_mps, c_dmd_manual_mds)
1008 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
1009 l_row_type := c_drow_other_dmd;
1010 l_offset := c_row_other_dmd;
1011 elsif (p_order_type = c_dmd_pod
1012 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
1013 l_row_type := c_drow_pod;
1014 l_offset := c_row_pod;
1015 elsif (p_order_type = c_dmd_defc_iso
1016 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
1017 l_row_type := c_drow_defc_iso;
1018 l_offset := c_row_defc_iso;
1019 elsif (p_order_type = c_dmd_defc_pod
1020 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
1021 l_row_type := c_drow_defc_pod;
1022 l_offset := c_row_plnd_defc_pod;
1023 elsif (p_order_type in (c_dmd_defc_part_dmd, c_dmd_defc_plnd_part_dmd)
1024 and nvl(p_item_type_id,c_part_cond_id) = c_part_cond_id and p_item_type_value = c_part_bad) then
1025 l_row_type := c_drow_defc_part_dmd;
1026 l_offset := c_row_defc_part_dmd;
1027 elsif (p_order_type = c_dmd_uncons_dmd
1028 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
1029 l_row_type := c_drow_uncons_dmd;
1030 l_offset := c_row_uncons_dmd;
1031 else
1032 l_row_type := c_discard;
1033 l_offset := c_discard;
1034 end if; --}
1035 if (p_type_flag = c_row_type_flag) then --{
1036 return l_row_type;
1037 else
1038 return l_offset;
1039 end if; --}
1040 end getDemandRowTypeOffset;
1041
1042 procedure getTotalRowTypesBuckets(p_view_type number,
1043 p_total_row_types out nocopy number, p_total_buckets out nocopy number) is
1044 begin
1045 if (p_view_type = c_sdview) then
1046 p_total_row_types := c_sd_total_row_types;
1047 p_total_buckets := g_sd_num_of_buckets;
1048 elsif (p_view_type = c_fcstview) then
1049 p_total_row_types := c_fcst_total_row_types;
1050 p_total_buckets := g_fcst_num_of_buckets;
1051 elsif (p_view_type = c_histview) then
1052 p_total_row_types := c_hist_total_row_types;
1053 p_total_buckets := g_hist_num_of_buckets;
1054 end if;
1055 end getTotalRowTypesBuckets;
1056
1060 if (p_view_type = c_sdview) then
1057 function getAnalysisQueryId(p_view_type number) return number is
1058 l_query_id number;
1059 begin
1061 l_query_id := g_sd_query_id;
1062 elsif (p_view_type = c_fcstview) then
1063 l_query_id := g_fcst_query_id;
1064 elsif (p_view_type = c_histview) then
1065 l_query_id := g_hist_query_id;
1066 end if;
1067 return l_query_id;
1068 end getAnalysisQueryId;
1069
1070 procedure getDataStreamLabel(p_view_type number, p_stream_name out nocopy varchar2) is
1071 begin
1072 if (p_view_type = c_sdview) then
1073 p_stream_name := c_sdview_data;
1074 elsif (p_view_type = c_fcstview) then
1075 p_stream_name := c_fcstview_data;
1076 elsif (p_view_type = c_histview) then
1077 p_stream_name := c_histview_data;
1078 end if;
1079 end getDataStreamLabel;
1080
1081 procedure initGrid (p_view_type number) is
1082 l_total_row_types number;
1083 l_total_buckets number;
1084 begin
1085 getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1086 -- initialize the bucket to zeros for new item
1087 for rowTypeIndex in 1..l_total_row_types
1088 loop --{
1089 for bktIndex in 1..l_total_buckets
1090 loop
1091 g_data_grid(rowTypeIndex)(bktIndex) := to_number(null);
1092 end loop;
1093 end loop; --}
1094 end initGrid;
1095
1096 procedure addDataToGrid(p_grid_row_index number, p_grid_column_index number, p_qty number,
1097 p_view_type number) is
1098 l_cur_qty number;
1099 l_total_row_types number;
1100 l_total_buckets number;
1101 begin
1102 msc_sda_utils.println(' addDataToGrid p_grid_row_index p_grid_column_index '
1103 || p_grid_row_index ||' - '|| p_grid_column_index );
1104
1105 getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1106
1107 --6606958 there was data beyond plan end_date, stopping that here
1108 if (p_grid_column_index >l_total_buckets) then
1109 return;
1110 end if;
1111 l_cur_qty := g_data_grid(p_grid_row_index)(p_grid_column_index);
1112 if (l_cur_qty is not null or p_qty is not null) then
1113 g_data_grid(p_grid_row_index)(p_grid_column_index) :=
1114 nvl(g_data_grid(p_grid_row_index)(p_grid_column_index), 0) + nvl(p_qty, 0);
1115 end if;
1116 end addDataToGrid;
1117
1118 /*
1119 procedure printStream(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1120 begin
1121 msc_sda_utils.println('printStream in - count '||p_out_data.count);
1122 for i in 1..p_out_data.count
1123 loop
1124 for j in 0..320
1125 loop
1126 msc_sda_utils.println('start i -'||i||'-j'||j*100||' - '||substr(p_out_data(i),j*100,100));
1127 end loop;
1128 end loop;
1129 msc_sda_utils.println('printStream out');
1130 end printStream;
1131 */
1132
1133
1134 procedure flushToStream(p_row_index number, p_out_data_index in out nocopy number,
1135 p_out_data in out nocopy msc_sda_utils.maxCharTbl, p_view_type number) is
1136
1137 l_one_record varchar2(32000) := null;
1138 l_total_row_types number;
1139 l_total_buckets number;
1140 l_stream_name varchar2(100);
1141
1142 begin
1143 msc_sda_utils.println('flushToStream in');
1144
1145 getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1146 getDataStreamLabel(p_view_type, l_stream_name);
1147
1148 --prepare the data stream
1149 for bktIndex in 1..l_total_buckets
1150 loop --{
1151 for rowTypeIndex in 1..l_total_row_types
1152 loop
1153 declare
1154 ll_grid_data_char varchar2(200);
1155 begin
1156 ll_grid_data_char := nvl(to_char(fnd_number.number_to_canonical(g_data_grid(rowTypeIndex)(bktIndex))), c_null_space);
1157
1158 if (l_one_record is null) then
1159 l_one_record := c_record_seperator || p_row_index || c_field_seperator || ll_grid_data_char;
1160 else
1161 if ( nvl(length(l_one_record || c_field_seperator || ll_grid_data_char),0) < 31980 ) then
1162 if (l_one_record = 'xxx') then
1163 l_one_record := null;
1164 end if;
1165 l_one_record := l_one_record || c_field_seperator || ll_grid_data_char;
1166 end if;
1167 end if;
1168
1169 if ( nvl(length(l_one_record || c_field_seperator || ll_grid_data_char),0) >= 31980
1170 or (rowTypeIndex = l_total_row_types and bktIndex = l_total_buckets) ) then
1171 if (nvl(length(p_out_data(1)),0) = 1) then
1172 l_one_record := l_stream_name || c_bang_separator || l_one_record;
1173 --else
1174 --l_one_record := c_record_seperator || l_one_record;
1175 end if;
1176 msc_sda_utils.addToOutStream(l_one_record, p_out_data_index, p_out_data, 1);
1177 l_one_record := 'xxx';
1178 end if;
1179 end;
1180 end loop;
1181 end loop; --}
1182 --printStream(p_out_data);
1183 msc_sda_utils.println('flushToStream out');
1184 end flushToStream;
1185
1186 procedure calculateFcstTotals is
1187 l_total_row_types number;
1188 l_total_buckets number;
1189 l_row2_manual_fcst number;
1190 l_row2_net_fcst number;
1191
1192 l_row2_ret_manual_fcst number;
1193 l_row2_ret_fcst number;
1194 begin
1195 msc_sda_utils.println('calculateFcstTotals in');
1199 loop --{
1196 getTotalRowTypesBuckets(c_fcstview, l_total_row_types, l_total_buckets);
1197
1198 for bktIndex in 1..l_total_buckets
1200
1201 -- calculate totals
1202
1203 -- total forecast
1204 l_row2_manual_fcst := g_data_grid(c_row2_manual_fcst)(bktIndex);
1205 l_row2_net_fcst := nvl(g_data_grid(c_row2_net_fcst)(bktIndex), 0)
1206 + nvl(g_data_grid(c_row2_dmd_schd)(bktIndex), 0)
1207 + nvl(g_data_grid(c_row2_usage_fcst)(bktIndex), 0)
1208 + nvl(g_data_grid(c_row2_popultn_fcst)(bktIndex), 0);
1209
1210 --6904437 bugfix..
1211 g_data_grid(c_row2_net_fcst)(bktIndex) := l_row2_net_fcst;
1212
1213 --if ( nvl(l_row2_manual_fcst, l_row2_net_fcst) <> 0 ) then
1214 g_data_grid(c_row2_total_fcst)(bktIndex) := nvl(l_row2_manual_fcst, l_row2_net_fcst);
1215 --end if;
1216 msc_sda_utils.println('c_row2_total_fcst l_row2_manual_fcst, l_row2_net_fcst'||c_row2_total_fcst||' - '||l_row2_manual_fcst||' - '||l_row2_net_fcst);
1217
1218 -- total returns forecast
1219 l_row2_ret_manual_fcst := g_data_grid(c_row2_ret_manual_fcst)(bktIndex);
1220 l_row2_ret_fcst := nvl(g_data_grid(c_row2_ret_fcst)(bktIndex), 0)
1221 + nvl(g_data_grid(c_row2_ret_dmd_schd)(bktIndex), 0);
1222
1223 --if ( nvl(l_row2_ret_manual_fcst, l_row2_ret_fcst) <> 0 ) then
1224 g_data_grid(c_row2_total_ret_fcst)(bktIndex) := nvl(l_row2_ret_manual_fcst, l_row2_ret_fcst);
1225 --end if;
1226
1227 end loop; --}
1228 msc_sda_utils.println('calculateFcstTotals out');
1229 end calculateFcstTotals;
1230
1231 procedure calculateSDTotals is
1232 l_total_row_types number;
1233 l_total_buckets number;
1234 l_ss_last_bucket_index number;
1235 begin
1236 msc_sda_utils.println('calculateSDTotals in');
1237
1238 getTotalRowTypesBuckets(c_sdview, l_total_row_types, l_total_buckets);
1239
1240 for bktIndex in 1..l_total_buckets
1241 loop --{
1242
1243 -- calculate totals
1244
1245 -- demand totals
1246
1247 -- Independent Demand -- ISO-field org + Sales orders + Net forecast
1248 g_data_grid(c_row_indepndt_dmd)(bktIndex) :=
1249 nvl(g_data_grid(c_row_net_fcst)(bktIndex), 0)
1250 + nvl(g_data_grid(c_row_so)(bktIndex), 0)
1251 + nvl(g_data_grid(c_row_iso_field_org)(bktIndex), 0);
1252
1253 -- Dependent Demand -- ISO + planned order demand
1254 g_data_grid(c_row_dependnt_dmd)(bktIndex) :=
1255 nvl(g_data_grid(c_row_iso)(bktIndex), 0)
1256 + nvl(g_data_grid(c_row_pod)(bktIndex), 0);
1257
1258 -- Total Demand -- c_row_indepndt_dmd + c_row_dependnt_dmd + c_row_other_dmd
1259 g_data_grid(c_row_total_dmd)(bktIndex) :=
1260 nvl(g_data_grid(c_row_indepndt_dmd)(bktIndex), 0)
1261 + nvl(g_data_grid(c_row_dependnt_dmd)(bktIndex), 0)
1262 + nvl(g_data_grid(c_row_other_dmd)(bktIndex), 0);
1263
1264 -- Total supply -- row-types 11 thru 24
1265 g_data_grid(c_row_total_supply)(bktIndex) :=
1266 nvl(g_data_grid(c_row_transit)(bktIndex), 0)
1267 + nvl(g_data_grid(c_row_receiving)(bktIndex), 0)
1268 + nvl(g_data_grid(c_row_new_buy_po)(bktIndex), 0)
1269 + nvl(g_data_grid(c_row_new_buy_po_req)(bktIndex), 0)
1270 + nvl(g_data_grid(c_row_intrnl_rpr_ordr)(bktIndex), 0)
1271 + nvl(g_data_grid(c_row_xtrnl_rpr_ordr)(bktIndex), 0)
1272 + nvl(g_data_grid(c_row_inbnd_ship)(bktIndex), 0)
1273 + nvl(g_data_grid(c_row_rpr_wo)(bktIndex), 0)
1274 + nvl(g_data_grid(c_row_plnd_new_buy_ordr)(bktIndex), 0)
1275 + nvl(g_data_grid(c_row_plnd_intrnl_rpr_ordr)(bktIndex), 0)
1276 + nvl(g_data_grid(c_row_plnd_xtrnl_rpr_ordr)(bktIndex), 0)
1277 + nvl(g_data_grid(c_row_plnd_inbnd_ship)(bktIndex), 0)
1278 + nvl(g_data_grid(c_row_plnd_rpr_wo)(bktIndex), 0)
1279 + nvl(g_data_grid(c_row_plnd_warr_ordr)(bktIndex), 0)
1280 + nvl(g_data_grid(c_row_onhand)(bktIndex), 0);
1281
1282 --Total Defective Part demand - c_row_defc_part_dmd pabram..need to check
1283 g_data_grid(c_row_total_defc_part_dmd)(bktIndex) :=
1284 nvl(g_data_grid(c_row_defc_part_dmd)(bktIndex), 0);
1285
1286 --Total Defective Supply, pivot-row-types sum of 38 thru 42
1287 g_data_grid(c_row_total_defc_supply)(bktIndex) :=
1288 nvl(g_data_grid(c_row_returns)(bktIndex), 0)
1289 + nvl(g_data_grid(c_row_defc_inbnd_ship)(bktIndex), 0)
1290 + nvl(g_data_grid(c_row_defc_plnd_inbnd_ship)(bktIndex), 0)
1291 + nvl(g_data_grid(c_row_defc_transit)(bktIndex), 0)
1292 + nvl(g_data_grid(c_row_defc_rec)(bktIndex), 0)
1293 + nvl(g_data_grid(c_row_defc_onhand)(bktIndex), 0);
1294
1295 --PAB - PAB(i-1) + onhand(i) + total-supply(i) - total-demand(i)
1296 if (bktIndex = 1) then
1297 g_data_grid(c_row_pab)(bktIndex) :=
1298 nvl(g_data_grid(c_row_total_supply)(bktIndex), 0)
1299 - nvl(g_data_grid(c_row_total_dmd)(bktIndex), 0);
1300
1301 -- nvl(g_data_grid(c_row_onhand)(bktIndex), 0) +
1302 --c_row_onhand is already part of c_row_total_supply
1303 --bug 6527725 fix
1304 else
1305 g_data_grid(c_row_pab)(bktIndex) :=
1306 nvl(g_data_grid(c_row_pab)(bktIndex-1), 0)
1307 + nvl(g_data_grid(c_row_total_supply)(bktIndex), 0)
1308 - nvl(g_data_grid(c_row_total_dmd)(bktIndex), 0);
1309
1310 -- + nvl(g_data_grid(c_row_onhand)(bktIndex), 0)
1311 --c_row_onhand is already part of c_row_total_supply
1312 --bug 6527725 fix
1313 end if;
1314
1315 --POH - POH(i-1) + onhand(i) + total-supply-without-planned-order-types(i) - total-demand(i)
1316 if (bktIndex = 1) then
1317 g_data_grid(c_row_poh)(bktIndex) :=
1318 nvl(g_data_grid(c_row_onhand)(bktIndex), 0)
1319 + nvl(g_data_grid(c_row_transit)(bktIndex), 0)
1323 + nvl(g_data_grid(c_row_intrnl_rpr_ordr)(bktIndex), 0)
1320 + nvl(g_data_grid(c_row_receiving)(bktIndex), 0)
1321 + nvl(g_data_grid(c_row_new_buy_po)(bktIndex), 0)
1322 + nvl(g_data_grid(c_row_new_buy_po_req)(bktIndex), 0)
1324 + nvl(g_data_grid(c_row_xtrnl_rpr_ordr)(bktIndex), 0)
1325 + nvl(g_data_grid(c_row_inbnd_ship)(bktIndex), 0)
1326 + nvl(g_data_grid(c_row_rpr_wo)(bktIndex), 0)
1327 - nvl(g_data_grid(c_row_total_dmd)(bktIndex), 0);
1328 else
1329 g_data_grid(c_row_poh)(bktIndex) :=
1330 nvl(g_data_grid(c_row_poh)(bktIndex-1), 0)
1331 + nvl(g_data_grid(c_row_onhand)(bktIndex), 0)
1332 + nvl(g_data_grid(c_row_transit)(bktIndex), 0)
1333 + nvl(g_data_grid(c_row_receiving)(bktIndex), 0)
1334 + nvl(g_data_grid(c_row_new_buy_po)(bktIndex), 0)
1335 + nvl(g_data_grid(c_row_new_buy_po_req)(bktIndex), 0)
1336 + nvl(g_data_grid(c_row_intrnl_rpr_ordr)(bktIndex), 0)
1337 + nvl(g_data_grid(c_row_xtrnl_rpr_ordr)(bktIndex), 0)
1338 + nvl(g_data_grid(c_row_inbnd_ship)(bktIndex), 0)
1339 + nvl(g_data_grid(c_row_rpr_wo)(bktIndex), 0)
1340 - nvl(g_data_grid(c_row_total_dmd)(bktIndex), 0);
1341 end if;
1342
1343 --PAB (Defective) - pab-defc(i-1) + onhand-defc(i) + total-supply-defc(i)
1344 -- - (c_row_defc_iso + c_row_plnd_defc_pod + c_row_defc_part_dmd + c_row_total_defc_part_dmd),
1345 if (bktIndex = 1) then
1346 g_data_grid(c_row_defc_pab)(bktIndex) :=
1347 nvl(g_data_grid(c_row_total_defc_supply)(bktIndex), 0)
1348 - (nvl(g_data_grid(c_row_defc_iso)(bktIndex), 0)
1349 + nvl(g_data_grid(c_row_plnd_defc_pod)(bktIndex), 0)
1350 + nvl(g_data_grid(c_row_total_defc_part_dmd)(bktIndex), 0));
1351 else
1352 g_data_grid(c_row_defc_pab)(bktIndex) :=
1353 nvl(g_data_grid(c_row_defc_pab)(bktIndex-1), 0)
1354 + nvl(g_data_grid(c_row_total_defc_supply)(bktIndex), 0)
1355 - (nvl(g_data_grid(c_row_defc_iso)(bktIndex), 0)
1356 + nvl(g_data_grid(c_row_plnd_defc_pod)(bktIndex), 0)
1357 + nvl(g_data_grid(c_row_total_defc_part_dmd)(bktIndex), 0));
1358 end if;
1359
1360 /*
1364 end if;
1361 --find the valid safety stock qty entered by user
1362 if (g_data_grid(c_row_ss_qty)(bktIndex) is not null) then
1363 l_ss_last_bucket_index := bktIndex;
1365 */
1366 --6400965 bugfix
1367 --6867580 bugfix, if the qty 0, show prev bucket value
1368 if (bktIndex > 1) then
1369 /*
1370 if ( nvl(g_data_grid(c_row_ss_qty)(bktIndex),0) = 0) then
1371 g_data_grid(c_row_ss_qty)(bktIndex) := g_data_grid(c_row_ss_qty)(bktIndex-1);
1372 end if;
1373 */
1374 if ( g_data_grid(c_row_ss_qty)(bktIndex) is null ) then
1375 --and g_data_grid(c_row_ss_qty)(bktIndex-1) <> 0 ) then
1376 --commented above condition as part of populatessDataNew chagnes
1377 g_data_grid(c_row_ss_qty)(bktIndex) := g_data_grid(c_row_ss_qty)(bktIndex-1);
1378 end if;
1379
1380 end if;
1381
1382 end loop; --}
1383
1384 /*
1385 if ( nvl(l_ss_last_bucket_index,c_mbp_null_value) > 0) then
1386 --special logic for safety stock qty - filling in the gaps
1387 for bktIndex in 1..l_ss_last_bucket_index
1388 loop --{
1389 if (bktIndex > 1) then
1390 if ( nvl(g_data_grid(c_row_ss_qty)(bktIndex),c_mbp_null_value) = c_mbp_null_value ) then
1391 g_data_grid(c_row_ss_qty)(bktIndex) := g_data_grid(c_row_ss_qty)(bktIndex-1);
1392 end if;
1393 end if;
1394 end loop; --}
1395 end if;
1396 */
1397
1398 msc_sda_utils.println('calculateSDTotals out');
1399 end calculateSDTotals;
1400
1401 function get_fcst_bucket_index(p_bucket_date date) return number is
1402 l_bkt_index number := -1;
1403 l_found boolean := false;
1404 begin
1405 for bktIndex in 2..(g_bkt_start_date.count-1)
1406 loop
1407 msc_sda_utils.println('get_fcst_bucket_index bktIndex '||bktIndex
1408 ||' :: bucket_date '|| to_char(p_bucket_date,c_datetime_format)
1409 ||' :: bkt_start_date '|| to_char(g_bkt_start_date(bktIndex),c_datetime_format) );
1410
1411 if bktIndex = 2 and trunc(p_bucket_date) <= g_bkt_start_date(bktIndex) then
1412 l_found := true;
1413 l_bkt_index := 1;
1414 end if;
1415 if bktIndex = (g_bkt_start_date.count-1) and trunc(p_bucket_date) >= g_bkt_start_date(bktIndex) then
1416 l_found := true;
1417 l_bkt_index := g_bkt_start_date.count;
1418 end if;
1419 if trunc(p_bucket_date) >= g_bkt_start_date(bktIndex) and trunc(p_bucket_date) < g_bkt_start_date(bktIndex+1) then
1420 l_found := true;
1421 l_bkt_index := bktIndex;
1422 end if;
1423 if (l_found) then
1424 msc_sda_utils.println(' found index '||l_bkt_index);
1425 exit;
1426 end if;
1427 end loop;
1428 return l_bkt_index;
1429 end get_fcst_bucket_index;
1430
1431 /*
1432 procedure populate_fcst_bkts_to_mfq is
1433 l_start_date date;
1434 l_end_date date;
1435 l_bkt_index number := 1;
1436 begin
1437 g_fcst_bkt_mfq_id := msc_sda_utils.getNewFormQueryId;
1438 msc_sda_utils.println('populate_fcst_bkts_to_mfq query_id '||g_fcst_bkt_mfq_id);
1439
1440 for bktIndex in 1..g_bkt_start_date.count
1441 loop
1442 if l_start_date is null then
1443 l_start_date := g_bkt_start_date(bktIndex);
1444 else
1445 l_end_date := g_bkt_start_date(bktIndex);
1446 insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
1447 values (g_fcst_bkt_mfq_id , sysdate, -1, sysdate, -1, l_bkt_index, l_start_date, l_end_date);
1448 l_start_date := l_end_date;
1449 l_bkt_index := l_bkt_index + 1;
1450 end if;
1451 end loop;
1452
1453 if l_end_date is not null then
1454 insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
1455 values (g_fcst_bkt_mfq_id , sysdate, -1, sysdate, -1, l_bkt_index, l_start_date, l_end_date);
1456 end if;
1457 end populate_fcst_bkts_to_mfq;
1458 */
1459
1460 procedure flushAndSendAddlData(p_view_type number, p_query_id number,
1461 p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1462
1463 --------------------------------------
1464 --- FORECAST VIEW ADDL ROWS CUSROR ---
1465 --------------------------------------
1466 cursor fcst_addl_snapshot_cur is
1467 select
1468 maq.row_index,
1469 c_row2_type_16 row_type,
1470 md.using_assembly_demand_date new_date,
1471 md.original_item_id due_item_id,
1472 msc_get_name.item_name(md.original_item_id, null, null, null) due_item_name,
1473 sum(decode(md.assembly_demand_comp_date,
1474 null, decode(md.origination_type,
1475 29,(nvl(md.probability,1)* md.using_requirement_quantity),
1476 31, 0,
1477 md.using_requirement_quantity),
1478 decode(md.origination_type,
1479 29,(nvl(md.probability,1)* md.daily_demand_rate),
1480 31, 0,
1481 md.daily_demand_rate)))/
1482 decode(nvl(least(sum(decode(md.origination_type,
1483 29,nvl(md.probability,0),
1484 null)),
1485 1) ,1),
1486 0,1,
1487 nvl(least(sum(decode(md.origination_type,
1488 29,nvl(md.probability,0),
1489 null)) ,1) ,1)) new_quantity
1493 msc_form_query mfq2, -- item-list
1490 from
1491 msc_demands md,
1492 msc_analysis_query maq,
1494 msc_plans mp,
1495 msc_form_query mfq3 --g_plan_bkts_query_id
1496 where maq.query_id = p_query_id
1497 and maq.region_id = c_global_reg_type
1498 and mfq2.query_id = g_chain_query_id
1499 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
1500 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1501 and md.plan_id = g_plan_id
1502 --and md.sr_instance_id = c_global_inst_id
1503 and md.organization_id = c_global_org_id
1504 and md.inventory_item_id = mfq2.number2
1505 and md.zone_id = c_global_reg_id
1506 and md.plan_id = mp.plan_id
1507 and mfq3.query_id = g_plan_bkts_query_id
1508 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
1509 and md.origination_type in (c_dmd2_popu_fcst)
1510 group by
1511 maq.row_index,
1512 c_row2_type_16,
1513 md.using_assembly_demand_date,
1514 md.original_item_id,
1515 msc_get_name.item_name(md.original_item_id, null, null, null)
1516 order by 1,2,3,4;
1517
1518 l_one_record varchar2(2000);
1519 l_out_data_index number := 1;
1520
1521 ll_row_index msc_sda_utils.number_arr;
1522 ll_row_type msc_sda_utils.number_arr;
1523 ll_new_date msc_sda_utils.date_arr;
1524 ll_due_item_id msc_sda_utils.number_arr;
1525 ll_due_item_name msc_sda_utils.char_arr;
1526 ll_new_quantity msc_sda_utils.number_arr;
1527
1528 l_cur_row_index number := c_mbp_null_value;
1529 hasRowChanged boolean;
1530 firstRecord boolean := false;
1531
1532 l_bkt_index number;
1533 begin
1534 open fcst_addl_snapshot_cur;
1535 fetch fcst_addl_snapshot_cur bulk collect into ll_row_index,
1536 ll_row_type, ll_new_date, ll_due_item_id, ll_due_item_name, ll_new_quantity;
1537 close fcst_addl_snapshot_cur;
1538
1539 for rIndex in 1 .. ll_row_index.count
1540 loop --{
1541 l_bkt_index := get_fcst_bucket_index(ll_new_date(rIndex));
1542 l_one_record := nvl(to_char(ll_row_index(rIndex)),c_null_space)
1543 || c_field_seperator || nvl(to_char(ll_row_type(rIndex)),c_null_space)
1544 --|| c_field_seperator || nvl(to_char(ll_new_date(rIndex), c_date_format), c_null_space)
1545 || c_field_seperator || nvl(to_char(l_bkt_index), c_null_space)
1546 || c_field_seperator || nvl(to_char(ll_new_quantity(rIndex)), c_null_space)
1547 || c_field_seperator || nvl(to_char(ll_due_item_id(rIndex)), c_null_space)
1548 || c_field_seperator || nvl(msc_sda_utils.escapeSplChars(ll_due_item_name(rIndex)), c_null_space);
1549
1550 msc_sda_utils.println(' l_one_record '||l_one_record);
1551 if (rIndex = 1) then --{
1552 if (l_cur_row_index = c_mbp_null_value) then
1553 l_cur_row_index := ll_row_index(rIndex);
1554 end if;
1555 l_one_record := c_fcstview_addl_data || c_bang_separator || c_record_seperator || l_one_record;
1556 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1557 else
1558 hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
1559 if (hasRowChanged ) then
1560 l_one_record := ll_row_index(rIndex) || c_record_seperator || l_one_record;
1561 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1562 l_cur_row_index := ll_row_index(rIndex);
1563 else
1564 msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1565 end if;
1566 end if; --}
1567 end loop; --}
1568 end flushAndSendAddlData;
1569
1570 procedure populateSSDataNew is
1571 cursor c_ss_data is
1572 select
1573 mss.plan_id||' - '||mss.sr_instance_id||' - '||mss.organization_id||' - '||mss.inventory_item_id row_index,
1574 mss.plan_id,
1575 mss.sr_instance_id,
1576 mss.organization_id,
1577 mss.inventory_item_id,
1578 mss.period_start_date,
1579 sum(mss.achieved_days_of_supply) achieved_days_of_supply,
1580 sum(mss.safety_stock_quantity) safety_stock_quantity
1581 from msc_safety_stocks mss,
1582 msc_form_query mfq1, -- org-list
1583 msc_form_query mfq2 -- item-list
1584 where mss.plan_id= g_plan_id
1585 and mfq1.query_id = g_org_query_id
1586 and mfq1.number2 = mss.sr_instance_id
1587 and mfq1.number3 = mss.organization_id
1588 and mfq2.query_id = g_chain_query_id
1589 and mfq2.number2 = mss.inventory_item_id
1590 group by mss.plan_id,
1591 mss.sr_instance_id,
1592 mss.organization_id,
1593 mss.inventory_item_id,
1594 mss.period_start_date
1595 order by
1596 mss.plan_id,
1597 mss.sr_instance_id,
1598 mss.organization_id,
1599 mss.inventory_item_id,
1600 mss.period_start_date;
1601
1602 ll_row_index msc_sda_utils.char_arr;
1603 ll_plan_id msc_sda_utils.number_arr;
1604 ll_inst_id msc_sda_utils.number_arr;
1605 ll_org_id msc_sda_utils.number_arr;
1606 ll_item_id msc_sda_utils.number_arr;
1607 ll_date msc_sda_utils.date_arr;
1608 ll_qty1 msc_sda_utils.number_arr;
1609 ll_qty2 msc_sda_utils.number_arr;
1610
1611 cursor c_mpb_data is
1612 select
1613 to_number(null) plan_id,
1614 to_number(null) inst_id,
1615 to_number(null) org_id,
1616 to_number(null) item_id,
1617 number1 bucket_type,
1618 date1 bkt_start_date,
1619 date2 bkt_end_date,
1620 to_number(null) achieved_days_of_supply,
1621 to_number(null) safety_stock_quantity
1622 from msc_form_query
1623 where query_id = g_plan_bkts_query_id
1624 order by 1,2;
1625
1626 lx_plan_id msc_sda_utils.number_arr;
1630 lx_bkt_type msc_sda_utils.number_arr;
1627 lx_inst_id msc_sda_utils.number_arr;
1628 lx_org_id msc_sda_utils.number_arr;
1629 lx_item_id msc_sda_utils.number_arr;
1631 lx_bkt_start_date msc_sda_utils.date_arr;
1632 lx_bkt_end_date msc_sda_utils.date_arr;
1633 lx_qty1 msc_sda_utils.number_arr;
1634 lx_qty2 msc_sda_utils.number_arr;
1635
1636 l_cur_row_index varchar2(300) := c_mbp_null_value;
1637 hasRowChanged boolean;
1638
1639 function isSSRowChanged(p_row_index varchar2, p_prev_row_index varchar2) return boolean is
1640 l_flag boolean := true;
1641 begin
1642 if (p_row_index = p_prev_row_index ) then
1643 l_flag := false;
1644 end if;
1645 return l_flag;
1646 end isSSRowChanged;
1647
1648 procedure fillSSgapsAndflushSSrows is
1649 begin
1650 for bktIndex in 1..lx_bkt_type.count
1651 loop --{
1652 --6867580 bugfix, if the qty 0, show prev bucket value
1653 if (bktIndex > 1) then
1654 if ( lx_qty2(bktIndex) is null ) then
1655 lx_qty2(bktIndex) := lx_qty2(bktIndex-1);
1656 end if;
1657 end if;
1658 end loop; --}
1659
1660 forall i in lx_bkt_type.first .. lx_bkt_type.last
1661 --loop
1662 insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by,
1663 number1, number2, number3, number4,
1664 date1, number5, number6)
1665 values (g_ss_query_id , sysdate, -1, sysdate, -1,
1666 lx_plan_id(i), lx_inst_id(i), lx_org_id(i), lx_item_id(i),
1667 lx_bkt_end_date(i), lx_qty1(i), lx_qty2(i));
1668 --end loop;
1669 end fillSSgapsAndflushSSrows;
1670
1671 begin
1672 g_ss_query_id := msc_sda_utils.getNewFormQueryId;
1673
1674 open c_ss_data;
1675 fetch c_ss_data bulk collect into ll_row_index,
1676 ll_plan_id, ll_inst_id, ll_org_id, ll_item_id, ll_date, ll_qty1, ll_qty2;
1677 close c_ss_data;
1678
1679 for rIndex in 1 .. ll_row_index.count
1680 loop --{
1681 if (rIndex = 1) then
1682 open c_mpb_data;
1683 fetch c_mpb_data bulk collect into lx_plan_id, lx_inst_id, lx_org_id, lx_item_id,
1684 lx_bkt_type, lx_bkt_start_date, lx_bkt_end_date, lx_qty1, lx_qty2;
1685 close c_mpb_data;
1686 if (l_cur_row_index = c_mbp_null_value) then
1687 l_cur_row_index := ll_row_index(rIndex);
1688 end if;
1689 else
1690 hasRowChanged := isSSRowChanged(ll_row_index(rIndex), l_cur_row_index);
1691 if (hasRowChanged ) then
1692 msc_sda_utils.println('populateSSDataNew ss row has changed ');
1693 fillSSgapsAndflushSSrows;
1694
1695 open c_mpb_data;
1696 fetch c_mpb_data bulk collect into lx_plan_id, lx_inst_id, lx_org_id, lx_item_id,
1697 lx_bkt_type, lx_bkt_start_date, lx_bkt_end_date, lx_qty1, lx_qty2;
1698 close c_mpb_data;
1699 l_cur_row_index := ll_row_index(rIndex);
1700 end if;
1701 end if;
1702
1703 for bktIndex in 1..lx_bkt_type.count
1704 loop --{
1705 lx_plan_id(bktIndex) := ll_plan_id(rIndex);
1706 lx_inst_id(bktIndex) := ll_inst_id(rIndex);
1707 lx_org_id(bktIndex) := ll_org_id(rIndex);
1708 lx_item_id(bktIndex) := ll_item_id(rIndex);
1709
1710 if ( trunc(ll_date(rIndex)) >= lx_bkt_start_date(bktIndex)
1711 and trunc(ll_date(rIndex)) <= lx_bkt_end_date(bktIndex) ) then
1712 lx_qty1(bktIndex) := ll_qty1(rIndex);
1713 lx_qty2(bktIndex) := ll_qty2(rIndex);
1714 msc_sda_utils.println('populateSSDataNew ss qty '|| lx_qty1(bktIndex));
1715 end if;
1716 end loop; --}
1717
1718 if (rIndex = ll_row_index.count) then --{
1719 if (rIndex = ll_row_index.count) then
1720 msc_sda_utils.println('populateSSDataNew last row');
1721 fillSSgapsAndflushSSrows;
1722 end if;
1723 end if; --}
1724 l_cur_row_index := ll_row_index(rIndex);
1725 end loop; --}
1726 end populateSSDataNew;
1727
1728 procedure flushAndSendData(p_view_type number, p_query_id number,
1729 p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1730
1731 cursor rowcount_cur is
1732 select
1733 count(*)
1734 from
1735 msc_analysis_query maq
1736 where maq.query_id = p_query_id
1737 and maq.parent_row_index = g_next_rowset_index;
1738 l_row_count number;
1739
1740 ---------------------------
1741 --- SUPPLY VIEW CUSROR ---
1742 ---------------------------
1743 cursor sd_snapshot_cur is
1744 select
1745 maq.row_index,
1746 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,
1747 msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id) offset,
1748 msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, nvl(ms.firm_date,ms.new_schedule_date)) new_date,
1749 msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, nvl(ms.firm_date,ms.new_schedule_date)) old_date,
1750 sum(decode(msi.base_item_id,
1751 null, decode(ms.disposition_status_type,
1752 2, 0,
1753 decode(ms.last_unit_completion_date,
1754 null, nvl(ms.firm_quantity,ms.new_order_quantity),
1755 ms.daily_rate) ),
1756 decode(ms.last_unit_completion_date,
1757 null, nvl(ms.firm_quantity,ms.new_order_quantity),
1758 ms.daily_rate) )) new_quantity,
1759 sum(nvl(ms.old_order_quantity,0)) old_quantity
1763 msc_form_query mfq1, -- org-list
1760 from
1761 msc_supplies ms,
1762 msc_analysis_query maq,
1764 msc_form_query mfq2, -- item-list
1765 msc_plans mp,
1766 msc_form_query mfq3, --g_plan_bkts_query_id
1767 msc_system_items msi
1768 where maq.query_id = p_query_id
1769 and maq.parent_row_index = g_next_rowset_index
1770 and mfq1.query_id = g_org_query_id
1771 and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1772 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1773 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1774 and mfq2.query_id = g_chain_query_id
1775 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1776 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1777 and ms.plan_id = g_plan_id
1778 and ms.sr_instance_id = mfq1.number2
1779 and ms.organization_id = mfq1.number3
1780 and ms.inventory_item_id = mfq2.number2
1781 and ms.plan_id = msi.plan_id
1782 and ms.inventory_item_id = msi.inventory_item_id
1783 and ms.organization_id = msi.organization_id
1784 and ms.sr_instance_id = msi.sr_instance_id
1785 and ms.plan_id = mp.plan_id
1786 and mfq3.query_id = g_plan_bkts_query_id
1787 and ( trunc(nvl(ms.firm_date,ms.new_schedule_date)) between trunc(mfq3.date1) and trunc(mfq3.date2) )
1788 group by
1789 maq.row_index,
1790 msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id),
1791 msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id),
1792 msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, nvl(ms.firm_date,ms.new_schedule_date)),
1793 msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, nvl(ms.firm_date,ms.new_schedule_date))
1794 union all
1795 select
1796 maq.row_index,
1797 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,
1798 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,
1799 msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, nvl(md.firm_date,md.using_assembly_demand_date)) new_date,
1800 msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, nvl(md.firm_date,md.using_assembly_demand_date)) old_date,
1801 sum(decode(md.assembly_demand_comp_date,
1802 null, decode(md.origination_type,
1803 29,(nvl(md.probability,1)* nvl(md.firm_quantity,md.using_requirement_quantity)),
1804 31, 0,
1805 nvl(md.firm_quantity,md.using_requirement_quantity)),
1806 decode(md.origination_type,
1807 29,(nvl(md.probability,1)* md.daily_demand_rate),
1808 31, 0,
1809 md.daily_demand_rate)))/
1810 decode(nvl(least(sum(decode(md.origination_type,
1811 29,nvl(md.probability,0),
1812 null)),
1813 1) ,1),
1814 0,1,
1815 nvl(least(sum(decode(md.origination_type,
1816 29,nvl(md.probability,0),
1817 null)) ,1) ,1)) new_quantity,
1818 0 old_quantity
1819 from
1820 msc_demands md,
1821 msc_analysis_query maq,
1822 msc_form_query mfq1, -- org-list
1823 msc_form_query mfq2, -- item-list
1824 msc_plans mp,
1825 msc_form_query mfq3, --g_plan_bkts_query_id
1826 msc_instance_orgs mio,
1827 msc_system_items msi
1828 where maq.query_id = p_query_id
1829 and maq.parent_row_index = g_next_rowset_index
1830 and mfq1.query_id = g_org_query_id
1831 and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1832 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1833 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1834 and mfq2.query_id = g_chain_query_id
1835 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1836 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1837 and md.plan_id = g_plan_id
1838 and md.sr_instance_id = mfq1.number2
1839 and md.organization_id = mfq1.number3
1840 and md.inventory_item_id = mfq2.number2
1841 and md.plan_id = msi.plan_id
1842 and md.inventory_item_id = msi.inventory_item_id
1843 and md.organization_id = msi.organization_id
1844 and md.sr_instance_id = msi.sr_instance_id
1845 and md.plan_id = mp.plan_id
1846 and mfq3.query_id = g_plan_bkts_query_id
1847 and ( trunc(nvl(md.firm_date,md.using_assembly_demand_date)) between trunc(mfq3.date1) and trunc(mfq3.date2))
1848 and md.sr_instance_id = mio.sr_instance_id
1849 and md.organization_id = mio.organization_id
1850 group by
1851 maq.row_index,
1852 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),
1853 msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type),
1854 msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, nvl(md.firm_date,md.using_assembly_demand_date)),
1855 msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, nvl(md.firm_date,md.using_assembly_demand_date))
1856 union all
1860 c_row_max_level offset,
1857 select
1858 maq.row_index,
1859 c_max_level row_type,
1861 mil.inventory_date new_date,
1862 mil.inventory_date old_date,
1863 max(mil.max_quantity) new_quantity,
1864 0 old_quantity
1865 from
1866 msc_inventory_levels mil,
1867 msc_analysis_query maq,
1868 msc_form_query mfq1, -- org-list
1869 msc_form_query mfq2 -- item-list
1870 where maq.query_id = p_query_id
1871 and maq.parent_row_index = g_next_rowset_index
1872 and mfq1.query_id = g_org_query_id
1873 and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1874 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1875 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1876 and mfq2.query_id = g_chain_query_id
1877 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1878 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1879 and mil.plan_id = g_plan_id
1880 and mil.sr_instance_id = mfq1.number2
1881 and mil.organization_id = mfq1.number3
1882 and mil.inventory_item_id = mfq2.number2
1883 and mil.inventory_date <= g_plan_end_date
1884 and nvl(mil.max_quantity,mil.max_quantity_dos) is not null
1885 group by
1886 maq.row_index,
1887 c_max_level,
1888 c_row_max_level,
1889 mil.inventory_date,
1890 mil.inventory_date
1891 union all
1892 select
1893 maq.row_index,
1894 c_ss_supply row_type,
1895 c_row_ss_supply offset,
1896 mss.date1 new_date,
1897 mss.date1 old_date,
1898 sum(mss.number5) new_quantity,
1899 sum(mss.number6) old_quantity
1900 from
1901 msc_form_query mss,
1902 msc_analysis_query maq,
1903 msc_form_query mfq1, -- org-list
1904 msc_form_query mfq2 -- item-list
1905 where maq.query_id = p_query_id
1906 and maq.parent_row_index = g_next_rowset_index
1907 and mfq1.query_id = g_org_query_id
1908 and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1909 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1910 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1911 and mfq2.query_id = g_chain_query_id
1912 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1913 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1914 and mss.query_id = g_ss_query_id
1915 and mss.number1 = g_plan_id
1916 and mss.number2 = mfq1.number2
1917 and mss.number3 = mfq1.number3
1918 and mss.number4 = mfq2.number2
1919 and mss.date1 <= g_plan_end_date
1920 group by
1921 maq.row_index,
1922 c_ss_supply,
1923 c_row_ss_supply,
1924 mss.date1,
1925 mss.date1
1926 union all
1927 select
1928 maq.row_index,
1929 c_target_level row_type,
1930 c_row_target_level offset,
1931 nvl(maa.week_start_date, maa.period_start_date) new_date,
1932 nvl(maa.week_start_date, maa.period_start_date) old_date,
1933 avg(maa.target_service_level) new_quantity,
1934 0 old_quantity
1935 from
1936 msc_analysis_aggregate maa,
1937 msc_analysis_query maq,
1938 msc_plan_buckets mpb,
1939 msc_form_query mfq1, -- org-list
1940 msc_form_query mfq2 -- item-list
1941 where maq.query_id = p_query_id
1942 and maq.parent_row_index = g_next_rowset_index
1943 and mfq1.query_id = g_org_query_id
1944 and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1945 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1946 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1947 and mfq2.query_id = g_chain_query_id
1948 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1949 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1950 and maa.plan_id = g_plan_id
1951 and maa.sr_instance_id = mfq1.number2
1952 and maa.organization_id = mfq1.number3
1953 and maa.inventory_item_id = mfq2.number2
1954 and maa.record_type = 3
1955 and maa.period_type = 1
1956 and mpb.plan_id = maa.plan_id
1957 and ( (mpb.bucket_type = 2 and maa.week_start_date = mpb.bkt_start_date) or
1958 (mpb.bucket_type = 3 and maa.period_start_date = mpb.bkt_start_date))
1959 group by
1960 maq.row_index,
1961 c_target_level,
1962 c_row_target_level,
1963 nvl(maa.week_start_date, maa.period_start_date),
1964 nvl(maa.week_start_date, maa.period_start_date)
1965 union all
1966 select
1967 maq.row_index,
1968 c_ss_level row_type,
1969 c_row_ss_level offset,
1970 nvl(maa.week_start_date, maa.period_start_date) new_date,
1971 nvl(maa.week_start_date, maa.period_start_date) old_date,
1972 sum(maa.achieved_service_level_qty1)
1973 / sum(decode(maa.achieved_service_level_qty2,
1974 0, 1, maa.achieved_service_level_qty2)) new_quantity,
1975 0 old_quantity
1976 from
1977 msc_analysis_aggregate maa,
1978 msc_analysis_query maq,
1979 msc_plan_buckets mpb,
1980 msc_form_query mfq1, -- org-list
1981 msc_form_query mfq2 -- item-list
1982 where maq.query_id = p_query_id
1983 and maq.parent_row_index = g_next_rowset_index
1984 and mfq1.query_id = g_org_query_id
1985 and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
1986 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
1987 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
1988 and mfq2.query_id = g_chain_query_id
1989 and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
1993 and maa.organization_id = mfq1.number3
1990 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
1991 and maa.plan_id = g_plan_id
1992 and maa.sr_instance_id = mfq1.number2
1994 and maa.inventory_item_id = mfq2.number2
1995 and maa.record_type = 3
1996 and maa.period_type = 1
1997 and mpb.plan_id = maa.plan_id
1998 and ( (mpb.bucket_type = 2 and maa.week_start_date = mpb.bkt_start_date) or
1999 (mpb.bucket_type = 3 and maa.period_start_date = mpb.bkt_start_date))
2000 group by
2001 maq.row_index,
2002 c_target_level,
2003 c_row_target_level,
2004 nvl(maa.week_start_date, maa.period_start_date),
2005 nvl(maa.week_start_date, maa.period_start_date)
2006 order by 1;
2007
2008 ----------------------------
2009 --- FORECAST VIEW CUSROR ---
2010 ----------------------------
2011 cursor fcst_snapshot_cur is
2012 --for region based demands
2013 select
2014 maq.row_index,
2015 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
2016 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
2017 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
2018 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
2019 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2020 md.using_assembly_demand_date) new_date,
2021 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2022 md.using_assembly_demand_date) old_date,
2023 sum(decode(md.assembly_demand_comp_date,
2024 null, decode(md.origination_type,
2025 29,(nvl(md.probability,1)* md.using_requirement_quantity),
2026 31, 0,
2027 md.using_requirement_quantity),
2028 decode(md.origination_type,
2029 29,(nvl(md.probability,1)* md.daily_demand_rate),
2030 31, 0,
2031 md.daily_demand_rate)))/
2032 decode(nvl(least(sum(decode(md.origination_type,
2033 29,nvl(md.probability,0),
2034 null)),
2035 1) ,1),
2036 0,1,
2037 nvl(least(sum(decode(md.origination_type,
2038 29,nvl(md.probability,0),
2039 null)) ,1) ,1)) new_quantity,
2040 sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
2041 from
2042 msc_demands md,
2043 msc_analysis_query maq,
2044 msc_form_query mfq1, -- region-to-org-list
2045 msc_form_query mfq2, -- item-list
2046 msc_plans mp,
2047 msc_form_query mfq3 --g_plan_bkts_query_id
2048 where maq.query_id = p_query_id
2049 and maq.parent_row_index = g_next_rowset_index
2050 and mfq1.query_id = g_org_query_id
2051 and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2052 c_global_reg_type, mfq1.number1, maq.region_id),
2053 mfq1.number1), mfq1.number1)
2054 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2055 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2056 and mfq2.query_id = g_chain_query_id
2057 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2058 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2059 and md.plan_id = g_plan_id
2060 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2061 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2062 and md.inventory_item_id = mfq2.number2
2063 and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2064 and md.plan_id = mp.plan_id
2065 and mfq3.query_id = g_plan_bkts_query_id
2066 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2067 and md.origination_type in (c_dmd2_net_fcst)
2068 and nvl(maq.region_id, -1) not in (c_global_reg_type, c_local_reg_type)
2069 and ( ( nvl(maq.region_id,-1) = -1
2070 and (md.organization_id = -1
2071 or (nvl(maq.org_id, c_mbp_null_value) = md.organization_id
2072 and (md.original_demand_id is null
2073 or md.original_demand_id in (select demand_id
2074 from msc_demands md2
2075 where md2.plan_id = g_plan_id
2076 and md2.origination_type = c_dmd2_net_fcst
2080 or (nvl(maq.region_id,-1) <> -1 and md.organization_id <> -1) )
2077 and md2.organization_id = -1
2078 and md2.inventory_item_id = md.inventory_item_id))
2079 )))
2081 --and md.organization_id <> -1
2082 group by
2083 maq.row_index,
2084 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
2085 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2086 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
2087 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2088 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
2089 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
2090 union all
2091 --for global based demands
2092 select
2093 maq.row_index,
2094 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
2095 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
2096 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
2097 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
2098 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2099 md.using_assembly_demand_date) new_date,
2100 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2101 md.using_assembly_demand_date) old_date,
2102 sum(decode(md.assembly_demand_comp_date,
2103 null, decode(md.origination_type,
2104 29,(nvl(md.probability,1)* md.using_requirement_quantity),
2105 31, 0,
2106 md.using_requirement_quantity),
2107 decode(md.origination_type,
2108 29,(nvl(md.probability,1)* md.daily_demand_rate),
2109 31, 0,
2110 md.daily_demand_rate)))/
2111 decode(nvl(least(sum(decode(md.origination_type,
2112 29,nvl(md.probability,0),
2113 null)),
2114 1) ,1),
2115 0,1,
2116 nvl(least(sum(decode(md.origination_type,
2117 29,nvl(md.probability,0),
2118 null)) ,1) ,1)) new_quantity,
2119 sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
2120 from
2121 msc_demands md,
2122 msc_analysis_query maq,
2123 msc_form_query mfq1, -- region-to-org-list
2124 msc_form_query mfq2, -- item-list
2125 msc_plans mp,
2126 msc_form_query mfq3 --g_plan_bkts_query_id
2127 where maq.query_id = p_query_id
2128 and maq.parent_row_index = g_next_rowset_index
2129 and mfq1.query_id = g_org_query_id
2130 and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2131 c_global_reg_type, mfq1.number1, maq.region_id),
2132 mfq1.number1), mfq1.number1)
2133 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2134 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2135 and mfq2.query_id = g_chain_query_id
2136 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2137 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2138 and md.plan_id = g_plan_id
2139 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2140 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2141 and md.inventory_item_id = mfq2.number2
2142 and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2143 and md.zone_id is null
2144 and md.plan_id = mp.plan_id
2145 and mfq3.query_id = g_plan_bkts_query_id
2146 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2147 and md.origination_type in (c_dmd2_net_fcst)
2148 and nvl(maq.region_id, -1) in (c_global_reg_type)
2149 and ( nvl(maq.org_id,-1) = md.organization_id)
2150 and ( nvl(maq.org_id,-1) = -1
2151 or (md.original_demand_id is null or
2152 md.original_demand_id in (select demand_id
2153 from msc_demands md2
2154 where md2.plan_id = g_plan_id
2155 and md2.origination_type = c_dmd2_net_fcst
2156 and md2.organization_id = -1
2157 and md2.inventory_item_id = md.inventory_item_id)) )
2158 group by
2159 maq.row_index,
2160 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
2161 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2162 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
2163 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2164 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
2165 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
2166 union all
2167 --for local based demands
2168 select
2169 maq.row_index,
2173 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
2170 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, mfq4.number2, md.item_type_id, md.item_type_value, c_row_type_flag,
2171 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
2172 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, mfq4.number2, md.item_type_id, md.item_type_value, c_offset_flag,
2174 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2175 decode(mfq4.number2, c_dmd2_manual_fcst, md.firm_date, md.using_assembly_demand_date)) new_date,
2176 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2177 decode(mfq4.number2, c_dmd2_manual_fcst, md.firm_date, md.using_assembly_demand_date)) old_date,
2178 decode(mfq4.number2, c_dmd2_manual_fcst, sum(nvl(md.firm_quantity,0)),
2179 (sum(decode(md.assembly_demand_comp_date,
2180 null, decode(md.origination_type,
2181 29,(nvl(md.probability,1)* md.using_requirement_quantity),
2182 31, 0,
2183 md.using_requirement_quantity),
2184 decode(md.origination_type,
2185 29,(nvl(md.probability,1)* md.daily_demand_rate),
2186 31, 0,
2187 md.daily_demand_rate)))/
2188 decode(nvl(least(sum(decode(md.origination_type,
2189 29,nvl(md.probability,0),
2190 null)),
2191 1) ,1),
2192 0,1,
2193 nvl(least(sum(decode(md.origination_type,
2194 29,nvl(md.probability,0),
2195 null)) ,1) ,1)))) new_quantity,
2196 sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
2197 from
2198 msc_demands md,
2199 msc_analysis_query maq,
2200 msc_form_query mfq1, -- region-to-org-list
2201 msc_form_query mfq2, -- item-list
2202 msc_form_query mfq4, -- msc_demands duplicate rows
2203 msc_plans mp,
2204 msc_form_query mfq3 --g_plan_bkts_query_id
2205 where maq.query_id = p_query_id
2206 and maq.parent_row_index = g_next_rowset_index
2207 and mfq1.query_id = g_org_query_id
2208 and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2209 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2210 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2211 and mfq2.query_id = g_chain_query_id
2212 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2213 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2214 and md.plan_id = g_plan_id
2215 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2216 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2217 and md.inventory_item_id = mfq2.number2
2218 --and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2219 and md.zone_id is null
2220 and md.plan_id = mp.plan_id
2221 and mfq3.query_id = g_plan_bkts_query_id
2222 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2223 and md.origination_type in (c_dmd2_net_fcst)
2224 and nvl(maq.region_id, -1) in (c_local_reg_type, -1)
2225 and mfq1.number1 = c_local_reg_type
2226 and md.organization_id <> -1
2227 and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
2228 --and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
2229 and ( (md.original_demand_id is null or
2230 md.original_demand_id not in (select demand_id
2231 from msc_demands md2
2232 where md2.plan_id = g_plan_id
2233 and md2.origination_type = c_dmd2_net_fcst
2234 and md2.organization_id = -1
2235 and md2.inventory_item_id = md.inventory_item_id)) )
2236 and mfq4.query_id = g_md_dup_rows_qid
2237 and mfq4.number1 = md.origination_type
2238 and ((mfq4.number2 = c_dmd2_net_fcst) or (mfq4.number2 = c_dmd2_manual_fcst and firm_date is not null))
2239 group by
2240 maq.row_index,
2241 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, mfq4.number2, md.item_type_id, md.item_type_value, c_row_type_flag,
2242 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2243 msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, mfq4.number2, md.item_type_id, md.item_type_value, c_offset_flag,
2244 md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
2245 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2246 decode(mfq4.number2, c_dmd2_manual_fcst, md.firm_date, md.using_assembly_demand_date)),
2247 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2248 decode(mfq4.number2, c_dmd2_manual_fcst, md.firm_date, md.using_assembly_demand_date)),
2249 mfq4.number2
2250 union all
2251 --for region based demands - consumption
2252 select
2253 maq.row_index,
2254 c_drow2_consm_qty row_type,
2255 c_row2_consumed_fcst offset,
2256 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2257 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2258 sum(mfu.consumed_qty) new_quantity,
2259 sum(mfu.overconsumption_qty) old_quantity
2260 from
2261 msc_demands md,
2262 msc_analysis_query maq,
2263 msc_form_query mfq1, -- region-to-org-list
2264 msc_form_query mfq2, -- item-list
2268 where maq.query_id = p_query_id
2265 msc_plans mp,
2266 msc_forecast_updates mfu,
2267 msc_form_query mfq3 --g_plan_bkts_query_id
2269 and maq.parent_row_index = g_next_rowset_index
2270 and mfq1.query_id = g_org_query_id
2271 and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2272 c_global_reg_type, mfq1.number1, maq.region_id),
2273 mfq1.number1), mfq1.number1)
2274 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2275 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2276 and mfq2.query_id = g_chain_query_id
2277 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2278 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2279 and md.plan_id = g_plan_id
2280 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2281 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2282 and md.inventory_item_id = mfq2.number2
2283 and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2284 and md.plan_id = mp.plan_id
2285 and mfq3.query_id = g_plan_bkts_query_id
2286 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2287 and md.origination_type in (c_dmd2_net_fcst)
2288 and nvl(maq.region_id, -1) not in (c_global_reg_type, c_local_reg_type)
2289 and ( ( nvl(maq.region_id,-1) = -1
2290 and (md.organization_id = -1
2291 or (nvl(maq.org_id, c_mbp_null_value) = md.organization_id
2292 and (md.original_demand_id is null
2293 or md.original_demand_id in (select demand_id
2294 from msc_demands md2
2295 where md2.plan_id = g_plan_id
2296 and md2.origination_type = c_dmd2_net_fcst
2297 and md2.organization_id = -1
2298 and md2.inventory_item_id = md.inventory_item_id))
2299 )))
2300 or (nvl(maq.region_id,-1) <> -1 and md.organization_id <> -1) )
2301 and md.plan_id = mfu.plan_id
2302 and md.demand_id = mfu.forecast_demand_id
2303 --and md.organization_id <> -1
2304 group by
2305 maq.row_index,
2306 c_drow2_consm_qty,
2307 c_row2_consumed_fcst,
2308 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2309 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2310 union all
2311 --for global based demands - consumption
2312 select
2313 maq.row_index,
2314 c_drow2_consm_qty row_type,
2315 c_row2_consumed_fcst offset,
2316 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2317 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2318 sum(mfu.consumed_qty) new_quantity,
2319 sum(mfu.overconsumption_qty) old_quantity
2320 from
2321 msc_demands md,
2322 msc_analysis_query maq,
2323 msc_form_query mfq1, -- region-to-org-list
2324 msc_form_query mfq2, -- item-list
2325 msc_plans mp,
2326 msc_forecast_updates mfu,
2327 msc_form_query mfq3 --g_plan_bkts_query_id
2328 where maq.query_id = p_query_id
2329 and maq.parent_row_index = g_next_rowset_index
2330 and mfq1.query_id = g_org_query_id
2331 and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2332 c_global_reg_type, mfq1.number1, maq.region_id),
2333 mfq1.number1), mfq1.number1)
2334 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2335 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2336 and mfq2.query_id = g_chain_query_id
2337 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2338 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2339 and md.plan_id = g_plan_id
2340 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2341 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2342 and md.inventory_item_id = mfq2.number2
2343 and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2344 and md.zone_id is null
2345 and md.plan_id = mp.plan_id
2346 and mfq3.query_id = g_plan_bkts_query_id
2347 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2348 and md.origination_type in (c_dmd2_net_fcst)
2349 and nvl(maq.region_id, -1) in (c_global_reg_type)
2350 and ( nvl(maq.org_id,-1) = md.organization_id)
2351 and ( nvl(maq.org_id,-1) = -1
2352 or (md.original_demand_id is null or
2353 md.original_demand_id in (select demand_id
2354 from msc_demands md2
2355 where md2.plan_id = g_plan_id
2356 and md2.origination_type = c_dmd2_net_fcst
2357 and md2.organization_id = -1
2358 and md2.inventory_item_id = md.inventory_item_id)) )
2359 and md.plan_id = mfu.plan_id
2360 and md.demand_id = mfu.forecast_demand_id
2361 group by
2362 maq.row_index,
2363 c_drow2_consm_qty,
2364 c_row2_consumed_fcst,
2365 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2366 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2367 union all
2368 --for local based demands - consumption
2369 select
2370 maq.row_index,
2371 c_drow2_consm_qty row_type,
2372 c_row2_consumed_fcst offset,
2373 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
2374 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2375 sum(mfu.consumed_qty) new_quantity,
2376 sum(mfu.overconsumption_qty) old_quantity
2377 from
2378 msc_demands md,
2379 msc_analysis_query maq,
2380 msc_form_query mfq1, -- region-to-org-list
2381 msc_form_query mfq2, -- item-list
2382 msc_plans mp,
2383 msc_forecast_updates mfu,
2384 msc_form_query mfq3 --g_plan_bkts_query_id
2385 where maq.query_id = p_query_id
2386 and maq.parent_row_index = g_next_rowset_index
2387 and mfq1.query_id = g_org_query_id
2388 and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2389 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2390 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2391 and mfq2.query_id = g_chain_query_id
2392 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2393 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2394 and md.plan_id = g_plan_id
2395 and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
2396 and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
2397 and md.inventory_item_id = mfq2.number2
2398 --and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2399 and md.zone_id is null
2400 and md.plan_id = mp.plan_id
2401 and mfq3.query_id = g_plan_bkts_query_id
2402 and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2403 and md.origination_type in (c_dmd2_net_fcst)
2404 and nvl(maq.region_id, -1) in (c_local_reg_type, -1)
2405 and mfq1.number1 = c_local_reg_type
2406 and md.organization_id <> -1
2407 and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
2408 --and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
2409 and ( (md.original_demand_id is null or
2410 md.original_demand_id not in (select demand_id
2411 from msc_demands md2
2412 where md2.plan_id = g_plan_id
2413 and md2.origination_type = c_dmd2_net_fcst
2414 and md2.organization_id = -1
2415 and md2.inventory_item_id = md.inventory_item_id)) )
2416 and md.plan_id = mfu.plan_id
2417 and md.demand_id = mfu.forecast_demand_id
2418 group by
2419 maq.row_index,
2420 c_drow2_consm_qty,
2421 c_row2_consumed_fcst,
2422 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
2423 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
2424 union all
2425 --for region based supplies
2426 select
2427 maq.row_index,
2428 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2429 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
2430 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2431 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
2432 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2433 ms.new_schedule_date) new_date,
2434 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2435 ms.new_schedule_date) old_date,
2436 sum(decode(msi.base_item_id,
2437 null, decode(ms.disposition_status_type,
2438 2, 0,
2439 decode(ms.last_unit_completion_date,
2440 null, ms.new_order_quantity,
2441 ms.daily_rate) ),
2442 decode(ms.last_unit_completion_date,
2443 null, ms.new_order_quantity,
2444 ms.daily_rate) )) new_quantity,
2445 0 old_quantity
2446 from
2447 msc_supplies ms,
2448 msc_analysis_query maq,
2449 msc_form_query mfq1, -- region-to-org-list
2450 msc_form_query mfq2, -- item-list
2451 msc_plans mp,
2452 msc_system_items msi,
2453 msc_form_query mfq3 --g_plan_bkts_query_id
2454 where maq.query_id = p_query_id
2455 and maq.parent_row_index = g_next_rowset_index
2456 and mfq1.query_id = g_org_query_id
2457 and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2458 c_global_reg_type, mfq1.number1, maq.region_id),
2459 mfq1.number1), mfq1.number1)
2460 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2461 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2462 and mfq2.query_id = g_chain_query_id
2466 and ms.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, ms.sr_instance_id))
2463 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2464 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2465 and ms.plan_id = g_plan_id
2467 and ms.organization_id = nvl(maq.org_id, nvl(mfq1.number3, ms.organization_id))
2468 and ms.inventory_item_id = mfq2.number2
2469 and nvl(ms.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2470 and ms.plan_id = mp.plan_id
2471 and mfq3.query_id = g_plan_bkts_query_id
2472 and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2473 and ms.order_type in (c_sup2_rtns_fcst,
2474 c_sup2_rtns_dmd_schd,
2475 c_sup2_rtns_bestfit_fcst)
2476 and nvl(maq.region_id, -1) not in (c_global_reg_type, c_local_reg_type)
2477 and ms.plan_id = msi.plan_id
2478 and ms.sr_instance_id = msi.sr_instance_id
2479 and decode(ms.organization_id,-1, mp.organization_id, ms.organization_id) = msi.organization_id
2480 and ms.inventory_item_id = msi.inventory_item_id
2481 --and ms.organization_id <> -1
2482 and ( ( nvl(maq.region_id,-1) = -1
2483 and (ms.organization_id = -1 or (nvl(maq.org_id, -23453) = ms.organization_id ))
2484 )
2485 or (nvl(maq.region_id,-1) <> -1 and ms.organization_id <> -1)
2486 )
2487 group by
2488 maq.row_index,
2489 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2490 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2491 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2492 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2493 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
2494 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
2495 union all
2496 --for global based supplies
2497 select
2498 maq.row_index,
2499 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2500 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
2501 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2502 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
2503 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2504 ms.new_schedule_date) new_date,
2505 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2506 ms.new_schedule_date) old_date,
2507 sum(decode(msi.base_item_id,
2508 null, decode(ms.disposition_status_type,
2509 2, 0,
2510 decode(ms.last_unit_completion_date,
2511 null, ms.new_order_quantity,
2512 ms.daily_rate) ),
2513 decode(ms.last_unit_completion_date,
2514 null, ms.new_order_quantity,
2515 ms.daily_rate) )) new_quantity,
2516 0 old_quantity
2517 from
2518 msc_supplies ms,
2519 msc_analysis_query maq,
2520 msc_form_query mfq1, -- region-to-org-list
2521 msc_form_query mfq2, -- item-list
2522 msc_plans mp,
2523 msc_system_items msi,
2524 msc_form_query mfq3 --g_plan_bkts_query_id
2525 where maq.query_id = p_query_id
2526 and maq.parent_row_index = g_next_rowset_index
2527 and mfq1.query_id = g_org_query_id
2528 and mfq1.number1 = nvl(nvl(decode(maq.region_id,
2529 c_global_reg_type, mfq1.number1, maq.region_id),
2530 mfq1.number1), mfq1.number1)
2531 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2532 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2533 and mfq2.query_id = g_chain_query_id
2534 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2535 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2536 and ms.plan_id = g_plan_id
2537 and ms.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, ms.sr_instance_id))
2538 and ms.organization_id = nvl(maq.org_id, nvl(mfq1.number3, ms.organization_id))
2539 and ms.inventory_item_id = mfq2.number2
2540 and nvl(ms.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2541 and ms.zone_id is null
2542 and ms.plan_id = mp.plan_id
2543 and mfq3.query_id = g_plan_bkts_query_id
2547 c_sup2_rtns_bestfit_fcst)
2544 and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2545 and ms.order_type in (c_sup2_rtns_fcst,
2546 c_sup2_rtns_dmd_schd,
2548 and nvl(maq.region_id, -1) in (c_global_reg_type)
2549 and ( nvl(maq.org_id,-1) = ms.organization_id)
2550 and ms.plan_id = msi.plan_id
2551 and ms.sr_instance_id = msi.sr_instance_id
2552 and decode(ms.organization_id,-1, mp.organization_id, ms.organization_id) = msi.organization_id
2553 and ms.inventory_item_id = msi.inventory_item_id
2554 group by
2555 maq.row_index,
2556 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2557 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2558 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
2559 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2560 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
2561 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
2562 union all
2563 --for local based supplies
2564 select
2565 maq.row_index,
2566 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, mfq4.number2, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2567 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
2568 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, mfq4.number2, ms.item_type_id, ms.item_type_value, c_offset_flag,
2569 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
2570 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2571 decode(mfq4.number2, c_sup2_rtns_manual_fcst, ms.firm_date, ms.new_schedule_date)) new_date,
2572 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2573 decode(mfq4.number2, c_sup2_rtns_manual_fcst, ms.firm_date, ms.new_schedule_date)) old_date,
2574 sum( decode(mfq4.number2, c_sup2_rtns_manual_fcst, nvl(ms.firm_quantity,0),
2575 decode(msi.base_item_id,
2576 null, decode(ms.disposition_status_type,
2577 2, 0,
2578 decode(ms.last_unit_completion_date,
2579 null, ms.new_order_quantity,
2580 ms.daily_rate) ),
2581 decode(ms.last_unit_completion_date,
2582 null, ms.new_order_quantity,
2583 ms.daily_rate) ))) new_quantity,
2584 0 old_quantity
2585 from
2586 msc_supplies ms,
2587 msc_analysis_query maq,
2588 msc_form_query mfq1, -- region-to-org-list
2589 msc_form_query mfq2, -- item-list
2590 msc_form_query mfq4, -- msc_supplies duplicate rows
2591 msc_plans mp,
2592 msc_system_items msi,
2593 msc_form_query mfq3 --g_plan_bkts_query_id
2594 where maq.query_id = p_query_id
2595 and maq.parent_row_index = g_next_rowset_index
2596 and mfq1.query_id = g_org_query_id
2597 and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2598 and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
2599 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2600 and mfq2.query_id = g_chain_query_id
2601 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2602 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2603 and ms.plan_id = g_plan_id
2604 and ms.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, ms.sr_instance_id))
2605 and ms.organization_id = nvl(maq.org_id, nvl(mfq1.number3, ms.organization_id))
2606 and ms.inventory_item_id = mfq2.number2
2607 --and nvl(ms.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
2608 and ms.zone_id is null
2609 and ms.plan_id = mp.plan_id
2610 and mfq3.query_id = g_plan_bkts_query_id
2611 and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
2612 and ms.order_type in (c_sup2_rtns_fcst,
2613 c_sup2_rtns_dmd_schd,
2614 c_sup2_rtns_bestfit_fcst)
2615 and nvl(maq.region_id, -1) in (c_local_reg_type, -1)
2616 and mfq1.number1 = c_local_reg_type
2617 and ms.organization_id <> -1
2618 and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
2619 --and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
2620 and ms.plan_id = msi.plan_id
2621 and ms.sr_instance_id = msi.sr_instance_id
2622 and decode(ms.organization_id,-1, mp.organization_id, ms.organization_id) = msi.organization_id
2623 and ms.inventory_item_id = msi.inventory_item_id
2624 and mfq4.query_id = g_ms_dup_rows_qid
2625 and mfq4.number1 = ms.order_type
2626 and ((mfq4.number2 = c_sup2_rtns_fcst) or (mfq4.number2 = c_sup2_rtns_manual_fcst and firm_date is not null))
2627 group by
2628 maq.row_index,
2629 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, mfq4.number2, ms.item_type_id, ms.item_type_value, c_row_type_flag,
2630 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2631 msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, mfq4.number2, ms.item_type_id, ms.item_type_value, c_offset_flag,
2632 c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
2633 msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
2634 decode(mfq4.number2, c_sup2_rtns_manual_fcst, ms.firm_date, ms.new_schedule_date)),
2635 msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
2636 decode(mfq4.number2, c_sup2_rtns_manual_fcst, ms.firm_date, ms.new_schedule_date))
2637 order by 1;
2638
2639 cursor hist_min_max_dates is
2640 select min(date1), max(date2)
2641 from msc_form_query
2642 where query_id = g_hist_cal_query_id;
2643
2644 l_min_date date;
2645 l_max_date date;
2646
2647 cursor hist_bucket_dates is
2648 select date1, date2
2649 from msc_form_query
2650 where query_id = g_hist_cal_query_id
2651 order by 1;
2652
2653 ll_bkt_start_date msc_sda_utils.date_arr;
2654 ll_bkt_end_date msc_sda_utils.date_arr;
2655
2656 ---------------------------
2657 --- HISTORY VIEW CUSROR ---
2658 ---------------------------
2659
2660 --engine will not flush demand history and returns history into msc_supplies/msc_demands
2661 --ui needs to look at msd views to get this information
2662 cursor hist_snapshot_cur is
2663 select
2664 maq.row_index,
2665 msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag) row_type,
2666 msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag) offset,
2667 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date) new_date,
2668 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date) old_date,
2669 sum(mmhv.quantity) new_quantity,
2670 0 old_quantity
2671 from
2672 msc_msd_history_v mmhv,
2673 msc_analysis_query maq,
2674 msc_form_query mfq1, -- region-to-org-list
2675 msc_form_query mfq2, -- item-list
2676 msc_form_query mfq3 -- history calendar
2677 where maq.query_id = p_query_id
2678 and maq.parent_row_index = g_next_rowset_index
2679 and mfq1.query_id = g_org_query_id
2680 and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2681 and nvl(mfq1.number2, c_mbp_not_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_not_null_value))
2682 and nvl(mfq1.number3, -1) = nvl(maq.org_id, nvl(mfq1.number3, -1))
2683 and mfq2.query_id = g_chain_query_id
2684 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2685 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2686 and nvl(mmhv.sr_instance_id, c_mbp_not_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_not_null_value))
2687 and nvl(mmhv.organization_id, -1) = nvl(maq.org_id, nvl(mfq1.number3, -1))
2688 and mmhv.inventory_item_id = mfq2.number2
2689 and nvl(mmhv.zone_id, c_local_reg_type) = mfq1.number1
2690 and mfq3.query_id = g_hist_cal_query_id
2691 and trunc(mmhv.anchor_date) between mfq3.date1 and mfq3.date2
2692 group by
2693 maq.row_index,
2694 msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag),
2695 msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag),
2696 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date),
2697 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date)
2698 order by 1;
2699
2700 /*
2701 cursor hist_snapshot_cur is
2702 select
2703 maq.row_index,
2707 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date) old_date,
2704 msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag) row_type,
2705 msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag) offset,
2706 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date) new_date,
2708 sum(ms.new_order_quantity) new_quantity,
2709 0 old_quantity
2710 from
2711 msc_supplies ms,
2712 msc_analysis_query maq,
2713 msc_form_query mfq1, -- region-to-org-list
2714 msc_form_query mfq2, -- item-list
2715 msc_form_query mfq3 -- history calendar
2716 where maq.query_id = p_query_id
2717 and maq.parent_row_index = g_next_rowset_index
2718 and mfq1.query_id = g_org_query_id
2719 and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2720 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
2721 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2722 and mfq2.query_id = g_chain_query_id
2723 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2724 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2725 and ms.plan_id = g_plan_id
2726 and ms.sr_instance_id = nvl(maq.inst_id, mfq1.number2)
2727 and ms.organization_id = nvl(maq.org_id, mfq1.number3)
2728 and ms.inventory_item_id = mfq2.number2
2729 and nvl(ms.zone_id, c_local_reg_type) = mfq1.number1
2730 and mfq3.query_id = g_hist_cal_query_id
2731 and trunc(ms.new_schedule_date) between mfq3.date1 and mfq3.date2
2732 and ms.order_type = c_returns_hist
2733 group by
2734 maq.row_index,
2735 msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag),
2736 msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag),
2737 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date),
2738 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date)
2739 union all
2740 select
2741 maq.row_index,
2742 msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag) row_type,
2743 msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag) offset,
2744 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date) new_date,
2745 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date) old_date,
2746 sum(md.using_requirement_quantity) new_quantity,
2747 0 old_quantity
2748 from
2749 msc_demands md,
2750 msc_analysis_query maq,
2751 msc_form_query mfq1, -- region-to-org-list
2752 msc_form_query mfq2, -- item-list
2753 msc_form_query mfq3 -- history calendar
2754 where maq.query_id = p_query_id
2755 and maq.parent_row_index = g_next_rowset_index
2756 and mfq1.query_id = g_org_query_id
2757 and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
2758 and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
2759 and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
2760 and mfq2.query_id = g_chain_query_id
2761 and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
2762 and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
2763 and md.plan_id = g_plan_id
2764 and md.sr_instance_id = nvl(maq.inst_id, mfq1.number2)
2765 and md.organization_id = nvl(maq.org_id, mfq1.number3)
2766 and md.inventory_item_id = mfq2.number2
2767 and nvl(md.zone_id, md.schedule_designator_id, c_local_reg_type) = mfq1.number1
2768 and mfq3.query_id = g_hist_cal_query_id
2769 and trunc(md.using_assembly_demand_date) between mfq3.date1 and mfq3.date2
2770 and md.origination_type = c_dmd_hist
2771 group by
2772 maq.row_index,
2773 msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag),
2774 msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag),
2775 msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date),
2776 msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date)
2777 order by 1;
2778 */
2779 /*
2780 QUERY_ID NUMBER1 NUMBER2 NUMBER3
2781 ---------- ---------- ---------- ----------
2782 235631 -400 21 207 --org_reg-query
2783 235631 -300 21 207 --org_reg-query
2784 235631 407 21 207 --org_reg-query
2785
2786 QUERY RIDX PIDX RLID RID OLID INST ORG TOP_ITEM_ID ITEM_ID
2787 ----- ---- ---- ----- ----- ---------- ---- ---- ----------- ---------- -- msc_analysis_query
2788
2789 755 1 1 -100 -200 49956 -- allRegions-allOrgs
2790
2791 755 2 2 -100 407 -200 49956 -- validRegion-allOrgs
2792 755 3 2 -100 -400 -200 49956 -- localRegion-allOrgs
2793
2794 755 4 3 -100 407 -200 21 207 49956 -- validRegion-validOrgs
2795
2796 755 5 4 -100 -200 21 207 49956 --allRegions-validOrgs
2797
2798 755 6 5 -100 -400 -200 21 207 49956 --localRegion-validOrgs
2799 */
2800
2801 ll_row_index msc_sda_utils.number_arr;
2802 ll_row_type msc_sda_utils.number_arr;
2803 ll_offset msc_sda_utils.number_arr;
2804 ll_new_date msc_sda_utils.date_arr;
2805 ll_old_date msc_sda_utils.date_arr;
2806 ll_new_quantity msc_sda_utils.number_arr;
2807 ll_old_quantity msc_sda_utils.number_arr;
2808
2809 l_cur_row_index number := c_mbp_null_value;
2813 begin
2810 hasRowChanged boolean;
2811
2812 l_out_data_index number := 1;
2814 msc_sda_utils.println('flushAndSendData in');
2815 msc_sda_utils.println('g_plan_id p_query_id g_next_rowset_index g_org_query_id g_chain_query_id '||
2816 g_plan_id ||' - '|| p_query_id ||' - '|| g_next_rowset_index ||' - '|| g_org_query_id ||' - '|| g_chain_query_id );
2817
2818 --fetch rows from snapshot cursor
2819 if (p_view_type = c_sdview) then
2820 populateSSDataNew;
2821 open sd_snapshot_cur;
2822 fetch sd_snapshot_cur bulk collect into ll_row_index,
2823 ll_row_type, ll_offset, ll_new_date, ll_old_date,
2824 ll_new_quantity, ll_old_quantity;
2825 close sd_snapshot_cur;
2826 msc_sda_utils.println('sd view row count '||ll_row_index.count);
2827 elsif (p_view_type = c_fcstview) then
2828 open fcst_snapshot_cur;
2829 fetch fcst_snapshot_cur bulk collect into ll_row_index,
2830 ll_row_type, ll_offset, ll_new_date, ll_old_date,
2831 ll_new_quantity, ll_old_quantity;
2832 close fcst_snapshot_cur;
2833 msc_sda_utils.println('fcst view row count '||ll_row_index.count);
2834 elsif (p_view_type = c_histview) then
2835 open hist_min_max_dates;
2836 fetch hist_min_max_dates into l_min_date, l_max_date;
2837 close hist_min_max_dates;
2838
2839 open hist_bucket_dates;
2840 fetch hist_bucket_dates bulk collect into ll_bkt_start_date, ll_bkt_end_date;
2841 close hist_bucket_dates;
2842
2843 open hist_snapshot_cur;
2844 fetch hist_snapshot_cur bulk collect into ll_row_index,
2845 ll_row_type, ll_offset, ll_new_date, ll_old_date,
2846 ll_new_quantity, ll_old_quantity;
2847 close hist_snapshot_cur;
2848 msc_sda_utils.println('hist view row count '||ll_row_index.count||' - bucket count '||ll_bkt_start_date.count);
2849 end if;
2850
2851 if (msc_sda_utils.g_log_flag) then
2852 msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2853 for rIndex in 1 .. ll_row_index.count
2854 loop --{
2855 msc_sda_utils.println(ll_row_index(rIndex)
2856 || ' - '|| ll_row_type(rIndex)
2857 || ' - '|| ll_offset(rIndex)
2858 || ' - '|| to_char(ll_new_date(rIndex), c_date_format)
2859 || ' - '|| to_char(ll_old_date(rIndex), c_date_format)
2860 || ' - '|| ll_new_quantity(rIndex)
2861 || ' - '|| ll_old_quantity(rIndex) );
2862 end loop;
2863 end if;
2864
2865 --for all items fetched from snapshot_cur, flush to bucket and then flush to table
2866 for rIndex in 1 .. ll_row_index.count
2867 loop --{
2868 msc_sda_utils.println('snapshot_cur row_index '|| ll_row_index(rIndex)
2869 || ' bktdate '|| to_char(ll_new_date(rIndex), c_date_format) ||' qty '
2870 || ll_new_quantity(rIndex) ||' offset '|| ll_offset(rIndex) );
2871
2872 if (rIndex = 1) then
2873 initGrid(p_view_type); -- init/reinit grid to zeros
2874 if (l_cur_row_index = c_mbp_null_value) then
2875 l_cur_row_index := ll_row_index(rIndex);
2876 end if;
2877 else
2878 hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
2879 if (hasRowChanged ) then
2880 msc_sda_utils.println('row has changed ');
2881 if (p_view_type = c_sdview) then
2882 calculateSDTotals;
2883 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2884 elsif (p_view_type = c_fcstview) then
2885 calculateFcstTotals;
2886 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2887 elsif (p_view_type = c_histview) then
2888 --calculateHistTotals;
2889 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2890 end if;
2891 initGrid(p_view_type); -- init/reinit grid to zeros
2892 l_cur_row_index := ll_row_index(rIndex);
2893 end if;
2894 end if;
2895
2896 --flush the values to right bucket
2897 if (p_view_type in (c_sdview, c_fcstview)) then
2898 for bktIndex in 1..g_bkt_start_date.count
2899 loop --{
2900 if ( trunc(ll_new_date(rIndex)) >= g_bkt_start_date(bktIndex)
2901 and trunc(ll_new_date(rIndex)) <= g_bkt_end_date(bktIndex)
2902 and ( ll_new_quantity(rIndex) <> 0 or ll_old_quantity(rIndex) <> 0
2903 or (p_view_type = c_sdview and ll_offset(rIndex) = c_row_ss_supply) )
2904 and ll_offset(rIndex) <> c_row_discard ) then
2905 addDataToGrid(ll_offset(rIndex), bktIndex, ll_new_quantity(rIndex), p_view_type);
2906
2907 msc_sda_utils.println('safety stock qty '|| ll_offset(rIndex) ||' - '|| c_row_ss_supply);
2908 if (ll_offset(rIndex) = c_row_ss_supply) then
2909 addDataToGrid(c_row_ss_qty, bktIndex, ll_old_quantity(rIndex), p_view_type);
2910 end if;
2911
2912 if (p_view_type = c_fcstview) then
2913 if (ll_offset(rIndex) = c_row2_consumed_fcst) then
2914 addDataToGrid(c_row2_over_consmptn, bktIndex, ll_old_quantity(rIndex), p_view_type);
2915 end if;
2916 if (ll_offset(rIndex) in (c_row2_net_fcst, c_row2_dmd_schd, c_row2_usage_fcst, c_row2_popultn_fcst) ) then
2917 addDataToGrid(c_row2_orig_fcst, bktIndex, ll_old_quantity(rIndex), p_view_type);
2918 end if;
2919 end if;
2920 end if;
2921 end loop; --}
2922
2923 elsif (p_view_type = c_histview) then
2924 for bktIndex in 1..ll_bkt_start_date.count
2925 loop --{
2926 if ( trunc(ll_new_date(rIndex)) >= ll_bkt_start_date(bktIndex)
2927 and trunc(ll_new_date(rIndex)) <= ll_bkt_end_date(bktIndex)
2928 and ll_new_quantity(rIndex) <> 0
2929 and ll_offset(rIndex) <> c_row_discard ) then
2933 end loop; --}
2930 addDataToGrid(ll_offset(rIndex), bktIndex, ll_new_quantity(rIndex), p_view_type);
2931 msc_sda_utils.println('addDataToGrid rindex bktindex qty '||ll_row_index(rIndex)||' - '||bktIndex||' - '||ll_new_quantity(rIndex));
2932 end if;
2934 end if;
2935
2936 if (rIndex = ll_row_index.count) then --{
2937 if (rIndex = ll_row_index.count) then
2938 msc_sda_utils.println(' last row');
2939 if (p_view_type = c_sdview) then
2940 calculateSDTotals;
2941 flushToStream(ll_row_index(rIndex), l_out_data_index, p_out_data, p_view_type);
2942 elsif (p_view_type = c_fcstview) then
2943 calculateFcstTotals;
2944 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2945 elsif (p_view_type = c_histview) then
2946 --calculateHistTotals;
2947 flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2948 end if;
2949 end if;
2950 end if; --}
2951
2952 l_cur_row_index := ll_row_index(rIndex);
2953 end loop; --}
2954
2955 msc_sda_utils.println('flushAndSendData out');
2956 end flushAndSendData;
2957
2958 procedure flushSDRows(p_query_id number, p_row_index number,
2959 p_orglist_action number, p_itemlist_action number, p_action_node number) is
2960
2961 l_orglist_action number;
2962 l_itemlist_action number;
2963 l_row c_row_values_cur%rowtype;
2964
2965 cursor c_orgs_cur is
2966 select distinct
2967 mfq.number1 org_list_id,
2968 mfq.char1 org_list,
2969 mfq.number2 inst_id,
2970 mfq.number3 org_id,
2971 mfq.char4 org_code,
2972 mfq.number4 sort_column
2973 from msc_form_query mfq
2974 where mfq.query_id = g_org_query_id
2975 order by sort_column;
2976
2977 cursor c_items_cur is
2978 select distinct
2979 number1 top_item_id,
2980 char1 top_item_name,
2981 number2 item_id,
2982 char2 item_name,
2983 number3 sort_column
2984 from msc_form_query
2985 where query_id = g_chain_query_id
2986 order by sort_column desc;
2987
2988 l_row_check number;
2989 begin
2990 msc_sda_utils.println('flushSDRows in');
2991 --msc_sda_utils.println('p_row_index - p_orglist_action - p_itemlist_action '|| p_row_index
2992 --||' -'|| p_orglist_action ||' -'|| p_itemlist_action );
2993
2994 if (p_row_index = 1 and p_action_node is null ) then
2995 msc_sda_utils.println('flushSDRows out 1');
2996 return;
2997 end if;
2998
2999 open c_row_values_cur(p_query_id, p_row_index, to_number(null));
3000 fetch c_row_values_cur into l_row;
3001 close c_row_values_cur;
3002
3003 if (l_row.item_id is null) then
3004 l_itemlist_action := c_collapsed_state;
3005 else
3006 l_itemlist_action := p_itemlist_action;
3007 end if;
3008
3009 if (l_row.org_id is null) then
3010 l_orglist_action := c_collapsed_state;
3011 else
3012 l_orglist_action := p_orglist_action;
3013 end if;
3014
3015 open c_next_rowset_index_cur(p_query_id);
3016 fetch c_next_rowset_index_cur into g_next_rowset_index;
3017 close c_next_rowset_index_cur;
3018 g_next_rowset_index := g_next_rowset_index + 1;
3019
3020 if (p_action_node = c_org_node) then --{
3021 for c_orgs in c_orgs_cur --{
3022 loop
3023 msc_sda_utils.println(' orgs +');
3024 g_row_index := g_row_index +1;
3025 insert into msc_analysis_query
3026 (query_id, row_index, parent_row_index,
3027 org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3028 top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3029 values (p_query_id, g_row_index, g_next_rowset_index,
3030 c_orgs.org_list_id, c_orgs.org_list, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, l_orglist_action,
3031 l_row.top_item_id, l_row.top_item_name, l_row.item_id, l_row.item_name, l_itemlist_action);
3032 end loop; -- }
3033 end if; -- }
3034
3035 if (p_action_node = c_item_node) then --{
3036 for c_item in c_items_cur --{
3037 loop
3038 msc_sda_utils.println(' items +');
3039 g_row_index := g_row_index +1;
3040 insert into msc_analysis_query
3041 (query_id, row_index, parent_row_index,
3042 org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3043 top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3044 values (p_query_id, g_row_index, g_next_rowset_index,
3045 l_row.org_list_id, l_row.org_list, l_row.inst_id, l_row.org_id, l_row.org_code, l_orglist_action,
3046 c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
3047 end loop; --}
3048 end if; -- }
3049
3050 msc_sda_utils.println('flushSDRows out');
3051 end flushSDRows;
3052
3053 procedure flushFcstHistoryRows(p_view_type number, p_query_id number, p_row_index number,
3054 p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number) is
3055
3056 l_reglist_action number;
3057 l_orglist_action number;
3058 l_itemlist_action number;
3059 l_row c_row_values_cur%rowtype;
3060
3061 cursor c_regs_cur is
3062 select distinct
3063 mfq.number2 region_id,
3064 mfq.char2 region_code,
3065 mfq.number3 sort_column
3066 from msc_form_query mfq
3067 where mfq.query_id = g_region_query_id
3068 and ( (p_view_type = c_fcstview)
3069 or (p_view_type = c_histview and mfq.number2 <> c_global_reg_type))
3070 order by sort_column desc;
3071
3075 mfq.number3 org_id,
3072 cursor c_orgs_cur(p_region_id number) is
3073 select distinct
3074 mfq.number2 inst_id,
3076 mfq.char1 org_code,
3077 mfq.number4 sort_column
3078 from msc_form_query mfq
3079 where mfq.query_id = g_org_query_id
3080 and mfq.number3 <> -1
3081 and ( (p_view_type = c_fcstview and nvl(p_region_id, c_mbp_null_value) <> c_local_reg_type)
3082 or (p_view_type = c_fcstview and nvl(p_region_id, c_mbp_null_value) = c_local_reg_type
3083 and nvl(mfq.number2,c_mbp_null_value) <> c_mbp_null_value)
3084 or (p_view_type = c_histview and nvl(mfq.number2,c_mbp_null_value) <> c_mbp_null_value))
3085 order by sort_column;
3086
3087 cursor c_items_cur is
3088 select distinct
3089 number1 top_item_id,
3090 char1 top_item_name,
3091 number2 item_id,
3092 char2 item_name,
3093 number3 sort_column
3094 from msc_form_query
3095 where query_id = g_chain_query_id
3096 order by sort_column desc;
3097
3098 l_row_check number;
3099 begin
3100 msc_sda_utils.println('flushFcstHistoryRows in');
3101
3102 if (p_row_index = 1 and p_action_node is null ) then
3103 return;
3104 end if;
3105
3106 open c_row_values_cur(p_query_id, p_row_index, to_number(null));
3107 fetch c_row_values_cur into l_row;
3108 close c_row_values_cur;
3109
3110 if (l_row.region_id is null) then
3111 l_reglist_action := c_collapsed_state;
3112 else
3113 l_reglist_action := p_reglist_action;
3114 end if;
3115
3116 if (l_row.org_id is null) then
3117 l_orglist_action := c_collapsed_state;
3118 else
3119 l_orglist_action := p_orglist_action;
3120 end if;
3121
3122 if (l_row.item_id is null) then
3123 l_itemlist_action := c_collapsed_state;
3124 else
3125 l_itemlist_action := p_itemlist_action;
3126 end if;
3127
3128 open c_next_rowset_index_cur(p_query_id);
3129 fetch c_next_rowset_index_cur into g_next_rowset_index;
3130 close c_next_rowset_index_cur;
3131 g_next_rowset_index := g_next_rowset_index + 1;
3132
3133 if (p_action_node = c_region_node) then --{
3134 for c_regs in c_regs_cur --{
3135 loop
3136 g_row_index := g_row_index +1;
3137 insert into msc_analysis_query
3138 (query_id, row_index, parent_row_index,
3139 region_list_id, region_list, region_id, region_code, region_list_state,
3140 org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3141 top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3142 values (p_query_id, g_row_index, g_next_rowset_index,
3143 l_row.region_list_id, l_row.region_list, c_regs.region_id, c_regs.region_code, l_reglist_action,
3144 l_row.org_list_id, l_row.org_list, l_row.inst_id, l_row.org_id, l_row.org_code, l_orglist_action,
3145 l_row.top_item_id, l_row.top_item_name, l_row.item_id, l_row.item_name, l_itemlist_action);
3146 end loop; -- }
3147 end if; -- }
3148
3149 if (p_action_node = c_org_node) then --{
3150 for c_orgs in c_orgs_cur(l_row.region_id) --{
3151 loop
3152 g_row_index := g_row_index +1;
3153 --msc_sda_utils.println( 'row_index '||g_row_index ||' - '|| c_orgs.org_list_id ||' '|| c_orgs.org_list ||' '|| c_orgs.inst_id ||' '|| c_orgs.org_id ||' '|| c_orgs.org_code ||' '|| c_orgs.sort_column);
3154 insert into msc_analysis_query
3155 (query_id, row_index, parent_row_index,
3156 region_list_id, region_list, region_id, region_code, region_list_state,
3157 org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3158 top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3159 values (p_query_id, g_row_index, g_next_rowset_index,
3160 l_row.region_list_id, l_row.region_list, l_row.region_id, l_row.region_code, l_reglist_action,
3161 l_row.org_list_id, l_row.org_list, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, l_orglist_action,
3162 l_row.top_item_id, l_row.top_item_name, l_row.item_id, l_row.item_name, l_itemlist_action);
3163 end loop; -- }
3164 end if; -- }
3165
3166 if (p_action_node = c_item_node) then --{
3167 for c_item in c_items_cur --{
3168 loop
3169 g_row_index := g_row_index +1;
3170 --msc_sda_utils.println('row_index '||g_row_index ||' - '|| c_item.top_item_id ||' '|| c_item.top_item_name ||' '|| c_item.item_id ||' '|| c_item.item_name);
3171 insert into msc_analysis_query
3172 (query_id, row_index, parent_row_index,
3173 region_list_id, region_list, region_id, region_code, region_list_state,
3174 org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3175 top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3176 values (p_query_id, g_row_index, g_next_rowset_index,
3177 l_row.region_list_id, l_row.region_list, l_row.region_id, l_row.region_code, l_reglist_action,
3178 l_row.org_list_id, l_row.org_list, l_row.inst_id, l_row.org_id, l_row.org_code, l_orglist_action,
3179 c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
3180 end loop; --}
3181 end if; -- }
3182
3183 msc_sda_utils.println('flushFcstHistoryRows out');
3184 end flushFcstHistoryRows;
3185
3186 procedure SdFCSTHistoryView(p_query_id in out nocopy number,
3187 p_view_type number,
3188 p_plan_id number,
3189 p_org_type number, p_org_list varchar2,
3190 p_region_type number, p_region_list varchar2,
3191 p_item_list number, p_item_view_type number,
3192 p_refresh_view boolean default false,
3193 p_error_code out nocopy varchar2,
3194 p_item_folder number,
3195 p_items_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3196 p_comments_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3197 p_excp_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3198
3202 ll_region_code varchar2(250);
3199 ll_reg_list_id number;
3200 ll_reg_list varchar2(250);
3201 ll_region_id number;
3203 ll_org_list_id number;
3204 ll_org_list varchar2(250);
3205 ll_inst_id number;
3206 ll_org_id number;
3207 ll_org_code varchar2(10);
3208 ll_top_item_id number;
3209 ll_top_item_name varchar2(250);
3210 ll_item_id number;
3211 ll_item_name varchar2(250);
3212
3213 ll_reglist_action number;
3214 ll_orglist_action number;
3215 ll_itemlist_action number;
3216 begin
3217 msc_sda_utils.println('SdFCSTHistoryView in');
3218 msc_sda_utils.println('p_query_id - p_view_type - p_plan_id - '
3219 || ' p_org_type - p_org_list - p_region_type - p_region_list - '
3220 || ' p_item_list - p_item_view_type - p_item_folder ');
3221 msc_sda_utils.println(p_query_id ||' - '|| p_view_type ||' - '|| p_plan_id ||' - '||
3222 p_org_type ||' - '|| p_org_list ||' - '|| p_region_type ||' - '|| p_region_list ||' - '||
3223 p_item_list ||' - '|| p_item_view_type ||' - '|| p_item_folder );
3224
3225 g_view_type := p_view_type;
3226 g_plan_id := p_plan_id;
3227
3228 --capture region info in global variables
3229 g_region_type := p_region_type;
3230 g_region_list := p_region_list;
3231
3232 --capture org info in global variables
3233 g_org_type := p_org_type;
3234 g_org_list := p_org_list;
3235
3236 --capture item info in global variables
3237 g_item_list := p_item_list;
3238 g_item_view_type := p_item_view_type;
3239
3240 setPlanInfo;
3241 setUserPrefInfo;
3242
3243 g_row_index := 1; --initialize row_index to 1
3244
3245 if (g_view_type = c_sdview) then -- SD VIEW
3246 g_sd_query_id := msc_sda_utils.getNewAnalysisQueryId;
3247
3248 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);
3249 if (g_org_type = c_org_view) then
3250 ll_orglist_action := c_nodrill_state;
3251 else
3252 ll_orglist_action := c_collapsed_state;
3253 end if;
3254
3255 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);
3256 g_item_list_name := ll_top_item_name;
3257
3258 if (g_item_view_type = c_item_view) then
3259 ll_itemlist_action := c_nodrill_state;
3260 msc_sda_utils.println(' item no drill state ');
3261 else
3262 ll_itemlist_action := c_collapsed_state;
3263 msc_sda_utils.println(' item collapsed state ');
3264 end if;
3265
3266 insert into msc_analysis_query
3267 (query_id, row_index, parent_row_index, org_list_id, org_list, inst_id, org_id, org_code,
3268 top_item_id, top_item_name, item_id, item_name, org_list_state, top_item_name_state)
3269 values (g_sd_query_id, c_first_row_index, g_next_rowset_index, ll_org_list_id, ll_org_list, ll_inst_id, ll_org_id, ll_org_code,
3270 ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_orglist_action, ll_itemlist_action);
3271
3272 g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
3273 msc_sda_utils.println('g_org_query_id '||g_org_query_id);
3274
3275 g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
3276 msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
3277
3278 else -- FORECAST VIEW AND HISTORY VIEW
3279 g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
3280 g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
3281
3282 msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
3283 ll_reglist_action := c_collapsed_state;
3284 ll_orglist_action := c_collapsed_state;
3285
3286 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);
3287 g_item_list_name := ll_top_item_name;
3288 if (g_item_view_type = c_item_view) then
3289 ll_itemlist_action := c_nodrill_state;
3290 else
3291 ll_itemlist_action := c_collapsed_state;
3292 end if;
3293
3294 insert into msc_analysis_query
3295 (query_id, row_index, parent_row_index,
3296 region_list_id, region_list, region_id, region_code,region_list_state,
3297 org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3298 top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3299 values (g_fcst_query_id, c_first_row_index, g_next_rowset_index,
3300 c_all_region_type, c_all_region_type_text, to_number(null), null, c_collapsed_state,
3301 c_all_org_type, c_all_org_type_text, to_number(null), to_number(null), null, c_collapsed_state,
3302 ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_itemlist_action);
3303
3304 insert into msc_analysis_query
3305 (query_id, row_index, parent_row_index,
3306 region_list_id, region_list, region_id, region_code,region_list_state,
3307 org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3308 top_item_id, top_item_name, item_id, item_name, top_item_name_state)
3309 values (g_hist_query_id, c_first_row_index, g_next_rowset_index,
3310 c_all_region_type, c_all_region_type_text, to_number(null), null, c_collapsed_state,
3311 c_all_org_type, c_all_org_type_text, to_number(null), to_number(null), null, c_collapsed_state,
3312 ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_itemlist_action);
3313
3314 --flush regions/orgs into mfq
3315 --flush supersession chain into mfq..pabram..this needs to be region specific
3316 msc_sda_utils.flushRegsOrgsIntoMfq(g_plan_id, g_region_type, g_region_list,
3320 msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
3317 g_org_type, g_org_list, g_region_query_id, g_org_query_id);
3318
3319 g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
3321
3322
3323 --we need to duplicate forecast rows into msc_demands, so we can get manual forecast also from one select in forecast view cursor
3324 g_md_dup_rows_qid := msc_sda_utils.getNewFormQueryId;
3325
3326 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3327 values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_net_fcst);
3328
3329 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3330 values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_manual_fcst);
3331
3332 --we need to duplicate returns forecast rows into msc_supplies, so we can get returns manual forecast also from one select in forecast view cursor
3333 g_ms_dup_rows_qid := msc_sda_utils.getNewFormQueryId;
3334
3335 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3336 values (g_ms_dup_rows_qid, sysdate, -1, -1, sysdate, c_sup2_rtns_fcst, c_sup2_rtns_fcst);
3337
3338 insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3339 values (g_ms_dup_rows_qid, sysdate, -1, -1, sysdate, c_sup2_rtns_fcst, c_sup2_rtns_manual_fcst);
3340
3341
3342 end if;
3343
3344 msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
3345 msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
3346 msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
3347
3348 msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
3349
3350 p_query_id := g_sd_query_id;
3351 msc_sda_utils.println('SdFCSTHistoryView out');
3352 end SdFCSTHistoryView;
3353
3354 -----
3355 ----- send apis
3356 -----
3357
3358 procedure sendSdFcstTimeBucket(p_view_type number,
3359 p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3360 p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3361 p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3362 p_bucket_count out nocopy number) is
3363 l_one_record varchar2(32000) := null;
3364 l_token varchar2(1000);
3365 l_out_data_index number := 1;
3366 begin
3367 --days
3368 for i in 1..g_bkt_start_date.count
3369 loop
3370 l_token := g_bkt_type(i) || c_field_seperator || to_char(g_bkt_start_date(i), c_date_format);
3371 if (l_one_record is null) then
3372 if (p_view_type = c_sdview) then
3373 l_one_record := c_sdview_bucket_data || c_bang_separator || g_bkt_start_date.count || c_record_seperator || l_token;
3374 elsif (p_view_type = c_fcstview) then
3375 l_one_record := c_fcstview_bucket_data || c_bang_separator || g_bkt_start_date.count || c_record_seperator || l_token;
3376 end if;
3377 else
3378 l_one_record := c_record_seperator || l_token;
3379 end if;
3380 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_bucket_data);
3381 end loop;
3382
3383 --weeks
3384 l_one_record := null;
3385 l_out_data_index := 1;
3386 for i in 1..g_week_start_dates.count
3387 loop
3388 l_token := to_char(g_week_start_dates(i), c_date_format);
3389 if (l_one_record is null) then
3390 if (p_view_type = c_sdview) then
3391 l_one_record := c_sdview_week_data || c_bang_separator || g_week_start_dates.count || c_record_seperator || l_token;
3392 elsif (p_view_type = c_fcstview) then
3393 l_one_record := c_fcstview_week_data || c_bang_separator || g_week_start_dates.count || c_record_seperator || l_token;
3394 end if;
3395 else
3396 l_one_record := c_record_seperator || l_token;
3397 end if;
3398 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_week_data);
3399 end loop;
3400
3401 --periods
3402 l_one_record := null;
3403 l_out_data_index := 1;
3404 for i in 1..g_period_start_dates.count
3405 loop
3406 l_token := to_char(g_period_start_dates(i), c_date_format);
3407 if (l_one_record is null) then
3408 if (p_view_type = c_sdview) then
3409 l_one_record := c_sdview_period_data || c_bang_separator || g_period_start_dates.count || c_record_seperator || l_token;
3410 elsif (p_view_type = c_fcstview) then
3411 l_one_record := c_fcstview_period_data || c_bang_separator || g_period_start_dates.count || c_record_seperator || l_token;
3412 end if;
3413 else
3414 l_one_record := c_record_seperator || l_token;
3415 end if;
3416 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_period_data);
3417 end loop;
3418
3419 if (p_view_type = c_sdview) then
3420 g_sd_num_of_buckets := g_num_of_buckets;
3421 p_bucket_count := g_sd_num_of_buckets;
3422 elsif (p_view_type = c_fcstview) then
3423 g_fcst_num_of_buckets := g_num_of_buckets;
3424 p_bucket_count := g_fcst_num_of_buckets;
3425 end if;
3426 end sendSdFcstTimeBucket;
3427
3428 procedure sentHistTimeBucket(p_start_date date, p_end_date date,
3429 p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_bucket_count out nocopy number) is
3430
3431 cursor c_hist_date_cur is
3432 select date1
3433 from msc_form_query
3434 where query_id = g_hist_cal_query_id
3435 order by 1;
3436
3437 cursor c_hist_date_count_cur is
3441 l_count number;
3438 select count(*)
3439 from msc_form_query
3440 where query_id = g_hist_cal_query_id;
3442 l_out_data_index number := 1;
3443 l_one_record varchar2(2000) := null;
3444 l_token varchar2(2000);
3445
3446 l_cal_code varchar2(250) := fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR');
3447 l_cal_type number;
3448 begin
3449 --g_hist_cal_query_id := msc_sda_utils.createHistCalInMfq(g_pref_hist_start_date, g_plan_start_date);
3450 g_hist_cal_query_id := msc_sda_utils.getNewFormQueryId;
3451 if (l_cal_code is not null) then
3452 l_cal_type := c_profile_cal;
3453 else
3454 l_cal_type := c_owning_org_cal;
3455 end if;
3456
3457 insert into msc_form_query (query_id, creation_date, created_by,
3458 last_updated_by, last_update_date,
3459 date1, date2, number1)
3460 select g_hist_cal_query_id, sysdate, -1, -1, sysdate, start_date, end_date,1
3461 from (
3462 select mpsd.period_start_date start_date, mpsd.next_date-1 end_date
3463 from msc_trading_partners mtp,
3464 msc_period_start_dates mpsd
3465 where l_cal_type = c_owning_org_cal
3466 and mpsd.calendar_code = mtp.calendar_code
3467 and mpsd.sr_instance_id = mtp.sr_instance_id
3468 and mpsd.exception_set_id = mtp.calendar_exception_set_id
3469 and mtp.sr_instance_id = g_owning_inst_id
3470 and mtp.sr_tp_id = g_owning_org_id
3471 and mtp.partner_type =3
3472 and mpsd.period_start_date >= g_pref_hist_start_date
3473 and mpsd.period_start_date <= g_plan_start_date
3474 union all
3475 select mpsd.period_start_date start_date, mpsd.next_date-1 end_date
3476 from msc_period_start_dates mpsd
3477 where l_cal_type = c_profile_cal
3478 and mpsd.calendar_code = l_cal_code
3479 and mpsd.period_start_date >= g_pref_hist_start_date
3480 and mpsd.period_start_date <= g_plan_start_date
3481 order by 1);
3482
3483 open c_hist_date_count_cur;
3484 fetch c_hist_date_count_cur into l_count;
3485 close c_hist_date_count_cur;
3486
3487 l_one_record := null;
3488 l_out_data_index := 1;
3489 for c_hist_date in c_hist_date_cur
3490 loop
3491 l_token := to_char(c_hist_date.date1, c_date_format);
3492 if (l_one_record is null) then
3493 l_one_record := c_histview_bucket_data || c_bang_separator || l_count || c_record_seperator || l_token;
3494 else
3495 l_one_record := c_record_seperator || l_token;
3496 end if;
3497 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_bucket_data);
3498 end loop;
3499 p_bucket_count := l_count;
3500 end sentHistTimeBucket;
3501
3502 procedure sendTimeBucket(p_view_type number,
3503 p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3504 p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3505 p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3506 begin
3507 if (p_view_type = c_sdview) then
3508 sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_sd_num_of_buckets);
3509 elsif (p_view_type = c_fcstview) then
3510 sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_fcst_num_of_buckets);
3511 elsif (p_view_type = c_histview) then
3512 sentHistTimeBucket(g_pref_hist_start_date, g_plan_start_date, p_bucket_data, g_hist_num_of_buckets);
3513 end if;
3514 msc_sda_utils.println('g_hist_cal_query_id '||g_hist_cal_query_id);
3515 end sendTimeBucket;
3516
3517 procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3518 p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3519 p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3520 p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3521 begin
3522 g_view_type := p_view_type;
3523 g_plan_id := p_plan_id;
3524 setPlanInfo;
3525 setUserPrefInfo;
3526 sendTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data);
3527 end sendTimeBucketEng;
3528
3529
3530 procedure sendRows(p_view_type number, p_query_id number, p_row_index number, p_parent_row_index number,
3531 p_rheader_data in out nocopy msc_sda_utils.maxCharTbl) is
3532
3533 l_out_data_index number := 1;
3534 l_one_record varchar2(2000) := null;
3535 l_token varchar2(2000);
3536 l_token1 varchar2(2000);
3537 l_token2 varchar2(2000);
3538 l_count number;
3539
3540 l_org_list_id number;
3541 l_org_list varchar2(200);
3542 l_org_list_state number;
3543 l_inst_id number;
3544 l_org_id number;
3545 l_org_code varchar2(100);
3546 begin
3547 msc_sda_utils.println('sendRows in ');
3548 open c_child_row_count(p_query_id, g_next_rowset_index);
3549 fetch c_child_row_count into l_count;
3550 close c_child_row_count;
3551
3552 for c_child_row in c_row_values_cur(p_query_id, to_number(null), g_next_rowset_index)
3553 loop
3554 l_org_list_id := c_child_row.org_list_id;
3555 l_org_list := c_child_row.org_list;
3556 l_org_list_state := c_child_row.org_list_state;
3557 l_inst_id := c_child_row.inst_id;
3558 l_org_id := c_child_row.org_id;
3559 l_org_code := c_child_row.org_code;
3560
3561 --6736491 bugfix, global will also contain orgs now
3562 /*
3563 if (p_view_type = c_fcstview and c_child_row.region_id = c_global_reg_type) then
3564 l_org_list_id := c_mbp_null_value;
3565 l_org_list := c_null_space;
3566 l_org_list_state := c_nodrill_state;
3567 l_inst_id := null;
3568 l_org_id := null;
3569 l_org_code := null;
3570 end if;
3571 */
3572 l_token := to_char(c_child_row.row_index);
3573 l_token1 := c_field_seperator || nvl(to_char(c_child_row.region_list_id), c_null_space)
3574 || c_field_seperator || nvl(to_char(c_child_row.region_list), c_null_space)
3575 || c_field_seperator || nvl(to_char(c_child_row.region_list_state), c_null_space)
3576 || c_field_seperator || nvl(to_char(c_child_row.region_id), c_null_space)
3577 || c_field_seperator || nvl(to_char(c_child_row.region_code), c_null_space);
3578 l_token2 := c_field_seperator || nvl(to_char(l_org_list_id), c_null_space)
3579 || c_field_seperator || nvl(l_org_list, c_null_space)
3580 || c_field_seperator || nvl(to_char(l_org_list_state), c_null_space)
3581 || c_field_seperator || nvl(to_char(l_inst_id), c_null_space)
3582 || c_field_seperator || nvl(to_char(l_org_id), c_null_space)
3583 || c_field_seperator || nvl(l_org_code, c_null_space)
3584 || c_field_seperator || nvl(to_char(c_child_row.top_item_id), c_null_space)
3585 || c_field_seperator || nvl(c_child_row.top_item_name, c_null_space)
3586 || c_field_seperator || nvl(to_char(c_child_row.top_item_name_state), c_null_space)
3587 || c_field_seperator || nvl(to_char(c_child_row.item_id), c_null_space)
3588 || c_field_seperator || nvl(c_child_row.item_name, c_null_space);
3589
3590 if (g_view_type = c_sdview) then
3591 l_token := l_token || l_token2;
3592 elsif (p_view_type = c_fcstview) then
3593 l_token := l_token || l_token1 || l_token2;
3594 elsif (p_view_type = c_histview) then
3595 l_token := l_token || l_token1 || l_token2;
3596 end if;
3597
3598 if (l_one_record is null) then
3599 if (p_view_type = c_sdview) then
3600 l_one_record := c_sdview_rheader_data || c_bang_separator || l_count || c_record_seperator || l_token;
3601 elsif (p_view_type = c_fcstview) then
3602 l_one_record := c_fcstview_rheader_data || c_bang_separator || l_count || c_record_seperator || l_token;
3603 elsif (p_view_type = c_histview) then
3604 l_one_record := c_histview_rheader_data || c_bang_separator || l_count || c_record_seperator || l_token;
3605 end if;
3606 else
3607 l_one_record := c_record_seperator || l_token;
3608 end if;
3609 msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_rheader_data);
3610 end loop;
3611 msc_sda_utils.println('sendRows out');
3612 end sendRows;
3613
3614 procedure sendSDData(p_row_index number,
3615 p_orglist_action number, p_itemlist_action number, p_action_node number,
3616 p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3617 p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3618 begin
3619 --flushs msc_analysis_query with row_header_information
3620 flushSDRows(g_sd_query_id, p_row_index, p_orglist_action, p_itemlist_action, p_action_node);
3621
3622 if ( p_orglist_action = c_expanded_state or p_itemlist_action = c_expanded_state ) then
3623 sendRows(c_sdview, g_sd_query_id, null, p_row_index, p_rheader_data);
3624 flushAndSendData(c_sdview, g_sd_query_id, p_out_data);
3625 else
3626 sendRows(c_sdview, g_sd_query_id, p_row_index, null, p_rheader_data);
3627 flushAndSendData(c_sdview, g_sd_query_id, p_out_data);
3628 end if;
3629 --p_out_data := msc_sda_utils.maxCharTbl(0); --pabram..testing purpose only..need to remove later
3630 end sendSDData;
3631
3632 procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3633 p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3634 p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3635 p_out_data in out nocopy msc_sda_utils.maxCharTbl,
3636 p_out_data2 in out nocopy msc_sda_utils.maxCharTbl) is
3637
3638 l_query_id number;
3639 begin
3640 if (p_view_type = c_fcstview) then
3641 l_query_id := g_fcst_query_id;
3642 elsif (p_view_type = c_histview) then
3643 l_query_id := g_hist_query_id;
3644 end if;
3645 --flushes msc_analysis_query with row_header_information
3646 flushFcstHistoryRows(p_view_type, l_query_id, p_row_index, p_reglist_action, p_orglist_action, p_itemlist_action, p_action_node);
3647
3648 if ( p_reglist_action = c_expanded_state or p_orglist_action = c_expanded_state or p_itemlist_action = c_expanded_state ) then
3649 sendRows(p_view_type, l_query_id, null, p_row_index, p_rheader_data);
3650 flushAndSendData(p_view_type, l_query_id, p_out_data);
3651 else
3652 sendRows(p_view_type, l_query_id, p_row_index, null, p_rheader_data);
3653 flushAndSendData(p_view_type, l_query_id, p_out_data);
3654 end if;
3655
3656 if ( p_view_type = c_fcstview and p_row_index = c_first_row_index and p_reglist_action = c_expanded_state) then
3657 flushAndSendAddlData(p_view_type, l_query_id, p_out_data2);
3658 end if;
3659
3660 --delete from msc_analysis_query_temp; insert into msc_analysis_query_temp select * from msc_analysis_query; commit;
3661 --delete from msc_form_query_temp; insert into msc_form_query_temp select * from msc_form_query; commit;
3662 end sendFCSTHistoryData;
3663
3664 procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3665 p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3666 p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3667 begin
3668 msc_sda_utils.sendSDRowTypes(p_sd_data);
3669 msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3670 msc_sda_utils.sendHistRowTypes(p_hist_data);
3671 end sendRowTypes;
3672
3673 procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3674 begin
3675 msc_sda_utils.getWorkSheetPrefData(p_out_data, p_refresh_flag);
3676 end getWorkSheetPrefData;
3677
3678 procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3679 begin
3680 msc_sda_utils.sendNlsMessages(p_out_data);
3681 end sendNlsMessages;
3682
3683 procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3684 begin
3685 msc_sda_utils.spreadTableMessages(p_out_data);
3686 end spreadTableMessages;
3687
3688 procedure set_shuttle_from_to(p_lookup_type varchar2, p_lookup_code_list varchar2,
3689 p_from_list out nocopy varchar2, p_to_list out nocopy varchar2) is
3690 begin
3691 msc_sda_utils.set_shuttle_from_to(p_lookup_type, p_lookup_code_list, p_from_list, p_to_list);
3692 end set_shuttle_from_to;
3693
3694 procedure update_pref_set (p_name varchar2, p_desc varchar2,
3695 p_days number, p_weeks number, p_periods number,
3696 p_factor number, p_decimal_places number,
3697 p_sd_row_list varchar2, p_fcst_row_list varchar2) is
3698 begin
3699 msc_sda_utils.update_pref_set(p_name, p_desc, p_days, p_weeks, p_periods,
3700 p_factor, p_decimal_places, p_sd_row_list, p_fcst_row_list);
3701 end update_pref_set;
3702
3703 procedure save_item_folder(p_folder_name varchar, p_folder_value varchar,
3704 p_default_flag number, p_public_flag number) is
3705 begin
3706 msc_sda_utils.save_item_folder(p_folder_name, p_folder_value,
3707 p_default_flag, p_public_flag);
3708 end save_item_folder;
3709
3710 procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
3711 begin
3712 msc_sda_utils.update_close_settings(p_event, p_event_list);
3713 end update_close_settings;
3714
3715 procedure send_close_settings(p_item_folder_save_list out nocopy varchar2,
3716 p_save_settings_list out nocopy varchar2) is
3717 begin
3718 msc_sda_utils.send_close_settings(p_item_folder_save_list, p_save_settings_list);
3719 end send_close_settings;
3720
3721 procedure getCommentsData(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3722 begin
3723 msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_out_data, c_sdview_comments_data_ref);
3724 end getCommentsData;
3725
3726 procedure getRegionList(p_out_data out nocopy varchar2) is
3727 cursor c_regions_cur is
3728 select distinct
3729 number2 region_id
3730 from msc_form_query
3731 where query_id = g_region_query_id;
3732 begin
3733 for c_regions in c_regions_cur
3734 loop
3735 if p_out_data is null then
3736 p_out_data := to_char(c_regions.region_id);
3737 else
3738 p_out_data := p_out_data || c_comma_separator|| to_char(c_regions.region_id);
3739 end if;
3740 end loop;
3741 end getRegionList;
3742
3743 procedure getOrgList(p_out_data out nocopy varchar2) is
3744 cursor c_orgs_cur is
3745 select distinct
3746 '('||number2||','||number3||')' org_id
3747 from msc_form_query
3748 where query_id = g_org_query_id
3749 and number2 is not null
3750 and number3 is not null;
3751 begin
3752 for c_orgs in c_orgs_cur
3753 loop
3754 if p_out_data is null then
3755 p_out_data := to_char(c_orgs.org_id);
3756 else
3757 p_out_data := p_out_data || c_comma_separator|| to_char(c_orgs.org_id);
3758 end if;
3759 end loop;
3760 end getOrgList;
3761
3762 procedure getItemList(p_out_data out nocopy varchar2) is
3763 cursor c_items_cur is
3764 select distinct
3765 number2 item_id
3766 from msc_form_query
3767 where query_id = g_chain_query_id;
3768 begin
3769 for c_items in c_items_cur
3770 loop
3771 if p_out_data is null then
3772 p_out_data := to_char(c_items.item_id);
3773 else
3774 p_out_data := p_out_data || c_comma_separator|| to_char(c_items.item_id);
3775 end if;
3776 end loop;
3777 end getItemList;
3778
3779 procedure getOrderTypesList(p_view_type number, p_row_offset number,
3780 p_order_type_list out nocopy varchar2, p_from_table out nocopy varchar2,
3781 p_part_condition out nocopy number) is
3782 begin
3783 if (p_view_type = c_histview) then --{
3784 p_from_table := 'msc_demands';
3785 if (p_row_offset = c_row_returns_hist) then
3786 p_order_type_list := c_returns_hist;
3787 elsif (p_row_offset = c_row_dmd_hist) then
3788 p_order_type_list := c_dmd_hist;
3789 end if;
3790 return;
3791 end if; --}
3792
3793 if (p_view_type = c_fcstview) then --{
3794 if (p_row_offset = c_row2_total_fcst) then
3795 p_from_table := 'msc_demands';
3796 p_order_type_list := to_char(c_dmd2_net_fcst) ||','|| to_char(c_dmd2_dmd_schd);
3797 elsif (p_row_offset = c_row2_orig_fcst) then
3798 p_from_table := 'msc_demands';
3799 p_order_type_list := c_dmd2_net_fcst;
3800 elsif (p_row_offset = c_row2_consumed_fcst) then
3801 p_from_table := 'msc_forecast_updates';
3802 p_order_type_list := null;
3803 elsif (p_row_offset = c_row2_net_fcst) then
3804 p_from_table := 'msc_demands';
3805 p_order_type_list := c_dmd2_net_fcst;
3806 elsif (p_row_offset = c_row2_over_consmptn) then
3807 p_from_table := 'msc_forecast_updates';
3808 p_order_type_list := null;
3809 elsif (p_row_offset = c_row2_manual_fcst) then
3810 p_from_table := 'msc_demands';
3811 p_order_type_list := c_dmd2_net_fcst;
3812 elsif (p_row_offset = c_row2_dmd_schd) then
3813 p_from_table := 'msc_demands';
3814 p_order_type_list := c_dmd2_dmd_schd;
3815 elsif (p_row_offset = c_row2_bestfit_fcst) then
3816 p_from_table := 'msc_demands';
3817 p_order_type_list := c_dmd2_bestfit_fcst;
3818 elsif (p_row_offset = c_row2_total_ret_fcst) then
3819 p_from_table := 'msc_supplies';
3820 p_order_type_list := to_char(c_sup2_rtns_fcst) ||','|| to_char(c_sup2_rtns_dmd_schd);
3821 elsif (p_row_offset = c_row2_ret_fcst) then
3822 p_from_table := 'msc_supplies';
3823 p_order_type_list := c_sup2_rtns_fcst;
3824 elsif (p_row_offset = c_row2_ret_dmd_schd) then
3825 p_from_table := 'msc_supplies';
3826 p_order_type_list := c_sup2_rtns_dmd_schd;
3827 elsif (p_row_offset = c_row2_ret_manual_fcst) then
3828 p_from_table := 'msc_supplies';
3829 p_order_type_list := c_sup2_rtns_fcst;
3830 elsif (p_row_offset = c_row2_ret_bestfit_fcst) then
3831 p_from_table := 'msc_supplies';
3832 p_order_type_list := c_sup2_rtns_bestfit_fcst;
3833 elsif (p_row_offset = c_row2_usage_fcst) then
3834 p_from_table := 'msc_demands';
3835 p_order_type_list := c_dmd2_usage_fcst;
3836 elsif (p_row_offset = c_row2_popultn_fcst) then
3837 p_from_table := 'msc_demands';
3838 p_order_type_list := c_dmd2_popu_fcst;
3839 elsif (p_row_offset = c_row2_type_16) then
3840 p_from_table := 'msc_demands';
3841 p_order_type_list := c_dmd2_popu_fcst;
3842 end if;
3843 return;
3844 end if; --}
3845
3846 if (p_view_type = c_sdview) then --{
3847 if (p_row_offset = c_row_net_fcst) then --1;
3848 p_from_table := 'msc_demands';
3849 p_order_type_list := c_dmd_fcst;
3850 elsif (p_row_offset = c_row_so) then --2;
3851 p_from_table := 'msc_demands';
3852 p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3853 elsif (p_row_offset = c_row_iso_field_org) then --3;
3854 p_from_table := 'msc_demands';
3855 p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3856 elsif (p_row_offset = c_row_indepndt_dmd) then --4;
3857 p_from_table := 'msc_demands';
3858 p_order_type_list := to_char(c_dmd_fcst) ||', '|| to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3859 elsif (p_row_offset = c_row_iso) then --5;
3860 p_from_table := 'msc_demands';
3861 p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3862 elsif (p_row_offset = c_row_pod) then --6;
3863 p_from_table := 'msc_demands';
3864 p_order_type_list := c_dmd_pod;
3865 elsif (p_row_offset = c_row_dependnt_dmd) then --7;
3866 p_from_table := 'msc_demands';
3867 p_order_type_list := to_char(c_dmd_pod);
3868 elsif (p_row_offset = c_row_other_dmd) then --8;
3869 p_from_table := 'msc_demands';
3870 p_order_type_list := to_char(c_dmd_mps) ||', '|| to_char(c_dmd_manual_mds);
3871 elsif (p_row_offset = c_row_total_dmd) then --9;
3872 p_from_table := 'msc_demands';
3873 p_order_type_list := to_char(c_dmd_fcst) ||', '|| to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds)
3874 ||', '|| to_char(c_dmd_pod)
3875 ||', '|| to_char(c_dmd_mps) ||', '|| to_char(c_dmd_manual_mds);
3876 elsif (p_row_offset = c_row_onhand) then --10;
3877 p_from_table := 'msc_supplies';
3878 p_order_type_list := c_sup_onhand;
3879 elsif (p_row_offset = c_row_transit) then --11;
3880 p_from_table := 'msc_supplies';
3881 p_order_type_list := c_sup_intrnst_shpmt;
3882 elsif (p_row_offset = c_row_receiving) then --12;
3883 p_from_table := 'msc_supplies';
3884 p_order_type_list := c_sup_intrnst_rec;
3885 elsif (p_row_offset = c_row_new_buy_po) then --13;
3886 p_from_table := 'msc_supplies';
3890 p_order_type_list := c_sup_new_buy_po_req;
3887 p_order_type_list := c_sup_new_buy_po;
3888 elsif (p_row_offset = c_row_new_buy_po_req) then --14;
3889 p_from_table := 'msc_supplies';
3891 elsif (p_row_offset = c_row_intrnl_rpr_ordr) then --15;
3892 p_from_table := 'msc_supplies';
3893 p_order_type_list := to_char(c_sup_intrnl_rpr_ordr);
3894 elsif (p_row_offset = c_row_xtrnl_rpr_ordr) then --16;
3895 p_from_table := 'msc_supplies';
3896 p_order_type_list := to_char(c_sup_xtrnl_rpr_ordr)||', '|| to_char(c_sup_ext_rep_req);
3897 elsif (p_row_offset = c_row_inbnd_ship) then --17;
3898 p_from_table := 'msc_supplies';
3899 p_order_type_list := c_sup_new_buy_po_req;
3900 elsif (p_row_offset = c_row_rpr_wo) then --18;
3901 p_from_table := 'msc_supplies';
3902 p_order_type_list := c_sup_rpr_wo ||', '|| to_char(c_sup_rpr_wo_ext_rep_supp) ;
3903 elsif (p_row_offset = c_row_plnd_new_buy_ordr) then --19;
3904 p_from_table := 'msc_supplies';
3905 p_order_type_list := c_sup_plnd_new_buy_ordr;
3906 elsif (p_row_offset = c_row_plnd_intrnl_rpr_ordr) then --20;
3907 p_from_table := 'msc_supplies';
3908 p_order_type_list := c_sup_plnd_intrnl_rpr_ordr;
3909 elsif (p_row_offset = c_row_plnd_xtrnl_rpr_ordr) then --21;
3910 p_from_table := 'msc_supplies';
3911 p_order_type_list := c_sup_plnd_xtrnl_rpr_ordr;
3912 elsif (p_row_offset = c_row_plnd_inbnd_ship) then --22;
3913 p_from_table := 'msc_supplies';
3914 p_order_type_list := c_sup_plnd_inbnd_ship;
3915 elsif (p_row_offset = c_row_plnd_rpr_wo) then --23;
3916 p_from_table := 'msc_supplies';
3917 p_order_type_list := c_sup_plnd_rpr_wo;
3918 elsif (p_row_offset = c_row_plnd_warr_ordr) then --24;
3919 p_from_table := null;
3920 p_order_type_list := null;
3921 elsif (p_row_offset = c_row_total_supply) then --25;
3922 p_from_table := 'msc_supplies';
3923 p_order_type_list := to_char(c_sup_intrnst_shpmt) ||','|| to_char(c_sup_intrnst_rec) ||','|| to_char(c_sup_new_buy_po) ||','||
3924 to_char(c_sup_new_buy_po_req) ||','|| to_char(c_sup_intrnl_rpr_ordr) ||','|| to_char(c_sup_xtrnl_rpr_ordr) ||','||
3925 to_char(c_sup_rpr_wo) ||','|| to_char(c_row_plnd_new_buy_ordr) ||','|| to_char(c_sup_plnd_intrnl_rpr_ordr) ||','||
3926 to_char(c_sup_plnd_xtrnl_rpr_ordr) ||','|| to_char(c_sup_plnd_inbnd_ship) ||','|| to_char(c_sup_plnd_rpr_wo)
3927 ||','|| to_char(c_sup_plnd_new_buy_ordr) ||','|| to_char(c_sup_onhand)
3928 ||','|| to_char(c_sup_rpr_wo) ||', '|| to_char(c_sup_rpr_wo_ext_rep_supp) ||', '|| to_char(c_sup_ext_rep_req);
3929 elsif (p_row_offset = c_row_ss_supply) then --26;
3930 p_from_table := null;
3931 p_order_type_list := '-1';
3932 --elsif (p_row_offset = c_row_total_uncons_dmd) then --27;
3933 --p_from_table := null;
3934 --p_order_type_list := '-1';
3935 elsif (p_row_offset = c_row_ss_level) then --28;
3936 p_from_table := null;
3937 p_order_type_list := '-1';
3938 elsif (p_row_offset = c_row_target_level) then --29;
3939 p_from_table := 'msc_demands';
3940 p_order_type_list := c_dmd_fcst;
3941 p_part_condition := c_part_good;
3942 elsif (p_row_offset = c_row_max_level) then --30;
3943 p_from_table := null;
3944 p_order_type_list := '-1';
3945 elsif (p_row_offset = c_row_pab) then --31;
3946 p_from_table := 'msc_orders_v';
3947 p_order_type_list := c_mbp_null_value;
3948 p_part_condition := c_part_good;
3949 elsif (p_row_offset = c_row_poh) then --32;
3950 p_from_table := 'msc_orders_v';
3951 p_order_type_list := c_mbp_null_value;
3952 p_part_condition := c_part_good;
3953 elsif (p_row_offset = c_row_defc_iso) then --33;
3954 p_from_table := 'msc_demands';
3955 p_order_type_list := c_dmd_defc_iso;
3956 p_part_condition := c_part_bad;
3957 elsif (p_row_offset = c_row_plnd_defc_pod) then --34;
3958 p_from_table := 'msc_demands';
3959 p_order_type_list := c_dmd_defc_pod;
3960 p_part_condition := c_part_bad;
3961 elsif (p_row_offset = c_row_defc_part_dmd) then --35;
3962 p_from_table := 'msc_demands';
3963 p_order_type_list := to_char(c_dmd_defc_part_dmd)||','||to_char(c_dmd_defc_plnd_part_dmd);
3964 p_part_condition := c_part_bad;
3965 elsif (p_row_offset = c_row_total_defc_part_dmd) then --36;
3966 p_from_table := 'msc_demands';
3967 p_order_type_list := to_char(c_dmd_defc_part_dmd)||','||to_char(c_dmd_defc_plnd_part_dmd);
3968 p_part_condition := c_part_bad;
3969 elsif (p_row_offset = c_row_defc_onhand) then --37;
3970 p_from_table := 'msc_supplies';
3971 p_order_type_list := c_sup_defc_onhand;
3972 p_part_condition := c_part_bad;
3973 elsif (p_row_offset = c_row_returns) then --38;
3974 p_from_table := 'msc_supplies';
3975 p_order_type_list := c_sup_defc_returns;
3976 p_part_condition := c_part_bad;
3977 elsif (p_row_offset = c_row_defc_inbnd_ship) then --39;
3978 p_from_table := 'msc_supplies';
3979 p_order_type_list := c_sup_defc_inbnd_ship;
3980 p_part_condition := c_part_bad;
3981 elsif (p_row_offset = c_row_defc_plnd_inbnd_ship) then --40;
3982 p_from_table := 'msc_supplies';
3983 p_order_type_list := c_sup_defc_plnd_inbnd_ship;
3984 p_part_condition := c_part_bad;
3985 elsif (p_row_offset = c_row_defc_transit) then --41;
3986 p_from_table := 'msc_supplies';
3987 p_order_type_list := c_sup_defc_transit;
3988 p_part_condition := c_part_bad;
3989 elsif (p_row_offset = c_row_defc_rec) then --42;
3990 p_from_table := 'msc_supplies';
3991 p_order_type_list := c_sup_defc_rec;
3992 p_part_condition := c_part_bad;
3993 elsif (p_row_offset = c_row_total_defc_supply) then --43;
3994 p_from_table := 'msc_supplies';
3995 p_order_type_list := to_char(c_sup_defc_returns) ||','|| to_char(c_sup_defc_inbnd_ship) ||','|| to_char(c_sup_defc_onhand)
3999 p_from_table := 'msc_orders_v';
3996 ||','|| to_char(c_row_defc_plnd_inbnd_ship) ||','|| to_char(c_sup_defc_transit) ||','|| to_char(c_sup_defc_rec);
3997 p_part_condition := c_part_bad;
3998 elsif (p_row_offset = c_row_defc_pab) then --44;
4000 p_order_type_list := c_mbp_null_value;
4001 p_part_condition := c_part_bad;
4002 elsif (p_row_offset = c_row_uncons_dmd) then --46;
4003 p_from_table := 'msc_demands';
4004 p_order_type_list := c_dmd_uncons_dmd;
4005 end if;
4006
4007 end if; -- }
4008
4009 return;
4010 end getOrderTypesList;
4011
4012 procedure getDrillDownDetails(p_view_type number, p_row_index number, p_row_offset number,
4013 p_date1 varchar2, p_date2 varchar2, p_from_table out nocopy varchar2, p_mfq_id out nocopy number) is
4014 l_row c_row_values_cur%rowtype;
4015 l_query_id number;
4016 l_order_type_list varchar2(1000);
4017
4018 l_reg_id_list varchar2(1000);
4019 l_org_id_list varchar2(1000);
4020 l_item_id_list varchar2(1000);
4021 sql_stmt varchar2(10000);
4022
4023 l_date1 date;
4024 l_date2 date;
4025 l_part_condition number;
4026 begin
4027 msc_sda_utils.println(' getDrillDownDetails in');
4028
4029 l_date1 := to_date(p_date1,c_date_format);
4030 l_date2 := to_date(p_date2,c_date_format);
4031
4032 --9755798 bugfix
4033 if (trunc(l_date1) < trunc(g_plan_start_date)) then
4034 l_date1 := trunc(g_plan_start_date - 1000); --approx 3 years from plan start
4035 l_date2 := trunc(g_plan_start_date -1);
4036 end if;
4037 --9755798 bugfix ends
4038
4039 l_query_id := getAnalysisQueryId(p_view_type);
4040 open c_row_values_cur(l_query_id, p_row_index, to_number(null));
4041 fetch c_row_values_cur into l_row;
4042 close c_row_values_cur;
4043
4044 if l_row.region_id is null then
4045 --getRegionList(l_reg_id_list);
4046 if ( l_row.region_list_id = c_all_region_type) then
4047 l_reg_id_list := ' and 1=1 ';
4048 end if;
4049 else
4050 l_reg_id_list := l_row.region_id;
4051 if ( l_row.region_id = c_global_reg_type) then
4052 l_reg_id_list := ' and organization_id in ('|| c_global_org_id ||') ';
4053 elsif ( l_row.region_id = c_local_reg_type) then
4054 l_reg_id_list := ' and zone_id is null ';
4055 else
4056 l_reg_id_list := ' and zone_id in ('|| l_reg_id_list ||') ';
4057 end if;
4058 end if;
4059
4060 if (l_row.inst_id is null) then
4061 getOrgList(l_org_id_list);
4062 else
4063 l_org_id_list := '('||l_row.inst_id||','||l_row.org_id||')';
4064 end if;
4065
4066 if (l_row.item_id is null) then
4067 getItemList(l_item_id_list);
4068 else
4069 l_item_id_list := l_row.item_id;
4070 end if;
4071
4072 getOrderTypesList(p_view_type, p_row_offset , l_order_type_list, p_from_table, l_part_condition);
4073 /* if (l_part_condition is null) then
4074 l_part_condition := c_part_good;
4075 end if;
4076 */
4077 if (p_from_table = 'msc_forecast_updates') then
4078 return;
4079 end if;
4080
4081 p_mfq_id := msc_sda_utils.getNewFormQueryId;
4082 sql_stmt := 'insert into msc_form_query ('||
4083 ' query_id, last_update_date, last_updated_by, creation_date, created_by,number1) '||
4084 ' select distinct '|| p_mfq_id ||', sysdate, 1, sysdate, 1, ';
4085
4086 if (p_from_table = 'msc_supplies') then
4087 sql_stmt := sql_stmt || ' transaction_id from '|| p_from_table;
4088 elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
4089 sql_stmt := sql_stmt || ' demand_id from '|| p_from_table;
4090 elsif (p_from_table = 'msc_orders_v') then
4091 sql_stmt := sql_stmt || ' transaction_id from '|| p_from_table;
4092 end if;
4093
4094 sql_stmt := sql_stmt || ' where plan_id = '||g_plan_id ;
4095 if (l_reg_id_list is not null) then
4096 sql_stmt := sql_stmt || l_reg_id_list ; --special handling for region
4097 end if;
4098 if (l_org_id_list is not null) then
4099 if l_row.region_id = c_global_reg_type then
4100 null;
4101 else
4102 sql_stmt := sql_stmt || ' and (sr_instance_id,organization_id) in ('|| l_org_id_list ||') ';
4103 end if;
4104 end if;
4105 if (l_item_id_list is not null) then
4106 sql_stmt := sql_stmt || ' and inventory_item_id in ('|| l_item_id_list ||') ';
4107 end if;
4108
4109 if (p_from_table = 'msc_supplies') then
4110 sql_stmt := sql_stmt || ' and order_type in ('|| l_order_type_list ||')';
4111 sql_stmt := sql_stmt || ' and trunc(nvl(firm_date,new_schedule_date)) between trunc(:l_date1) and trunc(:l_date2) ';
4112 sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id ;
4113 if l_part_condition is not null then
4114 sql_stmt := sql_stmt||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
4115 end if;
4116
4117 msc_sda_utils.println('msc_supplies '||sql_stmt);
4118 execute immediate sql_stmt using l_date1, l_date2;
4119 elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
4120 sql_stmt := sql_stmt || ' and origination_type in ('|| l_order_type_list ||')';
4121 sql_stmt := sql_stmt || ' and trunc(nvl(firm_date,using_assembly_demand_date)) between trunc(:l_date1) and trunc(:l_date2) ';
4122 sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id ;
4123 if l_part_condition is not null then
4124 sql_stmt := sql_stmt||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
4125 end if;
4126
4127 if (p_view_type = c_sdview) then
4128 if (p_row_offset = c_row_so) then
4129 sql_stmt := sql_stmt || ' and disposition_id is null ';
4130 elsif (p_row_offset = c_row_iso_field_org) then
4131 sql_stmt := sql_stmt || ' and disposition_id is not null ';
4132 elsif (p_row_offset = c_row_iso) then
4133 sql_stmt := sql_stmt || ' and disposition_id is not null ';
4134 end if;
4135 end if;
4136
4137 msc_sda_utils.println('msc_demands, msc_forecast_updates '||sql_stmt);
4138 execute immediate sql_stmt using l_date1, l_date2;
4139 elsif (p_from_table = 'msc_orders_v') then
4140 sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id ;
4141 if l_part_condition is not null then
4142 sql_stmt := sql_stmt||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
4143 end if;
4144 if (l_order_type_list = c_mbp_null_value) then
4145 sql_stmt := sql_stmt || ' and trunc(nvl(firm_date,new_due_date)) <= trunc(:l_date2) ';
4146 msc_sda_utils.println('msc_orders_v '||sql_stmt);
4147 execute immediate sql_stmt using l_date2;
4148 else
4149 sql_stmt := sql_stmt || ' and order_type in ('|| l_order_type_list ||')';
4150 sql_stmt := sql_stmt || ' and trunc(nvl(firm_date,new_due_date)) between trunc(:l_date1) and trunc(:l_date2) ';
4151 msc_sda_utils.println('msc_orders_v 2 '||sql_stmt);
4152 execute immediate sql_stmt using l_date1, l_date2;
4153 end if;
4154 else
4155 msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
4156 return;
4157 end if;
4158 msc_sda_utils.println('out '||sql_stmt);
4159 msc_sda_utils.println(' getDrillDownDetails out');
4160 end getDrillDownDetails;
4161
4162 -----
4163 ----- send apis done
4164 -----
4165
4166 function populateSupersessionChain(p_plan number, p_item number,p_related_flag number default null) return number is
4167 l_query_id number;
4168 begin
4169 l_query_id := msc_sda_utils.flushSupersessionChain(p_plan, p_item,p_related_flag);
4170 return l_query_id;
4171 end populateSupersessionChain;
4172 end MSC_SDA_PKG;