[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.11.12010000.2 2008/08/04 13:56:58 vgadde 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_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
103 ,sum(id.base_amount) base_amount
104 FROM
105 ap_invoices i,
106 ap_invoice_lines il,
107 ap_invoice_distributions id,
108 gl_code_combinations gl,
109 jg_zz_vat_rep_entities repent
110 WHERE repent.vat_reporting_entity_id = p_reporting_entity_id
111 and ( ( repent.entity_type_code = 'LEGAL' AND i.legal_entity_id = gn_legal_entity_id )
112 or ( repent.entity_type_code = 'ACCOUNTING' AND repent.entity_level_code = 'LEDGER' AND
113 i.set_of_books_id = gv_ledger_id)
114 or ( repent.entity_type_code = 'ACCOUNTING' AND repent.entity_level_code = 'BSV'
115 and i.set_of_books_id = gv_ledger_id
116 and get_bsv(id.dist_code_combination_id) = gv_balancing_segment_value ) )
117 and i.invoice_id = il.invoice_id
118 and i.invoice_id = id.invoice_id
119 and il.line_number = id.invoice_line_number
120 and id.dist_code_combination_id = gl.code_combination_id
121 and ( (P_called_from = 'JEITRAVL' and to_char(i.invoice_date, 'YYYY') in
122 (to_char(gd_period_end_date, 'YYYY'),to_char(add_months(gd_period_end_date,-12),'YYYY')))
123 or (P_called_from = 'JEPTAPVR' and
124 id.accounting_date between to_date('01/01/' || to_char(p_fiscal_year),'DD/MM/YYYY')
125 and to_date('31/12/' || to_char(p_fiscal_year),'DD/MM/YYYY'))
126
127 )
128 GROUP BY
129 i.invoice_id
130 ,i.vendor_id
131 ,i.vendor_site_id
132 ,i.invoice_date
133 ,i.invoice_currency_code
134 ,i.invoice_type_lookup_code
135 ,i.legal_entity_id
136 ,i.doc_sequence_value
137 ,il.line_number
138 ,il.line_type_lookup_code
139 ,id.period_name
140 ,nvl(id.tax_code_id,get_item_tax_code_id(id.invoice_id,id.invoice_distribution_id))
141 ,id.match_status_flag
142 ,id.charge_applicable_to_dist_id
143 ,id.invoice_distribution_id
144 ,id.merchant_taxpayer_id
145 ,id.line_type_lookup_code;
146
147
148
149 CURSOR entity_details IS
150 SELECT repent.ledger_id,
151 repent.balancing_segment_value,
152 gl.CHART_OF_ACCOUNTS_ID
153 FROM jg_zz_vat_rep_entities repent
154 ,gl_ledgers gl
155 WHERE vat_reporting_entity_id = p_reporting_entity_id
156 AND gl.ledger_id = repent.ledger_id;
157
158 begin
159
160 OPEN c_get_le_and_period_dates;
161 FETCH c_get_le_and_period_dates into gn_legal_entity_id
162 ,gv_repent_trn
163 ,gd_period_start_date
164 ,gd_period_end_date
165 ,gv_entity_identifier;
166 CLOSE c_get_le_and_period_dates;
167
168 fnd_file.put_line(fnd_file.log,'*******Information*********');
169 fnd_file.put_line(fnd_file.log,' gn_legal_entity_id :'|| gn_legal_entity_id);
170 fnd_file.put_line(fnd_file.log,' gv_repent_trn :'|| gv_repent_trn);
171 fnd_file.put_line(fnd_file.log,' gd_period_start_date :'||gd_period_start_date);
172 fnd_file.put_line(fnd_file.log,' gd_period_end_date :'|| gd_period_end_date);
173 fnd_file.put_line(fnd_file.log,' gv_entity_identifier :'|| gv_entity_identifier);
174
175 OPEN c_get_rep_entity_info;
176 FETCH c_get_rep_entity_info into gv_repent_id_number
177 ,gv_repent_name
178 ,gv_repent_address_line_1
179 ,gv_repent_address_line_2
180 ,gv_repent_address_line_3
181 ,gv_repent_town_or_city
182 ,gv_repent_postal_code
183 ,gv_country
184 ,gv_repent_phone_number
185 ,gv_tax_office_location
186 ,gv_tax_office_number
187 ,gv_tax_office_code ;
188 CLOSE c_get_rep_entity_info;
189
190 fnd_file.put_line(fnd_file.log,' gv_repent_id_number :'||gv_repent_id_number);
191 fnd_file.put_line(fnd_file.log,' gv_repent_name :'|| gv_repent_name);
192 fnd_file.put_line(fnd_file.log,' gv_repent_address_line_1 :'||gv_repent_address_line_1);
193 fnd_file.put_line(fnd_file.log,' gv_repent_address_line_2 :'||gv_repent_address_line_2);
194 fnd_file.put_line(fnd_file.log,' gv_repent_address_line_3 :'||gv_repent_address_line_3);
195 fnd_file.put_line(fnd_file.log,' gv_repent_town_or_city :'||gv_repent_town_or_city);
196 fnd_file.put_line(fnd_file.log,' gv_repent_postal_code :'||gv_repent_postal_code);
197 fnd_file.put_line(fnd_file.log,' gv_country :'||gv_country);
198 fnd_file.put_line(fnd_file.log,' gv_repent_phone_number :'||gv_repent_phone_number);
199 fnd_file.put_line(fnd_file.log,' gv_tax_office_location :'||gv_tax_office_location);
200 fnd_file.put_line(fnd_file.log,' gv_tax_office_number :'||gv_tax_office_number);
201 fnd_file.put_line(fnd_file.log,' gv_tax_office_code :'||gv_tax_office_code );
202
203
204
205 OPEN c_currency_vat_reg_num ;
206 FETCH c_currency_vat_reg_num into gv_currency_code
207 , gv_name
208 , gv_vat_reg_num
209 , gv_vat_country_code ;
210 CLOSE c_currency_vat_reg_num;
211
212 fnd_file.put_line(fnd_file.log,' gv_currency_code :'||gv_currency_code);
213 fnd_file.put_line(fnd_file.log,' gv_name :'||gv_name);
214 fnd_file.put_line(fnd_file.log,' gv_vat_reg_num :'||gv_vat_reg_num);
215 fnd_file.put_line(fnd_file.log,' gv_vat_country_code :'||gv_vat_country_code );
216
217 IF(gv_currency_code = 'PTE') THEN
218 gn_thousands:= 1000;
219 ELSE
220 gn_thousands := 1;
221 END IF;
222
223 OPEN c_get_gl_name;
224 FETCH c_get_gl_name into gv_name ;
225 CLOSE c_get_gl_name;
226
227 OPEN entity_details;
228 FETCH entity_details INTO gv_ledger_id,
229 gv_balancing_segment_value,
230 gv_chart_of_accounts_id;
231 CLOSE entity_details;
232
233 FOR r_inv_lines IN c_generic_ap_inv_lines
234 LOOP
235
236 INSERT INTO jg_zz_vat_trx_gt
237 (jg_info_n1 ,
238 jg_info_n2 ,
239 jg_info_n3 ,
240 jg_info_d1 ,
241 jg_info_v1 ,
242 jg_info_v2 ,
243 jg_info_n4 ,
244 jg_info_n5 ,
245 jg_info_n6 ,
246 jg_info_v3 ,
247 jg_info_v4 ,
248 jg_info_n7 ,
249 jg_info_v5 ,
250 jg_info_n8 , --stat_amount Now Taxable Amount Bug 5750278
251 jg_info_n9 , --amount
252 jg_info_n10, --base_amount
253 jg_info_n11, --charge_applicable_to_dist_id
254 jg_info_n12, --invoice_distribution_id
255 jg_info_v7, --merchant_taxpayer_id
256 jg_info_v6 --id_line_type_lookup_code
257 )
258 VALUES(
259 r_inv_lines.invoice_id
260 , r_inv_lines.vendor_id
261 , r_inv_lines.vendor_site_id
262 , r_inv_lines.invoice_date
263 , r_inv_lines.invoice_currency_code
264 , r_inv_lines.invoice_type_lookup_code
265 , r_inv_lines.legal_entity_id
266 , r_inv_lines.doc_sequence_value
267 , r_inv_lines.line_number
268 , r_inv_lines.line_type_lookup_code
269 , r_inv_lines.period_name
270 , r_inv_lines.tax_code_id
271 , r_inv_lines.match_status_flag
272 -- , r_inv_lines.stat_amount -- Commented for Bug 5750278
273 , r_inv_lines.taxable_amount -- Added for Bug 5750278
274 , r_inv_lines.amount
275 , r_inv_lines.base_amount
276 , r_inv_lines.charge_applicable_to_dist_id
277 , r_inv_lines.invoice_distribution_id
278 , r_inv_lines.merchant_taxpayer_id
279 , r_inv_lines.id_line_type_lookup_code
280 );
281
282 END LOOP;
283
284 fnd_file.put_line(fnd_file.log,' After inserting the data into Global Temp Table');
285
286 RETURN (TRUE);
287 END;
288
289 /*
290 REM +======================================================================+
291 REM Name: get_bsv
292 REM
293 REM Description: This function is called in the generic cursor for getting the
294 REM BSV for each invoice distribution.
295 REM
296 REM
297 REM Parameters: ccid (code combination id)
298 REM
299 REM +======================================================================+
300 */
301
302 FUNCTION get_bsv(ccid number) RETURN VARCHAR2 IS
303
304 l_segment VARCHAR2(30);
305 bal_segment_value VARCHAR2(25);
306
307 BEGIN
308
309 SELECT application_column_name
310 INTO l_segment
311 FROM fnd_segment_attribute_values ,
312 gl_ledgers gl
313 WHERE id_flex_code = 'GL#'
314 AND attribute_value = 'Y'
315 AND segment_attribute_type = 'GL_BALANCING'
316 AND application_id = 101
317 AND id_flex_num = gl.chart_of_accounts_id
318 AND gl.chart_of_accounts_id = gv_chart_of_accounts_id
319 AND gl.ledger_id = gv_ledger_id;
320
321 EXECUTE IMMEDIATE 'SELECT '||l_segment ||
322 ' FROM gl_code_combinations '||
323 ' WHERE code_combination_id = '||ccid
324 INTO bal_segment_value;
325
326 RETURN (bal_segment_value);
327
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Balancing segment. Error : ' || SUBSTR(SQLERRM,1,200));
331 RETURN NULL;
332
333 END get_bsv;
334
335 function get_gd_period_end_date return date IS
336 begin
337 return(gd_period_end_date);
338 end;
339
340 function get_gn_thousands return number IS
341 begin
342 return(gn_thousands);
343 end;
344
345 function get_gv_vat_country_code return varchar2 IS
346 begin
347 return(gv_vat_country_code);
348 end;
349
350 function get_gv_currency_code return varchar2 IS
351 begin
352 return(gv_currency_code);
353 end;
354
355 function get_gv_repent_country return varchar2 IS
356 begin
357 return(gv_country);
358 end;
359
360 function get_gv_vat_reg_num return varchar2 IS
361 begin
362 return(gv_vat_reg_num);
363 end;
364
365 function get_gv_repent_trn return varchar2 IS
366 begin
367 return(gv_repent_trn);
368 end;
369
370 function get_gv_repent_name return varchar2 IS
371 begin
372 return(gv_repent_name);
373 end;
374
375 function get_gv_repent_address_line_1 return varchar2 IS
376 begin
377 return(gv_repent_address_line_1);
378 end;
379
380 function get_gv_repent_address_line_2 return varchar2 IS
381 begin
382 return(gv_repent_address_line_2);
383 end;
384
385 function get_gv_repent_address_line_3 return varchar2 IS
386 begin
387 return(gv_repent_address_line_3);
388 end;
389
390 function get_gv_repent_town_or_city return varchar2 IS
391 begin
392 return(gv_repent_town_or_city);
393 end;
394
395 function get_gv_repent_postal_code return varchar2 IS
396 begin
397 return(gv_repent_postal_code);
398 end;
399
400 function get_gv_country return varchar2 IS
401 begin
402 return(gv_country);
403 end;
404
405 function get_gv_repent_id_number return varchar2 IS
406 begin
407 return(gv_repent_id_number);
408 end;
409
410 function get_gv_tax_office_location return varchar2 IS
411 begin
412 return(gv_tax_office_location);
413 end;
414
415 function get_gv_tax_office_number return varchar2 IS
416 begin
417 return(gv_tax_office_number);
418 end;
419
420 function get_gv_tax_office_code return varchar2 IS
421 begin
422 return(gv_tax_office_code);
423 end;
424
425 function get_gv_repent_phone_number return varchar2 IS
426 begin
427 return(gv_repent_phone_number);
428 end;
429
430 function get_gv_entity_identifier return varchar2 IS
431 begin
432 return(gv_entity_identifier);
433 end;
434
435 function get_item_tax_code_id(inv_id number,inv_dist_id number) return number IS
436
437 tax_code_id number;
438
439 CURSOR c_get_tax_code_id(p_inv_id number,p_inv_dist_id number) IS
440 SELECT distinct tax_code_id
441 FROM ap_invoice_distributions
442 WHERE invoice_id = p_inv_id
443 AND charge_applicable_to_dist_id = p_inv_dist_id;
444 begin
445
446 OPEN c_get_tax_code_id(inv_id,inv_dist_id);
447 FETCH c_get_tax_code_id into tax_code_id;
448 CLOSE c_get_tax_code_id;
449
450 RETURN tax_code_id;
451
452 end;
453
454 END JG_ZZ_PTCE_DT_PKG ;