[Home] [Help]
PACKAGE BODY: APPS.AP_CARD_INVOICE_PKG
Source
4 ---------------------------------------------------------------------------
1 PACKAGE BODY AP_CARD_INVOICE_PKG AS
2 /* $Header: apwcintb.pls 120.10.12020000.2 2012/08/10 07:46:03 lyanduru ship $ */
3
5 --
6 -- Procedure CREATE_INVOICE
7 --
8 -- Inserts records into AP_INVOICE_LINES_INTERFACE and
9 -- AP_INVOICES_INTERFACE to create invoice for credit card issuer
10 -- for payment of records in AP_EXPENSE_FEED_DISTS.
11 --
12 -- Records inserted into AP_INVOICE_LINES_INTERFACE from
13 -- AP_EXPENSE_FEED_DISTS where
14 -- o AP_EXPENSE_FEED_LINES.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
15 -- o AP_EXPENSE_FEED_DISTS.TRANSACTION_DATE between
16 -- P_START_DATE and P_END_DATE.
17 -- o AP_EXPENSE_FEED_DISTS.STATUS = 'APPROVED' or status is
18 -- not excepted from payment as defined at the card program
19 --
20 -- These records are summarized (rolled up) by
21 -- AP_EXPENSE_FEED_DISTS.DIST_CODE_COMBINATION_ID, TAX_CODE, and
22 -- AMOUNT_INCLUDES_TAX_FLAG when P_ROLLUP_FLAG = 'Y'
23 --
24 -- Single record inserted into AP_INVOICES_INTERFACE which serves as header
25 -- to those records inserted into AP_INVOICE_LINES_INTERFACE.
26 --
27 -- Records in AP_EXPENSE_FEED_DISTS updated to reflect invoice interface
28 -- insertions.
29 -- o AP_EXPENSE_FEED_DISTS.INVOICED_FLAG = 'Y'
30 -- o AP_EXPENSE_FEED_DISTS.INVOICE_ID = <sequence-generated ID>
31 -- o AP_EXPENSE_FEED_DISTS.INVOICE_LINE_ID = <sequence-generated ID>
32 --
33 ---------------------------------------------------------------------------
34 PROCEDURE CREATE_INVOICE(
35 P_CARD_PROGRAM_ID IN NUMBER,
36 P_INVOICE_ID IN OUT NOCOPY NUMBER,
37 P_START_DATE IN DATE DEFAULT NULL,
38 P_END_DATE IN DATE DEFAULT NULL,
39 P_ROLLUP_FLAG IN VARCHAR2 DEFAULT 'Y') IS
40
41 --
42 -- Define cursor for lines
43 --
44 -- If P_ROLLUP_FLAG = Y then rollup transactions by CCID
45 --
46 /*Bug 4997769*/
47 /*Replaced the transaction date by posted date*/
48 cursor lines_cursor is
49 select sum(nvl(efd.amount,0)) amount,
50 efd.dist_code_combination_id ccid,
51 efd.tax_code ,
52 nvl(efd.amount_includes_tax_flag,'N') amount_includes_tax_flag, --9727870
53 decode(P_ROLLUP_FLAG,
54 'N',efd.feed_distribution_id) feed_distribution_id,
55 decode(P_ROLLUP_FLAG,
56 'N',efl.transaction_date,
57 nvl(P_START_DATE,nvl(P_END_DATE,sysdate))) transaction_date,
58 decode(P_ROLLUP_FLAG,
59 'N',efd.description) description,
60 efd.org_id, -- Bug 5592139
61 efl.merchant_name,
62 efl.merchant_number, --bug8299023
63 efd.attribute_category, /* DFFs added for Bug 14457995 */
64 efd.attribute1,
65 efd.attribute2,
66 efd.attribute3,
67 efd.attribute4,
68 efd.attribute5,
69 efd.attribute6,
70 efd.attribute7,
71 efd.attribute8,
72 efd.attribute9,
73 efd.attribute10,
74 efd.attribute11,
75 efd.attribute12,
76 efd.attribute13,
77 efd.attribute14,
78 efd.attribute15
79 from ap_expense_feed_lines efl,
80 ap_expense_feed_dists efd,
81 ap_card_programs cp
82 where efl.feed_line_id = efd.feed_line_id
83 and efl.card_program_id = P_CARD_PROGRAM_ID
84 and efl.card_program_id = cp.card_program_id
85 and nvl(efd.invoiced_flag,'N') = 'N'
86 and ((nvl(efd.status_lookup_code,'VALIDATED') = 'APPROVED')
87 OR
88 (nvl(cp.exclude_unverified_flag,'N') = 'N' AND
89 nvl(efd.status_lookup_code,'VALIDATED') = 'VALIDATED')
90 OR
91 (nvl(cp.exclude_rejected_flag,'N') = 'N' AND
92 nvl(efd.status_lookup_code,'VALIDATED') in ('REJECTED','VERIFIED'))
93 OR
94 (nvl(cp.exclude_personal_flag,'N') = 'N' AND
95 nvl(efd.status_lookup_code,'VALIDATED') = 'PERSONAL')
96 OR
97 (nvl(cp.exclude_disputed_flag,'N') = 'N' AND
101 nvl(efd.status_lookup_code,'VALIDATED') = 'HOLD'))
98 nvl(efd.status_lookup_code,'VALIDATED') = 'DISPUTED')
99 OR
100 (nvl(cp.exclude_held_flag,'N') = 'N' AND
102 and (efl.posted_date is null OR
103 (efl.posted_date between
104 nvl(P_START_DATE,efl.posted_date-1) and
105 nvl(P_END_DATE,efl.posted_date+1)))
106 group by efd.dist_code_combination_id,
107 efd.tax_code,
108 nvl(efd.amount_includes_tax_flag,'N'), --9727870
109 decode(P_ROLLUP_FLAG,
110 'N',efd.feed_distribution_id),
111 decode(P_ROLLUP_FLAG,
112 'N',efl.transaction_date,
113 nvl(P_START_DATE,nvl(P_END_DATE,sysdate))),
114 decode(P_ROLLUP_FLAG,
115 'N',efd.description),
116 efd.org_id, -- Bug 5620010
117 efl.merchant_name,
118 efl.merchant_number, --bug8299023
119 efd.attribute_category, /*Bug 14457995*/
120 efd.attribute1,
121 efd.attribute2,
122 efd.attribute3,
123 efd.attribute4,
124 efd.attribute5,
125 efd.attribute6,
126 efd.attribute7,
127 efd.attribute8,
128 efd.attribute9,
129 efd.attribute10,
130 efd.attribute11,
131 efd.attribute12,
132 efd.attribute13,
133 efd.attribute14,
134 efd.attribute15 ; --Bug 14457995
135
136 --Commenting unnecessary variables 14457995.
137 /* l_amount number;
138 l_ccid number;
139 l_tax_code AP_EXPENSE_FEED_DISTS.tax_code%TYPE;
140 l_amount_includes_tax_flag
141 AP_EXPENSE_FEED_DISTS.amount_includes_tax_flag%TYPE;
142
143 l_description AP_EXPENSE_FEED_DISTS.description%TYPE := ''; */
144 l_invoice_currency_code
145 AP_CARD_PROGRAMS.card_program_currency_code%TYPE := '';
146 l_transaction_date date;
147 l_count number := 0;
148 l_sum number := 0;
149 l_invoice_id number;
150 l_invoice_line_id number;
151 l_feed_distribution_id number;
152 l_vendor_id number;
153 l_vendor_site_id number;
154 l_org_id number; -- Bug 5592139
155 l_debug_info VARCHAR2(100);
156 --Commenting unnecessary variables 14457995.
157 /*l_merchant_name AP_EXPENSE_FEED_LINES.merchant_name%TYPE;
158 l_merchant_number AP_EXPENSE_FEED_LINES.merchant_number%TYPE; --bug8299023 */
159 l_period_name ap_invoice_distributions.period_name%TYPE; --Bug 10218303
160 l_gl_date ap_invoices.gl_date%TYPE; --Bug 10218303
161 rec_lines_int lines_cursor%ROWTYPE; /*Bug 14457995*/
162
163
164
165
166
167 BEGIN
168
169 -----------------------------------------------------------------------
170 l_debug_info := 'Open lines cursor';
171 -----------------------------------------------------------------------
172 open lines_cursor;
173
174 loop
175
176 -----------------------------------------------------------------------
177 l_debug_info := 'Fetch lines cursor';
178 -----------------------------------------------------------------------
179 /*fetch lines_cursor into
180 l_amount,
181 l_ccid,
182 l_tax_code,
183 l_amount_includes_tax_flag,
184 l_feed_distribution_id,
185 l_transaction_date,
186 l_description,
187 l_org_id,
188 l_merchant_name,
189 l_merchant_number, --bug8299023
190
191 exit when lines_cursor%NOTFOUND; */
192 --Commented for bug 14457995 as a new row type variable is declared for
193 --lines cursor
194
195 fetch lines_cursor into rec_lines_int;
196 exit when lines_cursor%NOTFOUND;
197
198 l_count := l_count + 1;
199 l_sum := l_sum + rec_lines_int.amount;
200 l_transaction_date := rec_lines_int.transaction_date;
201
202 --
203 -- Get sequence-generated invoice_id if this is the first line
204 --
205 if (l_count = 1) then
206 -----------------------------------------------------------------------
207 l_debug_info := 'Getting next sequence from ap_invoices_interface_s';
208 -----------------------------------------------------------------------
209 select ap_invoices_interface_s.nextval
210 into l_invoice_id
211 from dual;
212
213 P_INVOICE_ID := l_invoice_id;
214
215 end if;
216
217 --
218 -- Get sequence-generated invoice_line_id
219 --
220 -----------------------------------------------------------------------
221 l_debug_info := 'Getting next sequence from ap_invoice_lines_interface_s';
222 -----------------------------------------------------------------------
223 select ap_invoice_lines_interface_s.nextval
224 into l_invoice_line_id
225 from dual;
226
227 --Bug 10218303: CTETALA Added code to move transaction date to next open period
228 -----------------------------------------------------------------------
232 if (l_transaction_date is null) then
229 l_debug_info := 'adjust transaction_date if in a closed period';
230 -----------------------------------------------------------------------
231 -- if transaction date is null
233 -- use sysdate as transaction date
234 l_transaction_date := sysdate;
235 else
236 -- if transaction date is in a closed period then adjust
237 l_period_name := ap_utilities_pkg.get_current_gl_date(l_transaction_date);
238 if (l_period_name is null) then
239 -- get next open/future period
240 ap_utilities_pkg.get_open_gl_date(l_transaction_date, l_period_name, l_gl_date);
241 l_transaction_date := l_gl_date;
242 end if;
243 end if;
244
245
246 -----------------------------------------------------------------------
247 l_debug_info := 'inserting into ap_invoice_lines_interface';
248 -----------------------------------------------------------------------
249 insert into ap_invoice_lines_interface
250 (INVOICE_ID,
251 INVOICE_LINE_ID,
252 LINE_NUMBER,
253 LINE_TYPE_LOOKUP_CODE,
254 AMOUNT,
255 ACCOUNTING_DATE,
256 DESCRIPTION,
257 TAX_CODE,
258 AMOUNT_INCLUDES_TAX_FLAG,
259 DIST_CODE_COMBINATION_ID,
260 ORG_ID, -- Bug 5592139
261 MERCHANT_NAME,
262 MERCHANT_REFERENCE,--bug8299023
263 ATTRIBUTE_CATEGORY,
264 ATTRIBUTE1,
265 ATTRIBUTE2,
266 ATTRIBUTE3,
267 ATTRIBUTE4,
268 ATTRIBUTE5,
269 ATTRIBUTE6,
270 ATTRIBUTE7,
271 ATTRIBUTE8,
272 ATTRIBUTE9,
273 ATTRIBUTE10,
274 ATTRIBUTE11,
275 ATTRIBUTE12,
276 ATTRIBUTE13,
277 ATTRIBUTE14,
278 ATTRIBUTE15) VALUES --Bug 14457995
279 (l_invoice_id,
280 l_invoice_line_id,
281 l_count,
282 'ITEM',
283 rec_lines_int.amount,
284 l_transaction_date,
285 rec_lines_int.description,
286 rec_lines_int.tax_code,
287 rec_lines_int.amount_includes_tax_flag,
288 rec_lines_int.ccid,
289 rec_lines_int.org_id, -- Bug 5592139
290 rec_lines_int.merchant_name,
291 rec_lines_int.merchant_number, --bug8299023
292 rec_lines_int.attribute_category,
293 rec_lines_int.attribute1,
294 rec_lines_int.attribute2,
295 rec_lines_int.attribute3,
296 rec_lines_int.attribute4,
297 rec_lines_int.attribute5,
298 rec_lines_int.attribute6,
299 rec_lines_int.attribute7,
300 rec_lines_int.attribute8,
301 rec_lines_int.attribute9,
302 rec_lines_int.attribute10,
303 rec_lines_int.attribute11,
304 rec_lines_int.attribute12,
305 rec_lines_int.attribute13,
306 rec_lines_int.attribute14,
307 rec_lines_int.attribute15
308 ); --Bug 14457995
309
310 if (rec_lines_int.feed_distribution_id is not null) then
311 --
312 -- Insertion is detail-level, therefore one-to-one correspondence
313 -- between AP_EXPENSE_FEED_DISTS and AP_INVOICE_LINES_INTERFACE.
314 --
315 -- Update the record AP_EXPENSE_FEED_DISTS with the ID of the
316 -- newly created line in AP_INVOICE_LINES_INTERFACE.
317 --
318 -----------------------------------------------------------------------
319 l_debug_info := 'Updating AP_EXPENSE_FEED_DISTS with DIST ID';
320 -----------------------------------------------------------------------
321 update AP_EXPENSE_FEED_DISTS
322 set invoiced_flag = 'Y',
323 INVOICE_ID = l_invoice_id,
324 INVOICE_LINE_ID = l_invoice_line_id
325 where feed_distribution_id = rec_lines_int.feed_distribution_id;
326
327 else
328 --
329 -- Insertion is summary-level, therefore many-to-one correspondence
330 -- between AP_EXPENSE_FEED_DISTS and AP_INVOICE_LINES_INTERFACE.
331 --
332 -- Update the records AP_EXPENSE_FEED_DISTS matching the group
333 -- criteria of the lines_cursor with the ID of the newly created
334 -- line in AP_INVOICE_LINES_INTERFACE.
335 --
336 -----------------------------------------------------------------------
337 l_debug_info := 'Updating AP_EXPENSE_FEED_DISTS';
338 -----------------------------------------------------------------------
339 /*Bug 4997769*/
340 /*Replaced the transaction date by posted date*/
341 update AP_EXPENSE_FEED_DISTS EFD
342 set invoiced_flag = 'Y',
343 INVOICE_ID = l_invoice_id,
344 INVOICE_LINE_ID = l_invoice_line_id
345 where nvl(invoiced_flag,'N') = 'N'
346 and dist_code_combination_id = rec_lines_int.ccid
347 and nvl(efd.tax_code,'N')= nvl(rec_lines_int.tax_code,'N') --9727870
348 and nvl(efd.amount_includes_tax_flag,'N')= rec_lines_int.amount_includes_tax_flag --9727870
349 and exists
350 (select 'Parent record meets group criteria from lines_cursor'
351 from AP_EXPENSE_FEED_LINES EFL,
352 AP_CARD_PROGRAMS CP
353 where EFL.feed_line_id = EFD.feed_line_id
354 and EFL.card_program_id = P_CARD_PROGRAM_ID
355 and EFL.card_program_id = CP.card_program_id
356 and EFL.merchant_name = rec_lines_int.merchant_name --9727870
357 and EFL.merchant_number = rec_lines_int.merchant_number --9727870
358 and ((nvl(efd.status_lookup_code,'VALIDATED') = 'APPROVED')
359 OR
360 (nvl(cp.exclude_unverified_flag,'N') = 'N' AND
364 nvl(efd.status_lookup_code,'VALIDATED') = 'REJECTED')
361 nvl(efd.status_lookup_code,'VALIDATED') = 'VALIDATED')
362 OR
363 (nvl(cp.exclude_rejected_flag,'N') = 'N' AND
365 OR
366 (nvl(cp.exclude_personal_flag,'N') = 'N' AND
367 nvl(efd.status_lookup_code,'VALIDATED') = 'PERSONAL')
368 OR
369 (nvl(cp.exclude_disputed_flag,'N') = 'N' AND
370 nvl(efd.status_lookup_code,'VALIDATED') = 'DISPUTED')
371 OR
372 (nvl(cp.exclude_held_flag,'N') = 'N' AND
373 nvl(efd.status_lookup_code,'VALIDATED') = 'HOLD')
374 OR
375 (nvl(cp.exclude_unreconciled_flag,'N') = 'N' AND
376 nvl(efd.status_lookup_code,'VALIDATED') = 'VERIFIED'))
377 and (efl.posted_date is null OR
378 (efl.posted_date between
379 nvl(P_START_DATE,efl.posted_date-1) and
380 nvl(P_END_DATE,efl.posted_date+1))));
381 end if;
382
383 end loop;
384
385 close lines_cursor;
386
387 --
388 -- If any records were inserted into AP_INVOICE_LINES_INTERFACE
389 -- then insert a single header record into AP_INVOICES_INTERFACE.
390 --
391 if (l_count > 0) then
392 --
393 -- Need vendor (payee) information from AP_CARD_PROGRAMS
394 --
395 -----------------------------------------------------------------------
396 l_debug_info := 'Retrieving vendor info from card program';
397 -----------------------------------------------------------------------
398 select vendor_id,
399 vendor_site_id,
400 card_program_currency_code
401 into l_vendor_id,
402 l_vendor_site_id,
403 l_invoice_currency_code
404 from ap_card_programs
405 where card_program_id = P_CARD_PROGRAM_ID;
406
407 -----------------------------------------------------------------------
408 l_debug_info := 'Inserting into AP_INVOICES_INTERFACE';
409 -----------------------------------------------------------------------
410 insert into AP_INVOICES_INTERFACE
411 (INVOICE_ID,
412 INVOICE_NUM,
413 VENDOR_ID,
414 VENDOR_SITE_ID,
415 INVOICE_AMOUNT,
416 INVOICE_CURRENCY_CODE,
417 SOURCE,
418 ORG_ID
419 ) VALUES
420 (l_invoice_id,
421 substrb(to_char(l_invoice_id)||'-'||to_char(sysdate),1,50),
422 l_vendor_id,
423 l_vendor_site_id,
424 l_sum,
425 l_invoice_currency_code,
426 'PCARD',
427 l_org_id -- Bug 5592139
428 );
429
430 end if;
431
432 EXCEPTION
433 WHEN OTHERS THEN
434 IF (SQLCODE <> -20001) THEN
435 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
436 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
437 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'CREATE_INTERFACE_RECORDS');
438 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
439 END IF;
440 APP_EXCEPTION.RAISE_EXCEPTION;
441
442 END;
443
444 END AP_CARD_INVOICE_PKG;