[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;