[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_TURNOVER_AR_PKG
Source
1 PACKAGE BODY jg_zz_turnover_ar_pkg
2 -- $Header: jgzzturnoverarb.pls 120.18.12020000.2 2012/10/01 08:47:31 rahulkum ship $
3 -- +===================================================================+
4 -- | Oracle Solution Services (India) |
5 -- | Bangalore, India |
6 -- +===================================================================+
7 -- |Name: JGZZTURNOVERARB.pls |
8 -- |Description: EMEA VAT TURNOVER_AR package creation script |
9 -- | |
10 -- | |
11 -- | |
12 -- |Change Record: |
13 -- |=============== |
14 -- |Version Date Author Remarks |
15 -- |======= ========== =============== ==========================|
16 -- |DRAFT1A 24-JAN-2006 Manish Upadhyay Initial version |
17 -- |DRAFT1B 22-FEB-2006 Balachander G Changes after IDC Review |
18 -- | 23-Mar-2006 Ramananda Modified the cursor lcu_be_annual_decl to incorporate the revised approach
19 -- | 21-Apr-2006 Ramananda Modified the cursor lcu_be_annual_decl
20 -- | RCT.interface_header_context <> 'CONTRA' changed to RCT.interface_header_context <> 'Contra'
21 -- | 26-Apr-2006 Ramananda Modified the cursor lcu_be_annual_decl
22 -- | 5189030 Removed unnecessary filter JZVTD.extract_source_ledger = 'AR.
23 -- | Removed duplicate join RTT.cust_trx_type_id = JZVTD.trx_type_id
24 -- | 28-Apr-2006 Ramananda Pokkula Dummy assignments are removed
25 -- | 29-Jun-2006 Ramananda Pokkula Bug # 5223170
26 -- | 08-Aug-2006 Kasbalas Bug # 5194991
27 -- | 27-Oct-2006 rjreddy Bug 5616757: In beforereport procedure, changed the condition while raising NO_TRANS_RECORDS exception
28 -- | 21-Nov-2006 spasupun BUG 5658632 and 5658620
29 -- | Fixed the issues reported in the bugs : 5658632 and
30 -- | 5658620
31 -- | 5658620 :- 1. In JEBEVADC header section
32 -- | tax_registration _num is not correct. In the header
33 -- | section for both the columns jg_info_v2
34 -- | and jg_info_v3,we are inserting taxpayer_id only.
35 -- | Changed the jg_info_v2 column source to
36 -- | l_tax_registration_num.
37 -- | 2. JEBEVADC report showing the data for each customer
38 -- | instead of showing summary of all customers. Made the
39 -- | changes in xml file.
40 -- | 5658632 : 1. Added header and footer information to
41 -- | report JEBEVA24.
42 -- | 2. Implemented the amount formatting logic by using
43 -- | precision and currency factor for report JEBEVA24.
44 -- | 21-Nov-2006 spasupun Modified the Having clause of cursor lcu_be_annual_decl.
45 -- | 01-Jul-2008 rsaini Modified function Before report to return FALSE on exception
46 -- 01-Oct-2012 rahulkum ER:14695260Belgium The Belgium VAT Annual ReturnXML Modifications-2012
47 -- +=====================================================================================================+
48 AS
49
50 gn_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
51
52 PROCEDURE message(p_message IN VARCHAR2)
53 AS
54 BEGIN
55 fnd_file.put_line(fnd_file.log,p_message);
56 END message;
57
58 FUNCTION beforeReport RETURN BOOLEAN
59 -- +======================================================================+
60 -- | Name : beforeReport |
61 -- | Description : This procedure processes the data before the |
62 -- | execution of report. |
63 -- | |
64 -- +======================================================================+
65 IS
66 /* Bug#5223170. Added hz_cust_acct_sites, hz_party_sites and join conditions */
67
68
69 l_precision NUMBER := 0;
70 l_curr_factor NUMBER := 1;
71
72 CURSOR lcu_be_annual_decl
73 IS
74 SELECT
75 MIN(JZVTD.billing_trading_partner_id ) CUSTOMER_ID
76 ,MIN(JZVTD.billing_tp_name) CUSTOMER_NAME
77 ,MIN(HZL.address1) STREET_NAME
78 ,MIN(HZL.postal_code) POSTAL_CODE
79 ,MIN(HZL.city) TOWN
80 ,JZVTD.billing_tp_site_tax_reg_num VAT_NUMBER
81 ,DECODE(P_REPORT_NAME,
82 'JEBEVA24',SUM(ROUND(NVL(JZVTD.taxable_amt_funcl_curr,0),l_precision)*l_curr_factor)
83 ,SUM(ROUND(NVL(JZVTD.taxable_amt_funcl_curr,0),l_precision))) AMOUNT
84 ,DECODE(P_REPORT_NAME,
85 'JEBEVA24',SUM(ROUND(NVL(JZVTD.tax_amt_funcl_curr,0),l_precision)*l_curr_factor)
86 ,SUM(ROUND(NVL(JZVTD.tax_amt_funcl_curr,0),l_precision))) TAX_AMOUNT
87 FROM jg_zz_vat_trx_details JZVTD
88 ,jg_zz_vat_rep_status JZVRS
89 ,ra_cust_trx_types RTT
90 ,ra_customer_trx RCT
91 ,hz_cust_acct_sites HZCAS
92 ,hz_party_sites HPS
93 ,hz_locations HZL
94 WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
95 AND JZVTD.reporting_status_id = JZVRS.reporting_status_id
96 AND RTT.cust_trx_type_id = JZVTD.trx_type_id
97 AND RTT.type IN ('INV','CM','DM')
98 AND JZVRS.tax_calendar_year = P_PERIOD
99 AND JZVRS.source = 'AR'
100 --AND JZVTD.billing_tp_address_id = HZL.location_id
101 AND JZVTD.billing_tp_address_id = HZCAS.cust_acct_site_id
102 AND HZCAS.party_site_id = HPS.party_site_id
103 AND HPS.location_id = HZL.location_id
104 AND SUBSTR(JZVTD.billing_tp_site_tax_reg_num,1,2)= 'BE'
105 AND RCT.customer_trx_id = JZVTD.trx_id
106 AND JZVTD.application_id = 222
107 AND JZVTD.entity_code = 'TRANSACTIONS'
108 AND NVL(RCT.interface_header_context,'X') <> 'Contra'
109 GROUP BY JZVTD.billing_tp_site_tax_reg_num
110 HAVING SUM(ROUND(NVL(JZVTD.taxable_amt_funcl_curr,0),l_precision)) >= P_MIN_AMOUNT
111 ORDER BY JZVTD.billing_tp_site_tax_reg_num;
112
113
114
115 l_address_line_1 VARCHAR2 (240);
116 l_address_line_2 VARCHAR2 (240);
117 l_address_line_3 VARCHAR2 (240);
118 l_address_line_4 VARCHAR2 (240);
119 l_city VARCHAR2 (60);
120 l_company_name VARCHAR2 (240);
121 l_contact_name VARCHAR2 (360);
122 l_country VARCHAR2 (60);
123 l_func_curr VARCHAR2 (30);
124 l_legal_entity_id NUMBER;
125 l_legal_entity_name VARCHAR2 (240);
126 l_period_end_date DATE;
127 l_period_start_date DATE;
128 l_phone_number VARCHAR2 (40);
129 l_postal_code VARCHAR2 (60);
130 l_registration_num VARCHAR2 (30);
131 l_reporting_status VARCHAR2 (60);
132 l_tax_payer_id VARCHAR2 (60);
133 l_tax_registration_num VARCHAR2 (240);
134 l_tax_regime VARCHAR2(240);
135 l_activity_code VARCHAR2(240);
136 cnt NUMBER:=0;
137 t_customer_name VARCHAR2(240);
138 t_street VARCHAR2(240);
139 t_postal_code VARCHAR2(240);
140 t_town VARCHAR2(240);
141 t_VAT_number VARCHAR2(240);
142 t_amount NUMBER := 0;
143 t_loop_amount NUMBER := 0;
144 t_tax_amount NUMBER := 0;
145 t_loop_tax NUMBER := 0;
146 t_file_totals VARCHAR2(32);
147 t_value NUMBER := 9999999999;
148 t_tax_value NUMBER := 9999999999;
149 t_total_amount NUMBER := 0;
150 t_total_tax_amount NUMBER := 0;
151 t_customer_id NUMBER;
152 l_error_pos NUMBER := 0;
153 l_vat_count NUMBER := 0;
154 -- Added for Glob-006 ER
155 l_province VARCHAR2(120);
156 l_comm_num VARCHAR2(30);
157 l_vat_reg_num VARCHAR2(50);
158 l_email_address VARCHAR2(240);
159
160
161 REPORT_LIMIT_REACHED EXCEPTION;
162 AMOUNT_LIMIT_REACHED EXCEPTION;
163 NO_TRANS_RECORDS EXCEPTION;
164
165
166
167 BEGIN
168 -- Call to Common Package
169 IF p_debug_flag = 'Y' THEN
170 fnd_file.put_line(fnd_file.log,'Executing jg_zz_turnover_ar_pkg.before_report');
171 fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.funct_curr_legal');
172 END IF;
173 BEGIN
174 jg_zz_common_pkg.funct_curr_legal(x_func_curr_code => l_func_curr
175 ,x_rep_entity_name => l_legal_entity_name
176 ,x_legal_entity_id => l_legal_entity_id
177 ,x_taxpayer_id => l_tax_payer_id
178 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
179 ,pn_period_year => p_period);
180 EXCEPTION
181 WHEN OTHERS THEN
182 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.funct_curr_legal failed with Error: '||SUBSTR(SQLERRM,1,200));
183 END;
184 IF p_debug_flag = 'Y' THEN
185 fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.tax_registration');
186 END IF;
187 BEGIN
188 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
189 ,x_period_start_date => l_period_start_date
190 ,x_period_end_date => l_period_end_date
191 ,x_status => l_reporting_status
192 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
193 ,pv_period_name => NULL
194 ,pn_period_year => p_period /*5223170*/
195 ,pv_source => 'ALL');
196 EXCEPTION
197 WHEN OTHERS THEN
198 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.tax_registration failed with Error: '||SUBSTR(SQLERRM,1,200));
199 END;
200
201
202 IF p_debug_flag = 'Y' THEN
203 fnd_file.put_line(fnd_file.log,'Calling jg_zz_vat_rep_utlity.get_report_status');
204 END IF;
205 BEGIN
206 l_reporting_status := jg_zz_vat_rep_utility.get_period_status
207 (
208 pn_vat_reporting_entity_id => p_vat_rep_entity_id,
209 pv_tax_calendar_period => NULL,
210 pv_tax_calendar_year => p_period,
211 pv_source => NULL,
212 pv_report_name => P_REPORT_NAME
213 );
214 EXCEPTION
215 WHEN OTHERS THEN
216 fnd_file.put_line(fnd_file.log,'Executing jg_zz_vat_rep_utility.get_report_status failed with Error: '||SUBSTR(SQLERRM,1,200));
217 END;
218
219 IF p_debug_flag = 'Y' THEN
220 fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.company_detail');
221 END IF;
222 BEGIN
223 jg_zz_common_pkg.company_detail(x_company_name => l_company_name
224 ,x_registration_number => l_registration_num
225 ,x_country => l_country
226 ,x_address1 => l_address_line_1
227 ,x_address2 => l_address_line_2
228 ,x_address3 => l_address_line_3
229 ,x_address4 => l_address_line_4
230 ,x_city => l_city
231 ,x_postal_code => l_postal_code
232 ,x_contact => l_contact_name
233 ,x_phone_number => l_phone_number
234 ,x_province => l_province
235 ,x_comm_number => l_comm_num
236 ,x_vat_reg_num => l_vat_reg_num
237 ,pn_legal_entity_id => l_legal_entity_id
238 ,p_vat_reporting_entity_id => p_vat_rep_entity_id);
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.company_detail failed with Error: '||SUBSTR(SQLERRM,1,200));
243 END;
244 BEGIN
245 SELECT activity_code
246 INTO l_activity_code
247 FROM xle_entity_profiles
248 WHERE legal_entity_id = l_legal_entity_id;
249 EXCEPTION
250 WHEN NO_DATA_FOUND THEN
251 fnd_file.put_line(fnd_file.log,'Cannot find Activity Code (Standard Inductry Classification Code for Legal Entity:'||l_legal_entity_id);
252 WHEN OTHERS THEN
253 fnd_file.put_line(fnd_file.log,'Error While retrieving Activity Code for Legal Entity:'||l_legal_entity_id);
254 fnd_file.put_line(fnd_file.log,'Error Message :'||SUBSTR(SQLERRM,1,200));
255 END;
256 BEGIN
257 SELECT hp.primary_phone_number PHONE_NUMBER
258 ,hp.email_address EMAIL_ADDRESS
259 INTO l_phone_number
260 ,l_email_address
261 FROM hz_parties hp,
262 (SELECT subject_id
263 FROM hz_relationships
264 WHERE object_id = (
265 SELECT party_id
266 FROM xle_firstparty_information_v
267 WHERE legal_entity_id = l_legal_entity_id
268 )
269 AND relationship_code = 'CONTACT_OF'
270 AND directional_flag = 'F'
271 ) q1
272 WHERE hp.party_id = q1.subject_id;
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 NULL;
276 WHEN OTHERS THEN
277 fnd_file.put_line(fnd_file.log,'Error While retrieving Contact information:'||l_legal_entity_id);
278 fnd_file.put_line(fnd_file.log,'Error Message :'||SUBSTR(SQLERRM,1,200));
279 END;
280
281 INSERT INTO jg_zz_vat_trx_gt
282 (
283 jg_info_n1
284 ,jg_info_v1
285 ,jg_info_v2
286 ,jg_info_v3
287 ,jg_info_v4
288 ,jg_info_v5
289 ,jg_info_v6
290 ,jg_info_v7
291 ,jg_info_v8
292 ,jg_info_v9
293 ,jg_info_v10
294 ,jg_info_v11
295 ,jg_info_v12
296 ,jg_info_v13
297 ,jg_info_v14
298 ,jg_info_v15
299 ,jg_info_v16
300 ,jg_info_v17
301 ,jg_info_d1
302 ,jg_info_d2
303 ,jg_info_v29
304 ,jg_info_v30
305 )
306 VALUES
307 (
308 l_legal_entity_id
309 ,l_company_name -- l_legal_entity_name
310 ,l_tax_registration_num
311 ,l_registration_num -- l_tax_payer_id
312 ,l_contact_name
313 ,l_address_line_1
314 ,l_address_line_2
315 ,l_address_line_3
316 ,l_address_line_4
317 ,l_city
318 ,l_country
319 ,l_phone_number
320 ,l_postal_code
321 ,l_func_curr
322 ,l_reporting_status
323 ,l_tax_regime
324 ,l_activity_code
325 ,l_tax_registration_num
326 ,l_period_end_date
327 ,l_period_start_date
328 ,l_email_address
329 ,'H'
330 );
331 IF p_debug_flag = 'Y' THEN
332 fnd_file.put_line(fnd_file.log,'Legal Entity ID =>' || l_legal_entity_id);
333 fnd_file.put_line(fnd_file.log,'Company Name =>' || l_company_name);
334 fnd_file.put_line(fnd_file.log,'Legal Entity Name =>' || l_company_name);
335 fnd_file.put_line(fnd_file.log,'Regiatration Number =>' || l_registration_num);
336 fnd_file.put_line(fnd_file.log,'Taxpayer ID =>' || l_registration_num);
337 fnd_file.put_line(fnd_file.log,'Contact Name =>' || l_contact_name);
338 fnd_file.put_line(fnd_file.log,'Address Line 1 =>' || l_address_line_1);
339 fnd_file.put_line(fnd_file.log,' 2 =>' || l_address_line_2);
340 fnd_file.put_line(fnd_file.log,' 3 =>' || l_address_line_3);
341 fnd_file.put_line(fnd_file.log,' 4 =>' || l_address_line_4);
342 fnd_file.put_line(fnd_file.log,'City =>' || l_city);
343 fnd_file.put_line(fnd_file.log,'Country =>' || l_country);
344 fnd_file.put_line(fnd_file.log,'Telephone Number =>' || l_phone_number);
345 fnd_file.put_line(fnd_file.log,'Postal Code =>' || l_postal_code);
346 fnd_file.put_line(fnd_file.log,'Currency Code =>' || l_func_curr);
347 fnd_file.put_line(fnd_file.log,'Reporting Status =>' || l_reporting_status);
348 fnd_file.put_line(fnd_file.log,'Period Start Date =>' || l_period_start_date);
349 fnd_file.put_line(fnd_file.log,' End Date =>' || l_period_end_date);
350 fnd_file.put_line(fnd_file.log,'Email address =>' || l_email_address);
351 END IF;
352 IF P_REPORT_NAME = 'JEBEVADC' OR P_REPORT_NAME = 'JEBEVA24' THEN
353
354 BEGIN
355 select precision
356 into l_precision
357 from fnd_currencies_vl
358 where currency_code = l_func_curr;
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 fnd_file.put_line(fnd_file.log,'No data found while retrieving precision. Check the currency code.');
362 WHEN OTHERS THEN
363 fnd_file.put_line(fnd_file.log,'Error While retrieving precision');
364 fnd_file.put_line(fnd_file.log,'Error Message :'||SUBSTR(SQLERRM,1,200));
365 END;
366
367 IF P_REPORT_NAME = 'JEBEVA24' THEN
368 if l_func_curr = gl_currency_api.get_euro_code() then
369 l_curr_factor := 100;
370 end if;
371 END IF;
372
373 OPEN lcu_be_annual_decl ;
374 LOOP
375 cnt := cnt+1;
376 l_error_pos := 5;
377 FETCH lcu_be_annual_decl INTO t_customer_id
378 , t_customer_name
379 , t_street
380 , t_postal_code
381 , t_town
382 , t_vat_number
383 , t_amount
384 , t_tax_amount ;
385
386 EXIT WHEN lcu_be_annual_decl%NOTFOUND;
387 IF p_debug_flag = 'Y' THEN
388 message( 'Customer ID '||t_customer_id||' VAT Number '||t_vat_number);
389 END IF;
390
391 -- Raise error if count reaches 999999
392 l_error_pos := 6;
393 IF cnt >= 999999 THEN
394 RAISE REPORT_LIMIT_REACHED;
395 END IF;
396 -- If amount is over field limit of 10 then do
397 -- splitting routine otherwise insert amounts
398 IF t_amount >= 0 and t_tax_amount >= 0 THEN
399
400 t_loop_amount := t_amount;
401 t_loop_tax := t_tax_amount;
402
403 LOOP
404 IF t_loop_amount <= t_value THEN
405 IF t_loop_tax <= t_tax_value THEN
406 l_error_pos := 7;
407 INSERT INTO jg_zz_vat_trx_gt
408 ( jg_info_n1
409 , jg_info_n2
410 , jg_info_v1
411 , jg_info_v2
412 , jg_info_v3
413 , jg_info_v4
414 , jg_info_v5
415 , jg_info_n3
416 , jg_info_n4
417 , jg_info_v6
418 )
419 VALUES
420 (cnt
421 , t_customer_id
422 , t_customer_name
423 , t_street
424 , t_postal_code
425 , t_town
426 , t_VAT_number
427 , t_loop_amount
428 , t_loop_tax
429 , l_func_curr);
430 EXIT;
431 ELSE
432 l_error_pos := 8;
433 INSERT INTO jg_zz_vat_trx_gt
434 ( jg_info_n1
435 , jg_info_n2
436 , jg_info_v1
437 , jg_info_v2
438 , jg_info_v3
439 , jg_info_v4
440 , jg_info_v5
441 , jg_info_n3
442 , jg_info_n4
443 , jg_info_v6
444 )
445 VALUES
446 (cnt
447 , t_customer_id
448 , t_customer_name
449 , t_street
450 , t_postal_code
451 , t_town
452 , t_VAT_number
453 , t_loop_amount
454 , t_tax_value
455 , l_func_curr);
456 cnt := cnt+1;
457 t_loop_tax := t_loop_tax - t_tax_value;
458 t_tax_value := t_tax_value - 1;
459 t_loop_amount := 0;
460 END IF;
461 ELSE
462 IF t_loop_tax <= t_tax_value THEN
463 l_error_pos := 9;
464 INSERT INTO jg_zz_vat_trx_gt
465 ( jg_info_n1
466 , jg_info_n2
467 , jg_info_v1
468 , jg_info_v2
469 , jg_info_v3
470 , jg_info_v4
471 , jg_info_v5
472 , jg_info_n3
473 , jg_info_n4
474 , jg_info_v6
475 )
476 VALUES
477 (cnt
478 , t_customer_id
479 , t_customer_name
480 , t_street
481 , t_postal_code
482 , t_town
483 , t_VAT_number
484 , t_value
485 , t_loop_tax
486 , l_func_curr);
487 t_loop_tax := 0;
488 ELSE
489 l_error_pos := 10;
490 INSERT INTO jg_zz_vat_trx_gt
491 ( jg_info_n1
492 , jg_info_n2
493 , jg_info_v1
494 , jg_info_v2
495 , jg_info_v3
496 , jg_info_v4
497 , jg_info_v5
498 , jg_info_n3
499 , jg_info_n4
500 , jg_info_v6
501 )
502 VALUES
503 (cnt
504 , t_customer_id
505 , t_customer_name
506 , t_street
507 , t_postal_code
508 , t_town
509 , t_VAT_number
510 , t_value
511 , t_tax_value
512 , l_func_curr);
513 t_loop_tax := t_loop_tax - t_tax_value;
514 t_tax_value := t_tax_value - 1;
515 END IF;
516 t_loop_amount := t_loop_amount - t_value;
517 t_value := t_value - 1;
518 cnt := cnt+1;
519 END IF;
520 END LOOP;
521 END IF;
522
523 -- Insert into JE_BE_ANNUAL_VAT amounts fetched for amounts or tax with less than 0 */
524 IF t_amount < 0 or t_tax_amount < 0 THEN
525 INSERT INTO jg_zz_vat_trx_gt
526 ( jg_info_n1
527 , jg_info_n2
528 , jg_info_v1
529 , jg_info_v2
530 , jg_info_v3
531 , jg_info_v4
532 , jg_info_v5
533 , jg_info_n3
534 , jg_info_n4
535 , jg_info_v6)
536 VALUES
537 (cnt
538 , t_customer_id
539 , t_customer_name
540 , t_street
541 , t_postal_code
542 , t_town
543 , t_VAT_number
544 , t_amount
545 , t_tax_amount
546 , l_func_curr);
547 l_error_pos := 11;
548 END IF;
549 t_total_amount := t_total_amount+t_amount;
550 t_total_tax_amount := t_total_tax_amount+t_tax_amount;
551 END LOOP;
552 CLOSE lcu_be_annual_decl ;
553
554 --Over flowing amount error check
555 l_error_pos := 12;
556 IF t_total_amount >= 9999999999999999 OR t_total_tax_amount >= 9999999999999999 THEN
557 RAISE AMOUNT_LIMIT_REACHED;
558 END IF;
559
560
561
562 /* Format reports totals for column2 then insert trailing record */
563 IF t_total_amount >= 0 and t_total_tax_amount >= 0 THEN
564 t_file_totals :=
565 lpad(t_total_amount,16,'0')||lpad(t_total_tax_amount,16,'0');
566 ELSIF t_total_amount >= 0 and t_total_tax_amount < 0 THEN
567 t_file_totals :=
568 lpad(t_total_amount,16,'0')||'-'||lpad(abs(t_total_tax_amount),15,'0');
569 ELSIF t_total_amount < 0 and t_total_tax_amount >= 0 THEN
570 t_file_totals :=
571 '-'||lpad(abs(t_total_amount),15,'0')||lpad(t_total_tax_amount,16,'0');
572 ELSE
573 t_file_totals :=
574 lpad(t_total_amount,16,'0')||lpad(t_total_tax_amount,16,'0');
575 END IF;
576
577 l_error_pos := 13;
578
579 -- Trailer information for JEBEVA24
580
581 INSERT INTO jg_zz_vat_trx_gt
582 ( jg_info_n1
583 , jg_info_v1
584 , jg_info_v2
585 , jg_info_v30
586 )
587 VALUES
588 ( 999999
589 ,t_file_totals
590 ,l_tax_registration_num
591 ,'JEBEV24-T');
592
593 -- Header information for JEBEVA24
594
595 INSERT INTO jg_zz_vat_trx_gt
596 ( jg_info_n1
597 , jg_info_v1
598 , jg_info_v2
599 , jg_info_v3
600 , jg_info_v4
601 , jg_info_v5
602 , jg_info_v6
603 , jg_info_n2
604 , jg_info_n3
605 , jg_info_v30
606 )
607 VALUES
608 ( 000000
609 , l_company_name
610 , l_address_line_1
611 , l_postal_code
612 , l_city
613 , l_tax_registration_num
614 , l_func_curr
615 , p_period
616 , 000
617 , 'JEBEV24-H');
618
619
620 END IF;
621
622 SELECT count(1)
623 INTO l_vat_count
624 FROM jg_zz_vat_trx_gt
625 WHERE jg_info_v30 IS NULL;
626
627 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt: ' || l_vat_count);
628
629 IF l_vat_count = 0 THEN
630 RAISE NO_TRANS_RECORDS;
631 END IF;
632 p_err_code := 0;
633 RETURN (TRUE);
634 EXCEPTION
635 WHEN NO_DATA_FOUND THEN
636 message( 'Error position: ' || to_char(l_error_pos) || ': No records found.');
637 p_err_code := 99;
638 IF l_error_pos = 15 THEN
639 message('VAT number ('||t_vat_number||') and customer site use are inconsistent.' );
640 END IF;
641 RETURN(TRUE);
642 WHEN REPORT_LIMIT_REACHED THEN
643 message('Error position: ' || to_char(l_error_pos) || ': Number of records exceeded the report limit.');
644 p_err_code := 10;
645 RETURN(FALSE);
646 WHEN AMOUNT_LIMIT_REACHED THEN
647 message( 'Error position: ' || to_char(l_error_pos) || ': Amount Overflow, total amount or tax has exceeded the limit.');
648 p_err_code := 20;
649 RETURN(FALSE);
650 WHEN NO_TRANS_RECORDS THEN
651 message('Error position: ' || to_char(l_error_pos) || ': There are no transactions that meet the criteria.');
652 p_err_code := 30;
653 RETURN(TRUE);
654 WHEN OTHERS THEN
655 message('Error in Before Report Trigger' || SUBSTR(SQLERRM,1,200));
656 RETURN (FALSE);
657 END beforeReport;
658
659 FUNCTION get_error_code RETURN NUMBER
660 IS BEGIN
661 RETURN p_err_code;
662 END;
663
664 END jg_zz_turnover_ar_pkg;