[Home] [Help]
PACKAGE BODY: APPS.ARCM_EXTRACT_XML_CF
Source
1 PACKAGE BODY ARCM_EXTRACT_XML_CF AS
2 /* $Header: ARCMXTCFB.pls 120.2 2006/05/03 08:19:57 kjoshi noship $ */
3
4 pg_bind_var NUMBER(10);
5 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
6
7 PROCEDURE debug (
8 p_message_name IN VARCHAR2 ) IS
9 BEGIN
10 ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.ARCM_EXTRACT_XML_CF' );
11 END;
12
13 FUNCTION get_case_folder_id return NUMBER is
14 BEGIN
15 return pg_bind_var;
16 END get_case_folder_id;
17
18 /*========================================================================+
19 | PUBLIC PROCEDURE process_xml_data |
20 | |
21 | DESCRIPTION |
22 | |
23 | This procedure writes the output file using the data generated in |
24 | procedure EXTRACT. |
25 | |
26 | PSEUDO CODE/LOGIC |
27 | |
28 | PARAMETERS |
29 | |
30 | |
31 | KNOWN ISSUES |
32 | |
33 | NOTES |
34 | |
35 | |
36 | MODIFICATION HISTORY |
37 | Date Author Description of Changes |
38 | 17-NOV-2005 kjoshi Created |
39 | |
40 *=======================================================================*/
41
42 PROCEDURE process_xml_data(p_xml_clob CLOB) IS
43
44 l_clob_size NUMBER;
45 l_offset NUMBER;
46 l_chunk_size INTEGER;
47 l_chunk VARCHAR2(32767);
48
49 BEGIN
50 IF pg_debug = 'Y'
51 THEN
52 debug('ARCM_EXTRACT_XML_CF.process_xml_data(+)');
53 END IF;
54
55 -- get length of internal lob and open the dest. file.
56
57 l_clob_size := dbms_lob.getlength(p_xml_clob);
58
59 IF (l_clob_size = 0) THEN
60 IF pg_debug = 'Y'
61 THEN
62 debug('CLOB is empty');
63 END IF;
64 RETURN;
65 END IF;
66
67 l_offset := 1;
68 l_chunk_size := 3000;
69
70 IF pg_debug = 'Y'
71 THEN
72 debug('Unloading... ' || l_clob_size);
73 END IF;
74
75 WHILE (l_clob_size > 0) LOOP
76
77
78 l_chunk := dbms_lob.substr (p_xml_clob, l_chunk_size, l_offset);
79
80 fnd_file.put(
81 which => fnd_file.output,
82 buff => l_chunk);
83
84 l_clob_size := l_clob_size - l_chunk_size;
85 l_offset := l_offset + l_chunk_size;
86
87 END LOOP;
88
89 fnd_file.new_line(fnd_file.output,1);
90 IF pg_debug = 'Y'
91 THEN
92 debug('ARCM_EXTRACT_XML_CF.process_xml_data(-)');
93 END IF;
94
95 EXCEPTION
96 WHEN OTHERS THEN
97 IF pg_debug = 'Y'
98 THEN
99 debug('EXCEPTION: OTHERS process_clob');
100 debug(sqlcode);
101 debug(sqlerrm);
102 END IF;
103 RAISE;
104
105 END process_xml_data;
106
107 PROCEDURE RAISE_BE_XTRACT (
108 P_CASE_FOLDER_ID IN NUMBER) IS
109 l_list WF_PARAMETER_LIST_T;
110 l_param WF_PARAMETER_T;
111 l_key VARCHAR2(240);
112 l_event_name VARCHAR2(240) := 'oracle.apps.ar.cmgt.CaseFolder.extract';
113 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
114 l_credit_request_id NUMBER;
115 l_source_name AR_CMGT_CREDIT_REQUESTS.source_name%TYPE;
116 l_source_column1 AR_CMGT_CREDIT_REQUESTS.source_column1%TYPE;
117 l_source_column2 AR_CMGT_CREDIT_REQUESTS.source_column2%TYPE;
118 l_source_column3 AR_CMGT_CREDIT_REQUESTS.source_column3%TYPE;
119 l_source_user_id ar_cmgt_credit_requests.SOURCE_USER_ID%type;
120 l_source_resp_id ar_cmgt_credit_requests.SOURCE_RESP_ID%type;
121 l_source_resp_appln_id ar_cmgt_credit_requests.SOURCE_RESP_APPLN_ID%type;
122 l_source_security_group_id ar_cmgt_credit_requests.SOURCE_SECURITY_GROUP_ID%type;
123 l_source_org_id ar_cmgt_credit_requests.SOURCE_ORG_ID%type;
124 l_cf_not_found EXCEPTION;
125 CURSOR get_case_folder_info IS
126 SELECT cr.credit_request_id,
127 cr.source_name, cr.source_column1,
128 cr.source_column2, cr.source_column3,
129 cr.source_user_id,
130 cr.source_resp_id,
131 cr.source_resp_appln_id,
132 cr.source_security_group_id,
133 cr.source_org_id
134 FROM ar_cmgt_credit_requests cr,
135 ar_cmgt_case_folders cf
136 WHERE case_folder_id = P_CASE_FOLDER_ID
137 AND cr.credit_request_id = cf.credit_request_id;
138
139 BEGIN
140 IF pg_debug = 'Y'
141 THEN
142 debug('ARCM_EXTRACT_XML_CF.RAISE_BE_XTRACT(+)');
143 debug('case folder id '|| P_CASE_FOLDER_ID );
144 END IF;
145 --open the cursor and fetch the attributes
146
147 OPEN get_case_folder_info;
148 FETCH get_case_folder_info INTO l_credit_request_id,
149 l_source_name,
150 l_source_column1,
151 l_source_column2,
152 l_source_column3,
153 l_source_user_id,
154 l_source_resp_id,
155 l_source_resp_appln_id,
156 l_source_security_group_id,
157 l_source_org_id ;
158
159 IF get_case_folder_info%NOTFOUND THEN
160 raise l_cf_not_found;
161 END IF;
162 IF pg_debug = 'Y'
163 THEN
164 debug('Credit Request Id ' || l_credit_request_id);
165 debug('Source name '|| l_source_name );
166 debug('Source Col 1 '|| l_source_column1 );
167 debug('Source Col 2 '|| l_source_column2 );
168 debug('Source Col 3 '|| l_source_column3 );
169 debug('User Id '|| l_source_user_id );
170 debug('Resp Id '|| l_source_resp_id );
171 debug('Appl Id '|| l_source_resp_appln_id );
172 debug('Security Id '|| l_source_security_group_id );
173 debug('Org Id '|| l_source_org_id );
174 END IF;
175 CLOSE get_case_folder_info ;
176
177 --Get the item key
178 l_key := AR_CMGT_EVENT_PKG.item_key( p_event_name => l_event_name,
179 p_unique_identifier => l_case_folder_id );
180
181 -- initialization of object variables
182
183 l_list := WF_PARAMETER_LIST_T();
184
185 -- Add Context values to the list
186 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
187
188
189 -- add more parameters to the parameters list
190
191 wf_event.AddParameterToList(p_name => 'CREDIT_REQUEST_ID',
192 p_value => l_credit_request_id,
193 p_parameterlist => l_list);
194
195 wf_event.AddParameterToList(p_name => 'CASE_FOLDER_ID',
196 p_value => l_case_folder_id,
197 p_parameterlist => l_list);
198
199 wf_event.AddParameterToList(p_name => 'SOURCE_NAME',
200 p_value => l_source_name,
201 p_parameterlist => l_list);
202
203 wf_event.AddParameterToList(p_name => 'SOURCE_COLUMN1',
204 p_value => l_source_column1,
205 p_parameterlist => l_list);
206 wf_event.AddParameterToList(p_name => 'SOURCE_COLUMN2',
207 p_value => l_source_column2,
208 p_parameterlist => l_list);
209 wf_event.AddParameterToList(p_name => 'SOURCE_COLUMN3',
210 p_value => l_source_column3,
211 p_parameterlist => l_list);
212
213 wf_event.AddParameterToList(p_name => 'SOURCE_USER_ID',
214 p_value => l_source_user_id,
215 p_parameterlist => l_list);
216
217 wf_event.AddParameterToList(p_name => 'SOURCE_RESP_ID',
218 p_value => l_source_resp_id,
219 p_parameterlist => l_list);
220 wf_event.AddParameterToList(p_name => 'SOURCE_RESP_APPLN_ID',
221 p_value => l_source_resp_appln_id,
222 p_parameterlist => l_list);
223 wf_event.AddParameterToList(p_name => 'SOURCE_SECURITY_GROUP_ID',
224 p_value => l_source_security_group_id,
225 p_parameterlist => l_list);
226 wf_event.AddParameterToList(p_name => 'SOURCE_ORG_ID',
227 p_value => l_source_org_id,
228 p_parameterlist => l_list);
229 wf_event.AddParameterToList(p_name => 'REQUEST_ID',
230 p_value => fnd_global.conc_request_id,
231 p_parameterlist => l_list);
232 -- Raise Event
233 AR_CMGT_EVENT_PKG.raise_event(
234 p_event_name => l_event_name,
235 p_event_key => l_key,
236 p_parameters => l_list );
237
238 l_list.DELETE;
239 IF pg_debug = 'Y'
240 THEN
241 debug('ARCM_EXTRACT_XML_CF.RAISE_BE_XTRACT(-)');
242 END IF;
243 END ;
244
245 /*========================================================================+
246 | PUBLIC PROCEDURE EXTRACT |
247 | |
248 | DESCRIPTION |
249 | |
250 | This procedure is used to create data from case folder details |
251 | to a CLOB which is then used as input to another procedure which |
252 | processes the CLOB data in xml format and puts it into an output file |
253 | |
254 | PSEUDO CODE/LOGIC |
255 | |
256 | PARAMETERS |
257 | |
258 | |
259 | KNOWN ISSUES |
260 | |
261 | NOTES |
262 | |
263 | |
264 | MODIFICATION HISTORY |
265 | Date Author Description of Changes |
266 | 17-NOV-2005 kjoshi Created |
267 | |
268 *=======================================================================*/
269
270 PROCEDURE EXTRACT (
271 ERRBUF IN OUT NOCOPY VARCHAR2,
272 RETCODE IN OUT NOCOPY VARCHAR2,
273 P_CASE_FOLDER_ID IN NUMBER) IS
274 l_result CLOB;
275 l_errNo NUMBER;
276 l_COUNT VARCHAR2(1);
277 No_Rows EXCEPTION;
278 l_rows_processed NUMBER;
279 l_errMsg VARCHAR2(200);
280 queryCtx DBMS_XMLquery.ctxType;
281 qryCtx DBMS_XMLGEN.ctxHandle;
282 l_xml_query VARCHAR2(32767);
283
284 BEGIN
285 IF pg_debug = 'Y'
286 THEN
287 debug('ARCM_EXTRACT_XML_CF.EXTRACT(+)');
288 debug ( 'case folder id : '|| p_case_folder_id);
289 END IF;
290 --initialize the return code.
291
292 RETCODE :=0;
293 pg_bind_var :=P_CASE_FOLDER_ID;
294 IF P_CASE_FOLDER_ID IS NOT NULL
295 THEN
296
297 --Validate Case_Folder_id
298 Begin
299
300 Select 'Y'
301 INTO L_COUNT
302 from AR_CMGT_CASE_FOLDERS
303 WHERE CASE_FOLDER_ID = P_CASE_FOLDER_ID;
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 RETCODE := 1;
307 ERRBUF := 'Invalid value of Case Folder Id ' ||P_CASE_FOLDER_ID;
308 return;
309 END;
310
311 l_xml_query := 'SELECT CFD.CASE_FOLDER_ID,CFD.DATA_POINT_ID,
312 DPD.DATA_POINT_NAME,CFD.DATA_POINT_VALUE,CFD.SCORE' ||
313 ' FROM AR_CMGT_CF_DTLS CFD,AR_CMGT_SCORABLE_DATA_POINTS_V DPD' ||
314 ' WHERE CASE_FOLDER_ID =ARCM_EXTRACT_XML_CF.get_case_folder_id' ||
315 ' AND CFD.DATA_POINT_ID = DPD.DATA_POINT_ID ' ;
316 ELSE
317 ERRBUF := 'Null values found in P_CASE_FOLDER_ID';
318 RETCODE :=1;
319 return;
320 END IF;
321
322 qryCtx := DBMS_XMLGEN.newContext(l_xml_query);
323
324 -- set the row set tag to Data Points
325 dbms_xmlgen.setRowSetTag(qryCtx,'CASE_FOLDER_DETAILS');
326
327 -- set the row tag to CF Dtls
328 dbms_xmlgen.setRowTag(qryCtx,'DATA_POINT_DETAIL');
332
329 l_result := DBMS_XMLGEN.getXML(qryCtx,DBMS_XMLGEN.NONE);
330 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
331 DBMS_XMLGEN.closeContext(qryCtx);
333
334 IF l_rows_processed >0
335 THEN
336 process_xml_data(l_result);
337 ELSE
338 ERRBUF := 'No data exists for the case folder id '|| P_CASE_FOLDER_ID;
339 RETCODE :=1;
340 return;
341 END IF;
342 -- raise the business event
343 RAISE_BE_XTRACT(P_CASE_FOLDER_ID);
344 IF pg_debug = 'Y'
345 THEN
346 debug('ARCM_EXTRACT_XML_CF.EXTRACT(-)');
347 END IF;
348 END EXTRACT;
349
350
351 END ARCM_EXTRACT_XML_CF;