[Home] [Help]
PACKAGE BODY: APPS.ARP_ADJUSTMENTS_MAIN
Source
1 PACKAGE BODY ARP_ADJUSTMENTS_MAIN AS
2 /* $Header: ARTADJMB.pls 120.12.12010000.3 2008/11/13 12:03:34 spdixit 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_ae_sys_rec ae_sys_rec_type;
13 g_ae_line_ctr BINARY_INTEGER ;
14
15 --{3377004
16 TYPE ctl_rem_amt_type IS RECORD
17 (customer_trx_line_id DBMS_SQL.NUMBER_TABLE,
18 amount_due_remaining DBMS_SQL.NUMBER_TABLE,
19 acctd_amount_due_remaining DBMS_SQL.NUMBER_TABLE,
20 chrg_amount_remaining DBMS_SQL.NUMBER_TABLE,
21 chrg_acctd_amount_remaining DBMS_SQL.NUMBER_TABLE);
22 --}
23
24 /* =======================================================================
25 | Private Procedure/Function prototypes
26 * ======================================================================*/
27 PROCEDURE Init_Ae_Lines;
28
29 PROCEDURE Derive_Accounting_Entry(p_from_llca_call IN VARCHAR2 DEFAULT 'N',
30 p_gt_id IN NUMBER DEFAULT NULL
31 );
32
33 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
34
35 PROCEDURE Delete_ADJ(p_ae_deleted OUT NOCOPY BOOLEAN);
36
37 PROCEDURE Reverse_Adjustment;
38
39 PROCEDURE Get_Doc_Entitity_Data (
40 p_level IN VARCHAR2 ,
41 p_adj_rec OUT NOCOPY ar_adjustments%ROWTYPE ,
42 p_cust_inv_rec OUT NOCOPY ra_customer_trx%ROWTYPE ,
43 p_ctlgd_inv_rec OUT NOCOPY ra_cust_trx_line_gl_dist%ROWTYPE ,
44 p_rule_rec OUT NOCOPY ae_rule_rec_type ,
45 p_ard_rec OUT NOCOPY ar_distributions%ROWTYPE );
46
47 -- Added parameter for Line Level Adjustment
48 PROCEDURE Create_Ae_Lines_Common (
49 p_level IN VARCHAR2,
50 p_from_llca_call IN VARCHAR2 DEFAULT 'N',
51 p_gt_id IN NUMBER DEFAULT NULL);
52 -- Added parameter for Line Level Adjustment
53 PROCEDURE Create_Ae_Lines_ADJ(
54 p_adj_rec IN ar_adjustments%ROWTYPE ,
55 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
56 p_ctlgd_inv_rec IN ra_cust_trx_line_gl_dist%ROWTYPE ,
57 p_rule_rec IN ae_rule_rec_type ,
58 p_ard_rec IN ar_distributions%ROWTYPE ,
59 p_from_llca_call IN VARCHAR2 DEFAULT 'N' ,
60 p_gt_id IN NUMBER DEFAULT NULL);
61
62 PROCEDURE Assign_Ael_Elements(
63 p_ae_line_rec IN ae_line_rec_type );
64
65 --{3377004
66 FUNCTION ctl_id_index(p_ctl_id_tab IN DBMS_SQL.NUMBER_TABLE,
67 p_ctl_id IN NUMBER)
68 RETURN NUMBER;
69
70 PROCEDURE init_rem_amt(x_rem_amt IN OUT NOCOPY ctl_rem_amt_type,
71 p_index IN NUMBER);
72 --}
73
74 /* =======================================================================
75 | Procedures/functions
76 * ======================================================================*/
77 /* =======================================================================
78 | PUBLIC PROCEDURE Delete_Acct
79 |
80 | DESCRIPTION
81 | Accounting Entry Deletion
82 | -------------------------
83 | This procedure is the Accounting Entry deletion routine which
84 | deletes data associated with Adjustments based on event and source
85 | table.
86 |
87 | PARAMETERS
88 | p_mode IN Document or Accounting Event mode
89 | p_ae_doc_rec IN Document Record
90 | p_ae_event_rec IN Event Record
91 | p_ae_deleted OUT NOCOPY AE Lines deletion status
92 * ======================================================================*/
93 PROCEDURE Delete_Acct( p_mode IN VARCHAR2,
94 p_ae_doc_rec IN ae_doc_rec_type,
95 p_ae_event_rec IN ae_event_rec_type,
96 p_ae_deleted OUT NOCOPY BOOLEAN ) IS
97 BEGIN
98 IF PG_DEBUG in ('Y', 'C') THEN
99 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Delete_Acct()+');
100 END IF;
101
102 /*----------------------------------------------------+
103 | Copy Document/Event Data to Global |
104 +----------------------------------------------------*/
105 g_mode := p_mode;
106 g_ae_doc_rec := p_ae_doc_rec;
107 g_ae_event_rec := p_ae_event_rec;
108
109 IF ( g_ae_doc_rec.source_table = 'ADJ' ) THEN
110
111 Delete_ADJ(p_ae_deleted => p_ae_deleted) ;
112
113 END IF;
114
115 IF PG_DEBUG in ('Y', 'C') THEN
116 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Delete_Acct()-');
117 END IF;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 IF PG_DEBUG in ('Y', 'C') THEN
122 arp_standard.debug( 'EXCEPTION: ARP_ADJUSTMENTS_MAIN.Delete_Acct');
123 END IF;
124 RAISE;
125
126 END Delete_Acct;
127
128 /* =======================================================================
129 | PUBLIC PROCEDURE Execute
130 |
131 | DESCRIPTION
132 | Accounting Entry Derivation Method
133 | ----------------------------------
134 | This procedure is the Accounting Entry derivation method for all
135 | accounting events associated with the Adjustments layer.
136 |
137 | Functions of the AE Derivation Method are:
138 | - Single Entry Point for easy extensibility
139 | - Read Event Data
140 | - Read Transaction and Setup Data
141 | - Determine AE Lines affected
142 | - Derive AE Lines
143 | - Return AE Lines created in a PL/SQL table.
144 |
145 | PARAMETERS
146 | p_mode IN Document or Accounting Event mode
147 | p_ae_doc_rec IN Document Record
148 | p_ae_event_rec IN Event Record
149 | p_ae_line_tbl OUT NOCOPY AE Lines table
150 | p_ae_created OUT NOCOPY AE Lines creation status
151 * ======================================================================*/
152 PROCEDURE Execute( p_mode IN VARCHAR2,
153 p_ae_doc_rec IN ae_doc_rec_type,
154 p_ae_event_rec IN ae_event_rec_type,
155 p_ae_line_tbl OUT NOCOPY ae_line_tbl_type,
156 p_ae_created OUT NOCOPY BOOLEAN,
157 p_from_llca_call IN VARCHAR2 DEFAULT 'N',
158 p_gt_id IN NUMBER DEFAULT NULL) IS
159
160
161 BEGIN
162 IF PG_DEBUG in ('Y', 'C') THEN
163 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Execute()+');
164 END IF;
165 /*------------------------------------------------------+
166 | Initialize Accounting Entry Header and Lines |
167 +------------------------------------------------------*/
168 g_ae_line_ctr := 0;
169 Init_Ae_Lines;
170
171 /*---------------------------------------------------------------+
172 | Copy Document/Event Data to Global, derive System Options info|
173 +---------------------------------------------------------------*/
174 g_mode := p_mode;
175 g_ae_doc_rec := p_ae_doc_rec;
176 g_ae_event_rec := p_ae_event_rec;
177
178 --Get system options info
179
180 g_ae_sys_rec.set_of_books_id := ARP_ACCT_MAIN.ae_sys_rec.set_of_books_id;
181 g_ae_sys_rec.gain_cc_id := ARP_ACCT_MAIN.ae_sys_rec.gain_cc_id;
182 g_ae_sys_rec.loss_cc_id := ARP_ACCT_MAIN.ae_sys_rec.loss_cc_id;
183 g_ae_sys_rec.round_cc_id := ARP_ACCT_MAIN.ae_sys_rec.round_cc_id;
184 g_ae_sys_rec.coa_id := ARP_ACCT_MAIN.ae_sys_rec.coa_id;
185 g_ae_sys_rec.base_currency := ARP_ACCT_MAIN.ae_sys_rec.base_currency;
186 g_ae_sys_rec.base_precision := ARP_ACCT_MAIN.ae_sys_rec.base_precision;
187 g_ae_sys_rec.base_min_acc_unit := ARP_ACCT_MAIN.ae_sys_rec.base_min_acc_unit;
188
189 -- MRC TRIGGER REPLACEMENT
190 -- Initialize a new global variable:
191 g_ae_sys_rec.sob_type := ARP_ACCT_MAIN.ae_sys_rec.sob_type;
192
193 /*------------------------------------------------------+
194 | Derive Accounting Entry |
195 +------------------------------------------------------*/
196 Derive_Accounting_Entry( p_from_llca_call => p_from_llca_call,
197 p_gt_id => p_gt_id);
198
199 /*------------------------------------------------------+
200 | Return Accounting Entry Creation Status |
201 +------------------------------------------------------*/
202 p_ae_line_tbl := g_ae_line_tbl;
203
204 IF g_ae_line_tbl.EXISTS(g_ae_line_ctr) THEN
205
206 p_ae_created := TRUE;
207
208 ELSE
209
210 p_ae_created := FALSE;
211
212 END IF;
213
214 IF PG_DEBUG in ('Y', 'C') THEN
215 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Execute()-');
216 END IF;
217
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 IF PG_DEBUG in ('Y', 'C') THEN
221 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Execute - NO_DATA_FOUND' );
222 END IF;
223 RAISE;
224
225 WHEN OTHERS THEN
226 IF PG_DEBUG in ('Y', 'C') THEN
227 arp_standard.debug( 'EXCEPTION: ARP_ADJUSTMENTS_MAIN.Execute');
228 END IF;
229 RAISE;
230
231 END Execute;
232
233
234 /* =======================================================================
235 |
236 | PROCEDURE Init_Ae_Lines
237 |
238 * ======================================================================*/
239 PROCEDURE Init_Ae_Lines IS
240
241 BEGIN
242
243 IF PG_DEBUG in ('Y', 'C') THEN
244 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Init_Ae_Lines()+');
245 END IF;
246
247 g_ae_line_tbl := g_empty_ae_line_tbl;
248
249 IF PG_DEBUG in ('Y', 'C') THEN
250 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Init_Ae_Lines()-');
251 END IF;
252 EXCEPTION
253
254 WHEN OTHERS THEN
255 IF PG_DEBUG in ('Y', 'C') THEN
256 arp_standard.debug( 'EXCEPTION: ARP_ADJUSTMENTS_MAIN.Init_Ae_Lines');
257 END IF;
258 RAISE;
259
260 END Init_Ae_Lines;
261
262 /* =======================================================================
263 |
264 | PROCEDURE Derive_Accounting_Entry
265 |
266 | DESCRIPTION
267 | This procedure gets the necessary transaction data and determines
268 | the accounting entries to be created at each of entity level.
269 |
270 | PARAMETERS
271 | Event_Rec Global Event Record
272 * ======================================================================*/
273 PROCEDURE Derive_Accounting_Entry (p_from_llca_call IN VARCHAR2 DEFAULT 'N',
274 p_gt_id IN NUMBER DEFAULT NULL
275 )
276 IS
277
278 BEGIN
279 IF PG_DEBUG in ('Y', 'C') THEN
280 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Derive_Accounting_Entry()+');
281 END IF;
282 /*------------------------------------------------------------+
283 | Create Accounting Entries at the Document Entity level. |
284 +------------------------------------------------------------*/
285 IF ( g_ae_doc_rec.accounting_entity_level = 'ONE' ) THEN
286
287 IF ((g_ae_doc_rec.source_id_old IS NOT NULL) and (g_ae_doc_rec.other_flag = 'REVERSE')) THEN
288 Reverse_Adjustment;
289
290 ELSIF ( g_ae_doc_rec.source_table = 'ADJ' ) THEN
291
292 /*---------------------------------------------------------+
293 | Create Accounting Entry Lines, Adjustments |
294 +---------------------------------------------------------*/
295 Create_Ae_Lines_Common(p_level => 'ADJ',
296 p_from_llca_call => p_from_llca_call,
297 p_gt_id => p_gt_id);
298
299 END IF;
300
301 END IF; -- accounting_entity_level = ONE
302
303 /*------------------------------------------------------+
304 | Create Accounting Entries at the Document Level |
305 | (All Entities) |
306 +------------------------------------------------------*/
307 IF ( g_ae_doc_rec.accounting_entity_level = 'ALL' ) THEN
308
309 NULL;
310
311 END IF; -- accounting_entity_level = ALL?
312
313 IF PG_DEBUG in ('Y', 'C') THEN
314 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Derive_Accounting_Entry()-');
315 END IF;
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 IF PG_DEBUG in ('Y', 'C') THEN
320 arp_standard.debug( 'EXCEPTION: ARP_ADJUSTMENTS_MAIN.Derive_Accounting_Entry');
321 END IF;
322 RAISE;
323
324 END Derive_Accounting_Entry;
325
326 /* =======================================================================
327 |
328 | PROCEDURE Delete_ADJ
329 |
330 | DESCRIPTION
331 | Deletes accounting associated with a Adjustment id from the
332 | AR_DISTRIBUTIONS table.This routine deletes all records
333 | matching the input source_id. Note records from child table
334 | (AR_DISTRIBUTIONS) be deleted first.
335 |
336 | PARAMETERS
337 | p_ae_deleted indicates whether records were deleted
338 | for source_id
339 * ======================================================================*/
340 PROCEDURE Delete_ADJ(p_ae_deleted OUT NOCOPY BOOLEAN) IS
341
342 l_source_id ar_distributions.source_id%TYPE;
343
344 l_ar_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
345
346 BEGIN
347 IF PG_DEBUG in ('Y', 'C') THEN
348 arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Delete_ADJ()+');
349 END IF;
350
351 /*-------------------------------------------------------------------+
352 | Verify that the source id is a valid candidate for deletion |
353 +-------------------------------------------------------------------*/
354 SELECT adj.adjustment_id
355 INTO l_source_id
356 FROM ar_adjustments adj
357 WHERE adj.adjustment_id = g_ae_doc_rec.source_id
358 AND adj.posting_control_id = -3
359 AND g_ae_doc_rec.source_table = 'ADJ'
360 AND EXISTS (SELECT 'x'
361 FROM ar_distributions ard
362 WHERE ard.source_id = adj.adjustment_id
363 AND ard.source_table = 'ADJ');
364
365 /*-------------------------------------------------------------------+
366 | Delete all accounting for source id and source table combination |
367 | if valid candidate for deletion |
368 +-------------------------------------------------------------------*/
369
370 -- modified for mrc trigger elimination.
371 DELETE FROM AR_DISTRIBUTIONS
372 WHERE source_id = l_source_id
373 AND source_table = 'ADJ'
374 RETURNING line_id
375 BULK COLLECT INTO l_ar_dist_key_value_list;
376
377 /*---------------------------------+
378 | Calling central MRC library |
379 | for MRC Integration |
380 +---------------------------------*/
381 --{BUG#4301323
382 -- ar_mrc_engine.maintain_mrc_data(
383 -- p_event_mode => 'DELETE',
384 -- p_table_name => 'AR_DISTRIBUTIONS',
385 -- p_mode => 'BATCH',
386 -- p_key_value_list => l_ar_dist_key_value_list);
387 --}
388
389 IF PG_DEBUG in ('Y', 'C') THEN
390 arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Delete_ADJ()-');
391 END IF;
392
393 EXCEPTION
394 WHEN NO_DATA_FOUND THEN
395 IF PG_DEBUG in ('Y', 'C') THEN
396 arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Delete_ADJ - NO_DATA_FOUND' );
397 END IF;
398 p_ae_deleted := FALSE;
399 RAISE;
400
401 WHEN OTHERS THEN
402 IF PG_DEBUG in ('Y', 'C') THEN
403 arp_standard.debug('EXCEPTION: ARP_ADJUSTMENTS_MAIN.Delete_ADJ');
404 END IF;
405 p_ae_deleted := FALSE;
406 RAISE ;
407
408 END Delete_ADJ;
409
410 /* =======================================================================
411 |
412 | PROCEDURE Reverse_Adjustment
413 |
414 | DESCRIPTION
415 | This procedure reverses the records in AR_DISTRIBUTIONS for a
416 | Adjustment. It gets the accounting for the old adjustment and
417 | simply swaps the amount columns creating new accounting for the
418 | new adjustment id. Adjustment reversals occur for receivable trx id
419 | -13.
420 |
421 | For Bills Receivable linked deferred tax for an application being
422 | reversed is also backed out NOCOPY by using a link id to reverse out NOCOPY the
423 | tax accounting associated with the Transaction history record for
424 | the maturity date event for a Bill. For this change a union was
425 | added and the source table is populated from g_ae_doc_rec
426 |
427 | Reversals are opposite images of the old adjustment, with the exception
428 | that reconciliation entries are not reversed from the old adjustment,
429 | it is left to the Reconciliation routine to do this as an effect across
430 | all activity.
431 |
432 | PARAMETERS
433 | None
434 * ======================================================================*/
435 PROCEDURE Reverse_Adjustment IS
436
437 -- MRC TRIGGER REPLACEMENT: Enumerate all columns and add two more unions
438 -- to retrieve MRC data
439
440 CURSOR get_old_ard IS
441 select ard.source_type,
442 ard.source_id_secondary,
443 ard.source_type_secondary,
444 ard.source_table_secondary,
445 ard.code_combination_id,
446 ard.amount_dr,
447 ard.amount_cr,
448 ard.acctd_amount_dr,
449 ard.acctd_amount_cr,
450 ard.taxable_entered_cr,
451 ard.taxable_entered_dr,
452 ard.taxable_accounted_cr,
453 ard.taxable_accounted_dr,
454 ard.currency_code,
455 ard.currency_conversion_rate,
456 ard.currency_conversion_type,
457 ard.currency_conversion_date,
458 ard.third_party_id,
459 ard.third_party_sub_id,
460 ard.tax_group_code_id,
461 ard.tax_code_id,
462 ard.location_segment_id,
463 ard.tax_link_id,
464 --{BUG#2979254
465 ard.ref_customer_trx_line_id,
466 ard.ref_cust_trx_line_gl_dist_id,
467 ard.ref_line_id,
468 --}
469 --{3377004
470 DECODE( ard.ref_customer_trx_line_id, NULL,'N',
471 DECODE(adj.type,'CHARGES','ADDCHRG','ADDCTL')) WHICH_BUCKET,
472 --}
473 ard.ref_account_class,
474 ard.activity_bucket,
475 ard.ref_dist_ccid
476 from ar_distributions ard,
477 ar_adjustments adj
478 where g_ae_sys_rec.sob_type = 'P'
479 and ard.source_id = g_ae_doc_rec.source_id_old
480 and ard.source_table = g_ae_doc_rec.source_table
481 and nvl(ard.source_type_secondary,'X') NOT IN
482 ('ASSIGNMENT_RECONCILE','RECONCILE')
483 and adj.adjustment_id(+) = g_ae_doc_rec.source_id_old --3377004
484 UNION
485 select ard.source_type,
486 ard.source_id_secondary,
487 ard.source_type_secondary,
488 ard.source_table_secondary,
489 ard.code_combination_id,
490 ard.amount_dr,
491 ard.amount_cr,
492 ard.acctd_amount_dr,
493 ard.acctd_amount_cr,
494 ard.taxable_entered_cr,
495 ard.taxable_entered_dr,
496 ard.taxable_accounted_cr,
497 ard.taxable_accounted_dr,
498 ard.currency_code,
499 ard.currency_conversion_rate,
500 ard.currency_conversion_type,
501 ard.currency_conversion_date,
502 ard.third_party_id,
503 ard.third_party_sub_id,
504 ard.tax_group_code_id,
505 ard.tax_code_id,
506 ard.location_segment_id,
507 ard.tax_link_id,
508 --{BUG#2979254
509 ard.ref_customer_trx_line_id,
510 ard.ref_cust_trx_line_gl_dist_id,
511 ard.ref_line_id,
512 --}
513 --{3377004
514 DECODE( ard.ref_customer_trx_line_id, NULL,'N',
515 DECODE(adj.type,'CHARGES','ADDCHRG','ADDCTL')) WHICH_BUCKET,
516 --}
517 ard.ref_account_class,
518 ard.activity_bucket,
519 ard.ref_dist_ccid
520 from ar_distributions ard,
521 ar_adjustments adj
522 where g_ae_sys_rec.sob_type = 'P'
523 and adj.adjustment_id = g_ae_doc_rec.source_id_old
524 and ard.source_id = adj.link_to_trx_hist_id
525 and ard.source_table = 'TH' --for Bills Receivable Standard/Factored
526 and nvl(ard.source_type_secondary,'X') NOT IN
527 ('ASSIGNMENT_RECONCILE','RECONCILE')
528 and nvl(g_ae_doc_rec.event,'NONE') <> 'RISK_UNELIMINATED'
529 order by 1 ;
530 --{BUG4301323
531 /*
532 UNION
533 select ard.source_type,
534 ard.source_id_secondary,
535 ard.source_type_secondary,
536 ard.source_table_secondary,
537 ard.code_combination_id,
538 ard.amount_dr,
539 ard.amount_cr,
540 ard.acctd_amount_dr,
541 ard.acctd_amount_cr,
542 ard.taxable_entered_cr,
543 ard.taxable_entered_dr,
544 ard.taxable_accounted_cr,
545 ard.taxable_accounted_dr,
546 ard.currency_code,
547 ard.currency_conversion_rate,
548 ard.currency_conversion_type,
549 ard.currency_conversion_date,
550 ard.third_party_id,
551 ard.third_party_sub_id,
552 ard.tax_group_code_id,
553 ard.tax_code_id,
554 ard.location_segment_id,
555 ard.tax_link_id,
556 --{BUG#2979254
557 ard.ref_customer_trx_line_id,
558 ard.ref_cust_trx_line_gl_dist_id,
559 ard.ref_line_id,
560 --}
561 --{3377004
562 DECODE( ard.ref_customer_trx_line_id, NULL,'N',
563 DECODE(adj.type,'CHARGES','ADDCHRG','ADDCTL')) WHICH_BUCKET
564 --}
565 from ar_mc_distributions_all ard,
566 ar_adjustments adj
567 where g_ae_sys_rec.sob_type = 'R'
568 and ard.set_of_books_id = g_ae_sys_rec.set_of_books_id
569 and ard.source_id = g_ae_doc_rec.source_id_old
570 and ard.source_table = g_ae_doc_rec.source_table
571 and nvl(ard.source_type_secondary,'X') NOT IN
572 ('ASSIGNMENT_RECONCILE','RECONCILE')
573 and adj.adjustment_id(+) = g_ae_doc_rec.source_id_old --3377004
574 UNION
575 select ard.source_type,
576 ard.source_id_secondary,
577 ard.source_type_secondary,
578 ard.source_table_secondary,
579 ard.code_combination_id,
580 ard.amount_dr,
581 ard.amount_cr,
582 ard.acctd_amount_dr,
583 ard.acctd_amount_cr,
584 ard.taxable_entered_cr,
585 ard.taxable_entered_dr,
586 ard.taxable_accounted_cr,
587 ard.taxable_accounted_dr,
588 ard.currency_code,
589 ard.currency_conversion_rate,
590 ard.currency_conversion_type,
591 ard.currency_conversion_date,
592 ard.third_party_id,
593 ard.third_party_sub_id,
594 ard.tax_group_code_id,
595 ard.tax_code_id,
596 ard.location_segment_id,
597 ard.tax_link_id,
598 --{BUG#2979254
599 ard.ref_customer_trx_line_id,
600 ard.ref_cust_trx_line_gl_dist_id,
601 ard.ref_line_id,
602 --}
603 --{3377004
604 DECODE( ard.ref_customer_trx_line_id, NULL,'N',
605 DECODE(adj.type,'CHARGES','ADDCHRG','ADDCTL')) WHICH_BUCKET
606 --}
607 from ar_mc_distributions_all ard,
608 ar_adjustments adj
609 where g_ae_sys_rec.sob_type = 'R'
610 and g_ae_sys_rec.set_of_books_id = ard.set_of_books_id
611 and adj.adjustment_id = g_ae_doc_rec.source_id_old
612 and ard.source_id = adj.link_to_trx_hist_id
613 and ard.source_table = 'TH' --for Bills Receivable Standard/Factored
614 and nvl(ard.source_type_secondary,'X') NOT IN
615 ('ASSIGNMENT_RECONCILE','RECONCILE')
616 and nvl(g_ae_doc_rec.event,'NONE') <> 'RISK_UNELIMINATED'
617 order by 1 ;
618 */
619 -- MRC Trigger Replacement: modified cursor to extact information.
620
621 CURSOR get_adj_details IS
622 SELECT adj.customer_trx_id customer_trx_id,
623 adj.amount amount,
624 --{BUG4301323
625 -- DECODE(g_ae_sys_rec.sob_type, 'P',
626 -- adj.acctd_amount,
627 -- arp_mrc_acct_main.get_adj_entity_data(
628 -- adj.adjustment_id,
629 -- g_ae_sys_rec.set_of_books_id)) acctd_amount,
630 adj.acctd_amount acctd_amount,
631 --}
632 ctinv.invoice_currency_code invoice_currency_code ,
633 --{BUG4301323
634 -- DECODE(g_ae_sys_rec.sob_type, 'P',
635 -- ctinv.exchange_rate,
636 -- arp_mrc_acct_main.get_ctx_exg_rate(
637 -- ctinv.customer_trx_id,
638 -- g_ae_sys_rec.set_of_books_id)) exchange_rate,
639 ctinv.exchange_rate exchange_rate,
640 --}
641 --{BUG4301323
642 -- DECODE(g_ae_sys_rec.sob_type, 'P',
643 -- ctinv.exchange_rate_type,
644 -- arp_mrc_acct_main.get_ctx_exg_rate_type(
645 -- ctinv.customer_trx_id,
646 -- g_ae_sys_rec.set_of_books_id)) exchange_rate_type,
647 ctinv.exchange_rate_type exchange_rate_type,
648 --}
649 --{BUG4301323
650 -- DECODE(g_ae_sys_rec.sob_type, 'P',
651 -- ctinv.exchange_date,
652 -- arp_mrc_acct_main.get_ctx_exg_date(
653 -- ctinv.customer_trx_id,
654 -- g_ae_sys_rec.set_of_books_id)) exchange_date,
655 ctinv.exchange_date exchange_date,
656 --}
657 ctinv.trx_date trx_date,
658 ctinv.bill_to_customer_id bill_to_customer_id,
659 ctinv.bill_to_site_use_id bill_to_site_use_id,
660 ctinv.drawee_id drawee_id,
661 ctinv.drawee_site_use_id drawee_site_use_id
662 from ar_adjustments adj,
663 ra_customer_trx ctinv
664 where adj.adjustment_id = g_ae_doc_rec.source_id_old
665 and adj.status = 'A'
666 and g_ae_doc_rec.source_table = 'ADJ'
667 and adj.customer_trx_id = ctinv.customer_trx_id;
668
669 l_ael_line_rec ae_line_rec_type;
670 l_ael_empty_line_rec ae_line_rec_type;
671 i BINARY_INTEGER := 1;
672 l_cust_inv_rec ra_customer_trx%ROWTYPE;
673 l_customer_trx_id NUMBER;
674 l_amount NUMBER;
675 l_acctd_amount NUMBER;
676 --{3377004
677 l_ctl_rem_amt ctl_rem_amt_type;
678 l_index NUMBER := 0;
679 --}
680
681 BEGIN
682
683 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Reverse_Adjustments()+');
684
685 --Reverse all adjustment accounting
686 FOR l_ard_rec in get_old_ard LOOP
687
688 --Initialize build record source id secondary and table is null for Adjustments
689 l_ael_line_rec := l_ael_empty_line_rec ;
690
691 l_ael_line_rec.ae_line_type := l_ard_rec.source_type ;
692 l_ael_line_rec.ae_line_type_secondary := l_ard_rec.source_type_secondary ;
693 l_ael_line_rec.source_id := g_ae_doc_rec.source_id ;
694 l_ael_line_rec.source_table := g_ae_doc_rec.source_table ;
695 l_ael_line_rec.source_table_secondary := l_ard_rec.source_table_secondary ;
696 l_ael_line_rec.source_id_secondary := l_ard_rec.source_id_secondary ;
697 l_ael_line_rec.account := l_ard_rec.code_combination_id ;
698
699 -- For reversals swap debits and credits
700
701 l_ael_line_rec.entered_cr := l_ard_rec.amount_dr;
702 l_ael_line_rec.accounted_cr := l_ard_rec.acctd_amount_dr;
703
704 l_ael_line_rec.entered_dr := l_ard_rec.amount_cr;
705 l_ael_line_rec.accounted_dr := l_ard_rec.acctd_amount_cr;
706
707 l_ael_line_rec.taxable_entered_cr := l_ard_rec.taxable_entered_dr;
708 l_ael_line_rec.taxable_accounted_cr := l_ard_rec.taxable_accounted_dr;
709
710 l_ael_line_rec.taxable_entered_dr := l_ard_rec.taxable_entered_cr;
711 l_ael_line_rec.taxable_accounted_dr := l_ard_rec.taxable_accounted_cr;
712
713 l_ael_line_rec.currency_code := l_ard_rec.currency_code;
714 l_ael_line_rec.currency_conversion_rate := l_ard_rec.currency_conversion_rate;
715 l_ael_line_rec.currency_conversion_type := l_ard_rec.currency_conversion_type;
716 l_ael_line_rec.currency_conversion_date := l_ard_rec.currency_conversion_date;
717 l_ael_line_rec.third_party_id := l_ard_rec.third_party_id;
718 l_ael_line_rec.third_party_sub_id := l_ard_rec.third_party_sub_id;
719 l_ael_line_rec.reversed_source_id := '';
720 l_ael_line_rec.tax_group_code_id := l_ard_rec.tax_group_code_id;
721 l_ael_line_rec.tax_code_id := l_ard_rec.tax_code_id;
722 l_ael_line_rec.location_segment_id := l_ard_rec.location_segment_id;
723 l_ael_line_rec.tax_link_id := l_ard_rec.tax_link_id;
724 --{BUG#2979254
725 l_ael_line_rec.ref_customer_trx_line_id := l_ard_rec.ref_customer_trx_line_id;
726 l_ael_line_rec.ref_cust_trx_line_gl_dist_id := l_ard_rec.ref_cust_trx_line_gl_dist_id;
727 l_ael_line_rec.ref_line_id := l_ard_rec.ref_line_id;
728 --}
729 l_ael_line_rec.ref_account_class := l_ard_rec.ref_account_class;
730 l_ael_line_rec.activity_bucket := l_ard_rec.activity_bucket;
731 l_ael_line_rec.ref_dist_ccid := l_ard_rec.ref_dist_ccid;
732 -- Assign AEL for Reversal
733 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
734
735
736 --{3377004
737 arp_standard.debug(' l_ard_rec.WHICH_BUCKET:'||l_ard_rec.WHICH_BUCKET);
738 arp_standard.debug(' l_ard_rec.ref_customer_trx_line_id:'||l_ard_rec.ref_customer_trx_line_id);
739 arp_standard.debug(' l_ard_rec.ref_line_id:'||l_ard_rec.ref_line_id);
740 IF l_ard_rec.WHICH_BUCKET = 'N' THEN
741 NULL;
742 ELSIF l_ard_rec.WHICH_BUCKET = 'ADDCTL' THEN
743
744 l_index := ctl_id_index(l_ctl_rem_amt.customer_trx_line_id,
745 l_ard_rec.ref_customer_trx_line_id);
746
747 IF NOT l_ctl_rem_amt.amount_due_remaining.EXISTS(l_index) THEN
748 init_rem_amt(x_rem_amt => l_ctl_rem_amt,
749 p_index => l_index);
750 END IF;
751
752 l_ctl_rem_amt.customer_trx_line_id(l_index) := l_ard_rec.ref_customer_trx_line_id;
753
754 l_ctl_rem_amt.amount_due_remaining(l_index) :=
755 NVL(l_ctl_rem_amt.amount_due_remaining(l_index),0)
756 + (NVL(l_ard_rec.amount_cr,0) - NVL(l_ard_rec.amount_dr,0));
757
758 l_ctl_rem_amt.acctd_amount_due_remaining(l_index) :=
759 NVL(l_ctl_rem_amt.acctd_amount_due_remaining(l_index),0)
760 + (NVL(l_ard_rec.acctd_amount_cr,0) - NVL(l_ard_rec.acctd_amount_dr,0));
761
762 arp_standard.debug('l_ctl_rem_amt.customer_trx_line_id('||l_index||'):'||
763 l_ctl_rem_amt.customer_trx_line_id(l_index));
764 arp_standard.debug('l_ctl_rem_amt.amount_due_remaining('||l_index||'):'||
765 l_ctl_rem_amt.amount_due_remaining(l_index));
766 arp_standard.debug('l_ctl_rem_amt.acctd_amount_due_remaining('||l_index||'):'||
767 l_ctl_rem_amt.acctd_amount_due_remaining(l_index));
768
769 ELSIF l_ard_rec.WHICH_BUCKET = 'ADDCHRG' THEN
770
771 l_index := ctl_id_index(l_ctl_rem_amt.customer_trx_line_id,
772 l_ard_rec.ref_customer_trx_line_id);
773
774 IF NOT l_ctl_rem_amt.chrg_amount_remaining.EXISTS(l_index) THEN
775 init_rem_amt(x_rem_amt => l_ctl_rem_amt,
776 p_index => l_index);
777 END IF;
778
779 l_ctl_rem_amt.customer_trx_line_id(l_index) := l_ard_rec.ref_customer_trx_line_id;
780
781 l_ctl_rem_amt.chrg_amount_remaining(l_index) :=
782 NVL(l_ctl_rem_amt.chrg_amount_remaining(l_index),0)
783 + (NVL(l_ard_rec.amount_cr,0) - NVL(l_ard_rec.amount_dr,0));
784
785 l_ctl_rem_amt.chrg_acctd_amount_remaining(l_index) :=
786 NVL(l_ctl_rem_amt.chrg_acctd_amount_remaining(l_index),0)
787 + (NVL(l_ard_rec.acctd_amount_cr,0) - NVL(l_ard_rec.acctd_amount_dr,0));
788
789 arp_standard.debug('l_ctl_rem_amt.customer_trx_line_id('||l_index||'):'||
790 l_ctl_rem_amt.customer_trx_line_id(l_index));
791 arp_standard.debug('l_ctl_rem_amt.chrg_amount_remaining('||l_index||'):'||
792 l_ctl_rem_amt.chrg_amount_remaining(l_index));
793 arp_standard.debug('l_ctl_rem_amt.chrg_acctd_amount_remaining('||l_index||'):'||
794 l_ctl_rem_amt.chrg_acctd_amount_remaining(l_index));
795
796 END IF;
797 --}
798 END LOOP;
799
800 --{3377004
801 IF l_index <> 0 THEN
802 FORALL m IN l_ctl_rem_amt.customer_trx_line_id.FIRST .. l_ctl_rem_amt.customer_trx_line_id.LAST
803 UPDATE ra_customer_trx_lines
804 SET AMOUNT_DUE_REMAINING = AMOUNT_DUE_REMAINING + l_ctl_rem_amt.amount_due_remaining(m),
805 ACCTD_AMOUNT_DUE_REMAINING = ACCTD_AMOUNT_DUE_REMAINING + l_ctl_rem_amt.acctd_amount_due_remaining(m),
806 CHRG_AMOUNT_REMAINING = CHRG_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_amount_remaining(m),
807 CHRG_ACCTD_AMOUNT_REMAINING = CHRG_ACCTD_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_acctd_amount_remaining(m)
808 WHERE customer_trx_line_id = l_ctl_rem_amt.customer_trx_line_id(m);
809 END IF;
810 --}
811
812 /*-----------------------------------------------------------------------------------+
813 |Call the Reconciliation routine, this is necessary because the transaction or Bill |
814 |which may have been overapplied is now closed due to reversal, or else the reversal|
815 |may have resulted in re-opening the transaction, hence we need to back out NOCOPY the old |
816 |reconciliation entries for the Bill or the Transactions. The reversal of the |
817 |adjustment accounting must take place to call the Reversal Routine. When called |
818 |this routine will always attempt do a reversal, first reverse accounting then call |
819 |the reconciliation routine. Note the reversal of Reconciliation entries is left to |
820 |The Reconciliation routine below i.e. we do not reverse them in the above cursor. |
821 +-----------------------------------------------------------------------------------*/
822 arp_standard.debug('Before call to Reconciliation routine');
823
824 FOR l_get_adj in get_adj_details LOOP --loop executes once only for adjustment
825
826 /*-------------------------------------------------------------------------------+
827 | Set currency and exchange rate details to that of the document which has been |
828 | adjusted. These details will get overriden by the transaction line assignment |
829 | exchange rate details for Bill. |
830 +-------------------------------------------------------------------------------*/
831 l_cust_inv_rec.invoice_currency_code := l_get_adj.invoice_currency_code;
832 l_cust_inv_rec.exchange_rate := l_get_adj.exchange_rate;
833 l_cust_inv_rec.exchange_rate_type := l_get_adj.exchange_rate_type;
834 l_cust_inv_rec.exchange_date := l_get_adj.exchange_date;
835 l_cust_inv_rec.trx_date := l_get_adj.trx_date;
836 l_cust_inv_rec.bill_to_customer_id := l_get_adj.bill_to_customer_id;
837 l_cust_inv_rec.bill_to_site_use_id := l_get_adj.bill_to_site_use_id;
838 l_cust_inv_rec.drawee_id := l_get_adj.drawee_id;
839 l_cust_inv_rec.drawee_site_use_id := l_get_adj.drawee_site_use_id;
840
841 --Required to determine whether the payment schedule is closed or not
842 --emulate sign of Receipt application
843 l_customer_trx_id := l_get_adj.customer_trx_id ;
844 l_amount := l_get_adj.amount ;
845 l_acctd_amount := l_get_adj.acctd_amount ;
846
847 ARP_RECONCILE.Reconcile_trx_br(
848 p_mode => g_mode ,
849 p_ae_doc_rec => g_ae_doc_rec ,
850 p_ae_event_rec => g_ae_event_rec ,
851 p_cust_inv_rec => l_cust_inv_rec ,
852 p_activity_cust_trx_id => l_customer_trx_id ,
853 p_activity_amt => l_amount ,
854 p_activity_acctd_amt => l_acctd_amount ,
855 p_call_num => 1 ,
856 p_g_ae_line_tbl => g_ae_line_tbl ,
857 p_g_ae_ctr => g_ae_line_ctr );
858
859 END LOOP; --get adjustment details
860
861 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Reverse_Adjustments()-');
862
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN
865 arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Reverse_Adjustments - NO_DATA_FOUND' );
866 RAISE;
867
868 WHEN OTHERS THEN
869 arp_standard.debug('EXCEPTION: ARP_ADJUSTMENTS_MAIN.Reverse_Adjustments');
870 RAISE ;
871
872 END Reverse_Adjustment;
873
874
875 /* =======================================================================
876 |
877 | PROCEDURE Get_Doc_Entitity_Data
878 |
879 | DESCRIPTION
880 | This procedure gets the necessary transaction data for each entity
881 | level.
882 |
883 | PARAMETERS
884 | p_level Entitity Level
885 | p_adj_rec Adjustment Record
886 | p_cust_inv_rec Invoice document or On Account Credit Memo data
887 | p_ctlgd_inv_rec Receivable account for Adjustment
888 | p_rule_rec Rule record
889 | p_ard_rec Distributions record used by Bills Receivable
890 * ======================================================================*/
891 PROCEDURE Get_Doc_Entitity_Data (
892 p_level IN VARCHAR2 ,
893 p_adj_rec OUT NOCOPY ar_adjustments%ROWTYPE ,
894 p_cust_inv_rec OUT NOCOPY ra_customer_trx%ROWTYPE ,
895 p_ctlgd_inv_rec OUT NOCOPY ra_cust_trx_line_gl_dist%ROWTYPE ,
896 p_rule_rec OUT NOCOPY ae_rule_rec_type ,
897 p_ard_rec OUT NOCOPY ar_distributions%ROWTYPE ) IS
898
899 l_tax_rate_id NUMBER;
900 l_le_id NUMBER;
901 l_msg_count NUMBER;
902 l_msg_data VARCHAR2(1024);
903 l_effective_date DATE;
904 l_return_status VARCHAR2(10);
905
906 BEGIN
907
908 IF PG_DEBUG in ('Y', 'C') THEN
909 arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Get_Doc_Entitity_Data()+');
910 END IF;
911
912 IF p_level = 'ADJ' then
913
914 /* receivables activities can now be set up for either org
915 or legal entity. When set up by legal entity, there is a
916 new child table that carries the asset_tax_code for each
917 activity/LE pair. */
918
919 IF arp_legal_entity_util.is_le_subscriber
920 THEN
921 /* LE setup is enabled */
922 /* 5236782 - but ar_rec_trx_le_details not required for adj */
923 select adj.adjustment_id ,
924 adj.customer_trx_id ,
925 adj.customer_trx_line_id ,
926 adj.payment_schedule_id ,
927 adj.receivables_trx_id ,
928 adj.code_combination_id ,
929 adj.apply_date ,
930 adj.gl_date ,
931 adj.type ,
932 adj.status ,
933 adj.amount ,
934 adj.acctd_amount ,
935 adj.line_adjusted ,
936 adj.freight_adjusted ,
937 adj.tax_adjusted ,
938 adj.receivables_charges_adjusted ,
939 ctinv.invoice_currency_code ,
940 ctinv.exchange_rate exchange_rate,
941 ctinv.exchange_rate_type exchange_rate_type,
942 ctinv.exchange_date exchange_date,
943 ctinv.bill_to_customer_id ,
944 ctinv.bill_to_site_use_id ,
945 ctinv.drawee_id ,
946 ctinv.drawee_site_use_id ,
947 ctlgdinv.code_combination_id ,
948 decode(g_ae_doc_rec.other_flag,
949 'CBREVERSAL', 'ACTIVITY_GL_ACCOUNT', --trx id -12
950 'CHARGEBACK', 'ACTIVITY_GL_ACCOUNT', --trx id -11
951 'COMMITMENT', 'ACTIVITY_GL_ACCOUNT', --trx od -1
952 nvl(rt.gl_account_source, 'NO_SOURCE')) ,
953 decode(g_ae_doc_rec.other_flag,
954 'CBREVERSAL', 'NONE', --trx id -12
955 'CHARGEBACK', 'NONE', --trx id -11
956 'COMMITMENT', 'NONE', --trx id -1
957 nvl(rt.tax_code_source , 'NO_SOURCE')) ,
958 decode(g_ae_doc_rec.other_flag,
959 'CBREVERSAL', '',
960 'CHARGEBACK', '',
961 'COMMITMENT', '',
962 rt.tax_recoverable_flag) ,
963 decode(g_ae_doc_rec.other_flag,
964 'CBREVERSAL',g_ae_doc_rec.source_id_old, --chargeback reversal
965 'CHARGEBACK',g_ae_doc_rec.source_id_old, --chargeback
966 'COMMITMENT',g_ae_doc_rec.source_id_old, --commitments
967 'OVERRIDE' ,g_ae_doc_rec.source_id_old, --when user specifies account
968 rt.code_combination_id) , --in adjustment form
969 nvl(rtd.asset_tax_code, rt.asset_tax_code) ,
970 nvl(rtd.liability_tax_code, rt.liability_tax_code),
971 '' ,
972 '' ,
973 'NO_SOURCE' ,
974 'NO_SOURCE' ,
975 '' ,
976 '' ,
977 '' ,
978 '' ,
979 '' ,
980 ''
981 into p_adj_rec.adjustment_id ,
982 p_adj_rec.customer_trx_id ,
983 p_adj_rec.customer_trx_line_id ,
984 p_adj_rec.payment_schedule_id ,
985 p_adj_rec.receivables_trx_id ,
986 p_adj_rec.code_combination_id ,
987 p_adj_rec.apply_date ,
988 p_adj_rec.gl_date ,
989 p_adj_rec.type ,
990 p_adj_rec.status ,
991 p_adj_rec.amount ,
992 p_adj_rec.acctd_amount ,
993 p_adj_rec.line_adjusted ,
994 p_adj_rec.freight_adjusted ,
995 p_adj_rec.tax_adjusted ,
996 p_adj_rec.receivables_charges_adjusted ,
997 p_cust_inv_rec.invoice_currency_code ,
998 p_cust_inv_rec.exchange_rate ,
999 p_cust_inv_rec.exchange_rate_type ,
1000 p_cust_inv_rec.exchange_date ,
1001 p_cust_inv_rec.bill_to_customer_id ,
1002 p_cust_inv_rec.bill_to_site_use_id ,
1003 p_cust_inv_rec.drawee_id ,
1004 p_cust_inv_rec.drawee_site_use_id ,
1005 p_ctlgd_inv_rec.code_combination_id ,
1006 p_rule_rec.gl_account_source1 ,
1007 p_rule_rec.tax_code_source1 ,
1008 p_rule_rec.tax_recoverable_flag1 ,
1009 p_rule_rec.code_combination_id1 ,
1010 p_rule_rec.asset_tax_code1 ,
1011 p_rule_rec.liability_tax_code1 ,
1012 p_rule_rec.act_tax_non_rec_ccid1 ,
1013 p_rule_rec.act_vat_tax_id1 ,
1014 p_rule_rec.gl_account_source2 ,
1015 p_rule_rec.tax_code_source2 ,
1016 p_rule_rec.tax_recoverable_flag2 ,
1017 p_rule_rec.code_combination_id2 ,
1018 p_rule_rec.asset_tax_code2 ,
1019 p_rule_rec.liability_tax_code2 ,
1020 p_rule_rec.act_tax_non_rec_ccid2 ,
1021 p_rule_rec.act_vat_tax_id2
1022 from ar_adjustments adj ,
1023 ar_receivables_trx rt ,
1024 ar_rec_trx_le_details rtd ,
1025 ra_customer_trx ctinv ,
1026 ra_cust_trx_line_gl_dist ctlgdinv
1027 where adj.adjustment_id = g_ae_doc_rec.source_id
1028 and adj.status = 'A' --only approved adjustments
1029 and adj.receivables_trx_id = rt.receivables_trx_id
1030 and rtd.receivables_trx_id (+) = rt.receivables_trx_id
1031 and nvl(rtd.legal_entity_id, ctinv.legal_entity_id)
1032 = ctinv.legal_entity_id
1033 and adj.customer_trx_id = ctinv.customer_trx_id --INV REC exchange rate Information
1034 and adj.customer_trx_id = ctlgdinv.customer_trx_id (+) --REC account ccid
1035 and 'REC' = ctlgdinv.account_class (+)
1036 and 'Y' = ctlgdinv.latest_rec_flag (+) ;
1037
1038 ELSE
1039 /* Setup is by OU, don't need child table */
1040
1041 select adj.adjustment_id ,
1042 adj.customer_trx_id ,
1043 adj.customer_trx_line_id ,
1044 adj.payment_schedule_id ,
1045 adj.receivables_trx_id ,
1046 adj.code_combination_id ,
1047 adj.apply_date ,
1048 adj.gl_date ,
1049 adj.type ,
1050 adj.status ,
1051 adj.amount ,
1052 adj.acctd_amount ,
1053 adj.line_adjusted ,
1054 adj.freight_adjusted ,
1055 adj.tax_adjusted ,
1056 adj.receivables_charges_adjusted ,
1057 ctinv.invoice_currency_code ,
1058 ctinv.exchange_rate exchange_rate,
1059 ctinv.exchange_rate_type exchange_rate_type,
1060 ctinv.exchange_date exchange_date,
1061 ctinv.bill_to_customer_id ,
1062 ctinv.bill_to_site_use_id ,
1063 ctinv.drawee_id ,
1064 ctinv.drawee_site_use_id ,
1065 ctlgdinv.code_combination_id ,
1066 decode(g_ae_doc_rec.other_flag,
1067 'CBREVERSAL', 'ACTIVITY_GL_ACCOUNT', --trx id -12
1068 'CHARGEBACK', 'ACTIVITY_GL_ACCOUNT', --trx id -11
1069 'COMMITMENT', 'ACTIVITY_GL_ACCOUNT', --trx od -1
1070 nvl(rt.gl_account_source, 'NO_SOURCE')) ,
1071 decode(g_ae_doc_rec.other_flag,
1072 'CBREVERSAL', 'NONE', --trx id -12
1073 'CHARGEBACK', 'NONE', --trx id -11
1074 'COMMITMENT', 'NONE', --trx id -1
1075 nvl(rt.tax_code_source , 'NO_SOURCE')) ,
1076 decode(g_ae_doc_rec.other_flag,
1077 'CBREVERSAL', '',
1078 'CHARGEBACK', '',
1079 'COMMITMENT', '',
1080 rt.tax_recoverable_flag) ,
1081 decode(g_ae_doc_rec.other_flag,
1082 'CBREVERSAL',g_ae_doc_rec.source_id_old, --chargeback reversal
1083 'CHARGEBACK',g_ae_doc_rec.source_id_old, --chargeback
1084 'COMMITMENT',g_ae_doc_rec.source_id_old, --commitments
1085 'OVERRIDE' ,g_ae_doc_rec.source_id_old, --when user specifies account
1086 rt.code_combination_id) , --in adjustment form
1087 rt.asset_tax_code ,
1088 rt.liability_tax_code ,
1089 '' ,
1090 '' ,
1091 'NO_SOURCE' ,
1092 'NO_SOURCE' ,
1093 '' ,
1094 '' ,
1095 '' ,
1096 '' ,
1097 '' ,
1098 ''
1099 into p_adj_rec.adjustment_id ,
1100 p_adj_rec.customer_trx_id ,
1101 p_adj_rec.customer_trx_line_id ,
1102 p_adj_rec.payment_schedule_id ,
1103 p_adj_rec.receivables_trx_id ,
1104 p_adj_rec.code_combination_id ,
1105 p_adj_rec.apply_date ,
1106 p_adj_rec.gl_date ,
1107 p_adj_rec.type ,
1108 p_adj_rec.status ,
1109 p_adj_rec.amount ,
1110 p_adj_rec.acctd_amount ,
1111 p_adj_rec.line_adjusted ,
1112 p_adj_rec.freight_adjusted ,
1113 p_adj_rec.tax_adjusted ,
1114 p_adj_rec.receivables_charges_adjusted ,
1115 p_cust_inv_rec.invoice_currency_code ,
1116 p_cust_inv_rec.exchange_rate ,
1117 p_cust_inv_rec.exchange_rate_type ,
1118 p_cust_inv_rec.exchange_date ,
1119 p_cust_inv_rec.bill_to_customer_id ,
1120 p_cust_inv_rec.bill_to_site_use_id ,
1121 p_cust_inv_rec.drawee_id ,
1122 p_cust_inv_rec.drawee_site_use_id ,
1123 p_ctlgd_inv_rec.code_combination_id ,
1124 p_rule_rec.gl_account_source1 ,
1125 p_rule_rec.tax_code_source1 ,
1126 p_rule_rec.tax_recoverable_flag1 ,
1127 p_rule_rec.code_combination_id1 ,
1128 p_rule_rec.asset_tax_code1 ,
1129 p_rule_rec.liability_tax_code1 ,
1130 p_rule_rec.act_tax_non_rec_ccid1 ,
1131 p_rule_rec.act_vat_tax_id1 ,
1132 p_rule_rec.gl_account_source2 ,
1133 p_rule_rec.tax_code_source2 ,
1134 p_rule_rec.tax_recoverable_flag2 ,
1135 p_rule_rec.code_combination_id2 ,
1136 p_rule_rec.asset_tax_code2 ,
1137 p_rule_rec.liability_tax_code2 ,
1138 p_rule_rec.act_tax_non_rec_ccid2 ,
1139 p_rule_rec.act_vat_tax_id2
1140 from ar_adjustments adj ,
1141 ar_receivables_trx rt ,
1142 ra_customer_trx ctinv ,
1143 ra_cust_trx_line_gl_dist ctlgdinv
1144 where adj.adjustment_id = g_ae_doc_rec.source_id
1145 and adj.status = 'A' --only approved adjustments
1146 and adj.receivables_trx_id = rt.receivables_trx_id
1147 and adj.customer_trx_id = ctinv.customer_trx_id --INV REC exchange rate Information
1148 and adj.customer_trx_id = ctlgdinv.customer_trx_id (+) --REC account ccid
1149 and 'REC' = ctlgdinv.account_class (+)
1150 and 'Y' = ctlgdinv.latest_rec_flag (+) ;
1151
1152 END IF;
1153
1154 /*----------------------------------------------------------+
1155 | Process for endorsements for Bills Receivable, get the |
1156 | Receivable account to offset the Write off account, |
1157 | based on the sign of the payment schedule, the Unpaid |
1158 | Bills receivable, or Bills Receivable account is selected|
1159 +----------------------------------------------------------*/
1160 IF (p_cust_inv_rec.drawee_site_use_id IS NOT null) THEN
1161
1162 -- MRC Trigger Replacment. Enumerated columns. Branched based
1163 -- on primary or Reporting.
1164 IF ( g_ae_sys_rec.sob_type = 'P') THEN
1165 select ard.line_id,
1166 ard.source_id,
1167 ard.source_table,
1168 ard.source_type,
1169 ard.code_combination_id,
1170 ard.amount_dr,
1171 ard.amount_cr,
1172 ard.acctd_amount_dr,
1173 ard.acctd_amount_cr,
1174 ard.creation_date,
1175 ard.created_by,
1176 ard.last_updated_by,
1177 ard.last_update_date,
1178 ard.last_update_login,
1179 ard.org_id,
1180 ard.source_table_secondary,
1181 ard.source_id_secondary,
1182 ard.currency_code,
1183 ard.currency_conversion_rate,
1184 ard.currency_conversion_type,
1185 ard.currency_conversion_date,
1186 ard.taxable_entered_dr,
1187 ard.taxable_entered_cr,
1188 ard.taxable_accounted_dr,
1189 ard.taxable_accounted_cr,
1190 ard.tax_link_id,
1191 ard.third_party_id,
1192 ard.third_party_sub_id,
1193 ard.reversed_source_id,
1194 ard.tax_code_id,
1195 ard.location_segment_id,
1196 ard.source_type_secondary,
1197 ard.tax_group_code_id,
1198 --{BUG#2979254
1199 ard.ref_customer_trx_line_id,
1200 ard.ref_cust_trx_line_gl_dist_id,
1201 ard.ref_line_id
1202 --}
1203 INTO p_ard_rec.line_id,
1204 p_ard_rec.source_id,
1205 p_ard_rec.source_table,
1206 p_ard_rec.source_type,
1207 p_ard_rec.code_combination_id,
1208 p_ard_rec.amount_dr,
1209 p_ard_rec.amount_cr,
1210 p_ard_rec.acctd_amount_dr,
1211 p_ard_rec.acctd_amount_cr,
1212 p_ard_rec.creation_date,
1213 p_ard_rec.created_by,
1214 p_ard_rec.last_updated_by,
1215 p_ard_rec.last_update_date,
1216 p_ard_rec.last_update_login,
1217 p_ard_rec.org_id,
1218 p_ard_rec.source_table_secondary,
1219 p_ard_rec.source_id_secondary,
1220 p_ard_rec.currency_code,
1221 p_ard_rec.currency_conversion_rate,
1222 p_ard_rec.currency_conversion_type,
1223 p_ard_rec.currency_conversion_date,
1224 p_ard_rec.taxable_entered_dr,
1225 p_ard_rec.taxable_entered_cr,
1226 p_ard_rec.taxable_accounted_dr,
1227 p_ard_rec.taxable_accounted_cr,
1228 p_ard_rec.tax_link_id,
1229 p_ard_rec.third_party_id,
1230 p_ard_rec.third_party_sub_id,
1231 p_ard_rec.reversed_source_id,
1232 p_ard_rec.tax_code_id,
1233 p_ard_rec.location_segment_id,
1234 p_ard_rec.source_type_secondary,
1235 p_ard_rec.tax_group_code_id,
1236 --{BUG#2979254
1237 p_ard_rec.ref_customer_trx_line_id,
1238 p_ard_rec.ref_cust_trx_line_gl_dist_id,
1239 p_ard_rec.ref_line_id
1240 --}
1241 from ar_transaction_history th,
1242 ar_payment_schedules pay,
1243 ar_distributions ard
1244 where
1245 th.transaction_history_id =
1246 (select max(th1.transaction_history_id)
1247 from ar_transaction_history th1
1248 where nvl(th1.POSTABLE_FLAG, 'N') = 'Y'
1249 and th1.status IN ('UNPAID', 'PENDING_REMITTANCE')
1250 and th1.customer_trx_id = p_adj_rec.customer_trx_id)
1251 and th.customer_trx_id = pay.customer_trx_id
1252 and ard.source_id = th.transaction_history_id
1253 and ard.source_table = 'TH'
1254 and (((sign(pay.amount_due_original) > 0)
1255 and ((nvl(ard.AMOUNT_DR,0) <> 0) OR
1256 (nvl(ard.ACCTD_AMOUNT_DR,0) <> 0))
1257 and (nvl(ard.AMOUNT_CR,0) = 0) and
1258 (nvl(ard.ACCTD_AMOUNT_CR,0) = 0))
1259 OR ((sign(pay.amount_due_original) < 0)
1260 and ((nvl(ard.AMOUNT_CR,0) <> 0) OR
1261 (nvl(ard.ACCTD_AMOUNT_CR,0) <> 0))
1262 and (nvl(ard.AMOUNT_DR,0) = 0) and
1263 (nvl(ard.ACCTD_AMOUNT_DR,0) = 0)));
1264 END IF;
1265
1266 p_ctlgd_inv_rec.code_combination_id := p_ard_rec.code_combination_id;
1267
1268 END IF;
1269
1270 /*--------------------------------------------------------+
1271 | Get non recoverable tax account for asset or liability |
1272 | tax code for finance charges |
1273 +--------------------------------------------------------*/
1274 IF (p_rule_rec.tax_code_source1 = 'ACTIVITY')
1275 AND (p_rule_rec.asset_tax_code1 IS NOT NULL)
1276 THEN
1277
1278 /* Initialize zx */
1279 zx_api_pub.set_tax_security_context(
1280 p_api_version => 1.0,
1281 p_init_msg_list => 'T',
1282 p_commit => 'F',
1283 p_validation_level => NULL,
1284 x_return_status => l_return_status,
1285 x_msg_count => l_msg_count,
1286 x_msg_data => l_msg_data,
1287 p_internal_org_id => arp_standard.sysparm.org_id,
1288 p_legal_entity_id => l_le_id,
1289 p_transaction_date => p_adj_rec.apply_date,
1290 p_related_doc_date => NULL,
1291 p_adjusted_doc_date=> NULL,
1292 x_effective_date => l_effective_date);
1293
1294 BEGIN
1295 /* Get tax_rate_id from zx_rates based on tax_rate_code (asset_tax_code)
1296 and then call function to get underlying accounts */
1297 SELECT tax_rate_id
1298 INTO l_tax_rate_id
1299 FROM zx_sco_rates
1300 WHERE tax_rate_code = p_rule_rec.asset_tax_code1
1301 AND p_adj_rec.apply_date BETWEEN
1302 NVL(effective_from, p_adj_rec.apply_date) AND
1303 NVL(effective_to, p_adj_rec.apply_date);
1304
1305 p_rule_rec.act_tax_non_rec_ccid1 :=
1306 arp_etax_util.get_tax_account(l_tax_rate_id,
1307 p_adj_rec.apply_date,
1308 'ADJ_NON_REC',
1309 'TAX_RATE');
1310
1311 p_rule_rec.act_vat_tax_id1 :=
1312 arp_etax_util.get_tax_account(l_tax_rate_id,
1313 p_adj_rec.apply_date,
1314 'FINCHRG_NON_REC',
1315 'TAX_RATE');
1316
1317 EXCEPTION
1318 WHEN NO_DATA_FOUND THEN
1319 IF PG_DEBUG in ('Y', 'C') THEN
1320 arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Get_Doc_Entitity_Data - ' ||
1321 'ACT_TAX_NON_REC_CCID - '|| 'NO_DATA_FOUND' );
1322 END IF;
1323 NULL;
1324
1325 WHEN OTHERS THEN
1326 IF PG_DEBUG in ('Y', 'C') THEN
1327 arp_standard.debug('EXCEPTION: ARP_ADJUSTMENTS_MAIN.'||
1328 'Get_Doc_Entitity_Data - ACT_TAX_NON_REC_CCID ');
1329 END IF;
1330 RAISE ;
1331 END;
1332
1333 END IF; --end if tax code source is activity
1334
1335 END IF; --end if p_level = ADJ
1336
1337 IF PG_DEBUG in ('Y', 'C') THEN
1338 arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Get_Doc_Entitity_Data()-');
1339 END IF;
1340
1341 EXCEPTION
1342 WHEN NO_DATA_FOUND THEN
1343 IF PG_DEBUG in ('Y', 'C') THEN
1344 arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Get_Doc_Entitity_Data - NO_DATA_FOUND' );
1345 END IF;
1346 RAISE;
1347
1348 WHEN OTHERS THEN
1349 IF PG_DEBUG in ('Y', 'C') THEN
1350 arp_standard.debug('EXCEPTION: ARP_ADJUSTMENTS_MAIN.Get_Doc_Entitity_Data');
1351 END IF;
1352 RAISE ;
1353
1354 END Get_Doc_Entitity_Data;
1355
1356 /* =======================================================================
1357 | PROCEDURE Create_Ae_Lines_Common
1358 |
1359 | DESCRIPTION
1360 | This procedure creates the AE lines at each entity level. Used
1361 | for creating lines as part of Adjustment creation.
1362 |
1363 | Functions:
1364 | - Create AE lines.
1365 | - Get additional data to determine the type of AE lines
1366 |
1367 | PARAMETERS
1368 | p_level Entity level from which the procedure was called
1369 * ======================================================================*/
1370 PROCEDURE Create_Ae_Lines_Common (
1371 p_level IN VARCHAR2,
1372 p_from_llca_call IN VARCHAR2 DEFAULT 'N',
1373 p_gt_id IN NUMBER DEFAULT NULL) IS
1374
1375 l_adj_rec ar_adjustments%ROWTYPE ;
1376 l_cust_inv_rec ra_customer_trx%ROWTYPE ;
1377 l_ctlgd_inv_rec ra_cust_trx_line_gl_dist%ROWTYPE ;
1378 l_rule_rec ae_rule_rec_type ;
1379 l_ard_rec ar_distributions%ROWTYPE ;
1380
1381 BEGIN
1382
1383 IF PG_DEBUG in ('Y', 'C') THEN
1384 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Create_Ae_Lines_Common()+');
1385 END IF;
1386
1387 /*-------------------------------------------------------------+
1388 | Get Document Entitity specific data |
1389 +-------------------------------------------------------------*/
1390
1391 Get_Doc_Entitity_Data(p_level => p_level ,
1392 p_adj_rec => l_adj_rec ,
1393 p_cust_inv_rec => l_cust_inv_rec ,
1394 p_ctlgd_inv_rec => l_ctlgd_inv_rec ,
1395 p_rule_rec => l_rule_rec ,
1396 p_ard_rec => l_ard_rec );
1397
1398 /*------------------------------------------------------+
1399 | Create AE Lines for Adjustment |
1400 +------------------------------------------------------*/
1401 IF p_level = 'ADJ' THEN -- Entity level = ar_adjustments
1402
1403 /*------------------------------------------------------+
1404 | Create AE Lines for Receivables |
1405 +------------------------------------------------------*/
1406 -- added parameter for Line Level Adjustment
1407 Create_Ae_Lines_ADJ(p_adj_rec => l_adj_rec ,
1408 p_cust_inv_rec => l_cust_inv_rec ,
1409 p_ctlgd_inv_rec => l_ctlgd_inv_rec ,
1410 p_rule_rec => l_rule_rec ,
1411 p_ard_rec => l_ard_rec ,
1412 p_from_llca_call => p_from_llca_call ,
1413 p_gt_id => p_gt_id);
1414
1415 END IF;
1416
1417 IF PG_DEBUG in ('Y', 'C') THEN
1418 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Create_Ae_Lines_Common()-');
1419 END IF;
1420
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 IF PG_DEBUG in ('Y', 'C') THEN
1424 arp_standard.debug('EXCEPTION: ARP_ADJUSTMENTS_MAIN.Create_Ae_Lines_Common');
1425 END IF;
1426 RAISE;
1427
1428 END Create_Ae_Lines_Common;
1429
1430 /* =======================================================================
1431 | PROCEDURE Create_Ae_Lines_ADJ
1432 |
1433 | DESCRIPTION
1434 | This procedure creates the AE lines for Receivables
1435 |
1436 | Functions:
1437 | - Create AE lines for Receivable .
1438 | - Determines Amounts Dr/Cr.
1439 |
1440 | PARAMETERS
1441 | p_adj_rec Adjustments Record
1442 | p_cust_inv_rec Invoice header Record
1443 | p_ctlgd_inv_rec REC account ccid record
1444 | p_rule_rec Rule record
1445 | p_ard_rec Distributions record used by Bills Receivable
1446 * ======================================================================*/
1447 PROCEDURE Create_Ae_Lines_ADJ(
1448 p_adj_rec IN ar_adjustments%ROWTYPE ,
1449 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
1450 p_ctlgd_inv_rec IN ra_cust_trx_line_gl_dist%ROWTYPE ,
1451 p_rule_rec IN ae_rule_rec_type ,
1452 p_ard_rec IN ar_distributions%ROWTYPE ,
1453 p_from_llca_call IN VARCHAR2 DEFAULT 'N' ,
1454 p_gt_id IN NUMBER DEFAULT NULL) IS
1455
1456 l_ael_line_rec ae_line_rec_type;
1457 l_empty_ael_line_rec ae_line_rec_type;
1458 l_app_rec ar_receivable_applications%ROWTYPE;
1459 l_ae_line_tbl ae_line_tbl_type;
1460 l_ae_ctr BINARY_INTEGER := 0;
1461 l_ctr BINARY_INTEGER;
1462 l_actual_ccid ar_adjustments.code_combination_id%TYPE := null;
1463
1464 BEGIN
1465 IF PG_DEBUG in ('Y', 'C') THEN
1466 arp_standard.debug( 'Create_Ae_Lines_ADJ.Create_Ae_Lines_ADJ()+');
1467 END IF;
1468
1469 /*-------------------------------------------------------------+
1470 | Get exchange rate and third part info from Invoice adjusted |
1471 | for building receivable REC |
1472 +-------------------------------------------------------------*/
1473 l_ael_line_rec.source_id := p_adj_rec.adjustment_id;
1474 l_ael_line_rec.source_table := 'ADJ';
1475
1476 /*-------------------------------------------------------------+
1477 | Set the source tyoe to the previous accounting record which |
1478 | needs to be offset for the Bills Receivable. This could be |
1479 | the Bills Receivable or Unpaid Bills Receivable account. |
1480 +-------------------------------------------------------------*/
1481 IF p_cust_inv_rec.drawee_site_use_id IS NOT NULL THEN
1482 l_ael_line_rec.ae_line_type := p_ard_rec.source_type;
1483 ELSE
1484 l_ael_line_rec.ae_line_type := 'REC';
1485 END IF;
1486
1487 l_ael_line_rec.ae_line_type_secondary := '';
1488 l_ael_line_rec.account := p_ctlgd_inv_rec.code_combination_id; --ccid for REC
1489 l_ael_line_rec.currency_code := p_cust_inv_rec.invoice_currency_code;
1490 l_ael_line_rec.currency_conversion_rate := p_cust_inv_rec.exchange_rate;
1491 l_ael_line_rec.currency_conversion_type := p_cust_inv_rec.exchange_rate_type;
1492 l_ael_line_rec.currency_conversion_date := p_cust_inv_rec.exchange_date;
1493
1494 IF p_cust_inv_rec.drawee_site_use_id IS NOT NULL THEN --if Bill
1495 l_ael_line_rec.third_party_id := p_cust_inv_rec.drawee_id;
1496 l_ael_line_rec.third_party_sub_id := p_cust_inv_rec.drawee_site_use_id;
1497 ELSE
1498 l_ael_line_rec.third_party_id := p_cust_inv_rec.bill_to_customer_id;
1499 l_ael_line_rec.third_party_sub_id := p_cust_inv_rec.bill_to_site_use_id;
1500 END IF; --Bill
1501
1502 /*--------------------------------------------------------------------------------+
1503 | Derive Receivable amounts, for Adjustments. |
1504 +--------------------------------------------------------------------------------*/
1505
1506 IF ( sign( p_adj_rec.amount ) = -1 ) THEN -- Credit Receivables for INV
1507
1508 l_ael_line_rec.entered_cr := abs(p_adj_rec.amount);
1509 l_ael_line_rec.entered_dr := NULL;
1510
1511 l_ael_line_rec.accounted_cr := abs(p_adj_rec.acctd_amount);
1512 l_ael_line_rec.accounted_dr := NULL;
1513
1514 ELSE -- Debit Receivables for INV, if amount adjusted is 0 then accounting record created
1515
1516 l_ael_line_rec.entered_dr := p_adj_rec.amount;
1517 l_ael_line_rec.entered_cr := NULL;
1518
1519 l_ael_line_rec.accounted_dr := p_adj_rec.acctd_amount;
1520 l_ael_line_rec.accounted_cr := NULL;
1521
1522 END IF;
1523
1524 /*-------------------------------------------------------------+
1525 | Assign AEL for REC for Invoice document for adjustment |
1526 +-------------------------------------------------------------*/
1527 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
1528
1529 /*---------------------------------------------------------------------------+
1530 | Call tax accounting routine for Adjustments if amount adjusted is non zero|
1531 +---------------------------------------------------------------------------*/
1532
1533 IF (nvl(p_adj_rec.amount,0) <> 0) THEN
1534
1535 l_ae_line_tbl := g_empty_ae_line_tbl;
1536 l_ae_ctr := 0;
1537
1538 /*---------------------------------------------------------------------------+
1539 | Verify whether invalid rule setup has occurred at the Receivable Activity |
1540 | in this case raise an error stating that the rule be set up correctly. |
1541 +---------------------------------------------------------------------------*/
1542 IF (((p_rule_rec.gl_account_source1 = 'NO_SOURCE') OR (p_rule_rec.tax_code_source1 = 'NO_SOURCE'))
1543 OR ((p_rule_rec.tax_code_source1 = 'INVOICE')
1544 AND (nvl(p_rule_rec.tax_recoverable_flag1, 'X') NOT IN ('Y','N'))))
1545 THEN
1546
1547 RAISE ARP_ALLOCATION_PKG.invalid_allocation_base;
1548
1549 END IF;
1550
1551 /*---------------------------------------------------------------------------+
1552 | The deferred tax flag is set by the Bills Receivable Houskeeper. When this|
1553 | is No, it means that there is no Tax accounting impact, as the maturity |
1554 | date event would have moved the deferred tax. For Transactions and where |
1555 | the Maturity date event merges with the creation of Receipt application, |
1556 | the Tax accounting Wrapper routine is called. For Transactions the normal |
1557 | Tax accounting routine is called. Note for Bills Receivable we only move |
1558 | deferred tax as there is no discount. |
1559 +---------------------------------------------------------------------------*/
1560 IF PG_DEBUG in ('Y', 'C') THEN
1561 arp_standard.debug('g_ae_doc_rec.deferred_tax ' || g_ae_doc_rec.deferred_tax);
1562 arp_standard.debug('p_cust_inv_rec.drawee_site_use_id '
1563 || p_cust_inv_rec.drawee_site_use_id);
1564 END IF;
1565
1566 IF (nvl(g_ae_doc_rec.deferred_tax,'Y') = 'Y') THEN
1567
1568 IF p_cust_inv_rec.drawee_site_use_id IS NULL THEN --check whether adjustment to bill
1569
1570 -- Added parameter for Line level Adjustment
1571 ARP_ALLOCATION_PKG.Allocate_Tax(
1572 p_ae_doc_rec => g_ae_doc_rec , --Document detail
1573 p_ae_event_rec => g_ae_event_rec , --Event record
1574 p_ae_rule_rec => p_rule_rec , --Rule info for payment method
1575 p_app_rec => l_app_rec , --dummy applications record
1576 p_cust_inv_rec => p_cust_inv_rec , --Invoice details
1577 p_adj_rec => p_adj_rec , --adjustment details
1578 p_ae_ctr => l_ae_ctr , --counter
1579 p_ae_line_tbl => l_ae_line_tbl , --final tax accounting table
1580 p_from_llca_call => p_from_llca_call , --Line Adj detail
1581 p_gt_id => p_gt_id);
1582 ELSE /*---------------------------------------------------------------------+
1583 | This condition occurs for endorsments without recourse only since |
1584 | adjustment is gross to activity, hence the Wrapper is called to |
1585 | move deferred tax only. The parent adjustment must be of type |
1586 | INVOICE. The wrapper breaks the adjustment into sub adjustments |
1587 | for each assignment. This is necessary to enable accurate reporting |
1588 | of taxable amount for deferred tax using link ids. So in this case |
1589 | we cannot simply create one accounting entry for gross to activity |
1590 | gl account. |
1591 +---------------------------------------------------------------------*/
1592 ARP_BR_ALLOC_WRAPPER_PKG.Allocate_Tax_BR_Main(
1593 p_mode => g_mode , --Mode
1594 p_ae_doc_rec => g_ae_doc_rec , --Document detail
1595 p_ae_event_rec => g_ae_event_rec , --Event record
1596 p_ae_rule_rec => p_rule_rec , --Rule info for payment method
1597 p_app_rec => l_app_rec , --Application details
1598 p_cust_inv_rec => p_cust_inv_rec , --Invoice details
1599 p_adj_rec => p_adj_rec , --adjustment record
1600 p_ae_sys_rec => g_ae_sys_rec , --system parameters
1601 p_ae_ctr => l_ae_ctr , --counter
1602 p_ae_line_tbl => l_ae_line_tbl ); --final tax accounting table
1603
1604 END IF; --adjustment to Bill or Transaction
1605 ELSE --adjustments with recourse (when Maturity date not same as risk eliminate event
1606 /*-----------------------------------------------------------------------+
1607 | Do not call the Tax accounting to move deferred tax this happens when |
1608 | the Bills Receivable Housekeeper determines that the maturity date |
1609 | event is seperate from the creation of the adjustment, for approved |
1610 | Endorsments, we need to update the link id so the last Transaction |
1611 | History Record must be for Matured Pending Risk elimination Endorsment|
1612 +-----------------------------------------------------------------------*/
1613 update ar_adjustments
1614 set link_to_trx_hist_id = (select max(th.transaction_history_id)
1615 from ar_transaction_history th
1616 where th.customer_trx_id = p_adj_rec.customer_trx_id
1617 and th.event = 'MATURITY_DATE'
1618 and exists (select 'x'
1619 from ar_distributions ard
1620 where ard.source_id = th.transaction_history_id
1621 and ard.source_table = 'TH'))
1622 where adjustment_id = p_adj_rec.adjustment_id;
1623
1624 /*-----------------------------------------------------------------------+
1625 | Create the Release 11 Writeoff to the Activity GL Account. Note that |
1626 | the deferred tax would already have been moved at the maturity date |
1627 | event, so we do not call the Tax accounting engine as the only |
1628 | accounting allowed is Gross to Activity Gl Account i.e. GL ACCOUNT |
1629 | SOURCE = 'ACTIVITY GL ACCOUNT', TAX CODE SOURCE = 'NONE', in this case|
1630 | a single accounting entry is created.
1631 +-----------------------------------------------------------------------*/
1632 IF p_cust_inv_rec.drawee_site_use_id IS NOT NULL THEN
1633
1634 l_ael_line_rec.ae_line_type := 'ADJ';
1635 l_ael_line_rec.account := p_rule_rec.code_combination_id1; --ccid for writeoff
1636
1637 IF ( sign( p_adj_rec.amount ) = -1 ) THEN -- Dr the Write-off account
1638
1639 l_ael_line_rec.entered_cr := NULL;
1640 l_ael_line_rec.entered_dr := abs(p_adj_rec.amount);
1641
1642 l_ael_line_rec.accounted_cr := NULL;
1643 l_ael_line_rec.accounted_dr := abs(p_adj_rec.acctd_amount);
1644
1645 ELSE -- Cr the Write-off account
1646
1647 l_ael_line_rec.entered_dr := NULL;
1648 l_ael_line_rec.entered_cr := p_adj_rec.amount;
1649
1650 l_ael_line_rec.accounted_dr := NULL;
1651 l_ael_line_rec.accounted_cr := p_adj_rec.acctd_amount;
1652
1653 END IF;
1654
1655 /*-------------------------------------------------------------+
1656 | Assign AEL for ADJ Writeoff for adjustment |
1657 +-------------------------------------------------------------*/
1658 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
1659
1660 END IF; --End if document is a bills receivable
1661
1662 END IF; --End if deferred tax required to be moved
1663
1664 /*-------------------------------------------------------------+
1665 | Assign AEL for Tax accounting |
1666 +-------------------------------------------------------------*/
1667 IF l_ae_line_tbl.EXISTS(l_ae_ctr) THEN --Atleast one Tax line exists
1668
1669 FOR l_ctr IN l_ae_line_tbl.FIRST .. l_ae_line_tbl.LAST LOOP
1670
1671 --It is necessary to populate the record and then call assign elements
1672 --because of standards and that the User Hook could override accounting
1673 --so need to populate this record (rather than direct table assignments)
1674
1675 l_ael_line_rec := l_empty_ael_line_rec;
1676 l_ael_line_rec := l_ae_line_tbl(l_ctr);
1677
1678 /*--------------------------------------------------------------+
1679 | Asign AEL for tax accounting for adjustments, finance charges|
1680 +--------------------------------------------------------------*/
1681 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
1682
1683 END LOOP; --process tax lines
1684
1685 END IF; --atleast one tax line exists
1686
1687 ELSE --if adjustment is a zero amount adjustment then Credit the Write off account
1688
1689 IF PG_DEBUG in ('Y', 'C') THEN
1690 arp_standard.debug('Processing Credit to Write Off for 0 amount adjustment ');
1691 END IF;
1692
1693 --Substitute the balancing segment
1694 --Bugfix 1948917.
1695 IF NVL(FND_PROFILE.value('AR_DISABLE_REC_ACTIVITY_BALSEG_SUBSTITUTION'), 'N') = 'N' THEN
1696 ARP_ALLOCATION_PKG.Substitute_Ccid(
1697 p_coa_id => g_ae_sys_rec.coa_id ,
1698 p_original_ccid => p_rule_rec.code_combination_id1,
1699 p_subs_ccid => l_ael_line_rec.account , --Rec account
1700 p_actual_ccid => l_actual_ccid );
1701 ELSE
1702 l_actual_ccid := p_rule_rec.code_combination_id1;
1703 END IF;
1704
1705 --Set the actual account
1706 l_ael_line_rec.account := l_actual_ccid;
1707
1708 --Set the source type
1709 IF g_ae_doc_rec.source_table = 'ADJ' and g_ae_doc_rec.document_type = 'ADJUSTMENT' THEN
1710
1711 l_ael_line_rec.ae_line_type := 'ADJ';
1712
1713 /*----------------------------------------------------------------------------+
1714 | Populate source type for finance charges |
1715 +----------------------------------------------------------------------------*/
1716 ELSIF g_ae_doc_rec.source_table = 'ADJ' and g_ae_doc_rec.document_type = 'FINANCE_CHARGES' THEN
1717
1718 l_ael_line_rec.ae_line_type := 'FINCHRG';
1719
1720 END IF;
1721
1722 --Set the amounts and accounted amounts
1723 l_ael_line_rec.entered_cr := abs(p_adj_rec.amount);
1724 l_ael_line_rec.entered_dr := NULL;
1725
1726 l_ael_line_rec.accounted_cr := abs(p_adj_rec.acctd_amount);
1727 l_ael_line_rec.accounted_dr := NULL;
1728
1729 /*-------------------------------------------------------------+
1730 | Assign AEL for REC for Invoice document for adjustment |
1731 +-------------------------------------------------------------*/
1732 Assign_Ael_Elements( p_ae_line_rec => l_ael_line_rec );
1733
1734 END IF; --adjustment amount is non zero
1735
1736 /*------------------------------------------------------------------------------------+
1737 |Call the Reconciliation routine, this is necessary because the transaction or Bill |
1738 |which may have been overapplied is now closed due to adjustment or overapplied |
1739 |may have resulted in re-opening the transaction, hence we need to back out NOCOPY the old |
1740 |reconciliation entries for the Bill or the Transactions |
1741 +------------------------------------------------------------------------------------*/
1742 ARP_RECONCILE.Reconcile_trx_br(
1743 p_mode => g_mode ,
1744 p_ae_doc_rec => g_ae_doc_rec ,
1745 p_ae_event_rec => g_ae_event_rec ,
1746 p_cust_inv_rec => p_cust_inv_rec ,
1747 p_activity_cust_trx_id => p_adj_rec.customer_trx_id ,
1748 p_activity_amt => p_adj_rec.amount ,
1749 p_activity_acctd_amt => p_adj_rec.acctd_amount ,
1750 p_call_num => 1 ,
1751 p_g_ae_line_tbl => g_ae_line_tbl ,
1752 p_g_ae_ctr => g_ae_line_ctr );
1753
1754 IF PG_DEBUG in ('Y', 'C') THEN
1755 arp_standard.debug('Create_Ae_Lines_ADJ.Create_Ae_Lines_ADJ()-');
1756 END IF;
1757
1758 EXCEPTION
1759 WHEN ARP_ALLOCATION_PKG.invalid_allocation_base THEN
1760 IF PG_DEBUG in ('Y', 'C') THEN
1761 arp_standard.debug('Create_Ae_Lines_ADJ.Create_Ae_Lines_ADJ - invalid_rule_error');
1762 END IF;
1763 fnd_message.set_name('AR','AR_INVALID_ACTIVITY');
1764 RAISE;
1765
1766 WHEN NO_DATA_FOUND THEN
1767 IF PG_DEBUG in ('Y', 'C') THEN
1768 arp_standard.debug('NO_DATA_FOUND' );
1769 END IF;
1770 RAISE;
1771
1772 WHEN OTHERS THEN
1773 IF PG_DEBUG in ('Y', 'C') THEN
1774 arp_standard.debug('EXCEPTION: Create_Ae_Lines_ADJ.Create_Ae_Lines_ADJ');
1775 END IF;
1776 RAISE;
1777
1778 END Create_Ae_Lines_ADJ;
1779
1780 /* =======================================================================
1781 | PROCEDURE Assign_Ael_Elements
1782 |
1783 | DESCRIPTION
1784 | This procedure stores the AE Line record into AE Lines PLSQL table.
1785 | Functions:
1786 | - Determine regular or negative Dr/Cr.
1787 | - Store AE Line Record in AE Lines PLSQL Table.
1788 | - In a fully implemented SLA model, Will determine the
1789 | account to use based on AE Line type and other parameters.
1790 | - In a fully implemented SLA model, Will determine the
1791 | account descriptions.
1792 |
1793 | GUIDELINE
1794 | - This procedure can be shared across document types
1795 | - Recommendation is to have one per document type(AE Derivation)
1796 |
1797 | PARAMETERS
1798 | p_ae_line_rec AE Line Record
1799 * ======================================================================*/
1800 PROCEDURE Assign_Ael_Elements(
1801 p_ae_line_rec IN ae_line_rec_type ) IS
1802
1803 l_account NUMBER;
1804 l_account_valid BOOLEAN;
1805 l_replace_default_account BOOLEAN;
1806
1807 BEGIN
1808
1809 IF PG_DEBUG in ('Y', 'C') THEN
1810 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Assign_Ael_Elements()+');
1811 END IF;
1812
1813 /*------------------------------------------------------+
1814 | Call Hook to Override Account |
1815 +------------------------------------------------------*/
1816 ARP_ACCT_HOOK.Override_Account(
1817 p_mode => g_mode,
1818 p_ae_doc_rec => g_ae_doc_rec,
1819 p_ae_event_rec => g_ae_event_rec,
1820 p_ae_line_rec => p_ae_line_rec,
1821 p_account => l_account,
1822 p_account_valid => l_account_valid,
1823 p_replace_default_account => l_replace_default_account
1824 );
1825
1826 IF ( NOT l_replace_default_account ) THEN
1827
1828 /*------------------------------------------------------+
1829 | SLA : Build Account for AE Line Type |
1830 | When SLA is fully implemented Account Builder |
1831 | will be called from here. |
1832 +------------------------------------------------------*/
1833 l_account := p_ae_line_rec.account;
1834
1835 END IF; -- Replace default account?
1836
1837 /*------------------------------------------------------+
1838 | SLA : Build Account description for AE Line Type |
1839 | When SLA is fully implemented Description |
1840 | builder will be called from here. |
1841 +------------------------------------------------------*/
1842
1843 /*------------------------------------------------------+
1844 | SLA : Check Negative Dr/Cr for AE Line |
1845 | When SLA is fully implemented. |
1846 +------------------------------------------------------*/
1847
1848 /*------------------------------------------------------+
1849 | Store AE Line elements in AE Lines temp table |
1850 +------------------------------------------------------*/
1851 g_ae_line_ctr := g_ae_line_ctr +1;
1852
1853 g_ae_line_tbl(g_ae_line_ctr).ae_line_type := p_ae_line_rec.ae_line_type;
1854 g_ae_line_tbl(g_ae_line_ctr).ae_line_type_secondary := p_ae_line_rec.ae_line_type_secondary;
1855 g_ae_line_tbl(g_ae_line_ctr).source_id := p_ae_line_rec.source_id;
1856 g_ae_line_tbl(g_ae_line_ctr).source_table := p_ae_line_rec.source_table;
1857 g_ae_line_tbl(g_ae_line_ctr).account := p_ae_line_rec.account;
1858 g_ae_line_tbl(g_ae_line_ctr).entered_dr := p_ae_line_rec.entered_dr;
1859 g_ae_line_tbl(g_ae_line_ctr).entered_cr := p_ae_line_rec.entered_cr;
1860 g_ae_line_tbl(g_ae_line_ctr).accounted_dr := p_ae_line_rec.accounted_dr;
1861 g_ae_line_tbl(g_ae_line_ctr).accounted_cr := p_ae_line_rec.accounted_cr;
1862 g_ae_line_tbl(g_ae_line_ctr).source_id_secondary := p_ae_line_rec.source_id_secondary;
1863 g_ae_line_tbl(g_ae_line_ctr).source_table_secondary := p_ae_line_rec.source_table_secondary;
1864 g_ae_line_tbl(g_ae_line_ctr).currency_code := p_ae_line_rec.currency_code;
1865 g_ae_line_tbl(g_ae_line_ctr).currency_conversion_rate := p_ae_line_rec.currency_conversion_rate;
1866 g_ae_line_tbl(g_ae_line_ctr).currency_conversion_type := p_ae_line_rec.currency_conversion_type;
1867 g_ae_line_tbl(g_ae_line_ctr).currency_conversion_date := p_ae_line_rec.currency_conversion_date;
1868 g_ae_line_tbl(g_ae_line_ctr).third_party_id := p_ae_line_rec.third_party_id;
1869 g_ae_line_tbl(g_ae_line_ctr).third_party_sub_id := p_ae_line_rec.third_party_sub_id;
1870 g_ae_line_tbl(g_ae_line_ctr).tax_group_code_id := p_ae_line_rec.tax_group_code_id;
1871 g_ae_line_tbl(g_ae_line_ctr).tax_code_id := p_ae_line_rec.tax_code_id;
1872 g_ae_line_tbl(g_ae_line_ctr).location_segment_id := p_ae_line_rec.location_segment_id;
1873 g_ae_line_tbl(g_ae_line_ctr).taxable_entered_dr := p_ae_line_rec.taxable_entered_dr;
1874 g_ae_line_tbl(g_ae_line_ctr).taxable_entered_cr := p_ae_line_rec.taxable_entered_cr;
1875 g_ae_line_tbl(g_ae_line_ctr).taxable_accounted_dr := p_ae_line_rec.taxable_accounted_dr;
1876 g_ae_line_tbl(g_ae_line_ctr).taxable_accounted_cr := p_ae_line_rec.taxable_accounted_cr;
1877 g_ae_line_tbl(g_ae_line_ctr).applied_from_doc_table := p_ae_line_rec.applied_from_doc_table;
1878 g_ae_line_tbl(g_ae_line_ctr).applied_from_doc_id := p_ae_line_rec.applied_from_doc_id;
1879 g_ae_line_tbl(g_ae_line_ctr).applied_to_doc_table := p_ae_line_rec.applied_to_doc_table;
1880 g_ae_line_tbl(g_ae_line_ctr).applied_to_doc_id := p_ae_line_rec.applied_to_doc_id;
1881 g_ae_line_tbl(g_ae_line_ctr).tax_link_id := p_ae_line_rec.tax_link_id;
1882 g_ae_line_tbl(g_ae_line_ctr).reversed_source_id := p_ae_line_rec.reversed_source_id;
1883 --{3377004
1884 g_ae_line_tbl(g_ae_line_ctr).ref_line_id := p_ae_line_rec.ref_line_id;
1885 g_ae_line_tbl(g_ae_line_ctr).ref_customer_trx_line_id := p_ae_line_rec.ref_customer_trx_line_id;
1886 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;
1887 --}
1888 g_ae_line_tbl(g_ae_line_ctr).ref_account_class := p_ae_line_rec.ref_account_class;
1889 g_ae_line_tbl(g_ae_line_ctr).activity_bucket := p_ae_line_rec.activity_bucket;
1890 g_ae_line_tbl(g_ae_line_ctr).ref_dist_ccid := p_ae_line_rec.ref_dist_ccid;
1891
1892 IF PG_DEBUG in ('Y', 'C') THEN
1893 arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Assign_Ael_Elements()-');
1894 END IF;
1895
1896 EXCEPTION
1897 WHEN OTHERS THEN
1898 IF PG_DEBUG in ('Y', 'C') THEN
1899 arp_standard.debug('EXCEPTION: ARP_ADJUSTMENTS_MAIN.Assign_Ael_Elements');
1900 END IF;
1901 RAISE;
1902
1903 END Assign_Ael_Elements;
1904
1905 --{3377004
1906 FUNCTION ctl_id_index(p_ctl_id_tab IN DBMS_SQL.NUMBER_TABLE,
1907 p_ctl_id IN NUMBER)
1908 RETURN NUMBER
1909 IS
1910 result NUMBER;
1911 cnt NUMBER := 0;
1912 BEGIN
1913 arp_standard.debug('ctl_id_index+');
1914 result := -1;
1915 cnt := p_ctl_id_tab.COUNT;
1916 IF cnt > 0 THEN
1917 FOR i IN p_ctl_id_tab.FIRST .. p_ctl_id_tab.LAST LOOP
1918 IF p_ctl_id_tab(i) = p_ctl_id THEN
1919 result := i;
1920 EXIT;
1921 END IF;
1922 END LOOP;
1923 ELSE
1924 result := 1;
1925 END IF;
1926 IF result = -1 THEN
1927 result := cnt + 1;
1928 END IF;
1929 arp_standard.debug(' result index:'||result);
1930 arp_standard.debug('ctl_id_index-');
1931 RETURN result;
1932 END;
1933
1934 PROCEDURE init_rem_amt(x_rem_amt IN OUT NOCOPY ctl_rem_amt_type,
1935 p_index IN NUMBER)
1936 IS
1937 BEGIN
1938 x_rem_amt.customer_trx_line_id(p_index) := 0;
1939 x_rem_amt.amount_due_remaining(p_index) := 0;
1940 x_rem_amt.acctd_amount_due_remaining(p_index) := 0;
1941 x_rem_amt.chrg_amount_remaining(p_index) := 0;
1942 x_rem_amt.chrg_acctd_amount_remaining(p_index) := 0;
1943 END;
1944 --}
1945
1946 END ARP_ADJUSTMENTS_MAIN;