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