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