eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JG_ZZ_RTCE_DT_PKG

Source


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