1 PACKAGE BODY AP_INVOICE_PAYMENTS_PKG AS
2 /* $Header: apiinpab.pls 120.3 2005/09/20 20:15:55 rlandows noship $ */
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_code%TYPE, --4552701
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 -- MOAC. Use table instead of SO views.
25 CURSOR payment_cursor (l_invoice_id NUMBER, l_payment_num NUMBER)
26 RETURN payment_record IS
27 SELECT ac.check_number,
28 ac.check_id,
29 ac.check_date,
30 ai.invoice_num,
31 ai.invoice_id,
32 aip.accounting_date,
33 aip.invoice_payment_type,
34 iby.payment_method_name, --4552701
35 ac.payment_method_code, --4552701
36 iby.payment_method_name
37 FROM ap_invoice_payments_all aip,
38 ap_checks_all ac,
39 ap_invoices_all ai,
40 iby_payment_methods_vl iby
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 iby.payment_method_code = ac.payment_method_code;
46
47
48 -----------------------------------------------------------------------
49 -- Function concat_document_num_type concatenates the document number
50 -- to the document type
51 --
52 FUNCTION concat_document_num_type(l_payment_record IN PAYMENT_RECORD)
53 RETURN VARCHAR2
54 IS
55 l_paid_by VARCHAR2(100);
56 BEGIN
57
58 -------------------------------------------------------
59 -- Get payment number concatenated to payment method type
60 --
61 l_paid_by := l_payment_record.check_number ||' - '||
62 l_payment_record.method_trans;
63
64 RETURN(l_paid_by);
65
66 END concat_document_num_type;
67
68
69 -----------------------------------------------------------------------
70 -- Function get_paid_by returns the document number concatenated to the
71 -- document type if payment schedule is paid by a single invoice payment
72 -- and the translated phrase 'Multiple Payments' if paid by multiple
73 -- payments or if unpaid.
74 --
75 FUNCTION get_paid_by(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
76 RETURN VARCHAR2
77 IS
78 l_paid_by VARCHAR2(100);
79 l_payment_record PAYMENT_RECORD;
80
81 BEGIN
82
83 OPEN payment_cursor(l_invoice_id, l_payment_num);
84
85 LOOP
86 FETCH payment_cursor INTO l_payment_record;
87 EXIT WHEN payment_cursor%NOTFOUND;
88
89 IF (payment_cursor%ROWCOUNT = 1) THEN
90
91 l_paid_by := AP_INVOICE_PAYMENTS_PKG.CONCAT_DOCUMENT_NUM_TYPE(
92 l_payment_record);
93
94 ELSIF (payment_cursor%ROWCOUNT > 1) THEN
95 -----------------------------------------------------------
96 -- Get the translated phrase 'Multiple Payments'
97 --
98 SELECT displayed_field
99 INTO l_paid_by
100 FROM ap_lookup_codes
101 WHERE lookup_type = 'NLS TRANSLATION'
102 AND lookup_code = 'MULTIPLE PAYMENTS';
103
104 EXIT;
105
106 END IF;
107
108 END LOOP;
109
110 CLOSE payment_cursor;
111
112 RETURN(l_paid_by);
113
114 END get_paid_by;
115
116
117 -----------------------------------------------------------------------
118 -- Function get_paid_by_list returns a list of document numbers
119 -- concatenated to the document type used to pay this payment schedule
120 -- or NULL if unpaid.
121 --
122 FUNCTION get_paid_by_list(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
123 RETURN VARCHAR2
124 IS
125 l_paid_by VARCHAR2(100);
126 l_paid_by_list VARCHAR2(2000) := NULL;
127 l_payment_record PAYMENT_RECORD;
128
129 BEGIN
130
131 OPEN payment_cursor(l_invoice_id, l_payment_num);
132
133 LOOP
134 FETCH payment_cursor INTO l_payment_record;
135 EXIT WHEN payment_cursor%NOTFOUND;
136
137 l_paid_by := AP_INVOICE_PAYMENTS_PKG.CONCAT_DOCUMENT_NUM_TYPE(
138 l_payment_record);
139
140 IF (l_paid_by_list IS NOT NULL) THEN
141 l_paid_by_list := l_paid_by_list || ', ';
142 END IF;
143
144 l_paid_by_list := l_paid_by_list || l_paid_by;
145
146 END LOOP;
147
148 CLOSE payment_cursor;
149
150 RETURN(l_paid_by_list);
151
152 END get_paid_by_list;
153
154
155 -----------------------------------------------------------------------
156 -- Function get_paid_date returns the check date if the payment schedule
157 -- is paid by a single invoice payment and NULL if paid by multiple
158 -- payments or if unpaid.
159 --
160 FUNCTION get_paid_date(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
161 RETURN DATE
162 IS
163 l_paid_date DATE := NULL;
164 l_payment_record PAYMENT_RECORD;
165 BEGIN
166
167 OPEN payment_cursor(l_invoice_id, l_payment_num);
168
169 LOOP
170 FETCH payment_cursor INTO l_payment_record;
171 EXIT WHEN payment_cursor%NOTFOUND;
172
173 IF (payment_cursor%ROWCOUNT = 1) THEN
174
175 -------------------------------------------------------
176 -- Get check date
177 --
178 l_paid_date := l_payment_record.check_date;
179
180 ELSIF (payment_cursor%ROWCOUNT > 1) THEN
181
182 l_paid_date := NULL;
183
184 EXIT;
185
186 END IF;
187
188 END LOOP;
189
190 CLOSE payment_cursor;
191
192 RETURN(l_paid_date);
193
194 END get_paid_date;
195
196
197 -----------------------------------------------------------------------
198 -- Function get_payment_id returns the check id if payment schedule is
199 -- paid by a single invoice payment and NULL if paid by multiple
200 -- payments or if unpaid.
201 --
202 FUNCTION get_payment_id(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
203 RETURN NUMBER
204 IS
205 l_payment_id NUMBER := NULL;
206 l_payment_record PAYMENT_RECORD;
207
208 BEGIN
209
210 OPEN payment_cursor(l_invoice_id, l_payment_num);
211
212 LOOP
213 FETCH payment_cursor INTO l_payment_record;
214 EXIT WHEN payment_cursor%NOTFOUND;
215
216 IF (payment_cursor%ROWCOUNT = 1) THEN
217
218 -------------------------------------------------------
219 -- Get check id
220 --
221 l_payment_id := l_payment_record.check_id;
222
223 ELSIF (payment_cursor%ROWCOUNT > 1) THEN
224
225 l_payment_id := NULL;
226
227 EXIT;
228
229 END IF;
230
231 END LOOP;
232
233 CLOSE payment_cursor;
234
235 RETURN(l_payment_id);
236
237 END get_payment_id;
238
239
240 -----------------------------------------------------------------------
241 -- Function get_payment_type returns the check payment method if payment
242 -- schedule is paid by a single invoice payment and NULL if paid by
243 -- multiple payments or if unpaid.
244 --
245 FUNCTION get_payment_type(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
246 RETURN VARCHAR2
247 IS
248 l_payment_type VARCHAR2(25);
249 l_payment_record PAYMENT_RECORD;
250
251 BEGIN
252
253 OPEN payment_cursor(l_invoice_id, l_payment_num);
254
255 LOOP
256 FETCH payment_cursor INTO l_payment_record;
257 EXIT WHEN payment_cursor%NOTFOUND;
258
259 IF (payment_cursor%ROWCOUNT = 1) THEN
260
261 -- Get check payment method
262 --
263 l_payment_type := l_payment_record.method;
264
265 ELSIF (payment_cursor%ROWCOUNT > 1) THEN
266
267 l_payment_type := NULL;
268
269 EXIT;
270
271 END IF;
272
273 END LOOP;
274
275 CLOSE payment_cursor;
276
277 RETURN(l_payment_type);
278
279 END get_payment_type;
280
281
282 -----------------------------------------------------------------------
283 -- Function get_max_gl_date returns the latest accounting date for
284 -- the invoice payments belonging to the check.
285 --
286 FUNCTION get_max_gl_date(l_check_id IN NUMBER)
287 RETURN DATE
288 IS
289 gl_date DATE;
290 BEGIN
291
292 SELECT NVL(MAX(accounting_date), SYSDATE-9000)
293 INTO gl_date
294 FROM ap_invoice_payments_all
295 WHERE check_id = l_check_id;
296
297 RETURN gl_date;
298
299 END get_max_gl_date;
300
301 END AP_INVOICE_PAYMENTS_PKG;
[Home] [Help]