1 package body per_fr_disc as
2 /* $Header: hrfrdisc.pkb 115.10 2003/05/02 14:27:50 jheer noship $ */
3
4 procedure exec_dyn_sql (string in varchar2)
5 is
6 cursor_name integer;
7 ret integer;
8 begin
9 cursor_name := DBMS_SQL.OPEN_CURSOR;
10 --DDL statements are executed by the parse call, which
11 --performs the implied commit
12 DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.V7);
13 ret := DBMS_SQL.EXECUTE(cursor_name);
14 DBMS_SQL.CLOSE_CURSOR(cursor_name);
15 end exec_dyn_sql;
16
17 procedure grant_hr_summary (errbuf out nocopy varchar2,
18 retcode out nocopy number,
19 db_connect_string in varchar2,
20 eul_user in varchar2,
21 eul_password in varchar2)
22 is
23 begin
24 --
25 hr_utility.trace('new pkg');
26 exec_dyn_sql('GRANT SELECT ON PAY_USER_TABLES TO '||eul_user);
27 exec_dyn_sql('GRANT SELECT ON PAY_USER_COLUMNS TO '||eul_user);
28 exec_dyn_sql('GRANT SELECT ON HR_SUMMARY_ITEM_TYPE TO '||eul_user);
29 exec_dyn_sql('GRANT SELECT ON HR_SUMMARY_ITEM_TYPE_USAGE TO '||eul_user);
30 exec_dyn_sql('GRANT SELECT ON HR_SUMMARY_ITEM_VALUE TO '||eul_user);
31 exec_dyn_sql('GRANT SELECT ON HR_SUMMARY_KEY_TYPE TO '||eul_user);
32 --
33 exec_dyn_sql('GRANT SELECT ON HR_SUMMARY_KEY_TYPE_USAGE TO '||eul_user);
34 exec_dyn_sql('GRANT SELECT ON HR_SUMMARY_KEY_VALUE TO '||eul_user);
35 exec_dyn_sql('GRANT SELECT ON HR_SUMMARY_PARAMETER TO '||eul_user);
36 exec_dyn_sql('GRANT SELECT ON HR_SUMMARY_TEMPLATE TO '||eul_user);
37 exec_dyn_sql('GRANT SELECT ON HR_SUMMARY_PROCESS_RUN TO '||eul_user);
38 exec_dyn_sql('GRANT SELECT ON FND_ID_FLEX_SEGMENTS_VL TO '||eul_user);
39 exec_dyn_sql('GRANT SELECT ON PAY_USER_COLUMN_INSTANCES_V2 TO '||eul_user);
40 exec_dyn_sql('GRANT SELECT ON PAY_USER_ROWS_X TO '||eul_user);
41 exec_dyn_sql('grant execute on hr_general to '||eul_user);
42 exec_dyn_sql('grant execute on fnd_message to '||eul_user);
43 --
44 -- new objects added in 115.9
45 exec_dyn_sql('grant SELECT ON HR_SUMMARY_RESTRICTION_TYPE to '||eul_user);
46 exec_dyn_sql('grant SELECT on HR_SUMMARY_VALID_RESTRICTION to '||eul_user);
47 exec_dyn_sql('grant SELECT on HR_SUMMARY_RESTRICTION_USAGE to '||eul_user);
48 exec_dyn_sql('grant SELECT on HR_SUMMARY_RESTRICTION_VALUE to '||eul_user);
49 exec_dyn_sql('grant SELECT on HR_SUMMARY_VALID_KEY_TYPE to '||eul_user);
50 exec_dyn_sql('grant SELECT on HR_SUMMARY_RUN to '||eul_user);
51 exec_dyn_sql('grant select on HR_SUMMARY_RUN_PARAMETER_VALUE to '||eul_user);
52 exec_dyn_sql('grant select on HR_SUMMARY_KEY_TYPE2 to '||eul_user);
53 exec_dyn_sql('grant select on HR_SUMMARY_KEY_VALUES_V to '||eul_user);
54 exec_dyn_sql('grant select on HR_SUMMARY_KEY_VALUES_ESTAB_V to '||eul_user);
55 exec_dyn_sql('grant select on HR_SUMMARY_KEY_VALUES_LOOKUP_V to '||eul_user);
56 exec_dyn_sql('grant select on HR_SUMMARY_KEY_VALUES_BAND_V to '||eul_user);
57 exec_dyn_sql('grant select on HR_SUMMARY_RUN_VALUES_V to '||eul_user);
58 exec_dyn_sql('grant select on HR_SUMMARY_RUN_VALUES_X2_V to '||eul_user);
59
60 errbuf := 'msg - Access to HR has been successfully Granted to Discoverer User';
61 retcode := 0;
62 exception
63 when others then
64 errbuf := sqlerrm;
65 retcode := 2;
66 end grant_hr_summary;
67
68 end per_fr_disc;