[Home] [Help]
PACKAGE BODY: APPS.MSC_X_HZ_PLAN
Source
1 PACKAGE BODY MSC_X_HZ_PLAN AS
2 /* $Header: MSCXHZPB.pls 120.7.12010000.2 2008/08/11 13:04:02 hbinjola ship $ */
3
4 NOT_SELECTED CONSTANT NUMBER(1) := 0;
5 SALES_FORECAST CONSTANT NUMBER(1) := 1;
6 ORDER_FORECAST_CST CONSTANT NUMBER(1) := 2;
7 SUPPLY_COMMIT CONSTANT NUMBER(1) := 3; -- SUPPLY COMMIT
8 G_RETURNS_FORECAST CONSTANT NUMBER(2) := 50; -- bug#6893383
9 G_DEFECTIVE_OUTBOUND_SHIPMENT CONSTANT NUMBER(2) := 51; -- bug#6893383
10 HISTORICAL_SALES CONSTANT NUMBER(1) := 4;
11 SELL_THRU_FORECAST CONSTANT NUMBER(1) := 5;
12 NEGOTIATED_CAPACITY CONSTANT NUMBER(1) := 6;
13 SAFETY_STOCK CONSTANT NUMBER(1) := 7;
14 PROJ_SAFETY_STOCK CONSTANT NUMBER(1) := 8;
15 ALLOCATED_ONHAND CONSTANT NUMBER(1) := 9;
16 UNALLOCATED_ONHAND CONSTANT NUMBER(2) := 10;
17 PROJ_UNALOC_AVL_BAL CONSTANT NUMBER(2) := 11;
18 PROJ_ALLOC_AVL_BAL CONSTANT NUMBER(2) := 12;
19 PURCHASE_ORDER CONSTANT NUMBER(2) := 13;
20 SALES_ORDER CONSTANT NUMBER(2) := 14;
21 ASN CONSTANT NUMBER(2) := 15;
22 SHIPMENT_RECEIPT CONSTANT NUMBER(2) := 16;
23 INTRANSIT CONSTANT NUMBER(2) := 17;
24 WORK_ORDER CONSTANT NUMBER(2) := 45;
25 PO_ACK CONSTANT NUMBER(2) := 21;
26 REPLENISHMENT CONSTANT NUMBER(2) := 19;
27 REQUISITION CONSTANT NUMBER(2) := 20;
28 RUN_TOT_SUPPLY CONSTANT NUMBER(2) := 46;
29 RUN_TOT_DEMAND CONSTANT NUMBER(2) := 47;
30 PO_FROM_PLAN CONSTANT NUMBER(2) := 22;
31 RELEASED_PLAN CONSTANT NUMBER(2) := 23;
32 PLANNED_ORDER CONSTANT NUMBER(2) := 24;
33 PROJ_AVAIL_BAL CONSTANT NUMBER(2) := 27;
34
35 DAY_BUCKET CONSTANT NUMBER(1) := 1;
36 WEEK_BUCKET CONSTANT NUMBER(1) := 2;
37 MONTH_BUCKET CONSTANT NUMBER(1) := 3;
38 SELECTED CONSTANT NUMBER(2) := 99;
39 v_temp_cnt number := 0;
40
41 module CONSTANT VARCHAR2(24) := 'msc.plsql.MSC_X_HZ_PLAN.';
42
43
44
45 /**
46 * The following procedure
47 * calculates the buckets and aggregates the quantites
48 * into appropriate buckets for display on the HZ View.
49 */
50 Procedure populate_bucketed_quantity(
51 arg_query_id OUT NOCOPY NUMBER,
52 arg_next_link OUT NOCOPY VARCHAR2,
53 arg_num_rowset OUT NOCOPY NUMBER,
54 arg_err_msg OUT NOCOPY VARCHAR2,
55 arg_default_pref OUT NOCOPY NUMBER,
56 arg_pref_name IN VARCHAR2, -- DEFAULT NULL,
57 arg_start_row IN NUMBER, -- DEFAULT 1,
58 arg_end_row IN NUMBER, -- DEFAULT 25,
59 arg_item_sort IN VARCHAR2, -- DEFAULT 'ASC',
60 arg_from_date IN DATE, -- DEFAULT sysdate,
61 arg_where_clause IN VARCHAR2, -- DEFAULT NULL,
62 arg_plan_under IN VARCHAR2, -- DEFAULT 'N',
63 arg_plan_over IN VARCHAR2, -- DEFAULT 'N',
64 arg_actual_under IN VARCHAR2, -- DEFAULT 'N',
65 arg_actual_over IN VARCHAR2 -- DEFAULT 'N'
66 )
67 IS
68
69 TYPE num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
70 TYPE temp_cursor IS REF CURSOR;
71 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
72 TYPE small_string IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
73 TYPE string IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
74 TYPE big_string IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
75
76 ORG_AGG CONSTANT NUMBER(1) := 0;
77 COMPANY_AGG CONSTANT NUMBER(1) := 1;
78 ALL_AGG CONSTANT NUMBER(1) := 2;
79 ITEM_AGG CONSTANT NUMBER(1) := 0;
80
81 THIRD_PARTY CONSTANT NUMBER(1) := 0;
82
83 error VARCHAR2(2000) ;
84 v_pref VARCHAR2(100);
85 v_graphtype NUMBER(1);
86 v_category VARCHAR2(250);
87 v_graphtitle VARCHAR2(250);
88 v_past_due_hdr VARCHAR2(250);
89 v_user_company VARCHAR2(250);
90 v_default_cal_code VARCHAR2(250);
91 v_calendar_code VARCHAR2(250);
92 v_customer_id NUMBER;
93 v_customer_site_id NUMBER;
94 v_supplier_id NUMBER;
95 v_supplier_site_id NUMBER;
96 g_multiple_sites NUMBER := -1;
97 v_sr_instance_id NUMBER;
98
99
100 v_lookup_name VARCHAR2(250);
101
102 record_cnt number ;
103 k number;
104
105 osce_bucketed_plan temp_cursor;
106 -- variables for the orders selected in the user prefs
107
108 v_sales_forecast NUMBER(2) := 0;
109 v_order_forecast NUMBER(2) := 0;
110 v_supply_commit NUMBER(2) := 0;
111 v_returns_forecast NUMBER(2) := 0; -- bug#6893383
112 v_def_outbound_shipment NUMBER(2) := 0; -- bug#6893383
113 v_hist_sales NUMBER(2) := 0;
114 v_sell_thru_fcst NUMBER(2) := 0;
115 v_negotiated_capacity NUMBER(2) := 0;
116 v_safety_stock NUMBER(2) := 0;
117 v_proj_safety_stock NUMBER(2) := 0;
118 v_alloc_onhand NUMBER(2) := 0;
119 v_unalloc_onhand NUMBER(2) := 0;
120 v_proj_unalloc_avl_bal NUMBER(2) := 0;
121 v_proj_alloc_avl_bal NUMBER(2) := 0;
122 v_purchase_order NUMBER(2) := 0;
123 v_sales_order NUMBER(2) := 0;
124 v_asn NUMBER(2) := 0;
125 v_shipment_receipt NUMBER(2) := 0;
126 v_intransit NUMBER(2) := 0;
127 v_work_order NUMBER(2) := 0;
128 v_po_ack NUMBER(2) := 0;
129 v_replenishment NUMBER(2) := 0;
130 v_requisition NUMBER(2) := 0;
131 v_run_tot_supply NUMBER(2) := 0;
132 v_run_tot_demand NUMBER(2) := 0;
133 v_po_from_plan NUMBER(2) := 0;
134 v_released_plan NUMBER(2) := 0;
135 v_planned_order NUMBER(2) := 0;
136 v_proj_avail_bal NUMBER(2) := 0;
137
138 v_shift_days NUMBER := 0;
139
140 -- Net forecast and total supply summary lines
141 v_net_forecast NUMBER(2) := 0;
142 v_total_supply NUMBER(2) := 0;
143 v_delete_purchase_order BOOLEAN := FALSE;
144 v_delete_requisition BOOLEAN := FALSE;
145
146 -- variables for the aggregation of item . co and tp
147 prod_agg NUMBER(1) := 0;
148 myco_agg NUMBER(1) := 0;
149 tpco_agg NUMBER(1) := 0;
150
151 daily_bucket_count NUMBER(2) := 0;
152 weekly_bucket_count NUMBER(2) := 0;
153 period_bucket_count NUMBER(2) := 0;
154
155 -- variables for the orde ranking selected in the user prefs
156 v_o_seller_forecast NUMBER(2) := 0;
157 v_o_forecast NUMBER(2) := 0;
158 v_o_supply_commit NUMBER(2) := 0;
159 v_o_returns_forecast NUMBER(2) := 0; -- bug#6893383
160 v_o_def_outbound_shipment NUMBER(2) := 0; -- bug#6893383
161 v_o_hist_sales NUMBER(2) := 0;
162 v_o_sell_thro_fcst NUMBER(2) := 0;
163 v_o_negcap NUMBER(2) := 0;
164 v_o_ss NUMBER(2) := 0;
165 v_o_pab NUMBER(2) := 0;
166 v_o_projected_ss NUMBER(2) := 0;
167 v_o_alct_onhand NUMBER(2) := 0;
168 v_o_unalct_onhand NUMBER(2) := 0;
169 v_o_po NUMBER(2) := 0;
170 v_o_work_order NUMBER(2) := 0;
171 v_o_po_ack NUMBER(2) := 0;
172 v_o_sales_orders NUMBER(2) := 0;
173 v_o_asn NUMBER(2) := 0;
174 v_o_receiving NUMBER(2) := 0;
175 v_o_transit NUMBER(2) := 0;
176 v_o_wip NUMBER(2) := 0;
177 v_o_req NUMBER(2) := 0;
178 v_o_replenishment NUMBER(2) := 0;
179 v_o_run_tot_supply NUMBER(2) := 0;
180 v_o_run_tot_demand NUMBER(2) := 0;
181 v_o_unalct_prjt_avl_bal NUMBER(2) := 0;
182 v_o_alcat_prjt_avl_bal NUMBER(2) := 0;
183 v_o_gross_requirements NUMBER(2) := 0;
184 v_o_po_from_plan NUMBER(2) := 0;
185 v_o_released_plan NUMBER(2) := 0;
186 v_o_planned_order NUMBER(2) := 0;
187
188 firstrec NUMBER(2) := 0;
189 unbucketed_flag number := -1;
190 g_statement VARCHAR2(4000);
191 curr_date date;
192
193 rec_counter NUMBER := 1;
194 calc_past_due boolean := TRUE;
195 tot_rec number := 1;
196 pagesize number := 1;
197
198 v_last_bkt_date date;
199 p_start_date date;
200
201
202 var_dates calendar_date ; -- Holds the start dates of buckets
203
204 var_item_id num;
205 var_next_item num;
206 var_order num;
207 var_order_rank num;
208 var_qty_nobkt num;
209 var_past_due_qty num;
210
211
212 var_pub_id1 number := 0;
213 var_pub_id2 number := 0;
214 var_pub_id3 number := 0;
215 var_pub_id4 number := 0;
216 var_pub_id5 number := 0;
217 var_pub_id6 number := 0;
218 var_pub_id7 number := 0;
219 var_pub_id8 number := 0;
220 var_pub_id9 number := 0;
221 var_pub_id10 number := 0;
222 var_pub_id11 number := 0;
223 var_pub_id12 number := 0;
224 var_pub_id13 number := 0;
225 var_pub_id14 number := 0;
226 var_pub_id15 number := 0;
227 var_pub_id16 number := 0;
228 var_pub_id17 number := 0;
229 var_pub_id18 number := 0;
230 var_pub_id19 number := 0;
231 var_pub_id20 number := 0;
232 var_pub_id21 number := 0;
233 var_pub_id22 number := 0;
234 var_pub_id23 number := 0;
235 var_pub_id24 number := 0;
236 var_pub_id25 number := 0;
237 var_pub_id26 number := 0;
238 var_pub_id27 number := 0;
239 var_pub_id28 number := 0;
240 var_pub_id29 number := 0;
241 var_pub_id30 number := 0;
242 var_pub_id31 number := 0;
243 var_pub_id32 number := 0;
244 var_pub_id33 number := 0;
245 var_pub_id34 number := 0;
246 var_pub_id35 number := 0;
247 var_pub_id36 number := 0;
248
249
250 var_temp_qty1 number := 0;
251 var_temp1 number := 0;
252 var_temp_qty2 number := 0;
253 var_temp2 number := 0;
254 var_temp_qty3 number := 0;
255 var_temp3 number := 0;
256 var_temp_qty4 number := 0;
257 var_temp4 number := 0;
258 var_temp_qty5 number := 0;
259 var_temp5 number := 0;
260 var_temp_qty6 number := 0;
261 var_temp6 number := 0;
262 var_temp_qty7 number := 0;
263 var_temp7 number := 0;
264 var_temp_qty8 number := 0;
265 var_temp8 number := 0;
266 var_temp_qty9 number := 0;
267 var_temp9 number := 0;
268 var_temp_qty10 number := 0;
269 var_temp10 number := 0;
270 var_temp_qty11 number := 0;
271 var_temp11 number := 0;
272 var_temp_qty12 number := 0;
273 var_temp12 number := 0;
274 var_temp_qty13 number := 0;
275 var_temp13 number := 0;
276 var_temp_qty14 number := 0;
277 var_temp14 number := 0;
278 var_temp_qty15 number := 0;
279 var_temp15 number := 0;
280 var_temp_qty16 number := 0;
281 var_temp16 number := 0;
282 var_temp_qty17 number := 0;
283 var_temp17 number := 0;
284 var_temp_qty18 number := 0;
285 var_temp18 number := 0;
286 var_temp_qty19 number := 0;
287 var_temp19 number := 0;
288 var_temp_qty20 number := 0;
289 var_temp20 number := 0;
290 var_temp_qty21 number := 0;
291 var_temp21 number := 0;
292 var_temp_qty22 number := 0;
293 var_temp22 number := 0;
294 var_temp_qty23 number := 0;
295 var_temp23 number := 0;
296 var_temp_qty24 number := 0;
297 var_temp24 number := 0;
298 var_temp_qty25 number := 0;
299 var_temp25 number := 0;
300 var_temp_qty26 number := 0;
301 var_temp26 number := 0;
302 var_temp_qty27 number := 0;
303 var_temp27 number := 0;
304 var_temp_qty28 number := 0;
305 var_temp28 number := 0;
306 var_temp_qty29 number := 0;
307 var_temp29 number := 0;
308 var_temp_qty30 number := 0;
309 var_temp30 number := 0;
310 var_temp_qty31 number := 0;
311 var_temp31 number := 0;
312 var_temp_qty32 number := 0;
313 var_temp32 number := 0;
314 var_temp_qty33 number := 0;
315 var_temp33 number := 0;
316 var_temp_qty34 number := 0;
317 var_temp34 number := 0;
318 var_temp_qty35 number := 0;
319 var_temp35 number := 0;
320 var_temp_qty36 number := 0;
321 var_temp36 number := 0;
322
323
324
325 var_flag1 BOOLEAN := TRUE;
326 var_flag2 BOOLEAN := TRUE;
327 var_flag3 BOOLEAN := TRUE;
328 var_flag4 BOOLEAN := TRUE;
329 var_flag5 BOOLEAN := TRUE;
330 var_flag6 BOOLEAN := TRUE;
331 var_flag7 BOOLEAN := TRUE;
332 var_flag8 BOOLEAN := TRUE;
333 var_flag9 BOOLEAN := TRUE;
334 var_flag10 BOOLEAN := TRUE;
335 var_flag11 BOOLEAN := TRUE;
336 var_flag12 BOOLEAN := TRUE;
337 var_flag13 BOOLEAN := TRUE;
338 var_flag14 BOOLEAN := TRUE;
339 var_flag15 BOOLEAN := TRUE;
340 var_flag16 BOOLEAN := TRUE;
341 var_flag17 BOOLEAN := TRUE;
342 var_flag18 BOOLEAN := TRUE;
343 var_flag19 BOOLEAN := TRUE;
344 var_flag20 BOOLEAN := TRUE;
345 var_flag21 BOOLEAN := TRUE;
346 var_flag22 BOOLEAN := TRUE;
347 var_flag23 BOOLEAN := TRUE;
348 var_flag24 BOOLEAN := TRUE;
349 var_flag25 BOOLEAN := TRUE;
350 var_flag26 BOOLEAN := TRUE;
351 var_flag27 BOOLEAN := TRUE;
352 var_flag28 BOOLEAN := TRUE;
353 var_flag29 BOOLEAN := TRUE;
354 var_flag30 BOOLEAN := TRUE;
355 var_flag31 BOOLEAN := TRUE;
356 var_flag32 BOOLEAN := TRUE;
357 var_flag33 BOOLEAN := TRUE;
358 var_flag34 BOOLEAN := TRUE;
359 var_flag35 BOOLEAN := TRUE;
360 var_flag36 BOOLEAN := TRUE;
361
362
363 var_temp_order_type1 number := 0;
364 var_temp_order_type2 number := 0;
365 var_temp_order_type3 number := 0;
366 var_temp_order_type4 number := 0;
367 var_temp_order_type5 number := 0;
368 var_temp_order_type6 number := 0;
369 var_temp_order_type7 number := 0;
370 var_temp_order_type8 number := 0;
371 var_temp_order_type9 number := 0;
372 var_temp_order_type10 number := 0;
373 var_temp_order_type11 number := 0;
374 var_temp_order_type12 number := 0;
375 var_temp_order_type13 number := 0;
376 var_temp_order_type14 number := 0;
377 var_temp_order_type15 number := 0;
378 var_temp_order_type16 number := 0;
379 var_temp_order_type17 number := 0;
380 var_temp_order_type18 number := 0;
381 var_temp_order_type19 number := 0;
382 var_temp_order_type20 number := 0;
383 var_temp_order_type21 number := 0;
384 var_temp_order_type22 number := 0;
385 var_temp_order_type23 number := 0;
386 var_temp_order_type24 number := 0;
387 var_temp_order_type25 number := 0;
388 var_temp_order_type26 number := 0;
389 var_temp_order_type27 number := 0;
390 var_temp_order_type28 number := 0;
391 var_temp_order_type29 number := 0;
392 var_temp_order_type30 number := 0;
393 var_temp_order_type31 number := 0;
394 var_temp_order_type32 number := 0;
395 var_temp_order_type33 number := 0;
396 var_temp_order_type34 number := 0;
397 var_temp_order_type35 number := 0;
398 var_temp_order_type36 number := 0;
399
400
401
402
403 var_qty1 num;
404 var_qty2 num;
405 var_qty3 num;
406 var_qty4 num;
407 var_qty5 num;
408 var_qty6 num;
409 var_qty7 num;
410 var_qty8 num;
411 var_qty9 num;
412 var_qty10 num;
413 var_qty11 num;
414 var_qty12 num;
415 var_qty13 num;
416 var_qty14 num;
417 var_qty15 num;
418 var_qty16 num;
419 var_qty17 num;
420 var_qty18 num;
421 var_qty19 num;
422 var_qty20 num;
423 var_qty21 num;
424 var_qty22 num;
425 var_qty23 num;
426 var_qty24 num;
427 var_qty25 num;
428 var_qty26 num;
429 var_qty27 num;
430 var_qty28 num;
431 var_qty29 num;
432 var_qty30 num;
433 var_qty31 num;
434 var_qty32 num;
435 var_qty33 num;
436 var_qty34 num;
437 var_qty35 num;
438 var_qty36 num;
439 var_day_bkt num;
440 var_week_bkt num;
441 var_month_bkt num;
442
443 var_bkt_type num;
444 var_pub_id num;
445 var_pub_site_id num;
446
447 var_relation big_string;
448 var_order_relation big_string;
449 var_supplier_id big_string;
450 var_customer_id big_string;
451 var_supplier_site_id big_string;
452 var_customer_site_id big_string;
453 var_from_co_name string;
454 var_item_name string;
455 var_item_name_desc string;
456 var_supplier string;
457 var_customer string;
458
459 var_from_org_name small_string;
460 var_supplier_org small_string;
461 var_customer_org small_string;
462 var_order_desc small_string;
463 var_ship_ctrl small_string;
464 var_uom small_string;
465
466 -- variables reqd for updating the owner item etc.
467 v_line_id num;
468 v_cust_name string;
469 v_sup_name string;
470 v_item_id num ;
471 v_order num;
472 v_pub_name string;
473
474 v_owner_item varchar2(250);
475 v_sup_item varchar2(250);
476 v_cust_item varchar2(250);
477 v_tp_uom varchar2(3);
478 v_uom_code varchar2(3);
479 v_owner_item_desc varchar2(240);
480 v_sup_item_desc varchar2(240);
481 v_cust_item_desc varchar2(240);
482
483 var_line_id num;
484 var_owner_item string;
485 var_cust_item string;
486 var_sup_item string;
487 var_owner_item_desc string;
488 var_cust_item_desc string;
489 var_sup_item_desc string;
490 var_tp_uom small_string;
491 var_uom_code small_string;
492
493 temp_sup_site num;
494 temp_cust_site num;
495 temp_sup num;
496 temp_cust num;
497
498 var_edit_flag num;
499
500 TYPE mrp_activity IS RECORD
501 (relation VARCHAR2(2000),
502 order_relation VARCHAR2(2000),
503 from_co_name VARCHAR2(255),
504 from_org_name VARCHAR2(40),
505 item_id NUMBER,
506 item_name VARCHAR2(250),
507 item_desc VARCHAR2(240),
508 supplier_item_name VARCHAR2(250),
509 order_rank NUMBER,
510 order_type NUMBER,
511 order_desc VARCHAR2(80),
512 shipping_control VARCHAR2(35),
513 new_date DATE,
514 uom VARCHAR2(3),
515 new_quantity NUMBER,
516 qty_nobucket NUMBER,
517 supplier_id NUMBER,
518 customer_id NUMBER,
519 supp_site_id NUMBER,
520 cust_site_id NUMBER,
521 supplier_name VARCHAR2(250),
522 customer_name VARCHAR2(250),
523 supplier_org VARCHAR2(40),
524 customer_org VARCHAR2(40),
525 third_party_flag NUMBER,
526 viewer_co VARCHAR2(255),
527 tp_co VARCHAR2(255),
528 bucket_type NUMBER(1),
529 publisher_id NUMBER,
530 publisher_site_id NUMBER
531 );
532
533 activity_rec mrp_activity;
534 previous_rec mrp_activity;
535
536 curr_rel VARCHAR2(2000);
537 curr_item VARCHAR2(750);
538
539 last_rel VARCHAR2(2000);
540 last_item VARCHAR2(750);
541
542 curr_ot NUMBER;
543 last_ot NUMBER;
544
545 curr_ship_ctrl VARCHAR2(30);
546 last_ship_ctrl VARCHAR2(30);
547
548
549 g_num_of_buckets INTEGER := 0;
550
551 i INTEGER := 0;
552
553
554
555
556 CURSOR c_total(ARG_ORDER_TYPE IN NUMBER)
557 IS
558 SELECT RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
559 SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
560 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
561 ROUND( sum(UNBUCKETED_QTY), 6) q_0,ROUND( sum(QTY_BUCKET1), 6) q_1,
562 ROUND( sum(QTY_BUCKET2), 6) q_2,ROUND( sum(QTY_BUCKET3), 6) q_3, ROUND( sum(QTY_BUCKET4), 6) q_4,
563 ROUND( sum(QTY_BUCKET5), 6) q_5,ROUND( sum(QTY_BUCKET6), 6) q_6,ROUND( sum(QTY_BUCKET7), 6) q_7,
564 ROUND( sum(QTY_BUCKET8), 6) q_8,ROUND( sum(QTY_BUCKET9), 6) q_9,ROUND( sum(QTY_BUCKET10), 6) q_10,
565 ROUND( sum(QTY_BUCKET11), 6) q_11,ROUND( sum(QTY_BUCKET12), 6) q_12,ROUND( sum(QTY_BUCKET13), 6) q_13,
566 ROUND( sum(QTY_BUCKET14), 6) q_14,ROUND( sum(QTY_BUCKET15), 6) q_15,ROUND( sum(QTY_BUCKET16), 6) q_16,
567 ROUND( sum(QTY_BUCKET17), 6) q_17,ROUND( sum(QTY_BUCKET18), 6) q_18,ROUND( sum(QTY_BUCKET19), 6) q_19,
568 ROUND( sum(QTY_BUCKET20), 6) q_20,ROUND( sum(QTY_BUCKET21), 6) q_21,ROUND( sum(QTY_BUCKET22), 6) q_22,
569 ROUND( sum(QTY_BUCKET23), 6) q_23,ROUND( sum(QTY_BUCKET24), 6) q_24,ROUND( sum(QTY_BUCKET25), 6) q_25,
570 ROUND( sum(QTY_BUCKET26), 6) q_26,ROUND( sum(QTY_BUCKET27), 6) q_27,ROUND( sum(QTY_BUCKET28), 6) q_28,
571 ROUND( sum(QTY_BUCKET29), 6) q_29,ROUND( sum(QTY_BUCKET30), 6) q_30,ROUND( sum(QTY_BUCKET31), 6) q_31,
572 ROUND( sum(QTY_BUCKET32), 6) q_32,ROUND( sum(QTY_BUCKET33), 6) q_33,ROUND( sum(QTY_BUCKET34), 6) q_34,
573 ROUND( sum(QTY_BUCKET35), 6) q_35,ROUND( sum(QTY_BUCKET36), 6) q_36
574 FROM msc_hz_ui_lines
575 WHERE ORDER_TYPE = nvl(arg_order_type,NOT_SELECTED)
576 AND query_id = arg_query_id
577 GROUP BY RELATION_GROUP,ORDER_RELATION_GROUP, FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
578 SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
579 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID;
580
581 CURSOR c_runTotal(arg_order_type IN NUMBER)
582 IS
583 SELECT RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
584 SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
585 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
586 ROUND( sum(UNBUCKETED_QTY), 6) q_0,ROUND( sum(QTY_BUCKET1), 6) q_1,ROUND( sum(QTY_BUCKET1+QTY_BUCKET2), 6) q_2,
587 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3), 6) q_3,
588 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4), 6) q_4,
589 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5), 6) q_5,
590 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6), 6) q_6,
591 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
592 +QTY_BUCKET7), 6) q_7,
593 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
597 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
594 +QTY_BUCKET7+QTY_BUCKET8), 6) q_8,
595 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
596 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9), 6) q_9,
598 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10), 6) q_10,
599 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
600 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11), 6) q_11,
601 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
602 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11
603 +QTY_BUCKET12), 6) q_12,
604 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5
605 +QTY_BUCKET6+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10
606 +QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13), 6) q_13,
607 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
608 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
609 +QTY_BUCKET13+QTY_BUCKET14), 6) q_14,
610 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
611 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
612 +QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15), 6) q_15,
613 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
614 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
615 +QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16), 6) q_16,
616 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
617 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
618 +QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17), 6) q_17,
619 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
620 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
621 +QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18), 6) q_18,
622 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
623 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
624 +QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18
625 +QTY_BUCKET19), 6) q_19,
626 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
627 +QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
628 +QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18
629 +QTY_BUCKET19+QTY_BUCKET20), 6) q_20,
630 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
631 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
632 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
633 +QTY_BUCKET20+QTY_BUCKET21), 6) q_21,
634 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
635 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
636 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
637 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22), 6) q_22,
638 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
639 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
640 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
641 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23), 6) q_23,
642 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
643 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
644 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
645 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24), 6) q_24,
646 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
647 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
648 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
649 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25), 6) q_25,
650 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
651 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
652 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
653 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
654 +QTY_BUCKET26), 6) q_26,
655 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
656 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
657 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
658 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
659 +QTY_BUCKET26+QTY_BUCKET27), 6) q_27,
663 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
660 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
661 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
662 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
664 +QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28), 6) q_28,
665 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
666 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
667 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
668 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
669 +QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29), 6) q_29,
670 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
671 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
672 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
673 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
674 +QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30), 6) q_30,
675 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
676 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
677 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
678 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
679 +QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31), 6) q_31,
680 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
681 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
682 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
683 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
684 +QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
685 +QTY_BUCKET32), 6) q_32,
686 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
687 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
688 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
689 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
690 +QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
691 +QTY_BUCKET32+QTY_BUCKET33), 6) q_33,
692 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
693 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
694 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
695 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
696 +QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
697 +QTY_BUCKET32+QTY_BUCKET33+QTY_BUCKET34), 6) q_34,
698 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
699 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
700 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
701 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
702 +QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
703 +QTY_BUCKET32+QTY_BUCKET33+QTY_BUCKET34+QTY_BUCKET35), 6) q_35,
704 ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
705 +QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
706 +QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
707 +QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
708 +QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
709 +QTY_BUCKET32+QTY_BUCKET33+QTY_BUCKET34+QTY_BUCKET35+QTY_BUCKET36), 6) q_36
710 FROM msc_hz_ui_lines
711 WHERE ORDER_TYPE = nvl(arg_order_type,NOT_SELECTED)
712 AND query_id = arg_query_id
713 GROUP BY RELATION_GROUP, ORDER_RELATION_GROUP, FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
714 SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
715 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID ;
716
717
718 /**
719 * The foll procedure gets the preference set if
720 * chosen by the user.
721 * If the user has not chosen a specific preference st
722 * then the default preference set is picked
723 * If no preference set is designated as default then
724 * default values are assigned.
725 */
726 PROCEDURE set_default_prefs IS
727 BEGIN
728 -- set the default buckets
729 daily_bucket_count := 0;
730 weekly_bucket_count := 15;
731 period_bucket_count := 0;
732
733 v_graphtype := 0;
737 v_purchase_order := PURCHASE_ORDER;
734 v_category := '';
735
736 -- set default order types in the foll order PO, SO, Order Forecast, Supply Commit
738 v_sales_order := SALES_ORDER;
739 v_order_forecast := ORDER_FORECAST_CST;
740 v_supply_commit := SUPPLY_COMMIT;
741
742 v_o_po := 1;
743 v_o_sales_orders := 2;
744 v_o_forecast := 3;
745 v_o_supply_commit := 4;
746
747 -- set the aggregation
748 myco_agg := COMPANY_AGG;
749 tpco_agg := COMPANY_AGG;
750
751 arg_default_pref := 1;
752
753 END set_default_prefs;
754
755 /**
756 * Bug 4200004
757 * The following function returns
758 * the correct bucket qty based on teh order type.
759 * @return number the qty.
760 */
761
762
763 procedure calculate_bucket_qty_ss_pab(arg_flag in out NOCOPY boolean,
764 arg_prev_publisher_site_id in out NOCOPY NUMBER,
765 arg_qty in out NOCOPY number,
766 arg_prev_temp in out NOCOPY number,
767 arg_prev_temp_qty in out NOCOPY number,
768 arg_prev_temp_order_type in out NOCOPY number)
769 is
770 var_return_qty number := 0 ;
771
772 begin
773
774 --check for SS/PAB order type
775 if(activity_rec.order_type = SAFETY_STOCK OR activity_rec.order_type = PROJ_AVAIL_BAL) then
776 --check for pref set aggregation level
777 if(myco_agg = ORG_AGG) then
778 --if agg is by ind org
779 --always show the last record
780 arg_qty := activity_rec.new_quantity ;
781
782 elsif(myco_agg = COMPANY_AGG) then
783 --if agg is across the entire company
784
785 --foll logic is added so that the last bucket records across the sites be added
786
787 --check for the boolean flag
788 --this is just to see if this is the first record for the bucket
789 if(arg_flag ) then
790 arg_prev_publisher_site_id := activity_rec.publisher_site_id;
791 arg_prev_temp_qty := activity_rec.new_quantity ;
792 arg_flag := false;
793 end if;
794
795 --additional logic -when the order type is changes, then initialize the variables.
796 if(activity_rec.order_type <> arg_prev_temp_order_type) then
797 arg_qty := 0;
798 arg_prev_temp := 0;
799 arg_prev_temp_qty := 0;
800
801 end if;
802
803 --if the org changes then store that quantity and add it to the previous bucket's last qty
804 if(activity_rec.publisher_site_id <> arg_prev_publisher_site_id) then
805 arg_prev_temp := arg_prev_temp + arg_prev_temp_qty;
806 end if;
807
808 --this is the final qty
809 --foll logic is there so as to take care of the cases where the qty record doesn't exist for a particular SS
810 if(arg_prev_temp = -99999999 and activity_rec.new_quantity = -99999999) then
811 arg_qty := -99999999;
812 elsif(arg_prev_temp = -99999999 and activity_rec.new_quantity <> -99999999) then
813 arg_qty := activity_rec.new_quantity ;
814 elsif(arg_prev_temp <> -99999999 and arg_prev_temp <> 0 and activity_rec.new_quantity = -99999999) then
815 arg_qty := arg_prev_temp ;
816 else
817 arg_qty := arg_prev_temp + activity_rec.new_quantity;
818 end if;
819
820 --temp variable to store the current qty
821 arg_prev_temp_qty := activity_rec.new_quantity ;
822
823 end if;
824
825 --temp variable to store the current site
826 arg_prev_publisher_site_id := activity_rec.publisher_site_id;
827
828 arg_prev_temp_order_type := activity_rec.order_type;
829
830
831 else
832 --if the order type is neither SS nor PAB
833 arg_qty := arg_qty + activity_rec.new_quantity;
834 end if;
835
836
837 end calculate_bucket_qty_ss_pab;
838 /**
839 * get the user_preferences data for the user.
840 * If a user preference is not defined
841 * then use the system default
842 * identified by sce_user_id = -1 and user_id = -1
843 * and named_set = "MSCX_SYSTEM_PREFERENCE"
844 * @param the preference set name
845 History BUG BY CHANGES
846 07-July-2008 6893383 HBINJOLA Added two new order type (Returns Forecast and Defective Outbound Shipment)
847 */
848 PROCEDURE get_user_prefs( v_pref_name IN VARCHAR2 -- DEFAULT NULL
849 ) IS
850
851 CURSOR c_public_pref_set IS
852 SELECT named_set, show_graph, category_name,
853 decode(summary_seller_forecast,'Y',SALES_FORECAST,NOT_SELECTED ),
854 decode(summary_forecast,'Y',ORDER_FORECAST_CST,NOT_SELECTED ),
855 decode(summary_allocated_supply,'Y',SUPPLY_COMMIT,NOT_SELECTED ),
856 decode(summary_returns_forecast,'Y',G_RETURNS_FORECAST,NOT_SELECTED ), --bug#6893383
857 decode(summary_def_outbound_shipment,'Y',G_DEFECTIVE_OUTBOUND_SHIPMENT,NOT_SELECTED ),--bug#6893383
858 decode(summary_hist_sales,'Y',HISTORICAL_SALES,NOT_SELECTED ),
859 decode(summary_sell_thro_fcst,'Y',SELL_THRU_FORECAST,NOT_SELECTED ),
860 decode(summary_suppcap,'Y',NEGOTIATED_CAPACITY,NOT_SELECTED ),
861 decode(summary_ss,'Y',SAFETY_STOCK,NOT_SELECTED ),
865 decode(summary_unallocated_onhand,'Y',UNALLOCATED_ONHAND,NOT_SELECTED ),
862 decode(summary_pab,'Y',PROJ_AVAIL_BAL,NOT_SELECTED ),
863 decode(summary_projected_ss,'Y',PROJ_SAFETY_STOCK,NOT_SELECTED ),
864 decode(summary_allocated_onhand,'Y',ALLOCATED_ONHAND,NOT_SELECTED ),
866 decode(summary_unalct_prjt_avl_bal,'Y',PROJ_UNALOC_AVL_BAL,NOT_SELECTED ),
867 decode(summary_alcat_prjt_avl_bal,'Y',PROJ_ALLOC_AVL_BAL,NOT_SELECTED ),
868 decode(summary_po,'Y',PURCHASE_ORDER,NOT_SELECTED ),
869 decode(summary_sales_orders,'Y',SALES_ORDER,NOT_SELECTED ),
870 decode(summary_asn,'Y',ASN,NOT_SELECTED ),
871 decode(summary_receiving,'Y',SHIPMENT_RECEIPT,NOT_SELECTED ),
872 decode(summary_transit,'Y',INTRANSIT,NOT_SELECTED ),
873 decode(summary_wip,'Y',WORK_ORDER,NOT_SELECTED ),
874 decode(summary_po_ack,'Y',PO_ACK,NOT_SELECTED ),
875 decode(summary_replenishment,'Y',REPLENISHMENT,NOT_SELECTED ),
876 decode(summary_req,'Y',REQUISITION,NOT_SELECTED ),
877 decode(summary_purchase_plan, 'Y' , PO_FROM_PLAN, NOT_SELECTED),
878 decode(summary_release_plan, 'Y', RELEASED_PLAN, NOT_SELECTED),
879 decode(summary_plan, 'Y', PLANNED_ORDER, NOT_SELECTED),
880 decode(running_total_supply,'Y',RUN_TOT_SUPPLY,NOT_SELECTED ),
881 decode(running_total_demand,'Y',RUN_TOT_DEMAND,NOT_SELECTED ),
882 ORDER_SELLER_FORECAST,ORDER_FORECAST,ORDER_ALLOCATED_SUPPLY,
883 RETURNS_FORECAST, DEF_OUTBOUND_SHIPMENT, --bug#6893383
884 ORDER_HIST_SALES,ORDER_SELL_THRO_FCST,ORDER_SUPPCAP,ORDER_SS, ORDER_PAB,
885 ORDER_PROJECTED_SS,ORDER_ALLOCATED_ONHAND,ORDER_UNALLOCATED_ONHAND,
886 ORDER_UNALCT_PRJT_AVL_BAL,ORDER_ALCAT_PRJT_AVL_BAL,ORDER_PO, ORDER_WIP, ORDER_PO_ACK,
887 ORDER_SALES_ORDERS,ORDER_ASN,ORDER_RECEIVING,ORDER_TRANSIT,
888 ORDER_WIP,ORDER_REPLENISHMENT,ORDER_REQ,ORDER_PURCHASE_PLAN,ORDER_RELEASE_PLAN, ORDER_PLAN,
889 ORDER_RUNNING_TOTAL_SUPPLY,ORDER_RUNNING_TOTAL_DEMAND,
890 NVL(prod_sum_level,ITEM_AGG),NVL(org_sum_level,COMPANY_AGG),
891 NVL(org_sum_level_tp,COMPANY_AGG),NVL(summary_display_days,NOT_SELECTED),
892 NVL(summary_display_weeks,NOT_SELECTED),
893 NVL(summary_display_periods,NOT_SELECTED)
894 , NVL(shift_days, 0)
895 , decode(net_forecast, 'Y', SELECTED, NOT_SELECTED)
896 , decode(total_supply, 'Y', SELECTED, NOT_SELECTED)
897 FROM msc_workbench_display_options
898 WHERE public_flag = 'Y'
899 ORDER BY named_set ASC
900 ;
901
902 CURSOR c_private_pref_set IS
903 SELECT named_set, show_graph, category_name,
904 decode(summary_seller_forecast,'Y',SALES_FORECAST,NOT_SELECTED ),
905 decode(summary_forecast,'Y',ORDER_FORECAST_CST,NOT_SELECTED ),
906 decode(summary_allocated_supply,'Y',SUPPLY_COMMIT,NOT_SELECTED ),
907 decode(summary_returns_forecast,'Y',G_RETURNS_FORECAST,NOT_SELECTED ), --bug#6893383
908 decode(summary_def_outbound_shipment,'Y',G_DEFECTIVE_OUTBOUND_SHIPMENT,NOT_SELECTED ),--bug#6893383
909 decode(summary_hist_sales,'Y',HISTORICAL_SALES,NOT_SELECTED ),
910 decode(summary_sell_thro_fcst,'Y',SELL_THRU_FORECAST,NOT_SELECTED ),
911 decode(summary_suppcap,'Y',NEGOTIATED_CAPACITY,NOT_SELECTED ),
912 decode(summary_ss,'Y',SAFETY_STOCK,NOT_SELECTED ),
913 decode(summary_pab,'Y',PROJ_AVAIL_BAL,NOT_SELECTED ),
914 decode(summary_projected_ss,'Y',PROJ_SAFETY_STOCK,NOT_SELECTED ),
915 decode(summary_allocated_onhand,'Y',ALLOCATED_ONHAND,NOT_SELECTED ),
916 decode(summary_unallocated_onhand,'Y',UNALLOCATED_ONHAND,NOT_SELECTED ),
917 decode(summary_unalct_prjt_avl_bal,'Y',PROJ_UNALOC_AVL_BAL,NOT_SELECTED ),
918 decode(summary_alcat_prjt_avl_bal,'Y',PROJ_ALLOC_AVL_BAL,NOT_SELECTED ),
919 decode(summary_po,'Y',PURCHASE_ORDER,NOT_SELECTED ),
920 decode(summary_sales_orders,'Y',SALES_ORDER,NOT_SELECTED ),
921 decode(summary_asn,'Y',ASN,NOT_SELECTED ),
922 decode(summary_receiving,'Y',SHIPMENT_RECEIPT,NOT_SELECTED ),
923 decode(summary_transit,'Y',INTRANSIT,NOT_SELECTED ),
924 decode(summary_wip,'Y',WORK_ORDER,NOT_SELECTED ),
925 decode(summary_po_ack,'Y',PO_ACK,NOT_SELECTED ),
926 decode(summary_replenishment,'Y',REPLENISHMENT,NOT_SELECTED ),
927 decode(summary_req,'Y',REQUISITION,NOT_SELECTED ),
928 decode(summary_purchase_plan, 'Y' , PO_FROM_PLAN, NOT_SELECTED),
929 decode(summary_release_plan, 'Y', RELEASED_PLAN, NOT_SELECTED),
930 decode(summary_plan, 'Y', PLANNED_ORDER, NOT_SELECTED),
931 decode(running_total_supply,'Y',RUN_TOT_SUPPLY,NOT_SELECTED ),
932 decode(running_total_demand,'Y',RUN_TOT_DEMAND,NOT_SELECTED ),
933 ORDER_SELLER_FORECAST,ORDER_FORECAST,ORDER_ALLOCATED_SUPPLY,
934 RETURNS_FORECAST, DEF_OUTBOUND_SHIPMENT, --bug#6893383
935 ORDER_HIST_SALES,ORDER_SELL_THRO_FCST,ORDER_SUPPCAP,ORDER_SS, ORDER_PAB,
939 ORDER_WIP,ORDER_REPLENISHMENT,ORDER_REQ,ORDER_PURCHASE_PLAN,ORDER_RELEASE_PLAN, ORDER_PLAN,
936 ORDER_PROJECTED_SS,ORDER_ALLOCATED_ONHAND,ORDER_UNALLOCATED_ONHAND,
937 ORDER_UNALCT_PRJT_AVL_BAL,ORDER_ALCAT_PRJT_AVL_BAL,ORDER_PO, ORDER_WIP, ORDER_PO_ACK,
938 ORDER_SALES_ORDERS,ORDER_ASN,ORDER_RECEIVING,ORDER_TRANSIT,
940 ORDER_RUNNING_TOTAL_SUPPLY,ORDER_RUNNING_TOTAL_DEMAND,
941 NVL(prod_sum_level,ITEM_AGG),NVL(org_sum_level,COMPANY_AGG),
942 NVL(org_sum_level_tp,COMPANY_AGG),NVL(summary_display_days,NOT_SELECTED),
943 NVL(summary_display_weeks,NOT_SELECTED),
944 NVL(summary_display_periods,NOT_SELECTED)
945 , NVL(shift_days, 0)
946 , decode(net_forecast, 'Y', SELECTED, NOT_SELECTED)
947 , decode(total_supply, 'Y', SELECTED, NOT_SELECTED)
948 FROM msc_workbench_display_options
949 WHERE NVL(public_flag, 'N') <> 'Y'
950 AND NVL(default_set, 'N') <> 'Y'
951 ORDER BY named_set ASC
952 ;
953
954 BEGIN
955 arg_default_pref := 0;
956
957 BEGIN
958 if v_pref_name is not null then
959 SELECT named_set, show_graph, category_name,
960 decode(summary_seller_forecast,'Y',SALES_FORECAST,NOT_SELECTED ),
961 decode(summary_forecast,'Y',ORDER_FORECAST_CST,NOT_SELECTED ),
962 decode(summary_allocated_supply,'Y',SUPPLY_COMMIT,NOT_SELECTED ),
963 decode(summary_returns_forecast,'Y',G_RETURNS_FORECAST,NOT_SELECTED ), --bug#6893383
964 decode(summary_def_outbound_shipment,'Y',G_DEFECTIVE_OUTBOUND_SHIPMENT,NOT_SELECTED ),--bug#6893383
965 decode(summary_hist_sales,'Y',HISTORICAL_SALES,NOT_SELECTED ),
966 decode(summary_sell_thro_fcst,'Y',SELL_THRU_FORECAST,NOT_SELECTED ),
967 decode(summary_suppcap,'Y',NEGOTIATED_CAPACITY,NOT_SELECTED ),
968 decode(summary_ss,'Y',SAFETY_STOCK,NOT_SELECTED ),
969 decode(summary_pab,'Y',PROJ_AVAIL_BAL,NOT_SELECTED ),
970 decode(summary_projected_ss,'Y',PROJ_SAFETY_STOCK,NOT_SELECTED ),
971 decode(summary_allocated_onhand,'Y',ALLOCATED_ONHAND,NOT_SELECTED ),
972 decode(summary_unallocated_onhand,'Y',UNALLOCATED_ONHAND,NOT_SELECTED ),
973 decode(summary_unalct_prjt_avl_bal,'Y',PROJ_UNALOC_AVL_BAL,NOT_SELECTED ),
974 decode(summary_alcat_prjt_avl_bal,'Y',PROJ_ALLOC_AVL_BAL,NOT_SELECTED ),
975 decode(summary_po,'Y',PURCHASE_ORDER,NOT_SELECTED ),
976 decode(summary_sales_orders,'Y',SALES_ORDER,NOT_SELECTED ),
977 decode(summary_asn,'Y',ASN,NOT_SELECTED ),
978 decode(summary_receiving,'Y',SHIPMENT_RECEIPT,NOT_SELECTED ),
979 decode(summary_transit,'Y',INTRANSIT,NOT_SELECTED ),
980 decode(summary_wip,'Y',WORK_ORDER,NOT_SELECTED ),
981 decode(summary_po_ack,'Y',PO_ACK,NOT_SELECTED ),
982 decode(summary_replenishment,'Y',REPLENISHMENT,NOT_SELECTED ),
983 decode(summary_req,'Y',REQUISITION,NOT_SELECTED ),
984 decode(summary_purchase_plan, 'Y' , PO_FROM_PLAN, NOT_SELECTED),
985 decode(summary_release_plan, 'Y', RELEASED_PLAN, NOT_SELECTED),
986 decode(summary_plan, 'Y', PLANNED_ORDER, NOT_SELECTED),
987 decode(running_total_supply,'Y',RUN_TOT_SUPPLY,NOT_SELECTED ),
988 decode(running_total_demand,'Y',RUN_TOT_DEMAND,NOT_SELECTED ),
989 ORDER_SELLER_FORECAST,ORDER_FORECAST,ORDER_ALLOCATED_SUPPLY,
990 RETURNS_FORECAST, DEF_OUTBOUND_SHIPMENT, --bug#6893383
991 ORDER_HIST_SALES,ORDER_SELL_THRO_FCST,ORDER_SUPPCAP,ORDER_SS, ORDER_PAB,
992 ORDER_PROJECTED_SS,ORDER_ALLOCATED_ONHAND,ORDER_UNALLOCATED_ONHAND,
993 ORDER_UNALCT_PRJT_AVL_BAL,ORDER_ALCAT_PRJT_AVL_BAL,ORDER_PO, ORDER_WIP, ORDER_PO_ACK,
994 ORDER_SALES_ORDERS,ORDER_ASN,ORDER_RECEIVING,ORDER_TRANSIT,
995 ORDER_WIP,ORDER_REPLENISHMENT,ORDER_REQ,ORDER_PURCHASE_PLAN,ORDER_RELEASE_PLAN, ORDER_PLAN,
996 ORDER_RUNNING_TOTAL_SUPPLY,ORDER_RUNNING_TOTAL_DEMAND,
997 NVL(prod_sum_level,ITEM_AGG),NVL(org_sum_level,COMPANY_AGG),
998 NVL(org_sum_level_tp,COMPANY_AGG),NVL(summary_display_days,NOT_SELECTED),
999 NVL(summary_display_weeks,NOT_SELECTED),
1000 NVL(summary_display_periods,NOT_SELECTED)
1001 , NVL(shift_days, 0)
1002 , decode(net_forecast, 'Y', SELECTED, NOT_SELECTED)
1003 , decode(total_supply, 'Y', SELECTED, NOT_SELECTED)
1004 INTO v_pref, v_graphtype, v_category, v_sales_forecast,
1005 v_order_forecast, v_supply_commit,v_returns_forecast,v_def_outbound_shipment, -- bug#6893383
1006 v_hist_sales,v_sell_thru_fcst, v_negotiated_capacity, v_safety_stock,v_proj_avail_bal,
1007 v_proj_safety_stock,v_alloc_onhand, v_unalloc_onhand, v_proj_unalloc_avl_bal,
1008 v_proj_alloc_avl_bal,v_purchase_order, v_sales_order,v_asn,
1009 v_shipment_receipt, v_intransit,v_work_order, v_po_ack, v_replenishment,
1010 v_requisition, v_po_from_plan, v_released_plan, v_planned_order, v_run_tot_supply, v_run_tot_demand,
1011 v_o_seller_forecast,v_o_forecast,v_o_supply_commit,
1012 v_o_returns_forecast,v_o_def_outbound_shipment, -- bug#6893383
1016 v_o_sales_orders,v_o_asn,v_o_receiving,v_o_transit,
1013 v_o_hist_sales,v_o_sell_thro_fcst,v_o_negcap,v_o_ss,v_o_pab,
1014 v_o_projected_ss,v_o_alct_onhand,v_o_unalct_onhand,
1015 v_o_unalct_prjt_avl_bal,v_o_alcat_prjt_avl_bal,v_o_po, v_o_work_order, v_o_po_ack,
1017 v_o_wip,v_o_replenishment,v_o_req,v_o_po_from_plan, v_o_released_plan, v_o_planned_order,
1018 v_o_run_tot_supply,v_o_run_tot_demand,
1019 prod_agg,myco_agg,tpco_agg,daily_bucket_count,weekly_bucket_count,
1020 period_bucket_count
1021 , v_shift_days
1022 , v_net_forecast
1023 , v_total_supply
1024 FROM msc_workbench_display_options
1025 WHERE rtrim(ltrim(named_set)) = rtrim(ltrim(v_pref_name))
1026 AND rownum < 2
1027 AND ( SCE_USER_ID = FND_GLOBAL.user_id
1028 OR PUBLIC_FLAG = 'Y' )
1029 ;
1030
1031 else
1032 SELECT named_set, show_graph, category_name,
1033 decode(summary_seller_forecast,'Y',SALES_FORECAST,NOT_SELECTED ),
1034 decode(summary_forecast,'Y',ORDER_FORECAST_CST,NOT_SELECTED ),
1035 decode(summary_allocated_supply,'Y',SUPPLY_COMMIT,NOT_SELECTED ),
1036 decode(summary_returns_forecast,'Y',G_RETURNS_FORECAST,NOT_SELECTED ), --bug#6893383
1037 decode(summary_def_outbound_shipment,'Y',G_DEFECTIVE_OUTBOUND_SHIPMENT,NOT_SELECTED ),--bug#6893383
1038 decode(summary_hist_sales,'Y',HISTORICAL_SALES,NOT_SELECTED ),
1039 decode(summary_sell_thro_fcst,'Y',SELL_THRU_FORECAST,NOT_SELECTED ),
1040 decode(summary_suppcap,'Y',NEGOTIATED_CAPACITY,NOT_SELECTED ),
1041 decode(summary_ss,'Y',SAFETY_STOCK,NOT_SELECTED ),
1042 decode(summary_pab,'Y',PROJ_AVAIL_BAL,NOT_SELECTED ),
1043 decode(summary_projected_ss,'Y',PROJ_SAFETY_STOCK,NOT_SELECTED ),
1044 decode(summary_allocated_onhand,'Y',ALLOCATED_ONHAND,NOT_SELECTED ),
1045 decode(summary_unallocated_onhand,'Y',UNALLOCATED_ONHAND,NOT_SELECTED ),
1046 decode(summary_unalct_prjt_avl_bal,'Y',PROJ_UNALOC_AVL_BAL,NOT_SELECTED ),
1047 decode(summary_alcat_prjt_avl_bal,'Y',PROJ_ALLOC_AVL_BAL,NOT_SELECTED ),
1048 decode(summary_po,'Y',PURCHASE_ORDER,NOT_SELECTED ),
1049 decode(summary_sales_orders,'Y',SALES_ORDER,NOT_SELECTED ),
1050 decode(summary_asn,'Y',ASN,NOT_SELECTED ),
1051 decode(summary_receiving,'Y',SHIPMENT_RECEIPT,NOT_SELECTED ),
1052 decode(summary_transit,'Y',INTRANSIT,NOT_SELECTED ),
1053 decode(summary_wip,'Y',WORK_ORDER,NOT_SELECTED ),
1054 decode(summary_po_ack,'Y',PO_ACK,NOT_SELECTED ),
1055 decode(summary_replenishment,'Y',REPLENISHMENT,NOT_SELECTED ),
1056 decode(summary_req,'Y',REQUISITION,NOT_SELECTED ),
1057 decode(summary_purchase_plan, 'Y' , PO_FROM_PLAN, NOT_SELECTED),
1058 decode(summary_release_plan, 'Y', RELEASED_PLAN, NOT_SELECTED),
1059 decode(summary_plan, 'Y', PLANNED_ORDER, NOT_SELECTED),
1060 decode(running_total_supply,'Y',RUN_TOT_SUPPLY,NOT_SELECTED ),
1061 decode(running_total_demand,'Y',RUN_TOT_DEMAND,NOT_SELECTED ),
1062 ORDER_SELLER_FORECAST,ORDER_FORECAST,ORDER_ALLOCATED_SUPPLY,
1063 RETURNS_FORECAST, DEF_OUTBOUND_SHIPMENT, --bug#6893383
1064 ORDER_HIST_SALES,ORDER_SELL_THRO_FCST,ORDER_SUPPCAP,ORDER_SS, ORDER_PAB,
1065 ORDER_PROJECTED_SS,ORDER_ALLOCATED_ONHAND,ORDER_UNALLOCATED_ONHAND,
1066 ORDER_UNALCT_PRJT_AVL_BAL,ORDER_ALCAT_PRJT_AVL_BAL,ORDER_PO, ORDER_WIP, ORDER_PO_ACK,
1067 ORDER_SALES_ORDERS,ORDER_ASN,ORDER_RECEIVING,ORDER_TRANSIT,
1068 ORDER_WIP,ORDER_REPLENISHMENT,ORDER_REQ,ORDER_PURCHASE_PLAN,ORDER_RELEASE_PLAN, ORDER_PLAN,
1069 ORDER_RUNNING_TOTAL_SUPPLY,ORDER_RUNNING_TOTAL_DEMAND,
1070 NVL(prod_sum_level,ITEM_AGG),NVL(org_sum_level,COMPANY_AGG),
1071 NVL(org_sum_level_tp,COMPANY_AGG),NVL(summary_display_days,NOT_SELECTED),
1072 NVL(summary_display_weeks,NOT_SELECTED),
1073 NVL(summary_display_periods,NOT_SELECTED)
1074 , NVL(shift_days, 0)
1075 , decode(net_forecast, 'Y', SELECTED, NOT_SELECTED)
1076 , decode(total_supply, 'Y', SELECTED, NOT_SELECTED)
1077 INTO v_pref, v_graphtype, v_category,v_sales_forecast,
1078 v_order_forecast, v_supply_commit,v_returns_forecast,v_def_outbound_shipment, -- bug#6893383
1079 v_hist_sales,v_sell_thru_fcst, v_negotiated_capacity, v_safety_stock,v_proj_avail_bal,
1080 v_proj_safety_stock,v_alloc_onhand, v_unalloc_onhand, v_proj_unalloc_avl_bal,
1081 v_proj_alloc_avl_bal,v_purchase_order, v_sales_order,v_asn,
1082 v_shipment_receipt, v_intransit,v_work_order, v_po_ack, v_replenishment,
1083 v_requisition,v_po_from_plan, v_released_plan, v_planned_order, v_run_tot_supply, v_run_tot_demand,
1084 v_o_seller_forecast,v_o_forecast,v_o_supply_commit,
1085 v_o_returns_forecast,v_o_def_outbound_shipment, -- bug#6893383
1086 v_o_hist_sales,v_o_sell_thro_fcst,v_o_negcap,v_o_ss, v_o_pab,
1087 v_o_projected_ss,v_o_alct_onhand,v_o_unalct_onhand,
1088 v_o_unalct_prjt_avl_bal,v_o_alcat_prjt_avl_bal,v_o_po, v_o_work_order, v_o_po_ack,
1092 prod_agg,myco_agg,tpco_agg,daily_bucket_count,weekly_bucket_count,
1089 v_o_sales_orders,v_o_asn,v_o_receiving,v_o_transit,
1090 v_o_wip,v_o_replenishment,v_o_req,v_o_po_from_plan, v_o_released_plan, v_o_planned_order,
1091 v_o_run_tot_supply,v_o_run_tot_demand,
1093 period_bucket_count
1094 , v_shift_days
1095 , v_net_forecast
1096 , v_total_supply
1097 FROM msc_workbench_display_options
1098 WHERE upper(default_set) = 'Y'
1099 AND rownum < 2
1100 AND SCE_USER_ID = FND_GLOBAL.user_id ;
1101 end if;
1102 EXCEPTION
1103 WHEN no_data_found THEN
1104 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1105 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'get_user_prefs','No preference set found');
1106 end if;
1107 -- v_pref := null;
1108 -- set_default_prefs;
1109 END;
1110 IF v_pref is null OR SQL%NOTFOUND then
1111
1112 OPEN c_public_pref_set;
1113 FETCH c_public_pref_set
1114 INTO v_pref, v_graphtype, v_category,
1115 v_sales_forecast, v_order_forecast,v_supply_commit,v_returns_forecast,v_def_outbound_shipment, -- bug#6893383
1116 v_hist_sales,v_sell_thru_fcst, v_negotiated_capacity, v_safety_stock,v_proj_avail_bal,
1117 v_proj_safety_stock,v_alloc_onhand, v_unalloc_onhand, v_proj_unalloc_avl_bal,
1118 v_proj_alloc_avl_bal,v_purchase_order, v_sales_order,v_asn,
1119 v_shipment_receipt, v_intransit,v_work_order, v_po_ack, v_replenishment,
1120 v_requisition,v_po_from_plan, v_released_plan, v_planned_order, v_run_tot_supply, v_run_tot_demand,
1121 v_o_seller_forecast,v_o_forecast,v_o_supply_commit,
1122 v_o_returns_forecast,v_o_def_outbound_shipment, -- bug#6893383
1123 v_o_hist_sales,v_o_sell_thro_fcst,v_o_negcap,v_o_ss, v_o_pab,
1124 v_o_projected_ss,v_o_alct_onhand,v_o_unalct_onhand,
1125 v_o_unalct_prjt_avl_bal,v_o_alcat_prjt_avl_bal,v_o_po, v_o_work_order, v_o_po_ack,
1126 v_o_sales_orders,v_o_asn,v_o_receiving,v_o_transit,
1127 v_o_wip,v_o_replenishment,v_o_req,v_o_po_from_plan, v_o_released_plan, v_o_planned_order,
1128 v_o_run_tot_supply,v_o_run_tot_demand,
1129 prod_agg,myco_agg,tpco_agg,daily_bucket_count,weekly_bucket_count,
1130 period_bucket_count
1131 , v_shift_days
1132 , v_net_forecast
1133 , v_total_supply
1134 ;
1135
1136 IF (c_public_pref_set%NOTFOUND) THEN
1137 OPEN c_private_pref_set;
1138 FETCH c_private_pref_set
1139 INTO v_pref, v_graphtype, v_category,
1140 v_sales_forecast, v_order_forecast, v_supply_commit,v_returns_forecast,v_def_outbound_shipment, -- bug#6893383
1141 v_hist_sales,v_sell_thru_fcst, v_negotiated_capacity, v_safety_stock,v_proj_avail_bal,
1142 v_proj_safety_stock,v_alloc_onhand, v_unalloc_onhand, v_proj_unalloc_avl_bal,
1143 v_proj_alloc_avl_bal,v_purchase_order, v_sales_order,v_asn,
1144 v_shipment_receipt, v_intransit,v_work_order, v_po_ack, v_replenishment,
1145 v_requisition, v_po_from_plan, v_released_plan, v_planned_order, v_run_tot_supply, v_run_tot_demand,
1146 v_o_seller_forecast,v_o_forecast,v_o_supply_commit,
1147 v_o_returns_forecast,v_o_def_outbound_shipment, -- bug#6893383
1148 v_o_hist_sales,v_o_sell_thro_fcst,v_o_negcap,v_o_ss, v_o_pab,
1149 v_o_projected_ss,v_o_alct_onhand,v_o_unalct_onhand,
1150 v_o_unalct_prjt_avl_bal,v_o_alcat_prjt_avl_bal,v_o_po, v_o_work_order, v_o_po_ack,
1151 v_o_sales_orders,v_o_asn,v_o_receiving,v_o_transit,
1152 v_o_wip,v_o_replenishment,v_o_req, v_o_po_from_plan, v_o_released_plan, v_o_planned_order,
1153 v_o_run_tot_supply,v_o_run_tot_demand,
1154 prod_agg,myco_agg,tpco_agg,daily_bucket_count,weekly_bucket_count,
1155 period_bucket_count
1156 , v_shift_days
1157 , v_net_forecast
1158 , v_total_supply
1159 ;
1160
1161 IF (c_private_pref_set%NOTFOUND) THEN
1162 set_default_prefs ;
1163 END IF;
1164 CLOSE c_private_pref_set;
1165 END IF;
1166
1167 CLOSE c_public_pref_set;
1168
1169 END IF;
1170
1171
1172 EXCEPTION
1173 when no_data_found then
1174 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1175 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'get_user_prefs','No preference set found');
1176 end if;
1177 v_pref := null;
1178 set_default_prefs;
1179
1180 when others then
1181 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1182 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'get_user_prefs',' Other ' || SQLERRM);
1183 end if;
1187
1184 v_pref := null;
1185 set_default_prefs;
1186
1188 END get_user_prefs;
1189
1190
1191 /**
1192 * The following procedure sets the calendar code to be used
1193 * while calculating the bucket dates
1194 * @param the start date.
1195 */
1196 PROCEDURE get_bucket_dates(arg_start_date IN DATE,
1197 p_cal_code IN VARCHAR2
1198 ) IS
1199
1200 DAY_BUCKET_FOUND boolean;
1201 WEEK_BUCKET_FOUND boolean;
1202 MONTH_BUCKET_FOUND boolean;
1203 v_cal_code VARCHAR2(14) := p_cal_code;
1204 daily_bucket_dates calendar_date;
1205 weekly_bucket_dates calendar_date;
1206 monthly_bucket_dates calendar_date;
1207 v_temp_bucket_start_date date;
1208 v_month_bkt_start_date date;
1209 counter INTEGER := 0;
1210
1211 v_temp_date date;
1212 k INTEGER := 0;
1213
1214
1215 BEGIN
1216
1217 -- initialize the dates array.
1218 for k in 1..36 loop
1219 var_dates(k) := null;
1220 end loop;
1221
1222 /** if user entered a start date, use it
1223 * set the start date
1224 */
1225
1226 IF arg_start_date IS NULL THEN
1227 if daily_bucket_count <> 0 then -- from the user prefs
1228 p_start_date := trunc(sysdate) + v_shift_days;
1229 elsif weekly_bucket_count <> 0 then
1230 select week_start_date into p_start_date
1231 from msc_cal_week_start_dates
1232 where calendar_code = v_cal_code
1233 and exception_set_id = -1
1234 and week_start_date <= SYSDATE + v_shift_days
1235 and next_date > SYSDATE + v_shift_days;
1236 else -- period_bucket_count
1237 select period_start_date into p_start_date
1238 from msc_period_start_dates
1239 where calendar_code = v_cal_code
1240 and exception_set_id = -1
1241 and period_start_date <= SYSDATE + v_shift_days
1242 and next_date > SYSDATE + v_shift_days;
1243 end if;
1244
1245 ELSE
1246 p_start_date := trunc(arg_start_date) + v_shift_days;
1247 END IF;
1248
1249
1250 /**
1251 * get the daily buckets
1252 * the fist bucket is the start date or the sysdate
1253 * if the last daily bucket falls in the middle of a week
1254 * then get additional daily buckets until the start of
1255 * next weekly or monthly bucket.
1256 */
1257 v_temp_date := p_start_date;
1258 IF (daily_bucket_count <> 0 ) THEN
1259 IF (weekly_bucket_count <> 0 ) THEN
1260 SELECT
1261 day.calendar_date
1262 BULK COLLECT INTO
1263 daily_bucket_dates
1264 FROM msc_calendar_dates day, msc_cal_week_start_dates week
1265 WHERE day.calendar_code = v_cal_code
1266 and day.exception_set_id = -1
1267 and day.calendar_date >= p_start_date
1268 and day.calendar_date < week.next_date
1269 and week.calendar_code = v_cal_code
1270 and week.exception_set_id = -1
1271 and week.week_start_date <= p_start_date + daily_bucket_count - 1
1272 and week.next_date > p_start_date + daily_bucket_count - 1
1273 order by day.calendar_date asc;
1274
1275 daily_bucket_count := daily_bucket_dates.COUNT;
1276
1277 ELSIF (period_bucket_count <> 0) THEN
1278 SELECT
1279 day.calendar_date
1280 BULK COLLECT INTO
1281 daily_bucket_dates
1282 FROM msc_calendar_dates day, msc_period_start_dates month
1283 WHERE day.calendar_code = v_cal_code
1284 and day.exception_set_id = -1
1285 and day.calendar_date >= p_start_date
1286 and day.calendar_date < month.next_date
1287 and month.calendar_code = v_cal_code
1288 and month.exception_set_id = -1
1289 and month.period_start_date <= p_start_date + daily_bucket_count - 1
1290 and month.next_date > p_start_date + daily_bucket_count - 1
1291 order by day.calendar_date asc;
1292
1293 daily_bucket_count := daily_bucket_dates.COUNT;
1294
1295 ELSE
1296 SELECT
1297 calendar_date
1298 BULK COLLECT INTO
1299 daily_bucket_dates
1300 FROM msc_calendar_dates
1301 WHERE calendar_code = v_cal_code
1302 and exception_set_id = -1
1303 and calendar_date >= p_start_date
1304 and calendar_date < p_start_date + daily_bucket_count
1305 order by calendar_date asc;
1306 END IF;
1307 v_temp_date := daily_bucket_dates(daily_bucket_dates.COUNT);
1308 END IF;
1309
1310 /**
1311 * get the weekly buckets.
1312 * simple case - days buckets found and added.
1313 * next case -- no day buckets then
1314 * start date is sysdate or arg_start_Date
1315 * this has to be the first bucket
1316 * and then get each bucket + the number of buckets needed
1317 * for padding until the next monthly bucket.
1318 */
1319
1320 IF (weekly_bucket_count <> 0 ) THEN
1321 IF (period_bucket_count <> 0) THEN
1322 IF (daily_bucket_count <> 0 ) THEN
1323 SELECT
1324 week.week_start_date
1325 BULK COLLECT INTO
1326 weekly_bucket_dates
1327 FROM msc_cal_week_start_dates week, msc_period_start_dates month
1331 and week.week_start_date < month.next_date
1328 WHERE week.calendar_code = v_cal_code
1329 and week.exception_set_id = -1
1330 and week.week_start_date >= v_temp_date
1332 and month.calendar_code = v_cal_code
1333 and month.exception_set_id = -1
1334 and month.period_start_date <= v_temp_date + 7*weekly_bucket_count
1335 and month.next_date > v_temp_date + 7*weekly_bucket_count
1336 order by week.week_start_date asc;
1337
1338 weekly_bucket_count := weekly_bucket_dates.COUNT;
1339 ELSE
1340 SELECT week.week_start_date into v_temp_bucket_start_date
1341 from msc_cal_week_start_dates week
1342 where week.calendar_code = v_cal_code
1343 and week.exception_set_id = -1
1344 and week.week_start_date <= p_start_date
1345 and week.next_date > p_start_date
1346 order by week.week_start_date asc;
1347
1348 SELECT
1349 week.week_start_date
1350 BULK COLLECT INTO
1351 weekly_bucket_dates
1352 FROM msc_cal_week_start_dates week, msc_period_start_dates month
1353 WHERE week.calendar_code = v_cal_code
1354 and week.exception_set_id = -1
1355 and week.next_date > v_temp_bucket_start_date
1356 and month.calendar_code = v_cal_code
1357 and month.exception_set_id = -1
1358 and month.period_start_date < v_temp_bucket_start_date + 7*weekly_bucket_count
1359 and month.next_date >= v_temp_bucket_start_date + 7*weekly_bucket_count
1360 and week.week_start_date < month.next_date
1361 order by week.week_start_date asc;
1362
1363 weekly_bucket_count := weekly_bucket_dates.COUNT;
1364 END IF;
1365 ELSE
1366 IF (daily_bucket_count <> 0) THEN
1367 SELECT
1368 week_start_date
1369 BULK COLLECT INTO
1370 weekly_bucket_dates
1371 FROM msc_cal_week_start_dates
1372 WHERE calendar_code = v_cal_code
1373 and exception_set_id = -1
1374 and week_start_date > v_temp_date
1375 and week_start_date <= v_temp_date + 7*weekly_bucket_count
1376 order by week_start_date asc;
1377 ELSE
1378 SELECT
1379 week_start_date
1380 BULK COLLECT INTO
1381 weekly_bucket_dates
1382 FROM msc_cal_week_start_dates
1383 WHERE calendar_code = v_cal_code
1384 and exception_set_id = -1
1385 and next_date > p_start_date
1386 and next_date <= p_start_date + 7*weekly_bucket_count
1387 order by week_start_date asc;
1388 END IF;
1389 END IF;
1390 END IF;
1391
1392 IF (daily_bucket_count > 36) THEN
1393 daily_bucket_count := 36;
1394 weekly_bucket_count := 0;
1395 period_bucket_count := 0;
1396 ELSE
1397 IF (daily_bucket_count + weekly_bucket_count > 36) THEN
1398 weekly_bucket_count := 36 - daily_bucket_count;
1399 period_bucket_count := 0;
1400 ELSE
1401 IF (daily_bucket_count + weekly_bucket_count + period_bucket_count > 36) THEN
1402 period_bucket_count := 36 - weekly_bucket_count - daily_bucket_count;
1403 END IF;
1404 END IF;
1405 END IF;
1406
1407
1408 /**
1409 * get the monthly buckets.
1410 * simple case - days buckets found or week_buckets found and added.
1411 * next case -- no day buckets or week buckets then
1412 * start date is sysdate or arg_start_Date
1413 * this has to be the first bucket
1414 * and then add 1 calendar month for each bucket thereafter for # of monthly buckets.
1415 */
1416
1417 IF (period_bucket_count <> 0) THEN
1418
1419 IF (weekly_bucket_count <> 0) THEN
1420 v_month_bkt_start_date := weekly_bucket_dates(weekly_bucket_count) + 7;
1421 ELSIF (daily_bucket_count <> 0) THEN
1422 v_month_bkt_start_date := daily_bucket_dates(daily_bucket_count) + 1;
1423 ELSE
1424 select month.period_start_date into v_month_bkt_start_date
1425 from msc_period_start_dates month
1426 where month.calendar_code = v_cal_code
1427 and month.exception_set_id = -1
1428 and month.period_start_date <= p_start_date
1429 and month.next_date > p_start_date
1430 order by month.period_start_date asc;
1431 END IF;
1432
1433 select period_start_date
1434 BULK COLLECT INTO
1435 monthly_bucket_dates
1436 from msc_period_start_dates
1437 where calendar_code = v_cal_code
1438 and exception_set_id = -1
1439 and level <= period_bucket_count
1440 start with period_start_date <= v_month_bkt_start_date
1441 and next_date > v_month_bkt_start_date
1442 connect by (
1443 PRIOR calendar_code = calendar_code
1444 and PRIOR exception_set_id = exception_set_id
1445 and PRIOR sr_instance_id = sr_instance_id
1446 and PRIOR next_date = period_start_date
1447 and PRIOR period_start_date < period_start_date
1448 )
1449 order by period_start_date asc;
1450 END IF;
1451
1452 counter := 0;
1453
1454 if daily_bucket_count = 0 then -- from the user prefs
1455 DAY_BUCKET_FOUND := FALSE ;
1456 else
1457 FOR i IN 1..daily_bucket_count LOOP
1458 counter := counter + 1;
1459 var_dates(counter) := daily_bucket_dates(i);
1460 if counter > 36 then
1461 exit;
1462 end if;
1463 END LOOP;
1464 end if;
1465
1469 end if;
1466 if counter > 36 then
1467 v_last_bkt_date := var_dates(37);
1468 return;
1470
1471 if weekly_bucket_count = 0 then
1472 WEEK_BUCKET_FOUND := FALSE;
1473 else
1474 FOR i IN 1..weekly_bucket_count LOOP
1475 counter := counter + 1;
1476 var_dates(counter) := weekly_bucket_dates(i);
1477 if counter > 36 then
1478 exit;
1479 end if;
1480 END LOOP;
1481 end if;
1482
1483 if counter > 36 then
1484 v_last_bkt_date := var_dates(37);
1485 return;
1486 end if;
1487
1488
1489 if period_bucket_count = 0 then
1490 MONTH_BUCKET_FOUND := FALSE;
1491 else
1492 FOR i IN 1..period_bucket_count LOOP
1493 counter := counter + 1;
1494 var_dates(counter) := monthly_bucket_dates(i);
1495 if counter > 36 then
1496 exit;
1497 end if;
1498 END LOOP;
1499 end if;
1500
1501 if counter = 0 then
1502 v_last_bkt_date := p_start_date;
1503 else
1504 v_last_bkt_date := var_dates(counter);
1505 end if;
1506
1507
1508 if period_bucket_count = 0 then
1509 if weekly_bucket_count = 0 then
1510 v_last_bkt_date := v_last_bkt_date + 1;
1511 else
1512 v_last_bkt_date := v_last_bkt_date + 7;
1513 end if;
1514 else
1515 select next_date into v_last_bkt_date
1516 from msc_period_start_dates
1517 where calendar_code = v_cal_code
1518 and exception_set_id = -1
1519 and period_start_date = var_dates(counter);
1520 end if;
1521
1522 g_num_of_buckets := least(counter,36) ;
1523
1524 Exception
1525 when NO_DATA_FOUND then
1526 arg_err_msg := arg_err_msg || ' Bkt gen' || SQLERRM;
1527 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1528 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'get_bucket_dates',' Bkt gen ' || SQLERRM);
1529 end if;
1530
1531 return ;
1532
1533 END get_bucket_dates;
1534
1535 /**
1536 * The following function checks to see
1537 * if multiple sites were found in the horizon.
1538 * The bucket dates are already populated with default calendar.
1539 * @return number 1 for multiple sites and 0 for single site.
1540 */
1541 Function check_for_multiple_sites(v_order IN NUMBER) RETURN NUMBER
1542 IS
1543 l_statement VARCHAR2(4000);
1544 l_sel_count VARCHAR2(100);
1545 l_sel_cust_site VARCHAR2(100);
1546 l_sel_sup_site VARCHAR2(100);
1547 l_orders VARCHAR2(50);
1548
1549 l_date VARCHAR2(20) := to_char(trunc(nvl(arg_from_date, p_start_date)),'MM-DD-YYYY');
1550 l_last VARCHAR2(20) := to_char(trunc(nvl(v_last_bkt_date, sysdate)), 'MM-DD-YYYY');
1551 l_cust_count NUMBER := 0;
1552 l_sup_count NUMBER := 0;
1553 l_multiple_sites NUMBER := 1;
1554
1555
1556
1557 BEGIN
1558
1559 l_orders := v_sales_forecast || ',' || v_order_forecast || ',' || v_supply_commit ;
1560 l_orders := l_orders || ',' || v_returns_forecast ||',' || v_def_outbound_shipment ; -- bug#6893383
1561 l_orders := l_orders || ',' || v_hist_sales || ',' || v_sell_thru_fcst || ',' || v_negotiated_capacity ;
1562 l_orders := l_orders || ',' || v_safety_stock || ',' || v_proj_avail_bal || ',' || v_proj_safety_stock ;
1563 l_orders := l_orders || ',' || v_alloc_onhand || ',' || v_unalloc_onhand ;
1564 l_orders := l_orders || ',' || v_proj_unalloc_avl_bal || ',' || v_proj_alloc_avl_bal ;
1565 l_orders := l_orders || ',' || v_purchase_order || ',' || v_sales_order || ',' || v_asn ;
1566 l_orders := l_orders || ',' || v_shipment_receipt || ',' || v_intransit || ',' || v_work_order ;
1567 l_orders := l_orders || ',' || v_replenishment || ',' || v_requisition ;
1568
1569 l_sel_count := ' SELECT COUNT(distinct customer_site_id), COUNT(distinct supplier_site_id) ';
1570 l_sel_cust_site := ' SELECT distinct customer_site_id, customer_id ';
1571 l_sel_sup_site := ' SELECT distinct supplier_site_id, supplier_id ';
1572 l_statement := ' FROM msc_sup_dem_entries_ui_v';
1573 l_statement := l_statement || ' WHERE plan_id = -1 AND PUBLISHER_ORDER_TYPE IN (';
1574 l_statement := l_statement || l_orders;
1575 l_statement := l_statement || ') ' ;
1576 l_statement := l_statement || ' AND NVL(KEY_DATE, SYSDATE + 99999) >= to_date(''' || l_date || ''', ''MM-DD-YYYY'') ';
1577 l_statement := l_statement || ' AND NVL(KEY_DATE, SYSDATE - 99999) < to_date(''' || l_last || ''', ''MM-DD-YYYY'') ';
1578
1579 if arg_where_clause is not null then
1580 -- here remove the reading clause from the arg where clause if it is OR
1581
1582 if instr(arg_where_clause, 'OR') > 0 and instr(arg_where_clause, 'OR') < 5 then
1583 l_statement := l_statement || ' AND (' || substr(arg_where_clause, instr(arg_where_clause, 'OR') + 2) || ')';
1584 else
1585 l_statement := l_statement || ' ' || arg_where_clause;
1586 end if;
1587 end if;
1588
1589 OPEN osce_bucketed_plan FOR l_sel_count || l_statement;
1590 LOOP
1591 FETCH osce_bucketed_plan INTO
1592 l_cust_count, l_sup_count;
1593 EXIT WHEN osce_bucketed_plan%NOTFOUND;
1594 END LOOP;
1595 CLOSE osce_bucketed_plan;
1596
1597
1598 /** if the result set contains only one customer site
1599 * get the customer id and customer site id
1603 */
1600 *
1601 * else if the result set contains only one supplier site
1602 * get the supplier id and supplier site id
1604
1605 IF l_cust_count = 1 THEN
1606 l_multiple_sites := 0;
1607 v_supplier_id := null;
1608 v_supplier_site_id := null;
1609 OPEN osce_bucketed_plan FOR l_sel_cust_site || l_statement || ' AND ROWNUM <= 1 ';
1610 LOOP
1611 FETCH osce_bucketed_plan INTO
1612 v_customer_site_id, v_customer_id;
1613 EXIT WHEN osce_bucketed_plan%NOTFOUND;
1614 END LOOP;
1615 CLOSE osce_bucketed_plan;
1616 IF l_sup_count = 1 THEN
1617 OPEN osce_bucketed_plan FOR l_sel_sup_site || l_statement || ' AND ROWNUM <= 1 ';
1618 LOOP
1619 FETCH osce_bucketed_plan INTO
1620 v_supplier_site_id, v_supplier_id;
1621 EXIT WHEN osce_bucketed_plan%NOTFOUND;
1622 END LOOP;
1623 CLOSE osce_bucketed_plan;
1624 END IF;
1625
1626 ELSE
1627 l_multiple_sites := 1;
1628 v_supplier_id := null;
1629 v_supplier_site_id := null;
1630 v_customer_id := null;
1631 v_customer_site_id := null;
1632 END IF;
1633
1634 return l_multiple_sites;
1635
1636 EXCEPTION
1637 WHEN OTHERS THEN
1638 arg_err_msg := ' check_for_multiple_sites ' || SQLERRM || ' ' || l_statement;
1639 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1640 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'check_for_multiple_sites', SQLERRM);
1641 end if;
1642 raise;
1643 END check_for_multiple_sites ;
1644
1645
1646 /*
1647 * The following procedure initializes the pl/sql tables
1648 * for a given counter.
1649 * This is called before adding a row into the PL/sql table
1650 * @param the counter.
1651 */
1652 PROCEDURE initialize(cnt IN NUMBER) IS
1653
1654 k INTEGER := cnt;
1655
1656 BEGIN
1657 var_qty1(k) := 0;
1658 var_qty2(k) := 0;
1659 var_qty3(k) := 0;
1660 var_qty4(k) := 0;
1661 var_qty5(k) := 0;
1662 var_qty6(k) := 0;
1663 var_qty7(k) := 0;
1664 var_qty8(k) := 0;
1665 var_qty9(k) := 0;
1666 var_qty10(k) := 0;
1667 var_qty11(k) := 0;
1668 var_qty12(k) := 0;
1669 var_qty13(k) := 0;
1670 var_qty14(k) := 0;
1671 var_qty15(k) := 0;
1672 var_qty16(k) := 0;
1673 var_qty17(k) := 0;
1674 var_qty18(k) := 0;
1675 var_qty19(k) := 0;
1676 var_qty20(k) := 0;
1677 var_qty21(k) := 0;
1678 var_qty22(k) := 0;
1679 var_qty23(k) := 0;
1680 var_qty24(k) := 0;
1681 var_qty25(k) := 0;
1682 var_qty26(k) := 0;
1683 var_qty27(k) := 0;
1684 var_qty28(k) := 0;
1685 var_qty29(k) := 0;
1686 var_qty30(k) := 0;
1687 var_qty31(k) := 0;
1688 var_qty32(k) := 0;
1689 var_qty33(k) := 0;
1690 var_qty34(k) := 0;
1691 var_qty35(k) := 0;
1692 var_qty36(k) := 0;
1693 var_qty_nobkt(k) := 0;
1694 var_past_due_qty(k) := 0;
1695 var_day_bkt(k) := 0;
1696 var_week_bkt(k) := 0;
1697 var_month_bkt(k) := 0;
1698
1699 var_supplier_id(k) := '';
1700 var_customer_id(k) := '';
1701 var_supplier_site_id(k) := '';
1702 var_customer_site_id(k) := '';
1703 var_item_id(k) := -1;
1704 var_next_item(k) := -1;
1705
1706 var_relation(k) := '';
1707 var_order_relation(k) := '';
1708 var_from_co_name(k) := '';
1709 var_from_org_name(k) := '';
1710 var_item_name(k) := '';
1711 var_item_name_desc(k) := '';
1712 var_sup_item(k):='';
1713 var_uom(k) := '';
1714 var_order_desc(k) := '';
1715 var_supplier(k) := '' ;
1716 var_customer(k) := '';
1717 var_supplier_org(k) := '';
1718 var_customer_org(k) := '';
1719 var_edit_flag(k) := 0;
1720
1721 IF temp_sup_site.COUNT > 0 THEN
1722 for k in temp_sup_site.FIRST..temp_sup_site.LAST loop
1723 temp_sup_site(k) := 0 ;
1724 end loop ;
1725 END IF;
1726
1727 IF temp_cust_site.COUNT > 0 THEN
1728 for k in temp_cust_site.FIRST..temp_cust_site.LAST LOOP
1729 temp_cust_site(k) := 0;
1730 end loop;
1731 END IF;
1732
1733 IF temp_sup.COUNT > 0 THEN
1734 for k in temp_sup.FIRST..temp_sup.LAST loop
1735 temp_sup(k) := 0;
1736 end loop;
1737 END IF;
1738
1739 IF temp_cust.COUNT > 0 THEN
1740 for k in temp_cust.FIRST..temp_cust.LAST loop
1741 temp_cust(k) := 0 ;
1742 end loop;
1743 END IF;
1744
1745 EXCEPTION
1746 when others then
1747 arg_err_msg := arg_err_msg || ' initialize ' || SQLERRM;
1748 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1749 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'initialize', SQLERRM);
1750 end if;
1751 raise ;
1755 /**
1752 END initialize ;
1753
1754
1756 * The following procedure sets the date buckets
1757 * into individual variables.
1758 * This is required to insert into the headers table.
1759 */
1760 PROCEDURE set_date_variables IS
1761
1762 k INTEGER := 0;
1763
1764 BEGIN
1765 if g_num_of_buckets < 36 then
1766 k := var_dates.COUNT;
1767
1768 for k in (var_dates.COUNT+1)..36 loop
1769
1770 var_dates(k) := null;
1771
1772 end loop;
1773 end if;
1774
1775 EXCEPTION
1776 when others then
1777 arg_err_msg := arg_err_msg || ' set dates ' || SQLERRM;
1778 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1779 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'set_date_variables', SQLERRM);
1780 end if;
1781 return ;
1782 END set_date_variables ;
1783
1784
1785 /**
1786 * The following procedure is for debug purposes
1787 * - prints the query to the screen.
1788 * NOTE: uncomment the --dbms_output and set serveroutput on
1789 * before running this procedure.
1790 */
1791 PROCEDURE print_query IS
1792 j INTEGER := 1;
1793 v_query VARCHAR2(4000) ;
1794
1795 BEGIN
1796 while j < length(g_statement) loop
1797 v_query := v_query || substr(g_statement,j,200) ;
1798 --dbms_output.put_line(substr(g_statement,j,200));
1799 j := j+ 200;
1800 end loop;
1801
1802 EXCEPTION
1803 when others then
1804 arg_err_msg := arg_err_msg || 'print_query ' || SQLERRM ;
1805 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1806 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'print_query', SQLERRM);
1807 end if;
1808 raise;
1809 END print_query ;
1810
1811
1812 /**
1813 * The following function returns the ranking of the order types.
1814 * as defined in the user_preference.
1815 * These ranking order variables are populated during the
1816 * get_user_prefs() method.
1817 * This function is called when inserting records into the lines table
1818 *
1819 * @param the order type.
1820 * @return the order rank
1821 */
1822 FUNCTION get_order_rank (arg_order_type IN NUMBER) RETURN NUMBER
1823 IS
1824 BEGIN
1825
1826 if arg_order_type = SALES_FORECAST then
1827 return v_o_seller_forecast;
1828 elsif arg_order_type = ORDER_FORECAST_CST then
1829 return v_o_forecast ;
1830 elsif arg_order_type = SUPPLY_COMMIT then
1831 return v_o_supply_commit ;
1832 elsif arg_order_type = G_RETURNS_FORECAST then
1833 return v_o_returns_forecast ; -- bug#6893383
1834 elsif arg_order_type = G_DEFECTIVE_OUTBOUND_SHIPMENT then
1835 return v_o_def_outbound_shipment ; -- bug#6893383
1836 elsif arg_order_type = HISTORICAL_SALES then
1837 return v_o_hist_sales ;
1838 elsif arg_order_type = SELL_THRU_FORECAST then
1839 return v_o_sell_thro_fcst ;
1840 elsif arg_order_type = NEGOTIATED_CAPACITY then
1841 return v_o_negcap;
1842 elsif arg_order_type = SAFETY_STOCK then
1843 return v_o_ss;
1844 elsif arg_order_type = PROJ_AVAIL_BAL then
1845 return v_o_pab;
1846 elsif arg_order_type = PROJ_SAFETY_STOCK then
1847 return v_o_projected_ss;
1848 elsif arg_order_type = ALLOCATED_ONHAND then
1849 return v_o_alct_onhand ;
1850 elsif arg_order_type = UNALLOCATED_ONHAND then
1851 return v_o_unalct_onhand ;
1852 elsif arg_order_type = PURCHASE_ORDER then
1853 return v_o_po ;
1854 elsif arg_order_type = PROJ_UNALOC_AVL_BAL then
1855 return v_o_unalct_prjt_avl_bal ;
1856 elsif arg_order_type = PROJ_ALLOC_AVL_BAL then
1857 return v_o_alcat_prjt_avl_bal ;
1858 elsif arg_order_type = SALES_ORDER then
1859 return v_o_sales_orders ;
1860 elsif arg_order_type = ASN then
1861 return v_o_asn ;
1862 elsif arg_order_type = SHIPMENT_RECEIPT then
1863 return v_o_receiving ;
1864 elsif arg_order_type = INTRANSIT then
1865 return v_o_transit ;
1866 elsif arg_order_type = WORK_ORDER then
1867 return v_o_wip ;
1868 elsif arg_order_type = PO_ACK then
1869 return v_o_po_ack ;
1870 elsif arg_order_type = REPLENISHMENT then
1871 return v_o_replenishment ;
1872 elsif arg_order_type = REQUISITION then
1873 return v_o_req ;
1874 elsif arg_order_type = PO_FROM_PLAN then
1875 return v_o_po_from_plan ;
1876 elsif arg_order_type = RELEASED_PLAN then
1877 return v_o_released_plan ;
1878 elsif arg_order_type = PLANNED_ORDER then
1879 return v_o_planned_order ;
1880 elsif arg_order_type = RUN_TOT_SUPPLY then
1881 return v_o_run_tot_supply ;
1882 elsif arg_order_type = RUN_TOT_DEMAND then
1883 return v_o_run_tot_demand ;
1884 else
1885 return 0;
1886 end if;
1887
1888 END get_order_rank ;
1889
1893 * This is used to convert the array that holds cust/sup ids
1890
1891 /**
1892 * The following procedure converts an array into a string
1894 * into a comma delimited string to store in the lines table.
1895 *
1896 * @param number array of ids
1897 * @param string
1898 * @return - a comma delimited string of ids.
1899 */
1900 FUNCTION convert_to_string(v_array IN num, ret_str IN OUT NOCOPY VARCHAR2) RETURN VARCHAR2
1901 IS
1902 l NUMBER;
1903 cnt number := 0;
1904 BEGIN
1905 if v_array.COUNT > 0 then
1906 FOR l IN v_array.FIRST..v_array.LAST LOOP
1907
1908 if v_array(l) <> 0 then
1909
1910 cnt := cnt + 1;
1911 if cnt = 1 then
1912 ret_str := v_array(l);
1913 else
1914 ret_str := ret_str || ',' || v_array(l) ;
1915 end if;
1916
1917 end if;
1918 END LOOP;
1919 end if;
1920
1921 return ret_str;
1922 EXCEPTION
1923 when no_data_found then
1924 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1925 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'convert_to_string', 'No data found');
1926 end if;
1927 return null;
1928
1929 when others then
1930 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1931 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'convert_to_string', SQLERRM);
1932 end if;
1933 return null;
1934 END;
1935
1936 /**
1937 * The foll function adds a value to an existing array.
1938 * This is used to add sup/cust ids to an array when the aggregation is
1939 * at a higher level.
1940 *
1941 * @param the array to which the value is to be added
1942 * @param value to add to the array
1943 * @return the array.
1944 */
1945 FUNCTION add_to_array(v_array IN OUT NOCOPY num, v_value IN NUMBER) RETURN num
1946 IS
1947 l NUMBER;
1948 add_value NUMBER := 0;
1949 v_temp_cnt NUMBER := 0;
1950 BEGIN
1951 if v_array.COUNT > 0 then
1952 FOR l IN v_array.FIRST..v_array.LAST LOOP
1953 if v_array(l) = v_value then
1954 add_value := 1;
1955 exit;
1956 end if;
1957 END LOOP;
1958 end if;
1959
1960 if add_value <> 1 then
1961 v_temp_cnt := v_array.COUNT + 1;
1962 v_array(v_temp_cnt) := v_value;
1963 end if;
1964
1965 return v_array;
1966 EXCEPTION
1967 when no_data_found then
1968 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1969 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'add_to_array', 'No data found');
1970 end if;
1971 return v_array;
1972
1973 when others then
1974 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1975 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'add_to_array', SQLERRM);
1976 end if;
1977 return v_array;
1978 END;
1979
1980
1981 /**
1982 * The following procedure sets the editable flag of the row
1983 * The row is editable if
1984 * - the order type is order forecast, sales forecast, supply commit,
1985 * historical sales, sell through forecast and negotiated capacity
1986 * - the user company is the document owner (publisher)
1987 * - the user preference aggregation is at site for myco and tpco.
1988 * - the user preferences are homogeneous
1989 * - the buckets fo the data are homogeneous
1990 * - the data buckets is equal to or lower than the user prefs bucket.
1991 * The values are
1992 * 0 - editable
1993 * 1 - not editable
1994 */
1995 PROCEDURE set_editable_row(arg_pos IN NUMBER) IS
1996
1997 c number := arg_pos;
1998 -- re-fix for bug#4111132 can lead to bug when aggregation at higher level
1999 -- var_cust/supp_site can be list of id concatenated by ',' eg '34,45'
2000 cursor security_check
2001 is select 1
2002 from msc_sup_dem_update_security_v a --,
2003 --msc_sup_dem_entries_ui_v b
2004 where --a.transaction_id = b.transaction_id
2005 a.customer_id = previous_rec.customer_id
2006 and a.customer_site_id = previous_rec.cust_site_id
2007 and a.supplier_id = previous_rec.supplier_id
2008 and a.supplier_site_id = previous_rec.supp_site_id
2009 and a.publisher_order_type = var_order(arg_pos)
2010 and a.inventory_item_id = var_item_id(arg_pos);
2011
2012 l_sec number := 0;
2013
2014 BEGIN
2015
2016 v_user_company := sys_context('MSC','COMPANY_NAME');
2017 var_edit_flag(c) := 1; -- not editable
2018
2019 -- order types
2020
2021 if var_order(c) in (ORDER_FORECAST_CST,SUPPLY_COMMIT,HISTORICAL_SALES,
2022 SELL_THRU_FORECAST,NEGOTIATED_CAPACITY,SALES_FORECAST )
2023 -- AND -- re-fix for bug#4111132
2024 -- l_sec = 1 -- replace publisher logic w/ sec rules (bug 4111132)
2028 (
2025 -- user has to be publisher of doc
2026 -- var_from_co_name(c) = v_user_company
2027 AND -- user pref has homogeneous buckets
2029 ( daily_bucket_count > 0 and weekly_bucket_count = 0 and period_bucket_count = 0) OR
2030 ( daily_bucket_count = 0 and weekly_bucket_count > 0 and period_bucket_count = 0) OR
2031 ( daily_bucket_count = 0 and weekly_bucket_count = 0 and period_bucket_count > 0 )
2032 )
2033 AND -- user aggregation is at site
2034 ( myco_agg = ORG_AGG and tpco_agg = ORG_AGG )
2035 AND -- data bkt has to be homogeneous
2036 (
2037 (var_day_bkt(c) > 0 and var_week_bkt(c) = 0 and var_month_bkt(c) = 0) or
2038 (var_day_bkt(c) = 0 and var_week_bkt(c) > 0 and var_month_bkt(c) = 0) or
2039 (var_day_bkt(c) = 0 and var_week_bkt(c) = 0 and var_month_bkt(c) > 0 )
2040 )
2041 AND -- user prefs bkt shoudl be equal to the data bkt
2042 ( (period_bucket_count > 0 and var_week_bkt(c) = 0 and var_day_bkt(c) = 0) OR -- cannot be week and day
2043 ( weekly_bucket_count > 0 and var_month_bkt(c) = 0 and var_day_bkt(c) = 0) OR -- cannot be month and day
2044 ( daily_bucket_count > 0 and var_month_bkt(c) = 0 and var_week_bkt(c) = 0) -- cannot be month and week
2045 )
2046 THEN
2047
2048 OPEN security_check;
2049 FETCH security_check into l_sec;
2050 CLOSE security_check;
2051
2052 IF l_sec = 1 THEN
2053 var_edit_flag(c) := 0; -- editable.
2054 END IF;
2055 end if;
2056
2057 -- set the bucket type for the row.
2058 if var_day_bkt(c) > 0 and (var_week_bkt(c) = 0 and var_month_bkt(c) = 0) then
2059 var_bkt_type(c) := DAY_BUCKET;
2060
2061 elsif var_day_bkt(c) = 0 and (var_week_bkt(c) > 0 and var_month_bkt(c) = 0) then
2062 var_bkt_type(c) := WEEK_BUCKET;
2063
2064 elsif var_day_bkt(c) = 0 and (var_week_bkt(c) = 0 and var_month_bkt(c) > 0) then
2065 var_bkt_type(c) := MONTH_BUCKET;
2066
2067 else
2068 var_bkt_type(c) := 0;
2069
2070 end if;
2071
2072 EXCEPTION
2073 WHEN OTHERS THEN
2074 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2075 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'set_editable_row', SQLERRM);
2076 end if;
2077 null;
2078 END set_editable_row ;
2079
2080 /**
2081 * The following procedure sets the non qty variables
2082 * into the appropriate position in the respective pl/sql table
2083 * @param the position.
2084 */
2085 PROCEDURE set_non_qty_data(arg_pos IN NUMBER) IS
2086 counter number := arg_pos ;
2087 temp_str VARCHAR2(2000);
2088 l number ;
2089 BEGIN
2090
2091 var_relation(counter) := nvl(previous_rec.relation,'NA') ;
2092 var_order_relation(counter) := nvl(previous_rec.order_relation,'NA') ;
2093 var_from_co_name(counter) := nvl(previous_rec.from_co_name,'NA' );
2094 var_from_org_name(counter) := nvl(previous_rec.from_org_name,'NA' );
2095
2096 var_item_name(counter) := previous_rec.item_name;
2097 var_order(counter) := nvl(previous_rec.order_type,-1) ;
2098 var_order_rank(counter) := nvl(get_order_rank(previous_rec.order_type),0);
2099 var_order_desc(counter) := nvl(previous_rec.order_desc,var_order(counter) );
2100 var_uom(counter) := nvl(previous_rec.uom,'Ea');
2101 var_item_name_desc(counter) := previous_rec.item_desc;
2102 var_sup_item(counter):=previous_rec.supplier_item_name;
2103
2104 var_supplier(counter) := previous_rec.supplier_name;
2105 var_customer(counter) := previous_rec.customer_name;
2106 var_supplier_org(counter) := previous_rec.supplier_org;
2107 var_customer_org(counter) := previous_rec.customer_org;
2108 var_item_id(counter) := previous_rec.item_id;
2109
2110 var_pub_id(counter) := previous_rec.publisher_id;
2111 var_pub_site_id(counter) := previous_rec.publisher_site_id;
2112
2113
2114 temp_str := NULL;
2115 if temp_sup is null then
2116 var_supplier_id(counter) := to_char(previous_rec.supplier_id);
2117 else
2118 temp_str := convert_to_string(temp_sup, temp_str) ;
2119 var_supplier_id(counter) := temp_str;
2120 end if;
2121
2122 temp_str := NULL;
2123 if temp_cust is null then
2124 var_customer_id(counter) := to_char(previous_rec.customer_id);
2125 else
2126 temp_str := convert_to_string(temp_cust, temp_str) ;
2127 var_customer_id(counter) := temp_str;
2128 end if;
2129
2130 temp_str := NULL;
2131 if temp_sup_site is null then
2132 var_supplier_site_id(counter) := to_char(previous_rec.supp_site_id);
2133 else
2134 temp_str := convert_to_string(temp_sup_site, temp_str) ;
2135 var_supplier_site_id(counter) := temp_str;
2136 end if;
2137
2138 temp_str := NULL;
2139 if temp_cust_site is null then
2140 var_customer_site_id(counter) := to_char(previous_rec.cust_site_id);
2141 else
2142 temp_str := convert_to_string(temp_cust_site, temp_str) ;
2143 var_customer_site_id(counter) := temp_str;
2147 set_editable_row(arg_pos);
2144 end if;
2145
2146 -- set the ditable falg for the row.
2148
2149 EXCEPTION
2150 when others then
2151 arg_err_msg := arg_err_msg || ' set data' || SQLERRM ;
2152 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2153 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'set_non_qty_data', SQLERRM);
2154 end if;
2155 raise;
2156 END set_non_qty_data;
2157
2158
2159 /**
2160 * The following function calculates and returns the past due qty
2161 * for the current order type PO, SO or ASN.
2162 *
2163 * @param order type.
2164 */
2165 Function calc_past_due_qty(v_order IN NUMBER) RETURN NUMBER
2166 IS
2167 v_past_due_qty NUMBER := 0;
2168 v_viewer_id number := -1;
2169 l_base_qty number := 0;
2170 l_config_qty number := 0;
2171 BEGIN
2172
2173 select sys_context('MSC','COMPANY_ID') into v_viewer_id from dual;
2174
2175 if tpco_agg = ORG_AGG THEN
2176
2177 if myco_agg = ORG_AGG THEN
2178
2179 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2180 customer_id, tp_quantity,
2181 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2182 from msc_sup_dem_entries_ui_v
2183 where base_item_id = activity_rec.item_id
2184 and customer_id = activity_rec.customer_id
2185 and customer_site_id = activity_rec.cust_site_id
2186 and supplier_id = activity_rec.supplier_id
2187 and supplier_site_id = activity_rec.supp_site_id
2188 and publisher_order_type = activity_rec.order_type
2189 and key_date < p_start_date;
2190
2191
2192 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2193 customer_id, tp_quantity,
2194 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2195 from msc_sup_dem_entries_ui_v
2196 where inventory_item_id = activity_rec.item_id
2197 and customer_id = activity_rec.customer_id
2198 and customer_site_id = activity_rec.cust_site_id
2199 and supplier_id = activity_rec.supplier_id
2200 and supplier_site_id = activity_rec.supp_site_id
2201 and publisher_order_type = activity_rec.order_type
2202 and key_date < p_start_date;
2203
2204 v_past_due_qty := l_base_qty + l_config_qty;
2205
2206 elsif myco_agg = COMPANY_AGG THEN
2207
2208 if(v_viewer_id = activity_rec.customer_id) then
2209
2210 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2211 customer_id, tp_quantity,
2212 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2213 from msc_sup_dem_entries_ui_v
2214 where base_item_id= activity_rec.item_id
2215 and customer_id = activity_rec.customer_id
2216 and supplier_id = activity_rec.supplier_id
2217 and supplier_site_id = activity_rec.supp_site_id
2218 and publisher_order_type = activity_rec.order_type
2219 and key_date < p_start_date;
2220
2221 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2222 customer_id, tp_quantity,
2223 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2224 from msc_sup_dem_entries_ui_v
2225 where inventory_item_id = activity_rec.item_id
2226 and customer_id = activity_rec.customer_id
2227 and supplier_id = activity_rec.supplier_id
2228 and supplier_site_id = activity_rec.supp_site_id
2229 and publisher_order_type = activity_rec.order_type
2230 and key_date < p_start_date;
2231
2232 v_past_due_qty := l_base_qty + l_config_qty;
2233
2234 elsif(v_viewer_id = activity_rec.supplier_id) then
2235
2236 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2237 customer_id, tp_quantity,
2238 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2239 from msc_sup_dem_entries_ui_v
2240 where base_item_id = activity_rec.item_id
2241 and customer_id = activity_rec.customer_id
2242 and customer_site_id = activity_rec.cust_site_id
2243 and supplier_id = activity_rec.supplier_id
2244 and publisher_order_type = activity_rec.order_type
2245 and key_date < p_start_date;
2246
2247 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2248 customer_id, tp_quantity,
2249 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2250 from msc_sup_dem_entries_ui_v
2251 where inventory_item_id = activity_rec.item_id
2252 and customer_id = activity_rec.customer_id
2253 and customer_site_id = activity_rec.cust_site_id
2254 and supplier_id = activity_rec.supplier_id
2255 and publisher_order_type = activity_rec.order_type
2256 and key_date < p_start_date;
2257
2258 v_past_due_qty := l_base_qty + l_config_qty;
2259 end if;
2260
2261 end if;
2262
2263 elsif tpco_agg = COMPANY_AGG THEN
2264
2265 if myco_agg = ORG_AGG THEN
2266
2267 if(v_viewer_id = activity_rec.supplier_id) then
2268
2269 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2270 customer_id, tp_quantity,
2274 and customer_id = activity_rec.customer_id
2271 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2272 from msc_sup_dem_entries_ui_v
2273 where base_item_id = activity_rec.item_id
2275 and supplier_id = activity_rec.supplier_id
2276 and supplier_site_id = activity_rec.supp_site_id
2277 and publisher_order_type = activity_rec.order_type
2278 and key_date < p_start_date;
2279
2280 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2281 customer_id, tp_quantity,
2282 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2283 from msc_sup_dem_entries_ui_v
2284 where inventory_item_id = activity_rec.item_id
2285 and customer_id = activity_rec.customer_id
2286 and supplier_id = activity_rec.supplier_id
2287 and supplier_site_id = activity_rec.supp_site_id
2288 and publisher_order_type = activity_rec.order_type
2289 and key_date < p_start_date;
2290
2291 v_past_due_qty := l_base_qty + l_config_qty;
2292
2293 elsif(v_viewer_id = activity_rec.customer_id) then
2294
2295 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2296 customer_id, tp_quantity,
2297 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2298 from msc_sup_dem_entries_ui_v
2299 where base_item_id = activity_rec.item_id
2300 and customer_id = activity_rec.customer_id
2301 and customer_site_id = activity_rec.cust_site_id
2302 and supplier_id = activity_rec.supplier_id
2303 and publisher_order_type = activity_rec.order_type
2304 and key_date < p_start_date;
2305
2306 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2307 customer_id, tp_quantity,
2308 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2309 from msc_sup_dem_entries_ui_v
2310 where inventory_item_id = activity_rec.item_id
2311 and customer_id = activity_rec.customer_id
2312 and customer_site_id = activity_rec.cust_site_id
2313 and supplier_id = activity_rec.supplier_id
2314 and publisher_order_type = activity_rec.order_type
2315 and key_date < p_start_date;
2316
2317 v_past_due_qty := l_base_qty + l_config_qty;
2318 end if;
2319
2320
2321 elsif myco_agg = COMPANY_AGG THEN
2322
2323 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2324 customer_id, tp_quantity,
2325 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2326 from msc_sup_dem_entries_ui_v
2327 where base_item_id = activity_rec.item_id
2328 and customer_id = activity_rec.customer_id
2329 and supplier_id = activity_rec.supplier_id
2330 and publisher_order_type = activity_rec.order_type
2331 and key_date < p_start_date;
2332
2333 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2334 customer_id, tp_quantity,
2335 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2336 from msc_sup_dem_entries_ui_v
2337 where inventory_item_id = activity_rec.item_id
2338 and customer_id = activity_rec.customer_id
2339 and supplier_id = activity_rec.supplier_id
2340 and publisher_order_type = activity_rec.order_type
2341 and key_date < p_start_date;
2342
2343 v_past_due_qty := l_base_qty + l_config_qty;
2344
2345 end if;
2346
2347
2348 elsif tpco_agg = ALL_AGG THEN
2349
2350 if myco_agg = ORG_AGG THEN
2351
2352 if(v_viewer_id = activity_rec.customer_id) then
2353
2354 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2355 customer_id, tp_quantity,
2356 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2357 from msc_sup_dem_entries_ui_v
2358 where base_item_id = activity_rec.item_id
2359 and customer_id = activity_rec.customer_id
2360 and customer_site_id = activity_rec.cust_site_id
2361 and publisher_order_type = activity_rec.order_type
2362 and key_date < p_start_date;
2363
2364
2365 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2366 customer_id, tp_quantity,
2367 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2368 from msc_sup_dem_entries_ui_v
2369 where inventory_item_id = activity_rec.item_id
2370 and customer_id = activity_rec.customer_id
2371 and customer_site_id = activity_rec.cust_site_id
2372 and publisher_order_type = activity_rec.order_type
2373 and key_date < p_start_date;
2374
2375 v_past_due_qty := l_base_qty + l_config_qty;
2376
2377 elsif(v_viewer_id = activity_rec.supplier_id) then
2378
2379 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2380 customer_id, tp_quantity,
2381 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2382 from msc_sup_dem_entries_ui_v
2383 where base_item_id = activity_rec.item_id
2384 and supplier_id = activity_rec.supplier_id
2385 and supplier_site_id = activity_rec.supp_site_id
2386 and publisher_order_type = activity_rec.order_type
2387 and key_date < p_start_date;
2388
2392 from msc_sup_dem_entries_ui_v
2389 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2390 customer_id, tp_quantity,
2391 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2393 where inventory_item_id = activity_rec.item_id
2394 and supplier_id = activity_rec.supplier_id
2395 and supplier_site_id = activity_rec.supp_site_id
2396 and publisher_order_type = activity_rec.order_type
2397 and key_date < p_start_date;
2398
2399 v_past_due_qty := l_base_qty + l_config_qty;
2400 end if;
2401
2402
2403 elsif myco_agg = COMPANY_AGG THEN
2404
2405 if(v_viewer_id = activity_rec.customer_id) then
2406
2407 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2408 customer_id, tp_quantity,
2409 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2410 from msc_sup_dem_entries_ui_v
2411 where base_item_id = activity_rec.item_id
2412 and customer_id = activity_rec.customer_id
2413 and publisher_order_type = activity_rec.order_type
2414 and key_date < p_start_date;
2415
2416
2417 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2418 customer_id, tp_quantity,
2419 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2420 from msc_sup_dem_entries_ui_v
2421 where inventory_item_id = activity_rec.item_id
2422 and customer_id = activity_rec.customer_id
2423 and publisher_order_type = activity_rec.order_type
2424 and key_date < p_start_date;
2425
2426 v_past_due_qty := l_base_qty + l_config_qty;
2427 elsif(v_viewer_id = activity_rec.supplier_id) then
2428
2429 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2430 customer_id, tp_quantity,
2431 supplier_id, tp_quantity, quantity)),0) into l_base_qty
2432 from msc_sup_dem_entries_ui_v
2433 where base_item_id = activity_rec.item_id
2434 and supplier_id = activity_rec.supplier_id
2435 and publisher_order_type = activity_rec.order_type
2436 and key_date < p_start_date;
2437
2438 select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
2439 customer_id, tp_quantity,
2440 supplier_id, tp_quantity, quantity)),0) into l_config_qty
2441 from msc_sup_dem_entries_ui_v
2442 where inventory_item_id = activity_rec.item_id
2443 and supplier_id = activity_rec.supplier_id
2444 and publisher_order_type = activity_rec.order_type
2445 and key_date < p_start_date;
2446
2447 v_past_due_qty := l_base_qty + l_config_qty;
2448
2449 end if;
2450
2451 end if;
2452
2453 end if;
2454
2455 return v_past_due_qty;
2456
2457 EXCEPTION
2458 WHEN OTHERS THEN
2459 arg_err_msg := ' calculate_past_due_quantity ' || SQLERRM;
2460 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2461 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'calculate_past_due_quantity', SQLERRM);
2462 end if;
2463 raise;
2464 END calc_past_due_qty ;
2465
2466 /**
2467 * The following procedure calculates the bucket qty
2468 * from the current record.
2469 * It aggregates/adds the current qty based on teh date into the appropriate
2470 * bucket pl/sql table.
2471 *
2472 * @param the position.
2473 */
2474 PROCEDURE calculate_bucket_data(arg_pos IN NUMBER) IS
2475 curr_cnt number := arg_pos;
2476
2477 v_nextbkt number := 0;
2478
2479
2480 BEGIN
2481
2482 -- check the onhand asn etc first.
2483 -- the order type is intransit or onhand then unbucketed value
2484 -- set the new_date to null
2485 -- NOTE : HERE NEED TO TAKE ONLY OPEN ASN's for intransit.
2486 IF (v_intransit > 0 AND activity_rec.order_type = INTRANSIT) OR
2487 -- (v_asn > 0 AND activity_rec.order_type = ASN) OR --- Commented for Bug # 6147428
2488 (v_alloc_onhand > 0 AND activity_rec.order_type = ALLOCATED_ONHAND) OR
2489 (v_unalloc_onhand > 0 AND activity_rec.order_type = UNALLOCATED_ONHAND) THEN
2490
2491
2492 var_qty_nobkt(curr_cnt) := var_qty_nobkt(curr_cnt) + activity_rec.new_quantity;
2493 unbucketed_flag := 1 ;
2494 activity_rec.new_date := null ;
2495
2496 END IF;
2497
2498
2499 -- check the bucket types of the record.
2500 if activity_rec.bucket_type = DAY_BUCKET then
2501 var_day_bkt(curr_cnt) := nvl(var_day_bkt(curr_cnt),0) + 1 ;
2502
2503 elsif activity_rec.bucket_type = WEEK_BUCKET then
2504 var_week_bkt(curr_cnt) := nvl(var_week_bkt(curr_cnt),0) + 1 ;
2505
2506 elsif activity_rec.bucket_type = MONTH_BUCKET then
2507 var_month_bkt(curr_cnt) := nvl(var_month_bkt(curr_cnt),0) + 1 ;
2508
2509 end if;
2510
2511 i := 0;
2512
2513
2514 if g_num_of_buckets > 0 and activity_rec.new_date is not null then
2515 for i in var_dates.FIRST..g_num_of_buckets loop
2516
2520
2517 curr_date := var_dates(i);
2518 v_nextbkt := 0;
2519
2521
2522 -- now if th value is less and if it is not th first record then
2523 -- add to the var_quantity (i-1).
2524 -- NOTE: if this is the first then there is no 0 record.
2525 IF i <> g_num_of_buckets THEN
2526 if activity_rec.new_date <> var_dates(i) AND
2527 activity_rec.new_date <> var_dates(i+1) THEN
2528
2529 if activity_rec.new_date > var_dates(i) AND
2530 activity_rec.new_date between var_dates(i) AND var_dates(i+1) THEN
2531
2532 v_nextbkt := 1;
2533 end if;
2534 end if;
2535 -- for the last bucket check to see if the key_date falls between the last bucket start and end date
2536 -- NOTE : v_last_bkt_date has starting date of last+1 bucket.
2537 ELSIF i = g_num_of_buckets THEN
2538 if activity_rec.new_date > var_dates(i) AND
2539 activity_rec.new_date between var_dates(i) AND v_last_bkt_date THEN
2540
2541 v_nextbkt := 1;
2542 end if;
2543 END IF;
2544
2545 -- if the dates are equal
2546 -- or if the above flag is set then
2547 -- add to theappropriate bucket
2548 -- for safety stock and PAB, do not aggregate (since it represents a level)
2549 -- for ss and pab, display the latest ss/pab value in that bucket.
2550 --Modifying the logic for not aggregating the PAB/SS Records..Bug 4200004
2551
2552
2553
2554 IF activity_rec.new_date = var_dates(i) OR v_nextbkt > 0 THEN
2555 if i = 1 then
2556
2557 calculate_bucket_qty_ss_pab(var_flag1,var_pub_id1,var_qty1(curr_cnt),var_temp1,var_temp_qty1,var_temp_order_type1 );
2558
2559 elsif i = 2 then
2560
2561 calculate_bucket_qty_ss_pab(var_flag2,var_pub_id2,var_qty2(curr_cnt),var_temp2,var_temp_qty2,var_temp_order_type2 );
2562
2563 elsif i = 3 then
2564
2565 calculate_bucket_qty_ss_pab(var_flag3,var_pub_id3,var_qty3(curr_cnt),var_temp3,var_temp_qty3,var_temp_order_type3 );
2566
2567 elsif i = 4 then
2568
2569 calculate_bucket_qty_ss_pab(var_flag4,var_pub_id4,var_qty4(curr_cnt),var_temp4,var_temp_qty4,var_temp_order_type4 );
2570
2571 elsif i = 5 then
2572
2573 calculate_bucket_qty_ss_pab(var_flag5,var_pub_id5,var_qty5(curr_cnt),var_temp5,var_temp_qty5,var_temp_order_type5 );
2574
2575
2576 elsif i = 6 then
2577
2578 calculate_bucket_qty_ss_pab(var_flag6,var_pub_id6,var_qty6(curr_cnt),var_temp6,var_temp_qty6,var_temp_order_type6 );
2579
2580 elsif i = 7 then
2581
2582 calculate_bucket_qty_ss_pab(var_flag7,var_pub_id7,var_qty7(curr_cnt),var_temp7,var_temp_qty7,var_temp_order_type7 );
2583
2584 elsif i = 8 then
2585
2586 calculate_bucket_qty_ss_pab(var_flag8,var_pub_id8,var_qty8(curr_cnt),var_temp8,var_temp_qty8,var_temp_order_type8 );
2587
2588 elsif i = 9 then
2589
2590 calculate_bucket_qty_ss_pab(var_flag9,var_pub_id9,var_qty9(curr_cnt),var_temp9,var_temp_qty9,var_temp_order_type9 );
2591
2592 elsif i = 10 then
2593
2594 calculate_bucket_qty_ss_pab(var_flag10,var_pub_id10,var_qty10(curr_cnt),var_temp10,var_temp_qty10,var_temp_order_type10 );
2595
2596 elsif i = 11 then
2597
2598 calculate_bucket_qty_ss_pab(var_flag11,var_pub_id11,var_qty11(curr_cnt),var_temp11,var_temp_qty11,var_temp_order_type11 );
2599
2600 elsif i = 12 then
2601
2602 calculate_bucket_qty_ss_pab(var_flag12,var_pub_id12,var_qty12(curr_cnt),var_temp12,var_temp_qty12,var_temp_order_type12 );
2603
2604 elsif i = 13 then
2605
2606 calculate_bucket_qty_ss_pab(var_flag13,var_pub_id13,var_qty13(curr_cnt),var_temp13,var_temp_qty13,var_temp_order_type13 );
2607
2608 elsif i = 14 then
2609
2610 calculate_bucket_qty_ss_pab(var_flag14,var_pub_id14,var_qty14(curr_cnt),var_temp14,var_temp_qty14,var_temp_order_type14 );
2611 elsif i = 15 then
2612
2613 calculate_bucket_qty_ss_pab(var_flag15,var_pub_id15,var_qty15(curr_cnt),var_temp15,var_temp_qty15,var_temp_order_type15 );
2614 elsif i = 16 then
2615
2616 calculate_bucket_qty_ss_pab(var_flag16,var_pub_id16,var_qty16(curr_cnt),var_temp16,var_temp_qty16,var_temp_order_type16 );
2617 elsif i = 17 then
2618
2619 calculate_bucket_qty_ss_pab(var_flag17,var_pub_id17,var_qty17(curr_cnt),var_temp17,var_temp_qty17,var_temp_order_type17 );
2620 elsif i = 18 then
2621
2622 calculate_bucket_qty_ss_pab(var_flag18,var_pub_id18,var_qty18(curr_cnt),var_temp18,var_temp_qty18,var_temp_order_type18 );
2623 elsif i = 19 then
2624
2625 calculate_bucket_qty_ss_pab(var_flag19,var_pub_id19,var_qty19(curr_cnt),var_temp19,var_temp_qty19,var_temp_order_type19 );
2626 elsif i = 20 then
2627
2631 calculate_bucket_qty_ss_pab(var_flag21,var_pub_id21,var_qty21(curr_cnt),var_temp21,var_temp_qty21,var_temp_order_type21 );
2628 calculate_bucket_qty_ss_pab(var_flag20,var_pub_id20,var_qty20(curr_cnt),var_temp20,var_temp_qty20,var_temp_order_type20 );
2629 elsif i = 21 then
2630
2632 elsif i = 22 then
2633
2634 calculate_bucket_qty_ss_pab(var_flag22,var_pub_id22,var_qty22(curr_cnt),var_temp22,var_temp_qty22,var_temp_order_type22 );
2635 elsif i = 23 then
2636
2637 calculate_bucket_qty_ss_pab(var_flag23,var_pub_id23,var_qty23(curr_cnt),var_temp23,var_temp_qty23,var_temp_order_type23 );
2638 elsif i = 24 then
2639
2640 calculate_bucket_qty_ss_pab(var_flag24,var_pub_id24,var_qty24(curr_cnt),var_temp24,var_temp_qty24,var_temp_order_type24 );
2641 elsif i = 25 then
2642
2643 calculate_bucket_qty_ss_pab(var_flag25,var_pub_id25,var_qty25(curr_cnt),var_temp25,var_temp_qty25,var_temp_order_type25 );
2644 elsif i = 26 then
2645
2646 calculate_bucket_qty_ss_pab(var_flag26,var_pub_id26,var_qty26(curr_cnt),var_temp26,var_temp_qty26,var_temp_order_type26 );
2647 elsif i = 27 then
2648
2649 calculate_bucket_qty_ss_pab(var_flag27,var_pub_id27,var_qty27(curr_cnt),var_temp27,var_temp_qty27,var_temp_order_type27 );
2650 elsif i = 28 then
2651
2652 calculate_bucket_qty_ss_pab(var_flag28,var_pub_id28,var_qty28(curr_cnt),var_temp28,var_temp_qty28,var_temp_order_type28 );
2653
2654 elsif i = 29 then
2655
2656 calculate_bucket_qty_ss_pab(var_flag29,var_pub_id29,var_qty29(curr_cnt),var_temp29,var_temp_qty29,var_temp_order_type29 );
2657 elsif i = 30 then
2658
2659 calculate_bucket_qty_ss_pab(var_flag30,var_pub_id30,var_qty30(curr_cnt),var_temp30,var_temp_qty30,var_temp_order_type30 );
2660 elsif i = 31 then
2661
2662 calculate_bucket_qty_ss_pab(var_flag31,var_pub_id31,var_qty31(curr_cnt),var_temp31,var_temp_qty31,var_temp_order_type31 );
2663 elsif i = 32 then
2664
2665 calculate_bucket_qty_ss_pab(var_flag32,var_pub_id32,var_qty32(curr_cnt),var_temp32,var_temp_qty32,var_temp_order_type32 );
2666 elsif i = 33 then
2667
2668 calculate_bucket_qty_ss_pab(var_flag33,var_pub_id33,var_qty33(curr_cnt),var_temp33,var_temp_qty33,var_temp_order_type33 );
2669 elsif i = 34 then
2670
2671 calculate_bucket_qty_ss_pab(var_flag34,var_pub_id34,var_qty34(curr_cnt),var_temp34,var_temp_qty34,var_temp_order_type34 );
2672 elsif i = 35 then
2673
2674 calculate_bucket_qty_ss_pab(var_flag35,var_pub_id35,var_qty35(curr_cnt),var_temp35,var_temp_qty35,var_temp_order_type35 );
2675 elsif i = 36 then
2676
2677 calculate_bucket_qty_ss_pab(var_flag36,var_pub_id36,var_qty36(curr_cnt),var_temp36,var_temp_qty36,var_temp_order_type36 );
2678
2679 end if;
2680 END IF;
2681
2682 end loop;
2683 end if;
2684
2685 -- commit;
2686 EXCEPTION
2687 when others then
2688 arg_err_msg := arg_err_msg || ' calc bkt data' || SQLERRM;
2689 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2690 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'calculate_bucket_data', SQLERRM);
2691 end if;
2692 raise;
2693 END calculate_bucket_data;
2694
2695 /**
2696 * The following function builds the sql statement
2697 * that fetches the data from the sup_dem table via the hz_v.
2698 * This is a dynamic sql
2699 *
2700 * @param the order type
2701 * @return the sql string
2702 */
2703 Function prepare_sql(v_order IN NUMBER) RETURN VARCHAR2
2704 IS
2705 l_statement VARCHAR2(4000);
2706 l_comcom VARCHAR2(125);
2707 l_orgorg VARCHAR2(275);
2708 l_orders VARCHAR2(100);
2709
2710 l_date VARCHAR2(20) := to_char(trunc(nvl(arg_from_date, p_start_date)),'MM-DD-YYYY');
2711 l_last VARCHAR2(20) := to_char(trunc(nvl(v_last_bkt_date, sysdate)), 'MM-DD-YYYY');
2712 l_order_group VARCHAR2(1000);
2713 l_item_id NUMBER;
2714 l_query_id NUMBER;
2715 l_sup_site VARCHAR2(300);
2716
2717 CURSOR category_items(arg_category_name VARCHAR2)
2718 IS
2719 SELECT distinct inventory_item_id
2720 FROM msc_item_categories
2721 where category_name = arg_category_name
2722 and category_set_id = FND_PROFILE.VALUE('MSCX_CP_HZ_CATEGORY_SET');
2723
2724 BEGIN
2725
2726
2727 -- insert all the items of the category into temp table msc_form_query.
2728 -- store all the item ids in number1 column of msc_form_query temp table.
2729 if (v_category is not null) then
2730 select msc_form_query_s.nextval into l_query_id from dual;
2731 OPEN category_items(v_category);
2732 LOOP
2733 FETCH category_items into l_item_id;
2734 EXIT WHEN category_items%NOTFOUND;
2735 INSERT INTO msc_form_query
2736 (
2740 REQUEST_ID,CHAR1,CHAR2,CHAR3,CHAR4,CHAR5,CHAR6,CHAR7,CHAR8,CHAR9,PROGRAM_UPDATE_DATE,
2737 QUERY_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
2738 DATE1,DATE2,DATE3,DATE4,DATE5,DATE6,DATE7,DATE8,NUMBER1,NUMBER2,NUMBER3,NUMBER4,NUMBER5,
2739 NUMBER6,NUMBER7,NUMBER8,NUMBER9,NUMBER10,NUMBER11,NUMBER12,NUMBER13,NUMBER14,NUMBER15,
2741 PROGRAM_APPLICATION_ID,PROGRAM_ID,NUMBER16,CHAR10,CHAR11,CHAR12,CHAR13,CHAR14,CHAR15 )
2742 VALUES
2743 (
2744 l_query_id,sysdate,fnd_profile.value('USER_ID'),sysdate,fnd_profile.value('USER_ID'),null,
2745 null,null,null,null,null,null,null,null, l_item_id,null,null,null,null,
2746 null,null,null,null,null,null,null,null,null,null,
2747 null,null,null,null,null,null,null,null,null,null,null,
2748 null,null,null,null,null,null,null,null,null
2749 );
2750 END LOOP;
2751 CLOSE category_items;
2752 end if;
2753
2754 if v_order IS NULL or v_order = 0 then
2755 l_orders := v_sales_forecast || ',' || v_order_forecast || ',' || v_supply_commit ;
2756 l_orders := l_orders || ',' || v_returns_forecast ||',' || v_def_outbound_shipment ; -- bug#6893383
2757 l_orders := l_orders || ',' || v_hist_sales || ',' || v_sell_thru_fcst || ',' || v_negotiated_capacity ;
2758 l_orders := l_orders || ',' || v_safety_stock || ',' || v_proj_avail_bal || ',' || v_proj_safety_stock ;
2759 l_orders := l_orders || ',' || v_alloc_onhand || ',' || v_unalloc_onhand ;
2760 l_orders := l_orders || ',' || v_proj_unalloc_avl_bal || ',' || v_proj_alloc_avl_bal ;
2761 l_orders := l_orders || ',' || v_purchase_order || ',' || v_sales_order || ',' || v_asn ;
2762 l_orders := l_orders || ',' || v_shipment_receipt || ',' || v_intransit || ',' || v_work_order || ',' || v_po_ack ;
2763 l_orders := l_orders || ',' || v_replenishment || ',' || v_requisition ;
2764 l_orders := l_orders || ',' || v_po_from_plan || ',' || v_released_plan || ',' || v_planned_order ;
2765 else
2766 l_orders := v_order ;
2767 end if;
2768
2769 l_statement := 'SELECT nvl(';
2770 -- publisher name should not be used in order by relation_group.
2771 l_order_group := ' nvl(';
2772
2773 /* -- Bug# 4199827 -- Added nvl to tp_company and tp_site so that viewer can view data according to
2774 sites based on Preference Set for OnHand , SS, PAB */
2775
2776 if tpco_agg = ORG_AGG THEN
2777
2778 if myco_agg = ORG_AGG THEN
2779
2780 l_statement := l_statement || 'decode(third_party_flag,0,';
2781 l_statement := l_statement || 'least(viewer_company||viewer_site,nvl(tp_company,-1)||nvl(tp_site,-1))|| ' ;
2782 l_statement := l_statement || 'greatest(viewer_company||viewer_site,nvl(tp_company,-1)||nvl(tp_site,-1)),';
2783 l_statement := l_statement || 'viewer_company||viewer_site||nvl(tp_company,-1)||nvl(tp_site,-1)|| decode(publisher_order_type, 1, publisher_name, ''''))';
2784
2785 l_order_group := l_order_group || 'decode(third_party_flag,0,';
2786 l_order_group := l_order_group || 'least(viewer_company||viewer_site,nvl(tp_company,-1)||nvl(tp_site,-1))|| ' ;
2787 l_order_group := l_order_group || 'greatest(viewer_company||viewer_site,nvl(tp_company,-1)||nvl(tp_site,-1)),';
2788 l_order_group := l_order_group || 'viewer_company||viewer_site||nvl(tp_company,-1)||nvl(tp_site,-1))';
2789
2790 elsif myco_agg = COMPANY_AGG THEN
2791 l_statement := l_statement || 'decode(third_party_flag,0,';
2792 l_statement := l_statement || 'least(viewer_company,nvl(tp_company,-1)||nvl(tp_site,-1))||';
2793 l_statement := l_statement || 'greatest(viewer_company,nvl(tp_company,-1)||nvl(tp_site,-1)) ';
2794 l_statement := l_statement || ',viewer_company||nvl(tp_company,-1)||nvl(tp_site,-1)||decode(publisher_order_type, 1, publisher_name, ''''))';
2795
2796 l_order_group := l_order_group || 'decode(third_party_flag,0,';
2797 l_order_group := l_order_group || 'least(viewer_company,nvl(tp_company,-1)||nvl(tp_site,-1))||';
2798 l_order_group := l_order_group || 'greatest(viewer_company,nvl(tp_company,-1)||nvl(tp_site,-1)) ';
2799 l_order_group := l_order_group || ',viewer_company||nvl(tp_company,-1)||nvl(tp_site,-1))';
2800 end if;
2801
2802 elsif tpco_agg = COMPANY_AGG THEN
2803
2804 if myco_agg = ORG_AGG THEN
2805 l_statement := l_statement || 'decode(third_party_flag,0,';
2806 l_statement := l_statement || 'least(viewer_company||viewer_site,nvl(tp_company,-1))||';
2807 l_statement := l_statement || 'greatest(viewer_company||viewer_site,nvl(tp_company,-1)),';
2808 l_statement := l_statement || 'viewer_company||viewer_site||nvl(tp_company,-1)||decode(publisher_order_type, 1, publisher_name, ''''))';
2809
2810 l_order_group := l_order_group || 'decode(third_party_flag,0,';
2811 l_order_group := l_order_group || 'least(viewer_company||viewer_site,nvl(tp_company,-1))||';
2812 l_order_group := l_order_group || 'greatest(viewer_company||viewer_site,nvl(tp_company,-1)),';
2813 l_order_group := l_order_group || 'viewer_company||viewer_site||nvl(tp_company,-1))';
2814
2815 elsif myco_agg = COMPANY_AGG THEN
2816 l_statement := l_statement || 'decode(third_party_flag,0,';
2820
2817 l_statement := l_statement || 'least(viewer_company,nvl(tp_company,-1))||';
2818 l_statement := l_statement || 'greatest(viewer_company,nvl(tp_company,-1)),';
2819 l_statement := l_statement || 'viewer_company||nvl(tp_company,-1)||decode(publisher_order_type, 1, publisher_name, ''''))';
2821 l_order_group := l_order_group || 'decode(third_party_flag,0,';
2822 l_order_group := l_order_group || 'least(viewer_company,nvl(tp_company,-1))||';
2823 l_order_group := l_order_group || 'greatest(viewer_company,nvl(tp_company,-1)),';
2824 l_order_group := l_order_group || 'viewer_company||nvl(tp_company,-1))';
2825 end if;
2826
2827
2828 elsif tpco_agg = ALL_AGG THEN
2829
2830 if myco_agg = ORG_AGG THEN
2831 l_statement := l_statement || 'decode(third_party_flag,0,least(viewer_company||viewer_site, ';
2832 l_statement := l_statement || 'FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL''))|| ';
2833 l_statement := l_statement || 'greatest(viewer_company||viewer_site,';
2834 l_statement := l_statement || 'FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')),';
2835 l_statement := l_statement || 'decode(viewer_company, customer_name, viewer_company||viewer_site||FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')';
2836 l_statement := l_statement || ', FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')||viewer_company||viewer_site))';
2837
2838 l_order_group := l_order_group || 'decode(third_party_flag,0,least(viewer_company||viewer_site, ';
2839 l_order_group := l_order_group || 'FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL''))|| ';
2840 l_order_group := l_order_group || 'greatest(viewer_company||viewer_site,';
2841 l_order_group := l_order_group || 'FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')),';
2842 l_order_group := l_order_group || 'decode(viewer_company, customer_name, viewer_company||viewer_site||FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')';
2843 l_order_group := l_order_group || ', FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')||viewer_company||viewer_site))';
2844
2845 elsif myco_agg = COMPANY_AGG THEN
2846 l_statement := l_statement || 'decode(third_party_flag,0,least(viewer_company,';
2847 l_statement := l_statement || 'FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL''))|| ';
2848 l_statement := l_statement || 'greatest(viewer_company,';
2849 l_statement := l_statement || 'FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')),';
2850 l_statement := l_statement || 'decode(viewer_company, customer_name, viewer_company||FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')';
2851 l_statement := l_statement || ', FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')||viewer_company))';
2852
2853 l_order_group := l_order_group || 'decode(third_party_flag,0,least(viewer_company,';
2854 l_order_group := l_order_group || 'FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL''))|| ';
2855 l_order_group := l_order_group || 'greatest(viewer_company,';
2856 l_order_group := l_order_group || 'FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')),';
2857 l_order_group := l_order_group || 'decode(viewer_company, customer_name, viewer_company||FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')';
2858 l_order_group := l_order_group || ', FND_MESSAGE.GET_STRING(''MSC'',''MSC_X_HZ_TP_ALL'')||viewer_company))';
2859 end if;
2860
2861 end if;
2862
2863 l_statement := l_statement || ', ''NA'') REL,';
2864 l_order_group := l_order_group || ', ''NA'') ORDER_REL,';
2865 l_statement := l_statement || l_order_group ;
2866 l_statement := l_statement || 'PUBLISHER_NAME,PUBLISHER_SITE_NAME, '; -- INVENTORY_ITEM_ID,';
2867 l_statement := l_statement || ' decode (publisher_order_type, 13, NVL(BASE_ITEM_ID, INVENTORY_ITEM_ID), ';
2868 l_statement := l_statement || ' 14, NVL(BASE_ITEM_ID, INVENTORY_ITEM_ID),';
2869 l_statement := l_statement || ' 20, NVL(BASE_ITEM_ID, INVENTORY_ITEM_ID),';
2870 l_statement := l_statement || ' 22, NVL(BASE_ITEM_ID, INVENTORY_ITEM_ID),';
2871 l_statement := l_statement || ' 23, NVL(BASE_ITEM_ID, INVENTORY_ITEM_ID),';
2872 l_statement := l_statement || ' 24, NVL(BASE_ITEM_ID, INVENTORY_ITEM_ID),';
2873 l_statement := l_statement || 'INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,';
2874
2875 -- for order types : po, so, req, po from plan, released plan and planned order
2876 -- aggregate by the base item name (at the model level) and for the rest of the
2877 -- order types aggregate at the config or item level.
2878 l_statement := l_statement || 'decode (publisher_order_type, 13, NVL(BASE_ITEM_NAME, ITEM_NAME), ';
2879 l_statement := l_statement || '14, NVL(BASE_ITEM_NAME, ITEM_NAME),';
2880 l_statement := l_statement || '20, NVL(BASE_ITEM_NAME, ITEM_NAME),';
2881 l_statement := l_statement || '22, NVL(BASE_ITEM_NAME, ITEM_NAME),';
2882 l_statement := l_statement || '23, NVL(BASE_ITEM_NAME, ITEM_NAME),';
2883 l_statement := l_statement || '24, NVL(BASE_ITEM_NAME, ITEM_NAME),';
2884 l_statement := l_statement || 'ITEM_NAME) ITEM_NAME,';
2885 --l_statement := l_statement || 'NVL(ITEM_NAME,INVENTORY_ITEM_ID) ITEM_NAME, ';
2886 l_statement := l_statement || ' ITEM_DESCRIPTION,SUPPLIER_ITEM_NAME, ';
2890 l_statement := l_statement || 'SHIPPING_CONTROL,';
2887 l_statement := l_statement || 'SUPPLIER_NAME,CUSTOMER_NAME,';
2888 l_statement := l_statement || 'SUPPLIER_SITE_NAME,CUSTOMER_SITE_NAME,';
2889 l_statement := l_statement || 'PUBLISHER_ORDER_TYPE,PUBLISHER_ORDER_TYPE_DESC,';
2891 l_statement := l_statement || 'decode(sys_context(''MSC'',''COMPANY_ID''),publisher_id,nvl(primary_uom,uom_code),';
2892 l_statement := l_statement || 'customer_id,tp_uom_code,supplier_id,tp_uom_code,uom_code) UOM,';
2893 l_statement := l_statement || 'decode(sys_context(''MSC'',''COMPANY_ID''),publisher_id,decode(publisher_order_type, 7, decode(primary_quantity, 0, -99999999, primary_quantity), ';
2894 l_statement := l_statement || '27, decode(primary_quantity, 0, -99999999, primary_quantity), primary_quantity),';
2895 l_statement := l_statement || 'customer_id,decode(publisher_order_type, 7, decode(tp_quantity, 0, -99999999, tp_quantity), ';
2896 l_statement := l_statement || '27, decode(tp_quantity, 0, -99999999, tp_quantity), tp_quantity), ' ;
2897 l_statement := l_statement || 'supplier_id,decode(publisher_order_type, 7, decode(tp_quantity, 0, -99999999, tp_quantity), ';
2898 l_statement := l_statement || '27, decode(tp_quantity, 0, -99999999, tp_quantity), tp_quantity), ';
2899 l_statement := l_statement || 'decode(publisher_order_type, 7, decode(quantity, 0, -99999999, quantity), ';
2900 l_statement := l_statement || '27, decode(quantity, 0, -99999999, quantity), quantity)) QUANTITY,';
2901 l_statement := l_statement || 'KEY_DATE,SUPPLIER_ID,CUSTOMER_ID,';
2902 l_statement := l_statement || 'SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,';
2903 l_statement := l_statement || 'THIRD_PARTY_FLAG,VIEWER_COMPANY,nvl(TP_COMPANY,-1),';
2904 l_statement := l_statement || 'nvl(BUCKET_TYPE,' || DAY_BUCKET || ')';
2905 l_statement := l_statement || ',PUBLISHER_ID,PUBLISHER_SITE_ID';
2906 l_statement := l_statement || ' FROM msc_sup_dem_entries_ui_v';
2907 l_statement := l_statement || ' WHERE plan_id = -1 AND PUBLISHER_ORDER_TYPE IN (';
2908 l_statement := l_statement || l_orders;
2909 l_statement := l_statement || ') ' ;
2910 l_statement := l_statement || ' AND KEY_DATE >= decode(publisher_order_type, 13, KEY_DATE, 14, KEY_DATE, 15, KEY_DATE, to_date(''' || l_date || ''', ''MM-DD-YYYY'')) ';
2911 l_statement := l_statement || ' AND KEY_DATE < decode(publisher_order_type, 13, KEY_DATE+1, 14, KEY_DATE+1, 15, KEY_DATE+1, to_date(''' || l_last || ''', ''MM-DD-YYYY'')) ';
2912
2913 if arg_where_clause is not null then
2914 -- here remove the reading clause from the arg where clause if it is OR
2915 if instr(arg_where_clause, 'OR') > 0 and instr(arg_where_clause, 'OR') < 5 then
2916 l_statement := l_statement || ' AND (' || substr(arg_where_clause, instr(arg_where_clause, 'OR') + 2) || ')';
2917 else
2918 l_statement := l_statement || ' ' || arg_where_clause;
2919 end if;
2920 end if;
2921
2922 if (v_category is not null) then
2923 l_statement := l_statement || ' and inventory_item_id in (select number1 from msc_form_query where query_id = ' || l_query_id || ')';
2924 end if;
2925
2926 l_statement := l_statement || ' ORDER BY ITEM_NAME ' || arg_item_sort || ' , REL ,';
2927 l_statement := l_statement || 'PUBLISHER_ORDER_TYPE,PUBLISHER_NAME,PUBLISHER_SITE_NAME,';
2928 l_statement := l_statement || 'nvl(TP_COMPANY,-1),nvl(TP_SITE,-1),KEY_DATE ';
2929
2930 return l_statement;
2931
2932 EXCEPTION
2933 WHEN OTHERS THEN
2934 arg_err_msg := ' prepare_sql ' || SQLERRM || ' ' || l_statement;
2935 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2936 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'prepare_sql', SQLERRM);
2937 end if;
2938 raise;
2939 END prepare_sql ;
2940
2941
2942 /**
2943 * the following procedure adds the companion row if required.
2944 * 1. check if both the order forecast and supply commit are chosen in the user prefs.
2945 * 2. select for each item if an order forecast is present where the user company
2946 * is not the doc owner.
2947 * 3. check if corresponding supply commit is present.
2948 * 4. if not add an editable row with 0 as the value for all columns
2949 * and user company as the publisher of the doc.
2950 * 5. set editable flag to editable.
2951 */
2952 PROCEDURE add_companion_row(arg_query_id IN NUMBER) IS
2953
2954 v_name VARCHAR2(250);
2955 v_next_item number;
2956
2957 BEGIN
2958
2959
2960 if (v_supply_commit > 0 AND v_order_forecast > 0)
2961 AND -- user pref has homogeneous buckets
2962 (
2963 ( daily_bucket_count > 0 and weekly_bucket_count = 0 and period_bucket_count = 0) OR
2964 ( daily_bucket_count = 0 and weekly_bucket_count > 0 and period_bucket_count = 0) OR
2965 ( daily_bucket_count = 0 and weekly_bucket_count = 0 and period_bucket_count > 0 )
2966 )
2967 AND -- user aggregation is at site
2968 ( myco_agg = ORG_AGG and tpco_agg = ORG_AGG )
2969 THEN
2970
2971 -- check if there are any supply commmits for which no order forecast has been posted.
2972 -- where user is the customer.
2973 -- want to insert OF where user is customer and supplier has posted supply commit.
2974 BEGIN
2978 a.order_type_rank,a.uom,a.cust_item,a.sup_item,a.cust_item_desc,
2975 SELECT a.relation_group,a.order_relation_group,a.inventory_item_id,a.item_name,a.item_description,
2976 a.supplier_id,a.customer_id,a.supplier_site_id,a.customer_site_id,
2977 a.supplier_name,a.customer_name,a.supplier_org_code,a.customer_org_code,
2979 a.sup_item_desc,a.tp_uom,a.owner_item,a.owner_item_desc
2980 BULK COLLECT INTO
2981 var_relation, var_order_relation,var_item_id, var_item_name, var_item_name_desc,
2982 var_supplier_id, var_customer_id, var_supplier_site_id,
2983 var_customer_site_id, var_supplier, var_customer, var_supplier_org,
2984 var_customer_org, var_order_rank, var_uom, var_cust_item, var_sup_item,
2985 var_cust_item_desc, var_sup_item_desc, var_tp_uom, var_owner_item,
2986 var_owner_item_desc
2987 FROM msc_hz_ui_lines a
2988 WHERE a.query_id = arg_query_id
2989 AND a.order_type = SUPPLY_COMMIT
2990 AND a.customer_id = sys_context('MSC','COMPANY_ID')
2991 AND a.bucket_type <> 0
2992 AND ( period_bucket_count > 0
2993 OR ( weekly_bucket_count > 0 and a.bucket_type <> MONTH_BUCKET ) -- cannot be month
2994 OR ( daily_bucket_count > 0 and a.bucket_type not in (MONTH_BUCKET, WEEK_BUCKET) )
2995 )
2996 AND not exists
2997 (select * from msc_sup_dem_entries b
2998 where b.publisher_order_type = ORDER_FORECAST_CST
2999 and b.inventory_item_id = a.inventory_item_id
3000 and b.supplier_id = a.supplier_id
3001 and b.customer_id = a.customer_id
3002 -- make companion row to the site level
3003 and b.supplier_site_id = a.supplier_site_id
3004 and b.customer_site_id = a.customer_site_id
3005 and b.publisher_name = a.customer_name
3006 and b.key_date >= p_start_date
3007 and b.key_date <= v_last_bkt_date
3008
3009 )
3010 ORDER BY a.item_name,a.relation_group ;
3011
3012 -- if the above is not null then add data into the pl/sql tables
3013 -- from (publisher) is the customer (user's company)
3014 -- order type is order forecast
3015 -- order type desc is ORDER_FORECAST
3016 -- customer, item and supplier remain the same.
3017 -- the order type rank is order type rank +1
3018
3019 v_name := get_lookup_name('MSC_X_ORDER_TYPE',ORDER_FORECAST_CST) ;
3020
3021 if var_relation is not null and var_relation.COUNT > 0 then
3022
3023 if v_o_forecast > v_o_supply_commit then
3024 v_next_item := 2;
3025 else
3026 v_next_item := 1;
3027 end if;
3028
3029 forall i in var_relation.FIRST..var_relation.LAST
3030
3031 INSERT INTO msc_hz_ui_lines
3032 (LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
3033 ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
3034 UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
3035 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,
3036 INVENTORY_ITEM_ID,SUP_ITEM,CUST_ITEM,SUP_ITEM_DESC,CUST_ITEM_DESC,
3037 OWNER_ITEM,OWNER_ITEM_DESC,TP_UOM,UOM_CODE,
3038 QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
3039 QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
3040 QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
3041 QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
3042 QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
3043 QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
3044 QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
3045 QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
3046 OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
3047 OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
3048 OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
3049 OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
3050 OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
3051 OLD_QTY36,EDITABLE_FLAG,BUCKET_TYPE,PUBLISHER_ID,PUBLISHER_SITE_ID,
3052 next_item,unbucketed_qty)
3053 VALUES
3054 (msc_x_hz_ui_line_id_s.nextval,arg_query_id,nvl(var_relation(i),'NA'),nvl(var_order_relation(i),'NA'),
3055 sys_context('MSC','COMPANY_NAME'),var_customer_org(i), var_item_name(i),
3056 var_item_name_desc(i),v_o_forecast, ORDER_FORECAST_CST, v_name,
3057 var_tp_uom(i), var_supplier(i), var_customer(i), var_supplier_org(i),
3061 var_owner_item(i), var_owner_item_desc(i),var_uom(i), var_tp_uom(i),
3058 var_customer_org(i),var_supplier_id(i),var_customer_id(i),
3059 var_supplier_site_id(i),var_customer_site_id(i), var_item_id(i),
3060 var_sup_item(i),var_cust_item(i),var_sup_item_desc(i),var_cust_item_desc(i),
3062 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
3063 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
3064 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
3065 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
3066 0,decode(period_bucket_count,0,
3067 (decode(weekly_bucket_count,0,DAY_BUCKET,WEEK_BUCKET)),
3068 MONTH_BUCKET),
3069 var_customer_id(i),var_customer_site_id(i),v_next_item,0) ;
3070 end if;
3071 EXCEPTION
3072 when others then
3073 arg_err_msg := arg_err_msg || ' Add companion row ' || SQLERRM;
3074 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3075 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'add_companion_row', SQLERRM);
3076 end if;
3077 null;
3078 END ;
3079
3080
3081 -- check if there are any order forecast with no supply commit posted.
3082 -- where user is the supplier
3083 -- want to insert Supply Commit where user is supplier and customer has posted OF.
3084 BEGIN
3085 SELECT a.relation_group,a.order_relation_group,a.inventory_item_id,a.item_name,a.item_description,
3086 a.supplier_id,a.customer_id,a.supplier_site_id,a.customer_site_id,
3087 a.supplier_name,a.customer_name,a.supplier_org_code,a.customer_org_code,
3088 a.order_type_rank,a.uom,a.cust_item,a.sup_item,a.cust_item_desc,
3089 a.sup_item_desc,a.tp_uom,a.owner_item,a.owner_item_desc
3090 BULK COLLECT INTO
3091 var_relation, var_order_relation,var_item_id, var_item_name, var_item_name_desc,
3092 var_supplier_id, var_customer_id, var_supplier_site_id,
3093 var_customer_site_id, var_supplier, var_customer, var_supplier_org,
3094 var_customer_org, var_order_rank, var_uom, var_cust_item, var_sup_item,
3095 var_cust_item_desc, var_sup_item_desc, var_tp_uom, var_owner_item,
3096 var_owner_item_desc
3097 FROM msc_hz_ui_lines a
3098 WHERE a.query_id = arg_query_id
3099 AND a.order_type = ORDER_FORECAST_CST
3100 AND a.supplier_id = sys_context('MSC','COMPANY_ID')
3101 AND a.bucket_type <> 0
3102 AND ( period_bucket_count > 0
3103 OR ( weekly_bucket_count > 0 and a.bucket_type <> MONTH_BUCKET ) -- cannot be month
3104 OR ( daily_bucket_count > 0 and a.bucket_type not in (MONTH_BUCKET, WEEK_BUCKET) )
3105 )
3106 AND not exists
3107 (select * from msc_sup_dem_entries b
3108 where b.publisher_order_type = SUPPLY_COMMIT
3109 and b.inventory_item_id = a.inventory_item_id
3110 and b.supplier_id = a.supplier_id
3111 and b.customer_id = a.customer_id
3112 -- make companion row to the site level
3113 and b.supplier_site_id = a.supplier_site_id
3114 and b.customer_site_id = a.customer_site_id
3115 and b.publisher_name = a.supplier_name
3116 and b.key_date >= p_start_date
3117 and b.key_date <= v_last_bkt_date
3118 )
3119 ORDER BY a.item_name,a.relation_group ;
3120
3121 -- if the above is not null then add data into the pl/sql tables
3122 -- from (publisher) is the customer (user's company)
3123 -- order type is order forecast
3124 -- order type desc is SUPPLY_COMMIT
3125 -- customer, item and supplier remain the same.
3126 -- the order type rank is order type rank +1
3127
3128 v_name := get_lookup_name('MSC_X_ORDER_TYPE',SUPPLY_COMMIT) ;
3129
3130 if var_relation is not null and var_relation.COUNT > 0 then
3131
3132 if v_o_forecast > v_o_supply_commit then
3133 v_next_item := 1;
3134 else
3135 v_next_item := 2;
3136 end if;
3137
3138 forall i in var_relation.FIRST..var_relation.LAST
3139 INSERT INTO msc_hz_ui_lines
3140 (LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
3141 ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
3142 UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
3143 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,
3147 QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
3144 INVENTORY_ITEM_ID,SUP_ITEM,CUST_ITEM,SUP_ITEM_DESC,CUST_ITEM_DESC,
3145 OWNER_ITEM,OWNER_ITEM_DESC,TP_UOM,UOM_CODE,
3146 QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
3148 QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
3149 QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
3150 QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
3151 QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
3152 QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
3153 QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
3154 OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
3155 OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
3156 OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
3157 OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
3158 OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
3159 OLD_QTY36,EDITABLE_FLAG,BUCKET_TYPE,PUBLISHER_ID,PUBLISHER_SITE_ID,
3160 next_item, unbucketed_qty)
3161 VALUES
3162 (msc_x_hz_ui_line_id_s.nextval,arg_query_id,nvl(var_relation(i),'NA'),nvl(var_order_relation(i),'NA'),
3163 sys_context('MSC','COMPANY_NAME'),var_supplier_org(i), var_item_name(i),
3164 var_item_name_desc(i),v_o_supply_commit, SUPPLY_COMMIT, v_name,
3165 var_tp_uom(i),var_supplier(i),var_customer(i),var_supplier_org(i),
3166 var_customer_org(i),var_supplier_id(i),var_customer_id(i),
3167 var_supplier_site_id(i),var_customer_site_id(i), var_item_id(i),
3168 var_sup_item(i), var_cust_item(i),var_sup_item_desc(i),var_cust_item_desc(i),
3169 var_owner_item(i),var_owner_item_desc(i),var_uom(i), var_tp_uom(i),
3170 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
3171 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
3172 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
3173 null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
3174 0,decode(period_bucket_count,0,
3175 (decode(weekly_bucket_count,0,DAY_BUCKET,WEEK_BUCKET)),
3176 MONTH_BUCKET),
3177 var_supplier_id(i),var_supplier_site_id(i),v_next_item,0) ;
3178 end if;
3179 EXCEPTION
3180 when others then
3181 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3182 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'add_companion_row', SQLERRM);
3183 end if;
3184 null;
3185 END ;
3186
3187 end if;
3188
3189
3190 EXCEPTION
3191 when others then
3192 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3193 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'add_companion_row', SQLERRM);
3194 end if;
3195
3196 END add_companion_row ;
3197
3198
3199 /**
3200 * The foll procedure check if the prev item and the curr item
3201 * are one and the same.
3202 * values
3203 * 2 - if the current item is the same as the prev one
3204 * 1 - if the current item is not the same as the prev one.
3205 *
3206 * This value is used on the UI to not display the item name
3207 * redundantly .
3208 *
3209 * @param the query id of the result set.
3210 */
3211 PROCEDURE correct_next_item(arg_query_id IN NUMBER) IS
3212 i number := 0;
3213 var_line_id num;
3214 BEGIN
3215 SELECT a.inventory_item_id, a.line_id
3216 BULK COLLECT INTO var_item_id, var_line_id
3217 FROM msc_hz_ui_lines a
3218 WHERE a.query_id = arg_query_id
3219 ORDER BY item_name, order_relation_group, order_type_rank ;
3220
3221 FOR i in var_item_id.FIRST..var_item_id.LAST loop
3222
3223 if i = var_item_id.FIRST then
3224 var_next_item(i) := 1;
3225 else
3226 if var_item_id(i) = var_item_id(i-1) then
3227 var_next_item(i) := 2;
3228 else
3229 var_next_item(i) := 1;
3230 end if;
3231 end if;
3232
3233 END LOOP;
3234
3235 -- now do bulk update
3236 FORALL i in var_line_id.FIRST..var_line_id.LAST
3237 UPDATE msc_hz_ui_lines
3238 SET next_item = var_next_item(i)
3239 WHERE query_id = arg_query_id
3240 AND line_id = var_line_id(i);
3241 EXCEPTION
3242 when others then
3243 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3247
3244 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'correct_next_item', SQLERRM);
3245 end if;
3246 END correct_next_item;
3248 /**
3249 * the foll procedure inserts the total into the temp tables.
3250 * NOTE: currently this is not being used on the UI.
3251 *
3252 * @param the order type.
3253 */
3254 PROCEDURE insert_total(v_order IN NUMBER) IS
3255 v_name VARCHAR2(250);
3256 BEGIN
3257 v_name := get_lookup_name('MSC_X_ORDER_TYPE',v_order) ;
3258 OPEN c_total(v_order) ;
3259 FETCH c_total BULK COLLECT INTO
3260 var_relation,var_order_relation,var_from_co_name,var_from_org_name,var_item_name,
3261 var_item_name_desc,var_supplier,var_customer,var_supplier_org,
3262 var_customer_org,var_uom,var_supplier_id,var_customer_id,var_supplier_site_id,
3263 var_customer_site_id,var_item_id,var_qty_nobkt,var_qty1,var_qty2,
3264 var_qty3,var_qty4,var_qty5,var_qty6,var_qty7,var_qty8,var_qty9,var_qty10,
3265 var_qty11,var_qty12,var_qty13,var_qty14,var_qty15,var_qty16,var_qty17,
3266 var_qty18,var_qty19,var_qty20,var_qty21,var_qty22,var_qty23,var_qty24,
3267 var_qty25,var_qty26,var_qty27,var_qty28,var_qty29,var_qty30,var_qty31,
3268 var_qty32,var_qty33,var_qty34,var_qty35,var_qty36 ;
3269 CLOSE c_total;
3270
3271 if var_relation is not null and var_relation.COUNT > 0 then
3272 FORALL i IN var_relation.FIRST..var_relation.LAST
3273 INSERT INTO msc_hz_ui_lines
3274 (LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
3275 ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
3276 UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
3277 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
3278 UNBUCKETED_QTY,QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
3279 QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
3280 QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
3281 QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
3282 QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
3283 QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
3284 QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
3285 QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
3286 OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
3287 OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
3288 OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
3289 OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
3290 OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
3291 OLD_QTY36,EDITABLE_FLAG)
3292 VALUES
3293 (msc_x_hz_ui_line_id_s.nextval,arg_query_id,nvl(var_relation(i),'NA'),nvl(var_order_relation(i),'NA'),
3294 nvl(var_from_co_name(i),'NA'),var_from_org_name(i),var_item_name(i),
3295 var_item_name_desc(i),0,v_order ,v_name,var_uom(i),
3296 var_supplier(i),var_customer(i),var_supplier_org(i),
3297 var_customer_org(i),var_supplier_id(i),var_customer_id(i),
3298 var_supplier_site_id(i),var_customer_site_id(i),var_item_id(i),
3299 var_qty_nobkt(i),var_qty1(i),var_qty2(i),
3300 var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
3301 var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
3302 var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
3303 var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
3304 var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
3305 var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
3306 var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),
3307 var_qty1(i),var_qty2(i),var_qty3(i),var_qty4(i),var_qty5(i),
3308 var_qty6(i),var_qty7(i),var_qty8(i),var_qty9(i),var_qty10(i),
3309 var_qty11(i),var_qty12(i),var_qty13(i),var_qty14(i),var_qty15(i),
3310 var_qty16(i),var_qty17(i),var_qty18(i),var_qty19(i),var_qty20(i),
3311 var_qty21(i),var_qty22(i),var_qty23(i),var_qty24(i),var_qty25(i),
3312 var_qty26(i),var_qty27(i),var_qty28(i),var_qty29(i),var_qty30(i),
3313 var_qty31(i),var_qty32(i),var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),1) ;
3314
3315 end if;
3316 EXCEPTION
3317 when others then
3318 arg_err_msg := arg_err_msg || ' insert total ' || SQLERRM;
3319 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3320 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'insert_total', SQLERRM);
3321 end if;
3322 if c_Total%ISOPEN then
3323 CLOSE c_Total;
3324 end if;
3325 END ;
3326
3327 PROCEDURE invert_quantities(arg_query_id NUMBER, p_order_type NUMBER)
3328 IS
3329 BEGIN
3330 UPDATE msc_hz_ui_lines
3331 SET UNBUCKETED_QTY = -UNBUCKETED_QTY,
3332 QTY_BUCKET1 = -QTY_BUCKET1,
3336 QTY_BUCKET5 = -QTY_BUCKET5,
3333 QTY_BUCKET2 = -QTY_BUCKET2,
3334 QTY_BUCKET3 = -QTY_BUCKET3,
3335 QTY_BUCKET4 = -QTY_BUCKET4,
3337 QTY_BUCKET6 = -QTY_BUCKET6,
3338 QTY_BUCKET7 = -QTY_BUCKET7,
3339 QTY_BUCKET8 = -QTY_BUCKET8,
3340 QTY_BUCKET9 = -QTY_BUCKET9,
3341 QTY_BUCKET10 = -QTY_BUCKET10,
3342 QTY_BUCKET11 = -QTY_BUCKET11,
3343 QTY_BUCKET12 = -QTY_BUCKET12,
3344 QTY_BUCKET13 = -QTY_BUCKET13,
3345 QTY_BUCKET14 = -QTY_BUCKET14,
3346 QTY_BUCKET15 = -QTY_BUCKET15,
3347 QTY_BUCKET16 = -QTY_BUCKET16,
3348 QTY_BUCKET17 = -QTY_BUCKET17,
3349 QTY_BUCKET18 = -QTY_BUCKET18,
3350 QTY_BUCKET19 = -QTY_BUCKET19,
3351 QTY_BUCKET20 = -QTY_BUCKET20,
3352 QTY_BUCKET21 = -QTY_BUCKET21,
3353 QTY_BUCKET22 = -QTY_BUCKET22,
3354 QTY_BUCKET23 = -QTY_BUCKET23,
3355 QTY_BUCKET24 = -QTY_BUCKET24,
3356 QTY_BUCKET25 = -QTY_BUCKET25,
3357 QTY_BUCKET26 = -QTY_BUCKET26,
3358 QTY_BUCKET27 = -QTY_BUCKET27,
3359 QTY_BUCKET28 = -QTY_BUCKET28,
3360 QTY_BUCKET29 = -QTY_BUCKET29,
3361 QTY_BUCKET30 = -QTY_BUCKET30,
3362 QTY_BUCKET31 = -QTY_BUCKET31,
3363 QTY_BUCKET32 = -QTY_BUCKET32,
3364 QTY_BUCKET33 = -QTY_BUCKET33,
3365 QTY_BUCKET34 = -QTY_BUCKET34,
3366 QTY_BUCKET35 = -QTY_BUCKET35,
3367 QTY_BUCKET36 = -QTY_BUCKET36
3368 WHERE order_type = p_order_type
3369 AND query_id = arg_query_id;
3370
3371 END;
3372
3373 PROCEDURE delete_order_type(arg_query_id NUMBER, p_order_type NUMBER)
3374 IS
3375 BEGIN
3376
3377 DELETE FROM msc_hz_ui_lines
3378 WHERE order_type = p_order_type
3379 AND query_id = arg_query_id;
3380
3381 EXCEPTION
3382 WHEN OTHERS THEN
3383 NULL;
3384 END;
3385
3386
3387 PROCEDURE insert_net_forecast(arg_query_id NUMBER)
3388 IS
3389
3390 CURSOR c_net_forecast
3391 IS
3392 SELECT ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
3393 SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
3394 SUPPLIER_ID,CUSTOMER_ID,INVENTORY_ITEM_ID,
3395 ROUND( sum(UNBUCKETED_QTY), 6) q_0,ROUND( sum(QTY_BUCKET1), 6) q_1,
3396 ROUND( sum(QTY_BUCKET2), 6) q_2,ROUND( sum(QTY_BUCKET3), 6) q_3, ROUND( sum(QTY_BUCKET4), 6) q_4,
3397 ROUND( sum(QTY_BUCKET5), 6) q_5,ROUND( sum(QTY_BUCKET6), 6) q_6,ROUND( sum(QTY_BUCKET7), 6) q_7,
3398 ROUND( sum(QTY_BUCKET8), 6) q_8,ROUND( sum(QTY_BUCKET9), 6) q_9,ROUND( sum(QTY_BUCKET10), 6) q_10,
3399 ROUND( sum(QTY_BUCKET11), 6) q_11,ROUND( sum(QTY_BUCKET12), 6) q_12,ROUND( sum(QTY_BUCKET13), 6) q_13,
3400 ROUND( sum(QTY_BUCKET14), 6) q_14,ROUND( sum(QTY_BUCKET15), 6) q_15,ROUND( sum(QTY_BUCKET16), 6) q_16,
3401 ROUND( sum(QTY_BUCKET17), 6) q_17,ROUND( sum(QTY_BUCKET18), 6) q_18,ROUND( sum(QTY_BUCKET19), 6) q_19,
3402 ROUND( sum(QTY_BUCKET20), 6) q_20,ROUND( sum(QTY_BUCKET21), 6) q_21,ROUND( sum(QTY_BUCKET22), 6) q_22,
3403 ROUND( sum(QTY_BUCKET23), 6) q_23,ROUND( sum(QTY_BUCKET24), 6) q_24,ROUND( sum(QTY_BUCKET25), 6) q_25,
3404 ROUND( sum(QTY_BUCKET26), 6) q_26,ROUND( sum(QTY_BUCKET27), 6) q_27,ROUND( sum(QTY_BUCKET28), 6) q_28,
3405 ROUND( sum(QTY_BUCKET29), 6) q_29,ROUND( sum(QTY_BUCKET30), 6) q_30,ROUND( sum(QTY_BUCKET31), 6) q_31,
3406 ROUND( sum(QTY_BUCKET32), 6) q_32,ROUND( sum(QTY_BUCKET33), 6) q_33,ROUND( sum(QTY_BUCKET34), 6) q_34,
3407 ROUND( sum(QTY_BUCKET35), 6) q_35,ROUND( sum(QTY_BUCKET36), 6) q_36
3408 FROM msc_hz_ui_lines
3409 WHERE ORDER_TYPE IN (ORDER_FORECAST_CST, REQUISITION,PURCHASE_ORDER)
3410 AND query_id = arg_query_id
3411 GROUP BY ORDER_RELATION_GROUP, FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
3412 SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
3413 SUPPLIER_ID,CUSTOMER_ID,INVENTORY_ITEM_ID
3414 HAVING sum(decode(order_type, ORDER_FORECAST_CST, 1, 0)) > 0;
3415
3416 v_calculation_name VARCHAR2(255) := fnd_message.get_string('MSC','MSC_X_HZ_NET_FORECAST') ;
3417
3418 BEGIN
3419 invert_quantities(arg_query_id, REQUISITION);
3420 invert_quantities(arg_query_id, PURCHASE_ORDER);
3421
3422 OPEN c_net_forecast;
3423 FETCH c_net_forecast BULK COLLECT INTO
3424 var_order_relation,var_from_co_name,var_from_org_name,var_item_name,
3425 var_item_name_desc,var_supplier,var_customer,var_supplier_org,
3426 var_customer_org,var_uom,var_supplier_id,var_customer_id,
3427 var_item_id,var_qty_nobkt,var_qty1,var_qty2,
3428 var_qty3,var_qty4,var_qty5,var_qty6,var_qty7,var_qty8,var_qty9,var_qty10,
3429 var_qty11,var_qty12,var_qty13,var_qty14,var_qty15,var_qty16,var_qty17,
3430 var_qty18,var_qty19,var_qty20,var_qty21,var_qty22,var_qty23,var_qty24,
3434
3431 var_qty25,var_qty26,var_qty27,var_qty28,var_qty29,var_qty30,var_qty31,
3432 var_qty32,var_qty33,var_qty34,var_qty35,var_qty36 ;
3433 CLOSE c_net_forecast;
3435 invert_quantities(arg_query_id, REQUISITION);
3436 invert_quantities(arg_query_id, PURCHASE_ORDER);
3437
3438 if var_order_relation is not null and var_order_relation.COUNT > 0 then
3439 FORALL i IN var_order_relation.FIRST..var_order_relation.LAST
3440 INSERT INTO msc_hz_ui_lines
3441 (LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
3442 ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
3443 UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
3444 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
3445 UNBUCKETED_QTY,QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
3446 QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
3447 QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
3448 QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
3449 QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
3450 QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
3451 QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
3452 QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
3453 OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
3454 OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
3455 OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
3456 OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
3457 OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
3458 OLD_QTY36,EDITABLE_FLAG)
3459 VALUES
3460 (msc_x_hz_ui_line_id_s.nextval,arg_query_id,'NA',nvl(var_order_relation(i),'NA'),
3461 nvl(var_from_co_name(i),'NA'),var_from_org_name(i),var_item_name(i),
3462 var_item_name_desc(i),39,-1,v_calculation_name,var_uom(i),var_supplier(i),
3463 var_customer(i),var_supplier_org(i),var_customer_org(i),
3464 var_supplier_id(i),var_customer_id(i),'NA',
3465 'NA',var_item_id(i),var_qty_nobkt(i),var_qty1(i),var_qty2(i),
3466 var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
3467 var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
3468 var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
3469 var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
3470 var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
3471 var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
3472 var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),var_qty1(i),var_qty2(i),
3473 var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
3474 var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
3475 var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
3476 var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
3477 var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
3478 var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
3479 var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),1) ;
3480
3481 end if;
3482 EXCEPTION
3483 when others then
3484 arg_err_msg := arg_err_msg || ' insert running total ' || SQLERRM;
3485 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3486 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'insert_running_total', SQLERRM);
3487 end if;
3488 if c_runTotal%ISOPEN then
3489 CLOSE c_runTotal;
3490 end if;
3491 END insert_net_forecast;
3492
3493 -- post processing for safety stock and PAB.
3494 -- in the case where ss/pab are 0's for a particular bucket
3495 -- display the ss/pab value of the previous bucket.
3496 PROCEDURE fill_ss_pab_empty_buckets IS
3497 default_ss NUMBER := 0;
3498 default_pab NUMBER := 0;
3499 i NUMBER := 1;
3500 BEGIN
3501
3502
3503 -- safety stock
3504
3505 FOR i in var_relation.FIRST..var_relation.LAST LOOP
3506 if(var_order(i) = SAFETY_STOCK or var_order(i) = PROJ_AVAIL_BAL) then
3507 BEGIN
3508 select quantity into default_ss
3509 from msc_sup_dem_entries_ui_v
3510 where nvl(base_item_id,inventory_item_id) = var_item_id(i)
3511 and publisher_order_type = 7
3512 and publisher_id = var_pub_id(i)
3513 and publisher_site_id = var_pub_site_id(i)
3514 and key_date < p_start_date
3515 and rownum < 2
3516 order by key_date asc;
3517
3518 EXCEPTION
3519 WHEN no_data_found THEN
3520 default_ss := 0;
3521 END;
3522 BEGIN
3523 select quantity into default_pab
3524 from msc_sup_dem_entries_ui_v
3525 where nvl(base_item_id,inventory_item_id) = var_item_id(i)
3526 and publisher_order_type = 8
3527 and publisher_id = var_pub_id(i)
3528 and publisher_site_id = var_pub_site_id(i)
3532
3529 and key_date < p_start_date
3530 and rownum < 2
3531 order by key_date asc;
3533 EXCEPTION
3534 WHEN no_data_found THEN
3535 default_pab := 0;
3536 END;
3537 if var_order(i) = SAFETY_STOCK then
3538 if var_qty1(i) = 0 then
3539 var_qty1(i) := default_ss;
3540 elsif var_qty1(i) = -99999999 then
3541 var_qty1(i) := 0;
3542 end if;
3543 elsif var_order(i) = PROJ_AVAIL_BAL then
3544 if var_qty1(i) = 0 then
3545 var_qty1(i) := default_pab;
3546 elsif var_qty1(i) = -99999999 then
3547 var_qty1(i) := 0;
3548 end if;
3549 end if;
3550
3551 if var_qty2(i) = 0 then
3552 var_qty2(i) := var_qty1(i);
3553 elsif var_qty2(i) = -99999999 then
3554 var_qty2(i) := 0;
3555 end if;
3556
3557 if var_qty3(i) = 0 then
3558 var_qty3(i) := var_qty2(i);
3559 elsif var_qty3(i) = -99999999 then
3560 var_qty3(i) := 0;
3561 end if;
3562
3563 if var_qty4(i) = 0 then
3564 var_qty4(i) := var_qty3(i);
3565 elsif var_qty4(i) = -99999999 then
3566 var_qty4(i) := 0;
3567 end if;
3568
3569 if var_qty5(i) = 0 then
3570 var_qty5(i) := var_qty4(i);
3571 elsif var_qty5(i) = -99999999 then
3572 var_qty5(i) := 0;
3573 end if;
3574
3575 if var_qty6(i) = 0 then
3576 var_qty6(i) := var_qty5(i);
3577 elsif var_qty6(i) = -99999999 then
3578 var_qty6(i) := 0;
3579 end if;
3580
3581 if var_qty7(i) = 0 then
3582 var_qty7(i) := var_qty6(i);
3583 elsif var_qty7(i) = -99999999 then
3584 var_qty7(i) := 0;
3585 end if;
3586
3587 if var_qty8(i) = 0 then
3588 var_qty8(i) := var_qty7(i);
3589 elsif var_qty8(i) = -99999999 then
3590 var_qty8(i) := 0;
3591 end if;
3592
3593 if var_qty9(i) = 0 then
3594 var_qty9(i) := var_qty8(i);
3595 elsif var_qty9(i) = -99999999 then
3596 var_qty9(i) := 0;
3597 end if;
3598
3599 if var_qty10(i) = 0 then
3600 var_qty10(i) := var_qty9(i);
3601 elsif var_qty10(i) = -99999999 then
3602 var_qty10(i) := 0;
3603 end if;
3604
3605 if var_qty11(i) = 0 then
3606 var_qty11(i) := var_qty10(i);
3607 elsif var_qty11(i) = -99999999 then
3608 var_qty11(i) := 0;
3609 end if;
3610
3611 if var_qty12(i) = 0 then
3612 var_qty12(i) := var_qty11(i);
3613 elsif var_qty12(i) = -99999999 then
3614 var_qty12(i) := 0;
3615 end if;
3616
3617 if var_qty13(i) = 0 then
3618 var_qty13(i) := var_qty12(i);
3619 elsif var_qty13(i) = -99999999 then
3620 var_qty13(i) := 0;
3621 end if;
3622
3623 if var_qty14(i) = 0 then
3624 var_qty14(i) := var_qty13(i);
3625 elsif var_qty14(i) = -99999999 then
3626 var_qty14(i) := 0;
3627 end if;
3628
3629 if var_qty15(i) = 0 then
3630 var_qty15(i) := var_qty14(i);
3631 elsif var_qty15(i) = -99999999 then
3632 var_qty15(i) := 0;
3633 end if;
3634
3635 if var_qty16(i) = 0 then
3636 var_qty16(i) := var_qty15(i);
3637 elsif var_qty16(i) = -99999999 then
3638 var_qty16(i) := 0;
3639 end if;
3640
3641 if var_qty17(i) = 0 then
3642 var_qty17(i) := var_qty16(i);
3643 elsif var_qty17(i) = -99999999 then
3644 var_qty17(i) := 0;
3645 end if;
3646
3647 if var_qty18(i) = 0 then
3648 var_qty18(i) := var_qty17(i);
3649 elsif var_qty18(i) = -99999999 then
3650 var_qty18(i) := 0;
3651 end if;
3652
3653 if var_qty19(i) = 0 then
3654 var_qty19(i) := var_qty18(i);
3655 elsif var_qty19(i) = -99999999 then
3656 var_qty19(i) := 0;
3657 end if;
3658
3659 if var_qty20(i) = 0 then
3660 var_qty20(i) := var_qty19(i);
3661 elsif var_qty20(i) = -99999999 then
3662 var_qty20(i) := 0;
3663 end if;
3664
3665 if var_qty21(i) = 0 then
3666 var_qty21(i) := var_qty20(i);
3667 elsif var_qty21(i) = -99999999 then
3668 var_qty21(i) := 0;
3669 end if;
3670
3671 if var_qty22(i) = 0 then
3672 var_qty22(i) := var_qty21(i);
3673 elsif var_qty22(i) = -99999999 then
3674 var_qty22(i) := 0;
3675 end if;
3676
3677 if var_qty23(i) = 0 then
3678 var_qty23(i) := var_qty22(i);
3679 elsif var_qty23(i) = -99999999 then
3680 var_qty23(i) := 0;
3681 end if;
3682
3683 if var_qty24(i) = 0 then
3684 var_qty24(i) := var_qty23(i);
3685 elsif var_qty24(i) = -99999999 then
3686 var_qty24(i) := 0;
3687 end if;
3688
3689 if var_qty25(i) = 0 then
3690 var_qty25(i) := var_qty24(i);
3691 elsif var_qty25(i) = -99999999 then
3692 var_qty25(i) := 0;
3693 end if;
3694
3695 if var_qty26(i) = 0 then
3696 var_qty26(i) := var_qty25(i);
3697 elsif var_qty26(i) = -99999999 then
3698 var_qty26(i) := 0;
3699 end if;
3700
3701 if var_qty27(i) = 0 then
3702 var_qty27(i) := var_qty26(i);
3703 elsif var_qty27(i) = -99999999 then
3704 var_qty27(i) := 0;
3705 end if;
3706
3707 if var_qty28(i) = 0 then
3711 end if;
3708 var_qty28(i) := var_qty27(i);
3709 elsif var_qty28(i) = -99999999 then
3710 var_qty28(i) := 0;
3712
3713 if var_qty29(i) = 0 then
3714 var_qty29(i) := var_qty28(i);
3715 elsif var_qty29(i) = -99999999 then
3716 var_qty29(i) := 0;
3717 end if;
3718
3719 if var_qty30(i) = 0 then
3720 var_qty30(i) := var_qty29(i);
3721 elsif var_qty30(i) = -99999999 then
3722 var_qty30(i) := 0;
3723 end if;
3724
3725 if var_qty31(i) = 0 then
3726 var_qty31(i) := var_qty30(i);
3727 elsif var_qty31(i) = -99999999 then
3728 var_qty31(i) := 0;
3729 end if;
3730
3731 if var_qty32(i) = 0 then
3732 var_qty32(i) := var_qty31(i);
3733 elsif var_qty32(i) = -99999999 then
3734 var_qty32(i) := 0;
3735 end if;
3736
3737 if var_qty33(i) = 0 then
3738 var_qty33(i) := var_qty32(i);
3739 elsif var_qty33(i) = -99999999 then
3740 var_qty33(i) := 0;
3741 end if;
3742
3743 if var_qty34(i) = 0 then
3744 var_qty34(i) := var_qty33(i);
3745 elsif var_qty34(i) = -99999999 then
3746 var_qty34(i) := 0;
3747 end if;
3748
3749 if var_qty35(i) = 0 then
3750 var_qty35(i) := var_qty34(i);
3751 elsif var_qty35(i) = -99999999 then
3752 var_qty35(i) := 0;
3753 end if;
3754
3755 if var_qty36(i) = 0 then
3756 var_qty36(i) := var_qty35(i);
3757 elsif var_qty36(i) = -99999999 then
3758 var_qty36(i) := 0;
3759 end if;
3760 end if;
3761
3762 END LOOP;
3763
3764 END;
3765
3766 PROCEDURE insert_total_supply(arg_query_id NUMBER)
3767 IS
3768
3769 CURSOR c_total_supply
3770 IS
3771 SELECT ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
3772 SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
3773 SUPPLIER_ID,CUSTOMER_ID,INVENTORY_ITEM_ID,
3774 ROUND( sum(UNBUCKETED_QTY), 6) q_0,ROUND( sum(QTY_BUCKET1), 6) q_1,
3775 ROUND( sum(QTY_BUCKET2), 6) q_2,ROUND( sum(QTY_BUCKET3), 6) q_3, ROUND( sum(QTY_BUCKET4), 6) q_4,
3776 ROUND( sum(QTY_BUCKET5), 6) q_5,ROUND( sum(QTY_BUCKET6), 6) q_6,ROUND( sum(QTY_BUCKET7), 6) q_7,
3777 ROUND( sum(QTY_BUCKET8), 6) q_8,ROUND( sum(QTY_BUCKET9), 6) q_9,ROUND( sum(QTY_BUCKET10), 6) q_10,
3778 ROUND( sum(QTY_BUCKET11), 6) q_11,ROUND( sum(QTY_BUCKET12), 6) q_12,ROUND( sum(QTY_BUCKET13), 6) q_13,
3779 ROUND( sum(QTY_BUCKET14), 6) q_14,ROUND( sum(QTY_BUCKET15), 6) q_15,ROUND( sum(QTY_BUCKET16), 6) q_16,
3780 ROUND( sum(QTY_BUCKET17), 6) q_17,ROUND( sum(QTY_BUCKET18), 6) q_18,ROUND( sum(QTY_BUCKET19), 6) q_19,
3781 ROUND( sum(QTY_BUCKET20), 6) q_20,ROUND( sum(QTY_BUCKET21), 6) q_21,ROUND( sum(QTY_BUCKET22), 6) q_22,
3782 ROUND( sum(QTY_BUCKET23), 6) q_23,ROUND( sum(QTY_BUCKET24), 6) q_24,ROUND( sum(QTY_BUCKET25), 6) q_25,
3783 ROUND( sum(QTY_BUCKET26), 6) q_26,ROUND( sum(QTY_BUCKET27), 6) q_27,ROUND( sum(QTY_BUCKET28), 6) q_28,
3784 ROUND( sum(QTY_BUCKET29), 6) q_29,ROUND( sum(QTY_BUCKET30), 6) q_30,ROUND( sum(QTY_BUCKET31), 6) q_31,
3785 ROUND( sum(QTY_BUCKET32), 6) q_32,ROUND( sum(QTY_BUCKET33), 6) q_33,ROUND( sum(QTY_BUCKET34), 6) q_34,
3786 ROUND( sum(QTY_BUCKET35), 6) q_35,ROUND( sum(QTY_BUCKET36), 6) q_36
3787 FROM msc_hz_ui_lines
3788 WHERE ORDER_TYPE IN (SUPPLY_COMMIT,SALES_ORDER)
3789 AND query_id = arg_query_id
3790 GROUP BY ORDER_RELATION_GROUP, FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
3791 SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
3792 SUPPLIER_ID,CUSTOMER_ID,INVENTORY_ITEM_ID;
3793
3794 v_calculation_name VARCHAR2(255) := fnd_message.get_string('MSC','MSC_X_HZ_TOTAL_SUPPLY') ;
3795
3796 BEGIN
3797 OPEN c_total_supply;
3798 FETCH c_total_supply BULK COLLECT INTO
3799 var_order_relation,var_from_co_name,var_from_org_name,var_item_name,
3800 var_item_name_desc,var_supplier,var_customer,var_supplier_org,
3801 var_customer_org,var_uom,var_supplier_id,var_customer_id,
3802 var_item_id,var_qty_nobkt,var_qty1,var_qty2,
3803 var_qty3,var_qty4,var_qty5,var_qty6,var_qty7,var_qty8,var_qty9,var_qty10,
3804 var_qty11,var_qty12,var_qty13,var_qty14,var_qty15,var_qty16,var_qty17,
3805 var_qty18,var_qty19,var_qty20,var_qty21,var_qty22,var_qty23,var_qty24,
3806 var_qty25,var_qty26,var_qty27,var_qty28,var_qty29,var_qty30,var_qty31,
3807 var_qty32,var_qty33,var_qty34,var_qty35,var_qty36 ;
3808 CLOSE c_total_supply;
3809
3810 IF var_order_relation is not null and var_order_relation.COUNT > 0 THEN
3811 FORALL i IN var_order_relation.FIRST..var_order_relation.LAST
3812 INSERT INTO msc_hz_ui_lines
3813 (LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
3814 ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
3815 UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
3816 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
3817 UNBUCKETED_QTY,QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
3818 QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
3822 QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
3819 QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
3820 QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
3821 QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
3823 QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
3824 QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
3825 OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
3826 OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
3827 OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
3828 OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
3829 OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
3830 OLD_QTY36,EDITABLE_FLAG)
3831 VALUES
3832 (msc_x_hz_ui_line_id_s.nextval,arg_query_id,'NA',nvl(var_order_relation(i),'NA'),
3833 nvl(var_from_co_name(i),'NA'),var_from_org_name(i),var_item_name(i),
3834 var_item_name_desc(i),41,-1,v_calculation_name,var_uom(i),var_supplier(i),
3835 var_customer(i),var_supplier_org(i),var_customer_org(i),
3836 var_supplier_id(i),var_customer_id(i),'NA',
3837 'NA',var_item_id(i),var_qty_nobkt(i),var_qty1(i),var_qty2(i),
3838 var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
3839 var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
3840 var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
3841 var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
3842 var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
3843 var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
3844 var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),var_qty1(i),var_qty2(i),
3845 var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
3846 var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
3847 var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
3848 var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
3849 var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
3850 var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
3851 var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),1) ;
3852
3853 END IF;
3854 EXCEPTION
3855 when others then
3856 arg_err_msg := arg_err_msg || ' insert running total ' || SQLERRM;
3857 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3858 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'insert_running_total', SQLERRM);
3859 end if;
3860 if c_runTotal%ISOPEN then
3861 CLOSE c_runTotal;
3862 end if;
3863 END insert_total_supply;
3864
3865 /**
3866 * the foll procedure inserts the running total row into the temp tables.
3867 * NOTE: currently this is being used on the UI for
3868 * only order forecast and supply commit
3869 *
3870 * @param the order type.
3871 */
3872 PROCEDURE insert_running_total(v_order_type IN NUMBER) IS
3873 v_name VARCHAR2(250);
3874 BEGIN
3875 if v_order_type = ORDER_FORECAST_CST then
3876 v_name := fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND') ;
3877 elsif v_order_type = SUPPLY_COMMIT then
3878 v_name := fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY') ;
3879 end if;
3880
3881 OPEN c_runTotal(v_order_type) ;
3882 FETCH c_runTotal BULK COLLECT INTO
3883 var_relation,var_order_relation,var_from_co_name,var_from_org_name,var_item_name,
3884 var_item_name_desc,var_supplier,var_customer,var_supplier_org,
3885 var_customer_org,var_uom,var_supplier_id,var_customer_id,var_supplier_site_id,
3886 var_customer_site_id,var_item_id,var_qty_nobkt,var_qty1,var_qty2,
3887 var_qty3,var_qty4,var_qty5,var_qty6,var_qty7,var_qty8,var_qty9,var_qty10,
3888 var_qty11,var_qty12,var_qty13,var_qty14,var_qty15,var_qty16,var_qty17,
3889 var_qty18,var_qty19,var_qty20,var_qty21,var_qty22,var_qty23,var_qty24,
3890 var_qty25,var_qty26,var_qty27,var_qty28,var_qty29,var_qty30,var_qty31,
3891 var_qty32,var_qty33,var_qty34,var_qty35,var_qty36 ;
3892 CLOSE c_runTotal;
3893
3894 if var_relation is not null and var_relation.COUNT > 0 then
3895 FORALL i IN var_relation.FIRST..var_relation.LAST
3896 INSERT INTO msc_hz_ui_lines
3897 (LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
3898 ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
3899 UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
3900 SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
3901 UNBUCKETED_QTY,QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
3902 QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
3903 QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
3904 QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
3905 QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
3906 QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
3910 OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
3907 QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
3908 QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
3909 OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
3911 OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
3912 OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
3913 OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
3914 OLD_QTY36,EDITABLE_FLAG)
3915 VALUES
3916 (msc_x_hz_ui_line_id_s.nextval,arg_query_id,nvl(var_relation(i),'NA'),nvl(var_order_relation(i),'NA'),
3917 nvl(var_from_co_name(i),'NA'),var_from_org_name(i),var_item_name(i),
3918 var_item_name_desc(i),40,-1,v_name,var_uom(i),var_supplier(i),
3919 var_customer(i),var_supplier_org(i),var_customer_org(i),
3920 var_supplier_id(i),var_customer_id(i),var_supplier_site_id(i),
3921 var_customer_site_id(i),var_item_id(i),var_qty_nobkt(i),var_qty1(i),var_qty2(i),
3922 var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
3923 var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
3924 var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
3925 var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
3926 var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
3927 var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
3928 var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),var_qty1(i),var_qty2(i),
3929 var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
3930 var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
3931 var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
3932 var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
3933 var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
3934 var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
3935 var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),1) ;
3936
3937 end if;
3938 EXCEPTION
3939 when others then
3940 arg_err_msg := arg_err_msg || ' insert running total ' || SQLERRM;
3941 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3942 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'insert_running_total', SQLERRM);
3943 end if;
3944 if c_runTotal%ISOPEN then
3945 CLOSE c_runTotal;
3946 end if;
3947 END;
3948
3949 -- ============================================================================= end declare block
3950
3951 /**
3952 * Begin the main procedure
3953 *
3954 * loop though the plans.
3955 * get dates.
3956 * get the number of buckets to be displayed.
3957 * insert the buckets into the msc_hz_ui_headers table.
3958 */
3959
3960
3961 BEGIN
3962 arg_next_link := 'N';
3963 arg_query_id := -1;
3964
3965 v_pref := rtrim(ltrim(arg_pref_name));
3966
3967 BEGIN
3968 get_user_prefs(v_pref);
3969 EXCEPTION
3970 when others then
3971 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3972 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'populate_bucketed_quantity', 'user prefs' || SQLERRM);
3973 end if;
3974 set_default_prefs;
3975 END;
3976
3977 if v_pref IS NOT NULL OR arg_default_pref = 1 then
3978
3979 -- get the graph title from the fnd_lookup_values table
3980 if v_graphtype <> 0 then
3981 v_graphtitle := get_lookup_name('MSC_X_GRAPH_OPTION', v_graphtype);
3982 end if;
3983
3984 --get the default calendar
3985 v_default_cal_code := FND_PROFILE.VALUE('MSC_X_DEFAULT_CALENDAR');
3986 --v_default_cal_code := 'CP-Mon-70';
3987
3988
3989 -- get the buckets with default calendar and initialise the array
3990 get_bucket_dates(arg_from_date, v_default_cal_code);
3991
3992 --check multiple sites are found with the default calendar
3993 g_multiple_sites := check_for_multiple_sites(null);
3994
3995 if (g_multiple_sites = 0 ) then
3996 -- call the api to get cust site's recieving calendar
3997 msc_x_util.get_calendar_code(v_supplier_id, v_supplier_site_id, v_customer_id, v_customer_site_id, v_calendar_code, v_sr_instance_id); --'CP-Mon-70';
3998
3999 if (v_calendar_code <> v_default_cal_code ) then
4000
4001 BEGIN
4002 get_user_prefs(v_pref);
4003 EXCEPTION
4004 when others then
4005 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4006 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'populate_bucketed_quantity', 'user prefs' || SQLERRM);
4007 end if;
4008 set_default_prefs;
4009 END;
4010 get_bucket_dates(arg_from_date, v_calendar_code);
4011 end if;
4012 end if;
4013
4014 set_date_variables();
4015
4016 --initialize the first array
4017 initialize(tot_rec) ;
4018
4019 -- If we need to show a net forecast, include these order types for
4023 v_delete_requisition := TRUE;
4020 -- summary purposes and delete them again later from the lines table
4021 IF v_net_forecast > 0 THEN
4022 IF v_requisition = NOT_SELECTED THEN
4024 v_requisition := REQUISITION;
4025 END IF;
4026
4027 IF v_purchase_order = NOT_SELECTED THEN
4028 v_delete_purchase_order := TRUE;
4029 v_purchase_order := PURCHASE_ORDER;
4030 END IF;
4031 END IF;
4032
4033 --get the past due column header
4034 v_past_due_hdr := FND_MESSAGE.GET_STRING('MSC','MSC_X_HZ_PAST_DUE');
4035
4036 --massage ss and pab data
4037 --fill_ss_pab_empty_buckets;
4038 -- get the query id first
4039 SELECT msc_x_hz_ui_query_id_s.nextval INTO arg_query_id FROM Dual;
4040
4041 -- insert the header
4042 INSERT INTO msc_hz_ui_headers
4043 (QUERY_ID,NO_OF_BUCKETS,PROD_SUM_LEVEL, ORG_SUM_LEVEL, ORG_SUM_LEVEL_TP,
4044 BUCKET1,BUCKET2,BUCKET3,BUCKET4,BUCKET5,BUCKET6,BUCKET7,BUCKET8,BUCKET9,
4045 BUCKET10,BUCKET11,BUCKET12,BUCKET13,BUCKET14,BUCKET15,BUCKET16,BUCKET17,
4046 BUCKET18,BUCKET19,BUCKET20,BUCKET21,BUCKET22,BUCKET23,BUCKET24,BUCKET25,
4047 BUCKET26,BUCKET27,BUCKET28,BUCKET29,BUCKET30,BUCKET31,BUCKET32,BUCKET33,
4048 BUCKET34,BUCKET35,BUCKET36,UNDATED_BUCKET_FLAG,USER_PREFERENCE,GRAPH_TYPE,
4049 GRAPH_TITLE,LAST_BUCKET)
4050 VALUES
4051 (arg_query_id,g_num_of_buckets,prod_agg, myco_agg, tpco_agg,
4052 var_dates(1),var_dates(2),var_dates(3),var_dates(4),var_dates(5),
4053 var_dates(6),var_dates(7),var_dates(8),var_dates(9),var_dates(10),
4054 var_dates(11),var_dates(12),var_dates(13),var_dates(14),var_dates(15),
4055 var_dates(16),var_dates(17),var_dates(18),var_dates(19),var_dates(20),
4056 var_dates(21),var_dates(22),var_dates(23),var_dates(24),var_dates(25),
4057 var_dates(26),var_dates(27),var_dates(28),var_dates(29),var_dates(30),
4058 var_dates(31),var_dates(32),var_dates(33),var_dates(34),var_dates(35),
4059 var_dates(36),'Y',v_pref,v_graphtype,v_graphtitle,v_last_bkt_date ) ;
4060
4061 -- Now get all the data using the params.
4062 g_statement := prepare_sql(null);
4063
4064
4065 print_query (); -- can be used for debug purposes.
4066
4067
4068 OPEN osce_bucketed_plan FOR g_statement;
4069
4070 LOOP
4071 FETCH osce_bucketed_plan INTO
4072 activity_rec.RELATION,
4073 activity_rec.ORDER_RELATION,
4074 activity_rec.FROM_CO_NAME,
4075 activity_rec.FROM_ORG_NAME,
4076 activity_rec.ITEM_ID,
4077 activity_rec.ITEM_NAME,
4078 activity_rec.ITEM_DESC,
4079 activity_rec.SUPPLIER_ITEM_NAME,
4080 activity_rec.SUPPLIER_NAME,
4081 activity_rec.CUSTOMER_NAME,
4082 activity_rec.SUPPLIER_ORG,
4083 activity_rec.CUSTOMER_ORG,
4084 activity_rec.ORDER_TYPE,
4085 activity_rec.ORDER_DESC,
4086 activity_rec.SHIPPING_CONTROL,
4087 activity_rec.UOM,
4088 activity_rec.NEW_QUANTITY,
4089 activity_rec.NEW_DATE,
4090 activity_rec.SUPPLIER_ID,
4091 activity_rec.CUSTOMER_ID,
4092 activity_rec.SUPP_SITE_ID,
4093 activity_rec.CUST_SITE_ID,
4094 activity_rec.THIRD_PARTY_FLAG,
4095 activity_rec.VIEWER_CO,
4096 activity_rec.TP_CO,
4097 activity_rec.BUCKET_TYPE,
4098 activity_rec.PUBLISHER_ID,
4099 activity_rec.PUBLISHER_SITE_ID
4100 ;
4101
4102 EXIT WHEN osce_bucketed_plan%NOTFOUND;
4103 activity_rec.new_date := trunc(activity_rec.new_date);
4104
4105 curr_date := trunc(nvl(arg_from_date, p_start_date));
4106
4107
4108 -- commented out for the past due calculation.
4109 --IF activity_rec.new_date >= curr_date THEN
4110
4111
4112 curr_rel := activity_rec.RELATION;
4113 curr_item := activity_rec.ITEM_NAME;
4114 curr_ot := activity_rec.ORDER_TYPE;
4115 curr_ship_ctrl := activity_rec.SHIPPING_CONTROL;
4116
4117 /**
4118 * basic logic:
4119 * if the date is = curr date store qty there
4120 * if the date is greater then compare with the next bucket
4121 * if it is less than the next bucket then put is in prev bucket.
4122 * for the combination of relation(buyer/seller),item, order type
4123 * LOOP THROUGH THE RESULT SET FORM MSC_SUP_DEM_ENTRIES
4124 * curr_relation = msc_supdem.currrecord.relation
4125 * curr_item = msc_supdem.currrecord.item_id
4126 * curr_order_type = msc_supdem.currrecord.order_type
4127 * if curr_relation <> last_relation AND
4128 * curr_item <> last_item AND
4129 * curr_order <> last_order THEN
4130 * I N S E R T into the lines table.
4131 * S E T the buckets to 0.
4132 * end if;
4136 * if new_date = var_dates(i) then
4133 * FOR i IN var_dates.FIRST TO var_dates.LAST LOOP
4134 * -- loop though the buckets array (var_dates)
4135 * -- check msc_supdem.currrecord.new_date with var_dates(i)
4137 * add the quantity to the quantity(i) bucket
4138 * i.e. quantity(i) = quantity(i) + quantity.
4139 * end if;
4140 * if new_date > var_dates(i) and new_date < var_dates(i+1) then
4141 * add the quantity to the quantity(i) bucket
4142 * i.e. quantity(i) = quantity(i) + quantity.
4143 * end if;
4144 * END LOOP;
4145 */
4146
4147
4148 -- get the correct starting point of the records.
4149 -- this will where the unique combination of the
4150 -- relation and item (START_COUNTER = the starting point and START_COUNTER < ending point)
4151 -- if STARTING_COUNTER has reached the ending point then exit the loop.
4152 -- till the START_COUNTER reaches the starting point do not start the calculation.
4153 -- set calculate past due boolean to true
4154
4155 IF curr_rel <> last_rel OR curr_item <> last_item OR curr_ot <> last_ot THEN
4156 rec_counter := rec_counter + 1;
4157 calc_past_due := TRUE;
4158 last_ship_ctrl := curr_ship_ctrl;
4159
4160 END IF;
4161
4162
4163 if (activity_rec.SHIPPING_CONTROL is not null) then
4164 var_ship_ctrl(rec_counter) := ' (' || activity_rec.SHIPPING_CONTROL || ') ';
4165 elsif (activity_rec.SHIPPING_CONTROL is null) then
4166 var_ship_ctrl(rec_counter) := null;
4167 end if;
4168
4169 IF((curr_ship_ctrl <> last_ship_ctrl) OR (myco_agg <> ORG_AGG) OR (tpco_agg <> ORG_AGG)) THEN
4170 var_ship_ctrl(rec_counter) := null;
4171 END IF;
4172
4173 IF(curr_ot = ASN) THEN --If order type is asn nullify shipping control
4174 var_ship_ctrl(rec_counter) := null;
4175 END IF; ---Bug # 6147428
4176
4177 -- here if the end row limit has been reached
4178 -- set the next link to true for the UI.
4179 -- then exit the loop
4180
4181 --Commneted out for bug#4445912
4182 --IF rec_counter >= 1 and rec_counter <= 200 THEN
4183
4184 -- if the very first record set the prev to the curr rec.
4185 -- initialize all the buckets to 0
4186 if firstrec = 0 then
4187
4188 -- set the prev and the current to be the same
4189 -- so that the combination start is the same
4190 previous_rec:= activity_rec;
4191
4192 -- increase the value of prev_rec
4193 -- so that this loop wont be entered again
4194 firstrec := firstrec + 1;
4195
4196 end if;
4197
4198 -- if the combination has changed then insert the prev record.
4199
4200 if previous_rec.relation <> activity_rec.relation or
4201 previous_rec.item_name <> activity_rec.item_name or
4202 previous_rec.order_type <> activity_rec.order_type -- or
4203 then
4204
4205 set_non_qty_data(tot_rec);
4206
4207 previous_rec := activity_rec;
4208
4209 tot_rec := tot_rec + 1;
4210 initialize(tot_rec);
4211
4212
4213 -- if curr item is same as prev then curr item is 2
4214
4215 if tot_rec > 1 AND var_item_id(tot_rec-1) = activity_rec.item_id then
4216 var_next_item(tot_rec) := 2;
4217 else
4218 var_next_item(tot_rec) := 1;
4219 end if;
4220 end if;
4221
4222 temp_sup_site := add_to_array(temp_sup_site, activity_rec.supp_site_id) ;
4223 temp_sup := add_to_array(temp_sup, activity_rec.supplier_id);
4224 temp_cust_site := add_to_array(temp_cust_site, activity_rec.cust_site_id);
4225 temp_cust := add_to_array(temp_cust, activity_rec.customer_id);
4226
4227
4228 calculate_bucket_data(tot_rec);
4229
4230
4231
4232 -- calculate past due quantity only when the record changed.
4233 if(calc_past_due = TRUE) then
4234
4235 -- calculate past due quantity for PO, SO and ASN
4236 IF (v_asn > 0 AND activity_rec.order_type = ASN) OR
4237 (v_purchase_order > 0 AND activity_rec.order_type = PURCHASE_ORDER) OR
4238 (v_sales_order > 0 AND activity_rec.order_type = SALES_ORDER) THEN
4239
4240 var_past_due_qty(rec_counter) := calc_past_due_qty(activity_rec.order_type);
4241 pagesize := pagesize + 1;
4242 END IF;
4243 calc_past_due := FALSE;
4244 end if;
4245
4246
4247 --END IF; -- end of rec_counter >= start and <= end
4248
4249
4250 -- assign the current relation and item to the last ones
4251 last_rel := curr_rel;
4252 last_item := curr_item;
4253 last_ot := curr_ot;
4254 last_ship_ctrl := curr_ship_ctrl;
4255
4256 END LOOP; -- end loop of the FETCH into
4257
4258 -- handle last record.
4262 else
4259 set_non_qty_data(tot_rec);
4260 if tot_rec > 1 AND var_item_id(tot_rec-1) = var_item_id(tot_rec) then
4261 var_next_item(tot_rec) := 2;
4263 var_next_item(tot_rec) := 1;
4264 end if;
4265
4266 close osce_bucketed_plan;
4267
4268 -- clean up records
4269 -- check if the headers has buckets - else just remove all
4270 -- records from the relation array
4271 BEGIN
4272 SELECT nvl(no_of_buckets,NOT_SELECTED)
4273 INTO record_cnt
4274 FROM msc_hz_ui_headers
4275 WHERE query_id = arg_query_id ;
4276
4277 EXCEPTION
4278 when no_data_found then
4279 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4280 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Get rec cnt '||SQLERRM);
4281 end if;
4282 record_cnt := 0;
4283
4284 when others then
4285 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4286 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Get rec cnt '||SQLERRM);
4287 end if;
4288 record_cnt :=0;
4289 END;
4290
4291 if record_cnt = 0 then
4292 var_relation.delete;
4293 var_order_relation.delete;
4294 end if;
4295
4296 /**
4297 * clean up invalid records
4298 */
4299 BEGIN
4300 if var_relation is not null and var_relation.COUNT > 0 then
4301 i := 0;
4302 FOR i IN var_relation.FIRST..var_relation.LAST LOOP
4303
4304 if ((var_relation(i) = 'NA' or
4305 var_relation(i) = null) and
4306 var_order(i) = -1 and
4307 var_supplier_id(i) is null and
4308 var_customer_id(i) is null)
4309 then
4310 var_relation.delete(i);
4311 var_order_relation.delete(i);
4312 var_item_name.delete(i);
4313 var_item_name.delete(i);
4314 var_supplier_id.delete(i);
4315 var_customer_id.delete(i);
4316 var_order.delete(i);
4317 end if;
4318 END LOOP ;
4319
4320 end if;
4321 EXCEPTION
4322 when others then
4323 arg_err_msg := arg_err_msg || ' cleanup ' || SQLERRM;
4324 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4325 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Cleanup '||SQLERRM);
4326 end if;
4327
4328 END;
4329
4330 --massage ss and pab data
4331 fill_ss_pab_empty_buckets;
4332
4333
4334 -- bulk insert;
4335 BEGIN
4336 if var_relation is not null and var_relation.COUNT > 0 then
4337 i := 0;
4338 FORALL i IN var_relation.FIRST..var_relation.LAST
4339 INSERT INTO msc_hz_ui_lines
4340 (LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
4341 ITEM_NAME,ITEM_DESCRIPTION,SUPPLIER_NAME,CUSTOMER_NAME,
4342 SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,ORDER_TYPE_RANK,
4343 ORDER_TYPE,ORDER_TYPE_DESC,shipping_control,UOM,SUPPLIER_ID,CUSTOMER_ID,
4344 SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,PAST_DUE_QTY, UNBUCKETED_QTY,
4345 QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,QTY_BUCKET5,
4346 QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,QTY_BUCKET10,
4347 QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,QTY_BUCKET15,
4348 QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,QTY_BUCKET20,
4349 QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,QTY_BUCKET25,
4350 QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,QTY_BUCKET30,
4351 QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,QTY_BUCKET35,
4352 QTY_BUCKET36,EDITABLE_FLAG,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
4353 OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
4354 OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
4355 OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
4356 OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
4357 OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,OLD_QTY36,
4358 BUCKET_TYPE,PUBLISHER_ID,PUBLISHER_SITE_ID,NEXT_ITEM,SUP_ITEM)
4359 VALUES
4360 (msc_x_hz_ui_line_id_s.nextval,arg_query_id,var_relation(i),var_order_relation(i),
4361 var_from_co_name(i),var_from_org_name(i),var_item_name(i),
4362 var_item_name_desc(i),var_supplier(i),var_customer(i),
4363 var_supplier_org(i),var_customer_org(i),var_order_rank(i),
4364 var_order(i),var_order_desc(i),var_ship_ctrl(i),var_uom(i),var_supplier_id(i),
4365 var_customer_id(i),var_supplier_site_id(i),var_customer_site_id(i),
4366 var_item_id(i),var_past_due_qty(i),var_qty_nobkt(i),var_qty1(i),var_qty2(i),var_qty3(i),
4367 var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),var_qty8(i),var_qty9(i),
4371 var_qty25(i),var_qty26(i),var_qty27(i),var_qty28(i),var_qty29(i),
4368 var_qty10(i),var_qty11(i),var_qty12(i),var_qty13(i),var_qty14(i),
4369 var_qty15(i),var_qty16(i),var_qty17(i),var_qty18(i),var_qty19(i),
4370 var_qty20(i),var_qty21(i),var_qty22(i),var_qty23(i),var_qty24(i),
4372 var_qty30(i),var_qty31(i),var_qty32(i),var_qty33(i),var_qty34(i),
4373 var_qty35(i),var_qty36(i),var_edit_flag(i),var_qty1(i),var_qty2(i),var_qty3(i),
4374 var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),var_qty8(i),var_qty9(i),
4375 var_qty10(i),var_qty11(i),var_qty12(i),var_qty13(i),var_qty14(i),
4376 var_qty15(i),var_qty16(i),var_qty17(i),var_qty18(i),var_qty19(i),
4377 var_qty20(i),var_qty21(i),var_qty22(i),var_qty23(i),var_qty24(i),
4378 var_qty25(i),var_qty26(i),var_qty27(i),var_qty28(i),var_qty29(i),
4379 var_qty30(i),var_qty31(i),var_qty32(i),var_qty33(i),var_qty34(i),
4380 var_qty35(i),var_qty36(i),var_bkt_type(i),var_pub_id(i),var_pub_site_id(i),
4381 var_next_item(i),var_sup_item(i));
4382 end if;
4383 EXCEPTION
4384 when others then
4385 arg_err_msg := arg_err_msg || ' insert records ' || SQLERRM ;
4386 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4387 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Insert record '||SQLERRM);
4388 end if;
4389 raise;
4390
4391 END ;
4392
4393 SELECT count(*)
4394 INTO arg_num_rowset
4395 FROM msc_hz_ui_lines
4396 WHERE query_id = arg_query_id
4397 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
4398 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
4399 ;
4400
4401 /**
4402 * Update other reqd fields.
4403 * update owner item, sup item, cust item, and uom for
4404 * all the editable records.
4405 */
4406 if myco_agg = ORG_AGG and tpco_agg = ORG_AGG THEN
4407 BEGIN
4408 SELECT line_id, customer_name,supplier_name,inventory_item_id,
4409 order_type,from_company_name
4410 BULK COLLECT INTO v_line_id, v_cust_name, v_sup_name,
4411 v_item_id,v_order,v_pub_name
4412 FROM msc_hz_ui_lines
4413 WHERE editable_flag = 0
4414 AND query_id = arg_query_id -- this is required to avoid the comp row disappearing act.
4415 OR order_type in (ORDER_FORECAST_CST, SUPPLY_COMMIT) ;
4416
4417 if v_line_id.COUNT > 0 then
4418 FOR i in v_line_id.FIRST..v_line_id.LAST LOOP
4419
4420 BEGIN
4421
4422 SELECT owner_item_name,supplier_item_name,customer_item_name,
4423 uom_code,nvl(tp_uom_code, uom_code) ,owner_item_description,
4424 supplier_item_description,customer_item_description
4425 INTO v_owner_item,v_sup_item,v_cust_item,v_uom_code,v_tp_uom,
4426 v_owner_item_desc,v_sup_item_desc,v_cust_item_desc
4427 FROM msc_sup_dem_entries_ui_v
4428 WHERE inventory_item_id = v_item_id(i)
4429 AND publisher_order_type = v_order(i)
4430 AND publisher_name = v_pub_name(i)
4431 AND customer_name = v_cust_name(i)
4432 AND supplier_name = v_sup_name(i)
4433 AND ROWNUM < 2 ;
4434
4435 if sql%rowcount > 0 then
4436
4437 k := var_line_id.COUNT + 1;
4438
4439 var_line_id(k) := v_line_id(i) ;
4440 var_owner_item(k) := v_owner_item;
4441 var_cust_item(k) := v_cust_item ;
4442 var_sup_item(k) := v_sup_item ;
4443 var_owner_item_desc(k) := v_owner_item_desc ;
4444 var_cust_item_desc(k) := v_cust_item_desc ;
4445 var_sup_item_desc(k) := v_sup_item_desc ;
4446 var_tp_uom(k) := v_tp_uom ;
4447 var_uom_code(k) := v_uom_code ;
4448
4449 end if;
4450 EXCEPTION
4451 when others then
4452 null;
4453 END ;
4454
4455 END LOOP;
4456
4457 end if;
4458
4459 if var_line_id.COUNT > 0 then
4460
4461 FORALL k in var_line_id.FIRST..var_line_id.LAST
4462 UPDATE msc_hz_ui_lines
4463 SET owner_item = var_owner_item(k),
4464 cust_item = var_cust_item(k),
4465 sup_item = var_sup_item(k),
4466 owner_item_desc = var_owner_item_desc(k),
4467 sup_item_desc = var_sup_item_desc(k),
4468 cust_item_desc = var_cust_item_desc(k),
4472 AND line_id = var_line_id(k);
4469 tp_uom = var_tp_uom(k),
4470 uom_code = var_uom_code(k)
4471 WHERE query_id = arg_query_id
4473
4474 end if;
4475
4476 EXCEPTION
4477 when others then
4478 arg_err_msg := arg_err_msg || ' Upd owner item etc. ' || SQLERRM;
4479 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4480 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Upd owner item '||SQLERRM);
4481 end if;
4482 END ;
4483 end if;
4484
4485
4486 SELECT count(*)
4487 INTO arg_num_rowset
4488 FROM msc_hz_ui_lines
4489 WHERE query_id = arg_query_id
4490 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
4491 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
4492 ;
4493
4494 /**
4495 * Companion row
4496 * check if a companion row is required
4497 * - if order forecast and supply commit are chosen
4498 * - if user company is the publisher of one of the above
4499 * and both orders are not present then insert a record with
4500 * the tp as the publisher, and the qty_bucket(X) value
4501 * defaulted to the companion rows values
4502 * but the old_qty(X) is still 0.
4503 */
4504 BEGIN
4505 add_companion_row(arg_query_id);
4506 EXCEPTION
4507 when others then
4508 arg_err_msg := arg_err_msg || ' companion row ' || SQLERRM;
4509 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4510 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity',' companion row '||SQLERRM);
4511 end if;
4512 END;
4513
4514
4515
4516 SELECT count(*)
4517 INTO arg_num_rowset
4518 FROM msc_hz_ui_lines
4519 WHERE query_id = arg_query_id
4520 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
4521 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
4522 ;
4523
4524 /**
4525 * Clean-Up
4526 * clean up bad records like item = -1 , document owner = -1 etc.
4527 * if the data is 3rd party then do not display the sites
4528 * if the agg is company do not display sites.
4529 */
4530 BEGIN
4531
4532 v_user_company := sys_context('MSC','COMPANY_NAME');
4533
4534 -- remove site if agg is at company level
4535 if myco_agg = COMPANY_AGG and tpco_AGG = COMPANY_AGG then
4536
4537 UPDATE msc_hz_ui_lines
4538 SET FROM_ORG_CODE = null,SUPPLIER_ORG_CODE = NULL,
4539 CUSTOMER_ORG_CODE = NULL
4540 WHERE query_id = arg_query_id ;
4541 end if;
4542
4543 if myco_agg = COMPANY_AGG and tpco_AGG = ORG_AGG then
4544 -- delete site only where user co id is not the id
4545 UPDATE msc_hz_ui_lines
4546 SET FROM_ORG_CODE = null
4547 WHERE FROM_COMPANY_NAME = v_user_company
4548 AND query_id = arg_query_id ;
4549
4550 UPDATE msc_hz_ui_lines
4551 SET SUPPLIER_ORG_CODE = null
4552 WHERE SUPPLIER_NAME = v_user_company -- user is the supplier
4553 AND query_id = arg_query_id ;
4554
4555 UPDATE msc_hz_ui_lines
4556 SET CUSTOMER_ORG_CODE = null
4557 WHERE CUSTOMER_NAME = v_user_company -- user is the customer
4558 AND query_id = arg_query_id ;
4559
4560 end if;
4561
4562 if myco_agg = ORG_AGG and tpco_AGG = COMPANY_AGG then
4563 -- delete site only where user co id is not the id
4564 UPDATE msc_hz_ui_lines
4565 SET FROM_ORG_CODE = null
4566 WHERE FROM_COMPANY_NAME <> v_user_company
4567 AND query_id = arg_query_id ;
4568
4569 UPDATE msc_hz_ui_lines
4570 SET SUPPLIER_ORG_CODE = null
4571 WHERE CUSTOMER_NAME = v_user_company -- user is the customer
4572 AND query_id = arg_query_id ;
4573
4574 UPDATE msc_hz_ui_lines
4575 SET CUSTOMER_ORG_CODE = null
4576 WHERE SUPPLIER_NAME = v_user_company -- user is the customer
4577 AND query_id = arg_query_id ;
4578
4579 -- third party
4580 UPDATE msc_hz_ui_lines
4581 SET FROM_ORG_CODE = null,SUPPLIER_ORG_CODE = NULL,
4582 CUSTOMER_ORG_CODE = NULL
4583 WHERE SUPPLIER_NAME <> v_user_company and CUSTOMER_NAME <> v_user_company ;
4584
4585 end if;
4586
4587 if tpco_agg = ALL_AGG then
4588
4589 -- third party - remove site for all
4590 UPDATE msc_hz_ui_lines
4594 AND CUSTOMER_NAME <> v_user_company
4591 SET FROM_ORG_CODE = null,SUPPLIER_ORG_CODE = NULL,
4592 CUSTOMER_ORG_CODE = NULL
4593 WHERE SUPPLIER_NAME <> v_user_company
4595 AND query_id = arg_query_id ;
4596
4597 -- if doc owner is not user then set it to null for ALL agg.
4598 UPDATE msc_hz_ui_lines
4599 SET FROM_COMPANY_NAME = null, FROM_ORG_CODE = null
4600 WHERE FROM_COMPANY_NAME <> v_user_company
4601 AND (SUPPLIER_NAME = v_user_company OR CUSTOMER_NAME = v_user_company )
4602 AND query_id = arg_query_id ;
4603
4604 UPDATE msc_hz_ui_lines
4605 SET customer_name = FND_MESSAGE.GET_STRING('MSC','MSC_X_HZ_TP_ALL'),
4606 customer_org_code = NULL
4607 --,customer_site_id = NULL
4608 WHERE SUPPLIER_NAME = v_user_company -- user is supplier
4609 AND query_id = arg_query_id ;
4610
4611 UPDATE msc_hz_ui_lines
4612 SET SUPPLIER_NAME = FND_MESSAGE.GET_STRING('MSC','MSC_X_HZ_TP_ALL'),
4613 SUPPLIER_ORG_CODE = NULL
4614 WHERE CUSTOMER_NAME = v_user_company -- user is customer
4615 AND query_id = arg_query_id ;
4616
4617 if myco_agg = COMPANY_AGG then
4618
4619 UPDATE msc_hz_ui_lines
4620 SET supplier_org_code = null
4621 WHERE SUPPLIER_NAME = v_user_company -- user is supplier
4622 AND query_id = arg_query_id ;
4623
4624 UPDATE msc_hz_ui_lines
4625 SET customer_org_code = null
4626 WHERE CUSTOMER_NAME = v_user_company -- user is customer
4627 AND query_id = arg_query_id ;
4628
4629 UPDATE msc_hz_ui_lines
4630 SET FROM_ORG_CODE = null
4631 WHERE FROM_COMPANY_NAME <> v_user_company
4632 AND query_id = arg_query_id ;
4633
4634 end if;
4635
4636 end if;
4637
4638 EXCEPTION
4639 when no_data_found then
4640 arg_err_msg := arg_err_msg || ' update ' || SQLERRM;
4641 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4642 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Update '||SQLERRM);
4643 end if;
4644
4645 when others then
4646 -- do nothing
4647 arg_err_msg := arg_err_msg || ' update-2' || SQLERRM;
4648 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4649 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Update '||SQLERRM);
4650 end if;
4651
4652 END;
4653
4654 -- NOW HANDLE THE NEW RECORDS FOR RUNNING/TOTAL DEMAND RUNNING/TOTAL SUPPLY
4655
4656 /**
4657 * Running totals
4658 *
4659 * basic logic
4660
4661 * for TOTAL demand/supply
4662 * select from the temp table and insert into temp table
4663
4664 * for running total demad/supply
4665 * current total + running_total of prev bucket
4666 * select the same as below then loop through
4667 * curr bucket qty (var_qty) := running_total + curr_total
4668 */
4669 -- NOW HANDLE RUNNING TOTAL.
4670 BEGIN
4671
4672 if v_run_tot_demand > 0 then
4673
4674 -- open the cursor pass the query id and the order types where clause.
4675 -- bulk insert
4676 insert_running_total(ORDER_FORECAST_CST);
4677 end if;
4678
4679 if v_run_tot_supply > 0 then
4680 -- open the cursor pass the query id and the order types where clause.
4681 -- bulk insert
4682 insert_running_total(SUPPLY_COMMIT);
4683
4684 end if;
4685 EXCEPTION
4686 when others then
4687 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4688 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity', 'Run tot '||SQLERRM);
4689 end if;
4690 null;
4691 END;
4692
4693
4694 SELECT count(*)
4695 INTO arg_num_rowset
4696 FROM msc_hz_ui_lines
4697 WHERE query_id = arg_query_id
4698 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
4699 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
4700 ;
4701
4702
4703 -- NOW HANDLE NET FORECAST
4704 BEGIN
4705
4706 IF v_net_forecast > 0 THEN
4707 insert_net_forecast(arg_query_id);
4708
4709 -- If we need to show a net forecast, we included these order types for
4710 -- summary purposes and need to delete them now from the lines table
4711 IF v_delete_requisition = TRUE THEN
4712 v_requisition := NOT_SELECTED;
4713 delete_order_type(arg_query_id, REQUISITION);
4717 v_purchase_order := NOT_SELECTED;
4714 END IF;
4715
4716 IF v_delete_purchase_order = TRUE THEN
4718 delete_order_type(arg_query_id, PURCHASE_ORDER);
4719 END IF;
4720 END IF;
4721
4722 EXCEPTION
4723 WHEN others THEN
4724 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4725 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'net_forecast', 'Net Forecast '||SQLERRM);
4726 end if;
4727 END;
4728
4729
4730 SELECT count(*)
4731 INTO arg_num_rowset
4732 FROM msc_hz_ui_lines
4733 WHERE query_id = arg_query_id
4734 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
4735 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
4736 ;
4737
4738 -- NOW HANDLE TOTAL SUPPLY
4739 BEGIN
4740
4741 IF v_total_supply > 0 THEN
4742 insert_total_supply(arg_query_id);
4743 END IF;
4744
4745 EXCEPTION
4746 WHEN others THEN
4747 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4748 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'total_supply', 'Total Supply '||SQLERRM);
4749 end if;
4750 END;
4751
4752
4753 SELECT count(*)
4754 INTO arg_num_rowset
4755 FROM msc_hz_ui_lines
4756 WHERE query_id = arg_query_id
4757 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
4758 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
4759 ;
4760
4761 BEGIN
4762 -- now update the undated_buckets value int the header
4763 if unbucketed_flag > 0 then
4764 UPDATE msc_hz_ui_headers
4765 SET undated_bucket_flag = 'Y'
4766 WHERE query_id = arg_query_id;
4767 else
4768 UPDATE msc_hz_ui_headers
4769 SET undated_bucket_flag = 'N'
4770 WHERE query_id = arg_query_id;
4771
4772 end if;
4773
4774 correct_next_item(arg_query_id);
4775
4776 -- SELECT count(distinct relation_group||item_name)
4777 SELECT count(*)
4778 INTO arg_num_rowset
4779 FROM msc_hz_ui_lines
4780 WHERE query_id = arg_query_id
4781 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
4782 AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
4783 ;
4784
4785 if arg_num_rowset = 0 then
4786 arg_query_id := -1;
4787 end if;
4788 EXCEPTION
4789 when no_data_found then
4790 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4791 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Upd Bkt flag '||SQLERRM);
4792 end if;
4793
4794 when others then
4795 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4796 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Upd Bkt flag '||SQLERRM);
4797 end if;
4798
4799 END ;
4800
4801 end if;
4802
4803 if arg_query_id = -1 then
4804 arg_err_msg := arg_err_msg || fnd_message.get_string('MSC','MSC_X_HZ_NODATA');
4805 arg_num_rowset := 0 ;
4806 end if;
4807
4808 EXCEPTION
4809 WHEN NO_DATA_FOUND THEN
4810 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4811 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'populate_bucketed_quantity', SQLERRM);
4812 end if;
4813 arg_query_id :=-1;
4814 arg_num_rowset := 0 ;
4815 arg_err_msg := arg_err_msg || fnd_message.get_string('MSC','MSC_X_HZ_NODATA');
4816
4817 if (osce_bucketed_plan%ISOPEN) then
4818 close osce_bucketed_plan;
4819 end if;
4820
4821 WHEN OTHERS THEN
4822 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4823 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'populate_bucketed_quantity', SQLERRM);
4824 end if;
4825 arg_query_id :=-1;
4826 arg_err_msg := arg_err_msg || fnd_message.get_string('MSC','MSC_X_HZ_NODATA');
4827 arg_num_rowset := 0 ;
4828
4829 if (osce_bucketed_plan%ISOPEN) then
4830 close osce_bucketed_plan;
4831 end if;
4832
4833 END;
4834
4835 /**
4836 * The foll function retrieves the meaning for different
4837 * lookup types and codes from teh fnd_lookup_values table.
4838 */
4839 FUNCTION get_lookup_name(v_lookup_type IN VARCHAR2, v_lookup_code IN NUMBER) RETURN VARCHAR2
4840 IS
4841 v_name VARCHAR2(250) := '';
4842 BEGIN
4843 SELECT meaning INTO v_name
4844 FROM fnd_lookup_values
4845 WHERE lookup_type = v_lookup_type
4846 AND lookup_code = nvl(v_lookup_code,-1)
4847 AND language = userenv('lang');
4848
4849 return v_name;
4850 EXCEPTION
4851 when no_data_found then
4852 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4853 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'get_lookup', SQLERRM);
4854 end if;
4855 return null;
4856
4857 when others then
4858 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4859 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'get_lookup', SQLERRM);
4860 end if;
4861 return null;
4862 END;
4863
4864
4865
4866
4867
4868
4869 END MSC_X_HZ_PLAN;