[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.2 2008/12/02 05:53:58 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 BEGIN
138
139 fnd_file.put_line(fnd_file.log,'p_reporting_level: '
140 ||p_reporting_level);
141 fnd_file.put_line(fnd_file.log,'p_vat_reporting_entity_id: '
142 ||p_vat_reporting_entity_id);
143 fnd_file.put_line(fnd_file.log,'p_bsv: '
144 ||p_bsv);
145 fnd_file.put_line(fnd_file.log,'p_period: '
146 ||p_period);
147
148 SELECT LEGAL.driving_date_code
149 ,ACCT.mapping_vat_rep_entity_id
150 ,LEGAL.tax_calendar_name
151 ,ACCT.ledger_id
152 ,LEGAL.legal_entity_id
153 INTO l_driving_date_code
154 ,l_mapping_rep_entity_id
155 ,l_tax_calendar_name
156 ,l_ledger_id
157 ,l_legal_entity_id
158 FROM JG_ZZ_VAT_REP_ENTITIES LEGAL
159 ,JG_ZZ_VAT_REP_ENTITIES ACCT
160 WHERE ACCT.VAT_REPORTING_ENTITY_ID = p_vat_reporting_entity_id
161 AND LEGAL.VAT_REPORTING_ENTITY_ID = ACCT.mapping_vat_rep_entity_id;
162
163 -- get country code
164
165 SELECT xle.country
166 INTO l_country
167 FROM xle_firstparty_information_v xle
168 WHERE xle.legal_entity_id = l_legal_entity_id;
169
170 IF p_reporting_level = 'LEDGER' THEN
171
172 OPEN c_last_rep_period_csr(p_vat_reporting_entity_id);
173 FETCH c_last_rep_period_csr
174 INTO l_last_reported_period;
175 CLOSE c_last_rep_period_csr;
176
177 OPEN c_is_mgr_trx_exist(p_vat_reporting_entity_id);
178 FETCH c_is_mgr_trx_exist
179 INTO l_is_mgr_trx_exist;
180 CLOSE c_is_mgr_trx_exist ;
181
182 ELSE -- p_reporting_level = 'BSV'
183
184 JG_ZZ_VAT_REP_UTILITY.maintain_selection_entities( pv_entity_level_code => p_reporting_level
185 , pn_vat_reporting_entity_id => l_mapping_rep_entity_id
186 , pn_ledger_id => l_ledger_id
187 , pv_balancing_segment_value => p_bsv
188 , xn_vat_reporting_entity_id => l_bsv_vat_rep_entity_id
189 , xv_return_status => l_return_status
190 , xv_return_message => l_return_message
191 );
192
193 IF l_return_status IN ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR) THEN
194 errbuf := l_return_message;
195 retcode := 2;
196 RETURN;
197 END IF;
198
199 OPEN c_last_rep_period_csr(l_bsv_vat_rep_entity_id);
200 FETCH c_last_rep_period_csr INTO l_last_reported_period;
201 CLOSE c_last_rep_period_csr;
202
203 OPEN c_is_mgr_trx_exist(l_bsv_vat_rep_entity_id);
204 FETCH c_is_mgr_trx_exist
205 INTO l_is_mgr_trx_exist;
206 CLOSE c_is_mgr_trx_exist ;
207
208 END IF; -- IF p_reporting_level = 'LEDGER' THEN
209
210 IF l_last_reported_period IS NOT NULL THEN
211 FND_MESSAGE.SET_NAME('JG','JG_ZZ_VAT_PRE_REP_PROC_RAN');
212 errbuf := FND_MESSAGE.GET;
213 retcode := 2;
214 RETURN;
215
216 ELSIF l_last_reported_period IS NULL AND l_is_mgr_trx_exist IS NOT NULL THEN
217
218 FND_MESSAGE.SET_NAME('JG','JG_ZZ_VAT_PRE_REP_PROC_NREQ');
219 errbuf := FND_MESSAGE.GET;
220 retcode := 2;
221 RETURN;
222
223 END IF;
224
225
226 fnd_file.put_line(fnd_file.log,'Ledger ID: '
227 ||l_ledger_id);
228 fnd_file.put_line(fnd_file.log,'BSV VAT Reporting Entity ID: '
229 ||l_bsv_vat_rep_entity_id);
230 fnd_file.put_line(fnd_file.log,'Driving Date Code: '
231 ||l_driving_date_code);
232 fnd_file.put_line(fnd_file.log,'Lega VAT Reporting Entity ID: '
233 ||l_mapping_rep_entity_id);
234
235 SELECT start_date ,
236 end_date
237 INTO l_start_date ,
238 l_end_date
239 FROM GL_PERIODS
240 WHERE period_set_name = l_tax_calendar_name
241 AND period_name = p_period;
242
243 IF l_driving_date_code = 'GL-TRX' THEN
244 l_driving_date_code_es := l_driving_date_code;
245 l_driving_date_code := 'GL';
246 END IF;
247
248 << continue_loop >>
249
250 l_update_query_ap :=
251 'UPDATE zx_lines zxl
252 SET zxl.legal_reporting_status =''000000000000000''
253 ,LAST_UPDATED_BY = -5
254 ,LAST_UPDATE_DATE = sysdate
255 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
256 WHERE zxl.legal_reporting_status = ''111111111111111''
257 AND zxl.record_type_code = ''MIGRATED''
258 AND zxl.ledger_id = $l_ledger_id$
259 AND zxl.legal_entity_id = $l_legal_entity_id$
260 AND zxl.trx_id NOT IN
261 (SELECT stg.trx_id
262 FROM jg_zz_vat_trx_upg_stg stg
263 WHERE stg.application_id = zxl.application_id
264 AND stg.EVENT_CLASS_CODE = zxl.EVENT_CLASS_CODE
265 AND stg.ENTITY_CODE = zxl.entity_code
266 )
267 AND zxl.trx_id NOT IN
268 (SELECT trxd.trx_id
269 FROM jg_zz_vat_rep_Status reps ,
270 jg_zz_vat_trx_details trxd
271 WHERE reps.reporting_status_id = trxd.reporting_status_id
272 AND reps.final_reporting_status_flag IS NOT NULL
273 AND reps.final_reporting_process_id IS NOT NULL
274 AND reps.final_reporting_process_date IS NOT NULL
275 )';
276
277 l_update_query_ar := l_update_query_ap;
278
279 IF l_driving_date_code = 'TRX' THEN
280
281 l_update_query_ap := l_update_query_ap||
282 ' AND zxl.application_id = 200
283 AND zxl.trx_date > $l_end_date$';
284
285 l_update_query_ar := l_update_query_ar||
286 ' AND zxl.application_id = 222
287 AND zxl.trx_date > $l_end_date$';
288
289
290 IF p_reporting_level = 'BSV' THEN
291
292 l_update_query_ap := l_update_query_ap ||
293 ' AND zxl.trx_id IN
294 (SELECT apd.invoice_id trx_id
295 FROM ap_invoice_distributions_all apd
296 ,ap_invoices_all apinv
297 WHERE apinv.invoice_id = apd.invoice_id
298 AND apinv.invoice_date > $l_end_date$
299 AND apinv.set_of_books_id = $l_ledger_id$
300 AND apinv.legal_entity_id = $l_legal_entity_id$
301 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$
302 )';
303
304 l_update_query_ar := l_update_query_ar ||
305 ' AND zxl.trx_id IN
306 ( SELECT rtd.customer_trx_id
307 FROM ra_cust_trx_line_gl_dist_all rtd ,
308 ra_customer_trx_all rinv
309 WHERE rinv.customer_trx_id = rtd.customer_trx_id
310 AND rinv.trx_date > $l_end_date$
311 AND rinv.set_of_books_id = $l_ledger_id$
312 AND rinv.legal_entity_id = $l_legal_entity_id$
313 AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(rtd.CODE_COMBINATION_ID ,$p_chart_of_account_id$ ,$l_ledger_id$) = $p_bsv$
314 )';
315
316
317 END IF; -- p_reporting_level = 'BSV'
318
319 ELSIF l_driving_date_code = 'GL' THEN
320
321 l_update_query_ap := l_update_query_ap||
322 ' AND zxl.application_id = 200
323 AND zxl.trx_id IN
324 (SELECT invoice_id
325 FROM ap_invoices_all
326 WHERE gl_date > $l_end_date$
327 AND set_of_books_id = $l_ledger_id$
328 AND legal_entity_id = $l_legal_entity_id$
329 )';
330
331 l_update_query_ar := l_update_query_ar||
332 ' AND zxl.application_id = 222
333 AND zxl.trx_id IN
334 (SELECT customer_trx_id
335 FROM ra_cust_trx_line_gl_dist_all
336 WHERE gl_date > $l_end_date$
337 AND set_of_books_id = $l_ledger_id$
338 ) ';
339
340 IF p_reporting_level = 'BSV' THEN
341
342 l_update_query_ap := l_update_query_ap||
343 ' AND zxl.trx_id IN
344 (SELECT apd.invoice_id trx_id
345 FROM ap_invoice_distributions_all apd ,
346 ap_invoices_all apinv
347 WHERE apinv.invoice_id = apd.invoice_id
348 AND apinv.gl_date > $l_end_date$
349 AND apinv.set_of_books_id = $l_ledger_id$
350 AND apinv.legal_entity_id = $l_legal_entity_id$
351 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$
352 ) ';
353
354 l_update_query_ar := l_update_query_ar||
355 ' AND zxl.trx_id IN
356 (SELECT rtd.customer_trx_id
357 FROM ra_cust_trx_line_gl_dist_all rtd ,
358 ra_customer_trx_all rinv
359 WHERE rinv.customer_trx_id = rtd.customer_trx_id
360 AND rtd.gl_date > $l_end_date$
361 AND rinv.set_of_books_id = $l_ledger_id$
362 AND rinv.legal_entity_id = $l_legal_entity_id$
363 AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(rtd.CODE_COMBINATION_ID ,$p_chart_of_account_id$ ,$l_ledger_id$) = $p_bsv$
364 )';
365
366 END IF; --p_reporting_level = 'BSV'
367
368 ELSIF l_driving_date_code = 'TID' THEN
369
370 l_update_query_ap := l_update_query_ap ||
371 ' AND zxl.application_id = 200
372 AND zxl.trx_id IN
373 (SELECT zxd.trx_id
374 FROM zx_lines_det_factors zxd
375 WHERE zxd.tax_invoice_date > $l_end_date$
376 AND zxd.application_id = zxl.application_id
377 AND zxd.EVENT_CLASS_CODE = zxl.EVENT_CLASS_CODE
378 AND zxd.ENTITY_CODE = zxl.entity_code
379 AND zxd.legal_entity_id = $l_legal_entity_id$
380 AND zxd.ledger_id = $l_ledger_id$
381 )';
382
383 l_update_query_ar := l_update_query_ar ||
384 ' AND zxl.application_id = 222
385 AND zxl.trx_id IN
386 (SELECT zxd.trx_id
387 FROM zx_lines_det_factors zxd
388 WHERE zxd.tax_invoice_date > $l_end_date$
389 AND zxd.application_id = zxl.application_id
390 AND zxd.EVENT_CLASS_CODE = zxl.EVENT_CLASS_CODE
391 AND zxd.ENTITY_CODE = zxl.entity_code
392 AND zxd.legal_entity_id = $l_legal_entity_id$
393 AND zxd.ledger_id = $l_ledger_id$
394 )';
395
396 IF p_reporting_level = 'BSV' THEN
397
398 l_update_query_ap := l_update_query_ap ||
399 ' AND zxl.trx_id IN
400 (SELECT apd.invoice_id trx_id
401 FROM ap_invoice_distributions_all apd ,
402 ap_invoices_all apinv
403 WHERE apinv.invoice_id = apd.invoice_id
404 AND apinv.set_of_books_id = $l_ledger_id$
405 AND apinv.legal_entity_id = $l_legal_entity_id$
406 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$
407 )';
408
409 l_update_query_ar := l_update_query_ar ||
410 ' AND zxl.trx_id IN
411 ( SELECT rtd.customer_trx_id
412 FROM ra_cust_trx_line_gl_dist_all rtd ,
413 ra_customer_trx_all rinv
414 WHERE rinv.customer_trx_id = rtd.customer_trx_id
415 AND rinv.set_of_books_id = $l_ledger_id$
416 AND rinv.legal_entity_id = $l_legal_entity_id$
417 AND JG_ZZ_VAT_PRE_REP_PROC_PKG.get_bsv(rtd.CODE_COMBINATION_ID,$p_chart_of_account_id$,$l_ledger_id$) = $p_bsv$
418 )';
419
420 END IF; --p_reporting_level = 'BSV'
421 END IF;
422
423
424 l_update_query_ap := REPLACE( l_update_query_ap,'$l_ledger_id$',l_ledger_id);
425 l_update_query_ap := REPLACE( l_update_query_ap,'$l_end_date$',''''||l_end_date||'''');
426 l_update_query_ap := REPLACE( l_update_query_ap,'$l_legal_entity_id$',l_legal_entity_id);
427 l_update_query_ap := REPLACE( l_update_query_ap,'$p_chart_of_account_id$',p_chart_of_account_id);
428 l_update_query_ap := REPLACE( l_update_query_ap,'$p_bsv$',p_bsv);
429
430 l_update_query_ar := REPLACE( l_update_query_ar,'$l_ledger_id$',l_ledger_id);
431 l_update_query_ar := REPLACE( l_update_query_ar,'$l_end_date$',''''||l_end_date||'''');
432 l_update_query_ar := REPLACE( l_update_query_ar,'$l_legal_entity_id$',l_legal_entity_id);
433 l_update_query_ar := REPLACE( l_update_query_ar,'$p_chart_of_account_id$',p_chart_of_account_id);
434 l_update_query_ar := REPLACE( l_update_query_ar,'$p_bsv$',p_bsv);
435
436 fnd_file.put_line(fnd_file.log,'l_update_query_ap :='||l_update_query_ap);
437 fnd_file.put_line(fnd_file.log,'l_update_query_ar :='||l_update_query_ar);
438
439 EXECUTE IMMEDIATE l_update_query_ap;
440
441 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Driving Date Code :'||l_driving_date_code);
442 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AP Rows updated: '||To_char(SQL%RowCount));
443
444 EXECUTE IMMEDIATE l_update_query_ar;
445 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Driving Date Code :'||l_driving_date_code);
446 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AR Rows updated: '||To_char(SQL%RowCount));
447
448 IF l_driving_date_code_es = 'GL-TRX' THEN
449 l_driving_date_code := 'TRX';
450 l_driving_date_code_es := NULL;
451 goto continue_loop;
452 END IF;
453
454
455 IF p_reporting_level = 'LEDGER' THEN
456
457 UPDATE JG_ZZ_VAT_REP_ENTITIES
458 SET LAST_REPORTED_PERIOD = p_period
459 WHERE VAT_REPORTING_ENTITY_ID = p_vat_reporting_entity_id;
460
461 ELSE --p_reporting_level = 'BSV'
462 UPDATE JG_ZZ_VAT_REP_ENTITIES
463 SET LAST_REPORTED_PERIOD = p_period
464 WHERE VAT_REPORTING_ENTITY_ID = l_bsv_vat_rep_entity_id;
465 END IF;
466
467 EXCEPTION
468 WHEN OTHERS THEN
469 g_error_buffer := SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 80);
470 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
471 FND_MSG_PUB.Add;
472 IF (g_level_unexpected >= g_current_runtime_level ) THEN
473 FND_LOG.STRING(g_level_unexpected, G_MODULE_NAME, g_error_buffer);
474 END IF;
475 retcode := 2;
476 END main;
477 END JG_ZZ_VAT_PRE_REP_PROC_PKG;