[Home] [Help]
PACKAGE BODY: APPS.AZW_FLOW
Source
1 PACKAGE BODY AZW_FLOW AS
2 /* $Header: AZWFLOWB.pls 115.13 2002/12/27 23:59:49 angupta ship $: */
3
4 --
5 -- application_not_found
6 --
7 -- Private function. Called by populate_product_flows.
8 -- Given an application id, return TRUE if it exists in az_product_phases
9 --
10
11 FUNCTION application_not_found(p_application_id NUMBER) RETURN BOOLEAN IS
12
13 ret BOOLEAN DEFAULT FALSE;
14 v_cnt INTEGER DEFAULT 0;
15
16 BEGIN
17 SELECT COUNT(*)
18 INTO v_cnt
19 FROM az_product_phases azpp
20 WHERE application_id = p_application_id;
21
22 IF (v_cnt = 0) THEN
23 ret := TRUE;
24 ELSE
25 ret := FALSE;
26 END IF;
27 RETURN ret;
28
29 EXCEPTION
30 WHEN app_exception.application_exception THEN
31 RAISE;
32 WHEN OTHERS THEN
33 --DBMS_OUTPUT.PUT_LINE('error: process_not_found: ' || SQLERRM);
34 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
35 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
36 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
37 fnd_message.set_token('AZW_ERROR_PROC','azw_flow.application_not_found');
38 fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_product_phases');
39 APP_EXCEPTION.RAISE_EXCEPTION;
40 END application_not_found;
41
42
43 --
44 -- POPULATE_PRODUCT_FLOWS
45 --
46 -- Public procedure.
47 -- Retrieve all workflow processes and store them in AZ_PRODUCT_FLOWS.
48 --
49
50 PROCEDURE populate_product_flows IS
51
52 CURSOR processes_cursor IS
53 SELECT wav.item_type, wav.name,
54 waav.text_default application_id,
55 waav1.text_default display_order,
56 waav2.text_default ctxt_type,
57 waav3.text_default parent_id,
58 waav4.text_default process_type
59 FROM wf_activities_vl wav,
60 wf_activity_attributes_vl waav,
61 wf_activity_attributes_vl waav1,
62 wf_activity_attributes_vl waav2,
63 wf_activity_attributes_vl waav3,
64 wf_activity_attributes_vl waav4
65 WHERE wav.item_type like 'AZW%'
66 AND wav.type = 'PROCESS'
67 AND wav.runnable_flag = 'Y'
68 AND wav.end_date is null
69 AND waav.activity_item_type = wav.item_type
70 AND waav.activity_name = wav.name
71 AND waav.name = 'AZW_IA_WFPROD'
72 AND waav.activity_version =
73 (SELECT MAX(activity_version)
74 FROM wf_activity_attributes_vl
75 WHERE activity_item_type = wav.item_type
76 AND activity_name = wav.name
77 AND name = 'AZW_IA_WFPROD')
78 AND waav1.activity_item_type = wav.item_type
79 AND waav1.activity_name = wav.name
80 AND waav1.name = 'AZW_IA_VAWM'
81 AND waav1.text_default IS NOT NULL
82 AND waav1.activity_version =
83 (SELECT MAX(activity_version)
84 FROM wf_activity_attributes_vl
85 WHERE activity_item_type = wav.item_type
86 AND activity_name = wav.name
87 AND name = 'AZW_IA_VAWM')
88 AND waav2.activity_item_type = wav.item_type
89 AND waav2.activity_name = wav.name
90 AND waav2.name = 'AZW_IA_CTXTYP'
91 AND exists
92 (SELECT lookup_code
93 FROM fnd_lookups
94 where lookup_code = waav2.text_default
95 AND lookup_type = 'AZ_CONTEXT_TYPE')
96 AND waav2.activity_version =
97 (SELECT MAX(activity_version)
98 FROM wf_activity_attributes_vl
99 WHERE activity_item_type = wav.item_type
100 AND activity_name = wav.name
101 AND name = 'AZW_IA_CTXTYP')
102 AND waav3.activity_item_type = wav.item_type
103 AND waav3.activity_name = wav.name
104 AND waav3.name = 'AZW_IA_PARENTID'
105 AND exists
106 (SELECT group_id
107 FROM az_groups ag
108 WHERE ag.group_id = waav3.text_default
109 AND ag.process_type = fnd_profile.value('AZ_CURRENT_MODE'))
110 AND waav3.activity_version =
111 (SELECT MAX(activity_version)
112 FROM wf_activity_attributes_vl
113 WHERE activity_item_type = wav.item_type
114 AND activity_name = wav.name
115 AND name = 'AZW_IA_PARENTID')
116 -- Start : added by Swarup
117 AND waav4.activity_item_type = wav.item_type
118 AND waav4.activity_name = wav.name
119 AND waav4.name = 'AZW_IA_FLOWTYPE'
120 AND exists
121 (SELECT meaning
122 FROM fnd_lookups
123 where lookup_code = waav4.text_default
124 AND lookup_type = 'AZ_PROCESS_TYPE')
125 AND waav4.activity_version =
126 (SELECT MAX(activity_version)
127 FROM wf_activity_attributes_vl
128 WHERE activity_item_type = wav.item_type
129 AND activity_name = wav.name
130 AND name = 'AZW_IA_FLOWTYPE')
131 -- End : added by Swarup
132 ORDER BY waav1.text_default;
133
134 CURSOR applications_cursor IS
135 SELECT distinct application_id
136 FROM fnd_application;
137
138 v_application_id NUMBER DEFAULT 0;
139 v_item_type wf_activities_vl.item_type%TYPE DEFAULT NULL;
140 v_process_name wf_activities_vl.name%TYPE DEFAULT NULL;
141 v_application_ids wf_activity_attributes_vl.text_default%TYPE DEFAULT NULL;
142 v_display_order wf_activity_attributes_vl.text_default%TYPE DEFAULT NULL;
143 v_ctxt_type wf_activity_attributes_vl.text_default%TYPE DEFAULT NULL;
144 v_parent_id wf_activity_attributes_vl.text_default%TYPE DEFAULT NULL;
145 v_process_type wf_activity_attributes_vl.text_default%TYPE DEFAULT NULL;
146 v_installed_prod_id fnd_product_installations.application_id%TYPE DEFAULT NULL;
147 id_count INTEGER DEFAULT 0;
148 v_display_number NUMBER(5) DEFAULT 0;
149
150 BEGIN
151
152 DELETE
153 FROM az_product_flows;
154 COMMIT;
155 -- populate defaults of phase 1 for all installed products into
156 -- az_product_phases
157
158 --DBMS_OUTPUT.PUT_LINE('trying to open cursor1');
159
160 BEGIN
161 OPEN applications_cursor;
162 FETCH applications_cursor INTO v_installed_prod_id;
163
164 WHILE applications_cursor%FOUND LOOP
165 --DBMS_OUTPUT.PUT_LINE('application id ' || v_installed_prod_id);
166 IF application_not_found(v_installed_prod_id) THEN
167 INSERT INTO az_product_phases
168 (application_id, phase, last_update_date, last_updated_by,
169 creation_date, created_by)
170 VALUES(v_installed_prod_id, 1, sysdate, 1, sysdate, 1);
171 END IF;
172
173 FETCH applications_cursor INTO v_installed_prod_id;
174 END LOOP;
175 CLOSE applications_cursor;
176 EXCEPTION
177 WHEN app_exception.application_exception THEN
178 RAISE;
179 WHEN OTHERS THEN
180 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
181 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
182 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
183 fnd_message.set_token('AZW_ERROR_PROC','azw_flow.populate_product_flows');
184 fnd_message.set_token('AZW_ERROR_STMT','CURSOR applications_cursor');
185 APP_EXCEPTION.RAISE_EXCEPTION;
186 END;
187
188
189 BEGIN
190 OPEN processes_cursor;
191 FETCH processes_cursor
192 INTO v_item_type, v_process_name, v_application_ids,
193 v_display_order, v_ctxt_type, v_parent_id, v_process_type;
194
195 --DBMS_OUTPUT.PUT_LINE('item_type: ' || v_item_type || ' apps ' ||
196 --v_application_ids);
197
198 WHILE processes_cursor%FOUND LOOP
199 id_count := id_count + 1;
200 v_display_number := v_display_number + 1;
201
202 --DBMS_OUTPUT.PUT_LINE('trying to insert process :' || id_count);
203
204 v_application_id :=
205 AZW_PROC.parse_application_ids(v_application_ids, id_count);
206
207 WHILE (v_application_id > -1) LOOP
208 --DBMS_OUTPUT.PUT_LINE('application id: into az_product_flows ' ||
209 --v_application_id);
210
211 INSERT INTO az_product_flows(application_id, item_type, process_name,
212 display_order, context_type, parent_id, process_type)
213 VALUES(v_application_id, v_item_type, v_process_name,
214 v_display_number , v_ctxt_type, v_parent_id,
215 NVL(v_process_type,'IMP'));
216
217 --DBMS_OUTPUT.PUT_LINE('inserted: '||v_process_name);
218 id_count := id_count + 1;
219 v_application_id :=
220 AZW_PROC.parse_application_ids(v_application_ids, id_count);
221 END LOOP;
222
223 id_count := 0;
224 FETCH processes_cursor
225 INTO v_item_type, v_process_name, v_application_ids,
226 v_display_order, v_ctxt_type, v_parent_id, v_process_type;
227
228 END LOOP;
229
230 CLOSE processes_cursor;
231
232 EXCEPTION
233 WHEN app_exception.application_exception THEN
234 RAISE;
235 WHEN OTHERS THEN
236 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
237 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
238 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
239 fnd_message.set_token('AZW_ERROR_PROC','azw_flow.populate_product_flows');
240 fnd_message.set_token('AZW_ERROR_STMT','CURSOR processes_cursor');
241 APP_EXCEPTION.RAISE_EXCEPTION;
242 END;
243
244 --Do a clean-up of AZ_PROCESSES for processes not in AZ_PRODUCT_FLOWS
245
246 BEGIN
247 DELETE from az_processes ap
248 WHERE not exists
249 (SELECT item_type from AZ_PRODUCT_FLOWS apf
250 where apf.item_type = ap.item_type)
251 OR not exists
252 (SELECT process_name from AZ_PRODUCT_FLOWS apf
253 where apf.process_name = ap.process_name);
254 EXCEPTION
255 WHEN app_exception.application_exception THEN
256 RAISE;
257 WHEN OTHERS THEN
258 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
259 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
260 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
261 fnd_message.set_token('AZW_ERROR_PROC','azw_flow.populate_product_flows');
262 fnd_message.set_token('AZW_ERROR_STMT','delete from az_processes');
263 APP_EXCEPTION.RAISE_EXCEPTION;
264 END;
265
266 COMMIT;
267
268 EXCEPTION
269 WHEN app_exception.application_exception THEN
270 RAISE;
271 WHEN OTHERS THEN
272 fnd_message.set_name('AZ','AZW_PLSQL_EXCEPTION');
273 fnd_message.set_token('AZW_ERROR_CODE',SQLCODE);
274 fnd_message.set_token('AZW_ERROR_MESG',SQLERRM);
275 fnd_message.set_token('AZW_ERROR_PROC','azw_flow.populate_product_flows');
276 fnd_message.set_token('AZW_ERROR_STMT','UNKNOWN');
277 APP_EXCEPTION.RAISE_EXCEPTION;
278 END populate_product_flows;
279
280 END AZW_FLOW;