1 PACKAGE BODY ARP_RECONCILE AS
2 /* $Header: ARTRECBB.pls 120.18 2011/05/30 12:12:35 kyennawa ship $ */
3
4 /*=======================================================================+
5 | Global Constants
6 +=======================================================================*/
7
8
9 TYPE g_tax_rec_type IS RECORD (
10 ae_location_segment_id NUMBER,
11 ae_tax_group_code_id NUMBER,
12 ae_tax_code_id NUMBER,
13 ae_code_combination_id NUMBER,
14 ae_amount NUMBER,
15 ae_acctd_amount NUMBER,
16 ae_taxable_amount NUMBER,
17 ae_taxable_acctd_amount NUMBER,
18 ae_match_flag VARCHAR2(1)
19 );
20
21 TYPE g_tax_tbl_type IS TABLE of g_tax_rec_type
22 INDEX BY BINARY_INTEGER;
23
24 TYPE g_pay_rec_type IS RECORD (
25 applied_customer_trx_id NUMBER,
26 applied_payment_schedule_id NUMBER,
27 amount_applied NUMBER,
28 acctd_amount_applied_to NUMBER,
29 line_applied NUMBER,
30 tax_applied NUMBER,
31 freight_applied NUMBER,
32 receivables_charges_applied NUMBER
33 );
34
35 TYPE g_pay_tbl_type IS TABLE of g_pay_rec_type
36 INDEX BY BINARY_INTEGER;
37
38 g_ae_empty_line_tbl ae_line_tbl_type;
39 g_orig_cust_trx_id NUMBER;
40 g_call_num NUMBER;
41
42 /*============================================================================+
43 | Private Procedure/Function prototypes |
44 +============================================================================*/
45 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
46
47 PROCEDURE Check_Entry(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
48 p_type IN VARCHAR2,
49 p_required OUT NOCOPY BOOLEAN );
50
51 PROCEDURE Check_all_bills_closed(p_customer_trx_id IN NUMBER ,
52 p_all_br_closed IN OUT NOCOPY VARCHAR2 );
53
54 PROCEDURE Reverse_Reconcile_entry(
55 p_mode IN VARCHAR2,
56 p_ae_doc_rec IN ae_doc_rec_type,
57 p_ae_event_rec IN ae_event_rec_type,
58 p_ae_sys_rec IN ae_sys_rec_type,
59 p_customer_trx_id IN NUMBER,
60 p_calling_point IN VARCHAR2,
61 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER,
62 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type );
63
64 PROCEDURE get_recon_acct(
65 p_mode IN VARCHAR2,
66 p_ae_doc_rec IN ae_doc_rec_type,
67 p_ae_event_rec IN ae_event_rec_type,
68 p_ae_sys_rec IN ae_sys_rec_type,
69 p_customer_trx_id IN NUMBER,
70 p_customer_trx_line_id IN NUMBER,
71 p_calling_point IN VARCHAR2,
72 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER,
73 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type );
74
75 PROCEDURE Reconcile_br_tax(
76 p_mode IN VARCHAR2,
77 p_ae_doc_rec IN ae_doc_rec_type,
78 p_ae_event_rec IN ae_event_rec_type,
79 p_ae_sys_rec IN ae_sys_rec_type,
80 p_customer_trx_id IN NUMBER,
81 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER,
82 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type );
83
84 PROCEDURE Reconcile_trx_tax(
85 p_mode IN VARCHAR2 ,
86 p_ae_doc_rec IN ae_doc_rec_type ,
87 p_ae_event_rec IN ae_event_rec_type ,
88 p_ae_sys_rec IN ae_sys_rec_type ,
89 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
90 p_customer_trx_id IN NUMBER ,
91 p_br_cust_trx_line_id IN NUMBER ,
92 p_calling_point IN VARCHAR2 ,
93 p_pay_class IN VARCHAR2 ,
94 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
95 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type );
96
97 PROCEDURE Detect_Closure(p_customer_trx_id IN NUMBER ,
98 p_pay_sched_upd_yn IN VARCHAR2 ,
99 p_pay_sched_upd_cm_yn IN VARCHAR2 ,
100 p_activity_amt IN NUMBER ,
101 p_activity_acctd_amt IN NUMBER ,
102 p_ae_sys_rec IN ae_sys_rec_type,
103 p_closed_pymt_yn OUT NOCOPY VARCHAR2 ,
104 p_pay_class OUT NOCOPY VARCHAR2 );
105
106 PROCEDURE Process_Recon(
107 p_mode IN VARCHAR2 ,
108 p_ae_doc_rec IN ae_doc_rec_type ,
109 p_ae_event_rec IN ae_event_rec_type ,
110 p_ae_sys_rec IN ae_sys_rec_type ,
111 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
112 p_br_cust_trx_line_id IN NUMBER ,
113 p_customer_trx_id IN NUMBER ,
114 p_simul_app IN VARCHAR2 ,
115 p_calling_point IN VARCHAR2 ,
116 p_pay_ctr IN BINARY_INTEGER ,
117 p_pay_tbl IN g_pay_tbl_type ,
118 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
119 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type );
120
121 PROCEDURE Assign_Elements(p_ae_line_rec IN OUT NOCOPY ae_line_rec_type ,
122 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
123 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type );
124
125 PROCEDURE Dump_Line_Amts(p_ae_line_rec IN ae_line_rec_type);
126
127 PROCEDURE Build_Deferred_Tax (p_customer_trx_id IN NUMBER,
128 p_br_cust_trx_line_id IN NUMBER,
129 p_location_segment_id IN NUMBER,
130 p_tax_group_code_id IN NUMBER,
131 p_tax_code_id IN NUMBER,
132 p_code_combination_id IN NUMBER,
133 p_ae_doc_rec IN ae_doc_rec_type,
134 p_cust_inv_rec IN ra_customer_trx%ROWTYPE,
135 p_calling_point IN VARCHAR2,
136 p_ae_line_rec IN OUT NOCOPY ae_line_rec_type);
137
138 PROCEDURE Build_Tax (p_customer_trx_id IN NUMBER,
139 p_location_segment_id IN NUMBER,
140 p_tax_group_code_id IN NUMBER,
141 p_tax_code_id IN NUMBER,
142 p_code_combination_id IN NUMBER,
143 p_ae_line_rec IN OUT NOCOPY ae_line_rec_type );
144
145 /*========================================================================
146 | PUBLIC PROCEDURE Reconcile_trx_br
147 |
148 | DESCRIPTION
149 | Reconciles deferred tax for a Transaction or a Bills Receivable
150 | document, as the case may be.
151 |
152 | PARAMETERS
153 | p_mode IN Document or Accounting Event mode
154 | p_ae_doc_rec IN Document Record
155 | p_ae_event_rec IN Event Record
156 | p_cust_inv_rec IN Contains currency, exchange rate, site
157 | details for the bill
158 | p_activity_cust_trx_id IN Transaction to which the activity was made
159 | p_activity_amt IN Amount by which the Open Receivables was
160 | changed due to activity.
161 | p_activity_acctd_amt IN Accounted amount by which the Open
162 | Receivables was changed due to activity.
163 | p_g_ae_line_tbl IN OUT NOCOPY Global accounting entries line table
164 | passed by parent routine
165 | p_g_ae_ctr IN OUT NOCOPY Global counter for accounting entries
166 | table passed by parent routine
167 *===========================================================================*/
168
169 PROCEDURE Reconcile_trx_br(
170 p_mode IN VARCHAR2,
171 p_ae_doc_rec IN ae_doc_rec_type,
172 p_ae_event_rec IN ae_event_rec_type,
173 p_cust_inv_rec IN ra_customer_trx%ROWTYPE,
174 p_activity_cust_trx_id IN NUMBER,
175 p_activity_amt IN NUMBER,
176 p_activity_acctd_amt IN NUMBER,
177 p_call_num IN NUMBER,
178 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type,
179 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ) IS
180
181 l_ae_sys_rec ae_sys_rec_type;
182 l_closed_pymt_yn VARCHAR2(1);
183 l_calling_point VARCHAR2(4);
184 l_pay_class ar_payment_schedules.class%TYPE;
185 l_required BOOLEAN;
186
187 BEGIN
188
189 IF PG_DEBUG in ('Y', 'C') THEN
190 arp_standard.debug('Check_Entry: ' || 'ARP_RECONCILE.Reconcile_trx_br ()+ ');
191 END IF;
192
193 --Set global variable for document being reconciled
194 g_orig_cust_trx_id := p_activity_cust_trx_id;
195 g_call_num := p_call_num;
196
197
198 --GOTO end_process_lbl;
199 --Get system options info
200 l_ae_sys_rec.set_of_books_id := ARP_ACCT_MAIN.ae_sys_rec.set_of_books_id;
201 l_ae_sys_rec.gain_cc_id := ARP_ACCT_MAIN.ae_sys_rec.gain_cc_id;
202 l_ae_sys_rec.loss_cc_id := ARP_ACCT_MAIN.ae_sys_rec.loss_cc_id;
203 l_ae_sys_rec.round_cc_id := ARP_ACCT_MAIN.ae_sys_rec.round_cc_id;
204 l_ae_sys_rec.coa_id := ARP_ACCT_MAIN.ae_sys_rec.coa_id;
205 l_ae_sys_rec.base_currency := ARP_ACCT_MAIN.ae_sys_rec.base_currency;
206 l_ae_sys_rec.base_precision := ARP_ACCT_MAIN.ae_sys_rec.base_precision;
207 l_ae_sys_rec.base_min_acc_unit := ARP_ACCT_MAIN.ae_sys_rec.base_min_acc_unit;
208
209 l_ae_sys_rec.sob_type := ARP_ACCT_MAIN.ae_sys_rec.sob_type;
210
211 --Set the calling mode
212 IF (p_cust_inv_rec.drawee_site_use_id IS NULL) THEN
213 l_calling_point := 'TRAN';
214 ELSE
215 l_calling_point := 'BILL';
216 END IF;
217
218 IF PG_DEBUG in ('Y', 'C') THEN
219 arp_standard.debug('Check_Entry: ' || 'Calling point ' || l_calling_point);
220 END IF;
221
222 /*--------------------------------------------------------------------------+
223 | If the Transaction is not deferred then do not do any process as there is|
224 | no deferred tax to reconcile. Simillar processing for all Transactions on|
225 | a Bills Receivable Document. |
226 +--------------------------------------------------------------------------*/
227 Check_Entry(p_customer_trx_id => p_activity_cust_trx_id,
228 p_type => l_calling_point ,
229 p_required => l_required );
230
231 --If no deferred tax then processing is not required
232 IF (NOT l_required) THEN
233 GOTO end_process_lbl;
234 END IF;
235
236 /*--------------------------------------------------------------------------+
237 | Call the Reversal routine only if the activity amounts are non zero. This|
238 | is to ensure that the old reconciliation entries are not backed out, as |
239 | 0 amount activity indicates that there is no change in the Transactions |
240 | payment schedule. So the last image is unchanged. |
241 +--------------------------------------------------------------------------*/
242 IF ((nvl(p_activity_amt,0) + nvl(p_activity_acctd_amt,0)) <> 0) THEN
243 --Call the Reversal routine
244 Reverse_Reconcile_entry(p_mode => p_mode ,
245 p_ae_doc_rec => p_ae_doc_rec ,
246 p_ae_event_rec => p_ae_event_rec ,
247 p_ae_sys_rec => l_ae_sys_rec ,
248 p_customer_trx_id => p_activity_cust_trx_id ,
249 p_calling_point => l_calling_point ,
250 p_g_ae_ctr => p_g_ae_ctr ,
251 p_g_ae_line_tbl => p_g_ae_line_tbl );
252 END IF;
253
254 /*-------------------------------------------------------------------------+
255 | Determine whether the payment schedule of the Transaction or Bill is |
256 | closed, only on closure do we need to create the Reconciliation entry. |
257 +-------------------------------------------------------------------------*/
258 Detect_Closure(p_customer_trx_id => p_activity_cust_trx_id ,
259 p_pay_sched_upd_yn => p_ae_doc_rec.pay_sched_upd_yn ,
260 p_pay_sched_upd_cm_yn => p_ae_doc_rec.pay_sched_upd_cm_yn ,
261 p_activity_amt => p_activity_amt ,
262 p_activity_acctd_amt => p_activity_acctd_amt ,
263 p_ae_sys_rec => l_ae_sys_rec,
264 p_closed_pymt_yn => l_closed_pymt_yn ,
265 p_pay_class => l_pay_class );
266
267 /*-------------------------------------------------------------------------+
268 | Reconcile deferred tax accounting on Bill or Transaction only if it is |
269 | closed. Note for a Bill the drawee site is always populated hence. |
270 +-------------------------------------------------------------------------*/
271 IF (l_closed_pymt_yn = 'Y') THEN
272
273 IF (p_cust_inv_rec.drawee_site_use_id IS NULL) THEN
274 Reconcile_trx_tax(p_mode => p_mode ,
275 p_ae_doc_rec => p_ae_doc_rec ,
276 p_ae_event_rec => p_ae_event_rec ,
277 p_ae_sys_rec => l_ae_sys_rec ,
278 p_cust_inv_rec => p_cust_inv_rec ,
279 p_customer_trx_id => p_activity_cust_trx_id ,
280 p_br_cust_trx_line_id => '' ,
281 p_calling_point => 'TRAN' ,
282 p_pay_class => l_pay_class ,
283 p_g_ae_ctr => p_g_ae_ctr ,
284 p_g_ae_line_tbl => p_g_ae_line_tbl );
285
286 ELSE --reconcile tax accounting for a Bills Receivable document
287 Reconcile_br_tax(p_mode => p_mode ,
288 p_ae_doc_rec => p_ae_doc_rec ,
289 p_ae_event_rec => p_ae_event_rec ,
290 p_ae_sys_rec => l_ae_sys_rec ,
291 p_customer_trx_id => p_activity_cust_trx_id ,
292 p_g_ae_ctr => p_g_ae_ctr ,
293 p_g_ae_line_tbl => p_g_ae_line_tbl );
294
295 END IF; --reconcile document
296
297 END IF; --payment schedule is closed
298
299 <<end_process_lbl>>
300 IF PG_DEBUG in ('Y', 'C') THEN
301 arp_standard.debug('Check_Entry: ' || 'ARP_RECONCILE.Reconcile_trx_br ()- ');
302 END IF;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 IF PG_DEBUG in ('Y', 'C') THEN
307 arp_standard.debug('Check_Entry: ' || 'EXCEPTION OTHERS: ARP_RECONCILE.Reconcile_trx_br ');
308 END IF;
309 RAISE;
310
311 END Reconcile_trx_br;
312
313 /* =======================================================================
314 | PROCEDURE Check_Entry
315 |
316 | DESCRIPTION
317 | This routine checks whether the current Transaction to be Reconciled
318 | is deferred. Only then is processing really required.
319 |
320 | SCOPE - PUBLIC
321 |
322 | PARAMETERS
323 | p_customer_trx_id IN Transaction identifier
324 | p_type IN Transaction or Bill
325 | p_required OUT NOCOPY Flag indicates whether tax processing
326 | is required
327 * ======================================================================*/
328 PROCEDURE Check_Entry(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
329 p_type IN VARCHAR2 ,
330 p_required OUT NOCOPY BOOLEAN ) IS
331
332 l_def_flag VARCHAR2(1);
333
334 BEGIN
335
336 IF PG_DEBUG in ('Y', 'C') THEN
337 arp_standard.debug('ARP_RECONCILE.Check_Entry - Checking for deferred tax');
338 END IF;
339
340 IF (p_type = 'TRAN') THEN
341
342 BEGIN
343 select 'Y'
344 into l_def_flag
345 from dual
346 where exists (select 'x'
347 from ra_cust_trx_line_gl_dist gld
348 where gld.account_class = 'TAX'
349 and gld.customer_trx_id = p_customer_trx_id
350 and gld.collected_tax_ccid IS NOT NULL
351 );
352
353 p_required := TRUE; --Atleast one deferred tax line exists
354
355 EXCEPTION
356 WHEN NO_DATA_FOUND THEN
357 IF PG_DEBUG in ('Y', 'C') THEN
358 arp_standard.debug('ARP_RECONCILE.Check_Entry - NO DEFERRED TAX');
359 END IF;
360 p_required := FALSE; --Tax is not deferred processing not required
361 WHEN OTHERS THEN
362 IF PG_DEBUG in ('Y', 'C') THEN
363 arp_standard.debug('EXCEPTION OTHERS: ARP_RECONCILE.Check_Entry - Transaction check ');
364 END IF;
365 RAISE;
366 END; --deferred tax processing required for Transactions
367
368 ELSIF (p_type = 'BILL') THEN
369 --BR entitity handler call
370 ARP_PROCESS_BR_HEADER.move_deferred_tax(
371 p_customer_trx_id => p_customer_trx_id,
372 p_required => p_required);
373 ELSE
374 p_required := TRUE; --Enable processing this should never happen
375 END IF;
376
377 IF PG_DEBUG in ('Y', 'C') THEN
378 arp_standard.debug('ARP_RECONCILE.Check_Entry - DEFERRED TAX');
379 END IF;
380
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 IF PG_DEBUG in ('Y', 'C') THEN
384 arp_standard.debug('EXCEPTION NO_DATA_FOUND: ARP_RECONCILE.Check_Entry ');
385 END IF;
386 RAISE;
387 WHEN OTHERS THEN
388 IF PG_DEBUG in ('Y', 'C') THEN
389 arp_standard.debug('EXCEPTION OTHERS: ARP_RECONCILE.Check_Entry ');
390 END IF;
391 RAISE;
392 END Check_Entry;
393
394 /*========================================================================
395 | PRIVATE PROCEDURE Reverse_Reconcile_entry
396 |
397 | DESCRIPTION
398 | Reverses out NOCOPY reconciliation entries by location, tax group, tax code
399 | and account for deferred tax entries. This is necessary because when
400 | this routine is called, at that point of time the sum total of any
401 | past reconciliation entries must be zero.
402 |
403 | PARAMETERS
404 | p_mode IN Document or Accounting Event mode
405 | p_ae_doc_rec IN Document Record
406 | p_ae_event_rec IN Event Record
407 | p_ae_sys_rec IN System parameter details
408 | p_cust_inv_rec IN Contains currency, exchange rate, site
409 | details for the bill
410 | p_g_ae_ctr IN OUT NOCOPY counter for global accounting lines table
411 | p_g_ae_line_tbl IN OUT NOCOPY accounting lines table containing reconciled
412 | entry
413 *=======================================================================*/
414 PROCEDURE Reverse_Reconcile_entry(
415 p_mode IN VARCHAR2 ,
416 p_ae_doc_rec IN ae_doc_rec_type ,
417 p_ae_event_rec IN ae_event_rec_type ,
418 p_ae_sys_rec IN ae_sys_rec_type ,
419 p_customer_trx_id IN NUMBER ,
420 p_calling_point IN VARCHAR2 ,
421 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
422 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type ) IS
423
424 l_cust_inv_rec ra_customer_trx%ROWTYPE;
425
426 CURSOR get_assignments(p_customer_trx_id IN NUMBER) is
427 SELECT ctl.customer_trx_id br_cust_trx_id ,
428 ctl.customer_trx_line_id br_customer_trx_line_id ,
429 ctl.br_ref_customer_trx_id br_ref_customer_trx_id ,
430 ctl.br_ref_payment_schedule_id br_ref_payment_schedule_id ,
431 ct.drawee_site_use_id drawee_site_use_id ,
432 ct.invoice_currency_code invoice_currency_code ,
433 ct.exchange_rate exchange_rate ,
434 ct.exchange_rate_type exchange_rate_type ,
435 ct.exchange_date exchange_date ,
436 ct.trx_date trx_date ,
437 ct.bill_to_customer_id bill_to_customer_id ,
438 ct.bill_to_site_use_id bill_to_site_use_id ,
439 adj.adjustment_id br_adj_id ,
440 nvl(adj.amount,0) br_adj_amt ,
441 nvl(adj.acctd_amount,0) br_adj_acctd_amt ,
442 nvl(adj.line_adjusted,0) br_adj_line_amt ,
443 nvl(adj.tax_adjusted,0) br_adj_tax_amt ,
444 nvl(adj.freight_adjusted,0) br_adj_frt_amt ,
445 nvl(adj.receivables_charges_adjusted,0) br_adj_chrg_amt
446 FROM ra_customer_trx_lines ctl,
447 ar_adjustments adj,
448 ra_customer_trx ct
449 WHERE ctl.customer_trx_id = p_customer_trx_id
450 AND ctl.br_adjustment_id = adj.adjustment_id
451 AND ct.customer_trx_id = ctl.br_ref_customer_trx_id
452 AND adj.status = 'A'
453 order by ctl.customer_trx_line_id;
454
455 BEGIN
456
457 IF PG_DEBUG in ('Y', 'C') THEN
458 arp_standard.debug('ARP_RECONCILE.Reverse_Reconcile_Entry()+ ');
459 arp_standard.debug('Reverse_Reconcile_entry: ' || 'p_customer_trx_id ' || p_customer_trx_id);
460 arp_standard.debug('Reverse_Reconcile_entry: ' || 'p_calling_point ' || p_calling_point);
461 END IF;
462
463 IF p_calling_point IN ('TRAN', 'BLTR') THEN
464
465 IF PG_DEBUG in ('Y', 'C') THEN
466 arp_standard.debug('Reverse_Reconcile_entry: ' || 'Calling get_recon_acct for 1 -' || p_calling_point);
467 END IF;
468
469 --get reconciliation entries customer trx line id, tax code, account
470 get_recon_acct( p_mode => p_mode ,
471 p_ae_doc_rec => p_ae_doc_rec ,
472 p_ae_event_rec => p_ae_event_rec ,
473 p_ae_sys_rec => p_ae_sys_rec ,
474 p_customer_trx_id => p_customer_trx_id ,
475 p_customer_trx_line_id => '' ,
476 p_calling_point => 'TRAN' ,
477 p_g_ae_ctr => p_g_ae_ctr ,
478 p_g_ae_line_tbl => p_g_ae_line_tbl );
479
480 --get transactions reconciliation entries only (not bills)
481 --Reverse reconciliation entry if required
482
483 ELSE
484
485 IF PG_DEBUG in ('Y', 'C') THEN
486 arp_standard.debug('Reverse_Reconcile_entry: ' || 'Calling get_recon_acct for 2 -' || p_calling_point);
487 END IF;
488
489 --Loop assignment times
490 FOR l_assign_rec IN get_assignments(p_customer_trx_id) LOOP
491
492 IF PG_DEBUG in ('Y', 'C') THEN
493 arp_standard.debug('Reverse_Reconcile_entry: ' || 'l_assign_rec.drawee_site_use_id ' || l_assign_rec.drawee_site_use_id);
494 END IF;
495
496 --If assignment is a bill then Recursive call
497 IF l_assign_rec.drawee_site_use_id IS NOT NULL THEN
498
499 IF PG_DEBUG in ('Y', 'C') THEN
500 arp_standard.debug('Reverse_Reconcile_entry: ' || 'Recursive call for l_assign_rec.br_ref_customer_trx_id ' || l_assign_rec.br_ref_customer_trx_id);
501 END IF;
502 Reverse_Reconcile_entry(
503 p_mode => p_mode ,
504 p_ae_doc_rec => p_ae_doc_rec ,
505 p_ae_event_rec => p_ae_event_rec ,
506 p_ae_sys_rec => p_ae_sys_rec ,
507 p_customer_trx_id => l_assign_rec.br_ref_customer_trx_id ,
508 p_calling_point => 'BILL' ,
509 p_g_ae_ctr => p_g_ae_ctr ,
510 p_g_ae_line_tbl => p_g_ae_line_tbl );
511 ELSE
512 -- get reconciliation entries customer trx line id,
513 -- tax code, account
514 get_recon_acct(
515 p_mode => p_mode ,
516 p_ae_doc_rec => p_ae_doc_rec ,
517 p_ae_event_rec => p_ae_event_rec ,
518 p_ae_sys_rec => p_ae_sys_rec ,
519 p_customer_trx_id => l_assign_rec.br_ref_customer_trx_id,
520 p_customer_trx_line_id => l_assign_rec.br_customer_trx_line_id,
521 p_calling_point => 'BILL',
522 p_g_ae_ctr => p_g_ae_ctr,
523 p_g_ae_line_tbl => p_g_ae_line_tbl );
524
525 --get reconciliation entries for transaction
526 get_recon_acct( p_mode => p_mode ,
527 p_ae_doc_rec => p_ae_doc_rec ,
528 p_ae_event_rec => p_ae_event_rec ,
529 p_ae_sys_rec => p_ae_sys_rec ,
530 p_customer_trx_id => l_assign_rec.br_ref_customer_trx_id ,
531 p_customer_trx_line_id => l_assign_rec.br_customer_trx_line_id ,
532 p_calling_point => 'BLTR' ,
533 p_g_ae_ctr => p_g_ae_ctr ,
534 p_g_ae_line_tbl => p_g_ae_line_tbl );
535
536 END IF; --drawee site is not null
537
538 END LOOP; --get assignments
539
540 END IF; --drawee site is null
541
542 IF PG_DEBUG in ('Y', 'C') THEN
543 arp_standard.debug('ARP_RECONCILE.Reverse_Reconcile_Entry()- ');
544 END IF;
545
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 IF PG_DEBUG in ('Y', 'C') THEN
549 arp_standard.debug('EXCEPTION NO_DATA_FOUND: ARP_RECONCILE.Reverse_Reconcile_entry ');
550 END IF;
551 RAISE;
552 WHEN OTHERS THEN
553 IF PG_DEBUG in ('Y', 'C') THEN
554 arp_standard.debug('Reverse_Reconcile_entry: ' || SQLERRM);
555 arp_standard.debug('EXCEPTION OTHERS: ARP_RECONCILE.Reverse_Reconcile_entry ');
556 END IF;
557 RAISE;
558
559 END Reverse_Reconcile_entry;
560
561 /*========================================================================
562 | PRIVATE PROCEDURE get_recon_acct
563 |
564 | DESCRIPTION
565 | Reverses out NOCOPY reconciliation entries by location, tax group, tax code
566 | and account for deferred tax entries. This is necessary because when
567 | this routine is called, at that point of time the sum total of any
568 | past reconciliation entries must be zero.
569 |
570 | PARAMETERS
571 | p_mode IN Document or Accounting Event mode
572 | p_ae_doc_rec IN Document Record
573 | p_ae_event_rec IN Event Record
574 | p_ae_sys_rec IN System parameter details
575 | p_customer_trx_id IN Transaction Id
576 | p_customer_trx_line_id IN transaction line id
577 | p_calling_point IN Callin from routine
578 | p_g_ae_ctr IN OUT NOCOPY counter for global accounting lines table
579 | p_g_ae_line_tbl IN OUT NOCOPY accounting lines table containing
580 | reconciled entry
581 *=======================================================================*/
582 PROCEDURE get_recon_acct(
583 p_mode IN VARCHAR2,
584 p_ae_doc_rec IN ae_doc_rec_type,
585 p_ae_event_rec IN ae_event_rec_type,
586 p_ae_sys_rec IN ae_sys_rec_type,
587 p_customer_trx_id IN NUMBER,
588 p_customer_trx_line_id IN NUMBER,
589 p_calling_point IN VARCHAR2,
590 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER,
591 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type
592 ) IS
593
594 --
595 -- Get the Reconciliation entries created for Receipt applications
596 -- The source id secondary and table secondary are populated based
597 -- on which activity the reconciliation reversal is going to be created,
598 -- if under a Bill or BLTR, then the source table is CTL and source id
599 -- secondary is the assignment line id or parameter p_customer_trx_line_id
600 --
601
602 TYPE get_recon_acct_type IS REF CURSOR;
603 get_recon_accounting get_recon_acct_type;
604 l_recon_rec get_recon_rec_type;
605
606 sql_stmt VARCHAR2(32000);
607
608 select1_stmt VARCHAR2(4000) := '
609 select source_type source_type,
610 source_id_secondary source_id_secondary,
611 source_table_secondary source_table_secondary,
612 source_type_secondary source_type_secondary,
613 max(currency_code) currency_code,
614 max(currency_conversion_rate) currency_conversion_rate,
615 max(currency_conversion_type) currency_conversion_type,
616 max(currency_conversion_date) currency_conversion_date,
617 max(third_party_id) third_party_id,
618 max(third_party_sub_id) third_party_sub_id,
619 max(reversed_source_id) reversed_source_id,
620 sum(amount) amount,
621 sum(acctd_amount) acctd_amount,
622 sum(taxable_entered) taxable_entered,
623 sum(taxable_accounted) taxable_accounted,
624 location_segment_id location_segment_id,
625 tax_group_code_id tax_group_code_id,
626 tax_code_id tax_code_id,
627 code_combination_id code_combination_id
628 from ( ';
629
630 --inline query
631 select2_stmt VARCHAR2(4000) := '
632 select
633 ard.source_type source_type,
634 decode(:p_calling_point,
635 ''BLTR'', :p_customer_trx_line_id,
636 ard.source_id_secondary) source_id_secondary,
637 decode(:p_calling_point,
638 ''BLTR'', ''CTL'',
639 ard.source_table_secondary) source_table_secondary,
640 ard.source_type_secondary source_type_secondary,
641 max(ard.currency_code) currency_code,
642 max(ard.currency_conversion_rate) currency_conversion_rate,
643 max(ard.currency_conversion_type) currency_conversion_type,
644 max(ard.currency_conversion_date) currency_conversion_date,
645 max(ard.third_party_id) third_party_id,
646 max(ard.third_party_sub_id) third_party_sub_id,
647 max(reversed_source_id) reversed_source_id,
648 sum(nvl(ard.amount_dr,0) * -1 + nvl(ard.amount_cr,0)) amount,
649 sum(nvl(ard.acctd_amount_dr,0) * -1 +
650 nvl(ard.acctd_amount_cr,0)) acctd_amount,
651 sum(nvl(ard.taxable_entered_dr,0) * -1 +
652 nvl(ard.taxable_entered_cr,0)) taxable_entered,
653 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
654 nvl(ard.taxable_accounted_cr,0)) taxable_accounted,
655 ard.location_segment_id location_segment_id,
656 ard.tax_group_code_id tax_group_code_id,
657 ard.tax_code_id tax_code_id,
658 ard.code_combination_id code_combination_id';
659
660 from1_stmt VARCHAR2(150) := ' from ar_distributions ard,
661 ar_receivable_applications app
662 where ';
663
664 where1_stmt VARCHAR2(3000) := ' app.applied_customer_trx_id = :p_customer_trx_id
665 and :p_calling_point IN (''TRAN'', ''BLTR'')
666 and app.status = ''APP''
667 and nvl(app.confirmed_flag, ''Y'') = ''Y''
668 and ard.source_id = app.receivable_application_id
669 and ard.source_table = ''RA''
670 and ard.source_type IN (''TAX'', ''DEFERRED_TAX'')
671 and ard.source_type_secondary = ''RECONCILE''
672 and ard.source_id_secondary = :p_customer_trx_id ';
673
674 group_stmt VARCHAR2(4000) := ' group by ard.source_type,
675 decode(:p_calling_point,
676 ''BLTR'', :p_customer_trx_line_id,
677 ard.source_id_secondary) ,
678 decode(:p_calling_point,
679 ''BLTR'', ''CTL'',
680 ard.source_table_secondary),
681 ard.source_type_secondary,
682 ard.location_segment_id,
683 ard.tax_group_code_id,
684 ard.tax_code_id,
685 ard.code_combination_id
686 having ((sum(nvl(ard.amount_dr,0) * -1 + nvl(ard.amount_cr,0)) <> 0)
687 OR (sum(nvl(ard.acctd_amount_dr,0) * -1 +
688 nvl(ard.acctd_amount_cr,0)) <> 0)
689 OR (sum(nvl(ard.taxable_entered_dr,0) * -1 +
690 nvl(ard.taxable_entered_cr,0)) <> 0)
691 OR (sum(nvl(ard.taxable_accounted_dr,0) * -1 +
692 nvl(ard.taxable_accounted_cr,0)) <> 0))';
693
694 -- Get the Reconciliation entries created for Adjustments
695
696 from2_stmt VARCHAR2(150) := ' from ar_distributions ard,
697 ar_adjustments adj
698 where ';
699
700 where2_stmt VARCHAR2(3000) := ' adj.customer_trx_id = :p_customer_trx_id
701 and :p_calling_point IN (''TRAN'', ''BLTR'')
702 and adj.status = ''A''
703 and ard.source_id = adj.adjustment_id
704 and ard.source_table = ''ADJ''
705 and ard.source_type IN (''TAX'', ''DEFERRED_TAX'')
706 and ard.source_type_secondary = ''RECONCILE''
707 and ard.source_id_secondary = :p_customer_trx_id ';
708
709 -- Get the Reconciliation entries created for Assignments of
710 -- transaction to a bill
711 select3_stmt VARCHAR2(4000) := '
712 select
713 ard.source_type source_type,
714 decode(:p_calling_point,
715 ''BLTR'', :p_customer_trx_line_id,
716 :p_customer_trx_id) source_id_secondary,
717 decode(:p_calling_point,
718 ''BLTR'',''CTL'',
719 ''CT'') source_table_secondary,
720 ard.source_type_secondary source_type_secondary,
721 max(ard.currency_code) currency_code,
722 max(ard.currency_conversion_rate) currency_conversion_rate,
723 max(ard.currency_conversion_type) currency_conversion_type,
724 max(ard.currency_conversion_date) currency_conversion_date,
725 max(ard.third_party_id) third_party_id,
726 max(ard.third_party_sub_id) third_party_sub_id,
727 max(reversed_source_id) reversed_source_id,
728 sum(nvl(ard.amount_dr,0) * -1 + nvl(ard.amount_cr,0)) amount,
729 sum(nvl(ard.acctd_amount_dr,0) * -1 +
730 nvl(ard.acctd_amount_cr,0)) acctd_amount,
731 sum(nvl(ard.taxable_entered_dr,0) * -1 +
732 nvl(ard.taxable_entered_cr,0)) taxable_entered,
733 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
734 nvl(ard.taxable_accounted_cr,0)) taxable_accounted,
735 ard.location_segment_id location_segment_id,
736 ard.tax_group_code_id tax_group_code_id,
737 ard.tax_code_id tax_code_id,
738 ard.code_combination_id code_combination_id';
739
740 from3_stmt VARCHAR2(150) := 'from ra_customer_trx_lines ctl,
741 ar_distributions ard
742 where ';
743
744 where3_stmt VARCHAR2(3000) := ' ctl.br_ref_customer_trx_id = :p_customer_trx_id
745 and :p_calling_point IN (''TRAN'', ''BLTR'')
746 and ard.source_id_secondary = ctl.customer_trx_line_id
747 and ard.source_table_secondary = ''CTL''
748 and ard.source_type_secondary = ''RECONCILE''
749 and ard.source_type IN (''TAX'', ''DEFERRED_TAX'') ';
750
751
752 group3_stmt VARCHAR2(4000) := 'group by ard.source_type,
753 decode(:p_calling_point,
754 ''BLTR'', :p_customer_trx_line_id,
755 :p_customer_trx_id) ,
756 decode(:p_calling_point,
757 ''BLTR'', ''CTL'',
758 ''CT'') ,
759 ard.source_type_secondary,
760 ard.location_segment_id,
761 ard.tax_group_code_id,
762 ard.tax_code_id,
763 ard.code_combination_id
764 having ((sum(nvl(ard.amount_dr,0) * -1 + nvl(ard.amount_cr,0)) <> 0)
765 OR (sum(nvl(ard.acctd_amount_dr,0) * -1 +
766 nvl(ard.acctd_amount_cr,0)) <> 0)
767 OR (sum(nvl(ard.taxable_entered_dr,0) * -1 +
768 nvl(ard.taxable_entered_cr,0)) <> 0)
769 OR (sum(nvl(ard.taxable_accounted_dr,0) * -1 +
770 nvl(ard.taxable_accounted_cr,0)) <> 0)) ';
771
772 --Get the Reconciliation entries created for assignments
773 select4_stmt VARCHAR2(4000) := '
774 select
775 ard.source_type source_type,
776 ard.source_id_secondary source_id_secondary,
777 ard.source_table_secondary source_table_secondary,
778 ard.source_type_secondary source_type_secondary,
779 max(ard.currency_code) currency_code,
780 max(ard.currency_conversion_rate) currency_conversion_rate,
781 max(ard.currency_conversion_type) currency_conversion_type,
782 max(ard.currency_conversion_date) currency_conversion_date,
783 max(ard.third_party_id) third_party_id,
784 max(ard.third_party_sub_id) third_party_sub_id,
785 max(reversed_source_id) reversed_source_id,
786 sum(nvl(ard.amount_dr,0) * -1 + nvl(ard.amount_cr,0)) amount,
787 sum(nvl(ard.acctd_amount_dr,0) * -1 +
788 nvl(ard.acctd_amount_cr,0)) acctd_amount,
789 sum(nvl(ard.taxable_entered_dr,0) * -1 +
790 nvl(ard.taxable_entered_cr,0)) taxable_entered,
791 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
792 nvl(ard.taxable_accounted_cr,0)) taxable_accounted,
793 ard.location_segment_id location_segment_id,
794 ard.tax_group_code_id tax_group_code_id,
795 ard.tax_code_id tax_code_id,
796 ard.code_combination_id code_combination_id ';
797
798 from4_stmt VARCHAR2(100) := ' from ar_distributions ard
799 where ';
800
801 where4_stmt VARCHAR2(3000) := ' ard.source_id_secondary = :p_customer_trx_line_id
802 and :p_calling_point = ''BILL''
803 and ard.source_table_secondary = ''CTL''
804 and ard.source_type_secondary = ''ASSIGNMENT_RECONCILE''
805 and ard.source_type IN (''TAX'', ''DEFERRED_TAX'') ';
806
807 group4_stmt VARCHAR2(4000) := ' group by ard.source_type,
808 ard.source_id_secondary,
809 ard.source_table_secondary,
810 ard.source_type_secondary,
811 ard.location_segment_id,
812 ard.tax_group_code_id,
813 ard.tax_code_id,
814 ard.code_combination_id
815 having ((sum(nvl(ard.amount_dr,0) * -1 + nvl(ard.amount_cr,0)) <> 0)
816 OR (sum(nvl(ard.acctd_amount_dr,0) * -1 +
817 nvl(ard.acctd_amount_cr,0)) <> 0)
818 OR (sum(nvl(ard.taxable_entered_dr,0) * -1 +
819 nvl(ard.taxable_entered_cr,0)) <> 0)
820 OR (sum(nvl(ard.taxable_accounted_dr,0) * -1 +
821 nvl(ard.taxable_accounted_cr,0)) <> 0)) ';
822
823 group2 VARCHAR2(1000) := ')
824 group by source_type,
825 source_id_secondary,
826 source_table_secondary,
827 source_type_secondary,
828 location_segment_id,
829 tax_group_code_id,
830 tax_code_id,
831 code_combination_id';
832
833 --from1_mrc_stmt VARCHAR2(100) := ' from ar_mc_distributions_all ard,
834 -- ar_receivable_applications app
835 -- where ';
836
837 --where1_mrc_stmt VARCHAR2(150) := ' ard.set_of_books_id = :sob_id and ';
838
839 --from2_mrc_stmt VARCHAR2(150) := ' from ar_mc_distributions_all ard,
840 -- ar_adjustments adj
841 -- where ';
842
843 --from3_mrc_stmt VARCHAR2(150) := ' from ra_customer_trx_lines ctl,
844 -- ar_mc_distributions_all ard
845 -- where ';
846
847 --from4_mrc_stmt VARCHAR2(100) := ' from ar_mc_distributions_all ard
848 --where ';
849
850 union_stmt VARCHAR2(15) := ' UNION ALL';
851
852 l_ae_line_rec ae_line_rec_type;
853 l_ae_empty_line_rec ae_line_rec_type;
854
855 sob_id NUMBER;
856
857 CRLF VARCHAR2(10) := arp_global.CRLF;
858
859 BEGIN
860
861 IF PG_DEBUG in ('Y', 'C') THEN
862 arp_standard.debug('ARP_RECONCILE.Get_Recon_Acct()+ ');
863 arp_standard.debug('get_recon_acct: ' || 'p_customer_trx_id ' || p_customer_trx_id);
864 arp_standard.debug('get_recon_acct: ' || 'p_customer_trx_line_id ' || p_customer_trx_line_id);
865 arp_standard.debug('get_recon_acct: ' || 'p_calling_point ' || p_calling_point);
866 END IF;
867
868 sob_id := p_ae_sys_rec.set_of_books_id;
869 IF PG_DEBUG in ('Y', 'C') THEN
870 arp_standard.debug('get_recon_acct: ' || 'set of books id = ' || to_char(sob_id));
871 END IF;
872
873 -- Construct Select Cursor based on reporting type:
874 IF PG_DEBUG in ('Y', 'C') THEN
875 arp_standard.debug('get_recon_acct: ' || 'selecting cursor based on reporting type');
876 END IF;
877
878 sql_stmt := select1_stmt ||
879 select2_stmt; /* common between primary and reporting */
880
881 IF ( p_ae_sys_rec.sob_type = 'P') THEN
882
883 sql_stmt := sql_stmt || CRLF || from1_stmt || CRLF ||
884 where1_stmt || CRLF ||
885 group_stmt || CRLF ||
886 union_stmt ||
887 select2_stmt || CRLF || from2_stmt || CRLF ||
888 where2_stmt || CRLF ||
889 group_stmt || CRLF ||
890 union_stmt ||
891 select3_stmt || CRLF || from3_stmt || CRLF ||
892 where3_stmt || CRLF ||
893 group3_stmt || CRLF ||
894 union_stmt ||
895 select4_stmt || CRLF || from4_stmt || CRLF ||
896 where4_stmt || CRLF ||
897 group4_stmt || CRLF ||
898 group2;
899 END IF;
900
901 IF PG_DEBUG in ('Y', 'C') THEN
902 arp_standard.debug ('get_recon_acct: ' || 'select_stmt = ' || sql_stmt);
903 END IF;
904
905 IF ( p_ae_sys_rec.sob_type = 'P') THEN
906 OPEN get_recon_accounting FOR sql_stmt
907 USING p_calling_point, p_customer_trx_line_id,
908 p_calling_point, p_customer_trx_id,
909 p_calling_point, p_customer_trx_id,
910 p_calling_point, p_customer_trx_line_id,
911 p_calling_point, p_calling_point,
912 p_customer_trx_line_id, p_calling_point,
913 p_customer_trx_id, p_calling_point,
914 p_customer_trx_id, p_calling_point,
915 p_customer_trx_line_id, p_calling_point,
916 p_calling_point, p_customer_trx_line_id,
917 p_customer_trx_id,
918 p_calling_point, p_customer_trx_id,
919 p_calling_point, p_calling_point,
920 p_customer_trx_line_id, p_customer_trx_id,
921 p_calling_point, p_customer_trx_line_id,
922 p_calling_point;
923
924 --{BUG4301323
925 LOOP
926 IF PG_DEBUG in ('Y', 'C') THEN
927 arp_standard.debug('get_recon_acct: ' || 'before fetch..');
928 END IF;
929 --get reconciliation entries customer trx line id, tax code, account
930 FETCH get_recon_accounting into l_recon_rec;
931 EXIT WHEN get_recon_accounting%NOTFOUND;
932
933 IF PG_DEBUG in ('Y', 'C') THEN
934 arp_standard.debug('get_recon_acct: ' || 'In the reverse reconciliation entries Loop ');
935 arp_standard.debug('get_recon_acct: ' || 'tax_group_code_id ' || l_recon_rec.tax_group_code_id);
936 arp_standard.debug('get_recon_acct: ' || 'tax_code_id ' || l_recon_rec.tax_code_id);
937 arp_standard.debug('get_recon_acct: ' || 'location segment id ' || l_recon_rec.location_segment_id);
938 END IF;
939
940 l_ae_line_rec := l_ae_empty_line_rec;
941
942 -- For each assignment with non zero reconciliation
943 -- accounting create reversal entries
944 l_ae_line_rec.ae_line_type := l_recon_rec.source_type;
945 l_ae_line_rec.ae_line_type_secondary := l_recon_rec.source_type_secondary;
946 l_ae_line_rec.source_id := p_ae_doc_rec.source_id;
947 l_ae_line_rec.source_table := p_ae_doc_rec.source_table;
948 l_ae_line_rec.account := l_recon_rec.code_combination_id;
949
950 --Create amounts
951 IF l_recon_rec.amount < 0 THEN
952 l_ae_line_rec.entered_cr := abs(l_recon_rec.amount);
953 l_ae_line_rec.entered_dr := NULL;
954 ELSIF l_recon_rec.amount > 0 THEN
955 l_ae_line_rec.entered_dr := abs(l_recon_rec.amount);
956 l_ae_line_rec.entered_cr := NULL;
957 END IF;
958
959 --Create accounted amounts
960 IF l_recon_rec.acctd_amount < 0 THEN
961 l_ae_line_rec.accounted_cr := abs(l_recon_rec.acctd_amount);
962 l_ae_line_rec.accounted_dr := NULL;
963 ELSIF l_recon_rec.acctd_amount > 0 THEN
964 l_ae_line_rec.accounted_dr := abs(l_recon_rec.acctd_amount);
965 l_ae_line_rec.accounted_cr := NULL;
966 END IF;
967
968 --Create taxable amounts
969 IF l_recon_rec.taxable_entered < 0 THEN
970 l_ae_line_rec.taxable_entered_cr := abs(l_recon_rec.taxable_entered);
971 l_ae_line_rec.taxable_entered_dr := NULL;
972 ELSIF l_recon_rec.taxable_entered > 0 THEN
973 l_ae_line_rec.taxable_entered_dr := abs(l_recon_rec.taxable_entered);
974 l_ae_line_rec.taxable_entered_cr := NULL;
975 END IF;
976
977 --Create taxable accounted amounts
978 IF l_recon_rec.taxable_accounted < 0 THEN
979 l_ae_line_rec.taxable_accounted_cr := abs(l_recon_rec.taxable_accounted);
980 l_ae_line_rec.taxable_accounted_dr := NULL;
981 ELSIF l_recon_rec.taxable_accounted > 0 THEN
982 l_ae_line_rec.taxable_accounted_dr := abs(l_recon_rec.taxable_accounted);
983 l_ae_line_rec.taxable_accounted_cr := NULL;
984 END IF;
985
986 l_ae_line_rec.source_id_secondary := l_recon_rec.source_id_secondary;
987 l_ae_line_rec.source_table_secondary := l_recon_rec.source_table_secondary;
988 l_ae_line_rec.currency_code := l_recon_rec.currency_code;
989 l_ae_line_rec.currency_conversion_rate := l_recon_rec.currency_conversion_rate;
990 l_ae_line_rec.currency_conversion_type := l_recon_rec.currency_conversion_type;
991 l_ae_line_rec.currency_conversion_date := l_recon_rec.currency_conversion_date;
992 l_ae_line_rec.third_party_id := l_recon_rec.third_party_id;
993 l_ae_line_rec.third_party_sub_id := l_recon_rec.third_party_sub_id;
994 l_ae_line_rec.tax_group_code_id := l_recon_rec.tax_group_code_id;
995 l_ae_line_rec.tax_code_id := l_recon_rec.tax_code_id;
996 l_ae_line_rec.location_segment_id := l_recon_rec.location_segment_id;
997 l_ae_line_rec.tax_link_id := '';
998 l_ae_line_rec.reversed_source_id := '';
999
1000 --Reverse reversal entries for Bill
1001 Assign_Elements(p_ae_line_rec => l_ae_line_rec ,
1002 p_g_ae_ctr => p_g_ae_ctr ,
1003 p_g_ae_line_tbl => p_g_ae_line_tbl );
1004
1005 END LOOP; --get reconciliation entries
1006
1007 -- Close cursor.
1008 CLOSE get_recon_accounting;
1009
1010 END IF;
1011
1012 IF PG_DEBUG in ('Y', 'C') THEN
1013 arp_standard.debug('ARP_RECONCILE.Get_Recon_Acct()- ');
1014 END IF;
1015
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 IF PG_DEBUG in ('Y', 'C') THEN
1019 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Get_Recon_Acct ');
1020 END IF;
1021 RAISE;
1022 END get_recon_acct;
1023
1024 /*=========================================================================================
1025 | PRIVATE PROCEDURE Check_all_bills_closed
1026 |
1027 | DESCRIPTION
1028 |
1029 | All chained Bills starting from the current Bill must be closed for the Reconciliation
1030 | process to commence. The current Bill may be assigned to several other Bills so it is
1031 | important to ensure that thes Bills in turn are closed. This is because the deferred
1032 | tax liability on the originating Bill is transfered to all Bills, so the starting Bill
1033 | assignments representating deferred tax on the original Transactions must be reconciled
1034 | only if this condition is true.
1035 |
1036 | PARAMETERS
1037 | p_customer_trx_id IN Current Bill or Transaction id
1038 | p_all_br_closed OUT NOCOPY Flag indicating that the Bill or transaction
1039 | is a candidate for Reconciliation
1040 *========================================================================================*/
1041 PROCEDURE Check_all_bills_closed(p_customer_trx_id IN NUMBER ,
1042 p_all_br_closed IN OUT NOCOPY VARCHAR2 ) IS
1043
1044 BEGIN
1045
1046 IF PG_DEBUG in ('Y', 'C') THEN
1047 arp_standard.debug('ARP_RECONCILE.Check_all_bills_closed ()+ ');
1048 END IF;
1049
1050 select 'N'
1051 into p_all_br_closed
1052 from dual
1053 where exists ( select /*+ ordered leading(rc.rct) use_nl(rc.rct ps)*/ 'x'
1054 from ( select customer_trx_id
1055 from ra_customer_trx_lines rct
1056 start with br_ref_customer_trx_id = p_customer_trx_id
1057 connect by prior customer_trx_id = br_ref_customer_trx_id
1058 ) rc, ar_payment_schedules ps
1059 where ps.customer_trx_id = rc.customer_trx_id
1060 and ps.status = 'OP'
1061 and ps.customer_trx_id <> g_orig_cust_trx_id
1062 );
1063
1064 IF PG_DEBUG in ('Y', 'C') THEN
1065 arp_standard.debug('ARP_RECONCILE.Check_all_bills_closed, -set p_all_br_closed to N ');
1066 arp_standard.debug('ARP_RECONCILE.Check_all_bills_closed ()- ');
1067 END IF;
1068
1069 EXCEPTION
1070 WHEN NO_DATA_FOUND then
1071 IF PG_DEBUG in ('Y', 'C') THEN
1072 arp_standard.debug('NO_DATA_FOUND : ARP_RECONCILE.Check_all_bills_closed, -set p_all_br_closed to Y ');
1073 END IF;
1074 p_all_br_closed := 'Y';
1075 WHEN OTHERS then
1076 IF PG_DEBUG in ('Y', 'C') THEN
1077 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Check_all_bills_closed ');
1078 END IF;
1079 RAISE;
1080
1081 END Check_all_bills_closed;
1082
1083 /*========================================================================
1084 | PRIVATE PROCEDURE Reconcile_trx_tax
1085 |
1086 | DESCRIPTION
1087 | Reconciles the transaction deferred tax accounting. For transactions
1088 | with no CM activity, reconciles actual accounting against the original
1089 | tax on the Invoice. In case there is CM activity, since CM's have their
1090 | own accounting, reconciles the actual non CM activity related accounting
1091 | with accounting derived as a result of simulating a single activity equal
1092 | to the sum of the non CM related activity and reconciles by tax code and
1093 | account.
1094 |
1095 | PARAMETERS
1096 | p_mode IN Document or Accounting Event mode
1097 | p_ae_doc_rec IN Document Record
1098 | p_ae_event_rec IN Event Record
1099 | p_ae_sys_rec IN System parameter details
1100 | p_cust_inv_rec IN Contains currency, exchange rate, site
1101 | details for the bill
1102 | p_g_ae_ctr IN OUT NOCOPY counter for lines table
1103 | p_g_ae_line_tbl IN OUT NOCOPY lines table containing reconciled entry
1104 *=======================================================================*/
1105 PROCEDURE Reconcile_trx_tax(
1106 p_mode IN VARCHAR2 ,
1107 p_ae_doc_rec IN ae_doc_rec_type ,
1108 p_ae_event_rec IN ae_event_rec_type ,
1109 p_ae_sys_rec IN ae_sys_rec_type ,
1110 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
1111 p_customer_trx_id IN NUMBER ,
1112 p_br_cust_trx_line_id IN NUMBER ,
1113 p_calling_point IN VARCHAR2 ,
1114 p_pay_class IN VARCHAR2 ,
1115 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
1116 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type ) IS
1117
1118
1119 CURSOR get_single_activity IS
1120 SELECT pay.payment_schedule_id payment_schedule_id,
1121 sum( nvl(app.amount_applied,0) +
1122 nvl(app.earned_discount_taken,0) +
1123 nvl(app.unearned_discount_taken,0)) amount,
1124 sum(nvl(app.acctd_amount_applied_to,0) +
1125 nvl(app.acctd_earned_discount_taken,0) +
1126 nvl(app.acctd_unearned_discount_taken,0)) acctd_amount,
1127 sum(nvl(app.line_applied,0) +
1128 nvl(app.line_ediscounted,0) +
1129 nvl(app.line_uediscounted,0)) line_amount,
1130 sum(nvl(app.tax_applied,0) +
1131 nvl(app.tax_ediscounted,0) +
1132 nvl(app.tax_uediscounted,0)) tax_amount,
1133 sum(nvl(app.freight_applied,0) +
1134 nvl(app.freight_ediscounted,0) +
1135 nvl(app.freight_uediscounted,0)) freight_amount,
1136 sum(nvl(app.receivables_charges_applied,0) +
1137 nvl(app.charges_ediscounted,0) +
1138 nvl(app.charges_uediscounted,0)) receivables_charges_amount
1139 FROM ar_receivable_applications app,
1140 ar_payment_schedules pay
1141 WHERE app.applied_customer_trx_id = p_customer_trx_id
1142 AND app.status = 'APP'
1143 AND nvl(app.confirmed_flag, 'Y') = 'Y'
1144 AND app.applied_payment_schedule_id = pay.payment_schedule_id
1145 AND app.application_type = 'CASH' --only payments result in movement of
1146 GROUP by pay.payment_schedule_id
1147 UNION ALL --get adjustment bucket details
1148 SELECT pay.payment_schedule_id payment_schedule_id,
1149 sum(nvl(adj.amount,0) * -1) amount,
1150 sum(nvl(adj.acctd_amount,0) * -1) acctd_amount,
1151 sum(nvl(adj.line_adjusted,0) * -1) line_amount,
1152 sum(nvl(adj.tax_adjusted,0) * -1) tax_amount,
1153 sum(nvl(adj.freight_adjusted,0) * -1) freight_amount,
1154 sum(nvl(adj.receivables_charges_adjusted,0) * -1) receivables_charges_amount
1155 FROM ar_adjustments adj,
1156 ar_payment_schedules pay
1157 WHERE adj.customer_trx_id = p_customer_trx_id
1158 AND adj.payment_schedule_id = pay.payment_schedule_id
1159 AND adj.status = 'A'
1160 GROUP by pay.payment_schedule_id;
1161
1162 l_pay_tbl g_pay_tbl_type;
1163 l_pay_empty_tbl g_pay_tbl_type;
1164
1165 l_accum_amount NUMBER := 0;
1166 l_accum_acctd_amt NUMBER := 0;
1167 l_accum_line_amt NUMBER := 0;
1168 l_accum_tax_amt NUMBER := 0;
1169 l_accum_freight_amt NUMBER := 0;
1170 l_accum_charges_amt NUMBER := 0;
1171 l_accum_line_acctd_amt NUMBER := 0;
1172 l_accum_tax_acctd_amt NUMBER := 0;
1173 l_accum_freight_acctd_amt NUMBER := 0;
1174 l_accum_charges_acctd_amt NUMBER := 0;
1175 l_ctr NUMBER := 0;
1176 l_cm_amt NUMBER := 0;
1177 l_cm_acctd_amt NUMBER := 0;
1178 l_cm_line_amt NUMBER := 0;
1179 l_cm_tax_amt NUMBER := 0;
1180 l_cm_frt_amt NUMBER := 0;
1181 l_cm_chrg_amt NUMBER := 0;
1182 l_cached BOOLEAN;
1183 l_simul_activity VARCHAR2(1) := 'N';
1184 l_pay_ctr NUMBER := 0;
1185 l_all_br_closed VARCHAR2(1) := 'N';
1186
1187 BEGIN
1188
1189 IF PG_DEBUG in ('Y', 'C') THEN
1190 arp_standard.debug('ARP_RECONCILE.Reconcile_Trx_Tax ()+ ');
1191 arp_standard.debug('Reconcile_trx_tax: ' || 'Input Parameters ');
1192 arp_standard.debug('Reconcile_trx_tax: ' || 'p_customer_trx_id ' || p_customer_trx_id);
1193 arp_standard.debug('Reconcile_trx_tax: ' || 'p_br_cust_trx_line_id ' || p_br_cust_trx_line_id);
1194 arp_standard.debug('Reconcile_trx_tax: ' || 'p_calling_point ' || p_calling_point);
1195 END IF;
1196
1197 /*-----------------------------------------------------------------------------+
1198 | All chained Bills containing the current transaction assignment must be |
1199 | closed for this process to commence. The current Bill may be assigned to |
1200 | several other Bills so it is important to ensure that these Bills in turn |
1201 | are closed. This is because the deferred tax liability on the originating |
1202 | Bill is transfered to all Bills, so the starting Bill assignments |
1203 | representating deferred tax on the original Transactions must be reconciled |
1204 | only if this condition is true. |
1205 +----------------------------------------------------------------------------*/
1206 Check_all_bills_closed(p_customer_trx_id => p_customer_trx_id ,
1207 p_all_br_closed => l_all_br_closed );
1208
1209 /*---------------------------------------------------------------------------+
1210 |Since the deferred tax liability for this transaction exists on Bills which|
1211 |are still open |hence the transaction reconciliation entry will be created |
1212 |when the Bill is closed so do not process. |
1213 +---------------------------------------------------------------------------*/
1214 IF l_all_br_closed = 'N' THEN
1215 GOTO End_Transaction_Reconcile;
1216 END IF;
1217
1218 l_pay_tbl := l_pay_empty_tbl;
1219 l_pay_ctr := 0;
1220
1221 /*-----------------------------------------------------------------------------------------+
1222 |Level 1 Check.
1223 |Determine whether the Transaction being reconciled has CM applications which are non zero|
1224 |against it, if so then a flag is set to indicate that the reconciliation should be done |
1225 |by simulating non-CM activity as CM's have their own accounting, so no deferred tax is |
1226 |moved on application of the CM to the transaction, the deferred tax accounting is weighed|
1227 |by the accounting on the CM itself. Hence we reconcile against the actual deferred tax |
1228 |accounting createed against the simulated single non CM activity related accounting. |
1229 |In case there is no CM activity against a transaction, then we reconcile the actual |
1230 |deferred tax accounting created against the original tax accounting on the transaction. |
1231 |Since a user can create a CM (on account) and pay multiple deferred tax Transactions, we |
1232 |the tax accounting on the CM documents weighs the transactions to which it was applied. |
1233 +-----------------------------------------------------------------------------------------*/
1234 BEGIN
1235
1236 IF PG_DEBUG in ('Y', 'C') THEN
1237 arp_standard.debug('Reconcile_trx_tax: ' || 'Check CM applications for deferred tax reconciliation ()+ ');
1238 END IF;
1239
1240 l_cm_amt := 0; l_cm_acctd_amt := 0; l_cm_line_amt := 0;
1241 l_cm_tax_amt := 0; l_cm_frt_amt := 0; l_cm_chrg_amt := 0;
1242
1243 SELECT sum(nvl(app.amount_applied,0)) ,
1244 sum(nvl(app.acctd_amount_applied_to,0)),
1245 sum(nvl(app.line_applied,0)),
1246 sum(nvl(app.tax_applied,0)),
1247 sum(nvl(app.freight_applied,0)),
1248 sum(nvl(app.receivables_charges_applied,0))
1249 INTO l_cm_amt,
1250 l_cm_acctd_amt,
1251 l_cm_line_amt,
1252 l_cm_tax_amt,
1253 l_cm_frt_amt,
1254 l_cm_chrg_amt
1255 FROM ar_receivable_applications app
1256 WHERE app.applied_customer_trx_id = p_customer_trx_id
1257 AND app.application_type = 'CM'
1258 AND nvl(app.confirmed_flag, 'Y') = 'Y'
1259 AND app.status = 'APP';
1260
1261 IF ((l_cm_amt <> 0) OR (l_cm_acctd_amt <> 0) OR (l_cm_line_amt <> 0)
1262 OR (l_cm_tax_amt <> 0) OR (l_cm_frt_amt <> 0) OR (l_cm_chrg_amt <> 0)) THEN
1263 IF PG_DEBUG in ('Y', 'C') THEN
1264 arp_standard.debug('Reconcile_trx_tax: ' || 'Sum of CM applications to Transaction is not zero - simulate activity');
1265 END IF;
1266 l_simul_activity := 'Y';
1267 ELSE
1268 IF PG_DEBUG in ('Y', 'C') THEN
1269 arp_standard.debug('Reconcile_trx_tax: ' || 'Sum of CM applications is zero or no applications - do not simulate activity ');
1270 END IF;
1271 l_simul_activity := 'N';
1272 END IF;
1273
1274 IF PG_DEBUG in ('Y', 'C') THEN
1275 arp_standard.debug('Reconcile_trx_tax: ' || 'Check CM applications for deferred tax reconciliation ()- ');
1276 END IF;
1277
1278 EXCEPTION
1279 WHEN NO_DATA_FOUND THEN
1280 IF PG_DEBUG in ('Y', 'C') THEN
1281 arp_standard.debug('NO_DATA_FOUND : ARP_RECONCILE.Reconcile_trx_tax, CM applications do not exist ');
1282 END IF;
1283 l_simul_activity := 'N';
1284 IF PG_DEBUG in ('Y', 'C') THEN
1285 arp_standard.debug('Reconcile_trx_tax: ' || 'set l_simul_activity ' || l_simul_activity);
1286 END IF;
1287 WHEN OTHERS THEN
1288 IF PG_DEBUG in ('Y', 'C') THEN
1289 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Reconcile_trx_tax, in CM check applications sum');
1290 END IF;
1291 RAISE;
1292
1293 END; --block to determine CM activity on transaction
1294
1295
1296 /*-------------------------------------------------------------------------------------------+
1297 |Level 2 check. |
1298 |Check whether applications from CM to Transaction are not zero if so then the CM has been |
1299 |applied to other transactions, and has in effect reduced its deferred tax amounts by tax |
1300 |code - since we do not create deferred tax movements on CM application to Trx, hence we |
1301 |simulate non CM activity on the CM which resulted in reducing its payment schedule balance.|
1302 |to reconcile its deferred tax. |
1303 +-------------------------------------------------------------------------------------------*/
1304 IF (l_simul_activity = 'N') AND (p_pay_class = 'CM') THEN
1305 BEGIN
1306
1307 IF PG_DEBUG in ('Y', 'C') THEN
1308 arp_standard.debug('Reconcile_trx_tax: ' || 'Check applications from CM to Transactions ()+ ');
1309 END IF;
1310
1311 l_cm_amt := 0; l_cm_acctd_amt := 0; l_cm_line_amt := 0;
1312 l_cm_tax_amt := 0; l_cm_frt_amt := 0; l_cm_chrg_amt := 0;
1313
1314 SELECT sum(nvl(app.amount_applied,0)) ,
1315 sum(nvl(app.acctd_amount_applied_to,0)),
1316 sum(nvl(app.line_applied,0)),
1317 sum(nvl(app.tax_applied,0)),
1318 sum(nvl(app.freight_applied,0)),
1319 sum(nvl(app.receivables_charges_applied,0))
1320 INTO l_cm_amt,
1321 l_cm_acctd_amt,
1322 l_cm_line_amt,
1323 l_cm_tax_amt,
1324 l_cm_frt_amt,
1325 l_cm_chrg_amt
1326 FROM ar_receivable_applications app
1327 WHERE app.customer_trx_id = p_customer_trx_id
1328 AND app.application_type = 'CM'
1329 AND nvl(app.confirmed_flag, 'Y') = 'Y'
1330 AND app.status = 'APP';
1331
1332 IF ((l_cm_amt <> 0) OR (l_cm_acctd_amt <> 0) OR (l_cm_line_amt <> 0)
1333 OR (l_cm_tax_amt <> 0) OR (l_cm_frt_amt <> 0) OR (l_cm_chrg_amt <> 0)) THEN
1334 IF PG_DEBUG in ('Y', 'C') THEN
1335 arp_standard.debug('Reconcile_trx_tax: ' || 'Applications from CM to Transaction are not zero - simulate activity');
1336 END IF;
1337 l_simul_activity := 'Y';
1338 ELSE
1339 IF PG_DEBUG in ('Y', 'C') THEN
1340 arp_standard.debug('Reconcile_trx_tax: ' || 'Applications from CM to Transaction are zero - do not simulate activity ');
1341 END IF;
1342 l_simul_activity := 'N';
1343 END IF;
1344
1345 IF PG_DEBUG in ('Y', 'C') THEN
1346 arp_standard.debug('Reconcile_trx_tax: ' || 'Check applications from CM to Transactions ()- ');
1347 END IF;
1348
1349 EXCEPTION
1350 WHEN NO_DATA_FOUND THEN
1351 IF PG_DEBUG in ('Y', 'C') THEN
1352 arp_standard.debug('NO_DATA_FOUND : ARP_RECONCILE.Reconcile_trx_tax, Applications from CM ' ||
1353 'to Transaction do not exist ');
1354 END IF;
1355 l_simul_activity := 'N';
1356 IF PG_DEBUG in ('Y', 'C') THEN
1357 arp_standard.debug('Reconcile_trx_tax: ' || 'set l_simul_activity ' || l_simul_activity);
1358 END IF;
1359 WHEN OTHERS THEN
1360 IF PG_DEBUG in ('Y', 'C') THEN
1361 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Reconcile_trx_tax, in CM applications ' ||
1362 'from CM to Transaction');
1363 END IF;
1364 RAISE;
1365
1366 END; --block to determine applications from CM to other transactions
1367 END IF;
1368
1369 /*--------------------------------------------------------------------+
1370 |Cache the payment schedule details into the table if already cached |
1371 +--------------------------------------------------------------------*/
1372 IF PG_DEBUG in ('Y', 'C') THEN
1373 arp_standard.debug('Reconcile_trx_tax: ' || 'Processing Non CM activity ');
1374 END IF;
1375
1376 IF (l_simul_activity = 'Y') THEN
1377
1378 IF PG_DEBUG in ('Y', 'C') THEN
1379 arp_standard.debug('Reconcile_trx_tax: ' || 'l_simul_activity ' || l_simul_activity);
1380 END IF;
1381
1382 FOR l_activity IN get_single_activity LOOP
1383
1384 IF PG_DEBUG in ('Y', 'C') THEN
1385 arp_standard.debug('Reconcile_trx_tax: ' || 'In loop get single activity');
1386 END IF;
1387
1388 l_cached := FALSE;
1389
1390 IF l_pay_tbl.EXISTS(l_pay_ctr) THEN
1391
1392 FOR l_ctr IN l_pay_tbl.FIRST .. l_pay_tbl.LAST LOOP
1393
1394 IF (l_pay_tbl(l_ctr).applied_payment_schedule_id = l_activity.payment_schedule_id)
1395 THEN
1396 IF PG_DEBUG in ('Y', 'C') THEN
1397 arp_standard.debug('Reconcile_trx_tax: ' || '5) Hit found in cache');
1398 END IF;
1399
1400 --Set the application record buckets
1401 l_pay_tbl(l_ctr).applied_customer_trx_id := p_customer_trx_id;
1402
1403 l_pay_tbl(l_ctr).applied_payment_schedule_id := l_activity.payment_schedule_id;
1404
1405 l_pay_tbl(l_ctr).amount_applied :=
1406 l_pay_tbl(l_ctr).amount_applied + l_activity.amount ;
1407
1408 l_pay_tbl(l_ctr).acctd_amount_applied_to :=
1409 l_pay_tbl(l_ctr).acctd_amount_applied_to + l_activity.acctd_amount ;
1410
1411 l_pay_tbl(l_ctr).line_applied :=
1412 l_pay_tbl(l_ctr).line_applied +l_activity.line_amount ;
1413
1414 l_pay_tbl(l_ctr).tax_applied :=
1415 l_pay_tbl(l_ctr).tax_applied + l_activity.tax_amount ;
1416
1417 l_pay_tbl(l_ctr).freight_applied :=
1418 l_pay_tbl(l_ctr).freight_applied + l_activity.freight_amount ;
1419
1420 l_pay_tbl(l_ctr).receivables_charges_applied :=
1421 l_pay_tbl(l_ctr).receivables_charges_applied + l_activity.receivables_charges_amount ;
1422
1423 l_cached := TRUE;
1424
1425 IF PG_DEBUG in ('Y', 'C') THEN
1426 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').applied_customer_trx_id = '||l_pay_tbl(l_ctr).applied_customer_trx_id);
1427 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').applied_payment_schedule_id = '||l_pay_tbl(l_ctr).applied_payment_schedule_id);
1428 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').amount_applied = '||l_pay_tbl(l_ctr).amount_applied);
1429 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').acctd_amount_applied_to = '||l_pay_tbl(l_ctr).acctd_amount_applied_to);
1430 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').line_applied = '||l_pay_tbl(l_ctr).line_applied);
1431 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').tax_applied= '||l_pay_tbl(l_ctr).tax_applied);
1432 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').freight_applied= '||l_pay_tbl(l_ctr).freight_applied);
1433 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').receivables_charges_applied= '||l_pay_tbl(l_ctr).receivables_charges_applied);
1434 END IF;
1435 END IF; --add to cache
1436
1437 END LOOP; --process cached lines in payment table
1438
1439 END IF; --payment schedule amounts table exists
1440
1441 /*------------------------------------------------------------------------+
1442 |Cache the payment schedule details into the table if not already cached |
1443 +------------------------------------------------------------------------*/
1444 IF (NOT l_cached) THEN
1445
1446 IF PG_DEBUG in ('Y', 'C') THEN
1447 arp_standard.debug('Reconcile_trx_tax: ' || '5) Now caching');
1448 END IF;
1449
1450 l_pay_ctr := l_pay_ctr + 1;
1451
1452 --Set the application record buckets
1453 l_pay_tbl(l_pay_ctr).applied_customer_trx_id := p_customer_trx_id;
1454 l_pay_tbl(l_pay_ctr).applied_payment_schedule_id := l_activity.payment_schedule_id;
1455 l_pay_tbl(l_pay_ctr).amount_applied := l_activity.amount ;
1456 l_pay_tbl(l_pay_ctr).acctd_amount_applied_to := l_activity.acctd_amount ;
1457 l_pay_tbl(l_pay_ctr).line_applied := l_activity.line_amount ;
1458 l_pay_tbl(l_pay_ctr).tax_applied := l_activity.tax_amount ;
1459 l_pay_tbl(l_pay_ctr).freight_applied := l_activity.freight_amount ;
1460 l_pay_tbl(l_pay_ctr).receivables_charges_applied := l_activity.receivables_charges_amount ;
1461
1462 l_cached := TRUE;
1463
1464 IF PG_DEBUG in ('Y', 'C') THEN
1465 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').applied_customer_trx_id = '||l_pay_tbl(l_pay_ctr).applied_customer_trx_id);
1466 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').applied_payment_schedule_id = '||l_pay_tbl(l_pay_ctr).applied_payment_schedule_id);
1467 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').amount_applied = '||l_pay_tbl(l_pay_ctr).amount_applied);
1468 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').acctd_amount_applied_to = '||l_pay_tbl(l_pay_ctr).acctd_amount_applied_to);
1469 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').line_applied = '||l_pay_tbl(l_pay_ctr).line_applied);
1470 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').tax_applied= '||l_pay_tbl(l_pay_ctr).tax_applied);
1471 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').freight_applied= '||l_pay_tbl(l_pay_ctr).freight_applied);
1472 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').receivables_charges_applied= '||l_pay_tbl(l_pay_ctr).receivables_charges_applied);
1473 END IF;
1474
1475 END IF; --not cached
1476
1477 END LOOP; --process all activities for the current transaction
1478
1479 END IF; --Simulate activity on document
1480
1481 /*--------------------------------------------------------------------------+
1482 | Call the common Routine, to simulate an application for activity by |
1483 | payment schedule.To reconcile against the actual accounting created in |
1484 | the distributions accounting table. |
1485 +--------------------------------------------------------------------------*/
1486 Process_Recon( p_mode => p_mode ,
1487 p_ae_doc_rec => p_ae_doc_rec ,
1488 p_ae_event_rec => p_ae_event_rec ,
1489 p_ae_sys_rec => p_ae_sys_rec ,
1490 p_cust_inv_rec => p_cust_inv_rec ,
1491 p_br_cust_trx_line_id => p_br_cust_trx_line_id,
1492 p_customer_trx_id => p_customer_trx_id,
1493 p_simul_app => l_simul_activity ,
1494 p_calling_point => p_calling_point ,
1495 p_pay_ctr => l_pay_ctr ,
1496 p_pay_tbl => l_pay_tbl ,
1497 p_g_ae_ctr => p_g_ae_ctr ,
1498 p_g_ae_line_tbl => p_g_ae_line_tbl );
1499
1500 <<End_Transaction_Reconcile>>
1501 IF PG_DEBUG in ('Y', 'C') THEN
1502 arp_standard.debug('ARP_RECONCILE.Reconcile_trx_tax ()- ');
1503 END IF;
1504
1505 EXCEPTION
1506 WHEN NO_DATA_FOUND THEN
1507 IF PG_DEBUG in ('Y', 'C') THEN
1508 arp_standard.debug('EXCEPTION NO_DATA_FOUND: ARP_RECONCILE.Reconcile_trx_tax ');
1509 END IF;
1510 RAISE;
1511
1512 WHEN OTHERS THEN
1513 IF PG_DEBUG in ('Y', 'C') THEN
1514 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Reconcile_trx_tax ');
1515 END IF;
1516 RAISE;
1517
1518 END Reconcile_trx_tax;
1519
1520 /*========================================================================
1521 | PRIVATE PROCEDURE Reconcile_br_tax
1522 |
1523 | DESCRIPTION
1524 | Reconciles each assignment on the Bill. Reconciliation is done
1525 | only if the Bill is closed and all chained Bills are also closed.
1526 | If an assignment is a Bill then this function is called recursively
1527 | to go to the child bill and start processing with the same condition
1528 | checks as was done for the parent bill.
1529 |
1530 | PARAMETERS
1531 | p_mode IN Document or Accounting Event mode
1532 | p_ae_doc_rec IN Document Record
1533 | p_ae_event_rec IN Event Record
1534 | p_ae_sys_rec IN System parameter details
1535 | p_customer_trx_id IN Bills Receivable trx id ,
1536 | p_g_ae_ctr IN OUT NOCOPY counter for global accounting lines table
1537 | p_g_ae_line_tbl IN OUT NOCOPY accounting lines table containing reconciled
1538 | entry
1539 *=======================================================================*/
1540 PROCEDURE Reconcile_br_tax(
1541 p_mode IN VARCHAR2 ,
1542 p_ae_doc_rec IN ae_doc_rec_type ,
1543 p_ae_event_rec IN ae_event_rec_type ,
1544 p_ae_sys_rec IN ae_sys_rec_type ,
1545 p_customer_trx_id IN NUMBER ,
1546 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
1547 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type ) IS
1548
1549 CURSOR get_assignments(p_customer_trx_id IN NUMBER) is
1550 SELECT ctl.customer_trx_id br_cust_trx_id ,
1551 ctl.customer_trx_line_id br_customer_trx_line_id ,
1552 ctl.br_ref_customer_trx_id br_ref_customer_trx_id ,
1553 ctl.br_ref_payment_schedule_id br_ref_payment_schedule_id ,
1554 ct.drawee_site_use_id drawee_site_use_id ,
1555 ct.invoice_currency_code invoice_currency_code ,
1556 ct.exchange_rate exchange_rate,
1557 ct.exchange_rate_type exchange_rate_type,
1558 ct.exchange_date exchange_date,
1559 ct.trx_date trx_date ,
1560 ct.bill_to_customer_id bill_to_customer_id ,
1561 ct.bill_to_site_use_id bill_to_site_use_id ,
1562 adj.adjustment_id br_adj_id ,
1563 nvl(adj.amount,0) br_adj_amt ,
1564 nvl(adj.acctd_amount,0) br_adj_acctd_amt ,
1565 nvl(adj.line_adjusted,0) br_adj_line_amt ,
1566 nvl(adj.tax_adjusted,0) br_adj_tax_amt ,
1567 nvl(adj.freight_adjusted,0) br_adj_frt_amt ,
1568 nvl(adj.receivables_charges_adjusted,0) br_adj_chrg_amt
1569 FROM ra_customer_trx_lines ctl,
1570 ar_adjustments adj,
1571 ra_customer_trx ct
1572 WHERE ctl.customer_trx_id = p_customer_trx_id
1573 AND ctl.br_adjustment_id = adj.adjustment_id
1574 AND ct.customer_trx_id = ctl.br_ref_customer_trx_id
1575 AND adj.status = 'A'
1576 order by ctl.customer_trx_line_id;
1577
1578 TYPE l_br_rec_type IS RECORD (
1579 br_cust_trx_id NUMBER,
1580 br_customer_trx_line_id NUMBER,
1581 br_ref_customer_trx_id NUMBER,
1582 br_ref_payment_schedule_id NUMBER,
1583 drawee_site_use_id ra_customer_trx.drawee_site_use_id%TYPE,
1584 br_adj_id NUMBER,
1585 br_adj_amt NUMBER,
1586 br_adj_acctd_amt NUMBER,
1587 br_adj_line_amt NUMBER,
1588 br_adj_tax_amt NUMBER,
1589 br_adj_frt_amt NUMBER,
1590 br_adj_chrg_amt NUMBER,
1591 br_adj_line_acctd_amt NUMBER,
1592 br_adj_tax_acctd_amt NUMBER,
1593 br_adj_frt_acctd_amt NUMBER,
1594 br_adj_chrg_acctd_amt NUMBER
1595 );
1596
1597 TYPE l_br_tbl_type IS TABLE of l_br_rec_type
1598 INDEX BY BINARY_INTEGER;
1599
1600 l_cust_inv_rec ra_customer_trx%ROWTYPE;
1601
1602 l_assn_ctr BINARY_INTEGER := 0;
1603
1604 l_pay_tbl g_pay_tbl_type;
1605
1606 l_pay_empty_tbl g_pay_tbl_type;
1607
1608 l_pay_ctr BINARY_INTEGER := 0;
1609
1610 l_br_tbl l_br_tbl_type;
1611
1612 l_app_rec ar_receivable_applications%ROWTYPE;
1613
1614 --The bill closed flag is defaulted to N
1615 l_all_br_closed VARCHAR2(1) := 'N';
1616 l_pay_class ar_payment_schedules.class%TYPE;
1617 l_closed_pymt_yn VARCHAR2(1);
1618
1619 l_required BOOLEAN;
1620
1621 BEGIN
1622 IF PG_DEBUG in ('Y', 'C') THEN
1623 arp_standard.debug('ARP_RECONCILE.Reconcile_br_tax ()+');
1624 END IF;
1625
1626 /*----------------------------------------------------------------------------------------+
1627 | All chained Bills starting from the current Bill must be closed for the Reconciliation |
1628 | process to commence. The current Bill may be assigned to several other Bills so it is |
1629 | important to ensure that thes Bills in turn are closed. This is because the deferred |
1630 | tax liability on the originating Bill is transfered to all Bills, so the starting Bill |
1631 | assignments representating deferred tax on the original Transactions must be reconciled|
1632 | only if this condition is true. |
1633 +----------------------------------------------------------------------------------------*/
1634 Check_all_bills_closed(p_customer_trx_id => p_customer_trx_id ,
1635 p_all_br_closed => l_all_br_closed );
1636
1637 IF (l_all_br_closed = 'Y') THEN
1638
1639 /*----------------------------------------------------------------------------------+
1640 | Get the shadow adjustments record for usage by the tax accounting engine to |
1641 | create deferred tax accounting as though a single application was made to each |
1642 | shadow adjustment (transaction assignment). |
1643 +----------------------------------------------------------------------------------*/
1644 FOR l_assign_rec IN get_assignments(p_customer_trx_id) LOOP
1645
1646 l_assn_ctr := l_assn_ctr + 1;
1647
1648 l_br_tbl(l_assn_ctr).br_cust_trx_id := l_assign_rec.br_cust_trx_id;
1649 l_br_tbl(l_assn_ctr).br_customer_trx_line_id := l_assign_rec.br_customer_trx_line_id;
1650 l_br_tbl(l_assn_ctr).br_ref_customer_trx_id := l_assign_rec.br_ref_customer_trx_id;
1651 l_br_tbl(l_assn_ctr).br_ref_payment_schedule_id := l_assign_rec.br_ref_payment_schedule_id;
1652 l_br_tbl(l_assn_ctr).drawee_site_use_id := l_assign_rec.drawee_site_use_id;
1653 l_br_tbl(l_assn_ctr).br_adj_id := l_assign_rec.br_adj_id;
1654 l_br_tbl(l_assn_ctr).br_adj_amt := l_assign_rec.br_adj_amt;
1655 l_br_tbl(l_assn_ctr).br_adj_acctd_amt := l_assign_rec.br_adj_acctd_amt;
1656 l_br_tbl(l_assn_ctr).br_adj_line_amt := l_assign_rec.br_adj_line_amt;
1657 l_br_tbl(l_assn_ctr).br_adj_tax_amt := l_assign_rec.br_adj_tax_amt;
1658 l_br_tbl(l_assn_ctr).br_adj_frt_amt := l_assign_rec.br_adj_frt_amt;
1659 l_br_tbl(l_assn_ctr).br_adj_chrg_amt := l_assign_rec.br_adj_chrg_amt;
1660
1661 /*----------------------------------------------------------------------------------+
1662 | Derive the currency, exchange rate and third party information. Assignments on |
1663 | a bill could have different third part and third party sub id information, hence |
1664 | we rederive it. The currency and exchange rate details of assignments match Bill |
1665 +----------------------------------------------------------------------------------*/
1666 l_cust_inv_rec.invoice_currency_code := l_assign_rec.invoice_currency_code;
1667 l_cust_inv_rec.exchange_rate := l_assign_rec.exchange_rate;
1668 l_cust_inv_rec.exchange_rate_type := l_assign_rec.exchange_rate_type;
1669 l_cust_inv_rec.exchange_date := l_assign_rec.exchange_date;
1670 l_cust_inv_rec.trx_date := l_assign_rec.trx_date;
1671 l_cust_inv_rec.bill_to_customer_id := l_assign_rec.bill_to_customer_id;
1672 l_cust_inv_rec.bill_to_site_use_id := l_assign_rec.bill_to_site_use_id;
1673
1674 /*------------------------------------------------------------------------------+
1675 | Now create a application to simulate a single activity such as a payment to |
1676 | each shadow adjustment (transaction assignment) on the Bill. |
1677 +------------------------------------------------------------------------------*/
1678 l_pay_tbl := l_pay_empty_tbl;
1679 l_pay_ctr := 1; --always for each recursive call for a Bill to this routine
1680
1681 --Set the application record buckets
1682 l_pay_tbl(l_pay_ctr).applied_customer_trx_id := l_br_tbl(l_assn_ctr).br_ref_customer_trx_id;
1683 l_pay_tbl(l_pay_ctr).applied_payment_schedule_id := l_br_tbl(l_assn_ctr).br_ref_payment_schedule_id;
1684 l_pay_tbl(l_pay_ctr).amount_applied := l_br_tbl(l_assn_ctr).br_adj_amt * -1;
1685 l_pay_tbl(l_pay_ctr).acctd_amount_applied_to := l_br_tbl(l_assn_ctr).br_adj_acctd_amt * -1;
1686 l_pay_tbl(l_pay_ctr).line_applied := l_br_tbl(l_assn_ctr).br_adj_line_amt * -1;
1687 l_pay_tbl(l_pay_ctr).tax_applied := l_br_tbl(l_assn_ctr).br_adj_tax_amt * -1;
1688 l_pay_tbl(l_pay_ctr).freight_applied := l_br_tbl(l_assn_ctr).br_adj_frt_amt * -1;
1689 l_pay_tbl(l_pay_ctr).receivables_charges_applied := l_br_tbl(l_assn_ctr).br_adj_chrg_amt * -1;
1690
1691 IF PG_DEBUG in ('Y', 'C') THEN
1692 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP applied_customer_trx_id ' || l_pay_tbl(l_pay_ctr).applied_customer_trx_id);
1693 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP applied_payment_schedule_id ' || l_pay_tbl(l_pay_ctr).applied_payment_schedule_id);
1694 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP amount_applied ' || l_pay_tbl(l_pay_ctr).amount_applied);
1695 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP acctd_amount_applied_to ' || l_pay_tbl(l_pay_ctr).acctd_amount_applied_to);
1696 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP line_applied ' || l_pay_tbl(l_pay_ctr).line_applied);
1697 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP tax_applied ' || l_pay_tbl(l_pay_ctr).tax_applied);
1698 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP freight_applied ' || l_pay_tbl(l_pay_ctr).freight_applied);
1699 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP receivables_charges_applied ' || l_pay_tbl(l_pay_ctr).receivables_charges_applied);
1700 arp_standard.debug('Reconcile_br_tax: ' || 'Drawee Site Id ' || l_br_tbl(l_assn_ctr).drawee_site_use_id);
1701 END IF;
1702
1703 /*--------------------------------------------------------------------------+
1704 | Recursive call required because current assignment is a bill of exchange.|
1705 | So the process of verifying that all Bills to which the current Bill is |
1706 | assigned are closed, so that the assignments on the Bill will be |
1707 | reconciled to simulate an application against each assignments shadow |
1708 | adjustment. |
1709 +--------------------------------------------------------------------------*/
1710 IF l_br_tbl(l_assn_ctr).drawee_site_use_id IS NOT NULL THEN
1711
1712 Reconcile_br_tax( p_mode => p_mode ,
1713 p_ae_doc_rec => p_ae_doc_rec ,
1714 p_ae_event_rec => p_ae_event_rec ,
1715 p_ae_sys_rec => p_ae_sys_rec ,
1716 p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1717 p_g_ae_ctr => p_g_ae_ctr ,
1718 p_g_ae_line_tbl => p_g_ae_line_tbl );
1719 ELSE
1720 /*--------------------------------------------------------------------------+
1721 | If the Transaction is not deferred then do not do any process as there is|
1722 | no deferred tax to reconcile. |
1723 +--------------------------------------------------------------------------*/
1724 Check_Entry(p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1725 p_type => 'TRAN',
1726 p_required => l_required );
1727
1728 IF (l_required) THEN
1729
1730 /*--------------------------------------------------------------------------+
1731 | Call the common Routine, to simulate an application for each adjustment |
1732 | on the bills line therby deriving accounting for single activity on Bills|
1733 | line. Subsequently retrieve accounting actually created from accounting |
1734 | table due to past activities and Reconcile what has been created with. |
1735 | what should have been created if there were single applications to each |
1736 | assignment. |
1737 +--------------------------------------------------------------------------*/
1738 Process_Recon( p_mode => p_mode ,
1739 p_ae_doc_rec => p_ae_doc_rec ,
1740 p_ae_event_rec => p_ae_event_rec ,
1741 p_ae_sys_rec => p_ae_sys_rec ,
1742 p_cust_inv_rec => l_cust_inv_rec ,
1743 p_br_cust_trx_line_id => l_br_tbl(l_assn_ctr).br_customer_trx_line_id,
1744 p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1745 p_simul_app => 'Y' ,
1746 p_calling_point => 'BILL' ,
1747 p_pay_ctr => l_pay_ctr ,
1748 p_pay_tbl => l_pay_tbl ,
1749 p_g_ae_ctr => p_g_ae_ctr ,
1750 p_g_ae_line_tbl => p_g_ae_line_tbl );
1751
1752 /*----------------------------------------------------------------------+
1753 | Determine whether the payment schedule of the Transaction is closed, |
1754 | only on closure do we need to create the Reconciliation entry. |
1755 +----------------------------------------------------------------------*/
1756 Detect_Closure(p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1757 p_pay_sched_upd_yn => 'Y',
1758 p_pay_sched_upd_cm_yn => null,
1759 p_activity_amt => 0 ,
1760 p_activity_acctd_amt => 0 ,
1761 p_ae_sys_rec => p_ae_sys_rec ,
1762 p_closed_pymt_yn => l_closed_pymt_yn,
1763 p_pay_class => l_pay_class );
1764
1765 /*--------------------------------------------------------------------------+
1766 | Call the Transaction Reconciliation routine for this assignment. This is |
1767 | necessary because the transaction which is closed and assigned to another|
1768 | Bill needs to be Reconciled after the Bills assignment reconciliation |
1769 | entry is built. The transaction reconciliation routine is called only if |
1770 | all Bills to which it has been assigned are also closed in addition to |
1771 | it being closed. It is is also important that the Transaction assignment |
1772 | must be closed to Reconcile it. |
1773 +--------------------------------------------------------------------------*/
1774 IF (l_closed_pymt_yn = 'Y') THEN
1775 Reconcile_trx_tax(p_mode => p_mode ,
1776 p_ae_doc_rec => p_ae_doc_rec ,
1777 p_ae_event_rec => p_ae_event_rec ,
1778 p_ae_sys_rec => p_ae_sys_rec ,
1779 p_cust_inv_rec => l_cust_inv_rec ,
1780 p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1781 p_br_cust_trx_line_id => l_br_tbl(l_assn_ctr).br_customer_trx_line_id,
1782 p_calling_point => 'BLTR' ,
1783 p_pay_class => l_pay_class ,
1784 p_g_ae_ctr => p_g_ae_ctr ,
1785 p_g_ae_line_tbl => p_g_ae_line_tbl );
1786
1787 END IF; --payment schedule of Trx is closed
1788
1789 END IF; -- processing required for deferred transaction
1790
1791 END IF; --drawee site is not null
1792
1793 END LOOP; --process each shadow adjustment
1794
1795 END IF; --All chained bills are closed
1796
1797 IF PG_DEBUG in ('Y', 'C') THEN
1798 arp_standard.debug('ARP_RECONCILE.Reconcile_br_tax ()-');
1799 END IF;
1800
1801 EXCEPTION
1802 WHEN NO_DATA_FOUND THEN
1803 IF PG_DEBUG in ('Y', 'C') THEN
1804 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Reconcile_br_tax ');
1805 END IF;
1806 RAISE;
1807
1808 WHEN OTHERS THEN
1809 IF PG_DEBUG in ('Y', 'C') THEN
1810 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Reconcile_br_tax ');
1811 END IF;
1812 RAISE;
1813
1814 END Reconcile_br_tax;
1815
1816 /* ==========================================================================
1817 | PROCEDURE Detect_Closure
1818 |
1819 | DESCRIPTION
1820 | This routine detects whether a transaction is closed. Closure is defined
1821 | as a point where the sum total for the amount due remaining and the
1822 | accounted amount due remaining is zero for all installments on the
1823 | Bill or Transaction. This routine passes a flag indicating as to whether
1824 | reconciliation is required.
1825 |
1826 | SCOPE - PRIVATE
1827 |
1828 | PARAMETERS
1829 | p_customer_trx_id Transaction identifier
1830 | p_pay_sched_upd_yn Value denotes whether the payment
1831 | schedule been updated or not, if not
1832 | then this routine will add the activity
1833 | on the Bill or transaction to the
1834 | installments
1835 | to make this decision
1836 | p_activity_amt previous activity amount
1837 | p_activity_acctd_amt previous activity accounted amount
1838 | p_closed_pymt_yn A Y value indicates that the Bill or
1839 | transaction is a candidate for
1840 | reconciliation
1841 *==========================================================================*/
1842 PROCEDURE Detect_Closure(p_customer_trx_id IN NUMBER ,
1843 p_pay_sched_upd_yn IN VARCHAR2 ,
1844 p_pay_sched_upd_cm_yn IN VARCHAR2 ,
1845 p_activity_amt IN NUMBER ,
1846 p_activity_acctd_amt IN NUMBER ,
1847 p_ae_sys_rec IN ae_sys_rec_type,
1848 p_closed_pymt_yn OUT NOCOPY VARCHAR2 ,
1849 p_pay_class OUT NOCOPY VARCHAR2 ) IS
1850
1851 l_amount_due_remaining NUMBER := 0;
1852 l_acctd_amount_due_remaining NUMBER := 0;
1853 l_pay_sched_upd_yn VARCHAR2(1);
1854
1855 BEGIN
1856 IF PG_DEBUG in ('Y', 'C') THEN
1857 arp_standard.debug('ARP_RECONCILE.Detect_Closure()+');
1858 END IF;
1859
1860 /*---------------------------------------------------------------------------+
1861 | Retrieve amount and accounted amount remaining for all installments on the|
1862 | Transaction |
1863 +---------------------------------------------------------------------------*/
1864
1865 IF (p_ae_sys_rec.sob_type = 'P') THEN
1866 select sum(pay.amount_due_remaining) ,
1867 sum(pay.acctd_amount_due_remaining) ,
1868 max(pay.class)
1869 into l_amount_due_remaining,
1870 l_acctd_amount_due_remaining,
1871 p_pay_class
1872 from ar_payment_schedules pay
1873 where pay.customer_trx_id = p_customer_trx_id;
1874 END IF;
1875 IF PG_DEBUG in ('Y', 'C') THEN
1876 arp_standard.debug('Detect_Closure: ' || 'p_customer_trx_id ' || p_customer_trx_id);
1877 arp_standard.debug('Detect_Closure: ' || 'pay_class ' || p_pay_class);
1878 arp_standard.debug('Detect_Closure: ' || 'p_pay_sched_upd_yn ' || p_pay_sched_upd_yn);
1879 arp_standard.debug('Detect_Closure: ' || 'p_pay_sched_upd_cm_yn ' || p_pay_sched_upd_cm_yn);
1880 arp_standard.debug('Detect_Closure: ' || 'g_call_num ' || g_call_num);
1881 arp_standard.debug('Detect_Closure: ' || 'Parameter p_activity_amt ' || p_activity_amt);
1882 arp_standard.debug('Detect_Closure: ' || 'Parameter p_activity_acctd_amt ' || p_activity_acctd_amt);
1883 arp_standard.debug('Detect_Closure: ' || 'Selected pay l_amount_due_remaining ' || l_amount_due_remaining);
1884 arp_standard.debug('Detect_Closure: ' || 'Selected pay l_acctd_amount_due_remaining ' || l_acctd_amount_due_remaining);
1885 END IF;
1886
1887 --Set the payment schedule updated flag
1888 IF (p_pay_class = 'CM') AND (g_call_num = 2) AND (p_pay_sched_upd_cm_yn IS NOT NULL) THEN
1889 l_pay_sched_upd_yn := p_pay_sched_upd_cm_yn;
1890 ELSE
1891 l_pay_sched_upd_yn := p_pay_sched_upd_yn;
1892 END IF;
1893
1894 /*---------------------------------------------------------------------------+
1895 | Add this to the amount and accounted amount due to activity. Zero amounts |
1896 | will indicate that the Transaction has been closed. |
1897 +--------------------------------------------------------------------------*/
1898 IF (NVL(l_pay_sched_upd_yn, 'N') = 'N') THEN
1899 l_amount_due_remaining := l_amount_due_remaining + p_activity_amt;
1900 l_acctd_amount_due_remaining := l_acctd_amount_due_remaining +
1901 p_activity_acctd_amt;
1902
1903 IF PG_DEBUG in ('Y', 'C') THEN
1904 arp_standard.debug('Detect_Closure: ' || 'Payment schedule not updated hence calculating remaining amounts ');
1905 arp_standard.debug('Detect_Closure: ' || 'l_amount_due_remaining + p_activity_amt ' || l_amount_due_remaining);
1906 arp_standard.debug('Detect_Closure: ' || 'l_acctd_amount_due_remaining + p_activity_acctd_amt ' || l_acctd_amount_due_remaining);
1907 END IF;
1908 END IF;
1909
1910 /*---------------------------------------------------------------------+
1911 | Set the payment schedule closed flag to indicate as to whether |
1912 | reconciliation is required. |
1913 +---------------------------------------------------------------------*/
1914
1915 IF ((l_amount_due_remaining + l_acctd_amount_due_remaining) <> 0) THEN
1916 p_closed_pymt_yn := 'N'; --paymentschedule is not closed so do not call reconciliation routine
1917 IF PG_DEBUG in ('Y', 'C') THEN
1918 arp_standard.debug('Detect_Closure: ' || 'Transaction ' || p_customer_trx_id || ' payment schedule is not closed - do not reconcile ');
1919 END IF;
1920 ELSE
1921 p_closed_pymt_yn := 'Y';
1922 IF PG_DEBUG in ('Y', 'C') THEN
1923 arp_standard.debug('Detect_Closure: ' || 'Transaction ' || p_customer_trx_id || ' payment schedule is closed - reconcile ');
1924 END IF;
1925 END IF;
1926
1927 <<end_process_lbl1>>
1928
1929 IF PG_DEBUG in ('Y', 'C') THEN
1930 arp_standard.debug('ARP_RECONCILE.Detect_Closure()-');
1931 END IF;
1932
1933 EXCEPTION
1934 WHEN NO_DATA_FOUND THEN
1935 IF PG_DEBUG in ('Y', 'C') THEN
1936 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Detect_Closure ');
1937 END IF;
1938 RAISE;
1939
1940 WHEN OTHERS THEN
1941 IF PG_DEBUG in ('Y', 'C') THEN
1942 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Detect_Closure ');
1943 END IF;
1944 RAISE;
1945
1946 END Detect_Closure;
1947
1948 /* ==========================================================================
1949 | PROCEDURE Assign_Elements
1950 |
1951 | DESCRIPTION
1952 | Assign revenue or tax lines built to global table which will eventually
1953 | be summarized
1954 |
1955 | SCOPE - PRIVATE
1956 |
1957 | PARAMETERS
1958 | NONE
1959 *==========================================================================*/
1960 PROCEDURE Assign_Elements(p_ae_line_rec IN OUT NOCOPY ae_line_rec_type ,
1961 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
1962 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type ) IS
1963
1964 BEGIN
1965 IF PG_DEBUG in ('Y', 'C') THEN
1966 arp_standard.debug('ARP_RECONCILE.Assign_Elements()+');
1967 END IF;
1968
1969 /*--------------------------------------------------------------------------------+
1970 | Do not create 0 amount Reconciliation entries if tax and taxable amounts are 0 |
1971 +--------------------------------------------------------------------------------*/
1972 IF ((nvl(p_ae_line_rec.entered_dr,0) + nvl(p_ae_line_rec.entered_cr,0) +
1973 nvl(p_ae_line_rec.accounted_dr,0) + nvl(p_ae_line_rec.accounted_cr,0) +
1974 nvl(p_ae_line_rec.taxable_entered_dr,0) + nvl(p_ae_line_rec.taxable_entered_cr,0) +
1975 nvl(p_ae_line_rec.taxable_accounted_dr,0) + nvl(p_ae_line_rec.taxable_accounted_cr,0)) = 0)
1976 THEN
1977 GOTO end_assign_elements;
1978 END IF;
1979
1980 /*--------------------------------------------------------------------------------+
1981 | 1) Populate 0 values for the accounted amounts based on the sign of the amounts|
1982 +--------------------------------------------------------------------------------*/
1983 --Populate a 0 amount if the other bucket is null for accounted amounts
1984 IF ((p_ae_line_rec.entered_dr IS NOT NULL)
1985 AND (p_ae_line_rec.accounted_dr IS NULL) AND (p_ae_line_rec.accounted_cr IS NULL)) THEN
1986 p_ae_line_rec.accounted_dr := 0;
1987 ELSIF ((p_ae_line_rec.entered_cr IS NOT NULL)
1988 AND (p_ae_line_rec.accounted_cr IS NULL) AND (p_ae_line_rec.accounted_dr IS NULL)) THEN
1989 p_ae_line_rec.accounted_cr := 0;
1990 END IF;
1991
1992 /*--------------------------------------------------------------------------------+
1993 | 1) Populate 0 values for the amounts based on the sign of the accounted amounts|
1994 +--------------------------------------------------------------------------------*/
1995 IF ((p_ae_line_rec.accounted_dr IS NOT NULL)
1996 AND (p_ae_line_rec.entered_dr IS NULL) AND (p_ae_line_rec.entered_cr IS NULL)) THEN
1997 p_ae_line_rec.entered_dr := 0;
1998 ELSIF ((p_ae_line_rec.accounted_cr IS NOT NULL)
1999 AND (p_ae_line_rec.entered_cr IS NULL) AND (p_ae_line_rec.entered_dr IS NULL)) THEN
2000 p_ae_line_rec.entered_cr := 0;
2001 END IF;
2002
2003 /*--------------------------------------------------------------------------------+
2004 | 2) Populate 0 values for the taxable accounted amounts based on the sign of the|
2005 | taxable amounts |
2006 +--------------------------------------------------------------------------------*/
2007 IF ((p_ae_line_rec.taxable_entered_dr IS NOT NULL)
2008 AND (p_ae_line_rec.taxable_accounted_dr IS NULL) AND (p_ae_line_rec.taxable_accounted_cr IS NULL)) THEN
2009 p_ae_line_rec.taxable_accounted_dr := 0;
2010 ELSIF ((p_ae_line_rec.taxable_entered_cr IS NOT NULL)
2011 AND (p_ae_line_rec.taxable_accounted_cr IS NULL) AND (p_ae_line_rec.taxable_accounted_dr IS NULL)) THEN
2012 p_ae_line_rec.taxable_accounted_cr := 0;
2013 END IF;
2014
2015 /*--------------------------------------------------------------------------------+
2016 | 2) Populate 0 values for the taxable amounts based on the sign of the taxable |
2017 | accounted amounts |
2018 +--------------------------------------------------------------------------------*/
2019 IF ((p_ae_line_rec.taxable_accounted_dr IS NOT NULL)
2020 AND (p_ae_line_rec.taxable_entered_dr IS NULL) AND (p_ae_line_rec.taxable_entered_cr IS NULL)) THEN
2021 p_ae_line_rec.taxable_entered_dr := 0;
2022 ELSIF ((p_ae_line_rec.taxable_accounted_cr IS NOT NULL)
2023 AND (p_ae_line_rec.taxable_entered_cr IS NULL) AND (p_ae_line_rec.taxable_entered_dr IS NULL)) THEN
2024 p_ae_line_rec.taxable_entered_cr := 0;
2025 END IF;
2026
2027 /*-----------------------------------------------------------------------------------+
2028 | 3) Populate 0 values for the taxable amounts based on the sign of the amounts |
2029 +-----------------------------------------------------------------------------------*/
2030 IF ((p_ae_line_rec.entered_dr IS NOT NULL)
2031 AND (p_ae_line_rec.taxable_entered_dr IS NULL) AND (p_ae_line_rec.taxable_entered_cr IS NULL)) THEN
2032 p_ae_line_rec.taxable_entered_dr := 0;
2033 ELSIF ((p_ae_line_rec.entered_cr IS NOT NULL)
2034 AND (p_ae_line_rec.taxable_entered_cr IS NULL) AND (p_ae_line_rec.taxable_entered_dr IS NULL)) THEN
2035 p_ae_line_rec.taxable_entered_cr := 0;
2036 END IF;
2037
2038 /*--------------------------------------------------------------------------------+
2039 | 3) Populate 0 values for the taxable accounted amounts based on the sign of the|
2040 | accounted amounts |
2041 +--------------------------------------------------------------------------------*/
2042 --Now for the accounted amounts
2043 IF ((p_ae_line_rec.accounted_dr IS NOT NULL)
2044 AND (p_ae_line_rec.taxable_accounted_dr IS NULL) AND (p_ae_line_rec.taxable_accounted_cr IS NULL)) THEN
2045 p_ae_line_rec.taxable_accounted_dr := 0;
2046 ELSIF ((p_ae_line_rec.accounted_cr IS NOT NULL)
2047 AND (p_ae_line_rec.taxable_accounted_cr IS NULL) AND (p_ae_line_rec.taxable_accounted_dr IS NULL)) THEN
2048 p_ae_line_rec.taxable_accounted_cr := 0;
2049 END IF;
2050
2051 /*--------------------------------------------------------------------------------+
2052 | 4) Populate 0 values for the amounts based on the sign of the taxable accounted|
2053 | amounts |
2054 +--------------------------------------------------------------------------------*/
2055 IF ((p_ae_line_rec.taxable_entered_dr IS NOT NULL)
2056 AND (p_ae_line_rec.entered_dr IS NULL) AND (p_ae_line_rec.entered_cr IS NULL)) THEN
2057 p_ae_line_rec.entered_dr := 0;
2058 ELSIF ((p_ae_line_rec.taxable_entered_cr IS NOT NULL)
2059 AND (p_ae_line_rec.entered_cr IS NULL) AND (p_ae_line_rec.entered_dr IS NULL)) THEN
2060 p_ae_line_rec.entered_cr := 0;
2061 END IF;
2062
2063 /*--------------------------------------------------------------------------------+
2064 | 4) Populate 0 values for the accounted amounts based on the sign of the taxable|
2065 | accounted amounts |
2066 +--------------------------------------------------------------------------------*/
2067 IF ((p_ae_line_rec.taxable_accounted_dr IS NOT NULL)
2068 AND (p_ae_line_rec.accounted_dr IS NULL) AND (p_ae_line_rec.accounted_cr IS NULL)) THEN
2069 p_ae_line_rec.accounted_dr := 0;
2070 ELSIF ((p_ae_line_rec.taxable_accounted_cr IS NOT NULL)
2071 AND (p_ae_line_rec.accounted_cr IS NULL) AND (p_ae_line_rec.accounted_dr IS NULL)) THEN
2072 p_ae_line_rec.accounted_cr := 0;
2073 END IF;
2074
2075 /*------------------------------------------------------+
2076 | Store AE Line elements in Global AE Lines table |
2077 +------------------------------------------------------*/
2078 p_g_ae_ctr := p_g_ae_ctr +1;
2079
2080 p_g_ae_line_tbl(p_g_ae_ctr).ae_line_type := p_ae_line_rec.ae_line_type;
2081 p_g_ae_line_tbl(p_g_ae_ctr).ae_line_type_secondary := p_ae_line_rec.ae_line_type_secondary;
2082 p_g_ae_line_tbl(p_g_ae_ctr).source_id := p_ae_line_rec.source_id;
2083 p_g_ae_line_tbl(p_g_ae_ctr).source_table := p_ae_line_rec.source_table;
2084 p_g_ae_line_tbl(p_g_ae_ctr).account := p_ae_line_rec.account;
2085 p_g_ae_line_tbl(p_g_ae_ctr).entered_dr := p_ae_line_rec.entered_dr;
2086 p_g_ae_line_tbl(p_g_ae_ctr).entered_cr := p_ae_line_rec.entered_cr;
2087 p_g_ae_line_tbl(p_g_ae_ctr).accounted_dr := p_ae_line_rec.accounted_dr;
2088 p_g_ae_line_tbl(p_g_ae_ctr).accounted_cr := p_ae_line_rec.accounted_cr;
2089 p_g_ae_line_tbl(p_g_ae_ctr).source_id_secondary := p_ae_line_rec.source_id_secondary;
2090 p_g_ae_line_tbl(p_g_ae_ctr).source_table_secondary := p_ae_line_rec.source_table_secondary;
2091 p_g_ae_line_tbl(p_g_ae_ctr).currency_code := p_ae_line_rec.currency_code;
2092 p_g_ae_line_tbl(p_g_ae_ctr).currency_conversion_rate := p_ae_line_rec.currency_conversion_rate;
2093 p_g_ae_line_tbl(p_g_ae_ctr).currency_conversion_type := p_ae_line_rec.currency_conversion_type;
2094 p_g_ae_line_tbl(p_g_ae_ctr).currency_conversion_date := p_ae_line_rec.currency_conversion_date;
2095 p_g_ae_line_tbl(p_g_ae_ctr).third_party_id := p_ae_line_rec.third_party_id;
2096 p_g_ae_line_tbl(p_g_ae_ctr).third_party_sub_id := p_ae_line_rec.third_party_sub_id;
2097 p_g_ae_line_tbl(p_g_ae_ctr).tax_group_code_id := p_ae_line_rec.tax_group_code_id;
2098 p_g_ae_line_tbl(p_g_ae_ctr).tax_code_id := p_ae_line_rec.tax_code_id;
2099 p_g_ae_line_tbl(p_g_ae_ctr).location_segment_id := p_ae_line_rec.location_segment_id;
2100 p_g_ae_line_tbl(p_g_ae_ctr).taxable_entered_dr := p_ae_line_rec.taxable_entered_dr;
2101 p_g_ae_line_tbl(p_g_ae_ctr).taxable_entered_cr := p_ae_line_rec.taxable_entered_cr;
2102 p_g_ae_line_tbl(p_g_ae_ctr).taxable_accounted_dr := p_ae_line_rec.taxable_accounted_dr;
2103 p_g_ae_line_tbl(p_g_ae_ctr).taxable_accounted_cr := p_ae_line_rec.taxable_accounted_cr;
2104 p_g_ae_line_tbl(p_g_ae_ctr).applied_from_doc_table := p_ae_line_rec.applied_from_doc_table;
2105 p_g_ae_line_tbl(p_g_ae_ctr).applied_from_doc_id := p_ae_line_rec.applied_from_doc_id;
2106 p_g_ae_line_tbl(p_g_ae_ctr).applied_to_doc_table := p_ae_line_rec.applied_to_doc_table;
2107 p_g_ae_line_tbl(p_g_ae_ctr).applied_to_doc_id := p_ae_line_rec.applied_to_doc_id;
2108 p_g_ae_line_tbl(p_g_ae_ctr).tax_link_id := p_ae_line_rec.tax_link_id;
2109 p_g_ae_line_tbl(p_g_ae_ctr).reversed_source_id := p_ae_line_rec.reversed_source_id;
2110 p_g_ae_line_tbl(p_g_ae_ctr).summarize_flag := 'N';
2111
2112 Dump_Line_Amts(p_ae_line_rec);
2113
2114 <<end_assign_elements>>
2115 IF PG_DEBUG in ('Y', 'C') THEN
2116 arp_standard.debug( 'ARP_RECONCILE.Assign_Elements()-');
2117 END IF;
2118
2119 EXCEPTION
2120 WHEN OTHERS THEN
2121 IF PG_DEBUG in ('Y', 'C') THEN
2122 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Assign_Elements');
2123 END IF;
2124 RAISE;
2125
2126 END Assign_Elements;
2127
2128 /* ==========================================================================
2129 | PROCEDURE Dump_Line_Amts
2130 |
2131 | DESCRIPTION
2132 | Dumps data accounting line data
2133 |
2134 | SCOPE - PRIVATE
2135 |
2136 | PARAMETERS
2137 | p_ae_line_rec IN Accounting lines record
2138 *==========================================================================*/
2139 PROCEDURE Dump_Line_Amts(p_ae_line_rec IN ae_line_rec_type) IS
2140
2141 BEGIN
2142
2143 IF PG_DEBUG in ('Y', 'C') THEN
2144 arp_standard.debug('ARP_RECONCILE.Dump_Line_Amts()+');
2145 arp_standard.debug('Dump_Line_Amts: ' || 'ae_line_type = ' || p_ae_line_rec.ae_line_type);
2146 arp_standard.debug('Dump_Line_Amts: ' || 'ae_line_type_secondary = ' || p_ae_line_rec.ae_line_type_secondary);
2147 arp_standard.debug('Dump_Line_Amts: ' || 'source_id = ' || p_ae_line_rec.source_id);
2148 arp_standard.debug('Dump_Line_Amts: ' || 'source_table = ' || p_ae_line_rec.source_table);
2149 arp_standard.debug('Dump_Line_Amts: ' || 'account = ' || p_ae_line_rec.account);
2150 arp_standard.debug('Dump_Line_Amts: ' || 'entered_dr = ' || p_ae_line_rec.entered_dr);
2151 arp_standard.debug('Dump_Line_Amts: ' || 'entered_cr = ' || p_ae_line_rec.entered_cr);
2152 arp_standard.debug('Dump_Line_Amts: ' || 'accounted_dr = ' || p_ae_line_rec.accounted_dr);
2153 arp_standard.debug('Dump_Line_Amts: ' || 'accounted_cr = ' || p_ae_line_rec.accounted_cr);
2154 arp_standard.debug('Dump_Line_Amts: ' || 'source_id_secondary = ' || p_ae_line_rec.source_id_secondary);
2155 arp_standard.debug('Dump_Line_Amts: ' || 'source_table_secondary = ' || p_ae_line_rec.source_table_secondary);
2156 arp_standard.debug('Dump_Line_Amts: ' || 'currency_code = ' || p_ae_line_rec.currency_code);
2157 arp_standard.debug('Dump_Line_Amts: ' || 'currency_conversion_rate = ' || p_ae_line_rec.currency_conversion_rate);
2158 arp_standard.debug('Dump_Line_Amts: ' || 'currency_conversion_type = ' || p_ae_line_rec.currency_conversion_type);
2159 arp_standard.debug('Dump_Line_Amts: ' || 'currency_conversion_date = ' || p_ae_line_rec.currency_conversion_date);
2160 arp_standard.debug('Dump_Line_Amts: ' || 'third_party_id = ' || p_ae_line_rec.third_party_id);
2161 arp_standard.debug('Dump_Line_Amts: ' || 'third_party_sub_id = ' || p_ae_line_rec.third_party_sub_id);
2162 arp_standard.debug('Dump_Line_Amts: ' || 'tax_group_code_id = ' || p_ae_line_rec.tax_group_code_id);
2163 arp_standard.debug('Dump_Line_Amts: ' || 'tax_code_id = ' || p_ae_line_rec.tax_code_id);
2164 arp_standard.debug('Dump_Line_Amts: ' || 'location_segment_id = ' || p_ae_line_rec.location_segment_id);
2165 arp_standard.debug('Dump_Line_Amts: ' || 'taxable_entered_dr = ' || p_ae_line_rec.taxable_entered_dr);
2166 arp_standard.debug('Dump_Line_Amts: ' || 'taxable_entered_cr = ' || p_ae_line_rec.taxable_entered_cr);
2167 arp_standard.debug('Dump_Line_Amts: ' || 'taxable_accounted_dr = ' || p_ae_line_rec.taxable_accounted_dr);
2168 arp_standard.debug('Dump_Line_Amts: ' || 'taxable_accounted_cr = ' || p_ae_line_rec.taxable_accounted_cr);
2169 arp_standard.debug('Dump_Line_Amts: ' || 'applied_from_doc_table = ' || p_ae_line_rec.applied_from_doc_table);
2170 arp_standard.debug('Dump_Line_Amts: ' || 'applied_from_doc_id = ' || p_ae_line_rec.applied_from_doc_id);
2171 arp_standard.debug('Dump_Line_Amts: ' || 'applied_to_doc_table = ' || p_ae_line_rec.applied_to_doc_table);
2172 arp_standard.debug('Dump_Line_Amts: ' || 'applied_to_doc_id = ' || p_ae_line_rec.applied_to_doc_id);
2173 arp_standard.debug('Dump_Line_Amts: ' || 'tax_link_id = ' || p_ae_line_rec.tax_link_id);
2174 arp_standard.debug('Dump_Line_Amts: ' || 'reversed_source_id = ' || p_ae_line_rec.reversed_source_id);
2175 arp_standard.debug('Dump_Line_Amts: ' || 'summarize_flag = ' || p_ae_line_rec.summarize_flag);
2176 arp_standard.debug('ARP_RECONCILE.Dump_Line_Amts()-');
2177 END IF;
2178
2179 EXCEPTION
2180 WHEN OTHERS THEN
2181 IF PG_DEBUG in ('Y', 'C') THEN
2182 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Dump_Line_Amts');
2183 END IF;
2184 RAISE;
2185
2186 END Dump_Line_Amts;
2187
2188 /*========================================================================
2189 | PRIVATE PROCEDURE Process_Recon
2190 |
2191 | DESCRIPTION
2192 | Actually reconciles each assignment of a Bill. Reconciliation is done
2193 | only if the Bill is closed and all chained Bills are also closed.
2194 | If an assignment is a Bill then this function is called recursively
2195 | to go to the child bill and start processing with the same condition
2196 | checks as was done for the parent bill.
2197 |
2198 | PARAMETERS
2199 | p_mode IN Document or Accounting Event mode
2200 | p_ae_doc_rec IN Document Record
2201 | p_ae_event_rec IN Event Record
2202 | p_ae_sys_rec IN System parameter details
2203 | p_cust_inv_rec IN Contains currency, exchange rate, site
2204 | details for the bill
2205 | p_br_cust_trx_line_id IN Bills Receivable assignment line id
2206 | p_customer_trx_id IN Transaction Id
2207 | p_simul_app IN Indicates that for a Bill shadow
2208 | adjustment
2209 | or assignment simulate a payment event
2210 | p_pay_ctr IN Application for assignment table counter
2211 | p_pay_tbl IN Application details for assignment table
2212 | p_g_ae_ctr IN OUT NOCOPY Global accounting entry table counter
2213 | p_g_ae_line_tbl IN OUT NOCOPY Global accounting entry lines table
2214 | containing accounting due to previous
2215 | activity on Bills
2216 | Transaction, or Bills (assignment)
2217 *=======================================================================*/
2218 PROCEDURE Process_Recon(
2219 p_mode IN VARCHAR2 ,
2220 p_ae_doc_rec IN ae_doc_rec_type ,
2221 p_ae_event_rec IN ae_event_rec_type ,
2222 p_ae_sys_rec IN ae_sys_rec_type ,
2223 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
2224 p_br_cust_trx_line_id IN NUMBER ,
2225 p_customer_trx_id IN NUMBER ,
2226 p_simul_app IN VARCHAR2 ,
2227 p_calling_point IN VARCHAR2 ,
2228 p_pay_ctr IN BINARY_INTEGER ,
2229 p_pay_tbl IN g_pay_tbl_type ,
2230 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
2231 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type ) IS
2232
2233 /*========================================================================+
2234 | Gets the initial deferred tax accounting for regular transactions |
2235 +------------------------------------------------------------------------*/
2236
2237 CURSOR get_init_def_tax_acct IS
2238 SELECT ctl.location_segment_id location_segment_id ,
2239 decode(ctl.autotax,
2240 'N','',
2241 decode(ctl.location_segment_id,
2242 '', decode(ctl.vat_tax_id,
2243 '','',
2244 ctl1.vat_tax_id, '',
2245 ctl1.vat_tax_id),
2246 '')) tax_group_code_id,
2247 ctl.vat_tax_id tax_code_id,
2248 gld.code_combination_id account,
2249 sum(nvl(gld.amount,0)) amount,
2250 sum(nvl(gld.acctd_amount,0)) acctd_amount,
2251 max(nvl(ctl.taxable_amount,0)) taxable_amount,
2252 max(decode(gld.account_class,
2253 'TAX',
2254 arpcurr.functional_amount(
2255 nvl(ctl.taxable_amount,0),
2256 p_ae_sys_rec.base_currency ,
2257 p_cust_inv_rec.exchange_rate ,
2258 p_ae_sys_rec.base_precision ,
2259 p_ae_sys_rec.base_min_acc_unit),
2260 '')) taxable_acctd_amount
2261 FROM ra_customer_trx ct ,
2262 ra_cust_trx_line_gl_dist gld,
2263 ra_customer_trx_lines ctl,
2264 ra_customer_trx_lines ctl1
2265 where ct.customer_trx_id = p_customer_trx_id
2266 and p_calling_point IN ('TRAN', 'BLTR')
2267 and ct.customer_trx_id = gld.customer_trx_id
2268 and gld.customer_trx_id = ctl.customer_trx_id
2269 and gld.customer_trx_line_id = ctl.customer_trx_line_id
2270 and gld.account_class = 'TAX'
2271 and gld.collected_tax_ccid IS NOT NULL --deferred tax lines only
2272 and gld.account_set_flag = 'N'
2273 and ctl.link_to_cust_trx_line_id = ctl1.customer_trx_line_id --outer join not required here
2274 and not exists (select 'x'
2275 from ra_customer_trx_lines ctl2
2276 where ctl2.customer_trx_id = p_customer_trx_id
2277 and p_calling_point IN ('TRAN', 'BLTR')
2278 and ctl2.autorule_complete_flag = 'N')
2279 group by ctl.customer_trx_line_id ,
2280 ctl.location_segment_id ,
2281 decode(ctl.autotax,'N','',
2282 decode(ctl.location_segment_id,
2283 '', decode(ctl.vat_tax_id,
2284 '','',
2285 ctl1.vat_tax_id, '',
2286 ctl1.vat_tax_id),
2287 '')),
2288 ctl.vat_tax_id ,
2289 gld.code_combination_id
2290 order by 1,2,3;
2291
2292 /*-------------------------------------------------------------------------+
2293 | Gets the accounting for applications on transactions from the accounting|
2294 | table for reconciliation purposes. |
2295 +-------------------------------------------------------------------------*/
2296
2297 CURSOR get_def_tax_acct IS --get accounting for applications on transactions
2298 select ard.location_segment_id location_segment_id ,
2299 ard.tax_group_code_id tax_group_code_id ,
2300 ard.tax_code_id tax_code_id ,
2301 ard.code_combination_id account ,
2302 sum(nvl(ard.amount_dr,0) * -1 +
2303 nvl(ard.amount_cr,0)) amount ,
2304 sum(nvl(ard.acctd_amount_dr,0) * -1 +
2305 nvl(ard.acctd_amount_cr,0)) acctd_amount ,
2306 sum(nvl(ard.taxable_entered_dr,0) * -1 +
2307 nvl(ard.taxable_entered_cr,0)) taxable_amount ,
2308 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
2309 nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
2310 from ar_distributions ard,
2311 ar_receivable_applications app
2312 where p_ae_sys_rec.sob_type = 'P'
2313 and app.applied_customer_trx_id = p_customer_trx_id
2314 and p_calling_point IN ('TRAN', 'BLTR')
2315 and app.status = 'APP'
2316 and nvl(app.confirmed_flag, 'Y') = 'Y'
2317 and ard.source_id = app.receivable_application_id
2318 and ard.source_table = 'RA'
2319 and ard.source_type = 'DEFERRED_TAX'
2320 and decode(ard.source_type_secondary,
2321 'RECONCILE', ard.source_id_secondary,
2322 p_customer_trx_id) = p_customer_trx_id
2323 group by ard.location_segment_id ,
2324 ard.tax_group_code_id ,
2325 ard.tax_code_id ,
2326 ard.code_combination_id
2327 /*-------------------------------------------------------------------------+
2328 | Gets the accounting for adjustments on transactions from the accounting |
2329 | table for reconciliation purposes. |
2330 +-------------------------------------------------------------------------*/
2331 UNION ALL--get accounting for adjustments on transaction
2332 select ard.location_segment_id location_segment_id ,
2333 ard.tax_group_code_id tax_group_code_id ,
2334 ard.tax_code_id tax_code_id ,
2335 ard.code_combination_id account ,
2336 sum(nvl(ard.amount_dr,0) * -1 +
2337 nvl(ard.amount_cr,0)) amount ,
2338 sum(nvl(ard.acctd_amount_dr,0) * -1 +
2339 nvl(ard.acctd_amount_cr,0)) acctd_amount ,
2340 sum(nvl(ard.taxable_entered_dr,0) * -1 +
2341 nvl(ard.taxable_entered_cr,0)) taxable_amount ,
2342 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
2343 nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
2344 from ar_distributions ard,
2345 ar_adjustments adj
2346 where p_ae_sys_rec.sob_type = 'P'
2347 and adj.customer_trx_id = p_customer_trx_id
2348 and p_calling_point IN ('TRAN', 'BLTR')
2349 and adj.status = 'A'
2350 and ard.source_id = adj.adjustment_id
2351 and ard.source_table = 'ADJ'
2352 and ard.source_type = 'DEFERRED_TAX'
2353 and decode(ard.source_type_secondary,
2354 'RECONCILE', ard.source_id_secondary,
2355 p_customer_trx_id) = p_customer_trx_id
2356 group by ard.location_segment_id ,
2357 ard.tax_group_code_id ,
2358 ard.tax_code_id ,
2359 ard.code_combination_id
2360 /*--------------------------------------------------------------------------+
2361 | Gets the accounting for activity on a Bill to which the transactions has |
2362 | been assigned. i.e. deferred tax accounting for transaction assignments |
2363 | to the Bill. This is used to reconcile the transaction. p_customer_trx_id|
2364 | is null when processing assignments on a Bill. So the statement below is |
2365 | used for transactions only. |
2366 +--------------------------------------------------------------------------*/
2367 UNION ALL--get accounting on Bills for Transactions
2368 select ard.location_segment_id location_segment_id ,
2369 ard.tax_group_code_id tax_group_code_id ,
2370 ard.tax_code_id tax_code_id ,
2371 ard.code_combination_id account ,
2372 sum(nvl(ard.amount_dr,0) * -1 +
2373 nvl(ard.amount_cr,0)) amount ,
2374 sum(nvl(ard.acctd_amount_dr,0) * -1 +
2375 nvl(ard.acctd_amount_cr,0)) acctd_amount ,
2376 sum(nvl(ard.taxable_entered_dr,0) * -1 +
2377 nvl(ard.taxable_entered_cr,0)) taxable_amount ,
2378 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
2379 nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
2380 from ra_customer_trx_lines ctl,
2381 ar_distributions ard
2382 where p_ae_sys_rec.sob_type = 'P'
2383 and ctl.br_ref_customer_trx_id = p_customer_trx_id
2384 and p_calling_point IN ('TRAN', 'BLTR')
2385 and ard.source_id_secondary = ctl.customer_trx_line_id
2386 and ard.source_table_secondary = 'CTL'
2387 and ard.source_type_secondary IN ('ASSIGNMENT', 'ASSIGNMENT_RECONCILE',
2388 'RECONCILE')
2389 and ard.source_type = 'DEFERRED_TAX'
2390 group by ard.location_segment_id ,
2391 ard.tax_group_code_id ,
2392 ard.tax_code_id ,
2393 ard.code_combination_id
2394 /*--------------------------------------------------------------------------+
2395 | Get the deferred tax accounting moved for the assignment on the Bill due |
2396 | to activity on the Bill from the accounting table. The assignment line id|
2397 | is used by the statement below. |
2398 +--------------------------------------------------------------------------*/
2399 UNION ALL--reconcile bill only
2400 select ard.location_segment_id location_segment_id ,
2401 ard.tax_group_code_id tax_group_code_id ,
2402 ard.tax_code_id tax_code_id ,
2403 ard.code_combination_id account ,
2404 sum(nvl(ard.amount_dr,0) * -1 +
2405 nvl(ard.amount_cr,0)) amount ,
2406 sum(nvl(ard.acctd_amount_dr,0) * -1 +
2407 nvl(ard.acctd_amount_cr,0)) acctd_amount ,
2408 sum(nvl(ard.taxable_entered_dr,0) * -1 +
2409 nvl(ard.taxable_entered_cr,0)) taxable_amount ,
2410 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
2411 nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
2412 from ar_distributions ard
2413 where p_ae_sys_rec.sob_type = 'P'
2414 and ard.source_id_secondary = p_br_cust_trx_line_id
2415 and p_calling_point = 'BILL'
2416 and ard.source_table_secondary = 'CTL'
2417 and ard.source_type_secondary IN ('ASSIGNMENT', 'ASSIGNMENT_RECONCILE')
2418 and ard.source_type = 'DEFERRED_TAX'
2419 group by ard.location_segment_id ,
2420 ard.tax_group_code_id ,
2421 ard.tax_code_id ,
2422 ard.code_combination_id
2423 order by 1,2,3;
2424
2425
2426 ae_tax_tbl g_tax_tbl_type;
2427 ae_tax_activity_tbl g_tax_tbl_type;
2428
2429 l_ae_line_tbl ae_line_tbl_type;
2430 l_ae_line_rec ae_line_rec_type;
2431 l_ae_empty_line_rec ae_line_rec_type;
2432
2433 l_ae_rule_rec ae_rule_rec_type;
2434
2435 l_app_rec ar_receivable_applications%ROWTYPE;
2436 l_adj_rec ar_adjustments%ROWTYPE;
2437
2438 l_tax_ctr NUMBER := 0;
2439 l_tax_ctr1 NUMBER := 0;
2440 l_ctr NUMBER;
2441 l_ctr1 NUMBER;
2442 l_ctr2 NUMBER;
2443 l_ae_ctr NUMBER;
2444 l_cached BOOLEAN;
2445 l_cre_rec BOOLEAN;
2446 l_match_cond BOOLEAN;
2447 l_ae_doc_rec ae_doc_rec_type;
2448
2449 BEGIN
2450
2451 IF PG_DEBUG in ('Y', 'C') THEN
2452 arp_standard.debug('ARP_RECONCILE.Process_Recon()+');
2453 arp_standard.debug('Process_Recon: ' || 'list Input of parameters ');
2454 arp_standard.debug('Process_Recon: ' || 'p_br_cust_trx_line_id ' || p_br_cust_trx_line_id);
2455 arp_standard.debug('Process_Recon: ' || 'p_customer_trx_id ' || p_customer_trx_id);
2456 arp_standard.debug('Process_Recon: ' || 'p_simul_app ' || p_simul_app);
2457 arp_standard.debug('Process_Recon: ' || 'p_calling_point ' || p_calling_point);
2458 arp_standard.debug('Process_Recon: ' || 'p_pay_ctr ' || p_pay_ctr);
2459 END IF;
2460
2461 /*-------------------------------------------------------------------------------+
2462 | For an assignment on a Bill simulate an activity such as an application which |
2463 | results in closing the amount assigned to the Bill due to the line assignment.|
2464 +-------------------------------------------------------------------------------*/
2465 IF (p_simul_app = 'Y') THEN
2466
2467 l_ae_line_tbl := g_ae_empty_line_tbl;
2468 l_ae_ctr := 0;
2469
2470 l_ae_doc_rec := p_ae_doc_rec;
2471 l_ae_doc_rec.source_table := 'RA';
2472
2473 IF p_pay_tbl.EXISTS(p_pay_ctr) THEN --atleast one activity exists
2474
2475 IF PG_DEBUG in ('Y', 'C') THEN
2476 arp_standard.debug('Process_Recon: ' || 'p_pay_tbl simulate application ');
2477 END IF;
2478
2479 FOR l_ctr3 IN p_pay_tbl.FIRST .. p_pay_tbl.LAST LOOP
2480
2481 l_app_rec.applied_customer_trx_id := p_pay_tbl(l_ctr3).applied_customer_trx_id ;
2482 l_app_rec.applied_payment_schedule_id := p_pay_tbl(l_ctr3).applied_payment_schedule_id ;
2483 l_app_rec.amount_applied := p_pay_tbl(l_ctr3).amount_applied ;
2484 l_app_rec.acctd_amount_applied_to := p_pay_tbl(l_ctr3).acctd_amount_applied_to ;
2485 l_app_rec.line_applied := p_pay_tbl(l_ctr3).line_applied ;
2486 l_app_rec.tax_applied := p_pay_tbl(l_ctr3).tax_applied ;
2487 l_app_rec.freight_applied := p_pay_tbl(l_ctr3).freight_applied ;
2488 l_app_rec.receivables_charges_applied := p_pay_tbl(l_ctr3).receivables_charges_applied ;
2489
2490 /*-----------------------------------------------------------------------------+
2491 | Call Tax accounting engine to allocate deferred tax for the simulated single|
2492 | activity on the assignment. |
2493 +-----------------------------------------------------------------------------*/
2494 ARP_ALLOCATION_PKG.Allocate_Tax(
2495 p_ae_doc_rec => l_ae_doc_rec , --Document detail
2496 p_ae_event_rec => p_ae_event_rec , --Event record
2497 p_ae_rule_rec => l_ae_rule_rec , --Rule info for payment method
2498 p_app_rec => l_app_rec , --Application details
2499 p_cust_inv_rec => p_cust_inv_rec , --Invoice details
2500 p_adj_rec => l_adj_rec , --dummy adjustment record
2501 p_ae_ctr => l_ae_ctr , --counter
2502 p_ae_line_tbl => l_ae_line_tbl , --final tax accounting table
2503 p_br_cust_trx_line_id => '' ,
2504 p_simul_app => p_simul_app );
2505
2506 IF l_ae_line_tbl.EXISTS(l_ae_ctr) THEN --Atleast one Tax line exists
2507
2508 IF PG_DEBUG in ('Y', 'C') THEN
2509 arp_standard.debug('Process_Recon: ' || 'Caching Tax for simulated application ');
2510 END IF;
2511
2512 FOR l_ctr1 IN l_ae_line_tbl.FIRST .. l_ae_line_tbl.LAST LOOP
2513
2514 /*--------------------------------------------------------------------------------+
2515 |Cache the deferred tax accounting into the tax table.This is the deferred tax |
2516 |created as though the amount on the shadow assignment of the transaction on |
2517 |the bill were paid off through a single activity. Note in this case we |
2518 |multiply the credits by -1 because we want to use the net amount by location |
2519 |or tax code for the simulated application accounting, and add it to the actual |
2520 |accounting for the Bills assignment. This will result in creating the offsetting|
2521 |reconciliation entries. |
2522 +--------------------------------------------------------------------------------*/
2523 l_cached := FALSE;
2524
2525 IF ae_tax_tbl.EXISTS(l_tax_ctr) THEN
2526
2527 FOR l_ctr IN ae_tax_tbl.FIRST .. ae_tax_tbl.LAST LOOP
2528
2529 IF ((((l_ae_line_tbl(l_ctr1).location_segment_id IS NOT NULL)
2530 AND (nvl(ae_tax_tbl(l_ctr).ae_location_segment_id,-999) = nvl(l_ae_line_tbl(l_ctr1).location_segment_id,-999)))
2531 OR ((nvl(l_ae_line_tbl(l_ctr1).tax_group_code_id,-999) = nvl(ae_tax_tbl(l_ctr).ae_tax_group_code_id,-999))
2532 AND (l_ae_line_tbl(l_ctr1).tax_code_id IS NOT NULL)
2533 AND (nvl(ae_tax_tbl(l_ctr).ae_tax_code_id,-999) = nvl(l_ae_line_tbl(l_ctr1).tax_code_id,-999))))
2534 AND (ae_tax_tbl(l_ctr).ae_code_combination_id = l_ae_line_tbl(l_ctr1).account)
2535 AND (l_ae_line_tbl(l_ctr1).ae_line_type = 'DEFERRED_TAX'))
2536 THEN
2537
2538 IF PG_DEBUG in ('Y', 'C') THEN
2539 arp_standard.debug('Process_Recon: ' || '1) Hit found in cache ae_tax_tbl');
2540 END IF;
2541
2542 ae_tax_tbl(l_ctr).ae_amount := ae_tax_tbl(l_ctr).ae_amount
2543 + nvl(l_ae_line_tbl(l_ctr1).entered_dr,0)
2544 + nvl(l_ae_line_tbl(l_ctr1).entered_cr,0) * -1;
2545
2546 ae_tax_tbl(l_ctr).ae_acctd_amount := ae_tax_tbl(l_ctr).ae_acctd_amount
2547 + nvl(l_ae_line_tbl(l_ctr1).accounted_dr,0)
2548 + nvl(l_ae_line_tbl(l_ctr1).accounted_cr,0) * -1; --bug6146807
2549
2550 ae_tax_tbl(l_ctr).ae_taxable_amount := ae_tax_tbl(l_ctr).ae_taxable_amount
2551 + nvl(l_ae_line_tbl(l_ctr1).taxable_entered_dr,0)
2552 + nvl(l_ae_line_tbl(l_ctr1).taxable_entered_cr,0) * -1;
2553
2554 ae_tax_tbl(l_ctr).ae_taxable_acctd_amount := ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
2555 + nvl(l_ae_line_tbl(l_ctr1).taxable_accounted_dr,0)
2556 + nvl(l_ae_line_tbl(l_ctr1).taxable_accounted_cr,0) * -1;
2557
2558 l_cached := TRUE;
2559
2560 END IF; --grouping rule satisfied
2561
2562 END LOOP; --ae_tax_tbl to verify whether tax record is cached
2563
2564 END IF; --ae_tax_tbl exists
2565
2566 /*-----------------------------------------------------------------------------+
2567 |Cache the deferred tax accounting entry into the table if not already cached |
2568 +-----------------------------------------------------------------------------*/
2569 IF (NOT l_cached) AND (l_ae_line_tbl(l_ctr1).ae_line_type = 'DEFERRED_TAX')
2570 THEN
2571 IF PG_DEBUG in ('Y', 'C') THEN
2572 arp_standard.debug('Process_Recon: ' || '1) Now caching in cache ae_tax_tbl');
2573 END IF;
2574
2575 l_tax_ctr := l_tax_ctr + 1;
2576
2577 ae_tax_tbl(l_tax_ctr).ae_location_segment_id := l_ae_line_tbl(l_ctr1).location_segment_id;
2578
2579 ae_tax_tbl(l_tax_ctr).ae_tax_group_code_id := l_ae_line_tbl(l_ctr1).tax_group_code_id;
2580
2581 ae_tax_tbl(l_tax_ctr).ae_tax_code_id := l_ae_line_tbl(l_ctr1).tax_code_id;
2582
2583 ae_tax_tbl(l_tax_ctr).ae_code_combination_id := l_ae_line_tbl(l_ctr1).account;
2584
2585 ae_tax_tbl(l_tax_ctr).ae_amount :=
2586 nvl(l_ae_line_tbl(l_ctr1).entered_dr,0)
2587 + nvl(l_ae_line_tbl(l_ctr1).entered_cr,0) * -1;
2588
2589 ae_tax_tbl(l_tax_ctr).ae_acctd_amount :=
2590 nvl(l_ae_line_tbl(l_ctr1).accounted_dr,0)
2591 + nvl(l_ae_line_tbl(l_ctr1).accounted_cr,0) * -1;
2592
2593 ae_tax_tbl(l_tax_ctr).ae_taxable_amount :=
2594 nvl(l_ae_line_tbl(l_ctr1).taxable_entered_dr,0)
2595 + nvl(l_ae_line_tbl(l_ctr1).taxable_entered_cr,0) * -1;
2596
2597 ae_tax_tbl(l_tax_ctr).ae_taxable_acctd_amount :=
2598 nvl(l_ae_line_tbl(l_ctr1).taxable_accounted_dr,0)
2599 + nvl(l_ae_line_tbl(l_ctr1).taxable_accounted_cr,0) * -1;
2600
2601 l_cached := TRUE;
2602
2603 IF PG_DEBUG in ('Y', 'C') THEN
2604 arp_standard.debug('Process_Recon: ' || ' ');
2605 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_tax_ctr||').ae_location_segment_id = ' || ae_tax_tbl(l_tax_ctr).ae_location_segment_id);
2606 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_tax_ctr||').ae_tax_group_code_id = ' || ae_tax_tbl(l_tax_ctr).ae_tax_group_code_id);
2607 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_tax_ctr||').ae_tax_code_id = ' || ae_tax_tbl(l_tax_ctr).ae_tax_code_id);
2608 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_tax_ctr||').ae_code_combination_id = ' || ae_tax_tbl(l_tax_ctr).ae_code_combination_id);
2609 arp_standard.debug('Process_Recon: ' || ' ');
2610 END IF;
2611
2612 END IF; --not cached
2613
2614 END LOOP; -- lines table
2615
2616 END IF; --atleast one tax line exists
2617
2618 END LOOP; --process the payment table for all simulated applications
2619
2620 END IF; --payment table exists
2621
2622 ELSE
2623 /*---------------------------------------------------------------------------------+
2624 |Cache the deferred tax from the original transaction accounting table for use in |
2625 |the reconciliation process |
2626 +---------------------------------------------------------------------------------*/
2627 IF PG_DEBUG in ('Y', 'C') THEN
2628 arp_standard.debug('Process_Recon: ' || 'Caching deferred tax from Original Transaction accounting ');
2629 END IF;
2630
2631 FOR l_init_def_tax IN get_init_def_tax_acct LOOP
2632
2633 IF PG_DEBUG in ('Y', 'C') THEN
2634 arp_standard.debug('Process_Recon: ' || 'Processing Original Transaction accounting ');
2635 END IF;
2636
2637 l_cached := FALSE;
2638
2639 IF ae_tax_tbl.EXISTS(l_tax_ctr) THEN --Atleast one cached deferred Tax line exists
2640
2641 FOR l_ctr IN ae_tax_tbl.FIRST .. ae_tax_tbl.LAST LOOP
2642
2643 IF ((((l_init_def_tax.location_segment_id IS NOT NULL)
2644 AND (nvl(ae_tax_tbl(l_ctr).ae_location_segment_id,-999) = nvl(l_init_def_tax.location_segment_id,-999)))
2645 OR ((l_init_def_tax.tax_code_id IS NOT NULL)
2646 AND (nvl(ae_tax_tbl(l_ctr).ae_tax_code_id,-999) = nvl(l_init_def_tax.tax_code_id,-999))
2647 AND (nvl(ae_tax_tbl(l_ctr).ae_tax_group_code_id,-999) = nvl(l_init_def_tax.tax_group_code_id,-999))))
2648 AND (ae_tax_tbl(l_ctr).ae_code_combination_id = l_init_def_tax.account))
2649 THEN
2650
2651 IF PG_DEBUG in ('Y', 'C') THEN
2652 arp_standard.debug('Process_Recon: ' || '2) Hit found in cache ae_tax_tbl');
2653 END IF;
2654
2655 ae_tax_tbl(l_ctr).ae_amount := ae_tax_tbl(l_ctr).ae_amount
2656 + l_init_def_tax.amount;
2657
2658 ae_tax_tbl(l_ctr).ae_acctd_amount := ae_tax_tbl(l_ctr).ae_acctd_amount
2659 + l_init_def_tax.acctd_amount;
2660
2661 ae_tax_tbl(l_ctr).ae_taxable_amount := ae_tax_tbl(l_ctr).ae_taxable_amount
2662 + l_init_def_tax.taxable_amount;
2663
2664 ae_tax_tbl(l_ctr).ae_taxable_acctd_amount := ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
2665 + l_init_def_tax.taxable_acctd_amount;
2666
2667 l_cached := TRUE;
2668
2669 END IF; --grouping rule satisfied
2670
2671 END LOOP; --ae_tax_tbl to verify whether tax record is cached
2672
2673 END IF; --activity table exists for already cached entries
2674
2675 /*---------------------------------------------------------------------------------+
2676 |If an entry is not already cached then cache the Original accounting |
2677 +---------------------------------------------------------------------------------*/
2678 IF (NOT l_cached) THEN
2679
2680 IF PG_DEBUG in ('Y', 'C') THEN
2681 arp_standard.debug('Process_Recon: ' || '2) Now caching in cache ae_tax_tbl');
2682 END IF;
2683
2684 l_tax_ctr := l_tax_ctr + 1;
2685
2686 ae_tax_tbl(l_tax_ctr).ae_location_segment_id := l_init_def_tax.location_segment_id;
2687
2688 ae_tax_tbl(l_tax_ctr).ae_tax_group_code_id := l_init_def_tax.tax_group_code_id;
2689
2690 ae_tax_tbl(l_tax_ctr).ae_tax_code_id := l_init_def_tax.tax_code_id;
2691
2692 ae_tax_tbl(l_tax_ctr).ae_code_combination_id := l_init_def_tax.account;
2693
2694 ae_tax_tbl(l_tax_ctr).ae_amount := l_init_def_tax.amount;
2695
2696 ae_tax_tbl(l_tax_ctr).ae_acctd_amount := l_init_def_tax.acctd_amount;
2697
2698 ae_tax_tbl(l_tax_ctr).ae_taxable_amount := l_init_def_tax.taxable_amount;
2699
2700 ae_tax_tbl(l_tax_ctr).ae_taxable_acctd_amount := l_init_def_tax.taxable_acctd_amount;
2701
2702 IF PG_DEBUG in ('Y', 'C') THEN
2703 arp_standard.debug('Process_Recon: ' || ' ');
2704 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_tax_ctr||').ae_location_segment_id = ' || ae_tax_tbl(l_tax_ctr).ae_location_segment_id);
2705 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_tax_ctr||').ae_tax_group_code_id = ' || ae_tax_tbl(l_tax_ctr).ae_tax_group_code_id);
2706 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_tax_ctr||').ae_tax_code_id = ' || ae_tax_tbl(l_tax_ctr).ae_tax_code_id);
2707 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_tax_ctr||').ae_code_combination_id = ' || ae_tax_tbl(l_tax_ctr).ae_code_combination_id);
2708 arp_standard.debug('Process_Recon: ' || ' ');
2709 END IF;
2710
2711 l_cached := TRUE;
2712
2713 END IF; --not cached then cache
2714
2715 END LOOP; --process original tax on Invoice and cache
2716
2717 END IF; --Simulating an application to reconcile against single activity
2718
2719 IF PG_DEBUG in ('Y', 'C') THEN
2720 arp_standard.debug('Process_Recon: ' || 'Start caching physically created tax accounting entries due to past activity');
2721 END IF;
2722
2723 /*---------------------------------------------------------------------------------+
2724 |Cache the deferred tax accounting entries physically created in ar_distributions |
2725 |due to activity on the bill. |
2726 +---------------------------------------------------------------------------------*/
2727 FOR l_inv_nr IN get_def_tax_acct LOOP
2728
2729 l_cached := FALSE;
2730
2731 IF ae_tax_activity_tbl.EXISTS(l_tax_ctr1) THEN
2732
2733 FOR l_ctr IN ae_tax_activity_tbl.FIRST .. ae_tax_activity_tbl.LAST LOOP
2734
2735 /*--------------------------------------------------------------------+
2736 |Add to accounting entry in cache if matching conditions |
2737 +--------------------------------------------------------------------*/
2738 IF ((((l_inv_nr.location_segment_id IS NOT NULL)
2739 AND (nvl(ae_tax_activity_tbl(l_ctr).ae_location_segment_id,-999) = nvl(l_inv_nr.location_segment_id,-999)))
2740 OR ((nvl(ae_tax_activity_tbl(l_ctr).ae_tax_group_code_id,-999) = nvl(l_inv_nr.tax_group_code_id,-999))
2741 AND (l_inv_nr.tax_code_id IS NOT NULL)
2742 AND (nvl(ae_tax_activity_tbl(l_ctr).ae_tax_code_id,-999) = nvl(l_inv_nr.tax_code_id,-999))))
2743 AND (ae_tax_activity_tbl(l_ctr).ae_code_combination_id = l_inv_nr.account))
2744 THEN
2745
2746 IF PG_DEBUG in ('Y', 'C') THEN
2747 arp_standard.debug('Process_Recon: ' || '3) Hit found in cache ae_tax_activity_tbl');
2748 END IF;
2749
2750 ae_tax_activity_tbl(l_ctr).ae_amount :=
2751 ae_tax_activity_tbl(l_ctr).ae_amount + l_inv_nr.amount;
2752
2753 ae_tax_activity_tbl(l_ctr).ae_acctd_amount :=
2754 ae_tax_activity_tbl(l_ctr).ae_acctd_amount + l_inv_nr.acctd_amount;
2755
2756 ae_tax_activity_tbl(l_ctr).ae_taxable_amount :=
2757 ae_tax_activity_tbl(l_ctr).ae_taxable_amount + l_inv_nr.taxable_amount;
2758
2759 ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount :=
2760 ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount +l_inv_nr.taxable_acctd_amount;
2761
2762 l_cached := TRUE;
2763
2764 END IF;
2765
2766 END LOOP; --for activity table from ar_distributions
2767
2768 END IF; --activity table exists for already cached entries
2769
2770 /*---------------------------------------------------------------------------------+
2771 |If an entry is not already cached when retrieved from ar_distributions then cache|
2772 +---------------------------------------------------------------------------------*/
2773 IF (NOT l_cached) THEN
2774
2775 IF PG_DEBUG in ('Y', 'C') THEN
2776 arp_standard.debug('Process_Recon: ' || '3) Now caching in cache ae_tax_activity_tbl');
2777 END IF;
2778
2779 l_tax_ctr1 := l_tax_ctr1 + 1;
2780 ae_tax_activity_tbl(l_tax_ctr1).ae_location_segment_id := l_inv_nr.location_segment_id;
2781
2782 ae_tax_activity_tbl(l_tax_ctr1).ae_tax_group_code_id := l_inv_nr.tax_group_code_id;
2783
2784 ae_tax_activity_tbl(l_tax_ctr1).ae_tax_code_id := l_inv_nr.tax_code_id;
2785
2786 ae_tax_activity_tbl(l_tax_ctr1).ae_code_combination_id := l_inv_nr.account;
2787
2788 ae_tax_activity_tbl(l_tax_ctr1).ae_amount := l_inv_nr.amount;
2789
2790 ae_tax_activity_tbl(l_tax_ctr1).ae_acctd_amount := l_inv_nr.acctd_amount;
2791
2792 ae_tax_activity_tbl(l_tax_ctr1).ae_taxable_amount := l_inv_nr.taxable_amount;
2793
2794 ae_tax_activity_tbl(l_tax_ctr1).ae_taxable_acctd_amount := l_inv_nr.taxable_acctd_amount;
2795
2796 l_cached := TRUE;
2797
2798 IF PG_DEBUG in ('Y', 'C') THEN
2799 arp_standard.debug('Process_Recon: ' || ' ');
2800 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_tax_ctr1||').ae_location_segment_id = ' || ae_tax_activity_tbl(l_tax_ctr1).ae_location_segment_id);
2801 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_tax_ctr1||').ae_tax_group_code_id = ' || ae_tax_activity_tbl(l_tax_ctr1).ae_tax_group_code_id);
2802 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_tax_ctr1||').ae_tax_code_id = ' || ae_tax_activity_tbl(l_tax_ctr1).ae_tax_code_id);
2803 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_tax_ctr1||').ae_amount = ' || ae_tax_activity_tbl(l_tax_ctr1).ae_amount);
2804 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_tax_ctr1||').ae_acctd_amount = ' || ae_tax_activity_tbl(l_tax_ctr1).ae_acctd_amount);
2805 arp_standard.debug('Process_Recon: ' || ' ');
2806 END IF;
2807
2808 END IF; --not cached then cache
2809
2810 END LOOP; --all activity
2811
2812 /*---------------------------------------------------------------------------------+
2813 |Now cache the accounting entries from the global accounting table due to previous|
2814 |activity on the Transaction or Bill. These accounting entries are stored in a |
2815 |PLSQL table by the parent routine which calls the reconciliation routine. This |
2816 |table may also contain reconciliation entries for assignments on Bill when a |
2817 |Transaction is being Reconciled. |
2818 +---------------------------------------------------------------------------------*/
2819 IF p_g_ae_line_tbl.EXISTS(p_g_ae_ctr) AND (g_call_num = 1) THEN
2820
2821 IF PG_DEBUG in ('Y', 'C') THEN
2822 arp_standard.debug('Process_Recon: ' || '4) Cache table p_g_ae_line_tbl Exists');
2823 END IF;
2824
2825 FOR l_ctr1 IN p_g_ae_line_tbl.FIRST .. p_g_ae_line_tbl.LAST LOOP
2826
2827 l_cached := FALSE;
2828
2829 IF PG_DEBUG in ('Y', 'C') THEN
2830 arp_standard.debug('Process_Recon: ' || ' ');
2831 arp_standard.debug('Process_Recon: ' || 'Checking whether global table accounting matches tax activity table');
2832 arp_standard.debug('Process_Recon: ' || 'p_br_cust_trx_line_id ' || p_br_cust_trx_line_id);
2833 arp_standard.debug('Process_Recon: ' || 'p_g_ae_line_tbl('|| l_ctr1 || ').source_id_secondary ' || p_g_ae_line_tbl(l_ctr1).source_id_secondary);
2834 arp_standard.debug('Process_Recon: ' || 'p_g_ae_line_tbl('|| l_ctr1 || ').source_table_secondary ' || p_g_ae_line_tbl(l_ctr1).source_table_secondary);
2835 arp_standard.debug('Process_Recon: ' || 'p_g_ae_line_tbl('|| l_ctr1 || ').ae_line_type ' || p_g_ae_line_tbl(l_ctr1).ae_line_type);
2836 arp_standard.debug('Process_Recon: ' || 'p_g_ae_line_tbl('|| l_ctr1 || ').location_segment_id ' || p_g_ae_line_tbl(l_ctr1).location_segment_id);
2837 arp_standard.debug('Process_Recon: ' || 'p_g_ae_line_tbl('|| l_ctr1 || ').tax_group_code_id ' || p_g_ae_line_tbl(l_ctr1).tax_group_code_id);
2838 arp_standard.debug('Process_Recon: ' || 'p_g_ae_line_tbl('|| l_ctr1 || ').tax_code_id ' || p_g_ae_line_tbl(l_ctr1).tax_code_id);
2839 arp_standard.debug('Process_Recon: ' || ' ');
2840 END IF;
2841
2842 IF ae_tax_activity_tbl.EXISTS(l_tax_ctr1) THEN
2843
2844 IF PG_DEBUG in ('Y', 'C') THEN
2845 arp_standard.debug('Process_Recon: ' || '4) Cache table ae_tax_activity_tbl Exists');
2846 END IF;
2847
2848 FOR l_ctr IN ae_tax_activity_tbl.FIRST .. ae_tax_activity_tbl.LAST LOOP
2849
2850 /*--------------------------------------------------------------------+
2851 |Add to accounting entry in cache if matching conditions |
2852 +--------------------------------------------------------------------*/
2853 IF ((((p_g_ae_line_tbl(l_ctr1).location_segment_id IS NOT NULL)
2854 AND (nvl(ae_tax_activity_tbl(l_ctr).ae_location_segment_id,-999)
2855 = nvl(p_g_ae_line_tbl(l_ctr1).location_segment_id,-999)))
2856 OR ((nvl(ae_tax_activity_tbl(l_ctr).ae_tax_group_code_id,-999)
2857 = nvl(p_g_ae_line_tbl(l_ctr1).tax_group_code_id,-999))
2858 AND (p_g_ae_line_tbl(l_ctr1).tax_code_id IS NOT NULL)
2859 AND (nvl(ae_tax_activity_tbl(l_ctr).ae_tax_code_id,-999)
2860 = nvl(p_g_ae_line_tbl(l_ctr1).tax_code_id,-999))))
2861
2862 --condition beow is required because the Bills global accounting cache may have accounting
2863 --entries for more than one assignment on the Bill, when br cust trx line id is populated
2864 --it implies that the source_table_secondary is CTL in p_g_ae_line_tbl because this is the
2865 --cache for the Bills accounting
2866 AND ((p_calling_point = 'TRAN')
2867 OR ((p_calling_point IN ('BILL', 'BLTR')
2868 AND (nvl(p_br_cust_trx_line_id,-999) = nvl(p_g_ae_line_tbl(l_ctr1).source_id_secondary,-999)))))
2869 AND (ae_tax_activity_tbl(l_ctr).ae_code_combination_id = p_g_ae_line_tbl(l_ctr1).account)
2870 AND (p_g_ae_line_tbl(l_ctr1).ae_line_type = 'DEFERRED_TAX'))
2871 THEN
2872
2873 IF PG_DEBUG in ('Y', 'C') THEN
2874 arp_standard.debug('Process_Recon: ' || '4) Hit found in cache ae_tax_activity_tbl');
2875 END IF;
2876
2877 ae_tax_activity_tbl(l_ctr).ae_amount :=
2878 ae_tax_activity_tbl(l_ctr).ae_amount + nvl(p_g_ae_line_tbl(l_ctr1).entered_dr,0) * -1
2879 + nvl(p_g_ae_line_tbl(l_ctr1).entered_cr,0);
2880
2881 ae_tax_activity_tbl(l_ctr).ae_acctd_amount := ae_tax_activity_tbl(l_ctr).ae_acctd_amount
2882 + nvl(p_g_ae_line_tbl(l_ctr1).accounted_dr,0) * -1
2883 + nvl(p_g_ae_line_tbl(l_ctr1).accounted_cr,0) ;
2884
2885 ae_tax_activity_tbl(l_ctr).ae_taxable_amount := ae_tax_activity_tbl(l_ctr).ae_taxable_amount
2886 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_entered_dr,0) * -1
2887 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_entered_cr,0);
2888
2889 ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount := ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount
2890 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_accounted_dr,0) * -1
2891 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_accounted_cr,0);
2892
2893 l_cached := TRUE;
2894
2895 END IF;
2896
2897 END LOOP; --for activity table from ar_distributions
2898
2899 END IF; --activity table exists for already cached entries
2900
2901 /*---------------------------------------------------------------------------------+
2902 |If an entry is not already cached when retrieved from ar_distributions then cache|
2903 +---------------------------------------------------------------------------------*/
2904 IF ((NOT l_cached)
2905 AND ((p_calling_point = 'TRAN')
2906 OR ((p_calling_point IN ('BILL', 'BLTR'))
2907 AND nvl(p_br_cust_trx_line_id,-999) = nvl(p_g_ae_line_tbl(l_ctr1).source_id_secondary,-999)))
2908 AND (p_g_ae_line_tbl(l_ctr1).ae_line_type = 'DEFERRED_TAX'))
2909 THEN
2910
2911 IF PG_DEBUG in ('Y', 'C') THEN
2912 arp_standard.debug('Process_Recon: ' || '4) Now caching in cache ae_tax_activity_tbl');
2913 END IF;
2914
2915 l_tax_ctr1 := l_tax_ctr1 + 1;
2916
2917 ae_tax_activity_tbl(l_tax_ctr1).ae_location_segment_id := p_g_ae_line_tbl(l_ctr1).location_segment_id;
2918
2919 ae_tax_activity_tbl(l_tax_ctr1).ae_tax_group_code_id := p_g_ae_line_tbl(l_ctr1).tax_group_code_id;
2920
2921 ae_tax_activity_tbl(l_tax_ctr1).ae_tax_code_id := p_g_ae_line_tbl(l_ctr1).tax_code_id;
2922
2923 ae_tax_activity_tbl(l_tax_ctr1).ae_code_combination_id := p_g_ae_line_tbl(l_ctr1).account;
2924
2925 ae_tax_activity_tbl(l_tax_ctr1).ae_amount :=
2926 nvl(p_g_ae_line_tbl(l_ctr1).entered_dr,0) * -1 + nvl(p_g_ae_line_tbl(l_ctr1).entered_cr,0);
2927
2928 ae_tax_activity_tbl(l_tax_ctr1).ae_acctd_amount :=
2929 nvl(p_g_ae_line_tbl(l_ctr1).accounted_dr,0) * -1 + nvl(p_g_ae_line_tbl(l_ctr1).accounted_cr,0);
2930
2931 ae_tax_activity_tbl(l_tax_ctr1).ae_taxable_amount :=
2932 nvl(p_g_ae_line_tbl(l_ctr1).taxable_entered_dr,0) * -1
2933 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_entered_cr,0);
2934
2935 ae_tax_activity_tbl(l_tax_ctr1).ae_taxable_acctd_amount :=
2936 nvl(p_g_ae_line_tbl(l_ctr1).taxable_accounted_dr,0) * -1
2937 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_accounted_cr,0);
2938
2939 l_cached := TRUE;
2940
2941 IF PG_DEBUG in ('Y', 'C') THEN
2942 arp_standard.debug('Process_Recon: ' || ' ');
2943 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_tax_ctr1||').ae_location_segment_id = ' || ae_tax_activity_tbl(l_tax_ctr1).ae_location_segment_id);
2944 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_tax_ctr1||').ae_tax_group_code_id = ' || ae_tax_activity_tbl(l_tax_ctr1).ae_tax_group_code_id);
2945 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_tax_ctr1||').ae_tax_code_id = ' || ae_tax_activity_tbl(l_tax_ctr1).ae_tax_code_id);
2946 arp_standard.debug('Process_Recon: ' || ' ');
2947 END IF;
2948
2949 END IF; --not cached then cache
2950
2951 END LOOP; --all activity
2952
2953 END IF; --entries exist in the global accounting table for a activity
2954
2955 /*------------------------------------------------------------------------------------------+
2956 |Reconcile the simulated application accounting for deferred tax for the shadow adjustment |
2957 |accounting with that of the physically stored accounting entries in ar_distributions, due |
2958 |to activity on the Bill |
2959 +------------------------------------------------------------------------------------------*/
2960 IF PG_DEBUG in ('Y', 'C') THEN
2961 arp_standard.debug('Process_Recon: ' || 'Reconciling original accounting with the activity accounting ');
2962 END IF;
2963
2964 IF ae_tax_tbl.EXISTS(l_tax_ctr) THEN
2965
2966 IF PG_DEBUG in ('Y', 'C') THEN
2967 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl Exists , ae_tax_activity_tbl Exists ');
2968 END IF;
2969
2970 FOR l_ctr IN ae_tax_tbl.FIRST .. ae_tax_tbl.LAST LOOP
2971
2972 IF PG_DEBUG in ('Y', 'C') THEN
2973 arp_standard.debug('Process_Recon: ' || 'Looping through table ae_tax_tbl to Reconcile l_ctr ' || l_ctr);
2974 arp_standard.debug('Process_Recon: ' || ' ');
2975 arp_standard.debug('Process_Recon: ' || '******** ');
2976 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_ctr||').ae_location_segment_id = '|| ae_tax_tbl(l_ctr).ae_location_segment_id);
2977 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_ctr||').ae_tax_group_code_id = '|| ae_tax_tbl(l_ctr).ae_tax_group_code_id);
2978 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_ctr||').ae_tax_code_id = '|| ae_tax_tbl(l_ctr).ae_tax_code_id);
2979 arp_standard.debug('Process_Recon: ' || '******** ');
2980 END IF;
2981
2982 l_match_cond := FALSE;
2983
2984 IF ae_tax_activity_tbl.EXISTS(l_tax_ctr1) THEN
2985
2986 FOR l_ctr1 IN ae_tax_activity_tbl.FIRST .. ae_tax_activity_tbl.LAST LOOP
2987
2988 IF PG_DEBUG in ('Y', 'C') THEN
2989 arp_standard.debug('Process_Recon: ' || 'Looping through table ae_tax_activity_tbl l_ctr1 ' || l_ctr1);
2990 arp_standard.debug('Process_Recon: ' || ' ');
2991 arp_standard.debug('Process_Recon: ' || '>>>>>>>> COMPARE');
2992 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_ctr1||').ae_location_segment_id = '|| ae_tax_activity_tbl(l_ctr1).ae_location_segment_id);
2993 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_ctr1||').ae_tax_group_code_id = '|| ae_tax_activity_tbl(l_ctr1).ae_tax_group_code_id);
2994 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_ctr1||').ae_tax_code_id = '|| ae_tax_activity_tbl(l_ctr1).ae_tax_code_id);
2995 arp_standard.debug('Process_Recon: ' || 'Amount ' || ae_tax_tbl(l_ctr).ae_amount || ' VS ' || ae_tax_activity_tbl(l_ctr1).ae_amount);
2996 arp_standard.debug('Process_Recon: ' || 'Accounted Amount ' || ae_tax_tbl(l_ctr).ae_acctd_amount || ' VS ' || ae_tax_activity_tbl(l_ctr1).ae_acctd_amount);
2997 arp_standard.debug('Process_Recon: ' || 'Taxable Amount ' || ae_tax_tbl(l_ctr).ae_taxable_amount || ' VS ' || ae_tax_activity_tbl(l_ctr1).ae_taxable_amount);
2998 arp_standard.debug('Process_Recon: ' || 'Taxable Accounted Amount ' || ae_tax_tbl(l_ctr).ae_taxable_acctd_amount || ' VS ' || ae_tax_activity_tbl(l_ctr1).ae_taxable_acctd_amount);
2999 arp_standard.debug('Process_Recon: ' || '>>>>>>>> COMPARE');
3000 END IF;
3001
3002 IF (((ae_tax_tbl(l_ctr).ae_location_segment_id IS NOT NULL)
3003 AND (ae_tax_activity_tbl(l_ctr1).ae_location_segment_id IS NOT NULL)
3004 AND (nvl(ae_tax_tbl(l_ctr).ae_location_segment_id,-999)
3005 = nvl(ae_tax_activity_tbl(l_ctr1).ae_location_segment_id,-999)))
3006 OR ((nvl(ae_tax_tbl(l_ctr).ae_tax_group_code_id,-999)
3007 = nvl(ae_tax_activity_tbl(l_ctr1).ae_tax_group_code_id,-999))
3008 AND (ae_tax_tbl(l_ctr).ae_tax_code_id IS NOT NULL)
3009 AND (ae_tax_activity_tbl(l_ctr1).ae_tax_code_id IS NOT NULL)
3010 AND (nvl(ae_tax_tbl(l_ctr).ae_tax_code_id,-999)
3011 = nvl(ae_tax_activity_tbl(l_ctr1).ae_tax_code_id,-999)))
3012 AND (ae_tax_tbl(l_ctr).ae_code_combination_id = ae_tax_activity_tbl(l_ctr1).ae_code_combination_id))
3013 THEN
3014
3015 IF PG_DEBUG in ('Y', 'C') THEN
3016 arp_standard.debug('Process_Recon: ' || 'Matching condition found in ae_tax_tbl, construct reconcile entry ');
3017 END IF;
3018
3019 ae_tax_activity_tbl(l_ctr1).ae_match_flag := 'Y';
3020
3021 l_match_cond := TRUE;
3022 l_cre_rec := FALSE;
3023 l_ae_line_rec := l_ae_empty_line_rec;
3024
3025 --deferred tax amounts
3026 IF ((ae_tax_tbl(l_ctr).ae_amount + ae_tax_activity_tbl(l_ctr1).ae_amount) < 0) THEN
3027
3028 l_ae_line_rec.entered_dr := NULL;
3029
3030 l_ae_line_rec.entered_cr :=
3031 abs(ae_tax_tbl(l_ctr).ae_amount + ae_tax_activity_tbl(l_ctr1).ae_amount);
3032
3033 l_cre_rec := TRUE;
3034
3035 ELSIF ((ae_tax_tbl(l_ctr).ae_amount + ae_tax_activity_tbl(l_ctr1).ae_amount) > 0) THEN
3036
3037 l_ae_line_rec.entered_dr :=
3038 abs(ae_tax_tbl(l_ctr).ae_amount + ae_tax_activity_tbl(l_ctr1).ae_amount);
3039
3040 l_ae_line_rec.entered_cr := NULL;
3041
3042 l_cre_rec := TRUE;
3043
3044 END IF;
3045
3046 --deferred tax accounted amounts
3047 IF ((ae_tax_tbl(l_ctr).ae_acctd_amount + ae_tax_activity_tbl(l_ctr1).ae_acctd_amount) < 0) THEN
3048
3049 l_ae_line_rec.accounted_dr := NULL;
3050
3051 l_ae_line_rec.accounted_cr :=
3052 abs(ae_tax_tbl(l_ctr).ae_acctd_amount + ae_tax_activity_tbl(l_ctr1).ae_acctd_amount) ;
3053
3054 l_cre_rec := TRUE;
3055
3056 ELSIF ((ae_tax_tbl(l_ctr).ae_acctd_amount + ae_tax_activity_tbl(l_ctr1).ae_acctd_amount) > 0) THEN
3057
3058 l_ae_line_rec.accounted_dr :=
3059 abs(ae_tax_tbl(l_ctr).ae_acctd_amount + ae_tax_activity_tbl(l_ctr1).ae_acctd_amount);
3060
3061 l_ae_line_rec.accounted_cr := NULL;
3062
3063 l_cre_rec := TRUE;
3064
3065 END IF;
3066
3067 --taxable amounts
3068 IF ((ae_tax_tbl(l_ctr).ae_taxable_amount + ae_tax_activity_tbl(l_ctr1).ae_taxable_amount) < 0) THEN
3069
3070 l_ae_line_rec.taxable_entered_dr := NULL;
3071
3072 l_ae_line_rec.taxable_entered_cr :=
3073 abs(ae_tax_tbl(l_ctr).ae_taxable_amount + ae_tax_activity_tbl(l_ctr1).ae_taxable_amount);
3074
3075 l_cre_rec := TRUE;
3076
3077 ELSIF ((ae_tax_tbl(l_ctr).ae_taxable_amount + ae_tax_activity_tbl(l_ctr1).ae_taxable_amount) > 0) THEN
3078
3079 l_ae_line_rec.taxable_entered_dr :=
3080 abs(ae_tax_tbl(l_ctr).ae_taxable_amount + ae_tax_activity_tbl(l_ctr1).ae_taxable_amount);
3081
3082 l_ae_line_rec.taxable_entered_cr := NULL;
3083
3084 l_cre_rec := TRUE;
3085
3086 END IF;
3087
3088 --taxable accounted amounts
3089 IF ((ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
3090 + ae_tax_activity_tbl(l_ctr1).ae_taxable_acctd_amount) < 0) THEN
3091
3092 l_ae_line_rec.taxable_accounted_dr := NULL;
3093
3094 l_ae_line_rec.taxable_accounted_cr :=
3095 abs(ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
3096 + ae_tax_activity_tbl(l_ctr1).ae_taxable_acctd_amount);
3097
3098 l_cre_rec := TRUE;
3099
3100 ELSIF ((ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
3101 + ae_tax_activity_tbl(l_ctr1).ae_taxable_acctd_amount) > 0) THEN
3102
3103 l_ae_line_rec.taxable_accounted_dr :=
3104 abs(ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
3105 + ae_tax_activity_tbl(l_ctr1).ae_taxable_acctd_amount);
3106
3107 l_ae_line_rec.taxable_accounted_cr := NULL;
3108
3109 l_cre_rec := TRUE;
3110
3111 END IF;
3112
3113 EXIT; --loop activity table because tax and activity table match
3114
3115 END IF; --deferred tax codes for tax and activity table match
3116
3117 END LOOP; --activity table
3118
3119 END IF; -- Tax activity table exists
3120
3121 /*---------------------------------------------------------------------------------+
3122 |If no matching condition between tax table and tax activity table, then it means |
3123 |we need to create a reconciliation entry matching the original tax on the Bills |
3124 |assignment or transaction. |
3125 +---------------------------------------------------------------------------------*/
3126 IF (NOT l_match_cond) THEN
3127
3128 IF PG_DEBUG in ('Y', 'C') THEN
3129 arp_standard.debug('Process_Recon: ' || 'Matching condition not found in ae_tax_tbl, construct reconcile entry ');
3130 END IF;
3131
3132 --set amount
3133 IF ae_tax_tbl(l_ctr).ae_amount > 0 THEN
3134
3135 l_ae_line_rec.entered_dr := abs(ae_tax_tbl(l_ctr).ae_amount);
3136 l_ae_line_rec.entered_cr := NULL;
3137 l_cre_rec := TRUE;
3138 ELSIF ae_tax_tbl(l_ctr).ae_amount < 0 THEN
3139 l_ae_line_rec.entered_dr := NULL;
3140 l_ae_line_rec.entered_cr := abs(ae_tax_tbl(l_ctr).ae_amount);
3141 l_cre_rec := TRUE;
3142 END IF;
3143
3144 --set accounted amount
3145 IF ae_tax_tbl(l_ctr).ae_acctd_amount > 0 THEN
3146
3147 l_ae_line_rec.accounted_dr := abs(ae_tax_tbl(l_ctr).ae_acctd_amount);
3148 l_ae_line_rec.accounted_cr := NULL;
3149 l_cre_rec := TRUE;
3150 ELSIF ae_tax_tbl(l_ctr).ae_acctd_amount < 0 THEN
3151 l_ae_line_rec.accounted_dr := NULL;
3152 l_ae_line_rec.accounted_cr := abs(ae_tax_tbl(l_ctr).ae_acctd_amount);
3153 l_cre_rec := TRUE;
3154 END IF;
3155
3156 --set taxable amount
3157 IF ae_tax_tbl(l_ctr).ae_taxable_amount > 0 THEN
3158 l_ae_line_rec.taxable_entered_dr := abs(ae_tax_tbl(l_ctr).ae_taxable_amount);
3159 l_ae_line_rec.taxable_entered_cr := NULL;
3160 l_cre_rec := TRUE;
3161 ELSIF ae_tax_tbl(l_ctr).ae_taxable_amount < 0 THEN
3162 l_ae_line_rec.taxable_entered_dr := NULL;
3163 l_ae_line_rec.taxable_entered_cr := abs(ae_tax_tbl(l_ctr).ae_taxable_amount);
3164 l_cre_rec := TRUE;
3165 END IF;
3166
3167 --set taxable accounted amount
3168 IF ae_tax_tbl(l_ctr).ae_taxable_acctd_amount > 0 THEN
3169 l_ae_line_rec.taxable_accounted_dr := abs(ae_tax_tbl(l_ctr).ae_taxable_acctd_amount);
3170 l_ae_line_rec.taxable_accounted_cr := NULL;
3171 l_cre_rec := TRUE;
3172 ELSIF ae_tax_tbl(l_ctr).ae_taxable_acctd_amount < 0 THEN
3173 l_ae_line_rec.taxable_accounted_dr := NULL;
3174 l_ae_line_rec.taxable_accounted_cr := abs(ae_tax_tbl(l_ctr).ae_taxable_acctd_amount);
3175 l_cre_rec := TRUE;
3176 END IF;
3177
3178 END IF; --no matching condition
3179
3180 /*---------------------------------------------------------------------------------+
3181 |Build the ar distributions accounting record for cache into the global accounting|
3182 |table. |
3183 +---------------------------------------------------------------------------------*/
3184 IF (l_cre_rec) THEN --set other attributes of accounting lines reconciliation entry
3185
3186 IF PG_DEBUG in ('Y', 'C') THEN
3187 arp_standard.debug('Process_Recon: ' || 'Assemble the l_ae_line_rec record for reconciliation entry ');
3188 END IF;
3189
3190 --Build the Deferred Tax accounting entry
3191 Build_Deferred_Tax(
3192 p_customer_trx_id => p_customer_trx_id ,
3193 p_br_cust_trx_line_id => p_br_cust_trx_line_id ,
3194 p_location_segment_id => ae_tax_tbl(l_ctr).ae_location_segment_id ,
3195 p_tax_group_code_id => ae_tax_tbl(l_ctr).ae_tax_group_code_id ,
3196 p_tax_code_id => ae_tax_tbl(l_ctr).ae_tax_code_id ,
3197 p_code_combination_id => ae_tax_tbl(l_ctr).ae_code_combination_id ,
3198 p_ae_doc_rec => p_ae_doc_rec ,
3199 p_cust_inv_rec => p_cust_inv_rec ,
3200 p_calling_point => p_calling_point ,
3201 p_ae_line_rec => l_ae_line_rec );
3202
3203 --Assign tax lines reconciliation record to global accounting table
3204 Assign_Elements(p_ae_line_rec => l_ae_line_rec ,
3205 p_g_ae_ctr => p_g_ae_ctr ,
3206 p_g_ae_line_tbl => p_g_ae_line_tbl );
3207
3208 --Build the Collected tax accounting entry
3209 Build_Tax (p_customer_trx_id => p_customer_trx_id,
3210 p_location_segment_id => ae_tax_tbl(l_ctr).ae_location_segment_id ,
3211 p_tax_group_code_id => ae_tax_tbl(l_ctr).ae_tax_group_code_id ,
3212 p_tax_code_id => ae_tax_tbl(l_ctr).ae_tax_code_id ,
3213 p_code_combination_id => ae_tax_tbl(l_ctr).ae_code_combination_id ,
3214 p_ae_line_rec => l_ae_line_rec );
3215
3216 --Assign tax lines reconciliation record to global accounting table
3217 Assign_Elements(p_ae_line_rec => l_ae_line_rec ,
3218 p_g_ae_ctr => p_g_ae_ctr ,
3219 p_g_ae_line_tbl => p_g_ae_line_tbl);
3220
3221 END IF; --create reconciliation accounting record
3222
3223 END LOOP; --tax table
3224
3225 IF PG_DEBUG in ('Y', 'C') THEN
3226 arp_standard.debug('Process_Recon: ' || 'REVERSE SWEEP ');
3227 END IF;
3228
3229 /*----------------------------------------------------------------------------+
3230 | Sweep through the tax activity table and if the matching flag is not Y then|
3231 | it means that the combination of tax group, tax code, tax account or tax |
3232 | location and account does not exist on the Original Transaction - so back |
3233 | out NOCOPY the deferred tax. |
3234 +----------------------------------------------------------------------------*/
3235 IF ae_tax_activity_tbl.EXISTS(l_tax_ctr1) THEN
3236
3237 FOR l_ctr IN ae_tax_activity_tbl.FIRST .. ae_tax_activity_tbl.LAST LOOP
3238
3239 IF nvl(ae_tax_activity_tbl(l_ctr).ae_match_flag, 'N') <> 'Y' THEN
3240
3241 --Initialize record
3242 l_ae_line_rec := l_ae_empty_line_rec;
3243
3244 /*------------------------------------------------------------------+
3245 | Set the deferred tax accounting buckets, and taxable buckets for |
3246 | creation of the Deferred tax reversal on accounting created due |
3247 | to activity as there is no match for on Original Transaction by |
3248 | tax group, tax code, location and account |
3249 +------------------------------------------------------------------*/
3250 IF PG_DEBUG in ('Y', 'C') THEN
3251 arp_standard.debug('Process_Recon: ' || 'REVERSE SWEEP ae_tax_activity_tbl(l_ctr).ae_amount' || ae_tax_activity_tbl(l_ctr).ae_amount);
3252 END IF;
3253 IF ae_tax_activity_tbl(l_ctr).ae_amount > 0 THEN
3254
3255 l_ae_line_rec.entered_dr := abs(ae_tax_activity_tbl(l_ctr).ae_amount);
3256 l_ae_line_rec.entered_cr := NULL;
3257 l_cre_rec := TRUE;
3258 ELSIF ae_tax_activity_tbl(l_ctr).ae_amount < 0 THEN
3259 l_ae_line_rec.entered_dr := NULL;
3260 l_ae_line_rec.entered_cr := abs(ae_tax_activity_tbl(l_ctr).ae_amount);
3261 l_cre_rec := TRUE;
3262 END IF;
3263
3264 IF PG_DEBUG in ('Y', 'C') THEN
3265 arp_standard.debug('Process_Recon: ' || 'REVERSE SWEEP ae_tax_activity_tbl(l_ctr).ae_acctd_amount' || ae_tax_activity_tbl(l_ctr).ae_acctd_amount);
3266 END IF;
3267 --set accounted amount
3268 IF ae_tax_activity_tbl(l_ctr).ae_acctd_amount > 0 THEN
3269
3270 l_ae_line_rec.accounted_dr := abs(ae_tax_activity_tbl(l_ctr).ae_acctd_amount);
3271 l_ae_line_rec.accounted_cr := NULL;
3272 l_cre_rec := TRUE;
3273 ELSIF ae_tax_activity_tbl(l_ctr).ae_acctd_amount < 0 THEN
3274 l_ae_line_rec.accounted_dr := NULL;
3275 l_ae_line_rec.accounted_cr := abs(ae_tax_activity_tbl(l_ctr).ae_acctd_amount);
3276 l_cre_rec := TRUE;
3277 END IF;
3278
3279 IF PG_DEBUG in ('Y', 'C') THEN
3280 arp_standard.debug('Process_Recon: ' || 'REVERSE SWEEP ae_tax_activity_tbl(l_ctr).ae_taxable_amount' || ae_tax_activity_tbl(l_ctr).ae_taxable_amount);
3281 END IF;
3282 --set taxable amount
3283 IF ae_tax_activity_tbl(l_ctr).ae_taxable_amount > 0 THEN
3284 l_ae_line_rec.taxable_entered_dr := abs(ae_tax_activity_tbl(l_ctr).ae_taxable_amount);
3285 l_ae_line_rec.taxable_entered_cr := NULL;
3286 l_cre_rec := TRUE;
3287 ELSIF ae_tax_activity_tbl(l_ctr).ae_taxable_amount < 0 THEN
3288 l_ae_line_rec.taxable_entered_dr := NULL;
3289 l_ae_line_rec.taxable_entered_cr := abs(ae_tax_activity_tbl(l_ctr).ae_taxable_amount);
3290 l_cre_rec := TRUE;
3291 END IF;
3292
3293 IF PG_DEBUG in ('Y', 'C') THEN
3294 arp_standard.debug('Process_Recon: ' || 'REVERSE SWEEP ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount' || ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount);
3295 END IF;
3296 --set taxable accounted amount
3297 IF ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount > 0 THEN
3298 l_ae_line_rec.taxable_accounted_dr := abs(ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount);
3299 l_ae_line_rec.taxable_accounted_cr := NULL;
3300 l_cre_rec := TRUE;
3301 ELSIF ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount < 0 THEN
3302 l_ae_line_rec.taxable_accounted_dr := NULL;
3303 l_ae_line_rec.taxable_accounted_cr := abs(ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount);
3304 l_cre_rec := TRUE;
3305 END IF;
3306
3307 --Build the Deferred Tax accounting entry
3308 Build_Deferred_Tax(
3309 p_customer_trx_id => p_customer_trx_id ,
3310 p_br_cust_trx_line_id => p_br_cust_trx_line_id ,
3311 p_location_segment_id => ae_tax_activity_tbl(l_ctr).ae_location_segment_id ,
3312 p_tax_group_code_id => ae_tax_activity_tbl(l_ctr).ae_tax_group_code_id ,
3313 p_tax_code_id => ae_tax_activity_tbl(l_ctr).ae_tax_code_id ,
3314 p_code_combination_id => ae_tax_activity_tbl(l_ctr).ae_code_combination_id ,
3315 p_ae_doc_rec => p_ae_doc_rec ,
3316 p_cust_inv_rec => p_cust_inv_rec ,
3317 p_calling_point => p_calling_point ,
3318 p_ae_line_rec => l_ae_line_rec );
3319
3320 --Assign tax lines reconciliation record to global accounting table
3321 Assign_Elements(p_ae_line_rec => l_ae_line_rec ,
3322 p_g_ae_ctr => p_g_ae_ctr ,
3323 p_g_ae_line_tbl => p_g_ae_line_tbl );
3324
3325 --Build the Collected tax accounting entry
3326 Build_Tax (p_customer_trx_id => p_customer_trx_id,
3327 p_location_segment_id => ae_tax_activity_tbl(l_ctr).ae_location_segment_id ,
3328 p_tax_group_code_id => ae_tax_activity_tbl(l_ctr).ae_tax_group_code_id ,
3329 p_tax_code_id => ae_tax_activity_tbl(l_ctr).ae_tax_code_id ,
3330 p_code_combination_id => ae_tax_activity_tbl(l_ctr).ae_code_combination_id ,
3331 p_ae_line_rec => l_ae_line_rec );
3332
3333 --Assign tax lines reconciliation record to global accounting table
3334 Assign_Elements(p_ae_line_rec => l_ae_line_rec ,
3335 p_g_ae_ctr => p_g_ae_ctr ,
3336 p_g_ae_line_tbl => p_g_ae_line_tbl);
3337
3338 END IF;
3339
3340 END LOOP; --tax activity table
3341
3342 END IF; --tax activity table exists
3343
3344 END IF; --lines exist in tax and activity table
3345
3346 IF PG_DEBUG in ('Y', 'C') THEN
3347 arp_standard.debug('ARP_RECONCILE.Process_Recon ()-');
3348 END IF;
3349
3350 EXCEPTION
3351 WHEN NO_DATA_FOUND THEN
3352 IF PG_DEBUG in ('Y', 'C') THEN
3353 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Process_Recon ');
3354 END IF;
3355 RAISE;
3356
3357 WHEN OTHERS THEN
3358 IF PG_DEBUG in ('Y', 'C') THEN
3359 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Process_Recon ');
3360 END IF;
3361 RAISE;
3362
3363 END Process_Recon;
3364
3365 /*========================================================================
3366 | PRIVATE PROCEDURE Build_Deferred_Tax
3367 |
3368 | DESCRIPTION
3369 | Builds the the deferred tax accounting entry for Reconciliation
3370 | of the accounting, sets currency details, accounts, source and
3371 | tax group, tax codes or location
3372 |
3373 | PARAMETERS
3374 | p_customer_trx_id IN Transaction Id
3375 | p_br_cust_trx_line_id IN Bills assignment line id
3376 | p_location_segment_id IN Location segment
3377 | p_tax_group_code_id IN Group Code
3378 | p_tax_code_id IN Tax Code Id
3379 | p_code_combination_id IN Ccid of deferred tax account
3380 | p_ae_doc_rec IN Document Record
3381 | p_cust_inv_rec IN Exchange rate details record
3382 | p_ae_line_rec IN Line record
3383 +-----------------------------------------------------------------------------*/
3384 PROCEDURE Build_Deferred_Tax (p_customer_trx_id IN NUMBER,
3385 p_br_cust_trx_line_id IN NUMBER,
3386 p_location_segment_id IN NUMBER,
3387 p_tax_group_code_id IN NUMBER,
3388 p_tax_code_id IN NUMBER,
3389 p_code_combination_id IN NUMBER,
3390 p_ae_doc_rec IN ae_doc_rec_type,
3391 p_cust_inv_rec IN ra_customer_trx%ROWTYPE,
3392 p_calling_point IN VARCHAR2,
3393 p_ae_line_rec IN OUT NOCOPY ae_line_rec_type ) IS
3394
3395 BEGIN
3396
3397 IF PG_DEBUG in ('Y', 'C') THEN
3398 arp_standard.debug('ARP_RECONCILE.Build_Deferred_Tax ()+');
3399 END IF;
3400
3401 /*-----------------------------------------------------------------------------+
3402 | Create the Dr or Cr to the deferred tax account, set details such as rates, |
3403 | source type secondary , tax group, tax code or location id. |
3404 +-----------------------------------------------------------------------------*/
3405 IF p_location_segment_id IS NOT NULL THEN
3406 p_ae_line_rec.location_segment_id := p_location_segment_id;
3407 ELSE
3408 p_ae_line_rec.tax_group_code_id := p_tax_group_code_id;
3409 p_ae_line_rec.tax_code_id := p_tax_code_id;
3410 END IF;
3411
3412 /*-----------------------------------------------------------------------------+
3413 | Assign Currency Exchange rate information to initialisation record, tax link|
3414 | id is not populated. Create the Dr or Cr to the deferred tax account. |
3415 +-----------------------------------------------------------------------------*/
3416 p_ae_line_rec.source_id := p_ae_doc_rec.source_id ;
3417 p_ae_line_rec.source_table := p_ae_doc_rec.source_table ;
3418 p_ae_line_rec.ae_line_type := 'DEFERRED_TAX' ;
3419 p_ae_line_rec.account := p_code_combination_id ;
3420
3421 /*------------------------------------------------------------------------------+
3422 | Populate the secondary columns, for Bills Receivable we populate with the |
3423 | Bill line id, however for transactions only the source type secondary is used|
3424 +------------------------------------------------------------------------------*/
3425 IF (p_calling_point IN ('BILL', 'BLTR')) THEN
3426 IF PG_DEBUG in ('Y', 'C') THEN
3427 arp_standard.debug('Build_Deferred_Tax: ' || 'Setting source type secondary to ASSIGNMENT_RECONCILE');
3428 END IF;
3429
3430 --set transaction reconciliation entries line type secondary when bill is closed
3431 IF (p_calling_point = 'BLTR') THEN
3432 p_ae_line_rec.ae_line_type_secondary := 'RECONCILE' ;
3433 ELSE
3434 p_ae_line_rec.ae_line_type_secondary := 'ASSIGNMENT_RECONCILE' ;
3435 END IF;
3436
3437 p_ae_line_rec.source_id_secondary := p_br_cust_trx_line_id ;
3438 p_ae_line_rec.source_table_secondary := 'CTL' ;
3439 ELSE --for transactions we only populate source type secondary for deferred tax
3440 IF PG_DEBUG in ('Y', 'C') THEN
3441 arp_standard.debug('Build_Deferred_Tax: ' || 'Setting source type secondary to RECONCILE');
3442 END IF;
3443 p_ae_line_rec.ae_line_type_secondary := 'RECONCILE';
3444 p_ae_line_rec.source_id_secondary := p_customer_trx_id;
3445 p_ae_line_rec.source_table_secondary := 'CT';
3446 END IF;
3447
3448 p_ae_line_rec.currency_code := p_cust_inv_rec.invoice_currency_code ;
3449 p_ae_line_rec.currency_conversion_rate := p_cust_inv_rec.exchange_rate ;
3450 p_ae_line_rec.currency_conversion_type := p_cust_inv_rec.exchange_rate_type ;
3451 p_ae_line_rec.currency_conversion_date := p_cust_inv_rec.exchange_date ;
3452 p_ae_line_rec.third_party_id := p_cust_inv_rec.bill_to_customer_id ;
3453 p_ae_line_rec.third_party_sub_id := p_cust_inv_rec.bill_to_site_use_id ;
3454 p_ae_line_rec.tax_link_id := '' ;
3455 p_ae_line_rec.reversed_source_id := '' ;
3456
3457 IF PG_DEBUG in ('Y', 'C') THEN
3458 arp_standard.debug('ARP_RECONCILE.Build_Deferred_Tax ()-');
3459 END IF;
3460
3461 EXCEPTION
3462 WHEN NO_DATA_FOUND THEN
3463 IF PG_DEBUG in ('Y', 'C') THEN
3464 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Build_Deferred_Tax ');
3465 END IF;
3466 RAISE;
3467
3468 WHEN OTHERS THEN
3469 IF PG_DEBUG in ('Y', 'C') THEN
3470 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Build_Deferred_Tax ');
3471 END IF;
3472 RAISE;
3473
3474 END Build_Deferred_Tax;
3475
3476 /*========================================================================
3477 | PRIVATE PROCEDURE Build_Tax
3478 |
3479 | DESCRIPTION
3480 | Builds the line record swapping the amounts and taxable amounts.
3481 | Sets the account.
3482 |
3483 | PARAMETERS
3484 | p_customer_trx_id IN Transaction Id
3485 | p_location_segment_id IN Location segment
3486 | p_tax_group_code_id IN Group Code
3487 | p_tax_code_id IN Tax Code Id
3488 | p_code_combination_id IN Ccid of deferred tax account
3489 | p_ae_line_rec IN Line record
3490 +-----------------------------------------------------------------------------*/
3491 PROCEDURE Build_Tax (p_customer_trx_id IN NUMBER,
3492 p_location_segment_id IN NUMBER,
3493 p_tax_group_code_id IN NUMBER,
3494 p_tax_code_id IN NUMBER,
3495 p_code_combination_id IN NUMBER,
3496 p_ae_line_rec IN OUT NOCOPY ae_line_rec_type ) IS
3497
3498 l_collected_ccid NUMBER;
3499 l_swap_amt NUMBER;
3500
3501 BEGIN
3502 IF PG_DEBUG in ('Y', 'C') THEN
3503 arp_standard.debug('ARP_RECONCILE.Build_Tax ()+');
3504 END IF;
3505
3506 /*-----------------------------------------------------------------------------+
3507 | Create the Offsetting Dr or Cr to the Collected tax account. To do this, the|
3508 | tax code or location is used to retrieve the collected tax account. |
3509 | Retrieve the offsetting collected tax account from the Invoices tax code or |
3510 | location from the accounting distributions. Note if the same tax group, tax |
3511 | code or location segment for a deferred tax account has more than one |
3512 | collected tax account, then the max of the ccid contains the reconciled |
3513 | difference. This may happen if it is possible to change distributions |
3514 | manually. Ideally the combination of deferred and collected tax accounts |
3515 | will not change. |
3516 +-----------------------------------------------------------------------------*/
3517 IF PG_DEBUG in ('Y', 'C') THEN
3518 arp_standard.debug('Build_Tax: ' || 'Fetching offsetting collected tax accounting entry');
3519 arp_standard.debug('Build_Tax: ' || 'Using parameter p_customer_trx_id ' || p_customer_trx_id);
3520 arp_standard.debug('Build_Tax: ' || 'Using parameter location_segment_id ' || p_location_segment_id);
3521 arp_standard.debug('Build_Tax: ' || 'Using parameter tax_group_code_id ' || p_tax_group_code_id);
3522 arp_standard.debug('Build_Tax: ' || 'Using parameter tax_code_id ' || p_tax_code_id);
3523 arp_standard.debug('Build_Tax: ' || 'Using parameter code_combination_id ' || p_code_combination_id);
3524 END IF;
3525
3526 --In R12 the vat tax id also called the tax rate id is the unique key
3527 --there is no concept of tax group id and location segment id is no longer
3528 --used - it is all vat tax id on TAX line type
3529 SELECT max(gld.collected_tax_ccid) ae_collected_tax_ccid
3530 INTO l_collected_ccid
3531 FROM ra_cust_trx_line_gl_dist gld,
3532 ra_customer_trx_lines ctl
3533 --ra_customer_trx_lines ctl1
3534 WHERE ctl.customer_trx_id = p_customer_trx_id
3535 AND gld.customer_trx_id = ctl.customer_trx_id
3536 AND gld.customer_trx_line_id = ctl.customer_trx_line_id
3537 AND gld.account_class = 'TAX'
3538 AND gld.account_set_flag = 'N'
3539 AND gld.collected_tax_ccid IS NOT NULL --deferred tax only
3540 AND gld.code_combination_id = p_code_combination_id
3541 -- AND (((p_location_segment_id IS NOT NULL)
3542 -- AND (ctl.location_segment_id = nvl(p_location_segment_id,-999)))
3543 --AND (p_tax_code_id IS NOT NULL)
3544 AND ctl.vat_tax_id = nvl(p_tax_code_id,-999)
3545 --AND ctl.link_to_cust_trx_line_id = ctl1.customer_trx_line_id
3546 --AND ctl1.vat_tax_id = nvl(p_tax_group_code_id,ctl1.vat_tax_id)
3547 AND not exists (select 'x'
3548 from ra_customer_trx_lines ctl1
3549 where ctl1.customer_trx_id = p_customer_trx_id
3550 and ctl1.autorule_complete_flag = 'N');
3551 --bug7484223
3552 IF l_collected_ccid IS NULL then
3553
3554 IF PG_DEBUG in ('Y', 'C') THEN
3555 arp_standard.debug('This code will be called in very rare scenario.');
3556 arp_standard.debug('This will derive tax ccid if above routine fails to derive.');
3557 END IF;
3558
3559 SELECT max(gld.collected_tax_ccid) ae_collected_tax_ccid
3560 INTO l_collected_ccid
3561 FROM ra_cust_trx_line_gl_dist gld,
3562 ra_customer_trx_lines ctl
3563 --ra_customer_trx_lines ctl1
3564 WHERE ctl.customer_trx_id = p_customer_trx_id
3565 AND gld.customer_trx_id = ctl.customer_trx_id
3566 AND gld.customer_trx_line_id = ctl.customer_trx_line_id
3567 AND gld.account_class = 'TAX'
3568 AND gld.account_set_flag = 'N'
3569 AND gld.collected_tax_ccid IS NOT NULL --deferred tax only
3570 -- AND gld.code_combination_id = p_code_combination_id
3571 -- AND (((p_location_segment_id IS NOT NULL)
3572 -- AND (ctl.location_segment_id = nvl(p_location_segment_id,-999)))
3573 --AND (p_tax_code_id IS NOT NULL)
3574 AND ctl.vat_tax_id = nvl(p_tax_code_id,-999)
3575 --AND ctl.link_to_cust_trx_line_id = ctl1.customer_trx_line_id
3576 --AND ctl1.vat_tax_id = nvl(p_tax_group_code_id,ctl1.vat_tax_id)
3577 AND not exists (select 'x'
3578 from ra_customer_trx_lines ctl1
3579 where ctl1.customer_trx_id = p_customer_trx_id
3580 and ctl1.autorule_complete_flag = 'N');
3581 END IF;
3582
3583 IF l_collected_ccid IS NULL then
3584
3585 /* In case above routine did not derive tax ccid from INV GLD if given
3586 vat_tax_id not exist in CTL, then ccid will be taken from existing
3587 app ard against the same INV. This is rarest scenario. */
3588
3589 IF PG_DEBUG in ('Y', 'C') THEN
3590 arp_standard.debug('This will derive tax ccid from app ard against the INV.');
3591 END IF;
3592
3593 SELECT MAX(code_combination_id)
3594 INTO l_collected_ccid
3595 FROM
3596 (SELECT ard.code_combination_id
3597 FROM ar_distributions ard
3598 WHERE ard.source_table = 'RA'
3599 AND source_id IN (SELECT receivable_application_id
3600 FROM ar_receivable_applications
3601 WHERE applied_customer_trx_id = p_customer_trx_id)
3602 AND ard.tax_code_id = nvl(p_tax_code_id,-999)
3603 AND source_type = 'TAX'
3604 AND source_type_secondary = 'PAYMENT'
3605 UNION
3606 SELECT ard.code_combination_id
3607 FROM ar_distributions ard
3608 WHERE ard.source_table = 'ADJ'
3609 AND source_id IN (SELECT adjustment_id
3610 FROM ar_adjustments
3611 WHERE customer_trx_id = p_customer_trx_id
3612 AND status = 'A')
3613 AND ard.tax_code_id = nvl(p_tax_code_id,-999)
3614 AND source_type = 'TAX'
3615 );
3616
3617 END IF;
3618
3619 IF PG_DEBUG in ('Y', 'C') THEN
3620 arp_standard.debug('Build_Tax: Collected CCID: '||l_collected_ccid);
3621 arp_standard.debug('Build_Tax: ' || 'Completed fetching offsetting collected tax accounting entry');
3622 END IF;
3623
3624 p_ae_line_rec.ae_line_type := 'TAX';
3625 p_ae_line_rec.account := l_collected_ccid;
3626
3627 --Now swap debits and credits for the Collected tax amounts
3628 l_swap_amt := p_ae_line_rec.entered_dr;
3629 p_ae_line_rec.entered_dr := p_ae_line_rec.entered_cr;
3630 p_ae_line_rec.entered_cr := l_swap_amt;
3631
3632 --Now swap debits and credits for the Collected tax accounted amounts
3633 l_swap_amt := p_ae_line_rec.accounted_dr;
3634 p_ae_line_rec.accounted_dr := p_ae_line_rec.accounted_cr;
3635 p_ae_line_rec.accounted_cr := l_swap_amt;
3636
3637 --Now swap debits and credits for the Collected taxable amounts
3638 l_swap_amt := p_ae_line_rec.taxable_entered_dr;
3639 p_ae_line_rec.taxable_entered_dr := p_ae_line_rec.taxable_entered_cr;
3640 p_ae_line_rec.taxable_entered_cr := l_swap_amt;
3641
3642 --Now swap debits and credits for the Collected taxable accounted amounts
3643 l_swap_amt := p_ae_line_rec.taxable_accounted_dr;
3644 p_ae_line_rec.taxable_accounted_dr := p_ae_line_rec.taxable_accounted_cr;
3645 p_ae_line_rec.taxable_accounted_cr := l_swap_amt;
3646
3647 IF PG_DEBUG in ('Y', 'C') THEN
3648 arp_standard.debug('ARP_RECONCILE.Build_Tax ()-');
3649 END IF;
3650
3651 EXCEPTION
3652 WHEN NO_DATA_FOUND THEN
3653 IF PG_DEBUG in ('Y', 'C') THEN
3654 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Build_Tax ');
3655 END IF;
3656 RAISE;
3657
3658 WHEN OTHERS THEN
3659 IF PG_DEBUG in ('Y', 'C') THEN
3660 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Build_Tax ');
3661 END IF;
3662 RAISE;
3663
3664 END Build_Tax;
3665
3666 END ARP_RECONCILE;
[Home] [Help]