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