[Home] [Help]
PACKAGE BODY: APPS.FII_CCC_SEC_UPL_PKG
Source
1 PACKAGE BODY FII_CCC_SEC_UPL_PKG AS
2 /* $Header: FIICCCSECB.pls 120.1.12000000.1 2007/04/12 21:43:32 lpoon ship $ */
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
5 g_sys_date DATE := sysdate;
6 g_user_id NUMBER := fnd_global.user_id;
7 g_indenting VARCHAR2(4) := ' ';
8 g_space VARCHAR2(30) := ' ';
9 g_long_line VARCHAR2(42) := '------------------------------------------';
10 g_short_line VARCHAR2(10) := '----------';
11
12 --------------------
13 -- debug
14 --------------------
15 PROCEDURE dbg(text IN VARCHAR2)
16 IS
17 BEGIN
18 IF (g_debug_flag = 'Y') THEN
19 fii_util.put_line(text);
20 END IF;
21 END dbg;
22
23
24 -------------------------------------------------------------------
25 -- Web_adi_upload called by WebADI
26 -------------------------------------------------------------------
27 FUNCTION web_adi_upload (
28 x_grantee_name IN VARCHAR2 DEFAULT NULL,
29 --x_grantee_key IN VARCHAR2 DEFAULT NULL,
30 x_role_name IN VARCHAR2 DEFAULT NULL,
31 x_start_date IN DATE DEFAULT NULL,
32 x_end_date IN DATE DEFAULT NULL,
33 x_dimension_code IN VARCHAR2 DEFAULT NULL,
34 x_dimension_value IN VARCHAR2 DEFAULT NULL
35 ) return VARCHAR2 IS
36
37 l_err_msg VARCHAR2(150);
38 l_grantee_key VARCHAR2(240);
39 l_dimension_id VARCHAR2(256);
40
41 BEGIN
42
43 BEGIN
44 SELECT distinct id INTO l_grantee_key
45 FROM hri_dbi_cl_per_n_v
46 WHERE value = x_grantee_name;
47 EXCEPTION
48 WHEN no_data_found THEN
49 l_err_msg := fnd_message.get_string('FII', 'FII_CCC_SEC_INVALID_GRANT_TO');
50 return l_err_msg;
51 WHEN too_many_rows THEN
52 l_err_msg := fnd_message.get_string('FII', 'FII_CCC_SEC_MULTIPLE_GRANT_TO');
53 return l_err_msg;
54 END;
55
56
57 IF x_dimension_code = 'FII_COMPANIES' THEN
58
59 BEGIN
60 SELECT id INTO l_dimension_id
61 FROM fii_ccc_values_v
62 WHERE dimension = 'FII_COMPANIES'
63 AND value = x_dimension_value;
64 EXCEPTION
65 WHEN no_data_found THEN
66 l_err_msg := fnd_message.get_string('FII', 'FII_CCC_SEC_INVALID_COM_VALUE');
67 return l_err_msg;
68 END;
69
70 ELSE
71 IF x_dimension_code = 'HRI_CL_ORGCC' THEN
72
73 BEGIN
74 SELECT id INTO l_dimension_id
75 FROM fii_ccc_values_v
76 WHERE dimension = 'HRI_CL_ORGCC'
77 AND value = x_dimension_value;
78 EXCEPTION
79 WHEN no_data_found THEN
80 l_err_msg := fnd_message.get_string('FII', 'FII_CCC_SEC_INVALID_CC_VALUE');
81 return l_err_msg;
82 END;
83
84 ELSE
85 l_err_msg := fnd_message.get_string('FII', 'FII_CCC_SEC_INVALID_DIMEN_CODE');
86 return l_err_msg;
87 END IF;
88 END IF;
89
90 INSERT INTO fii_ccc_sec_interface
91 (grantee_key,
92 menu_name,
93 start_date,
94 end_date,
95 dimension_code,
96 dimension_id,
97 creation_date,
98 created_by,
99 last_update_date,
100 last_updated_by,
101 last_update_login
102 )
103 VALUES
104 (l_grantee_key,
105 x_role_name,
106 x_start_date,
107 x_end_date,
108 x_dimension_code,
109 l_dimension_id,
110 g_sys_date,
111 g_user_id,
112 g_sys_date,
113 g_user_id,
114 g_user_id
115 );
116
117 return null;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 l_err_msg := fnd_message.get_string('FII', 'FII_CCC_SEC_OTHER_ERR');
122 return l_err_msg;
123 END web_adi_upload;
124
125
126
127 --------------------------------------------------------------------------
128 -- Validate Data in FII_CCC_SEC_INTERFACE before uploading into FND_GRANTS
129 --------------------------------------------------------------------------
130 PROCEDURE Validate (retcode IN OUT NOCOPY VARCHAR2) IS
131
132 l_violations_found BOOLEAN := FALSE;
133 l_grantee_key VARCHAR2(240);
134 l_menu_name VARCHAR2(30);
135 l_cnt NUMBER;
136 l_diff_date_header BOOLEAN := FALSE;
137 l_msg1 VARCHAR2(100):=null;
138 l_msg2 VARCHAR2(100):=null;
139 l_msg_length NUMBER := 42;
140 l_date_length NUMBER := 10;
141 l_grant_to_name VARCHAR2(153);
142 l_role_user_name VARCHAR2(80);
143 l_sd DATE;
144 l_ed DATE;
145
146 CURSOR v1_csr IS
147 SELECT grantee_key, menu_name, count(*)
148 FROM
149 (
150 SELECT distinct grantee_key, menu_name, start_date, end_date
151 FROM fii_ccc_sec_interface
152 WHERE status_code IS NULL
153 ) a
154 GROUP BY grantee_key, menu_name
155 HAVING COUNT(*) > 1;
156
157 CURSOR get_sd_ed_csr(p_grantee_key VARCHAR2, p_menu_name VARCHAR2) IS
158 SELECT distinct start_date, end_date
159 FROM fii_ccc_sec_interface
160 WHERE status_code IS NULL
161 AND grantee_key = p_grantee_key
162 AND menu_name = p_menu_name;
163
164 BEGIN
165
166 -- debug msg time stamp of entering this procedure
167 IF g_debug_flag = 'Y' THEN
168 FII_MESSAGE.Func_Ent('FII_CCC_SEC_UPL_PKG.validate');
169 END IF;
170
171 --------------------------------------------------------------
172 -- 1. The start/end date should be the same across all records
173 -- for the same GrantTo/Role pair
174 --------------------------------------------------------------
175 dbg('validate: check start/end date should be the same across all records for the same GrantTo/Role pair');
176 IF (NOT v1_csr%ISOPEN) THEN
177 OPEN v1_csr;
178 END IF;
179
180 LOOP
181 FETCH v1_csr INTO l_grantee_key, l_menu_name, l_cnt;
182 EXIT WHEN v1_csr%NOTFOUND;
183 l_violations_found := TRUE;
184
185 IF (NOT l_diff_date_header) then
186 fii_util.put_line(fnd_message.get_string('FII', 'FII_CCC_SEC_DIFF_DATES'));
187 fii_util.put_line(' Grant To (GRANTEE_KEY)' || g_indenting ||
188 ' Role (MENU_NAME)' || g_indenting ||
189 'Start Date' || g_indenting ||
190 ' End Date' || g_indenting);
191 fii_util.put_line( g_long_line || g_indenting || g_long_line || g_indenting ||
192 g_short_line|| g_indenting || g_short_line);
193 l_diff_date_header := TRUE;
194
195 END IF;
196
197 BEGIN
198 SELECT distinct value INTO l_grant_to_name
199 FROM hri_dbi_cl_per_n_v
200 WHERE id = l_grantee_key;
201 EXCEPTION
202 WHEN no_data_found THEN
203 l_grant_to_name := 'N/A';
204 END;
205
206 BEGIN
207 SELECT distinct role_user_name INTO l_role_user_name
208 FROM fii_ccc_sec_roles_v
209 WHERE role_name = l_menu_name;
210 EXCEPTION
211 WHEN no_data_found THEN
212 l_role_user_name := 'N/A';
213 END;
214
215
216 FOR get_sd_ed_rec IN get_sd_ed_csr(l_grantee_key, l_menu_name)
217 LOOP
218
219 -- print out the records that violate the validation 1 in the log file
220 l_msg1 := substr(l_grant_to_name,1,30) || ' (' || l_grantee_key || ')';
221 l_msg2 := substr(l_role_user_name,1,30) || ' (' || l_menu_name || ')';
222 fii_util.put_line(substr(g_space, 1, l_msg_length - length(l_msg1)) || substr(l_msg1,1,42) ||
223 g_indenting ||
224 substr(g_space, 1, l_msg_length - length(l_msg2)) || substr(l_msg2,1,42) ||
225 g_indenting ||
226 substr(g_space, 1, l_date_length - length(to_char(get_sd_ed_rec.start_date))) ||
227 get_sd_ed_rec.start_date ||
228 g_indenting ||
229 substr(g_space, 1, l_date_length - length(to_char(get_sd_ed_rec.end_date))) ||
230 get_sd_ed_rec.end_date
231 );
232 END LOOP;
233
234
235 --Update the Status_Code
236 UPDATE fii_ccc_sec_interface
237 SET status_code = 'ERR - DIFFERENT DATES',
238 last_update_date = g_sys_date,
239 last_updated_by = g_user_id,
240 last_update_login = g_user_id
241 WHERE status_code is NULL
242 AND grantee_key = l_grantee_key
243 AND menu_name = l_menu_name;
244
245 END LOOP;
246 CLOSE v1_csr;
247
248 IF l_violations_found = FALSE THEN
249 dbg('validate: validation 1 - passed');
250 retcode := 'S';
251
252 -- debug msg time stamp of completing this procedure successfully
253 IF g_debug_flag = 'Y' THEN
254 FII_MESSAGE.Func_Succ(func_name => 'FII_CCC_SEC_UPL_PKG.validate');
255 END IF;
256
257 ELSE
258
259 ------------------------------------------------------------
260 -- Update the remaining records that has status_code is NULL
261 -- to VALIDATED to avoid being picked up in the next run.
262 --------------------------------------------------------------
263 dbg('validate: validation 1 - failed');
264 dbg('validate: update remaining valid records to VALIDATED');
265 UPDATE fii_ccc_sec_interface
266 SET status_code = 'VALIDATED',
267 last_update_date = g_sys_date,
268 last_updated_by = g_user_id,
269 last_update_login = g_user_id
270 WHERE status_code is NULL;
271
272 retcode := 'E';
273 -- debug msg time stamp of exiting this procedure
274 IF g_debug_flag = 'Y' THEN
275 FII_MESSAGE.Func_Succ(func_name => 'FII_CCC_SEC_UPL_PKG.validate');
276 END IF;
277
278 END IF;
279
280 END validate;
281
282 -------------------------------------------------------------------
283 -- Upload Data into FND_GRANTS
284 -------------------------------------------------------------------
285 PROCEDURE upload (retcode IN OUT NOCOPY VARCHAR2) IS
286
287
288 CURSOR c1_csr IS
289 SELECT grantee_key, menu_name,
290 start_date, end_date,
291 dimension_code, dimension_id
292 FROM fii_ccc_sec_interface
293 WHERE status_code IS NULL
294 ORDER BY grantee_key, menu_name, dimension_code;
295
296
297 CURSOR get_grant_guid_csr IS
298 SELECT g.grant_guid --, g.grantee_key, g.menu_id, int.menu_name
299 FROM fnd_grants g,
300 fnd_menus m,
301 (
302 SELECT distinct grantee_key, menu_name
303 FROM fii_ccc_sec_interface
304 WHERE status_code IS NULL
305 ) int
306 WHERE int.grantee_key = g.grantee_key
307 AND int.menu_name = m.menu_name
308 AND m.menu_id = g.menu_id;
309
310 l_trunc_retcode VARCHAR2(20) := NULL;
311 x_grant_guid raw(16);
312 x_success VARCHAR(30);
313 x_errorcode VARCHAR2(500);
314
315 BEGIN
316
317 -- debug msg time stamp of entering this procedure
318 IF g_debug_flag = 'Y' THEN
319 FII_MESSAGE.Func_Ent('FII_CCC_SEC_UPL_PKG.upload');
320 END IF;
321
322
323 -- If there is already an existing grant for the Grant To / Role pair,
324 -- the existing grant will be completely deleted and overwritten by
325 -- the new grant.
326 dbg('upload: delete any existing grant for the grant to / role pair');
327
328 FOR get_grant_guid_rec IN get_grant_guid_csr LOOP
329
330 fnd_grants_pkg.revoke_grant
331 (
332 p_api_version => 1.0,
333 p_grant_guid => get_grant_guid_rec.grant_guid,
334 x_success => x_success,
335 x_errorcode => x_errorcode
336 );
337
338 IF x_success = FND_API.G_TRUE THEN
339 dbg('upload: revoke grant succeed. grant_guid = '||to_char(get_grant_guid_rec.grant_guid));
340 ELSE
341 dbg('upload: revoke grant failed. grant_guid = '||to_char(get_grant_guid_rec.grant_guid));
342 retcode := 'E';
343 RETURN;
344 END IF;
345 END LOOP;
346
347
348 --
349 -- Upload Data into FND_GRANTS
350 --
351 dbg('upload: upload data into fnd_grants');
352 FOR c1_rec IN c1_csr LOOP
353
354 dbg('upload: grantee_key=' || c1_rec.grantee_key
355 || ' menu_name=' || c1_rec.menu_name
356 || ' start_date=' || c1_rec.start_date
357 || ' end_date=' || c1_rec.end_date
358 || ' dimension_code=' || c1_rec.dimension_code
359 || ' dimension_id=' || c1_rec.dimension_id);
360
361 fnd_grants_pkg.grant_function
362 (
363 p_api_version => 1.0,
364 p_menu_name => c1_rec.menu_name,
365 p_object_name => 'HRI_PER',
366 p_instance_type => 'INSTANCE',
367 -- p_instance_set_id IN NUMBER DEFAULT NULL,
368 p_instance_pk1_value => c1_rec.dimension_id,
369 -- p_instance_pk2_value IN VARCHAR2 DEFAULT NULL,
370 -- p_instance_pk3_value IN VARCHAR2 DEFAULT NULL,
371 -- p_instance_pk4_value IN VARCHAR2 DEFAULT NULL,
372 -- p_instance_pk5_value IN VARCHAR2 DEFAULT NULL,
373 p_grantee_type => 'USER',
374 p_grantee_key => c1_rec.grantee_key,
375 p_start_date => c1_rec.start_date,
379 x_grant_guid => x_grant_guid, -- OUT
376 p_end_date => c1_rec.end_date,
377 p_program_name => 'BIS_PMV_GRANTS',
378 -- p_program_tag IN VARCHAR2 DEFAULT NULL,
380 x_success => x_success, -- OUT
381 x_errorcode => x_errorcode, -- OUT
382 p_parameter1 => c1_rec.dimension_code
383 -- p_parameter2 IN VARCHAR2 DEFAULT NULL,
384 -- p_parameter3 IN VARCHAR2 DEFAULT NULL,
385 -- p_parameter4 IN VARCHAR2 DEFAULT NULL,
386 -- p_parameter5 IN VARCHAR2 DEFAULT NULL,
387 -- p_parameter6 IN VARCHAR2 DEFAULT NULL,
388 -- p_parameter7 IN VARCHAR2 DEFAULT NULL,
389 -- p_parameter8 IN VARCHAR2 DEFAULT NULL,
390 -- p_parameter9 IN VARCHAR2 DEFAULT NULL,
391 -- p_parameter10 IN VARCHAR2 DEFAULT NULL,
392 -- p_ctx_secgrp_id IN NUMBER default -1,
393 -- p_ctx_resp_id IN NUMBER default -1,
394 -- p_ctx_resp_appl_id IN NUMBER default -1,
395 -- p_ctx_org_id IN NUMBER default -1,
396 -- p_name IN VARCHAR2 default null,
397 -- p_description IN VARCHAR2 default null
398 );
399
400 IF x_success = FND_API.G_TRUE THEN
401 dbg('upload: record created in fnd_grants. grant_guid = '||to_char(x_grant_guid));
402 ELSE
403 dbg('upload: record creation failed');
404 retcode := 'E';
405 RETURN;
406 END IF;
407
408 END LOOP;
409
410
411 -- update status_code column in the interface table to 'UPLOADED'
412 dbg('upload: update status_code to UPLOADED in the interface table');
413 UPDATE fii_ccc_sec_interface
414 SET status_code = 'UPLOADED',
415 upload_date = g_sys_date,
416 last_update_date = g_sys_date,
417 last_updated_by = g_user_id,
418 last_update_login = g_user_id
419 WHERE status_code IS NULL;
420
421 retcode := 'S';
422 -- debug msg time stamp of completing this procedure successfully
423 IF g_debug_flag = 'Y' THEN
424 FII_MESSAGE.Func_Succ(func_name => 'FII_CCC_SEC_UPL_PKG.upload');
425 END IF;
426
427
428 END upload;
429
430 ----------------------------------------------------------------------
431 -- Upload Company Cost Center Security Data from interface table
432 -- to fnd_grants
433 -- Called by concurrent program "Upload Company Cost Center Security"
434 --
435 -- CP: FII_CCC_SEC_UPLOAD_C (Upload Company Cost Center Security)
436 -- Executable: FII_CCC_SEC_UPLOAD_C
437 -- Execution File Name: FII_CCC_SEC_UPL_PKG.CONC_UPLOAD
438 -- Resp: Business Intelligence Administrator
439 -- Request Group: DBI Requests and Reports
440 -------------------------------------------------------------------
441 PROCEDURE conc_upload
442 (
443 errbuf OUT NOCOPY VARCHAR2,
444 retcode OUT NOCOPY VARCHAR2) IS
445
446 l_record_count NUMBER;
447 l_ret_status BOOLEAN;
448
449 BEGIN
450
451 -- debug msg time stamp of entering this procedure
452 IF g_debug_flag = 'Y' THEN
453 FII_MESSAGE.Func_Ent('FII_CCC_SEC_UPL_PKG.conc_upload');
454 END IF;
455
456 --
457 -- check if any data to process, exit if not
458 --
459 dbg('conc_upload: Check if any data in fii_ccc_sec_interface to process.');
460 SELECT count(*) INTO l_record_count
461 FROM fii_ccc_sec_interface
462 WHERE status_code IS null;
463
464 IF l_record_count = 0 THEN
465 dbg('conc_upload: No data in the interface table to process. Exit.');
466 -- debug msg time stamp of exiting this procedure
467 IF g_debug_flag = 'Y' THEN
468 FII_MESSAGE.Func_Succ('FII_CCC_SEC_UPL_PKG.conc_upload');
469 END IF;
470 retcode := 'W';
471 errbuf := fnd_message.get_string('FII', 'FII_CCC_SEC_CP_NO_REC_TO_PROCS');
472 l_ret_status := FND_CONCURRENT.Set_Completion_Status(status => 'WARNING',message => errbuf);
473 RETURN;
474 END IF;
475
476 --
477 -- validate records in fii_ccc_sec_interface table
478 --
479 dbg('conc_upload: Validate records in fii_ccc_sec_interface table. Calling validate procedure...');
480
481 validate(retcode);
482
483 IF retcode <> 'S' THEN
484 -- Commit before returning for status_code
485 dbg('conc_upload: call FND_CONCURRENT.Af_Commit');
486 FND_CONCURRENT.Af_Commit;
487
488 retcode := 'E';
489 errbuf := fnd_message.get_string('FII', 'FII_CCC_SEC_CP_VALIDATE_ERR');
490 l_ret_status := FND_CONCURRENT.Set_Completion_Status(status => 'ERROR',message => errbuf);
491 dbg('conc_upload: Data validation failed. Exit.');
492 -- debug msg time stamp of exiting this procedure
493 IF g_debug_flag = 'Y' THEN
494 FII_MESSAGE.Func_Succ('FII_CCC_SEC_UPL_PKG.conc_upload');
495 END IF;
496 RETURN;
497 END IF;
498
499 --
500 -- upload into fnd_grants
501 --
502 dbg('conc_upload: Upload data in interface table into fnd_grants. Calling upload procedure...');
503
504 upload(retcode);
505
506 IF retcode <> 'S' THEN
507 errbuf := fnd_message.get_string('FII', 'FII_CCC_SEC_CP_UPLOAD_ERR');
508 l_ret_status := FND_CONCURRENT.Set_Completion_Status(status => 'ERROR',message => errbuf);
509 dbg('conc_upload: Upload to fnd_grants process failed. Exit.');
510 -- debug msg time stamp of exiting this procedure
511 IF g_debug_flag = 'Y' THEN
512 FII_MESSAGE.Func_Succ('FII_CCC_SEC_UPL_PKG.conc_upload');
513 END IF;
517 -- debug msg time stamp of completing this procedure successfully
514 RETURN;
515 END IF;
516
518 IF g_debug_flag = 'Y' THEN
519 FII_MESSAGE.Func_Succ(func_name => 'FII_CCC_SEC_UPL_PKG.conc_upload');
520 END IF;
521 l_ret_status := FND_CONCURRENT.Set_Completion_Status
522 (status => 'COMPLETE', message => NULL);
523 EXCEPTION
524 WHEN OTHERS THEN
525 dbg('conc_upload: Unexpected error during concurrent upload process.');
526 retcode := 'E';
527 errbuf := fnd_message.get_string('FII', 'FII_CCC_SEC_CP_OTHERS_ERR');
528 l_ret_status := FND_CONCURRENT.Set_Completion_Status(status => 'ERROR',message => errbuf);
529 app_exception.raise_exception;
530 END conc_upload;
531
532
533
534 --------------------------------------------------------------------------
535 -- Purge interface table FII_CCC_SEC_INTERFACE
536 -- Called by concurrent program
537 -- "Purge Company Cost Center Security Interface Table"
538 --
539 -- CP: FII_CCC_SEC_PURGE_C
540 -- Purge Company Cost Center Security Interface Table
541 -- Executable: FII_CCC_SEC_PURGE_C
542 -- Execution File Name: FII_CCC_SEC_UPL_PKG.purge_interface
543 -- Resp: Business Intelligence Administrator
544 -- Request Group: DBI Requests and Reports
545 ----------------------------------------------------------------------
546 PROCEDURE purge_interface
547 (
548 errbuf OUT NOCOPY VARCHAR2,
549 retcode OUT NOCOPY VARCHAR2) IS
550
551 l_record_count NUMBER;
552 l_ret_status BOOLEAN;
553 l_trunc_retcode VARCHAR2(6);
554
555 BEGIN
556
557 -- debug msg time stamp of entering this procedure
558 IF g_debug_flag = 'Y' THEN
559 FII_MESSAGE.Func_Ent('FII_CCC_SEC_UPL_PKG.purge_interface');
560 END IF;
561
562 --
563 -- purge fii_ccc_sec_interface table
564 --
565 dbg('purge_interface: truncate fii_ccc_sec_interface table.');
566 fii_util.truncate_table('FII_CCC_SEC_INTERFACE', 'FII', l_trunc_retcode);
567
568
569 IF l_trunc_retcode = -1 THEN
570 dbg('purge_interface: purge interface table failed. Exit.');
571 -- debug msg time stamp of exiting this procedure
572 IF g_debug_flag = 'Y' THEN
573 FII_MESSAGE.Func_Succ('FII_CCC_SEC_UPL_PKG.purge_interface');
574 END IF;
575 retcode := 'E';
576 errbuf := fnd_message.get_string('FII', 'FII_CCC_SEC_PURGE_INTR_FAILED');
577 l_ret_status := FND_CONCURRENT.Set_Completion_Status(status => 'ERROR',message => errbuf);
578 RETURN;
579 ELSE
580 -- debug msg time stamp of completing this procedure successfully
581 IF g_debug_flag = 'Y' THEN
582 FII_MESSAGE.Func_Succ(func_name => 'FII_CCC_SEC_UPL_PKG.purge_interface');
583 END IF;
584 l_ret_status := FND_CONCURRENT.Set_Completion_Status
585 (status => 'COMPLETE', message => NULL);
586 END IF;
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 dbg('purge_interface: Unexpected error during purge process.');
591 retcode := 'E';
592 errbuf := fnd_message.get_string('FII', 'FII_CCC_SEC_CP_PURGE_ERR');
593 l_ret_status := FND_CONCURRENT.Set_Completion_Status(status => 'ERROR',message => errbuf);
594 app_exception.raise_exception;
595 END purge_interface;
596
597
598 END FII_CCC_SEC_UPL_PKG;
599