DBA Data[Home] [Help]

PACKAGE BODY: APPS.JE_BE_CSSR_PKG

Source


1 PACKAGE BODY JE_BE_CSSR_PKG AS
2 /* $Header: jebecsrb.pls 120.1.12010000.3 2008/09/02 07:16:30 vgadde ship $ */
3 
4 procedure main(
5  p_errbuf     OUT NOCOPY VARCHAR2,
6  p_retcode    OUT NOCOPY VARCHAR2,
7  p_vat_reporting_entity_id  jg_zz_vat_rep_entities.vat_reporting_entity_id%TYPE,
8  p_rep_period        gl_periods.period_name%TYPE,
9  p_fax_number        varchar2,
10  p_email             varchar2,
11  p_resp_email        varchar2,
12  p_trans_resp        varchar2,
13  p_trans_ackn        varchar2,
14  p_sec_resp          varchar2,
15  p_sec_ackn          varchar2)is
16 
17  cursor c_get_reg_det(p_vat_reporting_entity_id  jg_zz_vat_rep_entities.vat_reporting_entity_id%TYPE) is
18  SELECT substr(legal.tax_registration_number,3),
19         legal.legal_entity_id
20  FROM jg_zz_vat_rep_entities legal,
21       jg_zz_vat_rep_entities acc
22   WHERE acc.vat_reporting_entity_id = p_vat_reporting_entity_id
23   AND  ((acc.entity_type_code = 'ACCOUNTING'
24          AND acc.mapping_vat_rep_entity_id = legal.vat_reporting_entity_id)
25         OR
26          (acc.entity_type_code = 'LEGAL'
27           and acc.vat_reporting_entity_id = legal.vat_reporting_entity_id)
28          );
29 
30 
31  cursor c_get_iso_lang is
32   select lower(ISO_LANGUAGE) from fnd_languages
33   where language_code = userenv('LANG');
34 
35  l_iso_lang varchar2(4);
36  l_be_reg_number jg_zz_vat_rep_entities.tax_registration_number%type;
37 
38  l_survey_code varchar2(50);
39  l_proc_status varchar2(200);
40  l_legal_entity_name        xle_registrations.registered_name%TYPE;
41  l_tel_num     hz_parties.primary_phone_number%TYPE;
42  l_legal_entity_id jg_zz_vat_rep_entities.legal_entity_id%TYPE;
43  l_dummy varchar2(100);
44  l_exc_survey_code_null exception;
45  l_exc_lang_limit exception;
46 
47 begin
48 
49  fnd_file.put_line(fnd_file.log,'Parameters..');
50  fnd_file.put_line(fnd_file.log,'p_vat_reporting_entity_id  :'||p_vat_reporting_entity_id);
51  fnd_file.put_line(fnd_file.log,'p_rep_period  :'||p_rep_period);
52  fnd_file.put_line(fnd_file.log,'p_fax_number  :'||p_fax_number);
53  fnd_file.put_line(fnd_file.log,'p_email       :'||p_email);
54  fnd_file.put_line(fnd_file.log,'p_resp_email  :'||p_resp_email);
55  fnd_file.put_line(fnd_file.log,'p_trans_resp  :'||p_trans_resp);
56  fnd_file.put_line(fnd_file.log,'p_trans_ackn  :'||p_trans_ackn);
57  fnd_file.put_line(fnd_file.log,'p_sec_resp  :'||p_sec_resp);
58  fnd_file.put_line(fnd_file.log,'p_sec_ackn  :'||p_sec_ackn);
59 
60  open c_get_reg_det(p_vat_reporting_entity_id);
61  fetch c_get_reg_det into l_be_reg_number,l_legal_entity_id;
62  close c_get_reg_det;
63 
64 JG_ZZ_COMMON_PKG.company_detail(x_company_name =>l_legal_entity_name
65                                ,x_registration_number=>l_dummy
66                                ,x_country=>l_dummy
67                                ,x_address1=>l_dummy
68                                ,x_address2=>l_dummy
69                                ,x_address3=>l_dummy
70                                ,x_address4=>l_dummy
71                                ,x_city=>l_dummy
72                                ,x_postal_code=>l_dummy
73                                ,x_contact=>l_dummy
74                                ,x_phone_number =>l_tel_num
75                                ,x_province=>l_dummy
76                                ,x_comm_number=>l_dummy
77                                ,x_vat_reg_num =>l_dummy
78 			                   ,pn_legal_entity_id => l_legal_entity_id
79 			                   ,p_vat_reporting_entity_id=>p_vat_reporting_entity_id
80                                );
81 
82 
83  --get the value of form/survey code
84 
85  fnd_profile.get('JEBE_CSSR_SURVEY_CODE',l_survey_code);
86 
87  fnd_file.put_line(fnd_file.log,'Survey Code :'||l_survey_code);
88 
89  if (l_survey_code  is null ) then
90   raise l_exc_survey_code_null;
91  end if;
92 
93  open c_get_iso_lang;
94  fetch c_get_iso_lang into l_iso_lang;
95  close c_get_iso_lang;
96 
97  if( l_iso_lang not in ('en','nl','fr','de' )) then
98     raise l_exc_lang_limit;
99  end if;
100 
101  l_proc_status := 'Initilising the xml';
102 
103  g_vc_spacing  := 1;
104 
105  fnd_file.put_line(fnd_file.log,'Generating admin data....');
106 
107  get_admin_data(
108   p_vat_reg_num       =>l_be_reg_number,
109   p_email_address     =>p_email,
110   p_tel_num           =>l_tel_num,
111   p_fax_num           =>p_fax_number,
112   p_name              =>l_legal_entity_name,
113   p_email_resp        =>p_resp_email,
114   p_trans_resp        =>p_trans_resp,
115   p_trans_ackn        =>p_trans_ackn,
116   p_sec_resp          =>p_sec_resp,
117   p_sec_ackn          =>p_sec_ackn,
118   p_survey_code       =>l_survey_code,
119   p_period            =>p_rep_period
120  );
121 
122  fnd_file.put_line(fnd_file.log,'Completed admin data');
123 
124  fnd_file.put_line(fnd_file.log,'Generating content data...');
125  get_content_data (l_survey_code,p_rep_period,p_vat_reporting_entity_id);
126  fnd_file.put_line(fnd_file.log,'Completed content data');
127 
128 exception
129  when l_exc_survey_code_null then
130   fnd_file.put_line(fnd_file.log,'-------------');
131   fnd_file.put_line(fnd_file.log,'  ERROR LOG  ');
132   fnd_file.put_line(fnd_file.log,'-------------');
133   fnd_file.put_line(fnd_file.log,' JE_BE_CSSR_PKG: JEBE: Survey code Profile Option not set ');
134   fnd_file.put_line(fnd_file.log,'-------------');
135   P_RETCODE := 2;
136   RETURN;
137 
138  when l_exc_lang_limit then
139   fnd_file.put_line(fnd_file.log,'-------------');
140   fnd_file.put_line(fnd_file.log,'  ERROR LOG  ');
141   fnd_file.put_line(fnd_file.log,'-------------');
142   fnd_file.put_line(fnd_file.log,' JE_BE_CSSR_PKG: The Report can be run for only the following languages');
143   fnd_file.put_line(fnd_file.log,' EN DE NL FR ');
144   fnd_file.put_line(fnd_file.log,'-------------');
145   P_RETCODE := 2;
146   RETURN;
147 
148  when others then
149   fnd_file.put_line(fnd_file.log,'-------------');
150   fnd_file.put_line(fnd_file.log,'  ERROR LOG  ');
151   fnd_file.put_line(fnd_file.log,'-------------');
152   fnd_file.put_line(fnd_file.log,' Unexpected error occured in the JE_BE_CSSR_PKG package');
153   fnd_file.put_line(fnd_file.log,' Please check if all the setup is done properly');
154   fnd_file.put_line(fnd_file.log,'-------------');
155   raise;
156 end main;
157 
158 procedure get_admin_data
159 (
160  p_vat_reg_num varchar2,
161  p_email_address     varchar2,
162  p_tel_num           varchar2,
163  p_fax_num           varchar2,
164  p_name              varchar2,
165  p_email_resp        varchar2,
166  p_trans_resp        varchar2,
167  p_trans_ackn        varchar2,
168  p_sec_resp          varchar2,
169  p_sec_ackn          varchar2,
170  p_survey_code       varchar2,
171  p_period            varchar2
172 )
173 
174 is
175  l_var varchar2(2000);
176  l_proc_status varchar2(200);
177  l_level number(15);
178 
179  l_sysdate varchar2(200);
180  l_string            varchar2(200);
181  l_iso_lang varchar2(4);
182  cursor c_get_iso_lang is
183   select lower(ISO_LANGUAGE) from fnd_languages
184   where language_code = userenv('LANG');
185 
186 begin
187  -- getting the date in the required xml standard
188  select to_char(sysdate,'yyyy-mm-dd') ||'T'
189         || to_char(sysdate,'hh:mi:ss')
190  into l_sysdate
191  from dual;
192  --main open
193 -- g_xml := '<cssr_document xmlns="http://www.nbb.be/cssr">';
194  l_var := JE_BE_CSSR_PKG.level_up;
195  fnd_file.put_line(fnd_file.output,l_var);
196 
197  fnd_file.put_line(fnd_file.output,'<cssr_document xmlns="http://www.nbb.be/cssr">');
198   --admin open
199   l_var := JE_BE_CSSR_PKG.level_up;
200   fnd_file.put_line(fnd_file.output,l_var||'<admin creation_time="' ||l_sysdate || '">');
201 
202    -- sender info open
203   l_var := JE_BE_CSSR_PKG.level_up;
204      fnd_file.put_line(fnd_file.output,l_var||'<sender kbo="'||p_vat_reg_num||'">');
205 
206      --contact tag open
207      l_var := JE_BE_CSSR_PKG.level_up;
208      fnd_file.put_line(fnd_file.output,l_var||'<contact>');
209 
210       -- name tag open and close
211       l_var := JE_BE_CSSR_PKG.level_up;
212       if( p_name is not null ) then
213         fnd_file.put_line(fnd_file.output,l_var||'<name>'||p_name||'</name>');
214       else
215         fnd_file.put_line(fnd_file.output,l_var||'</name>');
216       end if;
217       -- same level ..
218 
219       if(p_email_address is not null) then
220           l_string := '<communication xmlns:xsi="http://www.w3.org/'||
221 		      '2001/XMLSchema-instance" xsi:type="Email" address="' ||
222 		      p_email_address || '"/>';
223           fnd_file.put_line(fnd_file.output,l_var||l_string);
224       end if;
225 
226       if ( p_tel_num is not null ) then
227          l_string := '<communication xmlns:xsi="http://www.w3.org/'||
228 		 '2001/XMLSchema-instance" xsi:type="Telephone" number="' ||
229 		 p_tel_num || '" />';
230          fnd_file.put_line(fnd_file.output,l_var||l_string);
231       end if;
232 
233       if( p_fax_num is not null  ) then
234          l_string := '<communication xmlns:xsi="http://www.w3.org/'||
235 		 '2001/XMLSchema-instance" xsi:type="Fax" number="' ||
236 		 p_fax_num || '" />';
237          fnd_file.put_line(fnd_file.output,l_var||l_string);
238       end if;
239 
240      -- contact tag close
241      l_var := JE_BE_CSSR_PKG.level_down;
242      fnd_file.put_line(fnd_file.output,l_var||'</contact>');
243    -- sender info close
244    l_var := JE_BE_CSSR_PKG.level_down;
245    fnd_file.put_line(fnd_file.output,l_var||'</sender>');
246 
247    fnd_file.put_line(fnd_file.output,l_var||'<receiver/>');
248 
249    -- receiver info open
250    if( p_email_resp is not null  or
251        p_trans_resp is not null  or
252 	   p_trans_ackn is not null  or
253 	   p_sec_resp is not null  or
254 	   p_sec_ackn is not null ) then
255 
256       -- processsing parameters tag open
257        fnd_file.put_line(fnd_file.output,l_var||'<processing_parameters>');
258 
259        l_var := JE_BE_CSSR_PKG.level_up;
260        -- email response tag
261         if ( p_email_resp is not null ) then
262          fnd_file.put_line(fnd_file.output,l_var||'<email_response>'
263  	                               ||lower(p_email_resp)||'</email_response>');
264        end if;
265 
266        -- transform response tag
267        if ( p_trans_resp is not null ) then
268           fnd_file.put_line(fnd_file.output,l_var||'<transform_response>'
269  		   ||lower(p_trans_resp)||'</transform_response>');
270  	  end if;
271 
272        -- transform acknowledgement tag open
273        if ( p_trans_ackn is not null ) then
274           fnd_file.put_line(fnd_file.output,l_var||'<transform_acknowledgement>'
275 	 	   ||lower(p_trans_ackn)||'</transform_acknowledgement>');
276 	   end if;
277 
278        -- secure response tag
279        if ( p_sec_resp is not null ) then
280           fnd_file.put_line(fnd_file.output,l_var||'<secure_response>'
281 	        ||lower(p_sec_resp)||'</secure_response>');
282        end if;
283 
284        -- secure acknowledgement tag
285        if ( p_sec_ackn is not null ) then
286           fnd_file.put_line(fnd_file.output,l_var||'<secure_acknowledgement>'
287            ||lower(p_sec_ackn)||'</secure_acknowledgement>');
288        end if;
289 
290       open c_get_iso_lang;
291       fetch c_get_iso_lang into l_iso_lang;
292       close c_get_iso_lang;
293       l_string := '<lang>'||l_iso_lang||'</lang>';
294       fnd_file.put_line(fnd_file.output,l_var||l_string);
295 
296       l_var := JE_BE_CSSR_PKG.level_down;
297 
298      -- processsing parameters tag close
299      fnd_file.put_line(fnd_file.output,l_var||'</processing_parameters>');
300     else
301      fnd_file.put_line(fnd_file.output,l_var||'<processing_parameters/>');
302     end if;
303   --admin close
304   l_var := JE_BE_CSSR_PKG.level_down;
305   fnd_file.put_line(fnd_file.output,l_var||'</admin>');
306 
307 end get_admin_data;
308 
309 
310 procedure get_content_data
311 (p_survey_code varchar2,
312  p_period      varchar2,
313  p_vat_reporting_entity_id jg_zz_vat_rep_entities.vat_reporting_entity_id%TYPE
314 )
315 is
316   l_level number(15);
317   l_var   varchar2(50);
318   l_reporting_level varchar2(30);
319   l_legal_entity_id jg_zz_vat_rep_entities.legal_entity_id%TYPE;
320   l_ledger_id gl_ledgers.ledger_id%type;
321   l_bsv  varchar2(30);
322 
323   -- ccid and sob
324   cursor get_coa_id (p_sob gl_sets_of_books.set_of_books_id%type)
325   is
326   select CHART_OF_ACCOUNTS_ID
327     from gl_sets_of_books
328   where set_of_books_id = p_sob;
329 
330   cursor get_disp_period (l_rep_period gl_periods.period_name%type)
331   is
332   SELECT glp.period_year||'-'||decode(length(glp.period_num),2,to_char(glp.period_num),'0'||to_char(glp.period_num) ) period_num
333         ,glp.start_date
334         ,glp.end_date
335    	    ,acc.ledger_id
336 	    ,acc.BALANCING_SEGMENT_VALUE
337 	    ,acc.entity_level_code
338 	    ,legal.legal_entity_id
339   FROM gl_periods glp
340       ,jg_zz_vat_rep_entities legal
341       ,jg_zz_vat_rep_entities acc
342   WHERE glp.period_set_name = legal.tax_calendar_name
343   AND acc.vat_reporting_entity_id = p_vat_reporting_entity_id
344   AND  ((acc.entity_type_code = 'ACCOUNTING'
345        AND acc.mapping_vat_rep_entity_id = legal.vat_reporting_entity_id)
346      OR
347      (acc.entity_type_code = 'LEGAL'
348        AND acc.vat_reporting_entity_id = legal.vat_reporting_entity_id)
349       )
350   AND period_name = l_rep_period;
351 
352 
353 
354   TYPE op_curtype IS REF CURSOR;
355   op_curvar op_curtype;
356   l_cntry   varchar2(25);
357   l_cur     varchar2(4);
358   l_rev     number;
359   l_chr     number;
360   l_disp_rev     varchar2(200);
361   l_disp_chr     varchar2(200);
362   l_op_string varchar2(500);
363   l_rub     varchar2(240);
364 
365   l_disp_period  gl_periods.period_name%type;
366   l_sob_id       gl_sets_of_books.set_of_books_id%type;
367   l_coa_id       gl_code_combinations.CHART_OF_ACCOUNTS_ID%type;
368   l_seg_name     fnd_id_flex_segments.application_column_name%type;
369   l_cc_id        gl_code_combinations.code_combination_id%type;
370   l_start_date   date;
371   l_end_date     date;
372   l_status_text  varchar2(2000);
373   l_data_string  varchar2(200);
374 
375   l_sob_var gl_sets_of_books.set_of_books_id%type;
376   l_string varchar2(2000);
377   l_qry_string varchar2(8000);
378   l_cntnt_prsnc_flag   number(1);
379 
380 begin
381 
382  open get_disp_period (p_period );
383  fetch get_disp_period
384  into l_disp_period
385       ,l_start_date
386       ,l_end_date
387       ,l_ledger_id
388       ,l_bsv
389       ,l_reporting_level
390       ,l_legal_entity_id;
391  close get_disp_period;
392 
393  fnd_file.put_line(fnd_file.log,'Reporting level :'||l_reporting_level);
394  fnd_file.put_line(fnd_file.log,'Legal Entity ID :'||l_legal_entity_id);
395  fnd_file.put_line(fnd_file.log,'Ledger ID :'||l_ledger_id);
396  fnd_file.put_line(fnd_file.log,'Balancing Segment Value :'||l_bsv);
397  fnd_file.put_line(fnd_file.log,'Start Date  :'||l_start_date);
398  fnd_file.put_line(fnd_file.log,'End Date :'||l_end_date);
399 
400  l_string := '<content xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" '
401  ||'xsi:type="BbpAcquisitionDataset" survey="' || p_survey_code ||'" per="'
402  || l_disp_period ||'">';
403  l_var := JE_BE_CSSR_PKG.level_down;
404  l_var := JE_BE_CSSR_PKG.level_up;
405  fnd_file.put_line(fnd_file.output,l_var||l_string);
406 
407  IF l_reporting_level <> 'LE' THEN
408     l_sob_id := l_ledger_id;
409 
410   -- get the chart of accounts id for the sets of books
411      l_status_text := ' Deriving Chart of accounts for the SOB '|| l_sob_id;
412 
413      open get_coa_id(l_sob_id);
414      fetch get_coa_id into l_coa_id;
415      close get_coa_id;
416 
417     fnd_file.put_line(fnd_file.log,'Char of account id :'||l_coa_id);
418 
419    -- Get the accounting/natural segment for the chart of accounts defined.
420 
421      l_status_text := ' Get the accounting/natural segment for coa '||l_coa_id;
422      l_seg_name := JE_BE_CSSR_PKG.get_accounting_segment(l_coa_id);
423 
424      fnd_file.put_line(fnd_file.log,'Accounting Segment :'||l_seg_name);
425 
426   END IF;
427 
428    l_var := JE_BE_CSSR_PKG.level_up;
429    l_string := l_var ||'<form code="'||p_survey_code||'"';
430 -- dbms_lob.writeappend(g_xml,length(l_var||l_string),l_var||l_string);
431 -- fnd_file.put_line(fnd_file.output,l_var||l_string);
432 
433  --to op tag level
434  l_var := JE_BE_CSSR_PKG.level_up;
435  l_cntnt_prsnc_flag := 0;
436  -- 4. get a list of defined mappings and get the corresponding ccids.
437  -- 4a. find the totals for AP and AR and produce the codes.
438 
439  IF l_reporting_level = 'LEDGER' THEN
440 
441 	l_qry_string :=
442 	 'SELECT  sum(charges),
443 	        sum(revenue),
444 	        country,
445 	        currency,
446 	        rubic_code
447 	    FROM
448 	        (SELECT sum(nvl(ap_dist.amount,0)) charges,
449                 0 revenue,
450                 ven.country country,
451                 ap_inv.INVOICE_CURRENCY_CODE currency,
452                 ap_inv.invoice_id invoice_id,
453                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
454    	    FROM
455 	        ap_invoices_all ap_inv,
456                 ap_supplier_sites_all ven ,
457                 ap_invoice_distributions_all ap_dist ,
458                 gl_code_combinations glcc,
459                 fnd_lookup_values lv
460 	    WHERE   ap_inv.vendor_site_id            = ven.vendor_site_id
461             AND ap_dist.invoice_id               = ap_inv.invoice_id
462             AND ap_dist.dist_code_combination_id = glcc.code_combination_id
463             AND glcc.chart_of_accounts_id        = :v_coa_id1
464             AND glcc.'||l_seg_name||'         = lv.lookup_code
465             AND lv.lookup_type                   = ''JEBE_NBBN_CODES''
466             AND lv.LANGUAGE                      = USERENV(''LANG'')
467             AND ap_inv.set_of_books_id           = :v_sob_id1
468             AND ven.country                     <> ''BE''
469 	    AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
470 	     	                                 ap_dist.ACCRUAL_POSTED_FLAG,
471 	     	                                 ap_dist.CASH_POSTED_FLAG,
472  	                                         ap_dist.POSTED_FLAG, ap_inv.org_id) in (''Y'',''P'')
473             AND ap_dist.accounting_date BETWEEN :v_start_date1 AND :v_end_date1
474             GROUP BY ap_inv.INVOICE_CURRENCY_CODE,
475                 ven.country ,
476                 ap_inv.invoice_id,
477                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
478                 to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
479             HAVING sum(nvl(ap_dist.base_amount,ap_dist.amount)) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
480             UNION ALL
481           SELECT  0 charges,
482                 sum(nvl(AMOUNT,0)) revenue,
483                 hzl.country country,
484                 invoice_currency_code currency,
485                 trx.customer_trx_id invoice_id,
486                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
487            FROM
488 	        RA_CUST_TRX_LINE_GL_DIST_ALL gld ,
489                 ra_customer_trx_all trx ,
490                 HZ_CUST_ACCOUNTS hz_cust ,
491                 hz_parties parties ,
492                 hz_party_sites hz_ps ,
493                 hz_locations hzl ,
494                 hz_cust_site_uses_all hz_csu ,
495                 hz_cust_acct_sites_all hz_cas ,
496                 gl_code_combinations glcc,
497                 fnd_lookup_values lv
498           WHERE
499 	    gld.customer_trx_id       = trx.customer_trx_id
500             AND trx.BILL_TO_customer_ID   = HZ_CUST.cust_account_id
501             AND hz_cust.party_id          = parties.party_id
502             AND hz_cas.cust_account_id    = HZ_CUST.cust_account_id
503             AND trx.BILL_TO_SITE_USE_ID   = HZ_CSU.SITE_USE_ID
504             AND hz_cas.cust_acct_site_id  = hz_csu.cust_acct_site_id
505             AND hz_ps.party_site_id       = hz_cas.party_site_id
506             AND hz_ps.party_id            = parties.party_id
507             AND hz_ps.location_id         = hzl.location_id
508             AND gld.code_combination_id   = glcc.code_combination_id
509             AND glcc.chart_of_accounts_id = :v_coa_id2
510             AND lv.lookup_type            = ''JEBE_NBBN_CODES''
511             AND glcc.'||l_seg_name||'     = lv.lookup_code
512             AND lv.LANGUAGE               = USERENV(''LANG'')
513             AND trx.set_of_books_id       = :v_sob_id2
514             AND hzl.country              <> ''BE''
515             AND gld.GL_DATE BETWEEN :v_start_date2 AND :v_end_date2
516 	    AND trx.complete_flag = ''Y''
517  	    AND gld.posting_control_id <> -3
518           GROUP BY hzl.COUNTRY,
519                 invoice_currency_code,
520                 trx.customer_trx_id,
521                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
522                 to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
523           HAVING sum(acctd_AMOUNT) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
524            )
525          GROUP BY country, currency, rubic_code ';
526 
527  ELSIF l_reporting_level = 'BSV' THEN
528 
529     l_qry_string :=
530 	 'SELECT  sum(charges),
531 	        sum(revenue),
532 	        country,
533 	        currency,
534 	        rubic_code
535 	    FROM
536 	        (SELECT sum(nvl(ap_dist.amount,0)) charges,
537                 0 revenue,
538                 ven.country country,
539                 ap_inv.INVOICE_CURRENCY_CODE currency,
540                 ap_inv.invoice_id invoice_id,
541                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
542    	    FROM
543 	        ap_invoices_all ap_inv,
544                 ap_supplier_sites_all ven ,
545                 ap_invoice_distributions_all ap_dist ,
546                 gl_code_combinations glcc,
547                 fnd_lookup_values lv
548 	    WHERE   ap_inv.vendor_site_id        = ven.vendor_site_id
549             AND ap_dist.invoice_id               = ap_inv.invoice_id
550             AND ap_dist.dist_code_combination_id = glcc.code_combination_id
551             AND glcc.chart_of_accounts_id        = :v_coa_id1
552             AND glcc.'||l_seg_name||'            = lv.lookup_code
553             AND lv.lookup_type                   = ''JEBE_NBBN_CODES''
554             AND lv.LANGUAGE                      = USERENV(''LANG'')
555             AND ap_inv.set_of_books_id           = :v_sob_id1
556     	    AND JE_BE_CSSR_PKG.get_bsv(ap_inv.set_of_books_id,glcc.chart_of_accounts_id,ap_dist.dist_code_combination_id) = :bsv1
557             AND ven.country                     <> ''BE''
558             AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
559 	                                         ap_dist.ACCRUAL_POSTED_FLAG,
560 	                                         ap_dist.CASH_POSTED_FLAG,
561                                                  ap_dist.POSTED_FLAG, ap_inv.org_id) in (''Y'',''P'')
562             AND ap_dist.accounting_date BETWEEN :v_start_date1 AND :v_end_date1
563             GROUP BY ap_inv.INVOICE_CURRENCY_CODE,
564                 ven.country ,
565                 ap_inv.invoice_id,
566                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
567                 to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
568             HAVING sum(nvl(ap_dist.base_amount,ap_dist.amount)) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
569             UNION ALL
570           SELECT  0 charges,
571                 sum(nvl(AMOUNT,0)) revenue,
572                 hzl.country country,
573                 invoice_currency_code currency,
574                 trx.customer_trx_id invoice_id,
575                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
576            FROM
577 	        RA_CUST_TRX_LINE_GL_DIST_ALL gld ,
578                 ra_customer_trx_all trx ,
579                 HZ_CUST_ACCOUNTS hz_cust ,
580                 hz_parties parties ,
581                 hz_party_sites hz_ps ,
582                 hz_locations hzl ,
583                 hz_cust_site_uses_all hz_csu ,
584                 hz_cust_acct_sites_all hz_cas ,
585                 gl_code_combinations glcc,
586                 fnd_lookup_values lv
587           WHERE
588 	    gld.customer_trx_id       = trx.customer_trx_id
589             AND trx.BILL_TO_customer_ID   = HZ_CUST.cust_account_id
590             AND hz_cust.party_id          = parties.party_id
591             AND hz_cas.cust_account_id    = HZ_CUST.cust_account_id
592             AND trx.BILL_TO_SITE_USE_ID   = HZ_CSU.SITE_USE_ID
593             AND hz_cas.cust_acct_site_id  = hz_csu.cust_acct_site_id
594             AND hz_ps.party_site_id       = hz_cas.party_site_id
595             AND hz_ps.party_id            = parties.party_id
596             AND hz_ps.location_id         = hzl.location_id
597             AND gld.code_combination_id   = glcc.code_combination_id
598             AND glcc.chart_of_accounts_id = :v_coa_id2
599             AND lv.lookup_type            = ''JEBE_NBBN_CODES''
600             AND glcc.'||l_seg_name||'     = lv.lookup_code
601             AND lv.LANGUAGE               = USERENV(''LANG'')
602             AND trx.set_of_books_id       = :v_sob_id2
603 	    AND JE_BE_CSSR_PKG.get_bsv(trx.set_of_books_id,glcc.chart_of_accounts_id,gld.code_combination_id) = :bsv2
604             AND hzl.country              <> ''BE''
605             AND gld.GL_DATE BETWEEN :v_start_date2 AND :v_end_date2
606             AND trx.complete_flag = ''Y''
607 	    AND gld.posting_control_id <> -3
608           GROUP BY hzl.COUNTRY,
609                 invoice_currency_code,
610                 trx.customer_trx_id,
611                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
612                 to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
613           HAVING sum(acctd_AMOUNT) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
614            )
615          GROUP BY country, currency, rubic_code ';
616 
617  ELSIF l_reporting_level = 'LE' THEN
618 
619      l_qry_string :=
620 
621 	'SELECT  sum(charges),
622         sum(revenue),
623         country,
624         currency,
625         rubic_code
626     FROM
627         (SELECT sum(nvl(ap_dist.amount,0)) charges,
628                 0 revenue,
629                 ven.country country,
630                 ap_inv.INVOICE_CURRENCY_CODE currency,
631                 ap_inv.invoice_id invoice_id,
632                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
633         FROM    ap_invoices_all ap_inv,
634                 ap_supplier_sites_all ven ,
635                 ap_invoice_distributions_all ap_dist ,
636                 fnd_lookup_values lv,
637                 gl_ledgers glr
638         WHERE   ap_inv.legal_entity_id           = :p_legal_entity_id1
639             AND ap_inv.vendor_site_id            = ven.vendor_site_id
640             AND ap_dist.invoice_id               = ap_inv.invoice_id
641             AND glr.ledger_id                    = ap_inv.set_of_books_id
642             AND JE_BE_CSSR_PKG.get_accounting_segment(glr.chart_of_accounts_id,ap_dist.dist_code_combination_id)   = lv.lookup_code
643             AND lv.lookup_type                   = ''JEBE_NBBN_CODES''
644             AND lv.LANGUAGE                      = USERENV(''LANG'')
645             AND ven.country                     <> ''BE''
646             AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
647 	                                         ap_dist.ACCRUAL_POSTED_FLAG,
648 	                                         ap_dist.CASH_POSTED_FLAG,
649                                                  ap_dist.POSTED_FLAG, ap_inv.org_id) in (''Y'',''P'')
650             AND ap_dist.accounting_date BETWEEN :v_start_date1 AND :v_end_date1
651         GROUP BY ap_inv.INVOICE_CURRENCY_CODE,
652                 ven.country ,
653                 ap_inv.invoice_id,
654                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
655                 to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
656         HAVING sum(nvl(ap_dist.base_amount,ap_dist.amount)) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
657         UNION ALL
658         SELECT  0 charges,
659                 sum(nvl(AMOUNT,0)) revenue,
660                 hzl.country country,
661                 invoice_currency_code currency,
662                 trx.customer_trx_id invoice_id,
663                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
664         FROM    ra_cust_trx_line_gl_dist_all gld ,
665                 ra_customer_trx_all  trx ,
666                 hz_cust_accounts     hz_cust,
667                 hz_cust_site_uses_all   hz_csu,
668                 hz_cust_acct_sites_all  hz_cas,
669                 hz_parties           parties,
670                 hz_party_sites       hz_ps,
671                 hz_locations         hzl,
672                 fnd_lookup_values lv,
673                 gl_ledgers glr
674         WHERE   trx.legal_entity_id       = :p_legal_entity_id2
675 	    AND gld.customer_trx_id       = trx.customer_trx_id
676             AND trx.bill_to_customer_id   = hz_cust.cust_account_id
677             AND hz_cust.party_id          = parties.party_id
678             AND hz_cas.cust_account_id    = hz_cust.cust_account_id
679             AND trx.bill_to_site_use_id   = hz_csu.site_use_id
680             AND hz_cas.cust_acct_site_id  = hz_csu.cust_acct_site_id
681             AND hz_ps.party_site_id       = hz_cas.party_site_id
682             AND hz_ps.party_id            = parties.party_id
683             AND hz_ps.location_id         = hzl.location_id
684             AND trx.set_of_books_id       = glr.ledger_id
685             AND lv.lookup_type            = ''JEBE_NBBN_CODES''
686             AND lv.LANGUAGE               = USERENV(''LANG'')
687             AND hzl.country              <> ''BE''
688             AND gld.GL_DATE BETWEEN :v_start_date2 AND :v_end_date2
689             AND trx.complete_flag = ''Y''
690 	    AND gld.posting_control_id <> -3
691             AND JE_BE_CSSR_PKG.get_accounting_segment(glr.chart_of_accounts_id, gld.code_combination_id ) = lv.lookup_code
692         GROUP BY hzl.COUNTRY,
693                 invoice_currency_code,
694                 trx.customer_trx_id,
695                 decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
696                 to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
697         HAVING sum(acctd_AMOUNT) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
698         )
699     GROUP BY country, currency, rubic_code ';
700  END IF;
701 
702 IF  l_reporting_level = 'LE' THEN
703 
704   OPEN op_curvar FOR l_qry_string USING l_legal_entity_id,
705                                         l_start_date,
706                                         l_end_date,
707 					l_legal_entity_id,
708 					l_start_date,
709                                         l_end_date;
710 
711 ELSIF l_reporting_level = 'LEDGER' THEN
712 
713   OPEN op_curvar FOR l_qry_string USING l_coa_id,
714                                         l_sob_id,
715                                         l_start_date,
716                                         l_end_date,
717                                         l_coa_id,
718                                         l_sob_id,
719                                         l_start_date,
720                                         l_end_date;
721 
722 ELSIF l_reporting_level = 'BSV' THEN
723 
724   OPEN op_curvar FOR l_qry_string USING l_coa_id,
725                                         l_sob_id,
726 					l_bsv,
727                                         l_start_date,
728                                         l_end_date,
729                                         l_coa_id,
730                                         l_sob_id,
731 					l_bsv,
732                                         l_start_date,
733                                         l_end_date;
734 END IF;
735 
736   loop
737    fetch op_curvar into l_chr,l_rev,l_cntry,l_cur,l_rub;
738    exit when op_curvar%notfound;
739    /* -ve values should not be present in the report */
740    /* -ve chr should be + ve rev and -ve rev should be +ve chr */
741    /* Amounts should be rounded. No digits after decimal.*/
742    IF l_rev >= 0 AND l_chr >= 0 THEN
743     l_disp_rev := to_char(round(l_rev));
744     l_disp_chr := to_char(round(l_chr));
745    ELSIF l_rev >= 0 AND l_chr <= 0 THEN
746     l_disp_rev := to_char(round(l_rev + (l_chr * -1)));
747     l_disp_chr := '0';
748    ELSIF l_rev <= 0 AND l_chr >= 0 THEN
749     l_disp_rev := '0';
750     l_disp_chr := to_char(round(l_chr + (l_rev * -1)));
751    ELSIF l_rev <= 0 and l_chr <= 0 THEN
752     l_disp_rev := to_char(round(l_chr));
753     l_disp_chr := to_char(round(l_rev));
754    END IF;
755 
756    l_op_string := '<op rub="'||rtrim(l_rub)||'"'||
757                   ' cntry="' || l_cntry ||'"'||
758                   ' cur="' || l_cur ||'"'||
759                   ' rev="' || l_disp_rev ||'"'||
760    	 		      ' chr="' || l_disp_chr ||'"'||
761 				  ' />' ;
762 
763     if ( l_cntnt_prsnc_flag = 0) then
764        l_cntnt_prsnc_flag := 1;
765        fnd_file.put_line(fnd_file.output,l_string ||' nihil = "false">');
766     end if;
767     fnd_file.put_line(fnd_file.output,l_var||l_op_string);
768   end loop;
769   CLOSE op_curvar;
770 -- end loop;
771  --to form tag level
772  l_var := JE_BE_CSSR_PKG.level_down;
773   if( l_cntnt_prsnc_flag = 1 ) then
774    l_string := '</form>';
775    fnd_file.put_line(fnd_file.output,l_var||l_string);
776   else
777    fnd_file.put_line(fnd_file.output,l_string ||' nihil = "true" />');
778   end if;
779 
780  --content tag close
781  l_var := JE_BE_CSSR_PKG.level_down;
782  fnd_file.put_line(fnd_file.output,l_var||'</content>');
783 
784  --main close
785  l_var := JE_BE_CSSR_PKG.level_down;
786  fnd_file.put_line(fnd_file.output,'</cssr_document>');
787 
788 end get_content_data;
789 
790 function level_up
791  return varchar2
792 is
793  l_var varchar2(200);
794 begin
795  g_vc_spacing := g_vc_spacing+1;
796  l_var := ' ';--fnd_global.newline();
797  for i in 1..g_vc_spacing-1 loop
798    l_var :=l_var||'  ';
799  end loop;
800 return l_var;
801 end level_up;
802 
803 function level_down return varchar2
804 is
805  l_var varchar2(200);
806 begin
807  g_vc_spacing := g_vc_spacing-1;
808  l_var :=' ';-- fnd_global.newline();
809  for i in 1..g_vc_spacing-1 loop
810    l_var :=l_var||'  ';
811  end loop;
812 return l_var;
813 end level_down;
814 
815 /*
816 REM +======================================================================+
817 REM Name: get_bsv
818 REM
819 REM Description: This function is called in the generic cursor for getting the
820 REM              BSV for each invoice distribution.
821 REM
822 REM
823 REM Parameters:  ccid  (code combination id)
824 REM
825 REM +======================================================================+
826 */
827 
828 FUNCTION get_bsv(p_ledger_id number,p_choac_id number,p_cc_id number) RETURN VARCHAR2 IS
829 
830 l_segment VARCHAR2(30);
831 bal_segment_value VARCHAR2(25);
832 
833 BEGIN
834 
835   SELECT application_column_name
836   INTO   l_segment
837   FROM   fnd_segment_attribute_values
838   WHERE    id_flex_code               = 'GL#'
839     AND    attribute_value            = 'Y'
840     AND    segment_attribute_type     = 'GL_BALANCING'
841     AND    application_id             = 101
842     AND    id_flex_num = p_choac_id;
843 
844   EXECUTE IMMEDIATE 'SELECT '||l_segment ||
845                   ' FROM gl_code_combinations '||
846                   ' WHERE code_combination_id = '||p_cc_id
847   INTO bal_segment_value;
848 
849   RETURN (bal_segment_value);
850 
851 EXCEPTION
852     WHEN NO_DATA_FOUND THEN
853       fnd_file.put_line(fnd_file.log,' Exception in get_bsv function : ' || SUBSTR(SQLERRM,1,200));
854       RETURN NULL;
855 
856 END get_bsv;
857 
858 /*
859 REM +======================================================================+
860 REM Name: get_accounting_segment
861 REM
862 REM Description: This function is called in the generic cursor for getting the
863 REM              Accounting Segment for each invoice distribution.
864 REM
865 REM
866 REM Parameters:  p_coa_id  (Chart of account id)
867 REM
868 REM +======================================================================+
869 */
870 
871 FUNCTION get_accounting_segment(p_coa_id  number,p_cc_id number default null) RETURN VARCHAR2 IS
872 
873 l_segment VARCHAR2(30);
874 l_segment_value VARCHAR(30);
875 
876 
877 BEGIN
878 
879 --Get the accounting/natural segment for the chart of accounts defined.
880 
881    SELECT application_column_name
882     INTO l_segment
883    FROM FND_SEGMENT_ATTRIBUTE_VALUES
884    WHERE id_flex_num            = p_coa_id --50714
885    AND segment_attribute_type = 'GL_ACCOUNT'
886    AND id_flex_code           = 'GL#'
887    AND attribute_value        = 'Y'
888    AND application_id         = 101;
889 
890   IF p_cc_id IS NOT NULL THEN
891 
892    EXECUTE IMMEDIATE 'SELECT '||l_segment ||
893                   ' FROM gl_code_combinations '||
894                   ' WHERE code_combination_id = '||p_cc_id||
895                   ' AND chart_of_accounts_id = '||p_coa_id
896   INTO l_segment_value;
897 
898     RETURN (l_segment_value);
899 
900   END IF;
901 
902   RETURN (l_segment);
903 
904 EXCEPTION
905     WHEN NO_DATA_FOUND THEN
906       fnd_file.put_line(fnd_file.log,' No record was returned for the Accounting segment. Error : ' || SUBSTR(SQLERRM,1,200));
907       RETURN NULL;
908 
909 END get_accounting_segment;
910 
911 
912 end JE_BE_CSSR_PKG;