[Home] [Help]
Skip to content
PACKAGE BODY: APPS.ZX_JA_EXTRACT_PKG
Source
1 PACKAGE BODY ZX_JA_EXTRACT_PKG AS
2 /* $Header: zxriextrajappvtb.pls 120.41.12020000.2 2012/07/23 13:02:53 srajapar noship $ */
3
4 -----------------------------------------
5 --Private Variable Declarations
6
7 -----------------------------------------
8 --
9 -----------------------------------------
10
11 --Private Methods Declarations
12 -----------------------------------------
13 l_err_msg varchar2(120);
14
15 FUNCTION GET_LOOKUP_INFO
16 (
17 P_LOOKUP_TYPE IN VARCHAR2,
18 P_LOOKUP_CODE IN VARCHAR2,
19 P_TRX_DATE IN DATE
20 )
21 return varchar2;
22
23
24 PROCEDURE bank_info
25 (
26 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
27 );
28
29 PROCEDURE GET_INVOICE_AMT
30 (
31 P_VENDOR_ID IN NUMBER,
32 P_INVOICE_NUM IN VARCHAR2,
33 X_INVOICE_AMT OUT NOCOPY ZX_REP_TRX_JX_EXT_T.NUMERIC12%TYPE,
34 X_BASE_AMT OUT NOCOPY ZX_REP_TRX_JX_EXT_T.NUMERIC13%TYPE,
35 X_PRINT_DATE OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_AP_INVOICES_ATT4%TYPE
36 );
37
38 PROCEDURE UPDATE_PRINT_DATE
39 (
40 P_INVOICE_ID IN NUMBER
41 );
42
43
44 PROCEDURE GET_GUI_SOURCE
45 (
46 P_TRX_SOURCE_NAME IN VARCHAR2,
47 X_GDF_RA_BATCH_SOURCES_ATT1 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT1%TYPE, -- reference transaction source
48 X_GDF_RA_BATCH_SOURCES_ATT2 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT2%TYPE, -- initial trx num
49 X_GDF_RA_BATCH_SOURCES_ATT3 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT3%TYPE, -- invoice word
50 X_GDF_RA_BATCH_SOURCES_ATT4 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT4%TYPE -- final trx num
51 );
52
53
54 PROCEDURE GET_ORG_TRX_NUMBER
55 (
56 P_TRX_SOURCE_ID IN NUMBER,
57 P_TRX_ID IN NUMBER,
58 X_ORG_TRX_NUMBER OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT9%TYPE -- org trx num
59 );
60
61
62 PROCEDURE GET_EXPORT_INFO
63 (
64 P_TRX_ID IN NUMBER,
65 X_GDF_RA_CUST_TRX_ATT4 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT4%TYPE, -- export certificate number
66 X_GDF_RA_CUST_TRX_ATT5 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT5%TYPE, -- export name
67 X_GDF_RA_CUST_TRX_ATT6 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT6%TYPE, -- export method
68 X_GDF_RA_CUST_TRX_ATT7 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT7%TYPE, -- export type
69 X_GDF_RA_CUST_TRX_ATT8 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT8%TYPE -- export date
70 );
71
72 --
73
74 -----------------------------------------
75 --Public Variable Declarations
76 -----------------------------------------
77 --
78 g_current_runtime_level NUMBER ;
79 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
80 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
81 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
82 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
83 g_error_buffer VARCHAR2(100);
84 -----------------------------------------
85 --Public Methods Declarations
86 -----------------------------------------
87
88 /*===========================================================================+
89 | PROCEDURE |
90 | FILTER_JA_AR_TAX_LINES |
91 | Type : Public |
92 | Pre-req : None |
93 | Function : |
94 | This procedure is called to filter the records of transaction tables |
95 | by selecting only the records associated with JA specific lookup type |
96 | all unnecessary rows in ZX_REP_TRX_DETAIL_T table are deleted |
97 | |
98 | Called from AR_TAX_EXTRACT.EXECUTE_SQL. |
99 | |
100 | Parameters : |
101 | IN : p_report_name varchar2 Required |
102 | p_request_id number Required |
103 | |
104 | MODIFICATION HISTORY |
105 | 27-Oct-03 Asako Takahashi created |
106 | |
107 | |
108 +===========================================================================*/
109
110
111 PROCEDURE FILTER_JA_AR_TAX_LINES
112 (
113 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
114 )
115
116 /* Following parameters are removed since we now access global variables directly
117 (
118 P_REPORT_NAME IN varchar2,
119 P_REQUEST_ID IN number,
120 P_EXP_CERT_DATE_FROM IN date,
121 P_EXP_CERT_DATE_TO IN date,
122 P_EXP_METHOD IN varchar2,
123 P_TRX_SOURCE_ID IN number,
124 P_INCLUDE_REFERENCED_SOURCE IN varchar2
125 )
126 */
127
128 IS
129 l_delete_sql_string varchar2(3000);
130 l_loc_trx_src_type varchar2(30);
131 l_referenced_trx_src_id varchar2(150);
132 lp_where_export_date_from varchar2(100);
133 lp_where_export_date_to varchar2(100);
134 lp_where_export_method varchar2(100);
135 lp_where_trx_source_id varchar2(100);
136 lp_where_inc_reference_source varchar2(100);
137 lp_count_taxable number(15);
138 l_insert_sql_string varchar2(1000);
139
140 BEGIN
141 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
142 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
143 IF (g_level_statement >= g_current_runtime_level ) THEN
144 FND_LOG.STRING(g_level_statement,
145 'ZX.TRL.ja_tax_extract.filter_ja_tax_lines.BEGIN',
146 'ja_tax_extract.filter_ja_tax_lines(+)');
147 END IF;
148
149 -- ------------------------ --
150 -- Filter the SUB ITF table --
151 -- ------------------------ --
152
153 if P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWRVAT' then
154 IF (g_level_statement >= g_current_runtime_level ) THEN
155 FND_LOG.STRING(g_level_statement,
156 'ZX.TRL.ja_tax_extract.filter_ja_tax_lines',
157 'P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID'||to_char(P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID));
158 END IF;
159 /* Taiwanese Output VAT Report */
160 if P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID is NOT NULL then
161 SELECT batch_source_type, TO_NUMBER(global_attribute1)
162 INTO l_loc_trx_src_type, l_referenced_trx_src_id
163 FROM ra_batch_sources_all
164 WHERE batch_source_Id = P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID;
165
166
167 IF (g_level_statement >= g_current_runtime_level ) THEN
168 FND_LOG.STRING(g_level_statement,
169 'ZX.TRL.ja_tax_extract.filter_ja_tax_lines',
170 'batch_source_type'||l_loc_trx_src_type);
171 END IF;
172
173 if l_loc_trx_src_type = 'INV' and
174 l_referenced_trx_src_id IS NOT NULL and
175 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_REFERENCED_SOURCE = 'Y' then
176 /* l_referenced_trx_src_id :GDF_RA_BATCH_SOURCES_ATT1 is used for Reference
177 Transaction Source */
178 lp_where_trx_source_id:= ' DET.TRX_BATCH_SOURCE_ID = ' || P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID;
179 lp_where_inc_reference_source:= ' or DET.TRX_BATCH_SOURCE_ID = '|| l_referenced_trx_src_id;
180 else
181 lp_where_trx_source_id:= ' DET.TRX_BATCH_SOURCE_ID = ' || P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID;
182 lp_where_inc_reference_source:= ' and 1 = 1';
183 end if;
184 else
185 lp_where_trx_source_id:= ' 1 = 1';
186 lp_where_inc_reference_source:= ' and 1 = 1';
187 end if;
188
189 IF (g_level_statement >= g_current_runtime_level ) THEN
190 FND_LOG.STRING(g_level_statement,
191 'ZX.TRL.ja_tax_extract.filter_ja_tax_lines',
192 'lp_where_trx_source_id:'||lp_where_trx_source_id);
193 FND_LOG.STRING(g_level_statement,
194 'ZX.TRL.ja_tax_extract.filter_ja_tax_lines',
195 'lp_where_inc_reference_source::'||lp_where_inc_reference_source);
196 END IF;
197
198 -- AMIt changed = to <> because there is NOT
199 l_delete_sql_string:=
200 'DELETE from ZX_REP_TRX_DETAIL_T DET
201 WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and NOT(
202 substrb(DET.DOCUMENT_SUB_TYPE,10,2) in (''31'',''32'',''33'',''34'',''35'',''36'',''37'') and
203 DET.TRX_LINE_CLASS IN (''INVOICE'', ''CREDIT_MEMO'') and
204 DET.DOC_EVENT_STATUS <> ''CANCELLED'' and ( ' ||
205 lp_where_trx_source_id || '' || lp_where_inc_reference_source || '))';
206
207
208 IF (g_level_statement >= g_current_runtime_level ) THEN
209 FND_LOG.STRING(g_level_statement,
210 'ZX.TRL.ja_tax_extract.filter_ja_tax_lines',
211 'l_delete_sql_string::'||l_delete_sql_string);
212 END IF;
213
214 -- AMIt changed = to <> because there is NOT
215 EXECUTE IMMEDIATE l_delete_sql_string;
216 DELETE from ZX_REP_TRX_DETAIL_T DET
217 WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and NOT(
218 substrb(DET.DOCUMENT_SUB_TYPE,10,2) in ('31','32','33','34','35','36','37') and
219 DET.TRX_LINE_CLASS IN ('INVOICE', 'CREDIT_MEMO') and
220 DET.DOC_EVENT_STATUS <> 'CANCELLED' and ( DET.TRX_BATCH_SOURCE_ID = P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID and 1 = 1));
221
222 IF (g_level_statement >= g_current_runtime_level ) THEN
223 FND_LOG.STRING(g_level_statement,
224 'ZX.TRL.ja_tax_extract.filter_ja_tax_lines',
225 'l_delete_sql_string::'||l_delete_sql_string);
226 END IF;
227
228 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWRUIL' then
229 /* Taiwanese Receivables Government Uniform Invoice Report */
230
231 l_referenced_trx_src_id := null;
232
233 if P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID is NOT NULL then
234 SELECT batch_source_type, TO_NUMBER(global_attribute1)
235 INTO l_loc_trx_src_type, l_referenced_trx_src_id
236 FROM ra_batch_sources
237 WHERE batch_source_Id = P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID;
238
239 if l_loc_trx_src_type = 'INV' and
240 l_referenced_trx_src_id IS NOT NULL and
241 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_REFERENCED_SOURCE = 'Y' then
242 /* l_referenced_trx_src_id is used for GDF_RA_BATCH_SOURCES_ATT1 */
243 lp_where_trx_source_id:= ' and DET.TRX_BATCH_SOURCE_ID = ' || P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID;
244 lp_where_inc_reference_source:= ' DET.TRX_BATCH_SOURCE_ID = '|| l_referenced_trx_src_id;
245 else
246 lp_where_trx_source_id:= ' DET.TRX_BATCH_SOURCE_ID = ' || P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID;
247 lp_where_inc_reference_source:= ' 1 = 1';
248 end if;
249 else
250 lp_where_trx_source_id:= ' 1 = 1';
251 lp_where_inc_reference_source:= ' 1 = 1';
252 end if;
253
254 l_delete_sql_string:=
255 'DELETE from ZX_REP_TRX_DETAIL_T DET
256 WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
257 NOT EXISTS
258 (
259 SELECT 1 FROM ra_batch_sources_all rbs,
260 ra_customer_trx_all rct
261 WHERE
262 rbs.batch_source_id = DET.TRX_BATCH_SOURCE_ID and
263 rct.batch_source_id = rbs.batch_source_id and
264 /* selecting trx with GUI TYPE 31,32,35,36,37 */
265 (
266 substrb(DET.DOCUMENT_SUB_TYPE,10,2) in (''31'',''32'',''35'',''36'',''37'') and
267 (';
268 IF l_referenced_trx_src_id is NULL THEN
269 l_delete_sql_string := l_delete_sql_string||' rbs.global_attribute_category = ''JA.TW.RAXSUMSC.BATCH_SOURCES'' and
270 rbs.global_attribute3 IS NOT NULL and (' ||
271 lp_where_trx_source_id || ' or ' ||
272 lp_where_inc_reference_source || ')
273 '; -- amit removed one bracket
274 ELSE
275 l_delete_sql_string := l_delete_sql_string||'rbs.global_attribute3 IS NULL and '||
276 lp_where_trx_source_id || '
277 )';
278 END IF;
279 l_delete_sql_string := l_delete_sql_string||' )
280 )
281 or
282 /* selecting trx with GUI TYPE 33,34 */
283 (substrb(DET.DOCUMENT_SUB_TYPE,10,2) in (''33'',''34'') and (' ||
284 lp_where_trx_source_id || ' or ' ||
285 lp_where_inc_reference_source || ')
286 )
287 or
288 /* selecting trx with GUI TYPE NULL */
289 (substrb(DET.DOCUMENT_SUB_TYPE,10,2) = '' '' and
290 rct.global_attribute_category = ''JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'' and
291 rct.global_attribute9 IS NOT NULL and
292 DET.DOC_EVENT_STATUS <> ''CANCELLED'' and '|| -- Amit changed to <>
293 lp_where_trx_source_id || '
294 )
295 -- )
296 )';
297
298 EXECUTE IMMEDIATE l_delete_sql_string;
299
300 IF ( g_level_statement>= g_current_runtime_level ) THEN
301 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
302 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
303 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
304 ' l_delete_sql_string : '||l_delete_sql_string );
305
306 END IF;
307
308 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWRZTR' then
309 /* Taiwanese Receivables Zero-Rate Tax Report */
310
311 if P_TRL_GLOBAL_VARIABLES_REC.EXP_CERT_DATE_FROM is NOT NULL then
312 lp_where_export_date_from:= ' and fnd_date.canonical_to_date(rct.global_attribute8) >= ' ||
313 P_TRL_GLOBAL_VARIABLES_REC.exp_cert_date_from ;
314 end if;
315
316 if P_TRL_GLOBAL_VARIABLES_REC.EXP_CERT_DATE_TO is NOT NULL then
317 lp_where_export_date_to:= ' and fnd_date.canonical_to_date(rct.global_attribute8) <= ' ||
318 P_TRL_GLOBAL_VARIABLES_REC.exp_cert_date_to ;
319 end if;
320
321 if P_TRL_GLOBAL_VARIABLES_REC.EXP_METHOD is NOT NULL then
322 lp_where_export_method:= ' and rct.global_attribute6 = ''' ||
323 P_TRL_GLOBAL_VARIABLES_REC.exp_method || '''';
324 end if;
325
326 l_delete_sql_string:=
327 'DELETE from ZX_REP_TRX_DETAIL_T DET
328 WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
329 NOT EXISTS
330 (SELECT 1
331 FROM
332 RA_CUSTOMER_TRX_ALL rct,
333 JA_LOOKUPS ja1,
334 JA_LOOKUPS ja2,
335 JA_LOOKUPS ja3
336 WHERE
337 DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
338 DET.TRX_ID = rct.customer_trx_id and
339 substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN (''31'',''32'',''33'',''34'',''35'',''36'',''37'') and
340 DET.TRX_LINE_CLASS IN (''INVOICE'',''CREDIT_MEMO'') ' ||
341 lp_where_export_date_from ||
342 lp_where_export_date_to ||
343 lp_where_export_method|| ')';
344
345 IF ( g_level_statement>= g_current_runtime_level ) THEN
346 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
347 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
348 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
349 ' l_delete_sql_string : '||l_delete_sql_string );
350
351 END IF;
352
353 EXECUTE IMMEDIATE l_delete_sql_string;
354
355 IF ( g_level_statement>= g_current_runtime_level ) THEN
356 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
357 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
358 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
359 ' l_delete_sql_string : '||l_delete_sql_string );
360
361 END IF;
362
363 --COMMIT; Bug 8262631
364
365
366
367 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWRSRD' then
368 /* Taiwanese Sales Return and Discount Report */
369
370 DELETE from ZX_REP_TRX_DETAIL_T DET
371 WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
372 NOT (DET.TRX_LINE_CLASS = 'CREDIT_MEMO' and
373 DET.EXTRACT_SOURCE_LEDGER = 'AR' and
374 substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN ('33','34'));
375
376 IF ( g_level_statement>= g_current_runtime_level ) THEN
377 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
378 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
379 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
380 ' l_delete_sql_string : '||l_delete_sql_string );
381
382 END IF;
383
384
385 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWSEDI' then
386 /* Taiwanese EDI Government Uniform Invoice
387 This report should print only the posted transaction if it's not a voided
388 invoices.
389 */
390 /* DELETE from ZX_REP_TRX_DETAIL_T DET
391 WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
392 NOT
393 (
394 (DET.EXTRACT_SOURCE_LEDGER = 'AR' and
395 DET.TRX_LINE_CLASS in ('INVOICE', 'CREDIT_MEMO') and
396 substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN ('31','32','33','34','35','36','37') and
397
398 ((DET.DOC_EVENT_STATUS = 'CANCELLED' and
399 DET.POSTED_FLAG = 'Y' ) or
400 DET.DOC_EVENT_STATUS = 'VALIDATED')
401 )
402 or
403 (DET.EXTRACT_SOURCE_LEDGER = 'AP' and
404 DET.POSTED_FLAG = 'Y' and
405 DET.TRX_LINE_CLASS in ('STANDARD INVOICES','AP_CREDIT_MEMO', 'AP_DEBIT_MEMO') and
406 substrb(DET.DOCUMENT_SUB_TYPE, 10,2) IN ('21','22', '23','24','25','26','27','28'))
407 );
408 */
409
410 DELETE
411 FROM zx_rep_trx_detail_t dtl
412 WHERE DTL.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
413 AND DTL.EXTRACT_SOURCE_LEDGER = 'AR'
414 AND EXISTS (SELECT 1 FROM ra_cust_trx_types_all types
415 WHERE types.org_id = dtl.internal_organization_id
416 AND dtl.trx_type_id = types.cust_trx_type_id
417 AND NVL(types.global_attribute1,'99') NOT IN ('31','32','33','34','35','36','37'));
418
419
420
421 IF ( g_level_statement>= g_current_runtime_level ) THEN
422 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
423 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
424 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
425 ' l_delete_sql_string : '||l_delete_sql_string );
426
427 END IF;
428
429 end if;
430
431 IF (g_level_statement >= g_current_runtime_level ) THEN
432 FND_LOG.STRING(g_level_statement,
433 'ZX.TRL.ja_tax_extract.filter_ja_tax_lines.END',
434 'ja_tax_extract.filter_ar_tax_lines(-)');
435 END IF;
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 IF ( g_level_statement>= g_current_runtime_level ) THEN
440 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines',
441 'Error Message : '||substrb(SQLERRM,1,120) );
442 END IF;
443
444 END filter_ja_ar_tax_lines;
445
446
447
448 /*===========================================================================+
449 | PROCEDURE |
450 | FILTER_JA_AP_TAX_LINES |
451 | Type : Public |
452 | Pre-req : None |
453 | Function : |
454 | This procedure is called to filter the records of transaction tables |
455 | by selecting only the records associated with JA specific lookup type |
456 | all unnecessary rows in ZX_REP_DETAIL_T table are deleted |
457 | |
458 | Called from AR_TAX_EXTRACT.EXECUTE_SQL. |
459 | |
460 | Parameters : |
461 | IN : p_report_name varchar2 Required |
462 | p_request_id number Required |
463 | |
464 | MODIFICATION HISTORY |
465 | 27-Oct-03 Asako Takahashi created |
466 | |
467 | |
468 +===========================================================================*/
469
470
471 PROCEDURE FILTER_JA_AP_TAX_LINES
472 (
473 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
474 )
475
476 /* Following parameters are removed since we now access global variables directly
477 (
478 P_REPORT_NAME IN varchar2,
479 P_REQUEST_ID IN number,
480 P_GUI_TYPE IN varchar2,
481 P_REPRINT IN varchar2,
482 -- P_APPLIED_TRX_NUMBER_LOW in varchar2, -- no longer required TRL perform this filter
483 -- P_APPLIED_TRX_NUMBER_HIGH in varchar2, -- DET.ADJUSTED_DOC_NUMBER >= ''' ||P_ADJUSTED_DOC_NUM_LOW
484 P_MRCSOBTYPE in varchar2,
485 P_REPORTING_LEVEL in varchar2,
486 P_REPORTING_CONTEXT in number,
487 P_SET_OF_BOOKS_ID in number
488 )
489 */
490
491 IS
492 l_delete_sql_string varchar2(3000);
493 lp_gui_type_where varchar2(100);
494 lp_reprint_where varchar2(100);
495 lp_applied_trx_num_from_where varchar2(100);
496 lp_applied_trx_num_to_where varchar2(100);
497 lp_from_payments varchar2(30);
498 lp_from_checks varchar2(30);
499 l_vendor_name VARCHAR2(80);
500 lp_where_org_art varchar2(2000);
501 type NumList is TABLE OF number
502 INDEX BY binary_integer;
503 nums NumList;
504
505 cnt NUMBER:= 0;
506 org_list VARCHAR2(32767):= NULL;
507
508 CURSOR c_ar (p_org_id NUMBER)
509 IS SELECT vat_tax_id
510 FROM ar_vat_tax_all_b
511 WHERE org_id = p_org_id;
512
513 CURSOR c_ap (p_org_id NUMBER)
514 IS SELECT tax_id
515 FROM ap_tax_codes_all
516 WHERE org_id = p_org_id;
517
518
519 BEGIN
520 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
521 IF (g_level_procedure >= g_current_runtime_level ) THEN
522 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES.BEGIN',
523 'ZX_JA_EXTRACT_PKG.filter_ap_tax_lines(+)');
524 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES.BEGIN',
525 'P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME : '||P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME);
526 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES.BEGIN',
527 'P_TRL_GLOBAL_VARIABLES_REC.DOCUMENT_SUB_TYPE : '||P_TRL_GLOBAL_VARIABLES_REC.DOCUMENT_SUB_TYPE );
528
529 END IF;
530
531 if P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWPVAT' then
532 /* Taiwanese Input VAT Rerport */
533
534 DELETE from ZX_REP_TRX_DETAIL_T DET
535 WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
536 NOT (DET.EXTRACT_SOURCE_LEDGER = 'AP' and
537 DET.TRX_LINE_CLASS IN ('STANDARD INVOICES', 'AP_CREDIT_MEMO', 'AP_DEBIT_MEMO') and
538 substrb(DET.DOCUMENT_SUB_TYPE, 10, 2) in ('21','22','23','24','25','26','27','28')
539 );
540
541 IF ( g_level_statement>= g_current_runtime_level ) THEN
542 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
543 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
544 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
545 'ja_tax_extract.filter_ja_tax_lines '||'l_delete_sql_string: ' || l_delete_sql_string);
546 END IF;
547
548 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWPSPC' then
549 /* Taiwanese Payables Sales/Purchase Return */
550
551 if P_TRL_GLOBAL_VARIABLES_REC.DOCUMENT_SUB_TYPE is NOT NULL then
552 lp_gui_type_where:= ' and substrb(DET.DOCUMENT_SUB_TYPE,10,2) = ''' || P_TRL_GLOBAL_VARIABLES_REC.DOCUMENT_SUB_TYPE || '''';
553 end if;
554
555 /* Need to be cerified
556 P_REPRINT should be available in Parameter Rec
557
558 if nvl(P_TRL_GLOBAL_VARIABLES_REC.REPRINT, 'N') <> 'Y' then
559 lp_reprint_where:= ' and ai.global_attribute4 is NULL ';
560 end if;
561 */
562 l_delete_sql_string:=
563 'DELETE from ZX_REP_TRX_DETAIL_T DET
564 WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
565 NOT EXISTS (SELECT 1 FROM AP_INVOICES ai
566 WHERE DET.EXTRACT_SOURCE_LEDGER = ''AP'' and
567 DET.TRX_LINE_CLASS IN (''STANDARD INVOICES'',''AP_CREDIT_MEMO'', ''AP_DEBIT_MEMO'') and
568 (DET.APPLIED_TO_TRX_NUMBER = ai.invoice_num OR DET.TRX_NUMBER = ai.invoice_num) and
569 DET.BILLING_TRADING_PARTNER_ID = ai.vendor_id and
570 substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN (''23'',''24'') '||
571 lp_gui_type_where ||
572 lp_reprint_where||')';
573 /*Bug 5439099 added the or condition check for trx number to accept standard invoices of type 23,24*/
574
575 EXECUTE IMMEDIATE l_delete_sql_string;
576
577 IF ( g_level_statement>= g_current_runtime_level ) THEN
578 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
579 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
580 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
581 ' l_delete_sql_string : '||l_delete_sql_string );
582
583 END IF;
584
585 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWSPRF' then
586 /* Taiwanese Pro Forma 401 Report */
587 DELETE from ZX_REP_TRX_DETAIL_T DET
588 WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
589 NOT (
590 (DET.EXTRACT_SOURCE_LEDGER = 'AR' and
591 DET.TRX_LINE_CLASS in ('INVOICE', 'CREDIT_MEMO') and
592 DET.DOC_EVENT_STATUS <> 'CANCELLED' and
593 substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN
594 ('31','32','33','34','35','36','37')) or
595 (DET.EXTRACT_SOURCE_LEDGER = 'AP' and
596 DET.TRX_LINE_CLASS IN ('STANDARD INVOICES', 'AP_CREDIT_MEMO', 'AP_DEBIT_MEMO') and
597 substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN
598 ('21','22','23','24','25','26','27','28'))
599 );
600
601
602
603 IF ( g_level_statement>= g_current_runtime_level ) THEN
604 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
605 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
606 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
607 ' l_delete_sql_string : '||l_delete_sql_string );
608
609 END IF;
610
611
612 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWPPRD' then
613 /* Taiwanese Purchase Return and Discount Report */
614 DELETE from ZX_REP_TRX_DETAIL_T DET
615 WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
616 NOT (DET.EXTRACT_SOURCE_LEDGER = 'AP' and
617 substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN ('23','24') and
618 DET.CANCEL_FLAG <> 'Y'
619 );
620 IF ( g_level_statement>= g_current_runtime_level ) THEN
621 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
622 ' NULL supplier '||l_delete_sql_string );
623
624 END IF;
625
626 IF ( g_level_statement>= g_current_runtime_level ) THEN
627 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
628 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
629 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
630 ' l_delete_sql_string : '||l_delete_sql_string );
631
632 END IF;
633
634 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXSGAGAL' then
635 /* Singaporean Input Taxes Gain/Loss Report */
636 DELETE from ZX_REP_TRX_DETAIL_T DET
637 WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
638 NOT (DET.EXTRACT_SOURCE_LEDGER = 'AP' and
639 DET.FUNCTIONAL_CURRENCY_CODE <> DET.TRX_CURRENCY_CODE and
640 DET.SUPPLIER_EXCHANGE_RATE IS NOT NULL and
641 DET.CURRENCY_CONVERSION_RATE is NOT NULL);
642
643 IF ( g_level_statement>= g_current_runtime_level ) THEN
644 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
645 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
646 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
647 ' l_delete_sql_string : '||l_delete_sql_string );
648
649 END IF;
650
651
652 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXSGGF5' then
653 /* Singapore GST F5 Report */
654 /* Singaporean GST F5 report assumed that tax journals in gl where populated by tax extract
655 based on the reporting level and reporting context parameter provided by the user.
656 However, Data in gl is not fully partitioned by org_id (only gl_je_batches is partitioned by
657 org) and to support this, we use lp_where_org_art selection criteria */
658
659 if P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEVEL = '1000' then
660 lp_where_org_art:= ' and DET.LEDGER_ID = ' || P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID;
661
662 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEVEL = '2000' then
663 lp_where_org_art:= ' and legal_entity_org_id = ' || P_TRL_GLOBAL_VARIABLES_REC.REPORTING_CONTEXT;
664
665 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEVEL = '3000' then
666
667 for r_ar IN c_ar(P_TRL_GLOBAL_VARIABLES_REC.REPORTING_CONTEXT) LOOP
668 nums(cnt):= r_ar.vat_tax_id;
669 cnt:= cnt+1;
670 end loop;
671
672 for r_ap IN c_ap(P_TRL_GLOBAL_VARIABLES_REC.REPORTING_CONTEXT) LOOP
673 nums(cnt):= r_ap.tax_id;
674 cnt:= cnt+1;
675 end loop;
676
677 for i in nums.FIRST .. nums.LAST LOOP
678 if i = nums.FIRST THEN
679 org_list:= nums(i);
680 else
681 org_list:= org_list || ', ' || nums(i);
682 end if;
683 end loop;
684
685 if org_list IS NOT NULL THEN
686 lp_where_org_art:= ' and DET.TAX_RATE_ID in (' || org_list || ')';
687 else
688 lp_where_org_art:= NULL;
689 end if;
690 end if;
691
692
693 l_delete_sql_string:=
694 ' DELETE from ZX_REP_TRX_DETAIL_T DET
695 WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
696 NOT ((DET.EXTRACT_SOURCE_LEDGER in (''AR'',''GL'') '||
697 lp_where_org_art ||' ) or
698 (DET.EXTRACT_SOURCE_LEDGER = ''AP''))';
699
700 -- EXECUTE IMMEDIATE l_delete_sql_string;
701
702
703 IF ( g_level_statement>= g_current_runtime_level ) THEN
704 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
705 'Deleted Count : '||to_char(SQL%ROWCOUNT) );
706 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
707 ' l_delete_sql_string : '||l_delete_sql_string );
708
709 END IF;
710
711 end if;
712
713 IF (g_level_procedure >= g_current_runtime_level ) THEN
714 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES.END',
715 'ZX_JA_EXTRACT_PKG.filter_ap_tax_lines(-):');
716 END IF;
717
718 EXCEPTION
719 WHEN OTHERS THEN
720 IF ( g_level_statement>= g_current_runtime_level ) THEN
721 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.FILTER_JA_AP_TAX_LINES',
722 'Error Message for report : '||substrb(SQLERRM,1,120) );
723 END IF;
724
725 END filter_ja_ap_tax_lines;
726
727
728
729 /*===========================================================================+
730 | PUBLIC PROCEDURE |
731 | POPULATE_JA_AR |
732 | Type : Public |
733 | Pre-req : None |
734 | Function : |
735 | This procedure calls the API to select the JA specific data from |
736 | JA receivables tables. |
737 | |
738 | Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
739 | |
740 | Parameters : |
741 | IN : p_zx_rep_detail_rec zx_rep_trx_detail_t%rowtype |
742 | p_report_name varchar2 -- required |
743 | |
744 | |
745 | MODIFICATION HISTORY |
746 | 27-Oct-03 Asako Takahashi created |
747 | |
748 | |
749 +===========================================================================*/
750
751 PROCEDURE POPULATE_JA_AR
752 (
753 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
754 )
755
756 /* Following parameter is removed since we now access global variables directly
757 (
758 -- IN parameters are passed as global variables.
759 -- DETAIL_TAX_LINE_ID_TBL
760 -- TRX_STATUS_CODE_TBL
761 -- TRX_BUSINESS_CATEGORY_TBL
762 -- TRX_BATCH_SOURCE_NAME_TBL
763 -- TRX_BATCH_SOURCE_ID_TBL
764 -- DOCUMENT_SUB_TYPE_TBL
765 -- TRX_BATCH_SOURCE_ID_TBL
766 -- GDF_RA_CUST_TRX_ATT7_TBL
767 -- GDF_RA_CUST_TRX_ATT5_TBL
768 -- GDF_RA_CUST_TRX_ATT6_TBL
769 -- PROD_FISC_CLASSIFICATION_TBL
770 P_REPORT_NAME IN varchar2
771 )
772 */
773
774 IS
775
776 P_LOOKUP_TYPE varchar2(500);
777 P_LOOKUP_CODE varchar2(1000); --Bug 5453806
778
779 TYPE GOVERNMENT_TAX_TYPE_TBL is TABLE OF
780 ZX_REP_TRX_JX_EXT_T.TAX_STATUS_MNG%TYPE INDEX BY BINARY_INTEGER;
781
782 TYPE DEDUCTIBLE_TYPE_TBL is TABLE OF
783 ZX_REP_TRX_JX_EXT_T.TRX_BUSINESS_CATEGORY_MNG%TYPE INDEX BY BINARY_INTEGER;
784
785 TYPE EXPORT_METHOD_TBL is TABLE OF
786 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE4%TYPE INDEX BY BINARY_INTEGER;
787
788 TYPE EXPORT_CERTIFICATE_NAME_TBL is TABLE OF
789 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE5%TYPE INDEX BY BINARY_INTEGER;
790
791 TYPE EXPORT_TYPE_TBL is TABLE OF
792 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE6%TYPE INDEX BY BINARY_INTEGER;
793
794 TYPE INVOICE_WORD_TBL is TABLE OF
795 ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT3%TYPE INDEX BY BINARY_INTEGER;
796
797 TYPE INITIAL_TRX_NUM_TBL is TABLE OF
798 ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT2%TYPE INDEX BY BINARY_INTEGER;
799
800 TYPE FINAL_TRX_NUM_TBL is TABLE OF
801 ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT4%TYPE INDEX BY BINARY_INTEGER;
802
803 TYPE REFERENCE_TRX_SRC_TBL is TABLE OF
804 ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT1%TYPE INDEX BY BINARY_INTEGER;
805
806 TYPE ORG_TRX_NUMBER_TBL is TABLE OF
807 ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT9%TYPE INDEX BY BINARY_INTEGER;
808
809 TYPE GDF_RA_CUST_TRX_ATT4_TBL is TABLE OF
810 ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT4%TYPE INDEX BY BINARY_INTEGER;
811
812 TYPE GDF_RA_CUST_TRX_ATT5_TBL is TABLE OF
813 ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT5%TYPE INDEX BY BINARY_INTEGER;
814
815 TYPE GDF_RA_CUST_TRX_ATT6_TBL is TABLE OF
816 ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT6%TYPE INDEX BY BINARY_INTEGER;
817
818 TYPE GDF_RA_CUST_TRX_ATT7_TBL is TABLE OF
819 ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT7%TYPE INDEX BY BINARY_INTEGER;
820
821 TYPE GDF_RA_CUST_TRX_ATT8_TBL is TABLE OF
822 ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT7%TYPE INDEX BY BINARY_INTEGER;
823
824 TYPE CNT_TAXABLE_AMT_TBL is TABLE OF
825 ZX_REP_TRX_JX_EXT_T.NUMERIC1%TYPE INDEX BY BINARY_INTEGER;
826
827 TYPE WINE_CIGARETTE_TBL is TABLE OF
828 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
829
830 TYPE ATTRIBUTE1_TBL is TABLE OF
831 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
832
833
834 X_GOVERNMENT_TAX_TYPE_TBL GOVERNMENT_TAX_TYPE_TBL;
835 X_DEDUCTIBLE_TYPE_TBL DEDUCTIBLE_TYPE_TBL;
836 X_EXPORT_METHOD_TBL EXPORT_METHOD_TBL;
837 X_EXPORT_CERTIFICATE_NAME_TBL EXPORT_CERTIFICATE_NAME_TBL;
838 X_EXPORT_TYPE_TBL EXPORT_TYPE_TBL;
839 X_INVOICE_WORD_TBL INVOICE_WORD_TBL;
840 X_INITIAL_TRX_NUM_TBL INITIAL_TRX_NUM_TBL;
841 X_FINAL_TRX_NUM_TBL FINAL_TRX_NUM_TBL;
842 X_REFERENCE_TRX_SRC_TBL REFERENCE_TRX_SRC_TBL;
843
844 X_ORG_TRX_NUMBER_TBL ORG_TRX_NUMBER_TBL;
845 X_GDF_RA_CUST_TRX_ATT4_TBL GDF_RA_CUST_TRX_ATT4_TBL;
846 X_GDF_RA_CUST_TRX_ATT5_TBL GDF_RA_CUST_TRX_ATT5_TBL;
847 X_GDF_RA_CUST_TRX_ATT6_TBL GDF_RA_CUST_TRX_ATT6_TBL;
848 X_GDF_RA_CUST_TRX_ATT7_TBL GDF_RA_CUST_TRX_ATT7_TBL;
849 X_GDF_RA_CUST_TRX_ATT8_TBL GDF_RA_CUST_TRX_ATT8_TBL;
850 X_CNT_TAXABLE_AMT_TBL CNT_TAXABLE_AMT_TBL;
851 X_WINE_CIGARETTE_TBL WINE_CIGARETTE_TBL;
852
853 lp_count_taxable number(15);
854 test varchar2(200);
855
856 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
857 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
858 l_tax_status_code_tbl ZX_EXTRACT_PKG.TAX_STATUS_CODE_TBL;
859 l_trx_business_category_tbl ZX_EXTRACT_PKG.TRX_BUSINESS_CATEGORY_TBL;
860 l_trx_batch_source_name_tbl ZX_EXTRACT_PKG.TRX_BATCH_SOURCE_NAME_TBL;
861 l_document_sub_type_tbl ZX_EXTRACT_PKG.DOCUMENT_SUB_TYPE_TBL;
862 l_prod_fisc_classification_tbl ZX_EXTRACT_PKG.PROD_FISC_CLASSIFICATION_TBL;
863
864 l_location_code_tbl ATTRIBUTE1_TBL;
865 l_address_line_1_tbl ATTRIBUTE1_TBL;
866 l_address_line_2_tbl ATTRIBUTE1_TBL;
867 l_address_line_3_tbl ATTRIBUTE1_TBL;
868 l_city_tbl ATTRIBUTE1_TBL;
869 l_region_1_tbl ATTRIBUTE1_TBL;
870 l_region_2_tbl ATTRIBUTE1_TBL;
871 l_postal_code_tbl ATTRIBUTE1_TBL;
872 l_country_code_tbl ATTRIBUTE1_TBL;
873 l_loc_tax_reg_num_tbl ATTRIBUTE1_TBL;
874 l_taxable_person_tbl ATTRIBUTE1_TBL;
875 l_ind_sub_classif_tbl ATTRIBUTE1_TBL;
876 l_ind_classif_tbl ATTRIBUTE1_TBL;
877
878 --Bug 5251425
879 l_trx_date_tbl zx_extract_pkg.trx_date_tbl;
880 x_trx_date_tbl zx_extract_pkg.trx_date_tbl;
881 l_canonical_date VARCHAR2(20);
882 l_roc_year NUMBER(15);
883 l_roc_mmdd VARCHAR2(5);
884 l_roc_date VARCHAR2(20);
885 l_trx_date DATE;
886
887 BEGIN
888
889 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
890 IF (g_level_statement >= g_current_runtime_level ) THEN
891 FND_LOG.STRING(g_level_statement,
892 'ZX.TRL.ja_tax_extract.ja_tax_extract.populate_ja_ar.BEGIN',
893 'ja_tax_extract.populate_ja_ar(+)');
894 END IF;
895
896 IF ( g_level_statement>= g_current_runtime_level ) THEN
897 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
898 'P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME : '||P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME );
899 END IF;
900
901
902 SELECT detail_tax_line_id,
903 trx_id,
904 tax_status_code,
905 trx_business_category,
906 trx_batch_source_name,
907 document_sub_type,
908 product_fisc_classification,
909 trx_date --Bug 5251425
910 BULK COLLECT INTO l_detail_tax_line_id_tbl,
911 l_trx_id_tbl,
912 l_tax_status_code_tbl,
913 l_trx_business_category_tbl,
914 l_trx_batch_source_name_tbl,
915 l_document_sub_type_tbl,
916 l_prod_fisc_classification_tbl,
917 l_trx_date_tbl --Bug 5251425
918 FROM zx_rep_trx_detail_t itf1
919 WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
920
921 IF ( g_level_statement>= g_current_runtime_level ) THEN
922 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
923 'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
924 END IF;
925
926 P_LOOKUP_CODE:= NULL;
927
928 if P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = ('ZXTWRVAT') and l_detail_tax_line_id_tbl.count <> 0 then
929
930 /****** Populate the missing columns ********/
931 FOR i in 1 .. nvl(l_detail_tax_line_id_tbl.count,0) LOOP
932
933 P_LOOKUP_TYPE:= 'JATW_GOVERNMENT_TAX_TYPE';
934 P_LOOKUP_CODE:= l_tax_status_code_tbl(i);
935 -- P_LOOKUP_CODE:=l_tax_status_id_tbl(i);
936 X_GOVERNMENT_TAX_TYPE_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE, l_trx_date_tbl(i));
937
938 P_LOOKUP_TYPE:= 'JATW_DEDUCTIBLE_TYPE';
939 --Bug 5453806
940 -- select translate(l_trx_business_category_tbl(i), '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') into P_LOOKUP_CODE from dual;
941 p_lookup_code := l_trx_business_category_tbl(i);
942 X_DEDUCTIBLE_TYPE_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE,l_trx_date_tbl(i));
943 IF (g_level_statement >= g_current_runtime_level ) THEN
944 FND_LOG.STRING(g_level_statement,
945 'ZX.TRL.ja_tax_extract.ja_tax_extract.populate_ja_ar',
946 'P_LOOKUP_CODE:'||P_LOOKUP_CODE||'-'||l_tax_status_code_tbl(i));
947 END IF;
948
949 END LOOP;
950
951 /****** Bulk Insert into Ext Table *******/
952 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
953
954 INSERT INTO ZX_REP_TRX_JX_EXT_T
955 (detail_tax_line_ext_id,
956 detail_tax_line_id,
957 tax_status_mng, -- government tax type meaning
958 trx_business_category_mng, -- deductible code meaning
959 created_by,
960 creation_date,
961 last_updated_by,
962 last_update_date,
963 last_update_login,
964 request_id)
965 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
966 l_detail_tax_line_id_tbl(i),
967 X_GOVERNMENT_TAX_TYPE_TBL(i),
968 X_DEDUCTIBLE_TYPE_TBL(i),
969 fnd_global.user_id,
970 sysdate,
971 fnd_global.user_id,
972 sysdate,
973 fnd_global.login_id,
974 p_trl_global_variables_rec.request_id);
975
976
977 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWRUIL' and l_detail_tax_line_id_tbl.count <> 0 then
978
979 /****** Populate the missing columns ********/
980
981 FOR i in 1 .. nvl(l_detail_tax_line_id_tbl.count,0) LOOP
982
983 P_LOOKUP_TYPE:= 'JATW_GOVERNMENT_TAX_TYPE';
984 P_LOOKUP_CODE:= l_tax_status_code_tbl(i);
985 X_GOVERNMENT_TAX_TYPE_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE,l_trx_date_tbl(i));
986 -- Added new trx date parameter for checking date effectivity
987
988
989 -- Get GUI MISSING SEQUENCE
990 -- Get GUI Source
991 GET_GUI_SOURCE(l_trx_batch_source_name_tbl(i),
992 X_REFERENCE_TRX_SRC_TBL(i), X_INITIAL_TRX_NUM_TBL(i),
993 X_INVOICE_WORD_TBL(i), X_FINAL_TRX_NUM_TBL(i));
994
995 -- Get Original Transaction Number
996 IF substrb(l_document_sub_type_tbl(i),10,2) in ('33', '34') THEN
997 GET_ORG_TRX_NUMBER(l_trx_batch_source_name_tbl(i), l_trx_id_tbl(i),X_ORG_TRX_NUMBER_TBL(i));
998 ELSE
999 X_ORG_TRX_NUMBER_TBL(i) := NULL;
1000 END IF;
1001
1002 --Bug 5251425 : To get the taiwan specific date format.
1003 IF ( g_level_statement>= g_current_runtime_level ) THEN
1004 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1005 'Before call to fnd_date.date_to_canonical to get canonical date format ');
1006 END IF;
1007 l_canonical_date := fnd_date.date_to_canonical(l_trx_date_tbl(i));
1008 l_roc_year := TO_NUMBER(SUBSTRB(l_canonical_date,1,4)) - 1911;
1009 l_roc_mmdd := SUBSTRB(l_canonical_date,6,5);
1010 l_roc_date := TO_CHAR(l_roc_year) || '/' || l_roc_mmdd;
1011 x_trx_date_tbl(i) := to_date(l_roc_date,'YYYY/MM/DD');
1012
1013 END LOOP;
1014
1015
1016 /****** Bulk Insert into Ext Table *******/
1017
1018 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1019
1020 INSERT INTO ZX_REP_TRX_JX_EXT_T
1021 (detail_tax_line_ext_id,
1022 detail_tax_line_id,
1023 tax_status_mng, -- government tax type meaning
1024 gdf_ra_cust_trx_att9, -- orginal transaction number ZXTWRUIL
1025 gdf_ra_batch_sources_att1, -- reference transaction source ZXTWRUIL
1026 gdf_ra_batch_sources_att2, -- initial trx num ZXTWRUIL
1027 gdf_ra_batch_sources_att3, -- invoice word ZXTWRUIL
1028 gdf_ra_batch_sources_att4, -- final trx num ZXTWRUIL
1029 attribute15, --Bug 5251425
1030 created_by,
1031 creation_date,
1032 last_updated_by,
1033 last_update_date,
1034 last_update_login,
1035 request_id)
1036 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1037 l_detail_tax_line_id_tbl(i),
1038 X_GOVERNMENT_TAX_TYPE_TBL(i),
1039 X_ORG_TRX_NUMBER_TBL(i),
1040 X_REFERENCE_TRX_SRC_TBL(i),
1041 X_INITIAL_TRX_NUM_TBL(i),
1042 X_INVOICE_WORD_TBL(i),
1043 X_FINAL_TRX_NUM_TBL(i),
1044 x_trx_date_tbl(i), --Bug 5251425
1045 fnd_global.user_id,
1046 sysdate,
1047 fnd_global.user_id,
1048 sysdate,
1049 fnd_global.login_id,
1050 p_trl_global_variables_rec.request_id);
1051
1052 IF ( g_level_statement>= g_current_runtime_level ) THEN
1053 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1054 'After insertion into zx_rep_trx_jx_ext_t ');
1055 END IF;
1056 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWRZTR' and l_detail_tax_line_id_tbl.count <> 0 then
1057
1058 /****** Populate the missing columns ********/
1059
1060 FOR i in 1 .. nvl(l_detail_tax_line_id_tbl.count,0) LOOP
1061
1062 GET_EXPORT_INFO(l_trx_id_tbl(i), X_GDF_RA_CUST_TRX_ATT4_TBL(i),
1063 X_GDF_RA_CUST_TRX_ATT5_TBL(i), X_GDF_RA_CUST_TRX_ATT6_TBL(i),
1064 X_GDF_RA_CUST_TRX_ATT7_TBL(i), X_GDF_RA_CUST_TRX_ATT8_TBL(i));
1065
1066 P_LOOKUP_TYPE:= 'JATW_EXPORT_METHOD';
1067 P_LOOKUP_CODE:= X_GDF_RA_CUST_TRX_ATT6_TBL(i);
1068 X_EXPORT_METHOD_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE,l_trx_date_tbl(i));
1069
1070 P_LOOKUP_TYPE:= 'JATW_EXPORT_CERTIFICATE_NAME';
1071 P_LOOKUP_CODE:= X_GDF_RA_CUST_TRX_ATT5_TBL(i);
1072 X_EXPORT_CERTIFICATE_NAME_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE,l_trx_date_tbl(i));
1073
1074 P_LOOKUP_TYPE:= 'JATW_EXPORT_TYPE';
1075 P_LOOKUP_CODE:= X_GDF_RA_CUST_TRX_ATT7_TBL(i);
1076 X_EXPORT_TYPE_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE,l_trx_date_tbl(i));
1077
1078 END LOOP;
1079
1080
1081 /****** Bulk Insert into Ext Table *******/
1082
1083 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1084
1085 INSERT INTO ZX_REP_TRX_JX_EXT_T
1086 (detail_tax_line_ext_id,
1087 detail_tax_line_id,
1088 attribute4, -- export method
1089 attribute5, -- export certificate name
1090 attribute6, -- export type
1091 gdf_ra_cust_trx_att4, -- export certificate number ZXTWRZTR
1092 gdf_ra_cust_trx_att5, -- export name ZXTWRZTR
1093 gdf_ra_cust_trx_att6, -- export method ZXTWRZTR
1094 gdf_ra_cust_trx_att7, -- export type ZXTWRZTR
1095 gdf_ra_cust_trx_att8, -- export date ZXTWRZTR
1096 created_by,
1097 creation_date,
1098 last_updated_by,
1099 last_update_date,
1100 last_update_login,
1101 request_id)
1102 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1103 l_detail_tax_line_id_tbl(i),
1104 X_EXPORT_METHOD_TBL(i),
1105 X_EXPORT_CERTIFICATE_NAME_TBL(i),
1106 X_EXPORT_TYPE_TBL(i),
1107 X_GDF_RA_CUST_TRX_ATT4_TBL(i),
1108 X_GDF_RA_CUST_TRX_ATT5_TBL(i),
1109 X_GDF_RA_CUST_TRX_ATT6_TBL(i),
1110 X_GDF_RA_CUST_TRX_ATT7_TBL(i),
1111 X_GDF_RA_CUST_TRX_ATT8_TBL(i),
1112 fnd_global.user_id,
1113 sysdate,
1114 fnd_global.user_id,
1115 sysdate,
1116 fnd_global.login_id,
1117 p_trl_global_variables_rec.request_id);
1118
1119 IF ( g_level_statement>= g_current_runtime_level ) THEN
1120 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1121 'After insertion into zx_rep_trx_jx_ext_t ');
1122 END IF;
1123 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWSEDI' and l_detail_tax_line_id_tbl.count <> 0 then
1124 INSERT INTO ZX_REP_TRX_JX_EXT_T(
1125 detail_tax_line_ext_id,
1126 detail_tax_line_id,
1127 document_sub_type_mng,
1128 created_by,
1129 creation_date,
1130 last_updated_by,
1131 last_update_date,
1132 last_update_login,
1133 request_id
1134 )
1135 SELECT
1136 zx_rep_trx_jx_ext_t_s.nextval,
1137 dtl.detail_tax_line_id,
1138 types.global_attribute1,
1139 fnd_global.user_id,
1140 sysdate,
1141 fnd_global.user_id,
1142 sysdate,
1143 fnd_global.login_id,
1144 p_trl_global_variables_rec.request_id
1145 FROM zx_rep_trx_detail_t dtl,
1146 ra_cust_trx_types_all types
1147 WHERE dtl.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
1148 and dtl.extract_source_ledger = 'AR'
1149 and types.cust_trx_type_id = dtl.trx_type_id
1150 and types.org_id = dtl.internal_organization_id ;
1151
1152 IF ( g_level_statement>= g_current_runtime_level ) THEN
1153 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1154 'After insertion into zx_rep_trx_jx_ext_t ');
1155 END IF;
1156 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'RXZXKVAT' THEN
1157
1158 SELECT loc.location_code,
1159 loc.ADDRESS_LINE_1,
1160 loc.ADDRESS_LINE_2,
1161 loc.ADDRESS_LINE_3,
1162 loc.TOWN_OR_CITY ,
1163 loc.REGION_1,
1164 loc.REGION_2,
1165 loc.POSTAL_CODE,
1166 loc.COUNTRY,
1167 loc.GLOBAL_ATTRIBUTE1,
1168 loc.GLOBAL_ATTRIBUTE4,
1169 -- loc.GLOBAL_ATTRIBUTE5,
1170 -- loc.GLOBAL_ATTRIBUTE6,
1171 -- loc.GLOBAL_ATTRIBUTE11,
1172 dtl.detail_tax_line_id
1173 BULK COLLECT INTO l_location_code_tbl,
1174 l_address_line_1_tbl,
1175 l_address_line_2_tbl,
1176 l_address_line_3_tbl,
1177 l_city_tbl,
1178 l_region_1_tbl,
1179 l_region_2_tbl,
1180 l_postal_code_tbl,
1181 l_country_code_tbl,
1182 l_loc_tax_reg_num_tbl,
1183 l_taxable_person_tbl,
1184 -- l_ind_sub_classif_tbl,
1185 -- l_ind_classif_tbl,
1186 l_detail_tax_line_id_tbl
1187 FROM zx_reporting_types_b rep_type,
1188 zx_report_codes_assoc rep_ass,
1189 hr_locations loc,
1190 zx_rep_trx_detail_t dtl
1191 WHERE rep_type.reporting_type_code = 'KR_BUSINESS_LOCATIONS'
1192 AND rep_ass.reporting_type_id = rep_type.reporting_type_id
1193 AND rep_ass.entity_code = 'ZX_RATES'
1194 AND rep_ass.entity_id = dtl.tax_rate_id
1195 AND dtl.tax_regime_code = rep_type.tax_regime_code
1196 AND rep_ass.reporting_code_char_value = loc.location_code
1197 AND loc.global_attribute_category = 'JA.KR.PERWSLOC.WITHHOLDING'
1198 AND dtl.application_id in (222,101)
1199 AND dtl.request_id = p_trl_global_variables_rec.request_id ;
1200
1201 IF ( g_level_statement>= g_current_runtime_level ) THEN
1202 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1203 'Reporting Type Query Count : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
1204
1205 END IF;
1206
1207 FOR i in 1..nvl(l_detail_tax_line_id_tbl.last, 0)
1208 LOOP
1209 l_ind_classif_tbl(i) := NULL;
1210 l_ind_sub_classif_tbl(i) := NULL;
1211 END LOOP;
1212
1213 IF p_trl_global_variables_rec.product = 'AR' THEN
1214 SELECT global_attribute8,
1215 global_attribute3
1216 BULK COLLECT INTO
1217 l_ind_classif_tbl,
1218 l_ind_sub_classif_tbl
1219 FROM hz_cust_acct_sites_all acct_site,
1220 zx_rep_trx_detail_t dtl
1221 WHERE acct_site.cust_acct_site_id = NVL(dtl.shipping_tp_address_id, dtl.billing_tp_address_id)
1222 AND dtl.application_id = 222
1223 AND dtl.request_id = p_trl_global_variables_rec.request_id ;
1224
1225 IF ( g_level_statement>= g_current_runtime_level ) THEN
1226 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1227 'AR Industry Class Query Count: '||to_char(nvl(l_ind_classif_tbl.count,0)) );
1228 END IF;
1229 END IF;
1230
1231
1232 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1233
1234 INSERT INTO ZX_REP_TRX_JX_EXT_T
1235 (detail_tax_line_ext_id,
1236 detail_tax_line_id,
1237 attribute1,
1238 attribute2,
1239 attribute3,
1240 attribute4,
1241 attribute5,
1242 attribute6,
1243 attribute7,
1244 attribute8,
1245 attribute9,
1246 attribute10,
1247 attribute11,
1248 attribute12,
1249 attribute13,
1250 created_by,
1251 creation_date,
1252 last_updated_by,
1253 last_update_date,
1254 last_update_login,
1255 request_id)
1256 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1257 l_detail_tax_line_id_tbl(i),
1258 l_location_code_tbl(i),
1259 l_address_line_1_tbl(i),
1260 l_address_line_2_tbl(i),
1261 l_address_line_3_tbl(i),
1262 l_city_tbl(i),
1263 l_region_1_tbl(i),
1264 l_region_2_tbl(i),
1265 l_postal_code_tbl(i),
1266 l_country_code_tbl(i),
1267 l_loc_tax_reg_num_tbl(i),
1268 l_taxable_person_tbl(i),
1269 l_ind_sub_classif_tbl(i),
1270 l_ind_classif_tbl(i),
1271 fnd_global.user_id,
1272 sysdate,
1273 fnd_global.user_id,
1274 sysdate,
1275 fnd_global.login_id,
1276 p_trl_global_variables_rec.request_id);
1277
1278 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXSGGF5' THEN
1279 bank_info(P_TRL_GLOBAL_VARIABLES_REC);
1280 end if;
1281
1282 IF ( g_level_statement>= g_current_runtime_level ) THEN
1283 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1284 'After insertion into zx_rep_trx_jx_ext_t ');
1285 END IF;
1286
1287 IF (g_level_procedure >= g_current_runtime_level ) THEN
1288 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar.END',
1289 'ZX_JA_EXTRACT_PKG.populate_ja_ar(-)');
1290 END IF;
1291
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 IF ( g_level_statement>= g_current_runtime_level ) THEN
1295 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1296 'Error Message for report : '||substrb(SQLERRM,1,120) );
1297 END IF;
1298
1299 END populate_ja_ar;
1300
1301
1302 /*===========================================================================+
1303 | PUBLIC PROCEDURE |
1304 | POPULATE_JA_AP |
1305 | Type : Public |
1306 | Pre-req : None |
1307 | Function : |
1308 | This procedure calls the API to select the JA specific data from |
1309 | JA payables tables. Currently only JA_LOOKUP_INFO plug-in is called |
1310 | inside. |
1311 | |
1312 | Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
1313 | |
1314 | Parameters : |
1315 | |
1316 | |
1317 | MODIFICATION HISTORY |
1318 | 27-Oct-03 Asako Takahashi created |
1319 | |
1320 | |
1321 +===========================================================================*/
1322
1323
1324 PROCEDURE POPULATE_JA_AP
1325 (
1326 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
1327 )
1328
1329 /* Following parameter is removed since we now access global variables directly
1330 (
1331 -- IN parameters are passed as global variables.
1332
1333 -- DETAIL_TAX_LINE_ID_TBL
1334 -- TRX_STATUS_CODE_TBL
1335 -- TRX_BUSINESS_CATEGORY_TBL
1336 -- BILLING_TRADING_PARTNER_ID_TBL
1337 -- ADJUSTED_DOC_NUMBER_TBL
1338 -- TRX_ID_TBL
1339 -- TAXABLE_AMT_TBL
1340 -- TAX_AMT_TBL
1341 -- CURRENCY_CONVERSION_RATE_TBL
1342 -- PRECISION_TBL
1343 -- REPRINT_TBL
1344 P_REPORT_NAME IN varchar2
1345 )
1346 */
1347
1348 IS
1349
1350
1351 TYPE GOVERNMENT_TAX_TYPE_TBL is TABLE OF
1352 ZX_REP_TRX_JX_EXT_T.TAX_STATUS_MNG%TYPE INDEX BY BINARY_INTEGER;
1353
1354 TYPE DEDUCTIBLE_TYPE_TBL is TABLE OF
1355 ZX_REP_TRX_JX_EXT_T.TRX_BUSINESS_CATEGORY_MNG%TYPE INDEX BY BINARY_INTEGER;
1356
1357 TYPE GUI_TYPE_TBL is TABLE OF
1358 ZX_REP_TRX_JX_EXT_T.DOCUMENT_SUB_TYPE_MNG%TYPE INDEX BY BINARY_INTEGER;
1359
1360 TYPE INVOICE_AMOUNT_TBL is TABLE OF
1361 ZX_REP_TRX_JX_EXT_T.NUMERIC12%TYPE INDEX BY BINARY_INTEGER;
1362
1363 TYPE BASE_AMOUNT_TBL is TABLE OF
1364 ZX_REP_TRX_JX_EXT_T.NUMERIC13%TYPE INDEX BY BINARY_INTEGER;
1365
1366 TYPE PRINT_DATE_TBL is TABLE OF
1367 ZX_REP_TRX_JX_EXT_T.GDF_AP_INVOICES_ATT4%TYPE INDEX BY BINARY_INTEGER;
1368
1369 TYPE INHOUSE_INV_AMT_TBL is TABLE OF
1370 ZX_REP_TRX_JX_EXT_T.NUMERIC1%TYPE INDEX BY BINARY_INTEGER;
1371
1372 TYPE SUPPLIER_INV_AMT_TBL is TABLE OF
1373 ZX_REP_TRX_JX_EXT_T.NUMERIC2%TYPE INDEX BY BINARY_INTEGER;
1374
1375 TYPE INHOUSE_TAX_AMT_TBL is TABLE OF
1376 ZX_REP_TRX_JX_EXT_T.NUMERIC3%TYPE INDEX BY BINARY_INTEGER;
1377
1378 TYPE SUPPLIER_TAX_AMT_TBL is TABLE OF
1379 ZX_REP_TRX_JX_EXT_T.NUMERIC4%TYPE INDEX BY BINARY_INTEGER;
1380
1381 TYPE TAXABLE_GAINLOSS_TBL is TABLE OF
1382 ZX_REP_TRX_JX_EXT_T.NUMERIC5%TYPE INDEX BY BINARY_INTEGER;
1383
1384 TYPE TAX_GAINLOSS_TBL is TABLE OF
1385 ZX_REP_TRX_JX_EXT_T.NUMERIC6%TYPE INDEX BY BINARY_INTEGER;
1386
1387 TYPE ATTRIBUTE1_TBL is TABLE OF
1388 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
1389
1390 X_GOVERNMENT_TAX_TYPE_TBL GOVERNMENT_TAX_TYPE_TBL;
1391 X_DEDUCTIBLE_TYPE_TBL DEDUCTIBLE_TYPE_TBL;
1392 X_GUI_TYPE_TBL GUI_TYPE_TBL;
1393 X_INVOICE_AMOUNT_TBL INVOICE_AMOUNT_TBL;
1394 X_BASE_AMOUNT_TBL BASE_AMOUNT_TBL;
1395 X_PRINT_DATE_TBL PRINT_DATE_TBL;
1396 X_INHOUSE_INV_AMT_TBL INHOUSE_INV_AMT_TBL;
1397 X_SUPPLIER_INV_AMT_TBL SUPPLIER_INV_AMT_TBL;
1398 X_INHOUSE_TAX_AMT_TBL INHOUSE_TAX_AMT_TBL;
1399 X_SUPPLIER_TAX_AMT_TBL SUPPLIER_TAX_AMT_TBL;
1400 X_TAXABLE_GAINLOSS_TBL TAXABLE_GAINLOSS_TBL;
1401 X_TAX_GAINLOSS_TBL TAX_GAINLOSS_TBL;
1402
1403 L_INHOUSE_INV_AMT_TBL INHOUSE_INV_AMT_TBL;
1404 L_SUPPLIER_INV_AMT_TBL SUPPLIER_INV_AMT_TBL;
1405 L_INHOUSE_TAX_AMT_TBL INHOUSE_TAX_AMT_TBL;
1406 L_SUPPLIER_TAX_AMT_TBL SUPPLIER_TAX_AMT_TBL;
1407 L_TAXABLE_GAINLOSS_TBL TAXABLE_GAINLOSS_TBL;
1408 L_TAX_GAINLOSS_TBL TAX_GAINLOSS_TBL;
1409
1410 P_LOOKUP_TYPE varchar2(100);
1411 P_LOOKUP_CODE varchar2(1000);
1412 P_VENDOR_ID number(15);
1413 P_INVOICE_NUM varchar2(30);
1414 l_trx_date_tbl zx_extract_pkg.trx_date_tbl;
1415
1416 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
1417 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
1418 l_trx_line_id_tbl ZX_EXTRACT_PKG.TRX_LINE_ID_TBL;
1419 l_tax_status_code_tbl ZX_EXTRACT_PKG.TAX_STATUS_CODE_TBL;
1420 l_tax_status_id_tbl ZX_EXTRACT_PKG.TAX_STATUS_ID_TBL;
1421 l_trx_business_category_tbl ZX_EXTRACT_PKG.TRX_BUSINESS_CATEGORY_TBL;
1422 l_document_sub_type_tbl ZX_EXTRACT_PKG.DOCUMENT_SUB_TYPE_TBL;
1423 l_billing_tp_id_tbl ZX_EXTRACT_PKG.BILLING_TRADING_PARTNER_ID_TBL;
1424 l_adjusted_doc_number_tbl ZX_EXTRACT_PKG.ADJUSTED_DOC_NUMBER_TBL;
1425 l_taxable_amt_tbl ZX_EXTRACT_PKG.TAXABLE_AMT_TBL;
1426 l_currency_conversion_rate_tbl ZX_EXTRACT_PKG.CURRENCY_CONVERSION_RATE_TBL;
1427 l_precision_tbl ZX_EXTRACT_PKG.PRECISION_TBL;
1428 l_supplier_exchange_rate_tbl ZX_EXTRACT_PKG.SUPPLIER_EXCHANGE_RATE_TBL;
1429 l_tax_amt_tbl ZX_EXTRACT_PKG.TAX_AMT_TBL;
1430 l_applied_to_trx_number_tbl ZX_EXTRACT_PKG.APPLIED_TO_TRX_NUMBER_TBL;
1431
1432 l_location_code_tbl ATTRIBUTE1_TBL;
1433 l_address_line_1_tbl ATTRIBUTE1_TBL;
1434 l_address_line_2_tbl ATTRIBUTE1_TBL;
1435 l_address_line_3_tbl ATTRIBUTE1_TBL;
1436 l_city_tbl ATTRIBUTE1_TBL;
1437 l_region_1_tbl ATTRIBUTE1_TBL;
1438 l_region_2_tbl ATTRIBUTE1_TBL;
1439 l_postal_code_tbl ATTRIBUTE1_TBL;
1440 l_country_code_tbl ATTRIBUTE1_TBL;
1441 l_loc_tax_reg_num_tbl ATTRIBUTE1_TBL;
1442 l_taxable_person_tbl ATTRIBUTE1_TBL;
1443 l_ind_sub_classif_tbl ATTRIBUTE1_TBL;
1444 l_ind_classif_tbl ATTRIBUTE1_TBL;
1445 k number;
1446
1447
1448 BEGIN
1449
1450 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1451
1452 IF (g_level_procedure >= g_current_runtime_level ) THEN
1453 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP.BEGIN',
1454 'ZX_JA_EXTRACT_PKG.POPULATE_JA_AP(+)');
1455 END IF;
1456
1457 SELECT detail_tax_line_id,
1458 trx_id,
1459 trx_line_id,
1460 tax_status_id,
1461 tax_status_code,
1462 trx_business_category,
1463 document_sub_type,
1464 billing_trading_partner_id,
1465 adjusted_doc_number,
1466 taxable_amt,
1467 currency_conversion_rate,
1468 precision,
1469 supplier_exchange_rate,
1470 tax_amt,
1471 applied_to_trx_number,
1472 trx_date
1473 BULK COLLECT INTO l_detail_tax_line_id_tbl,
1474 l_trx_id_tbl,
1475 l_trx_line_id_tbl, --Bug#5673935
1476 l_tax_status_id_tbl,
1477 l_tax_status_code_tbl,
1478 l_trx_business_category_tbl,
1479 l_document_sub_type_tbl,
1480 l_billing_tp_id_tbl,
1481 l_adjusted_doc_number_tbl,
1482 l_taxable_amt_tbl,
1483 l_currency_conversion_rate_tbl,
1484 l_precision_tbl,
1485 l_supplier_exchange_rate_tbl,
1486 l_tax_amt_tbl,
1487 l_applied_to_trx_number_tbl,
1488 l_trx_date_tbl
1489 FROM zx_rep_trx_detail_t itf1
1490 WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1491
1492 IF ( g_level_statement>= g_current_runtime_level ) THEN
1493 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1494 'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
1495 END IF;
1496
1497 P_LOOKUP_CODE:= NULL;
1498
1499 if P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = ('ZXTWPVAT') and l_detail_tax_line_id_tbl.count <> 0 then
1500 IF (g_level_statement >= g_current_runtime_level ) THEN
1501 FND_LOG.STRING(g_level_statement,
1502 'ZX.TRL.ja_tax_extract.populate_ja_ap',
1503 'ZXTWPVAT');
1504 END IF;
1505 /****** Populate the missing columns ********/
1506 FOR i in 1 .. nvl(l_detail_tax_line_id_tbl.count,0) LOOP
1507
1508 P_LOOKUP_TYPE:= 'JATW_GOVERNMENT_TAX_TYPE';
1509 P_LOOKUP_CODE:= l_tax_status_code_tbl(i); --Bug 5438409
1510 -- P_LOOKUP_CODE:=l_tax_status_id_tbl(i);
1511
1512 IF (g_level_statement >= g_current_runtime_level ) THEN
1513 FND_LOG.STRING(g_level_statement,
1514 'ZX.TRL.ja_tax_extract.populate_ja_ap',
1515 'P_LOOKUP_CODE:'||P_LOOKUP_CODE||'-'||to_char(l_tax_status_id_tbl(i)));
1516 END IF;
1517
1518 X_GOVERNMENT_TAX_TYPE_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE, l_trx_date_tbl(i));
1519
1520 P_LOOKUP_TYPE:= 'JATW_DEDUCTIBLE_TYPE';
1521 P_LOOKUP_CODE := l_trx_business_category_tbl(i); --BUG 5517615
1522 -- select translate(l_trx_business_category_tbl(i), '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') into P_LOOKUP_CODE from dual;
1523 X_DEDUCTIBLE_TYPE_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE, l_trx_date_tbl(i));
1524
1525 END LOOP;
1526
1527 /****** Bulk Insert into Ext Table *******/
1528 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1529
1530 INSERT INTO ZX_REP_TRX_JX_EXT_T
1531 (detail_tax_line_ext_id,
1532 detail_tax_line_id,
1533 tax_status_mng, -- government tax type meaning
1534 trx_business_category_mng, -- deductible code meaning
1535 created_by,
1536 creation_date,
1537 last_updated_by,
1538 last_update_date,
1539 last_update_login,
1540 request_id)
1541 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1542 l_detail_tax_line_id_tbl(i),
1543 X_GOVERNMENT_TAX_TYPE_TBL(i),
1544 X_DEDUCTIBLE_TYPE_TBL(i),
1545 fnd_global.user_id,
1546 sysdate,
1547 fnd_global.user_id,
1548 sysdate,
1549 fnd_global.login_id,
1550 p_trl_global_variables_rec.request_id);
1551
1552 IF ( g_level_statement>= g_current_runtime_level ) THEN
1553 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1554 'After insertion into zx_rep_trx_jx_ext_t ');
1555 END IF;
1556 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWSPRF' and l_detail_tax_line_id_tbl.count <> 0 then
1557
1558 /****** Populate the missing columns ********/
1559 FOR i in 1 .. nvl(l_detail_tax_line_id_tbl.count,0) LOOP
1560 IF ( g_level_statement>= g_current_runtime_level ) THEN
1561 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1562 'Processing detail_tax_line_id : '||l_detail_tax_line_id_tbl(i)||' trx id : '||l_trx_id_tbl(i));
1563 END IF;
1564
1565 P_LOOKUP_TYPE:= 'JATW_GUI_TYPE';
1566 -- select substrb(l_document_sub_type_tbl(i), 10,2) into P_LOOKUP_CODE from dual;
1567 --Bug 5453957
1568 p_lookup_code := l_document_sub_type_tbl(i);
1569 X_GUI_TYPE_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE, l_trx_date_tbl(i));
1570
1571 IF ( g_level_statement>= g_current_runtime_level ) THEN
1572 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1573 'Debug 1');
1574 END IF;
1575
1576 P_LOOKUP_TYPE:= 'JATW_GOVERNMENT_TAX_TYPE';
1577 P_LOOKUP_CODE:= l_tax_status_code_tbl(i);
1578 X_GOVERNMENT_TAX_TYPE_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE, l_trx_date_tbl(i));
1579
1580 IF ( g_level_statement>= g_current_runtime_level ) THEN
1581 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1582 'Debug 2');
1583 END IF;
1584
1585 P_LOOKUP_TYPE:= 'JATW_DEDUCTIBLE_TYPE';
1586 --Bug 5453957
1587 p_lookup_code := l_trx_business_category_tbl(i);
1588 -- select translate(l_trx_business_category_tbl(i), '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') into P_LOOKUP_CODE from dual;
1589 X_DEDUCTIBLE_TYPE_TBL(i):= GET_LOOKUP_INFO(P_LOOKUP_TYPE, P_LOOKUP_CODE, l_trx_date_tbl(i));
1590
1591 IF ( g_level_statement>= g_current_runtime_level ) THEN
1592 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1593 'Debug 3');
1594 END IF;
1595
1596 END LOOP;
1597
1598 /****** Bulk Insert into Ext Table *******/
1599
1600 IF ( g_level_statement>= g_current_runtime_level ) THEN
1601 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1602 'Before into zx_rep_trx_jx_ext_t ap ');
1603 END IF;
1604
1605 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1606
1607 INSERT INTO ZX_REP_TRX_JX_EXT_T
1608 (detail_tax_line_ext_id,
1609 detail_tax_line_id,
1610 tax_status_mng, -- government tax type meaning
1611 trx_business_category_mng, -- deductible code meaning
1612 document_sub_type_mng, -- document subtype meaning
1613 created_by,
1614 creation_date,
1615 last_updated_by,
1616 last_update_date,
1617 last_update_login,
1618 request_id)
1619 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1620 l_detail_tax_line_id_tbl(i),
1621 X_GOVERNMENT_TAX_TYPE_TBL(i),
1622 X_DEDUCTIBLE_TYPE_TBL(i),
1623 X_GUI_TYPE_TBL(i),
1624 fnd_global.user_id,
1625 sysdate,
1626 fnd_global.user_id,
1627 sysdate,
1628 fnd_global.login_id,
1629 p_trl_global_variables_rec.request_id);
1630
1631 IF ( g_level_statement>= g_current_runtime_level ) THEN
1632 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1633 'After insertion into zx_rep_trx_jx_ext_t ');
1634 END IF;
1635 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = ('ZXTWPSPC') and l_detail_tax_line_id_tbl.count <> 0 then
1636
1637 /****** Populate the missing columns ********/
1638
1639 FOR i in 1 .. nvl(l_detail_tax_line_id_tbl.count,0) LOOP
1640
1641 -- get print_date, document and document base amount
1642 P_VENDOR_ID:= l_billing_tp_id_tbl(i);
1643 -- P_INVOICE_NUM:= l_applied_to_trx_number_tbl(i); --replaced with below
1644 P_INVOICE_NUM:= l_adjusted_doc_number_tbl(i);
1645 GET_INVOICE_AMT(P_VENDOR_ID, P_INVOICE_NUM, X_INVOICE_AMOUNT_TBL(i), X_BASE_AMOUNT_TBL(i), X_PRINT_DATE_TBL(i));
1646
1647 -- update print date
1648 -- Print Date is null which means the first time this certificate is
1649 -- submitted.
1650 --
1651 -- Print Date is not null which means this certificate was submitted
1652 -- before. In this case, if reprint = 'Y', it will update print date.
1653 if ((X_PRINT_DATE_TBL(i) IS NULL)
1654 -- Need to verify or (P_TRL_GLOBAL_VARIABLES_REC.REPRINT = 'Y' and X_PRINT_DATE_TBL(i) is not null)
1655 ) then
1656 UPDATE_PRINT_DATE(l_trx_id_tbl(i));
1657 end if;
1658
1659 END LOOP;
1660
1661 /****** Bulk Insert into Ext Table *******/
1662 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1663
1664 INSERT INTO ZX_REP_TRX_JX_EXT_T
1665 (detail_tax_line_ext_id,
1666 detail_tax_line_id,
1667 gdf_ap_invoices_att4, -- print date
1668 numeric12, -- document amount
1669 numeric13, -- document base amount
1670 created_by,
1671 creation_date,
1672 last_updated_by,
1673 last_update_date,
1674 last_update_login,
1675 request_id)
1676 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1677 l_detail_tax_line_id_tbl(i),
1678 X_PRINT_DATE_TBL(i),
1679 X_INVOICE_AMOUNT_TBL(i),
1680 X_BASE_AMOUNT_TBL(i),
1681 fnd_global.user_id,
1682 sysdate,
1683 fnd_global.user_id,
1684 sysdate,
1685 fnd_global.login_id,
1686 p_trl_global_variables_rec.request_id);
1687
1688 IF ( g_level_statement>= g_current_runtime_level ) THEN
1689 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1690 'After insertion into zx_rep_trx_jx_ext_t ');
1691 END IF;
1692 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWPPRD' and l_detail_tax_line_id_tbl.count <> 0 then
1693
1694 /****** Populate the missing columns ********/
1695
1696 FOR i in 1 .. nvl(l_detail_tax_line_id_tbl.count,0) LOOP
1697
1698 -- get print_date, document and document base amount
1699 P_VENDOR_ID:= l_billing_tp_id_tbl(i);
1700 -- P_INVOICE_NUM:= l_applied_to_trx_number_tbl(i); --replaced with below
1701 P_INVOICE_NUM:= l_adjusted_doc_number_tbl(i);
1702 GET_INVOICE_AMT(P_VENDOR_ID, P_INVOICE_NUM, X_INVOICE_AMOUNT_TBL(i), X_BASE_AMOUNT_TBL(i), X_PRINT_DATE_TBL(i));
1703 END LOOP;
1704
1705
1706 /****** Bulk Insert into Ext Table *******/
1707 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1708
1709 INSERT INTO ZX_REP_TRX_JX_EXT_T
1710 (detail_tax_line_ext_id,
1711 detail_tax_line_id,
1712 numeric12, -- document amount
1713 numeric13, -- document base amount
1714 created_by,
1715 creation_date,
1716 last_updated_by,
1717 last_update_date,
1718 last_update_login,
1719 request_id)
1720 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1721 l_detail_tax_line_id_tbl(i),
1722 X_INVOICE_AMOUNT_TBL(i),
1723 X_BASE_AMOUNT_TBL(i),
1724 fnd_global.user_id,
1725 sysdate,
1726 fnd_global.user_id,
1727 sysdate,
1728 fnd_global.login_id,
1729 p_trl_global_variables_rec.request_id);
1730
1731 IF ( g_level_statement>= g_current_runtime_level ) THEN
1732 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1733 'After insertion into zx_rep_trx_jx_ext_t ');
1734 END IF;
1735
1736 ELSIF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXTWSEDI' AND l_detail_tax_line_id_tbl.count <> 0 then
1737
1738 IF ( g_level_statement>= g_current_runtime_level ) THEN
1739 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JE_AP',
1740 'P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME : '||P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME);
1741 END IF;
1742
1743 INSERT INTO zx_rep_trx_jx_ext_t (
1744 request_id,
1745 created_by,
1746 creation_date,
1747 last_updated_by,
1748 last_update_date,
1749 last_update_login,
1750 detail_tax_line_ext_id,
1751 detail_tax_line_id,
1752 document_sub_type_mng,
1753 numeric1,
1754 numeric2,
1755 numeric3,
1756 numeric4
1757 )
1758 SELECT
1759 p_trl_global_variables_rec.request_id,
1760 fnd_global.user_id,
1761 sysdate,
1762 fnd_global.user_id,
1763 sysdate,
1764 fnd_global.login_id,
1765 zx_rep_trx_jx_ext_t_s.NEXTVAL,
1766 detail_tax_line_id,
1767 document_sub_type,
1768 CASE WHEN tax_line_change= 1 OR tax_recoverable_flag = 'N' THEN taxable_amt
1769 ELSE 0
1770 END,
1771 CASE WHEN tax_line_change= 1 OR tax_recoverable_flag = 'N' THEN taxable_amt_funcl_curr
1772 ELSE 0
1773 END,
1774 tax_amt,
1775 tax_amt_funcl_curr
1776 FROM (
1777 SELECT itf.detail_tax_line_id,
1778 itf.document_sub_type,
1779 itf.tax_amt,
1780 itf.tax_amt_funcl_curr,
1781 itf.taxable_amt,
1782 itf.taxable_amt_funcl_curr,
1783 itf.tax_recoverable_flag,
1784 RANK() OVER (PARTITION BY itf.trx_id,
1785 itf.trx_line_id
1786 ORDER BY NVL(itf.tax_recoverable_flag,'N'),
1787 itf.actg_source_id,
1788 itf.detail_tax_line_id
1789 ) AS tax_line_change
1790 FROM zx_rep_trx_detail_t itf
1791 WHERE itf.request_id = p_trl_global_variables_rec.request_id
1792 AND itf.application_id = 200);
1793 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'ZXSGAGAL' and l_detail_tax_line_id_tbl.count <> 0 then
1794
1795 /****** Populate the missing columns ********/
1796
1797 FOR i in 1 .. nvl(l_detail_tax_line_id_tbl.count,0) LOOP
1798 k:= to_number(to_char(l_trx_id_tbl(i))||to_char(l_trx_line_id_tbl(i)));
1799
1800 IF ( g_level_statement>= g_current_runtime_level ) THEN
1801 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1802 'l_trx_id_tbl(i) : '||l_trx_id_tbl(i));
1803 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1804 'l_trx_line_id_tbl(i) : '||l_trx_line_id_tbl(i));
1805 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1806 'i : '||i||' k : '||k);
1807 END IF;
1808
1809 IF L_INHOUSE_INV_AMT_TBL.EXISTS(k) THEN
1810 null;
1811 ELSE
1812 L_INHOUSE_INV_AMT_TBL(k) := null;
1813 END IF;
1814
1815 IF ( g_level_statement>= g_current_runtime_level ) THEN
1816 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1817 'L_INHOUSE_INV_AMT_TBL(k) : '||L_INHOUSE_INV_AMT_TBL(k));
1818 END IF;
1819
1820 IF L_INHOUSE_INV_AMT_TBL(k) is NULL THEN
1821
1822 -- get inhouse and supplier invoice rated taxable/tax amount
1823 L_INHOUSE_INV_AMT_TBL(k):= round(l_taxable_amt_tbl(i) *
1824 to_number(nvl(l_currency_conversion_rate_tbl(i),1)), l_precision_tbl(i));
1825 L_SUPPLIER_INV_AMT_TBL(k):= round(l_taxable_amt_tbl(i) *
1826 to_number(nvl(l_supplier_exchange_rate_tbl(i),1)), l_precision_tbl(i));
1827 L_TAXABLE_GAINLOSS_TBL(k):= L_INHOUSE_INV_AMT_TBL(k) - L_SUPPLIER_INV_AMT_TBL(k);
1828 ELSE
1829 L_INHOUSE_INV_AMT_TBL(k):= 0;
1830 L_SUPPLIER_INV_AMT_TBL(k):= 0;
1831 L_TAXABLE_GAINLOSS_TBL(k):= 0;
1832 END IF;
1833 X_INHOUSE_INV_AMT_TBL(i):= L_INHOUSE_INV_AMT_TBL(k);
1834 X_SUPPLIER_INV_AMT_TBL(i):=L_SUPPLIER_INV_AMT_TBL(k);
1835 X_TAXABLE_GAINLOSS_TBL(i):= L_TAXABLE_GAINLOSS_TBL(k);
1836
1837 IF ( g_level_statement>= g_current_runtime_level ) THEN
1838 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1839 'X_INHOUSE_INV_AMT_TBL(i) : '||X_INHOUSE_INV_AMT_TBL(i));
1840 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1841 'X_SUPPLIER_INV_AMT_TBL(i) : '||X_SUPPLIER_INV_AMT_TBL(i));
1842 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1843 'X_TAXABLE_GAINLOSS_TBL(i) : '||X_TAXABLE_GAINLOSS_TBL(i));
1844 END IF;
1845
1846 X_INHOUSE_TAX_AMT_TBL(i):= round(l_tax_amt_tbl(i) *
1847 to_number(nvl(l_currency_conversion_rate_tbl(i),1)), l_precision_tbl(i));
1848 X_SUPPLIER_TAX_AMT_TBL(i):= round(l_tax_amt_tbl(i) *
1849 to_number(nvl(l_supplier_exchange_rate_tbl(i),1)), l_precision_tbl(i));
1850 X_TAX_GAINLOSS_TBL(i):= X_INHOUSE_TAX_AMT_TBL(i) - X_SUPPLIER_TAX_AMT_TBL(i);
1851
1852 IF ( g_level_statement>= g_current_runtime_level ) THEN
1853 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1854 'X_INHOUSE_TAX_AMT_TBL(i) : '||X_INHOUSE_TAX_AMT_TBL(i));
1855 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1856 'X_SUPPLIER_TAX_AMT_TBL(i) : '||X_SUPPLIER_TAX_AMT_TBL(i));
1857 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1858 'X_TAX_GAINLOSS_TBL(i) : '||X_TAX_GAINLOSS_TBL(i));
1859 END IF;
1860
1861 /*
1862 -- get inhouse and supplier invoice rated taxable/tax amount
1863 X_INHOUSE_INV_AMT_TBL(i):= round(l_taxable_amt_tbl(i) *
1864 to_number(nvl(l_currency_conversion_rate_tbl(i),1)), l_precision_tbl(i));
1865 X_SUPPLIER_INV_AMT_TBL(i):= round(l_taxable_amt_tbl(i) *
1866 to_number(nvl(l_supplier_exchange_rate_tbl(i),1)), l_precision_tbl(i));
1867 X_INHOUSE_TAX_AMT_TBL(i):= round(l_tax_amt_tbl(i) *
1868 to_number(nvl(l_currency_conversion_rate_tbl(i),1)), l_precision_tbl(i));
1869 X_SUPPLIER_TAX_AMT_TBL(i):= round(l_tax_amt_tbl(i) *
1870 to_number(nvl(l_supplier_exchange_rate_tbl(i),1)), l_precision_tbl(i));
1871 X_TAXABLE_GAINLOSS_TBL(i):= X_INHOUSE_INV_AMT_TBL(i) - X_SUPPLIER_INV_AMT_TBL(i);
1872 X_TAX_GAINLOSS_TBL(i):= X_INHOUSE_TAX_AMT_TBL(i) - X_SUPPLIER_TAX_AMT_TBL(i);
1873 */
1874 END LOOP;
1875
1876
1877 /****** Bulk Insert into Ext Table *******/
1878 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1879
1880 INSERT INTO ZX_REP_TRX_JX_EXT_T
1881 (detail_tax_line_ext_id,
1882 detail_tax_line_id,
1883 numeric1, -- inhouse invoice amt
1884 numeric2, -- supplier invoice amt
1885 numeric3, -- inhouse tax amt
1886 numeric4, -- supplier tax amt
1887 numeric5, -- taxable gainloss
1888 numeric6, -- gainloss
1889 created_by,
1890 creation_date,
1891 last_updated_by,
1892 last_update_date,
1893 last_update_login,
1894 request_id)
1895 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1896 l_detail_tax_line_id_tbl(i),
1897 X_INHOUSE_INV_AMT_TBL(i),
1898 X_SUPPLIER_INV_AMT_TBL(i),
1899 X_INHOUSE_TAX_AMT_TBL(i),
1900 X_SUPPLIER_TAX_AMT_TBL(i),
1901 X_TAXABLE_GAINLOSS_TBL(i),
1902 X_TAX_GAINLOSS_TBL(i),
1903 fnd_global.user_id,
1904 sysdate,
1905 fnd_global.user_id,
1906 sysdate,
1907 fnd_global.login_id,
1908 p_trl_global_variables_rec.request_id);
1909
1910 IF ( g_level_statement>= g_current_runtime_level ) THEN
1911 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
1912 'After insertion into zx_rep_trx_jx_ext_t ');
1913 END IF;
1914
1915
1916 elsif P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'RXZXKVAT' THEN
1917
1918 SELECT loc.location_code,
1919 loc.ADDRESS_LINE_1,
1920 loc.ADDRESS_LINE_2,
1921 loc.ADDRESS_LINE_3,
1922 loc.TOWN_OR_CITY ,
1923 loc.REGION_1,
1924 loc.REGION_2,
1925 loc.POSTAL_CODE,
1926 loc.COUNTRY,
1927 loc.GLOBAL_ATTRIBUTE1,
1928 loc.GLOBAL_ATTRIBUTE4,
1929 -- loc.GLOBAL_ATTRIBUTE5,
1930 -- loc.GLOBAL_ATTRIBUTE6,
1931 -- loc.GLOBAL_ATTRIBUTE11,
1932 dtl.detail_tax_line_id
1933 BULK COLLECT INTO l_location_code_tbl,
1934 l_address_line_1_tbl,
1935 l_address_line_2_tbl,
1936 l_address_line_3_tbl,
1937 l_city_tbl,
1938 l_region_1_tbl,
1939 l_region_2_tbl,
1940 l_postal_code_tbl,
1941 l_country_code_tbl,
1942 l_loc_tax_reg_num_tbl,
1943 l_taxable_person_tbl,
1944 -- l_ind_sub_classif_tbl,
1945 -- l_ind_classif_tbl,
1946 l_detail_tax_line_id_tbl
1947 FROM zx_reporting_types_b rep_type,
1948 zx_report_codes_assoc rep_ass,
1949 hr_locations loc,
1950 zx_rep_trx_detail_t dtl
1951 WHERE rep_type.reporting_type_code = 'KR_BUSINESS_LOCATIONS'
1952 AND rep_ass.reporting_type_id = rep_type.reporting_type_id
1953 AND rep_ass.entity_code = 'ZX_RATES'
1954 AND rep_ass.entity_id = dtl.tax_rate_id
1955 AND dtl.tax_regime_code = rep_type.tax_regime_code
1956 AND rep_ass.reporting_code_char_value = loc.location_code
1957 AND loc.global_attribute_category = 'JA.KR.PERWSLOC.WITHHOLDING'
1958 AND dtl.application_id = 200
1959 AND dtl.request_id = p_trl_global_variables_rec.request_id ;
1960
1961 IF ( g_level_statement>= g_current_runtime_level ) THEN
1962 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1963 'Reporting Types Query Count : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
1964
1965 END IF;
1966
1967
1968 SELECT global_attribute4,
1969 global_attribute5
1970 BULK COLLECT INTO
1971 l_ind_classif_tbl,
1972 l_ind_sub_classif_tbl
1973 FROM ap_supplier_sites_all sup_site,
1974 zx_rep_trx_detail_t dtl
1975 WHERE sup_site.vendor_site_id = NVL(dtl.shipping_tp_address_id, dtl.billing_tp_address_id)
1976 AND dtl.application_id = 200
1977 AND dtl.request_id = p_trl_global_variables_rec.request_id ;
1978
1979 IF ( g_level_statement>= g_current_runtime_level ) THEN
1980 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.populate_ja_ar',
1981 'AP Industry Class Query Count: '||to_char(nvl(l_ind_classif_tbl.count,0)) );
1982 END IF;
1983
1984
1985 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1986
1987 INSERT INTO ZX_REP_TRX_JX_EXT_T
1988 (detail_tax_line_ext_id,
1989 detail_tax_line_id,
1990 attribute1,
1991 attribute2,
1992 attribute3,
1993 attribute4,
1994 attribute5,
1995 attribute6,
1996 attribute7,
1997 attribute8,
1998 attribute9,
1999 attribute10,
2000 attribute11,
2001 attribute12,
2002 attribute13,
2003 created_by,
2004 creation_date,
2005 last_updated_by,
2006 last_update_date,
2007 last_update_login,
2008 request_id)
2009 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
2010 l_detail_tax_line_id_tbl(i),
2011 l_location_code_tbl(i),
2012 l_address_line_1_tbl(i),
2013 l_address_line_2_tbl(i),
2014 l_address_line_3_tbl(i),
2015 l_city_tbl(i),
2016 l_region_1_tbl(i),
2017 l_region_2_tbl(i),
2018 l_postal_code_tbl(i),
2019 l_country_code_tbl(i),
2020 l_loc_tax_reg_num_tbl(i),
2021 l_taxable_person_tbl(i),
2022 l_ind_sub_classif_tbl(i),
2023 l_ind_classif_tbl(i),
2024 fnd_global.user_id,
2025 sysdate,
2026 fnd_global.user_id,
2027 sysdate,
2028 fnd_global.login_id,
2029 p_trl_global_variables_rec.request_id);
2030
2031 end if;
2032
2033 IF (g_level_procedure >= g_current_runtime_level ) THEN
2034 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP.END',
2035 'ZX_JA_EXTRACT_PKG.POPULATE_JA_AP(-)');
2036 END IF;
2037
2038 EXCEPTION
2039 WHEN OTHERS THEN
2040
2041 IF ( g_level_statement>= g_current_runtime_level ) THEN
2042 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
2043 'Error Message for report : '||substrb(SQLERRM,1,120) );
2044 END IF;
2045 END populate_ja_ap;
2046
2047
2048
2049 /*===========================================================================+
2050 | FUNCTION |
2051 | GET_LOOKUP_INFO |
2052 | Type : Private |
2053 | Pre-req : None |
2054 | Function : |
2055 | This plug-in is used to fetch lookup meaning for |
2056 | a given lookup type from JA_LOOKUPS |
2057 | |
2058 | Called from ZX_JA_EXTRACT_PKG.POPULATE |
2059 | |
2060 | Parameters : |
2061 | IN : |
2062 | p_lookup_type IN VARCHAR2 Required |
2063 | p_lookup_code IN VARCHAR2 |
2064 | |
2065 | MODIFICATION HISTORY |
2066 | 27-Oct-03 Asako Takahashi created |
2067 | |
2068 | |
2069 +===========================================================================*/
2070
2071 FUNCTION GET_LOOKUP_INFO
2072 (
2073 P_LOOKUP_TYPE IN VARCHAR2,
2074 P_LOOKUP_CODE IN VARCHAR2,
2075 P_TRX_DATE IN DATE
2076 )
2077 return varchar2 IS
2078
2079 x_lookup_meaning VARCHAR2(80);
2080
2081 BEGIN
2082 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2083
2084 IF (g_level_statement >= g_current_runtime_level ) THEN
2085 FND_LOG.STRING(g_level_statement,
2086 'ZX.TRL.ja_tax_extract.get_lookup_info',
2087 'x_lookup_meaning:'||x_lookup_meaning);
2088 END IF;
2089
2090 IF (g_level_procedure >= g_current_runtime_level ) THEN
2091 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2092 'GET_LOOKUP_INFO(+)');
2093 END IF;
2094
2095 IF ( g_level_statement>= g_current_runtime_level ) THEN
2096 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
2097 ' P_LOOKUP_TYPE : '||P_LOOKUP_TYPE );
2098 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
2099 ' P_LOOKUP_CODE : '||P_LOOKUP_CODE );
2100 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
2101 ' LANG : '||userenv('LANG') );
2102 END IF;
2103
2104 IF P_LOOKUP_TYPE = 'JATW_GUI_TYPE' then
2105 BEGIN
2106 SELECT CLASSIFICATION_NAME
2107 INTO x_lookup_meaning
2108 FROM ZX_FC_CODES_DENORM_B
2109 WHERE CLASSIFICATION_TYPE_CODE = 'DOCUMENT_SUBTYPE' and
2110 -- CLASSIFICATION_CODE_LEVEL = 2 and --Bug5453806
2111 CONCAT_CLASSIF_CODE = P_LOOKUP_CODE
2112 AND LANGUAGE = userenv('LANG') --Bug 5453957
2113 and p_trx_date >= effective_from and p_trx_date <= nvl(effective_to, p_trx_date);
2114 EXCEPTION
2115 WHEN no_data_found THEN
2116 x_lookup_meaning := NULL;
2117 END ;
2118
2119 ELSIF P_LOOKUP_TYPE = 'JATW_GOVERNMENT_TAX_TYPE' then
2120 BEGIN
2121 SELECT TAX_STATUS_NAME
2122 INTO x_lookup_meaning
2123 FROM ZX_STATUS_TL stl, zx_status_b sb
2124 WHERE sb.tax_status_code = P_LOOKUP_CODE
2125 AND sb.tax_status_id = stl.tax_status_id
2126 AND LANGUAGE = userenv('LANG')
2127 and p_trx_date >= effective_from and p_trx_date <= nvl(effective_to, p_trx_date);
2128 EXCEPTION
2129 WHEN no_data_found THEN
2130 x_lookup_meaning := NULL;
2131 END;
2132
2133
2134 ELSIF P_LOOKUP_TYPE = 'JATW_DEDUCTIBLE_TYPE' then
2135 BEGIN
2136 SELECT CLASSIFICATION_NAME
2137 INTO x_lookup_meaning
2138 FROM ZX_FC_CODES_DENORM_B
2139 WHERE CLASSIFICATION_TYPE_CODE = 'TRX_BUSINESS_CATEGORY' and
2140 -- CLASSIFICATION_CODE_LEVEL = 3 and --Bug5453806
2141 CONCAT_CLASSIF_CODE = P_LOOKUP_CODE
2142 AND LANGUAGE = userenv('LANG') --Bug 5453957
2143 and p_trx_date >= effective_from and p_trx_date <= nvl(effective_to, p_trx_date);
2144 EXCEPTION
2145 WHEN no_data_found THEN
2146 x_lookup_meaning := NULL;
2147 END;
2148 ELSE
2149 BEGIN
2150 SELECT JA.MEANING
2151 INTO X_LOOKUP_MEANING
2152 FROM JA_LOOKUPS JA
2153 WHERE JA.LOOKUP_TYPE = P_LOOKUP_TYPE and
2154 JA.LOOKUP_CODE = P_LOOKUP_CODE
2155 and p_trx_date >= start_date_active and p_trx_date <= nvl(end_date_active, p_trx_date);
2156 EXCEPTION
2157 WHEN no_data_found THEN
2158 x_lookup_meaning := NULL;
2159 END;
2160 END IF;
2161
2162
2163 IF (g_level_statement >= g_current_runtime_level ) THEN
2164 FND_LOG.STRING(g_level_statement,
2165 'ZX.TRL.ja_tax_extract.get_lookup_info',
2166 'x_lookup_meaning:'||x_lookup_meaning);
2167 END IF;
2168
2169 IF (g_level_procedure >= g_current_runtime_level ) THEN
2170 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2171 'GET_LOOKUP_INFO(-)');
2172 END IF;
2173
2174 return (X_LOOKUP_MEANING);
2175
2176 EXCEPTION
2177
2178 WHEN NO_DATA_FOUND THEN
2179 return (NULL);
2180
2181 WHEN OTHERS THEN
2182 IF ( g_level_statement>= g_current_runtime_level ) THEN
2183 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2184 'Error Message : '||substrb(SQLERRM,1,120) );
2185 END IF;
2186 return (NULL);
2187
2188
2189 END get_lookup_info;
2190
2191
2192 /*===========================================================================+
2193 | PROCEDURE |
2194 | GET_INVOICE_AMT |
2195 | Type : Private |
2196 | Pre-req : None |
2197 | Function : |
2198 | This plug-in is used to fetch invoce_amount and base amount |
2199 | from AP_INVOICES table |
2200 | |
2201 | Called from ZX_JA_EXTRACT_PKG.POPULATE |
2202 | |
2203 | Parameters : |
2204 | IN : |
2205 | p_vendor_id IN NUMBER Required |
2206 | p_invoice_num IN VARCHAR2 |
2207 | |
2208 | MODIFICATION HISTORY |
2209 | 27-Oct-03 Asako Takahashi created |
2210 | |
2211 | |
2212 +===========================================================================*/
2213
2214 PROCEDURE GET_INVOICE_AMT
2215 (
2216 P_VENDOR_ID IN NUMBER,
2217 P_INVOICE_NUM IN VARCHAR2,
2218 X_INVOICE_AMT OUT NOCOPY ZX_REP_TRX_JX_EXT_T.NUMERIC12%TYPE,
2219 X_BASE_AMT OUT NOCOPY ZX_REP_TRX_JX_EXT_T.NUMERIC13%TYPE,
2220 X_PRINT_DATE OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_AP_INVOICES_ATT4%TYPE
2221 )
2222
2223 IS
2224 BEGIN
2225
2226 IF (g_level_procedure >= g_current_runtime_level ) THEN
2227 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2228 'GET_INVOICE_AMT(+)');
2229 END IF;
2230
2231 IF ( g_level_statement>= g_current_runtime_level ) THEN
2232 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
2233 ' P_VENDOR_ID : '||P_VENDOR_ID );
2234 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
2235 ' P_INVOICE_NUM : '||P_INVOICE_NUM );
2236 END IF;
2237
2238 SELECT AI.GLOBAL_ATTRIBUTE4, AI.INVOICE_AMOUNT, AI.BASE_AMOUNT
2239 INTO X_PRINT_DATE, X_INVOICE_AMT, X_BASE_AMT
2240 FROM AP_INVOICES AI
2241 WHERE AI.VENDOR_ID = P_VENDOR_ID and
2242 AI.INVOICE_NUM = P_INVOICE_NUM;
2243
2244 IF ( g_level_statement>= g_current_runtime_level ) THEN
2245 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
2246 ' X_INVOICE_AMT : '||X_INVOICE_AMT );
2247 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
2248 ' X_BASE_AMT : '||X_BASE_AMT );
2249 END IF;
2250
2251 IF (g_level_procedure >= g_current_runtime_level ) THEN
2252 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2253 'GET_INVOICE_AMT(-)');
2254 END IF;
2255
2256
2257 EXCEPTION
2258
2259 WHEN NO_DATA_FOUND THEN
2260 IF ( g_level_statement>= g_current_runtime_level ) THEN
2261 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2262 'Error Message : '||substrb(SQLERRM,1,120) );
2263 END IF;
2264
2265 END get_invoice_amt;
2266
2267
2268 /*===========================================================================+
2269 | FUNCTION |
2270 | GET_GUI_SOURCE
2271 | Type : Private |
2272 | Pre-req : None |
2273 | Function : |
2274 | This plug-in is used to fetch the following values for ZXTWRUIL |
2275 | reference_transaction_source |
2276 | initial trx number |
2277 | invoice word |
2278 | final trx number |
2279 | |
2280 | Called from ZX_JA_EXTRACT_PKG.POPULATE |
2281 | |
2282 | Parameters : |
2283 | IN : |
2284 | p_trx_source_name IN VARCHAR2 Required |
2285 | |
2286 | MODIFICATION HISTORY |
2287 | 27-Oct-03 Asako Takahashi created |
2288 | |
2289 | |
2290 +===========================================================================*/
2291
2292 PROCEDURE GET_GUI_SOURCE
2293 (
2294 P_TRX_SOURCE_NAME IN VARCHAR2,
2295 X_GDF_RA_BATCH_SOURCES_ATT1 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT1%TYPE, -- reference transaction source
2296 X_GDF_RA_BATCH_SOURCES_ATT2 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT2%TYPE, -- initial trx num
2297 X_GDF_RA_BATCH_SOURCES_ATT3 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT3%TYPE, -- invoice word
2298 X_GDF_RA_BATCH_SOURCES_ATT4 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_BATCH_SOURCES_ATT4%TYPE -- final trx num
2299
2300 )
2301 IS
2302
2303 BEGIN
2304
2305 IF (g_level_procedure >= g_current_runtime_level ) THEN
2306 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2307 'GET_GUI_SOURCE(+)');
2308 END IF;
2309
2310 IF ( g_level_statement>= g_current_runtime_level ) THEN
2311 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2312 ' P_TRX_SOURCE_NAME : '||P_TRX_SOURCE_NAME );
2313 END IF;
2314
2315 IF P_TRX_SOURCE_NAME is not null THEN
2316 SELECT decode(src.global_attribute1, NULL, src.batch_source_id,
2317 src.global_attribute1)
2318 INTO X_GDF_RA_BATCH_SOURCES_ATT1
2319 FROM ra_batch_sources src
2320 WHERE src.name = P_TRX_SOURCE_NAME;
2321
2322 IF ( g_level_statement>= g_current_runtime_level ) THEN
2323 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2324 ' X_GDF_RA_BATCH_SOURCES_ATT1 : '||X_GDF_RA_BATCH_SOURCES_ATT1 );
2325 END IF;
2326
2327 SELECT substr(global_attribute3,1,2),
2328 to_number(global_attribute2),
2329 to_number(global_attribute4)
2330 INTO X_GDF_RA_BATCH_SOURCES_ATT3,
2331 X_GDF_RA_BATCH_SOURCES_ATT2,
2332 X_GDF_RA_BATCH_SOURCES_ATT4
2333 FROM ra_batch_sources
2334 WHERE batch_source_id = X_GDF_RA_BATCH_SOURCES_ATT1;
2335
2336 END IF;
2337
2338 IF ( g_level_statement>= g_current_runtime_level ) THEN
2339 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2340 ' X_GDF_RA_BATCH_SOURCES_ATT2 : '||X_GDF_RA_BATCH_SOURCES_ATT2 );
2341 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2342 ' X_GDF_RA_BATCH_SOURCES_ATT3 : '||X_GDF_RA_BATCH_SOURCES_ATT3 );
2343 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2344 ' X_GDF_RA_BATCH_SOURCES_ATT4 : '||X_GDF_RA_BATCH_SOURCES_ATT4 );
2345 END IF;
2346
2347 IF (g_level_procedure >= g_current_runtime_level ) THEN
2348 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2349 'GET_GUI_SOURCE(-)');
2350 END IF;
2351 EXCEPTION
2352 WHEN NO_DATA_FOUND THEN
2353 IF ( g_level_statement>= g_current_runtime_level ) THEN
2354 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2355 'Error Message : '||substrb(SQLERRM,1,120) );
2356 END IF;
2357
2358 END get_gui_source;
2359
2360
2361 /*===========================================================================+
2362 | PROCEDURE |
2363 | UPDATE_PRINT_DATE |
2364 | Type : Private |
2365 | Pre-req : None |
2366 | Function : |
2367 | This plug-in updates print date stored in ap_invoices table for |
2368 | ZXTWPSPC |
2369 | |
2370 | Called from ZX_JA_EXTRACT_PKG.POPULATE |
2371 | |
2372 | Parameters : |
2373 | IN : |
2374 | p_trx_id IN NUMBER Required |
2375 | |
2376 | MODIFICATION HISTORY |
2377 | 27-Oct-03 Asako Takahashi created |
2378 | |
2379 | |
2380 +===========================================================================*/
2381
2382
2383 PROCEDURE UPDATE_PRINT_DATE
2384 (
2385 p_invoice_id number
2386 )
2387 IS
2388
2389 l_dummy varchar2(150);
2390
2391 CURSOR c_invoice (l_invoice_id NUMBER)
2392 IS SELECT ai.global_attribute4
2393 FROM ap_invoices ai
2394 WHERE ai.invoice_id = l_invoice_id
2395 FOR UPDATE NOWAIT;
2396
2397 BEGIN
2398 --
2399 -- Description:
2400 -- Update Print Date with Sysdate.
2401 --
2402 -- Called From:
2403 -- G_Vendor_NameGroupFilter
2404 --
2405 -- Note:
2406 -- Cannot use 'CURRENT OF' for 'SELECT FOR UPDATE NOWAIT'
2407 -- because of bug 219936.
2408 --
2409 IF (g_level_procedure >= g_current_runtime_level ) THEN
2410 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2411 'UPDATE_PRINT_DATE(+)');
2412 END IF;
2413
2414 IF ( g_level_statement>= g_current_runtime_level ) THEN
2415 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2416 ' p_invoice_id : '||p_invoice_id );
2417 END IF;
2418
2419 OPEN c_invoice(p_invoice_id);
2420 LOOP
2421 FETCH c_invoice INTO l_dummy;
2422 EXIT WHEN c_invoice%NOTFOUND;
2423
2424 UPDATE
2425 ap_invoices
2426 SET
2427 global_attribute4 = fnd_date.date_to_canonical(sysdate)
2428 WHERE
2429 invoice_id = p_invoice_id;
2430
2431 IF ( g_level_statement>= g_current_runtime_level ) THEN
2432 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
2433 'Update Count : '||to_char(SQL%ROWCOUNT) );
2434 END IF;
2435 END LOOP;
2436 CLOSE c_invoice;
2437
2438 IF (g_level_procedure >= g_current_runtime_level ) THEN
2439 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2440 'UPDATE_PRINT_DATE(-)');
2441 END IF;
2442
2443 EXCEPTION
2444 WHEN NO_DATA_FOUND THEN
2445 IF ( g_level_statement>= g_current_runtime_level ) THEN
2446 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2447 'Error Message : '||substrb(SQLERRM,1,120) );
2448 END IF;
2449
2450
2451 END update_print_date;
2452
2453
2454
2455 /*===========================================================================+
2456 | PROCEDURE |
2457 | GET_ORG_TRX_NUMBER |
2458 | Type : Private |
2459 | Pre-req : None |
2460 | Function : |
2461 | This plug-in is used to fetch the following values for ZXTWRUIL |
2462 | original transaction number |
2463 | |
2464 | Called from ZX_JA_EXTRACT_PKG.POPULATE |
2465 | |
2466 | Parameters : |
2467 | IN : |
2468 | p_request_id IN NUMBER Required |
2469 | |
2470 | MODIFICATION HISTORY |
2471 | 27-Oct-03 Asako Takahashi created |
2472 | |
2473 | |
2474 +===========================================================================*/
2475
2476
2477 PROCEDURE GET_ORG_TRX_NUMBER
2478 (
2479 P_TRX_SOURCE_ID IN NUMBER,
2480 P_TRX_ID IN NUMBER,
2481 X_ORG_TRX_NUMBER OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT9%TYPE -- org trx num
2482
2483 )
2484 IS
2485
2486 BEGIN
2487
2488 IF (g_level_procedure >= g_current_runtime_level ) THEN
2489 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2490 'GET_ORG_TRX_NUMBER(+)');
2491 END IF;
2492
2493 IF ( g_level_statement>= g_current_runtime_level ) THEN
2494 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2495 ' P_TRX_SOURCE_ID : '||P_TRX_SOURCE_ID );
2496 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2497 ' P_TRX_ID : '||P_TRX_ID );
2498 END IF;
2499
2500 IF P_TRX_SOURCE_ID is not null THEN
2501 SELECT rct_org.trx_number
2502 INTO X_ORG_TRX_NUMBER
2503 FROM ra_customer_trx_all rct,
2504 ra_customer_trx_all rct_org,
2505 ra_batcH_sources_all rbs
2506 WHERE rct_org.customer_Trx_id = rct.previous_customer_trx_id and
2507 rct.batch_source_id = rbs.batch_source_id and
2508 rbs.batch_source_id = P_TRX_SOURCE_ID and
2509 rct.customer_trx_id = p_trx_id;
2510
2511 END IF;
2512
2513 IF ( g_level_statement>= g_current_runtime_level ) THEN
2514 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2515 ' X_ORG_TRX_NUMBER : '||X_ORG_TRX_NUMBER );
2516 END IF;
2517
2518 IF (g_level_procedure >= g_current_runtime_level ) THEN
2519 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2520 'GET_ORG_TRX_NUMBER(-)');
2521 END IF;
2522
2523 EXCEPTION
2524 WHEN NO_DATA_FOUND THEN
2525 IF ( g_level_statement>= g_current_runtime_level ) THEN
2526 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2527 'Error Message : '||substrb(SQLERRM,1,120) );
2528 END IF;
2529 END get_org_trx_number;
2530
2531 /*===========================================================================+
2532 | PROCEDURE |
2533 | GET_EXPORT_INFO |
2534 | Type : Private |
2535 | Pre-req : None |
2536 | Function : |
2537 | This plug-in is used to fetch the following values for ZXTWRZTR |
2538 | export certificate number |
2539 | export name |
2540 | export method |
2541 | export type |
2542 | export date |
2543 | |
2544 | Called from ZX_JA_EXTRACT_PKG.POPULATE |
2545 | |
2546 | Parameters : |
2547 | IN : |
2548 | p_request_id IN NUMBER Required |
2549 | |
2550 | MODIFICATION HISTORY |
2551 | 27-Oct-03 Asako Takahashi created |
2552 | |
2553 | |
2554 +===========================================================================*/
2555
2556
2557 PROCEDURE GET_EXPORT_INFO
2558 (
2559 P_TRX_ID IN NUMBER,
2560 X_GDF_RA_CUST_TRX_ATT4 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT4%TYPE, -- export certificate number
2561 X_GDF_RA_CUST_TRX_ATT5 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT5%TYPE, -- export name
2562 X_GDF_RA_CUST_TRX_ATT6 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT6%TYPE, -- export method
2563 X_GDF_RA_CUST_TRX_ATT7 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT7%TYPE, -- export type
2564 X_GDF_RA_CUST_TRX_ATT8 OUT NOCOPY ZX_REP_TRX_JX_EXT_T.GDF_RA_CUST_TRX_ATT8%TYPE -- export date
2565 )
2566 IS
2567
2568 BEGIN
2569
2570 IF (g_level_procedure >= g_current_runtime_level ) THEN
2571 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2572 'GET_EXPORT_INFO(+)');
2573 END IF;
2574
2575 IF ( g_level_statement>= g_current_runtime_level ) THEN
2576 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2577 ' P_TRX_ID : '||P_TRX_ID );
2578 END IF;
2579
2580 IF P_TRX_ID is not null THEN
2581
2582 SELECT
2583 rct.global_attribute4,
2584 rct.global_attribute5,
2585 rct.global_attribute6,
2586 rct.global_attribute7,
2587 rct.global_attribute8
2588 INTO
2589 X_GDF_RA_CUST_TRX_ATT4,
2590 X_GDF_RA_CUST_TRX_ATT5,
2591 X_GDF_RA_CUST_TRX_ATT6,
2592 X_GDF_RA_CUST_TRX_ATT7,
2593 X_GDF_RA_CUST_TRX_ATT8
2594 FROM
2595 ra_customer_trx_all rct
2596 WHERE
2597 rct.customer_trx_id = P_TRX_ID and
2598 rct.global_attribute_category = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX';
2599
2600 IF ( g_level_statement>= g_current_runtime_level ) THEN
2601 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2602 ' X_GDF_RA_CUST_TRX_ATT4 : '||X_GDF_RA_CUST_TRX_ATT4 );
2603 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2604 ' X_GDF_RA_CUST_TRX_ATT5 : '||X_GDF_RA_CUST_TRX_ATT5 );
2605 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2606 ' X_GDF_RA_CUST_TRX_ATT6 : '||X_GDF_RA_CUST_TRX_ATT6 );
2607 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2608 ' X_GDF_RA_CUST_TRX_ATT7 : '||X_GDF_RA_CUST_TRX_ATT7 );
2609 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2610 ' X_GDF_RA_CUST_TRX_ATT8 : '||X_GDF_RA_CUST_TRX_ATT8 );
2611
2612 END IF;
2613
2614 END IF;
2615
2616 IF (g_level_procedure >= g_current_runtime_level ) THEN
2617 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2618 'GET_EXPORT_INFO(-)');
2619 END IF;
2620
2621 EXCEPTION
2622 WHEN NO_DATA_FOUND THEN
2623 IF ( g_level_statement>= g_current_runtime_level ) THEN
2624 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG',
2625 'Error Message : GET_EXPORT_INFO : '||substrb(SQLERRM,1,120) );
2626 END IF;
2627
2628
2629 END get_export_info;
2630
2631
2632 PROCEDURE bank_info
2633 (
2634 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
2635 )
2636 IS
2637
2638 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
2639
2640 CURSOR reg_bank_count_cur IS
2641 SELECT reg.bank_id,
2642 reg.bank_branch_id,
2643 reg.account_id,
2644 reg.bank_account_num,
2645 a.bank_name ,
2646 a.bank_branch_name
2647 FROM zx_party_tax_profile ptp,
2648 xle_etb_profiles xlep,
2649 zx_registrations reg ,
2650 ce_bank_branches_v a
2651 WHERE ptp.party_id = xlep.party_id
2652 AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
2653 AND xlep.legal_entity_id = P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id
2654 AND xlep.main_establishment_flag = 'Y'
2655 AND reg.bank_id = a.bank_party_id
2656 AND reg. bank_branch_id = a.branch_party_id
2657 AND reg.party_tax_profile_id = ptp.party_tax_profile_id;
2658
2659 l_bank_id number;
2660 l_bank_branch_id number;
2661 l_account_id number;
2662 l_bank_account_num VARCHAR2(30);
2663 l_bank_name ce_bank_branches_v.bank_name%type;
2664 l_branch_name ce_bank_branches_v.bank_branch_name%type;
2665
2666 l_count number;
2667 BEGIN
2668
2669 OPEN reg_bank_count_cur;
2670 FETCH reg_bank_count_cur into l_bank_id,
2671 l_bank_branch_id,
2672 l_account_id,
2673 l_bank_account_num,
2674 l_bank_name,
2675 l_branch_name;
2676 CLOSE reg_bank_count_cur;
2677
2678 SELECT dtl.detail_tax_line_id
2679 BULK COLLECT INTO l_detail_tax_line_id_tbl
2680 FROM zx_rep_trx_detail_t dtl
2681 WHERE dtl.request_id = P_TRL_GLOBAL_VARIABLES_REC.request_id;
2682
2683 IF (l_bank_id is NOT NULL
2684 OR l_bank_branch_id IS NOT NULL
2685 OR l_account_id IS NOT NULL) THEN
2686 BEGIN
2687 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
2688
2689 INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
2690 detail_tax_line_id,
2691 attribute28, --bank ID
2692 attribute29, --Bracnh ID
2693 attribute30, --Account ID
2694 created_by,
2695 creation_date,
2696 last_updated_by,
2697 last_update_date,
2698 last_update_login,
2699 request_id)
2700 VALUES ( zx_rep_trx_jx_ext_t_s.nextval,
2701 l_detail_tax_line_id_tbl(i),
2702 l_bank_name,
2703 l_branch_name,
2704 l_bank_account_num,
2705 fnd_global.user_id,
2706 sysdate,
2707 fnd_global.user_id,
2708 sysdate,
2709 fnd_global.login_id,
2710 p_trl_global_variables_rec.request_id);
2711 END;
2712
2713 ELSE
2714
2715 BEGIN
2716 INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
2717 detail_tax_line_id,
2718 attribute28,
2719 attribute29,
2720 attribute30,
2721 created_by,
2722 creation_date,
2723 last_updated_by,
2724 last_update_date,
2725 last_update_login,
2726 request_id)
2727 SELECT zx_rep_trx_jx_ext_t_s.nextval,
2728 dtl.detail_tax_line_id,
2729 loc.global_attribute5,
2730 loc.global_attribute6,
2731 loc.global_attribute7,
2732 fnd_global.user_id,
2733 sysdate,
2734 fnd_global.user_id,
2735 sysdate,
2736 fnd_global.login_id,
2737 p_trl_global_variables_rec.request_id
2738 FROM hr_all_organization_units ou,
2739 hr_organization_information oi,
2740 hr_locations loc,
2741 gl_sets_of_books sob,
2742 zx_rep_trx_detail_t dtl
2743 WHERE ou.organization_id = oi.organization_id
2744 AND ou.location_id = loc.location_id
2745 AND TO_NUMBER(oi.org_information1) = sob.set_of_books_id
2746 AND oi.org_information_context = 'Legal Entity Accounting'
2747 AND dtl.request_id = P_TRL_GLOBAL_VARIABLES_REC.request_id
2748 AND ou.organization_id = nvl(dtl.internal_organization_id,
2749 P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id);
2750
2751 END;
2752 END IF;
2753
2754 EXCEPTION
2755 WHEN NO_DATA_FOUND THEN
2756 NULL;
2757
2758 END bank_info;
2759
2760
2761 END ZX_JA_EXTRACT_PKG;