[Home] [Help]
PACKAGE BODY: APPS.AMW_CONTROL_PVT
Source
1 PACKAGE BODY AMW_CONTROL_PVT AS
2 /* $Header: amwvctlb.pls 120.1 2005/11/08 10:18:12 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_Control_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 g_pkg_name CONSTANT VARCHAR2 (30) := 'AMW_Control_PVT';
16 g_file_name CONSTANT VARCHAR2 (12) := 'amwvctlb.pls';
17 g_user_id NUMBER := fnd_global.user_id;
18 g_login_id NUMBER := fnd_global.conc_login_id;
19 G_OBJ_TYPE CONSTANT VARCHAR2(80) := AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_OBJECT_TYPE','CTRL');
20
21 ---G_FALSE VARCHAr2(1) := FND_API.G_FALSE;
22 ---G_TRUE VARCHAr2(1) := FND_API.G_TRUE;
23
24 -- This procedure has been created by Developer to validate the Business level logic
25 -- before calling the table handlers to do the actual loading of rows
26 PROCEDURE load_control (
27 p_api_version_number IN NUMBER,
28 p_init_msg_list IN VARCHAR2 := g_false,
29 p_commit IN VARCHAR2 := g_false,
30 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
31 x_return_status OUT nocopy VARCHAR2,
32 x_msg_count OUT nocopy NUMBER,
33 x_msg_data OUT nocopy VARCHAR2,
34 ---x_create_control_rev_id out number,
35 ---x_update_control_rev_id out number,
36 ---x_revision_control_rev_id out number,
37 x_control_rev_id out nocopy number,
38 x_control_id out nocopy number,
39 x_mode_affected OUT nocopy VARCHAR2,
40 p_control_rec IN control_rec_type
41 ----- := g_miss_control_rec,
42 -----p_load_control_mode IN VARCHAR2,
43 -----p_party_id IN NUMBER
44 ) IS
45 l_control_name VARCHAR2 (30);
46 l_api_name CONSTANT VARCHAR2 (30) := 'Load_Control';
47 l_api_version_number CONSTANT NUMBER := 1.0;
48 l_return_status_full VARCHAR2 (1);
49 ------l_object_version_number NUMBER := 1;
50 l_org_id NUMBER;
51 l_control_rev_id NUMBER;
52 l_control_rec control_rec_type;
53 l_load_control_mode VARCHAR2 (30) := 'CREATE';
54 update_control_rec control_rec_type;
55 l_create_control_rev_id NUMBER;
56 l_revision_control_rev_id NUMBER;
57 l_mode_affected VARCHAR2 (30);
58 l_object_version_number NUMBER;
59 l_draft_status_counter NUMBER := 0;
60 l_description varchar2(4000);
61 CURSOR get_name IS
62 SELECT DISTINCT NAME
63 FROM amw_controls_tl;
64 l_get_name get_name%ROWTYPE;
65 l_control_id NUMBER;
66 l_controlname VARCHAR2 (240);
67 CURSOR update_enabled IS
68 SELECT control_rev_id, description, control_id, controlname, approval_status,
69 approval_status_name, object_version_number, rev_num,
70 curr_approved_flag, latest_revision_flag, requestor_id,
71 update_switcher, delete_switcher, end_date
72 FROM (SELECT ac.control_id, ac.control_type, ac.CATEGORY, ac.SOURCE,
73 ac.control_location, ac.automation_type,
74 ac.application_id, fav.application_name, ac.job_id,
75 ac.requestor_id, act.NAME as controlname, act.description,
76 pj.NAME as job_name, ac.object_version_number,
77 amw_controls_page_pkg.get_lookup_value('AMW_CONTROL_TYPE',ac.control_type) as control_type_name,
78 amw_controls_page_pkg.get_lookup_value('AMW_CONTROL_LOCATION',ac.control_location) as control_location_name,
79 amw_controls_page_pkg.get_lookup_value('AMW_AUTOMATION_TYPE',ac.automation_type) as automation_type_name,
80 amw_controls_page_pkg.get_control_source(ac.SOURCE,ac.control_type,ac.automation_type,ac.application_id,ac.control_rev_id) as control_source_name,
81 act.physical_evidence, 'N' select_flag,ac.control_rev_id,
82 ac.curr_approved_flag,ac.latest_revision_flag,
83 ac.approval_status,ac.end_date,
84 amw_controls_page_pkg.get_lookup_value('AMW_CONTROL_APPROVAL_STATUS',ac.approval_status) as approval_status_name,
85 DECODE(ac.curr_approved_flag,'Y',DECODE(ac.latest_revision_flag,'N', DECODE(ac.end_date,NULL, 'ChangeInProgress','NoChangeInProgress'),'NoChangeInProgress'),'NoChangeInProgress') as progress_switcher,
86 DECODE(ac.approval_status,'D','UpdateEnabled','UpdateDisabled') as update_switcher,
87 --(SELECT control_rev_id FROM amw_controls_b WHERE control_id =ac.control_id AND latest_revision_flag = 'Y') as latest_control_rev_id,
88 --(SELECT rev_num FROM amw_controls_b WHERE control_id = ac.control_id AND latest_revision_flag = 'Y') as latest_control_rev_num,
89 ac.rev_num,
90 DECODE(ac.approval_status,'D', DECODE (ac.end_date,NULL, 'DeleteEnabled','DeleteDisabled'),'DeleteDisabled') delete_switcher,
91 ac.latest_revision_flag|| ac.curr_approved_flag control_revision_choice,
92 amw_controls_page_pkg.get_lookup_value('AMW_REVISION_VIEW_CHOICE',ac.latest_revision_flag|| ac.curr_approved_flag) revision
93 FROM amw_controls_b ac,
94 amw_controls_tl act,
95 fnd_application_vl fav,
96 per_jobs pj
97 WHERE ac.application_id = fav.application_id(+)
98 AND ac.job_id = pj.job_id(+)
99 AND ac.control_rev_id = act.control_rev_id
100 AND act.LANGUAGE = USERENV ('LANG'))
101 WHERE controlname LIKE p_control_rec.NAME
102 AND approval_status = 'D';
103 ---AND update_switcher = 'UpdateEnabled';
104 updenb update_enabled%ROWTYPE;
105 l_approval_status amw_controls_all_vl.approval_status%TYPE;
106 CURSOR rev_enabled IS
107 SELECT act.NAME, act.description, ac.control_rev_id, ac.control_id, ac.rev_num,
108 ac.object_version_number, ac.approval_status,
109 ac.curr_approved_flag, ac.latest_revision_flag, ac.requestor_id
110 FROM amw_controls_b ac, amw_controls_tl act
111 WHERE ac.latest_revision_flag = 'Y'
112 AND act.control_rev_id = ac.control_rev_id
113 AND act.LANGUAGE = USERENV ('LANG')
114 AND ( ac.approval_status = 'A'
115 OR ac.approval_status = 'R'
116 OR ac.approval_status = 'P'
117 )
118 AND act.NAME LIKE p_control_rec.NAME;
119 revenb rev_enabled%ROWTYPE;
120 l_control_exists NUMBER := 0;
121 l_update_row_found NUMBER := 0;
122 l_revision_row_found NUMBER := 0;
123 l_count NUMBER := 0;
124 l_rev_count number := 0;
125 l_o_v_n number := 0;
126 l_c_r_i number := 0;
127
128 CURSOR get_control_id (l_control_rev_id IN NUMBER) IS
129 SELECT control_id
130 FROM amw_controls_b
131 WHERE control_rev_id = l_control_rev_id;
132
133 out_control_id number;
134
135 BEGIN
136 -- Standard Start of API savepoint
137 SAVEPOINT load_control_pvt;
138 ---fnd_file.put_line (fnd_file.LOG,'Amw_Control_Pvt.Load_Control: Start');
139 -- Standard call to check for call compatibility.
140 IF NOT fnd_api.compatible_api_call (l_api_version_number,
141 p_api_version_number,
142 l_api_name,
143 g_pkg_name
144 ) THEN
145 RAISE fnd_api.g_exc_unexpected_error;
146 END IF;
147 -- Initialize message list if p_init_msg_list is set to TRUE.
148 IF fnd_api.to_boolean (p_init_msg_list) THEN
149 fnd_msg_pub.initialize;
150 END IF;
151 -- Debug Message
152 ---amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
153 amw_utility_pvt.debug_message (l_api_name || ' start');
154 -- Initialize API return status to SUCCESS
155 x_return_status := fnd_api.g_ret_sts_success;
156 l_control_rec := p_control_rec;
157 ---l_control_rec.requestor_id := p_party_id;
158 ----check if this control that user is trying to upload
159 ----exists in the application or not.
160 ----fnd_file.put_line (fnd_file.LOG,'Going to Get_Name cursor');
161 OPEN get_name;
162 LOOP
163 FETCH get_name
164 INTO l_get_name;
165 EXIT WHEN get_name%NOTFOUND;
166 IF (l_get_name.NAME = p_control_rec.NAME) THEN
167 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
168 p_token_name => 'ITEM',
169 p_token_value => 'Control_Name');
170 --RAISE FND_API.G_EXC_ERROR;
171 l_control_exists := 1;
172 END IF;
173 END LOOP;
174 CLOSE get_name;
175 ----fnd_file.put_line (fnd_file.LOG,'Out of Get_Name cursor, l_control_exists: '||l_control_exists);
176
177 IF (l_control_rec.approval_status IS NOT NULL AND
178 (l_control_rec.approval_status <> 'D' and l_control_rec.approval_status <> 'A')) THEN
179 ----amw_utility_pvt.debug_message('Validate_dm_model_rec: A Control can only be created in a Draft (''D'') status');
180 amw_utility_pvt.debug_message('Control_Status not ''D''');
181 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_INVALID_STATUS',
182 p_token_name => 'OBJ_TYPE',
183 p_token_value => G_OBJ_TYPE);
184
185 x_return_status := fnd_api.g_ret_sts_error;
186 RAISE fnd_api.g_exc_error;
187 ELSIF (l_control_rec.approval_status IS NULL) THEN
188 l_control_rec.approval_status := 'D';
189 END IF;
190
191 IF (l_control_exists = 0) THEN
192 --no match found, hence create here
193
194 if(l_control_rec.approval_status = 'D') then
195 l_control_rec.curr_approved_flag := 'N';
196 elsif(l_control_rec.approval_status = 'A') then
197 l_control_rec.curr_approved_flag := 'Y';
198 l_control_rec.approval_date := sysdate;
199 end if;
200 l_control_rec.latest_revision_flag := 'Y';
201 -----l_control_rec.requestor_id := p_party_id;
202 l_control_rec.rev_num := 1;
203 l_control_rec.object_version_number := 1;
204 create_control (p_api_version_number => p_api_version_number,
205 p_init_msg_list => p_init_msg_list,
206 p_commit => p_commit,
207 p_validation_level => p_validation_level,
208 x_return_status => x_return_status,
209 x_msg_count => x_msg_count,
210 x_msg_data => x_msg_data,
211 p_control_rec => l_control_rec,
212 x_control_rev_id => l_control_rev_id
213 );
214 ----fnd_file.put_line (fnd_file.LOG,'x_control_rev_id: '||l_control_rev_id);
215 ---these are the out variables, namely
216 ---control_rev_id and the mode (Create) for this uploaded row
217 OPEN get_control_id(l_CONTROL_REV_ID);
218 FETCH get_control_id INTO out_control_id;
219 CLOSE get_control_id;
220 x_control_id := out_control_id;
221
222 x_control_rev_id := l_control_rev_id;
223 x_mode_affected := 'CREATE_CONTROL';
224 ELSIF (l_control_exists = 1) THEN
225 --check whether this is an updateable mode ....
226 fnd_file.put_line (fnd_file.LOG,'Opening update_enabled');
227 OPEN update_enabled;
228 LOOP
229 FETCH update_enabled
230 INTO updenb;
231 EXIT WHEN update_enabled%NOTFOUND;
232 l_control_rec.control_rev_id := updenb.control_rev_id;
233 l_control_rec.control_id := updenb.control_id;
234 l_control_rec.object_version_number := updenb.object_version_number;
235 ---l_control_rec.description := updenb.description;
236 ---the below rows are needed to populate the essential
237 ---non display columns of the updated row
238 l_control_rec.rev_num := updenb.rev_num;
239 ----l_control_rec.approval_status := updenb.approval_status;
240 l_approval_status := updenb.approval_status;
241 l_control_rec.latest_revision_flag := updenb.latest_revision_flag;
242 l_control_rec.curr_approved_flag := updenb.curr_approved_flag;
243 l_control_rec.requestor_id := updenb.requestor_id;
244 l_update_row_found := l_update_row_found + 1;
245 END LOOP;
246 CLOSE update_enabled;
247 ----fnd_file.put_line (fnd_file.LOG,'Closed update_enabled, l_update_row_found: '||l_update_row_found);
248 IF (l_update_row_found = 0) THEN
249 ---this has to be revision mode
250 OPEN rev_enabled;
251 LOOP
252 FETCH rev_enabled
253 INTO revenb;
254 EXIT WHEN rev_enabled%NOTFOUND;
255 l_control_rec.control_rev_id := revenb.control_rev_id;
256 l_control_id := revenb.control_id;
257 l_control_rec.rev_num := revenb.rev_num;
258 l_description := revenb.description;
259 l_control_rec.object_version_number := revenb.object_version_number;
260 ---l_control_rec.approval_status := revenb.approval_status;
261 l_approval_status := revenb.approval_status;
262 l_control_rec.latest_revision_flag := revenb.latest_revision_flag;
263 l_control_rec.curr_approved_flag := revenb.curr_approved_flag;
264 --- l_control_rec.requestor_id := revenb.requestor_id;
265 l_revision_row_found := l_revision_row_found + 1;
266 END LOOP;
267 CLOSE rev_enabled;
268 ----fnd_file.put_line (fnd_file.LOG,'Closed rev_enabled, l_revision_row_found: '||l_revision_row_found);
269 IF (l_revision_row_found > 1) THEN
270 ---amw_utility_pvt.error_message(p_message_name => 'More than one row for this Control can be revised.');
271 ---amw_utility_pvt.error_message(p_message_name => 'Needs only one revisable row');
272 amw_utility_pvt.error_message(p_message_name => 'AMW_UNEXPECT_ERROR',
273 p_token_name => 'OBJ_TYPE',
274 p_token_value => G_OBJ_TYPE);
275
276 x_return_status := fnd_api.g_ret_sts_error;
277 RAISE fnd_api.g_exc_error;
278 ELSIF (l_revision_row_found = 1) THEN ----elsif for revision status
279 fnd_file.put_line (fnd_file.LOG,'>>>>>>>>>>> l_revision_row_found: '||l_revision_row_found);
280 --do an update for this row
281 l_control_rec.latest_revision_flag := 'N';
282 l_control_rec.object_version_number := l_control_rec.object_version_number;
283 l_count := 0;
284 IF (l_control_rec.approval_status = 'R') THEN
285 amw_utility_pvt.error_message(p_message_name => 'AMW_INVALID_STATUS',
286 p_token_name => 'OBJ_TYPE',
287 p_token_value => G_OBJ_TYPE);
288
289 x_return_status := fnd_api.g_ret_sts_error;
290 RAISE fnd_api.g_exc_error;
291 END IF;
292 IF (l_approval_status = 'P') THEN
293 amw_utility_pvt.error_message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
294 p_token_name => 'OBJ_TYPE',
295 p_token_value => G_OBJ_TYPE);
296
297 x_return_status := fnd_api.g_ret_sts_error;
298 RAISE fnd_api.g_exc_error;
299 END IF;
300 IF ((l_approval_status = 'R' OR l_approval_status = 'A')) THEN
301 fnd_file.put_line (fnd_file.LOG,'>>>>>>>>>>> l_approval_status: '||l_approval_status);
302 ---and l_control_rec.approval_status = 'D') then
303 --this means that there is no prior revision, hence create a revision
304 update_control_rec.latest_revision_flag := 'N';
305 if(l_control_rec.approval_status='A')then
306 fnd_file.put_line (fnd_file.LOG,'>>>>>>>>>>> l_control_rec.approval_status: '||l_control_rec.approval_status);
307 update_control_rec.curr_approved_flag := 'N';
308 ---12.28.2004 npanandi: set the EndDate of the existing
309 ---Approved Control before creating a new Revision
310 update_control_rec.end_date := sysdate;
311 end if;
312 update_control_rec.control_rev_id := l_control_rec.control_rev_id;
313 update_control_rec.NAME := l_control_rec.NAME;
314 update_control_rec.description := l_description;
315 update_control_rec.object_version_number := l_control_rec.object_version_number + 1;
316
317 ---01.13.2005 npanandi:
318 ---added below to check if for this revision, the Classification is null
319 ---for existing old revision, if it is null then add the currently uploaded
320 ---value for Classification
321 if(update_control_rec.classification is null) then
322 update_control_rec.classification := l_control_rec.classification;
323 end if;
324 ----fnd_file.put_line (fnd_file.LOG,'Revising this one, going to Update_Control');
325 update_control
326 (p_api_version_number => l_api_version_number,
327 p_init_msg_list => p_init_msg_list,
328 p_commit => p_commit,
329 p_validation_level => p_validation_level,
330 x_return_status => x_return_status,
331 x_msg_count => x_msg_count,
332 x_msg_data => x_msg_data,
333 p_control_rec => update_control_rec,
334 x_object_version_number => l_object_version_number
335 );
336 IF x_return_status = fnd_api.g_ret_sts_success THEN
337 if(l_control_rec.approval_status = 'A')then
338 l_control_rec.curr_approved_flag := 'Y';
339 l_control_rec.approval_date := sysdate;
340 elsif(l_control_rec.approval_status = 'D')then
341 ---l_control_rec.curr_approved_flag := 'N';
342 ---NPANANDI added this on 03/03/2004, to make it consistent
343 ---with UI revision
344 l_control_rec.curr_approved_flag := 'R';
345 --l_control_rec.curr_approved_flag := 'N';
346 end if;
347 l_control_rec.latest_revision_flag := 'Y';
348 -------l_control_rec.requestor_id := p_party_id;
349 l_control_rec.rev_num := l_control_rec.rev_num + 1;
350 l_control_rec.object_version_number := 1;
351 l_control_rec.control_id := l_control_id;
352 ----fnd_file.put_line (fnd_file.LOG,'Revising this one, going to Create_Control');
353 create_control
354 (p_api_version_number => p_api_version_number,
355 p_init_msg_list => p_init_msg_list,
356 p_commit => p_commit,
357 p_validation_level => p_validation_level,
358 x_return_status => x_return_status,
359 x_msg_count => x_msg_count,
360 x_msg_data => x_msg_data,
361 p_control_rec => l_control_rec,
362 x_control_rev_id => l_control_rev_id
363 );
364 -----fnd_file.put_line (fnd_file.LOG,'x_control_rev_id: '||l_control_rev_id);
365 OPEN get_control_id(l_CONTROL_REV_ID);
366 FETCH get_control_id INTO out_control_id;
367 CLOSE get_control_id;
368 x_control_id := out_control_id;
369 x_control_rev_id := l_control_rev_id;
370 x_mode_affected := 'REVISE_CONTROL';
371 END IF;
372 --create a new row for the revision with the appropriate statuses.
373 END IF;
374 END IF;
375 ELSIF (l_update_row_found > 1) THEN ----elsif for update status
376 amw_utility_pvt.error_message(p_message_name => 'AMW_UNEXPECT_ERROR',
377 p_token_name => 'OBJ_TYPE',
378 p_token_value => G_OBJ_TYPE);
379
380 RAISE fnd_api.g_exc_error;
381 ELSIF (l_update_row_found = 1) THEN ----elsif for update status
382 fnd_file.put_line (fnd_file.LOG,'Inside L_Update_Row_Found = 1, l_control_rec.control_id: '||l_control_rec.control_id);
383 select count(*) into l_rev_count from amw_controls_b
384 where control_id=l_control_rec.control_id
385 and curr_approved_flag='Y'
386 and latest_revision_flag='N';
387
388 -----fnd_file.put_line (fnd_file.LOG,'l_rev_count: '||l_rev_count);
389 /*if(l_rev_count=1)then --this is the previous version from which this
390 ---revised draft was created ....
391 */
392 --- this if is needed to set the curr_approved_flag of the
393 --- previous revision to 'N'
394 if(l_rev_count=1 and l_control_rec.approval_status='A') then
395 fnd_file.put_line (fnd_file.LOG,'Inside controversial IF because ---> l_rev_count: '||l_rev_count||' l_control_rec.approval_status: '||l_control_rec.approval_status);
396 /*select nvl(object_version_number,0) into l_o_v_n from amw_controls_b
397 where control_id=l_control_rec.control_id
398 and curr_approved_flag='Y'
399 and latest_revision_flag='N';
400
401 l_o_v_n := l_o_v_n+1;
402 */
403
404 select control_rev_id into l_c_r_i from amw_controls_b
405 where control_id=l_control_rec.control_id
406 and curr_approved_flag='Y'
407 and latest_revision_flag='N';
408
409 update amw_controls_b
410 set object_version_number=object_version_number+1,
411 curr_approved_flag='N',
412 latest_revision_flag='N',
413 ---12.28.2004 npanandi: setting the EndDate to sysdate for
414 ---previously Approved version of this Control
415 end_date=sysdate,
416 last_updated_by=g_user_id,
417 last_update_date=sysdate,
418 last_update_login=g_login_id
419 where control_rev_id=l_c_r_i;
420 end if;
421
422
423 ---l_control_rec.description := l_description2;
424 ---now you can update this Control
425 l_control_rec.object_version_number := l_control_rec.object_version_number + 1;
426 ---l_control_rec.approval_status := l_approval_status;
427
428 IF (l_control_rec.approval_status = 'A') THEN
429 l_control_rec.curr_approved_flag := 'Y';
430 l_control_rec.approval_date := sysdate;
431 END IF;
432
433 IF (l_control_rec.approval_status = 'R') THEN
434 amw_utility_pvt.error_message(p_message_name => 'AMW_INVALID_STATUS',
435 p_token_name => 'OBJ_TYPE',
436 p_token_value => G_OBJ_TYPE);
437 x_return_status := fnd_api.g_ret_sts_error;
438 RAISE fnd_api.g_exc_error;
439 END IF;
440 fnd_file.put_line (fnd_file.LOG,'2 --> $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
441 update_control (p_api_version_number => l_api_version_number,
442 p_init_msg_list => p_init_msg_list,
443 p_commit => p_commit,
444 p_validation_level => p_validation_level,
445 x_return_status => x_return_status,
446 x_msg_count => x_msg_count,
447 x_msg_data => x_msg_data,
448 p_control_rec => l_control_rec,
449 x_object_version_number => l_object_version_number
450 );
451 fnd_file.put_line (fnd_file.LOG,'x_return_status: '||x_return_status);
452 OPEN get_control_id(l_control_rec.CONTROL_REV_ID);
453 FETCH get_control_id INTO out_control_id;
454 CLOSE get_control_id;
455
456 x_control_id := out_control_id;
457 x_control_rev_id := l_control_rec.control_rev_id;
458 x_mode_affected := 'UPDATE_CONTROL';
459 END IF;
460 END IF;
461 amw_utility_pvt.debug_message (l_api_name || ' end');
462 -- Standard call to get message count and if count is 1, get message info.
463 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
464 EXCEPTION
465 WHEN fnd_api.g_exc_error THEN
466 ROLLBACK TO load_control_pvt;
467 x_return_status := fnd_api.g_ret_sts_error;
468 -- Standard call to get message count and if count=1, get the message
469 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
470 p_count => x_msg_count,
471 p_data => x_msg_data
472 );
473 WHEN fnd_api.g_exc_unexpected_error THEN
474 ROLLBACK TO load_control_pvt;
475 x_return_status := fnd_api.g_ret_sts_unexp_error;
476 -- Standard call to get message count and if count=1, get the message
477 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
478 p_count => x_msg_count,
479 p_data => x_msg_data
480 );
481 WHEN OTHERS THEN
482 ROLLBACK TO load_control_pvt;
483 x_return_status := fnd_api.g_ret_sts_unexp_error;
484 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
485 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
486 END IF;
487 -- Standard call to get message count and if count=1, get the message
488 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
489 p_count => x_msg_count,
490 p_data => x_msg_data
491 );
492 END load_control;
493 -- Hint: Primary key needs to be returned.
494 PROCEDURE create_control (
495 p_api_version_number IN NUMBER,
496 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
497 p_commit IN VARCHAR2 := fnd_api.g_false,
498 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
499 x_return_status OUT nocopy VARCHAR2,
500 x_msg_count OUT nocopy NUMBER,
501 x_msg_data OUT nocopy VARCHAR2,
502 p_control_rec IN control_rec_type,
503 ----- := g_miss_control_rec,
504 x_control_rev_id OUT nocopy NUMBER
505 ) IS
506 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Control';
507 l_api_version_number CONSTANT NUMBER := 1.0;
508 l_return_status_full VARCHAR2 (1);
509 l_object_version_number NUMBER := 1;
510 l_org_id NUMBER;
511 l_control_rev_id NUMBER;
512 l_dummy NUMBER;
513 l_control_rec control_rec_type;
514 l_control_id NUMBER;
515 CURSOR c_id IS
516 SELECT amw_controls_s.NEXTVAL
517 FROM DUAL;
518 CURSOR c_rev_id IS
519 SELECT amw_control_rev_s.NEXTVAL
520 FROM DUAL;
521 CURSOR c_id_exists (l_id IN NUMBER) IS
522 SELECT 1
523 FROM amw_controls_b
524 WHERE control_id = l_id;
525 CURSOR c_rev_id_exists (l_rev_id IN NUMBER) IS
526 SELECT 1
527 FROM amw_controls_b
528 WHERE control_rev_id = l_rev_id;
529 l_row_id amw_controls_all_vl.row_id%TYPE;
530 CURSOR c IS
531 SELECT ROWID
532 FROM amw_controls_b
533 WHERE control_rev_id = x_control_rev_id;
534 BEGIN
535 -- Standard Start of API savepoint
536 SAVEPOINT create_control_pvt;
537 -- Standard call to check for call compatibility.
538 IF NOT fnd_api.compatible_api_call (l_api_version_number,
539 p_api_version_number,
540 l_api_name,
541 g_pkg_name
542 ) THEN
543 RAISE fnd_api.g_exc_unexpected_error;
544 END IF;
545 -- Initialize message list if p_init_msg_list is set to TRUE.
546 IF fnd_api.to_boolean (p_init_msg_list) THEN
547 fnd_msg_pub.initialize;
548 END IF;
549 -- Debug Message
550 ---amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
551 amw_utility_pvt.debug_message (l_api_name || 'start');
552 -- Initialize API return status to SUCCESS
553 x_return_status := fnd_api.g_ret_sts_success;
554 -- Local variable initialization
555 l_control_rec := p_control_rec;
556 ---check and create control_rev_id
557 ---IF l_control_rec.CONTROL_REV_ID IS NULL then ---OR l_control_rec.CONTROL_REV_ID = FND_API.g_miss_num THEN
558 IF l_control_rev_id IS NULL THEN
559 ---OR l_control_rec.CONTROL_REV_ID = FND_API.g_miss_num THEN
560 ---LOOP
561 l_dummy := NULL;
562 OPEN c_rev_id;
563 FETCH c_rev_id
564 INTO l_control_rev_id;
565 CLOSE c_rev_id;
566 /**
567 OPEN c_rev_id_exists(l_CONTROL_REV_ID);
568 FETCH c_rev_id_exists INTO l_dummy;
569 CLOSE c_rev_id_exists;
570 EXIT WHEN l_dummy IS NULL;
571 **/
572 ---END LOOP;
573 END IF;
574 ---check and create control_id
575 IF l_control_rec.control_id IS NULL
576 OR l_control_rec.control_id = fnd_api.g_miss_num THEN
577 ---LOOP
578 l_dummy := NULL;
579 OPEN c_id;
580 FETCH c_id
581 INTO l_control_id;
582 CLOSE c_id;
583 /**
584 OPEN c_id_exists(l_CONTROL_ID);
585 FETCH c_id_exists INTO l_dummy;
586 CLOSE c_id_exists;
587 EXIT WHEN l_dummy IS NULL;
588 END LOOP;
589 **/
590 END IF;
591 l_control_rec.control_rev_id := l_control_rev_id;
592 IF (l_control_rec.control_id IS NULL) THEN
593 l_control_rec.control_id := l_control_id;
594 END IF;
595 l_control_rec.creation_date := SYSDATE;
596 l_control_rec.created_by := g_user_id;
597 l_control_rec.last_update_date := SYSDATE;
598 l_control_rec.last_updated_by := g_user_id;
599 l_control_rec.last_update_login := g_login_id;
600 -- =========================================================================
601 -- Validate Environment
602 -- =========================================================================
603 IF fnd_global.user_id IS NULL THEN
604 amw_utility_pvt.error_message(p_message_name => 'USER_PROFILE_MISSING');
605 RAISE fnd_api.g_exc_error;
606 END IF;
607 IF (p_validation_level >= fnd_api.g_valid_level_full) THEN
608 -- Debug message
609 amw_utility_pvt.debug_message ('Private API: Validate_Control');
610 -- Invoke validation procedures
611 validate_control (p_mode => 'CREATE',
612 p_api_version_number => 1.0,
613 p_init_msg_list => fnd_api.g_false,
614 p_validation_level => p_validation_level,
615 p_control_rec => l_control_rec,
616 x_return_status => x_return_status,
617 x_msg_count => x_msg_count,
618 x_msg_data => x_msg_data
619 );
620 END IF;
621 IF x_return_status <> fnd_api.g_ret_sts_success THEN
622 RAISE fnd_api.g_exc_error;
623 END IF;
624 -- Debug Message
625 amw_utility_pvt.debug_message ('Calling Create handler');
626 IF (l_control_rec.control_id IS NULL) THEN
627 l_control_id := l_control_id;
628 ELSE
629 l_control_id := l_control_rec.control_id;
630 END IF;
631 -- Invoke table handler(AMW_CONTROLS_B_PKG.Insert_Row)
632 amw_controls_pkg.insert_row
633 (x_rowid => l_row_id,
634 x_control_rev_id => l_control_rec.control_rev_id,
635 x_object_version_number => l_control_rec.object_version_number,
636 x_orig_system_reference => l_control_rec.orig_system_reference,
637 x_latest_revision_flag => l_control_rec.latest_revision_flag,
638 x_requestor_id => l_control_rec.requestor_id,
639 x_control_id => l_control_rec.control_id,
640 x_approval_status => l_control_rec.approval_status,
641 x_automation_type => l_control_rec.automation_type,
642 x_application_id => l_control_rec.application_id,
643 x_job_id => l_control_rec.job_id,
644 x_created_by_module => l_control_rec.created_by_module,
645 x_attribute13 => l_control_rec.attribute13,
646 x_attribute14 => l_control_rec.attribute14,
647 x_attribute15 => l_control_rec.attribute15,
648 x_security_group_id => l_control_rec.security_group_id,
649 x_control_location => l_control_rec.control_location,
650 x_rev_num => l_control_rec.rev_num,
651 x_approval_date => l_control_rec.approval_date,
652 x_control_type => l_control_rec.control_type,
653 x_category => l_control_rec.CATEGORY,
654 x_source => l_control_rec.SOURCE,
655 x_attribute_category => l_control_rec.attribute_category,
656 x_attribute1 => l_control_rec.attribute1,
657 x_attribute2 => l_control_rec.attribute2,
658 x_attribute3 => l_control_rec.attribute3,
659 x_attribute4 => l_control_rec.attribute4,
660 x_attribute5 => l_control_rec.attribute5,
661 x_attribute6 => l_control_rec.attribute6,
662 x_attribute7 => l_control_rec.attribute7,
663 x_attribute8 => l_control_rec.attribute8,
664 x_attribute9 => l_control_rec.attribute9,
665 x_attribute10 => l_control_rec.attribute10,
666 x_attribute11 => l_control_rec.attribute11,
667 x_attribute12 => l_control_rec.attribute12,
668 x_end_date => l_control_rec.end_date,
669 x_curr_approved_flag => l_control_rec.curr_approved_flag,
670 x_name => l_control_rec.NAME,
671 x_description => l_control_rec.description,
672 x_physical_evidence => l_control_rec.physical_evidence,
673 x_creation_date => l_control_rec.creation_date,
674 x_created_by => l_control_rec.created_by,
675 x_last_update_date => l_control_rec.last_update_date,
676 x_last_updated_by => l_control_rec.last_updated_by,
677 x_last_update_login => l_control_rec.last_update_login,
678 x_preventive_control => l_control_rec.preventive_control,
679 x_detective_control => l_control_rec.detective_control,
680 x_disclosure_control => l_control_rec.disclosure_control,
681 x_key_mitigating => l_control_rec.key_mitigating,
682 x_verification_source => l_control_rec.verification_source,
683 x_verification_source_name => l_control_rec.verification_source_name,
684 x_verification_instruction => l_control_rec.verification_instruction,
685 --- NPANANDI 12.08,2004: ADDED THE BELOW ATTRIBUTES
686 --- FOR CONTROL ENHANCEMENT
687 X_UOM_CODE => L_CONTROL_REC.UOM_CODE
688 ,X_CONTROL_FREQUENCY => L_CONTROL_REC.CONTROL_FREQUENCY
689 --- NPANANDI 12.10.2004: ADDED BELOW FOR CTRL CLASSIFICATION
690 ,X_CLASSIFICATION => L_CONTROL_REC.CLASSIFICATION
691 );
692 IF x_return_status <> fnd_api.g_ret_sts_success THEN
693 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_EXE_ERROR',
694 p_token_name => 'OBJ_TYPE',
695 p_token_value => G_OBJ_TYPE);
696 RAISE fnd_api.g_exc_error;
697 END IF;
698 --
699 -- End of API body
700 --
701 -- Standard check for p_commit
702 IF fnd_api.to_boolean (p_commit) THEN
703 COMMIT WORK;
704 END IF;
705 x_control_rev_id := l_control_rec.control_rev_id;
706 -- Debug Message
707 amw_utility_pvt.debug_message (l_api_name || 'end');
708 -- Standard call to get message count and if count is 1, get message info.
709 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
710 EXCEPTION
711 WHEN amw_utility_pvt.resource_locked THEN
712 x_return_status := fnd_api.g_ret_sts_error;
713 amw_utility_pvt.error_message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
714 WHEN fnd_api.g_exc_error THEN
715 ROLLBACK TO create_control_pvt;
716 x_return_status := fnd_api.g_ret_sts_error;
717 -- Standard call to get message count and if count=1, get the message
718 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
719 p_count => x_msg_count,
720 p_data => x_msg_data
721 );
722 WHEN fnd_api.g_exc_unexpected_error THEN
723 ROLLBACK TO create_control_pvt;
724 x_return_status := fnd_api.g_ret_sts_unexp_error;
725 -- Standard call to get message count and if count=1, get the message
726 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
727 p_count => x_msg_count,
728 p_data => x_msg_data
729 );
730 WHEN OTHERS THEN
731 ROLLBACK TO create_control_pvt;
732 x_return_status := fnd_api.g_ret_sts_unexp_error;
733 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
734 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
735 END IF;
736 -- Standard call to get message count and if count=1, get the message
737 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
738 p_count => x_msg_count,
739 p_data => x_msg_data
740 );
741 END create_control;
742 PROCEDURE update_control (
743 p_api_version_number IN NUMBER,
744 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
745 p_commit IN VARCHAR2 := fnd_api.g_false,
746 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
747 x_return_status OUT nocopy VARCHAR2,
748 x_msg_count OUT nocopy NUMBER,
749 x_msg_data OUT nocopy VARCHAR2,
750 p_control_rec IN control_rec_type,
751 x_object_version_number OUT nocopy NUMBER
752 ) IS
753 CURSOR c_get_control (control_rev_id NUMBER) IS
754 SELECT *
755 FROM amw_controls_b
756 WHERE control_rev_id = control_rev_id;
757 -- Hint: Developer need to provide Where clause
758 l_api_name CONSTANT VARCHAR2 (30) := 'Update_Control';
759 l_api_version_number CONSTANT NUMBER := 1.0;
760 -- Local Variables
761 l_object_version_number NUMBER;
762 l_control_rev_id NUMBER;
763 l_ref_control_rec c_get_control%ROWTYPE;
764 l_tar_control_rec amw_control_pvt.control_rec_type
765 := p_control_rec;
766 l_control_rec amw_control_pvt.control_rec_type
767 := p_control_rec;
768 l_rowid ROWID;
769 BEGIN
770 -- Standard Start of API savepoint
771 SAVEPOINT update_control_pvt;
772 -- Standard call to check for call compatibility.
773 IF NOT fnd_api.compatible_api_call (l_api_version_number,
774 p_api_version_number,
775 l_api_name,
776 g_pkg_name
777 ) THEN
778 RAISE fnd_api.g_exc_unexpected_error;
779 END IF;
780 -- Initialize message list if p_init_msg_list is set to TRUE.
781 IF fnd_api.to_boolean (p_init_msg_list) THEN
782 fnd_msg_pub.initialize;
783 END IF;
784 -- Debug Message
785 ----amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
786 amw_utility_pvt.debug_message (l_api_name || 'start');
787 -- Initialize API return status to SUCCESS
788 x_return_status := fnd_api.g_ret_sts_success;
789 -- Debug Message
790
791 /*
792 OPEN c_get_Control( l_tar_control_rec.control_id);
793
794 FETCH c_get_Control INTO l_ref_control_rec ;
795
796 If ( c_get_Control%NOTFOUND) THEN
797 AMW_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
798 p_token_name => 'INFO',
799 p_token_value => 'Control') ;
800 RAISE FND_API.G_EXC_ERROR;
801 END IF;
802 -- Debug Message
803 AMW_UTILITY_PVT.debug_message('Private API: - Close Cursor');
804 CLOSE c_get_Control;
805 */
806 --mpande comm
807 /*
808 If (l_tar_control_rec.object_version_number is NULL or
809 l_tar_control_rec.object_version_number = FND_API.G_MISS_NUM ) Then
810 AMW_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
811 p_token_name => 'COLUMN',
812 p_token_value => 'Last_Update_Date') ;
813 raise FND_API.G_EXC_ERROR;
814 End if;
815 -- Check Whether record has been changed by someone else
816 If (l_tar_control_rec.object_version_number <> l_ref_control_rec.object_version_number) Then
817 AMW_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
818 p_token_name => 'INFO',
819 p_token_value => 'Control') ;
820 raise FND_API.G_EXC_ERROR;
821 End if;
822 */
823 l_control_rec.last_update_date := SYSDATE;
824 l_control_rec.last_updated_by := g_user_id;
825 l_control_rec.last_update_login := g_login_id;
826 IF (p_validation_level >= fnd_api.g_valid_level_full) THEN
827 -- Debug message
828 amw_utility_pvt.debug_message ('Calling Validate_Control');
829 fnd_file.put_line (fnd_file.LOG,'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
830 fnd_file.put_line (fnd_file.LOG,'l_control_rec.control_classification: '||l_control_rec.classification);
831 -- Invoke validation procedures
832 validate_control (p_mode => 'UPDATE',
833 p_api_version_number => 1,
834 p_init_msg_list => fnd_api.g_false,
835 p_validation_level => p_validation_level,
836 ----p_control_rec => p_control_rec,
837 p_control_rec => l_control_rec,
838 x_return_status => x_return_status,
839 x_msg_count => x_msg_count,
840 x_msg_data => x_msg_data
841 );
842 END IF;
843 IF x_return_status <> fnd_api.g_ret_sts_success THEN
844 RAISE fnd_api.g_exc_error;
845 END IF;
846 -- Debug Message
847 amw_utility_pvt.debug_message ('Calling Update handler');
848 amw_controls_pkg.update_row
849 (x_control_rev_id => l_control_rec.control_rev_id,
850 x_object_version_number => l_control_rec.object_version_number,
851 x_orig_system_reference => l_control_rec.orig_system_reference,
852 x_latest_revision_flag => l_control_rec.latest_revision_flag,
853 x_requestor_id => l_control_rec.requestor_id,
854 x_control_id => l_control_rec.control_id,
855 x_approval_status => l_control_rec.approval_status,
856 x_automation_type => l_control_rec.automation_type,
857 x_application_id => l_control_rec.application_id,
858 x_job_id => l_control_rec.job_id,
859 x_created_by_module => l_control_rec.created_by_module,
860 x_attribute13 => l_control_rec.attribute13,
861 x_attribute14 => l_control_rec.attribute14,
862 x_attribute15 => l_control_rec.attribute15,
863 x_security_group_id => l_control_rec.security_group_id,
864 x_control_location => l_control_rec.control_location,
865 x_rev_num => l_control_rec.rev_num,
866 x_approval_date => l_control_rec.approval_date,
867 x_control_type => l_control_rec.control_type,
868 x_category => l_control_rec.CATEGORY,
869 x_source => l_control_rec.SOURCE,
870 x_attribute_category => l_control_rec.attribute_category,
871 x_attribute1 => l_control_rec.attribute1,
872 x_attribute2 => l_control_rec.attribute2,
873 x_attribute3 => l_control_rec.attribute3,
874 x_attribute4 => l_control_rec.attribute4,
875 x_attribute5 => l_control_rec.attribute5,
876 x_attribute6 => l_control_rec.attribute6,
877 x_attribute7 => l_control_rec.attribute7,
878 x_attribute8 => l_control_rec.attribute8,
879 x_attribute9 => l_control_rec.attribute9,
880 x_attribute10 => l_control_rec.attribute10,
881 x_attribute11 => l_control_rec.attribute11,
882 x_attribute12 => l_control_rec.attribute12,
883 x_end_date => l_control_rec.end_date,
884 x_curr_approved_flag => l_control_rec.curr_approved_flag,
885 x_name => l_control_rec.NAME,
886 x_description => l_control_rec.description,
887 x_physical_evidence => l_control_rec.physical_evidence,
888 x_last_update_date => SYSDATE,
889 x_last_updated_by => g_user_id,
890 x_last_update_login => g_login_id,
891 x_preventive_control => l_control_rec.preventive_control,
892 x_detective_control => l_control_rec.detective_control,
893 x_disclosure_control => l_control_rec.disclosure_control,
894 x_key_mitigating => l_control_rec.key_mitigating,
895 x_verification_source => l_control_rec.verification_source,
896 x_verification_source_name => l_control_rec.verification_source_name,
897 x_verification_instruction => l_control_rec.verification_instruction,
898 --- NPANANDI 12.08,2004: ADDED THE BELOW ATTRIBUTES
899 --- FOR CONTROL ENHANCEMENT
900 X_UOM_CODE => L_CONTROL_REC.UOM_CODE
901 ,X_CONTROL_FREQUENCY => L_CONTROL_REC.CONTROL_FREQUENCY
902 --- NPANANDI 12.10.2004: ADDED BELOW FOR CTRL CLASSIFICATION
903 ,X_CLASSIFICATION => L_CONTROL_REC.CLASSIFICATION
904 );
905 --
906 -- End of API body.
907 --
908
909 -- Standard check for p_commit
910 IF fnd_api.to_boolean (p_commit) THEN
911 COMMIT WORK;
912 END IF;
913 -- Debug Message
914 --amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'end');
915 amw_utility_pvt.debug_message (l_api_name || 'end');
916 -- Standard call to get message count and if count is 1, get message info.
917 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
918 EXCEPTION
919 WHEN amw_utility_pvt.resource_locked THEN
920 x_return_status := fnd_api.g_ret_sts_error;
921 amw_utility_pvt.error_message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
922 WHEN fnd_api.g_exc_error THEN
923 ROLLBACK TO update_control_pvt;
924 x_return_status := fnd_api.g_ret_sts_error;
925 -- Standard call to get message count and if count=1, get the message
926 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
927 p_count => x_msg_count,
928 p_data => x_msg_data
929 );
930 WHEN fnd_api.g_exc_unexpected_error THEN
931 ROLLBACK TO update_control_pvt;
932 x_return_status := fnd_api.g_ret_sts_unexp_error;
933 -- Standard call to get message count and if count=1, get the message
934 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
935 p_count => x_msg_count,
936 p_data => x_msg_data
937 );
938 WHEN OTHERS THEN
939 ROLLBACK TO update_control_pvt;
940 x_return_status := fnd_api.g_ret_sts_unexp_error;
941 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
942 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
943 END IF;
944 -- Standard call to get message count and if count=1, get the message
945 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
946 p_count => x_msg_count,
947 p_data => x_msg_data
948 );
949 END update_control;
950 PROCEDURE delete_control (
951 p_api_version_number IN NUMBER,
952 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
953 p_commit IN VARCHAR2 := fnd_api.g_false,
954 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
955 x_return_status OUT nocopy VARCHAR2,
956 x_msg_count OUT nocopy NUMBER,
957 x_msg_data OUT nocopy VARCHAR2,
958 p_control_rev_id IN NUMBER,
959 p_object_version_number IN NUMBER
960 ) IS
961 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Control';
962 l_api_version_number CONSTANT NUMBER := 1.0;
963 l_object_version_number NUMBER;
964 BEGIN
965 -- Standard Start of API savepoint
966 SAVEPOINT delete_control_pvt;
967 -- Standard call to check for call compatibility.
968 IF NOT fnd_api.compatible_api_call (l_api_version_number,
969 p_api_version_number,
970 l_api_name,
971 g_pkg_name
972 ) THEN
973 RAISE fnd_api.g_exc_unexpected_error;
974 END IF;
975 -- Initialize message list if p_init_msg_list is set to TRUE.
976 IF fnd_api.to_boolean (p_init_msg_list) THEN
977 fnd_msg_pub.initialize;
978 END IF;
979 -- Debug Message
980 ----amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
981 amw_utility_pvt.debug_message (l_api_name || 'start');
982 -- Initialize API return status to SUCCESS
983 x_return_status := fnd_api.g_ret_sts_success;
984 --
985 -- Api body
986 --
987 -- Debug Message
988 ---amw_utility_pvt.debug_message ('Private API: Calling delete table handler');
989 amw_utility_pvt.debug_message ('Private API: Calling delete table handler');
990 -- Invoke table handler(AMW_CONTROLS_B_PKG.Delete_Row)
991 amw_controls_pkg.delete_row (x_control_rev_id => p_control_rev_id);
992 --
993 -- End of API body
994 --
995
996 -- Standard check for p_commit
997 IF fnd_api.to_boolean (p_commit) THEN
998 COMMIT WORK;
999 END IF;
1000 -- Debug Message
1001 ---amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'end');
1002 amw_utility_pvt.debug_message (l_api_name || ' end');
1003 -- Standard call to get message count and if count is 1, get message info.
1004 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1005 EXCEPTION
1006 WHEN amw_utility_pvt.resource_locked THEN
1007 x_return_status := fnd_api.g_ret_sts_error;
1008 -----amw_utility_pvt.error_message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1009 amw_utility_pvt.error_message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1010 WHEN fnd_api.g_exc_error THEN
1011 ROLLBACK TO delete_control_pvt;
1012 x_return_status := fnd_api.g_ret_sts_error;
1013 -- Standard call to get message count and if count=1, get the message
1014 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1015 p_count => x_msg_count,
1016 p_data => x_msg_data
1017 );
1018 WHEN fnd_api.g_exc_unexpected_error THEN
1019 ROLLBACK TO delete_control_pvt;
1020 x_return_status := fnd_api.g_ret_sts_unexp_error;
1021 -- Standard call to get message count and if count=1, get the message
1022 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1023 p_count => x_msg_count,
1024 p_data => x_msg_data
1025 );
1026 WHEN OTHERS THEN
1027 ROLLBACK TO delete_control_pvt;
1028 x_return_status := fnd_api.g_ret_sts_unexp_error;
1029 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1030 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1031 END IF;
1032 -- Standard call to get message count and if count=1, get the message
1033 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1034 p_count => x_msg_count,
1035 p_data => x_msg_data
1036 );
1037 END delete_control;
1038 -- Hint: Primary key needs to be returned.
1039 PROCEDURE lock_control (
1040 p_api_version_number IN NUMBER,
1041 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1042 x_return_status OUT nocopy VARCHAR2,
1043 x_msg_count OUT nocopy NUMBER,
1044 x_msg_data OUT nocopy VARCHAR2,
1045 p_control_rev_id IN NUMBER,
1046 p_object_version IN NUMBER
1047 ) IS
1048 l_api_name CONSTANT VARCHAR2 (30) := 'Lock_Control';
1049 l_api_version_number CONSTANT NUMBER := 1.0;
1050 l_full_name CONSTANT VARCHAR2 (60)
1051 := g_pkg_name || '.' || l_api_name;
1052 l_control_rev_id NUMBER;
1053 CURSOR c_control IS
1054 SELECT control_rev_id
1055 FROM amw_controls_b
1056 WHERE control_rev_id = p_control_rev_id
1057 AND object_version_number = p_object_version
1058 FOR UPDATE NOWAIT;
1059 BEGIN
1060 -- Debug Message
1061 ----amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
1062 amw_utility_pvt.debug_message (l_api_name || 'start');
1063 -- Initialize message list if p_init_msg_list is set to TRUE.
1064 IF fnd_api.to_boolean (p_init_msg_list) THEN
1065 fnd_msg_pub.initialize;
1066 END IF;
1067 -- Standard call to check for call compatibility.
1068 IF NOT fnd_api.compatible_api_call (l_api_version_number,
1069 p_api_version_number,
1070 l_api_name,
1071 g_pkg_name
1072 ) THEN
1073 RAISE fnd_api.g_exc_unexpected_error;
1074 END IF;
1075 -- Initialize API return status to SUCCESS
1076 x_return_status := fnd_api.g_ret_sts_success;
1077 ------------------------ lock -------------------------
1078 amw_utility_pvt.debug_message (l_full_name || ': start');
1079 OPEN c_control;
1080 FETCH c_control
1081 INTO l_control_rev_id;
1082 IF (c_control%NOTFOUND) THEN
1083 CLOSE c_control;
1084 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
1085 fnd_message.set_name ('AMS', 'AMW_API_RECORD_NOT_FOUND');
1086 fnd_msg_pub.ADD;
1087 END IF;
1088 RAISE fnd_api.g_exc_error;
1089 END IF;
1090 CLOSE c_control;
1091 -------------------- finish --------------------------
1092 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1093 p_count => x_msg_count,
1094 p_data => x_msg_data
1095 );
1096 amw_utility_pvt.debug_message (l_full_name || ': end');
1097 EXCEPTION
1098 WHEN amw_utility_pvt.resource_locked THEN
1099 x_return_status := fnd_api.g_ret_sts_error;
1100 amw_utility_pvt.error_message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1101 WHEN fnd_api.g_exc_error THEN
1102 ROLLBACK TO lock_control_pvt;
1103 x_return_status := fnd_api.g_ret_sts_error;
1104 -- Standard call to get message count and if count=1, get the message
1105 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1106 p_count => x_msg_count,
1107 p_data => x_msg_data
1108 );
1109 WHEN fnd_api.g_exc_unexpected_error THEN
1110 ROLLBACK TO lock_control_pvt;
1111 x_return_status := fnd_api.g_ret_sts_unexp_error;
1112 -- Standard call to get message count and if count=1, get the message
1113 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1114 p_count => x_msg_count,
1115 p_data => x_msg_data
1116 );
1117 WHEN OTHERS THEN
1118 ROLLBACK TO lock_control_pvt;
1119 x_return_status := fnd_api.g_ret_sts_unexp_error;
1120 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1121 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1122 END IF;
1123 -- Standard call to get message count and if count=1, get the message
1124 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1125 p_count => x_msg_count,
1126 p_data => x_msg_data
1127 );
1128 END lock_control;
1129 PROCEDURE check_control_uk_items (
1130 p_control_rec IN control_rec_type,
1131 p_validation_mode IN VARCHAR2 := 'CREATE',
1132 x_return_status OUT nocopy VARCHAR2
1133 ) IS
1134 l_valid_flag VARCHAR2 (1);
1135 BEGIN
1136 x_return_status := fnd_api.g_ret_sts_success;
1137 IF p_validation_mode = 'CREATE' THEN
1138 l_valid_flag :=
1139 amw_utility_pvt.check_uniqueness ('AMW_CONTROLS_B','CONTROL_REV_ID = '''|| p_control_rec.control_rev_id|| '''');
1140 ELSE
1141 l_valid_flag :=
1142 amw_utility_pvt.check_uniqueness ('AMW_CONTROLS_B','CONTROL_REV_ID = '''|| p_control_rec.control_rev_id|| ''' AND CONTROL_REV_ID <> '|| p_control_rec.control_rev_id);
1143 END IF;
1144 IF l_valid_flag = fnd_api.g_false THEN
1145 ---amw_utility_pvt.error_message(p_message_name => 'AMW_CONTROL_REV_ID_DUPLICATE');
1146 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_UNIQUE_ITEM_ERROR',
1147 p_token_name => 'ITEM',
1148 p_token_value => 'Control_Rev_Id');
1149
1150 x_return_status := fnd_api.g_ret_sts_error;
1151 RETURN;
1152 END IF;
1153 END check_control_uk_items;
1154 PROCEDURE check_control_req_items (
1155 p_control_rec IN control_rec_type,
1156 p_validation_mode IN VARCHAR2 := 'CREATE',
1157 x_return_status OUT nocopy VARCHAR2
1158 ) IS
1159 BEGIN
1160 x_return_status := fnd_api.g_ret_sts_success;
1161 IF p_validation_mode = 'CREATE' THEN
1162 IF p_control_rec.control_id = fnd_api.g_miss_num
1163 OR p_control_rec.control_id IS NULL THEN
1164 ---amw_utility_pvt.error_message(p_message_name => 'AMW_control_NO_control_id');
1165 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1166 p_token_name => 'ITEM',
1167 p_token_value => 'Control_Id');
1168
1169 x_return_status := fnd_api.g_ret_sts_error;
1170 RETURN;
1171 END IF;
1172 IF p_control_rec.last_update_date = fnd_api.g_miss_date
1173 OR p_control_rec.last_update_date IS NULL THEN
1174 ----amw_utility_pvt.error_message(p_message_name => 'AMW_ctrl_NO_last_update_date');
1175 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1176 p_token_name => 'ITEM',
1177 p_token_value => 'Last_Update_Date');
1178 x_return_status := fnd_api.g_ret_sts_error;
1179 RETURN;
1180 END IF;
1181
1182 /*
1183 IF p_control_rec.last_updated_by = FND_API.g_miss_num OR p_control_rec.last_updated_by IS NULL THEN
1184 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_control_NO_last_updated_by');
1185 x_return_status := FND_API.g_ret_sts_error;
1186 RETURN;
1187 END IF;
1188
1189
1190 IF p_control_rec.creation_date = FND_API.g_miss_date OR p_control_rec.creation_date IS NULL THEN
1191 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_control_NO_creation_date');
1192 x_return_status := FND_API.g_ret_sts_error;
1193 RETURN;
1194 END IF;
1195
1196
1197 IF p_control_rec.created_by = FND_API.g_miss_num OR p_control_rec.created_by IS NULL THEN
1198 AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_control_NO_created_by');
1199 x_return_status := FND_API.g_ret_sts_error;
1200 RETURN;
1201 END IF;
1202
1203 */
1204 IF p_control_rec.control_rev_id = fnd_api.g_miss_num
1205 OR p_control_rec.control_rev_id IS NULL THEN
1206 ----amw_utility_pvt.error_message(p_message_name => 'AMW_control_NO_control_rev_id');
1207 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1208 p_token_name => 'ITEM',
1209 p_token_value => 'Control_Rev_Id');
1210 x_return_status := fnd_api.g_ret_sts_error;
1211 RETURN;
1212 END IF;
1213 IF p_control_rec.rev_num = fnd_api.g_miss_num
1214 OR p_control_rec.rev_num IS NULL THEN
1215 ---amw_utility_pvt.error_message(p_message_name => 'AMW_control_NO_rev_num');
1216 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1217 p_token_name => 'ITEM',
1218 p_token_value => 'Rev_Num');
1219 x_return_status := fnd_api.g_ret_sts_error;
1220 RETURN;
1221 END IF;
1222 ELSE ----Update mode checks
1223
1224 IF p_control_rec.last_update_date IS NULL THEN
1225 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1226 p_token_name => 'ITEM',
1227 p_token_value => 'Last_Update_Date');
1228 x_return_status := fnd_api.g_ret_sts_error;
1229 RETURN;
1230 END IF;
1231 IF p_control_rec.control_rev_id IS NULL THEN
1232 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
1233 p_token_name => 'ITEM',
1234 p_token_value => 'Control_Rev_Id');
1235 x_return_status := fnd_api.g_ret_sts_error;
1236 RETURN;
1237 END IF;
1238
1239
1240 END IF;
1241 END check_control_req_items;
1242 PROCEDURE check_control_fk_items (
1243 p_control_rec IN control_rec_type,
1244 x_return_status OUT nocopy VARCHAR2
1245 ) IS
1246 BEGIN
1247 x_return_status := fnd_api.g_ret_sts_success;
1248 -- Enter custom code here
1249 END check_control_fk_items;
1250 PROCEDURE check_control_lookup_items (
1251 p_control_rec IN control_rec_type,
1252 x_return_status OUT nocopy VARCHAR2
1253 ) IS
1254 BEGIN
1255 x_return_status := fnd_api.g_ret_sts_success;
1256 -- Enter custom code here
1257 ---amw_utility_pvt.CHECK_LOOKUP_EXISTS();
1258 END check_control_lookup_items;
1259
1260 PROCEDURE check_control_items (
1261 p_control_rec IN control_rec_type,
1262 p_validation_mode IN VARCHAR2,
1263 x_return_status OUT nocopy VARCHAR2
1264 ) IS
1265 BEGIN
1266 -- Check Items Uniqueness API calls
1267 check_control_uk_items (p_control_rec => p_control_rec,
1268 p_validation_mode => p_validation_mode,
1269 x_return_status => x_return_status
1270 );
1271 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1272 RETURN;
1273 END IF;
1274 -- Check Items Required/NOT NULL API calls
1275 check_control_req_items (p_control_rec => p_control_rec,
1276 p_validation_mode => p_validation_mode,
1277 x_return_status => x_return_status
1278 );
1279 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1280 RETURN;
1281 END IF;
1282 -- Check Items Foreign Keys API calls
1283 check_control_fk_items (p_control_rec => p_control_rec,
1284 x_return_status => x_return_status
1285 );
1286 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1287 RETURN;
1288 END IF;
1289 -- Check Items Lookups
1290 check_control_lookup_items (p_control_rec => p_control_rec,
1291 x_return_status => x_return_status
1292 );
1293 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1294 RETURN;
1295 END IF;
1296 END check_control_items;
1297
1298 PROCEDURE complete_control_rec (
1299 p_control_rec IN control_rec_type,
1300 x_complete_rec OUT nocopy control_rec_type
1301 ) IS
1302 l_return_status VARCHAR2 (1);
1303 CURSOR c_complete IS
1304 SELECT *
1305 FROM amw_controls_b
1306 WHERE control_rev_id = p_control_rec.control_rev_id;
1307 l_control_rec c_complete%ROWTYPE;
1308
1309 CURSOR c_desc IS
1310 SELECT description,
1311 verification_source_name,
1312 verification_instruction
1313 FROM amw_controls_tl
1314 WHERE control_rev_id = p_control_rec.control_rev_id;
1315 l_desc c_desc%ROWTYPE;
1316 BEGIN
1317 x_complete_rec := p_control_rec;
1318 OPEN c_complete;
1319 FETCH c_complete
1320 INTO l_control_rec;
1321 CLOSE c_complete;
1322
1323 OPEN c_desc;
1324 FETCH c_desc
1325 INTO l_desc;
1326 CLOSE c_desc;
1327
1328 if(p_control_rec.description is null) then
1329 x_complete_rec.description := l_desc.description;
1330 end if;
1331 if(p_control_rec.verification_source_name is null) then
1332 x_complete_rec.verification_source_name := l_desc.verification_source_name;
1333 end if;
1334 if(p_control_rec.verification_instruction is null) then
1335 x_complete_rec.verification_instruction := l_desc.verification_instruction;
1336 end if;
1337 -- control_id
1338 --IF p_control_rec.control_id = FND_API.g_miss_num THEN
1339 IF p_control_rec.control_id IS NULL THEN
1340 x_complete_rec.control_id := l_control_rec.control_id;
1341 END IF;
1342 -- last_update_date
1343 --IF p_control_rec.last_update_date is null THEN
1344 IF p_control_rec.last_update_date IS NULL THEN
1345 x_complete_rec.last_update_date := l_control_rec.last_update_date;
1346 END IF;
1347 -- last_updated_by
1348 --IF p_control_rec.last_updated_by = FND_API.g_miss_num THEN
1349 IF p_control_rec.last_updated_by IS NULL THEN
1350 x_complete_rec.last_updated_by := l_control_rec.last_updated_by;
1351 END IF;
1352 -- creation_date
1353 --IF p_control_rec.creation_date is null THEN
1354 IF p_control_rec.creation_date IS NULL THEN
1355 x_complete_rec.creation_date := l_control_rec.creation_date;
1356 END IF;
1357 -- created_by
1358 --IF p_control_rec.created_by is null THEN
1359 IF p_control_rec.created_by IS NULL THEN
1360 x_complete_rec.created_by := l_control_rec.created_by;
1361 END IF;
1362 -- last_update_login
1363 --IF p_control_rec.last_update_login is null THEN
1364 IF p_control_rec.last_update_login IS NULL THEN
1365 x_complete_rec.last_update_login := l_control_rec.last_update_login;
1366 END IF;
1367 -- control_type
1368 --IF p_control_rec.control_type is null THEN
1369 IF p_control_rec.control_type IS NULL THEN
1370 x_complete_rec.control_type := l_control_rec.control_type;
1371 END IF;
1372 -- category
1373 ---IF p_control_rec.category is null THEN
1374 IF p_control_rec.CATEGORY IS NULL THEN
1375 x_complete_rec.CATEGORY := l_control_rec.CATEGORY;
1376 END IF;
1377 -- attribute_category
1378 --IF p_control_rec.attribute_category is null THEN
1379 IF p_control_rec.attribute_category IS NULL THEN
1380 x_complete_rec.attribute_category := l_control_rec.attribute_category;
1381 END IF;
1382 -- source
1383 --IF p_control_rec.source is null THEN
1384 IF p_control_rec.SOURCE IS NULL THEN
1385 x_complete_rec.SOURCE := l_control_rec.SOURCE;
1386 END IF;
1387 -- attribute1
1388 --IF p_control_rec.attribute1 is null THEN
1389 IF p_control_rec.attribute1 IS NULL THEN
1390 x_complete_rec.attribute1 := l_control_rec.attribute1;
1391 END IF;
1392 -- attribute2
1393 --IF p_control_rec.attribute2 is null THEN
1394 IF p_control_rec.attribute2 IS NULL THEN
1395 x_complete_rec.attribute2 := l_control_rec.attribute2;
1396 END IF;
1397 -- attribute3
1398 --IF p_control_rec.attribute3 is null THEN
1399 IF p_control_rec.attribute3 IS NULL THEN
1400 x_complete_rec.attribute3 := l_control_rec.attribute3;
1401 END IF;
1402 -- attribute4
1403 --IF p_control_rec.attribute4 is null THEN
1404 IF p_control_rec.attribute4 IS NULL THEN
1405 x_complete_rec.attribute4 := l_control_rec.attribute4;
1406 END IF;
1407 -- attribute5
1408 --IF p_control_rec.attribute5 is null THEN
1409 IF p_control_rec.attribute5 IS NULL THEN
1410 x_complete_rec.attribute5 := l_control_rec.attribute5;
1411 END IF;
1412 -- attribute6
1413 --IF p_control_rec.attribute6 is null THEN
1414 IF p_control_rec.attribute6 IS NULL THEN
1415 x_complete_rec.attribute6 := l_control_rec.attribute6;
1416 END IF;
1417 -- attribute7
1418 --IF p_control_rec.attribute7 is null THEN
1419 IF p_control_rec.attribute7 IS NULL THEN
1420 x_complete_rec.attribute7 := l_control_rec.attribute7;
1421 END IF;
1422 -- attribute8
1423 --IF p_control_rec.attribute8 is null THEN
1424 IF p_control_rec.attribute8 IS NULL THEN
1425 x_complete_rec.attribute8 := l_control_rec.attribute8;
1426 END IF;
1427 -- attribute9
1428 --IF p_control_rec.attribute9 is null THEN
1429 IF p_control_rec.attribute9 IS NULL THEN
1430 x_complete_rec.attribute9 := l_control_rec.attribute9;
1431 END IF;
1432 -- attribute10
1433 --IF p_control_rec.attribute10 is null THEN
1434 IF p_control_rec.attribute10 IS NULL THEN
1435 x_complete_rec.attribute10 := l_control_rec.attribute10;
1436 END IF;
1437 -- attribute11
1438 --IF p_control_rec.attribute11 is null THEN
1439 IF p_control_rec.attribute11 IS NULL THEN
1440 x_complete_rec.attribute11 := l_control_rec.attribute11;
1441 END IF;
1442 -- attribute12
1443 --IF p_control_rec.attribute12 is null THEN
1444 IF p_control_rec.attribute12 IS NULL THEN
1445 x_complete_rec.attribute12 := l_control_rec.attribute12;
1446 END IF;
1447 -- attribute13
1448 --IF p_control_rec.attribute13 is null THEN
1449 IF p_control_rec.attribute13 IS NULL THEN
1450 x_complete_rec.attribute13 := l_control_rec.attribute13;
1451 END IF;
1452 -- attribute14
1453 --IF p_control_rec.attribute14 is null THEN
1454 IF p_control_rec.attribute14 IS NULL THEN
1455 x_complete_rec.attribute14 := l_control_rec.attribute14;
1456 END IF;
1457 -- attribute15
1458 --IF p_control_rec.attribute15 is null THEN
1459 IF p_control_rec.attribute15 IS NULL THEN
1460 x_complete_rec.attribute15 := l_control_rec.attribute15;
1461 END IF;
1462 -- security_group_id
1463 --IF p_control_rec.security_group_id is null THEN
1464 IF p_control_rec.security_group_id IS NULL THEN
1465 x_complete_rec.security_group_id := l_control_rec.security_group_id;
1466 END IF;
1467 -- control_location
1468 --IF p_control_rec.control_location is null THEN
1469 IF p_control_rec.control_location IS NULL THEN
1470 x_complete_rec.control_location := l_control_rec.control_location;
1471 END IF;
1472 -- automation_type
1473 --IF p_control_rec.automation_type is null THEN
1474 IF p_control_rec.automation_type IS NULL THEN
1475 x_complete_rec.automation_type := l_control_rec.automation_type;
1476 END IF;
1477 -- application_id
1478 --IF p_control_rec.application_id is null THEN
1479 IF p_control_rec.application_id IS NULL THEN
1480 x_complete_rec.application_id := l_control_rec.application_id;
1481 END IF;
1482 -- job_id
1483 --IF p_control_rec.job_id is null THEN
1484 IF p_control_rec.job_id IS NULL THEN
1485 x_complete_rec.job_id := l_control_rec.job_id;
1486 END IF;
1487 -- object_version_number
1488 --IF p_control_rec.object_version_number is null THEN
1489 IF p_control_rec.object_version_number IS NULL THEN
1490 x_complete_rec.object_version_number :=
1491 l_control_rec.object_version_number;
1492 END IF;
1493 -- control_rev_id
1494 --IF p_control_rec.control_rev_id is null THEN
1495 IF p_control_rec.control_rev_id IS NULL THEN
1496 x_complete_rec.control_rev_id := l_control_rec.control_rev_id;
1497 END IF;
1498 -- rev_num
1499 --IF p_control_rec.rev_num is null THEN
1500 IF p_control_rec.rev_num IS NULL THEN
1501 x_complete_rec.rev_num := l_control_rec.rev_num;
1502 END IF;
1503 -- end_date
1504 --IF p_control_rec.end_date is null THEN
1505 IF p_control_rec.end_date IS NULL THEN
1506 x_complete_rec.end_date := l_control_rec.end_date;
1507 END IF;
1508 -- approval_status
1509 --IF p_control_rec.approval_status is null THEN
1510 IF p_control_rec.approval_status IS NULL THEN
1511 x_complete_rec.approval_status := l_control_rec.approval_status;
1512 END IF;
1513 -- approval_date
1514 --IF p_control_rec.approval_date is null THEN
1515 IF p_control_rec.approval_date IS NULL THEN
1516 x_complete_rec.approval_date := l_control_rec.approval_date;
1517 END IF;
1518 -- requestor_id
1519 --IF p_control_rec.requestor_id is null THEN
1520 IF p_control_rec.requestor_id IS NULL THEN
1521 x_complete_rec.requestor_id := l_control_rec.requestor_id;
1522 END IF;
1523 -- created_by_module
1524 --IF p_control_rec.created_by_module is null THEN
1525 IF p_control_rec.created_by_module IS NULL THEN
1526 x_complete_rec.created_by_module := l_control_rec.created_by_module;
1527 END IF;
1528 -- curr_approved_flag
1529 --IF p_control_rec.curr_approved_flag is null THEN
1530 IF p_control_rec.curr_approved_flag IS NULL THEN
1531 x_complete_rec.curr_approved_flag := l_control_rec.curr_approved_flag;
1532 END IF;
1533 -- latest_revision_flag
1534 --IF p_control_rec.latest_revision_flag is null THEN
1535 IF p_control_rec.latest_revision_flag IS NULL THEN
1536 x_complete_rec.latest_revision_flag :=
1537 l_control_rec.latest_revision_flag;
1538 END IF;
1539 -- orig_system_reference
1540 --IF p_control_rec.orig_system_reference is null THEN
1541 IF p_control_rec.orig_system_reference IS NULL THEN
1542 x_complete_rec.orig_system_reference :=
1543 l_control_rec.orig_system_reference;
1544 END IF;
1545 -- preventive_control
1546 --IF p_control_rec.preventive_control is null THEN
1547 IF p_control_rec.preventive_control IS NULL THEN
1548 x_complete_rec.preventive_control :=
1549 l_control_rec.preventive_control;
1550 END IF;
1551 -- detective_control
1552 --IF p_control_rec.detective_control is null THEN
1553 IF p_control_rec.detective_control IS NULL THEN
1554 x_complete_rec.detective_control :=
1555 l_control_rec.detective_control;
1556 END IF;
1557 -- disclosure_control
1558 --IF p_control_rec.preventive_control is null THEN
1559 IF p_control_rec.disclosure_control IS NULL THEN
1560 x_complete_rec.disclosure_control :=
1561 l_control_rec.disclosure_control;
1562 END IF;
1563 -- key_mitigating
1564 --IF p_control_rec.key_mitigating is null THEN
1565 IF p_control_rec.key_mitigating IS NULL THEN
1566 x_complete_rec.key_mitigating :=
1567 l_control_rec.key_mitigating;
1568 END IF;
1569 -- verification_source
1570 --IF p_control_rec.verification_source is null THEN
1571 IF p_control_rec.verification_source IS NULL THEN
1572 x_complete_rec.verification_source :=
1573 l_control_rec.verification_source;
1574 END IF;
1575 -- UOM_CODE
1576 --IF p_control_rec.UOM_CODE is null THEN
1577 IF p_control_rec.UOM_CODE IS NULL THEN
1578 x_complete_rec.UOM_CODE := l_control_rec.UOM_CODE;
1579 END IF;
1580 -- CONTROL_FREQUENCY
1581 --IF p_control_rec.CONTROL_FREQUENCY is null THEN
1582 IF p_control_rec.CONTROL_FREQUENCY IS NULL THEN
1583 x_complete_rec.CONTROL_FREQUENCY := l_control_rec.CONTROL_FREQUENCY;
1584 END IF;
1585
1586 -- NPANANDI 12.10.2004: ADDED BELOW FOR CONTROL CLASSIFICATION
1587 -- CONTROL_FREQUENCY
1588 --IF p_control_rec.CONTROL_FREQUENCY is null THEN
1589 IF p_control_rec.CLASSIFICATION IS NULL THEN
1590 x_complete_rec.CLASSIFICATION := l_control_rec.CLASSIFICATION;
1591 END IF;
1592 -- Note: Developers need to modify the procedure
1593 -- to handle any business specific requirements.
1594 END complete_control_rec;
1595
1596 PROCEDURE validate_control (
1597 p_mode IN VARCHAR2,
1598 ---p_validation_mode in varchar2,
1599 p_api_version_number IN NUMBER,
1600 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1601 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1602 p_control_rec IN OUT nocopy control_rec_type,
1603 x_return_status OUT nocopy VARCHAR2,
1604 x_msg_count OUT nocopy NUMBER,
1605 x_msg_data OUT nocopy VARCHAR2
1606 ) IS
1607 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Control';
1608 l_api_version_number CONSTANT NUMBER := 1.0;
1609 l_object_version_number NUMBER;
1610 l_control_rec amw_control_pvt.control_rec_type;
1611 BEGIN
1612 -- Standard Start of API savepoint
1613 SAVEPOINT validate_control;
1614 -- Standard call to check for call compatibility.
1615 IF NOT fnd_api.compatible_api_call (l_api_version_number,
1616 p_api_version_number,
1617 l_api_name,
1618 g_pkg_name
1619 ) THEN
1620 RAISE fnd_api.g_exc_unexpected_error;
1621 END IF;
1622 -- Initialize message list if p_init_msg_list is set to TRUE.
1623 IF fnd_api.to_boolean (p_init_msg_list) THEN
1624 fnd_msg_pub.initialize;
1625 END IF;
1626 IF p_validation_level >= jtf_plsql_api.g_valid_level_item THEN
1627 check_control_items (p_control_rec => p_control_rec,
1628 p_validation_mode => p_mode,
1629 x_return_status => x_return_status
1630 );
1631 IF x_return_status = fnd_api.g_ret_sts_error THEN
1632 RAISE fnd_api.g_exc_error;
1633 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1634 RAISE fnd_api.g_exc_unexpected_error;
1635 END IF;
1636 END IF;
1637 complete_control_rec (p_control_rec => p_control_rec,
1638 x_complete_rec => l_control_rec
1639 );
1640 p_control_rec := l_control_rec;
1641 IF p_validation_level >= jtf_plsql_api.g_valid_level_item THEN
1642 validate_control_rec (p_mode => p_mode,
1643 p_api_version_number => 1.0,
1644 p_init_msg_list => fnd_api.g_false,
1645 x_return_status => x_return_status,
1646 x_msg_count => x_msg_count,
1647 x_msg_data => x_msg_data,
1648 p_control_rec => l_control_rec
1649 );
1650 IF x_return_status = fnd_api.g_ret_sts_error THEN
1651 RAISE fnd_api.g_exc_error;
1652 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1653 RAISE fnd_api.g_exc_unexpected_error;
1654 END IF;
1655 END IF;
1656 -- Debug Message
1657 ---amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'start');
1658 amw_utility_pvt.debug_message (l_api_name || 'start');
1659 -- Initialize API return status to SUCCESS
1660 x_return_status := fnd_api.g_ret_sts_success;
1661 -- Debug Message
1662 ---amw_utility_pvt.debug_message ('Private API: ' || l_api_name || 'end');
1663 amw_utility_pvt.debug_message (l_api_name || 'end');
1664 -- Standard call to get message count and if count is 1, get message info.
1665 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1666 EXCEPTION
1667 WHEN amw_utility_pvt.resource_locked THEN
1668 x_return_status := fnd_api.g_ret_sts_error;
1669 amw_utility_pvt.error_message(p_message_name => 'AMW_API_RESOURCE_LOCKED');
1670 WHEN fnd_api.g_exc_error THEN
1671 ROLLBACK TO validate_control;
1672 x_return_status := fnd_api.g_ret_sts_error;
1673 -- Standard call to get message count and if count=1, get the message
1674 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1675 p_count => x_msg_count,
1676 p_data => x_msg_data
1677 );
1678 WHEN fnd_api.g_exc_unexpected_error THEN
1679 ROLLBACK TO validate_control;
1680 x_return_status := fnd_api.g_ret_sts_unexp_error;
1681 -- Standard call to get message count and if count=1, get the message
1682 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1683 p_count => x_msg_count,
1684 p_data => x_msg_data
1685 );
1686 WHEN OTHERS THEN
1687 ROLLBACK TO validate_control;
1688 x_return_status := fnd_api.g_ret_sts_unexp_error;
1689 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1690 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1691 END IF;
1692 -- Standard call to get message count and if count=1, get the message
1693 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
1694 p_count => x_msg_count,
1695 p_data => x_msg_data
1696 );
1697 END validate_control;
1698 PROCEDURE validate_control_rec (
1699 p_mode IN VARCHAR2 := 'CREATE',
1700 p_api_version_number IN NUMBER,
1701 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1702 x_return_status OUT nocopy VARCHAR2,
1703 x_msg_count OUT nocopy NUMBER,
1704 x_msg_data OUT nocopy VARCHAR2,
1705 p_control_rec IN control_rec_type
1706 ) IS
1707 BEGIN
1708 -- Initialize message list if p_init_msg_list is set to TRUE.
1709 IF fnd_api.to_boolean (p_init_msg_list) THEN
1710 fnd_msg_pub.initialize;
1711 END IF;
1712 -- Initialize API return status to SUCCESS
1713 x_return_status := fnd_api.g_ret_sts_success;
1714 -- Hint: Validate data
1715 -- If data not valid
1716 -- THEN
1717 -- x_return_status := FND_API.G_RET_STS_ERROR;
1718 -- Debug Message
1719 amw_utility_pvt.debug_message ('Validate_conrol_rec starts');
1720 IF (p_control_rec.NAME IS NULL OR p_control_rec.NAME = '') THEN
1721 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_WEBADI_REQUIRED_ERROR',
1722 p_token_name => 'ITEM',
1723 p_token_value => 'Control Name');
1724 x_return_status := fnd_api.g_ret_sts_error;
1725 END IF;
1726 IF ( p_control_rec.description IS NULL
1727 OR TRIM (p_control_rec.description) = ''
1728 ) THEN
1729 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_WEBADI_REQUIRED_ERROR',
1730 p_token_name => 'ITEM',
1731 p_token_value => 'Control Description');
1732 x_return_status := fnd_api.g_ret_sts_error;
1733 END IF;
1734 IF ( p_control_rec.control_type IS NULL
1735 OR TRIM (p_control_rec.control_type) = ''
1736 ) THEN
1737 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_WEBADI_REQUIRED_ERROR',
1738 p_token_name => 'ITEM',
1739 p_token_value => 'Control Type');
1740 x_return_status := fnd_api.g_ret_sts_error;
1741 ELSE
1742 IF fnd_api.to_boolean(amw_utility_pvt.check_lookup_exists ('AMW_LOOKUPS','AMW_CONTROL_TYPE',p_control_rec.control_type)) THEN
1743 amw_utility_pvt.debug_message('AMW_Valid_Control_Type');
1744 ELSE
1745 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_WEBADI_VALID_ERROR',
1746 p_token_name => 'ITEM',
1747 p_token_value => 'Control Type');
1748 x_return_status := fnd_api.g_ret_sts_error;
1749 END IF;
1750 END IF;
1751 IF ( p_control_rec.control_location IS NOT NULL
1752 OR p_control_rec.control_location <> ''
1753 ) THEN
1754 IF (amw_utility_pvt.check_lookup_exists ('AMW_LOOKUPS','AMW_CONTROL_LOCATION',p_control_rec.control_location) = fnd_api.g_false
1755 ) THEN
1756 amw_utility_pvt.debug_message('AMW_Control_Location_Invalid');
1757 x_return_status := fnd_api.g_ret_sts_error;
1758 END IF;
1759 END IF;
1760
1761 --12.22.2004 npanandi: added check for mandatory Classification attribute
1762 --Bug 4670522 : Removed the 'Required' check
1763 /* IF ( p_control_rec.classification IS NULL
1764 OR TRIM (p_control_rec.classification) = ''
1765 ) THEN
1766 AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_WEBADI_REQUIRED_ERROR',
1767 p_token_name => 'ITEM',
1768 p_token_value => 'Control Classification');
1769 x_return_status := fnd_api.g_ret_sts_error;
1770 END IF;
1771 */
1772 -- Debug Message
1773 amw_utility_pvt.debug_message ('Validate_dm_model_rec ends');
1774 -- Standard call to get message count and if count is 1, get message info.
1775 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1776 END validate_control_rec;
1777 END amw_control_pvt;