[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_WF_PROCESSES
Source
1 PACKAGE BODY fnd_flex_wf_processes AS
2 /* $Header: AFFFWFPB.pls 120.1.12010000.1 2008/07/25 14:14:57 appldev ship $ */
3
4 --
5 -- Global Variables
6 --
7 g_internal_messages VARCHAR2(32000) := NULL;
8 g_validate_parameters BOOLEAN := TRUE;
9 g_session_mode VARCHAR2(100) := NULL;
10 g_newline VARCHAR2(100);
11 --
12 -- Who information.
13 --
14 TYPE who_type IS RECORD
15 (
16 created_by NUMBER,
17 creation_date DATE,
18 last_updated_by NUMBER,
19 last_update_date DATE,
20 last_update_login NUMBER
21 );
22
23 --
24 -- Private Functions/Procedures
25 --
26
27 -- --------------------------------------------------------------------
28 FUNCTION get_who RETURN who_type
29 IS
30 l_who who_type;
31 BEGIN
32 set_session_mode(g_session_mode);
33 IF (g_session_mode = 'seed_data') THEN
34 l_who.created_by := 1;
35 l_who.creation_date := To_date('01011996', 'MMDDYYYY');
36 ELSIF (g_session_mode = 'customer_data') THEN
37 l_who.created_by := 0;
38 l_who.creation_date := Sysdate;
39 END IF;
40 l_who.last_updated_by := l_who.created_by;
41 l_who.last_update_date := l_who.creation_date;
42 l_who.last_update_login := 0;
43 RETURN(l_who);
44 END get_who;
45
46 -- --------------------------------------------------------------------
47 PROCEDURE message(msg VARCHAR2) IS
48 BEGIN
49 g_internal_messages := g_internal_messages || msg || g_newline;
50 END message;
51
52 -- --------------------------------------------------------------------
53 PROCEDURE message_init IS
54 BEGIN
55 g_internal_messages := '';
56 message('FND_FLEX_WF_PROCESSES API Messages:');
57 END message_init;
58
59 -- --------------------------------------------------------------------
60 PROCEDURE validate_flexfield_structure(x_application_id IN NUMBER,
61 x_code IN VARCHAR2,
62 x_num IN NUMBER)
63 IS
64 dummy NUMBER;
65 BEGIN
66 SELECT 1
67 INTO dummy
68 FROM fnd_id_flex_structures
69 WHERE application_id = x_application_id
70 AND id_flex_code = x_code
71 AND id_flex_num = x_num;
72 EXCEPTION
73 WHEN OTHERS THEN
74 raise_application_error(-20001,
75 'Unable to locate flexfield structure:(' ||
76 TO_CHAR(x_application_id) || ',' ||
77 x_code || ',' || TO_CHAR(x_num) ||
78 ').' ||
79 g_newline || ' - Sqlerrm:' || Sqlerrm);
80 END;
81
82 -- --------------------------------------------------------------------
83 PROCEDURE validate_workflow_process(x_item_type IN VARCHAR2,
84 x_process IN VARCHAR2)
85 IS
86 dummy NUMBER;
87 BEGIN
88 BEGIN
89 SELECT 1
90 INTO dummy
91 FROM wf_runnable_processes_v
92 WHERE item_type = x_item_type
93 AND process_name = x_process;
94 EXCEPTION
95 WHEN no_data_found THEN
96 raise_application_error(-20001,
97 'Unable to locate process ' || x_process ||
98 ' in workflow item ' || x_item_type || '.' ||
99 g_newline || ' - Sqlerrm:' || Sqlerrm);
100 END;
101 EXCEPTION
102 WHEN OTHERS THEN
103 raise_application_error(-20001, 'validate_workflow_process() failed.' ||
104 g_newline || ' - Sqlerrm:' || Sqlerrm);
105 END;
106
107 -- --------------------------------------------------------------------
108 PROCEDURE validate_workflow_item(x_name IN VARCHAR2)
109 IS
110 dummy NUMBER;
111 BEGIN
112 BEGIN
113 SELECT 1
114 INTO dummy
115 FROM wf_item_types_vl item
116 WHERE item.name = x_name;
117 EXCEPTION
118 WHEN no_data_found THEN
119 raise_application_error(-20001,
120 'Unable to locate flexfield workflow item: ' ||
121 x_name || '.' ||
122 g_newline || ' - Sqlerrm:' || Sqlerrm);
123 END;
124
125 validate_workflow_process(x_name, 'DEFAULT_ACCOUNT_GENERATION');
126 EXCEPTION
127 WHEN OTHERS THEN
128 raise_application_error(-20001, 'validate_workflow_item() failed.' ||
129 g_newline || ' - Sqlerrm:' || Sqlerrm);
130 END;
131
132 --
133 -- Public Functions/Procedures
134 --
135
136 -- --------------------------------------------------------------------
137 -- Sets the validate mode on/off
138 PROCEDURE validate_on IS
139 BEGIN
140 g_validate_parameters := TRUE;
141 END validate_on;
142
143 -- --------------------------------------------------------------------
144 PROCEDURE validate_off IS
145 BEGIN
146 g_validate_parameters := FALSE;
147 END validate_off;
148
149 -- --------------------------------------------------------------------
150 -- Returns error messages
151 FUNCTION message RETURN VARCHAR2
152 IS
153 l_internal_messages VARCHAR2(32000);
154 BEGIN
155 message('End of Messages.');
156 l_internal_messages := g_internal_messages;
157 message_init();
158 RETURN l_internal_messages;
159 END message;
160
161 -- --------------------------------------------------------------------
162 -- Sets if this is seed data changes or customer changes
163 PROCEDURE set_session_mode(session_mode IN VARCHAR2) IS
164 BEGIN
165 IF (session_mode IN ('customer_data', 'seed_data')) THEN
166 g_session_mode := session_mode;
167 ELSE
168 raise_application_error(-20001,
169 'bad session mode:''' || session_mode ||
170 '''. Use set_session_mode() to specify. ' ||
171 'Valid values are: ''customer_data'', ''seed_data''.');
172 END IF;
173 END;
174
175 -- --------------------------------------------------------------------
176 -- Adds a new flexfield workflow item and process
177 PROCEDURE add_workflow_item_type(x_application_id IN NUMBER,
178 x_code IN VARCHAR2,
179 x_num IN NUMBER,
180 x_item_type IN VARCHAR2,
181 x_process_name IN VARCHAR2)
182 IS
183 l_who who_type;
184 BEGIN
185 message_init();
186 l_who := get_who();
187
188 IF (g_validate_parameters) THEN
189 validate_flexfield_structure(x_application_id, x_code, x_num);
190 validate_workflow_item(x_item_type);
191 validate_workflow_process(x_item_type, x_process_name);
192 END IF;
193
194 INSERT INTO fnd_flex_workflow_processes
195 (application_id, id_flex_code, id_flex_num,
196 wf_item_type, wf_process_name,
197 last_update_date, last_updated_by,
198 creation_date, created_by, last_update_login)
199 VALUES
200 (x_application_id, x_code, x_num,
201 x_item_type, x_process_name,
202 l_who.last_update_date, l_who.last_updated_by,
203 l_who.creation_date, l_who.created_by, l_who.last_update_login);
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 raise_application_error(-20001,
208 'Insert on fnd_flex_workflow_processes failed.' ||
209 g_newline || ' - Sqlerrm:' || Sqlerrm);
210 END add_workflow_item_type;
211
212 -- --------------------------------------------------------------------
213 PROCEDURE delete_workflow_item_type(x_application_id IN NUMBER,
214 x_code IN VARCHAR2,
215 x_num IN NUMBER,
216 x_item_type IN VARCHAR2)
217 IS
218 BEGIN
219 message_init();
220 DELETE
221 FROM fnd_flex_workflow_processes
222 WHERE application_id = x_application_id
223 AND id_flex_code = x_code
224 AND id_flex_num = x_num
225 AND wf_item_type = x_item_type;
226 EXCEPTION
227 WHEN OTHERS THEN
228 raise_application_error(-20001,
229 'Delete failed.' ||
230 g_newline || ' - Sqlerrm:' || Sqlerrm);
231 END delete_workflow_item_type;
232
233 -- --------------------------------------------------------------------
234 -- Changes the process associated with an flex workflow item
235 PROCEDURE change_workflow_process(x_application_id IN NUMBER,
236 x_code IN VARCHAR2,
237 x_num IN NUMBER,
238 x_item_type IN VARCHAR2,
239 x_process_name IN VARCHAR2)
240 IS
241 l_who who_type;
242 BEGIN
243 message_init();
244 l_who := get_who();
245
246 IF (g_validate_parameters) THEN
247 validate_flexfield_structure(x_application_id, x_code, x_num);
248 validate_workflow_item(x_item_type);
249 validate_workflow_process(x_item_type, x_process_name);
250 END IF;
251
252 UPDATE fnd_flex_workflow_processes
253 SET wf_process_name = x_process_name
254 WHERE application_id = x_application_id
255 AND id_flex_code = x_code
256 AND id_flex_num = x_num
257 AND wf_item_type = x_item_type;
258
259 IF SQL%NOTFOUND THEN
260 raise_application_error(-20001,
261 'Unable to locate workflow item ' ||
262 x_item_type || ' for flexfield structure: ' ||
263 TO_CHAR(x_application_id) || ',' ||
264 x_code || ',' || TO_CHAR(x_num));
265 END IF;
266
267 IF (g_session_mode = 'customer_data') THEN
268 UPDATE fnd_flex_workflow_processes
269 SET last_update_date = l_who.last_update_date,
270 last_updated_by = l_who.last_updated_by,
271 last_update_login = l_who.last_update_login
272 WHERE application_id = x_application_id
273 AND id_flex_code = x_code
274 AND id_flex_num = x_num
275 AND x_item_type = x_item_type;
276 END IF;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 raise_application_error(-20001,
281 'Update on fnd_flex_workflow_processes failed.'||
282 g_newline || ' - Sqlerrm:' || Sqlerrm);
283 END change_workflow_process;
284
285 PROCEDURE add_new_workflow_item_type(p_application_short_name IN VARCHAR2,
286 p_id_flex_code IN VARCHAR2,
287 p_wf_item_type IN VARCHAR2)
288 IS
289 l_who who_type;
290 l_wf_process_name VARCHAR2(100) := 'DEFAULT_ACCOUNT_GENERATION';
291 l_application_id NUMBER;
292 BEGIN
293 message_init();
294 l_who := get_who();
295
296 BEGIN
297 SELECT application_id
298 INTO l_application_id
299 FROM fnd_application
300 WHERE application_short_name = p_application_short_name;
301 EXCEPTION
302 WHEN no_data_found THEN
303 raise_application_error(-20001, '''' || p_application_short_name ||
304 ''' application does not exist.');
305 END;
306
307 validate_workflow_item(p_wf_item_type);
308 validate_workflow_process(p_wf_item_type, l_wf_process_name);
309
310 INSERT INTO fnd_flex_workflow_processes
311 (
312 application_id,
313 id_flex_code,
314 id_flex_num,
315 wf_item_type,
316 wf_process_name,
317
318 last_update_date,
319 last_updated_by,
320 creation_date,
321 created_by,
322 last_update_login
323 )
324 SELECT
325 ifst.application_id,
326 ifst.id_flex_code,
327 ifst.id_flex_num,
328 p_wf_item_type,
329 l_wf_process_name,
330
331 l_who.last_update_date,
332 l_who.last_updated_by,
333 l_who.creation_date,
334 l_who.created_by,
335 l_who.last_update_login
336 FROM fnd_id_flex_structures ifst
337 WHERE ifst.application_id = l_application_id
338 AND ifst.id_flex_code = p_id_flex_code
339 AND NOT exists (SELECT NULL
340 FROM fnd_flex_workflow_processes fwp
341 WHERE fwp.application_id = ifst.application_id
342 AND fwp.id_flex_code = ifst.id_flex_code
343 AND fwp.id_flex_num = ifst.id_flex_num
344 AND fwp.wf_item_type = p_wf_item_type);
345
346 EXCEPTION
347 WHEN OTHERS THEN
348 raise_application_error(-20001,
349 'add_new_workflow_item_type() failed.' ||
350 g_newline || ' - Sqlerrm:' || Sqlerrm);
351 END add_new_workflow_item_type;
352
353 -- --------------------------------------------------------------------
354 BEGIN
355 g_newline := fnd_global.newline;
356 message_init();
357 END fnd_flex_wf_processes;