1 PACKAGE BODY poa_edw_supperf AS
2 /* $Header: poaspwhb.pls 115.11 2003/12/09 10:56:20 bthammin ship $ */
3
4 ZERO_TOLERANCE CONSTANT REAL := 0.000001;
5 NULL_VALUE CONSTANT INTEGER := -23453;
6 MAGIC_STRING CONSTANT VARCHAR2(10) := '734jkhJK24';
7 NO_PRICE CONSTANT INTEGER := -1;
8 l_qty_rec_line_id NUMBER := -99999;
9 l_qty_rec_cnt_all NUMBER := -99999;
10 l_qty_rec_cnt_early NUMBER := -99999;
11 l_qty_rec_cnt_late NUMBER := -99999;
12 l_qty_rec_cnt_early_window NUMBER := -99999;
13 l_qty_rec_cnt_late_window NUMBER := -99999;
14 l_qty_rec_cnt_ondate NUMBER := -99999;
15 l_qty_rec_cnt_substitute NUMBER := -99999;
16 l_qty_rec_num_all NUMBER := -99999;
17 l_qty_rec_num_early NUMBER := -99999;
18 l_qty_rec_num_late NUMBER := -99999;
19 l_qty_rec_num_early_window NUMBER := -99999;
20 l_qty_rec_num_late_window NUMBER := -99999;
21 l_qty_rec_num_ondate NUMBER := -99999;
22 l_qty_rec_num_substitute NUMBER := -99999;
23
24
25 l_qty_rec_cnt_cor_line_id NUMBER := -99999;
26 l_qty_rec_cnt_cor_all NUMBER := -99999;
27 l_qty_rec_cnt_cor_early NUMBER := -99999;
28 l_qty_rec_cnt_cor_late NUMBER := -99999;
29 l_qty_rec_cnt_cor_early_window NUMBER := -99999;
30 l_qty_rec_cnt_cor_late_window NUMBER := -99999;
31 l_qty_rec_cnt_cor_ondate NUMBER := -99999;
32 l_qty_rec_cnt_cor_substitute NUMBER := -99999;
33 l_qty_rec_num_cor_line_id NUMBER := -99999;
34 l_qty_rec_num_cor_all NUMBER := -99999;
35 l_qty_rec_num_cor_early NUMBER := -99999;
36 l_qty_rec_num_cor_late NUMBER := -99999;
37 l_qty_rec_num_cor_early_window NUMBER := -99999;
38 l_qty_rec_num_cor_late_window NUMBER := -99999;
39 l_qty_rec_num_cor_ondate NUMBER := -99999;
40 l_qty_rec_num_cor_substitute NUMBER := -99999;
41
42
43
44 -- ========================================================================
45 -- get_invoice_date
46 --
47 -- Returns the minimum invoice date for all distributions for a line
48 -- location.
49 -- ========================================================================
50
51 FUNCTION get_invoice_date(p_line_location_id NUMBER)
52 RETURN DATE
53 IS
54 invoice_date DATE := NULL;
55 BEGIN
56
57 select MIN(aid.accounting_date)
58 into invoice_date
59 from po_distributions_all pod,
60 ap_invoice_distributions_all aid
61 where p_line_location_id = pod.line_location_id
62 AND aid.po_distribution_id = pod.po_distribution_id
63 AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
64
65 RETURN(invoice_date);
66
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 RETURN(invoice_date);
70 WHEN OTHERS THEN
71 RAISE;
72
73 END get_invoice_date;
74
75 -- ========================================================================
76 -- get_days_to_invoice
77 --
78 -- Returns the average number of days to invoice for all distributions
79 -- for a line location.
80 -- ========================================================================
81
82 FUNCTION get_days_to_invoice(p_line_location_id NUMBER)
83 RETURN NUMBER
84 IS
85 v_days NUMBER := 0;
86 BEGIN
87
88 select MIN(aid.accounting_date - pod.creation_date)
89 into v_days
90 from po_distributions_all pod,
91 ap_invoice_distributions_all aid
92 where p_line_location_id = pod.line_location_id
93 AND aid.po_distribution_id = pod.po_distribution_id
94 AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
95
96 RETURN(v_days);
97
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 RETURN(v_days);
101 WHEN OTHERS THEN
102 RAISE;
103
104 END get_days_to_invoice;
105
106
107 -- ========================================================================
108 -- get_ipv
109 --
110 -- Returns the sum ipv for all distributions
111 -- for a line location.
112 -- ========================================================================
113
114 FUNCTION get_ipv(p_line_location_id NUMBER)
115 RETURN NUMBER
116 IS
117 v_ipv NUMBER := 0;
118 BEGIN
119
120 select SUM(aid.base_invoice_price_variance / nvl(pod.rate,nvl(poh.rate,1)))
121 into v_ipv
122 from po_distributions_all pod,
123 ap_invoice_distributions_all aid,
124 po_headers_all poh
125 where p_line_location_id = pod.line_location_id
126 AND aid.po_distribution_id = pod.po_distribution_id
127 AND poh.po_header_id = pod.po_header_id
128 AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
129
130 RETURN(v_ipv);
131
132 EXCEPTION
133 WHEN NO_DATA_FOUND THEN
134 RETURN(0);
135 WHEN OTHERS THEN
136 RAISE;
137
138 END get_ipv;
139
140
141
142 -- ========================================================================
143 -- get_first_receipt_date
144 --
145 -- Returns the first/earliest receipt date for a particular shipment.
146 -- ========================================================================
147
148 FUNCTION get_first_receipt_date(p_line_location_id NUMBER)
149 RETURN DATE
150 IS
151 v_receipt_date DATE := NULL;
152 BEGIN
153
154 SELECT MIN(transaction_date)
155 INTO v_receipt_date
156 FROM rcv_transactions
157 WHERE po_line_location_id = p_line_location_id
158 AND transaction_type = 'RECEIVE';
159
160 RETURN(v_receipt_date);
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 RETURN(v_receipt_date);
165 WHEN OTHERS THEN
166 RAISE;
167
168 END get_first_receipt_date;
169
170
171
172 -- ========================================================================
173 -- get_last_rcv_trx_date
174 --
175 -- Returns the last transaction date for a particular shipment.
176 -- The dates considered are for:
177 -- - rcv_transactions - last_update_date for any transaction type
178 -- - rcv_shipment_lines - last_update_date
179 -- We should look at last_update_date instead of transaction_date for
180 -- rcv_transactions because the transaction_date can be in the past, but
181 -- the data would not be collected yet since it didn't exist then.
182 --
183 -- If any of these dates changed the data from the Supplier Performance
184 -- source view need to be re-collected.
185 -- ========================================================================
186
187 FUNCTION get_last_rcv_trx_date(p_line_location_id NUMBER)
188 RETURN DATE
189 IS
190 v_max_rcv_trx_date DATE := NULL;
191 v_max_shp_line_date DATE := NULL;
192 v_line_loc_date DATE := NULL;
193 BEGIN
194
195 --
196 -- Get max date from rcv_transactions, including corrections
197 --
198 BEGIN
199 SELECT MAX(last_update_date)
200 INTO v_max_rcv_trx_date
201 FROM rcv_transactions
202 WHERE po_line_location_id = p_line_location_id;
203
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 v_max_rcv_trx_date := NULL;
207 END;
208
209 --
210 -- Get max date from rcv_shipment_lines
211 --
212 BEGIN
213 SELECT MAX(last_update_date)
214 INTO v_max_shp_line_date
215 FROM rcv_shipment_lines
216 WHERE po_line_location_id = p_line_location_id;
217
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 v_max_shp_line_date := NULL;
221 END;
222
223 --
224 -- If there are no receipts, both dates will be NULL,
225 -- fall back on PO shipment last_update_date.
226 -- Returning NULL date here will cause NULL last_update_date
227 -- in the calling view.
228 --
229 BEGIN
230 SELECT last_update_date
231 INTO v_line_loc_date
232 FROM po_line_locations_all
233 WHERE line_location_id = p_line_location_id;
234 END;
235
236 RETURN(GREATEST(NVL(v_max_rcv_trx_date, v_line_loc_date),
237 NVL(v_max_shp_line_date, v_line_loc_date)));
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 RAISE;
242
243 END get_last_rcv_trx_date;
244
245
246
247 -- ========================================================================
248 -- get_qty_shipped
249 --
250 -- Returns the quantity shipped for a particular shipment.
251 -- The quantity is expressed in the shipment transaction UOM.
252 -- ========================================================================
253
254 FUNCTION get_qty_shipped(p_line_location_id NUMBER,
255 p_shipment_uom VARCHAR2)
256 RETURN NUMBER
257 IS
258 v_qty_shipped NUMBER := 0;
259 BEGIN
260
261 SELECT SUM(rsl.quantity_shipped *
262 poa_edw_util.get_uom_rate(rsl.item_id,
263 NULL, -- precision
264 NULL, -- from qty
265 '', -- from UOM code
266 '', -- to UOM code
267 rsl.unit_of_measure, -- from UOM name
268 p_shipment_uom)) -- to UOM name
269 INTO v_qty_shipped
270 FROM rcv_shipment_lines rsl
271 WHERE rsl.po_line_location_id = p_line_location_id;
272
273 RETURN(NVL(v_qty_shipped, 0));
274
275 EXCEPTION
276 WHEN NO_DATA_FOUND THEN
277 RETURN(NVL(v_qty_shipped, 0));
278 WHEN OTHERS THEN
279 RAISE;
280
281 END get_qty_shipped;
282
283
284
285 -- ========================================================================
286 -- get_qty_delivered
287 --
288 -- Returns the quantity delivered to the requestors for a particular shipment.
289 -- The quantity is expressed in the shipment transaction UOM.
290 -- ========================================================================
291
292 FUNCTION get_qty_delivered(p_line_location_id NUMBER)
293 RETURN NUMBER
294 IS
295 v_qty_delivered NUMBER := 0;
296 BEGIN
297
298 --
299 -- Get quantity delivered
300 --
301 SELECT SUM(pod.quantity_delivered)
302 INTO v_qty_delivered
303 FROM po_distributions_all pod
304 WHERE pod.line_location_id = p_line_location_id
305 AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
306
307 RETURN(v_qty_delivered);
308
309 EXCEPTION
310 WHEN NO_DATA_FOUND THEN
311 RETURN(v_qty_delivered);
312 WHEN OTHERS THEN
313 RAISE;
314
315 END get_qty_delivered;
316
317
318
319 -- ========================================================================
320 -- get_qty_received
321 --
322 -- Returns the quantity received for a particular shipment based on the
323 -- the type: ALL - all receipts
324 -- EARLY - receipts received before
325 -- expected date - days early allowed
326 -- LATE - receipts received after
327 -- expected date + days late allowed
328 -- EARLYWINDOW - receipts received within tolerance but before
329 -- the expected date
330 -- LATEWINDOW - receipts received within tolerance but after
331 -- the expected date
332 -- ONDATE - receipts received within tolerance and on the
333 -- expected date
334 -- SUBSTITUTE - receipts w/ substitute item
335 -- The quantity is expressed in the shipment transaction UOM.
336 -- ========================================================================
337
338 FUNCTION get_qty_received(p_type VARCHAR2,
339 p_line_location_id NUMBER,
340 p_expected_date DATE,
341 p_days_early_allowed NUMBER,
342 p_days_late_allowed NUMBER)
343 RETURN NUMBER
344 IS
345 v_transaction_qty NUMBER := 0;
346 v_correction_qty NUMBER := 0;
347 v_qty_received NUMBER := 0;
348 v_type VARCHAR2(20) := p_type;
349 invalid_type EXCEPTION;
350 BEGIN
351
352 IF v_type NOT IN ('ALL', 'EARLY', 'LATE', 'EARLYWINDOW',
353 'LATEWINDOW', 'ONDATE', 'SUBSTITUTE') THEN
354 RAISE invalid_type;
355 END IF;
356
357 --
358 -- For early, late, earlywindow, and latewindow calculation, if there's
359 -- no expected date the receipts are considered on time
360 --
361 IF v_type IN ('EARLY', 'LATE', 'EARLYWINDOW', 'LATEWINDOW') AND
362 p_expected_date IS NULL THEN
363 RETURN(v_qty_received);
364 END IF;
365
366 --
367 -- If there's no expected date, consider the receipts to be
368 -- on time on the expected date, which is equivalent to 'ALL'
369 --
370
371 IF v_type = 'ONDATE' AND p_expected_date IS NULL THEN
372 v_type := 'ALL';
373 END IF;
374
375 --
376 -- Get quantity received
377 -- Get correction to the receipt transactions
378
379 IF p_line_location_id <> l_qty_rec_line_id THEN
380
381 l_qty_rec_line_id := p_line_location_id;
382
383 SELECT
384 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, rct.source_doc_quantity, 0)),
385 sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, rct.source_doc_quantity, 0)),
386 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - trunc(p_expected_date)),-1,rct.source_doc_quantity,0))),
387 sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,rct.source_doc_quantity))),
388 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
389 - trunc(p_expected_date)),0,rct.source_doc_quantity,0)))),
390 sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', rct.source_doc_quantity, 0)),
391 sum(rct.source_doc_quantity),
392 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, 1, 0)),
393 sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, 1, 0)),
394 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - trunc(p_expected_date)),-1,1,0))),
395 sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,1))),
396 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
397 - trunc(p_expected_date)),0,1,0)))),
398 sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', 1, 0)),
399 sum(1)
400 INTO
401 l_qty_rec_cnt_early, l_qty_rec_cnt_late, l_qty_rec_cnt_early_window, l_qty_rec_cnt_late_window, l_qty_rec_cnt_ondate, l_qty_rec_cnt_substitute,
402 l_qty_rec_cnt_all, l_qty_rec_num_early, l_qty_rec_num_late, l_qty_rec_num_early_window, l_qty_rec_num_late_window, l_qty_rec_num_ondate, l_qty_rec_num_substitute, l_qty_rec_num_all
403 FROM rcv_transactions rct
404 WHERE rct.po_line_location_id = p_line_location_id
405 AND rct.transaction_type = 'RECEIVE';
406
407 END IF;
408
409 IF p_line_location_id <> l_qty_rec_cnt_cor_line_id THEN
410
411 l_qty_rec_cnt_cor_line_id:= p_line_location_id;
412
413 SELECT
414 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, rcor.source_doc_quantity, 0)),
415 sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, rcor.source_doc_quantity, 0)),
416 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - p_expected_date),-1,rcor.source_doc_quantity,0))),
417 sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,rcor.source_doc_quantity))),
418 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
419 - trunc(p_expected_date)),0,rct.source_doc_quantity,0)))),
420 sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', rcor.source_doc_quantity, 0)),
421 sum(rcor.source_doc_quantity)
422 INTO
426 WHERE rcor.po_line_location_id = p_line_location_id
423 l_qty_rec_cnt_cor_early, l_qty_rec_cnt_cor_late, l_qty_rec_cnt_cor_early_window, l_qty_rec_cnt_cor_late_window, l_qty_rec_cnt_cor_ondate, l_qty_rec_cnt_cor_substitute, l_qty_rec_cnt_cor_all
424 FROM rcv_transactions rcor,
425 rcv_transactions rct
427 AND rcor.transaction_type = 'CORRECT'
428 AND rct.transaction_id = rcor.parent_transaction_id
429 AND rct.transaction_type = 'RECEIVE';
430
431 END IF;
432
433 IF v_type = 'ALL' THEN
434 v_transaction_qty := l_qty_rec_cnt_all;
435 v_correction_qty := l_qty_rec_cnt_cor_all;
436 END IF;
437
438 IF v_type = 'EARLY' THEN
439 v_transaction_qty := l_qty_rec_cnt_early;
440 v_correction_qty := l_qty_rec_cnt_cor_early;
441 END IF;
442
443 IF v_type = 'LATE' THEN
444 v_transaction_qty := l_qty_rec_cnt_late;
445 v_correction_qty := l_qty_rec_cnt_cor_late;
446 END IF;
447
448 IF v_type = 'EARLYWINDOW' THEN
449 v_transaction_qty := l_qty_rec_cnt_early_window;
450 v_correction_qty := l_qty_rec_cnt_cor_early_window;
451 END IF;
452
453 IF v_type = 'LATEWINDOW' THEN
454 v_transaction_qty := l_qty_rec_cnt_late_window;
455 v_correction_qty := l_qty_rec_cnt_cor_late_window;
456 END IF;
457
458 IF v_type = 'ONDATE' THEN
459 v_transaction_qty := l_qty_rec_cnt_ondate;
460 v_correction_qty := l_qty_rec_cnt_cor_ondate;
461 END IF;
462
463 IF v_type = 'SUBSTITUTE' THEN
464 v_transaction_qty := l_qty_rec_cnt_substitute;
465 v_correction_qty := l_qty_rec_cnt_cor_substitute;
466 END IF;
467
468 v_qty_received := NVL(v_transaction_qty, 0) + NVL(v_correction_qty, 0);
469
470 RETURN(v_qty_received);
471
472 EXCEPTION
473 WHEN NO_DATA_FOUND THEN
474 RETURN(v_qty_received);
475 WHEN invalid_type THEN
476 RAISE;
477 WHEN OTHERS THEN
478 RAISE;
479
480 END get_qty_received;
481
482
483
484 -- ========================================================================
485 -- get_qty_pastdue
486 --
487 -- Returns the quantity past-due for a particular shipment.
488 --
489 -- A shipment has past-due quantity if today is past the expected date
490 -- plus the late days allowed and there are still quantity not received.
491 -- If there is no expected date the shipment will never be past due.
492 --
493 -- The quantity is expressed in the shipment transaction UOM.
494 -- ========================================================================
495
496 FUNCTION get_qty_pastdue(p_line_location_id NUMBER,
497 p_expected_date DATE,
498 p_days_late_allowed NUMBER)
499 RETURN NUMBER
500 IS
501 v_qty_pastdue NUMBER := 0;
502
503 BEGIN
504
505 --
506 -- If there's no expected date the shipment will never be past due.
507 --
508 IF p_expected_date IS NULL THEN
509 RETURN(v_qty_pastdue);
510 END IF;
511
512 --
513 -- Calculate past-due quantity
514 --
515
516 IF sysdate > (p_expected_date + p_days_late_allowed) THEN
517
518 SELECT pll.quantity - pll.quantity_cancelled - pll.quantity_received
519 INTO v_qty_pastdue
520 FROM po_line_locations_all pll
521 WHERE pll.line_location_id = p_line_location_id
522 AND pll.quantity - pll.quantity_cancelled - pll.quantity_received >= 0;
523
524 ELSE
525
526 v_qty_pastdue := 0;
527
528 END IF;
529
530 RETURN(v_qty_pastdue);
531
532 EXCEPTION
533 WHEN OTHERS THEN
534 RAISE;
535
536 END get_qty_pastdue;
537
538
539
540 -- ========================================================================
541 -- get_num_receipts
542 --
543 -- Returns the number of receipts for a particular shipment based on the
544 -- the type: ALL - all receipts
545 -- EARLY - receipts received before
546 -- expected date - days early allowed
547 -- LATE - receipts received after
548 -- expected date + days late allowed
549 -- EARLYWINDOW - receipts received within tolerance but before
550 -- the expected date
551 -- LATEWINDOW - receipts received within tolerance but after
552 -- the expected date
553 -- ONDATE - receipts received within tolerance and on the
554 -- expected date
555 -- SUBSTITUTE - receipts w/ substitute item
556 -- ========================================================================
557
558 FUNCTION get_num_receipts(p_type VARCHAR2,
559 p_line_location_id NUMBER,
560 p_expected_date DATE,
561 p_days_early_allowed NUMBER,
562 p_days_late_allowed NUMBER)
563 RETURN NUMBER
564 IS
565 v_transaction_num NUMBER := 0;
566 v_correction_num NUMBER := 0;
567 v_num_receipts NUMBER := 0;
568 v_type VARCHAR2(20) := p_type;
569 invalid_type EXCEPTION;
570 BEGIN
571
572 IF v_type NOT IN ('ALL', 'EARLY', 'LATE', 'EARLYWINDOW', 'LATEWINDOW',
573 'ONDATE', 'SUBSTITUTE') THEN
577 --
574 RAISE invalid_type;
575 END IF;
576
578 -- For early, late, earlywindow, and latewindow calculation, if there's
579 -- no expected date the receipts are considered on time
580 --
581 IF v_type IN ('EARLY', 'LATE', 'EARLYWINDOW', 'LATEWINDOW') AND
582 p_expected_date IS NULL THEN
583 RETURN(v_num_receipts);
584 END IF;
585
586 IF v_type = 'ONDATE' AND p_expected_date IS NULL THEN
587 v_type := 'ALL';
588 END IF;
589
590 --
591 -- Get number of receipts
592 --
593 IF p_line_location_id <> l_qty_rec_line_id THEN
594
595 l_qty_rec_line_id := p_line_location_id;
596
597 SELECT
598 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, rct.source_doc_quantity, 0)),
599 sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, rct.source_doc_quantity, 0)),
600 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - trunc(p_expected_date)),-1,rct.source_doc_quantity,0))),
601 sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,rct.source_doc_quantity))),
602 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
603 - trunc(p_expected_date)),0,rct.source_doc_quantity,0)))),
604 sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', rct.source_doc_quantity, 0)),
605 sum(rct.source_doc_quantity),
606 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, 1, 0)),
607 sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, 1, 0)),
608 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - trunc(p_expected_date)),-1,1,0))),
609 sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,1))),
610 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
611 - trunc(p_expected_date)),0,1,0)))),
612 sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', 1, 0)),
613 sum(1)
614 INTO
615 l_qty_rec_cnt_early, l_qty_rec_cnt_late, l_qty_rec_cnt_early_window, l_qty_rec_cnt_late_window, l_qty_rec_cnt_ondate, l_qty_rec_cnt_substitute,
616 l_qty_rec_cnt_all, l_qty_rec_num_early, l_qty_rec_num_late, l_qty_rec_num_early_window, l_qty_rec_num_late_window, l_qty_rec_num_ondate, l_qty_rec_num_substitute, l_qty_rec_num_all
617 FROM rcv_transactions rct
618 WHERE rct.po_line_location_id = p_line_location_id
619 AND rct.transaction_type = 'RECEIVE';
620
621 END IF;
622
623 IF v_type = 'ALL' THEN v_transaction_num := l_qty_rec_num_all; END IF;
624 IF v_type = 'EARLY' THEN v_transaction_num := l_qty_rec_num_early; END IF;
625 IF v_type = 'LATE' THEN v_transaction_num := l_qty_rec_num_late; END IF;
626 IF v_type = 'EARLYWINDOW' THEN v_transaction_num := l_qty_rec_num_early_window; END IF;
627 IF v_type = 'LATEWINDOW' THEN v_transaction_num := l_qty_rec_num_late_window; END IF;
628 IF v_type = 'ONDATE' THEN v_transaction_num := l_qty_rec_num_ondate; END IF;
629 IF v_type = 'SUBSTITUTE' THEN v_transaction_num := l_qty_rec_num_substitute; END IF;
630
631 --
632 -- Get correction to the receipt transactions.
633 --
634 -- Since we're counting just the number of receipts, a correction is
635 -- counted only when the quantity in the correction transaction matches
636 -- (the negative of) the quantity in the original receipt transactions.
637 -- In this case the transaction is considered as VOID.
638 --
639
640 IF p_line_location_id <> l_qty_rec_num_cor_line_id THEN
641
642 l_qty_rec_num_cor_line_id := p_line_location_id;
643
644 SELECT
645 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, 1, 0)),
646 sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, 1, 0)),
647 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, 0, decode(sign(rct.transaction_date - p_expected_date), -1, 1, 0))),
648 sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,1))),
649 sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
650 - trunc(p_expected_date)),0,1,0)))),
651 sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', 1, 0)),
652 sum(1)
653 INTO
654 l_qty_rec_num_cor_early, l_qty_rec_num_cor_late, l_qty_rec_num_cor_early_window, l_qty_rec_num_cor_late_window, l_qty_rec_num_cor_ondate, l_qty_rec_num_cor_substitute, l_qty_rec_num_cor_all
655 FROM rcv_transactions rcor,
656 rcv_transactions rct
657 WHERE rcor.po_line_location_id = p_line_location_id
658 AND rcor.transaction_type = 'CORRECT'
659 AND rct.transaction_id = rcor.parent_transaction_id
663
660 AND rct.transaction_type = 'RECEIVE'
661 AND rcor.source_doc_quantity + rct.source_doc_quantity
662 < ZERO_TOLERANCE;
664 END IF;
665
666 IF v_type = 'ALL' THEN v_correction_num := l_qty_rec_num_cor_all; END IF;
667 IF v_type = 'EARLY' THEN v_correction_num := l_qty_rec_num_cor_early; END IF;
668 IF v_type = 'LATE' THEN v_correction_num := l_qty_rec_num_cor_late; END IF;
669 IF v_type = 'EARLYWINDOW' THEN v_correction_num := l_qty_rec_num_cor_early_window; END IF;
670 IF v_type = 'LATEWINDOW' THEN v_correction_num := l_qty_rec_num_cor_late_window; END IF;
671 IF v_type = 'ONDATE' THEN v_correction_num := l_qty_rec_num_cor_ondate; END IF;
672 IF v_type = 'SUBSTITUTE' THEN v_correction_num := l_qty_rec_num_cor_substitute; END IF;
673
674 v_num_receipts := NVL(v_transaction_num, 0) - NVL(v_correction_num, 0);
675
676 RETURN(v_num_receipts);
677
678 EXCEPTION
679 WHEN NO_DATA_FOUND THEN
680 RETURN(v_num_receipts);
681 WHEN invalid_type THEN
682 RAISE;
683 WHEN OTHERS THEN
684 RAISE;
685
686 END get_num_receipts;
687
688
689
690 -- ========================================================================
691 -- find_best_price
692 --
693 -- Returns the best price possible for the shipment line.
694 -- The price is expressed in the shipment transaction currency code.
695 --
696 -- The following documents are checked to find the best possible price:
697 -- - Blanket POs (no consideration for CUMULATIVE/
698 -- NON CUMULATIVE price break)
699 -- - Standard POs
700 -- - Planned POs
701 --
702 -- Date check is against approved_date on the shipment level
703 -- Standard/Planned use a 6 month +/- spread for date check.
704 -- Only Approved POs are considered
705 -- Price = 0 is ignored
706 --
707 -- These documents are matched by:
708 -- - Item
709 -- - UOM
710 -- - Currency
711 --
712 -- If no matching document are found, the best price returned will be
713 -- the actual price of the shipment.
714 -- ========================================================================
715
716 FUNCTION find_best_price(p_line_location_id NUMBER)
717 RETURN NUMBER
718 IS
719 v_best_price NUMBER := NULL;
720 v_price NUMBER := NULL;
721 v_item_id NUMBER := NULL;
722 v_unit_meas_lookup_code VARCHAR2(25) := NULL;
723 v_currency_code VARCHAR2(15) := NULL;
724 v_approved_date DATE := NULL;
725 v_quantity NUMBER := NULL;
726 v_need_by_date DATE := NULL;
727 v_creation_date DATE := NULL;
728
729 x_progress VARCHAR2(3) := NULL;
730
731 BEGIN
732
733 --
734 -- Find the item_id, unit_measure, currency, quantity, approved date
735 -- for this p_line_location_id then pass the above values to each cursor to
736 -- get the best price (so there is one level less join in the cursor)
737 --
738
739 SELECT pol.item_id, pol.unit_meas_lookup_code, poh.currency_code,
740 pll.approved_date, pll.quantity, pll.need_by_date, pll.creation_date INTO
741 v_item_id, v_unit_meas_lookup_code, v_currency_code,
742 v_approved_date, v_quantity, v_need_by_date, v_creation_date
743 FROM po_headers_all poh,
744 po_lines_all pol,
745 po_line_locations_all pll
746 WHERE pll.line_location_id = p_line_location_id
747 AND pll.po_line_id = pol.po_line_id
748 AND pol.po_header_id = poh.po_header_id;
749
750 -- Find the best price
751
752 SELECT min(lowest_price) into v_best_price from
753 (SELECT
754 DECODE( poh.currency_code,
755 v_currency_code,
756 DECODE(pll.shipment_type, 'PRICE BREAK', pll.price_override, 'PLANNED', pll.price_override, pol.unit_price),
757 DECODE( poh.rate_type,
758 'User',
759 DECODE(gsob.currency_code,
760 v_currency_code,
761 poh.rate * DECODE(pll.shipment_type, 'PRICE BREAK', pll.price_override, 'PLANNED', pll.price_override, pol.unit_price),
762 gl_currency_api.convert_amount_sql(
763 gsob.currency_code,
764 v_currency_code,
765 NVL(poh.rate_date, pll.creation_date),
766 NULL,
767 poh.rate * DECODE(pll.shipment_type, 'PRICE BREAK', pll.price_override, 'PLANNED', pll.price_override, pol.unit_price))),
768 gl_currency_api.convert_amount_sql(
769 poh.currency_code,
770 v_currency_code,
771 NVL(poh.rate_date, pll.creation_date),
772 poh.rate_type,
773 DECODE(pll.shipment_type, 'PRICE BREAK', pll.price_override, 'PLANNED', pll.price_override, pol.unit_price))))
774 lowest_price
775 FROM gl_sets_of_books gsob,
776 financials_system_params_all fsp,
777 po_headers_all poh,
778 po_lines_all pol,
779 po_line_locations_all pll
780 WHERE pol.item_id = v_item_id
784 AND pll.po_release_id IS NULL
781 AND pol.unit_meas_lookup_code = v_unit_meas_lookup_code
782 AND ((Nvl(pll.shipment_type,'PRICE BREAK') = 'PRICE BREAK'
783 AND v_approved_date BETWEEN NVL(poh.start_date, Nvl(pll.approved_date,poh.approved_date)) AND NVL(poh.end_date, v_approved_date)
785 AND Nvl(pll.quantity,0) <= v_quantity
786 AND Trunc(Nvl(v_need_by_date, v_creation_date))
787 BETWEEN Trunc(Nvl(pll.start_date, Nvl(v_need_by_date, v_creation_date))) AND Nvl(pll.end_date, Nvl(v_need_by_date, v_creation_date))
788 AND Trunc(v_creation_date) <= Nvl(pol.expiration_date,v_creation_date))
789 OR(pll.shipment_type = 'BLANKET'
790 AND v_approved_date BETWEEN NVL(poh.start_date, pll.approved_date) AND NVL(poh.end_date, v_approved_date)
791 AND pol.unit_price > 0)
792 OR(pll.shipment_type = 'STANDARD'
793 AND v_approved_date BETWEEN (pll.approved_date - 180) AND (pll.approved_date + 180)
794 AND pol.unit_price > 0)
795 OR(pll.shipment_type = 'PLANNED'
796 AND v_approved_date BETWEEN (pll.approved_date - 180) AND (pll.approved_date + 180)
797 AND pol.unit_price > 0))
798 AND pll.approved_flag(+) = 'Y'
799 AND pll.po_line_id(+) = pol.po_line_id
800 AND pol.po_header_id = poh.po_header_id
801 AND NVL(pll.org_id, fsp.org_id) = fsp.org_id
802 AND gsob.set_of_books_id = fsp.set_of_books_id)
803 WHERE lowest_price > 0;
804
805 -- No best price found, set it to the shipment's transaction price
806
807 IF (v_best_price IS NULL) THEN
808
809 x_progress := '005';
810
811 SELECT pll.price_override
812 INTO v_best_price
813 FROM po_line_locations_all pll
814 WHERE line_location_id = p_line_location_id;
815
816 END IF;
817
818 RETURN(v_best_price);
819
820 EXCEPTION
821 WHEN OTHERS THEN
822 RAISE;
823
824 END find_best_price;
825
826
827
828 -- ========================================================================
829 -- get_rcv_txn_qty
830 --
831 -- Returns the quantity received, accepted, or rejected from a shipment line.
832 -- The quantity is expressed in the shipment transaction UOM.
833 -- ========================================================================
834
835 FUNCTION get_rcv_txn_qty(p_line_location_id NUMBER,
836 p_txn_type VARCHAR2)
837 RETURN NUMBER
838 IS
839 v_quantity NUMBER := 0;
840 v_txn_qty NUMBER := 0;
841 v_correction_qty NUMBER := 0;
842 x_progress VARCHAR2(3);
843 invalid_type EXCEPTION;
844 BEGIN
845
846 x_progress := '001';
847
848 IF p_txn_type NOT IN ('RECEIVE', 'ACCEPT', 'REJECT') THEN
849 RAISE invalid_type;
850 END IF;
851
852 x_progress := '002';
853
854 SELECT SUM(source_doc_quantity)
855 INTO v_txn_qty
856 FROM rcv_transactions
857 WHERE po_line_location_id = p_line_location_id
858 AND transaction_type = p_txn_type;
859
860 x_progress := '003';
861
862 SELECT SUM(rcor.source_doc_quantity)
863 INTO v_correction_qty
864 FROM rcv_transactions rcor,
865 rcv_transactions rct
866 WHERE rcor.po_line_location_id = p_line_location_id
867 AND rcor.transaction_type = 'CORRECT'
868 AND rct.transaction_id = rcor.parent_transaction_id
869 AND rct.transaction_type = p_txn_type;
870
871 v_quantity := NVL(v_txn_qty, 0) + NVL(v_correction_qty, 0);
872
873 RETURN(v_quantity);
874
875 EXCEPTION
876 WHEN NO_DATA_FOUND THEN
877 RETURN(v_quantity);
878 WHEN invalid_type THEN
879 RAISE;
880 WHEN OTHERS THEN
881 RAISE;
882
883 END get_rcv_txn_qty;
884
885 END poa_edw_supperf;
[Home] [Help]