1 PACKAGE BODY PA_PM_CONTROLS AS
2 /* $Header: PAPMCONB.pls 120.5 2008/08/13 10:41:38 rtgeorge ship $ */
3
4 Procedure Action_Allowed (p_action IN VARCHAR2,
5 p_pm_product_code IN VARCHAR2,
6 p_field_value_code IN VARCHAR2 DEFAULT NULL,
7 p_action_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
8 p_error_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
9 p_error_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
10 p_error_stage IN OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
11
12 -- This procedure checks the pa_pm_control_rules table to determine
13 -- whether a given action is allowed to be performed in Oracle Projects
14 -- The rules are set up , for each project management product
15 -- by system administrators . If an active record is found for the given
16 -- product code and action ,then the procedure returns p_action_allowed
17 -- as 'N', else it returns 'Y'
18 -- The procedure is called by various Oracle Projects forms to determine
19 -- whether an action can be performed on a record that has been imported
20 -- from an external project management system
21
22 l_old_stack varchar2(630);
23 l_field_value_allowed_flag VARCHAR2(1);
24 l_field_value_code VARCHAR2(30);
25 l_dummy VARCHAR2(1);
26
27 CURSOR l_control_actions_csr IS
28 SELECT NVL(field_value_allowed_flag,'N')
29 FROM pa_pm_control_actions
30 WHERE action = p_action;
31
32
33 CURSOR l_control_rules_csr IS
34 Select 'x'
35 FROM pa_pm_product_control_rules pc,
36 pa_pm_control_actions pa
37 WHERE pa.action = p_action
38 AND pa.control_rule_id = pc.control_rule_id
39 AND pc.pm_product_code = p_pm_product_code
40 AND NVL(l_field_value_code,'N') = NVL(pc.field_value_code,'N')
41 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
42 AND TRUNC(NVL(end_date_active,SYSDATE));
43
44 BEGIN
45 l_old_stack := p_error_stack;
46 p_error_code := 0;
47 p_error_stack := p_error_stack ||
48 '->PA_PM_CONTROLS.Action_Allowed';
49 IF p_action IS NULL THEN
50 p_error_code := 10;
51 p_error_stage := 'PA_PM_ACTION_NAME_REQD';
52 RETURN;
53 END IF ;
54
55 IF p_pm_product_code IS NULL THEN
56 p_error_code := 11;
57 p_error_stage := 'PA_PM_PRODUCT_CODE_REQD';
58 RETURN;
59 END IF ;
60 p_error_stage := 'Select nvl(field_value_allowed_flag,N) from '||
61 'pa_pm_control_actions';
62
63 -- Check whether the passed action is a valid one and get the
64 -- field_value_allowed_flag
65
66 OPEN l_control_actions_csr;
67 FETCH l_control_actions_csr INTO l_field_value_allowed_flag;
68 IF l_control_actions_csr%NOTFOUND THEN
69 p_error_code := 12;
70 p_error_stage := 'PA_PM_ACTION_NAME_INVALID';
71 CLOSE l_control_actions_csr;
72 RETURN;
73 ELSE
74 CLOSE l_control_actions_csr;
75 END IF;
76
77 -- If the field_value_allowed_flag is 'N' ,then ignore whatever is
78 -- passed for p_field_value_code
79
80 IF l_field_value_allowed_flag = 'N' THEN
81 l_field_value_code := NULL;
82 ELSE
83 l_field_value_code := p_field_value_code;
84 END IF;
85
86 p_error_stage :=
87 'Select x from pa_pm_product_control_rules,pa_pm_control_actions';
88
89 -- If a record is found , then return 'N' else return 'Y'
90
91 OPEN l_control_rules_csr;
92 FETCH l_control_rules_csr INTO l_dummy;
93 IF l_control_rules_csr%NOTFOUND THEN
94 p_action_allowed := 'Y';
95 ELSE
96 p_action_allowed := 'N';
97 END IF;
98 CLOSE l_control_rules_csr;
99
100 -- Restore the old stack
101
102 p_error_stack := l_old_stack;
103
104 EXCEPTION
105
106 WHEN OTHERS THEN
107 p_error_code := SQLCODE;
108 -- 4537865 RESET Other OUT PARAMS also
109 p_error_stack := p_error_stack || '->' || SUBSTRB(SQLERRM,1,100);
110 p_action_allowed := 'N';
111 -- p_error_stage should not be reset
112
113 END Action_Allowed;
114
115 Procedure Get_Project_Actions_Allowed (
116 p_pm_product_code IN VARCHAR2,
117 p_delete_project_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
118 p_update_proj_num_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
119 p_update_proj_name_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
120 p_update_proj_desc_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
121 p_update_proj_dates_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
122 p_update_proj_status_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
123 p_update_proj_manager_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
124 p_update_proj_org_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
125 p_add_task_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
126 p_delete_task_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
127 p_update_task_num_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
128 p_update_task_name_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
129 p_update_task_dates_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
130 p_update_task_desc_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
131 p_update_parent_task_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
132 p_update_task_org_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
133 p_error_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
134 p_error_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
135 p_error_stage IN OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
136 IS
137 -- This is a specific API being called by the Projects form
138 -- It returns 16 flags pertaining to project and tasks
139 -- indicating whether the action is allowed to be performed in Oracle Projects
140 -- The rules are set up , for each project management product
141 -- by system administrators . If an active record is found for the given
142 -- product code and action ,then the procedure returns the relevant flag
143 -- as 'N', else it returns 'Y'
144 -- The procedure is called only by the Projects form to determine
145 -- whether such actions can be performed on a project that has been imported
146 -- from an external project management system
147
148 l_old_stack varchar2(630);
149 TYPE actiontabtype IS TABLE OF pa_pm_control_actions.action%TYPE
150 INDEX BY BINARY_INTEGER;
151 l_action actiontabtype;
152 l_dummy VARCHAR2(1);
153
154 BEGIN
155 l_old_stack := p_error_stack;
156 p_error_code := 0;
157 p_error_stack := p_error_stack ||
158 '->PA_PM_CONTROLS.Get_Project_Actions_Allowed';
159
160 p_delete_project_allowed := 'Y';
161 p_update_proj_num_allowed := 'Y';
162 p_update_proj_name_allowed := 'Y';
163 p_update_proj_desc_allowed := 'Y';
164 p_update_proj_dates_allowed := 'Y';
165 p_update_proj_status_allowed := 'Y';
166 p_update_proj_manager_allowed := 'Y';
167 p_update_proj_org_allowed := 'Y';
168 p_add_task_allowed := 'Y';
169 p_delete_task_allowed := 'Y';
170 p_update_task_num_allowed := 'Y';
171 p_update_task_name_allowed := 'Y';
172 p_update_task_dates_allowed := 'Y';
173 p_update_task_desc_allowed := 'Y';
174 p_update_parent_task_allowed := 'Y';
175 p_update_task_org_allowed := 'Y';
176
177 l_action(1) := 'DELETE_PROJECT';
178 l_action(2) := 'UPDATE_PROJECT_NUMBER';
179 l_action(3) := 'UPDATE_PROJECT_NAME';
180 l_action(4) := 'UPDATE_PROJECT_DESCRIPTION';
181 l_action(5) := 'UPDATE_PROJECT_DATES';
182 l_action(6) := 'UPDATE_PROJECT_STATUS';
183 l_action(7) := 'UPDATE_PROJECT_MANAGER';
184 l_action(8) := 'UPDATE_PROJECT_ORGANIZATION';
185 l_action(9) := 'ADD_TASK';
186 l_action(10) := 'DELETE_TASK';
187 l_action(11) := 'UPDATE_TASK_NUMBER';
188 l_action(12) := 'UPDATE_TASK_NAME';
189 l_action(13) := 'UPDATE_TASK_DATES';
190 l_action(14) := 'UPDATE_TASK_DESCRIPTION';
191 l_action(15) := 'UPDATE_PARENT_TASK';
192 l_action(16) := 'UPDATE_TASK_ORGANIZATION';
193
194 FOR i IN 1..16 LOOP
195
196 BEGIN
197 p_error_stage :=
198 'Select x from pa_pm_product_control_rules,pa_pm_control_actions';
199
200 SELECT 'x' INTO l_dummy
201 FROM pa_pm_product_control_rules pc,
202 pa_pm_control_actions pa
203 WHERE pa.action = l_action(i)
204 AND pa.control_rule_id = pc.control_rule_id
205 AND pc.pm_product_code = p_pm_product_code
206 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
207 AND TRUNC(NVL(end_date_active,SYSDATE));
208
209 IF i = 1 THEN
210 p_delete_project_allowed := 'N';
211 ELSIF i = 2 THEN
212 p_update_proj_num_allowed := 'N';
213 ELSIF i = 3 THEN
214 p_update_proj_name_allowed := 'N';
215 ELSIF i = 4 THEN
216 p_update_proj_desc_allowed := 'N';
217 ELSIF i = 5 THEN
218 p_update_proj_dates_allowed := 'N';
219 ELSIF i = 6 THEN
220 p_update_proj_status_allowed := 'N';
221 ELSIF i = 7 THEN
222 p_update_proj_manager_allowed := 'N';
223 ELSIF i = 8 THEN
224 p_update_proj_org_allowed := 'N';
225 ELSIF i = 9 THEN
226 p_add_task_allowed := 'N';
227 ELSIF i = 10 THEN
228 p_delete_task_allowed := 'N';
229 ELSIF i = 11 THEN
230 p_update_task_num_allowed := 'N';
231 ELSIF i = 12 THEN
232 p_update_task_name_allowed := 'N';
233 ELSIF i = 13 THEN
234 p_update_task_dates_allowed := 'N';
235 ELSIF i = 14 THEN
236 p_update_task_desc_allowed := 'N';
237 ELSIF i = 15 THEN
238 p_update_parent_task_allowed := 'N';
239 ELSIF i = 16 THEN
240 p_update_task_org_allowed := 'N';
241 END IF;
242
243 EXCEPTION
244 WHEN NO_DATA_FOUND THEN
245 NULL;
246 WHEN OTHERS THEN
247 p_error_code := SQLCODE;
248 END ;
249
250 END LOOP;
251
252 -- Restore the old stack
253
254 p_error_stack := l_old_stack;
255
256 EXCEPTION
257
258 WHEN OTHERS THEN
259 p_error_code := SQLCODE;
260 -- 4537865 : RESET OTHER OUT PARAMS Also.
261 p_delete_project_allowed := 'N' ;
262 p_update_proj_num_allowed := 'N' ;
263 p_update_proj_name_allowed := 'N' ;
264 p_update_proj_desc_allowed := 'N' ;
265 p_update_proj_dates_allowed := 'N' ;
266 p_update_proj_status_allowed := 'N' ;
267 p_update_proj_manager_allowed := 'N' ;
268 p_update_proj_org_allowed := 'N' ;
269 p_add_task_allowed := 'N' ;
270 p_delete_task_allowed := 'N' ;
271 p_update_task_num_allowed := 'N' ;
272 p_update_task_name_allowed := 'N' ;
273 p_update_task_dates_allowed := 'N' ;
274 p_update_task_desc_allowed := 'N' ;
275 p_update_parent_task_allowed := 'N' ;
276 p_update_task_org_allowed := 'N' ;
277
278 p_error_stack := p_error_stack || '->' || SUBSTRB(SQLERRM,1,100) ;
279 -- Should not reset p_error_stage
280 END Get_Project_Actions_Allowed;
281
282
283 Procedure Get_Billing_Actions_Allowed (
284 p_pm_product_code IN VARCHAR2,
285 p_update_agreement_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
286 p_delete_agreement_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
287 p_add_funding_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
288 p_update_funding_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
289 p_delete_funding_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
290 p_error_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
291 p_error_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
292 p_error_stage IN OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
293 IS
294 /* *****************************************************************************
295 -- This is a specific API being called by the Agreement/Funding Form
296 -- It returns 5 flags pertaining to Agreements and Fundings
297 -- indicating whether the action is allowed to be performed in Oracle Projects
298 -- The rules are set up , for each project management product
299 -- by system administrators . If an active record is found for the given
300 -- product code and action ,then the procedure returns the relevant flag
301 -- as 'N', else it returns 'Y'
302 -- The procedure is called only by the Projects form to determine
303 -- whether such actions can be performed on a project that has been imported
304 -- from an external project management system
305 ***************************************************************************** */
306
307 l_old_stack varchar2(630);
308 TYPE actiontabtype IS TABLE OF pa_pm_control_actions.action%TYPE
309 INDEX BY BINARY_INTEGER;
310 l_action actiontabtype;
311 l_dummy VARCHAR2(1);
312
313 BEGIN
314 l_old_stack := p_error_stack;
315 p_error_code := 0;
316 p_error_stack := p_error_stack ||
317 '->PA_PM_CONTROLS.Get_Billing_Actions_Allowed';
318
319 p_update_agreement_allowed := 'Y';
320 p_delete_agreement_allowed := 'Y';
321 p_add_funding_allowed := 'Y';
322 p_update_funding_allowed := 'Y';
323 p_delete_funding_allowed := 'Y';
324
325 l_action(1) := 'UPDATE_AGREEMENT';
326 l_action(2) := 'DELETE_AGREEMENT';
327 l_action(3) := 'ADD_FUNDING';
328 l_action(4) := 'UPDATE_FUNDING';
329 l_action(5) := 'DELETE_FUNDING';
330
331 FOR i IN 1..5 LOOP
332
333 BEGIN
334 p_error_stage :=
335 'Select x from pa_pm_product_control_rules,pa_pm_control_actions';
336
337 SELECT 'x' INTO l_dummy
338 FROM pa_pm_product_control_rules pc,
339 pa_pm_control_actions pa
340 WHERE pa.action = l_action(i)
341 AND pa.control_rule_id = pc.control_rule_id
342 AND pc.pm_product_code = p_pm_product_code
343 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
344 AND TRUNC(NVL(end_date_active,SYSDATE));
345
346 IF i = 1 THEN
347 p_update_agreement_allowed := 'N';
348 ELSIF i = 2 THEN
349 p_delete_agreement_allowed := 'N';
350 ELSIF i = 3 THEN
351 p_add_funding_allowed := 'N';
355 p_delete_funding_allowed := 'N';
352 ELSIF i = 4 THEN
353 p_update_funding_allowed := 'N';
354 ELSIF i = 5 THEN
356 END IF;
357
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 NULL;
361 WHEN OTHERS THEN
362 p_error_code := SQLCODE;
363 END ;
364
365 END LOOP;
366
367 -- Restore the old stack
368
369 p_error_stack := l_old_stack;
370
371 EXCEPTION
372
373 WHEN OTHERS THEN
374 p_error_code := SQLCODE;
375 -- 4537865 : RESET other out params too.
376 p_update_agreement_allowed := 'N' ;
377 p_delete_agreement_allowed := 'N' ;
378 p_add_funding_allowed := 'N' ;
379 p_update_funding_allowed := 'N' ;
380 p_delete_funding_allowed := 'N' ;
381 p_error_stack := p_error_stack || '->' || SUBSTRB(SQLERRM,1,100);
382 END Get_Billing_Actions_Allowed;
383
384 /* ***********************************************************************************************
385 --This is a specific procedure called from event form.
386 --It returns two flags depending on whethere an event
387 --that originated from an external system can be updated
388 --or not and whether these events can be deleted.
389 ************************************************************************************************* */
390 PROCEDURE GET_EVENT_ACTIONS_ALLOWED
391 (P_PM_PRODUCT_CODE IN VARCHAR2,
392 p_update_Event_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
393 p_delete_Event_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
394 p_update_event_bill_hold OUT NOCOPY VARCHAR2, /* added for bug 6870421*/
395 P_ERROR_CODE OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
396 P_ERROR_STACK IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
397 P_ERROR_STAGE IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
398 IS
399
400 TYPE actiontabtype IS TABLE OF pa_pm_control_actions.action%TYPE INDEX BY BINARY_INTEGER;
401 l_action actiontabtype;
402 l_dummy VARCHAR2(1);
403
404 Begin
405 P_update_event_allowed :='Y';
406 P_delete_event_allowed := 'Y';
407 p_update_event_bill_hold :='Y'; /* added for bug 6870421*/
408 l_action(1) := 'UPDATE_EVENT';
409 l_action(2) := 'DELETE_EVENT';
410 l_action(3) := 'UPDATE_EVENT_BILL_HOLD'; /* added for bug 6870421*/
411 --4537865
412 P_ERROR_STAGE := P_ERROR_STAGE || '-> Inside GET_EVENT_ACTIONS_ALLOWED ' ;
413 FOR I IN 1..3 LOOP
414 BEGIN
415 P_ERROR_STAGE := 'SELECT x INTO l_dummy FROM pa_pm_product_control_rules pc,pa_pm_control_actions pa' ; --4537865
416
417 SELECT 'x' INTO l_dummy
418 FROM pa_pm_product_control_rules pc,
419 pa_pm_control_actions pa
420 WHERE pa.action = l_action(i)
421 AND pa.control_rule_id = pc.control_rule_id
422 AND pc.pm_product_code = p_pm_product_code
423 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
424 AND TRUNC(NVL(end_date_active,SYSDATE));
425
426 IF i = 1 THEN
427 p_update_event_allowed := 'N';
428 ELSIF i = 2 THEN
429 p_delete_event_allowed := 'N';
430 ELSIF i = 3 THEN /* added for bug 6870421*/
431 p_update_event_bill_hold := 'N';
432 END IF;
433 EXCEPTION WHEN NO_DATA_FOUND THEN
434 NULL;
435 END;
436 END LOOP;
437 -- 4537865
438 EXCEPTION
439 WHEN OTHERS THEN
440 P_ERROR_CODE := SQLCODE ;
441 p_update_event_allowed := 'N';
442 p_delete_event_allowed := 'N';
443 P_ERROR_STACK := P_ERROR_STACK || '->' || SUBSTRB(SQLERRM,1,100);
444
445 END GET_EVENT_ACTIONS_ALLOWED;
446 END PA_PM_CONTROLS;
[Home] [Help]