[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_EXPORT_ER
Source
1 PACKAGE BODY AP_WEB_EXPORT_ER AS
2 /* $Header: apwexptb.pls 120.74.12020000.5 2013/03/12 10:50:01 dsadipir ship $ */
3
4 PROCEDURE WriteOutput(p_msg VARCHAR2) IS
5 BEGIN
6 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_msg);
7 END WriteOutput;
8
9 PROCEDURE PrintExportOutput IS
10
11 CURSOR reject_reports(l_req_id IN NUMBER) IS
12 SELECT * FROM (
13 SELECT per.full_name,
14 per.employee_num,
15 decode(aeh.source, 'Both Pay', apsbp.vendor_name, aps.vendor_name) vendor_name,
16 decode(aeh.source, 'Both Pay', apsbp.segment1, aps.segment1) segment1,
17 aeh.invoice_num,
18 aeh.week_end_date,
19 aeh.payment_currency_code,
20 aeh.total,
21 alc.description,
22 aeh.org_id OU_ID,
23 hou.name,
24 aeh.default_currency_code
25 FROM
26 ap_expense_report_headers_all aeh,
27 ap_suppliers aps,
28 ap_suppliers apsbp,
29 per_employees_x per,
30 ap_lookup_codes alc,
31 hr_organization_units hou
32 where aeh.request_id = l_req_id
33 and aeh.reject_code is not null
34 and hou.organization_id(+) = aeh.org_id
35 and per.employee_id = aeh.employee_id
36 and aps.employee_id(+) = aeh.employee_id
37 and apsbp.vendor_id(+) = aeh.vendor_id
38 and alc.lookup_type in ('INVOICE IMPORT REJECTION','REJECT CODE')
39 and alc.lookup_code = aeh.reject_code
40 UNION ALL
41
42 SELECT per.full_name,
43 per.npw_number,
44 aps.vendor_name,
45 aps.segment1,
46 aeh.invoice_num,
47 aeh.week_end_date,
48 aeh.payment_currency_code,
49 aeh.total,
50 alc.description,
51 aeh.org_id OU_ID,
52 hou.name,
53 aeh.default_currency_code
54 FROM
55 ap_expense_report_headers_all aeh,
56 ap_suppliers aps,
57 per_cont_workers_x per,
58 ap_lookup_codes alc,
59 hr_organization_units hou
60 where aeh.request_id = l_req_id
61 and aeh.reject_code is not null
62 and hou.organization_id(+) = aeh.org_id
63 and per.person_id = aeh.employee_id
64 and aps.vendor_id(+) = aeh.vendor_id
65 and alc.lookup_type in ('INVOICE IMPORT REJECTION','REJECT CODE')
66 and alc.lookup_code = aeh.reject_code)
67
68 ORDER BY OU_ID;
69
70 CURSOR c_prepayments(l_req_id IN NUMBER) IS
71 SELECT I.invoice_num,
72 P.invoice_num,
73 V.vendor_name,
74 V.segment1,
75 I.invoice_currency_code,
76 I.invoice_amount,
77 ap_invoices_utility_pkg.get_prepaid_amount(I.invoice_id),
78 I.invoice_amount - ap_invoices_utility_pkg.get_prepaid_amount(I.invoice_id),
79 ap_invoices_utility_pkg.get_prepay_amount_remaining(P.invoice_id),
80 ap_invoices_utility_pkg.get_amt_applied_per_prepay(I.invoice_id,P.invoice_id),
81 aeh.org_id OU_ID,
82 hou.name,
83 aeh.default_currency_code
84 FROM ap_invoices_all I, ap_invoices_all P, ap_suppliers V,
85 ap_expense_report_headers_all aeh, hr_organization_units hou
86 WHERE aeh.request_id = l_req_id
87 AND aeh.vouchno is not null
88 AND hou.organization_id(+) = aeh.org_id
89 AND I.invoice_id = aeh.vouchno
90 AND P.invoice_id IN ( SELECT apd.invoice_id
91 FROM ap_invoice_distributions_all AID, ap_invoice_distributions_all APD
92 WHERE AID.invoice_id = I.invoice_id
93 AND AID.prepay_distribution_id = APD.invoice_distribution_id
94 AND AID.line_type_lookup_code = 'PREPAY')
95 AND V.vendor_id = I.vendor_id
96 AND V.vendor_id = P.vendor_id
97 ORDER BY OU_ID;
98
99
100 CURSOR export_reports(l_req_id IN NUMBER) IS
101 SELECT * FROM (
102 SELECT per.full_name,
103 per.employee_num,
104 decode(aeh.source, 'Both Pay', apsbp.vendor_name, aps.vendor_name) vendor_name,
105 decode(aeh.source, 'Both Pay', apsbp.segment1, aps.segment1) segment1,
106 aeh.invoice_num,
107 aeh.week_end_date,
108 aeh.payment_currency_code,
109 ai.invoice_amount,
110 decode(aeh.hold_lookup_code, null, null, alc.description) lookup_desc,
111 aeh.description header_desc,
112 nvl(aeh.vouchno,0),
113 aeh.org_id OU_ID,
114 hou.name,
115 aeh.default_currency_code
116 FROM
117 ap_expense_report_headers_all aeh,
118 ap_invoices_all ai,
119 ap_suppliers aps,
120 ap_suppliers apsbp,
121 per_employees_x per,
122 ap_lookup_codes alc,
123 hr_organization_units hou
124 where aeh.request_id = l_req_id
125 and nvl(aeh.vouchno, 0) > 0
126 and ai.invoice_id = aeh.vouchno
127 and aeh.reject_code is null
128 and hou.organization_id(+) = aeh.org_id
129 and per.employee_id = aeh.employee_id
130 and aps.employee_id(+) = aeh.employee_id
131 and apsbp.vendor_id(+) = aeh.vendor_id
132 and alc.lookup_type = decode(aeh.hold_lookup_code, null, 'EXPENSE REPORT STATUS', 'HOLD CODE')
133 and alc.lookup_code = decode(aeh.hold_lookup_code, null, nvl(aeh.expense_status_code, 'INVOICED'), aeh.hold_lookup_code)
134 UNION ALL
135
136 SELECT per.full_name,
137 per.npw_number,
138 aps.vendor_name,
139 aps.segment1,
140 aeh.invoice_num,
141 aeh.week_end_date,
142 aeh.payment_currency_code,
143 ai.invoice_amount,
144 decode(aeh.hold_lookup_code, null, null, alc.description) lookup_desc,
145 aeh.description header_desc,
146 nvl(aeh.vouchno,0),
147 aeh.org_id OU_ID,
148 hou.name,
149 aeh.default_currency_code
150 FROM
151 ap_expense_report_headers_all aeh,
152 ap_invoices_all ai,
153 ap_suppliers aps,
154 per_cont_workers_x per,
155 ap_lookup_codes alc,
156 hr_organization_units hou
157 where aeh.request_id = l_req_id
158 and nvl(aeh.vouchno, 0) > 0
159 and ai.invoice_id = aeh.vouchno
160 and aeh.reject_code is null
161 and hou.organization_id(+) = aeh.org_id
162 and per.person_id = aeh.employee_id
163 and aps.vendor_id(+) = aeh.vendor_id
164 and alc.lookup_type = decode(aeh.hold_lookup_code, null, 'EXPENSE REPORT STATUS', 'HOLD CODE')
165 and alc.lookup_code = decode(aeh.hold_lookup_code, null, nvl(aeh.expense_status_code, 'INVOICED'), aeh.hold_lookup_code))
166 ORDER BY OU_ID;
167
168
169 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
170 l_export_message VARCHAR2(100);
171 l_count NUMBER := 0;
172 l_full_name per_employees_x.full_name%TYPE;
173 l_emp_num per_employees_x.employee_num%TYPE;
174 l_vendor_name ap_suppliers.vendor_name%TYPE;
175 l_vendor_num ap_suppliers.segment1%TYPE;
176 l_inv_num ap_expense_report_headers_all.invoice_num%TYPE;
177 l_inv_date ap_expense_report_headers_all.week_end_date%TYPE;
178 l_inv_curr ap_expense_report_headers_all.payment_currency_code%TYPE;
179 l_inv_amount ap_expense_report_headers_all.total%TYPE;
180 l_reject_reason ap_lookup_codes.description%TYPE;
181 l_invoice_num ap_invoices_all.invoice_num%TYPE;
182 l_prepay_num ap_invoices_all.invoice_num%TYPE;
183 l_inv_curr_code ap_invoices_all.invoice_currency_code%TYPE;
184 l_inv_amt ap_invoices_all.invoice_amount%TYPE;
185 l_inv_remaining NUMBER;
186 l_prepay_remaining NUMBER;
187 l_per_prepay NUMBER;
188 l_prepay_applied NUMBER;
189 l_vouchno NUMBER;
190 l_exp_desc ap_expense_report_headers_all.description%TYPE;
191 l_hold ap_lookup_codes.description%TYPE;
192 l_org_id NUMBER;
193 l_prev_org_id NUMBER;
194 l_org_name hr_organization_units.name%TYPE;
195 l_print_header BOOLEAN := TRUE;
196 l_page_count NUMBER;
197 l_base_currency ap_system_parameters_all.base_currency_code%TYPE;
198 l_default_rate_type ap_system_parameters_all.default_exchange_rate_type%TYPE;
199 l_def_currency_code ap_expense_report_headers_all.default_currency_code%TYPE;
200 l_conv_amount NUMBER := 0;
201 l_total_func_amount NUMBER := 0;
202 l_rate NUMBER := 0;
203 l_header NUMBER := 0;
204
205
206 BEGIN
207 fnd_message.set_name('SQLAP', 'OIE_EXPENSE_REPORT_EXPORT');
208 l_export_message := FND_MESSAGE.Get;
209
210 WriteOutput('<html><head><title>'||l_export_message||'-'||l_request_id||'</title><style>th{border-bottom:1px dashed black;}</style></head><body>');
211
212 /* WriteOutput('<center><h3>'||l_export_message||' ('||to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||')</h3></center>');
213
214
215 WriteOutput('<BR><BR>');
216 WriteOutput('<center><table name="all_links" width="75%" border="0">');
217 WriteOutput('<tr>');
218 fnd_message.set_name('SQLAP', 'AP_APXXTR_IMPORT_9');
219 l_export_message := FND_MESSAGE.Get;
220 WriteOutput('<td width="25%" align="center"><a href="#audit">'||l_export_message||'</a></td>');
221 fnd_message.set_name('SQLAP', 'AP_APXXTR_PREPAY_1');
222 l_export_message := FND_MESSAGE.Get;
223 WriteOutput('<td width="25%" align="center"><a href="#prepay">'||l_export_message||'</a></td>');
224 fnd_message.set_name('SQLAP', 'AP_APXXTR_EXCEPT_1');
225 l_export_message := FND_MESSAGE.Get;
226 WriteOutput('<td width="25%" align="center"><a href="#rejections">'||l_export_message||'</a></td>');
227 WriteOutput('</tr>');
228 WriteOutput('</table></center>');
229 WriteOutput('<BR><BR>');*/
230
231 WriteOutput('<a name="audit"></a>');
232
233
234 l_count := 0;
235 l_org_id := -999999;
236 l_prev_org_id := -999999;
237 l_page_count := 0;
238 l_rate := 0;
239 l_header := 0;
240 OPEN export_reports(l_request_id);
241 LOOP
242 FETCH export_reports INTO l_full_name, l_emp_num, l_vendor_name, l_vendor_num, l_inv_num, l_inv_date, l_inv_curr, l_inv_amount, l_hold, l_exp_desc, l_vouchno, l_org_id, l_org_name, l_def_currency_code;
243 EXIT WHEN export_reports%NOTFOUND;
244 l_print_header := FALSE;
245 IF (l_org_id <> l_prev_org_id) THEN
246
247 IF(l_header > 0) THEN
248 WriteOutput('</table></center>');
249 WriteOutput('<center><table name="aud_tabfunfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
250 WriteOutput('<tr>');
251 fnd_message.set_name('SQLAP', 'AP_APXXTR_IMPORT_8');
252 FND_MESSAGE.SET_TOKEN('AMOUNT', l_total_func_amount);
253 l_export_message := FND_MESSAGE.Get;
254 WriteOutput('<tr>');
255 WriteOutput('<td>'||l_export_message||'</td>');
256 WriteOutput('</tr>');
257 WriteOutput('</table></center>');
258 END IF;
259
260 l_total_func_amount := 0;
261 l_conv_amount := 0;
262 l_print_header := TRUE;
263 l_header := 0;
264 BEGIN
265 SELECT base_currency_code, default_exchange_rate_type
266 INTO l_base_currency, l_default_rate_type
267 FROM ap_system_parameters_all where org_id = l_org_id;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 NULL;
272 END;
273 END IF;
274 BEGIN
275 IF(l_base_currency = l_def_currency_code) THEN
276 l_total_func_amount := l_total_func_amount + l_inv_amount;
277 ELSE
278 l_rate := AP_UTILITIES_PKG.get_exchange_rate(l_def_currency_code, l_base_currency, l_default_rate_type, sysdate, 'PrintExportOutput');
279 l_conv_amount := AP_WEB_UTILITIES_PKG.Oie_Round_Currency(l_inv_amount * l_rate, l_base_currency);
280 l_total_func_amount := l_total_func_amount + l_conv_amount;
281 END IF;
282 EXCEPTION
283 WHEN OTHERS THEN
284 NULL;
285 END;
286 IF(l_print_header = TRUE) THEN
287 l_header := 1;
288 l_page_count := l_page_count + 1;
289 IF (l_page_count > 1 ) THEN
290 WriteOutput('<p style="page-break-before: always">');
291 END IF;
292 WriteOutput('<center><table name="audit_tabhdr'||l_page_count||'" width="95%" border="0" cellspacing="15">');
293 WriteOutput('<tr>');
294 WriteOutput('<td width="35%">'||l_org_name||'<td>');
295 fnd_message.set_name('SQLAP', 'OIE_EXPENSE_REPORT_EXPORT');
296 l_export_message := FND_MESSAGE.Get;
297 WriteOutput('<td width="30%">'||l_export_message||'<td>');
298 WriteOutput('<td width="15%">'||to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||'<td>');
299 fnd_message.set_name('SQLAP', 'OIE_EXPORT_PAGE');
300 l_export_message := FND_MESSAGE.Get;
301 WriteOutput('<td width="15%">'||l_export_message||': '||l_page_count||'<td>');
302 WriteOutput('</tr>');
303 WriteOutput('</table></center>');
304 WriteOutput('<BR><BR>');
305 fnd_message.set_name('SQLAP', 'AP_APXXTR_IMPORT_9');
306 l_export_message := FND_MESSAGE.Get;
307 WriteOutput('<center><h4>'||l_export_message||'</h4></center>');
308
309 WriteOutput('<center><table name="audit_tab'||l_page_count||'" width="95%" border="0" cellspacing="15">');
310 WriteOutput('<tr>');
311
312 fnd_message.set_name('SQLAP', 'OIE_REP_EMPLOYEE_NAME');
313 l_export_message := FND_MESSAGE.Get;
314 WriteOutput('<th align="left">'||l_export_message||'</th>');
315
316 fnd_message.set_name('SQLAP', 'OIE_EMPLOYEE_NUMBER');
317 l_export_message := FND_MESSAGE.Get;
318 WriteOutput('<th align="left">'||l_export_message||'</th>');
319
320 fnd_message.set_name('SQLAP', 'OIE_SUPPLIER_NAME');
321 l_export_message := FND_MESSAGE.Get;
322 WriteOutput('<th align="left">'||l_export_message||'</th>');
323
324 fnd_message.set_name('SQLAP', 'OIE_SUPPLIER_NUMBER');
325 l_export_message := FND_MESSAGE.Get;
326 WriteOutput('<th align="left">'||l_export_message||'</th>');
327
328 fnd_message.set_name('SQLAP', 'OIE_INVOICE_NUMBER');
329 l_export_message := FND_MESSAGE.Get;
330 WriteOutput('<th align="left">'||l_export_message||'</th>');
331
332 fnd_message.set_name('SQLAP', 'OIE_INVOICE_DATE');
333 l_export_message := FND_MESSAGE.Get;
334 WriteOutput('<th align="left">'||l_export_message||'</th>');
335
336 fnd_message.set_name('SQLAP', 'OIE_INVOICE_CURRENCY');
337 l_export_message := FND_MESSAGE.Get;
338 WriteOutput('<th align="left">'||l_export_message||'</th>');
339
340 fnd_message.set_name('SQLAP', 'OIE_INVOICE_AMOUNT');
341 l_export_message := FND_MESSAGE.Get;
342 WriteOutput('<th align="right">'||l_export_message||'</th>');
343
344 fnd_message.set_name('SQLAP', 'AP_ALL_REPORTS_DESCRIPTION');
345 l_export_message := FND_MESSAGE.Get;
346 WriteOutput('<th align="left">'||l_export_message||'</th>');
347
348 fnd_message.set_name('SQLAP', 'AP_ACTION_LIST_HOLD');
349 l_export_message := FND_MESSAGE.Get;
350 WriteOutput('<th align="right">'||l_export_message||'</th>');
351 WriteOutput('</tr>');
352 END IF;
353
354 WriteOutput('<tr>');
355 WriteOutput('<td>'||l_full_name||'</td>');
356 WriteOutput('<td>'||l_emp_num||'</td>');
357 WriteOutput('<td>'||l_vendor_name||'</td>');
358 WriteOutput('<td>'||l_vendor_num||'</td>');
359 WriteOutput('<td>'||l_inv_num||'</td>');
360 WriteOutput('<td>'||l_inv_date||'</td>');
361 WriteOutput('<td>'||l_inv_curr||'</td>');
362 WriteOutput('<td align="right">'||l_inv_amount||'</td>');
363 WriteOutput('<td>'||l_exp_desc||'</td>');
364 WriteOutput('<td>'||l_hold||'</td>');
365 WriteOutput('</tr>');
366 WriteOutput('<tr>');
367 WriteOutput('<td colspan="10"> </td>');
368 WriteOutput('</tr>');
369
370 IF (l_vouchno > 0) THEN
371 l_count := l_count + 1;
372 END IF;
373
374 l_prev_org_id := l_org_id;
375 END LOOP;
376 CLOSE export_reports;
377 IF(l_header > 0) THEN
378 WriteOutput('</table></center>');
379 WriteOutput('<center><table name="aud_tabfunfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
380 WriteOutput('<tr>');
381 fnd_message.set_name('SQLAP', 'AP_APXXTR_IMPORT_8');
382 FND_MESSAGE.SET_TOKEN('AMOUNT', l_total_func_amount);
383 l_export_message := FND_MESSAGE.Get;
384 WriteOutput('<tr>');
385 WriteOutput('<td>'||l_export_message||'</td>');
386 WriteOutput('</tr>');
387 WriteOutput('</table></center>');
388 END IF;
389 IF(l_count > 0) THEN
390 fnd_message.set_name('SQLAP', 'AP_APXXTR_IMPORT_7');
391 FND_MESSAGE.SET_TOKEN('COUNT', l_count);
392 l_export_message := FND_MESSAGE.Get;
393 WriteOutput('<center><table name="aud_tabfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
394 WriteOutput('<tr>');
395 WriteOutput('<td>'||l_export_message||'</td>');
396 WriteOutput('</tr>');
397 WriteOutput('<tr>');
398 WriteOutput('<td> </td>');
399 WriteOutput('</tr>');
400 WriteOutput('</table></center>');
401 WriteOutput('<BR><BR>');
402 ELSE
403 fnd_message.set_name('SQLAP', 'AP_APXXTR_IMPORT_9');
404 l_export_message := FND_MESSAGE.Get;
405 WriteOutput('<center><h4>'||l_export_message||'</h4></center>');
406 WriteOutput('<center><table name="aud_tabfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
407 WriteOutput('<tr>');
408 WriteOutput('<td><hr style="border:dashed #000000; border-width:1px 0 0 0; height:0;line-height:0px;font-size:0;margin:0;padding:0;"></td>');
409 fnd_message.set_name('SQLAP', 'OIE_NO_REPORT_SELECTED');
410 l_export_message := FND_MESSAGE.Get;
411 WriteOutput('</tr>');
412 WriteOutput('<tr>');
413 WriteOutput('<td>'||l_export_message||'</td>');
414 WriteOutput('</tr>');
415 WriteOutput('</table></center>');
416 WriteOutput('<BR><BR>');
417 END IF;
418
419
420 WriteOutput('<a name="prepay"></a>');
421
422 l_count := 0;
423 l_org_id := -999999;
424 l_prev_org_id := -999999;
425 l_rate := 0;
426 l_header := 0;
427 OPEN c_prepayments(l_request_id);
428 LOOP
429 FETCH c_prepayments INTO l_invoice_num, l_prepay_num, l_vendor_name, l_vendor_num, l_inv_curr_code, l_inv_amt, l_prepay_applied, l_inv_remaining, l_prepay_remaining, l_per_prepay, l_org_id, l_org_name, l_def_currency_code;
430 EXIT WHEN c_prepayments%NOTFOUND;
431 l_print_header := FALSE;
432 IF (l_org_id <> l_prev_org_id) THEN
433
434 IF(l_header > 0) THEN
435 WriteOutput('</table></center>');
436 WriteOutput('<center><table name="aud_tabfunfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
437 WriteOutput('<tr>');
438 fnd_message.set_name('SQLAP', 'AP_APXXTR_PREPAY_7');
439 l_export_message := FND_MESSAGE.Get;
440 WriteOutput('<tr>');
441 WriteOutput('<td>'||l_export_message||' ' || l_total_func_amount || '</td>');
442 WriteOutput('</tr>');
443 WriteOutput('</table></center>');
444 END IF;
445 l_total_func_amount := 0;
446 l_conv_amount := 0;
447 l_print_header := TRUE;
448 l_header := 0;
449 BEGIN
450 SELECT base_currency_code, default_exchange_rate_type
451 INTO l_base_currency, l_default_rate_type
452 FROM ap_system_parameters_all where org_id = l_org_id;
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 NULL;
457 END;
458 END IF;
459 BEGIN
460 IF(l_base_currency = l_def_currency_code) THEN
461 l_total_func_amount := l_total_func_amount + l_inv_amount;
462 ELSE
463 l_rate := AP_UTILITIES_PKG.get_exchange_rate(l_def_currency_code, l_base_currency, l_default_rate_type, sysdate, 'PrintExportOutput');
464 l_conv_amount := AP_WEB_UTILITIES_PKG.Oie_Round_Currency(l_inv_amount * l_rate, l_base_currency);
465 l_total_func_amount := l_total_func_amount + l_conv_amount;
466 END IF;
467 EXCEPTION
468 WHEN OTHERS THEN
469 NULL;
470 END;
471 IF(l_print_header = TRUE) THEN
472
473 l_page_count := l_page_count + 1;
474 l_header := 1;
475 IF (l_page_count > 1 ) THEN
476 WriteOutput('<p style="page-break-before: always">');
477 END IF;
478 WriteOutput('<center><table name="prepay_tabhdr'||l_page_count||'" width="95%" border="0" cellspacing="15">');
479 WriteOutput('<tr>');
480 WriteOutput('<td width="35%">'||l_org_name||'<td>');
481 fnd_message.set_name('SQLAP', 'OIE_EXPENSE_REPORT_EXPORT');
482 l_export_message := FND_MESSAGE.Get;
483 WriteOutput('<td width="30%">'||l_export_message||'<td>');
484 WriteOutput('<td width="15%">'||to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||'<td>');
485 fnd_message.set_name('SQLAP', 'OIE_EXPORT_PAGE');
486 l_export_message := FND_MESSAGE.Get;
487 WriteOutput('<td width="15%">'||l_export_message||': '||l_page_count||'<td>');
488 WriteOutput('</tr>');
489 WriteOutput('</table></center>');
490 WriteOutput('<BR><BR>');
491 fnd_message.set_name('SQLAP', 'AP_APXXTR_PREPAY_1');
492 l_export_message := FND_MESSAGE.Get;
493 WriteOutput('<center><h4>'||l_export_message||'</h4></center>');
494
495 WriteOutput('<center><table name="prepay_tab'||l_page_count||'" width="95%" border="0" cellspacing="15">');
496 WriteOutput('<tr>');
497 fnd_message.set_name('SQLAP', 'OIE_SUPPLIER_NAME');
498 l_export_message := FND_MESSAGE.Get;
499 WriteOutput('<th align="left">'||l_export_message||'</th>');
500
501 fnd_message.set_name('SQLAP', 'OIE_SUPPLIER_NUMBER');
502 l_export_message := FND_MESSAGE.Get;
503 WriteOutput('<th align="left">'||l_export_message||'</th>');
504
505 fnd_message.set_name('SQLAP', 'OIE_INVOICE_NUMBER');
506 l_export_message := FND_MESSAGE.Get;
507 WriteOutput('<th align="left">'||l_export_message||'</th>');
508
509 fnd_message.set_name('SQLAP', 'OIE_PREPAY_NUMBER');
510 l_export_message := FND_MESSAGE.Get;
511 WriteOutput('<th align="left">'||l_export_message||'</th>');
512
513 fnd_message.set_name('SQLAP', 'OIE_INVOICE_CURRENCY');
514 l_export_message := FND_MESSAGE.Get;
515 WriteOutput('<th align="left">'||l_export_message||'</th>');
516
517 fnd_message.set_name('SQLAP', 'OIE_INVOICE_AMOUNT');
518 l_export_message := FND_MESSAGE.Get;
519 WriteOutput('<th align="right">'||l_export_message||'</th>');
520
521 fnd_message.set_name('SQLAP', 'OIE_AMOUNT_APPLIED');
522 l_export_message := FND_MESSAGE.Get;
523 WriteOutput('<th align="right">'||l_export_message||'</th>');
524
525 fnd_message.set_name('SQLAP', 'OIE_INV_AMOUNT_REM');
526 l_export_message := FND_MESSAGE.Get;
527 WriteOutput('<th align="right">'||l_export_message||'</th>');
528
529 fnd_message.set_name('SQLAP', 'OIE_PREPAY_AMT_AVAILABLE');
530 l_export_message := FND_MESSAGE.Get;
531 WriteOutput('<th align="right">'||l_export_message||'</th>');
532
533 WriteOutput('</tr>');
534
535
536 END IF;
537 WriteOutput('<tr>');
538 WriteOutput('<td>'||l_vendor_name||'</td>');
539 WriteOutput('<td>'||l_vendor_num||'</td>');
540 WriteOutput('<td>'||l_invoice_num||'</td>');
541 WriteOutput('<td>'||l_prepay_num||'</td>');
542 WriteOutput('<td>'||l_inv_curr_code||'</td>');
543 WriteOutput('<td align="right">'||l_inv_amt||'</td>');
544 WriteOutput('<td align="right">'||l_prepay_applied||'</td>');
545 WriteOutput('<td align="right">'||l_inv_remaining||'</td>');
546 WriteOutput('<td align="right">'||l_prepay_remaining||'</td>');
547 WriteOutput('</tr>');
548 WriteOutput('<tr>');
549 WriteOutput('<td colspan="9"> </td>');
550 WriteOutput('</tr>');
551
552 l_count := l_count + 1;
553
554 l_prev_org_id := l_org_id;
555 END LOOP;
556 CLOSE c_prepayments;
557
558 IF(l_header > 0) THEN
559 WriteOutput('</table></center>');
560 WriteOutput('<center><table name="aud_tabfunfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
561 WriteOutput('<tr>');
562 fnd_message.set_name('SQLAP', 'AP_APXXTR_PREPAY_7');
563 l_export_message := FND_MESSAGE.Get;
564 WriteOutput('<tr>');
565 WriteOutput('<td>'||l_export_message||' ' || l_total_func_amount || '</td>');
566 WriteOutput('</tr>');
567 WriteOutput('</table></center>');
568 END IF;
569
570 --WriteOutput('</table></center>');
571 IF(l_count > 0) THEN
572 fnd_message.set_name('SQLAP', 'AP_APXXTR_PREPAY_6');
573 FND_MESSAGE.SET_TOKEN('COUNT', l_count);
574 l_export_message := FND_MESSAGE.Get;
575 WriteOutput('<center><table name="prepay_tabfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
576 WriteOutput('<tr>');
577 WriteOutput('<td>'||l_export_message||'</td>');
578 WriteOutput('</tr>');
579 WriteOutput('<tr>');
580 WriteOutput('<td> </td>');
581 WriteOutput('</tr>');
582 WriteOutput('</table></center>');
583 WriteOutput('<BR><BR>');
584 ELSE
585 fnd_message.set_name('SQLAP', 'AP_APXXTR_PREPAY_1');
586 l_export_message := FND_MESSAGE.Get;
587 WriteOutput('<center><h4>'||l_export_message||'</h4></center>');
588 WriteOutput('<center><table name="prepay_tabfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
589 WriteOutput('<tr>');
590 WriteOutput('<td><hr style="border:dashed #000000; border-width:1px 0 0 0; height:0;line-height:0px;font-size:0;margin:0;padding:0;"></td>');
591 fnd_message.set_name('SQLAP', 'OIE_NO_PREPAY_APPLIED');
592 l_export_message := FND_MESSAGE.Get;
593 WriteOutput('</tr>');
594 WriteOutput('<tr>');
595 WriteOutput('<td>'||l_export_message||'</td>');
596 WriteOutput('</tr>');
597 WriteOutput('</table></center>');
598 WriteOutput('<BR><BR>');
599 END IF;
600
601
602 WriteOutput('<a name="rejections"></a>');
603
604
605 l_count := 0;
606 l_org_id := -999999;
607 l_prev_org_id := -999999;
608 l_rate := 0;
609 l_header := 0;
610 OPEN reject_reports(l_request_id);
611 LOOP
612 FETCH reject_reports INTO l_full_name, l_emp_num, l_vendor_name, l_vendor_num, l_inv_num, l_inv_date, l_inv_curr, l_inv_amount, l_reject_reason, l_org_id, l_org_name, l_def_currency_code;
613 EXIT WHEN reject_reports%NOTFOUND;
614 l_print_header := FALSE;
615 IF (l_org_id <> l_prev_org_id) THEN
616 IF(l_header > 0) THEN
617 WriteOutput('</table></center>');
618 WriteOutput('<center><table name="aud_tabfunfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
619 WriteOutput('<tr>');
620 fnd_message.set_name('SQLAP', 'OIE_FUNC_AMT_REJECTED');
621 FND_MESSAGE.SET_TOKEN('AMOUNT', l_total_func_amount);
622 l_export_message := FND_MESSAGE.Get;
623 WriteOutput('<tr>');
624 WriteOutput('<td>'||l_export_message||'</td>');
625 WriteOutput('</tr>');
626 WriteOutput('</table></center>');
627 END IF;
628 l_print_header := TRUE;
629 l_total_func_amount := 0;
630 l_conv_amount := 0;
631 l_header := 0;
632 BEGIN
633 SELECT base_currency_code, default_exchange_rate_type
634 INTO l_base_currency, l_default_rate_type
635 FROM ap_system_parameters_all where org_id = l_org_id;
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 NULL;
640 END;
641 END IF;
642 BEGIN
643 IF(l_base_currency = l_def_currency_code) THEN
644 l_total_func_amount := l_total_func_amount + l_inv_amount;
645 ELSE
646 l_rate := AP_UTILITIES_PKG.get_exchange_rate(l_def_currency_code, l_base_currency, l_default_rate_type, sysdate, 'PrintExportOutput');
647 l_conv_amount := AP_WEB_UTILITIES_PKG.Oie_Round_Currency(l_inv_amount * l_rate, l_base_currency);
648 l_total_func_amount := l_total_func_amount + l_conv_amount;
649 END IF;
650 EXCEPTION
651 WHEN OTHERS THEN
652 NULL;
653 END;
654 IF(l_print_header = TRUE) THEN
655 l_page_count := l_page_count + 1;
656 l_header := 1;
657 IF (l_page_count > 1 ) THEN
658 WriteOutput('<p style="page-break-before: always">');
659 END IF;
660
661 WriteOutput('<center><table name="rejections_tabhdr'||l_page_count||'" width="95%" border="0" cellspacing="15">');
662 WriteOutput('<tr>');
663 WriteOutput('<td width="35%">'||l_org_name||'<td>');
664 fnd_message.set_name('SQLAP', 'OIE_EXPENSE_REPORT_EXPORT');
665 l_export_message := FND_MESSAGE.Get;
666 WriteOutput('<td width="30%">'||l_export_message||'<td>');
667 WriteOutput('<td width="15%">'||to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||'<td>');
668 fnd_message.set_name('SQLAP', 'OIE_EXPORT_PAGE');
669 l_export_message := FND_MESSAGE.Get;
670 WriteOutput('<td width="15%">'||l_export_message||': '||l_page_count||'<td>');
671 WriteOutput('</tr>');
672 WriteOutput('</table></center>');
673 WriteOutput('<BR><BR>');
674 fnd_message.set_name('SQLAP', 'AP_APXXTR_EXCEPT_1');
675 l_export_message := FND_MESSAGE.Get;
676 WriteOutput('<center><h4>'||l_export_message||'</h4></center>');
677
678 WriteOutput('<center><table name="rejections_tab'||l_page_count||'" width="95%" border="0" cellspacing="15">');
679 WriteOutput('<tr>');
680
681 fnd_message.set_name('SQLAP', 'OIE_REP_EMPLOYEE_NAME');
682 l_export_message := FND_MESSAGE.Get;
683 WriteOutput('<th align="left">'||l_export_message||'</th>');
684
685 fnd_message.set_name('SQLAP', 'OIE_EMPLOYEE_NUMBER');
686 l_export_message := FND_MESSAGE.Get;
687 WriteOutput('<th align="left">'||l_export_message||'</th>');
688
689 fnd_message.set_name('SQLAP', 'OIE_SUPPLIER_NAME');
690 l_export_message := FND_MESSAGE.Get;
691 WriteOutput('<th align="left">'||l_export_message||'</th>');
692
693 fnd_message.set_name('SQLAP', 'OIE_SUPPLIER_NUMBER');
694 l_export_message := FND_MESSAGE.Get;
695 WriteOutput('<th align="left">'||l_export_message||'</th>');
696
697 fnd_message.set_name('SQLAP', 'OIE_INVOICE_NUMBER');
698 l_export_message := FND_MESSAGE.Get;
699 WriteOutput('<th align="left">'||l_export_message||'</th>');
700
701 fnd_message.set_name('SQLAP', 'OIE_INVOICE_DATE');
702 l_export_message := FND_MESSAGE.Get;
703 WriteOutput('<th align="left">'||l_export_message||'</th>');
704
705 fnd_message.set_name('SQLAP', 'OIE_INVOICE_CURRENCY');
706 l_export_message := FND_MESSAGE.Get;
707 WriteOutput('<th align="left">'||l_export_message||'</th>');
708
709 fnd_message.set_name('SQLAP', 'OIE_INVOICE_AMOUNT');
710 l_export_message := FND_MESSAGE.Get;
711 WriteOutput('<th align="right">'||l_export_message||'</th>');
712
713 fnd_message.set_name('SQLAP', 'OIE_REJECTION_REASON');
714 l_export_message := FND_MESSAGE.Get;
715 WriteOutput('<th align="left">'||l_export_message||'</th>');
716
717 WriteOutput('</tr>');
718
719
720 END IF;
721 WriteOutput('<tr>');
722 WriteOutput('<td>'||l_full_name||'</td>');
723 WriteOutput('<td>'||l_emp_num||'</td>');
724 WriteOutput('<td>'||l_vendor_name||'</td>');
725 WriteOutput('<td>'||l_vendor_num||'</td>');
726 WriteOutput('<td>'||l_inv_num||'</td>');
727 WriteOutput('<td>'||l_inv_date||'</td>');
728 WriteOutput('<td>'||l_inv_curr||'</td>');
729 WriteOutput('<td align="right">'||l_inv_amount||'</td>');
730 WriteOutput('<td>'||l_reject_reason||'</td>');
731 WriteOutput('</tr>');
732 WriteOutput('<tr>');
733 WriteOutput('<td colspan="9"> </td>');
734 WriteOutput('</tr>');
735
736 l_count := l_count + 1;
737
738
739
740 l_prev_org_id := l_org_id;
741 END LOOP;
742 CLOSE reject_reports;
743 IF(l_header > 0) THEN
744 WriteOutput('</table></center>');
745 WriteOutput('<center><table name="aud_tabfunfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
746 WriteOutput('<tr>');
747 fnd_message.set_name('SQLAP', 'OIE_FUNC_AMT_REJECTED');
748 FND_MESSAGE.SET_TOKEN('AMOUNT', l_total_func_amount);
749 l_export_message := FND_MESSAGE.Get;
750 WriteOutput('<tr>');
751 WriteOutput('<td>'||l_export_message||'</td>');
752 WriteOutput('</tr>');
753 WriteOutput('</table></center>');
754 END IF;
755 IF(l_count > 0) THEN
756 fnd_message.set_name('SQLAP', 'AP_APXXTR_EXCEPT_5');
757 FND_MESSAGE.SET_TOKEN('COUNT', l_count);
758 l_export_message := FND_MESSAGE.Get;
759 WriteOutput('<center><table name="rejections_tabfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
760 WriteOutput('<tr>');
761 WriteOutput('<td>'||l_export_message||'</td>');
762 WriteOutput('</tr>');
763 WriteOutput('</table></center>');
764 WriteOutput('<BR><BR>');
765 ELSE
766 fnd_message.set_name('SQLAP', 'AP_APXXTR_EXCEPT_1');
767 l_export_message := FND_MESSAGE.Get;
768 WriteOutput('<center><h4>'||l_export_message||'</h4></center>');
769 WriteOutput('<center><table name="rejections_tabfooter'||l_page_count||'" width="95%" border="0" cellspacing="15">');
770 WriteOutput('<tr>');
771 WriteOutput('<td><hr style="border:dashed #000000; border-width:1px 0 0 0; height:0;line-height:0px;font-size:0;margin:0;padding:0;"></td>');
772 fnd_message.set_name('SQLAP', 'OIE_NO_REPORT_REJECTED');
773 l_export_message := FND_MESSAGE.Get;
774 WriteOutput('</tr>');
775 WriteOutput('<tr>');
776 WriteOutput('<td>'||l_export_message||'</td>');
777 WriteOutput('</tr>');
778 WriteOutput('</table></center>');
779 WriteOutput('<BR><BR>');
780 END IF;
781
782 WriteOutput('</body></html>');
783 EXCEPTION
784 WHEN OTHERS THEN
785 fnd_file.put_line(fnd_file.log, 'Exception writing output ' || SQLERRM);
786 END PrintExportOutput;
787
788 ---------------------------------------------------------------------------------------------------------
789 -- Bug: 11071821, Validate Line and Dist amounts for nulls
790 ---------------------------------------------------------------------------------------------------------
791
792 PROCEDURE ValidateAmounts(p_report_header_id IN NUMBER,
793 p_reject_code OUT NOCOPY ap_expense_report_headers.reject_code%TYPE) IS
794 l_line_count NUMBER := 0;
795 l_dist_count NUMBER := 0;
796 l_header_total NUMBER;
797 l_line_total NUMBER;
798 l_dist_total NUMBER;
799 l_parameter_id NUMBER;
800 l_debug_info VARCHAR2(2000);
801
802 BEGIN
803
804 IF g_debug_switch = 'Y' THEN
805 fnd_file.put_line(fnd_file.log, 'Start ValidateAmounts ');
806 END IF;
807
808
809 SELECT count(*)
810 INTO l_line_count
811 FROM ap_expense_report_lines_all
812 WHERE report_header_id = p_report_header_id
813 AND amount IS NULL;
814
815 IF g_debug_switch = 'Y' THEN
816 fnd_file.put_line(fnd_file.log, 'Lines with null amount ' || l_line_count);
817 END IF;
818
819 SELECT count(*)
820 INTO l_dist_count
821 FROM ap_exp_report_dists_all
822 WHERE report_header_id = p_report_header_id
823 AND amount IS NULL;
824
825 IF g_debug_switch = 'Y' THEN
826 fnd_file.put_line(fnd_file.log, 'Dists with null amount ' || l_dist_count);
827 END IF;
828
829 IF (l_line_count > 0 OR l_dist_count > 0) THEN
830 p_reject_code := 'LINE AMOUNT IS NULL';
831 END IF;
832
833 l_debug_info := 'Fetching Header Total';
834 select total into l_header_total from ap_expense_report_headers_all where report_header_id = p_report_header_id;
835
836 -- Bug 14627375: Personal expenses to be excluded
837 select parameter_id into l_parameter_id from ap_expense_report_params_all where expense_type_code = 'PERSONAL';
838
839 l_debug_info := 'Fetching Line Total';
840 select nvl(sum(amount),0) into l_line_total
841 from ap_expense_report_lines_all
842 where report_header_id = p_report_header_id
843 and (itemization_parent_id is null or itemization_parent_id <> -1)
844 and web_parameter_id <> l_parameter_id;
845
846 l_debug_info := 'Fetching Dist Total';
847 select nvl(sum(amount),0) into l_dist_total from ap_exp_report_dists_all where report_header_id = p_report_header_id
848 and (web_parameter_id is null or web_parameter_id <> l_parameter_id);
849
850 l_debug_info := 'Header Total: ' || l_header_total || ', Line Total: ' || l_line_total || ', Dist Total: ' || l_dist_total;
851 IF g_debug_switch = 'Y' THEN
852 fnd_file.put_line(fnd_file.log, l_debug_info);
853 END IF;
854
855 IF (l_header_total <> l_line_total) THEN
856 p_reject_code := 'EXPENSE HEADER LINE MISMATCH';
857 END IF;
858
859 IF (l_line_total <> l_dist_total) THEN
860 p_reject_code := 'EXPENSE LINE DIST MISMATCH';
861 END IF;
862
863
864 IF g_debug_switch = 'Y' THEN
865 fnd_file.put_line(fnd_file.log, 'END ValidateAmounts ');
866 END IF;
867 EXCEPTION
868 WHEN OTHERS THEN
869 IF g_debug_switch = 'Y' THEN
870 fnd_file.put_line(fnd_file.log, 'Error for the Report#'||to_char(p_report_header_id)||
871 ':"'||SQLERRM||'" raised and ignored while validating amounts.');
872
873 END IF;
874
875 END ValidateAmounts;
876
877 -----------------
878 -- Bug#2823530 : Transfer the attachments to AP.
879 -----------------
880 -----------------------------------------------------------------------------------------------
881 PROCEDURE TransferAttachments(p_report_header_id IN NUMBER,
882 p_invoice_id IN NUMBER)
883 IS
884
885 CURSOR expense_attachments_cur(l_report_header_id IN NUMBER) IS
886 SELECT *
887 FROM ( SELECT *
888 FROM fnd_attached_documents
889 WHERE entity_name = 'OIE_HEADER_ATTACHMENTS'
890 AND pk1_value = To_Char(p_report_header_id)
891 UNION ALL
892 SELECT *
893 FROM fnd_attached_documents
894 WHERE entity_name = 'OIE_LINE_ATTACHMENTS'
895 AND pk1_value IN ( SELECT To_Char(report_line_id)
896 FROM ap_expense_report_lines_all
897 WHERE report_header_id = p_report_header_id
898 )
899 ) ORDER BY entity_name,pk1_value,attached_document_id ;--Used the Order by clause so that seq_num will be first given to Header attachments.
900
901 CURSOR expense_documents_cur(l_document_id IN NUMBER) IS
902 SELECT *
903 FROM fnd_documents
904 WHERE document_id = l_document_id;
905
906 CURSOR expense_documents_tl_cur(l_document_id IN NUMBER) IS
907 SELECT *
908 FROM fnd_documents_tl
909 WHERE document_id = l_document_id
910 AND rownum = 1;
911
912 AttachedDocTabRec expense_attachments_cur%ROWTYPE;
913
914 DocumentTabRec expense_documents_cur%ROWTYPE;
915
916 DocumentTLTabRec expense_documents_tl_cur%ROWTYPE;
917
918 l_debug_info VARCHAR2(2000);
919
920 l_rowid varchar2(60) := null;
921 l_media_id NUMBER := null;
922 l_seq_num NUMBER := 1;
923
924 BEGIN
925
926 OPEN expense_attachments_cur(p_report_header_id);
927
928 LOOP
929
930 ------------------------------------------------------------
931 l_debug_info := 'Fetching Attachments for Expense Reports...';
932 ------------------------------------------------------------
933 IF g_debug_switch = 'Y' THEN
934 fnd_file.put_line(fnd_file.log, l_debug_info);
935 END IF;
936
937 FETCH expense_attachments_cur
938 INTO AttachedDocTabRec;
939
940 EXIT WHEN expense_attachments_cur%NOTFOUND;
941
942 BEGIN
943
944 SELECT fnd_attached_documents_s.nextval
945 INTO AttachedDocTabRec.attached_document_id
946 FROM dual;
947
948
949 FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW
950 (x_rowid => l_rowid
951 , x_attached_document_id => AttachedDocTabRec.attached_document_id
952 , x_document_id => AttachedDocTabRec.document_id
953 , x_seq_num => l_seq_num
954 , x_entity_name => 'AP_INVOICES'
955 , x_pk1_value => To_Char(p_invoice_id)
956 , x_pk2_value => null
957 , x_pk3_value => null
958 , x_pk4_value => null
959 , x_pk5_value => null
960 , x_automatically_added_flag => 'Y'
961 , x_creation_date => sysdate
962 , x_created_by => to_number(fnd_global.user_id)
963 , x_last_update_date => sysdate
964 , x_last_updated_by => to_number(fnd_global.user_id)
965 , x_last_update_login => to_number(FND_GLOBAL.LOGIN_ID)
966 , x_column1 => AttachedDocTabRec.column1
967 , x_datatype_id => null
968 , x_category_id => AttachedDocTabRec.category_id
969 , x_security_type => null
970 , X_security_id => null
971 , X_publish_flag => null
972 , X_image_type => null
973 , X_storage_type => null
974 , X_usage_type => null
975 , X_language => null
976 , X_description => null
977 , X_file_name => null
978 , X_media_id => l_media_id
979 , X_doc_attribute_Category => null
980 , X_doc_attribute1 => null
981 , X_doc_attribute2 => null
982 , X_doc_attribute3 => null
983 , X_doc_attribute4 => null
984 , X_doc_attribute5 => null
985 , X_doc_attribute6 => null
986 , X_doc_attribute7 => null
987 , X_doc_attribute8 => null
988 , X_doc_attribute9 => null
989 , X_doc_attribute10 => null
990 , X_doc_attribute11 => null
991 , X_doc_attribute12 => null
992 , X_doc_attribute13 => null
993 , X_doc_attribute14 => null
994 , X_doc_attribute15 => null
995 );
996
997 l_seq_num := l_seq_num + 1;
998
999 /* Logic to update the document usage_type to "S" */
1000
1001 OPEN expense_documents_cur(AttachedDocTabRec.document_id);
1002
1003 FETCH expense_documents_cur
1004 INTO DocumentTabRec;
1005
1006 CLOSE expense_documents_cur;
1007
1008 OPEN expense_documents_tl_cur(AttachedDocTabRec.document_id);
1009
1010 FETCH expense_documents_tl_cur
1011 INTO DocumentTLTabRec;
1012
1013 CLOSE expense_documents_tl_cur;
1014
1015 FND_DOCUMENTS_PKG.Update_Row
1016 (X_document_id => DocumentTabRec.document_id
1017 ,X_last_update_date => sysdate
1018 ,X_last_updated_by => to_number(fnd_global.user_id)
1019 ,X_last_update_login => to_number(FND_GLOBAL.LOGIN_ID)
1020 ,X_datatype_id => DocumentTabRec.datatype_id
1021 ,X_category_id => DocumentTabRec.category_id
1022 ,X_security_type => DocumentTabRec.security_type
1023 ,X_security_id => DocumentTabRec.security_id
1024 ,X_publish_flag => DocumentTabRec.publish_flag
1025 ,X_image_type => DocumentTabRec.image_type
1026 ,X_storage_type => DocumentTabRec.storage_type
1027 ,X_usage_type => 'S'
1028 ,X_start_date_active => DocumentTabRec.start_date_active
1029 ,X_end_date_active => DocumentTabRec.end_date_active
1030 ,X_language => DocumentTLTabRec.language
1031 ,X_description => DocumentTLTabRec.description
1032 ,X_file_name => DocumentTabRec.file_name
1033 ,X_media_id => DocumentTabRec.media_id
1034 ,X_Attribute_Category => DocumentTLTabRec.doc_attribute_category
1035 ,X_Attribute1 => DocumentTLTabRec.doc_attribute1
1036 ,X_Attribute2 => DocumentTLTabRec.doc_attribute2
1037 ,X_Attribute3 => DocumentTLTabRec.doc_attribute3
1038 ,X_Attribute4 => DocumentTLTabRec.doc_attribute4
1039 ,X_Attribute5 => DocumentTLTabRec.doc_attribute5
1040 ,X_Attribute6 => DocumentTLTabRec.doc_attribute6
1041 ,X_Attribute7 => DocumentTLTabRec.doc_attribute7
1042 ,X_Attribute8 => DocumentTLTabRec.doc_attribute8
1043 ,X_Attribute9 => DocumentTLTabRec.doc_attribute9
1044 ,X_Attribute10 => DocumentTLTabRec.doc_attribute10
1045 ,X_Attribute11 => DocumentTLTabRec.doc_attribute11
1046 ,X_Attribute12 => DocumentTLTabRec.doc_attribute12
1047 ,X_Attribute13 => DocumentTLTabRec.doc_attribute13
1048 ,X_Attribute14 => DocumentTLTabRec.doc_attribute14
1049 ,X_Attribute15 => DocumentTLTabRec.doc_attribute15
1050 ,X_url => DocumentTabRec.url
1051 ,X_title => DocumentTLTabRec.title);
1052
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 IF g_debug_switch = 'Y' THEN
1056 --Error raised and ignored while Transferring the attachments.
1057 fnd_file.put_line(fnd_file.log, 'Error for the Report#'||to_char(p_report_header_id)||
1058 ':"'||SQLERRM||'" raised and ignored while Transferring the attachments.');
1059 END IF;
1060 END;
1061
1062 END LOOP;
1063 END TransferAttachments;
1064 -----------------------------------------------------------------------------------------------
1065
1066 -----------------
1067 -- Bug#2823530 : Validate the attachments before transferring to AP.
1068 -----------------
1069 -----------------------------------------------------------------------------------------------
1070 PROCEDURE ValidateAttachCategory(p_report_header_id IN NUMBER,
1071 p_reject_code OUT NOCOPY ap_expense_report_headers.reject_code%TYPE)
1072 IS
1073
1074 l_count_invalid_cat NUMBER;
1075
1076 BEGIN
1077
1078 SELECT Count(*)
1079 INTO l_count_invalid_cat
1080 FROM ( SELECT *
1081 FROM fnd_attached_documents
1082 WHERE entity_name = 'OIE_HEADER_ATTACHMENTS'
1083 AND pk1_value = To_Char(p_report_header_id)
1084 UNION ALL
1085 SELECT *
1086 FROM fnd_attached_documents
1087 WHERE entity_name = 'OIE_LINE_ATTACHMENTS'
1088 AND pk1_value IN ( SELECT To_Char(report_line_id)
1089 FROM ap_expense_report_lines_all
1090 WHERE report_header_id = p_report_header_id
1091 )
1092 ) OIE_ATTACHMENTS
1093 WHERE OIE_ATTACHMENTS.CATEGORY_ID
1094 NOT IN
1095 ( SELECT fdcu.CATEGORY_ID
1096 FROM fnd_doc_category_usages fdcu,
1097 fnd_attachment_functions faf,
1098 fnd_document_categories fdc
1099 WHERE faf.function_name = 'APXINWKB'
1100 AND fdcu.enabled_flag = 'Y'
1101 AND faf.attachment_function_id = fdcu.attachment_function_id
1102 AND fdc.category_id = fdcu.category_id
1103 AND sysdate BETWEEN nvl(start_date_active,sysdate-1) AND nvl(end_date_active,sysdate+1)
1104 );
1105
1106 IF l_count_invalid_cat <> 0 THEN
1107 p_reject_code := 'INVALID ATTACHMENT CATEGORY';
1108 END IF;
1109
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 p_reject_code := substr(SQLCODE,1,25);
1113 IF g_debug_switch = 'Y' THEN
1114 fnd_file.put_line(fnd_file.log, SQLERRM);
1115 END IF;
1116 END ValidateAttachCategory;
1117 -----------------------------------------------------------------------------------------------
1118
1119 -----------------
1120 -- Bug: 6965489
1121 -----------------
1122 -----------------------------------------------------------------------------------------------
1123 PROCEDURE ValidateGLDate(p_source_date IN DATE,
1124 p_valid_inv_gl_date IN DATE,
1125 p_source_item IN VARCHAR2,
1126 p_set_of_books_id IN ap_system_parameters.set_of_books_id%TYPE,
1127 p_open_gl_date OUT NOCOPY DATE,
1128 p_reject_code OUT NOCOPY ap_expense_report_headers.reject_code%TYPE) IS
1129 l_gl_period_status varchar2(2);
1130 l_new_gl_date ap_expense_report_headers_all.week_end_date%TYPE;
1131 l_debug_info VARCHAR2(2000);
1132 BEGIN
1133
1134 IF (p_source_date IS NULL) THEN
1135 RETURN;
1136 END IF;
1137 ------------------------------------------------------------
1138 l_debug_info := 'Validate GL Date with params p_source_date ' || p_source_date || ' , p_valid_inv_gl_date ' || p_valid_inv_gl_date || ' ,p_source_item ' || p_source_item;
1139 ------------------------------------------------------------
1140 IF g_debug_switch = 'Y' THEN
1141 fnd_file.put_line(fnd_file.log, l_debug_info);
1142 END IF;
1143
1144 BEGIN
1145
1146 SELECT closing_status
1147 INTO l_gl_period_status
1148 FROM gl_period_statuses
1149 WHERE application_id=200
1150 AND set_of_books_id= p_set_of_books_id
1151 AND trunc(p_source_date) BETWEEN start_date AND end_date
1152 AND NVL(adjustment_period_flag, 'N') = 'N';
1153
1154 EXCEPTION
1155 WHEN NO_DATA_FOUND THEN
1156 l_gl_period_status := NULL;
1157 WHEN OTHERS THEN
1158 l_gl_period_status := NULL;
1159
1160 END;
1161
1162 ----------------------------------
1163 l_debug_info := p_source_item || ' GL Period status: ' || l_gl_period_status;
1164 ----------------------------------
1165 IF g_debug_switch = 'Y' THEN
1166 fnd_file.put_line(fnd_file.log, l_debug_info);
1167 END IF;
1168
1169
1170 IF l_gl_period_status IS NOT NULL AND (l_gl_period_status = 'O' OR l_gl_period_status = 'F') THEN
1171
1172 ----------------------------------
1173 l_debug_info := p_source_item || ' GL Date is in Valid Period';
1174 ----------------------------------
1175 IF g_debug_switch = 'Y' THEN
1176 fnd_file.put_line(fnd_file.log, l_debug_info);
1177 END IF;
1178 p_open_gl_date := p_source_date;
1179
1180 ELSIF l_gl_period_status IS NOT NULL AND l_gl_period_status = 'N' THEN
1181
1182 ----------------------------------
1183 l_debug_info := p_source_item || ' GL Date is in Never Opened Period, Rejecting the expense report';
1184 ----------------------------------
1185 IF g_debug_switch = 'Y' THEN
1186 fnd_file.put_line(fnd_file.log, l_debug_info);
1187 END IF;
1188
1189 p_reject_code := 'GL Date in Closed Period';
1190
1191 ELSE
1192
1193 ----------------------------------
1194 l_debug_info := p_source_item || ' GL Date is in the closed period or GL period status is null. Getting new GL Date';
1195 ----------------------------------
1196 IF g_debug_switch = 'Y' THEN
1197 fnd_file.put_line(fnd_file.log, l_debug_info);
1198 END IF;
1199
1200 IF p_valid_inv_gl_date IS NOT NULL THEN
1201 p_open_gl_date := p_valid_inv_gl_date;
1202 ELSE
1203 BEGIN
1204
1205 SELECT min(start_date)
1206 INTO l_new_gl_date
1207 FROM gl_period_statuses
1208 WHERE application_id = 200
1209 AND set_of_books_id = p_set_of_books_id
1210 AND start_date > trunc(p_source_date)
1211 AND (closing_status in ('O', 'F'))
1212 AND NVL(adjustment_period_flag, 'N') = 'N';
1213
1214 EXCEPTION
1215 WHEN NO_DATA_FOUND THEN
1216 l_new_gl_date := NULL;
1217 WHEN OTHERS THEN
1218 l_new_gl_date := NULL;
1219 END;
1220
1221 IF l_new_gl_date IS NULL THEN
1222 p_reject_code := 'No Open Periods';
1223 ELSE
1224 p_open_gl_date := l_new_gl_date;
1225 END IF;
1226 END IF;
1227
1228 END IF;
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 p_reject_code := substr(SQLCODE,1,25);
1233 IF g_debug_switch = 'Y' THEN
1234 fnd_file.put_line(fnd_file.log, SQLERRM);
1235 END IF;
1236 END ValidateGLDate;
1237
1238 --------------------------
1239 -- Bug: 6356657
1240 --------------------------
1241 -------------------------------------------------------------------------------------------------
1242 PROCEDURE UpdateDistsWithReceiptInfo(p_report_header_id IN NUMBER, p_debug_switch IN VARCHAR2) IS
1243 -------------------------------------------------------------------------------------------------
1244
1245 CURSOR c_report_lines_dists(l_report_header_id IN NUMBER) IS
1246 SELECT xl.report_line_id, xl.currency_code,
1247 xl.receipt_currency_code,
1248 xl.receipt_conversion_rate,
1249 xl.receipt_currency_amount,xd.amount ,
1250 xd.report_distribution_id
1251 FROM ap_expense_report_lines xl,
1252 ap_exp_report_dists xd
1253 WHERE xd.report_line_id = xl.report_line_id
1254 and xl.report_header_id = l_report_header_id
1255 and xd.report_header_id = l_report_header_id
1256 and xd.receipt_currency_amount is null
1257 order by xd.report_line_id, xd.report_distribution_id;
1258
1259 l_report_line_id NUMBER;
1260 l_ln_receipt_curr_amt NUMBER;
1261 l_dist_amount NUMBER;
1262 l_line_currency_code VARCHAR2(15);
1263 l_receipt_currency_code VARCHAR2(15);
1264 l_receipt_conversion_rate NUMBER;
1265 l_report_distribution_id NUMBER;
1266 l_dist_rec_curr_amt NUMBER := 0;
1267 l_total_dist_rec_curr_amt NUMBER := 0;
1268 l_prev_line_id NUMBER := 0;
1269 l_prev_dist_id NUMBER := 0;
1270 l_prev_ln_receipt_curr_amt NUMBER := 0;
1271 l_debug_info VARCHAR2(2000);
1272
1273 BEGIN
1274 ------------------------------------------------------------
1275 l_debug_info := 'Start UpdateDistsWithReceiptInfo';
1276 ------------------------------------------------------------
1277 OPEN c_report_lines_dists(p_report_header_id);
1278 LOOP
1279 FETCH c_report_lines_dists INTO l_report_line_id,l_line_currency_code,
1280 l_receipt_currency_code, l_receipt_conversion_rate, l_ln_receipt_curr_amt, l_dist_amount,
1281 l_report_distribution_id;
1282 EXIT WHEN c_report_lines_dists%NOTFOUND;
1283
1284 BEGIN
1285 --------------------------------------------------------------------
1286 -- When the line changes update the last distribution of the previous
1287 -- line with the reminder.
1288 --------------------------------------------------------------------
1289 IF (l_prev_line_id <> 0 AND l_prev_line_id <> l_report_line_id) THEN
1290 IF ( l_prev_ln_receipt_curr_amt - l_total_dist_rec_curr_amt <> 0 ) THEN
1291 l_dist_rec_curr_amt := l_dist_rec_curr_amt + (l_prev_ln_receipt_curr_amt - l_total_dist_rec_curr_amt);
1292 -- Bug: 8408909, Donot update currency code and rate on line change
1293 update ap_exp_report_dists set
1294 receipt_currency_amount = l_dist_rec_curr_amt
1295 where report_distribution_id = l_prev_dist_id;
1296 END IF;
1297 l_total_dist_rec_curr_amt := 0;
1298 END IF;
1299 l_prev_ln_receipt_curr_amt := l_ln_receipt_curr_amt;
1300 l_prev_line_id := l_report_line_id;
1301 IF l_line_currency_code <> l_receipt_currency_code THEN
1302 l_dist_rec_curr_amt := l_dist_amount / l_receipt_conversion_rate;
1303
1304 ELSE
1305 l_dist_rec_curr_amt := l_dist_amount;
1306 END IF;
1307 l_dist_rec_curr_amt := ap_utilities_pkg.ap_round_currency(l_dist_rec_curr_amt,l_receipt_currency_code);
1308 l_total_dist_rec_curr_amt := l_total_dist_rec_curr_amt + l_dist_rec_curr_amt;
1309 l_prev_dist_id := l_report_distribution_id;
1310
1311 update ap_exp_report_dists set
1312 receipt_currency_amount = l_dist_rec_curr_amt,
1313 receipt_currency_code = l_receipt_currency_code,
1314 receipt_conversion_rate = l_receipt_conversion_rate
1315 where report_distribution_id = l_report_distribution_id;
1316 END;
1317
1318 END LOOP;
1319
1320 -------------------------------------------------------------------
1321 -- To the last distribution, add the difference amount if any left
1322 -------------------------------------------------------------------
1323 IF ( l_ln_receipt_curr_amt - l_total_dist_rec_curr_amt <> 0 ) THEN
1324 l_dist_rec_curr_amt := l_dist_rec_curr_amt + (l_ln_receipt_curr_amt - l_total_dist_rec_curr_amt);
1325 -- Bug: 8408909
1326 update ap_exp_report_dists set
1327 receipt_currency_amount = l_dist_rec_curr_amt
1328 where report_distribution_id = l_prev_dist_id;
1329 END IF;
1330
1331 close c_report_lines_dists;
1332
1333 ------------------------------------------------------------
1334 l_debug_info := 'End UpdateDistsWithReceiptInfo';
1335 ------------------------------------------------------------
1336 IF g_debug_switch = 'Y' THEN
1337 fnd_file.put_line(fnd_file.log, l_debug_info);
1338 END IF;
1339
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 ------------------------------------------------------------
1343 l_debug_info := 'Exception in UpdateDistsWithReceiptInfo' || SQLERRM;
1344 ------------------------------------------------------------
1345 IF g_debug_switch = 'Y' THEN
1346 fnd_file.put_line(fnd_file.log, l_debug_info);
1347 END IF;
1348 END UpdateDistsWithReceiptInfo;
1349
1350 ------------------------------------------------------------------------
1351 PROCEDURE ExportERtoAP(errbuf OUT NOCOPY VARCHAR2,
1352 retcode OUT NOCOPY NUMBER,
1353 p_batch_name IN VARCHAR2,
1354 p_source IN VARCHAR2,
1355 p_transfer_flag IN VARCHAR2,
1356 p_gl_date IN VARCHAR2,
1357 p_group_id IN VARCHAR2,
1358 p_commit_cycles IN NUMBER,
1359 p_debug_switch IN VARCHAR2,
1360 p_org_id IN NUMBER,
1361 p_role_name IN VARCHAR2,
1362 p_transfer_attachments IN VARCHAR2) IS
1363 ------------------------------------------------------------------------
1364
1365 CURSOR c_system_params(l_org_id IN NUMBER) IS
1366 SELECT employee_terms_id,
1367 base_currency_code,
1368 sp.set_of_books_id,
1369 fp.non_recoverable_tax_flag,
1370 nvl(sp.inv_doc_category_override, 'N'),
1371 sp.gl_date_from_receipt_flag,
1372 fp.expense_check_address_flag,
1373 f.minimum_accountable_unit,
1374 f.precision,
1375 sp.employee_pay_group_lookup_code,
1376 sp.employee_terms_id,
1377 sp.apply_advances_default
1378 FROM ap_system_parameters_all sp,
1379 financials_system_parameters fp,
1380 fnd_currencies f
1381 WHERE sp.base_currency_code = f.currency_code
1382 AND sp.org_id = l_org_id;
1383
1384 CURSOR c_successful_invoices(l_request_id IN NUMBER) IS
1385 SELECT ai.invoice_id, aerh.report_header_id,
1386 aerh.advance_invoice_to_apply, aerh.maximum_amount_to_apply
1387 FROM ap_expense_report_headers_all aerh, ap_invoices_all ai
1388 WHERE ai.APPLICATION_ID = 200
1389 AND ai.PRODUCT_TABLE = 'AP_EXPENSE_REPORT_HEADERS_ALL'
1390 AND ai.REFERENCE_KEY1 = aerh.report_header_id
1391 AND aerh.invoice_num = ai.invoice_num
1392 AND aerh.request_id = l_request_id
1393 AND aerh.vouchno = 0;
1394
1395 CURSOR c_rejected_invoices(l_request_id IN NUMBER) IS
1396 SELECT to_number(aii.reference_key1) report_header_id,
1397 reject_lookup_code,
1398 aii.invoice_id
1399 FROM ap_interface_rejections air, ap_invoices_interface aii
1400 WHERE air.parent_table = 'AP_INVOICES_INTERFACE'
1401 AND air.parent_id = aii.invoice_id
1402 AND aii.request_id = l_request_id
1403 UNION ALL
1404 SELECT to_number(aii.reference_key1) report_header_id,
1405 reject_lookup_code,
1406 aii.invoice_id
1407 FROM ap_interface_rejections air,
1408 ap_invoices_interface aii,
1409 ap_invoice_lines_interface aili
1410 WHERE air.parent_table = 'AP_INVOICE_LINES_INTERFACE'
1411 AND air.parent_id = aili.invoice_line_id
1412 AND aii.invoice_id = aili.invoice_id
1413 AND aii.request_id = l_request_id;
1414
1415 -- Criteria for this cursor is:
1416 -- Expense status code should not be 'ERROR' or 'PEND_HOLDS_CLEARANCE' or
1417 -- 'HOLD_PENDING_RECEIPTS'
1418 -- Vouchno = 0
1419 -- XH.hold_lookup_code is null
1420 CURSOR c_expenses_to_import(p_source IN VARCHAR2) IS
1421 SELECT XH.report_header_id report_header_id,
1422 nvl(emps.employee_id, -1) employee_id,
1423 emps.employee_num employee_number,
1424 XH.week_end_date week_end_date,
1425 nvl(XH.invoice_num, '') invoice_num,
1426 to_char(ap_utilities_pkg.ap_round_currency(XH.total,
1427 XH.default_currency_code)) total,
1428 nvl(XH.description, '') description,
1429 substrb(rtrim(emps.last_name || ', ' || emps.first_name ||
1430 DECODE(people.middle_names, null, '', ' ') ||
1431 people.middle_names),
1432 1,
1433 240) name,
1434 nvl(locs.location_code, '') location_code,
1435 locs.address_line_1 address_line_1,
1436 locs.address_line_2 address_line_2,
1437 locs.address_line_3 address_line_3,
1438 locs.town_or_city city,
1439 decode(locs.STYLE,
1440 'CA',
1441 '',
1442 'CA_GLB',
1443 '',
1444 nvl(locs.region_2, '')) state,
1445 locs.postal_code postal_code,
1446 decode(locs.STYLE,
1447 'US',
1448 '',
1449 'US_GLB',
1450 '',
1451 'IE',
1452 '',
1453 'IE_GLB',
1454 '',
1455 'GB',
1456 '',
1457 'CA',
1458 nvl(locs.REGION_1, ''),
1459 'JP',
1460 nvl(locs.REGION_1, ''),
1461 nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
1462 locs.REGION_1),
1463 '')) province,
1464 decode(locs.STYLE,
1465 'US',
1466 nvl(locs.REGION_1, ''),
1467 'US_GLB',
1468 nvl(locs.REGION_1, ''),
1469 'IE',
1470 nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
1471 locs.REGION_1),
1472 ''),
1473 'IE_GLB',
1474 nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
1475 locs.REGION_1),
1476 ''),
1477 'GB',
1478 nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
1479 locs.REGION_1),
1480 ''),
1481 '') county,
1482 locs.country,
1483 nvl(V.vendor_id, -1) vendor_id,
1484 nvl(XH.vendor_id, -1) header_vendor_id,
1485 --nvl(XH.hold_lookup_code, '') hold_lookup_code,
1486 --nvl(l1.displayed_field, '') nls_hold_code,
1487 --l1.description hold_description,
1488 XH.created_by created_by,
1489 XH.default_currency_code default_currency_code,
1490 nvl(XH.default_exchange_rate_type, '') default_exchange_rate_type,
1491 nvl(XH.default_exchange_rate,-1) default_exchange_rate,
1492 nvl(to_char(XH.default_exchange_date), '') default_exchange_date,
1493 nvl(XH.accts_pay_code_combination_id, -1) accts_pay_ccid,
1494 XH.set_of_books_id set_of_books_id,
1495 XH.accounting_date accounting_date,
1496 nvl(XH.vendor_site_id, -1) header_vendor_site_id,
1497 nvl(XH.apply_advances_default, 'N') apply_advances_flag,
1498 nvl(XH.advance_invoice_to_apply, -1) advance_invoice_to_apply,
1499 to_char(nvl(XH.maximum_amount_to_apply, XH.amt_due_employee)) amount_want_to_apply,
1500 XH.expense_check_address_flag home_or_office,
1501 nvl(emps.employee_id, -1) current_emp_id,
1502 XH.voucher_num voucher_num,
1503 '' base_amount,
1504 nvl(XH.doc_category_code, '') doc_category_code,
1505 nvl(XH.reference_1, '') reference_1,
1506 XH.reference_2 reference_2,
1507 nvl(to_char(XH.awt_group_id), '') awt_group_id,
1508 XH.global_attribute1,
1509 XH.global_attribute2,
1510 XH.global_attribute3,
1511 XH.global_attribute4,
1512 XH.global_attribute5,
1513 XH.global_attribute6,
1514 XH.global_attribute7,
1515 XH.global_attribute8,
1516 XH.global_attribute9,
1517 XH.global_attribute10,
1518 XH.global_attribute11,
1519 XH.global_attribute12,
1520 XH.global_attribute13,
1521 XH.global_attribute14,
1522 XH.global_attribute15,
1523 XH.global_attribute16,
1524 XH.global_attribute17,
1525 XH.global_attribute18,
1526 XH.global_attribute19,
1527 XH.global_attribute20,
1528 XH.global_attribute_category,
1529 nvl(decode(p_transfer_flag, 'Y', XH.attribute1), '') attribute1,
1530 nvl(decode(p_transfer_flag, 'Y', XH.attribute2), '') attribute2,
1531 nvl(decode(p_transfer_flag, 'Y', XH.attribute3), '') attribute3,
1532 nvl(decode(p_transfer_flag, 'Y', XH.attribute4), '') attribute4,
1533 nvl(decode(p_transfer_flag, 'Y', XH.attribute5), '') attribute5,
1534 nvl(decode(p_transfer_flag, 'Y', XH.attribute6), '') attribute6,
1535 nvl(decode(p_transfer_flag, 'Y', XH.attribute7), '') attribute7,
1536 nvl(decode(p_transfer_flag, 'Y', XH.attribute8), '') attribute8,
1537 nvl(decode(p_transfer_flag, 'Y', XH.attribute9), '') attribute9,
1538 nvl(decode(p_transfer_flag, 'Y', XH.attribute10), '') attribute10,
1539 nvl(decode(p_transfer_flag, 'Y', XH.attribute11), '') attribute11,
1540 nvl(decode(p_transfer_flag, 'Y', XH.attribute12), '') attribute12,
1541 nvl(decode(p_transfer_flag, 'Y', XH.attribute13), '') attribute13,
1542 nvl(decode(p_transfer_flag, 'Y', XH.attribute14), '') attribute14,
1543 nvl(decode(p_transfer_flag, 'Y', XH.attribute15), '') attribute15,
1544 nvl(decode(p_transfer_flag, 'Y', XH.attribute_category), '') attribute_category,
1545 nvl(XH.payment_currency_code, XH.default_currency_code) payment_currency_code,
1546 nvl(XH.payment_cross_rate_type, '') payment_cross_rate_type,
1547 nvl(XH.payment_cross_rate_date, XH.week_end_date) payment_cross_rate_date,
1548 nvl(XH.payment_cross_rate, 1) payment_cross_rate,
1549 nvl(XH.prepay_num, '') prepay_num,
1550 nvl(XH.prepay_dist_num, '') prepay_dist_num,
1551 nvl(to_char(XH.prepay_gl_date), '') prepay_gl_date,
1552 nvl(xh.paid_on_behalf_employee_id, '') paid_on_behalf_employee_id,
1553 to_char(nvl(xh.amt_due_employee, to_char(0))) amt_due_employee,
1554 to_char(nvl(xh.amt_due_ccard_company, to_char(0))) amt_due_ccard_company,
1555 substrb(rtrim(decode(people.per_information18,
1556 null,
1557 decode(people.per_information19,
1558 null,
1559 null,
1560 people.per_information19),
1561 people.per_information18 || ', ' ||
1562 people.per_information19)),
1563 1,
1564 240) per_information18_19,
1565 people.per_information_category per_information_category,
1566 XH.source source,
1567 p_group_id group_id,
1568 locs.style style,
1569 XH.org_id org_id,
1570 '' invoice_id,
1571 '' invoice_type_lookup_code,
1572 '' gl_date,
1573 '' alternate_name,
1574 '' amount_app_to_discount,
1575 V.payment_method_lookup_code,
1576 emps.is_contingent
1577 FROM ap_expense_report_headers XH,
1578 hr_locations locs,
1579 per_all_people_f people,
1580 (SELECT
1581 h.employee_id,
1582 h.full_name,
1583 h.employee_num,
1584 h.organization_id,
1585 h.last_name,
1586 h.first_name,
1587 h.business_group_id,
1588 h.location_id,
1589 'N' is_contingent
1590 FROM per_employees_x h
1591 WHERE AP_WEB_DB_HR_INT_PKG.isPersonCwk(h.employee_id)='N'
1592 UNION ALL
1593 SELECT
1594 h.person_id employee_id,
1595 h.full_name,
1596 h.npw_number employee_num,
1597 h.organization_id,
1598 h.last_name,
1599 h.first_name,
1600 h.business_group_id,
1601 h.location_id,
1602 'Y' is_contingent
1603 FROM PER_CONT_WORKERS_CURRENT_X h) emps,
1604 ap_suppliers V
1605 --ap_lookup_codes l1
1606 WHERE vouchno = 0
1607 AND XH.employee_id = V.employee_id(+)
1608 AND XH.employee_id = emps.employee_id(+)
1609 AND (trunc(sysdate) between people.effective_start_date(+) AND
1610 people.effective_end_date(+))
1611 AND ((emps.business_group_id IS NULL) OR
1612 (emps.business_group_id in
1613 (SELECT nvl(FSP.business_group_id, 0)
1614 FROM financials_system_parameters FSP)))
1615 AND emps.employee_id = people.person_id(+)
1616 AND emps.location_id = locs.location_id(+)
1617 AND decode(XH.source,
1618 'CREDIT CARD',
1619 'SelfService',
1620 'Both Pay',
1621 'SelfService',
1622 XH.source) = p_source
1623 AND NVL(XH.expense_status_code, 'NO ERROR') not IN
1624 ('ERROR', 'PEND_HOLDS_CLEARANCE', 'HOLD_PENDING_RECEIPTS')
1625 AND XH.hold_lookup_code is null
1626 --AND l1.lookup_type(+) = 'HOLD CODE'
1627 --AND l1.lookup_code(+) = XH.hold_lookup_code
1628 AND ((XH.org_id IS NOT NULL AND
1629 p_org_id IS NOT NULL AND
1630 XH.org_id = p_org_id)
1631 OR (p_org_id IS NULL AND
1632 XH.org_id IS NOT NULL AND
1633 (mo_global.check_access(XH.org_id)= 'Y'))
1634 OR (p_org_id is NOT NULL AND XH.org_id IS NULL)
1635 OR (p_org_id is NULL AND XH.org_id IS NULL))
1636 AND EXISTS
1637 (SELECT 'Y'
1638 FROM AP_EXPENSE_REPORT_LINES XL
1639 WHERE XH.REPORT_HEADER_ID = XL.REPORT_HEADER_ID)
1640 ORDER BY UPPER(emps.last_name) desc,
1641 UPPER(emps.first_name) desc,
1642 UPPER(people.middle_names) desc,
1643 total,
1644 week_end_date desc;
1645 --FOR UPDATE OF XH.report_header_id NOWAIT;
1646
1647 l_batch_control_flag VARCHAR2(10);
1648 l_batch_id NUMBER;
1649 l_batch_name VARCHAR2(50);
1650 l_debug_info VARCHAR2(2000);
1651 batch_failure EXCEPTION;
1652 validation_failed EXCEPTION;
1653 l_employee_terms_id ap_system_parameters.employee_terms_id%TYPE;
1654 l_base_currency ap_system_parameters.base_currency_code%TYPE;
1655 l_set_of_books_id ap_system_parameters.set_of_books_id%TYPE;
1656 l_enable_recoverable_flag financials_system_parameters.non_recoverable_tax_flag%TYPE;
1657 l_doc_category_override ap_system_parameters.inv_doc_category_override%TYPE;
1658 l_gl_date_flag ap_system_parameters.gl_date_from_receipt_flag%TYPE;
1659 l_address_flag financials_system_parameters.expense_check_address_flag%TYPE;
1660 l_min_accountable_unit NUMBER;
1661 l_precision NUMBER;
1662 vendor_valid_flag VARCHAR2(2);
1663 vendor_site_valid_flag VARCHAR2(2);
1664 l_payment_due_from VARCHAR2(15);
1665 l_invoice_rec InvoiceInfoRecType;
1666 l_invoice_lines_rec_tab InvoiceLinesRecTabType;
1667 l_request_id NUMBER;
1668 l_reject_code ap_expense_report_headers.reject_code%TYPE;
1669 l_total ap_expense_report_headers.total%TYPE;
1670 l_invoices_fetched NUMBER := 0;
1671 l_invoices_created NUMBER := 0;
1672 l_cc_invoices_fetched NUMBER := 0;
1673 l_cc_invoices_created NUMBER := 0;
1674 l_total_invoice_amount NUMBER;
1675 l_print_batch VARCHAR2(5);
1676 l_batch_error_flag VARCHAR2(5);
1677 l_calling_sequence VARCHAR2(30) := 'Expense Report Export';
1678 l_failed_open_interface NUMBER := 0;
1679 l_last_updated_by NUMBER;
1680 l_description VARCHAR2(300);
1681 l_seq_profile VARCHAR2(2);
1682 l_rows_to_import NUMBER := 0;
1683 l_expenses_fetched NUMBER := 0;
1684 l_org_id NUMBER;
1685 l_emp_pg_lookup_code ap_system_parameters.employee_pay_group_lookup_code%TYPE;
1686 l_emp_terms_id ap_system_parameters.employee_terms_id%TYPE;
1687 l_sys_apply_advances_flag ap_system_parameters.apply_advances_default%TYPE;
1688
1689 TYPE ReportHeaderIdType IS TABLE OF ap_expense_report_headers.report_header_id%TYPE
1690 INDEX BY BINARY_INTEGER;
1691 TYPE RejectCodeType IS TABLE OF ap_interface_rejections.reject_lookup_code%TYPE
1692 INDEX BY BINARY_INTEGER;
1693 TYPE InvoiceIdType IS TABLE OF ap_invoices_interface.invoice_id%TYPE
1694 INDEX BY BINARY_INTEGER;
1695 TYPE AdvAppliedType IS TABLE OF ap_expense_report_headers.maximum_amount_to_apply%TYPE
1696 INDEX BY BINARY_INTEGER;
1697
1698 l_report_header_id_list ReportHeaderIdType;
1699 l_reject_code_list RejectCodeType;
1700 l_invoice_id_list InvoiceIdType;
1701 l_vendor_rec VendorInfoRecType;
1702 l_oie_applied_prepay_list InvoiceIdType;
1703 l_oie_applied_amt_list AdvAppliedType;
1704
1705 l_expense_status_code ap_expense_report_headers_all.expense_status_code%TYPE;
1706 l_actual_adv_applied NUMBER;
1707 x_return_status VARCHAR2(4000);
1708 x_msg_count NUMBER;
1709 x_msg_data VARCHAR2(4000);
1710 l_rejection_list AP_IMPORT_INVOICES_PKG.rejection_tab_type;
1711 l_inv_total_amount NUMBER;
1712 l_payment_due_frm VARCHAR2(15);
1713 l_is_active_employee VARCHAR2(2);
1714 l_trx_attributes iby_disbursement_comp_pub.Trxn_Attributes_Rec_Type;
1715 l_result_pmt_attributes iby_disbursement_comp_pub.Default_Pmt_Attrs_Rec_Type;
1716 l_return_status varchar2(30);
1717 l_msg_count number;
1718 l_msg_data varchar2(2000);
1719 l_le_id number;
1720 l_available_prepays NUMBER;
1721 l_gl_period_status varchar2(2);
1722 l_period_year varchar2(5);
1723 l_new_gl_date ap_expense_report_headers_all.week_end_date%TYPE;
1724
1725
1726 BEGIN
1727
1728 g_debug_switch := p_debug_switch;
1729 g_last_updated_by := to_number(FND_GLOBAL.USER_ID);
1730 g_last_update_login := to_number(FND_GLOBAL.LOGIN_ID);
1731
1732 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1733
1734 FND_PROFILE.GET('AP_USE_INV_BATCH_CONTROLS', l_batch_control_flag);
1735
1736 l_batch_name := p_batch_name;
1737 IF l_batch_name = 'N/A' THEN
1738 l_batch_name := null;
1739 END IF;
1740
1741 IF (l_batch_control_flag <> 'Y') THEN
1742 --Bug#8352220: If the batch control is set to "No" then AP doesnot do any batch related processing.
1743 l_batch_name := NULL;
1744 END IF;
1745
1746 ------------------------------------------------------------
1747 l_debug_info := 'Batch Name = ' || l_batch_name;
1748 ------------------------------------------------------------
1749 IF g_debug_switch = 'Y' THEN
1750 fnd_file.put_line(fnd_file.log, l_debug_info);
1751 END IF;
1752
1753 ------------------------------------------------------------
1754 l_debug_info := 'Begin Receipts Management - Holds';
1755 ------------------------------------------------------------
1756 fnd_file.put_line(fnd_file.log, l_debug_info);
1757
1758 AP_WEB_HOLDS_WF.ExpenseHolds;
1759
1760 ------------------------------------------------------------
1761 l_debug_info := 'End Receipts Management - Holds';
1762 ------------------------------------------------------------
1763 IF g_debug_switch = 'Y' THEN
1764 fnd_file.put_line(fnd_file.log, l_debug_info);
1765 END IF;
1766
1767 ------------------------------------------------------------
1768 l_debug_info := 'Begin Processing Individual expense reports';
1769 ------------------------------------------------------------
1770 fnd_file.put_line(fnd_file.log, l_debug_info);
1771
1772 OPEN c_expenses_to_import(p_source);
1773
1774 LOOP
1775
1776 ------------------------------------------------------------
1777 l_debug_info := 'Fetching expense report...';
1778 ------------------------------------------------------------
1779 fnd_file.put_line(fnd_file.log, l_debug_info);
1780
1781 FETCH c_expenses_to_import
1782 INTO l_invoice_rec;
1783
1784 EXIT WHEN c_expenses_to_import%NOTFOUND;
1785
1786 BEGIN
1787
1788
1789 ------------------------------------------------------------
1790 l_debug_info := 'Updating the Dists with Receipt Info...';
1791 ------------------------------------------------------------
1792 UpdateDistsWithReceiptInfo(l_invoice_rec.report_header_id, g_debug_switch);
1793
1794
1795 l_expenses_fetched := l_expenses_fetched + 1;
1796 l_reject_code := NULL;
1797
1798 fnd_file.put_line(fnd_file.log,
1799 'Expense Report Number : **'||l_invoice_rec.invoice_num||'**');
1800
1801 --Bug#2823530
1802 l_debug_info := 'Transfer Attachments option(Y/N):'||p_transfer_attachments;
1803 fnd_file.put_line(fnd_file.log, l_debug_info);
1804
1805 IF p_transfer_attachments = 'Y' THEN
1806 l_debug_info := 'Validating the Attachment Categories';
1807 IF g_debug_switch = 'Y' THEN
1808 fnd_file.put_line(fnd_file.log, l_debug_info);
1809 END IF;
1810
1811 ValidateAttachCategory(l_invoice_rec.report_header_id,l_reject_code);
1812 IF (l_reject_code IS NOT NULL) THEN
1813 raise validation_failed;
1814 END IF;
1815
1816 END IF;
1817
1818 l_debug_info := 'Validate Amounts';
1819 fnd_file.put_line(fnd_file.log, l_debug_info);
1820 ValidateAmounts(l_invoice_rec.report_header_id,l_reject_code);
1821 IF (l_reject_code IS NOT NULL) THEN
1822 raise validation_failed;
1823 END IF;
1824
1825 --Bug#2823530
1826
1827 IF l_invoice_rec.org_id <> NVL(l_org_id, -3115) THEN
1828
1829 l_org_id := nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL));
1830
1831 ------------------------------------------------------------
1832 l_debug_info := 'Get the system parameters';
1833 ------------------------------------------------------------
1834 IF g_debug_switch = 'Y' THEN
1835 fnd_file.put_line(fnd_file.log, l_debug_info);
1836 END IF;
1837
1838 OPEN c_system_params(l_org_id);
1839 FETCH c_system_params
1840 INTO l_employee_terms_id,
1841 l_base_currency,
1842 l_set_of_books_id,
1843 l_enable_recoverable_flag,
1844 l_doc_category_override,
1845 l_gl_date_flag,
1846 l_address_flag,
1847 l_min_accountable_unit,
1848 l_precision,
1849 l_emp_pg_lookup_code,
1850 l_emp_terms_id,
1851 l_sys_apply_advances_flag;
1852 CLOSE c_system_params;
1853 END IF;
1854
1855
1856 IF l_employee_terms_id < 0 THEN
1857 ---------------------------------------------------------------------------
1858 l_debug_info := 'employee terms id is < 0 in system parameters. Aborting.';
1859 ---------------------------------------------------------------------------
1860 fnd_file.put_line(fnd_file.log, l_debug_info);
1861
1862 raise batch_failure;
1863 END IF;
1864
1865 IF l_invoice_rec.header_vendor_id <> -1 THEN
1866 ----------------------------------
1867 l_debug_info := 'Validate Vendor';
1868 ----------------------------------
1869 IF g_debug_switch = 'Y' THEN
1870 fnd_file.put_line(fnd_file.log, l_debug_info);
1871 END IF;
1872
1873 vendor_valid_flag := AP_WEB_DB_PO_INT_PKG.IsVendorValid(l_invoice_rec.header_vendor_id);
1874
1875 IF vendor_valid_flag = 'N' THEN
1876 ---------------------------------------------------------------
1877 l_debug_info := 'Inactive vendor *' ||
1878 to_char(l_invoice_rec.header_vendor_id) || '*';
1879 ---------------------------------------------------------------
1880 fnd_file.put_line(fnd_file.log, l_debug_info);
1881
1882 l_reject_code := 'Inactive vendor';
1883 raise validation_failed;
1884 END IF;
1885
1886 END IF;
1887
1888 IF l_invoice_rec.source not IN ('XpenseXpress', 'SelfService',
1889 'Oracle Project Accounting') then
1890 l_invoice_rec.vendor_id := l_invoice_rec.header_vendor_id;
1891 END IF;
1892
1893 IF l_invoice_rec.is_contingent = 'Y' THEN
1894 -------------------------------------------------------------------
1895 l_debug_info := 'Validate Site if employee is a contingent worker';
1896 -------------------------------------------------------------------
1897 IF g_debug_switch = 'Y' THEN
1898 fnd_file.put_line(fnd_file.log, l_debug_info);
1899 END IF;
1900
1901 IF l_invoice_rec.header_vendor_site_id is NULL THEN
1902 -------------------------------------
1903 l_debug_info := 'NULL vendor site *';
1904 -------------------------------------
1905 fnd_file.put_line(fnd_file.log, l_debug_info);
1906
1907 l_reject_code := 'Inactive site';
1908 raise validation_failed;
1909 END IF;
1910
1911 l_invoice_rec.vendor_id := l_invoice_rec.header_vendor_id;
1912
1913 vendor_site_valid_flag := AP_WEB_DB_PO_INT_PKG.IsVendorSiteValid(
1914 l_invoice_rec.header_vendor_site_id);
1915
1916 IF vendor_site_valid_flag = 'N' THEN
1917 --------------------------------------------------------------------
1918 l_debug_info := 'Inactive site *' ||
1919 to_char(l_invoice_rec.header_vendor_site_id) || '*';
1920 --------------------------------------------------------------------
1921 fnd_file.put_line(fnd_file.log, l_debug_info);
1922
1923 l_reject_code := 'Inactive site';
1924 raise validation_failed;
1925 END IF;
1926
1927 --------------------------------------------------------------------
1928 l_debug_info := 'Set the description field for a contingent worker';
1929 --------------------------------------------------------------------
1930 IF g_debug_switch = 'Y' THEN
1931 fnd_file.put_line(fnd_file.log, l_debug_info);
1932 END IF;
1933
1934 FND_MESSAGE.SET_NAME('SQLAP', 'OIE_REMITTANCE_DESC');
1935 FND_MESSAGE.Set_Token('PAID_ON_BEHALF_OF', l_invoice_rec.name);
1936 FND_MESSAGE.Set_Token('INVOICE_DESCRIPTION',
1937 l_invoice_rec.description);
1938 l_description := FND_MESSAGE.GET;
1939
1940 l_invoice_rec.description := substrb(l_description, 1, 240);
1941
1942 END IF;
1943
1944 IF l_invoice_rec.source = 'Oracle Project Accounting' then
1945
1946 ---------------------------------------------------
1947 l_debug_info := 'Validation for Project Expenses ';
1948 ---------------------------------------------------
1949 IF g_debug_switch = 'Y' THEN
1950 fnd_file.put_line(fnd_file.log, l_debug_info);
1951 END IF;
1952
1953 IF l_invoice_rec.employee_id = -1 THEN
1954 l_reject_code := 'Invalid employee';
1955 raise validation_failed;
1956 END IF;
1957
1958 IF l_invoice_rec.set_of_books_id <> l_set_of_books_id THEN
1959 --------------------------------------------------------------
1960 l_debug_info := 'Invalid SOB *' ||
1961 to_char(l_invoice_rec.set_of_books_id) || '*';
1962 --------------------------------------------------------------
1963 fnd_file.put_line(fnd_file.log, l_debug_info);
1964
1965 l_reject_code := 'Invalid set of books';
1966 raise validation_failed;
1967 END IF;
1968
1969 END IF;
1970
1971 -------------------------------------------------
1972 l_debug_info := 'Validate/Set the doc sequence';
1973 -------------------------------------------------
1974 IF g_debug_switch = 'Y' THEN
1975 fnd_file.put_line(fnd_file.log, l_debug_info);
1976 END IF;
1977
1978 FND_PROFILE.GET('UNIQUE:SEQ_NUMBERS', l_seq_profile);
1979 -------------------------------------------------
1980 l_debug_info := 'Sequence profile is: '||l_seq_profile;
1981 -------------------------------------------------
1982
1983 IF l_seq_profile IN ('A', 'P') THEN
1984 IF ( (l_invoice_rec.doc_category_code IS NULL) AND
1985 (l_doc_category_override = 'Y') ) THEN
1986 IF l_invoice_rec.source IN ('XpenseXpress', 'SelfService',
1987 'Oracle Project Accounting') THEN
1988 l_invoice_rec.doc_category_code := 'EXP REP INV';
1989 ELSIF l_invoice_rec.source IN ('Both Pay', 'CREDIT CARD') THEN
1990 l_invoice_rec.doc_category_code := 'PAY REQ INV';
1991 END IF;
1992 END IF;
1993 END IF;
1994
1995 /* Bug#8464009 - removed Doc Category Code validation in 120.21.12000000.43 as the validation is
1996 done by AP in "AP_IMPORT_UTILITIES_PKG.get_doc_sequence"
1997 */
1998
1999 ----------------------------------
2000 l_debug_info := 'Set the GL Date';
2001 ----------------------------------
2002 IF g_debug_switch = 'Y' THEN
2003 fnd_file.put_line(fnd_file.log, l_debug_info);
2004 END IF;
2005
2006 IF l_gl_date_flag IN ('I', 'N') THEN
2007 l_invoice_rec.gl_date := l_invoice_rec.week_end_date;
2008 ELSE
2009 l_invoice_rec.gl_date := sysdate;
2010 END IF;
2011
2012 IF l_invoice_rec.source NOT IN
2013 ('XpenseXpress', 'SelfService', 'Both Pay', 'CREDIT CARD') THEN
2014 IF l_invoice_rec.accounting_date IS NOT NULL THEN
2015 l_invoice_rec.gl_date := l_invoice_rec.accounting_date;
2016 END IF;
2017 ELSE
2018 IF p_gl_date IS NOT NULL THEN
2019 l_invoice_rec.gl_date := fnd_date.canonical_to_date(p_gl_date);
2020 END IF;
2021 END IF;
2022
2023 ----------------------------------
2024 l_debug_info := 'Checking GL Period status for the set Invoice GL Date: ' || l_invoice_rec.gl_date;
2025 ----------------------------------
2026 IF g_debug_switch = 'Y' THEN
2027 fnd_file.put_line(fnd_file.log, l_debug_info);
2028 END IF;
2029
2030 ValidateGLDate(l_invoice_rec.gl_date,
2031 null,
2032 'Invoice',
2033 l_invoice_rec.set_of_books_id,
2034 l_new_gl_date,
2035 l_reject_code);
2036 IF (l_reject_code IS NOT NULL) THEN
2037 raise validation_failed;
2038 ELSE
2039 l_invoice_rec.gl_date := l_new_gl_date;
2040 END IF;
2041
2042 -------------------------------------------------------------------
2043 l_debug_info := 'Final Invoice GL Date: ' || l_invoice_rec.gl_date;
2044 -------------------------------------------------------------------
2045 IF g_debug_switch = 'Y' THEN
2046 fnd_file.put_line(fnd_file.log, l_debug_info);
2047 END IF;
2048
2049 IF (l_invoice_rec.source = 'XpenseXpress') THEN
2050 ----------------------------------
2051 l_debug_info := 'Checking GL Period status for the set Prepayment GL Date: ' || l_invoice_rec.prepay_gl_date;
2052 ----------------------------------
2053 IF g_debug_switch = 'Y' THEN
2054 fnd_file.put_line(fnd_file.log, l_debug_info);
2055 END IF;
2056
2057 ValidateGLDate(l_invoice_rec.prepay_gl_date,
2058 l_invoice_rec.gl_date,
2059 'Prepayment',
2060 l_invoice_rec.set_of_books_id,
2061 l_new_gl_date,
2062 l_reject_code);
2063 IF (l_reject_code IS NOT NULL) THEN
2064 raise validation_failed;
2065 ELSE
2066 l_invoice_rec.prepay_gl_date := l_new_gl_date;
2067 END IF;
2068
2069 -----------------------------------------------------------------------------
2070 l_debug_info := 'Final Prepayment GL Date: ' || l_invoice_rec.prepay_gl_date;
2071 -----------------------------------------------------------------------------
2072 IF g_debug_switch = 'Y' THEN
2073 fnd_file.put_line(fnd_file.log, l_debug_info);
2074 END IF;
2075 ELSE -- Bug#7278445 - Prepayment GL Date defaulting is done by AP
2076 -----------------------------------------------------------------------------
2077 l_debug_info := 'As source is not XpenseXpress Resetting Prepay GL date to Null since Prepay GL date defaulting is done by Payables';
2078 -----------------------------------------------------------------------------
2079 IF g_debug_switch = 'Y' THEN
2080 fnd_file.put_line(fnd_file.log, l_debug_info);
2081 END IF;
2082 l_invoice_rec.prepay_gl_date := NULL;
2083 END IF;
2084
2085 ----------------------------------------
2086 l_debug_info := 'Set the Exchange Rate';
2087 ----------------------------------------
2088 IF g_debug_switch = 'Y' THEN
2089 fnd_file.put_line(fnd_file.log, l_debug_info);
2090 END IF;
2091
2092 IF l_base_currency = l_invoice_rec.default_currency_code THEN
2093 l_invoice_rec.default_exchange_rate := -1;
2094 ELSIF
2095 gl_currency_api.is_fixed_rate(l_invoice_rec.default_currency_code,
2096 l_base_currency,
2097 nvl(l_invoice_rec.accounting_date, sysdate))
2098 = 'Y' THEN
2099 l_invoice_rec.default_exchange_rate := gl_currency_api.get_rate(
2100 l_invoice_rec.default_currency_code,
2101 l_base_currency,
2102 nvl(l_invoice_rec.accounting_date,
2103 sysdate));
2104 END IF;
2105
2106 ----------------------------------------
2107 l_debug_info := 'Set the Base Amount';
2108 ----------------------------------------
2109 IF g_debug_switch = 'Y' THEN
2110 fnd_file.put_line(fnd_file.log, l_debug_info);
2111 END IF;
2112
2113 IF l_base_currency = l_invoice_rec.default_currency_code THEN
2114 l_invoice_rec.base_amount := '';
2115 ELSE
2116 IF l_min_accountable_unit IS NULL THEN
2117 l_invoice_rec.base_amount := ROUND(l_invoice_rec.total *
2118 l_invoice_rec.default_exchange_rate,
2119 l_precision);
2120 ELSE
2121 l_invoice_rec.base_amount := ROUND(l_invoice_rec.total *
2122 l_invoice_rec.default_exchange_rate
2123 /l_min_accountable_unit) *
2124 l_min_accountable_unit ;
2125 END IF;
2126 END IF;
2127
2128
2129 ----------------------------------------
2130 l_debug_info := 'Set the Address flag';
2131 ----------------------------------------
2132 IF g_debug_switch = 'Y' THEN
2133 fnd_file.put_line(fnd_file.log, l_debug_info);
2134 END IF;
2135
2136 --Bug#7207375 - Allow payment of Expense Report to Temporary Address
2137 l_invoice_rec.home_or_office := nvl(l_invoice_rec.home_or_office,l_address_flag);
2138
2139 IF l_invoice_rec.home_or_office IS NOT NULL THEN
2140 IF l_invoice_rec.home_or_office in ('HOME', 'H') THEN
2141 l_invoice_rec.home_or_office := 'H';
2142 ELSIF l_invoice_rec.home_or_office in ('OFFICE', 'O') THEN
2143 l_invoice_rec.home_or_office := 'O';
2144 ELSIF l_invoice_rec.home_or_office in ('PROVISIONAL', 'P') THEN
2145 l_invoice_rec.home_or_office := 'P';
2146 END IF;
2147 END IF;
2148
2149 ---------------------------------------
2150 l_debug_info := 'Set the Invoice Type';
2151 ---------------------------------------
2152 IF g_debug_switch = 'Y' THEN
2153 fnd_file.put_line(fnd_file.log, l_debug_info);
2154 END IF;
2155
2156 IF l_invoice_rec.source not IN
2157 ('XpenseXpress', 'SelfService', 'Both Pay', 'CREDIT CARD',
2158 'Oracle Project Accounting') THEN
2159 IF l_invoice_rec.total < 0 THEN
2160 l_invoice_rec.invoice_type_lookup_code := 'CREDIT';
2161 ELSE
2162 l_invoice_rec.invoice_type_lookup_code := 'STANDARD';
2163 END IF;
2164
2165 ELSIF l_invoice_rec.source IN
2166 ('Both Pay', 'CREDIT CARD') THEN
2167 l_invoice_rec.invoice_type_lookup_code := 'PAYMENT REQUEST';
2168
2169 ELSE
2170 l_invoice_rec.invoice_type_lookup_code := 'EXPENSE REPORT';
2171 END IF;
2172
2173 IF l_invoice_rec.per_information_category = 'JP' THEN
2174 l_invoice_rec.name := l_invoice_rec.per_information18_19;
2175 l_invoice_rec.alternate_name := l_invoice_rec.name;
2176 END IF;
2177
2178 ---------------------------------------------------------------------
2179 l_debug_info := 'Call procedure to query and validate expense lines';
2180 ---------------------------------------------------------------------
2181 IF g_debug_switch = 'Y' THEN
2182 fnd_file.put_line(fnd_file.log, l_debug_info);
2183 END IF;
2184
2185 SELECT AP_INVOICES_INTERFACE_S.nextval
2186 INTO l_invoice_rec.invoice_id
2187 FROM DUAL;
2188
2189
2190 IF l_reject_code is NOT NULL THEN
2191 raise validation_failed;
2192 END IF;
2193
2194 ----------------------------------------------------------------
2195 l_debug_info := 'Get/validate the vendor_id and vendor_site_id';
2196 ----------------------------------------------------------------
2197 IF g_debug_switch = 'Y' THEN
2198 fnd_file.put_line(fnd_file.log, l_debug_info);
2199 END IF;
2200
2201 l_vendor_rec.vendor_id := l_invoice_rec.vendor_id;
2202 l_vendor_rec.vendor_site_id := l_invoice_rec.header_vendor_site_id;
2203 l_vendor_rec.home_or_office := l_invoice_rec.home_or_office;
2204 l_vendor_rec.employee_id := l_invoice_rec.employee_id;
2205 l_vendor_rec.vendor_name := l_invoice_rec.name;
2206 l_vendor_rec.org_id := l_invoice_rec.org_id;
2207 -- bug 5350423 - supplier creation should not pass address info
2208 --l_vendor_rec.address_line_1 := l_invoice_rec.address_line_1;
2209 --l_vendor_rec.address_line_2 := l_invoice_rec.address_line_2;
2210 --l_vendor_rec.address_line_3 := l_invoice_rec.address_line_3;
2211 --l_vendor_rec.city := l_invoice_rec.city;
2212 --l_vendor_rec.state := l_invoice_rec.state;
2213 --l_vendor_rec.postal_code := l_invoice_rec.postal_code;
2214 --l_vendor_rec.province := l_invoice_rec.province;
2215 --l_vendor_rec.county := l_invoice_rec.county;
2216 --l_vendor_rec.country := l_invoice_rec.country;
2217 --l_vendor_rec.style := l_invoice_rec.style;
2218 --Bug 5890829 set the pay group
2219 l_vendor_rec.pay_group := l_emp_pg_lookup_code;
2220 l_vendor_rec.terms_date_basis := null;
2221 l_vendor_rec.liab_acc := null;
2222 --Bug 5890829 set the payment terms
2223 l_vendor_rec.terms_id := l_emp_terms_id;
2224 l_vendor_rec.payment_priority := null;
2225 l_vendor_rec.prepay_ccid := null;
2226 l_vendor_rec.always_take_disc_flag := null;
2227 l_vendor_rec.pay_date_basis := null;
2228 l_vendor_rec.vendor_num := null;
2229 l_vendor_rec.allow_awt_flag := null;
2230 l_vendor_rec.party_id := null;
2231
2232
2233 ----------------------------------
2234 l_debug_info := 'Get Vendor Info';
2235 ----------------------------------
2236 IF g_debug_switch = 'Y' THEN
2237 fnd_file.put_line(fnd_file.log, l_debug_info);
2238 END IF;
2239
2240 IF NOT GetVendorInfo(l_vendor_rec,
2241 l_reject_code) THEN
2242
2243 raise validation_failed;
2244 END IF;
2245
2246 IF l_reject_code is NOT NULL THEN
2247 raise validation_failed;
2248 ELSE
2249 l_invoice_rec.vendor_id := l_vendor_rec.vendor_id;
2250 l_invoice_rec.header_vendor_site_id := l_vendor_rec.vendor_site_id;
2251 END IF;
2252
2253 -------------------------------------------
2254 l_debug_info := 'Get the Payment Scenario';
2255 -------------------------------------------
2256 IF g_debug_switch = 'Y' THEN
2257 fnd_file.put_line(fnd_file.log, l_debug_info);
2258 END IF;
2259
2260 l_payment_due_from := '';
2261
2262 IF (NOT
2263 AP_WEB_DB_EXPRPT_PKG.getPaymentDueFromReport(l_invoice_rec.report_header_id,
2264 l_payment_due_from)) THEN
2265 NULL;
2266 END IF;
2267
2268 IF (l_payment_due_from = 'COMPANY') OR
2269 (l_payment_due_from = 'BOTH' AND
2270 l_invoice_rec.source = 'SelfService') THEN
2271
2272 -----------------------------------------------------------------
2273 l_debug_info := 'Calling Reversal Logic for payment scenario ' ||
2274 l_payment_due_from;
2275 -----------------------------------------------------------------
2276 IF g_debug_switch = 'Y' THEN
2277 fnd_file.put_line(fnd_file.log, l_debug_info);
2278 END IF;
2279 l_total := AP_CREDIT_CARD_INVOICE_PKG.createCreditCardReversals(l_invoice_rec.invoice_id,
2280 l_invoice_rec.report_header_id,
2281 l_invoice_rec.gl_date,
2282 l_invoice_rec.total);
2283 l_invoice_rec.total := l_total;
2284 END IF;
2285
2286
2287 IF l_invoice_rec.source in ('CREDIT CARD','Both Pay') THEN
2288
2289 ---------------------------------------------------------
2290 l_debug_info := 'Create the Payee if one does not exist';
2291 ---------------------------------------------------------
2292 IF g_debug_switch = 'Y' THEN
2293 fnd_file.put_line(fnd_file.log, l_debug_info);
2294 END IF;
2295
2296 IF NOT CreatePayee(l_vendor_rec.party_id,
2297 l_vendor_rec.org_id,
2298 l_reject_code) THEN
2299 raise validation_failed;
2300 END IF;
2301
2302 IF l_reject_code is NOT NULL THEN
2303 raise validation_failed;
2304 END IF;
2305 -- Vendor should not be passed. Payment will be made to the Payee
2306 -- bug 6730812 : comment as we need to pass the vendor id and
2307 -- vendor site id. for complete fix we need AP patch 6711062 too.
2308 --l_vendor_rec.vendor_id := null;
2309 --l_vendor_rec.vendor_site_id := null;
2310 END IF;
2311
2312
2313 ----------------------------------------------------------
2314 l_debug_info := 'Check if the employee is contingent worker';
2315 ----------------------------------------------------------
2316 IF g_debug_switch = 'Y' THEN
2317 fnd_file.put_line(fnd_file.log, l_debug_info);
2318 END IF;
2319
2320 IF l_invoice_rec.is_contingent = 'N' THEN
2321
2322 ----------------------------------------------------------
2323 l_debug_info := 'Employee is not contingent worker, checking if it is active or not';
2324 ----------------------------------------------------------
2325
2326 IF g_debug_switch = 'Y' THEN
2327 fnd_file.put_line(fnd_file.log, l_debug_info);
2328 END IF;
2329
2330 l_is_active_employee := 'N';
2331
2332 BEGIN
2333
2334 SELECT 'Y'
2335 INTO l_is_active_employee
2336 FROM per_periods_of_service_v
2337 WHERE person_id = l_invoice_rec.employee_id
2338 AND trunc(sysdate) <= trunc(nvl(final_process_date, sysdate))
2339 AND ROWNUM=1
2340 ORDER BY LAST_UPDATE_DATE DESC;
2341
2342 EXCEPTION
2343 WHEN NO_DATA_FOUND THEN
2344 l_is_active_employee := 'N';
2345 END;
2346
2347 ----------------------------------------------------------
2348 l_debug_info := 'Active Employee = ' || l_is_active_employee;
2349 ----------------------------------------------------------
2350
2351 IF g_debug_switch = 'Y' THEN
2352 fnd_file.put_line(fnd_file.log, l_debug_info);
2353 END IF;
2354
2355 IF l_is_active_employee = 'N' THEN
2356
2357 l_reject_code := 'INACTIVE EMPLOYEE';
2358 raise validation_failed;
2359
2360 END IF;
2361
2362
2363 ELSE
2364
2365 ----------------------------------------------------------
2366 l_debug_info := 'Employee is contingent worker';
2367 ----------------------------------------------------------
2368
2369 IF g_debug_switch = 'Y' THEN
2370 fnd_file.put_line(fnd_file.log, l_debug_info);
2371 END IF;
2372
2373 END IF;
2374
2375
2376 ----------------------------------------------------------
2377 l_debug_info := 'Check the Default Payment Method';
2378 ----------------------------------------------------------
2379 IF g_debug_switch = 'Y' THEN
2380 fnd_file.put_line(fnd_file.log, l_debug_info);
2381 END IF;
2382
2383 if (l_invoice_rec.payment_method_code is null) then
2384
2385 ----------------------------------------------------------
2386 l_debug_info := 'Get the Default Payment Method';
2387 ----------------------------------------------------------
2388 IF g_debug_switch = 'Y' THEN
2389 fnd_file.put_line(fnd_file.log, l_debug_info);
2390 END IF;
2391
2392 l_trx_attributes.application_id := 200;
2393
2394 ----------------------------------------------------------
2395 l_debug_info := 'Getting the legal entity id';
2396 ----------------------------------------------------------
2397 IF g_debug_switch = 'Y' THEN
2398 fnd_file.put_line(fnd_file.log, l_debug_info);
2399 END IF;
2400
2401
2402 AP_UTILITIES_PKG.Get_Invoice_LE(
2403 l_vendor_rec.vendor_site_id,
2404 nvl(l_invoice_rec.accts_pay_ccid, l_vendor_rec.liab_acc),
2405 nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL)),
2406 l_le_id);
2407
2408 l_trx_attributes.payer_legal_entity_id := l_le_id;
2409 l_trx_attributes.payer_org_type := 'OPERATING_UNIT';
2410 l_trx_attributes.payer_org_id := nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL));
2411 l_trx_attributes.payee_party_id := l_vendor_rec.party_id;
2412 --l_trx_attributes.payee_party_site_id := p_payee_party_site_id;
2413 l_trx_attributes.supplier_site_id := l_vendor_rec.vendor_site_id;
2414 l_trx_attributes.payment_currency := l_invoice_rec.default_currency_code;
2415 l_trx_attributes.payment_amount := l_invoice_rec.total;
2416 l_trx_attributes.payment_function := 'PAYABLES_DISB';
2417 l_trx_attributes.pay_proc_trxn_type_code := 'EMPLOYEE_EXP';
2418
2419
2420 ----------------------------------------------------------
2421 l_debug_info := 'Calling iby_disbursement_comp_pub.get_default_payment_attributes';
2422 ----------------------------------------------------------
2423 IF g_debug_switch = 'Y' THEN
2424 fnd_file.put_line(fnd_file.log, l_debug_info);
2425 END IF;
2426
2427
2428 iby_disbursement_comp_pub.get_default_payment_attributes(
2429 p_api_version => 1.0,
2430 p_trxn_attributes_rec => l_trx_attributes,
2431 p_ignore_payee_pref => 'N',
2432 x_return_status => l_return_status,
2433 x_msg_count => l_msg_count,
2434 x_msg_data => l_msg_data,
2435 x_default_pmt_attrs_rec => l_result_pmt_attributes);
2436
2437 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
2438
2439 l_invoice_rec.payment_method_code := l_result_pmt_attributes.payment_method.Payment_Method_Code;
2440
2441 ELSE
2442
2443 ----------------------------------------------------------
2444 l_debug_info := 'Calling get_default_payment_attributes is failed with result ' || l_return_status;
2445 ----------------------------------------------------------
2446
2447 IF g_debug_switch = 'Y' THEN
2448 fnd_file.put_line(fnd_file.log, l_debug_info);
2449 END IF;
2450
2451 END IF;
2452
2453 end if;
2454
2455 ----------------------------------------------------------
2456 l_debug_info := 'l_invoice_rec.payment_method_code := '|| l_invoice_rec.payment_method_code;
2457 ----------------------------------------------------------
2458 IF g_debug_switch = 'Y' THEN
2459 fnd_file.put_line(fnd_file.log, l_debug_info);
2460 END IF;
2461
2462
2463 ----------------------------------------------------------
2464 l_debug_info := 'Check for apply advance default flag := ' || l_invoice_rec.apply_advances_flag;
2465 ----------------------------------------------------------
2466
2467 IF g_debug_switch = 'Y' THEN
2468 fnd_file.put_line(fnd_file.log, l_debug_info);
2469 END IF;
2470
2471 -- Bug: 7329159, Donot reset the advances flag for XpenseXpress
2472 IF nvl(l_invoice_rec.advance_invoice_to_apply, -1) = -1 AND
2473 NOT l_invoice_rec.source IN ('Both Pay', 'CREDIT CARD', 'XpenseXpress') THEN
2474
2475 ----------------------------------------------------------
2476 l_debug_info := 'Rechecking if apply_advance_flag needs to be reset';
2477 ----------------------------------------------------------
2478
2479 IF g_debug_switch = 'Y' THEN
2480 fnd_file.put_line(fnd_file.log, l_debug_info);
2481 END IF;
2482
2483 ----------------------------------------------------------
2484 l_debug_info := 'Apply Advance in Payable options := ' || l_sys_apply_advances_flag;
2485 ----------------------------------------------------------
2486
2487 IF g_debug_switch = 'Y' THEN
2488 fnd_file.put_line(fnd_file.log, l_debug_info);
2489 END IF;
2490
2491 --------------------------------------------------------------------
2492 l_debug_info := 'Calculate available prepayments for this employee';
2493 --------------------------------------------------------------------
2494
2495 IF g_debug_switch = 'Y' THEN
2496 fnd_file.put_line(fnd_file.log, l_debug_info);
2497 END IF;
2498
2499 l_available_prepays := 0;
2500 -- Bug#7440653 - Should not apply advances when invoice total is zero.
2501 IF ( l_vendor_rec.vendor_id IS NOT NULL AND l_invoice_rec.total > 0 ) THEN
2502
2503 BEGIN
2504
2505 SELECT nvl(sum(decode(payment_status_flag, 'Y',
2506 decode(sign(earliest_settlement_date - sysdate),1,0,1),
2507 0)), 0)
2508 INTO l_available_prepays
2509 FROM ap_invoices I,
2510 ap_suppliers PV
2511 WHERE exists (SELECT 'x'
2512 FROM ap_invoice_distributions aid
2513 WHERE aid.invoice_id = i.invoice_id
2514 AND aid.line_type_lookup_code IN ('ITEM','TAX')
2515 AND NVL(aid.reversal_flag,'N') <> 'Y'
2516 AND nvl(aid.prepay_amount_remaining, aid.amount) > 0 )
2517 AND I.vendor_id = PV.vendor_id
2518 AND PV.employee_id = l_invoice_rec.employee_id
2519 AND I.invoice_type_lookup_code = 'PREPAYMENT'
2520 AND earliest_settlement_date IS NOT NULL
2521 AND I.invoice_amount > 0
2522 AND I.invoice_currency_code = l_invoice_rec.default_currency_code
2523 AND PV.vendor_id = l_vendor_rec.vendor_id;
2524
2525 EXCEPTION
2526 WHEN NO_DATA_FOUND THEN
2527 l_available_prepays := 0;
2528 END;
2529 -- Bug#7440653 - Should not apply advances when invoice total is zero.
2530 ELSIF ( l_invoice_rec.total > 0 ) THEN
2531
2532 BEGIN
2533
2534 SELECT nvl(sum(decode(payment_status_flag, 'Y',
2535 decode(sign(earliest_settlement_date - sysdate),1,0,1),
2536 0)), 0)
2537 INTO l_available_prepays
2538 FROM ap_invoices I,
2539 ap_suppliers PV
2540 WHERE exists (SELECT 'x'
2541 FROM ap_invoice_distributions aid
2542 WHERE aid.invoice_id = i.invoice_id
2543 AND aid.line_type_lookup_code IN ('ITEM','TAX')
2544 AND NVL(aid.reversal_flag,'N') <> 'Y'
2545 AND nvl(aid.prepay_amount_remaining, aid.amount) > 0 )
2546 AND I.vendor_id = PV.vendor_id
2547 AND PV.employee_id = l_invoice_rec.employee_id
2548 AND I.invoice_type_lookup_code = 'PREPAYMENT'
2549 AND earliest_settlement_date IS NOT NULL
2550 AND I.invoice_amount > 0
2551 AND I.invoice_currency_code = l_invoice_rec.default_currency_code;
2552
2553 EXCEPTION
2554 WHEN NO_DATA_FOUND THEN
2555 l_available_prepays := 0;
2556 END;
2557
2558 END IF;
2559
2560
2561 ----------------------------------------------------------
2562 l_debug_info := 'Available Prepayment Sign := ' || l_available_prepays;
2563 ----------------------------------------------------------
2564
2565 IF g_debug_switch = 'Y' THEN
2566 fnd_file.put_line(fnd_file.log, l_debug_info);
2567 END IF;
2568
2569
2570 IF l_invoice_rec.apply_advances_flag = 'Y' AND
2571 (l_sys_apply_advances_flag <> 'Y' OR
2572 l_available_prepays = 0) THEN
2573
2574 ----------------------------------------------------------
2575 l_debug_info := 'Resetting the apply advance default to N ';
2576 ----------------------------------------------------------
2577
2578 IF g_debug_switch = 'Y' THEN
2579 fnd_file.put_line(fnd_file.log, l_debug_info);
2580 END IF;
2581
2582
2583 UPDATE ap_expense_report_headers_all
2584 SET apply_advances_default = 'N'
2585 WHERE report_header_id = l_invoice_rec.report_header_id;
2586
2587 l_invoice_rec.apply_advances_flag := 'N';
2588
2589 ELSIF l_invoice_rec.apply_advances_flag = 'N' AND
2590 l_sys_apply_advances_flag = 'Y' AND
2591 l_available_prepays > 0 THEN
2592
2593 ----------------------------------------------------------
2594 l_debug_info := 'Resetting the apply advance default to Y ';
2595 ----------------------------------------------------------
2596
2597 IF g_debug_switch = 'Y' THEN
2598 fnd_file.put_line(fnd_file.log, l_debug_info);
2599 END IF;
2600
2601
2602 UPDATE ap_expense_report_headers_all
2603 SET apply_advances_default = 'Y'
2604 WHERE report_header_id = l_invoice_rec.report_header_id;
2605
2606 l_invoice_rec.apply_advances_flag := 'Y';
2607
2608 END IF;
2609
2610 END IF;
2611
2612
2613
2614 ----------------------------------------------------------
2615 l_debug_info := 'Insert into AP Invoices Interface table';
2616 ----------------------------------------------------------
2617 IF g_debug_switch = 'Y' THEN
2618 fnd_file.put_line(fnd_file.log, l_debug_info);
2619 END IF;
2620
2621
2622 InsertInvoiceInterface(l_invoice_rec, l_vendor_rec);
2623 -- Bug: 6809570
2624 InsertInvoiceLinesInterface(l_invoice_rec.report_header_id, l_invoice_rec.invoice_id,
2625 p_transfer_flag, l_base_currency, l_enable_recoverable_flag);
2626
2627
2628 if (l_invoice_rec.source in ('Both Pay', 'CREDIT CARD')) then
2629
2630 ------------------------------------------------------------
2631 l_debug_info := 'Processing '||l_invoice_rec.source;
2632 ------------------------------------------------------------
2633 IF g_debug_switch = 'Y' THEN
2634 fnd_file.put_line(fnd_file.log, l_debug_info);
2635 END IF;
2636
2637 ------------------------------------------------------------
2638 l_debug_info := 'Submitting Payment Request';
2639 ------------------------------------------------------------
2640 IF g_debug_switch = 'Y' THEN
2641 fnd_file.put_line(fnd_file.log, l_debug_info);
2642 END IF;
2643
2644 l_cc_invoices_fetched := l_cc_invoices_fetched + 1;
2645 l_rows_to_import := l_rows_to_import - 1;
2646
2647 AP_IMPORT_INVOICES_PKG.SUBMIT_PAYMENT_REQUEST(
2648 p_api_version => 1.0,
2649 p_invoice_interface_id => l_invoice_rec.invoice_id,
2650 p_budget_control => 'N',
2651 p_needs_invoice_approval=> 'N',
2652 p_invoice_id => l_invoice_rec.invoice_id,
2653 x_return_status => x_return_status,
2654 x_msg_count => x_msg_count,
2655 x_msg_data => x_msg_data,
2656 x_rejection_list => l_rejection_list,
2657 p_calling_sequence => 'AP_WEB_EXPORT_ER.ExportERtoAP',
2658 p_commit => FND_API.G_FALSE,
2659 p_batch_name => l_batch_name, --Bug#8352220
2660 p_conc_request_id => l_request_id);--Bug#8464009
2661
2662 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2663
2664 FOR i in l_rejection_list.FIRST .. l_rejection_list.LAST LOOP
2665 l_debug_info := i||' Errors found interfacing data to AP ...';
2666 fnd_file.put_line(fnd_file.log, l_debug_info);
2667 l_debug_info := l_rejection_list(i).reject_lookup_code;
2668 fnd_file.put_line(fnd_file.log, l_debug_info);
2669 l_reject_code := l_rejection_list(i).reject_lookup_code;
2670 END LOOP;
2671
2672 raise validation_failed;
2673
2674 ELSE
2675
2676 l_cc_invoices_created := l_cc_invoices_created + 1;
2677
2678 END IF;
2679
2680 end if; /* (l_invoice_rec.source in ('Both Pay', 'CREDIT CARD')) */
2681
2682 ------------------------------------------------------------
2683 l_debug_info := 'Update request_id';
2684 ------------------------------------------------------------
2685 IF g_debug_switch = 'Y' THEN
2686 fnd_file.put_line(fnd_file.log, l_debug_info);
2687 END IF;
2688
2689 UPDATE ap_expense_report_headers_all
2690 SET request_id = l_request_id,
2691 last_update_date = sysdate,
2692 last_updated_by = g_last_updated_by,
2693 last_update_login = g_last_update_login
2694 WHERE report_header_id = l_invoice_rec.report_header_id;
2695
2696
2697 EXCEPTION
2698 WHEN validation_failed then
2699 ------------------------------------------------------------
2700 l_debug_info := 'Validation failed';
2701 ------------------------------------------------------------
2702 IF g_debug_switch = 'Y' THEN
2703 fnd_file.put_line(fnd_file.log, l_debug_info);
2704 END IF;
2705
2706 UPDATE ap_expense_report_headers_all
2707 SET reject_code = l_reject_code,
2708 request_id = l_request_id,
2709 vouchno = 0,
2710 last_update_date = sysdate,
2711 last_updated_by = g_last_updated_by,
2712 last_update_login = g_last_update_login
2713 WHERE report_header_id = l_invoice_rec.report_header_id;
2714 END;
2715
2716 END LOOP;
2717
2718 l_rows_to_import := l_rows_to_import + c_expenses_to_import%ROWCOUNT;
2719
2720 -------------------------------------------------------------------
2721 l_debug_info := 'Credit Card Expenses Fetched = '|| to_char(l_cc_invoices_fetched);
2722 -------------------------------------------------------------------
2723 fnd_file.put_line(fnd_file.log, l_debug_info);
2724
2725 -------------------------------------------------------------------
2726 l_debug_info := 'Credit Card Invoices Created = '|| to_char(l_cc_invoices_created);
2727 -------------------------------------------------------------------
2728 fnd_file.put_line(fnd_file.log, l_debug_info);
2729
2730 CLOSE c_expenses_to_import;
2731
2732 IF l_rows_to_import = 0 THEN
2733 ------------------------------------------
2734 l_debug_info := 'No Rows found to import';
2735 ------------------------------------------
2736 fnd_file.put_line(fnd_file.log, l_debug_info);
2737
2738 ELSE
2739 -----------------------------------------------
2740 l_debug_info := 'Call Payables Open Interface';
2741 -----------------------------------------------
2742 fnd_file.put_line(fnd_file.log, l_debug_info);
2743
2744 IF (NOT AP_IMPORT_INVOICES_PKG.IMPORT_INVOICES(l_batch_name,
2745 l_invoice_rec.gl_date,
2746 to_char(''),
2747 to_char(''),
2748 p_commit_cycles,
2749 p_source,
2750 p_group_id,
2751 l_request_id,
2752 p_debug_switch,
2753 p_org_id,
2754 l_batch_error_flag,
2755 l_invoices_fetched,
2756 l_invoices_created,
2757 l_total_invoice_amount,
2758 l_print_batch,
2759 l_calling_sequence)) THEN
2760 -----------------------------------------------------------------------------------
2761 l_debug_info := 'Call to AP_IMPORT_INVOICES_PKG.IMPORT_INVOICES failed. Aborting.';
2762 -----------------------------------------------------------------------------------
2763 fnd_file.put_line(fnd_file.log, l_debug_info);
2764
2765 RAISE batch_failure;
2766 END IF;
2767
2768 END IF; /* l_rows_to_import = 0 */
2769
2770 l_invoices_fetched := l_invoices_fetched + l_cc_invoices_fetched;
2771 l_invoices_created := l_invoices_created + l_cc_invoices_created;
2772 l_failed_open_interface := l_invoices_fetched - l_invoices_created;
2773
2774 IF l_invoices_created >0 THEN
2775 OPEN c_successful_invoices (l_request_id);
2776 FETCH c_successful_invoices BULK COLLECT INTO l_invoice_id_list, l_report_header_id_list,
2777 l_oie_applied_prepay_list, l_oie_applied_amt_list;
2778
2779 FORALL i IN l_report_header_id_list.FIRST .. l_report_header_id_list.LAST
2780 UPDATE ap_expense_report_headers_all
2781 SET vouchno = l_invoice_id_list(i),
2782 reject_code = null
2783 WHERE report_header_id = l_report_header_id_list(i);
2784
2785 --Upadating the expense stataus code for reports which have been paid by advances
2786
2787 FOR i IN l_report_header_id_list.FIRST .. l_report_header_id_list.LAST
2788 LOOP
2789
2790 l_actual_adv_applied := NULL;
2791 --Bug#6988193 - Update the advance applied for a report during export
2792 IF l_oie_applied_prepay_list(i) IS NOT NULL THEN
2793 BEGIN
2794 SELECT abs(sum(amount))
2795 INTO l_actual_adv_applied
2796 FROM ap_invoice_lines_all
2797 WHERE invoice_id = l_invoice_id_list(i)
2798 AND line_type_lookup_code = 'PREPAY'
2799 AND prepay_invoice_id = l_oie_applied_prepay_list(i);
2800 EXCEPTION
2801 WHEN NO_DATA_FOUND THEN
2802 l_actual_adv_applied := 0;
2803 END;
2804
2805 IF ( l_oie_applied_amt_list(i) <> l_actual_adv_applied) THEN
2806 UPDATE ap_expense_report_headers_all
2807 SET maximum_amount_to_apply = l_actual_adv_applied,
2808 amt_due_employee = ( nvl(amt_due_employee,0) + (l_oie_applied_amt_list(i) - l_actual_adv_applied) )
2809 WHERE report_header_id = l_report_header_id_list(i);
2810
2811 -----------------------------------------------------------------------
2812 l_debug_info := 'Updated ap_expense_report_headers_all for report_header_id = '||to_char(l_report_header_id_list(i))||' with maximum_amount_to_apply = '||to_char(l_actual_adv_applied);
2813 -----------------------------------------------------------------------
2814 fnd_file.put_line(fnd_file.log, l_debug_info);
2815
2816 END IF;
2817 END IF;
2818
2819 BEGIN
2820 SELECT sum(amount)
2821 INTO l_inv_total_amount
2822 FROM ap_invoice_lines_all ap1
2823 WHERE invoice_id = l_invoice_id_list(i);
2824 EXCEPTION
2825 WHEN NO_DATA_FOUND THEN
2826 l_inv_total_amount :=-1;
2827 END;
2828 IF (l_inv_total_amount = 0) THEN
2829 l_expense_status_code := 'PAID';
2830 BEGIN
2831 SELECT payment_due_from_code
2832 INTO l_payment_due_frm
2833 FROM ap_credit_card_trxns_all trx
2834 WHERE trx.report_header_id =l_report_header_id_list(i)
2835 AND trx.category ='BUSINESS'
2836 AND rownum = 1;
2837 EXCEPTION
2838 WHEN NO_DATA_FOUND THEN
2839 l_payment_due_frm :=NULL;
2840 END;
2841 --Updating status for reports containing credit card transactions for which the cash part has been fully paid by advances.
2842 IF (l_payment_due_frm ='BOTH') THEN
2843 l_expense_status_code := 'PARPAID';
2844 END IF;
2845
2846 UPDATE ap_expense_report_headers_all ah
2847 SET expense_status_code = l_expense_status_code
2848 WHERE report_header_id = l_report_header_id_list(i) ;
2849
2850 END IF;
2851
2852 --Bug#2823530
2853 IF p_transfer_attachments = 'Y' THEN
2854 l_debug_info := 'Transferring the Attachments if any';
2855 IF g_debug_switch = 'Y' THEN
2856 fnd_file.put_line(fnd_file.log, l_debug_info);
2857 END IF;
2858
2859 TransferAttachments(l_report_header_id_list(i),l_invoice_id_list(i));
2860
2861 END IF;
2862
2863 END LOOP;
2864
2865 CLOSE c_successful_invoices;
2866 END IF;
2867
2868
2869 l_invoice_id_list.DELETE;
2870 l_report_header_id_list.DELETE;
2871
2872
2873 IF l_failed_open_interface <> 0 THEN
2874 -----------------------------------------------------------
2875 l_debug_info := 'Invoices that failed Open Interface *' ||
2876 to_char(l_failed_open_interface) || '*';
2877 -----------------------------------------------------------
2878 IF g_debug_switch = 'Y' THEN
2879 fnd_file.put_line(fnd_file.log, l_debug_info);
2880 END IF;
2881
2882 OPEN c_rejected_invoices(l_request_id);
2883
2884 FETCH c_rejected_invoices BULK COLLECT
2885 INTO l_report_header_id_list, l_reject_code_list, l_invoice_id_list;
2886
2887 IF l_report_header_id_list.COUNT > 0 THEN
2888
2889 -----------------------------------------------------------------------
2890 l_debug_info := 'Opening cursor for deleting from AP interface tables';
2891 -----------------------------------------------------------------------
2892
2893 IF g_debug_switch = 'Y' THEN
2894 fnd_file.put_line(fnd_file.log, l_debug_info);
2895 END IF;
2896
2897 FORALL i IN l_report_header_id_list.FIRST .. l_report_header_id_list.LAST
2898 UPDATE ap_expense_report_headers_all
2899 SET reject_code = l_reject_code_list(i), vouchno = 0
2900 WHERE report_header_id = l_report_header_id_list(i)
2901 and nvl(vouchno,0) = 0;
2902
2903 -- Bug#8464009 - Removed the delete SQL as this is being done at later point.
2904
2905 END IF;
2906
2907 CLOSE c_rejected_invoices;
2908
2909 END IF;
2910
2911 IF (l_expenses_fetched <> 0) THEN
2912 ----------------------------------------------------------
2913 l_debug_info := 'Purge data from the ap interface tables';
2914 ----------------------------------------------------------
2915 IF g_debug_switch = 'Y' THEN
2916 fnd_file.put_line(fnd_file.log, l_debug_info);
2917 END IF;
2918
2919 IF (NOT AP_IMPORT_INVOICES_PKG.IMPORT_PURGE(p_source,
2920 p_group_id,
2921 null, -- p_org_id
2922 p_commit_cycles,
2923 l_calling_sequence)) THEN
2924
2925 ----------------------------------------------------------------------
2926 l_debug_info := 'Purge from the ap interface tables failed. Aborting';
2927 ----------------------------------------------------------------------
2928 fnd_file.put_line(fnd_file.log, l_debug_info);
2929 raise batch_failure;
2930 END IF;
2931
2932 --Bug#8464009 - Deleting records from Interface tables that are left out by IMPORT_PURGE
2933 DELETE FROM ap_interface_rejections
2934 WHERE parent_table = 'AP_INVOICES_INTERFACE'
2935 AND parent_id IN (SELECT invoice_id
2936 FROM ap_invoices_interface
2937 WHERE request_id = l_request_id
2938 );
2939
2940 DELETE FROM ap_interface_rejections
2941 WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
2942 and parent_id IN (SELECT aili.invoice_line_id
2943 FROM ap_invoices_interface aii, ap_invoice_lines_interface aili
2944 WHERE aii.invoice_id = aili.invoice_id
2945 AND aii.request_id = l_request_id
2946 );
2947
2948 DELETE FROM ap_invoice_lines_interface
2949 WHERE invoice_id IN (SELECT invoice_id
2950 FROM ap_invoices_interface
2951 WHERE request_id = l_request_id
2952 );
2953
2954 DELETE FROM ap_invoices_interface
2955 WHERE request_id = l_request_id;
2956
2957 END IF; /* l_expenses_fetched <> 0 */
2958
2959
2960 -------------------------------------------------------------------
2961 l_debug_info := 'Expenses Fetched = '|| to_char(l_expenses_fetched);
2962 -------------------------------------------------------------------
2963 fnd_file.put_line(fnd_file.log, l_debug_info);
2964
2965 -------------------------------------------------------------------
2966 l_debug_info := 'Invoices Created = '|| to_char(l_invoices_created);
2967 -------------------------------------------------------------------
2968 fnd_file.put_line(fnd_file.log, l_debug_info);
2969
2970 IF (l_expenses_fetched <> l_invoices_created AND
2971 p_role_name IS NOT NULL )THEN
2972
2973 ---------------------------------------------------
2974 l_debug_info := 'Call Expenses Rejection Workflow';
2975 ---------------------------------------------------
2976 IF g_debug_switch = 'Y' THEN
2977 fnd_file.put_line(fnd_file.log, l_debug_info);
2978 END IF;
2979
2980 AP_WEB_EXPORT_WF.RaiseRejectionEvent(l_request_id,
2981 p_role_name );
2982 END IF;
2983
2984 COMMIT;
2985
2986 PrintExportOutput;
2987 EXCEPTION
2988 WHEN batch_failure THEN
2989 fnd_file.put_line(fnd_file.log, sqlerrm);
2990 rollback;
2991 raise;
2992 WHEN OTHERS THEN
2993 fnd_file.put_line(fnd_file.log, sqlerrm);
2994 rollback;
2995 raise;
2996
2997 END ExportERtoAP;
2998
2999 ---------------------------------------------------------------------------------------
3000 FUNCTION ValidateERLines(p_report_header_id IN NUMBER,
3001 p_invoice_id IN NUMBER,
3002 p_transfer_flag IN VARCHAR2,
3003 p_base_currency IN VARCHAR2,
3004 p_set_of_books_id IN NUMBER,
3005 p_source IN VARCHAR2,
3006 p_enable_recoverable_flag IN VARCHAR2,
3007 p_invoice_lines_rec_tab OUT NOCOPY InvoiceLinesRecTabType,
3008 p_reject_code OUT NOCOPY VARCHAR2)
3009 RETURN BOOLEAN IS
3010 ---------------------------------------------------------------------------------------
3011
3012 CURSOR c_expense_lines(l_report_header_id NUMBER, p_base_currency VARCHAR2) IS
3013 SELECT xl.report_header_id,
3014 xl.report_line_id,
3015 gcc.code_combination_id code_combination_id,
3016 nvl(lc.lookup_code, '') line_type_lookup_code,
3017 nvl(xl.vat_code, '') line_vat_code,
3018 nvl(xl.tax_code_id, -1) line_tax_code_id,
3019 SIGN(nvl(amount, 0)) distribution_amount_sign,
3020 SIGN(nvl(stat_amount, 0)) stat_amount_sign,
3021 to_char(nvl(xl.stat_amount, '')) stat_amount,
3022 xl.set_of_books_id line_set_of_books_id,
3023 to_char(nvl(ap_utilities_pkg.ap_round_currency(xl.amount,
3024 XH.default_currency_code),
3025 0)) distribution_amount,
3026 nvl(xl.item_description, '') item_description,
3027 xl.line_type_lookup_code db_line_type,
3028 xl.distribution_line_number,
3029 to_char(decode(p_base_currency,
3030 xh.default_currency_code,
3031 null,
3032 DECODE(F.minimum_accountable_unit,
3033 '',
3034 ROUND(ap_utilities_pkg.ap_round_currency(xl.amount,
3035 XH.default_currency_code) *
3036 xh.default_exchange_rate,
3037 F.precision),
3038 ROUND(ap_utilities_pkg.ap_round_currency(xl.amount,
3039 XH.default_currency_code) *
3040 xh.default_exchange_rate /
3041 F.minimum_accountable_unit) *
3042 F.minimum_accountable_unit))) base_amount,
3043 DECODE(nvl(gcc.account_type, 'x'), 'A', 'Y', 'N') assets_tracking_flag,
3044 nvl(decode(p_transfer_flag, 'Y', xl.attribute1), ''),
3045 nvl(decode(p_transfer_flag, 'Y', xl.attribute2), ''),
3046 nvl(decode(p_transfer_flag, 'Y', xl.attribute3), ''),
3047 nvl(decode(p_transfer_flag, 'Y', xl.attribute4), ''),
3048 nvl(decode(p_transfer_flag, 'Y', xl.attribute5), ''),
3049 nvl(decode(p_transfer_flag, 'Y', xl.attribute6), ''),
3050 nvl(decode(p_transfer_flag, 'Y', xl.attribute7), ''),
3051 nvl(decode(p_transfer_flag, 'Y', xl.attribute8), ''),
3052 nvl(decode(p_transfer_flag, 'Y', xl.attribute9), ''),
3053 nvl(decode(p_transfer_flag, 'Y', xl.attribute10), ''),
3054 nvl(decode(p_transfer_flag, 'Y', xl.attribute11), ''),
3055 nvl(decode(p_transfer_flag, 'Y', xl.attribute12), ''),
3056 nvl(decode(p_transfer_flag, 'Y', xl.attribute13), ''),
3057 nvl(decode(p_transfer_flag, 'Y', xl.attribute14), ''),
3058 nvl(decode(p_transfer_flag, 'Y', xl.attribute15), ''),
3059 nvl(decode(p_transfer_flag, 'Y', xl.attribute_category), ''),
3060 nvl(xl.project_accounting_context, ''),
3061 nvl(to_char(xl.project_id), ''),
3062 nvl(to_char(xl.task_id), ''),
3063 nvl(to_char(xl.expenditure_organization_id), ''),
3064 nvl(xl.expenditure_type, ''),
3065 nvl(to_char(xl.expenditure_item_date), ''),
3066 nvl(to_char(xl.pa_quantity), ''),
3067 nvl(xl.reference_1, ''),
3068 nvl(xl.reference_2, ''),
3069 nvl(to_char(xl.awt_group_id), ''),
3070 xl.amount_includes_tax_flag,
3071 nvl(xl.tax_code_override_flag, 'N'),
3072 '' tax_recovery_rate,
3073 'N' tax_recovery_override_flag,
3074 nvl(decode(p_enable_recoverable_flag,
3075 'Y',
3076 decode(xl.line_type_lookup_code, 'TAX', 'Y', 'N'),
3077 'N'),
3078 'N') tax_recoverable_flag,
3079 xl.global_attribute1,
3080 xl.global_attribute2,
3081 xl.global_attribute3,
3082 xl.global_attribute4,
3083 xl.global_attribute5,
3084 xl.global_attribute6,
3085 xl.global_attribute7,
3086 xl.global_attribute8,
3087 xl.global_attribute9,
3088 xl.global_attribute10,
3089 xl.global_attribute11,
3090 xl.global_attribute12,
3091 xl.global_attribute13,
3092 xl.global_attribute14,
3093 xl.global_attribute15,
3094 xl.global_attribute16,
3095 xl.global_attribute17,
3096 xl.global_attribute18,
3097 xl.global_attribute19,
3098 xl.global_attribute20,
3099 xl.global_attribute_category,
3100 nvl(xl.receipt_verified_flag, ''),
3101 nvl(xl.receipt_required_flag, ''),
3102 nvl(xl.receipt_missing_flag, ''),
3103 nvl(xl.justification, ''),
3104 nvl(xl.expense_group, ''),
3105 to_char(nvl(xl.start_expense_date, '')),
3106 to_char(nvl(xl.start_expense_date, xh.week_end_date)) start_expense_date2,
3107 to_char(nvl(xl.end_expense_date, '')),
3108 nvl(xl.merchant_document_number, ''),
3109 nvl(xl.merchant_name, ''),
3110 nvl(xl.merchant_reference, ''),
3111 nvl(xl.merchant_tax_reg_number, ''),
3112 nvl(xl.merchant_taxpayer_id, ''),
3113 nvl(xl.country_of_supply, ''),
3114 nvl(xl.receipt_currency_code, ''),
3115 to_char(nvl(xl.receipt_conversion_rate, '')),
3116 to_char(nvl(xl.receipt_currency_amount, '')),
3117 to_char(nvl(xl.daily_amount, '')),
3118 to_char(nvl(xl.web_parameter_id, '')),
3119 nvl(xl.adjustment_reason, ''),
3120 nvl(xl.credit_card_trx_id, ''),
3121 nvl(xl.company_prepaid_invoice_id, ''),
3122 xl.created_by,
3123 '' pa_addition_flag,
3124 '' type_1099,
3125 '' income_tax_region,
3126 '' award_id,
3127 '' invoice_id,
3128 '' accounting_date,
3129 XL.org_id org_id
3130 FROM ap_expense_report_lines XL,
3131 gl_code_combinations gcc,
3132 ap_lookup_codes lc,
3133 fnd_currencies F,
3134 ap_expense_report_headers XH
3135 WHERE XL.report_header_id = XH.report_header_id
3136 AND XH.report_header_id = p_report_header_id
3137 AND XL.code_combination_id = gcc.code_combination_id(+)
3138 AND nvl(XL.itemization_parent_id,0) <> -1 /* Itemization Project */
3139 AND lc.lookup_code(+) = XL.line_type_lookup_code
3140 AND lc.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
3141 AND F.currency_code = p_base_currency;
3142
3143 l_debug_info VARCHAR2(2000);
3144 line_validation_failed EXCEPTION;
3145 i BINARY_INTEGER := 0;
3146
3147 BEGIN
3148 ------------------------------------------------
3149 l_debug_info := 'Start Exporting Expense Lines';
3150 ------------------------------------------------
3151 IF g_debug_switch = 'Y' THEN
3152 fnd_file.put_line(fnd_file.log, l_debug_info);
3153 END IF;
3154
3155 OPEN c_expense_lines(p_report_header_id, p_base_currency);
3156
3157 LOOP
3158 FETCH c_expense_lines
3159 INTO p_invoice_lines_rec_tab(i);
3160
3161 IF c_expense_lines%NOTFOUND THEN
3162 EXIT;
3163 END IF;
3164
3165 ---------------------------------------------------------------------------
3166 l_debug_info := 'report_header_id = **'||to_char(p_report_header_id)||'**';
3167 ---------------------------------------------------------------------------
3168 IF g_debug_switch = 'Y' THEN
3169 fnd_file.put_line(fnd_file.log, l_debug_info);
3170 END IF;
3171
3172 p_invoice_lines_rec_tab(i).invoice_id := p_invoice_id;
3173
3174 IF p_source = 'Oracle Project Accounting' then
3175
3176 ---------------------------------------------
3177 l_debug_info := 'Validate Line Set Of Books';
3178 ---------------------------------------------
3179 IF g_debug_switch = 'Y' THEN
3180 fnd_file.put_line(fnd_file.log, l_debug_info);
3181 END IF;
3182
3183 IF p_invoice_lines_rec_tab(i).line_set_of_books_id <> p_set_of_books_id THEN
3184 ---------------------------------------------------------------------------------
3185 l_debug_info := 'Invalid set of books-line *' ||
3186 to_char(p_invoice_lines_rec_tab(i) .line_set_of_books_id) || '*';
3187 ---------------------------------------------------------------------------------
3188 fnd_file.put_line(fnd_file.log, l_debug_info);
3189
3190 p_reject_code := 'Invalid set of books-line';
3191 raise line_validation_failed;
3192 END IF;
3193 END IF;
3194
3195
3196 IF p_invoice_lines_rec_tab(i).project_id is NOT NULL THEN
3197 p_invoice_lines_rec_tab(i).pa_addition_flag := 'T';
3198 ELSE
3199 p_invoice_lines_rec_tab(i).pa_addition_flag := 'E';
3200 END IF;
3201
3202 IF p_invoice_lines_rec_tab(i).db_line_type = 'TAX' AND
3203 p_invoice_lines_rec_tab(i).line_vat_code IS NULL THEN
3204 p_reject_code := 'Tax code required';
3205 raise line_validation_failed;
3206 END IF;
3207
3208 i := i + 1;
3209
3210 END LOOP;
3211
3212 CLOSE c_expense_lines;
3213 RETURN(TRUE);
3214
3215 EXCEPTION
3216 WHEN line_validation_failed THEN
3217 CLOSE c_expense_lines;
3218 RETURN(FALSE);
3219 WHEN OTHERS THEN
3220 CLOSE c_expense_lines;
3221 p_reject_code := SQLCODE;
3222 fnd_file.put_line(fnd_file.log, SQLERRM);
3223 RETURN(FALSE);
3224
3225 END ValidateERLines;
3226
3227 ------------------------------------------------------------------------
3228 PROCEDURE InsertInvoiceInterface(p_invoice_rec InvoiceInfoRecType,
3229 p_vendor_rec VendorInfoRecType) IS
3230 ------------------------------------------------------------------------
3231 l_payment_priority ap_supplier_sites_all.payment_priority%TYPE;
3232 BEGIN
3233 BEGIN
3234 if (p_vendor_rec.vendor_site_id IS NOT NULL AND p_invoice_rec.source in ('Both Pay', 'CREDIT CARD')) then
3235 select payment_priority
3236 into l_payment_priority
3237 from ap_supplier_sites_all
3238 where vendor_site_id = p_vendor_rec.vendor_site_id;
3239 else
3240 l_payment_priority := NULL;
3241 end if;
3242 EXCEPTION
3243 WHEN OTHERS THEN
3244 l_payment_priority := NULL;
3245 END;
3246 INSERT INTO AP_INVOICES_INTERFACE
3247 (INVOICE_ID,
3248 APPLICATION_ID,
3249 PRODUCT_TABLE,
3250 REFERENCE_KEY1,
3251 INVOICE_NUM,
3252 INVOICE_TYPE_LOOKUP_CODE,
3253 INVOICE_DATE,
3254 VENDOR_ID,
3255 VENDOR_NUM,
3256 VENDOR_NAME,
3257 VENDOR_SITE_ID,
3258 VENDOR_SITE_CODE,
3259 INVOICE_AMOUNT,
3260 INVOICE_CURRENCY_CODE,
3261 EXCHANGE_RATE,
3262 EXCHANGE_RATE_TYPE,
3263 EXCHANGE_DATE,
3264 TERMS_ID,
3265 TERMS_NAME,
3266 DESCRIPTION,
3267 AWT_GROUP_ID,
3268 AWT_GROUP_NAME,
3269 LAST_UPDATE_DATE,
3270 LAST_UPDATED_BY,
3271 LAST_UPDATE_LOGIN,
3272 CREATION_DATE,
3273 CREATED_BY,
3274 ATTRIBUTE_CATEGORY,
3275 ATTRIBUTE1,
3276 ATTRIBUTE2,
3277 ATTRIBUTE3,
3278 ATTRIBUTE4,
3279 ATTRIBUTE5,
3280 ATTRIBUTE6,
3281 ATTRIBUTE7,
3282 ATTRIBUTE8,
3283 ATTRIBUTE9,
3284 ATTRIBUTE10,
3285 ATTRIBUTE11,
3286 ATTRIBUTE12,
3287 ATTRIBUTE13,
3288 ATTRIBUTE14,
3289 ATTRIBUTE15,
3290 GLOBAL_ATTRIBUTE_CATEGORY,
3291 GLOBAL_ATTRIBUTE1,
3292 GLOBAL_ATTRIBUTE2,
3293 GLOBAL_ATTRIBUTE3,
3294 GLOBAL_ATTRIBUTE4,
3295 GLOBAL_ATTRIBUTE5,
3296 GLOBAL_ATTRIBUTE6,
3297 GLOBAL_ATTRIBUTE7,
3298 GLOBAL_ATTRIBUTE8,
3299 GLOBAL_ATTRIBUTE9,
3300 GLOBAL_ATTRIBUTE10,
3301 GLOBAL_ATTRIBUTE11,
3302 GLOBAL_ATTRIBUTE12,
3303 GLOBAL_ATTRIBUTE13,
3304 GLOBAL_ATTRIBUTE14,
3305 GLOBAL_ATTRIBUTE15,
3306 GLOBAL_ATTRIBUTE16,
3307 GLOBAL_ATTRIBUTE17,
3308 GLOBAL_ATTRIBUTE18,
3309 GLOBAL_ATTRIBUTE19,
3310 GLOBAL_ATTRIBUTE20,
3311 STATUS,
3312 SOURCE,
3313 GROUP_ID,
3314 REQUEST_ID,
3315 PAYMENT_CROSS_RATE_TYPE,
3316 PAYMENT_CROSS_RATE_DATE,
3317 PAYMENT_CROSS_RATE,
3318 PAYMENT_CURRENCY_CODE,
3319 WORKFLOW_FLAG,
3320 DOC_CATEGORY_CODE,
3321 VOUCHER_NUM,
3322 PAY_GROUP_LOOKUP_CODE,
3323 GOODS_RECEIVED_DATE,
3324 INVOICE_RECEIVED_DATE,
3325 GL_DATE,
3326 ACCTS_PAY_CODE_COMBINATION_ID,
3327 ORG_ID,
3328 AMOUNT_APPLICABLE_TO_DISCOUNT,
3329 PREPAY_NUM,
3330 PREPAY_LINE_NUM,
3331 PREPAY_APPLY_AMOUNT,
3332 PREPAY_GL_DATE,
3333 INVOICE_INCLUDES_PREPAY_FLAG,
3334 NO_XRATE_BASE_AMOUNT,
3335 VENDOR_EMAIL_ADDRESS,
3336 TERMS_DATE,
3337 REQUESTER_ID,
3338 PAID_ON_BEHALF_EMPLOYEE_ID,
3339 PARTY_ID,
3340 PARTY_SITE_ID,
3341 PAYMENT_PRIORITY)
3342 VALUES
3343 (p_invoice_rec.invoice_id,
3344 200,
3345 'AP_EXPENSE_REPORT_HEADERS_ALL',
3346 p_invoice_rec.report_header_id,
3347 p_invoice_rec.invoice_num,
3348 p_invoice_rec.invoice_type_lookup_code,
3349 p_invoice_rec.week_end_date,
3350 p_invoice_rec.vendor_id,
3351 '',
3352 '',
3353 p_vendor_rec.vendor_site_id,
3354 '',
3355 p_invoice_rec.total,
3356 p_invoice_rec.default_currency_code,
3357 decode(p_invoice_rec.default_exchange_rate,-1,'',p_invoice_rec.default_exchange_rate),--Bug#8369669
3358 p_invoice_rec.default_exchange_rate_type,
3359 p_invoice_rec.default_exchange_date,
3360 p_vendor_rec.terms_id,
3361 '',
3362 p_invoice_rec.description,
3363 p_invoice_rec.awt_group_id,
3364 '',
3365 sysdate,
3366 g_last_updated_by,
3367 '',
3368 sysdate,
3369 p_invoice_rec.created_by,
3370 p_invoice_rec.attribute_category,
3371 p_invoice_rec.attribute1,
3372 p_invoice_rec.attribute2,
3373 p_invoice_rec.attribute3,
3374 p_invoice_rec.attribute4,
3375 p_invoice_rec.attribute5,
3376 p_invoice_rec.attribute6,
3377 p_invoice_rec.attribute7,
3378 p_invoice_rec.attribute8,
3379 p_invoice_rec.attribute9,
3380 p_invoice_rec.attribute10,
3381 p_invoice_rec.attribute11,
3382 p_invoice_rec.attribute12,
3383 p_invoice_rec.attribute13,
3384 p_invoice_rec.attribute14,
3385 p_invoice_rec.attribute15,
3386 p_invoice_rec.global_attribute_category,
3387 p_invoice_rec.global_attribute1,
3388 p_invoice_rec.global_attribute2,
3389 p_invoice_rec.global_attribute3,
3390 p_invoice_rec.global_attribute4,
3391 p_invoice_rec.global_attribute5,
3392 p_invoice_rec.global_attribute6,
3393 p_invoice_rec.global_attribute7,
3394 p_invoice_rec.global_attribute8,
3395 p_invoice_rec.global_attribute9,
3396 p_invoice_rec.global_attribute10,
3397 p_invoice_rec.global_attribute11,
3398 p_invoice_rec.global_attribute12,
3399 p_invoice_rec.global_attribute13,
3400 p_invoice_rec.global_attribute14,
3401 p_invoice_rec.global_attribute15,
3402 p_invoice_rec.global_attribute16,
3403 p_invoice_rec.global_attribute17,
3404 p_invoice_rec.global_attribute18,
3405 p_invoice_rec.global_attribute19,
3406 p_invoice_rec.global_attribute20,
3407 '',
3408 decode(p_invoice_rec.source,'CREDIT CARD',
3409 'SelfService',
3410 'Both Pay',
3411 'SelfService',
3412 p_invoice_rec.source),
3413 p_invoice_rec.group_id,
3414 FND_GLOBAL.CONC_REQUEST_ID,
3415 p_invoice_rec.payment_cross_rate_type,
3416 p_invoice_rec.payment_cross_rate_date,
3417 p_invoice_rec.payment_cross_rate,
3418 p_invoice_rec.payment_currency_code,
3419 '',
3420 p_invoice_rec.doc_category_code,
3421 p_invoice_rec.voucher_num,
3422 p_vendor_rec.pay_group,
3423 '',
3424 '',
3425 p_invoice_rec.gl_date,
3426 nvl(decode(p_invoice_rec.accts_pay_ccid, -1, p_vendor_rec.liab_acc), p_vendor_rec.liab_acc),
3427 p_invoice_rec.org_id,
3428 p_invoice_rec.amount_app_to_discount,
3429 decode(p_invoice_rec.apply_advances_flag,
3430 'Y',
3431 p_invoice_rec.prepay_num,
3432 ''),
3433 decode(p_invoice_rec.apply_advances_flag,
3434 'Y',
3435 p_invoice_rec.prepay_dist_num,
3436 ''),
3437 decode(p_invoice_rec.apply_advances_flag,
3438 'Y',
3439 p_invoice_rec.amount_want_to_apply,
3440 ''),
3441 p_invoice_rec.prepay_gl_date,
3442 '',
3443 '',
3444 '',
3445 decode(p_vendor_rec.terms_date_basis,
3446 'Current',
3447 sysdate,
3448 p_invoice_rec.week_end_date),
3449 '',
3450 p_invoice_rec.paid_on_behalf_employee_id,
3451 p_vendor_rec.party_id,
3452 decode(p_invoice_rec.invoice_type_lookup_code,
3453 'PAYMENT REQUEST',
3454 p_vendor_rec.party_site_id,
3455 Decode(p_invoice_rec.is_contingent,'Y',p_vendor_rec.party_site_id,'')),
3456 l_payment_priority);
3457
3458 END InsertInvoiceInterface;
3459
3460 ------------------------------------------------------------------------------------------
3461 --- Bug: 6809570
3462 ------------------------------------------------------------------------------------------
3463 PROCEDURE InsertInvoiceLinesInterface(p_report_header_id IN NUMBER,
3464 p_invoice_id IN NUMBER,
3465 p_transfer_flag IN VARCHAR2,
3466 p_base_currency IN VARCHAR2,
3467 p_enable_recoverable_flag IN VARCHAR2) IS
3468 ------------------------------------------------------------------------------------------
3469 l_debug_info VARCHAR2(2000);
3470 BEGIN
3471
3472 IF g_debug_switch = 'Y' THEN
3473 l_debug_info := 'Insert into Invoice Lines Interface, p_invoice_id ' || p_invoice_id || ', p_report_header_id ' || p_report_header_id || ', p_transfer_flag ' || p_transfer_flag || ', p_enable_recoverable_flag ' || p_enable_recoverable_flag;
3474 fnd_file.put_line(fnd_file.log, l_debug_info);
3475 END IF;
3476 INSERT INTO AP_INVOICE_LINES_INTERFACE
3477 (INVOICE_ID,
3478 APPLICATION_ID,
3479 PRODUCT_TABLE,
3480 REFERENCE_KEY1,
3481 REFERENCE_KEY2,
3482 INVOICE_LINE_ID,
3483 LINE_NUMBER,
3484 LINE_TYPE_LOOKUP_CODE,
3485 LINE_GROUP_NUMBER,
3486 AMOUNT,
3487 ACCOUNTING_DATE,
3488 DESCRIPTION,
3489 AMOUNT_INCLUDES_TAX_FLAG,
3490 TAX_CLASSIFICATION_CODE,
3491 ITEM_DESCRIPTION,
3492 DIST_CODE_COMBINATION_ID,
3493 AWT_GROUP_ID,
3494 LAST_UPDATED_BY,
3495 LAST_UPDATE_DATE,
3496 LAST_UPDATE_LOGIN,
3497 CREATED_BY,
3498 CREATION_DATE,
3499 ATTRIBUTE_CATEGORY,
3500 ATTRIBUTE1,
3501 ATTRIBUTE2,
3502 ATTRIBUTE3,
3503 ATTRIBUTE4,
3504 ATTRIBUTE5,
3505 ATTRIBUTE6,
3506 ATTRIBUTE7,
3507 ATTRIBUTE8,
3508 ATTRIBUTE9,
3509 ATTRIBUTE10,
3510 ATTRIBUTE11,
3511 ATTRIBUTE12,
3512 ATTRIBUTE13,
3513 ATTRIBUTE14,
3514 ATTRIBUTE15,
3515 GLOBAL_ATTRIBUTE_CATEGORY,
3516 GLOBAL_ATTRIBUTE1,
3517 GLOBAL_ATTRIBUTE2,
3518 GLOBAL_ATTRIBUTE3,
3519 GLOBAL_ATTRIBUTE4,
3520 GLOBAL_ATTRIBUTE5,
3521 GLOBAL_ATTRIBUTE6,
3522 GLOBAL_ATTRIBUTE7,
3523 GLOBAL_ATTRIBUTE8,
3524 GLOBAL_ATTRIBUTE9,
3525 GLOBAL_ATTRIBUTE10,
3526 GLOBAL_ATTRIBUTE11,
3527 GLOBAL_ATTRIBUTE12,
3528 GLOBAL_ATTRIBUTE13,
3529 GLOBAL_ATTRIBUTE14,
3530 GLOBAL_ATTRIBUTE15,
3531 GLOBAL_ATTRIBUTE16,
3532 GLOBAL_ATTRIBUTE17,
3533 GLOBAL_ATTRIBUTE18,
3534 GLOBAL_ATTRIBUTE19,
3535 GLOBAL_ATTRIBUTE20,
3536 PROJECT_ID,
3537 TASK_ID,
3538 EXPENDITURE_TYPE,
3539 EXPENDITURE_ITEM_DATE,
3540 EXPENDITURE_ORGANIZATION_ID,
3541 PROJECT_ACCOUNTING_CONTEXT,
3542 PA_ADDITION_FLAG,
3543 PA_QUANTITY,
3544 STAT_AMOUNT,
3545 TYPE_1099,
3546 INCOME_TAX_REGION,
3547 ASSETS_TRACKING_FLAG,
3548 ORG_ID,
3549 REFERENCE_1,
3550 REFERENCE_2,
3551 TAX_RECOVERY_RATE,
3552 TAX_RECOVERY_OVERRIDE_FLAG,
3553 TAX_RECOVERABLE_FLAG,
3554 TAX_CODE_OVERRIDE_FLAG,
3555 TAX_CODE_ID,
3556 CREDIT_CARD_TRX_ID,
3557 AWARD_ID,
3558 TAXABLE_FLAG,
3559 COMPANY_PREPAID_INVOICE_ID,
3560 EXPENSE_GROUP,
3561 JUSTIFICATION,
3562 MERCHANT_DOCUMENT_NUMBER,
3563 MERCHANT_NAME,
3564 MERCHANT_REFERENCE,
3565 MERCHANT_TAXPAYER_ID,
3566 MERCHANT_TAX_REG_NUMBER,
3567 RECEIPT_CONVERSION_RATE,
3568 RECEIPT_CURRENCY_AMOUNT,
3569 RECEIPT_CURRENCY_CODE,
3570 COUNTRY_OF_SUPPLY
3571 --bug 8658097 starts
3572 ,EXPENSE_START_DATE
3573 ,EXPENSE_END_DATE
3574 --bug 8658097 ends
3575 )
3576 (SELECT
3577 p_invoice_id,
3578 200,
3579 'AP_EXPENSE_REPORT_LINES_ALL',
3580 xl.report_header_id,
3581 xl.report_line_id,
3582 AP_INVOICE_LINES_INTERFACE_S.nextval,
3583 xl.distribution_line_number,
3584 nvl(lc.lookup_code, '') line_type_lookup_code,
3585 '',
3586 to_char(nvl(ap_utilities_pkg.ap_round_currency(xl.amount,
3587 XH.default_currency_code),
3588 0)) distribution_amount,
3589 '' accounting_date,
3590 nvl(xl.item_description, '') item_description,
3591 xl.amount_includes_tax_flag,
3592 nvl(xl.vat_code, '') line_vat_code,
3593 nvl(xl.item_description, '') item_description,
3594 gcc.code_combination_id code_combination_id,
3595 nvl(to_char(xl.awt_group_id), ''),
3596 g_last_updated_by,
3597 sysdate,
3598 g_last_update_login,
3599 xl.created_by,
3600 sysdate,
3601 nvl(decode(p_transfer_flag, 'Y', xl.attribute_category), ''),
3602 nvl(decode(p_transfer_flag, 'Y', xl.attribute1), ''),
3603 nvl(decode(p_transfer_flag, 'Y', xl.attribute2), ''),
3604 nvl(decode(p_transfer_flag, 'Y', xl.attribute3), ''),
3605 nvl(decode(p_transfer_flag, 'Y', xl.attribute4), ''),
3606 nvl(decode(p_transfer_flag, 'Y', xl.attribute5), ''),
3607 nvl(decode(p_transfer_flag, 'Y', xl.attribute6), ''),
3608 nvl(decode(p_transfer_flag, 'Y', xl.attribute7), ''),
3609 nvl(decode(p_transfer_flag, 'Y', xl.attribute8), ''),
3610 nvl(decode(p_transfer_flag, 'Y', xl.attribute9), ''),
3611 nvl(decode(p_transfer_flag, 'Y', xl.attribute10), ''),
3612 nvl(decode(p_transfer_flag, 'Y', xl.attribute11), ''),
3613 nvl(decode(p_transfer_flag, 'Y', xl.attribute12), ''),
3614 nvl(decode(p_transfer_flag, 'Y', xl.attribute13), ''),
3615 nvl(decode(p_transfer_flag, 'Y', xl.attribute14), ''),
3616 nvl(decode(p_transfer_flag, 'Y', xl.attribute15), ''),
3617 xl.global_attribute_category,
3618 xl.global_attribute1,
3619 xl.global_attribute2,
3620 xl.global_attribute3,
3621 xl.global_attribute4,
3622 xl.global_attribute5,
3623 xl.global_attribute6,
3624 xl.global_attribute7,
3625 xl.global_attribute8,
3626 xl.global_attribute9,
3627 xl.global_attribute10,
3628 xl.global_attribute11,
3629 xl.global_attribute12,
3630 xl.global_attribute13,
3631 xl.global_attribute14,
3632 xl.global_attribute15,
3633 xl.global_attribute16,
3634 xl.global_attribute17,
3635 xl.global_attribute18,
3636 xl.global_attribute19,
3637 xl.global_attribute20,
3638 nvl(to_char(xl.project_id), ''),
3639 nvl(to_char(xl.task_id), ''),
3640 nvl(xl.expenditure_type, ''),
3641 nvl(to_char(xl.expenditure_item_date), ''),
3642 nvl(to_char(xl.expenditure_organization_id), ''),
3643 nvl(xl.project_accounting_context, ''),
3644 nvl2(xl.project_id, 'T', 'E') pa_addition_flag,
3645 nvl(to_char(xl.pa_quantity), ''),
3646 to_char(nvl(xl.stat_amount, '')) stat_amount,
3647 '' type_1099,
3648 '' income_tax_region,
3649 DECODE(nvl(gcc.account_type, 'x'), 'A', 'Y', 'N') assets_tracking_flag,
3650 XL.org_id org_id,
3651 nvl(xl.reference_1, ''),
3652 nvl(xl.reference_2, ''),
3653 '' tax_recovery_rate,
3654 'N' tax_recovery_override_flag,
3655 nvl(decode(p_enable_recoverable_flag,
3656 'Y',
3657 decode(xl.line_type_lookup_code, 'TAX', 'Y', 'N'),
3658 'N'),
3659 'N') tax_recoverable_flag,
3660 nvl(xl.tax_code_override_flag, 'N'),
3661 '',
3662 nvl(xl.credit_card_trx_id, ''),
3663 '' award_id,
3664 '',
3665 nvl(xl.company_prepaid_invoice_id, ''),
3666 nvl(xl.expense_group, ''),
3667 nvl(xl.justification, ''),
3668 nvl(xl.merchant_document_number, ''),
3669 nvl(xl.merchant_name, ''),
3670 nvl(xl.merchant_reference, ''),
3671 nvl(xl.merchant_taxpayer_id, ''),
3672 nvl(xl.merchant_tax_reg_number, ''),
3673 to_char(nvl(xl.receipt_conversion_rate, '')),
3674 to_char(nvl(xl.receipt_currency_amount, '')),
3675 nvl(xl.receipt_currency_code, ''),
3676 nvl(xl.country_of_supply, '')
3677 --bug 8658097 starts
3678 ,xl.start_expense_date
3679 ,xl.end_expense_date
3680 --bug 8658097 ends
3681 FROM ap_expense_report_lines XL,
3682 gl_code_combinations gcc,
3683 ap_lookup_codes lc,
3684 fnd_currencies F,
3685 ap_expense_report_headers XH
3686 WHERE XL.report_header_id = XH.report_header_id
3687 AND XH.report_header_id = p_report_header_id
3688 AND XL.code_combination_id = gcc.code_combination_id(+)
3689 AND nvl(XL.itemization_parent_id,0) <> -1 /* Itemization Project */
3690 AND lc.lookup_code(+) = XL.line_type_lookup_code
3691 AND lc.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
3692 AND F.currency_code = p_base_currency);
3693
3694
3695 IF g_debug_switch = 'Y' THEN
3696 l_debug_info := 'Done Insert into Invoice Lines Interface';
3697 fnd_file.put_line(fnd_file.log, l_debug_info);
3698 END IF;
3699 END InsertInvoiceLinesInterface;
3700
3701 PROCEDURE PrintVendorInfo(p_vendor_rec IN VendorInfoRecType) IS
3702 l_debug_info VARCHAR2(2000);
3703 BEGIN
3704 IF g_debug_switch = 'Y' THEN
3705 l_debug_info := '>p_vendor_rec.vendor_id := '||p_vendor_rec.vendor_id;
3706 fnd_file.put_line(fnd_file.log, l_debug_info);
3707 l_debug_info := '>p_vendor_rec.vendor_site_id := '||p_vendor_rec.vendor_site_id;
3708 fnd_file.put_line(fnd_file.log, l_debug_info);
3709 l_debug_info := '>p_vendor_rec.home_or_office := '||p_vendor_rec.home_or_office;
3710 fnd_file.put_line(fnd_file.log, l_debug_info);
3711 l_debug_info := '>p_vendor_rec.employee_id := '||p_vendor_rec.employee_id;
3712 fnd_file.put_line(fnd_file.log, l_debug_info);
3713 l_debug_info := '>p_vendor_rec.vendor_name := '||p_vendor_rec.vendor_name;
3714 fnd_file.put_line(fnd_file.log, l_debug_info);
3715 l_debug_info := '>p_vendor_rec.org_id := '||p_vendor_rec.org_id;
3716 fnd_file.put_line(fnd_file.log, l_debug_info);
3717 l_debug_info := '>p_vendor_rec.address_line_1 := '||p_vendor_rec.address_line_1;
3718 fnd_file.put_line(fnd_file.log, l_debug_info);
3719 l_debug_info := '>p_vendor_rec.address_line_2 := '||p_vendor_rec.address_line_2;
3720 fnd_file.put_line(fnd_file.log, l_debug_info);
3721 l_debug_info := '>p_vendor_rec.address_line_3 := '||p_vendor_rec.address_line_3;
3722 fnd_file.put_line(fnd_file.log, l_debug_info);
3723 l_debug_info := '>p_vendor_rec.city := '||p_vendor_rec.city;
3724 fnd_file.put_line(fnd_file.log, l_debug_info);
3725 l_debug_info := '>p_vendor_rec.state := '||p_vendor_rec.state;
3726 fnd_file.put_line(fnd_file.log, l_debug_info);
3727 l_debug_info := '>p_vendor_rec.postal_code := '||p_vendor_rec.postal_code;
3728 fnd_file.put_line(fnd_file.log, l_debug_info);
3729 l_debug_info := '>p_vendor_rec.province := '||p_vendor_rec.province;
3730 fnd_file.put_line(fnd_file.log, l_debug_info);
3731 l_debug_info := '>p_vendor_rec.county := '||p_vendor_rec.county;
3732 fnd_file.put_line(fnd_file.log, l_debug_info);
3733 l_debug_info := '>p_vendor_rec.country := '||p_vendor_rec.country;
3734 fnd_file.put_line(fnd_file.log, l_debug_info);
3735 l_debug_info := '>p_vendor_rec.style := '||p_vendor_rec.style;
3736 fnd_file.put_line(fnd_file.log, l_debug_info);
3737
3738 l_debug_info := '>p_vendor_rec.pay_group := '||p_vendor_rec.pay_group;
3739 fnd_file.put_line(fnd_file.log, l_debug_info);
3740 l_debug_info := '>p_vendor_rec.terms_date_basis := '||p_vendor_rec.terms_date_basis;
3741 fnd_file.put_line(fnd_file.log, l_debug_info);
3742 l_debug_info := '>p_vendor_rec.liab_acc := '||p_vendor_rec.liab_acc;
3743 fnd_file.put_line(fnd_file.log, l_debug_info);
3744 l_debug_info := '>p_vendor_rec.terms_id := '||p_vendor_rec.terms_id;
3745 fnd_file.put_line(fnd_file.log, l_debug_info);
3746 l_debug_info := '>p_vendor_rec.payment_priority := '||p_vendor_rec.payment_priority;
3747 fnd_file.put_line(fnd_file.log, l_debug_info);
3748 l_debug_info := '>p_vendor_rec.prepay_ccid := '||p_vendor_rec.prepay_ccid;
3749 fnd_file.put_line(fnd_file.log, l_debug_info);
3750 l_debug_info := '>p_vendor_rec.always_take_disc_flag := '||p_vendor_rec.always_take_disc_flag;
3751 fnd_file.put_line(fnd_file.log, l_debug_info);
3752 l_debug_info := '>p_vendor_rec.pay_date_basis := '||p_vendor_rec.pay_date_basis;
3753 fnd_file.put_line(fnd_file.log, l_debug_info);
3754 l_debug_info := '>p_vendor_rec.vendor_num := '||p_vendor_rec.vendor_num;
3755 fnd_file.put_line(fnd_file.log, l_debug_info);
3756 l_debug_info := '>p_vendor_rec.allow_awt_flag := '||p_vendor_rec.allow_awt_flag;
3757 fnd_file.put_line(fnd_file.log, l_debug_info);
3758 l_debug_info := '>p_vendor_rec.party_id := '||p_vendor_rec.party_id;
3759 fnd_file.put_line(fnd_file.log, l_debug_info);
3760 END IF;
3761 END PrintVendorInfo;
3762
3763 PROCEDURE PrintVendorSiteInfo(p_vendor_site_rec IN AP_VENDOR_PUB_PKG.r_vendor_site_rec_type) IS
3764 l_debug_info VARCHAR2(2000);
3765 BEGIN
3766 IF g_debug_switch = 'Y' THEN
3767 l_debug_info := '>p_vendor_site_rec.vendor_id := '||p_vendor_site_rec.vendor_id;
3768 fnd_file.put_line(fnd_file.log, l_debug_info);
3769 l_debug_info := '>p_vendor_site_rec.org_id := '||p_vendor_site_rec.org_id;
3770 fnd_file.put_line(fnd_file.log, l_debug_info);
3771 l_debug_info := '>p_vendor_site_rec.org_name := '||p_vendor_site_rec.org_name;
3772 fnd_file.put_line(fnd_file.log, l_debug_info);
3773 l_debug_info := '>p_vendor_site_rec.vendor_site_code := '||p_vendor_site_rec.vendor_site_code;
3774 fnd_file.put_line(fnd_file.log, l_debug_info);
3775 l_debug_info := '>p_vendor_site_rec.pay_site_flag := '||p_vendor_site_rec.pay_site_flag;
3776 fnd_file.put_line(fnd_file.log, l_debug_info);
3777 l_debug_info := '>p_vendor_site_rec.address_line1 := '||p_vendor_site_rec.address_line1;
3778 fnd_file.put_line(fnd_file.log, l_debug_info);
3779 l_debug_info := '>p_vendor_site_rec.address_line2 := '||p_vendor_site_rec.address_line2;
3780 fnd_file.put_line(fnd_file.log, l_debug_info);
3781 l_debug_info := '>p_vendor_site_rec.address_line3 := '||p_vendor_site_rec.address_line3;
3782 fnd_file.put_line(fnd_file.log, l_debug_info);
3783 l_debug_info := '>p_vendor_site_rec.city := '||p_vendor_site_rec.city;
3784 fnd_file.put_line(fnd_file.log, l_debug_info);
3785 l_debug_info := '>p_vendor_site_rec.state := '||p_vendor_site_rec.state;
3786 fnd_file.put_line(fnd_file.log, l_debug_info);
3787 l_debug_info := '>p_vendor_site_rec.zip := '||p_vendor_site_rec.zip;
3788 fnd_file.put_line(fnd_file.log, l_debug_info);
3789 l_debug_info := '>p_vendor_site_rec.province := '||p_vendor_site_rec.province;
3790 fnd_file.put_line(fnd_file.log, l_debug_info);
3791 l_debug_info := '>p_vendor_site_rec.county := '||p_vendor_site_rec.county;
3792 fnd_file.put_line(fnd_file.log, l_debug_info);
3793 l_debug_info := '>p_vendor_site_rec.country := '||p_vendor_site_rec.country;
3794 fnd_file.put_line(fnd_file.log, l_debug_info);
3795 l_debug_info := '>p_vendor_site_rec.address_style := '||p_vendor_site_rec.address_style;
3796 fnd_file.put_line(fnd_file.log, l_debug_info);
3797 END IF;
3798 END PrintVendorSiteInfo;
3799
3800 ------------------------------------------------------------------------
3801 FUNCTION GetVendorInfo(p_vendor_rec IN OUT NOCOPY VendorInfoRecType,
3802 p_reject_code OUT NOCOPY VARCHAR2)
3803 RETURN BOOLEAN IS
3804 ------------------------------------------------------------------------
3805 CURSOR c_supplier_numbering_method IS
3806 SELECT supplier_numbering_method
3807 FROM ap_product_setup
3808 WHERE rownum = 1;
3809
3810 CURSOR c_vendor_site(l_vendor_id IN NUMBER, home_or_office IN VARCHAR2, l_org_id IN NUMBER ) IS
3811 SELECT vendor_site_id,
3812 nvl(pay_group_lookup_code, ''),
3813 nvl(terms_date_basis, ''),
3814 nvl(accts_pay_code_combination_id, -1),
3815 nvl(terms_id, -1),
3816 allow_awt_flag,
3817 party_site_id
3818 FROM ap_supplier_sites s, fnd_lookup_values l
3819 WHERE s.vendor_site_code || '' = SUBSTRB(UPPER(l.meaning), 1, 15)
3820 AND s.vendor_id = l_vendor_id
3821 AND l.lookup_type = 'HOME_OFFICE'
3822 AND l.lookup_code = home_or_office
3823 AND s.org_id = l_org_id;
3824
3825 CURSOR c_vendor_info(l_vendor_id IN NUMBER) IS
3826 SELECT nvl(terms_date_basis, ''),
3827 nvl(terms_id, -1),
3828 nvl(pay_group_lookup_code, ''),
3829 nvl(payment_priority, -1),
3830 nvl(accts_pay_code_combination_id, -1),
3831 nvl(prepay_code_combination_id, -1),
3832 nvl(always_take_disc_flag, 'N'),
3833 nvl(pay_date_basis_lookup_code, ''),
3834 vendor_name,
3835 segment1,
3836 party_id
3837 FROM ap_suppliers
3838 WHERE vendor_id = l_vendor_id;
3839
3840 CURSOR c_vendor_site_known(l_vendor_site_id IN NUMBER) IS
3841 SELECT nvl(pay_group_lookup_code, ''),
3842 nvl(terms_date_basis, ''),
3843 nvl(accts_pay_code_combination_id, -1),
3844 nvl(terms_id, -1),
3845 allow_awt_flag,
3846 party_site_id
3847 FROM ap_supplier_sites s
3848 WHERE vendor_site_id = l_vendor_site_id;
3849
3850 CURSOR c_party_id(l_employee_id IN NUMBER) IS
3851 SELECT party_id
3852 FROM per_employees_x
3853 WHERE employee_id = l_employee_id
3854 AND rownum = 1;
3855
3856 --Bug#7012808 - Payment Priority Defaulted to 99
3857 CURSOR c_create_supplier(l_org_id IN NUMBER) IS
3858 SELECT create_employee_vendor_flag, base_currency_code, employee_payment_priority
3859 FROM ap_system_parameters_all
3860 WHERE org_id = l_org_id;
3861
3862
3863 l_vendor_id NUMBER;
3864 l_vendor_site_id NUMBER;
3865 l_party_id NUMBER;
3866 l_party_site_id NUMBER;
3867 l_location_id NUMBER;
3868 l_terms_date_basis ap_suppliers.terms_date_basis%TYPE;
3869 l_terms_id ap_suppliers.terms_id%TYPE;
3870 l_pay_group ap_suppliers.pay_group_lookup_code%TYPE;
3871 l_payment_priority ap_suppliers.payment_priority%TYPE;
3872 l_liab_acc ap_suppliers.accts_pay_code_combination_id%TYPE;
3873 l_prepay_ccid ap_suppliers.prepay_code_combination_id%TYPE;
3874 l_always_take_disc_flag ap_suppliers.always_take_disc_flag%TYPE;
3875 l_pay_date_basis ap_suppliers.pay_date_basis_lookup_code%TYPE;
3876 l_vendor_name ap_suppliers.vendor_name%TYPE;
3877 l_vendor_num ap_suppliers.segment1%TYPE;
3878
3879 l_create_vendor BOOLEAN := FALSE;
3880
3881 l_site_pay_group ap_supplier_sites.pay_group_lookup_code%TYPE;
3882 l_site_terms_date_basis ap_supplier_sites.terms_date_basis%TYPE;
3883 l_site_liab_acc ap_supplier_sites.accts_pay_code_combination_id%TYPE;
3884 l_site_terms_id ap_supplier_sites.terms_id%TYPE;
3885 l_site_allow_awt_flag ap_supplier_sites.allow_awt_flag%TYPE;
3886 l_debug_info VARCHAR2(2000);
3887 l_duplicate_vendor VARCHAR2(2);
3888 l_val_return_status VARCHAR2(50);
3889 l_val_msg_count NUMBER;
3890 l_val_msg_data VARCHAR2(1000);
3891 l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
3892 l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
3893 l_create_vendor_flag ap_system_parameters_all.create_employee_vendor_flag%TYPE;
3894
3895 l_supplier_numbering_method ap_product_setup.supplier_numbering_method%TYPE;
3896 l_base_currency_code ap_system_parameters.base_currency_code%TYPE;
3897
3898 BEGIN
3899
3900 PrintVendorInfo(p_vendor_rec);
3901
3902 IF (nvl(p_vendor_rec.vendor_id, -1) = -1) THEN
3903
3904 --------------------------------------------------------------
3905 l_debug_info := 'Get Vendor Info when vendor id is not known';
3906 --------------------------------------------------------------
3907 IF g_debug_switch = 'Y' THEN
3908 fnd_file.put_line(fnd_file.log, l_debug_info);
3909 END IF;
3910
3911 BEGIN
3912 SELECT vendor_id,
3913 DECODE(employee_id,
3914 NULL,
3915 DECODE(nvl(vendor_type_lookup_code, 'EMPLOYEE'),
3916 'EMPLOYEE',
3917 'N',
3918 'Y'),
3919 p_vendor_rec.employee_id,
3920 'N',
3921 'Y'),
3922 party_id
3923 INTO l_vendor_id,
3924 l_duplicate_vendor,
3925 l_party_id
3926 FROM ap_suppliers
3927 WHERE employee_id = p_vendor_rec.employee_id;
3928 EXCEPTION
3929 WHEN NO_DATA_FOUND THEN
3930 fnd_file.put_line(fnd_file.log, 'Employee Id '||p_vendor_rec.employee_id||' is not found');
3931 l_vendor_id := -1;
3932 WHEN TOO_MANY_ROWS THEN
3933 p_reject_code := 'Create duplicate vendor';
3934 RETURN (FALSE);
3935 END;
3936
3937 IF g_debug_switch = 'Y' THEN
3938 l_debug_info := 'l_vendor_id := '||l_vendor_id;
3939 fnd_file.put_line(fnd_file.log, l_debug_info);
3940 l_debug_info := 'l_duplicate_vendor := '||l_duplicate_vendor;
3941 fnd_file.put_line(fnd_file.log, l_debug_info);
3942 l_debug_info := 'l_party_id := '||l_party_id;
3943 fnd_file.put_line(fnd_file.log, l_debug_info);
3944 END IF;
3945
3946 IF l_duplicate_vendor = 'Y' THEN
3947 p_reject_code := 'Create duplicate vendor';
3948 RETURN (FALSE);
3949 END IF;
3950
3951 END IF; /* (nvl(p_vendor_rec.vendor_id, -1) = -1) */
3952
3953
3954 IF (l_vendor_id is not null) then
3955 p_vendor_rec.vendor_id := l_vendor_id;
3956 ELSE
3957 l_vendor_id := p_vendor_rec.vendor_id;
3958 END IF;
3959
3960 l_create_vendor_flag := NULL; -- Bug: 7004219, Create Employee as supplier ignored
3961 l_payment_priority := NULL; --Bug#7012808 - Payment Priority Defaulted to 99
3962 OPEN c_create_supplier(p_vendor_rec.org_id);
3963 FETCH c_create_supplier INTO l_create_vendor_flag, l_base_currency_code, l_payment_priority;
3964 CLOSE c_create_supplier;
3965
3966
3967 IF (nvl(p_vendor_rec.vendor_id, -1) <> -1) THEN
3968 ----------------------------------------------------------
3969 l_debug_info := 'Get Vendor Info when vendor id is known';
3970 ----------------------------------------------------------
3971 IF g_debug_switch = 'Y' THEN
3972 fnd_file.put_line(fnd_file.log, l_debug_info);
3973 END IF;
3974
3975 l_payment_priority := NULL;
3976
3977 -- call AP_VENDOR_PUB_PKG.Validate_Vendor
3978 OPEN c_vendor_info(p_vendor_rec.vendor_id);
3979
3980 FETCH c_vendor_info
3981 INTO l_terms_date_basis,
3982 l_terms_id,
3983 l_pay_group,
3984 l_payment_priority,
3985 l_liab_acc,
3986 l_prepay_ccid,
3987 l_always_take_disc_flag,
3988 l_pay_date_basis,
3989 l_vendor_name,
3990 l_vendor_num,
3991 l_party_id ;
3992
3993 CLOSE c_vendor_info;
3994
3995 ELSE
3996 ------------------------------------
3997 l_debug_info := 'Get party_id';
3998 ------------------------------------
3999 IF g_debug_switch = 'Y' THEN
4000 fnd_file.put_line(fnd_file.log, l_debug_info);
4001 END IF;
4002
4003 OPEN c_party_id(p_vendor_rec.employee_id);
4004 FETCH c_party_id INTO l_vendor_rec.party_id;
4005 CLOSE c_party_id;
4006
4007 ------------------------------------
4008 l_debug_info := 'l_vendor_rec.party_id = '||l_vendor_rec.party_id;
4009 ------------------------------------
4010 IF g_debug_switch = 'Y' THEN
4011 fnd_file.put_line(fnd_file.log, l_debug_info);
4012 END IF;
4013 if nvl(l_vendor_rec.party_id, -1) = -1 then
4014 p_reject_code := 'INVALID PARTY';
4015 RETURN (FALSE);
4016 end if;
4017
4018 ------------------------------------
4019 l_debug_info := 'Checking Automatic Create Employee as Supplier option';
4020 ------------------------------------
4021 IF g_debug_switch = 'Y' THEN
4022 fnd_file.put_line(fnd_file.log, l_debug_info);
4023 END IF;
4024
4025
4026 if(nvl(l_create_vendor_flag, 'N') <> 'Y') then
4027
4028 ------------------------------------
4029 l_debug_info := 'Automatic Create Employee as Supplier is not checked in Payable Options';
4030 ------------------------------------
4031 IF g_debug_switch = 'Y' THEN
4032 fnd_file.put_line(fnd_file.log, l_debug_info);
4033 END IF;
4034
4035 p_reject_code := 'Not a vendor';
4036 RETURN (FALSE);
4037 end if;
4038
4039
4040 ------------------------------------
4041 l_debug_info := 'Checking ap_product_setup.supplier_numbering_method';
4042 ------------------------------------
4043 IF g_debug_switch = 'Y' THEN
4044 fnd_file.put_line(fnd_file.log, l_debug_info);
4045 END IF;
4046
4047 OPEN c_supplier_numbering_method;
4048 FETCH c_supplier_numbering_method into l_supplier_numbering_method;
4049 CLOSE c_supplier_numbering_method;
4050
4051 if (l_supplier_numbering_method <> 'AUTOMATIC') then
4052
4053 ------------------------------------
4054 l_debug_info := 'ap_product_setup.supplier_numbering_method is not AUTOMATIC';
4055 ------------------------------------
4056 IF g_debug_switch = 'Y' THEN
4057 fnd_file.put_line(fnd_file.log, l_debug_info);
4058 END IF;
4059
4060 p_reject_code := 'Create as vendor';
4061 RETURN (FALSE);
4062 end if;
4063
4064 ------------------------------------
4065 l_debug_info := 'Creating a vendor (AP_VENDOR_PUB_PKG.create_vendor())';
4066 ------------------------------------
4067 IF g_debug_switch = 'Y' THEN
4068 fnd_file.put_line(fnd_file.log, l_debug_info);
4069 END IF;
4070
4071 l_create_vendor := TRUE;
4072 l_pay_group := p_vendor_rec.pay_group;
4073 l_terms_id := p_vendor_rec.terms_id;
4074 l_vendor_rec.pay_group_lookup_code := p_vendor_rec.pay_group;
4075 l_vendor_rec.terms_id := p_vendor_rec.terms_id;
4076 l_vendor_rec.vendor_name := p_vendor_rec.vendor_name;
4077 l_vendor_rec.employee_id := p_vendor_rec.employee_id;
4078 l_vendor_rec.vendor_type_lookup_code := 'EMPLOYEE';
4079 --bug 6795742
4080 l_vendor_rec.invoice_currency_code := l_base_currency_code;
4081 l_vendor_rec.payment_currency_code := l_base_currency_code;
4082 l_vendor_rec.payment_priority := l_payment_priority;--Bug#7012808 - Payment Priority Defaulted to 99
4083
4084 AP_VENDOR_PUB_PKG.create_vendor( p_api_version => 1.0,
4085 p_init_msg_list => FND_API.G_FALSE,
4086 p_commit => FND_API.G_FALSE,
4087 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4088 x_return_status => l_val_return_status,
4089 x_msg_count => l_val_msg_count,
4090 x_msg_data => l_val_msg_data,
4091 p_vendor_rec => l_vendor_rec,
4092 x_vendor_id => l_vendor_id,
4093 x_party_id => l_party_id);
4094
4095 if l_party_id is null then
4096 l_party_id := l_vendor_rec.party_id;
4097 end if;
4098
4099 IF g_debug_switch = 'Y' THEN
4100
4101 l_debug_info := 'l_val_return_status := '||l_val_return_status;
4102 fnd_file.put_line(fnd_file.log, l_debug_info);
4103
4104 l_debug_info := 'l_val_msg_count := '||l_val_msg_count;
4105 fnd_file.put_line(fnd_file.log, l_debug_info);
4106
4107 if (nvl(l_val_msg_count, 0) > 1) then
4108 for i in 1..l_val_msg_count
4109 loop
4110 l_debug_info := 'l_val_msg_data('||i||') := '||substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
4111 fnd_file.put_line(fnd_file.log, l_debug_info);
4112 end loop;
4113 else
4114 l_debug_info := 'l_val_msg_data := '||l_val_msg_data;
4115 fnd_file.put_line(fnd_file.log, l_debug_info);
4116 end if;
4117
4118 l_debug_info := 'l_vendor_id := '||l_vendor_id;
4119 fnd_file.put_line(fnd_file.log, l_debug_info);
4120 l_debug_info := 'l_party_id := '||l_party_id;
4121 fnd_file.put_line(fnd_file.log, l_debug_info);
4122
4123 END IF;
4124
4125 IF l_val_return_status = FND_API.G_RET_STS_SUCCESS THEN
4126 p_vendor_rec.vendor_id := l_vendor_id;
4127 ELSE
4128
4129 if (nvl(l_val_msg_count, 0) > 1) then
4130 for i in 1..l_val_msg_count
4131 loop
4132 p_reject_code := substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
4133 if (p_reject_code is not null) then
4134 exit;
4135 end if;
4136 end loop;
4137 else
4138 p_reject_code := substrb(l_val_msg_data, 1, 30);
4139 end if;
4140
4141 RETURN (FALSE);
4142 END IF;
4143
4144 END IF; /* (nvl(p_vendor_rec.vendor_id, -1) <> -1) */
4145
4146
4147 IF (nvl(p_vendor_rec.vendor_site_id, -1) <> -1) THEN
4148 --------------------------------------------------------------------
4149 l_debug_info := 'Get Vendor Site Info when vendor site id is known';
4150 --------------------------------------------------------------------
4151 IF g_debug_switch = 'Y' THEN
4152 fnd_file.put_line(fnd_file.log, l_debug_info);
4153 END IF;
4154
4155
4156 OPEN c_vendor_site_known(p_vendor_rec.vendor_site_id);
4157
4158 FETCH c_vendor_site_known
4159 INTO l_site_pay_group,
4160 l_site_terms_date_basis,
4161 l_site_liab_acc,
4162 l_site_terms_id,
4163 l_site_allow_awt_flag,
4164 l_party_site_id;
4165
4166 CLOSE c_vendor_site_known;
4167
4168 ELSE
4169 -------------------------------------------------------------
4170 l_debug_info := 'Get Vendor Site Info for Employee when vendor site id is not known';
4171 -------------------------------------------------------------
4172 IF g_debug_switch = 'Y' THEN
4173 fnd_file.put_line(fnd_file.log, l_debug_info);
4174 END IF;
4175
4176 OPEN c_vendor_site(p_vendor_rec.vendor_id, p_vendor_rec.home_or_office, p_vendor_rec.org_id);
4177
4178 FETCH c_vendor_site
4179 INTO p_vendor_rec.vendor_site_id,
4180 l_site_pay_group,
4181 l_site_terms_date_basis,
4182 l_site_liab_acc,
4183 l_site_terms_id,
4184 l_site_allow_awt_flag,
4185 l_party_site_id;
4186
4187 CLOSE c_vendor_site;
4188
4189 END IF; /* (nvl(p_vendor_rec.vendor_site_id, -1) <> -1) */
4190
4191
4192 IF (nvl(p_vendor_rec.vendor_site_id, -1) = -1) THEN
4193 -----------------------------------------
4194 l_debug_info := 'Creating a vendor site (AP_VENDOR_PUB_PKG.create_vendor_site())';
4195 -----------------------------------------
4196 IF g_debug_switch = 'Y' THEN
4197 fnd_file.put_line(fnd_file.log, l_debug_info);
4198 END IF;
4199
4200 -- Bug: 13493260, Do not create a site when the the payables option is not set.
4201 if(nvl(l_create_vendor_flag, 'N') <> 'Y') then
4202
4203 ------------------------------------
4204 l_debug_info := 'Automatic Create Employee as Supplier is not checked in Payable Options, cannot create site';
4205 ------------------------------------
4206 IF g_debug_switch = 'Y' THEN
4207 fnd_file.put_line(fnd_file.log, l_debug_info);
4208 END IF;
4209
4210 p_reject_code := 'Invalid vendor site';
4211 RETURN (FALSE);
4212 end if;
4213
4214 l_vendor_site_rec.vendor_id := p_vendor_rec.vendor_id;
4215 l_vendor_site_rec.org_id := p_vendor_rec.org_id;
4216 l_vendor_site_rec.pay_group_lookup_code := p_vendor_rec.pay_group;
4217 l_vendor_site_rec.terms_id := p_vendor_rec.terms_id;
4218 l_vendor_site_rec.party_site_id := l_party_site_id;
4219
4220 if (p_vendor_rec.home_or_office = 'O') then
4221 l_vendor_site_rec.vendor_site_code := 'OFFICE';
4222 elsif (p_vendor_rec.home_or_office = 'H') then
4223 l_vendor_site_rec.vendor_site_code := 'HOME';
4224 elsif (p_vendor_rec.home_or_office = 'P') then
4225 l_vendor_site_rec.vendor_site_code := 'PROVISIONAL';--Bug#7207375 - Allow payment of Expense Report to Temporary Address
4226 else
4227 p_reject_code := 'Invalid vendor site';
4228 RETURN (FALSE);
4229 end if;
4230 l_vendor_site_rec.pay_site_flag := 'Y';
4231 --bug 6795742
4232 l_vendor_site_rec.invoice_currency_code := l_base_currency_code;
4233 l_vendor_site_rec.payment_currency_code := l_base_currency_code;
4234 l_vendor_site_rec.payment_priority := l_payment_priority; --Bug#7012808 - Payment Priority Defaulted to 99
4235 -- bug 5350423 - supplier creation should not pass address info
4236 --l_vendor_site_rec.address_line1 := p_vendor_rec.address_line_1;
4237 --l_vendor_site_rec.address_line2 := p_vendor_rec.address_line_2;
4238 --l_vendor_site_rec.address_line3 := p_vendor_rec.address_line_3;
4239 --l_vendor_site_rec.city := p_vendor_rec.city;
4240 --l_vendor_site_rec.state := p_vendor_rec.state;
4241 --l_vendor_site_rec.zip := p_vendor_rec.postal_code;
4242 --l_vendor_site_rec.province := p_vendor_rec.province;
4243 --l_vendor_site_rec.county := p_vendor_rec.county;
4244 --l_vendor_site_rec.country := p_vendor_rec.country;
4245 --l_vendor_site_rec.address_style := p_vendor_rec.style;
4246
4247 PrintVendorSiteInfo(l_vendor_site_rec);
4248
4249 AP_VENDOR_PUB_PKG.create_vendor_site (
4250 p_api_version => 1.0,
4251 p_init_msg_list => FND_API.G_FALSE,
4252 p_commit => FND_API.G_FALSE,
4253 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4254 x_return_status => l_val_return_status,
4255 x_msg_count => l_val_msg_count,
4256 x_msg_data => l_val_msg_data,
4257 p_vendor_site_rec => l_vendor_site_rec,
4258 x_vendor_site_id => l_vendor_site_id,
4259 x_party_site_id => l_party_site_id,
4260 x_location_id => l_location_id);
4261 --Bug#7207375 - party_site_id is initialized later.
4262 /*IF l_party_site_id is null THEN
4263 l_party_site_id := l_vendor_site_rec.party_site_id;
4264 END IF;*/
4265
4266 IF g_debug_switch = 'Y' THEN
4267
4268 l_debug_info := 'l_val_return_status := '||l_val_return_status;
4269 fnd_file.put_line(fnd_file.log, l_debug_info);
4270
4271 l_debug_info := 'l_val_msg_count := '||l_val_msg_count;
4272 fnd_file.put_line(fnd_file.log, l_debug_info);
4273
4274 if (nvl(l_val_msg_count, 0) > 1) then
4275 for i in 1..l_val_msg_count
4276 loop
4277 l_debug_info := 'l_val_msg_data('||i||') := '||substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
4278 fnd_file.put_line(fnd_file.log, l_debug_info);
4279 end loop;
4280 else
4281 l_debug_info := 'l_val_msg_data := '||l_val_msg_data;
4282 fnd_file.put_line(fnd_file.log, l_debug_info);
4283 end if;
4284
4285 l_debug_info := 'l_vendor_site_id := '||l_vendor_site_id;
4286 fnd_file.put_line(fnd_file.log, l_debug_info);
4287 l_debug_info := 'l_party_site_id := '||l_party_site_id;
4288 fnd_file.put_line(fnd_file.log, l_debug_info);
4289 l_debug_info := 'l_location_id := '||l_location_id;
4290 fnd_file.put_line(fnd_file.log, l_debug_info);
4291 END IF;
4292
4293
4294 IF l_val_return_status = FND_API.G_RET_STS_SUCCESS THEN
4295 p_vendor_rec.vendor_site_id := l_vendor_site_id;
4296
4297 --Bug#7207375 - Initialize vendor details from site.
4298 OPEN c_vendor_site_known(p_vendor_rec.vendor_site_id);
4299
4300 FETCH c_vendor_site_known
4301 INTO l_site_pay_group,
4302 l_site_terms_date_basis,
4303 l_site_liab_acc,
4304 l_site_terms_id,
4305 l_site_allow_awt_flag,
4306 l_party_site_id;
4307 CLOSE c_vendor_site_known;
4308
4309 ELSE
4310
4311 if (nvl(l_val_msg_count, 0) > 1) then
4312 for i in 1..l_val_msg_count
4313 loop
4314 p_reject_code := substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
4315 if (p_reject_code is not null) then
4316 exit;
4317 end if;
4318 end loop;
4319 else
4320 p_reject_code := substrb(l_val_msg_data, 1, 30);
4321 end if;
4322
4323 RETURN (FALSE);
4324 END IF;
4325
4326 END IF; /* (nvl(p_vendor_rec.vendor_site_id, -1) = -1) */
4327
4328
4329 --------------------------------------------------------------------------------
4330 l_debug_info := 'Vendor Site ID got is '|| to_char(p_vendor_rec.vendor_site_id);
4331 --------------------------------------------------------------------------------
4332 IF g_debug_switch = 'Y' THEN
4333 fnd_file.put_line(fnd_file.log, l_debug_info);
4334 END IF;
4335
4336 if (not l_create_vendor) then
4337 l_vendor_site_rec.vendor_id := p_vendor_rec.vendor_id;
4338 l_vendor_site_rec.org_id := p_vendor_rec.org_id;
4339 if (p_vendor_rec.home_or_office = 'O') then
4340 l_vendor_site_rec.vendor_site_code := 'OFFICE';
4341 elsif (p_vendor_rec.home_or_office = 'H') then
4342 l_vendor_site_rec.vendor_site_code := 'HOME';
4343 elsif (p_vendor_rec.home_or_office = 'P') then
4344 l_vendor_site_rec.vendor_site_code := 'PROVISIONAL';--Bug#7207375 - Allow payment of Expense Report to Temporary Address
4345 end if;
4346 l_vendor_site_rec.pay_site_flag := 'Y';
4347 -- bug 5350423 - supplier creation should not pass address info
4348 --l_vendor_site_rec.address_line1 := p_vendor_rec.address_line_1;
4349 --l_vendor_site_rec.address_line2 := p_vendor_rec.address_line_2;
4350 --l_vendor_site_rec.address_line3 := p_vendor_rec.address_line_3;
4351 --l_vendor_site_rec.city := p_vendor_rec.city;
4352 --l_vendor_site_rec.state := p_vendor_rec.state;
4353 --l_vendor_site_rec.zip := p_vendor_rec.postal_code;
4354 --l_vendor_site_rec.province := p_vendor_rec.province;
4355 --l_vendor_site_rec.county := p_vendor_rec.county;
4356 --l_vendor_site_rec.country := p_vendor_rec.country;
4357 --l_vendor_site_rec.address_style := p_vendor_rec.style;
4358
4359 -------------------------------------
4360 l_debug_info := 'Update Vendor Site';
4361 -------------------------------------
4362 IF g_debug_switch = 'Y' THEN
4363 fnd_file.put_line(fnd_file.log, l_debug_info);
4364 END IF;
4365
4366 PrintVendorSiteInfo(l_vendor_site_rec);
4367
4368 /*
4369 AP_VENDOR_PUB_PKG.update_vendor_site(
4370 p_api_version => 1.0,
4371 p_init_msg_list => FND_API.G_FALSE,
4372 p_commit => FND_API.G_FALSE,
4373 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4374 x_return_status => l_val_return_status,
4375 x_msg_count => l_val_msg_count,
4376 x_msg_data => l_val_msg_data,
4377 p_vendor_site_rec => l_vendor_site_rec,
4378 p_vendor_site_id => l_vendor_site_id);
4379
4380 IF g_debug_switch = 'Y' THEN
4381
4382 l_debug_info := 'l_val_return_status := '||l_val_return_status;
4383 fnd_file.put_line(fnd_file.log, l_debug_info);
4384
4385 l_debug_info := 'l_val_msg_count := '||l_val_msg_count;
4386 fnd_file.put_line(fnd_file.log, l_debug_info);
4387
4388 if (nvl(l_val_msg_count, 0) > 1) then
4389 for i in 1..l_val_msg_count
4390 loop
4391 l_debug_info := 'l_val_msg_data('||i||') := '||substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
4392 fnd_file.put_line(fnd_file.log, l_debug_info);
4393 end loop;
4394 else
4395 l_debug_info := 'l_val_msg_data := '||l_val_msg_data;
4396 fnd_file.put_line(fnd_file.log, l_debug_info);
4397 end if;
4398
4399 PrintVendorSiteInfo(l_vendor_site_rec);
4400
4401 l_debug_info := 'l_vendor_site_id := '||l_vendor_site_id;
4402 fnd_file.put_line(fnd_file.log, l_debug_info);
4403 END IF;
4404
4405 IF l_val_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4406
4407 if (nvl(l_val_msg_count, 0) > 1) then
4408 for i in 1..l_val_msg_count
4409 loop
4410 p_reject_code := substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
4411 end loop;
4412 else
4413 p_reject_code := substrb(l_val_msg_data, 1, 30);
4414 end if;
4415
4416 RETURN (FALSE);
4417 END IF;
4418 */
4419
4420 end if; /* (not l_create_vendor) */
4421
4422 p_vendor_rec.pay_group := nvl(l_site_pay_group,
4423 l_pay_group);
4424 p_vendor_rec.terms_date_basis := nvl(l_site_terms_date_basis,
4425 l_terms_date_basis);
4426 p_vendor_rec.liab_acc := nvl(l_site_liab_acc, l_liab_acc);
4427 p_vendor_rec.terms_id := nvl(l_site_terms_id, l_terms_id);
4428 p_vendor_rec.payment_priority := l_payment_priority;
4429 p_vendor_rec.prepay_ccid := l_prepay_ccid;
4430 p_vendor_rec.always_take_disc_flag := l_always_take_disc_flag;
4431 p_vendor_rec.pay_date_basis := l_pay_date_basis;
4432 p_vendor_rec.vendor_name := l_vendor_name;
4433 p_vendor_rec.vendor_num := l_vendor_num;
4434 p_vendor_rec.allow_awt_flag := l_site_allow_awt_flag;
4435 p_vendor_rec.party_id := l_party_id;
4436 p_vendor_rec.party_site_id := l_party_site_id;
4437
4438 PrintVendorInfo(p_vendor_rec);
4439
4440 RETURN (TRUE);
4441
4442 END GetVendorInfo;
4443
4444 ------------------------------------------------------------------------
4445 FUNCTION CreatePayee(p_party_id IN ap_suppliers.party_id%TYPE,
4446 p_org_id IN ap_expense_report_headers.org_id%TYPE,
4447 p_reject_code OUT NOCOPY VARCHAR2)
4448 RETURN BOOLEAN IS
4449 ------------------------------------------------------------------------
4450 l_debug_info VARCHAR2(2000);
4451 l_payee_exists VARCHAR2(2);
4452 l_return_status VARCHAR2(1);
4453 l_msg_count NUMBER;
4454 l_msg_data VARCHAR2(32767);
4455 l_External_Payee_Tab IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Tab_Type;
4456 l_Ext_Payee_ID_Tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Tab_Type;
4457 l_Ext_Payee_Create_Tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Create_Tab_Type;
4458
4459
4460 BEGIN
4461
4462 BEGIN
4463 select 'Y'
4464 into l_payee_exists
4465 from IBY_EXTERNAL_PAYEES_ALL
4466 where PAYEE_PARTY_ID = p_party_id
4467 and org_id = p_org_id
4468 and rownum =1;
4469
4470 EXCEPTION
4471 WHEN NO_DATA_FOUND THEN
4472 ------------------------------------------------------------------
4473 l_debug_info := 'Creating a Payee by calling Oracle Payments API';
4474 ------------------------------------------------------------------
4475 IF g_debug_switch = 'Y' THEN
4476 fnd_file.put_line(fnd_file.log, l_debug_info);
4477 END IF;
4478
4479 l_External_Payee_Tab(0).Payee_Party_Id := p_party_id;
4480 l_External_Payee_Tab(0).Payer_Org_Id := p_org_id;
4481 l_External_Payee_Tab(0).Payment_Function := 'PAYABLES_DISB';
4482 l_External_Payee_Tab(0).Exclusive_Pay_Flag := 'N';
4483
4484 IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee (
4485 p_api_version => 1.0,
4486 p_init_msg_list => FND_API.G_TRUE,
4487 p_ext_payee_tab => l_External_Payee_Tab,
4488 x_return_status => l_return_status,
4489 x_msg_count => l_msg_count,
4490 x_msg_data => l_msg_data,
4491 x_ext_payee_id_tab => l_Ext_Payee_ID_Tab,
4492 x_ext_payee_status_tab => l_Ext_Payee_Create_Tab);
4493
4494 IF g_debug_switch = 'Y' THEN
4495 fnd_file.put_line(fnd_file.log, 'l_return_status: ' || l_return_status);
4496
4497 fnd_file.put_line(fnd_file.log, 'Payee_Creation_Status: ' ||
4498 l_Ext_Payee_Create_Tab(0).Payee_Creation_Status);
4499 fnd_file.put_line(fnd_file.log, 'Payee_Creation_Msg: ' ||
4500 l_Ext_Payee_Create_Tab(0).Payee_Creation_Msg);
4501 END IF;
4502
4503 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4504 p_reject_code := substrb(l_Ext_Payee_Create_Tab(0).Payee_Creation_Msg, 1, 30);
4505 RETURN (FALSE);
4506 END IF;
4507 END;
4508
4509 RETURN (TRUE);
4510
4511 END CreatePayee;
4512
4513 END AP_WEB_EXPORT_ER;