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