1 PACKAGE BODY ZPB_OPENSQL_PKG as
2 /* $Header: ZPBOSQLB.pls 120.5 2007/12/06 12:35:58 mbhat noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(16) := 'ZPB_OPENSQL_PKG';
5
6 -- This procedure must be called by the APPS user.
7 -- It will enable open-sql access to the user/schema that
8 -- is passed in as the first argument to the procedure. The
9 -- second argument specifies the business area of interest
10 -- The procedure will enable open-sql access for the new user
11 -- to all dimension and data views that have already been created.
12 -- The procedure must be run again to allow open-sql access to
13 -- dimension and data views that have been created since the last
14 -- running of the procedure.
15 PROCEDURE ENABLE(errbuf out nocopy varchar2,
16 retcode out nocopy varchar2,
17 p_schema_name in varchar2,
18 p_business_area_id in number)
19 IS
20 e_insuff_privs EXCEPTION;
21 PRAGMA EXCEPTION_INIT (e_insuff_privs, -1031);
22
23 l_shar_aw varchar2(32);
24 l_annot_aw varchar2(32);
25 l_zpb_pref varchar2(8);
26
27 CURSOR c_sharedViews is
28 select table_name
29 from zpb_tables
30 where (table_name like 'ZPBDATA%' or table_name like 'ZPB'||p_Business_Area_id||'_D%') and
31 bus_area_id = p_business_area_id;
32
33 v_sharedView c_sharedViews%ROWTYPE;
34
35 BEGIN
36
37 --zpb_log.write('zpb_opensql_pkg.enable','called for schema ' ||
38 -- p_schema_name || ' and business area ' || to_char( p_business_area_id));
39
40 l_zpb_pref:= zpb_aw.get_schema;
41
42 select data_aw, annotation_aw into l_shar_aw, l_annot_aw
43 from zpb_business_areas
44 where business_area_id=p_business_area_id;
45
46 -- attempt to grant priviliges to business area shared data and code AWs to user.
47 -- This will only succeed if the apps schema has been granted the privilege to grant
48 -- others select privileges to ZPB objects by the system schema
49 begin
50 execute immediate 'grant select on ' || l_zpb_pref || '.aw$zpbcode to ' || p_schema_name;
51 execute immediate 'grant select on ' || l_zpb_pref || '.aw$' || l_shar_aw || ' to ' || p_schema_name;
52 execute immediate 'grant select on ' || l_zpb_pref || '.aw$' || l_annot_aw || ' to ' || p_schema_name;
53 exception
54 when e_insuff_privs then
55 retcode :=1;
56 errbuf := 'Insufficient privileges to AW lob. Please run patch/115/sql/zpboszpb.sql script with ZPB user';
57 end;
58
59 -- Grant execute priviliges on the open-sql initialization procedure
60 execute immediate 'grant execute on zpb_security_context to ' || p_schema_name;
61 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_security_context for zpb_security_context';
62 execute immediate 'grant execute on zpb_log to ' || p_schema_name;
63 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_log for zpb_log';
64 execute immediate 'grant execute on zpb_util_pvt to ' || p_schema_name;
65 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_util_pvt for zpb_util_pvt';
66 -- Create necessary synonyms and grant privileges to execute SQL initialization procedure
67 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_business_areas for zpb_business_areas';
68 execute immediate 'grant select on ZPB_BUSINESS_AREAS to ' || p_schema_name;
69 execute immediate 'create or replace synonym ' || p_schema_name || '.fnd_oracle_userid for fnd_oracle_userid';
70 execute immediate 'create or replace synonym ' || p_schema_name || '.fnd_application for fnd_application';
71 execute immediate 'create or replace synonym ' || p_schema_name || '.fnd_product_installations for fnd_product_installations';
72 execute immediate 'grant select on fnd_oracle_userid to ' || p_schema_name;
73 execute immediate 'grant select on fnd_application to ' || p_schema_name;
74 execute immediate 'grant select on fnd_product_installations to ' || p_schema_name;
75
76 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_ac_param_values for zpb_ac_param_values';
77 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_cycle_datasets for zpb_cycle_datasets';
78 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_cycle_currencies for zpb_cycle_currencies';
79 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_cycle_model_dimensions for zpb_cycle_model_dimensions';
80 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_analysis_cycle_tasks for zpb_analysis_cycle_tasks';
81 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_analysis_cycle_instances for zpb_analysis_cycle_instances';
82 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_analysis_cycles for zpb_analysis_cycles';
83 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_solve_output_selections for zpb_solve_output_selections';
84 execute immediate 'create or replace synonym ' || p_schema_name || '.zpb_business_areas_vl for zpb_business_areas_vl';
85 execute immediate 'create or replace synonym ' || p_schema_name || '.fnd_lookup_values_vl for fnd_lookup_values_vl';
86 execute immediate 'grant select on zpb_ac_param_values to ' || p_schema_name;
87 execute immediate 'grant select on zpb_cycle_datasets to ' || p_schema_name;
88 execute immediate 'grant select on zpb_cycle_currencies to ' || p_schema_name;
89 execute immediate 'grant select on zpb_cycle_model_dimensions to ' || p_schema_name;
90 execute immediate 'grant select on zpb_analysis_cycle_tasks to ' || p_schema_name;
91 execute immediate 'grant select on zpb_analysis_cycle_instances to ' || p_schema_name;
92 execute immediate 'grant select on zpb_analysis_cycles to ' || p_schema_name;
93 execute immediate 'grant select on zpb_solve_output_selections to ' || p_schema_name;
94 execute immediate 'grant select on zpb_business_areas_vl to ' || p_schema_name;
95 execute immediate 'grant select on fnd_lookup_values_vl to ' || p_schema_name;
96
97 -- Grant select priviliges on all of the pre-defined open-sql views (metadata open-sql views)
98 execute immediate 'grant select on ZPB_OS_ATTRIBUTES_V to ' || p_schema_name;
99 execute immediate 'grant select on ZPB_OS_BUSAREAS_V to ' || p_schema_name;
100 execute immediate 'grant select on ZPB_OS_DIMENSIONS_V to ' || p_schema_name;
101 execute immediate 'grant select on ZPB_OS_HIERARCHIES_V to ' || p_schema_name;
102 execute immediate 'grant select on ZPB_OS_LEVELS_V to ' || p_schema_name;
103 execute immediate 'grant select on ZPB_OS_MEASURES_V to ' || p_schema_name;
104 execute immediate 'grant select on ZPB_OS_MEAS_DIMS_V to ' || p_schema_name;
105 execute immediate 'grant select on ZPB_OS_TABLES_V to ' || p_schema_name;
106
107 -- Grant select priviliges on all the olap dimension and data views that have dynamically been created
108 -- during the life cycle of EPB. (data open-sql views)
109 for v_sharedView in c_sharedViews loop
110 begin
111 execute immediate 'grant select on ' || v_sharedView.table_name || ' to ' || p_schema_name;
112
113 -- zpb_log.write_statement(G_PKG_NAME || '.ENABLE', 'granted select on view '
114 -- || v_sharedView.table_name || ' to ' || p_schema_name);
115
116 -- in the un-planned case where metadata still exists for a view that has been delete,
117 -- skip the granting of select for the view and move on to the remaining views.
118 exception when others then
119 null;
120 end;
121 end loop;
122
123 -- zpb_log.write('zpb_opensql_pkg.enable','complete for schema ' ||
124 -- p_schema_name || ' and business area ' || to_char( p_business_area_id));
125
126 retcode := '0';
127 /*
128 EXCEPTION
129 WHEN OTHERS THEN
130 retcode := '2' ;
131 */
132 END ENABLE;
133
134 -- procedure that enables aw programs (OS.CREATE) to make ddl commands
135 procedure exec_ddl(p_cmd varchar2) is
136
137 begin
138 execute immediate p_cmd;
139 end exec_ddl;
140
141 end ZPB_OPENSQL_PKG;
142
[Home] [Help]