[Home] [Help]
PACKAGE BODY: APPS.RCV_INVOICE_MATCHING_SV
Source
1 PACKAGE BODY RCV_INVOICE_MATCHING_SV AS
2 /* $Header: RCVITRMB.pls 120.3 2011/10/25 19:20:13 vthevark ship $*/
3
4 /*====================== RCV_INVOICE_MATCHING_SV ===================*/
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'RCV_INVOICE_MATCHING_SV';
7
8 PROCEDURE get_quantities ( top_transaction_id IN NUMBER,
9 ordered_po_qty IN OUT NOCOPY NUMBER,
10 cancelled_po_qty IN OUT NOCOPY NUMBER,
11 received_po_qty IN OUT NOCOPY NUMBER,
12 corrected_po_qty IN OUT NOCOPY NUMBER,
13 delivered_po_qty IN OUT NOCOPY NUMBER,
14 rtv_po_qty IN OUT NOCOPY NUMBER,
15 billed_po_qty IN OUT NOCOPY NUMBER,
16 accepted_po_qty IN OUT NOCOPY NUMBER,
17 rejected_po_qty IN OUT NOCOPY NUMBER,
18 ordered_txn_qty IN OUT NOCOPY NUMBER,
19 cancelled_txn_qty IN OUT NOCOPY NUMBER,
20 received_txn_qty IN OUT NOCOPY NUMBER,
21 corrected_txn_qty IN OUT NOCOPY NUMBER,
22 delivered_txn_qty IN OUT NOCOPY NUMBER,
23 rtv_txn_qty IN OUT NOCOPY NUMBER,
24 billed_txn_qty IN OUT NOCOPY NUMBER,
25 accepted_txn_qty IN OUT NOCOPY NUMBER,
26 rejected_txn_qty IN OUT NOCOPY NUMBER) IS
27
28 X_progress VARCHAR2(3) := '000';
29
30 X_primary_uom VARCHAR2(25) := '';
31 X_txn_uom VARCHAR2(25) := '';
32 X_po_uom VARCHAR2(25) := '';
33 X_pr_to_txn_rate NUMBER := 1;
34 X_pr_to_po_rate NUMBER := 1;
35 X_po_to_txn_rate NUMBER := 1;
36 X_item_id NUMBER := 0;
37 X_line_location_id NUMBER := 0;
38 X_distribution_id NUMBER := 0; --Bug 10074319
39 X_received_quantity NUMBER := 0;
40 X_corrected_quantity NUMBER := 0;
41 X_delivered_quantity NUMBER := 0;
42 X_rtv_quantity NUMBER := 0;
43 X_accepted_quantity NUMBER := 0;
44 X_rejected_quantity NUMBER := 0;
45
46 v_primary_uom VARCHAR2(25) := '';
47 v_po_uom VARCHAR2(25) := '';
48 v_txn_uom VARCHAR2(25) := '';
49 v_txn_id NUMBER := 0;
50 v_primary_quantity NUMBER := 0;
51 v_transaction_type VARCHAR2(25) := '';
52 v_parent_id NUMBER := 0;
53 v_parent_type VARCHAR2(25) := '';
54 v_shipment_line_id NUMBER := 0;
55 v_line_location_id NUMBER := 0;
56 v_distribution_id NUMBER := 0; --Bug 10074319
57 /* Bug 2033579 Added two variables to store grand parent type and id */
58 grand_parent_type VARCHAR2(25) := '';
59 grand_parent_id NUMBER := 0;
60
61 /* This cursor recursively query up all the children of the
62 ** top transaction (RECEIVE or MATCH)
63 */
64 -- Bug 6115619
65 p_api_version NUMBER := 1.0;
66 x_skip_status VARCHAR2(1);
67 x_msg_count NUMBER;
68 x_msg_data VARCHAR2(2400);
69 x_return_status VARCHAR2(10);
70
71 CURSOR c_txn_history (c_transaction_id NUMBER) IS
72 SELECT
73 transaction_id,
74 primary_quantity,
75 primary_unit_of_measure,
76 unit_of_measure,
77 source_doc_unit_of_measure,
78 transaction_type,
79 shipment_line_id,
80 po_line_location_id,
81 po_distribution_id, --Bug 10074319
82 parent_transaction_id
83 FROM
84 rcv_transactions
85 START WITH transaction_id = c_transaction_id
86 CONNECT BY parent_transaction_id = PRIOR transaction_id;
87
88 BEGIN
89 -- return if invalid input parameters
90
91 IF top_transaction_id IS NULL THEN
92
93 RETURN;
94
95 END IF;
96
97 OPEN c_txn_history(top_transaction_id);
98
99 X_progress := '001';
100
101 LOOP
102 FETCH c_txn_history INTO v_txn_id,
103 v_primary_quantity,
104 v_primary_uom,
105 v_txn_uom,
106 v_po_uom,
107 v_transaction_type,
108 v_shipment_line_id,
109 v_line_location_id,
110 v_distribution_id, --Bug 10074319
111 v_parent_id;
112
113 EXIT WHEN c_txn_history%NOTFOUND;
114
115 X_progress := '002';
116
117 IF v_transaction_type = 'RECEIVE' OR v_transaction_type = 'MATCH' THEN
118
119 /* Find out the item_id for UOM conversion */
120
121 SELECT
122 item_id
123 INTO
124 X_item_id
125 FROM
126 rcv_shipment_lines
127 WHERE
128 shipment_line_id = v_shipment_line_id;
129
130 X_received_quantity := v_primary_quantity;
131 X_line_location_id := v_line_location_id;
132 X_distribution_id := v_distribution_id; --Bug 10074319
133 X_primary_uom := v_primary_uom;
134 X_txn_uom := v_txn_uom;
135 X_po_uom := v_po_uom;
136
137 /* Bug 6115619.With skip lot enable while creating an invoice with match to receipt
138 and match level as 4 way,accepted quantity was not calculated properly because for skipped transactions RT is not created.*/
139 BEGIN
140 QA_SKIPLOT_RCV_GRP.IS_LOT_SKIPPED(p_api_version =>p_api_version,
141 p_transaction_id =>top_transaction_id,
142 x_skip_status=>x_skip_status,
143 x_return_status=>x_return_status,
144 x_msg_count=>x_msg_count,
145 x_msg_data=>x_msg_data);
146
147 IF x_return_status = 'S' THEN
148 IF x_skip_status = 'T' THEN
149 X_accepted_quantity := X_received_quantity;
150 END IF;
151 END IF;
152 EXCEPTION
153 WHEN OTHERS then
154 po_message_s.sql_error('Error in call to QA_SKIPLOT_RCV_GRP.IS_LOT_SKIPPED', X_progress, sqlcode);
155 raise;
156 END;
157 -- End Bug 6115619
158 ELSIF v_transaction_type = 'RETURN TO VENDOR' THEN
159
160 /*Bug2033579 When Performing 'Return to vendor' on Accept or Reject Transaction
161 accepted quantity and Rejected quantity are not calculated correctly
162 */
163
164 SELECT
165 transaction_type
166 INTO
167 v_parent_type
168 FROM
169 rcv_transactions
170 WHERE
171 transaction_id = v_parent_id;
172
173 if v_parent_type = 'ACCEPT' THEN
174 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
175 end if;
176
177 if v_parent_type = 'REJECT' THEN
178 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
179 end if;
180
181 X_rtv_quantity := X_rtv_quantity + v_primary_quantity;
182
183 -- Bug 6115619
184 IF v_parent_type IN ('RECEIVE','RETURN TO RECEIVING') AND x_skip_status ='T' THEN
185 x_accepted_quantity := x_accepted_quantity - v_primary_quantity; -- saravanan
186 END IF;
187
188 ELSIF v_transaction_type = 'DELIVER' THEN
189
190 X_delivered_quantity := X_delivered_quantity + v_primary_quantity;
191
192 ELSIF v_transaction_type = 'ACCEPT' THEN
193
194 /*Bug 2033579 Accept quantity is not determined correctly when received goods
195 are inspected more than once by pressing Inspection button and quantity is
196 accepted.
197 */
198 SELECT
199 transaction_type
200 INTO
201 v_parent_type
202 FROM
203 rcv_transactions
204 WHERE
205 transaction_id = v_parent_id;
206
207 if v_parent_type <> 'ACCEPT' THEN
208 X_accepted_quantity := X_accepted_quantity + v_primary_quantity;
209 end if;
210
211 if v_parent_type = 'REJECT' THEN
212 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
213 end if;
214
215 ELSIF v_transaction_type = 'REJECT' THEN
216 /*Bug 2033579 Reject quantity is not determined correctly when received goods
217 are inspected more than once by pressing Inspection button and quantity is
218 Rejected.
219 */
220 SELECT
221 transaction_type
222 INTO
223 v_parent_type
224 FROM
225 rcv_transactions
226 WHERE
227 transaction_id = v_parent_id;
228
229 if v_parent_type <> 'REJECT' then
230 X_rejected_quantity := X_rejected_quantity + v_primary_quantity;
231 end if;
232 if v_parent_type = 'ACCEPT' then
233 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
234 end if;
235
236 ELSIF v_transaction_type = 'RETURN TO RECEIVING' THEN
237
238 X_delivered_quantity := X_delivered_quantity - v_primary_quantity;
239
240 ELSIF v_transaction_type = 'CORRECT' THEN
241
242 /* The correction function is based on parent transaction type */
243
244 SELECT
245 transaction_type,parent_transaction_id
246 INTO
247 v_parent_type,grand_parent_id
248 FROM
249 rcv_transactions
250 WHERE
251 transaction_id = v_parent_id;
252
253 /*Bug 2288641 Handling the No data found exception becuase when correction is
254 done on a Receipt grand_parent_id will be -1 in that case the sql below will
255 not return any records which shouldn't be treated as an error.
256 */
257
258 BEGIN
259
260 SELECT
261 transaction_type
262 INTO
263 grand_parent_type
264 FROM
265 rcv_transactions
266 WHERE
267 transaction_id = grand_parent_id;
268 EXCEPTION
269
270 WHEN NO_DATA_FOUND THEN
271 NULL;
272
273 END;
274
275 IF v_parent_type = 'RECEIVE' OR v_parent_type = 'MATCH' THEN
276
277 X_corrected_quantity := X_corrected_quantity + v_primary_quantity;
278
279 -- Bug 6115619
280 IF x_skip_status ='T' THEN
281 x_accepted_quantity := x_accepted_quantity + v_primary_quantity;
282 END IF;
283
284
285 ELSIF v_parent_type = 'RETURN TO VENDOR' THEN
286 /*Bug 2033579 When performing a correction on Return to Vendor and if its grand
287 parent is accept or Reject Transaction then the accepted or rejected quantity
288 will be adjusted accordingly.
289 */
290
291 if grand_parent_type = 'ACCEPT' THEN
292 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
293 end if;
294
295 if grand_parent_type = 'REJECT' THEN
296 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
297 end if;
298
299 X_rtv_quantity := X_rtv_quantity + v_primary_quantity;
300
301 -- Bug 6115619
302 IF grand_parent_type = 'RECEIVE' AND x_skip_status ='T' THEN
303 x_accepted_quantity := x_accepted_quantity - v_primary_quantity;
304 END IF;
305
306 ELSIF v_parent_type = 'DELIVER' THEN
307
308 X_delivered_quantity := X_delivered_quantity + v_primary_quantity;
309
310 ELSIF v_parent_type = 'ACCEPT' THEN
311 /*Bug 2033579 When performing a correction on Accept transaction and if its
312 grand parent is Reject transaction then the Rejected quantity will be adjusted*/
313
314 if grand_parent_type = 'REJECT' THEN
315 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
316 end if;
317
318 /* Bug 4038533: When performing a correction on ACCEPT transaction if grand parent
319 ** transaction is ACCEPT then the accepted qty should not be adjusted.
320 */
321
322 if grand_parent_type <> 'ACCEPT' THEN
323 X_accepted_quantity := X_accepted_quantity + v_primary_quantity;
324 end if;
325
326 ELSIF v_parent_type = 'REJECT' THEN
327 /*Bug 2033579 When performing a correction on Reject Transaction and if its
328 grand parent is Accept transaction then the accepted quantity will be
329 adjusted.
330 */
331 if grand_parent_type = 'ACCEPT' THEN
332 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
333 end if;
334
335 /* Bug 4038533: When performing a correction on REJECT transaction if grand parent
336 ** transaction is REJECT then the rejected qty should not be adjusted.
337 */
338
339 if grand_parent_type <> 'REJECT' THEN
340 X_rejected_quantity := X_rejected_quantity + v_primary_quantity;
341 end if;
342
343 ELSIF v_parent_type = 'RETURN TO RECEIVING' THEN
344
345 X_delivered_quantity := X_delivered_quantity - v_primary_quantity;
346
347 END IF;
348 END IF;
349
350 END LOOP;
351
352 CLOSE c_txn_history;
353
354 X_progress := '003';
355
356 /* Get the orderd, billed quantity from PO */
357 /* Bug 10074319 if match to specific distributions, then get info from POD*/
358 IF X_distribution_id IS NOT NULL THEN
359 SELECT
360 pod.quantity_ordered,
361 pod.quantity_cancelled,
362 rt.quantity_billed -- This quantity is in transaction UOM
363 INTO
364 ordered_po_qty,
365 cancelled_po_qty,
366 billed_txn_qty
367 FROM
368 po_distributions pod,
369 rcv_transactions rt
370 WHERE
371 rt.transaction_id = top_transaction_id and
372 rt.po_distribution_id = pod.po_distribution_id;
373 ELSIF X_line_location_id IS NOT NULL THEN
374 SELECT
375 ps.quantity,
376 ps.quantity_cancelled,
377 rt.quantity_billed -- This quantity is in transaction UOM
378 INTO
379 ordered_po_qty,
380 cancelled_po_qty,
381 billed_txn_qty
382 FROM
383 po_line_locations ps,
384 rcv_transactions rt
385 WHERE
386 rt.transaction_id = top_transaction_id and
387 rt.po_line_location_id = ps.line_location_id;
388 ELSE
389 ordered_po_qty := 0;
390 billed_txn_qty := 0;
391 END IF;
392
393 X_progress := '004';
394
395 /* Get UOM conversion rates */
396
397 X_pr_to_po_rate := po_uom_s.po_uom_convert(X_primary_uom, X_po_uom, X_item_id);
398 X_pr_to_txn_rate := po_uom_s.po_uom_convert(X_primary_uom, X_txn_uom, X_item_id);
399 X_po_to_txn_rate := po_uom_s.po_uom_convert(X_po_uom, X_txn_uom, X_item_id);
400
401 X_progress := '005';
402
403 /* Calculate the quantity with uom info */
404 corrected_po_qty := X_pr_to_po_rate * X_corrected_quantity;
405 delivered_po_qty := X_pr_to_po_rate * X_delivered_quantity;
406 rtv_po_qty := X_pr_to_po_rate * X_rtv_quantity;
407 accepted_po_qty := X_pr_to_po_rate * X_accepted_quantity;
408 rejected_po_qty := X_pr_to_po_rate * X_rejected_quantity;
409
410 billed_po_qty := billed_txn_qty / X_po_to_txn_rate; -- txn to po rate is inverse of
411 -- X_po_to_txn_rate
412
413 ordered_txn_qty := X_po_to_txn_rate * ordered_po_qty;
414 cancelled_txn_qty := X_po_to_txn_rate * cancelled_po_qty;
415
416 received_txn_qty := X_pr_to_txn_rate * X_received_quantity;
417 corrected_txn_qty := X_pr_to_txn_rate * X_corrected_quantity;
418 delivered_txn_qty := X_pr_to_txn_rate * X_delivered_quantity;
419 rtv_txn_qty := X_pr_to_txn_rate * X_rtv_quantity;
420 accepted_txn_qty := X_pr_to_txn_rate * X_accepted_quantity;
421 rejected_txn_qty := X_pr_to_txn_rate * X_rejected_quantity;
422
423 X_progress := '006';
424
425 /* Bug 2964160 need to round off the quantities before passing them out
426 Bug 10074319 rounding to 9 decimals to match the standard precision in RCV */
427 ordered_po_qty := round(ordered_po_qty,9);
428 cancelled_po_qty := round(cancelled_po_qty,9);
429 received_po_qty := round(received_po_qty,9);
430 corrected_po_qty := round(corrected_po_qty,9);
431 delivered_po_qty := round(delivered_po_qty,9);
432 rtv_po_qty := round(rtv_po_qty,9);
433 billed_po_qty := round(billed_po_qty,9);
434 accepted_po_qty := round(accepted_po_qty,9);
435 rejected_po_qty := round(rejected_po_qty,9);
436 ordered_txn_qty := round(ordered_txn_qty,9);
437 cancelled_txn_qty := round(cancelled_txn_qty,9);
438 received_txn_qty := round(received_txn_qty,9);
439 corrected_txn_qty := round(corrected_txn_qty,9);
440 delivered_txn_qty := round(delivered_txn_qty,9);
441 rtv_txn_qty := round(rtv_txn_qty,9);
442 billed_txn_qty := round(billed_txn_qty,9);
443 accepted_txn_qty := round(accepted_txn_qty,9);
444 rejected_txn_qty := round(rejected_txn_qty,9);
445
446 EXCEPTION
447
448 when others then
449 po_message_s.sql_error('get_transaction_quantities', X_progress, sqlcode);
450 raise;
451
452 END get_quantities;
453
454 PROCEDURE get_delivered_quantity( rcv_transaction_id IN NUMBER,
455 p_distribution_id IN NUMBER,
456 ordered_po_qty IN OUT NOCOPY NUMBER,
457 cancelled_po_qty IN OUT NOCOPY NUMBER,
458 delivered_po_qty IN OUT NOCOPY NUMBER,
459 returned_po_qty IN OUT NOCOPY NUMBER,
460 corrected_po_qty IN OUT NOCOPY NUMBER,
461 ordered_txn_qty IN OUT NOCOPY NUMBER,
462 cancelled_txn_qty IN OUT NOCOPY NUMBER,
463 delivered_txn_qty IN OUT NOCOPY NUMBER,
464 returned_txn_qty IN OUT NOCOPY NUMBER,
465 corrected_txn_qty IN OUT NOCOPY NUMBER) IS
466
467 X_progress VARCHAR2(3) := '000';
468
469 X_primary_uom VARCHAR2(25) := '';
470 X_txn_uom VARCHAR2(25) := '';
471 X_po_uom VARCHAR2(25) := '';
472 X_pr_to_txn_rate NUMBER := 1;
473 X_pr_to_po_rate NUMBER := 1;
474 X_po_to_txn_rate NUMBER := 1;
475 X_item_id NUMBER := 0;
476 X_corrected_quantity NUMBER := 0;
477 X_delivered_quantity NUMBER := 0;
478 X_returned_quantity NUMBER := 0;
479
480 X_deliver_txn_id NUMBER := 0;
481
482 v_txn_id NUMBER := 0;
483 v_primary_quantity NUMBER := 0;
484 v_transaction_type VARCHAR2(25) := '';
485 v_parent_id NUMBER := 0;
486 v_parent_type VARCHAR2(25) := '';
487
488 /* This cursor recursively query up all the children of the
489 ** top transaction (DELIVER)
490 */
491
492 CURSOR c_txn_history (c_transaction_id NUMBER) IS
493 SELECT
494 transaction_id,
495 primary_quantity,
496 transaction_type,
497 parent_transaction_id
498 FROM
499 rcv_transactions
500 START WITH transaction_id = c_transaction_id
501 CONNECT BY parent_transaction_id = PRIOR transaction_id;
502
503 /* This cursor query up the all the deliver transactions */
504
505 CURSOR c_deliver_txn (c_transaction_id NUMBER, c_distribution_id NUMBER) IS
506 SELECT
507 transaction_id
508 FROM
509 rcv_transactions
510 WHERE
511 transaction_type = 'DELIVER' AND
512 po_distribution_id = c_distribution_id
513 START WITH transaction_id = c_transaction_id
514 CONNECT BY parent_transaction_id = PRIOR transaction_id
515 AND PRIOR transaction_type <> 'DELIVER';
516
517 BEGIN
518
519 -- return if invalid input parameters
520
521 IF rcv_transaction_id IS NULL or p_distribution_id IS NULL THEN
522
523 RETURN;
524
525 END IF;
526
527 /* Query up UOM info */
528
529 SELECT
530 sl.item_id,
531 rt.primary_unit_of_measure,
532 rt.source_doc_unit_of_measure,
533 rt.unit_of_measure
534 INTO
535 X_item_id,
536 X_primary_uom,
537 X_po_uom,
538 X_txn_uom
539 FROM
540 rcv_shipment_lines sl,
541 rcv_transactions rt
542 WHERE
543 rt.transaction_id = rcv_transaction_id AND
544 rt.shipment_line_id = sl.shipment_line_id;
545
546 X_progress := '001';
547
548 OPEN c_deliver_txn(rcv_transaction_id, p_distribution_id);
549
550 LOOP
551
552 FETCH c_deliver_txn INTO X_deliver_txn_id;
553
554 EXIT WHEN c_deliver_txn%NOTFOUND;
555
556 X_progress := '002';
557
558 OPEN c_txn_history(X_deliver_txn_id);
559
560 X_progress := '003';
561
562 LOOP
563 FETCH c_txn_history INTO v_txn_id,
564 v_primary_quantity,
565 v_transaction_type,
566 v_parent_id;
567
568 EXIT WHEN c_txn_history%NOTFOUND;
569
570 X_progress := '004';
571
572 IF v_transaction_type = 'DELIVER' THEN
573
574 X_delivered_quantity := X_delivered_quantity + v_primary_quantity;
575
576 ELSIF v_transaction_type = 'RETURN TO RECEIVING' THEN
577
578 X_returned_quantity := X_returned_quantity + v_primary_quantity;
579
580 ELSIF v_transaction_type = 'CORRECT' THEN
581
582 /* The correction function is based on parent transaction type */
583
584 SELECT
585 transaction_type
586 INTO
587 v_parent_type
588 FROM
589 rcv_transactions
590 WHERE
591 transaction_id = v_parent_id;
592
593 IF v_parent_type = 'DELIVER' THEN
594
595 X_corrected_quantity := X_corrected_quantity + v_primary_quantity;
596
597 ELSIF v_parent_type = 'RETURN TO RECEIVING' THEN
598
599 X_returned_quantity := X_returned_quantity + v_primary_quantity;
600
601 END IF;
602 END IF;
603
604 END LOOP;
605
606 CLOSE c_txn_history;
607
608 END LOOP;
609
610 CLOSE c_deliver_txn;
611
612 X_progress := '005';
613
614 SELECT
615 pd.quantity_ordered,
616 pd.quantity_cancelled
617 INTO
618 ordered_po_qty,
619 cancelled_po_qty
620 FROM
621 po_distributions pd
622 WHERE
623 pd.po_distribution_id = p_distribution_id;
624
625 /* Get UOM conversion rates */
626
627 X_progress := '006';
628
629 X_pr_to_po_rate := po_uom_s.po_uom_convert(X_primary_uom, X_po_uom, X_item_id);
630 X_pr_to_txn_rate := po_uom_s.po_uom_convert(X_primary_uom, X_txn_uom, X_item_id);
631 X_po_to_txn_rate := po_uom_s.po_uom_convert(X_po_uom, X_txn_uom, X_item_id);
632
633 X_progress := '007';
634
635 /* Calculate the quantity with uom info */
636
637 delivered_po_qty := X_pr_to_po_rate * X_delivered_quantity;
638 returned_po_qty := X_pr_to_po_rate * X_returned_quantity;
639 corrected_po_qty := X_pr_to_po_rate * X_corrected_quantity;
640
641 ordered_txn_qty := X_po_to_txn_rate * ordered_po_qty;
642 cancelled_txn_qty := X_po_to_txn_rate * cancelled_po_qty;
643
644 delivered_txn_qty := X_pr_to_txn_rate * X_delivered_quantity;
645 returned_txn_qty := X_pr_to_txn_rate * X_returned_quantity;
646 corrected_txn_qty := X_pr_to_txn_rate * X_corrected_quantity;
647
648 EXCEPTION
649
650 when others then
651 po_message_s.sql_error('get_delivered_quantity', X_progress, sqlcode);
652 raise;
653
654 END get_delivered_quantity;
655
656 PROCEDURE Get_ReceiveAmount
657 ( p_api_version IN NUMBER,
658 p_init_msg_list IN VARCHAR2,
659 x_return_status OUT NOCOPY VARCHAR2,
660 x_msg_count OUT NOCOPY NUMBER,
661 x_msg_data OUT NOCOPY VARCHAR2,
662 p_receive_transaction_id IN NUMBER, -- RECEIVE Transaction id
663 x_billed_amt OUT NOCOPY NUMBER, -- rcv_transactions.amount_billed
664 x_received_amt OUT NOCOPY NUMBER, -- amount from rcv transactions table for the corresponding RECEIVE transaction
665 x_delivered_amt OUT NOCOPY NUMBER, -- amount from rcv transactions table for the corresponding DELIVER transaction
666 x_corrected_amt OUT NOCOPY NUMBER -- amount from rcv transactions table for the corresponding CORRECT transaction
667 ) IS
668 l_api_name CONSTANT VARCHAR2(30) := 'Get_ReceiveAmount';
669 l_api_version CONSTANT NUMBER := 1.0;
670
671 X_progress VARCHAR2(3) := '000';
672 l_parent_type VARCHAR2(25) := '';
673
674 -- This cursor recursively query up all the children of the RECEIVE transaction
675 CURSOR c_txn_history (c_transaction_id NUMBER) IS
676 SELECT
677 transaction_id,
678 amount,
679 amount_billed,
680 transaction_type,
681 shipment_line_id,
682 po_line_location_id,
683 parent_transaction_id,
684 PRIOR transaction_type parent_transaction_type
685 FROM
686 rcv_transactions
687 START WITH transaction_id = c_transaction_id
688 CONNECT BY parent_transaction_id = PRIOR transaction_id;
689
690 BEGIN
691 -- standard call to check for call compatibility
692 IF NOT FND_API.Compatible_API_Call( l_api_version,
693 p_api_version,
694 l_api_name,
695 G_PKG_NAME ) THEN
696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
697 END IF;
698
699 --initialize message list if p_init_msg_list is set
700 IF FND_API.To_Boolean( p_init_msg_list ) THEN
701 FND_MSG_PUB.initialize;
702 END IF;
703
704 --initialize return status to true
705 x_return_status := FND_API.G_RET_STS_SUCCESS;
706
707 -- return if invalid input parameters
708 IF p_receive_transaction_id IS NULL THEN
709 x_return_status := FND_API.G_RET_STS_ERROR;
710 RETURN;
711 END IF;
712
713 X_progress := '001';
714
715 -- loop through all child transactions
716 FOR l_transaction_record IN c_txn_history(p_receive_transaction_id)
717 LOOP
718
719 X_progress := '004';
720
721 IF l_transaction_record.transaction_type = 'RECEIVE' OR l_transaction_record.transaction_type = 'MATCH' THEN
722 x_received_amt := nvl(x_received_amt,0) + nvl(l_transaction_record.amount,0);
723 x_billed_amt := nvl(x_billed_amt,0) + nvl(l_transaction_record.amount_billed,0);
724
725 ELSIF l_transaction_record.transaction_type = 'DELIVER' THEN
726 x_delivered_amt := nvl(x_delivered_amt,0) + nvl(l_transaction_record.amount,0);
727 ELSIF l_transaction_record.transaction_type = 'CORRECT' THEN
728 IF l_transaction_record.parent_transaction_type = 'RECEIVE' OR
729 l_transaction_record.parent_transaction_type = 'MATCH' THEN
730 x_corrected_amt := nvl(x_corrected_amt,0) + nvl(l_transaction_record.amount,0);
731 END IF;
732 END IF;
733
734 END LOOP; -- c_txn_history
735
736 X_progress := '005';
737
738 EXCEPTION
739 WHEN OTHERS THEN
740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
741 po_message_s.sql_error('Get_DeliverAmount', X_progress, sqlcode);
742 RAISE;
743
744 END Get_ReceiveAmount;
745
746 PROCEDURE Get_DeliverAmount
747 ( p_api_version IN NUMBER,
748 p_init_msg_list IN VARCHAR2,
749 x_return_status OUT NOCOPY VARCHAR2,
750 x_msg_count OUT NOCOPY NUMBER,
751 x_msg_data OUT NOCOPY VARCHAR2,
752 p_receive_transaction_id IN NUMBER, -- Transaction id of the root RECEIVE transaction
753 p_po_distribution_id IN NUMBER,
754 x_delivered_amt OUT NOCOPY NUMBER, -- amount from rcv transactions table for the child DELIVER transactions
755 x_corrected_amt OUT NOCOPY NUMBER -- amount from rcv transactions table for the child CORRECT transactions
756 ) IS
757 l_api_name CONSTANT VARCHAR2(30) := 'Get_DeliverAmount';
758 l_api_version CONSTANT NUMBER := 1.0;
759
760 X_progress VARCHAR2(3) := '000';
761
762 /* This cursor recursively query up all the children of the
763 ** top transaction (DELIVER)
764 */
765 CURSOR c_txn_history (c_transaction_id NUMBER) IS
766 SELECT
767 transaction_id,
768 amount,
769 transaction_type,
770 parent_transaction_id,
771 PRIOR transaction_type parent_transaction_type
772 FROM
773 rcv_transactions
774 START WITH transaction_id = c_transaction_id
775 CONNECT BY parent_transaction_id = PRIOR transaction_id;
776
777 /* This cursor query up the all the deliver transactions */
778 CURSOR c_deliver_txn (c_transaction_id NUMBER, c_distribution_id NUMBER) IS
779 SELECT
780 transaction_id
781 FROM
782 rcv_transactions
783 WHERE
784 transaction_type = 'DELIVER' AND
785 po_distribution_id = c_distribution_id
786 START WITH transaction_id = c_transaction_id
787 CONNECT BY parent_transaction_id = PRIOR transaction_id
788 AND PRIOR transaction_type <> 'DELIVER';
789
790 BEGIN
791 -- standard call to check for call compatibility
792 IF NOT FND_API.Compatible_API_Call( l_api_version,
793 p_api_version,
794 l_api_name,
795 G_PKG_NAME ) THEN
796 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
797 END IF;
798
799 --initialize message list if p_init_msg_list is set
800 IF FND_API.To_Boolean( p_init_msg_list ) THEN
801 FND_MSG_PUB.initialize;
802 END IF;
803
804 --initialize return status to true
805 x_return_status := FND_API.G_RET_STS_SUCCESS;
806
807 -- return if invalid input parameters
808 IF p_receive_transaction_id IS NULL OR p_po_distribution_id IS NULL THEN
809 x_return_status := FND_API.G_RET_STS_ERROR;
810 RETURN;
811 END IF;
812
813 X_progress := '001';
814
815 FOR l_deliver_record IN c_deliver_txn (p_receive_transaction_id, p_po_distribution_id)
816 LOOP
817
818 X_progress := '002';
819
820 FOR l_transaction_record IN c_txn_history (l_deliver_record.transaction_id)
821 LOOP
822
823 X_progress := '004';
824
825 IF l_transaction_record.transaction_type = 'DELIVER' THEN
826 x_delivered_amt := nvl(x_delivered_amt,0) + nvl(l_transaction_record.amount,0);
827 ELSIF l_transaction_record.transaction_type = 'CORRECT' AND l_transaction_record.parent_transaction_type = 'DELIVER' THEN
828 x_corrected_amt := nvl(x_corrected_amt,0) + nvl(l_transaction_record.amount,0);
829 END IF;
830
831 END LOOP; -- c_txn_history
832
833 END LOOP; -- c_deliver_txn
834
835 X_progress := '005';
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
840 po_message_s.sql_error('Get_DeliverAmount', X_progress, sqlcode);
841 RAISE;
842
843 END Get_DeliverAmount;
844
845 END RCV_INVOICE_MATCHING_SV;