[Home] [Help]
PACKAGE BODY: APPS.ZX_JE_EXTRACT_PKG
Source
1 PACKAGE BODY ZX_JE_EXTRACT_PKG AS
2 /* $Header: zxriextrajeppvtb.pls 120.26.12020000.2 2013/03/18 10:37:35 msakalab ship $ */
3
4 -----------------------------------------
5 --Private Variable Declarations
6
7 -----------------------------------------
8
9 -----------------------------------------
10
11 --Private Methods Declarations
12 -----------------------------------------
13
14
15 PG_DEBUG varchar2(1) ;
16 l_err_msg varchar2(120);
17
18
19 -----------------------------------------
20 --Public Variable Declarations
21 -----------------------------------------
22 --
23 g_current_runtime_level NUMBER ;
24 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
25 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
26 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
27 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
28 g_error_buffer VARCHAR2(100);
29 -----------------------------------------
30 --Public Methods Declarations
31 -----------------------------------------
32
33 /*===========================================================================+
34 | PRIVATE PROCEDURE |
35 | POPULATE_JE_AR |
36 | Type : Private to the Package |
37 | Pre-req : None |
38 | Function : |
39 | This procedure populates the reporting code and description |
40 | for a particular tax line. |
41 | |
42 | Called from POPULATE_JE_AR and POPULATE_JE_AP. |
43 | |
44 | Parameters : |
45 | IN: p_tax_line_id zx_lines.tax_line_id%TYPE |
46 | IN OUT p_rep_code zx_reporting_codes_b.reporting_code_char_value%TYPE |
47 | IN OUT p_rep_name zx_reporting_codes_tl.reporting_code_name%TYPE |
48 +===========================================================================*/
49
50 Procedure get_emea_rep_code_name
51 (p_tax_line_id IN zx_lines.tax_line_id%TYPE,
52 p_rep_code IN OUT NOCOPY zx_reporting_codes_b.reporting_code_char_value%TYPE,
53 p_rep_name IN OUT NOCOPY zx_reporting_codes_tl.reporting_code_name%TYPE
54 )
55 IS
56
57 /*----------------------------------------+
58 | Cursor to retrieve reporting code for |
59 | a given taxline based on hierarchy |
60 +----------------------------------------*/
61 CURSOR get_lgl_msg IS
62 SELECT v1.reporting_code_name, v1.reporting_code_char_value
63 FROM
64 (SELECT v.reporting_code_name, v.reporting_code_char_value
65 FROM (
66 SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 1 order_num
67 FROM zx_report_codes_assoc rep_assoc,
68 zx_reporting_types_b rep_types,
69 zx_reporting_codes_vl rep_codes,
70 zx_lines zxl
71 WHERE zxl.tax_line_id = p_tax_line_id
72 AND rep_types.reporting_type_code = 'INTRA_EU_VAT_TRANSACTION_TYPE'
73 AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
74 AND rep_assoc.entity_id = NVL(zxl.direct_rate_result_id, zxl.rate_result_id)
75 AND rep_assoc.entity_code = 'ZX_PROCESS_RESULTS'
76 AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
77 NVL(rep_assoc.effective_to, zxl.trx_date)
78 AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
79 AND ROWNUM=1
80 UNION
81 SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 2 order_num
82 FROM zx_report_codes_assoc rep_assoc,
83 zx_reporting_types_b rep_types,
84 zx_reporting_codes_vl rep_codes,
85 zx_lines zxl
86 WHERE zxl.tax_line_id = p_tax_line_id
87 AND rep_types.reporting_type_code = 'INTRA_EU_VAT_TRANSACTION_TYPE'
88 AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
89 AND rep_assoc.entity_id = zxl.tax_rate_id
90 AND rep_assoc.entity_code = 'ZX_RATES'
91 AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
92 NVL(rep_assoc.effective_to, zxl.trx_date)
93 AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
94 AND ROWNUM = 1
95 UNION
96 SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 3 order_num
97 FROM zx_report_codes_assoc rep_assoc,
98 zx_reporting_types_b rep_types,
99 zx_reporting_codes_vl rep_codes,
100 zx_lines zxl
101 WHERE zxl.tax_line_id = p_tax_line_id
102 AND rep_types.reporting_type_code = 'INTRA_EU_VAT_TRANSACTION_TYPE'
103 AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
104 AND rep_assoc.entity_id = zxl.status_result_id
105 AND rep_assoc.entity_code = 'ZX_PROCESS_RESULTS'
106 AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
107 NVL(rep_assoc.effective_to, zxl.trx_date)
108 AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
109 AND ROWNUM = 1
110 ) v
111 WHERE v.reporting_code_name IS NOT NULL
112 ORDER BY v.order_num,v.reporting_code_name) v1
113 WHERE ROWNUM = 1;
114
115
116 BEGIN
117
118 p_rep_code := TO_CHAR(NULL);
119 p_rep_name := TO_CHAR(NULL);
120
121 OPEN get_lgl_msg;
122 FETCH get_lgl_msg INTO p_rep_name, p_rep_code;
123 CLOSE get_lgl_msg;
124
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 p_rep_code := TO_CHAR(NULL);
129 p_rep_name := TO_CHAR(NULL);
130 IF get_lgl_msg%ISOPEN THEN
131 CLOSE get_lgl_msg;
132 END IF;
133 IF ( g_level_unexpected >= g_current_runtime_level) THEN
134 FND_LOG.STRING(g_level_unexpected,'ZX.TRL.ZX_JE_EXTRACT_PKG.get_emea_rep_code_name',SQLERRM);
135 END IF;
136 END;
137
138 /*===========================================================================+
139 | PUBLIC PROCEDURE |
140 | POPULATE_JE_AR |
141 | Type : Public |
142 | Pre-req : None |
143 | Function : |
144 | This procedure calls the API to select the JE specific data from |
145 | JE receivables tables. |
146 | |
147 | Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
148 | |
149 | Parameters : |
150 | IN: P_TRL_GLOBAL_VARIABLES_REC ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE|
151 | |
152 | |
153 | MODIFICATION HISTORY |
154 | 13-FEB-2006 RJREDDY Created |
155 | |
156 | |
157 +===========================================================================*/
158
159 PROCEDURE POPULATE_JE_AR
160 (
161 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
162 )
163
164 IS
165
166
167 TYPE ATTRIBUTE1_TBL is TABLE OF
168 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
169
170 TYPE ATTRIBUTE2_TBL is TABLE OF
171 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE2%TYPE INDEX BY BINARY_INTEGER;
172
173 TYPE ATTRIBUTE3_TBL is TABLE OF
174 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE3%TYPE INDEX BY BINARY_INTEGER;
175
176 TYPE ATTRIBUTE4_TBL is TABLE OF
177 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE4%TYPE INDEX BY BINARY_INTEGER;
178
179 TYPE ATTRIBUTE5_TBL is TABLE OF
180 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE5%TYPE INDEX BY BINARY_INTEGER;
181
182 TYPE ATTRIBUTE6_TBL is TABLE OF
183 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE6%TYPE INDEX BY BINARY_INTEGER;
184
185 TYPE ATTRIBUTE7_TBL is TABLE OF
186 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE7%TYPE INDEX BY BINARY_INTEGER;
187
188 TYPE ATTRIBUTE8_TBL is TABLE OF
189 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE8%TYPE INDEX BY BINARY_INTEGER;
190
191 TYPE ATTRIBUTE9_TBL is TABLE OF
192 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE9%TYPE INDEX BY BINARY_INTEGER;
193
194 TYPE ATTRIBUTE11_TBL is TABLE OF
195 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE11%TYPE INDEX BY BINARY_INTEGER;
196
197 TYPE ATTRIBUTE12_TBL is TABLE OF
198 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE12%TYPE INDEX BY BINARY_INTEGER;
199
200 TYPE ATTRIBUTE13_TBL is TABLE OF
201 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE13%TYPE INDEX BY BINARY_INTEGER;
202
203 TYPE ATTRIBUTE23_TBL is TABLE OF
204 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE23%TYPE INDEX BY BINARY_INTEGER;
205
206 TYPE ATTRIBUTE24_TBL is TABLE OF
207 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE24%TYPE INDEX BY BINARY_INTEGER;
208
209 TYPE ATTRIBUTE25_TBL is TABLE OF
210 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE25%TYPE INDEX BY BINARY_INTEGER;
211
212 TYPE ATTRIBUTE26_TBL is TABLE OF
213 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE26%TYPE INDEX BY BINARY_INTEGER;
214
215 TYPE ATTRIBUTE27_TBL is TABLE OF
216 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE27%TYPE INDEX BY BINARY_INTEGER;
217
218 TYPE ATTRIBUTE28_TBL is TABLE OF
219 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE28%TYPE INDEX BY BINARY_INTEGER;
220
221 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
222 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
223 l_tax_status_code_tbl ZX_EXTRACT_PKG.TAX_STATUS_CODE_TBL;
224 l_trx_business_category_tbl ZX_EXTRACT_PKG.TRX_BUSINESS_CATEGORY_TBL;
225 l_document_sub_type_tbl ZX_EXTRACT_PKG.DOCUMENT_SUB_TYPE_TBL;
226 l_tax_rate_id_tbl ZX_EXTRACT_PKG.tax_rate_id_tbl ;
227 l_taxline_id_tbl ZX_EXTRACT_PKG.TAX_LINE_ID_TBL;
228
229 l_attribute1_tbl ATTRIBUTE1_TBL;
230 l_attribute2_tbl ATTRIBUTE2_TBL;
231 l_attribute3_tbl ATTRIBUTE3_TBL;
232 l_attribute4_tbl ATTRIBUTE4_TBL;
233 l_attribute5_tbl ATTRIBUTE5_TBL;
234 l_attribute6_tbl ATTRIBUTE6_TBL;
235 l_attribute7_tbl ATTRIBUTE7_TBL;
236
237 l_attribute1_tmp_tbl ATTRIBUTE1_TBL;
238 l_attribute2_tmp_tbl ATTRIBUTE2_TBL;
239 l_attribute3_tmp_tbl ATTRIBUTE3_TBL;
240 l_attribute4_tmp_tbl ATTRIBUTE4_TBL;
241 l_attribute5_tmp_tbl ATTRIBUTE5_TBL;
242 l_attribute6_tmp_tbl ATTRIBUTE6_TBL;
243 l_attribute7_tmp_tbl ATTRIBUTE7_TBL;
244
245 l_attribute8_tbl ATTRIBUTE8_TBL;
246 l_attribute9_tbl ATTRIBUTE9_TBL;
247 l_attribute11_tbl ATTRIBUTE11_TBL;
248 l_attribute12_tbl ATTRIBUTE12_TBL;
249 l_attribute13_tbl ATTRIBUTE23_TBL;
250 l_attribute23_tbl ATTRIBUTE23_TBL;
251 l_attribute24_tbl ATTRIBUTE24_TBL;
252 l_attribute25_tbl ATTRIBUTE25_TBL;
253 l_attribute26_tbl ATTRIBUTE26_TBL;
254 l_attribute27_tbl ATTRIBUTE27_TBL;
255 l_attribute28_tbl ATTRIBUTE28_TBL;
256 C_LINES_PER_COMMIT Number:=1000;
257 l_count NUMBER ;
258
259 cursor get_rep_entity_info_cur is
260 SELECT detail_tax_line_id,
261 itf1.tax_status_code,
262 itf1.trx_business_category,
263 itf1.document_sub_type,
264 itf1.TAX_RATE_ID,
265 (SELECT assoc.reporting_code_char_value
266 FROM zx_reporting_types_b rep_type,
267 zx_report_codes_assoc assoc
268 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
269 AND itf1.TAX_RATE_ID = assoc.entity_id
270 AND assoc.entity_code = 'ZX_RATES'
271 AND (assoc.EFFECTIVE_TO is null
272 or assoc.EFFECTIVE_TO >= DECODE(itf1.EVENT_CLASS_CODE,'CREDIT_MEMO',itf1.TAX_DETERMINE_DATE,
273 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
274 itf1.tax_invoice_date,
275 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
276 itf1.trx_date,
277 NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
278 itf1.gl_date,
279 sysdate
280 )
281 )
282 )
283 )
284 )
285 AND rep_type.reporting_type_code IN ('CZ_TAX_ORIGIN','HU_TAX_ORIGIN','PL_TAX_ORIGIN','CH_VAT_REGIME')
286 ),
287 (SELECT assoc.reporting_code_char_value
288 FROM zx_reporting_types_b rep_type,
289 zx_report_codes_assoc assoc
290 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
291 AND itf1.TAX_RATE_ID = assoc.entity_id
292 AND assoc.entity_code = 'ZX_RATES'
293 AND DECODE(itf1.EVENT_CLASS_CODE,'CREDIT_MEMO',itf1.TAX_DETERMINE_DATE,
294 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
295 itf1.tax_invoice_date,
296 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
297 itf1.trx_date,
298 NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
299 itf1.gl_date,
300 sysdate
301 )
302 )
303 )
304 )
305 BETWEEN assoc.EFFECTIVE_FROM AND
306 NVL(assoc.EFFECTIVE_TO,
307 DECODE(itf1.EVENT_CLASS_CODE,'CREDIT_MEMO',itf1.TAX_DETERMINE_DATE,
308 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
309 itf1.tax_invoice_date,
310 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
311 itf1.trx_date,
312 NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
313 itf1.gl_date,
314 sysdate
315 )
316 )
317 )
318 )
319 )
320 AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE'),
321 itf1.TAX_LINE_ID
322 FROM zx_rep_trx_detail_t itf1
323 WHERE itf1.application_id = 222
324 AND itf1.extract_source_ledger = 'AR'
325 AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO','APP','ADJ')
326 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
327
328 --Bug 5636632
329 CURSOR get_reporting_code_value(
330 p_entity_id zx_report_codes_assoc.entity_id%TYPE ,
331 p_reporting_type zx_reporting_types_b.reporting_type_code%TYPE ) IS
332 SELECT assoc.reporting_code_char_value
333 FROM zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
334 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
335 AND assoc.entity_id = p_entity_id
336 AND assoc.entity_code = 'ZX_RATES'
337 AND rep_type.reporting_type_code = p_reporting_type ;
338
339 BEGIN
340
341 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
342 IF (g_level_statement >= g_current_runtime_level ) THEN
343 FND_LOG.STRING(g_level_statement,
344 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
345 'je_tax_extract.populate_je_ar(+)');
346 FND_LOG.STRING(g_level_statement,
347 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar',
348 'P_TAX_INVOICE_DATE_HIGH = '||P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH);
349 FND_LOG.STRING(g_level_statement,
350 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar',
351 'TRX_DATE_HIGH = '||P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH);
352 FND_LOG.STRING(g_level_statement,
353 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar',
354 'GL_DATE_HIGH = '||P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH);
355 END IF;
356
357 BEGIN
358 -- Directly populate the tax line id from Base Doc on which application is done.
359 UPDATE zx_rep_trx_detail_t dtl
360 set dtl.tax_line_id = (select min (tax_line_id)
361 from zx_lines lines
362 where lines.application_id = 222
363 and lines.trx_id = dtl.ADJUSTED_DOC_TRX_ID
364 and lines.trx_line_id = dtl.APPLIED_TO_TRX_LINE_ID
365 and lines.tax_rate_id = dtl.tax_rate_id
366 and nvl(lines.hq_estb_reg_number,fnd_api.g_miss_char) =
367 P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
368 )
369 WHERE dtl.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
370 and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
371 and dtl.APPLICATION_ID =222
372 and dtl.APPLIED_FROM_ENTITY_CODE = 'APP';
373
374 -- Directly populate the tax line id from Adjustment Doc
375 UPDATE zx_rep_trx_detail_t dtl
376 set dtl.tax_line_id = (select min (tax_line_id)
377 from zx_lines lines
378 where lines.application_id = 222
379 and lines.trx_id = dtl.ADJUSTED_DOC_TRX_ID
380 and lines.trx_line_id = dtl.APPLIED_TO_TRX_LINE_ID
381 AND lines.tax_rate_id = dtl.tax_rate_id
382 AND nvl(lines.hq_estb_reg_number,fnd_api.g_miss_char) =
383 P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
384 )
385 WHERE dtl.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
386 and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
387 and dtl.APPLICATION_ID =222
388 and dtl.EVENT_CLASS_CODE = 'ADJ';
389
390 EXCEPTION WHEN OTHERS THEN
391 IF ( g_level_statement>= g_current_runtime_level ) THEN
392 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JE_EXTRACT.POPULATE_JE_AR',
393 'Error in updating tax line id zx_rep_trx_detail_t: Error Message : '||substrb(SQLERRM,1,120) );
394 END IF;
395 END;
396
397
398 SELECT detail_tax_line_id
399 BULK COLLECT INTO l_detail_tax_line_id_tbl
400 FROM zx_rep_trx_detail_t itf1
401 WHERE itf1.application_id = 222
402 AND itf1.extract_source_ledger = 'AR'
403 AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO','APP','ADJ')
404 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
405
406
407 IF l_detail_tax_line_id_tbl.count <> 0 THEN
408
409 INSERT INTO ZX_REP_TRX_JX_EXT_T
410 (detail_tax_line_ext_id,
411 detail_tax_line_id,
412 attribute9,
413 attribute11,
414 attribute12,
415 attribute13,
416 attribute23,
417 created_by,
418 creation_date,
419 last_updated_by,
420 last_update_date,
421 last_update_login,
422 request_id)
423 SELECT zx_rep_trx_jx_ext_t_s.nextval,
424 itf1.detail_tax_line_id,
425 CASE WHEN (itf1.entity_code = 'TRANSACTIONS' AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')) THEN
426 (SELECT decode (ra_cust.global_attribute_category,
427 'JE.ES.ARXTWMAI.MODELO347PR', ra_cust.global_attribute2,
428 'JE.ES.ARXTWMAI.MODELO415_347PR', ra_cust.global_attribute2,
429 NULL)
430 FROM ra_customer_trx_all ra_cust
431 WHERE ra_cust.customer_trx_id = itf1.trx_id
432 AND ra_cust.set_of_books_id = itf1.ledger_id)
433 ELSE NULL
434 END ,
435 CASE WHEN (itf1.entity_code = 'TRANSACTIONS' AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')) THEN
436 (SELECT decode (ra_cust.global_attribute_category,
437 'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute3,
438 'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute3,
439 NULL)
440 FROM ra_customer_trx_all ra_cust
441 WHERE ra_cust.customer_trx_id = itf1.trx_id
442 AND ra_cust.set_of_books_id = itf1.ledger_id)
443 ELSE NULL
444 END ,
445 CASE WHEN (itf1.entity_code = 'TRANSACTIONS' AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')) THEN
446 (SELECT decode (ra_cust.global_attribute_category,
447 'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute4,
448 'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute4,
449 NULL)
450 FROM ra_customer_trx_all ra_cust
451 WHERE ra_cust.customer_trx_id = itf1.trx_id
452 AND ra_cust.set_of_books_id = itf1.ledger_id )
453 ELSE NULL
454 END ,
455 CASE WHEN (itf1.entity_code = 'TRANSACTIONS' AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')) THEN
456 (SELECT decode (ra_cust.global_attribute_category,
457 'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute5,
458 'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute5,
459 NULL)
460 FROM ra_customer_trx_all ra_cust
461 WHERE ra_cust.customer_trx_id = itf1.trx_id
462 AND ra_cust.set_of_books_id = itf1.ledger_id )
463 ELSE NULL
464 END ,
465 substr(itf1.trx_business_category,(instr(itf1.trx_business_category,'MOD',1,1)+3),
466 length(itf1.trx_business_category)),
467 fnd_global.user_id,
468 sysdate,
469 fnd_global.user_id,
470 sysdate,
471 fnd_global.login_id,
472 P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
473 FROM zx_rep_trx_detail_t itf1
474 WHERE itf1.application_id = 222
475 AND itf1.extract_source_ledger = 'AR'
476 AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO','APP','ADJ')
477 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
478
479 open get_rep_entity_info_cur ;
480
481 Loop
482
483 Fetch get_rep_entity_info_cur
484 bulk collect into
485 L_DETAIL_TAX_LINE_ID_TBL,
486 L_TAX_STATUS_CODE_TBL,
487 L_TRX_BUSINESS_CATEGORY_TBL,
488 L_DOCUMENT_SUB_TYPE_TBL,
489 L_TAX_RATE_ID_TBL ,
490 L_ATTRIBUTE25_TBL , --Bug 5510822
491 L_ATTRIBUTE26_TBL , --EMEA Changes
492 l_taxline_id_tbl
493 LIMIT C_LINES_PER_COMMIT;
494
495
496
497 l_count := nvl(L_DETAIL_TAX_LINE_ID_TBL.COUNT,0);
498
499 IF (g_level_statement >= g_current_runtime_level ) THEN
500 FND_LOG.STRING(g_level_statement,
501 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
502 'l_count : '||l_count);
503 END IF;
504
505 FOR i IN 1..l_count
506 LOOP
507
508 l_attribute28_tbl(i):=NULL;
509 l_attribute27_tbl(i):=NULL;
510 get_emea_rep_code_name(l_taxline_id_tbl(i),l_attribute28_tbl(i),l_attribute27_tbl(i));
511
512 IF L_ATTRIBUTE1_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
513 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
514 ELSE
515 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_LOCATION');
516 FETCH get_reporting_code_value INTO L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
517 IF ( get_reporting_code_value%NOTFOUND ) THEN
518 L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
519 END IF ;
520 CLOSE get_reporting_code_value;
521 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
522 END IF ;
523
524 IF L_ATTRIBUTE2_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
525 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
526 ELSE
527 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_TAXABLE_BOX');
528 FETCH get_reporting_code_value INTO L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
529 IF ( get_reporting_code_value%NOTFOUND ) THEN
530 L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
531 END IF ;
532 CLOSE get_reporting_code_value;
533 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
534 END IF ;
535
536 IF L_ATTRIBUTE3_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
537 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
538 ELSE
539 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_REC_TAX_BOX');
540 FETCH get_reporting_code_value INTO L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
541 IF ( get_reporting_code_value%NOTFOUND ) THEN
542 L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
543 END IF ;
544 CLOSE get_reporting_code_value;
545 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
546 END IF ;
547
548 IF L_ATTRIBUTE4_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
549 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
550 ELSE
551 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_TTL_TAXABLE_BOX');
552 FETCH get_reporting_code_value INTO L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
553 IF ( get_reporting_code_value%NOTFOUND ) THEN
554 L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
555 END IF ;
556 CLOSE get_reporting_code_value;
557 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
558 END IF ;
559
560 IF L_ATTRIBUTE5_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
561 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
562 ELSE
563 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAXABLE');
564 FETCH get_reporting_code_value INTO L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
565 IF ( get_reporting_code_value%NOTFOUND ) THEN
566 L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
567 END IF ;
568 CLOSE get_reporting_code_value;
569 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
570 END IF ;
571
572 IF L_ATTRIBUTE6_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
573 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
574 ELSE
575 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_NON_REC_TAXABLE');
576 FETCH get_reporting_code_value INTO L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
577 IF ( get_reporting_code_value%NOTFOUND ) THEN
578 L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
579 END IF ;
580 CLOSE get_reporting_code_value;
581 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
582 END IF ;
583
584 IF L_ATTRIBUTE7_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
585 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
586 ELSE
587 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAX_BOX');
588 FETCH get_reporting_code_value INTO L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
589 IF ( get_reporting_code_value%NOTFOUND ) THEN
590 L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
591 END IF ;
592 CLOSE get_reporting_code_value;
593 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
594 END IF ;
595
596 IF (g_level_statement >= g_current_runtime_level ) THEN
597 FND_LOG.STRING(g_level_statement,
598 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
599 'Displaying the Vlaues for attributes : i :'||i);
600 FND_LOG.STRING(g_level_statement,
601 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
602 'L_DETAIL_TAX_LINE_ID_TBL(i)'||L_DETAIL_TAX_LINE_ID_TBL(i));
603 FND_LOG.STRING(g_level_statement,
604 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
605 'L_ATTRIBUTE1_TBL(i) :'||L_ATTRIBUTE1_TBL(i));
606 FND_LOG.STRING(g_level_statement,
607 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
608 'L_ATTRIBUTE2_TBL(i) :'||L_ATTRIBUTE2_TBL(i));
609 FND_LOG.STRING(g_level_statement,
610 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
611 'L_ATTRIBUTE3_TBL(i) :'||L_ATTRIBUTE3_TBL(i));
612 FND_LOG.STRING(g_level_statement,
613 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
614 'L_ATTRIBUTE4_TBL(i) :'||L_ATTRIBUTE4_TBL(i));
615 FND_LOG.STRING(g_level_statement,
616 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
617 'L_ATTRIBUTE5_TBL(i) :'||L_ATTRIBUTE5_TBL(i));
618 FND_LOG.STRING(g_level_statement,
619 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
620 'L_ATTRIBUTE6_TBL(i) :'||L_ATTRIBUTE6_TBL(i));
621 FND_LOG.STRING(g_level_statement,
622 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
623 'L_ATTRIBUTE7_TBL(i) :'||L_ATTRIBUTE7_TBL(i));
624 FND_LOG.STRING(g_level_statement,
625 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
626 'L_ATTRIBUTE25_TBL(i) :'||L_ATTRIBUTE25_TBL(i));
627 FND_LOG.STRING(g_level_statement,
628 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
629 'L_ATTRIBUTE26_TBL(i) :'||L_ATTRIBUTE26_TBL(i));
630 FND_LOG.STRING(g_level_statement,
631 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
632 'L_ATTRIBUTE27_TBL(i) :'||L_ATTRIBUTE27_TBL(i));
633 FND_LOG.STRING(g_level_statement,
634 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
635 'L_ATTRIBUTE28_TBL(i) :'||L_ATTRIBUTE28_TBL(i));
636 END IF;
637
638 END LOOP ;
639
640 IF (g_level_statement >= g_current_runtime_level ) THEN
641 FND_LOG.STRING(g_level_statement,
642 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
643 'Before Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained ' );
644 END IF;
645
646 IF ( l_count > 0 ) THEN
647
648 FORALL i IN 1 .. L_DETAIL_TAX_LINE_ID_TBL.count
649 UPDATE ZX_REP_TRX_JX_EXT_T
650 SET
651 TAX_STATUS_MNG = L_TAX_STATUS_CODE_TBL(i),
652 TRX_BUSINESS_CATEGORY_MNG = L_TRX_BUSINESS_CATEGORY_TBL(i),
653 DOCUMENT_SUB_TYPE_MNG = L_DOCUMENT_SUB_TYPE_TBL(i),
654 ATTRIBUTE1 = L_ATTRIBUTE1_TBL(i),
655 ATTRIBUTE2 = L_ATTRIBUTE2_TBL(i),
656 ATTRIBUTE3 = L_ATTRIBUTE3_TBL(i),
657 ATTRIBUTE4 = L_ATTRIBUTE4_TBL(i),
658 ATTRIBUTE5 = L_ATTRIBUTE5_TBL(i),
659 ATTRIBUTE6 = L_ATTRIBUTE6_TBL(i),
660 ATTRIBUTE7 = L_ATTRIBUTE7_TBL(i),
661 attribute25 = L_ATTRIBUTE25_TBL(i), --Bug 5510822
662 attribute26 = L_ATTRIBUTE26_TBL(i), --EMEA Changes
663 attribute27 = L_ATTRIBUTE27_TBL(i),
664 attribute28 = L_ATTRIBUTE28_TBL(i)
665 WHERE detail_tax_line_id = L_DETAIL_TAX_LINE_ID_TBL(i) and
666 request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
667
668 IF (g_level_statement >= g_current_runtime_level ) THEN
669 FND_LOG.STRING(g_level_statement,
670 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.BEGIN',
671 'After Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained ' );
672 END IF;
673 -- exit when get_rep_entity_info_cur%NOTFOUND;
674 ELSE
675 EXIT ;
676 END IF ;
677
678 end loop;
679
680 close get_rep_entity_info_cur ;
681
682 END IF;
683 --commit; Bug 8262631
684 IF (g_level_statement >= g_current_runtime_level ) THEN
685 FND_LOG.STRING(g_level_statement,
686 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ar.END',
687 'je_tax_extract.populate_je_ar(-)');
688 END IF;
689
690
691 EXCEPTION
692 WHEN OTHERS THEN
693
694 l_err_msg := substrb(SQLERRM,1,120);
695 arp_standard.debug('EXCEPTION raised in ' ||'POPULATE_JE_AR: ' ||SQLCODE ||':'||l_err_msg);
696
697 END populate_je_ar;
698
699
700 /*===========================================================================+
701 | PUBLIC PROCEDURE |
702 | POPULATE_JE_AP |
703 | Type : Public |
704 | Pre-req : None |
705 | Function : |
706 | This procedure calls the API to select the JE specific data from |
707 | JE payables tables. Currently only JE_LOOKUP_INFO plug-in is called |
708 | inside. |
709 | |
710 | Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
711 | |
712 | Parameters : |
713 | |
714 | |
715 | MODIFICATION HISTORY |
716 | 13-FEB-2006 RJREDDY Created |
717 | |
718 | |
719 +===========================================================================*/
720
721
722 PROCEDURE POPULATE_JE_AP
723 (
724 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
725 )
726
727 IS
728 TYPE ATTRIBUTE1_TBL is TABLE OF
729 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
730 TYPE ATTRIBUTE2_TBL is TABLE OF
731 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE2%TYPE INDEX BY BINARY_INTEGER;
732 TYPE ATTRIBUTE3_TBL is TABLE OF
733 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE3%TYPE INDEX BY BINARY_INTEGER;
734 TYPE ATTRIBUTE4_TBL is TABLE OF
735 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE4%TYPE INDEX BY BINARY_INTEGER;
736 TYPE ATTRIBUTE5_TBL is TABLE OF
737 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE5%TYPE INDEX BY BINARY_INTEGER;
738 TYPE ATTRIBUTE6_TBL is TABLE OF
739 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE6%TYPE INDEX BY BINARY_INTEGER;
740 TYPE ATTRIBUTE7_TBL is TABLE OF
741 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE7%TYPE INDEX BY BINARY_INTEGER;
742 TYPE REPORTING_TYPE_ID_TBL is TABLE OF
743 ZX_REPORTING_TYPES_B.REPORTING_TYPE_ID%TYPE INDEX BY BINARY_INTEGER;
744 TYPE ATTRIBUTE8_TBL is TABLE OF
745 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE8%TYPE INDEX BY BINARY_INTEGER;
746 TYPE ATTRIBUTE10_TBL is TABLE OF
747 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE10%TYPE INDEX BY BINARY_INTEGER;
748 TYPE ATTRIBUTE11_TBL is TABLE OF
749 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE11%TYPE INDEX BY BINARY_INTEGER;
750 TYPE ATTRIBUTE12_TBL is TABLE OF
751 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE12%TYPE INDEX BY BINARY_INTEGER;
752 TYPE ATTRIBUTE13_TBL is TABLE OF
753 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE13%TYPE INDEX BY BINARY_INTEGER;
754 TYPE ATTRIBUTE14_TBL is TABLE OF
755 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE14%TYPE INDEX BY BINARY_INTEGER;
756 TYPE ATTRIBUTE15_TBL is TABLE OF
757 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE15%TYPE INDEX BY BINARY_INTEGER;
758 TYPE ATTRIBUTE16_TBL is TABLE OF
759 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE16%TYPE INDEX BY BINARY_INTEGER;
760 TYPE ATTRIBUTE17_TBL is TABLE OF
761 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE17%TYPE INDEX BY BINARY_INTEGER;
762 TYPE ATTRIBUTE18_TBL is TABLE OF
763 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE18%TYPE INDEX BY BINARY_INTEGER;
764 TYPE ATTRIBUTE19_TBL is TABLE OF
765 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE19%TYPE INDEX BY BINARY_INTEGER;
766 TYPE ATTRIBUTE20_TBL is TABLE OF
767 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE20%TYPE INDEX BY BINARY_INTEGER;
768 TYPE DETAIL_TAX_LINE_ID_TBL is TABLE OF
769 ZX_REP_TRX_JX_EXT_T.DETAIL_TAX_LINE_ID%TYPE INDEX BY BINARY_INTEGER;
770 TYPE ATTRIBUTE21_TBL is TABLE OF
771 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE21%TYPE INDEX BY BINARY_INTEGER;
772 TYPE ATTRIBUTE22_TBL is TABLE OF
773 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE22%TYPE INDEX BY BINARY_INTEGER;
774 TYPE ATTRIBUTE23_TBL is TABLE OF
775 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE23%TYPE INDEX BY BINARY_INTEGER;
776 TYPE ATTRIBUTE24_TBL is TABLE OF
777 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE24%TYPE INDEX BY BINARY_INTEGER;
778 TYPE ATTRIBUTE25_TBL is TABLE OF
779 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE25%TYPE INDEX BY BINARY_INTEGER;
780 TYPE ATTRIBUTE26_TBL is TABLE OF
781 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE26%TYPE INDEX BY BINARY_INTEGER;
782 TYPE ATTRIBUTE27_TBL is TABLE OF
783 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE27%TYPE INDEX BY BINARY_INTEGER;
784 TYPE ATTRIBUTE28_TBL is TABLE OF
785 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE28%TYPE INDEX BY BINARY_INTEGER;
786
787 l_detail_tax_line_id_tbl DETAIL_TAX_LINE_ID_TBL;
788 l_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
789 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
790 l_tax_rate_id_tbl ZX_EXTRACT_PKG.tax_rate_id_tbl ;
791
792 l_tax_status_code_tbl ZX_EXTRACT_PKG.TAX_STATUS_CODE_TBL;
793 l_trx_business_category_tbl ZX_EXTRACT_PKG.TRX_BUSINESS_CATEGORY_TBL;
794 l_document_sub_type_tbl ZX_EXTRACT_PKG.DOCUMENT_SUB_TYPE_TBL;
795 l_taxline_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
796 l_attribute1_tbl ATTRIBUTE1_TBL;
797 l_attribute2_tbl ATTRIBUTE2_TBL;
798 l_attribute3_tbl ATTRIBUTE3_TBL;
799 l_attribute4_tbl ATTRIBUTE4_TBL;
800 l_attribute5_tbl ATTRIBUTE5_TBL;
801 l_attribute6_tbl ATTRIBUTE6_TBL;
802 l_attribute7_tbl ATTRIBUTE7_TBL;
803
804 L_ATTRIBUTE1_TMP_TBL ATTRIBUTE1_TBL;
805 L_ATTRIBUTE2_TMP_TBL ATTRIBUTE2_TBL;
806 L_ATTRIBUTE3_TMP_TBL ATTRIBUTE3_TBL;
807 L_ATTRIBUTE4_TMP_TBL ATTRIBUTE4_TBL;
808 L_ATTRIBUTE5_TMP_TBL ATTRIBUTE5_TBL;
809 L_ATTRIBUTE6_TMP_TBL ATTRIBUTE6_TBL;
810 L_ATTRIBUTE7_TMP_TBL ATTRIBUTE7_TBL;
811
812 l_attribute8_tbl ATTRIBUTE8_TBL;
813 l_attribute10_tbl ATTRIBUTE10_TBL;
814 l_attribute11_tbl ATTRIBUTE11_TBL;
815 l_attribute12_tbl ATTRIBUTE12_TBL;
816 l_attribute13_tbl ATTRIBUTE13_TBL;
817 l_attribute14_tbl ATTRIBUTE14_TBL;
818 l_attribute15_tbl ATTRIBUTE15_TBL;
819 l_attribute16_tbl ATTRIBUTE16_TBL;
820 l_attribute17_tbl ATTRIBUTE17_TBL;
821 l_attribute18_tbl ATTRIBUTE18_TBL;
822 l_attribute19_tbl ATTRIBUTE19_TBL;
823 l_attribute20_tbl ATTRIBUTE20_TBL;
824 l_attribute21_tbl ATTRIBUTE21_TBL;
825 l_attribute22_tbl ATTRIBUTE22_TBL;
826 l_attribute23_tbl ATTRIBUTE23_TBL;
827 l_attribute24_tbl ATTRIBUTE24_TBL;
828 l_attribute25_tbl ATTRIBUTE25_TBL;
829 l_attribute26_tbl ATTRIBUTE26_TBL;
830 l_attribute27_tbl ATTRIBUTE27_TBL;
831 l_attribute28_tbl ATTRIBUTE28_TBL;
832 C_LINES_PER_COMMIT Number:=1000;
833 l_count NUMBER ;
834
835
836 cursor get_rep_entity_info_cur is
837 SELECT
838 detail_tax_line_id,
839 fsp.vat_country_code,
840 tax_rate_id ,
841 decode ( hr_loc.global_attribute_category,
842 'JE.ES.PERWSLOC.PRL_NO', hr_loc.global_attribute1,
843 NULL ),
844 decode ( hr_loc.global_attribute_category,
845 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute1,
846 NULL ),
847 decode ( hr_loc.global_attribute_category,
848 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute2,
849 NULL ),
850 decode ( hr_loc.global_attribute_category,
851 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute3,
852 NULL ),
853 decode ( hr_loc.global_attribute_category,
854 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute4,
855 NULL ),
856 decode ( hr_loc.global_attribute_category,
857 'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute5,
858 NULL ),
859 (SELECT assoc.reporting_code_char_value FROM
860 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
861 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
862 AND itf1.TAX_RATE_ID = assoc.entity_id
863 AND assoc.entity_code = 'ZX_RATES'
864 AND (assoc.EFFECTIVE_TO is null
865 or assoc.EFFECTIVE_TO >= NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
866 itf1.tax_invoice_date,
867 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
868 itf1.trx_date,
869 NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
870 itf1.gl_date,
871 sysdate
872 )
873 )
874 )
875 )
876 AND rep_type.reporting_type_code IN ('CZ_TAX_ORIGIN','HU_TAX_ORIGIN','PL_TAX_ORIGIN','CH_VAT_REGIME')
877 ),
878 (SELECT assoc.reporting_code_char_value FROM
879 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
880 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
881 AND itf1.TAX_RATE_ID = assoc.entity_id
882 AND assoc.entity_code = 'ZX_RATES'
883 AND NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
884 itf1.tax_invoice_date,
885 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
886 itf1.trx_date,
887 NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
888 itf1.gl_date,
889 sysdate
890 )
891 )
892 )
893 BETWEEN assoc.EFFECTIVE_FROM AND
894 NVL(assoc.EFFECTIVE_TO,
895 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
896 itf1.tax_invoice_date,
897 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
898 itf1.trx_date,
899 NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
900 itf1.gl_date,
901 sysdate
902 )
903 )
904 )
905 )
906 AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE'),
907 itf1.tax_line_id
908 FROM financials_system_params_all fsp,
909 hr_locations_all hr_loc,
910 zx_rep_trx_detail_t itf1
911 WHERE
912 itf1.application_id = 200
913 AND itf1.entity_code = 'AP_INVOICES'
914 AND itf1.ledger_id = fsp.set_of_books_id
915 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
916 AND itf1.ship_to_location_id = hr_loc.ship_to_location_id(+) ;
917
918 --Bug 5636632
919 CURSOR get_reporting_code_value(
920 p_entity_id zx_report_codes_assoc.entity_id%TYPE ,
921 p_reporting_type zx_reporting_types_b.reporting_type_code%TYPE ) IS
922 SELECT assoc.reporting_code_char_value
923 FROM zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
924 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
925 AND assoc.entity_id = p_entity_id
926 AND assoc.entity_code = 'ZX_RATES'
927 AND rep_type.reporting_type_code = p_reporting_type ;
928
929 BEGIN
930 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
931 IF (g_level_statement >= g_current_runtime_level ) THEN
932 FND_LOG.STRING(g_level_statement,
933 'ZX.TRL.je_tax_extract.populate_je_ap.BEGIN',
934 'je_tax_extract.populate_je_ap(+)');
935 END IF;
936
937
938 SELECT detail_tax_line_id
939 BULK COLLECT INTO l_tax_line_id_tbl
940 FROM zx_rep_trx_detail_t itf1
941 WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
942
943
944 IF l_tax_line_id_tbl.count <> 0 THEN
945 IF (g_level_statement >= g_current_runtime_level ) THEN
946 FND_LOG.STRING(g_level_statement,
947 'ZX.TRL.je_tax_extract.populate_je_ap',
948 'POPULATE_JE_AP - Rows extracted : '||to_char(l_tax_line_id_tbl.count));
949 END IF;
950 INSERT INTO ZX_REP_TRX_JX_EXT_T
951 (detail_tax_line_ext_id,
952 detail_tax_line_id,
953 tax_status_mng,
954 trx_business_category_mng,
955 document_sub_type_mng,
956 attribute8,
957 attribute11,
958 attribute12,
959 attribute20,
960 attribute21,
961 attribute22,
962 attribute23,
963 created_by,
964 creation_date,
965 last_updated_by,
966 last_update_date,
967 last_update_login,
968 request_id)
969 SELECT zx_rep_trx_jx_ext_t_s.nextval,
970 itf1.detail_tax_line_id,
971 itf1.tax_status_code,
972 itf1.trx_business_category,
973 itf1.document_sub_type,
974 decode ( ap_inv.global_attribute_category,
975 'JE.SK.APXINWKB.INVOICE_INFO', ap_inv.global_attribute2,
976 'JE.HU.APXINWKB.TAX_DATE', ap_inv.global_attribute2,
977 NULL ),
978 decode ( ap_inv.global_attribute_category,
979 'JE.ES.APXINWKB.INVOICE_INFO', ap_inv.global_attribute2,
980 'JE.ES.APXINWKB.MODELO349', ap_inv.global_attribute2,
981 NULL ),
982 decode ( ap_inv.global_attribute_category,
983 'JE.ES.APXINWKB.INVOICE_INFO', ap_inv.global_attribute3,
984 'JE.ES.APXINWKB.MODELO349', ap_inv.global_attribute3,
985 NULL ),
986 ap_inv.source,
987 decode ( ap_inv.global_attribute_category,
988 'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE3,
989 'JE.IL.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE3,
990 NULL ),
991 decode ( ap_inv.global_attribute_category,
992 'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE4,
993 'JE.IL.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE4,
994 NULL ),
995 substr(itf1.trx_business_category,(instr(itf1.trx_business_category,'MOD',1,1)+3),
996 length(itf1.trx_business_category)),
997 fnd_global.user_id,
998 sysdate,
999 fnd_global.user_id,
1000 sysdate,
1001 fnd_global.login_id,
1002 P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
1003 FROM ap_invoices_all ap_inv,
1004 zx_rep_trx_detail_t itf1
1005 WHERE itf1.trx_id = ap_inv.invoice_id
1006 and itf1.application_id = 200
1007 and itf1.entity_code = 'AP_INVOICES'
1008 and itf1.ledger_id = ap_inv.set_of_books_id
1009 and itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1010
1011 IF ( g_level_statement>= g_current_runtime_level ) THEN
1012 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JE_AP',
1013 'No. of rows inserted into ZX_REP_TRX_JX_EXT_T : '||to_char(SQL%ROWCOUNT) );
1014 END IF;
1015
1016 open get_rep_entity_info_cur ;
1017
1018 Loop
1019
1020 Fetch get_rep_entity_info_cur
1021 BULK COLLECT INTO
1022 L_DETAIL_TAX_LINE_ID_TBL,
1023 L_ATTRIBUTE10_TBL,
1024 L_TAX_RATE_ID_TBL ,
1025 L_ATTRIBUTE14_TBL,
1026 L_ATTRIBUTE15_TBL,
1027 L_ATTRIBUTE16_TBL,
1028 L_ATTRIBUTE17_TBL,
1029 L_ATTRIBUTE18_TBL,
1030 L_ATTRIBUTE19_TBL,
1031 l_attribute25_tbl, --Bug 5510822
1032 l_attribute26_tbl, --Emea changes
1033 l_taxline_id_tbl
1034 LIMIT C_LINES_PER_COMMIT;
1035
1036 l_count := nvl(L_DETAIL_TAX_LINE_ID_TBL.COUNT,0);
1037
1038 IF (g_level_statement >= g_current_runtime_level ) THEN
1039 FND_LOG.STRING(g_level_statement,
1040 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1041 'l_count : '||l_count);
1042 END IF;
1043
1044 FOR i IN 1..l_count
1045 LOOP
1046
1047 l_attribute28_tbl(i):=NULL;
1048 l_attribute27_tbl(i):=NULL;
1049 get_emea_rep_code_name(l_taxline_id_tbl(i),l_attribute28_tbl(i),l_attribute27_tbl(i));
1050
1051 IF L_ATTRIBUTE1_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1052 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1053 ELSE
1054 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_LOCATION');
1055 FETCH get_reporting_code_value INTO L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1056 IF ( get_reporting_code_value%NOTFOUND ) THEN
1057 L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1058 END IF ;
1059 CLOSE get_reporting_code_value;
1060 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1061 END IF ;
1062
1063 IF L_ATTRIBUTE2_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1064 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1065 ELSE
1066 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_TAXABLE_BOX');
1067 FETCH get_reporting_code_value INTO L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1068 IF ( get_reporting_code_value%NOTFOUND ) THEN
1069 L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1070 END IF ;
1071 CLOSE get_reporting_code_value;
1072 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1073 END IF ;
1074
1075 IF L_ATTRIBUTE3_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1076 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1077 ELSE
1078 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_REC_TAX_BOX');
1079 FETCH get_reporting_code_value INTO L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1080 IF ( get_reporting_code_value%NOTFOUND ) THEN
1081 L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1082 END IF ;
1083 CLOSE get_reporting_code_value;
1084 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1085 END IF ;
1086
1087 IF L_ATTRIBUTE4_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1088 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1089 ELSE
1090 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_TTL_TAXABLE_BOX');
1091 FETCH get_reporting_code_value INTO L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1092 IF ( get_reporting_code_value%NOTFOUND ) THEN
1093 L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1094 END IF ;
1095 CLOSE get_reporting_code_value;
1096 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1097 END IF ;
1098
1099 IF L_ATTRIBUTE5_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1100 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1101 ELSE
1102 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAXABLE');
1103 FETCH get_reporting_code_value INTO L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1104 IF ( get_reporting_code_value%NOTFOUND ) THEN
1105 L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1106 END IF ;
1107 CLOSE get_reporting_code_value;
1108 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1109 END IF ;
1110
1111 IF L_ATTRIBUTE6_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1112 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1113 ELSE
1114 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_NON_REC_TAXABLE');
1115 FETCH get_reporting_code_value INTO L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1116 IF ( get_reporting_code_value%NOTFOUND ) THEN
1117 L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1118 END IF ;
1119 CLOSE get_reporting_code_value;
1120 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1121 END IF ;
1122
1123 IF L_ATTRIBUTE7_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1124 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1125 ELSE
1126 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAX_BOX');
1127 FETCH get_reporting_code_value INTO L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1128 IF ( get_reporting_code_value%NOTFOUND ) THEN
1129 L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1130 END IF ;
1131 CLOSE get_reporting_code_value;
1132 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1133 END IF ;
1134
1135 IF (g_level_statement >= g_current_runtime_level ) THEN
1136 FND_LOG.STRING(g_level_statement,
1137 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1138 'Displaying the Vlaues for attributes : i :'||i);
1139 FND_LOG.STRING(g_level_statement,
1140 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1141 'L_DETAIL_TAX_LINE_ID_TBL(i)'||L_DETAIL_TAX_LINE_ID_TBL(i));
1142 FND_LOG.STRING(g_level_statement,
1143 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1144 'L_ATTRIBUTE1_TBL(i) :'||L_ATTRIBUTE1_TBL(i));
1145 FND_LOG.STRING(g_level_statement,
1146 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1147 'L_ATTRIBUTE2_TBL(i) :'||L_ATTRIBUTE2_TBL(i));
1148 FND_LOG.STRING(g_level_statement,
1149 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1150 'L_ATTRIBUTE3_TBL(i) :'||L_ATTRIBUTE3_TBL(i));
1151 FND_LOG.STRING(g_level_statement,
1152 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1153 'L_ATTRIBUTE4_TBL(i) :'||L_ATTRIBUTE4_TBL(i));
1154 FND_LOG.STRING(g_level_statement,
1155 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1156 'L_ATTRIBUTE5_TBL(i) :'||L_ATTRIBUTE5_TBL(i));
1157 FND_LOG.STRING(g_level_statement,
1158 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1159 'L_ATTRIBUTE6_TBL(i) :'||L_ATTRIBUTE6_TBL(i));
1160 FND_LOG.STRING(g_level_statement,
1161 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1162 'L_ATTRIBUTE7_TBL(i) :'||L_ATTRIBUTE7_TBL(i));
1163 FND_LOG.STRING(g_level_statement,
1164 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1165 'L_ATTRIBUTE27_TBL(i) :'||L_ATTRIBUTE27_TBL(i));
1166 FND_LOG.STRING(g_level_statement,
1167 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1168 'L_ATTRIBUTE28_TBL(i) :'||L_ATTRIBUTE28_TBL(i));
1169 END IF;
1170
1171 END LOOP ;
1172
1173 IF (g_level_statement >= g_current_runtime_level ) THEN
1174 FND_LOG.STRING(g_level_statement,
1175 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_ap.BEGIN',
1176 'Before Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained ' );
1177 END IF;
1178
1179 IF ( l_count > 0 ) THEN
1180
1181 IF ( g_level_statement>= g_current_runtime_level ) THEN
1182 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JE_AP',
1183 'Before Updating ZX_REP_TRX_JX_EXT_T' );
1184 END IF;
1185 FORALL i IN 1 .. L_DETAIL_TAX_LINE_ID_TBL.count
1186 UPDATE ZX_REP_TRX_JX_EXT_T
1187 SET ATTRIBUTE10 = L_ATTRIBUTE10_TBL(i),
1188 ATTRIBUTE1 = L_ATTRIBUTE1_TBL(i),
1189 ATTRIBUTE2 = L_ATTRIBUTE2_TBL(i),
1190 ATTRIBUTE3 = L_ATTRIBUTE3_TBL(i),
1191 ATTRIBUTE4 = L_ATTRIBUTE4_TBL(i),
1192 ATTRIBUTE5 = L_ATTRIBUTE5_TBL(i),
1193 ATTRIBUTE6 = L_ATTRIBUTE6_TBL(i),
1194 ATTRIBUTE7 = L_ATTRIBUTE7_TBL(i),
1195 ATTRIBUTE14 = L_ATTRIBUTE14_TBL(i),
1196 ATTRIBUTE15 = L_ATTRIBUTE15_TBL(i),
1197 ATTRIBUTE16 = L_ATTRIBUTE16_TBL(i),
1198 ATTRIBUTE17 = L_ATTRIBUTE17_TBL(i),
1199 ATTRIBUTE18 = L_ATTRIBUTE18_TBL(i),
1200 ATTRIBUTE19 = L_ATTRIBUTE19_TBL(i),
1201 attribute25 = l_attribute25_tbl(i), --Bug 5510822
1202 attribute26 = l_attribute26_tbl(i),--Emea Changes
1203 attribute27 = l_attribute27_tbl(i),
1204 attribute28 = l_attribute28_tbl(i)
1205 WHERE detail_tax_line_id = L_DETAIL_TAX_LINE_ID_TBL(i)
1206 and request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1207
1208
1209 IF ( g_level_statement>= g_current_runtime_level ) THEN
1210 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JE_AP',
1211 'After Updating ZX_REP_TRX_JX_EXT_T' );
1212 END IF;
1213 ELSE
1214 EXIT ;
1215 END IF ;
1216
1217 end loop;
1218
1219 close get_rep_entity_info_cur ;
1220
1221
1222 END IF;
1223 --commit; Bug 8262631
1224 IF (g_level_statement >= g_current_runtime_level ) THEN
1225 FND_LOG.STRING(g_level_statement,
1226 'ZX.TRL.je_tax_extract.populate_je_ap.END',
1227 'je_tax_extract.populate_je_ap(-)');
1228 END IF;
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232
1233 l_err_msg := substrb(SQLERRM,1,120);
1234 arp_standard.debug('EXCEPTION raised in ' ||'POPULATE_JE_AP: ' ||SQLCODE ||':'||l_err_msg);
1235
1236 END populate_je_ap;
1237
1238 /*===========================================================================+
1239 | PUBLIC PROCEDURE |
1240 | POPULATE_JE_GL |
1241 | Type : Public |
1242 | Pre-req : None |
1243 | Function : |
1244 | This procedure calls the API to select the JE specific data from |
1245 | JE GL tables. |
1246 | |
1247 | Called from |
1248 | |
1249 | Parameters : |
1250 | IN: P_TRL_GLOBAL_VARIABLES_REC ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE|
1251 | |
1252 | |
1253 | MODIFICATION HISTORY |
1254 | 22-MAY-2006 VSDOSHI Created |
1255 | |
1256 | |
1257 +===========================================================================*/
1258
1259 PROCEDURE POPULATE_JE_GL
1260 (
1261 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
1262 )
1263
1264 IS
1265
1266 --Bug 5636632
1267 TYPE ATTRIBUTE1_TBL is TABLE OF
1268 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
1269
1270 TYPE ATTRIBUTE2_TBL is TABLE OF
1271 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE2%TYPE INDEX BY BINARY_INTEGER;
1272
1273 TYPE ATTRIBUTE3_TBL is TABLE OF
1274 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE3%TYPE INDEX BY BINARY_INTEGER;
1275
1276 TYPE ATTRIBUTE4_TBL is TABLE OF
1277 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE4%TYPE INDEX BY BINARY_INTEGER;
1278
1279 TYPE ATTRIBUTE5_TBL is TABLE OF
1280 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE5%TYPE INDEX BY BINARY_INTEGER;
1281
1282 TYPE ATTRIBUTE6_TBL is TABLE OF
1283 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE6%TYPE INDEX BY BINARY_INTEGER;
1284
1285 TYPE ATTRIBUTE7_TBL is TABLE OF
1286 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE7%TYPE INDEX BY BINARY_INTEGER;
1287
1288 TYPE ATTRIBUTE25_TBL is TABLE OF
1289 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE25%TYPE INDEX BY BINARY_INTEGER;
1290
1291 TYPE ATTRIBUTE26_TBL is TABLE OF
1292 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE26%TYPE INDEX BY BINARY_INTEGER;
1293
1294 l_attribute1_tbl ATTRIBUTE1_TBL;
1295 l_attribute2_tbl ATTRIBUTE2_TBL;
1296 l_attribute3_tbl ATTRIBUTE3_TBL;
1297 l_attribute4_tbl ATTRIBUTE4_TBL;
1298 l_attribute5_tbl ATTRIBUTE5_TBL;
1299 l_attribute6_tbl ATTRIBUTE6_TBL;
1300 l_attribute7_tbl ATTRIBUTE7_TBL;
1301 l_attribute25_tbl ATTRIBUTE25_TBL;
1302 l_attribute26_tbl ATTRIBUTE26_TBL;
1303
1304 l_attribute1_tmp_tbl ATTRIBUTE1_TBL;
1305 l_attribute2_tmp_tbl ATTRIBUTE2_TBL;
1306 l_attribute3_tmp_tbl ATTRIBUTE3_TBL;
1307 l_attribute4_tmp_tbl ATTRIBUTE4_TBL;
1308 l_attribute5_tmp_tbl ATTRIBUTE5_TBL;
1309 l_attribute6_tmp_tbl ATTRIBUTE6_TBL;
1310 l_attribute7_tmp_tbl ATTRIBUTE7_TBL;
1311
1312 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
1313 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
1314 l_tax_status_code_tbl ZX_EXTRACT_PKG.TAX_STATUS_CODE_TBL;
1315 l_trx_business_category_tbl ZX_EXTRACT_PKG.TRX_BUSINESS_CATEGORY_TBL;
1316 l_document_sub_type_tbl ZX_EXTRACT_PKG.DOCUMENT_SUB_TYPE_TBL;
1317 l_tax_rate_id_tbl ZX_EXTRACT_PKG.tax_rate_id_tbl ;
1318 L_COUNT NUMBER ;
1319 C_LINES_PER_COMMIT Number:=1000;
1320
1321 CURSOR get_reporting_code_value(
1322 p_entity_id zx_report_codes_assoc.entity_id%TYPE ,
1323 p_reporting_type zx_reporting_types_b.reporting_type_code%TYPE ) IS
1324 SELECT assoc.reporting_code_char_value
1325 FROM zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
1326 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
1327 AND assoc.entity_id = p_entity_id
1328 AND assoc.entity_code = 'ZX_RATES'
1329 AND rep_type.reporting_type_code = p_reporting_type ;
1330
1331 cursor get_rep_entity_info_cur is
1332 SELECT detail_tax_line_id,
1333 itf1.tax_status_code,
1334 itf1.trx_business_category,
1335 itf1.document_sub_type,
1336 itf1.TAX_RATE_ID,
1337 (SELECT assoc.reporting_code_char_value FROM
1338 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
1339 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
1340 AND itf1.TAX_RATE_ID = assoc.entity_id
1341 AND assoc.entity_code = 'ZX_RATES'
1342 AND (assoc.EFFECTIVE_TO is null
1343 or assoc.EFFECTIVE_TO >= NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
1344 itf1.tax_invoice_date,
1345 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
1346 itf1.trx_date,
1347 NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
1348 itf1.gl_date,
1349 sysdate
1350 )
1351 )
1352 )
1353 )
1354 AND rep_type.reporting_type_code IN ('CZ_TAX_ORIGIN','HU_TAX_ORIGIN','PL_TAX_ORIGIN','CH_VAT_REGIME')
1355 ) ,
1356 (SELECT assoc.reporting_code_char_value FROM
1357 zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
1358 WHERE rep_type.reporting_type_id = assoc.reporting_type_id
1359 AND itf1.TAX_RATE_ID = assoc.entity_id
1360 AND assoc.entity_code = 'ZX_RATES'
1361 AND NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
1362 itf1.tax_invoice_date,
1363 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
1364 itf1.trx_date,
1365 NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
1366 itf1.gl_date,
1367 sysdate
1368 )
1369 )
1370 )
1371 BETWEEN assoc.EFFECTIVE_FROM AND
1372 NVL(assoc.EFFECTIVE_TO,
1373 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
1374 itf1.tax_invoice_date,
1375 NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
1376 itf1.trx_date,
1377 NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
1378 itf1.gl_date,
1379 sysdate
1380 )
1381 )
1382 )
1383 )
1384 AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE')
1385 FROM zx_rep_trx_detail_t itf1
1386 WHERE itf1.application_id = 101
1387 AND itf1.entity_code = 'GL_JE_LINES'
1388 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1389
1390 BEGIN
1391
1392 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1393 IF (g_level_statement >= g_current_runtime_level ) THEN
1394 FND_LOG.STRING(g_level_statement,
1395 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1396 'je_tax_extract.populate_je_gl(+)');
1397
1398 FND_LOG.STRING(g_level_statement,
1399 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1400 'Before insertion into ZX_REP_TRX_JX_EXT_T - 1');
1401 END IF;
1402
1403 SELECT detail_tax_line_id
1404 BULK COLLECT INTO l_detail_tax_line_id_tbl
1405 FROM zx_rep_trx_detail_t itf1
1406 WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1407
1408 IF (g_level_statement >= g_current_runtime_level ) THEN
1409 FND_LOG.STRING(g_level_statement,
1410 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1411 'Count Before insertion - 1'||to_char(l_detail_tax_line_id_tbl.count));
1412 END IF;
1413
1414 IF l_detail_tax_line_id_tbl.count <> 0 THEN
1415
1416 INSERT INTO ZX_REP_TRX_JX_EXT_T
1417 (detail_tax_line_ext_id,
1418 detail_tax_line_id,
1419 created_by,
1420 creation_date,
1421 last_updated_by,
1422 last_update_date,
1423 last_update_login,
1424 attribute24,
1425 request_id)
1426 SELECT zx_rep_trx_jx_ext_t_s.nextval,
1427 itf1.detail_tax_line_id,
1428 fnd_global.user_id,
1429 sysdate,
1430 fnd_global.user_id,
1431 sysdate,
1432 fnd_global.login_id,
1433 itf1.tax_type_code, --gjl.tax_type_code,
1434 P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
1435 FROM zx_rep_trx_detail_t itf1
1436 WHERE itf1.application_id = 101
1437 AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
1438 AND EXISTS (SELECT 1
1439 FROM gl_je_lines gjl
1440 WHERE gjl.je_header_id = itf1.trx_id
1441 AND gjl.je_line_num = itf1.trx_line_id
1442 );
1443
1444 IF (g_level_statement >= g_current_runtime_level ) THEN
1445 FND_LOG.STRING(g_level_statement,
1446 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1447 'No. of rows inserted into ZX_REP_TRX_JX_EXT_T - 1'||to_char(sql%ROWCOUNT));
1448 END IF;
1449
1450 open get_rep_entity_info_cur ;
1451
1452 Loop
1453
1454 Fetch get_rep_entity_info_cur
1455 bulk collect into
1456 L_DETAIL_TAX_LINE_ID_TBL,
1457 L_TAX_STATUS_CODE_TBL,
1458 L_TRX_BUSINESS_CATEGORY_TBL,
1459 L_DOCUMENT_SUB_TYPE_TBL,
1460 L_TAX_RATE_ID_TBL ,
1461 L_ATTRIBUTE25_TBL,
1462 L_ATTRIBUTE26_TBL
1463 LIMIT C_LINES_PER_COMMIT;
1464
1465 l_count := nvl(L_DETAIL_TAX_LINE_ID_TBL.COUNT,0);
1466
1467 IF (g_level_statement >= g_current_runtime_level ) THEN
1468 FND_LOG.STRING(g_level_statement,
1469 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1470 'l_count : '||l_count);
1471 END IF;
1472
1473 FOR i IN 1..l_count
1474 LOOP
1475 IF L_ATTRIBUTE1_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1476 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1477 ELSE
1478 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_LOCATION');
1479 FETCH get_reporting_code_value INTO L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1480 IF ( get_reporting_code_value%NOTFOUND ) THEN
1481 L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1482 END IF ;
1483 CLOSE get_reporting_code_value;
1484 L_ATTRIBUTE1_TBL(i) := L_ATTRIBUTE1_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1485 END IF ;
1486
1487 IF L_ATTRIBUTE2_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1488 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1489 ELSE
1490 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_TAXABLE_BOX');
1491 FETCH get_reporting_code_value INTO L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1492 IF ( get_reporting_code_value%NOTFOUND ) THEN
1493 L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1494 END IF ;
1495 CLOSE get_reporting_code_value;
1496 L_ATTRIBUTE2_TBL(i) := L_ATTRIBUTE2_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1497 END IF ;
1498
1499 IF L_ATTRIBUTE3_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1500 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1501 ELSE
1502 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_PRD_REC_TAX_BOX');
1503 FETCH get_reporting_code_value INTO L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1504 IF ( get_reporting_code_value%NOTFOUND ) THEN
1505 L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1506 END IF ;
1507 CLOSE get_reporting_code_value;
1508 L_ATTRIBUTE3_TBL(i) := L_ATTRIBUTE3_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1509 END IF ;
1510
1511 IF L_ATTRIBUTE4_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1512 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1513 ELSE
1514 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_TTL_TAXABLE_BOX');
1515 FETCH get_reporting_code_value INTO L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1516 IF ( get_reporting_code_value%NOTFOUND ) THEN
1517 L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1518 END IF ;
1519 CLOSE get_reporting_code_value;
1520 L_ATTRIBUTE4_TBL(i) := L_ATTRIBUTE4_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1521 END IF ;
1522
1523 IF L_ATTRIBUTE5_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1524 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1525 ELSE
1526 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAXABLE');
1527 FETCH get_reporting_code_value INTO L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1528 IF ( get_reporting_code_value%NOTFOUND ) THEN
1529 L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1530 END IF ;
1531 CLOSE get_reporting_code_value;
1532 L_ATTRIBUTE5_TBL(i) := L_ATTRIBUTE5_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1533 END IF ;
1534
1535 IF L_ATTRIBUTE6_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1536 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1537 ELSE
1538 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_NON_REC_TAXABLE');
1539 FETCH get_reporting_code_value INTO L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1540 IF ( get_reporting_code_value%NOTFOUND ) THEN
1541 L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1542 END IF ;
1543 CLOSE get_reporting_code_value;
1544 L_ATTRIBUTE6_TBL(i) := L_ATTRIBUTE6_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1545 END IF ;
1546
1547 IF L_ATTRIBUTE7_TMP_TBL.EXISTS(L_TAX_RATE_ID_TBL(i)) THEN
1548 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1549 ELSE
1550 OPEN get_reporting_code_value(L_TAX_RATE_ID_TBL(i),'PT_ANL_REC_TAX_BOX');
1551 FETCH get_reporting_code_value INTO L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1552 IF ( get_reporting_code_value%NOTFOUND ) THEN
1553 L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i)) := NULL ;
1554 END IF ;
1555 CLOSE get_reporting_code_value;
1556 L_ATTRIBUTE7_TBL(i) := L_ATTRIBUTE7_TMP_TBL(L_TAX_RATE_ID_TBL(i));
1557 END IF ;
1558
1559
1560 IF (g_level_statement >= g_current_runtime_level ) THEN
1561 FND_LOG.STRING(g_level_statement,
1562 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1563 'Displaying the Vlaues for attributes : i :'||i);
1564 FND_LOG.STRING(g_level_statement,
1565 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1566 'L_DETAIL_TAX_LINE_ID_TBL(i)'||L_DETAIL_TAX_LINE_ID_TBL(i));
1567 FND_LOG.STRING(g_level_statement,
1568 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1569 'L_ATTRIBUTE1_TBL(i) :'||L_ATTRIBUTE1_TBL(i));
1570 FND_LOG.STRING(g_level_statement,
1571 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1572 'L_ATTRIBUTE2_TBL(i) :'||L_ATTRIBUTE2_TBL(i));
1573 FND_LOG.STRING(g_level_statement,
1574 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1575 'L_ATTRIBUTE3_TBL(i) :'||L_ATTRIBUTE3_TBL(i));
1576 FND_LOG.STRING(g_level_statement,
1577 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1578 'L_ATTRIBUTE4_TBL(i) :'||L_ATTRIBUTE4_TBL(i));
1579 FND_LOG.STRING(g_level_statement,
1580 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1581 'L_ATTRIBUTE5_TBL(i) :'||L_ATTRIBUTE5_TBL(i));
1582 FND_LOG.STRING(g_level_statement,
1583 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1584 'L_ATTRIBUTE6_TBL(i) :'||L_ATTRIBUTE6_TBL(i));
1585 FND_LOG.STRING(g_level_statement,
1586 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1587 'L_ATTRIBUTE7_TBL(i) :'||L_ATTRIBUTE7_TBL(i));
1588 FND_LOG.STRING(g_level_statement,
1589 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1590 'L_TAX_STATUS_CODE_TBL(i) :'||L_TAX_STATUS_CODE_TBL(i));
1591 FND_LOG.STRING(g_level_statement,
1592 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1593 'L_TRX_BUSINESS_CATEGORY_TBL(i) :'||L_TRX_BUSINESS_CATEGORY_TBL(i));
1594 FND_LOG.STRING(g_level_statement,
1595 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1596 'L_DOCUMENT_SUB_TYPE_TBL(i) :'||L_DOCUMENT_SUB_TYPE_TBL(i));
1597 END IF;
1598
1599 END LOOP ;
1600
1601 IF ( l_count > 0 ) THEN
1602
1603 IF (g_level_statement >= g_current_runtime_level ) THEN
1604 FND_LOG.STRING(g_level_statement,
1605 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1606 'Before Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained -GL ' );
1607 END IF;
1608
1609 FORALL i IN 1 .. l_count
1610 UPDATE ZX_REP_TRX_JX_EXT_T
1611 SET
1612 TAX_STATUS_MNG = L_TAX_STATUS_CODE_TBL(i),
1613 TRX_BUSINESS_CATEGORY_MNG = L_TRX_BUSINESS_CATEGORY_TBL(i),
1614 DOCUMENT_SUB_TYPE_MNG = L_DOCUMENT_SUB_TYPE_TBL(i),
1615 ATTRIBUTE1 = L_ATTRIBUTE1_TBL(i),
1616 ATTRIBUTE2 = L_ATTRIBUTE2_TBL(i),
1617 ATTRIBUTE3 = L_ATTRIBUTE3_TBL(i),
1618 ATTRIBUTE4 = L_ATTRIBUTE4_TBL(i),
1619 ATTRIBUTE5 = L_ATTRIBUTE5_TBL(i),
1620 ATTRIBUTE6 = L_ATTRIBUTE6_TBL(i),
1621 ATTRIBUTE7 = L_ATTRIBUTE7_TBL(i),
1622 attribute25 = L_ATTRIBUTE25_TBL(i),
1623 attribute26 = L_ATTRIBUTE26_TBL(i)
1624 WHERE detail_tax_line_id = L_DETAIL_TAX_LINE_ID_TBL(i) and
1625 request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1626
1627 IF (g_level_statement >= g_current_runtime_level ) THEN
1628 FND_LOG.STRING(g_level_statement,
1629 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.BEGIN',
1630 'After Updating ZX_REP_TRX_JX_EXT_T with attribute columns obtained ' );
1631 END IF;
1632 -- exit when get_rep_entity_info_cur%NOTFOUND;
1633 ELSE
1634 EXIT ;
1635 END IF ;
1636
1637 end loop;
1638
1639 close get_rep_entity_info_cur ;
1640
1641 END IF ;
1642
1643 --commit; Bug 8262631
1644 IF (g_level_statement >= g_current_runtime_level ) THEN
1645 FND_LOG.STRING(g_level_statement,
1646 'ZX.TRL.je_tax_extract.je_tax_extract.populate_je_gl.END',
1647 'je_tax_extract.populate_je_gl(-)');
1648 END IF;
1649
1650
1651 EXCEPTION
1652 WHEN OTHERS THEN
1653
1654 l_err_msg := substrb(SQLERRM,1,120);
1655 arp_standard.debug('EXCEPTION raised in ' ||'POPULATE_JE_GL: ' ||SQLCODE ||':'||l_err_msg);
1656
1657 END populate_je_gl;
1658
1659
1660
1661
1662 END ZX_JE_EXTRACT_PKG;