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