[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_JOURNAL_AR_PKG
Source
1 PACKAGE BODY jg_zz_journal_ar_pkg
2 /* $Header: jgzzjournalarb.pls 120.31.12020000.2 2012/07/08 16:10:00 abhijsar ship $ */
3 AS
4 --
5
6 -- +======================================================================+
7 -- Name: GET_START_SEQUENCE
8 --
9 -- Description: This function is private to this package. It is called to
10 -- get the start_sequence_num for the report JEITARSV and JEITRDVR
11 -- transactions.
12 --
13 -- Parameters: None
14 -- +======================================================================+
15
16 FUNCTION get_start_sequence RETURN NUMBER IS
17 l_start_seq NUMBER;
18 l_period_start_date DATE;
19 BEGIN
20 SELECT max(vrs.period_start_date)
21 INTO l_period_start_date
22 FROM jg_zz_vat_final_reports vfr,
23 jg_zz_vat_rep_status vrs
24 WHERE vfr.report_name = p_report_name
25 AND vfr.vat_register_id = p_vat_register_id
26 AND vrs.reporting_status_id = vfr.reporting_status_id;
27
28 SELECT start_sequence_num
29 INTO l_start_seq
30 FROM jg_zz_vat_final_reports vfr,
31 jg_zz_vat_rep_status vrs
32 WHERE vfr.report_name = p_report_name
33 AND vfr.vat_register_id = p_vat_register_id
34 AND vrs.reporting_status_id = vfr.reporting_status_id
35 AND vrs.period_start_date = l_period_start_date;
36
37 RETURN l_start_seq;
38 EXCEPTION
39 WHEN others THEN
40 RETURN 0;
41 END get_start_sequence;
42
43
44 /* +======================================================================+
45 Name: BEFORE_REPORT
46
47 Description: This function is called as a before report trigger by the
48 data template. It populates the data in the global_tmp table
49 and creates the dynamic where clause for the data template
50 queries(lexical reference).
51
52 Parameters: None
53 +======================================================================+
54 */
55
56 FUNCTION beforeReport RETURN BOOLEAN
57 IS
58 l_address_line_1 VARCHAR2 (240);
59 l_address_line_2 VARCHAR2 (240);
60 l_address_line_3 VARCHAR2 (240);
61 l_address_line_4 VARCHAR2 (240);
62 l_city VARCHAR2 (60);
63 l_company_name VARCHAR2 (240);
64 l_contact_name VARCHAR2 (360);
65 l_country VARCHAR2 (60);
66 l_func_curr VARCHAR2 (30);
67 l_legal_entity_id NUMBER;
68 l_legal_entity_name VARCHAR2 (240);
69 l_period_end_date DATE;
70 l_period_start_date DATE;
71 l_to_period_end_date DATE; -- Bug8267272
72 l_to_period_start_date DATE; -- Bug8267272
73 l_phone_number VARCHAR2 (40);
74 l_postal_code VARCHAR2 (60);
75 l_registration_num VARCHAR2 (30);
76 l_reporting_status VARCHAR2 (60);
77 l_to_reporting_status VARCHAR2 (60); -- Bug8267272
78 l_tax_payer_id VARCHAR2 (60);
79 l_tax_registration_num VARCHAR2 (240);
80 l_to_tax_registration_num VARCHAR2 (240);-- Bug8267272
81 l_tax_regime VARCHAR2(240);
82 l_vat_register_name VARCHAR2(500);
83 l_sequence_start NUMBER;
84 l_start_seq NUMBER;
85 -- Added for Glob-006 ER
86 l_province VARCHAR2(120);
87 l_comm_num VARCHAR2(30);
88 l_vat_reg_num VARCHAR2(50);
89
90 BEGIN
91
92 IF p_report_name = 'JEITARSV' THEN
93 fnd_file.put_line(fnd_file.log,'**********************************************************');
94 fnd_file.put_line(fnd_file.log,'Italian Receivables Sales VAT Register');
95 fnd_file.put_line(fnd_file.log,'**********************************************************');
96 fnd_file.put_line(fnd_file.log,'');
97 fnd_file.put_line(fnd_file.log,'Report Parameters');
98 fnd_file.put_line(fnd_file.log,'Tax Registraion Number : '||p_vat_rep_entity_id);
99 fnd_file.put_line(fnd_file.log,'Tax Calendar Year : '||p_period);
100 fnd_file.put_line(fnd_file.log,'VAT Register Id : '||p_vat_register_id);
101 fnd_file.put_line(fnd_file.log,'**********************************************************');
102 fnd_file.put_line(fnd_file.log,'');
103 fnd_file.put_line(fnd_file.log,'');
104
105 BEGIN
106
107 SELECT register_name
108 INTO l_vat_register_name
109 FROM jg_zz_vat_registers_vl jzvr
110 ,jg_zz_vat_rep_entities jzvre
111 WHERE ((jzvre.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
112 and
113 jzvre.entity_type_code = 'ACCOUNTING'
114 and
115 jzvre.mapping_vat_rep_entity_id = jzvr.vat_reporting_entity_id
116 )
117 OR
118 (jzvre.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
119 and
120 jzvre.entity_type_code = 'LEGAL'
121 and
122 jzvre.vat_reporting_entity_id = jzvr.vat_reporting_entity_id
123 )) --OR P_VAT_REP_ENTITY_ID is null
124 AND jzvr.vat_register_id = p_vat_register_id
125 AND jzvr.register_type = 'SALES_VAT' ;
126 EXCEPTION
127 WHEN OTHERS THEN
128 fnd_file.put_line(fnd_file.log,'An error occured in the before report trigger, while fetching the VAT Register name. Exception : ' || SUBSTR(SQLERRM,1,200) || SQLCODE);
129 raise;
130 RETURN(FALSE);
131 END;
132
133 END IF;
134
135 IF p_report_name = 'JEITRDVR' THEN
136 fnd_file.put_line(fnd_file.log,'**********************************************************');
137 fnd_file.put_line(fnd_file.log,'Italian Receivables Deferred VAT Register');
138 fnd_file.put_line(fnd_file.log,'**********************************************************');
139 fnd_file.put_line(fnd_file.log,'');
140 fnd_file.put_line(fnd_file.log,'Report Parameters');
141 fnd_file.put_line(fnd_file.log,'Tax Registraion Number : '||p_vat_rep_entity_id);
142 fnd_file.put_line(fnd_file.log,'Tax Calendar Year : '||p_period);
143 fnd_file.put_line(fnd_file.log,'VAT Register Id : '||p_vat_register_id);
144 fnd_file.put_line(fnd_file.log,'**********************************************************');
145 fnd_file.put_line(fnd_file.log,'');
146 fnd_file.put_line(fnd_file.log,'');
147
148 BEGIN
149
150 SELECT register_name
151 INTO l_vat_register_name
152 FROM jg_zz_vat_registers_vl jzvr
153 ,jg_zz_vat_rep_entities jzvre
154 WHERE ((jzvre.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
155 and
156 jzvre.entity_type_code = 'ACCOUNTING'
157 and
158 jzvre.mapping_vat_rep_entity_id = jzvr.vat_reporting_entity_id
159 )
160 OR
161 (jzvre.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
162 and
163 jzvre.entity_type_code = 'LEGAL'
164 and
165 jzvre.vat_reporting_entity_id = jzvr.vat_reporting_entity_id
166 )) --OR P_VAT_REP_ENTITY_ID is null
167 AND jzvr.vat_register_id = p_vat_register_id
168 AND jzvr.register_type = 'DEFERRED_VAT';
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 fnd_file.put_line(fnd_file.log,'An error occured in the before report trigger, while fetching the VAT Register name. Exception : ' || SUBSTR(SQLERRM,1,200) || SQLCODE);
173 raise;
174 RETURN(FALSE);
175 END;
176
177 END IF;
178
179 fnd_file.put_line(fnd_file.log,'before Report Trigger');
180 jg_zz_common_pkg.funct_curr_legal(x_func_curr_code => l_func_curr
181 ,x_rep_entity_name => l_legal_entity_name
182 ,x_legal_entity_id => l_legal_entity_id
183 ,x_taxpayer_id => l_tax_payer_id
184 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
185 ,pv_period_name => p_period);
186
187 fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.tax_registration');
188 -- Bug8267272 Start
189 IF P_REPORT_NAME = 'JEESRRVR' THEN
190 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
191 ,x_period_start_date => l_period_start_date
192 ,x_period_end_date => l_period_end_date
193 ,x_status => l_reporting_status
194 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
195 ,pv_period_name => p_period
196 ,pv_source => 'ALL');
197 jg_zz_common_pkg.tax_registration(x_tax_registration => l_to_tax_registration_num
198 ,x_period_start_date => l_to_period_start_date
199 ,x_period_end_date => l_to_period_end_date
200 ,x_status => l_to_reporting_status
201 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
202 ,pv_period_name => p_period_to
203 ,pv_source => 'ALL');
204 ELSE
205 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
206 ,x_period_start_date => l_period_start_date
207 ,x_period_end_date => l_period_end_date
208 ,x_status => l_reporting_status
209 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
210 ,pv_period_name => p_period
211 ,pv_source => 'ALL');
212 end if;
213 -- Bug8267272 End
214
215 fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.company_detail');
216 jg_zz_common_pkg.company_detail(x_company_name => l_company_name
217 ,x_registration_number => l_registration_num
218 ,x_country => l_country
219 ,x_address1 => l_address_line_1
220 ,x_address2 => l_address_line_2
221 ,x_address3 => l_address_line_3
222 ,x_address4 => l_address_line_4
223 ,x_city => l_city
224 ,x_postal_code => l_postal_code
225 ,x_contact => l_contact_name
226 ,x_phone_number => l_phone_number
227 ,x_province => l_province
228 ,x_comm_number => l_comm_num
229 ,x_vat_reg_num => l_vat_reg_num
230 ,pn_legal_entity_id => l_legal_entity_id
231 ,p_vat_reporting_entity_id => P_VAT_REP_ENTITY_ID);
232
233 IF P_REPORT_NAME = 'JEITARSV' OR P_REPORT_NAME = 'JEITRDVR' THEN
234 -- Get the Start Sequence Number for the Previous Finally Reported period for same report and vat register.
235 l_start_seq := get_start_sequence;
236
237 l_reporting_status := JG_ZZ_VAT_REP_UTILITY.get_period_status(pn_vat_reporting_entity_id => p_vat_rep_entity_id
238 ,pv_tax_calendar_period => p_period
239 ,pv_tax_calendar_year => null
240 ,pv_source => NULL
241 ,pv_report_name => p_report_name
242 ,pv_vat_register_id => p_vat_register_id);
243
244 -- Insert the record into jg_zz_vat_trx_gt with Sequence and reporting mode info
245 IF l_reporting_status = 'COPY' THEN
246 SELECT last_start_sequence_num
247 INTO l_start_seq
248 FROM jg_zz_vat_final_reports vfr,
249 jg_zz_vat_rep_status vrs
250 WHERE vfr.report_name = p_report_name
251 AND vfr.vat_register_id = p_vat_register_id
252 AND vrs.reporting_status_id = vfr.reporting_status_id
253 AND vrs.tax_calendar_period = p_period;
254 END IF;
255
256 INSERT INTO jg_zz_vat_trx_gt (jg_info_n1,
257 jg_info_v1,
258 jg_info_v30)
259 VALUES (l_start_seq,
260 l_reporting_status,
261 'SEQ');
262
263 ELSE
264 l_reporting_status := JG_ZZ_VAT_REP_UTILITY.get_period_status(pn_vat_reporting_entity_id => p_vat_rep_entity_id
265 ,pv_tax_calendar_period => p_period
266 ,pv_tax_calendar_year => null
267 ,pv_source => NULL
268 ,pv_report_name => p_report_name);
269 END IF;
270
271 fnd_file.put_line(fnd_file.log,'P_REPORT_NAME :'||P_REPORT_NAME);
272 fnd_file.put_line(fnd_file.log,'P_SEQUENCE :'||P_SEQUENCE);
273 fnd_file.put_line(fnd_file.log,'l_period_start_date :'||l_period_start_date);
274 fnd_file.put_line(fnd_file.log,'l_period_end_date :'||l_period_end_date);
275
276 IF P_REPORT_NAME = 'JEESRRVR' THEN
277 IF P_SEQUENCE = 'Y' THEN
278 BEGIN
279 /*select count ( distinct ctl.customer_trx_id )
280 into l_sequence_start
281 from ra_customer_trx_lines ctl,
282 ra_customer_trx ct,
283 zx_rates_b zxb,
284 zx_taxes_b ztb,
285 zx_report_codes_assoc zxass
286 where ctl.vat_tax_id = zxb.tax_rate_id
287 and zxb.tax = ztb.tax
288 and zxb.tax_regime_code = ztb.tax_regime_code
289 and zxb.content_owner_id = ztb.content_owner_id
290 and zxb.tax_rate_id = zxass.entity_id
291 and zxass.entity_code = 'ZX_RATES'
292 and DECODE(ztb.offset_tax_flag,'Y','OFFSET',
293 Decode(zxb.def_rec_settlement_option_code,
294 'DEFERRED','DEFERRED',
295 zxass.REPORTING_CODE_CHAR_VALUE))= P_TAX_TYPE
296 and ct.customer_trx_id = ctl.customer_trx_id
297 and not exists (select 'x'
298 from ra_cust_trx_line_gl_dist gld
299 where gld.customer_trx_line_id = ctl.customer_trx_line_id
300 and gld.gl_posted_date is null)
301 and l_period_start_date >all
302 (select trx_date
303 from ra_customer_trx ct
304 where ct.customer_trx_id = ctl.customer_trx_id )
305 and trunc(l_period_start_date,'YYYY') <= all
306 (select trx_date
307 from ra_customer_trx ct
308 where ct.customer_trx_id = ctl.customer_trx_id );
309 */
310
311 SELECT count ( distinct JZVTD.trx_id )
312 INTO l_sequence_start
313 FROM jg_zz_vat_trx_details JZVTD
314 ,jg_zz_vat_rep_status JZVRS
315
316 WHERE JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
317 FROM jg_zz_vat_rep_status JZRS
318 WHERE JZRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
319 AND JZRS.source = 'AR')
320 AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
321 AND JZVRS.source = 'AR'
322 AND ((JZVTD.tax_rate_register_type_code = 'TAX' AND P_TAX_REGISTER_TYPE = 'TAX')
323 OR (JZVTD.tax_rate_register_type_code = 'INTERIM' AND P_TAX_REGISTER_TYPE = 'INTERIM')
324 OR (JZVTD.tax_rate_register_type_code = 'NON-RECOVERABLE' AND P_TAX_REGISTER_TYPE = 'NON-RECOVERABLE'))
325 AND ( JZVTD.trx_tax_balancing_segment = P_BALANCING_SEGMENT OR P_BALANCING_SEGMENT is NULL )
326 AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
327 AND JZVRS.reporting_status_id in ( SELECT reporting_status_id FROM jg_zz_vat_rep_status
328 WHERE period_start_date < l_period_start_date
329 AND vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
330 AND source='AR')
331 AND trunc(l_period_start_date,'YYYY') <= all
332 (select trx_date from jg_zz_vat_trx_details A
333 where A.trx_id = JZVTD.trx_id );
334
335 fnd_file.put_line(fnd_file.log,'l_sequence_start :'||l_sequence_start);
336
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 l_sequence_start := 0;
340 WHEN OTHERS THEN
341 fnd_file.put_line(fnd_file.log,'An error occured while calculating the l_sequence_start. Exception : ' || SUBSTR(SQLERRM,1,200) || SQLCODE);
342 END;
343 ELSE
344 l_sequence_start := 0;
345 END IF;
346 END IF;
347 INSERT INTO jg_zz_vat_trx_gt
348 (
349 jg_info_n1
350 ,jg_info_v1
351 ,jg_info_v2
352 ,jg_info_v3
353 ,jg_info_v4
354 ,jg_info_v5
355 ,jg_info_v6
356 ,jg_info_v7
357 ,jg_info_v8
358 ,jg_info_v9
359 ,jg_info_v10
360 ,jg_info_v11
361 ,jg_info_v12
362 ,jg_info_v13
363 ,jg_info_v14
364 ,jg_info_v15
365 ,jg_info_v16
366 ,jg_info_v17
367 ,jg_info_d1
368 ,jg_info_d2
369 ,jg_info_n2
370 ,jg_info_v30
371 ,jg_info_v18
372 ,jg_info_v19
373 ,jg_info_v20
374 )
375 VALUES
376 (
377 l_legal_entity_id
378 ,l_company_name
379 ,l_company_name --l_legal_entity_name
380 ,l_tax_registration_num --l_registration_num
381 ,l_registration_num --l_tax_payer_id
382 ,l_contact_name
383 ,l_address_line_1
384 ,l_address_line_2
385 ,l_address_line_3
386 ,l_address_line_4
387 ,l_city
388 ,l_country
389 ,l_phone_number
390 ,l_postal_code
391 ,l_func_curr
392 ,l_reporting_status
393 ,l_tax_regime
394 ,l_vat_register_name
395 ,decode(nvl(P_REPORT_NAME,'ZZ'),'JEESRRVR',l_to_period_end_date,l_period_end_date)
396 ,l_period_start_date
397 ,l_sequence_start
398 ,'H'
399 ,l_province
400 ,l_comm_num
401 ,l_vat_reg_num
402 );
403
404 IF P_REPORT_NAME = 'JEBEDV07' THEN
405 fnd_file.put_line(fnd_file.log,'Calling jebedv07');
406 jebedv07(p_vat_rep_entity_id => p_vat_rep_entity_id
407 ,p_period => p_period
408 ,p_document_sequence_name_from => p_document_sequence_name_from
409 ,p_document_sequence_name_to => p_document_sequence_name_to
410 ,p_customer_name_from => p_customer_name_from
411 ,p_customer_name_to => p_customer_name_to
412 ,p_detail_summary => p_detail_summary
413 ,x_err_msg => l_err_msg);
414 fnd_file.put_line(fnd_file.log,'After Calling jebedv07');
415 ELSIF P_REPORT_NAME = 'JEITARSV' THEN
416 fnd_file.put_line(fnd_file.log,'Calling jeitarsv');
417 jeitarsv(p_vat_rep_entity_id => p_vat_rep_entity_id
418 ,p_period => p_period
419 ,p_vat_register_id => p_vat_register_id
420 ,x_err_msg => l_err_msg);
421 fnd_file.put_line(fnd_file.log,'After Calling jeitarsv');
422 ELSIF P_REPORT_NAME = 'JEITRDVR' THEN
423 fnd_file.put_line(fnd_file.log,'Calling jeitrdvr');
424 jeitrdvr(p_vat_rep_entity_id => p_vat_rep_entity_id
425 ,p_period => p_period
426 ,p_vat_register_id => p_vat_register_id
427 ,x_err_msg => l_err_msg);
428 fnd_file.put_line(fnd_file.log,'After Calling jeitrdvr');
429 ELSIF P_REPORT_NAME = 'JEESRRVR' THEN
430 fnd_file.put_line(fnd_file.log,'Calling jeesrrvr');
431 fnd_file.put_line(fnd_file.log,'--PARAMETERS--');
432 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID :'||P_VAT_REP_ENTITY_ID);
433 fnd_file.put_line(fnd_file.log,'P_PERIOD :'||P_PERIOD);
434 fnd_file.put_line(fnd_file.log,'P_TAX_TYPE :'||P_TAX_TYPE);
435 fnd_file.put_line(fnd_file.log,'P_TAX_REGISTER_TYPE :'||P_TAX_REGISTER_TYPE );
436 fnd_file.put_line(fnd_file.log,'P_CHART_OF_ACCOUNT_ID :'||P_CHART_OF_ACCOUNT_ID);
437 fnd_file.put_line(fnd_file.log,'P_BALANCING_SEGMENT :'||P_BALANCING_SEGMENT );
438 fnd_file.put_line(fnd_file.log,'P_SEQUENCE :'||P_SEQUENCE);
439
440 jeesrrvr(p_vat_rep_entity_id => p_vat_rep_entity_id
441 ,p_period => p_period
442 ,p_period_to => p_period_to -- Bug8267272
443 ,p_tax_type => p_tax_type
444 ,p_tax_register_type => p_tax_register_type
445 ,p_sequence => p_sequence
446 ,x_err_msg => l_err_msg);
447 fnd_file.put_line(fnd_file.log,'After Calling jeesrrvr');
448 ELSIF (P_REPORT_NAME = 'JOURNAL-AR' OR P_REPORT_NAME IS NOT NULL) THEN
449 fnd_file.put_line(fnd_file.log,'Calling journal_ar');
450 NULL;
451 fnd_file.put_line(fnd_file.log,'After Calling journal_ar');
452 END IF;
453 RETURN (TRUE);
454 EXCEPTION
455 WHEN OTHERS THEN
456 fnd_file.put_line(fnd_file.log,'An error occured in the before report trigger. Exception : ' || SUBSTR(SQLERRM,1,200) || SQLCODE);
457 raise;
458 RETURN(FALSE);
459 END beforeReport;
460
461 --
462 -- +======================================================================+
463 -- Name: JEBEDV07
464 --
465 -- Description: This procedure used by the Extract when the Concurrent
466 -- Program 'Belgian VAT Sales Journal Report' is run.
467 --
468 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
469 -- P_PERIOD => Tax Calendar Year
470 -- P_DOCUMENT_SEQUENCE_NAME_FROM => Document Sequence Name From
471 -- P_DOCUMENT_SEQUENCE_NAME_TO => Document Sequence Name To
472 -- P_CUSTOMER_NAME_FROM => Customer Name From
473 -- P_CUSTOMER_NAME_TO => Customer Name To
474 -- P_DETAIL_SUMMARY => Detail Summary
475 -- +======================================================================+
476 --
477 PROCEDURE jebedv07(p_vat_rep_entity_id IN NUMBER
478 ,P_Period In VARCHAR2
479 ,P_Document_Sequence_Name_From In VARCHAR2
480 ,p_document_sequence_name_to IN VARCHAR2
481 ,p_customer_name_from IN VARCHAR2
482 ,p_customer_name_to IN VARCHAR2
483 ,p_detail_summary IN VARCHAR2
484 ,x_err_msg OUT NOCOPY VARCHAR2)
485 IS
486 CURSOR c_invoice IS
487 SELECT JZVRS.tax_calendar_year PERIOD_YEAR
488 ,JZVRS.tax_calendar_period PERIOD_NAME
489 ,JZVTD.doc_seq_name DOCUMENT_SEQUENCE_NAME
490 ,JZVTD.doc_seq_value DOCUMENT_SEQUENCE_NUMBER
491 ,JZVTD.trx_date INVOICE_DATE
492 ,SUBSTR(JZVTD.billing_tp_number,1,11) CUSTOMER_NUMBER
493 ,SUBSTR(JZVTD.billing_tp_name,1,18) CUSTOMER_NAME
494 ,JZVTD.trx_number INVOICE_NUMBER
495 ,NVL(JZVTD.tax_amt_funcl_curr,0) * to_number(jzvar.tax_rec_sign_flag ||'1') VAT_AMOUNT_FUNCL_CURR --bug10103169
496 ,NVL(JZVTD.taxable_amt_funcl_curr,0) * to_number(jzvar.taxable_rec_sign_flag ||'1') INV_AMT_WO_VAT_FUN_CURR --bug10103169
497 ,(
498 NVL(JZVTD.tax_amt_funcl_curr,0) * to_number(jzvar.tax_rec_sign_flag ||'1')
499 + NVL(JZVTD.taxable_amt_funcl_curr,0) * to_number(jzvar.taxable_rec_sign_flag ||'1')
500 ) TOT_INV_AMT_W_VAT_FUN_CURR --bug10103169
501 ,JZVTD.trx_line_type LINE_TYPE
502 ,JZVTD.trx_line_number LINE_NUMBER
503 ,JZVTD.account_flexfield ACCOUNT_FLEXFIELD
504 ,JZVTD.trx_control_account_flexfield TAXABLE_ACCT_FLEXFIELD
505 ,JZVTD.account_description ACCOUNT_DESCRIPTION
506 ,FA_RX_FLEX_PKG.GET_DESCRIPTION ( 101, 'GL#',
507 (select chart_of_accounts_id from gl_ledgers where ledger_id = JZVTD.ledger_id),
508 'ALL', JZVTD.trx_control_account_flexfield ) TXBL_ACCT_DESCRIPTION
509 ,NVL(JZVTD.trx_line_amt,0) ACCTD_AMOUNT
510 ,NVL(JZVTD.tax_amt_funcl_curr,0) ACCTD_VAT_AMT
511 ,NVL(JZVTD.taxable_amt_funcl_curr,0) ACCTD_INV_AMT
512 ,NVL(JZVTD.tax_amt_funcl_curr,0)
513 + NVL(JZVTD.taxable_amt_funcl_curr,0) ACCTD_TOT_AMT
514 ,JZVTD.tax_rate_code VAT_CODE
515 ,JZVTD.tax_rate_vat_trx_type_desc VAT_TRX_TYPE
516 ,NVL(JZVBA.taxable_box, '99') VAT_TAXABLE_BOX
517 ,NVL(JZVBA.tax_box, '99') VAT_TAX_BOX
518 ,NVL(JZVTD.tax_amt_funcl_curr,0) * to_number(jzvar.tax_rec_sign_flag ||'1') TAX_AMOUNT --bug10103169
519 ,NVL(JZVTD.taxable_amt_funcl_curr,0) * to_number(jzvar.taxable_rec_sign_flag ||'1') TAXABLE_AMOUNT --bug10103169
520 ,NVL(JZVTD.taxable_amt_funcl_curr,0) * to_number(jzvar.taxable_rec_sign_flag ||'1') TAXABLE_AMT_FUN_CURR --bug10103169
521 ,NVL(JZVTD.tax_amt_funcl_curr,0) * to_number(jzvar.tax_rec_sign_flag ||'1') TAX_AMT_FUN_CURR --bug10103169
522 FROM jg_zz_vat_trx_details JZVTD
523 ,jg_zz_vat_rep_status JZVRS
524 ,jg_zz_vat_box_allocs JZVBA
525 ,ra_cust_trx_types RCTT
526 ,jg_zz_vat_alloc_rules jzvar
527 WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
528 AND RCTT.cust_trx_type_id = JZVTD.trx_type_id
529 AND JZVTD.vat_transaction_id = JZVBA.vat_transaction_id
530 AND JZVBA.period_type = 'PERIODIC'
531 AND JZVRS.source = 'AR'
532 AND JZVAR.ALLOCATION_RULE_ID = JZVBA.ALLOCATION_RULE_ID
533 AND RCTT.type IN ('INV','CM','DM','CB','DEP','GUAR')
534 AND (JZVTD.billing_tp_name BETWEEN NVL(P_customer_name_from, JZVTD.billing_tp_name)
535 AND NVL(P_customer_name_to, JZVTD.billing_tp_name))
536 And ( (P_Document_Sequence_Name_From Is Null And P_Document_Sequence_Name_To Is Null)
537 Or (JZVTD.doc_seq_name BETWEEN NVL(P_DOCUMENT_SEQUENCE_NAME_FROM,JZVTD.doc_seq_name)
538 AND NVL(P_DOCUMENT_SEQUENCE_NAME_TO,JZVTD.doc_seq_name))
539
540 )
541 AND JZVRS.tax_calendar_period = P_PERIOD
542 AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID;
543
544 l_invoice c_invoice%ROWTYPE;
545
546 BEGIN
547 OPEN c_invoice;
548 LOOP
549 FETCH c_invoice INTO l_invoice;
550 EXIT WHEN c_invoice%NOTFOUND;
551 INSERT INTO jg_zz_vat_trx_gt
552 (
553 jg_info_n1
554 , jg_info_v1
555 , jg_info_v2
556 , jg_info_v3
557 , jg_info_d1
558 , jg_info_v4
559 , jg_info_v5
560 , jg_info_v6
561 , jg_info_n2
562 , jg_info_n3
563 , jg_info_n4
564 , jg_info_v7
565 , jg_info_n5 --line_number
566 , jg_info_v8
567 , jg_info_v14
568 , jg_info_v9
569 , jg_info_v15
570 , jg_info_n6
571 , jg_info_n11
572 , jg_info_n12
573 , jg_info_n13
574 , jg_info_v10
575 , jg_info_v11
576 , jg_info_v12 -- vat_taxable_box
577 , jg_info_v13 -- vat_tax_box
578 , jg_info_n7
579 , jg_info_n8
580 , jg_info_n9
581 , jg_info_n10
582 )
583 VALUES(
584 l_invoice.period_year
585 , l_invoice.period_name
586 , l_invoice.document_sequence_name
587 , l_invoice.document_sequence_number
588 , l_invoice.invoice_date
589 , l_invoice.customer_number
590 , l_invoice.customer_name
591 , l_invoice.invoice_number
592 , l_invoice.vat_amount_funcl_curr
593 , l_invoice.inv_amt_wo_vat_fun_curr
594 , l_invoice.tot_inv_amt_w_vat_fun_curr
595 , l_invoice.line_type
596 , l_invoice.line_number
597 , l_invoice.account_flexfield
598 , l_invoice.taxable_acct_flexfield
599 , l_invoice.account_description
600 , l_invoice.txbl_acct_description
601 , l_invoice.acctd_amount
602 , l_invoice.acctd_vat_amt
603 , l_invoice.acctd_inv_amt
604 , l_invoice.acctd_tot_amt
605 , l_invoice.vat_code
606 , l_invoice.vat_trx_type
607 , l_invoice.vat_taxable_box
608 , l_invoice.vat_tax_box
609 , l_invoice.tax_amount
610 , l_invoice.taxable_amount
611 , l_invoice.taxable_amt_fun_curr
612 , l_invoice.tax_amt_fun_curr
613 );
614
615 END LOOP;
616
617 EXCEPTION
618 WHEN OTHERS THEN
619 fnd_file.put_line(fnd_file.log,'An error occured in the Procedure JEBEDV07. Exception : ' || SUBSTR(SQLERRM,1,200) || SQLCODE);
620 END jebedv07;
621
622 --
623 -- +======================================================================+
624 -- Name: JEITARSV
625 --
626 -- Description: This procedure used by the Extract when the Concurrent
627 -- Program 'Italian Receivables Sales VAT Register' is run.
628 --
629 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
630 -- P_PERIOD => Tax Calendar Year
631 -- P_VAT_REGISTER_ID => VAT Register ID
632 -- +======================================================================+
633 --
634 PROCEDURE jeitarsv(p_vat_rep_entity_id IN NUMBER
635 ,p_period IN VARCHAR2
636 ,p_vat_register_id IN NUMBER
637 ,x_err_msg OUT NOCOPY VARCHAR2)
638 IS
639 CURSOR c_get_std_invoice IS
640 SELECT
641 JZVTD.doc_seq_name DOCUMENT_SEQ_NAME
642 ,JZVTD.trx_number PRINT_SEQ
643 ,JZVTD.gl_date GL_DATE
644 ,JZVTD.doc_seq_value DOCUMENT_SEQ_NUMBER
645 ,JZVTD.billing_tp_name CUSTOMER_NAME
646 ,JZVTD.billing_tp_number CUSTOMER_NUMBER
647 ,JZVTD.billing_tp_site_name CUSTOMER_SITE_NAME
648 ,NVL(NVL(NVL(JZVTD.billing_tp_site_tax_reg_num,JZVTD.shipping_tp_site_tax_reg_num)
649 ,JZVTD.billing_tp_tax_reg_num), JZVTD.shipping_tp_tax_reg_num) TAX_REFERENCE
650 ,JZVTD.trx_number INVOICE_NUMBER
651 ,JZVTD.trx_date INVOICE_DATE
652 ,JZVTD.tax_rate_code TAX_CODE
653 ,NVL(JZVTD.taxable_amt_funcl_curr,0) TAXABLE_AMT_FUNCL_CURR
654 ,NVL(JZVTD.tax_amt_funcl_curr,0) TAX_AMT_FUNCL_CURR
655 ,JZVTD.tax_rate TAX_RATE
656 ,JZVTD.tax_rate_code_description DESCRIPTION
657 ,JZVRV.effective_to_date JEITARSV_PREL_ALERT
658 ,JZVTD.trx_line_id TRX_LINE_ID
659 ,SUBSTR(JZVTD.billing_tp_name,1,90) PARTY_NAME
660 ,SUBSTR(JZVTD.billing_tp_name, 1 , 22) VAT_REGISTER_NAME
661 ,JZVTD.posted_flag POSTED_FLAG
662 ,JZVTD.trx_currency_code TRX_CURRENCY_CODE
663 ,JZVTD.TRX_DUE_DATE TRX_DUE_DATE
664 ,JZVTD.ACCOUNTING_DATE ACCOUNTING_DATE
665 FROM jg_zz_vat_trx_details JZVTD
666 ,jg_zz_vat_registers_vl JZVRV
667 ,jg_zz_vat_rep_status JZVRS
668 ,jg_zz_vat_doc_sequences JZVDS
669 WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
670 AND JZVRV.vat_reporting_entity_id = JZVRS.mapping_vat_rep_entity_id
671 AND JZVDS.doc_sequence_id = JZVTD.doc_seq_id
672 AND JZVDS.vat_register_id = JZVRV.vat_register_id
673 AND JZVRS.source = 'AR'
674 AND NVL(JZVTD.offset_flag,'N') <> 'Y'
675 AND JZVRS.tax_calendar_period = P_PERIOD
676 AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
677 AND JZVRV.vat_register_id = P_VAT_REGISTER_ID;
678
679 l_get_std_invoice c_get_std_invoice%ROWTYPE;
680 lv_start_seq jg_zz_vat_final_reports.start_sequence_num%type;
681 lv_reporting_status varchar2(15);
682 l_rec_count number(15);
683
684 BEGIN
685
686 SELECT jg_info_n1, jg_info_v1
687 INTO lv_start_seq, lv_reporting_status
688 FROM jg_zz_vat_trx_gt
689 WHERE jg_info_v30 = 'SEQ';
690
691 OPEN c_get_std_invoice;
692 LOOP
693 FETCH c_get_std_invoice INTO l_get_std_invoice;
694
695 EXIT WHEN c_get_std_invoice%NOTFOUND;
696 INSERT INTO jg_zz_vat_trx_gt
697 (
698 jg_info_v2
699 , jg_info_v1
700 , jg_info_d1
701 , jg_info_n2
702 , jg_info_v3
703 , jg_info_v4
704 , jg_info_v5
705 , jg_info_d2
706 , jg_info_v6
707 , jg_info_n3
708 , jg_info_n4
709 , jg_info_n5 --tax_rate
710 , jg_info_v7
711 , jg_info_d3
712 , jg_info_v8
713 , jg_info_v9
714 , jg_info_v10 --posted_flag
715 , jg_info_v11 --customer_number
716 , jg_info_v12 --trx_currency_code
717 , jg_info_v13 --customer_site_name
718 , jg_info_d4 --trx_due_date
719 , jg_info_d5 --accounting_date
720 , jg_info_v30
721 )
722 VALUES(
723 l_get_std_invoice.document_seq_name
724 , l_get_std_invoice.print_seq
725 , l_get_std_invoice.gl_date
726 , l_get_std_invoice.document_seq_number
727 , l_get_std_invoice.customer_name
728 , l_get_std_invoice.tax_reference
729 , l_get_std_invoice.invoice_number
730 , l_get_std_invoice.invoice_date
731 , l_get_std_invoice.tax_code
732 , l_get_std_invoice.taxable_amt_funcl_curr
733 , l_get_std_invoice.tax_amt_funcl_curr
734 , l_get_std_invoice.tax_rate
735 , l_get_std_invoice.description
736 , l_get_std_invoice.jeitarsv_prel_alert
737 , l_get_std_invoice.party_name
738 , l_get_std_invoice.vat_register_name
739 , l_get_std_invoice.posted_flag
740 , l_get_std_invoice.customer_number
741 , l_get_std_invoice.trx_currency_code
742 , l_get_std_invoice.customer_site_name
743 , l_get_std_invoice.trx_due_date
744 , l_get_std_invoice.accounting_date
745 , 'JEITARSV'
746 );
747
748 END LOOP;
749
750 CLOSE c_get_std_invoice;
751
752 -- Update teh jg_zz_vat_final_reports table for print sequence numbers if lv_reporting_status = 'FINAL'
753 IF lv_reporting_status = 'FINAL' THEN
754
755 SELECT count(*)
756 INTO l_rec_count
757 FROM (SELECT 1
758 FROM jg_zz_vat_trx_gt
759 WHERE jg_info_v30 = 'JEITARSV'
760 GROUP BY jg_info_v2
761 , jg_info_n2
762 , jg_info_v1
763 , jg_info_d1
764 , jg_info_v3
765 , jg_info_v11
766 , jg_info_v13
767 , jg_info_v4
768 , jg_info_v5
769 , jg_info_d2
770 , jg_info_d4
771 , jg_info_v12
772 , jg_info_v10
773 , jg_info_d5);
774
775 -- Update the entry in JG_ZZ_VAT_FINAL_REPORTS table
776 UPDATE jg_zz_vat_final_reports
777 SET start_sequence_num = lv_start_seq + l_rec_count,
778 last_start_sequence_num = lv_start_seq
779 WHERE report_name = p_report_name
780 AND vat_register_id = p_vat_register_id
781 AND reporting_status_id = (SELECT reporting_status_id
782 FROM jg_zz_vat_rep_status
783 WHERE vat_reporting_entity_id = p_vat_rep_entity_id
784 AND source = 'AR'
785 AND tax_calendar_period = p_period);
786
787 END IF;
788
789 EXCEPTION
790 WHEN OTHERS THEN
791 fnd_file.put_line(fnd_file.log,'An error occured in the Procedure JEITARSV. Exception : ' || SUBSTR(SQLERRM,1,200) || SQLCODE);
792 END jeitarsv;
793
794
795 --
796 -- +======================================================================+
797 -- Name: JEITRDVR
798 --
799 -- Description: This procedure used by the Extract when the Concurrent
800 -- Program 'Italian Receivables Deferred VAT Register' is run.
801 --
802 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
803 -- P_PERIOD => Tax Calendar Year
804 -- P_VAT_REGISTER_ID => VAT Register ID
805 -- +======================================================================+
806 --
807 PROCEDURE jeitrdvr(p_vat_rep_entity_id IN NUMBER
808 ,p_period IN VARCHAR2
809 ,p_vat_register_id IN NUMBER
810 ,x_err_msg OUT NOCOPY VARCHAR2)
811 IS
812 CURSOR c_get_std_invoice IS
813 SELECT
814 JZVTD.doc_seq_name DOCUMENT_SEQ_NAME
815 ,JZVTD.trx_number PRINT_SEQ_INV_NUM
816 ,JZVTD.gl_date GL_DATE
817 ,JZVTD.doc_seq_value DOCUMENT_SEQ_NUM
818 ,JZVTD.billing_tp_name CUSTOMER_NAME
819 ,NVL(NVL(NVL(JZVTD.billing_tp_site_tax_reg_num,
820 JZVTD.shipping_tp_site_tax_reg_num)
821 ,JZVTD.billing_tp_tax_reg_num),
822 JZVTD.shipping_tp_tax_reg_num) TAX_REFERENCE
823 ,JZVTD.trx_number INVOICE_NUMBER
824 ,JZVTD.trx_date INVOICE_DATE
825 ,JZVTD.trx_id INVOICE_ID
826 ,JZVTD.tax_rate_code TAX_CODE
827 ,NVL(JZVTD.taxable_amt_funcl_curr,0) TAXABLE_AMOUNT
828 ,NVL(JZVTD.tax_amt_funcl_curr,0) TAX_AMOUNT
829 ,NVL(JZVTD.tax_amt_funcl_curr,0) +
830 NVL(JZVTD.taxable_amt_funcl_curr,0) TOTAL_AMOUNT_FUNC_CURR
831 ,NVL(JZVTD.taxable_amt_funcl_curr,0) INV_AMT_WITHOUT_VAT_FUNC_CURR
832 ,NVL(JZVTD.tax_amt_funcl_curr,0) VAT_AMOUNT_FUNC_CURR
833 ,JZVTD.applied_to_trx_number NOTE
834 ,JZVTD.applied_to_trx_id APPLIED_TO_TRX_ID
835 ,JZVTD.tax_rate TAX_RATE
836 ,JZVTD.tax_rate_code_description DESCRIPTION
837 ,JZVRV.effective_to_date JEITRDVR_PREL_ALERT
838 ,JZVTD.trx_line_id TRX_LINE_ID
839 ,SUBSTR(JZVTD.billing_tp_name,1,90) PARTY_NAME
840 ,SUBSTR(JZVTD.billing_tp_name,1,22) VAT_REGISTER_NAME
841 ,JZVTD.posted_flag POSTED_FLAG
842 ,JZVTD.ACCOUNTING_DATE ACCOUNTING_DATE -- bug9872998
843 FROM jg_zz_vat_trx_details JZVTD
844 ,jg_zz_vat_registers_vl JZVRV
845 ,jg_zz_vat_rep_status JZVRS
846 ,ar_lookups LK
847 ,jg_zz_vat_doc_sequences JZVDS
848 WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
849 AND JZVRS.tax_calendar_period = P_PERIOD
850 AND JZVRV.vat_register_id = P_VAT_REGISTER_ID
851 AND JZVRS.source = 'AR'
852 AND JZVTD.reporting_status_id = JZVRS.reporting_status_id
853 AND JZVRV.vat_reporting_entity_id = JZVRS.mapping_vat_rep_entity_id
854 AND JZVRV.vat_register_id = JZVDS.vat_register_id
855 AND JZVDS.doc_sequence_id = JZVTD.doc_seq_id
856 -- Bug 6238170 Start
857 --AND JZVTD.tax_type_code = LK.lookup_code
858 AND JZVTD.reporting_code = LK.lookup_code
859 -- Bug 6238170 End
860 AND LK.lookup_type = 'JE_DEFERRED_TAX_TYPE'
861 AND JZVTD.tax_rate_register_type_code = 'INTERIM'
862 AND nvl(JZVTD.offset_flag,'N') <> 'Y';
863
864
865 l_get_std_invoice c_get_std_invoice%ROWTYPE;
866 lv_start_seq jg_zz_vat_final_reports.start_sequence_num%type;
867 lv_reporting_status varchar2(15);
868 l_rec_count number;
869
870 BEGIN
871
872 SELECT jg_info_n1, jg_info_v1
873 INTO lv_start_seq, lv_reporting_status
874 FROM jg_zz_vat_trx_gt
875 WHERE jg_info_v30 = 'SEQ';
876
877 OPEN c_get_std_invoice;
878 LOOP
879 FETCH c_get_std_invoice INTO l_get_std_invoice;
880 EXIT WHEN c_get_std_invoice%NOTFOUND;
881 INSERT INTO jg_zz_vat_trx_gt
882 (
883 jg_info_v1
884 , jg_info_v2
885 , jg_info_d1
886 , jg_info_v3
887 , jg_info_v4
888 , jg_info_v5
889 , jg_info_v6
890 , jg_info_d2
891 , jg_info_v7
892 , jg_info_n1
893 , jg_info_n2
894 , jg_info_n3
895 , jg_info_n4
896 , jg_info_n5
897 , jg_info_v8
898 , jg_info_n6
899 , jg_info_v9
900 , jg_info_d3
901 , jg_info_n7
902 , jg_info_v10
903 , jg_info_v11
904 , jg_info_n8
905 , jg_info_v12 --posted_flag
906 , jg_info_n9 --APPLIED_TO_TRX_ID
907 , jg_info_v30
908 ,jg_info_d5 -- ACCOUNTING_DATE
909 )
910 VALUES(
911 l_get_std_invoice.document_seq_name
912 , l_get_std_invoice.print_seq_inv_num
913 , l_get_std_invoice.gl_date
914 , l_get_std_invoice.document_seq_num
915 , l_get_std_invoice.customer_name
916 , l_get_std_invoice.tax_reference
917 , l_get_std_invoice.invoice_number
918 , l_get_std_invoice.invoice_date
919 , l_get_std_invoice.tax_code
920 , l_get_std_invoice.taxable_amount
921 , l_get_std_invoice.tax_amount
922 , l_get_std_invoice.total_amount_func_curr
923 , l_get_std_invoice.inv_amt_without_vat_func_curr
924 , l_get_std_invoice.vat_amount_func_curr
925 , l_get_std_invoice.note
926 , l_get_std_invoice.tax_rate
927 , l_get_std_invoice.description
928 , l_get_std_invoice.jeitrdvr_prel_alert
929 , l_get_std_invoice.trx_line_id
930 , l_get_std_invoice.party_name
931 , l_get_std_invoice.vat_register_name
932 , l_get_std_invoice.invoice_id
933 , l_get_std_invoice.posted_flag
934 , l_get_std_invoice.applied_to_trx_id
935 , 'JEITRDVR'
936 ,l_get_std_invoice.accounting_date
937 );
938 END LOOP;
939
940 -- Update teh jg_zz_vat_final_reports table for print sequence numbers if lv_reporting_status = 'FINAL'
941 IF lv_reporting_status = 'FINAL' THEN
942
943 SELECT count(*)
944 INTO l_rec_count
945 FROM (SELECT 1
946 FROM jg_zz_vat_trx_gt
947 WHERE jg_info_v30 = 'JEITRDVR'
948 GROUP BY jg_info_v1
949 ,jg_info_v2
950 ,jg_info_d1
951 ,jg_info_v3
952 ,jg_info_v4
953 ,jg_info_v5
954 ,jg_info_v6
955 ,jg_info_d2);
956
957 -- Update the entry in JG_ZZ_VAT_FINAL_REPORTS table
958 UPDATE jg_zz_vat_final_reports
959 SET start_sequence_num = lv_start_seq + l_rec_count,
960 last_start_sequence_num = lv_start_seq
961 WHERE report_name = p_report_name
962 AND vat_register_id = p_vat_register_id
963 AND reporting_status_id = (SELECT reporting_status_id
964 FROM jg_zz_vat_rep_status
965 WHERE vat_reporting_entity_id = p_vat_rep_entity_id
966 AND source = 'AR'
967 AND tax_calendar_period = p_period);
968
969 END IF;
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 fnd_file.put_line(fnd_file.log,'An error occured in the Procedure JEITRDVR. Exception : ' || SUBSTR(SQLERRM,1,200) || SQLCODE);
974 END jeitrdvr;
975
976
977 --
978 -- +======================================================================+
979 -- Name: JEESRRVR
980 --
981 -- Description: This procedure used by the Extract when the Concurrent
982 -- Program 'Spanish Output VAT Journal Report' is run.
983 --
984 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
985 -- P_PERIOD => Tax Calendar Year
986 -- P_TAX_TYPE => Tax Type
987 -- P_TAX_REGISTER_TYPE => Tax Register Type
988 -- P_SEQUENCE => Sequence
989 -- +======================================================================+
990 --
991 PROCEDURE jeesrrvr(p_vat_rep_entity_id IN NUMBER
992 ,p_period IN VARCHAR2
993 ,p_period_to IN VARCHAR2 -- Bug8267272 Start
994 ,p_tax_type IN VARCHAR2
995 ,p_tax_register_type IN VARCHAR2
996 ,p_sequence IN VARCHAR2
997 ,x_err_msg OUT NOCOPY VARCHAR2)
998 IS
999 CURSOR C_INV_LINES IS
1000 SELECT
1001 JZVTD.trx_id SEQ_NUM
1002 ,JZVTD.doc_seq_name ||'/'|| JZVTD.doc_seq_value DOC_SEQ_NUM
1003 ,JZVTD.trx_date INVOICE_DATE
1004 ,DECODE(JZVTD.trx_line_class,'APP'
1005 ,JZVTD.applied_to_trx_number
1006 ,JZVTD.trx_number) INVOICE_NUMBER
1007 ,SUBSTR(JZVTD.billing_tp_name,1,150)||' '||
1008 JZVTD.billing_tp_tax_reg_num CUSTOMER_NAME
1009 ,NVL(JZVTD.taxable_amt_funcl_curr,0) NET_AMOUNT
1010 ,JZVTD.tax_rate_code TAX_CODE
1011 ,JZVTD.tax_rate TAX_RATE
1012 ,NVL(JZVTD.tax_amt_funcl_curr,0) TAX_AMOUNT
1013 ,JZVTD.tax_rate_vat_trx_type_desc TAX_DESCRIPTION
1014 ,JZVTD.REPORTING_CODE REPORTING_CODE
1015 ,TAX_RATE_REGISTER_TYPE_CODE REGISTER_TYPE
1016 ,JZVTD.trx_line_id TRX_LINE_ID
1017 FROM jg_zz_vat_trx_details JZVTD
1018 ,jg_zz_vat_rep_status JZVRS
1019 -- ,fnd_lookup_values LK
1020 WHERE JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
1021 FROM jg_zz_vat_rep_status JZRS
1022 WHERE JZRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
1023 AND JZRS.source = 'AR')
1024 AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
1025 -- JZVTD.reporting_status_id = JZVRS.reporting_status_id
1026 -- AND JZVTD.tax_type_code = LK.lookup_code
1027 -- AND LK.lookup_type = 'ZX_TRL_REGISTER_TYPE'
1028 -- AND LK.source_lang = USERENV('LANG')
1029 AND JZVRS.source = 'AR'
1030 AND ((JZVTD.tax_rate_register_type_code = 'TAX' AND P_TAX_REGISTER_TYPE = 'TAX')
1031 OR (JZVTD.tax_rate_register_type_code = 'INTERIM' AND P_TAX_REGISTER_TYPE = 'INTERIM')
1032 OR (JZVTD.tax_rate_register_type_code = 'NON-RECOVERABLE' AND P_TAX_REGISTER_TYPE = 'NON-RECOVERABLE'))
1033 AND JZVTD.reporting_code = P_TAX_TYPE --BUG:9223611
1034 -- Bug8267272 Start
1035 AND JZVRS.tax_calendar_period IN (SELECT RPS1.tax_calendar_period
1036 FROM JG_ZZ_VAT_REP_STATUS RPS1,
1037 (Select min(vat_reporting_entity_id) vat_reporting_entity_id,
1038 min(period_start_date) period_start_date
1039 From JG_ZZ_VAT_REP_STATUS
1040 Where vat_reporting_entity_id = p_vat_rep_entity_id
1041 And tax_calendar_period =p_period) RPS2,
1042 (Select min(vat_reporting_entity_id) vat_reporting_entity_id,
1043 min(period_end_date) period_end_date
1044 From JG_ZZ_VAT_REP_STATUS
1045 Where vat_reporting_entity_id = p_vat_rep_entity_id
1046 And tax_calendar_period = p_period_to) RPS3
1047 WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
1048 AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
1049 AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
1050 AND trunc(RPS1.period_start_date) >=
1051 trunc(RPS2.period_start_date)
1052 AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
1053 GROUP by RPS1.tax_calendar_period)
1054 -- Bug8267272 End
1055
1056 AND ( JZVTD.trx_tax_balancing_segment = P_BALANCING_SEGMENT OR P_BALANCING_SEGMENT is NULL )
1057 AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID;
1058 -- AND LK.lookup_code = P_TAX_REGISTER_TYPE;
1059
1060 l_inv_lines c_inv_lines%ROWTYPE;
1061
1062 BEGIN
1063
1064 OPEN c_inv_lines;
1065 LOOP
1066 FETCH c_inv_lines INTO l_inv_lines;
1067 EXIT WHEN c_inv_lines%NOTFOUND;
1068
1069 INSERT INTO jg_zz_vat_trx_gt
1070 (
1071 jg_info_n1
1072 , jg_info_v1
1073 , jg_info_d1
1074 , jg_info_v2
1075 , jg_info_v3
1076 , jg_info_n2
1077 , jg_info_v4
1078 , jg_info_n3
1079 , jg_info_n4
1080 , jg_info_v5
1081 , jg_info_v6
1082 , jg_info_v7
1083 , jg_info_n5
1084 )
1085 VALUES(
1086 l_inv_lines.seq_num
1087 , l_inv_lines.doc_seq_num
1088 , l_inv_lines.invoice_date
1089 , l_inv_lines.invoice_number
1090 , l_inv_lines.customer_name
1091 , l_inv_lines.net_amount
1092 , l_inv_lines.tax_code
1093 , l_inv_lines.tax_rate
1094 , l_inv_lines.tax_amount
1095 , l_inv_lines.tax_description
1096 , l_inv_lines.reporting_code
1097 , l_inv_lines.register_type
1098 , l_inv_lines.trx_line_id
1099 );
1100
1101 END LOOP;
1102
1103
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 fnd_file.put_line(fnd_file.log,'An error occured in the Procedure JEESRRVR. Exception : ' || SUBSTR(SQLERRM,1,200) || SQLCODE);
1107 END jeesrrvr;
1108
1109 FUNCTION get_sequence_number RETURN NUMBER IS
1110 l_start_seq NUMBER;
1111 BEGIN
1112 IF p_report_name = 'JEITARSV' or p_report_name = 'JEITRDVR' THEN
1113 SELECT jg_info_n1
1114 INTO l_start_seq
1115 FROM jg_zz_vat_trx_gt
1116 WHERE jg_info_v30 = 'SEQ';
1117
1118 RETURN l_start_seq;
1119 ELSE
1120 RETURN 0;
1121 END IF;
1122 EXCEPTION
1123 WHEN others THEN
1124 RETURN 0;
1125 END get_sequence_number;
1126
1127 END JG_ZZ_JOURNAL_AR_PKG;