DBA Data[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;