1 PACKAGE BODY ARP_RECONCILE AS
2 /* $Header: ARTRECBB.pls 120.11 2008/02/19 10:34:33 dgaurab 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 'x'
1054 from ar_payment_schedules ps
1055 where ps.customer_trx_id in (
1056 select customer_trx_id
1057 from ra_customer_trx_lines
1058 start with br_ref_customer_trx_id = p_customer_trx_id
1059 connect by prior customer_trx_id = br_ref_customer_trx_id
1060 )
1061 and ps.status = 'OP'
1062 and ps.customer_trx_id <> g_orig_cust_trx_id
1063 );
1064
1065 IF PG_DEBUG in ('Y', 'C') THEN
1066 arp_standard.debug('ARP_RECONCILE.Check_all_bills_closed, -set p_all_br_closed to N ');
1067 arp_standard.debug('ARP_RECONCILE.Check_all_bills_closed ()- ');
1068 END IF;
1069
1070 EXCEPTION
1071 WHEN NO_DATA_FOUND then
1072 IF PG_DEBUG in ('Y', 'C') THEN
1073 arp_standard.debug('NO_DATA_FOUND : ARP_RECONCILE.Check_all_bills_closed, -set p_all_br_closed to Y ');
1074 END IF;
1075 p_all_br_closed := 'Y';
1076 WHEN OTHERS then
1077 IF PG_DEBUG in ('Y', 'C') THEN
1078 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Check_all_bills_closed ');
1079 END IF;
1080 RAISE;
1081
1082 END Check_all_bills_closed;
1083
1084 /*========================================================================
1085 | PRIVATE PROCEDURE Reconcile_trx_tax
1086 |
1087 | DESCRIPTION
1088 | Reconciles the transaction deferred tax accounting. For transactions
1089 | with no CM activity, reconciles actual accounting against the original
1090 | tax on the Invoice. In case there is CM activity, since CM's have their
1091 | own accounting, reconciles the actual non CM activity related accounting
1092 | with accounting derived as a result of simulating a single activity equal
1093 | to the sum of the non CM related activity and reconciles by tax code and
1094 | account.
1095 |
1096 | PARAMETERS
1097 | p_mode IN Document or Accounting Event mode
1098 | p_ae_doc_rec IN Document Record
1099 | p_ae_event_rec IN Event Record
1100 | p_ae_sys_rec IN System parameter details
1101 | p_cust_inv_rec IN Contains currency, exchange rate, site
1102 | details for the bill
1103 | p_g_ae_ctr IN OUT NOCOPY counter for lines table
1104 | p_g_ae_line_tbl IN OUT NOCOPY lines table containing reconciled entry
1105 *=======================================================================*/
1106 PROCEDURE Reconcile_trx_tax(
1107 p_mode IN VARCHAR2 ,
1108 p_ae_doc_rec IN ae_doc_rec_type ,
1109 p_ae_event_rec IN ae_event_rec_type ,
1110 p_ae_sys_rec IN ae_sys_rec_type ,
1111 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
1112 p_customer_trx_id IN NUMBER ,
1113 p_br_cust_trx_line_id IN NUMBER ,
1114 p_calling_point IN VARCHAR2 ,
1115 p_pay_class IN VARCHAR2 ,
1116 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
1117 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type ) IS
1118
1119
1120 CURSOR get_single_activity IS
1121 SELECT pay.payment_schedule_id payment_schedule_id,
1122 sum( nvl(app.amount_applied,0) +
1123 nvl(app.earned_discount_taken,0) +
1124 nvl(app.unearned_discount_taken,0)) amount,
1125 sum(nvl(app.acctd_amount_applied_to,0) +
1126 nvl(app.acctd_earned_discount_taken,0) +
1127 nvl(app.acctd_unearned_discount_taken,0)) acctd_amount,
1128 sum(nvl(app.line_applied,0) +
1129 nvl(app.line_ediscounted,0) +
1130 nvl(app.line_uediscounted,0)) line_amount,
1131 sum(nvl(app.tax_applied,0) +
1132 nvl(app.tax_ediscounted,0) +
1133 nvl(app.tax_uediscounted,0)) tax_amount,
1134 sum(nvl(app.freight_applied,0) +
1135 nvl(app.freight_ediscounted,0) +
1136 nvl(app.freight_uediscounted,0)) freight_amount,
1137 sum(nvl(app.receivables_charges_applied,0) +
1138 nvl(app.charges_ediscounted,0) +
1139 nvl(app.charges_uediscounted,0)) receivables_charges_amount
1140 FROM ar_receivable_applications app,
1141 ar_payment_schedules pay
1142 WHERE app.applied_customer_trx_id = p_customer_trx_id
1143 AND app.status = 'APP'
1144 AND nvl(app.confirmed_flag, 'Y') = 'Y'
1145 AND app.applied_payment_schedule_id = pay.payment_schedule_id
1146 AND app.application_type = 'CASH' --only payments result in movement of
1147 GROUP by pay.payment_schedule_id
1148 UNION ALL --get adjustment bucket details
1149 SELECT pay.payment_schedule_id payment_schedule_id,
1150 sum(nvl(adj.amount,0) * -1) amount,
1151 sum(nvl(adj.acctd_amount,0) * -1) acctd_amount,
1152 sum(nvl(adj.line_adjusted,0) * -1) line_amount,
1153 sum(nvl(adj.tax_adjusted,0) * -1) tax_amount,
1154 sum(nvl(adj.freight_adjusted,0) * -1) freight_amount,
1155 sum(nvl(adj.receivables_charges_adjusted,0) * -1) receivables_charges_amount
1156 FROM ar_adjustments adj,
1157 ar_payment_schedules pay
1158 WHERE adj.customer_trx_id = p_customer_trx_id
1159 AND adj.payment_schedule_id = pay.payment_schedule_id
1160 AND adj.status = 'A'
1161 GROUP by pay.payment_schedule_id;
1162
1163 l_pay_tbl g_pay_tbl_type;
1164 l_pay_empty_tbl g_pay_tbl_type;
1165
1166 l_accum_amount NUMBER := 0;
1167 l_accum_acctd_amt NUMBER := 0;
1168 l_accum_line_amt NUMBER := 0;
1169 l_accum_tax_amt NUMBER := 0;
1170 l_accum_freight_amt NUMBER := 0;
1171 l_accum_charges_amt NUMBER := 0;
1172 l_accum_line_acctd_amt NUMBER := 0;
1173 l_accum_tax_acctd_amt NUMBER := 0;
1174 l_accum_freight_acctd_amt NUMBER := 0;
1175 l_accum_charges_acctd_amt NUMBER := 0;
1176 l_ctr NUMBER := 0;
1177 l_cm_amt NUMBER := 0;
1178 l_cm_acctd_amt NUMBER := 0;
1179 l_cm_line_amt NUMBER := 0;
1180 l_cm_tax_amt NUMBER := 0;
1181 l_cm_frt_amt NUMBER := 0;
1182 l_cm_chrg_amt NUMBER := 0;
1183 l_cached BOOLEAN;
1184 l_simul_activity VARCHAR2(1) := 'N';
1185 l_pay_ctr NUMBER := 0;
1186 l_all_br_closed VARCHAR2(1) := 'N';
1187
1188 BEGIN
1189
1190 IF PG_DEBUG in ('Y', 'C') THEN
1191 arp_standard.debug('ARP_RECONCILE.Reconcile_Trx_Tax ()+ ');
1192 arp_standard.debug('Reconcile_trx_tax: ' || 'Input Parameters ');
1193 arp_standard.debug('Reconcile_trx_tax: ' || 'p_customer_trx_id ' || p_customer_trx_id);
1194 arp_standard.debug('Reconcile_trx_tax: ' || 'p_br_cust_trx_line_id ' || p_br_cust_trx_line_id);
1195 arp_standard.debug('Reconcile_trx_tax: ' || 'p_calling_point ' || p_calling_point);
1196 END IF;
1197
1198 /*-----------------------------------------------------------------------------+
1199 | All chained Bills containing the current transaction assignment must be |
1200 | closed for this process to commence. The current Bill may be assigned to |
1201 | several other Bills so it is important to ensure that these Bills in turn |
1202 | are closed. This is because the deferred tax liability on the originating |
1203 | Bill is transfered to all Bills, so the starting Bill assignments |
1204 | representating deferred tax on the original Transactions must be reconciled |
1205 | only if this condition is true. |
1206 +----------------------------------------------------------------------------*/
1207 Check_all_bills_closed(p_customer_trx_id => p_customer_trx_id ,
1208 p_all_br_closed => l_all_br_closed );
1209
1210 /*---------------------------------------------------------------------------+
1211 |Since the deferred tax liability for this transaction exists on Bills which|
1212 |are still open |hence the transaction reconciliation entry will be created |
1213 |when the Bill is closed so do not process. |
1214 +---------------------------------------------------------------------------*/
1215 IF l_all_br_closed = 'N' THEN
1216 GOTO End_Transaction_Reconcile;
1217 END IF;
1218
1219 l_pay_tbl := l_pay_empty_tbl;
1220 l_pay_ctr := 0;
1221
1222 /*-----------------------------------------------------------------------------------------+
1223 |Level 1 Check.
1224 |Determine whether the Transaction being reconciled has CM applications which are non zero|
1225 |against it, if so then a flag is set to indicate that the reconciliation should be done |
1226 |by simulating non-CM activity as CM's have their own accounting, so no deferred tax is |
1227 |moved on application of the CM to the transaction, the deferred tax accounting is weighed|
1228 |by the accounting on the CM itself. Hence we reconcile against the actual deferred tax |
1229 |accounting createed against the simulated single non CM activity related accounting. |
1230 |In case there is no CM activity against a transaction, then we reconcile the actual |
1231 |deferred tax accounting created against the original tax accounting on the transaction. |
1232 |Since a user can create a CM (on account) and pay multiple deferred tax Transactions, we |
1233 |the tax accounting on the CM documents weighs the transactions to which it was applied. |
1234 +-----------------------------------------------------------------------------------------*/
1235 BEGIN
1236
1237 IF PG_DEBUG in ('Y', 'C') THEN
1238 arp_standard.debug('Reconcile_trx_tax: ' || 'Check CM applications for deferred tax reconciliation ()+ ');
1239 END IF;
1240
1241 l_cm_amt := 0; l_cm_acctd_amt := 0; l_cm_line_amt := 0;
1242 l_cm_tax_amt := 0; l_cm_frt_amt := 0; l_cm_chrg_amt := 0;
1243
1244 SELECT sum(nvl(app.amount_applied,0)) ,
1245 sum(nvl(app.acctd_amount_applied_to,0)),
1246 sum(nvl(app.line_applied,0)),
1247 sum(nvl(app.tax_applied,0)),
1248 sum(nvl(app.freight_applied,0)),
1249 sum(nvl(app.receivables_charges_applied,0))
1250 INTO l_cm_amt,
1251 l_cm_acctd_amt,
1252 l_cm_line_amt,
1253 l_cm_tax_amt,
1254 l_cm_frt_amt,
1255 l_cm_chrg_amt
1256 FROM ar_receivable_applications app
1257 WHERE app.applied_customer_trx_id = p_customer_trx_id
1258 AND app.application_type = 'CM'
1259 AND nvl(app.confirmed_flag, 'Y') = 'Y'
1260 AND app.status = 'APP';
1261
1262 IF ((l_cm_amt <> 0) OR (l_cm_acctd_amt <> 0) OR (l_cm_line_amt <> 0)
1263 OR (l_cm_tax_amt <> 0) OR (l_cm_frt_amt <> 0) OR (l_cm_chrg_amt <> 0)) THEN
1264 IF PG_DEBUG in ('Y', 'C') THEN
1265 arp_standard.debug('Reconcile_trx_tax: ' || 'Sum of CM applications to Transaction is not zero - simulate activity');
1266 END IF;
1267 l_simul_activity := 'Y';
1268 ELSE
1269 IF PG_DEBUG in ('Y', 'C') THEN
1270 arp_standard.debug('Reconcile_trx_tax: ' || 'Sum of CM applications is zero or no applications - do not simulate activity ');
1271 END IF;
1272 l_simul_activity := 'N';
1273 END IF;
1274
1275 IF PG_DEBUG in ('Y', 'C') THEN
1276 arp_standard.debug('Reconcile_trx_tax: ' || 'Check CM applications for deferred tax reconciliation ()- ');
1277 END IF;
1278
1279 EXCEPTION
1280 WHEN NO_DATA_FOUND THEN
1281 IF PG_DEBUG in ('Y', 'C') THEN
1282 arp_standard.debug('NO_DATA_FOUND : ARP_RECONCILE.Reconcile_trx_tax, CM applications do not exist ');
1283 END IF;
1284 l_simul_activity := 'N';
1285 IF PG_DEBUG in ('Y', 'C') THEN
1286 arp_standard.debug('Reconcile_trx_tax: ' || 'set l_simul_activity ' || l_simul_activity);
1287 END IF;
1288 WHEN OTHERS THEN
1289 IF PG_DEBUG in ('Y', 'C') THEN
1290 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Reconcile_trx_tax, in CM check applications sum');
1291 END IF;
1292 RAISE;
1293
1294 END; --block to determine CM activity on transaction
1295
1296
1297 /*-------------------------------------------------------------------------------------------+
1298 |Level 2 check. |
1299 |Check whether applications from CM to Transaction are not zero if so then the CM has been |
1300 |applied to other transactions, and has in effect reduced its deferred tax amounts by tax |
1301 |code - since we do not create deferred tax movements on CM application to Trx, hence we |
1302 |simulate non CM activity on the CM which resulted in reducing its payment schedule balance.|
1303 |to reconcile its deferred tax. |
1304 +-------------------------------------------------------------------------------------------*/
1305 IF (l_simul_activity = 'N') AND (p_pay_class = 'CM') THEN
1306 BEGIN
1307
1308 IF PG_DEBUG in ('Y', 'C') THEN
1309 arp_standard.debug('Reconcile_trx_tax: ' || 'Check applications from CM to Transactions ()+ ');
1310 END IF;
1311
1312 l_cm_amt := 0; l_cm_acctd_amt := 0; l_cm_line_amt := 0;
1313 l_cm_tax_amt := 0; l_cm_frt_amt := 0; l_cm_chrg_amt := 0;
1314
1315 SELECT sum(nvl(app.amount_applied,0)) ,
1316 sum(nvl(app.acctd_amount_applied_to,0)),
1317 sum(nvl(app.line_applied,0)),
1318 sum(nvl(app.tax_applied,0)),
1319 sum(nvl(app.freight_applied,0)),
1320 sum(nvl(app.receivables_charges_applied,0))
1321 INTO l_cm_amt,
1322 l_cm_acctd_amt,
1323 l_cm_line_amt,
1324 l_cm_tax_amt,
1325 l_cm_frt_amt,
1326 l_cm_chrg_amt
1327 FROM ar_receivable_applications app
1328 WHERE app.customer_trx_id = p_customer_trx_id
1329 AND app.application_type = 'CM'
1330 AND nvl(app.confirmed_flag, 'Y') = 'Y'
1331 AND app.status = 'APP';
1332
1333 IF ((l_cm_amt <> 0) OR (l_cm_acctd_amt <> 0) OR (l_cm_line_amt <> 0)
1334 OR (l_cm_tax_amt <> 0) OR (l_cm_frt_amt <> 0) OR (l_cm_chrg_amt <> 0)) THEN
1335 IF PG_DEBUG in ('Y', 'C') THEN
1336 arp_standard.debug('Reconcile_trx_tax: ' || 'Applications from CM to Transaction are not zero - simulate activity');
1337 END IF;
1338 l_simul_activity := 'Y';
1339 ELSE
1340 IF PG_DEBUG in ('Y', 'C') THEN
1341 arp_standard.debug('Reconcile_trx_tax: ' || 'Applications from CM to Transaction are zero - do not simulate activity ');
1342 END IF;
1343 l_simul_activity := 'N';
1344 END IF;
1345
1346 IF PG_DEBUG in ('Y', 'C') THEN
1347 arp_standard.debug('Reconcile_trx_tax: ' || 'Check applications from CM to Transactions ()- ');
1348 END IF;
1349
1350 EXCEPTION
1351 WHEN NO_DATA_FOUND THEN
1352 IF PG_DEBUG in ('Y', 'C') THEN
1353 arp_standard.debug('NO_DATA_FOUND : ARP_RECONCILE.Reconcile_trx_tax, Applications from CM ' ||
1354 'to Transaction do not exist ');
1355 END IF;
1356 l_simul_activity := 'N';
1357 IF PG_DEBUG in ('Y', 'C') THEN
1358 arp_standard.debug('Reconcile_trx_tax: ' || 'set l_simul_activity ' || l_simul_activity);
1359 END IF;
1360 WHEN OTHERS THEN
1361 IF PG_DEBUG in ('Y', 'C') THEN
1362 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Reconcile_trx_tax, in CM applications ' ||
1363 'from CM to Transaction');
1364 END IF;
1365 RAISE;
1366
1367 END; --block to determine applications from CM to other transactions
1368 END IF;
1369
1370 /*--------------------------------------------------------------------+
1371 |Cache the payment schedule details into the table if already cached |
1372 +--------------------------------------------------------------------*/
1373 IF PG_DEBUG in ('Y', 'C') THEN
1374 arp_standard.debug('Reconcile_trx_tax: ' || 'Processing Non CM activity ');
1375 END IF;
1376
1377 IF (l_simul_activity = 'Y') THEN
1378
1379 IF PG_DEBUG in ('Y', 'C') THEN
1380 arp_standard.debug('Reconcile_trx_tax: ' || 'l_simul_activity ' || l_simul_activity);
1381 END IF;
1382
1383 FOR l_activity IN get_single_activity LOOP
1384
1385 IF PG_DEBUG in ('Y', 'C') THEN
1386 arp_standard.debug('Reconcile_trx_tax: ' || 'In loop get single activity');
1387 END IF;
1388
1389 l_cached := FALSE;
1390
1391 IF l_pay_tbl.EXISTS(l_pay_ctr) THEN
1392
1393 FOR l_ctr IN l_pay_tbl.FIRST .. l_pay_tbl.LAST LOOP
1394
1395 IF (l_pay_tbl(l_ctr).applied_payment_schedule_id = l_activity.payment_schedule_id)
1396 THEN
1397 IF PG_DEBUG in ('Y', 'C') THEN
1398 arp_standard.debug('Reconcile_trx_tax: ' || '5) Hit found in cache');
1399 END IF;
1400
1401 --Set the application record buckets
1402 l_pay_tbl(l_ctr).applied_customer_trx_id := p_customer_trx_id;
1403
1404 l_pay_tbl(l_ctr).applied_payment_schedule_id := l_activity.payment_schedule_id;
1405
1406 l_pay_tbl(l_ctr).amount_applied :=
1407 l_pay_tbl(l_ctr).amount_applied + l_activity.amount ;
1408
1409 l_pay_tbl(l_ctr).acctd_amount_applied_to :=
1410 l_pay_tbl(l_ctr).acctd_amount_applied_to + l_activity.acctd_amount ;
1411
1412 l_pay_tbl(l_ctr).line_applied :=
1413 l_pay_tbl(l_ctr).line_applied +l_activity.line_amount ;
1414
1415 l_pay_tbl(l_ctr).tax_applied :=
1416 l_pay_tbl(l_ctr).tax_applied + l_activity.tax_amount ;
1417
1418 l_pay_tbl(l_ctr).freight_applied :=
1419 l_pay_tbl(l_ctr).freight_applied + l_activity.freight_amount ;
1420
1421 l_pay_tbl(l_ctr).receivables_charges_applied :=
1422 l_pay_tbl(l_ctr).receivables_charges_applied + l_activity.receivables_charges_amount ;
1423
1424 l_cached := TRUE;
1425
1426 IF PG_DEBUG in ('Y', 'C') THEN
1427 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').applied_customer_trx_id = '||l_pay_tbl(l_ctr).applied_customer_trx_id);
1428 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').applied_payment_schedule_id = '||l_pay_tbl(l_ctr).applied_payment_schedule_id);
1429 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').amount_applied = '||l_pay_tbl(l_ctr).amount_applied);
1430 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').acctd_amount_applied_to = '||l_pay_tbl(l_ctr).acctd_amount_applied_to);
1431 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').line_applied = '||l_pay_tbl(l_ctr).line_applied);
1432 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').tax_applied= '||l_pay_tbl(l_ctr).tax_applied);
1433 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').freight_applied= '||l_pay_tbl(l_ctr).freight_applied);
1434 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_ctr||').receivables_charges_applied= '||l_pay_tbl(l_ctr).receivables_charges_applied);
1435 END IF;
1436 END IF; --add to cache
1437
1438 END LOOP; --process cached lines in payment table
1439
1440 END IF; --payment schedule amounts table exists
1441
1442 /*------------------------------------------------------------------------+
1443 |Cache the payment schedule details into the table if not already cached |
1444 +------------------------------------------------------------------------*/
1445 IF (NOT l_cached) THEN
1446
1447 IF PG_DEBUG in ('Y', 'C') THEN
1448 arp_standard.debug('Reconcile_trx_tax: ' || '5) Now caching');
1449 END IF;
1450
1451 l_pay_ctr := l_pay_ctr + 1;
1452
1453 --Set the application record buckets
1454 l_pay_tbl(l_pay_ctr).applied_customer_trx_id := p_customer_trx_id;
1455 l_pay_tbl(l_pay_ctr).applied_payment_schedule_id := l_activity.payment_schedule_id;
1456 l_pay_tbl(l_pay_ctr).amount_applied := l_activity.amount ;
1457 l_pay_tbl(l_pay_ctr).acctd_amount_applied_to := l_activity.acctd_amount ;
1458 l_pay_tbl(l_pay_ctr).line_applied := l_activity.line_amount ;
1459 l_pay_tbl(l_pay_ctr).tax_applied := l_activity.tax_amount ;
1460 l_pay_tbl(l_pay_ctr).freight_applied := l_activity.freight_amount ;
1461 l_pay_tbl(l_pay_ctr).receivables_charges_applied := l_activity.receivables_charges_amount ;
1462
1463 l_cached := TRUE;
1464
1465 IF PG_DEBUG in ('Y', 'C') THEN
1466 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);
1467 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);
1468 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').amount_applied = '||l_pay_tbl(l_pay_ctr).amount_applied);
1469 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);
1470 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').line_applied = '||l_pay_tbl(l_pay_ctr).line_applied);
1471 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').tax_applied= '||l_pay_tbl(l_pay_ctr).tax_applied);
1472 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').freight_applied= '||l_pay_tbl(l_pay_ctr).freight_applied);
1473 arp_standard.debug('Reconcile_trx_tax: ' || 'l_pay_tbl('||l_pay_ctr||').receivables_charges_applied= '||l_pay_tbl(l_pay_ctr).receivables_charges_applied);
1474 END IF;
1475
1476 END IF; --not cached
1477
1478 END LOOP; --process all activities for the current transaction
1479
1480 END IF; --Simulate activity on document
1481
1482 /*--------------------------------------------------------------------------+
1483 | Call the common Routine, to simulate an application for activity by |
1484 | payment schedule.To reconcile against the actual accounting created in |
1485 | the distributions accounting table. |
1486 +--------------------------------------------------------------------------*/
1487 Process_Recon( p_mode => p_mode ,
1488 p_ae_doc_rec => p_ae_doc_rec ,
1489 p_ae_event_rec => p_ae_event_rec ,
1490 p_ae_sys_rec => p_ae_sys_rec ,
1491 p_cust_inv_rec => p_cust_inv_rec ,
1492 p_br_cust_trx_line_id => p_br_cust_trx_line_id,
1493 p_customer_trx_id => p_customer_trx_id,
1494 p_simul_app => l_simul_activity ,
1495 p_calling_point => p_calling_point ,
1496 p_pay_ctr => l_pay_ctr ,
1497 p_pay_tbl => l_pay_tbl ,
1498 p_g_ae_ctr => p_g_ae_ctr ,
1499 p_g_ae_line_tbl => p_g_ae_line_tbl );
1500
1501 <<End_Transaction_Reconcile>>
1502 IF PG_DEBUG in ('Y', 'C') THEN
1503 arp_standard.debug('ARP_RECONCILE.Reconcile_trx_tax ()- ');
1504 END IF;
1505
1506 EXCEPTION
1507 WHEN NO_DATA_FOUND THEN
1508 IF PG_DEBUG in ('Y', 'C') THEN
1509 arp_standard.debug('EXCEPTION NO_DATA_FOUND: ARP_RECONCILE.Reconcile_trx_tax ');
1510 END IF;
1511 RAISE;
1512
1513 WHEN OTHERS THEN
1514 IF PG_DEBUG in ('Y', 'C') THEN
1515 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Reconcile_trx_tax ');
1516 END IF;
1517 RAISE;
1518
1519 END Reconcile_trx_tax;
1520
1521 /*========================================================================
1522 | PRIVATE PROCEDURE Reconcile_br_tax
1523 |
1524 | DESCRIPTION
1525 | Reconciles each assignment on the Bill. Reconciliation is done
1526 | only if the Bill is closed and all chained Bills are also closed.
1527 | If an assignment is a Bill then this function is called recursively
1528 | to go to the child bill and start processing with the same condition
1529 | checks as was done for the parent bill.
1530 |
1531 | PARAMETERS
1532 | p_mode IN Document or Accounting Event mode
1533 | p_ae_doc_rec IN Document Record
1534 | p_ae_event_rec IN Event Record
1535 | p_ae_sys_rec IN System parameter details
1536 | p_customer_trx_id IN Bills Receivable trx id ,
1537 | p_g_ae_ctr IN OUT NOCOPY counter for global accounting lines table
1538 | p_g_ae_line_tbl IN OUT NOCOPY accounting lines table containing reconciled
1539 | entry
1540 *=======================================================================*/
1541 PROCEDURE Reconcile_br_tax(
1542 p_mode IN VARCHAR2 ,
1543 p_ae_doc_rec IN ae_doc_rec_type ,
1544 p_ae_event_rec IN ae_event_rec_type ,
1545 p_ae_sys_rec IN ae_sys_rec_type ,
1546 p_customer_trx_id IN NUMBER ,
1547 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
1548 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type ) IS
1549
1550 CURSOR get_assignments(p_customer_trx_id IN NUMBER) is
1551 SELECT ctl.customer_trx_id br_cust_trx_id ,
1552 ctl.customer_trx_line_id br_customer_trx_line_id ,
1553 ctl.br_ref_customer_trx_id br_ref_customer_trx_id ,
1554 ctl.br_ref_payment_schedule_id br_ref_payment_schedule_id ,
1555 ct.drawee_site_use_id drawee_site_use_id ,
1556 ct.invoice_currency_code invoice_currency_code ,
1557 ct.exchange_rate exchange_rate,
1558 ct.exchange_rate_type exchange_rate_type,
1559 ct.exchange_date exchange_date,
1560 ct.trx_date trx_date ,
1561 ct.bill_to_customer_id bill_to_customer_id ,
1562 ct.bill_to_site_use_id bill_to_site_use_id ,
1563 adj.adjustment_id br_adj_id ,
1564 nvl(adj.amount,0) br_adj_amt ,
1565 nvl(adj.acctd_amount,0) br_adj_acctd_amt ,
1566 nvl(adj.line_adjusted,0) br_adj_line_amt ,
1567 nvl(adj.tax_adjusted,0) br_adj_tax_amt ,
1568 nvl(adj.freight_adjusted,0) br_adj_frt_amt ,
1569 nvl(adj.receivables_charges_adjusted,0) br_adj_chrg_amt
1570 FROM ra_customer_trx_lines ctl,
1571 ar_adjustments adj,
1572 ra_customer_trx ct
1573 WHERE ctl.customer_trx_id = p_customer_trx_id
1574 AND ctl.br_adjustment_id = adj.adjustment_id
1575 AND ct.customer_trx_id = ctl.br_ref_customer_trx_id
1576 AND adj.status = 'A'
1577 order by ctl.customer_trx_line_id;
1578
1579 TYPE l_br_rec_type IS RECORD (
1580 br_cust_trx_id NUMBER,
1581 br_customer_trx_line_id NUMBER,
1582 br_ref_customer_trx_id NUMBER,
1583 br_ref_payment_schedule_id NUMBER,
1584 drawee_site_use_id ra_customer_trx.drawee_site_use_id%TYPE,
1585 br_adj_id NUMBER,
1586 br_adj_amt NUMBER,
1587 br_adj_acctd_amt NUMBER,
1588 br_adj_line_amt NUMBER,
1589 br_adj_tax_amt NUMBER,
1590 br_adj_frt_amt NUMBER,
1591 br_adj_chrg_amt NUMBER,
1592 br_adj_line_acctd_amt NUMBER,
1593 br_adj_tax_acctd_amt NUMBER,
1594 br_adj_frt_acctd_amt NUMBER,
1595 br_adj_chrg_acctd_amt NUMBER
1596 );
1597
1598 TYPE l_br_tbl_type IS TABLE of l_br_rec_type
1599 INDEX BY BINARY_INTEGER;
1600
1601 l_cust_inv_rec ra_customer_trx%ROWTYPE;
1602
1603 l_assn_ctr BINARY_INTEGER := 0;
1604
1605 l_pay_tbl g_pay_tbl_type;
1606
1607 l_pay_empty_tbl g_pay_tbl_type;
1608
1609 l_pay_ctr BINARY_INTEGER := 0;
1610
1611 l_br_tbl l_br_tbl_type;
1612
1613 l_app_rec ar_receivable_applications%ROWTYPE;
1614
1615 --The bill closed flag is defaulted to N
1616 l_all_br_closed VARCHAR2(1) := 'N';
1617 l_pay_class ar_payment_schedules.class%TYPE;
1618 l_closed_pymt_yn VARCHAR2(1);
1619
1620 l_required BOOLEAN;
1621
1622 BEGIN
1623 IF PG_DEBUG in ('Y', 'C') THEN
1624 arp_standard.debug('ARP_RECONCILE.Reconcile_br_tax ()+');
1625 END IF;
1626
1627 /*----------------------------------------------------------------------------------------+
1628 | All chained Bills starting from the current Bill must be closed for the Reconciliation |
1629 | process to commence. The current Bill may be assigned to several other Bills so it is |
1630 | important to ensure that thes Bills in turn are closed. This is because the deferred |
1631 | tax liability on the originating Bill is transfered to all Bills, so the starting Bill |
1632 | assignments representating deferred tax on the original Transactions must be reconciled|
1633 | only if this condition is true. |
1634 +----------------------------------------------------------------------------------------*/
1635 Check_all_bills_closed(p_customer_trx_id => p_customer_trx_id ,
1636 p_all_br_closed => l_all_br_closed );
1637
1638 IF (l_all_br_closed = 'Y') THEN
1639
1640 /*----------------------------------------------------------------------------------+
1641 | Get the shadow adjustments record for usage by the tax accounting engine to |
1642 | create deferred tax accounting as though a single application was made to each |
1643 | shadow adjustment (transaction assignment). |
1644 +----------------------------------------------------------------------------------*/
1645 FOR l_assign_rec IN get_assignments(p_customer_trx_id) LOOP
1646
1647 l_assn_ctr := l_assn_ctr + 1;
1648
1649 l_br_tbl(l_assn_ctr).br_cust_trx_id := l_assign_rec.br_cust_trx_id;
1650 l_br_tbl(l_assn_ctr).br_customer_trx_line_id := l_assign_rec.br_customer_trx_line_id;
1651 l_br_tbl(l_assn_ctr).br_ref_customer_trx_id := l_assign_rec.br_ref_customer_trx_id;
1652 l_br_tbl(l_assn_ctr).br_ref_payment_schedule_id := l_assign_rec.br_ref_payment_schedule_id;
1653 l_br_tbl(l_assn_ctr).drawee_site_use_id := l_assign_rec.drawee_site_use_id;
1654 l_br_tbl(l_assn_ctr).br_adj_id := l_assign_rec.br_adj_id;
1655 l_br_tbl(l_assn_ctr).br_adj_amt := l_assign_rec.br_adj_amt;
1656 l_br_tbl(l_assn_ctr).br_adj_acctd_amt := l_assign_rec.br_adj_acctd_amt;
1657 l_br_tbl(l_assn_ctr).br_adj_line_amt := l_assign_rec.br_adj_line_amt;
1658 l_br_tbl(l_assn_ctr).br_adj_tax_amt := l_assign_rec.br_adj_tax_amt;
1659 l_br_tbl(l_assn_ctr).br_adj_frt_amt := l_assign_rec.br_adj_frt_amt;
1660 l_br_tbl(l_assn_ctr).br_adj_chrg_amt := l_assign_rec.br_adj_chrg_amt;
1661
1662 /*----------------------------------------------------------------------------------+
1663 | Derive the currency, exchange rate and third party information. Assignments on |
1664 | a bill could have different third part and third party sub id information, hence |
1665 | we rederive it. The currency and exchange rate details of assignments match Bill |
1666 +----------------------------------------------------------------------------------*/
1667 l_cust_inv_rec.invoice_currency_code := l_assign_rec.invoice_currency_code;
1668 l_cust_inv_rec.exchange_rate := l_assign_rec.exchange_rate;
1669 l_cust_inv_rec.exchange_rate_type := l_assign_rec.exchange_rate_type;
1670 l_cust_inv_rec.exchange_date := l_assign_rec.exchange_date;
1671 l_cust_inv_rec.trx_date := l_assign_rec.trx_date;
1672 l_cust_inv_rec.bill_to_customer_id := l_assign_rec.bill_to_customer_id;
1673 l_cust_inv_rec.bill_to_site_use_id := l_assign_rec.bill_to_site_use_id;
1674
1675 /*------------------------------------------------------------------------------+
1676 | Now create a application to simulate a single activity such as a payment to |
1677 | each shadow adjustment (transaction assignment) on the Bill. |
1678 +------------------------------------------------------------------------------*/
1679 l_pay_tbl := l_pay_empty_tbl;
1680 l_pay_ctr := 1; --always for each recursive call for a Bill to this routine
1681
1682 --Set the application record buckets
1683 l_pay_tbl(l_pay_ctr).applied_customer_trx_id := l_br_tbl(l_assn_ctr).br_ref_customer_trx_id;
1684 l_pay_tbl(l_pay_ctr).applied_payment_schedule_id := l_br_tbl(l_assn_ctr).br_ref_payment_schedule_id;
1685 l_pay_tbl(l_pay_ctr).amount_applied := l_br_tbl(l_assn_ctr).br_adj_amt * -1;
1686 l_pay_tbl(l_pay_ctr).acctd_amount_applied_to := l_br_tbl(l_assn_ctr).br_adj_acctd_amt * -1;
1687 l_pay_tbl(l_pay_ctr).line_applied := l_br_tbl(l_assn_ctr).br_adj_line_amt * -1;
1688 l_pay_tbl(l_pay_ctr).tax_applied := l_br_tbl(l_assn_ctr).br_adj_tax_amt * -1;
1689 l_pay_tbl(l_pay_ctr).freight_applied := l_br_tbl(l_assn_ctr).br_adj_frt_amt * -1;
1690 l_pay_tbl(l_pay_ctr).receivables_charges_applied := l_br_tbl(l_assn_ctr).br_adj_chrg_amt * -1;
1691
1692 IF PG_DEBUG in ('Y', 'C') THEN
1693 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP applied_customer_trx_id ' || l_pay_tbl(l_pay_ctr).applied_customer_trx_id);
1694 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP applied_payment_schedule_id ' || l_pay_tbl(l_pay_ctr).applied_payment_schedule_id);
1695 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP amount_applied ' || l_pay_tbl(l_pay_ctr).amount_applied);
1696 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP acctd_amount_applied_to ' || l_pay_tbl(l_pay_ctr).acctd_amount_applied_to);
1697 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP line_applied ' || l_pay_tbl(l_pay_ctr).line_applied);
1698 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP tax_applied ' || l_pay_tbl(l_pay_ctr).tax_applied);
1699 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP freight_applied ' || l_pay_tbl(l_pay_ctr).freight_applied);
1700 arp_standard.debug('Reconcile_br_tax: ' || 'l_pay_tbl APP receivables_charges_applied ' || l_pay_tbl(l_pay_ctr).receivables_charges_applied);
1701 arp_standard.debug('Reconcile_br_tax: ' || 'Drawee Site Id ' || l_br_tbl(l_assn_ctr).drawee_site_use_id);
1702 END IF;
1703
1704 /*--------------------------------------------------------------------------+
1705 | Recursive call required because current assignment is a bill of exchange.|
1706 | So the process of verifying that all Bills to which the current Bill is |
1707 | assigned are closed, so that the assignments on the Bill will be |
1708 | reconciled to simulate an application against each assignments shadow |
1709 | adjustment. |
1710 +--------------------------------------------------------------------------*/
1711 IF l_br_tbl(l_assn_ctr).drawee_site_use_id IS NOT NULL THEN
1712
1713 Reconcile_br_tax( p_mode => p_mode ,
1714 p_ae_doc_rec => p_ae_doc_rec ,
1715 p_ae_event_rec => p_ae_event_rec ,
1716 p_ae_sys_rec => p_ae_sys_rec ,
1717 p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1718 p_g_ae_ctr => p_g_ae_ctr ,
1719 p_g_ae_line_tbl => p_g_ae_line_tbl );
1720 ELSE
1721 /*--------------------------------------------------------------------------+
1722 | If the Transaction is not deferred then do not do any process as there is|
1723 | no deferred tax to reconcile. |
1724 +--------------------------------------------------------------------------*/
1725 Check_Entry(p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1726 p_type => 'TRAN',
1727 p_required => l_required );
1728
1729 IF (l_required) THEN
1730
1731 /*--------------------------------------------------------------------------+
1732 | Call the common Routine, to simulate an application for each adjustment |
1733 | on the bills line therby deriving accounting for single activity on Bills|
1734 | line. Subsequently retrieve accounting actually created from accounting |
1735 | table due to past activities and Reconcile what has been created with. |
1736 | what should have been created if there were single applications to each |
1737 | assignment. |
1738 +--------------------------------------------------------------------------*/
1739 Process_Recon( p_mode => p_mode ,
1740 p_ae_doc_rec => p_ae_doc_rec ,
1741 p_ae_event_rec => p_ae_event_rec ,
1742 p_ae_sys_rec => p_ae_sys_rec ,
1743 p_cust_inv_rec => l_cust_inv_rec ,
1744 p_br_cust_trx_line_id => l_br_tbl(l_assn_ctr).br_customer_trx_line_id,
1745 p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1746 p_simul_app => 'Y' ,
1747 p_calling_point => 'BILL' ,
1748 p_pay_ctr => l_pay_ctr ,
1749 p_pay_tbl => l_pay_tbl ,
1750 p_g_ae_ctr => p_g_ae_ctr ,
1751 p_g_ae_line_tbl => p_g_ae_line_tbl );
1752
1753 /*----------------------------------------------------------------------+
1754 | Determine whether the payment schedule of the Transaction is closed, |
1755 | only on closure do we need to create the Reconciliation entry. |
1756 +----------------------------------------------------------------------*/
1757 Detect_Closure(p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1758 p_pay_sched_upd_yn => 'Y',
1759 p_pay_sched_upd_cm_yn => null,
1760 p_activity_amt => 0 ,
1761 p_activity_acctd_amt => 0 ,
1762 p_ae_sys_rec => p_ae_sys_rec ,
1763 p_closed_pymt_yn => l_closed_pymt_yn,
1764 p_pay_class => l_pay_class );
1765
1766 /*--------------------------------------------------------------------------+
1767 | Call the Transaction Reconciliation routine for this assignment. This is |
1768 | necessary because the transaction which is closed and assigned to another|
1769 | Bill needs to be Reconciled after the Bills assignment reconciliation |
1770 | entry is built. The transaction reconciliation routine is called only if |
1771 | all Bills to which it has been assigned are also closed in addition to |
1772 | it being closed. It is is also important that the Transaction assignment |
1773 | must be closed to Reconcile it. |
1774 +--------------------------------------------------------------------------*/
1775 IF (l_closed_pymt_yn = 'Y') THEN
1776 Reconcile_trx_tax(p_mode => p_mode ,
1777 p_ae_doc_rec => p_ae_doc_rec ,
1778 p_ae_event_rec => p_ae_event_rec ,
1779 p_ae_sys_rec => p_ae_sys_rec ,
1780 p_cust_inv_rec => l_cust_inv_rec ,
1781 p_customer_trx_id => l_br_tbl(l_assn_ctr).br_ref_customer_trx_id,
1782 p_br_cust_trx_line_id => l_br_tbl(l_assn_ctr).br_customer_trx_line_id,
1783 p_calling_point => 'BLTR' ,
1784 p_pay_class => l_pay_class ,
1785 p_g_ae_ctr => p_g_ae_ctr ,
1786 p_g_ae_line_tbl => p_g_ae_line_tbl );
1787
1788 END IF; --payment schedule of Trx is closed
1789
1790 END IF; -- processing required for deferred transaction
1791
1792 END IF; --drawee site is not null
1793
1794 END LOOP; --process each shadow adjustment
1795
1796 END IF; --All chained bills are closed
1797
1798 IF PG_DEBUG in ('Y', 'C') THEN
1799 arp_standard.debug('ARP_RECONCILE.Reconcile_br_tax ()-');
1800 END IF;
1801
1802 EXCEPTION
1803 WHEN NO_DATA_FOUND THEN
1804 IF PG_DEBUG in ('Y', 'C') THEN
1805 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Reconcile_br_tax ');
1806 END IF;
1807 RAISE;
1808
1809 WHEN OTHERS THEN
1810 IF PG_DEBUG in ('Y', 'C') THEN
1811 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Reconcile_br_tax ');
1812 END IF;
1813 RAISE;
1814
1815 END Reconcile_br_tax;
1816
1817 /* ==========================================================================
1818 | PROCEDURE Detect_Closure
1819 |
1820 | DESCRIPTION
1821 | This routine detects whether a transaction is closed. Closure is defined
1822 | as a point where the sum total for the amount due remaining and the
1823 | accounted amount due remaining is zero for all installments on the
1824 | Bill or Transaction. This routine passes a flag indicating as to whether
1825 | reconciliation is required.
1826 |
1827 | SCOPE - PRIVATE
1828 |
1829 | PARAMETERS
1830 | p_customer_trx_id Transaction identifier
1831 | p_pay_sched_upd_yn Value denotes whether the payment
1832 | schedule been updated or not, if not
1833 | then this routine will add the activity
1834 | on the Bill or transaction to the
1835 | installments
1836 | to make this decision
1837 | p_activity_amt previous activity amount
1838 | p_activity_acctd_amt previous activity accounted amount
1839 | p_closed_pymt_yn A Y value indicates that the Bill or
1840 | transaction is a candidate for
1841 | reconciliation
1842 *==========================================================================*/
1843 PROCEDURE Detect_Closure(p_customer_trx_id IN NUMBER ,
1844 p_pay_sched_upd_yn IN VARCHAR2 ,
1845 p_pay_sched_upd_cm_yn IN VARCHAR2 ,
1846 p_activity_amt IN NUMBER ,
1847 p_activity_acctd_amt IN NUMBER ,
1848 p_ae_sys_rec IN ae_sys_rec_type,
1849 p_closed_pymt_yn OUT NOCOPY VARCHAR2 ,
1850 p_pay_class OUT NOCOPY VARCHAR2 ) IS
1851
1852 l_amount_due_remaining NUMBER := 0;
1853 l_acctd_amount_due_remaining NUMBER := 0;
1854 l_pay_sched_upd_yn VARCHAR2(1);
1855
1856 BEGIN
1857 IF PG_DEBUG in ('Y', 'C') THEN
1858 arp_standard.debug('ARP_RECONCILE.Detect_Closure()+');
1859 END IF;
1860
1861 /*---------------------------------------------------------------------------+
1862 | Retrieve amount and accounted amount remaining for all installments on the|
1863 | Transaction |
1864 +---------------------------------------------------------------------------*/
1865
1866 IF (p_ae_sys_rec.sob_type = 'P') THEN
1867 select sum(pay.amount_due_remaining) ,
1868 sum(pay.acctd_amount_due_remaining) ,
1869 max(pay.class)
1870 into l_amount_due_remaining,
1871 l_acctd_amount_due_remaining,
1872 p_pay_class
1873 from ar_payment_schedules pay
1874 where pay.customer_trx_id = p_customer_trx_id;
1875 END IF;
1876 IF PG_DEBUG in ('Y', 'C') THEN
1877 arp_standard.debug('Detect_Closure: ' || 'p_customer_trx_id ' || p_customer_trx_id);
1878 arp_standard.debug('Detect_Closure: ' || 'pay_class ' || p_pay_class);
1879 arp_standard.debug('Detect_Closure: ' || 'p_pay_sched_upd_yn ' || p_pay_sched_upd_yn);
1880 arp_standard.debug('Detect_Closure: ' || 'p_pay_sched_upd_cm_yn ' || p_pay_sched_upd_cm_yn);
1881 arp_standard.debug('Detect_Closure: ' || 'g_call_num ' || g_call_num);
1882 arp_standard.debug('Detect_Closure: ' || 'Parameter p_activity_amt ' || p_activity_amt);
1883 arp_standard.debug('Detect_Closure: ' || 'Parameter p_activity_acctd_amt ' || p_activity_acctd_amt);
1884 arp_standard.debug('Detect_Closure: ' || 'Selected pay l_amount_due_remaining ' || l_amount_due_remaining);
1885 arp_standard.debug('Detect_Closure: ' || 'Selected pay l_acctd_amount_due_remaining ' || l_acctd_amount_due_remaining);
1886 END IF;
1887
1888 --Set the payment schedule updated flag
1889 IF (p_pay_class = 'CM') AND (g_call_num = 2) AND (p_pay_sched_upd_cm_yn IS NOT NULL) THEN
1890 l_pay_sched_upd_yn := p_pay_sched_upd_cm_yn;
1891 ELSE
1892 l_pay_sched_upd_yn := p_pay_sched_upd_yn;
1893 END IF;
1894
1895 /*---------------------------------------------------------------------------+
1896 | Add this to the amount and accounted amount due to activity. Zero amounts |
1897 | will indicate that the Transaction has been closed. |
1898 +--------------------------------------------------------------------------*/
1899 IF (NVL(l_pay_sched_upd_yn, 'N') = 'N') THEN
1900 l_amount_due_remaining := l_amount_due_remaining + p_activity_amt;
1901 l_acctd_amount_due_remaining := l_acctd_amount_due_remaining +
1902 p_activity_acctd_amt;
1903
1904 IF PG_DEBUG in ('Y', 'C') THEN
1905 arp_standard.debug('Detect_Closure: ' || 'Payment schedule not updated hence calculating remaining amounts ');
1906 arp_standard.debug('Detect_Closure: ' || 'l_amount_due_remaining + p_activity_amt ' || l_amount_due_remaining);
1907 arp_standard.debug('Detect_Closure: ' || 'l_acctd_amount_due_remaining + p_activity_acctd_amt ' || l_acctd_amount_due_remaining);
1908 END IF;
1909 END IF;
1910
1911 /*---------------------------------------------------------------------+
1912 | Set the payment schedule closed flag to indicate as to whether |
1913 | reconciliation is required. |
1914 +---------------------------------------------------------------------*/
1915
1916 IF ((l_amount_due_remaining + l_acctd_amount_due_remaining) <> 0) THEN
1917 p_closed_pymt_yn := 'N'; --paymentschedule is not closed so do not call reconciliation routine
1918 IF PG_DEBUG in ('Y', 'C') THEN
1919 arp_standard.debug('Detect_Closure: ' || 'Transaction ' || p_customer_trx_id || ' payment schedule is not closed - do not reconcile ');
1920 END IF;
1921 ELSE
1922 p_closed_pymt_yn := 'Y';
1923 IF PG_DEBUG in ('Y', 'C') THEN
1924 arp_standard.debug('Detect_Closure: ' || 'Transaction ' || p_customer_trx_id || ' payment schedule is closed - reconcile ');
1925 END IF;
1926 END IF;
1927
1928 <<end_process_lbl1>>
1929
1930 IF PG_DEBUG in ('Y', 'C') THEN
1931 arp_standard.debug('ARP_RECONCILE.Detect_Closure()-');
1932 END IF;
1933
1934 EXCEPTION
1935 WHEN NO_DATA_FOUND THEN
1936 IF PG_DEBUG in ('Y', 'C') THEN
1937 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Detect_Closure ');
1938 END IF;
1939 RAISE;
1940
1941 WHEN OTHERS THEN
1942 IF PG_DEBUG in ('Y', 'C') THEN
1943 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Detect_Closure ');
1944 END IF;
1945 RAISE;
1946
1947 END Detect_Closure;
1948
1949 /* ==========================================================================
1950 | PROCEDURE Assign_Elements
1951 |
1952 | DESCRIPTION
1953 | Assign revenue or tax lines built to global table which will eventually
1954 | be summarized
1955 |
1956 | SCOPE - PRIVATE
1957 |
1958 | PARAMETERS
1959 | NONE
1960 *==========================================================================*/
1961 PROCEDURE Assign_Elements(p_ae_line_rec IN OUT NOCOPY ae_line_rec_type ,
1962 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
1963 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type ) IS
1964
1965 BEGIN
1966 IF PG_DEBUG in ('Y', 'C') THEN
1967 arp_standard.debug('ARP_RECONCILE.Assign_Elements()+');
1968 END IF;
1969
1970 /*--------------------------------------------------------------------------------+
1971 | Do not create 0 amount Reconciliation entries if tax and taxable amounts are 0 |
1972 +--------------------------------------------------------------------------------*/
1973 IF ((nvl(p_ae_line_rec.entered_dr,0) + nvl(p_ae_line_rec.entered_cr,0) +
1974 nvl(p_ae_line_rec.accounted_dr,0) + nvl(p_ae_line_rec.accounted_cr,0) +
1975 nvl(p_ae_line_rec.taxable_entered_dr,0) + nvl(p_ae_line_rec.taxable_entered_cr,0) +
1976 nvl(p_ae_line_rec.taxable_accounted_dr,0) + nvl(p_ae_line_rec.taxable_accounted_cr,0)) = 0)
1977 THEN
1978 GOTO end_assign_elements;
1979 END IF;
1980
1981 /*--------------------------------------------------------------------------------+
1982 | 1) Populate 0 values for the accounted amounts based on the sign of the amounts|
1983 +--------------------------------------------------------------------------------*/
1984 --Populate a 0 amount if the other bucket is null for accounted amounts
1985 IF ((p_ae_line_rec.entered_dr IS NOT NULL)
1986 AND (p_ae_line_rec.accounted_dr IS NULL) AND (p_ae_line_rec.accounted_cr IS NULL)) THEN
1987 p_ae_line_rec.accounted_dr := 0;
1988 ELSIF ((p_ae_line_rec.entered_cr IS NOT NULL)
1989 AND (p_ae_line_rec.accounted_cr IS NULL) AND (p_ae_line_rec.accounted_dr IS NULL)) THEN
1990 p_ae_line_rec.accounted_cr := 0;
1991 END IF;
1992
1993 /*--------------------------------------------------------------------------------+
1994 | 1) Populate 0 values for the amounts based on the sign of the accounted amounts|
1995 +--------------------------------------------------------------------------------*/
1996 IF ((p_ae_line_rec.accounted_dr IS NOT NULL)
1997 AND (p_ae_line_rec.entered_dr IS NULL) AND (p_ae_line_rec.entered_cr IS NULL)) THEN
1998 p_ae_line_rec.entered_dr := 0;
1999 ELSIF ((p_ae_line_rec.accounted_cr IS NOT NULL)
2000 AND (p_ae_line_rec.entered_cr IS NULL) AND (p_ae_line_rec.entered_dr IS NULL)) THEN
2001 p_ae_line_rec.entered_cr := 0;
2002 END IF;
2003
2004 /*--------------------------------------------------------------------------------+
2005 | 2) Populate 0 values for the taxable accounted amounts based on the sign of the|
2006 | taxable amounts |
2007 +--------------------------------------------------------------------------------*/
2008 IF ((p_ae_line_rec.taxable_entered_dr IS NOT NULL)
2009 AND (p_ae_line_rec.taxable_accounted_dr IS NULL) AND (p_ae_line_rec.taxable_accounted_cr IS NULL)) THEN
2010 p_ae_line_rec.taxable_accounted_dr := 0;
2011 ELSIF ((p_ae_line_rec.taxable_entered_cr IS NOT NULL)
2012 AND (p_ae_line_rec.taxable_accounted_cr IS NULL) AND (p_ae_line_rec.taxable_accounted_dr IS NULL)) THEN
2013 p_ae_line_rec.taxable_accounted_cr := 0;
2014 END IF;
2015
2016 /*--------------------------------------------------------------------------------+
2017 | 2) Populate 0 values for the taxable amounts based on the sign of the taxable |
2018 | accounted amounts |
2019 +--------------------------------------------------------------------------------*/
2020 IF ((p_ae_line_rec.taxable_accounted_dr IS NOT NULL)
2021 AND (p_ae_line_rec.taxable_entered_dr IS NULL) AND (p_ae_line_rec.taxable_entered_cr IS NULL)) THEN
2022 p_ae_line_rec.taxable_entered_dr := 0;
2023 ELSIF ((p_ae_line_rec.taxable_accounted_cr IS NOT NULL)
2024 AND (p_ae_line_rec.taxable_entered_cr IS NULL) AND (p_ae_line_rec.taxable_entered_dr IS NULL)) THEN
2025 p_ae_line_rec.taxable_entered_cr := 0;
2026 END IF;
2027
2028 /*-----------------------------------------------------------------------------------+
2029 | 3) Populate 0 values for the taxable amounts based on the sign of the amounts |
2030 +-----------------------------------------------------------------------------------*/
2031 IF ((p_ae_line_rec.entered_dr IS NOT NULL)
2032 AND (p_ae_line_rec.taxable_entered_dr IS NULL) AND (p_ae_line_rec.taxable_entered_cr IS NULL)) THEN
2033 p_ae_line_rec.taxable_entered_dr := 0;
2034 ELSIF ((p_ae_line_rec.entered_cr IS NOT NULL)
2035 AND (p_ae_line_rec.taxable_entered_cr IS NULL) AND (p_ae_line_rec.taxable_entered_dr IS NULL)) THEN
2036 p_ae_line_rec.taxable_entered_cr := 0;
2037 END IF;
2038
2039 /*--------------------------------------------------------------------------------+
2040 | 3) Populate 0 values for the taxable accounted amounts based on the sign of the|
2041 | accounted amounts |
2042 +--------------------------------------------------------------------------------*/
2043 --Now for the accounted amounts
2044 IF ((p_ae_line_rec.accounted_dr IS NOT NULL)
2045 AND (p_ae_line_rec.taxable_accounted_dr IS NULL) AND (p_ae_line_rec.taxable_accounted_cr IS NULL)) THEN
2046 p_ae_line_rec.taxable_accounted_dr := 0;
2047 ELSIF ((p_ae_line_rec.accounted_cr IS NOT NULL)
2048 AND (p_ae_line_rec.taxable_accounted_cr IS NULL) AND (p_ae_line_rec.taxable_accounted_dr IS NULL)) THEN
2049 p_ae_line_rec.taxable_accounted_cr := 0;
2050 END IF;
2051
2052 /*--------------------------------------------------------------------------------+
2053 | 4) Populate 0 values for the amounts based on the sign of the taxable accounted|
2054 | amounts |
2055 +--------------------------------------------------------------------------------*/
2056 IF ((p_ae_line_rec.taxable_entered_dr IS NOT NULL)
2057 AND (p_ae_line_rec.entered_dr IS NULL) AND (p_ae_line_rec.entered_cr IS NULL)) THEN
2058 p_ae_line_rec.entered_dr := 0;
2059 ELSIF ((p_ae_line_rec.taxable_entered_cr IS NOT NULL)
2060 AND (p_ae_line_rec.entered_cr IS NULL) AND (p_ae_line_rec.entered_dr IS NULL)) THEN
2061 p_ae_line_rec.entered_cr := 0;
2062 END IF;
2063
2064 /*--------------------------------------------------------------------------------+
2065 | 4) Populate 0 values for the accounted amounts based on the sign of the taxable|
2066 | accounted amounts |
2067 +--------------------------------------------------------------------------------*/
2068 IF ((p_ae_line_rec.taxable_accounted_dr IS NOT NULL)
2069 AND (p_ae_line_rec.accounted_dr IS NULL) AND (p_ae_line_rec.accounted_cr IS NULL)) THEN
2070 p_ae_line_rec.accounted_dr := 0;
2071 ELSIF ((p_ae_line_rec.taxable_accounted_cr IS NOT NULL)
2072 AND (p_ae_line_rec.accounted_cr IS NULL) AND (p_ae_line_rec.accounted_dr IS NULL)) THEN
2073 p_ae_line_rec.accounted_cr := 0;
2074 END IF;
2075
2076 /*------------------------------------------------------+
2077 | Store AE Line elements in Global AE Lines table |
2078 +------------------------------------------------------*/
2079 p_g_ae_ctr := p_g_ae_ctr +1;
2080
2081 p_g_ae_line_tbl(p_g_ae_ctr).ae_line_type := p_ae_line_rec.ae_line_type;
2082 p_g_ae_line_tbl(p_g_ae_ctr).ae_line_type_secondary := p_ae_line_rec.ae_line_type_secondary;
2083 p_g_ae_line_tbl(p_g_ae_ctr).source_id := p_ae_line_rec.source_id;
2084 p_g_ae_line_tbl(p_g_ae_ctr).source_table := p_ae_line_rec.source_table;
2085 p_g_ae_line_tbl(p_g_ae_ctr).account := p_ae_line_rec.account;
2086 p_g_ae_line_tbl(p_g_ae_ctr).entered_dr := p_ae_line_rec.entered_dr;
2087 p_g_ae_line_tbl(p_g_ae_ctr).entered_cr := p_ae_line_rec.entered_cr;
2088 p_g_ae_line_tbl(p_g_ae_ctr).accounted_dr := p_ae_line_rec.accounted_dr;
2089 p_g_ae_line_tbl(p_g_ae_ctr).accounted_cr := p_ae_line_rec.accounted_cr;
2090 p_g_ae_line_tbl(p_g_ae_ctr).source_id_secondary := p_ae_line_rec.source_id_secondary;
2091 p_g_ae_line_tbl(p_g_ae_ctr).source_table_secondary := p_ae_line_rec.source_table_secondary;
2092 p_g_ae_line_tbl(p_g_ae_ctr).currency_code := p_ae_line_rec.currency_code;
2093 p_g_ae_line_tbl(p_g_ae_ctr).currency_conversion_rate := p_ae_line_rec.currency_conversion_rate;
2094 p_g_ae_line_tbl(p_g_ae_ctr).currency_conversion_type := p_ae_line_rec.currency_conversion_type;
2095 p_g_ae_line_tbl(p_g_ae_ctr).currency_conversion_date := p_ae_line_rec.currency_conversion_date;
2096 p_g_ae_line_tbl(p_g_ae_ctr).third_party_id := p_ae_line_rec.third_party_id;
2097 p_g_ae_line_tbl(p_g_ae_ctr).third_party_sub_id := p_ae_line_rec.third_party_sub_id;
2098 p_g_ae_line_tbl(p_g_ae_ctr).tax_group_code_id := p_ae_line_rec.tax_group_code_id;
2099 p_g_ae_line_tbl(p_g_ae_ctr).tax_code_id := p_ae_line_rec.tax_code_id;
2100 p_g_ae_line_tbl(p_g_ae_ctr).location_segment_id := p_ae_line_rec.location_segment_id;
2101 p_g_ae_line_tbl(p_g_ae_ctr).taxable_entered_dr := p_ae_line_rec.taxable_entered_dr;
2102 p_g_ae_line_tbl(p_g_ae_ctr).taxable_entered_cr := p_ae_line_rec.taxable_entered_cr;
2103 p_g_ae_line_tbl(p_g_ae_ctr).taxable_accounted_dr := p_ae_line_rec.taxable_accounted_dr;
2104 p_g_ae_line_tbl(p_g_ae_ctr).taxable_accounted_cr := p_ae_line_rec.taxable_accounted_cr;
2105 p_g_ae_line_tbl(p_g_ae_ctr).applied_from_doc_table := p_ae_line_rec.applied_from_doc_table;
2106 p_g_ae_line_tbl(p_g_ae_ctr).applied_from_doc_id := p_ae_line_rec.applied_from_doc_id;
2107 p_g_ae_line_tbl(p_g_ae_ctr).applied_to_doc_table := p_ae_line_rec.applied_to_doc_table;
2108 p_g_ae_line_tbl(p_g_ae_ctr).applied_to_doc_id := p_ae_line_rec.applied_to_doc_id;
2109 p_g_ae_line_tbl(p_g_ae_ctr).tax_link_id := p_ae_line_rec.tax_link_id;
2110 p_g_ae_line_tbl(p_g_ae_ctr).reversed_source_id := p_ae_line_rec.reversed_source_id;
2111 p_g_ae_line_tbl(p_g_ae_ctr).summarize_flag := 'N';
2112
2113 Dump_Line_Amts(p_ae_line_rec);
2114
2115 <<end_assign_elements>>
2116 IF PG_DEBUG in ('Y', 'C') THEN
2117 arp_standard.debug( 'ARP_RECONCILE.Assign_Elements()-');
2118 END IF;
2119
2120 EXCEPTION
2121 WHEN OTHERS THEN
2122 IF PG_DEBUG in ('Y', 'C') THEN
2123 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Assign_Elements');
2124 END IF;
2125 RAISE;
2126
2127 END Assign_Elements;
2128
2129 /* ==========================================================================
2130 | PROCEDURE Dump_Line_Amts
2131 |
2132 | DESCRIPTION
2133 | Dumps data accounting line data
2134 |
2135 | SCOPE - PRIVATE
2136 |
2137 | PARAMETERS
2138 | p_ae_line_rec IN Accounting lines record
2139 *==========================================================================*/
2140 PROCEDURE Dump_Line_Amts(p_ae_line_rec IN ae_line_rec_type) IS
2141
2142 BEGIN
2143
2144 IF PG_DEBUG in ('Y', 'C') THEN
2145 arp_standard.debug('ARP_RECONCILE.Dump_Line_Amts()+');
2146 arp_standard.debug('Dump_Line_Amts: ' || 'ae_line_type = ' || p_ae_line_rec.ae_line_type);
2147 arp_standard.debug('Dump_Line_Amts: ' || 'ae_line_type_secondary = ' || p_ae_line_rec.ae_line_type_secondary);
2148 arp_standard.debug('Dump_Line_Amts: ' || 'source_id = ' || p_ae_line_rec.source_id);
2149 arp_standard.debug('Dump_Line_Amts: ' || 'source_table = ' || p_ae_line_rec.source_table);
2150 arp_standard.debug('Dump_Line_Amts: ' || 'account = ' || p_ae_line_rec.account);
2151 arp_standard.debug('Dump_Line_Amts: ' || 'entered_dr = ' || p_ae_line_rec.entered_dr);
2152 arp_standard.debug('Dump_Line_Amts: ' || 'entered_cr = ' || p_ae_line_rec.entered_cr);
2153 arp_standard.debug('Dump_Line_Amts: ' || 'accounted_dr = ' || p_ae_line_rec.accounted_dr);
2154 arp_standard.debug('Dump_Line_Amts: ' || 'accounted_cr = ' || p_ae_line_rec.accounted_cr);
2155 arp_standard.debug('Dump_Line_Amts: ' || 'source_id_secondary = ' || p_ae_line_rec.source_id_secondary);
2156 arp_standard.debug('Dump_Line_Amts: ' || 'source_table_secondary = ' || p_ae_line_rec.source_table_secondary);
2157 arp_standard.debug('Dump_Line_Amts: ' || 'currency_code = ' || p_ae_line_rec.currency_code);
2158 arp_standard.debug('Dump_Line_Amts: ' || 'currency_conversion_rate = ' || p_ae_line_rec.currency_conversion_rate);
2159 arp_standard.debug('Dump_Line_Amts: ' || 'currency_conversion_type = ' || p_ae_line_rec.currency_conversion_type);
2160 arp_standard.debug('Dump_Line_Amts: ' || 'currency_conversion_date = ' || p_ae_line_rec.currency_conversion_date);
2161 arp_standard.debug('Dump_Line_Amts: ' || 'third_party_id = ' || p_ae_line_rec.third_party_id);
2162 arp_standard.debug('Dump_Line_Amts: ' || 'third_party_sub_id = ' || p_ae_line_rec.third_party_sub_id);
2163 arp_standard.debug('Dump_Line_Amts: ' || 'tax_group_code_id = ' || p_ae_line_rec.tax_group_code_id);
2164 arp_standard.debug('Dump_Line_Amts: ' || 'tax_code_id = ' || p_ae_line_rec.tax_code_id);
2165 arp_standard.debug('Dump_Line_Amts: ' || 'location_segment_id = ' || p_ae_line_rec.location_segment_id);
2166 arp_standard.debug('Dump_Line_Amts: ' || 'taxable_entered_dr = ' || p_ae_line_rec.taxable_entered_dr);
2167 arp_standard.debug('Dump_Line_Amts: ' || 'taxable_entered_cr = ' || p_ae_line_rec.taxable_entered_cr);
2168 arp_standard.debug('Dump_Line_Amts: ' || 'taxable_accounted_dr = ' || p_ae_line_rec.taxable_accounted_dr);
2169 arp_standard.debug('Dump_Line_Amts: ' || 'taxable_accounted_cr = ' || p_ae_line_rec.taxable_accounted_cr);
2170 arp_standard.debug('Dump_Line_Amts: ' || 'applied_from_doc_table = ' || p_ae_line_rec.applied_from_doc_table);
2171 arp_standard.debug('Dump_Line_Amts: ' || 'applied_from_doc_id = ' || p_ae_line_rec.applied_from_doc_id);
2172 arp_standard.debug('Dump_Line_Amts: ' || 'applied_to_doc_table = ' || p_ae_line_rec.applied_to_doc_table);
2173 arp_standard.debug('Dump_Line_Amts: ' || 'applied_to_doc_id = ' || p_ae_line_rec.applied_to_doc_id);
2174 arp_standard.debug('Dump_Line_Amts: ' || 'tax_link_id = ' || p_ae_line_rec.tax_link_id);
2175 arp_standard.debug('Dump_Line_Amts: ' || 'reversed_source_id = ' || p_ae_line_rec.reversed_source_id);
2176 arp_standard.debug('Dump_Line_Amts: ' || 'summarize_flag = ' || p_ae_line_rec.summarize_flag);
2177 arp_standard.debug('ARP_RECONCILE.Dump_Line_Amts()-');
2178 END IF;
2179
2180 EXCEPTION
2181 WHEN OTHERS THEN
2182 IF PG_DEBUG in ('Y', 'C') THEN
2183 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Dump_Line_Amts');
2184 END IF;
2185 RAISE;
2186
2187 END Dump_Line_Amts;
2188
2189 /*========================================================================
2190 | PRIVATE PROCEDURE Process_Recon
2191 |
2192 | DESCRIPTION
2193 | Actually reconciles each assignment of a Bill. Reconciliation is done
2194 | only if the Bill is closed and all chained Bills are also closed.
2195 | If an assignment is a Bill then this function is called recursively
2196 | to go to the child bill and start processing with the same condition
2197 | checks as was done for the parent bill.
2198 |
2199 | PARAMETERS
2200 | p_mode IN Document or Accounting Event mode
2201 | p_ae_doc_rec IN Document Record
2202 | p_ae_event_rec IN Event Record
2203 | p_ae_sys_rec IN System parameter details
2204 | p_cust_inv_rec IN Contains currency, exchange rate, site
2205 | details for the bill
2206 | p_br_cust_trx_line_id IN Bills Receivable assignment line id
2207 | p_customer_trx_id IN Transaction Id
2208 | p_simul_app IN Indicates that for a Bill shadow
2209 | adjustment
2210 | or assignment simulate a payment event
2211 | p_pay_ctr IN Application for assignment table counter
2212 | p_pay_tbl IN Application details for assignment table
2213 | p_g_ae_ctr IN OUT NOCOPY Global accounting entry table counter
2214 | p_g_ae_line_tbl IN OUT NOCOPY Global accounting entry lines table
2215 | containing accounting due to previous
2216 | activity on Bills
2217 | Transaction, or Bills (assignment)
2218 *=======================================================================*/
2219 PROCEDURE Process_Recon(
2220 p_mode IN VARCHAR2 ,
2221 p_ae_doc_rec IN ae_doc_rec_type ,
2222 p_ae_event_rec IN ae_event_rec_type ,
2223 p_ae_sys_rec IN ae_sys_rec_type ,
2224 p_cust_inv_rec IN ra_customer_trx%ROWTYPE ,
2225 p_br_cust_trx_line_id IN NUMBER ,
2226 p_customer_trx_id IN NUMBER ,
2227 p_simul_app IN VARCHAR2 ,
2228 p_calling_point IN VARCHAR2 ,
2229 p_pay_ctr IN BINARY_INTEGER ,
2230 p_pay_tbl IN g_pay_tbl_type ,
2231 p_g_ae_ctr IN OUT NOCOPY BINARY_INTEGER ,
2232 p_g_ae_line_tbl IN OUT NOCOPY ae_line_tbl_type ) IS
2233
2234 /*========================================================================+
2235 | Gets the initial deferred tax accounting for regular transactions |
2236 +------------------------------------------------------------------------*/
2237
2238 CURSOR get_init_def_tax_acct IS
2239 SELECT ctl.location_segment_id location_segment_id ,
2240 decode(ctl.autotax,
2241 'N','',
2242 decode(ctl.location_segment_id,
2243 '', decode(ctl.vat_tax_id,
2244 '','',
2245 ctl1.vat_tax_id, '',
2246 ctl1.vat_tax_id),
2247 '')) tax_group_code_id,
2248 ctl.vat_tax_id tax_code_id,
2249 gld.code_combination_id account,
2250 sum(nvl(gld.amount,0)) amount,
2251 sum(nvl(gld.acctd_amount,0)) acctd_amount,
2252 max(nvl(ctl.taxable_amount,0)) taxable_amount,
2253 max(decode(gld.account_class,
2254 'TAX',
2255 arpcurr.functional_amount(
2256 nvl(ctl.taxable_amount,0),
2257 p_ae_sys_rec.base_currency ,
2258 p_cust_inv_rec.exchange_rate ,
2259 p_ae_sys_rec.base_precision ,
2260 p_ae_sys_rec.base_min_acc_unit),
2261 '')) taxable_acctd_amount
2262 FROM ra_customer_trx ct ,
2263 ra_cust_trx_line_gl_dist gld,
2264 ra_customer_trx_lines ctl,
2265 ra_customer_trx_lines ctl1
2266 where ct.customer_trx_id = p_customer_trx_id
2267 and p_calling_point IN ('TRAN', 'BLTR')
2268 and ct.customer_trx_id = gld.customer_trx_id
2269 and gld.customer_trx_id = ctl.customer_trx_id
2270 and gld.customer_trx_line_id = ctl.customer_trx_line_id
2271 and gld.account_class = 'TAX'
2272 and gld.collected_tax_ccid IS NOT NULL --deferred tax lines only
2273 and gld.account_set_flag = 'N'
2274 and ctl.link_to_cust_trx_line_id = ctl1.customer_trx_line_id --outer join not required here
2275 and not exists (select 'x'
2276 from ra_customer_trx_lines ctl2
2277 where ctl2.customer_trx_id = p_customer_trx_id
2278 and p_calling_point IN ('TRAN', 'BLTR')
2279 and ctl2.autorule_complete_flag = 'N')
2280 group by ctl.customer_trx_line_id ,
2281 ctl.location_segment_id ,
2282 decode(ctl.autotax,'N','',
2283 decode(ctl.location_segment_id,
2284 '', decode(ctl.vat_tax_id,
2285 '','',
2286 ctl1.vat_tax_id, '',
2287 ctl1.vat_tax_id),
2288 '')),
2289 ctl.vat_tax_id ,
2290 gld.code_combination_id
2291 order by 1,2,3;
2292
2293 /*-------------------------------------------------------------------------+
2294 | Gets the accounting for applications on transactions from the accounting|
2295 | table for reconciliation purposes. |
2296 +-------------------------------------------------------------------------*/
2297
2298 CURSOR get_def_tax_acct IS --get accounting for applications on transactions
2299 select ard.location_segment_id location_segment_id ,
2300 ard.tax_group_code_id tax_group_code_id ,
2301 ard.tax_code_id tax_code_id ,
2302 ard.code_combination_id account ,
2303 sum(nvl(ard.amount_dr,0) * -1 +
2304 nvl(ard.amount_cr,0)) amount ,
2305 sum(nvl(ard.acctd_amount_dr,0) * -1 +
2306 nvl(ard.acctd_amount_cr,0)) acctd_amount ,
2307 sum(nvl(ard.taxable_entered_dr,0) * -1 +
2308 nvl(ard.taxable_entered_cr,0)) taxable_amount ,
2309 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
2310 nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
2311 from ar_distributions ard,
2312 ar_receivable_applications app
2313 where p_ae_sys_rec.sob_type = 'P'
2314 and app.applied_customer_trx_id = p_customer_trx_id
2315 and p_calling_point IN ('TRAN', 'BLTR')
2316 and app.status = 'APP'
2317 and nvl(app.confirmed_flag, 'Y') = 'Y'
2318 and ard.source_id = app.receivable_application_id
2319 and ard.source_table = 'RA'
2320 and ard.source_type = 'DEFERRED_TAX'
2321 and decode(ard.source_type_secondary,
2322 'RECONCILE', ard.source_id_secondary,
2323 p_customer_trx_id) = p_customer_trx_id
2324 group by ard.location_segment_id ,
2325 ard.tax_group_code_id ,
2326 ard.tax_code_id ,
2327 ard.code_combination_id
2328 /*-------------------------------------------------------------------------+
2329 | Gets the accounting for adjustments on transactions from the accounting |
2330 | table for reconciliation purposes. |
2331 +-------------------------------------------------------------------------*/
2332 UNION ALL--get accounting for adjustments on transaction
2333 select ard.location_segment_id location_segment_id ,
2334 ard.tax_group_code_id tax_group_code_id ,
2335 ard.tax_code_id tax_code_id ,
2336 ard.code_combination_id account ,
2337 sum(nvl(ard.amount_dr,0) * -1 +
2338 nvl(ard.amount_cr,0)) amount ,
2339 sum(nvl(ard.acctd_amount_dr,0) * -1 +
2340 nvl(ard.acctd_amount_cr,0)) acctd_amount ,
2341 sum(nvl(ard.taxable_entered_dr,0) * -1 +
2342 nvl(ard.taxable_entered_cr,0)) taxable_amount ,
2343 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
2344 nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
2345 from ar_distributions ard,
2346 ar_adjustments adj
2347 where p_ae_sys_rec.sob_type = 'P'
2348 and adj.customer_trx_id = p_customer_trx_id
2349 and p_calling_point IN ('TRAN', 'BLTR')
2350 and adj.status = 'A'
2351 and ard.source_id = adj.adjustment_id
2352 and ard.source_table = 'ADJ'
2353 and ard.source_type = 'DEFERRED_TAX'
2354 and decode(ard.source_type_secondary,
2355 'RECONCILE', ard.source_id_secondary,
2356 p_customer_trx_id) = p_customer_trx_id
2357 group by ard.location_segment_id ,
2358 ard.tax_group_code_id ,
2359 ard.tax_code_id ,
2360 ard.code_combination_id
2361 /*--------------------------------------------------------------------------+
2362 | Gets the accounting for activity on a Bill to which the transactions has |
2363 | been assigned. i.e. deferred tax accounting for transaction assignments |
2364 | to the Bill. This is used to reconcile the transaction. p_customer_trx_id|
2365 | is null when processing assignments on a Bill. So the statement below is |
2366 | used for transactions only. |
2367 +--------------------------------------------------------------------------*/
2368 UNION ALL--get accounting on Bills for Transactions
2369 select ard.location_segment_id location_segment_id ,
2370 ard.tax_group_code_id tax_group_code_id ,
2371 ard.tax_code_id tax_code_id ,
2372 ard.code_combination_id account ,
2373 sum(nvl(ard.amount_dr,0) * -1 +
2374 nvl(ard.amount_cr,0)) amount ,
2375 sum(nvl(ard.acctd_amount_dr,0) * -1 +
2376 nvl(ard.acctd_amount_cr,0)) acctd_amount ,
2377 sum(nvl(ard.taxable_entered_dr,0) * -1 +
2378 nvl(ard.taxable_entered_cr,0)) taxable_amount ,
2379 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
2380 nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
2381 from ra_customer_trx_lines ctl,
2382 ar_distributions ard
2383 where p_ae_sys_rec.sob_type = 'P'
2384 and ctl.br_ref_customer_trx_id = p_customer_trx_id
2385 and p_calling_point IN ('TRAN', 'BLTR')
2386 and ard.source_id_secondary = ctl.customer_trx_line_id
2387 and ard.source_table_secondary = 'CTL'
2388 and ard.source_type_secondary IN ('ASSIGNMENT', 'ASSIGNMENT_RECONCILE',
2389 'RECONCILE')
2390 and ard.source_type = 'DEFERRED_TAX'
2391 group by ard.location_segment_id ,
2392 ard.tax_group_code_id ,
2393 ard.tax_code_id ,
2394 ard.code_combination_id
2395 /*--------------------------------------------------------------------------+
2396 | Get the deferred tax accounting moved for the assignment on the Bill due |
2397 | to activity on the Bill from the accounting table. The assignment line id|
2398 | is used by the statement below. |
2399 +--------------------------------------------------------------------------*/
2400 UNION ALL--reconcile bill only
2401 select ard.location_segment_id location_segment_id ,
2402 ard.tax_group_code_id tax_group_code_id ,
2403 ard.tax_code_id tax_code_id ,
2404 ard.code_combination_id account ,
2405 sum(nvl(ard.amount_dr,0) * -1 +
2406 nvl(ard.amount_cr,0)) amount ,
2407 sum(nvl(ard.acctd_amount_dr,0) * -1 +
2408 nvl(ard.acctd_amount_cr,0)) acctd_amount ,
2409 sum(nvl(ard.taxable_entered_dr,0) * -1 +
2410 nvl(ard.taxable_entered_cr,0)) taxable_amount ,
2411 sum(nvl(ard.taxable_accounted_dr,0) * -1 +
2412 nvl(ard.taxable_accounted_cr,0)) taxable_acctd_amount
2413 from ar_distributions ard
2414 where p_ae_sys_rec.sob_type = 'P'
2415 and ard.source_id_secondary = p_br_cust_trx_line_id
2416 and p_calling_point = 'BILL'
2417 and ard.source_table_secondary = 'CTL'
2418 and ard.source_type_secondary IN ('ASSIGNMENT', 'ASSIGNMENT_RECONCILE')
2419 and ard.source_type = 'DEFERRED_TAX'
2420 group by ard.location_segment_id ,
2421 ard.tax_group_code_id ,
2422 ard.tax_code_id ,
2423 ard.code_combination_id
2424 order by 1,2,3;
2425
2426
2427 ae_tax_tbl g_tax_tbl_type;
2428 ae_tax_activity_tbl g_tax_tbl_type;
2429
2430 l_ae_line_tbl ae_line_tbl_type;
2431 l_ae_line_rec ae_line_rec_type;
2432 l_ae_empty_line_rec ae_line_rec_type;
2433
2434 l_ae_rule_rec ae_rule_rec_type;
2435
2436 l_app_rec ar_receivable_applications%ROWTYPE;
2437 l_adj_rec ar_adjustments%ROWTYPE;
2438
2439 l_tax_ctr NUMBER := 0;
2440 l_tax_ctr1 NUMBER := 0;
2441 l_ctr NUMBER;
2442 l_ctr1 NUMBER;
2443 l_ctr2 NUMBER;
2444 l_ae_ctr NUMBER;
2445 l_cached BOOLEAN;
2446 l_cre_rec BOOLEAN;
2447 l_match_cond BOOLEAN;
2448 l_ae_doc_rec ae_doc_rec_type;
2449
2450 BEGIN
2451
2452 IF PG_DEBUG in ('Y', 'C') THEN
2453 arp_standard.debug('ARP_RECONCILE.Process_Recon()+');
2454 arp_standard.debug('Process_Recon: ' || 'list Input of parameters ');
2455 arp_standard.debug('Process_Recon: ' || 'p_br_cust_trx_line_id ' || p_br_cust_trx_line_id);
2456 arp_standard.debug('Process_Recon: ' || 'p_customer_trx_id ' || p_customer_trx_id);
2457 arp_standard.debug('Process_Recon: ' || 'p_simul_app ' || p_simul_app);
2458 arp_standard.debug('Process_Recon: ' || 'p_calling_point ' || p_calling_point);
2459 arp_standard.debug('Process_Recon: ' || 'p_pay_ctr ' || p_pay_ctr);
2460 END IF;
2461
2462 /*-------------------------------------------------------------------------------+
2463 | For an assignment on a Bill simulate an activity such as an application which |
2464 | results in closing the amount assigned to the Bill due to the line assignment.|
2465 +-------------------------------------------------------------------------------*/
2466 IF (p_simul_app = 'Y') THEN
2467
2468 l_ae_line_tbl := g_ae_empty_line_tbl;
2469 l_ae_ctr := 0;
2470
2471 l_ae_doc_rec := p_ae_doc_rec;
2472 l_ae_doc_rec.source_table := 'RA';
2473
2474 IF p_pay_tbl.EXISTS(p_pay_ctr) THEN --atleast one activity exists
2475
2476 IF PG_DEBUG in ('Y', 'C') THEN
2477 arp_standard.debug('Process_Recon: ' || 'p_pay_tbl simulate application ');
2478 END IF;
2479
2480 FOR l_ctr3 IN p_pay_tbl.FIRST .. p_pay_tbl.LAST LOOP
2481
2482 l_app_rec.applied_customer_trx_id := p_pay_tbl(l_ctr3).applied_customer_trx_id ;
2483 l_app_rec.applied_payment_schedule_id := p_pay_tbl(l_ctr3).applied_payment_schedule_id ;
2484 l_app_rec.amount_applied := p_pay_tbl(l_ctr3).amount_applied ;
2485 l_app_rec.acctd_amount_applied_to := p_pay_tbl(l_ctr3).acctd_amount_applied_to ;
2486 l_app_rec.line_applied := p_pay_tbl(l_ctr3).line_applied ;
2487 l_app_rec.tax_applied := p_pay_tbl(l_ctr3).tax_applied ;
2488 l_app_rec.freight_applied := p_pay_tbl(l_ctr3).freight_applied ;
2489 l_app_rec.receivables_charges_applied := p_pay_tbl(l_ctr3).receivables_charges_applied ;
2490
2491 /*-----------------------------------------------------------------------------+
2492 | Call Tax accounting engine to allocate deferred tax for the simulated single|
2493 | activity on the assignment. |
2494 +-----------------------------------------------------------------------------*/
2495 ARP_ALLOCATION_PKG.Allocate_Tax(
2496 p_ae_doc_rec => l_ae_doc_rec , --Document detail
2497 p_ae_event_rec => p_ae_event_rec , --Event record
2498 p_ae_rule_rec => l_ae_rule_rec , --Rule info for payment method
2499 p_app_rec => l_app_rec , --Application details
2500 p_cust_inv_rec => p_cust_inv_rec , --Invoice details
2501 p_adj_rec => l_adj_rec , --dummy adjustment record
2502 p_ae_ctr => l_ae_ctr , --counter
2503 p_ae_line_tbl => l_ae_line_tbl , --final tax accounting table
2504 p_br_cust_trx_line_id => '' ,
2505 p_simul_app => p_simul_app );
2506
2507 IF l_ae_line_tbl.EXISTS(l_ae_ctr) THEN --Atleast one Tax line exists
2508
2509 IF PG_DEBUG in ('Y', 'C') THEN
2510 arp_standard.debug('Process_Recon: ' || 'Caching Tax for simulated application ');
2511 END IF;
2512
2513 FOR l_ctr1 IN l_ae_line_tbl.FIRST .. l_ae_line_tbl.LAST LOOP
2514
2515 /*--------------------------------------------------------------------------------+
2516 |Cache the deferred tax accounting into the tax table.This is the deferred tax |
2517 |created as though the amount on the shadow assignment of the transaction on |
2518 |the bill were paid off through a single activity. Note in this case we |
2519 |multiply the credits by -1 because we want to use the net amount by location |
2520 |or tax code for the simulated application accounting, and add it to the actual |
2521 |accounting for the Bills assignment. This will result in creating the offsetting|
2522 |reconciliation entries. |
2523 +--------------------------------------------------------------------------------*/
2524 l_cached := FALSE;
2525
2526 IF ae_tax_tbl.EXISTS(l_tax_ctr) THEN
2527
2528 FOR l_ctr IN ae_tax_tbl.FIRST .. ae_tax_tbl.LAST LOOP
2529
2530 IF ((((l_ae_line_tbl(l_ctr1).location_segment_id IS NOT NULL)
2531 AND (nvl(ae_tax_tbl(l_ctr).ae_location_segment_id,-999) = nvl(l_ae_line_tbl(l_ctr1).location_segment_id,-999)))
2532 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))
2533 AND (l_ae_line_tbl(l_ctr1).tax_code_id IS NOT NULL)
2534 AND (nvl(ae_tax_tbl(l_ctr).ae_tax_code_id,-999) = nvl(l_ae_line_tbl(l_ctr1).tax_code_id,-999))))
2535 AND (ae_tax_tbl(l_ctr).ae_code_combination_id = l_ae_line_tbl(l_ctr1).account)
2536 AND (l_ae_line_tbl(l_ctr1).ae_line_type = 'DEFERRED_TAX'))
2537 THEN
2538
2539 IF PG_DEBUG in ('Y', 'C') THEN
2540 arp_standard.debug('Process_Recon: ' || '1) Hit found in cache ae_tax_tbl');
2541 END IF;
2542
2543 ae_tax_tbl(l_ctr).ae_amount := ae_tax_tbl(l_ctr).ae_amount
2544 + nvl(l_ae_line_tbl(l_ctr1).entered_dr,0)
2545 + nvl(l_ae_line_tbl(l_ctr1).entered_cr,0) * -1;
2546
2547 ae_tax_tbl(l_ctr).ae_acctd_amount := ae_tax_tbl(l_ctr).ae_acctd_amount
2548 + nvl(l_ae_line_tbl(l_ctr1).accounted_dr,0)
2549 + nvl(l_ae_line_tbl(l_ctr1).accounted_cr,0) * -1; --bug6146807
2550
2551 ae_tax_tbl(l_ctr).ae_taxable_amount := ae_tax_tbl(l_ctr).ae_taxable_amount
2552 + nvl(l_ae_line_tbl(l_ctr1).taxable_entered_dr,0)
2553 + nvl(l_ae_line_tbl(l_ctr1).taxable_entered_cr,0) * -1;
2554
2555 ae_tax_tbl(l_ctr).ae_taxable_acctd_amount := ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
2556 + nvl(l_ae_line_tbl(l_ctr1).taxable_accounted_dr,0)
2557 + nvl(l_ae_line_tbl(l_ctr1).taxable_accounted_cr,0) * -1;
2558
2559 l_cached := TRUE;
2560
2561 END IF; --grouping rule satisfied
2562
2563 END LOOP; --ae_tax_tbl to verify whether tax record is cached
2564
2565 END IF; --ae_tax_tbl exists
2566
2567 /*-----------------------------------------------------------------------------+
2568 |Cache the deferred tax accounting entry into the table if not already cached |
2569 +-----------------------------------------------------------------------------*/
2570 IF (NOT l_cached) AND (l_ae_line_tbl(l_ctr1).ae_line_type = 'DEFERRED_TAX')
2571 THEN
2572 IF PG_DEBUG in ('Y', 'C') THEN
2573 arp_standard.debug('Process_Recon: ' || '1) Now caching in cache ae_tax_tbl');
2574 END IF;
2575
2576 l_tax_ctr := l_tax_ctr + 1;
2577
2578 ae_tax_tbl(l_tax_ctr).ae_location_segment_id := l_ae_line_tbl(l_ctr1).location_segment_id;
2579
2580 ae_tax_tbl(l_tax_ctr).ae_tax_group_code_id := l_ae_line_tbl(l_ctr1).tax_group_code_id;
2581
2582 ae_tax_tbl(l_tax_ctr).ae_tax_code_id := l_ae_line_tbl(l_ctr1).tax_code_id;
2583
2584 ae_tax_tbl(l_tax_ctr).ae_code_combination_id := l_ae_line_tbl(l_ctr1).account;
2585
2586 ae_tax_tbl(l_tax_ctr).ae_amount :=
2587 nvl(l_ae_line_tbl(l_ctr1).entered_dr,0)
2588 + nvl(l_ae_line_tbl(l_ctr1).entered_cr,0) * -1;
2589
2590 ae_tax_tbl(l_tax_ctr).ae_acctd_amount :=
2591 nvl(l_ae_line_tbl(l_ctr1).accounted_dr,0)
2592 + nvl(l_ae_line_tbl(l_ctr1).accounted_cr,0) * -1;
2593
2594 ae_tax_tbl(l_tax_ctr).ae_taxable_amount :=
2595 nvl(l_ae_line_tbl(l_ctr1).taxable_entered_dr,0)
2596 + nvl(l_ae_line_tbl(l_ctr1).taxable_entered_cr,0) * -1;
2597
2598 ae_tax_tbl(l_tax_ctr).ae_taxable_acctd_amount :=
2599 nvl(l_ae_line_tbl(l_ctr1).taxable_accounted_dr,0)
2600 + nvl(l_ae_line_tbl(l_ctr1).taxable_accounted_cr,0) * -1;
2601
2602 l_cached := TRUE;
2603
2604 IF PG_DEBUG in ('Y', 'C') THEN
2605 arp_standard.debug('Process_Recon: ' || ' ');
2606 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);
2607 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);
2608 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);
2609 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);
2610 arp_standard.debug('Process_Recon: ' || ' ');
2611 END IF;
2612
2613 END IF; --not cached
2614
2615 END LOOP; -- lines table
2616
2617 END IF; --atleast one tax line exists
2618
2619 END LOOP; --process the payment table for all simulated applications
2620
2621 END IF; --payment table exists
2622
2623 ELSE
2624 /*---------------------------------------------------------------------------------+
2625 |Cache the deferred tax from the original transaction accounting table for use in |
2626 |the reconciliation process |
2627 +---------------------------------------------------------------------------------*/
2628 IF PG_DEBUG in ('Y', 'C') THEN
2629 arp_standard.debug('Process_Recon: ' || 'Caching deferred tax from Original Transaction accounting ');
2630 END IF;
2631
2632 FOR l_init_def_tax IN get_init_def_tax_acct LOOP
2633
2634 IF PG_DEBUG in ('Y', 'C') THEN
2635 arp_standard.debug('Process_Recon: ' || 'Processing Original Transaction accounting ');
2636 END IF;
2637
2638 l_cached := FALSE;
2639
2640 IF ae_tax_tbl.EXISTS(l_tax_ctr) THEN --Atleast one cached deferred Tax line exists
2641
2642 FOR l_ctr IN ae_tax_tbl.FIRST .. ae_tax_tbl.LAST LOOP
2643
2644 IF ((((l_init_def_tax.location_segment_id IS NOT NULL)
2645 AND (nvl(ae_tax_tbl(l_ctr).ae_location_segment_id,-999) = nvl(l_init_def_tax.location_segment_id,-999)))
2646 OR ((l_init_def_tax.tax_code_id IS NOT NULL)
2647 AND (nvl(ae_tax_tbl(l_ctr).ae_tax_code_id,-999) = nvl(l_init_def_tax.tax_code_id,-999))
2648 AND (nvl(ae_tax_tbl(l_ctr).ae_tax_group_code_id,-999) = nvl(l_init_def_tax.tax_group_code_id,-999))))
2649 AND (ae_tax_tbl(l_ctr).ae_code_combination_id = l_init_def_tax.account))
2650 THEN
2651
2652 IF PG_DEBUG in ('Y', 'C') THEN
2653 arp_standard.debug('Process_Recon: ' || '2) Hit found in cache ae_tax_tbl');
2654 END IF;
2655
2656 ae_tax_tbl(l_ctr).ae_amount := ae_tax_tbl(l_ctr).ae_amount
2657 + l_init_def_tax.amount;
2658
2659 ae_tax_tbl(l_ctr).ae_acctd_amount := ae_tax_tbl(l_ctr).ae_acctd_amount
2660 + l_init_def_tax.acctd_amount;
2661
2662 ae_tax_tbl(l_ctr).ae_taxable_amount := ae_tax_tbl(l_ctr).ae_taxable_amount
2663 + l_init_def_tax.taxable_amount;
2664
2665 ae_tax_tbl(l_ctr).ae_taxable_acctd_amount := ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
2666 + l_init_def_tax.taxable_acctd_amount;
2667
2668 l_cached := TRUE;
2669
2670 END IF; --grouping rule satisfied
2671
2672 END LOOP; --ae_tax_tbl to verify whether tax record is cached
2673
2674 END IF; --activity table exists for already cached entries
2675
2676 /*---------------------------------------------------------------------------------+
2677 |If an entry is not already cached then cache the Original accounting |
2678 +---------------------------------------------------------------------------------*/
2679 IF (NOT l_cached) THEN
2680
2681 IF PG_DEBUG in ('Y', 'C') THEN
2682 arp_standard.debug('Process_Recon: ' || '2) Now caching in cache ae_tax_tbl');
2683 END IF;
2684
2685 l_tax_ctr := l_tax_ctr + 1;
2686
2687 ae_tax_tbl(l_tax_ctr).ae_location_segment_id := l_init_def_tax.location_segment_id;
2688
2689 ae_tax_tbl(l_tax_ctr).ae_tax_group_code_id := l_init_def_tax.tax_group_code_id;
2690
2691 ae_tax_tbl(l_tax_ctr).ae_tax_code_id := l_init_def_tax.tax_code_id;
2692
2693 ae_tax_tbl(l_tax_ctr).ae_code_combination_id := l_init_def_tax.account;
2694
2695 ae_tax_tbl(l_tax_ctr).ae_amount := l_init_def_tax.amount;
2696
2697 ae_tax_tbl(l_tax_ctr).ae_acctd_amount := l_init_def_tax.acctd_amount;
2698
2699 ae_tax_tbl(l_tax_ctr).ae_taxable_amount := l_init_def_tax.taxable_amount;
2700
2701 ae_tax_tbl(l_tax_ctr).ae_taxable_acctd_amount := l_init_def_tax.taxable_acctd_amount;
2702
2703 IF PG_DEBUG in ('Y', 'C') THEN
2704 arp_standard.debug('Process_Recon: ' || ' ');
2705 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);
2706 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);
2707 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);
2708 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);
2709 arp_standard.debug('Process_Recon: ' || ' ');
2710 END IF;
2711
2712 l_cached := TRUE;
2713
2714 END IF; --not cached then cache
2715
2716 END LOOP; --process original tax on Invoice and cache
2717
2718 END IF; --Simulating an application to reconcile against single activity
2719
2720 IF PG_DEBUG in ('Y', 'C') THEN
2721 arp_standard.debug('Process_Recon: ' || 'Start caching physically created tax accounting entries due to past activity');
2722 END IF;
2723
2724 /*---------------------------------------------------------------------------------+
2725 |Cache the deferred tax accounting entries physically created in ar_distributions |
2726 |due to activity on the bill. |
2727 +---------------------------------------------------------------------------------*/
2728 FOR l_inv_nr IN get_def_tax_acct LOOP
2729
2730 l_cached := FALSE;
2731
2732 IF ae_tax_activity_tbl.EXISTS(l_tax_ctr1) THEN
2733
2734 FOR l_ctr IN ae_tax_activity_tbl.FIRST .. ae_tax_activity_tbl.LAST LOOP
2735
2736 /*--------------------------------------------------------------------+
2737 |Add to accounting entry in cache if matching conditions |
2738 +--------------------------------------------------------------------*/
2739 IF ((((l_inv_nr.location_segment_id IS NOT NULL)
2740 AND (nvl(ae_tax_activity_tbl(l_ctr).ae_location_segment_id,-999) = nvl(l_inv_nr.location_segment_id,-999)))
2741 OR ((nvl(ae_tax_activity_tbl(l_ctr).ae_tax_group_code_id,-999) = nvl(l_inv_nr.tax_group_code_id,-999))
2742 AND (l_inv_nr.tax_code_id IS NOT NULL)
2743 AND (nvl(ae_tax_activity_tbl(l_ctr).ae_tax_code_id,-999) = nvl(l_inv_nr.tax_code_id,-999))))
2744 AND (ae_tax_activity_tbl(l_ctr).ae_code_combination_id = l_inv_nr.account))
2745 THEN
2746
2747 IF PG_DEBUG in ('Y', 'C') THEN
2748 arp_standard.debug('Process_Recon: ' || '3) Hit found in cache ae_tax_activity_tbl');
2749 END IF;
2750
2751 ae_tax_activity_tbl(l_ctr).ae_amount :=
2752 ae_tax_activity_tbl(l_ctr).ae_amount + l_inv_nr.amount;
2753
2754 ae_tax_activity_tbl(l_ctr).ae_acctd_amount :=
2755 ae_tax_activity_tbl(l_ctr).ae_acctd_amount + l_inv_nr.acctd_amount;
2756
2757 ae_tax_activity_tbl(l_ctr).ae_taxable_amount :=
2758 ae_tax_activity_tbl(l_ctr).ae_taxable_amount + l_inv_nr.taxable_amount;
2759
2760 ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount :=
2761 ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount +l_inv_nr.taxable_acctd_amount;
2762
2763 l_cached := TRUE;
2764
2765 END IF;
2766
2767 END LOOP; --for activity table from ar_distributions
2768
2769 END IF; --activity table exists for already cached entries
2770
2771 /*---------------------------------------------------------------------------------+
2772 |If an entry is not already cached when retrieved from ar_distributions then cache|
2773 +---------------------------------------------------------------------------------*/
2774 IF (NOT l_cached) THEN
2775
2776 IF PG_DEBUG in ('Y', 'C') THEN
2777 arp_standard.debug('Process_Recon: ' || '3) Now caching in cache ae_tax_activity_tbl');
2778 END IF;
2779
2780 l_tax_ctr1 := l_tax_ctr1 + 1;
2781 ae_tax_activity_tbl(l_tax_ctr1).ae_location_segment_id := l_inv_nr.location_segment_id;
2782
2783 ae_tax_activity_tbl(l_tax_ctr1).ae_tax_group_code_id := l_inv_nr.tax_group_code_id;
2784
2785 ae_tax_activity_tbl(l_tax_ctr1).ae_tax_code_id := l_inv_nr.tax_code_id;
2786
2787 ae_tax_activity_tbl(l_tax_ctr1).ae_code_combination_id := l_inv_nr.account;
2788
2789 ae_tax_activity_tbl(l_tax_ctr1).ae_amount := l_inv_nr.amount;
2790
2791 ae_tax_activity_tbl(l_tax_ctr1).ae_acctd_amount := l_inv_nr.acctd_amount;
2792
2793 ae_tax_activity_tbl(l_tax_ctr1).ae_taxable_amount := l_inv_nr.taxable_amount;
2794
2795 ae_tax_activity_tbl(l_tax_ctr1).ae_taxable_acctd_amount := l_inv_nr.taxable_acctd_amount;
2796
2797 l_cached := TRUE;
2798
2799 IF PG_DEBUG in ('Y', 'C') THEN
2800 arp_standard.debug('Process_Recon: ' || ' ');
2801 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);
2802 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);
2803 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);
2804 arp_standard.debug('Process_Recon: ' || 'ae_tax_activity_tbl('||l_tax_ctr1||').ae_amount = ' || ae_tax_activity_tbl(l_tax_ctr1).ae_amount);
2805 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);
2806 arp_standard.debug('Process_Recon: ' || ' ');
2807 END IF;
2808
2809 END IF; --not cached then cache
2810
2811 END LOOP; --all activity
2812
2813 /*---------------------------------------------------------------------------------+
2814 |Now cache the accounting entries from the global accounting table due to previous|
2815 |activity on the Transaction or Bill. These accounting entries are stored in a |
2816 |PLSQL table by the parent routine which calls the reconciliation routine. This |
2817 |table may also contain reconciliation entries for assignments on Bill when a |
2818 |Transaction is being Reconciled. |
2819 +---------------------------------------------------------------------------------*/
2820 IF p_g_ae_line_tbl.EXISTS(p_g_ae_ctr) AND (g_call_num = 1) THEN
2821
2822 IF PG_DEBUG in ('Y', 'C') THEN
2823 arp_standard.debug('Process_Recon: ' || '4) Cache table p_g_ae_line_tbl Exists');
2824 END IF;
2825
2826 FOR l_ctr1 IN p_g_ae_line_tbl.FIRST .. p_g_ae_line_tbl.LAST LOOP
2827
2828 l_cached := FALSE;
2829
2830 IF PG_DEBUG in ('Y', 'C') THEN
2831 arp_standard.debug('Process_Recon: ' || ' ');
2832 arp_standard.debug('Process_Recon: ' || 'Checking whether global table accounting matches tax activity table');
2833 arp_standard.debug('Process_Recon: ' || 'p_br_cust_trx_line_id ' || p_br_cust_trx_line_id);
2834 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);
2835 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);
2836 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);
2837 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);
2838 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);
2839 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);
2840 arp_standard.debug('Process_Recon: ' || ' ');
2841 END IF;
2842
2843 IF ae_tax_activity_tbl.EXISTS(l_tax_ctr1) THEN
2844
2845 IF PG_DEBUG in ('Y', 'C') THEN
2846 arp_standard.debug('Process_Recon: ' || '4) Cache table ae_tax_activity_tbl Exists');
2847 END IF;
2848
2849 FOR l_ctr IN ae_tax_activity_tbl.FIRST .. ae_tax_activity_tbl.LAST LOOP
2850
2851 /*--------------------------------------------------------------------+
2852 |Add to accounting entry in cache if matching conditions |
2853 +--------------------------------------------------------------------*/
2854 IF ((((p_g_ae_line_tbl(l_ctr1).location_segment_id IS NOT NULL)
2855 AND (nvl(ae_tax_activity_tbl(l_ctr).ae_location_segment_id,-999)
2856 = nvl(p_g_ae_line_tbl(l_ctr1).location_segment_id,-999)))
2857 OR ((nvl(ae_tax_activity_tbl(l_ctr).ae_tax_group_code_id,-999)
2858 = nvl(p_g_ae_line_tbl(l_ctr1).tax_group_code_id,-999))
2859 AND (p_g_ae_line_tbl(l_ctr1).tax_code_id IS NOT NULL)
2860 AND (nvl(ae_tax_activity_tbl(l_ctr).ae_tax_code_id,-999)
2861 = nvl(p_g_ae_line_tbl(l_ctr1).tax_code_id,-999))))
2862
2863 --condition beow is required because the Bills global accounting cache may have accounting
2864 --entries for more than one assignment on the Bill, when br cust trx line id is populated
2865 --it implies that the source_table_secondary is CTL in p_g_ae_line_tbl because this is the
2866 --cache for the Bills accounting
2867 AND ((p_calling_point = 'TRAN')
2868 OR ((p_calling_point IN ('BILL', 'BLTR')
2869 AND (nvl(p_br_cust_trx_line_id,-999) = nvl(p_g_ae_line_tbl(l_ctr1).source_id_secondary,-999)))))
2870 AND (ae_tax_activity_tbl(l_ctr).ae_code_combination_id = p_g_ae_line_tbl(l_ctr1).account)
2871 AND (p_g_ae_line_tbl(l_ctr1).ae_line_type = 'DEFERRED_TAX'))
2872 THEN
2873
2874 IF PG_DEBUG in ('Y', 'C') THEN
2875 arp_standard.debug('Process_Recon: ' || '4) Hit found in cache ae_tax_activity_tbl');
2876 END IF;
2877
2878 ae_tax_activity_tbl(l_ctr).ae_amount :=
2879 ae_tax_activity_tbl(l_ctr).ae_amount + nvl(p_g_ae_line_tbl(l_ctr1).entered_dr,0) * -1
2880 + nvl(p_g_ae_line_tbl(l_ctr1).entered_cr,0);
2881
2882 ae_tax_activity_tbl(l_ctr).ae_acctd_amount := ae_tax_activity_tbl(l_ctr).ae_acctd_amount
2883 + nvl(p_g_ae_line_tbl(l_ctr1).accounted_dr,0) * -1
2884 + nvl(p_g_ae_line_tbl(l_ctr1).accounted_cr,0) ;
2885
2886 ae_tax_activity_tbl(l_ctr).ae_taxable_amount := ae_tax_activity_tbl(l_ctr).ae_taxable_amount
2887 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_entered_dr,0) * -1
2888 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_entered_cr,0);
2889
2890 ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount := ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount
2891 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_accounted_dr,0) * -1
2892 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_accounted_cr,0);
2893
2894 l_cached := TRUE;
2895
2896 END IF;
2897
2898 END LOOP; --for activity table from ar_distributions
2899
2900 END IF; --activity table exists for already cached entries
2901
2902 /*---------------------------------------------------------------------------------+
2903 |If an entry is not already cached when retrieved from ar_distributions then cache|
2904 +---------------------------------------------------------------------------------*/
2905 IF ((NOT l_cached)
2906 AND ((p_calling_point = 'TRAN')
2907 OR ((p_calling_point IN ('BILL', 'BLTR'))
2908 AND nvl(p_br_cust_trx_line_id,-999) = nvl(p_g_ae_line_tbl(l_ctr1).source_id_secondary,-999)))
2909 AND (p_g_ae_line_tbl(l_ctr1).ae_line_type = 'DEFERRED_TAX'))
2910 THEN
2911
2912 IF PG_DEBUG in ('Y', 'C') THEN
2913 arp_standard.debug('Process_Recon: ' || '4) Now caching in cache ae_tax_activity_tbl');
2914 END IF;
2915
2916 l_tax_ctr1 := l_tax_ctr1 + 1;
2917
2918 ae_tax_activity_tbl(l_tax_ctr1).ae_location_segment_id := p_g_ae_line_tbl(l_ctr1).location_segment_id;
2919
2920 ae_tax_activity_tbl(l_tax_ctr1).ae_tax_group_code_id := p_g_ae_line_tbl(l_ctr1).tax_group_code_id;
2921
2922 ae_tax_activity_tbl(l_tax_ctr1).ae_tax_code_id := p_g_ae_line_tbl(l_ctr1).tax_code_id;
2923
2924 ae_tax_activity_tbl(l_tax_ctr1).ae_code_combination_id := p_g_ae_line_tbl(l_ctr1).account;
2925
2926 ae_tax_activity_tbl(l_tax_ctr1).ae_amount :=
2927 nvl(p_g_ae_line_tbl(l_ctr1).entered_dr,0) * -1 + nvl(p_g_ae_line_tbl(l_ctr1).entered_cr,0);
2928
2929 ae_tax_activity_tbl(l_tax_ctr1).ae_acctd_amount :=
2930 nvl(p_g_ae_line_tbl(l_ctr1).accounted_dr,0) * -1 + nvl(p_g_ae_line_tbl(l_ctr1).accounted_cr,0);
2931
2932 ae_tax_activity_tbl(l_tax_ctr1).ae_taxable_amount :=
2933 nvl(p_g_ae_line_tbl(l_ctr1).taxable_entered_dr,0) * -1
2934 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_entered_cr,0);
2935
2936 ae_tax_activity_tbl(l_tax_ctr1).ae_taxable_acctd_amount :=
2937 nvl(p_g_ae_line_tbl(l_ctr1).taxable_accounted_dr,0) * -1
2938 + nvl(p_g_ae_line_tbl(l_ctr1).taxable_accounted_cr,0);
2939
2940 l_cached := TRUE;
2941
2942 IF PG_DEBUG in ('Y', 'C') THEN
2943 arp_standard.debug('Process_Recon: ' || ' ');
2944 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);
2945 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);
2946 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);
2947 arp_standard.debug('Process_Recon: ' || ' ');
2948 END IF;
2949
2950 END IF; --not cached then cache
2951
2952 END LOOP; --all activity
2953
2954 END IF; --entries exist in the global accounting table for a activity
2955
2956 /*------------------------------------------------------------------------------------------+
2957 |Reconcile the simulated application accounting for deferred tax for the shadow adjustment |
2958 |accounting with that of the physically stored accounting entries in ar_distributions, due |
2959 |to activity on the Bill |
2960 +------------------------------------------------------------------------------------------*/
2961 IF PG_DEBUG in ('Y', 'C') THEN
2962 arp_standard.debug('Process_Recon: ' || 'Reconciling original accounting with the activity accounting ');
2963 END IF;
2964
2965 IF ae_tax_tbl.EXISTS(l_tax_ctr) THEN
2966
2967 IF PG_DEBUG in ('Y', 'C') THEN
2968 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl Exists , ae_tax_activity_tbl Exists ');
2969 END IF;
2970
2971 FOR l_ctr IN ae_tax_tbl.FIRST .. ae_tax_tbl.LAST LOOP
2972
2973 IF PG_DEBUG in ('Y', 'C') THEN
2974 arp_standard.debug('Process_Recon: ' || 'Looping through table ae_tax_tbl to Reconcile l_ctr ' || l_ctr);
2975 arp_standard.debug('Process_Recon: ' || ' ');
2976 arp_standard.debug('Process_Recon: ' || '******** ');
2977 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_ctr||').ae_location_segment_id = '|| ae_tax_tbl(l_ctr).ae_location_segment_id);
2978 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);
2979 arp_standard.debug('Process_Recon: ' || 'ae_tax_tbl('||l_ctr||').ae_tax_code_id = '|| ae_tax_tbl(l_ctr).ae_tax_code_id);
2980 arp_standard.debug('Process_Recon: ' || '******** ');
2981 END IF;
2982
2983 l_match_cond := FALSE;
2984
2985 IF ae_tax_activity_tbl.EXISTS(l_tax_ctr1) THEN
2986
2987 FOR l_ctr1 IN ae_tax_activity_tbl.FIRST .. ae_tax_activity_tbl.LAST LOOP
2988
2989 IF PG_DEBUG in ('Y', 'C') THEN
2990 arp_standard.debug('Process_Recon: ' || 'Looping through table ae_tax_activity_tbl l_ctr1 ' || l_ctr1);
2991 arp_standard.debug('Process_Recon: ' || ' ');
2992 arp_standard.debug('Process_Recon: ' || '>>>>>>>> COMPARE');
2993 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);
2994 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);
2995 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);
2996 arp_standard.debug('Process_Recon: ' || 'Amount ' || ae_tax_tbl(l_ctr).ae_amount || ' VS ' || ae_tax_activity_tbl(l_ctr1).ae_amount);
2997 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);
2998 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);
2999 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);
3000 arp_standard.debug('Process_Recon: ' || '>>>>>>>> COMPARE');
3001 END IF;
3002
3003 IF (((ae_tax_tbl(l_ctr).ae_location_segment_id IS NOT NULL)
3004 AND (ae_tax_activity_tbl(l_ctr1).ae_location_segment_id IS NOT NULL)
3005 AND (nvl(ae_tax_tbl(l_ctr).ae_location_segment_id,-999)
3006 = nvl(ae_tax_activity_tbl(l_ctr1).ae_location_segment_id,-999)))
3007 OR ((nvl(ae_tax_tbl(l_ctr).ae_tax_group_code_id,-999)
3008 = nvl(ae_tax_activity_tbl(l_ctr1).ae_tax_group_code_id,-999))
3009 AND (ae_tax_tbl(l_ctr).ae_tax_code_id IS NOT NULL)
3010 AND (ae_tax_activity_tbl(l_ctr1).ae_tax_code_id IS NOT NULL)
3011 AND (nvl(ae_tax_tbl(l_ctr).ae_tax_code_id,-999)
3012 = nvl(ae_tax_activity_tbl(l_ctr1).ae_tax_code_id,-999)))
3013 AND (ae_tax_tbl(l_ctr).ae_code_combination_id = ae_tax_activity_tbl(l_ctr1).ae_code_combination_id))
3014 THEN
3015
3016 IF PG_DEBUG in ('Y', 'C') THEN
3017 arp_standard.debug('Process_Recon: ' || 'Matching condition found in ae_tax_tbl, construct reconcile entry ');
3018 END IF;
3019
3020 ae_tax_activity_tbl(l_ctr1).ae_match_flag := 'Y';
3021
3022 l_match_cond := TRUE;
3023 l_cre_rec := FALSE;
3024 l_ae_line_rec := l_ae_empty_line_rec;
3025
3026 --deferred tax amounts
3027 IF ((ae_tax_tbl(l_ctr).ae_amount + ae_tax_activity_tbl(l_ctr1).ae_amount) < 0) THEN
3028
3029 l_ae_line_rec.entered_dr := NULL;
3030
3031 l_ae_line_rec.entered_cr :=
3032 abs(ae_tax_tbl(l_ctr).ae_amount + ae_tax_activity_tbl(l_ctr1).ae_amount);
3033
3034 l_cre_rec := TRUE;
3035
3036 ELSIF ((ae_tax_tbl(l_ctr).ae_amount + ae_tax_activity_tbl(l_ctr1).ae_amount) > 0) THEN
3037
3038 l_ae_line_rec.entered_dr :=
3039 abs(ae_tax_tbl(l_ctr).ae_amount + ae_tax_activity_tbl(l_ctr1).ae_amount);
3040
3041 l_ae_line_rec.entered_cr := NULL;
3042
3043 l_cre_rec := TRUE;
3044
3045 END IF;
3046
3047 --deferred tax accounted amounts
3048 IF ((ae_tax_tbl(l_ctr).ae_acctd_amount + ae_tax_activity_tbl(l_ctr1).ae_acctd_amount) < 0) THEN
3049
3050 l_ae_line_rec.accounted_dr := NULL;
3051
3052 l_ae_line_rec.accounted_cr :=
3053 abs(ae_tax_tbl(l_ctr).ae_acctd_amount + ae_tax_activity_tbl(l_ctr1).ae_acctd_amount) ;
3054
3055 l_cre_rec := TRUE;
3056
3057 ELSIF ((ae_tax_tbl(l_ctr).ae_acctd_amount + ae_tax_activity_tbl(l_ctr1).ae_acctd_amount) > 0) THEN
3058
3059 l_ae_line_rec.accounted_dr :=
3060 abs(ae_tax_tbl(l_ctr).ae_acctd_amount + ae_tax_activity_tbl(l_ctr1).ae_acctd_amount);
3061
3062 l_ae_line_rec.accounted_cr := NULL;
3063
3064 l_cre_rec := TRUE;
3065
3066 END IF;
3067
3068 --taxable amounts
3069 IF ((ae_tax_tbl(l_ctr).ae_taxable_amount + ae_tax_activity_tbl(l_ctr1).ae_taxable_amount) < 0) THEN
3070
3071 l_ae_line_rec.taxable_entered_dr := NULL;
3072
3073 l_ae_line_rec.taxable_entered_cr :=
3074 abs(ae_tax_tbl(l_ctr).ae_taxable_amount + ae_tax_activity_tbl(l_ctr1).ae_taxable_amount);
3075
3076 l_cre_rec := TRUE;
3077
3078 ELSIF ((ae_tax_tbl(l_ctr).ae_taxable_amount + ae_tax_activity_tbl(l_ctr1).ae_taxable_amount) > 0) THEN
3079
3080 l_ae_line_rec.taxable_entered_dr :=
3081 abs(ae_tax_tbl(l_ctr).ae_taxable_amount + ae_tax_activity_tbl(l_ctr1).ae_taxable_amount);
3082
3083 l_ae_line_rec.taxable_entered_cr := NULL;
3084
3085 l_cre_rec := TRUE;
3086
3087 END IF;
3088
3089 --taxable accounted amounts
3090 IF ((ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
3091 + ae_tax_activity_tbl(l_ctr1).ae_taxable_acctd_amount) < 0) THEN
3092
3093 l_ae_line_rec.taxable_accounted_dr := NULL;
3094
3095 l_ae_line_rec.taxable_accounted_cr :=
3096 abs(ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
3097 + ae_tax_activity_tbl(l_ctr1).ae_taxable_acctd_amount);
3098
3099 l_cre_rec := TRUE;
3100
3101 ELSIF ((ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
3102 + ae_tax_activity_tbl(l_ctr1).ae_taxable_acctd_amount) > 0) THEN
3103
3104 l_ae_line_rec.taxable_accounted_dr :=
3105 abs(ae_tax_tbl(l_ctr).ae_taxable_acctd_amount
3106 + ae_tax_activity_tbl(l_ctr1).ae_taxable_acctd_amount);
3107
3108 l_ae_line_rec.taxable_accounted_cr := NULL;
3109
3110 l_cre_rec := TRUE;
3111
3112 END IF;
3113
3114 EXIT; --loop activity table because tax and activity table match
3115
3116 END IF; --deferred tax codes for tax and activity table match
3117
3118 END LOOP; --activity table
3119
3120 END IF; -- Tax activity table exists
3121
3122 /*---------------------------------------------------------------------------------+
3123 |If no matching condition between tax table and tax activity table, then it means |
3124 |we need to create a reconciliation entry matching the original tax on the Bills |
3125 |assignment or transaction. |
3126 +---------------------------------------------------------------------------------*/
3127 IF (NOT l_match_cond) THEN
3128
3129 IF PG_DEBUG in ('Y', 'C') THEN
3130 arp_standard.debug('Process_Recon: ' || 'Matching condition not found in ae_tax_tbl, construct reconcile entry ');
3131 END IF;
3132
3133 --set amount
3134 IF ae_tax_tbl(l_ctr).ae_amount > 0 THEN
3135
3136 l_ae_line_rec.entered_dr := abs(ae_tax_tbl(l_ctr).ae_amount);
3137 l_ae_line_rec.entered_cr := NULL;
3138 l_cre_rec := TRUE;
3139 ELSIF ae_tax_tbl(l_ctr).ae_amount < 0 THEN
3140 l_ae_line_rec.entered_dr := NULL;
3141 l_ae_line_rec.entered_cr := abs(ae_tax_tbl(l_ctr).ae_amount);
3142 l_cre_rec := TRUE;
3143 END IF;
3144
3145 --set accounted amount
3146 IF ae_tax_tbl(l_ctr).ae_acctd_amount > 0 THEN
3147
3148 l_ae_line_rec.accounted_dr := abs(ae_tax_tbl(l_ctr).ae_acctd_amount);
3149 l_ae_line_rec.accounted_cr := NULL;
3150 l_cre_rec := TRUE;
3151 ELSIF ae_tax_tbl(l_ctr).ae_acctd_amount < 0 THEN
3152 l_ae_line_rec.accounted_dr := NULL;
3153 l_ae_line_rec.accounted_cr := abs(ae_tax_tbl(l_ctr).ae_acctd_amount);
3154 l_cre_rec := TRUE;
3155 END IF;
3156
3157 --set taxable amount
3158 IF ae_tax_tbl(l_ctr).ae_taxable_amount > 0 THEN
3159 l_ae_line_rec.taxable_entered_dr := abs(ae_tax_tbl(l_ctr).ae_taxable_amount);
3160 l_ae_line_rec.taxable_entered_cr := NULL;
3161 l_cre_rec := TRUE;
3162 ELSIF ae_tax_tbl(l_ctr).ae_taxable_amount < 0 THEN
3163 l_ae_line_rec.taxable_entered_dr := NULL;
3164 l_ae_line_rec.taxable_entered_cr := abs(ae_tax_tbl(l_ctr).ae_taxable_amount);
3165 l_cre_rec := TRUE;
3166 END IF;
3167
3168 --set taxable accounted amount
3169 IF ae_tax_tbl(l_ctr).ae_taxable_acctd_amount > 0 THEN
3170 l_ae_line_rec.taxable_accounted_dr := abs(ae_tax_tbl(l_ctr).ae_taxable_acctd_amount);
3171 l_ae_line_rec.taxable_accounted_cr := NULL;
3172 l_cre_rec := TRUE;
3173 ELSIF ae_tax_tbl(l_ctr).ae_taxable_acctd_amount < 0 THEN
3174 l_ae_line_rec.taxable_accounted_dr := NULL;
3175 l_ae_line_rec.taxable_accounted_cr := abs(ae_tax_tbl(l_ctr).ae_taxable_acctd_amount);
3176 l_cre_rec := TRUE;
3177 END IF;
3178
3179 END IF; --no matching condition
3180
3181 /*---------------------------------------------------------------------------------+
3182 |Build the ar distributions accounting record for cache into the global accounting|
3183 |table. |
3184 +---------------------------------------------------------------------------------*/
3185 IF (l_cre_rec) THEN --set other attributes of accounting lines reconciliation entry
3186
3187 IF PG_DEBUG in ('Y', 'C') THEN
3188 arp_standard.debug('Process_Recon: ' || 'Assemble the l_ae_line_rec record for reconciliation entry ');
3189 END IF;
3190
3191 --Build the Deferred Tax accounting entry
3192 Build_Deferred_Tax(
3193 p_customer_trx_id => p_customer_trx_id ,
3194 p_br_cust_trx_line_id => p_br_cust_trx_line_id ,
3195 p_location_segment_id => ae_tax_tbl(l_ctr).ae_location_segment_id ,
3196 p_tax_group_code_id => ae_tax_tbl(l_ctr).ae_tax_group_code_id ,
3197 p_tax_code_id => ae_tax_tbl(l_ctr).ae_tax_code_id ,
3198 p_code_combination_id => ae_tax_tbl(l_ctr).ae_code_combination_id ,
3199 p_ae_doc_rec => p_ae_doc_rec ,
3200 p_cust_inv_rec => p_cust_inv_rec ,
3201 p_calling_point => p_calling_point ,
3202 p_ae_line_rec => l_ae_line_rec );
3203
3204 --Assign tax lines reconciliation record to global accounting table
3205 Assign_Elements(p_ae_line_rec => l_ae_line_rec ,
3206 p_g_ae_ctr => p_g_ae_ctr ,
3207 p_g_ae_line_tbl => p_g_ae_line_tbl );
3208
3209 --Build the Collected tax accounting entry
3210 Build_Tax (p_customer_trx_id => p_customer_trx_id,
3211 p_location_segment_id => ae_tax_tbl(l_ctr).ae_location_segment_id ,
3212 p_tax_group_code_id => ae_tax_tbl(l_ctr).ae_tax_group_code_id ,
3213 p_tax_code_id => ae_tax_tbl(l_ctr).ae_tax_code_id ,
3214 p_code_combination_id => ae_tax_tbl(l_ctr).ae_code_combination_id ,
3215 p_ae_line_rec => l_ae_line_rec );
3216
3217 --Assign tax lines reconciliation record to global accounting table
3218 Assign_Elements(p_ae_line_rec => l_ae_line_rec ,
3219 p_g_ae_ctr => p_g_ae_ctr ,
3220 p_g_ae_line_tbl => p_g_ae_line_tbl);
3221
3222 END IF; --create reconciliation accounting record
3223
3224 END LOOP; --tax table
3225
3226 IF PG_DEBUG in ('Y', 'C') THEN
3227 arp_standard.debug('Process_Recon: ' || 'REVERSE SWEEP ');
3228 END IF;
3229
3230 /*----------------------------------------------------------------------------+
3231 | Sweep through the tax activity table and if the matching flag is not Y then|
3232 | it means that the combination of tax group, tax code, tax account or tax |
3233 | location and account does not exist on the Original Transaction - so back |
3234 | out NOCOPY the deferred tax. |
3235 +----------------------------------------------------------------------------*/
3236 IF ae_tax_activity_tbl.EXISTS(l_tax_ctr1) THEN
3237
3238 FOR l_ctr IN ae_tax_activity_tbl.FIRST .. ae_tax_activity_tbl.LAST LOOP
3239
3240 IF nvl(ae_tax_activity_tbl(l_ctr).ae_match_flag, 'N') <> 'Y' THEN
3241
3242 --Initialize record
3243 l_ae_line_rec := l_ae_empty_line_rec;
3244
3245 /*------------------------------------------------------------------+
3246 | Set the deferred tax accounting buckets, and taxable buckets for |
3247 | creation of the Deferred tax reversal on accounting created due |
3248 | to activity as there is no match for on Original Transaction by |
3249 | tax group, tax code, location and account |
3250 +------------------------------------------------------------------*/
3251 IF PG_DEBUG in ('Y', 'C') THEN
3252 arp_standard.debug('Process_Recon: ' || 'REVERSE SWEEP ae_tax_activity_tbl(l_ctr).ae_amount' || ae_tax_activity_tbl(l_ctr).ae_amount);
3253 END IF;
3254 IF ae_tax_activity_tbl(l_ctr).ae_amount > 0 THEN
3255
3256 l_ae_line_rec.entered_dr := abs(ae_tax_activity_tbl(l_ctr).ae_amount);
3257 l_ae_line_rec.entered_cr := NULL;
3258 l_cre_rec := TRUE;
3259 ELSIF ae_tax_activity_tbl(l_ctr).ae_amount < 0 THEN
3260 l_ae_line_rec.entered_dr := NULL;
3261 l_ae_line_rec.entered_cr := abs(ae_tax_activity_tbl(l_ctr).ae_amount);
3262 l_cre_rec := TRUE;
3263 END IF;
3264
3265 IF PG_DEBUG in ('Y', 'C') THEN
3266 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);
3267 END IF;
3268 --set accounted amount
3269 IF ae_tax_activity_tbl(l_ctr).ae_acctd_amount > 0 THEN
3270
3271 l_ae_line_rec.accounted_dr := abs(ae_tax_activity_tbl(l_ctr).ae_acctd_amount);
3272 l_ae_line_rec.accounted_cr := NULL;
3273 l_cre_rec := TRUE;
3274 ELSIF ae_tax_activity_tbl(l_ctr).ae_acctd_amount < 0 THEN
3275 l_ae_line_rec.accounted_dr := NULL;
3276 l_ae_line_rec.accounted_cr := abs(ae_tax_activity_tbl(l_ctr).ae_acctd_amount);
3277 l_cre_rec := TRUE;
3278 END IF;
3279
3280 IF PG_DEBUG in ('Y', 'C') THEN
3281 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);
3282 END IF;
3283 --set taxable amount
3284 IF ae_tax_activity_tbl(l_ctr).ae_taxable_amount > 0 THEN
3285 l_ae_line_rec.taxable_entered_dr := abs(ae_tax_activity_tbl(l_ctr).ae_taxable_amount);
3286 l_ae_line_rec.taxable_entered_cr := NULL;
3287 l_cre_rec := TRUE;
3288 ELSIF ae_tax_activity_tbl(l_ctr).ae_taxable_amount < 0 THEN
3289 l_ae_line_rec.taxable_entered_dr := NULL;
3290 l_ae_line_rec.taxable_entered_cr := abs(ae_tax_activity_tbl(l_ctr).ae_taxable_amount);
3291 l_cre_rec := TRUE;
3292 END IF;
3293
3294 IF PG_DEBUG in ('Y', 'C') THEN
3295 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);
3296 END IF;
3297 --set taxable accounted amount
3298 IF ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount > 0 THEN
3299 l_ae_line_rec.taxable_accounted_dr := abs(ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount);
3300 l_ae_line_rec.taxable_accounted_cr := NULL;
3301 l_cre_rec := TRUE;
3302 ELSIF ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount < 0 THEN
3303 l_ae_line_rec.taxable_accounted_dr := NULL;
3304 l_ae_line_rec.taxable_accounted_cr := abs(ae_tax_activity_tbl(l_ctr).ae_taxable_acctd_amount);
3305 l_cre_rec := TRUE;
3306 END IF;
3307
3308 --Build the Deferred Tax accounting entry
3309 Build_Deferred_Tax(
3310 p_customer_trx_id => p_customer_trx_id ,
3311 p_br_cust_trx_line_id => p_br_cust_trx_line_id ,
3312 p_location_segment_id => ae_tax_activity_tbl(l_ctr).ae_location_segment_id ,
3313 p_tax_group_code_id => ae_tax_activity_tbl(l_ctr).ae_tax_group_code_id ,
3314 p_tax_code_id => ae_tax_activity_tbl(l_ctr).ae_tax_code_id ,
3315 p_code_combination_id => ae_tax_activity_tbl(l_ctr).ae_code_combination_id ,
3316 p_ae_doc_rec => p_ae_doc_rec ,
3317 p_cust_inv_rec => p_cust_inv_rec ,
3318 p_calling_point => p_calling_point ,
3319 p_ae_line_rec => l_ae_line_rec );
3320
3321 --Assign tax lines reconciliation record to global accounting table
3322 Assign_Elements(p_ae_line_rec => l_ae_line_rec ,
3323 p_g_ae_ctr => p_g_ae_ctr ,
3324 p_g_ae_line_tbl => p_g_ae_line_tbl );
3325
3326 --Build the Collected tax accounting entry
3327 Build_Tax (p_customer_trx_id => p_customer_trx_id,
3328 p_location_segment_id => ae_tax_activity_tbl(l_ctr).ae_location_segment_id ,
3329 p_tax_group_code_id => ae_tax_activity_tbl(l_ctr).ae_tax_group_code_id ,
3330 p_tax_code_id => ae_tax_activity_tbl(l_ctr).ae_tax_code_id ,
3331 p_code_combination_id => ae_tax_activity_tbl(l_ctr).ae_code_combination_id ,
3332 p_ae_line_rec => l_ae_line_rec );
3333
3334 --Assign tax lines reconciliation record to global accounting table
3335 Assign_Elements(p_ae_line_rec => l_ae_line_rec ,
3336 p_g_ae_ctr => p_g_ae_ctr ,
3337 p_g_ae_line_tbl => p_g_ae_line_tbl);
3338
3339 END IF;
3340
3341 END LOOP; --tax activity table
3342
3343 END IF; --tax activity table exists
3344
3345 END IF; --lines exist in tax and activity table
3346
3347 IF PG_DEBUG in ('Y', 'C') THEN
3348 arp_standard.debug('ARP_RECONCILE.Process_Recon ()-');
3349 END IF;
3350
3351 EXCEPTION
3352 WHEN NO_DATA_FOUND THEN
3353 IF PG_DEBUG in ('Y', 'C') THEN
3354 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Process_Recon ');
3355 END IF;
3356 RAISE;
3357
3358 WHEN OTHERS THEN
3359 IF PG_DEBUG in ('Y', 'C') THEN
3360 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Process_Recon ');
3361 END IF;
3362 RAISE;
3363
3364 END Process_Recon;
3365
3366 /*========================================================================
3367 | PRIVATE PROCEDURE Build_Deferred_Tax
3368 |
3369 | DESCRIPTION
3370 | Builds the the deferred tax accounting entry for Reconciliation
3371 | of the accounting, sets currency details, accounts, source and
3372 | tax group, tax codes or location
3373 |
3374 | PARAMETERS
3375 | p_customer_trx_id IN Transaction Id
3376 | p_br_cust_trx_line_id IN Bills assignment line id
3377 | p_location_segment_id IN Location segment
3378 | p_tax_group_code_id IN Group Code
3379 | p_tax_code_id IN Tax Code Id
3380 | p_code_combination_id IN Ccid of deferred tax account
3381 | p_ae_doc_rec IN Document Record
3382 | p_cust_inv_rec IN Exchange rate details record
3383 | p_ae_line_rec IN Line record
3384 +-----------------------------------------------------------------------------*/
3385 PROCEDURE Build_Deferred_Tax (p_customer_trx_id IN NUMBER,
3386 p_br_cust_trx_line_id IN NUMBER,
3387 p_location_segment_id IN NUMBER,
3388 p_tax_group_code_id IN NUMBER,
3389 p_tax_code_id IN NUMBER,
3390 p_code_combination_id IN NUMBER,
3391 p_ae_doc_rec IN ae_doc_rec_type,
3392 p_cust_inv_rec IN ra_customer_trx%ROWTYPE,
3393 p_calling_point IN VARCHAR2,
3394 p_ae_line_rec IN OUT NOCOPY ae_line_rec_type ) IS
3395
3396 BEGIN
3397
3398 IF PG_DEBUG in ('Y', 'C') THEN
3399 arp_standard.debug('ARP_RECONCILE.Build_Deferred_Tax ()+');
3400 END IF;
3401
3402 /*-----------------------------------------------------------------------------+
3403 | Create the Dr or Cr to the deferred tax account, set details such as rates, |
3404 | source type secondary , tax group, tax code or location id. |
3405 +-----------------------------------------------------------------------------*/
3406 IF p_location_segment_id IS NOT NULL THEN
3407 p_ae_line_rec.location_segment_id := p_location_segment_id;
3408 ELSE
3409 p_ae_line_rec.tax_group_code_id := p_tax_group_code_id;
3410 p_ae_line_rec.tax_code_id := p_tax_code_id;
3411 END IF;
3412
3413 /*-----------------------------------------------------------------------------+
3414 | Assign Currency Exchange rate information to initialisation record, tax link|
3415 | id is not populated. Create the Dr or Cr to the deferred tax account. |
3416 +-----------------------------------------------------------------------------*/
3417 p_ae_line_rec.source_id := p_ae_doc_rec.source_id ;
3418 p_ae_line_rec.source_table := p_ae_doc_rec.source_table ;
3419 p_ae_line_rec.ae_line_type := 'DEFERRED_TAX' ;
3420 p_ae_line_rec.account := p_code_combination_id ;
3421
3422 /*------------------------------------------------------------------------------+
3423 | Populate the secondary columns, for Bills Receivable we populate with the |
3424 | Bill line id, however for transactions only the source type secondary is used|
3425 +------------------------------------------------------------------------------*/
3426 IF (p_calling_point IN ('BILL', 'BLTR')) THEN
3427 IF PG_DEBUG in ('Y', 'C') THEN
3428 arp_standard.debug('Build_Deferred_Tax: ' || 'Setting source type secondary to ASSIGNMENT_RECONCILE');
3429 END IF;
3430
3431 --set transaction reconciliation entries line type secondary when bill is closed
3432 IF (p_calling_point = 'BLTR') THEN
3433 p_ae_line_rec.ae_line_type_secondary := 'RECONCILE' ;
3434 ELSE
3435 p_ae_line_rec.ae_line_type_secondary := 'ASSIGNMENT_RECONCILE' ;
3436 END IF;
3437
3438 p_ae_line_rec.source_id_secondary := p_br_cust_trx_line_id ;
3439 p_ae_line_rec.source_table_secondary := 'CTL' ;
3440 ELSE --for transactions we only populate source type secondary for deferred tax
3441 IF PG_DEBUG in ('Y', 'C') THEN
3442 arp_standard.debug('Build_Deferred_Tax: ' || 'Setting source type secondary to RECONCILE');
3443 END IF;
3444 p_ae_line_rec.ae_line_type_secondary := 'RECONCILE';
3445 p_ae_line_rec.source_id_secondary := p_customer_trx_id;
3446 p_ae_line_rec.source_table_secondary := 'CT';
3447 END IF;
3448
3449 p_ae_line_rec.currency_code := p_cust_inv_rec.invoice_currency_code ;
3450 p_ae_line_rec.currency_conversion_rate := p_cust_inv_rec.exchange_rate ;
3451 p_ae_line_rec.currency_conversion_type := p_cust_inv_rec.exchange_rate_type ;
3452 p_ae_line_rec.currency_conversion_date := p_cust_inv_rec.exchange_date ;
3453 p_ae_line_rec.third_party_id := p_cust_inv_rec.bill_to_customer_id ;
3454 p_ae_line_rec.third_party_sub_id := p_cust_inv_rec.bill_to_site_use_id ;
3455 p_ae_line_rec.tax_link_id := '' ;
3456 p_ae_line_rec.reversed_source_id := '' ;
3457
3458 IF PG_DEBUG in ('Y', 'C') THEN
3459 arp_standard.debug('ARP_RECONCILE.Build_Deferred_Tax ()-');
3460 END IF;
3461
3462 EXCEPTION
3463 WHEN NO_DATA_FOUND THEN
3464 IF PG_DEBUG in ('Y', 'C') THEN
3465 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Build_Deferred_Tax ');
3466 END IF;
3467 RAISE;
3468
3469 WHEN OTHERS THEN
3470 IF PG_DEBUG in ('Y', 'C') THEN
3471 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Build_Deferred_Tax ');
3472 END IF;
3473 RAISE;
3474
3475 END Build_Deferred_Tax;
3476
3477 /*========================================================================
3478 | PRIVATE PROCEDURE Build_Tax
3479 |
3480 | DESCRIPTION
3481 | Builds the line record swapping the amounts and taxable amounts.
3482 | Sets the account.
3483 |
3484 | PARAMETERS
3485 | p_customer_trx_id IN Transaction Id
3486 | p_location_segment_id IN Location segment
3487 | p_tax_group_code_id IN Group Code
3488 | p_tax_code_id IN Tax Code Id
3489 | p_code_combination_id IN Ccid of deferred tax account
3490 | p_ae_line_rec IN Line record
3491 +-----------------------------------------------------------------------------*/
3492 PROCEDURE Build_Tax (p_customer_trx_id IN NUMBER,
3493 p_location_segment_id IN NUMBER,
3494 p_tax_group_code_id IN NUMBER,
3495 p_tax_code_id IN NUMBER,
3496 p_code_combination_id IN NUMBER,
3497 p_ae_line_rec IN OUT NOCOPY ae_line_rec_type ) IS
3498
3499 l_collected_ccid NUMBER;
3500 l_swap_amt NUMBER;
3501
3502 BEGIN
3503 IF PG_DEBUG in ('Y', 'C') THEN
3504 arp_standard.debug('ARP_RECONCILE.Build_Tax ()+');
3505 END IF;
3506
3507 /*-----------------------------------------------------------------------------+
3508 | Create the Offsetting Dr or Cr to the Collected tax account. To do this, the|
3509 | tax code or location is used to retrieve the collected tax account. |
3510 | Retrieve the offsetting collected tax account from the Invoices tax code or |
3511 | location from the accounting distributions. Note if the same tax group, tax |
3512 | code or location segment for a deferred tax account has more than one |
3513 | collected tax account, then the max of the ccid contains the reconciled |
3514 | difference. This may happen if it is possible to change distributions |
3515 | manually. Ideally the combination of deferred and collected tax accounts |
3516 | will not change. |
3517 +-----------------------------------------------------------------------------*/
3518 IF PG_DEBUG in ('Y', 'C') THEN
3519 arp_standard.debug('Build_Tax: ' || 'Fetching offsetting collected tax accounting entry');
3520 arp_standard.debug('Build_Tax: ' || 'Using parameter p_customer_trx_id ' || p_customer_trx_id);
3521 arp_standard.debug('Build_Tax: ' || 'Using parameter location_segment_id ' || p_location_segment_id);
3522 arp_standard.debug('Build_Tax: ' || 'Using parameter tax_group_code_id ' || p_tax_group_code_id);
3523 arp_standard.debug('Build_Tax: ' || 'Using parameter tax_code_id ' || p_tax_code_id);
3524 arp_standard.debug('Build_Tax: ' || 'Using parameter code_combination_id ' || p_code_combination_id);
3525 END IF;
3526
3527 --In R12 the vat tax id also called the tax rate id is the unique key
3528 --there is no concept of tax group id and location segment id is no longer
3529 --used - it is all vat tax id on TAX line type
3530 SELECT max(gld.collected_tax_ccid) ae_collected_tax_ccid
3531 INTO l_collected_ccid
3532 FROM ra_cust_trx_line_gl_dist gld,
3533 ra_customer_trx_lines ctl
3534 --ra_customer_trx_lines ctl1
3535 WHERE ctl.customer_trx_id = p_customer_trx_id
3536 AND gld.customer_trx_id = ctl.customer_trx_id
3537 AND gld.customer_trx_line_id = ctl.customer_trx_line_id
3538 AND gld.account_class = 'TAX'
3539 AND gld.account_set_flag = 'N'
3540 AND gld.collected_tax_ccid IS NOT NULL --deferred tax only
3541 AND gld.code_combination_id = p_code_combination_id
3542 -- AND (((p_location_segment_id IS NOT NULL)
3543 -- AND (ctl.location_segment_id = nvl(p_location_segment_id,-999)))
3544 --AND (p_tax_code_id IS NOT NULL)
3545 AND ctl.vat_tax_id = nvl(p_tax_code_id,-999)
3546 --AND ctl.link_to_cust_trx_line_id = ctl1.customer_trx_line_id
3547 --AND ctl1.vat_tax_id = nvl(p_tax_group_code_id,ctl1.vat_tax_id)
3548 AND not exists (select 'x'
3549 from ra_customer_trx_lines ctl1
3550 where ctl1.customer_trx_id = p_customer_trx_id
3551 and ctl1.autorule_complete_flag = 'N');
3552
3553 IF PG_DEBUG in ('Y', 'C') THEN
3554 arp_standard.debug('Build_Tax: ' || 'Completed fetching offsetting collected tax accounting entry');
3555 END IF;
3556
3557 p_ae_line_rec.ae_line_type := 'TAX';
3558 p_ae_line_rec.account := l_collected_ccid;
3559
3560 --Now swap debits and credits for the Collected tax amounts
3561 l_swap_amt := p_ae_line_rec.entered_dr;
3562 p_ae_line_rec.entered_dr := p_ae_line_rec.entered_cr;
3563 p_ae_line_rec.entered_cr := l_swap_amt;
3564
3565 --Now swap debits and credits for the Collected tax accounted amounts
3566 l_swap_amt := p_ae_line_rec.accounted_dr;
3567 p_ae_line_rec.accounted_dr := p_ae_line_rec.accounted_cr;
3568 p_ae_line_rec.accounted_cr := l_swap_amt;
3569
3570 --Now swap debits and credits for the Collected taxable amounts
3571 l_swap_amt := p_ae_line_rec.taxable_entered_dr;
3572 p_ae_line_rec.taxable_entered_dr := p_ae_line_rec.taxable_entered_cr;
3573 p_ae_line_rec.taxable_entered_cr := l_swap_amt;
3574
3575 --Now swap debits and credits for the Collected taxable accounted amounts
3576 l_swap_amt := p_ae_line_rec.taxable_accounted_dr;
3577 p_ae_line_rec.taxable_accounted_dr := p_ae_line_rec.taxable_accounted_cr;
3578 p_ae_line_rec.taxable_accounted_cr := l_swap_amt;
3579
3580 IF PG_DEBUG in ('Y', 'C') THEN
3581 arp_standard.debug('ARP_RECONCILE.Build_Tax ()-');
3582 END IF;
3583
3584 EXCEPTION
3585 WHEN NO_DATA_FOUND THEN
3586 IF PG_DEBUG in ('Y', 'C') THEN
3587 arp_standard.debug('EXCEPTION NO_DATA_FOUND : ARP_RECONCILE.Build_Tax ');
3588 END IF;
3589 RAISE;
3590
3591 WHEN OTHERS THEN
3592 IF PG_DEBUG in ('Y', 'C') THEN
3593 arp_standard.debug('EXCEPTION OTHERS : ARP_RECONCILE.Build_Tax ');
3594 END IF;
3595 RAISE;
3596
3597 END Build_Tax;
3598
3599 END ARP_RECONCILE;
[Home] [Help]