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