[Home] [Help]
PACKAGE BODY: APPS.ICX_AP_INVOICE_PAYMENTS_PKG
Source
1 PACKAGE BODY ICX_AP_INVOICE_PAYMENTS_PKG AS
2 /* $Header: ICXAPPAB.pls 115.0 99/08/09 17:21:56 porting ship $ */
3
4 -----------------------------------------------------------------------
5 -- Declare record type used to hold records returned from generic cursor
6 --
7 TYPE PAYMENT_RECORD IS
8 RECORD (check_number ap_checks.check_number%TYPE,
9 check_id ap_checks.check_id%TYPE,
10 check_date ap_checks.check_date%TYPE,
11 invoice_num ap_invoices.invoice_num%TYPE,
12 invoice_id ap_invoices.invoice_id%TYPE,
13 gl_date ap_invoice_payments.accounting_date%TYPE,
14 type ap_invoice_payments.invoice_payment_type%TYPE,
15 type_trans ap_lookup_codes.displayed_field%TYPE,
16 method ap_checks.payment_method_lookup_code%TYPE,
17 method_trans ap_lookup_codes.displayed_field%TYPE);
18
19 -----------------------------------------------------------------------
20 -- Declare generic cursor to get check number, check id, invoice number
21 -- invoice id, payment type, payment method lookup code, and translated
22 -- payment method
23 --
24 CURSOR payment_cursor (l_invoice_id NUMBER, l_payment_num NUMBER)
25 RETURN payment_record IS
26 SELECT ac.check_number,
27 ac.check_id,
28 ac.check_date,
29 ai.invoice_num,
30 ai.invoice_id,
31 aip.accounting_date,
32 aip.invoice_payment_type,
33 alc1.displayed_field,
34 ac.payment_method_lookup_code,
35 alc2.displayed_field
36 FROM ap_invoice_payments aip,
37 ap_checks ac,
38 ap_invoices ai,
39 ap_lookup_codes alc1,
40 ap_lookup_codes alc2
41 WHERE aip.invoice_id = l_invoice_id
42 AND aip.payment_num = l_payment_num
43 AND aip.check_id = ac.check_id
44 AND aip.other_invoice_id = ai.invoice_id (+)
45 AND alc1.lookup_type = 'NLS TRANSLATION'
46 AND alc1.lookup_code = 'PREPAY'
47 AND alc2.lookup_type = 'PAYMENT METHOD'
48 AND alc2.lookup_code = ac.payment_method_lookup_code;
49
50 -----------------------------------------------------------------------
51 -- Function concat_document_num_type concatenates the document number
52 -- to the document type
53 --
54 FUNCTION concat_document_num_type(l_payment_record IN PAYMENT_RECORD)
55 RETURN VARCHAR2
56 IS
57 l_paid_by VARCHAR2(80);
58 BEGIN
59
60 IF (l_payment_record.type = 'PREPAY') THEN
61 -------------------------------------------------------
62 -- Get prepayment number concatenated to prepayment type
63 --
64 l_paid_by := l_payment_record.invoice_num ||' - '||
65 l_payment_record.type_trans;
66 ELSE
67 -------------------------------------------------------
68 -- Get payment number concatenated to payment method type
69 --
70 l_paid_by := l_payment_record.check_number ||' - '||
71 l_payment_record.method_trans;
72 END IF;
73
74 RETURN(l_paid_by);
75
76 END concat_document_num_type;
77
78
79 -----------------------------------------------------------------------
80 -- Function get_paid_by_list returns a list of document numbers
81 -- concatenated to the document type used to pay this payment schedule
82 -- or NULL if unpaid.
83 --
84 FUNCTION get_paid_by_list(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
85 RETURN VARCHAR2
86 IS
87 l_paid_by VARCHAR2(80);
88 l_paid_by_list VARCHAR2(2000) := NULL;
89 l_payment_record PAYMENT_RECORD;
90
91 BEGIN
92
93 OPEN payment_cursor(l_invoice_id, l_payment_num);
94
95 LOOP
96 FETCH payment_cursor INTO l_payment_record;
97 EXIT WHEN payment_cursor%NOTFOUND;
98
99 l_paid_by := ICX_AP_INVOICE_PAYMENTS_PKG.CONCAT_DOCUMENT_NUM_TYPE(
100 l_payment_record);
101
102 IF (l_paid_by_list IS NOT NULL) THEN
103 l_paid_by_list := l_paid_by_list || ', ';
104 END IF;
105
106 l_paid_by_list := l_paid_by_list || l_paid_by;
107
108 END LOOP;
109
110 CLOSE payment_cursor;
111
112 RETURN(l_paid_by_list);
113
114 END get_paid_by_list;
115
116 END ICX_AP_INVOICE_PAYMENTS_PKG;