[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_PTCE_DT_PKG
Source
1 PACKAGE BODY JG_ZZ_PTCE_DT_PKG AS
2 /* $Header: jgzztoapntrlb.pls 120.16.12020000.3 2012/11/08 12:53:29 abhijsar ship $*/
3
4 function BeforeReport return boolean is
5
6 CURSOR c_get_le_and_period_dates
7 is
8 SELECT nvl(cfg.legal_entity_id,cfgd.legal_entity_id)
9 ,nvl(cfg.tax_registration_number,cfgd.tax_registration_number) repent_trn
10 ,min(glp.start_date)
11 ,max(glp.end_date)
12 ,nvl(cfg.entity_identifier,cfgd.entity_identifier) entity_identifier
13 FROM jg_zz_vat_rep_entities cfg
14 ,jg_zz_vat_rep_entities cfgd
15 ,gl_periods glp
16 WHERE cfg.vat_reporting_entity_id = p_reporting_entity_id
17 and (
18 ( cfg.entity_type_code = 'ACCOUNTING'
19 and cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
20 )
21 or
22 ( cfg.entity_type_code = 'LEGAL'
23 and cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
24 )
25 )
26 AND glp.period_set_name = nvl(cfg.tax_calendar_name,cfgd.tax_calendar_name)
27 AND glp.period_year = p_fiscal_year
28 GROUP BY nvl(cfg.legal_entity_id,cfgd.legal_entity_id)
29 ,nvl(cfg.tax_registration_number,cfgd.tax_registration_number)
30 ,nvl(cfg.entity_identifier,cfgd.entity_identifier);
31
32 CURSOR c_currency_vat_reg_num
33 IS
34 SELECT gllev.currency_code
35 ,gl.name
36 ,fsp.vat_registration_num
37 ,fsp.vat_country_code
38 FROM gl_ledger_le_v gllev
39 ,gl_ledgers gl
40 ,financials_system_parameters fsp
41 WHERE gllev.ledger_category_code='PRIMARY'
42 AND gllev.legal_entity_id = gn_legal_entity_id
43 AND gl.ledger_id = gllev.ledger_id
44 AND fsp.set_of_books_id = gllev.ledger_id ;
45
46 CURSOR c_get_gl_name IS
47 SELECT gl.name
48 FROM gl_ledger_le_v glle,
49 gl_ledgers gl
50 WHERE
51 glle.legal_entity_id = gn_legal_entity_id
52 AND gl.ledger_id = glle.ledger_id ;
53
54 CURSOR c_get_rep_entity_info IS
55 SELECT xfpiv.registration_number
56 , xfpiv.name
57 , xfpiv.address_line_1
58 , xfpiv.address_line_2
59 , xfpiv.address_line_3
60 , xfpiv.town_or_city
61 , xfpiv.postal_code
62 , xfpiv.country
63 , hzp.primary_phone_area_code
64 ||' '|| hzp.primary_phone_country_code
65 ||' '|| hzp.primary_phone_number phone_number
66 , xlelav.city tax_office_location
67 , xlelav.address2 tax_office_number
68 , xlelav.address3 tax_office_code
69 FROM XLE_FIRSTPARTY_INFORMATION_V xfpiv
70 ,xle_registrations xle_reg
71 , hz_parties hzp
72 , xle_legalauth_v xlelav
73 WHERE xle_reg.source_id = xfpiv.legal_entity_id
74 AND xle_reg.source_table = 'XLE_ENTITY_PROFILES'
75 AND xlelav.legalauth_id = xle_reg.issuing_authority_id
76 AND xle_reg.identifying_flag = 'Y'
77 AND xfpiv.legislative_cat_code = 'INCOME_TAX'
78 AND xfpiv.legal_entity_id = gn_legal_entity_id
79 AND hzp.party_id = xlelav.party_id;
80
81 CURSOR c_generic_ap_inv_lines IS
82 SELECT
83 i.invoice_id
84 ,i.vendor_id
85 ,i.vendor_site_id
86 ,i.invoice_date
87 ,i.invoice_currency_code
88 ,i.invoice_type_lookup_code
89 ,i.legal_entity_id
90 ,i.doc_sequence_value
91 ,il.line_number
92 ,il.line_type_lookup_code
93 ,id.period_name
94 ,nvl(id.tax_code_id,get_item_tax_code_id(id.invoice_id,id.invoice_distribution_id,tax_rate_id)) tax_code_id
95 ,id.match_status_flag
96 ,id.charge_applicable_to_dist_id
97 ,id.invoice_distribution_id
98 ,id.merchant_taxpayer_id
99 ,id.line_type_lookup_code id_line_type_lookup_code
100 -- ,sum(id.stat_amount) stat_amount Commented for Bug 5750278
101 ,sum(il.assessable_value) taxable_amount --Added for 5750278
102 -- ,sum(id.amount) amount --Commented for bug 14317864
103 ,sum(nvl(id.TOTAL_DIST_AMOUNT,id.amount)) amount --Added for bug 14317864
104 -- ,sum(id.base_amount) base_amount --Commented for bug 14317864
105 -- ,sum(nvl(id.TOTAL_DIST_BASE_AMOUNT,id.base_amount)) base_amount --Added for bug 14317864 -- commented for bug 14791442
106 ,sum(decode(id.TOTAL_DIST_BASE_AMOUNT,0,id.amount,null,id.amount,id.TOTAL_DIST_BASE_AMOUNT)) base_amount -- Added for bug 14791442
107 FROM
108 ap_invoices i,
109 ap_invoice_lines il,
110 ap_invoice_distributions id,
111 gl_code_combinations gl,
112 jg_zz_vat_rep_entities repent
113 WHERE repent.vat_reporting_entity_id = p_reporting_entity_id
114 and ( ( repent.entity_type_code = 'LEGAL' AND i.legal_entity_id = gn_legal_entity_id )
115 or ( repent.entity_type_code = 'ACCOUNTING' AND repent.entity_level_code = 'LEDGER' AND
116 i.set_of_books_id = gv_ledger_id)
117 or ( repent.entity_type_code = 'ACCOUNTING' AND repent.entity_level_code = 'BSV'
118 and i.set_of_books_id = gv_ledger_id
119 and get_bsv(id.dist_code_combination_id) = gv_balancing_segment_value ) )
120 and i.invoice_id = il.invoice_id
121 and i.invoice_id = id.invoice_id
122 and il.line_number = id.invoice_line_number
123 and id.dist_code_combination_id = gl.code_combination_id
124 and ( (P_called_from = 'JEITRAVL' and to_char(i.invoice_date, 'YYYY') in
125 (to_char(gd_period_end_date, 'YYYY'),to_char(add_months(gd_period_end_date,-12),'YYYY')))
126 or (P_called_from = 'JEPTAPVR' and
127 id.accounting_date between to_date('01/01/' || to_char(p_fiscal_year),'DD/MM/YYYY')
128 and to_date('31/12/' || to_char(p_fiscal_year),'DD/MM/YYYY'))
129
130 )
131 GROUP BY
132 i.invoice_id
133 ,i.vendor_id
134 ,i.vendor_site_id
135 ,i.invoice_date
136 ,i.invoice_currency_code
137 ,i.invoice_type_lookup_code
138 ,i.legal_entity_id
139 ,i.doc_sequence_value
140 ,il.line_number
141 ,il.line_type_lookup_code
142 ,id.period_name
143 ,nvl(id.tax_code_id,get_item_tax_code_id(id.invoice_id,id.invoice_distribution_id,tax_rate_id))
144 ,id.match_status_flag
145 ,id.charge_applicable_to_dist_id
146 ,id.invoice_distribution_id
147 ,id.merchant_taxpayer_id
148 ,id.line_type_lookup_code;
149
150
151
152 CURSOR entity_details IS
153 SELECT repent.ledger_id,
154 repent.balancing_segment_value,
155 gl.CHART_OF_ACCOUNTS_ID
156 FROM jg_zz_vat_rep_entities repent
157 ,gl_ledgers gl
158 WHERE vat_reporting_entity_id = p_reporting_entity_id
159 AND gl.ledger_id = repent.ledger_id;
160
161 begin
162
163 OPEN c_get_le_and_period_dates;
164 FETCH c_get_le_and_period_dates into gn_legal_entity_id
165 ,gv_repent_trn
166 ,gd_period_start_date
167 ,gd_period_end_date
168 ,gv_entity_identifier;
169 CLOSE c_get_le_and_period_dates;
170
171 fnd_file.put_line(fnd_file.log,'*******Information*********');
172 fnd_file.put_line(fnd_file.log,' gn_legal_entity_id :'|| gn_legal_entity_id);
173 fnd_file.put_line(fnd_file.log,' gv_repent_trn :'|| gv_repent_trn);
174 fnd_file.put_line(fnd_file.log,' gd_period_start_date :'||gd_period_start_date);
175 fnd_file.put_line(fnd_file.log,' gd_period_end_date :'|| gd_period_end_date);
176 fnd_file.put_line(fnd_file.log,' gv_entity_identifier :'|| gv_entity_identifier);
177
178 OPEN c_get_rep_entity_info;
179 FETCH c_get_rep_entity_info into gv_repent_id_number
180 ,gv_repent_name
181 ,gv_repent_address_line_1
182 ,gv_repent_address_line_2
183 ,gv_repent_address_line_3
184 ,gv_repent_town_or_city
185 ,gv_repent_postal_code
186 ,gv_country
187 ,gv_repent_phone_number
188 ,gv_tax_office_location
189 ,gv_tax_office_number
190 ,gv_tax_office_code ;
191 CLOSE c_get_rep_entity_info;
192
193 fnd_file.put_line(fnd_file.log,' gv_repent_id_number :'||gv_repent_id_number);
194 fnd_file.put_line(fnd_file.log,' gv_repent_name :'|| gv_repent_name);
195 fnd_file.put_line(fnd_file.log,' gv_repent_address_line_1 :'||gv_repent_address_line_1);
196 fnd_file.put_line(fnd_file.log,' gv_repent_address_line_2 :'||gv_repent_address_line_2);
197 fnd_file.put_line(fnd_file.log,' gv_repent_address_line_3 :'||gv_repent_address_line_3);
198 fnd_file.put_line(fnd_file.log,' gv_repent_town_or_city :'||gv_repent_town_or_city);
199 fnd_file.put_line(fnd_file.log,' gv_repent_postal_code :'||gv_repent_postal_code);
200 fnd_file.put_line(fnd_file.log,' gv_country :'||gv_country);
201 fnd_file.put_line(fnd_file.log,' gv_repent_phone_number :'||gv_repent_phone_number);
202 fnd_file.put_line(fnd_file.log,' gv_tax_office_location :'||gv_tax_office_location);
203 fnd_file.put_line(fnd_file.log,' gv_tax_office_number :'||gv_tax_office_number);
204 fnd_file.put_line(fnd_file.log,' gv_tax_office_code :'||gv_tax_office_code );
205
206
207
208 OPEN c_currency_vat_reg_num ;
209 FETCH c_currency_vat_reg_num into gv_currency_code
210 , gv_name
211 , gv_vat_reg_num
212 , gv_vat_country_code ;
213 CLOSE c_currency_vat_reg_num;
214
215 fnd_file.put_line(fnd_file.log,' gv_currency_code :'||gv_currency_code);
216 fnd_file.put_line(fnd_file.log,' gv_name :'||gv_name);
217 fnd_file.put_line(fnd_file.log,' gv_vat_reg_num :'||gv_vat_reg_num);
218 fnd_file.put_line(fnd_file.log,' gv_vat_country_code :'||gv_vat_country_code );
219
220 IF(gv_currency_code = 'PTE') THEN
221 gn_thousands:= 1000;
222 ELSE
223 gn_thousands := 1;
224 END IF;
225
226 OPEN c_get_gl_name;
227 FETCH c_get_gl_name into gv_name ;
228 CLOSE c_get_gl_name;
229
230 OPEN entity_details;
231 FETCH entity_details INTO gv_ledger_id,
232 gv_balancing_segment_value,
233 gv_chart_of_accounts_id;
234 CLOSE entity_details;
235
236 FOR r_inv_lines IN c_generic_ap_inv_lines
237 LOOP
238
239 INSERT INTO jg_zz_vat_trx_gt
240 (jg_info_n1 ,
241 jg_info_n2 ,
242 jg_info_n3 ,
243 jg_info_d1 ,
244 jg_info_v1 ,
245 jg_info_v2 ,
246 jg_info_n4 ,
247 jg_info_n5 ,
248 jg_info_n6 ,
249 jg_info_v3 ,
250 jg_info_v4 ,
251 jg_info_n7 ,
252 jg_info_v5 ,
253 jg_info_n8 , --stat_amount Now Taxable Amount Bug 5750278
254 jg_info_n9 , --amount
255 jg_info_n10, --base_amount
256 jg_info_n11, --charge_applicable_to_dist_id
257 jg_info_n12, --invoice_distribution_id
258 jg_info_v7, --merchant_taxpayer_id
259 jg_info_v6 --id_line_type_lookup_code
260 )
261 VALUES(
262 r_inv_lines.invoice_id
263 , r_inv_lines.vendor_id
264 , r_inv_lines.vendor_site_id
265 , r_inv_lines.invoice_date
266 , r_inv_lines.invoice_currency_code
267 , r_inv_lines.invoice_type_lookup_code
268 , r_inv_lines.legal_entity_id
269 , r_inv_lines.doc_sequence_value
270 , r_inv_lines.line_number
271 , r_inv_lines.line_type_lookup_code
272 , r_inv_lines.period_name
273 , r_inv_lines.tax_code_id
274 , r_inv_lines.match_status_flag
275 -- , r_inv_lines.stat_amount -- Commented for Bug 5750278
276 , r_inv_lines.taxable_amount -- Added for Bug 5750278
277 , r_inv_lines.amount
278 , r_inv_lines.base_amount
279 , r_inv_lines.charge_applicable_to_dist_id
280 , r_inv_lines.invoice_distribution_id
281 , r_inv_lines.merchant_taxpayer_id
282 , r_inv_lines.id_line_type_lookup_code
283 );
284
285 END LOOP;
286
287 fnd_file.put_line(fnd_file.log,' After inserting the data into Global Temp Table');
288
289 RETURN (TRUE);
290 END;
291
292 /*
293 REM +======================================================================+
294 REM Name: get_bsv
295 REM
296 REM Description: This function is called in the generic cursor for getting the
297 REM BSV for each invoice distribution.
298 REM
299 REM
300 REM Parameters: ccid (code combination id)
301 REM
302 REM +======================================================================+
303 */
304
305 FUNCTION get_bsv(ccid number) RETURN VARCHAR2 IS
306
307 l_segment VARCHAR2(30);
308 bal_segment_value VARCHAR2(25);
309
310 BEGIN
311
312 SELECT application_column_name
313 INTO l_segment
314 FROM fnd_segment_attribute_values ,
315 gl_ledgers gl
316 WHERE id_flex_code = 'GL#'
317 AND attribute_value = 'Y'
318 AND segment_attribute_type = 'GL_BALANCING'
319 AND application_id = 101
320 AND id_flex_num = gl.chart_of_accounts_id
321 AND gl.chart_of_accounts_id = gv_chart_of_accounts_id
322 AND gl.ledger_id = gv_ledger_id;
323
324 EXECUTE IMMEDIATE 'SELECT '||l_segment ||
325 ' FROM gl_code_combinations '||
326 ' WHERE code_combination_id = '||ccid
327 INTO bal_segment_value;
328
329 RETURN (bal_segment_value);
330
331 EXCEPTION
332 WHEN NO_DATA_FOUND THEN
333 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Balancing segment. Error : ' || SUBSTR(SQLERRM,1,200));
334 RETURN NULL;
335
336 END get_bsv;
337
338 function get_gd_period_end_date return date IS
339 begin
340 return(gd_period_end_date);
341 end;
342
343 function get_gn_thousands return number IS
344 begin
345 return(gn_thousands);
346 end;
347
348 function get_gv_vat_country_code return varchar2 IS
349 begin
350 return(gv_vat_country_code);
351 end;
352
353 function get_gv_currency_code return varchar2 IS
354 begin
355 return(gv_currency_code);
356 end;
357
358 function get_gv_repent_country return varchar2 IS
359 begin
360 return(gv_country);
361 end;
362
363 function get_gv_vat_reg_num return varchar2 IS
364 begin
365 return(gv_vat_reg_num);
366 end;
367
368 function get_gv_repent_trn return varchar2 IS
369 begin
370 return(gv_repent_trn);
371 end;
372
373 function get_gv_repent_name return varchar2 IS
374 begin
375 return(gv_repent_name);
376 end;
377
378 function get_gv_repent_address_line_1 return varchar2 IS
379 begin
380 return(gv_repent_address_line_1);
381 end;
382
383 function get_gv_repent_address_line_2 return varchar2 IS
384 begin
385 return(gv_repent_address_line_2);
386 end;
387
388 function get_gv_repent_address_line_3 return varchar2 IS
389 begin
390 return(gv_repent_address_line_3);
391 end;
392
393 function get_gv_repent_town_or_city return varchar2 IS
394 begin
395 return(gv_repent_town_or_city);
396 end;
397
398 function get_gv_repent_postal_code return varchar2 IS
399 begin
400 return(gv_repent_postal_code);
401 end;
402
403 function get_gv_country return varchar2 IS
404 begin
405 return(gv_country);
406 end;
407
408 function get_gv_repent_id_number return varchar2 IS
409 begin
410 return(gv_repent_id_number);
411 end;
412
413 function get_gv_tax_office_location return varchar2 IS
414 begin
415 return(gv_tax_office_location);
416 end;
417
418 function get_gv_tax_office_number return varchar2 IS
419 begin
420 return(gv_tax_office_number);
421 end;
422
423 function get_gv_tax_office_code return varchar2 IS
424 begin
425 return(gv_tax_office_code);
426 end;
427
428 function get_gv_repent_phone_number return varchar2 IS
429 begin
430 return(gv_repent_phone_number);
431 end;
432
433 function get_gv_entity_identifier return varchar2 IS
434 begin
435 return(gv_entity_identifier);
436 end;
437
438 function get_item_tax_code_id(inv_id number,inv_dist_id number,tax_rate_id number) return number IS
439
440 l_tax_code_id number;
441
442 CURSOR c_get_tax_code_id(p_inv_id number,p_inv_dist_id number,p_tax_rate_id number) IS
443 SELECT distinct tax_code_id
444 FROM ap_invoice_distributions_all
445 WHERE invoice_id = p_inv_id
446 AND charge_applicable_to_dist_id = p_inv_dist_id;
447
448 BEGIN
449
450 BEGIN
451
452 SELECT NVL(tax_code_id,-1)
453 INTO l_tax_code_id
454 FROM ap_invoice_distributions_all
455 WHERE invoice_id = inv_id
456 AND invoice_distribution_id = inv_dist_id;
457
458 IF l_tax_code_id <> -1 THEN
459
460 IF l_tax_code_id=tax_rate_id THEN
461 RETURN l_tax_code_id;
462 ELSE
463 RETURN -1;
464 END IF;
465
466 END IF;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 l_tax_code_id := -1;
471 END;
472
473 IF l_tax_code_id = -1 THEN
474
475 FOR r_tax_codes IN c_get_tax_code_id(inv_id,inv_dist_id,tax_rate_id)
476 LOOP
477
478 if ( tax_rate_id = r_tax_codes.tax_code_id ) then
479 RETURN(tax_rate_id);
480 ----------------------------------
481 -- Added else for Bug 14180262
482 ----------------------------------
483 else
484 RETURN(r_tax_codes.tax_code_id);
485 end if;
486
487 END LOOP;
488 END IF;
489
490 RETURN(1);
491
492 END;
493
494
495 END JG_ZZ_PTCE_DT_PKG ;