[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_SUMMARY_ALL_PKG
Source
1 PACKAGE BODY JG_ZZ_SUMMARY_ALL_PKG
2 -- $Header: jgzzsummaryallb.pls 120.70.12020000.5 2012/11/23 05:48:55 mkandula 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 -- 120.41 12-FEB-2009 RAHULKUM BUG:8237932 Modified the JEITPSSR_AMOUNT_TO_PAY_UPDATE.
123 -- 120.42 07-APR-2009 RAHULKUM BUG:8347134 Modified the JEITPSSR.Added two new cursors c_get_last_process_date ,c_get_balance.Update the jg_zz_vat_trx_gt
124 -- 120.43 12-JUN-2009 RAHULKUM BUG:8587526 Modified the JEITPSSR.
125 -- 120.31.12010000.15 23-Jun-2009 SPASUPUN Bug 8501251 : Modified procedure jebeva06
126 -- to exclude multiple occurance of taxable informaiton. This issue was
127 -- there for AP transactions due to REC and Non-Rec lines concept.
128 -- 120.26.12000000.19 10-Aug-2009 VKEJRIWA Bug 8779393 : Modified the function jeilr835_inv_nu
129 -- to replace p_str by l_str when calculating l_len for the
130 -- second time so that the last 6 characters can be taken
131 -- 120.48 28-OCT-2009 PAKUMARE BUG:8983828 Modified the cursor c_belgian_vat.
132 -- 120.31.12010000.19 03-Jan-2009 PAKUMARE Bug:9241039: Changes made for IL VAT 2010 ER.
133 -- 120.31.12010000.20 05-Jan-2009 RAHULKUM Bug:9186339: Modified the Procedure JEITPSSR and
134 -- function JEITPSSR_AMOUNT_TO_PAY.
135 -- 120.31.12010000.21 13-JAN-2010 MKANDULA BUG:9275163: Modified the formula to calculate Amount_to_pay in
136 -- JEITPSSR_AMOUNT_TO_PAY function.
137 -- 120.31.12010000.22 05-FEB-2010 PAKUMARE BUG:9338006 Increased the size to 9 in JEILR835_INV_NUM fn.
138 -- 120.31.12010000.23 23-Jun-2010 RSAINI BUG:9839672
139 -- 25-Jun-2010 ABUISSA ER 9155483 (Israeli Ichud Oskim)
140 -- 13-Jul-2010 ABUISSA Bug 9880383: Rep Entity check
141 -- 120.31.12010000.27 06-AUG-2010 RSHERGIL Bug 99710101: Belgian VAT
142 -- 120.31.12010000.30 03-MAR-2010 SAKEKUMA Bug 11813288: Changed the query "c_belgian_vat" in the procedure "jebeva06".In the query added --TAXABLE_ITEM_SOURCE_ID column to pick up all invoice splited lines taxable amount.
143 -- 120.31.12010000.31 21-APR-2011 RAHULKUM Bug:12375800:Modified Procedure JEITPSSR
144 -- 120.31.12010000.32 16-MAY-2011 SAKEKUMA Bug:12359827:Added sql optimizer for the session to fix 11.1.0.7 database summation issue in joins.
145 -- 120.31.12010000.35 05-AUG-2011 RAHULKUM Bug:12359827:Modified Procedure jebeva06.
146 -- 120.31.12010000.36 18-AUG-2010 SAKEKUMA Bug:12691340: Modified Procedure jeitpssr.
147 -- 120.31.12010000.39 21-SEP-2011 SPASUPUN Bug:12359827:Modified Procedure jebeva06 to consider the taxable amount only
148 -- for non-recoverable entries in case of AP transactions.
149 -- 120.31.12010000.44 23-NOV-2011 MKANDULA Bug:15862976: Modified c_italian_vat cursor.
150 -- +===========================================================================+
151 AS
152 FUNCTION before_report RETURN BOOLEAN
153 IS
154 CURSOR get_legal_auth_info(p_le_id number)
155 IS
156 SELECT xle_auth.city tax_office_location
157 ,xle_auth.address3 tax_office_code
158 FROM XLE_FIRSTPARTY_INFORMATION_V xfpiv
159 , xle_registrations xle_reg
160 , xle_legalauth_v xle_auth
161 WHERE xle_reg.source_id = xfpiv.legal_entity_id
162 AND xle_reg.source_table = 'XLE_ENTITY_PROFILES'
163 AND xle_auth.legalauth_id (+) = xle_reg.issuing_authority_id
164 AND xle_reg.identifying_flag = 'Y'
165 AND xfpiv.legislative_cat_code = 'INCOME_TAX'
166 AND xfpiv.legal_entity_id = p_le_id;
167
168 -- Israel VAT Reporting 2010 - ER
169 CURSOR il_get_rep_status_id
170 IS
171 SELECT
172 JZVRS.REPORTING_STATUS_ID
173 FROM
174 JG_ZZ_VAT_REP_STATUS JZVRS
175 WHERE
176 JZVRS.SOURCE='AP'
177 AND JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
178 AND JZVRS.TAX_CALENDAR_PERIOD = P_PERIOD;
179
180 CURSOR il_get_vat_aggregate_limit
181 IS
182 SELECT
183 JLMT.VAT_AGGREGATE_LIMIT_AMT
184 FROM
185 JE_IL_VAT_LIMITS JLMT,
186 JG_ZZ_VAT_REP_STATUS JZVRS
187 WHERE
188 JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
189 AND JZVRS.TAX_CALENDAR_PERIOD = P_PERIOD
190 AND JZVRS.TAX_CALENDAR_NAME = JLMT.PERIOD_SET_NAME
191 AND JLMT.PERIOD_NAME = P_PERIOD
192 AND ROWNUM = 1;
193
194 l_address_line_1 VARCHAR2 (240);
195 l_address_line_2 VARCHAR2 (240);
196 l_address_line_3 VARCHAR2 (240);
197 l_address_line_4 VARCHAR2 (240);
198 l_city VARCHAR2 (60);
199 l_company_name VARCHAR2 (240);
200 l_contact_name VARCHAR2 (360);
201 l_country VARCHAR2 (60);
202 l_func_curr VARCHAR2 (30);
203 l_legal_entity_id NUMBER;
204 l_legal_entity_name VARCHAR2 (240);
205 l_period_end_date DATE;
206 l_period_start_date DATE;
207 l_phone_number VARCHAR2 (40);
208 l_postal_code VARCHAR2 (60);
209 l_registration_num VARCHAR2 (30);
210 l_reporting_status VARCHAR2 (60);
211 l_tax_payer_id VARCHAR2 (60);
212 l_tax_registration_num VARCHAR2 (240);
213 l_tax_regime VARCHAR2(240);
214 l_activity_code VARCHAR2(240);
215 l_vat_register_name VARCHAR2(500);
216 l_tax_office_location xle_legalauth_v.city%TYPE;
217 l_tax_office_code xle_legalauth_v.address3%TYPE;
218 l_precision NUMBER;
219 -- Added for Glob-006 ER
220 l_province VARCHAR2(120);
221 l_comm_num VARCHAR2(30);
222 l_vat_reg_num VARCHAR2(50);
223 l_entity_type_code VARCHAR2(30);
224 l_ledger_category_code VARCHAR2(30);
225 l_ent_name_main VARCHAR2(80);
226 LOG_MESSAGE_MAIN VARCHAR2(1000);
227
228 BEGIN
229 EXECUTE IMMEDIATE 'alter session set optimizer_features_enable=''10.1.0.5'''; ----Bug 12359827
230 IF p_debug_flag = 'Y' THEN
231 fnd_file.put_line(fnd_file.log,' *** Report Parameters *** ');
232 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID : ' || P_VAT_REP_ENTITY_ID ) ;
233 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID2 : ' || P_VAT_REP_ENTITY_ID2 ) ;
234 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID3 : ' || P_VAT_REP_ENTITY_ID3 ) ;
235 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID4 : ' || P_VAT_REP_ENTITY_ID4 ) ;
236 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID6 : ' || P_VAT_REP_ENTITY_ID5 ) ;
237 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID6 : ' || P_VAT_REP_ENTITY_ID6 ) ;
238 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID7 : ' || P_VAT_REP_ENTITY_ID7 ) ;
239 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID8 : ' || P_VAT_REP_ENTITY_ID8 ) ;
240 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID9 : ' || P_VAT_REP_ENTITY_ID9 ) ;
241 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID10 : ' || P_VAT_REP_ENTITY_ID10 ) ;
242 fnd_file.put_line(fnd_file.log,'P_ICHUD_OSKIM_NUM : ' || P_ICHUD_OSKIM_NUM ) ;
243 fnd_file.put_line(fnd_file.log,'P_PERIOD : ' || P_PERIOD ) ;
244 fnd_file.put_line(fnd_file.log,'P_VAT_BOX : ' || P_VAT_BOX ) ;
245 fnd_file.put_line(fnd_file.log,'P_VAT_TRX_TYPE_LOW : ' || P_VAT_TRX_TYPE_LOW ) ;
246 fnd_file.put_line(fnd_file.log,'P_VAT_TRX_TYPE_HIGH : ' || P_VAT_TRX_TYPE_HIGH ) ;
247 fnd_file.put_line(fnd_file.log,'P_VAT_TRX_TYPE : ' || P_VAT_TRX_TYPE ) ;
248 fnd_file.put_line(fnd_file.log,'P_REPORT_FORMAT : ' || P_REPORT_FORMAT ) ;
249 fnd_file.put_line(fnd_file.log,'P_BOX_FROM : ' || P_BOX_FROM ) ;
250 fnd_file.put_line(fnd_file.log,'P_BOX_TO : ' || P_BOX_TO ) ;
251 fnd_file.put_line(fnd_file.log,'P_SOURCE : ' || P_SOURCE ) ;
252 fnd_file.put_line(fnd_file.log,'P_DOC_NAME : ' || P_DOC_NAME ) ;
253 fnd_file.put_line(fnd_file.log,'P_DOC_SEQ_VALUE : ' || P_DOC_SEQ_VALUE ) ;
254 fnd_file.put_line(fnd_file.log,'P_REPORT_NAME : ' || P_REPORT_NAME ) ;
255 fnd_file.put_line(fnd_file.log,'P_VAR_ON_PURCHASES : ' || P_VAR_ON_PURCHASES ) ;
256 fnd_file.put_line(fnd_file.log,'P_VAR_ON_SALES : ' || P_VAR_ON_SALES ) ;
257 fnd_file.put_line(fnd_file.log,'P_LOCATION : ' || P_LOCATION ) ;
258 fnd_file.put_line(fnd_file.log,'P_LEGAL_ENTITY_NAME : ' || P_LEGAL_ENTITY_NAME ) ;
259 fnd_file.put_line(fnd_file.log,' *************************** ');
260 END IF;
261
262 BEGIN
263 IF p_report_name = 'JEPTAVAT' THEN --Only for annual report, period year is passed
264 jg_zz_common_pkg.funct_curr_legal(x_func_curr_code => l_func_curr
265 ,x_rep_entity_name => l_legal_entity_name
266 ,x_legal_entity_id => l_legal_entity_id
267 ,x_taxpayer_id => l_tax_payer_id
268 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
269 ,pn_period_year => to_number(p_period));
270
271 p_legal_entity_id := l_legal_entity_id;
272 ELSE
273 jg_zz_common_pkg.funct_curr_legal(x_func_curr_code => l_func_curr
274 ,x_rep_entity_name => l_legal_entity_name
275 ,x_legal_entity_id => l_legal_entity_id
276 ,x_taxpayer_id => l_tax_payer_id
277 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
278 ,pv_period_name => p_period);
279
280 p_legal_entity_id := l_legal_entity_id;
281 END IF ;
282 EXCEPTION
283 WHEN OTHERS THEN
284 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.funct_curr_legal: '||SUBSTR(SQLERRM,1,200));
285 END;
286
287 BEGIN
288 IF p_report_name = 'JEPTAVAT' THEN
289 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
290 ,x_period_start_date => l_period_start_date
291 ,x_period_end_date => l_period_end_date
292 ,x_status => l_reporting_status
293 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
294 ,pv_period_name => NULL
295 ,pn_period_year => to_number(p_period)
296 ,pv_source => 'ALL');
297 ELSE
298 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
299 ,x_period_start_date => l_period_start_date
300 ,x_period_end_date => l_period_end_date
301 ,x_status => l_reporting_status
302 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
303 ,pv_period_name => p_period
304 ,pn_period_year => NULL
305 ,pv_source => 'ALL');
306 END IF;
307 EXCEPTION
308 WHEN OTHERS THEN
309 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.tax_registration: '||SUBSTR(SQLERRM,1,200));
310 END;
311
312 BEGIN
313 if(P_REPORT_NAME <> 'JEPTAVAT' )then
314 l_reporting_status := jg_zz_vat_rep_utility.get_period_status
315 (
316 pn_vat_reporting_entity_id => p_vat_rep_entity_id,
317 pv_tax_calendar_period => p_period,
318 pv_tax_calendar_year => NULL,
319 pv_source => NULL,
320 pv_report_name => P_REPORT_NAME
321 );
322 else
323 l_reporting_status := jg_zz_vat_rep_utility.get_period_status
324 (
325 pn_vat_reporting_entity_id => p_vat_rep_entity_id,
326 pv_tax_calendar_period => NULL,
327 pv_tax_calendar_year => p_period,
328 pv_source => NULL,
329 pv_report_name => P_REPORT_NAME
330 );
331 end if;
332
333 EXCEPTION
334 WHEN OTHERS THEN
335 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.tax_registration: '||SUBSTR(SQLERRM,1,200));
336 END;
337
338 BEGIN
339 jg_zz_common_pkg.company_detail(x_company_name => l_company_name
340 ,x_registration_number => l_registration_num
341 ,x_country => l_country
342 ,x_address1 => l_address_line_1
343 ,x_address2 => l_address_line_2
344 ,x_address3 => l_address_line_3
345 ,x_address4 => l_address_line_4
346 ,x_city => l_city
347 ,x_postal_code => l_postal_code
348 ,x_contact => l_contact_name
349 ,x_phone_number => l_phone_number
350 ,x_province => l_province
351 ,x_comm_number => l_comm_num
352 ,x_vat_reg_num => l_vat_reg_num
353 ,pn_legal_entity_id => l_legal_entity_id
354 ,p_vat_reporting_entity_id => P_VAT_REP_ENTITY_ID);
355 EXCEPTION
356 WHEN OTHERS THEN
357 fnd_file.put_line(fnd_file.log,'Executing jg_zz_common_pkg.company_detail: '||SUBSTR(SQLERRM,1,200));
358 END;
359 BEGIN
360 SELECT activity_code
361 INTO l_activity_code
362 FROM xle_entity_profiles
363 WHERE legal_entity_id = l_legal_entity_id;
364 EXCEPTION
365 WHEN NO_DATA_FOUND THEN
366 fnd_file.put_line(fnd_file.log,'Cannot find Activity Code (Standard Inductry Classification Code for Legal Entity:'||l_legal_entity_id);
367 WHEN OTHERS THEN
368 fnd_file.put_line(fnd_file.log,'Error While retriving Activity Code for Legal Entity:'||l_legal_entity_id);
369 fnd_file.put_line(fnd_file.log,'Error Message :'||SUBSTR(SQLERRM,1,200));
370 END;
371
372 BEGIN
373
374 OPEN get_legal_auth_info(p_legal_entity_id);
375 FETCH get_legal_auth_info INTO
376 l_tax_office_location,
377 l_tax_office_code;
378 CLOSE get_legal_auth_info;
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 fnd_file.put_line(fnd_file.log,'Error Message :'||SUBSTR(SQLERRM,1,200));
383 END;
384
385
386 /* Get Currency Precision */
387
388 BEGIN
389 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Code :'||l_func_curr);
390
391 SELECT precision
392 INTO l_precision
393 FROM fnd_currencies
394 WHERE currency_code = l_func_curr;
395
396 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Precision :'||l_precision);
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 FND_FILE.PUT_LINE(FND_FILE.LOG,'error in getting currency precision');
401 END;
402
403
404 INSERT INTO jg_zz_vat_trx_gt
405 (
406 jg_info_n1
407 ,jg_info_v1
408 ,jg_info_v2
409 ,jg_info_v3
410 ,jg_info_v4
411 ,jg_info_v5
412 ,jg_info_v6
413 ,jg_info_v7
414 ,jg_info_v8
415 ,jg_info_v9
416 ,jg_info_v10
417 ,jg_info_v11
418 ,jg_info_v12
419 ,jg_info_v13
420 ,jg_info_v14
421 ,jg_info_v15
422 ,jg_info_v16
423 ,jg_info_v17
424 ,jg_info_d1
425 ,jg_info_d2
426 ,jg_info_v20
427 ,jg_info_v21
428 ,jg_info_n25
429 ,jg_info_v30
430 ,jg_info_v22
431 ,jg_info_v23
432 ,jg_info_v24
433 )
434 VALUES
435 (
436 l_legal_entity_id
437 ,l_company_name ---l_legal_entity_name
438 ,l_tax_registration_num
439 ,l_registration_num --l_tax_payer_id
440 ,l_contact_name
441 ,l_address_line_1
442 ,l_address_line_2
443 ,l_address_line_3
444 ,l_address_line_4
445 ,l_city
446 ,l_country
447 ,l_phone_number
448 ,l_postal_code
449 ,l_func_curr
450 ,l_reporting_status
451 ,l_tax_regime
452 ,l_activity_code
453 ,l_tax_registration_num
454 ,l_period_end_date
455 ,l_period_start_date
456 ,l_tax_office_location
457 ,l_tax_office_code
458 ,l_precision -- currency precision
459 ,'H'
460 ,l_province
461 ,l_comm_num
462 ,l_vat_reg_num
463 );
464 IF p_debug_flag = 'Y' THEN
465 fnd_file.put_line(fnd_file.log,'Legal Entity ID => ' || l_legal_entity_id);
466 fnd_file.put_line(fnd_file.log,'Company Name => ' || l_company_name);
467 fnd_file.put_line(fnd_file.log,'Legal Entity Name => ' || l_company_name);
468 fnd_file.put_line(fnd_file.log,'Regiatration Number => ' || l_registration_num);
469 fnd_file.put_line(fnd_file.log,'Taxpayer ID => ' || l_registration_num);
470 fnd_file.put_line(fnd_file.log,'Contact Name => ' || l_contact_name);
471 fnd_file.put_line(fnd_file.log,'Address Line 1 => ' || l_address_line_1);
472 fnd_file.put_line(fnd_file.log,'Address Line 2 => ' || l_address_line_2);
473 fnd_file.put_line(fnd_file.log,'Address Line 3 => ' || l_address_line_3);
474 fnd_file.put_line(fnd_file.log,'Address Line 4 => ' || l_address_line_4);
475 fnd_file.put_line(fnd_file.log,'City => ' || l_city);
476 fnd_file.put_line(fnd_file.log,'Country => ' || l_country);
477 fnd_file.put_line(fnd_file.log,'Telephone Number => ' || l_phone_number);
478 fnd_file.put_line(fnd_file.log,'Postal Code => ' || l_postal_code);
479 fnd_file.put_line(fnd_file.log,'Currency Code => ' || l_func_curr);
480 fnd_file.put_line(fnd_file.log,'Reporting Status => ' || l_reporting_status);
481 fnd_file.put_line(fnd_file.log,'Period Start Date => ' || l_period_start_date);
482 fnd_file.put_line(fnd_file.log,'Period End Date => ' || l_period_end_date);
483 fnd_file.put_line(fnd_file.log,'l_tax_office_location => ' || l_tax_office_location);
484 fnd_file.put_line(fnd_file.log,'l_tax_office_code => ' || l_tax_office_code);
485 END IF;
486
487
488 -- Israel VAT Reporting 2010 - ER
489 IF p_report_name = 'JEILSVAT' OR p_report_name = 'JEILR835' THEN
490 BEGIN
491 -- Get the reporting status id
492 FOR cur_get_rep_status_id IN il_get_rep_status_id
493 LOOP
494 l_vat_rep_status_id := cur_get_rep_status_id.reporting_status_id;
495 END LOOP;
496
497 -- Get the VAT Aggregate Limit Amount
498 FOR cur_get_vat_aggregate_limit IN il_get_vat_aggregate_limit
499 LOOP
500 l_vat_aggregation_limit_amt := cur_get_vat_aggregate_limit.VAT_AGGREGATE_LIMIT_AMT;
501 END LOOP;
502 EXCEPTION
503 WHEN OTHERS THEN
504 fnd_file.put_line(fnd_file.log,'Error Message :'||SUBSTR(SQLERRM,1,200));
505 END;
506
507 BEGIN
508 select distinct nvl(jzvre.ledger_id,0)
509 , jzvre.entity_type_code
510 into l_ledger_id,l_entity_type_code
511 from jg_zz_vat_rep_entities jzvre
512 where jzvre.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID;
513 EXCEPTION
514 WHEN OTHERS THEN
515 l_ledger_id := 0;
516 l_entity_type_code := 'NO_ENTITY_TYPE_CODE';
517 END;
518
519 BEGIN
520 select DISTINCT ledger_category_code
521 into l_ledger_category_code
522 from gl_ledgers
523 where ledger_id = l_ledger_id;
524 EXCEPTION
525 WHEN OTHERS THEN
526 l_ledger_category_code := 'NO_CATEGORY_CODE';
527 END;
528
529 IF ( l_entity_type_code = 'LEGAL' OR ( l_entity_type_code = 'ACCOUNTING' AND l_ledger_category_code = 'PRIMARY' ))
530 THEN
531 l_ledger_id := -1;
532 END IF;
533
534 IF l_vat_aggregation_limit_amt IS NULL THEN
535 fnd_message.set_name('JE', 'JE_IL_VAT_LIM_NOT_SET');
536 fnd_message.set_token('P_PERIOD', P_PERIOD);
537 LOG_MESSAGE_MAIN := fnd_message.get;
538 fnd_file.put_line(fnd_file.log,LOG_MESSAGE_MAIN);
539 raise_application_error(-20010,LOG_MESSAGE_MAIN);
540 END IF;
541 IF p_report_name = 'JEILR835' THEN null; --moved logic to jeilr835 procedure
542 ELSIF l_vat_rep_status_id IS NULL THEN
543 fnd_message.set_name('JE', 'JE_IL_VAT_REP_NOT_RUN');
544 fnd_message.set_token('P_PERIOD', P_PERIOD);
545 LOG_MESSAGE_MAIN := fnd_message.get;
546 fnd_file.put_line(fnd_file.log,LOG_MESSAGE_MAIN);
547 select ENTITY_IDENTIFIER
548 into l_ent_name_main
549 from jg_zz_vat_rep_entities
550 where VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID;
551 fnd_file.put_line(fnd_file.log,'>>>>>'||l_ent_name_main);
552 raise_application_error(-20010,LOG_MESSAGE_MAIN);
553 END IF;
554
555 --Set the precision
556 g_precision := l_precision;
557
558 IF p_debug_flag = 'Y' THEN
559 fnd_file.put_line(fnd_file.log,'VAT Aggregate Limit Amount '||to_char(l_vat_aggregation_limit_amt));
560 END IF;
561 IF p_debug_flag = 'Y' THEN
562 fnd_file.put_line(fnd_file.log,'Get Reporting Status ID '||to_char(l_vat_rep_status_id));
563 END IF;
564
565 -- Start ER 9155483 (Ichud Oskim)
566 IF p_report_name = 'JEILR835' THEN
567 IF p_debug_flag = 'Y' THEN
568 fnd_file.put_line(fnd_file.log,'Calling Procedure jeilr835');
569 END IF;
570 jeilr835(p_vat_rep_entity_id => p_vat_rep_entity_id
571 ,p_vat_rep_entity_id2 => p_vat_rep_entity_id2
572 ,p_vat_rep_entity_id3 => p_vat_rep_entity_id3
573 ,p_vat_rep_entity_id4 => p_vat_rep_entity_id4
574 ,p_vat_rep_entity_id5 => p_vat_rep_entity_id5
575 ,p_vat_rep_entity_id6 => p_vat_rep_entity_id6
576 ,p_vat_rep_entity_id7 => p_vat_rep_entity_id7
577 ,p_vat_rep_entity_id8 => p_vat_rep_entity_id8
578 ,p_vat_rep_entity_id9 => p_vat_rep_entity_id9
579 ,p_vat_rep_entity_id10 => p_vat_rep_entity_id10
580 ,p_ichud_oskim_num => p_ichud_oskim_num
581 ,p_period => p_period
582 ,p_vat_trx_type => p_vat_trx_type
583 ,p_report_name => p_report_name
584 ,p_operation_code => p_operation_code
585 ,x_err_msg => l_err_msg);
586 IF p_debug_flag = 'Y' THEN
587 fnd_file.put_line(fnd_file.log,'Completed Call to Procedure jeilr835');
588 END IF;
589
590 END IF;
591 -- End ER 9155483 (Ichud Oskim)
592
593 ELSIF p_report_name = 'JEBEVA06' THEN
594 IF p_debug_flag = 'Y' THEN
595 fnd_file.put_line(fnd_file.log,'Calling Procedure jgbeva06');
596 END IF;
597 jebeva06(p_vat_rep_entity_id => p_vat_rep_entity_id
598 ,p_period => p_period
599 ,p_vat_box => p_vat_box
600 ,p_vat_trx_type_low => p_vat_trx_type_low
601 ,p_vat_trx_type_high => p_vat_trx_type_high
602 ,p_source => p_source
603 ,p_doc_name => p_doc_name
604 ,p_doc_seq_value => p_doc_seq_value
605 ,x_err_msg => l_err_msg);
606 IF p_debug_flag = 'Y' THEN
607 fnd_file.put_line(fnd_file.log,'Completed Call to Procedure jgbava06');
608 END IF;
609 ELSIF p_report_name = 'JEPTAVAT' THEN
610 IF p_debug_flag = 'Y' THEN
611 fnd_file.put_line(fnd_file.log,'Calling Procedure JEPTAVAT');
612 END IF;
613 jeptavat(p_vat_rep_entity_id => p_vat_rep_entity_id
614 ,p_period => p_period
615 ,p_location => p_location
616 ,x_err_msg => l_err_msg);
617 IF p_debug_flag = 'Y' THEN
618 fnd_file.put_line(fnd_file.log,'Completed Call to Procedure JEPTAVAT');
619 END IF;
620 ELSIF p_report_name = 'JEPTPVAT' THEN
621 IF p_debug_flag = 'Y' THEN
622 fnd_file.put_line(fnd_file.log,'Calling Procedure JEPTPVAT');
623 END IF;
624 jeptpvat(p_vat_rep_entity_id => p_vat_rep_entity_id
625 ,p_period => p_period
626 ,p_location => p_location
627 ,x_err_msg => l_err_msg);
628 IF p_debug_flag = 'Y' THEN
629 fnd_file.put_line(fnd_file.log,'Completed Call to Procedure JEPTPVAT');
630 END IF;
631 ELSIF p_report_name = 'JEITPSSR' THEN
632 IF p_debug_flag = 'Y' THEN
633 fnd_file.put_line(fnd_file.log,'Calling Procedure JEITPSSR');
634 END IF;
635 jeitpssr(p_vat_rep_entity_id => p_vat_rep_entity_id
636 ,p_period => p_period
637 ,p_var_on_purchases => p_var_on_purchases
638 ,p_var_on_sales => p_var_on_sales
639 ,x_err_msg => l_err_msg);
640 IF p_debug_flag = 'Y' THEN
641 fnd_file.put_line(fnd_file.log,'Completed Call to Procedure JEITPSSR');
642 END IF;
643 END IF;
644
645 IF p_debug_flag = 'Y' THEN
646 fnd_file.put_line(fnd_file.log,'End of Before Report');
647 END IF;
648
649 RETURN (TRUE);
650 EXCEPTION
651 WHEN NO_DATA_FOUND THEN
652 RETURN (TRUE);
653 WHEN OTHERS THEN
654 fnd_file.put_line(fnd_file.log,'Error in Before Report Trigger' || SQLCODE || SUBSTR(SQLERRM,1,200));
655 raise;
656 RETURN (FALSE);
657 END before_report;
658
659 --
660 -- +======================================================================+
661 -- Name: JEBEVA06
662 --
663 -- Description: This procedure used by the Extract when the Concurrent
664 -- Program 'Belgian VAT Monthly VAT Preparation Report' is run.
665 --
666 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
667 -- P_PERIOD => Tax Calendar Year
668 -- P_VAT_BOX => VAT Report Box
669 -- P_VAT_TRX_TYPE_LOW => VAT Transaction Type Low
670 -- P_VAT_TRX_TYPE_HIGH => VAT Transaction Type Low
671 -- P_SOURCE => Transaction Source
672 -- P_DOC_NAME => Document Sequence Name
673 -- P_DOC_SEQ_VALUE => Document Sequence Value
674 -- +======================================================================+
675 --
676 PROCEDURE jebeva06(p_vat_rep_entity_id IN NUMBER
677 ,p_period IN VARCHAR2
678 ,p_vat_box IN VARCHAR2
679 ,p_vat_trx_type_low IN VARCHAR2
680 ,p_vat_trx_type_high IN VARCHAR2
681 ,p_source IN VARCHAR2
682 ,p_doc_name IN VARCHAR2
683 ,p_doc_seq_value IN VARCHAR2
684 ,x_err_msg OUT NOCOPY VARCHAR2)
685 IS
686
687 /* brathod, modified the cursor to remove usage of jg_zz_alloc_rules table. */
688 CURSOR c_belgian_vat
689 IS
690 SELECT
691 JZVRS.tax_calendar_year PERIOD_YEAR
692 , JZVRS.tax_calendar_period PERIOD_NAME
693 , NVL(AL.tax_box, '99') TAX_BOX
694 , NVL(AL.taxable_box, '99') TAXABLE_BOX
695 , TBLLOOKUP.description TAXABLEBOX_DESCRIPTION
696 , TLOOKUP.description TAXBOX_DESCRIPTION
697 , JG.extract_source_ledger SOURCE
698 , JG.doc_seq_name DOCUMENT_SEQ_NAME
699 , JG.doc_seq_value DOCUMENT_SEQ_VALUE
700 /**************************************************
701 Commented the below code for Bug 7344931 and added GL_DATE
702 , fnd_date.date_to_displaydate(JG.trx_date) GL_DATE
703 **************************************************/
704 , JG.gl_date GL_DATE
705 , JG.billing_tp_number TRADING_PARTNER_NUMBER
706 , JG.billing_tp_name TRADING_PARTNER_NAME
707 , JG.trx_number INVOICE
708 , JG.trx_line_number LINE_NUMBER
709 , JG.tax_rate_Code VAT_CODE
710 , JG.tax_rate_vat_trx_type_code VAT_TRANSACTION_TYPE
711 , nvl(JG.taxable_amt_funcl_curr,JG.taxable_amt) * to_number (alr.taxable_rec_sign_flag || '1') TAXABLE_AMOUNT -- bug9971010
712 , nvl(JG.tax_amt_funcl_curr,JG.tax_amt) * to_number(decode(JG.extract_source_ledger, 'AP',
713 decode(jg.tax_recoverable_flag, 'Y',
714 alr.tax_rec_sign_flag, alr.tax_non_rec_sign_flag) || '1',
715 alr.tax_rec_sign_flag || '1')) TAX_AMOUNT
716 ,JG.tax_line_id
717 ,AL.allocation_rule_id
718 ,nvl(JG.cancel_flag,'N') cancel_flag
719 ,JG.TAXABLE_ITEM_SOURCE_ID source_id --Added for bug 11813288
720 ,Jg.Tax_Recoverable_Flag
721 ,Jg.Tax_Recovery_Rate
722 ,zx.Record_Type_Code
723 FROM
724 jg_zz_vat_box_allocs AL
725 , jg_zz_vat_alloc_rules alr
726 , jg_zz_vat_trx_details JG
727 , fnd_lookup_values TLOOKUP
728 , fnd_lookup_values TBLLOOKUP
729 , Jg_Zz_Vat_Rep_Status Jzvrs
730 , ZX_lines zx
731 WHERE
732 NVL(AL.taxable_box, '99') = TBLLOOKUP.lookup_code
733 AND NVL(AL.tax_box, '99') = TLOOKUP.lookup_code
734 AND TLOOKUP.lookup_type = 'JGZZ_VAT_REPORT_BOXES'
735 AND TBLLOOKUP.lookup_type = 'JGZZ_VAT_REPORT_BOXES'
736 AND TLOOKUP.language = USERENV('LANG')
737 AND TBLLOOKUP.language = USERENV('LANG')
738 AND AL.PERIOD_TYPE = 'PERIODIC'
739 AND jg.tax_rate_vat_trx_type_code BETWEEN NVL( P_VAT_TRX_TYPE_LOW,jg.tax_rate_vat_trx_type_code )
740 AND NVL( P_VAT_TRX_TYPE_HIGH,jg.tax_rate_vat_trx_type_code )
741 AND JZVRS.reporting_status_id = JG.reporting_status_id
742 AND AL.vat_transaction_id = JG.vat_transaction_id
743 AND alr.allocation_rule_id = al.allocation_rule_id
744 AND (P_vat_box IS NULL OR AL.tax_box = P_vat_box OR AL.taxable_box = P_vat_box)
745 AND JG.extract_source_ledger = Nvl((decode(P_source,'ALL',Jg.Extract_Source_Ledger,P_source)),Jg.Extract_Source_Ledger)
746 AND (JG.doc_seq_name = P_doc_name OR P_doc_name IS NULL)
747 AND (JG.doc_seq_value = P_doc_seq_value OR P_doc_seq_value IS NULL)
748 AND JZVRS.vat_reporting_entity_id = P_vat_rep_entity_id
749 And Jzvrs.Tax_Calendar_Period = P_Period
750 And jg.trx_id = zx.trx_id
751 And jg.tax_line_id = zx.tax_line_id
752 AND jg.application_id = zx.application_id
753 AND jg.entity_code = zx.entity_code
754 AND jg.event_class_code = zx.event_class_code
755 ORDER BY
756 JG.tax_line_id
757 , AL.allocation_rule_id
758 ,JG.taxable_item_source_id
759 , nvl(JG.cancel_flag,'N')
760 ,JG.actg_source_id
761 , JG.trx_date
762 , AL.tax_box
763 , source
764 , JG.doc_seq_name
765 , JG.doc_seq_value
766 , DECODE (jg.extract_source_ledger
767 ,'AR', JG.trx_line_number
768 ,'AP', JG.trx_line_number
769 ,'GL', JG.trx_line_number);
770 -- ,'CR', JG.trx_line_id); brathod, commented as 'CR' type of transactions are merged with AR
771
772 l_be_vat c_belgian_vat%ROWTYPE;
773
774 /**
775 Brathod, Modified record defination to use table refered data types instead of normal data types
776 */
777
778 TYPE r_box_amounts IS RECORD
779 (
780 period_year jg_zz_vat_rep_status.tax_calendar_year%type --NUMBER
781 ,period_name jg_zz_vat_rep_status.tax_calendar_period%type --VARCHAR2(10)
782 ,tax_box jg_zz_vat_box_allocs.tax_box%type
783 ,taxable_box jg_zz_vat_box_allocs.taxable_box%type --VARCHAR2(10)
784 ,taxablebox_description fnd_lookup_values.description%type --VARCHAR2(250)
785 ,taxbox_description fnd_lookup_values.description%type --VARCHAR2(250)
786 ,source jg_zz_vat_trx_details.extract_source_ledger%type -- VARCHAR2(10)
787 ,document_seq_name jg_zz_vat_trx_details.doc_seq_name%type -- VARCHAR2(60)
788 ,document_seq_value jg_zz_vat_trx_details.doc_seq_value%type --NUMBER
789 ,gl_date jg_zz_vat_trx_details.trx_date%type
790 ,trading_partner_number jg_zz_vat_trx_details.billing_tp_number%type -- VARCHAR2(240)
791 ,trading_partner_name jg_zz_vat_trx_details.billing_tp_name%type --VARCHAR2(240)
792 ,invoice jg_zz_vat_trx_details.trx_number%type --VARCHAR2(60)
793 ,line_number jg_zz_vat_trx_details.trx_line_number%type --NUMBER
794 ,vat_code jg_zz_vat_trx_details.tax_rate_Code%type --VARCHAR2(100)
795 ,vat_transaction_type jg_zz_vat_trx_details.tax_rate_vat_trx_type_code%type --VARCHAR2(100)
796 ,taxable_amount jg_zz_vat_trx_details.taxable_amt%type --NUMBER
797 ,tax_amount jg_zz_vat_trx_details.tax_amt%type --NUMBER
798 ,tax_line_id jg_zz_vat_trx_details.tax_line_id%type --NUMBER
799 ,allocation_rule_id jg_zz_vat_box_allocs.allocation_rule_id%type -- NUMBER
800 ,cancel_flag jg_zz_vat_trx_details.cancel_flag%type
801 ,source_id jg_zz_vat_trx_details.taxable_item_source_id%type --Added for bug 11813288
802 ,Tax_Recoverable_Flag Jg_Zz_Vat_Trx_Details.Tax_Recoverable_Flag%Type
803 ,Tax_Recovery_Rate Jg_Zz_Vat_Trx_Details.Tax_Recovery_Rate%Type
804 ,Record_Type_Code ZX_LINES.Record_Type_Code%type
805 );
806
807 TYPE r_vatbox_amounts IS RECORD
808 (
809 period_year jg_zz_vat_rep_status.tax_calendar_year%type --NUMBER
810 ,period_name jg_zz_vat_rep_status.tax_calendar_period%type --VARCHAR2(10)
811 ,vat_report_box jg_zz_vat_box_allocs.tax_box%type --VARCHAR2(10)
812 ,vat_box_description fnd_lookup_values.description%type --VARCHAR2(250)
813 ,source jg_zz_vat_trx_details.extract_source_ledger%type -- VARCHAR2(10)
814 ,document_seq_name jg_zz_vat_trx_details.doc_seq_name%type -- VARCHAR2(60)
815 ,document_seq_value jg_zz_vat_trx_details.doc_seq_value%type --NUMBER
816 ,gl_date jg_zz_vat_trx_details.accounting_date%type
817 ,trading_partner_number jg_zz_vat_trx_details.billing_tp_number%type -- VARCHAR2(240)
818 ,trading_partner_name jg_zz_vat_trx_details.billing_tp_name%type --VARCHAR2(240)
819 ,invoice jg_zz_vat_trx_details.trx_number%type --VARCHAR2(60)
820 ,line_number jg_zz_vat_trx_details.trx_line_number%type --NUMBER
821 ,vat_code jg_zz_vat_trx_details.tax%type --VARCHAR2(100)
822 ,vat_transaction_type jg_zz_vat_trx_details.tax_rate_vat_trx_type_code%type --VARCHAR2(100)
823 ,taxable_amount jg_zz_vat_trx_details.taxable_amt%type --NUMBER
824 ,tax_amount jg_zz_vat_trx_details.tax_amt%type --NUMBER
825 );
826
827
828 TYPE t_box_amounts IS TABLE OF r_box_amounts
829 INDEX BY BINARY_INTEGER;
830
831 TYPE t_vatbox_amounts IS TABLE OF r_vatbox_amounts
832 INDEX BY BINARY_INTEGER;
833
834 v_box_amount t_box_amounts;
835 l_box_amount_grouped t_vatbox_amounts;
836 l_index NUMBER;
837 l_count NUMBER := 0 ;
838
839 t_tax_line_id NUMBER :=0;
840 t_allocation_rule_id NUMBER :=0;
841 t_cancel_flag VARCHAR2(1) := 'X';
842 t_source_id NUMBER :=-1; --Added for bug 11813288
843 t_invoice_amt NUMBER := 0.00000000000001;
844
845 BEGIN
846 IF p_debug_flag = 'Y' THEN
847 fnd_file.put_line(fnd_file.log,'Executing jg_zz_summary_all_pkg.jgbeva06');
848 fnd_file.put_line(fnd_file.log,'Open Cursor C_BELGIAN_VAT');
849 END IF;
850 OPEN c_belgian_vat;
851 FETCH c_belgian_vat BULK COLLECT INTO v_box_amount;
852 CLOSE c_belgian_vat;
853 l_index := 1;
854 FOR i IN 1..v_box_amount.COUNT
855 LOOP
856 IF v_box_amount(i).tax_box = v_box_amount(i).taxable_box THEN
857 -- The Tax Box is same as taxable box. Therefore no need for changes.
858 l_box_amount_grouped(l_index).period_year := v_box_amount(i).period_year;
859 l_box_amount_grouped(l_index).period_name := v_box_amount(i).period_name;
860 l_box_amount_grouped(l_index).vat_report_box := v_box_amount(i).tax_box;
861 l_box_amount_grouped(l_index).vat_box_description := v_box_amount(i).taxablebox_description;
862 l_box_amount_grouped(l_index).source := v_box_amount(i).source;
863 l_box_amount_grouped(l_index).document_seq_name := v_box_amount(i).document_seq_name;
864 l_box_amount_grouped(l_index).document_seq_value := v_box_amount(i).document_seq_value;
865 l_box_amount_grouped(l_index).gl_date := v_box_amount(i).gl_date;
866 l_box_amount_grouped(l_index).trading_partner_number := v_box_amount(i).trading_partner_number;
867 l_box_amount_grouped(l_index).trading_partner_name := v_box_amount(i).trading_partner_name;
868 l_box_amount_grouped(l_index).invoice := v_box_amount(i).invoice;
869 l_box_amount_grouped(l_index).line_number := v_box_amount(i).line_number;
870 l_box_amount_grouped(l_index).vat_code := v_box_amount(i).vat_code;
871 l_box_amount_grouped(l_index).vat_transaction_type := v_box_amount(i).vat_transaction_type;
872 l_box_amount_grouped(l_index).tax_amount := v_box_amount(i).tax_amount;
873
874 /* if v_box_amount(i).tax_line_id <> t_tax_line_id or
875 v_box_amount(i).allocation_rule_id <> t_allocation_rule_id or
876 v_box_amount(i).cancel_flag <> t_cancel_flag or
877 v_box_amount(i).source_id <> t_source_id or --Added for bug 11813288
878 v_box_amount(i).taxable_amount <> t_invoice_amt */
879
880 IF
881 ( ( V_Box_Amount(I).Source = 'AP' And V_Box_Amount(I).Tax_Recoverable_Flag = 'N')
882 Or (V_Box_Amount(I).Source<> 'AP') )
883 Then
884 L_Box_Amount_Grouped(L_Index).Taxable_Amount := V_Box_Amount(I).Taxable_Amount;
885
886 Elsif
887 ( V_Box_Amount(I).Source = 'AP' and V_Box_Amount(I).Record_Type_Code='MIGRATED' And V_Box_Amount(I).Tax_Recovery_Rate=100 )
888 Then
889 L_Box_Amount_Grouped(L_Index).Taxable_Amount := V_Box_Amount(I).Taxable_Amount;
890
891 Else
892 L_Box_Amount_Grouped(L_Index).Taxable_Amount := Null;
893 END IF;
894
895 t_tax_line_id := v_box_amount(i).tax_line_id;
896 t_allocation_rule_id := v_box_amount(i).allocation_rule_id;
897 t_cancel_flag := v_box_amount(i).cancel_flag;
898 t_source_id :=v_box_amount(i).source_id; --Added for bug 11813288
899 t_invoice_amt := v_box_amount(i).taxable_amount;
900
901 ELSE
902 -- Tax Box and Taxable Box Numbers are different. Therefore create two lines
903 -- Create the Taxable Box Line
904 l_box_amount_grouped(l_index).period_year := v_box_amount(i).period_year;
905 l_box_amount_grouped(l_index).period_name := v_box_amount(i).period_name;
906 l_box_amount_grouped(l_index).vat_report_box := v_box_amount(i).taxable_box;
907 l_box_amount_grouped(l_index).vat_box_description := v_box_amount(i).taxablebox_description;
908 l_box_amount_grouped(l_index).source := v_box_amount(i).source;
909 l_box_amount_grouped(l_index).document_seq_name := v_box_amount(i).document_seq_name;
910 l_box_amount_grouped(l_index).document_seq_value := v_box_amount(i).document_seq_value;
911 l_box_amount_grouped(l_index).gl_date := v_box_amount(i).gl_date;
912 l_box_amount_grouped(l_index).trading_partner_number := v_box_amount(i).trading_partner_number;
913 l_box_amount_grouped(l_index).trading_partner_name := v_box_amount(i).trading_partner_name;
914 l_box_amount_grouped(l_index).invoice := v_box_amount(i).invoice;
915 l_box_amount_grouped(l_index).line_number := v_box_amount(i).line_number;
916 l_box_amount_grouped(l_index).vat_code := v_box_amount(i).vat_code;
917 l_box_amount_grouped(l_index).vat_transaction_type := v_box_amount(i).vat_transaction_type;
918 l_box_amount_grouped(l_index).tax_amount := NULL;
919
920 /*if v_box_amount(i).tax_line_id <> t_tax_line_id or
921 v_box_amount(i).allocation_rule_id <> t_allocation_rule_id or
922 v_box_amount(i).cancel_flag <> t_cancel_flag or
923 v_box_amount(i).source_id <> t_source_id or --Addded for bug 11813288
924 v_box_amount(i).taxable_amount <> t_invoice_amt */
925
926 If
927 ( ( V_Box_Amount(I).Source = 'AP' And V_Box_Amount(I).Tax_Recoverable_Flag = 'N')
928 Or (V_Box_Amount(I).Source<> 'AP') )
929 Then
930 L_Box_Amount_Grouped(L_Index).Taxable_Amount := V_Box_Amount(I).Taxable_Amount;
931
932 Elsif
933 ( V_Box_Amount(I).Source = 'AP' and V_Box_Amount(I).Record_Type_Code='MIGRATED' And V_Box_Amount(I).Tax_Recovery_Rate=100 )
934 Then
935 L_Box_Amount_Grouped(L_Index).Taxable_Amount := V_Box_Amount(I).Taxable_Amount;
936
937 Else
938 L_Box_Amount_Grouped(L_Index).Taxable_Amount := Null;
939 END IF;
940
941 t_tax_line_id := v_box_amount(i).tax_line_id;
942 t_allocation_rule_id := v_box_amount(i).allocation_rule_id;
943 t_cancel_flag := v_box_amount(i).cancel_flag;
944 t_source_id :=v_box_amount(i).source_id; --Added for bug 11813288
945 t_invoice_amt := v_box_amount(i).taxable_amount;
946
947 -- Create the Tax Line
948 l_index := l_index + 1;
949 l_box_amount_grouped(l_index).period_year := v_box_amount(i).period_year;
950 l_box_amount_grouped(l_index).period_name := v_box_amount(i).period_name;
951 l_box_amount_grouped(l_index).vat_report_box := v_box_amount(i).tax_box;
952 l_box_amount_grouped(l_index).vat_box_description := v_box_amount(i).taxbox_description;
953 l_box_amount_grouped(l_index).source := v_box_amount(i).source;
954 l_box_amount_grouped(l_index).document_seq_name := v_box_amount(i).document_seq_name;
955 l_box_amount_grouped(l_index).document_seq_value := v_box_amount(i).document_seq_value;
956 l_box_amount_grouped(l_index).gl_date := v_box_amount(i).gl_date;
957 l_box_amount_grouped(l_index).trading_partner_number := v_box_amount(i).trading_partner_number;
958 l_box_amount_grouped(l_index).trading_partner_name := v_box_amount(i).trading_partner_name;
959 l_box_amount_grouped(l_index).invoice := v_box_amount(i).invoice;
960 l_box_amount_grouped(l_index).line_number := v_box_amount(i).line_number;
961 l_box_amount_grouped(l_index).vat_code := v_box_amount(i).vat_code;
962 l_box_amount_grouped(l_index).vat_transaction_type := v_box_amount(i).vat_transaction_type;
963 l_box_amount_grouped(l_index).taxable_amount := NULL;
964 l_box_amount_grouped(l_index).tax_amount := v_box_amount(i).tax_amount;
965 END IF;
966 l_index := l_index + 1;
967 END LOOP;
968
969 FOR i IN 1..l_box_amount_grouped.COUNT
970 LOOP
971 IF p_debug_flag = 'Y' THEN
972 fnd_file.put_line(fnd_file.log,'Period Year => ' || l_box_amount_grouped(i).period_year);
973 fnd_file.put_line(fnd_file.log,'Period Name => ' || l_box_amount_grouped(i).period_name);
974 fnd_file.put_line(fnd_file.log,'VAT Report Box => ' || l_box_amount_grouped(i).vat_report_box);
975 fnd_file.put_line(fnd_file.log,'Box Descriptionc => ' || l_box_amount_grouped(i).vat_box_description);
976 fnd_file.put_line(fnd_file.log,'Source => ' || l_box_amount_grouped(i).source);
977 fnd_file.put_line(fnd_file.log,'Document Name => ' || l_box_amount_grouped(i).document_seq_name);
978 fnd_file.put_line(fnd_file.log,'Documemt Value => ' || l_box_amount_grouped(i).document_seq_value);
979 fnd_file.put_line(fnd_file.log,'GL Date => ' || l_box_amount_grouped(i).gl_date);
980 fnd_file.put_line(fnd_file.log,'Vendor Number => ' || l_box_amount_grouped(i).trading_partner_number);
981 fnd_file.put_line(fnd_file.log,'Vendor_Name => ' || l_box_amount_grouped(i).trading_partner_name);
982 fnd_file.put_line(fnd_file.log,'Invoice Number => ' || l_box_amount_grouped(i).invoice);
983 fnd_file.put_line(fnd_file.log,'Line Number => ' || l_box_amount_grouped(i).line_number);
984 fnd_file.put_line(fnd_file.log,'VAT Code => ' || l_box_amount_grouped(i).vat_code);
985 fnd_file.put_line(fnd_file.log,'VAT Trans Type => ' || l_box_amount_grouped(i).vat_transaction_type);
986 fnd_file.put_line(fnd_file.log,'Taxable Amount => ' || l_box_amount_grouped(i).taxable_amount);
987 fnd_file.put_line(fnd_file.log,'Tax Amount => ' || l_box_amount_grouped(i).tax_amount);
988 END IF;
989
990 IF l_box_amount_grouped(i).taxable_amount IS NOT NULL OR
991 l_box_amount_grouped(i).tax_amount IS NOT NULL THEN
992
993 INSERT INTO jg_zz_vat_trx_gt
994 (
995 jg_info_n1
996 , jg_info_v1
997 , jg_info_v2
998 , jg_info_v3
999 , jg_info_v4
1000 , jg_info_v5
1001 , jg_info_n2
1002 , jg_info_d1
1003 , jg_info_v6
1004 , jg_info_v7
1005 , jg_info_v8
1006 , jg_info_n3
1007 , jg_info_v9
1008 , jg_info_v10
1009 , jg_info_n4
1010 , jg_info_n5
1011 )
1012 VALUES(
1013 l_box_amount_grouped(i).period_year
1014 , l_box_amount_grouped(i).period_name
1015 , l_box_amount_grouped(i).vat_report_box
1016 , l_box_amount_grouped(i).vat_box_description
1017 , l_box_amount_grouped(i).source
1018 , l_box_amount_grouped(i).document_seq_name
1019 , l_box_amount_grouped(i).document_seq_value
1020 , l_box_amount_grouped(i).gl_date
1021 , l_box_amount_grouped(i).trading_partner_number
1022 , l_box_amount_grouped(i).trading_partner_name
1023 , l_box_amount_grouped(i).invoice
1024 , l_box_amount_grouped(i).line_number
1025 , l_box_amount_grouped(i).vat_code
1026 , l_box_amount_grouped(i).vat_transaction_type
1027 , l_box_amount_grouped(i).taxable_amount
1028 , l_box_amount_grouped(i).tax_amount
1029 );
1030
1031 END IF;
1032
1033 END LOOP;
1034
1035 SELECT count(*) into l_count from jg_zz_vat_trx_gt ;
1036 IF p_debug_flag = 'Y' THEN
1037 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_count );
1038 fnd_file.put_line(fnd_file.log,'Completed procedure jg_zz_summary_all_pkg.jgbeva06');
1039 END IF;
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.jebeva06 ' || SUBSTR(SQLERRM,1,200));
1043 RAISE;
1044 END jebeva06;
1045
1046 --
1047 -- +======================================================================+
1048 -- Name: JEPTAVAT
1049 --
1050 -- Description: This procedure used by the Extract when the Concurrent
1051 -- Program 'Portuguese Annual VAT Report' is run.
1052 --
1053 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
1054 -- P_PERIOD => Tax Calendar Year
1055 -- P_LOCATION => Location Code
1056 -- +======================================================================+
1057 --
1058 PROCEDURE jeptavat(p_vat_rep_entity_id IN NUMBER
1059 ,p_period IN VARCHAR2
1060 ,p_location IN VARCHAR2
1061 ,x_err_msg OUT NOCOPY VARCHAR2)
1062 IS
1063 CURSOR c_pt_annual_vat
1064 IS
1065 SELECT
1066 NVL(
1067 DECODE(ALLOCBOX.tax_recoverable_flag,
1068 'Y',TO_NUMBER(ALLOCBOX.taxable_box)),1000) TXBL_REC_BOX
1069 , NVL(
1070 DECODE(NVL(ALLOCBOX.tax_recoverable_flag,'N'),
1071 'N',TO_NUMBER(ALLOCBOX.taxable_box)),1000) TXBL_NRC_BOX
1072
1073 , DECODE(JG.extract_source_ledger
1074 ,'AP', DECODE(JG.tax_recoverable_flag,'Y', NVL(JG.taxable_amt_funcl_curr ,0))
1075 ,NVL(JG.taxable_amt_funcl_curr ,0)) TXBL_AMOUNT
1076
1077 , DECODE(JG.extract_source_ledger
1078 ,'AP', DECODE(JG.tax_recoverable_flag,'N', NVL(JG.taxable_amt_funcl_curr ,0))
1079 ,NVL(JG.taxable_amt_funcl_curr ,0)) TXBL_NRC_AMOUNT
1080
1081 /* , DECODE(ALLOCBOX.tax_recoverable_flag,
1082 'Y',taxable_amt, 0) TXBL_AMOUNT
1083
1084 , DECODE(ALLOCBOX.tax_recoverable_flag,'Y',0,taxable_amt ) TXBL_NRC_AMOUNT */
1085
1086 , NVL(TO_NUMBER(ALLOCRUL.TOTAL_BOX),1000) TOTAL_BOX
1087
1088 , NVL(TO_NUMBER(ALLOCBOX.tax_box),1000) TAX_BOX --Here we have to identify, whether this tax box is recoverable or non-rec box.
1089
1090 , NVL(DECODE(JG.extract_source_ledger,'GL',
1091 DECODE(JG.prl_no,
1092 'I',(NVL(JG.taxable_amt_funcl_curr ,0)*(NVL(JG.tax_rate,0)/100))*(NVL(JG.tax_recovery_rate,0)/ 100),
1093 'O',((NVL(JG.taxable_amt_funcl_curr,0)*(NVL(JG.tax_rate,0)/100))*(NVL(JG.tax_recovery_rate,0)/ 100)*-1 ))
1094 ,DECODE(JG.extract_source_ledger,'AP',NVL(JG.tax_amt_funcl_curr , 0)
1095 ,DECODE(JG.extract_source_ledger,'AR',NVL(JG.tax_amt_funcl_curr , 0)*-1)))
1096 ,0) TAX_AMOUNT
1097
1098 , DECODE(JG.application_id,
1099 200,'I',
1100 222,'0',
1101 101,JG.prl_no) TAX_CLASS
1102 , JG.tax_rate_code TAX_CODE_RATE
1103 , JG.tax_rate TAX_RATE
1104 , JG.trx_id TRANSACTION_ID
1105 , JG.trx_number TRX_NUMBER
1106 , JG.extract_source_ledger SOURCE_LEDGER
1107 , JG.tax_recovery_rate TAX_RECV_RATE
1108 , ALLOCBOX.tax_recoverable_flag TAX_RECOVERABLE_FLAG
1109 , JG.extract_source_ledger EXTRACT_SOURCE_LEDGER
1110 , AllOCBOX.allocation_rule_id ALLOCATION_RULE_ID
1111 FROM jg_zz_vat_trx_details JG
1112 , jg_zz_vat_rep_status JZVRS
1113 , jg_zz_vat_box_allocs AllOCBOX
1114 , jg_zz_vat_alloc_rules ALLOCRUL
1115 WHERE JZVRS.reporting_status_id = JG.reporting_status_id
1116 AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1117 AND JZVRS.tax_calendar_year = p_period
1118 AND JG.pt_location = p_location
1119 AND AllOCBOX.vat_transaction_id = JG.vat_transaction_id
1120 AND AllOCBOX.allocation_rule_id = ALLOCRUL.allocation_rule_id
1121 AND AllOCBOX.period_type = 'ANNUAL'
1122 ORDER BY JG.trx_id,
1123 AllOCBOX.allocation_rule_id;
1124
1125 -- Record Type to hold amounts
1126 TYPE r_Box_Amounts IS RECORD(
1127 taxable_amount NUMBER,
1128 tax_amount NUMBER,
1129 box_type VARCHAR2(10),
1130 tot_box_num NUMBER,
1131 tax_rate NUMBER);
1132
1133 -- Table Type of record
1134 TYPE t_Box_Amounts IS TABLE OF r_Box_Amounts
1135 INDEX BY BINARY_INTEGER;
1136
1137 -- Variable of above table type
1138 v_Box_Amounts t_Box_Amounts;
1139 l_data_found VARCHAR2(1) := 'N';
1140 l_tax_box NUMBER;
1141 l_count NUMBER := 0 ;
1142 ln_stat_no NUMBER ;
1143 BEGIN
1144 ln_stat_no := 1;
1145 FOR l_pt_avat in c_pt_annual_vat LOOP
1146 ln_stat_no := 2;
1147 IF v_box_amounts.EXISTS(l_pt_avat.txbl_rec_box) THEN
1148 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);
1149 ELSE
1150 v_box_amounts(l_pt_avat.txbl_rec_box).taxable_amount := nvl(l_pt_avat.txbl_amount,0);
1151 v_box_amounts(l_pt_avat.txbl_rec_box).tot_box_num := l_pt_avat.total_box;
1152 v_box_amounts(l_pt_avat.txbl_rec_box).box_type := 'RECTXBL';
1153 END IF;
1154 ln_stat_no := 3;
1155 IF v_box_amounts.EXISTS(l_pt_avat.txbl_nrc_box) THEN
1156 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;
1157 ELSE
1158 v_box_amounts(l_pt_avat.txbl_nrc_box).taxable_amount := nvl(l_pt_avat.txbl_amount,0);
1159 v_box_amounts(l_pt_avat.txbl_nrc_box).tot_box_num := l_pt_avat.total_box;
1160 v_box_amounts(l_pt_avat.txbl_nrc_box).box_type := 'NRCTXBL';
1161 END IF;
1162 ln_stat_no := 4;
1163 -- SUM together Tax Amounts for this box
1164 IF l_pt_avat.tax_class <> 'OFFSET' THEN
1165 l_tax_box := l_pt_avat.tax_box * 10000 + nvl(l_pt_avat.tax_rate,0) * 100;
1166 IF v_box_amounts.EXISTS(l_tax_box) THEN
1167 v_box_amounts(l_tax_box).tax_amount := NVL(v_box_amounts(l_tax_box).tax_amount ,0) + l_pt_avat.tax_amount;
1168 ELSE
1169 v_box_amounts(l_tax_box).tax_amount := l_pt_avat.tax_amount;
1170 v_box_amounts(l_tax_box).tax_rate := nvl(l_pt_avat.tax_rate,0);
1171 v_box_amounts(l_tax_box).box_type := 'TAX';
1172 END IF;
1173 ln_stat_no := 5;
1174 END IF;
1175 ln_stat_no := 6;
1176 -- Set data flag to yes
1177 l_data_found := 'Y';
1178
1179 IF p_debug_flag = 'Y' then
1180 fnd_file.put_line(fnd_file.log,'---- Trans Id ------ '||l_pt_avat.transaction_id||'-----');
1181 fnd_file.put_line(fnd_file.log,'---- Trans Number--- '||l_pt_avat.trx_number||'-----');
1182 fnd_file.put_line(fnd_file.log,'Tax class information: ' || l_pt_avat.tax_class);
1183 fnd_file.put_line(fnd_file.log,'Tax Recovery Rate: ' ||l_pt_avat.tax_recv_rate);
1184 fnd_file.put_line(fnd_file.log,'Tax Recoverable Flag : '||l_pt_avat.tax_recoverable_flag);
1185 fnd_file.put_line(fnd_file.log,'Taxable box: ' ||l_pt_avat.txbl_nrc_box);
1186 fnd_file.put_line(fnd_file.log,'Taxable amount: ' ||l_pt_avat.txbl_nrc_amount);
1187 fnd_file.put_line(fnd_file.log,'Tax box: ' || l_pt_avat.tax_box);
1188 fnd_file.put_line(fnd_file.log,'Tax Amount: '||l_pt_avat.tax_amount);
1189 fnd_file.put_line(fnd_file.log,'Total box: '|| l_pt_avat.total_box);
1190 fnd_file.put_line(fnd_file.log,'Tax rate: ' || l_pt_avat.tax_rate);
1191 fnd_file.put_line(fnd_file.log,'Tax code: ' || l_pt_avat.tax_code_rate);
1192 fnd_file.put_line(fnd_file.log,'Extract Source Ledger: ' || l_pt_avat.extract_source_ledger);
1193 fnd_file.put_line(fnd_file.log,'Allocation Rule ID: ' || l_pt_avat.allocation_rule_id);
1194
1195 END IF;
1196
1197 END LOOP;
1198
1199 IF l_data_found = 'Y' THEN
1200 FOR i in v_box_amounts.FIRST .. v_box_amounts.LAST LOOP
1201
1202 IF p_debug_flag = 'Y' and v_box_amounts.EXISTS(i) and i <> 1000 THEN
1203 fnd_file.put_line(fnd_file.log,'-----------------------------------');
1204 IF i > 1000 THEN
1205 fnd_file.put_line(fnd_file.log,' Box Num =>'||floor(i/10000));
1206 ELSE
1207 fnd_file.put_line(fnd_file.log,' Box Num =>'||i);
1208 END IF;
1209 fnd_file.put_line(fnd_file.log,' Taxable Amount =>'|| v_box_amounts(i).taxable_amount);
1210 fnd_file.put_line(fnd_file.log,' Tax Amount =>'|| v_box_amounts(i).tax_amount);
1211 fnd_file.put_line(fnd_file.log,' Total Box Number =>'|| v_box_amounts(i).tot_box_num);
1212 fnd_file.put_line(fnd_file.log,' Tax Rate =>'|| v_box_amounts(i).tax_rate);
1213 fnd_file.put_line(fnd_file.log,'-----------------------------------');
1214 END IF;
1215
1216 IF v_box_amounts.EXISTS(i) and i <= 1000 THEN
1217 INSERT INTO jg_zz_vat_trx_gt ( jg_info_n1
1218 ,jg_info_n2
1219 ,jg_info_n3
1220 ,jg_info_n4
1221 ,jg_info_v1
1222 )
1223 VALUES(
1224 i,
1225 v_box_amounts(i).taxable_amount,
1226 v_box_amounts(i).tax_amount,
1227 v_box_amounts(i).tot_box_num,
1228 v_box_amounts(i).box_type
1229 );
1230 ELSIF v_box_amounts.EXISTS(i) and i > 1000 THEN --This must be a tax box
1231 INSERT INTO jg_zz_vat_trx_gt ( jg_info_n1
1232 ,jg_info_n2
1233 ,jg_info_n3
1234 ,jg_info_n5
1235 ,jg_info_v1
1236 )
1237 VALUES(
1238 floor(i/10000) + decode(sign(v_box_amounts(i).tax_rate), -1, 1, 0)
1239 ,v_box_amounts(i).taxable_amount
1240 ,v_box_amounts(i).tax_amount
1241 ,v_box_amounts(i).tax_rate
1242 ,v_box_amounts(i).box_type
1243 );
1244 END IF;
1245 END LOOP;
1246 END IF;
1247
1248 select count(*) into l_count from jg_zz_vat_trx_gt ;
1249 IF p_debug_flag = 'Y' THEN
1250 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table :' || l_count );
1251 END IF ;
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.jeptavat: ' || SUBSTR(SQLERRM,1,200));
1255 fnd_file.put_line(fnd_file.log,'Error at statement: ' || ln_stat_no);
1256 RAISE;
1257 END jeptavat;
1258
1259 --
1260 -- +======================================================================+
1261 -- Name: JGPTPVAT
1262 --
1263 -- Description: This procedure used by the Extract when the Concurrent
1264 -- Program 'Portuguese Periodic VAT Report' is run.
1265 --
1266 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
1267 -- P_PERIOD => Tax Calendar Period
1268 -- P_LOCATION => Location Code
1269 -- +======================================================================+
1270 --
1271 PROCEDURE jeptpvat(p_vat_rep_entity_id IN NUMBER
1272 ,p_period IN VARCHAR2
1273 ,p_location IN VARCHAR2
1274 ,x_err_msg OUT NOCOPY VARCHAR2)
1275 IS
1276 l_data_found VARCHAR2(1) := 'N';
1277
1278 -- Record Type to hold amounts
1279 TYPE r_Box_Amounts IS RECORD(
1280 taxable_amount NUMBER,
1281 deduct_tax NUMBER,
1282 clear_tax NUMBER,
1283 box_type VARCHAR2(10));
1284
1285 -- Table Type of record
1286 TYPE t_Box_Amounts IS TABLE OF r_Box_Amounts
1287 INDEX BY BINARY_INTEGER;
1288
1289 -- Variable of above table type
1290 v_Box_Amounts t_Box_Amounts;
1291
1292 -- Cursor for holding amounts from JG table
1293 CURSOR c_pt_periodic_vat
1294 IS
1295 SELECT NVL(TO_NUMBER(al.taxable_box), 1000) TAXABLE_BOX
1296
1297 , NVL(taxable_amt_funcl_curr , 0) TAXABLE_AMOUNT
1298
1299 , NVL(TO_NUMBER(al.tax_box), 1000) TAX_BOX
1300
1301 , NVL(DECODE(DECODE(JG.extract_source_ledger
1302 ,'GL',DECODE(JG.PRL_NO,'I','AP','O','AR')
1303 ,JG.extract_source_ledger)
1304 , 'AP', DECODE(SIGN(tax_amt_funcl_curr )
1305 , 1 , DECODE(trx_line_class,'AP_CREDIT_MEMO',0,
1306 'AP_DEBIT_MEMO',0,tax_amt_funcl_curr )
1307 , DECODE(reporting_code
1308 , 'OFFSET' , 0
1309 , DECODE(trx_line_class
1310 , 'AP_CREDIT_MEMO' , 0
1311 , 'AP_DEBIT_MEMO' , 0
1312 ,tax_amt_funcl_curr )))
1313 , 'AR', DECODE(reporting_code
1314 , 'OFFSET',DECODE(SIGN(tax_amt_funcl_curr ), 1,tax_amt_funcl_curr )
1315 , DECODE(SIGN(tax_amt_funcl_curr ), -1, tax_amt_funcl_curr * -1 , 0))), 0) DEDUCT_TAX
1316
1317 , NVL(DECODE(DECODE(JG.extract_source_ledger
1318 ,'GL',DECODE(JG.PRL_NO,'I','AP','O','AR')
1319 ,JG.extract_source_ledger)
1320 , 'AP', DECODE(SIGN(tax_amt_funcl_curr )
1321 , -1, DECODE(reporting_code
1322 , 'OFFSET', tax_amt_funcl_curr * -1
1323 , DECODE(trx_line_class
1324 , 'AP_CREDIT_MEMO',tax_amt_funcl_curr *-1
1325 , 'AP_DEBIT_MEMO',tax_amt_funcl_curr * -1,0))
1326 , DECODE(trx_line_class
1327 , 'AP_CREDIT_MEMO' ,tax_amt_funcl_curr
1328 , 'AP_DEBIT_MEMO',tax_amt_funcl_curr,0))
1329 , 'AR', DECODE(reporting_code
1330 , 'OFFSET',DECODE(SIGN(tax_amt_funcl_curr ), -1,tax_amt* -1 )
1331 , DECODE(SIGN(tax_amt_funcl_curr ), 1, tax_amt_funcl_curr , 0))), 0) CLEAR_TAX
1332
1333 , JG.trx_line_class TRX_LINE_CLASS
1334 , JG.tax_rate_code TAX_RATE_CODE
1335 , JG.trx_id TRX_ID
1336 , JG.tax_recoverable_flag TAX_RECOVERABLE_FLAG
1337 , JG.trx_number TRX_NUMBER
1338 , DECODE(JG.application_id, 200,'I', 222,'O',
1339 101,JG.prl_no) TAX_CLASS
1340 ,JG.extract_source_ledger EXTRACT_SOURCE_LEDGER
1341 ,JG.reporting_code REPORTING_CODE
1342 FROM jg_zz_vat_trx_details JG
1343 , jg_zz_vat_rep_status JZVRS
1344 , jg_zz_vat_box_allocs AL
1345 WHERE decode(JG.extract_source_ledger,'AP',NVL(JG.tax_recoverable_flag,'N'),'Y') ='Y' -- Bug 5561879
1346 AND JZVRS.reporting_status_id = JG.reporting_status_id
1347 AND AL.vat_transaction_id = JG.vat_transaction_id
1348 AND AL.period_type = 'PERIODIC'
1349 AND JZVRS.tax_calendar_period = p_period
1350 AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1351 AND JG.pt_location = p_location ;
1352
1353 l_count NUMBER := 0 ;
1354
1355 BEGIN
1356 FOR l_pt_periodic_vat IN c_pt_periodic_vat LOOP
1357 -- SUM together Taxable Amounts for this box
1358 IF v_box_amounts.EXISTS(l_pt_periodic_vat.taxable_box) THEN
1359 v_box_amounts(l_pt_periodic_vat.taxable_box).taxable_amount := NVL(v_box_amounts(l_pt_periodic_vat.taxable_box).taxable_amount, 0)
1360 + l_pt_periodic_vat.taxable_amount;
1361 ELSE
1362 v_box_amounts(l_pt_periodic_vat.taxable_box).taxable_amount := l_pt_periodic_vat.taxable_amount;
1363 v_box_amounts(l_pt_periodic_vat.taxable_box).box_type := 'TAXABLE';
1364 END IF;
1365 -- SUM together Tax Amounts for this box
1366 IF v_box_amounts.EXISTS(l_pt_periodic_vat.tax_box) THEN
1367 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;
1368 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;
1369 ELSE
1370 v_box_amounts(l_pt_periodic_vat.tax_box).deduct_tax := l_pt_periodic_vat.deduct_tax;
1371 v_box_amounts(l_pt_periodic_vat.tax_box).clear_tax := l_pt_periodic_vat.clear_tax;
1372 v_box_amounts(l_pt_periodic_vat.tax_box).box_type := 'TAX';
1373 END IF;
1374 l_data_found := 'Y';
1375
1376 IF p_debug_flag = 'Y' THEN
1377 fnd_file.put_line(fnd_file.log,'-----------------------------------');
1378 fnd_file.put_line(fnd_file.log,'---- Trans Id ' || l_pt_periodic_vat.trx_id || '-----');
1379 fnd_file.put_line(fnd_file.log,'---- Trans Number '||l_pt_periodic_vat.trx_number||'-----');
1380 fnd_file.put_line(fnd_file.log,' Tax class information => ' || l_pt_periodic_vat.tax_class);
1381 fnd_file.put_line(fnd_file.log,' Extract Source Ledger => ' ||l_pt_periodic_vat.extract_source_ledger);
1382 fnd_file.put_line(fnd_file.log,' trx_line_class => ' ||l_pt_periodic_vat.trx_line_class);
1383 fnd_file.put_line(fnd_file.log,' Reporting Code => ' ||l_pt_periodic_vat.reporting_code);
1384 fnd_file.put_line(fnd_file.log,' Trx Taxable Amount =>'|| l_pt_periodic_vat.taxable_amount);
1385 fnd_file.put_line(fnd_file.log,' Trx Deduct Tax Amount=>'|| l_pt_periodic_vat.deduct_tax);
1386 fnd_file.put_line(fnd_file.log,' Trx Clear Tax Amount =>'|| l_pt_periodic_vat.clear_tax);
1387 fnd_file.put_line(fnd_file.log,' Taxable Box => '|| l_pt_periodic_vat.taxable_box);
1388 fnd_file.put_line(fnd_file.log,' Taxable Amt => ' ||v_box_amounts(l_pt_periodic_vat.taxable_box).taxable_amount);
1389 fnd_file.put_line(fnd_file.log,' Tax Box => ' ||l_pt_periodic_vat.tax_box);
1390 fnd_file.put_line(fnd_file.log,' Deduct Amt => ' ||v_box_amounts(l_pt_periodic_vat.tax_box).deduct_tax);
1391 fnd_file.put_line(fnd_file.log,' Clear Amt => ' ||v_box_amounts(l_pt_periodic_vat.tax_box).clear_tax);
1392 fnd_file.put_line(fnd_file.log,' Tax Recoverable Flag => ' || l_pt_periodic_vat.tax_recoverable_flag);
1393 fnd_file.put_line(fnd_file.log,'-----------------------------------');
1394 END IF;
1395 END LOOP;
1396
1397 IF l_data_found = 'Y' THEN
1398 FOR i in v_box_amounts.FIRST .. v_box_amounts.LAST LOOP
1399
1400 IF v_box_amounts.EXISTS(i) THEN
1401 INSERT INTO jg_zz_vat_trx_gt(
1402 jg_info_n1,
1403 jg_info_n2,
1404 jg_info_n3,
1405 jg_info_n4,
1406 jg_info_v1
1407 )
1408 VALUES(
1409 i,
1410 v_box_amounts(i).taxable_amount,
1411 v_box_amounts(i).deduct_tax,
1412 v_box_amounts(i).clear_tax,
1413 v_box_amounts(i).box_type
1414 );
1415 END IF;
1416 END LOOP;
1417 END IF;
1418
1419 SELECT count(*) into l_count from jg_zz_vat_trx_gt ;
1420 IF p_debug_flag = 'Y' THEN
1421 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_count );
1422 END IF ;
1423
1424 EXCEPTION
1425 WHEN OTHERS THEN
1426 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.jeptpvat ' || SUBSTR(SQLERRM,1,200));
1427 RAISE;
1428 END jeptpvat;
1429
1430 --
1431 -- +======================================================================+
1432 -- Name: JEITPSSR
1433 --
1434 -- Description: This procedure used by the Extract when the Concurrent
1435 -- Program 'Italian Payables Summary VAT Report' is run.
1436 --
1437 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
1438 -- P_PERIOD => Tax Calendar Period
1439 -- P_VAR_ON_PURCHASES => Variation on Purchases
1440 -- P_VAR_ON_SALES => Variationt on Sales
1441 -- +======================================================================+
1442 --
1443 PROCEDURE jeitpssr(p_vat_rep_entity_id IN NUMBER
1444 ,p_period IN VARCHAR2
1445 ,p_var_on_purchases IN NUMBER
1446 ,p_var_on_sales IN NUMBER
1447 ,x_err_msg OUT NOCOPY VARCHAR2)
1448 IS
1449 CURSOR c_italian_vat IS
1450 SELECT
1451 tax_rate_code TAX_RATE_CODE
1452 , tax_rate_code_description VAT_CODE_DESC
1453 , trx_id TRX_ID
1454 , DECODE(extract_source_ledger,'AP',taxable_amt*(tax_recovery_rate/100),taxable_amt) TAXABLE_AMT
1455 , tax_amt TAX_AMT
1456 /****************************************************
1457 Re ordered the query to keep it in sync with the Insert query used
1458 below
1459 Bug 7355610 START
1460 ****************************************************
1461 , DECODE(extract_source_ledger,'AP',taxable_amt_funcl_curr*(tax_recovery_rate/100),taxable_amt_funcl_curr) TAXABLE_AMT_FUNCL_CURR
1462 , tax_amt_funcl_curr TAX_AMT_FUNCL_CURR
1463 ***************************************************/
1464 , tax_amt_funcl_curr TAX_AMT_FUNCL_CURR
1465 , DECODE(extract_source_ledger,'AP',taxable_amt_funcl_curr*(tax_recovery_rate/100),taxable_amt_funcl_curr) TAXABLE_AMT_FUNCL_CURR
1466 /**************************************************
1467 Bug 7355610 END
1468 **************************************************/
1469 , doc_seq_id DOC_SEQ_ID
1470 , extract_source_ledger EXTRACT_SOURCE_LEDGER
1471 , trx_line_type TRX_LINE_TYPE
1472 , accounting_date ACCOUNTING_DATE
1473 -- Bug 6238170 Start
1474 --, tax_type_code TAX_TYPE_CODE
1475 , reporting_code REPORTING_CODE
1476 -- Bug 6238170 End
1477 , gl_transfer_flag GL_TRANSFER_FLAG
1478 , tax_rate_register_type_code TAX_RATE_REGISTER_TYPE_CODE
1479 , trx_currency_code TRX_CURRENCY_CODE
1480 , tax_recovery_rate TAX_RECOVERY_RATE
1481 , tax_recoverable_flag TAX_RECOVERABLE_FLAG
1482 , tax_rate_id TAX_RATE_ID
1483 , trx_number TRX_NUMBER
1484 , DECODE(credit_balance_amt,NULL,0,credit_balance_amt) CREDIT_BALANCE_AMT
1485 , assessable_value ASSESSABLE_VALUE
1486 , offset_tax_rate_code OFFSET_TAX_RATE_CODE
1487 , offset_flag OFFSET_FLAG
1488 , applied_to_trx_id APPLIED_TO_TRX_ID -- bug 12691340
1489 , JZVRS.mapping_vat_rep_entity_id MAPPING_VAT_REP_ENTITY_ID
1490 FROM
1491 jg_zz_vat_trx_details JG
1492 , jg_zz_vat_rep_status JZVRS
1493 WHERE JZVRS.reporting_status_id = JG.reporting_status_id
1494 AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1495 AND JZVRS.tax_calendar_period = p_period ;
1496
1497 --Bug:8347134 Start
1498 CURSOR c_get_last_process_date IS
1499 SELECT
1500 max(JZVRS.period_end_date)
1501 FROM
1502 jg_zz_vat_rep_status JZVRS
1503 WHERE
1504 JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1505 AND JZVRS.period_end_date <
1506 (SELECT max(JZVRS1.period_end_date)
1507 FROM jg_zz_vat_rep_status JZVRS1
1508 WHERE JZVRS1.tax_calendar_period = p_period
1509 AND JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
1510 --AND JZVRS1.final_reporting_status_flag = 'S'
1511 )
1512 AND (JZVRS.final_reporting_status_flag = 'S')
1513 ;
1514
1515 CURSOR c_get_balance ( pn_last_process_date DATE) IS
1516 SELECT
1517 JZVRS.CREDIT_BALANCE_AMT
1518 FROM jg_zz_vat_rep_status JZVRS
1519 WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
1520 AND JZVRS.tax_calendar_period =( SELECT JZVRS1.tax_calendar_period
1521 FROM jg_zz_vat_rep_status JZVRS1
1522 WHERE
1523 JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
1524 AND TRUNC(JZVRS1.period_end_date) = pn_last_process_date
1525 AND ROWNUM =1)
1526 AND ROWNUM = 1;
1527 LAST_PROCESS_DATE DATE;
1528 CARRY_OVER NUMBER;
1529 --BUG8347134 End
1530
1531 l_count NUMBER := 0 ;
1532 BEGIN
1533
1534 FOR l_it_vat IN c_italian_vat LOOP
1535 INSERT INTO jg_zz_vat_trx_gt
1536 (
1537 jg_info_v1
1538 ,jg_info_v2
1539 ,jg_info_n1
1540 ,jg_info_n2 --TAXABLE_AMT_FUNCL_CURR
1541 ,jg_info_n3
1542 ,jg_info_n4
1543 ,jg_info_n5 --TAXABLE_AMT
1544 ,jg_info_n6
1545 ,jg_info_v3 --extract_source_ledger
1546 ,jg_info_v4 --trx_line_type
1547 ,jg_info_v5
1548 ,jg_info_v6 --reporting_code
1549 -- ,jg_info_d1 --bug5573113
1550 ,jg_info_v7 --tax_rate_register_type_code
1551 ,jg_info_v8
1552 ,jg_info_n7
1553 ,jg_info_v9 --tax_recoverable_flag
1554 ,jg_info_n9
1555 ,jg_info_v10
1556 ,jg_info_n10
1557 ,jg_info_n11
1558 ,jg_info_v12 --offset_tax_rate_code
1559 ,jg_info_v13 --offset_flag
1560 ,jg_info_n12 -- applied_to_trx_id -- bug 12691340
1561 ,jg_info_n13 --mapping_vat_rep_entity_id
1562 )
1563 VALUES
1564 (l_it_vat.tax_rate_code
1565 ,l_it_vat.vat_code_desc
1566 ,l_it_vat.trx_id
1567 ,l_it_vat.taxable_amt_funcl_curr --l_it_vat.taxable_amt bug8587526
1568 /******************************************
1569 Reordered the way of inserting into the temp table as it was not
1570 in sync with the Query used to fetch the values in data definition
1571 jgzzsummaryall.xml
1572 Bug 7355610 START
1573 ,l_it_vat.taxable_amt_funcl_curr
1574 ,l_it_vat.tax_amt
1575 ,l_it_vat.tax_amt_funcl_curr
1576 *****************************************/
1577 ,l_it_vat.tax_amt
1578 ,l_it_vat.tax_amt_funcl_curr
1579 ,l_it_vat.taxable_amt --l_it_vat.taxable_amt_funcl_curr bug8587526
1580 /***************************************
1581 Bug 7355610 END
1582 ****************************************/
1583 ,l_it_vat.doc_seq_id
1584 ,l_it_vat.extract_source_ledger
1585 ,l_it_vat.trx_line_type
1586 ,l_it_vat.accounting_date
1587 -- Bug 6238170 Start
1588 --,l_it_vat.tax_type_code
1589 ,l_it_vat.reporting_code
1590 -- Bug 6238170 End
1591 --,l_it_vat.gl_transfer_flag --bug5573113
1592 ,l_it_vat.tax_rate_register_type_code
1593 ,l_it_vat.trx_currency_code
1594 ,l_it_vat.tax_recovery_rate
1595 ,l_it_vat.tax_recoverable_flag
1596 ,l_it_vat.tax_rate_id
1597 ,l_it_vat.trx_number
1598 ,l_it_vat.credit_balance_amt
1599 ,l_it_vat.assessable_value
1600 ,l_it_vat.offset_tax_rate_code
1601 ,l_it_vat.offset_flag
1602 ,l_it_vat.applied_to_trx_id -- bug 12691340
1603 ,l_it_vat.MAPPING_VAT_REP_ENTITY_ID
1604 );
1605 END LOOP;
1606
1607 --BUG8347134 Start
1608 --OPEN CURSOR
1609 OPEN c_get_last_process_date;
1610 FETCH c_get_last_process_date INTO LAST_PROCESS_DATE;
1611 CLOSE c_get_last_process_date;
1612
1613 OPEN c_get_balance(LAST_PROCESS_DATE);
1614 FETCH c_get_balance INTO CARRY_OVER;
1615 CLOSE c_get_balance;
1616
1617 UPDATE jg_zz_vat_trx_gt
1618 SET jg_info_n10 = CARRY_OVER;
1619 --BUG8347134 end
1620
1621 SELECT count(*) into l_count from jg_zz_vat_trx_gt ;
1622 IF p_debug_flag = 'Y' THEN
1623 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_count );
1624 END IF ;
1625
1626 EXCEPTION
1627 WHEN OTHERS THEN
1628 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.jeitpssr ' || SUBSTR(SQLERRM,1,200));
1629 RAISE;
1630 END jeitpssr;
1631
1632 -- ER 9155483
1633 -- +======================================================================+
1634 -- Name: JEILR835
1635 --
1636 -- Description: This procedure is used by the Extract when the Concurrent
1637 -- Program 'Israeli VAT 835 Report' is run.
1638 --
1639 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
1640 -- P_VAT_REP_ENTITY_ID2 => VAT Reporting Entity ID2
1641 -- P_VAT_REP_ENTITY_ID3 => VAT Reporting Entity ID3
1642 -- P_VAT_REP_ENTITY_ID4 => VAT Reporting Entity ID4
1643 -- P_VAT_REP_ENTITY_ID5 => VAT Reporting Entity ID5
1644 -- P_VAT_REP_ENTITY_ID6 => VAT Reporting Entity ID6
1645 -- P_VAT_REP_ENTITY_ID7 => VAT Reporting Entity ID7
1646 -- P_VAT_REP_ENTITY_ID8 => VAT Reporting Entity ID8
1647 -- P_VAT_REP_ENTITY_ID9 => VAT Reporting Entity ID9
1648 -- P_VAT_REP_ENTITY_ID10 => VAT Reporting Entity ID10
1649 -- P_ICHUD_OSKIM_NUM => Ichud Oskim Number
1650 -- P_PERIOD => Tax Calendar Period
1651 -- P_VAT_TRX_TYPE => VAT Transaction Type
1652 -- P_REPORT_NAME => JEILR835
1653 -- P_OPERATION_CODE => Operation Code (Default = 1)
1654 -- +======================================================================+
1655 --
1656 PROCEDURE jeilr835(p_vat_rep_entity_id IN NUMBER
1657 ,p_vat_rep_entity_id2 IN NUMBER
1658 ,p_vat_rep_entity_id3 IN NUMBER
1659 ,p_vat_rep_entity_id4 IN NUMBER
1660 ,p_vat_rep_entity_id5 IN NUMBER
1661 ,p_vat_rep_entity_id6 IN NUMBER
1662 ,p_vat_rep_entity_id7 IN NUMBER
1663 ,p_vat_rep_entity_id8 IN NUMBER
1664 ,p_vat_rep_entity_id9 IN NUMBER
1665 ,p_vat_rep_entity_id10 IN NUMBER
1666 ,p_ichud_oskim_num IN NUMBER
1667 ,p_period IN VARCHAR2
1668 ,p_vat_trx_type IN VARCHAR2
1669 ,p_report_name IN VARCHAR2
1670 ,p_operation_code IN VARCHAR2
1671 ,x_err_msg OUT NOCOPY VARCHAR2)
1672 IS
1673
1674 CURSOR q_ap_invoices IS
1675 SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY
1676 ,1 COUNT_STD_INV, DECODE(JZVTD.REPORTING_CODE, 'VAT-C', 'C','VAT-P','P','VAT-H','H','T') CLASS
1677 ,JZVTD.TRX_ID INV_ID_STANDARD
1678 ,NVL(AP.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP
1679 ,JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) INV_NUMBER_STANDARD
1680 ,TO_CHAR(JZVTD.TRX_DATE,'RRRRMMDD') INV_DATE_STANDARD
1681 ,JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS
1682 ,JZVTD.BILLING_TP_NUMBER VEN_NUM_STANDARD
1683 ,NVL(LPAD(SUBSTR(NVL(JZVTD.BILLING_TP_SITE_TAX_REG_NUM, JZVTD.BILLING_TP_TAX_REG_NUM),1,9),9,'0'),'000000000') TAX_REG_NUM_STANDARD
1684 ,SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) VAT_AMOUNT_STANDARD
1685 ,SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT_STANDARD
1686 FROM
1687 JG_ZZ_VAT_TRX_DETAILS JZVTD
1688 ,JG_ZZ_VAT_REP_STATUS JZVRS
1689 ,AP_INVOICES_ALL AP
1690 WHERE
1691 JZVRS.VAT_REPORTING_ENTITY_ID in
1692 (p_vat_rep_entity_id, p_vat_rep_entity_id2,
1693 p_vat_rep_entity_id3, p_vat_rep_entity_id4,
1694 p_vat_rep_entity_id5, p_vat_rep_entity_id6,
1695 p_vat_rep_entity_id7, p_vat_rep_entity_id8,
1696 p_vat_rep_entity_id9, p_vat_rep_entity_id10)
1697 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
1698 AND JZVRS.SOURCE = 'AP'
1699 AND JZVRS.TAX_CALENDAR_PERIOD = p_period
1700 AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = 'TAX'
1701 AND JZVTD.REPORTING_CODE IN ('VAT-A','VAT-S','VAT-C', 'VAT-P','VAT-H')
1702 AND (p_vat_trx_type IS NULL OR
1703 JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
1704 AND AP.INVOICE_ID = JZVTD.TRX_ID
1705 GROUP BY
1706 JZVRS.VAT_REPORTING_ENTITY_ID,
1707 DECODE(JZVTD.REPORTING_CODE, 'VAT-C', 'C','VAT-P','P','VAT-H','H','T')
1708 ,JZVTD.TRX_ID
1709 ,NVL(AP.GLOBAL_ATTRIBUTE1,'0000')
1710 ,JZVTD.TRX_NUMBER
1711 ,JZVTD.TRX_DATE
1712 ,JZVTD.BILLING_TP_NUMBER
1713 ,NVL(LPAD(SUBSTR(NVL(JZVTD.BILLING_TP_SITE_TAX_REG_NUM,JZVTD.BILLING_TP_TAX_REG_NUM),1,9),9,'0'),'000000000')
1714 ,JZVTD.TRX_LINE_CLASS
1715 HAVING
1716 SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) <> 0
1717 ORDER BY
1718 JZVRS.VAT_REPORTING_ENTITY_ID,
1719 JZVTD.TRX_DATE;
1720
1721 CURSOR q_rshimon_imports IS
1722 SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1723 1 COUNT_IMPORT, JZVTD.TRX_ID INV_ID_IMPORT,
1724 NVL(APINV.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
1725 JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) INV_NUMBER_IMPORT,
1726 JZVTD.TRX_DATE INV_DATE_IMPORT,
1727 JZVTD.BILLING_TP_NUMBER VEN_NUMBER,
1728 NVL(LPAD(SUBSTR(NVL(JZVTD.BILLING_TP_SITE_TAX_REG_NUM, JZVTD.BILLING_TP_TAX_REG_NUM), 1, 9), 9, '0'), '000000000') TAX_REG_NUM_IMPORT,
1729 NVL(APINVL.GLOBAL_ATTRIBUTE14, APINV.INVOICE_NUM) IMP_DOC_NUM_IMPORT,
1730 TO_CHAR(DECODE(LENGTH(NVL(APINVL.GLOBAL_ATTRIBUTE13,'A')),
1731 1,JZVTD.TRX_DATE,
1732 19,TO_DATE(APINVL.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD HH24:MI:SS'),
1733 TO_DATE(APINVL.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR')),'RRRRMMDD') IMP_DOC_DATE_IMPORT,
1734 SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) VAT_AMOUNT_IMPORT,
1735 SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT_IMPORT
1736 FROM JG_ZZ_VAT_TRX_DETAILS JZVTD,
1737 JG_ZZ_VAT_REP_STATUS JZVRS,
1738 AP_INVOICES_ALL APINV,
1739 AP_INVOICE_LINES_ALL APINVL,
1740 ZX_LINES ZXL
1741 WHERE JZVRS.SOURCE = 'AP'
1742 AND JZVRS.VAT_REPORTING_ENTITY_ID in
1743 (p_vat_rep_entity_id, p_vat_rep_entity_id2,
1744 p_vat_rep_entity_id3, p_vat_rep_entity_id4,
1745 p_vat_rep_entity_id5, p_vat_rep_entity_id6,
1746 p_vat_rep_entity_id7, p_vat_rep_entity_id8,
1747 p_vat_rep_entity_id9, p_vat_rep_entity_id10)
1748 AND JZVRS.TAX_CALENDAR_PERIOD = p_period
1749 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
1750 AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = 'TAX'
1751 AND JZVTD.REPORTING_CODE IN ('VAT-RS','VAT-RA')
1752 AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
1753 AND APINV.INVOICE_ID = JZVTD.TRX_ID
1754 AND ZXL.TRX_ID = APINV.INVOICE_ID
1755 AND ZXL.TRX_ID = APINVL.INVOICE_ID(+)
1756 AND ZXL.TAX_LINE_ID = JZVTD.TAX_LINE_ID
1757 AND ZXL.SUMMARY_TAX_LINE_ID = APINVL.SUMMARY_TAX_LINE_ID(+)
1758 AND APINVL.LINE_TYPE_LOOKUP_CODE(+) = 'TAX'
1759 GROUP BY JZVRS.VAT_REPORTING_ENTITY_ID,
1760 JZVTD.TRX_ID ,
1761 NVL(APINV.GLOBAL_ATTRIBUTE1,'0000'),
1762 JZVTD.TRX_NUMBER ,
1763 JZVTD.TRX_DATE ,
1764 JZVTD.BILLING_TP_NUMBER ,
1765 NVL(LPAD(SUBSTR(NVL(JZVTD.BILLING_TP_SITE_TAX_REG_NUM, JZVTD.BILLING_TP_TAX_REG_NUM), 1, 9), 9, '0'), '000000000') ,
1766 NVL(APINVL.GLOBAL_ATTRIBUTE14, APINV.INVOICE_NUM) ,
1767 TO_CHAR(DECODE(LENGTH(NVL(APINVL.GLOBAL_ATTRIBUTE13,'A')),
1768 1,JZVTD.TRX_DATE,
1769 19,TO_DATE(APINVL.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD HH24:MI:SS'),
1770 TO_DATE(APINVL.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR')),'RRRRMMDD')
1771 HAVING
1772 SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) <> 0
1773 ORDER BY JZVRS.VAT_REPORTING_ENTITY_ID,
1774 JZVTD.TRX_DATE;
1775
1776 CURSOR q_petty_cash_summary IS
1777 SELECT VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1778 1 COUNT_PETTY,
1779 REFERENCE_GROUP,
1780 COUNT(TRX_ID) NO_OF_INVOICES,
1781 SUM(VAT_AMOUNT) VAT_AMOUNT_PETTY_CASH,
1782 SUM(TAXABLE_AMOUNT) TAXABLE_AMOUNT_PETTY_CASH
1783 FROM( SELECT JZVRS.VAT_REPORTING_ENTITY_ID,
1784 NVL(APINV.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
1785 JZVTD.TRX_ID TRX_ID,
1786 SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) VAT_AMOUNT,
1787 SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
1788 FROM JG_ZZ_VAT_TRX_DETAILS JZVTD,
1789 JG_ZZ_VAT_REP_STATUS JZVRS,
1790 AP_INVOICES_ALL APINV
1791 WHERE JZVRS.SOURCE = 'AP'
1792 AND JZVRS.VAT_REPORTING_ENTITY_ID in
1793 (p_vat_rep_entity_id, p_vat_rep_entity_id2,
1794 p_vat_rep_entity_id3, p_vat_rep_entity_id4,
1795 p_vat_rep_entity_id5, p_vat_rep_entity_id6,
1796 p_vat_rep_entity_id7, p_vat_rep_entity_id8,
1797 p_vat_rep_entity_id9, p_vat_rep_entity_id10)
1798 AND JZVTD.IL_VAT_REP_STATUS_ID in (
1799 SELECT JZVRS.REPORTING_STATUS_ID
1800 FROM JG_ZZ_VAT_REP_STATUS JZVRS
1801 WHERE JZVRS.SOURCE='AP'
1802 AND JZVRS.VAT_REPORTING_ENTITY_ID in (
1803 p_vat_rep_entity_id, p_vat_rep_entity_id2,
1804 p_vat_rep_entity_id3, p_vat_rep_entity_id4,
1805 p_vat_rep_entity_id5, p_vat_rep_entity_id6,
1806 p_vat_rep_entity_id7, p_vat_rep_entity_id8,
1807 p_vat_rep_entity_id9, p_vat_rep_entity_id10)
1808 AND JZVRS.TAX_CALENDAR_PERIOD = P_PERIOD)
1809 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
1810 AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = 'TAX'
1811 AND JZVTD.REPORTING_CODE IN('VAT-KS','VAT-KA')
1812 AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
1813 AND APINV.INVOICE_ID = JZVTD.TRX_ID
1814 GROUP BY JZVRS.VAT_REPORTING_ENTITY_ID,
1815 NVL(APINV.GLOBAL_ATTRIBUTE1,'0000'),JZVTD.TRX_ID
1816 HAVING
1817 SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) <> 0)
1818 GROUP by VAT_REPORTING_ENTITY_ID, REFERENCE_GROUP
1819 ORDER by VAT_REPORTING_ENTITY_ID, REFERENCE_GROUP;
1820
1821 CURSOR q_ar_transactions IS
1822 SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1823 1 COUNT_TRX,
1824 JZVTD.REPORTING_CODE REPORTING_CODE,
1825 JZVTD.TRX_ID TRX_ID,
1826 NVL(AR.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
1827 JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) TRX_NUMBER,
1828 TO_CHAR(DECODE(JZVTD.REPORTING_CODE, 'VAT-Y',DECODE(LENGTH(NVL(ARL.GLOBAL_ATTRIBUTE2,'A')),
1829 1,jzvtd.TRX_DATE,
1830 19,TO_DATE(ARL.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),
1831 TO_DATE(ARL.global_attribute2, 'DD-MM-RRRR')),jzvtd.trx_date),'RRRRMMDD') TRX_DATE,
1832 JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS,
1833 DECODE(JZVTD.REPORTING_CODE, 'VAT-Y',NVL(ARL.GLOBAL_ATTRIBUTE1,'999999999'),JZVTD.BILLING_TP_TAXPAYER_ID) TAXPAYER_ID,
1834 DECODE(JZVTD.REPORTING_CODE, 'VAT-Y', 0, SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT))) VAT_AMOUNT,
1835 SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
1836 FROM
1837 JG_ZZ_VAT_TRX_DETAILS JZVTD,
1838 JG_ZZ_VAT_REP_STATUS JZVRS,
1839 RA_CUSTOMER_TRX_ALL AR,
1840 RA_CUSTOMER_TRX_LINES_ALL ARL
1841 WHERE
1842 JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
1843 AND JZVRS.SOURCE = 'AR'
1844 AND JZVRS.VAT_REPORTING_ENTITY_ID in
1845 (p_vat_rep_entity_id, p_vat_rep_entity_id2,
1846 p_vat_rep_entity_id3, p_vat_rep_entity_id4,
1847 p_vat_rep_entity_id5, p_vat_rep_entity_id6,
1848 p_vat_rep_entity_id7, p_vat_rep_entity_id8,
1849 p_vat_rep_entity_id9, p_vat_rep_entity_id10)
1850 AND JZVRS.TAX_CALENDAR_PERIOD = p_period
1851 AND JZVTD.REPORTING_CODE IN ('VAT-M','VAT-I','VAT-Y')
1852 AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
1853 AND AR.CUSTOMER_TRX_ID = JZVTD.TRX_ID
1854 AND ARL.CUSTOMER_TRX_ID = JZVTD.TRX_ID
1855 AND ARL.CUSTOMER_TRX_LINE_ID = JZVTD.TRX_LINE_ID
1856 GROUP BY
1857 JZVRS.VAT_REPORTING_ENTITY_ID,
1858 JZVTD.REPORTING_CODE,
1859 JZVTD.TRX_ID,
1860 NVL(AR.GLOBAL_ATTRIBUTE1,'0000'),
1861 JZVTD.TRX_NUMBER,
1862 TO_CHAR(DECODE(JZVTD.REPORTING_CODE, 'VAT-Y',DECODE(LENGTH(NVL(ARL.GLOBAL_ATTRIBUTE2,'A')),
1863 1,jzvtd.TRX_DATE,
1864 19,TO_DATE(ARL.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),
1865 TO_DATE(ARL.global_attribute2, 'DD-MM-RRRR')),jzvtd.trx_date),'RRRRMMDD'),
1866 JZVTD.TRX_LINE_CLASS,
1867 DECODE(JZVTD.REPORTING_CODE, 'VAT-Y',NVL(ARL.GLOBAL_ATTRIBUTE1,'999999999'),JZVTD.BILLING_TP_TAXPAYER_ID)
1868 ORDER BY 1,2,6;
1869
1870 CURSOR q_std_trx_registered_cust IS
1871 SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1872 1 COUNT_REG_TRX,
1873 JZVTD.REPORTING_CODE REPORTING_CODE,
1874 JZVTD.TRX_ID TRX_ID,
1875 NVL(AR.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
1876 JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) TRX_NUMBER,
1877 TO_CHAR(JZVTD.TRX_DATE,'RRRRMMDD') TRX_DATE,
1878 JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS,
1879 JZVTD.BILLING_TP_TAXPAYER_ID TAXPAYER_ID,
1880 DECODE(JZVTD.REPORTING_CODE, 'VAT-S', SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT)) , 0) VAT_AMOUNT,
1881 SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
1882 FROM
1883 JG_ZZ_VAT_TRX_DETAILS JZVTD,
1884 JG_ZZ_VAT_REP_STATUS JZVRS,
1885 RA_CUSTOMER_TRX_ALL AR
1886 WHERE
1887 JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
1888 AND JZVRS.SOURCE = 'AR'
1889 AND JZVRS.VAT_REPORTING_ENTITY_ID in
1890 (p_vat_rep_entity_id, p_vat_rep_entity_id2,
1891 p_vat_rep_entity_id3, p_vat_rep_entity_id4,
1892 p_vat_rep_entity_id5, p_vat_rep_entity_id6,
1893 p_vat_rep_entity_id7, p_vat_rep_entity_id8,
1894 p_vat_rep_entity_id9, p_vat_rep_entity_id10)
1895 AND JZVRS.TAX_CALENDAR_PERIOD = p_period
1896 AND JZVTD.REPORTING_CODE IN ('VAT-S','VAT-0','VAT-EXEMPT','IL_VAT_EXEMPT')
1897 AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
1898 AND AR.CUSTOMER_TRX_ID = JZVTD.TRX_ID
1899 AND JZVTD.BILLING_TP_TAXPAYER_ID IS NOT NULL
1900 GROUP BY
1901 JZVRS.VAT_REPORTING_ENTITY_ID,
1902 JZVTD.REPORTING_CODE,
1903 JZVTD.TRX_ID,
1904 NVL(AR.GLOBAL_ATTRIBUTE1,'0000'),
1905 JZVTD.TRX_NUMBER,
1906 JZVTD.TRX_DATE,
1907 JZVTD.TRX_LINE_CLASS,
1908 JZVTD.BILLING_TP_TAXPAYER_ID
1909 HAVING
1910 DECODE(JZVTD.REPORTING_CODE, 'VAT-S', SIGN(l_vat_aggregation_limit_amt - SUM(ABS(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)))) , 0) <> 1
1911 ORDER BY 1,2,6;
1912
1913 CURSOR q_std_trx_unknown_cust IS
1914 SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1915 1 COUNT_UNKNOWN_TRX,
1916 JZVTD.REPORTING_CODE REPORTING_CODE,
1917 JZVTD.TRX_ID TRX_ID,
1918 NVL(AR.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
1919 JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) TRX_NUMBER,
1920 TO_CHAR(JZVTD.TRX_DATE,'RRRRMMDD') TRX_DATE,
1921 JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS,
1922 DECODE(JZVTD.REPORTING_CODE, 'VAT-L', SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT)),
1923 'VAT-S', SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT)), 0) VAT_AMOUNT,
1924 SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
1925 FROM
1926 JG_ZZ_VAT_TRX_DETAILS JZVTD,
1927 JG_ZZ_VAT_REP_STATUS JZVRS,
1928 RA_CUSTOMER_TRX_ALL AR
1929 WHERE
1930 JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
1931 AND JZVRS.SOURCE = 'AR'
1932 AND JZVRS.VAT_REPORTING_ENTITY_ID in
1933 (p_vat_rep_entity_id, p_vat_rep_entity_id2,
1934 p_vat_rep_entity_id3, p_vat_rep_entity_id4,
1935 p_vat_rep_entity_id5, p_vat_rep_entity_id6,
1936 p_vat_rep_entity_id7, p_vat_rep_entity_id8,
1937 p_vat_rep_entity_id9, p_vat_rep_entity_id10)
1938 AND JZVRS.TAX_CALENDAR_PERIOD = p_period
1939 AND JZVTD.REPORTING_CODE IN ('VAT-S', 'VAT-L','VAT-0','VAT-EXEMPT','IL_VAT_EXEMPT')
1940 AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
1941 AND AR.CUSTOMER_TRX_ID = JZVTD.TRX_ID
1942 AND ((JZVTD.REPORTING_CODE = 'VAT-L') OR (JZVTD.BILLING_TP_TAXPAYER_ID IS NULL AND JZVTD.REPORTING_CODE <> 'VAT-L'))
1943 GROUP BY
1944 JZVRS.VAT_REPORTING_ENTITY_ID,
1945 JZVTD.REPORTING_CODE,
1946 JZVTD.TRX_ID,
1947 NVL(AR.GLOBAL_ATTRIBUTE1,'0000'),
1948 JZVTD.TRX_NUMBER,
1949 JZVTD.TRX_DATE,
1950 JZVTD.TRX_LINE_CLASS
1951 HAVING
1952 DECODE(JZVTD.REPORTING_CODE, 'VAT-L', SIGN(l_vat_aggregation_limit_amt - SUM(ABS(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)))),
1953 'VAT-S', SIGN(l_vat_aggregation_limit_amt - SUM(ABS(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)))) , 0) <> 1
1954 ORDER BY
1955 JZVRS.VAT_REPORTING_ENTITY_ID,
1956 JZVTD.REPORTING_CODE,
1957 JZVTD.TRX_NUMBER;
1958
1959 CURSOR q_std_trxs_summary IS
1960 SELECT VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1961 1 COUNT_SUM_TRXS,
1962 REFERENCE_GROUP,
1963 COUNT(DISTINCT TRX_ID) NO_OF_AGGRE_INVOICES,
1964 SUM(VAT_AMOUNT) SUM_OF_VAT_AMT,
1965 SUM(TAXABLE_AMOUNT) SUM_OF_TAXABLE_AMT
1966 FROM ( SELECT JZVRS.VAT_REPORTING_ENTITY_ID,
1967 JZVTD.TRX_ID TRX_ID,
1968 NVL(AR.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
1969 JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) TRX_NUMBER,
1970 JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS,
1971 SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT)) VAT_AMOUNT,
1972 SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
1973 FROM
1974 JG_ZZ_VAT_TRX_DETAILS JZVTD,
1975 JG_ZZ_VAT_REP_STATUS JZVRS,
1976 RA_CUSTOMER_TRX_ALL AR
1977 WHERE
1978 JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
1979 AND JZVRS.SOURCE = 'AR'
1980 AND JZVRS.VAT_REPORTING_ENTITY_ID in
1981 (p_vat_rep_entity_id, p_vat_rep_entity_id2,
1982 p_vat_rep_entity_id3, p_vat_rep_entity_id4,
1983 p_vat_rep_entity_id5, p_vat_rep_entity_id6,
1984 p_vat_rep_entity_id7, p_vat_rep_entity_id8,
1985 p_vat_rep_entity_id9, p_vat_rep_entity_id10)
1986 AND JZVRS.TAX_CALENDAR_PERIOD = p_period
1987 AND JZVTD.REPORTING_CODE IN ('VAT-S','VAT-L')
1988 AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
1989 AND AR.CUSTOMER_TRX_ID = JZVTD.TRX_ID
1990 GROUP BY
1991 JZVRS.VAT_REPORTING_ENTITY_ID,
1992 JZVTD.TRX_ID,
1993 NVL(AR.GLOBAL_ATTRIBUTE1,'0000'),
1994 JZVTD.TRX_NUMBER,
1995 JZVTD.TRX_LINE_CLASS
1996 HAVING
1997 SUM(ABS(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT))) < l_vat_aggregation_limit_amt
1998 ) GROUP BY VAT_REPORTING_ENTITY_ID, REFERENCE_GROUP
1999 ORDER BY VAT_REPORTING_ENTITY_ID, REFERENCE_GROUP;
2000
2001 TYPE r_ent_list IS RECORD (ent_id number);
2002 TYPE t_ent_list IS TABLE OF r_ent_list INDEX BY BINARY_INTEGER;
2003 l_ent_list t_ent_list;
2004 var_system_date varchar2(30) := NULL;
2005 var_tax_period varchar2(30) := NULL;
2006 var_last_day_tax_period varchar2(30) := NULL;
2007 no_of_invs number := 0;
2008 no_of_imports number := 0;
2009 no_of_pettycash number := 0;
2010 no_of_trxs number := 0;
2011 no_of_regis_trxs number := 0;
2012 no_of_unknown_trxs number := 0;
2013 no_of_summary_trxs number := 0;
2014 l_rep_status number;
2015 l_ent_index number := 1;
2016 l_ent_name varchar2(80);
2017 VAT_REP_NOT_RUN exception;
2018 LOG_MESSAGE_835 varchar2(1000);
2019
2020 BEGIN
2021 -- Get the reporting status id
2022 -- QA Bug 9880383: Modified logic to check Rep Entity Id status
2023
2024 BEGIN
2025 SELECT REPORTING_STATUS_ID
2026 INTO l_rep_status
2027 FROM JG_ZZ_VAT_REP_STATUS
2028 WHERE SOURCE='AP'
2029 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
2030 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2031 EXCEPTION
2032 WHEN NO_DATA_FOUND THEN
2033 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID;
2034 l_ent_index := l_ent_index + 1;
2035 END;
2036
2037 IF P_VAT_REP_ENTITY_ID2 IS NOT NULL THEN
2038
2039 BEGIN
2040 SELECT REPORTING_STATUS_ID
2041 INTO l_rep_status
2042 FROM JG_ZZ_VAT_REP_STATUS
2043 WHERE SOURCE='AP'
2044 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID2
2045 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2046 EXCEPTION
2047 WHEN NO_DATA_FOUND THEN
2048 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID2;
2049 l_ent_index := l_ent_index + 1;
2050 END;
2051 END IF;
2052
2053 IF P_VAT_REP_ENTITY_ID3 IS NOT NULL THEN
2054
2055 BEGIN
2056 SELECT REPORTING_STATUS_ID
2057 INTO l_rep_status
2058 FROM JG_ZZ_VAT_REP_STATUS
2059 WHERE SOURCE='AP'
2060 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID3
2061 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2062 EXCEPTION
2063 WHEN NO_DATA_FOUND THEN
2064 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID3;
2065 l_ent_index := l_ent_index + 1;
2066 END;
2067 END IF;
2068
2069 IF P_VAT_REP_ENTITY_ID4 IS NOT NULL THEN
2070
2071 BEGIN
2072 SELECT REPORTING_STATUS_ID
2073 INTO l_rep_status
2074 FROM JG_ZZ_VAT_REP_STATUS
2075 WHERE SOURCE='AP'
2076 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID4
2077 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2078 EXCEPTION
2079 WHEN NO_DATA_FOUND THEN
2080 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID4;
2081 l_ent_index := l_ent_index + 1;
2082 END;
2083 END IF;
2084
2085 IF P_VAT_REP_ENTITY_ID5 IS NOT NULL THEN
2086
2087 BEGIN
2088 SELECT REPORTING_STATUS_ID
2089 INTO l_rep_status
2090 FROM JG_ZZ_VAT_REP_STATUS
2091 WHERE SOURCE='AP'
2092 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID5
2093 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2094 EXCEPTION
2095 WHEN NO_DATA_FOUND THEN
2096 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID5;
2097 l_ent_index := l_ent_index + 1;
2098 END;
2099 END IF;
2100
2101 IF P_VAT_REP_ENTITY_ID6 IS NOT NULL THEN
2102
2103 BEGIN
2104 SELECT REPORTING_STATUS_ID
2105 INTO l_rep_status
2106 FROM JG_ZZ_VAT_REP_STATUS
2107 WHERE SOURCE='AP'
2108 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID6
2109 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2110 EXCEPTION
2111 WHEN NO_DATA_FOUND THEN
2112 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID6;
2113 l_ent_index := l_ent_index + 1;
2114 END;
2115 END IF;
2116
2117 IF P_VAT_REP_ENTITY_ID7 IS NOT NULL THEN
2118
2119 BEGIN
2120 SELECT REPORTING_STATUS_ID
2121 INTO l_rep_status
2122 FROM JG_ZZ_VAT_REP_STATUS
2123 WHERE SOURCE='AP'
2124 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID7
2125 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2126 EXCEPTION
2127 WHEN NO_DATA_FOUND THEN
2128 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID7;
2129 l_ent_index := l_ent_index + 1;
2130 END;
2131 END IF;
2132
2133 IF P_VAT_REP_ENTITY_ID8 IS NOT NULL THEN
2134
2135 BEGIN
2136 SELECT REPORTING_STATUS_ID
2137 INTO l_rep_status
2138 FROM JG_ZZ_VAT_REP_STATUS
2139 WHERE SOURCE='AP'
2140 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID8
2141 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2142 EXCEPTION
2143 WHEN NO_DATA_FOUND THEN
2144 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID8;
2145 l_ent_index := l_ent_index + 1;
2146 END;
2147 END IF;
2148
2149 IF P_VAT_REP_ENTITY_ID9 IS NOT NULL THEN
2150
2151 BEGIN
2152 SELECT REPORTING_STATUS_ID
2153 INTO l_rep_status
2154 FROM JG_ZZ_VAT_REP_STATUS
2155 WHERE SOURCE='AP'
2156 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID9
2157 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2158 EXCEPTION
2159 WHEN NO_DATA_FOUND THEN
2160 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID9;
2161 l_ent_index := l_ent_index + 1;
2162 END;
2163 END IF;
2164
2165 IF P_VAT_REP_ENTITY_ID10 IS NOT NULL THEN
2166
2167 BEGIN
2168 SELECT REPORTING_STATUS_ID
2169 INTO l_rep_status
2170 FROM JG_ZZ_VAT_REP_STATUS
2171 WHERE SOURCE='AP'
2172 AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID10
2173 AND TAX_CALENDAR_PERIOD = P_PERIOD;
2174 EXCEPTION
2175 WHEN NO_DATA_FOUND THEN
2176 l_ent_list(l_ent_index).ent_id := P_VAT_REP_ENTITY_ID10;
2177 l_ent_index := l_ent_index + 1;
2178 END;
2179 END IF;
2180
2181 IF l_ent_index > 1 THEN
2182 RAISE VAT_REP_NOT_RUN;
2183 END IF;
2184
2185 SELECT
2186 TO_CHAR(SYSDATE, 'RRRRMMDD') SYSTEM_DATE,
2187 JLMT.PERIOD_YEAR || DECODE(LENGTH(JLMT.PERIOD_NUM), 1, 0 || JLMT.PERIOD_NUM, JLMT.PERIOD_NUM) TAX_PERIOD,
2188 TO_CHAR(sta.period_end_date,'RRRRMMDD') LAST_DAY_TAX_PERIOD
2189 INTO var_system_date, var_tax_period, var_last_day_tax_period
2190 FROM
2191 JE_IL_VAT_LIMITS JLMT,
2192 JG_ZZ_VAT_REP_STATUS STA
2193 WHERE
2194 STA.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
2195 AND STA.TAX_CALENDAR_PERIOD = p_period
2196 AND JLMT.PERIOD_SET_NAME = STA.TAX_CALENDAR_NAME
2197 AND JLMT.PERIOD_NAME = p_period
2198 AND ROWNUM = 1;
2199
2200 FOR l_ap_invoices IN q_ap_invoices LOOP
2201 INSERT INTO jg_zz_vat_trx_gt
2202 ( -- Data mapping used by ALL CURSORS:
2203 -- ================================
2204 jg_info_v1 -- First position in template
2205 ,jg_info_v2 -- Tax Registration Id
2206 ,jg_info_v3 -- Trx Date
2207 ,jg_info_v4 -- Reference Group
2208 ,jg_info_v5 -- Trx Num
2209 ,jg_info_v6 -- Tax Amount
2210 ,jg_info_v7 -- Taxable amount sign
2211 ,jg_info_v8 -- Taxable Amount value
2212 ,jg_info_v35 -- Used to order records
2213 ,jg_info_v39 -- Report data identifier
2214 ,jg_info_v40 -- Used to order trx types in output
2215 ,jg_info_n29 -- Used by template to order by Rep Entity
2216 ,jg_info_n30 -- Rep Entity Id
2217 )
2218 VALUES
2219 (
2220 l_ap_invoices.class
2221 ,lpad(l_ap_invoices.tax_reg_num_standard,9,'0')
2222 ,l_ap_invoices.inv_date_standard
2223 ,l_ap_invoices.reference_group
2224 ,l_ap_invoices.inv_number_standard
2225 ,lpad(round(abs(l_ap_invoices.vat_amount_standard)),9,'0')
2226 ,decode(sign(l_ap_invoices.taxable_amount_standard),'-1','-','+')
2227 ,lpad(round(abs(nvl(l_ap_invoices.taxable_amount_standard,0))),10,'0')
2228 ,l_ap_invoices.inv_date_standard
2229 ,'JEILR835'
2230 ,decode(l_ap_invoices.class,'T','10Q_AP_INVOICES',
2231 'C','20Q_AP_INVOICES',
2232 'H','30Q_AP_INVOICES',
2233 '40Q_AP_INVOICES')
2234 ,decode(l_ap_invoices.reporting_entity, p_vat_rep_entity_id, 1,
2235 p_vat_rep_entity_id2, 2,
2236 p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
2237 p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
2238 p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
2239 p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
2240 ,l_ap_invoices.reporting_entity
2241 );
2242 no_of_invs := no_of_invs + 1;
2243 END LOOP;
2244
2245 FOR l_rshimon_imports IN q_rshimon_imports LOOP
2246 INSERT INTO jg_zz_vat_trx_gt
2247 (
2248 jg_info_v1
2249 ,jg_info_v2
2250 ,jg_info_v3
2251 ,jg_info_v4
2252 ,jg_info_v5
2253 ,jg_info_v6
2254 ,jg_info_v7
2255 ,jg_info_v8
2256 ,jg_info_v35
2257 ,jg_info_v39
2258 ,jg_info_v40
2259 ,jg_info_n29
2260 ,jg_info_n30
2261 )
2262 VALUES
2263 ('R'
2264 ,lpad(l_rshimon_imports.imp_doc_num_import,9,'0')
2265 ,to_char(l_rshimon_imports.imp_doc_date_import)
2266 ,lpad(l_rshimon_imports.reference_group,4,'0')
2267 ,l_rshimon_imports.inv_number_import
2268 ,lpad(round(abs(l_rshimon_imports.vat_amount_import)),9,'0')
2269 ,decode(sign(l_rshimon_imports.taxable_amount_import),'-1','-','+')
2270 ,lpad(round(abs(l_rshimon_imports.taxable_amount_import)),10,'0')
2271 ,to_char(l_rshimon_imports.inv_date_import,'RRRRMMDD')
2272 ,'JEILR835'
2273 ,'50Q_RSHIMON_IMPORTS'
2274 ,decode(l_rshimon_imports.reporting_entity, p_vat_rep_entity_id, 1,
2275 p_vat_rep_entity_id2, 2,
2276 p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
2277 p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
2278 p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
2279 p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
2280 ,l_rshimon_imports.reporting_entity
2281 );
2282 no_of_imports := no_of_imports + 1;
2283 END LOOP;
2284
2285 FOR l_petty_cash_summary IN q_petty_cash_summary LOOP
2286 INSERT INTO jg_zz_vat_trx_gt
2287 (
2288 jg_info_v1
2289 ,jg_info_v2
2290 ,jg_info_v3
2291 ,jg_info_v4
2292 ,jg_info_v5
2293 ,jg_info_v6
2294 ,jg_info_v7
2295 ,jg_info_v8
2296 ,jg_info_v35
2297 ,jg_info_v39
2298 ,jg_info_v40
2299 ,jg_info_n29
2300 ,jg_info_n30
2301 )
2302 VALUES
2303 ('K'
2304 ,'000000000'
2305 ,var_last_day_tax_period
2306 ,l_petty_cash_summary.reference_group
2307 ,l_petty_cash_summary.no_of_invoices
2308 ,lpad(round(nvl(l_petty_cash_summary.vat_amount_petty_cash,0)),9,'0')
2309 ,decode(sign(l_petty_cash_summary.taxable_amount_petty_cash),'-1','-','+')
2310 ,lpad(round(abs(l_petty_cash_summary.taxable_amount_petty_cash)),10,'0')
2311 ,l_petty_cash_summary.reference_group
2312 ,'JEILR835'
2313 ,'60Q_PETTY_CASH_SUMMARY'
2314 ,decode(l_petty_cash_summary.reporting_entity, p_vat_rep_entity_id, 1,
2315 p_vat_rep_entity_id2, 2,
2316 p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
2317 p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
2318 p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
2319 p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
2320 ,l_petty_cash_summary.reporting_entity
2321 );
2322 no_of_pettycash := no_of_pettycash + 1;
2323 END LOOP;
2324
2325 FOR l_ar_transactions IN q_ar_transactions LOOP
2326 INSERT INTO jg_zz_vat_trx_gt
2327 (
2328 jg_info_v1
2329 ,jg_info_v2
2330 ,jg_info_v3
2331 ,jg_info_v4
2332 ,jg_info_v5
2333 ,jg_info_v6
2334 ,jg_info_v7
2335 ,jg_info_v8
2336 ,jg_info_v35
2337 ,jg_info_v39
2338 ,jg_info_v40
2339 ,jg_info_n29
2340 ,jg_info_n30
2341 )
2342 VALUES
2343 (
2344 decode(l_ar_transactions.reporting_code,'VAT-M','M',
2345 'VAT-Y','Y',
2346 'VAT-I','I')
2347 ,lpad(l_ar_transactions.taxpayer_id,9,'0')
2348 ,l_ar_transactions.trx_date
2349 ,l_ar_transactions.reference_group
2350 ,l_ar_transactions.trx_number
2351 ,lpad(round(abs(l_ar_transactions.vat_amount)),9,'0')
2352 ,decode(sign(l_ar_transactions.taxable_amount),'-1','-','+')
2353 ,lpad(round(abs(l_ar_transactions.taxable_amount)),10,'0')
2354 ,lpad(l_ar_transactions.trx_number,9,'0')
2355 ,'JEILR835'
2356 ,decode(l_ar_transactions.reporting_code,'VAT-M','92Q_AR_TRANSACTIONS',
2357 'VAT-I','94Q_AR_TRANSACTIONS',
2358 'VAT-Y','96Q_AR_TRANSACTIONS')
2359 ,decode(l_ar_transactions.reporting_entity, p_vat_rep_entity_id, 1,
2360 p_vat_rep_entity_id2, 2,
2361 p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
2362 p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
2363 p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
2364 p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
2365 ,l_ar_transactions.reporting_entity
2366 );
2367 no_of_trxs := no_of_trxs + 1;
2368 END LOOP;
2369
2370 FOR l_std_trx_registered_cust IN q_std_trx_registered_cust LOOP
2371 INSERT INTO jg_zz_vat_trx_gt
2372 (
2373 jg_info_v1
2374 ,jg_info_v2
2375 ,jg_info_v3
2376 ,jg_info_v4
2377 ,jg_info_v5
2378 ,jg_info_v6
2379 ,jg_info_v7
2380 ,jg_info_v8
2381 ,jg_info_v35
2382 ,jg_info_v39
2383 ,jg_info_v40
2384 ,jg_info_n29
2385 ,jg_info_n30
2386 )
2387 VALUES
2388 ('S'
2389 ,lpad(l_std_trx_registered_cust.taxpayer_id,9,'0')
2390 ,l_std_trx_registered_cust.trx_date
2391 ,l_std_trx_registered_cust.reference_group
2392 ,l_std_trx_registered_cust.trx_number
2393 ,lpad(round(abs(l_std_trx_registered_cust.vat_amount)),9,'0')
2394 ,decode(sign(l_std_trx_registered_cust.taxable_amount),'-1','-','+')
2395 ,lpad(round(abs(l_std_trx_registered_cust.taxable_amount)),10,'0')
2396 ,lpad(l_std_trx_registered_cust.trx_number,9,'0')
2397 ,'JEILR835'
2398 ,'70Q_STD_TRX_REGISTERED_CUST'
2399 ,decode(l_std_trx_registered_cust.reporting_entity, p_vat_rep_entity_id, 1,
2400 p_vat_rep_entity_id2, 2,
2401 p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
2402 p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
2403 p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
2404 p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
2405 ,l_std_trx_registered_cust.reporting_entity
2406 );
2407 no_of_regis_trxs := no_of_regis_trxs + 1;
2408 END LOOP;
2409
2410 FOR l_std_trx_unknown_cust IN q_std_trx_unknown_cust LOOP
2411 INSERT INTO jg_zz_vat_trx_gt
2412 (
2413 jg_info_v1
2414 ,jg_info_v2
2415 ,jg_info_v3
2416 ,jg_info_v4
2417 ,jg_info_v5
2418 ,jg_info_v6
2419 ,jg_info_v7
2420 ,jg_info_v8
2421 ,jg_info_v35
2422 ,jg_info_v39
2423 ,jg_info_v40
2424 ,jg_info_n29
2425 ,jg_info_n30
2426 )
2427 VALUES
2428 ('L'
2429 ,'000000000'
2430 ,l_std_trx_unknown_cust.trx_date
2431 ,l_std_trx_unknown_cust.reference_group
2432 ,l_std_trx_unknown_cust.trx_number
2433 ,lpad(round(abs(l_std_trx_unknown_cust.vat_amount)),9,'0')
2434 ,decode(sign(l_std_trx_unknown_cust.taxable_amount),'-1','-','+')
2435 ,lpad(round(abs(l_std_trx_unknown_cust.taxable_amount)),10,'0')
2436 ,l_std_trx_unknown_cust.reporting_code||lpad(l_std_trx_unknown_cust.trx_number,9,'0')
2437 ,'JEILR835'
2438 ,'90Q_STD_TRX_UNKNOWN_CUST'
2439 ,decode(l_std_trx_unknown_cust.reporting_entity, p_vat_rep_entity_id, 1,
2440 p_vat_rep_entity_id2, 2,
2441 p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
2442 p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
2443 p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
2444 p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
2445 ,l_std_trx_unknown_cust.reporting_entity
2446 );
2447 no_of_unknown_trxs := no_of_unknown_trxs + 1;
2448 END LOOP;
2449
2450 FOR l_std_trxs_summary IN q_std_trxs_summary LOOP
2451 INSERT INTO jg_zz_vat_trx_gt
2452 (
2453 jg_info_v1
2454 ,jg_info_v2
2455 ,jg_info_v3
2456 ,jg_info_v4
2457 ,jg_info_v5
2458 ,jg_info_v6
2459 ,jg_info_v7
2460 ,jg_info_v8
2461 ,jg_info_v35
2462 ,jg_info_v39
2463 ,jg_info_v40
2464 ,jg_info_n29
2465 ,jg_info_n30
2466 )
2467 VALUES
2468 ('L'
2469 ,'000000000'
2470 ,var_last_day_tax_period
2471 ,l_std_trxs_summary.reference_group
2472 ,l_std_trxs_summary.no_of_aggre_invoices
2473 ,lpad(round(abs(l_std_trxs_summary.sum_of_vat_amt)),9,'0')
2474 ,decode(sign(l_std_trxs_summary.sum_of_taxable_amt),'-1','-','+')
2475 ,lpad(round(abs(l_std_trxs_summary.sum_of_taxable_amt)),10,'0')
2476 ,l_std_trxs_summary.reference_group
2477 ,'JEILR835'
2478 ,'80Q_STD_TRXS_SUMMARY'
2479 ,decode(l_std_trxs_summary.reporting_entity, p_vat_rep_entity_id, 1,
2480 p_vat_rep_entity_id2, 2,
2481 p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
2482 p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
2483 p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
2484 p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
2485 ,l_std_trxs_summary.reporting_entity
2486 );
2487 no_of_summary_trxs := no_of_summary_trxs + 1;
2488 END LOOP;
2489
2490 -- Last insert to store number of each type of trx created in above cursors
2491
2492 INSERT INTO jg_zz_vat_trx_gt
2493 (
2494 jg_info_n1
2495 ,jg_info_n2
2496 ,jg_info_n3
2497 ,jg_info_n4
2498 ,jg_info_n5
2499 ,jg_info_n6
2500 ,jg_info_n7
2501 ,jg_info_v39
2502 )
2503 VALUES
2504 (no_of_invs
2505 ,no_of_imports
2506 ,no_of_pettycash
2507 ,no_of_trxs
2508 ,no_of_regis_trxs
2509 ,no_of_unknown_trxs
2510 ,no_of_summary_trxs
2511 ,'JEILR835-TOTALS'
2512 );
2513
2514 EXCEPTION
2515 WHEN VAT_REP_NOT_RUN THEN
2516 fnd_message.set_name('JE', 'JE_IL_VAT_REP_NOT_RUN');
2517 fnd_message.set_token('P_PERIOD', P_PERIOD);
2518 LOG_MESSAGE_835 := fnd_message.get;
2519 fnd_file.put_line(fnd_file.log,LOG_MESSAGE_835);
2520 FOR i IN 1..l_ent_index-1 LOOP
2521 select ENTITY_IDENTIFIER
2522 into l_ent_name
2523 from jg_zz_vat_rep_entities
2524 where VAT_REPORTING_ENTITY_ID = l_ent_list(i).ent_id;
2525 fnd_file.put_line(fnd_file.log,'>>>>>'||l_ent_name);
2526 END LOOP;
2527 raise_application_error(-20010,LOG_MESSAGE_835);
2528 WHEN OTHERS THEN
2529 fnd_file.put_line(fnd_file.log,'Error in Procedure JG_ZZ_SUMMARY_ALL_PKG.jeilr835 ' || SUBSTR(SQLERRM,1,200));
2530 RAISE;
2531 END jeilr835;
2532
2533 FUNCTION a_real_tax_amount(p_payment_amt IN NUMBER
2534 , p_tax_amount IN NUMBER
2535 , p_cust_trx_id IN NUMBER)
2536 RETURN NUMBER IS
2537 tax_amount NUMBER;
2538 l_total_invoice NUMBER;
2539 BEGIN
2540 IF p_debug_flag = 'Y' THEN
2541 fnd_file.put_line(fnd_file.log,' p_payment_amt =>'||p_payment_amt);
2542 END IF;
2543 IF p_payment_amt = 0 THEN
2544 /****************************************
2545 tax_amount := tax_amount;
2546 Commented the above code added the below for Bug 7355610 Start
2547 ****************************************/
2548 tax_amount := p_tax_amount;
2549 /***************************************
2550 Bug 7355610 changes End
2551 ***************************************/
2552 ELSE
2553 l_total_invoice := a_total_invoices(p_cust_trx_id);
2554 IF p_func_curr = 'ITL' THEN
2555 tax_amount := p_payment_amt*(p_tax_amount/nvl(l_total_invoice, 1))+.49;
2556 ELSE
2557 tax_amount := p_payment_amt*(p_tax_amount/nvl(l_total_invoice, 1));
2558 END IF;
2559 END IF;
2560 IF p_debug_flag = 'Y' THEN
2561 fnd_file.put_line(fnd_file.log,' tax_amount =>'||tax_amount);
2562 END IF;
2563 RETURN(tax_amount);
2564 EXCEPTION
2565 WHEN OTHERS THEN
2566 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.a_real_tax_amount ' || SUBSTR(SQLERRM,1,200));
2567 RAISE;
2568 END a_real_tax_amount;
2569
2570 FUNCTION a_real_invoice_amount(p_payment_amt IN NUMBER
2571 , p_invoice_amount IN NUMBER
2572 , p_cust_trx_id IN NUMBER)
2573 RETURN NUMBER IS
2574 invoice_amount NUMBER;
2575 l_total_invoice NUMBER;
2576 BEGIN
2577
2578 IF p_payment_amt = 0 THEN
2579 invoice_amount := p_invoice_amount;
2580 ELSE
2581 l_total_invoice := a_total_invoices(p_cust_trx_id);
2582 IF p_func_curr = 'ITL' THEN
2583 invoice_amount := p_payment_amt*(p_invoice_amount/NVL(l_total_invoice, 1))+.49;
2584 ELSE
2585 invoice_amount := p_payment_amt*(p_invoice_amount/NVL(l_total_invoice, 1));
2586 END IF;
2587 END IF;
2588 RETURN(invoice_amount);
2589 EXCEPTION
2590 WHEN OTHERS THEN
2591 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.a_real_invoice_amount ' || SUBSTR(SQLERRM,1,200));
2592 RAISE;
2593 END a_real_invoice_amount;
2594
2595 FUNCTION a_total_invoices(p_cust_trx_id IN NUMBER)
2596 RETURN NUMBER IS
2597 invoice_values NUMBER;
2598 BEGIN
2599 IF p_func_curr = 'ITL' THEN
2600 SELECT SUM(tax_amt_funcl_curr)
2601 INTO invoice_values
2602 FROM jg_zz_vat_trx_details
2603 WHERE trx_id = p_cust_trx_id
2604 AND application_id = 200
2605 AND event_class_code = 'PURCHASE_TRANSACTION'
2606 AND entity_code = 'AP_INVOICES';
2607 ELSE
2608 SELECT SUM(tax_amt)
2609 INTO invoice_values
2610 FROM jg_zz_vat_trx_details
2611 WHERE trx_id = p_cust_trx_id
2612 AND application_id = 200
2613 AND event_class_code = 'PURCHASE_TRANSACTION'
2614 AND entity_code = 'AP_INVOICES';
2615 END IF;
2616 RETURN(invoice_values);
2617 EXCEPTION
2618 WHEN OTHERS THEN
2619 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.a_total_invoices ' || SUBSTR(SQLERRM,1,200));
2620 RAISE;
2621 END a_total_invoices;
2622
2623 FUNCTION get_report_format
2624 RETURN VARCHAR2 IS
2625 l_detail_summary VARCHAR2(30);
2626 BEGIN
2627 IF p_report_name = 'JEBEVA06' THEN
2628 SELECT meaning
2629 INTO l_detail_summary
2630 FROM fnd_lookups
2631 WHERE lookup_type = 'JEBE_REPORT_FORMAT'
2632 AND lookup_code = P_REPORT_FORMAT
2633 AND p_report_name = 'JEBEVA06';
2634 RETURN l_detail_summary;
2635 ELSE
2636 RETURN NULL;
2637 END IF;
2638 EXCEPTION
2639 WHEN NO_DATA_FOUND then
2640 fnd_file.put_line(fnd_file.log,'Error while fetching Meaning for Lookup Type JEBE_REPORT_FORMAT' || SUBSTR(SQLERRM,1,200));
2641 l_detail_summary := P_REPORT_FORMAT;
2642 RETURN l_detail_summary;
2643 WHEN OTHERS then
2644 fnd_file.put_line(fnd_file.log,'Error while fetching Meaning for Lookup Type JEBE_REPORT_FORMAT' || SUBSTR(SQLERRM,1,200));
2645 l_detail_summary := P_REPORT_FORMAT;
2646 RETURN l_detail_summary;
2647 END get_report_format;
2648
2649 FUNCTION get_location_name
2650 RETURN VARCHAR2 IS
2651 l_location_name VARCHAR2(100);
2652 BEGIN
2653 IF p_report_name = 'JEPTAVAT' OR p_report_name = 'JEPTPVAT' THEN
2654 SELECT FFV.description
2655 INTO l_location_name
2656 FROM fnd_flex_values_vl FFV
2657 , fnd_flex_value_sets FFVS
2658 WHERE FFV.flex_value_set_id = FFVS.flex_value_set_id
2659 AND FFVS.flex_value_set_name = 'JEPT_TAX_LOCATION'
2660 AND FFV.flex_value = P_LOCATION;
2661 ELSE
2662 l_location_name := NULL;
2663 END IF;
2664 RETURN l_location_name;
2665 EXCEPTION
2666 WHEN NO_DATA_FOUND THEN
2667 fnd_file.put_line(fnd_file.log,'Location Name for Location Code ' || P_LOCATION || ' is not found');
2668 l_location_name := P_LOCATION;
2669 RETURN l_location_name;
2670 WHEN OTHERS THEN
2671 fnd_file.put_line(fnd_file.log,'Error while fetching Location Name' || SUBSTR(SQLERRM,1,200));
2672 l_location_name := P_LOCATION;
2673 RETURN l_location_name;
2674 END;
2675
2676 FUNCTION get_start_exempt_limit
2677 RETURN NUMBER
2678 IS
2679 l_starting_limit NUMBER;
2680 l_period_date date;
2681 BEGIN
2682 -- Get the date for the period
2683 SELECT DISTINCT RPS.period_start_date
2684 INTO l_period_date
2685 FROM JG_ZZ_VAT_REP_STATUS RPS
2686 WHERE RPS.TAX_CALENDAR_PERIOD = P_PERIOD
2687 AND RPS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID;
2688
2689 l_starting_limit := 0;
2690 SELECT NVL(jiy.adjusted_limit_amount, 0)
2691 INTO l_starting_limit
2692 FROM je_it_year_ex_limit JIY,
2693 fnd_lookups fl
2694 WHERE fl.lookup_type = 'JEIT_MONTH'
2695 AND JIY.month = fl.lookup_code
2696 AND JIY.year = to_char(l_period_date,'RRRR')
2697 AND JIY.month = to_char(l_period_date,'MM')
2698 AND JIY.legal_entity_id = P_LEGAL_ENTITY_ID ;
2699 fnd_file.put_line(fnd_file.log,'Starting Exemption Limit for Period => : '||l_starting_limit);
2700 RETURN l_starting_limit;
2701 EXCEPTION
2702 WHEN NO_DATA_FOUND THEN
2703 fnd_file.put_line(fnd_file.log,'Exeption Amount for beginning of Period not found' || SUBSTR(SQLERRM,1,200));
2704 RETURN 0;
2705 WHEN OTHERS THEN
2706 fnd_file.put_line(fnd_file.log,'Error while fetching Available Exemption at Beginning of Period ' || SUBSTR(SQLERRM,1,200));
2707 RETURN 0;
2708 END get_start_exempt_limit;
2709
2710 FUNCTION get_adjustment_exempt_limit
2711 RETURN NUMBER
2712 IS
2713 l_adjustment_limit NUMBER;
2714 l_period_date date;
2715 BEGIN
2716 -- Get the date for the period
2717 SELECT DISTINCT RPS.period_start_date
2718 INTO l_period_date
2719 FROM JG_ZZ_VAT_REP_STATUS RPS
2720 WHERE RPS.TAX_CALENDAR_PERIOD = P_PERIOD
2721 AND RPS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID;
2722
2723 l_adjustment_limit := 0;
2724 SELECT NVL(JIY.adjusted_limit_amount, 0)
2725 INTO l_adjustment_limit
2726 FROM je_it_year_ex_limit JIY
2727 WHERE JIY.year = to_char(l_period_date,'RRRR')
2728 AND JIY.month = to_char(l_period_date,'MM')
2729 AND JIY.legal_entity_id = P_LEGAL_ENTITY_ID ;
2730 fnd_file.put_line(fnd_file.log,'Adjustment Amount =>: '||l_adjustment_limit);
2731 RETURN l_adjustment_limit;
2732 EXCEPTION
2733 WHEN NO_DATA_FOUND THEN
2734 fnd_file.put_line(fnd_file.log,'Adjustments Yearly Exemption Limit not found' || SUBSTR(SQLERRM,1,200));
2735 RETURN 0;
2736 WHEN OTHERS THEN
2737 fnd_file.put_line(fnd_file.log,'Error while getting Adjustment to Yearly Exemption Limit ' || SUBSTR(SQLERRM,1,200));
2738 RETURN 0;
2739 END get_adjustment_exempt_limit;
2740
2741 FUNCTION get_old_debit_vat
2742 RETURN NUMBER
2743 IS
2744 BEGIN
2745 -- This Code will return the Previous Period VAT Credit Amount
2746 -- This is used by the XML Element OLD_DEBIT_VAT
2747 RETURN 0;
2748 EXCEPTION
2749 WHEN OTHERS THEN
2750 fnd_file.put_line(fnd_file.log,'Error while getting Previous Period VAT Credit ' || SUBSTR(SQLERRM,1,200));
2751 RETURN 0;
2752 END get_old_debit_vat;
2753
2754 FUNCTION JEITPSSR_AMOUNT_TO_PAY(A_TOT_TOT_TAX_AMT_SUM IN NUMBER,
2755 B_TOT_TAX_AMT_SUM IN NUMBER,
2756 D_TOT_TAX_AMT_SUM IN NUMBER,
2757 D_VAT_REC_SUM IN NUMBER,
2758 C_VAT_REC_SUM IN NUMBER,
2759 C_VAT_NON_RECC_SUM IN NUMBER)
2760 RETURN NUMBER IS
2761
2762 AMOUNT_TO_PAY NUMBER;
2763 CARRY_OVER NUMBER;
2764 FINAL_FLAG VARCHAR2(1);
2765 LAST_PROCESS_DATE DATE;
2766 INVALID_CARRY_OVER EXCEPTION;
2767 l_period_start_date DATE;
2768 LOG_MESSAGE VARCHAR2(1000);
2769
2770 /* Bug:8237932 This cursor is no longer needed to get the
2771 credit_balance_amount,flag_status and last_processed_date.
2772 CURSOR c_get_details IS
2773 SELECT
2774 JZVRS.CREDIT_BALANCE_AMT,
2775 JZVRS.FINAL_REPORTING_STATUS_FLAG,
2776 jg_zz_vat_rep_utility.get_last_processed_date(p_vat_rep_entity_id,'ALL','FINAL REPORTING') LAST_PROCESS_DATE
2777 FROM jg_zz_vat_rep_status JZVRS
2778 WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
2779 AND JZVRS.tax_calendar_period = p_period
2780 AND ROWNUM = 1;
2781 Bug:8237932*/
2782
2783 CURSOR c_get_period_start_date IS
2784 SELECT jg_info_d2 FROM jg_zz_vat_trx_gt WHERE jg_info_v30 = 'H';
2785
2786 /*Added three new cursors:
2787 c_get_last_process_date ,c_get_balance,c_get_flag*/
2788 --Bug:8237932
2789 CURSOR c_get_last_process_date IS
2790 SELECT
2791 max(JZVRS.period_end_date)
2792
2793 FROM
2794 jg_zz_vat_rep_status JZVRS
2795 WHERE
2796 JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
2797 AND JZVRS.period_end_date <
2798 (SELECT max(JZVRS1.period_end_date)
2799 FROM jg_zz_vat_rep_status JZVRS1
2800 WHERE JZVRS1.tax_calendar_period = p_period
2801 AND JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
2802 --AND JZVRS1.final_reporting_status_flag = 'S'
2803 )
2804 AND JZVRS.final_reporting_status_flag = 'S'
2805 AND JZVRS.tax_calendar_period <> p_period;
2806
2807 CURSOR c_get_balance ( pn_last_process_date DATE) IS
2808 SELECT
2809 JZVRS.CREDIT_BALANCE_AMT
2810 FROM jg_zz_vat_rep_status JZVRS
2811 WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
2812 AND JZVRS.tax_calendar_period =( SELECT JZVRS1.tax_calendar_period
2813 FROM jg_zz_vat_rep_status JZVRS1
2814 WHERE
2815 JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
2816 AND TRUNC(JZVRS1.period_end_date) = pn_last_process_date
2817 AND ROWNUM =1)
2818 AND ROWNUM = 1;
2819
2820 CURSOR c_get_flag IS
2821 SELECT
2822 JZVRS.FINAL_REPORTING_STATUS_FLAG
2823 FROM jg_zz_vat_rep_status JZVRS
2824 WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
2825 AND JZVRS.tax_calendar_period = p_period
2826 AND ROWNUM = 1;
2827
2828 --Bug:8237932
2829 BEGIN
2830
2831 /*Bug:8237932
2832 OPEN c_get_details;
2833 FETCH c_get_details INTO CARRY_OVER,FINAL_FLAG,LAST_PROCESS_DATE;
2834 CLOSE c_get_details;
2835 */
2836 LAST_PROCESS_DATE := NULL;
2837 --OPEN CURSOR
2838 OPEN c_get_last_process_date;
2839 FETCH c_get_last_process_date INTO LAST_PROCESS_DATE;
2840 CLOSE c_get_last_process_date;
2841
2842 OPEN c_get_balance(LAST_PROCESS_DATE);
2843 FETCH c_get_balance INTO CARRY_OVER;
2844 CLOSE c_get_balance;
2845
2846 OPEN c_get_flag;
2847 FETCH c_get_flag INTO FINAL_FLAG;
2848 CLOSE c_get_flag;
2849 --Bug:8237932
2850
2851 OPEN c_get_period_start_date;
2852 FETCH c_get_period_start_date INTO l_period_start_date;
2853 CLOSE c_get_period_start_date;
2854
2855 fnd_file.put_line(fnd_file.log,'In jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY ..FINAL_FLAG:'||FINAL_FLAG);
2856 fnd_file.put_line(fnd_file.log,'In jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY ..CARRY_OVER :'||TO_CHAR(CARRY_OVER));
2857 fnd_file.put_line(fnd_file.log,'In jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY ..LAST_PROCESS_DATE :'||TO_CHAR(LAST_PROCESS_DATE));
2858
2859 IF FINAL_FLAG = 'S' THEN
2860 IF CARRY_OVER IS NULL THEN
2861 IF LAST_PROCESS_DATE IS NULL THEN
2862 CARRY_OVER := 0;
2863 ELSE
2864 AMOUNT_TO_PAY := 0;
2865
2866 fnd_message.set_name('JE', 'JE_IT_VAT_AP_SUM_CR_BALANCE');
2867 fnd_message.set_token('DATE', LAST_PROCESS_DATE);
2868 LOG_MESSAGE := fnd_message.get;
2869
2870 RAISE INVALID_CARRY_OVER;
2871 END IF;
2872 ELSIF (LAST_PROCESS_DATE+1) <> l_period_start_date THEN
2873
2874 fnd_message.set_name('JE', 'JE_IT_VAT_GAP_IN_FINAL_PROCESS');
2875 fnd_message.set_token('DATE', LAST_PROCESS_DATE);
2876 LOG_MESSAGE := fnd_message.get;
2877
2878 RAISE INVALID_CARRY_OVER;
2879 END IF;
2880 END IF;
2881
2882 AMOUNT_TO_PAY := (A_TOT_TOT_TAX_AMT_SUM + B_TOT_TAX_AMT_SUM ) - D_TOT_TAX_AMT_SUM + D_VAT_REC_SUM
2883 + P_VAR_ON_SALES - C_VAT_REC_SUM - P_VAR_ON_PURCHASES + NVL(CARRY_OVER,0);
2884
2885
2886 /*Commented this part of code for bug 7633948.This functionaliy is implemented in
2887 JEITPSSR_AMOUNT_TO_PAY_UPDATE function
2888 IF FINAL_FLAG = 'S' THEN
2889
2890 IF AMOUNT_TO_PAY >= 0 THEN
2891 CARRY_OVER := 0;
2892 ELSE
2893 CARRY_OVER := AMOUNT_TO_PAY;
2894 END IF;
2895
2896 IF TEMP_FLAG = 0 THEN
2897
2898 UPDATE jg_zz_vat_rep_status SET CREDIT_BALANCE_AMT=CARRY_OVER
2899 WHERE vat_reporting_entity_id = p_vat_rep_entity_id
2900 AND tax_calendar_period = p_period;
2901
2902 TEMP_FLAG := 1;
2903
2904 END IF;
2905
2906 END IF;for bug 7633948*/
2907 fnd_file.put_line(fnd_file.log,'In jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY ' || to_char(AMOUNT_TO_PAY));
2908 RETURN(AMOUNT_TO_PAY);
2909
2910 EXCEPTION
2911 WHEN INVALID_CARRY_OVER THEN
2912 fnd_file.put_line(fnd_file.log,LOG_MESSAGE);
2913 raise_application_error(-20010,LOG_MESSAGE);
2914 WHEN OTHERS THEN
2915 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY ' || SUBSTR(SQLERRM,1,200));
2916 raise_application_error(-20011,'Error in Procedure jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY ' || SUBSTR(SQLERRM,1,200));
2917 END JEITPSSR_AMOUNT_TO_PAY;
2918
2919 /****Added below function JEITPSSR_AMOUNT_TO_PAY_UPDATE for bug 7633948***/
2920 FUNCTION JEITPSSR_AMOUNT_TO_PAY_UPDATE(AMOUNT_TO_PAY IN NUMBER)
2921 RETURN BOOLEAN IS
2922
2923 FINAL_FLAG VARCHAR2(1);
2924 CARRY_OVER NUMBER;
2925 --INVALID_CARRY_OVER EXCEPTION;
2926 --LOG_MESSAGE VARCHAR2(240);
2927 CURSOR c_get_details IS
2928 SELECT
2929 JZVRS.FINAL_REPORTING_STATUS_FLAG
2930 FROM jg_zz_vat_rep_status JZVRS
2931 WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
2932 AND JZVRS.tax_calendar_period = p_period
2933 AND ROWNUM = 1;
2934 BEGIN
2935 IF p_report_name = 'JEITPSSR' THEN
2936 fnd_file.put_line(fnd_file.log,'Called JEITPSSR_AMOUNT_TO_PAY_UPDATE function');
2937 OPEN c_get_details;
2938 FETCH c_get_details INTO FINAL_FLAG;
2939 CLOSE c_get_details;
2940 fnd_file.put_line(fnd_file.log,'In JEITPSSR_AMOUNT_TO_PAY_UPDATE FINAL_FLAG:'||FINAL_FLAG);
2941 IF FINAL_FLAG='S' THEN
2942
2943 IF AMOUNT_TO_PAY >= 0 THEN
2944 CARRY_OVER := 0;
2945 ELSE
2946 CARRY_OVER := AMOUNT_TO_PAY;
2947 END IF;
2948 fnd_file.put_line(fnd_file.log,'In JEITPSSR_AMOUNT_TO_PAY_UPDATE TEMP_FLAG:'||TO_CHAR(TEMP_FLAG));
2949 fnd_file.put_line(fnd_file.log,'In JEITPSSR_AMOUNT_TO_PAY_UPDATE AMOUNT_TO_PAY:'||TO_CHAR(AMOUNT_TO_PAY));
2950 fnd_file.put_line(fnd_file.log,'In JEITPSSR_AMOUNT_TO_PAY_UPDATE CARRY_OVER:'||TO_CHAR(CARRY_OVER));
2951
2952 IF TEMP_FLAG = 0 THEN
2953
2954 UPDATE jg_zz_vat_rep_status SET CREDIT_BALANCE_AMT=CARRY_OVER
2955 WHERE vat_reporting_entity_id = p_vat_rep_entity_id
2956 AND tax_calendar_period = p_period;
2957
2958 TEMP_FLAG := 1;
2959 END IF;
2960 END IF;
2961 END IF;
2962 RETURN (TRUE);
2963 EXCEPTION
2964 -- WHEN INVALID_CARRY_OVER THEN
2965 -- fnd_file.put_line(fnd_file.log,LOG_MESSAGE);
2966 WHEN OTHERS THEN
2967 fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY_UPDATE ' || SUBSTR(SQLERRM,1,200));
2968 RETURN (FALSE);
2969 END JEITPSSR_AMOUNT_TO_PAY_UPDATE;
2970
2971 /******Added the below function JEILR835 INV_NUM for Bug 7427956*********/
2972 FUNCTION JEILR835_INV_NUM ( p_str VARCHAR2) RETURN VARCHAR2 IS
2973 l_str VARCHAR2(100);
2974 l_char VARCHAR2(2);
2975 l_len NUMBER;
2976 l_start NUMBER :=1;
2977 BEGIN
2978 l_len:=NVL(LENGTH(p_str), 0);
2979 l_str :='';
2980 FOR l_start IN 1..l_len LOOP
2981 l_char:= SUBSTRB(p_str,l_start,1);
2982 IF (l_char IN ('1','2','3','4','5','6','7','8','9','0')) THEN
2983 l_str:=l_str||l_char;
2984 END IF;
2985 END LOOP;
2986 l_len:=NVL(LENGTH(l_str), 0);
2987 IF (l_len > 9) THEN
2988 l_start:=l_len-8;
2989 l_str:=substrb(l_str,l_start,9);
2990 END IF;
2991 RETURN (l_str);
2992 END JEILR835_INV_NUM;
2993
2994 END JG_ZZ_SUMMARY_ALL_PKG;