[Home] [Help]
PACKAGE BODY: APPS.AZW_ARCHIVE
Source
1 PACKAGE BODY AZW_ARCHIVE AS
2 /* $Header: AZWARCHB.pls 115.8 1999/11/09 12:52:51 pkm ship $ */
3
4 /* Private Procedure Declarations ********************************************/
5 PROCEDURE Processes;
6 PROCEDURE Task_Steps;
7
8 FUNCTION is_process_notfound(p_node_id varchar2) RETURN boolean
9 IS
10 cnt number DEFAULT 0;
11 BEGIN
12
13 SELECT COUNT(*) INTO cnt
14 FROM az_archive aap
15 where aap.node_id = p_node_id;
16
17 if(cnt = 0) then
18 return TRUE;
19 ELSE
20 return FALSE;
21 END IF;
22
23 return FALSE;
24 END is_process_notfound;
25
26 FUNCTION is_step_notfound(p_item_key varchar2
27 ,p_activity_type varchar2
28 ,p_step varchar2
29 ,p_begin_date date
30 ) RETURN boolean
31 IS
32 cnt number DEFAULT 0;
33 BEGIN
34 select count(*) into cnt
35 from az_archive_steps aas
36 where aas.item_key = p_item_key
37 and aas.activity_type = p_activity_type
38 and aas.step = p_step
39 and aas.begin_date = p_begin_date;
40
41 if(cnt = 0) then
42 return TRUE;
43 ELSE
44 return FALSE;
45 END IF;
46
47 return FALSE;
48
49 END is_step_notfound;
50
51 /*------------------------------------------------------------------------
52 * PROCESSES
53 *
54 * Private procedure. Called by procedure Run.
55 * Populate implementation process hierarchies in the intermediate table.
56 * It performs the following steps:
57 * 1. Get all distinct processes of the given phase from az_processes_all_v and
58 * az_flow_phases_v into the intermediate table.
59 * 2. Find all distinct parent ids for the processes found in Step 1.
60 * 3. For each parent id in Step 2, get all distinct hierarchy ancestors
61 * in az_groups_v into the intermediate table.
62 *-----------------------------------------------------------------------*/
63 PROCEDURE processes IS
64 CURSOR all_groups_cursor IS
65 SELECT TO_CHAR(agv.display_order, '0000')||'.'||agv.group_id node_id
66 ,agv.display_name
67 ,TO_CHAR(ag.display_order, '0000')||'.'||
68 agv.hierarchy_parent_id parent_node_id
69 ,agv.status
70 ,fl.meaning status_display_name
71 FROM az_groups_v agv
72 ,az_groups ag
73 ,fnd_lookups fl
74 WHERE agv.hierarchy_parent_id = ag.group_id
75 AND agv.status = fl.lookup_code
76 AND fl.lookup_type = 'AZ_PROCESS_STATUS'
77 UNION ALL
78 SELECT TO_CHAR(agv.display_order, '0000')||'.'||agv.group_id node_id
79 ,agv.display_name
80 ,agv.hierarchy_parent_id parent_node_id
81 ,agv.status
82 ,fl.meaning status_display_name
83 FROM az_groups_v agv
84 ,fnd_lookups fl
85 WHERE agv.hierarchy_parent_id is NULL
86 AND agv.status = fl.lookup_code
87 AND fl.lookup_type = 'AZ_PROCESS_STATUS';
88
89 CURSOR all_processes_cursor IS
90 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
91 ||'.'||apv.process_name||'.'||apv.context_id node_id ,
92 apv.display_name,
93 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id parent_node_id,
94 apv.context_type,
95 apv.context_type_name context_type_display_name,
96 apv.context_id,
97 apv.context_name context_display_name,
98 apv.status,
99 fl.meaning status_display_name,
100 apv.comments
101 FROM az_processes_all_v apv,
102 az_groups ag,
103 fnd_lookups fl
104 WHERE apv.parent_id = ag.group_id
105 AND apv.status = fl.lookup_code
106 AND fl.lookup_type = 'AZ_PROCESS_STATUS';
107
108 CURSOR all_tasks_cursor IS
109 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
110 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id
111 ,apv.display_name
112 ,TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
113 apv.process_name||'.'||apv.context_id parent_node_id
114 ,apv.context_type
115 ,apv.context_type_name context_type_display_name
116 ,atv.context_id
117 ,atv.context_name context_display_name
118 ,atv.status
119 ,fl.meaning status_display_name
120 ,atv.item_key
121 ,atv.assigned_user
122 ,wf_directory.GetRoleDisplayName(atv.assigned_user)
123 assigned_user_display_name
124 ,atv.begin_date
125 ,atv.end_date
126 ,atv.duration
127 FROM az_tasks_v atv
128 ,az_processes_all_v apv
129 ,fnd_lookups fl
130 WHERE atv.item_type = apv.item_type
131 AND atv.root_activity = apv.process_name
132 AND atv.context_id = apv.context_id
133 AND atv.status = fl.lookup_code
134 AND fl.lookup_type = 'AZ_PROCESS_STATUS';
135 BEGIN
136 FOR each_group IN all_groups_cursor LOOP
137 if (is_process_notfound(each_group.node_id)) then
138 INSERT INTO az_archive
139 (node_id, node_type
140 ,parent_node_id, node_name
141 ,context_type, context_type_name
142 ,context_id, context_name
143 ,status_code, status_name
144 ,item_key
145 ,assigned_user, assigned_user_name
146 ,start_date, end_date
147 ,duration, comments
148 )
149 VALUES
150 (each_group.node_id, 'G',
151 each_group.parent_node_id, each_group.display_name,
152 NULL, NULL, NULL, NULL,
153 each_group.status, each_group.status_display_name,
154 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
155 COMMIT;
156 end if;
157 END LOOP;
158
159 FOR each_proc IN all_processes_cursor LOOP
160 if (is_process_notfound(each_proc.node_id)) then
161 INSERT INTO az_archive
162 (node_id, node_type
163 ,parent_node_id, node_name
164 ,context_type, context_type_name
165 ,context_id, context_name
166 ,status_code, status_name
167 ,item_key
168 ,assigned_user, assigned_user_name
169 ,start_date, end_date
170 ,duration, comments
171 )
172 VALUES
173 (each_proc.node_id, 'P',
174 each_proc.parent_node_id, each_proc.display_name,
175 each_proc.context_type, each_proc.context_type_display_name,
176 each_proc.context_id, each_proc.context_display_name,
177 each_proc.status, each_proc.status_display_name,
178 NULL, NULL, NULL, NULL, NULL, NULL,
179 each_proc.comments);
180 COMMIT;
181 end if;
182 END LOOP;
183
184 FOR each_task in all_tasks_cursor LOOP
185 if (is_process_notfound(each_task.node_id)) then
186 INSERT into az_archive
187 (node_id, node_type
188 ,parent_node_id, node_name
189 ,context_type, context_type_name
190 ,context_id, context_name
191 ,status_code, status_name
192 ,item_key
193 ,assigned_user, assigned_user_name
194 ,start_date, end_date
195 ,duration, comments
196 )
197 VALUES
198 (each_task.node_id, 'T',
199 each_task.parent_node_id, each_task.display_name,
200 each_task.context_type, each_task.context_type_display_name,
201 each_task.context_id, each_task.context_display_name,
202 each_task.status, each_task.status_display_name,
203 each_task.item_key,
204 each_task.assigned_user, each_task.assigned_user_display_name,
205 each_task.begin_date, each_task.end_date, each_task.duration, NULL);
206 COMMIT;
207 end if;
208 END LOOP;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 --DBMS_OUTPUT.PUT_LINE('error:azw_archive.processes' || SQLERRM);
213 RAISE;
214 END processes;
215
216 /*------------------------------------------------------------------------
217 * Run
218 *
219 * Publice procedure. To be called to generate archive information.
220 *-----------------------------------------------------------------------*/
221 PROCEDURE Run IS
222 rel varchar2(255);
223 rel_info varchar2(255);
224 result boolean;
225 BEGIN
226 result :=fnd_release.get_release(rel,rel_info);
227 --DBMS_OUTPUT.PUT_LINE(rel);
228 fnd_profile.put('AZ_ARCHIVE_RELEASE', rel);
229 COMMIT;
230
231 processes;
232 task_steps;
233 EXCEPTION
234 WHEN OTHERS THEN
235 --DBMS_OUTPUT.PUT_LINE('error: AZW_ARCHIVE.Run ' || SQLERRM);
236 RAISE;
237 END Run;
238
239 /*------------------------------------------------------------------------
240 * Task_Steps
241 *
242 * Private procedure. To be called by Run to copy the task steps information
243 * into the archive table, az_archive_task_steps.
244 *-----------------------------------------------------------------------*/
245 PROCEDURE Task_Steps IS
246 l_item_type az_tasks_v.item_type%TYPE;
247 l_item_key az_tasks_v.item_key%TYPE;
248 CURSOR all_tasks_cursor IS
249 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
250 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
251 atv.item_type item_type,
252 atv.item_key item_key
253 FROM az_tasks_v atv;
254
255 CURSOR all_steps_cursor IS
256 SELECT wiasv.activity_type_code type
257 --,wiasv.activity_type_display_name
258 ,wiasv.activity_name name
259 ,wiasv.activity_display_name display_name
260 ,wiasv.assigned_user user_name
261 ,wiasv.assigned_user_display_name user_display_name
262 ,wna.text_value form_name
263 ,wiasv.activity_status_code status
264 --,wiasv.activity_status_display_name status_display_name
265 ,wiasv.activity_result_code result
266 --,wiasv.activity_result_display_name result_display_name
267 ,wiasv.activity_begin_date begin_date
268 ,wiasv.activity_end_date end_date
269 ,wiasv.execution_time
270 ,wn.user_comment
271 FROM wf_item_activity_statuses_v wiasv
272 ,wf_notification_attributes wna
273 ,wf_notifications wn
274 WHERE wiasv.item_type = l_item_type
275 AND wiasv.item_key = l_item_key
276 AND wiasv.notification_id = wn.notification_id (+)
277 AND wn.notification_id = wna.notification_id (+)
278 AND wna.name (+) = 'AZW_IA_FORM'
279 AND NOT (wiasv.activity_name in ('START', 'END'))
280 UNION
281 SELECT wiasv.activity_type type
282 --,wiasv.activity_type_display_name
283 ,wiasv.activity_name name
284 ,wiasv.activity_display_name display_name
285 ,wiasv.recipient_role user_name
286 ,wiasv.recipient_role_name user_display_name
287 ,wna.text_value form_name
288 ,wiasv.activity_status status
289 --,wiasv.activity_status_display_name status_display_name
290 --,wiasv.result result
291 ,wiash.activity_result_code result
292 --,wiasv.activity_result_display_name result_display_name
293 ,wiasv.begin_date begin_date
294 ,wiasv.end_date end_date
295 ,wiasv.execution_time
296 ,wn.user_comment
297 FROM wf_item_activities_history_v wiasv
298 ,wf_item_activity_statuses_h wiash
299 ,wf_notification_attributes wna
300 ,wf_notifications wn
301 WHERE wiasv.item_type = l_item_type
302 AND wiasv.item_key = l_item_key
303 AND wiasv.item_type = wiash.item_type
304 AND wiasv.item_key = wiash.item_key
305 AND wiasv.begin_date = wiash.begin_date
306 AND wiasv.notification_id = wiash.notification_id
307 AND wiasv.notification_id = wn.notification_id (+)
308 AND wn.notification_id = wna.notification_id (+)
309 AND wna.name (+) = 'AZW_IA_FORM'
310 AND NOT (wiasv.activity_name in ('START', 'END'));
311 BEGIN
312 FOR each_task in all_tasks_cursor LOOP
313 l_item_type := each_task.item_type;
314 l_item_key := each_task.item_key;
315 FOR each_step in all_steps_cursor LOOP
316 if(is_step_notfound(each_task.item_key, each_step.type
317 ,each_step.name, each_step.begin_date)) then
318 INSERT into az_archive_steps
319 (item_key
320 ,activity_type
321 ,step, step_name
322 ,assigned_user, assigned_user_name
323 ,form_name
324 ,status_code
325 ,result_code
326 ,begin_date
327 ,end_date
328 ,duration
329 ,comments
330 ,node_id
331 )
332 VALUES
333 (each_task.item_key
334 ,each_step.type
335 ,each_step.name, each_step.display_name
336 ,each_step.user_name, each_step.user_display_name
337 ,each_step.form_name
338 ,each_step.status
339 ,each_step.result
340 ,each_step.begin_date, each_step.end_date
341 ,each_step.execution_time
342 ,each_step.user_comment
343 ,each_task.node_id);
344 COMMIT;
345 end if;
346 END LOOP;
347 END LOOP;
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 --DBMS_OUTPUT.PUT_LINE('error: AZW_ARCHIVE.Task_Steps' || SQLERRM);
352 RAISE;
353 END Task_Steps;
354
355 END AZW_ARCHIVE;