[Home] [Help]
PACKAGE BODY: APPS.JE_IT_INVOICES_ABOVE_THRESHOLD
Source
1 PACKAGE BODY JE_IT_INVOICES_ABOVE_THRESHOLD AS
2 /* $Header: jeitiatb.pls 120.18.12020000.2 2012/07/16 12:03:52 rshergil noship $ */
3
4 PROCEDURE purge_trx_data (p_vat_reporting_entity_id NUMBER,
5 p_year NUMBER,
6 p_type_of_upload NUMBER);
7 PROCEDURE Extract_AP_Trx_data;
8 PROCEDURE Extract_AR_Trx_data;
9 PROCEDURE Check_payment_mode_error;
10 PROCEDURE Check_missing_invoice;
11 PROCEDURE Check_partial_adj_inv;
12 PROCEDURE Update_Above_Threshold_Lines;
13 PROCEDURE Insert_Above_Threshold_Lines;
14 PROCEDURE debug_message(p_message_str VARCHAR2);
15 g_b2c_trx_date DATE;
16 PROCEDURE Extract_data (
17 errbuf OUT NOCOPY VARCHAR2,
18 retcode OUT NOCOPY NUMBER,
19 p_vat_reporting_entity_id IN NUMBER,
20 p_year IN NUMBER,
21 p_type_of_upload IN VARCHAR2,
22 p_type_of_upload_dummy IN VARCHAR2,
23 p_transmission_code IN NUMBER,
24 p_protocol_num IN NUMBER,
25 p_province_code IN VARCHAR2,
26 p_decl_inc_company IN VARCHAR2,
27 p_inv_acctg_status IN VARCHAR2,
28 p_end_date_for_cm_dm IN VARCHAR2,
29 p_b2c_threshold IN NUMBER,
30 p_b2b_threshold IN NUMBER,
31 p_b2c_trx_start_date IN VARCHAR2,
32 p_max_no_of_records IN NUMBER,
33 p_gen_eft_report IN VARCHAR2) IS
34
35 l_api_name VARCHAR2(100) := 'Extract_Data';
36 l_status_code VARCHAR2(20);
37 l_count NUMBER := 0;
38 l_eft_count NUMBER;
39 l_xml_layout BOOLEAN;
40 l_request_id NUMBER;
41 l_appln_name VARCHAR2(10);
42 l_con_cp_audit VARCHAR2(50);
43 l_con_cp_eft VARCHAR2(50);
44 l_con_cp_audit_desc VARCHAR2(100);
45 l_con_cp_eft_desc VARCHAR2(100);
46 l_year NUMBER(4);
47 l_prev_request_id NUMBER(15);
48 l_type_of_upload_mng VARCHAR2(100);
49 l_template_language VARCHAR2(2);
50 l_template_territory VARCHAR2(2);
51 l_schema_name VARCHAR2(10);
52
53 CURSOR cur_frozen(p_vat_reporting_entity_id NUMBER,p_year NUMBER) IS
54 SELECT freeze_indicator_flag
55 FROM je_it_setup_header_all
56 WHERE year_of_declaration = p_year
57 AND vat_reporting_entity_id = p_vat_reporting_entity_id;
58
59 CURSOR cur_report_mode(p_vat_reporting_entity_id NUMBER,p_year NUMBER,p_type_of_upload NUMBER) IS
60 SELECT report_mode
61 FROM je_it_above_thr_hdr_all
62 WHERE year_of_declaration = p_year
63 AND vat_reporting_entity_id = p_vat_reporting_entity_id
64 AND type_of_upload = p_type_of_upload;
65
66 BEGIN
67 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start PROCEDURE Extract_data');
68 FND_FILE.PUT_LINE(FND_FILE.LOG,'Parameters are :');
69 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_vat_reporting_entity_id :'||p_vat_reporting_entity_id);
70 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_year :'||p_year);
71
72 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_type_of_upload :'||p_type_of_upload);
73 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_transmission_code :'||p_transmission_code);
74 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_protocol_number :'||p_protocol_num);
75 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_province_code :'||p_province_code);
76 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_decl_inc_company :'||p_decl_inc_company);
77 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_inv_acctg_status :'||p_inv_acctg_status);
78 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_end_date_for_cm_dm :'||p_end_date_for_cm_dm);
79 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_b2b_threshold :'||p_b2b_threshold);
80 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_b2c_threshold :'||p_b2c_threshold);
81 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_b2c_trx_start_date :'||p_b2c_trx_start_date);
82 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_max_no_of_records :'||p_max_no_of_records);
83 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_gen_eft_report :'||p_gen_eft_report);
84
85 g_vat_reporting_entity_id := p_vat_reporting_entity_id;
86 g_year := p_year;
87 g_type_of_upload := p_type_of_upload;
88 g_transmission_code := p_transmission_code;
89 g_protocol_num := p_protocol_num;
90 g_province_code := p_province_code;
91 g_decl_inc_company := p_decl_inc_company;
92 g_inv_acctg_status := p_inv_acctg_status;
93 g_end_date_for_cm_dm := fnd_date.canonical_to_date(p_end_date_for_cm_dm);
94 g_b2b_threshold := p_b2b_threshold;
95 g_b2c_threshold := p_b2c_threshold;
96 g_b2c_trx_start_date := fnd_date.canonical_to_date(p_b2c_trx_start_date);
97 g_max_no_of_records := p_max_no_of_records;
98 g_gen_eft_report := p_gen_eft_report;
99
100 g_conc_request_id := NVL(fnd_global.CONC_REQUEST_ID,1);
101 g_created_by := NVL(fnd_global.USER_ID,1);
102 g_creation_date := sysdate;
103 g_last_updated_by := NVL(fnd_global.USER_ID,1);
104 g_last_update_date := sysdate;
105 g_last_update_login := 1;
106 g_debug_flag := NVL(fnd_profile.value('aflog_enabled'), 'n');
107 g_currency_code := 'EUR';
108 l_appln_name := 'JE';
109 l_con_cp_eft := 'JEITIATE_XMLP';
110 l_con_cp_eft_desc := 'Italian Invoices Above Threshold - Electronic File';
111 l_con_cp_audit := 'JEITIATA_XMLP';
112 l_con_cp_audit_desc := 'Italian Invoices Above Threshold - Audit Report';
113 l_schema_name := 'JG';
114 --Fetching Set of books id and Org id
115 BEGIN
116
117 ------------------------------------------
118
119 SELECT legal_entity_id
120 INTO g_legal_entity_id
121 FROM jg_zz_vat_rep_entities
122 WHERE VAT_REPORTING_ENTITY_ID = g_vat_reporting_entity_id;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
127 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_INVOICES_ABOVE_THRESHOLD.Extract_data','Exception in Fetching legal entity id ');
128 END IF;
129 retcode :=2;
130 errbuf :='Exception in Fetching legal entity id';
131 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
132 debug_message( g_error_buffer);
133 return;
134 END;
135
136 debug_message('Legal entity id :'||g_legal_entity_id);
137 Begin
138 SELECT owner
139 INTO g_table_schema
140 FROM all_tables
141 WHERE table_name = 'JE_IT_TRX_LINES_ALL'
142 AND OWNER = l_schema_name;
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 g_table_schema := 'JE';
146 END;
147 debug_message('Table Schema Name :'||g_table_schema);
148 ----------------------------------------------------------------------------
149 BEGIN -- check whether setup is frozen or not for the fiscal year.
150 OPEN cur_frozen(p_vat_reporting_entity_id,p_year);
151 FETCH cur_frozen INTO l_status_code;
152 CLOSE cur_frozen;
153
154 IF l_status_code <> 'Y' THEN
155 FND_MESSAGE.SET_NAME('JE','JE_IT_SETUP_NOT_FROZEN');
156 FND_MESSAGE.SET_TOKEN('VAT_REP',p_vat_reporting_entity_id);
157 FND_MESSAGE.SET_TOKEN('VAT_YEAR',p_year);
158 errbuf :=FND_MESSAGE.get;
159 retcode := 2; -- Error
160 debug_message( 'Setup not frozen for VAT Reporting Entity'||p_vat_reporting_entity_id|| 'and declaration year '||p_year);
161 return;
162 END IF;
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN -- If setup not available.
165 FND_MESSAGE.SET_NAME('JE','JE_IT_SETUP_NOT_AVAILABLE');
166 FND_MESSAGE.SET_TOKEN('VAT_REP',p_vat_reporting_entity_id);
167 FND_MESSAGE.SET_TOKEN('VAT_YEAR',p_year);
168 errbuf :=FND_MESSAGE.get;
169 retcode := 2; -- Error
170 IF g_debug_flag = 'Y' THEN
171 debug_message( 'Setup doesnt exist for VAT Reporting Entity'||p_vat_reporting_entity_id|| 'and declaration year '||p_year);
172 END IF;
173 return;
174 WHEN OTHERS THEN
175 errbuf :='Exception in fetching Freeze Status';
176 retcode := 2;
177 debug_message( 'Exception in fetching frozen status of VAT Reporting Entity'||p_vat_reporting_entity_id|| 'and declaration year '||p_year);
178 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
179 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,'Exception in fetching frozen status of VAT Reporting Entity'||p_vat_reporting_entity_id|| 'and declaration year '||p_year);
180 END IF;
181 return;
182 END;
183 BEGIN
184
185 SELECT meaning INTO l_type_of_upload_mng
186 FROM fnd_lookups
187 WHERE lookup_type='JEIT_UPLOAD_TYPE'
188 AND lookup_code=p_type_of_upload;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 NULL;
193 END;
194 BEGIN
195
196 IF p_type_of_upload = 1 THEN -- If type of upload is 'Substitution'
197
198 SELECT COUNT(*)
199 INTO l_count
200 FROM je_it_above_thr_hdr_all
201 WHERE year_of_declaration = p_year
202 AND vat_reporting_entity_id = p_vat_reporting_entity_id
203 AND type_of_upload in (0,2)
204 AND report_mode = 'F';
205
206 IF l_count = 0 THEN
207 FND_MESSAGE.SET_NAME('JE','JE_IT_CANNOT_ISSUE_DECL1');
208 errbuf :=FND_MESSAGE.get;
209 retcode := 2; -- Error
210 debug_message( 'Original or Cancellation Declaration was not submitted in Final mode'||p_year);
211 return;
212 END IF;
213
214 ELSIF p_type_of_upload = 2 THEN -- If type of upload is 'cancellation'
215
216 SELECT COUNT(*)
217 INTO l_count
218 FROM je_it_above_thr_hdr_all
219 WHERE year_of_declaration = p_year
220 AND vat_reporting_entity_id = p_vat_reporting_entity_id
221 AND type_of_upload in (0,1)
222 AND report_mode = 'F';
223
224 IF l_count = 0 THEN
225 FND_MESSAGE.SET_NAME('JE','JE_IT_CANNOT_ISSUE_DECL2');
226 errbuf :=FND_MESSAGE.get;
227 retcode := 2; -- Error
228 debug_message( 'Original or Substitution Declaration was not submitted in Final mode'||p_year);
229 return;
230 END IF;
231
232 END IF; -- end for IF p_type_of_upload = 1
233
234
235 -- Check whether Final reporting is done or not for the declaration year and type of upload combination.
236 l_status_code := null;
237 OPEN cur_report_mode(p_vat_reporting_entity_id,p_year,p_type_of_upload);
238 FETCH cur_report_mode INTO l_status_code;
239 CLOSE cur_report_mode;
240 debug_message('earlier report mode-'||l_status_code);
241 IF l_status_code in ('F','R') THEN
242 FND_MESSAGE.SET_NAME('JE','JE_IT_FINAL_LISTING');
243 FND_MESSAGE.SET_TOKEN('VAT_REP',p_vat_reporting_entity_id);
244 FND_MESSAGE.SET_TOKEN('VAT_YEAR',p_year);
245 FND_MESSAGE.SET_TOKEN('UPLOAD_TYPE',l_type_of_upload_mng);
246 errbuf :=FND_MESSAGE.get;
247 retcode := 2; -- Error
248 debug_message( 'Report got already submitted in Final mode for the declaratiob year '||p_year||' and for type of upload '||p_type_of_upload);
249 return;
250 ELSIF l_status_code='P' THEN -- report_mode is 'preliminary'.
251 debug_message('before calling purge proc');
252 Purge_trx_data(p_vat_reporting_entity_id,p_year,p_type_of_upload);
253 IF g_retcode = 2 THEN
254 errbuf := g_errbuf;
255 retcode:= g_retcode;
256 RETURN;
257 END IF;
258 END IF;
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261 NULL;
262 WHEN OTHERS THEN
263 errbuf :='Exception in fetching report mode';
264 retcode := 2;
265 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
266 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,'Exception in fetching reprot mode of declaration year '||p_year||' and type of upload '||p_type_of_upload);
267 END IF;
268 return;
269 END;
270
271 -- Data Extract can be run only for 'Preliminary' mode. So when customer running the extract again
272 -- with same parameters then delete the earlier run's data.
273
274 --fetch default payment mode
275 BEGIN
276
277 debug_message('before fetching default payment mode');
278 SELECT default_payment_mode
279 INTO g_default_payment_mode
280 FROM je_it_setup_header_all
281 WHERE year_of_declaration = p_year
282 AND vat_reporting_entity_id = p_vat_reporting_entity_id
283 AND ROWNUM = 1; -- form has validation. only one record exists for declaration year.
284 debug_message('g_default_payment_mode:'||g_default_payment_mode);
285 EXCEPTION
286 WHEN OTHERS THEN
287 errbuf :='Exception in fetching default payment mode';
288 retcode := 2;
289 debug_message(errbuf);
290 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
291 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,'Exception in fetching default payment mode');
292 END IF;
293 return;
294 END;
295
296 --------------------------------------------------------------------------
297 --
298 -- This period start date end date should be hard coded to 01-01-RRRR and 31-12-RRRRR
299 --
300 g_start_date := to_date('01-01-'||g_year,'DD-MM-RRRR');
301 g_end_date := to_date('31-12-'||g_year,'DD-MM-RRRR');
302
303 ---------------------------------------------------------------------------
304 debug_message('Start and End date of the fiscal year: '||g_start_date||' - '||g_end_date);
305 g_b2c_trx_date := NVL(g_b2c_trx_start_date,g_end_date_for_cm_dm+1);
306
307
308 BEGIN
309 INSERT INTO je_it_above_thr_hdr_all (vat_reporting_entity_id,
310 year_of_declaration, -- no need of storing the company details in hdr table. write a separate query in xml file.
311 org_id,
312 report_mode,
313 type_of_upload,
314 transmission_code,
315 protocol_num,
316 province_code,
317 declr_of_incorp_comp,
318 trx_accounting_status,
319 end_date_to_extr_cm_dm,
320 b2c_invoice_threshold,
321 b2b_invoice_threshold,
322 start_date_to_extr_b2c_trx,
323 no_of_detailed_records,
324 request_id,
325 last_update_date,
326 last_updated_by,
327 last_update_login,
328 creation_date,
329 created_by )
330 SELECT p_vat_reporting_entity_id,
331 p_year,
332 g_org_id,
333 'P',
334 p_type_of_upload,
335 p_transmission_code,
336 p_protocol_num,
337 p_province_code,
338 p_decl_inc_company,
339 p_inv_acctg_status,
340 g_end_date_for_cm_dm,
341 p_b2c_threshold,
342 p_b2b_threshold,
343 g_b2c_trx_start_date,
344 p_max_no_of_records,
345 g_conc_request_id,
346 g_last_update_date,
347 g_last_updated_by,
348 g_last_update_login,
349 g_creation_date,
350 g_created_by
351 FROM dual;
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 errbuf := 'Error while inserting data into JE_IT_ABOVE_THR_HDR_ALL table '||SQLCODE||'-'||SQLERRM;
356 retcode := 2;
357 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
358 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
359 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,'Error while inserting data into je_it_list_hdr_all table');
360 debug_message('Error while inserting data into hdr table'||SQLCODE||'-'||SQLERRM);
361 END IF;
362 return;
363 END;
364 IF p_type_of_upload <> 2 THEN -- If type of upload is 'Cancellation' then no need to run the extract.
365
366 Extract_AP_Trx_data; -- Extract AP data and inserts into je_it_trx_lines_all table.
367 IF g_retcode = 2 THEN
368 errbuf := g_errbuf;
369 retcode := 2;
370 return;
371 END IF;
372
373 Extract_AR_Trx_data;
374 IF g_retcode = 2 THEN
375 errbuf := g_errbuf;
376 retcode := 2;
377 return;
378 END IF;
379
380 CHECK_PAYMENT_MODE_ERROR;
381 IF g_retcode = 2 THEN
382 errbuf := g_errbuf;
383 retcode := 2;
384 return;
385 END IF;
386
387 CHECK_PARTIAL_ADJ_INV;
388 IF g_retcode = 2 THEN
389 errbuf := g_errbuf;
390 retcode := 2;
391 return;
392 END IF;
393 Check_missing_invoice;
394 IF g_retcode = 2 THEN
395 errbuf := g_errbuf;
396 retcode := 2;
397 return;
398 END IF;
399
400 Update_Above_Threshold_Lines;
401 IF g_retcode = 2 THEN
402 errbuf := g_errbuf;
403 retcode := 2;
404 return;
405 END IF;
406
407 Insert_Above_Threshold_Lines;
408 IF g_retcode = 2 THEN
409 errbuf := g_errbuf;
410 retcode := 2;
411 return;
412 END IF;
413 END IF; -- End for If p_type_of_upload <> 2
414 debug_message('End');
415
416 ---------------------------- Pending need to modify once it is finalized on audit and EFT cp.
417
418 -- Submit Audit Report and EFT Report (EFT based on parameter)
419 debug_message('p_year :'||p_vat_reporting_entity_id);
420 debug_message('p_year :'||p_year);
421 debug_message('p_type_of_upload :'||p_type_of_upload);
422 debug_message('g_conc_request_id :'||g_conc_request_id);
423
424 BEGIN
425 SELECT LOWER(FL.iso_language)
426 ,FL.iso_territory
427 INTO l_template_language
428 ,l_template_territory
429 FROM NLS_SESSION_PARAMETERS NSP
430 ,fnd_languages FL
431 WHERE NSP.parameter = 'NLS_LANGUAGE'
432 AND NSP.value = FL.nls_language;
433
434 fnd_file.put_line(fnd_file.log,'l_template_language :'||l_template_language);
435 fnd_file.put_line(fnd_file.log,'l_template_territory :'||l_template_territory);
436
437 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,'JEITIATA',l_template_language,l_template_territory,'PDF');
438 l_request_id := fnd_request.submit_request(application => l_appln_name,
439 program => l_con_cp_audit,
440 description => l_con_cp_audit_desc,
441 start_time => NULL,
442 sub_request => FALSE,
443 argument1 => p_vat_reporting_entity_id,
444 argument2 => p_year,
445 argument3 => p_type_of_upload,
446 argument4 => g_conc_request_id,
447 argument6 => CHR(0));
448 IF l_request_id = 0 THEN
449 debug_message('Error occurred while generating the Audit Report');
450 END IF;
451 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
452 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.je_it_invoices_above_threhold','Audit Report Request Id = '||l_request_id);
453 END IF;
454 EXCEPTION
455 WHEN OTHERS THEN
456 debug_message('Error while generating the EFT report');
457 return;
458 END;
459
460 IF g_gen_eft_report = 'Y' THEN
461 BEGIN
462 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,'JEITIATEF','en','US','ETEXT');
463 l_request_id := fnd_request.submit_request(application => l_appln_name,
464 program => l_con_cp_eft,
465 description => l_con_cp_eft_desc,
466 start_time => NULL,
467 sub_request => FALSE,
468 argument1 => p_vat_reporting_entity_id,
469 argument2 => p_year, --Fiscal year
470 argument3 => p_type_of_upload,
471 argument4 => g_conc_request_id,
472 argument5 => 'P',
473 argument6 => 1,
474 argument7 => CHR(0));
475
476 IF l_request_id = 0 THEN
477 debug_message('Error occurred while generating the EFT Report');
478 END IF;
479 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
480 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.je_it_invoices_above_threhold','EFT Report Request Id = '||l_request_id);
481 END IF;
482 EXCEPTION
483 WHEN OTHERS THEN
484 debug_message('Error while generating the EFT report');
485 return;
486 END;
487 END IF;
488 -----------------------------------
489
490 END Extract_data;
491
492 PROCEDURE purge_trx_data (p_vat_reporting_entity_id NUMBER,
493 p_year NUMBER,
494 p_type_of_upload NUMBER) IS
495 l_api_name VARCHAR2(50) := 'Purge_trx_data';
496 l_conc_request_id NUMBER;
497 BEGIN
498 debug_message('in purge_trx_data1');
499 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
500 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Start PROCEDURE Purge_trx_data');
501 END IF;
502 g_retcode :=0;
503
504 debug_message('in purge_trx_data');
505 SELECT REQUEST_ID
506 INTO l_conc_request_id
507 FROM je_it_above_thr_hdr_all
508 WHERE year_of_declaration = p_year
509 AND vat_reporting_entity_id = p_vat_reporting_entity_id
510 AND type_of_upload = p_type_of_upload;
511
512 debug_message('after request_id -'||l_conc_request_id );
513
514 DELETE je_it_trx_lines_all
515 WHERE REQUEST_ID = l_conc_request_id;
516
517 DELETE je_it_trx_above_thr_all
518 WHERE REQUEST_ID = l_conc_request_id;
519
520 DELETE je_it_above_thr_hdr_all
521 WHERE REQUEST_ID = l_conc_request_id;
522
523 debug_message('after delete');
524 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
525 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name,'End PROCEDURE Purge_trx_data');
526 END IF;
527 EXCEPTION
528 WHEN OTHERS THEN
529 g_retcode :=2;
530 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Purge_trx_data';
531 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
532 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,'Exception in PROCEDURE Purge_trx_data');
533 END IF;
534 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
535 IF g_debug_flag = 'Y' THEN
536 debug_message( g_error_buffer);
537 END IF;
538 return;
539 END Purge_trx_data;
540
541
542 PROCEDURE Extract_AP_Trx_data IS
543 l_api_name VARCHAR2(30) := 'Extract_AP_Trx_Data';
544
545 BEGIN
546 debug_message('Start Of Extract_ap_trx_data');
547
548 INSERT INTO je_it_trx_lines_all (REQUEST_ID,
549 VAT_REPORTING_ENTITY_ID,
550 ORG_ID,
551 APPLICATION_ID,
552 PARTY_ID,
553 PARTY_NAME,
554 VAT_REGISTRATION_NUM,
555 TAX_PAYER_ID,
556 COUNTRY,
557 TRX_ID,
558 TRX_NUM,
559 TRX_DATE,
560 TRX_GL_DATE,
561 TRX_TYPE,
562 PAYMENT_METHOD,
563 TRX_ACCOUNTING_STATUS,
564 trx_line_id,
565 TRX_LINE_NUM,
566 TRX_LINE_TYPE,
567 ASSESSABLE_AMT,
568 VAT_AMT,
569 PAYMENT_MODE,
570 BELOW_THRESHOLD_FLAG,
571 REPORT_EXCLUSION_FLAG,
572 CONTRACT_IDENTIFICATION,
573 ADJ_INV_FLAG,
574 ORIG_TRX_ID,
575 PARTY_TYPE,
576 INDV_PARTY_LAST_NAME, --not required. Same as vendor name
577 INDV_PARTY_FIRST_NAME,
578 INDV_PARTY_DOB,
579 INDV_PARTY_CITY,
580 INDV_PARTY_PROVINCE,
581 COMPANY_CITY,
582 COMPANY_ADDRESS,
583 INCONST_CM_DM_APPL_FLAG,
584 LAST_UPDATE_DATE,
585 LAST_UPDATED_BY,
586 LAST_UPDATE_LOGIN,
587 CREATION_DATE,
588 CREATED_BY)
589 select g_conc_request_id,
590 g_vat_reporting_entity_id,
591 null,
592 200,
593 pv.vendor_id,
594 substr(pv.vendor_name,1,60),
595 substr(NVL(pvs.vat_registration_num,pv.vat_registration_num),1,11),
596 NULL, -- taxpayer_id not required for payables as there is no need of B2C transactions
597 pvs.country,
598 ai.invoice_id,
599 ai.invoice_num,
600 ai.invoice_date,
601 ai.gl_date,
602 ai.invoice_type_lookup_code trx_type,
603 --alc.displayed_field
604 alc.payment_method_name payment_method,
605 DECODE(NVL(aid.posted_flag,'N'),'Y','ACCOUNTED','UNACCOUNTED'),
606 aid.invoice_distribution_id,
607 ail.line_number,
608 aid.line_type_lookup_code line_type_lookup_code,
609 DECODE(aid.line_type_lookup_code, 'REC_TAX',0
610 , 'NONREC_TAX',0
611 , Decode(ai.invoice_currency_code,g_currency_code,aid.amount, aid.base_amount)) assessable_amt,
612 DECODE(aid.line_type_lookup_code,'REC_TAX',Decode(ai.invoice_currency_code,g_currency_code,aid.Amount, aid.base_amount)
613 ,'NONREC_TAX',Decode(ai.invoice_currency_code,g_currency_code,aid.amount, aid.base_amount)
614 ,0) vat_amt,
615 NVL(ail.global_attribute11,g_default_payment_mode) payment_mode,
616 NVL(ail.global_attribute9,'N') below_threshold_flag,
617 NVL(ail.global_attribute10,'N') report_exclusion_flag,
618 ail.global_attribute8 contract_ident,
619 NVL(ail.global_attribute6,'N') adj_inv_flag,
620 decode(ai.invoice_type_lookup_code,'CREDIT',NVL(aid.parent_invoice_id,ail.global_attribute7),
621 'DEBIT',aid.parent_invoice_id,ail.global_attribute7)ORIG_TRX_ID,
622 DECODE(pvs.country,'IT',null,hp.party_type) party_type,
623 DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)) indv_last_name,
624 DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)) indv_first_name,
625 DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(pv.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)) INDV_PARTY_DOB,
626 DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(pv.global_attribute3,1,40),null)) INDV_PARTY_CITY,
627 DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',pv.global_attribute4,null)) INDV_PARTY_PROVINCE,
628 DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(pvs.city,1,40),null)) company_city,
629 DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION', substr(pvs.address_line1||' '||pvs.address_line2||' '||pvs.address_line3,1,40),null)) company_address,
630 is_cm_dm_line_consistent(200,DECODE(ai.invoice_type_lookup_code,'CREDIT',NVL(aid.parent_invoice_id,ail.global_attribute7),
631 'DEBIT',aid.parent_invoice_id,
632 ail.global_attribute7),
633 aid.line_type_lookup_code,ail.global_attribute9, ail.global_attribute10,
634 ail.global_attribute8,ail.global_attribute11) INCONST_CM_DM_APPL_FLAG,
635 g_last_update_date,
636 g_last_updated_by,
637 g_last_update_login,
638 g_creation_date,
639 g_created_by
640
641
642 FROM ap_invoices_All ai,
643 ap_invoice_distributions_all aid,
644 ap_invoice_lines_all ail,
645 po_vendors pv,
646 po_vendor_sites_all pvs,
647 iby_payment_methods_vl alc, -- ap_lookup_codes alc,
648 (SELECT distinct person_id ,national_identifier
649 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf,
650 je_it_setup_doc_seqs_all ds,
651 jg_zz_vat_rep_entities repent,
652 hz_parties hp,
653 ap_invoice_distributions_all itemdist,
654 ap_invoice_lines_all itemline
655
656 WHERE repent.vat_reporting_entity_id = g_vat_reporting_entity_id
657 AND ( ( repent.entity_type_code = 'LEGAL'
658 AND ai.legal_entity_id = repent.legal_entity_id ))
659 AND ai.invoice_id = aid.invoice_id
660 AND ai.invoice_id = ail.invoice_id
661 AND ail.line_number = aid.invoice_line_number
662 AND ai.vendor_id = pv.vendor_id
663 AND pv.vendor_id = pvs.vendor_id
664 AND ai.vendor_site_id = pvs.vendor_site_id
665 /* --Changed for performance
666 AND repent.tax_regime_code = (select zx.tax_regime_code
667 From ap_invoice_distributions_all aida1,
668 zx_rates_b zx
669 Where aida1.invoice_id = Ai.invoice_id
670 And ( aida1.charge_applicable_to_dist_id= aid.invoice_distribution_id or
671 aida1.invoice_distribution_id = aid.invoice_distribution_id )
672 And zx.Tax_Rate_Id = aida1.tax_code_id
673 and rownum = 1)
674 */
675 AND
676 (
677 repent.tax_regime_code =
678 (SELECT zx.tax_regime_code
679 FROM ap_invoice_distributions_all aida1a,
680 zx_rates_b zx
681 WHERE aida1a.invoice_id = ai.invoice_id
682 AND aida1a.charge_applicable_to_dist_id = aid.invoice_distribution_id
683 AND zx.tax_rate_id = aida1a.tax_code_id
684 AND rownum = 1)
685 OR
686 repent.tax_regime_code =
687 (SELECT zx.tax_regime_code
688 FROM ap_invoice_distributions_all aida1b,
689 zx_rates_b zx
690 WHERE aida1b.invoice_id = ai.invoice_id
691 AND aida1b.invoice_distribution_id = aid.invoice_distribution_id
692 AND zx.tax_rate_id = aida1b.tax_code_id
693 AND rownum = 1)
694 OR
695 repent.tax_regime_code =
696 (SELECT zx.tax_regime_code
697 FROM ap_invoice_distributions_all aida1c,
698 zx_rates_b zx
699 WHERE aida1c.invoice_id = ai.invoice_id
700 AND aida1c.charge_applicable_to_dist_id = aid.related_id
701 AND zx.tax_rate_id = aida1c.tax_code_id
702 AND rownum = 1)
703 )
704 AND aid.line_type_lookup_code <> 'AWT'
705 AND NOT EXISTS (SELECT 1
706 FROM ap_invoice_distributions_all aid1
707 WHERE aid1.invoice_id = ai.invoice_id
708 AND NVL(aid1.match_status_flag,'N') <>'A')
709 AND (aid.tax_code_id IS not NULL or exists (select 1
710 from ap_invoice_distributions_all aida
711 WHERE aida.invoice_id = ai.invoice_id
712 AND aida.charge_applicable_to_dist_id= aid.invoice_distribution_id)
713 or exists (select 1
714 from ap_invoice_distributions_all aida
715 WHERE aida.invoice_id = ai.invoice_id
716 AND aida.charge_applicable_to_dist_id= aid.related_id))
717 AND (pv.vat_registration_num IS NOT NULL OR pvs.vat_registration_num IS NOT NULL) -- only B2B invoices for payables.
718 --AND ai.payment_method_code = alc.lookup_code
719 --AND alc.lookup_type = 'PAYMENT METHOD'
720 AND ai.payment_method_code = alc.payment_method_code
721 AND (pvs.country ='IT' OR pvs.country IN ( select territory_code from FND_Territories where alternate_territory_code IS NOT NULL))
722 -- we need to pick only IT countries and EU countries transactions.
723 AND NVL(pv.employee_id, -99) = papf.person_id (+)
724 AND hp.party_id =pv.party_id
725 AND itemline.invoice_id = ai.invoice_id
726 AND itemdist.invoice_distribution_id (+) = aid.charge_applicable_to_dist_id
727 AND itemline.line_number = NVL(itemdist.invoice_line_number,aid.invoice_line_number)
728
729 AND (TRUNC(aid.accounting_date) BETWEEN g_start_date AND g_end_date
730 OR ((ai.invoice_type_lookup_code in ('CREDIT','DEBIT') OR NVL(ail.global_attribute6,'N')='Y' OR
731 (NVL(itemline.global_attribute6,'N')='Y' AND aid.line_type_lookup_code in('REC_TAX','NONREC_TAX') ))
732 AND TRUNC(aid.accounting_date) BETWEEN g_start_date AND g_end_date_for_cm_dm))
733
734 AND ai.doc_sequence_id = ds.document_sequence_id
735 AND ds.vat_reporting_entity_id = g_vat_reporting_entity_id
736 AND ds.year_of_declaration = g_year
737 AND ds.application_id = 200
738 AND ((g_inv_acctg_status = 'ACCOUNTED' AND nvl(aid.posted_flag,'N') = 'Y') --TRX_ACCOUNTING_STATUS check
739 OR (g_inv_acctg_status = 'UNACCOUNTED' AND nvl(aid.posted_flag,'N') = 'N')
740 OR g_inv_acctg_status = 'BOTH')
741 AND NOT EXISTS (SELECT 1 -- Black listed countries
742 FROM je_it_country_rep_codes cc
743 WHERE pvs.country = cc.country_code
744 AND aid.accounting_date BETWEEN NVL(cc.rep_exclusion_date_from,sysdate)
745 AND NVL(cc.rep_exclusion_date_to,sysdate));
746 -- AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date;
747
748 debug_message('Number of records inserted :'||SQL%ROWCOUNT);
749
750 -- Update the record types.
751 -- For normal invoices (which are not CM,DM,Adj Inv), if the country='IT' then record type will 2. if country <>'IT' then record type is 3
752 -- For adjustment invs(eg.. CM,DM,Adj Inv), If original invoice exists in current year's data - then if country='IT' -> record type = 2
753 -- if country<>'IT' -> record type = 3
754 -- For adjustment invs(eg.. CM,DM,Adj Inv), if original invoice column not populated or original invoice exists in earlier year's data
755 -- then if country ='IT' -> record type = 4
756 -- if country<>'IT' -> record type = 5
757 -- For adjustment invs(eg.. CM,DM,Adj Inv), original invoice populated but that data doesn't exist in this year amd earlier year
758 -- then it will be taken care by check_missing_invoice proc.(orig invoice missing error)
759
760 --Added for Performance -Start
761 commit;
762 fnd_stats.gather_table_stats(g_table_schema,'JE_IT_TRX_LINES_ALL',PERCENT=>30);
763 --Added for Performance -End
764
765 debug_message('Updating gdf information for AP Tax lines');
766
767 BEGIN
768 /*
769 --Changed for performace
770 UPDATE je_it_trx_lines_all trx_lines
771 SET (payment_mode,
772 below_threshold_flag,
773 report_exclusion_flag,
774 contract_identification,
775 adj_inv_flag,
776 orig_trx_id,
777 inconst_cm_dm_appl_flag) = (SELECT
778 payment_mode,
779 below_threshold_flag,
780 report_exclusion_flag,
781 contract_identification,
782 adj_inv_flag,
783 orig_trx_id,
784 inconst_cm_dm_appl_flag
785 FROM je_it_trx_lines_all item_lines
786 WHERE item_lines.trx_id = trx_lines.trx_id
787 AND item_lines.REQUEST_ID = g_conc_request_id
788 AND item_lines.trx_line_id = (SELECT adl.charge_applicable_to_dist_id FROM
789 ap_invoice_distributions_all adl
790 WHERE adl.invoice_distribution_id =trx_lines.trx_line_id
791 AND adl.invoice_id =item_lines.trx_id)
792 )
793 WHERE trx_lines.application_id = 200
794 and trx_lines.trx_line_type like '%TAX'
795 AND trx_lines.REQUEST_ID = g_conc_request_id;
796 */
797
798 UPDATE je_it_trx_lines_all trx_lines
799 SET (payment_mode,
800 below_threshold_flag,
801 report_exclusion_flag,
802 contract_identification,
803 adj_inv_flag,
804 orig_trx_id,
805 inconst_cm_dm_appl_flag)
806 =
807 (SELECT payment_mode,
808 below_threshold_flag,
809 report_exclusion_flag,
810 contract_identification,
811 adj_inv_flag,
812 orig_trx_id,
813 inconst_cm_dm_appl_flag
814 FROM je_it_trx_lines_all item_lines,
815 ap_invoice_distributions_all adl
816 WHERE item_lines.trx_id = trx_lines.trx_id
817 AND item_lines.request_id = g_conc_request_id
818 AND item_lines.trx_line_id = adl.charge_applicable_to_dist_id
819 AND adl.invoice_distribution_id = trx_lines.trx_line_id
820 AND adl.invoice_id = item_lines.trx_id)
821 WHERE trx_lines.application_id = 200
822 and trx_lines.trx_line_type like '%TAX'
823 AND trx_lines.REQUEST_ID = g_conc_request_id;
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 g_retcode :=2;
828 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Extract_AP_Trx_data while updating tax lines GDF';
829 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
830 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Extract_AP_Trx_data while updating tax lines GDF');
831 END IF;
832 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
833 debug_message('Error while updating tax lines GDF in extract_ap_trx_data -'||SQLERRM);
834 return;
835 END;
836
837 BEGIN
838
839 UPDATE je_it_trx_lines_all
840 SET record_type = DECODE(country,'IT',2,3)
841 WHERE application_id = 200
842 AND request_id = g_conc_request_id
843 AND trx_type <> 'CREDIT'
844 AND trx_type <> 'DEBIT'
845 AND adj_inv_flag = 'N';
846
847 UPDATE je_it_trx_lines_all a
848 SET record_type = DECODE(country,'IT',2,3)
849 WHERE application_id = 200
850 AND request_id = g_conc_request_id
851 AND (a.trx_type = 'CREDIT' OR a.trx_type = 'DEBIT' OR a.adj_inv_flag = 'Y')
852 AND a.orig_trx_id IS NOT NULL
853 AND exists (SELECT 1
854 FROM je_it_trx_lines_all b
855 WHERE b.request_id = g_conc_request_id
856 AND b.application_id = 200
857 AND b.trx_id = a.orig_trx_id);
858
859 UPDATE je_it_trx_lines_all a
860 SET a.record_type = DECODE(a.country,'IT',4,5)
861 WHERE a.application_id = 200
862 AND a.request_id = g_conc_request_id
863 AND (a.trx_type = 'CREDIT' OR a.trx_type = 'DEBIT' OR a.adj_inv_flag = 'Y')
864 AND (a.orig_trx_id IS NULL
865 OR EXISTS (SELECT 1
866 FROM je_it_trx_lines_all b,
867 je_it_above_thr_hdr_all h
868 WHERE h.year_of_declaration in (g_year-1,g_year-2)
869 AND h.request_id = b.request_id
870 AND b.application_id = 200
871 AND b.trx_id = a.orig_trx_id
872 AND b.is_above_threshold = 'Y'));
873
874 EXCEPTION
875 WHEN OTHERS THEN
876 g_retcode :=2;
877 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Extract_AP_Trx_data while updating record_type';
878 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
879 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Extract_AP_Trx_data while updating record_type');
880 END IF;
881 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
882 debug_message('Error while updating record type in extract_ap_trx_data -'||SQLERRM);
883 return;
884 END;
885
886 debug_message('End of Extract_ap_trx_data');
887
888 EXCEPTION
889 WHEN OTHERS THEN
890 g_retcode :=2;
891 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Extract_AP_Trx_data';
892 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
893 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Extract_AP_Trx_data');
894 END IF;
895 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
896 debug_message('Error while inserting data in extract_ap_trx_data -'||SQLERRM);
897 return;
898 END Extract_AP_Trx_data;
899
900 FUNCTION is_cm_dm_line_consistent( p_application_id NUMBER,
901 p_parent_trx_id VARCHAR2,
902 p_line_type VARCHAR2,
903 p_below_threshold VARCHAR2,
904 p_report_exclusion VARCHAR2,
905 p_contract_ident VARCHAR2,
906 p_payment_mode VARCHAR2) RETURN VARCHAR2 IS
907 l_consistent_flag VARCHAR2(1) := 'Y';
908 l_count NUMBER := 0;
909 l_api_name VARCHAR2(60) := 'IS_CM_DM_LINE_CONSISTENT';
910 BEGIN
911 IF p_parent_trx_id IS NOT NULL AND p_line_type NOT IN ('REC_TAX','NONREC_TAX','TAX') THEN
912 BEGIN
913 IF p_application_id = 200 THEN -- AP Transaction
914
915 SELECT count(*)
916 INTO l_count
917 FROM ap_invoice_lines_all ail
918 WHERE ail.invoice_id = p_parent_trx_id
919 And ail.line_number In ( select aid1.invoice_line_number from ap_invoice_distributions_all aid1,ap_invoice_distributions_all aid2
920 Where aid1.invoice_id = p_parent_trx_id
921 and aid2.invoice_id = p_parent_trx_id
922 and aid2.charge_applicable_to_dist_id=aid1.invoice_distribution_id)
923 --AND aid.line_type_lookup_code = p_line_type
924 AND ail.line_type_lookup_code <> 'TAX'
925 AND NVL(ail.global_attribute9,'N') = NVL(p_below_threshold,'N')
926 AND NVL(ail.global_attribute10,'N') = NVL(p_report_exclusion,'N')
927 AND NVL(ail.global_attribute8,'ZZZ') = NVL(p_contract_ident,'ZZZ')
928 AND NVL(ail.global_attribute11,g_default_payment_mode) = NVL(p_payment_mode,g_default_payment_mode);
929 ELSE
930
931 SELECT count(*)
932 INTO l_count
933 FROM ra_customer_trx_lines_all rcl
934 WHERE rcl.customer_trx_id = p_parent_trx_id
935 AND rcl.line_type = p_line_type
936 AND exists (select 1 from ra_customer_trx_lines_all rc2
937 where rc2.link_to_cust_trx_line_id = rcl.customer_trx_line_id)
938 AND rcl.line_type <> 'TAX'
939 AND NVL(rcl.global_attribute9,'N') = NVL(p_below_threshold,'N')
940 AND NVL(rcl.global_attribute10,'N') = NVL(p_report_exclusion,'N')
941 AND NVL(rcl.global_attribute8,'ZZZ') = NVL(p_contract_ident,'ZZZ')
942 AND NVL(rcl.global_attribute11,g_default_payment_mode) = NVL(p_payment_mode,g_default_payment_mode);
943 END IF;
944 EXCEPTION
945 WHEN OTHERS THEN
946 g_retcode :=2;
947 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.is_cm_dm_line_consistent for parent_trx_id '||p_parent_trx_id;
948 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
949 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE is_cm_dm_line_consistent');
950 END IF;
951 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
952 return 'Y';
953 END;
954
955 IF l_count = 0 THEN
956
957 RETURN 'Y'; -- credit/debit memo line grouping doesn't exist in invoice line grouping. so inconsistent applciation.
958 ELSE
959 RETURN 'N'; -- consistent application.
960 END IF;
961
962 ELSE -- p_parent_trx_id is null.which means that credit/debit memo is not applied on any invoice.
963 RETURN NULL;
964 END IF;
965
966 END is_cm_dm_line_consistent;
967
968
969 FUNCTION get_orig_trx_id( p_cust_trx_id NUMBER,
970 p_parent_trx_id NUMBER
971 ) RETURN NUMBER IS
972
973 l_count NUMBER := 0;
974 l_orig_trx_id NUMBER := NULL;
975 l_api_name VARCHAR2(60) := 'get_orig_trx_id';
976 BEGIN
977
978 IF p_parent_trx_id IS NULL THEN
979
980 SELECT count(*)
981 INTO l_count
982 FROM ar_receivable_applications_all
983 WHERE customer_trx_id = p_cust_trx_id;
984
985 IF (l_count = 0 or l_count > 1) Then
986 l_orig_trx_id := NULL;
987 ELSE
988 SELECT applied_customer_trx_id
989 INTO l_orig_trx_id
990 FROM ar_receivable_applications_all
991 WHERE customer_trx_id = p_cust_trx_id;
992 END IF;
993 ELSE
994 l_orig_trx_id :=p_parent_trx_id;
995 END IF;
996 RETURN l_orig_trx_id;
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 g_retcode :=2;
1001 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.get_orig_trx_id for parent_trx_id '||p_cust_trx_id;
1002 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1003 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE get_orig_trx_id');
1004 END IF;
1005 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1006 --return 'Y';
1007
1008
1009 END get_orig_trx_id;
1010
1011
1012 PROCEDURE Extract_AR_Trx_data IS
1013 cursor c_update_gdf(p_request_id NUMBER)
1014 is select trx_id,trx_line_id from
1015 je_it_trx_lines_all
1016 where application_id = 222
1017 and trx_line_type = 'TAX'
1018 AND REQUEST_ID = p_request_id;
1019 l_api_name VARCHAR2(30) := 'Extract_AR_Trx_Data';
1020 BEGIN
1021
1022 debug_message('Start of Extract_AR_Trx_data');
1023 -- For performance Expand SQL into two separate statements UNION together.
1024 BEGIN
1025 INSERT INTO je_it_trx_lines_all (request_id,
1026 vat_reporting_entity_id,
1027 org_id,
1028 application_id,
1029 party_id,
1030 party_name,
1031 vat_registration_num,
1032 tax_payer_id,
1033 country,
1034 trx_id,
1035 trx_num,
1036 trx_date,
1037 trx_type,
1038 payment_method,
1039 trx_accounting_status,
1040 trx_line_id,
1041 trx_line_num,
1042 trx_line_type,
1043 assessable_amt,
1044 vat_amt,
1045 payment_mode,
1046 below_threshold_flag,
1047 report_exclusion_flag,
1048 contract_identification,
1049 adj_inv_flag,
1050 orig_trx_id,
1051 party_type,
1052 indv_party_last_name,
1053 indv_party_first_name,
1054 indv_party_dob,
1055 indv_party_city,
1056 indv_party_province,
1057 company_city,
1058 company_address,
1059 inconst_cm_dm_appl_flag,
1060 last_update_date,
1061 last_updated_by,
1062 last_update_login,
1063 creation_date,
1064 created_by)
1065 SELECT g_conc_request_id,
1066 g_vat_reporting_entity_id,
1067 g_org_id,
1068 222,
1069 hca.cust_account_id,
1070 substr(hp.party_name,1,60),
1071 substr(NVL(hcsu.tax_reference,hp.tax_reference),1,11) vat_registration_num,
1072 -- NVL(hcsu.tax_reference,hp.tax_reference) vat_registration_num, --bug 13897492
1073 hp.jgzz_fiscal_code taxpayer_id,
1074 hl.country,
1075 rct.customer_trx_id,
1076 rct.trx_number,
1077 rct.trx_date,
1078 rctt.type,
1079 arm.name,
1080 DECODE(nvl(rctd.posting_control_id,-3),-3,'UNACCOUNTED','ACCOUNTED') accounting_status,
1081 rctl.customer_trx_line_id,
1082 rctl.line_number,
1083 rctl.line_type,
1084 DECODE(rctl.line_type,'TAX',0,sum(rctd.amount * nvl(rct.exchange_rate,1))) assessable_amt,
1085 DECODE(rctl.line_type,'TAX',sum(rctd.amount * nvl(rct.exchange_rate,1)),0) vat_amt,
1086 nvl(rctl.global_attribute11,g_default_payment_mode) payment_mode,
1087 nvl(rctl.global_attribute9,'N') below_thre_flag,
1088 nvl(rctl.global_attribute10,'N') report_excl_flag,
1089 rctl.global_attribute8 contact_ident,
1090 NVL(rctl.global_attribute6,'N') adj_inv_flag,
1091 DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7) ORIG_TRX_ID,
1092 --DECODE(rctt.type,'CM',rctl.previous_customer_trx_id,rctl.global_attribute7) ORIG_TRX_ID,
1093 DECODE(hl.country,'IT',null,hp.party_type) party_type,
1094 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)) ind_party_last_name,
1095 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)) ind_party_first_name,
1096 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(hca.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)) ind_party_dob,
1097 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hca.global_attribute3,1,40),null)) ind_party_city,
1098 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',hca.global_attribute5,null)) ind_party_province,
1099 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.city,1,40),null)) company_city,
1100 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.address1||hl.address2||hl.address3,1,40),null)) company_address,
1101 is_cm_dm_line_consistent(222,DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),rctl.line_type,
1102 rctl.global_attribute9,rctl.global_attribute10,rctl.global_attribute8,rctl.global_attribute11) inconsistent_app,
1103 g_last_update_date,
1104 g_last_updated_by,
1105 g_last_update_login,
1106 g_creation_date,
1107 g_created_by
1108 FROM ra_customer_trx_all rct,
1109 ra_customer_trx_lines_all rctl,
1110 ra_cust_trx_line_gl_dist_all rctd,
1111 ra_cust_trx_types_all rctt,
1112 ar_receipt_methods arm,
1113 hz_cust_accounts hca,
1114 hz_parties hp,
1115 hz_cust_site_uses_all hcsu,
1116 hz_cust_acct_sites_all hcas,
1117 hz_party_sites hps,
1118 hz_locations hl,
1119 je_it_setup_doc_seqs_all ds,
1120 jg_zz_vat_rep_entities repent,
1121 zx_lines_v zx,
1122 ra_customer_trx_lines_all itemgdf
1123 where --rct.customer_trx_id = 665616 AND
1124 zx.trx_id = rct.customer_trx_id
1125 and zx.trx_line_id = rctl.customer_trx_line_id
1126 and zx.tax_regime_code = repent.tax_regime_code
1127 and repent.vat_reporting_entity_id = g_vat_reporting_entity_id AND
1128 ( ( repent.entity_type_code = 'LEGAL'
1129 AND rctt.legal_entity_id = repent.legal_entity_id ))
1130 and rct.customer_trx_id = rctl.customer_trx_id
1131 AND rctl.customer_trx_id = rctd.customer_trx_id
1132 AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
1133 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
1134 AND NVL(rct.complete_flag,'N') = 'Y'
1135 AND itemgdf.customer_trx_line_id (+) = rctl.link_to_cust_trx_line_id
1136 AND (TRUNC(rctd.gl_date) BETWEEN NVL2(NVL(hcsu.tax_reference,hp.tax_reference),g_start_date,g_b2c_trx_date) AND g_end_date
1137 OR ((rctt.type = 'CM' OR nvl(rctl.global_attribute6,'N')='Y' OR (rctl.line_type='TAX' and nvl(itemgdf.global_attribute6,'N')='Y' ))
1138 AND trunc(rctd.gl_date) BETWEEN NVL2(NVL(hcsu.tax_reference,hp.tax_reference),g_start_date,g_b2c_trx_date) AND g_end_date_for_cm_dm))
1139 AND rct.receipt_method_id = arm.receipt_method_id(+)
1140 AND rct.bill_to_customer_id = hca.cust_account_id
1141 AND hca.party_id = hp.party_id
1142 AND rct.bill_to_site_use_id = hcsu.site_use_id
1143 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
1144 AND hcas.party_site_id = hps.party_site_id
1145 AND hp.party_id = hps.party_id
1146 AND hps.location_id = hl.location_id
1147 AND (NVL(hcsu.tax_reference,hp.tax_reference) IS NULL OR hl.country ='IT' OR hl.country IN (select territory_code from FND_Territories where alternate_territory_code IS NOT NULL))
1148 AND ( rctl.vat_tax_id IS not NULL or exists (select 1 from
1149 ra_customer_trx_lines_all rctla
1150 where rctla.customer_trx_id = rct.customer_trx_id
1151 and rctla.link_to_cust_trx_line_id= rctl.customer_trx_line_id))
1152 AND rct.doc_sequence_id = ds.document_sequence_id
1153 AND ds.vat_reporting_entity_id = g_vat_reporting_entity_id
1154 AND ds.year_of_declaration = g_year
1155 AND ds.application_id = 222
1156 AND ((g_inv_acctg_status = 'ACCOUNTED' AND nvl(rctd.posting_control_id,-3) <> -3) --TRX_ACCOUNTING_STATUS check
1157 OR (g_inv_acctg_status = 'UNACCOUNTED' AND nvl(rctd.posting_control_id,-3) = -3)
1158 OR g_inv_acctg_status = 'BOTH')
1159 AND NOT EXISTS (SELECT 1 -- Black listed countries
1160 FROM je_it_country_rep_codes cc
1161 WHERE hl.country = cc.country_code
1162 AND rctd.gl_date BETWEEN NVL(cc.rep_exclusion_date_from,sysdate)
1163 AND NVL(cc.rep_exclusion_date_to,sysdate))
1164 group by 222,
1165 hca.cust_account_id,
1166 substr(hp.party_name,1,60),
1167 substr(NVL(hcsu.tax_reference,hp.tax_reference),1,11),
1168 --NVL(hcsu.tax_reference,hp.tax_reference),
1169 hp.jgzz_fiscal_code,
1170 hl.country,
1171 rct.customer_trx_id,
1172 rct.trx_number,
1173 rct.trx_date,
1174 rctt.type,
1175 arm.name,
1176 DECODE(nvl(rctd.posting_control_id,-3),-3,'UNACCOUNTED','ACCOUNTED'),
1177 rctl.customer_trx_line_id,
1178 rctl.line_number,
1179 rctl.line_type,
1180 nvl(rctl.global_attribute11,g_default_payment_mode),
1181 nvl(rctl.global_attribute9,'N'),
1182 nvl(rctl.global_attribute10,'N'),
1183 rctl.global_attribute8,
1184 NVL(rctl.global_attribute6,'N'),
1185 -- DECODE(rctt.type,'CM',rctl.previous_customer_trx_id,rctl.global_attribute7),
1186 DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),
1187 DECODE(hl.country,'IT',null,hp.party_type),
1188 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)),
1189 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)),
1190 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(hca.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)),
1191 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hca.global_attribute3,1,40),null)),
1192 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',hca.global_attribute5,null)),
1193 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.city,1,40),null)),
1194 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.address1||hl.address2||hl.address3,1,40),null)),
1195 is_cm_dm_line_consistent(222,DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),rctl.line_type,
1196 rctl.global_attribute9,rctl.global_attribute10,rctl.global_attribute8,rctl.global_attribute11)
1197 UNION
1198
1199 SELECT g_conc_request_id,
1200 g_vat_reporting_entity_id,
1201 g_org_id,
1202 222,
1203 hca.cust_account_id,
1204 substr(hp.party_name,1,60),
1205 substr(NVL(hcsu.tax_reference,hp.tax_reference),1,11) vat_registration_num,
1206 -- NVL(hcsu.tax_reference,hp.tax_reference) vat_registration_num, --bug 13897492
1207 hp.jgzz_fiscal_code taxpayer_id,
1208 hl.country,
1209 rct.customer_trx_id,
1210 rct.trx_number,
1211 rct.trx_date,
1212 rctt.type,
1213 arm.name,
1214 DECODE(nvl(rctd.posting_control_id,-3),-3,'UNACCOUNTED','ACCOUNTED') accounting_status,
1215 rctl.customer_trx_line_id,
1216 rctl.line_number,
1217 rctl.line_type,
1218 DECODE(rctl.line_type,'TAX',0,sum(rctd.amount * nvl(rct.exchange_rate,1))) assessable_amt,
1219 DECODE(rctl.line_type,'TAX',sum(rctd.amount * nvl(rct.exchange_rate,1)),0) vat_amt,
1220 nvl(rctl.global_attribute11,g_default_payment_mode) payment_mode,
1221 nvl(rctl.global_attribute9,'N') below_thre_flag,
1222 nvl(rctl.global_attribute10,'N') report_excl_flag,
1223 rctl.global_attribute8 contact_ident,
1224 NVL(rctl.global_attribute6,'N') adj_inv_flag,
1225 DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7) ORIG_TRX_ID,
1226 --DECODE(rctt.type,'CM',rctl.previous_customer_trx_id,rctl.global_attribute7) ORIG_TRX_ID,
1227 DECODE(hl.country,'IT',null,hp.party_type) party_type,
1228 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)) ind_party_last_name,
1229 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)) ind_party_first_name,
1230 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(hca.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)) ind_party_dob,
1231 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hca.global_attribute3,1,40),null)) ind_party_city,
1232 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',hca.global_attribute5,null)) ind_party_province,
1233 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.city,1,40),null)) company_city,
1234 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.address1||hl.address2||hl.address3,1,40),null)) company_address,
1235 is_cm_dm_line_consistent(222,DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),rctl.line_type,
1236 rctl.global_attribute9,rctl.global_attribute10,rctl.global_attribute8,rctl.global_attribute11) inconsistent_app,
1237 g_last_update_date,
1238 g_last_updated_by,
1239 g_last_update_login,
1240 g_creation_date,
1241 g_created_by
1242 FROM ra_customer_trx_all rct,
1243 ra_customer_trx_lines_all rctl,
1244 ra_cust_trx_line_gl_dist_all rctd,
1245 ra_cust_trx_types_all rctt,
1246 ar_receipt_methods arm,
1247 hz_cust_accounts hca,
1248 hz_parties hp,
1249 hz_cust_site_uses_all hcsu,
1250 hz_cust_acct_sites_all hcas,
1251 hz_party_sites hps,
1252 hz_locations hl,
1253 je_it_setup_doc_seqs_all ds,
1254 jg_zz_vat_rep_entities repent,
1255 zx_lines_v zx,
1256 ra_customer_trx_lines_all itemgdf
1257 where --rct.customer_trx_id = 665616 AND
1258 zx.trx_id = rct.customer_trx_id
1259 and zx.tax_line_id=rctl.tax_line_id
1260 and zx.tax_regime_code = repent.tax_regime_code
1261 and repent.vat_reporting_entity_id = g_vat_reporting_entity_id AND
1262 ( ( repent.entity_type_code = 'LEGAL'
1263 AND rctt.legal_entity_id = repent.legal_entity_id ))
1264 and rct.customer_trx_id = rctl.customer_trx_id
1265 AND rctl.customer_trx_id = rctd.customer_trx_id
1266 AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
1267 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
1268 AND NVL(rct.complete_flag,'N') = 'Y'
1269 AND itemgdf.customer_trx_line_id (+) = rctl.link_to_cust_trx_line_id
1270 AND (TRUNC(rctd.gl_date) BETWEEN NVL2(NVL(hcsu.tax_reference,hp.tax_reference),g_start_date,g_b2c_trx_date) AND g_end_date
1271 OR ((rctt.type = 'CM' OR nvl(rctl.global_attribute6,'N')='Y' OR (rctl.line_type='TAX' and nvl(itemgdf.global_attribute6,'N')='Y' ))
1272 AND trunc(rctd.gl_date) BETWEEN NVL2(NVL(hcsu.tax_reference,hp.tax_reference),g_start_date,g_b2c_trx_date) AND g_end_date_for_cm_dm))
1273 AND rct.receipt_method_id = arm.receipt_method_id(+)
1274 AND rct.bill_to_customer_id = hca.cust_account_id
1275 AND hca.party_id = hp.party_id
1276 AND rct.bill_to_site_use_id = hcsu.site_use_id
1277 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
1278 AND hcas.party_site_id = hps.party_site_id
1279 AND hp.party_id = hps.party_id
1280 AND hps.location_id = hl.location_id
1281 AND (NVL(hcsu.tax_reference,hp.tax_reference) IS NULL OR hl.country ='IT' OR hl.country IN (select territory_code from FND_Territories where alternate_territory_code IS NOT NULL))
1282 AND ( rctl.vat_tax_id IS not NULL or exists (select 1 from
1283 ra_customer_trx_lines_all rctla
1284 where rctla.customer_trx_id = rct.customer_trx_id
1285 and rctla.link_to_cust_trx_line_id= rctl.customer_trx_line_id))
1286 AND rct.doc_sequence_id = ds.document_sequence_id
1287 AND ds.vat_reporting_entity_id = g_vat_reporting_entity_id
1288 AND ds.year_of_declaration = g_year
1289 AND ds.application_id = 222
1290 AND ((g_inv_acctg_status = 'ACCOUNTED' AND nvl(rctd.posting_control_id,-3) <> -3) --TRX_ACCOUNTING_STATUS check
1291 OR (g_inv_acctg_status = 'UNACCOUNTED' AND nvl(rctd.posting_control_id,-3) = -3)
1292 OR g_inv_acctg_status = 'BOTH')
1293 AND NOT EXISTS (SELECT 1 -- Black listed countries
1294 FROM je_it_country_rep_codes cc
1295 WHERE hl.country = cc.country_code
1296 AND rctd.gl_date BETWEEN NVL(cc.rep_exclusion_date_from,sysdate)
1297 AND NVL(cc.rep_exclusion_date_to,sysdate))
1298 group by 222,
1299 hca.cust_account_id,
1300 substr(hp.party_name,1,60),
1301 substr(NVL(hcsu.tax_reference,hp.tax_reference),1,11),
1302 --NVL(hcsu.tax_reference,hp.tax_reference),
1303 hp.jgzz_fiscal_code,
1304 hl.country,
1305 rct.customer_trx_id,
1306 rct.trx_number,
1307 rct.trx_date,
1308 rctt.type,
1309 arm.name,
1310 DECODE(nvl(rctd.posting_control_id,-3),-3,'UNACCOUNTED','ACCOUNTED'),
1311 rctl.customer_trx_line_id,
1312 rctl.line_number,
1313 rctl.line_type,
1314 nvl(rctl.global_attribute11,g_default_payment_mode),
1315 nvl(rctl.global_attribute9,'N'),
1316 nvl(rctl.global_attribute10,'N'),
1317 rctl.global_attribute8,
1318 NVL(rctl.global_attribute6,'N'),
1319 -- DECODE(rctt.type,'CM',rctl.previous_customer_trx_id,rctl.global_attribute7),
1320 DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),
1321 DECODE(hl.country,'IT',null,hp.party_type),
1322 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)),
1323 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)),
1324 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(hca.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)),
1325 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hca.global_attribute3,1,40),null)),
1326 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',hca.global_attribute5,null)),
1327 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.city,1,40),null)),
1328 DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.address1||hl.address2||hl.address3,1,40),null)),
1329 is_cm_dm_line_consistent(222,DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),rctl.line_type,
1330 rctl.global_attribute9,rctl.global_attribute10,rctl.global_attribute8,rctl.global_attribute11);
1331
1332 debug_message('Number of records inserted :'||SQL%ROWCOUNT);
1333 debug_message('After insert in Extract_AR_Trx_data');
1334
1335 EXCEPTION
1336 WHEN OTHERS THEN
1337 g_retcode :=2;
1338 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Extract_AR_Trx_data';
1339 debug_message('Exception in Extract_AR_Trx_data :'||SQLERRM);
1340 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1341 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Extract_AR_Trx_data');
1342 END IF;
1343 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1344 return;
1345 END;
1346
1347 --Added for Performance -Start
1348 commit;
1349 fnd_stats.gather_table_stats(g_table_schema,'JE_IT_TRX_LINES_ALL',PERCENT=>30);
1350 --Added for Performance -End
1351 debug_message('Updating gdf information for AR Tax lines');
1352
1353 BEGIN
1354 /*
1355 --Changed for performace
1356 UPDATE je_it_trx_lines_all trx_lines
1357 SET (payment_mode,
1358 below_threshold_flag,
1359 report_exclusion_flag,
1360 contract_identification,
1361 adj_inv_flag,
1362 orig_trx_id,
1363 inconst_cm_dm_appl_flag) = (SELECT
1364 payment_mode,
1365 below_threshold_flag,
1366 report_exclusion_flag,
1367 contract_identification,
1368 adj_inv_flag,
1369 orig_trx_id,
1370 inconst_cm_dm_appl_flag
1371 FROM je_it_trx_lines_all item_lines
1372 WHERE item_lines.trx_id = trx_lines.trx_id
1373 AND item_lines.REQUEST_ID = g_conc_request_id
1374 AND item_lines.trx_line_id = (SELECT rctl.link_to_cust_trx_line_id FROM
1375 ra_customer_trx_lines_all rctl
1376 WHERE rctl.customer_trx_line_id =trx_lines.trx_line_id
1377 AND rctl.customer_trx_id =item_lines.trx_id)
1378 )
1379 WHERE trx_lines.application_id = 222
1380 and trx_lines.trx_line_type = 'TAX'
1381 AND trx_lines.REQUEST_ID = g_conc_request_id;
1382 --Changed for performace
1383 */
1384 UPDATE je_it_trx_lines_all trx_lines
1385 SET (payment_mode,
1386 below_threshold_flag,
1387 report_exclusion_flag,
1388 contract_identification,
1389 adj_inv_flag,
1390 orig_trx_id,
1391 inconst_cm_dm_appl_flag)
1392 =
1393 (SELECT
1394 payment_mode,
1395 below_threshold_flag,
1396 report_exclusion_flag,
1397 contract_identification,
1398 adj_inv_flag,
1399 orig_trx_id,
1400 inconst_cm_dm_appl_flag
1401 FROM je_it_trx_lines_all item_lines
1402 ,ra_customer_trx_lines_all rctl
1403 WHERE item_lines.trx_id = trx_lines.trx_id
1404 AND item_lines.REQUEST_ID = g_conc_request_id
1405 AND item_lines.trx_line_id = rctl.link_to_cust_trx_line_id
1406 AND rctl.customer_trx_line_id =trx_lines.trx_line_id
1407 AND rctl.customer_trx_id =item_lines.trx_id)
1408 WHERE trx_lines.application_id = 222
1409 and trx_lines.trx_line_type = 'TAX'
1410 AND trx_lines.REQUEST_ID = g_conc_request_id;
1411
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 g_retcode :=2;
1415 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Extract_AR_Trx_data while updating tax lines GDF';
1416 debug_message('Exception in Extract_AR_Trx_data while updating tax lines GDF information :'||SQLERRM);
1417 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1418 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Extract_AR_Trx_data while updating tax lines GDF');
1419 END IF;
1420 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1421
1422 return;
1423 End;
1424
1425
1426
1427 -- Update GL Date. GL Date should be transaction's header GL_DATE. below logic derived from RA_CUSTOMER_TRX_PARTIAL_V
1428 -- As this view is the source for AR Transaction's header block in Transaction Workbench.
1429 BEGIN
1430
1431 UPDATE je_it_trx_lines_all trx_lines
1432 SET trx_lines.trx_gl_date = (SELECT gl_date
1433 FROM ra_cust_trx_line_gl_dist_all dist
1434 WHERE dist.customer_trx_id = trx_lines.trx_id
1435 AND dist.account_class = 'REC'
1436 AND dist.latest_rec_flag = 'Y')
1437 WHERE trx_lines.application_id = 222
1438 AND trx_lines.REQUEST_ID = g_conc_request_id;
1439
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 g_retcode :=2;
1443 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Extract_AR_Trx_data, While updating GL Date';
1444 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1445 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Extract_AR_Trx_data, While updating GL Date');
1446 END IF;
1447 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1448 return;
1449 END;
1450
1451 debug_message('After update1 in Extract_AR_Trx_data');
1452
1453
1454
1455 -- Update the record types.
1456 -- country='IT' and vat_reg_num is not null: For cm/dm, record_Type is 4 and for other trx types record type is 2
1457 -- country='IT' and vat_reg_num is null : I think cm/dm doesn't exist.even if exists leave them as it shouldn't be shown in 4th or 5th record type. For other trxs, record type is 1
1458 -- country <> 'IT' then for cm/dm, record type is 5. For other trx, record type is 3
1459 BEGIN
1460
1461 UPDATE je_it_trx_lines_all
1462 SET record_type = DECODE(country,'IT',NVL2(vat_registration_num,2,1),3)
1463 WHERE application_id = 222
1464 AND request_id = g_conc_request_id
1465 AND trx_type <> 'CM'
1466 AND trx_type <> 'DM'
1467 AND adj_inv_flag = 'N';
1468
1469 UPDATE je_it_trx_lines_all a
1470 SET record_type = DECODE(country,'IT',NVL2(vat_registration_num,2,1),3)
1471 WHERE application_id = 222
1472 AND request_id = g_conc_request_id
1473 AND (a.trx_type = 'CM' OR a.trx_type = 'DM' OR a.adj_inv_flag = 'Y')
1474 AND a.orig_trx_id IS NOT NULL
1475 AND exists (SELECT 1
1476 FROM je_it_trx_lines_all b
1477 WHERE b.request_id = g_conc_request_id
1478 AND b.application_id = 222
1479 AND b.trx_id = a.orig_trx_id);
1480
1481 UPDATE je_it_trx_lines_all a
1482 SET a.record_type = DECODE(a.country,'IT',4,5)
1483 WHERE a.application_id = 222
1484 AND a.request_id = g_conc_request_id
1485 AND (a.trx_type = 'CM' OR a.trx_type = 'DM' OR a.adj_inv_flag = 'Y')
1486 AND (a.orig_trx_id IS NULL
1487 OR EXISTS (SELECT 1
1488 FROM je_it_trx_lines_all b,
1489 je_it_above_thr_hdr_all h
1490 WHERE h.year_of_declaration in (g_year-1,g_year-2)
1491 AND h.request_id = b.request_id
1492 AND b.application_id = 222
1493 AND b.trx_id = a.orig_trx_id
1494 AND b.is_above_threshold = 'Y'));
1495
1496 debug_message('After record type update in Extract_AR_Trx_data');
1497 EXCEPTION
1498 WHEN OTHERS THEN
1499 g_retcode :=2;
1500 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Extract_AR_Trx_data, While updating record type';
1501 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1502 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Extract_AR_Trx_data, While updating record type');
1503 END IF;
1504 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1505 return;
1506 END;
1507 debug_message('End of Extract_AR_Trx_data');
1508 END Extract_AR_Trx_data;
1509
1510
1511 PROCEDURE CHECK_PAYMENT_MODE_ERROR IS
1512 --this proc will update the PAYMENT_MODE_ERR_FLAG column if the invoice (or) Contractor has multiple payment modes.
1513 l_api_name VARCHAR2(50) := 'Check_Payment_mode_error';
1514 BEGIN
1515
1516 --Check if a transaction has multiple payment mode values. If it has then update the PAYMENT_MODE_ERR_FLAG with 'Y'
1517 debug_message('Start of CHECK_PAYMENT_MODE_ERROR');
1518 BEGIN
1519
1520 UPDATE je_it_trx_lines_all
1521 SET PAYMENT_MODE_ERR_FLAG = 'Y'
1522 WHERE (application_id,trx_id) IN (SELECT application_id,trx_id
1523 FROM je_it_trx_lines_all
1524 WHERE request_id = g_conc_request_id
1525 AND trx_line_type not in ('REC_TAX','NONREC_TAX','TAX') -- Tax line GDF are not considered in R12
1526 GROUP BY application_id,trx_id
1527 HAVING COUNT(DISTINCT payment_mode) > 1)
1528 AND request_id = g_conc_request_id;
1529
1530
1531 -- IF the original transaction is inconsistent (having different payment modes), then the
1532 -- credit/debit memos which got applied on that orginal transaction should considered as
1533 -- inconsistent application. (As the application is not valid)
1534
1535 UPDATE je_it_trx_lines_all a
1536 SET inconst_cm_dm_appl_flag = 'Y'
1537 WHERE request_id = g_conc_request_id
1538 AND exists (SELECT 1
1539 FROM je_it_trx_lines_all b
1540 WHERE b.request_id = g_conc_request_id
1541 AND b.trx_id = a.orig_trx_id
1542 AND b.payment_mode_err_flag = 'Y');
1543
1544
1545 EXCEPTION
1546 WHEN OTHERS THEN
1547 g_retcode :=2;
1548 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.check_payment_mode_error';
1549 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1550 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE check_payment_mode_error for Invoice check');
1551 END IF;
1552 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1553 return;
1554 END;
1555
1556
1557 --Check if a Contract Identification has multiple payment mode values. If it has update the PAYMENT_MODE_ERR_FLAG with 'Y'
1558
1559 BEGIN
1560
1561 UPDATE je_it_trx_lines_all
1562 SET PAYMENT_MODE_ERR_FLAG = 'Y'
1563 WHERE (application_id,party_id,contract_identification) IN (SELECT application_id,party_id,contract_identification
1564 FROM je_it_trx_lines_all
1565 WHERE request_id = g_conc_request_id
1566 AND trx_line_type not in ('REC_TAX','NONREC_TAX','TAX') -- Tax line GDF are not considered in R12
1567 GROUP BY application_id,party_id,contract_identification
1568 HAVING COUNT(DISTINCT payment_mode) > 1)
1569 AND request_id = g_conc_request_id;
1570
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573 g_retcode :=2;
1574 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.check_payment_mode_error';
1575 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1576 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE check_payment_mode_error for Contract check');
1577 END IF;
1578 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1579 return;
1580 END;
1581 debug_message('End of CHECK_PAYMENT_MODE_ERROR');
1582 END CHECK_PAYMENT_MODE_ERROR;
1583
1584 PROCEDURE CHECK_PARTIAL_ADJ_INV IS
1585 --this proc will update the PARTIAL_ADJ_INV_FLAG column if only some of the invoice lines were marked as adjustment invoice.
1586 --Ideally, all invoice lines should have 'Invoice adjustment flag'(GDF segment) marked as 'Yes' or 'No'. Otherwise report it in error section.
1587 l_api_name VARCHAR2(50) := 'CHECK_PARTIAL_ADJ_INV';
1588 BEGIN
1589
1590 --Check if a transaction has multiple payment mode values. If it has then update the PAYMENT_MODE_ERR_FLAG with 'Y'
1591 debug_message('Start of CHECK_PARTIAL_ADJ_INV');
1592 BEGIN
1593
1594 UPDATE je_it_trx_lines_all
1595 SET partial_adj_inv_flag = 'Y'
1596 WHERE (application_id,trx_id) IN (SELECT application_id,trx_id
1597 FROM je_it_trx_lines_all
1598 WHERE request_id = g_conc_request_id
1599 AND trx_line_type not in ('REC_TAX','NONREC_TAX','TAX') -- Tax line GDF are not considered in R12
1600 GROUP BY application_id,trx_id
1601 HAVING COUNT(DISTINCT adj_inv_flag) > 1)
1602 AND request_id = g_conc_request_id;
1603
1604 EXCEPTION
1605 WHEN OTHERS THEN
1606 g_retcode :=2;
1607 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.check_payment_mode_error';
1608 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1609 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE check_payment_mode_error for Invoice check');
1610 END IF;
1611 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1612 return;
1613 END;
1614
1615 debug_message('End of CHECK_PARTIAL_ADJ_INV');
1616 END CHECK_PARTIAL_ADJ_INV;
1617
1618 PROCEDURE Check_missing_invoice IS
1619 l_api_name VARCHAR2(50) := 'Check_missing_invoice';
1620
1621 BEGIN
1622 debug_message('Start of Check_missing_invoice');
1623 /* --Changed for performace
1624 UPDATE je_it_trx_lines_all
1625 SET ORIG_TRX_MISSING_FLAG = 'Y'
1626 WHERE (application_id,ORIG_TRX_ID) not in (SELECT application_id,trx_id
1627 FROM je_it_trx_lines_all
1628 WHERE request_id = g_conc_request_id
1629 )
1630 AND (application_id,ORIG_TRX_ID) not in (SELECT application_id,trx_id
1631 FROM je_it_trx_lines_all trx_lines,
1632 je_it_above_thr_hdr_all hdr
1633 WHERE trx_lines.request_id = hdr.request_id
1634 AND hdr.year_of_declaration in (g_year-1,g_year-2)
1635 AND is_above_threshold = 'Y')
1636 AND request_id = g_conc_request_id;
1637 */
1638
1639 --Changed for Performance -Start
1640 UPDATE je_it_trx_lines_all
1641 SET orig_trx_missing_flag = 'Y'
1642 WHERE rowid NOT IN
1643 (SELECT t2.rowid
1644 FROM je_it_trx_lines_all t1,
1645 je_it_trx_lines_all t2
1646 WHERE t1.request_id = g_conc_request_id
1647 AND t2.request_id = g_conc_request_id
1648 AND t2.application_id = t1.application_id
1649 AND t2.orig_trx_id = t1.trx_id
1650 AND t2.orig_trx_id IS NOT NULL
1651 UNION
1652 SELECT t2.rowid
1653 FROM je_it_trx_lines_all t2,
1654 je_it_trx_lines_all trx_lines,
1655 je_it_above_thr_hdr_all hdr
1656 WHERE trx_lines.request_id = hdr.request_id
1657 AND hdr.year_of_declaration IN(g_year -1,g_year-2)
1658 AND trx_lines.is_above_threshold = 'Y'
1659 AND t2.request_id = g_conc_request_id
1660 AND t2.application_id = trx_lines.application_id
1661 AND t2.orig_trx_id = trx_lines.trx_id
1662 AND t2.orig_trx_id IS NOT NULL)
1663 AND orig_trx_id IS NOT NULL;
1664 --Changed for Performance -Start
1665
1666 debug_message('End of Check_missing_invoice');
1667 EXCEPTION
1668 WHEN OTHERS THEN
1669 g_retcode :=2;
1670 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.check_missing_invoice';
1671 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1672 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE check_missing_invoice');
1673 END IF;
1674 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1675 return;
1676 END Check_missing_invoice;
1677
1678
1679 PROCEDURE Update_Above_Threshold_Lines IS
1680 l_api_name VARCHAR2(50) := 'Update_above_threshold_lines';
1681 BEGIN
1682
1683 debug_message('Start of Update_Above_Threshold_Lines');
1684
1685 UPDATE je_it_trx_lines_all a -- For normal grouping
1686 SET a.is_above_threshold = 'Y'
1687 WHERE a.request_id = g_conc_request_id
1688 AND (a.application_id,NVL(a.ORIG_TRX_ID,a.trx_id)) IN (SELECT b.application_id,
1689 NVL(b.ORIG_TRX_ID,b.trx_id)
1690 FROM je_it_trx_lines_all b
1691 WHERE b.request_id = g_conc_request_id
1692 AND b.below_threshold_flag = 'N'
1693 AND NVL(b.report_exclusion_flag,'N') = 'N'
1694 AND NVL(b.inconst_cm_dm_appl_flag,'N') = 'N'
1695 AND NVL(b.payment_mode_err_flag,'N') = 'N'
1696 AND NVL(b.orig_trx_missing_flag,'N') = 'N'
1697 AND NVL(b.partial_adj_inv_flag,'N') = 'N'
1698 AND b.trx_id in (select c.trx_id
1699 from je_it_trx_lines_all c
1700 where c.request_id = g_conc_request_id
1701 and c.contract_identification is null)
1702 AND b.record_type in(1,2,3)
1703 GROUP BY b.application_id,
1704 NVL(b.ORIG_TRX_ID,b.trx_id),
1705 b.vat_registration_num
1706 HAVING ((b.vat_registration_num IS NOT NULL AND SUM(b.assessable_amt) >= g_b2b_threshold)
1707 OR (b.vat_registration_num IS NULL AND SUM(b.assessable_amt + b.vat_amt) >= g_b2c_threshold )))
1708 AND a.below_threshold_flag = 'N'
1709 AND NVL(a.report_exclusion_flag,'N') = 'N'
1710 AND NVL(a.inconst_cm_dm_appl_flag,'N') = 'N'
1711 AND NVL(a.payment_mode_err_flag,'N') = 'N'
1712 AND NVL(a.orig_trx_missing_flag,'N') = 'N'
1713 AND NVL(a.partial_adj_inv_flag,'N') = 'N'
1714 AND a.record_type in (1,2,3);
1715
1716
1717 UPDATE je_it_trx_lines_all a -- FOR Contractor grouping.
1718 SET a.is_above_threshold = 'Y'
1719 WHERE a.request_id = g_conc_request_id
1720 AND (a.application_id,a.party_id,a.contract_identification)
1721 IN (SELECT b.application_id,
1722 b.party_id,
1723 b.contract_identification
1724 FROM je_it_trx_lines_all b
1725 WHERE b.request_id = g_conc_request_id
1726 AND b.below_threshold_flag = 'N'
1727 AND NVL(b.report_exclusion_flag,'N') = 'N'
1728 AND NVL(b.inconst_cm_dm_appl_flag,'N') = 'N'
1729 AND NVL(b.payment_mode_err_flag,'N') = 'N'
1730 AND NVL(b.orig_trx_missing_flag,'N') = 'N'
1731 AND NVL(b.partial_adj_inv_flag,'N') = 'N'
1732 AND b.contract_identification IS NOT NULL
1733 AND b.record_type in (1,2,3)
1734 GROUP BY b.application_id,
1735 b.party_id,
1736 b.contract_identification,
1737 b.vat_registration_num
1738 HAVING ((b.vat_registration_num IS NOT NULL AND SUM(b.assessable_amt) >= g_b2b_threshold)
1739 OR (b.vat_registration_num IS NULL AND SUM(b.assessable_amt + b.vat_amt) >= g_b2c_threshold )))
1740 AND a.below_threshold_flag = 'N'
1741 AND NVL(a.report_exclusion_flag,'N') = 'N'
1742 AND NVL(a.inconst_cm_dm_appl_flag,'N') = 'N'
1743 AND NVL(a.payment_mode_err_flag,'N') = 'N'
1744 AND NVL(a.orig_trx_missing_flag,'N') = 'N'
1745 AND NVL(a.partial_adj_inv_flag,'N') = 'N'
1746 AND a.contract_identification IS NOT NULL
1747 AND a.record_type in (1,2,3);
1748
1749
1750 UPDATE je_it_trx_lines_all trx_lines -- if below_threshold flag is 'Y' then that lines has to be reported without checking against threshold
1751 SET is_above_threshold = 'Y'
1752 WHERE trx_lines.request_id = g_conc_request_id
1753 AND NVL(trx_lines.below_threshold_flag,'N') = 'Y'
1754 AND NVL(trx_lines.report_exclusion_flag,'N') = 'N'
1755 AND NVL(trx_lines.inconst_cm_dm_appl_flag,'N') = 'N'
1756 AND NVL(trx_lines.payment_mode_err_flag,'N') = 'N'
1757 AND NVL(trx_lines.orig_trx_missing_flag,'N') = 'N'
1758 AND NVL(trx_lines.partial_adj_inv_flag,'N') = 'N';
1759
1760 --record type 4,5 contains only the variable notes which are not applied
1761 --and variation notes applied but the original invoices declared in previous declaration year
1762 --variation notes with above two scenarios has to be reported. so update is_threshold_flag to 'Y'
1763
1764 UPDATE je_it_trx_lines_all trx_lines
1765 SET is_above_threshold = 'Y'
1766 WHERE trx_lines.request_id = g_conc_request_id
1767 AND trx_lines.record_type in (4,5)
1768 AND NVL(trx_lines.report_exclusion_flag,'N') = 'N'
1769 AND NVL(trx_lines.inconst_cm_dm_appl_flag,'N') = 'N'
1770 AND NVL(trx_lines.payment_mode_err_flag,'N') = 'N'
1771 AND NVL(trx_lines.orig_trx_missing_flag,'N') = 'N'
1772 AND NVL(trx_lines.partial_adj_inv_flag,'N') = 'N'
1773 AND (trx_lines.orig_trx_id IS NULL
1774 OR NOT EXISTS (SELECT 1 --to filter credit memo lines already reported.
1775 FROM je_it_trx_lines_all a,
1776 je_it_above_thr_hdr_all b
1777 WHERE a.request_id = b.request_id
1778 AND b.year_of_declaration in (g_year-1,g_year-2)
1779 AND a.trx_id = trx_lines.trx_id
1780 AND nvl(a.trx_line_id,0) = nvl(trx_lines.trx_line_id,0)
1781 AND nvl(a.trx_line_num,0) = nvl(trx_lines.trx_line_num,0) ));
1782
1783 debug_message('End of Update_Above_Threshold_Lines');
1784
1785 END Update_Above_Threshold_Lines;
1786
1787 PROCEDURE Insert_Above_Threshold_Lines IS
1788 l_api_name VARCHAR2(50) := 'Insert_Above_Threshold_Lines';
1789 l_std_inv_cnt NUMBER :=0;
1790 l_prog_cnt NUMBER :=0;
1791 l_seq_cnt NUMBER :=0;
1792 i number := 1;
1793 cursor update_seq_no_cur IS
1794 SELECT record_type,
1795 application_id,
1796 party_name,
1797 party_id,
1798 trx_gl_date,
1799 trx_id,
1800 contract_identification,
1801 below_threshold_flag
1802 FROM je_it_trx_above_thr_all
1803 WHERE request_id = g_conc_request_id
1804 ORDER BY record_type,
1805 application_id desc,
1806 party_name,
1807 party_id,
1808 trx_gl_date,
1809 trx_id,
1810 contract_identification,
1811 below_threshold_flag;
1812
1813 BEGIN
1814 debug_message('Start of Insert_Above_Threshold_Lines');
1815 BEGIN
1816 INSERT INTO je_it_trx_above_thr_all
1817 (
1818 request_id ,
1819 vat_reporting_entity_id,
1820 org_id,
1821 application_id,
1822 record_type,
1823 party_id,
1824 party_name,
1825 vat_registration_num,
1826 tax_payer_id,
1827 country,
1828 trx_id,
1829 -- trx_num,
1830 -- trx_date,
1831 -- trx_gl_date,
1832 -- payment_method,
1833 -- trx_accounting_status,
1834 assessable_amt,
1835 vat_amt,
1836 payment_mode,
1837 below_threshold_flag,
1838 contract_identification,
1839 party_type,
1840 indv_party_last_name,
1841 indv_party_first_name,
1842 indv_party_dob,
1843 indv_party_city,
1844 indv_party_province,
1845 company_city,
1846 company_address,
1847 last_update_date,
1848 last_updated_by,
1849 last_update_login,
1850 creation_date,
1851 created_by
1852 )
1853 SELECT
1854 g_conc_request_id ,
1855 g_vat_reporting_entity_id,
1856 g_org_id,
1857 application_id,
1858 record_type,
1859 party_id,
1860 party_name,
1861 vat_registration_num,
1862 tax_payer_id,
1863 country,
1864 trx_id,
1865 -- trx_num,
1866 -- trx_date,
1867 -- trx_gl_date,
1868 -- payment_method,
1869 -- trx_accounting_status,
1870 assessable_amt,
1871 vat_amt,
1872 payment_mode,
1873 below_threshold_flag,
1874 contract_identification,
1875 party_type,
1876 indv_party_last_name,
1877 indv_party_first_name,
1878 indv_party_dob,
1879 indv_party_city,
1880 indv_party_province,
1881 company_city,
1882 company_address,
1883 g_last_update_date,
1884 g_last_updated_by,
1885 g_last_update_login,
1886 g_creation_date,
1887 g_created_by
1888 FROM
1889 (SELECT b.application_id ,
1890 b.record_type ,
1891 b.party_id ,
1892 b.party_name ,
1893 b.country ,
1894 b.vat_registration_num ,
1895 b.tax_payer_id ,
1896 NVL(b.orig_trx_id, b.trx_id) trx_id ,
1897 ROUND(nvl2(b.vat_registration_num, SUM(b.assessable_amt), SUM(b.assessable_amt + b.vat_amt))) assessable_amt,
1898 ROUND(nvl2(b.vat_registration_num, SUM(b.vat_amt), 0)) vat_amt ,
1899 b.payment_mode ,
1900 b.below_threshold_flag ,
1901 NULL contract_identification,
1902 MAX(b.party_type) party_type ,
1903 MAX(b.indv_party_last_name) indv_party_last_name ,
1904 MAX(b.indv_party_first_name) indv_party_first_name ,
1905 MAX(b.indv_party_dob) indv_party_dob ,
1906 MAX(b.indv_party_city) indv_party_city ,
1907 MAX(b.indv_party_province) indv_party_province ,
1908 MAX(b.company_city) company_city ,
1909 MAX(b.company_address) company_address
1910 FROM je_it_trx_lines_all b
1911 WHERE b.request_id = g_conc_request_id
1912 AND NVL(b.is_above_threshold, 'N') = 'Y'
1913 AND b.record_type IN(1, 2, 3)
1914 AND EXISTS
1915 (SELECT
1916 /*+ UNNEST INDEX(a XXFN_JE_IT_TRX_LINES_N101) */
1917 'exists'
1918 FROM je_it_trx_lines_all a
1919 WHERE a.request_id = g_conc_request_id
1920 AND a.trx_id = b.trx_id
1921 AND a.record_type IN(1, 2, 3)
1922 AND a.contract_identification IS NULL
1923 )
1924 GROUP BY b.application_id ,
1925 b.record_type ,
1926 b.party_id ,
1927 b.party_name ,
1928 b.country ,
1929 b.vat_registration_num ,
1930 b.tax_payer_id ,
1931 nvl(b.orig_trx_id, b.trx_id),
1932 b.payment_mode ,
1933 b.below_threshold_flag
1934
1935 UNION ALL
1936
1937 SELECT b.application_id ,
1938 b.record_type ,
1939 b.party_id ,
1940 b.party_name ,
1941 b.country ,
1942 b.vat_registration_num ,
1943 b.tax_payer_id ,
1944 NULL ,
1945 ROUND(nvl2(b.vat_registration_num, SUM(b.assessable_amt), SUM(b.assessable_amt + b.vat_amt))) assessable_amt,
1946 ROUND(nvl2(b.vat_registration_num, SUM(b.vat_amt), 0)) vat_amt ,
1947 b.payment_mode ,
1948 b.below_threshold_flag ,
1949 b.contract_identification ,
1950 MAX(b.party_type) party_type ,
1951 MAX(b.indv_party_last_name) indv_party_last_name ,
1952 MAX(b.indv_party_first_name) indv_party_first_name ,
1953 MAX(b.indv_party_dob) indv_party_dob ,
1954 MAX(b.indv_party_city) indv_party_city ,
1955 MAX(b.indv_party_province) indv_party_province ,
1956 MAX(b.company_city) company_city ,
1957 MAX(b.company_address) company_address
1958 FROM je_it_trx_lines_all b
1959 WHERE b.request_id = g_conc_request_id
1960 AND b.contract_identification IS NOT NULL
1961 AND NVL(b.is_above_threshold, 'N') = 'Y'
1962 AND b.record_type IN(1, 2, 3)
1963 AND NOT EXISTS
1964 (SELECT
1965 /*+ UNNEST INDEX(a XXFN_JE_IT_TRX_LINES_N101) */
1966 'exists'
1967 FROM je_it_trx_lines_all a
1968 WHERE a.request_id = g_conc_request_id
1969 AND a.trx_id = b.trx_id
1970 AND a.record_type IN(1, 2, 3)
1971 AND a.contract_identification IS NULL
1972 )
1973 GROUP BY b.application_id ,
1974 b.record_type ,
1975 b.party_id ,
1976 b.party_name ,
1977 b.country ,
1978 b.vat_registration_num ,
1979 b.tax_payer_id ,
1980 b.contract_identification,
1981 b.payment_mode ,
1982 b.below_threshold_flag
1983 );
1984 debug_message('In between of Insert_Above_Threshold_Lines');
1985 EXCEPTION
1986 WHEN OTHERS THEN
1987 g_retcode :=2;
1988 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
1989 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1990 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while inserting record type 1,2,3 data');
1991 END IF;
1992 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1993 return;
1994 END;
1995
1996 BEGIN -- For Contract Identification, update the trx_gl_date with the latest invoice gl_date
1997 debug_message('Update for trx_gl_date with the latest invoice gl_date');
1998 UPDATE je_it_trx_above_thr_all trx
1999 SET trx_gl_date = (SELECT MAX(trx_gl_date)
2000 FROM je_it_trx_lines_all trx_lines
2001 WHERE request_id = g_conc_request_id
2002 AND trx_lines.contract_identification = trx.contract_identification
2003 AND trx_lines.party_id = trx.party_id
2004 AND trx_lines.application_id = trx.application_id
2005 AND NVL(trx_lines.is_above_threshold,'N')='Y'
2006 AND trx_lines.record_type in (1,2,3))
2007 WHERE request_id = g_conc_request_id
2008 AND trx.contract_identification is not null
2009 AND trx.record_type in (1,2,3);
2010
2011 EXCEPTION
2012 WHEN OTHERS THEN
2013 g_retcode :=2;
2014 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
2015 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2016 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while updating the trx_gl_date for contract grouping');
2017 END IF;
2018 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2019 return;
2020 END;
2021
2022 BEGIN -- For Contract Identification, update the trx_number with the latest invoice number
2023 debug_message('Update the trx_number with the latest invoice number');
2024 UPDATE je_it_trx_above_thr_all trx
2025 SET trx_num = (SELECT MAX(trx_num)
2026 FROM je_it_trx_lines_all trx_lines
2027 WHERE request_id = g_conc_request_id
2028 AND trx_lines.contract_identification = trx.contract_identification
2029 AND trx_lines.party_id = trx.party_id
2030 AND trx_lines.application_id = trx.application_id
2031 AND trx_lines.trx_gl_date = trx.trx_gl_date
2032 AND NVL(trx_lines.is_above_threshold,'N')='Y'
2033 AND trx_lines.record_type in (1,2,3))
2034 WHERE request_id = g_conc_request_id
2035 AND trx.contract_identification is not null
2036 AND trx.record_type in (1,2,3);
2037
2038 EXCEPTION
2039 WHEN OTHERS THEN
2040 g_retcode :=2;
2041 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
2042 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2043 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while updating the trx_number for contract grouping');
2044 END IF;
2045 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2046 return;
2047 END;
2048
2049
2050
2051 -- Update trx_num,trx_date,trx_gl_date,trx_payment_method,trx_accounting_status for transactions fall
2052 -- under normal invoice grouping.
2053 debug_message('Update for normal invoice grouping');
2054 UPDATE je_it_trx_above_thr_all a
2055 SET (trx_num,trx_date,trx_gl_date,
2056 payment_method,trx_accounting_status) = (SELECT MAX(trx_num),MAX(trx_date),
2057 MAX(trx_gl_date),MAX(payment_method),
2058 MAX(trx_accounting_status)
2059 FROM je_it_trx_lines_all b
2060 WHERE request_id = g_conc_request_id
2061 AND contract_identification IS NULL
2062 AND b.trx_id = a.trx_id
2063 AND b.application_id=a.application_id )
2064 WHERE a.request_id = g_conc_request_id
2065 AND a.contract_identification IS NULL;
2066
2067
2068
2069 For rec IN update_seq_no_cur
2070 LOOP
2071 UPDATE je_it_trx_above_thr_all
2072 SET eft_progression_num = decode(mod(i,g_max_no_of_records),0,trunc(i/g_max_no_of_records),trunc(i/g_max_no_of_records)+1),
2073 eft_sequential_num =decode(mod(i,g_max_no_of_records),0,g_max_no_of_records,mod(i,g_max_no_of_records))
2074 WHERE request_id = g_conc_request_id
2075 AND record_type = rec.record_type
2076 AND application_id = rec.application_id
2077 AND party_id = rec.party_id
2078 AND party_name = rec.party_name
2079 AND nvl(trx_id,9999999999) = NVL(rec.trx_id,9999999999)
2080 AND trx_gl_date = rec.trx_gl_date
2081 AND nvl(contract_identification,'ZZZZZZ') = NVL(rec.contract_identification,'ZZZZZZ')
2082 AND below_threshold_flag = rec.below_threshold_flag;
2083
2084 i := i+1;
2085 END LOOP;
2086
2087 BEGIN -- get the number of records inserted into je_it_trx_above_threshold table for record types 1,2,3.
2088 -- Required to derive eft_transmission_number and sequential_number
2089
2090 SELECT MAX(eft_progression_num)
2091 INTO l_prog_cnt
2092 FROM je_it_trx_above_thr_all
2093 WHERE request_id = g_conc_request_id;
2094
2095 SELECT MAX(eft_sequential_num)
2096 INTO l_seq_cnt
2097 FROM je_it_trx_above_thr_all
2098 WHERE request_id = g_conc_request_id
2099 AND eft_progression_num = l_prog_cnt;
2100
2101 l_std_inv_cnt := ((l_prog_cnt-1) * g_max_no_of_records) + l_seq_cnt;
2102
2103 debug_message('l_std_inv_cnt :'||l_std_inv_cnt);
2104
2105 EXCEPTION
2106 WHEN OTHERS THEN
2107 g_retcode :=2;
2108 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
2109 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2110 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while calculating l_std_inv_cnt');
2111 END IF;
2112 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2113 return;
2114 END;
2115
2116 -- Insert record type 4,5 information.
2117 BEGIN
2118 INSERT INTO je_it_trx_above_thr_all (request_id,
2119 vat_reporting_entity_id,
2120 org_id,
2121 application_id,
2122 record_type,
2123 eft_progression_num,
2124 eft_sequential_num,
2125 country,
2126 party_id,
2127 party_name,
2128 vat_registration_num,
2129 tax_payer_id,
2130 trx_id,
2131 trx_num,
2132 trx_type,
2133 trx_date,
2134 trx_gl_date,
2135 payment_method,
2136 trx_accounting_status,
2137 assessable_amt,
2138 vat_amt,
2139 payment_mode,
2140 party_type,
2141 indv_party_last_name,
2142 indv_party_first_name,
2143 indv_party_dob,
2144 indv_party_city,
2145 indv_party_province,
2146 company_city,
2147 company_address,
2148 orig_trx_id,
2149 last_update_date,
2150 last_updated_by,
2151 last_update_login,
2152 creation_date,
2153 created_by)
2154 SELECT g_conc_request_id,
2155 g_vat_reporting_entity_id,
2156 g_org_id,
2157 application_id,
2158 record_type,
2159 decode(mod(l_std_inv_cnt+rownum,g_max_no_of_records),0,trunc((l_std_inv_cnt+ROWNUM)/g_max_no_of_records),trunc((l_std_inv_cnt+ROWNUM)/g_max_no_of_records)+1),
2160 decode(mod(l_std_inv_cnt+ROWNUM,g_max_no_of_records),0,g_max_no_of_records,mod(l_std_inv_cnt+rownum,g_max_no_of_records)),
2161 country,
2162 party_id,
2163 party_name,
2164 vat_registration_num,
2165 tax_payer_id,
2166 trx_id,
2167 trx_num,
2168 trx_type,
2169 trx_date,
2170 trx_gl_date,
2171 payment_method,
2172 trx_accounting_status,
2173 assessable_amt,
2174 vat_amt,
2175 payment_mode,
2176 party_type,
2177 indv_party_last_name,
2178 indv_party_first_name,
2179 indv_party_dob,
2180 indv_party_city,
2181 indv_party_province,
2182 company_city,
2183 company_address,
2184 orig_trx_id,
2185 g_last_update_date,
2186 g_last_updated_by,
2187 g_last_update_login,
2188 g_creation_date,
2189 g_created_by
2190 FROM (SELECT application_id,
2191 record_type,
2192 country,
2193 party_id,
2194 party_name,
2195 vat_registration_num,
2196 tax_payer_id,
2197 trx_id,
2198 trx_num,
2199 trx_type,
2200 MAX(trx_date) trx_date,
2201 MAX(trx_gl_date) trx_gl_date,
2202 MAX(payment_method) payment_method,
2203 MAX(trx_accounting_status) trx_accounting_status,
2204 SUM(assessable_amt) assessable_amt,
2205 SUM(vat_amt) vat_amt,
2206 MAX(payment_mode) payment_mode,
2207 MAX(party_type) party_type,
2208 MAX(indv_party_last_name) indv_party_last_name,
2209 MAX(indv_party_first_name) indv_party_first_name,
2210 MAX(indv_party_dob) indv_party_dob,
2211 MAX(indv_party_city) indv_party_city,
2212 MAX(indv_party_province) indv_party_province,
2213 MAX(company_city) company_city,
2214 MAX(company_address) company_address,
2215 orig_trx_id
2216 FROM je_it_trx_lines_all
2217 WHERE request_id = g_conc_request_id
2218 AND record_type in (4,5)
2219 AND NVL(is_above_threshold,'N') = 'Y'
2220 GROUP BY application_id,
2221 record_type,
2222 country,
2223 party_id,
2224 party_name,
2225 vat_registration_num,
2226 tax_payer_id,
2227 trx_id,
2228 trx_num,
2229 trx_type,
2230 orig_trx_id
2231 ORDER BY application_id desc,
2232 record_type,
2233 party_name,
2234 trx_gl_date,
2235 trx_id,
2236 orig_trx_id);
2237 debug_message('in between-2 of Insert_Above_Threshold_Lines');
2238 EXCEPTION
2239 WHEN OTHERS THEN
2240 g_retcode :=2;
2241 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
2242 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2243 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while inserting record type 4 and 5 data');
2244 END IF;
2245 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2246 return;
2247 END;
2248
2249 -- Update original trx_id information. original invoice information may exist in previous declaration years also.
2250 BEGIN
2251 UPDATE je_it_trx_above_thr_all trx
2252 SET (orig_trx_num,orig_trx_date,
2253 orig_trx_gl_date,orig_trx_acctg_status,
2254 orig_trx_payment_method) = (SELECT MAX(trx_num),NVL(MAX(trx_date),g_end_date),NVL(MAX(trx_gl_date),g_end_date),
2255 MAX(trx_lines.trx_accounting_status),MAX(payment_method)
2256 FROM je_it_trx_lines_all trx_lines,
2257 je_it_above_thr_hdr_all hdr
2258 WHERE trx_lines.request_id = hdr.request_id
2259 AND year_of_declaration in (g_year,g_year-1,g_year-2)
2260 AND trx_lines.application_id = trx.application_id
2261 AND trx_lines.trx_id = trx.orig_trx_id
2262 AND trx_lines.record_type in (1,2,3)
2263 AND NVL(trx_lines.is_above_threshold,'N')= 'Y')
2264 WHERE trx.REQUEST_ID = g_conc_request_id
2265 AND trx.record_type in (4,5) ;
2266
2267 debug_message('End of Insert_Above_Threshold_Lines');
2268
2269 EXCEPTION
2270 WHEN OTHERS THEN
2271 g_retcode :=2;
2272 g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
2273 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2274 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while updating cm/dm original invoice amounts.');
2275 END IF;
2276 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2277 return;
2278 END;
2279
2280 END Insert_Above_Threshold_Lines;
2281
2282 FUNCTION generate_next_eft (p_year VARCHAR2,
2283 p_vat_reporting_entity_id NUMBER,
2284 p_type_of_upload NUMBER,
2285 p_concurrent_request_id NUMBER,
2286 p_report_mode VARCHAR2,
2287 p_progression_number NUMBER) RETURN BOOLEAN IS
2288 l_exist NUMBER := 0;
2289 l_xml_layout BOOLEAN;
2290 l_appln_name VARCHAR2(30);
2291 l_con_cp_elec VARCHAR2(20);
2292 l_con_cp_elec_desc VARCHAR2(200);
2293 l_request_id NUMBER(15); -- bug 14063111
2294 BEGIN
2295
2296 l_appln_name := 'JE';
2297 l_con_cp_elec := 'JEITIATE_XMLP';
2298 l_con_cp_elec_desc := 'Italian Invoices Above Threshold - Electronic File';
2299 l_exist := 0;
2300
2301 SELECT count(1)
2302 INTO l_exist
2303 FROM je_it_trx_above_thr_all
2304 WHERE request_id = p_concurrent_request_id
2305 AND eft_progression_num = p_progression_number+1;
2306
2307 debug_message('l_exist :'|| l_exist);
2308 IF l_exist = 0 THEN
2309 IF p_report_mode = 'F' THEN
2310
2311 --BEGIN
2312 UPDATE je_it_above_thr_hdr_all
2313 SET report_mode = 'F'
2314 WHERE request_id = p_concurrent_request_id;
2315
2316 RETURN TRUE;
2317 /*EXCEPTION
2318 WHEN OTHERS THEN
2319 g_retcode :=2;
2320 g_errbuf :='Exception while updating the report mode';
2321 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2322 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception while updating the report mode in Generate_next_eft proc.');
2323 END IF;
2324 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2325 return;
2326 END;*/
2327 END IF;
2328 RETURN TRUE;
2329 ELSE
2330 debug_message('Calling next layout');
2331 Begin
2332 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,'JEITIATEF','en','US','ETEXT');
2333 l_request_id := fnd_request.submit_request(application => l_appln_name,
2334 program => l_con_cp_elec,
2335 description => l_con_cp_elec_desc,
2336 start_time => NULL,
2337 sub_request => FALSE,
2338 argument1 => p_vat_reporting_entity_id,
2339 argument2 => P_YEAR, --Fiscal year
2340 argument3 => p_type_of_upload,
2341 argument4 => p_concurrent_request_id,
2342 argument5 => P_REPORT_MODE,
2343 argument6 => p_progression_number+1,
2344 argument7 => CHR(0));
2345
2346 IF l_request_id = 0 THEN
2347 g_errbuf :='Exception in Generating EFT';
2348 -- RAISE e_request_submit_error;
2349 END IF;
2350 RETURN TRUE;
2351 exception
2352 when others then
2353 null;
2354 end;
2355 END IF;
2356
2357 END generate_next_eft;
2358
2359
2360 PROCEDURE debug_message(p_message_str VARCHAR2)
2361 IS
2362 BEGIN
2363 IF g_debug_flag = 'Y' THEN
2364 fnd_file.put_line(fnd_file.log,p_message_str);
2365 END IF;
2366 END debug_message;
2367
2368 END JE_IT_INVOICES_ABOVE_THRESHOLD;
2369