[Home] [Help]
PACKAGE BODY: APPS.AMW_ASSOC_POST_TXN
Source
1 PACKAGE BODY AMW_ASSOC_POST_TXN AS
2 /* $Header: amwpastb.pls 115.1 2004/02/10 03:48:15 abedajna noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'AMW_ASSOC_POST_TXN';
5 g_file_name CONSTANT VARCHAR2 (12) := 'amwpastb.pls';
6 g_user_id NUMBER := fnd_global.user_id;
7 g_login_id NUMBER := fnd_global.conc_login_id;
8
9 -- for RISK_ORG and CONTROL_ORG, pass at least p_process_organization_id.
10 -- for library contexts, you do not need to pass anything.
11 PROCEDURE assoc_post_txn (
12 p_process_id IN NUMBER := NULL,
13 p_risk_id IN NUMBER := NULL,
14 p_control_id IN NUMBER := NULL,
15 p_process_organization_id IN NUMBER := NULL,
16 p_association_mode IN VARCHAR2 := 'ASSOCIATE',
17 p_object IN VARCHAR2 := 'RISK',
18 p_commit IN VARCHAR2 := fnd_api.g_false,
19 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
20 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
21 p_api_version_number IN NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2 ) IS
25
26 l_api_name CONSTANT VARCHAR2 (30) := 'assoc_post_txn';
27 l_api_version_number CONSTANT NUMBER := 1.0;
28 x_process_organization_id NUMBER := 0;
29 l_process_id NUMBER;
30 l_org_id NUMBER;
31
32 -- abb added
33 l_process_id2 NUMBER;
34 l_org_id2 NUMBER;
35 l_risk_assoc_id number;
36 -- abb added
37
38 cursor cc5 IS
39 SELECT control_association_id ,control_id from amw_control_associations
40 where object_type='RISK_ORG'
41 and pk1 In (
42 select risk_association_id
43 from amw_risk_associations
44 where object_type='PROCESS_ORG' and pk1= p_process_organization_id
45 and risk_id = p_risk_id );
46
47 delete_ctrl_org cc5 %ROWTYPE;
48
49
50 BEGIN
51 SAVEPOINT get_process_hierarchy_pvt;
52 x_return_status := fnd_api.g_ret_sts_success;
53 IF NOT fnd_api.compatible_api_call (l_api_version_number,
54 p_api_version_number,
55 l_api_name,
56 g_pkg_name
57 ) THEN
58 RAISE fnd_api.g_exc_unexpected_error;
59 END IF;
60 -- Initialize message list if p_init_msg_list is set to TRUE.
61 IF fnd_api.to_boolean (p_init_msg_list) THEN
62 fnd_msg_pub.initialize;
63 END IF;
64 -- Debug Message
65 amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
66 -- Initialize API return status to SUCCESS
67 x_return_status := fnd_api.g_ret_sts_success;
68
69 IF fnd_global.user_id IS NULL THEN
70 amw_utility_pvt.error_message(p_message_name => 'USER_PROFILE_MISSING');
71 RAISE fnd_api.g_exc_error;
72 END IF;
73
74 IF ( (p_object = 'RISK_ORG') OR (p_object = 'CONTROL_ORG') ) THEN
75 select organization_id
76 into l_org_id
77 from amw_process_organization
78 where process_organization_id = p_process_organization_id;
79 END IF;
80
81 -- abb added
82 IF (p_association_mode = 'ASSOCIATE') THEN
83 IF (p_object = 'RISK_ORG') THEN
84
85 select risk_association_id into l_risk_assoc_id from amw_risk_associations where risk_id = p_risk_id and pk1 = p_process_organization_id and object_type = 'PROCESS_ORG';
86
87 insert into amw_control_associations
88 (CONTROL_ASSOCIATION_ID,
89 LAST_UPDATE_DATE,
90 LAST_UPDATED_BY,
91 CREATION_DATE,
92 CREATED_BY,
93 LAST_UPDATE_LOGIN,
94 CONTROL_ID,
95 PK1,
96 OBJECT_TYPE,
97 ATTRIBUTE_CATEGORY,
98 ATTRIBUTE1,
99 ATTRIBUTE2,
100 ATTRIBUTE3,
101 ATTRIBUTE4,
102 ATTRIBUTE5,
103 ATTRIBUTE6,
104 ATTRIBUTE7,
105 ATTRIBUTE8,
106 ATTRIBUTE9,
107 ATTRIBUTE10,
108 ATTRIBUTE11,
109 ATTRIBUTE12,
110 ATTRIBUTE13,
111 ATTRIBUTE14,
112 ATTRIBUTE15,
113 OBJECT_VERSION_NUMBER,
114 EFFECTIVE_DATE_FROM,
115 EFFECTIVE_DATE_TO )
116 ( select
117 amw_control_associations_s.nextval,
118 sysdate,
119 FND_GLOBAL.USER_ID,
120 sysdate,
121 FND_GLOBAL.USER_ID,
122 FND_GLOBAL.CONC_LOGIN_ID,
123 control_id,
124 l_risk_assoc_id,
125 'RISK_ORG',
126 ATTRIBUTE_CATEGORY,
127 ATTRIBUTE1,
128 ATTRIBUTE2,
129 ATTRIBUTE3,
130 ATTRIBUTE4,
131 ATTRIBUTE5,
132 ATTRIBUTE6,
133 ATTRIBUTE7,
134 ATTRIBUTE8,
135 ATTRIBUTE9,
136 ATTRIBUTE10,
137 ATTRIBUTE11,
138 ATTRIBUTE12,
139 ATTRIBUTE13,
140 ATTRIBUTE14,
141 ATTRIBUTE15,
142 1,
143 EFFECTIVE_DATE_FROM,
144 EFFECTIVE_DATE_TO
145 from amw_control_associations
146 where object_type = 'RISK'
147 and pk1 = p_risk_id);
148
149 SELECT organization_id into l_org_id2 from amw_process_organization where process_organization_id = p_process_organization_id;
150 SELECT process_id into l_process_id2 from amw_process_organization where process_organization_id = p_process_organization_id;
151
152 insert into amw_ap_associations
153 (AP_ASSOCIATION_ID,
154 LAST_UPDATE_DATE,
155 LAST_UPDATED_BY,
156 CREATION_DATE,
157 CREATED_BY,
158 LAST_UPDATE_LOGIN,
159 PK1,
160 PK2,
161 PK3,
162 OBJECT_TYPE,
163 AUDIT_PROCEDURE_ID,
164 ATTRIBUTE_CATEGORY,
165 ATTRIBUTE1,
166 ATTRIBUTE2,
167 ATTRIBUTE3,
168 ATTRIBUTE4,
169 ATTRIBUTE5,
170 ATTRIBUTE6,
171 ATTRIBUTE7,
172 ATTRIBUTE8,
173 ATTRIBUTE9,
174 ATTRIBUTE10,
175 ATTRIBUTE11,
176 ATTRIBUTE12,
177 ATTRIBUTE13,
178 ATTRIBUTE14,
179 ATTRIBUTE15,
180 OBJECT_VERSION_NUMBER,
181 DESIGN_EFFECTIVENESS,
182 OP_EFFECTIVENESS )
183 ( select
184 amw_ap_associations_s.nextval,
185 sysdate,
186 FND_GLOBAL.USER_ID,
187 sysdate,
188 FND_GLOBAL.USER_ID,
189 FND_GLOBAL.CONC_LOGIN_ID,
190 l_org_id,
191 l_process_id2,
192 pk1,
193 'CTRL_ORG',
194 AUDIT_PROCEDURE_ID,
195 ATTRIBUTE_CATEGORY,
196 ATTRIBUTE1,
197 ATTRIBUTE2,
198 ATTRIBUTE3,
199 ATTRIBUTE4,
200 ATTRIBUTE5,
201 ATTRIBUTE6,
202 ATTRIBUTE7,
203 ATTRIBUTE8,
204 ATTRIBUTE9,
205 ATTRIBUTE10,
206 ATTRIBUTE11,
207 ATTRIBUTE12,
208 ATTRIBUTE13,
209 ATTRIBUTE14,
210 ATTRIBUTE15,
211 1,
212 DESIGN_EFFECTIVENESS,
213 OP_EFFECTIVENESS
214 from amw_ap_associations
215 where object_type = 'CTRL'
216 and pk1 in (select control_id from amw_control_associations
217 where object_type = 'RISK'
218 and pk1 = p_risk_id));
219
220 ELSIF (p_object = 'CONTROL_ORG') THEN
221
222 SELECT organization_id into l_org_id2 from amw_process_organization where process_organization_id = p_process_organization_id;
223 SELECT process_id into l_process_id2 from amw_process_organization where process_organization_id = p_process_organization_id;
224
225 insert into amw_ap_associations
226 (AP_ASSOCIATION_ID,
227 LAST_UPDATE_DATE,
228 LAST_UPDATED_BY,
229 CREATION_DATE,
230 CREATED_BY,
231 LAST_UPDATE_LOGIN,
232 PK1,
233 PK2,
234 PK3,
235 OBJECT_TYPE,
236 AUDIT_PROCEDURE_ID,
237 ATTRIBUTE_CATEGORY,
238 ATTRIBUTE1,
239 ATTRIBUTE2,
240 ATTRIBUTE3,
241 ATTRIBUTE4,
242 ATTRIBUTE5,
243 ATTRIBUTE6,
244 ATTRIBUTE7,
245 ATTRIBUTE8,
246 ATTRIBUTE9,
247 ATTRIBUTE10,
248 ATTRIBUTE11,
249 ATTRIBUTE12,
250 ATTRIBUTE13,
251 ATTRIBUTE14,
252 ATTRIBUTE15,
253 OBJECT_VERSION_NUMBER,
254 DESIGN_EFFECTIVENESS,
255 OP_EFFECTIVENESS )
256 ( select
257 amw_ap_associations_s.nextval,
258 sysdate,
259 FND_GLOBAL.USER_ID,
260 sysdate,
261 FND_GLOBAL.USER_ID,
262 FND_GLOBAL.CONC_LOGIN_ID,
263 l_org_id,
264 l_process_id2,
265 p_control_id,
266 'CTRL_ORG',
267 AUDIT_PROCEDURE_ID,
268 ATTRIBUTE_CATEGORY,
269 ATTRIBUTE1,
270 ATTRIBUTE2,
271 ATTRIBUTE3,
272 ATTRIBUTE4,
273 ATTRIBUTE5,
274 ATTRIBUTE6,
275 ATTRIBUTE7,
276 ATTRIBUTE8,
277 ATTRIBUTE9,
278 ATTRIBUTE10,
279 ATTRIBUTE11,
280 ATTRIBUTE12,
281 ATTRIBUTE13,
282 ATTRIBUTE14,
283 ATTRIBUTE15,
284 1,
285 DESIGN_EFFECTIVENESS,
286 OP_EFFECTIVENESS
287 from amw_ap_associations
288 where object_type = 'CTRL'
289 and pk1 = p_control_id);
290
291 END IF;
292 END IF;
293
294 -- abb added
295
296
297 IF (p_association_mode = 'ASSOCIATE') THEN
298 IF ( (p_object = 'RISK') OR (p_object = 'CONTROL') ) THEN
299 AMW_WF_HIERARCHY_PKG.reset_process_risk_ctrl_count;
300 ELSIF ( (p_object = 'RISK_ORG') OR (p_object = 'CONTROL_ORG') ) THEN
301 AMW_WF_HIERARCHY_PKG.reset_proc_org_risk_ctrl_count(l_org_id);
302 END IF;
303
304 ELSIF (p_association_mode = 'DISASSOCIATE') THEN
305 IF ( (p_object = 'RISK') OR (p_object = 'CONTROL') )THEN
306 AMW_WF_HIERARCHY_PKG.reset_process_risk_ctrl_count;
307 ELSIF (p_object = 'RISK_ORG') THEN
308 OPEN cc5;
309 LOOP
310 FETCH cc5 INTO delete_ctrl_org;
311 EXIT WHEN cc5%NOTFOUND;
312
313 -- added mpande 11/14/2003
314 delete from amw_ap_associations
315 where object_type='CTRL_ORG'
316 and pk1 = ( SELECT organization_id from amw_process_organization
317 where process_organization_id = p_process_organization_id )
318 and pk2 = ( SELECT process_id from amw_process_organization
319 where process_organization_id = p_process_organization_id )
320 AND pk3 = delete_ctrl_org.control_id
321 and
322 not exists ( select control_id from amw_control_associations aca, amw_risk_associations ara
323 where aca.pk1= ara.risk_association_id
324 and ara.object_type = 'PROCESS_ORG'
325 and aca.object_type = 'RISK_ORG'
326 and control_id = delete_ctrl_org.control_id ) ;
330 END LOOP ;
327
328 delete from amw_control_associations
329 where control_association_id = delete_ctrl_org.control_association_id ;
331 CLOSE cc5 ;
332 AMW_WF_HIERARCHY_PKG.reset_proc_org_risk_ctrl_count(l_org_id);
333 ELSIF (p_object = 'CONTROL_ORG') THEN
334 -- added mpande 11/14/2003
335 delete from amw_ap_associations
336 where object_type='CTRL_ORG'
337 and pk1 = ( SELECT organization_id from amw_process_organization
338 where process_organization_id = p_process_organization_id )
339 and pk2 = ( SELECT process_id from amw_process_organization
340 where process_organization_id = p_process_organization_id )
341 AND pk3 = p_control_id
342 and
343 not exists ( select control_id from amw_control_associations aca, amw_risk_associations ara
344 where aca.pk1= ara.risk_association_id
345 and ara.object_type = 'PROCESS_ORG'
346 and aca.object_type = 'RISK_ORG'
347 and control_id = p_control_id );
348 AMW_WF_HIERARCHY_PKG.reset_proc_org_risk_ctrl_count(l_org_id);
349
350 END IF;
351 END IF;
352
353
354 IF x_return_status <> fnd_api.g_ret_sts_success THEN
355 RAISE fnd_api.g_exc_error;
356 END IF;
357 -- Standard check for p_commit
358 IF fnd_api.to_boolean (p_commit) THEN
359 COMMIT WORK;
360 END IF;
361 --Debug Message
362 amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'end');
363 -- Standard call to get message count and if count is 1, get message info.
364 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
365 EXCEPTION
366 WHEN fnd_api.g_exc_error THEN
367 ROLLBACK TO get_process_hierarchy_pvt;
368 x_return_status := fnd_api.g_ret_sts_error;
369 -- Standard call to get message count and if count=1, get the message
370 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
371 p_count => x_msg_count,
372 p_data => x_msg_data
373 );
374 WHEN fnd_api.g_exc_unexpected_error THEN
375 ROLLBACK TO get_process_hierarchy_pvt;
376 x_return_status := fnd_api.g_ret_sts_unexp_error;
377 -- Standard call to get message count and if count=1, get the message
378 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
379 p_count => x_msg_count,
380 p_data => x_msg_data
381 );
382 WHEN OTHERS THEN
383 ROLLBACK TO get_process_hierarchy_pvt;
384 x_return_status := fnd_api.g_ret_sts_unexp_error;
385 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
386 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
387 END IF;
388 -- Standard call to get message count and if count=1, get the message
389 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
390 p_count => x_msg_count,
391 p_data => x_msg_data
392 );
393 END assoc_post_txn;
394 END AMW_ASSOC_POST_TXN;