1 Package Body pqh_prvcalc as
2 /* $Header: pqprvcal.pkb 120.1 2005/07/05 17:13:19 hsajja noship $ */
3 -- global package variable for the purpose of hr_utility.
4
5 g_package varchar2(2000) := 'pqh_prvcalc.';
6
7
8 --
9 -- ----------------------------------------------------------------------------
10 -- |------------------------< list_attribute_privs >----------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 -- to be used for debugging results with trace on
14 --
15 procedure list_attribute_privs is
16 begin
17 for i in g_result.first .. g_result.last loop
18 hr_utility.set_location('form_col :'||g_result(i).form_column_name||' Mod : '||g_result(i).mode_flag||' Req :'||g_result(i).reqd_flag,10);
19 end loop;
20 end;
21 --
22 -- ----------------------------------------------------------------------------
23 -- |------------------------< task_task_reqd_comp_flag >------------------------|
24 -- ----------------------------------------------------------------------------
25 --
26
27 procedure task_task_reqd_comp_flag (p_task1_reqd_flag in varchar2,
28 p_task2_reqd_flag in varchar2,
29 p_result_reqd_flag out nocopy varchar )
30 as
31 l_proc varchar2(2000) := g_package||'task_task_reqd_comp_flag' ;
32 begin
33 hr_utility.set_location('Entering'||l_proc,10);
34 if p_task1_reqd_flag = 'Y' or p_task2_reqd_flag ='Y' then
35 p_result_reqd_flag := 'Y' ;
36 else
37 p_result_reqd_flag := 'N' ;
38 end if;
39 hr_utility.set_location('Exiting'||l_proc,10000);
40 exception when others then
41 p_result_reqd_flag := null;
42 raise;
43 end task_task_reqd_comp_flag;
44
45 --
46 -- ----------------------------------------------------------------------------
47 -- |------------------------< attribute_flag_result >------------------------|
48 -- ----------------------------------------------------------------------------
49 --
50 procedure attribute_flag_result (p_edit_flag in varchar2,
51 p_view_flag in varchar2,
52 p_result_flag out nocopy varchar2 )
53 as
54 l_proc varchar2(2000) := g_package||'attribute_flag_result' ;
55 begin
56 -- hr_utility.set_location('Entering'||l_proc,10);
57 /*
58 This procedure is used for calculating the result flag based on the
59 values of the edit flag and the view flag which are taken as input.
60 If edit flag is Yes then result is E irrespective of the value of the View flag.
61 if edit flag is No then view flag determines the value of the result flag.
62 if view flag is yes and edit flag is no then result flag is V else result flag is N.
63 */
64 if p_edit_flag = 'Y' then
65 p_result_flag := 'E';
66 elsif p_view_flag = 'Y' then
67 p_result_flag := 'V' ;
68 else
69 p_result_flag := 'N' ;
70 end if;
71 hr_utility.set_location('edit'||p_edit_flag||' view '||p_view_flag||' result '||p_result_flag||l_proc,20);
72 -- hr_utility.set_location('Exiting'||l_proc,10000);
73 exception when others then
74 p_result_flag := null;
75 raise;
76 end attribute_flag_result;
77
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------------------------< task_task_mode_comp_flag >------------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 procedure task_task_mode_comp_flag(p_task1_flag in varchar2,
84 p_task2_flag in varchar2,
85 p_result_flag out nocopy varchar2 )
86 as
87 l_proc varchar2(2000) := g_package||'task_task_mode_comp_flag' ;
88 begin
89 -- hr_utility.set_location('Entering'||l_proc,10);
90 /*
91 This procedure is used for calculating result of the comparison of the
92 flags associated with an attribute of a task.
93 Task1 task2 are the input parameters . Flags can have possible values
94 (E,V,N ) listed in the order of heirarchy . In the task flag comparison ,
95 higher flag is to be returned as result. if any of the flag is E then
96 result is E which is the highest value for the flag. which represents
97 that the attribute is editable for the task. if both the flags are N
98 then result is N which means that attribute is not even viewable for
99 the task.
100 */
101 if p_task1_flag ='E' or p_task2_flag = 'E' then
102 p_result_flag := 'E' ;
103 elsif p_task1_flag = 'V' or p_task2_flag = 'V' then
104 p_result_flag := 'V' ;
105 else
106 p_result_flag := 'N' ;
107 end if;
108 hr_utility.set_location('task1'||p_task1_flag||' task2 '||p_task2_flag||' result '||p_result_flag||l_proc,20 );
109 -- hr_utility.set_location('Exiting'||l_proc,10000);
110 exception when others then
111 p_result_flag := null;
112 raise;
113 end task_task_mode_comp_flag;
114
115 --
116 -- ----------------------------------------------------------------------------
117 -- |------------------------< domain_task_mode_comp_flag >------------------------|
118 -- ----------------------------------------------------------------------------
119 --
120 procedure domain_task_mode_comp_flag(p_domain_mode_flag in varchar2,
121 p_task_mode_flag in varchar2,
122 p_result_flag out nocopy varchar2 )
123 as
124 l_proc varchar2(2000) := g_package||'domain_task_mode_comp_flag' ;
125 begin
126 -- hr_utility.set_location('Entering'||l_proc,10);
127 /*
128 This procedure is used for calculating the result of comparison of
129 domain flag and task flag associated with the attribute. Flag can have
130 values (E,V,N) . Minimum of the two flags is to be calculated as result.
131 */
132 if p_domain_mode_flag = 'E' then
133 if p_task_mode_flag = 'E' then
134 p_result_flag := 'E' ;
135 elsif p_task_mode_flag ='V' then
136 p_result_flag := 'V' ;
137 else
138 p_result_flag := 'N' ;
139 end if;
140 elsif p_domain_mode_flag = 'V' then
141 if p_task_mode_flag ='N' then
142 p_result_flag := 'N' ;
143 else
144 p_result_flag := 'V' ;
145 end if;
146 else
147 p_result_flag := 'N' ;
148 end if;
149 hr_utility.set_location('domain'||p_domain_mode_flag||' task '||p_task_mode_flag||' result '||p_result_flag||l_proc,20 );
150 -- hr_utility.set_location('Exiting'||l_proc,10000);
151 exception when others then
152 p_result_flag := null;
153 raise;
154 end domain_task_mode_comp_flag;
155
156 --
157 -- ----------------------------------------------------------------------------
158 -- |------------------------< domain_result_calc >------------------------|
159 -- ----------------------------------------------------------------------------
160 --
161 procedure domain_result_calc (p_domain in pqh_template_attributes.template_id%type,
162 p_result out nocopy t_attid_priv)
163 as
164 l_count number := 1;
165 l_result_flag varchar2(1) ;
166 l_result t_attid_priv;
167 cursor c1(l_template_id number) is
168 select edit_flag,view_flag,attribute_id
169 from pqh_template_attributes
170 where template_id = l_template_id;
171 l_proc varchar2(2000) := g_package||'domain_result_calc' ;
172 begin
173 hr_utility.set_location('Entering'||l_proc,10);
174 /*
175 This procedure is used for calculating the attribute and result flag
176 associated to a domain. domain template id is taken as input parameter
177 and result is stored in the pl/sql table of records. Call to attribute
178 flag result is made to find out nocopy the result flag for each attribute.
179 */
180 l_count := 1 ;
181 for l_domain_rec in c1(p_domain) loop
182 -- hr_utility.set_location('# '||to_char(l_count)||' attribute '||to_char(l_domain_rec.attribute_id)||l_proc,20 );
183 attribute_flag_result(p_edit_flag => l_domain_rec.edit_flag,
184 p_view_flag => l_domain_rec.view_flag,
185 p_result_flag => l_result_flag);
186 p_result(l_count).attribute_id := l_domain_rec.attribute_id;
187 p_result(l_count).mode_flag := l_result_flag;
188 -- domain 's required flag is not considered for computing the required flag of attribute.
189 p_result(l_count).reqd_flag := '';
190 -- domain's task type is also not considered
191 p_result(l_count).task_type := '';
192 l_count := l_count + 1 ;
193 end loop;
194 hr_utility.set_location('Exiting'||l_proc,10000);
195 exception when others then
196 p_result := l_result;
197 raise;
198 end;
199
200 --
201 -- ----------------------------------------------------------------------------
202 -- |------------------------< task_result_update >------------------------|
203 -- ----------------------------------------------------------------------------
204 --
205 procedure task_result_update (p_task in pqh_template_attributes.template_id%type,
206 p_task_type in varchar2,
207 p_result_int in out nocopy t_attid_priv)
208 as
209 l_result_int t_attid_priv := p_result_int;
210 l_res_count number ;
211 l_result_flag varchar2(1);
212 l_reqd_flag varchar2(1);
213 l_ins_result varchar2(1);
214 l_task_last_count number ;
215 cursor c1(l_task_template_id number) is
216 select edit_flag,view_flag,attribute_id,required_flag
217 from pqh_template_attributes
218 where template_id = l_task_template_id;
219 l_proc varchar2(2000) := g_package||'task_result_update' ;
220 begin
221 -- hr_utility.set_location('Entering'||l_proc,10);
222 /*
223 This procedure is used to update internal result table which stores result
224 flags for the attributes associated with the list of tasks. Internal result
225 table and task template id are the input parameters and internal result
226 table after updation is passed back as result.
227 for the task, attributes their flags are fetched from the database. Result
228 flag is calculated using attribute flag result procedure.
229 Presence of the attribute is checked in the internal result table. if the
230 attribute is available in the internal result table then flag comparison
231 is made and result is stored back in internal result table. if no match is
232 made for the attribute then attribute is added in the internal result
233 table with the result flag.
234 */
235 hr_utility.set_location('task is'||p_task||l_proc,15 );
236 for i in c1(p_task) loop
237 -- hr_utility.set_location('Values task, attribute '||to_char(i.attribute_id)||'edit '||i.edit_flag||' view '||i.view_flag||l_proc,20 );
238 attribute_flag_result(p_edit_flag => i.edit_flag,
239 p_view_flag => i.view_flag,
240 p_result_flag => l_result_flag);
241 l_reqd_flag := substr(nvl(i.required_flag,'N'),1,1) ;
242 l_res_count := 1;
243 l_ins_result := 'Y';
244 l_task_last_count := p_result_int.count;
245 for l_res_count in 1..l_task_last_count loop
246 if p_result_int(l_res_count).attribute_id = i.attribute_id then
247 l_ins_result := 'N' ;
248 -- hr_utility.set_location('Table has'||p_result_int(l_res_count).task_type||' adding '||p_task_type||l_proc,30);
249 if p_result_int(l_res_count).task_type = 'T' then
250 if p_task_type ='T' then
251 -- max of the attribute flags is stored in the table
252 -- hr_utility.set_location('Attribute flags updated '||l_proc,40);
253 task_task_mode_comp_flag(p_task1_flag => p_result_int(l_res_count).mode_flag,
254 p_task2_flag => l_result_flag,
255 p_result_flag => p_result_int(l_res_count).mode_flag);
256 task_task_reqd_comp_flag(p_task1_reqd_flag => p_result_int(l_res_count).reqd_flag,
257 p_task2_reqd_flag => l_reqd_flag,
258 p_result_reqd_flag => p_result_int(l_res_count).reqd_flag);
259 -- else attribute flags of the reference template are not considered in comparison to task attribute flags
260 end if;
261 else
262 if p_task_type ='T' then
263 -- table stores Reference and compared with task , task attribute flags replae reference template attribute flags
264 p_result_int(l_res_count).mode_flag := l_result_flag;
265 p_result_int(l_res_count).reqd_flag := l_reqd_flag;
266 p_result_int(l_res_count).task_type := 'T';
267 else
268 -- max of the attribute flags is stored in the table
269 task_task_mode_comp_flag(p_task1_flag => p_result_int(l_res_count).mode_flag,
270 p_task2_flag => l_result_flag,
271 p_result_flag => p_result_int(l_res_count).mode_flag);
272 task_task_reqd_comp_flag(p_task1_reqd_flag => p_result_int(l_res_count).reqd_flag,
273 p_task2_reqd_flag => l_reqd_flag,
274 p_result_reqd_flag => p_result_int(l_res_count).reqd_flag);
275 end if;
276 end if;
277 end if;
278 end loop;
279 if l_ins_result ='Y' then
280 -- hr_utility.set_location('New attribute added '||l_proc,50);
281 l_res_count := l_task_last_count + 1;
282 p_result_int(l_res_count).attribute_id := i.attribute_id;
283 p_result_int(l_res_count).mode_flag := l_result_flag ;
284 p_result_int(l_res_count).reqd_flag := l_reqd_flag;
285 p_result_int(l_res_count).task_type := p_task_type;
286 end if;
287 end loop;
288 -- hr_utility.set_location('Exiting'||l_proc,10000);
289 exception when others then
290 p_result_int := l_result_int;
291 raise;
292 end task_result_update;
293
294 --
295 -- ----------------------------------------------------------------------------
296 -- |------------------------< task_references >------------------------|
297 -- ----------------------------------------------------------------------------
298 --
299
300 procedure task_references(p_task in pqh_template_attributes.template_id%type,
301 p_result_int in out nocopy t_attid_priv)
302 as
303
304 l_result_int t_attid_priv := p_result_int;
305 cursor c2(p_template_id number) is
306 select base_template_id
307 from pqh_ref_templates
308 where parent_template_id = p_template_id
309 and reference_type_cd = 'REFERENCE';
310
311 l_proc varchar2(2000) := g_package||'task_references' ;
312 begin
313 hr_utility.set_location('Entering'||l_proc,10);
314 /*
315 This procedure is used for finding out nocopy reference tasks associated with
316 each task and also taking into account their flag values and updating
317 those into the internal result table. for each reference task associated
318 with a task, call to task_result update is made.
319 */
320 for i in c2(p_task) loop
321 hr_utility.set_location('values fetched are attribute '||to_char(i.base_template_id)||l_proc,20 );
322 task_result_update(p_task => i.base_template_id,
323 p_task_type => 'R',
324 p_result_int => p_result_int);
325 end loop;
326 hr_utility.set_location('Exiting'||l_proc,10000);
327
328 exception when others then
329 p_result_int := l_result_int;
330 raise;
331 end task_references;
332
333 procedure check_priv_calc is
334 p_domain pqh_template_attributes.template_id%type;
335 p_tasks t_task_templ;
336 p_result t_attname_priv;
337 p_transaction_category_id number;
338 l_proc varchar2(256) := g_package||'check_priv_calc';
339 begin
340 p_domain := 1;
341 p_tasks(1) := 2;
342 priviledge_calc(p_domain => p_domain,
343 p_tasks => p_tasks,
344 p_transaction_category_id => p_transaction_category_id,
345 p_result => p_result);
346 for i in 1..p_result.last loop
350
347 hr_utility.set_location('att'||p_result(i).form_column_name||'mode'||p_result(i).mode_flag||'reqd'||p_result(i).reqd_flag||l_proc,200);
348 end loop;
349 end check_priv_calc;
351 procedure get_row_prv( p_row in number,
352 p_form_column_name out nocopy pqh_txn_category_attributes.form_column_name%type,
353 p_mode_flag out nocopy varchar2,
354 p_reqd_flag out nocopy varchar2)
355 is
356 l_proc varchar2(256) := g_package||'get_row_prv';
357 begin
358 p_form_column_name := g_result(p_row).form_column_name;
359 p_mode_flag := g_result(p_row).mode_flag;
360 p_reqd_flag := g_result(p_row).reqd_flag;
361 hr_utility.set_location('exiting'||l_proc,10000);
362 end get_row_prv;
363
364 procedure priviledge_calc_count (p_domain in pqh_template_attributes.template_id%type,
365 p_tasks in t_task_templ,
366 p_transaction_category_id in number,
367 p_result_count out nocopy number )
368 is
369 l_temp_flag varchar2(1);
370 l_task_count number := 1;
371 l_error_flag boolean := FALSE;
372 l_task_last_count number := 1;
373 l_domain_last_count number := 1;
374 l_att_count number := 1;
375 l_res_count number := 1;
376 l_int_res_count number := 1;
377 l_count number := 1;
378 l_task_att_count number ;
379 l_chg_result varchar2(1) := 'Y' ;
380 l_task_template_id pqh_template_attributes.template_id%type;
381 l_form_column_name pqh_txn_category_attributes.form_column_name%type;
382 l_result_flag varchar2(1) ;
383 l_result_task_int t_attid_priv;
384 l_result_domain_int t_attid_priv;
385 cursor c1(p_attribute_id number) is
386 select form_column_name
387 from pqh_txn_category_attributes
388 where attribute_id = p_attribute_id
389 and transaction_category_id = p_transaction_category_id;
390 l_proc varchar2(2000) := g_package||'priviledge_calc_count' ;
391 begin
392 hr_utility.set_location('Entering'||l_proc,10);
393 /*
394 This is the main procedure . It takes domain template id and an table of tasks as input
395 and passes the attributes and their flag values as result .
396 This procedure has three parts.
397 1) Internal result table is computed according to the tasks and their references attributes and flags.
398 2) Result table is computed according to the domain attributes and their flags.
399 3) Internal result table is applied on result table to take the minimum of the attribute flag
400 value and result is stored in the result table .
401 4) form_column name is fetched corresponding to the attribute_id from the database and flag values are
402 stored in the table and this table of records is passed as out nocopy parameter
403 */
404 /* Check the input data validity, if domain is null then raise error and don't process the whole procedure */
405
406 l_task_last_count := p_tasks.count;
407 if p_domain is null then
408 l_error_flag := TRUE;
409 hr_utility.set_location('domain reqd'||l_proc,20);
410 elsif l_task_last_count = 0 then
411 l_error_flag := TRUE;
412 hr_utility.set_location('tasks reqd'||l_proc,30);
413 end if;
414
415 -- Part1 starts here
416 /*
417 for each task in the task table internal result table is updated call to Task
418 references is made
419 */
420 if l_error_flag = FALSE then
421 for l_task_count in 1..l_task_last_count loop
422 l_task_template_id := p_tasks(l_task_count) ;
423 task_result_update(p_task => p_tasks(l_task_count),
424 p_task_type => 'T',
425 p_result_int => l_result_task_int) ;
426 task_references(p_task => p_tasks(l_task_count),
427 p_result_int => l_result_task_int) ;
428 end loop;
429 l_task_att_count := l_result_task_int.count;
430 for i in 1..l_task_att_count loop
431 hr_utility.set_location('att '||l_result_task_int(i).attribute_id||'mode '||l_result_task_int(i).mode_flag||'reqd '||l_result_task_int(i).reqd_flag||l_proc,35);
432 end loop;
433
434 -- Part2 : Computation of result table , corresponding to domain template
435 domain_result_calc(p_domain => p_domain,
436 p_result => l_result_domain_int);
437
438 -- Part3 starts here
439 /*
440 Applying internal result table on the result table to find out nocopy the minimum of the
441 flag value applicable to the attribute
442 Call to domain_task_comp flag is made for each comparison.
443 if an attribute is not there in the internal result table then result table is updated
444 for that attribute and flag is made N.
445 */
446 l_domain_last_count := l_result_domain_int.count;
447 l_res_count := 1;
448 for l_res_count in 1..l_domain_last_count loop
449 l_int_res_count := 1;
450 l_chg_result := 'Y' ;
451 l_task_last_count := l_result_task_int.count;
452 for l_int_res_count in 1..l_task_last_count loop
453 if l_result_domain_int(l_res_count).attribute_id = l_result_task_int(l_int_res_count).attribute_id then
454 l_chg_result := 'N' ;
455 hr_utility.set_location('attribute in task'||to_char(l_result_task_int(l_int_res_count).attribute_id)||l_proc,95);
456 -- mode flag is taken as minimum of domain and task privledges
460 l_result_domain_int(l_res_count).mode_flag := l_temp_flag;
457 domain_task_mode_comp_flag(p_domain_mode_flag => l_result_domain_int(l_res_count).mode_flag,
458 p_task_mode_flag => l_result_task_int(l_int_res_count).mode_flag,
459 p_result_flag => l_temp_flag);
461 -- while required flag is solely task priviledge
462 l_result_domain_int(l_res_count).reqd_flag := l_result_task_int(l_int_res_count).reqd_flag ;
463 end if;
464 end loop;
465 if l_chg_result = 'Y' then
466 -- selected tasks don't have priviledge on this attribute
467 l_result_domain_int(l_res_count).mode_flag := 'N' ;
468 l_result_domain_int(l_res_count).reqd_flag := 'N' ;
469 end if;
470 end loop;
471
472 -- Part 4 start
473 -- The results stored in l_result_domain_int table are to be transfered to result table which will have attribute name
474 p_result_count := l_result_domain_int.count;
475 for i in 1..p_result_count loop
476 open c1(l_result_domain_int(i).attribute_id) ;
477 fetch c1 into l_form_column_name;
478 close c1;
479 g_result(i).form_column_name := l_form_column_name;
480 g_result(i).mode_flag := l_result_domain_int(i).mode_flag;
481 g_result(i).reqd_flag := l_result_domain_int(i).reqd_flag;
482 hr_utility.set_location('att'||g_result(i).form_column_name||'mode'||g_result(i).mode_flag||'reqd'||g_result(i).reqd_flag||l_proc,200);
483 end loop;
484 else
485 hr_utility.set_location('errors , cannot execute'||l_proc,100);
486 end if;
487 hr_utility.set_location('Exiting'||l_proc,10000);
488 exception when others then
489 p_result_count := null;
490 raise;
491 end priviledge_calc_count;
492
493 --
494 -- ----------------------------------------------------------------------------
495 -- |------------------------< priviledge_calc >------------------------|
496 -- ----------------------------------------------------------------------------
497 --
498
499 procedure priviledge_calc (p_domain in pqh_template_attributes.template_id%type,
500 p_tasks in t_task_templ,
501 p_transaction_category_id in number,
502 p_result out nocopy t_attname_priv )
503 is
504 l_temp_flag varchar2(1);
505 l_result t_attname_priv;
506 l_task_count number := 1;
507 l_error_flag boolean := FALSE;
508 l_task_last_count number := 1;
509 l_domain_last_count number := 1;
510 l_att_count number := 1;
511 l_res_count number := 1;
512 l_int_res_count number := 1;
513 l_count number := 1;
514 l_task_att_count number ;
515 l_chg_result varchar2(1) := 'Y' ;
516 l_task_template_id pqh_template_attributes.template_id%type;
517 l_form_column_name pqh_txn_category_attributes.form_column_name%type;
518 l_result_flag varchar2(1) ;
519 l_result_task_int t_attid_priv;
520 l_result_domain_int t_attid_priv;
521 cursor c1(p_attribute_id number) is
522 select form_column_name
523 from pqh_txn_category_attributes
524 where attribute_id = p_attribute_id
525 and transaction_category_id = p_transaction_category_id;
526 l_proc varchar2(2000) := g_package||'priviledge_calc' ;
527 begin
528 hr_utility.set_location('Entering'||l_proc,10);
529 /*
530 This is the main procedure . It takes domain template id and an table of tasks as input
531 and passes the attributes and their flag values as result .
532 This procedure has three parts.
533 1) Internal result table is computed according to the tasks and their references attributes and flags.
534 2) Result table is computed according to the domain attributes and their flags.
535 3) Internal result table is applied on result table to take the minimum of the attribute flag
536 value and result is stored in the result table .
537 4) form_column_name is fetched corresponding to the attribute_id from the database and flag values are
538 stored in the table and this table of records is passed as out nocopy parameter
539 */
540 /* Check the input data validity, if domain is null then raise error and don't process the whole procedure */
541
542 l_task_last_count := p_tasks.count;
543 if p_domain is null then
544 l_error_flag := TRUE;
545 hr_utility.set_location('domain reqd'||l_proc,20);
546 elsif l_task_last_count = 0 then
547 l_error_flag := TRUE;
548 hr_utility.set_location('tasks reqd'||l_proc,30);
549 end if;
550
551 -- Part1 starts here
552 /*
553 for each task in the task table internal result table is updated call to Task
554 references is made
555 */
556 if l_error_flag = FALSE then
557 for l_task_count in 1..l_task_last_count loop
558 l_task_template_id := p_tasks(l_task_count) ;
559 task_result_update(p_task => p_tasks(l_task_count),
560 p_task_type => 'T',
561 p_result_int => l_result_task_int) ;
562 task_references(p_task => p_tasks(l_task_count),
563 p_result_int => l_result_task_int) ;
564 end loop;
565 l_task_att_count := l_result_task_int.count;
566 for i in 1..l_task_att_count loop
570 -- Part2 : Computation of result table , corresponding to domain template
567 hr_utility.set_location('att '||l_result_task_int(i).attribute_id||'mode '||l_result_task_int(i).mode_flag||'reqd '||l_result_task_int(i).reqd_flag||l_proc,35);
568 end loop;
569
571 domain_result_calc(p_domain => p_domain,
572 p_result => l_result_domain_int);
573
574 -- Part3 starts here
575 /*
576 Applying internal result table on the result table to find out nocopy the minimum of the
577 flag value applicable to the attribute
578 Call to domain_task_comp flag is made for each comparison.
579 if an attribute is not there in the internal result table then result table is updated
580 for that attribute and flag is made N.
581 */
582 l_domain_last_count := l_result_domain_int.count;
583 l_res_count := 1;
584 for l_res_count in 1..l_domain_last_count loop
585 l_int_res_count := 1;
586 l_chg_result := 'Y' ;
587 l_task_last_count := l_result_task_int.count;
588 for l_int_res_count in 1..l_task_last_count loop
589 if l_result_domain_int(l_res_count).attribute_id = l_result_task_int(l_int_res_count).attribute_id then
590 l_chg_result := 'N' ;
591 hr_utility.set_location('attribute in task'||to_char(l_result_task_int(l_int_res_count).attribute_id)||l_proc,95);
592 -- mode flag is taken as minimum of domain and task privledges
593 domain_task_mode_comp_flag(p_domain_mode_flag => l_result_domain_int(l_res_count).mode_flag,
594 p_task_mode_flag => l_result_task_int(l_int_res_count).mode_flag,
595 p_result_flag => l_temp_flag);
596 l_result_domain_int(l_res_count).mode_flag := l_temp_flag;
597 -- while required flag is solely task priviledge
598 l_result_domain_int(l_res_count).reqd_flag := l_result_task_int(l_int_res_count).reqd_flag ;
599 end if;
600 end loop;
601 if l_chg_result = 'Y' then
602 -- selected tasks don't have priviledge on this attribute
603 l_result_domain_int(l_res_count).mode_flag := 'N' ;
604 l_result_domain_int(l_res_count).reqd_flag := 'N' ;
605 end if;
606 end loop;
607
608 -- Part 4 start
609 -- The results stored in l_result_domain_int table are to be transfered to result table which will have attribute name
610 l_count := l_result_domain_int.count;
611 for i in 1..l_count loop
612 open c1(l_result_domain_int(i).attribute_id) ;
613 fetch c1 into l_form_column_name;
614 close c1;
615 p_result(i).form_column_name := l_form_column_name;
616 p_result(i).mode_flag := l_result_domain_int(i).mode_flag;
617 p_result(i).reqd_flag := l_result_domain_int(i).reqd_flag;
618 hr_utility.set_location('att'||p_result(i).form_column_name||'mode'||p_result(i).mode_flag||'reqd'||p_result(i).reqd_flag||l_proc,200);
619 end loop;
620 else
621 hr_utility.set_location('errors , cannot execute'||l_proc,100);
622 end if;
623 hr_utility.set_location('Exiting'||l_proc,10000);
624 exception when others then
625 p_result := l_result;
626 raise;
627 end priviledge_calc;
628
629 --
630 -- ----------------------------------------------------------------------------
631 -- |------------------------< template_attrib_reqd_calc >------------------------|
632 -- ----------------------------------------------------------------------------
633 --
634
635 procedure template_attrib_reqd_calc (p_tasks in t_task_templ,
636 p_transaction_category_id in number,
637 p_result out nocopy t_attname_priv)
638 as
639 l_task_count number := 1;
640 i binary_integer := 1;
641 l_task_template_id pqh_template_attributes.template_id%type;
642 l_form_column_name pqh_txn_category_attributes.form_column_name%type;
643 l_result_task_int t_attid_priv;
644 l_result t_attname_priv;
645
646 cursor c1(p_attribute_id number) is
647 select form_column_name
648 from pqh_txn_category_attributes
649 where attribute_id = p_attribute_id
650 and transaction_category_id = p_transaction_category_id;
651
652 l_proc varchar2(2000) := g_package||'template_attrib_reqd_calc' ;
653 begin
654 hr_utility.set_location('Entering'||l_proc,10);
655 for l_task_count in 1..p_tasks.count loop
656 l_task_template_id := p_tasks(l_task_count) ;
657 task_result_update(p_task => p_tasks(l_task_count),
658 p_task_type => 'T',
659 p_result_int => l_result_task_int) ;
660 task_references(p_task => p_tasks(l_task_count),
661 p_result_int => l_result_task_int) ;
662 end loop;
663 for i in 1..l_result_task_int.count loop
664 open c1(l_result_task_int(i).attribute_id) ;
665 fetch c1 into l_form_column_name;
666 close c1;
667 p_result(i).form_column_name := l_form_column_name;
668 p_result(i).mode_flag := l_result_task_int(i).mode_flag;
669 p_result(i).reqd_flag := l_result_task_int(i).reqd_flag;
670 end loop;
671 hr_utility.set_location('Exiting'||l_proc,10000);
672 exception when others then
673 p_result := l_result;
674 raise;
675 end template_attrib_reqd_calc;
676 --
677 -- ----------------------------------------------------------------------------
678 -- |------------------------< get_attribute_mode >----------------------------|
679 -- ----------------------------------------------------------------------------
680 --
681
682 function get_attribute_mode(
683 p_form_column_name in varchar2)
684 return varchar2 is
685 begin
686 for i in g_result.first .. g_result.last loop
687 if g_result(i).form_column_name = p_form_column_name then
688 return g_result(i).mode_flag;
689 end if;
690 end loop;
691 return 'N';
692 exception
693 when others then
694 return 'N';
695 end;
696 --
697 end;
[Home] [Help]