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_COLL_CAMP_SCHD_RPT_PKG

Source


1 PACKAGE body iex_coll_camp_schd_rpt_pkg AS
2 /* $Header: iexccsrb.pls 120.11 2011/05/06 11:06:16 pnaveenk noship $ */
3   G_PKG_NAME VARCHAR2(100)         :='iex_coll_camp_schd_rpt_pkg';
4   G_LOG_ENABLED                   varchar2(5);
5   G_MSG_LEVEL                     NUMBER;
6 
7 --  l_api_name CONSTANT VARCHAR2(50) := 'Collector Campaign Schedule Report';
8 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
9 IS
10 BEGIN
11     if (p_msg_level >= G_MSG_LEVEL) then
12 
13         FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
14 
15     end if;
16 
17     if FND_GLOBAL.Conc_Request_Id is not null then
18         fnd_file.put_line(FND_FILE.LOG, p_msg);
19     end if;
20 
21 EXCEPTION
22     WHEN OTHERS THEN
23         LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
24 END;
25 
26 PROCEDURE PRINT_CLOB
27   (
28     lob_loc IN CLOB)
29             IS
30   /*-----------------------------------------------------------------------+
31   | Local Variable Declarations and initializations                       |
32   +-----------------------------------------------------------------------*/
33   l_api_name    CONSTANT VARCHAR2(30) := 'PRINT_CLOB';
34   l_api_version CONSTANT NUMBER       := 1.0;
35   c_endline     CONSTANT VARCHAR2 (1) := '
36 ';
37   c_endline_len CONSTANT NUMBER       := LENGTH (c_endline);
38   l_start       NUMBER                := 1;
39   l_end         NUMBER;
40   l_one_line    VARCHAR2 (7000);
41   l_charset     VARCHAR2(100);
42   /*-----------------------------------------------------------------------+
43   | Cursor Declarations                                                   |
44   +-----------------------------------------------------------------------*/
45 BEGIN
46    LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
47   LOOP
48     l_end := DBMS_LOB.INSTR (lob_loc => lob_loc, pattern => c_endline, offset => l_start, nth => 1 );
49     FND_FILE.put_line( FND_FILE.LOG,'l_end-->'||l_end);
50     IF (NVL (l_end, 0) < 1) THEN
51       EXIT;
52     END IF;
53     l_one_line := DBMS_LOB.SUBSTR (lob_loc => lob_loc, amount => l_end - l_start, offset => l_start );
54     FND_FILE.put_line( FND_FILE.LOG,'l_one_line-->'||l_one_line);
55     FND_FILE.put_line( FND_FILE.LOG,'c_endline_len-->'||c_endline_len);
56     l_start := l_end + c_endline_len;
57     FND_FILE.put_line( FND_FILE.LOG,'l_start-->'||l_start);
58     FND_FILE.put_line( FND_FILE.LOG,'32');
59     Fnd_File.PUT_line(Fnd_File.OUTPUT,l_one_line);
60   END LOOP;
61 END PRINT_CLOB;
62 PROCEDURE gen_xml_data_collcamp
63   (
64     ERRBUF OUT NOCOPY  VARCHAR2,
65     RETCODE OUT NOCOPY VARCHAR2,
66     p_date_from                 IN varchar2,
67     p_date_to                   IN varchar2,
68     p_coll_camp_typ             IN VARCHAR2,
69     p_campaign                  IN VARCHAR2,
70     p_collector                 IN VARCHAR2,
71     p_report_level              IN VARCHAR2,
72     p_outcome                   IN VARCHAR2,
73     p_result                    IN VARCHAR2,
74     p_reason                    IN VARCHAR2)
75                                 IS
76   l_api_name CONSTANT VARCHAR2(50) := 'gen_xml_data_collcamp';
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;--varchar2(4000);
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 TYPE ref_cur
99 IS
100   REF
101   CURSOR;
102     l_xml_stmt ref_cur;
103     l_rows_processed NUMBER;
104     l_new_line       VARCHAR2(1);
105     l_close_tag      VARCHAR2(100);
106     l_res_cnt        NUMBER;
107     l_res_qry        VARCHAR2(5000);
108     l_pro_status     VARCHAR2(20);
109     l_pro_state      VARCHAR2(20);
110     l_ctr_enbl_flg   VARCHAR2(1);
111     l_base_op_curr   VARCHAR2(10);
112     l_coll_rate      VARCHAR2(20);
113     l_query_dtl      VARCHAR2(11000);
114     l_query          VARCHAR2(11000);
115     l_org_id         VARCHAR2(10);
116     l_coll_camp_typ varchar2(20);
117     l_out_code varchar2(100);
118     l_resl_code varchar2(100);
119     l_res_code varchar2(100);
120     l_collector varchar2(200);
121     l_campaign varchar2(200);
122     l_sysdate date;
123     l_encoding              VARCHAR2(100);  --Added for bug 10213070 snuthala 19th Oct 10
124     l_from_date date;
125     l_to_date date;
126 
127   BEGIN
128   LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
129      FND_FILE.put_line( FND_FILE.LOG,'Begin procedure');
130      l_from_date := TRUNC(fnd_date.canonical_to_date(p_date_from));
131      l_to_date := TRUNC(fnd_date.canonical_to_date(p_date_to));
132 
133      if p_date_from is not null and p_date_to is not null then
134       l_where:=l_where||'and trunc(jii.creation_date) >=to_date('''||l_from_date||''',''DD-MM-RRRR'') ';
135       l_where:=l_where||'and trunc(jii.creation_date) <=to_date('''||l_to_date||''',''DD-MM-RRRR'')+1 ';
136     end if;
137 
138       IF (p_coll_camp_typ='COLLECTOR' OR p_coll_camp_typ='CAMPAIGN') THEN
139       FND_FILE.put_line( FND_FILE.LOG,'4.5');
140       if p_collector is not null then
141       IF p_report_level='GROUP' THEN
142           SELECT DISTINCT COUNT(resource_id)
143            INTO l_res_cnt
144            FROM JTF_RS_RESOURCE_EXTNS
145           WHERE source_id IN
146           ( SELECT DISTINCT b.person_id
147              FROM jtf_rs_rep_managers b,
148             JTF_RS_RESOURCE_EXTNS a
149             WHERE b.manager_person_id = a.source_id
150           AND a.resource_id           = p_collector
151           );
152       IF l_res_cnt <> 0 THEN
153         l_res_qry  := '(select distinct resource_id '||
154 	'from '|| 'JTF_RS_RESOURCE_EXTNS '||
155 	'where source_id in (select distinct b.person_id '||
156 	'from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a '||
157 	'where b.manager_person_id = a.source_id '|| 'and a.resource_id = '||p_collector;
158         l_where    :=l_where||' and jii.resource_id in '|| l_res_qry ;
159         l_where    :=l_where||'))';
160       ELSE
161         FND_FILE.put_line( FND_FILE.LOG,'This Collector can see only promises assigned to him');
162         l_where:=l_where||' and jii.resource_id= '|| p_collector;
163       END IF;
164     ELSE
165       FND_FILE.put_line( FND_FILE.LOG,'4.6');
166       l_where:=l_where||' and jii.resource_id= '|| p_collector;
167     END IF;
168     end if;
169   END IF;
170 
171   IF p_campaign IS NOT NULL THEN
172     FND_FILE.put_line( FND_FILE.LOG,'6');
173     l_where:=l_where||' and jii.source_code_id ='||p_campaign;
174     else
175 	   l_where:=l_where||' and (jii.source_code_id in (Select source_code_id '||
176  	                     'from  ams_source_codes '||
177 			     'where arc_source_code_for =''CAMP'') '||
178  	                     'OR jii.source_code_id is NULL)';
179 
180   END IF;
181 
182   if p_outcome is not null then
183     l_where:=l_where||' and jii.outcome_id='||p_outcome;
184     select outcome_code
185     into l_out_code
186     from jtf_ih_outcomes_vl
187     where outcome_id=p_outcome;
188   else
189     l_out_code:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
190   end if;
191 
192   if p_result is not null then
193     l_where:=l_where||' and jii.result_id='||p_result;
194     select result_code
195     into l_resl_code
196     from jtf_ih_results_vl
197     where result_id=p_result;
198   else
199     l_resl_code:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
200   end if;
201 
202   if p_reason is not null then
203     l_where:=l_where||' and jii.reason_id='||p_reason;
204     select reason_code
205     into l_res_code
206     from jtf_ih_reasons_vl
207     where reason_id=p_reason;
208   else
209     l_res_code:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
210   end if;
211 
212   /* l_where:=l_where||' and (ipd.campaign_sched_id in (Select source_code_id '||
213   'from  ams_source_codes '||
214   'where arc_source_code_for =''CAMP'') '||
215   'OR IPD.campaign_sched_id is NULL)'; */
216   --end of get filters
217   --group_by
218   IF p_coll_camp_typ='COLLECTOR' THEN
219     l_coll_camp_typ:='Collector';
220     l_group_by     := ' group by jrrev.source_name, amsc.source_code,'|| 'jiov.outcome_code, jires.result_code,jirea.reason_code';
221   ELSE
222     l_coll_camp_typ:='Campaign';
223     l_group_by:=' group by  amsc.source_code,jrrev.source_name,'|| 'jiov.outcome_code, jires.result_code,jirea.reason_code';
224   END IF;
225   l_query:='select jrrev.source_name Collector,amsc.source_code Campaign,jiov.outcome_code Outcome,'|| 'jires.result_code Result,jirea.reason_code Reason , count(*) Count '||
226             'from '||
227 	    'jtf_ih_interactions jii,jtf_rs_resource_extns jrrev,'|| 'jtf_ih_outcomes_vl jiov,jtf_ih_results_vl jires,jtf_ih_reasons_vl jirea,ams_source_codes amsc '||
228 	    'where jii.resource_id= jrrev.resource_id(+) '||
229 	    'and jii.outcome_id= jiov.outcome_id(+) '||
230 	    'and jii.result_id= jires.result_id(+) '||
231 	    'and jii.reason_id= jirea.reason_id(+) '||
232 	    'and jii.source_code= amsc.source_code(+) ';
233 
234   l_query:=l_query||l_where;
235   l_query:=l_query||l_group_by;
236   FND_FILE.put_line( FND_FILE.LOG,'qry-->'||l_query);
237   LogMessage(FND_LOG.LEVEL_STATEMENT, 'query: ' || l_query);
238   ctx := DBMS_XMLQUERY.newContext(l_query);
239   DBMS_XMLQUERY.setRaiseNoRowsException(ctx,TRUE);
240   -- Bind Mandatory Variables
241   --DBMS_XMLQUERY.setBindValue(ctx, 'p_date_from', p_date_from);
242   --DBMS_XMLQUERY.setBindValue(ctx, 'p_date_to', p_date_to);
243   --   DBMS_XMLQUERY.setBindValue(ctx, 'p_pro_state', p_pro_state);
244   --    DBMS_XMLQUERY.setBindValue(ctx, 'p_pro_status', p_pro_status);
245   --get the result
246   BEGIN
247     l_result := DBMS_XMLQUERY.getXML(ctx);
248     DBMS_XMLQUERY.closeContext(ctx);
249     l_rows_processed := 1;
250   EXCEPTION
251   WHEN OTHERS THEN
252     DBMS_XMLQUERY.getExceptionContent(ctx,l_errNo,l_errMsg);
253     IF l_errNo          = 1403 THEN
254       l_rows_processed := 0;
255       --l_no_data_flag:=0;
256     END IF;
257     DBMS_XMLQUERY.closeContext(ctx);
258   END;
259   IF l_rows_processed <> 0 THEN
260     FND_FILE.put_line( FND_FILE.LOG,'8') ;
261     --get the length og the rowset header
262     l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
263     FND_FILE.put_line( FND_FILE.LOG,'9') ;
264   ELSE
265     l_resultOffset := 0;
266   END IF;
267 
268   if p_collector is not null then
269     select source_name
270     into l_collector
271     from jtf_rs_resource_extns
272     where resource_id=p_collector;
273   else
274      l_collector:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
275   end if;
276 
277   if p_campaign is not null then
278     select source_code
279     into l_campaign
280     from ams_source_codes
281     where source_code_id=p_campaign;
282   else
283     l_campaign:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
284   end if;
285 
286   select TRUNC(sysdate)
287   into l_sysdate
288   from dual;
289 
290    l_new_line := '
291 ';
292   FND_FILE.put_line( FND_FILE.LOG,'10') ;
293   /* Prepare the tag for the report heading */
294    --Start adding for bug 10213070 snuthala 19th Oct 10
295    --l_xml_header     := '<?xml version="1.0" encoding="UTF-8"?>';
296    -- Instead of hard coding the value, pick the charcter set value from "ICX: Client IANA Encoding" profile.
297    l_encoding       := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
298    l_xml_header     := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
299    --End adding for bug 10213070 snuthala 19th Oct 10
300   l_xml_header      := l_xml_header ||l_new_line||'<COLLCAMP>';
301   l_xml_header      := l_xml_header ||l_new_line||'    <PARAMETERS>'||l_new_line;
302   l_xml_header      := l_xml_header ||l_new_line||'        <P_DATE_FROM>'||TO_CHAR(l_from_date,'YYYY-MM-DD')||'</P_DATE_FROM>';
303   l_xml_header      := l_xml_header ||l_new_line||'        <P_DATE_TO>' ||TO_CHAR(l_to_date,'YYYY-MM-DD') ||'</P_DATE_TO>';
304   l_xml_header      := l_xml_header ||l_new_line||'        <P_TYPE>' ||l_coll_camp_typ||'</P_TYPE>';
305   l_xml_header      := l_xml_header ||l_new_line||'        <P_CAMPAIGN>' ||l_campaign ||'</P_CAMPAIGN>';
306   l_xml_header      := l_xml_header ||l_new_line||'        <P_COLLECTOR>' ||l_collector||'</P_COLLECTOR>';
307   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>';
308   l_xml_header      := l_xml_header ||l_new_line||'        <P_OUTCOME>' ||l_out_code||'</P_OUTCOME>';
309   l_xml_header      := l_xml_header ||l_new_line||'        <P_RESULT>' ||l_resl_code||'</P_RESULT>';
310   l_xml_header      := l_xml_header ||l_new_line||'        <P_REASON>' ||l_res_code||'</P_REASON>';
311 --  IF l_rows_processed=0 THEN
312     l_xml_header    := l_xml_header ||l_new_line||' <DATA_FOUND>' ||l_rows_processed||'</DATA_FOUND>';
313 --  END IF;
314 l_xml_header    := l_xml_header ||l_new_line||' <CURR_DATE>' ||TO_CHAR(l_sysdate,'YYYY-MM-DD')||'</CURR_DATE>';
315   l_xml_header        := l_xml_header ||l_new_line||'    </PARAMETERS>';
316   l_close_tag         := l_new_line||'</COLLCAMP>'||l_new_line;
317   l_xml_header_length := dbms_lob.getlength(l_xml_header);
318   tempResult          :=l_xml_header;
319   IF l_rows_processed <> 0 THEN
320     --copy result set to tempResult
321     dbms_lob.copy(tempResult,l_result,dbms_lob.getlength(l_result)-l_resultOffset, l_xml_header_length,l_resultOffset);
322   ELSE
323     dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
324     dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
325     dbms_lob.writeAppend(tempResult, LENGTH(l_xml_header), l_xml_header);
326   END IF;
327   --append the close tag to tempResult
328   dbms_lob.writeAppend(tempResult, LENGTH(l_close_tag), l_close_tag);
329   --print to the o/p file
330   print_clob(lob_loc => tempResult);
331   FND_FILE.put_line( FND_FILE.LOG,'15--end') ;
332   LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || '-end');
333 EXCEPTION
334 WHEN OTHERS THEN
335   --dbms_output.put_line('err'||sqlerrm);
336   LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' -');
337 END gen_xml_data_collcamp;
338 
339 BEGIN
340    G_LOG_ENABLED := 'N';
341    G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
342 
343    /* getting msg logging info */
344    G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
345    if (G_LOG_ENABLED = 'N') then
346       G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
347    else
348       G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
349    end if;
350 
351    LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
352 
353 END;