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_COLL_RPT_PKG

Source


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