[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_EXPORT_ER
Source
1 PACKAGE BODY AP_WEB_EXPORT_ER AS
2 /* $Header: apwexptb.pls 120.43.12010000.12 2008/11/28 10:52:37 stalasil ship $ */
3
4
5 -----------------
6 -- Bug: 6965489
7 -----------------
8 -----------------------------------------------------------------------------------------------
9 PROCEDURE ValidateGLDate(p_source_date IN DATE,
10 p_valid_inv_gl_date IN DATE,
11 p_source_item IN VARCHAR2,
12 p_set_of_books_id IN ap_system_parameters.set_of_books_id%TYPE,
13 p_open_gl_date OUT NOCOPY DATE,
14 p_reject_code OUT NOCOPY ap_expense_report_headers.reject_code%TYPE) IS
15 l_gl_period_status varchar2(2);
16 l_new_gl_date ap_expense_report_headers_all.week_end_date%TYPE;
17 l_debug_info VARCHAR2(2000);
18 BEGIN
19
20 IF (p_source_date IS NULL) THEN
21 RETURN;
22 END IF;
23 ------------------------------------------------------------
24 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;
25 ------------------------------------------------------------
26 IF g_debug_switch = 'Y' THEN
27 fnd_file.put_line(fnd_file.log, l_debug_info);
28 END IF;
29
30 BEGIN
31
32 SELECT closing_status
33 INTO l_gl_period_status
34 FROM gl_period_statuses
35 WHERE application_id=200
36 AND set_of_books_id= p_set_of_books_id
37 AND to_date(p_source_date,'DD-MM-RRRR') BETWEEN start_date AND end_date
38 AND NVL(adjustment_period_flag, 'N') = 'N';
39
40 EXCEPTION
41 WHEN NO_DATA_FOUND THEN
42 l_gl_period_status := NULL;
43 WHEN OTHERS THEN
44 l_gl_period_status := NULL;
45
46 END;
47
48 ----------------------------------
49 l_debug_info := p_source_item || ' GL Period status: ' || l_gl_period_status;
50 ----------------------------------
51 IF g_debug_switch = 'Y' THEN
52 fnd_file.put_line(fnd_file.log, l_debug_info);
53 END IF;
54
55
56 IF l_gl_period_status IS NOT NULL AND (l_gl_period_status = 'O' OR l_gl_period_status = 'F') THEN
57
58 ----------------------------------
59 l_debug_info := p_source_item || ' GL Date is in Valid Period';
60 ----------------------------------
61 IF g_debug_switch = 'Y' THEN
62 fnd_file.put_line(fnd_file.log, l_debug_info);
63 END IF;
64 p_open_gl_date := p_source_date;
65
66 ELSIF l_gl_period_status IS NOT NULL AND l_gl_period_status = 'N' THEN
67
68 ----------------------------------
69 l_debug_info := p_source_item || ' GL Date is in Never Opened Period, Rejecting the expense report';
70 ----------------------------------
71 IF g_debug_switch = 'Y' THEN
72 fnd_file.put_line(fnd_file.log, l_debug_info);
73 END IF;
74
75 p_reject_code := 'GL Date in Closed Period';
76
77 ELSE
78
79 ----------------------------------
80 l_debug_info := p_source_item || ' GL Date is in the closed period or GL period status is null. Getting new GL Date';
81 ----------------------------------
82 IF g_debug_switch = 'Y' THEN
83 fnd_file.put_line(fnd_file.log, l_debug_info);
84 END IF;
85
86 IF p_valid_inv_gl_date IS NOT NULL THEN
87 p_open_gl_date := p_valid_inv_gl_date;
88 ELSE
89 BEGIN
90
91 SELECT min(start_date)
92 INTO l_new_gl_date
93 FROM gl_period_statuses
94 WHERE application_id = 200
95 AND set_of_books_id = p_set_of_books_id
96 AND start_date > to_date(p_source_date,'DD-MM-RRRR')
97 AND (closing_status in ('O', 'F'))
98 AND NVL(adjustment_period_flag, 'N') = 'N';
99
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 l_new_gl_date := NULL;
103 WHEN OTHERS THEN
104 l_new_gl_date := NULL;
105 END;
106
107 IF l_new_gl_date IS NULL THEN
108 p_reject_code := 'No Open Periods';
109 ELSE
110 p_open_gl_date := l_new_gl_date;
111 END IF;
112 END IF;
113
114 END IF;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 p_reject_code := substr(SQLCODE,1,25);
119 IF g_debug_switch = 'Y' THEN
120 fnd_file.put_line(fnd_file.log, SQLERRM);
121 END IF;
122 END ValidateGLDate;
123
124 --------------------------
125 -- Bug: 6356657
126 --------------------------
127 -------------------------------------------------------------------------------------------------
128 PROCEDURE UpdateDistsWithReceiptInfo(p_report_header_id IN NUMBER, p_debug_switch IN VARCHAR2) IS
129 -------------------------------------------------------------------------------------------------
130
131 CURSOR c_report_lines_dists(l_report_header_id IN NUMBER) IS
132 SELECT xl.report_line_id, xl.currency_code,
133 xl.receipt_currency_code,
134 xl.receipt_conversion_rate,
135 xl.receipt_currency_amount,xd.amount ,
136 xd.report_distribution_id
137 FROM ap_expense_report_lines xl,
138 ap_exp_report_dists xd
139 WHERE xd.report_line_id = xl.report_line_id
140 and xl.report_header_id = l_report_header_id
141 and xd.report_header_id = l_report_header_id
142 and xd.receipt_currency_amount is null
143 order by xd.report_line_id, xd.report_distribution_id;
144
145 l_report_line_id NUMBER;
146 l_ln_receipt_curr_amt NUMBER;
147 l_dist_amount NUMBER;
148 l_line_currency_code VARCHAR2(15);
149 l_receipt_currency_code VARCHAR2(15);
150 l_receipt_conversion_rate NUMBER;
151 l_report_distribution_id NUMBER;
152 l_dist_rec_curr_amt NUMBER := 0;
153 l_total_dist_rec_curr_amt NUMBER := 0;
154 l_prev_line_id NUMBER := 0;
155 l_prev_dist_id NUMBER := 0;
156 l_prev_ln_receipt_curr_amt NUMBER := 0;
157 l_debug_info VARCHAR2(2000);
158
159 BEGIN
160 ------------------------------------------------------------
161 l_debug_info := 'Start UpdateDistsWithReceiptInfo';
162 ------------------------------------------------------------
163 OPEN c_report_lines_dists(p_report_header_id);
164 LOOP
165 FETCH c_report_lines_dists INTO l_report_line_id,l_line_currency_code,
166 l_receipt_currency_code, l_receipt_conversion_rate, l_ln_receipt_curr_amt, l_dist_amount,
167 l_report_distribution_id;
168 EXIT WHEN c_report_lines_dists%NOTFOUND;
169
170 BEGIN
171 --------------------------------------------------------------------
172 -- When the line changes update the last distribution of the previous
173 -- line with the reminder.
174 --------------------------------------------------------------------
175 IF (l_prev_line_id <> 0 AND l_prev_line_id <> l_report_line_id) THEN
176 IF ( l_prev_ln_receipt_curr_amt - l_total_dist_rec_curr_amt <> 0 ) THEN
177 l_dist_rec_curr_amt := l_dist_rec_curr_amt + (l_prev_ln_receipt_curr_amt - l_total_dist_rec_curr_amt);
178 update ap_exp_report_dists set
179 receipt_currency_amount = l_dist_rec_curr_amt,
180 receipt_currency_code = l_receipt_currency_code,
181 receipt_conversion_rate = l_receipt_conversion_rate
182 where report_distribution_id = l_prev_dist_id;
183 END IF;
184 l_total_dist_rec_curr_amt := 0;
185 END IF;
186 l_prev_ln_receipt_curr_amt := l_ln_receipt_curr_amt;
187 l_prev_line_id := l_report_line_id;
188 IF l_line_currency_code <> l_receipt_currency_code THEN
189 l_dist_rec_curr_amt := l_dist_amount / l_receipt_conversion_rate;
190
191 ELSE
192 l_dist_rec_curr_amt := l_dist_amount;
193 END IF;
194 l_dist_rec_curr_amt := ap_utilities_pkg.ap_round_currency(l_dist_rec_curr_amt,l_receipt_currency_code);
195 l_total_dist_rec_curr_amt := l_total_dist_rec_curr_amt + l_dist_rec_curr_amt;
196 l_prev_dist_id := l_report_distribution_id;
197
198 update ap_exp_report_dists set
199 receipt_currency_amount = l_dist_rec_curr_amt,
200 receipt_currency_code = l_receipt_currency_code,
201 receipt_conversion_rate = l_receipt_conversion_rate
202 where report_distribution_id = l_report_distribution_id;
203 END;
204
205 END LOOP;
206
207 -------------------------------------------------------------------
208 -- To the last distribution, add the difference amount if any left
209 -------------------------------------------------------------------
210 IF ( l_ln_receipt_curr_amt - l_total_dist_rec_curr_amt <> 0 ) THEN
211 l_dist_rec_curr_amt := l_dist_rec_curr_amt + (l_ln_receipt_curr_amt - l_total_dist_rec_curr_amt);
212 update ap_exp_report_dists set
213 receipt_currency_amount = l_dist_rec_curr_amt,
214 receipt_currency_code = l_receipt_currency_code,
215 receipt_conversion_rate = l_receipt_conversion_rate
216 where report_distribution_id = l_prev_dist_id;
217 END IF;
218
219 close c_report_lines_dists;
220
221 ------------------------------------------------------------
222 l_debug_info := 'End UpdateDistsWithReceiptInfo';
223 ------------------------------------------------------------
224 IF g_debug_switch = 'Y' THEN
225 fnd_file.put_line(fnd_file.log, l_debug_info);
226 END IF;
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 ------------------------------------------------------------
231 l_debug_info := 'Exception in UpdateDistsWithReceiptInfo' || SQLERRM;
232 ------------------------------------------------------------
233 IF g_debug_switch = 'Y' THEN
234 fnd_file.put_line(fnd_file.log, l_debug_info);
235 END IF;
236 END UpdateDistsWithReceiptInfo;
237
238 ------------------------------------------------------------------------
239 PROCEDURE ExportERtoAP(errbuf OUT NOCOPY VARCHAR2,
240 retcode OUT NOCOPY NUMBER,
241 p_batch_name IN VARCHAR2,
242 p_source IN VARCHAR2,
243 p_transfer_flag IN VARCHAR2,
244 p_gl_date IN VARCHAR2,
245 p_group_id IN VARCHAR2,
246 p_commit_cycles IN NUMBER,
247 p_debug_switch IN VARCHAR2,
248 p_org_id IN NUMBER,
249 p_role_name IN VARCHAR2) IS
250 ------------------------------------------------------------------------
251
252 CURSOR c_system_params(l_org_id IN NUMBER) IS
253 SELECT employee_terms_id,
254 base_currency_code,
255 sp.set_of_books_id,
256 fp.non_recoverable_tax_flag,
257 nvl(sp.inv_doc_category_override, 'N'),
258 sp.gl_date_from_receipt_flag,
259 fp.expense_check_address_flag,
260 f.minimum_accountable_unit,
261 f.precision,
262 sp.employee_pay_group_lookup_code,
263 sp.employee_terms_id,
264 sp.apply_advances_default
265 FROM ap_system_parameters_all sp,
266 financials_system_parameters fp,
267 fnd_currencies f
268 WHERE sp.base_currency_code = f.currency_code
269 AND sp.org_id = l_org_id;
270
271 CURSOR c_successful_invoices(l_request_id IN NUMBER) IS
272 SELECT ai.invoice_id, aerh.report_header_id,
273 aerh.advance_invoice_to_apply, aerh.maximum_amount_to_apply
274 FROM ap_expense_report_headers_all aerh, ap_invoices_all ai
275 WHERE ai.APPLICATION_ID = 200
276 AND ai.PRODUCT_TABLE = 'AP_EXPENSE_REPORT_HEADERS_ALL'
277 AND ai.REFERENCE_KEY1 = aerh.report_header_id
278 AND aerh.invoice_num = ai.invoice_num
279 AND aerh.request_id = l_request_id
280 AND aerh.vouchno = 0;
281
282 CURSOR c_rejected_invoices(l_request_id IN NUMBER) IS
283 SELECT to_number(aii.reference_key1) report_header_id,
284 reject_lookup_code,
285 aii.invoice_id
286 FROM ap_interface_rejections air, ap_invoices_interface aii
287 WHERE air.parent_table = 'AP_INVOICES_INTERFACE'
288 AND air.parent_id = aii.invoice_id
289 AND aii.request_id = l_request_id
290 UNION ALL
291 SELECT to_number(aii.reference_key1) report_header_id,
292 reject_lookup_code,
293 aii.invoice_id
294 FROM ap_interface_rejections air,
295 ap_invoices_interface aii,
296 ap_invoice_lines_interface aili
297 WHERE air.parent_table = 'AP_INVOICE_LINES_INTERFACE'
298 AND air.parent_id = aili.invoice_line_id
299 AND aii.invoice_id = aili.invoice_id
300 AND aii.request_id = l_request_id;
301
302 -- Criteria for this cursor is:
303 -- Expense status code should not be 'ERROR' or 'PEND_HOLDS_CLEARANCE' or
304 -- 'HOLD_PENDING_RECEIPTS'
305 -- Vouchno = 0
306 -- XH.hold_lookup_code is null
307 CURSOR c_expenses_to_import(p_source IN VARCHAR2) IS
308 SELECT XH.report_header_id report_header_id,
309 nvl(emps.employee_id, -1) employee_id,
310 emps.employee_num employee_number,
311 XH.week_end_date week_end_date,
312 nvl(XH.invoice_num, '') invoice_num,
313 to_char(ap_utilities_pkg.ap_round_currency(XH.total,
314 XH.default_currency_code)) total,
315 nvl(XH.description, '') description,
316 substrb(rtrim(emps.last_name || ', ' || emps.first_name ||
317 DECODE(people.middle_names, null, '', ' ') ||
318 people.middle_names),
319 1,
320 240) name,
321 nvl(locs.location_code, '') location_code,
322 locs.address_line_1 address_line_1,
323 locs.address_line_2 address_line_2,
324 locs.address_line_3 address_line_3,
325 locs.town_or_city city,
326 decode(locs.STYLE,
327 'CA',
328 '',
329 'CA_GLB',
330 '',
331 nvl(locs.region_2, '')) state,
332 locs.postal_code postal_code,
333 decode(locs.STYLE,
334 'US',
335 '',
336 'US_GLB',
337 '',
338 'IE',
339 '',
340 'IE_GLB',
341 '',
342 'GB',
343 '',
344 'CA',
345 nvl(locs.REGION_1, ''),
346 'JP',
347 nvl(locs.REGION_1, ''),
348 nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
349 locs.REGION_1),
350 '')) province,
351 decode(locs.STYLE,
352 'US',
353 nvl(locs.REGION_1, ''),
354 'US_GLB',
355 nvl(locs.REGION_1, ''),
356 'IE',
357 nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
358 locs.REGION_1),
359 ''),
360 'IE_GLB',
361 nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
362 locs.REGION_1),
363 ''),
364 'GB',
365 nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
366 locs.REGION_1),
367 ''),
368 '') county,
369 locs.country,
370 nvl(V.vendor_id, -1) vendor_id,
371 nvl(XH.vendor_id, -1) header_vendor_id,
372 --nvl(XH.hold_lookup_code, '') hold_lookup_code,
373 --nvl(l1.displayed_field, '') nls_hold_code,
374 --l1.description hold_description,
375 XH.created_by created_by,
376 XH.default_currency_code default_currency_code,
377 nvl(XH.default_exchange_rate_type, '') default_exchange_rate_type,
378 nvl(XH.default_exchange_rate,-1) default_exchange_rate,
379 nvl(to_char(XH.default_exchange_date), '') default_exchange_date,
380 nvl(XH.accts_pay_code_combination_id, -1) accts_pay_ccid,
381 XH.set_of_books_id set_of_books_id,
382 XH.accounting_date accounting_date,
383 nvl(XH.vendor_site_id, -1) header_vendor_site_id,
384 nvl(XH.apply_advances_default, 'N') apply_advances_flag,
385 nvl(XH.advance_invoice_to_apply, -1) advance_invoice_to_apply,
386 to_char(nvl(XH.maximum_amount_to_apply, XH.amt_due_employee)) amount_want_to_apply,
387 XH.expense_check_address_flag home_or_office,
388 nvl(emps.employee_id, -1) current_emp_id,
389 XH.voucher_num voucher_num,
390 '' base_amount,
391 nvl(XH.doc_category_code, '') doc_category_code,
392 nvl(XH.reference_1, '') reference_1,
393 XH.reference_2 reference_2,
394 nvl(to_char(XH.awt_group_id), '') awt_group_id,
395 XH.global_attribute1,
396 XH.global_attribute2,
397 XH.global_attribute3,
398 XH.global_attribute4,
399 XH.global_attribute5,
400 XH.global_attribute6,
401 XH.global_attribute7,
402 XH.global_attribute8,
403 XH.global_attribute9,
404 XH.global_attribute10,
405 XH.global_attribute11,
406 XH.global_attribute12,
407 XH.global_attribute13,
408 XH.global_attribute14,
409 XH.global_attribute15,
410 XH.global_attribute16,
411 XH.global_attribute17,
412 XH.global_attribute18,
413 XH.global_attribute19,
414 XH.global_attribute20,
415 XH.global_attribute_category,
416 nvl(decode(p_transfer_flag, 'Y', XH.attribute1), '') attribute1,
417 nvl(decode(p_transfer_flag, 'Y', XH.attribute2), '') attribute2,
418 nvl(decode(p_transfer_flag, 'Y', XH.attribute3), '') attribute3,
419 nvl(decode(p_transfer_flag, 'Y', XH.attribute4), '') attribute4,
420 nvl(decode(p_transfer_flag, 'Y', XH.attribute5), '') attribute5,
421 nvl(decode(p_transfer_flag, 'Y', XH.attribute6), '') attribute6,
422 nvl(decode(p_transfer_flag, 'Y', XH.attribute7), '') attribute7,
423 nvl(decode(p_transfer_flag, 'Y', XH.attribute8), '') attribute8,
424 nvl(decode(p_transfer_flag, 'Y', XH.attribute9), '') attribute9,
425 nvl(decode(p_transfer_flag, 'Y', XH.attribute10), '') attribute10,
426 nvl(decode(p_transfer_flag, 'Y', XH.attribute11), '') attribute11,
427 nvl(decode(p_transfer_flag, 'Y', XH.attribute12), '') attribute12,
428 nvl(decode(p_transfer_flag, 'Y', XH.attribute13), '') attribute13,
429 nvl(decode(p_transfer_flag, 'Y', XH.attribute14), '') attribute14,
430 nvl(decode(p_transfer_flag, 'Y', XH.attribute15), '') attribute15,
431 nvl(decode(p_transfer_flag, 'Y', XH.attribute_category), '') attribute_category,
432 nvl(XH.payment_currency_code, XH.default_currency_code) payment_currency_code,
433 nvl(XH.payment_cross_rate_type, '') payment_cross_rate_type,
434 nvl(XH.payment_cross_rate_date, XH.week_end_date) payment_cross_rate_date,
435 nvl(XH.payment_cross_rate, 1) payment_cross_rate,
436 nvl(XH.prepay_num, '') prepay_num,
437 nvl(XH.prepay_dist_num, '') prepay_dist_num,
438 nvl(to_char(XH.prepay_gl_date), '') prepay_gl_date,
439 nvl(xh.paid_on_behalf_employee_id, '') paid_on_behalf_employee_id,
440 to_char(nvl(xh.amt_due_employee, to_char(0))) amt_due_employee,
441 to_char(nvl(xh.amt_due_ccard_company, to_char(0))) amt_due_ccard_company,
442 substrb(rtrim(decode(people.per_information18,
443 null,
444 decode(people.per_information19,
445 null,
446 null,
447 people.per_information19),
448 people.per_information18 || ', ' ||
449 people.per_information19)),
450 1,
451 240) per_information18_19,
452 people.per_information_category per_information_category,
453 XH.source source,
454 p_group_id group_id,
455 locs.style style,
456 XH.org_id org_id,
457 '' invoice_id,
458 '' invoice_type_lookup_code,
459 '' gl_date,
460 '' alternate_name,
461 '' amount_app_to_discount,
462 V.payment_method_lookup_code
463 FROM ap_expense_report_headers XH,
464 hr_locations locs,
465 per_all_people_f people,
466 (SELECT
467 h.employee_id,
468 h.full_name,
469 h.employee_num,
470 h.organization_id,
471 h.last_name,
472 h.first_name,
473 h.business_group_id,
474 h.location_id
475 FROM per_employees_x h
476 WHERE AP_WEB_DB_HR_INT_PKG.isPersonCwk(h.employee_id)='N'
477 UNION ALL
478 SELECT
479 h.person_id employee_id,
480 h.full_name,
481 h.npw_number employee_num,
482 h.organization_id,
483 h.last_name,
484 h.first_name,
485 h.business_group_id,
486 h.location_id
487 FROM PER_CONT_WORKERS_CURRENT_X h) emps,
488 ap_suppliers V
489 --ap_lookup_codes l1
490 WHERE vouchno = 0
491 AND XH.employee_id = V.employee_id(+)
492 AND XH.employee_id = emps.employee_id(+)
493 AND (trunc(sysdate) between people.effective_start_date(+) AND
494 people.effective_end_date(+))
495 AND ((emps.business_group_id IS NULL) OR
496 (emps.business_group_id in
497 (SELECT nvl(FSP.business_group_id, 0)
498 FROM financials_system_parameters FSP)))
499 AND emps.employee_id = people.person_id(+)
500 AND emps.location_id = locs.location_id(+)
501 AND decode(XH.source,
502 'CREDIT CARD',
503 'SelfService',
504 'Both Pay',
505 'SelfService',
506 XH.source) = p_source
507 AND NVL(XH.expense_status_code, 'NO ERROR') not IN
508 ('ERROR', 'PEND_HOLDS_CLEARANCE', 'HOLD_PENDING_RECEIPTS')
509 AND XH.hold_lookup_code is null
510 --AND l1.lookup_type(+) = 'HOLD CODE'
511 --AND l1.lookup_code(+) = XH.hold_lookup_code
512 AND ((XH.org_id IS NOT NULL AND
513 p_org_id IS NOT NULL AND
514 XH.org_id = p_org_id)
515 OR (p_org_id IS NULL AND
516 XH.org_id IS NOT NULL AND
517 (mo_global.check_access(XH.org_id)= 'Y'))
518 OR (p_org_id is NOT NULL AND XH.org_id IS NULL)
519 OR (p_org_id is NULL AND XH.org_id IS NULL))
520 AND EXISTS
521 (SELECT 'Y'
522 FROM AP_EXPENSE_REPORT_LINES XL
523 WHERE XH.REPORT_HEADER_ID = XL.REPORT_HEADER_ID)
524 ORDER BY UPPER(emps.last_name) desc,
525 UPPER(emps.first_name) desc,
526 UPPER(people.middle_names) desc,
527 total,
528 week_end_date desc;
529 --FOR UPDATE OF XH.report_header_id NOWAIT;
530
531 l_batch_control_flag VARCHAR2(10);
532 l_batch_id NUMBER;
533 l_batch_name VARCHAR2(50);
534 l_dummy VARCHAR2(50);
535 l_debug_info VARCHAR2(2000);
536 batch_failure EXCEPTION;
537 validation_failed EXCEPTION;
538 l_employee_terms_id ap_system_parameters.employee_terms_id%TYPE;
539 l_base_currency ap_system_parameters.base_currency_code%TYPE;
540 l_set_of_books_id ap_system_parameters.set_of_books_id%TYPE;
541 l_enable_recoverable_flag financials_system_parameters.non_recoverable_tax_flag%TYPE;
542 l_doc_category_override ap_system_parameters.inv_doc_category_override%TYPE;
543 l_gl_date_flag ap_system_parameters.gl_date_from_receipt_flag%TYPE;
544 l_address_flag financials_system_parameters.expense_check_address_flag%TYPE;
545 l_min_accountable_unit NUMBER;
546 l_precision NUMBER;
547 vendor_valid_flag VARCHAR2(2);
548 vendor_site_valid_flag VARCHAR2(2);
549 is_person_cwk VARCHAR2(2);
550 l_payment_due_from VARCHAR2(15);
551 l_invoice_rec InvoiceInfoRecType;
552 l_invoice_lines_rec_tab InvoiceLinesRecTabType;
553 l_request_id NUMBER;
554 l_reject_code ap_expense_report_headers.reject_code%TYPE;
555 l_total ap_expense_report_headers.total%TYPE;
556 l_invoices_fetched NUMBER := 0;
557 l_invoices_created NUMBER := 0;
558 l_cc_invoices_fetched NUMBER := 0;
559 l_cc_invoices_created NUMBER := 0;
560 l_total_invoice_amount NUMBER;
561 l_print_batch VARCHAR2(5);
562 l_batch_error_flag VARCHAR2(5);
563 l_calling_sequence VARCHAR2(30) := 'Expense Report Export';
564 l_failed_open_interface NUMBER := 0;
565 l_last_updated_by NUMBER;
566 l_description VARCHAR2(300);
567 l_seq_profile VARCHAR2(2);
568 l_rows_to_import NUMBER := 0;
569 l_expenses_fetched NUMBER := 0;
570 l_org_id NUMBER;
571 l_emp_pg_lookup_code ap_system_parameters.employee_pay_group_lookup_code%TYPE;
572 l_emp_terms_id ap_system_parameters.employee_terms_id%TYPE;
573 l_sys_apply_advances_flag ap_system_parameters.apply_advances_default%TYPE;
574
575 TYPE ReportHeaderIdType IS TABLE OF ap_expense_report_headers.report_header_id%TYPE
576 INDEX BY BINARY_INTEGER;
577 TYPE RejectCodeType IS TABLE OF ap_interface_rejections.reject_lookup_code%TYPE
578 INDEX BY BINARY_INTEGER;
579 TYPE InvoiceIdType IS TABLE OF ap_invoices_interface.invoice_id%TYPE
580 INDEX BY BINARY_INTEGER;
581 TYPE AdvAppliedType IS TABLE OF ap_expense_report_headers.maximum_amount_to_apply%TYPE
582 INDEX BY BINARY_INTEGER;
583
584 l_report_header_id_list ReportHeaderIdType;
585 l_reject_code_list RejectCodeType;
586 l_invoice_id_list InvoiceIdType;
587 l_vendor_rec VendorInfoRecType;
588 l_oie_applied_prepay_list InvoiceIdType;
589 l_oie_applied_amt_list AdvAppliedType;
590
591 l_expense_status_code ap_expense_report_headers_all.expense_status_code%TYPE;
592 l_actual_adv_applied NUMBER;
593 x_return_status VARCHAR2(4000);
594 x_msg_count NUMBER;
595 x_msg_data VARCHAR2(4000);
596 l_rejection_list AP_IMPORT_INVOICES_PKG.rejection_tab_type;
597 l_inv_total_amount NUMBER;
598 l_payment_due_frm VARCHAR2(15);
599 l_is_contigent_worker VARCHAR2(2);
600 l_is_active_employee VARCHAR2(2);
601 l_trx_attributes iby_disbursement_comp_pub.Trxn_Attributes_Rec_Type;
602 l_result_pmt_attributes iby_disbursement_comp_pub.Default_Pmt_Attrs_Rec_Type;
603 l_return_status varchar2(30);
604 l_msg_count number;
605 l_msg_data varchar2(2000);
606 l_le_id number;
607 l_available_prepays NUMBER;
608 l_gl_period_status varchar2(2);
609 l_period_year varchar2(5);
610 l_new_gl_date ap_expense_report_headers_all.week_end_date%TYPE;
611
612
613 BEGIN
614
615 g_debug_switch := p_debug_switch;
616 g_last_updated_by := to_number(FND_GLOBAL.USER_ID);
617 g_last_update_login := to_number(FND_GLOBAL.LOGIN_ID);
618
619
620
621 IF g_debug_switch = 'Y' THEN
622 fnd_file.put_line(fnd_file.log, 'p_batch_name = ' || p_batch_name);
623 END IF;
624
625 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
626
627 ------------------------------------------------------------
628 l_debug_info := 'Get Batch Name if not unique';
629 ------------------------------------------------------------
630 IF g_debug_switch = 'Y' THEN
631 fnd_file.put_line(fnd_file.log, l_debug_info);
632 END IF;
633
634 FND_PROFILE.GET('AP_USE_INV_BATCH_CONTROLS', l_batch_control_flag);
635
636 l_batch_name := p_batch_name;
637 IF l_batch_name = 'N/A' THEN
638 l_batch_name := null;
639 END IF;
640
641 l_dummy := 'Batch name is not unique';
642
643 IF (l_batch_control_flag = 'Y' and l_batch_name is not null) THEN
644
645 BEGIN
646 SELECT 'Batch name is not unique'
647 INTO l_dummy
648 FROM ap_batches
649 WHERE batch_name = l_batch_name;
650 EXCEPTION
651 WHEN NO_DATA_FOUND THEN
652 l_dummy := 'Unique_Batch';
653 WHEN OTHERS THEN
654 l_dummy := 'Batch name is not unique';
655 END;
656
657 IF l_dummy <> 'Unique_Batch' THEN
658
659 SELECT AP_BATCHES_S2.nextval INTO l_batch_id FROM dual;
660
661 l_batch_name := l_batch_name || to_char(l_batch_id);
662
663 END IF;
664 END IF;
665
666 ------------------------------------------------------------
667 l_debug_info := 'Batch Name = ' || l_batch_name;
668 ------------------------------------------------------------
669 IF g_debug_switch = 'Y' THEN
670 fnd_file.put_line(fnd_file.log, l_debug_info);
671 END IF;
672
673 ------------------------------------------------------------
674 l_debug_info := 'Begin Receipts Management - Holds';
675 ------------------------------------------------------------
676 fnd_file.put_line(fnd_file.log, l_debug_info);
677
678 AP_WEB_HOLDS_WF.ExpenseHolds;
679
680 ------------------------------------------------------------
681 l_debug_info := 'End Receipts Management - Holds';
682 ------------------------------------------------------------
683 IF g_debug_switch = 'Y' THEN
684 fnd_file.put_line(fnd_file.log, l_debug_info);
685 END IF;
686
687 ------------------------------------------------------------
688 l_debug_info := 'Begin Processing Individual expense reports';
689 ------------------------------------------------------------
690 fnd_file.put_line(fnd_file.log, l_debug_info);
691
692 OPEN c_expenses_to_import(p_source);
693
694 LOOP
695
696 ------------------------------------------------------------
697 l_debug_info := 'Fetching expense report...';
698 ------------------------------------------------------------
699 fnd_file.put_line(fnd_file.log, l_debug_info);
700
701 FETCH c_expenses_to_import
702 INTO l_invoice_rec;
703
704 EXIT WHEN c_expenses_to_import%NOTFOUND;
705
706 BEGIN
707
708
709 ------------------------------------------------------------
710 l_debug_info := 'Updating the Dists with Receipt Info...';
711 ------------------------------------------------------------
712 UpdateDistsWithReceiptInfo(l_invoice_rec.report_header_id, g_debug_switch);
713
714
715 l_expenses_fetched := l_expenses_fetched + 1;
716 l_reject_code := NULL;
717
718 fnd_file.put_line(fnd_file.log,
719 'Expense Report Number : **'||l_invoice_rec.invoice_num||'**');
720
721 IF l_invoice_rec.org_id <> NVL(l_org_id, -3115) THEN
722
723 l_org_id := nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL));
724
725 ------------------------------------------------------------
726 l_debug_info := 'Get the system parameters';
727 ------------------------------------------------------------
728 IF g_debug_switch = 'Y' THEN
729 fnd_file.put_line(fnd_file.log, l_debug_info);
730 END IF;
731
732 OPEN c_system_params(l_org_id);
733 FETCH c_system_params
734 INTO l_employee_terms_id,
735 l_base_currency,
736 l_set_of_books_id,
737 l_enable_recoverable_flag,
738 l_doc_category_override,
739 l_gl_date_flag,
740 l_address_flag,
741 l_min_accountable_unit,
742 l_precision,
743 l_emp_pg_lookup_code,
744 l_emp_terms_id,
745 l_sys_apply_advances_flag;
746 CLOSE c_system_params;
747 END IF;
748
749
750 IF l_employee_terms_id < 0 THEN
751 ---------------------------------------------------------------------------
752 l_debug_info := 'employee terms id is < 0 in system parameters. Aborting.';
753 ---------------------------------------------------------------------------
754 fnd_file.put_line(fnd_file.log, l_debug_info);
755
756 raise batch_failure;
757 END IF;
758
759 IF l_invoice_rec.header_vendor_id <> -1 THEN
760 ----------------------------------
761 l_debug_info := 'Validate Vendor';
762 ----------------------------------
763 IF g_debug_switch = 'Y' THEN
764 fnd_file.put_line(fnd_file.log, l_debug_info);
765 END IF;
766
767 vendor_valid_flag := AP_WEB_DB_PO_INT_PKG.IsVendorValid(l_invoice_rec.header_vendor_id);
768
769 IF vendor_valid_flag = 'N' THEN
770 ---------------------------------------------------------------
771 l_debug_info := 'Inactive vendor *' ||
772 to_char(l_invoice_rec.header_vendor_id) || '*';
773 ---------------------------------------------------------------
774 fnd_file.put_line(fnd_file.log, l_debug_info);
775
776 l_reject_code := 'Inactive vendor';
777 raise validation_failed;
778 END IF;
779
780 END IF;
781
782 IF l_invoice_rec.source not IN ('XpenseXpress', 'SelfService',
783 'Oracle Project Accounting') then
784 l_invoice_rec.vendor_id := l_invoice_rec.header_vendor_id;
785 END IF;
786
787 is_person_cwk := AP_WEB_DB_HR_INT_PKG.IsPersonCwk(l_invoice_rec.employee_id);
788
789 IF is_person_cwk = 'Y' THEN
790 -------------------------------------------------------------------
791 l_debug_info := 'Validate Site if employee is a contingent worker';
792 -------------------------------------------------------------------
793 IF g_debug_switch = 'Y' THEN
794 fnd_file.put_line(fnd_file.log, l_debug_info);
795 END IF;
796
797 IF l_invoice_rec.header_vendor_site_id is NULL THEN
798 -------------------------------------
799 l_debug_info := 'NULL vendor site *';
800 -------------------------------------
801 fnd_file.put_line(fnd_file.log, l_debug_info);
802
803 l_reject_code := 'Inactive site';
804 raise validation_failed;
805 END IF;
806
807 l_invoice_rec.vendor_id := l_invoice_rec.header_vendor_id;
808
809 vendor_site_valid_flag := AP_WEB_DB_PO_INT_PKG.IsVendorSiteValid(
810 l_invoice_rec.header_vendor_site_id);
811
812 IF vendor_site_valid_flag = 'N' THEN
813 --------------------------------------------------------------------
814 l_debug_info := 'Inactive site *' ||
815 to_char(l_invoice_rec.header_vendor_site_id) || '*';
816 --------------------------------------------------------------------
817 fnd_file.put_line(fnd_file.log, l_debug_info);
818
819 l_reject_code := 'Inactive site';
820 raise validation_failed;
821 END IF;
822
823 --------------------------------------------------------------------
824 l_debug_info := 'Set the description field for a contingent worker';
825 --------------------------------------------------------------------
826 IF g_debug_switch = 'Y' THEN
827 fnd_file.put_line(fnd_file.log, l_debug_info);
828 END IF;
829
830 FND_MESSAGE.SET_NAME('SQLAP', 'OIE_REMITTANCE_DESC');
831 FND_MESSAGE.Set_Token('PAID_ON_BEHALF_OF', l_invoice_rec.name);
832 FND_MESSAGE.Set_Token('INVOICE_DESCRIPTION',
833 l_invoice_rec.description);
834 l_description := FND_MESSAGE.GET;
835
836 l_invoice_rec.description := substrb(l_description, 1, 240);
837
838 END IF;
839
840 IF l_invoice_rec.source = 'Oracle Project Accounting' then
841
842 ---------------------------------------------------
843 l_debug_info := 'Validation for Project Expenses ';
844 ---------------------------------------------------
845 IF g_debug_switch = 'Y' THEN
846 fnd_file.put_line(fnd_file.log, l_debug_info);
847 END IF;
848
849 IF l_invoice_rec.employee_id = -1 THEN
850 l_reject_code := 'Invalid employee';
851 raise validation_failed;
852 END IF;
853
854 IF l_invoice_rec.set_of_books_id <> l_set_of_books_id THEN
855 --------------------------------------------------------------
856 l_debug_info := 'Invalid SOB *' ||
857 to_char(l_invoice_rec.set_of_books_id) || '*';
858 --------------------------------------------------------------
859 fnd_file.put_line(fnd_file.log, l_debug_info);
860
861 l_reject_code := 'Invalid set of books';
862 raise validation_failed;
863 END IF;
864
865 END IF;
866
867 -------------------------------------------------
868 l_debug_info := 'Validate/Set the doc sequence';
869 -------------------------------------------------
870 IF g_debug_switch = 'Y' THEN
871 fnd_file.put_line(fnd_file.log, l_debug_info);
872 END IF;
873
874 FND_PROFILE.GET('UNIQUE:SEQ_NUMBERS', l_seq_profile);
875 -------------------------------------------------
876 l_debug_info := 'Sequence profile is: '||l_seq_profile;
877 -------------------------------------------------
878
879 IF l_seq_profile IN ('A', 'P') THEN
880 IF l_invoice_rec.doc_category_code IS NULL THEN
881 IF l_invoice_rec.source IN ('XpenseXpress', 'SelfService',
882 'Oracle Project Accounting') THEN
883 l_invoice_rec.doc_category_code := 'EXP REP INV';
884 ELSIF l_invoice_rec.source IN ('Both Pay', 'CREDIT CARD') THEN
885 l_invoice_rec.doc_category_code := 'PAY REQ INV';
886 ELSE
887 IF l_invoice_rec.total < 0 THEN
888 l_invoice_rec.doc_category_code := 'CRM INV';
889 ELSE
890 l_invoice_rec.doc_category_code := 'STD INV';
891 END IF;
892 END IF;
893 ELSE
894 IF l_doc_category_override <> 'Y' THEN
895 l_reject_code := 'No Doc Category Override';
896 raise validation_failed;
897 ELSE
898 IF l_invoice_rec.source IN ('XpenseXpress', 'SelfService',
899 'Oracle Project Accounting') THEN
900 IF l_invoice_rec.doc_category_code <> 'STD INV' THEN
901 l_reject_code := 'Invalid Doc Category';
902 raise validation_failed;
903 END IF;
904 ELSE
905 IF l_invoice_rec.doc_category_code IN
906 ('EXP REP INV', 'INT INV', 'MIX INV', 'DBM INV',
907 'REC INV', 'PREPAY INV', 'CHECK PAY', 'CLEAR PAY',
908 'EFT PAY', 'WIRE PAY', 'FUTURE PAY', 'MAN FUTURE PAY') THEN
909 l_reject_code := 'Invalid Doc Category';
910 raise validation_failed;
911 ELSE
912 IF (l_invoice_rec.doc_category_code = 'STD INV' AND
913 l_invoice_rec.total < 0) THEN
914 l_reject_code := 'Invalid Doc Category';
915 raise validation_failed;
916 ELSIF (l_invoice_rec.doc_category_code = 'CRM INV' AND
917 l_invoice_rec.total > 0) THEN
918 l_reject_code := 'Invalid Doc Category';
919 raise validation_failed;
920 ELSE
921 BEGIN
922 SELECT 'user defined document category exists'
923 INTO l_dummy
924 FROM fnd_doc_sequence_categories
925 WHERE code = l_invoice_rec.doc_category_code
926 AND (table_name = 'AP_INVOICES' OR
927 table_name = 'AP_INVOICES_ALL');
928 EXCEPTION
929 WHEN OTHERS THEN
930 l_reject_code := 'Invalid Doc Category';
931 raise validation_failed;
932 END;
933 END IF;
934 END IF;
935 END IF;
936 END IF;
937 END IF;
938 END IF;
939 ----------------------------------
940 l_debug_info := 'Set the GL Date';
941 ----------------------------------
942 IF g_debug_switch = 'Y' THEN
943 fnd_file.put_line(fnd_file.log, l_debug_info);
944 END IF;
945
946 IF l_gl_date_flag IN ('I', 'N') THEN
947 l_invoice_rec.gl_date := l_invoice_rec.week_end_date;
948 ELSE
949 l_invoice_rec.gl_date := sysdate;
950 END IF;
951
952 IF l_invoice_rec.source NOT IN
953 ('XpenseXpress', 'SelfService', 'Both Pay', 'CREDIT CARD') THEN
954 IF l_invoice_rec.accounting_date IS NOT NULL THEN
955 l_invoice_rec.gl_date := l_invoice_rec.accounting_date;
956 END IF;
957 ELSE
958 IF p_gl_date IS NOT NULL THEN
959 l_invoice_rec.gl_date := fnd_date.canonical_to_date(p_gl_date);
960 END IF;
961 END IF;
962
963 ----------------------------------
964 l_debug_info := 'Checking GL Period status for the set Invoice GL Date: ' || l_invoice_rec.gl_date;
965 ----------------------------------
966 IF g_debug_switch = 'Y' THEN
967 fnd_file.put_line(fnd_file.log, l_debug_info);
968 END IF;
969
970 ValidateGLDate(l_invoice_rec.gl_date,
971 null,
972 'Invoice',
973 l_invoice_rec.set_of_books_id,
974 l_new_gl_date,
975 l_reject_code);
976 IF (l_reject_code IS NOT NULL) THEN
977 raise validation_failed;
978 ELSE
979 l_invoice_rec.gl_date := l_new_gl_date;
980 END IF;
981
982 -------------------------------------------------------------------
983 l_debug_info := 'Final Invoice GL Date: ' || l_invoice_rec.gl_date;
984 -------------------------------------------------------------------
985 IF g_debug_switch = 'Y' THEN
986 fnd_file.put_line(fnd_file.log, l_debug_info);
987 END IF;
988
989 IF (l_invoice_rec.source = 'XpenseXpress') THEN
990 ----------------------------------
991 l_debug_info := 'Checking GL Period status for the set Prepayment GL Date: ' || l_invoice_rec.prepay_gl_date;
992 ----------------------------------
993 IF g_debug_switch = 'Y' THEN
994 fnd_file.put_line(fnd_file.log, l_debug_info);
995 END IF;
996
997 ValidateGLDate(l_invoice_rec.prepay_gl_date,
998 l_invoice_rec.gl_date,
999 'Prepayment',
1000 l_invoice_rec.set_of_books_id,
1001 l_new_gl_date,
1002 l_reject_code);
1003 IF (l_reject_code IS NOT NULL) THEN
1004 raise validation_failed;
1005 ELSE
1006 l_invoice_rec.prepay_gl_date := l_new_gl_date;
1007 END IF;
1008
1009 -----------------------------------------------------------------------------
1010 l_debug_info := 'Final Prepayment GL Date: ' || l_invoice_rec.prepay_gl_date;
1011 -----------------------------------------------------------------------------
1012 IF g_debug_switch = 'Y' THEN
1013 fnd_file.put_line(fnd_file.log, l_debug_info);
1014 END IF;
1015 ELSE -- Bug#7278445 - Prepayment GL Date defaulting is done by AP
1016 -----------------------------------------------------------------------------
1017 l_debug_info := 'As source is not XpenseXpress Resetting Prepay GL date to Null since Prepay GL date defaulting is done by Payables';
1018 -----------------------------------------------------------------------------
1019 IF g_debug_switch = 'Y' THEN
1020 fnd_file.put_line(fnd_file.log, l_debug_info);
1021 END IF;
1022 l_invoice_rec.prepay_gl_date := NULL;
1023 END IF;
1024
1025 ----------------------------------------
1026 l_debug_info := 'Set the Exchange Rate';
1027 ----------------------------------------
1028 IF g_debug_switch = 'Y' THEN
1029 fnd_file.put_line(fnd_file.log, l_debug_info);
1030 END IF;
1031
1032 IF l_base_currency = l_invoice_rec.default_currency_code THEN
1033 l_invoice_rec.default_exchange_rate := -1;
1034 ELSIF
1035 gl_currency_api.is_fixed_rate(l_invoice_rec.default_currency_code,
1036 l_base_currency,
1037 nvl(l_invoice_rec.accounting_date, sysdate))
1038 = 'Y' THEN
1039 l_invoice_rec.default_exchange_rate := gl_currency_api.get_rate(
1040 l_invoice_rec.default_currency_code,
1041 l_base_currency,
1042 nvl(l_invoice_rec.accounting_date,
1043 sysdate));
1044 END IF;
1045
1046 ----------------------------------------
1047 l_debug_info := 'Set the Base Amount';
1048 ----------------------------------------
1049 IF g_debug_switch = 'Y' THEN
1050 fnd_file.put_line(fnd_file.log, l_debug_info);
1051 END IF;
1052
1053 IF l_base_currency = l_invoice_rec.default_currency_code THEN
1054 l_invoice_rec.base_amount := '';
1055 ELSE
1056 IF l_min_accountable_unit IS NULL THEN
1057 l_invoice_rec.base_amount := ROUND(l_invoice_rec.total *
1058 l_invoice_rec.default_exchange_rate,
1059 l_precision);
1060 ELSE
1061 l_invoice_rec.base_amount := ROUND(l_invoice_rec.total *
1062 l_invoice_rec.default_exchange_rate
1063 /l_min_accountable_unit) *
1064 l_min_accountable_unit ;
1065 END IF;
1066 END IF;
1067
1068
1069 ----------------------------------------
1070 l_debug_info := 'Set the Address flag';
1071 ----------------------------------------
1072 IF g_debug_switch = 'Y' THEN
1073 fnd_file.put_line(fnd_file.log, l_debug_info);
1074 END IF;
1075
1076 --Bug#7207375 - Allow payment of Expense Report to Temporary Address
1077 l_invoice_rec.home_or_office := nvl(l_invoice_rec.home_or_office,l_address_flag);
1078
1079 IF l_invoice_rec.home_or_office IS NOT NULL THEN
1080 IF l_invoice_rec.home_or_office in ('HOME', 'H') THEN
1081 l_invoice_rec.home_or_office := 'H';
1082 ELSIF l_invoice_rec.home_or_office in ('OFFICE', 'O') THEN
1083 l_invoice_rec.home_or_office := 'O';
1084 ELSIF l_invoice_rec.home_or_office in ('PROVISIONAL', 'P') THEN
1085 l_invoice_rec.home_or_office := 'P';
1086 END IF;
1087 END IF;
1088
1089 ---------------------------------------
1090 l_debug_info := 'Set the Invoice Type';
1091 ---------------------------------------
1092 IF g_debug_switch = 'Y' THEN
1093 fnd_file.put_line(fnd_file.log, l_debug_info);
1094 END IF;
1095
1096 IF l_invoice_rec.source not IN
1097 ('XpenseXpress', 'SelfService', 'Both Pay', 'CREDIT CARD',
1098 'Oracle Project Accounting') THEN
1099 IF l_invoice_rec.total < 0 THEN
1100 l_invoice_rec.invoice_type_lookup_code := 'CREDIT';
1101 ELSE
1102 l_invoice_rec.invoice_type_lookup_code := 'STANDARD';
1103 END IF;
1104
1105 ELSIF l_invoice_rec.source IN
1106 ('Both Pay', 'CREDIT CARD') THEN
1107 l_invoice_rec.invoice_type_lookup_code := 'PAYMENT REQUEST';
1108
1109 ELSE
1110 l_invoice_rec.invoice_type_lookup_code := 'EXPENSE REPORT';
1111 END IF;
1112
1113 IF l_invoice_rec.per_information_category = 'JP' THEN
1114 l_invoice_rec.name := l_invoice_rec.per_information18_19;
1115 l_invoice_rec.alternate_name := l_invoice_rec.name;
1116 END IF;
1117
1118 ---------------------------------------------------------------------
1119 l_debug_info := 'Call procedure to query and validate expense lines';
1120 ---------------------------------------------------------------------
1121 IF g_debug_switch = 'Y' THEN
1122 fnd_file.put_line(fnd_file.log, l_debug_info);
1123 END IF;
1124
1125 SELECT AP_INVOICES_INTERFACE_S.nextval
1126 INTO l_invoice_rec.invoice_id
1127 FROM DUAL;
1128
1129
1130 IF l_reject_code is NOT NULL THEN
1131 raise validation_failed;
1132 END IF;
1133
1134 ----------------------------------------------------------------
1135 l_debug_info := 'Get/validate the vendor_id and vendor_site_id';
1136 ----------------------------------------------------------------
1137 IF g_debug_switch = 'Y' THEN
1138 fnd_file.put_line(fnd_file.log, l_debug_info);
1139 END IF;
1140
1141 l_vendor_rec.vendor_id := l_invoice_rec.vendor_id;
1142 l_vendor_rec.vendor_site_id := l_invoice_rec.header_vendor_site_id;
1143 l_vendor_rec.home_or_office := l_invoice_rec.home_or_office;
1144 l_vendor_rec.employee_id := l_invoice_rec.employee_id;
1145 l_vendor_rec.vendor_name := l_invoice_rec.name;
1146 l_vendor_rec.org_id := l_invoice_rec.org_id;
1147 -- bug 5350423 - supplier creation should not pass address info
1148 --l_vendor_rec.address_line_1 := l_invoice_rec.address_line_1;
1149 --l_vendor_rec.address_line_2 := l_invoice_rec.address_line_2;
1150 --l_vendor_rec.address_line_3 := l_invoice_rec.address_line_3;
1151 --l_vendor_rec.city := l_invoice_rec.city;
1152 --l_vendor_rec.state := l_invoice_rec.state;
1153 --l_vendor_rec.postal_code := l_invoice_rec.postal_code;
1154 --l_vendor_rec.province := l_invoice_rec.province;
1155 --l_vendor_rec.county := l_invoice_rec.county;
1156 --l_vendor_rec.country := l_invoice_rec.country;
1157 --l_vendor_rec.style := l_invoice_rec.style;
1158 --Bug 5890829 set the pay group
1159 l_vendor_rec.pay_group := l_emp_pg_lookup_code;
1160 l_vendor_rec.terms_date_basis := null;
1161 l_vendor_rec.liab_acc := null;
1162 --Bug 5890829 set the payment terms
1163 l_vendor_rec.terms_id := l_emp_terms_id;
1164 l_vendor_rec.payment_priority := null;
1165 l_vendor_rec.prepay_ccid := null;
1166 l_vendor_rec.always_take_disc_flag := null;
1167 l_vendor_rec.pay_date_basis := null;
1168 l_vendor_rec.vendor_num := null;
1169 l_vendor_rec.allow_awt_flag := null;
1170 l_vendor_rec.party_id := null;
1171
1172
1173 ----------------------------------
1174 l_debug_info := 'Get Vendor Info';
1175 ----------------------------------
1176 IF g_debug_switch = 'Y' THEN
1177 fnd_file.put_line(fnd_file.log, l_debug_info);
1178 END IF;
1179
1180 IF NOT GetVendorInfo(l_vendor_rec,
1181 l_reject_code) THEN
1182
1183 raise validation_failed;
1184 END IF;
1185
1186 IF l_reject_code is NOT NULL THEN
1187 raise validation_failed;
1188 ELSE
1189 l_invoice_rec.vendor_id := l_vendor_rec.vendor_id;
1190 l_invoice_rec.header_vendor_site_id := l_vendor_rec.vendor_site_id;
1191 END IF;
1192
1193 -------------------------------------------
1194 l_debug_info := 'Get the Payment Scenario';
1195 -------------------------------------------
1196 IF g_debug_switch = 'Y' THEN
1197 fnd_file.put_line(fnd_file.log, l_debug_info);
1198 END IF;
1199
1200 l_payment_due_from := '';
1201
1202 IF (NOT
1203 AP_WEB_DB_EXPRPT_PKG.getPaymentDueFromReport(l_invoice_rec.report_header_id,
1204 l_payment_due_from)) THEN
1205 NULL;
1206 END IF;
1207
1208 IF (l_payment_due_from = 'COMPANY') OR
1209 (l_payment_due_from = 'BOTH' AND
1210 l_invoice_rec.source = 'SelfService') THEN
1211
1212 -----------------------------------------------------------------
1213 l_debug_info := 'Calling Reversal Logic for payment scenario ' ||
1214 l_payment_due_from;
1215 -----------------------------------------------------------------
1216 IF g_debug_switch = 'Y' THEN
1217 fnd_file.put_line(fnd_file.log, l_debug_info);
1218 END IF;
1219
1220 l_total := AP_CREDIT_CARD_INVOICE_PKG.createCreditCardReversals(l_invoice_rec.invoice_id,
1221 l_invoice_rec.report_header_id,
1222 l_invoice_rec.gl_date,
1223 l_invoice_rec.total);
1224
1225 l_invoice_rec.total := l_total;
1226 END IF;
1227
1228
1229 IF l_invoice_rec.source in ('CREDIT CARD','Both Pay') THEN
1230
1231 ---------------------------------------------------------
1232 l_debug_info := 'Create the Payee if one does not exist';
1233 ---------------------------------------------------------
1234 IF g_debug_switch = 'Y' THEN
1235 fnd_file.put_line(fnd_file.log, l_debug_info);
1236 END IF;
1237
1238 IF NOT CreatePayee(l_vendor_rec.party_id,
1239 l_vendor_rec.org_id,
1240 l_reject_code) THEN
1241 raise validation_failed;
1242 END IF;
1243
1244 IF l_reject_code is NOT NULL THEN
1245 raise validation_failed;
1246 END IF;
1247 -- Vendor should not be passed. Payment will be made to the Payee
1248 -- bug 6730812 : comment as we need to pass the vendor id and
1249 -- vendor site id. for complete fix we need AP patch 6711062 too.
1250 --l_vendor_rec.vendor_id := null;
1251 --l_vendor_rec.vendor_site_id := null;
1252 END IF;
1253
1254
1255 ----------------------------------------------------------
1256 l_debug_info := 'Check if the employee is contingent worker';
1257 ----------------------------------------------------------
1258 IF g_debug_switch = 'Y' THEN
1259 fnd_file.put_line(fnd_file.log, l_debug_info);
1260 END IF;
1261
1262 l_is_contigent_worker := AP_WEB_DB_HR_INT_PKG.IsPersonCwk(l_invoice_rec.employee_id);
1263
1264 IF l_is_contigent_worker = 'N' THEN
1265
1266 ----------------------------------------------------------
1267 l_debug_info := 'Employee is not contingent worker, checking if it is active or not';
1268 ----------------------------------------------------------
1269
1270 IF g_debug_switch = 'Y' THEN
1271 fnd_file.put_line(fnd_file.log, l_debug_info);
1272 END IF;
1273
1274 l_is_active_employee := 'N';
1275
1276 BEGIN
1277
1278 SELECT 'Y'
1279 INTO l_is_active_employee
1280 FROM per_periods_of_service_v
1281 WHERE person_id = l_invoice_rec.employee_id
1282 AND trunc(sysdate) <= trunc(nvl(final_process_date, sysdate))
1283 AND ROWNUM=1
1284 ORDER BY LAST_UPDATE_DATE DESC;
1285
1286 EXCEPTION
1287 WHEN NO_DATA_FOUND THEN
1288 l_is_active_employee := 'N';
1289 END;
1290
1291 ----------------------------------------------------------
1292 l_debug_info := 'Active Employee = ' || l_is_active_employee;
1293 ----------------------------------------------------------
1294
1295 IF g_debug_switch = 'Y' THEN
1296 fnd_file.put_line(fnd_file.log, l_debug_info);
1297 END IF;
1298
1299 IF l_is_active_employee = 'N' THEN
1300
1301 l_reject_code := 'INACTIVE EMPLOYEE';
1302 raise validation_failed;
1303
1304 END IF;
1305
1306
1307 ELSE
1308
1309 ----------------------------------------------------------
1310 l_debug_info := 'Employee is contingent worker';
1311 ----------------------------------------------------------
1312
1313 IF g_debug_switch = 'Y' THEN
1314 fnd_file.put_line(fnd_file.log, l_debug_info);
1315 END IF;
1316
1317 END IF;
1318
1319
1320 ----------------------------------------------------------
1321 l_debug_info := 'Check the Default Payment Method';
1322 ----------------------------------------------------------
1323 IF g_debug_switch = 'Y' THEN
1324 fnd_file.put_line(fnd_file.log, l_debug_info);
1325 END IF;
1326
1327 if (l_invoice_rec.payment_method_code is null) then
1328
1329 ----------------------------------------------------------
1330 l_debug_info := 'Get the Default Payment Method';
1331 ----------------------------------------------------------
1332 IF g_debug_switch = 'Y' THEN
1333 fnd_file.put_line(fnd_file.log, l_debug_info);
1334 END IF;
1335
1336 l_trx_attributes.application_id := 200;
1337
1338 ----------------------------------------------------------
1339 l_debug_info := 'Getting the legal entity id';
1340 ----------------------------------------------------------
1341 IF g_debug_switch = 'Y' THEN
1342 fnd_file.put_line(fnd_file.log, l_debug_info);
1343 END IF;
1344
1345
1346 AP_UTILITIES_PKG.Get_Invoice_LE(
1347 l_vendor_rec.vendor_site_id,
1348 nvl(l_invoice_rec.accts_pay_ccid, l_vendor_rec.liab_acc),
1349 nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL)),
1350 l_le_id);
1351
1352 l_trx_attributes.payer_legal_entity_id := l_le_id;
1353 l_trx_attributes.payer_org_type := 'OPERATING_UNIT';
1354 l_trx_attributes.payer_org_id := nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL));
1355 l_trx_attributes.payee_party_id := l_vendor_rec.party_id;
1356 --l_trx_attributes.payee_party_site_id := p_payee_party_site_id;
1357 l_trx_attributes.supplier_site_id := l_vendor_rec.vendor_site_id;
1358 l_trx_attributes.payment_currency := l_invoice_rec.default_currency_code;
1359 l_trx_attributes.payment_amount := l_invoice_rec.total;
1360 l_trx_attributes.payment_function := 'PAYABLES_DISB';
1361 l_trx_attributes.pay_proc_trxn_type_code := 'EMPLOYEE_EXP';
1362
1363
1364 ----------------------------------------------------------
1365 l_debug_info := 'Calling iby_disbursement_comp_pub.get_default_payment_attributes';
1366 ----------------------------------------------------------
1367 IF g_debug_switch = 'Y' THEN
1368 fnd_file.put_line(fnd_file.log, l_debug_info);
1369 END IF;
1370
1371
1372 iby_disbursement_comp_pub.get_default_payment_attributes(
1373 p_api_version => 1.0,
1374 p_trxn_attributes_rec => l_trx_attributes,
1375 p_ignore_payee_pref => 'N',
1376 x_return_status => l_return_status,
1377 x_msg_count => l_msg_count,
1378 x_msg_data => l_msg_data,
1379 x_default_pmt_attrs_rec => l_result_pmt_attributes);
1380
1381 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1382
1383 l_invoice_rec.payment_method_code := l_result_pmt_attributes.payment_method.Payment_Method_Code;
1384
1385 ELSE
1386
1387 ----------------------------------------------------------
1388 l_debug_info := 'Calling get_default_payment_attributes is failed with result ' || l_return_status;
1389 ----------------------------------------------------------
1390
1391 IF g_debug_switch = 'Y' THEN
1392 fnd_file.put_line(fnd_file.log, l_debug_info);
1393 END IF;
1394
1395 END IF;
1396
1397 end if;
1398
1399 ----------------------------------------------------------
1400 l_debug_info := 'l_invoice_rec.payment_method_code := '|| l_invoice_rec.payment_method_code;
1401 ----------------------------------------------------------
1402 IF g_debug_switch = 'Y' THEN
1403 fnd_file.put_line(fnd_file.log, l_debug_info);
1404 END IF;
1405
1406
1407 ----------------------------------------------------------
1408 l_debug_info := 'Check for apply advance default flag := ' || l_invoice_rec.apply_advances_flag;
1409 ----------------------------------------------------------
1410
1411 IF g_debug_switch = 'Y' THEN
1412 fnd_file.put_line(fnd_file.log, l_debug_info);
1413 END IF;
1414
1415 -- Bug: 7329159, Donot reset the advances flag for XpenseXpress
1416 IF nvl(l_invoice_rec.advance_invoice_to_apply, -1) = -1 AND
1417 NOT l_invoice_rec.source IN ('Both Pay', 'CREDIT CARD', 'XpenseXpress') THEN
1418
1419 ----------------------------------------------------------
1420 l_debug_info := 'Rechecking if apply_advance_flag needs to be reset';
1421 ----------------------------------------------------------
1422
1423 IF g_debug_switch = 'Y' THEN
1424 fnd_file.put_line(fnd_file.log, l_debug_info);
1425 END IF;
1426
1427 ----------------------------------------------------------
1428 l_debug_info := 'Apply Advance in Payable options := ' || l_sys_apply_advances_flag;
1429 ----------------------------------------------------------
1430
1431 IF g_debug_switch = 'Y' THEN
1432 fnd_file.put_line(fnd_file.log, l_debug_info);
1433 END IF;
1434
1435 --------------------------------------------------------------------
1436 l_debug_info := 'Calculate available prepayments for this employee';
1437 --------------------------------------------------------------------
1438
1439 IF g_debug_switch = 'Y' THEN
1440 fnd_file.put_line(fnd_file.log, l_debug_info);
1441 END IF;
1442
1443 l_available_prepays := 0;
1444 -- Bug#7440653 - Should not apply advances when invoice total is zero.
1445 IF ( l_vendor_rec.vendor_id IS NOT NULL AND l_invoice_rec.total > 0 ) THEN
1446
1447 BEGIN
1448
1449 SELECT nvl(sum(decode(payment_status_flag, 'Y',
1450 decode(sign(earliest_settlement_date - sysdate),1,0,1),
1451 0)), 0)
1452 INTO l_available_prepays
1453 FROM ap_invoices I,
1454 ap_suppliers PV
1455 WHERE exists (SELECT 'x'
1456 FROM ap_invoice_distributions aid
1457 WHERE aid.invoice_id = i.invoice_id
1458 AND aid.line_type_lookup_code IN ('ITEM','TAX')
1459 AND NVL(aid.reversal_flag,'N') <> 'Y'
1460 AND nvl(aid.prepay_amount_remaining, aid.amount) > 0 )
1461 AND I.vendor_id = PV.vendor_id
1462 AND PV.employee_id = l_invoice_rec.employee_id
1463 AND I.invoice_type_lookup_code = 'PREPAYMENT'
1464 AND earliest_settlement_date IS NOT NULL
1465 AND I.invoice_amount > 0
1466 AND I.invoice_currency_code = l_invoice_rec.default_currency_code
1467 AND PV.vendor_id = l_vendor_rec.vendor_id;
1468
1469 EXCEPTION
1470 WHEN NO_DATA_FOUND THEN
1471 l_available_prepays := 0;
1472 END;
1473 -- Bug#7440653 - Should not apply advances when invoice total is zero.
1474 ELSIF ( l_invoice_rec.total > 0 ) THEN
1475
1476 BEGIN
1477
1478 SELECT nvl(sum(decode(payment_status_flag, 'Y',
1479 decode(sign(earliest_settlement_date - sysdate),1,0,1),
1480 0)), 0)
1481 INTO l_available_prepays
1482 FROM ap_invoices I,
1483 ap_suppliers PV
1484 WHERE exists (SELECT 'x'
1485 FROM ap_invoice_distributions aid
1486 WHERE aid.invoice_id = i.invoice_id
1487 AND aid.line_type_lookup_code IN ('ITEM','TAX')
1488 AND NVL(aid.reversal_flag,'N') <> 'Y'
1489 AND nvl(aid.prepay_amount_remaining, aid.amount) > 0 )
1490 AND I.vendor_id = PV.vendor_id
1491 AND PV.employee_id = l_invoice_rec.employee_id
1492 AND I.invoice_type_lookup_code = 'PREPAYMENT'
1493 AND earliest_settlement_date IS NOT NULL
1494 AND I.invoice_amount > 0
1495 AND I.invoice_currency_code = l_invoice_rec.default_currency_code;
1496
1497 EXCEPTION
1498 WHEN NO_DATA_FOUND THEN
1499 l_available_prepays := 0;
1500 END;
1501
1502 END IF;
1503
1504
1505 ----------------------------------------------------------
1506 l_debug_info := 'Available Prepayment Sign := ' || l_available_prepays;
1507 ----------------------------------------------------------
1508
1509 IF g_debug_switch = 'Y' THEN
1510 fnd_file.put_line(fnd_file.log, l_debug_info);
1511 END IF;
1512
1513
1514 IF l_invoice_rec.apply_advances_flag = 'Y' AND
1515 (l_sys_apply_advances_flag <> 'Y' OR
1516 l_available_prepays = 0) THEN
1517
1518 ----------------------------------------------------------
1519 l_debug_info := 'Resetting the apply advance default to N ';
1520 ----------------------------------------------------------
1521
1522 IF g_debug_switch = 'Y' THEN
1523 fnd_file.put_line(fnd_file.log, l_debug_info);
1524 END IF;
1525
1526
1527 UPDATE ap_expense_report_headers_all
1528 SET apply_advances_default = 'N'
1529 WHERE report_header_id = l_invoice_rec.report_header_id;
1530
1531 l_invoice_rec.apply_advances_flag := 'N';
1532
1533 ELSIF l_invoice_rec.apply_advances_flag = 'N' AND
1534 l_sys_apply_advances_flag = 'Y' AND
1535 l_available_prepays > 0 THEN
1536
1537 ----------------------------------------------------------
1538 l_debug_info := 'Resetting the apply advance default to Y ';
1539 ----------------------------------------------------------
1540
1541 IF g_debug_switch = 'Y' THEN
1542 fnd_file.put_line(fnd_file.log, l_debug_info);
1543 END IF;
1544
1545
1546 UPDATE ap_expense_report_headers_all
1547 SET apply_advances_default = 'Y'
1548 WHERE report_header_id = l_invoice_rec.report_header_id;
1549
1550 l_invoice_rec.apply_advances_flag := 'Y';
1551
1552 END IF;
1553
1554 END IF;
1555
1556
1557
1558 ----------------------------------------------------------
1559 l_debug_info := 'Insert into AP Invoices Interface table';
1560 ----------------------------------------------------------
1561 IF g_debug_switch = 'Y' THEN
1562 fnd_file.put_line(fnd_file.log, l_debug_info);
1563 END IF;
1564
1565
1566 InsertInvoiceInterface(l_invoice_rec, l_vendor_rec);
1567 -- Bug: 6809570
1568 InsertInvoiceLinesInterface(l_invoice_rec.report_header_id, l_invoice_rec.invoice_id,
1569 p_transfer_flag, l_base_currency, l_enable_recoverable_flag);
1570
1571
1572 if (l_invoice_rec.source in ('Both Pay', 'CREDIT CARD')) then
1573
1574 ------------------------------------------------------------
1575 l_debug_info := 'Processing '||l_invoice_rec.source;
1576 ------------------------------------------------------------
1577 IF g_debug_switch = 'Y' THEN
1578 fnd_file.put_line(fnd_file.log, l_debug_info);
1579 END IF;
1580
1581 ------------------------------------------------------------
1582 l_debug_info := 'Submitting Payment Request';
1583 ------------------------------------------------------------
1584 IF g_debug_switch = 'Y' THEN
1585 fnd_file.put_line(fnd_file.log, l_debug_info);
1586 END IF;
1587
1588 l_cc_invoices_fetched := l_cc_invoices_fetched + 1;
1589 l_rows_to_import := l_rows_to_import - 1;
1590
1591 AP_IMPORT_INVOICES_PKG.SUBMIT_PAYMENT_REQUEST(
1592 p_api_version => 1.0,
1593 p_invoice_interface_id => l_invoice_rec.invoice_id,
1594 p_budget_control => 'N',
1595 p_needs_invoice_approval=> 'N',
1596 p_invoice_id => l_invoice_rec.invoice_id,
1597 x_return_status => x_return_status,
1598 x_msg_count => x_msg_count,
1599 x_msg_data => x_msg_data,
1600 x_rejection_list => l_rejection_list,
1601 p_calling_sequence => 'AP_WEB_EXPORT_ER.ExportERtoAP',
1602 p_commit => FND_API.G_FALSE);
1603
1604 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1605
1606 FOR i in l_rejection_list.FIRST .. l_rejection_list.LAST LOOP
1607 l_debug_info := i||' Errors found interfacing data to AP ...';
1608 fnd_file.put_line(fnd_file.log, l_debug_info);
1609 l_debug_info := l_rejection_list(i).reject_lookup_code;
1610 fnd_file.put_line(fnd_file.log, l_debug_info);
1611 l_reject_code := l_rejection_list(i).reject_lookup_code;
1612 END LOOP;
1613
1614 raise validation_failed;
1615
1616 ELSE
1617
1618 l_cc_invoices_created := l_cc_invoices_created + 1;
1619
1620 END IF;
1621
1622 end if; /* (l_invoice_rec.source in ('Both Pay', 'CREDIT CARD')) */
1623
1624 ------------------------------------------------------------
1625 l_debug_info := 'Update request_id';
1626 ------------------------------------------------------------
1627 IF g_debug_switch = 'Y' THEN
1628 fnd_file.put_line(fnd_file.log, l_debug_info);
1629 END IF;
1630
1631 UPDATE ap_expense_report_headers_all
1632 SET request_id = l_request_id
1633 WHERE report_header_id = l_invoice_rec.report_header_id;
1634
1635
1636 EXCEPTION
1637 WHEN validation_failed then
1638 ------------------------------------------------------------
1639 l_debug_info := 'Validation failed';
1640 ------------------------------------------------------------
1641 IF g_debug_switch = 'Y' THEN
1642 fnd_file.put_line(fnd_file.log, l_debug_info);
1643 END IF;
1644
1645 UPDATE ap_expense_report_headers_all
1646 SET reject_code = l_reject_code,
1647 request_id = l_request_id,
1648 vouchno = 0
1649 WHERE report_header_id = l_invoice_rec.report_header_id;
1650 END;
1651
1652 END LOOP;
1653
1654 l_rows_to_import := l_rows_to_import + c_expenses_to_import%ROWCOUNT;
1655
1656 -------------------------------------------------------------------
1657 l_debug_info := 'Credit Card Expenses Fetched = '|| to_char(l_cc_invoices_fetched);
1658 -------------------------------------------------------------------
1659 fnd_file.put_line(fnd_file.log, l_debug_info);
1660
1661 -------------------------------------------------------------------
1662 l_debug_info := 'Credit Card Invoices Created = '|| to_char(l_cc_invoices_created);
1663 -------------------------------------------------------------------
1664 fnd_file.put_line(fnd_file.log, l_debug_info);
1665
1666 CLOSE c_expenses_to_import;
1667
1668 IF l_rows_to_import = 0 THEN
1669 ------------------------------------------
1670 l_debug_info := 'No Rows found to import';
1671 ------------------------------------------
1672 fnd_file.put_line(fnd_file.log, l_debug_info);
1673
1674 ELSE
1675 -----------------------------------------------
1676 l_debug_info := 'Call Payables Open Interface';
1677 -----------------------------------------------
1678 fnd_file.put_line(fnd_file.log, l_debug_info);
1679
1680 IF (NOT AP_IMPORT_INVOICES_PKG.IMPORT_INVOICES(l_batch_name,
1681 l_invoice_rec.gl_date,
1682 to_char(''),
1683 to_char(''),
1684 p_commit_cycles,
1685 p_source,
1686 p_group_id,
1687 l_request_id,
1688 p_debug_switch,
1689 p_org_id,
1690 l_batch_error_flag,
1691 l_invoices_fetched,
1692 l_invoices_created,
1693 l_total_invoice_amount,
1694 l_print_batch,
1695 l_calling_sequence)) THEN
1696 -----------------------------------------------------------------------------------
1697 l_debug_info := 'Call to AP_IMPORT_INVOICES_PKG.IMPORT_INVOICES failed. Aborting.';
1698 -----------------------------------------------------------------------------------
1699 fnd_file.put_line(fnd_file.log, l_debug_info);
1700
1701 RAISE batch_failure;
1702 END IF;
1703
1704 END IF; /* l_rows_to_import = 0 */
1705
1706 l_invoices_fetched := l_invoices_fetched + l_cc_invoices_fetched;
1707 l_invoices_created := l_invoices_created + l_cc_invoices_created;
1708 l_failed_open_interface := l_invoices_fetched - l_invoices_created;
1709
1710 IF l_invoices_created >0 THEN
1711 OPEN c_successful_invoices (l_request_id);
1712 FETCH c_successful_invoices BULK COLLECT INTO l_invoice_id_list, l_report_header_id_list,
1713 l_oie_applied_prepay_list, l_oie_applied_amt_list;
1714
1715 FORALL i IN l_report_header_id_list.FIRST .. l_report_header_id_list.LAST
1716 UPDATE ap_expense_report_headers_all
1717 SET vouchno = l_invoice_id_list(i),
1718 reject_code = null
1719 WHERE report_header_id = l_report_header_id_list(i);
1720
1721 --Upadating the expense stataus code for reports which have been paid by advances
1722
1723 FOR i IN l_report_header_id_list.FIRST .. l_report_header_id_list.LAST
1724 LOOP
1725
1726 l_actual_adv_applied := NULL;
1727 --Bug#6988193 - Update the advance applied for a report during export
1728 IF l_oie_applied_prepay_list(i) IS NOT NULL THEN
1729 BEGIN
1730 SELECT abs(nvl(amount,0))
1731 INTO l_actual_adv_applied
1732 FROM ap_invoice_lines_all
1733 WHERE invoice_id = l_invoice_id_list(i)
1734 AND line_type_lookup_code = 'PREPAY'
1735 AND prepay_invoice_id = l_oie_applied_prepay_list(i);
1736 EXCEPTION
1737 WHEN NO_DATA_FOUND THEN
1738 l_actual_adv_applied := 0;
1739 END;
1740
1741 IF ( l_oie_applied_amt_list(i) <> l_actual_adv_applied) THEN
1742 UPDATE ap_expense_report_headers_all
1743 SET maximum_amount_to_apply = l_actual_adv_applied,
1744 amt_due_employee = ( nvl(amt_due_employee,0) + (l_oie_applied_amt_list(i) - l_actual_adv_applied) )
1745 WHERE report_header_id = l_report_header_id_list(i);
1746
1747 -----------------------------------------------------------------------
1748 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);
1749 -----------------------------------------------------------------------
1750 fnd_file.put_line(fnd_file.log, l_debug_info);
1751
1752 END IF;
1753 END IF;
1754
1755 BEGIN
1756 SELECT sum(amount)
1757 INTO l_inv_total_amount
1758 FROM ap_invoice_lines_all ap1
1759 WHERE invoice_id = l_invoice_id_list(i);
1760 EXCEPTION
1761 WHEN NO_DATA_FOUND THEN
1762 l_inv_total_amount :=-1;
1763 END;
1764 IF (l_inv_total_amount = 0) THEN
1765 l_expense_status_code := 'PAID';
1766 BEGIN
1767 SELECT payment_due_from_code
1768 INTO l_payment_due_frm
1769 FROM ap_credit_card_trxns_all trx
1770 WHERE trx.report_header_id =l_report_header_id_list(i)
1771 AND trx.category ='BUSINESS'
1772 AND rownum = 1;
1773 EXCEPTION
1774 WHEN NO_DATA_FOUND THEN
1775 l_payment_due_frm :=NULL;
1776 END;
1777 --Updating status for reports containing credit card transactions for which the cash part has been fully paid by advances.
1778 IF (l_payment_due_frm ='BOTH') THEN
1779 l_expense_status_code := 'PARPAID';
1780 END IF;
1781
1782 UPDATE ap_expense_report_headers_all ah
1783 SET expense_status_code = l_expense_status_code
1784 WHERE report_header_id = l_report_header_id_list(i) ;
1785
1786 END IF;
1787 END LOOP;
1788
1789 CLOSE c_successful_invoices;
1790 END IF;
1791
1792
1793 l_invoice_id_list.DELETE;
1794 l_report_header_id_list.DELETE;
1795
1796
1797 IF l_failed_open_interface <> 0 THEN
1798 -----------------------------------------------------------
1799 l_debug_info := 'Invoices that failed Open Interface *' ||
1800 to_char(l_failed_open_interface) || '*';
1801 -----------------------------------------------------------
1802 IF g_debug_switch = 'Y' THEN
1803 fnd_file.put_line(fnd_file.log, l_debug_info);
1804 END IF;
1805
1806 OPEN c_rejected_invoices(l_request_id);
1807
1808 FETCH c_rejected_invoices BULK COLLECT
1809 INTO l_report_header_id_list, l_reject_code_list, l_invoice_id_list;
1810
1811 IF l_report_header_id_list.COUNT > 0 THEN
1812
1813 -----------------------------------------------------------------------
1814 l_debug_info := 'Opening cursor for deleting from AP interface tables';
1815 -----------------------------------------------------------------------
1816
1817 IF g_debug_switch = 'Y' THEN
1818 fnd_file.put_line(fnd_file.log, l_debug_info);
1819 END IF;
1820
1821 FORALL i IN l_report_header_id_list.FIRST .. l_report_header_id_list.LAST
1822 UPDATE ap_expense_report_headers_all
1823 SET reject_code = l_reject_code_list(i), vouchno = 0
1824 WHERE report_header_id = l_report_header_id_list(i)
1825 and nvl(vouchno,0) = 0;
1826
1827 FORALL i IN l_invoice_id_list.FIRST .. l_invoice_id_list.LAST
1828 DELETE FROM ap_interface_rejections
1829 WHERE parent_table = 'AP_INVOICES_INTERFACE'
1830 AND parent_id = l_invoice_id_list(i);
1831
1832 FORALL i IN l_invoice_id_list.FIRST .. l_invoice_id_list.LAST
1833 DELETE FROM ap_interface_rejections
1834 WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
1835 AND parent_id IN
1836 (SELECT invoice_line_id
1837 FROM ap_invoice_lines_interface
1838 WHERE invoice_id = l_invoice_id_list(i));
1839
1840 FORALL i IN l_invoice_id_list.FIRST .. l_invoice_id_list.LAST
1841 DELETE FROM ap_invoices_interface
1842 WHERE invoice_id = l_invoice_id_list(i);
1843
1844 FORALL i IN l_invoice_id_list.FIRST .. l_invoice_id_list.LAST
1845 DELETE FROM ap_invoice_lines_interface
1846 WHERE invoice_id = l_invoice_id_list(i);
1847
1848 END IF;
1849
1850 CLOSE c_rejected_invoices;
1851
1852 END IF;
1853
1854 IF (l_expenses_fetched <> 0) THEN
1855 ----------------------------------------------------------
1856 l_debug_info := 'Purge data from the ap interface tables';
1857 ----------------------------------------------------------
1858 IF g_debug_switch = 'Y' THEN
1859 fnd_file.put_line(fnd_file.log, l_debug_info);
1860 END IF;
1861
1862 IF (NOT AP_IMPORT_INVOICES_PKG.IMPORT_PURGE(p_source,
1863 p_group_id,
1864 null, -- p_org_id
1865 p_commit_cycles,
1866 l_calling_sequence)) THEN
1867
1868 ----------------------------------------------------------------------
1869 l_debug_info := 'Purge from the ap interface tables failed. Aborting';
1870 ----------------------------------------------------------------------
1871 fnd_file.put_line(fnd_file.log, l_debug_info);
1872 raise batch_failure;
1873 END IF;
1874 END IF; /* l_expenses_fetched <> 0 */
1875
1876
1877 -------------------------------------------------------------------
1878 l_debug_info := 'Expenses Fetched = '|| to_char(l_expenses_fetched);
1879 -------------------------------------------------------------------
1880 fnd_file.put_line(fnd_file.log, l_debug_info);
1881
1882 -------------------------------------------------------------------
1883 l_debug_info := 'Invoices Created = '|| to_char(l_invoices_created);
1884 -------------------------------------------------------------------
1885 fnd_file.put_line(fnd_file.log, l_debug_info);
1886
1887 IF (l_expenses_fetched <> l_invoices_created AND
1888 p_role_name IS NOT NULL )THEN
1889
1890 ---------------------------------------------------
1891 l_debug_info := 'Call Expenses Rejection Workflow';
1892 ---------------------------------------------------
1893 IF g_debug_switch = 'Y' THEN
1894 fnd_file.put_line(fnd_file.log, l_debug_info);
1895 END IF;
1896
1897 AP_WEB_EXPORT_WF.RaiseRejectionEvent(l_request_id,
1898 p_role_name );
1899 END IF;
1900
1901 COMMIT;
1902
1903 EXCEPTION
1904 WHEN batch_failure THEN
1905 fnd_file.put_line(fnd_file.log, sqlerrm);
1906 rollback;
1907 raise;
1908 WHEN OTHERS THEN
1909 fnd_file.put_line(fnd_file.log, sqlerrm);
1910 rollback;
1911 raise;
1912
1913 END ExportERtoAP;
1914
1915 ---------------------------------------------------------------------------------------
1916 FUNCTION ValidateERLines(p_report_header_id IN NUMBER,
1917 p_invoice_id IN NUMBER,
1918 p_transfer_flag IN VARCHAR2,
1919 p_base_currency IN VARCHAR2,
1920 p_set_of_books_id IN NUMBER,
1921 p_source IN VARCHAR2,
1922 p_enable_recoverable_flag IN VARCHAR2,
1923 p_invoice_lines_rec_tab OUT NOCOPY InvoiceLinesRecTabType,
1924 p_reject_code OUT NOCOPY VARCHAR2)
1925 RETURN BOOLEAN IS
1926 ---------------------------------------------------------------------------------------
1927
1928 CURSOR c_expense_lines(l_report_header_id NUMBER, p_base_currency VARCHAR2) IS
1929 SELECT xl.report_header_id,
1930 xl.report_line_id,
1931 gcc.code_combination_id code_combination_id,
1932 nvl(lc.lookup_code, '') line_type_lookup_code,
1933 nvl(xl.vat_code, '') line_vat_code,
1934 nvl(xl.tax_code_id, -1) line_tax_code_id,
1935 SIGN(nvl(amount, 0)) distribution_amount_sign,
1936 SIGN(nvl(stat_amount, 0)) stat_amount_sign,
1937 to_char(nvl(xl.stat_amount, '')) stat_amount,
1938 xl.set_of_books_id line_set_of_books_id,
1939 to_char(nvl(ap_utilities_pkg.ap_round_currency(xl.amount,
1940 XH.default_currency_code),
1941 0)) distribution_amount,
1942 nvl(xl.item_description, '') item_description,
1943 xl.line_type_lookup_code db_line_type,
1944 xl.distribution_line_number,
1945 to_char(decode(p_base_currency,
1946 xh.default_currency_code,
1947 null,
1948 DECODE(F.minimum_accountable_unit,
1949 '',
1950 ROUND(ap_utilities_pkg.ap_round_currency(xl.amount,
1951 XH.default_currency_code) *
1952 xh.default_exchange_rate,
1953 F.precision),
1954 ROUND(ap_utilities_pkg.ap_round_currency(xl.amount,
1955 XH.default_currency_code) *
1956 xh.default_exchange_rate /
1957 F.minimum_accountable_unit) *
1958 F.minimum_accountable_unit))) base_amount,
1959 DECODE(nvl(gcc.account_type, 'x'), 'A', 'Y', 'N') assets_tracking_flag,
1960 nvl(decode(p_transfer_flag, 'Y', xl.attribute1), ''),
1961 nvl(decode(p_transfer_flag, 'Y', xl.attribute2), ''),
1962 nvl(decode(p_transfer_flag, 'Y', xl.attribute3), ''),
1963 nvl(decode(p_transfer_flag, 'Y', xl.attribute4), ''),
1964 nvl(decode(p_transfer_flag, 'Y', xl.attribute5), ''),
1965 nvl(decode(p_transfer_flag, 'Y', xl.attribute6), ''),
1966 nvl(decode(p_transfer_flag, 'Y', xl.attribute7), ''),
1967 nvl(decode(p_transfer_flag, 'Y', xl.attribute8), ''),
1968 nvl(decode(p_transfer_flag, 'Y', xl.attribute9), ''),
1969 nvl(decode(p_transfer_flag, 'Y', xl.attribute10), ''),
1970 nvl(decode(p_transfer_flag, 'Y', xl.attribute11), ''),
1971 nvl(decode(p_transfer_flag, 'Y', xl.attribute12), ''),
1972 nvl(decode(p_transfer_flag, 'Y', xl.attribute13), ''),
1973 nvl(decode(p_transfer_flag, 'Y', xl.attribute14), ''),
1974 nvl(decode(p_transfer_flag, 'Y', xl.attribute15), ''),
1975 nvl(decode(p_transfer_flag, 'Y', xl.attribute_category), ''),
1976 nvl(xl.project_accounting_context, ''),
1977 nvl(to_char(xl.project_id), ''),
1978 nvl(to_char(xl.task_id), ''),
1979 nvl(to_char(xl.expenditure_organization_id), ''),
1980 nvl(xl.expenditure_type, ''),
1981 nvl(to_char(xl.expenditure_item_date), ''),
1982 nvl(to_char(xl.pa_quantity), ''),
1983 nvl(xl.reference_1, ''),
1984 nvl(xl.reference_2, ''),
1985 nvl(to_char(xl.awt_group_id), ''),
1986 xl.amount_includes_tax_flag,
1987 nvl(xl.tax_code_override_flag, 'N'),
1988 '' tax_recovery_rate,
1989 'N' tax_recovery_override_flag,
1990 nvl(decode(p_enable_recoverable_flag,
1991 'Y',
1992 decode(xl.line_type_lookup_code, 'TAX', 'Y', 'N'),
1993 'N'),
1994 'N') tax_recoverable_flag,
1995 xl.global_attribute1,
1996 xl.global_attribute2,
1997 xl.global_attribute3,
1998 xl.global_attribute4,
1999 xl.global_attribute5,
2000 xl.global_attribute6,
2001 xl.global_attribute7,
2002 xl.global_attribute8,
2003 xl.global_attribute9,
2004 xl.global_attribute10,
2005 xl.global_attribute11,
2006 xl.global_attribute12,
2007 xl.global_attribute13,
2008 xl.global_attribute14,
2009 xl.global_attribute15,
2010 xl.global_attribute16,
2011 xl.global_attribute17,
2012 xl.global_attribute18,
2013 xl.global_attribute19,
2014 xl.global_attribute20,
2015 xl.global_attribute_category,
2016 nvl(xl.receipt_verified_flag, ''),
2017 nvl(xl.receipt_required_flag, ''),
2018 nvl(xl.receipt_missing_flag, ''),
2019 nvl(xl.justification, ''),
2020 nvl(xl.expense_group, ''),
2021 to_char(nvl(xl.start_expense_date, '')),
2022 to_char(nvl(xl.start_expense_date, xh.week_end_date)) start_expense_date2,
2023 to_char(nvl(xl.end_expense_date, '')),
2024 nvl(xl.merchant_document_number, ''),
2025 nvl(xl.merchant_name, ''),
2026 nvl(xl.merchant_reference, ''),
2027 nvl(xl.merchant_tax_reg_number, ''),
2028 nvl(xl.merchant_taxpayer_id, ''),
2029 nvl(xl.country_of_supply, ''),
2030 nvl(xl.receipt_currency_code, ''),
2031 to_char(nvl(xl.receipt_conversion_rate, '')),
2032 to_char(nvl(xl.receipt_currency_amount, '')),
2033 to_char(nvl(xl.daily_amount, '')),
2034 to_char(nvl(xl.web_parameter_id, '')),
2035 nvl(xl.adjustment_reason, ''),
2036 nvl(xl.credit_card_trx_id, ''),
2037 nvl(xl.company_prepaid_invoice_id, ''),
2038 xl.created_by,
2039 '' pa_addition_flag,
2040 '' type_1099,
2041 '' income_tax_region,
2042 '' award_id,
2043 '' invoice_id,
2044 '' accounting_date,
2045 XL.org_id org_id
2046 FROM ap_expense_report_lines XL,
2047 gl_code_combinations gcc,
2048 ap_lookup_codes lc,
2049 fnd_currencies F,
2050 ap_expense_report_headers XH
2051 WHERE XL.report_header_id = XH.report_header_id
2052 AND XH.report_header_id = p_report_header_id
2053 AND XL.code_combination_id = gcc.code_combination_id(+)
2054 AND nvl(XL.itemization_parent_id,0) <> -1 /* Itemization Project */
2055 AND lc.lookup_code(+) = XL.line_type_lookup_code
2056 AND lc.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
2057 AND F.currency_code = p_base_currency;
2058
2059 l_debug_info VARCHAR2(2000);
2060 line_validation_failed EXCEPTION;
2061 i BINARY_INTEGER := 0;
2062
2063 BEGIN
2064 ------------------------------------------------
2065 l_debug_info := 'Start Exporting Expense Lines';
2066 ------------------------------------------------
2067 IF g_debug_switch = 'Y' THEN
2068 fnd_file.put_line(fnd_file.log, l_debug_info);
2069 END IF;
2070
2071 OPEN c_expense_lines(p_report_header_id, p_base_currency);
2072
2073 LOOP
2074 FETCH c_expense_lines
2075 INTO p_invoice_lines_rec_tab(i);
2076
2077 IF c_expense_lines%NOTFOUND THEN
2078 EXIT;
2079 END IF;
2080
2081 ---------------------------------------------------------------------------
2082 l_debug_info := 'report_header_id = **'||to_char(p_report_header_id)||'**';
2083 ---------------------------------------------------------------------------
2084 IF g_debug_switch = 'Y' THEN
2085 fnd_file.put_line(fnd_file.log, l_debug_info);
2086 END IF;
2087
2088 p_invoice_lines_rec_tab(i).invoice_id := p_invoice_id;
2089
2090 IF p_source = 'Oracle Project Accounting' then
2091
2092 ---------------------------------------------
2093 l_debug_info := 'Validate Line Set Of Books';
2094 ---------------------------------------------
2095 IF g_debug_switch = 'Y' THEN
2096 fnd_file.put_line(fnd_file.log, l_debug_info);
2097 END IF;
2098
2099 IF p_invoice_lines_rec_tab(i).line_set_of_books_id <> p_set_of_books_id THEN
2100 ---------------------------------------------------------------------------------
2101 l_debug_info := 'Invalid set of books-line *' ||
2102 to_char(p_invoice_lines_rec_tab(i) .line_set_of_books_id) || '*';
2103 ---------------------------------------------------------------------------------
2104 fnd_file.put_line(fnd_file.log, l_debug_info);
2105
2106 p_reject_code := 'Invalid set of books-line';
2107 raise line_validation_failed;
2108 END IF;
2109 END IF;
2110
2111
2112 IF p_invoice_lines_rec_tab(i).project_id is NOT NULL THEN
2113 p_invoice_lines_rec_tab(i).pa_addition_flag := 'T';
2114 ELSE
2115 p_invoice_lines_rec_tab(i).pa_addition_flag := 'E';
2116 END IF;
2117
2118 IF p_invoice_lines_rec_tab(i).db_line_type = 'TAX' AND
2119 p_invoice_lines_rec_tab(i).line_vat_code IS NULL THEN
2120 p_reject_code := 'Tax code required';
2121 raise line_validation_failed;
2122 END IF;
2123
2124 i := i + 1;
2125
2126 END LOOP;
2127
2128 CLOSE c_expense_lines;
2129 RETURN(TRUE);
2130
2131 EXCEPTION
2132 WHEN line_validation_failed THEN
2133 CLOSE c_expense_lines;
2134 RETURN(FALSE);
2135 WHEN OTHERS THEN
2136 CLOSE c_expense_lines;
2137 p_reject_code := SQLCODE;
2138 fnd_file.put_line(fnd_file.log, SQLERRM);
2139 RETURN(FALSE);
2140
2141 END ValidateERLines;
2142
2143 ------------------------------------------------------------------------
2144 PROCEDURE InsertInvoiceInterface(p_invoice_rec InvoiceInfoRecType,
2145 p_vendor_rec VendorInfoRecType) IS
2146 ------------------------------------------------------------------------
2147 BEGIN
2148 INSERT INTO AP_INVOICES_INTERFACE
2149 (INVOICE_ID,
2150 APPLICATION_ID,
2151 PRODUCT_TABLE,
2152 REFERENCE_KEY1,
2153 INVOICE_NUM,
2154 INVOICE_TYPE_LOOKUP_CODE,
2155 INVOICE_DATE,
2156 VENDOR_ID,
2157 VENDOR_NUM,
2158 VENDOR_NAME,
2159 VENDOR_SITE_ID,
2160 VENDOR_SITE_CODE,
2161 INVOICE_AMOUNT,
2162 INVOICE_CURRENCY_CODE,
2163 EXCHANGE_RATE,
2164 EXCHANGE_RATE_TYPE,
2165 EXCHANGE_DATE,
2166 TERMS_ID,
2167 TERMS_NAME,
2168 DESCRIPTION,
2169 AWT_GROUP_ID,
2170 AWT_GROUP_NAME,
2171 LAST_UPDATE_DATE,
2172 LAST_UPDATED_BY,
2173 LAST_UPDATE_LOGIN,
2174 CREATION_DATE,
2175 CREATED_BY,
2176 ATTRIBUTE_CATEGORY,
2177 ATTRIBUTE1,
2178 ATTRIBUTE2,
2179 ATTRIBUTE3,
2180 ATTRIBUTE4,
2181 ATTRIBUTE5,
2182 ATTRIBUTE6,
2183 ATTRIBUTE7,
2184 ATTRIBUTE8,
2185 ATTRIBUTE9,
2186 ATTRIBUTE10,
2187 ATTRIBUTE11,
2188 ATTRIBUTE12,
2189 ATTRIBUTE13,
2190 ATTRIBUTE14,
2191 ATTRIBUTE15,
2192 GLOBAL_ATTRIBUTE_CATEGORY,
2193 GLOBAL_ATTRIBUTE1,
2194 GLOBAL_ATTRIBUTE2,
2195 GLOBAL_ATTRIBUTE3,
2196 GLOBAL_ATTRIBUTE4,
2197 GLOBAL_ATTRIBUTE5,
2198 GLOBAL_ATTRIBUTE6,
2199 GLOBAL_ATTRIBUTE7,
2200 GLOBAL_ATTRIBUTE8,
2201 GLOBAL_ATTRIBUTE9,
2202 GLOBAL_ATTRIBUTE10,
2203 GLOBAL_ATTRIBUTE11,
2204 GLOBAL_ATTRIBUTE12,
2205 GLOBAL_ATTRIBUTE13,
2206 GLOBAL_ATTRIBUTE14,
2207 GLOBAL_ATTRIBUTE15,
2208 GLOBAL_ATTRIBUTE16,
2209 GLOBAL_ATTRIBUTE17,
2210 GLOBAL_ATTRIBUTE18,
2211 GLOBAL_ATTRIBUTE19,
2212 GLOBAL_ATTRIBUTE20,
2213 STATUS,
2214 SOURCE,
2215 GROUP_ID,
2216 REQUEST_ID,
2217 PAYMENT_CROSS_RATE_TYPE,
2218 PAYMENT_CROSS_RATE_DATE,
2219 PAYMENT_CROSS_RATE,
2220 PAYMENT_CURRENCY_CODE,
2221 WORKFLOW_FLAG,
2222 DOC_CATEGORY_CODE,
2223 VOUCHER_NUM,
2224 PAY_GROUP_LOOKUP_CODE,
2225 GOODS_RECEIVED_DATE,
2226 INVOICE_RECEIVED_DATE,
2227 GL_DATE,
2228 ACCTS_PAY_CODE_COMBINATION_ID,
2229 ORG_ID,
2230 AMOUNT_APPLICABLE_TO_DISCOUNT,
2231 PREPAY_NUM,
2232 PREPAY_LINE_NUM,
2233 PREPAY_APPLY_AMOUNT,
2234 PREPAY_GL_DATE,
2235 INVOICE_INCLUDES_PREPAY_FLAG,
2236 NO_XRATE_BASE_AMOUNT,
2237 VENDOR_EMAIL_ADDRESS,
2238 TERMS_DATE,
2239 REQUESTER_ID,
2240 PAID_ON_BEHALF_EMPLOYEE_ID,
2241 PARTY_ID,
2242 PARTY_SITE_ID)
2243 VALUES
2244 (p_invoice_rec.invoice_id,
2245 200,
2246 'AP_EXPENSE_REPORT_HEADERS_ALL',
2247 p_invoice_rec.report_header_id,
2248 p_invoice_rec.invoice_num,
2249 p_invoice_rec.invoice_type_lookup_code,
2250 p_invoice_rec.week_end_date,
2251 p_invoice_rec.vendor_id,
2252 '',
2253 '',
2254 p_vendor_rec.vendor_site_id,
2255 '',
2256 p_invoice_rec.total,
2257 p_invoice_rec.default_currency_code,
2258 decode(p_invoice_rec.default_exchange_rate,-1,''),
2259 p_invoice_rec.default_exchange_rate_type,
2260 p_invoice_rec.default_exchange_date,
2261 p_vendor_rec.terms_id,
2262 '',
2263 p_invoice_rec.description,
2264 p_invoice_rec.awt_group_id,
2265 '',
2266 sysdate,
2267 g_last_updated_by,
2268 '',
2269 sysdate,
2270 p_invoice_rec.created_by,
2271 p_invoice_rec.attribute_category,
2272 p_invoice_rec.attribute1,
2273 p_invoice_rec.attribute2,
2274 p_invoice_rec.attribute3,
2275 p_invoice_rec.attribute4,
2276 p_invoice_rec.attribute5,
2277 p_invoice_rec.attribute6,
2278 p_invoice_rec.attribute7,
2279 p_invoice_rec.attribute8,
2280 p_invoice_rec.attribute9,
2281 p_invoice_rec.attribute10,
2282 p_invoice_rec.attribute11,
2283 p_invoice_rec.attribute12,
2284 p_invoice_rec.attribute13,
2285 p_invoice_rec.attribute14,
2286 p_invoice_rec.attribute15,
2287 p_invoice_rec.global_attribute_category,
2288 p_invoice_rec.global_attribute1,
2289 p_invoice_rec.global_attribute2,
2290 p_invoice_rec.global_attribute3,
2291 p_invoice_rec.global_attribute4,
2292 p_invoice_rec.global_attribute5,
2293 p_invoice_rec.global_attribute6,
2294 p_invoice_rec.global_attribute7,
2295 p_invoice_rec.global_attribute8,
2296 p_invoice_rec.global_attribute9,
2297 p_invoice_rec.global_attribute10,
2298 p_invoice_rec.global_attribute11,
2299 p_invoice_rec.global_attribute12,
2300 p_invoice_rec.global_attribute13,
2301 p_invoice_rec.global_attribute14,
2302 p_invoice_rec.global_attribute15,
2303 p_invoice_rec.global_attribute16,
2304 p_invoice_rec.global_attribute17,
2305 p_invoice_rec.global_attribute18,
2306 p_invoice_rec.global_attribute19,
2307 p_invoice_rec.global_attribute20,
2308 '',
2309 decode(p_invoice_rec.source,'CREDIT CARD',
2310 'SelfService',
2311 'Both Pay',
2312 'SelfService',
2313 p_invoice_rec.source),
2314 p_invoice_rec.group_id,
2315 FND_GLOBAL.CONC_REQUEST_ID,
2316 p_invoice_rec.payment_cross_rate_type,
2317 p_invoice_rec.payment_cross_rate_date,
2318 p_invoice_rec.payment_cross_rate,
2319 p_invoice_rec.payment_currency_code,
2320 '',
2321 p_invoice_rec.doc_category_code,
2322 p_invoice_rec.voucher_num,
2323 p_vendor_rec.pay_group,
2324 '',
2325 '',
2326 p_invoice_rec.gl_date,
2327 nvl(decode(p_invoice_rec.accts_pay_ccid, -1, p_vendor_rec.liab_acc), p_vendor_rec.liab_acc),
2328 p_invoice_rec.org_id,
2329 p_invoice_rec.amount_app_to_discount,
2330 decode(p_invoice_rec.apply_advances_flag,
2331 'Y',
2332 p_invoice_rec.prepay_num,
2333 ''),
2334 decode(p_invoice_rec.apply_advances_flag,
2335 'Y',
2336 p_invoice_rec.prepay_dist_num,
2337 ''),
2338 decode(p_invoice_rec.apply_advances_flag,
2339 'Y',
2340 p_invoice_rec.amount_want_to_apply,
2341 ''),
2342 p_invoice_rec.prepay_gl_date,
2343 '',
2344 '',
2345 '',
2346 decode(p_vendor_rec.terms_date_basis,
2347 'Current',
2348 sysdate,
2349 p_invoice_rec.week_end_date),
2350 '',
2351 p_invoice_rec.paid_on_behalf_employee_id,
2352 p_vendor_rec.party_id,
2353 decode(p_invoice_rec.invoice_type_lookup_code,
2354 'PAYMENT REQUEST',
2355 p_vendor_rec.party_site_id,
2356 ''));
2357
2358 END InsertInvoiceInterface;
2359
2360 ------------------------------------------------------------------------------------------
2361 --- Bug: 6809570
2362 ------------------------------------------------------------------------------------------
2363 PROCEDURE InsertInvoiceLinesInterface(p_report_header_id IN NUMBER,
2364 p_invoice_id IN NUMBER,
2365 p_transfer_flag IN VARCHAR2,
2366 p_base_currency IN VARCHAR2,
2367 p_enable_recoverable_flag IN VARCHAR2) IS
2368 ------------------------------------------------------------------------------------------
2369 l_debug_info VARCHAR2(2000);
2370 BEGIN
2371
2372 IF g_debug_switch = 'Y' THEN
2373 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;
2374 fnd_file.put_line(fnd_file.log, l_debug_info);
2375 END IF;
2376 INSERT INTO AP_INVOICE_LINES_INTERFACE
2377 (INVOICE_ID,
2378 APPLICATION_ID,
2379 PRODUCT_TABLE,
2380 REFERENCE_KEY1,
2381 REFERENCE_KEY2,
2382 INVOICE_LINE_ID,
2383 LINE_NUMBER,
2384 LINE_TYPE_LOOKUP_CODE,
2385 LINE_GROUP_NUMBER,
2386 AMOUNT,
2387 ACCOUNTING_DATE,
2388 DESCRIPTION,
2389 AMOUNT_INCLUDES_TAX_FLAG,
2390 TAX_CLASSIFICATION_CODE,
2391 ITEM_DESCRIPTION,
2392 DIST_CODE_COMBINATION_ID,
2393 AWT_GROUP_ID,
2394 LAST_UPDATED_BY,
2395 LAST_UPDATE_DATE,
2396 LAST_UPDATE_LOGIN,
2397 CREATED_BY,
2398 CREATION_DATE,
2399 ATTRIBUTE_CATEGORY,
2400 ATTRIBUTE1,
2401 ATTRIBUTE2,
2402 ATTRIBUTE3,
2403 ATTRIBUTE4,
2404 ATTRIBUTE5,
2405 ATTRIBUTE6,
2406 ATTRIBUTE7,
2407 ATTRIBUTE8,
2408 ATTRIBUTE9,
2409 ATTRIBUTE10,
2410 ATTRIBUTE11,
2411 ATTRIBUTE12,
2412 ATTRIBUTE13,
2413 ATTRIBUTE14,
2414 ATTRIBUTE15,
2415 GLOBAL_ATTRIBUTE_CATEGORY,
2416 GLOBAL_ATTRIBUTE1,
2417 GLOBAL_ATTRIBUTE2,
2418 GLOBAL_ATTRIBUTE3,
2419 GLOBAL_ATTRIBUTE4,
2420 GLOBAL_ATTRIBUTE5,
2421 GLOBAL_ATTRIBUTE6,
2422 GLOBAL_ATTRIBUTE7,
2423 GLOBAL_ATTRIBUTE8,
2424 GLOBAL_ATTRIBUTE9,
2425 GLOBAL_ATTRIBUTE10,
2426 GLOBAL_ATTRIBUTE11,
2427 GLOBAL_ATTRIBUTE12,
2428 GLOBAL_ATTRIBUTE13,
2429 GLOBAL_ATTRIBUTE14,
2430 GLOBAL_ATTRIBUTE15,
2431 GLOBAL_ATTRIBUTE16,
2432 GLOBAL_ATTRIBUTE17,
2433 GLOBAL_ATTRIBUTE18,
2434 GLOBAL_ATTRIBUTE19,
2435 GLOBAL_ATTRIBUTE20,
2436 PROJECT_ID,
2437 TASK_ID,
2438 EXPENDITURE_TYPE,
2439 EXPENDITURE_ITEM_DATE,
2440 EXPENDITURE_ORGANIZATION_ID,
2441 PROJECT_ACCOUNTING_CONTEXT,
2442 PA_ADDITION_FLAG,
2443 PA_QUANTITY,
2444 STAT_AMOUNT,
2445 TYPE_1099,
2446 INCOME_TAX_REGION,
2447 ASSETS_TRACKING_FLAG,
2448 ORG_ID,
2449 REFERENCE_1,
2450 REFERENCE_2,
2451 TAX_RECOVERY_RATE,
2452 TAX_RECOVERY_OVERRIDE_FLAG,
2453 TAX_RECOVERABLE_FLAG,
2454 TAX_CODE_OVERRIDE_FLAG,
2455 TAX_CODE_ID,
2456 CREDIT_CARD_TRX_ID,
2457 AWARD_ID,
2458 TAXABLE_FLAG,
2459 COMPANY_PREPAID_INVOICE_ID,
2460 EXPENSE_GROUP,
2461 JUSTIFICATION,
2462 MERCHANT_DOCUMENT_NUMBER,
2463 MERCHANT_NAME,
2464 MERCHANT_REFERENCE,
2465 MERCHANT_TAXPAYER_ID,
2466 MERCHANT_TAX_REG_NUMBER,
2467 RECEIPT_CONVERSION_RATE,
2468 RECEIPT_CURRENCY_AMOUNT,
2469 RECEIPT_CURRENCY_CODE,
2470 COUNTRY_OF_SUPPLY)
2471 (SELECT
2472 p_invoice_id,
2473 200,
2474 'AP_EXPENSE_REPORT_LINES_ALL',
2475 xl.report_header_id,
2476 xl.report_line_id,
2477 AP_INVOICE_LINES_INTERFACE_S.nextval,
2478 xl.distribution_line_number,
2479 nvl(lc.lookup_code, '') line_type_lookup_code,
2480 '',
2481 to_char(nvl(ap_utilities_pkg.ap_round_currency(xl.amount,
2482 XH.default_currency_code),
2483 0)) distribution_amount,
2484 '' accounting_date,
2485 nvl(xl.item_description, '') item_description,
2486 xl.amount_includes_tax_flag,
2487 nvl(xl.vat_code, '') line_vat_code,
2488 nvl(xl.item_description, '') item_description,
2489 gcc.code_combination_id code_combination_id,
2490 nvl(to_char(xl.awt_group_id), ''),
2491 g_last_updated_by,
2492 sysdate,
2493 g_last_update_login,
2494 xl.created_by,
2495 sysdate,
2496 nvl(decode(p_transfer_flag, 'Y', xl.attribute_category), ''),
2497 nvl(decode(p_transfer_flag, 'Y', xl.attribute1), ''),
2498 nvl(decode(p_transfer_flag, 'Y', xl.attribute2), ''),
2499 nvl(decode(p_transfer_flag, 'Y', xl.attribute3), ''),
2500 nvl(decode(p_transfer_flag, 'Y', xl.attribute4), ''),
2501 nvl(decode(p_transfer_flag, 'Y', xl.attribute5), ''),
2502 nvl(decode(p_transfer_flag, 'Y', xl.attribute6), ''),
2503 nvl(decode(p_transfer_flag, 'Y', xl.attribute7), ''),
2504 nvl(decode(p_transfer_flag, 'Y', xl.attribute8), ''),
2505 nvl(decode(p_transfer_flag, 'Y', xl.attribute9), ''),
2506 nvl(decode(p_transfer_flag, 'Y', xl.attribute10), ''),
2507 nvl(decode(p_transfer_flag, 'Y', xl.attribute11), ''),
2508 nvl(decode(p_transfer_flag, 'Y', xl.attribute12), ''),
2509 nvl(decode(p_transfer_flag, 'Y', xl.attribute13), ''),
2510 nvl(decode(p_transfer_flag, 'Y', xl.attribute14), ''),
2511 nvl(decode(p_transfer_flag, 'Y', xl.attribute15), ''),
2512 xl.global_attribute_category,
2513 xl.global_attribute1,
2514 xl.global_attribute2,
2515 xl.global_attribute3,
2516 xl.global_attribute4,
2517 xl.global_attribute5,
2518 xl.global_attribute6,
2519 xl.global_attribute7,
2520 xl.global_attribute8,
2521 xl.global_attribute9,
2522 xl.global_attribute10,
2523 xl.global_attribute11,
2524 xl.global_attribute12,
2525 xl.global_attribute13,
2526 xl.global_attribute14,
2527 xl.global_attribute15,
2528 xl.global_attribute16,
2529 xl.global_attribute17,
2530 xl.global_attribute18,
2531 xl.global_attribute19,
2532 xl.global_attribute20,
2533 nvl(to_char(xl.project_id), ''),
2534 nvl(to_char(xl.task_id), ''),
2535 nvl(xl.expenditure_type, ''),
2536 nvl(to_char(xl.expenditure_item_date), ''),
2537 nvl(to_char(xl.expenditure_organization_id), ''),
2538 nvl(xl.project_accounting_context, ''),
2539 nvl2(xl.project_id, 'T', 'E') pa_addition_flag,
2540 nvl(to_char(xl.pa_quantity), ''),
2541 to_char(nvl(xl.stat_amount, '')) stat_amount,
2542 '' type_1099,
2543 '' income_tax_region,
2544 DECODE(nvl(gcc.account_type, 'x'), 'A', 'Y', 'N') assets_tracking_flag,
2545 XL.org_id org_id,
2546 nvl(xl.reference_1, ''),
2547 nvl(xl.reference_2, ''),
2548 '' tax_recovery_rate,
2549 'N' tax_recovery_override_flag,
2550 nvl(decode(p_enable_recoverable_flag,
2551 'Y',
2552 decode(xl.line_type_lookup_code, 'TAX', 'Y', 'N'),
2553 'N'),
2554 'N') tax_recoverable_flag,
2555 nvl(xl.tax_code_override_flag, 'N'),
2556 '',
2557 nvl(xl.credit_card_trx_id, ''),
2558 '' award_id,
2559 '',
2560 nvl(xl.company_prepaid_invoice_id, ''),
2561 nvl(xl.expense_group, ''),
2562 nvl(xl.justification, ''),
2563 nvl(xl.merchant_document_number, ''),
2564 nvl(xl.merchant_name, ''),
2565 nvl(xl.merchant_reference, ''),
2566 nvl(xl.merchant_taxpayer_id, ''),
2567 nvl(xl.merchant_tax_reg_number, ''),
2568 to_char(nvl(xl.receipt_conversion_rate, '')),
2569 to_char(nvl(xl.receipt_currency_amount, '')),
2570 nvl(xl.receipt_currency_code, ''),
2571 nvl(xl.country_of_supply, '')
2572 FROM ap_expense_report_lines XL,
2573 gl_code_combinations gcc,
2574 ap_lookup_codes lc,
2575 fnd_currencies F,
2576 ap_expense_report_headers XH
2577 WHERE XL.report_header_id = XH.report_header_id
2578 AND XH.report_header_id = p_report_header_id
2579 AND XL.code_combination_id = gcc.code_combination_id(+)
2580 AND nvl(XL.itemization_parent_id,0) <> -1 /* Itemization Project */
2581 AND lc.lookup_code(+) = XL.line_type_lookup_code
2582 AND lc.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
2583 AND F.currency_code = p_base_currency);
2584
2585
2586 IF g_debug_switch = 'Y' THEN
2587 l_debug_info := 'Done Insert into Invoice Lines Interface';
2588 fnd_file.put_line(fnd_file.log, l_debug_info);
2589 END IF;
2590 END InsertInvoiceLinesInterface;
2591
2592 PROCEDURE PrintVendorInfo(p_vendor_rec IN VendorInfoRecType) IS
2593 l_debug_info VARCHAR2(2000);
2594 BEGIN
2595 IF g_debug_switch = 'Y' THEN
2596 l_debug_info := '>p_vendor_rec.vendor_id := '||p_vendor_rec.vendor_id;
2597 fnd_file.put_line(fnd_file.log, l_debug_info);
2598 l_debug_info := '>p_vendor_rec.vendor_site_id := '||p_vendor_rec.vendor_site_id;
2599 fnd_file.put_line(fnd_file.log, l_debug_info);
2600 l_debug_info := '>p_vendor_rec.home_or_office := '||p_vendor_rec.home_or_office;
2601 fnd_file.put_line(fnd_file.log, l_debug_info);
2602 l_debug_info := '>p_vendor_rec.employee_id := '||p_vendor_rec.employee_id;
2603 fnd_file.put_line(fnd_file.log, l_debug_info);
2604 l_debug_info := '>p_vendor_rec.vendor_name := '||p_vendor_rec.vendor_name;
2605 fnd_file.put_line(fnd_file.log, l_debug_info);
2606 l_debug_info := '>p_vendor_rec.org_id := '||p_vendor_rec.org_id;
2607 fnd_file.put_line(fnd_file.log, l_debug_info);
2608 l_debug_info := '>p_vendor_rec.address_line_1 := '||p_vendor_rec.address_line_1;
2609 fnd_file.put_line(fnd_file.log, l_debug_info);
2610 l_debug_info := '>p_vendor_rec.address_line_2 := '||p_vendor_rec.address_line_2;
2611 fnd_file.put_line(fnd_file.log, l_debug_info);
2612 l_debug_info := '>p_vendor_rec.address_line_3 := '||p_vendor_rec.address_line_3;
2613 fnd_file.put_line(fnd_file.log, l_debug_info);
2614 l_debug_info := '>p_vendor_rec.city := '||p_vendor_rec.city;
2615 fnd_file.put_line(fnd_file.log, l_debug_info);
2616 l_debug_info := '>p_vendor_rec.state := '||p_vendor_rec.state;
2617 fnd_file.put_line(fnd_file.log, l_debug_info);
2618 l_debug_info := '>p_vendor_rec.postal_code := '||p_vendor_rec.postal_code;
2619 fnd_file.put_line(fnd_file.log, l_debug_info);
2620 l_debug_info := '>p_vendor_rec.province := '||p_vendor_rec.province;
2621 fnd_file.put_line(fnd_file.log, l_debug_info);
2622 l_debug_info := '>p_vendor_rec.county := '||p_vendor_rec.county;
2623 fnd_file.put_line(fnd_file.log, l_debug_info);
2624 l_debug_info := '>p_vendor_rec.country := '||p_vendor_rec.country;
2625 fnd_file.put_line(fnd_file.log, l_debug_info);
2626 l_debug_info := '>p_vendor_rec.style := '||p_vendor_rec.style;
2627 fnd_file.put_line(fnd_file.log, l_debug_info);
2628
2629 l_debug_info := '>p_vendor_rec.pay_group := '||p_vendor_rec.pay_group;
2630 fnd_file.put_line(fnd_file.log, l_debug_info);
2631 l_debug_info := '>p_vendor_rec.terms_date_basis := '||p_vendor_rec.terms_date_basis;
2632 fnd_file.put_line(fnd_file.log, l_debug_info);
2633 l_debug_info := '>p_vendor_rec.liab_acc := '||p_vendor_rec.liab_acc;
2634 fnd_file.put_line(fnd_file.log, l_debug_info);
2635 l_debug_info := '>p_vendor_rec.terms_id := '||p_vendor_rec.terms_id;
2636 fnd_file.put_line(fnd_file.log, l_debug_info);
2637 l_debug_info := '>p_vendor_rec.payment_priority := '||p_vendor_rec.payment_priority;
2638 fnd_file.put_line(fnd_file.log, l_debug_info);
2639 l_debug_info := '>p_vendor_rec.prepay_ccid := '||p_vendor_rec.prepay_ccid;
2640 fnd_file.put_line(fnd_file.log, l_debug_info);
2641 l_debug_info := '>p_vendor_rec.always_take_disc_flag := '||p_vendor_rec.always_take_disc_flag;
2642 fnd_file.put_line(fnd_file.log, l_debug_info);
2643 l_debug_info := '>p_vendor_rec.pay_date_basis := '||p_vendor_rec.pay_date_basis;
2644 fnd_file.put_line(fnd_file.log, l_debug_info);
2645 l_debug_info := '>p_vendor_rec.vendor_num := '||p_vendor_rec.vendor_num;
2646 fnd_file.put_line(fnd_file.log, l_debug_info);
2647 l_debug_info := '>p_vendor_rec.allow_awt_flag := '||p_vendor_rec.allow_awt_flag;
2648 fnd_file.put_line(fnd_file.log, l_debug_info);
2649 l_debug_info := '>p_vendor_rec.party_id := '||p_vendor_rec.party_id;
2650 fnd_file.put_line(fnd_file.log, l_debug_info);
2651 END IF;
2652 END PrintVendorInfo;
2653
2654 PROCEDURE PrintVendorSiteInfo(p_vendor_site_rec IN AP_VENDOR_PUB_PKG.r_vendor_site_rec_type) IS
2655 l_debug_info VARCHAR2(2000);
2656 BEGIN
2657 IF g_debug_switch = 'Y' THEN
2658 l_debug_info := '>p_vendor_site_rec.vendor_id := '||p_vendor_site_rec.vendor_id;
2659 fnd_file.put_line(fnd_file.log, l_debug_info);
2660 l_debug_info := '>p_vendor_site_rec.org_id := '||p_vendor_site_rec.org_id;
2661 fnd_file.put_line(fnd_file.log, l_debug_info);
2662 l_debug_info := '>p_vendor_site_rec.org_name := '||p_vendor_site_rec.org_name;
2663 fnd_file.put_line(fnd_file.log, l_debug_info);
2664 l_debug_info := '>p_vendor_site_rec.vendor_site_code := '||p_vendor_site_rec.vendor_site_code;
2665 fnd_file.put_line(fnd_file.log, l_debug_info);
2666 l_debug_info := '>p_vendor_site_rec.pay_site_flag := '||p_vendor_site_rec.pay_site_flag;
2667 fnd_file.put_line(fnd_file.log, l_debug_info);
2668 l_debug_info := '>p_vendor_site_rec.address_line1 := '||p_vendor_site_rec.address_line1;
2669 fnd_file.put_line(fnd_file.log, l_debug_info);
2670 l_debug_info := '>p_vendor_site_rec.address_line2 := '||p_vendor_site_rec.address_line2;
2671 fnd_file.put_line(fnd_file.log, l_debug_info);
2672 l_debug_info := '>p_vendor_site_rec.address_line3 := '||p_vendor_site_rec.address_line3;
2673 fnd_file.put_line(fnd_file.log, l_debug_info);
2674 l_debug_info := '>p_vendor_site_rec.city := '||p_vendor_site_rec.city;
2675 fnd_file.put_line(fnd_file.log, l_debug_info);
2676 l_debug_info := '>p_vendor_site_rec.state := '||p_vendor_site_rec.state;
2677 fnd_file.put_line(fnd_file.log, l_debug_info);
2678 l_debug_info := '>p_vendor_site_rec.zip := '||p_vendor_site_rec.zip;
2679 fnd_file.put_line(fnd_file.log, l_debug_info);
2680 l_debug_info := '>p_vendor_site_rec.province := '||p_vendor_site_rec.province;
2681 fnd_file.put_line(fnd_file.log, l_debug_info);
2682 l_debug_info := '>p_vendor_site_rec.county := '||p_vendor_site_rec.county;
2683 fnd_file.put_line(fnd_file.log, l_debug_info);
2684 l_debug_info := '>p_vendor_site_rec.country := '||p_vendor_site_rec.country;
2685 fnd_file.put_line(fnd_file.log, l_debug_info);
2686 l_debug_info := '>p_vendor_site_rec.address_style := '||p_vendor_site_rec.address_style;
2687 fnd_file.put_line(fnd_file.log, l_debug_info);
2688 END IF;
2689 END PrintVendorSiteInfo;
2690
2691 ------------------------------------------------------------------------
2692 FUNCTION GetVendorInfo(p_vendor_rec IN OUT NOCOPY VendorInfoRecType,
2693 p_reject_code OUT NOCOPY VARCHAR2)
2694 RETURN BOOLEAN IS
2695 ------------------------------------------------------------------------
2696 CURSOR c_supplier_numbering_method IS
2697 SELECT supplier_numbering_method
2698 FROM ap_product_setup
2699 WHERE rownum = 1;
2700
2701 CURSOR c_vendor_site(l_vendor_id IN NUMBER, home_or_office IN VARCHAR2, l_org_id IN NUMBER ) IS
2702 SELECT vendor_site_id,
2703 nvl(pay_group_lookup_code, ''),
2704 nvl(terms_date_basis, ''),
2705 nvl(accts_pay_code_combination_id, -1),
2706 nvl(terms_id, -1),
2707 allow_awt_flag,
2708 party_site_id
2709 FROM ap_supplier_sites s, fnd_lookup_values l
2710 WHERE s.vendor_site_code || '' = SUBSTRB(UPPER(l.meaning), 1, 15)
2711 AND s.vendor_id = l_vendor_id
2712 AND l.lookup_type = 'HOME_OFFICE'
2713 AND l.lookup_code = home_or_office
2714 AND s.org_id = l_org_id;
2715
2716 CURSOR c_vendor_info(l_vendor_id IN NUMBER) IS
2717 SELECT nvl(terms_date_basis, ''),
2718 nvl(terms_id, -1),
2719 nvl(pay_group_lookup_code, ''),
2720 nvl(payment_priority, -1),
2721 nvl(accts_pay_code_combination_id, -1),
2722 nvl(prepay_code_combination_id, -1),
2723 nvl(always_take_disc_flag, 'N'),
2724 nvl(pay_date_basis_lookup_code, ''),
2725 vendor_name,
2726 segment1,
2727 party_id
2728 FROM ap_suppliers
2729 WHERE vendor_id = l_vendor_id;
2730
2731 CURSOR c_vendor_site_known(l_vendor_site_id IN NUMBER) IS
2732 SELECT nvl(pay_group_lookup_code, ''),
2733 nvl(terms_date_basis, ''),
2734 nvl(accts_pay_code_combination_id, -1),
2735 nvl(terms_id, -1),
2736 allow_awt_flag,
2737 party_site_id
2738 FROM ap_supplier_sites s
2739 WHERE vendor_site_id = l_vendor_site_id;
2740
2741 CURSOR c_party_id(l_employee_id IN NUMBER) IS
2742 SELECT party_id
2743 FROM per_employees_x
2744 WHERE employee_id = l_employee_id
2745 AND rownum = 1;
2746
2747 --Bug#7012808 - Payment Priority Defaulted to 99
2748 CURSOR c_create_supplier(l_org_id IN NUMBER) IS
2749 SELECT create_employee_vendor_flag, base_currency_code, employee_payment_priority
2750 FROM ap_system_parameters_all
2751 WHERE org_id = l_org_id;
2752
2753
2754 l_vendor_id NUMBER;
2755 l_vendor_site_id NUMBER;
2756 l_party_id NUMBER;
2757 l_party_site_id NUMBER;
2758 l_location_id NUMBER;
2759 l_terms_date_basis ap_suppliers.terms_date_basis%TYPE;
2760 l_terms_id ap_suppliers.terms_id%TYPE;
2761 l_pay_group ap_suppliers.pay_group_lookup_code%TYPE;
2762 l_payment_priority ap_suppliers.payment_priority%TYPE;
2763 l_liab_acc ap_suppliers.accts_pay_code_combination_id%TYPE;
2764 l_prepay_ccid ap_suppliers.prepay_code_combination_id%TYPE;
2765 l_always_take_disc_flag ap_suppliers.always_take_disc_flag%TYPE;
2766 l_pay_date_basis ap_suppliers.pay_date_basis_lookup_code%TYPE;
2767 l_vendor_name ap_suppliers.vendor_name%TYPE;
2768 l_vendor_num ap_suppliers.segment1%TYPE;
2769
2770 l_create_vendor BOOLEAN := FALSE;
2771
2772 l_site_pay_group ap_supplier_sites.pay_group_lookup_code%TYPE;
2773 l_site_terms_date_basis ap_supplier_sites.terms_date_basis%TYPE;
2774 l_site_liab_acc ap_supplier_sites.accts_pay_code_combination_id%TYPE;
2775 l_site_terms_id ap_supplier_sites.terms_id%TYPE;
2776 l_site_allow_awt_flag ap_supplier_sites.allow_awt_flag%TYPE;
2777 l_debug_info VARCHAR2(2000);
2778 l_duplicate_vendor VARCHAR2(2);
2779 l_val_return_status VARCHAR2(50);
2780 l_val_msg_count NUMBER;
2781 l_val_msg_data VARCHAR2(1000);
2782 l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
2783 l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
2784 l_create_vendor_flag ap_system_parameters_all.create_employee_vendor_flag%TYPE;
2785
2786 l_supplier_numbering_method ap_product_setup.supplier_numbering_method%TYPE;
2787 l_base_currency_code ap_system_parameters.base_currency_code%TYPE;
2788
2789 BEGIN
2790
2791 PrintVendorInfo(p_vendor_rec);
2792
2793 IF (nvl(p_vendor_rec.vendor_id, -1) = -1) THEN
2794
2795 --------------------------------------------------------------
2796 l_debug_info := 'Get Vendor Info when vendor id is not known';
2797 --------------------------------------------------------------
2798 IF g_debug_switch = 'Y' THEN
2799 fnd_file.put_line(fnd_file.log, l_debug_info);
2800 END IF;
2801
2802 BEGIN
2803 SELECT vendor_id,
2804 DECODE(employee_id,
2805 NULL,
2806 DECODE(nvl(vendor_type_lookup_code, 'EMPLOYEE'),
2807 'EMPLOYEE',
2808 'N',
2809 'Y'),
2810 p_vendor_rec.employee_id,
2811 'N',
2812 'Y'),
2813 party_id
2814 INTO l_vendor_id,
2815 l_duplicate_vendor,
2816 l_party_id
2817 FROM ap_suppliers
2818 WHERE employee_id = p_vendor_rec.employee_id;
2819 EXCEPTION
2820 WHEN NO_DATA_FOUND THEN
2821 fnd_file.put_line(fnd_file.log, 'Employee Id '||p_vendor_rec.employee_id||' is not found');
2822 l_vendor_id := -1;
2823 WHEN TOO_MANY_ROWS THEN
2824 p_reject_code := 'Create duplicate vendor';
2825 RETURN (FALSE);
2826 END;
2827
2828 IF g_debug_switch = 'Y' THEN
2829 l_debug_info := 'l_vendor_id := '||l_vendor_id;
2830 fnd_file.put_line(fnd_file.log, l_debug_info);
2831 l_debug_info := 'l_duplicate_vendor := '||l_duplicate_vendor;
2832 fnd_file.put_line(fnd_file.log, l_debug_info);
2833 l_debug_info := 'l_party_id := '||l_party_id;
2834 fnd_file.put_line(fnd_file.log, l_debug_info);
2835 END IF;
2836
2837 IF l_duplicate_vendor = 'Y' THEN
2838 p_reject_code := 'Create duplicate vendor';
2839 RETURN (FALSE);
2840 END IF;
2841
2842 END IF; /* (nvl(p_vendor_rec.vendor_id, -1) = -1) */
2843
2844
2845 IF (l_vendor_id is not null) then
2846 p_vendor_rec.vendor_id := l_vendor_id;
2847 ELSE
2848 l_vendor_id := p_vendor_rec.vendor_id;
2849 END IF;
2850
2851 l_create_vendor_flag := NULL; -- Bug: 7004219, Create Employee as supplier ignored
2852 l_payment_priority := NULL; --Bug#7012808 - Payment Priority Defaulted to 99
2853 OPEN c_create_supplier(p_vendor_rec.org_id);
2854 FETCH c_create_supplier INTO l_create_vendor_flag, l_base_currency_code, l_payment_priority;
2855 CLOSE c_create_supplier;
2856
2857
2858 IF (nvl(p_vendor_rec.vendor_id, -1) <> -1) THEN
2859 ----------------------------------------------------------
2860 l_debug_info := 'Get Vendor Info when vendor id is known';
2861 ----------------------------------------------------------
2862 IF g_debug_switch = 'Y' THEN
2863 fnd_file.put_line(fnd_file.log, l_debug_info);
2864 END IF;
2865
2866 l_payment_priority := NULL;
2867
2868 -- call AP_VENDOR_PUB_PKG.Validate_Vendor
2869 OPEN c_vendor_info(p_vendor_rec.vendor_id);
2870
2871 FETCH c_vendor_info
2872 INTO l_terms_date_basis,
2873 l_terms_id,
2874 l_pay_group,
2875 l_payment_priority,
2876 l_liab_acc,
2877 l_prepay_ccid,
2878 l_always_take_disc_flag,
2879 l_pay_date_basis,
2880 l_vendor_name,
2881 l_vendor_num,
2882 l_party_id ;
2883
2884 CLOSE c_vendor_info;
2885
2886 ELSE
2887 ------------------------------------
2888 l_debug_info := 'Get party_id';
2889 ------------------------------------
2890 IF g_debug_switch = 'Y' THEN
2891 fnd_file.put_line(fnd_file.log, l_debug_info);
2892 END IF;
2893
2894 OPEN c_party_id(p_vendor_rec.employee_id);
2895 FETCH c_party_id INTO l_vendor_rec.party_id;
2896 CLOSE c_party_id;
2897
2898 ------------------------------------
2899 l_debug_info := 'l_vendor_rec.party_id = '||l_vendor_rec.party_id;
2900 ------------------------------------
2901 IF g_debug_switch = 'Y' THEN
2902 fnd_file.put_line(fnd_file.log, l_debug_info);
2903 END IF;
2904 if nvl(l_vendor_rec.party_id, -1) = -1 then
2905 p_reject_code := 'INVALID PARTY';
2906 RETURN (FALSE);
2907 end if;
2908
2909 ------------------------------------
2910 l_debug_info := 'Checking Automatic Create Employee as Supplier option';
2911 ------------------------------------
2912 IF g_debug_switch = 'Y' THEN
2913 fnd_file.put_line(fnd_file.log, l_debug_info);
2914 END IF;
2915
2916
2917 if(nvl(l_create_vendor_flag, 'N') <> 'Y') then
2918
2919 ------------------------------------
2920 l_debug_info := 'Automatic Create Employee as Supplier is not checked in Payable Options';
2921 ------------------------------------
2922 IF g_debug_switch = 'Y' THEN
2923 fnd_file.put_line(fnd_file.log, l_debug_info);
2924 END IF;
2925
2926 p_reject_code := 'Not a vendor';
2927 RETURN (FALSE);
2928 end if;
2929
2930
2931 ------------------------------------
2932 l_debug_info := 'Checking ap_product_setup.supplier_numbering_method';
2933 ------------------------------------
2934 IF g_debug_switch = 'Y' THEN
2935 fnd_file.put_line(fnd_file.log, l_debug_info);
2936 END IF;
2937
2938 OPEN c_supplier_numbering_method;
2939 FETCH c_supplier_numbering_method into l_supplier_numbering_method;
2940 CLOSE c_supplier_numbering_method;
2941
2942 if (l_supplier_numbering_method <> 'AUTOMATIC') then
2943
2944 ------------------------------------
2945 l_debug_info := 'ap_product_setup.supplier_numbering_method is not AUTOMATIC';
2946 ------------------------------------
2947 IF g_debug_switch = 'Y' THEN
2948 fnd_file.put_line(fnd_file.log, l_debug_info);
2949 END IF;
2950
2951 p_reject_code := 'Create as vendor';
2952 RETURN (FALSE);
2953 end if;
2954
2955 ------------------------------------
2956 l_debug_info := 'Creating a vendor (AP_VENDOR_PUB_PKG.create_vendor())';
2957 ------------------------------------
2958 IF g_debug_switch = 'Y' THEN
2959 fnd_file.put_line(fnd_file.log, l_debug_info);
2960 END IF;
2961
2962 l_create_vendor := TRUE;
2963 l_pay_group := p_vendor_rec.pay_group;
2964 l_terms_id := p_vendor_rec.terms_id;
2965 l_vendor_rec.pay_group_lookup_code := p_vendor_rec.pay_group;
2966 l_vendor_rec.terms_id := p_vendor_rec.terms_id;
2967 l_vendor_rec.vendor_name := p_vendor_rec.vendor_name;
2968 l_vendor_rec.employee_id := p_vendor_rec.employee_id;
2969 l_vendor_rec.vendor_type_lookup_code := 'EMPLOYEE';
2970 --bug 6795742
2971 l_vendor_rec.invoice_currency_code := l_base_currency_code;
2972 l_vendor_rec.payment_currency_code := l_base_currency_code;
2973 l_vendor_rec.payment_priority := l_payment_priority;--Bug#7012808 - Payment Priority Defaulted to 99
2974
2975 AP_VENDOR_PUB_PKG.create_vendor( p_api_version => 1.0,
2976 p_init_msg_list => FND_API.G_FALSE,
2977 p_commit => FND_API.G_FALSE,
2978 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2979 x_return_status => l_val_return_status,
2980 x_msg_count => l_val_msg_count,
2981 x_msg_data => l_val_msg_data,
2982 p_vendor_rec => l_vendor_rec,
2983 x_vendor_id => l_vendor_id,
2984 x_party_id => l_party_id);
2985
2986 if l_party_id is null then
2987 l_party_id := l_vendor_rec.party_id;
2988 end if;
2989
2990 IF g_debug_switch = 'Y' THEN
2991
2992 l_debug_info := 'l_val_return_status := '||l_val_return_status;
2993 fnd_file.put_line(fnd_file.log, l_debug_info);
2994
2995 l_debug_info := 'l_val_msg_count := '||l_val_msg_count;
2996 fnd_file.put_line(fnd_file.log, l_debug_info);
2997
2998 if (nvl(l_val_msg_count, 0) > 1) then
2999 for i in 1..l_val_msg_count
3000 loop
3001 l_debug_info := 'l_val_msg_data('||i||') := '||substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
3002 fnd_file.put_line(fnd_file.log, l_debug_info);
3003 end loop;
3004 else
3005 l_debug_info := 'l_val_msg_data := '||l_val_msg_data;
3006 fnd_file.put_line(fnd_file.log, l_debug_info);
3007 end if;
3008
3009 l_debug_info := 'l_vendor_id := '||l_vendor_id;
3010 fnd_file.put_line(fnd_file.log, l_debug_info);
3011 l_debug_info := 'l_party_id := '||l_party_id;
3012 fnd_file.put_line(fnd_file.log, l_debug_info);
3013
3014 END IF;
3015
3016 IF l_val_return_status = FND_API.G_RET_STS_SUCCESS THEN
3017 p_vendor_rec.vendor_id := l_vendor_id;
3018 ELSE
3019
3020 if (nvl(l_val_msg_count, 0) > 1) then
3021 for i in 1..l_val_msg_count
3022 loop
3023 p_reject_code := substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
3024 if (p_reject_code is not null) then
3025 exit;
3026 end if;
3027 end loop;
3028 else
3029 p_reject_code := substrb(l_val_msg_data, 1, 30);
3030 end if;
3031
3032 RETURN (FALSE);
3033 END IF;
3034
3035 END IF; /* (nvl(p_vendor_rec.vendor_id, -1) <> -1) */
3036
3037
3038 IF (nvl(p_vendor_rec.vendor_site_id, -1) <> -1) THEN
3039 --------------------------------------------------------------------
3040 l_debug_info := 'Get Vendor Site Info when vendor site id is known';
3041 --------------------------------------------------------------------
3042 IF g_debug_switch = 'Y' THEN
3043 fnd_file.put_line(fnd_file.log, l_debug_info);
3044 END IF;
3045
3046
3047 OPEN c_vendor_site_known(p_vendor_rec.vendor_site_id);
3048
3049 FETCH c_vendor_site_known
3050 INTO l_site_pay_group,
3051 l_site_terms_date_basis,
3052 l_site_liab_acc,
3053 l_site_terms_id,
3054 l_site_allow_awt_flag,
3055 l_party_site_id;
3056
3057 CLOSE c_vendor_site_known;
3058
3059 ELSE
3060 -------------------------------------------------------------
3061 l_debug_info := 'Get Vendor Site Info for Employee when vendor site id is not known';
3062 -------------------------------------------------------------
3063 IF g_debug_switch = 'Y' THEN
3064 fnd_file.put_line(fnd_file.log, l_debug_info);
3065 END IF;
3066
3067 OPEN c_vendor_site(p_vendor_rec.vendor_id, p_vendor_rec.home_or_office, p_vendor_rec.org_id);
3068
3069 FETCH c_vendor_site
3070 INTO p_vendor_rec.vendor_site_id,
3071 l_site_pay_group,
3072 l_site_terms_date_basis,
3073 l_site_liab_acc,
3074 l_site_terms_id,
3075 l_site_allow_awt_flag,
3076 l_party_site_id;
3077
3078 CLOSE c_vendor_site;
3079
3080 END IF; /* (nvl(p_vendor_rec.vendor_site_id, -1) <> -1) */
3081
3082
3083 IF (nvl(p_vendor_rec.vendor_site_id, -1) = -1) THEN
3084 -----------------------------------------
3085 l_debug_info := 'Creating a vendor site (AP_VENDOR_PUB_PKG.create_vendor_site())';
3086 -----------------------------------------
3087 IF g_debug_switch = 'Y' THEN
3088 fnd_file.put_line(fnd_file.log, l_debug_info);
3089 END IF;
3090
3091 l_vendor_site_rec.vendor_id := p_vendor_rec.vendor_id;
3092 l_vendor_site_rec.org_id := p_vendor_rec.org_id;
3093 l_vendor_site_rec.pay_group_lookup_code := p_vendor_rec.pay_group;
3094 l_vendor_site_rec.terms_id := p_vendor_rec.terms_id;
3095 l_vendor_site_rec.party_site_id := l_party_site_id;
3096
3097 if (p_vendor_rec.home_or_office = 'O') then
3098 l_vendor_site_rec.vendor_site_code := 'OFFICE';
3099 elsif (p_vendor_rec.home_or_office = 'H') then
3100 l_vendor_site_rec.vendor_site_code := 'HOME';
3101 elsif (p_vendor_rec.home_or_office = 'P') then
3102 l_vendor_site_rec.vendor_site_code := 'PROVISIONAL';--Bug#7207375 - Allow payment of Expense Report to Temporary Address
3103 else
3104 p_reject_code := 'Invalid vendor site';
3105 RETURN (FALSE);
3106 end if;
3107 l_vendor_site_rec.pay_site_flag := 'Y';
3108 --bug 6795742
3109 l_vendor_site_rec.invoice_currency_code := l_base_currency_code;
3110 l_vendor_site_rec.payment_currency_code := l_base_currency_code;
3111 l_vendor_site_rec.payment_priority := l_payment_priority; --Bug#7012808 - Payment Priority Defaulted to 99
3112 -- bug 5350423 - supplier creation should not pass address info
3113 --l_vendor_site_rec.address_line1 := p_vendor_rec.address_line_1;
3114 --l_vendor_site_rec.address_line2 := p_vendor_rec.address_line_2;
3115 --l_vendor_site_rec.address_line3 := p_vendor_rec.address_line_3;
3116 --l_vendor_site_rec.city := p_vendor_rec.city;
3117 --l_vendor_site_rec.state := p_vendor_rec.state;
3118 --l_vendor_site_rec.zip := p_vendor_rec.postal_code;
3119 --l_vendor_site_rec.province := p_vendor_rec.province;
3120 --l_vendor_site_rec.county := p_vendor_rec.county;
3121 --l_vendor_site_rec.country := p_vendor_rec.country;
3122 --l_vendor_site_rec.address_style := p_vendor_rec.style;
3123
3124 PrintVendorSiteInfo(l_vendor_site_rec);
3125
3126 AP_VENDOR_PUB_PKG.create_vendor_site (
3127 p_api_version => 1.0,
3128 p_init_msg_list => FND_API.G_FALSE,
3129 p_commit => FND_API.G_FALSE,
3130 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3131 x_return_status => l_val_return_status,
3132 x_msg_count => l_val_msg_count,
3133 x_msg_data => l_val_msg_data,
3134 p_vendor_site_rec => l_vendor_site_rec,
3135 x_vendor_site_id => l_vendor_site_id,
3136 x_party_site_id => l_party_site_id,
3137 x_location_id => l_location_id);
3138 --Bug#7207375 - party_site_id is initialized later.
3139 /*IF l_party_site_id is null THEN
3140 l_party_site_id := l_vendor_site_rec.party_site_id;
3141 END IF;*/
3142
3143 IF g_debug_switch = 'Y' THEN
3144
3145 l_debug_info := 'l_val_return_status := '||l_val_return_status;
3146 fnd_file.put_line(fnd_file.log, l_debug_info);
3147
3148 l_debug_info := 'l_val_msg_count := '||l_val_msg_count;
3149 fnd_file.put_line(fnd_file.log, l_debug_info);
3150
3151 if (nvl(l_val_msg_count, 0) > 1) then
3152 for i in 1..l_val_msg_count
3153 loop
3154 l_debug_info := 'l_val_msg_data('||i||') := '||substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
3155 fnd_file.put_line(fnd_file.log, l_debug_info);
3156 end loop;
3157 else
3158 l_debug_info := 'l_val_msg_data := '||l_val_msg_data;
3159 fnd_file.put_line(fnd_file.log, l_debug_info);
3160 end if;
3161
3162 l_debug_info := 'l_vendor_site_id := '||l_vendor_site_id;
3163 fnd_file.put_line(fnd_file.log, l_debug_info);
3164 l_debug_info := 'l_party_site_id := '||l_party_site_id;
3165 fnd_file.put_line(fnd_file.log, l_debug_info);
3166 l_debug_info := 'l_location_id := '||l_location_id;
3167 fnd_file.put_line(fnd_file.log, l_debug_info);
3168 END IF;
3169
3170
3171 IF l_val_return_status = FND_API.G_RET_STS_SUCCESS THEN
3172 p_vendor_rec.vendor_site_id := l_vendor_site_id;
3173
3174 --Bug#7207375 - Initialize vendor details from site.
3175 OPEN c_vendor_site_known(p_vendor_rec.vendor_site_id);
3176
3177 FETCH c_vendor_site_known
3178 INTO l_site_pay_group,
3179 l_site_terms_date_basis,
3180 l_site_liab_acc,
3181 l_site_terms_id,
3182 l_site_allow_awt_flag,
3183 l_party_site_id;
3184 CLOSE c_vendor_site_known;
3185
3186 ELSE
3187
3188 if (nvl(l_val_msg_count, 0) > 1) then
3189 for i in 1..l_val_msg_count
3190 loop
3191 p_reject_code := substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
3192 if (p_reject_code is not null) then
3193 exit;
3194 end if;
3195 end loop;
3196 else
3197 p_reject_code := substrb(l_val_msg_data, 1, 30);
3198 end if;
3199
3200 RETURN (FALSE);
3201 END IF;
3202
3203 END IF; /* (nvl(p_vendor_rec.vendor_site_id, -1) = -1) */
3204
3205
3206 --------------------------------------------------------------------------------
3207 l_debug_info := 'Vendor Site ID got is '|| to_char(p_vendor_rec.vendor_site_id);
3208 --------------------------------------------------------------------------------
3209 IF g_debug_switch = 'Y' THEN
3210 fnd_file.put_line(fnd_file.log, l_debug_info);
3211 END IF;
3212
3213 if (not l_create_vendor) then
3214 l_vendor_site_rec.vendor_id := p_vendor_rec.vendor_id;
3215 l_vendor_site_rec.org_id := p_vendor_rec.org_id;
3216 if (p_vendor_rec.home_or_office = 'O') then
3217 l_vendor_site_rec.vendor_site_code := 'OFFICE';
3218 elsif (p_vendor_rec.home_or_office = 'H') then
3219 l_vendor_site_rec.vendor_site_code := 'HOME';
3220 elsif (p_vendor_rec.home_or_office = 'P') then
3221 l_vendor_site_rec.vendor_site_code := 'PROVISIONAL';--Bug#7207375 - Allow payment of Expense Report to Temporary Address
3222 end if;
3223 l_vendor_site_rec.pay_site_flag := 'Y';
3224 -- bug 5350423 - supplier creation should not pass address info
3225 --l_vendor_site_rec.address_line1 := p_vendor_rec.address_line_1;
3226 --l_vendor_site_rec.address_line2 := p_vendor_rec.address_line_2;
3227 --l_vendor_site_rec.address_line3 := p_vendor_rec.address_line_3;
3228 --l_vendor_site_rec.city := p_vendor_rec.city;
3229 --l_vendor_site_rec.state := p_vendor_rec.state;
3230 --l_vendor_site_rec.zip := p_vendor_rec.postal_code;
3231 --l_vendor_site_rec.province := p_vendor_rec.province;
3232 --l_vendor_site_rec.county := p_vendor_rec.county;
3233 --l_vendor_site_rec.country := p_vendor_rec.country;
3234 --l_vendor_site_rec.address_style := p_vendor_rec.style;
3235
3236 -------------------------------------
3237 l_debug_info := 'Update Vendor Site';
3238 -------------------------------------
3239 IF g_debug_switch = 'Y' THEN
3240 fnd_file.put_line(fnd_file.log, l_debug_info);
3241 END IF;
3242
3243 PrintVendorSiteInfo(l_vendor_site_rec);
3244
3245 /*
3246 AP_VENDOR_PUB_PKG.update_vendor_site(
3247 p_api_version => 1.0,
3248 p_init_msg_list => FND_API.G_FALSE,
3249 p_commit => FND_API.G_FALSE,
3250 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3251 x_return_status => l_val_return_status,
3252 x_msg_count => l_val_msg_count,
3253 x_msg_data => l_val_msg_data,
3254 p_vendor_site_rec => l_vendor_site_rec,
3255 p_vendor_site_id => l_vendor_site_id);
3256
3257 IF g_debug_switch = 'Y' THEN
3258
3259 l_debug_info := 'l_val_return_status := '||l_val_return_status;
3260 fnd_file.put_line(fnd_file.log, l_debug_info);
3261
3262 l_debug_info := 'l_val_msg_count := '||l_val_msg_count;
3263 fnd_file.put_line(fnd_file.log, l_debug_info);
3264
3265 if (nvl(l_val_msg_count, 0) > 1) then
3266 for i in 1..l_val_msg_count
3267 loop
3268 l_debug_info := 'l_val_msg_data('||i||') := '||substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
3269 fnd_file.put_line(fnd_file.log, l_debug_info);
3270 end loop;
3271 else
3272 l_debug_info := 'l_val_msg_data := '||l_val_msg_data;
3273 fnd_file.put_line(fnd_file.log, l_debug_info);
3274 end if;
3275
3276 PrintVendorSiteInfo(l_vendor_site_rec);
3277
3278 l_debug_info := 'l_vendor_site_id := '||l_vendor_site_id;
3279 fnd_file.put_line(fnd_file.log, l_debug_info);
3280 END IF;
3281
3282 IF l_val_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3283
3284 if (nvl(l_val_msg_count, 0) > 1) then
3285 for i in 1..l_val_msg_count
3286 loop
3287 p_reject_code := substrb(substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255), 1, 30);
3288 end loop;
3289 else
3290 p_reject_code := substrb(l_val_msg_data, 1, 30);
3291 end if;
3292
3293 RETURN (FALSE);
3294 END IF;
3295 */
3296
3297 end if; /* (not l_create_vendor) */
3298
3299 p_vendor_rec.pay_group := nvl(l_site_pay_group,
3300 l_pay_group);
3301 p_vendor_rec.terms_date_basis := nvl(l_site_terms_date_basis,
3302 l_terms_date_basis);
3303 p_vendor_rec.liab_acc := nvl(l_site_liab_acc, l_liab_acc);
3304 p_vendor_rec.terms_id := nvl(l_site_terms_id, l_terms_id);
3305 p_vendor_rec.payment_priority := l_payment_priority;
3306 p_vendor_rec.prepay_ccid := l_prepay_ccid;
3307 p_vendor_rec.always_take_disc_flag := l_always_take_disc_flag;
3308 p_vendor_rec.pay_date_basis := l_pay_date_basis;
3309 p_vendor_rec.vendor_name := l_vendor_name;
3310 p_vendor_rec.vendor_num := l_vendor_num;
3311 p_vendor_rec.allow_awt_flag := l_site_allow_awt_flag;
3312 p_vendor_rec.party_id := l_party_id;
3313 p_vendor_rec.party_site_id := l_party_site_id;
3314
3315 PrintVendorInfo(p_vendor_rec);
3316
3317 RETURN (TRUE);
3318
3319 END GetVendorInfo;
3320
3321 ------------------------------------------------------------------------
3322 FUNCTION CreatePayee(p_party_id IN ap_suppliers.party_id%TYPE,
3323 p_org_id IN ap_expense_report_headers.org_id%TYPE,
3324 p_reject_code OUT NOCOPY VARCHAR2)
3325 RETURN BOOLEAN IS
3326 ------------------------------------------------------------------------
3327 l_debug_info VARCHAR2(2000);
3328 l_payee_exists VARCHAR2(2);
3329 l_return_status VARCHAR2(1);
3330 l_msg_count NUMBER;
3331 l_msg_data VARCHAR2(32767);
3332 l_External_Payee_Tab IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Tab_Type;
3333 l_Ext_Payee_ID_Tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Tab_Type;
3334 l_Ext_Payee_Create_Tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Create_Tab_Type;
3335
3336
3337 BEGIN
3338
3339 BEGIN
3340 select 'Y'
3341 into l_payee_exists
3342 from IBY_EXTERNAL_PAYEES_ALL
3343 where PAYEE_PARTY_ID = p_party_id
3344 and org_id = p_org_id
3345 and rownum =1;
3346
3347 EXCEPTION
3348 WHEN NO_DATA_FOUND THEN
3349 ------------------------------------------------------------------
3350 l_debug_info := 'Creating a Payee by calling Oracle Payments API';
3351 ------------------------------------------------------------------
3352 IF g_debug_switch = 'Y' THEN
3353 fnd_file.put_line(fnd_file.log, l_debug_info);
3354 END IF;
3355
3356 l_External_Payee_Tab(0).Payee_Party_Id := p_party_id;
3357 l_External_Payee_Tab(0).Payer_Org_Id := p_org_id;
3358 l_External_Payee_Tab(0).Payment_Function := 'PAYABLES_DISB';
3359 l_External_Payee_Tab(0).Exclusive_Pay_Flag := 'N';
3360
3361 IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee (
3362 p_api_version => 1.0,
3363 p_init_msg_list => FND_API.G_TRUE,
3364 p_ext_payee_tab => l_External_Payee_Tab,
3365 x_return_status => l_return_status,
3366 x_msg_count => l_msg_count,
3367 x_msg_data => l_msg_data,
3368 x_ext_payee_id_tab => l_Ext_Payee_ID_Tab,
3369 x_ext_payee_status_tab => l_Ext_Payee_Create_Tab);
3370
3371 IF g_debug_switch = 'Y' THEN
3372 fnd_file.put_line(fnd_file.log, 'l_return_status: ' || l_return_status);
3373
3374 fnd_file.put_line(fnd_file.log, 'Payee_Creation_Status: ' ||
3375 l_Ext_Payee_Create_Tab(0).Payee_Creation_Status);
3376 fnd_file.put_line(fnd_file.log, 'Payee_Creation_Msg: ' ||
3377 l_Ext_Payee_Create_Tab(0).Payee_Creation_Msg);
3378 END IF;
3379
3380 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3381 p_reject_code := substrb(l_Ext_Payee_Create_Tab(0).Payee_Creation_Msg, 1, 30);
3382 RETURN (FALSE);
3383 END IF;
3384 END;
3385
3386 RETURN (TRUE);
3387
3388 END CreatePayee;
3389
3390 END AP_WEB_EXPORT_ER;