[Home] [Help]
PACKAGE BODY: APPS.JE_IT_LISTING_PKG
Source
1 PACKAGE BODY JE_IT_LISTING_PKG AS
2 /* $Header: jeitlstb.pls 120.6.12010000.3 2009/02/23 07:36:29 rahulkum ship $ */
3
4 -------------------------------------------------------------------------------
5 --Global Variables
6 -------------------------------------------------------------------------------
7 gv_ledger_id NUMBER(15);
8 gv_balancing_segment_value VARCHAR2(25);
9 gv_chart_of_accounts_id NUMBER(15);
10 gn_legal_entity_id varchar2(240);
11 gv_repent_trn varchar2(50);
12 gd_period_start_date date;
13 gd_period_end_date date;
14 gv_currency_code varchar2(60);
15 gv_vat_country_code varchar2(15);
16
17 g_lines_per_commit NUMBER;
18
19 gn_legal_vat_rep_entity_id NUMBER;
20 gn_ar_app_id NUMBER;
21 gn_ap_app_id NUMBER;
22 g_rec_per_eft NUMBER;
23
24
25
26 gt_party_id JE_IT_LISTING_PKG.tab_party_id;
27 gt_trx_type_code JE_IT_LISTING_PKG.tab_trx_type_code;
28 gt_trx_type_id JE_IT_LISTING_PKG.tab_trx_type_id;
29 gt_doc_seq_num JE_IT_LISTING_PKG.tab_doc_seq_num;
30 gt_doc_seq_val JE_IT_LISTING_PKG.tab_doc_seq_val;
31 gt_trx_date JE_IT_LISTING_PKG.tab_trx_date;
32 gt_trx_id JE_IT_LISTING_PKG.tab_trx_id;
33 gt_trx_num JE_IT_LISTING_PKG.tab_trx_num;
34 gt_trx_line_dist_id JE_IT_LISTING_PKG.tab_trx_dist_id;
35 gt_trx_tax_dist_id JE_IT_LISTING_PKG.tab_trx_dist_id;
36 gt_trx_line_type_code JE_IT_LISTING_PKG.tab_trx_line_type_code;
37 gt_trx_tax_line_type_code JE_IT_LISTING_PKG.tab_trx_line_type_code;
38 gt_trx_line_tax_rate_id JE_IT_LISTING_PKG.tab_trx_line_tax_rate_id;
39 gt_inv_tax_line_amount JE_IT_LISTING_PKG.tab_inv_line_amount;
40 gt_inv_line_amount JE_IT_LISTING_PKG.tab_inv_line_amount;
41 gt_inv_tax_line_amount_cm JE_IT_LISTING_PKG.tab_inv_line_amount;
42 gt_inv_line_amount_cm JE_IT_LISTING_PKG.tab_inv_line_amount;
43 gt_trx_type JE_IT_LISTING_PKG.tab_inv_type;
44 gt_party_vat_reg_num JE_IT_LISTING_PKG.tab_party_vat_reg_num;
45 gt_party_fiscal_id_num JE_IT_LISTING_PKG.tab_party_fiscal_id_num;
46
47 g_created_by NUMBER(15);
48 g_creation_date DATE;
49 g_last_updated_by NUMBER(15);
50 g_last_update_date DATE;
51 g_last_update_login NUMBER(15);
52
53 g_debug_flag VARCHAR2(1);
54 g_error_buffer VARCHAR2(200);
55 g_errbuf VARCHAR2(200);
56 g_retcode NUMBER;
57 g_current_runtime_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
58 g_level_statement CONSTANT NUMBER := fnd_log.level_statement;
59 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
60 g_level_event CONSTANT NUMBER := fnd_log.level_event;
61 g_level_exception CONSTANT NUMBER := fnd_log.level_exception;
62 g_level_error CONSTANT NUMBER := fnd_log.level_error;
63 g_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
64
65 --------------------------------------------------------------------------------
66 --Private Methods Declaration
67 --------------------------------------------------------------------------------
68
69 PROCEDURE Fetch_trx_data_ap(
70 p_vat_reporting_entity_id IN NUMBER,
71 p_year_of_declaration IN NUMBER,
72 p_vat_reg IN VARCHAR2);
73
74 PROCEDURE Fetch_trx_data_ar(
75 p_vat_reporting_entity_id IN NUMBER,
76 p_year_of_declaration IN NUMBER,
77 p_vat_reg IN VARCHAR2);
78
79 PROCEDURE Insert_tax_data(
80 p_vat_reporting_entity_id IN NUMBER,
81 p_year_of_declaration IN NUMBER,
82 p_app_id IN NUMBER);
83
84 PROCEDURE Init_gt_variables;
85
86 PROCEDURE Generate_trx_headers(
87 p_vat_reporting_entity_id IN NUMBER,
88 p_year_of_declaration IN NUMBER,
89 p_cust_sort_col IN VARCHAR2,
90 p_vend_sort_col IN VARCHAR2,
91 p_group_parties_flag IN VARCHAR2);
92
93 PROCEDURE Initialize_proc_var(
94 p_vat_reporting_entity_id IN NUMBER,
95 p_year_of_declaration IN NUMBER);
96
97
98
99
100 --------------------------------------------------------------------------------
101 --Public Methods
102 --------------------------------------------------------------------------------
103 /*===========================================================================+
104 | PROCEDURE |
105 | Extract_Data() |
106 | |
107 | DESCRIPTION |
108 | This procedure |
109 | (1) Checks the if the setup information is freezed or not |
110 | (2) Purges the existing data using Purge_trx_data() if the procedure |
111 | is called for the same period again in preliminary mode. |
112 | (3) Fetches the AP and AR lines information and populates the |
113 | parties table using Fetch_trx_data_ap and |
114 | Fetch_trx_data_ar. |
115 | (4) Runs the report and generates EFT(optionally). |
116 | |
117 | SCOPE - Public |
118 | |
119 | NOTES |
120 | |
121 | MODIFICATION HISTORY |
122 | Date Author Description |
123 | ============ ============== ================================= |
124 | 11-Dec-2007 spasupun Initial Version. |
125 | 20-Feb-2009 rahulkum Bug:8274173 Added NVL for MAX(TRANSMISSION_NUM)|
126 +===========================================================================*/
127 PROCEDURE Extract_Data(
128 errbuf OUT NOCOPY VARCHAR2,
129 retcode OUT NOCOPY VARCHAR2,
130 P_VAT_REPORTING_ENTITY_ID IN NUMBER,
131 P_YEAR_OF_DECLARATION IN NUMBER,
132 P_REPORT_TYPE IN VARCHAR2,
133 P_DUMMY IN NUMBER,
134 P_VAT IN VARCHAR2,
135 P_REPORT_MODE IN VARCHAR2,
136 P_EFT IN VARCHAR2,
137 P_PARTY_LIMIT IN NUMBER,
138 P_CUST_SORT_COL IN VARCHAR2,
139 P_VEND_SORT_COL IN VARCHAR2,
140 P_GROUP_PARTIES_FLAG IN VARCHAR2
141 ) IS
142
143 l_setup_not_available EXCEPTION;
144 l_setup_not_frozen EXCEPTION;
145 l_final_already_run EXCEPTION;
146 l_prelim_not_run EXCEPTION;
147 l_final_not_run EXCEPTION;
148 e_request_submit_error EXCEPTION;
149
150 l_vat_registration_flag BOOLEAN;
151 l_gen_efile BOOLEAN;
152 l_request_id NUMBER;
153 l_appln_name VARCHAR2(10);
154 l_con_cp_list VARCHAR2(15);
155 l_con_cp_list_desc VARCHAR2(200);
156 l_con_cp_elec VARCHAR2(15);
157 l_con_cp_elec_desc VARCHAR2(200);
158 l_xml_layout BOOLEAN;
159 l_eft_count NUMBER;
160 p_status_code VARCHAR2(30) := NULL;
161 l_entity_identifier jg_zz_vat_rep_entities.entity_identifier%type;
162
163
164 CURSOR cur_status(P_VAT_REPORTING_ENTITY_ID NUMBER,P_YEAR_OF_DECLARATION NUMBER) IS
165 SELECT status_code
166 FROM je_it_list_hdr_all
167 WHERE vat_reporting_entity_id= P_VAT_REPORTING_ENTITY_ID
168 AND year_of_declaration = P_YEAR_OF_DECLARATION;
169
170 CURSOR cur_frozen(P_VAT_REPORTING_ENTITY_ID NUMBER,P_YEAR_OF_DECLARATION NUMBER) IS
171 SELECT freeze_indicator_flag
172 FROM je_it_setup_hdr_all
173 WHERE vat_reporting_entity_id= P_VAT_REPORTING_ENTITY_ID
174 AND year_of_declaration = P_YEAR_OF_DECLARATION;
175
176 CURSOR entity_identifier(P_VAT_REPORTING_ENTITY_ID NUMBER) IS
177 SELECT LEGAL.ENTITY_IDENTIFIER,LEGAL.VAT_REPORTING_ENTITY_ID
178 FROM jg_zz_vat_rep_entities LEGAL,
179 jg_zz_vat_rep_entities ACC
180 WHERE ACC.VAT_REPORTING_ENTITY_ID = P_VAT_REPORTING_ENTITY_ID
181 AND ((ACC.ENTITY_TYPE_CODE = 'ACCOUNTING'
182 AND ACC.MAPPING_VAT_REP_ENTITY_ID = LEGAL.VAT_REPORTING_ENTITY_ID)
183 OR
184 (ACC.ENTITY_TYPE_CODE = 'LEGAL'
185 AND ACC.VAT_REPORTING_ENTITY_ID = LEGAL.VAT_REPORTING_ENTITY_ID)
186 );
187
188 BEGIN
189
190 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
191 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Start PROCEDURE Extract_Data');
192 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data','Parameters are :');
193 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data',' P_VAT_REPORTING_ENTITY_ID ='||P_VAT_REPORTING_ENTITY_ID);
194 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data',' P_YEAR_OF_DECLARATION ='||P_YEAR_OF_DECLARATION);
195 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data',' P_REPORT_TYPE ='||P_REPORT_TYPE);
196 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data',' P_VAT ='||P_VAT);
197 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data',' P_REPORT_MODEL = '||P_REPORT_MODE);
198 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data',' P_EFT = '||P_EFT);
199 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data',' P_CUST_SORT_COL = '||P_CUST_SORT_COL);
200 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data',' P_VEND_SORT_COL = '||P_VEND_SORT_COL);
201 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Extract_Data',' P_GROUP_PARTIES_FLAG = '||P_GROUP_PARTIES_FLAG);
202 END IF;
203 g_retcode :=0;
204 l_appln_name := 'JE';
205 l_con_cp_list := 'JEITLSTR_XMLP';
206 l_con_cp_elec := 'JEITLSTE_XMLP';
207 l_con_cp_list_desc := 'Italian Annual Customer and Supplier Listing Report';
208 l_con_cp_elec_desc := 'Italian Annual Customer and Supplier Electronic Format Report';
209
210 l_eft_count := 0;
211 l_gen_efile :=TRUE;
212 g_rec_per_eft :=P_PARTY_LIMIT;
213
214
215 IF P_EFT = 'N' THEN
216 l_gen_efile :=FALSE;
217 END IF;
218
219 BEGIN
220 OPEN entity_identifier(P_VAT_REPORTING_ENTITY_ID);
221 FETCH entity_identifier INTO l_entity_identifier,gn_legal_vat_rep_entity_id;
222 CLOSE entity_identifier;
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 g_errbuf :='Exception in fetching legal vat reporting entity id';
227 g_retcode := 2;
228 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
229 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Exception in fetching legal vat reporting entity id');
230 END IF;
231 errbuf := g_errbuf;
232 retcode:= g_retcode;
233 RETURN;
234 END;
235
236 BEGIN
237 OPEN cur_frozen(gn_legal_vat_rep_entity_id,P_YEAR_OF_DECLARATION);
238 FETCH cur_frozen INTO p_status_code;
239 CLOSE cur_frozen;
240
241 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
242 IF p_status_code IS NULL THEN
243 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Not Setup vailable for the reporting entity and year:');
244 END IF;
245 END IF;
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 g_errbuf :='Exception in fetching Freeze Status';
250 g_retcode := 2;
251 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
252 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Exception in fetching cur_frozen - p_status_code');
253 END IF;
254 errbuf := g_errbuf;
255 retcode:= g_retcode;
256 RETURN;
257 END;
258
259 IF p_status_code IS NULL THEN
260 RAISE l_setup_not_available;
261 ELSIF p_status_code <> 'Y' THEN
262 RAISE l_setup_not_frozen;
263 END IF;
264
265 p_status_code := NULL;
266
267 BEGIN
268 OPEN cur_status(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION);
269 FETCH cur_status INTO p_status_code;
270 CLOSE cur_status;
271 EXCEPTION
272 WHEN OTHERS THEN
273 g_errbuf :='Exception in fetching Status Code';
274 g_retcode := 2;
275 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
276 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Exception in fetching cur_status - p_status_code');
277 END IF;
278 errbuf := g_errbuf;
279 retcode:= g_retcode;
280 RETURN;
281 END;
282
283 IF P_REPORT_TYPE = 'P' THEN
284 IF p_status_code IS NULL THEN
285 p_status_code := 'P';
286 ELSIF p_status_code='F' THEN
287 RAISE l_final_already_run;
288 END IF;
289 ELSIF P_REPORT_TYPE = 'F' THEN
290 IF p_status_code IS NULL THEN
291 RAISE l_prelim_not_run;
292 ELSIF p_status_code='F' THEN
293 RAISE l_final_already_run;
294 END IF;
295 ELSIF P_REPORT_TYPE = 'R' THEN
296 IF p_status_code IS NULL OR p_status_code = 'P' THEN
297 RAISE l_final_not_run;
298 END IF;
299 END IF;
300
301 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
302 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','p_status_code = '||p_status_code);
303 END IF;
304
305 IF P_REPORT_TYPE = 'P' THEN
306
307 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
308 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Calling PROCEDURE Purge_trx_data');
309 END IF;
310
311 Purge_trx_data(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION);
312
313 IF g_retcode = 2 THEN
314 errbuf := g_errbuf;
315 retcode:= g_retcode;
316 RETURN;
317 END IF;
318
319 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
320 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Calling PROCEDURE Initialize_proc_var');
321 END IF;
322
323 Initialize_proc_var(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION);
324
325 IF g_retcode = 2 THEN
326 errbuf := g_errbuf;
327 retcode:= g_retcode;
328 RETURN;
329 END IF;
330
331 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
332 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Calling PROCEDURE Fetch_trx_data_ap');
333 END IF;
334
335 Fetch_trx_data_ap(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION,P_VAT);
336
337 IF g_retcode = 2 THEN
338 errbuf := g_errbuf;
339 retcode:= g_retcode;
340 RETURN;
341 END IF;
342
343 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
344 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Calling PROCEDURE Fetch_trx_data_ar');
345 END IF;
346
347 Fetch_trx_data_ar(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION,P_VAT);
348
349 IF g_retcode = 2 THEN
350 errbuf := g_errbuf;
351 retcode:= g_retcode;
352 RETURN;
353 END IF;
354
355 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
356 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Calling PROCEDURE Generate_trx_headers');
357 END IF;
358
359 Generate_trx_headers(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION,P_CUST_SORT_COL,P_VEND_SORT_COL,P_GROUP_PARTIES_FLAG);
360
361 IF g_retcode = 2 THEN
362 errbuf := g_errbuf;
363 retcode:= g_retcode;
364 RETURN;
365 END IF;
366
367 ELSIF P_REPORT_TYPE = 'F' OR P_REPORT_TYPE = 'R' THEN
368 l_gen_efile :=TRUE;
369 END IF;
370
371 -- Code for Running the Concurrent Programs.
372
373 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,'JEITLSTR','en','US','PDF');
374
375 l_request_id := fnd_request.submit_request(application => l_appln_name,
376 program => l_con_cp_list,
377 description => l_con_cp_list_desc,
378 start_time => NULL,
379 sub_request => FALSE,
380 argument1 => P_VAT_REPORTING_ENTITY_ID,
381 argument2 => P_YEAR_OF_DECLARATION,
382 argument3 => P_REPORT_MODE,
383 argument4 => P_REPORT_TYPE,
384 argument5 => CHR(0));
385
386 IF l_request_id = 0 THEN
387 g_errbuf :='Exception in running the Report';
388 RAISE e_request_submit_error;
389 END IF;
390
391 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
392 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','l_request_id = '||l_request_id);
393 END IF;
394 retcode := 0; -- CP completed successfully
395
396 IF l_gen_efile THEN
397 BEGIN
398 SELECT NVL(MAX(TRANSMISSION_NUM),0) --Bug:8274173
399 INTO l_eft_count
400 FROM JE_IT_LIST_PARTIES_ALL
401 WHERE vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
402 AND year_of_declaration = P_YEAR_OF_DECLARATION;
403 EXCEPTION
404 WHEN OTHERS THEN
405 g_retcode :=1;
406 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
407 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Date','Exception in fetching l_eft_count');
408 END IF;
409 END;
410
411 IF g_retcode = 1 THEN
412 g_errbuf :='Exception in fetching l_eft_count';
413 RAISE e_request_submit_error;
414 END IF;
415
416 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
417 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','l_eft_count = '||l_eft_count);
418 END IF;
419
420 FOR i IN 1..l_eft_count LOOP
421 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,'JEITLSTE','en','US','ETEXT');
422 l_request_id := fnd_request.submit_request(application => l_appln_name,
423 program => l_con_cp_elec,
424 description => l_con_cp_elec_desc,
425 start_time => NULL,
426 sub_request => FALSE,
427 argument1 => P_VAT_REPORTING_ENTITY_ID,
428 argument2 => P_YEAR_OF_DECLARATION, --Fiscal year
429 argument3 => i, --elec prog number
430 argument4 => P_REPORT_TYPE,
431 argument5 => CHR(0));
432
433 IF l_request_id = 0 THEN
434 g_errbuf :='Exception in generating the EFT';
435 RAISE e_request_submit_error;
436 END IF;
437
438 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
439 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','l_request_id,'||i||' = '||l_request_id);
440 END IF;
441 END LOOP;
442 END IF;
443
444 IF P_REPORT_TYPE = 'F' THEN
445 Final_data(errbuf
446 ,retcode
447 ,p_vat_reporting_entity_id
448 ,p_year_of_declaration);
449 END IF;
450
451 IF g_retcode = 1 THEN
452 g_errbuf :='Exception in Finalizing Data';
453 RAISE e_request_submit_error;
454 END IF;
455
456
457 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
458 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','End PROCEDURE Extract_Data');
459 END IF;
460
461 EXCEPTION
462 WHEN l_setup_not_frozen THEN
463 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
464 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Exception in PROCEDURE Extract_Data - l_setup_not_frozen');
465 END IF;
466 FND_MESSAGE.SET_NAME('JE','JE_IT_SETUP_NOT_FROZEN');
467 FND_MESSAGE.SET_TOKEN('VAT_REP',l_entity_identifier);
468 FND_MESSAGE.SET_TOKEN('VAT_YEAR',P_YEAR_OF_DECLARATION);
469 errbuf :=FND_MESSAGE.get;
470 retcode := 2; -- Error
471 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
472 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
473 WHEN l_setup_not_available THEN
474 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
475 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Exception in PROCEDURE Extract_Data - Not setup_available');
476 END IF;
477 FND_MESSAGE.SET_NAME('JE','JE_IT_SETUP_NOT_AVAILABLE');
478 FND_MESSAGE.SET_TOKEN('VAT_REP',l_entity_identifier);
479 FND_MESSAGE.SET_TOKEN('VAT_YEAR',P_YEAR_OF_DECLARATION);
480 errbuf :=FND_MESSAGE.get;
481 retcode := 2; -- Error
482 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
483 WHEN l_final_already_run THEN
484 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
485 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Exception in PROCEDURE Extract_Data - l_final_already_run');
486 END IF;
487 FND_MESSAGE.SET_NAME('JE','JE_IT_FINAL_LISTING');
488 FND_MESSAGE.SET_TOKEN('VAT_REP',l_entity_identifier);
489 FND_MESSAGE.SET_TOKEN('VAT_YEAR',P_YEAR_OF_DECLARATION);
490 errbuf :=FND_MESSAGE.get;
491 retcode := 2; -- Error
492 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
493 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
494 WHEN l_prelim_not_run THEN
495 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
496 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Final_data','Exception in PROCEDURE Extract_Data - l_prelim_not_run ');
497 END IF;
498 FND_MESSAGE.SET_NAME('JE','JE_IT_PRELIMINARY_LISTING');
499 FND_MESSAGE.SET_TOKEN('VAT_REP',l_entity_identifier);
500 FND_MESSAGE.SET_TOKEN('VAT_YEAR',P_YEAR_OF_DECLARATION);
501 errbuf :=FND_MESSAGE.get;
502 retcode := 2; --Error
503 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
504 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
505 WHEN l_final_not_run THEN
506 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
507 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Final_data','Exception in PROCEDURE Extract_Data - l_final_not_run ');
508 END IF;
509 FND_MESSAGE.SET_NAME('JE','JE_IT_FINAL_NOT_RUN');
510 FND_MESSAGE.SET_TOKEN('VAT_REP',l_entity_identifier);
511 FND_MESSAGE.SET_TOKEN('VAT_YEAR',P_YEAR_OF_DECLARATION);
512 errbuf :=FND_MESSAGE.get;
513 retcode := 2; --Error
514 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
515 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
516 WHEN e_request_submit_error THEN
517 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
518 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Exception in PROCEDURE Extract_Data - e_request_submit_error');
519 END IF;
520 errbuf := g_errbuf;
521 retcode := 1; -- Warning
522 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
523 IF g_debug_flag = 'Y' THEN
524 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
525 END IF;
526 WHEN OTHERS THEN
527 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
528 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Extract_Data','Exception in PROCEDURE Extract_Data ');
529 END IF;
530 errbuf :='Unknown exception occured in JE_IT_LISTING_PKG.Extract_Data';
531 retcode := 2; -- Error
532 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
533 IF g_debug_flag = 'Y' THEN
534 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
535 END IF;
536 END Extract_Data;
537 --------------------------------------------------------------------------------
538 /*===========================================================================+
539 | PROCEDURE |
540 | Final_data() |
541 | |
542 | DESCRIPTION |
543 | This procedure |
544 | (1) Checks the if the setup information is freezed or not. |
545 | (2) Sets the STATUS_CODE to F in JE_IT_LIST_HDR if the preliminary |
546 | data is already extracted. |
547 | (3) Runs the report and generates EFT. |
548 | |
549 | SCOPE - Public |
550 | |
551 | NOTES |
552 | |
553 | MODIFICATION HISTORY |
554 | Date Author Description |
555 | ============ ============== ================================= |
556 | 13-Dec-2007 spasupun Initial Version. |
557 | |
558 +===========================================================================*/
559 PROCEDURE Final_data(
560 errbuf OUT NOCOPY VARCHAR2 ,
561 retcode OUT NOCOPY VARCHAR2,
562 P_VAT_REPORTING_ENTITY_ID IN NUMBER,
563 P_YEAR_OF_DECLARATION IN NUMBER) IS
564
565 l_prelim_not_run EXCEPTION;
566 l_setup_not_frozen EXCEPTION;
567 l_final_already_run EXCEPTION;
568 e_request_submit_error EXCEPTION;
569
570 p_status_code VARCHAR2(30);
571 l_gen_efile BOOLEAN;
572 l_request_id NUMBER;
573 l_appln_name VARCHAR2(10);
574 l_con_cp_list VARCHAR2(15);
575 l_con_cp_list_desc VARCHAR2(200);
576 l_con_cp_elec VARCHAR2(15);
577 l_con_cp_elec_desc VARCHAR2(200);
578 l_xml_layout BOOLEAN;
579 l_eft_count NUMBER;
580
581 CURSOR cur_status(p_vat_reporting_entity_id number,p_year_of_declaration number) IS
582 SELECT status_code
583 FROM je_it_list_hdr_all
584 WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
585 ANd year_of_declaration = p_year_of_declaration;
586
587 BEGIN
588 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
589 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Final_data','Start PROCEDURE Final_data');
590 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Final_data','Parameters are :');
591 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Final_data',' P_VAT_REPORTING_ENTITY_ID ='||P_VAT_REPORTING_ENTITY_ID);
592 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Final_data',' P_YEAR_OF_DECLARATION ='||P_YEAR_OF_DECLARATION);
593
594 END IF;
595
596
597 BEGIN
598 OPEN cur_status(p_vat_reporting_entity_id,p_year_of_declaration);
599 FETCH cur_status INTO p_status_code;
600 CLOSE cur_status;
601
602 EXCEPTION
603 WHEN OTHERS THEN
604 g_retcode:=2;
605 g_errbuf:='Exception in fetching Status Code';
606 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
607 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Final_data','Exception in fetching p_status_code for cur_status');
608 END IF;
609 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
610 IF g_debug_flag = 'Y' THEN
611 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
612 END IF;
613 END;
614
615 IF g_retcode = 2 THEN
616 retcode:=g_retcode;
617 errbuf:=g_errbuf;
618 RETURN;
619 END IF;
620
621 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
622 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Final_data','cur_status - p_status_code = '||p_status_code);
623 END IF;
624
625 IF p_status_code = 'P' THEN
626
627 UPDATE JE_IT_LIST_HDR_ALL
628 SET STATUS_CODE = 'F'
629 WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
630 AND year_of_declaration = p_year_of_declaration;
631
632 COMMIT;
633
634 ELSIF p_status_code = 'F' THEN
635
636 RAISE l_final_already_run;
637 ELSE
638 RAISE l_prelim_not_run;
639 END IF;
640
641 EXCEPTION
642 WHEN l_setup_not_frozen THEN
643 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
644 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Final_data','Exception in PROCEDURE Final_data - l_setup_not_frozen ');
645 END IF;
646 FND_MESSAGE.SET_NAME('JE','JE_IT_SETUP_NOT_FROZEN');
647 errbuf :=FND_MESSAGE.get;
648 retcode := 2; --Error
649 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
650 IF g_debug_flag = 'Y' THEN
651 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
652 END IF;
653 WHEN l_prelim_not_run THEN
654 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
655 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Final_data','Exception in PROCEDURE Final_data - l_prelim_not_run ');
656 END IF;
657 FND_MESSAGE.SET_NAME('JE','JE_IT_PRELIMINARY_LISTING');
658 errbuf :=FND_MESSAGE.get;
659 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
660 retcode := 2; --Error
661 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
662 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
663 WHEN l_final_already_run THEN
664 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
665 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Final_data','Exception in PROCEDURE Final_data - l_final_already_run ');
666 END IF;
667 errbuf :='Final mode is already run for this Fiscal Period.';
668 retcode := 2; --Error
669 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
670 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
671 WHEN OTHERS THEN
672 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
673 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Final_data','Exception in PROCEDURE Final_data');
674 END IF;
675 errbuf :='Unknown Exception Occured in the package JE_IT_LISTING_PKG in PROCEDURE Final_data';
676 retcode := 2; --Error
677 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
678 IF g_debug_flag = 'Y' THEN
679 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
680 END IF;
681 END Final_data;
682
683 --------------------------------------------------------------------------------
684 /*===========================================================================+
685 | PROCEDURE |
686 | Purge_trx_data() |
687 | |
688 | DESCRIPTION |
689 | This procedure deletes all the rows from JE_IT_LIST_LINES_ALL, |
690 | JE_IT_LIST_PARTIES_ALL , JE_IT_LIST_HDR_ALL for a given period. |
691 | |
692 | SCOPE - Public |
693 | |
694 | NOTES |
695 | |
696 | MODIFICATION HISTORY |
697 | Date Author Description |
698 | ============ ============== ================================= |
699 | 03-Oct-2007 HBALIJEP Initial Version. |
700 | |
701 +===========================================================================*/
702 PROCEDURE Purge_trx_data(p_vat_reporting_entity_id IN NUMBER,P_YEAR_OF_DECLARATION IN NUMBER) IS
703 BEGIN
704 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
705 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Start PROCEDURE Purge_trx_data');
706 END IF;
707 g_retcode :=0;
708
709 DELETE JE_IT_LIST_LINES_ALL
710 WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
711 AND year_of_declaration = p_year_of_declaration;
712
713 DELETE JE_IT_LIST_PARTIES_ALL
714 WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
715 AND year_of_declaration = p_year_of_declaration;
716
717 DELETE JE_IT_LIST_HDR_ALL
718 WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
719 AND year_of_declaration = p_year_of_declaration;
720
721 COMMIT;
722 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
723 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','End PROCEDURE Purge_trx_data');
724 END IF;
725 EXCEPTION
726 WHEN OTHERS THEN
727 g_retcode :=2;
728 g_errbuf :='Exception in JE_IT_LISTING_PKG.Purge_trx_data';
729 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
730 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Exception in PROCEDURE Purge_trx_data');
731 END IF;
732 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
733 IF g_debug_flag = 'Y' THEN
734 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
735 END IF;
736 END Purge_trx_data;
737 --------------------------------------------------------------------------------
738 --------------------------------------------------------------------------------
739 -- PRIVATE METHODS
740 --------------------------------------------------------------------------------
741 /*===========================================================================+
742 | PROCEDURE |
743 | Fetch_trx_data_ap |
744 | |
745 | DESCRIPTION |
746 | This procedure fetches the distribution lines from the AP |
747 | using bulk fetch and calls the procedure insert_tax_data |
748 | to insert data into the JE_IT_LIST_LINES table |
749 | Called from JE_IT_LISTING_PKG.Extract_Data() |
750 | |
751 | SCOPE - Private |
752 | |
753 | NOTES |
754 | |
755 | MODIFICATION HISTORY |
756 | Date Author Description |
757 | ============ ============== ================================= |
758 | 03-Oct-2007 HBALIJEP Initial Version. |
759 | |
760 +===========================================================================*/
761 PROCEDURE Fetch_trx_data_ap( P_VAT_REPORTING_ENTITY_ID IN NUMBER,
762 P_YEAR_OF_DECLARATION IN NUMBER,
763 P_VAT_REG IN VARCHAR2) IS
764
765 CURSOR trl_tax_data_csr
766 IS
767 SELECT ih.vendor_id , -- Supplier ID
768 ih.invoice_type_lookup_code, -- Invoice Type
769 NULL , -- Transaction Type ID - AR
770 ih.doc_sequence_id , -- Document sequence ID
771 ih.doc_sequence_value , -- Dcoument sequence value
772 ih.invoice_date , -- Invoice Date
773 ih.invoice_id , -- Invoice ID
774 ih.invoice_num , -- Invoice_Number
775 id.invoice_distribution_id , -- Invoive Tax Line Distribution ID
776 iditem.invoice_distribution_id , -- Invoive Item Line Distribution ID
777 il.line_type_lookup_code , -- Invoice Tax Line Type (allways TAX)
778 ilitem.line_type_lookup_code , -- Iteam Line - Line Type Lookup Code (always ITEM
779 il.tax_rate_id , -- Tax Rate ID
780 -- Tax Amount for other than Credit Memo Invoices
781 DECODE(ih.invoice_type_lookup_code,'CREDIT',0,
782 DECODE(ih.invoice_currency_code,gv_currency_code, id.amount, id.base_amount)) amount_tax,
783 -- Tax Amount for Credit Memo Invoices
784 DECODE(ih.invoice_type_lookup_code,'CREDIT',DECODE(ih.invoice_currency_code,gv_currency_code, id.amount, id.base_amount)
785 ,0) cm_amount_tax,
786 -- Item Line Amount for other than Credit Memo Invoices
787 DECODE(ih.invoice_type_lookup_code,'CREDIT',0,
788 DECODE(ih.invoice_currency_code,gv_currency_code, iditem.amount, iditem.base_amount)) amount_item,
789 -- Item Line Amount for Credit Memo Invoices
790 DECODE(ih.invoice_type_lookup_code,'CREDIT',DECODE(ih.invoice_currency_code,gv_currency_code, iditem.amount, iditem.base_amount)
791 ,0) cm_amount_item
792 FROM ap_invoices_all ih ,
793 ap_invoice_lines_all il ,
794 ap_invoice_distributions_all id ,
795 ap_invoice_lines_all ilitem ,
796 ap_invoice_distributions_all iditem ,
797 ap_suppliers pv ,
798 ap_supplier_sites_all pvs ,
799 jg_zz_vat_rep_entities repent ,
800 zx_rates_b zxrates ,
801 zx_taxes_b zxtaxes ,
802 zx_report_codes_assoc zxass ,
803 (SELECT distinct person_id
804 ,national_identifier
805 FROM per_all_people_f
806 WHERE nvl(effective_end_date,sysdate) >= sysdate ) papf
807 WHERE repent.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
808 AND ( ( repent.entity_type_code = 'LEGAL'
809 AND ih.legal_entity_id = gn_legal_entity_id )
810 OR(repent.entity_type_code = 'ACCOUNTING'
811 AND repent.entity_level_code = 'LEDGER'
812 AND ih.set_of_books_id = gv_ledger_id)
813 OR(repent.entity_type_code = 'ACCOUNTING'
814 AND repent.entity_level_code = 'BSV'
815 AND ih.set_of_books_id = gv_ledger_id
816 AND get_bsv(id.dist_code_combination_id) = gv_balancing_segment_value )
817 )
818 AND ih.invoice_id = il.invoice_id
819 AND ih.invoice_id = id.invoice_id
820 AND il.line_number = id.invoice_line_number
821 AND id.posted_flag IN ('P', 'Y')
822 AND il.line_type_lookup_code = 'TAX'
823 AND ilitem.line_type_lookup_code = 'ITEM'
824 AND ih.invoice_id = ilitem.invoice_id
825 AND ih.invoice_id = iditem.invoice_id
826 AND ilitem.line_number = iditem.invoice_line_number
827 AND id.charge_applicable_to_dist_id = iditem.invoice_distribution_id
828 --In Case of Credit Memo Transaction, The following logic check credit memos lines issued
829 -- during the year but applied to invoices issued in the previous years.
830 AND ( ( ih.invoice_type_lookup_code <> 'CREDIT')
831 or( ih.invoice_type_lookup_code = 'CREDIT'
832 and EXISTS (SELECT 1
833 FROM ap_invoices_all tih
834 WHERe tih.invoice_id = id.parent_invoice_id
835 AND TO_CHAR(tih.invoice_date, 'YYYY') =TO_CHAR(add_months(gd_period_end_date,-12), 'YYYY') --bug 7031451
836 ))
837 )
838
839 AND TO_CHAR(ih.invoice_date, 'YYYY') = TO_CHAR(gd_period_end_date, 'YYYY')
840 AND ih.vendor_id = pv.vendor_id
841 AND pvs.vendor_id = pv.vendor_id
842 AND pvs.tax_reporting_site_flag = 'Y'
843 AND pv.federal_reportable_flag = 'Y'
844 AND pvs.country = gv_vat_country_code
845 AND pv.employee_id = papf.person_id (+)
846 AND NVL(NVL(pvs.vat_registration_num, pv.vat_registration_num),'-99') <> gv_repent_trn --bug 7018923
847 AND ((P_VAT_REG = 'N') OR
848 (P_VAT_REG = 'Y' AND NVL(pvs.vat_registration_num, pv.vat_registration_num) IS NOT NULL))
849 AND il.tax_rate_id = zxrates.tax_rate_id
850 AND zxrates.content_owner_id = zxtaxes.content_owner_id
851 AND zxrates.tax_regime_code = zxtaxes.tax_regime_code
852 AND zxrates.tax = zxtaxes.tax
853 AND zxrates.tax_rate_id = zxass.entity_id(+)
854 AND zxass.entity_code(+) = 'ZX_RATES'
855 AND DECODE(zxtaxes.offset_tax_flag , 'Y', 'OFFSET',
856 DECODE(zxrates.def_rec_settlement_option_code, 'DEFERRED','DEFERRED',
857 zxass.REPORTING_CODE_CHAR_VALUE))<> 'CUSTOM BILL'
858
859 AND il.tax_rate_id IN (SELECT tax_rate_id
860 FROM je_it_setup_lines_all
861 WHERE year_of_declaration = P_YEAR_OF_DECLARATION
862 AND application_id = gn_ap_app_id
863 AND vat_reporting_entity_id = gn_legal_vat_rep_entity_id);
864
865 l_record_count NUMBER :=0;
866
867 BEGIN
868 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
869 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Start PROCEDURE Fetch_trx_data_ap');
870 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Parameters are :');
871 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','P_VAT_REPORTING_ENTITY_ID'||P_VAT_REPORTING_ENTITY_ID);
872 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','P_YEAR_OF_DECLARATION'||P_YEAR_OF_DECLARATION);
873
874 IF P_VAT_REG= 'Y' THEN
875 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','P_VAT_REG is TRUE' );
876 ELSE
877 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','P_VAT_REG is FALSE' );
878 END IF;
879 END IF;
880
881 g_retcode :=0;
882
883 OPEN trl_tax_data_csr;
884 LOOP
885 FETCH trl_tax_data_csr BULK COLLECT INTO
886 gt_party_id,
887 gt_trx_type_code,
888 gt_trx_type_id,
889 gt_doc_seq_num,
890 gt_doc_seq_val,
891 gt_trx_date,
892 gt_trx_id,
893 gt_trx_num,
894 gt_trx_tax_dist_id,
895 gt_trx_line_dist_id,
896 gt_trx_tax_line_type_code,
897 gt_trx_line_type_code,
898 gt_trx_line_tax_rate_id,
899 gt_inv_tax_line_amount,
900 gt_inv_tax_line_amount_cm,
901 gt_inv_line_amount,
902 gt_inv_line_amount_cm
903 LIMIT g_lines_per_commit;
904
905 l_record_count := l_record_count+ gt_trx_id.count;
906
907 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
908 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Calling insert_tax_data');
909 END IF;
910
911 SAVEPOINT before_insert_lines;
912 insert_tax_data(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION,gn_ap_app_id);
913 COMMIT;
914 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
915 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Calling init_gt_variables');
916 END IF;
917 init_gt_variables;
918 EXIT WHEN trl_tax_data_csr%NOTFOUND;
919 END LOOP;
920
921
922 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
923 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Number of AP records inserted into JE_IT_LIST_LINES :'||l_record_count);
924 END IF;
925
926 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
927 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','End PROCEDURE Fetch_trx_data_ap');
928 END IF;
929 EXCEPTION
930 WHEN OTHERS THEN
931 g_retcode :=2;
932 g_errbuf :='Exception in JE_IT_LISTING_PKG.Fetch_trx_data_ap';
933 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
934 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Exception in PROCEDURE Fetch_trx_data_ap');
935 END IF;
936 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
937 IF g_debug_flag = 'Y' THEN
938 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
939 END IF;
940 ROLLBACK TO before_insert_lines;
941 END Fetch_trx_data_ap;
942
943 --------------------------------------------------------------------------------
944 /*===========================================================================+
945 | PROCEDURE |
946 | Fetch_trx_data_ar |
947 | |
948 | DESCRIPTION |
949 | This procedure fetches the distribution lines from the AR |
950 | using bulk fetch and calls the procedure insert_tax_data |
951 | to insert data into the JE_IT_LIST_LINES table |
952 | Called from JE_IT_LISTING_PKG.Extract_Data() |
953 | |
954 | SCOPE - Private |
955 | |
956 | NOTES |
957 | |
958 | MODIFICATION HISTORY |
959 | Date Author Description |
960 | ============ ============== ================================= |
961 | 14-Dec-2007 spasupun Initial Version. |
962 | |
963 +===========================================================================*/
964 PROCEDURE Fetch_trx_data_ar( P_VAT_REPORTING_ENTITY_ID IN NUMBER,
965 P_YEAR_OF_DECLARATION IN NUMBER,
966 P_VAT_REG IN VARCHAR2) IS
967
968 CURSOR trl_tax_data_csr IS
969 SELECT
970 NVL(rth.sold_to_customer_id, rth.bill_to_customer_id), --PARTY_ID - Third party ID
971 rtp.type, --TRX_TYPE_CODE -Transaction_Type - AP --bug 7031451
972 rth.cust_trx_type_id, --TRX_TYPE_ID - Transaction Type ID - AR
973 rth.doc_sequence_id, --DOC_SEQ_NUM - Sequence_Number
974 rth.doc_sequence_value, --DOC_SEQ_VAL
975 rth.trx_date, --TRX_DATE - Invoice_Date
976 rth.customer_trx_id, --TRX_ID
977 rth.trx_number, --TRX_NUM - Invoice_Number --TRX_NUM - Invoice_Number
978 rcgl.cust_trx_line_gl_dist_id, --TAX_DIST_ID
979 rcglitem.cust_trx_line_gl_dist_id, --LINE_DIST_ID
980 rtl.line_type, --TRX_LINE_TYPE_CODE - Inv_Line_Type
981 rtlitem.line_type, --TRX_LINE_TYPE_CODE - Inv_Line_Type
982 rtl.vat_tax_id, --TRX_LINE_TAX_CODE_ID - Inv_Line_Tax_Code
983 DECODE(rtp.type,'CM',0,ROUND(rcgl.amount*NVL(rth.exchange_rate, 1),2)) amount_tax, --Inv_Line_Amt
984 DECODE(rtp.type,'CM',ROUND(rcgl.amount*NVL(rth.exchange_rate, 1),2),0) cm_amount_tax, --Inv_Line_Amt
985 DECODE(rtp.type,'CM',0,ROUND(rcglitem.amount*NVL(rth.exchange_rate, 1),2)) amount_item, --Inv_Line_Amt
986 decode(rtp.type,'CM',ROUND(rcglitem.amount*NVL(rth.exchange_rate, 1),2),0) cm_amount_item
987
988 FROM ra_customer_trx_all rth,
989 ra_customer_trx_lines_all rtl,
990 ra_cust_trx_line_gl_dist_all rcgl,
991 ra_customer_trx_lines_all rtlitem,
992 ra_cust_trx_line_gl_dist_all rcglitem,
993 hz_cust_site_uses_all hzcsu,
994 hz_cust_acct_sites_all hzcas,
995 hz_cust_accounts hzca,
996 hz_parties hzp,
997 jg_zz_vat_rep_entities repent,
998 zx_rates_b zxrates,
999 zx_taxes_b zxtaxes,
1000 zx_report_codes_assoc zxass,
1001 ra_cust_trx_types_all rtp
1002 WHERE repent.vat_reporting_entity_id = p_vat_reporting_entity_id
1003 AND ( ( repent.entity_type_code = 'LEGAL'
1004 AND rth.legal_entity_id = gn_legal_entity_id )
1005 OR( repent.entity_type_code = 'ACCOUNTING'
1006 AND repent.entity_level_code = 'LEDGER'
1007 AND rth.set_of_books_id = gv_ledger_id)
1008 OR( repent.entity_type_code = 'ACCOUNTING'
1009 AND repent.entity_level_code = 'BSV'
1010 AND rth.set_of_books_id = gv_ledger_id
1011 AND get_bsv(rcgl.code_combination_id) = gv_balancing_segment_value)
1012 )
1013 AND rtl.customer_trx_id = rth.customer_trx_id
1014 AND TO_CHAR(rth.trx_date, 'YYYY') = TO_CHAR(gd_period_end_date, 'YYYY')
1015 AND rcgl.customer_trx_id = rtl.customer_trx_id
1016 AND rcgl.customer_trx_line_id = rtl.customer_trx_line_id
1017 AND rtl.line_type = 'TAX'
1018 AND rtlitem.customer_trx_id = rtl.customer_trx_id
1019 AND rtlitem.line_type = 'LINE'
1020 AND rtl.link_to_cust_trx_line_id = rtlitem.customer_trx_line_id (+)
1021 AND rcglitem.customer_trx_line_id = rtlitem.customer_trx_line_id
1022 AND rcglitem.customer_trx_id = rtlitem.customer_trx_id
1023 AND rcgl.posting_control_id <> -3
1024 AND NVL(rth.sold_to_customer_id, rth.bill_to_customer_id) = hzca.cust_account_id
1025 AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
1026 AND hzcas.cust_account_id = hzca.cust_account_id
1027 AND hzca.party_id = hzp.party_id
1028 AND upper(hzcsu.site_use_code) = 'LEGAL'
1029 AND hzcsu.primary_flag = 'Y'
1030 AND hzcsu.status = 'A'
1031 AND hzp.country = gv_vat_country_code
1032 AND NVL(decode(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference),'-99') <> TO_CHAR(gv_repent_trn)
1033 AND ((P_VAT_REG = 'N') OR
1034 (P_VAT_REG = 'Y' AND decode(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference) IS NOT NULL))
1035 AND rtl.vat_tax_id = zxrates.tax_rate_id
1036 AND zxrates.content_owner_id = zxtaxes.content_owner_id
1037 AND zxrates.tax_regime_code = zxtaxes.tax_regime_code
1038 AND zxrates.tax = zxtaxes.tax
1039 AND zxrates.tax_rate_id = zxass.entity_id(+)
1040 AND zxass.entity_code(+) = 'ZX_RATES'
1041 AND DECODE(zxtaxes.offset_tax_flag , 'Y', 'OFFSET', DECODE(zxrates.def_rec_settlement_option_code, 'DEFERRED','DEFERRED',zxass.REPORTING_CODE_CHAR_VALUE))<> 'CUSTOM BILL'
1042 AND rtp.cust_trx_type_id = rth.cust_trx_type_id
1043 AND rtp.org_id = rth.org_id --bug 7031451
1044 AND ( (rtp.type <> 'CM') or
1045 (rtp.type = 'CM'
1046 and EXISTS(SELECT arct.customer_trx_id
1047 FROM ar_receivable_applications_all arap,
1048 ra_customer_trx_all arct
1049 WHERE arap.customer_trx_id = rth.customer_trx_id
1050 AND application_type ='CM'
1051 AND arap.applied_customer_trx_id = arct.customer_trx_id
1052 AND TO_CHAR(arct.trx_date, 'YYYY') = TO_CHAR(add_months(gd_period_end_date,-12), 'YYYY')
1053 ))
1054 )
1055 AND rtl.vat_tax_id IN (SELECT tax_rate_id FROM JE_IT_SETUP_LINES_ALL
1056 WHERE vat_reporting_entity_id = gn_legal_vat_rep_entity_id
1057 AND year_of_declaration = P_YEAR_OF_DECLARATION
1058 AND application_id= gn_ar_app_id);
1059
1060 l_record_count NUMBER := 0;
1061
1062 BEGIN
1063 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1064 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ar','Start PROCEDURE Fetch_trx_data_ar');
1065 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ar','Parameters are :');
1066 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ar','P_VATRE_REPORTING_ENTITY_ID='||P_VAT_REPORTING_ENTITY_ID);
1067 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ar','P_YEAR_OF_DECLARATION='||P_YEAR_OF_DECLARATION);
1068
1069 IF P_VAT_REG = 'Y' THEN
1070 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ar','P_VAT_REG is TRUE');
1071 ELSE
1072 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ar','P_VAT_REG is FALSE');
1073 END IF;
1074
1075 END IF;
1076 g_retcode :=0;
1077
1078 init_gt_variables;
1079
1080 OPEN trl_tax_data_csr;
1081 LOOP
1082 FETCH trl_tax_data_csr BULK COLLECT INTO
1083 gt_party_id,
1084 gt_trx_type_code,
1085 gt_trx_type_id,
1086 gt_doc_seq_num,
1087 gt_doc_seq_val,
1088 gt_trx_date,
1089 gt_trx_id,
1090 gt_trx_num,
1091 gt_trx_tax_dist_id,
1092 gt_trx_line_dist_id,
1093 gt_trx_tax_line_type_code,
1094 gt_trx_line_type_code,
1095 gt_trx_line_tax_rate_id,
1096 gt_inv_tax_line_amount,
1097 gt_inv_tax_line_amount_cm,
1098 gt_inv_line_amount,
1099 gt_inv_line_amount_cm
1100 LIMIT g_lines_per_commit;
1101
1102 l_record_count := l_record_count + gt_trx_id.count;
1103
1104 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1105 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Calling insert_tax_data');
1106 END IF;
1107
1108 SAVEPOINT before_insert_lines;
1109 insert_tax_data(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION,gn_ar_app_id);
1110 COMMIT;
1111 init_gt_variables;
1112 EXIT WHEN trl_tax_data_csr%NOTFOUND;
1113 END LOOP;
1114
1115 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1116 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ar','Number of AR records inserted into JE_IT_LIST_LINES :'||l_record_count);
1117 END IF;
1118
1119
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 g_retcode :=2;
1123 g_errbuf :='Exception in JE_IT_LISTING_PKG.Fetch_trx_data_ar';
1124 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1125 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ar','Exception in PROCEDURE Fetch_trx_data_ar');
1126 END IF;
1127 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1128 IF g_debug_flag = 'Y' THEN
1129 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
1130 END IF;
1131 ROLLBACK TO before_insert_lines;
1132 END Fetch_trx_data_ar;
1133
1134 --------------------------------------------------------------------------------
1135 /*===========================================================================+
1136 | PROCEDURE |
1137 | Fetch_trx_data_ap |
1138 | |
1139 | DESCRIPTION |
1140 | This procedure inserts data into the JE_IT_LIST_LINES table |
1141 | Called from JE_IT_LISTING_PKG.Fetch_trx_data_ap() and |
1142 | JE_IT_LISTING_PKG.Fetch_trx_data_ar() |
1143 | SCOPE - Private |
1144 | |
1145 | NOTES |
1146 | |
1147 | MODIFICATION HISTORY |
1148 | Date Author Description |
1149 | ============ ============== ================================= |
1150 | 14-Dec-2007 spasupun Initial Version. |
1151 | |
1152 +===========================================================================*/
1153
1154 PROCEDURE Insert_tax_data( P_VAT_REPORTING_ENTITY_ID IN NUMBER,
1155 P_YEAR_OF_DECLARATION IN NUMBER,
1156 P_APP_ID IN NUMBER) IS
1157
1158 --Variable for Tax Line
1159
1160 v_taxable_t NUMBER;
1161 v_non_taxable_t NUMBER;
1162 v_vat_t NUMBER;
1163 v_exempt_t NUMBER;
1164 v_tax_vat_t NUMBER;
1165 v_tax_vat_inv_t NUMBER;
1166
1167 v_taxable_cm_t NUMBER;
1168 v_non_taxable_cm_t NUMBER;
1169 v_vat_cm_t NUMBER;
1170 v_exempt_cm_t NUMBER;
1171 v_tax_vat_cm_t NUMBER;
1172 v_tax_vat_inv_cm_t NUMBER;
1173
1174 --Variable for ITEM/LINE Line
1175
1176 v_taxable_l NUMBER;
1177 v_non_taxable_l NUMBER;
1178 v_vat_l NUMBER;
1179 v_exempt_l NUMBER;
1180 v_tax_vat_l NUMBER;
1181 v_tax_vat_inv_l NUMBER;
1182
1183 v_taxable_cm_l NUMBER;
1184 v_non_taxable_cm_l NUMBER;
1185 v_vat_cm_l NUMBER;
1186 v_exempt_cm_l NUMBER;
1187 v_tax_vat_cm_l NUMBER;
1188 v_tax_vat_inv_cm_l NUMBER;
1189
1190 available_flag varchar2(10) := 'N';
1191
1192 CURSOR vat_ui(P_VAT_REPORTING_ENTITY_ID NUMBER,P_YEAR_OF_DECLARATION NUMBER
1193 ,P_APP_ID NUMBER,P_TAX_RATE_ID NUMBER) IS
1194 SELECT listing_column_code
1195 FROM je_it_setup_lines_all
1196 WHERE vat_reporting_entity_id=P_VAT_REPORTING_ENTITY_ID
1197 AND year_of_declaration = P_YEAR_OF_DECLARATION
1198 AND application_id = P_APP_ID
1199 AND tax_rate_id = P_TAX_RATE_ID;
1200 BEGIN
1201 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1202 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Start PROCEDURE Insert_tax_data');
1203 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Parameters are :');
1204 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Insert_tax_data','p_vat_reporting_entity_id ='||p_vat_reporting_entity_id);
1205 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Insert_tax_data','p_year_of_declaration ='||p_year_of_declaration);
1206 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Insert_tax_data','p_app_id ='||p_app_id);
1207 END IF;
1208 g_retcode :=0;
1209
1210 FOR i IN 1 .. g_lines_per_commit
1211
1212 LOOP
1213 --Variables for Tax Line
1214
1215 v_taxable_t := NULL;
1216 v_non_taxable_t := NULL;
1217 v_vat_t := NULL;
1218 v_exempt_t := NULL;
1219 v_tax_vat_t := NULL;
1220 v_tax_vat_inv_t := NULL;
1221
1222 v_taxable_cm_t := NULL;
1223 v_non_taxable_cm_t := NULL;
1224 v_vat_cm_t := NULL;
1225 v_exempt_cm_t := NULL;
1226 v_tax_vat_cm_t := NULL;
1227 v_tax_vat_inv_cm_t := NULL;
1228
1229 --Variables for ITEM Line
1230
1231 v_taxable_l := NULL;
1232 v_non_taxable_l := NULL;
1233 v_vat_l := NULL;
1234 v_exempt_l := NULL;
1235 v_tax_vat_l := NULL;
1236 v_tax_vat_inv_l := NULL;
1237
1238 v_taxable_cm_l := NULL;
1239 v_non_taxable_cm_l := NULL;
1240 v_vat_cm_l := NULL;
1241 v_exempt_cm_l := NULL;
1242 v_tax_vat_cm_l := NULL;
1243 v_tax_vat_inv_cm_l := NULL;
1244
1245 FOR rec_vat_ui IN vat_ui(gn_legal_vat_rep_entity_id,P_YEAR_OF_DECLARATION,P_APP_ID,gt_trx_line_tax_rate_id(i))
1246
1247 LOOP
1248
1249 -- Tax Line Information
1250
1251
1252 IF gt_trx_tax_line_type_code(i) = 'TAX' THEN
1253
1254 IF ((P_APP_ID = 222 AND gt_trx_type_code(i) <> 'CM' )
1255 or(P_APP_ID = 200 AND gt_trx_type_code(i) <> 'CREDIT')) THEN
1256
1257 IF rec_vat_ui.listing_column_code = 'TAXABLE' THEN
1258 v_vat_t := gt_inv_tax_line_amount(i);
1259
1260 END IF;
1261 IF rec_vat_ui.listing_column_code = 'TAX_VAT' THEN
1262 v_tax_vat_t := gt_inv_tax_line_amount(i);
1263
1264 END IF;
1265 IF rec_vat_ui.listing_column_code = 'TAX_VAT_INV' THEN
1266 v_tax_vat_inv_t := gt_inv_tax_line_amount(i);
1267
1268 END IF;
1269
1270 ELSIF ((P_APP_ID = 222 AND gt_trx_type_code(i) = 'CM' )
1271 or(P_APP_ID = 200 AND gt_trx_type_code(i) = 'CREDIT')) THEN
1272
1273 IF rec_vat_ui.listing_column_code = 'TAXABLE' THEN
1274 v_vat_cm_t := gt_inv_tax_line_amount_cm(i);
1275
1276
1277 END IF;
1278 IF rec_vat_ui.listing_column_code = 'TAX_VAT' THEN
1279 v_tax_vat_cm_t := gt_inv_tax_line_amount_cm(i);
1280
1281 END IF;
1282 IF rec_vat_ui.listing_column_code = 'TAX_VAT_INV' THEN
1283 v_tax_vat_inv_cm_t := gt_inv_tax_line_amount_cm(i);
1284 END IF;
1285 END IF;
1286 END IF;
1287
1288 -- Tax Line corresponding Item Line information.
1289
1290 IF gt_trx_line_type_code(i) = 'LINE' or gt_trx_line_type_code(i) = 'ITEM' THEN
1291
1292 IF ((P_APP_ID = 222 AND gt_trx_type_code(i) <> 'CM' )
1293 or(P_APP_ID = 200 AND gt_trx_type_code(i) <> 'CREDIT')) THEN
1294
1295 IF rec_vat_ui.listing_column_code = 'TAXABLE' THEN
1296 v_taxable_l := gt_inv_line_amount(i);
1297
1298 END IF;
1299 IF rec_vat_ui.listing_column_code = 'NONTAXABLE' THEN
1300 v_non_taxable_l := gt_inv_line_amount(i);
1301
1302 END IF;
1303 IF rec_vat_ui.listing_column_code = 'EXEMPT' THEN
1304 v_exempt_l := gt_inv_line_amount(i);
1305
1306 END IF;
1307 IF rec_vat_ui.listing_column_code = 'TAX_VAT' THEN
1308 v_tax_vat_l := gt_inv_line_amount(i);
1309
1310 END IF;
1311 IF rec_vat_ui.listing_column_code = 'TAX_VAT_INV' THEN
1312 v_tax_vat_inv_l := gt_inv_line_amount(i);
1313
1314
1315 END IF;
1316 ELSIF ((P_APP_ID = 222 AND gt_trx_type_code(i) = 'CM' )
1317 or(P_APP_ID = 200 AND gt_trx_type_code(i) = 'CREDIT')) THEN
1318
1319 IF rec_vat_ui.listing_column_code = 'TAXABLE' THEN
1320 v_taxable_cm_l := gt_inv_line_amount_cm(i);
1321
1322 END IF;
1323 IF rec_vat_ui.listing_column_code = 'NONTAXABLE' THEN
1324 v_non_taxable_cm_l := gt_inv_line_amount_cm(i);
1325
1326 END IF;
1327 IF rec_vat_ui.listing_column_code = 'EXEMPT' THEN
1328 v_exempt_cm_l := gt_inv_line_amount_cm(i);
1329
1330 END IF;
1331 IF rec_vat_ui.listing_column_code = 'TAX_VAT' THEN
1332 v_tax_vat_cm_l := gt_inv_line_amount_cm(i);
1333
1334 END IF;
1335 IF rec_vat_ui.listing_column_code = 'TAX_VAT_INV' THEN
1336 v_tax_vat_inv_cm_l := gt_inv_line_amount_cm(i);
1337
1338 END IF;
1339
1340 END IF;
1341 END IF;
1342 END LOOP;
1343
1344 IF P_APP_ID = 222 THEN
1345 v_tax_vat_t :=NULL;
1346 v_tax_vat_cm_t :=NULL;
1347 v_tax_vat_l :=NULL;
1348 v_tax_vat_cm_l :=NULL;
1349 END IF;
1350
1351 --- Bug 7018923
1352
1353
1354
1355 BEGIN
1356 SELECT 'Y' INTO available_flag FROM JE_IT_LIST_LINES_ALL
1357 WHERE
1358 VAT_REPORTING_ENTITY_ID = P_VAT_REPORTING_ENTITY_ID
1359 AND YEAR_OF_DECLARATION = P_YEAR_OF_DECLARATION
1360 AND APPLICATION_ID = p_app_id
1361 AND PARTY_ID = gt_party_id(i)
1362 AND TRX_DIST_ID = gt_trx_line_dist_id(i);
1363
1364 EXCEPTION
1365 WHEN NO_DATA_FOUND THEN
1366 available_flag := 'N';
1367 END;
1368
1369
1370
1371 IF available_flag is NULL or available_flag = 'N' THEN
1372
1373 -- ITEM line insertion
1374 INSERT INTO JE_IT_LIST_LINES_ALL(
1375 VAT_REPORTING_ENTITY_ID,
1376 YEAR_OF_DECLARATION,
1377 APPLICATION_ID,
1378 PARTY_ID,
1379 TRX_TYPE_CODE,
1380 TRX_TYPE_ID,
1381 DOC_SEQ_ID,
1382 DOC_SEQ_NUM,
1383 TRX_DATE,
1384 TRX_ID,
1385 TRX_NUM,
1386 TRX_DIST_ID,
1387 TRX_LINE_TYPE_CODE,
1388 TAX_RATE_ID,
1389 TAXABLE_AMT,
1390 VAT_AMT,
1391 NON_TAXABLE_AMT,
1392 EXEMPT_AMT,
1393 TAXABLE_VAT_AMT,
1394 TAXABLE_VAT_INV_AMT,
1395 CM_TAXABLE_AMT,
1396 CM_VAT_AMT,
1397 CM_NON_TAXABLE_AMT,
1398 CM_EXEMPT_AMT,
1399 CM_TAXABLE_VAT_AMT,
1400 CM_TAXABLE_VAT_INV_AMT,
1401 LAST_UPDATE_DATE,
1402 LAST_UPDATED_BY,
1403 LAST_UPDATE_LOGIN,
1404 CREATION_DATE,
1405 CREATED_BY)
1406 VALUES (P_VAT_REPORTING_ENTITY_ID,
1407 P_YEAR_OF_DECLARATION,
1408 p_app_id, --APPLICATION_ID
1409 gt_party_id(i), --PARTY_ID
1410 gt_trx_type_code(i), --TRX_TYPE_CODE
1411 gt_trx_type_id(i), --TRX_TYPE_ID
1412 gt_doc_seq_num(i), --DOC_SEQ_ID
1413 gt_doc_seq_val(i), --DOC_SEQ_NUM
1414 gt_trx_date(i), --TRX_DATE
1415 gt_trx_id(i), --TRX_ID
1416 gt_trx_num(i), --TRX_NUM
1417 gt_trx_line_dist_id(i), --TRX_DIST_ID
1418 gt_trx_line_type_code(i), --TRX_LINE_TYPE_CODE
1419 gt_trx_line_tax_rate_id(i), --TRX_LINE_TAX_CODE_ID
1420 v_taxable_l, --TAXABLE_AMT
1421 v_vat_l, --VAT_AMT
1422 v_non_taxable_l, --NON_TAXABLE_AMT
1423 v_exempt_l, --EXEMPT_AMT
1424 v_tax_vat_l, --TAXABLE_VAT_AMT
1425 v_tax_vat_inv_l, --TAXABLE_VAT_INV_AMT
1426 v_taxable_cm_l, --TAXABLE_AMT
1427 v_vat_cm_l, --VAT_AMT
1428 v_non_taxable_cm_l, --NON_TAXABLE_AMT
1429 v_exempt_cm_l, --EXEMPT_AMT
1430 v_tax_vat_cm_l, --TAXABLE_VAT_AMT
1431 v_tax_vat_inv_cm_l, --TAXABLE_VAT_INV_AMT
1432 g_last_update_date, --LAST_UPDATE_DATE
1433 g_last_updated_by, --LAST_UPDATED_BY
1434 g_last_update_login, --LAST_UPDATE_LOGIN
1435 g_creation_date, --CREATION_DATE
1436 g_created_by); --CREATED_BY
1437 END IF;
1438
1439 --- Bug 7018923
1440
1441 -- tax line insertion
1442
1443 INSERT INTO JE_IT_LIST_LINES_ALL(
1444 VAT_REPORTING_ENTITY_ID,
1445 YEAR_OF_DECLARATION,
1446 APPLICATION_ID,
1447 PARTY_ID,
1448 TRX_TYPE_CODE,
1449 TRX_TYPE_ID,
1450 DOC_SEQ_ID,
1451 DOC_SEQ_NUM,
1452 TRX_DATE,
1453 TRX_ID,
1454 TRX_NUM,
1455 TRX_DIST_ID,
1456 TRX_LINE_TYPE_CODE,
1457 TAX_RATE_ID,
1458 TAXABLE_AMT,
1459 VAT_AMT,
1460 NON_TAXABLE_AMT,
1461 EXEMPT_AMT,
1462 TAXABLE_VAT_AMT,
1463 TAXABLE_VAT_INV_AMT,
1464 CM_TAXABLE_AMT,
1465 CM_VAT_AMT,
1466 CM_NON_TAXABLE_AMT,
1467 CM_EXEMPT_AMT,
1468 CM_TAXABLE_VAT_AMT,
1469 CM_TAXABLE_VAT_INV_AMT,
1470 LAST_UPDATE_DATE,
1471 LAST_UPDATED_BY,
1472 LAST_UPDATE_LOGIN,
1473 CREATION_DATE,
1474 CREATED_BY)
1475 VALUES (P_VAT_REPORTING_ENTITY_ID,
1476 P_YEAR_OF_DECLARATION,
1477 p_app_id, --APPLICATION_ID
1478 gt_party_id(i), --PARTY_ID
1479 gt_trx_type_code(i), --TRX_TYPE_CODE
1480 gt_trx_type_id(i), --TRX_TYPE_ID
1481 gt_doc_seq_num(i), --DOC_SEQ_ID
1482 gt_doc_seq_val(i), --DOC_SEQ_NUM
1483 gt_trx_date(i), --TRX_DATE
1484 gt_trx_id(i), --TRX_ID
1485 gt_trx_num(i), --TRX_NUM
1486 gt_trx_tax_dist_id(i), --TRX_DIST_ID
1487 gt_trx_tax_line_type_code(i), --TRX_LINE_TYPE_CODE
1488 gt_trx_line_tax_rate_id(i), --TRX_LINE_TAX_CODE_ID
1489 v_taxable_t, --TAXABLE_AMT
1490 v_vat_t, --VAT_AMT
1491 v_non_taxable_t, --NON_TAXABLE_AMT
1492 v_exempt_t, --EXEMPT_AMT
1493 v_tax_vat_t, --TAXABLE_VAT_AMT
1494 v_tax_vat_inv_t, --TAXABLE_VAT_INV_AMT
1495 v_taxable_cm_t, --TAXABLE_AMT
1496 v_vat_cm_t, --VAT_AMT
1497 v_non_taxable_cm_t, --NON_TAXABLE_AMT
1498 v_exempt_cm_t, --EXEMPT_AMT
1499 v_tax_vat_cm_t, --TAXABLE_VAT_AMT
1500 v_tax_vat_inv_cm_t, --TAXABLE_VAT_INV_AMT
1501 g_last_update_date, --LAST_UPDATE_DATE
1502 g_last_updated_by, --LAST_UPDATED_BY
1503 g_last_update_login, --LAST_UPDATE_LOGIN
1504 g_creation_date, --CREATION_DATE
1505 g_created_by); --CREATED_BY
1506 END LOOP;
1507
1508
1509 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1510 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','End PROCEDURE Insert_tax_data');
1511 END IF;
1512 EXCEPTION
1513 WHEN OTHERS THEN
1514 g_retcode :=2;
1515 g_errbuf :='Exception in JE_IT_LISTING_PKG.Insert_tax_data';
1516 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1517 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Exception in PROCEDURE Insert_tax_data');
1518 END IF;
1519 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1520 IF g_debug_flag = 'Y' THEN
1521 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
1522 END IF;
1523 END Insert_tax_data;
1524
1525 --------------------------------------------------------------------------------
1526 /*===========================================================================+
1527 | PROCEDURE |
1528 | INIT_GT_VARIABLES |
1529 | DESCRIPTION |
1530 | This procedure initializes all global variables |
1531 | |
1532 | SCOPE - Private |
1533 | |
1534 | NOTES |
1535 | |
1536 | MODIFICATION HISTORY |
1537 | Date Author Description |
1538 | ============ ============== ================================= |
1539 | 14-Dec-2007 spasupun Initial Version. |
1540 | |
1541 +===========================================================================*/
1542 PROCEDURE Init_gt_variables IS
1543 BEGIN
1544 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1545 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Init_gt_variables','Start PROCEDURE Init_gt_variables');
1546 END IF;
1547 g_retcode :=0;
1548 gt_party_id.DELETE;
1549 gt_trx_type_code.DELETE;
1550 gt_trx_type_id.DELETE;
1551 gt_doc_seq_num.DELETE;
1552 gt_doc_seq_val.DELETE;
1553 gt_trx_date.DELETE;
1554 gt_trx_id.DELETE;
1555 gt_trx_num.DELETE;
1556 gt_trx_line_dist_id.DELETE;
1557 gt_trx_tax_dist_id.DELETE;
1558 gt_trx_line_type_code.DELETE;
1559 gt_trx_tax_line_type_code.DELETE;
1560 gt_trx_line_tax_rate_id.DELETE;
1561 gt_inv_line_amount.DELETE;
1562 gt_inv_tax_line_amount.DELETE;
1563 gt_inv_line_amount_cm.DELETE;
1564 gt_inv_tax_line_amount_cm.DELETE;
1565 gt_trx_type.DELETE;
1566 gt_party_vat_reg_num.DELETE;
1567 gt_party_fiscal_id_num.DELETE;
1568
1569 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1570 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Init_gt_variables','End PROCEDURE Init_gt_variables');
1571 END IF;
1572 EXCEPTION
1573 WHEN OTHERS THEN
1574 g_retcode :=2;
1575 g_errbuf :='Exception in JE_IT_LISTING_PKG.Init_gt_variables';
1576 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1577 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Init_gt_variables','Exception in PROCEDURE Insert_tax_data');
1578 END IF;
1579 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
1580 IF g_debug_flag = 'Y' THEN
1581 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
1582 END IF;
1583 END;
1584 -----------------------------------------------------------------------------
1585 /*===========================================================================+
1586 | PROCEDURE |
1587 | GENERATE_TRX_HEADERS |
1588 | DESCRIPTION |
1589 | This procedure populates the tables JE_IT_LIST_PARTIES_ALL |
1590 | and JE_IT_LIST_HDR_ALL |
1591 | SCOPE - Private |
1592 | |
1593 | NOTES |
1594 | |
1595 | MODIFICATION HISTORY |
1596 | Date Author Description |
1597 | ============ ============== ================================= |
1598 | 14-Dec-2007 spasupun Initial Version. |
1599 | |
1600 +===========================================================================*/
1601 ---------------------------------------------------------------------------------
1602 PROCEDURE Generate_trx_headers(
1603 P_VAT_REPORTING_ENTITY_ID IN NUMBER,
1604 P_YEAR_OF_DECLARATION IN NUMBER,
1605 P_CUST_SORT_COL IN VARCHAR2,
1606 P_VEND_SORT_COL IN VARCHAR2,
1607 P_GROUP_PARTIES_FLAG IN VARCHAR2) IS
1608
1609 l_transnum NUMBER;
1610 l_count NUMBER;
1611 l_seq_num NUMBER;
1612
1613 CURSOR cur_trx_lines(P_VAT_REPORTING_ENTITY NUMBER
1614 ,P_YEAR_OF_DECLARATION NUMBER
1615 ,P_CUST_SORT_COL VARCHAR2
1616 ,P_VEND_SORT_COL VARCHAR2) IS
1617 SELECT jit.application_id application_id,
1618 jit.PARTY_ID party_id, --PARTY_ID
1619 DECODE(P_CUST_SORT_COL,'C',hzp.party_name,'T',hzp.jgzz_fiscal_code
1620 ,'R',DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference)) sort_column,
1621 SUM(jit.TAXABLE_AMT) tot_taxable_amt, --TAXABLE_AMT
1622 SUM(jit.VAT_AMT) tot_vat_amt, --VAT_AMT
1623 SUM(jit.NON_TAXABLE_AMT) tot_non_taxable_amt, --NON_TAXABLE_AMT
1624 SUM(jit.EXEMPT_AMT) tot_exempt_amt, --EXEMPT_AMT
1625 SUM(jit.TAXABLE_VAT_AMT) tot_taxable_vat_amt, --TAXABLE_VAT_AMT
1626 SUM(jit.TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
1627 SUM(jit.CM_TAXABLE_AMT) tot_taxable_amt_cm, --TAXABLE_AMT
1628 SUM(jit.CM_VAT_AMT) tot_vat_amt_cm, --VAT_AMT
1629 SUM(jit.CM_NON_TAXABLE_AMT) tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
1630 SUM(jit.CM_EXEMPT_AMT) tot_exempt_amt_cm, --EXEMPT_AMT
1631 SUM(jit.CM_TAXABLE_VAT_AMT) tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
1632 SUM(jit.CM_TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
1633 hzp.jgzz_fiscal_code tax_payer_id, -- Customer Tax Payer ID
1634 DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference) vat_reg_num-- Customer Tax Registration Number
1635 FROM JE_IT_LIST_LINES_ALL jit,
1636 hz_cust_site_uses_all hzcsu,
1637 hz_cust_acct_sites_all hzcas,
1638 hz_cust_accounts hzca,
1639 hz_parties hzp
1640 WHERE jit.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
1641 AND jit.year_of_declaration = P_YEAR_OF_DECLARATION
1642 AND jit.APPLICATION_ID = 222
1643 AND jit.party_id = hzca.cust_account_id
1644 AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
1645 AND hzcas.cust_account_id = hzca.cust_account_id
1646 AND hzca.party_id = hzp.party_id
1647 AND upper(hzcsu.site_use_code) = 'LEGAL'
1648 AND hzcsu.primary_flag = 'Y'
1649 AND hzcsu.status = 'A'
1650 AND hzp.country = gv_vat_country_code
1651 AND NVL(decode(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference),'-99') <> TO_CHAR(gv_repent_trn)
1652 GROUP BY jit.PARTY_ID,
1653 jit.APPLICATION_ID,
1654 hzp.jgzz_fiscal_code,
1655 DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference),
1656 DECODE(P_CUST_SORT_COL,'C',hzp.party_name,'T',hzp.jgzz_fiscal_code
1657 ,'R',DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference))
1658 UNION ALL
1659 SELECT jit.APPLICATION_ID application_id,
1660 jit.PARTY_ID party_id, --PARTY_ID
1661 DECODE(P_VEND_SORT_COL,'V',pv.vendor_name,'T',NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)),
1662 'R',NVL(pvs.vat_registration_num, pv.vat_registration_num)) sort_column,
1663 SUM(jit.TAXABLE_AMT) tot_taxable_amt, --TAXABLE_AMT
1664 SUM(jit.VAT_AMT) tot_vat_amt, --VAT_AMT
1665 SUM(jit.NON_TAXABLE_AMT) tot_non_taxable_amt, --NON_TAXABLE_AMT
1666 SUM(jit.EXEMPT_AMT) tot_exempt_amt, --EXEMPT_AMT
1667 SUM(jit.TAXABLE_VAT_AMT) tot_taxable_vat_amt, --TAXABLE_VAT_AMT
1668 SUM(jit.TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
1669 SUM(jit.CM_TAXABLE_AMT) tot_taxable_amt_cm, --TAXABLE_AMT
1670 SUM(jit.CM_VAT_AMT) tot_vat_amt_cm, --VAT_AMT
1671 SUM(jit.CM_NON_TAXABLE_AMT) tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
1672 SUM(jit.CM_EXEMPT_AMT) tot_exempt_amt_cm, --EXEMPT_AMT
1673 SUM(jit.CM_TAXABLE_VAT_AMT) tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
1674 SUM(jit.CM_TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
1675 NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)), --Supplier Tax Payer ID
1676 NVL(pvs.vat_registration_num, pv.vat_registration_num) --Supplier Tax Registration Number
1677 FROM je_it_list_lines_all jit,
1678 ap_suppliers pv,
1679 ap_supplier_sites_all pvs,
1680 (SELECT distinct person_id
1681 ,national_identifier
1682 FROM per_all_people_f
1683 WHERE nvl(effective_end_date,sysdate) >= sysdate ) papf
1684 WHERE jit.year_of_declaration = P_YEAR_OF_DECLARATION
1685 AND jit.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
1686 AND jit.APPLICATION_ID = 200
1687 AND jit.party_id = pv.vendor_id
1688 AND pvs.vendor_id = pv.vendor_id
1689 AND pvs.tax_reporting_site_flag = 'Y'
1690 AND pv.federal_reportable_flag = 'Y'
1691 AND pvs.country = gv_vat_country_code
1692 AND NVL(NVL(pvs.vat_registration_num, pv.vat_registration_num),'-99') <> gv_repent_trn
1693 AND pv.employee_id = papf.person_id (+)
1694 GROUP BY jit.PARTY_ID,
1695 jit.APPLICATION_ID,
1696 NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)), --Supplier Tax Payer ID
1697 NVL(pvs.vat_registration_num, pv.vat_registration_num),
1698 DECODE(P_VEND_SORT_COL,'V',pv.vendor_name,'T',NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)),
1699 'R',NVL(pvs.vat_registration_num, pv.vat_registration_num))
1700 ORDER BY application_id DESC,sort_column;
1701
1702
1703 CURSOR cur_trx_lines_group(P_VAT_REPORTING_ENTITY NUMBER
1704 ,P_YEAR_OF_DECLARATION NUMBER
1705 ,P_CUST_SORT_COL VARCHAR2
1706 ,P_VEND_SORT_COL VARCHAR2) IS
1707 SELECT *
1708 FROM
1709 (
1710 SELECT COLLECTION.*,
1711 hzp.party_name party_name
1712 FROM
1713 (SELECT jit.application_id application_id,
1714 MAX(jit.PARTY_ID) party_id, --MAX PARTY_ID
1715 SUM(jit.TAXABLE_AMT) tot_taxable_amt, --TAXABLE_AMT
1716 SUM(jit.VAT_AMT) tot_vat_amt, --VAT_AMT
1717 SUM(jit.NON_TAXABLE_AMT) tot_non_taxable_amt, --NON_TAXABLE_AMT
1718 SUM(jit.EXEMPT_AMT) tot_exempt_amt, --EXEMPT_AMT
1719 SUM(jit.TAXABLE_VAT_AMT) tot_taxable_vat_amt, --TAXABLE_VAT_AMT
1720 SUM(jit.TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
1721 SUM(jit.CM_TAXABLE_AMT) tot_taxable_amt_cm, --TAXABLE_AMT
1722 SUM(jit.CM_VAT_AMT) tot_vat_amt_cm, --VAT_AMT
1723 SUM(jit.CM_NON_TAXABLE_AMT) tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
1724 SUM(jit.CM_EXEMPT_AMT) tot_exempt_amt_cm, --EXEMPT_AMT
1725 SUM(jit.CM_TAXABLE_VAT_AMT) tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
1726 SUM(jit.CM_TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
1727 hzp.jgzz_fiscal_code tax_payer_id, -- Customer Tax Payer ID
1728 DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference) vat_reg_num-- Customer Tax Registration Number
1729 FROM JE_IT_LIST_LINES_ALL jit,
1730 hz_cust_site_uses_all hzcsu,
1731 hz_cust_acct_sites_all hzcas,
1732 hz_cust_accounts hzca,
1733 hz_parties hzp
1734 WHERE jit.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
1735 AND jit.year_of_declaration = P_YEAR_OF_DECLARATION
1736 AND jit.APPLICATION_ID = 222
1737 AND jit.party_id = hzca.cust_account_id
1738 AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
1739 AND hzcas.cust_account_id = hzca.cust_account_id
1740 AND hzca.party_id = hzp.party_id
1741 AND upper(hzcsu.site_use_code) = 'LEGAL'
1742 AND hzcsu.primary_flag = 'Y'
1743 AND hzcsu.status = 'A'
1744 AND hzp.country = gv_vat_country_code
1745 AND NVL(decode(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference),'-99') <> TO_CHAR(gv_repent_trn)
1746 GROUP BY jit.APPLICATION_ID, hzp.jgzz_fiscal_code, DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference)
1747 ) COLLECTION,
1748 hz_cust_accounts hzca,
1749 hz_parties hzp
1750 WHERE
1751 COLLECTION.party_id = hzca.cust_account_id
1752 AND hzca.party_id = hzp.party_id
1753
1754 UNION ALL
1755
1756 SELECT COLLECTION.*,
1757 pv.vendor_name party_name
1758 FROM
1759
1760 (SELECT jit.APPLICATION_ID application_id,
1761 MAX(jit.PARTY_ID) party_id, --MAX PARTY_ID
1762 SUM(jit.TAXABLE_AMT) tot_taxable_amt, --TAXABLE_AMT
1763 SUM(jit.VAT_AMT) tot_vat_amt, --VAT_AMT
1764 SUM(jit.NON_TAXABLE_AMT) tot_non_taxable_amt, --NON_TAXABLE_AMT
1765 SUM(jit.EXEMPT_AMT) tot_exempt_amt, --EXEMPT_AMT
1766 SUM(jit.TAXABLE_VAT_AMT) tot_taxable_vat_amt, --TAXABLE_VAT_AMT
1767 SUM(jit.TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
1768 SUM(jit.CM_TAXABLE_AMT) tot_taxable_amt_cm, --TAXABLE_AMT
1769 SUM(jit.CM_VAT_AMT) tot_vat_amt_cm, --VAT_AMT
1770 SUM(jit.CM_NON_TAXABLE_AMT) tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
1771 SUM(jit.CM_EXEMPT_AMT) tot_exempt_amt_cm, --EXEMPT_AMT
1772 SUM(jit.CM_TAXABLE_VAT_AMT) tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
1773 SUM(jit.CM_TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
1774 NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)) tax_payer_id, --Supplier Tax Payer ID
1775 NVL(pvs.vat_registration_num, pv.vat_registration_num) vat_reg_num --Supplier Tax Registration Number
1776 FROM je_it_list_lines_all jit,
1777 ap_suppliers pv,
1778 ap_supplier_sites_all pvs,
1779 (SELECT distinct person_id
1780 ,national_identifier
1781 FROM per_all_people_f
1782 WHERE nvl(effective_end_date,sysdate) >= sysdate ) papf
1783 WHERE jit.year_of_declaration = P_YEAR_OF_DECLARATION
1784 AND jit.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
1785 AND jit.APPLICATION_ID = 200
1786 AND jit.party_id = pv.vendor_id
1787 AND pvs.vendor_id = pv.vendor_id
1788 AND pvs.tax_reporting_site_flag = 'Y'
1789 AND pv.federal_reportable_flag = 'Y'
1790 AND pvs.country = gv_vat_country_code
1791 AND NVL(NVL(pvs.vat_registration_num, pv.vat_registration_num),'-99') <> gv_repent_trn
1792 AND pv.employee_id = papf.person_id (+)
1793 GROUP BY jit.APPLICATION_ID,
1794 NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)),
1795 NVL(pvs.vat_registration_num, pv.vat_registration_num)
1796 ) COLLECTION,
1797 ap_suppliers pv
1798 WHERE
1799 COLLECTION.party_id = pv.vendor_id
1800 ) OUTERQ
1801 ORDER BY OUTERQ.application_id DESC,
1802 DECODE (OUTERQ.application_id,
1803 222,
1804 DECODE(P_CUST_SORT_COL,'C',OUTERQ.party_name,'T',OUTERQ.tax_payer_id,'R',OUTERQ.vat_reg_num),
1805 200,
1806 DECODE(P_VEND_SORT_COL,'V',OUTERQ.party_name,'T',OUTERQ.tax_payer_id,'R',OUTERQ.vat_reg_num));
1807
1808
1809 BEGIN
1810 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1811 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','Start PROCEDURE Generate_trx_headers');
1812 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','Parameters are :');
1813 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','P_VAT_REPORTING_ENTITY_ID ='||P_VAT_REPORTING_ENTITY_ID);
1814 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','P_YEAR_OF_DECLARATION ='||P_YEAR_OF_DECLARATION);
1815 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','P_VEND_SORT_COL ='||P_VEND_SORT_COL);
1816 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','P_CUST_SORT_COL ='||P_CUST_SORT_COL);
1817 END IF;
1818
1819 g_retcode :=0;
1820 l_transnum :=1;
1821 l_count :=1;
1822 l_seq_num :=1;
1823
1824 SAVEPOINT before_insert_parties;
1825
1826 IF P_GROUP_PARTIES_FLAG IS NULL or P_GROUP_PARTIES_FLAG = 'N' THEN -- If 'group by' paramater is set to No or Null
1827
1828 FOR rec_lines IN cur_trx_lines(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION,P_CUST_SORT_COL ,P_VEND_SORT_COL ) LOOP
1829
1830 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1831 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','Inside FOR rec_lines IN cur_trx_lines');
1832 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','l_transnum = '||l_transnum);
1833 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','l_count = '||l_count);
1834 END IF;
1835
1836 IF l_count > nvl(g_rec_per_eft,l_count) THEN
1837 l_transnum := l_transnum + 1;
1838 l_count :=1;
1839 END IF;
1840
1841 INSERT INTO JE_IT_LIST_PARTIES_ALL
1842 (VAT_REPORTING_ENTITY_ID,
1843 YEAR_OF_DECLARATION,
1844 TRANSMISSION_NUM,
1845 APPLICATION_ID,
1846 PARTY_ID,
1847 TAXABLE_AMT,
1848 VAT_AMT,
1849 NON_TAXABLE_AMT,
1850 EXEMPT_AMT,
1851 TAXABLE_VAT_AMT,
1852 TAXABLE_VAT_INV_AMT,
1853 CM_TAXABLE_AMT,
1854 CM_VAT_AMT,
1855 CM_NON_TAXABLE_AMT,
1856 CM_EXEMPT_AMT,
1857 CM_TAXABLE_VAT_AMT,
1858 CM_TAXABLE_VAT_INV_AMT,
1859 FISCAL_ID_NUM,
1860 VAT_REGISTRATION_NUM,
1861 PARTY_SEQUENCE_NUM,
1862 LAST_UPDATE_DATE,
1863 LAST_UPDATED_BY,
1864 LAST_UPDATE_LOGIN,
1865 CREATION_DATE,
1866 CREATED_BY
1867 )
1868 VALUES
1869 (P_VAT_REPORTING_ENTITY_ID,
1870 P_YEAR_OF_DECLARATION,
1871 l_transnum, --TRANSMISSION_NUM
1872 rec_lines.APPLICATION_ID, --APPLICATION_ID
1873 rec_lines.PARTY_ID, --PARTY_ID
1874 rec_lines.tot_taxable_amt, --TAXABLE_AMT
1875 rec_lines.tot_vat_amt, --VAT_AMT
1876 rec_lines.tot_non_taxable_amt, --NON_TAXABLE_AMT
1877 rec_lines.tot_exempt_amt, --EXEMPT_AMT
1878 rec_lines.tot_taxable_vat_amt, --TAXABLE_VAT_AMT
1879 rec_lines.tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
1880 rec_lines.tot_taxable_amt_cm, --TAXABLE_AMT
1881 rec_lines.tot_vat_amt_cm, --VAT_AMT
1882 rec_lines.tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
1883 rec_lines.tot_exempt_amt_cm, --EXEMPT_AMT
1884 rec_lines.tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
1885 rec_lines.tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
1886 rec_lines.tax_payer_id,
1887 rec_lines.vat_reg_num,
1888 l_seq_num,
1889 g_last_update_date, --LAST_UPDATE_DATE
1890 g_last_updated_by, --LAST_UPDATED_BY
1891 g_last_update_login, --LAST_UPDATE_LOGIN
1892 g_creation_date, --CREATION_DATE
1893 g_created_by --CREATED_BY
1894 );
1895
1896 l_count := l_count + 1;
1897 l_seq_num:= l_seq_num + 1;
1898 END LOOP;
1899
1900 ELSE -- If 'group by' paramater is set to Yes
1901
1902 FOR rec_lines IN cur_trx_lines_group(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION,P_CUST_SORT_COL ,P_VEND_SORT_COL ) LOOP
1903
1904 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1905 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','Inside FOR rec_lines IN cur_trx_lines');
1906 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','l_transnum = '||l_transnum);
1907 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','l_count = '||l_count);
1908 END IF;
1909
1910 IF l_count > nvl(g_rec_per_eft,l_count) THEN
1911 l_transnum := l_transnum + 1;
1912 l_count :=1;
1913 END IF;
1914
1915 INSERT INTO JE_IT_LIST_PARTIES_ALL
1916 (VAT_REPORTING_ENTITY_ID,
1917 YEAR_OF_DECLARATION,
1918 TRANSMISSION_NUM,
1919 APPLICATION_ID,
1920 PARTY_ID,
1921 TAXABLE_AMT,
1922 VAT_AMT,
1923 NON_TAXABLE_AMT,
1924 EXEMPT_AMT,
1925 TAXABLE_VAT_AMT,
1926 TAXABLE_VAT_INV_AMT,
1927 CM_TAXABLE_AMT,
1928 CM_VAT_AMT,
1929 CM_NON_TAXABLE_AMT,
1930 CM_EXEMPT_AMT,
1931 CM_TAXABLE_VAT_AMT,
1932 CM_TAXABLE_VAT_INV_AMT,
1933 FISCAL_ID_NUM,
1934 VAT_REGISTRATION_NUM,
1935 PARTY_SEQUENCE_NUM,
1936 LAST_UPDATE_DATE,
1937 LAST_UPDATED_BY,
1938 LAST_UPDATE_LOGIN,
1939 CREATION_DATE,
1940 CREATED_BY
1941 )
1942 VALUES
1943 (P_VAT_REPORTING_ENTITY_ID,
1944 P_YEAR_OF_DECLARATION,
1945 l_transnum, --TRANSMISSION_NUM
1946 rec_lines.APPLICATION_ID, --APPLICATION_ID
1947 rec_lines.PARTY_ID, --PARTY_ID
1948 rec_lines.tot_taxable_amt, --TAXABLE_AMT
1949 rec_lines.tot_vat_amt, --VAT_AMT
1950 rec_lines.tot_non_taxable_amt, --NON_TAXABLE_AMT
1951 rec_lines.tot_exempt_amt, --EXEMPT_AMT
1952 rec_lines.tot_taxable_vat_amt, --TAXABLE_VAT_AMT
1953 rec_lines.tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
1954 rec_lines.tot_taxable_amt_cm, --TAXABLE_AMT
1955 rec_lines.tot_vat_amt_cm, --VAT_AMT
1956 rec_lines.tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
1957 rec_lines.tot_exempt_amt_cm, --EXEMPT_AMT
1958 rec_lines.tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
1959 rec_lines.tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
1960 rec_lines.tax_payer_id,
1961 rec_lines.vat_reg_num,
1962 l_seq_num,
1963 g_last_update_date, --LAST_UPDATE_DATE
1964 g_last_updated_by, --LAST_UPDATED_BY
1965 g_last_update_login, --LAST_UPDATE_LOGIN
1966 g_creation_date, --CREATION_DATE
1967 g_created_by --CREATED_BY
1968 );
1969
1970 l_count := l_count + 1;
1971 l_seq_num:= l_seq_num + 1;
1972 END LOOP;
1973 END IF;
1974
1975 INSERT INTO JE_IT_LIST_HDR_ALL(
1976 VAT_REPORTING_ENTITY_ID,
1977 YEAR_OF_DECLARATION,
1978 STATUS_CODE,
1979 LAST_UPDATE_DATE,
1980 LAST_UPDATED_BY,
1981 LAST_UPDATE_LOGIN,
1982 CREATION_DATE,
1983 CREATED_BY)
1984 VALUES
1985 (P_VAT_REPORTING_ENTITY_ID,
1986 P_YEAR_OF_DECLARATION,
1987 'P',
1988 g_last_update_date,
1989 g_last_updated_by,
1990 g_last_update_login,
1991 g_creation_date,
1992 g_created_by);
1993
1994 COMMIT;
1995
1996 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1997 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','End PROCEDURE Generate_trx_headers');
1998 END IF;
1999 EXCEPTION
2000 WHEN OTHERS THEN
2001 g_retcode :=2;
2002 g_errbuf :='Exception in JE_IT_LISTING_PKG.Generate_trx_headers';
2003 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2004 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Generate_trx_headers','Exception in PROCEDURE Generate_trx_headers');
2005 END IF;
2006 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2007 IF g_debug_flag = 'Y' THEN
2008 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
2009 END IF;
2010 ROLLBACK TO before_insert_parties;
2011 END Generate_trx_headers;
2012 --------------------------------------------------------------------------------
2013 /*===========================================================================+
2014 | PROCEDURE |
2015 | INITIALIZE_PROC_VAR |
2016 | DESCRIPTION |
2017 | This procedure initializes all the Package variables |
2018 | |
2019 | SCOPE - Private |
2020 | |
2021 | NOTES |
2022 | |
2023 | MODIFICATION HISTORY |
2024 | Date Author Description |
2025 | ============ ============== ================================= |
2026 | 14-Dec-2007 SPASUPUN Initial Version. |
2027 | |
2028 +===========================================================================*/
2029 --------------------------------------------------------------------------------
2030 PROCEDURE Initialize_proc_var( P_VAT_REPORTING_ENTITY_ID IN NUMBER,
2031 P_YEAR_OF_DECLARATION IN NUMBER) IS
2032
2033 t_chart_of_accounts_id NUMBER;
2034 t_set_of_books_name VARCHAR2(30);
2035 t_func_curr VARCHAR2(15);
2036 t_errorbuffer VARCHAR2(132);
2037 t_date DATE;
2038
2039 CURSOR entity_details IS
2040 SELECT repent.ledger_id,
2041 repent.balancing_segment_value,
2042 gl.chart_of_accounts_id
2043 FROM jg_zz_vat_rep_entities repent
2044 ,gl_ledgers gl
2045 WHERE vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
2046 AND gl.ledger_id = repent.ledger_id;
2047
2048 CURSOR c_get_le_and_period_dates
2049 is
2050 SELECT nvl(cfg.legal_entity_id,cfgd.legal_entity_id)
2051 ,nvl(cfg.tax_registration_number,cfgd.tax_registration_number) repent_trn
2052 ,min(glp.start_date)
2053 ,max(glp.end_date)
2054 FROM jg_zz_vat_rep_entities cfg
2055 ,jg_zz_vat_rep_entities cfgd
2056 ,gl_periods glp
2057 WHERE cfg.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
2058 and (
2059 ( cfg.entity_type_code = 'ACCOUNTING'
2060 and cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
2061 )
2062 or
2063 ( cfg.entity_type_code = 'LEGAL'
2064 and cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
2065 )
2066 )
2067 AND glp.period_set_name = nvl(cfg.tax_calendar_name,cfgd.tax_calendar_name)
2068 AND glp.period_year = P_YEAR_OF_DECLARATION
2069 GROUP BY nvl(cfg.legal_entity_id,cfgd.legal_entity_id)
2070 ,nvl(cfg.tax_registration_number,cfgd.tax_registration_number)
2071 ,nvl(cfg.entity_identifier,cfgd.entity_identifier);
2072
2073 CURSOR c_currency_vat_reg_num
2074 IS
2075 SELECT gllev.currency_code
2076 ,hl.country
2077 FROM gl_ledger_le_v gllev
2078 ,gl_ledgers gl
2079 ,xle_registrations xr
2080 ,xle_entity_profiles xep
2081 ,hr_locations_all hl
2082 WHERE gllev.ledger_category_code='PRIMARY'
2083 AND gllev.legal_entity_id = gn_legal_entity_id
2084 AND gl.ledger_id = gllev.ledger_id
2085 AND xep.legal_entity_id = gllev.legal_entity_id
2086 AND xr.source_id = xep.legal_entity_id
2087 AND xr.source_table = 'XLE_ENTITY_PROFILES'
2088 AND xr.location_id = hl.location_id
2089 AND xr.identifying_flag = 'Y';
2090
2091 BEGIN
2092
2093 --Setting the application id for AP and AR
2094
2095 gn_ap_app_id:=200; --AP
2096 gn_ar_app_id:=222; --AR
2097
2098 g_lines_per_commit :=1000;
2099 g_retcode :=0;
2100
2101 g_debug_flag := NVL(fnd_profile.value('aflog_enabled'), 'n');
2102
2103 BEGIN
2104
2105 OPEN entity_details;
2106 FETCH entity_details
2107 INTO gv_ledger_id,
2108 gv_balancing_segment_value,
2109 gv_chart_of_accounts_id;
2110 CLOSE entity_details;
2111
2112 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2113 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','gv_ledger_id = '||gv_ledger_id);
2114 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','gv_balancing_segment_value = '||gv_balancing_segment_value);
2115 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','gv_chart_of_accounts_id = '||gv_chart_of_accounts_id);
2116 END IF;
2117
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2121 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','Exception in Fetching ledger id,balancing segment value and chart of account id');
2122 END IF;
2123 g_retcode :=2;
2124 g_errbuf :='Exception in Fetching ledger id,balancing segment value and chart of account id';
2125 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2126 IF g_debug_flag = 'Y' THEN
2127 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
2128 END IF;
2129 END;
2130
2131 BEGIN
2132 OPEN c_get_le_and_period_dates;
2133 FETCH c_get_le_and_period_dates
2134 INTO gn_legal_entity_id
2135 ,gv_repent_trn
2136 ,gd_period_start_date
2137 ,gd_period_end_date;
2138 CLOSE c_get_le_and_period_dates;
2139
2140 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2141 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','gn_legal_entity_id = '||gn_legal_entity_id);
2142 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','gv_repent_trn = '||gv_repent_trn);
2143 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','gd_period_start_date = '||gd_period_start_date);
2144 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','gd_period_end_date = '||gd_period_end_date);
2145 END IF;
2146
2147 EXCEPTION
2148 WHEN OTHERS THEN
2149 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2150 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','Exception in Fetching start and end date of the declaration year');
2151 END IF;
2152 g_retcode :=2;
2153 g_errbuf :='Exception in Fetching start and end date of the declaration year';
2154 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2155 IF g_debug_flag = 'Y' THEN
2156 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
2157 END IF;
2158 END;
2159
2160
2161
2162 BEGIN
2163 OPEN c_currency_vat_reg_num ;
2164 FETCH c_currency_vat_reg_num
2165 INTO gv_currency_code
2166 ,gv_vat_country_code ;
2167 CLOSE c_currency_vat_reg_num;
2168
2169 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2170 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','gv_currency_code = '||gv_currency_code);
2171 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','gv_vat_country_code = '||gv_vat_country_code);
2172 END IF;
2173
2174
2175 EXCEPTION
2176 WHEN OTHERS THEN
2177 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2178 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','Exception in Fetching start and end date of the declaration year');
2179 END IF;
2180 g_retcode :=2;
2181 g_errbuf :='Exception in Fetching start and end date of the declaration year';
2182 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2183 IF g_debug_flag = 'Y' THEN
2184 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
2185 END IF;
2186 END;
2187
2188
2189 ----------------------------
2190 --initalize who variables
2191 ---------------------------
2192 g_created_by := NVL(fnd_profile.value('USER_ID'),1);
2193 g_creation_date := SYSDATE;
2194 g_last_updated_by := NVL(fnd_profile.value('USER_ID'),1);
2195 g_last_update_date := SYSDATE;
2196 g_last_update_login := 1;
2197
2198 EXCEPTION
2199 WHEN OTHERS THEN
2200 g_retcode :=2;
2201 g_errbuf :='Exception in JE_IT_LISTING_PKG.Initialize_proc_var';
2202 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2203 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Initialize_proc_var','Exception in PROCEDURE Initialize_proc_var');
2204 END IF;
2205 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2206 IF g_debug_flag = 'Y' THEN
2207 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
2208 END IF;
2209 END Initialize_proc_var;
2210 -----------------------------------------------------------------------------------
2211
2212 /*
2213 REM +======================================================================+
2214 REM Name: get_bsv
2215 REM
2216 REM Description: This function is called in the generic cursor for getting the
2217 REM BSV for each invoice distribution.
2218 REM
2219 REM
2220 REM Parameters: ccid (code combination id)
2221 REM
2222 REM +======================================================================+
2223 */
2224
2225 FUNCTION get_bsv(ccid number) RETURN VARCHAR2 IS
2226
2227 l_segment VARCHAR2(30);
2228 bal_segment_value VARCHAR2(25);
2229
2230 BEGIN
2231
2232 SELECT application_column_name
2233 INTO l_segment
2234 FROM fnd_segment_attribute_values ,
2235 gl_ledgers gl
2236 WHERE id_flex_code = 'GL#'
2237 AND attribute_value = 'Y'
2238 AND segment_attribute_type = 'GL_BALANCING'
2239 AND application_id = 101
2240 AND gl.chart_of_accounts_id = gv_chart_of_accounts_id
2241 AND gl.ledger_id = gv_ledger_id;
2242
2243 EXECUTE IMMEDIATE 'SELECT '||l_segment ||
2244 ' FROM gl_code_combinations '||
2245 ' WHERE code_combination_id = '||ccid
2246 INTO bal_segment_value;
2247
2248 RETURN (bal_segment_value);
2249
2250 EXCEPTION
2251 WHEN NO_DATA_FOUND THEN
2252 g_retcode :=2;
2253 g_errbuf :='Exception in JE_IT_LISTING_PKG.get_bsv';
2254 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2255 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.get_bsv','Exception in FUNCTIONI get_bsv');
2256 END IF;
2257 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
2258 IF g_debug_flag = 'Y' THEN
2259 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
2260 END IF;
2261 RETURN NULL;
2262 END get_bsv;
2263
2264 END JE_IT_LISTING_PKG;
2265