1 PACKAGE BODY pa_security_extn AS
2 /* $Header: PAPSECXB.pls 120.5 2007/10/24 04:24:31 rballamu 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')
121 THEN
122 X_value := 'Y';
123 RETURN;
124 END IF;
125
126 IF ( X_event = 'ALLOW_QUERY' ) THEN
127
128 -- Default processing is to only grant ALLOW_QUERY access to cross
129 -- project update users (done at beginning of procedure), cross project
130 -- view users, project authorities for the encompassing organization, and
131 -- active key members defined for the project.
132
133 -- PA provides an API to determine whether or not a given person is a
134 -- project authority on a specified project. This function,
135 -- CHECK_PROJECT_AUTHORITY is defined in the PA_SECURITY package. It takes
136 -- two input parameters, person_id and project_id, and returns as
137 -- output:
138 -- 'Y' if the person is a project authority for the project,
139 -- 'N' if the person is not.
140
141 -- Note, if NULL values are passed for either parameter, person or
142 -- project, then the function returns NULL.
143
144 -- PA provides an API to determine whether or not a given person is an
145 -- active key member on a specified project. This function,
146 -- CHECK_KEY_MEMBER is defined in the PA_SECURITY package. It takes
147 -- two input parameters, person_id and project_id, and returns as
148 -- output:
149 -- 'Y' if the person is an active key member for the project,
150 -- 'N' if the person is not.
151
152 -- Note, if NULL values are passed for either parameter, person or
153 -- project, then the function returns NULL.
154
155 -- You can change the default processing by adding your own rules
156 -- based on the project and user attributes passed into this procedure.
157
158 IF X_cross_project_view = 'Y' THEN
159 X_value := 'Y';
160 RETURN;
161 END IF;
162
163 /*Enhancement 6519194 changes begin here*/
164 /* IF X_calling_module = 'PA_FORECASTING' THEN
165 IF pa_security.check_key_member( X_person_id, X_project_id ) = 'Y' THEN
166 X_value := 'Y';
167 RETURN;
168 END IF;
169
170 X_value := pa_security.check_forecast_authority( X_person_id, X_project_id );
171 ELSE
172 */
173 IF pa_security.check_key_member_no_dates( X_person_id, X_project_id ) = 'Y' THEN
174 X_value := 'Y';
175 RETURN;
176 END IF;
177
178 X_value := pa_security.check_project_authority( X_person_id, X_project_id );
179 /* END IF;*/ --Enhancement 6519194 changes end here.
180
181 RETURN;
182
183 ELSIF ( X_event = 'ALLOW_UPDATE' ) THEN
184
185
186 -- Default processing is to only grant ALLOW_QUERY access to cross
187 -- project update users (done at beginning of procedure), project authorities
188 -- for the encompassing organization, and active key members defined for the
189 -- project.
190
191 IF X_cross_project_user = 'Y' THEN
192 X_value := 'Y';
193 RETURN;
194 END IF;
195
196 IF pa_security.check_key_member( X_person_id, X_project_id ) = 'Y' THEN
197 X_value := 'Y';
198 RETURN;
199 END IF;
200
201 X_value := pa_security.check_project_authority( X_person_id, X_project_id );
202 RETURN;
203
204 RETURN;
205
206 ELSIF ( X_event = 'VIEW_LABOR_COSTS' ) THEN
207
208 -- Default validation in PA to determine if a user has privileges to
209 -- view labor cost amounts for expenditure item details is to ensure
210 -- that the person is an active key member for the project, and that
211 -- the user's project role type for that assignment is one that allows
212 -- query access to labor cost amounts.
213
214 -- PA provides an API to determine whether or not a given person
215 -- has VIEW_LABOR_COSTS access for a given project based on the above
216 -- criteria. This function, CHECK_LABOR_COST_ACCESS is defined in
217 -- the PA_SECURITY package. It takes two input parameters, person_id
218 -- and project_id, and returns as output:
219 -- 'Y' if the person has access to view labor costs
220 -- 'N' if the person does not.
221
222 -- Note, if NULL values are passed for either parameter, person or
223 -- project, then the function returns NULL.
224
225 IF X_cross_project_user = 'Y' THEN
226 X_value := 'Y';
227 RETURN;
228 END IF;
229
230 X_value := pa_security.check_labor_cost_access( X_person_id
231 , X_project_id );
232 RETURN;
233
234 END IF;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 X_Value := 'N';
239 Raise;
240
241 END check_project_access;
242
243 END pa_security_extn;