[Home] [Help]
PACKAGE BODY: APPS.ARP_RECEIPTS_MAIN
Source
1 PACKAGE BODY ARP_RECEIPTS_MAIN AS
2 /* $Header: ARRECACB.pls 120.60.12010000.2 2008/11/04 09:05:33 dgaurab ship $ */
3
4 /* =======================================================================
5 | Package Globals
6 * ======================================================================*/
7 g_mode VARCHAR2(30);
8 g_ae_doc_rec ae_doc_rec_type;
9 g_ae_event_rec ae_event_rec_type;
10 g_ae_line_tbl ae_line_tbl_type;
11 g_empty_ae_line_tbl ae_line_tbl_type;
12 g_empty_ae_app_pair_tbl ae_app_pair_tbl_type;
13 g_ae_sys_rec ae_sys_rec_type;
14 g_ae_line_ctr BINARY_INTEGER;
15
16 TYPE g_ae_miscel_rec_type IS RECORD (
17 gain_loss_ccid ar_system_parameters.code_combination_id_gain%TYPE,
18 fixed_rate varchar2(1)
19 );
20
21 --{3377004
22 TYPE ctl_rem_amt_type IS RECORD
23 (customer_trx_line_id DBMS_SQL.NUMBER_TABLE,
24 amount_due_remaining DBMS_SQL.NUMBER_TABLE,
25 acctd_amount_due_remaining DBMS_SQL.NUMBER_TABLE,
26 chrg_amount_remaining DBMS_SQL.NUMBER_TABLE,
27 chrg_acctd_amount_remaining DBMS_SQL.NUMBER_TABLE);
28 --}
29
30
31 /* =======================================================================
32 | Private Procedure/Function prototypes
33 * ======================================================================*/
34 PROCEDURE Init_Ae_Lines;
35
36 PROCEDURE Derive_Accounting_Entry
37 --{HYUDETUPT
38 (p_from_llca_call IN VARCHAR2,
39 p_gt_id IN NUMBER);
40 --}
41
42
43 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
44
45 PROCEDURE Delete_RA(p_ae_deleted OUT NOCOPY BOOLEAN);
46
47 PROCEDURE Delete_MCD(p_ae_deleted OUT NOCOPY BOOLEAN);
48
49 PROCEDURE Reverse_Receipt_CM;
50
51 PROCEDURE Get_Doc_Entitity_Data (
52 p_level IN VARCHAR2 ,
53 p_app_rec OUT NOCOPY ar_receivable_applications%ROWTYPE ,
54 p_cr_rec OUT NOCOPY ar_cash_receipts%ROWTYPE ,
55 p_cust_inv_rec OUT NOCOPY ra_customer_trx%ROWTYPE ,
56 p_cust_cm_rec OUT NOCOPY ra_customer_trx%ROWTYPE ,
57 p_ctlgd_cm_rec OUT NOCOPY ra_cust_trx_line_gl_dist%ROWTYPE ,
58 p_miscel_rec OUT NOCOPY g_ae_miscel_rec_type ,
59 p_vat_rec OUT NOCOPY ar_vat_tax%ROWTYPE ,
60 p_curr_rec OUT NOCOPY ae_curr_rec_type ,
61 p_rule_rec OUT NOCOPY ae_rule_rec_type );
62
63 PROCEDURE Act_Tax_Non_Rec_Ccid (
64 p_type IN VARCHAR2 ,
65 p_asset_tax_code IN VARCHAR2 ,
66 p_apply_date IN DATE ,
67 p_act_tax_non_rec_ccid OUT NOCOPY ar_receivables_trx.code_combination_id%TYPE ,
68 p_act_vat_tax_id OUT NOCOPY ar_vat_tax.vat_tax_id%TYPE );
69
70 PROCEDURE Create_Ae_Lines_Common (
71 p_level IN VARCHAR2,
72 --{HYUDETUPT
73 p_from_llca_call IN VARCHAR2,
74 p_gt_id IN NUMBER
75 --}
76 );
77
78 PROCEDURE Create_Ae_Lines_RA(
79 p_app_rec IN ar_receivable_applications%ROWTYPE ,
80 p_cr_rec IN ar_cash_receipts%ROWTYPE ,
81 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
82 p_cust_cm_rec IN ra_customer_trx%ROWTYPE ,
83 p_ctlgd_cm_rec IN ra_cust_trx_line_gl_dist%ROWTYPE ,
84 p_miscel_rec IN g_ae_miscel_rec_type ,
85 p_rule_rec IN ae_rule_rec_type ,
86 --{
87 p_from_llca_call IN VARCHAR2,
88 p_gt_id IN NUMBER
89 --}
90 );
91
92 PROCEDURE Create_Ae_Lines_MCD(
93 p_cr_rec IN ar_cash_receipts%ROWTYPE ,
94 p_vat_rec IN ar_vat_tax%ROWTYPE ,
95 p_curr_rec IN ae_curr_rec_type );
96
97 PROCEDURE Assign_Ael_Elements(
98 p_ae_line_rec IN ae_line_rec_type );
99
100
101 --{3377004
102 FUNCTION ctl_id_index(p_ctl_id_tab IN DBMS_SQL.NUMBER_TABLE,
103 p_ctl_id IN NUMBER)
104 RETURN NUMBER;
105
106 PROCEDURE init_rem_amt(x_rem_amt IN OUT NOCOPY ctl_rem_amt_type,
107 p_index IN NUMBER);
108
109 --{BUG#5437275 Need from amt for activity distributions
110 FUNCTION from_num_amt
111 (p_to_curr IN VARCHAR2,
112 p_from_curr IN VARCHAR2,
113 p_to_curr_rate IN NUMBER, -- vavenugo bug6653443
114 p_from_curr_rate IN NUMBER, -- vavenugo bug6653443
115 p_to_den_amt IN NUMBER,
116 p_from_num_amt IN NUMBER,
117 p_to_num_amt IN NUMBER)
118 RETURN NUMBER
119 IS
120 l_res NUMBER;
121 BEGIN
122 /* vavenugo bug6653443 */
123 -- IF p_to_curr = p_from_curr THEN
124 IF (p_to_curr = p_from_curr AND p_to_curr_rate = p_from_curr_rate) THEN
125 l_res := p_to_num_amt;
126 ELSE
127 IF p_to_den_amt IS NULL OR p_to_den_amt = 0 THEN
128 l_res := p_to_num_amt;
129 ELSE
130 l_res := arpcurr.CurrRound(
131 p_from_num_amt /
132 p_to_den_amt *
133 p_to_num_amt);
134 END IF;
135 END IF;
136 RETURN l_res;
137 END;
138
139
140
141 /* =======================================================================
142 | Procedures/functions
143 * ======================================================================*/
144 /* =======================================================================
145 | PUBLIC PROCEDURE Delete_Acct
146 |
147 | DESCRIPTION
148 | Accounting Entry Deletion
149 | -------------------------
150 | This procedure is the Accounting Entry deletion routine which
151 | deletes data associated with Receipts based on event and source
152 | table.
153 |
154 | PARAMETERS
155 | p_mode IN Document or Accounting Event mode
156 | p_ae_doc_rec IN Document Record
157 | p_ae_event_rec IN Event Record
158 | p_ae_deleted OUT NOCOPY AE Lines deletion status
159 * ======================================================================*/
160 PROCEDURE Delete_Acct( p_mode IN VARCHAR2,
161 p_ae_doc_rec IN OUT NOCOPY ae_doc_rec_type,
162 p_ae_event_rec IN ae_event_rec_type,
163 p_ae_deleted OUT NOCOPY BOOLEAN ) IS
164 BEGIN
165 IF PG_DEBUG in ('Y', 'C') THEN
166 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Delete_Acct()+');
167 END IF;
168
169 /*----------------------------------------------------+
170 | Copy Document/Event Data to Global |
171 +----------------------------------------------------*/
172 g_mode := p_mode;
173 g_ae_doc_rec := p_ae_doc_rec;
174 g_ae_event_rec := p_ae_event_rec;
175
176 IF ( g_ae_doc_rec.source_table = 'RA' ) THEN
177
178 Delete_RA(p_ae_deleted => p_ae_deleted) ;
179
180 /*---------------------------------------------------------------+
181 | If paired id of deleted UNAPP record is returned if delete is |
182 | followed by a create for update of a UNAPP record |
183 +---------------------------------------------------------------*/
184 p_ae_doc_rec.source_id_old := g_ae_doc_rec.source_id_old ;
185
186 ELSIF ( g_ae_doc_rec.source_table = 'MCD' ) THEN
187
188 Delete_MCD(p_ae_deleted => p_ae_deleted) ;
189
190 END IF;
191
192 IF PG_DEBUG in ('Y', 'C') THEN
193 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Delete_Acct()-');
194 END IF;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 IF PG_DEBUG in ('Y', 'C') THEN
199 arp_standard.debug( 'EXCEPTION: ARP_RECEIPTS_MAIN.Delete_Acct');
200 END IF;
201 RAISE;
202
203 END Delete_Acct;
204
205 /* =======================================================================
206 | PUBLIC PROCEDURE Execute
207 |
208 | DESCRIPTION
209 | Accounting Entry Derivation Method
210 | ----------------------------------
211 | This procedure is the Accounting Entry derivation method for all
212 | accounting events associated with the receivable applications layer.
213 |
214 | Functions of the AE Derivation Method are:
215 | - Single Entry Point for easy extensibility
216 | - Read Event Data
217 | - Read Transaction and Setup Data
218 | - Determine AE Lines affected
219 | - Derive AE Lines
220 | - Return AE Lines created in a PL/SQL table.
221 |
222 | PARAMETERS
223 | p_mode IN Document or Accounting Event mode
224 | p_ae_doc_rec IN Document Record
225 | p_ae_event_rec IN Event Record
226 | p_ae_line_tbl OUT NOCOPY AE Lines table
227 | p_ae_created OUT NOCOPY AE Lines creation status
228 * ======================================================================*/
229 PROCEDURE Execute( p_mode IN VARCHAR2,
230 p_ae_doc_rec IN ae_doc_rec_type,
231 p_ae_event_rec IN ae_event_rec_type,
232 p_ae_line_tbl OUT NOCOPY ae_line_tbl_type,
233 p_ae_created OUT NOCOPY BOOLEAN,
234 --{HYUDETUPT
235 p_from_llca_call IN VARCHAR2 DEFAULT 'N',
236 p_gt_id IN NUMBER DEFAULT NULL
237 --}
238 ) IS
239
240
241 BEGIN
242 IF PG_DEBUG in ('Y', 'C') THEN
243 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Execute()+');
244 END IF;
245 /*------------------------------------------------------+
246 | Initialize Accounting Entry Header and Lines |
247 +------------------------------------------------------*/
248 g_ae_line_ctr := 0;
249 Init_Ae_Lines;
250
251 /*---------------------------------------------------------------+
252 | Copy Document/Event Data to Global, derive System Options info|
253 +---------------------------------------------------------------*/
254 g_mode := p_mode;
255 g_ae_doc_rec := p_ae_doc_rec;
256 g_ae_event_rec := p_ae_event_rec;
257
258 --Get system options info
259
260 g_ae_sys_rec.set_of_books_id := ARP_ACCT_MAIN.ae_sys_rec.set_of_books_id;
261 g_ae_sys_rec.gain_cc_id := ARP_ACCT_MAIN.ae_sys_rec.gain_cc_id;
262 g_ae_sys_rec.loss_cc_id := ARP_ACCT_MAIN.ae_sys_rec.loss_cc_id;
263 g_ae_sys_rec.round_cc_id := ARP_ACCT_MAIN.ae_sys_rec.round_cc_id;
264 g_ae_sys_rec.coa_id := ARP_ACCT_MAIN.ae_sys_rec.coa_id;
265 g_ae_sys_rec.base_currency := ARP_ACCT_MAIN.ae_sys_rec.base_currency;
266 g_ae_sys_rec.base_precision := ARP_ACCT_MAIN.ae_sys_rec.base_precision;
267 g_ae_sys_rec.base_min_acc_unit := ARP_ACCT_MAIN.ae_sys_rec.base_min_acc_unit;
268
269 -- MRC TRIGGER REPLACEMENT
270 -- Initialize a new global variable:
271 g_ae_sys_rec.sob_type := ARP_ACCT_MAIN.ae_sys_rec.sob_type;
272
273 /*------------------------------------------------------+
274 | Derive Accounting Entry |
275 +------------------------------------------------------*/
276 Derive_Accounting_Entry
277 --{HYUDETUPT
278 (p_from_llca_call => p_from_llca_call,
279 p_gt_id => p_gt_id);
280 --}
281
282 /*------------------------------------------------------+
283 | Return Accounting Entry Creation Status |
284 +------------------------------------------------------*/
285 p_ae_line_tbl := g_ae_line_tbl;
286
287 IF g_ae_line_tbl.EXISTS(g_ae_line_ctr) THEN
288 p_ae_created := TRUE;
289
290 ELSE
291 p_ae_created := FALSE;
292
293 END IF;
294
295 IF PG_DEBUG in ('Y', 'C') THEN
296 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Execute()-');
297 END IF;
298
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 IF PG_DEBUG in ('Y', 'C') THEN
302 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Execute - NO_DATA_FOUND' );
303 END IF;
304 RAISE;
305
306 WHEN OTHERS THEN
307 IF PG_DEBUG in ('Y', 'C') THEN
308 arp_standard.debug( 'EXCEPTION: ARP_RECEIPTS_MAIN.Execute');
309 END IF;
310 RAISE;
311
312 END Execute;
313
314
315 /* =======================================================================
316 |
317 | PROCEDURE Init_Ae_Lines
318 |
319 | DESCRIPTION
320 | Initialises the Global lines table
321 |
322 | PARAMETERS
323 | NONE
324 * ======================================================================*/
325 PROCEDURE Init_Ae_Lines IS
326
327 BEGIN
328
329 IF PG_DEBUG in ('Y', 'C') THEN
330 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Init_Ae_Lines()+');
331 END IF;
332
333 g_ae_line_tbl := g_empty_ae_line_tbl;
334
335 IF PG_DEBUG in ('Y', 'C') THEN
336 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Init_Ae_Lines()-');
337 END IF;
338 EXCEPTION
339
340 WHEN OTHERS THEN
341 IF PG_DEBUG in ('Y', 'C') THEN
342 arp_standard.debug( 'EXCEPTION: ARP_RECEIPTS_MAIN.Init_Ae_Lines');
343 END IF;
344 RAISE;
345
346 END Init_Ae_Lines;
347
348
349 /* =======================================================================
350 |
351 | PROCEDURE Derive_Accounting_Entry
352 |
353 | DESCRIPTION
354 | This procedure gets the necessary transaction data and determines
355 | the accounting entries to be created at each of entity level.
356 |
357 | PARAMETERS
358 | Event_Rec Global Event Record
359 * ======================================================================*/
360 PROCEDURE Derive_Accounting_Entry
361 --{HYUDETUPT
362 (p_from_llca_call IN VARCHAR2,
363 p_gt_id IN NUMBER)
364 --}
365 IS
366
367 BEGIN
368 IF PG_DEBUG in ('Y', 'C') THEN
369 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Derive_Accounting_Entry()+');
370 arp_standard.debug( ' p_from_llca_call : '||p_from_llca_call);
371 arp_standard.debug( ' p_gt_id : '||p_gt_id);
372 END IF;
373 /*------------------------------------------------------------+
374 | Create Accounting Entries at the Document Entity level. |
375 +------------------------------------------------------------*/
376 IF ( g_ae_doc_rec.accounting_entity_level = 'ONE' ) THEN
377
378 IF ((g_ae_doc_rec.source_id_old IS NOT NULL) and (g_ae_doc_rec.other_flag = 'REVERSE')) THEN
379
380 /*------------------------------------------------------------+
381 | Reverse Accounting Entry Lines Misc Cash Receipts, Cash |
382 | Receipts and Credit Memos |
383 +------------------------------------------------------------*/
384 Reverse_Receipt_CM;
385
386 ELSIF ( g_ae_doc_rec.source_table = 'RA' ) THEN
387
388 /*---------------------------------------------------------+
389 | Create Accounting Entry Lines, Receipt and CM's |
390 +---------------------------------------------------------*/
391 Create_Ae_Lines_Common(p_level => 'RA',
392 --{HYUDETUPT
393 p_from_llca_call => p_from_llca_call,
394 p_gt_id => p_gt_id
395 --}
396 );
397
398 ELSIF ( g_ae_doc_rec.source_table = 'MCD' ) THEN
399
400 /*---------------------------------------------------------------+
401 | Create Accounting Entry Lines, Misc Cash Receipts and Payments|
402 +---------------------------------------------------------------*/
403 Create_Ae_Lines_Common(p_level => 'MCD',
404 --{HYUDETUPT
405 p_from_llca_call => p_from_llca_call,
406 p_gt_id => p_gt_id
407 --}
408 );
409
410 END IF;
411
412 END IF; -- accounting_entity_level = ONE
413
414 /*------------------------------------------------------+
415 | Create Accounting Entries at the Document Level |
416 | (All Entities) |
417 +------------------------------------------------------*/
418 IF ( g_ae_doc_rec.accounting_entity_level = 'ALL' ) THEN
419
420 NULL;
421
422 END IF; -- accounting_entity_level = ALL?
423
424 IF PG_DEBUG in ('Y', 'C') THEN
425 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Derive_Accounting_Entry()-');
426 END IF;
427
428 EXCEPTION
429 WHEN OTHERS THEN
430 IF PG_DEBUG in ('Y', 'C') THEN
431 arp_standard.debug( 'EXCEPTION: ARP_RECEIPTS_MAIN.Derive_Accounting_Entry');
432 END IF;
433 RAISE;
434 END Derive_Accounting_Entry;
435
436 /* =======================================================================
437 |
438 | PROCEDURE Delete_RA
439 |
440 | DESCRIPTION
441 | Deletes accounting associated with a Receivable application id
442 | from the AR_DISTRIBUTIONS table.This routine deletes all records
443 | matching the input source_id. Note records from child table
444 | (AR_DISTRIBUTIONS) be deleted first.
445 |
446 | PARAMETERS
447 | p_ae_deleted indicates whether records were deleted
448 | for source_id
449 * ======================================================================*/
450 PROCEDURE Delete_RA(p_ae_deleted OUT NOCOPY BOOLEAN) IS
451
452 l_status ar_receivable_applications.status%TYPE;
453
454 l_ar_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
455
456 BEGIN
457 IF PG_DEBUG in ('Y', 'C') THEN
458 arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_RA()+');
459 END IF;
460
461 /*-------------------------------------------------------------------+
462 | Verify that the source id is a valid candidate for deletion |
463 +-------------------------------------------------------------------*/
464 SELECT ra.status
465 INTO l_status
466 FROM ar_receivable_applications ra
467 WHERE ra.receivable_application_id = g_ae_doc_rec.source_id
468 /* bug 1454382 : when a receipt application is unapplied, the reversal_gl_date is populated
469 even when it's only a reversal of the application and not the whole receipt, hence, this
470 condition was causing an EXCEPTION when the receipt was being deleted.
471 Fix is to comment out NOCOPY the following line.
472
473 AND ra.reversal_gl_date is null --Not rate adjusted or reversed
474 */
475 AND ra.posting_control_id = -3
476 AND g_ae_doc_rec.source_table = 'RA'
477 AND nvl(ra.confirmed_flag,'Y') = 'Y' ;
478
479 /*----------------------------------------------------------------------+
480 | Get the app id of the record with which the UNAPP is paired this |
481 | is necessary as in update mode delete is called first and then create|
482 +----------------------------------------------------------------------*/
483 IF l_status = 'UNAPP' THEN
484
485 SELECT ard.source_id_secondary
486 INTO g_ae_doc_rec.source_id_old
487 FROM ar_distributions ard
488 where ard.source_id = g_ae_doc_rec.source_id
489 and ard.source_table = 'RA';
490
491 END IF;
492
493 /*-------------------------------------------------------------------+
494 | Delete all accounting for source id and source table combination |
495 | if valid candidate for deletion |
496 +-------------------------------------------------------------------*/
497
498 -- MRC Trigger Elimination:
499 DELETE FROM AR_DISTRIBUTIONS
500 WHERE source_id = g_ae_doc_rec.source_id
501 AND source_table = 'RA'
502 RETURNING line_id
503 BULK COLLECT INTO l_ar_dist_key_value_list;
504
505 IF PG_DEBUG in ('Y', 'C') THEN
506 arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_RA()-');
507 END IF;
508
509 EXCEPTION
510 WHEN NO_DATA_FOUND THEN
511 IF PG_DEBUG in ('Y', 'C') THEN
512 arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_RA - NO_DATA_FOUND' );
513 END IF;
514 p_ae_deleted := FALSE;
515 RAISE;
516
517 WHEN OTHERS THEN
518 IF PG_DEBUG in ('Y', 'C') THEN
519 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Delete_RA - OTHERS');
520 END IF;
521 p_ae_deleted := FALSE;
522 RAISE ;
523
524 END Delete_RA;
525
526 /* =======================================================================
527 |
528 | PROCEDURE Delete_MCD
529 |
530 | DESCRIPTION
531 | Deletes accounting associated with a Miscellaneous Cash Receipt or
532 | Payment. Note record from child (AR_DISTRIBUTIONS) table must be
533 | deleted first.
534 |
535 | PARAMETERS
536 | p_ae_deleted indicates whether records were deleted
537 | for source_id
538 * ======================================================================*/
539 PROCEDURE Delete_MCD(p_ae_deleted OUT NOCOPY BOOLEAN) IS
540
541 CURSOR del_misc_rec IS
542 SELECT mcd.misc_cash_distribution_id misc_dist_id
543 FROM ar_misc_cash_distributions mcd
544 WHERE mcd.cash_receipt_id = g_ae_doc_rec.document_id
545 AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate not those reversed
546 AND mcd.posting_control_id = -3 --Not posted
547 AND EXISTS (SELECT 'x'
548 FROM ar_distributions ard
549 WHERE ard.source_id = mcd.misc_cash_distribution_id
550 AND ard.source_table = 'MCD');
551
552 l_dummy VARCHAR2(1);
553
554 l_ar_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
555
556 BEGIN
557 IF PG_DEBUG in ('Y', 'C') THEN
558 arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_MCD()+');
559 END IF;
560
561 p_ae_deleted := FALSE;
562 l_dummy := 'N';
563
564 FOR l_misc_rec IN del_misc_rec LOOP
565
566 DELETE FROM ar_distributions ard
567 WHERE ard.source_id = l_misc_rec.misc_dist_id
568 AND ard.source_table = 'MCD'
569 RETURNING line_id
570 BULK COLLECT INTO l_ar_dist_key_value_list;
571
572 p_ae_deleted := TRUE;
573 l_dummy := 'Y';
574
575 END LOOP;
576
577 --Force a No data found exception to be raised if call made and no rows to delete
578 SELECT 'x'
579 INTO l_dummy
580 FROM dual
581 WHERE l_dummy = 'Y';
582
583 IF PG_DEBUG in ('Y', 'C') THEN
584 arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_MCD()-');
585 END IF;
586
587 EXCEPTION
588 WHEN NO_DATA_FOUND THEN
589 IF PG_DEBUG in ('Y', 'C') THEN
590 arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_MCD - NO_DATA_FOUND' );
591 END IF;
592 p_ae_deleted := FALSE;
593 RAISE;
594
595 WHEN OTHERS THEN
596 IF PG_DEBUG in ('Y', 'C') THEN
597 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Delete_MCD');
598 END IF;
599 p_ae_deleted := FALSE;
600 RAISE ;
601
602 END Delete_MCD;
603
604 /* =======================================================================
605 |
606 | PROCEDURE Reverse_Receipt_CM
607 |
608 | DESCRIPTION
609 | This procedure reverses the records in AR_DISTRIBUTIONS for a
610 | Misc Cash Receipt, Cash Receipts and Credit Memo applications.
611 | There is a concept of caching used for UNAPP records, as if they
612 | are paired before Reversal they need to be paired after Reversal.
613 | In order for this caching to work, the APP, ACC and UNID records
614 | must be processed before the UNAPP records
615 |
616 | For Bills Receivable linked deferred tax for an application being
617 | reversed is also backed out NOCOPY by using a link id to reverse out NOCOPY the
618 | tax accounting associated with the Transaction history record for
619 | the maturity date event for a Bill. For this change a union was
620 | added and the source table is populated from g_ae_doc_rec
621 |
622 | PARAMETERS
623 | None
624 * ======================================================================*/
625 PROCEDURE Reverse_Receipt_CM IS
626
627 -- MRC TRIGGER REPLACEMENT: Enumerate all columns and add two more unions
628 -- to retrieve MRC data
629
630 CURSOR get_old_ard IS
631 select ard.line_id,
632 ard.source_type,
633 ard.source_id_secondary,
634 ard.source_type_secondary,
635 ard.source_table_secondary,
636 ard.code_combination_id,
637 ard.amount_dr,
638 ard.amount_cr,
639 ard.acctd_amount_dr,
640 ard.acctd_amount_cr,
641 ard.taxable_entered_cr,
642 ard.taxable_entered_dr,
643 ard.taxable_accounted_cr,
644 ard.taxable_accounted_dr,
645 ard.currency_code,
646 ard.currency_conversion_rate,
647 ard.currency_conversion_type,
648 ard.currency_conversion_date,
649 ard.third_party_id,
650 ard.third_party_sub_id,
651 ard.tax_group_code_id,
652 ard.tax_code_id,
653 ard.location_segment_id,
654 ard.tax_link_id,
655 ard.ref_customer_trx_line_id,
656 ard.ref_cust_trx_line_gl_dist_id,
657 ard.ref_line_id,
658 --{2979254 ref_dist_ccid and ref_dist_flag
659 ard.ref_dist_ccid,
660 ard.ref_mf_dist_flag,
661 ard.ref_account_class,
662 ard.activity_bucket,
663 --}
664 --{3377004
665 ard.from_amount_dr,
666 ard.from_amount_cr,
667 ard.from_acctd_amount_dr,
668 ard.from_acctd_amount_cr,
669 DECODE(ard.ref_customer_trx_line_id, NULL, 'N',
670 DECODE(ard.ref_line_id, NULL, 'ADDCTL',
671 DECODE(line_trx_type.line_type,'CHARGES','ADDCTL',
672 DECODE(adjsrctp.source_type,'CHARGES','ADDCHRG'
673 ,'FINCHRG','ADDCHRG'
674 ,'ADDCTL')))) WHICH_BUCKET,
675 line_trx_type.type trx_type
676 --}
677 from ar_distributions ard,
678 ar_distributions adjsrctp,
679 --{ For CM APP on legacy from 11i
680 (SELECT tt.type type,
681 ctl.customer_trx_line_id customer_trx_line_id,
682 ctl.line_type line_type
683 FROM ra_customer_trx_lines ctl,
684 ra_customer_trx trx,
685 ra_cust_trx_types tt
686 WHERE ctl.customer_trx_id = trx.customer_trx_id
687 AND trx.cust_trx_type_id = tt.cust_trx_type_id
688 AND tt.org_id = trx.org_id) line_trx_type
689 --}
690 where g_ae_sys_rec.sob_type = 'P'
691 and ard.source_id = g_ae_doc_rec.source_id_old
692 and ard.source_table = g_ae_doc_rec.source_table
693 and nvl(ard.source_type_secondary,'X') NOT IN
694 ('ASSIGNMENT_RECONCILE','RECONCILE')
695 and ard.ref_line_id = adjsrctp.line_id(+) --3377004
696 and ard.ref_customer_trx_line_id = line_trx_type.customer_trx_line_id(+)
697 UNION
698 select ard.line_id,
699 ard.source_type,
700 ard.source_id_secondary,
701 ard.source_type_secondary,
702 ard.source_table_secondary,
703 ard.code_combination_id,
704 ard.amount_dr,
705 ard.amount_cr,
706 ard.acctd_amount_dr,
707 ard.acctd_amount_cr,
708 ard.taxable_entered_cr,
709 ard.taxable_entered_dr,
710 ard.taxable_accounted_cr,
711 ard.taxable_accounted_dr,
712 ard.currency_code,
713 ard.currency_conversion_rate,
714 ard.currency_conversion_type,
715 ard.currency_conversion_date,
716 ard.third_party_id,
717 ard.third_party_sub_id,
718 ard.tax_group_code_id,
719 ard.tax_code_id,
720 ard.location_segment_id,
721 ard.tax_link_id,
722 ard.ref_customer_trx_line_id,
723 ard.ref_cust_trx_line_gl_dist_id,
724 ard.ref_line_id,
725 --{2979254 ref_dist_ccid and ref_dist_flag
726 ard.ref_dist_ccid,
727 ard.ref_mf_dist_flag,
728 ard.ref_account_class,
729 ard.activity_bucket,
730 --}
731 --{3377004
732 ard.from_amount_dr,
733 ard.from_amount_cr,
734 ard.from_acctd_amount_dr,
735 ard.from_acctd_amount_cr,
736 DECODE(ard.ref_customer_trx_line_id, NULL, 'N',
737 DECODE(ard.ref_line_id, NULL, 'ADDCTL',
738 DECODE(line_trx_type.line_type,'CHARGES','ADDCTL',
739 DECODE(adjsrctp.source_type,'CHARGES','ADDCHRG'
740 ,'FINCHRG','ADDCHRG'
741 ,'ADDCTL')))) WHICH_BUCKET,
742 line_trx_type.type trx_type
743 --}
744 from ar_distributions ard,
745 ar_receivable_applications app,
746 ar_distributions adjsrctp,
747 -- For CM APP on legacy from 11i
748 (SELECT tt.type type,
749 ctl.customer_trx_line_id customer_trx_line_id,
750 ctl.line_type line_type
751 FROM ra_customer_trx_lines ctl,
752 ra_customer_trx trx,
753 ra_cust_trx_types tt
754 WHERE ctl.customer_trx_id = trx.customer_trx_id
755 AND trx.cust_trx_type_id = tt.cust_trx_type_id
756 AND tt.org_id = trx.org_id) line_trx_type
757 --}
758 where g_ae_sys_rec.sob_type = 'P'
759 and app.receivable_application_id = g_ae_doc_rec.source_id_old
760 and ard.source_id = app.link_to_trx_hist_id
761 and nvl(ard.source_type_secondary,'X') NOT IN
762 ('ASSIGNMENT_RECONCILE','RECONCILE')
763 and ard.source_table = 'TH' --for Bills Receivable Standard/Factored
764 and nvl(g_ae_doc_rec.event,'NONE') <> 'RISK_UNELIMINATED'
765 and ard.ref_line_id = adjsrctp.line_id(+) --3377004
766 and ard.ref_customer_trx_line_id = line_trx_type.customer_trx_line_id(+)
767 order by 1;
768
769 -- The maintenance of the line balances is not required for transaction marked as R12_11ICASH
770 CURSOR verif_trx_mark(p_app_id IN NUMBER)
771 IS
772 SELECT trx.upgrade_method trx_upgrade_method,
773 app.applied_customer_trx_id trx_id,
774 cm.upgrade_method cm_upgrade_method,
775 app.customer_trx_id cm_id
776 FROM ar_receivable_applications app,
777 ra_customer_trx trx,
778 ra_customer_trx cm
779 WHERE app.receivable_application_id = p_app_id
780 AND app.applied_customer_trx_id = trx.customer_trx_id
781 AND app.customer_trx_id = cm.customer_trx_id(+);
782
783 l_record_mark verif_trx_mark%ROWTYPE;
784 l_trx_rec ra_customer_trx%ROWTYPE;
785 l_requery VARCHAR2(1) := 'N';
786 /*-------------------------------------------------------------------+
787 | Get exchange rate, third part details and amounts for transaction |
788 | to which application has been made. There is a bit of redundancy |
789 | as the main select routine for document could be used, however |
790 | did not want to destabilize any logic in Get_Doc_Entitity_Data |
791 +-------------------------------------------------------------------*/
792 CURSOR get_app_details IS
793 SELECT app.applied_customer_trx_id applied_customer_trx_id,
794 app.application_type application_type,
795 app.amount_applied + nvl(app.earned_discount_taken,0)
796 + nvl(app.unearned_discount_taken,0) amount_applied ,
797 nvl(app.acctd_amount_applied_to,0) +
798 nvl(app.acctd_earned_discount_taken,0) +
799 nvl(app.acctd_unearned_discount_taken,0) acctd_amount_applied_to,
800 app.customer_trx_id customer_trx_id,
801 app.acctd_amount_applied_from acctd_amount_applied_from,
802 ctinv.invoice_currency_code invoice_currency_code,
803 ctinv.exchange_rate exchange_rate,
804 ctinv.exchange_rate_type exchange_rate_type,
805 ctinv.exchange_date exchange_date,
806 ctinv.trx_date trx_date,
807 ctinv.bill_to_customer_id bill_to_customer_id,
808 ctinv.bill_to_site_use_id bill_to_site_use_id,
809 ctinv.drawee_id drawee_id,
810 ctinv.drawee_site_use_id drawee_site_use_id,
811 ctcm.invoice_currency_code cm_invoice_currency_code,
812 ctcm.exchange_rate cm_exchange_rate,
813 ctcm.exchange_rate_type cm_exchange_rate_type,
814 ctcm.exchange_date cm_exchange_date,
815 ctcm.trx_date cm_trx_date,
816 ctcm.bill_to_customer_id cm_bill_to_customer_id,
817 ctcm.bill_to_site_use_id cm_bill_to_site_use_id
818 from ar_receivable_applications app ,
819 ra_customer_trx ctinv,
820 ra_customer_trx ctcm
821 where app.receivable_application_id = g_ae_doc_rec.source_id_old
822 and app.status = 'APP'
823 and nvl(confirmed_flag,'Y') = 'Y'
824 and g_ae_doc_rec.source_table = 'RA'
825 and app.applied_customer_trx_id = ctinv.customer_trx_id
826 and app.customer_trx_id = ctcm.customer_trx_id (+);
827
828 l_ael_line_rec ae_line_rec_type;
829 l_ael_empty_line_rec ae_line_rec_type;
830 i BINARY_INTEGER:= 1;
831 l_set_pairing_id BOOLEAN:= FALSE;
832 l_ae_line_tbl ae_line_tbl_type;
833 l_ae_ctr NUMBER;
834 l_ctr NUMBER;
835 l_cust_inv_rec ra_customer_trx%ROWTYPE;
836 l_cust_cm_rec ra_customer_trx%ROWTYPE;
837 l_applied_customer_trx_id NUMBER;
838 l_amount_applied NUMBER;
839 l_acctd_amount_applied_to NUMBER;
840 --{3377004
841 l_ctl_rem_amt ctl_rem_amt_type;
842 l_index NUMBER := 0;
843 --}
844 BEGIN
845 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Reverse_Receipt_CM()+');
846
847 -- Check for upgrade_method
848 OPEN verif_trx_mark(g_ae_doc_rec.source_id_old);
849 FETCH verif_trx_mark INTO l_record_mark;
850 IF verif_trx_mark%NOTFOUND THEN
851 -- Nothing will happen as no distributions will be picked up
852 l_requery := 'N';
853 ELSE
854 l_requery := 'N';
855 IF l_record_mark.trx_upgrade_method IS NULL AND l_record_mark.trx_id IS NOT NULL THEN
856 l_trx_rec.customer_trx_id := l_record_mark.trx_id;
857 arp_det_dist_pkg.set_original_rem_amt(p_customer_trx => l_trx_rec);
858 l_requery := 'Y';
859 END IF;
860 IF l_record_mark.cm_upgrade_method IS NULL AND l_record_mark.cm_id IS NOT NULL THEN
861 l_trx_rec.customer_trx_id := l_record_mark.cm_id;
862 arp_det_dist_pkg.set_original_rem_amt(p_customer_trx => l_trx_rec);
863 l_requery := 'Y';
864 END IF;
865 END IF;
866 CLOSE verif_trx_mark;
867
868 IF l_requery = 'Y' THEN
869 OPEN verif_trx_mark(g_ae_doc_rec.source_id_old);
870 FETCH verif_trx_mark INTO l_record_mark;
871 CLOSE verif_trx_mark;
872 END IF;
873 --}
874 --ard_cash_receipt_id Global variable for package
875 IF ard_cash_receipt_id <> g_ae_doc_rec.document_id THEN
876
877 --Initialise pairing table for UNAPP records for new CR
878
879 ae_app_pair_tbl := g_empty_ae_app_pair_tbl;
880 ae_app_pair_tbl_ctr := 0;
881 ard_cash_receipt_id := g_ae_doc_rec.document_id;
882
883 END IF;
884
885 FOR l_ard_rec in get_old_ard LOOP
886
887 --Initialize build record
888 l_ael_line_rec := l_ael_empty_line_rec ;
889
890 --There is a chance that the current record may have a paired UNAPP record
891 --so cache the new app and old app source id's in the table to determine pairing in Rel 11.5
892 /* J Rautiainen BR Implementation */
893 IF l_ard_rec.source_type IN ('REC', 'ACC', 'UNID','SHORT_TERM_DEBT','FACTOR','REMITTANCE','UNPAIDREC',
894 'OTHER ACC','ACTIVITY') THEN
895
896 ae_app_pair_tbl_ctr := ae_app_pair_tbl_ctr + 1;
897 ae_app_pair_tbl(ae_app_pair_tbl_ctr).status := l_ard_rec.source_type;
898 ae_app_pair_tbl(ae_app_pair_tbl_ctr).source_id := g_ae_doc_rec.source_id;
899 ae_app_pair_tbl(ae_app_pair_tbl_ctr).source_id_old := g_ae_doc_rec.source_id_old;
900 ae_app_pair_tbl(ae_app_pair_tbl_ctr).source_table := g_ae_doc_rec.source_table;
901
902 ELSIF l_ard_rec.source_type = 'UNAPP' AND ae_app_pair_tbl.EXISTS(ae_app_pair_tbl_ctr) THEN
903
904 FOR i IN ae_app_pair_tbl.FIRST .. ae_app_pair_tbl.LAST LOOP
905
906 --We have hit the APP record associated with this UNAPP record
907 IF ae_app_pair_tbl(i).source_id_old = l_ard_rec.source_id_secondary THEN
908 l_ael_line_rec.source_id_secondary := ae_app_pair_tbl(i).source_id; -- Pair New UNAPP with new APP
909 l_ael_line_rec.source_table_secondary := ae_app_pair_tbl(i).source_table;
910 l_set_pairing_id := TRUE;
911 EXIT;
912 END IF;
913
914 --If a Release pre release 11.0 receipt is being reversed then there would be no
915 --secondary id populated to indicate pairing as it is difficult to determine this,
916 --this would be part of a future release.
917
918 END LOOP;
919 END IF;
920
921 l_ael_line_rec.ae_line_type := l_ard_rec.source_type ;
922 l_ael_line_rec.ae_line_type_secondary := l_ard_rec.source_type_secondary ;
923 l_ael_line_rec.source_id := g_ae_doc_rec.source_id ;
924 l_ael_line_rec.source_table := g_ae_doc_rec.source_table ;
925
926 IF (NOT l_set_pairing_id) THEN --if not set above
927 l_ael_line_rec.source_id_secondary := l_ard_rec.source_id_secondary ;
928 l_ael_line_rec.source_table_secondary := l_ard_rec.source_table_secondary ;
929 END IF;
930
931 l_ael_line_rec.account := l_ard_rec.code_combination_id ;
932
933 --Set reversed source id for APP - REC records only for Receipts as this is a
934 --requirement by MRC to know which gain or loss account needs to be offset in
935 --their reporting set of books as for all APP records there could be a gain or
936 --loss
937 /* J Rautiainen BR implementation */
938 IF ((g_ae_doc_rec.source_table = 'RA') AND
939 (l_ard_rec.source_type in ('REC','FACTOR','REMITTANCE','UNPAIDREC',
940 --{3377004
941 'EDISC','UNEDISC'))) THEN
942 --EDISC and UNEDISC should be also back out from Reversal process
943 --otherwise the line level balance will be wrong because the amounts
944 --affected to EDISC and UNEDISC are missing
945 --}
946 l_ael_line_rec.reversed_source_id := g_ae_doc_rec.source_id_old ;
947 END IF;
948
949 -- For reversals swap debits and credits
950 l_ael_line_rec.entered_cr := l_ard_rec.amount_dr;
951 l_ael_line_rec.accounted_cr := l_ard_rec.acctd_amount_dr;
952
953 l_ael_line_rec.entered_dr := l_ard_rec.amount_cr;
954 l_ael_line_rec.accounted_dr := l_ard_rec.acctd_amount_cr;
955
956 l_ael_line_rec.taxable_entered_cr := l_ard_rec.taxable_entered_dr;
957 l_ael_line_rec.taxable_accounted_cr := l_ard_rec.taxable_accounted_dr;
958
959 l_ael_line_rec.taxable_entered_dr := l_ard_rec.taxable_entered_cr;
960 l_ael_line_rec.taxable_accounted_dr := l_ard_rec.taxable_accounted_cr;
961
962 l_ael_line_rec.currency_code := l_ard_rec.currency_code;
963 l_ael_line_rec.currency_conversion_rate := l_ard_rec.currency_conversion_rate;
964 l_ael_line_rec.currency_conversion_type := l_ard_rec.currency_conversion_type;
965 l_ael_line_rec.currency_conversion_date := l_ard_rec.currency_conversion_date;
966 l_ael_line_rec.third_party_id := l_ard_rec.third_party_id;
967 l_ael_line_rec.third_party_sub_id := l_ard_rec.third_party_sub_id;
968 l_ael_line_rec.tax_group_code_id := l_ard_rec.tax_group_code_id;
969 l_ael_line_rec.tax_code_id := l_ard_rec.tax_code_id;
970 l_ael_line_rec.location_segment_id := l_ard_rec.location_segment_id;
971 l_ael_line_rec.tax_link_id := l_ard_rec.tax_link_id;
972 --{BUG#2979254
973 l_ael_line_rec.ref_customer_trx_line_id := l_ard_rec.ref_customer_trx_line_id;
974 l_ael_line_rec.ref_cust_trx_line_gl_dist_id:= l_ard_rec.ref_cust_trx_line_gl_dist_id;
975 l_ael_line_rec.ref_line_id := l_ard_rec.ref_line_id;
976 --}
977 --{ref_dist_ccid + ref_mf_dist_flag
978 l_ael_line_rec.ref_account_class := l_ard_rec.ref_account_class;
979 l_ael_line_rec.activity_bucket := l_ard_rec.activity_bucket;
980 l_ael_line_rec.ref_dist_ccid := l_ard_rec.ref_dist_ccid;
981 l_ael_line_rec.ref_mf_dist_flag := l_ard_rec.ref_mf_dist_flag;
982 --}
983 --{3377004
984 l_ael_line_rec.from_amount_dr := l_ard_rec.from_amount_cr;
985 l_ael_line_rec.from_amount_cr := l_ard_rec.from_amount_dr;
986 l_ael_line_rec.from_acctd_amount_dr := l_ard_rec.from_acctd_amount_cr;
987 l_ael_line_rec.from_acctd_amount_cr := l_ard_rec.from_acctd_amount_dr;
988 --}
989 -- Assign AEL for Reversal
990 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
991
992 --{3377004
993 arp_standard.debug(' l_ard_rec.WHICH_BUCKET:'||l_ard_rec.WHICH_BUCKET);
994 arp_standard.debug(' l_ard_rec.ref_customer_trx_line_id:'||l_ard_rec.ref_customer_trx_line_id);
995 arp_standard.debug(' l_ard_rec.ref_line_id:'||l_ard_rec.ref_line_id);
996 arp_standard.debug(' trx_upgrade_method :'||l_record_mark.trx_upgrade_method);
997 arp_standard.debug(' cm_upgrade_method :'||l_record_mark.cm_upgrade_method);
998
999 IF l_ard_rec.WHICH_BUCKET = 'N' THEN
1000
1001 NULL;
1002
1003 ELSIF l_ard_rec.WHICH_BUCKET = 'ADDCTL' THEN
1004
1005 IF (l_ard_rec.trx_type = 'INV'
1006 AND l_record_mark.trx_upgrade_method NOT IN ('R12_11ICASH','R12_NLB')) OR
1007 (l_ard_rec.trx_type = 'CM'
1008 AND l_record_mark.cm_upgrade_method NOT IN ('R12_11ICASH','R12_NLB'))
1009 THEN --HYUNLB
1010
1011 l_index := ctl_id_index(l_ctl_rem_amt.customer_trx_line_id,
1012 l_ard_rec.ref_customer_trx_line_id);
1013
1014 IF NOT l_ctl_rem_amt.amount_due_remaining.EXISTS(l_index) THEN
1015 init_rem_amt(x_rem_amt => l_ctl_rem_amt,
1016 p_index => l_index);
1017 END IF;
1018
1019 l_ctl_rem_amt.customer_trx_line_id(l_index) := l_ard_rec.ref_customer_trx_line_id;
1020
1021 IF l_ard_rec.source_type IN ('EDISC','UNEDISC') THEN
1022 l_ctl_rem_amt.amount_due_remaining(l_index) :=
1023 NVL(l_ctl_rem_amt.amount_due_remaining(l_index),0)
1024 + (-NVL(l_ard_rec.amount_cr,0) + NVL(l_ard_rec.amount_dr,0));
1025
1026 l_ctl_rem_amt.acctd_amount_due_remaining(l_index) :=
1027 NVL(l_ctl_rem_amt.acctd_amount_due_remaining(l_index),0)
1028 + (NVL(-l_ard_rec.acctd_amount_cr,0) + NVL(l_ard_rec.acctd_amount_dr,0));
1029 ELSE
1030 l_ctl_rem_amt.amount_due_remaining(l_index) :=
1031 NVL(l_ctl_rem_amt.amount_due_remaining(l_index),0)
1032 + (NVL(l_ard_rec.amount_cr,0) - NVL(l_ard_rec.amount_dr,0));
1033
1034 l_ctl_rem_amt.acctd_amount_due_remaining(l_index) :=
1035 NVL(l_ctl_rem_amt.acctd_amount_due_remaining(l_index),0)
1036 + (NVL(l_ard_rec.acctd_amount_cr,0) - NVL(l_ard_rec.acctd_amount_dr,0));
1037 END IF;
1038 arp_standard.debug('l_ctl_rem_amt.customer_trx_line_id('||l_index||'):'||
1039 l_ctl_rem_amt.customer_trx_line_id(l_index));
1040 arp_standard.debug('l_ctl_rem_amt.amount_due_remaining('||l_index||'):'||
1041 l_ctl_rem_amt.amount_due_remaining(l_index));
1042 arp_standard.debug('l_ctl_rem_amt.acctd_amount_due_remaining('||l_index||'):'||
1043 l_ctl_rem_amt.acctd_amount_due_remaining(l_index));
1044
1045 END IF; --1
1046
1047 ELSIF l_ard_rec.WHICH_BUCKET = 'ADDCHRG' THEN
1048
1049 IF (l_ard_rec.trx_type = 'INV'
1050 AND l_record_mark.trx_upgrade_method NOT IN ('R12_11ICASH','R12_NLB')) OR
1051 (l_ard_rec.trx_type = 'CM'
1052 AND l_record_mark.cm_upgrade_method NOT IN ('R12_11ICASH','R12_NLB'))
1053 THEN --HYUNLB
1054
1055 l_index := ctl_id_index(l_ctl_rem_amt.customer_trx_line_id,
1056 l_ard_rec.ref_customer_trx_line_id);
1057
1058 IF NOT l_ctl_rem_amt.chrg_amount_remaining.EXISTS(l_index) THEN
1059 init_rem_amt(x_rem_amt => l_ctl_rem_amt,
1060 p_index => l_index);
1061 END IF;
1062
1063 l_ctl_rem_amt.customer_trx_line_id(l_index) := l_ard_rec.ref_customer_trx_line_id;
1064
1065 IF l_ard_rec.source_type IN ('EDISC','UNEDISC') THEN
1066 l_ctl_rem_amt.chrg_amount_remaining(l_index) :=
1067 NVL(l_ctl_rem_amt.chrg_amount_remaining(l_index),0)
1068 + (-NVL(l_ard_rec.amount_cr,0) + NVL(l_ard_rec.amount_dr,0));
1069
1070 l_ctl_rem_amt.chrg_acctd_amount_remaining(l_index) :=
1071 NVL(l_ctl_rem_amt.chrg_acctd_amount_remaining(l_index),0)
1072 + (-NVL(l_ard_rec.acctd_amount_cr,0) + NVL(l_ard_rec.acctd_amount_dr,0));
1073 ELSE
1074 l_ctl_rem_amt.chrg_amount_remaining(l_index) :=
1075 NVL(l_ctl_rem_amt.chrg_amount_remaining(l_index),0)
1076 + (NVL(l_ard_rec.amount_cr,0) - NVL(l_ard_rec.amount_dr,0));
1077
1078 l_ctl_rem_amt.chrg_acctd_amount_remaining(l_index) :=
1079 NVL(l_ctl_rem_amt.chrg_acctd_amount_remaining(l_index),0)
1080 + (NVL(l_ard_rec.acctd_amount_cr,0) - NVL(l_ard_rec.acctd_amount_dr,0));
1081 END IF;
1082 arp_standard.debug('l_ctl_rem_amt.customer_trx_line_id('||l_index||'):'||
1083 l_ctl_rem_amt.customer_trx_line_id(l_index));
1084 arp_standard.debug('l_ctl_rem_amt.chrg_amount_remaining('||l_index||'):'||
1085 l_ctl_rem_amt.chrg_amount_remaining(l_index));
1086 arp_standard.debug('l_ctl_rem_amt.chrg_acctd_amount_remaining('||l_index||'):'||
1087 l_ctl_rem_amt.chrg_acctd_amount_remaining(l_index));
1088
1089 END IF;
1090 END IF;
1091
1092 --}
1093 END LOOP;
1094
1095 --{bug#4554703 - cm reversal needs to flag the app record as r12
1096 -- always. There should not be any differences
1097 UPDATE ar_receivable_applications
1098 SET upgrade_method = 'R12'
1099 WHERE receivable_application_id = g_ae_doc_rec.source_id;
1100 --}
1101
1102 --{3377004
1103 IF l_index <> 0 THEN
1104
1105 FORALL m IN l_ctl_rem_amt.customer_trx_line_id.FIRST .. l_ctl_rem_amt.customer_trx_line_id.LAST
1106 UPDATE ra_customer_trx_lines
1107 SET AMOUNT_DUE_REMAINING = AMOUNT_DUE_REMAINING + l_ctl_rem_amt.amount_due_remaining(m),
1108 ACCTD_AMOUNT_DUE_REMAINING = ACCTD_AMOUNT_DUE_REMAINING + l_ctl_rem_amt.acctd_amount_due_remaining(m),
1109 CHRG_AMOUNT_REMAINING = CHRG_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_amount_remaining(m),
1110 CHRG_ACCTD_AMOUNT_REMAINING = CHRG_ACCTD_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_acctd_amount_remaining(m)
1111 WHERE customer_trx_line_id = l_ctl_rem_amt.customer_trx_line_id(m);
1112
1113 --BUG#4753570 make the amount on trx line and gloabl has to in sync
1114 FORALL m IN l_ctl_rem_amt.customer_trx_line_id.FIRST .. l_ctl_rem_amt.customer_trx_line_id.LAST
1115 UPDATE ra_customer_trx_lines_gt
1116 SET AMOUNT_DUE_REMAINING = AMOUNT_DUE_REMAINING + l_ctl_rem_amt.amount_due_remaining(m),
1117 ACCTD_AMOUNT_DUE_REMAINING = ACCTD_AMOUNT_DUE_REMAINING + l_ctl_rem_amt.acctd_amount_due_remaining(m),
1118 CHRG_AMOUNT_REMAINING = CHRG_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_amount_remaining(m),
1119 CHRG_ACCTD_AMOUNT_REMAINING = CHRG_ACCTD_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_acctd_amount_remaining(m)
1120 WHERE customer_trx_line_id = l_ctl_rem_amt.customer_trx_line_id(m);
1121
1122
1123 END IF;
1124 --}
1125
1126 /*-----------------------------------------------------------------------------------+
1127 |Call the Reconciliation routine, this is necessary because the transaction or Bill |
1128 |which may have been overapplied is now closed due to reversal, or else the reversal|
1129 |may have resulted in re-opening the transaction, hence we need to back out NOCOPY the old |
1130 |reconciliation entries for the Bill or the Transactions |
1131 +-----------------------------------------------------------------------------------*/
1132 FOR l_get_app in get_app_details LOOP --loop executes once only for APP
1133
1134 /*-------------------------------------------------------------------------------+
1135 | Set currency and exchange rate details to that of the document which has been |
1136 | applied. These details will get overriden by the transaction line assignment |
1137 | exchange rate details for Bill. |
1138 +-------------------------------------------------------------------------------*/
1139 l_cust_inv_rec.invoice_currency_code := l_get_app.invoice_currency_code;
1140 l_cust_inv_rec.exchange_rate := l_get_app.exchange_rate;
1141 l_cust_inv_rec.exchange_rate_type := l_get_app.exchange_rate_type;
1142 l_cust_inv_rec.exchange_date := l_get_app.exchange_date;
1143 l_cust_inv_rec.trx_date := l_get_app.trx_date;
1144 l_cust_inv_rec.bill_to_customer_id := l_get_app.bill_to_customer_id;
1145 l_cust_inv_rec.bill_to_site_use_id := l_get_app.bill_to_site_use_id;
1146 l_cust_inv_rec.drawee_id := l_get_app.drawee_id;
1147 l_cust_inv_rec.drawee_site_use_id := l_get_app.drawee_site_use_id;
1148
1149 --Required to determine whether the payment schedule is closed or not
1150 l_applied_customer_trx_id := l_get_app.applied_customer_trx_id;
1151 l_amount_applied := l_get_app.amount_applied;
1152 l_acctd_amount_applied_to := l_get_app.acctd_amount_applied_to;
1153
1154 ARP_RECONCILE.Reconcile_trx_br(
1155 p_mode => g_mode ,
1156 p_ae_doc_rec => g_ae_doc_rec ,
1157 p_ae_event_rec => g_ae_event_rec ,
1158 p_cust_inv_rec => l_cust_inv_rec ,
1159 p_activity_cust_trx_id => l_applied_customer_trx_id ,
1160 p_activity_amt => l_amount_applied * -1 ,
1161 p_activity_acctd_amt => l_acctd_amount_applied_to * -1 ,
1162 p_call_num => 1 ,
1163 p_g_ae_line_tbl => g_ae_line_tbl ,
1164 p_g_ae_ctr => g_ae_line_ctr );
1165
1166 IF l_get_app.application_type = 'CM' THEN
1167 l_cust_cm_rec.invoice_currency_code := l_get_app.cm_invoice_currency_code;
1168 l_cust_cm_rec.exchange_rate := l_get_app.cm_exchange_rate;
1169 l_cust_cm_rec.exchange_rate_type := l_get_app.cm_exchange_rate_type;
1170 l_cust_cm_rec.exchange_date := l_get_app.cm_exchange_date;
1171 l_cust_cm_rec.trx_date := l_get_app.cm_trx_date;
1172 l_cust_cm_rec.bill_to_customer_id := l_get_app.cm_bill_to_customer_id;
1173 l_cust_cm_rec.bill_to_site_use_id := l_get_app.cm_bill_to_site_use_id;
1174
1175 --Required to determine whether the payment schedule is closed or not
1176 l_applied_customer_trx_id := l_get_app.customer_trx_id;
1177 l_amount_applied := l_get_app.amount_applied;
1178 l_acctd_amount_applied_to := l_get_app.acctd_amount_applied_from;
1179
1180 ARP_RECONCILE.Reconcile_trx_br(
1181 p_mode => g_mode ,
1182 p_ae_doc_rec => g_ae_doc_rec ,
1183 p_ae_event_rec => g_ae_event_rec ,
1184 p_cust_inv_rec => l_cust_inv_rec ,
1185 p_activity_cust_trx_id => l_applied_customer_trx_id ,
1186 p_activity_amt => l_amount_applied ,
1187 p_activity_acctd_amt => l_acctd_amount_applied_to ,
1188 p_call_num => 2 ,
1189 p_g_ae_line_tbl => g_ae_line_tbl ,
1190 p_g_ae_ctr => g_ae_line_ctr );
1191
1192 END IF; --application type is CM
1193
1194 END LOOP; -- reconciliation routine called for applications
1195
1196 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Reverse_Receipt_CM()-');
1197
1198 EXCEPTION
1199 WHEN NO_DATA_FOUND THEN
1200 arp_standard.debug('ARP_RECEIPTS_MAIN.Reverse_Receipt_CM - NO_DATA_FOUND' );
1201 RAISE;
1202
1203 WHEN OTHERS THEN
1204 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Reverse_Receipt_CM:'||SQLERRM);
1205 RAISE ;
1206
1207 END Reverse_Receipt_CM;
1208
1209 /* =======================================================================
1210 |
1211 | PROCEDURE Get_Doc_Entitity_Data
1212 |
1213 | DESCRIPTION
1214 | This procedure gets the necessary transaction data for each entity
1215 | level.
1216 |
1217 | PARAMETERS
1218 | p_level Entitity Level
1219 | p_app_rec Application Record
1220 | p_cr_rec Cash Receipt Record
1221 | p_cust_inv_rec Invoice document or On Account Credit Memo data
1222 | p_cust_cm_rec Credit Memo data
1223 | p_ctlgd_cm_rec Receivable account for Credit Memo
1224 | p_miscel_rec Gain Loss ccid and fixed rate flag
1225 | p_vat_rec Tax details
1226 | p_curr_rec Currency details for Misc Cash document
1227 |
1228 | NOTES
1229 | The variables miscle1..8 hold currency, exchange rate and pay from
1230 | customer, site information. This is necessary because it may be
1231 | possible that the Receipt is cached and the applications are created
1232 | first as in autoreceipts, hence if the outer join to cash receipts
1233 | returns a null then the document structure values are used.
1234 |
1235 | MODIFICATION HISTORY
1236 | S.Nambiar 14-May-01 Balancing segment for ACTIVITY applications
1237 | will be replaced with balancing segment
1238 | of receipt's UNAPP.
1239 * ======================================================================*/
1240 PROCEDURE Get_Doc_Entitity_Data (
1241 p_level IN VARCHAR2 ,
1242 p_app_rec OUT NOCOPY ar_receivable_applications%ROWTYPE ,
1243 p_cr_rec OUT NOCOPY ar_cash_receipts%ROWTYPE ,
1244 p_cust_inv_rec OUT NOCOPY ra_customer_trx%ROWTYPE ,
1245 p_cust_cm_rec OUT NOCOPY ra_customer_trx%ROWTYPE ,
1246 p_ctlgd_cm_rec OUT NOCOPY ra_cust_trx_line_gl_dist%ROWTYPE ,
1247 p_miscel_rec OUT NOCOPY g_ae_miscel_rec_type ,
1248 p_vat_rec OUT NOCOPY ar_vat_tax%ROWTYPE ,
1249 p_curr_rec OUT NOCOPY ae_curr_rec_type ,
1250 p_rule_rec OUT NOCOPY ae_rule_rec_type) IS
1251
1252 l_gain_loss_ccid ar_system_parameters.code_combination_id_gain%TYPE;
1253 l_concat_segs varchar2(240) ;
1254 l_concat_ids varchar2(2000) ;
1255 l_concat_descs varchar2(2000) ;
1256 l_arerror varchar2(2000) ;
1257 l_id_dummy ar_receivable_applications.code_combination_id%TYPE;
1258 l_cr_unapp_ccid ar_receivable_applications.code_combination_id%TYPE;
1259 l_le_id NUMBER;
1260 l_effective_date DATE;
1261 l_msg_count NUMBER;
1262 l_msg_data VARCHAR2(1024);
1263 l_return_status VARCHAR2(10);
1264 BEGIN
1265
1266 IF PG_DEBUG in ('Y', 'C') THEN
1267 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Get_Doc_Entitity_Data()+');
1268 END IF;
1269
1270 IF p_level = 'RA' then
1271
1272 select ra.receivable_application_id ,
1273 ra.applied_customer_trx_id ,
1274 ra.customer_trx_id ,
1275 ra.applied_payment_schedule_id ,
1276 ra.code_combination_id ,
1277 ra.amount_applied ,
1278 ra.amount_applied_from ,
1279 ra.acctd_amount_applied_to acctd_amount_applied_to,
1280 ra.acctd_amount_applied_from acctd_amount_applied_from,
1281 ra.line_applied ,
1282 ra.tax_applied ,
1283 ra.freight_applied ,
1284 ra.receivables_charges_applied ,
1285 ra.earned_discount_ccid ,
1286 ra.earned_discount_taken ,
1287 ra.acctd_earned_discount_taken acctd_earned_discount_taken,
1288 ra.line_ediscounted ,
1289 ra.tax_ediscounted ,
1290 ra.freight_ediscounted ,
1291 ra.charges_ediscounted ,
1292 ra.unearned_discount_ccid ,
1293 ra.unearned_discount_taken ,
1294 ra.acctd_unearned_discount_taken acctd_unearned_discount_taken,
1295 ra.line_uediscounted ,
1296 ra.tax_uediscounted ,
1297 ra.freight_uediscounted ,
1298 ra.charges_uediscounted ,
1299 ra.status ,
1300 ra.application_type ,
1301 ra.application_ref_id ,
1302 ra.cash_receipt_id ,
1303 ra.reversal_gl_date ,
1304 ra.apply_date ,
1305 ra.confirmed_flag ,
1306 ra.receivables_trx_id ,
1307 ra.cash_receipt_id ,
1308 nvl(cr.currency_code, g_ae_doc_rec.miscel1) ,
1309 nvl(cr.exchange_rate, g_ae_doc_rec.miscel2),
1310 nvl(cr.exchange_rate_type, g_ae_doc_rec.miscel3),
1311 nvl(cr.exchange_date, g_ae_doc_rec.miscel4),
1312 nvl(cr.pay_from_customer, g_ae_doc_rec.miscel5) ,
1313 nvl(cr.customer_site_use_id, g_ae_doc_rec.miscel6),
1314 nvl(cr.remit_bank_acct_use_id,g_ae_doc_rec.miscel7),
1315 nvl(cr.receipt_method_id, g_ae_doc_rec.miscel8) ,
1316 ctinv.invoice_currency_code ,
1317 ctinv.exchange_rate,
1318 ctinv.exchange_rate_type,
1319 ctinv.exchange_date,
1320 ctinv.trx_date ,
1321 ctinv.bill_to_customer_id ,
1322 ctinv.bill_to_site_use_id ,
1323 ctinv.drawee_id ,
1324 ctinv.drawee_site_use_id ,
1325 ctinv.upgrade_method , --Invoice upgrade_method
1326 ctinv.customer_trx_id ,
1327 ctcm.invoice_currency_code ,
1328 ctcm.exchange_rate,
1329 ctcm.exchange_rate_type ,
1330 ctcm.exchange_date,
1331 ctcm.bill_to_customer_id ,
1332 ctcm.bill_to_site_use_id ,
1333 ctcm.upgrade_method , --Cm upgrade_method
1334 ctcm.customer_trx_id ,
1335 ctlgdcm.code_combination_id ,
1336 decode(ra.status,
1337 'APP', decode(
1338 sign(ra.acctd_amount_applied_from -
1339 ra.acctd_amount_applied_to),
1340 -1, g_ae_sys_rec.loss_cc_id,
1341 1, g_ae_sys_rec.gain_cc_id,
1342 ''),
1343 'ACTIVITY', decode(
1344 sign(ra.acctd_amount_applied_from -
1345 ra.acctd_amount_applied_to),
1346 -1, g_ae_sys_rec.loss_cc_id,
1347 1, g_ae_sys_rec.gain_cc_id,
1348 '')),
1349 DECODE(g_ae_doc_rec.document_type,'CREDIT_MEMO',ra.code_combination_id,rma.unapplied_ccid) -- Bug 4112494 CM refunds
1350 into p_app_rec.receivable_application_id ,
1351 p_app_rec.applied_customer_trx_id ,
1352 p_app_rec.customer_trx_id ,
1353 p_app_rec.applied_payment_schedule_id ,
1354 p_app_rec.code_combination_id ,
1355 p_app_rec.amount_applied ,
1356 p_app_rec.amount_applied_from ,
1357 p_app_rec.acctd_amount_applied_to ,
1358 p_app_rec.acctd_amount_applied_from ,
1359 p_app_rec.line_applied ,
1360 p_app_rec.tax_applied ,
1361 p_app_rec.freight_applied ,
1362 p_app_rec.receivables_charges_applied ,
1363 p_app_rec.earned_discount_ccid ,
1364 p_app_rec.earned_discount_taken ,
1365 p_app_rec.acctd_earned_discount_taken ,
1366 p_app_rec.line_ediscounted ,
1367 p_app_rec.tax_ediscounted ,
1368 p_app_rec.freight_ediscounted ,
1369 p_app_rec.charges_ediscounted ,
1370 p_app_rec.unearned_discount_ccid ,
1371 p_app_rec.unearned_discount_taken ,
1372 p_app_rec.acctd_unearned_discount_taken ,
1373 p_app_rec.line_uediscounted ,
1374 p_app_rec.tax_uediscounted ,
1375 p_app_rec.freight_uediscounted ,
1376 p_app_rec.charges_uediscounted ,
1377 p_app_rec.status ,
1378 p_app_rec.application_type ,
1379 p_app_rec.application_ref_id ,
1380 p_app_rec.cash_receipt_id ,
1381 p_app_rec.reversal_gl_date ,
1382 p_app_rec.apply_date ,
1383 p_app_rec.confirmed_flag ,
1384 p_app_rec.receivables_trx_id ,
1385 p_cr_rec.cash_receipt_id ,
1386 p_cr_rec.currency_code ,
1387 p_cr_rec.exchange_rate ,
1388 p_cr_rec.exchange_rate_type ,
1389 p_cr_rec.exchange_date ,
1390 p_cr_rec.pay_from_customer ,
1391 p_cr_rec.customer_site_use_id ,
1392 p_cr_rec.remit_bank_acct_use_id ,
1393 p_cr_rec.receipt_method_id ,
1394 p_cust_inv_rec.invoice_currency_code ,
1395 p_cust_inv_rec.exchange_rate ,
1396 p_cust_inv_rec.exchange_rate_type ,
1397 p_cust_inv_rec.exchange_date ,
1398 p_cust_inv_rec.trx_date ,
1399 p_cust_inv_rec.bill_to_customer_id ,
1400 p_cust_inv_rec.bill_to_site_use_id ,
1401 p_cust_inv_rec.drawee_id ,
1402 p_cust_inv_rec.drawee_site_use_id ,
1403 p_cust_inv_rec.upgrade_method , -- Invoice upgrade_method
1404 p_cust_inv_rec.customer_trx_id ,
1405 p_cust_cm_rec.invoice_currency_code ,
1406 p_cust_cm_rec.exchange_rate ,
1407 p_cust_cm_rec.exchange_rate_type ,
1408 p_cust_cm_rec.exchange_date ,
1409 p_cust_cm_rec.bill_to_customer_id ,
1410 p_cust_cm_rec.bill_to_site_use_id ,
1411 p_cust_cm_rec.upgrade_method , -- CM upgrade_method
1412 p_cust_cm_rec.customer_trx_id ,
1413 p_ctlgd_cm_rec.code_combination_id ,
1414 l_gain_loss_ccid ,
1415 l_cr_unapp_ccid
1416 from ar_receivable_applications ra ,
1417 ar_cash_receipts cr ,
1418 ar_receipt_method_accounts rma ,
1419 ra_customer_trx ctinv ,
1420 ra_customer_trx ctcm ,
1421 ra_cust_trx_line_gl_dist ctlgdcm
1422 where ra.receivable_application_id = g_ae_doc_rec.source_id
1423 and ra.cash_receipt_id = cr.cash_receipt_id (+) --CR UNID, ACC, UNAPP exchange rate Information
1424 and cr.remit_bank_acct_use_id = rma.remit_bank_acct_use_id (+) --UNAPP ccid for receipt
1425 and cr.receipt_method_id = rma.receipt_method_id (+)
1426 and ra.applied_customer_trx_id = ctinv.customer_trx_id (+) --INV REC or On Account CM exchange rate Information
1427 and ra.customer_trx_id = ctcm.customer_trx_id (+) --CM REC exchange rate Information
1428 and ra.customer_trx_id = ctlgdcm.customer_trx_id (+) --CM REC account ccid
1429 and decode(ra.application_type,
1430 'CASH', 'REC',
1431 'CM' , ctlgdcm.account_class) = 'REC'
1432 and decode(ra.application_type,
1433 'CASH', 'Y',
1434 'CM' , ctlgdcm.latest_rec_flag) = 'Y';
1435
1436 --Replace balancing segment for gain, loss or round ccid matching that of
1437 --Receivable account of Invoice or On Account Credit Memo
1438
1439 IF (p_app_rec.status in ('APP','ACTIVITY')) AND
1440 (sign(p_app_rec.acctd_amount_applied_from - p_app_rec.acctd_amount_applied_to) <> 0) THEN
1441
1442 p_miscel_rec.fixed_rate := 'N';
1443
1444 IF p_app_rec.status = 'APP' THEN
1445
1446 IF p_app_rec.application_type = 'CASH' THEN
1447 BEGIN
1448 p_miscel_rec.fixed_rate := arpcurr.isfixedrate(p_cr_rec.currency_code ,
1449 g_ae_sys_rec.base_currency ,
1450 p_cr_rec.receipt_date ,
1451 p_cust_inv_rec.invoice_currency_code ,
1452 p_cust_inv_rec.trx_date
1453 );
1454 EXCEPTION
1455 WHEN GL_CURRENCY_API.INVALID_CURRENCY THEN
1456 p_miscel_rec.fixed_rate := 'N'; --Posting treats this as null
1457 WHEN OTHERS THEN
1458 RAISE;
1459 END;
1460 END IF;
1461
1462 --If fixed rate, override the gain or loss ccid with the round ccid from system parameters
1463 IF p_miscel_rec.fixed_rate = 'Y' THEN
1464 l_gain_loss_ccid := g_ae_sys_rec.round_cc_id;
1465 END IF;
1466
1467 ARP_ALLOCATION_PKG.Substitute_Ccid(p_coa_id => g_ae_sys_rec.coa_id ,
1468 p_original_ccid => l_gain_loss_ccid ,
1469 p_subs_ccid => p_app_rec.code_combination_id ,
1470 p_actual_ccid => p_miscel_rec.gain_loss_ccid );
1471 ELSE
1472
1473 --Replace balancing segment for gain, loss ccid matching that of receipts UNAPP
1474 --for ACTIVITY application.
1475
1476 ARP_ALLOCATION_PKG.Substitute_Ccid(p_coa_id => g_ae_sys_rec.coa_id ,
1477 p_original_ccid => l_gain_loss_ccid ,
1478 p_subs_ccid => l_cr_unapp_ccid ,
1479 p_actual_ccid => p_miscel_rec.gain_loss_ccid );
1480
1481 END IF;
1482
1483 END IF;
1484
1485 IF p_app_rec.status = 'APP' and p_app_rec.application_type = 'CASH' then
1486
1487 /* 4922353 - always get LE regardless of
1488 response from is_le_subscriber */
1489 select legal_entity_id
1490 into l_le_id
1491 from ra_customer_trx
1492 where customer_trx_id = p_app_rec.applied_customer_trx_id;
1493
1494 /* 4594101 - Check if legal entities are in use and, if so,
1495 include the new child/detail table in the join */
1496 IF arp_legal_entity_util.is_le_subscriber
1497 THEN
1498 /* LE is in use */
1499
1500 /* 5236782 - there are cases where no rows will exist
1501 in ar_rec_trx_le_details. The following sql has
1502 been modified to ignore these cases without error */
1503
1504 select nvl(ed.gl_account_source,'NO_SOURCE') ,
1505 nvl(ed.tax_code_source,'NO_SOURCE') ,
1506 ed.tax_recoverable_flag ,
1507 ed.code_combination_id , --activity gl account earned discount
1508 nvl(edd.asset_tax_code, ed.asset_tax_code) ,
1509 nvl(edd.liability_tax_code, ed.liability_tax_code),
1510 '' ,
1511 '' ,
1512 nvl(uned.gl_account_source,'NO_SOURCE') ,
1513 nvl(uned.tax_code_source,'NO_SOURCE') ,
1514 uned.tax_recoverable_flag ,
1515 uned.code_combination_id , --activity gl account unearned discount
1516 nvl(unedd.asset_tax_code,uned.asset_tax_code),
1517 nvl(unedd.liability_tax_code,uned.liability_tax_code),
1518 '' ,
1519 ''
1520 into p_rule_rec.gl_account_source1 , --Earned discounts
1521 p_rule_rec.tax_code_source1 ,
1522 p_rule_rec.tax_recoverable_flag1 ,
1523 p_rule_rec.code_combination_id1 ,
1524 p_rule_rec.asset_tax_code1 ,
1525 p_rule_rec.liability_tax_code1 ,
1526 p_rule_rec.act_tax_non_rec_ccid1 ,
1527 p_rule_rec.act_vat_tax_id1 ,
1528 p_rule_rec.gl_account_source2 , --Unearned discounts
1529 p_rule_rec.tax_code_source2 ,
1530 p_rule_rec.tax_recoverable_flag2 ,
1531 p_rule_rec.code_combination_id2 ,
1532 p_rule_rec.asset_tax_code2 ,
1533 p_rule_rec.liability_tax_code2 ,
1534 p_rule_rec.act_tax_non_rec_ccid2 ,
1535 p_rule_rec.act_vat_tax_id2
1536 from ar_receipt_method_accounts rma,
1537 ar_receivables_trx ed,
1538 ar_rec_trx_le_details edd,
1539 ar_receivables_trx uned,
1540 ar_rec_trx_le_details unedd
1541 where rma.receipt_method_id = p_cr_rec.receipt_method_id
1542 and rma.remit_bank_acct_use_id = p_cr_rec.remit_bank_acct_use_id
1543 and rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
1544 and ed.receivables_trx_id = edd.receivables_trx_id (+)
1545 and edd.legal_entity_id (+) = l_le_id
1546 and rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+)
1547 and uned.receivables_trx_id = unedd.receivables_trx_id (+)
1548 and unedd.legal_entity_id (+) = l_le_id;
1549
1550 ELSE
1551 /* OU is in use, reference ar_receivables_trx directly */
1552
1553 select nvl(ed.gl_account_source,'NO_SOURCE') ,
1554 nvl(ed.tax_code_source,'NO_SOURCE') ,
1555 ed.tax_recoverable_flag ,
1556 ed.code_combination_id , --activity gl account earned discount
1557 ed.asset_tax_code ,
1558 ed.liability_tax_code ,
1559 '' ,
1560 '' ,
1561 nvl(uned.gl_account_source,'NO_SOURCE') ,
1562 nvl(uned.tax_code_source,'NO_SOURCE') ,
1563 uned.tax_recoverable_flag ,
1564 uned.code_combination_id , --activity gl account unearned discount
1565 uned.asset_tax_code ,
1566 uned.liability_tax_code ,
1567 '' ,
1568 ''
1569 into p_rule_rec.gl_account_source1 , --Earned discounts
1570 p_rule_rec.tax_code_source1 ,
1571 p_rule_rec.tax_recoverable_flag1 ,
1572 p_rule_rec.code_combination_id1 ,
1573 p_rule_rec.asset_tax_code1 ,
1574 p_rule_rec.liability_tax_code1 ,
1575 p_rule_rec.act_tax_non_rec_ccid1 ,
1576 p_rule_rec.act_vat_tax_id1 ,
1577 p_rule_rec.gl_account_source2 , --Unearned discounts
1578 p_rule_rec.tax_code_source2 ,
1579 p_rule_rec.tax_recoverable_flag2 ,
1580 p_rule_rec.code_combination_id2 ,
1581 p_rule_rec.asset_tax_code2 ,
1582 p_rule_rec.liability_tax_code2 ,
1583 p_rule_rec.act_tax_non_rec_ccid2 ,
1584 p_rule_rec.act_vat_tax_id2
1585 from ar_receipt_method_accounts rma,
1586 ar_receivables_trx ed,
1587 ar_receivables_trx uned
1588 where rma.receipt_method_id = p_cr_rec.receipt_method_id
1589 and rma.remit_bank_acct_use_id = p_cr_rec.remit_bank_acct_use_id
1590 and rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
1591 and rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+) ;
1592
1593 END IF;
1594
1595 /* Initialize etax for rate validation */
1596 zx_api_pub.set_tax_security_context(
1597 p_api_version => 1.0,
1598 p_init_msg_list => 'T',
1599 p_commit => 'F',
1600 p_validation_level => NULL,
1601 x_return_status => l_return_status,
1602 x_msg_count => l_msg_count,
1603 x_msg_data => l_msg_data,
1604 p_internal_org_id => arp_standard.sysparm.org_id,
1605 p_legal_entity_id => l_le_id,
1606 p_transaction_date => p_app_rec.apply_date,
1607 p_related_doc_date => NULL,
1608 p_adjusted_doc_date=> NULL,
1609 x_effective_date => l_effective_date);
1610
1611 /*---------------------------------------------------------------------+
1612 | Get the non recoverable tax ccid if tax code source is ACTIVITY for |
1613 | Earned discounts. |
1614 +---------------------------------------------------------------------*/
1615 IF ((p_rule_rec.tax_code_source1 = 'ACTIVITY')
1616 AND (p_rule_rec.asset_tax_code1 IS NOT NULL)
1617 AND nvl(p_app_rec.earned_discount_taken,0) <> 0) THEN
1618
1619 Act_Tax_Non_Rec_Ccid('EDISC' ,
1620 p_rule_rec.asset_tax_code1 ,
1621 p_app_rec.apply_date ,
1622 p_rule_rec.act_tax_non_rec_ccid1,
1623 p_rule_rec.act_vat_tax_id1 );
1624
1625 END IF; --end if activity earned dicsounts
1626
1627 /*---------------------------------------------------------------------+
1628 | Get the non recoverable tax ccid if tax code source is ACTIVITY for |
1629 | Unearned discounts. |
1630 +---------------------------------------------------------------------*/
1631 IF ((p_rule_rec.tax_code_source2 = 'ACTIVITY')
1632 AND (p_rule_rec.asset_tax_code2 IS NOT NULL)
1633 AND nvl(p_app_rec.unearned_discount_taken,0) <> 0) THEN
1634
1635 Act_Tax_Non_Rec_Ccid('UNEDISC' ,
1636 p_rule_rec.asset_tax_code2 ,
1637 p_app_rec.apply_date ,
1638 p_rule_rec.act_tax_non_rec_ccid2 ,
1639 p_rule_rec.act_vat_tax_id2 );
1640
1641 END IF; --end if activity unearned discounts
1642
1643 END IF; --end if cash application
1644
1645 ELSIF p_level = 'MCD' then
1646
1647 select cr.cash_receipt_id ,
1648 cr.amount ,
1649 cr.vat_tax_id ,
1650 cr.tax_rate ,
1651 cr.currency_code ,
1652 cr.exchange_rate exchange_rate,
1653 cr.exchange_rate_type exchange_rate_type,
1654 cr.exchange_date exchange_date,
1655 cr.pay_from_customer ,
1656 cr.customer_site_use_id ,
1657 decode(avt.tax_rate_id, null, null,
1658 arp_etax_util.get_tax_account(cr.vat_tax_id,
1659 cr.deposit_date,
1660 'TAX',
1661 'TAX_RATE')),
1662 avt.tax_rate_id ,
1663 fc.precision ,
1664 fc.minimum_accountable_unit
1665 into p_cr_rec.cash_receipt_id ,
1666 p_cr_rec.amount ,
1667 p_cr_rec.vat_tax_id ,
1668 p_cr_rec.tax_rate ,
1669 p_cr_rec.currency_code ,
1670 p_cr_rec.exchange_rate ,
1671 p_cr_rec.exchange_rate_type ,
1672 p_cr_rec.exchange_date ,
1673 p_cr_rec.pay_from_customer ,
1674 p_cr_rec.customer_site_use_id ,
1675 p_vat_rec.tax_account_id ,
1676 p_vat_rec.vat_tax_id ,
1677 p_curr_rec.precision ,
1678 p_curr_rec.minimum_accountable_unit
1679 from ar_cash_receipts cr ,
1680 zx_rates_b avt ,
1681 fnd_currencies fc
1682 where cr.cash_receipt_id = g_ae_doc_rec.document_id
1683 and cr.currency_code = fc.currency_code
1684 and cr.vat_tax_id = avt.tax_rate_id (+);
1685
1686 p_vat_rec.tax_account_id := g_ae_doc_rec.gl_tax_acct; /* Bug fix 2300268 */
1687 END IF;
1688
1689 IF PG_DEBUG in ('Y', 'C') THEN
1690 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Get_Doc_Entitity_Data()-');
1691 END IF;
1692
1693 EXCEPTION
1694 WHEN ARP_ALLOCATION_PKG.flex_subs_ccid_error THEN
1695 IF PG_DEBUG in ('Y', 'C') THEN
1696 arp_standard.debug('ARP_RECEIPTS_MAIN.Get_Doc_Entitity_Data - flex_subs_ccid_error');
1697 END IF;
1698 RAISE;
1699
1700 WHEN NO_DATA_FOUND THEN
1701 IF PG_DEBUG in ('Y', 'C') THEN
1702 arp_standard.debug('ARP_RECEIPTS_MAIN.Get_Doc_Entitity_Data - NO_DATA_FOUND' );
1703 END IF;
1704 RAISE;
1705
1706 WHEN OTHERS THEN
1707 IF PG_DEBUG in ('Y', 'C') THEN
1708 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Get_Doc_Entitity_Data');
1709 END IF;
1710 RAISE ;
1711
1712 END Get_Doc_Entitity_Data;
1713
1714 /* ============================================================================
1715 | PROCEDURE Act_Tax_Non_Rec_Ccid
1716 |
1717 | DESCRIPTION
1718 | This procedure gets the Non Recoverable account when TAX CODE
1719 | SOURCE is activity tax code. For Cash Receipts, liability tax
1720 | code is used, functionality has been provided for Receipts which
1721 | are positive (currently indicate reversals) however this functionality
1722 | for reversal would not get utilized currently.
1723 |
1724 | PARAMETERS
1725 | p_type IN Earned discount, Unearned discount amount
1726 | p_asset_tax_code IN Asset Tax Code from Receivables Trx discount
1727 | p_apply_date IN Receipt apply date which restricts Tax Code
1728 * ============================================================================*/
1729 PROCEDURE Act_Tax_Non_Rec_Ccid (
1730 p_type IN VARCHAR2 ,
1731 p_asset_tax_code IN VARCHAR2 ,
1732 p_apply_date IN DATE ,
1733 p_act_tax_non_rec_ccid OUT NOCOPY ar_receivables_trx.code_combination_id%TYPE ,
1734 p_act_vat_tax_id OUT NOCOPY ar_vat_tax.vat_tax_id%TYPE ) IS
1735
1736 BEGIN
1737
1738 IF PG_DEBUG in ('Y', 'C') THEN
1739 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Act_Tax_Non_Rec_Ccid()+');
1740 END IF;
1741
1742 /*--------------------------------------------------------+
1743 | Get non recoverable tax account for asset or liability |
1744 | tax code for finance charges |
1745 +--------------------------------------------------------*/
1746 p_act_tax_non_rec_ccid := null;
1747 p_act_vat_tax_id := null;
1748
1749 SELECT tax_rate_id
1750 INTO p_act_vat_tax_id
1751 FROM zx_sco_rates
1752 WHERE tax_rate_code = p_asset_tax_code
1753 AND p_apply_date BETWEEN nvl(effective_from, p_apply_date) AND
1754 nvl(effective_to, p_apply_date);
1755
1756 /* Now get the corresponding account */
1757 IF p_type IN ('EDISC','UNEDISC')
1758 THEN
1759
1760 /* 5659355 - non recoverable - should use EDISC_NON_REC
1761 and UNEDISC_NON_REC */
1762 IF p_type = 'EDISC'
1763 THEN
1764 p_act_tax_non_rec_ccid :=
1765 arp_etax_util.get_tax_account(p_act_vat_tax_id,
1766 p_apply_date,
1767 'EDISC_NON_REC',
1768 'TAX_RATE');
1769 ELSE
1770 /* UNEDISC */
1771 p_act_tax_non_rec_ccid :=
1772 arp_etax_util.get_tax_account(p_act_vat_tax_id,
1773 p_apply_date,
1774 'UNEDISC_NON_REC',
1775 'TAX_RATE');
1776
1777 END IF;
1778 ELSE
1779 p_act_tax_non_rec_ccid := NULL;
1780 END IF;
1781
1782 IF PG_DEBUG in ('Y', 'C') THEN
1783 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Act_Tax_Non_Rec_Ccid()+');
1784 END IF;
1785
1786 EXCEPTION
1787 /*-------------------------------------------------------------------+
1788 | Invalid ccid error will be raised in tax allocation routine hence |
1789 | null exception for no data found for non recoverable account. |
1790 +-------------------------------------------------------------------*/
1791 WHEN NO_DATA_FOUND THEN
1792 IF PG_DEBUG in ('Y', 'C') THEN
1793 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Act_Tax_Non_Rec_Ccid - NO_DATA_FOUND');
1794 END IF;
1795 null;
1796
1797 WHEN OTHERS THEN
1798 IF PG_DEBUG in ('Y', 'C') THEN
1799 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Act_Tax_Non_Rec_Ccid');
1800 END IF;
1801 RAISE ;
1802
1803 END Act_Tax_Non_Rec_Ccid;
1804
1805 /* =======================================================================
1806 | PROCEDURE Create_Ae_Lines_Common
1807 |
1808 | DESCRIPTION
1809 | This procedure creates the AE lines at each entity level. Used
1810 | for creating lines as part of Receipt Creation and Reversals.
1811 |
1812 | Functions:
1813 | - Create AE lines.
1814 | - Get additional data to determine the type of AE lines
1815 |
1816 | PARAMETERS
1817 | p_level Entity level from which the procedure was called
1818 * ======================================================================*/
1819 PROCEDURE Create_Ae_Lines_Common (
1820 p_level IN VARCHAR2,
1821 --{HYUDETUPT
1822 p_from_llca_call IN VARCHAR2,
1823 p_gt_id IN NUMBER
1824 --}
1825 ) IS
1826
1827 l_miscel_rec g_ae_miscel_rec_type ;
1828 l_app_rec ar_receivable_applications%ROWTYPE ;
1829 l_cr_rec ar_cash_receipts%ROWTYPE ;
1830 l_cust_inv_rec ra_customer_trx%ROWTYPE ;
1831 l_cust_cm_rec ra_customer_trx%ROWTYPE ;
1832 l_ctlgd_cm_rec ra_cust_trx_line_gl_dist%ROWTYPE ;
1833 l_vat_rec ar_vat_tax%ROWTYPE ;
1834 l_curr_rec ae_curr_rec_type ;
1835 l_rule_rec ae_rule_rec_type ;
1836
1837 BEGIN
1838
1839 IF PG_DEBUG in ('Y', 'C') THEN
1840 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Create_Ae_Lines_Common()+');
1841 END IF;
1842
1843 /*-------------------------------------------------------------+
1844 | Get Document Entitity specific data |
1845 +-------------------------------------------------------------*/
1846
1847 Get_Doc_Entitity_Data(p_level => p_level ,
1848 p_app_rec => l_app_rec ,
1849 p_cr_rec => l_cr_rec ,
1850 p_cust_inv_rec => l_cust_inv_rec ,
1851 p_cust_cm_rec => l_cust_cm_rec ,
1852 p_ctlgd_cm_rec => l_ctlgd_cm_rec ,
1853 p_miscel_rec => l_miscel_rec ,
1854 p_vat_rec => l_vat_rec ,
1855 p_curr_rec => l_curr_rec ,
1856 p_rule_rec => l_rule_rec );
1857
1858 /*------------------------------------------------------+
1859 | Create AE Lines for Cash Receipt |
1860 +------------------------------------------------------*/
1861 IF ((p_level = 'RA') AND (nvl(l_app_rec.confirmed_flag, 'Y') = 'Y')) THEN -- Entity level receivable_application
1862
1863 --{LLCA CROSS CURRENCY
1864 IF p_from_llca_call = 'Y' THEN
1865 IF l_app_rec.acctd_amount_applied_from IS NOT NULL OR l_app_rec.amount_applied_from IS NOT NULL THEN
1866 ARP_DET_DIST_PKG.update_from_gt
1867 (p_from_amt => l_app_rec.amount_applied_from,
1868 p_from_acctd_amt => l_app_rec.acctd_amount_applied_from,
1869 p_ae_sys_rec => g_ae_sys_rec,
1870 p_app_rec => l_app_rec,
1871 p_gt_id => p_gt_id,
1872 p_inv_currency => l_cust_inv_rec.invoice_currency_code);
1873
1874 UPDATE ar_receivable_applications SET upgrade_method = 'R12'
1875 WHERE receivable_application_id = l_app_rec.receivable_application_id;
1876
1877 END IF;
1878 END IF;
1879 --}
1880 /*------------------------------------------------------+
1881 | Create AE Lines for Receivables, On-Account or |
1882 | Unidentified or Unapplied |
1883 +------------------------------------------------------*/
1884 Create_Ae_Lines_RA(p_app_rec => l_app_rec ,
1885 p_cr_rec => l_cr_rec ,
1886 p_cust_inv_rec => l_cust_inv_rec ,
1887 p_cust_cm_rec => l_cust_cm_rec ,
1888 p_ctlgd_cm_rec => l_ctlgd_cm_rec ,
1889 p_miscel_rec => l_miscel_rec ,
1890 p_rule_rec => l_rule_rec ,
1891 --{HYUDETUPT
1892 p_from_llca_call => p_from_llca_call,
1893 p_gt_id => p_gt_id
1894 --}
1895 );
1896
1897 ELSIF p_level = 'MCD' THEN -- Entity level = ar_misc_cash_distributions
1898 /*------------------------------------------------------+
1899 | Create AE Lines for Misc Cash Receipts, Payments |
1900 +------------------------------------------------------*/
1901
1902 Create_Ae_Lines_MCD(p_cr_rec => l_cr_rec ,
1903 p_vat_rec => l_vat_rec ,
1904 p_curr_rec => l_curr_rec );
1905
1906 END IF;
1907
1908 IF PG_DEBUG in ('Y', 'C') THEN
1909 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Create_Ae_Lines_Common()-');
1910 END IF;
1911
1912 EXCEPTION
1913 WHEN OTHERS THEN
1914 IF PG_DEBUG in ('Y', 'C') THEN
1915 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Create_Ae_Lines_Common');
1916 END IF;
1917 RAISE;
1918
1919 END Create_Ae_Lines_Common;
1920
1921 /* ================================================================================
1922 | PROCEDURE Create_Ae_Lines_RA
1923 |
1924 | DESCRIPTION
1925 | This procedure creates the AE lines for Unidentified, On-Account
1926 | and Receivables and also creates the pairing Unapplied Cash record
1927 |
1928 | Functions:
1929 | - Create AE lines for Receivable and Unapplied Cash.
1930 | - Determines Amounts Dr/Cr.
1931 |
1932 | PARAMETERS
1933 | p_app_rec Receivables Application Record(Transaction data)
1934 | p_cr_rec Cash Receipt Record
1935 | p_cust_inv_rec Invoice header Record
1936 | p_cust_cm_rec CM header Record
1937 | p_ctlgd_cm_rec CM distributions REC account ccid
1938 |
1939 | 02-Jul-2001 S.Nambiar Bug 1859937 In case of receipt chargeback
1940 | or credit card refund ,instead of
1941 | acctd_amount_aplied_from,
1942 | acctd_amount_applied_to
1943 | should be used to credit/debit.
1944 | 12-MAY-2003 J.Beckett Bug 2821139 As above for payment netting
1945 | i.e. where receivables_trx_id = -16
1946 * =================================================================================*/
1947 PROCEDURE Create_Ae_Lines_RA(
1948 p_app_rec IN ar_receivable_applications%ROWTYPE ,
1949 p_cr_rec IN ar_cash_receipts%ROWTYPE ,
1950 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
1951 p_cust_cm_rec IN ra_customer_trx%ROWTYPE ,
1952 p_ctlgd_cm_rec IN ra_cust_trx_line_gl_dist%ROWTYPE ,
1953 p_miscel_rec IN g_ae_miscel_rec_type ,
1954 p_rule_rec IN ae_rule_rec_type ,
1955 --{HYUDETUPT
1956 p_from_llca_call IN VARCHAR2,
1957 p_gt_id IN NUMBER
1958 --}
1959 ) IS
1960 l_app_id ar_receivable_applications.receivable_application_id%TYPE;
1961 l_ael_line_rec ae_line_rec_type;
1962 l_empty_ael_line_rec ae_line_rec_type;
1963 l_adj_rec ar_adjustments%ROWTYPE;
1964 l_ae_line_tbl ae_line_tbl_type;
1965 l_ae_ctr BINARY_INTEGER := 0;
1966 l_ctr BINARY_INTEGER;
1967 --{BUG#2927254
1968 l_app_rec ar_receivable_applications%ROWTYPE;
1969 l_cust_inv_cm_rec ra_customer_trx%ROWTYPE;
1970 l_i BINARY_INTEGER;
1971 l_j BINARY_INTEGER;
1972 l_amount_applied NUMBER;
1973 l_acctd_amount_applied NUMBER;
1974 l_rec_amt Number;
1975 l_call_alloc_tax Boolean := TRUE;
1976 --}
1977
1978 BEGIN
1979 IF PG_DEBUG in ('Y', 'C') THEN
1980 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Create_Ae_Lines_RA()+');
1981 END IF;
1982
1983
1984 /*-------------------------------------------------------------------+
1985 | Set counters to indicate dual creation of receivables for CM and |
1986 | CM and Invoices for CM applications - detail level of granularity |
1987 +-------------------------------------------------------------------*/
1988 IF p_app_rec.application_type = 'CM' THEN
1989 l_j := 2;
1990 ELSE
1991 l_j := 1;
1992 END IF;
1993
1994
1995 /*-------------------------------------------------------------+
1996 | Get exchange rate and third part info from Cash Receipt for |
1997 | building ACC, UNID, UNAPP |
1998 +-------------------------------------------------------------*/
1999 l_ael_line_rec.source_id := p_app_rec.receivable_application_id;
2000 l_ael_line_rec.source_table := 'RA';
2001 l_ael_line_rec.account := p_app_rec.code_combination_id; --ccid for UNAPP, UNID, ACC and APP
2002 l_ael_line_rec.currency_code := p_cr_rec.currency_code;
2003 l_ael_line_rec.currency_conversion_rate := p_cr_rec.exchange_rate;
2004 l_ael_line_rec.currency_conversion_type := p_cr_rec.exchange_rate_type;
2005 l_ael_line_rec.currency_conversion_date := p_cr_rec.exchange_date;
2006 l_ael_line_rec.third_party_id := p_cr_rec.pay_from_customer;
2007 l_ael_line_rec.third_party_sub_id := p_cr_rec.customer_site_use_id;
2008 l_ael_line_rec.ae_line_type_secondary := '';
2009
2010 /*-----------------------------------------------------------+
2011 | Accounting Entry Line Type UNID, ACC, REC (APP) for |
2012 | UNID records set the third_party_id and third_party_sub_id|
2013 | to null as receipt is unidentified. |
2014 +-----------------------------------------------------------*/
2015
2016 IF p_app_rec.status IN ('ACC', 'UNID', 'UNAPP','ACTIVITY','OTHER ACC') THEN
2017
2018 IF (p_app_rec.status = 'ACC') THEN
2019 l_ael_line_rec.ae_line_type := 'ACC';
2020
2021 ELSIF (p_app_rec.status = 'OTHER ACC') THEN
2022 l_ael_line_rec.ae_line_type := 'OTHER ACC';
2023
2024 ELSIF (p_app_rec.status = 'UNID') THEN
2025 l_ael_line_rec.ae_line_type := 'UNID';
2026 l_ael_line_rec.third_party_id := '';
2027 l_ael_line_rec.third_party_sub_id := '';
2028
2029 ELSIF (p_app_rec.status = 'ACTIVITY') THEN
2030
2031 IF p_app_rec.applied_payment_schedule_id = -2 THEN
2032 l_ael_line_rec.ae_line_type := 'SHORT_TERM_DEBT';
2033 ELSE
2034 l_ael_line_rec.ae_line_type := 'ACTIVITY';
2035 END IF;
2036
2037 ELSIF (p_app_rec.status = 'UNAPP') THEN
2038 l_ael_line_rec.ae_line_type := 'UNAPP';
2039
2040 IF g_ae_doc_rec.other_flag = 'PAIR' THEN
2041
2042 --A value of PAIR indicates that the UNAPP is paired with an APP, ACC, UNID
2043
2044 l_ael_line_rec.source_id_secondary := g_ae_doc_rec.source_id_old;
2045 l_ael_line_rec.source_table_secondary := 'RA';
2046
2047 END IF;
2048
2049 END IF;
2050
2051 /*----------------------------------------------------------------+
2052 |Bug 1859937 In case of receipt chargeback or credit card refund |
2053 |(application ref_id is populated in both these cases),instead of|
2054 |acctd_amount_aplied_from,acctd_amount_applied_to should be used |
2055 |to credit/debit. |
2056 +----------------------------------------------------------------*/
2057 IF ( sign( p_app_rec.amount_applied ) = -1 ) THEN --Debits
2058
2059 --BUG#5437275
2060 l_ael_line_rec.entered_dr := abs(p_app_rec.amount_applied);
2061 l_ael_line_rec.from_amount_dr := abs(p_app_rec.amount_applied_from);
2062
2063 l_ael_line_rec.entered_cr := NULL;
2064 l_ael_line_rec.accounted_cr := NULL;
2065 l_ael_line_rec.from_amount_cr := NULL;
2066 l_ael_line_rec.from_acctd_amount_cr := NULL;
2067
2068 IF ((p_app_rec.status = 'ACTIVITY') AND (p_app_rec.application_ref_id is not null OR p_app_rec.receivables_trx_id = -16)) THEN
2069 l_ael_line_rec.accounted_dr := abs(p_app_rec.acctd_amount_applied_to);
2070 l_ael_line_rec.from_acctd_amount_dr := abs(p_app_rec.acctd_amount_applied_from);
2071 ELSE
2072 l_ael_line_rec.accounted_dr := abs(p_app_rec.acctd_amount_applied_from);
2073 l_ael_line_rec.from_acctd_amount_dr := abs(p_app_rec.acctd_amount_applied_to);
2074 END IF;
2075
2076 ELSE
2077
2078 --BUG#5437275
2079 l_ael_line_rec.entered_cr := p_app_rec.amount_applied;
2080 l_ael_line_rec.from_amount_cr := abs(p_app_rec.amount_applied_from);
2081
2082 l_ael_line_rec.entered_dr := NULL;
2083 l_ael_line_rec.from_amount_dr := NULL;
2084 l_ael_line_rec.accounted_dr := NULL;
2085 l_ael_line_rec.from_acctd_amount_dr := NULL;
2086
2087 IF ((p_app_rec.status = 'ACTIVITY') AND (p_app_rec.application_ref_id is not null OR p_app_rec.receivables_trx_id = -16)) THEN
2088 l_ael_line_rec.accounted_cr := p_app_rec.acctd_amount_applied_to;
2089 l_ael_line_rec.from_acctd_amount_cr := abs(p_app_rec.acctd_amount_applied_from);
2090 ELSE
2091 l_ael_line_rec.accounted_cr := p_app_rec.acctd_amount_applied_from;
2092 l_ael_line_rec.from_acctd_amount_cr := abs(p_app_rec.acctd_amount_applied_to);
2093 END IF;
2094
2095 END IF;
2096
2097 -- Assign AEL for UNID, ACC for Receipts
2098 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
2099 END IF;
2100
2101 IF ((p_app_rec.status = 'APP') OR
2102 ((p_app_rec.status = 'ACTIVITY') AND
2103 (p_app_rec.application_ref_id is not null OR p_app_rec.receivables_trx_id = -16))) THEN
2104
2105 IF (p_app_rec.status = 'APP') THEN
2106
2107 /*--------------------------------------------------------------------------------+
2108 | Derive Receivable amounts, for Receipt applications, REC for Invoice document. |
2109 | On-Account Credit Memos combined with Receipts, REC for Credit Memo document. |
2110 +--------------------------------------------------------------------------------*/
2111 l_ael_line_rec.ae_line_type := 'REC';
2112
2113 /* J Rautiainen BR Implementation
2114 * Override the source type if it is passed */
2115 IF g_ae_doc_rec.override_source_type IS NOT NULL THEN
2116 l_ael_line_rec.ae_line_type := g_ae_doc_rec.override_source_type;
2117 END IF;
2118
2119 l_rec_amt := 0;
2120 IF p_app_rec.amount_applied <> 0 THEN
2121 SELECT SUM( abs(nvl(amount_line_items_original,0)) +
2122 abs(nvl(tax_original,0)) +
2123 abs(nvl(discount_original,0)) +
2124 abs(nvl(freight_original,0)) +
2125 abs(nvl(receivables_charges_charged,0)) )
2126 INTO
2127 l_rec_amt
2128 FROM ar_payment_schedules
2129 where customer_trx_id = p_app_rec.applied_customer_trx_id
2130 GROUP BY customer_trx_id;
2131 END IF;
2132
2133 /* Bug 2306701. Zero amount application of Credit memo is same as Positive
2134 application. */
2135 /* Bug fix 3247264. The sign of amount_applied + discounts should be considered
2136 for deciding on Debit or Credit */
2137 IF ( sign( p_app_rec.amount_applied +
2138 nvl(p_app_rec.earned_discount_taken,0) +
2139 nvl(p_app_rec.unearned_discount_taken,0) ) in (-1,0 )) THEN
2140
2141 --{EDISC or UNEDISC REC Distr will be created by this pack.
2142 --The REC on document or ON ACCT will move to Allocation package.
2143 IF (NVL(p_app_rec.earned_discount_taken,0) <> 0 OR
2144 NVL(p_app_rec.unearned_discount_taken,0) <> 0 ) AND l_rec_amt <> 0
2145 THEN
2146
2147 l_ael_line_rec.entered_dr := abs((nvl(p_app_rec.earned_discount_taken,0) +
2148 nvl(p_app_rec.unearned_discount_taken,0)));
2149
2150 --BUG#5437275
2151 l_ael_line_rec.from_amount_dr := abs(from_num_amt
2152 (p_to_curr => p_cust_inv_rec.invoice_currency_code,
2153 p_from_curr => p_cr_rec.currency_code,
2154 p_to_curr_rate => p_cust_inv_rec.exchange_rate, -- vavenugo bug6653443
2155 p_from_curr_rate => p_cr_rec.exchange_rate, -- vavenugo bug6653443
2156 p_to_den_amt => p_app_rec.AMOUNT_APPLIED,
2157 p_from_num_amt=> nvl(p_app_rec.AMOUNT_APPLIED_FROM,p_app_rec.AMOUNT_APPLIED), -- vavenugo bug6653443
2158 p_to_num_amt => abs((nvl(p_app_rec.earned_discount_taken,0) +
2159 nvl(p_app_rec.unearned_discount_taken,0)))));
2160
2161 l_ael_line_rec.entered_cr := NULL;
2162 l_ael_line_rec.from_amount_cr := NULL;
2163
2164
2165 l_ael_line_rec.accounted_dr := abs((nvl(p_app_rec.acctd_earned_discount_taken,0) +
2166 nvl(p_app_rec.acctd_unearned_discount_taken,0)));
2167
2168 l_ael_line_rec.from_acctd_amount_dr :=
2169 abs(from_num_amt(p_to_curr => p_cust_inv_rec.invoice_currency_code,
2170 p_from_curr => p_cr_rec.currency_code,
2171 p_to_curr_rate => p_cust_inv_rec.exchange_rate, -- vavenugo bug6653443
2172 p_from_curr_rate => p_cr_rec.exchange_rate, -- vavenugo bug6653443
2173 p_to_den_amt => p_app_rec.ACCTD_AMOUNT_APPLIED_TO,
2174 p_from_num_amt=> p_app_rec.ACCTD_AMOUNT_APPLIED_FROM,
2175 p_to_num_amt => abs((nvl(p_app_rec.acctd_earned_discount_taken,0) +
2176 nvl(p_app_rec.acctd_unearned_discount_taken,0)))));
2177 l_ael_line_rec.accounted_cr := NULL;
2178 l_ael_line_rec.from_acctd_amount_cr := NULL;
2179
2180
2181 ELSE
2182 --}
2183 -- For REC (APP) and discounts, accounting in currency of Invoice document
2184 l_ael_line_rec.entered_dr := abs((p_app_rec.amount_applied +
2185 nvl(p_app_rec.earned_discount_taken,0) +
2186 nvl(p_app_rec.unearned_discount_taken,0)));
2187 --BUG#5437275
2188 l_ael_line_rec.from_amount_dr :=
2189 abs(from_num_amt(p_to_curr => p_cust_inv_rec.invoice_currency_code,
2190 p_from_curr => p_cr_rec.currency_code,
2191 p_to_curr_rate => p_cust_inv_rec.exchange_rate, -- vavenugo bug6653443
2192 p_from_curr_rate => p_cr_rec.exchange_rate, -- vavenugo bug6653443
2193 p_to_den_amt => p_app_rec.AMOUNT_APPLIED,
2194 p_from_num_amt=> nvl(p_app_rec.AMOUNT_APPLIED_FROM,p_app_rec.AMOUNT_APPLIED), -- vavenugo bug6653443
2195 p_to_num_amt => abs((nvl(p_app_rec.amount_applied +
2196 p_app_rec.earned_discount_taken,0) +
2197 nvl(p_app_rec.unearned_discount_taken,0)))));
2198
2199 l_ael_line_rec.entered_cr := NULL;
2200 l_ael_line_rec.from_amount_cr := NULL;
2201
2202 l_ael_line_rec.accounted_dr := abs((p_app_rec.acctd_amount_applied_to +
2203 nvl(p_app_rec.acctd_earned_discount_taken,0) +
2204 nvl(p_app_rec.acctd_unearned_discount_taken,0)));
2205 l_ael_line_rec.from_acctd_amount_dr :=
2206 abs(from_num_amt(p_to_curr => p_cust_inv_rec.invoice_currency_code,
2207 p_from_curr => p_cr_rec.currency_code,
2208 p_to_curr_rate => p_cust_inv_rec.exchange_rate, -- vavenugo bug6653443
2209 p_from_curr_rate => p_cr_rec.exchange_rate, -- vavenugo bug6653443
2210 p_to_den_amt => p_app_rec.ACCTD_AMOUNT_APPLIED_TO,
2211 p_from_num_amt=> p_app_rec.ACCTD_AMOUNT_APPLIED_FROM,
2212 p_to_num_amt => abs((p_app_rec.acctd_amount_applied_to +
2213 nvl(p_app_rec.acctd_earned_discount_taken,0) +
2214 nvl(p_app_rec.acctd_unearned_discount_taken,0)))));
2215
2216 l_ael_line_rec.accounted_cr := NULL;
2217 l_ael_line_rec.from_acctd_amount_cr := NULL;
2218
2219 END IF;
2220
2221 ELSE -- Credit Receivables for INV, if amount_applied is 0 then accounting record created
2222
2223 --{EDISC or UNEDISC REC Distr will be created by this pack.
2224 --The REC on document or ON ACCT will move to Allocation package.
2225 IF (NVL(p_app_rec.earned_discount_taken,0) <> 0 OR
2226 NVL(p_app_rec.unearned_discount_taken,0) <> 0 ) AND l_rec_amt <> 0
2227 THEN
2228
2229 l_ael_line_rec.entered_cr := abs((nvl(p_app_rec.earned_discount_taken,0) +
2230 nvl(p_app_rec.unearned_discount_taken,0)));
2231
2232 --BUG#5437275
2233 l_ael_line_rec.from_amount_cr :=
2234 abs(from_num_amt(p_to_curr => p_cust_inv_rec.invoice_currency_code,
2235 p_from_curr => p_cr_rec.currency_code,
2236 p_to_curr_rate => p_cust_inv_rec.exchange_rate, -- vavenugo bug6653443
2237 p_from_curr_rate => p_cr_rec.exchange_rate, -- vavenugo bug6653443
2238 p_to_den_amt => p_app_rec.AMOUNT_APPLIED,
2239 p_from_num_amt=> nvl(p_app_rec.AMOUNT_APPLIED_FROM,p_app_rec.AMOUNT_APPLIED), -- vavenugo bug6653443
2240 p_to_num_amt => abs((nvl(p_app_rec.earned_discount_taken,0) +
2241 nvl(p_app_rec.unearned_discount_taken,0)))));
2242
2243 l_ael_line_rec.entered_dr := NULL;
2244 l_ael_line_rec.from_amount_dr := NULL;
2245
2246 l_ael_line_rec.accounted_cr := abs((nvl(p_app_rec.acctd_earned_discount_taken,0) +
2247 nvl(p_app_rec.acctd_unearned_discount_taken,0)));
2248
2249 l_ael_line_rec.from_acctd_amount_cr :=
2250 abs(from_num_amt(p_to_curr => p_cust_inv_rec.invoice_currency_code,
2251 p_from_curr => p_cr_rec.currency_code,
2252 p_to_curr_rate => p_cust_inv_rec.exchange_rate, -- vavenugo bug6653443
2253 p_from_curr_rate => p_cr_rec.exchange_rate, -- vavenugo bug6653443
2254 p_to_den_amt => p_app_rec.ACCTD_AMOUNT_APPLIED_TO,
2255 p_from_num_amt=> p_app_rec.ACCTD_AMOUNT_APPLIED_FROM,
2256 p_to_num_amt => abs((nvl(p_app_rec.acctd_earned_discount_taken,0) +
2257 nvl(p_app_rec.acctd_unearned_discount_taken,0)))));
2258
2259 l_ael_line_rec.accounted_dr := NULL;
2260 l_ael_line_rec.from_acctd_amount_dr := NULL;
2261
2262 ELSE
2263 --}
2264
2265 l_ael_line_rec.entered_cr := p_app_rec.amount_applied +
2266 nvl(p_app_rec.earned_discount_taken,0) +
2267 nvl(p_app_rec.unearned_discount_taken,0);
2268 l_ael_line_rec.from_amount_cr :=
2269 abs(from_num_amt(p_to_curr => p_cust_inv_rec.invoice_currency_code,
2270 p_from_curr => p_cr_rec.currency_code,
2271 p_to_curr_rate => p_cust_inv_rec.exchange_rate, -- vavenugo bug6653443
2272 p_from_curr_rate => p_cr_rec.exchange_rate, -- vavenugo bug6653443
2273 p_to_den_amt => p_app_rec.AMOUNT_APPLIED,
2274 p_from_num_amt=> nvl(p_app_rec.AMOUNT_APPLIED_FROM,p_app_rec.AMOUNT_APPLIED), -- vavenugo bug6653443
2275 p_to_num_amt => abs((nvl(p_app_rec.amount_applied +
2276 p_app_rec.earned_discount_taken,0) +
2277 nvl(p_app_rec.unearned_discount_taken,0)))));
2278
2279 l_ael_line_rec.entered_dr := NULL;
2280 l_ael_line_rec.from_amount_dr := NULL;
2281
2282 l_ael_line_rec.accounted_cr := p_app_rec.acctd_amount_applied_to +
2283 nvl(p_app_rec.acctd_earned_discount_taken,0) +
2284 nvl(p_app_rec.acctd_unearned_discount_taken,0);
2285
2286 l_ael_line_rec.from_acctd_amount_cr :=
2287 abs(from_num_amt(p_to_curr => p_cust_inv_rec.invoice_currency_code,
2288 p_from_curr => p_cr_rec.currency_code,
2289 p_to_curr_rate => p_cust_inv_rec.exchange_rate, -- vavenugo bug6653443
2290 p_from_curr_rate => p_cr_rec.exchange_rate, -- vavenugo bug6653443
2291 p_to_den_amt => p_app_rec.ACCTD_AMOUNT_APPLIED_TO,
2292 p_from_num_amt=> p_app_rec.ACCTD_AMOUNT_APPLIED_FROM,
2293 p_to_num_amt => abs((p_app_rec.acctd_amount_applied_to +
2294 nvl(p_app_rec.acctd_earned_discount_taken,0) +
2295 nvl(p_app_rec.acctd_unearned_discount_taken,0)))));
2296 l_ael_line_rec.accounted_dr := NULL;
2297 l_ael_line_rec.from_acctd_amount_dr := NULL;
2298
2299 END IF;
2300 END IF;
2301
2302 -- Override exchange rate information and use Invoice Rate for Receivables (APP)
2303 -- or Credit Memo for On Account CM with Receipts in this case p_cust_inv_rec
2304 -- holds the exchange rate info of the CM
2305
2306 l_ael_line_rec.currency_code := p_cust_inv_rec.invoice_currency_code;
2307 l_ael_line_rec.currency_conversion_rate := p_cust_inv_rec.exchange_rate;
2308 l_ael_line_rec.currency_conversion_type := p_cust_inv_rec.exchange_rate_type;
2309 l_ael_line_rec.currency_conversion_date := p_cust_inv_rec.exchange_date;
2310
2311 IF p_cust_inv_rec.drawee_site_use_id IS NOT NULL THEN
2312 l_ael_line_rec.third_party_id := p_cust_inv_rec.drawee_id;
2313 l_ael_line_rec.third_party_sub_id := p_cust_inv_rec.drawee_site_use_id;
2314 ELSE
2315 l_ael_line_rec.third_party_id := p_cust_inv_rec.bill_to_customer_id;
2316 l_ael_line_rec.third_party_sub_id := p_cust_inv_rec.bill_to_site_use_id;
2317 END IF;
2318
2319 -- Assign AEL for REC(APP) for Invoice document or On-Account CM's combined with Receipts
2320 -- going forward blls receivable will also be created by alloc routione for REC
2321 -- issues with rate, third party info
2322 /*bug 6151622*/
2323 /* Bug 6803266: Reverted changes of bug 6151622 and added condition to chk defered_tax as N*/
2324 IF ( p_cust_inv_rec.drawee_site_use_id IS NOT NULL
2325 AND nvl(g_ae_doc_rec.deferred_tax,'Y') = 'N' )
2326 OR ((nvl(p_app_rec.earned_discount_taken,0) <> 0)
2327 OR (nvl(p_app_rec.unearned_discount_taken,0) <> 0)
2328 OR l_rec_amt = 0
2329 OR (p_app_rec.amount_applied = 0)) THEN /* Added check from 0 amount applied */
2330 /*Bug fix 6721786 */
2331 IF l_rec_amt = 0 THEN
2332 l_call_alloc_tax := FALSE;
2333 END IF;
2334 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
2335 END IF;
2336
2337 END IF;
2338 /*--------------------------------------------------------------------------------+
2339 | Derive Round, Gain or Loss accounting for Receipts and Credit Memo applications|
2340 | On-Account Credit Memos combined with Receipts. |
2341 +--------------------------------------------------------------------------------*/
2342 IF sign(p_app_rec.acctd_amount_applied_from - p_app_rec.acctd_amount_applied_to) = -1 THEN
2343
2344 IF (p_miscel_rec.fixed_rate = 'Y') THEN
2345 l_ael_line_rec.ae_line_type := 'CURR_ROUND'; --Currency Round account
2346 ELSE
2347 l_ael_line_rec.ae_line_type := 'EXCH_LOSS'; --Exchange Loss
2348 END IF;
2349
2350 --Null out NOCOPY exchange rate and currency information
2351 l_ael_line_rec.currency_code := NULL;
2352 l_ael_line_rec.currency_conversion_rate := NULL;
2353 l_ael_line_rec.currency_conversion_type := NULL;
2354 l_ael_line_rec.currency_conversion_date := NULL;
2355 l_ael_line_rec.third_party_id := NULL;
2356 l_ael_line_rec.third_party_sub_id := NULL;
2357
2358 l_ael_line_rec.account := p_miscel_rec.gain_loss_ccid;
2359
2360 l_ael_line_rec.accounted_dr := abs(p_app_rec.acctd_amount_applied_from
2361 - p_app_rec.acctd_amount_applied_to);
2362 l_ael_line_rec.entered_dr := 0 ;
2363
2364 l_ael_line_rec.from_acctd_amount_dr := abs(p_app_rec.acctd_amount_applied_from
2365 - p_app_rec.acctd_amount_applied_to);
2366 l_ael_line_rec.from_amount_dr := 0 ;
2367
2368
2369 l_ael_line_rec.entered_cr := NULL;
2370 l_ael_line_rec.accounted_cr := NULL;
2371 l_ael_line_rec.from_acctd_amount_cr := NULL;
2372 l_ael_line_rec.from_amount_cr := NULL ;
2373
2374 -- Assign AEL for Exchange Loss
2375 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
2376
2377 ELSIF sign(p_app_rec.acctd_amount_applied_from - p_app_rec.acctd_amount_applied_to) = 1 THEN
2378
2379 IF (p_miscel_rec.fixed_rate = 'Y') THEN
2380 l_ael_line_rec.ae_line_type := 'CURR_ROUND'; --Currency Round account
2381 ELSE
2382 l_ael_line_rec.ae_line_type := 'EXCH_GAIN'; --Exchange Gain
2383 END IF;
2384
2385 --Null out NOCOPY exchange rate and currency information
2386
2387 l_ael_line_rec.currency_code := NULL;
2388 l_ael_line_rec.currency_conversion_rate := NULL;
2389 l_ael_line_rec.currency_conversion_type := NULL;
2390 l_ael_line_rec.currency_conversion_date := NULL;
2391 l_ael_line_rec.third_party_id := NULL;
2392 l_ael_line_rec.third_party_sub_id := NULL;
2393
2394 --Exchange rate currency and other details are from the Invoice or Credit Memo document
2395 l_ael_line_rec.account := p_miscel_rec.gain_loss_ccid;
2396
2397 l_ael_line_rec.accounted_cr := p_app_rec.acctd_amount_applied_from
2398 - p_app_rec.acctd_amount_applied_to ;
2399 l_ael_line_rec.entered_cr := 0 ;
2400
2401
2402 l_ael_line_rec.from_acctd_amount_cr := p_app_rec.acctd_amount_applied_from
2403 - p_app_rec.acctd_amount_applied_to ;
2404 l_ael_line_rec.from_amount_cr:= 0;
2405
2406
2407 l_ael_line_rec.entered_dr := NULL;
2408 l_ael_line_rec.accounted_dr := NULL;
2409 l_ael_line_rec.from_acctd_amount_dr := NULL;
2410 l_ael_line_rec.from_amount_dr := NULL ;
2411
2412
2413 -- Assign AEL for Exchange Gain
2414 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
2415
2416 END IF;
2417
2418 -- Call tax accounting routine for Receipt payments, earned and unearned discounts
2419 -- for Receipts, if amount_applied, earned discounts or unearned discounts is non zero
2420
2421 -- IF ((p_app_rec.application_type <> 'CM')
2422 IF (((nvl(p_app_rec.earned_discount_taken,0) <> 0) OR (nvl(p_app_rec.unearned_discount_taken,0) <> 0)
2423 OR (p_app_rec.amount_applied <> 0)) AND (p_app_rec.status ='APP')) THEN
2424
2425 l_ae_line_tbl := g_empty_ae_line_tbl;
2426 l_ae_ctr := 0;
2427
2428 /*---------------------------------------------------------------------------+
2429 | Verify whether invalid rule setup has occurred at the Receivable Activity |
2430 | in this case raise an error stating that the rule be set up correctly. |
2431 +---------------------------------------------------------------------------*/
2432 IF (((((p_rule_rec.gl_account_source1 = 'NO_SOURCE') OR (p_rule_rec.tax_code_source1 = 'NO_SOURCE'))
2433 OR ((p_rule_rec.tax_code_source1 = 'INVOICE') AND (nvl(p_rule_rec.tax_recoverable_flag1, 'X') NOT IN ('Y','N'))))
2434 AND (nvl(p_app_rec.earned_discount_taken,0) <> 0))
2435 OR ((((p_rule_rec.gl_account_source2 = 'NO_SOURCE') OR (p_rule_rec.tax_code_source2 = 'NO_SOURCE'))
2436 OR ((p_rule_rec.tax_code_source2 = 'INVOICE') AND (nvl(p_rule_rec.tax_recoverable_flag2, 'X') NOT IN ('Y','N'))))
2437 AND (nvl(p_app_rec.unearned_discount_taken,0) <> 0))) THEN
2438
2439 RAISE ARP_ALLOCATION_PKG.invalid_allocation_base;
2440
2441 END IF;
2442
2443 /*---------------------------------------------------------------------------+
2444 | The deferred tax flag is set by the Bills Receivable Houskeeper. When this|
2445 | is No, it means that there is no Tax accounting impact, as the maturity |
2446 | date event would have moved the deferred tax. For Transactions and where |
2447 | the Maturity date event merges with the creation of Receipt application, |
2448 | the Tax accounting Wrapper routine is called. For Transactions the normal |
2449 | Tax accounting routine is called. Note for Bills Receivable we only move |
2450 | deferred tax as there is no discount. |
2451 +---------------------------------------------------------------------------*/
2452 IF (nvl(g_ae_doc_rec.deferred_tax,'Y') = 'Y') THEN
2453
2454 FOR l_i in 1..l_j LOOP
2455
2456 IF l_i = 1 THEN
2457
2458 l_cust_inv_cm_rec := p_cust_inv_rec;
2459 l_app_rec := p_app_rec;
2460 g_ae_doc_rec.inv_cm_app_mode := 'I';
2461
2462 ELSIF l_i = 2 THEN
2463
2464 --{HYUDETUPT
2465
2466 -- move to detail distribution
2467 ------------------------------
2468 -- at this level we pass the ra_record as it is and add a flag to allocation to
2469 -- indicate the document allocating
2470 --
2471 l_app_rec := p_app_rec;
2472 l_app_rec.applied_customer_trx_id := p_app_rec.customer_trx_id;
2473 l_app_rec.customer_trx_id := p_app_rec.customer_trx_id;
2474 l_app_rec.receivable_application_id := p_app_rec.receivable_application_id;
2475 --{switch of accounted amount for CM G/L
2476 l_app_rec.acctd_amount_applied_from := p_app_rec.acctd_amount_applied_to;
2477 l_app_rec.acctd_amount_applied_to := p_app_rec.acctd_amount_applied_from;
2478 --}
2479 g_ae_doc_rec.inv_cm_app_mode := 'C';
2480 l_cust_inv_cm_rec := p_cust_cm_rec;
2481
2482 END IF;
2483
2484 -- Bug 6598080 - Modified if condition to restrict duplicate record entry for BR to CM application.
2485 IF p_cust_inv_rec.drawee_site_use_id IS NULL
2486 OR ((l_i = 2) AND (p_cust_inv_rec.drawee_site_use_id IS NOT NULL) AND (p_app_rec.application_type = 'CM')) THEN --application to Transaction
2487
2488 IF l_call_alloc_tax = TRUE THEN
2489 ARP_ALLOCATION_PKG.Allocate_Tax(
2490 p_ae_doc_rec => g_ae_doc_rec , --Document detail
2491 p_ae_event_rec => g_ae_event_rec , --Event record
2492 p_ae_rule_rec => p_rule_rec , --Rule info for payment method
2493 p_app_rec => l_app_rec , --Application details
2494 p_cust_inv_rec => l_cust_inv_cm_rec, --p_cust_inv_rec , --Invoice, details
2495 p_adj_rec => l_adj_rec , --dummy adjustment record
2496 p_ae_ctr => l_ae_ctr , --counter
2497 p_ae_line_tbl => l_ae_line_tbl , --final tax accounting table
2498 p_br_cust_trx_line_id => NULL,
2499 p_simul_app => NULL ,
2500 --{HYUDETUPT
2501 p_from_llca_call => p_from_llca_call,
2502 p_gt_id => p_gt_id,
2503 -- Need to move the ra record conversion from inv to cm at detail distribution
2504 p_inv_cm => g_ae_doc_rec.inv_cm_app_mode
2505 );
2506 END IF;
2507
2508 ELSE --application to Bills Receivable
2509
2510 ARP_BR_ALLOC_WRAPPER_PKG.Allocate_Tax_BR_Main(
2511 p_mode => g_mode , --Mode
2512 p_ae_doc_rec => g_ae_doc_rec , --Document detail
2513 p_ae_event_rec => g_ae_event_rec , --Event record
2514 p_ae_rule_rec => p_rule_rec , --Rule info for payment method
2515 p_app_rec => p_app_rec , --Application details
2516 p_cust_inv_rec => p_cust_inv_rec , --Invoice details
2517 p_adj_rec => l_adj_rec , --dummy adjustment record
2518 p_ae_sys_rec => g_ae_sys_rec , --system parameters
2519 p_ae_ctr => l_ae_ctr , --counter
2520 p_ae_line_tbl => l_ae_line_tbl); --final tax accounting table
2521 -- There is no requirement from converting ra record from inv to cm for BR
2522 -- as we can not apply CM to BR
2523
2524
2525 END IF; --application to Bill or Transaction
2526
2527 END LOOP; --dual call to allocation routine
2528
2529 ELSE
2530 /*-----------------------------------------------------------------------+
2531 | do not call the Tax accounting to move deferred tax this happens when |
2532 | the Bills Receivable Housekeeper determines that the maturity date |
2533 | event is seperate from the creation of the application, for standard |
2534 | Remittance and Factored (with Recourse) we need to update the link id |
2535 | so the last Transaction History Record must be Standard Remitted or |
2536 | pending risk elimination |
2537 +-----------------------------------------------------------------------*/
2538 update ar_receivable_applications
2539 set link_to_trx_hist_id = (select max(th.transaction_history_id)
2540 from ar_transaction_history th
2541 where th.customer_trx_id = p_app_rec.applied_customer_trx_id
2542 and th.event = 'MATURITY_DATE'
2543 and exists (select 'x'
2544 from ar_distributions ard
2545 where ard.source_id = th.transaction_history_id
2546 and ard.source_table = 'TH'))
2547 where receivable_application_id = p_app_rec.receivable_application_id;
2548
2549 END IF; --End if Tax accounting or deferred tax required
2550
2551 IF l_ae_line_tbl.EXISTS(l_ae_ctr) THEN --Atleast one Tax line exists
2552
2553 FOR l_ctr IN l_ae_line_tbl.FIRST .. l_ae_line_tbl.LAST LOOP
2554
2555 --It is necessary to populate the record and then call assign elements
2556 --because of standards and that the User Hook could override accounting
2557 --so need to populate this record (rather than direct table assignments)
2558
2559 l_ael_line_rec := l_empty_ael_line_rec;
2560 l_ael_line_rec := l_ae_line_tbl(l_ctr);
2561
2562 --Asign AEL for REC for Credit Memo document
2563 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
2564
2565 END LOOP; --lines table
2566
2567 END IF; --line table records exist
2568
2569 END IF; --deferred tax is to be moved
2570
2571 END IF; --receipt aplication and discounts or payment is non zero move deferred tax
2572
2573 --Receivable account record for CM Bills Receivable only
2574 -- Bug 6598080 - Modified if condition
2575 IF p_app_rec.application_type = 'CM'
2576 AND ((p_app_rec.amount_applied = 0)) then
2577
2578 l_ael_line_rec.ae_line_type := 'REC';
2579 l_ael_line_rec.ae_line_type_secondary := '';
2580
2581 /* Bug 2306701. Zero amount application of Credit memo is same as Positive
2582 application. */
2583
2584 IF ( sign( p_app_rec.amount_applied ) in (-1,0)) THEN -- Credit Receivables for CM
2585
2586 -- For REC (APP) and discounts, accounting in currency of Invoice document
2587
2588 l_ael_line_rec.entered_cr := abs(p_app_rec.amount_applied);
2589
2590 l_ael_line_rec.accounted_cr := abs(p_app_rec.acctd_amount_applied_from);
2591
2592 l_ael_line_rec.entered_dr := NULL;
2593 l_ael_line_rec.accounted_dr := NULL;
2594
2595 ELSE
2596
2597 l_ael_line_rec.entered_dr := p_app_rec.amount_applied;
2598
2599 l_ael_line_rec.accounted_dr := p_app_rec.acctd_amount_applied_from;
2600
2601 l_ael_line_rec.entered_cr := NULL;
2602 l_ael_line_rec.accounted_cr := NULL;
2603
2604 END IF;
2605
2606 -- Override exchange rate information and use Credit Memo Rate
2607 l_ael_line_rec.account := p_ctlgd_cm_rec.code_combination_id;
2608 l_ael_line_rec.currency_code := p_cust_cm_rec.invoice_currency_code;
2609 l_ael_line_rec.currency_conversion_rate := p_cust_cm_rec.exchange_rate;
2610 l_ael_line_rec.currency_conversion_type := p_cust_cm_rec.exchange_rate_type;
2611 l_ael_line_rec.currency_conversion_date := p_cust_cm_rec.exchange_date;
2612 l_ael_line_rec.third_party_id := p_cust_cm_rec.bill_to_customer_id;
2613 l_ael_line_rec.third_party_sub_id := p_cust_cm_rec.bill_to_site_use_id;
2614
2615 --Asign AEL for REC for Credit Memo document
2616 IF p_cust_inv_rec.drawee_site_use_id IS NOT NULL OR sign(p_app_rec.amount_applied) = 0 THEN
2617 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
2618 END IF;
2619
2620 END IF;
2621
2622
2623 --Receivable account record for CM Activity only
2624 IF p_app_rec.application_type = 'CM'
2625 AND p_cust_inv_rec.customer_trx_id IS NULL
2626 AND p_app_rec.amount_applied <> 0
2627 AND p_app_rec.status = 'ACTIVITY'
2628 THEN
2629
2630 l_app_rec := p_app_rec;
2631 l_app_rec.applied_customer_trx_id := p_app_rec.customer_trx_id;
2632 l_app_rec.customer_trx_id := p_app_rec.customer_trx_id;
2633 l_app_rec.receivable_application_id := p_app_rec.receivable_application_id;
2634 l_app_rec.acctd_amount_applied_from := p_app_rec.acctd_amount_applied_to;
2635 l_app_rec.acctd_amount_applied_to := p_app_rec.acctd_amount_applied_from;
2636 g_ae_doc_rec.inv_cm_app_mode := 'C';
2637 l_cust_inv_cm_rec := p_cust_cm_rec;
2638
2639 ARP_ALLOCATION_PKG.Allocate_Tax(
2640 p_ae_doc_rec => g_ae_doc_rec , --Document detail
2641 p_ae_event_rec => g_ae_event_rec , --Event record
2642 p_ae_rule_rec => p_rule_rec , --Rule info for payment method
2643 p_app_rec => l_app_rec , --Application details
2644 p_cust_inv_rec => l_cust_inv_cm_rec, --p_cust_inv_rec , --Invoice, details
2645 p_adj_rec => l_adj_rec , --dummy adjustment record
2646 p_ae_ctr => l_ae_ctr , --counter
2647 p_ae_line_tbl => l_ae_line_tbl , --final tax accounting table
2648 p_br_cust_trx_line_id => NULL,
2649 p_simul_app => NULL ,
2650 p_from_llca_call => p_from_llca_call,
2651 p_gt_id => p_gt_id,
2652 p_inv_cm => g_ae_doc_rec.inv_cm_app_mode
2653 );
2654
2655
2656 IF l_ae_line_tbl.EXISTS(l_ae_ctr) THEN --Atleast one Tax line exists
2657
2658 FOR l_ctr IN l_ae_line_tbl.FIRST .. l_ae_line_tbl.LAST LOOP
2659
2660 --It is necessary to populate the record and then call assign elements
2661 --because of standards and that the User Hook could override accounting
2662 --so need to populate this record (rather than direct table assignments)
2663
2664 l_ael_line_rec := l_empty_ael_line_rec;
2665 l_ael_line_rec := l_ae_line_tbl(l_ctr);
2666
2667 --Asign AEL for REC for Credit Memo document
2668 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
2669
2670 END LOOP; --lines table
2671
2672 END IF; --line table records exist
2673
2674 END IF;
2675
2676 /*-----------------------------------------------------------------------------------+
2677 |Call the Reconciliation routine, this is necessary because the transaction or Bill |
2678 |which may have been overapplied is now closed due to reversal, or else the reversal|
2679 |may have resulted in re-opening the transaction, hence we need to back out NOCOPY the old |
2680 |reconciliation entries for the Bill or the Transactions |
2681 +-----------------------------------------------------------------------------------*/
2682 IF (p_app_rec.status = 'APP') THEN
2683
2684 ARP_RECONCILE.Reconcile_trx_br(
2685 p_mode => g_mode ,
2686 p_ae_doc_rec => g_ae_doc_rec ,
2687 p_ae_event_rec => g_ae_event_rec ,
2688 p_cust_inv_rec => p_cust_inv_rec ,
2689 p_activity_cust_trx_id => p_app_rec.applied_customer_trx_id ,
2690 p_activity_amt => p_app_rec.amount_applied * -1 ,
2691 p_activity_acctd_amt => p_app_rec.acctd_amount_applied_to * -1,
2692 p_call_num => 1 ,
2693 p_g_ae_line_tbl => g_ae_line_tbl ,
2694 p_g_ae_ctr => g_ae_line_ctr );
2695
2696 IF p_app_rec.application_type = 'CM' THEN
2697
2698 ARP_RECONCILE.Reconcile_trx_br(
2699 p_mode => g_mode ,
2700 p_ae_doc_rec => g_ae_doc_rec ,
2701 p_ae_event_rec => g_ae_event_rec ,
2702 p_cust_inv_rec => p_cust_cm_rec ,
2703 p_activity_cust_trx_id => p_app_rec.customer_trx_id ,
2704 p_activity_amt => p_app_rec.amount_applied ,
2705 p_activity_acctd_amt => p_app_rec.acctd_amount_applied_from ,
2706 p_call_num => 2 ,
2707 p_g_ae_line_tbl => g_ae_line_tbl ,
2708 p_g_ae_ctr => g_ae_line_ctr );
2709
2710 END IF; --application type is Credit Memo
2711
2712 END IF; -- reconciliation routine called for applications only
2713
2714 IF PG_DEBUG in ('Y', 'C') THEN
2715 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Create_Ae_Lines_RA()-');
2716 END IF;
2717
2718 EXCEPTION
2719 WHEN ARP_ALLOCATION_PKG.invalid_allocation_base THEN
2720 IF PG_DEBUG in ('Y', 'C') THEN
2721 arp_standard.debug('ARP_RECEIPTS_MAIN.Create_Ae_Lines_RA - invalid_rule_error');
2722 END IF;
2723 fnd_message.set_name('AR','AR_INVALID_ACTIVITY');
2724 RAISE;
2725
2726 WHEN NO_DATA_FOUND THEN
2727 IF PG_DEBUG in ('Y', 'C') THEN
2728 arp_standard.debug('ARP_RECEIPTS_MAIN.Create_Ae_Lines_RA - NO_DATA_FOUND' );
2729 END IF;
2730 RAISE;
2731
2732 WHEN OTHERS THEN
2733 IF PG_DEBUG in ('Y', 'C') THEN
2734 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Create_Ae_Lines_RA');
2735 END IF;
2736 RAISE;
2737
2738 END Create_Ae_Lines_RA;
2739
2740 /* =======================================================================
2741 | PROCEDURE Create_Ae_Lines_MCD
2742 |
2743 | DESCRIPTION
2744 | This procedure creates the AE lines for Unidentified, On-Account
2745 | and Receivables and also creates the pairing Unapplied Cash record
2746 | Will be used when Creating Receipts and Reversing Receipts
2747 |
2748 | Functions:
2749 | - Create AE lines for Receivable and Unapplied Cash.
2750 | - Determines Amounts Dr/Cr.
2751 |
2752 | PARAMETERS
2753 | p_cr_rec Cash Receipt Record
2754 | p_vat_rec VAT tax Record
2755 | p_curr_rec Currency Record
2756 * ======================================================================*/
2757 PROCEDURE Create_Ae_Lines_MCD(
2758 p_cr_rec IN ar_cash_receipts%ROWTYPE ,
2759 p_vat_rec IN ar_vat_tax%ROWTYPE ,
2760 p_curr_rec IN ae_curr_rec_type ) IS
2761
2762 -- MRC TRigger REPLACEMENT: modified cursor to extract acctd_amount
2763 -- based on function.
2764
2765 CURSOR get_mcd_rec IS
2766 SELECT mcd.misc_cash_distribution_id misc_cash_distribution_id ,
2767 mcd.code_combination_id code_combination_id ,
2768 mcd.percent percent ,
2769 mcd.amount amount ,
2770 mcd.acctd_amount acctd_amount
2771 FROM ar_misc_cash_distributions mcd
2772 WHERE mcd.cash_receipt_id = g_ae_doc_rec.document_id
2773 AND g_ae_sys_rec.sob_type = 'P'
2774 AND mcd.reversal_gl_date is null --so we create only new rate adjusted or new mcd records
2775 AND mcd.posting_control_id = -3
2776 AND not exists (select 'x'
2777 from ar_distributions ard
2778 where ard.source_id = mcd.misc_cash_distribution_id
2779 and ard.source_table = 'MCD')
2780 ORDER by misc_cash_distribution_id;
2781 --{BUG4301323
2782 /* UNION
2783 SELECT mcd.misc_cash_distribution_id misc_cash_distribution_id,
2784 mcd.code_combination_id code_combination_id,
2785 mcd.percent percent,
2786 mcd.amount amount,
2787 mcd_mrc.acctd_amount acctd_amount
2788 FROM ar_misc_cash_distributions mcd,
2789 ar_mc_misc_cash_dists mcd_mrc
2790 WHERE mcd.cash_receipt_id = g_ae_doc_rec.document_id
2791 AND mcd.misc_cash_distribution_id = mcd_mrc.misc_cash_distribution_id
2792 AND g_ae_sys_rec.sob_type = 'R'
2793 AND mcd_mrc.set_of_books_id = g_ae_sys_rec.set_of_books_id
2794 AND mcd.reversal_gl_date is null --so we create only new rate adjusted or new mcd records.
2795 AND mcd_mrc.posting_control_id = -3
2796 AND not exists (select 'x'
2797 from ar_mc_distributions_all ard
2798 where ard.source_id = mcd.misc_cash_distribution_id
2799 and ard.source_table = 'MCD'
2800 and ard.set_of_books_id = g_ae_sys_rec.set_of_books_id)
2801 */
2802 l_ael_line_rec ae_line_rec_type;
2803 l_mcd_first_rec_id ar_misc_cash_distributions.misc_cash_distribution_id%TYPE ;
2804
2805 l_mcd_actual_acctd_amt NUMBER := 0;
2806 l_mcd_amount NUMBER := 0;
2807 l_mcd_acctd_amount NUMBER := 0;
2808 l_mcd_run_amt_tot NUMBER := 0;
2809 l_mcd_run_acctd_amt_tot NUMBER := 0;
2810 l_mcd_run_pro_amt_tot NUMBER := 0;
2811 l_mcd_run_pro_acctd_amt_tot NUMBER := 0;
2812 l_mcd_tax_amt NUMBER := 0;
2813 l_mcd_tax_acctd_amt NUMBER := 0;
2814 l_mcd_taxable_amt NUMBER := 0;
2815 l_mcd_taxable_acctd_amt NUMBER := 0;
2816 l_mcd_exists BOOLEAN := FALSE;
2817 l_dummy VARCHAR2(1);
2818 /* Added for bug 2494858 */
2819 l_precision NUMBER := 0;
2820 l_extended_precision NUMBER := 0;
2821 l_rounding_rule VARCHAR2(30);
2822 l_min_acct_unit NUMBER := 0;
2823 /* end of bug 2494858 */
2824 BEGIN
2825 IF PG_DEBUG in ('Y', 'C') THEN
2826 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Create_Ae_Lines_MCD()+');
2827 END IF;
2828
2829 /* Added the next two statements for bug 2494858 */
2830 l_rounding_rule := arp_standard.sysparm.tax_rounding_rule;
2831 fnd_currency.get_info(p_cr_rec.currency_code,l_precision,l_extended_precision,l_min_acct_unit);
2832
2833
2834 /*-------------------------------------------------------------+
2835 | Get exchange rate and third part info from Cash Receipt for |
2836 | building MISCCASH records in AR_DISTRIBUTIONS |
2837 +-------------------------------------------------------------*/
2838 l_mcd_first_rec_id := '';
2839
2840 l_ael_line_rec.source_table := 'MCD';
2841 l_ael_line_rec.source_id_secondary := p_cr_rec.cash_receipt_id;
2842 l_ael_line_rec.source_table_secondary := 'CR';
2843 l_ael_line_rec.currency_code := p_cr_rec.currency_code;
2844 l_ael_line_rec.currency_conversion_rate := p_cr_rec.exchange_rate;
2845 l_ael_line_rec.currency_conversion_type := p_cr_rec.exchange_rate_type;
2846 l_ael_line_rec.currency_conversion_date := p_cr_rec.exchange_date;
2847 l_ael_line_rec.third_party_id := p_cr_rec.pay_from_customer;
2848 l_ael_line_rec.third_party_sub_id := p_cr_rec.customer_site_use_id;
2849
2850 FOR l_mcd_rec in get_mcd_rec LOOP
2851
2852 IF l_mcd_first_rec_id IS NULL THEN
2853 l_mcd_first_rec_id := l_mcd_rec.misc_cash_distribution_id;
2854 l_mcd_exists := TRUE;
2855 END IF;
2856
2857 l_mcd_actual_acctd_amt := l_mcd_actual_acctd_amt + l_mcd_rec.acctd_amount;
2858
2859 l_ael_line_rec.source_id := l_mcd_rec.misc_cash_distribution_id;
2860 l_ael_line_rec.ae_line_type := 'MISCCASH';
2861 l_ael_line_rec.account := l_mcd_rec.code_combination_id;
2862
2863
2864 IF (nvl(p_cr_rec.tax_rate,0) <> 0) THEN
2865
2866 /*------------------------------------------------------------------------------+
2867 | Maintain running total amounts for MCD amounts and MCD accounted amounts |
2868 +------------------------------------------------------------------------------*/
2869 l_mcd_run_amt_tot := l_mcd_run_amt_tot + l_mcd_rec.amount;
2870 l_mcd_run_acctd_amt_tot := l_mcd_run_acctd_amt_tot + l_mcd_rec.acctd_amount;
2871
2872 /*------------------------------------------------------------------------------+
2873 | Determine actual MCD amounts and accounted amounts using the following. |
2874 | Line 1 100, Line 2 200, Line 3 300 (Total MCD amount = 600) Tax 2% |
2875 | |
2876 | 1 - 2/(100 + 2)= .98039216 |
2877 | |
2878 | Line 1 a -> .98039216 * 100 = 98.04 (allocated) |
2879 | |
2880 | Line 2 -> (100 + 200) * .98 = 294.12 |
2881 | b -> 294.12 - a = 196.08 (allocated) |
2882 | |
2883 | Line 3 -> (100 + 200 + 300) * .98 = 588.24 |
2884 | c -> 588.24 - a - b = 294.12 |
2885 +------------------------------------------------------------------------------*/
2886
2887 l_mcd_amount := arpcurr.CurrRound(l_mcd_run_amt_tot * (1 - (p_cr_rec.tax_rate/(100 + p_cr_rec.tax_rate))),
2888 p_cr_rec.currency_code) - l_mcd_run_pro_amt_tot;
2889
2890 /*------------------------------------------------------------------------------+
2891 | Running total for prorated mcd amount in currency of Invoice |
2892 +------------------------------------------------------------------------------*/
2893 l_mcd_run_pro_amt_tot := l_mcd_run_pro_amt_tot + l_mcd_amount;
2894
2895 /*------------------------------------------------------------------------------+
2896 | Calculate MCD accounted amount for MCD line |
2897 +------------------------------------------------------------------------------*/
2898 l_mcd_acctd_amount := arpcurr.CurrRound(l_mcd_run_acctd_amt_tot * (1 - (p_cr_rec.tax_rate/(100 + p_cr_rec.tax_rate))),
2899 g_ae_sys_rec.base_currency) - l_mcd_run_pro_acctd_amt_tot;
2900
2901 /*------------------------------------------------------------------------------+
2902 | Running total for prorated MCD accounted amount in currency of Invoice |
2903 +------------------------------------------------------------------------------*/
2904 l_mcd_run_pro_acctd_amt_tot := l_mcd_run_pro_acctd_amt_tot + l_mcd_acctd_amount;
2905
2906
2907 ELSE
2908
2909 l_mcd_amount := l_mcd_rec.amount;
2910 l_mcd_run_pro_amt_tot := l_mcd_run_pro_amt_tot + l_mcd_amount;
2911
2912 l_mcd_acctd_amount := l_mcd_rec.acctd_amount;
2913 l_mcd_run_pro_acctd_amt_tot := l_mcd_run_pro_acctd_amt_tot + l_mcd_acctd_amount;
2914
2915 END IF;
2916 /* Bug 2233284
2917 tax_link_id need to be assigned even if the tax_rate is zero */
2918 IF p_cr_rec.tax_rate is NOT NULL THEN
2919 l_ael_line_rec.tax_link_id := 1;
2920 ELSE
2921 l_ael_line_rec.tax_link_id := NULL;
2922 END IF;
2923
2924 IF sign(l_mcd_amount) = -1 THEN -- Debits for Misc Cash Payments
2925
2926 l_ael_line_rec.entered_dr := abs(l_mcd_amount);
2927 l_ael_line_rec.accounted_dr := abs(l_mcd_acctd_amount);
2928 l_ael_line_rec.entered_cr := NULL;
2929 l_ael_line_rec.accounted_cr := NULL;
2930
2931 ELSE -- Credits for Misc Cash Receipts includes 0 dollar amounts
2932
2933 l_ael_line_rec.entered_cr := l_mcd_amount;
2934 l_ael_line_rec.accounted_cr := l_mcd_acctd_amount;
2935 l_ael_line_rec.entered_dr := NULL;
2936 l_ael_line_rec.accounted_dr := NULL;
2937
2938 END IF;
2939
2940 -- Assign AEL for Misc Cash Distributions
2941 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
2942
2943 END LOOP;
2944
2945 -- Create Tax line if required for Misc Cash Receipt or Misc Cash Payment
2946 /* Bug 2233284
2947 The Tax accounting line should be created for 0% tax rate also.
2948 IF ((nvl(p_cr_rec.tax_rate,0) <> 0) AND (l_mcd_exists)) THEN */
2949
2950 IF ((p_cr_rec.tax_rate IS NOT NULL) AND (l_mcd_exists)) THEN
2951
2952 l_ael_line_rec.source_table := 'MCD';
2953 l_ael_line_rec.source_id := l_mcd_first_rec_id; -- Hook tax line with first MCD line
2954 l_ael_line_rec.ae_line_type := 'TAX';
2955 l_ael_line_rec.ae_line_type_secondary := 'MISCCASH';
2956 l_ael_line_rec.account := p_vat_rec.tax_account_id;
2957 l_ael_line_rec.tax_code_id := p_vat_rec.vat_tax_id;
2958 l_ael_line_rec.tax_link_id := 1;
2959
2960 /*------------------------------------------------------------------------------+
2961 | Calculate Tax for MCD using rate from Misc Cash Receipt or Payment |
2962 +------------------------------------------------------------------------------*/
2963 /* The below statement is commented for bug 2494858 and the next statement is added */
2964 /* l_mcd_tax_amt := arpcurr.CurrRound(p_cr_rec.amount * (p_cr_rec.tax_rate/(100 + p_cr_rec.tax_rate)),
2965 p_cr_rec.currency_code);*/
2966
2967 l_mcd_tax_amt := arp_etax_util.tax_curr_round(p_cr_rec.amount * p_cr_rec.tax_rate/(100 + p_cr_rec.tax_rate),p_cr_rec.currency_code,l_precision,l_min_acct_unit,l_rounding_rule,'Y');
2968
2969 /*------------------------------------------------------------------------------+
2970 | Maintain running totals adding tax amount |
2971 +------------------------------------------------------------------------------*/
2972 l_mcd_run_pro_amt_tot := l_mcd_run_pro_amt_tot + l_mcd_tax_amt;
2973
2974 /*------------------------------------------------------------------------------+
2975 | Calculate Tax accounted for MCD using rate from Misc Cash Receipt or Payment |
2976 +------------------------------------------------------------------------------*/
2977 /* The below statement is commented for bug 2494858 and the next two statements are added */
2978 /* l_mcd_tax_acctd_amt := arpcurr.CurrRound(l_mcd_actual_acctd_amt * (p_cr_rec.tax_rate/(100 + p_cr_rec.tax_rate)),
2979 g_ae_sys_rec.base_currency); */
2980
2981 fnd_currency.get_info(g_ae_sys_rec.base_currency, l_precision,l_extended_precision, l_min_acct_unit);
2982
2983 l_mcd_tax_acctd_amt := arp_etax_util.tax_curr_round(l_mcd_actual_acctd_amt * p_cr_rec.tax_rate/(100 + p_cr_rec.tax_rate), g_ae_sys_rec.base_currency, l_precision, l_min_acct_unit, l_rounding_rule, 'Y');
2984
2985 /*------------------------------------------------------------------------------+
2986 | Maintain running totals for tax accounted |
2987 +------------------------------------------------------------------------------*/
2988 l_mcd_run_pro_acctd_amt_tot := l_mcd_run_pro_acctd_amt_tot + l_mcd_tax_acctd_amt;
2989
2990 /*------------------------------------------------------------------------------+
2991 | Calculate Taxable amount and Taxable accounted amount for Tax |
2992 +------------------------------------------------------------------------------*/
2993 l_mcd_taxable_amt := p_cr_rec.amount - l_mcd_tax_amt;
2994
2995 l_mcd_taxable_acctd_amt := l_mcd_actual_acctd_amt - l_mcd_tax_acctd_amt ;
2996
2997 /*------------------------------------------------------------------------------+
2998 | Set Debit or Credits for Tax accounting record |
2999 +------------------------------------------------------------------------------*/
3000 IF sign(p_cr_rec.amount) = -1 THEN --Debit Tax Account Payments
3001
3002 l_ael_line_rec.entered_dr := abs(l_mcd_tax_amt);
3003 l_ael_line_rec.accounted_dr := abs(l_mcd_tax_acctd_amt);
3004
3005 l_ael_line_rec.entered_cr := NULL;
3006 l_ael_line_rec.accounted_cr := NULL;
3007
3008 l_ael_line_rec.taxable_entered_dr := abs(l_mcd_taxable_amt);
3009 l_ael_line_rec.taxable_accounted_dr := abs(l_mcd_taxable_acctd_amt);
3010
3011 l_ael_line_rec.taxable_entered_cr := NULL;
3012 l_ael_line_rec.taxable_accounted_cr := NULL;
3013
3014 ELSE --Credit Tax Account Receipts
3015
3016 l_ael_line_rec.entered_cr := l_mcd_tax_amt;
3017 l_ael_line_rec.accounted_cr := l_mcd_tax_acctd_amt;
3018
3019 l_ael_line_rec.entered_dr := NULL;
3020 l_ael_line_rec.accounted_dr := NULL;
3021
3022 l_ael_line_rec.taxable_entered_cr := l_mcd_taxable_amt;
3023 l_ael_line_rec.taxable_accounted_cr := l_mcd_taxable_acctd_amt;
3024
3025 l_ael_line_rec.taxable_entered_dr := NULL;
3026 l_ael_line_rec.taxable_accounted_dr := NULL;
3027
3028 END IF;
3029
3030 /*------------------------------------------------------------------------------+
3031 | Asign AEL for Tax line for Misc Cash |
3032 +------------------------------------------------------------------------------*/
3033 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
3034
3035 END IF; --End if tax rate is not null
3036
3037 /*------------------------------------------------------------------------------+
3038 | Raise no data found exception if this routine is called then MCD must exist |
3039 | for creation of accounting |
3040 +------------------------------------------------------------------------------*/
3041 IF NOT l_mcd_exists THEN
3042 select 'x'
3043 into l_dummy
3044 from dual
3045 where 1 = 2;
3046 END IF;
3047
3048 /*--------------------------------------------------------------------------------------------+
3049 |Now process for rounding correction generic in terms of the Maths which requires to be done |
3050 |End result is that the entered and accounted amounts must reconcile with those in the MCD |
3051 |table, rounding correction is added to the first MCD line. As there exists atleast one MCD |
3052 |hence process for rounding. |
3053 +--------------------------------------------------------------------------------------------*/
3054 IF sign(p_cr_rec.amount) = -1 THEN -- Add rounding correction to debits for Payments
3055
3056 g_ae_line_tbl(1).entered_dr := g_ae_line_tbl(1).entered_dr +
3057 abs(p_cr_rec.amount) - abs(l_mcd_run_pro_amt_tot);
3058
3059 g_ae_line_tbl(1).accounted_dr := g_ae_line_tbl(1).accounted_dr +
3060 abs(l_mcd_actual_acctd_amt) - abs(l_mcd_run_pro_acctd_amt_tot);
3061
3062 ELSE -- Add rounding correction for credits for Receipts
3063
3064 g_ae_line_tbl(1).entered_cr := g_ae_line_tbl(1).entered_cr +
3065 abs(p_cr_rec.amount) - abs(l_mcd_run_pro_amt_tot);
3066
3067 g_ae_line_tbl(1).accounted_cr := g_ae_line_tbl(1).accounted_cr +
3068 abs(l_mcd_actual_acctd_amt) - abs(l_mcd_run_pro_acctd_amt_tot);
3069
3070 END IF;
3071
3072 IF PG_DEBUG in ('Y', 'C') THEN
3073 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Create_Ae_Lines_MCD()-');
3074 END IF;
3075
3076 EXCEPTION
3077
3078 WHEN NO_DATA_FOUND THEN
3079 IF PG_DEBUG in ('Y', 'C') THEN
3080 arp_standard.debug('ARP_RECEIPTS_MAIN.Create_Ae_Lines_MCD - NO_DATA_FOUND' );
3081 END IF;
3082 RAISE;
3083
3084 WHEN OTHERS THEN
3085 IF PG_DEBUG in ('Y', 'C') THEN
3086 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Create_Ae_Lines_MCD');
3087 END IF;
3088 RAISE;
3089
3090 END Create_Ae_Lines_MCD;
3091
3092 /* =======================================================================
3093 | PROCEDURE Assign_Ael_Elements
3094 |
3095 | DESCRIPTION
3096 | This procedure stores the AE Line record into AE Lines PLSQL table.
3097 | Functions:
3098 | - Determine regular or negative Dr/Cr.
3099 | - Store AE Line Record in AE Lines PLSQL Table.
3100 | - In a fully implemented SLA model, Will determine the
3101 | account to use based on AE Line type and other parameters.
3102 | - In a fully implemented SLA model, Will determine the
3103 | account descriptions.
3104 |
3105 | GUIDELINE
3106 | - This procedure can be shared across document types
3107 | - Recommendation is to have one per document type(AE Derivation)
3108 |
3109 | PARAMETERS
3110 | p_ae_line_rec AE Line Record
3111 * ======================================================================*/
3112 PROCEDURE Assign_Ael_Elements(
3113 p_ae_line_rec IN ae_line_rec_type ) IS
3114
3115 l_account NUMBER;
3116 l_account_valid BOOLEAN;
3117 l_replace_default_account BOOLEAN;
3118
3119 BEGIN
3120 arp_global.init_global; --bug6024475
3121 IF PG_DEBUG in ('Y', 'C') THEN
3122 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Assign_Ael_Elements()+');
3123 END IF;
3124
3125 /*------------------------------------------------------+
3126 | Call Hook to Override Account |
3127 +------------------------------------------------------*/
3128 ARP_ACCT_HOOK.Override_Account(
3129 p_mode => g_mode,
3130 p_ae_doc_rec => g_ae_doc_rec,
3131 p_ae_event_rec => g_ae_event_rec,
3132 p_ae_line_rec => p_ae_line_rec,
3133 p_account => l_account,
3134 p_account_valid => l_account_valid,
3135 p_replace_default_account => l_replace_default_account
3136 );
3137
3138 IF ( NOT l_replace_default_account ) THEN
3139
3140 /*------------------------------------------------------+
3141 | SLA : Build Account for AE Line Type |
3142 | When SLA is fully implemented Account Builder |
3143 | will be called from here. |
3144 +------------------------------------------------------*/
3145 l_account := p_ae_line_rec.account;
3146
3147 END IF; -- Replace default account?
3148
3149 /*------------------------------------------------------+
3150 | SLA : Build Account description for AE Line Type |
3151 | When SLA is fully implemented Description |
3152 | builder will be called from here. |
3153 +------------------------------------------------------*/
3154
3155 /*------------------------------------------------------+
3156 | SLA : Check Negative Dr/Cr for AE Line |
3157 | When SLA is fully implemented. |
3158 +------------------------------------------------------*/
3159
3160 /*------------------------------------------------------+
3161 | Store AE Line elements in AE Lines temp table |
3162 +------------------------------------------------------*/
3163 g_ae_line_ctr := g_ae_line_ctr +1;
3164
3165 g_ae_line_tbl(g_ae_line_ctr).ae_line_type := p_ae_line_rec.ae_line_type;
3166 g_ae_line_tbl(g_ae_line_ctr).ae_line_type_secondary := p_ae_line_rec.ae_line_type_secondary;
3167 g_ae_line_tbl(g_ae_line_ctr).source_id := p_ae_line_rec.source_id;
3168 g_ae_line_tbl(g_ae_line_ctr).source_table := p_ae_line_rec.source_table;
3169
3170 /*bug6024475 Passing the value of l_account after checking its validty and values of flags */
3171 if (l_replace_default_account and l_account_valid and l_account>0) then
3172 IF fnd_flex_keyval.validate_ccid(
3173 appl_short_name => 'SQLGL',
3174 key_flex_code => 'GL#',
3175 structure_number => arp_global.chart_of_accounts_id,
3176 combination_id => l_account) THEN
3177 g_ae_line_tbl(g_ae_line_ctr).account := l_account;
3178 --bug6313298
3179 IF (p_ae_line_rec.source_table = 'RA') then
3180 Update ar_receivable_applications set code_combination_id = l_account
3181 where receivable_application_id = p_ae_line_rec.source_id and status in
3182 ('ACC', 'UNID', 'UNAPP', 'ACTIVITY', 'OTHER ACC');
3183 END IF;
3184 else
3185 raise invalid_ccid_error;
3186 end if;
3187 else
3188 g_ae_line_tbl(g_ae_line_ctr).account := p_ae_line_rec.account;
3189 end if;
3190 /*bug6024475 end*/
3191
3192 g_ae_line_tbl(g_ae_line_ctr).entered_dr := p_ae_line_rec.entered_dr;
3193 g_ae_line_tbl(g_ae_line_ctr).entered_cr := p_ae_line_rec.entered_cr;
3194 g_ae_line_tbl(g_ae_line_ctr).accounted_dr := p_ae_line_rec.accounted_dr;
3195 g_ae_line_tbl(g_ae_line_ctr).accounted_cr := p_ae_line_rec.accounted_cr;
3196 g_ae_line_tbl(g_ae_line_ctr).source_id_secondary := p_ae_line_rec.source_id_secondary;
3197 g_ae_line_tbl(g_ae_line_ctr).source_table_secondary := p_ae_line_rec.source_table_secondary;
3198 g_ae_line_tbl(g_ae_line_ctr).currency_code := p_ae_line_rec.currency_code;
3199 g_ae_line_tbl(g_ae_line_ctr).currency_conversion_rate := p_ae_line_rec.currency_conversion_rate;
3200 g_ae_line_tbl(g_ae_line_ctr).currency_conversion_type := p_ae_line_rec.currency_conversion_type;
3201 g_ae_line_tbl(g_ae_line_ctr).currency_conversion_date := p_ae_line_rec.currency_conversion_date;
3202 g_ae_line_tbl(g_ae_line_ctr).third_party_id := p_ae_line_rec.third_party_id;
3203 g_ae_line_tbl(g_ae_line_ctr).third_party_sub_id := p_ae_line_rec.third_party_sub_id;
3204 g_ae_line_tbl(g_ae_line_ctr).tax_group_code_id := p_ae_line_rec.tax_group_code_id;
3205 g_ae_line_tbl(g_ae_line_ctr).tax_code_id := p_ae_line_rec.tax_code_id;
3206 g_ae_line_tbl(g_ae_line_ctr).location_segment_id := p_ae_line_rec.location_segment_id;
3207 g_ae_line_tbl(g_ae_line_ctr).taxable_entered_dr := p_ae_line_rec.taxable_entered_dr;
3208 g_ae_line_tbl(g_ae_line_ctr).taxable_entered_cr := p_ae_line_rec.taxable_entered_cr;
3209 g_ae_line_tbl(g_ae_line_ctr).taxable_accounted_dr := p_ae_line_rec.taxable_accounted_dr;
3210 g_ae_line_tbl(g_ae_line_ctr).taxable_accounted_cr := p_ae_line_rec.taxable_accounted_cr;
3211 g_ae_line_tbl(g_ae_line_ctr).applied_from_doc_table := p_ae_line_rec.applied_from_doc_table;
3212 g_ae_line_tbl(g_ae_line_ctr).applied_from_doc_id := p_ae_line_rec.applied_from_doc_id;
3213 g_ae_line_tbl(g_ae_line_ctr).applied_to_doc_table := p_ae_line_rec.applied_to_doc_table;
3214 g_ae_line_tbl(g_ae_line_ctr).applied_to_doc_id := p_ae_line_rec.applied_to_doc_id;
3215 g_ae_line_tbl(g_ae_line_ctr).tax_link_id := p_ae_line_rec.tax_link_id;
3216 g_ae_line_tbl(g_ae_line_ctr).reversed_source_id := p_ae_line_rec.reversed_source_id;
3217 --{3377004
3218 --{ref_dist_ccid + ref_mf_dist_flag
3219 g_ae_line_tbl(g_ae_line_ctr).ref_dist_ccid := p_ae_line_rec.ref_dist_ccid;
3220 g_ae_line_tbl(g_ae_line_ctr).ref_mf_dist_flag := p_ae_line_rec.ref_mf_dist_flag;
3221 g_ae_line_tbl(g_ae_line_ctr).ref_account_class := p_ae_line_rec.ref_account_class;
3222 g_ae_line_tbl(g_ae_line_ctr).activity_bucket := p_ae_line_rec.activity_bucket;
3223 --}
3224 g_ae_line_tbl(g_ae_line_ctr).ref_line_id := p_ae_line_rec.ref_line_id;
3225 g_ae_line_tbl(g_ae_line_ctr).ref_customer_trx_line_id := p_ae_line_rec.ref_customer_trx_line_id;
3226 g_ae_line_tbl(g_ae_line_ctr).ref_cust_trx_line_gl_dist_id := p_ae_line_rec.ref_cust_trx_line_gl_dist_id;
3227 g_ae_line_tbl(g_ae_line_ctr).from_amount_dr := p_ae_line_rec.from_amount_dr;
3228 g_ae_line_tbl(g_ae_line_ctr).from_amount_cr := p_ae_line_rec.from_amount_cr;
3229 g_ae_line_tbl(g_ae_line_ctr).from_acctd_amount_dr := p_ae_line_rec.from_acctd_amount_dr;
3230 g_ae_line_tbl(g_ae_line_ctr).from_acctd_amount_cr := p_ae_line_rec.from_acctd_amount_cr;
3231 --}
3232 IF PG_DEBUG in ('Y', 'C') THEN
3233 arp_standard.debug( 'ARP_RECEIPTS_MAIN.Assign_Ael_Elements()-');
3234 END IF;
3235
3236 EXCEPTION
3237 /*bug 6024475 adds a new exception.*/
3238 WHEN invalid_ccid_error THEN
3239 IF PG_DEBUG in ('Y', 'C') THEN
3240 arp_standard.debug('Invalid Account ccid - ARP_RECEIPTS_MAIN.Assign_Ael_Elements' );
3241 END IF;
3242 fnd_message.set_name('AR','AR_INVALID_ACCOUNT');
3243 RAISE;
3244 /* bug6024475 end*/
3245 WHEN OTHERS THEN
3246 IF PG_DEBUG in ('Y', 'C') THEN
3247 arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Assign_Ael_Elements');
3248 END IF;
3249 RAISE;
3250
3251 END Assign_Ael_Elements;
3252
3253 --{3377004
3254 FUNCTION ctl_id_index(p_ctl_id_tab IN DBMS_SQL.NUMBER_TABLE,
3255 p_ctl_id IN NUMBER)
3256 RETURN NUMBER
3257 IS
3258 result NUMBER;
3259 cnt NUMBER := 0;
3260 BEGIN
3261 arp_standard.debug('ctl_id_index+');
3262 result := -1;
3263 cnt := p_ctl_id_tab.COUNT;
3264 IF cnt > 0 THEN
3265 FOR i IN p_ctl_id_tab.FIRST .. p_ctl_id_tab.LAST LOOP
3266 IF p_ctl_id_tab(i) = p_ctl_id THEN
3267 result := i;
3268 EXIT;
3269 END IF;
3270 END LOOP;
3271 ELSE
3272 result := 1;
3273 END IF;
3274 IF result = -1 THEN
3275 result := cnt + 1;
3276 END IF;
3277 arp_standard.debug(' result index:'||result);
3278 arp_standard.debug('ctl_id_index-');
3279 RETURN result;
3280 END;
3281
3282 PROCEDURE init_rem_amt(x_rem_amt IN OUT NOCOPY ctl_rem_amt_type,
3283 p_index IN NUMBER)
3284 IS
3285 BEGIN
3286 x_rem_amt.customer_trx_line_id(p_index) := 0;
3287 x_rem_amt.amount_due_remaining(p_index) := 0;
3288 x_rem_amt.acctd_amount_due_remaining(p_index) := 0;
3289 x_rem_amt.chrg_amount_remaining(p_index) := 0;
3290 x_rem_amt.chrg_acctd_amount_remaining(p_index) := 0;
3291 END;
3292 --}
3293
3294
3295 END ARP_RECEIPTS_MAIN;