[Home] [Help]
PACKAGE BODY: APPS.ZX_JE_EXTRACT_PKG
Source
1 PACKAGE BODY ZX_JE_EXTRACT_PKG AS
2 /* $Header: zxriextrajeppvtb.pls 120.14.12010000.2 2008/11/12 12:59:05 spasala ship $ */
3
4 -----------------------------------------
5 --Private Variable Declarations
6
7 -----------------------------------------
8
9 -----------------------------------------
10
11 --Private Methods Declarations
12 -----------------------------------------
13
14 PG_DEBUG varchar2(1) ;
15 l_err_msg varchar2(120);
16
17
18 -----------------------------------------
19 --Public Variable Declarations
20 -----------------------------------------
21 --
22 g_current_runtime_level NUMBER ;
23 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
24 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
25 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
26 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
27 g_error_buffer VARCHAR2(100);
28 -----------------------------------------
29 --Public Methods Declarations
30 -----------------------------------------
31
32
33 /*===========================================================================+
34 | PUBLIC PROCEDURE |
35 | POPULATE_JE_AR |
36 | Type : Public |
37 | Pre-req : None |
38 | Function : |
39 | This procedure calls the API to select the JE specific data from |
40 | JE receivables tables. |
41 | |
42 | Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
43 | |
44 | Parameters : |
45 | IN: P_TRL_GLOBAL_VARIABLES_REC ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE|
46 | |
47 | |
48 | MODIFICATION HISTORY |
49 | 13-FEB-2006 RJREDDY Created |
50 | |
51 | |
52 +===========================================================================*/
53
54 PROCEDURE POPULATE_JE_AR
55 (
56 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
57 )
58
59 IS
60
61
62 TYPE ATTRIBUTE1_TBL is TABLE OF
63 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
64
65 TYPE ATTRIBUTE2_TBL is TABLE OF
66 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE2%TYPE INDEX BY BINARY_INTEGER;
67
68 TYPE ATTRIBUTE3_TBL is TABLE OF
69 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE3%TYPE INDEX BY BINARY_INTEGER;
70
71 TYPE ATTRIBUTE4_TBL is TABLE OF
72 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE4%TYPE INDEX BY BINARY_INTEGER;
73
74 TYPE ATTRIBUTE5_TBL is TABLE OF
75 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE5%TYPE INDEX BY BINARY_INTEGER;
76
77 TYPE ATTRIBUTE6_TBL is TABLE OF
78 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE6%TYPE INDEX BY BINARY_INTEGER;
79
80 TYPE ATTRIBUTE7_TBL is TABLE OF
81 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE7%TYPE INDEX BY BINARY_INTEGER;
82
83 TYPE ATTRIBUTE8_TBL is TABLE OF
84 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE8%TYPE INDEX BY BINARY_INTEGER;
85
86 TYPE ATTRIBUTE9_TBL is TABLE OF
87 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE9%TYPE INDEX BY BINARY_INTEGER;
88
89 TYPE ATTRIBUTE11_TBL is TABLE OF
90 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE11%TYPE INDEX BY BINARY_INTEGER;
91
92 TYPE ATTRIBUTE12_TBL is TABLE OF
93 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE12%TYPE INDEX BY BINARY_INTEGER;
94
95 TYPE ATTRIBUTE13_TBL is TABLE OF
96 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE13%TYPE INDEX BY BINARY_INTEGER;
97
98 TYPE ATTRIBUTE23_TBL is TABLE OF
99 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE23%TYPE INDEX BY BINARY_INTEGER;
100
101 TYPE ATTRIBUTE24_TBL is TABLE OF
102 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE24%TYPE INDEX BY BINARY_INTEGER;
103
104 TYPE ATTRIBUTE25_TBL is TABLE OF
105 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE25%TYPE INDEX BY BINARY_INTEGER;
106
107 TYPE ATTRIBUTE26_TBL is TABLE OF
108 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE26%TYPE INDEX BY BINARY_INTEGER;
109
110 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
111 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
112 l_tax_status_code_tbl ZX_EXTRACT_PKG.TAX_STATUS_CODE_TBL;
113 l_trx_business_category_tbl ZX_EXTRACT_PKG.TRX_BUSINESS_CATEGORY_TBL;
114 l_document_sub_type_tbl ZX_EXTRACT_PKG.DOCUMENT_SUB_TYPE_TBL;
115 l_tax_rate_id_tbl ZX_EXTRACT_PKG.tax_rate_id_tbl ;
116
117 l_attribute1_tbl ATTRIBUTE1_TBL;
118 l_attribute2_tbl ATTRIBUTE2_TBL;
119 l_attribute3_tbl ATTRIBUTE3_TBL;
120 l_attribute4_tbl ATTRIBUTE4_TBL;
121 l_attribute5_tbl ATTRIBUTE5_TBL;
122 l_attribute6_tbl ATTRIBUTE6_TBL;
123 l_attribute7_tbl ATTRIBUTE7_TBL;
124
125 l_attribute1_tmp_tbl ATTRIBUTE1_TBL;
126 l_attribute2_tmp_tbl ATTRIBUTE2_TBL;
127 l_attribute3_tmp_tbl ATTRIBUTE3_TBL;
128 l_attribute4_tmp_tbl ATTRIBUTE4_TBL;
129 l_attribute5_tmp_tbl ATTRIBUTE5_TBL;
130 l_attribute6_tmp_tbl ATTRIBUTE6_TBL;
131 l_attribute7_tmp_tbl ATTRIBUTE7_TBL;
132
133 l_attribute8_tbl ATTRIBUTE8_TBL;
134 l_attribute9_tbl ATTRIBUTE9_TBL;
135 l_attribute11_tbl ATTRIBUTE11_TBL;
136 l_attribute12_tbl ATTRIBUTE12_TBL;
137 l_attribute13_tbl ATTRIBUTE23_TBL;
138 l_attribute23_tbl ATTRIBUTE23_TBL;
139 l_attribute24_tbl ATTRIBUTE24_TBL;
140 l_attribute25_tbl ATTRIBUTE25_TBL;
141 l_attribute26_tbl ATTRIBUTE26_TBL;
142 C_LINES_PER_COMMIT Number:=1000;
143 l_count NUMBER ;
144
145 cursor get_rep_entity_info_cur is
146 SELECT detail_tax_line_id,
147 itf1.tax_status_code,
148 itf1.trx_business_category,
149 itf1.document_sub_type,
150 itf1.TAX_RATE_ID,
151 (SELECT assoc.reporting_code_char_value FROM
152 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
153 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
154 AND itf1.TAX_RATE_ID = assoc.entity_id
155 AND assoc.entity_code = 'ZX_RATES'
156 AND (assoc.EFFECTIVE_TO is null or assoc.EFFECTIVE_TO > sysdate)
157 AND rep_type.reporting_type_code
158 IN ('CZ_TAX_ORIGIN','HU_TAX_ORIGIN','PL_TAX_ORIGIN','CH_VAT_REGIME')
159 ),
160 (SELECT assoc.reporting_code_char_value FROM
161 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
162 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
163 AND itf1.TAX_RATE_ID = assoc.entity_id
164 AND assoc.entity_code = 'ZX_RATES'
165 AND (assoc.EFFECTIVE_TO is null or assoc.EFFECTIVE_TO > sysdate)
166 AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE')
167 FROM zx_rep_trx_detail_t itf1
168 WHERE itf1.application_id = 222
169 AND itf1.entity_code = 'TRANSACTIONS'
170 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
171
172 --Bug 5636632
173 CURSOR get_reporting_code_value(
174 p_entity_id zx_report_codes_assoc.entity_id%TYPE ,
175 p_reporting_type zx_reporting_types_b.reporting_type_code%TYPE ) IS
176 SELECT assoc.reporting_code_char_value
177 FROM zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
178 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
179 AND assoc.entity_id = p_entity_id
180 AND assoc.entity_code = 'ZX_RATES'
181 AND rep_type.reporting_type_code = p_reporting_type ;
182
183 BEGIN
184
185 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
186 IF (g_level_unexpected >= g_current_runtime_level ) THEN
187 FND_LOG.STRING(g_level_unexpected,
188 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
189 'je_tax_extract.populate_je_ar(+)');
190 END IF;
191
192 SELECT detail_tax_line_id
193 BULK COLLECT INTO l_detail_tax_line_id_tbl
194 FROM zx_rep_trx_detail_t itf1
195 WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
196
197
198 IF l_detail_tax_line_id_tbl.count <> 0 THEN
199
200 INSERT INTO ZX_REP_TRX_JX_EXT_T
201 (detail_tax_line_ext_id,
202 detail_tax_line_id,
203 attribute9,
204 attribute11,
205 attribute12,
206 attribute13,
207 attribute23,
208 created_by,
209 creation_date,
210 last_updated_by,
211 last_update_date,
212 last_update_login,
213 request_id)
214 SELECT zx_rep_trx_jx_ext_t_s.nextval,
215 itf1.detail_tax_line_id,
216 decode (ra_cust.global_attribute_category,
217 'JE.ES.ARXTWMAI.MODELO347PR', ra_cust.global_attribute2,
218 'JE.ES.ARXTWMAI.MODELO415_347PR', ra_cust.global_attribute2,
219 NULL),
220 decode (ra_cust.global_attribute_category,
221 'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute3,
222 'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute3,
223 NULL),
224 decode (ra_cust.global_attribute_category,
225 'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute4,
226 'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute4,
227 NULL),
228 decode (ra_cust.global_attribute_category,
229 'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute5,
230 'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute5,
231 NULL),
232 substr(itf1.trx_business_category,(instr(itf1.trx_business_category,'MOD',1,1)+3),
233 length(itf1.trx_business_category)),
234 fnd_global.user_id,
235 sysdate,
236 fnd_global.user_id,
237 sysdate,
238 fnd_global.login_id,
239 P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
240 FROM ra_customer_trx_all ra_cust,
241 zx_rep_trx_detail_t itf1
242 WHERE itf1.trx_id = ra_cust.customer_trx_id
243 AND itf1.application_id = 222
244 AND itf1.entity_code = 'TRANSACTIONS'
245 AND itf1.ledger_id = ra_cust.set_of_books_id
246 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
247
248
249 open get_rep_entity_info_cur ;
250
251 Loop
252
253 Fetch get_rep_entity_info_cur
254 bulk collect into
255 L_DETAIL_TAX_LINE_ID_TBL,
256 L_TAX_STATUS_CODE_TBL,
257 L_TRX_BUSINESS_CATEGORY_TBL,
258 L_DOCUMENT_SUB_TYPE_TBL,
259 L_TAX_RATE_ID_TBL ,
260 L_ATTRIBUTE25_TBL , --Bug 5510822
261 L_ATTRIBUTE26_TBL --EMEA Changes
262 LIMIT C_LINES_PER_COMMIT;
263
264
265
266 l_count := nvl(L_DETAIL_TAX_LINE_ID_TBL.COUNT,0);
267
268 IF (g_level_unexpected >= g_current_runtime_level ) THEN
269 FND_LOG.STRING(g_level_unexpected,
270 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
271 'l_count : '||l_count);
272 END IF;
273
274 FOR i IN 1..l_count
275 LOOP
276 IF L_ATTRIBUTE1_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
277 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
278 ELSE
279 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_LOCATION');
280 FETCH get_reporting_code_value INTO L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
281 IF ( get_reporting_code_value%NOTFOUND ) THEN
282 L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
283 END IF ;
284 CLOSE get_reporting_code_value;
285 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
286 END IF ;
287
288 IF L_ATTRIBUTE2_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
289 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
290 ELSE
291 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_TAXABLE_BOX');
292 FETCH get_reporting_code_value INTO L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
293 IF ( get_reporting_code_value%NOTFOUND ) THEN
294 L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
295 END IF ;
296 CLOSE get_reporting_code_value;
297 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
298 END IF ;
299
300 IF L_ATTRIBUTE3_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
301 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
302 ELSE
303 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_REC_TAX_BOX');
304 FETCH get_reporting_code_value INTO L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
305 IF ( get_reporting_code_value%NOTFOUND ) THEN
306 L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
307 END IF ;
308 CLOSE get_reporting_code_value;
309 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
310 END IF ;
311
312 IF L_ATTRIBUTE4_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
313 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
314 ELSE
315 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_TTL_TAXABLE_BOX');
316 FETCH get_reporting_code_value INTO L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
317 IF ( get_reporting_code_value%NOTFOUND ) THEN
318 L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
319 END IF ;
320 CLOSE get_reporting_code_value;
321 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
322 END IF ;
323
324 IF L_ATTRIBUTE5_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
325 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
326 ELSE
327 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAXABLE');
328 FETCH get_reporting_code_value INTO L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
329 IF ( get_reporting_code_value%NOTFOUND ) THEN
330 L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
331 END IF ;
332 CLOSE get_reporting_code_value;
333 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
334 END IF ;
335
336 IF L_ATTRIBUTE6_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
337 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
338 ELSE
339 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_NON_REC_TAXABLE');
340 FETCH get_reporting_code_value INTO L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
341 IF ( get_reporting_code_value%NOTFOUND ) THEN
342 L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
343 END IF ;
344 CLOSE get_reporting_code_value;
345 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
346 END IF ;
347
348 IF L_ATTRIBUTE7_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
349 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
350 ELSE
351 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAX_BOX');
352 FETCH get_reporting_code_value INTO L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
353 IF ( get_reporting_code_value%NOTFOUND ) THEN
354 L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
355 END IF ;
356 CLOSE get_reporting_code_value;
357 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
358 END IF ;
359
360 IF (g_level_unexpected >= g_current_runtime_level ) THEN
361 FND_LOG.STRING(g_level_unexpected,
362 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
363 'Displaying the Vlaues for attributes : i :'||i);
364 FND_LOG.STRING(g_level_unexpected,
365 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
366 'L_DETAIL_TAX_LINE_ID_TBL(i)'||L_DETAIL_TAX_LINE_ID_TBL(i));
367 FND_LOG.STRING(g_level_unexpected,
368 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
369 'L_ATTRIBUTE1_TBL(i) :'||L_ATTRIBUTE1_TBL(i));
370 FND_LOG.STRING(g_level_unexpected,
371 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
375 'L_ATTRIBUTE3_TBL(i) :'||L_ATTRIBUTE3_TBL(i));
372 'L_ATTRIBUTE2_TBL(i) :'||L_ATTRIBUTE2_TBL(i));
373 FND_LOG.STRING(g_level_unexpected,
374 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
376 FND_LOG.STRING(g_level_unexpected,
377 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
378 'L_ATTRIBUTE4_TBL(i) :'||L_ATTRIBUTE4_TBL(i));
379 FND_LOG.STRING(g_level_unexpected,
380 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
381 'L_ATTRIBUTE5_TBL(i) :'||L_ATTRIBUTE5_TBL(i));
382 FND_LOG.STRING(g_level_unexpected,
383 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
384 'L_ATTRIBUTE6_TBL(i) :'||L_ATTRIBUTE6_TBL(i));
385 FND_LOG.STRING(g_level_unexpected,
386 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
387 'L_ATTRIBUTE7_TBL(i) :'||L_ATTRIBUTE7_TBL(i));
388 END IF;
389
390 END LOOP ;
391
392 IF (g_level_unexpected >= g_current_runtime_level ) THEN
393 FND_LOG.STRING(g_level_unexpected,
394 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
395 'Before Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained ' );
396 END IF;
397
398 IF ( l_count > 0 ) THEN
399
400 FORALL i IN 1 .. L_DETAIL_TAX_LINE_ID_TBL.count
401 UPDATE ZX_REP_TRX_JX_EXT_T
402 SET
403 TAX_STATUS_MNG = L_TAX_STATUS_CODE_TBL(i),
404 TRX_BUSINESS_CATEGORY_MNG = L_TRX_BUSINESS_CATEGORY_TBL(i),
405 DOCUMENT_SUB_TYPE_MNG = L_DOCUMENT_SUB_TYPE_TBL(i),
406 ATTRIBUTE1 = L_ATTRIBUTE1_TBL(i),
407 ATTRIBUTE2 = L_ATTRIBUTE2_TBL(i),
408 ATTRIBUTE3 = L_ATTRIBUTE3_TBL(i),
409 ATTRIBUTE4 = L_ATTRIBUTE4_TBL(i),
410 ATTRIBUTE5 = L_ATTRIBUTE5_TBL(i),
411 ATTRIBUTE6 = L_ATTRIBUTE6_TBL(i),
412 ATTRIBUTE7 = L_ATTRIBUTE7_TBL(i),
413 attribute25 = L_ATTRIBUTE25_TBL(i), --Bug 5510822
414 attribute26 = L_ATTRIBUTE26_TBL(i) --EMEA Changes
415 WHERE detail_tax_line_id = L_DETAIL_TAX_LINE_ID_TBL(i) and
416 request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
417
418 IF (g_level_unexpected >= g_current_runtime_level ) THEN
419 FND_LOG.STRING(g_level_unexpected,
420 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
421 'After Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained ' );
422 END IF;
423 -- exit when get_rep_entity_info_cur%NOTFOUND;
424 ELSE
425 EXIT ;
426 END IF ;
427
428 end loop;
429
430 close get_rep_entity_info_cur ;
431
432 END IF;
433 commit;
434 IF (g_level_unexpected >= g_current_runtime_level ) THEN
435 FND_LOG.STRING(g_level_unexpected,
436 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.END',
437 'je_tax_extract.populate_je_ar(-)');
438 END IF;
439
440
441 EXCEPTION
442 WHEN OTHERS THEN
443
444 l_err_msg := substrb(SQLERRM,1,120);
445 arp_standard.debug('EXCEPTION raised in ' ||'POPULATE_JE_AR: ' ||SQLCODE ||':'||l_err_msg);
446
447 END populate_je_ar;
448
449
450 /*===========================================================================+
451 | PUBLIC PROCEDURE |
452 | POPULATE_JE_AP |
453 | Type : Public |
454 | Pre-req : None |
455 | Function : |
456 | This procedure calls the API to select the JE specific data from |
457 | JE payables tables. Currently only JE_LOOKUP_INFO plug-in is called |
458 | inside. |
459 | |
460 | Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
461 | |
462 | Parameters : |
463 | |
464 | |
465 | MODIFICATION HISTORY |
466 | 13-FEB-2006 RJREDDY Created |
467 | |
468 | |
469 +===========================================================================*/
470
471
472 PROCEDURE POPULATE_JE_AP
473 (
474 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
475 )
476
477 IS
478 TYPE ATTRIBUTE1_TBL is TABLE OF
479 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
480 TYPE ATTRIBUTE2_TBL is TABLE OF
481 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE2%TYPE INDEX BY BINARY_INTEGER;
482 TYPE ATTRIBUTE3_TBL is TABLE OF
483 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE3%TYPE INDEX BY BINARY_INTEGER;
484 TYPE ATTRIBUTE4_TBL is TABLE OF
485 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE4%TYPE INDEX BY BINARY_INTEGER;
486 TYPE ATTRIBUTE5_TBL is TABLE OF
487 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE5%TYPE INDEX BY BINARY_INTEGER;
488 TYPE ATTRIBUTE6_TBL is TABLE OF
492 TYPE REPORTING_TYPE_ID_TBL is TABLE OF
489 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE6%TYPE INDEX BY BINARY_INTEGER;
490 TYPE ATTRIBUTE7_TBL is TABLE OF
491 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE7%TYPE INDEX BY BINARY_INTEGER;
493 ZX_REPORTING_TYPES_B.REPORTING_TYPE_ID%TYPE INDEX BY BINARY_INTEGER;
494 TYPE ATTRIBUTE8_TBL is TABLE OF
495 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE8%TYPE INDEX BY BINARY_INTEGER;
496 TYPE ATTRIBUTE10_TBL is TABLE OF
497 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE10%TYPE INDEX BY BINARY_INTEGER;
498 TYPE ATTRIBUTE11_TBL is TABLE OF
499 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE11%TYPE INDEX BY BINARY_INTEGER;
500 TYPE ATTRIBUTE12_TBL is TABLE OF
501 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE12%TYPE INDEX BY BINARY_INTEGER;
502 TYPE ATTRIBUTE13_TBL is TABLE OF
503 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE13%TYPE INDEX BY BINARY_INTEGER;
504 TYPE ATTRIBUTE14_TBL is TABLE OF
505 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE14%TYPE INDEX BY BINARY_INTEGER;
506 TYPE ATTRIBUTE15_TBL is TABLE OF
507 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE15%TYPE INDEX BY BINARY_INTEGER;
508 TYPE ATTRIBUTE16_TBL is TABLE OF
509 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE16%TYPE INDEX BY BINARY_INTEGER;
510 TYPE ATTRIBUTE17_TBL is TABLE OF
511 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE17%TYPE INDEX BY BINARY_INTEGER;
512 TYPE ATTRIBUTE18_TBL is TABLE OF
513 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE18%TYPE INDEX BY BINARY_INTEGER;
514 TYPE ATTRIBUTE19_TBL is TABLE OF
515 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE19%TYPE INDEX BY BINARY_INTEGER;
516 TYPE ATTRIBUTE20_TBL is TABLE OF
517 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE20%TYPE INDEX BY BINARY_INTEGER;
518 TYPE DETAIL_TAX_LINE_ID_TBL is TABLE OF
519 ZX_REP_TRX_JX_EXT_T.DETAIL_TAX_LINE_ID%TYPE INDEX BY BINARY_INTEGER;
520 TYPE ATTRIBUTE21_TBL is TABLE OF
521 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE21%TYPE INDEX BY BINARY_INTEGER;
522 TYPE ATTRIBUTE22_TBL is TABLE OF
523 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE22%TYPE INDEX BY BINARY_INTEGER;
524 TYPE ATTRIBUTE23_TBL is TABLE OF
525 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE23%TYPE INDEX BY BINARY_INTEGER;
526
527 TYPE ATTRIBUTE24_TBL is TABLE OF
528 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE24%TYPE INDEX BY BINARY_INTEGER;
529
530 TYPE ATTRIBUTE25_TBL is TABLE OF
531 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE25%TYPE INDEX BY BINARY_INTEGER;
532
533 TYPE ATTRIBUTE26_TBL is TABLE OF
534 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE26%TYPE INDEX BY BINARY_INTEGER;
535
536 l_detail_tax_line_id_tbl DETAIL_TAX_LINE_ID_TBL;
537 l_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
538 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
539 l_tax_rate_id_tbl ZX_EXTRACT_PKG.tax_rate_id_tbl ;
540
541 l_tax_status_code_tbl ZX_EXTRACT_PKG.TAX_STATUS_CODE_TBL;
542 l_trx_business_category_tbl ZX_EXTRACT_PKG.TRX_BUSINESS_CATEGORY_TBL;
543 l_document_sub_type_tbl ZX_EXTRACT_PKG.DOCUMENT_SUB_TYPE_TBL;
544
545 l_attribute1_tbl ATTRIBUTE1_TBL;
546 l_attribute2_tbl ATTRIBUTE2_TBL;
547 l_attribute3_tbl ATTRIBUTE3_TBL;
548 l_attribute4_tbl ATTRIBUTE4_TBL;
549 l_attribute5_tbl ATTRIBUTE5_TBL;
550 l_attribute6_tbl ATTRIBUTE6_TBL;
551 l_attribute7_tbl ATTRIBUTE7_TBL;
552
553 L_ATTRIBUTE1_TMP_TBL ATTRIBUTE1_TBL;
554 L_ATTRIBUTE2_TMP_TBL ATTRIBUTE2_TBL;
555 L_ATTRIBUTE3_TMP_TBL ATTRIBUTE3_TBL;
556 L_ATTRIBUTE4_TMP_TBL ATTRIBUTE4_TBL;
557 L_ATTRIBUTE5_TMP_TBL ATTRIBUTE5_TBL;
558 L_ATTRIBUTE6_TMP_TBL ATTRIBUTE6_TBL;
559 L_ATTRIBUTE7_TMP_TBL ATTRIBUTE7_TBL;
560
561 l_attribute8_tbl ATTRIBUTE8_TBL;
562 l_attribute10_tbl ATTRIBUTE10_TBL;
563 l_attribute11_tbl ATTRIBUTE11_TBL;
564 l_attribute12_tbl ATTRIBUTE12_TBL;
565 l_attribute13_tbl ATTRIBUTE13_TBL;
566 l_attribute14_tbl ATTRIBUTE14_TBL;
567 l_attribute15_tbl ATTRIBUTE15_TBL;
568 l_attribute16_tbl ATTRIBUTE16_TBL;
569 l_attribute17_tbl ATTRIBUTE17_TBL;
570 l_attribute18_tbl ATTRIBUTE18_TBL;
571 l_attribute19_tbl ATTRIBUTE19_TBL;
572 l_attribute20_tbl ATTRIBUTE20_TBL;
573 l_attribute21_tbl ATTRIBUTE21_TBL;
574 l_attribute22_tbl ATTRIBUTE22_TBL;
575 l_attribute23_tbl ATTRIBUTE23_TBL;
576 l_attribute24_tbl ATTRIBUTE24_TBL;
577 l_attribute25_tbl ATTRIBUTE25_TBL;
578 l_attribute26_tbl ATTRIBUTE26_TBL;
579 C_LINES_PER_COMMIT Number:=1000;
580 l_count NUMBER ;
581
582
583 cursor get_rep_entity_info_cur is
584 SELECT
585 detail_tax_line_id,
586 fsp.vat_country_code,
587 tax_rate_id ,
588 decode ( hr_loc.global_attribute_category,
589 'JE.ES.PERWSLOC.PRL_NO', hr_loc.global_attribute1,
590 NULL ),
591 decode ( hr_loc.global_attribute_category,
592 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute1,
593 NULL ),
594 decode ( hr_loc.global_attribute_category,
595 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute2,
596 NULL ),
597 decode ( hr_loc.global_attribute_category,
598 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute3,
599 NULL ),
600 decode ( hr_loc.global_attribute_category,
601 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute4,
602 NULL ),
603 decode ( hr_loc.global_attribute_category,
604 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute5,
605 NULL ),
606 (SELECT assoc.reporting_code_char_value FROM
607 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
608 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
612 AND rep_type.reporting_type_code
609 AND itf1.TAX_RATE_ID = assoc.entity_id
610 AND assoc.entity_code = 'ZX_RATES'
611 AND (assoc.EFFECTIVE_TO is null or assoc.EFFECTIVE_TO > sysdate)
613 IN ('CZ_TAX_ORIGIN','HU_TAX_ORIGIN','PL_TAX_ORIGIN','CH_VAT_REGIME')
614 ),
615 (SELECT assoc.reporting_code_char_value FROM
616 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
617 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
618 AND itf1.TAX_RATE_ID = assoc.entity_id
619 AND assoc.entity_code = 'ZX_RATES'
620 AND (assoc.EFFECTIVE_TO is null or assoc.EFFECTIVE_TO > sysdate)
621 AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE')
622 FROM financials_system_params_all fsp,
623 hr_locations_all hr_loc,
624 zx_rep_trx_detail_t itf1
625 WHERE
626 itf1.application_id = 200
627 AND itf1.entity_code = 'AP_INVOICES'
628 AND itf1.ledger_id = fsp.set_of_books_id
629 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
630 AND itf1.ship_to_location_id = hr_loc.ship_to_location_id(+) ;
631
632 --Bug 5636632
633 CURSOR get_reporting_code_value(
634 p_entity_id zx_report_codes_assoc.entity_id%TYPE ,
635 p_reporting_type zx_reporting_types_b.reporting_type_code%TYPE ) IS
636 SELECT assoc.reporting_code_char_value
637 FROM zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
638 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
639 AND assoc.entity_id = p_entity_id
640 AND assoc.entity_code = 'ZX_RATES'
641 AND rep_type.reporting_type_code = p_reporting_type ;
642
643 BEGIN
644 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
645 IF (g_level_unexpected >= g_current_runtime_level ) THEN
646 FND_LOG.STRING(g_level_unexpected,
647 'ZX.TRL.je_tax_extract.populate_je_ap.BEGIN',
648 'je_tax_extract.populate_je_ap(+)');
649 END IF;
650
651
652 SELECT detail_tax_line_id
653 BULK COLLECT INTO l_tax_line_id_tbl
654 FROM zx_rep_trx_detail_t itf1
655 WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
656
657
658 IF l_tax_line_id_tbl.count <> 0 THEN
659 IF (g_level_unexpected >= g_current_runtime_level ) THEN
660 FND_LOG.STRING(g_level_unexpected,
661 'ZX.TRL.je_tax_extract.populate_je_ap',
662 'POPULATE_JE_AP - Rows extracted : '||to_char(l_tax_line_id_tbl.count));
663 END IF;
664 INSERT INTO ZX_REP_TRX_JX_EXT_T
665 (detail_tax_line_ext_id,
666 detail_tax_line_id,
667 tax_status_mng,
668 trx_business_category_mng,
669 document_sub_type_mng,
670 attribute8,
671 attribute11,
672 attribute12,
673 -- attribute3,
674 -- attribute13,
675 attribute20,
676 attribute21,
677 attribute22,
678 attribute23,
679 created_by,
680 creation_date,
681 last_updated_by,
682 last_update_date,
683 last_update_login,
684 request_id)
685 SELECT zx_rep_trx_jx_ext_t_s.nextval,
686 itf1.detail_tax_line_id,
687 itf1.tax_status_code,
688 itf1.trx_business_category,
689 itf1.document_sub_type,
690 decode ( ap_inv.global_attribute_category,
691 'JE.SK.APXINWKB.INVOICE_INFO', ap_inv.global_attribute2,
692 'JE.HU.APXINWKB.TAX_DATE', ap_inv.global_attribute2,
693 NULL ),
694 decode ( ap_inv.global_attribute_category,
695 'JE.ES.APXINWKB.INVOICE_INFO', ap_inv.global_attribute2,
696 'JE.ES.APXINWKB.MODELO349', ap_inv.global_attribute2,
697 NULL ),
698 decode ( ap_inv.global_attribute_category,
699 'JE.ES.APXINWKB.INVOICE_INFO', ap_inv.global_attribute3,
700 'JE.ES.APXINWKB.MODELO349', ap_inv.global_attribute3,
701 NULL ),
702 -- decode ( ap_inv.global_attribute_category,
703 -- 'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.global_attribute3,
704 -- NULL ),
705 -- decode ( ap_inv.global_attribute_category,
706 -- 'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.global_attribute4,
707 -- NULL ),
708 ap_inv.source,
709 decode ( ap_inv.global_attribute_category,
710 'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE3,
711 'JE.IL.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE3,
712 NULL ),
713 decode ( ap_inv.global_attribute_category,
714 'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE4,
715 'JE.IL.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE4,
716 NULL ),
717 substr(itf1.trx_business_category,(instr(itf1.trx_business_category,'MOD',1,1)+3),
718 length(itf1.trx_business_category)),
719 fnd_global.user_id,
720 sysdate,
721 fnd_global.user_id,
722 sysdate,
723 fnd_global.login_id,
724 P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
725 FROM ap_invoices_all ap_inv,
726 zx_rep_trx_detail_t itf1
727 WHERE itf1.trx_id = ap_inv.invoice_id
728 and itf1.application_id = 200
729 and itf1.entity_code = 'AP_INVOICES'
733 IF ( g_level_unexpected>= g_current_runtime_level ) THEN
730 and itf1.ledger_id = ap_inv.set_of_books_id
731 and itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
732
734 FND_LOG.STRING(g_level_unexpected, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JE_AP',
735 'No. of rows inserted into ZX_REP_TRX_JX_EXT_T : '||to_char(SQL%ROWCOUNT) );
736 END IF;
737
738 open get_rep_entity_info_cur ;
739
740 Loop
741
742 Fetch get_rep_entity_info_cur
743 BULK COLLECT INTO
744 L_DETAIL_TAX_LINE_ID_TBL,
745 L_ATTRIBUTE10_TBL,
746 L_TAX_RATE_ID_TBL ,
747 L_ATTRIBUTE14_TBL,
748 L_ATTRIBUTE15_TBL,
749 L_ATTRIBUTE16_TBL,
750 L_ATTRIBUTE17_TBL,
751 L_ATTRIBUTE18_TBL,
752 L_ATTRIBUTE19_TBL,
753 l_attribute25_tbl, --Bug 5510822
754 l_attribute26_tbl --Emea changes
755 LIMIT C_LINES_PER_COMMIT;
756
757 l_count := nvl(L_DETAIL_TAX_LINE_ID_TBL.COUNT,0);
758
759 IF (g_level_unexpected >= g_current_runtime_level ) THEN
760 FND_LOG.STRING(g_level_unexpected,
761 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
762 'l_count : '||l_count);
763 END IF;
764
765 FOR i IN 1..l_count
766 LOOP
767 IF L_ATTRIBUTE1_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
768 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
769 ELSE
770 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_LOCATION');
771 FETCH get_reporting_code_value INTO L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
772 IF ( get_reporting_code_value%NOTFOUND ) THEN
773 L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
774 END IF ;
775 CLOSE get_reporting_code_value;
776 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
777 END IF ;
778
779 IF L_ATTRIBUTE2_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
780 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
781 ELSE
782 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_TAXABLE_BOX');
783 FETCH get_reporting_code_value INTO L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
784 IF ( get_reporting_code_value%NOTFOUND ) THEN
785 L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
786 END IF ;
787 CLOSE get_reporting_code_value;
788 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
789 END IF ;
790
791 IF L_ATTRIBUTE3_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
792 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
793 ELSE
794 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_REC_TAX_BOX');
795 FETCH get_reporting_code_value INTO L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
796 IF ( get_reporting_code_value%NOTFOUND ) THEN
797 L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
798 END IF ;
799 CLOSE get_reporting_code_value;
800 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
801 END IF ;
802
803 IF L_ATTRIBUTE4_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
804 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
805 ELSE
806 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_TTL_TAXABLE_BOX');
807 FETCH get_reporting_code_value INTO L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
808 IF ( get_reporting_code_value%NOTFOUND ) THEN
809 L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
810 END IF ;
811 CLOSE get_reporting_code_value;
812 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
813 END IF ;
814
815 IF L_ATTRIBUTE5_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
816 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
817 ELSE
818 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAXABLE');
819 FETCH get_reporting_code_value INTO L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
820 IF ( get_reporting_code_value%NOTFOUND ) THEN
821 L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
822 END IF ;
823 CLOSE get_reporting_code_value;
824 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
825 END IF ;
826
827 IF L_ATTRIBUTE6_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
828 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
829 ELSE
830 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_NON_REC_TAXABLE');
831 FETCH get_reporting_code_value INTO L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
832 IF ( get_reporting_code_value%NOTFOUND ) THEN
833 L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
834 END IF ;
835 CLOSE get_reporting_code_value;
836 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
837 END IF ;
838
839 IF L_ATTRIBUTE7_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
840 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
841 ELSE
842 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAX_BOX');
843 FETCH get_reporting_code_value INTO L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
844 IF ( get_reporting_code_value%NOTFOUND ) THEN
845 L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
846 END IF ;
847 CLOSE get_reporting_code_value;
848 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
849 END IF ;
850
851 IF (g_level_unexpected >= g_current_runtime_level ) THEN
852 FND_LOG.STRING(g_level_unexpected,
856 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
853 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
854 'Displaying the Vlaues for attributes : i :'||i);
855 FND_LOG.STRING(g_level_unexpected,
857 'L_DETAIL_TAX_LINE_ID_TBL(i)'||L_DETAIL_TAX_LINE_ID_TBL(i));
858 FND_LOG.STRING(g_level_unexpected,
859 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
860 'L_ATTRIBUTE1_TBL(i) :'||L_ATTRIBUTE1_TBL(i));
861 FND_LOG.STRING(g_level_unexpected,
862 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
863 'L_ATTRIBUTE2_TBL(i) :'||L_ATTRIBUTE2_TBL(i));
864 FND_LOG.STRING(g_level_unexpected,
865 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
866 'L_ATTRIBUTE3_TBL(i) :'||L_ATTRIBUTE3_TBL(i));
867 FND_LOG.STRING(g_level_unexpected,
868 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
869 'L_ATTRIBUTE4_TBL(i) :'||L_ATTRIBUTE4_TBL(i));
870 FND_LOG.STRING(g_level_unexpected,
871 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
872 'L_ATTRIBUTE5_TBL(i) :'||L_ATTRIBUTE5_TBL(i));
873 FND_LOG.STRING(g_level_unexpected,
874 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
875 'L_ATTRIBUTE6_TBL(i) :'||L_ATTRIBUTE6_TBL(i));
876 FND_LOG.STRING(g_level_unexpected,
877 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
878 'L_ATTRIBUTE7_TBL(i) :'||L_ATTRIBUTE7_TBL(i));
879 END IF;
880
881 END LOOP ;
882
883 IF (g_level_unexpected >= g_current_runtime_level ) THEN
884 FND_LOG.STRING(g_level_unexpected,
885 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
886 'Before Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained ' );
887 END IF;
888
889 IF ( l_count > 0 ) THEN
890
891 IF ( g_level_statement>= g_current_runtime_level ) THEN
892 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JE_AP',
893 'Before Updating ZX_REP_TRX_JX_EXT_T' );
894 END IF;
895 FORALL i IN 1 .. L_DETAIL_TAX_LINE_ID_TBL.count
896 UPDATE ZX_REP_TRX_JX_EXT_T
897 SET ATTRIBUTE10 = L_ATTRIBUTE10_TBL(i),
898 ATTRIBUTE1 = L_ATTRIBUTE1_TBL(i),
899 ATTRIBUTE2 = L_ATTRIBUTE2_TBL(i),
900 ATTRIBUTE3 = L_ATTRIBUTE3_TBL(i),
901 ATTRIBUTE4 = L_ATTRIBUTE4_TBL(i),
902 ATTRIBUTE5 = L_ATTRIBUTE5_TBL(i),
903 ATTRIBUTE6 = L_ATTRIBUTE6_TBL(i),
904 ATTRIBUTE7 = L_ATTRIBUTE7_TBL(i),
905 ATTRIBUTE14 = L_ATTRIBUTE14_TBL(i),
906 ATTRIBUTE15 = L_ATTRIBUTE15_TBL(i),
907 ATTRIBUTE16 = L_ATTRIBUTE16_TBL(i),
908 ATTRIBUTE17 = L_ATTRIBUTE17_TBL(i),
909 ATTRIBUTE18 = L_ATTRIBUTE18_TBL(i),
910 ATTRIBUTE19 = L_ATTRIBUTE19_TBL(i),
911 attribute25 = l_attribute25_tbl(i), --Bug 5510822
912 attribute26 = l_attribute26_tbl(i)--Emea Changes
913 WHERE detail_tax_line_id = L_DETAIL_TAX_LINE_ID_TBL(i)
914 and request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
915
916 --exit when get_rep_entity_info_cur%NOTFOUND;
917
918 IF ( g_level_unexpected>= g_current_runtime_level ) THEN
919 FND_LOG.STRING(g_level_unexpected, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JE_AP',
920 'After Updating ZX_REP_TRX_JX_EXT_T' );
921 END IF;
922 ELSE
923 EXIT ;
924 END IF ;
925
926 end loop;
927
928 close get_rep_entity_info_cur ;
929
930
931 END IF;
932 commit;
933 IF (g_level_unexpected >= g_current_runtime_level ) THEN
934 FND_LOG.STRING(g_level_unexpected,
935 'ZX.TRL.je_tax_extract.populate_je_ap.END',
936 'je_tax_extract.populate_je_ap(-)');
937 END IF;
938
939 EXCEPTION
940 WHEN OTHERS THEN
941
942 l_err_msg := substrb(SQLERRM,1,120);
943 arp_standard.debug('EXCEPTION raised in ' ||'POPULATE_JE_AP: ' ||SQLCODE ||':'||l_err_msg);
944
945 END populate_je_ap;
946
947 /*===========================================================================+
948 | PUBLIC PROCEDURE |
949 | POPULATE_JE_GL |
950 | Type : Public |
951 | Pre-req : None |
952 | Function : |
953 | This procedure calls the API to select the JE specific data from |
954 | JE GL tables. |
955 | |
956 | Called from |
957 | |
958 | Parameters : |
959 | IN: P_TRL_GLOBAL_VARIABLES_REC ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE|
960 | |
961 | |
962 | MODIFICATION HISTORY |
963 | 22-MAY-2006 VSDOSHI Created |
967
964 | |
965 | |
966 +===========================================================================*/
968 PROCEDURE POPULATE_JE_GL
969 (
970 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
971 )
972
973 IS
974
975 --Bug 5636632
976 TYPE ATTRIBUTE1_TBL is TABLE OF
977 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
978
979 TYPE ATTRIBUTE2_TBL is TABLE OF
980 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE2%TYPE INDEX BY BINARY_INTEGER;
981
982 TYPE ATTRIBUTE3_TBL is TABLE OF
983 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE3%TYPE INDEX BY BINARY_INTEGER;
984
985 TYPE ATTRIBUTE4_TBL is TABLE OF
986 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE4%TYPE INDEX BY BINARY_INTEGER;
987
988 TYPE ATTRIBUTE5_TBL is TABLE OF
989 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE5%TYPE INDEX BY BINARY_INTEGER;
990
991 TYPE ATTRIBUTE6_TBL is TABLE OF
992 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE6%TYPE INDEX BY BINARY_INTEGER;
993
994 TYPE ATTRIBUTE7_TBL is TABLE OF
995 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE7%TYPE INDEX BY BINARY_INTEGER;
996
997 TYPE ATTRIBUTE25_TBL is TABLE OF
998 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE25%TYPE INDEX BY BINARY_INTEGER;
999
1000 TYPE ATTRIBUTE26_TBL is TABLE OF
1001 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE26%TYPE INDEX BY BINARY_INTEGER;
1002
1003 l_attribute1_tbl ATTRIBUTE1_TBL;
1004 l_attribute2_tbl ATTRIBUTE2_TBL;
1005 l_attribute3_tbl ATTRIBUTE3_TBL;
1006 l_attribute4_tbl ATTRIBUTE4_TBL;
1007 l_attribute5_tbl ATTRIBUTE5_TBL;
1008 l_attribute6_tbl ATTRIBUTE6_TBL;
1009 l_attribute7_tbl ATTRIBUTE7_TBL;
1010 l_attribute25_tbl ATTRIBUTE25_TBL;
1011 l_attribute26_tbl ATTRIBUTE26_TBL;
1012
1013 l_attribute1_tmp_tbl ATTRIBUTE1_TBL;
1014 l_attribute2_tmp_tbl ATTRIBUTE2_TBL;
1015 l_attribute3_tmp_tbl ATTRIBUTE3_TBL;
1016 l_attribute4_tmp_tbl ATTRIBUTE4_TBL;
1017 l_attribute5_tmp_tbl ATTRIBUTE5_TBL;
1018 l_attribute6_tmp_tbl ATTRIBUTE6_TBL;
1019 l_attribute7_tmp_tbl ATTRIBUTE7_TBL;
1020
1021 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
1022 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
1023 l_tax_status_code_tbl ZX_EXTRACT_PKG.TAX_STATUS_CODE_TBL;
1024 l_trx_business_category_tbl ZX_EXTRACT_PKG.TRX_BUSINESS_CATEGORY_TBL;
1025 l_document_sub_type_tbl ZX_EXTRACT_PKG.DOCUMENT_SUB_TYPE_TBL;
1026 l_tax_rate_id_tbl ZX_EXTRACT_PKG.tax_rate_id_tbl ;
1027 L_COUNT NUMBER ;
1028 C_LINES_PER_COMMIT Number:=1000;
1029
1030 CURSOR get_reporting_code_value(
1031 p_entity_id zx_report_codes_assoc.entity_id%TYPE ,
1032 p_reporting_type zx_reporting_types_b.reporting_type_code%TYPE ) IS
1033 SELECT assoc.reporting_code_char_value
1034 FROM zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
1035 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
1036 AND assoc.entity_id = p_entity_id
1037 AND assoc.entity_code = 'ZX_RATES'
1038 AND rep_type.reporting_type_code = p_reporting_type ;
1039
1040 cursor get_rep_entity_info_cur is
1041 SELECT detail_tax_line_id,
1042 itf1.tax_status_code,
1043 itf1.trx_business_category,
1044 itf1.document_sub_type,
1045 itf1.TAX_RATE_ID,
1046 (SELECT assoc.reporting_code_char_value FROM
1047 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
1048 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
1049 AND itf1.TAX_RATE_ID = assoc.entity_id
1050 AND assoc.entity_code = 'ZX_RATES'
1051 AND (assoc.EFFECTIVE_TO is null or assoc.EFFECTIVE_TO > sysdate)
1052 AND rep_type.reporting_type_code
1053 IN ('CZ_TAX_ORIGIN','HU_TAX_ORIGIN','PL_TAX_ORIGIN','CH_VAT_REGIME')
1054 ) ,
1055 (SELECT assoc.reporting_code_char_value FROM
1056 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
1057 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
1058 AND itf1.TAX_RATE_ID = assoc.entity_id
1059 AND assoc.entity_code = 'ZX_RATES'
1060 AND (assoc.EFFECTIVE_TO is null or assoc.EFFECTIVE_TO > sysdate)
1061 AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE')
1062 FROM zx_rep_trx_detail_t itf1
1063 WHERE itf1.application_id = 101
1064 AND itf1.entity_code = 'GL_JE_LINES'
1065 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1066
1067 BEGIN
1068
1069 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1070 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1071 FND_LOG.STRING(g_level_unexpected,
1072 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1073 'je_tax_extract.populate_je_gl(+)');
1074
1075 FND_LOG.STRING(g_level_unexpected,
1076 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1077 'Before insertion into ZX_REP_TRX_JX_EXT_T - 1');
1078 END IF;
1079
1080 SELECT detail_tax_line_id
1081 BULK COLLECT INTO l_detail_tax_line_id_tbl
1082 FROM zx_rep_trx_detail_t itf1
1083 WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1084
1085 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1086 FND_LOG.STRING(g_level_unexpected,
1087 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1091 IF l_detail_tax_line_id_tbl.count <> 0 THEN
1088 'Count Before insertion - 1'||to_char(l_detail_tax_line_id_tbl.count));
1089 END IF;
1090
1092
1093 INSERT INTO ZX_REP_TRX_JX_EXT_T
1094 (detail_tax_line_ext_id,
1095 detail_tax_line_id,
1096 created_by,
1097 creation_date,
1098 last_updated_by,
1099 last_update_date,
1100 last_update_login,
1101 attribute24,
1102 request_id)
1103 SELECT zx_rep_trx_jx_ext_t_s.nextval,
1104 itf1.detail_tax_line_id,
1105 fnd_global.user_id,
1106 sysdate,
1107 fnd_global.user_id,
1108 sysdate,
1109 fnd_global.login_id,
1110 gjl.tax_type_code,
1111 P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
1112 FROM gl_je_lines gjl ,
1113 zx_rep_trx_detail_t itf1
1114 WHERE itf1.application_id = 101
1115 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
1116 AND itf1.trx_id = gjl.je_header_id
1117 AND itf1.trx_line_id = gjl.je_line_num;
1118
1119 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1120 FND_LOG.STRING(g_level_unexpected,
1121 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1122 'No. of rows inserted into ZX_REP_TRX_JX_EXT_T - 1'||to_char(sql%ROWCOUNT));
1123 END IF;
1124
1125 open get_rep_entity_info_cur ;
1126
1127 Loop
1128
1129 Fetch get_rep_entity_info_cur
1130 bulk collect into
1131 L_DETAIL_TAX_LINE_ID_TBL,
1132 L_TAX_STATUS_CODE_TBL,
1133 L_TRX_BUSINESS_CATEGORY_TBL,
1134 L_DOCUMENT_SUB_TYPE_TBL,
1135 L_TAX_RATE_ID_TBL ,
1136 L_ATTRIBUTE25_TBL,
1137 L_ATTRIBUTE26_TBL
1138 LIMIT C_LINES_PER_COMMIT;
1139
1140 l_count := nvl(L_DETAIL_TAX_LINE_ID_TBL.COUNT,0);
1141
1142 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1143 FND_LOG.STRING(g_level_unexpected,
1144 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1145 'l_count : '||l_count);
1146 END IF;
1147
1148 FOR i IN 1..l_count
1149 LOOP
1150 IF L_ATTRIBUTE1_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1151 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1152 ELSE
1153 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_LOCATION');
1154 FETCH get_reporting_code_value INTO L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1155 IF ( get_reporting_code_value%NOTFOUND ) THEN
1156 L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1157 END IF ;
1158 CLOSE get_reporting_code_value;
1159 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1160 END IF ;
1161
1162 IF L_ATTRIBUTE2_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1163 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1164 ELSE
1165 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_TAXABLE_BOX');
1166 FETCH get_reporting_code_value INTO L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1167 IF ( get_reporting_code_value%NOTFOUND ) THEN
1168 L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1169 END IF ;
1170 CLOSE get_reporting_code_value;
1171 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1172 END IF ;
1173
1174 IF L_ATTRIBUTE3_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1175 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1176 ELSE
1177 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_REC_TAX_BOX');
1178 FETCH get_reporting_code_value INTO L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1179 IF ( get_reporting_code_value%NOTFOUND ) THEN
1180 L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1181 END IF ;
1182 CLOSE get_reporting_code_value;
1183 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1184 END IF ;
1185
1186 IF L_ATTRIBUTE4_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1187 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1188 ELSE
1189 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_TTL_TAXABLE_BOX');
1190 FETCH get_reporting_code_value INTO L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1191 IF ( get_reporting_code_value%NOTFOUND ) THEN
1192 L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1193 END IF ;
1194 CLOSE get_reporting_code_value;
1195 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1196 END IF ;
1197
1198 IF L_ATTRIBUTE5_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1199 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1200 ELSE
1201 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAXABLE');
1202 FETCH get_reporting_code_value INTO L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1203 IF ( get_reporting_code_value%NOTFOUND ) THEN
1204 L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1205 END IF ;
1206 CLOSE get_reporting_code_value;
1207 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1208 END IF ;
1209
1210 IF L_ATTRIBUTE6_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1211 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1212 ELSE
1213 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_NON_REC_TAXABLE');
1214 FETCH get_reporting_code_value INTO L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1218 CLOSE get_reporting_code_value;
1215 IF ( get_reporting_code_value%NOTFOUND ) THEN
1216 L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1217 END IF ;
1219 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1220 END IF ;
1221
1222 IF L_ATTRIBUTE7_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1223 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1224 ELSE
1225 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAX_BOX');
1226 FETCH get_reporting_code_value INTO L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1227 IF ( get_reporting_code_value%NOTFOUND ) THEN
1228 L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1229 END IF ;
1230 CLOSE get_reporting_code_value;
1231 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1232 END IF ;
1233
1234
1235 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1236 FND_LOG.STRING(g_level_unexpected,
1237 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1238 'Displaying the Vlaues for attributes : i :'||i);
1239 FND_LOG.STRING(g_level_unexpected,
1240 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1241 'L_DETAIL_TAX_LINE_ID_TBL(i)'||L_DETAIL_TAX_LINE_ID_TBL(i));
1242 FND_LOG.STRING(g_level_unexpected,
1243 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1244 'L_ATTRIBUTE1_TBL(i) :'||L_ATTRIBUTE1_TBL(i));
1245 FND_LOG.STRING(g_level_unexpected,
1246 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1247 'L_ATTRIBUTE2_TBL(i) :'||L_ATTRIBUTE2_TBL(i));
1248 FND_LOG.STRING(g_level_unexpected,
1249 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1250 'L_ATTRIBUTE3_TBL(i) :'||L_ATTRIBUTE3_TBL(i));
1251 FND_LOG.STRING(g_level_unexpected,
1252 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1253 'L_ATTRIBUTE4_TBL(i) :'||L_ATTRIBUTE4_TBL(i));
1254 FND_LOG.STRING(g_level_unexpected,
1255 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1256 'L_ATTRIBUTE5_TBL(i) :'||L_ATTRIBUTE5_TBL(i));
1257 FND_LOG.STRING(g_level_unexpected,
1258 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1259 'L_ATTRIBUTE6_TBL(i) :'||L_ATTRIBUTE6_TBL(i));
1260 FND_LOG.STRING(g_level_unexpected,
1261 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1262 'L_ATTRIBUTE7_TBL(i) :'||L_ATTRIBUTE7_TBL(i));
1263 FND_LOG.STRING(g_level_unexpected,
1264 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1265 'L_TAX_STATUS_CODE_TBL(i) :'||L_TAX_STATUS_CODE_TBL(i));
1266 FND_LOG.STRING(g_level_unexpected,
1267 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1268 'L_TRX_BUSINESS_CATEGORY_TBL(i) :'||L_TRX_BUSINESS_CATEGORY_TBL(i));
1269 FND_LOG.STRING(g_level_unexpected,
1270 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1271 'L_DOCUMENT_SUB_TYPE_TBL(i) :'||L_DOCUMENT_SUB_TYPE_TBL(i));
1272 END IF;
1273
1274 END LOOP ;
1275
1276 IF ( l_count > 0 ) THEN
1277
1278 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1279 FND_LOG.STRING(g_level_unexpected,
1280 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1281 'Before Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained -GL ' );
1282 END IF;
1283
1284 FORALL i IN 1 .. l_count
1285 UPDATE ZX_REP_TRX_JX_EXT_T
1286 SET
1287 TAX_STATUS_MNG = L_TAX_STATUS_CODE_TBL(i),
1288 TRX_BUSINESS_CATEGORY_MNG = L_TRX_BUSINESS_CATEGORY_TBL(i),
1289 DOCUMENT_SUB_TYPE_MNG = L_DOCUMENT_SUB_TYPE_TBL(i),
1290 ATTRIBUTE1 = L_ATTRIBUTE1_TBL(i),
1291 ATTRIBUTE2 = L_ATTRIBUTE2_TBL(i),
1292 ATTRIBUTE3 = L_ATTRIBUTE3_TBL(i),
1293 ATTRIBUTE4 = L_ATTRIBUTE4_TBL(i),
1294 ATTRIBUTE5 = L_ATTRIBUTE5_TBL(i),
1295 ATTRIBUTE6 = L_ATTRIBUTE6_TBL(i),
1296 ATTRIBUTE7 = L_ATTRIBUTE7_TBL(i),
1297 attribute25 = L_ATTRIBUTE25_TBL(i),
1298 attribute26 = L_ATTRIBUTE26_TBL(i)
1299 WHERE detail_tax_line_id = L_DETAIL_TAX_LINE_ID_TBL(i) and
1300 request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1301
1302 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1303 FND_LOG.STRING(g_level_unexpected,
1304 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1305 'After Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained ' );
1306 END IF;
1307 -- exit when get_rep_entity_info_cur%NOTFOUND;
1308 ELSE
1309 EXIT ;
1310 END IF ;
1311
1312 end loop;
1313
1314 close get_rep_entity_info_cur ;
1315
1316 END IF ;
1317
1318 commit;
1319 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1320 FND_LOG.STRING(g_level_unexpected,
1321 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.END',
1322 'je_tax_extract.populate_je_gl(-)');
1323 END IF;
1324
1325
1326 EXCEPTION
1327 WHEN OTHERS THEN
1328
1329 l_err_msg := substrb(SQLERRM,1,120);
1330 arp_standard.debug('EXCEPTION raised in ' ||'POPULATE_JE_GL: ' ||SQLCODE ||':'||l_err_msg);
1331
1332 END populate_je_gl;
1333
1334 END ZX_JE_EXTRACT_PKG;