[Home] [Help]
PACKAGE BODY: APPS.AP_CARD_INVOICE_PKG
Source
1 PACKAGE BODY AP_CARD_INVOICE_PKG AS
2 /* $Header: apwcintb.pls 120.5 2006/10/25 10:05:16 pranpaul noship $ */
3
4 ---------------------------------------------------------------------------
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)),
50 efd.dist_code_combination_id,
51 efd.tax_code,
52 efd.amount_includes_tax_flag,
53 decode(P_ROLLUP_FLAG,
54 'N',efd.feed_distribution_id),
55 decode(P_ROLLUP_FLAG,
56 'N',efl.transaction_date,
57 nvl(P_START_DATE,nvl(P_END_DATE,sysdate))),
58 decode(P_ROLLUP_FLAG,
59 'N',efd.description),
60 efd.org_id -- Bug 5592139
61 from ap_expense_feed_lines efl,
62 ap_expense_feed_dists efd,
63 ap_card_programs cp
64 where efl.feed_line_id = efd.feed_line_id
65 and efl.card_program_id = P_CARD_PROGRAM_ID
66 and efl.card_program_id = cp.card_program_id
67 and nvl(efd.invoiced_flag,'N') = 'N'
68 and ((nvl(efd.status_lookup_code,'VALIDATED') = 'APPROVED')
69 OR
70 (nvl(cp.exclude_unverified_flag,'N') = 'N' AND
71 nvl(efd.status_lookup_code,'VALIDATED') = 'VALIDATED')
72 OR
73 (nvl(cp.exclude_rejected_flag,'N') = 'N' AND
74 nvl(efd.status_lookup_code,'VALIDATED') in ('REJECTED','VERIFIED'))
75 OR
76 (nvl(cp.exclude_personal_flag,'N') = 'N' AND
77 nvl(efd.status_lookup_code,'VALIDATED') = 'PERSONAL')
78 OR
79 (nvl(cp.exclude_disputed_flag,'N') = 'N' AND
80 nvl(efd.status_lookup_code,'VALIDATED') = 'DISPUTED')
81 OR
82 (nvl(cp.exclude_held_flag,'N') = 'N' AND
83 nvl(efd.status_lookup_code,'VALIDATED') = 'HOLD'))
84 and (efl.posted_date is null OR
85 (efl.posted_date between
86 nvl(P_START_DATE,efl.posted_date-1) and
87 nvl(P_END_DATE,efl.posted_date+1)))
88 group by efd.dist_code_combination_id,
89 efd.tax_code,
90 efd.amount_includes_tax_flag,
91 decode(P_ROLLUP_FLAG,
92 'N',efd.feed_distribution_id),
93 decode(P_ROLLUP_FLAG,
94 'N',efl.transaction_date,
95 nvl(P_START_DATE,nvl(P_END_DATE,sysdate))),
96 decode(P_ROLLUP_FLAG,
97 'N',efd.description),
98 efd.org_id; -- Bug 5620010
99
100 l_amount number;
101 l_ccid number;
102 l_tax_code AP_EXPENSE_FEED_DISTS.tax_code%TYPE;
103 l_amount_includes_tax_flag
104 AP_EXPENSE_FEED_DISTS.amount_includes_tax_flag%TYPE;
105 l_transaction_date date;
106 l_description AP_EXPENSE_FEED_DISTS.description%TYPE := '';
107 l_invoice_currency_code
108 AP_CARD_PROGRAMS.card_program_currency_code%TYPE := '';
109 l_count number := 0;
110 l_sum number := 0;
111 l_invoice_id number;
112 l_invoice_line_id number;
113 l_feed_distribution_id number;
114 l_vendor_id number;
115 l_vendor_site_id number;
116 l_org_id number; -- Bug 5592139
117 l_debug_info VARCHAR2(100);
118
119 BEGIN
120
121 -----------------------------------------------------------------------
122 l_debug_info := 'Open lines cursor';
123 -----------------------------------------------------------------------
124 open lines_cursor;
125
126 loop
127
128 -----------------------------------------------------------------------
129 l_debug_info := 'Fetch lines cursor';
130 -----------------------------------------------------------------------
131 fetch lines_cursor into
132 l_amount,
133 l_ccid,
134 l_tax_code,
135 l_amount_includes_tax_flag,
136 l_feed_distribution_id,
137 l_transaction_date,
138 l_description,
139 l_org_id;
140
141 exit when lines_cursor%NOTFOUND;
142
143 l_count := l_count + 1;
144 l_sum := l_sum + l_amount;
145
146 --
147 -- Get sequence-generated invoice_id if this is the first line
148 --
149 if (l_count = 1) then
150 -----------------------------------------------------------------------
151 l_debug_info := 'Getting next sequence from ap_invoices_interface_s';
152 -----------------------------------------------------------------------
153 select ap_invoices_interface_s.nextval
154 into l_invoice_id
155 from dual;
156
157 P_INVOICE_ID := l_invoice_id;
158
159 end if;
160
161 --
162 -- Get sequence-generated invoice_line_id
163 --
164 -----------------------------------------------------------------------
165 l_debug_info := 'Getting next sequence from ap_invoice_lines_interface_s';
166 -----------------------------------------------------------------------
167 select ap_invoice_lines_interface_s.nextval
168 into l_invoice_line_id
169 from dual;
170
171 -----------------------------------------------------------------------
172 l_debug_info := 'inserting into ap_invoice_lines_interface';
173 -----------------------------------------------------------------------
174 insert into ap_invoice_lines_interface
175 (INVOICE_ID,
176 INVOICE_LINE_ID,
177 LINE_NUMBER,
178 LINE_TYPE_LOOKUP_CODE,
179 AMOUNT,
180 ACCOUNTING_DATE,
181 DESCRIPTION,
182 TAX_CODE,
183 AMOUNT_INCLUDES_TAX_FLAG,
184 DIST_CODE_COMBINATION_ID,
185 ORG_ID) VALUES -- Bug 5592139
186 (l_invoice_id,
187 l_invoice_line_id,
188 l_count,
189 'ITEM',
190 l_amount,
191 l_transaction_date,
192 l_description,
193 l_tax_code,
194 l_amount_includes_tax_flag,
195 l_ccid,
196 l_org_id); -- Bug 5592139
197
198 if (l_feed_distribution_id is not null) then
199 --
200 -- Insertion is detail-level, therefore one-to-one correspondence
201 -- between AP_EXPENSE_FEED_DISTS and AP_INVOICE_LINES_INTERFACE.
202 --
203 -- Update the record AP_EXPENSE_FEED_DISTS with the ID of the
204 -- newly created line in AP_INVOICE_LINES_INTERFACE.
205 --
206 -----------------------------------------------------------------------
207 l_debug_info := 'Updating AP_EXPENSE_FEED_DISTS with DIST ID';
208 -----------------------------------------------------------------------
209 update AP_EXPENSE_FEED_DISTS
210 set invoiced_flag = 'Y',
211 INVOICE_ID = l_invoice_id,
212 INVOICE_LINE_ID = l_invoice_line_id
213 where feed_distribution_id = l_feed_distribution_id;
214
215 else
216 --
217 -- Insertion is summary-level, therefore many-to-one correspondence
218 -- between AP_EXPENSE_FEED_DISTS and AP_INVOICE_LINES_INTERFACE.
219 --
220 -- Update the records AP_EXPENSE_FEED_DISTS matching the group
221 -- criteria of the lines_cursor with the ID of the newly created
222 -- line in AP_INVOICE_LINES_INTERFACE.
223 --
224 -----------------------------------------------------------------------
225 l_debug_info := 'Updating AP_EXPENSE_FEED_DISTS';
226 -----------------------------------------------------------------------
227 /*Bug 4997769*/
228 /*Replaced the transaction date by posted date*/
229 update AP_EXPENSE_FEED_DISTS EFD
230 set invoiced_flag = 'Y',
231 INVOICE_ID = l_invoice_id,
232 INVOICE_LINE_ID = l_invoice_line_id
233 where nvl(invoiced_flag,'N') = 'N'
234 and dist_code_combination_id = l_ccid
235 and exists
236 (select 'Parent record meets group criteria from lines_cursor'
237 from AP_EXPENSE_FEED_LINES EFL,
238 AP_CARD_PROGRAMS CP
239 where EFL.feed_line_id = EFD.feed_line_id
240 and EFL.card_program_id = P_CARD_PROGRAM_ID
241 and EFL.card_program_id = CP.card_program_id
242 and ((nvl(efd.status_lookup_code,'VALIDATED') = 'APPROVED')
243 OR
244 (nvl(cp.exclude_unverified_flag,'N') = 'N' AND
245 nvl(efd.status_lookup_code,'VALIDATED') = 'VALIDATED')
246 OR
247 (nvl(cp.exclude_rejected_flag,'N') = 'N' AND
248 nvl(efd.status_lookup_code,'VALIDATED') = 'REJECTED')
249 OR
250 (nvl(cp.exclude_personal_flag,'N') = 'N' AND
251 nvl(efd.status_lookup_code,'VALIDATED') = 'PERSONAL')
252 OR
253 (nvl(cp.exclude_disputed_flag,'N') = 'N' AND
254 nvl(efd.status_lookup_code,'VALIDATED') = 'DISPUTED')
255 OR
256 (nvl(cp.exclude_held_flag,'N') = 'N' AND
257 nvl(efd.status_lookup_code,'VALIDATED') = 'HOLD')
258 OR
259 (nvl(cp.exclude_unreconciled_flag,'N') = 'N' AND
260 nvl(efd.status_lookup_code,'VALIDATED') = 'VERIFIED'))
261 and (efl.posted_date is null OR
262 (efl.posted_date between
263 nvl(P_START_DATE,efl.posted_date-1) and
264 nvl(P_END_DATE,efl.posted_date+1))));
265 end if;
266
267 end loop;
268
269 close lines_cursor;
270
271 --
272 -- If any records were inserted into AP_INVOICE_LINES_INTERFACE
273 -- then insert a single header record into AP_INVOICES_INTERFACE.
274 --
275 if (l_count > 0) then
276 --
277 -- Need vendor (payee) information from AP_CARD_PROGRAMS
278 --
279 -----------------------------------------------------------------------
280 l_debug_info := 'Retrieving vendor info from card program';
281 -----------------------------------------------------------------------
282 select vendor_id,
283 vendor_site_id,
284 card_program_currency_code
285 into l_vendor_id,
286 l_vendor_site_id,
287 l_invoice_currency_code
288 from ap_card_programs
289 where card_program_id = P_CARD_PROGRAM_ID;
290
291 -----------------------------------------------------------------------
292 l_debug_info := 'Inserting into AP_INVOICES_INTERFACE';
293 -----------------------------------------------------------------------
294 insert into AP_INVOICES_INTERFACE
295 (INVOICE_ID,
296 INVOICE_NUM,
297 VENDOR_ID,
298 VENDOR_SITE_ID,
299 INVOICE_AMOUNT,
300 INVOICE_CURRENCY_CODE,
301 SOURCE,
302 ORG_ID
303 ) VALUES
304 (l_invoice_id,
305 substrb(to_char(l_invoice_id)||'-'||to_char(sysdate),1,50),
306 l_vendor_id,
307 l_vendor_site_id,
308 l_sum,
309 l_invoice_currency_code,
310 'CREDIT CARD',
311 l_org_id -- Bug 5592139
312 );
313
314 end if;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 IF (SQLCODE <> -20001) THEN
319 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
320 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
321 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'CREATE_INTERFACE_RECORDS');
322 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
323 END IF;
324 APP_EXCEPTION.RAISE_EXCEPTION;
325
326 END;
327
328 END AP_CARD_INVOICE_PKG;