1 PACKAGE BODY AMW_COMPLIANCE_ENV_ASSOCS_PVT as
2 /* $Header: amwvenvb.pls 120.0 2005/05/31 23:30:18 appldev noship $ */
3
4 -- ===============================================================
5 -- Function name
6 -- COMPLIANCE_ENVS_PRESENT
7 -- Purpose
8 -- return non-translated character (Y/N) to indicate the
9 -- selected(associated) Compliance Environment
10 -- History
11 -- 12.09.2004 tsho: bug 3902348 fixed
12 -- ===============================================================
13 FUNCTION COMPLIANCE_ENVS_PRESENT (
14 p_compliance_env_id IN NUMBER,
15 p_object_type IN VARCHAR2,
16 p_pk1 IN NUMBER,
17 p_pk2 IN NUMBER := NULL,
18 p_pk3 IN NUMBER := NULL,
19 p_pk4 IN NUMBER := NULL,
20 p_pk5 IN NUMBER := NULL
21 ) RETURN VARCHAR2 IS
22
23 n number;
24 BEGIN
25 select count(*)
26 into n
27 from amw_compliance_env_assocs
28 where pk1 = p_pk1
29 and object_type = p_object_type
30 and compliance_env_id = p_compliance_env_id;
31
32 if n > 0 then
33 return 'Y';
34 else
35 return 'N';
36 end if;
37 END COMPLIANCE_ENVS_PRESENT;
38
39
40 -- ===============================================================
41 -- Function name
42 -- COMPLIANCE_ENVS_PRESENT_MEAN
43 -- Purpose
44 -- return translated meaning (Yes/No) to indicate the
45 -- selected(associated) Compliance Environment
46 -- ===============================================================
47 FUNCTION COMPLIANCE_ENVS_PRESENT_MEAN (
48 p_compliance_env_id IN NUMBER,
49 p_object_type IN VARCHAR2,
50 p_pk1 IN NUMBER,
51 p_pk2 IN NUMBER := NULL,
52 p_pk3 IN NUMBER := NULL,
53 p_pk4 IN NUMBER := NULL,
54 p_pk5 IN NUMBER := NULL
55 ) RETURN VARCHAR2 IS
56
57 n number;
58 yes varchar2(80);
59 no varchar2(80);
60 BEGIN
61 select count(*)
62 into n
63 from amw_compliance_env_assocs
64 where pk1 = p_pk1
65 and object_type = p_object_type
66 and compliance_env_id = p_compliance_env_id;
67
68 select meaning
69 into yes
70 from fnd_lookups
71 where lookup_type='YES_NO'
72 and lookup_code='Y';
73
74 select meaning
75 into no
76 from fnd_lookups
77 where lookup_type='YES_NO'
78 and lookup_code='N';
79
80 if n > 0 then
81 return yes;
82 else
83 return no;
84 end if;
85 END COMPLIANCE_ENVS_PRESENT_MEAN;
86
87
88
89 -- ===============================================================
90 -- Function name
91 -- COMPLIANCE_ENVS_DISABLE
92 -- Purpose
93 -- this function is used for OBJECT_TYPE = 'SETUP_RISK_TYPE'.
94 -- return non translated character (Y/N) to indicate the
95 -- specified Compliance Environment should be disabled or not
96 -- for the children of passed-in parent, p_pk1(ie, parent_setup_risk_type_id)
97 -- ===============================================================
98 FUNCTION COMPLIANCE_ENVS_DISABLE (
99 p_compliance_env_id IN NUMBER,
100 p_object_type IN VARCHAR2, -- 'SETUP_RISK_TYPE'
101 p_pk1 IN NUMBER, -- parent setup_risk_type_id
102 p_object_id IN NUMBER := NULL, -- setup_risk_type_id
103 p_pk2 IN NUMBER := NULL,
104 p_pk3 IN NUMBER := NULL,
105 p_pk4 IN NUMBER := NULL,
106 p_pk5 IN NUMBER := NULL
107 ) RETURN VARCHAR2 IS
108
109 l_disabled varchar2(1);
110 n number;
111 m number;
112 l_parent_setup_risk_type_id number;
113 l_setup_risk_type_id number;
114
115 -- find the parent of specified setup risk type
116 cursor get_parent_risk_type_c (l_setup_risk_type_id IN NUMBER) is
117 SELECT parent_setup_risk_type_id
118 from amw_setup_risk_types_b
119 where setup_risk_type_id = l_setup_risk_type_id;
120
121 -- find out is the parent of specified setup risk type is associated this env already
122 cursor is_associated_env_c (l_setup_risk_type_id IN NUMBER) is
123 SELECT count(*)
124 from amw_compliance_env_assocs
125 where pk1 = l_setup_risk_type_id
126 and object_type = 'SETUP_RISK_TYPE'
127 and compliance_env_id = p_compliance_env_id;
128
129 -- 12.09.2004 tsho: find out is the direct children of specified setup risk type is associated this env already
130 cursor is_child_associated_env_c (l_setup_risk_type_id IN NUMBER) is
131 SELECT count(*)
132 from amw_compliance_env_assocs
133 where pk1 in (
134 select b.setup_risk_type_id
135 from amw_setup_risk_types_b b
136 where b.parent_setup_risk_type_id = l_setup_risk_type_id
137 )
138 and object_type = 'SETUP_RISK_TYPE'
139 and compliance_env_id = p_compliance_env_id;
140
141 BEGIN
142 l_disabled := 'Y';
143 l_parent_setup_risk_type_id := p_pk1;
144 l_setup_risk_type_id := p_object_id;
145
146 -- find the parent setup_risk_type
147 if (p_object_type = 'SETUP_RISK_TYPE') then
148 --BEGIN
149 /*
150 OPEN get_parent_risk_type_c (p_pk1);
151 FETCH get_parent_risk_type_c INTO l_parent_setup_risk_type_id;
152 CLOSE get_parent_risk_type_c;
153 */
154
155 -- itself is the root
156 if (l_parent_setup_risk_type_id is NULL) then
157 l_disabled := 'Y';
158 else
159 -- 12.09.2004 tsho: should disable the checkbox if any of its descendants are already associated with this env
160 OPEN is_child_associated_env_c (l_setup_risk_type_id);
161 FETCH is_child_associated_env_c INTO m;
162 CLOSE is_child_associated_env_c;
163
164 if (m >0) then
165 -- its direct children are already associated with this env, disable this env choice for preventing from disassociating
166 l_disabled := 'Y';
167 else
168 -- since its direct children are not yet associated with this env,
169 -- can continue check if its parent node is the root node, then itself can associate with this env
170 if (l_parent_setup_risk_type_id = -1) then
171 l_disabled := 'N';
172 else
173 OPEN is_associated_env_c (l_parent_setup_risk_type_id);
174 FETCH is_associated_env_c INTO n;
175 CLOSE is_associated_env_c;
176
177 if (n >0) then
178 -- its parent is already associated with this env, thus child can associate with it as well
179 l_disabled := 'N';
180 else
181 -- parent is not yet associated with this env, disable this env choice for child association
182 l_disabled := 'Y';
183 end if; -- end of if: n >0
184
185 end if; -- end of if :l_parent_setup_risk_type_id = -1
186 end if; -- end of if: m >0
187
188 end if; -- end of if: l_parent_setup_risk_type_id is NULL
189
190 /*
191 EXCEPTION
192 WHEN no_data_found then
193 -- no parent found for specified setup_risk_type, itself is the root
194 -- root node is not allowed to associate with any env.
195 l_disabled := 'Y';
196 END;
197 */
198
199 end if; -- end of if: p_object_type
200
201 return l_disabled;
202
203 END COMPLIANCE_ENVS_DISABLE;
204
205
206
207
208
209 -- ===============================================================
210 -- Procedure name
211 -- PROCESS_COMPLIANCE_ENV_ASSOCS
212 -- Purpose
213 -- Update the compliance environment associations depending
214 -- on the specified p_select_flag .
215 -- The p_pk1 is co-related with p_object_type, for exampel:
216 -- if p_object_type is SETUP_RISK_TYPE, then
217 -- p_pk1 is SETUP_RISK_TYPE_ID .
218 -- ===============================================================
219 PROCEDURE PROCESS_COMPLIANCE_ENV_ASSOCS (
220 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
221 p_commit IN VARCHAR2 := FND_API.G_FALSE,
222 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
223 p_select_flag IN VARCHAR2,
224 p_compliance_env_id IN NUMBER,
225 p_object_type IN VARCHAR2,
226 p_pk1 IN NUMBER,
227 p_pk2 IN NUMBER := NULL,
228 p_pk3 IN NUMBER := NULL,
229 p_pk4 IN NUMBER := NULL,
230 p_pk5 IN NUMBER := NULL,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2
234 ) IS
235
236 l_creation_date date;
237 l_created_by number;
238 l_last_update_date date;
239 l_last_updated_by number;
240 l_last_update_login number;
241 l_compliance_env_assoc_id number;
242 l_object_version_number number;
243
244 BEGIN
245
246 -- create savepoint if p_commit is true
247 IF p_commit = FND_API.G_TRUE THEN
248 SAVEPOINT compliance_env_assocs_save;
249 END IF;
250
251 -- initialize message list if p_init_msg_list is set to true
252 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
253 fnd_msg_pub.initialize;
254 end if;
255
256 -- initialize return status to success
257 x_return_status := fnd_api.g_ret_sts_success;
258
259 delete from amw_compliance_env_assocs
260 where pk1 = p_pk1
261 and object_type = p_object_type
262 and compliance_env_id = p_compliance_env_id;
263
264 if (p_select_flag = 'Y') then
265 l_creation_date := SYSDATE;
266 l_created_by := FND_GLOBAL.USER_ID;
267 l_last_update_date := SYSDATE;
268 l_last_updated_by := FND_GLOBAL.USER_ID;
269 l_last_update_login := FND_GLOBAL.USER_ID;
270 l_object_version_number := 1;
271
272 select amw_compliance_env_assoc_s.nextval into l_compliance_env_assoc_id from dual;
273
274 insert into amw_compliance_env_assocs (compliance_env_assoc_id,
275 compliance_env_id,
276 object_type,
277 pk1,
278 pk2,
279 pk3,
280 pk4,
281 pk5,
282 creation_date,
283 created_by,
284 last_update_date,
285 last_updated_by,
286 last_update_login,
287 object_version_number)
288 values (l_compliance_env_assoc_id,
289 p_compliance_env_id,
290 p_object_type,
291 p_pk1,
292 p_pk2,
293 p_pk3,
294 p_pk4,
295 p_pk5,
296 l_creation_date,
297 l_created_by,
298 l_last_update_date,
299 l_last_updated_by,
300 l_last_update_login,
301 l_object_version_number);
302
303 end if;
304 EXCEPTION
305 WHEN FND_API.G_EXC_ERROR THEN
306 IF p_commit = FND_API.G_TRUE THEN
307 ROLLBACK TO compliance_env_assocs_save;
308 END IF;
309
310 x_return_status := FND_API.G_RET_STS_ERROR;
311
312 fnd_msg_pub.count_and_get(p_count => x_msg_count,
313 p_data => x_msg_data);
314
315 WHEN OTHERS THEN
316 IF p_commit = FND_API.G_TRUE THEN
317 ROLLBACK TO compliance_env_assocs_save;
318 END IF;
319
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
321
322 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_COMPLIANCE_ENV_ASSOCS_PVT',
326 fnd_msg_pub.count_and_get(p_count => x_msg_count,
323 p_procedure_name => 'PROCESS_COMPLIANCE_ENV_ASSOCS',
324 p_error_text => SUBSTRB(SQLERRM,1,240));
325
327 p_data => x_msg_data);
328
329 END PROCESS_COMPLIANCE_ENV_ASSOCS;
330
331
332 -- ===============================================================
333 -- Function name
334 -- COMPLIANCE_ENVS_IN_USE
335 -- Purpose
336 -- return non translated character (Y/N) to indicate the
337 -- selected(associated) Compliance Environment is used for assoication
338 -- if it's in used, return 'Y', else, return 'N'.
339 -- Notes
340 -- don't need to bother which p_object_type it's associated with.
341 -- as long as it appears in amw_compliance_env_assocs table,
342 -- the return value will be 'Y'.
343 -- ===============================================================
344 FUNCTION COMPLIANCE_ENVS_IN_USE (
345 p_compliance_env_id IN NUMBER
346 ) RETURN VARCHAR2 IS
347
348 n number;
349 BEGIN
350 select count(*)
351 into n
352 from amw_compliance_env_assocs
353 where compliance_env_id = p_compliance_env_id;
354
355 if n > 0 then
356 return 'Y';
357 else
358 return 'N';
359 end if;
360 END COMPLIANCE_ENVS_IN_USE;
361
362
363 -- ----------------------------------------------------------------------
364 END AMW_COMPLIANCE_ENV_ASSOCS_PVT;
365
[Home] [Help]