[Home] [Help]
PACKAGE BODY: APPS.FV_APPLY_CASH_RECEIPT
Source
1 PACKAGE BODY fv_apply_cash_receipt AS
2 --$Header: FVXDCCRB.pls 120.18.12010000.3 2008/09/04 15:30:47 sasukuma 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************************');
326 fnd_file.put_line (fnd_file.log, '*******************************************************');
323 FOR l_Counter IN 1..g_MaxErrorMessages LOOP
324 fnd_file.put_line(fnd_file.log, g_ErrorMessages(l_Counter));
325 END LOOP;
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;
427 */
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');
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
520 BEGIN
517 ) IS
518 l_module_name VARCHAR2(30) := 'write_report_header';
519 l_WroteErrorHeader BOOLEAN := FALSE;
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);
633 output (' '||RPAD('=', 24, '='));
630 output (' Receipt Amount: '||g_OutCashReceipts.receipt_amount);
631 output (' ');
632
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'));
731 debug_msg (l_module_name, 'batch_source_id => '|| p_ARBatchRec.batch_source_id);
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);
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,
880 attribute1,
877 exchange_date,
878 exchange_rate_type,
879 attribute_category,
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. *--
994 p_OnAccountRec IN OnAccountRecType,
991 --****************************************************************************************--
992 PROCEDURE apply_on_account
993 (
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');
1090 (
1087 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1088 g_OutErrorInfo(g_OutCashReceipts.total_errors).error_desc := l_MessageData;
1089 error
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
1188 l_ReceivableApplicationId
1185 SELECT ara.amount_applied,
1186 ara.receivable_application_id
1187 INTO l_PreviousAmount,
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,
1302 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1299 p_msg => p_ErrorDesc,
1300 p_loc => p_ErrorLoc
1301 );
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);
1380 debug_msg (l_module_name, 'p_link_to_trx_hist_id => '||p_ApplyCashRec.link_to_trx_hist_id);
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);
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)');
1455
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);
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 --
1543 g_OutCashReceipts.total_errors := g_OutCashReceipts.total_errors + 1;
1540 ----------------------------------------------------------------------
1541 FOR l_Counter IN 1..l_MessageCount LOOP
1542 l_MessageData := fnd_msg_pub.get (p_encoded => 'F');
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
1653 p_ErrorCode := g_FAILURE;
1650 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
1651 EXCEPTION
1652 WHEN OTHERS THEN
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 *--
1763 --* : g_OutErrorInfo WRITE *--
1760 --* : p_ErrorDesc OUT The Error Description *--
1761 --* : p_ErrorLoc OUT The Error Location *--
1762 --* Global Vars : g_SUCCESS READ *--
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);
1841 debug_msg (l_module_name, ' p_customer_bank_account_num => '||p_CreateCashRec.customer_bank_account_num);
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);
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,
1908 p_ussgl_transaction_code => null,
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,
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 *--
2014 --* : p_InvoiceLineId IN Invoice Line id *--
2011 --* : p_CashReceiptId IN Cash Receipt Id *--
2012 --* : p_InvoiceNumber IN Invoice Number *--
2013 --* : p_InvoiceId IN Invoice 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
2172 l_ApplyCashRec.line_number := p_LineNumber;
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;
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 *--
2272 --* : p_EffectiveDate IN Effective Date *--
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 *--
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';
2381 p_pgm => l_module_name,
2378 error
2379 (
2380 p_error_type => p_ErrorCode,
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;
2461 SELECT distinct aps.customer_trx_id invoice_id,
2458 l_UnAppliedAmount NUMBER := 0;
2459
2460 CURSOR DebitMemo_Cur (c_invoice_id NUMBER) IS
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
2564 END IF;
2561 l_ExchangeRate := p_ExchangeRate;
2562 ELSE
2563 l_ExchangeRate := NULL;
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 *--
2665 --* : p_ErrorCode OUT The Error Code *--
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*--
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,
2749 CURSOR C_Receipts_Cursor
2746 ficr.site_use_id
2747 ORDER BY ficr.receipt_number;
2748
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 (
2883 p_ErrorLoc => p_ErrorLoc
2880 p_BatchRec => p_BatchRec,
2881 p_ErrorCode => p_ErrorCode,
2882 p_ErrorDesc => p_ErrorDesc,
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,
2979 ) LOOP
2976 p_BatchRec.set_of_books_id,
2977 p_BatchRec.currency_code,
2978 l_SiteUseId
2980 log_msg (l_module_name,'Currently Processing 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
3007 IF (p_ErrorCode = g_SUCCESS) THEN
3008 IF (l_RemainingReceiptAmount > 0) THEN
3009 --------------------------------------------------------------------------------------
3010 -- Next pay of the Invoice --
3011 --------------------------------------------------------------------------------------
3012 pay_the_invoice
3013 (
3014 p_ReceiptNumber => ReceiptsRec.receipt_number,
3015 p_CashReceiptId => l_CashReceiptId,
3016 p_InvoiceNumber => InvoiceRec.invoice_number,
3017 p_LineNumber => ReceiptsRec.line_number,
3018 p_InvoiceId => InvoiceRec.customer_trx_id,
3019 p_InvoiceLineId => ReceiptsRec.customer_trx_line_id,
3020 p_CurrencyCode => ReceiptsRec.currency_code,
3021 p_InvoiceCurrencyCode => InvoiceRec.invoice_currency_code,
3022 p_ExchangeRateDate => p_BatchRec.exchange_date,
3023 p_ExchangeRate => p_BatchRec.exchange_rate,
3024 p_ExchangeRateType => p_BatchRec.exchange_rate_type,
3025 p_PaymentScheduleId => InvoiceRec.payment_schedule_id,
3026 p_InvoiceAmount => InvoiceRec.amount_due,
3027 p_InvoiceLineAmount => ReceiptsRec.line_amount,
3028 p_ReceiptDate => ReceiptsRec.receipt_date,
3029 p_GLDate => ReceiptsRec.gl_date,
3030 p_RemaingReceiptAmount => l_RemainingReceiptAmount,
3031 -- p_USSGLTransactionCode => ReceiptsRec.ussgl_transaction_code,
3032 p_org_id => ReceiptsRec.org_id,
3033 p_ErrorCode => p_ErrorCode,
3034 p_ErrorDesc => p_ErrorDesc,
3035 p_ErrorLoc => p_ErrorLoc
3036 );
3037 END IF;
3038 END IF;
3039
3040 IF (p_ErrorCode <> g_SUCCESS) THEN
3041 EXIT;
3042 END IF;
3043 END LOOP;
3044
3045 IF (p_ErrorCode = g_SUCCESS) THEN
3046 IF (l_RemainingReceiptAmount > 0) THEN
3047 --------------------------------------------------------------------------------------
3048 -- After all the pay off there is still balance left --
3049 --------------------------------------------------------------------------------------
3050
3051 l_OnAccountRec.cash_receipt_id := l_CashReceiptId;
3052 l_OnAccountRec.receipt_number := ReceiptsRec.receipt_number;
3053 l_OnAccountRec.amount_applied := l_RemainingReceiptAmount;
3054 l_OnAccountRec.apply_date := ReceiptsRec.receipt_date;
3055 l_OnAccountRec.apply_gl_date := ReceiptsRec.gl_date;
3056 -- l_OnAccountRec.ussgl_transaction_code := ReceiptsRec.ussgl_transaction_code;
3057
3058 --------------------------------------------------------------------------------------
3059 -- Apply the remaining amount to On Account --
3060 --------------------------------------------------------------------------------------
3061 apply_on_account
3062 (
3063 p_OnAccountRec => l_OnAccountRec,
3064 p_ErrorCode => p_ErrorCode,
3065 p_ErrorDesc => p_ErrorDesc,
3066 p_ErrorLoc => p_ErrorLoc
3067 );
3068
3069 IF (p_ErrorCode = g_SUCCESS) THEN
3073 g_OutCashReceipts.total_applications := g_OutCashReceipts.total_applications + 1;
3070 --------------------------------------------------------------------------------------
3071 -- Initialize the Report Variables for the On Account Application --
3072 --------------------------------------------------------------------------------------
3074 g_OutReceiptApplications(g_OutCashReceipts.total_applications).status := 'A';
3075 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_number := 'On Account';
3076 g_OutReceiptApplications(g_OutCashReceipts.total_applications).line_number := NULL;
3077 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_amount := l_RemainingReceiptAmount;
3078 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_type := 'ON ACCOUNT';
3079 g_OutReceiptApplications(g_OutCashReceipts.total_applications).amt_applied_in_inv_curr := l_RemainingReceiptAmount;
3080 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_currency := '';
3081 g_OutReceiptApplications(g_OutCashReceipts.total_applications).invoice_amount_due := 0;
3082 g_OutReceiptApplications(g_OutCashReceipts.total_applications).applied_currency := '';
3083 g_OutReceiptApplications(g_OutCashReceipts.total_applications).exchange_rate := '';
3084 END IF;
3085 END IF;
3086 END IF;
3087
3088 END IF;
3089
3090 debug_msg (l_module_name, 'p_ErrorCode(1)='||p_ErrorCode);
3091 IF (p_ErrorCode <> g_SUCCESS) THEN
3092 g_ErrorFound := TRUE;
3093 END IF;
3094
3095 END LOOP;
3096
3097 debug_msg (l_module_name, 'Calling write_report_for_a_receipt');
3098 --------------------------------------------------------------------------------------
3099 -- Write the output report for a receipt --
3100 --------------------------------------------------------------------------------------
3101 write_report_for_a_receipt
3102 (
3103 p_ErrorCode => p_ErrorCode,
3104 p_ErrorDesc => p_ErrorDesc,
3105 p_ErrorLoc => p_ErrorLoc
3106 );
3107
3108 END LOOP;
3109 END IF;
3110
3111 IF (p_ErrorCode <> g_SUCCESS) THEN
3112 g_ErrorFound := TRUE;
3113 END IF;
3114
3115 IF (g_ErrorFound = TRUE) THEN
3116 p_ErrorCode := g_FAILURE;
3117 END IF;
3118
3119 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
3120 EXCEPTION
3121 WHEN OTHERS THEN
3122 p_ErrorCode := g_FAILURE;
3123 p_ErrorDesc := SQLERRM;
3124 p_ErrorLoc := 'Final Exception';
3125 error
3126 (
3127 p_error_type => p_ErrorCode,
3128 p_pgm => l_module_name,
3129 p_msg => p_ErrorDesc,
3130 p_loc => p_ErrorLoc
3131 );
3132 debug_exit (p_ErrorCode, p_ErrorDesc, p_ErrorLoc);
3133 END process_receipts;
3134
3135 --****************************************************************************************--
3136 --* Name : main *--
3137 --* Type : Procedure *--
3138 --* Purpose : This is the main procedure *--
3139 --* Parameters : p_errbuf OUT The Concurrent Program Error Buffer *--
3140 --* : p_retcode OUT The Concurrent Program Return Code *--
3141 --* : p_batch_name IN The Input Receipt Batch name *--
3142 --* Global Vars : g_SUCCESS READ *--
3143 --* Called from : Concurrent Program *--
3144 --* Calls : init *--
3145 --* : process_receipts *--
3146 --* : update_fv_batch_status *--
3147 --* : debug_msg *--
3148 --* : debug_init *--
3149 --* : debug_exit *--
3150 --* : error *--
3151 --* Tables Used : fv_ar_batches SELECT *--
3152 --* Logic : 1. Given the batch name get the record from table fv_ar_batches *--
3153 --* : 2. Call process_receipts to Start Processing the receipts in the *--
3154 --* : batch. *--
3155 --* : 3. Call update_fv_batch_status to update the batch status *--
3156 --****************************************************************************************--
3157 PROCEDURE main
3158 (
3159 p_errbuf OUT NOCOPY VARCHAR2,
3160 p_retcode OUT NOCOPY VARCHAR2,
3161 p_batch_name IN VARCHAR2
3162 ) IS
3163 l_module_name VARCHAR2(30) := 'main';
3164
3165 l_ErrorCode NUMBER;
3166 l_ErrorDesc VARCHAR2(1024);
3167 l_ErrorLoc VARCHAR2(1024);
3168
3169 l_BatchRec fv_ar_batches%ROWTYPE;
3170 BEGIN
3171 l_ErrorCode := g_SUCCESS;
3172 l_ErrorDesc := '';
3173 l_ErrorLoc := '';
3174
3175 ----------------------------------------------------------------------
3176 -- Initialize
3177 ----------------------------------------------------------------------
3178 -- init;
3179 -- debug_init (g_PackageName, l_module_name);
3180
3181 debug_msg (l_module_name, 'p_batch_name = '||p_batch_name);
3182 log_msg (l_module_name,'p_batch_name = '||p_batch_name);
3183
3184 ----------------------------------------------------------------------
3185 -- Get the batch details from fv_ar_batches given the batch name
3186 -- If there is no data found then it is an error.
3187 ----------------------------------------------------------------------
3188 IF (l_ErrorCode = g_SUCCESS) THEN
3189 BEGIN
3190 debug_msg (l_module_name, 'Getting the Batch Details');
3191 SELECT *
3192 INTO l_BatchRec
3193 FROM fv_ar_batches fab
3194 WHERE batch_name = p_batch_name;
3195 EXCEPTION
3196 WHEN NO_DATA_FOUND THEN
3197 l_ErrorCode := g_FAILURE;
3198 l_ErrorDesc := 'No Batch with name <'||p_batch_name||'> Exists.';
3199 l_ErrorLoc := l_module_name || ':' || 'SELECT fv_ar_batches';
3200 error
3201 (
3202 p_error_type => l_ErrorCode,
3203 p_pgm => l_module_name,
3204 p_msg => l_ErrorDesc,
3205 p_loc => l_ErrorLoc
3206 );
3207 debug_msg (l_module_name, 'No Data Found for the batch <'||p_batch_name);
3208 WHEN OTHERS THEN
3209 l_ErrorCode := g_FAILURE;
3210 l_ErrorDesc := SQLERRM;
3211 l_ErrorLoc := l_module_name || ':' || 'SELECT fv_ar_batches';
3212 error
3213 (
3214 p_error_type => l_ErrorCode,
3215 p_pgm => l_module_name,
3216 p_msg => l_ErrorDesc,
3217 p_loc => l_ErrorLoc
3218 );
3219 debug_msg (l_module_name, l_ErrorDesc||'at location'||l_ErrorLoc);
3220 END;
3221 END IF;
3222
3223 IF (l_ErrorCode = g_SUCCESS) THEN
3224 debug_msg (l_module_name, 'Calling process_receipts');
3225 ----------------------------------------------------------------------
3226 -- Call process_receipts to Start Processing the receipts in the --
3227 -- batch. --
3228 ----------------------------------------------------------------------
3229 process_receipts
3230 (
3231 p_BatchRec => l_BatchRec,
3232 p_ErrorCode => l_ErrorCode,
3233 p_ErrorDesc => l_ErrorDesc,
3234 p_ErrorLoc => l_ErrorLoc
3235 );
3236 END IF;
3237
3238 IF (l_ErrorCode = g_SUCCESS) THEN
3239 debug_msg (l_module_name, 'Calling update_fv_batch_status with SUCCESS');
3240
3241 ----------------------------------------------------------------------
3242 -- The Process was successful, hence update with status as --
3243 -- COMPLETED. --
3244 ----------------------------------------------------------------------
3245 update_fv_batch_status
3246 (
3247 p_BatchId => l_BatchRec.batch_id,
3248 p_Status => 'COMPLETED',
3249 p_ErrorCode => l_ErrorCode,
3250 p_ErrorDesc => l_ErrorDesc,
3251 p_ErrorLoc => l_ErrorLoc
3252 );
3253 ELSE
3254 ROLLBACK;
3255 debug_msg (l_module_name, 'Calling update_fv_batch_status with FAILURE');
3256 ----------------------------------------------------------------------
3257 -- The Process was failure, hence update with status as --
3258 -- NEEDS RESUB. --
3259 ----------------------------------------------------------------------
3260 update_fv_batch_status
3261 (
3262 p_BatchId => l_BatchRec.batch_id,
3263 p_Status => 'NEEDS RESUBMISSION',
3264 p_ErrorCode => l_ErrorCode,
3265 p_ErrorDesc => l_ErrorDesc,
3266 p_ErrorLoc => l_ErrorLoc
3267 );
3268
3269 IF (l_ErrorCode = g_SUCCESS) THEN
3270 l_ErrorCode := g_FAILURE;
3271 END IF;
3272 END IF;
3273
3274 COMMIT;
3275
3276 p_retcode := l_ErrorCode;
3277 p_errbuf := l_ErrorDesc;
3278 debug_exit (l_ErrorCode, l_ErrorDesc, l_ErrorLoc);
3279 -- error_write;
3280 -- log_write;
3281 -- debug_write;
3282 EXCEPTION
3283 WHEN OTHERS THEN
3284 l_ErrorCode := g_FAILURE;
3285 l_ErrorDesc := SQLERRM;
3286 l_ErrorLoc := l_module_name || ':' || 'Final Exception';
3287 error
3288 (
3289 p_error_type => l_ErrorCode,
3290 p_pgm => l_module_name,
3291 p_msg => l_ErrorDesc,
3292 p_loc => l_ErrorLoc
3293 );
3294 debug_exit (l_ErrorCode, l_ErrorDesc, l_ErrorLoc);
3295 p_retcode := l_ErrorCode;
3296 p_errbuf := l_ErrorDesc;
3297 -- error_write;
3298 -- log_write;
3299 -- debug_write;
3300 ROLLBACK;
3301 END main;
3302 END fv_apply_cash_receipt;