[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.16.12010000.4 2008/09/15 07:49:47 rveliche 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 -----------------------------------------------------------------------
266 l_debugInfo := 'Retrieving accts_pay_code_combination_id from po_vendors';
267 -----------------------------------------------------------------------
268 l_result := AP_WEB_DB_AP_INT_PKG.GetVendorCodeCombID(
269 l_vendor_id,
270 l_accts_pay_ccid);
271 -- Bug 6838894
272 BEGIN
273 SELECT party_site_id, nvl(l_accts_pay_ccid,accts_pay_code_combination_id), terms_id
274 INTO l_party_site_id, l_accts_pay_ccid, l_terms_id
275 FROM ap_supplier_sites
276 WHERE vendor_site_id = l_vendor_site_id;
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 l_party_site_id := null;
280 l_terms_id := null;
281 WHEN OTHERS THEN
282 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetVendorSiteId' );
283 APP_EXCEPTION.RAISE_EXCEPTION;
284 END;
285
286 -- Bug: 7234744 populate terms-id in the interface table.
287 IF (l_terms_id IS NULL) THEN
288 BEGIN
289 SELECT terms_id
290 INTO l_terms_id
291 FROM ap_suppliers
292 WHERE vendor_id = l_vendor_id;
293 EXCEPTION
294 WHEN OTHERS THEN
295 l_terms_id := null;
296 END;
297 END IF;
298
299 -- UNIQUE:SEQ_NUMBERS takes values, A - Always Used , N - Not Used , P - Partially Used
300 IF (FND_PROFILE.VALUE('UNIQUE:SEQ_NUMBERS') = 'N') THEN
301 l_doc_cat_code := NULL;
302 END IF;
303 ------------------------------------------------------------------------
304 l_debugInfo := 'Inserting into AP_INVOICES_INTERFACE';
305 fnd_file.put_line(fnd_file.log, l_debugInfo);
306 ------------------------------------------------------------------------
307 l_result := AP_WEB_DB_AP_INT_PKG.InsertInvoiceInterface(
308 l_invoice_id,
309 l_party_id,
310 l_vendor_id,
311 l_vendor_site_id,
312 l_sum,
313 l_invoice_currency_code,
314 'SelfService',
315 l_pay_group_lookup_code,
316 l_org_id,
317 l_doc_cat_code,-- Bug:7345524, replaced 'PAY REQ INV',
318 'PAYMENT REQUEST',
319 l_accts_pay_ccid,
320 l_party_site_id,
321 l_terms_id);
322
323 p_invoiceID := l_invoice_id;
324
325 FND_FILE.PUT_LINE( FND_FILE.LOG, 'A record with invoice id = ' || l_invoice_id || ' is created' );
326 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_CARD_NEW_INVOICE_ACK' );
327 FND_MESSAGE.SET_TOKEN( 'INVOICE_ID', l_invoice_id );
328 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, FND_MESSAGE.get );
329
330 ------------------------------------------------------------------------
331 l_debugInfo := 'Submitting Payment Request';
332 fnd_file.put_line(fnd_file.log, l_debugInfo);
333 ------------------------------------------------------------------------
334
335 AP_IMPORT_INVOICES_PKG.g_debug_switch := 'Y';
336
337 l_invoice_id_temp := l_invoice_id; -- Bug 6687752
338
339 AP_IMPORT_INVOICES_PKG.SUBMIT_PAYMENT_REQUEST(
340 p_api_version => 1.0,
341 p_invoice_interface_id => l_invoice_id,
342 p_budget_control => 'N',
343 p_needs_invoice_approval=> 'N',
344 p_invoice_id => l_invoice_id,
345 x_return_status => x_return_status,
346 x_msg_count => x_msg_count,
347 x_msg_data => x_msg_data,
348 x_rejection_list => l_rejection_list,
349 p_calling_sequence => 'AP_CREDIT_CARD_INVOICE_PKG.createCreditCardInvoice');
350
351 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
352
353 /*
354 Rejected invoice should be purged from the interface table
355 */
356 update ap_credit_card_trxns_all
357 set company_prepaid_invoice_id = null
358 where company_prepaid_invoice_id = l_invoice_id_temp -- Bug 6687752
359 and card_program_id = p_cardProgramID;
360
361 /*
362 delete from ap_interface_rejections
363 where parent_id = l_invoice_id;
364
365 delete from ap_invoices_interface
366 where invoice_id = l_invoice_id;
367
371
368 delete from ap_invoice_lines_interface
369 where invoice_id = l_invoice_id;
370 */
372 FOR i in l_rejection_list.FIRST .. l_rejection_list.LAST LOOP
373 l_debugInfo := i||' Errors found interfacing data to AP ...';
374 fnd_file.put_line(fnd_file.log, l_debugInfo);
375 l_debugInfo := l_rejection_list(i).reject_lookup_code;
376 fnd_file.put_line(fnd_file.log, l_debugInfo);
377 END LOOP;
378
379 COMMIT;
380
381 END IF;
382
383 END IF;
384
385 EXCEPTION
386
387 WHEN OTHERS THEN
388 BEGIN
389 IF ( SQLCODE <> -20001 )
390 THEN
391 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
392 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
393 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'createCreditCardInvoice' );
394 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debugInfo );
395 errbuf := FND_MESSAGE.get;
396 retcode := 2;
397 ELSE
398 -- Do not need to set the token since it has been done in the
399 -- child process
400 RAISE;
401 END IF;
402 END;
403
404 END createCreditCardInvoice; -- Enter further code below as specified in the Package spec.
405
406
407 PROCEDURE createCCardReversals(p_invoiceId IN NUMBER,
408 p_expReportHeaderId IN NUMBER,
409 p_glDate IN DATE,
410 p_periodName IN VARCHAR2)
411 IS
412 l_cCardLineCursor AP_WEB_DB_EXPLINE_PKG.CCTrxnCursor;
413 l_debugInfo VARCHAR2(2000);
414 l_clearingCCID NUMBER;
415 l_invoiceAmt AP_WEB_DB_AP_INT_PKG.invLines_amount := 0;
416 l_baseAmt AP_WEB_DB_AP_INT_PKG.invAll_baseAmount;
417 l_totalCCardAmt NUMBER := 0;
418 l_callingSequence varchar2(100);
419 l_minAcctUnit AP_WEB_DB_COUNTRY_PKG.curr_minAcctUnit ;
420 l_precision AP_WEB_DB_COUNTRY_PKG.curr_precision;
421 l_exchangeRate AP_WEB_DB_AP_INT_PKG.invAll_exchangeRate;
422 l_prepaidInvId AP_WEB_DB_CCARD_PKG.ccTrxn_companyPrepaidInvID;
423 l_cCardLineAmt AP_WEB_DB_EXPLINE_PKG.expLines_amount;
424 l_cardProgramID NUMBER;
425 l_Personal VARCHAR2(10);
426 l_org_id NUMBER;
427 l_transaction_date DATE;
428 l_employee_id NUMBER;
429
430 BEGIN
431 l_callingSequence := 'createCCardReversals';
432 ------------------------------------------------------------------
433 l_debugInfo := 'Get the invoice amount.';
434 ------------------------------------------------------------------
435 if (AP_WEB_DB_AP_INT_PKG.GetInvoiceAmt(p_invoiceId,l_invoiceAmt, l_exchangeRate,
436 l_minAcctUnit, l_precision) <> true) then
437 raise NO_DATA_FOUND;
438 end if;
439
440 ------------------------------------------------------------------
441 l_debugInfo := 'Get the credit card report line cursor.';
442 ----------------------------------------------------------------
443 if (AP_WEB_DB_EXPLINE_PKG.GetCCardLineCursor(p_expReportHeaderId, l_cCardLineCursor) <> TRUE) THEN
444 raise NO_DATA_FOUND;
445 END IF;
446
447 ------------------------------------------------------------------
448 l_debugInfo := 'Create negative distribution lines in the invoice table.';
449 ------------------------------------------------------------------
450 LOOP
451 FETCH l_cCardLineCursor INTO
452 l_cCardLineAmt, l_prepaidInvId, l_cardProgramID, l_Personal, l_org_id,
453 l_transaction_date,l_employee_id;
454 EXIT WHEN l_cCardLineCursor%NOTFOUND;
455
456 l_totalCCardAmt := l_totalCCardAmt + l_cCardLineAmt;
457
458 IF (l_Personal <> 'PERSONAL') THEN
459
460 -- Bug 3068119: CardProgramID passed to GetExpenseClearingCCID.
461 IF (AP_WEB_DB_AP_INT_PKG.GetExpenseClearingCCID(l_clearingCCID,l_cardProgramID,
462 l_employee_id, l_transaction_date) <> true) then
463 raise NO_DATA_FOUND;
464 END IF;
465
466 IF l_clearingCCID is null THEN
467 raise no_data_found;
468 END IF;
469
470 AP_WEB_WRAPPER_PKG.insert_dist(
471 p_invoice_id => p_invoiceId,
472 p_Line_Type => 'MISCELLANEOUS',
473 p_GL_Date => p_glDate,
474 p_Period_Name => p_periodName,
475 p_Type_1099 => null,
476 p_Income_Tax_Region => null,
477 p_Amount => (l_cCardLineAmt),
478 p_Vat_Code => null,
479 p_Code_Combination_Id => l_clearingCCID,
480 p_PA_Quantity => null,
481 p_Description => null,
482 p_Project_Acct_Cont => null,
483 p_Project_Id => null,
484 p_Task_Id => null,
485 p_Expenditure_Type => null,
486 p_Expenditure_Org_Id => null,
487 p_Exp_item_date => null,
488 p_Attribute_Category => null,
492 p_Attribute4 => null,
489 p_Attribute1 => null,
490 p_Attribute2 => null,
491 p_Attribute3 => null,
493 p_Attribute5 => null,
494 p_Attribute6 => null,
495 p_Attribute7 => null,
496 p_Attribute8 => null,
497 p_Attribute9 => null,
498 p_Attribute10 => null,
499 p_Attribute11 => null,
500 p_Attribute12 => null,
501 p_Attribute13 => null,
502 p_Attribute14 => null,
503 p_Attribute15 => null,
504 p_invoice_distribution_id => null,
505 p_Tax_Code_Id => null,
506 p_tax_recoverable_flag => null,
507 p_tax_recovery_rate => null,
508 p_tax_code_override_flag => null,
509 p_tax_recovery_override_flag => null,
510 p_po_distribution_id => null,
511 p_Calling_Sequence => l_callingSequence,
512 p_company_prepaid_invoice_id => l_prepaidInvId,
513 p_cc_reversal_flag => 'Y');
514 END IF;
515 END LOOP;
516 CLOSE l_cCardLineCursor;
517
518 -- Code Fix for bug 1930746.changed greater than to Not Equal to
519 if (l_totalCCardAmt <> 0) then
520 -------------------------------------------------------------------
521 l_debugInfo := 'Update the invoice_amount.';
522 -------------------------------------------------------------------
523 l_invoiceAmt := l_invoiceAmt - l_totalCCardAmt;
524 if (l_minAcctUnit is NULL) then
525 l_baseAmt := ROUND(l_invoiceAmt*l_exchangeRate, l_precision);
526 else
527 l_baseAmt := ROUND(l_invoiceAmt*l_exchangeRate/l_minAcctUnit) * l_minAcctUnit;
528 end if;
529 if (AP_WEB_DB_AP_INT_PKG.SetInvoiceAmount(p_invoiceId, l_invoiceAmt, l_baseAmt) <> true) then
530 raise NO_DATA_FOUND;
531 end if;
532 end if;
533
534 EXCEPTION
535 When OTHERS then
536 IF (SQLCODE <> -20001) THEN
537 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
538 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
539 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', l_callingSequence);
540 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debugInfo );
541 APP_EXCEPTION.RAISE_EXCEPTION;
542 ELSE
543 -- Do not need to set the token since it has been done in the
544 -- child process
545 RAISE;
546 END IF;
547 END createCCardReversals;
548
549 ---------------------------------------------------------------
550 FUNCTION createCreditCardReversals(p_invoiceId IN NUMBER,
551 p_expReportHeaderId IN NUMBER,
552 p_gl_date IN DATE,
553 p_invoiceTotal IN NUMBER)
554 RETURN NUMBER IS
555 l_cCardLineCursor AP_WEB_DB_EXPLINE_PKG.CCTrxnCursor;
556 l_debugInfo VARCHAR2(2000);
557 l_clearingCCID NUMBER;
558 l_invoiceAmt AP_WEB_DB_AP_INT_PKG.invLines_amount := p_invoiceTotal;
559 l_totalCCardAmt NUMBER := 0;
560 l_prepaidInvId AP_WEB_DB_CCARD_PKG.ccTrxn_companyPrepaidInvID;
561 l_cCardLineAmt AP_WEB_DB_EXPLINE_PKG.expLines_amount;
562 l_cardProgramID NUMBER;
563 l_Personal VARCHAR2(10);
564 l_callingSequence varchar2(100);
565 l_org_id NUMBER;
566 l_transaction_date DATE;
567 l_employee_id NUMBER;
568
569 BEGIN
570 l_callingSequence := 'createCreditCardReversals';
571 ------------------------------------------------------------------
572 l_debugInfo := 'Get the credit card report line cursor.';
573 ------------------------------------------------------------------
574 if (AP_WEB_DB_EXPLINE_PKG.GetCCardLineCursor(p_expReportHeaderId,
575 l_cCardLineCursor) <> TRUE) THEN
576 raise NO_DATA_FOUND;
577 END IF;
578
579 --------------------------------------------------------------------------
580 l_debugInfo := 'Create negative distribution lines in the invoice table.';
581 --------------------------------------------------------------------------
582 LOOP
583 FETCH l_cCardLineCursor
584 INTO l_cCardLineAmt, l_prepaidInvId, l_cardProgramID, l_Personal, l_org_id,
585 l_transaction_date,l_employee_id;
586 EXIT WHEN l_cCardLineCursor%NOTFOUND;
587
588 l_totalCCardAmt := l_totalCCardAmt + l_cCardLineAmt;
589
590 IF (l_Personal <> 'PERSONAL') THEN
591
592 -- Bug 3068119: CardProgramID passed to GetExpenseClearingCCID.
593 IF (AP_WEB_DB_AP_INT_PKG.GetExpenseClearingCCID(l_clearingCCID,l_cardProgramID,
594 l_employee_id, l_transaction_date) <> true) then
595 raise NO_DATA_FOUND;
596 END IF;
597
598 IF l_clearingCCID is null THEN
599 raise no_data_found;
600 END IF;
601
602 INSERT INTO AP_INVOICE_LINES_INTERFACE
603 (INVOICE_ID,
604 INVOICE_LINE_ID,
605 LINE_TYPE_LOOKUP_CODE,
606 ACCOUNTING_DATE,
607 AMOUNT,
608 ASSETS_TRACKING_FLAG,
609 DIST_CODE_COMBINATION_ID,
610 ORG_ID,
611 CC_REVERSAL_FLAG)
612 SELECT p_invoiceId,
613 AP_INVOICE_LINES_INTERFACE_S.nextval,
614 'MISCELLANEOUS',
615 p_gl_date,
616 -l_cCardLineAmt,
617 DECODE(nvl(gcc.account_type, 'x'),
618 'A',
619 'Y',
620 'N'),
621 l_clearingCCID,
622 l_org_id,
623 'Y'
624 FROM gl_code_combinations GCC
625 WHERE GCC.code_combination_id = l_clearingCCID;
626
627 END IF;
628 END LOOP;
629 CLOSE l_cCardLineCursor;
630
631 if (l_totalCCardAmt <> 0) then
632 -------------------------------------------------------------------
633 l_debugInfo := 'Update the invoice_amount.';
634 -------------------------------------------------------------------
635 l_invoiceAmt := l_invoiceAmt - l_totalCCardAmt;
636 end if;
637 RETURN l_invoiceAmt;
638 EXCEPTION
639 When OTHERS then
640 IF (SQLCODE <> -20001) THEN
641 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
642 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
643 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_callingSequence);
644 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debugInfo);
645 APP_EXCEPTION.RAISE_EXCEPTION;
646 ELSE
647 -- Do not need to set the token since it has been done in the
648 -- child process
649 RAISE;
650 END IF;
651 NULL;
652 END createCreditCardReversals;
653
654 END AP_CREDIT_CARD_INVOICE_PKG;