[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_SUMMARY_ALL_PKG
Source
1 PACKAGE BODY JG_ZZ_SUMMARY_ALL_PKG
2 -- $Header: jgzzsummaryallb.pls 120.31.12010000.11 2009/02/20 10:39:50 rahulkum ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1996 Oracle Corporation Redwood Shores, California, USA|
5 -- | All rights reserved. |
6 -- +======================================================================+
7 -- NAME: JG_ZZ_SUMMARY_ALL_PKG
8 --
9 -- DESCRIPTION: This Package is the default Package containing the Procedures
10 -- used by SUMMARY-ALL Extract
11 --
12 -- NOTES:
13 --
14 -- Change Record:
15 -- ===============
16 -- Version Date Author Remarks
17 -- ======= =========== ==================== ===========================+
18 -- DRAFT 1A 04-Feb-2006 Balachander Ganesh Initial draft version
19 -- DRAFT 1B 21-Feb-2006 Balachander Ganesh Updated with Review
20 -- comments from IDC
21 -- DRAFT 1C 22-Feb-2006 Suresh Pasupunuri Included the code for RTP
22 -- DRAFT 1C 27-Feb-2006 Suresh Pasupunuri sum_fixedformula,
23 -- sum_other_formula,
24 -- cf_total_vat_chargedformula
25 -- vat_total_chargedformula,
26 -- cf_vat_total_inputformula
27 -- functions are removed.
28 -- DRAFT 1D 23-Mar-2006 Suresh Pasupunuri Added a function
29 -- JEITPSSR_AMOUNT_TO_PAY for
30 -- calculating the AMOUNT_TO_PAY
31 -- for Italy Payables
32 -- Summary VAT Report.
33 -- Added a CREDIT_BALANCE_AMT
34 -- column to Italy Payables Summary
35 -- VAT Report query.
36 -- DRAFT 1E 03-Apr-2006 Suresh Pasupunuri JEITPSSR_AMOUNT_TO_PAY Function
37 -- has modified.
38 -- 120.6 26-Apr-2006 Ramananda Pokkula In Proceudre jeptavat: Removed
39 -- dummy insert statements. Added
40 -- missing join condition:
41 -- AL.vat_transaction_id = JG.vat_transaction_id
42 -- In the call jg_zz_common_pkg.tax_registration
43 -- pn_period_year parameter is changed to
44 -- pv_period_name
45 -- 120.7 28-Apr-2006 Ramananda Pokkula Dummy assignments are removed
46 -- 120.8 31-May-2006 Ramananda Pokkula Refer bug#5258440 for UT changes
47 -- 120.9 06-July-2006 Suresh.Pasupunuri Created a new cursor
48 -- get_legal_auth_info to get the Tax Office Code
49 -- and Tax Office Location for Portuguese
50 -- Periodic and Annual VAT report.
51 -- c_pt_periodic_vat and c_pt_annual_vat cursors
52 -- are modified.
53 -- 120.10 10-Jul-2006 Suresh.Pasupunuri Implemented the SIGN_INDICATOR
54 -- logic in the follwoing cursors.
55 -- c_pt_periodic_vat and c_pt_annual_vat.
56 -- 120.11 20-Jul-2006 Suresh.Pasupunuri TRN and Tax Payer ID columns are
57 -- showing same data in the report output.
58 -- Because while inserting the header
59 -- information in to the GT table, we are
60 -- inserting the value
61 -- l_registration_num as TRN instead of
62 -- l_tax_registration_num.
63 -- 120.12 20-Jul-2006 Rukmani Basker Cursor c_belgian_vat is modified to
64 -- remove incorrect
65 -- tables/column references.
66 -- 120.13 25-Jul-2006 Rukmani Basker Bug 5407549. Incorrect reference to _GT
67 -- table.
68 -- 120.15 26-Jul-2006 Bhavik Rathod Bug:5408280. Modified to remove usage
69 -- of sign_indicator column
70 -- as it has been dropped.
71 -- 120.17 18-AUG-2006 RJREDDY Bug: 5406944. Modified "c_belgian_vat"
72 -- cursor in "jebeva06" procedure,
73 -- to select '99' when tax_box or
74 -- taxable_box is NULL.
75 -- 120.18 05-SEP-2006 KASBALAS Implemented changes for Reprint i
76 -- functionality.
77 -- 120.19 22-SEP-2006 RBASKER Bug 5553571. Modification done to debug
78 -- message printing done for jeptpvat
79 -- to avoid ORA 1403.
80 -- 120.20 25-AUG-2006 SPASUPUN Bug:5553811. Modified the cursor-c_pt_annual_vat
81 -- select query for column tax_class.
82 -- Added a separate call to API
83 -- jg_zz_common_pkg.tax_registration for report
84 -- JEPTAVAT. Included some debug messages.
85 -- 120.21 05-SEP-2006 RJREDDY Bug: 5563317. Modified c_belgian_vat cursor
86 -- query, so that the Taxable_Amt should always be
87 -- multiplied by taxable_non_rec_sign_flag and Tax_Amt
88 -- by appropriate Tax sign flag.
89 -- 120.22 16-OCT-2006 SPASUPUN Bug:5573113 - In procedure JEITPSSR , while inserting the
90 -- data into temp table , there is a wrong column mapping i.e.
91 -- column jg_info_d1 from tmp table is mapping with gl_transfer_flag
92 -- coulmn from jg table. Due to this the report is erroring out when
93 -- gl_transfer_flag is not null. Commented this invalid mapping,
94 -- as column are not used in reporting.
95 -- 120.23 06-NOV-2006 RBASKER Bug 5561879: PT Periodic VAT is not
96 -- picking AR and GL transactions. Fixed
97 -- by having proper check for recover-
98 -- ability. The cursor for LegalAuthority
99 -- is modified to have proper joins. Made
100 -- changes to PT Annual VAT report to
101 -- report GL transactions.
102 -- 120.24 12-DEC-2006 PMADDULA Bug 5674047 - In Procedure JEITPSSR,
103 -- added a new column assessable_value to
104 -- the cursor 'c_italian_vat' in JEITPSSR procedure.
105 -- Populated the column 'jg_ingo_n11' of table
106 -- jg_zz_vat_trx_gt with the value obtained.
107 -- This is to display correct taxable amount
108 -- when the tax type code of the transaction
109 -- is eitther 'Custom Bill' or 'Self Invoice'.
110 -- 120.26 20-DEC-2006 RJREDDY Bug 5718147: Changed the c_belgian_vat cursor query in JEBEVA06 procedure,
111 -- so that the Tax and Taxable amounts for the transactions will be multiplied with
112 -- -1 or 1 when the allocated Box sign is -ve or +ve respectively.
113 -- 120.27 24-JUL-2007 ASHDAS Bug 6189243: Changed the query "c_belgian_vat" in the procedure "jebeva06".
114 -- In the query used the column "language" instead of "source_lang" for the table "fnd_lookup_values"
115 -- 120.28 27-AUG-2008 ASHDAS Bug 7344931 Changed the c_belgian_vat and instead of Transaction date now GL_date is fetched.
116 -- 120.35 29-JAN-2009 RAHULKUM Bug 7633948 Modified the JEITPSSR_AMOUNT_TO_PAY and added a new function JEITPSSR_AMOUNT_TO_PAY_UPDATE.
117 -- 120.36 10-FEB-2009 RAHULKUM BUG:8237932 Modified the JEITPSSR_AMOUNT_TO_PAY.Added NVL function to CARRY_OVER.Replicating the changes of bug 7380506.
118 -- 120.37 16-FEB-2009 RAHULKUM BUG:8237932 Modified the JEITPSSR_AMOUNT_TO_PAY.Revertback the changes done for replicating the changes of bug 7380506.
119 -- 120.38 17-FEB-2009 RAHULKUM BUG:8237932 Modified the JEITPSSR_AMOUNT_TO_PAY.Added three new cursorsc_get_last_process_date ,c_get_balance,c_get_flag to replace c_get_details.
120 -- 120.39 18-FEB-2009 RAHULKUM BUG:8237932 Modified the JEITPSSR_AMOUNT_TO_PAY.Added raise_application_error to terminate the program in case of exception.
121 -- 120.40 20-FEB-2009 RAHULKUM BUG:8237932 Added TRUNC function for date in c_get_balance cursor.
122 -- +===========================================================================+
123 AS
124 FUNCTION before_report RETURN BOOLEAN
125 IS
126 CURSOR get_legal_auth_info(p_le_id number)
127 IS
128 SELECT xle_auth.city tax_office_location
129 ,xle_auth.address3 tax_office_code
130 FROM XLE_FIRSTPARTY_INFORMATION_V xfpiv
131 , xle_registrations xle_reg
132 , xle_legalauth_v xle_auth
133 WHERE xle_reg.source_id = xfpiv.legal_entity_id
134 AND xle_reg.source_table = 'XLE_ENTITY_PROFILES'
135 AND xle_auth.legalauth_id (+) = xle_reg.issuing_authority_id
136 AND xle_reg.identifying_flag = 'Y'
137 AND xfpiv.legislative_cat_code = 'INCOME_TAX'
138 AND xfpiv.legal_entity_id = p_le_id;
139
140 l_address_line_1 VARCHAR2 (240);
141 l_address_line_2 VARCHAR2 (240);
142 l_address_line_3 VARCHAR2 (240);
143 l_address_line_4 VARCHAR2 (240);
144 l_city VARCHAR2 (60);
145 l_company_name VARCHAR2 (240);
146 l_contact_name VARCHAR2 (360);
147 l_country VARCHAR2 (60);
148 l_func_curr VARCHAR2 (30);
149 l_legal_entity_id NUMBER;
150 l_legal_entity_name VARCHAR2 (240);
151 l_period_end_date DATE;
152 l_period_start_date DATE;
153 l_phone_number VARCHAR2 (40);
154 l_postal_code VARCHAR2 (60);
155 l_registration_num VARCHAR2 (30);
156 l_reporting_status VARCHAR2 (60);
157 l_tax_payer_id VARCHAR2 (60);
158 l_tax_registration_num VARCHAR2 (240);
159 l_tax_regime VARCHAR2(240);
160 l_activity_code VARCHAR2(240);
161 l_vat_register_name VARCHAR2(500);
162 l_tax_office_location xle_legalauth_v.city%TYPE;
163 l_tax_office_code xle_legalauth_v.address3%TYPE;
164 l_precision NUMBER;
165 -- Added for Glob-006 ER
166 l_province VARCHAR2(120);
167 l_comm_num VARCHAR2(30);
168 l_vat_reg_num VARCHAR2(50);
169
170 BEGIN
171 IF p_debug_flag = 'Y' THEN
172 fnd_file.put_line(fnd_file.log,' *** Report Parameters *** ');
173 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID : ' || P_VAT_REP_ENTITY_ID ) ;
174 fnd_file.put_line(fnd_file.log,'P_PERIOD : ' || P_PERIOD ) ;
175 fnd_file.put_line(fnd_file.log,'P_VAT_BOX : ' || P_VAT_BOX ) ;
176 fnd_file.put_line(fnd_file.log,'P_VAT_TRX_TYPE_LOW : ' || P_VAT_TRX_TYPE_LOW ) ;
177 fnd_file.put_line(fnd_file.log,'P_VAT_TRX_TYPE_HIGH : ' || P_VAT_TRX_TYPE_HIGH ) ;
178 fnd_file.put_line(fnd_file.log,'P_VAT_TRX_TYPE : ' || P_VAT_TRX_TYPE ) ;
179 fnd_file.put_line(fnd_file.log,'P_REPORT_FORMAT : ' || P_REPORT_FORMAT ) ;
180 fnd_file.put_line(fnd_file.log,'P_BOX_FROM : ' || P_BOX_FROM ) ;
181 fnd_file.put_line(fnd_file.log,'P_BOX_TO : ' || P_BOX_TO ) ;
182 fnd_file.put_line(fnd_file.log,'P_SOURCE : ' || P_SOURCE ) ;
183 fnd_file.put_line(fnd_file.log,'P_DOC_NAME : ' || P_DOC_NAME ) ;
184 fnd_file.put_line(fnd_file.log,'P_DOC_SEQ_VALUE : ' || P_DOC_SEQ_VALUE ) ;
185 fnd_file.put_line(fnd_file.log,'P_REPORT_NAME : ' || P_REPORT_NAME ) ;
186 fnd_file.put_line(fnd_file.log,'P_VAR_ON_PURCHASES : ' || P_VAR_ON_PURCHASES ) ;
187 fnd_file.put_line(fnd_file.log,'P_VAR_ON_SALES : ' || P_VAR_ON_SALES ) ;
188 fnd_file.put_line(fnd_file.log,'P_LOCATION : ' || P_LOCATION ) ;
189 fnd_file.put_line(fnd_file.log,'P_LEGAL_ENTITY_NAME : ' || P_LEGAL_ENTITY_NAME ) ;
190 fnd_file.put_line(fnd_file.log,' *************************** ');
191 END IF;
192
193 BEGIN
194 IF p_report_name = 'JEPTAVAT' THEN --Only for annual report, period year is passed
195 jg_zz_common_pkg.funct_curr_legal(x_func_curr_code => l_func_curr
196 ,x_rep_entity_name => l_legal_entity_name
197 ,x_legal_entity_id => l_legal_entity_id
198 ,x_taxpayer_id => l_tax_payer_id
199 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
200 ,pn_period_year => to_number(p_period));
201
202 p_legal_entity_id := l_legal_entity_id;
203 ELSE
204 jg_zz_common_pkg.funct_curr_legal(x_func_curr_code => l_func_curr
205 ,x_rep_entity_name => l_legal_entity_name
206 ,x_legal_entity_id => l_legal_entity_id
207 ,x_taxpayer_id => l_tax_payer_id
208 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
209 ,pv_period_name => p_period);
210
211 p_legal_entity_id := l_legal_entity_id;
212 END IF ;
213 EXCEPTION
214 WHEN OTHERS THEN
215 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.funct_curr_legal: '||SUBSTR(SQLERRM,1,200));
216 END;
217
218 BEGIN
219 IF p_report_name = 'JEPTAVAT' THEN
220 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
221 ,x_period_start_date => l_period_start_date
222 ,x_period_end_date => l_period_end_date
223 ,x_status => l_reporting_status
224 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
225 ,pv_period_name => NULL
226 ,pn_period_year => to_number(p_period)
227 ,pv_source => 'ALL');
228 ELSE
229 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
230 ,x_period_start_date => l_period_start_date
231 ,x_period_end_date => l_period_end_date
232 ,x_status => l_reporting_status
233 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
234 ,pv_period_name => p_period
235 ,pn_period_year => NULL
236 ,pv_source => 'ALL');
237 END IF;
238 EXCEPTION
239 WHEN OTHERS THEN
240 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.tax_registration: '||SUBSTR(SQLERRM,1,200));
241 END;
242
243 BEGIN
244 if(P_REPORT_NAME <> 'JEPTAVAT' )then
245 l_reporting_status := jg_zz_vat_rep_utility.get_period_status
246 (
247 pn_vat_reporting_entity_id => p_vat_rep_entity_id,
248 pv_tax_calendar_period => p_period,
249 pv_tax_calendar_year => NULL,
250 pv_source => NULL,
251 pv_report_name => P_REPORT_NAME
252 );
253 else
254 l_reporting_status := jg_zz_vat_rep_utility.get_period_status
255 (
256 pn_vat_reporting_entity_id => p_vat_rep_entity_id,
257 pv_tax_calendar_period => NULL,
258 pv_tax_calendar_year => p_period,
259 pv_source => NULL,
260 pv_report_name => P_REPORT_NAME
261 );
262 end if;
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.tax_registration: '||SUBSTR(SQLERRM,1,200));
267 END;
268
269 BEGIN
270 jg_zz_common_pkg.company_detail(x_company_name => l_company_name
271 ,x_registration_number => l_registration_num
272 ,x_country => l_country
273 ,x_address1 => l_address_line_1
274 ,x_address2 => l_address_line_2
275 ,x_address3 => l_address_line_3
276 ,x_address4 => l_address_line_4
277 ,x_city => l_city
278 ,x_postal_code => l_postal_code
279 ,x_contact => l_contact_name
280 ,x_phone_number => l_phone_number
281 ,x_province => l_province
282 ,x_comm_number => l_comm_num
283 ,x_vat_reg_num => l_vat_reg_num
284 ,pn_legal_entity_id => l_legal_entity_id
285 ,p_vat_reporting_entity_id => P_VAT_REP_ENTITY_ID);
286 EXCEPTION
287 WHEN OTHERS THEN
288 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.company_detail: '||SUBSTR(SQLERRM,1,200));
289 END;
290 BEGIN
291 SELECT activity_code
292 INTO l_activity_code
293 FROM xle_entity_profiles
294 WHERE legal_entity_id = l_legal_entity_id;
295 EXCEPTION
296 WHEN NO_DATA_FOUND THEN
297 fnd_file.put_line(fnd_file.log,'Cannot find Activity Code (Standard Inductry Classification Code for Legal Entity:'||l_legal_entity_id);
298 WHEN OTHERS THEN
299 fnd_file.put_line(fnd_file.log,'Error While retriving Activity Code for Legal Entity:'||l_legal_entity_id);
300 fnd_file.put_line(fnd_file.log,'Error Message :'||SUBSTR(SQLERRM,1,200));
301 END;
302
303 BEGIN
304
305 OPEN get_legal_auth_info(p_legal_entity_id);
306 FETCH get_legal_auth_info INTO
307 l_tax_office_location,
308 l_tax_office_code;
309 CLOSE get_legal_auth_info;
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 fnd_file.put_line(fnd_file.log,'Error Message :'||SUBSTR(SQLERRM,1,200));
314 END;
315
316
317 /* Get Currency Precision */
318
319 BEGIN
320 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Code :'||l_func_curr);
321
322 SELECT precision
323 INTO l_precision
324 FROM fnd_currencies
325 WHERE currency_code = l_func_curr;
326
327 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Precision :'||l_precision);
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 FND_FILE.PUT_LINE(FND_FILE.LOG,'error in getting currency precision');
332 END;
333
334
335 INSERT INTO jg_zz_vat_trx_gt
336 (
337 jg_info_n1
338 ,jg_info_v1
339 ,jg_info_v2
340 ,jg_info_v3
341 ,jg_info_v4
342 ,jg_info_v5
343 ,jg_info_v6
344 ,jg_info_v7
345 ,jg_info_v8
346 ,jg_info_v9
347 ,jg_info_v10
348 ,jg_info_v11
349 ,jg_info_v12
350 ,jg_info_v13
351 ,jg_info_v14
352 ,jg_info_v15
353 ,jg_info_v16
354 ,jg_info_v17
355 ,jg_info_d1
356 ,jg_info_d2
357 ,jg_info_v20
358 ,jg_info_v21
359 ,jg_info_n25
360 ,jg_info_v30
361 ,jg_info_v22
362 ,jg_info_v23
363 ,jg_info_v24
364 )
365 VALUES
366 (
367 l_legal_entity_id
368 ,l_company_name ---l_legal_entity_name
369 ,l_tax_registration_num
370 ,l_registration_num --l_tax_payer_id
371 ,l_contact_name
372 ,l_address_line_1
373 ,l_address_line_2
374 ,l_address_line_3
375 ,l_address_line_4
376 ,l_city
377 ,l_country
378 ,l_phone_number
379 ,l_postal_code
380 ,l_func_curr
381 ,l_reporting_status
382 ,l_tax_regime
383 ,l_activity_code
384 ,l_tax_registration_num
385 ,l_period_end_date
386 ,l_period_start_date
387 ,l_tax_office_location
388 ,l_tax_office_code
389 ,l_precision -- currency precision
390 ,'H'
391 ,l_province
392 ,l_comm_num
393 ,l_vat_reg_num
394 );
395 IF p_debug_flag = 'Y' THEN
396 fnd_file.put_line(fnd_file.log,'Legal Entity ID => ' || l_legal_entity_id);
397 fnd_file.put_line(fnd_file.log,'Company Name => ' || l_company_name);
398 fnd_file.put_line(fnd_file.log,'Legal Entity Name => ' || l_company_name);
399 fnd_file.put_line(fnd_file.log,'Regiatration Number => ' || l_registration_num);
400 fnd_file.put_line(fnd_file.log,'Taxpayer ID => ' || l_registration_num);
401 fnd_file.put_line(fnd_file.log,'Contact Name => ' || l_contact_name);
402 fnd_file.put_line(fnd_file.log,'Address Line 1 => ' || l_address_line_1);
403 fnd_file.put_line(fnd_file.log,'Address Line 2 => ' || l_address_line_2);
404 fnd_file.put_line(fnd_file.log,'Address Line 3 => ' || l_address_line_3);
405 fnd_file.put_line(fnd_file.log,'Address Line 4 => ' || l_address_line_4);
406 fnd_file.put_line(fnd_file.log,'City => ' || l_city);
407 fnd_file.put_line(fnd_file.log,'Country => ' || l_country);
408 fnd_file.put_line(fnd_file.log,'Telephone Number => ' || l_phone_number);
409 fnd_file.put_line(fnd_file.log,'Postal Code => ' || l_postal_code);
410 fnd_file.put_line(fnd_file.log,'Currency Code => ' || l_func_curr);
411 fnd_file.put_line(fnd_file.log,'Reporting Status => ' || l_reporting_status);
412 fnd_file.put_line(fnd_file.log,'Period Start Date => ' || l_period_start_date);
413 fnd_file.put_line(fnd_file.log,'Period End Date => ' || l_period_end_date);
414 fnd_file.put_line(fnd_file.log,'l_tax_office_location => ' || l_tax_office_location);
415 fnd_file.put_line(fnd_file.log,'l_tax_office_code => ' || l_tax_office_code);
416 END IF;
417 IF p_report_name = 'JEBEVA06' THEN
418 IF p_debug_flag = 'Y' THEN
419 fnd_file.put_line(fnd_file.log,'Calling Procedure jgbeva06');
420 END IF;
421 jebeva06(p_vat_rep_entity_id => p_vat_rep_entity_id
422 ,p_period => p_period
423 ,p_vat_box => p_vat_box
424 ,p_vat_trx_type_low => p_vat_trx_type_low
425 ,p_vat_trx_type_high => p_vat_trx_type_high
426 ,p_source => p_source
427 ,p_doc_name => p_doc_name
428 ,p_doc_seq_value => p_doc_seq_value
429 ,x_err_msg => l_err_msg);
430 IF p_debug_flag = 'Y' THEN
431 fnd_file.put_line(fnd_file.log,'Completed Call to Procedure jgbava06');
432 END IF;
433 ELSIF p_report_name = 'JEPTAVAT' THEN
434 IF p_debug_flag = 'Y' THEN
435 fnd_file.put_line(fnd_file.log,'Calling Procedure JEPTAVAT');
436 END IF;
437 jeptavat(p_vat_rep_entity_id => p_vat_rep_entity_id
438 ,p_period => p_period
439 ,p_location => p_location
440 ,x_err_msg => l_err_msg);
441 IF p_debug_flag = 'Y' THEN
442 fnd_file.put_line(fnd_file.log,'Completed Call to Procedure JEPTAVAT');
443 END IF;
444 ELSIF p_report_name = 'JEPTPVAT' THEN
445 IF p_debug_flag = 'Y' THEN
446 fnd_file.put_line(fnd_file.log,'Calling Procedure JEPTPVAT');
447 END IF;
448 jeptpvat(p_vat_rep_entity_id => p_vat_rep_entity_id
449 ,p_period => p_period
450 ,p_location => p_location
451 ,x_err_msg => l_err_msg);
452 IF p_debug_flag = 'Y' THEN
453 fnd_file.put_line(fnd_file.log,'Completed Call to Procedure JEPTPVAT');
454 END IF;
455 ELSIF p_report_name = 'JEITPSSR' THEN
456 IF p_debug_flag = 'Y' THEN
457 fnd_file.put_line(fnd_file.log,'Calling Procedure JEITPSSR');
458 END IF;
459 jeitpssr(p_vat_rep_entity_id => p_vat_rep_entity_id
460 ,p_period => p_period
461 ,p_var_on_purchases => p_var_on_purchases
462 ,p_var_on_sales => p_var_on_sales
463 ,x_err_msg => l_err_msg);
464 IF p_debug_flag = 'Y' THEN
465 fnd_file.put_line(fnd_file.log,'Completed Call to Procedure JEITPSSR');
466 END IF;
467 END IF;
468
469 IF p_debug_flag = 'Y' THEN
470 fnd_file.put_line(fnd_file.log,'End of Before Report');
471 END IF;
472
473 RETURN (TRUE);
474 EXCEPTION
475 WHEN NO_DATA_FOUND THEN
476 RETURN (TRUE);
477 WHEN OTHERS THEN
478 fnd_file.put_line(fnd_file.log,'Error in Before Report Trigger' || SQLCODE || SUBSTR(SQLERRM,1,200));
479 raise;
480 RETURN (FALSE);
481 END before_report;
482
483 --
484 -- +======================================================================+
485 -- Name: JEBEVA06
486 --
487 -- Description: This procedure used by the Extract when the Concurrent
488 -- Program 'Belgian VAT Monthly VAT Preparation Report' is run.
489 --
490 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
491 -- P_PERIOD => Tax Calendar Year
492 -- P_VAT_BOX => VAT Report Box
493 -- P_VAT_TRX_TYPE_LOW => VAT Transaction Type Low
494 -- P_VAT_TRX_TYPE_HIGH => VAT Transaction Type Low
495 -- P_SOURCE => Transaction Source
496 -- P_DOC_NAME => Document Sequence Name
497 -- P_DOC_SEQ_VALUE => Document Sequence Value
498 -- +======================================================================+
499 --
500 PROCEDURE jebeva06(p_vat_rep_entity_id IN NUMBER
501 ,p_period IN VARCHAR2
502 ,p_vat_box IN VARCHAR2
503 ,p_vat_trx_type_low IN VARCHAR2
504 ,p_vat_trx_type_high IN VARCHAR2
505 ,p_source IN VARCHAR2
506 ,p_doc_name IN VARCHAR2
507 ,p_doc_seq_value IN VARCHAR2
508 ,x_err_msg OUT NOCOPY VARCHAR2)
509 IS
510
511 /* brathod, modified the cursor to remove usage of jg_zz_alloc_rules table. */
512 CURSOR c_belgian_vat
513 IS
514 SELECT
515 JZVRS.tax_calendar_year PERIOD_YEAR
516 , JZVRS.tax_calendar_period PERIOD_NAME
517 , NVL(AL.tax_box, '99') TAX_BOX
518 , NVL(AL.taxable_box, '99') TAXABLE_BOX
519 , TBLLOOKUP.description TAXABLEBOX_DESCRIPTION
520 , TLOOKUP.description TAXBOX_DESCRIPTION
521 , JG.extract_source_ledger SOURCE
522 , JG.doc_seq_name DOCUMENT_SEQ_NAME
523 , JG.doc_seq_value DOCUMENT_SEQ_VALUE
524 /**************************************************
525 Commented the below code for Bug 7344931 and added GL_DATE
526 , fnd_date.date_to_displaydate(JG.trx_date) GL_DATE
527 **************************************************/
528 , JG.gl_date GL_DATE
529 , JG.billing_tp_number TRADING_PARTNER_NUMBER
530 , JG.billing_tp_name TRADING_PARTNER_NAME
531 , JG.trx_number INVOICE
532 , JG.trx_line_number LINE_NUMBER
533 , JG.tax_rate_Code VAT_CODE
534 , JG.tax_rate_vat_trx_type_code VAT_TRANSACTION_TYPE
535 , JG.taxable_amt * to_number (alr.taxable_non_rec_sign_flag || '1') TAXABLE_AMOUNT
536 , JG.tax_amt * to_number(decode(JG.extract_source_ledger, 'AP',
537 decode(jg.tax_recoverable_flag, 'Y',
538 alr.tax_rec_sign_flag, alr.tax_non_rec_sign_flag) || '1',
539 alr.tax_rec_sign_flag || '1')) TAX_AMOUNT
540 FROM
541 jg_zz_vat_box_allocs AL
542 , jg_zz_vat_alloc_rules alr
543 , jg_zz_vat_trx_details JG
544 , fnd_lookup_values TLOOKUP
545 , fnd_lookup_values TBLLOOKUP
546 , jg_zz_vat_rep_status JZVRS
547 WHERE
548 NVL(AL.taxable_box, '99') = TBLLOOKUP.lookup_code
549 AND NVL(AL.tax_box, '99') = TLOOKUP.lookup_code -- brathod, Using AND condition instead of OR
550 AND TLOOKUP.lookup_type = 'JGZZ_VAT_REPORT_BOXES'
551 AND TBLLOOKUP.lookup_type = 'JGZZ_VAT_REPORT_BOXES'
552 AND TLOOKUP.language = USERENV('LANG') -- Modified for bug 6189243 used col "language" instead of "source_lang"
553 AND TBLLOOKUP.language = USERENV('LANG') -- Modified for bug 6189243 used col "language" instead of "source_lang"
554 AND AL.PERIOD_TYPE = 'PERIODIC'
555 AND jg.tax_rate_vat_trx_type_code BETWEEN NVL( P_VAT_TRX_TYPE_LOW,jg.tax_rate_vat_trx_type_code )
556 AND NVL( P_VAT_TRX_TYPE_HIGH,jg.tax_rate_vat_trx_type_code )
557 AND JZVRS.reporting_status_id = JG.reporting_status_id
558 AND AL.vat_transaction_id = JG.vat_transaction_id
559 AND alr.allocation_rule_id = al.allocation_rule_id
560 AND (P_vat_box IS NULL OR AL.tax_box = P_vat_box OR AL.taxable_box = P_vat_box)
561 AND JG.extract_source_ledger = NVL(P_source,JG.extract_source_ledger)
562 AND (JG.doc_seq_name = P_doc_name OR P_doc_name IS NULL)
563 AND (JG.doc_seq_value = P_doc_seq_value OR P_doc_seq_value IS NULL)
564 AND JZVRS.vat_reporting_entity_id = P_vat_rep_entity_id
565 AND JZVRS.tax_calendar_period = P_PERIOD
566 ORDER BY
567 JG.trx_date
568 , AL.tax_box
569 , source
570 , JG.doc_seq_name
571 , JG.doc_seq_value
572 , DECODE (jg.extract_source_ledger
573 ,'AR', JG.trx_line_number
574 ,'AP', JG.trx_line_number
575 ,'GL', JG.trx_line_number);
576 -- ,'CR', JG.trx_line_id); brathod, commented as 'CR' type of transactions are merged with AR
577
578 l_be_vat c_belgian_vat%ROWTYPE;
579
580 /**
581 Brathod, Modified record defination to use table refered data types instead of normal data types
582 */
583
584 TYPE r_box_amounts IS RECORD
585 (
586 period_year jg_zz_vat_rep_status.tax_calendar_year%type --NUMBER
587 ,period_name jg_zz_vat_rep_status.tax_calendar_period%type --VARCHAR2(10)
588 ,tax_box jg_zz_vat_box_allocs.tax_box%type
589 ,taxable_box jg_zz_vat_box_allocs.taxable_box%type --VARCHAR2(10)
590 ,taxablebox_description fnd_lookup_values.description%type --VARCHAR2(250)
591 ,taxbox_description fnd_lookup_values.description%type --VARCHAR2(250)
592 ,source jg_zz_vat_trx_details.extract_source_ledger%type -- VARCHAR2(10)
593 ,document_seq_name jg_zz_vat_trx_details.doc_seq_name%type -- VARCHAR2(60)
594 ,document_seq_value jg_zz_vat_trx_details.doc_seq_value%type --NUMBER
595 ,gl_date jg_zz_vat_trx_details.trx_date%type
596 ,trading_partner_number jg_zz_vat_trx_details.billing_tp_number%type -- VARCHAR2(240)
597 ,trading_partner_name jg_zz_vat_trx_details.billing_tp_name%type --VARCHAR2(240)
598 ,invoice jg_zz_vat_trx_details.trx_number%type --VARCHAR2(60)
599 ,line_number jg_zz_vat_trx_details.trx_line_number%type --NUMBER
600 ,vat_code jg_zz_vat_trx_details.tax_rate_Code%type --VARCHAR2(100)
601 ,vat_transaction_type jg_zz_vat_trx_details.tax_rate_vat_trx_type_code%type --VARCHAR2(100)
602 ,taxable_amount jg_zz_vat_trx_details.taxable_amt%type --NUMBER
603 ,tax_amount jg_zz_vat_trx_details.tax_amt%type --NUMBER
604 );
605
606 TYPE r_vatbox_amounts IS RECORD
607 (
608 period_year jg_zz_vat_rep_status.tax_calendar_year%type --NUMBER
609 ,period_name jg_zz_vat_rep_status.tax_calendar_period%type --VARCHAR2(10)
610 ,vat_report_box jg_zz_vat_box_allocs.tax_box%type --VARCHAR2(10)
611 ,vat_box_description fnd_lookup_values.description%type --VARCHAR2(250)
612 ,source jg_zz_vat_trx_details.extract_source_ledger%type -- VARCHAR2(10)
613 ,document_seq_name jg_zz_vat_trx_details.doc_seq_name%type -- VARCHAR2(60)
614 ,document_seq_value jg_zz_vat_trx_details.doc_seq_value%type --NUMBER
615 ,gl_date jg_zz_vat_trx_details.accounting_date%type
616 ,trading_partner_number jg_zz_vat_trx_details.billing_tp_number%type -- VARCHAR2(240)
617 ,trading_partner_name jg_zz_vat_trx_details.billing_tp_name%type --VARCHAR2(240)
618 ,invoice jg_zz_vat_trx_details.trx_number%type --VARCHAR2(60)
619 ,line_number jg_zz_vat_trx_details.trx_line_number%type --NUMBER
620 ,vat_code jg_zz_vat_trx_details.tax%type --VARCHAR2(100)
621 ,vat_transaction_type jg_zz_vat_trx_details.tax_rate_vat_trx_type_code%type --VARCHAR2(100)
622 ,taxable_amount jg_zz_vat_trx_details.taxable_amt%type --NUMBER
623 ,tax_amount jg_zz_vat_trx_details.tax_amt%type --NUMBER
624 );
625
626
627 TYPE t_box_amounts IS TABLE OF r_box_amounts
628 INDEX BY BINARY_INTEGER;
629
630 TYPE t_vatbox_amounts IS TABLE OF r_vatbox_amounts
631 INDEX BY BINARY_INTEGER;
632
633 v_box_amount t_box_amounts;
634 l_box_amount_grouped t_vatbox_amounts;
635 l_index NUMBER;
636 l_count NUMBER := 0 ;
637 BEGIN
638 IF p_debug_flag = 'Y' THEN
639 fnd_file.put_line(fnd_file.log,'Executing jg_zz_summary_all_pkg.jgbeva06');
640 fnd_file.put_line(fnd_file.log,'Open Cursor C_BELGIAN_VAT');
641 END IF;
642 OPEN c_belgian_vat;
643 FETCH c_belgian_vat BULK COLLECT INTO v_box_amount;
644 CLOSE c_belgian_vat;
645 l_index := 1;
646 FOR i IN 1..v_box_amount.COUNT
647 LOOP
648 IF v_box_amount(i).tax_box = v_box_amount(i).taxable_box THEN
649 -- The Tax Box is same as taxable box. Therefore no need for changes.
650 l_box_amount_grouped(l_index).period_year := v_box_amount(i).period_year;
651 l_box_amount_grouped(l_index).period_name := v_box_amount(i).period_name;
652 l_box_amount_grouped(l_index).vat_report_box := v_box_amount(i).tax_box;
653 l_box_amount_grouped(l_index).vat_box_description := v_box_amount(i).taxablebox_description;
654 l_box_amount_grouped(l_index).source := v_box_amount(i).source;
655 l_box_amount_grouped(l_index).document_seq_name := v_box_amount(i).document_seq_name;
656 l_box_amount_grouped(l_index).document_seq_value := v_box_amount(i).document_seq_value;
657 l_box_amount_grouped(l_index).gl_date := v_box_amount(i).gl_date;
658 l_box_amount_grouped(l_index).trading_partner_number := v_box_amount(i).trading_partner_number;
659 l_box_amount_grouped(l_index).trading_partner_name := v_box_amount(i).trading_partner_name;
660 l_box_amount_grouped(l_index).invoice := v_box_amount(i).invoice;
661 l_box_amount_grouped(l_index).line_number := v_box_amount(i).line_number;
662 l_box_amount_grouped(l_index).vat_code := v_box_amount(i).vat_code;
663 l_box_amount_grouped(l_index).vat_transaction_type := v_box_amount(i).vat_transaction_type;
664 l_box_amount_grouped(l_index).taxable_amount := v_box_amount(i).taxable_amount;
665 l_box_amount_grouped(l_index).tax_amount := v_box_amount(i).tax_amount;
666 ELSE
667 -- Tax Box and Taxable Box Numbers are different. Therefore create two lines
668 -- Create the Taxable Box Line
669 l_box_amount_grouped(l_index).period_year := v_box_amount(i).period_year;
670 l_box_amount_grouped(l_index).period_name := v_box_amount(i).period_name;
671 l_box_amount_grouped(l_index).vat_report_box := v_box_amount(i).taxable_box;
672 l_box_amount_grouped(l_index).vat_box_description := v_box_amount(i).taxablebox_description;
673 l_box_amount_grouped(l_index).source := v_box_amount(i).source;
674 l_box_amount_grouped(l_index).document_seq_name := v_box_amount(i).document_seq_name;
675 l_box_amount_grouped(l_index).document_seq_value := v_box_amount(i).document_seq_value;
676 l_box_amount_grouped(l_index).gl_date := v_box_amount(i).gl_date;
677 l_box_amount_grouped(l_index).trading_partner_number := v_box_amount(i).trading_partner_number;
678 l_box_amount_grouped(l_index).trading_partner_name := v_box_amount(i).trading_partner_name;
679 l_box_amount_grouped(l_index).invoice := v_box_amount(i).invoice;
680 l_box_amount_grouped(l_index).line_number := v_box_amount(i).line_number;
681 l_box_amount_grouped(l_index).vat_code := v_box_amount(i).vat_code;
682 l_box_amount_grouped(l_index).vat_transaction_type := v_box_amount(i).vat_transaction_type;
683 l_box_amount_grouped(l_index).taxable_amount := v_box_amount(i).taxable_amount;
684 l_box_amount_grouped(l_index).tax_amount := NULL;
685
686 -- Create the Tax Line
687 l_index := l_index + 1;
688 l_box_amount_grouped(l_index).period_year := v_box_amount(i).period_year;
689 l_box_amount_grouped(l_index).period_name := v_box_amount(i).period_name;
690 l_box_amount_grouped(l_index).vat_report_box := v_box_amount(i).tax_box;
691 l_box_amount_grouped(l_index).vat_box_description := v_box_amount(i).taxbox_description;
692 l_box_amount_grouped(l_index).source := v_box_amount(i).source;
693 l_box_amount_grouped(l_index).document_seq_name := v_box_amount(i).document_seq_name;
694 l_box_amount_grouped(l_index).document_seq_value := v_box_amount(i).document_seq_value;
695 l_box_amount_grouped(l_index).gl_date := v_box_amount(i).gl_date;
696 l_box_amount_grouped(l_index).trading_partner_number := v_box_amount(i).trading_partner_number;
697 l_box_amount_grouped(l_index).trading_partner_name := v_box_amount(i).trading_partner_name;
698 l_box_amount_grouped(l_index).invoice := v_box_amount(i).invoice;
699 l_box_amount_grouped(l_index).line_number := v_box_amount(i).line_number;
700 l_box_amount_grouped(l_index).vat_code := v_box_amount(i).vat_code;
701 l_box_amount_grouped(l_index).vat_transaction_type := v_box_amount(i).vat_transaction_type;
702 l_box_amount_grouped(l_index).taxable_amount := NULL;
703 l_box_amount_grouped(l_index).tax_amount := v_box_amount(i).tax_amount;
704 END IF;
705 l_index := l_index + 1;
706 END LOOP;
707
708 FOR i IN 1..l_box_amount_grouped.COUNT
709 LOOP
710 IF p_debug_flag = 'Y' THEN
711 fnd_file.put_line(fnd_file.log,'Period Year => ' || l_box_amount_grouped(i).period_year);
712 fnd_file.put_line(fnd_file.log,'Period Name => ' || l_box_amount_grouped(i).period_name);
713 fnd_file.put_line(fnd_file.log,'VAT Report Box => ' || l_box_amount_grouped(i).vat_report_box);
714 fnd_file.put_line(fnd_file.log,'Box Descriptionc => ' || l_box_amount_grouped(i).vat_box_description);
715 fnd_file.put_line(fnd_file.log,'Source => ' || l_box_amount_grouped(i).source);
716 fnd_file.put_line(fnd_file.log,'Document Name => ' || l_box_amount_grouped(i).document_seq_name);
717 fnd_file.put_line(fnd_file.log,'Documemt Value => ' || l_box_amount_grouped(i).document_seq_value);
718 fnd_file.put_line(fnd_file.log,'GL Date => ' || l_box_amount_grouped(i).gl_date);
719 fnd_file.put_line(fnd_file.log,'Vendor Number => ' || l_box_amount_grouped(i).trading_partner_number);
720 fnd_file.put_line(fnd_file.log,'Vendor_Name => ' || l_box_amount_grouped(i).trading_partner_name);
721 fnd_file.put_line(fnd_file.log,'Invoice Number => ' || l_box_amount_grouped(i).invoice);
722 fnd_file.put_line(fnd_file.log,'Line Number => ' || l_box_amount_grouped(i).line_number);
723 fnd_file.put_line(fnd_file.log,'VAT Code => ' || l_box_amount_grouped(i).vat_code);
724 fnd_file.put_line(fnd_file.log,'VAT Trans Type => ' || l_box_amount_grouped(i).vat_transaction_type);
725 fnd_file.put_line(fnd_file.log,'Taxable Amount => ' || l_box_amount_grouped(i).taxable_amount);
726 fnd_file.put_line(fnd_file.log,'Tax Amount => ' || l_box_amount_grouped(i).tax_amount);
727 END IF;
728 INSERT INTO jg_zz_vat_trx_gt
729 (
730 jg_info_n1
731 , jg_info_v1
732 , jg_info_v2
733 , jg_info_v3
734 , jg_info_v4
735 , jg_info_v5
736 , jg_info_n2
737 , jg_info_d1
738 , jg_info_v6
739 , jg_info_v7
740 , jg_info_v8
741 , jg_info_n3
742 , jg_info_v9
743 , jg_info_v10
744 , jg_info_n4
745 , jg_info_n5
746 )
747 VALUES(
748 l_box_amount_grouped(i).period_year
749 , l_box_amount_grouped(i).period_name
750 , l_box_amount_grouped(i).vat_report_box
751 , l_box_amount_grouped(i).vat_box_description
752 , l_box_amount_grouped(i).source
753 , l_box_amount_grouped(i).document_seq_name
754 , l_box_amount_grouped(i).document_seq_value
755 , l_box_amount_grouped(i).gl_date
756 , l_box_amount_grouped(i).trading_partner_number
757 , l_box_amount_grouped(i).trading_partner_name
758 , l_box_amount_grouped(i).invoice
759 , l_box_amount_grouped(i).line_number
760 , l_box_amount_grouped(i).vat_code
761 , l_box_amount_grouped(i).vat_transaction_type
762 , l_box_amount_grouped(i).taxable_amount
763 , l_box_amount_grouped(i).tax_amount
764 );
765
766 END LOOP;
767
768 SELECT count(*) into l_count from jg_zz_vat_trx_gt ;
769 IF p_debug_flag = 'Y' THEN
770 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_count );
771 fnd_file.put_line(fnd_file.log,'Completed procedure jg_zz_summary_all_pkg.jgbeva06');
772 END IF;
773 EXCEPTION
774 WHEN OTHERS THEN
775 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.jebeva06 ' || SUBSTR(SQLERRM,1,200));
776 RAISE;
777 END jebeva06;
778
779 --
780 -- +======================================================================+
781 -- Name: JEPTAVAT
782 --
783 -- Description: This procedure used by the Extract when the Concurrent
784 -- Program 'Portuguese Annual VAT Report' is run.
785 --
786 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
787 -- P_PERIOD => Tax Calendar Year
788 -- P_LOCATION => Location Code
789 -- +======================================================================+
790 --
791 PROCEDURE jeptavat(p_vat_rep_entity_id IN NUMBER
792 ,p_period IN VARCHAR2
793 ,p_location IN VARCHAR2
794 ,x_err_msg OUT NOCOPY VARCHAR2)
795 IS
796 CURSOR c_pt_annual_vat
797 IS
798 SELECT
799 NVL(
800 DECODE(ALLOCBOX.tax_recoverable_flag,
801 'Y',TO_NUMBER(ALLOCBOX.taxable_box)),1000) TXBL_REC_BOX
802 , NVL(
803 DECODE(NVL(ALLOCBOX.tax_recoverable_flag,'N'),
804 'N',TO_NUMBER(ALLOCBOX.taxable_box)),1000) TXBL_NRC_BOX
805
806 , DECODE(JG.extract_source_ledger
807 ,'AP', DECODE(JG.tax_recoverable_flag,'Y', NVL(JG.taxable_amt_funcl_curr ,0))
808 ,NVL(JG.taxable_amt_funcl_curr ,0)) TXBL_AMOUNT
809
810 , DECODE(JG.extract_source_ledger
811 ,'AP', DECODE(JG.tax_recoverable_flag,'N', NVL(JG.taxable_amt_funcl_curr ,0))
812 ,NVL(JG.taxable_amt_funcl_curr ,0)) TXBL_NRC_AMOUNT
813
814 /* , DECODE(ALLOCBOX.tax_recoverable_flag,
815 'Y',taxable_amt, 0) TXBL_AMOUNT
816
817 , DECODE(ALLOCBOX.tax_recoverable_flag,'Y',0,taxable_amt ) TXBL_NRC_AMOUNT */
818
819 , NVL(TO_NUMBER(ALLOCRUL.TOTAL_BOX),1000) TOTAL_BOX
820
821 , NVL(TO_NUMBER(ALLOCBOX.tax_box),1000) TAX_BOX --Here we have to identify, whether this tax box is recoverable or non-rec box.
822
823 , NVL(DECODE(JG.extract_source_ledger,'GL',
824 DECODE(JG.prl_no,
825 'I',(NVL(JG.taxable_amt_funcl_curr ,0)*(NVL(JG.tax_rate,0)/100))*(NVL(JG.tax_recovery_rate,0)/ 100),
826 'O',((NVL(JG.taxable_amt_funcl_curr,0)*(NVL(JG.tax_rate,0)/100))*(NVL(JG.tax_recovery_rate,0)/ 100)*-1 ))
827 ,DECODE(JG.extract_source_ledger,'AP',NVL(JG.tax_amt_funcl_curr , 0)
828 ,DECODE(JG.extract_source_ledger,'AR',NVL(JG.tax_amt_funcl_curr , 0)*-1)))
829 ,0) TAX_AMOUNT
830
831 , DECODE(JG.application_id,
832 200,'I',
833 222,'0',
834 101,JG.prl_no) TAX_CLASS
835 , JG.tax_rate_code TAX_CODE_RATE
836 , JG.tax_rate TAX_RATE
837 , JG.trx_id TRANSACTION_ID
838 , JG.trx_number TRX_NUMBER
839 , JG.extract_source_ledger SOURCE_LEDGER
840 , JG.tax_recovery_rate TAX_RECV_RATE
841 , ALLOCBOX.tax_recoverable_flag TAX_RECOVERABLE_FLAG
842 , JG.extract_source_ledger EXTRACT_SOURCE_LEDGER
843 , AllOCBOX.allocation_rule_id ALLOCATION_RULE_ID
844 FROM jg_zz_vat_trx_details JG
845 , jg_zz_vat_rep_status JZVRS
846 , jg_zz_vat_box_allocs AllOCBOX
847 , jg_zz_vat_alloc_rules ALLOCRUL
848 WHERE JZVRS.reporting_status_id = JG.reporting_status_id
849 AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
850 AND JZVRS.tax_calendar_year = p_period
851 AND JG.pt_location = p_location
852 AND AllOCBOX.vat_transaction_id = JG.vat_transaction_id
853 AND AllOCBOX.allocation_rule_id = ALLOCRUL.allocation_rule_id
854 AND AllOCBOX.period_type = 'ANNUAL'
855 ORDER BY JG.trx_id,
856 AllOCBOX.allocation_rule_id;
857
858 -- Record Type to hold amounts
859 TYPE r_Box_Amounts IS RECORD(
860 taxable_amount NUMBER,
861 tax_amount NUMBER,
862 box_type VARCHAR2(10),
863 tot_box_num NUMBER,
864 tax_rate NUMBER);
865
866 -- Table Type of record
867 TYPE t_Box_Amounts IS TABLE OF r_Box_Amounts
868 INDEX BY BINARY_INTEGER;
869
870 -- Variable of above table type
871 v_Box_Amounts t_Box_Amounts;
872 l_data_found VARCHAR2(1) := 'N';
873 l_tax_box NUMBER;
874 l_count NUMBER := 0 ;
875 ln_stat_no NUMBER ;
876 BEGIN
877 ln_stat_no := 1;
878 FOR l_pt_avat in c_pt_annual_vat LOOP
879 ln_stat_no := 2;
880 IF v_box_amounts.EXISTS(l_pt_avat.txbl_rec_box) THEN
881 v_box_amounts(l_pt_avat.txbl_rec_box).taxable_amount := NVL(v_box_amounts(l_pt_avat.txbl_rec_box).taxable_amount, 0) + nvl(l_pt_avat.txbl_amount,0);
882 ELSE
883 v_box_amounts(l_pt_avat.txbl_rec_box).taxable_amount := nvl(l_pt_avat.txbl_amount,0);
884 v_box_amounts(l_pt_avat.txbl_rec_box).tot_box_num := l_pt_avat.total_box;
885 v_box_amounts(l_pt_avat.txbl_rec_box).box_type := 'RECTXBL';
886 END IF;
887 ln_stat_no := 3;
888 IF v_box_amounts.EXISTS(l_pt_avat.txbl_nrc_box) THEN
889 v_box_amounts(l_pt_avat.txbl_nrc_box).taxable_amount := NVL(v_box_amounts(l_pt_avat.txbl_nrc_box).taxable_amount, 0) + l_pt_avat.txbl_amount;
890 ELSE
891 v_box_amounts(l_pt_avat.txbl_nrc_box).taxable_amount := nvl(l_pt_avat.txbl_amount,0);
892 v_box_amounts(l_pt_avat.txbl_nrc_box).tot_box_num := l_pt_avat.total_box;
893 v_box_amounts(l_pt_avat.txbl_nrc_box).box_type := 'NRCTXBL';
894 END IF;
895 ln_stat_no := 4;
896 -- SUM together Tax Amounts for this box
897 IF l_pt_avat.tax_class <> 'OFFSET' THEN
898 l_tax_box := l_pt_avat.tax_box * 10000 + nvl(l_pt_avat.tax_rate,0) * 100;
899 IF v_box_amounts.EXISTS(l_tax_box) THEN
900 v_box_amounts(l_tax_box).tax_amount := NVL(v_box_amounts(l_tax_box).tax_amount ,0) + l_pt_avat.tax_amount;
901 ELSE
902 v_box_amounts(l_tax_box).tax_amount := l_pt_avat.tax_amount;
903 v_box_amounts(l_tax_box).tax_rate := nvl(l_pt_avat.tax_rate,0);
904 v_box_amounts(l_tax_box).box_type := 'TAX';
905 END IF;
906 ln_stat_no := 5;
907 END IF;
908 ln_stat_no := 6;
909 -- Set data flag to yes
910 l_data_found := 'Y';
911
912 IF p_debug_flag = 'Y' then
913 fnd_file.put_line(fnd_file.log,'---- Trans Id ------ '||l_pt_avat.transaction_id||'-----');
914 fnd_file.put_line(fnd_file.log,'---- Trans Number--- '||l_pt_avat.trx_number||'-----');
915 fnd_file.put_line(fnd_file.log,'Tax class information: ' || l_pt_avat.tax_class);
916 fnd_file.put_line(fnd_file.log,'Tax Recovery Rate: ' ||l_pt_avat.tax_recv_rate);
917 fnd_file.put_line(fnd_file.log,'Tax Recoverable Flag : '||l_pt_avat.tax_recoverable_flag);
918 fnd_file.put_line(fnd_file.log,'Taxable box: ' ||l_pt_avat.txbl_nrc_box);
919 fnd_file.put_line(fnd_file.log,'Taxable amount: ' ||l_pt_avat.txbl_nrc_amount);
920 fnd_file.put_line(fnd_file.log,'Tax box: ' || l_pt_avat.tax_box);
921 fnd_file.put_line(fnd_file.log,'Tax Amount: '||l_pt_avat.tax_amount);
922 fnd_file.put_line(fnd_file.log,'Total box: '|| l_pt_avat.total_box);
923 fnd_file.put_line(fnd_file.log,'Tax rate: ' || l_pt_avat.tax_rate);
924 fnd_file.put_line(fnd_file.log,'Tax code: ' || l_pt_avat.tax_code_rate);
925 fnd_file.put_line(fnd_file.log,'Extract Source Ledger: ' || l_pt_avat.extract_source_ledger);
926 fnd_file.put_line(fnd_file.log,'Allocation Rule ID: ' || l_pt_avat.allocation_rule_id);
927
928 END IF;
929
930 END LOOP;
931
932 IF l_data_found = 'Y' THEN
933 FOR i in v_box_amounts.FIRST .. v_box_amounts.LAST LOOP
934
935 IF p_debug_flag = 'Y' and v_box_amounts.EXISTS(i) and i <> 1000 THEN
936 fnd_file.put_line(fnd_file.log,'-----------------------------------');
937 IF i > 1000 THEN
938 fnd_file.put_line(fnd_file.log,' Box Num =>'||floor(i/10000));
939 ELSE
940 fnd_file.put_line(fnd_file.log,' Box Num =>'||i);
941 END IF;
942 fnd_file.put_line(fnd_file.log,' Taxable Amount =>'|| v_box_amounts(i).taxable_amount);
943 fnd_file.put_line(fnd_file.log,' Tax Amount =>'|| v_box_amounts(i).tax_amount);
944 fnd_file.put_line(fnd_file.log,' Total Box Number =>'|| v_box_amounts(i).tot_box_num);
945 fnd_file.put_line(fnd_file.log,' Tax Rate =>'|| v_box_amounts(i).tax_rate);
946 fnd_file.put_line(fnd_file.log,'-----------------------------------');
947 END IF;
948
949 IF v_box_amounts.EXISTS(i) and i <= 1000 THEN
950 INSERT INTO jg_zz_vat_trx_gt ( jg_info_n1
951 ,jg_info_n2
952 ,jg_info_n3
953 ,jg_info_n4
954 ,jg_info_v1
955 )
956 VALUES(
957 i,
958 v_box_amounts(i).taxable_amount,
959 v_box_amounts(i).tax_amount,
960 v_box_amounts(i).tot_box_num,
961 v_box_amounts(i).box_type
962 );
963 ELSIF v_box_amounts.EXISTS(i) and i > 1000 THEN --This must be a tax box
964 INSERT INTO jg_zz_vat_trx_gt ( jg_info_n1
965 ,jg_info_n2
966 ,jg_info_n3
967 ,jg_info_n5
968 ,jg_info_v1
969 )
970 VALUES(
971 floor(i/10000) + decode(sign(v_box_amounts(i).tax_rate), -1, 1, 0)
972 ,v_box_amounts(i).taxable_amount
973 ,v_box_amounts(i).tax_amount
974 ,v_box_amounts(i).tax_rate
975 ,v_box_amounts(i).box_type
976 );
977 END IF;
978 END LOOP;
979 END IF;
980
981 select count(*) into l_count from jg_zz_vat_trx_gt ;
982 IF p_debug_flag = 'Y' THEN
983 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table :' || l_count );
984 END IF ;
985 EXCEPTION
986 WHEN OTHERS THEN
987 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.jeptavat: ' || SUBSTR(SQLERRM,1,200));
988 fnd_file.put_line(fnd_file.log,'Error at statement: ' || ln_stat_no);
989 RAISE;
990 END jeptavat;
991
992 --
993 -- +======================================================================+
994 -- Name: JGPTPVAT
995 --
996 -- Description: This procedure used by the Extract when the Concurrent
997 -- Program 'Portuguese Periodic VAT Report' is run.
998 --
999 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
1000 -- P_PERIOD => Tax Calendar Period
1001 -- P_LOCATION => Location Code
1002 -- +======================================================================+
1003 --
1004 PROCEDURE jeptpvat(p_vat_rep_entity_id IN NUMBER
1005 ,p_period IN VARCHAR2
1006 ,p_location IN VARCHAR2
1007 ,x_err_msg OUT NOCOPY VARCHAR2)
1008 IS
1009 l_data_found VARCHAR2(1) := 'N';
1010
1011 -- Record Type to hold amounts
1012 TYPE r_Box_Amounts IS RECORD(
1013 taxable_amount NUMBER,
1014 deduct_tax NUMBER,
1015 clear_tax NUMBER,
1016 box_type VARCHAR2(10));
1017
1018 -- Table Type of record
1019 TYPE t_Box_Amounts IS TABLE OF r_Box_Amounts
1020 INDEX BY BINARY_INTEGER;
1021
1022 -- Variable of above table type
1023 v_Box_Amounts t_Box_Amounts;
1024
1025 -- Cursor for holding amounts from JG table
1026 CURSOR c_pt_periodic_vat
1027 IS
1028 SELECT NVL(TO_NUMBER(al.taxable_box), 1000) TAXABLE_BOX
1029
1030 , NVL(taxable_amt_funcl_curr , 0) TAXABLE_AMOUNT
1031
1032 , NVL(TO_NUMBER(al.tax_box), 1000) TAX_BOX
1033
1034 , NVL(DECODE(DECODE(JG.extract_source_ledger
1035 ,'GL',DECODE(JG.PRL_NO,'I','AP','O','AR')
1036 ,JG.extract_source_ledger)
1037 , 'AP', DECODE(SIGN(tax_amt_funcl_curr )
1038 , 1 , DECODE(trx_line_class,'AP_CREDIT_MEMO',0,
1039 'AP_DEBIT_MEMO',0,tax_amt_funcl_curr )
1040 , DECODE(reporting_code
1041 , 'OFFSET' , 0
1042 , DECODE(trx_line_class
1043 , 'AP_CREDIT_MEMO' , 0
1044 , 'AP_DEBIT_MEMO' , 0
1045 ,tax_amt_funcl_curr )))
1046 , 'AR', DECODE(reporting_code
1047 , 'OFFSET',DECODE(SIGN(tax_amt_funcl_curr ), 1,tax_amt_funcl_curr )
1048 , DECODE(SIGN(tax_amt_funcl_curr ), -1, tax_amt_funcl_curr * -1 , 0))), 0) DEDUCT_TAX
1049
1050 , NVL(DECODE(DECODE(JG.extract_source_ledger
1051 ,'GL',DECODE(JG.PRL_NO,'I','AP','O','AR')
1052 ,JG.extract_source_ledger)
1053 , 'AP', DECODE(SIGN(tax_amt_funcl_curr )
1054 , -1, DECODE(reporting_code
1055 , 'OFFSET', tax_amt_funcl_curr * -1
1056 , DECODE(trx_line_class
1057 , 'AP_CREDIT_MEMO',tax_amt_funcl_curr *-1
1058 , 'AP_DEBIT_MEMO',tax_amt_funcl_curr * -1,0))
1059 , DECODE(trx_line_class
1060 , 'AP_CREDIT_MEMO' ,tax_amt_funcl_curr
1061 , 'AP_DEBIT_MEMO',tax_amt_funcl_curr,0))
1062 , 'AR', DECODE(reporting_code
1063 , 'OFFSET',DECODE(SIGN(tax_amt_funcl_curr ), -1,tax_amt* -1 )
1064 , DECODE(SIGN(tax_amt_funcl_curr ), 1, tax_amt_funcl_curr , 0))), 0) CLEAR_TAX
1065
1066 , JG.trx_line_class TRX_LINE_CLASS
1067 , JG.tax_rate_code TAX_RATE_CODE
1068 , JG.trx_id TRX_ID
1069 , JG.tax_recoverable_flag TAX_RECOVERABLE_FLAG
1070 , JG.trx_number TRX_NUMBER
1071 , DECODE(JG.application_id, 200,'I', 222,'O',
1072 101,JG.prl_no) TAX_CLASS
1073 ,JG.extract_source_ledger EXTRACT_SOURCE_LEDGER
1074 ,JG.reporting_code REPORTING_CODE
1075 FROM jg_zz_vat_trx_details JG
1076 , jg_zz_vat_rep_status JZVRS
1077 , jg_zz_vat_box_allocs AL
1078 WHERE decode(JG.extract_source_ledger,'AP',NVL(JG.tax_recoverable_flag,'N'),'Y') ='Y' -- Bug 5561879
1079 AND JZVRS.reporting_status_id = JG.reporting_status_id
1080 AND AL.vat_transaction_id = JG.vat_transaction_id
1081 AND AL.period_type = 'PERIODIC'
1082 AND JZVRS.tax_calendar_period = p_period
1083 AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1084 AND JG.pt_location = p_location ;
1085
1086 l_count NUMBER := 0 ;
1087
1088 BEGIN
1089 FOR l_pt_periodic_vat IN c_pt_periodic_vat LOOP
1090 -- SUM together Taxable Amounts for this box
1091 IF v_box_amounts.EXISTS(l_pt_periodic_vat.taxable_box) THEN
1092 v_box_amounts(l_pt_periodic_vat.taxable_box).taxable_amount := NVL(v_box_amounts(l_pt_periodic_vat.taxable_box).taxable_amount, 0)
1093 + l_pt_periodic_vat.taxable_amount;
1094 ELSE
1095 v_box_amounts(l_pt_periodic_vat.taxable_box).taxable_amount := l_pt_periodic_vat.taxable_amount;
1096 v_box_amounts(l_pt_periodic_vat.taxable_box).box_type := 'TAXABLE';
1097 END IF;
1098 -- SUM together Tax Amounts for this box
1099 IF v_box_amounts.EXISTS(l_pt_periodic_vat.tax_box) THEN
1100 v_box_amounts(l_pt_periodic_vat.tax_box).deduct_tax := NVL(v_box_amounts(l_pt_periodic_vat.tax_box).deduct_tax ,0) + l_pt_periodic_vat.deduct_tax;
1101 v_box_amounts(l_pt_periodic_vat.tax_box).clear_tax := NVL(v_box_amounts(l_pt_periodic_vat.tax_box).clear_tax, 0) + l_pt_periodic_vat.clear_tax;
1102 ELSE
1103 v_box_amounts(l_pt_periodic_vat.tax_box).deduct_tax := l_pt_periodic_vat.deduct_tax;
1104 v_box_amounts(l_pt_periodic_vat.tax_box).clear_tax := l_pt_periodic_vat.clear_tax;
1105 v_box_amounts(l_pt_periodic_vat.tax_box).box_type := 'TAX';
1106 END IF;
1107 l_data_found := 'Y';
1108
1109 IF p_debug_flag = 'Y' THEN
1110 fnd_file.put_line(fnd_file.log,'-----------------------------------');
1111 fnd_file.put_line(fnd_file.log,'---- Trans Id ' || l_pt_periodic_vat.trx_id || '-----');
1112 fnd_file.put_line(fnd_file.log,'---- Trans Number '||l_pt_periodic_vat.trx_number||'-----');
1113 fnd_file.put_line(fnd_file.log,' Tax class information => ' || l_pt_periodic_vat.tax_class);
1114 fnd_file.put_line(fnd_file.log,' Extract Source Ledger => ' ||l_pt_periodic_vat.extract_source_ledger);
1115 fnd_file.put_line(fnd_file.log,' trx_line_class => ' ||l_pt_periodic_vat.trx_line_class);
1116 fnd_file.put_line(fnd_file.log,' Reporting Code => ' ||l_pt_periodic_vat.reporting_code);
1117 fnd_file.put_line(fnd_file.log,' Trx Taxable Amount =>'|| l_pt_periodic_vat.taxable_amount);
1118 fnd_file.put_line(fnd_file.log,' Trx Deduct Tax Amount=>'|| l_pt_periodic_vat.deduct_tax);
1119 fnd_file.put_line(fnd_file.log,' Trx Clear Tax Amount =>'|| l_pt_periodic_vat.clear_tax);
1120 fnd_file.put_line(fnd_file.log,' Taxable Box => '|| l_pt_periodic_vat.taxable_box);
1121 fnd_file.put_line(fnd_file.log,' Taxable Amt => ' ||v_box_amounts(l_pt_periodic_vat.taxable_box).taxable_amount);
1122 fnd_file.put_line(fnd_file.log,' Tax Box => ' ||l_pt_periodic_vat.tax_box);
1123 fnd_file.put_line(fnd_file.log,' Deduct Amt => ' ||v_box_amounts(l_pt_periodic_vat.tax_box).deduct_tax);
1124 fnd_file.put_line(fnd_file.log,' Clear Amt => ' ||v_box_amounts(l_pt_periodic_vat.tax_box).clear_tax);
1125 fnd_file.put_line(fnd_file.log,' Tax Recoverable Flag => ' || l_pt_periodic_vat.tax_recoverable_flag);
1126 fnd_file.put_line(fnd_file.log,'-----------------------------------');
1127 END IF;
1128 END LOOP;
1129
1130 IF l_data_found = 'Y' THEN
1131 FOR i in v_box_amounts.FIRST .. v_box_amounts.LAST LOOP
1132
1133 IF v_box_amounts.EXISTS(i) THEN
1134 INSERT INTO jg_zz_vat_trx_gt(
1135 jg_info_n1,
1136 jg_info_n2,
1137 jg_info_n3,
1138 jg_info_n4,
1139 jg_info_v1
1140 )
1141 VALUES(
1142 i,
1143 v_box_amounts(i).taxable_amount,
1144 v_box_amounts(i).deduct_tax,
1145 v_box_amounts(i).clear_tax,
1146 v_box_amounts(i).box_type
1147 );
1148 END IF;
1149 END LOOP;
1150 END IF;
1151
1152 SELECT count(*) into l_count from jg_zz_vat_trx_gt ;
1153 IF p_debug_flag = 'Y' THEN
1154 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_count );
1155 END IF ;
1156
1157 EXCEPTION
1158 WHEN OTHERS THEN
1159 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.jeptpvat ' || SUBSTR(SQLERRM,1,200));
1160 RAISE;
1161 END jeptpvat;
1162
1163 --
1164 -- +======================================================================+
1165 -- Name: JEITPSSR
1166 --
1167 -- Description: This procedure used by the Extract when the Concurrent
1168 -- Program 'Italian Payables Summary VAT Report' is run.
1169 --
1170 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
1171 -- P_PERIOD => Tax Calendar Period
1172 -- P_VAR_ON_PURCHASES => Variation on Purchases
1173 -- P_VAR_ON_SALES => Variationt on Sales
1174 -- +======================================================================+
1175 --
1176 PROCEDURE jeitpssr(p_vat_rep_entity_id IN NUMBER
1177 ,p_period IN VARCHAR2
1178 ,p_var_on_purchases IN NUMBER
1179 ,p_var_on_sales IN NUMBER
1180 ,x_err_msg OUT NOCOPY VARCHAR2)
1181 IS
1182 CURSOR c_italian_vat IS
1183 SELECT
1184 tax_rate_code TAX_RATE_CODE
1185 , tax_rate_code_description VAT_CODE_DESC
1186 , trx_id TRX_ID
1187 , DECODE(extract_source_ledger,'AP',taxable_amt*(tax_recovery_rate/100),taxable_amt) TAXABLE_AMT
1188 , tax_amt TAX_AMT
1189 /****************************************************
1190 Re ordered the query to keep it in sync with the Insert query used
1191 below
1192 Bug 7355610 START
1193 ****************************************************
1194 , DECODE(extract_source_ledger,'AP',taxable_amt_funcl_curr*(tax_recovery_rate/100),taxable_amt_funcl_curr) TAXABLE_AMT_FUNCL_CURR
1195 , tax_amt_funcl_curr TAX_AMT_FUNCL_CURR
1196 ***************************************************/
1197 , tax_amt_funcl_curr TAX_AMT_FUNCL_CURR
1198 , DECODE(extract_source_ledger,'AP',taxable_amt_funcl_curr*(tax_recovery_rate/100),taxable_amt_funcl_curr) TAXABLE_AMT_FUNCL_CURR
1199 /**************************************************
1200 Bug 7355610 END
1201 **************************************************/
1202 , doc_seq_id DOC_SEQ_ID
1203 , extract_source_ledger EXTRACT_SOURCE_LEDGER
1204 , trx_line_type TRX_LINE_TYPE
1205 , accounting_date ACCOUNTING_DATE
1206 -- Bug 6238170 Start
1207 --, tax_type_code TAX_TYPE_CODE
1208 , reporting_code REPORTING_CODE
1209 -- Bug 6238170 End
1210 , gl_transfer_flag GL_TRANSFER_FLAG
1211 , tax_rate_register_type_code TAX_RATE_REGISTER_TYPE_CODE
1212 , trx_currency_code TRX_CURRENCY_CODE
1213 , tax_recovery_rate TAX_RECOVERY_RATE
1214 , tax_recoverable_flag TAX_RECOVERABLE_FLAG
1215 , tax_rate_id TAX_RATE_ID
1216 , trx_number TRX_NUMBER
1217 , DECODE(credit_balance_amt,NULL,0,credit_balance_amt) CREDIT_BALANCE_AMT
1218 , assessable_value ASSESSABLE_VALUE
1219 FROM
1220 jg_zz_vat_trx_details JG
1221 , jg_zz_vat_rep_status JZVRS
1222 WHERE JZVRS.reporting_status_id = JG.reporting_status_id
1223 AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1224 AND JZVRS.tax_calendar_period = p_period ;
1225
1226 l_count NUMBER := 0 ;
1227 BEGIN
1228
1229 FOR l_it_vat IN c_italian_vat LOOP
1230 INSERT INTO jg_zz_vat_trx_gt
1231 (
1232 jg_info_v1
1233 ,jg_info_v2
1234 ,jg_info_n1
1235 ,jg_info_n2
1236 ,jg_info_n3
1237 ,jg_info_n4
1238 ,jg_info_n5
1239 ,jg_info_n6
1240 ,jg_info_v3 --extract_source_ledger
1241 ,jg_info_v4 --trx_line_type
1242 ,jg_info_v5
1243 ,jg_info_v6 --reporting_code
1244 -- ,jg_info_d1 --bug5573113
1245 ,jg_info_v7 --tax_rate_register_type_code
1246 ,jg_info_v8
1247 ,jg_info_n7
1248 ,jg_info_v9 --tax_recoverable_flag
1249 ,jg_info_n9
1250 ,jg_info_v10
1251 ,jg_info_n10
1252 ,jg_info_n11
1253 )
1254 VALUES
1255 (l_it_vat.tax_rate_code
1256 ,l_it_vat.vat_code_desc
1257 ,l_it_vat.trx_id
1258 ,l_it_vat.taxable_amt
1259 /******************************************
1260 Reordered the way of inserting into the temp table as it was not
1261 in sync with the Query used to fetch the values in data definition
1262 jgzzsummaryall.xml
1263 Bug 7355610 START
1264 ,l_it_vat.taxable_amt_funcl_curr
1265 ,l_it_vat.tax_amt
1266 ,l_it_vat.tax_amt_funcl_curr
1267 *****************************************/
1268 ,l_it_vat.tax_amt
1269 ,l_it_vat.tax_amt_funcl_curr
1270 ,l_it_vat.taxable_amt_funcl_curr
1271 /***************************************
1272 Bug 7355610 END
1273 ****************************************/
1274 ,l_it_vat.doc_seq_id
1275 ,l_it_vat.extract_source_ledger
1276 ,l_it_vat.trx_line_type
1277 ,l_it_vat.accounting_date
1278 -- Bug 6238170 Start
1279 --,l_it_vat.tax_type_code
1280 ,l_it_vat.reporting_code
1281 -- Bug 6238170 End
1282 --,l_it_vat.gl_transfer_flag --bug5573113
1283 ,l_it_vat.tax_rate_register_type_code
1284 ,l_it_vat.trx_currency_code
1285 ,l_it_vat.tax_recovery_rate
1286 ,l_it_vat.tax_recoverable_flag
1287 ,l_it_vat.tax_rate_id
1288 ,l_it_vat.trx_number
1289 ,l_it_vat.credit_balance_amt
1290 ,l_it_vat.assessable_value
1291 );
1292 END LOOP;
1293
1294 SELECT count(*) into l_count from jg_zz_vat_trx_gt ;
1295 IF p_debug_flag = 'Y' THEN
1296 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_count );
1297 END IF ;
1298
1299 EXCEPTION
1300 WHEN OTHERS THEN
1301 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.jeitpssr ' || SUBSTR(SQLERRM,1,200));
1302 RAISE;
1303 END jeitpssr;
1304
1305 FUNCTION a_real_tax_amount(p_payment_amt IN NUMBER
1306 , p_tax_amount IN NUMBER
1307 , p_cust_trx_id IN NUMBER)
1308 RETURN NUMBER IS
1309 tax_amount NUMBER;
1310 l_total_invoice NUMBER;
1311 BEGIN
1312 IF p_debug_flag = 'Y' THEN
1313 fnd_file.put_line(fnd_file.log,' p_payment_amt =>'||p_payment_amt);
1314 END IF;
1315 IF p_payment_amt = 0 THEN
1316 /****************************************
1317 tax_amount := tax_amount;
1318 Commented the above code added the below for Bug 7355610 Start
1319 ****************************************/
1320 tax_amount := p_tax_amount;
1321 /***************************************
1322 Bug 7355610 changes End
1323 ***************************************/
1324 ELSE
1325 l_total_invoice := a_total_invoices(p_cust_trx_id);
1326 IF p_func_curr = 'ITL' THEN
1327 tax_amount := p_payment_amt*(p_tax_amount/nvl(l_total_invoice, 1))+.49;
1328 ELSE
1329 tax_amount := p_payment_amt*(p_tax_amount/nvl(l_total_invoice, 1));
1330 END IF;
1331 END IF;
1332 IF p_debug_flag = 'Y' THEN
1333 fnd_file.put_line(fnd_file.log,' tax_amount =>'||tax_amount);
1334 END IF;
1335 RETURN(tax_amount);
1336 EXCEPTION
1337 WHEN OTHERS THEN
1338 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.a_real_tax_amount ' || SUBSTR(SQLERRM,1,200));
1339 RAISE;
1340 END a_real_tax_amount;
1341
1342 FUNCTION a_real_invoice_amount(p_payment_amt IN NUMBER
1343 , p_invoice_amount IN NUMBER
1344 , p_cust_trx_id IN NUMBER)
1345 RETURN NUMBER IS
1346 invoice_amount NUMBER;
1347 l_total_invoice NUMBER;
1348 BEGIN
1349
1350 IF p_payment_amt = 0 THEN
1351 invoice_amount := p_invoice_amount;
1352 ELSE
1353 l_total_invoice := a_total_invoices(p_cust_trx_id);
1354 IF p_func_curr = 'ITL' THEN
1355 invoice_amount := p_payment_amt*(p_invoice_amount/NVL(l_total_invoice, 1))+.49;
1356 ELSE
1357 invoice_amount := p_payment_amt*(p_invoice_amount/NVL(l_total_invoice, 1));
1358 END IF;
1359 END IF;
1360 RETURN(invoice_amount);
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.a_real_invoice_amount ' || SUBSTR(SQLERRM,1,200));
1364 RAISE;
1365 END a_real_invoice_amount;
1366
1367 FUNCTION a_total_invoices(p_cust_trx_id IN NUMBER)
1368 RETURN NUMBER IS
1369 invoice_values NUMBER;
1370 BEGIN
1371 IF p_func_curr = 'ITL' THEN
1372 SELECT SUM(tax_amt_funcl_curr)
1373 INTO invoice_values
1374 FROM jg_zz_vat_trx_details
1375 WHERE trx_id = p_cust_trx_id
1376 AND application_id = 200
1377 AND event_class_code = 'PURCHASE_TRANSACTION'
1378 AND entity_code = 'AP_INVOICES';
1379 ELSE
1380 SELECT SUM(tax_amt)
1381 INTO invoice_values
1382 FROM jg_zz_vat_trx_details
1383 WHERE trx_id = p_cust_trx_id
1384 AND application_id = 200
1385 AND event_class_code = 'PURCHASE_TRANSACTION'
1386 AND entity_code = 'AP_INVOICES';
1387 END IF;
1388 RETURN(invoice_values);
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.a_total_invoices ' || SUBSTR(SQLERRM,1,200));
1392 RAISE;
1393 END a_total_invoices;
1394
1395 FUNCTION get_report_format
1396 RETURN VARCHAR2 IS
1397 l_detail_summary VARCHAR2(30);
1398 BEGIN
1399 IF p_report_name = 'JEBEVA06' THEN
1400 SELECT meaning
1401 INTO l_detail_summary
1402 FROM fnd_lookups
1403 WHERE lookup_type = 'JEBE_REPORT_FORMAT'
1404 AND lookup_code = P_REPORT_FORMAT
1405 AND p_report_name = 'JEBEVA06';
1406 RETURN l_detail_summary;
1407 ELSE
1408 RETURN NULL;
1409 END IF;
1410 EXCEPTION
1411 WHEN NO_DATA_FOUND then
1412 fnd_file.put_line(fnd_file.log,'Error while fetching Meaning for Lookup Type JEBE_REPORT_FORMAT' || SUBSTR(SQLERRM,1,200));
1413 l_detail_summary := P_REPORT_FORMAT;
1414 RETURN l_detail_summary;
1415 WHEN OTHERS then
1416 fnd_file.put_line(fnd_file.log,'Error while fetching Meaning for Lookup Type JEBE_REPORT_FORMAT' || SUBSTR(SQLERRM,1,200));
1417 l_detail_summary := P_REPORT_FORMAT;
1418 RETURN l_detail_summary;
1419 END get_report_format;
1420
1421 FUNCTION get_location_name
1422 RETURN VARCHAR2 IS
1423 l_location_name VARCHAR2(100);
1424 BEGIN
1425 IF p_report_name = 'JEPTAVAT' OR p_report_name = 'JEPTPVAT' THEN
1426 SELECT FFV.description
1427 INTO l_location_name
1428 FROM fnd_flex_values_vl FFV
1429 , fnd_flex_value_sets FFVS
1430 WHERE FFV.flex_value_set_id = FFVS.flex_value_set_id
1431 AND FFVS.flex_value_set_name = 'JEPT_TAX_LOCATION'
1432 AND FFV.flex_value = P_LOCATION;
1433 ELSE
1434 l_location_name := NULL;
1435 END IF;
1436 RETURN l_location_name;
1437 EXCEPTION
1438 WHEN NO_DATA_FOUND THEN
1439 fnd_file.put_line(fnd_file.log,'Location Name for Location Code ' || P_LOCATION || ' is not found');
1440 l_location_name := P_LOCATION;
1441 RETURN l_location_name;
1442 WHEN OTHERS THEN
1443 fnd_file.put_line(fnd_file.log,'Error while fetching Location Name' || SUBSTR(SQLERRM,1,200));
1444 l_location_name := P_LOCATION;
1445 RETURN l_location_name;
1446 END;
1447
1448 FUNCTION get_start_exempt_limit
1449 RETURN NUMBER
1450 IS
1451 l_starting_limit NUMBER;
1452 BEGIN
1453 l_starting_limit := 0;
1454 SELECT NVL(jiy.adjusted_limit_amount, 0)
1455 INTO l_starting_limit
1456 FROM je_it_year_ex_limit JIY,
1457 fnd_lookups fl
1458 WHERE fl.lookup_type = 'JEIT_MONTH'
1459 AND JIY.month = fl.lookup_code
1460 AND JIY.year = to_char(P_PERIOD,'RRRR')
1461 AND JIY.month = to_char(P_PERIOD,'MM')
1462 AND JIY.legal_entity_id = P_LEGAL_ENTITY_ID ;
1463 fnd_file.put_line(fnd_file.log,'Starting Exemption Limit for Period => : '||l_starting_limit);
1464 RETURN l_starting_limit;
1465 EXCEPTION
1466 WHEN NO_DATA_FOUND THEN
1467 fnd_file.put_line(fnd_file.log,'Exeption Amount for beginning of Period not found' || SUBSTR(SQLERRM,1,200));
1468 RETURN 0;
1469 WHEN OTHERS THEN
1470 fnd_file.put_line(fnd_file.log,'Error while fetching Available Exemption at Beginning of Period ' || SUBSTR(SQLERRM,1,200));
1471 RETURN 0;
1472 END get_start_exempt_limit;
1473
1474 FUNCTION get_adjustment_exempt_limit
1475 RETURN NUMBER
1476 IS
1477 l_adjustment_limit NUMBER;
1478 BEGIN
1479 l_adjustment_limit := 0;
1480 SELECT NVL(JIY.adjusted_limit_amount, 0)
1481 INTO l_adjustment_limit
1482 FROM je_it_year_ex_limit JIY
1483 WHERE JIY.year = to_char(P_PERIOD,'RRRR')
1484 AND JIY.month = to_char(P_PERIOD,'MM')
1485 AND JIY.legal_entity_id = P_LEGAL_ENTITY_ID ;
1486 fnd_file.put_line(fnd_file.log,'Adjustment Amount =>: '||l_adjustment_limit);
1487 RETURN l_adjustment_limit;
1488 EXCEPTION
1489 WHEN NO_DATA_FOUND THEN
1490 fnd_file.put_line(fnd_file.log,'Adjustments Yearly Exemption Limit not found' || SUBSTR(SQLERRM,1,200));
1491 RETURN 0;
1492 WHEN OTHERS THEN
1493 fnd_file.put_line(fnd_file.log,'Error while getting Adjustment to Yearly Exemption Limit ' || SUBSTR(SQLERRM,1,200));
1494 RETURN 0;
1495 END get_adjustment_exempt_limit;
1496
1497 FUNCTION get_old_debit_vat
1498 RETURN NUMBER
1499 IS
1500 BEGIN
1501 -- This Code will return the Previous Period VAT Credit Amount
1502 -- This is used by the XML Element OLD_DEBIT_VAT
1503 RETURN 0;
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 fnd_file.put_line(fnd_file.log,'Error while getting Previous Period VAT Credit ' || SUBSTR(SQLERRM,1,200));
1507 RETURN 0;
1508 END get_old_debit_vat;
1509
1510 FUNCTION JEITPSSR_AMOUNT_TO_PAY(A_TOT_TOT_TAX_AMT_SUM IN NUMBER,
1511 B_TOT_TAX_AMT_SUM IN NUMBER,
1512 D_TOT_TAX_AMT_SUM IN NUMBER,
1513 D_VAT_REC_SUM IN NUMBER,
1514 C_VAT_REC_SUM IN NUMBER,
1515 C_VAT_NON_RECC_SUM IN NUMBER)
1516 RETURN NUMBER IS
1517
1518 AMOUNT_TO_PAY NUMBER;
1519 CARRY_OVER NUMBER;
1520 FINAL_FLAG VARCHAR2(1);
1521 LAST_PROCESS_DATE DATE;
1522 INVALID_CARRY_OVER EXCEPTION;
1523 l_period_start_date DATE;
1524 LOG_MESSAGE VARCHAR2(240);
1525
1526 /* Bug:8237932 This cursor is no longer needed to get the
1527 credit_balance_amount,flag_status and last_processed_date.
1528 CURSOR c_get_details IS
1529 SELECT
1530 JZVRS.CREDIT_BALANCE_AMT,
1531 JZVRS.FINAL_REPORTING_STATUS_FLAG,
1532 jg_zz_vat_rep_utility.get_last_processed_date(p_vat_rep_entity_id,'ALL','FINAL REPORTING') LAST_PROCESS_DATE
1533 FROM jg_zz_vat_rep_status JZVRS
1534 WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1535 AND JZVRS.tax_calendar_period = p_period
1536 AND ROWNUM = 1;
1537 Bug:8237932*/
1538
1539 CURSOR c_get_period_start_date IS
1540 SELECT jg_info_d2 FROM jg_zz_vat_trx_gt WHERE jg_info_v30 = 'H';
1541
1542 /*Added three new cursors:
1543 c_get_last_process_date ,c_get_balance,c_get_flag*/
1544 --Bug:8237932
1545 CURSOR c_get_last_process_date IS
1546 SELECT
1547 max(JZVRS.period_end_date)
1548
1549 FROM
1550 jg_zz_vat_rep_status JZVRS
1551 WHERE
1552 JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1553 AND JZVRS.period_end_date <
1554 (SELECT max(JZVRS1.period_end_date)
1555 FROM jg_zz_vat_rep_status JZVRS1
1556 WHERE JZVRS1.tax_calendar_period = p_period
1557 AND JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
1558 AND JZVRS1.final_reporting_status_flag = 'S'
1559 )
1560 AND (JZVRS.final_reporting_status_flag = 'S')
1561 ;
1562
1563 CURSOR c_get_balance ( pn_last_process_date DATE) IS
1564 SELECT
1565 JZVRS.CREDIT_BALANCE_AMT
1566 FROM jg_zz_vat_rep_status JZVRS
1567 WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1568 AND JZVRS.tax_calendar_period =( SELECT JZVRS1.tax_calendar_period
1569 FROM jg_zz_vat_rep_status JZVRS1
1570 WHERE
1571 JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
1572 AND TRUNC(JZVRS1.period_end_date) = pn_last_process_date
1573 AND ROWNUM =1)
1574 AND ROWNUM = 1;
1575
1576 CURSOR c_get_flag IS
1577 SELECT
1578 JZVRS.FINAL_REPORTING_STATUS_FLAG
1579 FROM jg_zz_vat_rep_status JZVRS
1580 WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1581 AND JZVRS.tax_calendar_period = p_period
1582 AND ROWNUM = 1;
1583
1584 --Bug:8237932
1585 BEGIN
1586
1587 /*Bug:8237932
1588 OPEN c_get_details;
1589 FETCH c_get_details INTO CARRY_OVER,FINAL_FLAG,LAST_PROCESS_DATE;
1590 CLOSE c_get_details;
1591 */
1592 LAST_PROCESS_DATE := NULL;
1593 --OPEN CURSOR
1594 OPEN c_get_last_process_date;
1595 FETCH c_get_last_process_date INTO LAST_PROCESS_DATE;
1596 CLOSE c_get_last_process_date;
1597
1598 OPEN c_get_balance(LAST_PROCESS_DATE);
1599 FETCH c_get_balance INTO CARRY_OVER;
1600 CLOSE c_get_balance;
1601
1602 OPEN c_get_flag;
1603 FETCH c_get_flag INTO FINAL_FLAG;
1604 CLOSE c_get_flag;
1605 --Bug:8237932
1606
1607 OPEN c_get_period_start_date;
1608 FETCH c_get_period_start_date INTO l_period_start_date;
1609 CLOSE c_get_period_start_date;
1610
1611
1612 IF FINAL_FLAG = 'S' THEN
1613 IF CARRY_OVER IS NULL THEN
1614 IF LAST_PROCESS_DATE IS NULL THEN
1615 CARRY_OVER := 0;
1616 ELSE
1617 AMOUNT_TO_PAY := 0;
1618
1619 fnd_message.set_name('JE', 'JE_IT_VAT_AP_SUM_CR_BALANCE');
1620 fnd_message.set_token('DATE', LAST_PROCESS_DATE);
1621 LOG_MESSAGE := fnd_message.get;
1622
1623 RAISE INVALID_CARRY_OVER;
1624 END IF;
1625 ELSIF (LAST_PROCESS_DATE+1) <> l_period_start_date THEN
1626
1627 fnd_message.set_name('JE', 'JE_IT_VAT_GAP_IN_FINAL_PROCESS');
1628 fnd_message.set_token('DATE', LAST_PROCESS_DATE);
1629 LOG_MESSAGE := fnd_message.get;
1630
1631 RAISE INVALID_CARRY_OVER;
1632 END IF;
1633 END IF;
1634
1635 AMOUNT_TO_PAY := (A_TOT_TOT_TAX_AMT_SUM + B_TOT_TAX_AMT_SUM ) - D_TOT_TAX_AMT_SUM + D_VAT_REC_SUM
1636 + P_VAR_ON_SALES - C_VAT_REC_SUM - P_VAR_ON_PURCHASES - NVL(CARRY_OVER,0);
1637
1638
1639 /*Commented this part of code for bug 7633948.This functionaliy is implemented in
1640 JEITPSSR_AMOUNT_TO_PAY_UPDATE function
1641 IF FINAL_FLAG = 'S' THEN
1642
1643 IF AMOUNT_TO_PAY >= 0 THEN
1644 CARRY_OVER := 0;
1645 ELSE
1646 CARRY_OVER := AMOUNT_TO_PAY;
1647 END IF;
1648
1649 IF TEMP_FLAG = 0 THEN
1650
1651 UPDATE jg_zz_vat_rep_status SET CREDIT_BALANCE_AMT=CARRY_OVER
1652 WHERE vat_reporting_entity_id = p_vat_rep_entity_id
1653 AND tax_calendar_period = p_period;
1654
1655 TEMP_FLAG := 1;
1656
1657 END IF;
1658
1659 END IF;for bug 7633948*/
1660
1661 RETURN(AMOUNT_TO_PAY);
1662
1663 EXCEPTION
1664 WHEN INVALID_CARRY_OVER THEN
1665 fnd_file.put_line(fnd_file.log,LOG_MESSAGE);
1666 raise_application_error(-20010,LOG_MESSAGE);
1667 WHEN OTHERS THEN
1668 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY ' || SUBSTR(SQLERRM,1,200));
1669 raise_application_error(-20011,'Error in Procedure jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY ' || SUBSTR(SQLERRM,1,200));
1670 END JEITPSSR_AMOUNT_TO_PAY;
1671
1672 /****Added below function JEITPSSR_AMOUNT_TO_PAY_UPDATE for bug 7633948***/
1673 FUNCTION JEITPSSR_AMOUNT_TO_PAY_UPDATE(AMOUNT_TO_PAY IN NUMBER)
1674 RETURN BOOLEAN IS
1675
1676 FINAL_FLAG VARCHAR2(1);
1677 CARRY_OVER NUMBER;
1678 --INVALID_CARRY_OVER EXCEPTION;
1679 --LOG_MESSAGE VARCHAR2(240);
1680 CURSOR c_get_details IS
1681 SELECT
1682 JZVRS.FINAL_REPORTING_STATUS_FLAG
1683 FROM jg_zz_vat_rep_status JZVRS
1684 WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1685 AND JZVRS.tax_calendar_period = p_period
1686 AND ROWNUM = 1;
1687 BEGIN
1688
1689 OPEN c_get_details;
1690 FETCH c_get_details INTO FINAL_FLAG;
1691 CLOSE c_get_details;
1692
1693 IF FINAL_FLAG='S' THEN
1694
1695 IF AMOUNT_TO_PAY >= 0 THEN
1696 CARRY_OVER := 0;
1697 ELSE
1698 CARRY_OVER := AMOUNT_TO_PAY;
1699 END IF;
1700
1701 IF TEMP_FLAG = 0 THEN
1702
1703 UPDATE jg_zz_vat_rep_status SET CREDIT_BALANCE_AMT=CARRY_OVER
1704 WHERE vat_reporting_entity_id = p_vat_rep_entity_id
1705 AND tax_calendar_period = p_period;
1706
1707 TEMP_FLAG := 1;
1708 END IF;
1709 END IF;
1710 RETURN (TRUE);
1711 EXCEPTION
1712 -- WHEN INVALID_CARRY_OVER THEN
1713 -- fnd_file.put_line(fnd_file.log,LOG_MESSAGE);
1714 WHEN OTHERS THEN
1715 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY_UPDATE ' || SUBSTR(SQLERRM,1,200));
1716 RETURN (FALSE);
1717 END JEITPSSR_AMOUNT_TO_PAY_UPDATE;
1718
1719 /******Added the below function JEILR835 INV_NUM for Bug 7427956*********/
1720 FUNCTION JEILR835_INV_NUM ( p_str VARCHAR2) RETURN VARCHAR2 IS
1721 l_str VARCHAR2(100);
1722 l_char VARCHAR2(2);
1723 l_len NUMBER;
1724 l_start NUMBER :=1;
1725 BEGIN
1726 l_len:=NVL(LENGTH(p_str), 0);
1727 l_str :='';
1728 FOR l_start IN 1..l_len LOOP
1729 l_char:= SUBSTRB(p_str,l_start,1);
1730 IF (l_char IN ('1','2','3','4','5','6','7','8','9','0')) THEN
1731 l_str:=l_str||l_char;
1732 END IF;
1733 END LOOP;
1734 l_len:=NVL(LENGTH(p_str), 0);
1735 IF (l_len > 6) THEN
1736 l_start:=l_len-5;
1737 l_str:=substrb(l_str,l_start,6);
1738 END IF;
1739 RETURN (l_str);
1740 END JEILR835_INV_NUM;
1741
1742 END JG_ZZ_SUMMARY_ALL_PKG;