[Home] [Help]
PACKAGE BODY: APPS.IEX_PROMISE_RECON_RPT_PKG
Source
1 package body iex_promise_recon_rpt_pkg as
2 /* $Header: iexprcrb.pls 120.17.12020000.2 2012/07/24 19:04:20 schekuri ship $ */
3
4 G_PKG_NAME varchar2(100):='iex_promise_recon_rpt_pkg';
5 --l_api_name CONSTANT VARCHAR2(30) := 'Promise Reconciliation';
6
7 l_res_hash l_res_hash_type;
8 l_pmt_cnt l_pmt_cnt_type;
9 l_pmt_amt l_pmt_amt_type;
10 g_base_curr varchar2(10) default null;
11 G_LOG_ENABLED varchar2(5);
12 G_MSG_LEVEL NUMBER;
13
14
15 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
16 IS
17 BEGIN
18 if (p_msg_level >= G_MSG_LEVEL) then
19
20 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
21
22 end if;
23
24 if FND_GLOBAL.Conc_Request_Id is not null then
25 fnd_file.put_line(FND_FILE.LOG, p_msg);
26 end if;
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
31 END;
32
33
34 PROCEDURE PRINT_CLOB (lob_loc in clob) IS
35
36 /*-----------------------------------------------------------------------+
37 | Local Variable Declarations and initializations |
38 +-----------------------------------------------------------------------*/
39
40 l_api_name CONSTANT VARCHAR2(30) := 'PRINT_CLOB';
41 l_api_version CONSTANT NUMBER := 1.0;
42 c_endline CONSTANT VARCHAR2 (1) := '
43 ';
44 c_endline_len CONSTANT NUMBER := LENGTH (c_endline);
45 l_start NUMBER := 1;
46 l_end NUMBER;
47 l_one_line VARCHAR2 (7000);
48 l_charset VARCHAR2(100);
49
50 /*-----------------------------------------------------------------------+
51 | Cursor Declarations |
52 +-----------------------------------------------------------------------*/
53 BEGIN
54 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
55
56 LOOP
57 l_end :=
58 DBMS_LOB.INSTR (lob_loc => lob_loc,
59 pattern => c_endline,
60 offset => l_start,
61 nth => 1
62 );
63
64 FND_FILE.put_line( FND_FILE.LOG,'l_end-->'||l_end);
65
66
67 IF (NVL (l_end, 0) < 1)
68 THEN
69 EXIT;
70 END IF;
71
72 l_one_line :=
73 DBMS_LOB.SUBSTR (lob_loc => lob_loc,
74 amount => l_end - l_start,
75 offset => l_start
76 );
77 FND_FILE.put_line( FND_FILE.LOG,'l_one_line-->'||l_one_line);
78 FND_FILE.put_line( FND_FILE.LOG,'c_endline_len-->'||c_endline_len);
79 l_start := l_end + c_endline_len;
80 FND_FILE.put_line( FND_FILE.LOG,'l_start-->'||l_start);
81 FND_FILE.put_line( FND_FILE.LOG,'32');
82 Fnd_File.PUT_line(Fnd_File.OUTPUT,l_one_line);
83
84 END LOOP;
85
86 END PRINT_CLOB;
87
88
89 Procedure gen_xml_data(ERRBUF OUT NOCOPY VARCHAR2,
90 RETCODE OUT NOCOPY VARCHAR2,
91 p_org_id in number,
92 p_date_from in varchar2,
93 p_date_to in varchar2,
94 p_currency in varchar2,
95 p_pro_state in varchar2,
96 p_pro_status in varchar2,
97 p_summ_det in varchar2,
98 p_group_by in varchar2,
99 p_group_by_mode in varchar2,
100 p_group_by_coll_dumm in varchar2 default null,
101 p_group_by_value_coll in varchar2 default null,
102 p_group_by_sch_dumm in varchar2 default null,
103 p_group_by_value_sch in varchar2 default null
104 )
105 is
109 result CLOB;
106 l_api_name CONSTANT VARCHAR2(30) := 'gen_xml_data';
107 l_api_version CONSTANT NUMBER := 1.0;
108 ctx DBMS_XMLQUERY.ctxType;
110 qryCtx DBMS_XMLQUERY.ctxHandle;
111 l_result CLOB;
112 tempResult CLOB;
113 l_where varchar2(8000):='';
114 l_group_by varchar2(4000);
115 l_order_by varchar2(4000);
116 l_res_id number;
117 l_version varchar2(20);
118 l_compatibility varchar2(20);
119 l_suffix varchar2(2);
120 l_majorVersion number;
121 l_resultOffset number;
122 l_xml_header clob;
123 l_xml_header_length number;
124 l_errNo NUMBER;
125 l_errMsg VARCHAR2(200);
126 queryCtx DBMS_XMLquery.ctxType;
127 l_xml_query VARCHAR2(32767);
128 TYPE ref_cur IS REF CURSOR;
129 l_xml_stmt ref_cur;
130 l_rows_processed NUMBER;
131 l_new_line VARCHAR2(1);
132 l_close_tag VARCHAR2(100);
133 l_res_cnt number;
134 l_res_qry varchar2(5000);
135 l_pro_status varchar2(20);
136 l_pro_state varchar2(20);
137 -- l_ctr_enbl_flg varchar2(1); --commented for bug 10054672 gnramasa 27th Aug 10
138 l_coll_rate varchar2(20);
139 l_query varchar2(11000);
140 l_org_id varchar2(100);
141 l_no_data_flag number;
142 l_collector varchar2(200);
143 l_campaign varchar2(200);
144 l_sysdate date;
145 l_status boolean;
146 l_encoding VARCHAR2(100); --Added for bug 10213070 snuthala 19th Oct 10
147 l_from_date date;
148 l_to_date date;
149
150 begin
151
152 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
153
154 -- p_group_by_value_coll condition is added for bug 13844678 by bibeura
155 IF ( p_group_by_value_coll IS NULL AND p_group_by_mode = 'GROUP' ) THEN
156 FND_FILE.put_line( FND_FILE.LOG,'*******************************************************************************************************************************');
157 FND_FILE.PUT_LINE( FND_FILE.LOG,'* ERROR: You can''t run this report without Collector name at Group Level. *');
158 FND_FILE.PUT_LINE( FND_FILE.LOG,'* Collector Name is a mandatory parameter when report is submitted with Group Level. *');
159 FND_FILE.PUT_LINE( FND_FILE.LOG,'* Please select Collector name from the lov. *');
160 FND_FILE.put_line( FND_FILE.LOG,'*******************************************************************************************************************************');
161
162 l_status := fnd_concurrent.set_completion_status (status => 'ERROR',
163 message => 'Collector name is missing');
164 return;
165 END IF;
166 l_from_date := TRUNC(fnd_date.canonical_to_date(p_date_from));
167 l_to_date := TRUNC(fnd_date.canonical_to_date(p_date_to));
168
169 if p_date_from is not null and p_date_to is not null then
170 l_where:=l_where||' and ipd1.promise_date between(to_date('''||l_from_date||''', ''DD-MM-RR'')) and (to_date('''||l_to_date||''',''DD-MM-RR''))';
171 end if;
172
173 --Start for bug 10054672 gnramasa 27th Aug 10
174 /*
175 select NVL(FND_PROFILE.value('IEX_LEASE_ENABLED'), 'N')
176 into l_ctr_enbl_flg
177 from dual;
178
179 if l_ctr_enbl_flg='N' then
180 l_where :=l_where||' and ipd1.contract_id is null';
181 else
182 l_where :=l_where||' and ipd1.contract_id is not null';
183 end if;
184 */
185
186 l_where :=l_where||' and ipd1.contract_id is null';
187
188 --End for bug 10054672 gnramasa 27th Aug 10
189
190 select nvl(fnd_profile.value('IEX_COLLECTIONS_RATE_TYPE'),'N')
191 into l_coll_rate
192 from dual;
193
194 if l_coll_rate is null then
195 l_coll_rate:='Corporate';
196 end if;
197
198 if p_org_id is not null then
199 l_where:=l_where||' and ipd1.org_id='||p_org_id;
200 select name
201 into l_org_id
202 from hr_operating_units
203 where organization_id=p_org_id;
204 else
205 l_org_id:='All';
206 end if;
207
208 if p_pro_status is null then
209 l_pro_status:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
210 FND_FILE.put_line( FND_FILE.LOG,'2.5');
211 null;
212 else
213 FND_FILE.put_line( FND_FILE.LOG,'3');
214 -- l_pro_status:=p_pro_status;
215 l_where:=l_where||' and ipd1.status='''||p_pro_status||'''';
216 select meaning
217 into l_pro_status
218 from iex_lookups_v
219 where lookup_code=p_pro_status
220 and lookup_type='IEX_PROMISE_STATUSES';
221 end if;
222
223 if p_pro_state is null then
224 l_pro_state:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
225 FND_FILE.put_line( FND_FILE.LOG,'3.5');
226 null;
227 else
228 FND_FILE.put_line( FND_FILE.LOG,'4');
229 -- l_pro_state:=p_pro_state;
230 l_where:=l_where||' and ipd1.state='''||p_pro_state||'''';
231 select meaning
232 into l_pro_state
233 from iex_lookups_v
234 where lookup_code=p_pro_state
235 and lookup_type='IEX_PROMISE_STATES';
236 end if;
237
238 if p_group_by='COLLECTOR' then
239 FND_FILE.put_line( FND_FILE.LOG,'4.5');
240 if p_group_by_value_coll is not null then
241 select source_name
242 into l_collector
243 from jtf_rs_resource_extns
244 where resource_id=p_group_by_value_coll;
245
246 if p_group_by_mode='GROUP' then
247 select distinct count(resource_id)
248 into l_res_cnt
249 from
250 JTF_RS_RESOURCE_EXTNS
251 where source_id in ( select distinct b.person_id
252 from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a
253 where b.manager_person_id = a.source_id
254 and a.resource_id = p_group_by_value_coll);--100000937);
255
256 if l_res_cnt <> 0 then
257 l_res_qry:= '(select distinct resource_id '||
258 'from '||
259 'JTF_RS_RESOURCE_EXTNS '||
260 'where source_id in (select distinct b.person_id '||
261 'from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a '||
262 'where b.manager_person_id = a.source_id '||
263 'and a.resource_id = '||p_group_by_value_coll;
264
265 l_where:=l_where||' and ipd1.resource_id in '|| l_res_qry ;
266 l_where:=l_where||'))';
267 else
268 FND_FILE.put_line( FND_FILE.LOG,'This Collector can see only promises assigned to him');
269 l_where:=l_where||' and ipd1.resource_id= '|| p_group_by_value_coll;
270 end if;
271 else
272 FND_FILE.put_line( FND_FILE.LOG,'4.6');
273 l_where:=l_where||' and ipd1.resource_id= '|| p_group_by_value_coll;
274 end if;
275 end if;
276 end if;
277
278 if p_group_by='SCHEDULE' then
279 FND_FILE.put_line( FND_FILE.LOG,'5');
280 if p_group_by_value_sch is not null then
281 select source_code
282 into l_campaign
283 from ams_source_codes
284 where source_code_id=p_group_by_value_sch;
285
286
287 FND_FILE.put_line( FND_FILE.LOG,'6');
288 l_where:=l_where||' and ipd1.campaign_sched_id ='||p_group_by_value_sch;
289 end if;
290 end if;
291 l_where:=l_where||' and (ipd1.campaign_sched_id in (Select source_code_id '||
292 'from ams_source_codes '||
293 'where arc_source_code_for =''CAMP'') '||
294 'OR IPD1.campaign_sched_id is NULL)';
295
296 -- FND_FILE.put_line( FND_FILE.LOG,'base_curr'||g_base_curr);
297
298 --end of get filters
299
300 /*if p_group_by_value_coll is null then
301 l_collector:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
302 end if;*/
303 if p_group_by_value_sch is null then
304 l_campaign:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
305 end if;
306
307 --group_by
308 if p_group_by='SCHEDULE' then
309 l_group_by:= ' group by amsc.source_code_id,amsc.source_code,jrev.resource_id,jrev.resource_name)';
310 else
311 l_group_by:=' group by jrev.resource_id,jrev.resource_name,amsc.source_code_id,amsc.source_code)';
312 end if;
313
314 --report mode DETAIL
315 --For Bug 9054660 28-Oct-2009 barathsr..
316 --1)Modified the filter clause by removing the ipax.reversed_flag check to see promises of reversed payments.
317 --2)Used decode for payment related columns to void the payment details for reversed payments.
318
319 if p_summ_det = 'DETAIL' then
320 l_query:=
321 'select '||
322 'resource_id,'||
323 'resource_name,'||
324 'source_code_id,'||
325 'source_code,'||
326 'account_number,'||
327 'invoice,'||
328 'installment_number,'||
329 'promise_status,'||
330 'p_amt,'||
331 'p_itemno,'||
332 'p_origdt,'||
333 'p_exp_pmtdt,'||
334 'remaining_balance,'||
335 'pmt_dt,'||
336 'pmt_amt,'||
337 'pmt_type,'||
338 'pmt_itemno,'||
339 'currency,'||
340 'promise_detail_id,'||
341 'promise_state '||
342 'from (select jrev.resource_id resource_id,'||
343 'IEX_UTILITIES.REPLACE_SPECIAL_CHARS(jrev.resource_name) resource_name, amsc.source_code_id source_code_id,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(amsc.source_code) source_code,'||
344 'IEX_UTILITIES.REPLACE_SPECIAL_CHARS(hca.account_number) account_number,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(aps.trx_number) invoice, aps.terms_sequence_number installment_number,'||
345 'iex_utilities.get_lookup_meaning(''IEX_PROMISE_STATUSES'',ipd1.status) promise_status,'||
346 'gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.promise_amount) p_amt,'||
347 'ipd1.promise_item_number p_itemno,to_char(ipd1.creation_date,''YYYY-MM-DD'')p_origdt, to_char(ipd1.promise_date,''YYYY-MM-DD'')p_exp_pmtdt,'||
348 'gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.amount_due_remaining) remaining_balance,'||
349 'decode(nvl(ipax.reversed_flag,''N''),''N'',to_char(ara.apply_date,''YYYY-MM-DD''),null)pmt_dt,'||
350 'decode(nvl(ipax.reversed_flag,''N''),''N'',gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipax.amount_applied),null)pmt_amt,'||
354 'from '||
351 'decode(nvl(ipax.reversed_flag,''N''),''N'',IEX_UTILITIES.REPLACE_SPECIAL_CHARS(acr.payment_method_dsp),null) pmt_type, decode(nvl(ipax.reversed_flag,''N''),''N'',IEX_UTILITIES.REPLACE_SPECIAL_CHARS(acr.receipt_number),null) pmt_itemno,'||
352 'ipd1.currency_code currency, ipd1.promise_detail_id promise_detail_id,'||
353 'iex_utilities.get_lookup_meaning(''IEX_PROMISE_STATES'',ipd1.state) promise_state '||
355 'iex_promise_details ipd1, ams_source_codes amsc, ar_cash_receipts_v acr, iex_prd_appl_xref ipax,'||
356 'ar_receivable_applications ara, hz_cust_accounts hca, jtf_rs_resource_extns_vl jrev, iex_delinquencies id, ar_payment_schedules aps,ar_system_parameters asp '||
357 ' where '||
358 'ipd1.promise_detail_id=ipax.promise_detail_id(+) and ipax.receivable_application_id=ara.receivable_application_id(+) '||
359 'and ara.cash_receipt_id=acr.cash_receipt_id(+) '||
360 'and aps.payment_schedule_id(+)=id.payment_schedule_id and jrev.resource_id(+)=ipd1.resource_id and ipd1.org_id=asp.org_id '||
361 'and amsc.source_code_id(+)=ipd1.campaign_sched_id '||
362 'and ipd1.cust_account_id=hca.cust_account_id '||
363 'and id.delinquency_id(+)=ipd1.delinquency_id ';
364 -- Used function REPLACE_SPECIAL_CHARS for bug 13594457
365
366 l_order_by:=' order by ipd1.promise_detail_id,jrev.resource_name,amsc.source_code,ipd1.cust_account_id,ipd1.promise_amount)';
367 l_query:=l_query||l_where;
368 l_query:=l_query||l_order_by;
369 FND_FILE.put_line( FND_FILE.LOG,'qry-->'||l_query);
370 ctx := DBMS_XMLQUERY.newContext(l_query);
371 end if;
372
373 --report mode SUMMARY
374 if p_summ_det = 'SUMMARY' then
375 l_query:='select '||
376 'source_code_id,'||
377 'source_code,'||
378 'resource_id,'||
379 'resource_name,'||
380 'ptp_count,'||
381 'ptp_amt,'||
382 'pmt_count,'||
383 'pmt_amt,'||
384 'broken_count,'||
385 'broken_amt,'||
386 'open_count,'||
387 'open_amt '||
388 'from '||
389 '(select amsc.source_code_id source_code_id,'||
390 'IEX_UTILITIES.REPLACE_SPECIAL_CHARS(amsc.source_code) source_code,'||
391 'jrev.resource_id resource_id, IEX_UTILITIES.REPLACE_SPECIAL_CHARS(jrev.resource_name) resource_name,'||
392 'count(ipd1.promise_detail_id) ptp_count,'||
393 'sum(gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.promise_amount))ptp_amt,'||
394 'iex_promise_recon_rpt_pkg.get_pmt_count(jrev.resource_id,amsc.source_code_id) pmt_count,'||
395 'iex_promise_recon_rpt_pkg.get_pmt_amount(jrev.resource_id,amsc.source_code_id) pmt_amt,'||
396 'sum(decode(ipd1.state,''BROKEN_PROMISE'',1,0))broken_count,'||
397 'sum(decode(ipd1.state,''BROKEN_PROMISE'',gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.promise_amount),0))broken_amt,'||
398 'sum(decode(ipd1.state,''PROMISE'',1,0))open_count,'||
399 'sum(decode(ipd1.state,''PROMISE'',gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.promise_amount),0))open_amt '||
400 'from '||
401 'iex_promise_details ipd1,jtf_rs_resource_extns_vl jrev,ar_system_parameters asp,'||
402 'ams_source_codes amsc,hz_cust_accounts hzca '||
403 'where jrev.resource_id(+)=ipd1.resource_id '||
404 'and amsc.source_code_id(+)=ipd1.campaign_sched_id '||
405 'and ipd1.cust_account_id=hzca.cust_account_id and ipd1.org_id=asp.org_id ';
406
407
408 l_query:=l_query||l_where;
409 l_query:=l_query||l_group_by;
410
411 --call procedure to calculate pmt_cnt and pmt_amt
412 -- changed for bug 12687380 PNAVEENK
413 IF p_group_by_value_coll IS NOT NULL then
414 calc_pmt_amt_cnt(p_org_id,l_from_date,l_to_date,p_currency,p_pro_state,p_pro_status,
415 p_group_by,p_group_by_mode,p_group_by_value_coll,p_group_by_value_sch);
416 END IF;
417 -- end if;
418
419 FND_FILE.put_line( FND_FILE.LOG,'l_query_summ-->'||l_query);
420 ctx := DBMS_XMLQUERY.newContext(l_query);
421 FND_FILE.put_line( FND_FILE.LOG,'7.5');
422
423 end if;
424
425 DBMS_XMLQUERY.setRaiseNoRowsException(ctx,TRUE);
426
427 -- Bind Mandatory Variables
428 -- DBMS_XMLQUERY.setBindValue(ctx, 'p_date_from', p_date_from);
429 -- DBMS_XMLQUERY.setBindValue(ctx, 'p_date_to', p_date_to);
430 -- DBMS_XMLQUERY.setBindValue(ctx, 'p_currency', p_currency);
431 -- DBMS_XMLQUERY.setBindValue(ctx, 'p_currency1', p_currency);
432 -- DBMS_XMLQUERY.setBindValue(ctx, 'p_currency2', p_currency);
433 -- DBMS_XMLQUERY.setBindValue(ctx, 'p_pro_status', p_pro_status);
434
435 --get the result
436 BEGIN
437 l_result := DBMS_XMLQUERY.getXML(ctx);
438 DBMS_XMLQUERY.closeContext(ctx);
439 l_rows_processed := 1;
440
441 EXCEPTION
442 WHEN OTHERS THEN
443 DBMS_XMLQUERY.getExceptionContent(ctx,l_errNo,l_errMsg);
444 IF l_errNo = 1403 THEN
445 l_rows_processed := 0;
446 --l_no_data_flag:=0;
447 END IF;
448 DBMS_XMLQUERY.closeContext(ctx);
449 END;
450
451 IF l_rows_processed <> 0 THEN
452 FND_FILE.put_line( FND_FILE.LOG,'8') ;
453 --get the length og the rowset header
454 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
455 FND_FILE.put_line( FND_FILE.LOG,'9') ;
456 ELSE
457 l_resultOffset := 0;
458 END IF;
459
460 select TRUNC(sysdate)
461 into l_sysdate
462 from dual;
463
464 l_new_line := '
465 ';
466 FND_FILE.put_line( FND_FILE.LOG,'10') ;
467 /* Prepare the tag for the report heading */
468 --Start adding for bug 10213070 snuthala 19th Oct 10
469 --l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
470 -- Instead of hard coding the value, pick the charcter set value from "ICX: Client IANA Encoding" profile.
471 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
472 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
473 --End adding for bug 10213070 snuthala 19th Oct 10
474 l_xml_header := l_xml_header ||l_new_line||'<PROMRECONCILIATION>';
475 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>'||l_new_line;
476 l_xml_header := l_xml_header ||l_new_line||' <P_DATE_FROM>'||TO_CHAR(l_from_date,'YYYY-MM-DD')||'</P_DATE_FROM>';
477 l_xml_header := l_xml_header ||l_new_line||' <P_DATE_TO>' ||TO_CHAR(l_to_date,'YYYY-MM-DD') ||'</P_DATE_TO>';
478 l_xml_header := l_xml_header ||l_new_line||' <P_CURRENCY>' ||p_currency||'</P_CURRENCY>';
479 l_xml_header := l_xml_header ||l_new_line||' <P_PRO_STATE>' ||l_pro_state ||'</P_PRO_STATE>';
480 l_xml_header := l_xml_header ||l_new_line||' <P_PRO_STATUS>' ||l_pro_status||'</P_PRO_STATUS>';
481 l_xml_header := l_xml_header ||l_new_line||' <P_REPORT_TYPE>' ||iex_utilities.get_lookup_meaning('IEX_REPORT_MODE',p_summ_det)||'</P_REPORT_TYPE>';
482 l_xml_header := l_xml_header ||l_new_line||' <P_REPORT_CODE>' ||p_summ_det||'</P_REPORT_CODE>'; -- added for bug 13789323 by sunagesh
483 l_xml_header := l_xml_header ||l_new_line||' <P_GROUP_BY>' ||iex_utilities.get_lookup_meaning('IEX_REP_COLL_SCH',p_group_by)||'</P_GROUP_BY>';
484 l_xml_header := l_xml_header ||l_new_line||' <P_GROUP_BY_MODE>' ||p_group_by_mode||'</P_GROUP_BY_MODE>'; -- Added for bug 13844678 by bibeura
485 l_xml_header := l_xml_header ||l_new_line||' <P_REPORT_LEVEL>' ||iex_utilities.get_lookup_meaning('IEX_REPORT_LEVEL',p_group_by_mode)||'</P_REPORT_LEVEL>';
486 l_xml_header := l_xml_header ||l_new_line||' <P_COLLECTOR>' ||'<![CDATA['||l_collector||']]>'||'</P_COLLECTOR>';
487 l_xml_header := l_xml_header ||l_new_line||' <P_CAMPAIGN>' ||'<![CDATA['||l_campaign||']]>'||'</P_CAMPAIGN>';
488 l_xml_header := l_xml_header ||l_new_line||' <P_ORG_ID>'||'<![CDATA['||l_org_id||']]>'||'</P_ORG_ID>';-- added for bug 13594457 by sunagesh in 25.jan-2011
489 l_xml_header := l_xml_header ||l_new_line||' <DATA_FOUND>' ||l_rows_processed||'</DATA_FOUND>';
490 l_xml_header := l_xml_header ||l_new_line||' <CURR_DATE>' ||TO_CHAR(l_sysdate,'YYYY-MM-DD')||'</CURR_DATE>';
491 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
492 l_close_tag := l_new_line||'</PROMRECONCILIATION>'||l_new_line;
493
494 l_xml_header_length := dbms_lob.getlength(l_xml_header);
495 tempResult:=l_xml_header;
496
497 IF l_rows_processed <> 0 THEN
498 --copy result set to tempResult
499 dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
500 l_xml_header_length,l_resultOffset);
501
502 ELSE
503
504 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
505 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
506 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
507 END IF;
508
509 FND_FILE.put_line( FND_FILE.LOG,'5.base_curr'||g_base_curr);
510 --append the close tag to tempResult
511 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
512 --print to the o/p file
513 print_clob(lob_loc => tempResult);
514
515 FND_FILE.put_line( FND_FILE.LOG,'15--end') ;
516 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +end');
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 FND_FILE.put_line( FND_FILE.LOG,'err-->'||sqlerrm);
521 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || '-'||sqlerrm);
522 END gen_xml_data;
523
524 --Procedure to calculate Payment count and Payment amount
525 Procedure calc_pmt_amt_cnt(p_org_id in number,
526 p_date_from in date,
527 p_date_to in date,
528 p_currency in varchar2,
529 p_pro_state in varchar2,
530 p_pro_status in varchar2,
531 p_group_by in varchar2,
532 p_group_by_mode in varchar2,
533 p_group_by_value_coll in varchar2 default null,
534 p_group_by_value_sch in varchar2 default null
535 )
536 is
537 l_temp_resource_id number;
538 l_temp_source_code_id number;
539 l_temp_pmt_count number;
540 l_temp_pmt_amount number;
541 --l_ctr_enbl_flg varchar2(1); --commented for bug 10054672 gnramasa 27th Aug 10
542 l_curr varchar2(10) default null;
543 TYPE pmtcnt IS REF CURSOR;
544 pmtdet pmtcnt;
545 l_pmt_sum varchar2(10000);
546 l_resr_cnt number;
547 l_resr_qry varchar2(10000);
548 l_where varchar2(9000):='';
549 l_group_by varchar2(1000);
550 l_coll_rate varchar2(50);
551 l_api_name CONSTANT VARCHAR2(30) := 'calc_pmt_amt_cnt';
552 l_resource_char varchar2(100);
553 l_source_code_char varchar2(100);
557 FND_FILE.put_line( FND_FILE.LOG,'*****Start of pmt_amt_cnt procedure***********') ;
554 l_hash_value number;
555 begin
556 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
558
559
560 select nvl(fnd_profile.value('IEX_COLLECTIONS_RATE_TYPE'),'N')
561 into l_coll_rate
562 from dual;
563
564 /*if l_coll_rate is null then
565 select default_exchange_rate_type
566 into l_coll_rate
567 from ar_cmgt_setup_options;
568 end if;*/
569
570 if l_coll_rate is null then
571 l_coll_rate:='Corporate';
572 end if;
573
574 --Start for bug 10054672 gnramasa 27th Aug 10
575 /*
576 select NVL(FND_PROFILE.value('IEX_LEASE_ENABLED'), 'N')
577 into l_ctr_enbl_flg
578 from dual;
579
580 if l_ctr_enbl_flg='N' then
581 FND_FILE.put_line( FND_FILE.LOG,'20') ;
582
583 l_where :=l_where||' and ipd1.contract_id is null';
584 else
585 l_where :=l_where||' and ipd1.contract_id is not null';
586 end if;
587 */
588
589 FND_FILE.put_line( FND_FILE.LOG,'20') ;
590 l_where :=l_where||' and ipd1.contract_id is null';
591
592 --End for bug 10054672 gnramasa 27th Aug 10
593
594 if p_org_id is not null then
595 l_where :=l_where||' and ipd1.org_id='||p_org_id;
596 end if;
597
598
599 if p_date_from is not null and p_date_to is not null then
600 FND_FILE.put_line( FND_FILE.LOG,'21');
601 l_where:=l_where||' and ipd1.promise_date between(to_date('''||p_date_from||''', ''DD-MON-RR'')) and (to_date('''||p_date_to||''',''DD-MON-RR''))';
602 end if;
603
604 l_pmt_sum:='select source_code_id,resource_id,pmt_count, pmt_amount from '||
605 '(select amsc.source_code_id,jrev.resource_id resource_id,count(unique arra.cash_receipt_id) pmt_count ,'||
606 'sum(decode(arra.cash_receipt_id, null,0,gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',prdapplx.amount_applied)))pmt_amount '||
607 'FROM IEX_PROMISE_DETAILS IPD1 , JTF_RS_RESOURCE_EXTNS_VL JREV , AMS_SOURCE_CODES AMSC , iex_prd_appl_xref PRDAPPLX , ar_receivable_applications ARRA , hz_cust_accounts HZCA,ar_system_parameters asp '||
608 'WHERE JREV.resource_id(+) = IPD1.resource_id '||
609 'AND AMSC.source_code_id(+) = IPD1.campaign_sched_id '||
610 'and IPD1.cust_account_id = HZCA.cust_account_id and ipd1.org_id=asp.org_id '||
611 'and nvl(PRDAPPLX.reversed_flag,''N'') <> ''Y'' '||
612 'and IPD1.promise_detail_id = PRDAPPLX.promise_detail_id (+) '||
613 'and PRDAPPLX.receivable_application_id = ARRA.receivable_application_id(+) ';
614
615 if p_pro_state is not null then
616 l_where:=l_where||' and ipd1.state='''||p_pro_state||'''';
617 end if;
618
619 if p_pro_status is not null then
620 l_where:=l_where||' and ipd1.status='''||p_pro_status||'''';
621 end if;
622
623 if p_group_by='COLLECTOR' then
624 FND_FILE.put_line( FND_FILE.LOG,'23');
625 if p_group_by_mode='GROUP' then
626 FND_FILE.put_line( FND_FILE.LOG,'24');
627 begin
628 select count(distinct(resource_id))
629 into l_resr_cnt
630 from
631 JTF_RS_RESOURCE_EXTNS
632 where source_id in ( select distinct b.person_id
633 from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a
634 where b.manager_person_id = a.source_id
635 and a.resource_id = p_group_by_value_coll);--100000937);
636
637 if l_resr_cnt <> 0 then
638 l_resr_qry:= '(select distinct resource_id '||
639 'from '||
640 'JTF_RS_RESOURCE_EXTNS '||
641 'where source_id in (select distinct b.person_id '||
642 'from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a '||
643 'where b.manager_person_id = a.source_id '||
644 'and a.resource_id = '||p_group_by_value_coll;
645
646 l_where:=l_where||' and ipd1.resource_id in '|| l_resr_qry ;
647 l_where:=l_where||'))';
648 else
649 FND_FILE.put_line( FND_FILE.LOG,'This Collector can see only promises assigned to him');
650 l_where:=l_where||' and ipd1.resource_id= '|| p_group_by_value_coll;
651 end if;
652 exception
653 when others then
654 FND_FILE.put_line( FND_FILE.LOG,'***error in fetching resource count****'||sqlerrm);
655 end;
656 else
657 FND_FILE.put_line( FND_FILE.LOG,'26');
658 l_where:=l_where||' and ipd1.resource_id= '|| p_group_by_value_coll;
659 end if;
660 end if;
661
662 if p_group_by='SCHEDULE' then
663 FND_FILE.put_line( FND_FILE.LOG,'27');
664 if p_group_by_value_sch is not null then
665 FND_FILE.put_line( FND_FILE.LOG,'28');
666 l_where:=l_where||' and ipd1.campaign_sched_id ='||p_group_by_value_sch;
667 l_group_by:=' group by amsc.source_code_id,amsc.source_code,jrev.resource_id,jrev.resource_name)';
668 end if;
669 end if;
670 FND_FILE.put_line( FND_FILE.LOG,'28.5');
671 l_where:=l_where||' and (ipd1.campaign_sched_id in (Select source_code_id '||
672 'from ams_source_codes '||
673 'where arc_source_code_for =''CAMP'') '||
674 'OR IPD1.campaign_sched_id is NULL)';
675
676
677 l_group_by:=' group by jrev.resource_id,jrev.resource_name,amsc.source_code_id,amsc.source_code)';
678
679 l_pmt_sum:=l_pmt_sum||l_where;
680 l_pmt_sum:=l_pmt_sum||l_group_by;
681
682 FND_FILE.put_line( FND_FILE.LOG,'l_pmt_summ-->'||l_pmt_sum);
683
684 open pmtdet for l_pmt_sum;
685 loop
686 fetch pmtdet into l_temp_source_code_id,l_temp_resource_id,l_temp_pmt_count,l_temp_pmt_amount;
687 exit when pmtdet%notfound;
688 IF l_temp_source_code_id IS NOT NULL AND
689 l_temp_resource_id IS NOT NULL THEN
690
691 l_resource_char:=to_char(l_temp_resource_id);
692 l_source_code_char:=to_char(l_temp_source_code_id);
693
694 elsif l_temp_source_code_id is not null and l_temp_resource_id is null then
695 l_resource_char:='';
696 l_source_code_char:=to_char(l_temp_source_code_id);
697 elsif l_temp_source_code_id is null and l_temp_resource_id is not null then
698 l_resource_char:=to_char(l_temp_resource_id);
699 l_source_code_char:='';
700 else
701 l_resource_char:='';
702 l_source_code_char:='';
703 end if;
704
705 l_hash_value := DBMS_UTILITY.get_hash_value(
706 l_resource_char||'@*?'||l_source_code_char,
707 1000,
708 25000);
709
710
711 l_pmt_cnt(l_hash_value):=l_temp_pmt_count;
712 l_pmt_amt(l_hash_value):=l_temp_pmt_amount;
713 FND_FILE.put_line( FND_FILE.LOG,'in loop');
714 FND_FILE.put_line( FND_FILE.LOG,l_pmt_cnt(l_hash_value));
715 FND_FILE.put_line( FND_FILE.LOG,l_pmt_amt(l_hash_value));
716 end loop;
717 --end if;
718 close pmtdet;
719 FND_FILE.put_line( FND_FILE.LOG,'29');
720 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
721 exception
722 when others then
723 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || '-'||sqlerrm);
724 FND_FILE.put_line( FND_FILE.LOG,'err in proc-->'||sqlerrm);
725 end calc_pmt_amt_cnt;
726
727 function get_pmt_count(p_resource_id number,p_source_code_id in number) return number
728 is
729 l_resource_char varchar2(100);
730 l_source_code_char varchar2(100);
731 l_hash_value number;
732 begin
733
734 IF p_resource_id IS NOT NULL AND
735 p_source_code_id IS NOT NULL THEN
736
737 l_resource_char:=to_char(p_resource_id);
738 l_source_code_char:=to_char(p_source_code_id);
739
740 elsif p_source_code_id is not null and p_resource_id is null then
741 l_resource_char:='';
742 l_source_code_char:=to_char(p_source_code_id);
743 elsif p_source_code_id is null and p_resource_id is not null then
744 l_resource_char:=to_char(p_resource_id);
745 l_source_code_char:='';
746 else
747 l_resource_char:='';
748 l_source_code_char:='';
749 end if;
750
751 l_hash_value := DBMS_UTILITY.get_hash_value(
752 l_resource_char||'@*?'||l_source_code_char,
753 1000,
754 25000);
755
756
757 if l_pmt_cnt.exists(l_hash_value) then
758 FND_FILE.put_line( FND_FILE.LOG,'30');
759 return l_pmt_cnt(l_hash_value);
760 else
761 return 0;
762 end if;
763 end get_pmt_count;
764
765 function get_pmt_amount(p_resource_id number,p_source_code_id in number) return number
766 is
767 l_resource_char varchar2(100);
768 l_source_code_char varchar2(100);
769 l_hash_value number;
770 begin
771 IF p_resource_id IS NOT NULL AND
772 p_source_code_id IS NOT NULL THEN
773
774 l_resource_char:=to_char(p_resource_id);
775 l_source_code_char:=to_char(p_source_code_id);
776
777 elsif p_source_code_id is not null and p_resource_id is null then
778 l_resource_char:='';
779 l_source_code_char:=to_char(p_source_code_id);
780 elsif p_source_code_id is null and p_resource_id is not null then
781 l_resource_char:=to_char(p_resource_id);
782 l_source_code_char:='';
783 else
784 l_resource_char:='';
785 l_source_code_char:='';
786 end if;
787
788 l_hash_value := DBMS_UTILITY.get_hash_value(
789 l_resource_char||'@*?'||l_source_code_char,
790 1000,
791 25000);
792 if l_pmt_amt.exists(l_hash_value) then
793 FND_FILE.put_line( FND_FILE.LOG,'31');
794 return l_pmt_amt(l_hash_value);
795 else
796 return 0;
797 end if;
798 end get_pmt_amount;
799
800 BEGIN
801 G_LOG_ENABLED := 'N';
802 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
803
804 /* getting msg logging info */
805 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
806 if (G_LOG_ENABLED = 'N') then
807 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
808 else
809 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
810 end if;
811
812 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
813
814 end;