1 PACKAGE BODY pa_security_extn AS
2 /* $Header: PAPSECXB.pls 120.6.12020000.3 2013/03/06 09:45:05 admarath ship $ */
3
4 PROCEDURE check_project_access ( X_project_id IN NUMBER
5 , X_person_id IN NUMBER
6 , X_cross_project_user IN VARCHAR2
7 , X_calling_module IN VARCHAR2
8 , X_event IN VARCHAR2
9 , X_value OUT NOCOPY VARCHAR2
10 , X_cross_project_view IN VARCHAR2 := 'Y' )
11 IS
12 -- Declare local variables
13
14 X_project_num VARCHAR2(25);
15 X_tmp CHAR;
16
17 BEGIN
18
19 /*** Calling Modules *********************************************************
20
21 The pa_security_extn will be invoked from the following modules.
22 You can use the module name in this extension to control project access in
23 a specific module. The calling module parameter X_calling_module has the
24 following values.
25
26 FORMS:
27
28 Module Name User Name Description
29 --------- ----------- -----------
30 PAXBUEBU Budgets Enter Budgets
31 PAXCARVW Capital Projects Manage Capital project asset
32 capitalization
33 PAXINEAG Agreements Enter Agreements and Funding
34 PAXINEVT Events Maintenance Events Inquiry
35 PAXINRVW Invoices Review Invoices
36 PAXINVPF Project Funding Inquire on Project funding
37 Inquiry
38 PAXPREPR Projects Enter projects
39 PAXRVRVW Review Revenue Review Revenue
40 PAXTRAPE Expenditure Inquiry Inquire, Adjust Expenditure
41 PAXURDDC Project Status Display Define Project status display
42 Columns columns
43 PAXURVPS Project Status Inquiry Inquire on project status
44
45 Open Integration Toolkit :
46
47 OIT Budget creation and maintenance
48
49 Module Name
50 ------------
51 PA_PM_CREATE_DRAFT_BUDGET
52 PA_PM_ADD_BUDGET_LINE
53 PA_PM_BASELINE_BUDGET
54 PA_PM_DELETE_DRAFT_BUDGET
55 PA_PM_DELETE_BUDGET_LINE
56 PA_PM_UPDATE_BUDGET
57 PA_PM_UPDATE_BUDGET_LINE
58
59 OIT Project Maintenance
60
61 Module Name
62 ------------
63 PA_PM_ADD_TASK
64 PA_PM_UPDATE_PROJECT
65 PA_PM_UPDATE_TASK
66 PA_PM_DELETE_PROJECT
67
68 OIT Maintain Progess Data
69
70 Module Name
71 ------------
72 PA_PM_UPDATE_PROJ_PROGRESS
73 PA_PM_UPDATE_EARNED_VALUE
74
75 *******************************************************************************/
76
77 /****************** Example Security Code Begins *******************************
78
79 -- To use the following example code, please uncomment the code.
80 --
81 -- The example allows only users assigned to the same organization as the
82 -- project organization to have access to the project.
83 --
84 -- If required, the security check can be only for specific modules.
85 -- You change the IF condition to include or remove the module names.
86
87
88
89 IF X_calling_module = 'Module Name' THEN
90
91 BEGIN
92 IF (x_project_id IS NOT NULL) THEN -- Added the condition for bug 2853458
93 SELECT 'x'
94 INTO x_tmp
95 FROM pa_projects_all ppa , per_assignments_f paf
96 WHERE ppa.project_id = X_project_id
97 AND ppa.carrying_out_organization_id = paf.organization_id
98 AND paf.person_id = X_person_id
99 AND paf.assignment_type = 'E'
100 AND paf.primary_flag='Y' --Added for bug 291451
101 AND trunc(SYSDATE)
102 BETWEEN paf.effective_start_date AND paf.effective_end_date;
103 END IF;
104 EXCEPTION
105 WHEN NO_DATA_FOUND THEN
106 X_value := 'N';
107 RETURN;
108
109 END;
110
111 X_value := 'Y';
112 RETURN;
113
114 END IF;
115 ********* Example Code Ends Here ************************************************/
116
117
118 IF x_calling_module IN ('PAXTRAPE_GL_DRILLDOWN','PAXRVRVW_GL_DRILLDOWN',
119 'GL_DRILLDOWN_PA_COST', 'GL_DRILLDOWN_PA_REVENUE')
120 --AND x_event IN ('ALLOW_QUERY' , 'VIEW_LABOR_COSTS') --Bug 14032184
121 AND x_event IN ('ALLOW_QUERY') --Bug 14032184
122 THEN
123 X_value := 'Y';
124 RETURN;
125 END IF;
126
127 IF ( X_event = 'ALLOW_QUERY' ) THEN
128
129 -- Default processing is to only grant ALLOW_QUERY access to cross
130 -- project update users (done at beginning of procedure), cross project
131 -- view users, project authorities for the encompassing organization, and
132 -- active key members defined for the project.
133
134 -- PA provides an API to determine whether or not a given person is a
135 -- project authority on a specified project. This function,
136 -- CHECK_PROJECT_AUTHORITY is defined in the PA_SECURITY package. It takes
137 -- two input parameters, person_id and project_id, and returns as
138 -- output:
139 -- 'Y' if the person is a project authority for the project,
140 -- 'N' if the person is not.
141
142 -- Note, if NULL values are passed for either parameter, person or
143 -- project, then the function returns NULL.
144
145 -- PA provides an API to determine whether or not a given person is an
146 -- active key member on a specified project. This function,
147 -- CHECK_KEY_MEMBER is defined in the PA_SECURITY package. It takes
148 -- two input parameters, person_id and project_id, and returns as
149 -- output:
150 -- 'Y' if the person is an active key member for the project,
151 -- 'N' if the person is not.
152
153 -- Note, if NULL values are passed for either parameter, person or
154 -- project, then the function returns NULL.
155
156 -- You can change the default processing by adding your own rules
157 -- based on the project and user attributes passed into this procedure.
158
159 IF X_cross_project_view = 'Y' THEN
160 X_value := 'Y';
161 RETURN;
162 END IF;
163
164 /*Enhancement 6519194 changes begin here*/
165 /* IF X_calling_module = 'PA_FORECASTING' THEN
166 IF pa_security.check_key_member( X_person_id, X_project_id ) = 'Y' THEN
167 X_value := 'Y';
168 RETURN;
169 END IF;
170
171 X_value := pa_security.check_forecast_authority( X_person_id, X_project_id );
172 ELSE
173 */
174 IF pa_security.check_key_member_no_dates( X_person_id, X_project_id ) = 'Y' THEN
175 X_value := 'Y';
176 RETURN;
177 END IF;
178
179 X_value := pa_security.check_project_authority( X_person_id, X_project_id );
180 /* END IF;*/ --Enhancement 6519194 changes end here.
181
182 RETURN;
183
184 ELSIF ( X_event = 'ALLOW_UPDATE' ) THEN
185
186
187 -- Default processing is to only grant ALLOW_QUERY access to cross
188 -- project update users (done at beginning of procedure), project authorities
189 -- for the encompassing organization, and active key members defined for the
190 -- project.
191
192 IF X_cross_project_user = 'Y' THEN
193 X_value := 'Y';
194 RETURN;
195 END IF;
196
197 IF pa_security.check_key_member( X_person_id, X_project_id ) = 'Y' THEN
198 X_value := 'Y';
199 RETURN;
200 END IF;
201
202 X_value := pa_security.check_project_authority( X_person_id, X_project_id );
203 RETURN;
204
205 RETURN;
206
207 ELSIF ( X_event = 'VIEW_LABOR_COSTS' ) THEN
208
209 -- Default validation in PA to determine if a user has privileges to
210 -- view labor cost amounts for expenditure item details is to ensure
211 -- that the person is an active key member for the project, and that
212 -- the user's project role type for that assignment is one that allows
213 -- query access to labor cost amounts.
214
215 -- PA provides an API to determine whether or not a given person
216 -- has VIEW_LABOR_COSTS access for a given project based on the above
217 -- criteria. This function, CHECK_LABOR_COST_ACCESS is defined in
218 -- the PA_SECURITY package. It takes two input parameters, person_id
219 -- and project_id, and returns as output:
220 -- 'Y' if the person has access to view labor costs
221 -- 'N' if the person does not.
222
223 -- Note, if NULL values are passed for either parameter, person or
224 -- project, then the function returns NULL.
225
226 IF X_cross_project_user = 'Y' THEN
227 X_value := 'Y';
228 RETURN;
229 END IF;
230
231 X_value := pa_security.check_labor_cost_access( X_person_id
232 , X_project_id );
233 RETURN;
234
235 END IF;
236
237 EXCEPTION
238 WHEN OTHERS THEN
239 X_Value := 'N';
240 Raise;
241
242 END check_project_access;
243
244
245
246 /* Added for Bug 8306009 */
247
248 FUNCTION custom_project_access ( X_mode IN VARCHAR2
249 , X_project_id IN NUMBER
250 , X_person_id IN NUMBER) RETURN VARCHAR2
251 IS
252 -- Declare local variables
253
254 x_tmp VARCHAR2(1);
255
256 BEGIN
257
258 -- This function is called with X_mode = 'CHECK_IF_CUSTOMIZED' to check
259 -- if the client extension is being used to customize accessibility to
260 -- projects on the project search pages.
261 -- If custom code written in this api is to be considered, the return value
262 -- should be set to 'Y' in the block below. If the return value is set to 'N',
263 -- the custom code is not considered.
264
265 IF (X_mode = 'CHECK_IF_CUSTOMIZED') THEN
266 RETURN 'N';
267 END IF;
268
269
270 -- This function is called with X_mode = 'CHECK_IF_ACCESSIBLE' to check
271 -- if the particular project can be accessed on the project search pages.
272 -- If the project should be accessible, the return value should be 'Y'
273 -- in the block below. If the return value is 'N', the project cannot
274 -- be accessed. If the default value 'D' is returned, the default security
275 -- checks will be done to determine the project's accessibility.
276
277 IF (X_mode = 'CHECK_IF_ACCESSIBLE') THEN
278
279 /****************** Example Security Code Begins *******************************
280
281 -- To use the following example code, please uncomment the code.
282 --
283 -- The example does not allow users assigned to an organization different from
284 -- the project organization to access the project on the project search
285 -- pages.
286
287 BEGIN
288 IF (X_project_id IS NOT NULL) THEN
289 SELECT 'x'
290 INTO x_tmp
291 FROM pa_projects_all ppa , per_assignments_f paf
292 WHERE ppa.project_id = X_project_id
293 AND ppa.carrying_out_organization_id = paf.organization_id
294 AND paf.person_id = X_person_id
295 AND paf.assignment_type = 'E'
296 AND paf.primary_flag='Y'
297 AND trunc(SYSDATE)
298 BETWEEN paf.effective_start_date AND paf.effective_end_date;
299 END IF;
300 EXCEPTION
301 WHEN NO_DATA_FOUND THEN
302 RETURN 'N';
303 END;
304
305 RETURN 'D';
306
307 ********* Example Code Ends Here ************************************************/
308
309 RETURN 'D';
310
311 END IF;
312
313 END custom_project_access;
314
315 END pa_security_extn;
[Home] [Help]