[Home] [Help]
PACKAGE BODY: APPS.FV_APPLY_CASH_RECEIPT
Source
1 PACKAGE BODY fv_apply_cash_receipt AS
2 --$Header: FVXDCCRB.pls 120.20.12020000.3 2013/02/13 14:32:50 snama ship $
3
4 g_module_name VARCHAR2(100) := 'fv.plsql.fvxdccrb.fv_apply_cash_receipt.';
5 g_org_id NUMBER;
6 g_sob_id NUMBER;
7 -- g_debug VARCHAR2(1);
8 g_ErrorFound BOOLEAN := FALSE;
9
10 g_DEBIT_MEMO NUMBER := 1;
11 g_INVOICE NUMBER := 2;
12
13
14 ------------------------------------------------------------------------
15 -- Output data structures
16 ------------------------------------------------------------------------
17 TYPE ErrorInfoRec IS RECORD
18 (
19 error_code NUMBER,
20 error_desc VARCHAR2(1024)
21 );
22
23 TYPE ErrorInfoTbl IS TABLE OF ErrorInfoRec INDEX BY BINARY_INTEGER;
24
25 /*
26 TYPE ErrorMessagesTblType IS TABLE OF VARCHAR2(2048) INDEX BY BINARY_INTEGER;
27 g_ErrorMessages ErrorMessagesTblType;
28 g_MaxErrorMessages NUMBER := 0;
29
30 TYPE LogMessagesTblType IS TABLE OF VARCHAR2(2048) INDEX BY BINARY_INTEGER;
31 g_LogMessages LogMessagesTblType;
32 g_MaxLogMessages NUMBER := 0;
33 */
34
35 TYPE CashReceiptApplicationsRec IS RECORD
36 (
37 invoice_number ra_customer_trx.trx_number%TYPE,
38 line_number ra_customer_trx_lines.line_number%TYPE,
39 invoice_type VARCHAR2(100),
40 applied_amount NUMBER,
41 applied_currency fnd_currencies_vl.name%TYPE,
42 amt_applied_in_inv_curr NUMBER,
43 invoice_amount_due NUMBER,
44 invoice_currency fnd_currencies_vl.name%TYPE,
45 exchange_rate NUMBER,
46 status VARCHAR2(1) DEFAULT 'A'
47 );
48
49 TYPE CashReceiptApplicationsTbl IS TABLE OF CashReceiptApplicationsRec INDEX BY BINARY_INTEGER;
50
51 TYPE CashReceiptRec IS RECORD
52 (
53 receipt_number fv_interim_cash_receipts.receipt_number%TYPE,
54 customer_name hz_parties.party_name%TYPE,
55 receipt_amount fv_interim_cash_receipts.amount%TYPE,
56 applied_currency fnd_currencies_vl.name%TYPE,
57 actual_amount NUMBER,
58 actual_currency fnd_currencies_vl.name%TYPE,
59 total_applications NUMBER,
60 total_errors NUMBER
61 );
62
63 TYPE CashReceiptTbl IS TABLE OF CashReceiptRec INDEX BY BINARY_INTEGER;
64
65 g_OutReceiptApplications CashReceiptApplicationsTbl;
66 g_OutCashReceipts CashReceiptRec;
67 g_OutErrorInfo ErrorInfoTbl;
68
69
70 ------------------------------------------------------------------------
71 -- Data Structures required for Calling API's
72 ------------------------------------------------------------------------
73
74 ------------------------------------------------------------------------
75 -- Parameters Required for CreateCash API --
76 ------------------------------------------------------------------------
77 TYPE CreateCashRecType IS RECORD
78 (
79 usr_currency_code fnd_currencies_vl.name%TYPE,
80 currency_code fnd_currencies_vl.name%TYPE,
81 usr_exchange_rate_type gl_daily_conversion_types.user_conversion_type%TYPE,
82 exchange_rate_type ar_cash_receipts.exchange_rate_type%TYPE,
83 exchange_rate ar_cash_receipts.exchange_rate%TYPE,
84 exchange_rate_date ar_cash_receipts.exchange_date%TYPE,
85 amount ar_cash_receipts.amount%TYPE,
86 factor_discount_amount ar_cash_receipts.factor_discount_amount%TYPE,
87 receipt_number ar_cash_receipts.receipt_number%TYPE,
88 receipt_date ar_cash_receipts.receipt_date%type,
89 gl_date DATE,
90 maturity_date DATE,
91 postmark_date DATE,
92 customer_id hz_parties.party_id%TYPE,
93 customer_name hz_parties.party_name%TYPE,
94 customer_number hz_cust_accounts.account_number%TYPE,
95 customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE,
96 customer_bank_account_num ce_bank_accounts.bank_account_num%TYPE,
97 customer_bank_account_name ce_bank_accounts.bank_account_name%TYPE,
98 location hz_cust_site_uses.location%type,
99 customer_site_use_id hz_cust_site_uses.site_use_id%TYPE,
100 customer_receipt_reference ar_cash_receipts.customer_receipt_reference%TYPE,
101 override_remit_account_flag ar_cash_receipts.override_remit_account_flag%TYPE,
102 remittance_bank_account_id ar_cash_receipts.remit_bank_acct_use_id%TYPE,
103 remittance_bank_account_num ce_bank_accounts.bank_account_num%TYPE,
104 remittance_bank_account_name ce_bank_accounts.bank_account_name%TYPE,
105 deposit_date ar_cash_receipts.deposit_date%TYPE,
106 receipt_method_id ar_cash_receipts.receipt_method_id%TYPE,
107 receipt_method_name ar_receipt_methods.name%TYPE,
108 doc_sequence_value NUMBER,
109 -- ussgl_transaction_code ar_cash_receipts.ussgl_transaction_code%TYPE,
110 anticipated_clearing_date ar_cash_receipts.anticipated_clearing_date%TYPE,
111 called_from VARCHAR2(100),
112 attribute_rec ar_receipt_api_pub.attribute_rec_type,
113 global_attribute_rec ar_receipt_api_pub.global_attribute_rec_type,
114 comments ar_receivable_applications.comments%TYPE,
115 issuer_name ar_cash_receipts.issuer_name%TYPE,
116 issue_date ar_cash_receipts.issue_date%TYPE,
117 issuer_bank_branch_id ar_cash_receipts.issuer_bank_branch_id%TYPE,
118 org_id ar_Cash_receipts.org_id%TYPE
119 );
120
121 ------------------------------------------------------------------------
122 -- Parameters Required for ApplyCash API --
123 ------------------------------------------------------------------------
124 TYPE ApplyCashRecType IS RECORD
125 (
126 cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE,
127 receipt_number ar_cash_receipts.receipt_number%TYPE,
128 customer_trx_id ra_customer_trx.customer_trx_id%TYPE,
129 trx_number ra_customer_trx.trx_number%TYPE,
130 installment ar_payment_schedules.terms_sequence_number%TYPE,
131 applied_payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE,
132 amount_applied ar_receivable_applications.amount_applied%TYPE,
133 amount_applied_from ar_receivable_applications.amount_applied_from%TYPE,
134 trans_to_receipt_rate ar_receivable_applications.trans_to_receipt_rate%TYPE,
135 discount ar_receivable_applications.earned_discount_taken%TYPE,
136 apply_date ar_receivable_applications.apply_date%TYPE,
137 apply_gl_date ar_receivable_applications.gl_date%TYPE,
138 -- ussgl_transaction_code ar_receivable_applications.ussgl_transaction_code%TYPE,
139 org_id ar_receivable_applications.org_id%TYPE,
140 customer_trx_line_id ar_receivable_applications.applied_customer_trx_line_id%TYPE,
141 line_number ra_customer_trx_lines.line_number%TYPE,
142 show_closed_invoices VARCHAR2(100),
143 called_from VARCHAR2(100),
144 move_deferred_tax VARCHAR2(100),
145 link_to_trx_hist_id ar_receivable_applications.link_to_trx_hist_id%TYPE,
146 attribute_rec ar_receipt_api_pub.attribute_rec_type,
147 global_attribute_rec ar_receipt_api_pub.global_attribute_rec_type,
148 comments ar_receivable_applications.comments%TYPE,
149 payment_set_id ar_receivable_applications.payment_set_id%TYPE,
150 application_ref_type ar_receivable_applications.application_ref_type%TYPE,
151 application_ref_id ar_receivable_applications.application_ref_id%TYPE,
152 application_ref_num ar_receivable_applications.application_ref_num%TYPE,
153 secondary_application_ref_id ar_receivable_applications.secondary_application_ref_id%TYPE,
154 application_ref_reason ar_receivable_applications.application_ref_reason%TYPE,
155 customer_reference ar_receivable_applications.customer_reference%TYPE
156 );
157
158 ------------------------------------------------------------------------
159 -- Parameters Required for OnAccount API --
160 ------------------------------------------------------------------------
161 TYPE OnAccountRecType IS RECORD
162 (
163 cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE,
164 receipt_number ar_cash_receipts.receipt_number%TYPE,
165 amount_applied ar_receivable_applications.amount_applied%TYPE,
166 apply_date ar_receivable_applications.apply_date%TYPE,
167 apply_gl_date ar_receivable_applications.gl_date%TYPE,
168 -- ussgl_transaction_code ar_receivable_applications.ussgl_transaction_code%TYPE,
169 attribute_rec ar_receipt_api_pub.attribute_rec_type,
170 global_attribute_rec ar_receipt_api_pub.global_attribute_rec_type,
171 comments ar_receivable_applications.comments%TYPE
172 );
173
174 ------------------------------------------------------------------------
175 -- Data Structure Required for Debug. --
176 ------------------------------------------------------------------------
177 TYPE DebugRecType IS RECORD
178 (
179 pkg_name VARCHAR2(100),
180 module_name VARCHAR2(100),
181 intend_str VARCHAR2(1024),
182 error_code NUMBER,
183 error_desc VARCHAR2(1024),
184 error_loc VARCHAR2(1024)
185 );
186
187 TYPE DebugTblType IS TABLE OF DebugRecType INDEX BY BINARY_INTEGER;
188
189 g_DebugTbl DebugTblType;
190
191 TYPE DebugMessagesTblType IS TABLE OF VARCHAR2(2048) INDEX BY BINARY_INTEGER;
192 g_DebugMessages DebugMessagesTblType;
193
194 g_MaxDebugProcs NUMBER := 0;
195 g_CurDebugProcs NUMBER := 0;
196 g_MaxDebugMessages NUMBER := 0;
197
198 ------------------------------------------------------------------------
199 -- Procedure Return Values --
200 ------------------------------------------------------------------------
201 g_SUCCESS NUMBER := 0;
202 g_WARNING NUMBER := 1;
203 g_FAILURE NUMBER := 2;
204
205 --****************************************************************************************--
206 --* Name : log *--
207 --* Type : Procedure *--
208 --* Purpose : This procedure writes log messages *--
209 --* Parameters : p_pgm The Program Name *--
210 --* : p_loc The location *--
211 --* : p_msg The message that has to be written to the log file *--
212 --* Global Vars : None *--
213 --* Called from : None *--
214 --* Calls : fnd_file.put_line *--
215 --* Tables Used : None *--
216 --* Logic : Call fnd_file.put_line with the message as a parameter *--
217 --****************************************************************************************--
218 PROCEDURE log_msg
219 (
220 p_ModuleName IN VARCHAR2,
221 p_msg IN VARCHAR2
222 ) IS
223 l_module_name VARCHAR2(200) := g_module_name || 'log_msg';
224 l_errbuf VARCHAR2(1024);
225 BEGIN
226 /*
227 g_MaxLogMessages := g_MaxLogMessages + 1;
228 g_LogMessages (g_MaxLogMessages) := p_msg;
229 */
230 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
231 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, g_module_name||p_ModuleName,p_msg);
232 END IF;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 l_errbuf := SQLERRM;
237 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
238
239 END log_msg;
240
241 /*
242 PROCEDURE log_write
243 IS
244 BEGIN
245 fnd_file.put_line (fnd_file.log, '*********************Log Messages**********************');
246 FOR l_Counter IN 1..g_MaxLogMessages LOOP
247 fnd_file.put_line(fnd_file.log, g_LogMessages(l_Counter));
248 END LOOP;
249 fnd_file.put_line (fnd_file.log, '*******************************************************');
250 fnd_file.put_line (fnd_file.log, ' ');
251 END;
252 */
253
254 --****************************************************************************************--
255 --* Name : output *--
256 --* Type : Procedure *--
257 --* Purpose : This procedure writes log messages *--
258 --* Parameters : p_msg The message that has to be written to the output file *--
259 --* Global Vars : None *--
260 --* Called from : write_report_header *--
261 --* : write_report_for_a_receipt *--
262 --* Calls : fnd_file.put_line *--
263 --* Tables Used : None *--
264 --* Logic : Call fnd_file.put_line with the message as a parameter *--
265 --****************************************************************************************--
266 PROCEDURE output
267 (
268 p_msg IN VARCHAR2
269 ) IS
270 BEGIN
271 fnd_file.put_line(fnd_file.output, p_msg);
272 END output;
273
274 --****************************************************************************************--
275 --* Name : error *--
276 --* Type : Procedure *--
277 --* Purpose : This procedure writes error messages *--
278 --* Parameters : p_error_type The Type of Error (ERROR or just WARNING) *--
279 --* : p_pgm The program Name *--
280 --* : p_msg The message that has to be written to the log file *--
281 --* : p_loc The location of error *--
282 --* Global Vars : g_SUCCESS *--
283 --* Called from : To be filled in *--
284 --* Calls : fnd_file.put_line *--
285 --* Tables Used : None *--
286 --* Logic : Call fnd_file.put_line with the message as a parameter *--
287 --****************************************************************************************--
288 PROCEDURE error
289 (
290 p_error_type IN NUMBER, --ERROR or WARNING
291 p_pgm IN VARCHAR2,
292 p_msg IN VARCHAR2,
293 p_loc IN VARCHAR2
294 ) IS
295 l_Prefix VARCHAR2(100) := '';
296 l_module_name VARCHAR2(200) := g_module_name || 'error';
297 l_errbuf VARCHAR2(1024);
298 BEGIN
299 /*
300 IF (p_error_type = g_FAILURE) THEN
301 l_Prefix := 'ERROR: ';
302 ELSIF (p_error_type = g_WARNING) THEN
303 l_Prefix := 'WARNING: ';
304 ELSE
305 l_Prefix := NULL;
306 END IF;
307 g_MaxErrorMessages := g_MaxErrorMessages + 1;
308 g_ErrorMessages (g_MaxErrorMessages) := l_Prefix||p_msg ||'['||p_pgm||':'||p_loc||']';
309 */
310 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, g_module_name||p_pgm||'.'||p_loc,p_msg);
311 EXCEPTION
312 WHEN OTHERS THEN
313 l_errbuf := SQLERRM;
314 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
315
316 END error;
317
318 /*
319 PROCEDURE error_write
320 IS
321 BEGIN
322 fnd_file.put_line (fnd_file.log, '*****************Error Messages************************');
323 FOR l_Counter IN 1..g_MaxErrorMessages LOOP
324 fnd_file.put_line(fnd_file.log, g_ErrorMessages(l_Counter));
325 END LOOP;
326 fnd_file.put_line (fnd_file.log, '*******************************************************');
327 fnd_file.put_line (fnd_file.log, ' ');
328 END;
329 */
330 --****************************************************************************************--
331 --* Name : debug_msg *--
332 --* Type : Procedure *--
333 --* Purpose : Used to display debug messages *--
334 --* Parameters : p_msg IN The message that has to be written *--
335 --* Called from : debug_init *--
336 --* : debug_exit *--
337 --* : write_report_for_a_receipt *--
338 --* : dump_ar_batch *--
339 --* : apply_on_account *--
340 --* : apply_cash_receipt *--
341 --* : update_cash_receipt_hist *--
342 --* : update_fv_batch_status *--
343 --* : create_cash_receipt *--
344 --* : pay_the_invoice *--
345 --* : get_receipt_txn_code *--
346 --* : pay_debit_memos *--
347 --* : process_receipts *--
348 --* : main *--
349 --* Calls : fnd_file.put_line *--
350 --* Tables Used : None *--
351 --* Global Vars : g_debug READ *--
352 --* : FND_FILE.LOG READ *--
353 --* : g_DebugTbl READ *--
354 --* : g_CurDebugProcs READ *--
355 --* Logic : Call fnd_file.put_line with the message as a parameter. *--
356 --* : The message will be displayed only if the debug flag is on *--
357 --* : The package name, module name and the intendation string are used *--
358 --* : from the global variable called g_DebugTbl. The procedures *--
359 --* : debug_init and debug_exit inserts and modifies this table *--
360 --****************************************************************************************--
361 PROCEDURE debug_msg
362 (
363 p_ModuleName IN VARCHAR2,
364 p_Message IN VARCHAR2
365 ) IS
366 l_module_name VARCHAR2(200) := g_module_name || 'debug_msg';
367 l_errbuf VARCHAR2(1024);
368 BEGIN
369 /*
370 IF (g_debug = 'Y') THEN
371 g_MaxDebugMessages := g_MaxDebugMessages + 1;
372 g_DebugMessages (g_MaxDebugMessages) :=
373 ' (debug) |--'||
374 g_DebugTbl(g_CurDebugProcs).intend_str||
375 ' ('||
376 g_DebugTbl(g_CurDebugProcs).pkg_name||
377 '.'||
378 g_DebugTbl(g_CurDebugProcs).module_name||
379 ') : ' ||
380 p_Message;
381 END IF;
382 */
383 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
384 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, g_module_name||p_ModuleName,p_Message);
385 END IF;
386 EXCEPTION
387 WHEN OTHERS THEN
388 l_errbuf := SQLERRM;
389 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
390
391 END;
392
393 /*
394 PROCEDURE debug_write
395 IS
396 BEGIN
397 IF (g_debug = 'Y') THEN
398 fnd_file.put_line (fnd_file.log, '*****************Debug Messages************************');
399 FOR l_Counter IN 1..g_MaxDebugMessages LOOP
400 fnd_file.put_line(fnd_file.log, g_DebugMessages(l_Counter));
401 END LOOP;
402 fnd_file.put_line (fnd_file.log, '*******************************************************');
403 fnd_file.put_line (fnd_file.log, ' ');
404 END IF;
405 END;
406 */
407
408 PROCEDURE debug_init
409 (
410 p_PkgName IN VARCHAR2,
411 p_ModuleName IN VARCHAR2
412 ) IS
413 l_module_name VARCHAR2(200) := g_module_name || 'debug_init';
414 l_errbuf VARCHAR2(1024);
415 BEGIN
416 /*
417 g_MaxDebugProcs := g_MaxDebugProcs + 1;
418 g_CurDebugProcs := g_CurDebugProcs + 1;
419 g_DebugTbl(0).pkg_name := NULL;
420 g_DebugTbl(0).module_name := NULL;
421 g_DebugTbl(0).intend_str := NULL;
422
423 g_DebugTbl(g_CurDebugProcs).pkg_name := p_PkgName;
424 g_DebugTbl(g_CurDebugProcs).module_name := p_ModuleName;
425 g_DebugTbl(g_CurDebugProcs).intend_str := g_DebugTbl(g_CurDebugProcs-1).intend_str || '--';
426 debug_msg (l_module_name, 'Entering Program');
427 */
428 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
429 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, p_ModuleName,'ENTERING');
430 END IF;
431 EXCEPTION
432 WHEN OTHERS THEN
433 l_errbuf := SQLERRM;
434 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
435
436
437 END;
438
439 PROCEDURE debug_exit
440 (
441 p_ErrorCode IN NUMBER,
442 p_ErrorDesc IN VARCHAR2,
443 p_ErrorLoc IN VARCHAR2
444 ) IS
445 l_module_name VARCHAR2(200) := 'debug_exit';
446 BEGIN
447 debug_msg (l_module_name, 'Returning from Program with Exit Code ='||p_ErrorCode);
448 debug_msg (l_module_name, 'Returning from Program with Exit Desc ='||p_ErrorDesc);
449 debug_msg (l_module_name, 'Returning from Program with Exit Loc ='||p_ErrorLoc);
450 debug_msg (l_module_name, 'Exiting Program');
451 /*
452 g_DebugTbl(g_CurDebugProcs).error_code := p_ErrorCode;
453 g_DebugTbl(g_CurDebugProcs).error_desc := p_ErrorDesc;
454 g_DebugTbl(g_CurDebugProcs).error_loc := p_ErrorLoc;
455
456 g_CurDebugProcs := g_CurDebugProcs - 1;
457 */
458 END;
459
460 --****************************************************************************************--
461 --* Name : init *--
462 --* Type : Procedure *--
463 --* Purpose : This procedure initializes the required global variables *--
464 --* Parameters : None *--
465 --* Global Vars : g_org_id WRITE *--
466 --* : g_sob_id WRITE *--
467 --* : g_debug WRITE *--
468 --* Called from : main *--
469 --* Calls : fnd_profile.value *--
470 --* Tables Used : None *--
471 --* Logic : Initialize Org Id SOB Id and Debug Flag. *--
472 --****************************************************************************************--
473 PROCEDURE init IS
474 l_module_name VARCHAR2(200) := g_module_name || 'init';
475 l_errbuf VARCHAR2(1024);
476 l_ledger_name VARCHAR2(30); --PSKI changes for BA and MOAC Uptake
477 BEGIN
478 -- g_org_id := to_number(fnd_profile.value('ORG_ID'));
479 -- g_sob_id := to_number(fnd_profile.value('GL_SET_OF_BKS_ID'));
480 -- g_org_id := MO_GLOBAL.get_current_org_id; -- PSKI Changes for BA and MOAC Uptake
481 -- MO_UTILS.get_ledger_info(g_org_id,g_sob_id,l_ledger_name); -- PSKI Changes for BA and MOAC Uptake
482 -- g_debug := NVL(UPPER(SUBSTR(FND_PROFILE.VALUE('FV_DEBUG_FLAG'), 1, 1)),'N');
483 NULL;
484 EXCEPTION
485 WHEN OTHERS THEN
486 l_errbuf := SQLERRM;
487 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
488 END;
489
490 --****************************************************************************************--
491 --* Name : write_report_header *--
492 --* Type : Procedure *--
493 --* Purpose : This procedure Writes the Report Header information like batch name *--
494 --* : date submitted etc. *--
495 --* Parameters : p_BatchName IN The name of the batch *--
496 --* : p_DateSubmitted IN The Date batch was submitted *--
497 --* : p_ErrorCode OUT The Error Code *--
498 --* : p_ErrorDesc OUT The Error Description *--
499 --* : p_ErrorLoc OUT The Error Location *--
500 --* Global Vars : g_SUCCESS READ *--
501 --* Called from : process_receipts *--
502 --* Calls : debug_init *--
503 --* : debug_msg *--
504 --* : debug_exit *--
505 --* : output *--
506 --* : error *--
507 --* Tables Used : None *--
508 --* Logic : Call output to write the Batch Name and the Date Submitted in the *--
509 --* : required report format. *--
510 --****************************************************************************************--
511 PROCEDURE write_report_header
512 (
513 p_BatchRec IN fv_ar_batches%ROWTYPE,
514 p_ErrorCode OUT NOCOPY VARCHAR2,
515 p_ErrorDesc OUT NOCOPY VARCHAR2,
516 p_ErrorLoc OUT NOCOPY VARCHAR2
517 ) IS
518 l_module_name VARCHAR2(30) := 'write_report_header';
519 l_WroteErrorHeader BOOLEAN := FALSE;
520 BEGIN
521 p_ErrorCode := g_SUCCESS;
522 p_ErrorDesc := NULL;
523 p_ErrorLoc := NULL;
524
525 debug_init (g_PackageName, l_module_name);
526
527 output (' Batch Name: '||p_BatchRec.batch_name);
528 output (' Date Submitted: '||TO_CHAR(p_BatchRec.last_update_date, 'MM/DD/YYYY HH24:MI:SS'));
529 output (' Receipt Currency: '||p_BatchRec.currency_code);
530 output (' Exchange Rate: '||p_BatchRec.exchange_rate);
531 output ('Exchange Rate Date: '||p_BatchRec.exchange_date);
532 output ('Exchange Rate Type: '||p_BatchRec.exchange_rate_type);
533 output (' ');
534 output (RPAD('*', 100, '*'));
535 output (' ');
536
537 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
538 EXCEPTION
539 WHEN OTHERS THEN
540 p_ErrorCode := g_FAILURE;
541 p_ErrorDesc := SQLERRM;
542 p_ErrorLoc := 'Final Exception';
543 error
544 (
545 p_error_type => p_ErrorCode,
546 p_pgm => l_module_name,
547 p_msg => p_ErrorDesc,
548 p_loc => p_ErrorLoc
549 );
550 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
551 END;
552
553 PROCEDURE del_report_line_for_a_receipt
554 (
555 p_InvoiceNumber IN VARCHAR2,
556 p_ErrorCode OUT NOCOPY VARCHAR2,
557 p_ErrorDesc OUT NOCOPY VARCHAR2,
558 p_ErrorLoc OUT NOCOPY VARCHAR2
559 ) IS
560 l_module_name VARCHAR2(30) := 'del_report_line_for_a_receipt';
561 l_Counter NUMBER;
562 BEGIN
563 p_ErrorCode := g_SUCCESS;
564 p_ErrorDesc := NULL;
565 p_ErrorLoc := NULL;
566
567 debug_init (g_PackageName, l_module_name);
568 FOR l_Counter IN 1..g_OutCashReceipts.total_applications LOOP
569 IF (g_OutReceiptApplications(l_Counter).invoice_number = p_InvoiceNumber) THEN
570 g_OutReceiptApplications(l_Counter).status := 'D';
571 END IF;
572 END LOOP;
573 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
574 EXCEPTION
575 WHEN OTHERS THEN
576 p_ErrorCode := g_FAILURE;
577 p_ErrorDesc := SQLERRM;
578 p_ErrorLoc := 'Final Exception';
579 error
580 (
581 p_error_type => p_ErrorCode,
582 p_pgm => l_module_name,
583 p_msg => p_ErrorDesc,
584 p_loc => p_ErrorLoc
585 );
586 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
587 END del_report_line_for_a_receipt;
588
589 --****************************************************************************************--
590 --* Name : write_report_for_a_receipt *--
591 --* Type : Procedure *--
592 --* Purpose : This procedure Writes the detailed Report for a particular receipt *--
593 --* Parameters : p_ErrorCode OUT The Error Code *--
594 --* : p_ErrorDesc OUT The Error Description *--
595 --* : p_ErrorLoc OUT The Error Location *--
596 --* Global Vars : g_SUCCESS READ *--
597 --* : g_OutCashReceipts READ *--
598 --* : g_OutInvoiceDebitMemos READ *--
599 --* : g_OutErrorInfo READ *--
600 --* Called from : process_receipts *--
601 --* Calls : debug_init *--
602 --* : debug_exit *--
603 --* : output *--
604 --* Tables Used : None *--
605 --* Logic : Write the Receipt Information from g_OutCashReceipts *--
606 --* : Write the Debit Memo and Invoice Applications from *--
607 --* : g_OutInvoiceDebitMemos *--
608 --* : Write the Error Information from g_OutErrorInfo *--
609 --****************************************************************************************--
610 PROCEDURE write_report_for_a_receipt
611 (
612 p_ErrorCode OUT NOCOPY VARCHAR2,
613 p_ErrorDesc OUT NOCOPY VARCHAR2,
614 p_ErrorLoc OUT NOCOPY VARCHAR2
615 ) IS
616 l_module_name VARCHAR2(30) := 'write_report_for_a_receipt';
617 l_WroteErrorHeader BOOLEAN := FALSE;
618 BEGIN
619 p_ErrorCode := g_SUCCESS;
620 p_ErrorDesc := NULL;
621 p_ErrorLoc := NULL;
622
623 debug_init (g_PackageName, l_module_name);
624
625 ----------------------------------------------------------------------
626 -- Write Receipt Details --
627 ----------------------------------------------------------------------
628 output (' Receipt Number: '||g_OutCashReceipts.receipt_number);
629 output (' Customer: '||g_OutCashReceipts.customer_name);
630 output (' Receipt Amount: '||g_OutCashReceipts.receipt_amount);
631 output (' ');
632
633 output (' '||RPAD('=', 24, '='));
634 output (' Receipt Application');
635 output (' ');
636 output (' -----------------------------------------------------------------------');
637 output (' | Applied Against | Line Number | Invoice Type | Amount Applied |');
638 output (' ----------------------------------------------------------------------');
639 output (' | | | | |');
640 ----------------------------------------------------------------------
641 -- Write Receipt Application Details --
642 ----------------------------------------------------------------------
643 FOR l_Counter IN 1..g_OutCashReceipts.total_applications LOOP
644 IF (NVL(g_OutReceiptApplications(l_Counter).status, 'A') = 'A') THEN
645 output (' |'||
646 RPAD(SUBSTR(g_OutReceiptApplications(l_Counter).invoice_number, 1, 19), 19, ' ')||
647 '|'||
648 RPAD(SUBSTR(NVL(TO_CHAR(g_OutReceiptApplications(l_Counter).line_number), ' '), 1, 13), 13, ' ')||
649 '|'||
650 RPAD(SUBSTR(g_OutReceiptApplications(l_Counter).invoice_type, 1, 17), 17, ' ')||
651 '|'||
652 TO_CHAR(g_OutReceiptApplications(l_Counter).applied_amount, '99999999999990.00')||
653 '|');
654 END IF;
655 END LOOP;
656
657 output (' | | | | |');
658 output (' ----------------------------------------------------------------------');
659 output (' ');
660 output (' ');
661
662 ----------------------------------------------------------------------
663 -- Write Error Information --
664 ----------------------------------------------------------------------
665 FOR l_Counter IN 1..g_OutCashReceipts.total_errors LOOP
666 IF (l_WroteErrorHeader = FALSE) THEN
667 output (' '||RPAD('=', 24, '='));
668 output (' Error Messages');
669 output (' ');
670 l_WroteErrorHeader := TRUE;
671 END IF;
672
673 output (' '||
674 l_Counter ||
675 '. ' ||
676 g_OutErrorInfo(l_Counter).error_desc);
677 END LOOP;
678 output (' ');
679 output (RPAD('*', 100, '*'));
680 output (' ');
681
682 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
683 EXCEPTION
684 WHEN OTHERS THEN
685 p_ErrorCode := g_FAILURE;
686 p_ErrorDesc := SQLERRM;
687 p_ErrorLoc := 'Final Exception';
688 error
689 (
690 p_error_type => p_ErrorCode,
691 p_pgm => l_module_name,
692 p_msg => p_ErrorDesc,
693 p_loc => p_ErrorLoc
694 );
695 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
696 END write_report_for_a_receipt;
697
698 --****************************************************************************************--
699 --* Name : dump_ar_batch *--
700 --* Type : Procedure *--
701 --* Purpose : Writes the data in the record for ar_batches using debug_msg *--
702 --* Parameters : p_ARBatchRec IN ar_batches%ROWTYPE *--
703 --* Global Vars : None *--
704 --* Called from : insert_ar_batch *--
705 --* Calls : debug_msg *--
706 --* Tables Used : None *--
707 --* Logic : Call debug_msg and write the contents of p_ARBatchRec. *--
708 --****************************************************************************************--
709 PROCEDURE dump_ar_batch
710 (
711 p_ARBatchRec IN ar_batches%ROWTYPE
712 ) IS
713 l_module_name VARCHAR2(30) := 'dump_ar_batch';
714 BEGIN
715 ----------------------------------------------------------------------
716 -- For Debug purposes dump the contents of ar_batches record --
717 ----------------------------------------------------------------------
718 debug_msg (l_module_name, '====> Contents of AR_BATCHES record <====');
719 debug_msg (l_module_name, 'batch_id => '|| p_ARBatchRec.batch_id);
720 debug_msg (l_module_name, 'last_updated_by => '|| p_ARBatchRec.last_updated_by);
721 debug_msg (l_module_name, 'last_update_date => '|| TO_CHAR(p_ARBatchRec.last_update_date, 'MM/DD/YYYY HH24:MI:SS'));
722 debug_msg (l_module_name, 'last_update_login => '|| p_ARBatchRec.last_update_login);
723 debug_msg (l_module_name, 'created_by => '|| p_ARBatchRec.created_by);
724 debug_msg (l_module_name, 'creation_date => '|| TO_CHAR(p_ARBatchRec.creation_date, 'MM/DD/YYYY HH24:MI:SS'));
725 debug_msg (l_module_name, 'name => '|| p_ARBatchRec.name);
726 debug_msg (l_module_name, 'batch_date => '|| TO_CHAR(p_ARBatchRec.batch_date, 'MM/DD/YYYY HH24:MI:SS'));
727 debug_msg (l_module_name, 'gl_date => '|| TO_CHAR(p_ARBatchRec.gl_date, 'MM/DD/YYYY HH24:MI:SS'));
728 debug_msg (l_module_name, 'status => '|| p_ARBatchRec.status);
729 debug_msg (l_module_name, 'deposit_date => '|| TO_CHAR(p_ARBatchRec.deposit_date, 'MM/DD/YYYY HH24:MI:SS'));
730 debug_msg (l_module_name, 'type => '|| p_ARBatchRec.type);
731 debug_msg (l_module_name, 'batch_source_id => '|| p_ARBatchRec.batch_source_id);
732 debug_msg (l_module_name, 'control_count => '|| p_ARBatchRec.control_count);
733 debug_msg (l_module_name, 'control_amount => '|| p_ARBatchRec.control_amount);
734 debug_msg (l_module_name, 'batch_applied_status => '|| p_ARBatchRec.batch_applied_status);
735 debug_msg (l_module_name, 'currency_code => '|| p_ARBatchRec.currency_code);
736 debug_msg (l_module_name, 'exchange_rate => '|| p_ARBatchRec.exchange_rate);
737 debug_msg (l_module_name, 'exchange_date => '|| TO_CHAR(p_ARBatchRec.exchange_date, 'MM/DD/YYYY HH24:MI:SS'));
738 debug_msg (l_module_name, 'exchange_rate_type => '|| p_ARBatchRec.exchange_rate_type);
739 debug_msg (l_module_name, 'attribute_category => '|| p_ARBatchRec.attribute_category);
740 debug_msg (l_module_name, 'attribute1 => '|| p_ARBatchRec.attribute1);
741 debug_msg (l_module_name, 'attribute2 => '|| p_ARBatchRec.attribute2);
742 debug_msg (l_module_name, 'attribute3 => '|| p_ARBatchRec.attribute3);
743 debug_msg (l_module_name, 'attribute4 => '|| p_ARBatchRec.attribute4);
744 debug_msg (l_module_name, 'attribute5 => '|| p_ARBatchRec.attribute5);
745 debug_msg (l_module_name, 'attribute6 => '|| p_ARBatchRec.attribute6);
746 debug_msg (l_module_name, 'attribute7 => '|| p_ARBatchRec.attribute7);
747 debug_msg (l_module_name, 'attribute8 => '|| p_ARBatchRec.attribute8);
748 debug_msg (l_module_name, 'attribute9 => '|| p_ARBatchRec.attribute9);
749 debug_msg (l_module_name, 'attribute10 => '|| p_ARBatchRec.attribute10);
750 debug_msg (l_module_name, 'attribute11 => '|| p_ARBatchRec.attribute11);
751 debug_msg (l_module_name, 'attribute12 => '|| p_ARBatchRec.attribute12);
752 debug_msg (l_module_name, 'attribute13 => '|| p_ARBatchRec.attribute13);
753 debug_msg (l_module_name, 'attribute14 => '|| p_ARBatchRec.attribute14);
754 debug_msg (l_module_name, 'attribute15 => '|| p_ARBatchRec.attribute15);
755 debug_msg (l_module_name, 'receipt_method_id => '|| p_ARBatchRec.receipt_method_id);
756 debug_msg (l_module_name, 'remittance_bank_account_id => '|| p_ARBatchRec.remit_bank_acct_use_id); --PSKI changes for BA and MOAC Uptake
757 debug_msg (l_module_name, 'receipt_class_id => '|| p_ARBatchRec.receipt_class_id);
758 debug_msg (l_module_name, 'set_of_books_id => '|| p_ARBatchRec.set_of_books_id);
759 debug_msg (l_module_name, 'org_id => '|| p_ARBatchRec.org_id);
760 END dump_ar_batch;
761
762 --****************************************************************************************--
763 --* Name : insert_ar_batch *--
764 --* Type : Procedure *--
765 --* Purpose : This procedure inserts a record into the table ar_batches *--
766 --* Parameters : p_BatchRec IN fv_ar_batches%ROWTYPE *--
767 --* : p_ErrorCode OUT The Error Code *--
768 --* : p_ErrorDesc OUT The Error Description *--
769 --* : p_ErrorLoc OUT The Error Location *--
770 --* Global Vars : g_SUCCESS READ *--
771 --* Called from : process_receipts *--
772 --* Calls : debug_init *--
773 --* : debug_exit *--
774 --* : dump_ar_batch *--
775 --* : log_msg *--
776 --* Tables Used : ar_batches (VIEW) INSERT *--
777 --* Logic : Copy the FV_AR_BATCHES record into AR_BATCHES record. *--
778 --* : Call dump_ar_batch to display the AR_BATCHES record in debug mode *--
779 --* : Insert the AR_BATCHES record into the table ar_batches *--
780 --****************************************************************************************--
781 PROCEDURE insert_ar_batch
782 (
783 p_BatchRec IN fv_ar_batches%ROWTYPE,
784 p_ErrorCode OUT NOCOPY VARCHAR2,
785 p_ErrorDesc OUT NOCOPY VARCHAR2,
786 p_ErrorLoc OUT NOCOPY VARCHAR2
787 ) IS
788 l_module_name VARCHAR2(30) := 'insert_ar_batch';
789
790 l_ARBatchRec ar_batches%ROWTYPE;
791
792 BEGIN
793 p_ErrorCode := g_SUCCESS;
794 p_ErrorDesc := NULL;
795 p_ErrorLoc := NULL;
796
797 debug_init (g_PackageName, l_module_name);
798
799 log_msg(l_module_name,'Creating Receipt Batch '||p_BatchRec.batch_name);
800
801
802 ----------------------------------------------------------------------
803 -- Copy the fv_ar_batches record into ar_batches record --
804 ----------------------------------------------------------------------
805
806 l_ARBatchRec.batch_id := p_BatchRec.batch_id;
807 l_ARBatchRec.last_updated_by := p_BatchRec.last_updated_by;
808 l_ARBatchRec.last_update_date := p_BatchRec.last_update_date;
809 l_ARBatchRec.last_update_login := p_BatchRec.last_update_login;
810 l_ARBatchRec.created_by := p_BatchRec.created_by;
811 l_ARBatchRec.creation_date := p_BatchRec.creation_date;
812 l_ARBatchRec.name := p_BatchRec.batch_name;
813 l_ARBatchRec.batch_date := trunc(SYSDATE); --for Bug 5299453
814 l_ARBatchRec.gl_date := p_BatchRec.gl_date;
815 l_ARBatchRec.status := 'CL';
816 l_ARBatchRec.deposit_date := p_BatchRec.deposit_date;
817 l_ARBatchRec.type := 'MANUAL';
818 l_ARBatchRec.batch_source_id := p_BatchRec.batch_source_id;
819 l_ARBatchRec.control_count := p_BatchRec.batch_count;
820 l_ARBatchRec.control_amount := p_BatchRec.batch_amount;
821 l_ARBatchRec.batch_applied_status := 'PROCESSED';
822 l_ARBatchRec.currency_code := p_BatchRec.currency_code;
823 l_ARBatchRec.exchange_rate := p_BatchRec.exchange_rate;
824 l_ARBatchRec.exchange_date := p_BatchRec.exchange_date;
825 l_ARBatchRec.exchange_rate_type := p_BatchRec.exchange_rate_type;
826 l_ARBatchRec.attribute_category := p_BatchRec.attribute_category;
827 l_ARBatchRec.attribute1 := p_BatchRec.attribute1;
828 l_ARBatchRec.attribute2 := p_BatchRec.attribute2;
829 l_ARBatchRec.attribute3 := p_BatchRec.attribute3;
830 l_ARBatchRec.attribute4 := p_BatchRec.attribute4;
831 l_ARBatchRec.attribute5 := p_BatchRec.attribute5;
832 l_ARBatchRec.attribute6 := p_BatchRec.attribute6;
833 l_ARBatchRec.attribute7 := p_BatchRec.attribute7;
834 l_ARBatchRec.attribute8 := p_BatchRec.attribute8;
835 l_ARBatchRec.attribute9 := p_BatchRec.attribute9;
836 l_ARBatchRec.attribute10 := p_BatchRec.attribute10;
837 l_ARBatchRec.attribute11 := p_BatchRec.attribute11;
838 l_ARBatchRec.attribute12 := p_BatchRec.attribute12;
839 l_ARBatchRec.attribute13 := p_BatchRec.attribute13;
840 l_ARBatchRec.attribute14 := p_BatchRec.attribute14;
841 l_ARBatchRec.attribute15 := p_BatchRec.attribute15;
842 l_ARBatchRec.receipt_method_id := p_BatchRec.receipt_method_id;
843 l_ARBatchRec.remit_bank_acct_use_id := p_BatchRec.ce_bank_acct_use_id; --PSKI changes for BA and MOAC Uptake
844 l_ARBatchRec.receipt_class_id := p_BatchRec.receipt_class_id;
845 l_ARBatchRec.set_of_books_id := p_BatchRec.set_of_books_id;
846 l_ARBatchRec.org_id := p_BatchRec.org_id;
847
848 ----------------------------------------------------------------------
849 -- Call dump_ar_batch to display ar_batches record in debug mode --
850 ----------------------------------------------------------------------
851 dump_ar_batch (l_ARBatchRec);
852
853 ----------------------------------------------------------------------
854 -- Insert the ar_batches record into ar_batches table --
855 ----------------------------------------------------------------------
856 debug_msg (l_module_name, 'Inserting data into ar_batches');
857 INSERT INTO ar_batches
858 (
859 batch_id,
860 last_updated_by,
861 last_update_date,
862 last_update_login,
863 created_by,
864 creation_date,
865 name,
866 batch_date,
867 gl_date,
868 status,
869 deposit_date,
870 type,
871 batch_source_id,
872 control_count,
873 control_amount,
874 batch_applied_status,
875 currency_code,
876 exchange_rate,
877 exchange_date,
878 exchange_rate_type,
879 attribute_category,
880 attribute1,
881 attribute2,
882 attribute3,
883 attribute4,
884 attribute5,
885 attribute6,
886 attribute7,
887 attribute8,
888 attribute9,
889 attribute10,
890 attribute11,
891 attribute12,
892 attribute13,
893 attribute14,
894 attribute15,
895 receipt_method_id,
896 remit_bank_acct_use_id, --PSKI changes for BA and MOAC Uptake
897 receipt_class_id,
898 set_of_books_id,
899 org_id
900 )
901 VALUES
902 (
903 l_ARBatchRec.batch_id,
904 l_ARBatchRec.last_updated_by,
905 l_ARBatchRec.last_update_date,
906 l_ARBatchRec.last_update_login,
907 l_ARBatchRec.created_by,
908 l_ARBatchRec.creation_date,
909 l_ARBatchRec.name,
910 l_ARBatchRec.batch_date,
911 l_ARBatchRec.gl_date,
912 l_ARBatchRec.status,
913 l_ARBatchRec.deposit_date,
914 l_ARBatchRec.type,
915 l_ARBatchRec.batch_source_id,
916 l_ARBatchRec.control_count,
917 l_ARBatchRec.control_amount,
918 l_ARBatchRec.batch_applied_status,
919 l_ARBatchRec.currency_code,
920 l_ARBatchRec.exchange_rate,
921 l_ARBatchRec.exchange_date,
922 l_ARBatchRec.exchange_rate_type,
923 l_ARBatchRec.attribute_category,
924 l_ARBatchRec.attribute1,
925 l_ARBatchRec.attribute2,
926 l_ARBatchRec.attribute3,
927 l_ARBatchRec.attribute4,
928 l_ARBatchRec.attribute5,
929 l_ARBatchRec.attribute6,
930 l_ARBatchRec.attribute7,
931 l_ARBatchRec.attribute8,
932 l_ARBatchRec.attribute9,
933 l_ARBatchRec.attribute10,
934 l_ARBatchRec.attribute11,
935 l_ARBatchRec.attribute12,
936 l_ARBatchRec.attribute13,
937 l_ARBatchRec.attribute14,
938 l_ARBatchRec.attribute15,
939 l_ARBatchRec.receipt_method_id,
940 l_ARBatchRec.remit_bank_acct_use_id, --PSKI changes for BA and MOAC Uptake
941 l_ARBatchRec.receipt_class_id,
942 l_ARBatchRec.set_of_books_id,
943 l_ARBatchRec.org_id
944 );
945
946 log_msg(l_module_name,'Successfully created Receipt Batch '||p_BatchRec.batch_name);
947 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
948 EXCEPTION
949 WHEN OTHERS THEN
950 p_ErrorCode := g_FAILURE;
951 p_ErrorDesc := SQLERRM;
952 p_ErrorLoc := 'Final Exception';
953 error
954 (
955 p_error_type => p_ErrorCode,
956 p_pgm => l_module_name,
957 p_msg => p_ErrorDesc,
958 p_loc => p_ErrorLoc
959 );
960 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
961 END insert_ar_batch;
962
963 --****************************************************************************************--
964 --* Name : apply_on_account *--
965 --* Type : Procedure *--
966 --* Purpose : Calls the API ar_receipt_api_pub.Apply_on_account to Apply the *--
967 --* : receipt amount to On Account for the customer *--
968 --* Parameters : p_OnAccountRec IN OnAccountRecType *--
969 --* : p_ErrorCode OUT The Error Code *--
970 --* : p_ErrorDesc OUT The Error Description *--
971 --* : p_ErrorLoc OUT The Error Location *--
972 --* Global Vars : g_SUCCESS READ *--
973 --* : g_OutErrorInfo WRITE *--
974 --* : g_OutCashReceipts WRITE, READ *--
975 --* Called from : process_receipts *--
976 --* Calls : ar_receipt_api_pub.Apply_on_account *--
977 --* : fnd_msg_pub.get *--
978 --* : debug_msg *--
979 --* : debug_init *--
980 --* : debug_exit *--
981 --* : error *--
982 --* Tables Used : None *--
983 --* Logic : Use the values in p_OnAccountRec and use them as parameters to call *--
984 --* : ar_receipt_api_pub.Apply_on_account *--
985 --* : If there is an error the return code in x_return_status will not be S*--
986 --* : If there is an error, check the contents of x_msg_count *--
987 --* : If x_msg_count is 1 then the error message is obtained from *--
988 --* : x_msg_data *--
989 --* : If x_msg_count is > 1 then call fnd_msg_pub.get x_msg_count times to *--
990 --* : get the error messages. *--
991 --****************************************************************************************--
992 PROCEDURE apply_on_account
993 (
994 p_OnAccountRec IN OnAccountRecType,
995 p_ErrorCode OUT NOCOPY VARCHAR2,
996 p_ErrorDesc OUT NOCOPY VARCHAR2,
997 p_ErrorLoc OUT NOCOPY VARCHAR2
998 ) IS
999 l_module_name VARCHAR2(30) := 'apply_on_account ';
1000 l_api_version CONSTANT NUMBER := 1.0;
1001 l_ReturnStatus VARCHAR2(10);
1002 l_MessageCount NUMBER;
1003 l_MessageData VARCHAR2(1024);
1004 l_CashReceiptId NUMBER;
1005
1006 BEGIN
1007 p_ErrorCode := g_SUCCESS;
1008 p_ErrorDesc := NULL;
1009 p_ErrorLoc := NULL;
1010
1011 debug_init (g_PackageName, l_module_name);
1012
1013 ----------------------------------------------------------------------
1014 -- Print contents of P_OnAccountRec for debug purposes --
1015 ----------------------------------------------------------------------
1016 debug_msg (l_module_name, 'Calling API ar_receipt_api_pub.Apply_on_account with the following paraeteres');
1017 debug_msg (l_module_name, 'p_api_version => '||l_api_version);
1018 debug_msg (l_module_name, 'p_cash_receipt_id => '||p_OnAccountRec.cash_receipt_id);
1019 debug_msg (l_module_name, 'p_receipt_number => '||p_OnAccountRec.receipt_number);
1020 debug_msg (l_module_name, 'p_amount_applied => '||p_OnAccountRec.amount_applied);
1021 debug_msg (l_module_name, 'p_apply_date => '||TO_CHAR(p_OnAccountRec.apply_date, 'MM/DD/YYYY HH24:MI:SS'));
1022 debug_msg (l_module_name, 'p_apply_gl_date => '||TO_CHAR(p_OnAccountRec.apply_gl_date, 'MM/DD/YYYY HH24:MI:SS'));
1023 -- debug_msg (l_module_name, 'p_ussgl_transaction_code => '||p_OnAccountRec.ussgl_transaction_code);
1024 debug_msg (l_module_name, 'p_comments => '||p_OnAccountRec.comments);
1025
1026 ----------------------------------------------------------------------
1027 -- Call API ar_receipt_api_pub.Apply_on_account for applying --
1028 -- p_OnAccountRec.amount_applied towards On account for the --
1029 -- customer --
1030 ----------------------------------------------------------------------
1031 log_msg (l_module_name,'Applying On Account for an amount of '||p_OnAccountRec.amount_applied);
1032 ar_receipt_api_pub.Apply_on_account
1033 (
1034 p_api_version => l_api_version,
1035 p_init_msg_list => FND_API.G_TRUE,
1036 p_commit => FND_API.G_FALSE,
1037 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1038 x_return_status => l_ReturnStatus,
1039 x_msg_count => l_MessageCount,
1040 x_msg_data => l_MessageData,
1041 p_cash_receipt_id => p_OnAccountRec.cash_receipt_id,
1042 p_receipt_number => p_OnAccountRec.receipt_number,
1043 p_amount_applied => p_OnAccountRec.amount_applied,
1044 p_apply_date => p_OnAccountRec.apply_date,
1045 p_apply_gl_date => p_OnAccountRec.apply_gl_date,
1046 -- p_ussgl_transaction_code => p_OnAccountRec.ussgl_transaction_code,
1047 p_ussgl_transaction_code => null,
1048 p_attribute_rec => p_OnAccountRec.attribute_rec,
1049 p_global_attribute_rec => p_OnAccountRec.global_attribute_rec,
1050 p_comments => p_OnAccountRec.comments
1051 );
1052 debug_msg (l_module_name, 'After Calling API ar_receipt_api_pub.Apply_on_account (Return Values)');
1053 debug_msg (l_module_name, 'x_return_status => '||l_ReturnStatus);
1054 debug_msg (l_module_name, 'x_msg_count => '||l_MessageCount);
1055 debug_msg (l_module_name, 'x_msg_data => '||l_MessageData);
1056
1057 IF (l_ReturnStatus <> 'S') THEN
1058 log_msg (l_module_name,'Could not apply On Account');
1059 ----------------------------------------------------------------------
1060 -- There is an error --
1061 ----------------------------------------------------------------------
1062 p_ErrorCode := g_FAILURE;
1063 p_ErrorLoc := 'After Calling API ar_receipt_api_pub.Apply_on_account.';
1064
1065 IF (l_MessageCount = 1) THEN
1066 ----------------------------------------------------------------------
1067 -- Message Count is 1, hence the error message is in x_msg_data --
1068 ----------------------------------------------------------------------
1069 p_ErrorDesc := l_MessageData;
1070 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1071 g_OutErrorInfo(g_OutCashReceipts.total_errors).error_desc := l_MessageData;
1072 error
1073 (
1074 p_error_type => p_ErrorCode,
1075 p_pgm => l_module_name,
1076 p_msg => p_ErrorDesc,
1077 p_loc => p_ErrorLoc
1078 );
1079 debug_msg (l_module_name, 'Error Message is :'||l_MessageData);
1080 ELSE
1081 ----------------------------------------------------------------------
1082 -- Message Count is > 1, hence loop for x_msg_count times and call --
1083 -- fnd_msg_pub.get to get the error messages --
1084 ----------------------------------------------------------------------
1085 FOR l_Counter IN 1..l_MessageCount LOOP
1086 l_MessageData := fnd_msg_pub.get (p_encoded => 'F');
1087 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1088 g_OutErrorInfo(g_OutCashReceipts.total_errors).error_desc := l_MessageData;
1089 error
1090 (
1091 p_error_type => p_ErrorCode,
1092 p_pgm => l_module_name,
1093 p_msg => p_ErrorDesc,
1094 p_loc => p_ErrorLoc
1095 );
1096 debug_msg (l_module_name, 'Error Message is :'||l_MessageData);
1097 END LOOP;
1098 p_ErrorDesc := 'Look at the Report to find the error';
1099 END IF;
1100 ELSE
1101 log_msg (l_module_name,'On Account Application Successfull');
1102 END IF;
1103
1104 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 p_ErrorCode := g_FAILURE;
1108 p_ErrorDesc := SQLERRM;
1109 p_ErrorLoc := 'Final Exception';
1110 error
1111 (
1112 p_error_type => p_ErrorCode,
1113 p_pgm => l_module_name,
1114 p_msg => p_ErrorDesc,
1115 p_loc => p_ErrorLoc
1116 );
1117 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1118 END apply_on_account ;
1119
1120 --****************************************************************************************--
1121 --* Name : unapply_if_already_applied *--
1122 --* Type : Procedure *--
1123 --* Purpose : Checks to see if the debit memo was already applied to a cash receipt*--
1124 --* : If so the application is reversed and the amount is returned. *--
1125 --* Parameters : p_ReceiptId IN Cash Receipt *--
1126 --* : p_InvoiceId IN Invoice Id to be unapplied *--
1127 --* : p_UnAppliedAmount OUT The Unapplied amount is returned *--
1128 --* : p_ErrorCode OUT The Error Code *--
1129 --* : p_ErrorDesc OUT The Error Description *--
1130 --* : p_ErrorLoc OUT The Error Location *--
1131 --* Global Vars : g_SUCCESS READ *--
1132 --* : g_OutErrorInfo WRITE *--
1133 --* : g_OutCashReceipts WRITE, READ *--
1134 --* Called from : pay_debit_memos *--
1135 --* Calls : ar_receipt_api_pub.unapply *--
1136 --* : fnd_msg_pub.get *--
1137 --* : debug_msg *--
1138 --* : debug_init *--
1139 --* : debug_exit *--
1140 --* : error *--
1141 --* Tables Used : ar_receivable_applications SELECT *--
1142 --* Logic : For the specific Cash Receipt Id and Invoice Id see if there is any *--
1143 --* : data in the table ar_receivable_applications. *--
1144 --* : If not exit. *--
1145 --* : If there is any data in ar_receivable_applications, then call *--
1146 --* : ar_receipt_api_pub.unapply to unapply the invoice *--
1147 --* : If there is an error the return code in x_return_status will not be S*--
1148 --* : If there is an error, check the contents of x_msg_count *--
1149 --* : If x_msg_count is 1 then the error message is obtained from *--
1150 --* : x_msg_data *--
1151 --* : If x_msg_count is > 1 then call fnd_msg_pub.get x_msg_count times to *--
1152 --* : get the error messages. *--
1153 --* : Mark the row as erased in the report table. *--
1154 --* : Return the unapplied amount. *--
1155 --****************************************************************************************--
1156 PROCEDURE unapply_if_already_applied
1157 (
1158 p_ReceiptId IN NUMBER,
1159 p_InvoiceId IN NUMBER,
1160 p_UnAppliedAmount OUT NOCOPY NUMBER,
1161 p_ErrorCode OUT NOCOPY VARCHAR2,
1162 p_ErrorDesc OUT NOCOPY VARCHAR2,
1163 p_ErrorLoc OUT NOCOPY VARCHAR2
1164 ) IS
1165 l_module_name VARCHAR2(30) := 'unapply_if_already_applied';
1166 l_api_version CONSTANT NUMBER := 1.0;
1167 l_ReturnStatus VARCHAR2(10);
1168 l_MessageCount NUMBER;
1169 l_MessageData VARCHAR2(1024);
1170 l_PreviousAmount NUMBER;
1171 l_ReceivableApplicationId NUMBER;
1172
1173 l_Count NUMBER;
1174
1175 BEGIN
1176 p_ErrorCode := g_SUCCESS;
1177 p_ErrorDesc := NULL;
1178 p_ErrorLoc := NULL;
1179
1180 debug_init (g_PackageName, l_module_name);
1181 debug_msg (l_module_name, 'p_Receiptid = '||p_Receiptid);
1182 debug_msg (l_module_name, 'p_InvoiceId = '||p_InvoiceId);
1183
1184 BEGIN
1185 SELECT ara.amount_applied,
1186 ara.receivable_application_id
1187 INTO l_PreviousAmount,
1188 l_ReceivableApplicationId
1189 FROM ar_receivable_applications ara
1190 WHERE ara.cash_receipt_id = p_Receiptid
1191 AND ara.applied_customer_trx_id = p_InvoiceId;
1192 EXCEPTION
1193 WHEN NO_DATA_FOUND THEN
1194 l_PreviousAmount := 0;
1195 debug_msg (l_module_name, 'No Data Found');
1196 WHEN OTHERS THEN
1197 p_ErrorCode := g_FAILURE;
1198 p_ErrorDesc := SQLERRM;
1199 p_ErrorLoc := 'SELECT ar_receivable_applications';
1200 error
1201 (
1202 p_error_type => p_ErrorCode,
1203 p_pgm => l_module_name,
1204 p_msg => p_ErrorDesc,
1205 p_loc => p_ErrorLoc
1206 );
1207 debug_msg (l_module_name, p_ErrorDesc||'at location'||p_ErrorLoc);
1208 END;
1209
1210 debug_msg (l_module_name, 'l_ReceivableApplicationId = '||l_ReceivableApplicationId);
1211 debug_msg (l_module_name, 'l_PreviousAmount = '||l_PreviousAmount);
1212
1213 IF (p_ErrorCode = g_SUCCESS) THEN
1214 IF (l_PreviousAmount <> 0) THEN
1215 ar_receipt_api_pub.unapply
1216 (
1217 p_api_version => l_api_version,
1218 p_init_msg_list => FND_API.G_TRUE,
1219 p_commit => FND_API.G_FALSE,
1220 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1221 x_return_status => l_ReturnStatus,
1222 x_msg_count => l_MessageCount,
1223 x_msg_data => l_MessageData,
1224 p_receipt_number => NULL,
1225 p_cash_receipt_id => NULL,
1226 p_trx_number => NULL,
1227 p_customer_trx_id => NULL,
1228 p_installment => NULL,
1229 p_applied_payment_schedule_id => NULL,
1230 p_receivable_application_id => l_ReceivableApplicationId,
1231 p_reversal_gl_date => NULL,
1232 p_called_from => NULL,
1233 p_cancel_claim_flag => NULL
1234 );
1235
1236 debug_msg (l_module_name, 'After Calling API ar_receipt_api_pub.Unapply (Return Values)');
1237 debug_msg (l_module_name, 'x_return_status => '||l_ReturnStatus);
1238 debug_msg (l_module_name, 'x_msg_count => '||l_MessageCount);
1239 debug_msg (l_module_name, 'x_msg_data => '||l_MessageData);
1240
1241 IF (l_ReturnStatus <> 'S') THEN
1242 ----------------------------------------------------------------------
1243 -- There is an error --
1244 ----------------------------------------------------------------------
1245 p_ErrorCode := g_FAILURE;
1246 p_ErrorLoc := 'After Calling API ar_receipt_api_pub.UnApply.';
1247
1248 IF (l_MessageCount = 1) THEN
1249 ----------------------------------------------------------------------
1250 -- Message Count is 1, hence the error message is in x_msg_data --
1251 ----------------------------------------------------------------------
1252 p_ErrorDesc := l_MessageData;
1253 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1254 g_OutErrorInfo(g_OutCashReceipts.total_errors).error_desc := l_MessageData;
1255 error
1256 (
1257 p_error_type => p_ErrorCode,
1258 p_pgm => l_module_name,
1259 p_msg => p_ErrorDesc,
1260 p_loc => p_ErrorLoc
1261 );
1262 debug_msg (l_module_name, 'Error Message is :'||l_MessageData);
1263 ELSE
1264 ----------------------------------------------------------------------
1265 -- Message Count is > 1, hence loop for x_msg_count times and call --
1266 -- fnd_msg_pub.get to get the error messages --
1267 ----------------------------------------------------------------------
1268 FOR l_Counter IN 1..l_MessageCount LOOP
1269 l_MessageData := fnd_msg_pub.get (p_encoded => 'F');
1270 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1271 g_OutErrorInfo(g_OutCashReceipts.total_errors).error_desc := l_MessageData;
1272 debug_msg (l_module_name, 'Error Message is :'||l_MessageData);
1273 error
1274 (
1275 p_error_type => p_ErrorCode,
1276 p_pgm => l_module_name,
1277 p_msg => p_ErrorDesc,
1278 p_loc => p_ErrorLoc
1279 );
1280 END LOOP;
1281 p_ErrorDesc := 'Look at the Report to find the error';
1282 END IF;
1283 END IF;
1284 END IF;
1285 END IF;
1286
1287 p_UnAppliedAmount := l_PreviousAmount;
1288
1289 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1290 EXCEPTION
1291 WHEN OTHERS THEN
1292 p_ErrorCode := g_FAILURE;
1293 p_ErrorDesc := SQLERRM;
1294 p_ErrorLoc := 'Final Exception';
1295 error
1296 (
1297 p_error_type => p_ErrorCode,
1298 p_pgm => l_module_name,
1299 p_msg => p_ErrorDesc,
1300 p_loc => p_ErrorLoc
1301 );
1302 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1303 END unapply_if_already_applied;
1304
1305 --****************************************************************************************--
1306 --* Name : CreateCashReceipt *--
1307 --* Type : Procedure *--
1308 --* Purpose : This procedure calls API ar_receipt_api_pub.Apply to apply the *--
1309 --* : receipt against an invoice or debit memo. *--
1310 --* Parameters : p_ApplyCashRec IN ApplyCashRecType *--
1311 --* : p_ErrorCode OUT The Error Code *--
1312 --* : p_ErrorDesc OUT The Error Description *--
1313 --* : p_ErrorLoc OUT The Error Location *--
1314 --* Global Vars : g_SUCCESS READ *--
1315 --* : g_OutErrorInfo WRITE *--
1316 --* : g_OutCashReceipts WRITE, READ *--
1317 --* Called from : pay_the_invoice *--
1318 --* : pay_debit_memos *--
1319 --* Calls : ar_receipt_api_pub.Apply *--
1320 --* : fnd_msg_pub.get *--
1321 --* : debug_msg *--
1322 --* : debug_init *--
1323 --* : debug_exit *--
1324 --* Tables Used : None *--
1325 --* Logic : Use the values in p_OnAccountRec and use them as parameters to call *--
1326 --* : ar_receipt_api_pub.Apply *--
1327 --* : If there is an error the return code in x_return_status will not be S*--
1328 --* : If there is an error, check the contents of x_msg_count *--
1329 --* : If x_msg_count is 1 then the error message is obtained from *--
1330 --* : x_msg_data *--
1331 --* : If x_msg_count is > 1 then call fnd_msg_pub.get x_msg_count times to *--
1332 --* : get the error messages. *--
1333 --****************************************************************************************--
1334 PROCEDURE apply_cash_receipt
1335 (
1336 p_ApplyCashRec IN ApplyCashRecType,
1337 p_ErrorCode OUT NOCOPY VARCHAR2,
1338 p_ErrorDesc OUT NOCOPY VARCHAR2,
1339 p_ErrorLoc OUT NOCOPY VARCHAR2
1340 ) IS
1341 l_module_name VARCHAR2(30) := 'apply_cash_receipt';
1342 l_api_version CONSTANT NUMBER := 1.0;
1343 l_ReturnStatus VARCHAR2(10);
1344 l_MessageCount NUMBER;
1345 l_MessageData VARCHAR2(1024);
1346 llca_def_trx_lines_tbl AR_RECEIPT_API_PUB.llca_trx_lines_tbl_type;
1347
1348
1349 BEGIN
1350 p_ErrorCode := g_SUCCESS;
1351 p_ErrorDesc := NULL;
1352 p_ErrorLoc := NULL;
1353
1354 debug_init (g_PackageName, l_module_name);
1355
1356 IF (p_ErrorCode = g_SUCCESS) THEN
1357 ----------------------------------------------------------------------
1358 -- Print contents of p_ApplyCashRec for debug purposes --
1359 ----------------------------------------------------------------------
1360 debug_msg (l_module_name, 'Calling API ar_receipt_api_pub.Apply/Apply_In_Detail with the following parameters');
1361 debug_msg (l_module_name, 'p_api_version => '||l_api_version);
1362 debug_msg (l_module_name, 'p_cash_receipt_id => '||p_ApplyCashRec.cash_receipt_id);
1363 debug_msg (l_module_name, 'p_receipt_number => '||p_ApplyCashRec.receipt_number);
1364 debug_msg (l_module_name, 'p_customer_trx_id => '||p_ApplyCashRec.customer_trx_id);
1365 debug_msg (l_module_name, 'p_trx_number => '||p_ApplyCashRec.trx_number);
1366 debug_msg (l_module_name, 'p_installment => '||p_ApplyCashRec.installment);
1367 debug_msg (l_module_name, 'p_applied_payment_schedule_id => '||p_ApplyCashRec.applied_payment_schedule_id);
1368 debug_msg (l_module_name, 'p_amount_applied => '||p_ApplyCashRec.amount_applied);
1369 debug_msg (l_module_name, 'p_amount_applied_from => '||p_ApplyCashRec.amount_applied_from);
1370 debug_msg (l_module_name, 'p_trans_to_receipt_rate => '||p_ApplyCashRec.trans_to_receipt_rate);
1371 debug_msg (l_module_name, 'p_discount => '||p_ApplyCashRec.discount);
1372 debug_msg (l_module_name, 'p_apply_date => '||TO_CHAR(p_ApplyCashRec.apply_date, 'MM/DD/YYYY HH24:MI:SS'));
1373 debug_msg (l_module_name, 'p_apply_gl_date => '||TO_CHAR(p_ApplyCashRec.apply_gl_date, 'MM/DD/YYYY HH24:MI:SS'));
1374 -- debug_msg (l_module_name, 'p_ussgl_transaction_code => '||p_ApplyCashRec.ussgl_transaction_code);
1375 debug_msg (l_module_name, 'p_customer_trx_line_id => '||p_ApplyCashRec.customer_trx_line_id);
1376 debug_msg (l_module_name, 'p_line_number => '||p_ApplyCashRec.line_number);
1377 debug_msg (l_module_name, 'p_show_closed_invoices => '||p_ApplyCashRec.show_closed_invoices);
1378 debug_msg (l_module_name, 'p_called_from => '||p_ApplyCashRec.called_from);
1379 debug_msg (l_module_name, 'p_move_deferred_tax => '||p_ApplyCashRec.move_deferred_tax);
1380 debug_msg (l_module_name, 'p_link_to_trx_hist_id => '||p_ApplyCashRec.link_to_trx_hist_id);
1381 debug_msg (l_module_name, 'p_comments => '||p_ApplyCashRec.comments);
1382 debug_msg (l_module_name, 'p_payment_set_id => '||p_ApplyCashRec.payment_set_id);
1383 debug_msg (l_module_name, 'p_application_ref_type => '||p_ApplyCashRec.application_ref_type);
1384 debug_msg (l_module_name, 'p_application_ref_id => '||p_ApplyCashRec.application_ref_id);
1385 debug_msg (l_module_name, 'p_application_ref_num => '||p_ApplyCashRec.application_ref_num);
1386 debug_msg (l_module_name, 'p_secondary_application_ref_id => '||p_ApplyCashRec.secondary_application_ref_id);
1387 debug_msg (l_module_name, 'p_application_ref_reason => '||p_ApplyCashRec.application_ref_reason);
1388 debug_msg (l_module_name, 'p_customer_reference => '||p_ApplyCashRec.customer_reference);
1389 debug_msg (l_module_name, 'p_org_id => '||p_ApplyCashRec.org_id);
1390
1391 ---------------------------------------------------------------------
1392 -- 1. Check if the Receipt Applied is for line level application
1393 -- 2. If yes then call AR LLCA API
1394 ---------------------------------------------------------------------
1395 IF (p_ApplyCashRec.customer_trx_line_id IS NOT NULL) THEN
1396
1397 log_msg (l_module_name,'Applying Invoice Id <'||p_ApplyCashRec.line_number||
1398 '> for line Number <'||p_ApplyCashRec.line_number||
1399 '> against Cash Receipt Id <'||p_ApplyCashRec.cash_receipt_id||'>');
1400
1401
1402 llca_def_trx_lines_tbl(1).customer_trx_line_id := p_ApplyCashRec.customer_trx_line_id;
1403 llca_def_trx_lines_tbl(1).line_number := p_ApplyCashRec.line_number;
1404 llca_def_trx_lines_tbl(1).line_amount := p_ApplyCashRec.amount_applied;
1405 llca_def_trx_lines_tbl(1).amount_applied := p_ApplyCashRec.amount_applied;
1406 llca_def_trx_lines_tbl(1).amount_applied_from := p_ApplyCashRec.amount_applied_from;
1407
1408 ar_receipt_api_pub.Apply_In_Detail
1409 (
1410 p_api_version => l_api_version,
1411 p_init_msg_list => FND_API.G_TRUE,
1412 p_commit => FND_API.G_TRUE,
1413 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1414 x_return_status => l_ReturnStatus,
1415 x_msg_count => l_MessageCount,
1416 x_msg_data => l_MessageData,
1417 p_llca_type => 'L',
1418 p_llca_trx_lines_tbl => llca_def_trx_lines_tbl,
1419 p_line_amount => p_ApplyCashRec.amount_applied,
1420 p_cash_receipt_id => p_ApplyCashRec.cash_receipt_id,
1421 p_receipt_number => p_ApplyCashRec.receipt_number,
1422 p_customer_trx_id => p_ApplyCashRec.customer_trx_id,
1423 p_trx_number => p_ApplyCashRec.trx_number,
1424 p_installment => p_ApplyCashRec.installment,
1425 p_applied_payment_schedule_id => p_ApplyCashRec.applied_payment_schedule_id,
1426 p_amount_applied => p_ApplyCashRec.amount_applied,
1427 p_amount_applied_from => p_ApplyCashRec.amount_applied_from,
1428 p_trans_to_receipt_rate => p_ApplyCashRec.trans_to_receipt_rate,
1429 p_discount => p_ApplyCashRec.discount,
1430 p_apply_date => p_ApplyCashRec.apply_date,
1431 p_apply_gl_date => p_ApplyCashRec.apply_gl_date,
1432 p_ussgl_transaction_code => null,
1433 -- p_customer_trx_line_id => p_ApplyCashRec.customer_trx_line_id,
1434 -- p_line_number => p_ApplyCashRec.line_number,
1435 p_show_closed_invoices => p_ApplyCashRec.show_closed_invoices,
1436 p_called_from => p_ApplyCashRec.called_from,
1437 p_move_deferred_tax => p_ApplyCashRec.move_deferred_tax,
1438 p_link_to_trx_hist_id => p_ApplyCashRec.link_to_trx_hist_id,
1439 p_attribute_rec => p_ApplyCashRec.attribute_rec,
1440 p_global_attribute_rec => p_ApplyCashRec.global_attribute_rec,
1441 p_comments => p_ApplyCashRec.comments,
1442 p_payment_set_id => p_ApplyCashRec.payment_set_id,
1443 p_application_ref_type => p_ApplyCashRec.application_ref_type,
1444 p_application_ref_id => p_ApplyCashRec.application_ref_id,
1445 p_application_ref_num => p_ApplyCashRec.application_ref_num,
1446 p_secondary_application_ref_id => p_ApplyCashRec.secondary_application_ref_id,
1447 p_application_ref_reason => p_ApplyCashRec.application_ref_reason,
1448 p_customer_reference => p_ApplyCashRec.customer_reference,
1449 p_org_id => p_ApplyCashRec.org_id
1450 );
1451 debug_msg (l_module_name, 'After Calling API ar_receipt_api_pub.Apply_In_Detail (Return Values)');
1452 debug_msg (l_module_name, 'x_return_status => '||l_ReturnStatus);
1453 debug_msg (l_module_name, 'x_msg_count => '||l_MessageCount);
1454 debug_msg (l_module_name, 'x_msg_data => '||l_MessageData);
1455
1456 ELSE
1457 ----------------------------------------------------------------------
1458 -- Call API ar_receipt_api_pub.Apply for applying the receipt amt --
1459 -- p_ApplyCashRec.amount_applied, p_ApplyCashRec.amount_applied_from--
1460 -- towards the invoice or debit memo --
1461 ----------------------------------------------------------------------
1462 log_msg (l_module_name,'Applying Invoice Id <'||p_ApplyCashRec.customer_trx_id||
1463 '> against Cash Receipt Id <'||p_ApplyCashRec.cash_receipt_id||'>');
1464 ar_receipt_api_pub.Apply
1465 (
1466 p_api_version => l_api_version,
1467 p_init_msg_list => FND_API.G_TRUE,
1468 p_commit => FND_API.G_FALSE,
1469 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1470 x_return_status => l_ReturnStatus,
1471 x_msg_count => l_MessageCount,
1472 x_msg_data => l_MessageData,
1473 p_cash_receipt_id => p_ApplyCashRec.cash_receipt_id,
1474 p_receipt_number => p_ApplyCashRec.receipt_number,
1475 p_customer_trx_id => p_ApplyCashRec.customer_trx_id,
1476 p_trx_number => p_ApplyCashRec.trx_number,
1477 p_installment => p_ApplyCashRec.installment,
1478 p_applied_payment_schedule_id => p_ApplyCashRec.applied_payment_schedule_id,
1479 p_amount_applied => p_ApplyCashRec.amount_applied,
1480 p_amount_applied_from => p_ApplyCashRec.amount_applied_from,
1481 p_trans_to_receipt_rate => p_ApplyCashRec.trans_to_receipt_rate,
1482 p_discount => p_ApplyCashRec.discount,
1483 p_apply_date => p_ApplyCashRec.apply_date,
1484 p_apply_gl_date => p_ApplyCashRec.apply_gl_date,
1485 -- p_ussgl_transaction_code => p_ApplyCashRec.ussgl_transaction_code,
1486 p_ussgl_transaction_code => null,
1487 p_customer_trx_line_id => p_ApplyCashRec.customer_trx_line_id,
1488 p_line_number => p_ApplyCashRec.line_number,
1489 p_show_closed_invoices => p_ApplyCashRec.show_closed_invoices,
1490 p_called_from => p_ApplyCashRec.called_from,
1491 p_move_deferred_tax => p_ApplyCashRec.move_deferred_tax,
1492 p_link_to_trx_hist_id => p_ApplyCashRec.link_to_trx_hist_id,
1493 p_attribute_rec => p_ApplyCashRec.attribute_rec,
1494 p_global_attribute_rec => p_ApplyCashRec.global_attribute_rec,
1495 p_comments => p_ApplyCashRec.comments,
1496 p_payment_set_id => p_ApplyCashRec.payment_set_id,
1497 p_application_ref_type => p_ApplyCashRec.application_ref_type,
1498 p_application_ref_id => p_ApplyCashRec.application_ref_id,
1499 p_application_ref_num => p_ApplyCashRec.application_ref_num,
1500 p_secondary_application_ref_id => p_ApplyCashRec.secondary_application_ref_id,
1501 p_application_ref_reason => p_ApplyCashRec.application_ref_reason,
1502 p_customer_reference => p_ApplyCashRec.customer_reference,
1503 p_org_id => p_ApplyCashRec.org_id
1504 );
1505
1506 debug_msg (l_module_name, 'After Calling API ar_receipt_api_pub.Apply (Return Values)');
1507 debug_msg (l_module_name, 'x_return_status => '||l_ReturnStatus);
1508 debug_msg (l_module_name, 'x_msg_count => '||l_MessageCount);
1509 debug_msg (l_module_name, 'x_msg_data => '||l_MessageData);
1510
1511 END IF;
1512
1513 IF (l_ReturnStatus <> 'S') THEN
1514 log_msg (l_module_name,'Cash Receipt Application Failed');
1515 ----------------------------------------------------------------------
1516 -- There is an error --
1517 ----------------------------------------------------------------------
1518 p_ErrorCode := g_FAILURE;
1519 p_ErrorLoc := 'After Calling API ar_receipt_api_pub.Apply.';
1520
1521 IF (l_MessageCount = 1) THEN
1522 ----------------------------------------------------------------------
1523 -- Message Count is 1, hence the error message is in x_msg_data --
1524 ----------------------------------------------------------------------
1525 p_ErrorDesc := l_MessageData;
1526 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1527 g_OutErrorInfo(g_OutCashReceipts.total_errors).error_desc := l_MessageData;
1528 error
1529 (
1530 p_error_type => p_ErrorCode,
1531 p_pgm => l_module_name,
1532 p_msg => p_ErrorDesc,
1533 p_loc => p_ErrorLoc
1534 );
1535 debug_msg (l_module_name, 'Error Message is :'||l_MessageData);
1536 ELSE
1537 ----------------------------------------------------------------------
1538 -- Message Count is > 1, hence loop for x_msg_count times and call --
1539 -- fnd_msg_pub.get to get the error messages --
1540 ----------------------------------------------------------------------
1541 FOR l_Counter IN 1..l_MessageCount LOOP
1542 l_MessageData := fnd_msg_pub.get (p_encoded => 'F');
1543 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1544 g_OutErrorInfo(g_OutCashReceipts.total_errors).error_desc := l_MessageData;
1545 error
1546 (
1547 p_error_type => p_ErrorCode,
1548 p_pgm => l_module_name,
1549 p_msg => p_ErrorDesc,
1550 p_loc => p_ErrorLoc
1551 );
1552 debug_msg (l_module_name, 'Error Message is :'||l_MessageData);
1553 END LOOP;
1554 p_ErrorDesc := 'Look at the Report to find the error';
1555 END IF;
1556 END IF;
1557 ELSE
1558 log_msg (l_module_name,'Successfully Applied against the Cash Receipt');
1559 END IF;
1560
1561 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 p_ErrorCode := g_FAILURE;
1565 p_ErrorDesc := SQLERRM;
1566 p_ErrorLoc := 'Final Exception';
1567 error
1568 (
1569 p_error_type => p_ErrorCode,
1570 p_pgm => l_module_name,
1571 p_msg => p_ErrorDesc,
1572 p_loc => p_ErrorLoc
1573 );
1574 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1575 END apply_cash_receipt;
1576
1577 --****************************************************************************************--
1578 --* Name : update_cash_receipt_hist *--
1579 --* Type : Procedure *--
1580 --* Purpose : Currenty the APIs for Receipt process does not have an option to *--
1581 --* : receive batch_id, hence the fv_ar_batch details are entered into the *--
1582 --* : table ar_batches and the batch_id obtained is used to update the *--
1583 --* : table ar_cash_receipt_history_all, so that it simulates the current *--
1584 --* : process of entering the receipt details through batch *--
1585 --* Parameters : p_BatchId IN The batch Id *--
1586 --* : p_CashReceiptId IN The Cash Receipt Id *--
1587 --* : p_ErrorCode OUT The Error Code *--
1588 --* : p_ErrorDesc OUT The Error Description *--
1589 --* : p_ErrorLoc OUT The Error Location *--
1590 --* Global Vars : g_SUCCESS READ *--
1591 --* Called from : create_cash_receipt *--
1592 --* Calls : debug_msg *--
1593 --* : debug_init *--
1594 --* : debug_exit *--
1595 --* : error *--
1596 --* : log_msg *--
1597 --* Tables Used : ar_cash_receipt_history_all UPDATE *--
1598 --* Logic : UPDATE ar_cash_receipt_history_all with value p_BatchId for the *--
1599 --* : receipt id p_CashReceiptId *--
1600 --****************************************************************************************--
1601 PROCEDURE update_cash_receipt_hist
1602 (
1603 p_BatchId IN NUMBER,
1604 p_CashReceiptId IN NUMBER,
1605 p_ErrorCode OUT NOCOPY VARCHAR2,
1606 p_ErrorDesc OUT NOCOPY VARCHAR2,
1607 p_ErrorLoc OUT NOCOPY VARCHAR2
1608 ) IS
1609 l_module_name VARCHAR2(30) := 'update_cash_receipt_hist';
1610
1611 BEGIN
1612 p_ErrorCode := g_SUCCESS;
1613 p_ErrorDesc := NULL;
1614 p_ErrorLoc := NULL;
1615
1616 debug_init (g_PackageName, l_module_name);
1617
1618 debug_msg (l_module_name, 'p_BatchId = '||p_BatchId);
1619 debug_msg (l_module_name, 'p_CashReceiptId = '||p_CashReceiptId);
1620
1621 log_msg (l_module_name,'Updating Cash Receipt History');
1622
1623 BEGIN
1624 ----------------------------------------------------------------------
1625 -- Update the table ar_cash_receipt_history_all to link it with the --
1626 -- table ar_batches --
1627 ----------------------------------------------------------------------
1628 UPDATE ar_cash_receipt_history_all
1629 SET batch_id = p_BatchId
1630 WHERE cash_receipt_id = p_CashReceiptId;
1631
1632 log_msg (l_module_name,'Successfully Updated Cash Receipt History');
1633 debug_msg (l_module_name, 'Updated '||SQL%ROWCOUNT||' rows.');
1634 EXCEPTION
1635 WHEN OTHERS THEN
1636 p_ErrorCode := g_FAILURE;
1637 p_ErrorDesc := SQLERRM;
1638 p_ErrorLoc := 'UPDATE ar_cash_receipt_history_all';
1639 error
1640 (
1641 p_error_type => p_ErrorCode,
1642 p_pgm => l_module_name,
1643 p_msg => p_ErrorDesc,
1644 p_loc => p_ErrorLoc
1645 );
1646 log_msg (l_module_name,'Error Updating Cash Receipt History');
1647 debug_msg (l_module_name, p_ErrorDesc||'at location'||p_ErrorLoc);
1648 END;
1649
1650 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1651 EXCEPTION
1652 WHEN OTHERS THEN
1653 p_ErrorCode := g_FAILURE;
1654 p_ErrorDesc := SQLERRM;
1655 p_ErrorLoc := 'Final Exception';
1656 error
1657 (
1658 p_error_type => p_ErrorCode,
1659 p_pgm => l_module_name,
1660 p_msg => p_ErrorDesc,
1661 p_loc => p_ErrorLoc
1662 );
1663 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1664 END update_cash_receipt_hist;
1665
1666 --****************************************************************************************--
1667 --* Name : update_fv_batch_status *--
1668 --* Type : Procedure *--
1669 --* Purpose : This procedure is used to update the status of the table *--
1670 --* : fv_ar_batches_all.
1671 --* Parameters : p_BatchId IN The batch Id *--
1672 --* : p_Status IN The Status to which the table to be updated *--
1673 --* : p_ErrorCode OUT The Error Code *--
1674 --* : p_ErrorDesc OUT The Error Description *--
1675 --* : p_ErrorLoc OUT The Error Location *--
1676 --* Global Vars : g_SUCCESS READ *--
1677 --* Called from : main *--
1678 --* Calls : debug_msg *--
1679 --* : debug_init *--
1680 --* : debug_exit *--
1681 --* Tables Used : fv_ar_batches_all UPDATE *--
1682 --* Logic : Update the table fv_ar_batches_all with the status p_Status for the *--
1683 --* : batch_id p_BatchId *--
1684 --****************************************************************************************--
1685 PROCEDURE update_fv_batch_status
1686 (
1687 p_BatchId IN NUMBER,
1688 p_Status IN VARCHAR2,
1689 p_ErrorCode OUT NOCOPY VARCHAR2,
1690 p_ErrorDesc OUT NOCOPY VARCHAR2,
1691 p_ErrorLoc OUT NOCOPY VARCHAR2
1692 ) IS
1693 l_module_name VARCHAR2(30) := 'update_fv_batch_status';
1694
1695 BEGIN
1696 p_ErrorCode := g_SUCCESS;
1697 p_ErrorDesc := NULL;
1698 p_ErrorLoc := NULL;
1699
1700 debug_init (g_PackageName, l_module_name);
1701
1702 debug_msg (l_module_name, 'p_BatchId = '||p_BatchId);
1703 debug_msg (l_module_name, 'p_Status = '||p_Status);
1704
1705 BEGIN
1706 debug_msg (l_module_name, 'Updating table fv_ar_batches_all');
1707
1708 ----------------------------------------------------------------------
1709 -- Update the table fv_ar_batches_all to the status p_Status for --
1710 -- batch_id p_BatchId --
1711 ----------------------------------------------------------------------
1712 UPDATE fv_ar_batches_all
1713 SET transfer_status = p_status
1714 WHERE batch_id = p_BatchId;
1715
1716 debug_msg (l_module_name, 'Updated '||SQL%ROWCOUNT||' rows.');
1717
1718 EXCEPTION
1719 WHEN OTHERS THEN
1720 p_ErrorCode := g_FAILURE;
1721 p_ErrorDesc := SQLERRM;
1722 p_ErrorLoc := 'UPDATE fv_ar_batches_all';
1723 error
1724 (
1725 p_error_type => p_ErrorCode,
1726 p_pgm => l_module_name,
1727 p_msg => p_ErrorDesc,
1728 p_loc => p_ErrorLoc
1729 );
1730 debug_msg (l_module_name, p_ErrorDesc||'at location'||p_ErrorLoc);
1731 END;
1732
1733 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 p_ErrorCode := g_FAILURE;
1737 p_ErrorDesc := SQLERRM;
1738 p_ErrorLoc := 'Final Exception';
1739 error
1740 (
1741 p_error_type => p_ErrorCode,
1742 p_pgm => l_module_name,
1743 p_msg => p_ErrorDesc,
1744 p_loc => p_ErrorLoc
1745 );
1746 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1747 END update_fv_batch_status;
1748
1749 --****************************************************************************************--
1750 --* Name : create_cash_receipt *--
1751 --* Type : Procedure *--
1752 --* Purpose : This procedure creates a Cash Receipt ID by calling the API *--
1753 --* : ar_receipt_api_pub.Create_cash and returns the Cash Receipt Id to *--
1754 --* : calling program. This procedure also calls update_cash_receipt_hist *--
1755 --* : to update the Cash Receipt History table with the batch_id *--
1756 --* Parameters : p_BatchId IN The batch Id *--
1757 --* : p_CreateCashRec IN CreateCashRecType *--
1758 --* : p_CashReceiptId OUT Cash Receipt Id *--
1759 --* : p_ErrorCode OUT The Error Code *--
1760 --* : p_ErrorDesc OUT The Error Description *--
1761 --* : p_ErrorLoc OUT The Error Location *--
1762 --* Global Vars : g_SUCCESS READ *--
1763 --* : g_OutErrorInfo WRITE *--
1764 --* : g_OutCashReceipts WRITE, READ *--
1765 --* Called from : process_receipts *--
1766 --* Calls : update_cash_receipt_hist *--
1767 --* : ar_receipt_api_pub.Create_cash *--
1768 --* : fnd_msg_pub.get *--
1769 --* : debug_msg *--
1770 --* : debug_init *--
1771 --* : debug_exit *--
1772 --* : error *--
1773 --* : log_msg *--
1774 --* Tables Used : None *--
1775 --* Logic : Use the values in p_CreateCashRec and use them as parameters to call *--
1776 --* : ar_receipt_api_pub.Create_cash *--
1777 --* : If there is an error the return code in x_return_status will not be S*--
1778 --* : If there is an error, check the contents of x_msg_count *--
1779 --* : If x_msg_count is 1 then the error message is obtained from *--
1780 --* : x_msg_data *--
1781 --* : If x_msg_count is > 1 then call fnd_msg_pub.get x_msg_count times to *--
1782 --* : get the error messages. *--
1783 --* : Call update_cash_receipt_hist to update the Cash Receipt History *--
1784 --* : table. *--
1785 --****************************************************************************************--
1786 PROCEDURE create_cash_receipt
1787 (
1788 p_BatchId IN NUMBER,
1789 p_CreateCashRec IN CreateCashRecType,
1790 p_CashReceiptId OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE,
1791 p_ErrorCode OUT NOCOPY VARCHAR2,
1792 p_ErrorDesc OUT NOCOPY VARCHAR2,
1793 p_ErrorLoc OUT NOCOPY VARCHAR2
1794 ) IS
1795 l_module_name VARCHAR2(30) := 'create_cash_receipt';
1796 l_api_version CONSTANT NUMBER := 1.0;
1797 l_ReturnStatus VARCHAR2(10);
1798 l_MessageCount NUMBER;
1799 l_MessageData VARCHAR2(1024);
1800 l_exchange_rate NUMBER;
1801
1802 BEGIN
1803 p_ErrorCode := g_SUCCESS;
1804 p_ErrorDesc := NULL;
1805 p_ErrorLoc := NULL;
1806
1807 debug_init (g_PackageName, l_module_name);
1808
1809 debug_msg (l_module_name, 'BatchId '||p_BatchId);
1810
1811 ----------------------------------------------------------------------
1812 -- Print contents of p_CreateCashRec for debug purposes --
1813 ----------------------------------------------------------------------
1814 debug_msg (l_module_name, 'Calling API ar_receipt_api_pub.Create_cash with the following paraeteres');
1815 debug_msg (l_module_name, ' p_api_version => '||l_api_version);
1816 debug_msg (l_module_name, ' p_usr_currency_code => '||p_CreateCashRec.usr_currency_code);
1817 debug_msg (l_module_name, ' p_currency_code => '||p_CreateCashRec.currency_code);
1818 debug_msg (l_module_name, ' p_usr_exchange_rate_type => '||p_CreateCashRec.usr_exchange_rate_type);
1819 debug_msg (l_module_name, ' p_exchange_rate_type => '||p_CreateCashRec.exchange_rate_type);
1820
1821 -- if exchange rate type is not 'User' then we don't need to pass exchange rate
1822 IF p_CreateCashRec.exchange_rate_type <> 'User' THEN
1823 l_exchange_rate := null;
1824 ELSE
1825 l_exchange_rate := p_CreateCashRec.exchange_rate;
1826 END IF;
1827
1828 debug_msg (l_module_name, ' p_exchange_rate => '||l_exchange_rate);
1829 debug_msg (l_module_name, ' p_exchange_rate_date => '||TO_CHAR(p_CreateCashRec.exchange_rate_date, 'MM/DD/YYYY'));
1830 debug_msg (l_module_name, ' p_amount => '||p_CreateCashRec.amount);
1831 debug_msg (l_module_name, ' p_factor_discount_amount => '||p_CreateCashRec.factor_discount_amount);
1832 debug_msg (l_module_name, ' p_receipt_number => '||p_CreateCashRec.receipt_number);
1833 debug_msg (l_module_name, ' p_receipt_date => '||TO_CHAR(p_CreateCashRec.receipt_date, 'MM/DD/YYYY'));
1834 debug_msg (l_module_name, ' p_gl_date => '||TO_CHAR(p_CreateCashRec.gl_date, 'MM/DD/YYYY'));
1835 debug_msg (l_module_name, ' p_maturity_date => '||TO_CHAR(p_CreateCashRec.maturity_date, 'MM/DD/YYYY'));
1836 debug_msg (l_module_name, ' p_postmark_date => '||TO_CHAR(p_CreateCashRec.postmark_date, 'MM/DD/YYYY'));
1837 debug_msg (l_module_name, ' p_customer_id => '||p_CreateCashRec.customer_id);
1838 debug_msg (l_module_name, ' p_customer_name => '||p_CreateCashRec.customer_name);
1839 debug_msg (l_module_name, ' p_customer_number => '||p_CreateCashRec.customer_number);
1840 debug_msg (l_module_name, ' p_customer_bank_account_id => '||p_CreateCashRec.customer_bank_account_id);
1841 debug_msg (l_module_name, ' p_customer_bank_account_num => '||p_CreateCashRec.customer_bank_account_num);
1842 debug_msg (l_module_name, ' p_customer_bank_account_name => '||p_CreateCashRec.customer_bank_account_name);
1843 debug_msg (l_module_name, ' p_location => '||p_CreateCashRec.location);
1844 debug_msg (l_module_name, ' p_customer_site_use_id => '||p_CreateCashRec.customer_site_use_id);
1845 debug_msg (l_module_name, ' p_customer_receipt_reference => '||p_CreateCashRec.customer_receipt_reference);
1846 debug_msg (l_module_name, ' p_override_remit_account_flag => '||p_CreateCashRec.override_remit_account_flag);
1847 debug_msg (l_module_name, ' p_remittance_bank_account_id => '||p_CreateCashRec.remittance_bank_account_id);
1848 debug_msg (l_module_name, ' p_remittance_bank_account_num => '||p_CreateCashRec.remittance_bank_account_num);
1849 debug_msg (l_module_name, ' p_remittance_bank_account_name => '||p_CreateCashRec.remittance_bank_account_name);
1850 debug_msg (l_module_name, ' p_deposit_date => '||TO_CHAR(p_CreateCashRec.deposit_date, 'MM/DD/YYYY'));
1851 debug_msg (l_module_name, ' p_receipt_method_id => '||p_CreateCashRec.receipt_method_id);
1852 debug_msg (l_module_name, ' p_receipt_method_name => '||p_CreateCashRec.receipt_method_name);
1853 debug_msg (l_module_name, ' p_doc_sequence_value => '||p_CreateCashRec.doc_sequence_value);
1854 -- debug_msg (l_module_name, ' p_ussgl_transaction_code => '||p_CreateCashRec.ussgl_transaction_code);
1855 debug_msg (l_module_name, ' p_anticipated_clearing_date => '||TO_CHAR(p_CreateCashRec.anticipated_clearing_date, 'MM/DD/YYYY'));
1856 debug_msg (l_module_name, ' p_called_from => '||p_CreateCashRec.called_from);
1857 debug_msg (l_module_name, ' p_comments => '||p_CreateCashRec.comments);
1858 debug_msg (l_module_name, ' p_issuer_name => '||p_CreateCashRec.issuer_name);
1859 debug_msg (l_module_name, ' p_issue_date => '||TO_CHAR(p_CreateCashRec.issue_date, 'MM/DD/YYYY'));
1860 debug_msg (l_module_name, ' p_issuer_bank_branch_id => '||p_CreateCashRec.issuer_bank_branch_id);
1861 debug_msg (l_module_name, ' p_org_id => '||p_CreateCashRec.org_id);
1862
1863 ----------------------------------------------------------------------
1864 -- Call API ar_receipt_api_pub.Create_cash to create a Cash Receipt --
1865 -- using the record p_CreateCashRec. --
1866 ----------------------------------------------------------------------
1867 log_msg (l_module_name,'Creating a Cash Receipt '||p_CreateCashRec.receipt_number);
1868 ar_receipt_api_pub.Create_cash
1869 (
1870 p_api_version => l_api_version,
1871 p_init_msg_list => FND_API.G_TRUE,
1872 p_commit => FND_API.G_FALSE,
1873 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1874 x_return_status => l_ReturnStatus,
1875 x_msg_count => l_MessageCount,
1876 x_msg_data => l_MessageData,
1877 p_usr_currency_code => p_CreateCashRec.usr_currency_code,
1878 p_currency_code => p_CreateCashRec.currency_code,
1879 p_usr_exchange_rate_type => p_CreateCashRec.usr_exchange_rate_type,
1880 p_exchange_rate_type => p_CreateCashRec.exchange_rate_type,
1881 p_exchange_rate => l_exchange_rate,
1882 p_exchange_rate_date => p_CreateCashRec.exchange_rate_date,
1883 p_amount => p_CreateCashRec.amount,
1884 p_factor_discount_amount => p_CreateCashRec.factor_discount_amount,
1885 p_receipt_number => p_CreateCashRec.receipt_number,
1886 p_receipt_date => p_CreateCashRec.receipt_date,
1887 p_gl_date => p_CreateCashRec.gl_date,
1888 p_maturity_date => p_CreateCashRec.maturity_date,
1889 p_postmark_date => p_CreateCashRec.postmark_date,
1890 p_customer_id => p_CreateCashRec.customer_id,
1891 p_customer_name => p_CreateCashRec.customer_name,
1892 p_customer_number => p_CreateCashRec.customer_number,
1893 p_customer_bank_account_id => p_CreateCashRec.customer_bank_account_id,
1894 p_customer_bank_account_num => p_CreateCashRec.customer_bank_account_num,
1895 p_customer_bank_account_name => p_CreateCashRec.customer_bank_account_name,
1896 p_location => p_CreateCashRec.location,
1897 p_customer_site_use_id => p_CreateCashRec.customer_site_use_id,
1898 p_customer_receipt_reference => p_CreateCashRec.customer_receipt_reference,
1899 p_override_remit_account_flag => p_CreateCashRec.override_remit_account_flag,
1900 p_remittance_bank_account_id => p_CreateCashRec.remittance_bank_account_id,
1901 p_remittance_bank_account_num => p_CreateCashRec.remittance_bank_account_num,
1902 p_remittance_bank_account_name => p_CreateCashRec.remittance_bank_account_name,
1903 p_deposit_date => p_CreateCashRec.deposit_date,
1904 p_receipt_method_id => p_CreateCashRec.receipt_method_id,
1905 p_receipt_method_name => p_CreateCashRec.receipt_method_name,
1906 p_doc_sequence_value => p_CreateCashRec.doc_sequence_value,
1907 -- p_ussgl_transaction_code => p_CreateCashRec.ussgl_transaction_code,
1908 p_ussgl_transaction_code => null,
1909 p_anticipated_clearing_date => p_CreateCashRec.anticipated_clearing_date,
1910 p_called_from => p_CreateCashRec.called_from,
1911 p_attribute_rec => p_CreateCashRec.attribute_rec,
1912 p_global_attribute_rec => p_CreateCashRec.global_attribute_rec,
1913 p_comments => p_CreateCashRec.comments,
1914 p_issuer_name => p_CreateCashRec.issuer_name,
1915 p_issue_date => p_CreateCashRec.issue_date,
1916 p_issuer_bank_branch_id => p_CreateCashRec.issuer_bank_branch_id,
1917 p_cr_id => p_CashReceiptId,
1918 p_org_id => p_CreateCashRec.org_id
1919 );
1920
1921 debug_msg (l_module_name, 'After Calling API ar_receipt_api_pub.Create_cash (Return Values)');
1922 debug_msg (l_module_name, ' p_cr_id => '||p_CashReceiptId);
1923 debug_msg (l_module_name, ' x_return_status => '||l_ReturnStatus);
1924 debug_msg (l_module_name, ' x_msg_count => '||l_MessageCount);
1925 debug_msg (l_module_name, ' x_msg_data => '||l_MessageData);
1926
1927 IF (l_ReturnStatus <> 'S') THEN
1928 log_msg (l_module_name,'Error creating Cash Receipt '||p_CreateCashRec.receipt_number);
1929 ----------------------------------------------------------------------
1930 -- There is an error --
1931 ----------------------------------------------------------------------
1932 p_ErrorCode := g_FAILURE;
1933 p_ErrorLoc := 'After Calling API ar_receipt_api_pub.Create_cash.';
1934
1935 IF (l_MessageCount = 1) THEN
1936 ----------------------------------------------------------------------
1937 -- Message Count is 1, hence the error message is in x_msg_data --
1938 ----------------------------------------------------------------------
1939 p_ErrorDesc := l_MessageData;
1940 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1941 g_OutErrorInfo(g_OutCashReceipts.total_errors).error_desc := l_MessageData;
1942 error
1943 (
1944 p_error_type => p_ErrorCode,
1945 p_pgm => l_module_name,
1946 p_msg => p_ErrorDesc,
1947 p_loc => p_ErrorLoc
1948 );
1949 debug_msg (l_module_name, 'Error Message is :'||l_MessageData);
1950 ELSE
1951 ----------------------------------------------------------------------
1952 -- Message Count is > 1, hence loop for x_msg_count times and call --
1953 -- fnd_msg_pub.get to get the error messages --
1954 ----------------------------------------------------------------------
1955 FOR l_Counter IN 1..l_MessageCount LOOP
1956 l_MessageData := fnd_msg_pub.get (p_encoded => 'F');
1957 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1958 g_OutErrorInfo(g_OutCashReceipts.total_errors).error_desc := l_MessageData;
1959 error
1960 (
1961 p_error_type => p_ErrorCode,
1962 p_pgm => l_module_name,
1963 p_msg => p_ErrorDesc,
1964 p_loc => p_ErrorLoc
1965 );
1966 debug_msg (l_module_name, 'Error Message is :'||l_MessageData);
1967 END LOOP;
1968 p_ErrorDesc := 'Look at the Report to find the error';
1969 END IF;
1970 END IF;
1971
1972 IF (p_ErrorCode = g_SUCCESS) THEN
1973 log_msg (l_module_name,'Successfully Created Cash Receipt '||p_CreateCashRec.receipt_number);
1974 ----------------------------------------------------------------------
1975 -- Call update_cash_receipt_hist to update the Cash Receipt History --
1976 -- table with the batch_id as p_BatchId for cash_receipt_id --
1977 -- p_CashReceiptId --
1978 ----------------------------------------------------------------------
1979 debug_msg (l_module_name, 'Calling update_cash_receipt_hist.');
1980 update_cash_receipt_hist
1981 (
1982 p_BatchId => p_BatchId,
1983 p_CashReceiptId => p_CashReceiptId,
1984 p_ErrorCode => p_ErrorCode,
1985 p_ErrorDesc => p_ErrorDesc,
1986 p_ErrorLoc => p_ErrorLoc
1987 );
1988 END IF;
1989
1990 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1991 EXCEPTION
1992 WHEN OTHERS THEN
1993 p_ErrorCode := g_FAILURE;
1994 p_ErrorDesc := SQLERRM;
1995 p_ErrorLoc := 'Final Exception';
1996 error
1997 (
1998 p_error_type => p_ErrorCode,
1999 p_pgm => l_module_name,
2000 p_msg => p_ErrorDesc,
2001 p_loc => p_ErrorLoc
2002 );
2003 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
2004 END create_cash_receipt;
2005
2006 --****************************************************************************************--
2007 --* Name : pay_the_invoice *--
2008 --* Type : Procedure *--
2009 --* Purpose :
2010 --* Parameters : p_ReceiptNumber IN The receipt Number *--
2011 --* : p_CashReceiptId IN Cash Receipt Id *--
2012 --* : p_InvoiceNumber IN Invoice Number *--
2013 --* : p_InvoiceId IN Invoice Id *--
2014 --* : p_InvoiceLineId IN Invoice Line id *--
2015 --* : p_CurrencyCode IN Receipt Currency Code *--
2016 --* : p_InvoiceCurrencyCode IN Invoice Currency Code *--
2017 --* : p_ExchangeRateDate IN Exchange Rate Date *--
2018 --* : p_ExchangeRate IN Exchange Rate *--
2019 --* : p_ExchangeRateType IN Exchange Rate Type *--
2020 --* : p_PaymentScheduleId IN Payment Schedule Id *--
2021 --* : p_InvoiceAmount IN Invoice Amount *--
2022 --* : p_ReceiptDate IN Receipt Date *--
2023 --* : p_GLDate IN GL Date *--
2024 --* : p_USSGLTransactionCode IN USSGL Transaction Code *--
2025 --* : p_RemaingReceiptAmount IN OUT The Remaining Receipt Amount *--
2026 --* : p_ErrorCode OUT The Error Code *--
2027 --* : p_ErrorDesc OUT The Error Description *--
2028 --* : p_ErrorLoc OUT The Error Location *--
2029 --* Global Vars : g_SUCCESS READ *--
2030 --* Called from : process_receipts *--
2031 --* Calls : apply_cash_receipt *--
2032 --* : debug_msg *--
2033 --* : debug_init *--
2034 --* : debug_exit *--
2035 --* : error *--
2036 --* Tables Used : None *--
2037 --* Logic : . *--
2038 --****************************************************************************************--
2039 PROCEDURE pay_the_invoice
2040 (
2041 p_ReceiptNumber IN fv_interim_cash_receipts.receipt_number%TYPE,
2042 p_CashReceiptId IN ar_cash_receipts.cash_receipt_id%TYPE,
2043 p_InvoiceNumber IN ra_customer_trx_all.trx_number%TYPE,
2044 p_LineNumber IN ra_customer_trx_lines_all.line_number%TYPE,
2045 p_InvoiceId IN ra_customer_trx_all.customer_trx_id%TYPE,
2046 p_InvoiceLineId IN ra_customer_trx_lines_all.customer_trx_line_id%TYPE,
2047 p_CurrencyCode IN fv_interim_cash_receipts.currency_code%TYPE,
2048 p_InvoiceCurrencyCode IN ra_customer_trx_all.invoice_currency_code%TYPE,
2049 p_ExchangeRateDate IN ar_batches.exchange_date%TYPE,
2050 p_ExchangeRate IN ar_batches.exchange_rate%TYPE,
2051 p_ExchangeRateType IN ar_batches.exchange_rate_type%TYPE,
2052 p_PaymentScheduleId IN ar_payment_schedules.payment_schedule_id%TYPE,
2053 p_InvoiceAmount IN NUMBER,
2054 p_InvoiceLineAmount IN NUMBER,
2055 p_ReceiptDate IN ar_cash_receipts.receipt_date%TYPE,
2056 p_GLDate IN DATE,
2057 -- p_USSGLTransactionCode IN ar_cash_receipts.ussgl_transaction_code%TYPE,
2058 p_org_id IN NUMBER,
2059 p_RemaingReceiptAmount IN OUT NOCOPY NUMBER,
2060 p_ErrorCode OUT NOCOPY VARCHAR2,
2061 p_ErrorDesc OUT NOCOPY VARCHAR2,
2062 p_ErrorLoc OUT NOCOPY VARCHAR2
2063 ) IS
2064 l_module_name VARCHAR2(30) := 'pay_the_invoice';
2065
2066 l_AmountApplied NUMBER;
2067 l_InvAmountApplied NUMBER;
2068 l_ApplyCashRec ApplyCashRecType;
2069 l_ConvertedInvoiceAmount NUMBER;
2070 l_ExchangeRate NUMBER;
2071 l_OnAccountRec OnAccountRecType;
2072
2073 l_InvoiceAmount NUMBER := p_InvoiceAmount;
2074 l_LineAmount NUMBER;
2075
2076 BEGIN
2077 p_ErrorCode := g_SUCCESS;
2078 p_ErrorDesc := NULL;
2079 p_ErrorLoc := NULL;
2080
2081 debug_init (g_PackageName, l_module_name);
2082
2083 debug_msg (l_module_name, 'p_ReceiptNumber = '||p_ReceiptNumber);
2084 debug_msg (l_module_name, 'p_CashReceiptId = '||p_CashReceiptId);
2085 debug_msg (l_module_name, 'p_InvoiceId = '||p_InvoiceId);
2086 debug_msg (l_module_name, 'p_InvoiceNumber = '||p_InvoiceNumber);
2087 debug_msg (l_module_name, 'p_InvoiceLineId = '||p_InvoiceLineId);
2088 debug_msg (l_module_name, 'p_CurrencyCode = '||p_CurrencyCode);
2089 debug_msg (l_module_name, 'p_InvoiceCurrencyCode = '||p_InvoiceCurrencyCode);
2090 debug_msg (l_module_name, 'p_ExchangeRateDate = '||TO_CHAR(p_ExchangeRateDate, 'MM/DD/YYYY HH24:MI:SS'));
2091 debug_msg (l_module_name, 'p_ExchangeRate = '||p_ExchangeRate);
2092 debug_msg (l_module_name, 'p_ExchangeRateType = '||p_ExchangeRateType);
2093 debug_msg (l_module_name, 'p_PaymentScheduleId = '||p_PaymentScheduleId);
2094 debug_msg (l_module_name, 'p_InvoiceAmount = '||p_InvoiceAmount);
2095 debug_msg (l_module_name, 'p_ReceiptDate = '||TO_CHAR(p_ReceiptDate, 'MM/DD/YYYY HH24:MI:SS'));
2096 debug_msg (l_module_name, 'p_GLDate = '||TO_CHAR(p_GLDate, 'MM/DD/YYYY HH24:MI:SS'));
2097 -- debug_msg (l_module_name, 'p_USSGLTransactionCode = '||p_USSGLTransactionCode);
2098 debug_msg (l_module_name, 'p_org_id = ' || p_org_id);
2099 debug_msg (l_module_name, 'p_RemaingReceiptAmount = '||p_RemaingReceiptAmount);
2100
2101
2102 IF (p_InvoiceCurrencyCode <> p_CurrencyCode) THEN
2103 l_ExchangeRate := p_ExchangeRate;
2104 ELSE
2105 l_ExchangeRate := NULL;
2106 END IF;
2107
2108 IF (p_InvoiceLineId IS NOT NULL ) THEN
2109 BEGIN
2110 SELECT ctl.extended_amount * nvl(tl.relative_amount,1)/ nvl(t.base_amount,1) original_line_amount
2111 INTO l_LineAmount
2112 FROM ra_customer_trx_lines ctl ,
2113 ra_terms t,
2114 ra_terms_lines tl,
2115 ar_payment_schedules ps
2116 WHERE ps.payment_schedule_id = p_PaymentScheduleId
2117 AND ctl.customer_trx_id = p_InvoiceId
2118 AND ctl.line_type = 'LINE'
2119 AND tl.term_id(+) = ps.term_id
2120 AND tl.sequence_num(+) = ps.terms_sequence_number
2121 AND t.term_id(+) = tl.term_id
2122 AND ctl.customer_trx_line_id = p_InvoiceLineId;
2123 EXCEPTION
2124 WHEN NO_DATA_FOUND THEN
2125 l_LineAmount := 0;
2126 debug_msg (l_module_name, 'No Data Found for payment_schedule_id <'||p_PaymentScheduleId||'>');
2127 WHEN OTHERS THEN
2128 p_ErrorCode := g_FAILURE;
2129 p_ErrorDesc := SQLERRM;
2130 p_ErrorLoc := 'SELECT ra_customer_trx_lines, ra_terms...';
2131 error
2132 (
2133 p_error_type => p_ErrorCode,
2134 p_pgm => l_module_name,
2135 p_msg => p_ErrorDesc,
2136 p_loc => p_ErrorLoc
2137 );
2138 debug_msg (l_module_name, p_ErrorDesc||'at location'||p_ErrorLoc);
2139 END;
2140
2141 debug_msg (l_module_name, 'adjusted l_lineAmount = '||l_lineAmount);
2142
2143 IF (l_InvoiceAmount > l_LineAmount) THEN
2144 l_InvoiceAmount := l_LineAmount;
2145 END IF;
2146 END IF;
2147
2148 l_ConvertedInvoiceAmount := l_InvoiceAmount*NVL(l_ExchangeRate, 1);
2149 debug_msg(l_module_name, 'l_convertedInvoiceAmount = '||l_convertedInvoiceAmount);
2150
2151 IF (p_ErrorCode = g_SUCCESS) THEN
2152 IF (p_RemaingReceiptAmount <= l_ConvertedInvoiceAmount) THEN
2153 l_AmountApplied := p_RemaingReceiptAmount;
2154 p_RemaingReceiptAmount := 0;
2155 ELSE
2156 l_AmountApplied := l_ConvertedInvoiceAmount;
2157 p_RemaingReceiptAmount := p_RemaingReceiptAmount - l_ConvertedInvoiceAmount;
2158 END IF;
2159
2160 l_InvAmountApplied := l_AmountApplied / NVL(l_ExchangeRate, 1);
2161
2162 IF (p_InvoiceCurrencyCode <> p_CurrencyCode) THEN
2163 l_ApplyCashRec.amount_applied := l_InvAmountApplied;
2164 l_ApplyCashRec.amount_applied_from := l_AmountApplied;
2165 ELSE
2166 l_ApplyCashRec.amount_applied := l_AmountApplied;
2167 END IF;
2168
2169 l_ApplyCashRec.cash_receipt_id := p_CashReceiptId;
2170 l_ApplyCashRec.customer_trx_id := p_InvoiceId;
2171 l_ApplyCashRec.customer_trx_line_id := p_InvoiceLineId;
2172 l_ApplyCashRec.line_number := p_LineNumber;
2173 l_ApplyCashRec.applied_payment_schedule_id := p_PaymentScheduleId;
2174 l_ApplyCashRec.apply_date := p_ReceiptDate;
2175 l_ApplyCashRec.apply_gl_date := p_GLDate;
2176 -- l_ApplyCashRec.ussgl_transaction_code := p_USSGLTransactionCode;
2177 l_ApplyCashRec.org_id := p_org_id;
2178 l_ApplyCashRec.trans_to_receipt_rate := l_ExchangeRate;
2179
2180
2181 apply_cash_receipt
2182 (
2183 p_ApplyCashRec => l_ApplyCashRec,
2184 p_ErrorCode => p_ErrorCode,
2185 p_ErrorDesc => p_ErrorDesc,
2186 p_ErrorLoc => p_ErrorLoc
2187 );
2188 END IF;
2189
2190 IF (p_ErrorCode = g_SUCCESS) THEN
2191 g_OutCashReceipts.total_applications := g_OutCashReceipts.total_applications + 1;
2192 g_OutReceiptApplications(g_OutCashReceipts.total_applications).status := 'A';
2193 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_number := p_InvoiceNumber;
2194 g_OutReceiptApplications(g_OutCashReceipts.total_applications).line_number := p_LineNumber;
2195 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_amount := l_AmountApplied;
2196 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_type := 'INVOICE';
2197 g_OutReceiptApplications(g_OutCashReceipts.total_applications).amt_applied_in_inv_curr := l_InvAmountApplied;
2198 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_currency := p_InvoiceCurrencyCode;
2199 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_amount_due := l_InvoiceAmount;
2200 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_currency := p_CurrencyCode;
2201 g_OutReceiptApplications(g_OutCashReceipts.total_applications).exchange_rate := NVL(l_ExchangeRate, 1);
2202 END IF;
2203
2204 /* commenting this section out because this is not needed currently.
2205 caused a problem when processing a mfar split term invoice.
2206 IF (p_ErrorCode = g_SUCCESS) THEN
2207 IF ((p_InvoiceLineId IS NOT NULL) AND (p_RemaingReceiptAmount > 0)) THEN
2208
2209
2210 --------------------------------------------------------------------------------------
2211 -- Initialize the Report Variables for the On Account Application --
2212 --------------------------------------------------------------------------------------
2213 l_OnAccountRec.cash_receipt_id := P_CashReceiptId;
2214 l_OnAccountRec.amount_applied := p_RemaingReceiptAmount;
2215 l_OnAccountRec.apply_date := p_ReceiptDate;
2216 l_OnAccountRec.apply_gl_date := p_GLDate;
2217 l_OnAccountRec.ussgl_transaction_code := p_USSGLTransactionCode;
2218
2219
2220 --------------------------------------------------------------------------------------
2221 -- Apply the remaining amount to On Account --
2222 --------------------------------------------------------------------------------------
2223 apply_on_account
2224 (
2225 p_OnAccountRec => l_OnAccountRec,
2226 p_ErrorCode => p_ErrorCode,
2227 p_ErrorDesc => p_ErrorDesc,
2228 p_ErrorLoc => p_ErrorLoc
2229 );
2230
2231 IF (p_ErrorCode = g_SUCCESS) THEN
2232 g_OutCashReceipts.total_applications := g_OutCashReceipts.total_applications + 1;
2233 g_OutReceiptApplications(g_OutCashReceipts.total_applications).status := 'A';
2234 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_number := 'On Account';
2235 g_OutReceiptApplications(g_OutCashReceipts.total_applications).line_number := NULL;
2236 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_amount := p_RemaingReceiptAmount;
2237 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_type := 'ON ACCOUNT';
2238 g_OutReceiptApplications(g_OutCashReceipts.total_applications).amt_applied_in_inv_curr := p_RemaingReceiptAmount;
2239 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_currency := '';
2240 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_amount_due := 0;
2241 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_currency := '';
2242 g_OutReceiptApplications(g_OutCashReceipts.total_applications).exchange_rate := '';
2243 END IF;
2244
2245 p_RemaingReceiptAmount := 0;
2246
2247 END IF;
2248 END IF;
2249 */
2250 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 p_ErrorCode := g_FAILURE;
2254 p_ErrorDesc := SQLERRM;
2255 p_ErrorLoc := 'Final Exception';
2256 error
2257 (
2258 p_error_type => p_ErrorCode,
2259 p_pgm => l_module_name,
2260 p_msg => p_ErrorDesc,
2261 p_loc => p_ErrorLoc
2262 );
2263 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
2264 END pay_the_invoice;
2265 --* Commented Out get_receipt_txn_code procedure for Transaction Codes Obsoletion *--
2266 --****************************************************************************************--
2267 --* Name : get_receipt_txn_code *--
2268 --* Type : Procedure *--
2269 --* Purpose : This Procedure is used to get the Receipt Transaction Code from the *--
2270 --* : mapping table for a Debit Memo. *--
2271 --* Parameters : p_DebitMemoId IN Debit Memo Id *--
2272 --* : p_EffectiveDate IN Effective Date *--
2273 --* : p_ReceiptTxnCode OUT Receipt Transaction Code *--
2274 --* : p_ErrorCode OUT The Error Code *--
2275 --* : p_ErrorDesc OUT The Error Description *--
2276 --* : p_ErrorLoc OUT The Error Location *--
2277 --* Global Vars : g_SUCCESS READ *--
2278 --* Called from : pay_debit_memos *--
2279 --* Calls : debug_msg *--
2280 --* : debug_init *--
2281 --* : debug_exit *--
2282 --* : error *--
2283 --* : log_msg *--
2284 --* Tables Used : ra_cust_trx_line_gl_dist SELECT *--
2285 --* : fv_tc_map_dtl SELECT *--
2286 --* : fv_tc_map_hdr SELECT *--
2287 --* Logic : 1. Get the Transaction Code from the Revenue side of the Debit Memo *--
2288 --* : transcation. *--
2289 --* : 2. Use that to get the receipt transaction code from the mapping *--
2290 --* : table fv_tc_map_dtl. *--
2291 --* : 3. Return this value *--
2292 --****************************************************************************************--
2293 /*--- Commented Out get_receipt_txn_code procedure for Transaction Codes Obsoletion
2294 PROCEDURE get_receipt_txn_code
2295 (
2296 p_DebitMemoId IN VARCHAR2,
2297 p_EffectiveDate IN DATE,
2298 p_ReceiptTxnCode OUT NOCOPY VARCHAR2,
2299 p_ErrorCode OUT NOCOPY VARCHAR2,
2300 p_ErrorDesc OUT NOCOPY VARCHAR2,
2301 p_ErrorLoc OUT NOCOPY VARCHAR2
2302 ) IS
2303 l_module_name VARCHAR2(30) := 'get_receipt_txn_code';
2304
2305 l_DebitMemoTxnCode ra_cust_trx_line_gl_dist.ussgl_transaction_code%TYPE;
2306
2307 BEGIN
2308 p_ErrorCode := g_SUCCESS;
2309 p_ErrorDesc := NULL;
2310 p_ErrorLoc := NULL;
2311
2312 debug_init (g_PackageName, l_module_name);
2313
2314 BEGIN
2315 SELECT DISTINCT ussgl_transaction_code
2316 INTO l_DebitMemoTxnCode
2317 FROM ra_cust_trx_line_gl_dist
2318 WHERE customer_trx_id = p_DebitMemoId
2319 AND account_class = 'REV';
2320 EXCEPTION
2321 WHEN NO_DATA_FOUND THEN
2322 l_DebitMemoTxnCode := NULL;
2323 debug_msg (l_module_name, 'No Data Found for p_DebitMemoId <'||p_DebitMemoId||'>');
2324 WHEN OTHERS THEN
2325 p_ErrorCode := g_FAILURE;
2326 p_ErrorDesc := SQLERRM;
2327 p_ErrorLoc := 'SELECT ra_cust_trx_line_gl_dist';
2328 error
2329 (
2330 p_error_type => p_ErrorCode,
2331 p_pgm => l_module_name,
2332 p_msg => p_ErrorDesc,
2333 p_loc => p_ErrorLoc
2334 );
2335 debug_msg (l_module_name, p_ErrorDesc||'at location'||p_ErrorLoc);
2336 END;
2337
2338
2339 IF (p_ErrorCode = g_SUCCESS) THEN
2340 debug_msg (l_module_name, 'Debit Memo Txn Code is <'||l_DebitMemoTxnCode||'>');
2341 log_msg (l_module_name,'Trying to Map Debit Memo Txn Code '||l_DebitMemoTxnCode);
2342 BEGIN
2343 SELECT receipt_txn_code
2344 INTO p_ReceiptTxnCode
2345 FROM fv_tc_map_dtl ftmd,
2346 fv_tc_map_hdr ftmh
2347 WHERE ftmh.document_type = 'RECEIPT'
2348 AND ftmd.tc_map_hdr_id = ftmh.tc_map_hdr_id
2349 AND ftmd.debit_memo_txn_code = l_DebitMemoTxnCode
2350 AND p_EffectiveDate BETWEEN ftmd.start_date AND NVL(ftmd.end_date, SYSDATE);
2351 log_msg (l_module_name,'Debit Memo Txn Code '||l_DebitMemoTxnCode||' mapped to Receipt Txn '||p_ReceiptTxnCode);
2352 EXCEPTION
2353 WHEN NO_DATA_FOUND THEN
2354 p_ReceiptTxnCode := NULL;
2355 debug_msg (l_module_name, 'No Data Found for Debit Memo Txn Code <'||l_DebitMemoTxnCode||'>');
2356 log_msg (l_module_name,'Could not map Debit Memo Txn Code '||l_DebitMemoTxnCode);
2357 WHEN OTHERS THEN
2358 p_ErrorCode := g_FAILURE;
2359 p_ErrorDesc := SQLERRM;
2360 p_ErrorLoc := 'SELECT fv_tc_map_dtl';
2361 error
2362 (
2363 p_error_type => p_ErrorCode,
2364 p_pgm => l_module_name,
2365 p_msg => p_ErrorDesc,
2366 p_loc => p_ErrorLoc
2367 );
2368 debug_msg (l_module_name, p_ErrorDesc||'at location'||p_ErrorLoc);
2369 END;
2370 END IF;
2371
2372 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
2373 EXCEPTION
2374 WHEN OTHERS THEN
2375 p_ErrorCode := g_FAILURE;
2376 p_ErrorDesc := SQLERRM;
2377 p_ErrorLoc := 'Final Exception';
2378 error
2379 (
2380 p_error_type => p_ErrorCode,
2381 p_pgm => l_module_name,
2382 p_msg => p_ErrorDesc,
2383 p_loc => p_ErrorLoc
2384 );
2385 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
2386 END get_receipt_txn_code;
2387 -------------------- End of Cmmnets -----------------------------------------------*/
2388 --****************************************************************************************--
2389 --* Name : pay_debit_memos *--
2390 --* Type : Procedure *--
2391 --* Purpose : This Procedure Pays of all the debit memos for an invoice *--
2392 --* Parameters : p_ReceiptNumber IN The Receipt Number *--
2393 --* : p_CashReceiptId IN The Cash Receipt Id *--
2394 --* : p_InvoiceId IN Invoice Id *--
2395 --* : p_CurrencyCode IN Currency Code *--
2396 --* : p_ExchangeRateDate IN Exchange Rate Date *--
2397 --* : p_ExchangeRate IN Exchange Rate *--
2398 --* : p_ExchangeRateType IN Exchange Rate Type *--
2399 --* : p_ReceiptDate IN Receipt Date *--
2400 --* : p_gldate IN GL Date *--
2401 --* : p_RemaingReceiptAmount IN OUT The remaining rcpt amt after applying *--
2402 --* : p_ErrorCode OUT The Error Code *--
2403 --* : p_ErrorDesc OUT The Error Description *--
2404 --* : p_ErrorLoc OUT The Error Location *--
2405 --* Global Vars : g_SUCCESS READ *--
2406 --* : g_OutInvoiceDebitMemos WRITE *--
2407 --* : g_OutCashReceipts READ WRITE *--
2408 --* Called from : process_receipts *--
2409 --* Calls : get_receipt_txn_code *--
2410 --* : unapply_if_already_applied *--
2411 --* : apply_cash_receipt *--
2412 --* : debug_msg *--
2413 --* : debug_init *--
2414 --* : debug_exit *--
2415 --* : error *--
2416 --* : log_msg *--
2417 --* Tables Used : ra_customer_trx SELECT *--
2418 --* : ar_payment_schedules SELECT *--
2419 --* : fv_finance_charge_controls SELECT *--
2420 --* Logic : 1. Loop and Process the following steps for every Debit Memos that *--
2421 --* : exist for the invoice (That are due) *--
2422 --* : 2. If the debit memo was procesed earlier using the same cash *--
2423 --* : cash receipt, unapply the old application and apply once again *--
2424 --* : 3. Call the program get_receipt_txn_code to get the Debit Memo Txn *--
2425 --* : code given the receipt transaction code *--
2426 --* : 4. Convert the Debit Memo Amount into the Receipt Currency for *--
2427 --* : finding out the application amount. *--
2428 --* : 5. Populate the structure required to call the API for application *--
2429 --* : 6. Call the program apply_cash_receipt to Apply the receipt *--
2430 --* : 7. Populate the Report Varaibles for output *--
2431 --****************************************************************************************--
2432 PROCEDURE pay_debit_memos
2433 (
2434 p_ReceiptNumber IN fv_interim_cash_receipts.receipt_number%TYPE,
2435 p_CashReceiptId IN ar_cash_receipts_all.cash_receipt_id%TYPE,
2436 p_InvoiceId IN ra_customer_trx_all.customer_trx_id%TYPE,
2437 p_CurrencyCode IN fv_interim_cash_receipts.currency_code%TYPE,
2438 p_ExchangeRateDate IN ar_batches.exchange_date%TYPE,
2439 p_ExchangeRate IN ar_batches.exchange_rate%TYPE,
2440 p_ExchangeRateType IN ar_batches.exchange_rate_type%TYPE,
2441 p_ReceiptDate IN ar_cash_receipts.receipt_date%TYPE,
2442 p_gldate IN DATE,
2443 p_RemaingReceiptAmount IN OUT NOCOPY NUMBER,
2444 p_ErrorCode OUT NOCOPY VARCHAR2,
2445 p_ErrorDesc OUT NOCOPY VARCHAR2,
2446 p_ErrorLoc OUT NOCOPY VARCHAR2
2447 ) IS
2448 l_module_name VARCHAR2(30) := 'pay_debit_memos';
2449
2450 l_AmountApplied NUMBER;
2451 l_InvAmountApplied NUMBER;
2452 l_ApplyCashRec ApplyCashRecType;
2453 -- l_USSGLTransactionCode ar_cash_receipts.ussgl_transaction_code%TYPE;
2454 l_ConvertedAmountDue NUMBER;
2455 l_denominator NUMBER;
2456 l_numerator NUMBER;
2457 l_ExchangeRate ar_batches.exchange_rate%TYPE;
2458 l_UnAppliedAmount NUMBER := 0;
2459
2460 CURSOR DebitMemo_Cur (c_invoice_id NUMBER) IS
2461 SELECT distinct aps.customer_trx_id invoice_id,
2462 aps.amount_due_remaining amount_due,
2463 fcc.priority,
2464 aps.payment_schedule_id,
2465 aps.cust_trx_type_id,
2466 aps.due_date,
2467 rct.trx_date invoice_date,
2468 rct.trx_number invoice_number,
2469 rct.invoice_currency_code
2470 FROM ra_customer_trx rct,
2471 ar_payment_schedules aps,
2472 fv_finance_charge_controls fcc
2473 WHERE rct.related_customer_trx_id = c_invoice_id
2474 AND aps.customer_trx_id = rct.customer_trx_id
2475 AND rct.interface_header_attribute3 = fcc.charge_type
2476 AND aps.amount_due_remaining > 0
2477 ORDER BY fcc.priority ;
2478
2479 BEGIN
2480 p_ErrorCode := g_SUCCESS;
2481 p_ErrorDesc := NULL;
2482 p_ErrorLoc := NULL;
2483
2484 debug_init (g_PackageName, l_module_name);
2485
2486 debug_msg (l_module_name, 'p_ReceiptNumber = '||p_ReceiptNumber);
2487 debug_msg (l_module_name, 'p_CashReceiptId = '||p_CashReceiptId);
2488 debug_msg (l_module_name, 'p_InvoiceId = '||p_InvoiceId);
2489 debug_msg (l_module_name, 'p_CurrencyCode = '||p_CurrencyCode);
2490 debug_msg (l_module_name, 'p_ExchangeRateDate = '||TO_CHAR(p_ExchangeRateDate, 'MM/DD/YYYY HH24:MI:SS'));
2491 debug_msg (l_module_name, 'p_ExchangeRate = '||p_ExchangeRate);
2492 debug_msg (l_module_name, 'p_ExchangeRateType = '||p_ExchangeRateType);
2493 debug_msg (l_module_name, 'p_ReceiptDate = '||TO_CHAR(p_ReceiptDate, 'MM/DD/YYYY HH24:MI:SS'));
2494 debug_msg (l_module_name, 'p_gldate = '||TO_CHAR(p_gldate, 'MM/DD/YYYY HH24:MI:SS'));
2495 debug_msg (l_module_name, 'p_RemaingReceiptAmount = '||p_RemaingReceiptAmount);
2496
2497
2498 --------------------------------------------------------------------------------------
2499 -- Get the Debit Memos for the Invoice --
2500 --------------------------------------------------------------------------------------
2501 FOR DebitMemo_Rec IN DebitMemo_Cur (p_InvoiceId) LOOP
2502 debug_msg (l_module_name, 'Processing Debit Memo <'||DebitMemo_Rec.invoice_number||'>');
2503 log_msg (l_module_name,'Processing Debit Memo <'||DebitMemo_Rec.invoice_number||'>');
2504
2505 --------------------------------------------------------------------------------------
2506 -- The API does not allow duplicate applications on the same invoice. --
2507 -- To avoid that, see if the debit memo was applied earlier due to a partial --
2508 -- application in the same Cash Receipt. If so unapply that amount and --
2509 -- apply once again with the total amount --
2510 --------------------------------------------------------------------------------------
2511 unapply_if_already_applied
2512 (
2513 p_ReceiptId => p_CashReceiptId,
2514 p_InvoiceId => DebitMemo_Rec.invoice_id,
2515 p_UnAppliedAmount => l_UnAppliedAmount,
2516 p_ErrorCode => p_ErrorCode,
2517 p_ErrorDesc => p_ErrorDesc,
2518 p_ErrorLoc => p_ErrorLoc
2519 );
2520
2521 IF (p_ErrorCode = g_SUCCESS) THEN
2522 IF (l_UnAppliedAmount <> 0) THEN
2523 debug_msg (l_module_name, 'Debit Memo Application <'||DebitMemo_Rec.invoice_number||'> Reversed for amount '||l_UnAppliedAmount||' for reapplication');
2524 --------------------------------------------------------------------------------------
2525 -- Change the Original Report Line in Output to Deleted --
2526 --------------------------------------------------------------------------------------
2527 del_report_line_for_a_receipt
2528 (
2529 p_InvoiceNumber => DebitMemo_Rec.invoice_number,
2530 p_ErrorCode => p_ErrorCode,
2531 p_ErrorDesc => p_ErrorDesc,
2532 p_ErrorLoc => p_ErrorLoc
2533 );
2534 END IF;
2535 END IF;
2536
2537 /*--- Commented Out for Transaction Codes Obsoletion-----------------------
2538 IF (p_ErrorCode = g_SUCCESS) THEN
2539 debug_msg (l_module_name, 'Calling get_receipt_txn_code');
2540 --------------------------------------------------------------------------------------
2541 -- Get the Transaction Code from the Mapping Table --
2542 --------------------------------------------------------------------------------------
2543 get_receipt_txn_code
2544 (
2545 p_DebitMemoId => DebitMemo_Rec.invoice_id,
2546 p_EffectiveDate => DebitMemo_Rec.invoice_date,
2547 p_ReceiptTxnCode => l_USSGLTransactionCode,
2548 p_ErrorCode => p_ErrorCode,
2549 p_ErrorDesc => p_ErrorDesc,
2550 p_ErrorLoc => p_ErrorLoc
2551 );
2552 END IF;
2553 --------End of Comments--------------------------------------------------*/
2554
2555 IF (p_ErrorCode = g_SUCCESS) THEN
2556 --------------------------------------------------------------------------------------
2557 -- Convert the Debit Memo Invoice Amount into the Receipt Currency Code. --
2558 --------------------------------------------------------------------------------------
2559 debug_msg (l_module_name, 'DebitMemo_Rec.invoice_currency_code='||DebitMemo_Rec.invoice_currency_code);
2560 IF (DebitMemo_Rec.invoice_currency_code <> p_CurrencyCode) THEN
2561 l_ExchangeRate := p_ExchangeRate;
2562 ELSE
2563 l_ExchangeRate := NULL;
2564 END IF;
2565
2566 l_ConvertedAmountDue := DebitMemo_Rec.amount_due*NVL(l_ExchangeRate, 1);
2567 debug_msg (l_module_name, 'Converted Amount Due is '||l_ConvertedAmountDue);
2568
2569 --------------------------------------------------------------------------------------
2570 -- Get the amount that needs receipt application --
2571 -- If the Remaining Receipt Amount is less than the Amount due then the whole --
2572 -- receipt amount is applied, else the amount due will be applied and the remaining --
2573 -- receipt amount will be reduced. --
2574 --------------------------------------------------------------------------------------
2575 IF (p_RemaingReceiptAmount <= l_ConvertedAmountDue) THEN
2576 l_AmountApplied := p_RemaingReceiptAmount;
2577 p_RemaingReceiptAmount := 0;
2578 ELSE
2579 l_AmountApplied := l_ConvertedAmountDue;
2580 p_RemaingReceiptAmount := p_RemaingReceiptAmount - l_ConvertedAmountDue;
2581 END IF;
2582
2583
2584 --------------------------------------------------------------------------------------
2585 -- Prepare the structure to call the API to apply against a receipt --
2586 --------------------------------------------------------------------------------------
2587 l_InvAmountApplied := l_AmountApplied/NVL(l_ExchangeRate, 1);
2588
2589 IF (DebitMemo_Rec.invoice_currency_code <> p_CurrencyCode) THEN
2590 l_ApplyCashRec.amount_applied := NULL;--l_InvAmountApplied+l_UnAppliedAmount;
2591 l_ApplyCashRec.amount_applied_from := l_AmountApplied+l_UnAppliedAmount;
2592 ELSE
2593 l_ApplyCashRec.amount_applied := l_AmountApplied+l_UnAppliedAmount;
2594 END IF;
2595
2596 l_ApplyCashRec.cash_receipt_id := p_CashReceiptId;
2597 l_ApplyCashRec.customer_trx_id := DebitMemo_Rec.invoice_id;
2598 l_ApplyCashRec.customer_trx_line_id := NULL;
2599 l_ApplyCashRec.applied_payment_schedule_id := DebitMemo_Rec.payment_schedule_id;
2600 l_ApplyCashRec.apply_date := p_ReceiptDate;
2601 l_ApplyCashRec.apply_gl_date := p_GLDate;
2602 -- l_ApplyCashRec.ussgl_transaction_code := l_USSGLTransactionCode;
2603 l_ApplyCashRec.trans_to_receipt_rate := l_ExchangeRate;
2604
2605 --------------------------------------------------------------------------------------
2606 -- This program calls the API for receipt application --
2607 --------------------------------------------------------------------------------------
2608 apply_cash_receipt
2609 (
2610 p_ApplyCashRec => l_ApplyCashRec,
2611 p_ErrorCode => p_ErrorCode,
2612 p_ErrorDesc => p_ErrorDesc,
2613 p_ErrorLoc => p_ErrorLoc
2614 );
2615
2616 IF (p_ErrorCode = g_SUCCESS) THEN
2617 --------------------------------------------------------------------------------------
2618 -- Process the Structure that generates the report for Output --
2619 --------------------------------------------------------------------------------------
2620 g_OutCashReceipts.total_applications := g_OutCashReceipts.total_applications + 1;
2621 g_OutReceiptApplications(g_OutCashReceipts.total_applications).status := 'A';
2622 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_number := DebitMemo_Rec.invoice_number;
2623 g_OutReceiptApplications(g_OutCashReceipts.total_applications).line_number := NULL;
2624 g_OutReceiptApplications(g_OutCashReceipts.total_applications).amt_applied_in_inv_curr := l_InvAmountApplied+l_UnAppliedAmount;
2625 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_currency := DebitMemo_Rec.invoice_currency_code;
2626 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_amount_due := DebitMemo_Rec.amount_due;
2627 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_amount := l_AmountApplied+l_UnAppliedAmount;
2628 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_currency := p_CurrencyCode;
2629 g_OutReceiptApplications(g_OutCashReceipts.total_applications).exchange_rate := NVL(l_ExchangeRate, 1);
2630 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_type := 'DEBIT MEMO';
2631 END IF;
2632 END IF;
2633
2634 IF (p_ErrorCode <> g_SUCCESS) THEN
2635 EXIT;
2636 END IF;
2637
2638 IF (p_RemaingReceiptAmount <= 0) THEN
2639 EXIT;
2640 END IF;
2641 END LOOP;
2642
2643 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
2644 EXCEPTION
2645 WHEN OTHERS THEN
2646 p_ErrorCode := g_FAILURE;
2647 p_ErrorDesc := SQLERRM;
2648 p_ErrorLoc := 'Final Exception';
2649 error
2650 (
2651 p_error_type => p_ErrorCode,
2652 p_pgm => l_module_name,
2653 p_msg => p_ErrorDesc,
2654 p_loc => p_ErrorLoc
2655 );
2656 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
2657 END pay_debit_memos;
2658
2659
2660 --****************************************************************************************--
2661 --* Name : process_receipts *--
2662 --* Type : Procedure *--
2663 --* Purpose : This is the procedure which starts processing a receipt batch *--
2664 --* Parameters : p_BatchRec IN The Complete record in fv_ar_batches for a batch_id*--
2665 --* : p_ErrorCode OUT The Error Code *--
2666 --* : p_ErrorDesc OUT The Error Description *--
2667 --* : p_ErrorLoc OUT The Error Location *--
2668 --* Global Vars : g_SUCCESS READ *--
2669 --* : g_OutCashReceipts WRITE *--
2670 --* Called from : main *--
2671 --* Calls : write_report_header *--
2672 --* : insert_ar_batch *--
2673 --* : create_cash_receipt *--
2674 --* : pay_debit_memos *--
2675 --* : pay_the_invoice *--
2676 --* : apply_on_account *--
2677 --* : write_report_for_a_receipt *--
2678 --* : debug_msg *--
2679 --* : debug_init *--
2680 --* : debug_exit *--
2681 --* : error *--
2682 --* : log_msg *--
2683 --* Tables Used : fv_interim_cash_receipts SELECT *--
2684 --* : ra_customer_trx SELECT *--
2685 --* : ra_customer_trx_lines SELECT *--
2686 --* : ra_customers SELECT *--
2687 --* : ar_payment_schedules SELECT *--
2688 --* : ra_cust_trx_types SELECT *--
2689 --* Logic : 1. Write the Batch Information into the output report. *--
2690 --* : 2. Insert the fv_ar_batch details into the table ar_batches by *--
2691 --* : calling the procedure insert_ar_batch *--
2692 --* : 3. For each of the receipt in the batch fv_ar_batches do the *--
2693 --* : following *--
2694 --* : 4. Initialize the receipt report variables *--
2695 --* : 5. If the invoice id is filled up, use the invoice and ignore the *--
2696 --* : customer details as the receipt is against the invoice, else the *--
2697 --* : receipt is made against all the invoices against the customer. *--
2698 --* : 6. Create a Cash Receipt. This Cash Receipt will be used for all the *--
2699 --* : receipt applications towards debit memos and the original invoices*--
2700 --* : 7. Get the outstanding Invoice details, either for the invoice or *--
2701 --* : for the customer. Do the following for each invoice obtained *--
2702 --* : 8. First Pay of all the outstanding debit memos *--
2703 --* : 9. Next pay of the invoice *--
2704 --* :10. If there is any balance left, apply it against On Account *--
2705 --* :11. Write the output report for a receipt *--
2706 --****************************************************************************************--
2707 PROCEDURE process_receipts
2708 (
2709 p_BatchRec IN fv_ar_batches%ROWTYPE,
2710 p_ErrorCode OUT NOCOPY VARCHAR2,
2711 p_ErrorDesc OUT NOCOPY VARCHAR2,
2712 p_ErrorLoc OUT NOCOPY VARCHAR2
2713 ) IS
2714 l_module_name VARCHAR2(30) := 'process_receipts';
2715
2716 l_SiteUseId ra_customer_trx.bill_to_site_use_id%TYPE;
2717 l_CustomerNumber hz_parties.party_id%TYPE;
2718 l_RemainingReceiptAmount fv_interim_cash_receipts.amount%TYPE;
2719 l_CashReceiptId ar_cash_receipts.cash_receipt_id%TYPE;
2720
2721 l_CreateCashRec CreateCashRecType;
2722 l_NullCreateCashRec CreateCashRecType;
2723 l_OnAccountRec OnAccountRecType;
2724
2725 l_OldInvoiceId NUMBER := 0;
2726
2727 CURSOR C_DistinctReceipts_Cursor
2728 (
2729 c_batch_id NUMBER
2730 ) IS
2731 SELECT ficr.receipt_number,
2732 ficr.customer_id,
2733 hzp.party_name customer_name,
2734 trunc(ficr.receipt_date) receipt_date,
2735 ficr.site_use_id,
2736 sum(ficr.amount) amount
2737 FROM fv_interim_cash_receipts ficr,
2738 hz_parties hzp, hz_cust_accounts hzca
2739 WHERE ficr.batch_id = c_batch_id
2740 AND hzp.party_id = hzca.party_id
2741 AND ficr.customer_id = hzca.cust_account_id
2742 GROUP BY ficr.receipt_number,
2743 ficr.customer_id,
2744 hzp.party_name,
2745 ficr.receipt_date,
2746 ficr.site_use_id
2747 ORDER BY ficr.receipt_number;
2748
2749 CURSOR C_Receipts_Cursor
2750 (
2751 c_batch_id NUMBER,
2752 c_receipt_number VARCHAR2,
2753 c_customer_id NUMBER,
2754 c_receipt_date DATE
2755 ) IS
2756 SELECT ficr.batch_id,
2757 ficr.currency_code,
2758 ficr.receipt_number,
2759 ficr.customer_id,
2760 ficr.special_type,
2761 ficr.status,
2762 ficr.customer_trx_id,
2763 trunc(ficr.gl_date) gl_date,
2764 SUM(ficr.amount) amount,
2765 ficr.site_use_id,
2766 ficr.ce_bank_acct_use_id, --PSKI changes for BA and MOAC Uptake
2767 ficr.set_of_books_id,
2768 trunc(ficr.receipt_date) receipt_date,
2769 ficr.related_invoice_id,
2770 ficr.receipt_method_id,
2771 ficr.payment_schedule_id,
2772 -- ficr.ussgl_transaction_code,
2773 ficr.org_id,
2774 ficr.customer_trx_line_id,
2775 rct.trx_number invoice_number,
2776 rct.invoice_currency_code,
2777 rct.exchange_rate_type invoice_exchange_rate_type,
2778 rctl.line_number line_number,
2779 hzp.party_name,
2780 rctl.extended_amount line_amount
2781 FROM fv_interim_cash_receipts ficr,
2782 ra_customer_trx rct,
2783 ra_customer_trx_lines rctl,
2784 hz_parties hzp, hz_cust_accounts hzca
2785 WHERE ficr.batch_id = c_batch_id
2786 AND hzp.party_id = hzca.party_id
2787 AND ficr.receipt_number = c_receipt_number
2788 AND ficr.customer_id = c_customer_id
2789 AND ficr.receipt_date = c_receipt_date
2790 AND rct.customer_trx_id (+) = ficr.customer_trx_id
2791 AND rctl.customer_trx_line_id (+) = ficr.customer_trx_line_id
2792 AND hzca.cust_account_id (+) =ficr.customer_id
2793 GROUP BY
2794 ficr.batch_id,
2795 ficr.currency_code,
2796 ficr.receipt_number,
2797 ficr.customer_id,
2798 ficr.special_type,
2799 ficr.status,
2800 ficr.customer_trx_id,
2801 trunc(ficr.gl_date),
2802 ficr.site_use_id,
2803 ficr.ce_bank_acct_use_id, --PSKI changes for BA and MOAC Uptake
2804 ficr.set_of_books_id,
2805 trunc(ficr.receipt_date) ,
2806 ficr.related_invoice_id,
2807 ficr.receipt_method_id,
2808 ficr.payment_schedule_id,
2809 -- ficr.ussgl_transaction_code,
2810 ficr.org_id,
2811 ficr.customer_trx_line_id,
2812 rct.trx_number ,
2813 rct.invoice_currency_code,
2814 rct.exchange_rate_type ,
2815 rctl.line_number ,
2816 hzp.party_name,
2817 rctl.extended_amount
2818
2819 ORDER BY rct.trx_number ASC,
2820 rctl.line_number DESC;
2821
2822 CURSOR C_Invoices_Cursor
2823 (
2824 c_cust_no NUMBER,
2825 c_invoice_id NUMBER,
2826 c_sob NUMBER,
2827 c_currency VARCHAR2,
2828 c_site_use_id NUMBER
2829 ) IS
2830 SELECT aps.customer_trx_id,
2831 aps.amount_due_remaining amount_due,
2832 aps.payment_schedule_id,
2833 aps.cust_trx_type_id,
2834 aps.due_date,
2835 aps.trx_number invoice_number,
2836 rac.invoice_currency_code
2837 FROM ar_payment_schedules aps,
2838 ra_cust_trx_types rct,
2839 ra_customer_trx rac
2840 WHERE aps.amount_due_remaining > 0
2841 AND aps.status = 'OP'
2842 AND aps.customer_id = NVL(c_cust_no,aps.customer_id)
2843 AND aps.customer_trx_id = NVL(c_invoice_id,aps.customer_trx_id)
2844 AND aps.cust_trx_type_id = rct.cust_trx_type_id
2845 AND rct.type = 'INV'
2846 AND aps.customer_trx_id = rac.customer_trx_id
2847 AND rac.bill_to_site_use_id = nvl(c_site_use_id,rac.bill_to_site_use_id)
2848 AND rac.set_of_books_id = c_sob
2849 AND rac.invoice_currency_code = c_currency
2850 ORDER BY aps.customer_trx_id,
2851 payment_schedule_id;
2852
2853 BEGIN
2854 p_ErrorCode := g_SUCCESS;
2855 p_ErrorDesc := NULL;
2856 p_ErrorLoc := NULL;
2857
2858 debug_init (g_PackageName, l_module_name);
2859
2860 debug_msg (l_module_name, 'Calling write_report_header');
2861 --------------------------------------------------------------------------------------
2862 -- Write the Report header. i.e. the batch details will be written at this point --
2863 --------------------------------------------------------------------------------------
2864 write_report_header
2865 (
2866 p_BatchRec => p_BatchRec,
2867 p_ErrorCode => p_ErrorCode,
2868 p_ErrorDesc => p_ErrorDesc,
2869 p_ErrorLoc => p_ErrorLoc
2870 );
2871
2872 IF (p_ErrorCode = g_SUCCESS) THEN
2873 debug_msg (l_module_name, 'Calling insert_ar_batch');
2874 --------------------------------------------------------------------------------------
2875 -- Insert the fv_ar_batch details into ar_batch table. Currently there is no API --
2876 -- that does this. Until then a direct insert into the table is done --
2877 --------------------------------------------------------------------------------------
2878 insert_ar_batch
2879 (
2880 p_BatchRec => p_BatchRec,
2881 p_ErrorCode => p_ErrorCode,
2882 p_ErrorDesc => p_ErrorDesc,
2883 p_ErrorLoc => p_ErrorLoc
2884 );
2885 END IF;
2886
2887 IF (p_ErrorCode = g_SUCCESS) THEN
2888
2889 --------------------------------------------------------------------------------------
2890 -- Get Distinct Cash Receipts --
2891 --------------------------------------------------------------------------------------
2892 FOR DisctinctReceiptsRec IN C_DistinctReceipts_Cursor (p_BatchRec.batch_id) LOOP
2893
2894 --------------------------------------------------------------------------------------
2895 -- Initialize Receipt Report Variables --
2896 --------------------------------------------------------------------------------------
2897 g_OutCashReceipts.receipt_number := DisctinctReceiptsRec.receipt_number;
2898 g_OutCashReceipts.customer_name := DisctinctReceiptsRec.customer_name;
2899 g_OutCashReceipts.receipt_amount := DisctinctReceiptsRec.amount;
2900 g_OutCashReceipts.total_applications := 0;
2901 g_OutCashReceipts.total_errors := 0;
2902
2903 --------------------------------------------------------------------------------------
2904 -- Create a Cash Receipt. This Cash Receipt will be used for all the receipt --
2905 -- applications towards debit memos and the original invoices. --
2906 --------------------------------------------------------------------------------------
2907 l_CreateCashRec := l_NullCreateCashRec;
2908 l_CreateCashRec.receipt_number := DisctinctReceiptsRec.receipt_number;
2909 l_CreateCashRec.receipt_date := DisctinctReceiptsRec.receipt_date;
2910 l_CreateCashRec.gl_date := trunc(p_BatchRec.gl_date);
2911 l_CreateCashRec.currency_code := p_BatchRec.currency_code;
2912 l_CreateCashRec.exchange_rate := p_BatchRec.exchange_rate;
2913 l_CreateCashRec.exchange_rate_type := p_BatchRec.exchange_rate_type;
2914 l_CreateCashRec.exchange_rate_date := p_BatchRec.exchange_date;
2915 l_CreateCashRec.amount := DisctinctReceiptsRec.amount;
2916 l_CreateCashRec.receipt_method_id := p_BatchRec.receipt_method_id;
2917 l_CreateCashRec.customer_id := DisctinctReceiptsRec.customer_id;
2918 -- l_CreateCashRec.customer_bank_account_id := DisctinctReceiptsRec.bank_account_id;
2919 l_CreateCashRec.customer_site_use_id := DisctinctReceiptsRec.site_use_id;
2920 l_CreateCashRec.deposit_date := p_BatchRec.deposit_date;
2921 -- l_CreateCashRec.ussgl_transaction_code := p_BatchRec.ussgl_transaction_code;
2922 l_CreateCashRec.org_id := p_BatchRec.org_id;
2923
2924 debug_msg (l_module_name, 'Calling create_cash_receipt');
2925 create_cash_receipt
2926 (
2927 p_BatchId => p_BatchRec.batch_id,
2928 p_CreateCashRec => l_CreateCashRec,
2929 p_CashReceiptId => l_CashReceiptId,
2930 p_ErrorCode => p_ErrorCode,
2931 p_ErrorDesc => p_ErrorDesc,
2932 p_ErrorLoc => p_ErrorLoc
2933 );
2934
2935 --------------------------------------------------------------------------------------
2936 -- Get Applications for the same Cash Receipt --
2937 --------------------------------------------------------------------------------------
2938 FOR ReceiptsRec IN C_Receipts_Cursor
2939 (
2940 p_BatchRec.batch_id,
2941 DisctinctReceiptsRec.receipt_number,
2942 DisctinctReceiptsRec.customer_id,
2943 DisctinctReceiptsRec.receipt_date
2944 ) LOOP
2945 debug_msg (l_module_name, 'Currently Processing Receipt Number <'||ReceiptsRec.receipt_number||'>');
2946 log_msg (l_module_name,'Currently Processing Receipt Number <'||ReceiptsRec.receipt_number||'>');
2947
2948 --------------------------------------------------------------------------------------
2949 -- If the invoice id is filled up, use the invoice and ignore the customer details --
2950 -- as the receipt is against the invoice, else the receipt is made against all the --
2951 -- invoices against the customer and site use id. --
2952 --------------------------------------------------------------------------------------
2953 IF (ReceiptsRec.customer_trx_id IS NOT NULL) THEN
2954 debug_msg (l_module_name, 'Customer Id forced to NULL');
2955 l_CustomerNumber := NULL;
2956 l_SiteUseId := NULL;
2957 ELSE
2958 debug_msg (l_module_name, 'Customer Id is '||ReceiptsRec.customer_id);
2959 l_CustomerNumber := ReceiptsRec.customer_id;
2960 l_SiteUseId := ReceiptsRec.site_use_id;
2961 END IF;
2962
2963
2964 IF (p_ErrorCode = g_SUCCESS) THEN
2965 l_RemainingReceiptAmount := ReceiptsRec.amount;
2966
2967 --------------------------------------------------------------------------------------
2968 -- Get the outstanding Invoice details, either for the invoice or for the customer --
2969 --------------------------------------------------------------------------------------
2970
2971 l_OldInvoiceId := 0;
2972 FOR InvoiceRec IN C_Invoices_Cursor
2973 (
2974 l_CustomerNumber,
2975 ReceiptsRec.customer_trx_id,
2976 p_BatchRec.set_of_books_id,
2977 p_BatchRec.currency_code,
2978 l_SiteUseId
2979 ) LOOP
2980 log_msg (l_module_name,'Currently Processing DM for Invoice <'||InvoiceRec.invoice_number||'>');
2981 IF (l_OldInvoiceId <> InvoiceRec.customer_trx_id) THEN
2982 IF (l_RemainingReceiptAmount > 0) THEN
2983 --------------------------------------------------------------------------------------
2984 -- First Pay of all the outstanding debit memos --
2985 --------------------------------------------------------------------------------------
2986 pay_debit_memos
2987 (
2988 p_ReceiptNumber => ReceiptsRec.receipt_number,
2989 p_CashReceiptId => l_CashReceiptId,
2990 p_CurrencyCode => ReceiptsRec.currency_code,
2991 p_ExchangeRateDate => p_BatchRec.exchange_date,
2992 p_ExchangeRate => p_BatchRec.exchange_rate,
2993 p_ExchangeRateType => p_BatchRec.exchange_rate_type,
2994 p_InvoiceId => InvoiceRec.customer_trx_id,
2995 p_ReceiptDate => ReceiptsRec.receipt_date,
2996 p_GLDate => ReceiptsRec.gl_date,
2997 p_RemaingReceiptAmount => l_RemainingReceiptAmount,
2998 p_ErrorCode => p_ErrorCode,
2999 p_ErrorDesc => p_ErrorDesc,
3000 p_ErrorLoc => p_ErrorLoc
3001 );
3002 END IF;
3003 END IF;
3004
3005 l_OldInvoiceId := InvoiceRec.customer_trx_id;
3006 IF (p_ErrorCode <> g_SUCCESS) THEN
3007 EXIT;
3008 END IF;
3009 End loop;
3010
3011 l_OldInvoiceId := 0;
3012 log_msg (l_module_name,'Applied Receipts to DMs and Processing Invoice');
3013 IF (p_ErrorCode = g_SUCCESS) THEN
3014 FOR InvoiceRec IN C_Invoices_Cursor
3015 ( l_CustomerNumber,
3016 ReceiptsRec.customer_trx_id,
3017 p_BatchRec.set_of_books_id,
3018 p_BatchRec.currency_code,
3019 l_SiteUseId
3020 ) LOOP
3021 IF (l_OldInvoiceId <> InvoiceRec.customer_trx_id) THEN
3022 IF (l_RemainingReceiptAmount > 0) THEN
3023 --------------------------------------------------------------------------------------
3024 -- Next pay of the Invoice --
3025 --------------------------------------------------------------------------------------
3026 log_msg (l_module_name,'Currently Processing Invoice <'||InvoiceRec.invoice_number||'>');
3027 log_msg( l_module_name,'Remaining amount ' || l_RemainingReceiptAmount);
3028 pay_the_invoice
3029 (
3030 p_ReceiptNumber => ReceiptsRec.receipt_number,
3031 p_CashReceiptId => l_CashReceiptId,
3032 p_InvoiceNumber => InvoiceRec.invoice_number,
3033 p_LineNumber => ReceiptsRec.line_number,
3034 p_InvoiceId => InvoiceRec.customer_trx_id,
3035 p_InvoiceLineId => ReceiptsRec.customer_trx_line_id,
3036 p_CurrencyCode => ReceiptsRec.currency_code,
3037 p_InvoiceCurrencyCode => InvoiceRec.invoice_currency_code,
3038 p_ExchangeRateDate => p_BatchRec.exchange_date,
3039 p_ExchangeRate => p_BatchRec.exchange_rate,
3040 p_ExchangeRateType => p_BatchRec.exchange_rate_type,
3041 p_PaymentScheduleId => InvoiceRec.payment_schedule_id,
3042 p_InvoiceAmount => InvoiceRec.amount_due,
3043 p_InvoiceLineAmount => ReceiptsRec.line_amount,
3044 p_ReceiptDate => ReceiptsRec.receipt_date,
3045 p_GLDate => ReceiptsRec.gl_date,
3046 p_RemaingReceiptAmount => l_RemainingReceiptAmount,
3047 -- p_USSGLTransactionCode => ReceiptsRec.ussgl_transaction_code,
3048 p_org_id => ReceiptsRec.org_id,
3049 p_ErrorCode => p_ErrorCode,
3050 p_ErrorDesc => p_ErrorDesc,
3051 p_ErrorLoc => p_ErrorLoc
3052 );
3053 END IF;
3054 END IF;
3055
3056 l_OldInvoiceId := InvoiceRec.customer_trx_id;
3057
3058 IF (p_ErrorCode <> g_SUCCESS) THEN
3059 EXIT;
3060 END IF;
3061 END LOOP;
3062 End if;
3063
3064 IF (p_ErrorCode = g_SUCCESS) THEN
3065 IF (l_RemainingReceiptAmount > 0) THEN
3066 --------------------------------------------------------------------------------------
3067 -- After all the pay off there is still balance left --
3068 --------------------------------------------------------------------------------------
3069
3070 l_OnAccountRec.cash_receipt_id := l_CashReceiptId;
3071 l_OnAccountRec.receipt_number := ReceiptsRec.receipt_number;
3072 l_OnAccountRec.amount_applied := l_RemainingReceiptAmount;
3073 l_OnAccountRec.apply_date := ReceiptsRec.receipt_date;
3074 l_OnAccountRec.apply_gl_date := ReceiptsRec.gl_date;
3075 -- l_OnAccountRec.ussgl_transaction_code := ReceiptsRec.ussgl_transaction_code;
3076
3077 --------------------------------------------------------------------------------------
3078 -- Apply the remaining amount to On Account --
3079 --------------------------------------------------------------------------------------
3080 apply_on_account
3081 (
3082 p_OnAccountRec => l_OnAccountRec,
3083 p_ErrorCode => p_ErrorCode,
3084 p_ErrorDesc => p_ErrorDesc,
3085 p_ErrorLoc => p_ErrorLoc
3086 );
3087
3088 IF (p_ErrorCode = g_SUCCESS) THEN
3089 --------------------------------------------------------------------------------------
3090 -- Initialize the Report Variables for the On Account Application --
3091 --------------------------------------------------------------------------------------
3092 g_OutCashReceipts.total_applications := g_OutCashReceipts.total_applications + 1;
3093 g_OutReceiptApplications(g_OutCashReceipts.total_applications).status := 'A';
3094 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_number := 'On Account';
3095 g_OutReceiptApplications(g_OutCashReceipts.total_applications).line_number := NULL;
3096 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_amount := l_RemainingReceiptAmount;
3097 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_type := 'ON ACCOUNT';
3098 g_OutReceiptApplications(g_OutCashReceipts.total_applications).amt_applied_in_inv_curr := l_RemainingReceiptAmount;
3099 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_currency := '';
3100 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_amount_due := 0;
3101 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_currency := '';
3102 g_OutReceiptApplications(g_OutCashReceipts.total_applications).exchange_rate := '';
3103 END IF;
3104 END IF;
3105 END IF;
3106
3107 END IF;
3108
3109 debug_msg (l_module_name, 'p_ErrorCode(1)='||p_ErrorCode);
3110 IF (p_ErrorCode <> g_SUCCESS) THEN
3111 g_ErrorFound := TRUE;
3112 END IF;
3113
3114 END LOOP;
3115
3116 debug_msg (l_module_name, 'Calling write_report_for_a_receipt');
3117 --------------------------------------------------------------------------------------
3118 -- Write the output report for a receipt --
3119 --------------------------------------------------------------------------------------
3120 write_report_for_a_receipt
3121 (
3122 p_ErrorCode => p_ErrorCode,
3123 p_ErrorDesc => p_ErrorDesc,
3124 p_ErrorLoc => p_ErrorLoc
3125 );
3126
3127 END LOOP;
3128 END IF;
3129
3130 IF (p_ErrorCode <> g_SUCCESS) THEN
3131 g_ErrorFound := TRUE;
3132 END IF;
3133
3134 IF (g_ErrorFound = TRUE) THEN
3135 p_ErrorCode := g_FAILURE;
3136 END IF;
3137
3138 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
3139 EXCEPTION
3140 WHEN OTHERS THEN
3141 p_ErrorCode := g_FAILURE;
3142 p_ErrorDesc := SQLERRM;
3143 p_ErrorLoc := 'Final Exception';
3144 error
3145 (
3146 p_error_type => p_ErrorCode,
3147 p_pgm => l_module_name,
3148 p_msg => p_ErrorDesc,
3149 p_loc => p_ErrorLoc
3150 );
3151 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
3152 END process_receipts;
3153
3154 --****************************************************************************************--
3155 --* Name : main *--
3156 --* Type : Procedure *--
3157 --* Purpose : This is the main procedure *--
3158 --* Parameters : p_errbuf OUT The Concurrent Program Error Buffer *--
3159 --* : p_retcode OUT The Concurrent Program Return Code *--
3160 --* : p_batch_name IN The Input Receipt Batch name *--
3161 --* Global Vars : g_SUCCESS READ *--
3162 --* Called from : Concurrent Program *--
3163 --* Calls : init *--
3164 --* : process_receipts *--
3165 --* : update_fv_batch_status *--
3166 --* : debug_msg *--
3167 --* : debug_init *--
3168 --* : debug_exit *--
3169 --* : error *--
3170 --* Tables Used : fv_ar_batches SELECT *--
3171 --* Logic : 1. Given the batch name get the record from table fv_ar_batches *--
3172 --* : 2. Call process_receipts to Start Processing the receipts in the *--
3173 --* : batch. *--
3174 --* : 3. Call update_fv_batch_status to update the batch status *--
3175 --****************************************************************************************--
3176 PROCEDURE main
3177 (
3178 p_errbuf OUT NOCOPY VARCHAR2,
3179 p_retcode OUT NOCOPY VARCHAR2,
3180 p_batch_name IN VARCHAR2
3181 ) IS
3182 l_module_name VARCHAR2(30) := 'main';
3183
3184 l_ErrorCode NUMBER;
3185 l_ErrorDesc VARCHAR2(1024);
3186 l_ErrorLoc VARCHAR2(1024);
3187
3188 l_BatchRec fv_ar_batches%ROWTYPE;
3189 BEGIN
3190 l_ErrorCode := g_SUCCESS;
3191 l_ErrorDesc := '';
3192 l_ErrorLoc := '';
3193
3194 ----------------------------------------------------------------------
3195 -- Initialize
3196 ----------------------------------------------------------------------
3197 -- init;
3198 -- debug_init (g_PackageName, l_module_name);
3199
3200 debug_msg (l_module_name, 'p_batch_name = '||p_batch_name);
3201 log_msg (l_module_name,'p_batch_name = '||p_batch_name);
3202
3203 ----------------------------------------------------------------------
3204 -- Get the batch details from fv_ar_batches given the batch name
3205 -- If there is no data found then it is an error.
3206 ----------------------------------------------------------------------
3207 IF (l_ErrorCode = g_SUCCESS) THEN
3208 BEGIN
3209 debug_msg (l_module_name, 'Getting the Batch Details');
3210 SELECT *
3211 INTO l_BatchRec
3212 FROM fv_ar_batches fab
3213 WHERE batch_name = p_batch_name;
3214 EXCEPTION
3215 WHEN NO_DATA_FOUND THEN
3216 l_ErrorCode := g_FAILURE;
3217 l_ErrorDesc := 'No Batch with name <'||p_batch_name||'> Exists.';
3218 l_ErrorLoc := l_module_name || ':' || 'SELECT fv_ar_batches';
3219 error
3220 (
3221 p_error_type => l_ErrorCode,
3222 p_pgm => l_module_name,
3223 p_msg => l_ErrorDesc,
3224 p_loc => l_ErrorLoc
3225 );
3226 debug_msg (l_module_name, 'No Data Found for the batch <'||p_batch_name);
3227 WHEN OTHERS THEN
3228 l_ErrorCode := g_FAILURE;
3229 l_ErrorDesc := SQLERRM;
3230 l_ErrorLoc := l_module_name || ':' || 'SELECT fv_ar_batches';
3231 error
3232 (
3233 p_error_type => l_ErrorCode,
3234 p_pgm => l_module_name,
3235 p_msg => l_ErrorDesc,
3236 p_loc => l_ErrorLoc
3237 );
3238 debug_msg (l_module_name, l_ErrorDesc||'at location'||l_ErrorLoc);
3239 END;
3240 END IF;
3241
3242 IF (l_ErrorCode = g_SUCCESS) THEN
3243 debug_msg (l_module_name, 'Calling process_receipts');
3244 ----------------------------------------------------------------------
3245 -- Call process_receipts to Start Processing the receipts in the --
3246 -- batch. --
3247 ----------------------------------------------------------------------
3248 process_receipts
3249 (
3250 p_BatchRec => l_BatchRec,
3251 p_ErrorCode => l_ErrorCode,
3252 p_ErrorDesc => l_ErrorDesc,
3253 p_ErrorLoc => l_ErrorLoc
3254 );
3255 END IF;
3256
3257 IF (l_ErrorCode = g_SUCCESS) THEN
3258 debug_msg (l_module_name, 'Calling update_fv_batch_status with SUCCESS');
3259
3260 ----------------------------------------------------------------------
3261 -- The Process was successful, hence update with status as --
3262 -- COMPLETED. --
3263 ----------------------------------------------------------------------
3264 update_fv_batch_status
3265 (
3266 p_BatchId => l_BatchRec.batch_id,
3267 p_Status => 'COMPLETED',
3268 p_ErrorCode => l_ErrorCode,
3269 p_ErrorDesc => l_ErrorDesc,
3270 p_ErrorLoc => l_ErrorLoc
3271 );
3272 ELSE
3273 ROLLBACK;
3274 debug_msg (l_module_name, 'Calling update_fv_batch_status with FAILURE');
3275 ----------------------------------------------------------------------
3276 -- The Process was failure, hence update with status as --
3277 -- NEEDS RESUB. --
3278 ----------------------------------------------------------------------
3279 update_fv_batch_status
3280 (
3281 p_BatchId => l_BatchRec.batch_id,
3282 p_Status => 'NEEDS RESUBMISSION',
3283 p_ErrorCode => l_ErrorCode,
3284 p_ErrorDesc => l_ErrorDesc,
3285 p_ErrorLoc => l_ErrorLoc
3286 );
3287
3288 IF (l_ErrorCode = g_SUCCESS) THEN
3289 l_ErrorCode := g_FAILURE;
3290 END IF;
3291 END IF;
3292
3293 COMMIT;
3294
3295 p_retcode := l_ErrorCode;
3296 p_errbuf := l_ErrorDesc;
3297 debug_exit (l_ErrorCode, l_ErrorDesc, l_ErrorLoc);
3298 -- error_write;
3299 -- log_write;
3300 -- debug_write;
3301 EXCEPTION
3302 WHEN OTHERS THEN
3303 l_ErrorCode := g_FAILURE;
3304 l_ErrorDesc := SQLERRM;
3305 l_ErrorLoc := l_module_name || ':' || 'Final Exception';
3306 error
3307 (
3308 p_error_type => l_ErrorCode,
3309 p_pgm => l_module_name,
3310 p_msg => l_ErrorDesc,
3311 p_loc => l_ErrorLoc
3312 );
3313 debug_exit (l_ErrorCode, l_ErrorDesc, l_ErrorLoc);
3314 p_retcode := l_ErrorCode;
3315 p_errbuf := l_ErrorDesc;
3316 -- error_write;
3317 -- log_write;
3318 -- debug_write;
3319 ROLLBACK;
3320 END main;
3321 END fv_apply_cash_receipt;