[Home] [Help]
PACKAGE BODY: APPS.AP_RETRO_PRICING_UTIL_PKG
Source
1 PACKAGE BODY AP_RETRO_PRICING_UTIL_PKG AS
2 /* $Header: apretrub.pls 120.16.12020000.2 2012/11/26 07:12:11 sbonala ship $ */
3
4
5 /*=============================================================================
6 | FUNCTION - Are_Original_Invoices_Valid()
7 |
8 | DESCRIPTION
9 | This function checks for a particular instruction if all the base
10 | matched Invoices(along with Price Corrections,Qty Corrections and the
11 | previously existing(If Any) Retro Price Adjustments Documents ) for the
12 | retropriced shipments(Records in AP_INVOICE_LINES_INTERFACE) are VALID
13 |
14 | PARAMETERS
15 | p_instruction_id
16 | p_org_id
17 } p_orig_invoices_valid --OUT
18 |
19 | MODIFICATION HISTORY
20 | Date Author Description of Change
21 | 29-JUL-2003 dgulraja Creation
22 |
23 *============================================================================*/
24 FUNCTION Are_Original_Invoices_Valid(
25 p_instruction_id IN NUMBER,
26 p_org_id IN NUMBER,
27 p_orig_invoices_valid OUT NOCOPY VARCHAR2)
28 RETURN BOOLEAN IS
29
30 l_count NUMBER := 0;
31 debug_info VARCHAR2(1000);
32
33 BEGIN
34
35 debug_info := 'Are Original Invoices Valid';
36 SELECT count(*)
37 INTO l_count
38 FROM ap_invoice_lines_interface IL,
39 ap_invoice_lines_all L
40 WHERE IL.invoice_id = p_instruction_id
41 AND IL.po_line_location_id = L.po_line_location_id
42 AND L.org_id = p_org_id
43 AND L.match_type IN ('ITEM_TO_PO', 'ITEM_TO_RECEIPT',
44 'QTY_CORRECTION', 'PRICE_CORRECTION',
45 'PO_PRICE_ADJUSTMENT', 'ADJUSTMENT_CORRECTION')
46 AND L.discarded_flag <> 'Y'
47 AND L.cancelled_flag <> 'Y'
48 AND (NVL(L.generate_dists, 'Y') <> 'D' OR
49 EXISTS (SELECT 'Unapproved matched dist'
50 FROM ap_invoice_distributions_all D
51 WHERE D.invoice_id = L.invoice_id
52 AND D.invoice_line_number = L.line_number
53 AND nvl(D.match_status_flag, 'X') NOT IN ('A', 'T'))
54 );
55
56 IF l_count > 0 THEN
57 p_orig_invoices_valid := 'N';
58 ELSE
59 p_orig_invoices_valid := 'Y';
60 END IF;
61
62 RETURN(TRUE);
63
64 EXCEPTION
65 WHEN OTHERS THEN
66 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
67 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
68 debug_info);
69 END IF;
70
71 IF (SQLCODE < 0) then
72 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
73 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
74 SQLERRM);
75 END IF;
76 END IF;
77
78 RETURN(FALSE);
79
80 END Are_Original_Invoices_Valid;
81
82
83 /*=============================================================================
84 | FUNCTION - Are_Holds_Ok()
85 |
86 | DESCRIPTION
87 | This function checks for a particular instruction if all the base
88 | matched Invoices(along with Price Corrections, Qty Corrections and the
89 | previously existing(If Any) Retro Price Adjustments Documents ) for the
90 | retropriced shipments(Records in AP_INVOICE_LINES_INTERFACE) has any holds
91 | (other than Price Hold)
92 |
93 | PARAMETERS
94 | p_instruction_id
95 | p_org_id
96 } p_orig_invoices_valid --OUT
97 |
98 | MODIFICATION HISTORY
99 | Date Author Description of Change
100 | 29-JUL-2003 dgulraja Creation
101 |
102 *============================================================================*/
103 FUNCTION Are_Holds_Ok(
104 p_instruction_id IN NUMBER,
105 p_org_id IN NUMBER,
106 p_orig_invoices_valid OUT NOCOPY VARCHAR2)
107
108 RETURN BOOLEAN IS
109
110 l_count NUMBER := 0;
111 debug_info VARCHAR2(1000);
112
113 BEGIN
114
115 debug_info := 'Are Holds OK';
116 SELECT count(*)
117 INTO l_count
118 FROM ap_invoice_lines_interface IL,
119 ap_invoice_lines_all L
120 WHERE IL.invoice_id = p_instruction_id
121 AND L.org_id = p_org_id
122 AND L.po_line_location_id = IL.po_line_location_id
123 AND L.match_type IN ('ITEM_TO_PO', 'ITEM_TO_RECEIPT',
124 'QTY_CORRECTION', 'PRICE_CORRECTION',
125 'PO_PRICE_ADJUSTMENT', 'ADJUSTMENT_CORRECTION')
126 AND L.discarded_flag <> 'Y'
127 AND L.cancelled_flag <> 'Y'
128 AND (NVL(L.generate_dists, 'Y') = 'D'
129 AND NOT EXISTS (SELECT 'Unapproved matched dist'
130 FROM ap_invoice_distributions_all D
131 WHERE D.invoice_id = L.invoice_id
132 AND D.invoice_line_number = L.line_number
133 AND nvl(D.match_status_flag, 'X') NOT IN ('A', 'T'))
134 AND EXISTS (SELECT 'Holds other than Price Hold'
135 FROM ap_holds_all H
136 WHERE H.invoice_id = L.invoice_id
137 AND H.release_lookup_code is null
138 AND H.hold_lookup_code <> 'PRICE'));
139
140 IF l_count > 0 THEN
141 p_orig_invoices_valid := 'N';
142 ELSE
143 p_orig_invoices_valid := 'Y';
144 END IF;
145
146 RETURN(TRUE);
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
151 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
152 debug_info);
153 END IF;
154
155 IF (SQLCODE < 0) then
156 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
157 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
158 SQLERRM);
159 END IF;
160 END IF;
161
162 RETURN(FALSE);
163
164 END Are_Holds_Ok;
165
166
167 /*=============================================================================
168 | FUNCTION - Is_sequence_assigned()
169 |
170 | DESCRIPTION
171 | This function checks whether or not a sequence is assigned with the
172 | particular document category code. This procedure is added for the
173 | bug5769161
174 |
175 | PARAMETERS
176 | p_document_category_code
177 | p_set_of_books_id
178 | p_is_sequence_assigned -OUT
179 |
180 | MODIFICATION HISTORY
181 | Date Author Description of Change
182 | 12-MAR-2007 gagrawal Creation
183 | 22-MAY-2009 gagrawal Changed to input org instead of
184 | set of books (bug8514744)
185 |
186 *============================================================================*/
187 FUNCTION Is_sequence_assigned(
188 p_document_category_code IN VARCHAR2,
189 p_org_id IN NUMBER,
190 p_is_sequence_assigned OUT NOCOPY VARCHAR2)
191
192 RETURN BOOLEAN IS
193
194 l_count NUMBER := 0;
195 debug_info VARCHAR2(1000);
196
197 BEGIN
198
199 debug_info := 'Is sequence Assigned?';
200
201 SELECT count(*)
202 INTO l_count
203 FROM fnd_document_sequences SEQ,
204 fnd_doc_sequence_assignments SA,
205 ap_system_parameters_all asp
206 WHERE SEQ.doc_sequence_id = SA.doc_sequence_id
207 AND SA.application_id = 200
208 AND SA.category_code = p_document_category_code
209 AND (NVL(SA.method_code,'A') = 'A')
210 AND (asp.org_id = p_org_id)
211 AND asp.set_of_books_id = SA.set_of_books_id
212 AND SYSDATE -- never null
213 BETWEEN SA.start_date
214 AND NVL(SA.end_date, TO_DATE('31/12/4712','DD/MM/YYYY'));
215
216 IF l_count > 0 THEN
217 p_is_sequence_assigned := 'Y';
218 ELSE
219 p_is_sequence_assigned := 'N';
220 END IF;
221
222 RETURN(TRUE);
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
227 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
228 debug_info);
229 END IF;
230
231 IF (SQLCODE < 0) then
232 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
233 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
234 SQLERRM);
235 END IF;
236 END IF;
237
238 RETURN(FALSE);
239
240 END Is_sequence_assigned;
241
242
243 /*=============================================================================
244 | FUNCTION - Ppa_Already_Exists()
245 |
246 | DESCRIPTION
247 | This function checks if PPA document already exists for a base matched
248 | invoice line that needs to be retropriced. The Adjustment Corrections on the
249 | base matched Invoice doesn't guarentee the existence of a PPA document.
250 | In case multiple PPA document exist for the base matched Invoice then we
251 | select the last PPA document created for reversal.
252 | Note: MAX(invoice_id) insures that we reverse the latest PPA.
253 |
254 | PARAMETERS
255 | P_invoice_id
256 | P_line_number
257 | p_ppa_exists --OUT
258 | P_existing_ppa_inv_id --OUT
259 |
260 | MODIFICATION HISTORY
261 | Date Author Description of Change
262 | 29-JUL-2003 dgulraja Creation
263 |
264 *============================================================================*/
265 FUNCTION Ppa_Already_Exists(
266 P_invoice_id IN NUMBER,
267 P_line_number IN NUMBER,
268 p_ppa_exists OUT NOCOPY VARCHAR2,
269 P_existing_ppa_inv_id OUT NOCOPY NUMBER)
270 RETURN BOOLEAN IS
271
272 l_count NUMBER := 0;
273 p_existing_invoice_id NUMBER;
274 debug_info VARCHAR2(1000);
275
276 BEGIN
277 --
278 debug_info := 'IF ppa_already_Exists';
279 SELECT count(*)
280 INTO l_count
281 FROM ap_invoice_lines_all
282 WHERE corrected_inv_id = p_invoice_id
283 AND corrected_line_number = p_line_number
284 AND line_type_lookup_code IN ('RETROITEM')
285 AND match_type = 'PO_PRICE_ADJUSTMENT';
286 --
287 IF l_count > 0 THEN
288 --
289 P_ppa_exists := 'Y';
290 debug_info := 'Get Existing Ppa_invoice_id';
291 SELECT invoice_id
292 INTO p_existing_ppa_inv_id -- Bug 5525506
293 FROM ap_invoices_all AI
294 WHERE invoice_type_lookup_code = 'PO PRICE ADJUST'
295 AND source = 'PPA'
296 AND ai.invoice_id = (SELECT MAX(invoice_id)
297 FROM ap_invoice_lines_all
298 WHERE corrected_inv_id = p_invoice_id
299 AND corrected_line_number = p_line_number
300 AND line_type_lookup_code IN ('RETROITEM')
301 AND match_type = 'PO_PRICE_ADJUSTMENT'
302 );
303 --
304 ELSE
305 --
306 P_ppa_exists := 'N';
307 --
308 END IF;
309 --
310 RETURN(TRUE);
311 EXCEPTION
312 WHEN OTHERS THEN
313 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
314 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
315 debug_info);
316 END IF;
317
318 IF (SQLCODE < 0) then
319 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
320 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
321 SQLERRM);
322 END IF;
323 END IF;
324 --
325 RETURN(FALSE);
326 --
327 END Ppa_already_Exists;
328
329
330 /*=============================================================================
331 | FUNCTION - Ipv_Dists_Exists()
332 |
333 | DESCRIPTION
334 | This function checks if IPV distributions exist for base matched
335 | Invoice Line(also Price Correction and Qty Correction Lines) for a
336 | retropriced shipment
337 |
338 | PARAMETERS
339 | P_invoice_id
340 | P_line_number
341 | p_ipv_dists_exist --OUT
342 |
343 | MODIFICATION HISTORY
344 | Date Author Description of Change
345 | 29-JUL-2003 dgulraja Creation
346 |
347 *============================================================================*/
348 FUNCTION Ipv_Dists_Exists(
349 p_invoice_id IN NUMBER,
350 p_line_number IN NUMBER,
351 p_ipv_dists_exist OUT NOCOPY VARCHAR2)
352 RETURN BOOLEAN IS
353
354 l_count NUMBER := 0;
355 debug_info VARCHAR2(1000);
356
357 BEGIN
358 debug_info := 'Get Existing Ppa_invoice_id';
359 SELECT count(*)
360 INTO l_count
361 FROM ap_invoice_distributions_all
362 WHERE invoice_id = p_invoice_id
363 AND invoice_line_number = p_line_number
364 AND line_type_lookup_code = 'IPV';
365
366 IF l_count > 0 THEN
367 p_ipv_dists_exist := 'Y';
368 ELSE
369 p_ipv_dists_exist := 'N';
370 END IF;
371
372 RETURN(TRUE);
373
374 END Ipv_Dists_Exists;
375
376
377 /*=============================================================================
378 | FUNCTION - Erv_Dists_Exists()
379 |
380 | DESCRIPTION
381 | This function checks if ERV distributions exist for base matched
382 | Invoice Line(also Price Correction and Qty Correction Lines) for a
383 | retropriced shipment. This function is called Compute_IPV_Adjustment_Corr
384 |
385 | PARAMETERS
386 | P_invoice_id
387 | P_line_number
388 | p_erv_dists_exist OUT
389 |
390 | MODIFICATION HISTORY
391 | Date Author Description of Change
392 | 29-JUL-2003 dgulraja Creation
393 |
394 *============================================================================*/
395 FUNCTION Erv_Dists_Exists(
396 p_invoice_id IN NUMBER,
397 p_line_number IN NUMBER,
398 p_erv_dists_exist OUT NOCOPY VARCHAR2)
399 RETURN BOOLEAN IS
400
401 l_count NUMBER := 0;
402 debug_info VARCHAR2(1000);
403
404 BEGIN
405 debug_info := 'IF Erv Dists Exist';
406 SELECT count(*)
407 INTO l_count
408 FROM ap_invoice_distributions_all
409 WHERE invoice_id = p_invoice_id
410 AND invoice_line_number = p_line_number
411 AND line_type_lookup_code = 'ERV';
412
413
414 IF l_count > 0 THEN
415 p_erv_dists_exist := 'Y';
416 ELSE
417 p_erv_dists_exist := 'N';
418 END IF;
419
420 RETURN(TRUE);
421 EXCEPTION
422 WHEN OTHERS THEN
423 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
424 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
425 debug_info);
426 END IF;
427
428 IF (SQLCODE < 0) then
429 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
430 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
431 SQLERRM);
432 END IF;
433 END IF;
434 --
435 RETURN(FALSE);
436 --
437 END Erv_Dists_Exists;
438
439
440 /*=============================================================================
441 | FUNCTION - Adj_Corr_Exists()
442 |
443 | DESCRIPTION
444 | This function checks if Adjustment Corrections exist for base matched
445 | Invoice Line(also Price Correction and Qty Correction Lines) for a
446 | retropriced shipment.
447 |
448 | PARAMETERS
449 | P_invoice_id
450 | P_line_number
451 | p_adj_corr_exists OUT
452 |
453 | MODIFICATION HISTORY
454 | Date Author Description of Change
455 | 29-JUL-2003 dgulraja Creation
456 |
457 *============================================================================*/
458 FUNCTION Adj_Corr_Exists(
459 p_invoice_id IN NUMBER,
460 p_line_number IN NUMBER,
461 p_adj_corr_exists OUT NOCOPY VARCHAR2)
462 RETURN BOOLEAN IS
463
464 l_count NUMBER := 0;
465 debug_info VARCHAR2(1000);
466 BEGIN
467 debug_info := 'IF Adj Corr Exists';
468 SELECT count(*)
469 INTO l_count
470 FROM ap_invoice_lines_all
471 WHERE invoice_id = p_invoice_id
472 AND corrected_inv_id = p_invoice_id
473 AND corrected_line_number = p_line_number
474 AND line_type_lookup_code IN ('RETROITEM')
475 AND match_type = 'ADJUSTMENT_CORRECTION';
476
477 IF l_count > 0 THEN
478 p_adj_corr_exists := 'Y';
479 ELSE
480 p_adj_corr_exists := 'N';
481 END IF;
482
483 RETURN(TRUE);
484
485 END Adj_Corr_Exists;
486
487
488 /*=============================================================================
489 | FUNCTION - Corrections_Exists()
490 |
491 | DESCRIPTION
492 | This function returns Price or Qty Corrections Lines for affected base
493 | matched Invoice Line depending upon the line_type_lookup_code passed to the
494 | function
495 |
496 | PARAMETERS
497 | P_invoice_id
498 | P_line_number
499 | p_adj_corr_exists OUT
500 |
501 | MODIFICATION HISTORY
502 | Date Author Description of Change
503 | 29-JUL-2003 dgulraja Creation
504 |
505 *============================================================================*/
506 FUNCTION Corrections_Exists(
507 p_invoice_id IN NUMBER,
508 p_line_number IN NUMBER,
509 p_match_ype IN VARCHAR2, --p_line_type_lookup_code bug#9573078
510 p_lines_list OUT NOCOPY AP_RETRO_PRICING_PKG.invoice_lines_list_type,
511 p_corrections_exist OUT NOCOPY VARCHAR2 )
512 RETURN BOOLEAN IS
513
514 CURSOR corr_lines IS
515 SELECT invoice_id,
516 line_number,
517 line_type_lookup_code,
518 requester_id,
519 description,
520 line_source,
521 org_id,
522 inventory_item_id,
523 item_description,
524 serial_number,
525 manufacturer,
526 model_number,
527 generate_dists,
528 match_type,
529 default_dist_ccid,
530 prorate_across_all_items,
531 accounting_date,
532 period_name,
533 deferred_acctg_flag,
534 set_of_books_id,
535 amount,
536 base_amount,
537 rounding_amt,
538 quantity_invoiced,
539 unit_meas_lookup_code,
540 unit_price,
541 -- ussgl_transaction_code, - Bug 4277744
542 discarded_flag,
543 cancelled_flag,
544 income_tax_region,
545 type_1099,
546 corrected_inv_id,
547 corrected_line_number,
548 po_header_id,
549 po_line_id,
550 po_release_id,
551 po_line_location_id,
552 po_distribution_id,
553 rcv_transaction_id,
554 final_match_flag,
555 assets_tracking_flag,
556 asset_book_type_code,
557 asset_category_id,
558 project_id,
559 task_id,
560 expenditure_type,
561 expenditure_item_date,
562 expenditure_organization_id,
563 award_id,
564 awt_group_id,
565 pay_awt_group_id, -- Bug 6832773
566 receipt_verified_flag,
567 receipt_required_flag,
568 receipt_missing_flag,
569 justification,
570 expense_group,
571 start_expense_date,
572 end_expense_date,
573 receipt_currency_code,
574 receipt_conversion_rate,
575 receipt_currency_amount,
576 daily_amount,
577 web_parameter_id,
578 adjustment_reason,
579 merchant_document_number,
580 merchant_name,
581 merchant_reference,
582 merchant_tax_reg_number,
583 merchant_taxpayer_id,
584 country_of_supply,
585 credit_card_trx_id,
586 company_prepaid_invoice_id,
587 cc_reversal_flag,
588 creation_date,
589 created_by,
590 attribute_category,
591 attribute1,
592 attribute2,
593 attribute3,
594 attribute4,
595 attribute5,
596 attribute6,
597 attribute7,
598 attribute8,
599 attribute9,
600 attribute10,
601 attribute11,
602 attribute12,
603 attribute13,
604 attribute14,
605 attribute15,
606 global_attribute_category,
607 global_attribute1,
608 global_attribute2,
609 global_attribute3,
610 global_attribute4,
611 global_attribute5,
612 global_attribute6,
613 global_attribute7,
614 global_attribute8,
615 global_attribute9,
616 global_attribute10,
617 global_attribute11,
618 global_attribute12,
619 global_attribute13,
620 global_attribute14,
621 global_attribute15,
622 global_attribute16,
623 global_attribute17,
624 global_attribute18,
625 global_attribute19,
626 global_attribute20,
627 primary_intended_use,
628 ship_to_location_id,
629 product_type,
630 product_category,
631 product_fisc_classification,
632 user_defined_fisc_class,
633 trx_business_category,
634 summary_tax_line_id,
635 tax_regime_code,
636 tax,
637 tax_jurisdiction_code,
638 tax_status_code,
639 tax_rate_id,
640 tax_rate_code,
641 tax_rate,
642 wfapproval_status,
643 pa_quantity,
644 NULL, --instruction_id
645 NULL, --adj_type
646 cost_factor_id, --cost_factor_id
647 TAX_CLASSIFICATION_CODE,
648 SOURCE_APPLICATION_ID,
649 SOURCE_EVENT_CLASS_CODE,
650 SOURCE_ENTITY_CODE,
651 SOURCE_TRX_ID,
652 SOURCE_LINE_ID,
653 SOURCE_TRX_LEVEL_TYPE,
654 PA_CC_AR_INVOICE_ID,
655 PA_CC_AR_INVOICE_LINE_NUM,
656 PA_CC_PROCESSED_CODE,
657 REFERENCE_1,
658 REFERENCE_2,
659 DEF_ACCTG_START_DATE,
660 DEF_ACCTG_END_DATE,
661 DEF_ACCTG_NUMBER_OF_PERIODS,
662 DEF_ACCTG_PERIOD_TYPE,
663 REFERENCE_KEY5,
664 PURCHASING_CATEGORY_ID,
665 NULL, -- line group number
666 WARRANTY_NUMBER,
667 REFERENCE_KEY3,
668 REFERENCE_KEY4,
669 APPLICATION_ID,
670 PRODUCT_TABLE,
671 REFERENCE_KEY1,
672 REFERENCE_KEY2,
673 RCV_SHIPMENT_LINE_ID
674 FROM ap_invoice_lines_all
675 WHERE corrected_inv_id = p_invoice_id
676 AND corrected_line_number = p_line_number
677 AND discarded_flag <> 'Y'
678 AND cancelled_flag <> 'Y'
679 --Modified 'line_type_lookup_code' to 'match_type' for bug#9573078
680 AND match_type = p_match_ype
681 AND generate_dists = 'D';
682 /*AND NOT EXISTS (SELECT 'Unapproved matched dist'
683 FROM ap_invoice_distributions D
684 WHERE D.invoice_id = L.invoice_id
685 AND D.invoice_line_number = L.line_number
686 AND nvl(D.match_status_flag, 'X') NOT IN ('A', 'T'))
687 AND EXISTS (SELECT 'Holds other than Price Hold'
688 FROM ap_holds H
689 WHERE H.invoice_id = L.invoice_id
690 AND H.release_lookup_code is null
691 AND H.hold_lookup_code <> 'PRICE')); */
692
693
694 l_count NUMBER := 0;
695 debug_info VARCHAR2(1000);
696
697 BEGIN
698 --
699 debug_info := 'Open cursor Corr_line';
700 OPEN corr_lines;
701 FETCH corr_lines
702 BULK COLLECT INTO p_lines_list;
703 CLOSE corr_lines;
704
705 IF p_lines_list.COUNT > 0 THEN
706 p_corrections_exist := 'Y';
707 ELSE
708 p_corrections_exist := 'N';
709 END IF;
710
711 RETURN(TRUE);
712 EXCEPTION
713 WHEN OTHERS THEN
714 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
715 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
716 debug_info);
717 END IF;
718
719 IF (SQLCODE < 0) then
720 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
721 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
722 SQLERRM);
723 END IF;
724 END IF;
725
726 IF ( corr_lines%ISOPEN ) THEN
727 CLOSE corr_lines;
728 END IF;
729
730 RETURN(FALSE);
731
732 END Corrections_Exists;
733
734
735 /*=============================================================================
736 | FUNCTION - Tipv_Exists()
737 |
738 | DESCRIPTION
739 | This function returns all the Tax lines allocated to the base matched
740 | (or Price/Qty Correction) line that is affected by Retropricing. The function
741 | insures that the Tax line has TIPV distribtuions that need to be
742 | Retro-Adjusted.
743 | Note : Only EXCLUSIVE tax is supported for Po matched lines. TIPV distributions
744 | can only exist on the Tax line if the original invoce line(that the tax
745 | line is allocated to) has IPV distributions. Futhermore this check is
746 | only done if original invoice has IPV dists and the Original Invoice
747 | has not been retro-adjusted
748 |
749 |
750 | PARAMETERS
751 | P_invoice_id
752 | P_line_number
753 | p_tax_lines_list --OUT
754 | p_tipv_exist --OUT
755 | MODIFICATION HISTORY
756 | Date Author Description of Change
757 | 29-JUL-2003 dgulraja Creation
758 |
759 *============================================================================*/
760 FUNCTION Tipv_Exists(
761 p_invoice_id IN NUMBER,
762 p_invoice_line_number IN NUMBER,
763 p_tax_lines_list OUT NOCOPY AP_RETRO_PRICING_PKG.invoice_lines_list_type,
764 p_tipv_exist OUT NOCOPY VARCHAR2)
765 RETURN BOOLEAN IS
766
767 CURSOR tax_lines IS
768 SELECT AIL.invoice_id,
769 AIL.line_number,
770 AIL.line_type_lookup_code,
771 AIL.requester_id,
772 AIL.description,
773 AIL.line_source,
774 AIL.org_id,
775 AIL.inventory_item_id,
776 AIL.item_description,
777 AIL.serial_number,
778 AIL.manufacturer,
779 AIL.model_number,
780 AIL.generate_dists,
781 AIL.match_type,
782 AIL.default_dist_ccid,
783 AIL.prorate_across_all_items,
784 AIL.accounting_date,
785 AIL.period_name,
786 AIL.deferred_acctg_flag,
787 AIL.set_of_books_id,
788 AIL.amount,
789 AIL.base_amount,
790 AIL.rounding_amt,
791 AIL.quantity_invoiced,
792 AIL.unit_meas_lookup_code,
793 AIL.unit_price,
794 -- AIL.ussgl_transaction_code, - Bug 4277744
795 AIL.discarded_flag,
796 AIL.cancelled_flag,
797 AIL.income_tax_region,
798 AIL.type_1099,
799 AIL.corrected_inv_id,
800 AIL.corrected_line_number,
801 AIL.po_header_id,
802 AIL.po_line_id,
803 AIL.po_release_id,
804 AIL.po_line_location_id,
805 AIL.po_distribution_id,
806 AIL.rcv_transaction_id,
807 AIL.final_match_flag,
808 AIL.assets_tracking_flag,
809 AIL.asset_book_type_code,
810 AIL.asset_category_id,
811 AIL.project_id,
812 AIL.task_id,
813 AIL.expenditure_type,
814 AIL.expenditure_item_date,
815 AIL.expenditure_organization_id,
816 AIL.award_id,
817 AIL.awt_group_id,
818 AIL.pay_awt_group_id, -- Bug 6832773
819 AIL.receipt_verified_flag,
820 AIL.receipt_required_flag,
821 AIL.receipt_missing_flag,
822 AIL.justification,
823 AIL.expense_group,
824 AIL.start_expense_date,
825 AIL.end_expense_date,
826 AIL.receipt_currency_code,
827 AIL.receipt_conversion_rate,
828 AIL.receipt_currency_amount,
829 AIL.daily_amount,
830 AIL.web_parameter_id,
831 AIL.adjustment_reason,
832 AIL.merchant_document_number,
833 AIL.merchant_name,
834 AIL.merchant_reference,
835 AIL.merchant_tax_reg_number,
836 AIL.merchant_taxpayer_id,
837 AIL.country_of_supply,
838 AIL.credit_card_trx_id,
839 AIL.company_prepaid_invoice_id,
840 AIL.cc_reversal_flag,
841 AIL.creation_date,
842 AIL.created_by,
843 AIL.attribute_category,
844 AIL.attribute1,
845 AIL.attribute2,
846 AIL.attribute3,
847 AIL.attribute4,
848 AIL.attribute5,
849 AIL.attribute6,
850 AIL.attribute7,
851 AIL.attribute8,
852 AIL.attribute9,
853 AIL.attribute10,
854 AIL.attribute11,
855 AIL.attribute12,
856 AIL.attribute13,
857 AIL.attribute14,
858 AIL.attribute15,
859 AIL.global_attribute_category,
860 AIL.global_attribute1,
861 AIL.global_attribute2,
862 AIL.global_attribute3,
863 AIL.global_attribute4,
864 AIL.global_attribute5,
865 AIL.global_attribute6,
866 AIL.global_attribute7,
867 AIL.global_attribute8,
868 AIL.global_attribute9,
869 AIL.global_attribute10,
870 AIL.global_attribute11,
871 AIL.global_attribute12,
872 AIL.global_attribute13,
873 AIL.global_attribute14,
874 AIL.global_attribute15,
875 AIL.global_attribute16,
876 AIL.global_attribute17,
877 AIL.global_attribute18,
878 AIL.global_attribute19,
879 AIL.global_attribute20,
880 AIL.primary_intended_use,
881 AIL.ship_to_location_id,
882 AIL.product_type,
883 AIL.product_category,
884 AIL.product_fisc_classification,
885 AIL.user_defined_fisc_class,
886 AIL.trx_business_category,
887 AIL.summary_tax_line_id,
888 AIL.tax_regime_code,
889 AIL.tax,
890 AIL.tax_jurisdiction_code,
891 AIL.tax_status_code,
892 AIL.tax_rate_id,
893 AIL.tax_rate_code,
894 AIL.tax_rate,
895 AIL.wfapproval_status,
896 AIL.pa_quantity,
897 NULL, --instruction_id
898 NULL, --adj_type
899 AIL.cost_factor_id, --cost_factor_id
900 AIL.TAX_CLASSIFICATION_CODE,
901 AIL.SOURCE_APPLICATION_ID,
902 AIL.SOURCE_EVENT_CLASS_CODE,
903 AIL.SOURCE_ENTITY_CODE,
904 AIL.SOURCE_TRX_ID,
905 AIL.SOURCE_LINE_ID,
906 AIL.SOURCE_TRX_LEVEL_TYPE,
907 AIL.PA_CC_AR_INVOICE_ID,
908 AIL.PA_CC_AR_INVOICE_LINE_NUM,
909 AIL.PA_CC_PROCESSED_CODE,
910 AIL.REFERENCE_1,
911 AIL.REFERENCE_2,
912 AIL.DEF_ACCTG_START_DATE,
913 AIL.DEF_ACCTG_END_DATE,
914 AIL.DEF_ACCTG_NUMBER_OF_PERIODS,
915 AIL.DEF_ACCTG_PERIOD_TYPE,
916 AIL.REFERENCE_KEY5,
917 AIL.PURCHASING_CATEGORY_ID,
918 NULL, -- line group number
919 AIL.WARRANTY_NUMBER,
920 AIL.REFERENCE_KEY3,
921 AIL.REFERENCE_KEY4,
922 AIL.APPLICATION_ID,
923 AIL.PRODUCT_TABLE,
924 AIL.REFERENCE_KEY1,
925 AIL.REFERENCE_KEY2,
926 AIL.RCV_SHIPMENT_LINE_ID
927 FROM ap_invoice_lines AIL,
928 ap_allocation_rule_lines ARL
929 WHERE AIL.invoice_id = ARL.invoice_id
930 AND ARL.invoice_id = p_invoice_id
931 AND ARL.to_invoice_line_number = p_invoice_line_number
932 AND ARL.chrg_invoice_line_number = AIL.line_number
933 AND AIL.line_type_lookup_code = 'TAX'
934 AND EXISTS (SELECT 1
935 FROM ap_invoice_distributions_all AID
936 WHERE AID.invoice_id = AIL.invoice_id
937 AND AID.invoice_line_number = AIL.line_number
938 AND AID.invoice_id = p_invoice_id
939 --Bug#10416960
940 AND AID.line_type_lookup_code in ('TIPV','TRV','REC_TAX','TERV'));
941
942
943 l_included_tax_amount NUMBER;
944 l_tipv_count NUMBER := 0;
945 debug_info VARCHAR2(1000);
946
947 BEGIN
948 --
949 debug_info := 'IF tipv exist';
950 SELECT count(*)
951 INTO l_tipv_count
952 FROM ap_invoice_distributions_all d1
953 WHERE invoice_id = p_invoice_id
954 AND invoice_line_number <> p_invoice_line_number
955 --Bug#10416960
956 AND line_type_lookup_code in('TIPV','TRV','REC_TAX','TERV')
957 AND charge_applicable_to_dist_id IN
958 (SELECT invoice_distribution_id
959 FROM ap_invoice_distributions_all
960 WHERE invoice_id = p_invoice_id
961 AND invoice_line_number = p_invoice_line_number);
962 --
963 IF l_tipv_count > 0 THEN
964 p_tipv_exist := 'Y';
965 ELSE
966 p_tipv_exist := 'N';
967 END IF;
968 --
969 debug_info := 'Open cursor tax_lines';
970 OPEN tax_lines;
971 FETCH tax_lines
972 BULK COLLECT INTO p_tax_lines_list;
973 CLOSE tax_lines;
974 --
975 RETURN(TRUE);
976 --
977 EXCEPTION
978 WHEN OTHERS THEN
979 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
980 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
981 debug_info);
982 END IF;
983 --
984 IF (SQLCODE < 0) then
985 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
986 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
987 SQLERRM);
988 END IF;
989 END IF;
990 --
991 IF ( tax_lines%ISOPEN ) THEN
992 CLOSE tax_lines;
993 END IF;
994 --
995 RETURN(FALSE);
996 --
997 END Tipv_Exists;
998
999
1000 /*=============================================================================
1001 | FUNCTION - Terv_Dists_Exists()
1002 |
1003 | DESCRIPTION
1004 | This function is called from Compute_TIPV_Adjustment_Corr to check if TERV
1005 | distributions exist for Tax line(allocated to a original line for a
1006 | retropriced shipment). Furthermore check is only made if the allocated Tax lines
1007 | have TIPV distributions.
1008 |
1009 |
1010 |
1011 | PARAMETERS
1012 | P_invoice_id
1013 | P_line_number
1014 | p_terv_dists_exist OUT
1015 | MODIFICATION HISTORY
1016 | Date Author Description of Change
1017 | 29-JUL-2003 dgulraja Creation
1018 |
1019 *============================================================================*/
1020 FUNCTION Terv_Dists_Exists(
1021 p_invoice_id IN NUMBER,
1022 p_line_number IN NUMBER,
1023 p_terv_dists_exist OUT NOCOPY VARCHAR2)
1024 RETURN BOOLEAN IS
1025
1026 l_count NUMBER := 0;
1027 debug_info VARCHAR2(1000);
1028
1029 BEGIN
1030 --
1031 debug_info := 'IF Terv Dists Exist';
1032 SELECT count(*)
1033 INTO l_count
1034 FROM ap_invoice_distributions_all
1035 WHERE invoice_id = p_invoice_id
1036 AND invoice_line_number = p_line_number
1037 AND line_type_lookup_code = 'TERV';
1038
1039 IF l_count > 0 THEN
1040 p_terv_dists_exist := 'Y';
1041 ELSE
1042 p_terv_dists_exist := 'N';
1043 END IF;
1044
1045 RETURN(TRUE);
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1049 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1050 debug_info);
1051 END IF;
1052
1053 IF (SQLCODE < 0) then
1054 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1055 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1056 SQLERRM);
1057 END IF;
1058 END IF;
1059 --
1060 RETURN(FALSE);
1061 --
1062 END Terv_Dists_Exists;
1063
1064 /*=============================================================================
1065 | FUNCTION - Get_Invoice_distribution_id()
1066 |
1067 | DESCRIPTION
1068 | This function returns the invoice_distribution_id
1069 |
1070 | PARAMETERS
1071 | NONE
1072 |
1073 | MODIFICATION HISTORY
1074 | Date Author Description of Change
1075 | 29-JUL-2003 dgulraja Creation
1076 |
1077 *============================================================================*/
1078 FUNCTION Get_Invoice_distribution_id
1079 RETURN NUMBER IS
1080
1081 l_inv_dist_id NUMBER(15);
1082 debug_info VARCHAR2(1000);
1083 BEGIN
1084 debug_info := 'Get Invoice_distribution_id';
1085 SELECT ap_invoice_distributions_s.NEXTVAL
1086 INTO l_inv_dist_id
1087 FROM dual;
1088
1089 RETURN l_inv_dist_id;
1090
1091 END Get_Invoice_distribution_id;
1092
1093
1094 /*=============================================================================
1095 | FUNCTION - Get_Ccid()
1096 |
1097 | DESCRIPTION
1098 | This function returns the ccid depending on the Parameter
1099 | p_invoice_distribution_id. This function is called in context
1100 | of IPV distributions on the base matched line or Price Corrections.
1101 | p_invoice_distribution_id
1102 | = Related_dist_Id for the IPV distributions on the base matched line.
1103 | = corrected_dist_id for the IPV distributions on the PC Line.
1104 |
1105 |
1106 | PARAMETERS
1107 | p_invoice_distribution_id
1108 |
1109 | MODIFICATION HISTORY
1110 | Date Author Description of Change
1111 | 29-JUL-2003 dgulraja Creation
1112 |
1113 *============================================================================*/
1114 FUNCTION Get_Ccid(
1115 p_invoice_distribution_id IN NUMBER)
1116 RETURN NUMBER IS
1117
1118 l_ccid NUMBER;
1119 debug_info VARCHAR2(1000);
1120
1121 BEGIN
1122 debug_info := 'Get ccid';
1123 SELECT dist_code_combination_id
1124 INTO l_ccid
1125 FROM ap_invoice_distributions_all
1126 WHERE invoice_distribution_id = p_invoice_distribution_id;
1127 --
1128 RETURN l_ccid;
1129 --
1130 END Get_Ccid;
1131
1132
1133 /*=============================================================================
1134 | FUNCTION - Get_Dist_Type_lookup_code()
1135 |
1136 | DESCRIPTION
1137 | This function returns the Dist_Type_lookup_code depending on the
1138 | parameter invoice_distribution_id. This function is called in context
1139 | of IPV distributions on the base matched line or Price Corrections.
1140 | p_invoice_distribution_id
1141 | = Related_dist_Id for the IPV distributions on the base matched line.
1142 | = corrected_dist_id for the IPV distributions on the PC Line.
1143 |
1144 |
1145 | PARAMETERS
1146 | p_invoice_distribution_id
1147 |
1148 | MODIFICATION HISTORY
1149 | Date Author Description of Change
1150 | 29-JUL-2003 dgulraja Creation
1151 |
1152 *============================================================================*/
1153 FUNCTION Get_Dist_Type_lookup_code(
1154 p_invoice_distribution_id IN NUMBER)
1155 RETURN VARCHAR2 IS
1156
1157 l_line_type_lookup_code AP_INVOICE_LINES_ALL.line_type_lookup_code%TYPE;
1158 debug_info VARCHAR2(1000);
1159
1160 BEGIN
1161 debug_info := 'Get Dist_Type_lookup_code';
1162 SELECT DECODE(line_type_lookup_code, 'ITEM', 'RETROEXPENSE',
1163 'ACCRUAL', 'RETROACCRUAL', 'RETROEXPENSE')
1164 INTO l_line_type_lookup_code
1165 FROM ap_invoice_distributions_all
1166 WHERE invoice_distribution_id = p_invoice_distribution_id;
1167
1168 RETURN l_line_type_lookup_code;
1169
1170 END Get_Dist_Type_lookup_code;
1171
1172
1173 /*=============================================================================
1174 | FUNCTION - get_max_ppa_line_num()
1175 |
1176 | DESCRIPTION
1177 | This function is called to get the max line number for the PPA Document
1178 | from the global temp table for a given PPA invoice_id.
1179 |
1180 | PARAMETERS
1181 | P_invoice_id
1182 |
1183 | MODIFICATION HISTORY
1184 | Date Author Description of Change
1185 | 29-JUL-2003 dgulraja Creation
1186 |
1187 *============================================================================*/
1188 FUNCTION get_max_ppa_line_num(
1189 P_invoice_id IN NUMBER)
1190 RETURN NUMBER IS
1191
1192 l_max_inv_line_num NUMBER := 0;
1193 debug_info VARCHAR2(1000);
1194
1195 BEGIN
1196 debug_info := 'Get max_ppa_line_num';
1197 SELECT COUNT(*)
1198 INTO l_max_inv_line_num
1199 FROM ap_ppa_invoice_lines_gt
1200 WHERE invoice_id = P_invoice_id;
1201
1202 RETURN (l_max_inv_line_num);
1203
1204 END get_max_ppa_line_num;
1205
1206
1207 /*=============================================================================
1208 | FUNCTION - Get_Exchange_Rate()
1209 |
1210 | DESCRIPTION
1211 | This function returns the Exchange rate on the Receipt or PO depending
1212 | on the P_match paramter.
1213 |
1214 | PARAMETERS
1215 | P_match
1216 | p_id
1217 |
1218 | MODIFICATION HISTORY
1219 | Date Author Description of Change
1220 | 29-JUL-2003 dgulraja Creation
1221 |
1222 *============================================================================*/
1223 FUNCTION get_exchange_rate(
1224 P_match IN VARCHAR2,
1225 p_id IN NUMBER)
1226 RETURN NUMBER IS
1227
1228 l_rate NUMBER;
1229 debug_info VARCHAR2(1000);
1230 BEGIN
1231 debug_info := 'Get exchange_rate';
1232 IF (p_match = 'RECEIPT') then
1233 SELECT RTXN.currency_conversion_rate
1234 INTO l_rate
1235 FROM rcv_transactions RTXN
1236 WHERE RTXN.transaction_id = p_id;
1237 ELSE
1238 /* Bug#10416960
1239 SELECT rate
1240 INTO l_rate
1241 FROM po_headers_All
1242 WHERE po_header_id = p_id; */
1243
1244 SELECT rate
1245 INTO l_rate
1246 FROM po_distributions_All
1247 WHERE po_distribution_id = p_id;
1248
1249 END IF;
1250 --
1251 RETURN(l_rate);
1252 --
1253 END get_exchange_rate;
1254
1255
1256 /*============================================================================
1257 | FUNCTION - get_invoice_amount()
1258 |
1259 | DESCRIPTION
1260 | This function sums the invoice line amounts for the PPA docs created
1261 | in the Global temporary tables for a particular invoice.
1262 |
1263 | PARAMETERS
1264 | NONE
1265 |
1266 | MODIFICATION HISTORY
1267 | Date Author Description of Change
1268 | 29-JUL-2003 dgulraja Creation
1269 |
1270 *==========================================================================*/
1271 --Bugfix:4681253 modified the signature of get_invoice_amount to make
1272 --p_invoice_currency_code of type VARCHAR2
1273 FUNCTION get_invoice_amount(
1274 P_invoice_id IN NUMBER,
1275 p_invoice_currency_code IN VARCHAR2)
1276 RETURN NUMBER IS
1277
1278 l_invoice_amount NUMBER := 0;
1279 debug_info VARCHAR2(1000);
1280 BEGIN
1281 SELECT NVL(SUM(amount), 0)
1282 INTO l_invoice_amount
1283 FROM ap_ppa_invoice_lines_gt L
1284 WHERE L.invoice_id = P_invoice_id;
1285 -- AND L.adj_type = 'PPA'; bug#9573078
1286
1287 IF l_invoice_amount <> 0 THEN
1288 debug_info := 'Get_Invoice_Amount step2: Call ap_round_currency';
1289 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1290 AP_IMPORT_UTILITIES_PKG.Print(
1291 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1292 END IF;
1293
1294 l_invoice_amount := ap_utilities_pkg.ap_round_currency(
1295 l_invoice_amount,
1296 p_invoice_currency_code);
1297 END IF;
1298
1299 debug_info := 'l_invoice_amount is '||l_invoice_amount;
1300 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1301 AP_IMPORT_UTILITIES_PKG.Print(
1302 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1303 END IF;
1304
1305 RETURN (l_invoice_amount);
1306
1307 END get_invoice_amount;
1308
1309
1310 /*============================================================================
1311 | FUNCTION - Get_corresponding_retro_DistId()
1312 |
1313 | DESCRIPTION
1314 | This function returns the distribution_id of the corresponding Retro
1315 | Expense/Accrual distribution.
1316 |
1317 | PARAMETERS
1318 | NONE
1319 |
1320 | MODIFICATION HISTORY
1321 | Date Author Description of Change
1322 | 29-JUL-2003 dgulraja Creation
1323 |
1324 *==========================================================================*/
1325 FUNCTION Get_corresponding_retro_DistId(
1326 p_match_type IN VARCHAR2,
1327 p_ccid IN NUMBER)
1328 RETURN NUMBER IS
1329
1330 l_dist_id NUMBER;
1331 debug_info VARCHAR2(1000);
1332 BEGIN
1333 debug_info := 'Get corresponding_retro_ccid';
1334
1335 /* Modified below SELECT for bug#10416960
1336 SELECT invoice_distribution_id
1337 INTO l_dist_id
1338 FROM ap_ppa_invoice_dists_gt
1339 WHERE corrected_invoice_dist_id =
1340 (SELECT invoice_distribution_id --5485084
1341 FROM ap_invoice_distributions_all
1342 WHERE DECODE(p_match_type,
1343 'PRICE_CORRECTION',corrected_invoice_dist_id,
1344 related_id) = p_ccid
1345 AND line_type_lookup_code = 'IPV')
1346 AND line_type_lookup_code IN ('RETROEXPENSE', 'RETROACCRUAL'); */
1347
1348 --Here p_ccid is charge_applicable_to_dist_id of TIPV, which is IPV dist id
1349 --IPV dist id corrected through cadip and new retroexpense/retroaccrual
1350 --line get create
1351 --now latest nonrec tax charge applicable to dist id is the dist of
1352 --new retro expense retroaccrual
1353
1354 BEGIN
1355 SELECT invoice_distribution_id
1356 INTO l_dist_id
1357 FROM ap_ppa_invoice_dists_gt
1358 WHERE DECODE(p_match_type,
1359 'PRICE_CORRECTION',corrected_invoice_dist_id,
1360 'ADJUSTMENT_CORRECTION',corrected_invoice_dist_id,
1361 related_id) = p_ccid
1362 AND line_type_lookup_code IN ('RETROEXPENSE', 'RETROACCRUAL','IPV');
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 NULL;
1366 END;
1367
1368 RETURN (l_dist_id);
1369
1370 END Get_corresponding_retro_DistId;
1371
1372
1373 /*============================================================================
1374 | FUNCTION - Create_Line()
1375 |
1376 | DESCRIPTION
1377 | This function is called to create zero amount adjustments lines
1378 | for IPV reversals, reversals for existing Po Price Adjustment PPA lines,
1379 | and to create Po Price Adjsutment lines w.r.t the Retropriced Amount.
1380 |
1381 | PARAMETERS
1382 | p_lines_rec
1383 | P_calling_sequence
1384 |
1385 | MODIFICATION HISTORY
1386 | Date Author Description of Change
1387 | 29-JUL-2003 dgulraja Creation
1388 |
1389 *==========================================================================*/
1390 FUNCTION Create_Line(
1391 p_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
1392 P_calling_sequence IN VARCHAR2)
1393 RETURN BOOLEAN IS
1394
1395 debug_info VARCHAR2(1000);
1396 BEGIN
1397
1398 debug_info := 'Insert into ap_ppa_invoice_lines_gt';
1399
1400 INSERT INTO ap_ppa_invoice_lines_gt (
1401 invoice_id,
1402 line_number,
1403 line_type_lookup_code,
1404 requester_id,
1405 description,
1406 line_source,
1407 org_id,
1408 inventory_item_id,
1409 item_description,
1410 serial_number,
1411 manufacturer,
1412 model_number,
1413 generate_dists,
1414 match_type,
1415 default_dist_ccid,
1416 prorate_across_all_items,
1417 accounting_date,
1418 period_name,
1419 deferred_acctg_flag,
1420 set_of_books_id,
1421 amount,
1422 base_amount,
1423 rounding_amt,
1424 quantity_invoiced,
1425 unit_meas_lookup_code,
1426 unit_price,
1427 -- ussgl_transaction_code, - Bug 4277744
1428 discarded_flag,
1429 cancelled_flag,
1430 income_tax_region,
1431 type_1099,
1432 corrected_inv_id,
1433 corrected_line_number,
1434 po_header_id,
1435 po_line_id,
1436 po_release_id,
1437 po_line_location_id,
1438 po_distribution_id,
1439 rcv_transaction_id,
1440 final_match_flag,
1441 assets_tracking_flag,
1442 asset_book_type_code,
1443 asset_category_id,
1444 project_id,
1445 task_id,
1446 expenditure_type,
1447 expenditure_item_date,
1448 expenditure_organization_id,
1449 award_id,
1450 awt_group_id,
1451 pay_awt_group_id, -- Bug 6832773
1452 receipt_verified_flag,
1453 receipt_required_flag,
1454 receipt_missing_flag,
1455 justification,
1456 expense_group,
1457 start_expense_date,
1458 end_expense_date,
1459 receipt_currency_code,
1460 receipt_conversion_rate,
1461 receipt_currency_amount,
1462 daily_amount,
1463 web_parameter_id,
1464 adjustment_reason,
1465 merchant_document_number,
1466 merchant_name,
1467 merchant_reference,
1468 merchant_tax_reg_number,
1469 merchant_taxpayer_id,
1470 country_of_supply,
1471 credit_card_trx_id,
1472 company_prepaid_invoice_id,
1473 cc_reversal_flag,
1474 creation_date,
1475 created_by,
1476 attribute_category,
1477 attribute1,
1478 attribute2,
1479 attribute3,
1480 attribute4,
1481 attribute5,
1482 attribute6,
1483 attribute7,
1484 attribute8,
1485 attribute9,
1486 attribute10,
1487 attribute11,
1488 attribute12,
1489 attribute13,
1490 attribute14,
1491 attribute15,
1492 global_attribute_category,
1493 global_attribute1,
1494 global_attribute2,
1495 global_attribute3,
1496 global_attribute4,
1497 global_attribute5,
1498 global_attribute6,
1499 global_attribute7,
1500 global_attribute8,
1501 global_attribute9,
1502 global_attribute10,
1503 global_attribute11,
1504 global_attribute12,
1505 global_attribute13,
1506 global_attribute14,
1507 global_attribute15,
1508 global_attribute16,
1509 global_attribute17,
1510 global_attribute18,
1511 global_attribute19,
1512 global_attribute20,
1513 primary_intended_use,
1514 ship_to_location_id,
1515 product_type,
1516 product_category,
1517 product_fisc_classification,
1518 user_defined_fisc_class,
1519 trx_business_category,
1520 summary_tax_line_id,
1521 tax_regime_code,
1522 tax,
1523 tax_jurisdiction_code,
1524 tax_status_code,
1525 tax_rate_id,
1526 tax_rate_code,
1527 tax_rate,
1528 wfapproval_status,
1529 pa_quantity,
1530 instruction_id,
1531 adj_type,
1532 invoice_line_id,
1533 cost_factor_id)
1534 VALUES (
1535 p_lines_rec.invoice_id,
1536 p_lines_rec.line_number,
1537 p_lines_rec.line_type_lookup_code,
1538 p_lines_rec.requester_id,
1539 p_lines_rec.description,
1540 p_lines_rec.line_source,
1541 p_lines_rec.org_id,
1542 p_lines_rec.inventory_item_id,
1543 p_lines_rec.item_description,
1544 p_lines_rec.serial_number,
1545 p_lines_rec.manufacturer,
1546 p_lines_rec.model_number,
1547 p_lines_rec.generate_dists,
1548 p_lines_rec.match_type,
1549 p_lines_rec.default_dist_ccid,
1550 p_lines_rec.prorate_across_all_items,
1551 p_lines_rec.accounting_date,
1552 p_lines_rec.period_name,
1553 p_lines_rec.deferred_acctg_flag,
1554 p_lines_rec.set_of_books_id,
1555 p_lines_rec.amount,
1556 p_lines_rec.base_amount,
1557 p_lines_rec.rounding_amt,
1558 p_lines_rec.quantity_invoiced,
1559 p_lines_rec.unit_meas_lookup_code,
1560 p_lines_rec.unit_price,
1561 -- p_lines_rec.ussgl_transaction_code, - Bug 4277744
1562 p_lines_rec.discarded_flag,
1563 p_lines_rec.cancelled_flag,
1564 p_lines_rec.income_tax_region,
1565 p_lines_rec.type_1099,
1566 p_lines_rec.corrected_inv_id,
1567 p_lines_rec.corrected_line_number,
1568 p_lines_rec.po_header_id,
1569 p_lines_rec.po_line_id,
1570 p_lines_rec.po_release_id,
1571 p_lines_rec.po_line_location_id,
1572 p_lines_rec.po_distribution_id,
1573 p_lines_rec.rcv_transaction_id,
1574 p_lines_rec.final_match_flag,
1575 p_lines_rec.assets_tracking_flag,
1576 p_lines_rec.asset_book_type_code,
1577 p_lines_rec.asset_category_id,
1578 p_lines_rec.project_id,
1579 p_lines_rec.task_id,
1580 p_lines_rec.expenditure_type,
1581 p_lines_rec.expenditure_item_date,
1582 p_lines_rec.expenditure_organization_id,
1583 p_lines_rec.award_id,
1584 p_lines_rec.awt_group_id,
1585 p_lines_rec.pay_awt_group_id, --Bug 6832773
1586 p_lines_rec.receipt_verified_flag,
1587 p_lines_rec.receipt_required_flag,
1588 p_lines_rec.receipt_missing_flag,
1589 p_lines_rec.justification,
1590 p_lines_rec.expense_group,
1591 p_lines_rec.start_expense_date,
1592 p_lines_rec.end_expense_date,
1593 p_lines_rec.receipt_currency_code,
1594 p_lines_rec.receipt_conversion_rate,
1595 p_lines_rec.receipt_currency_amount,
1596 p_lines_rec.daily_amount,
1597 p_lines_rec.web_parameter_id,
1598 p_lines_rec.adjustment_reason,
1599 p_lines_rec.merchant_document_number,
1600 p_lines_rec.merchant_name,
1601 p_lines_rec.merchant_reference,
1602 p_lines_rec.merchant_tax_reg_number,
1603 p_lines_rec.merchant_taxpayer_id,
1604 p_lines_rec.country_of_supply,
1605 p_lines_rec.credit_card_trx_id,
1606 p_lines_rec.company_prepaid_invoice_id,
1607 p_lines_rec.cc_reversal_flag,
1608 p_lines_rec.creation_date,
1609 p_lines_rec.created_by,
1610 p_lines_rec.attribute_category,
1611 p_lines_rec.attribute1,
1612 p_lines_rec.attribute2,
1613 p_lines_rec.attribute3,
1614 p_lines_rec.attribute4,
1615 p_lines_rec.attribute5,
1616 p_lines_rec.attribute6,
1617 p_lines_rec.attribute7,
1618 p_lines_rec.attribute8,
1619 p_lines_rec.attribute9,
1620 p_lines_rec.attribute10,
1621 p_lines_rec.attribute11,
1622 p_lines_rec.attribute12,
1623 p_lines_rec.attribute13,
1624 p_lines_rec.attribute14,
1625 p_lines_rec.attribute15,
1626 p_lines_rec.global_attribute_category,
1627 p_lines_rec.global_attribute1,
1628 p_lines_rec.global_attribute2,
1629 p_lines_rec.global_attribute3,
1630 p_lines_rec.global_attribute4,
1631 p_lines_rec.global_attribute5,
1632 p_lines_rec.global_attribute6,
1633 p_lines_rec.global_attribute7,
1634 p_lines_rec.global_attribute8,
1635 p_lines_rec.global_attribute9,
1636 p_lines_rec.global_attribute10,
1637 p_lines_rec.global_attribute11,
1638 p_lines_rec.global_attribute12,
1639 p_lines_rec.global_attribute13,
1640 p_lines_rec.global_attribute14,
1641 p_lines_rec.global_attribute15,
1642 p_lines_rec.global_attribute16,
1643 p_lines_rec.global_attribute17,
1644 p_lines_rec.global_attribute18,
1645 p_lines_rec.global_attribute19,
1646 p_lines_rec.global_attribute20,
1647 p_lines_rec.primary_intended_use,
1648 p_lines_rec.ship_to_location_id,
1649 p_lines_rec.product_type,
1650 p_lines_rec.product_category,
1651 p_lines_rec.product_fisc_classification,
1652 p_lines_rec.user_defined_fisc_class,
1653 p_lines_rec.trx_business_category,
1654 p_lines_rec.summary_tax_line_id,
1655 p_lines_rec.tax_regime_code,
1656 p_lines_rec.tax,
1657 p_lines_rec.tax_jurisdiction_code,
1658 p_lines_rec.tax_status_code,
1659 p_lines_rec.tax_rate_id,
1660 p_lines_rec.tax_rate_code,
1661 p_lines_rec.tax_rate,
1662 p_lines_rec.wfapproval_status,
1663 p_lines_rec.pa_quantity,
1664 p_lines_rec.instruction_id,
1665 p_lines_rec.adj_type,
1666 AP_INVOICE_LINES_INTERFACE_S.nextval,
1667 p_lines_rec.cost_factor_id);
1668
1669
1670 --
1671 RETURN(TRUE);
1672 --
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1676 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1677 debug_info);
1678 END IF;
1679
1680 IF (SQLCODE < 0) then
1681 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1682 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1683 SQLERRM);
1684 END IF;
1685 END IF;
1686
1687 RETURN(FALSE);
1688
1689 END Create_Line;
1690
1691
1692
1693 /*============================================================================
1694 | FUNCTION - Get_Base_Match_Lines()
1695 |
1696 | DESCRIPTION
1697 | This function returns the list of all base matched Invoice Lines
1698 | for the Instruction that are candidate for retropricing.
1699 | Note: Retro price Adjustments and Adjustment corrections may already
1700 | exist for these base matched lines.
1701 |
1702 | PARAMETERS
1703 | p_instruction_id
1704 | p_instruction_line_id
1705 | p_base_match_lines_list
1706 | P_calling_sequence
1707 |
1708 | MODIFICATION HISTORY
1709 | Date Author Description of Change
1710 | 29-JUL-2003 dgulraja Creation
1711 |
1712 *==========================================================================*/
1713 FUNCTION Get_Base_Match_Lines(
1714 p_instruction_id IN NUMBER,
1715 p_instruction_line_id IN NUMBER,
1716 p_base_match_lines_list OUT NOCOPY AP_RETRO_PRICING_PKG.invoice_lines_list_type,
1717 P_calling_sequence IN VARCHAR2)
1718 RETURN BOOLEAN IS
1719
1720 current_calling_sequence VARCHAR2(1000);
1721 debug_info VARCHAR2(1000);
1722
1723 CURSOR base_match_lines IS
1724 SELECT L.invoice_id,
1725 L.line_number,
1726 L.line_type_lookup_code,
1727 L.requester_id,
1728 L.description,
1729 L.line_source,
1730 L.org_id,
1731 L.inventory_item_id,
1732 L.item_description,
1733 L.serial_number,
1734 L.manufacturer,
1735 L.model_number,
1736 L.generate_dists,
1737 L.match_type,
1738 L.default_dist_ccid,
1739 L.prorate_across_all_items,
1740 L.accounting_date,
1741 L.period_name,
1742 L.deferred_acctg_flag,
1743 L.set_of_books_id,
1744 L.amount,
1745 L.base_amount,
1746 L.rounding_amt,
1747 L.quantity_invoiced,
1748 L.unit_meas_lookup_code,
1749 L.unit_price,
1750 -- L.ussgl_transaction_code, - Bug 4277744
1751 L.discarded_flag,
1752 L.cancelled_flag,
1753 L.income_tax_region,
1754 L.type_1099,
1755 L.corrected_inv_id,
1756 L.corrected_line_number,
1757 L.po_header_id,
1758 L.po_line_id,
1759 L.po_release_id,
1760 L.po_line_location_id,
1761 L.po_distribution_id,
1762 L.rcv_transaction_id,
1763 L.final_match_flag,
1764 L.assets_tracking_flag,
1765 L.asset_book_type_code,
1766 L.asset_category_id,
1767 L.project_id,
1768 L.task_id,
1769 L.expenditure_type,
1770 L.expenditure_item_date,
1771 L.expenditure_organization_id,
1772 L.award_id,
1773 L.awt_group_id,
1774 L.pay_awt_group_id, --Bug 6832773
1775 L.receipt_verified_flag,
1776 L.receipt_required_flag,
1777 L.receipt_missing_flag,
1778 L.justification,
1779 L.expense_group,
1780 L.start_expense_date,
1781 L.end_expense_date,
1782 L.receipt_currency_code,
1783 L.receipt_conversion_rate,
1784 L.receipt_currency_amount,
1785 L.daily_amount,
1786 L.web_parameter_id,
1787 L.adjustment_reason,
1788 L.merchant_document_number,
1789 L.merchant_name,
1790 L.merchant_reference,
1791 L.merchant_tax_reg_number,
1792 L.merchant_taxpayer_id,
1793 L.country_of_supply,
1794 L.credit_card_trx_id,
1795 L.company_prepaid_invoice_id,
1796 L.cc_reversal_flag,
1797 L.creation_date,
1798 L.created_by,
1799 L.attribute_category,
1800 L.attribute1,
1801 L.attribute2,
1802 L.attribute3,
1803 L.attribute4,
1804 L.attribute5,
1805 L.attribute6,
1806 L.attribute7,
1807 L.attribute8,
1808 L.attribute9,
1809 L.attribute10,
1810 L.attribute11,
1811 L.attribute12,
1812 L.attribute13,
1813 L.attribute14,
1814 L.attribute15,
1815 L.global_attribute_category,
1816 L.global_attribute1,
1817 L.global_attribute2,
1818 L.global_attribute3,
1819 L.global_attribute4,
1820 L.global_attribute5,
1821 L.global_attribute6,
1822 L.global_attribute7,
1823 L.global_attribute8,
1824 L.global_attribute9,
1825 L.global_attribute10,
1826 L.global_attribute11,
1827 L.global_attribute12,
1828 L.global_attribute13,
1829 L.global_attribute14,
1830 L.global_attribute15,
1831 L.global_attribute16,
1832 L.global_attribute17,
1833 L.global_attribute18,
1834 L.global_attribute19,
1835 L.global_attribute20,
1836 L.primary_intended_use,
1837 L.ship_to_location_id,
1838 L.product_type,
1839 L.product_category,
1840 L.product_fisc_classification,
1841 L.user_defined_fisc_class,
1842 L.trx_business_category,
1843 L.summary_tax_line_id,
1844 L.tax_regime_code,
1845 L.tax,
1846 L.tax_jurisdiction_code,
1847 L.tax_status_code,
1848 L.tax_rate_id,
1849 L.tax_rate_code,
1850 L.tax_rate,
1851 L.wfapproval_status,
1852 L.pa_quantity,
1853 p_instruction_id, --instruction_id
1854 NULL , --adj_type
1855 L.cost_factor_id, --cost_factor_id
1856 L.TAX_CLASSIFICATION_CODE,
1857 L.SOURCE_APPLICATION_ID,
1858 L.SOURCE_EVENT_CLASS_CODE,
1859 L.SOURCE_ENTITY_CODE,
1860 L.SOURCE_TRX_ID,
1861 L.SOURCE_LINE_ID,
1862 L.SOURCE_TRX_LEVEL_TYPE,
1863 L.PA_CC_AR_INVOICE_ID,
1864 L.PA_CC_AR_INVOICE_LINE_NUM,
1865 L.PA_CC_PROCESSED_CODE,
1866 L.REFERENCE_1,
1867 L.REFERENCE_2,
1868 L.DEF_ACCTG_START_DATE,
1869 L.DEF_ACCTG_END_DATE,
1870 L.DEF_ACCTG_NUMBER_OF_PERIODS,
1871 L.DEF_ACCTG_PERIOD_TYPE,
1872 L.REFERENCE_KEY5,
1873 L.PURCHASING_CATEGORY_ID,
1874 NULL, -- line group number
1875 L.WARRANTY_NUMBER,
1876 L.REFERENCE_KEY3,
1877 L.REFERENCE_KEY4,
1878 L.APPLICATION_ID,
1879 L.PRODUCT_TABLE,
1880 L.REFERENCE_KEY1,
1881 L.REFERENCE_KEY2,
1882 L.RCV_SHIPMENT_LINE_ID
1883 FROM ap_invoice_lines L,
1884 ap_invoice_lines_interface IL
1885 WHERE L.po_line_location_id = IL.po_line_location_id
1886 AND IL.invoice_id = p_instruction_id
1887 AND IL.invoice_line_id = p_instruction_line_id
1888 AND L.discarded_flag <> 'Y'
1889 AND L.cancelled_flag <> 'Y'
1890 AND L.line_type_lookup_code = 'ITEM'
1891 AND L.match_type IN ('ITEM_TO_PO', 'ITEM_TO_RECEIPT')
1892 AND L.generate_dists = 'D'
1893 ORDER BY L.invoice_id; --Added for bug#9855094
1894
1895 BEGIN
1896 --
1897 current_calling_sequence := 'AP_RETRO_PRICING_PKG.Get_Base_Match_Lines'
1898 ||P_Calling_Sequence;
1899
1900 debug_info := 'Open base_match_lines';
1901 OPEN base_match_lines;
1902 FETCH base_match_lines
1903 BULK COLLECT INTO p_base_match_lines_list;
1904 CLOSE base_match_lines;
1905 --
1906 RETURN(TRUE);
1907 --
1908 EXCEPTION
1909 WHEN OTHERS THEN
1910 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1911 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1912 debug_info);
1913 END IF;
1914 --
1915 IF (SQLCODE < 0) then
1916 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1917 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1918 SQLERRM);
1919 END IF;
1920 END IF;
1921 --
1922 IF ( base_match_lines%ISOPEN ) THEN
1923 CLOSE base_match_lines;
1924 END IF;
1925 --
1926 RETURN(FALSE);
1927
1928 END Get_Base_Match_Lines;
1929
1930 /*============================================================================
1931 | FUNCTION - Create_ppa_Invoice()
1932 |
1933 | DESCRIPTION
1934 | This function inserts a temporary Ppa Invoice Header in the Global
1935 | Temporary Tables.
1936 |
1937 | PARAMETERS
1938 | p_instruction_id
1939 | p_instruction_line_id
1940 | p_base_match_lines_list
1941 | P_calling_sequence
1942 |
1943 | MODIFICATION HISTORY
1944 | Date Author Description of Change
1945 | 29-JUL-2003 dgulraja Creation
1946 |
1947 *==========================================================================*/
1948 FUNCTION Create_ppa_Invoice(
1949 p_instruction_id IN NUMBER,
1950 p_invoice_id IN NUMBER, --Base match line's invoice_id
1951 p_line_number IN NUMBER, --Base match line number
1952 p_batch_id IN NUMBER,
1953 p_ppa_invoice_rec OUT NOCOPY AP_RETRO_PRICING_PKG.invoice_rec_type,
1954 P_calling_sequence IN VARCHAR2)
1955 RETURN BOOLEAN IS
1956 CURSOR ppa_header
1957 IS
1958 SELECT ap_invoices_s.NEXTVAL, --invoice_id
1959 NVL(AII.vendor_id, AI.vendor_id), --vendor_id
1960 AI.invoice_num, --invoice_num
1961 AI.set_of_books_id, --set_of_books_id
1962 AI.invoice_currency_code, --invoice_currency_code
1963 NVL(AII.payment_currency_code, AI.payment_currency_code),
1964 NVL(AII.payment_cross_rate, AI.payment_cross_rate),
1965 NULL, --invoice_amount
1966 --Bugfix:4681253
1967 AI.vendor_site_id, --vendor_site_id -- from po_view
1968 TRUNC(SYSDATE), --invoice_date
1969 'PPA', --source
1970 'PO PRICE ADJUST', --Invoice_type_lookup_code
1971 NULL, --description
1972 NULL,
1973 NVL(AII.terms_id, AI.terms_id), --terms_id
1974 trunc(sysdate), --terms_date
1975 NVL(AII.payment_method_code, AI.payment_method_code), --4552701
1976 NVL(AII.Pay_group_lookup_code, AI.pay_group_lookup_code),
1977 NVL(AII.accts_pay_code_combination_id, AI.accts_pay_code_combination_id),
1978 'N', --payment_status_flag
1979 SYSDATE, --creation_date
1980 AII.created_by, --created_by
1981 NULL, --base_amount
1982 DECODE(sign(AI.invoice_amount), -1, 'N', AI.exclusive_payment_flag),
1983 AI.goods_received_date, --goods_received_date
1984 NULL, --invoice_received_date
1985 -- Bug 5469166. Modified to 'User' from 'USER'
1986 DECODE(AI.exchange_rate_type, 'User', NVL(AII.exchange_rate, AI.exchange_rate),
1987 NULL) exchange_rate,
1988 NVL(AII.exchange_rate_type, AI.exchange_rate_type) exchange_rate_type,
1989 DECODE(AI.exchange_rate_type, 'User', AI.exchange_date,
1990 NULL,NULL,
1991 trunc(sysdate)) exchange_date,
1992 AI.attribute1,
1993 AI.attribute2,
1994 AI.attribute3,
1995 AI.attribute4,
1996 AI.attribute5,
1997 AI.attribute6,
1998 AI.attribute7,
1999 AI.attribute8,
2000 AI.attribute9,
2001 AI.attribute10,
2002 AI.attribute11,
2003 AI.attribute12,
2004 AI.attribute13,
2005 AI.attribute14,
2006 AI.attribute15,
2007 AI.attribute_category,
2008 -- AI.ussgl_transaction_code, - Bug 4277744
2009 -- AI.ussgl_trx_code_context, - Bug 4277744
2010 AI.project_id,
2011 AI.task_id,
2012 AI.expenditure_type,
2013 AI.expenditure_item_date,
2014 AI.expenditure_organization_id,
2015 AI.pa_default_dist_ccid,
2016 'N', --awt_flag
2017 AI.awt_group_id, --awt_group_id
2018 AI.pay_awt_group_id, --pay_awt_group_id Bug 6832773
2019 AI.org_id, --org_id
2020 AI.award_id, --award_id
2021 'Y', --approval_ready_flag
2022 'NOT REQUIRED', --wfapproval_status
2023 NVL(AII.requester_id, AI.requester_id),
2024 AI.global_attribute_category,
2025 NVL(aii.global_attribute1, AI.global_attribute1),
2026 NVL(aii.global_attribute2, AI.global_attribute2),
2027 NVL(aii.global_attribute3, AI.global_attribute3),
2028 NVL(aii.global_attribute4, AI.global_attribute4),
2029 NVL(aii.global_attribute5, AI.global_attribute5),
2030 NVL(aii.global_attribute6, AI.global_attribute6),
2031 NVL(aii.global_attribute7, AI.global_attribute7),
2032 NVL(aii.global_attribute8, AI.global_attribute8),
2033 NVL(AII.global_attribute9, AI.global_attribute9),
2034 NVL(AII.global_attribute10, AI.global_attribute10),
2035 NVL(AII.global_attribute11, AI.global_attribute11),
2036 NVL(AII.global_attribute12, AI.global_attribute12),
2037 NVL(AII.global_attribute13, AI.global_attribute13),
2038 NVL(AII.global_attribute14, AI.global_attribute14),
2039 NVL(AII.global_attribute15, AI.global_attribute15),
2040 NVL(AII.global_attribute16, AI.global_attribute16),
2041 NVL(AII.global_attribute17, AI.global_attribute17),
2042 NVL(AII.global_attribute18, AI.global_attribute18),
2043 NVL(AII.global_attribute19, AI.global_attribute19),
2044 NVL(AII.global_attribute20, AI.global_attribute20),
2045 p_instruction_id, --instruction_id
2046 'U', --instr_status_flag
2047 p_batch_id, --batch_id
2048 NULL, --doc_sequence_id
2049 NULL, --doc_sequence_value
2050 NULL, --doc_category_code
2051 ai.APPLICATION_ID ,
2052 ai.BANK_CHARGE_BEARER ,
2053 ai.DELIVERY_CHANNEL_CODE ,
2054 ai.DISC_IS_INV_LESS_TAX_FLAG ,
2055 ai.DOCUMENT_SUB_TYPE ,
2056 ai.EXCLUDE_FREIGHT_FROM_DISCOUNT ,
2057 ai.EXTERNAL_BANK_ACCOUNT_ID ,
2058 NULL , -- gl date
2059 ai.LEGAL_ENTITY_ID ,
2060 ai.NET_OF_RETAINAGE_FLAG ,
2061 ai.PARTY_ID ,
2062 ai.PARTY_SITE_ID ,
2063 ai.PAYMENT_CROSS_RATE_DATE ,
2064 ai.PAYMENT_CROSS_RATE_TYPE ,
2065 ai.PAYMENT_FUNCTION ,
2066 ai.PAYMENT_REASON_CODE ,
2067 ai.PAYMENT_REASON_COMMENTS ,
2068 ai.PAY_CURR_INVOICE_AMOUNT ,
2069 ai.PAY_PROC_TRXN_TYPE_CODE ,
2070 ai.PORT_OF_ENTRY_CODE ,
2071 ai.POSTING_STATUS ,
2072 ai.PO_HEADER_ID ,
2073 ai.PRODUCT_TABLE ,
2074 ai.PROJECT_ACCOUNTING_CONTEXT ,
2075 ai.QUICK_PO_HEADER_ID ,
2076 ai.REFERENCE_1 ,
2077 ai.REFERENCE_2 ,
2078 ai.REFERENCE_KEY1 ,
2079 ai.REFERENCE_KEY2 ,
2080 ai.REFERENCE_KEY3 ,
2081 ai.REFERENCE_KEY4 ,
2082 ai.REFERENCE_KEY5 ,
2083 ai.REMITTANCE_MESSAGE1 ,
2084 ai.REMITTANCE_MESSAGE2 ,
2085 ai.REMITTANCE_MESSAGE3 ,
2086 ai.SETTLEMENT_PRIORITY ,
2087 ai.SUPPLIER_TAX_EXCHANGE_RATE ,
2088 ai.SUPPLIER_TAX_INVOICE_DATE ,
2089 ai.SUPPLIER_TAX_INVOICE_NUMBER ,
2090 ai.TAXATION_COUNTRY ,
2091 ai.TAX_INVOICE_INTERNAL_SEQ ,
2092 ai.TAX_INVOICE_RECORDING_DATE ,
2093 ai.TAX_RELATED_INVOICE_ID ,
2094 ai.TRX_BUSINESS_CATEGORY ,
2095 ai.UNIQUE_REMITTANCE_IDENTIFIER ,
2096 ai.URI_CHECK_DIGIT ,
2097 ai.USER_DEFINED_FISC_CLASS
2098 FROM ap_invoices_all AI,
2099 ap_invoices_interface AII
2100 WHERE AII.invoice_id = p_instruction_id -- instruction_rec.invoice_id
2101 AND AI.vendor_id = AII.vendor_id
2102 AND AI.invoice_id = p_invoice_id; -- base_match_lines_rec.invoice_id
2103
2104 l_new_ppa_count NUMBER;
2105 l_existing_ppa_count NUMBER;
2106 l_temp_ppa_count NUMBER; --bug#9855094
2107 l_ppa_invoice_rec AP_RETRO_PRICING_PKG.invoice_rec_type;
2108 l_description AP_INVOICES_ALL.description%TYPE;
2109 l_dbseqnm VARCHAR2(30);
2110 l_seqassid NUMBER;
2111 l_seq_num_profile VARCHAR2(80);
2112 l_return_code NUMBER;
2113 current_calling_sequence VARCHAR2(1000);
2114 debug_info VARCHAR2(1000);
2115
2116
2117 BEGIN
2118
2119 current_calling_sequence := 'AP_RETRO_PRICING_PKG.Create_ppa_Invoice'
2120 ||P_Calling_Sequence;
2121 ---------------------------------------------
2122 debug_info := 'Create_Ppa_Invoice Step :1 Open cursor ppa_header';
2123 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2124 AP_IMPORT_UTILITIES_PKG.Print(
2125 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2126 END IF;
2127 ---------------------------------------------
2128 OPEN ppa_header;
2129 FETCH ppa_header INTO l_ppa_invoice_rec;
2130 IF (ppa_header%NOTFOUND) THEN
2131 CLOSE ppa_header;
2132 RAISE NO_DATA_FOUND;
2133 END IF;
2134 CLOSE ppa_header;
2135
2136 -------------------------------------------
2137 debug_info := 'Create_Ppa_Invoice Step :2 Get meaning';
2138 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2139 AP_IMPORT_UTILITIES_PKG.Print(
2140 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2141 END IF;
2142 -------------------------------------------
2143 SELECT displayed_field
2144 INTO l_description
2145 FROM ap_lookup_codes
2146 WHERE lookup_type = 'LINE SOURCE'
2147 AND lookup_code = 'PO PRICE ADJUSTMENT';
2148
2149 l_ppa_invoice_rec.description := l_description || '-' ||
2150 l_ppa_invoice_rec.invoice_num;
2151
2152 ----------------------------------------------------------------
2153 debug_info := 'Create_Ppa_Invoice Step :3 Get existing ppa count for the base matched line';
2154 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2155 AP_IMPORT_UTILITIES_PKG.Print(
2156 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2157 END IF;
2158 -----------------------------------------------------------------
2159 SELECT count(*)
2160 INTO l_existing_ppa_count
2161 FROM ap_invoices_all I
2162 WHERE I.source = 'PPA'
2163 AND EXISTS (SELECT invoice_id
2164 FROM ap_invoice_lines_all L
2165 WHERE L.invoice_id = I.invoice_id
2166 AND L.corrected_inv_id = p_invoice_id
2167 --Commented below condition for bug#9855094
2168 --AND L.corrected_line_number = p_line_number
2169 AND L.match_type = 'PO_PRICE_ADJUSTMENT');
2170
2171
2172 --Introduced below SELECT for bug#9855094
2173 SELECT count(*)
2174 INTO l_temp_ppa_count
2175 FROM ap_ppa_invoices_gt apig
2176 WHERE instruction_id = p_instruction_id
2177 and exists(select invoice_id
2178 from ap_ppa_invoice_lines_gt apilg
2179 where apilg.invoice_id = apig.invoice_id
2180 and apilg.corrected_inv_id = p_invoice_id
2181 -- and apilg.adj_type = 'PPA' --Commented for bug#9573078
2182 and nvl(apilg.amount,0) <> 0); --Modified for bug#9573078
2183
2184 l_ppa_invoice_rec.invoice_num := l_ppa_invoice_rec.source
2185 || '-' || substrb(l_ppa_invoice_rec.invoice_num,0,27)
2186 || '-' ||(l_existing_ppa_count + l_temp_ppa_count+1); --bug#9855094
2187
2188 debug_info := 'l_ppa_invoice_rec.invoice_num is '||l_ppa_invoice_rec.invoice_num;
2189 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2190 AP_IMPORT_UTILITIES_PKG.Print(
2191 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2192 END IF;
2193 -- Removed step 4 and step 5 for bug8514744
2194 -- Same logic moved to apretrob.pls
2195 ------------------------------------------------
2196 debug_info := 'Create_Ppa_Invoice Step :6 Insert into ap_ppa_invoices_gt';
2197 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2198 AP_IMPORT_UTILITIES_PKG.Print(
2199 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2200 END IF;
2201 ------------------------------------------------
2202 INSERT INTO ap_ppa_invoices_gt(
2203 accts_pay_code_combination_id,
2204 amount_applicable_to_discount,
2205 approval_ready_flag,
2206 attribute_category,
2207 attribute1,
2208 attribute10,
2209 attribute11,
2210 attribute12,
2211 attribute13,
2212 attribute14,
2213 attribute15,
2214 attribute2,
2215 attribute3,
2216 attribute4,
2217 attribute5,
2218 attribute6,
2219 attribute7,
2220 attribute8,
2221 attribute9,
2222 award_id,
2223 awt_flag,
2224 awt_group_id,
2225 pay_awt_group_id, -- Bug 6832773
2226 base_amount,
2227 batch_id,
2228 created_by,
2229 creation_date,
2230 description,
2231 exchange_date,
2232 exchange_rate,
2233 exchange_rate_type,
2234 exclusive_payment_flag,
2235 expenditure_item_date,
2236 expenditure_organization_id,
2237 expenditure_type,
2238 global_attribute_category,
2239 global_attribute1,
2240 global_attribute10,
2241 global_attribute11,
2242 global_attribute12,
2243 global_attribute13,
2244 global_attribute14,
2245 global_attribute15,
2246 global_attribute16,
2247 global_attribute17,
2248 global_attribute18,
2249 global_attribute19,
2250 global_attribute2,
2251 global_attribute20,
2252 global_attribute3,
2253 global_attribute4,
2254 global_attribute5,
2255 global_attribute6,
2256 global_attribute7,
2257 global_attribute8,
2258 global_attribute9,
2259 goods_received_date,
2260 invoice_amount,
2261 invoice_currency_code,
2262 invoice_date,
2263 invoice_id,
2264 invoice_num,
2265 invoice_received_date,
2266 invoice_type_lookup_code,
2267 org_id,
2268 pa_default_dist_ccid,
2269 pay_group_lookup_code,
2270 payment_cross_rate,
2271 payment_currency_code,
2272 payment_method_code,
2273 payment_status_flag,
2274 project_id,
2275 requester_id,
2276 set_of_books_id,
2277 source,
2278 task_id,
2279 terms_date,
2280 terms_id,
2281 -- ussgl_transaction_code, - Bug 4277744
2282 -- ussgl_trx_code_context, - Bug 4277744
2283 vendor_id,
2284 vendor_site_id,
2285 wfapproval_status,
2286 doc_sequence_id,
2287 doc_sequence_value,
2288 doc_category_code,
2289 instruction_id,
2290 instr_status_flag,
2291 party_id,
2292 party_site_id,
2293 legal_entity_id,
2294 external_bank_account_id, /*Bug 9048000: Added external bank account id*/
2295 taxation_country,
2296 bank_charge_bearer,
2297 trx_business_category,
2298 user_defined_fisc_class) /*Bug 10017013:Added 4 new columns*/
2299 VALUES ( l_ppa_invoice_rec.accts_pay_code_combination_id,
2300 l_ppa_invoice_rec.amount_applicable_to_discount,
2301 l_ppa_invoice_rec.approval_ready_flag,
2302 l_ppa_invoice_rec.attribute_category,
2303 l_ppa_invoice_rec.attribute1,
2304 l_ppa_invoice_rec.attribute10,
2305 l_ppa_invoice_rec.attribute11,
2306 l_ppa_invoice_rec.attribute12,
2307 l_ppa_invoice_rec.attribute13,
2308 l_ppa_invoice_rec.attribute14,
2309 l_ppa_invoice_rec.attribute15,
2310 l_ppa_invoice_rec.attribute2,
2311 l_ppa_invoice_rec.attribute3,
2312 l_ppa_invoice_rec.attribute4,
2313 l_ppa_invoice_rec.attribute5,
2314 l_ppa_invoice_rec.attribute6,
2315 l_ppa_invoice_rec.attribute7,
2316 l_ppa_invoice_rec.attribute8,
2317 l_ppa_invoice_rec.attribute9,
2318 l_ppa_invoice_rec.award_id,
2319 l_ppa_invoice_rec.awt_flag,
2320 l_ppa_invoice_rec.awt_group_id,
2321 l_ppa_invoice_rec.pay_awt_group_id, -- Bug 6832773
2322 l_ppa_invoice_rec.base_amount,
2323 p_batch_id,
2324 l_ppa_invoice_rec.created_by,
2325 l_ppa_invoice_rec.creation_date,
2326 l_ppa_invoice_rec.description,
2327 l_ppa_invoice_rec.exchange_date,
2328 l_ppa_invoice_rec.exchange_rate,
2329 l_ppa_invoice_rec.exchange_rate_type,
2330 l_ppa_invoice_rec.exclusive_payment_flag,
2331 l_ppa_invoice_rec.expenditure_item_date,
2332 l_ppa_invoice_rec.expenditure_organization_id,
2333 l_ppa_invoice_rec.expenditure_type,
2334 l_ppa_invoice_rec.global_attribute_category,
2335 l_ppa_invoice_rec.global_attribute1,
2336 l_ppa_invoice_rec.global_attribute10,
2337 l_ppa_invoice_rec.global_attribute11,
2338 l_ppa_invoice_rec.global_attribute12,
2339 l_ppa_invoice_rec.global_attribute13,
2340 l_ppa_invoice_rec.global_attribute14,
2341 l_ppa_invoice_rec.global_attribute15,
2342 l_ppa_invoice_rec.global_attribute16,
2343 l_ppa_invoice_rec.global_attribute17,
2344 l_ppa_invoice_rec.global_attribute18,
2345 l_ppa_invoice_rec.global_attribute19,
2346 l_ppa_invoice_rec.global_attribute2,
2347 l_ppa_invoice_rec.global_attribute20,
2348 l_ppa_invoice_rec.global_attribute3,
2349 l_ppa_invoice_rec.global_attribute4,
2350 l_ppa_invoice_rec.global_attribute5,
2351 l_ppa_invoice_rec.global_attribute6,
2352 l_ppa_invoice_rec.global_attribute7,
2353 l_ppa_invoice_rec.global_attribute8,
2354 l_ppa_invoice_rec.global_attribute9,
2355 l_ppa_invoice_rec.goods_received_date,
2356 l_ppa_invoice_rec.invoice_amount,
2357 l_ppa_invoice_rec.invoice_currency_code,
2358 l_ppa_invoice_rec.invoice_date,
2359 l_ppa_invoice_rec.invoice_id,
2360 l_ppa_invoice_rec.invoice_num,
2361 l_ppa_invoice_rec.invoice_received_date,
2362 l_ppa_invoice_rec.invoice_type_lookup_code,
2363 l_ppa_invoice_rec.org_id,
2364 l_ppa_invoice_rec.pa_default_dist_ccid,
2365 l_ppa_invoice_rec.pay_group_lookup_code,
2366 l_ppa_invoice_rec.payment_cross_rate,
2367 l_ppa_invoice_rec.payment_currency_code,
2368 l_ppa_invoice_rec.payment_method_code,
2369 l_ppa_invoice_rec.payment_status_flag,
2370 l_ppa_invoice_rec.project_id,
2371 l_ppa_invoice_rec.requester_id,
2372 l_ppa_invoice_rec.set_of_books_id,
2373 l_ppa_invoice_rec.source,
2374 l_ppa_invoice_rec.task_id,
2375 l_ppa_invoice_rec.terms_date,
2376 l_ppa_invoice_rec.terms_id,
2377 -- l_ppa_invoice_rec.ussgl_transaction_code, - Bug 4277744
2378 -- l_ppa_invoice_rec.ussgl_trx_code_context, - Bug 4277744
2379 l_ppa_invoice_rec.vendor_id,
2380 l_ppa_invoice_rec.vendor_site_id,
2381 l_ppa_invoice_rec.wfapproval_status,
2382 l_ppa_invoice_rec.doc_sequence_id,
2383 l_ppa_invoice_rec.doc_sequence_value,
2384 l_ppa_invoice_rec.doc_category_code,
2385 l_ppa_invoice_rec.instruction_id,
2386 l_ppa_invoice_rec.instr_status_flag,
2387 l_ppa_invoice_rec.party_id,
2388 l_ppa_invoice_rec.party_site_id,
2389 l_ppa_invoice_rec.legal_entity_id,
2390 l_ppa_invoice_rec.external_bank_account_id, /*Bug 9048000: Added external bank account id*/
2391 l_ppa_invoice_rec.taxation_country,
2392 l_ppa_invoice_rec.bank_charge_bearer,
2393 l_ppa_invoice_rec.trx_business_category,
2394 l_ppa_invoice_rec.user_defined_fisc_class); /*Bug 10017013:Added 4 new columns*/
2395
2396 --Bugfix:4681253
2397 p_ppa_invoice_rec := l_ppa_invoice_rec;
2398 --
2399 RETURN(TRUE);
2400 --
2401 EXCEPTION
2402 WHEN OTHERS THEN
2403 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2404 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
2405 debug_info);
2406 END IF;
2407
2408 IF (SQLCODE < 0) then
2409 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2410 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
2411 SQLERRM);
2412 END IF;
2413 END IF;
2414 --
2415 IF ( ppa_header%ISOPEN ) THEN
2416 CLOSE ppa_header;
2417 END IF;
2418 --
2419 RETURN(FALSE);
2420
2421 END Create_ppa_Invoice;
2422
2423
2424 /*============================================================================
2425 | FUNCTION - get_invoice_num()
2426 |
2427 | DESCRIPTION
2428 | This function is called from the APXIIMPT.rdf
2429 |
2430 | PARAMETERS
2431 | p_invoice_id
2432 |
2433 | MODIFICATION HISTORY
2434 | Date Author Description of Change
2435 | 29-JUL-2003 dgulraja Creation
2436 |
2437 *==========================================================================*/
2438 FUNCTION get_invoice_num(
2439 p_invoice_id IN NUMBER)
2440 RETURN VARCHAR2 IS
2441
2442 l_invoice_num VARCHAR2(50);
2443 debug_info VARCHAR2(1000);
2444 BEGIN
2445 debug_info := 'Get invoice_num for the corrected invoice';
2446 SELECT invoice_num
2447 INTO l_invoice_num
2448 FROM ap_invoices_all
2449 WHERE invoice_id = p_invoice_id;
2450
2451 RETURN l_invoice_num;
2452
2453 EXCEPTION
2454 WHEN NO_DATA_FOUND THEN
2455 NULL;
2456 END get_invoice_num;
2457
2458
2459 /*============================================================================
2460 | FUNCTION - get_corrected_pc_line_num()
2461 |
2462 | DESCRIPTION
2463 | This function is called to get the corrected line number for the
2464 | Ajustment Correction Lines on the PPA document.
2465 | Note: These lines correct the Zero Line Adjustments Lines for a PC.
2466 |
2467 | PARAMETERS
2468 | p_invoice_id
2469 |
2470 | MODIFICATION HISTORY
2471 | Date Author Description of Change
2472 | 29-JUL-2003 dgulraja Creation
2473 |
2474 *==========================================================================*/
2475 FUNCTION get_corrected_pc_line_num(
2476 p_invoice_id IN NUMBER,
2477 p_line_number IN NUMBER)
2478 RETURN NUMBER IS
2479
2480 l_line_number NUMBER;
2481 debug_info VARCHAR2(1000);
2482
2483 BEGIN
2484 debug_info := 'Get invoice_num for the corrected invoice';
2485 SELECT line_number
2486 INTO l_line_number
2487 FROM ap_ppa_invoice_lines_gt
2488 WHERE invoice_id = p_invoice_id
2489 AND corrected_line_number = p_line_number
2490 AND match_type = 'ADJUSTMENT_CORRECTION';
2491
2492 RETURN (l_line_number);
2493
2494 EXCEPTION
2495 WHEN NO_DATA_FOUND THEN
2496 NULL;
2497 END get_corrected_pc_line_num;
2498
2499
2500 /*=============================================================================
2501 | FUNCTION - Get_Erv_Ccid()
2502 |
2503 | DESCRIPTION
2504 | This function returns the ccid of the ERV distribution related to the
2505 | IPV distribution on the Price Correction and (IPV+Item) distribution
2506 | on the base match or qty correction.
2507 |
2508 |
2509 | PARAMETERS
2510 | p_invoice_distribution_id
2511 |
2512 | MODIFICATION HISTORY
2513 | Date Author Description of Change
2514 | 29-JUL-2003 dgulraja Creation
2515 |
2516 *============================================================================*/
2517 -- Bug 5469166. Modified the logic to derive erv ccid based original IPV
2518 FUNCTION Get_Erv_Ccid(
2519 p_invoice_distribution_id IN NUMBER)
2520 RETURN NUMBER IS
2521
2522 l_ccid NUMBER;
2523 debug_info VARCHAR2(1000);
2524
2525 BEGIN
2526 debug_info := 'Get ERV ccid';
2527 SELECT aid1.dist_code_combination_id
2528 INTO l_ccid
2529 FROM ap_invoice_distributions_all aid1
2530 WHERE aid1.line_type_lookup_code = 'ERV'
2531 AND aid1.related_id = (SELECT aid2.related_id
2532 FROM ap_invoice_distributions_all aid2
2533 WHERE aid2.line_type_lookup_code = 'IPV'
2534 AND aid2.invoice_distribution_id =
2535 p_invoice_distribution_id);
2536 --
2537 RETURN (l_ccid);
2538 --
2539 /* Bug#10416960
2540 EXCEPTION
2541 WHEN NO_DATA_FOUND THEN
2542 RETURN (NULL); */
2543
2544 END Get_Erv_Ccid;
2545
2546
2547 /*=============================================================================
2548 | FUNCTION - Get_Terv_Ccid()
2549 |
2550 | DESCRIPTION
2551 | This function returns the ccid of the TERV distribution related to the
2552 | TIPV distribution.
2553 |
2554 | PARAMETERS
2555 | p_invoice_distribution_id
2556 |
2557 | MODIFICATION HISTORY
2558 | Date Author Description of Change
2559 | 29-JUL-2003 dgulraja Creation
2560 |
2561 *============================================================================*/
2562 -- Bug 5469166. Modified the logic to derive erv ccid based original TIPV
2563 FUNCTION Get_Terv_Ccid(
2564 p_invoice_distribution_id IN NUMBER)
2565 RETURN NUMBER IS
2566
2567 l_ccid NUMBER;
2568 debug_info VARCHAR2(1000);
2569
2570 BEGIN
2571 debug_info := 'Get Terv ccid';
2572 SELECT aid1.dist_code_combination_id
2573 INTO l_ccid
2574 FROM ap_invoice_distributions_all aid1
2575 WHERE aid1.line_type_lookup_code = 'TERV'
2576 AND aid1.related_id = (SELECT aid2.related_id
2577 FROM ap_invoice_distributions_all aid2
2578 WHERE aid2.line_type_lookup_code = 'TIPV'
2579 AND aid2.invoice_distribution_id =
2580 p_invoice_distribution_id);
2581 --
2582 RETURN (l_ccid);
2583 --
2584 /* Bug#10416960
2585 EXCEPTION
2586 WHEN NO_DATA_FOUND THEN
2587 RETURN(NULL); */
2588 END Get_Terv_Ccid;
2589
2590
2591 END AP_RETRO_PRICING_UTIL_PKG;