DBA Data[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;