[Home] [Help]
PACKAGE BODY: APPS.IEU_UWQ_WORK_PANEL_PVT
Source
1 PACKAGE BODY IEU_UWQ_WORK_PANEL_PVT AS
2 /* $Header: IEUVUWPB.pls 120.0 2005/06/02 15:50:41 appldev noship $ */
3
4 PROCEDURE GET_UWQ_ACTION_DATA
5 (P_UWQ_ACTION_DATA IN VARCHAR2,
6 X_UWQ_ACTION_DATA_LIST OUT NOCOPY IEU_UWQ_WORK_PANEL_PUB.UWQ_ACTION_DATA_REC_LIST) AS
7
8 tempString varchar2(4000);
9 list_ctr number := 0;
10 j number;
11 k number;
12 l_counter number;
13
14 BEGIN
15
16 j:= 1;
17 l_counter := 1;
18 k := 1;
19
20 IF (length(p_uwq_action_data) is not null)
21 THEN
22 WHILE (l_counter < length(p_uwq_action_data) )
23 LOOP
24 tempString := substr
25 (
26 p_uwq_action_data,
27 instr(p_uwq_action_data, fnd_global.local_chr(20),1,j),
28 ( instr(p_uwq_action_data, fnd_global.local_chr(28),1,j) -
29 instr(p_uwq_action_data, fnd_global.local_chr(20),1,j) + 1)
30 );
31
32 x_uwq_action_data_list(list_ctr).name :=
33 substr
34 ( tempString,
35 2,
36 instr(tempString, fnd_global.local_chr(31),1,k) - 2
37 );
38 x_uwq_action_data_list(list_ctr).value :=
39 substr
40 ( tempString,
41 instr(tempString, fnd_global.local_chr(31),1,k) + 1,
42 ( instr(tempString,fnd_global.local_chr(31),1,k+1) -
43 instr(tempString, fnd_global.local_chr(31),1,k) - 1)
44 );
45 x_uwq_action_data_list(list_ctr).type :=
46 substr
47 ( tempString,
48 instr(tempString, fnd_global.local_chr(31),1,k+1) + 1,
49 length(tempstring) - instr(tempString, fnd_global.local_chr(31),1,k+1) -1
50 );
51
52 l_counter := instr(p_uwq_action_data, fnd_global.local_chr(28),1,j);
53 j := j+1;
54 list_ctr := list_ctr + 1;
55
56 END LOOP;
57 END IF;
58
59 END GET_UWQ_ACTION_DATA;
60 PROCEDURE CALL_WORK_ACTIONS
61 (p_resource_id IN NUMBER,
62 p_langauge IN VARCHAR2,
63 p_source_lang IN VARCHAR2,
64 p_action_key IN VARCHAR2,
65 p_action_proc IN VARCHAR2,
66 p_work_action_data IN IEU_UWQ_WORK_PANEL_PUB.uwq_action_data_rec_list,
67 x_uwq_action_list OUT NOCOPY IEU_UWQ_WORK_PANEL_PUB.uwq_action_rec_list,
68 x_msg_count OUT NOCOPY NUMBER,
69 x_msg_data OUT NOCOPY VARCHAR2,
70 x_return_status OUT NOCOPY VARCHAR2) IS
71
72 l_work_action_data system.ACTION_INPUT_DATA_NST;
73 x_uwq_action_data SYSTEM.IEU_UWQ_WORK_ACTIONS_NST;
74 l_token_str VARCHAR2(500);
75
76 BEGIN
77
78
79 l_work_action_data := system.ACTION_INPUT_DATA_NST();
80 x_uwq_action_data := SYSTEM.IEU_UWQ_WORK_ACTIONS_NST();
81
82 FND_MSG_PUB.INITIALIZE;
83
84 FOR i in p_work_action_data.first .. p_work_action_data.last
85 LOOP
86 l_work_action_data.extend;
87 l_work_action_data(l_work_action_data.last) := system.ACTION_INPUT_DATA_OBJ
88 (
89 p_work_action_data(i).dataSetType,
90 p_work_action_data(i).dataSetID,
91 p_work_action_data(i).name,
92 p_work_action_data(i).value,
93 p_work_action_data(i).type);
94 END LOOP;
95
96 BEGIN
97 EXECUTE IMMEDIATE 'BEGIN '||p_action_proc||'( :1, :2, :3, :4 , :5, :6, :7, :8, :9); END;'
98 USING IN p_resource_id, IN p_langauge, IN p_source_lang, IN p_action_key, IN l_work_action_data ,
99 OUT x_uwq_action_data, OUT x_msg_count, OUT x_msg_data, OUT x_return_status;
100 EXCEPTION
101 WHEN OTHERS THEN
102 x_return_status := 'E';
103 l_token_str := substr(sqlerrm,1,150);
104 FND_MESSAGE.SET_NAME('IEU', 'IEU_WP_EXEC_WORK_ACT_FAILED');
105 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','GET_UWQ_ACTION_DATA.CALL_WORK_ACTIONS');
106 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
107
108 fnd_msg_pub.ADD;
109 fnd_msg_pub.Count_and_Get
110 (
111 p_count => x_msg_count,
112 p_data => x_msg_data
113 );
114 -- x_msg_data := sqlerrm;
115 END;
116
117 if (x_return_status = 'S')
118 then
119 if x_uwq_action_data is not null then
120 FOR i in 1..x_uwq_action_data.count
121 LOOP
122 x_uwq_action_list(i).uwq_action_key := x_uwq_action_data(i).uwq_action_key;
123 x_uwq_action_list(i).action_data := x_uwq_action_data(i).action_data;
124 x_uwq_action_list(i).dialog_style := x_uwq_action_data(i).dialog_style;
125 x_uwq_action_list(i).message := x_uwq_action_data(i).message;
126 END LOOP;
127 end if;
128 else
129 l_token_str := substr(sqlerrm,1,150);
130 FND_MESSAGE.SET_NAME('IEU', 'IEU_WP_EXEC_WORK_ACT_FAILED');
131 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','GET_UWQ_ACTION_DATA.CALL_WORK_ACTIONS');
132 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
133
134 fnd_msg_pub.ADD;
135 fnd_msg_pub.Count_and_Get
136 (
137 p_count => x_msg_count,
138 p_data => x_msg_data
139 );
140 end if;
141
142 END CALL_WORK_ACTIONS;
143 /*
144 PROCEDURE CALL_INFO_ACTIONS (
145 p_resource_id IN NUMBER,
146 p_language IN VARCHAR2,
147 p_source_lang IN VARCHAR2,
148 p_action_key IN VARCHAR2,
149 p_exec_proc IN VARCHAR2,
150 p_workitem_data_list IN IEU_UWQ_WORK_PANEL_PUB.uwq_action_data_rec_list,
151 x_work_notes_long_list OUT NOCOPY IEU_UWQ_WORK_PANEL_PVT.t_work_notes_long_data,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2,
154 x_return_status OUT NOCOPY VARCHAR2
155 )
156 is
157
158 l_workitem_data_list SYSTEM.ACTION_INPUT_DATA_NST;
159 l_work_notes_data_list SYSTEM.app_info_data_nst;
160 l_work_notes_clob_list IEU_UWQ_WORK_PANEL_PUB.t_app_info_data_rec_list;
161 l_token_str VARCHAR2(500);
162
163 clob_selected CLOB;
164 read_amount NUMBER;
165 read_offset NUMBER;
166 buffer long;
167 clob_length number;
168 l_ctr binary_integer;
169 BEGIN
170
171 ---- Convert p_workitem_data_list(TOR) to l_workitem_data_list(NESTED)--
172 l_workitem_data_list := SYSTEM.ACTION_INPUT_DATA_NST();
173
174 FND_MSG_PUB.INITIALIZE;
175
176 for i in 1..p_workitem_data_list.count loop
177 l_workitem_data_list.EXTEND;
178 l_workitem_data_list(l_workitem_data_list.LAST) := SYSTEM.ACTION_INPUT_DATA_OBJ(
179 null,
180 null,
181 p_workitem_data_list(i).NAME,
182 p_workitem_data_list(i).VALUE,
183 p_workitem_data_list(i).TYPE
184 );
185 end loop;
186
187 -------- remove after testing nto sure ---------------
188 l_work_notes_data_list := SYSTEM.app_info_data_nst();
189 ------------------------------------------------------
190 BEGIN
191
192 execute immediate
193 'begin '|| p_exec_proc || '(' || ':p_resource_id,' || ':p_language,' || ':p_source_lang,' || ':p_action_key,' || ':l_workitem_data_list,' || ':l_work_notes_data_list,' || ':x_msg_count,' ||':x_msg_data,' || ':x_return_status);end;'
194 using in p_resource_id, p_language, p_source_lang, p_action_key, l_workitem_data_list, out l_work_notes_data_list, out x_msg_count, out x_msg_data, out x_return_status;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 x_return_status := 'E';
199 l_token_str := substr(sqlerrm,1,150);
200 FND_MESSAGE.SET_NAME('IEU', 'IEU_WP_EXEC_INFO_ACT_FAILED');
201 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','GET_UWQ_ACTION_DATA.CALL_INFO_ACTIONS');
202 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
203
204 fnd_msg_pub.ADD;
205 fnd_msg_pub.Count_and_Get
206 (
207 p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210 -- x_msg_data := sqlerrm;
211 END;
212
213 ---- Convert l_work_notes_data_list(NESTED CLOB) to l_workitem_data_list(TOR LONG)--
214 begin
215 read_offset := 1;
216 l_ctr := 1;
217 for i in 1..l_work_notes_data_list.count loop
218 clob_selected := l_work_notes_data_list(i).APP_INFO_DETAIL;
219 clob_length := dbms_lob.getlength(clob_selected);
220 --dbms_output.put_line('Clob length : ' || to_char(clob_length));
221 if read_offset > clob_length then
222 x_work_notes_long_list(l_ctr).REC_ID := i;
223 x_work_notes_long_list(l_ctr).NOTES_HEAD := l_work_notes_data_list(i).APP_INFO_HEADER;
224 x_work_notes_long_list(l_ctr).NOTES_DET := buffer;
225 l_ctr := l_ctr + 1;
226 end if;
227 while read_offset <= clob_length loop
228 read_amount := 32700;
229 dbms_lob.read(clob_selected, read_amount, read_offset, buffer);
230 x_work_notes_long_list(l_ctr).REC_ID := i;
231 x_work_notes_long_list(l_ctr).NOTES_HEAD := l_work_notes_data_list(i).APP_INFO_HEADER;
232 x_work_notes_long_list(l_ctr).NOTES_DET := buffer;
233 l_ctr := l_ctr + 1;
234 --dbms_output.put_line('Total: ' || dbms_lob.getlength(clob_selected) || ' Selected: ' || length(buffer));
235 --dbms_output.put_line('Buffer length : ' || length(buffer));
236 buffer := null;
237 read_offset := read_offset + read_amount;
238 end loop;
239 end loop;
240 exception
241 when no_data_found then null;
242 end;
243
244 END CALL_INFO_ACTIONS;
245 */
246 PROCEDURE CALL_INFO_ACTIONS (
247 p_resource_id IN NUMBER,
248 p_language IN VARCHAR2,
249 p_source_lang IN VARCHAR2,
250 p_action_key IN VARCHAR2,
251 p_exec_proc IN VARCHAR2,
252 p_workitem_data_list IN IEU_UWQ_WORK_PANEL_PUB.uwq_action_data_rec_list,
253 x_work_notes_long_list OUT NOCOPY IEU_UWQ_WORK_PANEL_PVT.t_work_notes_long_data,
254 x_msg_count OUT NOCOPY NUMBER,
255 x_msg_data OUT NOCOPY VARCHAR2,
256 x_return_status OUT NOCOPY VARCHAR2
257 )
258 is
259
260 l_workitem_data_list SYSTEM.ACTION_INPUT_DATA_NST;
261 l_work_notes_data_list SYSTEM.APP_INFO_HEADER_NST;
262 --l_work_notes_clob_list IEU_UWQ_WORK_PANEL_PUB.t_app_info_data_rec_list;
263 l_token_str VARCHAR2(500);
264 l_prev_header varchar2(4000);
265 l_curr_header varchar2(4000);
266 l_first_header varchar2(1);
267 l_rec_id number(10);
268
269 clob_selected CLOB;
270 read_amount NUMBER;
271 read_offset NUMBER;
272 -- buffer VARCHAR2(32767);
273 buffer long;
274 clob_length number;
275 l_ctr binary_integer;
276 BEGIN
277
278 l_first_header := 'Y';
279 ---- Convert p_workitem_data_list(TOR) to l_workitem_data_list(NESTED)--
280 l_workitem_data_list := SYSTEM.ACTION_INPUT_DATA_NST();
281 l_work_notes_data_list := SYSTEM.APP_INFO_HEADER_NST();
282
283 FND_MSG_PUB.INITIALIZE;
284
285 for i in 1..p_workitem_data_list.count loop
286 l_workitem_data_list.EXTEND;
287 l_workitem_data_list(l_workitem_data_list.LAST) := SYSTEM.ACTION_INPUT_DATA_OBJ(
288 null,
289 null,
290 p_workitem_data_list(i).NAME,
291 p_workitem_data_list(i).VALUE,
292 p_workitem_data_list(i).TYPE
293 );
294 end loop;
295
296 BEGIN
297 execute immediate
298 'begin '|| p_exec_proc || '(' || ':p_resource_id,' || ':p_language,' || ':p_source_lang,' || ':p_action_key,' || ':l_workitem_data_list,' || ':l_work_notes_data_list,' || ':x_msg_count,' ||':x_msg_data,' || ':x_return_status);end;'
299 using in p_resource_id, p_language, p_source_lang, p_action_key, l_workitem_data_list, out l_work_notes_data_list, out x_msg_count, out x_msg_data, out x_return_status;
300
301
302 for i in 1..l_work_notes_data_list.count loop
303 x_work_notes_long_list(i).REC_ID := i;
304 x_work_notes_long_list(i).NOTES_HEAD := l_work_notes_data_list(i).APP_INFO_HEADER;
305 x_work_notes_long_list(i).NOTES_DET := null;
306 -- dbms_output.put_line('Sample Count ' || x_work_notes_long_list.count );
307 end loop;
308
309 EXCEPTION
310 WHEN OTHERS THEN
311 x_return_status := 'E';
312 l_token_str := substr(sqlerrm,1,150);
313 FND_MESSAGE.SET_NAME('IEU', 'IEU_WP_EXEC_INFO_ACT_FAILED');
314 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','GET_UWQ_ACTION_DATA.CALL_INFO_ACTIONS');
315 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
316
317 fnd_msg_pub.ADD;
318 fnd_msg_pub.Count_and_Get
319 (
320 p_count => x_msg_count,
321 p_data => x_msg_data
322 );
323
324 -- x_msg_data := sqlerrm;
325 END;
326
327 END CALL_INFO_ACTIONS;
328
329
330 PROCEDURE CALL_MESG_ACTIONS (
331 p_resource_id IN NUMBER,
332 p_language IN VARCHAR2,
333 p_source_lang IN VARCHAR2,
334 p_action_key IN VARCHAR2,
335 p_exec_proc IN VARCHAR2,
336 p_workitem_data_list IN IEU_UWQ_WORK_PANEL_PUB.uwq_action_data_rec_list,
337 x_work_mesg OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY VARCHAR2,
340 x_return_status OUT NOCOPY VARCHAR2
341 )
342 is
343
344 l_workitem_data_list SYSTEM.ACTION_INPUT_DATA_NST;
345 l_uwq_mesg VARCHAR2(2000);
346 l_token_str VARCHAR2(500);
347
348 BEGIN
349
350 ---- Convert p_workitem_data_list(TOR) to l_workitem_data_list(NESTED)--
351 l_workitem_data_list := SYSTEM.ACTION_INPUT_DATA_NST();
352
353 FND_MSG_PUB.INITIALIZE;
354
355 for i in 1..p_workitem_data_list.count loop
356 l_workitem_data_list.EXTEND;
357 l_workitem_data_list(l_workitem_data_list.LAST) := SYSTEM.ACTION_INPUT_DATA_OBJ(
358 null,
359 null,
360 p_workitem_data_list(i).NAME,
361 p_workitem_data_list(i).VALUE,
362 p_workitem_data_list(i).TYPE
363 );
364 end loop;
365
366 BEGIN
367 execute immediate
368 'begin '|| p_exec_proc || '(' || ':p_resource_id,' || ':p_language,' || ':p_source_lang,' || ':p_action_key,' || ':l_workitem_data_list,' || ':l_uwq_mesg,' || ':x_msg_count,' ||':x_msg_data,' || ':x_return_status);end;'
369 using in p_resource_id, p_language, p_source_lang, p_action_key, l_workitem_data_list, out x_work_mesg, out x_msg_count, out x_msg_data, out x_return_status;
370 EXCEPTION
371 WHEN OTHERS THEN
372 x_return_status := 'E';
373 l_token_str := substr(sqlerrm,1,150);
374 FND_MESSAGE.SET_NAME('IEU', 'IEU_WP_EXEC_MESG_ACT_FAILED');
375 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','GET_UWQ_ACTION_DATA.CALL_MESG_ACTIONS');
376 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
377
378 fnd_msg_pub.ADD;
379 fnd_msg_pub.Count_and_Get
380 (
381 p_count => x_msg_count,
382 p_data => x_msg_data
383 );
384 -- x_msg_data := sqlerrm;
385 END;
386
387 END CALL_MESG_ACTIONS;
388
389 END IEU_UWQ_WORK_PANEL_PVT;