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