1 PACKAGE BODY MSC_X_PEGGING_FUNC AS
2 /* $Header: MSCXPEGB.pls 115.20 2004/04/26 19:05:59 vpillari ship $ */
3
4 /**
5 * package to create the function that are used in Enhanced pegging screen
6 */
7
8 TYPE date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER ;
9
10 TYPE int_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
11
12
13 PURCHASE_ORDER CONSTANT INTEGER := 13;
14 SALES_ORDER CONSTANT INTEGER := 14;
15 ASN CONSTANT INTEGER := 15;
16 RECEIPT CONSTANT INTEGER := 16;
17
18 RECEIPT_LEVEL CONSTANT INTEGER := 5;
19 ASN_LEVEL CONSTANT INTEGER := 4;
20 SO_LEVEL CONSTANT INTEGER := 3;
21 OTHER_LEVEL CONSTANT INTEGER := 2;
22
23 LEVEL_5 CONSTANT INTEGER := 5;
24 LEVEL_4 CONSTANT INTEGER := 4;
25 LEVEL_3 CONSTANT INTEGER := 3;
26 LEVEL_2 CONSTANT INTEGER := 2;
27
28
29 DATE_EXCEP CONSTANT INTEGER := 1;
30 QTY_EXCEP CONSTANT INTEGER := 2;
31 DEFAULT_FORMAT VARCHAR2(10) := 'MM-DD-YYYY';
32
33
34 /**
35 * the following function returns a string composed of
36 * transactionids of immediate children for the required
37 * transactionid.
38
39 * @param transaction id as number
40 * @return varchar2 - a string of transids separated with
41 - @transactionIds=
42 * NOTE: the @ is used here in place of the ampersand.
43 * : since the ampersand is a reservered character in PL/SQL
44 */
45 FUNCTION get_transids (arg_transid IN NUMBER,arg_binder IN VARCHAR2) RETURN VARCHAR2
46 IS
47
48
49 var_ids int_type;
50
51 var_order_type number;
52
53 var_start_id number;
54
55 var_retstr VARCHAR2(4000) ;
56
57 var_str CONSTANT VARCHAR2(16) := arg_binder || 'transactionIds=' ;
58
59 i INTEGER := 0;
60
61 BEGIN
62 var_start_id := arg_transid;
63
64 SELECT publisher_order_type
65 INTO var_order_type
66 FROM msc_sup_dem_entries_ui_v -- msc_sup_dem_entries_ui_v
67 WHERE transaction_id = arg_transid ;
68
69 if var_order_type <> PURCHASE_ORDER then
70
71 var_start_id := get_immediate_po(arg_transid, var_order_type);
72
73 end if;
74
75
76 -- now start and peg down
77 SELECT a.transaction_id
78 BULK COLLECT INTO var_ids
79 FROM msc_sup_dem_entries a -- msc_sup_dem_entries_ui_v
80 WHERE exists
81 (select 1 from msc_sup_dem_security_v security
82 where security.transaction_id = a.transaction_id )
83 AND Level < RECEIPT_LEVEL
84 START WITH a.transaction_id = var_start_id
85 CONNECT BY PRIOR a.order_number = a.end_order_number
86 AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
87 AND (
88 (a.end_order_line_number IS NOT NULL AND
89 PRIOR a.line_number = a.end_order_line_number )
90 OR
91 (a.end_order_line_number IS NULL AND
92 PRIOR a.publisher_id = a.end_order_publisher_id AND
93 decode(a.end_order_publisher_site_id,
94 null, PRIOR a.publisher_site_id,
95 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
96 PRIOR a.inventory_item_id = a.inventory_item_id )
97 OR
98 (a.end_order_line_number IS NULL AND
99 a.end_order_publisher_id <> a.publisher_id AND
100 PRIOR a.inventory_item_id = a.inventory_item_id )
101 )
102 AND (
103 (a.end_order_publisher_id IS NOT NULL AND
104 PRIOR a.publisher_id = a.end_order_publisher_id AND
105 a.end_order_type IS NOT NULL AND
106 PRIOR a.publisher_order_type = a.end_order_type AND
107 decode(a.end_order_publisher_site_id,
108 null, PRIOR a.publisher_site_id,
109 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
110 )
111 OR
112 (a.end_order_publisher_id IS NULL AND
113 a.end_order_type IS NOT NULL AND
114 PRIOR a.publisher_id = a.publisher_id)
115 ) ;
116
117 if var_ids is not null then
118 for i in var_ids.FIRST.. var_ids.LAST loop
119 -- now if the length exceeds 4000 then exit
120 if (length(var_retstr) + length(var_ids(i)) > 4000) then
121 exit;
122 end if;
126
123 var_retstr := var_retstr || var_str || var_ids(i);
124 end loop;
125 end if;
127 return var_retstr;
128 EXCEPTION
129 when others then
130 return null;
131
132 END get_transids;
133
134
135
136 /**
137 * the following function checks if the given
138 * transactionid has any children pegged to it
139
140 * @param transaction id as number
141 * @return number of children pegged - excluding itself
142 */
143 FUNCTION get_child_num (arg_transid IN NUMBER) RETURN NUMBER
144 IS
145 v_ret_num number;
146 BEGIN
147
148 -- PEG DOWN
149 SELECT count(a.transaction_id)
150 INTO v_ret_num
151 FROM msc_sup_dem_entries a-- msc_sup_dem_entries_ui_v
152 WHERE exists
153 (select 1 from msc_sup_dem_security_v security
154 where security.transaction_id = a.transaction_id )
155 AND a.transaction_id <> arg_transid -- else the starting rec will be counted as one
156 START WITH a.transaction_id = arg_transid
157 CONNECT BY
158 PRIOR a.order_number = a.end_order_number
159 AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
160 AND (
161 (a.end_order_line_number IS NOT NULL AND
162 PRIOR a.line_number = a.end_order_line_number )
163 OR
164 (a.end_order_line_number IS NULL AND
165 PRIOR a.publisher_id = a.end_order_publisher_id AND
166 decode(a.end_order_publisher_site_id,
167 null, PRIOR a.publisher_site_id,
168 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
169 PRIOR a.inventory_item_id = a.inventory_item_id )
170 OR
171 (a.end_order_line_number IS NULL AND
172 a.end_order_publisher_id <> a.publisher_id AND
173 PRIOR a.inventory_item_id = a.inventory_item_id )
174 )
175 AND (
176 (a.end_order_publisher_id IS NOT NULL AND
177 PRIOR a.publisher_id = a.end_order_publisher_id AND
178 a.end_order_type IS NOT NULL AND
179 PRIOR a.publisher_order_type = a.end_order_type AND
180 decode(a.end_order_publisher_site_id,
181 null, PRIOR a.publisher_site_id,
182 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
183 )
184 OR
185 (a.end_order_publisher_id IS NULL AND
186 a.end_order_type IS NOT NULL AND
187 PRIOR a.publisher_id = a.publisher_id)
188 ) ;
189
190
191 return v_ret_num;
192 EXCEPTION
193 when others then
194 return 0;
195
196 END get_child_num;
197
198
199
200 /**
201 * The following function returns the max receipt date as follows.
202 * For an SO/ASN/SR row, this will be the receipt date for that SO
203 * For a PO row, this will be the last receipt date of all immediate SO pegged to that PO.
204 */
205
206 FUNCTION get_receipt_date (arg_transid IN NUMBER) RETURN DATE
207 IS
208 v_receipt_date date ;
209 v_order_type number;
210
211 BEGIN
212
213 /**
214 * get the order type of the current order
215 * if so then return the receipt date
216 * if po then get the max(receipt_date) of all the sos pegged to the po.
217 */
218
219 Select publisher_order_type, receipt_date
220 Into v_order_type, v_receipt_date
221 From msc_sup_dem_entries_ui_v
222 Where transaction_id = arg_transid
223 and publisher_order_type in (PURCHASE_ORDER, SALES_ORDER, ASN);
224
225 if v_order_type = SALES_ORDER OR v_order_type = ASN then
226 return v_receipt_date;
227 end if;
228
229 if v_order_type = PURCHASE_ORDER then
230
231 -- PEG DOWN
232 SELECT max(a.receipt_date)
233 INTO v_receipt_date
234 FROM msc_sup_dem_entries a
235 WHERE a.plan_id = -1
236 AND a.publisher_order_type = SALES_ORDER
237 AND exists
238 (select 1 from msc_sup_dem_security_v security
239 where security.transaction_id = a.transaction_id )
240 AND Level < LEVEL_3
241 START with a.transaction_id = arg_transid
242 CONNECT BY PRIOR a.order_number = a.end_order_number
243 AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
244 AND (
245 (a.end_order_line_number IS NOT NULL AND
246 PRIOR a.line_number = a.end_order_line_number )
247 OR
248 (a.end_order_line_number IS NULL AND
249 PRIOR a.publisher_id = a.end_order_publisher_id AND
250 decode(a.end_order_publisher_site_id,
251 null, PRIOR a.publisher_site_id,
252 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
253 PRIOR a.inventory_item_id = a.inventory_item_id )
254 OR
255 (a.end_order_line_number IS NULL AND
256 a.end_order_publisher_id <> a.publisher_id AND
257 PRIOR a.inventory_item_id = a.inventory_item_id )
258 )
259 AND (
260 (a.end_order_publisher_id IS NOT NULL AND
261 PRIOR a.publisher_id = a.end_order_publisher_id AND
262 a.end_order_type IS NOT NULL AND
263 PRIOR a.publisher_order_type = a.end_order_type AND
267 )
264 decode(a.end_order_publisher_site_id,
265 null, PRIOR a.publisher_site_id,
266 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
268 OR
269 (a.end_order_publisher_id IS NULL AND
270 a.end_order_type IS NOT NULL AND
271 PRIOR a.publisher_id = a.publisher_id)
272 ) ;
273 end if;
274
275 return v_receipt_date;
276
277 EXCEPTION
278 WHEN NO_DATA_FOUND then
279 return null;
280
281 WHEN OTHERS then
282 return null;
283
284 END get_receipt_date;
285
286
287 /**
288 * Foll function gets the number of days late, as follows
289 * For an SO row, this will be [Need By date on the immediate PO - Scheduled Receipt Date on that SO]
290 * For a PO row, this will be [Need By date on the PO - Scheduled Receipt Date of the immediate
291 * SO that will be received last]
292 *
293 */
294 FUNCTION get_days_late (arg_transid IN NUMBER) RETURN NUMBER
295 IS
296 v_days_late number;
297
298 v_receipt_date date ;
299 v_need_by_date date ;
300 v_order_type number;
301
302 v_start_id number;
303
304 BEGIN
305
306 /*
307 * find out the order type.
308 * if it is a sales order then peg up and then peg down
309 * if PO then peg down.
310 */
311 v_start_id := arg_transid;
312
313 Select publisher_order_type,
314 decode(publisher_order_type,PURCHASE_ORDER,receipt_date,null),
315 decode(publisher_order_type,SALES_ORDER,receipt_date,null)
316 Into v_order_type, v_need_by_date, v_receipt_date
317 From msc_sup_dem_entries_ui_v
318 Where transaction_id = arg_transid
319 and publisher_order_type in (PURCHASE_ORDER, SALES_ORDER);
320
321 IF v_order_type = SALES_ORDER THEN
322 -- peg up to the PO
323 v_start_id := get_immediate_po(arg_transid, v_order_type);
324
325 Select receipt_date
326 Into v_need_by_date
327 From msc_sup_dem_entries_ui_v
328 Where transaction_id = v_start_id ;
329
330 END IF;
331
332 IF v_order_type = PURCHASE_ORDER then
333 v_receipt_date := MSC_X_PEGGING_FUNC.get_receipt_date(v_start_id);
334 END IF;
335
336 if v_receipt_date <= v_need_by_date then
337 return null;
338 else
339 v_days_late := v_receipt_date - v_need_by_date ;
340
341 end if;
342
343 if v_days_late IS NOT NULL then
344 v_days_late:= round(v_days_late,2);
345 end if ;
346
347 return v_days_late;
348
349 EXCEPTION
350 when others then
351 return null;
352
353 END get_days_late ;
354
355
356 /*
357 * The foll function returns the max days late for a po
358 */
359 FUNCTION get_max_late (arg_transid IN NUMBER) RETURN NUMBER
360 IS
361 v_order_type number;
362 v_max_late number;
363
364 BEGIN
365
366 Select publisher_order_type
367 Into v_order_type
368 From msc_sup_dem_entries_ui_v
369 Where transaction_id = arg_transid ;
370
371 IF v_order_type = PURCHASE_ORDER then
372
373 -- PEG DOWN
374
375 SELECT max(get_days_late(a.transaction_id))
376 INTO v_max_late
377 FROM msc_sup_dem_entries a
378 WHERE a.plan_id = -1
379 AND a.publisher_order_type = PURCHASE_ORDER
380 AND exists
381 (select 1 from msc_sup_dem_security_v security
382 where security.transaction_id = a.transaction_id )
383 START with a.transaction_id = arg_transid
384 CONNECT BY PRIOR a.order_number = a.end_order_number
385 AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
386 AND (
387 (a.end_order_line_number IS NOT NULL AND
388 PRIOR a.line_number = a.end_order_line_number )
389 OR
390 (a.end_order_line_number IS NULL AND
391 PRIOR a.publisher_id = a.end_order_publisher_id AND
392 decode(a.end_order_publisher_site_id,
393 null, PRIOR a.publisher_site_id,
394 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
395 PRIOR a.inventory_item_id = a.inventory_item_id )
396 OR
397 (a.end_order_line_number IS NULL AND
398 a.end_order_publisher_id <> a.publisher_id AND
399 PRIOR a.inventory_item_id = a.inventory_item_id )
400 )
401 AND (
402 (a.end_order_publisher_id IS NOT NULL AND
403 PRIOR a.publisher_id = a.end_order_publisher_id AND
404 a.end_order_type IS NOT NULL AND
405 PRIOR a.publisher_order_type = a.end_order_type AND
406 decode(a.end_order_publisher_site_id,
407 null, PRIOR a.publisher_site_id,
408 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
409 )
410 OR
411 (a.end_order_publisher_id IS NULL AND
412 a.end_order_type IS NOT NULL AND
413 PRIOR a.publisher_id = a.publisher_id)
414 ) ;
415 END IF;
416 IF v_max_late IS NOT NULL then
420 return v_max_late;
417 v_max_late:=round(v_max_late,2);
418 END IF;
419
421
422 END get_max_late ;
423
424
425
426 FUNCTION get_immediate_po (arg_transid IN NUMBER, arg_order_type IN NUMBER) RETURN NUMBER
427 IS
428 v_transid NUMBER;
429 v_level NUMBER := 1;
430 BEGIN
431
432 if arg_order_type = SALES_ORDER then
433 v_level := LEVEL_2;
434 elsif arg_order_type = ASN then
435 v_level := LEVEL_3;
436 elsif arg_order_type = RECEIPT then
437 v_level := LEVEL_4;
438 end if;
439
440 -- peg UP
441 SELECT sd.transaction_id
442 INTO v_transid
443 FROM msc_sup_dem_entries sd -- msc_sup_dem_entries_ui_v
444 WHERE sd.publisher_order_type = PURCHASE_ORDER
445 AND sd.plan_id = -1
446 AND exists
447 (select 1 from msc_sup_dem_security_v security
448 where security.transaction_id = sd.transaction_id )
449 AND level = v_level
450 START WITH transaction_id = arg_transid
451 CONNECT BY
452 sd.order_number = PRIOR sd.end_order_number
453 AND ( (PRIOR sd.end_order_line_number IS NOT NULL AND
454 PRIOR sd.end_order_line_number = sd.line_number)
455 OR
456 (PRIOR sd.end_order_line_number IS NULL AND
457 sd.publisher_id = PRIOR sd.end_order_publisher_id AND
458 decode(PRIOR sd.end_order_publisher_site_id, null,
459 sd.publisher_site_id,
460 PRIOR sd.end_order_publisher_site_id)
461 = sd.publisher_site_id AND
462 PRIOR sd.inventory_item_id = sd.inventory_item_id )
463 OR
464 (PRIOR sd.end_order_line_number IS NULL AND
465 PRIOR sd.publisher_id <> PRIOR sd.end_order_publisher_id)
466 )
467 AND nvl(sd.release_number, -1)
468 = nvl(PRIOR sd.end_order_rel_number, -1)
469 AND ((PRIOR sd.end_order_publisher_id IS NOT NULL AND
470 PRIOR sd.end_order_type IS NOT NULL AND
471 PRIOR sd.end_order_type = sd.publisher_order_type AND
472 PRIOR sd.end_order_publisher_id = sd.publisher_id AND
473 decode(PRIOR sd.end_order_publisher_site_id, null,
474 sd.publisher_site_id,
475 PRIOR sd.end_order_publisher_site_id)
476 = sd.publisher_site_id
477 )
478 OR
479 (PRIOR sd.end_order_publisher_id IS NULL AND
480 PRIOR sd.end_order_type IS NOT NULL AND
481 PRIOR sd.publisher_id = sd.publisher_id) )
482 and rownum = 1;
483
484 return nvl(v_transid, arg_transid);
485
486 END get_immediate_po;
487
488
489 FUNCTION get_qty_ontime (arg_transid IN NUMBER) RETURN NUMBER
490 IS
491 v_need_by_date date;
492 v_order_type number;
493 v_ontime_qty number;
494 v_start_id number;
495 v_receipt_date date;
496 BEGIN
497 v_start_id := arg_transid;
498
499 SELECT publisher_order_type,
500 decode(publisher_order_type,13,receipt_date,null),
501 decode(publisher_order_type,14,receipt_date,null),
502 decode(sys_context('MSC','COMPANY_ID'),
503 publisher_id, primary_quantity,
504 customer_id, tp_quantity,
505 supplier_id, tp_quantity,
506 quantity)
507 INTO v_order_type, v_need_by_date, v_receipt_date, v_ontime_qty
508 FROM msc_sup_dem_entries_ui_v
509 WHERE transaction_id = arg_transid;
510
511 /**
512 * if the order is SO
513 * then peg up get the po's need by date
514 * and compare if the so's date is before the need by date.
515 * If the order is po
516 * then peg down and then get the sum of qty where the so's date before the PO.
517 */
518
519 if v_order_type = SALES_ORDER then
520 v_start_id := get_immediate_po(arg_transid,v_order_type) ;
521
522 SELECT receipt_date
523 INTO v_need_by_date
524 FROM msc_sup_dem_entries_ui_v
525 WHERE transaction_id = v_start_id ;
526
527 if v_need_by_date >= v_receipt_date then
528 v_ontime_qty:=round(v_ontime_qty,6);
529 return v_ontime_qty;
530 else
531 v_ontime_qty := null;
532 end if;
533
534 end if;
535
536 if v_order_type = PURCHASE_ORDER then
537 -- PEG DOWN
538 SELECT sum(decode(sys_context('MSC','COMPANY_ID'),
539 publisher_id, primary_quantity,
540 customer_id, tp_quantity,
541 supplier_id, tp_quantity,
542 quantity) )
543 INTO v_ontime_qty
544 FROM msc_sup_dem_entries a
545 WHERE a.plan_id = -1
546 AND a.publisher_order_type = SALES_ORDER
547 AND exists
548 (select 1 from msc_sup_dem_security_v security
549 where security.transaction_id = a.transaction_id )
550 AND a.receipt_date <= v_need_by_date
551 AND LEVEL < LEVEL_3
552 START with a.transaction_id = arg_transid
553 CONNECT BY PRIOR a.order_number = a.end_order_number
554 AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
555 AND (
556 (a.end_order_line_number IS NOT NULL AND
557 PRIOR a.line_number = a.end_order_line_number )
561 decode(a.end_order_publisher_site_id,
558 OR
559 (a.end_order_line_number IS NULL AND
560 PRIOR a.publisher_id = a.end_order_publisher_id AND
562 null, PRIOR a.publisher_site_id,
563 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
564 PRIOR a.inventory_item_id = a.inventory_item_id )
565 OR
566 (a.end_order_line_number IS NULL AND
567 a.end_order_publisher_id <> a.publisher_id AND
568 PRIOR a.inventory_item_id = a.inventory_item_id )
569 )
570 AND (
571 (a.end_order_publisher_id IS NOT NULL AND
572 PRIOR a.publisher_id = a.end_order_publisher_id AND
573 a.end_order_type IS NOT NULL AND
574 PRIOR a.publisher_order_type = a.end_order_type AND
575 decode(a.end_order_publisher_site_id,
576 null, PRIOR a.publisher_site_id,
577 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
578 )
579 OR
580 (a.end_order_publisher_id IS NULL AND
581 a.end_order_type IS NOT NULL AND
582 PRIOR a.publisher_id = a.publisher_id)
583 ) ;
584 end if;
585
586 v_ontime_qty:=round(v_ontime_qty,6);
587
588 return v_ontime_qty;
589
590 END get_qty_ontime;
591
592
593 FUNCTION get_qty_late (arg_transid IN NUMBER) RETURN NUMBER
594 IS
595 v_need_by_date date;
596 v_order_type number;
597 v_late_qty number;
598 v_start_id number;
599 v_receipt_date date;
600 BEGIN
601 v_start_id := arg_transid;
602
603 SELECT publisher_order_type,
604 decode(publisher_order_type,PURCHASE_ORDER,receipt_date,null),
605 decode(publisher_order_type,SALES_ORDER,receipt_date,null),
606 decode(sys_context('MSC','COMPANY_ID'),
607 publisher_id, primary_quantity,
608 customer_id, tp_quantity,
609 supplier_id, tp_quantity,
610 quantity)
611 INTO v_order_type, v_need_by_date, v_receipt_date, v_late_qty
612 FROM msc_sup_dem_entries_ui_v
613 WHERE transaction_id = arg_transid;
614
615 /**
616 * if the order is SO
617 * then peg up get the po's need by date
618 * and compare if the so's date is after the need by date.
619 * If the order is po
620 * then peg down and then get the sum of qty where the so's date after the PO.
621 */
622
623
624 if v_order_type = SALES_ORDER then
625
626 v_start_id := get_immediate_po(arg_transid,v_order_type) ;
627
628 SELECT receipt_date
629 INTO v_need_by_date
630 FROM msc_sup_dem_entries_ui_v
631 WHERE transaction_id = v_start_id ;
632
633 if v_need_by_date < v_receipt_date then
634 v_late_qty:=round(v_late_qty,6);
635 return v_late_qty;
636 else
637 v_late_qty := null;
638 end if;
639
640 end if;
641
642 IF v_order_type = PURCHASE_ORDER THEN
643
644 -- PEG DOWN
645 SELECT sum(decode(sys_context('MSC','COMPANY_ID'),
646 publisher_id, primary_quantity,
647 customer_id, tp_quantity,
648 supplier_id, tp_quantity,
649 quantity) )
650 INTO v_late_qty
651 FROM msc_sup_dem_entries a
652 WHERE a.plan_id = -1
653 AND a.publisher_order_type = SALES_ORDER
654 AND exists
655 (select 1 from msc_sup_dem_security_v security
656 where security.transaction_id = a.transaction_id )
657 AND a.receipt_date > v_need_by_date
658 AND LEVEL < LEVEL_3
659 START with a.transaction_id = arg_transid
660 CONNECT BY PRIOR a.order_number = a.end_order_number
661 AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
662 AND (
663 (a.end_order_line_number IS NOT NULL AND
664 PRIOR a.line_number = a.end_order_line_number )
665 OR
666 (a.end_order_line_number IS NULL AND
667 PRIOR a.publisher_id = a.end_order_publisher_id AND
668 decode(a.end_order_publisher_site_id,
669 null, PRIOR a.publisher_site_id,
670 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
671 PRIOR a.inventory_item_id = a.inventory_item_id )
672 OR
673 (a.end_order_line_number IS NULL AND
674 a.end_order_publisher_id <> a.publisher_id AND
675 PRIOR a.inventory_item_id = a.inventory_item_id )
676 )
677 AND (
678 (a.end_order_publisher_id IS NOT NULL AND
679 PRIOR a.publisher_id = a.end_order_publisher_id AND
680 a.end_order_type IS NOT NULL AND
681 PRIOR a.publisher_order_type = a.end_order_type AND
682 decode(a.end_order_publisher_site_id,
686 OR
683 null, PRIOR a.publisher_site_id,
684 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
685 )
687 (a.end_order_publisher_id IS NULL AND
688 a.end_order_type IS NOT NULL AND
689 PRIOR a.publisher_id = a.publisher_id)
690 ) ;
691 END IF;
692 v_late_qty:=round(v_late_qty,6);
693 return v_late_qty;
694
695 END get_qty_late;
696
697
698 FUNCTION get_intransit (arg_transid IN NUMBER) RETURN NUMBER
699 IS
700 v_intransit_qty number;
701 v_order_type number;
702 v_start_id number;
703
704 BEGIN
705 v_start_id := arg_transid;
706
707 SELECT publisher_order_type,
708 decode(sys_context('MSC','COMPANY_ID'),
709 publisher_id, primary_quantity,
710 customer_id, tp_quantity,
711 supplier_id, tp_quantity,
712 quantity)
713 INTO v_order_type, v_intransit_qty
714 FROM msc_sup_dem_entries_ui_v
715 WHERE transaction_id = arg_transid
716 AND publisher_order_type in (PURCHASE_ORDER, ASN);
717
718 if v_order_type = ASN then
719 v_intransit_qty:=round(v_intransit_qty,6);
720 return v_intransit_qty ;
721 end if;
722
723 if v_order_type = PURCHASE_ORDER then
724 -- PEG DOWN
725 SELECT sum(decode(sys_context('MSC','COMPANY_ID'),
726 publisher_id, primary_quantity,
727 customer_id, tp_quantity,
728 supplier_id, tp_quantity,
729 quantity) )
730 INTO v_intransit_qty
731 FROM msc_sup_dem_entries a
732 WHERE a.plan_id = -1
733 AND a.publisher_order_type = ASN
734 AND exists
735 (select 1 from msc_sup_dem_security_v security
736 where security.transaction_id = a.transaction_id )
737 AND LEVEL < LEVEL_4
738 START with a.transaction_id = arg_transid
739 CONNECT BY PRIOR a.order_number = a.end_order_number
740 AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
741 AND (
742 (a.end_order_line_number IS NOT NULL AND
743 PRIOR a.line_number = a.end_order_line_number )
744 OR
745 (a.end_order_line_number IS NULL AND
746 PRIOR a.publisher_id = a.end_order_publisher_id AND
747 decode(a.end_order_publisher_site_id,
748 null, PRIOR a.publisher_site_id,
749 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
750 PRIOR a.inventory_item_id = a.inventory_item_id )
751 OR
752 (a.end_order_line_number IS NULL AND
753 a.end_order_publisher_id <> a.publisher_id AND
754 PRIOR a.inventory_item_id = a.inventory_item_id )
755
756 )
757 AND (
758 (a.end_order_publisher_id IS NOT NULL AND
759 PRIOR a.publisher_id = a.end_order_publisher_id AND
760 a.end_order_type IS NOT NULL AND
761 PRIOR a.publisher_order_type = a.end_order_type AND
762 decode(a.end_order_publisher_site_id,
763 null, PRIOR a.publisher_site_id,
764 a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
765 )
766 OR
767 (a.end_order_publisher_id IS NULL AND
768 a.end_order_type IS NOT NULL AND
769 PRIOR a.publisher_id = a.publisher_id)
770 ) ;
771 end if;
772 v_intransit_qty:=round(v_intransit_qty,6);
773 return v_intransit_qty;
774
775 END get_intransit;
776
777
778 FUNCTION get_uncommitted (arg_transid IN NUMBER) RETURN NUMBER
779 IS
780 v_po_qty number;
781 v_late_qty number;
782 v_ontime_qty number;
783 v_uncommitted number;
784 v_order_type number;
785 BEGIN
786 SELECT publisher_order_type,
787 decode(sys_context('MSC','COMPANY_ID'),
788 publisher_id, primary_quantity,
789 customer_id, tp_quantity,
790 supplier_id, tp_quantity,
791 quantity)
792 INTO v_order_type, v_po_qty
793 FROM msc_sup_dem_entries_ui_v
794 WHERE transaction_id = arg_transid;
795
796
797 IF v_order_type = PURCHASE_ORDER then
798
799 BEGIN
800 v_late_qty := nvl(get_qty_late(arg_transid),0);
801
802 EXCEPTION
803 when others then
804 v_late_qty := 0;
805 END;
806
807 BEGIN
808 v_ontime_qty := nvl(get_qty_ontime(arg_transid),0);
809 EXCEPTION
810 when others then
811 v_ontime_qty := 0;
812 END;
813
814 v_uncommitted := v_po_qty - ( v_late_qty + v_ontime_qty);
815 v_uncommitted:=round(v_uncommitted,6);
816
817 END IF;
818
819 return v_uncommitted;
820
821 EXCEPTION
822 when others then
823 return null;
824
825 END get_uncommitted;
826
827
828 END MSC_X_PEGGING_FUNC;
[Home] [Help]