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