[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_VAT_PRE_REP_PROC_PKG
Source
1 PACKAGE BODY JG_ZZ_VAT_PRE_REP_PROC_PKG AS
2 /* $Header: jgzzprpb.pls 120.0.12010000.4 2010/01/07 19:37:37 spasupun noship $ */
3 -----------------------------------------
4 --Public Variable Declarations
5 -----------------------------------------
6 g_current_runtime_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
8 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
9 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 g_level_error CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
12 g_level_exception CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
13 g_error_buffer VARCHAR2(100);
14 g_debug_flag VARCHAR2(1);
15 g_pkg_name CONSTANT VARCHAR2(30) := 'JG_ZZ_VAT_PRE_REP_PROC_PKG';
16 g_module_name CONSTANT VARCHAR2(30) := 'JG_ZZ_VAT_PRE_REP_PROC_PKG';
17
18 /*
19 REM +======================================================================+
20 REM Name: get_bsv
21 REM
22 REM Description: This function is called for getting the
23 REM BSV for each invoice distribution.
24 REM
25 REM
26 REM Parameters: p_ccid (Code Combination ID)
27 REM p_chart_of_accounts_id (Chart of account)
28 REM p_ledger_id (Ledger ID)
29 REM +======================================================================+
30 */
31
32 FUNCTION GET_BSV
33 ( p_ccid NUMBER,
34 p_chart_of_accounts_id NUMBER,
35 p_ledger_id NUMBER)
36 RETURN VARCHAR2
37 IS
38 l_segment VARCHAR2(30);
39 bal_segment_value VARCHAR2(25):= '1';
40 BEGIN
41
42 SELECT application_column_name
43 INTO l_segment
44 FROM fnd_segment_attribute_values ,
45 gl_ledgers gl
46 WHERE id_flex_code = 'GL#'
47 AND attribute_value = 'Y'
48 AND segment_attribute_type = 'GL_BALANCING'
49 AND application_id = 101
50 AND id_flex_num = gl.chart_of_accounts_id
51 AND gl.chart_of_accounts_id = p_chart_of_accounts_id
52 AND gl.ledger_id = p_ledger_id;
53
54 EXECUTE IMMEDIATE 'SELECT '||l_segment || ' FROM gl_code_combinations '
55 || ' WHERE code_combination_id = '||p_ccid INTO bal_segment_value;
56
57 RETURN (bal_segment_value);
58
59 EXCEPTION
60 WHEN NO_DATA_FOUND THEN
61 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Balancing segment. Error : ' || SUBSTR(SQLERRM,1,200));
62 RETURN NULL;
63 WHEN OTHERS THEN
64 fnd_file.put_line(fnd_file.log,' Error in GET_BSV function : ' || SUBSTR(SQLERRM,1,200));
65 RETURN NULL;
66 END get_bsv;
67
68 -----------------------------------------
69 --Public Methods
70 -----------------------------------------
71 --
72 /*===========================================================================+
73 | PROCEDURE |
74 | main() |
75 | |
76 | DESCRIPTION |
77 | |
78 | |
79 | SCOPE - Public |
80 | |
81 | NOTES |
82 | |
83 +===========================================================================*/
84 PROCEDURE main
85 ( errbuf OUT NOCOPY VARCHAR2,
86 retcode OUT NOCOPY NUMBER,
87 p_reporting_level IN jg_zz_vat_rep_entities.entity_level_code%TYPE,
88 p_vat_reporting_entity_id IN jg_zz_vat_rep_entities.vat_reporting_entity_id%TYPE,
89 p_chart_of_account_id IN NUMBER,
90 p_bsv IN jg_zz_vat_rep_entities.balancing_segment_value%TYPE,
91 p_period IN jg_zz_vat_rep_status.tax_calendar_period%TYPE
92 ) IS
93
94 l_return_status VARCHAR2(1);
95 l_return_message VARCHAR2(1000);
96
97 CURSOR c_last_rep_period_csr (p_vat_reporting_entity_id jg_zz_vat_rep_entities.vat_reporting_entity_id%TYPE)
98 IS
99 SELECT last_reported_period
100 FROM jg_zz_vat_rep_entities
101 WHERE vat_reporting_entity_id = p_vat_reporting_entity_id;
102
103 CURSOR c_is_mgr_trx_exist(pn_vat_rep_entity_id number)
104 IS
105 SELECT JGTRD.trx_id
106 FROM jg_zz_vat_trx_details JGTRD,
107 jg_zz_vat_rep_status JGREPS,
108 zx_lines ZX
109 WHERE JGREPS.vat_reporting_entity_id = pn_vat_rep_entity_id
110 AND JGREPS.reporting_status_id = JGTRD.reporting_status_id
111 AND JGREPS.source = JGTRD.extract_source_ledger
112 AND ZX.trx_id = JGTRD.trx_id
113 AND JGTRD.created_by = 1
114 AND ZX.record_type_code = 'MIGRATED'
115 AND ZX.application_id = JGTRD.application_id
116 AND ZX.entity_code = JGTRD.entity_code
117 AND ZX.event_class_code = JGTRD.event_class_code
118 AND rownum=1;
119
120 l_country XLE_FIRSTPARTY_INFORMATION_V.country%TYPE;
121 l_legal_entity_id JG_ZZ_VAT_REP_ENTITIES.legal_entity_id%TYPE;
122 l_ledger JG_ZZ_VAT_REP_ENTITIES.ledger_id%TYPE;
123 l_driving_date_code JG_ZZ_VAT_REP_ENTITIES.driving_date_code%TYPE;
124 l_driving_date_code_es JG_ZZ_VAT_REP_ENTITIES.driving_date_code%TYPE;
125 l_mapping_rep_entity_id JG_ZZ_VAT_REP_ENTITIES.mapping_vat_rep_entity_id%TYPE;
126 l_tax_calendar_name JG_ZZ_VAT_REP_ENTITIES.tax_calendar_name%TYPE;
127 l_last_reported_period JG_ZZ_VAT_REP_ENTITIES.last_reported_period%TYPE;
128 l_bsv_vat_rep_entity_id JG_ZZ_VAT_REP_ENTITIES.vat_reporting_entity_id%TYPE;
129 l_ledger_id JG_ZZ_VAT_REP_ENTITIES.ledger_id%TYPE;
130 l_start_date DATE;
131 l_end_date DATE;
132 l_is_mgr_trx_exist NUMBER(15);
133
134
135 l_update_query_ap VARCHAR2(5000);
136 l_update_query_ar VARCHAR2(5000);
137
138 l_is_upgrade_customer NUMBER := 0;
139
140 CURSOR c_is_upgrade_customer IS
141 SELECT 1
142 FROM zx_lines
143 WHERE record_type_code= 'MIGRATED'
144 AND rownum=1;
145
146 BEGIN
147
148 fnd_file.put_line(fnd_file.log,'p_reporting_level: '
149 ||p_reporting_level);
150 fnd_file.put_line(fnd_file.log,'p_vat_reporting_entity_id: '
151 ||p_vat_reporting_entity_id);
152 fnd_file.put_line(fnd_file.log,'p_bsv: '
153 ||p_bsv);
154 fnd_file.put_line(fnd_file.log,'p_period: '
155 ||p_period);
156
157 OPEN c_is_upgrade_customer;
158 FETCH c_is_upgrade_customer INTO l_is_upgrade_customer;
159 CLOSE c_is_upgrade_customer;
160
161 IF l_is_upgrade_customer = 0 THEN
162
163 FND_MESSAGE.SET_NAME('JG','JG_ZZ_VAT_PRE_REP_PROC_NREQ');
164 fnd_file.put_line(fnd_file.log,FND_MESSAGE.GET);
165 return;
166
167 END IF;
168
169 SELECT LEGAL.driving_date_code
170 ,ACCT.mapping_vat_rep_entity_id
171 ,LEGAL.tax_calendar_name
172 ,ACCT.ledger_id
173 ,LEGAL.legal_entity_id
174 INTO l_driving_date_code
175 ,l_mapping_rep_entity_id
176 ,l_tax_calendar_name
177 ,l_ledger_id
178 ,l_legal_entity_id
179 FROM JG_ZZ_VAT_REP_ENTITIES LEGAL
180 ,JG_ZZ_VAT_REP_ENTITIES ACCT
181 WHERE ACCT.VAT_REPORTING_ENTITY_ID = p_vat_reporting_entity_id
182 AND LEGAL.VAT_REPORTING_ENTITY_ID = ACCT.mapping_vat_rep_entity_id;
183
184 -- get country code
185
186 SELECT xle.country
187 INTO l_country
188 FROM xle_firstparty_information_v xle
189 WHERE xle.legal_entity_id = l_legal_entity_id;
190
191 IF p_reporting_level = 'LEDGER' THEN
192
193 OPEN c_last_rep_period_csr(p_vat_reporting_entity_id);
194 FETCH c_last_rep_period_csr
195 INTO l_last_reported_period;
196 CLOSE c_last_rep_period_csr;
197
198 OPEN c_is_mgr_trx_exist(p_vat_reporting_entity_id);
199 FETCH c_is_mgr_trx_exist
200 INTO l_is_mgr_trx_exist;
201 CLOSE c_is_mgr_trx_exist ;
202
203 ELSE -- p_reporting_level = 'BSV'
204
205 JG_ZZ_VAT_REP_UTILITY.maintain_selection_entities( pv_entity_level_code => p_reporting_level
206 , pn_vat_reporting_entity_id => l_mapping_rep_entity_id
207 , pn_ledger_id => l_ledger_id
208 , pv_balancing_segment_value => p_bsv
209 , xn_vat_reporting_entity_id => l_bsv_vat_rep_entity_id
210 , xv_return_status => l_return_status
211 , xv_return_message => l_return_message
212 );
213
214 IF l_return_status IN ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR) THEN
215 errbuf := l_return_message;
216 retcode := 2;
217 RETURN;
218 END IF;
219
220 OPEN c_last_rep_period_csr(l_bsv_vat_rep_entity_id);
221 FETCH c_last_rep_period_csr INTO l_last_reported_period;
222 CLOSE c_last_rep_period_csr;
223
224 OPEN c_is_mgr_trx_exist(l_bsv_vat_rep_entity_id);
225 FETCH c_is_mgr_trx_exist
226 INTO l_is_mgr_trx_exist;
227 CLOSE c_is_mgr_trx_exist ;
228
229 END IF; -- IF p_reporting_level = 'LEDGER' THEN
230
231 IF l_last_reported_period IS NOT NULL THEN
232 FND_MESSAGE.SET_NAME('JG','JG_ZZ_VAT_PRE_REP_PROC_RAN');
233 errbuf := FND_MESSAGE.GET;
234 retcode := 2;
235 RETURN;
236
237 ELSIF l_last_reported_period IS NULL AND l_is_mgr_trx_exist IS NOT NULL THEN
238
239 FND_MESSAGE.SET_NAME('JG','JG_ZZ_VAT_PRE_REP_PROC_NREQ');
240 errbuf := FND_MESSAGE.GET;
241 retcode := 2;
242 RETURN;
243
244 END IF;
245
246
247 fnd_file.put_line(fnd_file.log,'Ledger ID: '
248 ||l_ledger_id);
249 fnd_file.put_line(fnd_file.log,'BSV VAT Reporting Entity ID: '
250 ||l_bsv_vat_rep_entity_id);
251 fnd_file.put_line(fnd_file.log,'Driving Date Code: '
252 ||l_driving_date_code);
253 fnd_file.put_line(fnd_file.log,'Lega VAT Reporting Entity ID: '
254 ||l_mapping_rep_entity_id);
255
256 SELECT start_date ,
257 end_date
258 INTO l_start_date ,
259 l_end_date
260 FROM GL_PERIODS
261 WHERE period_set_name = l_tax_calendar_name
262 AND period_name = p_period;
263
264 IF l_driving_date_code = 'GL-TRX' THEN
265 l_driving_date_code_es := l_driving_date_code;
266 l_driving_date_code := 'GL';
267 END IF;
268
269 << continue_loop >>
270
271 l_update_query_ap :=
272 'UPDATE zx_lines zxl
273 SET zxl.legal_reporting_status =''000000000000000''
274 ,LAST_UPDATED_BY = -5
275 ,LAST_UPDATE_DATE = sysdate
276 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
277 WHERE zxl.legal_reporting_status = ''111111111111111''
278 AND zxl.record_type_code = ''MIGRATED''
279 AND zxl.ledger_id = $l_ledger_id$
280 AND zxl.legal_entity_id = $l_legal_entity_id$
281 AND zxl.trx_id NOT IN
282 (SELECT stg.trx_id
283 FROM jg_zz_vat_trx_upg_stg stg
284 WHERE stg.application_id = zxl.application_id
285 AND stg.EVENT_CLASS_CODE = zxl.EVENT_CLASS_CODE
286 AND stg.ENTITY_CODE = zxl.entity_code
287 )
288 AND zxl.trx_id NOT IN
289 (SELECT trxd.trx_id
290 FROM jg_zz_vat_rep_Status reps ,
291 jg_zz_vat_trx_details trxd
292 WHERE reps.reporting_status_id = trxd.reporting_status_id
293 AND reps.final_reporting_status_flag IS NOT NULL
294 AND reps.final_reporting_process_id IS NOT NULL
295 AND reps.final_reporting_process_date IS NOT NULL
296 )';
297
298 l_update_query_ar := l_update_query_ap;
299
300 IF l_driving_date_code = 'TRX' THEN
301
302 l_update_query_ap := l_update_query_ap||
303 ' AND zxl.application_id = 200
304 AND zxl.trx_date > $l_end_date$';
305
306 l_update_query_ar := l_update_query_ar||
307 ' AND zxl.application_id = 222
308 AND zxl.trx_date > $l_end_date$';
309
310
311 IF p_reporting_level = 'BSV' THEN
312
313 l_update_query_ap := l_update_query_ap ||
314 ' AND zxl.trx_id IN
315 (SELECT apd.invoice_id trx_id
316 FROM ap_invoice_distributions_all apd
317 ,ap_invoices_all apinv
318 WHERE apinv.invoice_id = apd.invoice_id
319 AND apinv.invoice_date > $l_end_date$
320 AND apinv.set_of_books_id = $l_ledger_id$
321 AND apinv.legal_entity_id = $l_legal_entity_id$
322 AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(apd.dist_code_combination_id ,$p_chart_of_account_id$ ,$l_ledger_id$) = ''$p_bsv$''
323 )';
324
325 l_update_query_ar := l_update_query_ar ||
326 ' AND zxl.trx_id IN
327 ( SELECT rtd.customer_trx_id
328 FROM ra_cust_trx_line_gl_dist_all rtd ,
329 ra_customer_trx_all rinv
330 WHERE rinv.customer_trx_id = rtd.customer_trx_id
331 AND rinv.trx_date > $l_end_date$
332 AND rinv.set_of_books_id = $l_ledger_id$
333 AND rinv.legal_entity_id = $l_legal_entity_id$
334 AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(rtd.CODE_COMBINATION_ID ,$p_chart_of_account_id$ ,$l_ledger_id$) = ''$p_bsv$''
335 )';
336
337
338 END IF; -- p_reporting_level = 'BSV'
339
340 ELSIF l_driving_date_code = 'GL' THEN
341
342 l_update_query_ap := l_update_query_ap||
343 ' AND zxl.application_id = 200
344 AND zxl.trx_id IN
345 (SELECT invoice_id
346 FROM ap_invoices_all
347 WHERE gl_date > $l_end_date$
348 AND set_of_books_id = $l_ledger_id$
349 AND legal_entity_id = $l_legal_entity_id$
350 )';
351
352 l_update_query_ar := l_update_query_ar||
353 ' AND zxl.application_id = 222
354 AND zxl.trx_id IN
355 (SELECT customer_trx_id
356 FROM ra_cust_trx_line_gl_dist_all
357 WHERE gl_date > $l_end_date$
358 AND set_of_books_id = $l_ledger_id$
359 ) ';
360
361 IF p_reporting_level = 'BSV' THEN
362
363 l_update_query_ap := l_update_query_ap||
364 ' AND zxl.trx_id IN
365 (SELECT apd.invoice_id trx_id
366 FROM ap_invoice_distributions_all apd ,
367 ap_invoices_all apinv
368 WHERE apinv.invoice_id = apd.invoice_id
369 AND apinv.gl_date > $l_end_date$
370 AND apinv.set_of_books_id = $l_ledger_id$
371 AND apinv.legal_entity_id = $l_legal_entity_id$
372 AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(apd.dist_code_combination_id ,$p_chart_of_account_id$ ,$l_ledger_id$) = ''$p_bsv$''
373 ) ';
374
375 l_update_query_ar := l_update_query_ar||
376 ' AND zxl.trx_id IN
377 (SELECT rtd.customer_trx_id
378 FROM ra_cust_trx_line_gl_dist_all rtd ,
379 ra_customer_trx_all rinv
380 WHERE rinv.customer_trx_id = rtd.customer_trx_id
381 AND rtd.gl_date > $l_end_date$
382 AND rinv.set_of_books_id = $l_ledger_id$
383 AND rinv.legal_entity_id = $l_legal_entity_id$
384 AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(rtd.CODE_COMBINATION_ID ,$p_chart_of_account_id$ ,$l_ledger_id$) = ''$p_bsv$''
385 )';
386
387 END IF; --p_reporting_level = 'BSV'
388
389 ELSIF l_driving_date_code = 'TID' THEN
390
391 l_update_query_ap := l_update_query_ap ||
392 ' AND zxl.application_id = 200
393 AND zxl.trx_id IN
394 (SELECT zxd.trx_id
395 FROM zx_lines_det_factors zxd
396 WHERE zxd.tax_invoice_date > $l_end_date$
397 AND zxd.application_id = zxl.application_id
398 AND zxd.EVENT_CLASS_CODE = zxl.EVENT_CLASS_CODE
399 AND zxd.ENTITY_CODE = zxl.entity_code
400 AND zxd.legal_entity_id = $l_legal_entity_id$
401 AND zxd.ledger_id = $l_ledger_id$
402 )';
403
404 l_update_query_ar := l_update_query_ar ||
405 ' AND zxl.application_id = 222
406 AND zxl.trx_id IN
407 (SELECT zxd.trx_id
408 FROM zx_lines_det_factors zxd
409 WHERE zxd.tax_invoice_date > $l_end_date$
410 AND zxd.application_id = zxl.application_id
411 AND zxd.EVENT_CLASS_CODE = zxl.EVENT_CLASS_CODE
412 AND zxd.ENTITY_CODE = zxl.entity_code
413 AND zxd.legal_entity_id = $l_legal_entity_id$
414 AND zxd.ledger_id = $l_ledger_id$
415 )';
416
417 IF p_reporting_level = 'BSV' THEN
418
419 l_update_query_ap := l_update_query_ap ||
420 ' AND zxl.trx_id IN
421 (SELECT apd.invoice_id trx_id
422 FROM ap_invoice_distributions_all apd ,
423 ap_invoices_all apinv
424 WHERE apinv.invoice_id = apd.invoice_id
425 AND apinv.set_of_books_id = $l_ledger_id$
426 AND apinv.legal_entity_id = $l_legal_entity_id$
427 AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(apd.dist_code_combination_id ,$p_chart_of_account_id$ ,$l_ledger_id$) = ''$p_bsv$''
428 )';
429
430 l_update_query_ar := l_update_query_ar ||
431 ' AND zxl.trx_id IN
432 ( SELECT rtd.customer_trx_id
433 FROM ra_cust_trx_line_gl_dist_all rtd ,
434 ra_customer_trx_all rinv
435 WHERE rinv.customer_trx_id = rtd.customer_trx_id
436 AND rinv.set_of_books_id = $l_ledger_id$
437 AND rinv.legal_entity_id = $l_legal_entity_id$
438 AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(rtd.CODE_COMBINATION_ID,$p_chart_of_account_id$,$l_ledger_id$) = ''$p_bsv$''
439 )';
440
441 END IF; --p_reporting_level = 'BSV'
442 END IF;
443
444
445 l_update_query_ap := REPLACE( l_update_query_ap,'$l_ledger_id$',l_ledger_id);
446 l_update_query_ap := REPLACE( l_update_query_ap,'$l_end_date$',''''||l_end_date||'''');
447 l_update_query_ap := REPLACE( l_update_query_ap,'$l_legal_entity_id$',l_legal_entity_id);
448 l_update_query_ap := REPLACE( l_update_query_ap,'$p_chart_of_account_id$',p_chart_of_account_id);
449 l_update_query_ap := REPLACE( l_update_query_ap,'$p_bsv$',p_bsv);
450
451 l_update_query_ar := REPLACE( l_update_query_ar,'$l_ledger_id$',l_ledger_id);
452 l_update_query_ar := REPLACE( l_update_query_ar,'$l_end_date$',''''||l_end_date||'''');
453 l_update_query_ar := REPLACE( l_update_query_ar,'$l_legal_entity_id$',l_legal_entity_id);
454 l_update_query_ar := REPLACE( l_update_query_ar,'$p_chart_of_account_id$',p_chart_of_account_id);
455 l_update_query_ar := REPLACE( l_update_query_ar,'$p_bsv$',p_bsv);
456
457 fnd_file.put_line(fnd_file.log,'l_update_query_ap :='||l_update_query_ap);
458 fnd_file.put_line(fnd_file.log,'l_update_query_ar :='||l_update_query_ar);
459
460 EXECUTE IMMEDIATE l_update_query_ap;
461
462 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Driving Date Code :'||l_driving_date_code);
463 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AP Rows updated: '||To_char(SQL%RowCount));
464
465 EXECUTE IMMEDIATE l_update_query_ar;
466 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Driving Date Code :'||l_driving_date_code);
467 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AR Rows updated: '||To_char(SQL%RowCount));
468
469 IF l_driving_date_code_es = 'GL-TRX' THEN
470 l_driving_date_code := 'TRX';
471 l_driving_date_code_es := NULL;
472 goto continue_loop;
473 END IF;
474
475
476 IF p_reporting_level = 'LEDGER' THEN
477
478 UPDATE JG_ZZ_VAT_REP_ENTITIES
479 SET LAST_REPORTED_PERIOD = p_period
480 WHERE VAT_REPORTING_ENTITY_ID = p_vat_reporting_entity_id;
481
482 ELSE --p_reporting_level = 'BSV'
483 UPDATE JG_ZZ_VAT_REP_ENTITIES
484 SET LAST_REPORTED_PERIOD = p_period
485 WHERE VAT_REPORTING_ENTITY_ID = l_bsv_vat_rep_entity_id;
486 END IF;
487
488 EXCEPTION
489 WHEN OTHERS THEN
490 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
491 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
492 FND_MSG_PUB.Add;
493 IF (g_level_unexpected >= g_current_runtime_level ) THEN
494 FND_LOG.STRING(g_level_unexpected, G_MODULE_NAME, g_error_buffer);
495 END IF;
496 retcode := 2;
497 END main;
498 END JG_ZZ_VAT_PRE_REP_PROC_PKG;