[Home] [Help]
PACKAGE BODY: APPS.AP_CREDIT_CARD_INVOICE_PKG
Source
1 PACKAGE BODY AP_CREDIT_CARD_INVOICE_PKG AS
2 /* $Header: apwcciib.pls 120.24 2011/07/21 18:02:03 dsadipir ship $ */
3 --------------------------------------------------------------------------------
4 --
5 -- PROCEDURE CreateCreditCardInvoice
6 --
7 -- Params:
8 -- input:
9 -- p_cardProgramID - credit card program code
10 -- p_startDate - date to start collecting transaction records
11 -- p_endDate - date to end collecting transaction records
12 --
13 -- output:
14 -- errbuf - contains error message; required by Concurrent Manager
15 -- retcode - contains return code; required by Concurrent Manager
16 -- p_invoiceID - the invoice id value of the newly generated invoice record
17 --
18 -- Returns:
19 -- none
20 --
21 -- Desc:
22 -- Insert the records into AP_INVOICE_LINES_INTERFACE and AP_INVOICES_INTERFACE
23 -- to create invoice for caredit card isser for payment of records in
24 -- AP_CREDIT_CARD_TRXNS.
25 --
26 -- After being inserted into invoice open interface, the corresponding records in
27 -- the AP_CREDIT_CARD_TRXNS_ALL need to be updated with COMPANY_PREPAID_FLAG set to
28 -- 'Y'
29 --
30 -- Single record is inserted into AP_INVOICES_INTERFACE and serves as a header to
31 -- those records inserted into AP_INVOICE_LINES_INTERFACE.
32 --
33 -- InvoiceId of the newly created Invoice is returned back.
34 ---------------------------------------------------------------------------------
35 PROCEDURE createCreditCardInvoice(
36 errbuf OUT NOCOPY VARCHAR2,
37 retcode OUT NOCOPY NUMBER,
38 p_cardProgramID IN NUMBER,
39 p_startDate IN DATE DEFAULT NULL,
40 p_endDate IN DATE DEFAULT NULL,
41 p_invoiceId OUT NOCOPY NUMBER )
42 IS
43 l_count NUMBER := 0;
44 l_sum AP_WEB_DB_AP_INT_PKG.invIntf_invAmt := 0;
45 l_card_trxn_id AP_WEB_DB_CCARD_PKG.ccTrxn_trxID;
46 l_transaction_date DATE; --3028505, renamed this variable
47 l_invoice_id AP_WEB_DB_AP_INT_PKG.invLines_invID;
48 l_invoice_id_temp AP_WEB_DB_AP_INT_PKG.invLines_invID; -- Bug 6687752
49 l_invoice_line_id AP_WEB_DB_AP_INT_PKG.invLines_invLineID;
50 l_party_id ap_suppliers.party_id%TYPE;
51 l_party_site_id ap_supplier_sites.party_site_id%TYPE;
52 l_vendor_id AP_WEB_DB_CCARD_PKG.cardProgs_vendorID;
53 l_vendor_site_id AP_WEB_DB_CCARD_PKG.cardProgs_vendorSiteID;
54 l_terms_id AP_TERMS.TERM_ID%TYPE; -- Bug: 7234744 populate terms-id in the interface table.
55 l_ccid AP_WEB_DB_AP_INT_PKG.invLines_distCodeCombID;
56 l_invoice_currency_code AP_WEB_DB_AP_INT_PKG.invIntf_invCurrCode;
57 l_pay_group_lookup_code AP_WEB_DB_AP_INT_PKG.vendorSites_payGroupLookupCode;
58 l_accts_pay_ccid AP_WEB_DB_AP_INT_PKG.invIntf_acctsPayCCID;
59
60 l_debugInfo VARCHAR2(2000);
61 l_payment_due_code VARCHAR2(15);
62 l_result BOOLEAN;
63 l_ccard_trxn_cursor AP_WEB_DB_CCARD_PKG.UnpaidCreditCardTrxnCursor;
64 l_billed_amt AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
65 l_billed_curr_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
66 l_masked_cc_number AP_WEB_DB_CCARD_PKG.ccTrxn_cardId;
67
68 --3028505, added variables below
69 l_period_name gl_period_statuses.period_name%TYPE;
70 l_open_date date;
71 l_full_name per_people_f.full_name%TYPE;
72 l_description AP_WEB_DB_AP_INT_PKG.invLines_description;
73 l_employee_id per_employees_x.employee_id%type;
74 --
75 -- 4458253, added variable
76 l_org_id NUMBER;
77
78
79 x_return_status VARCHAR2(4000);
80 x_msg_count NUMBER;
81 x_msg_data VARCHAR2(4000);
82 l_rejection_list AP_IMPORT_INVOICES_PKG.rejection_tab_type;
83
84 l_doc_cat_code AP_WEB_DB_AP_INT_PKG.invIntf_docCategoryCode := 'PAY REQ INV';
85
86 --
87 --
88 BEGIN
89
90 -- Bug 4458253: Should pass org id explicitly into InsertInvoice* calls
91 ------------------------------------------------------------------
92 l_debugInfo := 'Get Org ID from card program to pass into InsertInvoice*';
93 ------------------------------------------------------------------
94 SELECT org_id
95 INTO l_org_id
96 FROM ap_card_programs
97 WHERE card_program_id = p_cardProgramID;
98
99
100 -- Bug 3068119: Transactions with Payment Scenario COMPANY will be processed.
101 -------------------------------------------------------------------
102 l_debugInfo := 'Set the payment due code';
103 -------------------------------------------------------------------
104 l_payment_due_code := 'COMPANY';
105
106 -------------------------------------------------------------------
107 l_debugInfo := 'Loop through each of every transaction';
108 -------------------------------------------------------------------
109 IF ( AP_WEB_DB_CCARD_PKG.GetUnpaidCreditCardTrxnCursor(
110 l_ccard_trxn_cursor,
111 p_cardProgramID,
112 l_payment_due_code,
113 p_startDate,
114 p_endDate ) ) THEN
115
116 LOOP
117 FETCH l_ccard_trxn_cursor INTO
118 l_card_trxn_id,
119 l_transaction_date,
120 l_billed_amt,
121 l_masked_cc_number,
122 l_full_name,
123 l_employee_id;
124
125 EXIT WHEN l_ccard_trxn_cursor%NOTFOUND;
126
127 --3028505, added code below to check if the transaction date is in an open period
128 --if not, then increment it to an open period.
129
130 l_period_name := ap_utilities_pkg.get_current_gl_date(l_transaction_date);
131
132 if l_period_name is null then
133 ap_utilities_pkg.get_only_open_gl_date(l_transaction_date,
134 l_period_name,
135 l_open_date);
136
137 if l_open_date is not null then
138 l_transaction_date := l_open_date;
139 end if;
140
141 end if;
142
143 -------------------------------------------------------------------
144 l_debugInfo := 'Get the clearing account id';
145 -------------------------------------------------------------------
146 -- Bug 3068119: CardProgramID passed to GetExpenseClearingCCID.
147
148 l_result := AP_WEB_DB_AP_INT_PKG.GetExpenseClearingCCID( l_ccid,
149 p_cardProgramID,
150 l_employee_id,
151 l_transaction_date);
152
153 IF ( l_result <> TRUE ) THEN
154 l_ccid := NULL;
155 END IF;
156
157 l_count := l_count + 1;
158 l_sum := l_sum + l_billed_amt;
159
160 IF ( l_count = 1 ) THEN
161 --------------------------------------------------------------------
162 l_debugInfo := 'Getting next sequence from AP_INVOICES_INTERFACE_S';
163 --------------------------------------------------------------------
164 l_result := AP_WEB_DB_AP_INT_PKG.GetNextInvoiceId(
165 l_invoice_id );
166
167 IF ( l_result <> TRUE ) THEN
168 l_invoice_id := NULL;
169 END IF;
170 END IF;
171
172 --------------------------------------------------------------------
173 l_debugInfo := 'Getting next sequence from AP_INVOICE_LINES_INTERFACE_S';
174 --------------------------------------------------------------------
175 l_result := AP_WEB_DB_AP_INT_PKG.GetNextInvoiceLineId(
176 l_invoice_line_id );
177 IF ( l_result <> TRUE ) THEN
178 l_invoice_line_id := NULL;
179 END IF;
180
181 /*Bug 2889204 : Setting the description for invoice line */
182 --l_masked_cc_number := l_masked_cc_number||'/'||l_full_name;
183 FND_MESSAGE.SET_NAME('SQLAP','OIE_INVOICE_DESC');
184 l_description := FND_MESSAGE.GET;
185 l_description := replace(l_description,'EMP_FULL_NAME',l_full_name);
186 l_description := replace(l_description,'EMP_CARD_NUM',l_masked_cc_number);
187 l_description := replace(l_description,'EXP_RPT_PURPOSE','');
188 l_description := substrb(l_description,1,240);
189 l_description := rtrim(l_description);
190
191 IF substr(l_description, -1) = '-' THEN
192 l_description := substr(l_description,1, length(l_description) -1);
193 END IF;
194
195 ---------------------------------------------------------------------
196 l_debugInfo := 'Inserting into AP_INVOICE_LINES_INTERFACE';
197 ---------------------------------------------------------------------
198 l_result := AP_WEB_DB_AP_INT_PKG.InsertInvoiceLinesInterface(
199 l_invoice_id,
200 l_invoice_line_id,
201 l_count,
202 'ITEM',
203 l_billed_amt,
204 l_transaction_date,
205 l_ccid,
206 l_card_trxn_id,
207 l_description,
208 l_org_id );
209
210 ----------------------------------------------------------------------
211 l_debugInfo := 'Write to the log file regarding the progress of the operation';
212 ----------------------------------------------------------------------
213 FND_FILE.PUT_LINE( FND_FILE.LOG, ' processing transaction id: ' || l_card_trxn_id );
214
215 ----------------------------------------------------------------------
216 l_debugInfo := 'Update AP_CREDIT_CARD_TRXNS_ALL.COMPANY_PREPAID_INVOICE_ID';
217 ----------------------------------------------------------------------
218 l_result := AP_WEB_DB_CCARD_PKG.SetCCTrxnInvoiceId(
219 l_card_trxn_id,
220 l_invoice_id );
221
222 END LOOP;
223
224 CLOSE l_ccard_trxn_cursor;
225 END IF;
226
227 ----------------------------------------------------------------------
228 l_debugInfo := 'Write to the log file regarding the progress of the operation';
229 ----------------------------------------------------------------------
230 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Total processed transactions: ' || l_count );
231 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_CARD_TRXNS_DONE_ACK' );
232 FND_MESSAGE.SET_TOKEN( 'TOTAL_TRXNS_NUM', l_count );
233 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, FND_MESSAGE.get );
234 --
235 -- create a summary invoice for this batch of credit card transactions
236 --
237 IF ( l_count > 0 )
238 THEN
239 -----------------------------------------------------------------------
240 l_debugInfo := 'Retrieving vendor info from card program';
241 -----------------------------------------------------------------------
242 l_result := AP_WEB_DB_CCARD_PKG.GetCardProgramInfo(
243 p_cardProgramID,
244 l_vendor_id,
245 l_vendor_site_id,
246 l_invoice_currency_code );
247
248 -----------------------------------------------------------------------
249 l_debugInfo := 'Retrieving Party Id using Vendor Id';
250 -----------------------------------------------------------------------
251 select party_id
252 into l_party_id
253 from ap_suppliers
254 where vendor_id = l_vendor_id
255 and rownum = 1;
256
257 -----------------------------------------------------------------------
258 l_debugInfo := 'Retrieving Pay Group Lookup Code from po_vendors';
259 -----------------------------------------------------------------------
260 l_result := AP_WEB_DB_AP_INT_PKG.GetPayGroupLookupCode(
261 l_vendor_id,
262 l_vendor_site_id,
263 l_pay_group_lookup_code);
264
265 -- Bug 9878665
266 -----------------------------------------------------------------------
267 -- l_debugInfo := 'Retrieving accts_pay_code_combination_id from po_vendors';
268 -----------------------------------------------------------------------
269 -- l_result := AP_WEB_DB_AP_INT_PKG.GetVendorCodeCombID(
270 -- l_vendor_id,
271 -- l_accts_pay_ccid);
272
273 -- Bug 6838894
274 BEGIN
275 SELECT party_site_id,
276 nvl(accts_pay_code_combination_id,(select accts_pay_code_combination_id from ap_system_parameters)), terms_id
277 INTO l_party_site_id, l_accts_pay_ccid, l_terms_id
278 FROM ap_supplier_sites
279 WHERE vendor_site_id = l_vendor_site_id;
280 EXCEPTION
281 WHEN NO_DATA_FOUND THEN
282 l_party_site_id := null;
283 l_terms_id := null;
284 l_accts_pay_ccid := null;
285 WHEN OTHERS THEN
286 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetVendorSiteId' );
287 APP_EXCEPTION.RAISE_EXCEPTION;
288 END;
289
290 -- Bug: 7234744 populate terms-id in the interface table.
291 IF (l_terms_id IS NULL) THEN
292 BEGIN
293 SELECT terms_id
294 INTO l_terms_id
295 FROM ap_suppliers
296 WHERE vendor_id = l_vendor_id;
297 EXCEPTION
298 WHEN OTHERS THEN
299 l_terms_id := null;
300 END;
301 END IF;
302
303 -- UNIQUE:SEQ_NUMBERS takes values, A - Always Used , N - Not Used , P - Partially Used
304 IF (FND_PROFILE.VALUE('UNIQUE:SEQ_NUMBERS') = 'N') THEN
305 l_doc_cat_code := NULL;
306 END IF;
307 ------------------------------------------------------------------------
308 l_debugInfo := 'Inserting into AP_INVOICES_INTERFACE';
309 fnd_file.put_line(fnd_file.log, l_debugInfo);
310 ------------------------------------------------------------------------
311 l_result := AP_WEB_DB_AP_INT_PKG.InsertInvoiceInterface(
312 l_invoice_id,
313 l_party_id,
314 l_vendor_id,
315 l_vendor_site_id,
316 l_sum,
317 l_invoice_currency_code,
318 'SelfService',
319 l_pay_group_lookup_code,
320 l_org_id,
321 l_doc_cat_code,-- Bug:7345524, replaced 'PAY REQ INV',
322 'PAYMENT REQUEST',
323 l_accts_pay_ccid,
324 l_party_site_id,
325 l_terms_id);
326
327 p_invoiceID := l_invoice_id;
328
329 FND_FILE.PUT_LINE( FND_FILE.LOG, 'A record with invoice id = ' || l_invoice_id || ' is created' );
330 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_CARD_NEW_INVOICE_ACK' );
331 FND_MESSAGE.SET_TOKEN( 'INVOICE_ID', l_invoice_id );
332 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, FND_MESSAGE.get );
333
334 ------------------------------------------------------------------------
335 l_debugInfo := 'Submitting Payment Request';
336 fnd_file.put_line(fnd_file.log, l_debugInfo);
337 ------------------------------------------------------------------------
338
339 AP_IMPORT_INVOICES_PKG.g_debug_switch := 'Y';
340
341 l_invoice_id_temp := l_invoice_id; -- Bug 6687752
342
343 AP_IMPORT_INVOICES_PKG.SUBMIT_PAYMENT_REQUEST(
344 p_api_version => 1.0,
345 p_invoice_interface_id => l_invoice_id,
346 p_budget_control => 'N',
347 p_needs_invoice_approval=> 'N',
348 p_invoice_id => l_invoice_id,
349 x_return_status => x_return_status,
350 x_msg_count => x_msg_count,
351 x_msg_data => x_msg_data,
352 x_rejection_list => l_rejection_list,
353 p_calling_sequence => 'AP_CREDIT_CARD_INVOICE_PKG.createCreditCardInvoice');
354
355 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
356
357 /*
358 Rejected invoice should be purged from the interface table
359 */
360 update ap_credit_card_trxns_all
361 set company_prepaid_invoice_id = null
362 where company_prepaid_invoice_id = l_invoice_id_temp -- Bug 6687752
363 and card_program_id = p_cardProgramID;
364
365 /*
366 delete from ap_interface_rejections
367 where parent_id = l_invoice_id;
368
369 delete from ap_invoices_interface
370 where invoice_id = l_invoice_id;
371
372 delete from ap_invoice_lines_interface
373 where invoice_id = l_invoice_id;
374 */
375
376 FOR i in l_rejection_list.FIRST .. l_rejection_list.LAST LOOP
377 l_debugInfo := i||' Errors found interfacing data to AP ...';
378 fnd_file.put_line(fnd_file.log, l_debugInfo);
379 l_debugInfo := l_rejection_list(i).reject_lookup_code;
380 fnd_file.put_line(fnd_file.log, l_debugInfo);
381 END LOOP;
382
383 Else -- Bug 8365869 start
384 update ap_credit_card_trxns_all
385 set company_prepaid_invoice_id = l_invoice_id
386 where company_prepaid_invoice_id = l_invoice_id_temp -- Bug 8365869 update the company_prepaid_invoice_id as invoice_id in ap_invoices_all
387 and card_program_id = p_cardProgramID;
388 -- Bug 8365869 end
389 COMMIT;
390
391 END IF;
392
393 END IF;
394
395 EXCEPTION
396
397 WHEN OTHERS THEN
398 BEGIN
399 IF ( SQLCODE <> -20001 )
400 THEN
401 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
402 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
403 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'createCreditCardInvoice' );
404 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debugInfo );
405 errbuf := FND_MESSAGE.get;
406 retcode := 2;
407 ELSE
408 -- Do not need to set the token since it has been done in the
409 -- child process
410 RAISE;
411 END IF;
412 END;
413
414 END createCreditCardInvoice; -- Enter further code below as specified in the Package spec.
415
416
417 PROCEDURE createCCardReversals(p_invoiceId IN NUMBER,
418 p_expReportHeaderId IN NUMBER,
419 p_glDate IN DATE,
420 p_periodName IN VARCHAR2)
421 IS
422 l_cCardLineCursor AP_WEB_DB_EXPLINE_PKG.CCTrxnCursor;
423 l_debugInfo VARCHAR2(2000);
424 l_clearingCCID NUMBER;
425 l_invoiceAmt AP_WEB_DB_AP_INT_PKG.invLines_amount := 0;
426 l_baseAmt AP_WEB_DB_AP_INT_PKG.invAll_baseAmount;
427 l_totalCCardAmt NUMBER := 0;
428 l_callingSequence varchar2(100);
429 l_minAcctUnit AP_WEB_DB_COUNTRY_PKG.curr_minAcctUnit ;
430 l_precision AP_WEB_DB_COUNTRY_PKG.curr_precision;
431 l_exchangeRate AP_WEB_DB_AP_INT_PKG.invAll_exchangeRate;
432 l_prepaidInvId AP_WEB_DB_CCARD_PKG.ccTrxn_companyPrepaidInvID;
433 l_cCardLineAmt AP_WEB_DB_EXPLINE_PKG.expLines_amount;
434 l_cardProgramID NUMBER;
435 l_Personal VARCHAR2(10);
436 l_org_id NUMBER;
437 l_transaction_date DATE;
438 l_employee_id NUMBER;
439
440 BEGIN
441 l_callingSequence := 'createCCardReversals';
442 ------------------------------------------------------------------
443 l_debugInfo := 'Get the invoice amount.';
444 ------------------------------------------------------------------
445 if (AP_WEB_DB_AP_INT_PKG.GetInvoiceAmt(p_invoiceId,l_invoiceAmt, l_exchangeRate,
446 l_minAcctUnit, l_precision) <> true) then
447 raise NO_DATA_FOUND;
448 end if;
449
450 ------------------------------------------------------------------
451 l_debugInfo := 'Get the credit card report line cursor.';
452 ----------------------------------------------------------------
453 if (AP_WEB_DB_EXPLINE_PKG.GetCCardLineCursor(p_expReportHeaderId, l_cCardLineCursor) <> TRUE) THEN
454 raise NO_DATA_FOUND;
455 END IF;
456
457 ------------------------------------------------------------------
458 l_debugInfo := 'Create negative distribution lines in the invoice table.';
459 ------------------------------------------------------------------
460 LOOP
461 FETCH l_cCardLineCursor INTO
462 l_cCardLineAmt, l_prepaidInvId, l_cardProgramID, l_Personal, l_org_id,
463 l_transaction_date,l_employee_id;
464 EXIT WHEN l_cCardLineCursor%NOTFOUND;
465
466 l_totalCCardAmt := l_totalCCardAmt + l_cCardLineAmt;
467
468 IF (l_Personal <> 'PERSONAL') THEN
469
470 -- Bug 3068119: CardProgramID passed to GetExpenseClearingCCID.
471 IF (AP_WEB_DB_AP_INT_PKG.GetExpenseClearingCCID(l_clearingCCID,l_cardProgramID,
472 l_employee_id, l_transaction_date) <> true) then
473 raise NO_DATA_FOUND;
474 END IF;
475
476 IF l_clearingCCID is null THEN
477 raise no_data_found;
478 END IF;
479
480 AP_WEB_WRAPPER_PKG.insert_dist(
481 p_invoice_id => p_invoiceId,
482 p_Line_Type => 'MISCELLANEOUS',
483 p_GL_Date => p_glDate,
484 p_Period_Name => p_periodName,
485 p_Type_1099 => null,
486 p_Income_Tax_Region => null,
487 p_Amount => (l_cCardLineAmt),
488 p_Vat_Code => null,
489 p_Code_Combination_Id => l_clearingCCID,
490 p_PA_Quantity => null,
491 p_Description => null,
492 p_Project_Acct_Cont => null,
493 p_Project_Id => null,
494 p_Task_Id => null,
495 p_Expenditure_Type => null,
496 p_Expenditure_Org_Id => null,
497 p_Exp_item_date => null,
498 p_Attribute_Category => null,
499 p_Attribute1 => null,
500 p_Attribute2 => null,
501 p_Attribute3 => null,
502 p_Attribute4 => null,
503 p_Attribute5 => null,
504 p_Attribute6 => null,
505 p_Attribute7 => null,
506 p_Attribute8 => null,
507 p_Attribute9 => null,
508 p_Attribute10 => null,
509 p_Attribute11 => null,
510 p_Attribute12 => null,
511 p_Attribute13 => null,
512 p_Attribute14 => null,
513 p_Attribute15 => null,
514 p_invoice_distribution_id => null,
515 p_Tax_Code_Id => null,
516 p_tax_recoverable_flag => null,
517 p_tax_recovery_rate => null,
518 p_tax_code_override_flag => null,
519 p_tax_recovery_override_flag => null,
520 p_po_distribution_id => null,
521 p_Calling_Sequence => l_callingSequence,
522 p_company_prepaid_invoice_id => l_prepaidInvId,
523 p_cc_reversal_flag => 'Y');
524 END IF;
525 END LOOP;
526 CLOSE l_cCardLineCursor;
527
528 -- Code Fix for bug 1930746.changed greater than to Not Equal to
529 if (l_totalCCardAmt <> 0) then
530 -------------------------------------------------------------------
531 l_debugInfo := 'Update the invoice_amount.';
532 -------------------------------------------------------------------
533 l_invoiceAmt := l_invoiceAmt - l_totalCCardAmt;
534 if (l_minAcctUnit is NULL) then
535 l_baseAmt := ROUND(l_invoiceAmt*l_exchangeRate, l_precision);
536 else
537 l_baseAmt := ROUND(l_invoiceAmt*l_exchangeRate/l_minAcctUnit) * l_minAcctUnit;
538 end if;
539 if (AP_WEB_DB_AP_INT_PKG.SetInvoiceAmount(p_invoiceId, l_invoiceAmt, l_baseAmt) <> true) then
540 raise NO_DATA_FOUND;
541 end if;
542 end if;
543
544 EXCEPTION
545 When OTHERS then
546 IF (SQLCODE <> -20001) THEN
547 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
548 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
549 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', l_callingSequence);
550 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debugInfo );
551 APP_EXCEPTION.RAISE_EXCEPTION;
552 ELSE
553 -- Do not need to set the token since it has been done in the
554 -- child process
555 RAISE;
556 END IF;
557 END createCCardReversals;
558
559 ---------------------------------------------------------------
560 FUNCTION createCreditCardReversals(p_invoiceId IN NUMBER,
561 p_expReportHeaderId IN NUMBER,
562 p_gl_date IN DATE,
563 p_invoiceTotal IN NUMBER)
564 RETURN NUMBER IS
565 l_cCardLineCursor AP_WEB_DB_EXPLINE_PKG.CCTrxnCursor;
566 l_debugInfo VARCHAR2(2000);
567 l_clearingCCID NUMBER;
568 l_invoiceAmt AP_WEB_DB_AP_INT_PKG.invLines_amount := p_invoiceTotal;
569 l_totalCCardAmt NUMBER := 0;
570 l_prepaidInvId AP_WEB_DB_CCARD_PKG.ccTrxn_companyPrepaidInvID;
571 l_cCardLineAmt AP_WEB_DB_EXPLINE_PKG.expLines_amount;
572 l_cardProgramID NUMBER;
573 l_Personal VARCHAR2(10);
574 l_callingSequence varchar2(100);
575 l_org_id NUMBER;
576 l_transaction_date DATE;
577 l_employee_id NUMBER;
578 l_vat_code AP_EXPENSE_REPORT_LINES_ALL.VAT_CODE%TYPE;
579 l_amount_includes_tax_flag AP_EXPENSE_REPORT_LINES_ALL.amount_includes_tax_flag%TYPE;
580 l_parameterId number;
581
582 BEGIN
583 l_callingSequence := 'createCreditCardReversals';
584
585 if (AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_parameterId) <> TRUE) then
586 APP_EXCEPTION.RAISE_EXCEPTION;
587 end if;
588 ------------------------------------------------------------------
589 l_debugInfo := 'Get the credit card report line cursor.';
590 ------------------------------------------------------------------
591 /*if (AP_WEB_DB_EXPLINE_PKG.GetCCardLineCursor(p_expReportHeaderId,
592 l_cCardLineCursor) <> TRUE) THEN
593 raise NO_DATA_FOUND;
594 END IF;*/
595
596 OPEN l_cCardLineCursor FOR
597 SELECT decode(erl.web_parameter_id,l_parameterId,-erl.amount,erl.amount),
598 cc.company_prepaid_invoice_id, cc.card_program_id,
599 decode(erl.web_parameter_id,l_parameterId, 'PERSONAL', 'BUSINESS'),
600 erl.org_id,
601 nvl(cc.transaction_date,sysdate),
602 erh.employee_id,
603 erl.vat_code,
604 erl.amount_includes_tax_flag
605 FROM ap_expense_report_lines_all erl,
606 ap_credit_card_trxns_all cc,
607 ap_expense_report_headers_all erh
608 WHERE erl.report_header_id = erh.report_header_id
609 AND nvl(erl.itemization_parent_id,0) <> -1 /* Itemization Project */
610 AND cc.trx_id = erl.credit_card_trx_id -- is a credit card transaction
611 AND cc.payment_due_from_code in ('BOTH','COMPANY') -- Both Pay split project
612 AND erh.report_header_id = p_expReportHeaderId
613 AND erh.source = 'SelfService';
614
615
616 --------------------------------------------------------------------------
617 l_debugInfo := 'Create negative distribution lines in the invoice table.';
618 --------------------------------------------------------------------------
619 LOOP
620 FETCH l_cCardLineCursor
621 INTO l_cCardLineAmt, l_prepaidInvId, l_cardProgramID, l_Personal, l_org_id,
622 l_transaction_date,l_employee_id, l_vat_code, l_amount_includes_tax_flag;
623 EXIT WHEN l_cCardLineCursor%NOTFOUND;
624
625 l_totalCCardAmt := l_totalCCardAmt + l_cCardLineAmt;
626
627 IF (l_Personal <> 'PERSONAL') THEN
628
629 -- Bug 3068119: CardProgramID passed to GetExpenseClearingCCID.
630 IF (AP_WEB_DB_AP_INT_PKG.GetExpenseClearingCCID(l_clearingCCID,l_cardProgramID,
631 l_employee_id, l_transaction_date) <> true) then
632 raise NO_DATA_FOUND;
633 END IF;
634
635 IF l_clearingCCID is null THEN
636 raise no_data_found;
637 END IF;
638
639 INSERT INTO AP_INVOICE_LINES_INTERFACE
640 (INVOICE_ID,
641 INVOICE_LINE_ID,
642 LINE_TYPE_LOOKUP_CODE,
643 ACCOUNTING_DATE,
644 AMOUNT,
645 ASSETS_TRACKING_FLAG,
646 DIST_CODE_COMBINATION_ID,
647 ORG_ID,
648 CC_REVERSAL_FLAG,
649 TAX_CLASSIFICATION_CODE,
650 AMOUNT_INCLUDES_TAX_FLAG)
651 SELECT p_invoiceId,
652 AP_INVOICE_LINES_INTERFACE_S.nextval,
653 'MISCELLANEOUS',
654 p_gl_date,
655 -l_cCardLineAmt,
656 DECODE(nvl(gcc.account_type, 'x'),
657 'A',
658 'Y',
659 'N'),
660 l_clearingCCID,
661 l_org_id,
662 'Y',
663 l_vat_code,
664 l_amount_includes_tax_flag
665 FROM gl_code_combinations GCC
666 WHERE GCC.code_combination_id = l_clearingCCID;
667
668 END IF;
669 END LOOP;
670 CLOSE l_cCardLineCursor;
671
672 if (l_totalCCardAmt <> 0) then
673 -------------------------------------------------------------------
674 l_debugInfo := 'Update the invoice_amount.';
675 -------------------------------------------------------------------
676 l_invoiceAmt := l_invoiceAmt - l_totalCCardAmt;
677 end if;
678 RETURN l_invoiceAmt;
679 EXCEPTION
680 When OTHERS then
681 IF (SQLCODE <> -20001) THEN
682 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
683 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
684 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_callingSequence);
685 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debugInfo);
686 APP_EXCEPTION.RAISE_EXCEPTION;
687 ELSE
688 -- Do not need to set the token since it has been done in the
689 -- child process
690 RAISE;
691 END IF;
692 NULL;
693 END createCreditCardReversals;
694
695 END AP_CREDIT_CARD_INVOICE_PKG;