[Home] [Help]
PACKAGE BODY: APPS.ZX_JG_EXTRACT_PKG
Source
1 PACKAGE BODY zx_jg_extract_pkg AS
2 /* $Header: zxriextrajgppvtb.pls 120.4.12010000.2 2008/11/12 12:44:26 spasala ship $ */
3
4 -----------------------------------------
5 --Public Variable Declarations
6 -----------------------------------------
7 --
8 -- Define Global Variables;
9
10
11 TYPE numtab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
12
13 g_chart_of_accounts_id NUMBER;
14 g_user_id NUMBER;
15 g_today DATE;
16 g_login_id NUMBER;
17 g_request_id NUMBER;
18 g_balancing_seg VARCHAR2(30);
19 g_acct_seg_from VARCHAR2(30);
20 g_acct_seg_to VARCHAR2(30);
21
22 TYPE t_acct_all_tbl IS TABLE OF VARCHAR2(750) INDEX BY BINARY_INTEGER;
23 TYPE t_acct_all_desc_tbl IS TABLE OF VARCHAR2(7200) INDEX BY BINARY_INTEGER;
24 TYPE t_bal_seg_tbl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
25 TYPE t_bal_seg_desc_tbl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
26 TYPE t_acct_seg_tbl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
27 TYPE t_acct_seg_desc_tbl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
28 TYPE t_ccid_tbl IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
29 TYPE t_acct_date_tbl IS TABLE OF ZX_REP_ACTG_EXT_T.ACCOUNTING_DATE%TYPE INDEX BY BINARY_INTEGER;
30
31 g_acct_all_tbl t_acct_all_tbl;
32 g_acct_all_desc_tbl t_acct_all_desc_tbl;
33 g_bal_seg_tbl t_bal_seg_tbl;
34 g_bal_seg_desc_tbl t_bal_seg_desc_tbl;
35 g_acct_seg_tbl t_acct_seg_tbl;
36 g_acct_seg_desc_tbl t_acct_seg_desc_tbl;
37
38 PG_DEBUG VARCHAR2(1);
39 g_current_runtime_level NUMBER ;
40 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
41 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
42 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
43 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
44 g_error_buffer VARCHAR2(100);
45
46
47
48 /**
49 * Function Name: currency_round
50 *
51 * This function is for rounding that will be used in tax prorating logic.
52 *
53 * @return rounded currency amount
54 * @parameter: p_amount
55 * @parameter: p_precistion
56 * @parameter: p_minimum_accountable_unit
57 *
58 *
59 **/
60
61 FUNCTION currency_round(p_amount IN OUT NOCOPY NUMBER,
62 p_precision IN NUMBER,
63 p_minimum_accountable_unit IN NUMBER)
64 RETURN NUMBER
65 IS
66 l_amount NUMBER;
67 BEGIN
68
69 IF 'Y' = PG_DEBUG THEN
70
71 arp_util_tax.debug('zx_jg_extract_pkg.currency_round()+');
72
73 END IF;
74
75 IF p_precision IS NOT NULL THEN
76
77 l_amount := Round(p_amount, p_precision);
78
79 ELSIF p_minimum_accountable_unit IS NOT NULL THEN
80
81 l_amount := Round(p_amount / p_minimum_accountable_unit) * p_minimum_accountable_unit;
82
83 ELSE
84
85 IF 'Y' = PG_DEBUG THEN
86 arp_util_tax.debug('EXCEPTION in CURRENCY_ROUND()');
87 arp_util_tax.debug('Precision or Minimum Accountable Unit must be NOT NULL');
88 END IF;
89
90 RAISE program_error;
91 END IF;
92
93 IF 'Y' = PG_DEBUG THEN
94
95 arp_util_tax.debug('zx_jg_extract_pkg.currency_round()-');
96
97 END IF;
98
99 RETURN l_amount;
100
101 END currency_round;
102
103
104 /**
105 * Procedure Name: prorate_tax
106 *
107 * This procedure prorate tax per taxable account
108 * and is called from GET_AR_TAXABLE.
109 * Proraction is done for AR Transactions
110 *
111 *
112 * @param p_tax_total
113 * @param p_tax_funcl_curr_total
114 * @param p_tax_amt_tbl
115 * @param p_tax_amt_funcl_curr_tbl
116 * @param p_minimum_accountable_unit_tbl
117 * @param p_func_precistion_tbl
118 * @param p_func_min_account_unit_tbl
119 * @param p_current_line
120 * @param p_last_line
121 **/
122
123
124 PROCEDURE prorate_tax(p_tax_total IN NUMBER,
125 p_tax_funcl_curr_total IN NUMBER,
126 p_percent_tbl IN OUT NOCOPY numtab,
127 p_tax_amt_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_TBL,
128 p_tax_amt_funcl_curr_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL,
129 p_precision_tbl IN OUT NOCOPY numtab,
130 p_minimum_accountable_unit_tbl IN OUT NOCOPY numtab,
131 p_func_precision IN NUMBER,
132 p_func_min_account_unit IN NUMBER,
133 p_current_line IN NUMBER,
134 p_last_line IN NUMBER)
135 IS
136 rounderr NUMBER;
137 rounderr_funcl_curr NUMBER;
138 alloc_tax NUMBER;
139 alloc_tax_funcl_curr NUMBER;
140 full_tax NUMBER;
141 full_tax_funcl_curr NUMBER;
142
143 BEGIN
144
145 IF (g_level_procedure >= g_current_runtime_level ) THEN
146 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.prorate_tax()+',
147 'prorate_tax');
148 END IF;
149
150 IF (g_level_procedure >= g_current_runtime_level ) THEN
151 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.prorate_tax',
152 'prorate_tax='||To_char(p_tax_total)||', Acctd tax='||To_char(p_tax_funcl_curr_total));
153 END IF;
154
155
156 IF p_current_line = p_last_line THEN
157 p_tax_amt_tbl(p_current_line) := p_tax_total;
158 p_tax_amt_funcl_curr_tbl(p_current_line) := p_tax_funcl_curr_total;
159 RETURN;
160 END IF;
161
162 rounderr := 0;
163 rounderr_funcl_curr := 0;
164 alloc_tax := 0;
165 alloc_tax_funcl_curr := 0;
166
167 FOR i IN p_current_line..p_last_line-1 LOOP
168 IF (g_level_procedure >= g_current_runtime_level ) THEN
169 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.prorate_tax',
170 'Percent='||To_char(Round(p_percent_tbl(i)*100, 3))||
171 ', Precision='||To_char(p_precision_tbl(i))||
172 ', Mimimum Accountable Unit='||To_char(p_minimum_accountable_unit_tbl(i)));
173 END IF;
174
175
176 IF (p_percent_tbl(i) = 0) THEN
177
178 full_tax := 0;
179 full_tax_funcl_curr := 0;
180
181 ELSIF (p_percent_tbl(i) = 100) THEN
182
183 full_tax := p_tax_total;
184 full_tax_funcl_curr := p_tax_funcl_curr_total;
185
186 ELSE
187
188 full_tax := p_tax_total * p_percent_tbl(i) + rounderr;
189 full_tax_funcl_curr := p_tax_funcl_curr_total * p_percent_tbl(i) + rounderr_funcl_curr;
190
191 END IF;
192
193 p_tax_amt_tbl(i) := currency_round(full_tax, p_precision_tbl(i), p_minimum_accountable_unit_tbl(i));
194 p_tax_amt_funcl_curr_tbl(i) := currency_round(full_tax_funcl_curr, p_func_precision, p_func_min_account_unit);
195
196 IF (g_level_procedure >= g_current_runtime_level ) THEN
197 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.prorate_tax',
198 'Tax Amount='||To_char(p_tax_amt_tbl(i))||', Acctd Tax Amount='||To_char(p_tax_amt_funcl_curr_tbl(i)));
199 END IF;
200
201
202 rounderr := full_tax - p_tax_amt_tbl(i);
203 rounderr_funcl_curr := full_tax_funcl_curr - p_tax_amt_funcl_curr_tbl(i);
204
205 alloc_tax := alloc_tax + p_tax_amt_tbl(i);
206 alloc_tax_funcl_curr := alloc_tax_funcl_curr + p_tax_amt_funcl_curr_tbl(i);
207
208 END LOOP;
209
210 p_tax_amt_tbl(p_last_line) := p_tax_total - alloc_tax;
211 p_tax_amt_funcl_curr_tbl(p_last_line) := p_tax_funcl_curr_total - alloc_tax_funcl_curr;
212
213 IF (g_level_procedure >= g_current_runtime_level ) THEN
214 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.prorate_tax',
215 'Tax Amount='||To_char(p_tax_amt_tbl(p_last_line))||', Acctd Tax Amount='||To_char(p_tax_amt_funcl_curr_tbl(p_last_line)));
216 END IF;
217
218 IF (g_level_procedure >= g_current_runtime_level ) THEN
219 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.prorate_tax()-',
220 'prorate_tax');
221 END IF;
222
223 END prorate_tax;
224
225
226
227 /**
228 * PROCEDURE Name: insert_row
229 *
230 + This procedure insert fetched accounting info into zx_rep_actg_ext_t table
231 * and calculated tax/taxable amount into zx_rep_trx_jx_ext_t table
232 *
233 * @parameter: p_detail_tax_line_id_tbl
234 * @parameter: p_taxable_amt_tbl
235 * @parameter: p_taxable_amt_funcl_curr_tbl
236 * @parameter: p_tax_amt_tbl
237 * @parameter: p_tax_amt_funcl_curr_tbl
238 * @parameter: p_ccid_tbl
239 * @parameter: p_acct_date_tbl
240 *
241 **/
242
243 PROCEDURE insert_row(p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
244 p_taxable_amt_tbl IN ZX_EXTRACT_PKG.TAXABLE_AMT_TBL,
245 p_taxable_amt_funcl_curr_tbl IN ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL,
246 p_tax_amt_tbl IN ZX_EXTRACT_PKG.TAX_AMT_TBL,
247 p_tax_amt_funcl_curr_tbl IN ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL,
248 p_ccid_tbl IN t_ccid_tbl,
249 p_acct_date_tbl IN t_acct_date_tbl)
250
251 IS
252
253 count_tbl numtab;
254 j integer;
255
256 BEGIN
257
258 IF (g_level_procedure >= g_current_runtime_level ) THEN
259 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()+',
260 'insert_row API call ');
261 END IF;
262
263 j := 0;
264
265 -- Filter the lines by user parameter 'Balancing Segment'
266 -- And Account Segment From/To
267
268 /*
269 * Insert accounting info to TRL acct ext table
270 */
271 FOR i in p_detail_tax_line_id_tbl.first..p_detail_tax_line_id_tbl.last LOOP
272
273 IF (g_level_procedure >= g_current_runtime_level ) THEN
274 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()+',
275 'For Loop : ');
276 END IF;
277
278 -- IF g_balancing_seg = g_bal_seg_tbl(p_ccid_tbl(i)) AND
279 -- g_acct_seg_tbl(p_ccid_tbl(i)) BETWEEN g_acct_seg_from AND g_acct_seg_to THEN
280
281 IF (g_level_procedure >= g_current_runtime_level ) THEN
282 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()+',
283 'g_balancing_seg : ');
284 END IF;
285
286 /* INSERT INTO ZX_REP_ACTG_EXT_T
287 (
288 request_id,
289 created_by,
290 creation_date,
291 last_updated_by,
292 last_update_date,
293 last_update_login,
294 actg_ext_line_id,
295 detail_tax_line_id,
296 accounting_date,
297 trx_taxable_account,
298 trx_taxable_account_desc,
299 trx_taxable_balancing_segment,
300 trx_taxable_balseg_desc,
301 trx_taxable_natural_account,
302 trx_taxable_natacct_seg_desc
303 )
304 VALUES
305 (
306 g_request_id,
307 g_user_id,
308 g_today,
309 g_user_id,
310 g_today,
311 g_login_id,
312 zx_rep_actg_ext_t_s.NEXTVAL,
313 p_detail_tax_line_id_tbl(i),
314 p_acct_date_tbl(i),
315 substrb(g_acct_all_tbl(p_ccid_tbl(i)),1,240),
316 g_acct_all_desc_tbl(p_ccid_tbl(i)),
317 g_bal_seg_tbl(p_ccid_tbl(i)),
318 g_bal_seg_desc_tbl(p_ccid_tbl(i)),
319 g_acct_seg_tbl(p_ccid_tbl(i)),
320 g_acct_seg_desc_tbl(p_ccid_tbl(i))
321 ); */
322
323 /*
324 * Insert Prorated amount into jx ext itf
325 */
326
327 INSERT INTO zx_rep_trx_jx_ext_t
328 (
329 request_id,
330 created_by,
331 creation_date,
332 last_updated_by,
333 last_update_date,
334 last_update_login,
335 detail_tax_line_ext_id,
336 detail_tax_line_id,
337 numeric1,
338 numeric2,
339 numeric3,
340 numeric4
341 )
342 VALUES
343 (
344 g_request_id,
345 g_user_id,
346 g_today,
347 g_user_id,
348 g_today,
349 g_login_id,
350 zx_rep_trx_jx_ext_t_s.NEXTVAL,
351 p_detail_tax_line_id_tbl(i),
352 p_taxable_amt_tbl(i),
353 p_taxable_amt_funcl_curr_tbl(i),
354 p_tax_amt_tbl(i),
355 p_tax_amt_funcl_curr_tbl(i)
356 );
357
358 -- END IF;
359 END LOOP;
360
361 IF (g_level_procedure >= g_current_runtime_level ) THEN
362 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()-',
363 'insert_row()-: ');
364 END IF;
365
366 EXCEPTION
367 WHEN OTHERS THEN
368
369 IF (g_level_procedure >= g_current_runtime_level ) THEN
370 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()-',
371 'insert_row(EXCEPTION)- ');
372 END IF;
373 RAISE;
374
375 END insert_row;
376
377 /*-------------------+
378 | Private Procedure |
379 +-------------------*/
380
381 /**
382 * Procedure Name: initialize
383 *
384 * @return none
385 * @parameter: p_trl_global_variables_rec
386 *
387 *
388 **/
389
390 PROCEDURE initialize(p_trl_global_variables_rec IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
391 l_func_precision OUT NOCOPY NUMBER,
392 l_func_min_account_unit OUT NOCOPY NUMBER,
393 l_sob_type OUT NOCOPY VARCHAR2)
394 IS
395
396 BEGIN
397
398 IF 'Y' = PG_DEBUG THEN
399
400 arp_util_tax.debug('zx_jg_extract_pkg.initialize()+');
401
402 END IF;
403
404 g_user_id := fnd_global.user_id;
405 g_today := sysdate;
406 g_login_id := fnd_global.login_id;
407 g_chart_of_accounts_id := p_trl_global_variables_rec.chart_of_accounts_id;
408 g_request_id := p_trl_global_variables_rec.request_id;
409 g_balancing_seg := p_trl_global_variables_rec.balancing_segment_low;
410 g_acct_seg_from := p_trl_global_variables_rec.taxable_account_low;
411 --g_acct_seg_from := p_trl_global_variables_rec.tax_account_low;
412 -- g_acct_seg_to := p_trl_global_variables_rec.tax_account_high;
413 g_acct_seg_to := p_trl_global_variables_rec.taxable_account_high;
414
415 BEGIN
416
417 SELECT precision,
418 minimum_accountable_unit,
419 decode(alc_ledger_type_code,'SOURCE','P',
420 'TARGET','R',
421 'NONE','N')
422 INTO l_func_precision,
423 l_func_min_account_unit,
424 l_sob_type
425 FROM gl_ledgers sob,
426 fnd_currencies curr
427 WHERE sob.ledger_id = p_trl_global_variables_rec.ledger_id
428 AND sob.currency_code = curr.currency_code;
429
430 IF 'Y' = PG_DEBUG THEN
431
432 arp_util_tax.debug('zx_jg_extract_pkg.initialize()-');
433
434 END IF;
435
436 EXCEPTION
437
438 WHEN OTHERS THEN
439
440 IF 'Y' = PG_DEBUG THEN
441
442 arp_util_tax.debug('ledger_id = '||p_trl_global_variables_rec.ledger_id);
443
444 END IF;
445 RAISE;
446
447 END;
448
449 END initialize;
450
451 /**
452 * Procedure Name: set_accounting_info
453 *
454 * This procedure get and cache accounting info using fa_rx_flex_pkg
455 *
456 * @return none
457 * @parameter: p_ccid_tbl
458 *
459 *
460 **/
461
462 PROCEDURE set_accounting_info(p_ccid_tbl IN t_ccid_tbl)
463
464 IS
465
466 BEGIN
467
468 IF 'Y' = PG_DEBUG THEN
469
470 arp_util_tax.debug('zx_jg_extract_pkg.set_accounting_info()+');
471
472 END IF;
473
474 FOR i in p_ccid_tbl.first..p_ccid_tbl.last LOOP
475
476 IF g_acct_all_tbl.exists(p_ccid_tbl(i)) THEN
477 null;
478 ELSE
479 g_acct_all_tbl(p_ccid_tbl(i)) := fa_rx_flex_pkg.get_value(101,
480 'GL#',
481 g_chart_of_accounts_id,
482 'ALL',
483 p_ccid_tbl(i));
484 END IF;
485
486 IF g_acct_all_desc_tbl.exists(p_ccid_tbl(i)) THEN
487 null;
488 ELSE
489 g_acct_all_desc_tbl(p_ccid_tbl(i)) := fa_rx_flex_pkg.get_description(101,
490 'GL#',
491 g_chart_of_accounts_id,
492 'ALL',
493 g_acct_all_tbl(p_ccid_tbl(i)));
494 END IF;
495
496 IF g_bal_seg_tbl.exists(p_ccid_tbl(i)) THEN
497 null;
498 ELSE
499 g_bal_seg_tbl(p_ccid_tbl(i)) := fa_rx_flex_pkg.get_value(101,
500 'GL#',
501 g_chart_of_accounts_id,
502 'GL_BALANCING',
503 p_ccid_tbl(i));
504 END IF;
505
506 IF g_bal_seg_desc_tbl.exists(p_ccid_tbl(i)) THEN
507 null;
508 ELSE
509 g_bal_seg_desc_tbl(p_ccid_tbl(i)) := fa_rx_flex_pkg.get_description(101,
510 'GL#',
511 g_chart_of_accounts_id,
512 'GL_BALANCING',
513 g_bal_seg_tbl(p_ccid_tbl(i)));
514 END IF;
515
516 IF g_acct_seg_tbl.exists(p_ccid_tbl(i)) THEN
517 null;
518 ELSE
519 g_acct_seg_tbl(p_ccid_tbl(i)) := fa_rx_flex_pkg.get_value(101,
520 'GL#',
521 g_chart_of_accounts_id,
522 'GL_ACCOUNT',
523 p_ccid_tbl(i));
524 END IF;
525
526 IF g_acct_seg_desc_tbl.exists(p_ccid_tbl(i)) THEN
527 null;
528 ELSE
529 g_acct_seg_desc_tbl(p_ccid_tbl(i)) := fa_rx_flex_pkg.get_description(101,
530 'GL#',
531 g_chart_of_accounts_id,
532 'GL_ACCOUNT',
533 g_acct_seg_tbl(p_ccid_tbl(i)));
534 END IF;
535
536 END LOOP;
537
538 IF 'Y' = PG_DEBUG THEN
539
540 arp_util_tax.debug('zx_jg_extract_pkg.set_accounting_info()-');
541
542 END IF;
543
544 EXCEPTION
545
546 WHEN OTHERS THEN
547
548 IF 'Y' = PG_DEBUG THEN
549
550 arp_util_tax.debug('zx_jg_extract_pkg.set_accounting_info(EXCEPTION)-');
551
552 END IF;
553 RAISE;
554
555 END set_accounting_info;
556
557 /**
558 * PROCEDURE Name: reset_result_tables
559 *
560 * This procedure reset the cached value of temporary variables
561 *
562 * @parameter: p_detail_tax_line_id_tbl
563 * @parameter: p_trx_id_tbl
564 * @parameter: p_tax_line_id_tbl
565 * @parameter: p_trx_line_id_tbl
566 * @parameter: p_event_class_code_tbl
567 * @parameter: p_taxable_amt_tbl
568 * @parameter: p_tax_rate_id_tbl
569 * @parameter: p_extract_source_ledger_tbl
570 * @parameter: p_ledger_id_tbl
571 * @parameter: l_detail_tax_line_id_tbl
572 * @parameter: l_taxable_amt_tbl
573 * @parameter: l_taxable_amt_funcl_curr_tbl
574 * @parameter: l_tax_amt_tbl
575 * @parameter: l_tax_amt_funcl_curr_tbl
576 * @parameter: l_ccid_tbl
577 * @parameter: l_acct_date_tbl
578 *
579 **/
580
581 PROCEDURE reset_result_tables(p_detail_tax_line_id_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
582 p_trx_id_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TRX_ID_TBL,
583 p_tax_line_id_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAX_LINE_ID_TBL,
584 p_trx_line_id_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TRX_LINE_ID_TBL,
585 p_tax_dist_id_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL,
586 p_event_class_code_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.EVENT_CLASS_CODE_TBL,
587 p_taxable_amt_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAXABLE_AMT_TBL,
588 p_tax_amt_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_TBL,
589 p_tax_amt_funcl_curr_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL,
590 p_tax_rate_id_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAX_RATE_ID_TBL,
591 p_extract_source_ledger_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.EXTRACT_SOURCE_LEDGER_TBL,
592 p_ledger_id_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.LEDGER_ID_TBL,
593 l_detail_tax_line_id_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
594 l_taxable_amt_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAXABLE_AMT_TBL,
595 l_taxable_amt_funcl_curr_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL,
596 l_tax_amt_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_TBL,
597 l_tax_amt_funcl_curr_tbl IN OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL,
598 l_ccid_tbl IN OUT NOCOPY t_ccid_tbl,
599 l_acct_date_tbl IN OUT NOCOPY t_acct_date_tbl)
600
601 IS
602
603
604 BEGIN
605
606 IF 'Y' = PG_DEBUG THEN
607
608 arp_util_tax.debug('zx_jg_extract_pkg.reset_result_tables()+');
609
610 END IF;
611
612 FOR i in p_detail_tax_line_id_tbl.first..p_detail_tax_line_id_tbl.last LOOP
613
614 p_detail_tax_line_id_tbl.delete(i);
615 p_trx_id_tbl.delete(i);
616 p_tax_line_id_tbl.delete(i);
617 p_trx_line_id_tbl.delete(i);
618 p_tax_dist_id_tbl.delete(i);
619 p_event_class_code_tbl.delete(i);
620 p_tax_dist_id_tbl.delete(i);
621 p_taxable_amt_tbl.delete(i);
622 p_tax_amt_tbl.delete(i);
623 p_tax_amt_funcl_curr_tbl.delete(i);
624 p_tax_rate_id_tbl.delete(i);
625 p_extract_source_ledger_tbl.delete(i);
626 p_ledger_id_tbl.delete(i);
627
628 END LOOP;
629
630 FOR j in l_detail_tax_line_id_tbl.first..l_detail_tax_line_id_tbl.last LOOP
631
632 l_detail_tax_line_id_tbl.delete(j);
633 l_taxable_amt_tbl.delete(j);
634 l_taxable_amt_funcl_curr_tbl.delete(j);
635 l_tax_amt_tbl.delete(j);
636 l_tax_amt_funcl_curr_tbl.delete(j);
637 l_ccid_tbl.delete(j);
638 l_acct_date_tbl.delete(j);
639
640 END LOOP;
641
642 IF 'Y' = PG_DEBUG THEN
643
644 arp_util_tax.debug('zx_jg_extract_pkg.reset_result_tables()-');
645
646 END IF;
647
648 EXCEPTION
649
650 WHEN OTHERS THEN
651
652 IF 'Y' = PG_DEBUG THEN
653
654 arp_util_tax.debug('zx_jg_extract_pkg.reset_result_tables(EXCEPTION)-');
655
656 END IF;
657 RAISE;
658
659 END reset_result_tables;
660
661
662
663 /**
664 * Procedure Name: get_gl_taxable
665 *
666 * @param c_detail_tax_line_id_tbl
667 * @param c_trx_id_tbl
668 * @param c_tax_line_id_tbl
669 * @param c_trx_line_id_tbl
670 * @param p_minimum_accountable_unit
671 * @param p_func_precistion
672 * @param p_func_min_account_unit
673 **/
674
675
676 PROCEDURE get_gl_taxable( c_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
677 c_trx_id_tbl IN ZX_EXTRACT_PKG.TRX_ID_TBL,
678 c_tax_line_id_tbl IN ZX_EXTRACT_PKG.TAX_LINE_ID_TBL,
679 c_trx_line_id_tbl IN ZX_EXTRACT_PKG.TRX_LINE_ID_TBL,
680 t_detail_tax_line_id_tbl OUT NOCOPY ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
681 t_ccid_tbl OUT NOCOPY t_ccid_tbl,
682 t_acct_date_tbl OUT NOCOPY t_acct_date_tbl,
683 t_taxable_amt_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAXABLE_AMT_TBL,
684 t_taxable_amt_funcl_curr_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL,
685 t_tax_amt_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_TBL,
686 t_tax_amt_funcl_curr_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL
687 )
688 IS
689
690 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
691
692 --
693 -- Cursor definition
694 --
695 CURSOR c_gl (c_detail_tax_line_id in NUMBER, c_trx_id in NUMBER, c_tax_line_id IN NUMBER) IS
696 SELECT c_detail_tax_line_id detail_tax_line_id,
697 trx_line.code_combination_id,
698 header.default_effective_date accounting_date,
699 itf.taxable_amt,
700 itf.taxable_amt_funcl_curr,
701 itf.tax_amt,
702 itf.tax_amt_funcl_curr
703 FROM gl_je_headers header,
704 gl_je_lines trx_line,
705 gl_je_lines tax_line,
706 zx_rep_trx_detail_t itf
707 WHERE header.je_header_id = c_trx_id
708 AND tax_line.je_header_id = header.je_header_id
709 AND tax_line.je_line_num = c_tax_line_id
710 AND tax_line.je_header_id = trx_line.je_header_id
711 AND tax_line.tax_group_id = trx_line.tax_group_id
712 AND itf.detail_tax_line_id = c_detail_tax_line_id
713 AND NVL(trx_line.tax_line_flag,'N') <> 'Y'
714 FOR UPDATE;
715
716
717 BEGIN
718
719 IF 'Y' = PG_DEBUG THEN
720
721 arp_util_tax.debug('zx_jg_extract_pkg.get_gl_taxable()+');
722
723 END IF;
724
725 FOR i in c_detail_tax_line_id_tbl.first..c_detail_tax_line_id_tbl.last LOOP
726
727 FOR crow_gl IN c_gl(c_detail_tax_line_id_tbl(i),c_trx_id_tbl(i),c_tax_line_id_tbl(i)) LOOP
728
729 t_detail_tax_line_id_tbl(i) := crow_gl.detail_tax_line_id;
730 t_ccid_tbl(i) := crow_gl.code_combination_id;
731 t_acct_date_tbl(i) := crow_gl.accounting_date;
732 t_taxable_amt_tbl(i) := crow_gl.taxable_amt;
733 t_taxable_amt_funcl_curr_tbl(i) := crow_gl.taxable_amt_funcl_curr;
734 t_tax_amt_tbl(i) := crow_gl.tax_amt;
735 t_tax_amt_funcl_curr_tbl(i) := crow_gl.tax_amt_funcl_curr;
736
737 END LOOP;
738
739 END LOOP;
740
741 IF 'Y' = PG_DEBUG THEN
742
743 arp_util_tax.debug('zx_jg_extract_pkg.get_gl_taxable()-');
744
745 END IF;
746
747 END get_gl_taxable;
748
749
750 /**
751 * Procedure Name: get_ap_taxable
752 *
753 * @param c_detail_tax_line_id_tbl
754 * @param c_trx_id_tbl
755 * @param c_tax_line_id_tbl
756 * @param c_trx_line_id_tbl
757 *
758 **/
759
760
761 PROCEDURE get_ap_taxable( c_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
762 c_trx_id_tbl IN ZX_EXTRACT_PKG.TRX_ID_TBL,
763 c_tax_line_id_tbl IN ZX_EXTRACT_PKG.TAX_LINE_ID_TBL,
764 c_trx_line_id_tbl IN ZX_EXTRACT_PKG.TRX_LINE_ID_TBL,
765 c_tax_dist_id_tbl IN ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL,
766 c_ledger_id_tbl IN ZX_EXTRACT_PKG.ledger_id_tbl,
767 t_detail_tax_line_id_tbl OUT NOCOPY ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
768 t_ccid_tbl OUT NOCOPY t_ccid_tbl,
769 t_acct_date_tbl OUT NOCOPY t_acct_date_tbl,
770 t_taxable_amt_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAXABLE_AMT_TBL,
771 t_taxable_amt_funcl_curr_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL,
772 t_tax_amt_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_TBL,
773 t_tax_amt_funcl_curr_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL
774 )
775 IS
776
777 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
778 j INTEGER;
779 k INTEGER;
780 k_taxable_amt_tbl ZX_EXTRACT_PKG.TAXABLE_AMT_TBL;
781
782 --
783 -- Private Data Type
784 --
785
786 -- TYPE crow_type IS REF CURSOR;
787
788 --
789 -- Cursor definition
790 --
791 /* CURSOR c_ap (c_detail_tax_line_id in NUMBER, c_trx_id in NUMBER, c_trx_line_id IN NUMBER, c_tax_line_id IN NUMBER) IS
792 SELECT c_detail_tax_line_id detail_tax_line_id,
793 ael.code_combination_id,
794 aeh.accounting_date,
795 zx_dist.taxable_amt,
796 zx_dist.taxable_amt_funcl_curr,
797 zx_dist.prd_tax_amt,
798 zx_dist.prd_tax_amt_funcl_curr
799 FROM zx_rec_nrec_dist zx_dist,
800 xla_distribution_links lnk,
801 xla_ae_headers aeh,
802 xla_ae_lines ael,
803 xla_acct_class_assgns acs,
804 xla_assignment_defns_b asd
805 WHERE zx_dist.trx_id = c_trx_id
806 AND zx_dist.tax_line_id = c_tax_line_id
807 AND zx_dist.trx_line_id = c_trx_line_id
808 AND lnk.application_id = 200
809 AND lnk.source_distribution_type = 'AP_INV_DIST'
810 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_id
811 AND lnk.ae_header_id = ael.ae_header_id
812 AND lnk.ae_line_num = ael.ae_line_num
813 AND aeh.ae_header_id = ael.ae_header_id
814 AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
815 AND acs.program_code = asd.program_code
816 --AND asd.assignment_code = 'TAX_REPORTING_LEDGER_ACCTS'
817 AND asd.assignment_code = acs.assignment_code
818 AND asd.enabled_flag = 'Y'
819 AND acs.accounting_class_code = ael.accounting_class_code
820 FOR UPDATE;
821 */
822
823 CURSOR c_ap (c_detail_tax_line_id in NUMBER, c_trx_id in NUMBER, c_trx_line_id IN NUMBER, c_tax_line_id IN NUMBER,
824 c_tax_dist_id NUMBER, c_ledger_id number) IS
825 SELECT c_detail_tax_line_id detail_tax_line_id,
826 ael.code_combination_id,
827 aeh.accounting_date,
828 zx_dist.taxable_amt,
829 zx_dist.taxable_amt_funcl_curr,
830 zx_dist.rec_nrec_tax_amt,
831 zx_dist.rec_nrec_tax_amt_funcl_curr
832 FROM zx_rec_nrec_dist zx_dist,
833 xla_distribution_links lnk,
834 xla_ae_headers aeh,
835 xla_ae_lines ael,
836 xla_acct_class_assgns acs,
837 xla_assignment_defns_b asd
838 WHERE zx_dist.trx_id = c_trx_id
839 AND zx_dist.tax_line_id = c_tax_line_id
840 AND zx_dist.trx_line_id = c_trx_line_id
841 AND lnk.application_id = 200
842 AND lnk.source_distribution_type = 'AP_INV_DIST'
843 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
844 AND zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
845 AND lnk.ae_header_id = ael.ae_header_id
846 AND lnk.ae_line_num = ael.ae_line_num
847 AND aeh.ae_header_id = ael.ae_header_id
848 AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
849 AND acs.program_code = asd.program_code
850 --AND asd.assignment_code = 'TAX_REPORTING_LEDGER_ACCTS'
851 AND asd.assignment_code = acs.assignment_code
852 AND asd.enabled_flag = 'Y'
853 AND ael.ledger_id = c_ledger_id
854 AND acs.accounting_class_code = ael.accounting_class_code
855 FOR UPDATE;
856
857 BEGIN
858
859 IF (g_level_procedure >= g_current_runtime_level ) THEN
860 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable()+',
861 'get_ap_taxable : ');
862 END IF;
863
864
865 j := 0;
866 k:=0;
867
868 FOR i in c_detail_tax_line_id_tbl.first..c_detail_tax_line_id_tbl.last LOOP
869
870 IF (g_level_procedure >= g_current_runtime_level ) THEN
871 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
872 'c_detail_tax_line_id_tbl : '|| to_char(c_detail_tax_line_id_tbl(i)));
873 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
874 'c_trx_line_id_tbl(i) : '|| to_char(c_trx_line_id_tbl(i)));
875 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
876 'c_trx_id_tbl(i) : '|| to_char(c_trx_id_tbl(i)));
877 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
878 'c_tax_line_id_tbl(i) : '|| to_char(c_tax_line_id_tbl(i)));
879 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
880 'c_tax_dist_id_tbl(i) : '|| to_char(c_tax_dist_id_tbl(i)));
881 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
882 'c_ledger_id_tbl(i) : '|| to_char(c_ledger_id_tbl(i)));
883 END IF;
884
885 FOR crow_ap IN c_ap(c_detail_tax_line_id_tbl(i), c_trx_id_tbl(i), c_trx_line_id_tbl(i),
886 c_tax_line_id_tbl(i), c_tax_dist_id_tbl(i), c_ledger_id_tbl(i))
887
888 LOOP
889 IF (g_level_procedure >= g_current_runtime_level ) THEN
890 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
891 't_detail_tax_line_id_tbl(j): '||to_char(crow_ap.detail_tax_line_id));
892 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
893 't_ccid_tbl(j): '||to_char(crow_ap.code_combination_id));
894 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
895 'crow_ap.taxable_amt: '||to_char(crow_ap.taxable_amt));
896 END IF;
897
898 k:= to_number(to_char(c_trx_id_tbl(i))||to_char(c_trx_line_id_tbl(i)));
899
900
901 IF (g_level_procedure >= g_current_runtime_level ) THEN
902 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
903 'K : '||to_char(k));
904 END IF;
905
906 j := j+1;
907 t_detail_tax_line_id_tbl(j) := crow_ap.detail_tax_line_id;
908 t_ccid_tbl(j) := crow_ap.code_combination_id;
909 t_acct_date_tbl(j) := crow_ap.accounting_date;
910 -- t_taxable_amt_tbl(j) := crow_ap.taxable_amt;
911 -- t_taxable_amt_funcl_curr_tbl(j) := crow_ap.taxable_amt_funcl_curr;
912 t_tax_amt_tbl(j) := crow_ap.rec_nrec_tax_amt;
913 t_tax_amt_funcl_curr_tbl(j) := crow_ap.rec_nrec_tax_amt_funcl_curr;
914
915 IF k_taxable_amt_tbl.EXISTS(k) THEN
916 t_taxable_amt_tbl(j) := 0;
917 k_taxable_amt_tbl(k) := 0;
918 t_taxable_amt_funcl_curr_tbl(j) := 0;
919
920 IF (g_level_procedure >= g_current_runtime_level ) THEN
921 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
922 'k value test IF: ');
923 END IF;
924 ELSE
925 t_taxable_amt_tbl(j) :=crow_ap.taxable_amt;
926 k_taxable_amt_tbl(k) :=crow_ap.taxable_amt;
927 t_taxable_amt_funcl_curr_tbl(j) := crow_ap.taxable_amt_funcl_curr;
928
929 IF (g_level_procedure >= g_current_runtime_level ) THEN
930 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
931 'k value test else: ');
932 END IF;
933
934 END IF;
935
936 IF (g_level_procedure >= g_current_runtime_level ) THEN
937 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
938 't_detail_tax_line_id_tbl(j): '||to_char(t_detail_tax_line_id_tbl(j)));
939 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
940 't_ccid_tbl(j): '||to_char(t_ccid_tbl(j)));
941 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable',
942 't_taxable_amt_tbl(j): '||to_char(t_taxable_amt_tbl(j)));
943 END IF;
944
945 END LOOP;
946
947 END LOOP;
948
949
950 IF (g_level_procedure >= g_current_runtime_level ) THEN
951 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ap_taxable()-',
952 'get_ap_taxable : ');
953 END IF;
954
955
956
957 END get_ap_taxable;
958
959
960 /**
961 * Procedure Name: get_ar_taxable
962 *
963 * This procedure gets taxable amount from AR trx tables and
964 * also getst accunting ccid from XLA table.
965 *
966 * @param c_event_class_code_tbl
967 * @param c_trx_id_tbl
968 * @param c_tax_line_id_tbl
969 * @param c_taxable_amt_tbl
970 * @param c_tax_rate_id_tbl
971 * @param p_sob_type
972 * @param p_func_precision
973 * @param p_func_min_account_unit
974 *
975 **/
976
977 PROCEDURE get_ar_taxable(c_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
978 c_event_class_code_tbl IN ZX_EXTRACT_PKG.EVENT_CLASS_CODE_TBL,
979 c_trx_id_tbl IN ZX_EXTRACT_PKG.TRX_ID_TBL,
980 c_tax_line_id_tbl IN ZX_EXTRACT_PKG.TAX_LINE_ID_TBL,
981 c_trx_line_id_tbl IN ZX_EXTRACT_PKG.TRX_LINE_ID_TBL,
982 c_taxable_amt_tbl IN ZX_EXTRACT_PKG.TAXABLE_AMT_TBL,
983 c_tax_amt_tbl IN ZX_EXTRACT_PKG.TAX_AMT_TBL,
984 c_tax_amt_funcl_curr_tbl IN ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL,
985 c_tax_rate_id_tbl IN ZX_EXTRACT_PKG.TAX_RATE_ID_TBL,
986 p_sob_type IN VARCHAR2,
987 p_func_precision IN NUMBER,
988 p_func_min_account_unit IN NUMBER,
989 t_detail_tax_line_id_tbl OUT NOCOPY ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
990 t_ccid_tbl OUT NOCOPY t_ccid_tbl,
991 t_acct_date_tbl OUT NOCOPY t_acct_date_tbl,
992 t_taxable_amt_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAXABLE_AMT_TBL,
993 t_taxable_amt_funcl_curr_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL,
994 t_tax_amt_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_TBL,
995 t_tax_amt_funcl_curr_tbl OUT NOCOPY ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL) IS
996
997
998 CURSOR c_ar_inv (c_detail_tax_line_id in NUMBER) IS
999 SELECT detail_tax_line_id,
1000 actg_line_ccid code_combination_id,
1001 accounting_date ,
1002 0 taxable_amt, -- -1*zx_dist.taxable_amt taxable_amt,
1003 0 taxable_amt_funcl_curr, -- -1*zx_dist.taxable_amt_funcl_curr taxable_amt_funcl_curr,
1004 0 tax_amt, -- -1*zx_dist.prd_tax_amt tax_amt,
1005 0 tax_amt_funcl_curr -- -1*zx_dist.prd_tax_amt_funcl_curr tax_amt_funcl_curr
1006 FROM zx_rep_actg_ext_t
1007 WHERE detail_tax_line_id = c_detail_tax_line_id;
1008
1009 CURSOR c_ar_inv1(c_detail_tax_line_id in NUMBER,
1010 c_trx_id in NUMBER,
1011 c_trx_line_id IN NUMBER,
1012 c_tax_line_id IN NUMBER
1013 ) IS
1014
1015 SELECT c_detail_tax_line_id detail_tax_line_id,
1016 ael.code_combination_id,
1017 aeh.accounting_date,
1018 0 taxable_amt, -- -1*zx_dist.taxable_amt taxable_amt,
1019 0 taxable_amt_funcl_curr, -- -1*zx_dist.taxable_amt_funcl_curr taxable_amt_funcl_curr,
1020 0 tax_amt, -- -1*zx_dist.prd_tax_amt tax_amt,
1021 0 tax_amt_funcl_curr -- -1*zx_dist.prd_tax_amt_funcl_curr tax_amt_funcl_curr
1022 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1023 xla_distribution_links lnk,
1024 xla_ae_headers aeh,
1025 xla_ae_lines ael,
1026 xla_acct_class_assgns acs,
1027 xla_assignment_defns_b asd
1028 WHERE gl_dist.customer_trx_id = c_trx_id
1029 AND gl_dist.customer_trx_line_id = c_trx_line_id
1030 AND gl_dist.account_class = 'REV'
1031 AND lnk.application_id = 222
1032 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1033 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1034 AND lnk.ae_header_id = ael.ae_header_id
1035 AND lnk.ae_line_num = ael.ae_line_num
1036 AND aeh.ae_header_id = ael.ae_header_id
1037 AND acs.program_code = 'TAX_REPORTING_LEDGER_SALES'
1038 AND acs.program_code = asd.program_code
1039 AND asd.assignment_code = acs.assignment_code
1040 AND asd.enabled_flag = 'Y'
1041 AND acs.accounting_class_code = ael.accounting_class_code
1042 FOR UPDATE;
1043
1044 /* SELECT c_detail_tax_line_id detail_tax_line_id,
1045 ael.code_combination_id,
1046 aeh.accounting_date,
1047 -1*zx_dist.taxable_amt taxable_amt,
1048 -1*zx_dist.taxable_amt_funcl_curr taxable_amt_funcl_curr,
1049 -1*zx_dist.prd_tax_amt tax_amt,
1050 -1*zx_dist.prd_tax_amt_funcl_curr tax_amt_funcl_curr
1051 FROM zx_rec_nrec_dist zx_dist,
1052 xla_distribution_links lnk,
1053 xla_ae_headers aeh,
1054 xla_ae_lines ael,
1055 xla_acct_class_assgns acs,
1056 xla_assignment_defns_b asd
1057 WHERE zx_dist.trx_id = c_trx_id
1058 AND zx_dist.tax_line_id = c_tax_line_id
1059 AND zx_dist.trx_line_id = c_trx_line_id
1060 AND lnk.application_id = 222
1061 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS'
1062 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_id
1063 AND lnk.ae_header_id = ael.ae_header_id
1064 AND lnk.ae_line_num = ael.ae_line_num
1065 AND aeh.ae_header_id = ael.ae_header_id
1066 AND acs.program_code = 'TAX_REPORTING_LEDGER'
1067 AND acs.program_code = asd.program_code
1068 AND asd.assignment_code = 'TAX_REPORTING_LEDGER_ACCTS'
1069 AND asd.assignment_code = acs.assignment_code
1070 AND asd.enabled_flag = 'Y'
1071 AND acs.accounting_class_code = ael.accounting_class_code
1072 FOR UPDATE; */
1073
1074 TYPE crow_type IS REF CURSOR;
1075 c_row crow_type;
1076
1077 l_cur_aradj VARCHAR2(10000);
1078 l_cur_armisc VARCHAR2(10000);
1079 l_cur_arra VARCHAR2(10000);
1080
1081 l_ar_adjustments VARCHAR2(30);
1082 l_ar_cash_receipts VARCHAR2(30);
1083 l_ar_distributions VARCHAR2(30);
1084 l_ar_receivable_applications VARCHAR2(30);
1085 l_ar_misc_cash_distributions VARCHAR2(30);
1086 l_ra_customer_trx VARCHAR2(30);
1087 l_ra_customer_trx_lines VARCHAR2(30);
1088 l_ra_cust_trx_line_gl_dist VARCHAR2(30);
1089
1090 l_percent_tbl numtab;
1091 l_tax_amt_tbl ZX_EXTRACT_PKG.TAX_AMT_TBL;
1092 l_tax_amt_funcl_curr_tbl ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL;
1093 l_precision_tbl numtab;
1094 l_minimum_accountable_unit_tbl numtab;
1095
1096 j INTEGER;
1097 l_first_line INTEGER;
1098
1099
1100 BEGIN
1101
1102 IF (g_level_procedure >= g_current_runtime_level ) THEN
1103 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable()+',
1104 'get_ar_taxable : ');
1105 END IF;
1106
1107
1108 /* --------------------------------------------------------- *
1109 * Case1. Set of Books is Reporting Book *
1110 * --------------------------------------------------------- */
1111
1112 IF p_sob_type = 'R' THEN
1113
1114 l_ar_adjustments := 'ar_adjustments_mrc_v';
1115 l_ar_cash_receipts := 'ar_cash_receipts_mrc_v';
1116 l_ar_distributions := 'ar_distributions_mrc_v';
1117 l_ar_receivable_applications := 'ar_receivable_apps_mrc_v';
1118 l_ar_misc_cash_distributions := 'ar_misc_cash_dists_mrc_v';
1119 l_ra_cust_trx_line_gl_dist := 'ra_trx_line_gl_dist_mrc_v';
1120 l_ra_customer_trx := 'ra_customer_trx_mrc_v';
1121 l_ra_customer_trx_lines := 'ra_cust_trx_ln_mrc_v';
1122
1123 /* --------------------------------------------------------- *
1124 * Case2. Set of Books is Primary Book or Not MRC Book *
1125 * --------------------------------------------------------- */
1126
1127 ELSIF p_sob_type <> 'R' AND p_sob_type IS NOT NULL THEN
1128
1129 l_ar_adjustments := 'ar_adjustments_all';
1130 l_ar_cash_receipts := 'ar_cash_receipts_all';
1131 l_ar_distributions := 'ar_distributions_all';
1132 l_ar_receivable_applications := 'ar_receivable_applications';
1133 l_ar_misc_cash_distributions := 'ar_misc_cash_distributions';
1134 l_ra_cust_trx_line_gl_dist := 'ra_cust_trx_line_gl_dist';
1135 l_ra_customer_trx := 'ra_customer_trx_all';
1136 l_ra_customer_trx_lines := 'ra_customer_trx_lines';
1137
1138 ELSE
1139
1140 IF 'Y' = PG_DEBUG THEN
1141
1142 arp_util_tax.debug('zx_jg_extract_pkg.get_ar_taxable:Unable to specify if the Book is Reporting Book or Not');
1143
1144 END IF;
1145
1146 END IF;
1147
1148 l_ar_adjustments := 'ar_adjustments_all';
1149 l_ar_cash_receipts := 'ar_cash_receipts_all';
1150 l_ar_distributions := 'ar_distributions_all';
1151
1152
1153 /* ----------------------------------------------------------------- *
1154 * Defined following 3 new cursors which use Dynamic SQL *
1155 * *
1156 * l_cur_aradj -- replacement for CURSOR aradj *
1157 * l_cur_armisc -- replacement for CURSOR armis *
1158 * l_cur_arra -- replacement for CURSOR arra *
1159 * *
1160 * ----------------------------------------------------------------- */
1161
1162 l_cur_aradj := 'SELECT
1163 :c_detail_tax_line_id,
1164 ael.code_combination_id ccid,
1165 Decode(:c_taxable_total, 0, 0,
1166 (Nvl(adjlndist.amount_dr,0)+Nvl(-1*adjlndist.amount_cr,0))/:c_taxable_total) percent,
1167 Nvl(adjlndist.amount_dr,0)+Nvl(-1*adjlndist.amount_cr,0) taxable_amount,
1168 Nvl(adjlndist.acctd_amount_dr,0)+Nvl(-1*adjlndist.acctd_amount_cr,0) acctd_taxable_amount,
1169 curr.precision,
1170 curr.minimum_accountable_unit,
1171 aeh.accounting_date
1172 FROM
1173 '|| l_ar_distributions ||' adjlndist,
1174 '|| l_ar_distributions ||' adjtxdist,
1175 '|| l_ar_adjustments ||' adj,
1176 '|| l_ra_customer_trx ||' trx,
1177 fnd_currencies curr,
1178 xla_distribution_links lnk,
1179 xla_ae_headers aeh,
1180 xla_ae_lines ael,
1181 xla_acct_class_assgns acs,
1182 xla_assignment_defns_b asd
1183 WHERE
1184 adj.adjustment_id = :c_trx_id AND
1185 trx.customer_trx_id = adj.customer_trx_id AND
1186 adjlndist.source_id = adj.adjustment_id AND
1187 adjtxdist.source_id = adj.adjustment_id AND
1188 adjlndist.source_table = ''ADJ'' AND
1189 adjtxdist.source_table = ''ADJ'' AND
1190 adjlndist.source_type IN (''ADJ'', ''FINCHRG'') AND
1191 adjtxdist.source_type = ''TAX'' AND
1192 adjlndist.tax_link_id = adjtxdist.tax_link_id AND
1193 adjtxdist.tax_code_id = :c_tax_rate_id AND
1194 lnk.application_id = 222 AND
1195 lnk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'' AND
1196 lnk.source_distribution_id_num_1 = adjlndist.line_id AND
1197 lnk.ae_header_id = ael.ae_header_id AND
1198 lnk.ae_line_num = ael.ae_line_num AND
1199 aeh.ae_header_id = ael.ae_header_id AND
1200 trx.invoice_currency_code = curr.currency_code AND
1201 acs.program_code = ''TAX_REPORTING_LEDGER_SALES'' AND
1202 acs.program_code = asd.program_code AND
1203 asd.assignment_code = acs.assignment_code AND
1204 asd.enabled_flag = ''Y'' AND
1205 acs.accounting_class_code = ael.accounting_class_code';
1206
1207 /* l_cur_aradj := 'SELECT
1208 :c_detail_tax_line_id,
1209 ael.code_combination_id ccid,
1210 Decode(:c_taxable_total, 0, 0,
1211 (Nvl(adjlndist.amount_dr,0)+Nvl(-1*adjlndist.amount_cr,0))/:c_taxable_total) percent,
1212 Nvl(adjlndist.amount_dr,0)+Nvl(-1*adjlndist.amount_cr,0) taxable_amount,
1213 Nvl(adjlndist.acctd_amount_dr,0)+Nvl(-1*adjlndist.acctd_amount_cr,0) acctd_taxable_amount,
1214 curr.precision,
1215 curr.minimum_accountable_unit,
1216 aeh.accounting_date
1217 FROM
1218 '|| l_ar_distributions ||' adjlndist,
1219 '|| l_ar_distributions ||' adjtxdist,
1220 '|| l_ar_adjustments ||' adj,
1221 '|| l_ra_customer_trx ||' trx,
1222 fnd_currencies curr,
1223 xla_distribution_links lnk,
1224 xla_ae_headers aeh,
1225 xla_ae_lines ael,
1226 xla_acct_class_assgns acs,
1227 xla_assignment_defns_b asd
1228 WHERE
1229 adj.adjustment_id = :c_trx_id AND
1230 trx.customer_trx_id = adj.customer_trx_id AND
1231 adjlndist.source_id = adj.adjustment_id AND
1232 adjtxdist.source_id = adj.adjustment_id AND
1233 adjlndist.source_table = ''ADJ'' AND
1234 adjtxdist.source_table = ''ADJ'' AND
1235 adjlndist.source_type IN (''ADJ'', ''FINCHRG'') AND
1236 adjtxdist.source_type = ''TAX'' AND
1237 adjlndist.tax_link_id = adjtxdist.tax_link_id AND
1238 adjtxdist.tax_code_id = :c_tax_rate_id AND
1239 lnk.application_id = 222 AND
1240 lnk.source_distribution_type = ''AR_DISTRIBUTIONS'' AND
1241 lnk.source_distribution_id_num_1 = zx_dist.trx_line_id AND
1242 lnk.ae_header_id = ael.ae_header_id AND
1243 lnk.ae_line_num = ael.ae_line_num AND
1244 aeh.ae_header_id = ael.ae_header_id AND
1245 trx.invoice_currency_code = curr.currency_code AND
1246 acs.program_code = ''TAX_REPORTING_LEDGER'' AND
1247 acs.program_code = asd.program_code AND
1248 asd.assignment_code = ''TAX_REPORTING_LEDGER_ACCTS'' AND
1249 asd.assignment_code = acs.assignment_code AND
1250 asd.enabled_flag = ''Y'' AND
1251 acs.accounting_class_code = ael.accounting_class_code';
1252 */
1253
1254 l_cur_armisc := 'SELECT
1255 :c_detail_tax_line_id,
1256 ael.code_combination_id ccid,
1257 Decode(:c_taxable_total, 0, 0, (Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0))/:c_taxable_total) percent,
1258 Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0) taxable_amount,
1259 Nvl(d.acctd_amount_dr,0)+Nvl(-1*d.acctd_amount_cr,0) acctd_taxable_amount,
1260 curr.precision,
1261 curr.minimum_accountable_unit,
1262 aeh.accounting_date
1263 FROM
1264 '|| l_ar_cash_receipts ||' cr,
1265 '|| l_ar_misc_cash_distributions ||' mcd,
1266 '|| l_ar_distributions ||' d,
1267 xla_distribution_links lnk,
1268 xla_ae_headers aeh,
1269 xla_ae_lines ael,
1270 fnd_currencies curr,
1271 xla_acct_class_assgns acs,
1272 xla_assignment_defns_b asd
1273 WHERE
1274 cr.cash_receipt_id = :c_trx_id AND
1275 cr.cash_receipt_id = mcd.cash_receipt_id AND
1276 d.source_table = ''MCD'' AND
1277 d.source_id = mcd.misc_cash_distribution_id AND
1278 d.source_type <> ''TAX'' AND
1279 lnk.application_id = 222 AND
1280 lnk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'' AND
1281 lnk.source_distribution_id_num_1 = d.line_id AND
1282 lnk.ae_header_id = ael.ae_header_id AND
1283 lnk.ae_line_num = ael.ae_line_num AND
1284 aeh.ae_header_id = ael.ae_header_id AND
1285 cr.currency_code = curr.currency_code AND
1286 acs.program_code = ''TAX_REPORTING_LEDGER_SALES'' AND
1287 acs.program_code = asd.program_code AND
1288 asd.assignment_code = acs.assignment_code AND
1289 asd.enabled_flag = ''Y'' AND
1290 acs.accounting_class_code = ael.accounting_class_code';
1291
1292 /*l_cur_armisc := 'SELECT
1293 :c_detail_tax_line_id,
1294 ael.code_combination_id ccid,
1295 Decode(:c_taxable_total, 0, 0, (Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0))/:c_taxable_total) percent,
1296 Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0) taxable_amount,
1297 Nvl(d.acctd_amount_dr,0)+Nvl(-1*d.acctd_amount_cr,0) acctd_taxable_amount,
1298 curr.precision,
1299 curr.minimum_accountable_unit,
1300 aeh.accounting_date
1301 FROM
1302 '|| l_ar_cash_receipts ||' cr,
1303 '|| l_ar_misc_cash_distributions ||' mcd,
1304 '|| l_ar_distributions ||' d,
1305 xla_distribution_links lnk,
1306 xla_ae_headers aeh,
1307 xla_ae_lines ael,
1308 fnd_currencies curr,
1309 xla_acct_class_assgns acs,
1310 xla_assignment_defns_b asd
1311 WHERE
1312 cr.cash_receipt_id = :c_trx_id AND
1313 cr.cash_receipt_id = mcd.cash_receipt_id AND
1314 d.source_table = ''MCD'' AND
1315 d.source_id = mcd.misc_cash_distribution_id AND
1316 d.source_type <> ''TAX'' AND
1317 lnk.application_id = 222 AND
1318 lnk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'' AND
1319 lnk.source_distribution_id_num_1 = zx_dist.trx_line_id AND
1320 lnk.ae_header_id = ael.ae_header_id AND
1321 lnk.ae_line_num = ael.ae_line_num AND
1322 aeh.ae_header_id = ael.ae_header_id AND
1323 cr.currency_code = curr.currency_code AND
1324 acs.program_code = ''TAX_REPORTING_LEDGER_SALES'' AND
1325 acs.program_code = asd.program_code AND
1326 asd.assignment_code = ''TAX_REPORTING_LEDGER_ACCTS'' AND
1327 asd.assignment_code = acs.assignment_code AND
1328 asd.enabled_flag = ''Y'' AND
1329 acs.accounting_class_code = ael.accounting_class_code';
1330 */
1331
1332 l_cur_arra := 'SELECT
1333 :c_detail_tax_line_id,
1334 ael.code_combination_id ccid,
1335 Decode(c_taxable_total, 0, 0, (Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0))/:c_taxable_total) percent,
1336 Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0) taxable_amount,
1337 Nvl(d.acctd_amount_dr,0)+Nvl(-1*d.acctd_amount_cr,0) acctd_taxable_amount,
1338 curr.precision,
1339 curr.minimum_accountable_unit,
1340 aeh.accounting_date
1341 FROM
1342 '|| l_ar_distributions ||' dtax,
1343 '|| l_ar_distributions ||' d,
1344 '|| l_ar_receivable_applications ||' ra,
1345 '|| l_ar_cash_receipts ||' cr,
1346 fnd_currencies curr,
1347 xla_distribution_links lnk,
1348 xla_ae_headers aeh,
1349 xla_ae_lines ael,
1350 xla_acct_class_assgns acs,
1351 xla_assignment_defns_b asd
1352 WHERE
1353 -- dtax.line_id = :c_acctg_dist_id AND
1354 cr.cash_receipt_id = :c_trx_id AND
1355 dtax.source_table = ''RA'' AND
1356 d.source_table = ''RA'' AND
1357 d.source_id = dtax.source_id AND
1358 d.source_type <> ''TAX'' and
1359 dtax.source_type = ''TAX'' and
1360 (d.tax_link_id = -1 OR Nvl(d.tax_link_id,0) = Nvl(dtax.tax_link_id,0)) AND
1361 ra.receivable_application_id = dtax.source_id AND
1362 cr.cash_receipt_id = ra.cash_receipt_id AND
1363 lnk.application_id = 222 AND
1364 lnk.source_distribution_type = ''AR_DISTRIBUTIONS'' AND
1365 lnk.source_distribution_id_num_1 = zx_dist.trx_line_id AND
1366 lnk.ae_header_id = ael.ae_header_id AND
1367 lnk.ae_line_num = ael.ae_line_num AND
1368 aeh.ae_header_id = ael.ae_header_id AND
1369 cr.currency_code = curr.currency_code AND
1370 acs.program_code = ''TAX_REPORTING_LEDGER'' AND
1371 acs.program_code = asd.program_code AND
1372 asd.assignment_code = ''TAX_REPORTING_LEDGER_ACCTS'' AND
1373 asd.assignment_code = acs.assignment_code AND
1374 asd.enabled_flag = ''Y'' AND
1375 acs.accounting_class_code = ael.accounting_class_code';
1376
1377
1378 j := 0;
1379
1380 FOR i in c_event_class_code_tbl.first..c_event_class_code_tbl.last LOOP
1381
1382 IF c_event_class_code_tbl(i) IN ('INVOICE', 'CREDIT_MEMO', 'DEBIT_MEMO') THEN
1383
1384 IF (g_level_procedure >= g_current_runtime_level ) THEN
1385 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable()+',
1386 'Getting INV/CM/DM : ');
1387 END IF;
1388
1389
1390 /*FOR c_ar_inv_row IN c_ar_inv(c_detail_tax_line_id_tbl(i),
1391 c_trx_id_tbl(i),
1392 c_trx_line_id_tbl(i),
1393 c_tax_line_id_tbl(i)) */
1394 FOR c_ar_inv_row IN c_ar_inv(c_detail_tax_line_id_tbl(i))
1395 LOOP
1396
1397 j := j+1;
1398 t_detail_tax_line_id_tbl(j) := c_ar_inv_row.detail_tax_line_id;
1399 t_ccid_tbl(j) := c_ar_inv_row.code_combination_id;
1400 t_acct_date_tbl(j) := c_ar_inv_row.accounting_date;
1401 t_taxable_amt_tbl(j) := c_taxable_amt_tbl(i);
1402 t_taxable_amt_funcl_curr_tbl(j) := c_taxable_amt_tbl(i);
1403 t_tax_amt_tbl(j) := c_tax_amt_tbl(i);
1404 t_tax_amt_funcl_curr_tbl(j) := c_tax_amt_funcl_curr_tbl(i);
1405 --t_taxable_amt_tbl(j) := c_ar_inv_row.taxable_amt;
1406 --t_taxable_amt_funcl_curr_tbl(j) := c_ar_inv_row.taxable_amt_funcl_curr;
1407 --t_tax_amt_tbl(j) := c_ar_inv_row.tax_amt;
1408 --t_tax_amt_funcl_curr_tbl(j) := c_ar_inv_row.tax_amt_funcl_curr;
1409
1410 IF (g_level_procedure >= g_current_runtime_level ) THEN
1411 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1412 't_detail_tax_line_id_tbl(j): '||to_char(t_detail_tax_line_id_tbl(j)));
1413 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1414 't_ccid_tbl(j): '||to_char(t_ccid_tbl(j)));
1415 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1416 't_taxable_amt_tbl(j): '||to_char(t_taxable_amt_tbl(j)));
1417 END IF;
1418
1419
1420 END LOOP;
1421
1422 ELSIF c_event_class_code_tbl(i) IN ('ADJ') THEN
1423
1424 IF (g_level_procedure >= g_current_runtime_level ) THEN
1425 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1426 'Getting ADJ:' );
1427 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1428 'c_trx_id_tbl(i):'||to_char(c_trx_id_tbl(i)));
1429 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1430 'c_tax_rate_id_tbl(i):'||to_char(c_tax_rate_id_tbl(i)));
1431
1432 END IF;
1433
1434 l_first_line := j+1;
1435
1436 OPEN c_row FOR l_cur_aradj USING c_detail_tax_line_id_tbl(i),
1437 c_taxable_amt_tbl(i),
1438 c_taxable_amt_tbl(i),
1439 c_trx_id_tbl(i),
1440 c_tax_rate_id_tbl(i);
1441
1442 LOOP
1443
1444 FETCH c_row INTO t_detail_tax_line_id_tbl(j+1),
1445 t_ccid_tbl(j+1),
1446 l_percent_tbl(j+1),
1447 t_taxable_amt_tbl(j+1),
1448 t_taxable_amt_funcl_curr_tbl(j+1),
1449 l_precision_tbl(j+1),
1450 l_minimum_accountable_unit_tbl(j+1),
1451 t_acct_date_tbl(j+1);
1452
1453 EXIT WHEN c_row%NOTFOUND;
1454 IF (g_level_procedure >= g_current_runtime_level ) THEN
1455 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1456 'l_cur_aradj : t_detail_tax_line_id_tbl(j): '||to_char(t_detail_tax_line_id_tbl(j+1)));
1457 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1458 'l_cur_aradj : t_ccid_tbl(j): '||to_char(t_ccid_tbl(j+1)));
1459 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1460 'l_cur_aradj : t_taxable_amt_tbl(j): '||to_char(t_taxable_amt_tbl(j+1)));
1461 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1462 'l_cur_aradj : t_taxable_amt_tbl(j): '||to_char(j));
1463 END IF;
1464
1465
1466
1467 j := j+1;
1468
1469 END LOOP;
1470
1471 CLOSE c_row;
1472
1473 IF (g_level_procedure >= g_current_runtime_level ) THEN
1474 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1475 'l_cur_aradj : prorate_tax call: ');
1476 END IF;
1477
1478 prorate_tax(-1*c_tax_amt_tbl(i),
1479 -1*c_tax_amt_funcl_curr_tbl(i),
1480 l_percent_tbl,
1481 t_tax_amt_tbl,
1482 t_tax_amt_funcl_curr_tbl,
1483 l_precision_tbl,
1484 l_minimum_accountable_unit_tbl,
1485 p_func_precision,
1486 p_func_min_account_unit,
1487 l_first_line,
1488 j
1489 );
1490
1491 ELSIF c_event_class_code_tbl(i) IN ('MISC_CASH_RECEIPT') THEN
1492
1493 IF 'Y' = PG_DEBUG THEN
1494
1495 arp_util_tax.debug('Getting MCR');
1496
1497 END IF;
1498
1499 l_first_line := j+1;
1500
1501 OPEN c_row FOR l_cur_armisc USING c_detail_tax_line_id_tbl(i),
1502 c_taxable_amt_tbl(i),
1503 c_taxable_amt_tbl(i),
1504 c_trx_id_tbl(i);
1505 LOOP
1506
1507 FETCH c_row INTO t_detail_tax_line_id_tbl(j+1),
1508 t_ccid_tbl(j+1),
1509 l_percent_tbl(j+1),
1510 t_taxable_amt_tbl(j+1),
1511 t_taxable_amt_funcl_curr_tbl(j+1),
1512 l_precision_tbl(j+1),
1513 l_minimum_accountable_unit_tbl(j+1),
1514 t_acct_date_tbl(j+1);
1515
1516 EXIT WHEN c_row%NOTFOUND;
1517 j := j+1;
1518
1519 END LOOP;
1520
1521 CLOSE c_row;
1522
1523 prorate_tax(-1*c_tax_amt_tbl(i),
1524 -1*c_tax_amt_funcl_curr_tbl(i),
1525 l_percent_tbl,
1526 t_tax_amt_tbl,
1527 t_tax_amt_funcl_curr_tbl,
1528 l_precision_tbl,
1529 l_minimum_accountable_unit_tbl,
1530 p_func_precision,
1531 p_func_min_account_unit,
1532 l_first_line,
1533 j
1534 );
1535
1536 ELSIF c_event_class_code_tbl(i) IN ('EDISC', 'UNEDISC', 'APP') THEN
1537
1538 IF 'Y' = PG_DEBUG THEN
1539
1540 arp_util_tax.debug('Getting DISC/APP');
1541
1542 END IF;
1543
1544 l_first_line := j+1;
1545
1546 OPEN c_row FOR l_cur_arra USING c_detail_tax_line_id_tbl(i),
1547 c_taxable_amt_tbl(i),
1548 c_taxable_amt_tbl(i),
1549 c_trx_id_tbl(i);
1550
1551 LOOP
1552
1553 FETCH c_row INTO t_detail_tax_line_id_tbl(j+1),
1554 t_ccid_tbl(j+1),
1555 l_percent_tbl(j+1),
1556 t_taxable_amt_tbl(j+1),
1557 t_taxable_amt_funcl_curr_tbl(j+1),
1558 l_precision_tbl(j+1),
1559 l_minimum_accountable_unit_tbl(j+1),
1560 t_acct_date_tbl(j+1);
1561
1562 EXIT WHEN c_row%NOTFOUND;
1563 j := j+1;
1564
1565 END LOOP;
1566 CLOSE c_row;
1567
1568 prorate_tax(-1*c_tax_amt_tbl(i),
1569 -1*c_tax_amt_funcl_curr_tbl(i),
1570 l_percent_tbl,
1571 t_tax_amt_tbl,
1572 t_tax_amt_funcl_curr_tbl,
1573 l_precision_tbl,
1574 l_minimum_accountable_unit_tbl,
1575 p_func_precision,
1576 p_func_min_account_unit,
1577 l_first_line,
1578 j
1579 );
1580
1581 ELSE
1582 IF 'Y' = PG_DEBUG THEN
1583
1584 arp_util_tax.debug('Unknown Trx_Class_Code!');
1585
1586 END IF;
1587
1588 app_exception.raise_exception;
1589
1590 END IF;
1591 IF (g_level_procedure >= g_current_runtime_level ) THEN
1592 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable',
1593 'dtl cursor loop :');
1594 END IF;
1595 END LOOP;
1596
1597 IF (g_level_procedure >= g_current_runtime_level ) THEN
1598 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_ar_taxable.END',
1599 'zx_jg_extract.get_ar_taxable()-');
1600 END IF;
1601
1602 END get_ar_taxable;
1603
1604
1605
1606 /**
1607 * procedure Name: get_taxable
1608 *
1609 * Wrapper procedure to get prorated taxa amount per taxable accont
1610 * and accounting info.
1611 *
1612 * @param p_trx_global_variabl_variables_rec
1613 *
1614 **/
1615
1616 PROCEDURE get_taxable(p_trl_global_variables_rec IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
1617
1618 IS
1619
1620 --
1621 -- Private Parameters
1622 --
1623 p_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
1624 p_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
1625 p_tax_line_id_tbl ZX_EXTRACT_PKG.TAX_LINE_ID_TBL;
1626 p_trx_line_id_tbl ZX_EXTRACT_PKG.TRX_LINE_ID_TBL;
1627 p_tax_dist_id_tbl ZX_EXTRACT_PKG.actg_source_id_tbl;
1628 p_event_class_code_tbl ZX_EXTRACT_PKG.EVENT_CLASS_CODE_TBL;
1629 p_taxable_amt_tbl ZX_EXTRACT_PKG.TAXABLE_AMT_TBL;
1630 p_tax_amt_tbl ZX_EXTRACT_PKG.TAX_AMT_TBL;
1631 p_tax_amt_funcl_curr_tbl ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL;
1632 p_tax_rate_id_tbl ZX_EXTRACT_PKG.TAX_RATE_ID_TBL;
1633 p_extract_source_ledger_tbl ZX_EXTRACT_PKG.EXTRACT_SOURCE_LEDGER_TBL;
1634 p_ledger_id_tbl ZX_EXTRACT_PKG.LEDGER_ID_TBL;
1635
1636
1637 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
1638 l_ccid_tbl t_ccid_tbl;
1639 l_acct_date_tbl t_acct_date_tbl;
1640 l_percent_tbl numtab;
1641 l_taxable_amt_tbl ZX_EXTRACT_PKG.TAXABLE_AMT_TBL;
1642 l_taxable_amt_funcl_curr_tbl ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL;
1643 l_tax_amt_tbl ZX_EXTRACT_PKG.TAX_AMT_TBL;
1644 l_tax_amt_funcl_curr_tbl ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL;
1645 l_precision_tbl numtab;
1646 l_minimum_accountable_unit_tbl numtab;
1647
1648 l_func_precision NUMBER;
1649 l_func_min_account_unit NUMBER;
1650
1651 l_sob_type GL_SETS_OF_BOOKS.MRC_SOB_TYPE_CODE%TYPE;
1652
1653 --
1654 -- Cursor definitions
1655 --
1656
1657 CURSOR c_trl_itf(c_request_id IN NUMBER, c_source_ledger IN VARCHAR2) IS
1658 SELECT detail_tax_line_id,
1659 trx_id,
1660 tax_line_id,
1661 trx_line_id,
1662 actg_source_id,
1663 event_class_code,
1664 taxable_amt,
1665 tax_amt,
1666 tax_amt_funcl_curr,
1667 tax_rate_id,
1668 extract_source_ledger,
1669 ledger_id
1670 FROM zx_rep_trx_detail_t
1671 WHERE request_id = c_request_id
1672 AND extract_source_ledger = c_source_ledger;
1673
1674 BEGIN
1675
1676 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1677 IF (g_level_procedure >= g_current_runtime_level ) THEN
1678 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_taxable.BEGIN',
1679 'zx_jg_extract.get_taxable()+');
1680 END IF;
1681
1682 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1683
1684
1685 IF p_trl_global_variables_rec.report_name = 'ZXJGTAX' THEN
1686 IF (g_level_procedure >= g_current_runtime_level ) THEN
1687 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_taxable',
1688 'initialize Call '||p_trl_global_variables_rec.report_name);
1689 END IF;
1690
1691 -- Set values from TRL global variables
1692
1693 initialize(p_trl_global_variables_rec,
1694 l_func_precision,
1695 l_func_min_account_unit,
1696 l_sob_type);
1697
1698 IF (g_level_procedure >= g_current_runtime_level ) THEN
1699 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_taxable',
1700 'Get gl taxable transactions ');
1701 END IF;
1702
1703 -- For GL transactions
1704
1705 OPEN c_trl_itf(p_trl_global_variables_rec.request_id, 'GL');
1706
1707 FETCH c_trl_itf BULK COLLECT INTO p_detail_tax_line_id_tbl,
1708 p_trx_id_tbl,
1709 p_tax_line_id_tbl,
1710 p_trx_line_id_tbl,
1711 p_tax_dist_id_tbl,
1712 p_event_class_code_tbl,
1713 p_taxable_amt_tbl,
1714 p_tax_amt_tbl,
1715 p_tax_amt_funcl_curr_tbl,
1716 p_tax_rate_id_tbl,
1717 p_extract_source_ledger_tbl,
1718 p_ledger_id_tbl;
1719 CLOSE c_trl_itf;
1720
1721 IF p_detail_tax_line_id_tbl.count > 0 THEN
1722
1723 -- Get taxable amount
1724 get_gl_taxable(p_detail_tax_line_id_tbl,
1725 p_trx_id_tbl,
1726 p_tax_line_id_tbl,
1727 p_trx_line_id_tbl,
1728 -- p_tax_dist_id_tbl,
1729 l_detail_tax_line_id_tbl,
1730 l_ccid_tbl,
1731 l_acct_date_tbl,
1732 l_taxable_amt_tbl,
1733 l_taxable_amt_funcl_curr_tbl,
1734 l_tax_amt_tbl,
1735 l_tax_amt_funcl_curr_tbl);
1736
1737 -- Set accounting info
1738 --set_accounting_info(l_ccid_tbl);
1739
1740 -- Insert accounting infor and prorated amount into TRL interface table
1741 insert_row(p_detail_tax_line_id_tbl,
1742 l_taxable_amt_tbl,
1743 l_taxable_amt_funcl_curr_tbl,
1744 l_tax_amt_tbl,
1745 l_tax_amt_funcl_curr_tbl,
1746 l_ccid_tbl,
1747 l_acct_date_tbl);
1748
1749 -- Reset result table
1750 reset_result_tables(p_detail_tax_line_id_tbl,
1751 p_trx_id_tbl,
1752 p_tax_line_id_tbl,
1753 p_trx_line_id_tbl,
1754 p_tax_dist_id_tbl,
1755 p_event_class_code_tbl,
1756 p_taxable_amt_tbl,
1757 p_tax_amt_tbl,
1758 p_tax_amt_funcl_curr_tbl,
1759 p_tax_rate_id_tbl,
1760 p_extract_source_ledger_tbl,
1761 p_ledger_id_tbl,
1762 l_detail_tax_line_id_tbl,
1763 l_taxable_amt_tbl,
1764 l_taxable_amt_funcl_curr_tbl,
1765 l_tax_amt_tbl,
1766 l_tax_amt_funcl_curr_tbl,
1767 l_ccid_tbl,
1768 l_acct_date_tbl);
1769
1770 END IF;
1771
1772 -- For AP transactions
1773
1774 IF (g_level_procedure >= g_current_runtime_level ) THEN
1775 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_taxable',
1776 'Get AP taxable transactions ');
1777 END IF;
1778
1779 OPEN c_trl_itf(p_trl_global_variables_rec.request_id, 'AP');
1780
1781 FETCH c_trl_itf BULK COLLECT INTO p_detail_tax_line_id_tbl,
1782 p_trx_id_tbl,
1783 p_tax_line_id_tbl,
1784 p_trx_line_id_tbl,
1785 p_tax_dist_id_tbl,
1786 p_event_class_code_tbl,
1787 p_taxable_amt_tbl,
1788 p_tax_amt_tbl,
1789 p_tax_amt_funcl_curr_tbl,
1790 p_tax_rate_id_tbl,
1791 p_extract_source_ledger_tbl,
1792 p_ledger_id_tbl;
1793 CLOSE c_trl_itf;
1794
1795 IF (g_level_procedure >= g_current_runtime_level ) THEN
1796 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_taxable',
1797 'Rows fetched from c_trl_itf :'||to_char(p_detail_tax_line_id_tbl.count));
1798 END IF;
1799
1800
1801 IF p_detail_tax_line_id_tbl.count > 0 THEN
1802
1803 get_ap_taxable(p_detail_tax_line_id_tbl,
1804 p_trx_id_tbl,
1805 p_tax_line_id_tbl,
1806 p_trx_line_id_tbl,
1807 p_tax_dist_id_tbl,
1808 p_ledger_id_tbl,
1809 l_detail_tax_line_id_tbl,
1810 l_ccid_tbl,
1811 l_acct_date_tbl,
1812 l_taxable_amt_tbl,
1813 l_taxable_amt_funcl_curr_tbl,
1814 l_tax_amt_tbl,
1815 l_tax_amt_funcl_curr_tbl);
1816
1817 -- Set accounting info
1818 --set_accounting_info(l_ccid_tbl);
1819
1820 -- Insert accounting infor and prorated amount into TRL interface table
1821 insert_row(l_detail_tax_line_id_tbl,
1822 l_taxable_amt_tbl,
1823 l_taxable_amt_funcl_curr_tbl,
1824 l_tax_amt_tbl,
1825 l_tax_amt_funcl_curr_tbl,
1826 l_ccid_tbl,
1827 l_acct_date_tbl);
1828
1829 -- Reset result table
1830 reset_result_tables(p_detail_tax_line_id_tbl,
1831 p_trx_id_tbl,
1832 p_tax_line_id_tbl,
1833 p_trx_line_id_tbl,
1834 p_tax_dist_id_tbl,
1835 p_event_class_code_tbl,
1836 p_taxable_amt_tbl,
1837 p_tax_amt_tbl,
1838 p_tax_amt_funcl_curr_tbl,
1839 p_tax_rate_id_tbl,
1840 p_extract_source_ledger_tbl,
1841 p_ledger_id_tbl,
1842 l_detail_tax_line_id_tbl,
1843 l_taxable_amt_tbl,
1844 l_taxable_amt_funcl_curr_tbl,
1845 l_tax_amt_tbl,
1846 l_tax_amt_funcl_curr_tbl,
1847 l_ccid_tbl,
1848 l_acct_date_tbl);
1849 END IF;
1850
1851 IF (g_level_procedure >= g_current_runtime_level ) THEN
1852 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_taxable',
1853 'Get AR taxable transactions ');
1854 END IF;
1855
1856 -- For AR Transactions
1857
1858 OPEN c_trl_itf(p_trl_global_variables_rec.request_id, 'AR');
1859
1860 FETCH c_trl_itf BULK COLLECT INTO p_detail_tax_line_id_tbl,
1861 p_trx_id_tbl,
1862 p_tax_line_id_tbl,
1863 p_trx_line_id_tbl,
1864 p_tax_dist_id_tbl,
1865 p_event_class_code_tbl,
1866 p_taxable_amt_tbl,
1867 p_tax_amt_tbl,
1868 p_tax_amt_funcl_curr_tbl,
1869 p_tax_rate_id_tbl,
1870 p_extract_source_ledger_tbl,
1871 p_ledger_id_tbl;
1872 CLOSE c_trl_itf;
1873
1874 IF (g_level_procedure >= g_current_runtime_level ) THEN
1875 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_taxable',
1876 'After c_trl_itf Cursor Call for AR taxable transactions ');
1877 END IF;
1878
1879 IF p_detail_tax_line_id_tbl.count > 0 THEN
1880
1881 get_ar_taxable(p_detail_tax_line_id_tbl,
1882 p_event_class_code_tbl,
1883 p_trx_id_tbl,
1884 p_tax_line_id_tbl,
1885 p_trx_line_id_tbl,
1886 -- p_tax_dist_id_tbl,
1887 p_taxable_amt_tbl,
1888 p_tax_amt_tbl,
1889 p_tax_amt_funcl_curr_tbl,
1890 p_tax_rate_id_tbl,
1891 l_sob_type,
1892 l_func_precision,
1893 l_func_min_account_unit,
1894 l_detail_tax_line_id_tbl,
1895 l_ccid_tbl,
1896 l_acct_date_tbl,
1897 l_taxable_amt_tbl,
1898 l_taxable_amt_funcl_curr_tbl,
1899 l_tax_amt_tbl,
1900 l_tax_amt_funcl_curr_tbl);
1901
1902 -- Set accounting info
1903 --set_accounting_info(l_ccid_tbl);
1904
1905 -- Insert accounting infor and prorated amount into TRL interface table
1906 IF (g_level_procedure >= g_current_runtime_level ) THEN
1907 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_taxable',
1908 'insert_row API call ');
1909 END IF;
1910
1911 insert_row(l_detail_tax_line_id_tbl,
1912 l_taxable_amt_tbl,
1913 l_taxable_amt_funcl_curr_tbl,
1914 l_tax_amt_tbl,
1915 l_tax_amt_funcl_curr_tbl,
1916 l_ccid_tbl,
1917 l_acct_date_tbl);
1918
1919 END IF; -- IF p_detail_tax_line_id_tbl.count > 0
1920
1921 END IF; -- IF report_name = ..
1922 IF (g_level_procedure >= g_current_runtime_level ) THEN
1923 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.get_taxable.END',
1924 'zx_jg_extract_pkg.get_taxable()- ');
1925 END IF;
1926
1927 END get_taxable;
1928
1929 END zx_jg_extract_pkg;