[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_AUDIT_AR_PKG
Source
1 PACKAGE BODY JG_ZZ_AUDIT_AR_PKG
2 -- $Header: jgzzauditarb.pls 120.12.12020000.2 2012/07/20 08:56:07 rshergil ship $
3 --*************************************************************************************
4 -- | Copyright (c) 1996 Oracle Corporation Redwood Shores, California, USA |
5 -- | All rights reserved. |
6 --*************************************************************************************
7 --
8 --
9 -- PROGRAM NAME
10 -- JGZZ_AUDITARB.pls
11 --
12 -- DESCRIPTION
13 -- Script to Create package specification for AUDIT-AR Report
14 --
15 -- HISTORY
16 -- =======
17 --
18 -- VERSION DATE AUTHOR(S) DESCRIPTION
19 -- ------- ----------- --------------- -----------------------------------------------------------
20 -- DRAFT 1A 18-Jan-2005 Murali V Initial draft version
21 -- DRAFT 1B 21-Feb-2006 Manish Upadhyay Modified as per the Review comments.
22 -- 120.1 01-MAR-2006 brathod Reference to jgzz_common_pkg changed to jg_zz_common_pkg
23 -- 120.3 26-APR-2006 brathod Bug: 5189166. Modified to correct code for Unit Testing.
24 -- 120.4 15-MAY-2006 Vijay Shankar Bug: 5125654. Rectified the issues that occured during Unit Testing.
25 -- 120.5 18-jul-2006 Aparajita Das Bug: 5225958. UT bug fix.
26 -- 120.6 04-Aug-2005 Venkataramanan S Bug 5194991 : Incorporated the reprint functionality
27 -- 120.12 08-Feb-2012 Rahul kumar Bug 13649715 : Modified cursor CURSOR c_jgbevat
28 -- 120.13 06-Mar-2012 Mallika Kandula Bug 13804055 : Reverted changes done as part of bug#13649715
29 --***********************************************************************************************************
30 AS
31
32 gv_debug constant boolean := false;
33
34
35 FUNCTION BeforeReport
36 RETURN BOOLEAN
37 IS
38 BEGIN
39 DECLARE
40
41 l_precision NUMBER := 0;
42
43 --JEBEVA17 Annual VAT Audit Report
44 /* Brathod, Updated the cursor to remove usage of hz_cust_account_sites by removing outer join.
45 Directly using jg_zz_vat_trx_details.billing_tp_site_id to joing with hps.party_site_id
46 */
47 CURSOR c_jgbevat IS
48 SELECT JZVTD.doc_seq_value BE_DOC_SEQ_VALUE
49 ,SUBSTR(JZVTD.billing_tp_name,1,25) CUSTOMER_NAME
50 ,SUBSTR(HZL.address1,1,18) ADDRESS1
51 ,SUBSTR(JZVTD.billing_tp_number,1,24) CUSTOMER_NUMBER
52 ,SUBSTR(HZL.postal_code,1,4) POSTAL_CODE
53 ,SUBSTR(HZL.city,1,22) CITY
54 ,JZVTD.trx_class_mng CLASS
55 ,JZVTD.trx_number DOC_NUMBER
56 ,JZVTD.trx_type_mng INVOICE_TYPE
57 ,JZVTD.trx_date DOCUMENT_DATE
58 ,JZVTD.gl_date GL_DATE
59 ,JZVTD.billing_tp_site_tax_reg_num TAX_REFERENCE
60 ,ROUND(NVL(JZVTD.taxable_amt_funcl_curr, taxable_amt)
61 +NVL(JZVTD.tax_amt_funcl_curr, tax_amt), l_precision) TOTAL_AMOUNT
62 ,ROUND(NVL(JZVTD.taxable_amt_funcl_curr, taxable_amt), l_precision) TAXABLE_AMOUNT
63 ,ROUND(NVL(JZVTD.tax_amt_funcl_curr, tax_amt), l_precision) TAX_AMOUNT
64 FROM jg_zz_vat_trx_details JZVTD
65 ,jg_zz_vat_rep_status JZVRS
66 ,hz_cust_acct_sites_all HZCAS
67 ,hz_party_sites HPS
68 ,hz_locations HZL
69 ,ra_customer_trx_all RCTA
70 ,ra_cust_trx_types RTT
71 WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
72 AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
73 AND JZVRS.source = 'AR'
74 AND JZVRS.tax_calendar_year = p_year
75 AND JZVTD.billing_tp_address_id = HZCAS.cust_acct_site_id
76 AND HZCAS.party_site_id = HPS.party_site_id (+)
77 AND HPS.location_id = HZL.location_id (+)
78 AND SUBSTR(JZVTD.billing_tp_site_tax_reg_num,1,2) = 'BE'
82 OR
79 AND JZVTD.trx_id = RCTA.customer_trx_id
80 AND (
81 p_customer_name_from IS NULL
83 ( JZVTD.billing_tp_name BETWEEN
84 p_customer_name_from AND NVL(p_customer_name_to,JZVTD.billing_tp_name)
85 )
86 )
87 AND RTT.cust_trx_type_id = JZVTD.trx_type_id
88 AND RTT.type IN ('INV','CM','DM')
89 AND NVL(UPPER(RCTA.interface_header_context),'X') <> 'CONTRA'
90 ;
91
92 --JECZAREX Czech Export Tax Report
93 /* brathod, Modified cursor to remove usage of fnd_lookups and jg_zz_vat_box_allocs. */
94 CURSOR c_jgczvat IS
95 SELECT JZVTD.doc_seq_value CZ_DOC_SEQ_VALUE
96 ,JZVTD.functional_currency_code FUNCTIONAL_CURRENCY_CODE
97 ,JZVTD.TAX_RATE_CODE_VAT_TRX_TYPE_MNG VAT_BOX
98 ,JZVTD.TAX_RATE_VAT_TRX_TYPE_DESC VAT_BOX_DESC
99 ,JZVTD.trx_description TRANSACTION_DESC
100 ,JZVTD.trx_number DOC_NUMBER
101 ,JZVTD.tax_invoice_date TAX_DATE
102 ,JZVTD.accounting_date GL_DATE
103 ,NVL(JZVTD.taxable_amt_funcl_curr,taxable_amt) TAXABLE_AMOUNT
104 FROM jg_zz_vat_trx_details JZVTD
105 ,jg_zz_vat_rep_status JZVRS
106 ,ra_customer_trx_all RCTA
107 WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
108 AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
109 AND JZVRS.tax_calendar_period = p_period
110 AND JZVTD.trx_id = RCTA.customer_trx_id
111 AND JZVRS.source = 'AR'
112 AND JZVTD.trx_line_class IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')
113 -- bug 8616974 - start
114 --AND (p_tax_type is null or JZVTD.tax_type_code = p_tax_type)
115 AND (p_tax_type is null or JZVTD.reporting_code = p_tax_type)
116 -- bug 8616974 - end
117 AND NVL(UPPER(RCTA.interface_header_context),'X') <> 'CONTRA'
118 ;
119
120 --AUDIT-AR
121 CURSOR c_jgzzaudar IS
122 SELECT JZVTD.doc_seq_value AR_DOC_SEQ_VALUE
123 ,SUBSTR(JZVTD.billing_tp_name,1,25) CUSTOMER_NAME
124 ,SUBSTR(HZ.address1,1,18) ADDRESS1
125 ,SUBSTR(JZVTD.billing_tp_number,1,24) CUSTOMER_NUMBER
126 ,SUBSTR(HZ.postal_code,1,4) POSTAL_CODE
127 ,SUBSTR(HZ.city,1,22) CITY
128 ,JZVTD.trx_class_mng CLASS
129 ,JZVTD.trx_number DOC_NUMBER
130 ,JZVTD.trx_type_mng INVOICE_TYPE
131 ,JZVTD.billing_tp_site_tax_reg_num TAX_REFERENCE
132 ,JZVTD.functional_currency_code FUNCTIONAL_CURRENCY_CODE
133 ,JZVTD.TAX_RATE_CODE_VAT_TRX_TYPE_MNG VAT_BOX
134 ,JZVTD.TAX_RATE_VAT_TRX_TYPE_DESC VAT_BOX_DESC
135 ,JZVTD.trx_description TRANSACTION_DESC
136 ,JZVTD.trx_date DOCUMENT_DATE
137 ,JZVTD.accounting_date GL_DATE
138 ,JZVTD.tax_invoice_date TAX_DATE
139 ,(NVL(JZVTD.taxable_amt_funcl_curr, taxable_amt)
140 +NVL(JZVTD.tax_amt_funcl_curr, tax_amt)) TOTAL_AMOUNT
141 ,NVL(JZVTD.taxable_amt_funcl_curr, taxable_amt) TAXABLE_AMOUNT
142 ,NVL(JZVTD.tax_amt_funcl_curr, tax_amt) TAX_AMOUNT
143 FROM jg_zz_vat_trx_details JZVTD
144 ,jg_zz_vat_rep_status JZVRS
145 ,hz_locations HZ
146 ,hz_party_sites HPS
147 ,ra_customer_trx_all RCTA
148 WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
149 AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
150 AND JZVRS.source = 'AR'
151 AND JZVRS.tax_calendar_period = p_period
152 AND JZVTD.billing_tp_site_id = HPS.party_site_id (+)
153 AND HZ.location_id (+) = HPS.location_id
154 AND JZVTD.trx_id = RCTA.customer_trx_id
155 AND JZVTD.trx_line_class IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')
156 AND NVL(UPPER(RCTA.interface_header_context),'X') <> 'CONTRA'
157 ;
158
159 -- Record count check
160 CURSOR c_count
161 IS
162 SELECT COUNT(*)
163 FROM jg_zz_vat_trx_gt;
164
165 l_rec_count NUMBER;
166 lc_curr_code VARCHAR2(50);
167 lc_rep_entity_name jg_zz_vat_trx_details.rep_context_entity_name%TYPE;
168 ln_legal_entity_id NUMBER;
169 ln_taxpayer_id jg_zz_vat_trx_details.taxpayer_id%TYPE;
170 lc_company_name xle_registrations.registered_name%TYPE;
171 lc_registration_number xle_registrations.registration_number%TYPE;
172 lc_country hz_locations.country%TYPE;
173 lc_address1 hz_locations.address1%TYPE;
174 lc_address2 hz_locations.address2%TYPE;
175 lc_address3 hz_locations.address3%TYPE;
176 lc_address4 hz_locations.address4%TYPE;
177 lc_city hz_locations.city%TYPE;
178 lc_postal_code hz_locations.postal_code%TYPE;
182 l_province VARCHAR2(120);
179 lc_contact hz_parties.party_name%TYPE;
180 lc_phone_number hz_contact_points.phone_number%TYPE;
181 -- Added for Glob-006 ER
183 l_comm_num VARCHAR2(30);
184 l_vat_reg_num VARCHAR2(50);
185
186
187 lc_tax_registration jg_zz_vat_rep_status.tax_registration_number%TYPE;
188 ld_period_start_date jg_zz_vat_rep_status.period_start_date%TYPE ;
189 ld_period_end_date jg_zz_vat_rep_status.period_end_date%TYPE ;
190 lc_status VARCHAR2(25);
191
192 t_total_amount NUMBER := 0;
193
194 REPORT_LIMIT_REACHED EXCEPTION;
195 AMOUNT_LIMIT_REACHED EXCEPTION;
196
197 BEGIN
198
199 -- Call to common pack for Header Info.
200 -- Calling this package first because, currency_code is needed for getting precision
201 if gv_debug then fnd_file.put_line(fnd_file.log,'Calling common pack Procedure FUNCT_CURR_LEGAL'); end if;
202
203 /* changed from JGZZ_COMMON_PKG to JG_ZZ_COMMON_PKG */
204 JG_ZZ_COMMON_PKG.funct_curr_legal(lc_curr_code -- x_func_curr_code O VARCHAR2
205 ,lc_rep_entity_name -- x_rep_entity_name O VARCHAR2
206 ,ln_legal_entity_id -- x_legal_entity_id O NUMBER
207 ,ln_taxpayer_id -- x_taxpayer_id O NUMBER
208 ,p_vat_rep_entity_id -- pn_vat_rep_entity_id I NUMBER
209 ,p_period -- pv_period_name I VARCHAR2 DEFAULT NULL
210 ,p_year -- pn_period_year I NUMBER DEFAULT NULL
211 );
212
213 SELECT precision
214 INTO l_precision
215 FROM fnd_currencies_vl
216 WHERE currency_code = lc_curr_code;
217
218 IF p_report_name = 'JEBEVA17' THEN
219
220 if gv_debug then
221 fnd_file.put_line(fnd_file.log,'Insert JEBEVA17 Annual VAT Audit Report Info');
222 end if;
223
224 l_rec_count:=0;
225 t_total_amount := 0;
226 FOR r_jgbevat IN c_jgbevat
227 LOOP
228 INSERT INTO jg_zz_vat_trx_gt
229 (jg_info_v1 -- BE_DOC_SEQ_VALUE
230 ,jg_info_v2 -- CUSTOMER_NAME
231 ,jg_info_v3 -- ADDRESS1
232 ,jg_info_v4 -- CUSTOMER_NUMBER
233 ,jg_info_v5 -- POSTAL_CODE
234 ,jg_info_v6 -- CITY
235 ,jg_info_v7 -- CLASS
236 ,jg_info_v8 -- DOC_NUMBER
237 ,jg_info_v9 -- INVOICE_TYPE
238 ,jg_info_d1 -- DOCUMENT_DATE
239 ,jg_info_d2 -- GL_DATE
240 ,jg_info_v10 -- TAX_REFERENCE
241 ,jg_info_n1 -- TOTAL_AMOUNT
242 ,jg_info_n2 -- TAXABLE_AMOUNT
243 ,jg_info_n3 -- TAX_AMOUNT
244 ) VALUES
245 (r_jgbevat.be_doc_seq_value
246 ,r_jgbevat.customer_name
247 ,r_jgbevat.address1
248 ,r_jgbevat.customer_number
249 ,r_jgbevat.postal_code
250 ,r_jgbevat.city
251 ,r_jgbevat.class
252 ,r_jgbevat.doc_number
253 ,r_jgbevat.invoice_type
254 ,r_jgbevat.document_date
255 ,r_jgbevat.gl_date
256 ,r_jgbevat.tax_reference
257 ,r_jgbevat.total_amount
258 ,r_jgbevat.taxable_amount
259 ,r_jgbevat.tax_amount
260 );
261 l_rec_count := l_rec_count + 1;
262
263 -- Raise error if count reaches 999999
264 IF l_rec_count >= 999999 THEN
265 RAISE REPORT_LIMIT_REACHED;
266 END IF;
267
268 t_total_amount := t_total_amount + r_jgbevat.total_amount;
269 IF t_total_amount >= 9999999999999999 THEN
270 RAISE AMOUNT_LIMIT_REACHED;
271 END IF;
272
273 END LOOP;
274 ELSIF p_report_name = 'JECZAREX' THEN
275 if gv_debug then fnd_file.put_line(fnd_file.log,'Insert JECZAREX Czech Export Tax Report Info'); end if;
276
277 l_rec_count:=0;
278 FOR r_jgczvat IN c_jgczvat
279 LOOP
280 INSERT INTO jg_zz_vat_trx_gt
281 (jg_info_v1 -- CZ_DOC_SEQ_VALUE
282 ,jg_info_v2 -- FUNCTIONAL_CURRENCY_CODE
283 ,jg_info_v3 -- VAT_BOX
284 ,jg_info_v4 -- VAT_BOX_DESC
285 ,jg_info_v5 -- TRANSACTION_DESC
286 ,jg_info_v8 -- DOC_NUMBER
287 ,jg_info_d1 -- TAX_DATE
288 ,jg_info_d2 -- GL_DATE
289 ,jg_info_n2 -- TAXABLE_AMOUNT
290 ) VALUES
291 (r_jgczvat.cz_doc_seq_value
292 ,r_jgczvat.functional_currency_code
293 ,r_jgczvat.vat_box
294 ,r_jgczvat.vat_box_desc
295 ,r_jgczvat.transaction_desc
296 ,r_jgczvat.doc_number
297 ,r_jgczvat.tax_date
298 ,r_jgczvat.gl_date
299 ,r_jgczvat.taxable_amount
300 );
301 END LOOP;
302 ELSIF NVL(p_report_name,'AUDIT-AR') = 'AUDIT-AR' THEN
303 if gv_debug then fnd_file.put_line(fnd_file.log,'Insert AUDIT-AR Info'); end if;
304
305 l_rec_count:=0;
306 FOR r_jgzzaudar IN c_jgzzaudar
310 ,jg_info_v2 -- CUSTOMER_NAME
307 LOOP
308 INSERT INTO jg_zz_vat_trx_gt
309 (jg_info_v1 -- AR_DOC_SEQ_VALUE
311 ,jg_info_v3 -- ADDRESS1
312 ,jg_info_v4 -- CUSTOMER_NUMBER
313 ,jg_info_v5 -- POSTAL_CODE
314 ,jg_info_v6 -- CITY
315 ,jg_info_v7 -- CLASS
316 ,jg_info_v8 -- DOC_NUMBER
317 ,jg_info_v9 -- INVOICE_TYPE
318 ,jg_info_v10 -- TAX_REFERENCE
319 ,jg_info_v11 -- FUNCTIONAL_CURRENCY_CODE
320 ,jg_info_v12 -- VAT_BOX
321 ,jg_info_v13 -- VAT_BOX_DESC
322 ,jg_info_v14 -- TRANSACTION_DESC
323 ,jg_info_d1 -- DOCUMENT_DATE
324 ,jg_info_d2 -- GL_DATE
325 ,jg_info_d3 -- TAX_DATE
326 ,jg_info_n1 -- TOTAL_AMOUNT
327 ,jg_info_n2 -- TAXABLE_AMOUNT
328 ,jg_info_n3 -- TAX_AMOUNT
329 ) VALUES
330 (r_jgzzaudar.ar_doc_seq_value
331 ,r_jgzzaudar.customer_name
332 ,r_jgzzaudar.address1
333 ,r_jgzzaudar.customer_number
334 ,r_jgzzaudar.postal_code
335 ,r_jgzzaudar.city
336 ,r_jgzzaudar.class
337 ,r_jgzzaudar.doc_number
338 ,r_jgzzaudar.invoice_type
339 ,r_jgzzaudar.tax_reference
340 ,r_jgzzaudar.functional_currency_code
341 ,r_jgzzaudar.vat_box
342 ,r_jgzzaudar.vat_box_desc
343 ,r_jgzzaudar.transaction_desc
344 ,r_jgzzaudar.document_date
345 ,r_jgzzaudar.gl_date
346 ,r_jgzzaudar.tax_date
347 ,r_jgzzaudar.total_amount
348 ,r_jgzzaudar.taxable_amount
349 ,r_jgzzaudar.tax_amount
350 );
351 END LOOP;
352 END IF;
353 if gv_debug then fnd_file.put_line(fnd_file.log,'Global temp table records check'); end if;
354
355 OPEN c_count;
356 FETCH c_count INTO p_rec_count;
357 CLOSE c_count;
358
359 if gv_debug then fnd_file.put_line(fnd_file.log,'Calling common pack Procedure TAX_REGISTRATION'); end if;
360
361 /* changed from JGZZ_COMMON_PKG to JG_ZZ_COMMON_PKG */
362 JG_ZZ_COMMON_PKG.tax_registration(lc_tax_registration -- x_tax_registration O VARCHAR2
363 ,ld_period_start_date -- x_period_start_date O DATE
364 ,ld_period_end_date -- x_period_end_date O DATE
365 ,lc_status -- x_status O VARCHAR2
366 ,p_vat_rep_entity_id -- pn_vat_rep_entity_id I NUMBER
367 ,p_period -- pv_period_name I VARCHAR2 DEFAULT NULL
368 ,p_year -- pn_period_year I NUMBER DEFAULT NULL
369 ,'AR' -- pv_source I VARCHAR2
370 );
371
372 if gv_debug then fnd_file.put_line(fnd_file.log,'Calling common pack Procedure COMPANY_DETAIL'); end if;
373
374 /* changed from JGZZ_COMMON_PKG to JG_ZZ_COMMON_PKG */
375
376 jg_zz_common_pkg.company_detail(x_company_name => lc_company_name
377 ,x_registration_number => lc_registration_number
378 ,x_country => lc_country
379 ,x_address1 => lc_address1
380 ,x_address2 => lc_address2
381 ,x_address3 => lc_address3
382 ,x_address4 => lc_address4
383 ,x_city => lc_city
384 ,x_postal_code => lc_postal_code
385 ,x_contact => lc_contact
386 ,x_phone_number => lc_phone_number
387 ,x_province => l_province
388 ,x_comm_number => l_comm_num
389 ,x_vat_reg_num => l_vat_reg_num
390 ,pn_legal_entity_id => ln_legal_entity_id
391 ,p_vat_reporting_entity_id => P_VAT_REP_ENTITY_ID);
392
393
394 lc_status := JG_ZZ_VAT_REP_UTILITY.get_period_status(pn_vat_reporting_entity_id => p_vat_rep_entity_id
395 ,pv_tax_calendar_period => p_period
396 ,pv_tax_calendar_year => p_year
397 ,pv_source => NULL
398 ,pv_report_name => p_report_name);
399
400
401 if gv_debug then fnd_file.put_line(fnd_file.log,'Calling common pack Inserting Header Info'); end if;
402
403 INSERT INTO jg_zz_vat_trx_gt (jg_info_v1 -- curr_code
404 ,jg_info_v2 -- entity_name
405 ,jg_info_v3 -- taxpayer_id
406 ,jg_info_v4 -- company_name
407 ,jg_info_v5 -- registration_number
408 ,jg_info_v6 -- country
409 ,jg_info_v7 -- address1
410 ,jg_info_v8 -- address2
411 ,jg_info_v9 -- address3
412 ,jg_info_v10 -- address4
413 ,jg_info_v11 -- city
414 ,jg_info_v12 -- postal_code
415 ,jg_info_v13 -- contact
416 ,jg_info_v14 -- phone_number
417 ,jg_info_v15 -- reporting mode
418 ,jg_info_v30 -- Header record indicator
419 ,jg_info_d1 -- start_date
420 ,jg_info_d2 -- end_date
421 )
422 VALUES (lc_curr_code -- curr_code
423 ,lc_company_name -- lc_rep_entity_name -- entity_name
424 ,ln_taxpayer_id -- ln_taxpayer_id -- taxpayer_id
425 ,lc_company_name -- company_name
426 ,lc_tax_registration -- registration_number
427 ,lc_country -- country
428 ,lc_address1 -- address1
429 ,lc_address2 -- address2
430 ,lc_address3 -- address3
431 ,lc_address4 -- address4
432 ,lc_city -- city
433 ,lc_postal_code -- postal_code
434 ,lc_contact -- contact
435 ,lc_phone_number -- phone_number
436 ,lc_status -- reporting mode
437 ,'H' -- Header record indicator
438 ,ld_period_start_date -- start_date
439 ,ld_period_end_date -- end_date
440 );
441
442 RETURN (TRUE);
443 EXCEPTION
444 /*UT TEST
445 WHEN NO_DATA_FOUND THEN
446 FND_FILE.PUT_LINE(FND_FILE.LOG,'No data found during Before Report Trigger');
447 RETURN (FALSE); */
448
449 WHEN REPORT_LIMIT_REACHED THEN
450 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of records exceeded the report limit.');
451 RETURN (FALSE);
452
453 WHEN AMOUNT_LIMIT_REACHED THEN
454 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Amount Overflow, total amount or tax has exceeded the limit.');
455 RETURN (FALSE);
456
457 WHEN OTHERS THEN
458 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in beforeReport trigger of JG_ZZ_AUDIT_AR_PKG package. Error-' || SQLCODE || SUBSTR(SQLERRM,1,200));
459 RETURN (FALSE);
460 END;
461
462 RETURN (TRUE);
463
464 EXCEPTION
465 WHEN OTHERS THEN
466 RETURN (FALSE);
467 END BeforeReport;
468
469 END JG_ZZ_AUDIT_AR_PKG;