[Home] [Help]
PACKAGE BODY: APPS.AMW_PARAMETERS_PVT_PKG
Source
1 PACKAGE BODY AMW_PARAMETERS_PVT_PKG AS
2 /* $Header: amwparpb.pls 120.1 2005/10/25 23:25:14 appldev noship $ */
3 -- HISTORY
4 -- 11/19/2004 kosriniv Creates
5 ---------------------------------------------------------------------
6
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_PARAMETERS_PVT_PKG';
8 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwparpb.pls';
9 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
10 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
11
12 PROCEDURE insert_parameter(
13 p_parameter_name IN VARCHAR2,
14 p_parameter_value IN VARCHAR2,
15 p_pk1 IN VARCHAR2,
16 p_pk2 IN VARCHAR2,
17 p_pk3 IN VARCHAR2,
18 p_pk4 IN VARCHAR2,
19 p_pk5 IN VARCHAR2)
20 is
21 l_dummy number;
22 begin
23
24 select 1 into l_dummy
25 from amw_parameters
26 where parameter_name = p_parameter_name
27 and pk1 = p_pk1
28 and NVL(pk2,-99) = NVL(p_pk2,-99)
29 and NVL(pk3,-99) = NVL(p_pk3,-99)
30 and NVL(pk4,-99) = NVL(p_pk4,-99)
31 and NVL(pk5,-99) = NVL(p_pk5,-99) ;
32
33 EXCEPTION
34 when no_data_found then
35 INSERT INTO
36 AMW_PARAMETERS(
37 OBJECT_VERSION_NUMBER,
38 PARAMETER_NAME,
39 PARAMETER_VALUE,
40 PK1,PK2,PK3,PK4,PK5,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
41 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
42 VALUES(1,p_parameter_name, p_parameter_value,p_pk1,p_pk2,p_pk3,p_pk4,p_pk5,G_USER_ID, SYSDATE, G_USER_ID,SYSDATE,G_LOGIN_ID);
43
44 end insert_parameter;
45
46 PROCEDURE update_parameter(
47 p_parameter_name in varchar2,
48 p_parameter_value in varchar2,
49 p_pk1 in varchar2,
50 p_pk2 in varchar2,
51 p_pk3 in varchar2,
52 p_pk4 in varchar2,
53 p_pk5 in varchar2)
54 is
55
56 l_param_value varchar2(80);
57 l_object_version_number number;
58
59 begin
60
61 -- Retrieve the parameter..row...
62
63 select parameter_value
64 into l_param_value
65 from amw_parameters
66 where parameter_name = p_parameter_name
67 and pk1 = p_pk1
68 and NVL(pk2,-99) = NVL(p_pk2,-99)
69 and NVL(pk3,-99) = NVL(p_pk3,-99)
70 and NVL(pk4,-99) = NVL(p_pk4,-99)
71 and NVL(pk5,-99) = NVL(p_pk5,-99) ;
72 -- Make sure that the parameter value changed..
73 IF l_param_value <> p_parameter_value THEN
74 update amw_parameters
75 set parameter_value = p_parameter_value,
76 last_updated_by = g_user_id,
77 last_update_date = sysdate,
78 last_update_login = G_LOGIN_ID,
79 object_version_number = object_version_number+1
80 where
81 parameter_name = p_parameter_name
82 and pk1 = p_pk1
83 and NVL(pk2,-99) = NVL(p_pk2,-99)
84 and NVL(pk3,-99) = NVL(p_pk3,-99)
85 and NVL(pk4,-99) = NVL(p_pk4,-99)
86 and NVL(pk5,-99) = NVL(p_pk5,-99) ;
87 END IF;
88 END update_parameter;
89
90 PROCEDURE initialize_org_parameters(
91 p_process_approval_option IN VARCHAR2,
92 p_process_auto_approve IN VARCHAR2,
93 p_pk1 IN VARCHAR2,
94 p_pk2 IN VARCHAR2 := NULL,
95 p_pk3 IN VARCHAR2 := NULL,
96 p_pk4 IN VARCHAR2 := NULL,
97 p_pk5 IN VARCHAR2 := NULL,
98 p_commit IN VARCHAR2 := FND_API.G_FALSE,
99 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY VARCHAR2,
103 x_msg_data OUT NOCOPY VARCHAR2
104 ) IS
105 L_API_NAME CONSTANT VARCHAR2(30) := 'initialize_org_parameters';
106 BEGIN
107
108 -- Standard Initialization..
109 G_USER_ID := FND_GLOBAL.USER_ID;
110 G_LOGIN_ID := FND_GLOBAL.CONC_LOGIN_ID;
111 --standard message list initialization code..
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113 IF FND_API.to_Boolean( p_init_msg_list ) THEN
114 FND_MSG_PUB.initialize;
115 END IF;
116 IF FND_GLOBAL.User_Id IS NULL THEN
117 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
118 RAISE FND_API.G_EXC_ERROR;
119 END IF;
120
121 -- INSERT PROCESS_APPROVAL_OPTION..
122
123 insert_parameter(
124 p_parameter_name => 'PROCESS_APPROVAL_OPTION',
125 p_parameter_value => p_process_approval_option,
126 p_pk1 => p_pk1,
127 p_pk2 => p_pk2,
128 p_pk3 => p_pk3,
129 p_pk4 => p_pk4,
130 p_pk5 => p_pk5);
131
132 -- INSERT PROCESS_AUTO_APPROVE ..
133
134 insert_parameter(
135 p_parameter_name => 'PROCESS_AUTO_APPROVE',
136 p_parameter_value => p_process_auto_approve,
137 p_pk1 => p_pk1,
138 p_pk2 => p_pk2,
139 p_pk3 => p_pk3,
140 p_pk4 => p_pk4,
141 p_pk5 => p_pk5);
142
143
144 -- INSERT A NEW ROW IN TO THE AMW_PROCESS_ORGANIZATION..
145 INSERT INTO AMW_PROCESS_ORGANIZATION(
146 PROCESS_ORGANIZATION_ID,
147 PROCESS_ID,
148 STANDARD_PROCESS_FLAG,
149 APPROVAL_STATUS,
150 ORGANIZATION_ID,
151 OBJECT_VERSION_NUMBER,
152 PROCESS_CODE,
153 REVISION_NUMBER,
154 PROCESS_ORG_REV_ID,
155 START_DATE,
156 APPROVAL_DATE,
157 RL_PROCESS_REV_ID,
158 RISK_CATEGORY,
159 CREATED_BY,
160 CREATION_DATE,
161 LAST_UPDATED_BY,
162 LAST_UPDATE_DATE,
163 LAST_UPDATE_LOGIN)
164 values( AMW_PROCESS_ORGANIZATION_S.nextval,
165 -2,
166 'Y',
167 'A',
168 p_pk1,
169 1,
170 '-2',
171 1,
172 AMW_PROCESS_ORG_REV_S.nextval,
173 sysdate,
174 sysdate,
175 -2,
176 'R',
177 g_user_id,
178 sysdate,
179 g_user_id,
180 sysdate,
181 G_LOGIN_ID
182 );
183
184 exception
185 WHEN FND_API.G_EXC_ERROR THEN
186 ROLLBACK;
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
189
190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
191 ROLLBACK;
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
193 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
194
195 WHEN OTHERS THEN
196 ROLLBACK;
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
199 THEN
200 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
201 END IF;
202 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
203 END initialize_org_parameters;
204
205 PROCEDURE update_org_parameters(
206 p_process_approval_option IN VARCHAR2,
207 p_process_auto_approve IN VARCHAR2,
208 p_pk1 IN VARCHAR2,
209 p_pk2 IN VARCHAR2 := NULL,
210 p_pk3 IN VARCHAR2 := NULL,
211 p_pk4 IN VARCHAR2 := NULL,
212 p_pk5 IN VARCHAR2 := NULL,
213 p_commit IN VARCHAR2 := FND_API.G_FALSE,
214 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
215 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
216 x_return_status OUT NOCOPY VARCHAR2,
217 x_msg_count OUT NOCOPY VARCHAR2,
218 x_msg_data OUT NOCOPY VARCHAR2
219 ) IS
220
221 L_API_NAME CONSTANT VARCHAR2(30) := 'update_org_parameters';
222 BEGIN
223 -- Standard Initialization..
224 G_USER_ID := FND_GLOBAL.USER_ID;
225 G_LOGIN_ID := FND_GLOBAL.CONC_LOGIN_ID;
226 --standard message list initialization code..
227 x_return_status := FND_API.G_RET_STS_SUCCESS;
228 IF FND_API.to_Boolean( p_init_msg_list ) THEN
229 FND_MSG_PUB.initialize;
230 END IF;
231 IF FND_GLOBAL.User_Id IS NULL THEN
232 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
233 RAISE FND_API.G_EXC_ERROR;
234 END IF;
235
236 -- UPDATE PROCESS_APPROVAL_OPTION..
237
238 update_parameter(
239 p_parameter_name => 'PROCESS_APPROVAL_OPTION',
240 p_parameter_value => p_process_approval_option,
241 p_pk1 => p_pk1,
242 p_pk2 => p_pk2,
243 p_pk3 => p_pk3,
244 p_pk4 => p_pk4,
245 p_pk5 => p_pk5);
246
247 -- UPDATE PROCESS_AUTO_APPROVE ..
248
249 update_parameter(
250 p_parameter_name => 'PROCESS_AUTO_APPROVE',
251 p_parameter_value => p_process_auto_approve,
252 p_pk1 => p_pk1,
253 p_pk2 => p_pk2,
254 p_pk3 => p_pk3,
255 p_pk4 => p_pk4,
256 p_pk5 => p_pk5);
257 exception
258 WHEN FND_API.G_EXC_ERROR THEN
259 ROLLBACK;
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
262
263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264 ROLLBACK;
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
267
268 WHEN OTHERS THEN
269 ROLLBACK;
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
272 THEN
273 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
274 END IF;
275 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
276
277 end update_org_parameters;
278
279
280 -- abedajna added this for seed data entry.
281
282 procedure load_initial_seed_data (p_PARAMETER_NAME in varchar2,
283 p_parameter_value in varchar2,
284 p_pk1 in varchar2,
285 p_pk2 in varchar2,
286 p_pk3 in varchar2,
287 p_pk4 in varchar2,
288 p_pk5 in varchar2,
289 x_owner in varchar2,
290 x_last_update_date in varchar2) is
291 l_OWNER number;
292 l_last_update_date date;
293 l_dummy number;
294 begin
295 select 1 into l_dummy from amw_parameters where PARAMETER_NAME = p_PARAMETER_NAME and PK1 = p_PK1;
296 exception
297 when no_data_found then
298 l_OWNER := fnd_load_util.owner_id(X_OWNER);
299 l_last_update_date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
300 insert into amw_parameters(OBJECT_VERSION_NUMBER,
301 PARAMETER_NAME,
302 PARAMETER_VALUE,
303 PK1,
304 pk2,
305 pk3,
306 pk4,
307 pk5,
308 CREATED_BY,
309 CREATION_DATE,
310 LAST_UPDATED_BY,
311 LAST_UPDATE_DATE,
312 LAST_UPDATE_LOGIN)
313 VALUES (
314 1,
315 p_PARAMETER_NAME,
316 p_parameter_value,
317 decode (p_pk1, '*NULL*', null, p_pk1),
318 decode (p_pk2, '*NULL*', null, p_pk2),
319 decode (p_pk3, '*NULL*', null, p_pk3),
320 decode (p_pk4, '*NULL*', null, p_pk4),
321 decode (p_pk5, '*NULL*', null, p_pk5),
322 l_OWNER,
323 l_last_update_date,
324 l_OWNER,
325 l_last_update_date,
326 0);
327
328
329 end load_initial_seed_data;
330
331 --kosriniv..for bug fix..4336520
332 PROCEDURE default_org_parameters(
333 p_org IN VARCHAR2,
334 p_commit IN VARCHAR2 := FND_API.G_FALSE,
335 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
336 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY VARCHAR2,
339 x_msg_data OUT NOCOPY VARCHAR2
340 ) IS
341 L_API_NAME CONSTANT VARCHAR2(30) := 'default_org_parameters';
342 l_proc_approval_option AMW_PARAMETERS.PARAMETER_VALUE%TYPE;
343 l_proc_auto_approve AMW_PARAMETERS.PARAMETER_VALUE%TYPE;
344 BEGIN
345
346
347 -- Standard Initialization..
348 G_USER_ID := FND_GLOBAL.USER_ID;
349 G_LOGIN_ID := FND_GLOBAL.CONC_LOGIN_ID;
350
351 --standard message list initialization code..
352 x_return_status := FND_API.G_RET_STS_SUCCESS;
353 IF FND_API.to_Boolean( p_init_msg_list ) THEN
354 FND_MSG_PUB.initialize;
355 END IF;
356 IF FND_GLOBAL.User_Id IS NULL THEN
357 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
358 RAISE FND_API.G_EXC_ERROR;
359 END IF;
360
361 -- Select the Values from Risk Library...
362
363 select parameter_value
364 into l_proc_approval_option
365 from amw_parameters
366 where parameter_name = 'PROCESS_APPROVAL_OPTION'
367 and pk1 = -1;
368
369 select parameter_value
370 into l_proc_auto_approve
371 from amw_parameters
372 where parameter_name = 'PROCESS_AUTO_APPROVE'
373 and pk1 = -1;
374
375 -- Initialize the Parameters...
376
377 initialize_org_parameters(
378 p_process_approval_option => l_proc_approval_option,
379 p_process_auto_approve => l_proc_auto_approve,
380 p_pk1 => p_org,
381 x_return_status => x_return_status,
382 x_msg_count => x_msg_count,
383 x_msg_data => x_msg_data);
384
385 exception
386 WHEN FND_API.G_EXC_ERROR THEN
387 ROLLBACK;
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
390
391 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
392 ROLLBACK;
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
395
396 WHEN OTHERS THEN
397 ROLLBACK;
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
399 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
400 THEN
401 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
402 END IF;
403 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
404 END default_org_parameters;
405
406 -- kosriniv ..update orgs concurrent programs
407 PROCEDURE update_all_org_params_cp(
408 errbuf out nocopy varchar2,
409 retcode out nocopy varchar2,
410 p_proc_approval_option in varchar2, -- A,B,C..
411 p_approval_required in varchar2, -- Y/N ..Y means auto_approve is No and N means auto_approve is Yes.
412 p_all_orgs in varchar2 -- NOCONF means only orgs that have not been configured..ALL means set/update all the orgs..
413 ) is
414
415 L_API_NAME CONSTANT VARCHAR2(30) := 'update_all_org_params_cp';
416 l_proc_approval_option AMW_PARAMETERS.PARAMETER_VALUE%TYPE;
420 cursor orgs_to_configure is
417 l_proc_auto_approve AMW_PARAMETERS.PARAMETER_VALUE%TYPE;
418 conc_status boolean;
419
421 select organization_id
422 from amw_audit_units_v
423 where organization_id not in (select organization_id
424 from amw_process_organization
425 where process_id = -2 );
426
427 -- when updating you need to skip those orgs in which processes are in pending approval.
428 cursor orgs_to_update is
429 select organization_id
430 from amw_process_organization
431 where process_id = -2
432 and organization_id not in (select distinct organization_id
433 from amw_process_locks);
434
435 begin
436
437
438 retcode :=0;
439 errbuf :='';
440
441 -- first check whether the options available or not.
442 if p_proc_approval_option is null then
443 fnd_file.put_line (fnd_file.LOG, 'No Process Approval Option provided. Defaulting from Risk Library');
444 -- Select the Values from Risk Library...
445 select parameter_value
446 into l_proc_approval_option
447 from amw_parameters
448 where parameter_name = 'PROCESS_APPROVAL_OPTION'
449 and pk1 = -1;
450 else
451 l_proc_approval_option := p_proc_approval_option;
452 end if;
453
454 if p_approval_required is null then
455 fnd_file.put_line (fnd_file.LOG, 'No Process Approval Required Parameter provided. Defaulting from Risk Library');
456 select parameter_value
457 into l_proc_auto_approve
458 from amw_parameters
459 where parameter_name = 'PROCESS_AUTO_APPROVE'
460 and pk1 = -1;
461 else
462 if p_approval_required = 'N' then
463 l_proc_auto_approve := 'Y';
464 else l_proc_auto_approve := 'N';
465 end if;
466 end if;
467 if 'ALL' = p_all_orgs OR 'NOCONF' = p_all_orgs then
468 -- Now check p_all_orgs
469 if 'ALL' = p_all_orgs then
470 fnd_file.put_line (fnd_file.LOG, 'Updating Existing Org Parameters. Skipping Organizations where Process are in pending approval.');
471 -- now update those that are configured..
472 for org in orgs_to_update loop
473 exit when orgs_to_update%notfound;
474
475 -- UPDATE PROCESS_APPROVAL_OPTION..
476 update_parameter(
477 p_parameter_name => 'PROCESS_APPROVAL_OPTION',
478 p_parameter_value => l_proc_approval_option,
479 p_pk1 => org.organization_id,
480 p_pk2 => null,
481 p_pk3 => null,
482 p_pk4 => null,
483 p_pk5 => null);
484 -- UPDATE PROCESS_AUTO_APPROVE ..
485 update_parameter(
486 p_parameter_name => 'PROCESS_AUTO_APPROVE',
487 p_parameter_value => l_proc_auto_approve,
488 p_pk1 => org.organization_id,
489 p_pk2 => null,
490 p_pk3 => null,
491 p_pk4 => null,
492 p_pk5 => null);
493 end loop;
494 end if;
495 -- by default only set those that are not configured...
496 for org in orgs_to_configure loop
497 exit when orgs_to_configure%notfound;
498 -- INSERT PROCESS_APPROVAL_OPTION..
499 insert_parameter(
500 p_parameter_name => 'PROCESS_APPROVAL_OPTION',
501 p_parameter_value => l_proc_approval_option,
502 p_pk1 => org.organization_id,
503 p_pk2 => null,
504 p_pk3 => null,
505 p_pk4 => null,
506 p_pk5 => null);
507 -- INSERT PROCESS_AUTO_APPROVE ..
508 insert_parameter(
509 p_parameter_name => 'PROCESS_AUTO_APPROVE',
510 p_parameter_value => l_proc_auto_approve,
511 p_pk1 => org.organization_id,
512 p_pk2 => null,
513 p_pk3 => null,
514 p_pk4 => null,
515 p_pk5 => null);
516 -- INSERT A NEW ROW IN TO THE AMW_PROCESS_ORGANIZATION..
517 INSERT INTO AMW_PROCESS_ORGANIZATION(
518 PROCESS_ORGANIZATION_ID,
519 PROCESS_ID,
520 STANDARD_PROCESS_FLAG,
521 APPROVAL_STATUS,
522 ORGANIZATION_ID,
523 OBJECT_VERSION_NUMBER,
524 PROCESS_CODE,
525 REVISION_NUMBER,
526 PROCESS_ORG_REV_ID,
527 START_DATE,
528 APPROVAL_DATE,
529 RL_PROCESS_REV_ID,
530 RISK_CATEGORY,
531 CREATED_BY,
532 CREATION_DATE,
533 LAST_UPDATED_BY,
534 LAST_UPDATE_DATE,
535 LAST_UPDATE_LOGIN)
536 values(
537 AMW_PROCESS_ORGANIZATION_S.nextval,
538 -2,
539 'Y',
540 'A',
541 org.organization_id,
542 1,
543 '-2',
544 1,
545 AMW_PROCESS_ORG_REV_S.nextval,
546 sysdate,
547 sysdate,
548 -2,
549 'R',
550 g_user_id,
551 sysdate,
552 g_user_id,
553 sysdate,
554 G_LOGIN_ID
555 );
556 end loop;
557 end if;
558 commit;
559 exception
560 when others then
561 rollback;
562 retcode :=2;
563 errbuf :=SUBSTR(SQLERRM,1,1000);
564 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error: '|| SQLERRM);
565 end update_all_org_params_cp;
566
567 END AMW_PARAMETERS_PVT_PKG;