[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_APPLICATION2
Source
1 PACKAGE BODY ARP_PROCESS_APPLICATION2 AS
2 /* $Header: ARCEAP2B.pls 120.13 2010/11/30 09:13:36 dgaurab ship $ */
3
4 /* =======================================================================
5 | Global Data Types
6 * ======================================================================*/
7 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
8
9 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
10 FUNCTION revision RETURN VARCHAR2 IS
11 BEGIN
12 RETURN '$Revision: 120.13 $';
13 END revision;
14
15 /*===========================================================================+
16 | PROCEDURE |
17 | update_application |
18 | |
19 | DESCRIPTION |
20 | This procedure is used to update an application, e.g. USSGL |
21 | Transaction Code, Cross Currency Rate etc. Columns that can be |
22 | modified without having to reverse the original rows and create |
23 | new ones. We simply update the APP row with the new value. |
24 | |
25 | SCOPE - PUBLIC |
26 | |
27 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
28 | |
29 | ARGUMENTS : IN: |
30 | |
31 | OUT: |
32 | |
33 | RETURNS : NONE |
34 | |
35 | NOTES |
36 | |
37 | MODIFICATION HISTORY |
38 | |
39 | 07/29/1997 Karen Lawrance Release 11. |
40 | Added trans_to_receipt_rate to update call |
41 | for cross currency. |
42 | Also included acctd amount applied to and |
43 | from as OUT NOCOPY parameters. These are used to |
44 | update the form with accurate values. |
45 | 08/21/1997 Tasman Tang Added global_attribute_category, |
46 | global_attribute[1-20] for global |
47 | descriptive flexfield |
48 | 05/24/1999 Debbie Jancis Bug fix 874714 |
49 | update_application should not update anything|
50 | having to do with amount columns because |
51 | amount columns affect posting. Also, |
52 | apply_date or gl_date |
53 | 06/06/2001 S.Nambiar Bug 1815528 - Added claim related parameters |
54 | 07/31/2001 jbeckett Bug 1905659 - For invoice related claim, pass|
55 | trx info to create_claim |
56 | 08/03/2001 jbeckett Bug 1905659 - Added parameter |
57 | p_amount_due_remaining |
58 | 08/10/2001 S.Nambiar Migrated chargeback_customer_trx_id to |
59 | secondary_application_ref_id
60 | 03/15/2002 jbeckett Added new parameters p_application_ref_reason|
61 | and p_customer_reference (bug 2254777). |
62 | 05/09/2002 jbeckett Passes primary_salesrep_id to create_claim |
63 | for invoice related deductions |
64 | 02/20/2002 jbeckett Bug 2751910 - Added p_customer_reason and |
65 | p_applied_rec_app_id to update_application |
66 | 10/25/2005 jbeckett Bug 4565758 - legal_entity_id passed to |
67 | create_claim.
68 +===========================================================================*/
69
70 PROCEDURE update_application(
71 p_ra_id IN NUMBER,
72 p_receipt_ps_id IN NUMBER,
73 p_invoice_ps_id IN NUMBER,
74 p_ussgl_transaction_code IN VARCHAR2,
75 p_application_ref_type IN
76 ar_receivable_applications.application_ref_type%TYPE,
77 p_application_ref_id IN
78 ar_receivable_applications.application_ref_id%TYPE,
79 p_application_ref_num IN
80 ar_receivable_applications.application_ref_num%TYPE,
81 p_secondary_application_ref_id IN
82 ar_receivable_applications.secondary_application_ref_id%TYPE,
83 p_receivable_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE,
84 p_attribute_category IN VARCHAR2,
85 p_attribute1 IN VARCHAR2,
86 p_attribute2 IN VARCHAR2,
87 p_attribute3 IN VARCHAR2,
88 p_attribute4 IN VARCHAR2,
89 p_attribute5 IN VARCHAR2,
90 p_attribute6 IN VARCHAR2,
91 p_attribute7 IN VARCHAR2,
92 p_attribute8 IN VARCHAR2,
93 p_attribute9 IN VARCHAR2,
94 p_attribute10 IN VARCHAR2,
95 p_attribute11 IN VARCHAR2,
96 p_attribute12 IN VARCHAR2,
97 p_attribute13 IN VARCHAR2,
98 p_attribute14 IN VARCHAR2,
99 p_attribute15 IN VARCHAR2,
100 p_global_attribute_category IN VARCHAR2,
101 p_global_attribute1 IN VARCHAR2,
102 p_global_attribute2 IN VARCHAR2,
103 p_global_attribute3 IN VARCHAR2,
104 p_global_attribute4 IN VARCHAR2,
105 p_global_attribute5 IN VARCHAR2,
106 p_global_attribute6 IN VARCHAR2,
107 p_global_attribute7 IN VARCHAR2,
108 p_global_attribute8 IN VARCHAR2,
109 p_global_attribute9 IN VARCHAR2,
110 p_global_attribute10 IN VARCHAR2,
111 p_global_attribute11 IN VARCHAR2,
112 p_global_attribute12 IN VARCHAR2,
113 p_global_attribute13 IN VARCHAR2,
114 p_global_attribute14 IN VARCHAR2,
115 p_global_attribute15 IN VARCHAR2,
116 p_global_attribute16 IN VARCHAR2,
117 p_global_attribute17 IN VARCHAR2,
118 p_global_attribute18 IN VARCHAR2,
119 p_global_attribute19 IN VARCHAR2,
120 p_global_attribute20 IN VARCHAR2,
121 p_comments IN VARCHAR2, -- Added for bug 1839744
122 p_gl_date OUT NOCOPY DATE,
123 p_customer_trx_line_id IN NUMBER,
124 p_module_name IN VARCHAR2,
125 p_module_version IN VARCHAR2,
126 x_application_ref_id OUT NOCOPY
127 ar_receivable_applications.application_ref_id%TYPE,
128 x_application_ref_num OUT NOCOPY
129 ar_receivable_applications.application_ref_num%TYPE,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_msg_count OUT NOCOPY NUMBER,
132 x_msg_data OUT NOCOPY VARCHAR2,
133 p_acctd_amount_applied_to OUT NOCOPY NUMBER,
134 p_acctd_amount_applied_from OUT NOCOPY NUMBER,
135 p_amount_due_remaining IN ar_payment_schedules.amount_due_remaining%TYPE,
136 p_application_ref_reason IN ar_receivable_applications.application_ref_reason%TYPE,
137 p_customer_reference IN ar_receivable_applications.customer_reference%TYPE,
138 p_customer_reason IN ar_receivable_applications.customer_reason%TYPE,
139 p_applied_rec_app_id IN ar_receivable_applications.applied_rec_app_id%TYPE,
140 x_claim_reason_name OUT NOCOPY VARCHAR2,
141 p_on_acct_cust_id IN ar_receivable_applications.on_acct_cust_id%TYPE DEFAULT NULL,
142 p_on_acct_cust_site_use_id IN ar_receivable_applications.on_acct_cust_site_use_id%TYPE DEFAULT NULL,
143 p_on_acct_po_num IN ar_receivable_applications.on_acct_po_num%TYPE DEFAULT NULL
144 ) IS
145
146 l_rec_ra_rec ar_receivable_applications%ROWTYPE;
147 l_currency_code ar_cash_receipts.currency_code%TYPE;
148 l_exchange_rate_type ar_cash_receipts.exchange_rate_type%TYPE;
149 l_exchange_rate_date ar_cash_receipts.exchange_date%TYPE;
150 l_exchange_rate ar_cash_receipts.exchange_rate%TYPE;
151 l_customer_id ar_cash_receipts.pay_from_customer%TYPE;
152 l_bill_to_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
153 l_ship_to_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
154 l_receipt_number ar_cash_receipts.receipt_number%TYPE;
155 l_amount_due_remaining NUMBER;
156 l_claim_amount NUMBER;
157 l_customer_trx_id ra_customer_trx.customer_trx_id%TYPE;
158 l_trx_number ra_customer_trx.trx_number%TYPE;
159 l_cust_trx_type_id ra_cust_trx_types.cust_trx_type_id%TYPE;
160 l_salesrep_id ra_customer_trx.primary_salesrep_id%TYPE;
161 --BUG#2750340
162 l_xla_ev_rec arp_xla_events.xla_events_type;
163 l_legal_entity_id ar_cash_receipts.legal_entity_id%TYPE;
164
165 BEGIN
166 IF PG_DEBUG in ('Y', 'C') THEN
167 arp_standard.debug( 'arp_process_application.update_application()+');
168 END IF;
169
170 IF (p_ra_id IS NULL)
171 THEN
172 APP_EXCEPTION.INVALID_ARGUMENT(
173 'ARP_PROCESS_APPLICATION.UPDATE_APPLICATION'
174 , 'P_RA_ID'
175 , 'NULL');
176
177 ELSIF (p_invoice_ps_id IS NULL)
178 THEN
179 APP_EXCEPTION.INVALID_ARGUMENT(
180 'ARP_PROCESS_APPLICATION.UPDATE_APPLICATION'
181 , 'p_invoice_ps_id'
182 , 'NULL');
183
184 END IF;
185
186 -- First get the old values
187 arp_app_pkg.fetch_p(p_ra_id, l_rec_ra_rec);
188
189 p_acctd_amount_applied_to := l_rec_ra_rec.acctd_amount_applied_to;
190 p_acctd_amount_applied_from := l_rec_ra_rec.acctd_amount_applied_from;
191
192 -- The assign the passed values
193
194 -- KML 12/04/1996
195 -- Added if restriction, as p_receipt_ps_id will be null for
196 -- Credit Memo Applications.
197
198 if p_receipt_ps_id is not null then
199 l_rec_ra_rec.payment_schedule_id := p_receipt_ps_id;
200 end if;
201
202 l_rec_ra_rec.applied_payment_schedule_id := p_invoice_ps_id;
203 l_rec_ra_rec.applied_customer_trx_line_id := p_customer_trx_line_id;
204 l_rec_ra_rec.ussgl_transaction_code := p_ussgl_transaction_code;
205 l_rec_ra_rec.attribute_category := p_attribute_category;
206 l_rec_ra_rec.attribute1 := p_attribute1;
207 l_rec_ra_rec.attribute2 := p_attribute2;
208 l_rec_ra_rec.attribute3 := p_attribute3;
209 l_rec_ra_rec.attribute4 := p_attribute4;
210 l_rec_ra_rec.attribute5 := p_attribute5;
211 l_rec_ra_rec.attribute6 := p_attribute6;
212 l_rec_ra_rec.attribute7 := p_attribute7;
213 l_rec_ra_rec.attribute8 := p_attribute8;
214 l_rec_ra_rec.attribute9 := p_attribute9;
215 l_rec_ra_rec.attribute10 := p_attribute10;
216 l_rec_ra_rec.attribute11 := p_attribute11;
217 l_rec_ra_rec.attribute12 := p_attribute12;
218 l_rec_ra_rec.attribute13 := p_attribute13;
219 l_rec_ra_rec.attribute14 := p_attribute14;
220 l_rec_ra_rec.attribute15 := p_attribute15;
221 l_rec_ra_rec.global_attribute_category := p_global_attribute_category;
222 l_rec_ra_rec.global_attribute1 := p_global_attribute1;
223 l_rec_ra_rec.global_attribute2 := p_global_attribute2;
224 l_rec_ra_rec.global_attribute3 := p_global_attribute3;
225 l_rec_ra_rec.global_attribute4 := p_global_attribute4;
226 l_rec_ra_rec.global_attribute5 := p_global_attribute5;
227 l_rec_ra_rec.global_attribute6 := p_global_attribute6;
228 l_rec_ra_rec.global_attribute7 := p_global_attribute7;
229 l_rec_ra_rec.global_attribute8 := p_global_attribute8;
230 l_rec_ra_rec.global_attribute9 := p_global_attribute9;
231 l_rec_ra_rec.global_attribute10 := p_global_attribute10;
232 l_rec_ra_rec.global_attribute11 := p_global_attribute11;
233 l_rec_ra_rec.global_attribute12 := p_global_attribute12;
234 l_rec_ra_rec.global_attribute13 := p_global_attribute13;
235 l_rec_ra_rec.global_attribute14 := p_global_attribute14;
236 l_rec_ra_rec.global_attribute15 := p_global_attribute15;
237 l_rec_ra_rec.global_attribute16 := p_global_attribute16;
238 l_rec_ra_rec.global_attribute17 := p_global_attribute17;
239 l_rec_ra_rec.global_attribute18 := p_global_attribute18;
240 l_rec_ra_rec.global_attribute19 := p_global_attribute19;
241 l_rec_ra_rec.global_attribute20 := p_global_attribute20;
242 l_rec_ra_rec.comments := p_comments; -- Added for bug 1839744
243 l_rec_ra_rec.application_ref_type := p_application_ref_type;
244 l_rec_ra_rec.application_ref_num := p_application_ref_num;
245 l_rec_ra_rec.application_ref_id := p_application_ref_id;
246 l_rec_ra_rec.secondary_application_ref_id := p_secondary_application_ref_id;
247 l_rec_ra_rec.application_ref_reason := p_application_ref_reason;
248 l_rec_ra_rec.customer_reference := p_customer_reference;
249 l_rec_ra_rec.applied_rec_app_id := p_applied_rec_app_id;
250 l_rec_ra_rec.customer_reason := p_customer_reason;
251
252 --Bug 4696620
253 l_rec_ra_rec.on_acct_cust_id := p_on_acct_cust_id;
254 l_rec_ra_rec.on_acct_cust_site_use_id := p_on_acct_cust_site_use_id;
255 l_rec_ra_rec.on_acct_po_num := p_on_acct_po_num;
256
257
258
259 --Bug 4131243 - set the out parameters for application_ref_num/id so
260 --they are passed back correctly if claim is not created.
261 x_application_ref_num := p_application_ref_num;
262 x_application_ref_id := p_secondary_application_ref_id;
263
264 --Bug 1815528 If claim type is CLAIM, then create claim
265
266 IF PG_DEBUG in ('Y', 'C') THEN
267 arp_standard.debug( 'p_application_ref_type = '||p_application_ref_type);
268 arp_standard.debug( 'p_application_ref_num = '||nvl(p_application_ref_num,'NULL'));
269 END IF;
270 IF (p_application_ref_type = 'CLAIM' AND
271 p_application_ref_num IS NULL)
272 THEN
273 IF p_invoice_ps_id = -4
274 -- its a non trx related claim, get all details from receipt
275 THEN
276 --fetch the receipt details
277 SELECT ps.cash_receipt_id
278 , cr.currency_code
279 , cr.exchange_rate_type
280 , cr.exchange_date
281 , cr.exchange_rate
282 , cr.pay_from_customer
283 , cr.customer_site_use_id
284 , NULL
285 , cr.receipt_number
286 , cr.legal_entity_id
287 INTO l_rec_ra_rec.cash_receipt_id
288 , l_currency_code
289 , l_exchange_rate_type
290 , l_exchange_rate_date
291 , l_exchange_rate
292 , l_customer_id
293 , l_bill_to_site_use_id
294 , l_ship_to_site_use_id
295 , l_receipt_number
296 , l_legal_entity_id
297 FROM ar_payment_schedules ps
298 , ar_cash_receipts cr
299 , ar_cash_receipt_history crh
300 , ar_receipt_methods rm
301 , ce_bank_acct_uses ba
302 , ar_receipt_method_accounts rma
303 WHERE ps.payment_schedule_id = p_receipt_ps_id
304 AND cr.cash_receipt_id = ps.cash_receipt_id
305 AND crh.cash_receipt_id = cr.cash_receipt_id
306 AND crh.current_record_flag = 'Y'
307 AND rm.receipt_method_id = cr.receipt_method_id
308 AND ba.bank_acct_use_id = cr.remit_bank_acct_use_id
309 AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
310 AND rma.receipt_method_id = rm.receipt_method_id;
311
312 l_customer_trx_id := NULL;
313 l_trx_number := NULL;
314 l_cust_trx_type_id := NULL;
315 l_salesrep_id := NULL; -- bug 2361331
316
317 ELSE
318 -- claim is trx related, fetch invoice details
319 SELECT t.invoice_currency_code
320 , t.exchange_rate_type
321 , t.exchange_date
322 , t.exchange_rate
323 , t.customer_trx_id
324 , t.trx_number
325 , t.cust_trx_type_id
326 , t.bill_to_customer_id
327 , t.bill_to_site_use_id
328 , t.ship_to_site_use_id
329 , p.amount_due_remaining
330 , t.primary_salesrep_id
331 , t.legal_entity_id
332 INTO l_currency_code
333 , l_exchange_rate_type
334 , l_exchange_rate_date
335 , l_exchange_rate
336 , l_customer_trx_id
337 , l_trx_number
338 , l_cust_trx_type_id
339 , l_customer_id
340 , l_bill_to_site_use_id
341 , l_ship_to_site_use_id
342 , l_amount_due_remaining
343 , l_salesrep_id -- bug 2361331
344 , l_legal_entity_id
345 FROM ra_customer_trx t
346 , ar_payment_schedules p
347 WHERE t.customer_trx_id = p.customer_trx_id
348 AND p.payment_schedule_id = p_invoice_ps_id;
349
350 SELECT cr.cash_receipt_id, cr.receipt_number
351 INTO l_rec_ra_rec.cash_receipt_id
352 , l_receipt_number
353 FROM ar_cash_receipts cr,
354 ar_payment_schedules ps
355 WHERE ps.payment_schedule_id = p_receipt_ps_id
356 AND cr.cash_receipt_id = ps.cash_receipt_id;
357 END IF;
358
359 IF p_invoice_ps_id = -4
360 THEN
361 l_claim_amount := l_rec_ra_rec.amount_applied;
362 ELSIF
363 p_amount_due_remaining IS NULL
364 THEN
365 l_claim_amount := l_amount_due_remaining;
366 ELSE
367 l_claim_amount := p_amount_due_remaining;
368 END IF;
369
370 arp_process_application.create_claim(
371 p_amount => l_claim_amount
372 , p_amount_applied => l_rec_ra_rec.amount_applied
373 , p_currency_code => l_currency_code
374 , p_exchange_rate_type => l_exchange_rate_type
375 , p_exchange_rate_date => l_exchange_rate_date
376 , p_exchange_rate => l_exchange_rate
377 , p_customer_trx_id => l_customer_trx_id
378 , p_invoice_ps_id => p_invoice_ps_id
379 , p_cust_trx_type_id => l_cust_trx_type_id
380 , p_trx_number => l_trx_number
381 , p_cust_account_id => l_customer_id
382 , p_bill_to_site_id => l_bill_to_site_use_id
383 , p_ship_to_site_id => l_ship_to_site_use_id
384 , p_salesrep_id => l_salesrep_id -- bug 2361331
385 , p_customer_ref_date => NULL
386 , p_customer_ref_number => p_customer_reference
387 , p_cash_receipt_id => l_rec_ra_rec.cash_receipt_id
388 , p_receipt_number => l_receipt_number
389 , p_customer_reason => p_customer_reason
390 , p_reason_id => TO_NUMBER(p_application_ref_reason)
391 , p_comments => l_rec_ra_rec.comments
392 , p_apply_date => l_rec_ra_rec.apply_date --Bug5495310
393 , p_attribute_category => p_attribute_category
394 , p_attribute1 => p_attribute1
395 , p_attribute2 => p_attribute2
396 , p_attribute3 => p_attribute3
397 , p_attribute4 => p_attribute4
398 , p_attribute5 => p_attribute5
399 , p_attribute6 => p_attribute6
400 , p_attribute7 => p_attribute7
401 , p_attribute8 => p_attribute8
402 , p_attribute9 => p_attribute9
403 , p_attribute10 => p_attribute10
404 , p_attribute11 => p_attribute11
405 , p_attribute12 => p_attribute12
406 , p_attribute13 => p_attribute13
407 , p_attribute14 => p_attribute14
408 , p_attribute15 => p_attribute15
409 , x_return_status => x_return_status
410 , x_msg_count => x_msg_count
411 , x_msg_data => x_msg_data
412 , x_claim_id => l_rec_ra_rec.secondary_application_ref_id
413 , x_claim_number => l_rec_ra_rec.application_ref_num
414 , x_claim_reason_name => x_claim_reason_name
415 , p_legal_entity_id => l_legal_entity_id);
416
417 x_application_ref_id := l_rec_ra_rec.secondary_application_ref_id;
418 x_application_ref_num := l_rec_ra_rec.application_ref_num;
419
420 END IF;
421
422 -- Dump the data into database
423 arp_app_pkg.update_p(l_rec_ra_rec);
424 p_gl_date := l_rec_ra_rec.gl_date;
425
426 --BUG#2750340
427 l_xla_ev_rec.xla_from_doc_id := p_ra_id;
428 l_xla_ev_rec.xla_to_doc_id := p_ra_id;
429 l_xla_ev_rec.xla_doc_table := 'APP';
430 l_xla_ev_rec.xla_mode := 'O';
431 l_xla_ev_rec.xla_call := 'B';
432 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
433
434 IF PG_DEBUG in ('Y', 'C') THEN
435 arp_standard.debug( 'arp_process_application.update_application()-');
436 END IF;
437
438 EXCEPTION
439 when others then
440 raise;
441
442 END update_application;
443
444 /*===========================================================================+
445 | PROCEDURE
446 | delete_selected_transaction
447 |
448 | DESCRIPTION
449 | This procedure is used to delete an application that has been
450 | created through the automatic receipt creation process.
451 |
452 | SCOPE - PUBLIC
453 |
454 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
455 |
456 | ARGUMENTS : IN:
457 | p_ra_id Id of application to be deleted.
458 | p_app_ps_id Payment Schedule Id of the applied
459 | Transaction.
460 |
461 | OUT:
462 |
463 | RETURNS : NONE
464 |
465 | NOTES
466 |
467 | MODIFICATION HISTORY
468 | 12/06/1996 Karen Lawrance Created
469 | 10/22/1997 Karen Murphy Bug #567872. Added code to update the
470 | UNAPP row in receivable applications
471 | when an APP row is deleted.
472 12/04/1997 Karen Murphy Bug fix #567872. Added the setting of the
473 acctd_amount_applied_from for the UNAPP row.
474 | 24/03/1998 Vikram Ahluwalia Plugin calls one delete for the APP record
475 | and a combination of delete followed by
476 | create for the UNAPP record accounting.
477 | Though this appears to be specifically
478 | written for Unconfirmed Autoreceipts (APP
479 | and UNAPP combination it patched for
480 | completeness - notice the confirmed flag
481 | check in delete cursor and create call
482 +===========================================================================*/
483 PROCEDURE delete_selected_transaction (
484 p_ra_id IN NUMBER
485 , p_app_ps_id IN NUMBER
486 ) IS
487
488 CURSOR get_app_C(l_app_id NUMBER) IS
489 select app.receivable_application_id app_id,
490 app.cash_receipt_id cr_id
491 from ar_receivable_applications app
492 where app.receivable_application_id = l_app_id
493 and nvl(app.confirmed_flag,'Y') = 'Y' --confirmed records have accounting only
494 and exists (select 'x'
495 from ar_distributions ard
496 where ard.source_table = 'RA'
497 and ard.source_id = app.receivable_application_id);
498
499 lr_ps_rec ar_payment_schedules%ROWTYPE;
500 lr_ra_rec ar_receivable_applications%ROWTYPE;
501
502 ln_amount_applied NUMBER;
503 ln_acctd_amount_applied_from NUMBER;
504 ln_cash_receipt_id NUMBER;
505 ln_unapp_ra_id NUMBER;
506 l_ae_doc_rec ae_doc_rec_type;
507
508 BEGIN
509 IF PG_DEBUG in ('Y', 'C') THEN
510 arp_standard.debug( 'arp_process_application.delete_selected_transaction()+');
511 END IF;
512
513 -- Check that the Application Id, and the Applied Payment Schedule Id
514 -- have been provided.
515 IF (p_ra_id IS NULL)
516 THEN
517 APP_EXCEPTION.INVALID_ARGUMENT(
518 'ARP_PROCESS_APPLICATION.DELETE_AUTOMATIC_APPLICATION'
519 , 'P_RA_ID'
520 , 'NULL');
521 ELSIF (p_app_ps_id IS NULL)
522 THEN
523 APP_EXCEPTION.INVALID_ARGUMENT(
524 'ARP_PROCESS_APPLICATION.DELETE_AUTOMATIC_APPLICATION'
525 , 'P_APP_PS_ID'
526 , 'NULL');
527 END IF;
528
529 -- Before we delete it, get the cash receipt id and amount applied
530 -- for the application.
531 select ra.cash_receipt_id,
532 ra.amount_applied,
533 ra.acctd_amount_applied_from
534 into ln_cash_receipt_id,
535 ln_amount_applied,
536 ln_acctd_amount_applied_from
537 from ar_receivable_applications ra
538 where ra.receivable_application_id = p_ra_id;
539
540 --
541 --Release 11.5 delete child accounting records associated with
542 --parent applications for APP
543 --
544 FOR l_get_app_rec IN get_app_C(p_ra_id) LOOP
545
546 l_ae_doc_rec.document_type := 'RECEIPT';
547 l_ae_doc_rec.document_id := l_get_app_rec.cr_id;
548 l_ae_doc_rec.accounting_entity_level := 'ONE';
549 l_ae_doc_rec.source_table := 'RA';
550 l_ae_doc_rec.source_id := l_get_app_rec.app_id; --same as p_ra_id
551 l_ae_doc_rec.source_id_old := '';
552 l_ae_doc_rec.other_flag := '';
553 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
554
555 END LOOP;
556
557 -- Delete Receivable Application record.
558 arp_app_pkg.delete_p(p_ra_id);
559
560 /*---------------------------------+
561 | Calling central MRC library |
562 | for MRC Integration |
563 +---------------------------------*/
564
565 ar_mrc_engine.maintain_mrc_data(
566 p_event_mode => 'DELETE',
567 p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
568 p_mode => 'SINGLE',
569 p_key_value => p_ra_id);
570
571 --Bug#2750340
572 ARP_XLA_EVENTS.delete_event
573 ( p_document_id => p_ra_id,
574 p_doc_table => 'APP');
575
576 -- Populate the Payment Schedule record from ar_payment_schedules,
577 -- based on the provided Applied_Payment_Schedule_Id.
578 arp_ps_pkg.fetch_p( p_app_ps_id, lr_ps_rec );
579
580 -- Update the Transaction's Payment Schedule, set flag "Selected for
581 -- Receipt Batch Id" to null, allowing it to be selected again for
582 -- automatic payment.
583 lr_ps_rec.selected_for_receipt_batch_id := null;
584
585 arp_ps_pkg.update_p(lr_ps_rec, p_app_ps_id);
586
587 ----------------------------------------------------------------
588 -- Now that we have deleted the application and updated the
589 -- payment schedule, update the UNAPP row for the cash receipt.
590 -- Amount applied needs to be reduced by the amount that was
591 -- deleted.
592 ----------------------------------------------------------------
593
594 IF PG_DEBUG in ('Y', 'C') THEN
595 arp_standard.debug('delete_selected_transaction: ' || 'Getting the Receivable Application Id for the UNAPP row');
596 END IF;
597 -- Get the receivable application id for the UNAPP row.
598 select ra.receivable_application_id
599 into ln_unapp_ra_id
600 from ar_receivable_applications ra
601 where ra.cash_receipt_id = ln_cash_receipt_id
602 and ra.status = 'UNAPP';
603
604 IF PG_DEBUG in ('Y', 'C') THEN
605 arp_standard.debug('delete_selected_transaction: ' || 'Fetch the UNAPP row');
606 END IF;
607 -- Fetch the UNAPP row.
608 arp_app_pkg.fetch_p( ln_unapp_ra_id, lr_ra_rec );
609
610 -- Set the amount with the new value.
611 lr_ra_rec.amount_applied := lr_ra_rec.amount_applied - ln_amount_applied;
612
613 IF PG_DEBUG in ('Y', 'C') THEN
614 arp_standard.debug('delete_selected_transaction: ' || 'New UNAPP amount: ' || to_char(lr_ra_rec.amount_applied));
615 END IF;
616
617 -- Set the acctd amount with the new value.
618 lr_ra_rec.acctd_amount_applied_from := lr_ra_rec.acctd_amount_applied_from - ln_acctd_amount_applied_from;
619 IF PG_DEBUG in ('Y', 'C') THEN
620 arp_standard.debug('delete_selected_transaction: ' || 'Update the UNAPP row');
621 END IF;
622 --
623 --Release 11.5 delete child accounting records associated with
624 --parent applications UNAPP record as update is a combination
625 --of delete for by create
626 --
627 FOR l_get_app_rec IN get_app_C(lr_ra_rec.receivable_application_id) LOOP
628
629 l_ae_doc_rec.document_type := 'RECEIPT';
630 l_ae_doc_rec.document_id := l_get_app_rec.cr_id;
631 l_ae_doc_rec.accounting_entity_level := 'ONE';
632 l_ae_doc_rec.source_table := 'RA';
633 l_ae_doc_rec.source_id := l_get_app_rec.app_id;
634 l_ae_doc_rec.source_id_old := '';
635 l_ae_doc_rec.other_flag := '';
636 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
637
638 END LOOP;
639
640 -- Update the UNAPP row.
641 arp_app_pkg.update_p(lr_ra_rec);
642
643 --
644 --Release 11.5 create accounting associated with UNAPP row
645 --This is standalone and not paired with an APP
646 --
647 IF NVL(lr_ra_rec.confirmed_flag,'Y') = 'Y' THEN
648 l_ae_doc_rec.document_type := 'RECEIPT';
649 l_ae_doc_rec.document_id := lr_ra_rec.cash_receipt_id;
650 l_ae_doc_rec.accounting_entity_level := 'ONE';
651 l_ae_doc_rec.source_table := 'RA';
652 l_ae_doc_rec.source_id := lr_ra_rec.receivable_application_id;
653 l_ae_doc_rec.source_id_old := '';
654 l_ae_doc_rec.other_flag := '';
655 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
656 END IF;
657
658 IF PG_DEBUG in ('Y', 'C') THEN
659 arp_standard.debug('delete_selected_transaction: ' || 'arp_process_application.delete_receivable_application()-');
660 END IF;
661
662 EXCEPTION
663 WHEN OTHERS THEN
664 IF PG_DEBUG in ('Y', 'C') THEN
665 arp_standard.debug('delete_selected_transaction: ' || '-- EXCEPTION:');
666 arp_standard.debug('delete_selected_transaction: ' || 'Printing procedure parameter values:');
667 arp_standard.debug('delete_selected_transaction: ' || '-- p_ra_id = '||TO_CHAR(p_ra_id));
668 arp_standard.debug('delete_selected_transaction: ' || '-- p_app_ps_id = '||TO_CHAR(p_app_ps_id));
669 END IF;
670 app_exception.raise_exception;
671 END delete_selected_transaction;
672
673
674 /*===========================================================================+
675 | PROCEDURE
676 | update_onacct_cust_app
677 |
678 | DESCRIPTION
679 | This procedure is used to update the on-account customer details
680 | on ar_receivable_applications_all
681 |
682 | SCOPE - PUBLIC
683 |
684 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
685 |
686 | ARGUMENTS : IN:
687 | p_ra_id Id of application
688 |
689 |
690 | OUT:
691 |
692 | RETURNS : NONE
693 |
694 | NOTES
695 |
696 | MODIFICATION HISTORY
697 | 28/03/2006 Surendra Rajan Created - Enh: 4696620
698 +===========================================================================*/
699 PROCEDURE update_onacct_cust_app(
700 p_ra_id IN NUMBER,
701 p_on_acct_cust_id IN ar_receivable_applications.on_acct_cust_id%TYPE DEFAULT NULL,
702 p_on_acct_cust_site_use_id IN ar_receivable_applications.on_acct_cust_site_use_id%TYPE DEFAULT NULL,
703 p_on_acct_po_num IN ar_receivable_applications.on_acct_po_num%TYPE DEFAULT NULL
704 ) IS
705 l_ra_rec ar_receivable_applications%ROWTYPE;
706
707 Begin
708 IF PG_DEBUG in ('Y', 'C') THEN
709 arp_standard.debug( 'arp_process_application.update_onacct_cust_app()+');
710 END IF;
711 IF (p_ra_id IS NULL)
712 THEN
713 APP_EXCEPTION.INVALID_ARGUMENT(
714 'ARP_PROCESS_APPLICATION.UPDATE_ONACCT_CUST_APP'
715 , 'P_RA_ID'
716 , 'NULL');
717 END IF;
718 -- First get the old values
719 arp_app_pkg.fetch_p(p_ra_id, l_ra_rec);
720
721 l_ra_rec.on_acct_cust_id := p_on_acct_cust_id;
722 l_ra_rec.on_acct_cust_site_use_id := p_on_acct_cust_site_use_id;
723 l_ra_rec.on_acct_po_num := p_on_acct_po_num;
724
725 IF PG_DEBUG in ('Y', 'C') THEN
726 arp_standard.debug( 'p_ra_id ='||p_ra_id);
727 arp_standard.debug( 'p_on_acct_cust_id = '||p_on_acct_cust_id );
728 arp_standard.debug( 'p_on_acct_cust_site_use_id = '||p_on_acct_cust_site_use_id );
729 arp_standard.debug( 'p_on_acct_po_num = '||p_on_acct_po_num);
730 END IF;
731
732 -- Dump the data into database
733 arp_app_pkg.update_p(l_ra_rec);
734
735 IF PG_DEBUG in ('Y', 'C') THEN
736 arp_standard.debug( 'arp_process_application.update_onacct_cust_app()-');
737 END IF;
738 EXCEPTION
739 when others then
740 raise;
741 END update_onacct_cust_app;
742
743 END arp_process_application2;