[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_RTCE_DT_PKG
Source
1 PACKAGE BODY JG_ZZ_RTCE_DT_PKG AS
2 /* $Header: jgzztoarntrlb.pls 120.12.12020000.3 2012/10/22 09:33:02 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 FROM jg_zz_vat_rep_entities cfg
13 ,jg_zz_vat_rep_entities cfgd
14 ,gl_periods glp
15 WHERE cfg.vat_reporting_entity_id = P_REPORTING_ENTITY_ID
16 AND (( cfg.entity_type_code = 'ACCOUNTING'
17 AND cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id)
18 OR
19 ( cfg.entity_type_code = 'LEGAL'
20 AND cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id))
21 AND glp.period_set_name = nvl(cfg.tax_calendar_name,cfgd.tax_calendar_name)
22 AND glp.period_year = P_FISCAL_YEAR
23 GROUP BY nvl(cfg.legal_entity_id,cfgd.legal_entity_id),
24 nvl(cfg.tax_registration_number,cfgd.tax_registration_number);
25
26 CURSOR c_currency_tax_reg_num
27 IS
28 SELECT gllev.currency_code
29 ,arsp.tax_registration_number
30 FROM gl_ledger_le_v gllev
31 ,ar_system_parameters arsp
32 WHERE gllev.legal_entity_id = gn_legal_entity_id
33 AND arsp.set_of_books_id = gllev.ledger_id
34 AND gllev.ledger_category_code = 'PRIMARY';
35
36 /* Modified the cursor for bug#5199499 */
37 CURSOR c_get_rep_entity_info
38 IS
39 SELECT xfpiv.registration_number
40 , xfpiv.name
41 , xfpiv.address_line_1
42 , xfpiv.address_line_2
43 , xfpiv.address_line_3
44 , xfpiv.town_or_city
45 , xfpiv.postal_code
46 , xfpiv.country
47 , NULL phone_number
48 , xle_auth.city tax_office_location
49 , xle_auth.address2 tax_office_number
50 , xle_auth.address3 tax_office_code
51 , xle_reg.issuing_authority_id
52 , xle_auth.party_id
53 FROM xle_firstparty_information_v xfpiv
54 , xle_registrations xle_reg
55 -- , hz_parties hzp -- Bug 5522964
56 , xle_legalauth_v xle_auth
57 WHERE xle_reg.source_id = xfpiv.legal_entity_id
58 AND xle_reg.source_table = 'XLE_ENTITY_PROFILES'
59 AND xle_auth.legalauth_id (+) = xle_reg.issuing_authority_id
60 AND xle_reg.identifying_flag = 'Y'
61 AND xfpiv.legislative_cat_code = 'INCOME_TAX'
62 AND xfpiv.legal_entity_id = gn_legal_entity_id;
63 -- AND hzp.party_id = xle_auth.party_id ; -- Bug 5522964
64
65 CURSOR c_generic_ar_inv_lines
66 IS
67 SELECT ract.customer_trx_id
68 ,ract.cust_trx_type_id
69 ,ract.trx_number
70 ,ract.trx_date
71 ,ract.sold_to_customer_id
72 ,ract.bill_to_customer_id
73 ,ract.exchange_rate
74 ,ract.printing_original_date
75 ,ract.previous_customer_trx_id
76 ,ract.complete_flag
77 ,racgd.cust_trx_line_gl_dist_id --14249544
78 ,ractl.customer_trx_line_id
79 ,ractl.line_number
80 ,ractl.line_type
81 ,ractl.link_to_cust_trx_line_id
82 ,ractl.extended_amount
83 ,ractl.vat_tax_id
84 ,sum(racgd.acctd_amount) acctd_amount
85 ,sum(racgd.amount) amount
86 FROM ra_customer_trx ract
87 ,ra_customer_trx_lines ractl
88 ,ra_cust_trx_line_gl_dist racgd
89 ,jg_zz_vat_rep_entities repent
90 WHERE repent.vat_reporting_entity_id = P_REPORTING_ENTITY_ID
91 AND (( repent.entity_type_code = 'LEGAL' AND
92 ract.legal_entity_id = gn_legal_entity_id )
93 OR
94 ( repent.entity_type_code = 'ACCOUNTING' AND
95 repent.entity_level_code = 'LEDGER' AND
96 ract.set_of_books_id = gv_ledger_id )
97 OR
98 ( repent.entity_type_code = 'ACCOUNTING' AND
99 repent.entity_level_code = 'BSV' AND
100 ract.set_of_books_id = gv_ledger_id AND
101 get_bsv(racgd.code_combination_id) = gv_balancing_segment_value ))
102 AND racgd.customer_trx_line_id = ractl.customer_trx_line_id
103 AND racgd.customer_trx_id = ractl.customer_trx_id
104 AND ractl.customer_trx_id = ract.customer_trx_id
105 -- AND nvl(racgd.CCID_CHANGE_FLAG,'Y') <>'N' -- Bug 14249544
106 GROUP BY ract.customer_trx_id
107 ,ract.cust_trx_type_id
108 ,ract.trx_number
109 ,ract.trx_date
110 ,ract.sold_to_customer_id
111 ,ract.bill_to_customer_id
112 ,ract.exchange_rate
113 ,ract.printing_original_date
114 ,ract.previous_customer_trx_id
115 ,racgd.cust_trx_line_gl_dist_id
116 ,ract.complete_flag
117 ,ractl.customer_trx_line_id
118 ,ractl.line_number
119 ,ractl.line_type
120 ,ractl.link_to_cust_trx_line_id
121 ,ractl.extended_amount
122 ,ractl.vat_tax_id ;
123
124 CURSOR entity_details
125 IS
126 SELECT repent.ledger_id
127 ,repent.balancing_segment_value
128 ,gl.chart_of_accounts_id
129 FROM jg_zz_vat_rep_entities repent
130 ,gl_ledgers gl
131 WHERE vat_reporting_entity_id = P_REPORTING_ENTITY_ID AND
132 gl.ledger_id = repent.ledger_id;
133
134 lv_count NUMBER default 0 ;
135 ln_party_id NUMBER ;
136 p_debug_flag VARCHAR2(1) default 'Y' ;
137 lv_party_id NUMBER(15);
138 lv_issuing_authority_id NUMBER(15);
139
140 begin
141
142 fnd_file.put_line ( fnd_file.log, 'In main' );
143
144 IF p_debug_flag = 'Y' THEN
145 fnd_file.put_line(fnd_file.log,'Inside BeforeReport Trigger');
146 fnd_file.put_line(fnd_file.log,'p_reporting_entity_id : ' || P_REPORTING_ENTITY_ID);
147 fnd_file.put_line(fnd_file.log,'p_fiscal_year : ' || P_FISCAL_YEAR);
148 fnd_file.put_line(fnd_file.log,'p_dec_type : ' || P_DEC_TYPE);
149 fnd_file.put_line(fnd_file.log,'p_min_inv_amt : ' || P_MIN_INV_AMT);
150 fnd_file.put_line(fnd_file.log,'p_called_from : ' || P_CALLED_FROM);
151 fnd_file.put_line(fnd_file.log,'$Profile$.ORG_ID : ' || fnd_profile.value('ORG_ID'));
152 END IF ;
153
154 OPEN c_get_le_and_period_dates ;
155 FETCH c_get_le_and_period_dates INTO gn_legal_entity_id
156 ,gv_repent_trn
157 ,gd_period_start_date
158 ,gd_period_end_date ;
159 CLOSE c_get_le_and_period_dates ;
160
161 fnd_file.put_line ( fnd_file.log, gn_legal_entity_id || ' : ' || gv_repent_trn || ' : ' || gd_period_start_date || ' : ' || gd_period_end_date );
162
163 IF p_debug_flag = 'Y' THEN
164 fnd_file.put_line(fnd_file.log,'gn_legal_entity_id : ' ||gn_legal_entity_id);
165 fnd_file.put_line(fnd_file.log,'gv_repent_trn : ' ||gv_repent_trn);
166 fnd_file.put_line(fnd_file.log,'gd_period_start_date : ' ||gd_period_start_date);
167 fnd_file.put_line(fnd_file.log,'gd_period_end_date : ' ||gd_period_end_date);
168 END IF ;
169
170 OPEN c_get_rep_entity_info;
171 FETCH c_get_rep_entity_info INTO gv_repent_id_number
172 ,gv_repent_name
173 ,gv_repent_address_line_1
174 ,gv_repent_address_line_2
175 ,gv_repent_address_line_3
176 ,gv_repent_town_or_city
177 ,gv_repent_postal_code
178 ,gv_country
179 ,gv_repent_phone_number
180 ,gv_tax_office_location
181 ,gv_tax_office_number
182 ,gv_tax_office_code
183 ,lv_issuing_authority_id
184 ,lv_party_id;
185 CLOSE c_get_rep_entity_info;
186
187 IF lv_issuing_authority_id IS NOT NULL THEN
188 SELECT hzp.primary_phone_area_code
189 ||' '|| hzp.primary_phone_country_code
190 ||' '|| hzp.primary_phone_number phone_number
191 INTO gv_repent_phone_number
192 FROM hz_parties hzp
193 WHERE hzp.party_id = lv_party_id;
194 END IF;
195
196 IF p_debug_flag = 'Y' THEN
197 fnd_file.put_line(fnd_file.log,'gv_repent_id_number : ' ||gv_repent_id_number ) ;
198 fnd_file.put_line(fnd_file.log,'gv_repent_name : ' ||gv_repent_name ) ;
199 fnd_file.put_line(fnd_file.log,'gv_repent_address_line_1 : ' ||gv_repent_address_line_1 ) ;
200 fnd_file.put_line(fnd_file.log,'gv_repent_address_line_2 : ' ||gv_repent_address_line_2 ) ;
201 fnd_file.put_line(fnd_file.log,'gv_repent_address_line_3 : ' ||gv_repent_address_line_3 ) ;
202 fnd_file.put_line(fnd_file.log,'gv_repent_town_or_city : ' ||gv_repent_town_or_city ) ;
203 fnd_file.put_line(fnd_file.log,'gv_repent_postal_code : ' ||gv_repent_postal_code ) ;
204 fnd_file.put_line(fnd_file.log,'gv_country : ' ||gv_country ) ;
205 fnd_file.put_line(fnd_file.log,'gv_repent_phone_number : ' ||gv_repent_phone_number ) ;
206 fnd_file.put_line(fnd_file.log,'gv_tax_office_location : ' ||gv_tax_office_location ) ;
207 fnd_file.put_line(fnd_file.log,'gv_tax_office_number : ' ||gv_tax_office_number ) ;
208 fnd_file.put_line(fnd_file.log,'gv_tax_office_code : ' ||gv_tax_office_code ) ;
209 END IF ;
210
211
212 OPEN c_currency_tax_reg_num;
213 FETCH c_currency_tax_reg_num INTO gv_currency_code
214 ,gv_tax_reg_num ;
215 CLOSE c_currency_tax_reg_num;
216
217 IF p_debug_flag = 'Y' THEN
218 fnd_file.put_line(fnd_file.log,'gv_currency_code : '|| gv_currency_code ) ;
219 fnd_file.put_line(fnd_file.log,'gv_tax_reg_num : '|| gv_tax_reg_num ) ;
220 END IF ;
221
222 OPEN entity_details;
223 FETCH entity_details INTO gv_ledger_id,
224 gv_balancing_segment_value,
225 gv_chart_of_accounts_id;
226 CLOSE entity_details;
227
228 IF p_debug_flag = 'Y' THEN
229 fnd_file.put_line(fnd_file.log,'gv_ledger_id : '|| gv_ledger_id ) ;
230 fnd_file.put_line(fnd_file.log,'gv_balancing_segment_value : '|| gv_balancing_segment_value ) ;
231 fnd_file.put_line(fnd_file.log,'gv_chart_of_accounts_id : '|| gv_chart_of_accounts_id ) ;
232 END IF ;
233
234 fnd_file.put_line(fnd_file.log,'Before Insert INTO JG_ZZ_VAT_TRX_GT table' ) ;
235
236 FOR r_inv_lines in c_generic_ar_inv_lines
237 LOOP
238
239 INSERT INTO jg_zz_vat_trx_gt
240 ( jg_info_n1
241 , jg_info_n2
242 , jg_info_v1
243 , jg_info_d1
244 , jg_info_n3
245 , jg_info_n4
246 , jg_info_n5
247 , jg_info_d2
248 , jg_info_n6
249 , jg_info_v2
250 , jg_info_n7
251 , jg_info_n8
252 , jg_info_v3
253 , jg_info_n9
254 , jg_info_n10
255 , jg_info_n11
256 , jg_info_n12
257 , jg_info_n13
258 , jg_info_n14
259 )
260 values ( r_inv_lines.customer_trx_id
261 ,r_inv_lines.cust_trx_type_id
262 ,r_inv_lines.trx_number
263 ,r_inv_lines.trx_date
264 ,r_inv_lines.sold_to_customer_id
265 ,r_inv_lines.bill_to_customer_id
266 ,r_inv_lines.exchange_rate
267 ,r_inv_lines.printing_original_date
268 ,r_inv_lines.previous_customer_trx_id
269 ,r_inv_lines.complete_flag
270 ,r_inv_lines.customer_trx_line_id
271 ,r_inv_lines.line_number
272 ,r_inv_lines.line_type
273 ,r_inv_lines.link_to_cust_trx_line_id
274 ,r_inv_lines.extended_amount
275 ,r_inv_lines.vat_tax_id
276 ,r_inv_lines.acctd_amount
277 ,r_inv_lines.amount
278 ,r_inv_lines.cust_trx_line_gl_dist_id
279 );
280 end loop;
281
282 select count(*) INTO lv_count from jg_zz_vat_trx_gt;
283
284 fnd_file.put_line(fnd_file.log,'Number of records inserted INTO JG_ZZ_VAT_TRX_GT table: ' || lv_count );
285 return (TRUE);
286 EXCEPTION
287 WHEN OTHERS THEN
288 fnd_file.put_line(fnd_file.log,'Unexpected error occurred in BeforeReport Trigger. Error : ' || SUBSTR(SQLERRM,1,200));
289 RETURN (NULL) ;
290
291 END BeforeReport;
292
293 FUNCTION cf_total_amount(taxable_amount in number, tax_amount in number, exempt_amount in number, nontaxable_amount in number) return number is
294 begin
295 return ( taxable_amount
296 + tax_amount
297 + exempt_amount
298 + nontaxable_amount
299 );
300 end cf_total_amount;
301
302 FUNCTION get_bsv(ccid number) RETURN VARCHAR2 IS
303 l_segment VARCHAR2(30);
304 bal_segment_value VARCHAR2(25);
305 BEGIN
306
307 SELECT application_column_name
308 INTO l_segment
309 FROM fnd_segment_attribute_values ,
310 gl_ledgers gl
311 WHERE id_flex_code = 'GL#'
312 AND attribute_value = 'Y'
313 AND segment_attribute_type = 'GL_BALANCING'
314 AND application_id = 101
315 AND id_flex_num = gl.chart_of_accounts_id
316 AND gl.chart_of_accounts_id = gv_chart_of_accounts_id
317 AND gl.ledger_id = gv_ledger_id;
318
319
320 EXECUTE IMMEDIATE 'SELECT '||l_segment ||
321 ' FROM gl_code_combinations '||
322 ' WHERE code_combination_id = '||ccid
323 INTO bal_segment_value;
324
325 RETURN (bal_segment_value);
326
327 EXCEPTION
328 WHEN NO_DATA_FOUND THEN
329 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Balancing segment. Error : ' || SUBSTR(SQLERRM,1,200));
330 RETURN NULL;
331 END get_bsv;
332
333 /*
334 || Added the following funtions during UT, Start
335 */
336
337 FUNCTION cf_currency_code return VARCHAR2 IS
338 BEGIN
339 return ( gv_currency_code );
340 END ;
341
342 FUNCTION cf_country return VARCHAR2 IS
343 BEGIN
344 return ( gv_country ) ;
345 END ;
346
347
348 FUNCTION cf_tax_reg_num return VARCHAR2 IS
349 BEGIN
350 return ( gv_tax_reg_num ) ;
351 END ;
352
353
354 FUNCTION cf_legal_entity_id return NUMBER IS
355 BEGIN
356 return ( gn_legal_entity_id ) ;
357 END ;
358
359
360 FUNCTION cf_repent_id_number return VARCHAR2 IS
361 BEGIN
362 return ( gv_repent_id_number ) ;
363 END ;
364
365
366 FUNCTION cf_repent_trn return VARCHAR2 IS
367 BEGIN
368 return ( gv_repent_trn ) ;
369 END ;
370
371
372 FUNCTION cf_period_start_date return DATE IS
373 BEGIN
374 return ( gd_period_start_date ) ;
375 END ;
376
377
378 FUNCTION cf_period_end_date return DATE IS
379 BEGIN
380 return ( gd_period_END_date ) ;
381 END ;
382
383
384 FUNCTION cf_repent_name return VARCHAR2 IS
385 BEGIN
386 return ( gv_repent_name ) ;
387 END ;
388
389
390 FUNCTION cf_repent_address_line_1 return VARCHAR2 IS
391 BEGIN
392 return ( gv_repent_address_line_1 ) ;
393 END ;
394
395
396 FUNCTION cf_repent_address_line_2 return VARCHAR2 IS
397 BEGIN
398 return ( gv_repent_address_line_2 ) ;
399 END ;
400
401
402 FUNCTION cf_repent_address_line_3 return VARCHAR2 IS
403 BEGIN
404 return ( gv_repent_address_line_3 ) ;
405 END ;
406
407
408 FUNCTION cf_repent_town_or_city return VARCHAR2 IS
409 BEGIN
410 return ( gv_repent_town_or_city ) ;
411 END ;
412
413
414 FUNCTION cf_repent_postal_code return VARCHAR2 IS
415 BEGIN
416 return ( gv_repent_postal_code ) ;
417 END ;
418
419
420 FUNCTION cf_repent_phone_number return VARCHAR2 IS
421 BEGIN
422 return ( gv_repent_phone_number ) ;
423 END ;
424
425
426 FUNCTION cf_tax_office_location return VARCHAR2 IS
427 BEGIN
428 return ( gv_tax_office_location ) ;
429 END ;
430
431
432 FUNCTION cf_tax_office_number return VARCHAR2 IS
433 BEGIN
434 return ( gv_tax_office_number ) ;
435 END ;
436
437
438 FUNCTION cf_tax_office_code return VARCHAR2 IS
439 BEGIN
440 return ( gv_tax_office_code ) ;
441 END ;
442
443 FUNCTION cf_prev_fiscal_code return VARCHAR2 IS
444 BEGIN
445 return ( gv_prev_fiscal_code ) ;
446 END ;
447
448 /*
449 || Added the Funtions during UT, end
450 */
451
452 END JG_ZZ_RTCE_DT_PKG ;