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

Source


1 PACKAGE body iex_payment_camp_rpt_pkg AS
2 /* $Header: iexpcmrb.pls 120.19.12020000.3 2012/10/23 06:48:02 sunagesh ship $ */
3   G_PKG_NAME VARCHAR2(100)         :='iex_payment_camp_rpt_pkg';
4    G_LOG_ENABLED                   varchar2(5);
5   G_MSG_LEVEL                     NUMBER;
6 
7   Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
8 IS
9 BEGIN
10     if (p_msg_level >= G_MSG_LEVEL) then
11 
12         FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
13 
14     end if;
15 
16     if FND_GLOBAL.Conc_Request_Id is not null then
17         fnd_file.put_line(FND_FILE.LOG, p_msg);
18     end if;
19 
20 EXCEPTION
21     WHEN OTHERS THEN
22         LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
23 END;
24 
25 PROCEDURE PRINT_CLOB
26   (
27     lob_loc IN CLOB)
28             IS
29   /*-----------------------------------------------------------------------+
30   | Local Variable Declarations and initializations                       |
31   +-----------------------------------------------------------------------*/
32   l_api_name    CONSTANT VARCHAR2(30) := 'PRINT_CLOB';
33   l_api_version CONSTANT NUMBER       := 1.0;
34   c_endline     CONSTANT VARCHAR2 (1) := '
35 ';
36   c_endline_len CONSTANT NUMBER       := LENGTH (c_endline);
37   l_start       NUMBER                := 1;
38   l_end         NUMBER;
39   l_one_line    VARCHAR2 (7000);
40   l_charset     VARCHAR2(100);
41   /*-----------------------------------------------------------------------+
42   | Cursor Declarations                                                   |
43   +-----------------------------------------------------------------------*/
44 BEGIN
45   LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
46   LOOP
47     l_end := DBMS_LOB.INSTR (lob_loc => lob_loc, pattern => c_endline, offset => l_start, nth => 1 );
48     FND_FILE.put_line( FND_FILE.LOG,'l_end-->'||l_end);
49     IF (NVL (l_end, 0) < 1) THEN
50       EXIT;
51     END IF;
52     l_one_line := DBMS_LOB.SUBSTR (lob_loc => lob_loc, amount => l_end - l_start, offset => l_start );
53     FND_FILE.put_line( FND_FILE.LOG,'l_one_line-->'||l_one_line);
54     FND_FILE.put_line( FND_FILE.LOG,'c_endline_len-->'||c_endline_len);
55     l_start := l_end + c_endline_len;
56     FND_FILE.put_line( FND_FILE.LOG,'l_start-->'||l_start);
57   --  FND_FILE.put_line( FND_FILE.LOG,'32');
58     Fnd_File.PUT_line(Fnd_File.OUTPUT,l_one_line);
59   END LOOP;
60 END PRINT_CLOB;
61 PROCEDURE gen_xml_data_pcamp
62   (
63     ERRBUF OUT NOCOPY  VARCHAR2,
64     RETCODE OUT NOCOPY VARCHAR2,
65     p_org_id in number,
66     p_date_from    IN varchar2,
67     p_date_to      IN varchar2,
68     p_currency     IN VARCHAR2,
69     p_campaign     IN VARCHAR2,
70     p_collector    IN VARCHAR2,
71     p_report_level IN VARCHAR2,
72     p_summ_det     IN VARCHAR2,
73     p_payment_type IN VARCHAR2,
74     p_goal         IN VARCHAR2,
75     p_goal_amount NUMBER )
76                                 IS
77  l_api_name CONSTANT VARCHAR2(30) := 'gen_xml_data_pcamp';
78   l_api_version CONSTANT NUMBER := 1.0;
79   ctx DBMS_XMLQUERY.ctxType;
80   result CLOB;
81   qryCtx DBMS_XMLquery.ctxHandle;
82   l_result CLOB;
83   tempResult CLOB;
84   l_where         VARCHAR2(8000):='';
85   l_group_by      VARCHAR2(4000);
86   l_order_by      VARCHAR2(4000);
87   l_res_id        NUMBER;
88   l_version       VARCHAR2(20);
89   l_compatibility VARCHAR2(20);
90   l_suffix        VARCHAR2(2);
91   l_majorVersion  NUMBER;
92   l_resultOffset  NUMBER;
93   l_xml_header CLOB;--varchar2(4000);
94   l_xml_header_length NUMBER;
95   l_errNo             NUMBER;
96   l_errMsg            VARCHAR2(200);
97   queryCtx DBMS_XMLquery.ctxType;
98   l_xml_query VARCHAR2(32767);
99 TYPE ref_cur IS   REF CURSOR;
100     l_xml_stmt ref_cur;
101     l_rows_processed NUMBER;
102     l_new_line       VARCHAR2(1);
103     l_close_tag      VARCHAR2(100);
104     l_res_cnt        NUMBER;
105     l_res_qry        VARCHAR2(5000);
106     l_pro_status     VARCHAR2(20);
107     l_pro_state      VARCHAR2(20);
108     l_ctr_enbl_flg   VARCHAR2(1);
109     l_base_op_curr   VARCHAR2(10);
110     l_coll_rate      VARCHAR2(20);
111     l_query      VARCHAR2(11000);
112     l_org_id         VARCHAR2(100);
113     l_hourly_goal    NUMBER;
114     l_daily_goal     NUMBER;
118     l_sysdate date;
115     l_pay_typ_meaning varchar2(100);
116     l_collector varchar2(200);
117     l_campaign varchar2(200);
119     l_encoding              VARCHAR2(100);  --Added for bug 10213070 snuthala 19th Oct 10
120     l_from_date date;
121     l_to_date date;
122 
123   BEGIN
124     FND_FILE.put_line( FND_FILE.LOG,'*************start of the proc***************');
125     LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || '');
126    --start of get filters
127     l_from_date := TRUNC(fnd_date.canonical_to_date(p_date_from));
128     l_to_date := TRUNC(fnd_date.canonical_to_date(p_date_to))+1;
129 
130    if p_date_from is not null and p_date_to is not null then
131       l_where:=l_where||'and trunc(p.creation_date) >=to_date('''||l_from_date||''',''DD-MM-RRRR'') ';
132       l_where:=l_where||'and trunc(p.creation_date) <=to_date('''||l_to_date||''',''DD-MM-RRRR'')';
133    end if;
134 
135     if p_org_id is not null then
136       l_where:=l_where||' and d.org_id='||p_org_id;
137        select name
138 	  into l_org_id
139 	  from hr_operating_units
140 	  where organization_id=p_org_id;
141      else
142 	  l_org_id:='All';
143     end if;
144 
145 
146     select nvl(fnd_profile.value('IEX_COLLECTIONS_RATE_TYPE'),'N')
147     into l_coll_rate
148     from dual;
149 
150 /*if l_coll_rate is null then
151   select default_exchange_rate_type
152   into l_coll_rate
153   from ar_cmgt_setup_options;
154 end if;*/
155 
156 if l_coll_rate is null then
157   l_coll_rate:='Corporate';
158 end if;
159 
160 
161      if  p_campaign is not null then
162        select source_code
163        into l_campaign
164        from ams_source_codes
165        where source_code_id=p_campaign;
166 	--  FND_FILE.put_line( FND_FILE.LOG,'6');
167 	   l_where:=l_where||' and p.campaign_sched_id ='||p_campaign;
168      else
169 	   l_where:=l_where||' and (p.campaign_sched_id in (Select source_code_id '||
170  	                     'from  ams_source_codes '||
171 			     'where arc_source_code_for =''CAMP'') '||
172  	                     'OR p.campaign_sched_id is NULL)';
173      end if;
174 
175   IF p_collector IS NOT NULL THEN
176     --  FND_FILE.put_line( FND_FILE.LOG,'4.5');
177      select source_name
178      into l_collector
179      from jtf_rs_resource_extns
180      where resource_id=p_collector;
181     IF p_report_level='GROUP' THEN
182       SELECT DISTINCT COUNT(resource_id)
183          INTO l_res_cnt
184          FROM JTF_RS_RESOURCE_EXTNS
185         WHERE source_id IN
186         ( SELECT DISTINCT b.person_id
187            FROM jtf_rs_rep_managers b,
188           JTF_RS_RESOURCE_EXTNS a
189           WHERE b.manager_person_id = a.source_id
190         AND a.resource_id           = p_collector
191         );--100000937);
192       IF l_res_cnt <> 0 THEN
193         l_res_qry  := '(select distinct resource_id '||
194 	'from '|| 'JTF_RS_RESOURCE_EXTNS '||
195 	'where source_id in (select distinct b.person_id '||
196 	'from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a '||
197 	'where b.manager_person_id = a.source_id '|| 'and a.resource_id = '||p_collector;
198         l_where    :=l_where||' and p.resource_id in '|| l_res_qry ;
199         l_where    :=l_where||'))';
200       ELSE
201         FND_FILE.put_line( FND_FILE.LOG,'This Collector can see only promises assigned to him');
202         l_where:=l_where||' and p.resource_id= '|| p_collector;
203       END IF;
204     ELSE
205       --    FND_FILE.put_line( FND_FILE.LOG,'4.6');
206       l_where:=l_where||' and p.resource_id= '|| p_collector;
207     END IF;
208   END IF;
209 
210   if p_collector is null then
211    l_collector :=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
212   elsif p_campaign is null then
213    l_campaign:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
214   end if;
215 
216   IF p_payment_type IS NOT NULL THEN
217     --    FND_FILE.put_line( FND_FILE.LOG,'4.7');
218     l_where:=l_where||' and l.lookup_code='''||p_payment_type||'''';
219     select meaning
220     into l_pay_typ_meaning
221     from iex_lookups_v
222     where lookup_type='IEX_PAYMENT_TYPES'
223     and lookup_code=p_payment_type;
224      else
225       l_pay_typ_meaning:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
226   END IF;
227   --end of get filters
228   --report mode DETAIL
229   IF p_summ_det    = 'DETAIL' THEN
230 
231       l_query := 'SELECT l.meaning payment_method,p.ipayment_status payment_status,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(c.source_code) campaign,p.campaign_sched_id campaign_schedule_id ,'||
232       'to_char(p.creation_date,''YYYY-MM-DD'') payment_date,gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount) amount,'||
233       'p.payment_id,d.delinquency_id,p.resource_id,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(j.source_name) collector,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(hca.account_number) account_number,'||
234       'IEX_UTILITIES.REPLACE_SPECIAL_CHARS(s.trx_number) invoice_id,d.payment_schedule_id,s.class,'||
235       /*'gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',s.amount_due_remaining) amount_due_remaining,'||  commented for bug 14753025
236       'gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',s.amount_due_original) amount_due_original,'||*/
237       'gl_currency_api.convert_amount(s.invoice_currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',s.amount_due_remaining) amount_due_remaining,'|| --added for bug 14753025
238       'gl_currency_api.convert_amount(s.invoice_currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',s.amount_due_original) amount_due_original,'||
242       'iex_lookups_v l,hz_cust_accounts hca,ar_system_parameters asp '||
239       'r.currency_code currency_code,l.lookup_code payment_method_lookup_code '||
240       'FROM iex_delinquencies d,'|| 'iex_payments p,'||
241       'ar_payment_schedules_all s,'|| 'iex_del_pay_xref xdp,'|| 'ams_source_codes c,'|| 'jtf_rs_resource_extns j,'|| 'iex_pay_receipt_xref xpr,'||'ar_cash_receipts_all r,'||
243       'WHERE xdp.payment_id = p.payment_id '|| 'AND xdp.DELINQUENCY_ID= d.DELINQUENCY_ID '|| 'AND d.payment_schedule_id = s.payment_schedule_id '||
244       'AND c.source_code_id(+)   = p.campaign_sched_id '|| 'AND p.resource_id         = j.resource_id(+) '|| 'AND hca.cust_account_id=d.cust_account_id '||
245       'AND p.payment_id          = xpr.payment_id '||      'AND r.cash_receipt_id     = xpr.cash_receipt_id and d.org_id=asp.org_id '||
246       'AND l.lookup_type         = ''IEX_PAYMENT_TYPES'' '|| 'AND p.payment_method      = l.lookup_code '||'and s.class=''INV'' ';
247 
248     l_order_by :=' order by d.cust_account_id,p.payment_id';
249     l_query:=l_query||l_where;
250     l_query:=l_query||l_order_by;
251     FND_FILE.put_line( FND_FILE.LOG,'qry-->'||l_query);
252     ctx := DBMS_XMLQUERY.newContext(l_query);
253     --FND_FILE.put_line( FND_FILE.LOG,'21');
254   END IF;
255   --report mode SUMMARY
256   IF p_summ_det          = 'SUMMARY' THEN
257     IF p_goal           IS NOT NULL THEN
258       IF (p_goal_amount IS NOT NULL AND p_goal_amount >0) THEN
259         --  FND_FILE.put_line( FND_FILE.LOG,'4.8');
260         FND_FILE.put_line( FND_FILE.LOG,'hourly-->'||p_goal);
261         IF p_goal       ='HOURLY' THEN
262           l_hourly_goal:=round(p_goal_amount,2);
263           l_daily_goal :=round(p_goal_amount*8,2); --default 8 hours per day
264           FND_FILE.put_line( FND_FILE.LOG,'l_hourly_goal1-->'||l_hourly_goal);
265         ELSE
266           l_hourly_goal:=round(p_goal_amount/8,2);
267           l_daily_goal :=round(p_goal_amount,2);
268           FND_FILE.put_line( FND_FILE.LOG,'l_hourly_goal2-->'||l_hourly_goal);
269         END IF;
270       ELSE
271         FND_FILE.put_line( FND_FILE.LOG,'Goal Amount cannot be null or negative');
272       END IF;
273     END IF;
274 
275       --FND_FILE.put_line( FND_FILE.LOG,'5');
276       l_query:='SELECT IEX_UTILITIES.REPLACE_SPECIAL_CHARS(c.source_code) Campaign,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(j.source_name) Collector,'||
277       'SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount)) Total_Amount_Collected,'||
278       'ROUND((SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/((to_date('''||l_to_date||
279       ''',''DD-MON-RRRR'')-to_date('''||l_from_date||''',''DD-MON-RRRR''))+1))/8,2) Hourly_Average,'||l_hourly_goal||' Hourly_Goal,'||
280       'ROUND(((((SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/((to_date('''||l_to_date||
281       ''',''DD-MON-RRRR'')-to_date('''||l_from_date||''',''DD-MON-RRRR''))+1))/8)/'||l_hourly_goal||') *100),2) percent_hourly_avr,'||
282       'ROUND((SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/((to_date('''||l_to_date||
283       ''',''DD-MON-RRRR'')-to_date('''||l_from_date||''',''DD-MON-RRRR''))+1)),2) Daily_Average,'|| l_daily_goal||' Daily_Goal,'||
284       'ROUND((((SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/((to_date('''||l_to_date||
285       ''',''DD-MON-RRRR'')-to_date('''||l_from_date||''',''DD-MON-RRRR''))+1))/'||l_daily_goal||') *100),2) percent_daily_avr,'||
286       'COUNT(DISTINCT(p.payment_id)) Payments,'||
287       'ROUND(SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/COUNT(DISTINCT(p.payment_id)),2) Average_Payment,'||
288       'COUNT(DISTINCT(d.transaction_id)) Invoices,'||
289       'ROUND(SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/COUNT(DISTINCT(d.transaction_id)),2) Average_Invoices,'||
290       'COUNT(DISTINCT(d.cust_account_id)) Accounts,'||
291       'ROUND(SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/COUNT(DISTINCT(d.cust_account_id)),2) Average_Account '||
292       'FROM '|| 'iex_delinquencies d,iex_payments p, ar_payment_schedules_all s , iex_del_pay_xref xdp,'||
293       'ams_source_codes c,jtf_rs_resource_extns j,iex_pay_receipt_xref xpr,ar_cash_receipts_all r,'|| 'iex_lookups_v l,ar_system_parameters asp '||
294       'WHERE xdp.payment_id      = p.payment_id '|| 'AND xdp.DELINQUENCY_ID      = d.DELINQUENCY_ID '|| 'AND d.payment_schedule_id   = s.payment_schedule_id '||
295       'AND c.source_code_id(+)     = p.campaign_sched_id '|| 'AND p.resource_id = j.resource_id(+) '|| 'AND p.payment_id = xpr.payment_id and d.org_id = asp.org_id '||
296       'AND r.cash_receipt_id       = xpr.cash_receipt_id '|| 'AND l.lookup_type           = ''IEX_PAYMENT_TYPES'' '|| 'AND p.payment_method        = l.lookup_code '||
297       'AND s.class                 =''INV'' ';
298 
299     l_group_by  :=' group by IEX_UTILITIES.REPLACE_SPECIAL_CHARS(c.source_code),'||
300                   ' IEX_UTILITIES.REPLACE_SPECIAL_CHARS(j.source_name)' ;
301     l_query:=l_query||l_where;
302     l_query:=l_query||l_group_by;
303     --call procedure to calculate pmt_cnt and pmt_amt
304     FND_FILE.put_line( FND_FILE.LOG,'l_query_summ-->'||l_query);
305     ctx := DBMS_XMLQUERY.newContext(l_query);
306   --  FND_FILE.put_line( FND_FILE.LOG,'7.5');
307   END IF;
308   DBMS_XMLQUERY.setRaiseNoRowsException(ctx,TRUE);
309 
310   -- Bind Mandatory Variables
311  -- DBMS_XMLQUERY.setBindValue(ctx, 'p_date_from', p_date_from);
312  -- DBMS_XMLQUERY.setBindValue(ctx, 'p_date_to', p_date_to);
313 
314   --get the result
315   BEGIN
316     l_result := DBMS_XMLQUERY.getXML(ctx);
317     DBMS_XMLQUERY.closeContext(ctx);
318     l_rows_processed := 1;
319     FND_FILE.put_line( FND_FILE.LOG,'l_res_len-->'||dbms_lob.getlength(l_result));
320     --FND_FILE.put_line( FND_FILE.LOG,'l_res-->'||l_result);
324     FND_FILE.put_line( FND_FILE.LOG,'l_errMsg-->'||l_errMsg);
321   EXCEPTION
322   WHEN OTHERS THEN
323     DBMS_XMLQUERY.getExceptionContent(ctx,l_errNo,l_errMsg);
325     IF l_errNo          = 1403 THEN
326       l_rows_processed := 0;
327     END IF;
328     DBMS_XMLQUERY.closeContext(ctx);
329   END;
330   IF l_rows_processed <> 0 THEN
331    -- FND_FILE.put_line( FND_FILE.LOG,'8') ;
332 
333     --get the length of the rowset header
334     l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
335     FND_FILE.put_line( FND_FILE.LOG,'l_res_off-->'||l_resultOffset) ;
336   ELSE
337     l_resultOffset := 0;
338   END IF;
339 
340   select trunc(sysdate)
341   into l_sysdate
342   from dual;
343 
344   FND_FILE.put_line( FND_FILE.LOG,' Sysdate '|| l_sysdate);
345   l_new_line := '
346 ';
347 --  FND_FILE.put_line( FND_FILE.LOG,'10') ;
348   /* Prepare the tag for the report heading */
349   --Start adding for bug 10213070 snuthala 19th Oct 10
350    --l_xml_header     := '<?xml version="1.0" encoding="UTF-8"?>';
351    -- Instead of hard coding the value, pick the charcter set value from "ICX: Client IANA Encoding" profile.
352    l_encoding       := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
353    l_xml_header     := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
354    --End adding for bug 10213070 snuthala 19th Oct 10
355   l_xml_header   := l_xml_header ||l_new_line||'<PAYCOLLECTOR>';
356   l_xml_header   := l_xml_header ||l_new_line||' <PARAMETERS>';
357   l_xml_header   := l_xml_header ||l_new_line||' <P_DATE_FROM>'||TO_CHAR(l_from_date,'YYYY-MM-DD')||'</P_DATE_FROM>';
358   l_xml_header   := l_xml_header ||l_new_line||' <P_DATE_TO>' ||TO_CHAR(l_to_date,'YYYY-MM-DD') ||'</P_DATE_TO>';
359   l_xml_header   := l_xml_header ||l_new_line||' <P_CURRENCY>' ||p_currency||'</P_CURRENCY>';
360   l_xml_header   := l_xml_header ||l_new_line||' <P_CAMPAIGN>' ||'<![CDATA['||l_campaign||']]>'||'</P_CAMPAIGN>';
361   l_xml_header   := l_xml_header ||l_new_line||' <P_COLLECTOR>' ||'<![CDATA['||l_collector||']]>'||'</P_COLLECTOR>';
362   l_xml_header   := l_xml_header ||l_new_line||' <P_REPORT_LEVEL>' ||iex_utilities.get_lookup_meaning('IEX_REPORT_LEVEL',p_report_level)||'</P_REPORT_LEVEL>';
363   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>';
364   l_xml_header   := l_xml_header ||l_new_line||' <P_PAYMENT_TYPE>' ||l_pay_typ_meaning||'</P_PAYMENT_TYPE>';
365   FND_FILE.put_line( FND_FILE.LOG, 'xml header '|| l_xml_header);
366   IF p_summ_det   ='SUMMARY' THEN
367     l_xml_header := l_xml_header ||l_new_line||' <P_GOAL>' ||iex_utilities.get_lookup_meaning('IEX_GOAL_TYPES',p_goal)||'</P_GOAL>';
368     l_xml_header := l_xml_header ||l_new_line||' <P_GOAL_AMOUNT>' ||p_goal_amount||'</P_GOAL_AMOUNT>';
369   END IF;
370   l_xml_header   := l_xml_header ||l_new_line||' <P_ORG_ID>' ||'<![CDATA['||l_org_id||']]>'||'</P_ORG_ID>';
371    l_xml_header      := l_xml_header ||l_new_line||' <CURR_DATE>' ||TO_CHAR(l_sysdate,'YYYY-MM-DD')||'</CURR_DATE>';
372     l_xml_header    := l_xml_header ||l_new_line||' <DATA_FOUND>' ||l_rows_processed||'</DATA_FOUND>';
373    l_xml_header        := l_xml_header ||l_new_line||' </PARAMETERS>';
374   l_close_tag         := l_new_line||'</PAYCOLLECTOR>'||l_new_line;
375   l_xml_header_length := dbms_lob.getlength(l_xml_header);
376   tempResult          :=l_xml_header;
377   FND_FILE.put_line( FND_FILE.LOG,'tempRes0-->'||tempResult);
378   IF l_rows_processed <> 0 THEN
379     --copy result set to tempResult
380     dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset, l_xml_header_length,l_resultOffset);
381 --  FND_FILE.put_line( FND_FILE.LOG,'11') ;
382   --  FND_FILE.put_line( FND_FILE.LOG,'tempRes1-->'||tempResult);
383   ELSE
384  -- FND_FILE.put_line( FND_FILE.LOG,'12') ;
385     dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
386     dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
387     dbms_lob.writeAppend(tempResult, LENGTH(l_xml_header), l_xml_header);
388   END IF;
389 
390   --append the close tag to tempResult
391   dbms_lob.writeAppend(tempResult, LENGTH(l_close_tag), l_close_tag);
392 --  FND_FILE.put_line( FND_FILE.LOG,'tempRes2-->'||tempResult);
393   --print to the o/p file
394   print_clob(lob_loc => tempResult);
395   FND_FILE.put_line( FND_FILE.LOG,'15--end') ;
396   LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || '-end');
397 EXCEPTION
398 WHEN OTHERS THEN
399  -- dbms_output.put_line('err'||sqlerrm);
400   LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' -');
401 END gen_xml_data_pcamp;
402 BEGIN
403    G_LOG_ENABLED := 'N';
404    G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
405 
406    /* getting msg logging info */
407    G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
408    if (G_LOG_ENABLED = 'N') then
409       G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
410    else
411       G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
412    end if;
413 
414    LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
415 
416 END;