[Home] [Help]
PACKAGE BODY: APPS.HR_INTEGRATION_UTILS
Source
1 PACKAGE BODY hr_integration_utils AS
2 /* $Header: hrintutl.pkb 120.6 2010/12/10 06:26:37 sbrahmad ship $ */
3 --
4 --
5 --
6 --
7 ----$ Store form name in global variable to be created as a parameter along with
8 -- where and extra where params when an integrator is launched from forms
9 g_form_name varchar2(20);
10 --
11 -- ---------------------------------------------------------------------------
12 -- |------------------------< intg_resp_chk >--------------------------------|
13 -- ---------------------------------------------------------------------------
14 --
15 -- Description:
19 --
16 -- Check the Integrator/Responsibility associations table, and if
17 -- integrator exists in the table - restrict by resp_id
18 -- integrator does not exist in the table - do not restrict by resp_id
20 -- ---------------------------------------------------------------------------
21 FUNCTION intg_resp_chk(p_intg_code IN varchar2) RETURN boolean IS
22 --
23 CURSOR csr_chk_resp IS
24 SELECT resp_application_id, responsibility_id
25 FROM hr_adi_intg_resp
26 WHERE intg_application_id || ':' || integrator_code = p_intg_code;
27 --
28 l_curr_resp_id number := fnd_global.resp_id;
29 l_curr_resp_app_id number := fnd_global.resp_appl_id;
30 l_found boolean := false;
31 l_count number := 0;
32 --
33 BEGIN
34 --
35 FOR c1 IN csr_chk_resp LOOP
36 -- An entry exists for this integrator, so restrict by resp_id
37 l_count := l_count +1;
38 IF not l_found THEN
39 --
40 IF ((c1.resp_application_id = l_curr_resp_app_id) AND
41 (c1.responsibility_id = l_curr_resp_id)) THEN
42 l_found := TRUE;
43 END IF;
44 --
45 END IF;
46 END LOOP;
47 --
48 IF l_count = 0 THEN
49 --
50 -- No entries in this table for this integrator, do not restrict
51 RETURN TRUE;
52 ELSE
53 -- Return True/False depending on appropriate resp matching
54 RETURN l_found;
55 END IF;
56 END intg_resp_chk;
57 --
58 FUNCTION fetch_other_params(p_form_name IN varchar2) RETURN varchar2 IS
59 --
60 TYPE IntCurTyp IS REF CURSOR;
61 l_int_csr IntCurTyp;
62 l_other_params varchar2(2000) := 'bne:integrator=';
63 l_int_code varchar2(60);
64 l_int_count integer := 0;
65 --
66 BEGIN
67 --
68 -- Open cursor
69 OPEN l_int_csr FOR
70 'SELECT pli.string_value ' ||
71 ' FROM bne_param_lists_tl plt ' ||
72 ' , bne_param_list_items pli ' ||
73 ' WHERE plt.application_id = 800 ' ||
74 ' AND plt.user_name = ''' || p_form_name || ''' ' ||
75 ' AND pli.application_id = plt.application_id ' ||
76 ' AND pli.param_list_code = plt.param_list_code ' ||
77 ' AND plt.language = USERENV(''LANG'')';
78
79 -- ' FROM bne_param_list_tl plt ' ||
80 -- ' , bne_param_list_items pli ' ||
81 -- ' , bne_object_properties_tl opt ' ||
82 -- ' , bne_integrators i ' ||
83 -- ' WHERE upper(plt.param_list_name) = ''' || p_form_name ||
84 -- ''' ' ||
85 -- ' AND plt.param_list_id = pli.param_list_id ' ||
86 -- ' AND upper(pli.string_value) = upper(opt.value) ' ||
87 -- ' AND opt.object_id = i.integrator_id ';
88 --
89 LOOP
90 --
91 FETCH l_int_csr INTO l_int_code;
92 EXIT WHEN l_int_csr%NOTFOUND;
93 --
94 IF intg_resp_chk(l_int_code) THEN
95 IF l_int_count <> 0 THEN
96 --
97 l_other_params := l_other_params || ',';
98 --
99 END IF;
100 --
101 l_other_params := l_other_params || l_int_code;
102 l_int_count := l_int_count + 1;
103 --
104 END IF;
105 END LOOP;
106 --
107 CLOSE l_int_csr;
108 --
109 IF l_int_count = 0 THEN
110 l_other_params := 'ERROR';
111 ELSE
112 l_other_params := l_other_params||'&'||'bne:noreview=true' ;
113 END IF;
114 --
115 --$ Assign value to global variable
116 g_form_name := p_form_name;
117
118 RETURN l_other_params;
119 --
120 END fetch_other_params;
121 --
122 FUNCTION fetch_other_letter_params(p_letter IN varchar2) RETURN varchar2 IS
123 --
124 TYPE IntCurTyp IS REF CURSOR;
125 l_int_csr IntCurTyp;
126 l_other_params varchar2(2000) := 'bne:integrator=';
127 l_int_code varchar2(60);
128 l_int_count integer := 0;
129 --
130 BEGIN
131 --
132 -- Open cursor
133 --OPEN l_int_csr FOR
134 --'SELECT i.integrator_id ' ||
135 --' FROM bne_param_list_tl plt ' ||
136 --' , bne_param_list_items pli ' ||
137 --' , bne_object_properties_tl opt1 ' ||
138 --' , bne_integrators i ' ||
139 --' , bne_layouts bl ' ||
140 --' , bne_object_properties_tl opt2 ' ||
141 --' WHERE upper(plt.param_list_name) = ''LETTER'' ' ||
142 --' AND plt.param_list_id = pli.param_list_id ' ||
143 --' AND upper(pli.string_value) = upper(opt1.value) ' ||
144 --' AND opt1.object_id = i.integrator_id ' ||
145 --' AND i.integrator_id = bl.integrator_id ' ||
146 --' AND bl.layout_id = opt2.object_id ' ||
147 --' AND upper(opt2.value) = upper(''' || p_letter || ''')';
148 hr_utility.set_location('L:'||p_letter,5);
149 OPEN l_int_csr FOR
150 'SELECT pli.string_value ' ||
151 ' FROM bne_param_lists_b plb ' ||
152 ' , bne_param_list_items pli ' ||
153 ' , bne_layouts_b lb ' ||
154 ' , bne_layouts_tl lt ' ||
155 ' WHERE plb.application_id = 800 ' ||
156 ' AND plb.param_list_code = ''HR_LETTER'' ' ||
157 ' AND pli.application_id = plb.application_id ' ||
158 ' AND pli.param_list_code = plb.param_list_code ' ||
159 ' AND lb.integrator_app_id = ' ||
160 ' substr(pli.string_value,0,instr(pli.string_value,'':'')-1) ' ||
161 ' AND lb.integrator_code = ' ||
162 ' substr(pli.string_value,instr(pli.string_value,'':'')+1) ' ||
163 ' AND lt.application_id = lb.application_id ' ||
164 ' AND lt.layout_code = lb.layout_code ' ||
165 ' AND lt.user_name = ''' || p_letter || ''' ';
166 --
167 LOOP
168 --
169 FETCH l_int_csr INTO l_int_code;
170 EXIT WHEN l_int_csr%NOTFOUND;
171 --
172 IF intg_resp_chk(l_int_code) THEN
173 --
174 IF l_int_count <> 0 THEN
175 --
176 l_other_params := l_other_params || ',';
177 --
178 END IF;
179 --
180 l_other_params := l_other_params || l_int_code;
181 l_int_count := l_int_count + 1;
182 --
183 END IF;
184 END LOOP;
185 --
186 CLOSE l_int_csr;
187 --
188 IF l_int_count = 0 THEN
189 l_other_params := 'ERROR';
190 END IF;
191 --
192 --$ Assign value to global variable
193 g_form_name := 'HR_LETTER';
194
195 RETURN l_other_params;
196 --
197 END fetch_other_letter_params;
198 --
199 FUNCTION store_sql(p_sql IN varchar2, p_date IN varchar2) RETURN varchar2 IS
200 PRAGMA AUTONOMOUS_TRANSACTION;
201 --
202 --$ Pass form name as an additional argument to add into param list
203 l_sql varchar2(4000) := 'begin ' ||
204 ' :1 := hr_passed_sql.get_passed_sql_id(:2,:3,:4); ' ||
205 'end;';
206 l_return_code varchar2(60);
207 --
208 BEGIN
209 hr_utility.trace('Form_name ='||g_form_name||'--');
210 EXECUTE IMMEDIATE l_sql
211 USING out l_return_code,
212 in p_sql,
213 in p_date,
214 in g_form_name;
215 COMMIT;
216 RETURN l_return_code;
217 END store_sql;
218 --
219 -- -------------------------------------------------------------------------
220 -- |----------------------< add_or_update_session >------------------------|
221 -- -------------------------------------------------------------------------
222 PROCEDURE add_or_update_session(p_sess_date in date) is
223 PRAGMA AUTONOMOUS_TRANSACTION;
224 --
225 CURSOR csr_find_row IS
226 SELECT 'Y'
227 FROM fnd_sessions
228 WHERE session_id = userenv('sessionid');
229 --
230 l_exists varchar2(1);
231 BEGIN
232 --
233 -- hr_utility.trace_on;
234 hr_utility.set_location('ADD_OR_UPDATE_SESSION',10);
235 --
236 open csr_find_row;
237 fetch csr_find_row into l_exists;
238 IF csr_find_row%NOTFOUND THEN
239 --
240 hr_utility.set_location('ADD_OR_UPDATE_SESSION - NO ROW',20);
241 --
242 -- Row does not exist, so add
243 INSERT INTO fnd_sessions (session_id, effective_date)
244 VALUES (userenv('sessionid'), p_sess_date);
245 --
246 hr_utility.set_location('ADD_OR_UPDATE_SESSION - ROW ADDED',30);
247 --
248 ELSE
249 --
250 hr_utility.set_location('ADD_OR_UPDATE_SESSION - ROW EXISTS',40);
251 -- Row exists, so update date
252 UPDATE fnd_sessions
253 SET effective_date = p_sess_date
254 WHERE session_id = userenv('sessionid');
255 hr_utility.set_location('ADD_OR_UPDATE_SESSION - ROW UPDATE',50);
256 --
257 END IF;
258 close csr_find_row;
259 --
260 commit;
261 -- hr_utility.trace_off;
262 END add_or_update_session;
263 --
264 -- -------------------------------------------------------------------------
265 -- |------------------< add_hr_param_list_to_content >---------------------|
266 -- -------------------------------------------------------------------------
267 PROCEDURE add_hr_param_list_to_content(p_application_id in number
268 ,p_content_code in varchar2) IS
269 --
270 l_param_list_name varchar2(30) := 'HR_STANDARD';
271 l_update_sql varchar2(2000);
272 --
273 BEGIN
274 --
275 l_update_sql :=
276 'BEGIN ' ||
277 'UPDATE BNE_CONTENTS_B ' ||
278 ' SET param_list_app_id = 800' ||
279 ' , param_list_code = :1 ' ||
280 ' WHERE application_id = :2 ' ||
281 ' AND content_code = :3 ; ' ||
282 'END;';
283 --
284 EXECUTE IMMEDIATE l_update_sql
285 USING IN l_param_list_name,
286 IN to_char(p_application_id),
287 IN p_content_code;
288 --
289 END add_hr_param_list_to_content;
290 --
291 -- -------------------------------------------------------------------------
292 -- |-------------------< add_hr_upload_list_to_integ >---------------------|
293 -- -------------------------------------------------------------------------
294 PROCEDURE add_hr_upload_list_to_integ(p_application_id in number
295 ,p_integrator_code in varchar2) IS
296 --
297 l_param_list_name varchar2(30) := 'HR_UPLOAD';
298 l_update_sql varchar2(2000);
299 --
300 BEGIN
301 --
302 l_update_sql :=
306 ' , upload_param_list_code = :1 ' ||
303 'BEGIN ' ||
304 'UPDATE BNE_INTEGRATORS_B ' ||
305 ' SET upload_param_list_app_id = 800 ' ||
307 ' , upload_serv_param_list_app_id = 231 ' ||
308 ' , upload_serv_param_list_code = ''UPL_SERV_JNLS'' ' ||
309 ' WHERE application_id = :2 ' ||
310 ' AND integrator_code = :3 ; ' ||
311 'END;';
312 --
313 EXECUTE IMMEDIATE l_update_sql
314 USING IN l_param_list_name,
315 IN to_char(p_application_id),
316 IN p_integrator_code;
317 --
318 END add_hr_upload_list_to_integ;
319 --
320 -- ------------------------------------------------------------------------
321 -- | -----------------< register_integrator_to_form >---------------------|
322 -- ------------------------------------------------------------------------
323 PROCEDURE register_integrator_to_form(p_integrator in varchar2
324 ,p_form_name in varchar2) IS
325 --
326 l_plsql varchar2(2000);
327 l_desc_value varchar2(100) := 'Integrator for use on this form';
328 l_id number;
329 l_app_id number;
330 l_list_code varchar2(60);
331 l_list_key varchar2(60);
332 l_persistent varchar2(1) := 'Y';
333 l_count number;
334 --
335 BEGIN
336 l_list_code := 'HR_' || p_form_name;
337 l_app_id := 800;
338
339 l_plsql := 'BEGIN ' ||
340 ' SELECT count(*) ' ||
341 ' INTO :1 ' ||
342 ' FROM bne_param_lists_tl ' ||
343 ' WHERE user_name = :2 ' ||
344 ' AND param_list_code = :3 ' ||
345 ' AND application_id = 800; ' ||
346 'END;';
347 --
348 EXECUTE IMMEDIATE l_plsql
349 USING out l_count,
350 in p_form_name,
351 in l_list_code;
352 --
353 IF l_count = 0 THEN
354 --
355 -- Need to create Param List
356 --
357 -- Create the Form Parameter List
358 l_plsql :=
359 'BEGIN ' ||
360 ' :1 := BNE_PARAMETER_UTILS.CREATE_PARAM_LIST_ALL' ||
361 ' (p_application_id => 800 ' ||
362 ' ,p_param_list_code => :2 ' ||
363 ' ,p_persistent => :3 ' ||
364 ' ,p_comments => null ' ||
365 ' ,p_attribute_app_id => null ' ||
366 ' ,p_attribute_code => null ' ||
367 ' ,p_list_resolver => null ' ||
368 ' ,p_prompt_left => null ' ||
369 ' ,p_prompt_above => :4 ' ||
370 ' ,p_user_name => :5 ' ||
371 ' ,p_user_tip => null ' ||
372 ' ); ' ||
373 'END; ';
374 --
375 EXECUTE IMMEDIATE l_plsql
376 USING out l_list_key,
377 IN l_list_code,
378 IN l_persistent,
379 IN p_form_name || ' Integrators ',
380 IN p_form_name;
381 --
382 END IF;
383 --
384 --
385 l_plsql := 'BEGIN ' ||
386 ' :1 := BNE_PARAMETER_UTILS.CREATE_LIST_ITEMS_ALL ' ||
387 ' (p_application_id => :2 ' ||
388 ' ,p_param_list_code => :3 ' ||
389 ' ,p_param_defn_app_id => null ' ||
390 ' ,p_param_defn_code => null ' ||
391 ' ,p_param_name => ''' || 'Integrator' || ''' ' ||
392 ' ,p_attribute_app_id => null ' ||
393 ' ,p_attribute_code => null ' ||
394 ' ,p_string_val => :4 ' ||
395 ' ,p_date_val => null ' ||
396 ' ,p_number_val => null ' ||
397 ' ,p_boolean_val => null ' ||
398 ' ,p_formula => null ' ||
399 ' ,p_desc_val => :5 ' ||
400 ' ); ' ||
401 ' END;';
402 --
403 EXECUTE IMMEDIATE l_plsql
404 USING out l_id,
405 IN to_char(l_app_id),
406 IN l_list_code,
407 IN p_integrator,
408 IN l_desc_value;
409 --
410 END register_integrator_to_form;
411 --
412 -- ------------------------------------------------------------------------
413 -- | ---------------------< process_where_clause >------------------------|
414 -- ------------------------------------------------------------------------
415 FUNCTION process_where_clause(p_where_clause IN varchar2) RETURN varchar2 IS
416 --
417 l_base_table_temp varchar2(100);
418 l_base_table_alias varchar2(100);
419 l_base_table varchar2(30);
420 l_temp_sql varchar2(4000);
421 l_return_sql varchar2(4000);
422 l_upper_sql varchar2(4000);
423 l_start integer;
424 l_pos integer;
425 --
426 BEGIN
427 --
428 -- Determine base table
429 l_base_table_temp := substr(p_where_clause, 7,
430 instr(upper(p_where_clause),'WHERE')-8);
431 --
432 -- Check for Alias
433 IF (instr(l_base_table_temp, ' ') > 0) THEN
434 --
435 -- Alias found
436 l_base_table := substr(l_base_table_temp,1,instr(l_base_table_temp,' ')-1);
437 l_base_table_alias := substr(l_base_table_temp,instr(l_base_table_temp,' ')+1);
438 --
439 ELSE
440 --
441 -- No alias.
442 l_base_table := l_base_table_temp;
443 l_base_table_alias := '';
444 END IF;
445 --
446 -- build up WHERE clause
447 l_temp_sql := substr(p_where_clause, instr(upper(p_where_clause),'WHERE'));
448 l_temp_sql := ' ADI1 ' || l_temp_sql;
449 --
453 --
450 -- Replace table name, which may be in upper or lower case
451 l_upper_sql := upper(l_temp_sql);
452 l_start := 1;
454 WHILE (instr(l_upper_sql,upper(l_base_table)|| '.',l_start) > 0) LOOP
455 --
456 l_pos := instr(l_upper_sql,upper(l_base_table) || '.',l_start);
457 hr_utility.set_location('Found at: ' || l_pos,10);
458 --
459 l_temp_sql := substr(l_temp_sql,1,l_pos-1) || 'ADI1.' ||
460 substr(l_temp_sql,l_pos + length(l_base_table || '.'));
461 hr_utility.set_location('replaced',11);
462 --
463 l_upper_sql := upper(l_temp_sql);
464 --
465 -- l_start := l_pos + length(l_base_table || '.');
466 -- hr_utility.set_location('start:'||l_start,12);
467 --
468 END LOOP;
469 --
470 -- replace Alias which may be in upper or lower case
471 l_upper_sql := upper(l_temp_sql);
472 l_start := 1;
473 --
474 WHILE (instr(l_upper_sql, ' ' || upper(l_base_table_alias) || '.', l_start) > 0) LOOP
475 --
476 l_pos := instr(l_upper_sql, ' '||upper(l_base_table_alias) || '.', l_start);
477 hr_utility.set_location('Alias found at: ' || l_pos,13);
478 --
479 l_temp_sql := substr(l_temp_sql,1,l_pos-1) || ' ADI1.' ||
480 substr(l_temp_sql,l_pos + length(' '||l_base_table_alias||'.'));
481 hr_utility.set_location('Alias replaced',14);
482 --
483 l_upper_sql := upper(l_temp_sql);
484 --
485 END LOOP;
486 --
487 -- replace Alias which may be in upper or lower case
488 l_upper_sql := upper(l_temp_sql);
489 l_start := 1;
490 --
491 WHILE (instr(l_upper_sql, '(' || upper(l_base_table_alias) || '.', l_start) > 0) LOOP
492 --
493 l_pos := instr(l_upper_sql, '('||upper(l_base_table_alias) || '.', l_start);
494 hr_utility.set_location('Alias found at: ' || l_pos,13);
495 --
496 l_temp_sql := substr(l_temp_sql,1,l_pos-1) || '(ADI1.' ||
497 substr(l_temp_sql,l_pos + length('('||l_base_table_alias||'.'));
498 hr_utility.set_location('Alias replaced',14);
499 --
500 l_upper_sql := upper(l_temp_sql);
501 --
502 END LOOP;
503 --
504 -- l_return_sql := replace(upper(l_temp_sql),upper(l_base_table)||'.','ADI1.');
505 l_return_sql := l_temp_sql;
506 --
507 -- Return modified WHERE clause
508 --
509 RETURN l_return_sql;
510 --
511 END process_where_clause;
512 --
513 -- -------------------------------------------------------------------------
514 -- |-------------------< create_param_list_for_content >-------------------|
515 -- -------------------------------------------------------------------------
516 PROCEDURE create_param_list_for_content
517 (p_content_code in varchar
518 ,p_application_id in number
519 ,p_param_list_code out NOCOPY varchar2) IS
520 --
521 TYPE CSR_TYP IS REF CURSOR;
522 csr_int CSR_TYP;
523 --
524 -- Local variables
525 l_param_list_code varchar2(30);
526 l_attribute_code varchar2(30);
527 l_persistent varchar2(1) := 'Y';
528 l_prompt_above varchar2(240):= 'Download parameters';
529 l_list_name varchar2(240);
530 l_list_key varchar2(30);
531 l_sequence number;
532 l_param_defn_code varchar2(30);
533 l_plsql varchar2(4000);
534 --
535 BEGIN
536 --
537 --
538 l_param_list_code := substr(p_content_code, 1, 27) || '_PL';
539 --
540 -- Check if a param list already exists, if so, return param list code
541 --
542 OPEN csr_int FOR
543 'SELECT param_list_code ' ||
544 ' FROM bne_param_lists_b ' ||
545 ' WHERE param_list_code = ''' || l_param_list_code || ''' ' ||
546 ' AND application_id = ' || p_application_id || ' ';
547 --
548 FETCH csr_int INTO l_param_list_code;
549 --
550 IF csr_int%NOTFOUND THEN
551 -- Create Param List
552 --
553 l_list_name := l_prompt_above || ': ' || l_param_list_code;
554 l_prompt_above := fnd_message.get_string('PER','HR_DOWNLOAD_PARAM_LABEL');
555 --
556 l_plsql := 'BEGIN ' ||
557 ' :1 := BNE_PARAMETER_UTILS.CREATE_PARAM_LIST_ALL' ||
558 ' (p_application_id => :2 ' ||
559 ' ,p_param_list_code => :3 ' ||
560 ' ,p_persistent => :4 ' ||
561 ' ,p_comments => null ' ||
562 ' ,p_attribute_app_id => null ' ||
563 ' ,p_attribute_code => null ' ||
564 ' ,p_list_resolver => null ' ||
565 ' ,p_prompt_left => null ' ||
566 ' ,p_prompt_above => :5 ' ||
567 ' ,p_user_name => :6 ' ||
568 ' ,p_user_tip => null ' ||
569 ' ); ' ||
570 'END; ';
571 --
572 EXECUTE IMMEDIATE l_plsql
573 USING out l_list_key,
574 IN p_application_id,
575 IN l_param_list_code,
576 IN l_persistent,
577 IN l_prompt_above,
578 IN l_list_name;
579 --
580 -- Create 3 param list items for this param list
581 -- 1. hr:sessionDate
582 -- 2. hr:where
583 -- 3. hr:extra
584 --
585 l_param_defn_code := 'HR_STANDARD_SESS_DATE';
586 l_attribute_code := 'HR_STANDARD_NO_SAVE';
587 --
588 l_plsql := 'BEGIN ' ||
589 ' :1 := BNE_PARAMETER_UTILS.CREATE_LIST_ITEMS_ALL' ||
590 ' (p_application_id => :2 ' ||
591 ' ,p_param_list_code => :3 ' ||
595 ' ,p_attribute_app_id => 800 ' ||
592 ' ,p_param_defn_app_id => 800 ' ||
593 ' ,p_param_defn_code => :4 ' ||
594 ' ,p_param_name => ''hr:sessionDate'' ' ||
596 ' ,p_attribute_code => :5 ' ||
597 ' ,p_string_val => '''' ' ||
598 ' ,p_date_val => '''' ' ||
599 ' ,p_number_val => '''' ' ||
600 ' ,p_boolean_val => '''' ' ||
601 ' ,p_formula => '''' ' ||
602 ' ,p_desc_val => '''' ' ||
603 ' ); ' ||
604 'END; ';
605 --
606 EXECUTE IMMEDIATE l_plsql
607 USING out l_sequence,
608 IN p_application_id,
609 IN l_param_list_code,
610 IN l_param_defn_code,
611 IN l_attribute_code;
612 --
613 l_param_defn_code := 'HR_STANDARD_WHERE';
614 l_attribute_code := 'HR_STANDARD_NO_SAVE';
615 --
616 l_plsql := 'BEGIN ' ||
617 ' :1 := BNE_PARAMETER_UTILS.CREATE_LIST_ITEMS_ALL' ||
618 ' (p_application_id => :2 ' ||
619 ' ,p_param_list_code => :3 ' ||
620 ' ,p_param_defn_app_id => 800 ' ||
621 ' ,p_param_defn_code => :4 ' ||
622 ' ,p_param_name => ''hr:where'' ' ||
623 ' ,p_attribute_app_id => 800 ' ||
624 ' ,p_attribute_code => :5 ' ||
625 ' ,p_string_val => '''' ' ||
626 ' ,p_date_val => '''' ' ||
627 ' ,p_number_val => '''' ' ||
628 ' ,p_boolean_val => '''' ' ||
629 ' ,p_formula => '''' ' ||
630 ' ,p_desc_val => '''' ' ||
631 ' ); ' ||
632 'END; ';
633 --
634 EXECUTE IMMEDIATE l_plsql
635 USING out l_sequence,
636 IN p_application_id,
637 IN l_param_list_code,
638 IN l_param_defn_code,
639 IN l_attribute_code;
640 --
641 l_param_defn_code := 'HR_STANDARD_EXTRA';
642 l_attribute_code := 'HR_STANDARD_NO_SAVE';
643 --
644 l_plsql := 'BEGIN ' ||
645 ' :1 := BNE_PARAMETER_UTILS.CREATE_LIST_ITEMS_ALL' ||
646 ' (p_application_id => :2 ' ||
647 ' ,p_param_list_code => :3 ' ||
648 ' ,p_param_defn_app_id => 800 ' ||
649 ' ,p_param_defn_code => :4 ' ||
650 ' ,p_param_name => ''hr:extra'' ' ||
651 ' ,p_attribute_app_id => 800 ' ||
652 ' ,p_attribute_code => :5 ' ||
653 ' ,p_string_val => '''' ' ||
654 ' ,p_date_val => '''' ' ||
655 ' ,p_number_val => '''' ' ||
656 ' ,p_boolean_val => '''' ' ||
657 ' ,p_formula => '''' ' ||
658 ' ,p_desc_val => '''' ' ||
659 ' ); ' ||
660 'END; ';
661 --
662 EXECUTE IMMEDIATE l_plsql
663 USING out l_sequence,
664 IN p_application_id,
665 IN l_param_list_code,
666 IN l_param_defn_code,
667 IN l_attribute_code;
668 --
669 END IF;
670 CLOSE csr_int;
671 --
672 -- Return param list code
673 p_param_list_code := l_param_list_code;
674 --
675 END create_param_list_for_content;
676 --
677 -- -------------------------------------------------------------------------
678 -- |----------------------< create_param_list_item >-----------------------|
679 -- -------------------------------------------------------------------------
680 PROCEDURE create_param_list_item
681 (p_param_list_code in varchar2
682 ,p_application_id in number
683 ,p_param_name in varchar2
684 ,p_param_type in number
685 ,p_param_prompt in varchar2) IS
686 --
687 -- Local variable
688 l_param_defn_code varchar2(30);
689 l_return_code varchar2(30);
690 l_attribute_code varchar2(30);
691 l_plsql varchar2(4000);
692 l_default_string varchar2(10) := '';
693 l_max_size number := 20;
694 l_display_size number := 10;
695 l_format_mask varchar2(10);
696 l_sequence number;
697 l_param_name varchar2(240);
698 l_data_type number;
699 --
700 BEGIN
701 --
702 -- Determine if a param defn already exists
703 --
704 l_plsql := 'BEGIN ' ||
705 ' :1 := BNE_PARAMETER_UTILS.GET_PARAM_DEFN_ID ' ||
706 ' (P_APPLICATION_ID => :2 ' ||
707 ' ,P_PARAM_DEFN_NAME => :3 ' ||
708 ' ,P_PARAM_SOURCE => ''HR:Download'' ' ||
709 ' ); ' ||
710 'END; ';
711 --
712 EXECUTE IMMEDIATE l_plsql
713 USING out l_param_defn_code,
714 IN p_application_id,
715 IN p_param_name;
716 --
717 IF l_param_defn_code IS NULL THEN
718 --
719 -- Create parameter definition
720 --
721 l_param_defn_code := upper(replace(p_param_name,':','_'));
722 --
723 IF p_param_type = 1 THEN
724 l_default_string := '%';
725 l_max_size := 80;
726 l_display_size := 30;
730 --
727 ELSIF p_param_type = 3 THEN
728 l_format_mask := 'yyyy/MM/dd';
729 END IF;
731 l_plsql := 'BEGIN ' ||
732 ' :1 := BNE_PARAMETER_UTILS.CREATE_PARAM_ALL ' ||
733 ' (P_APPLICATION_ID => :2 ' ||
734 ' ,P_PARAM_CODE => :3 ' ||
735 ' ,P_PARAM_NAME => :4 ' ||
736 ' ,P_PARAM_SOURCE => ''HR:Download'' ' ||
737 ' ,P_CATEGORY => 5 ' ||
738 ' ,P_DATA_TYPE => :5 ' ||
739 ' ,P_ATTRIBUTE_APP_ID => '''' ' ||
740 ' ,P_ATTRIBUTE_CODE => '''' ' ||
741 ' ,P_PARAM_RESOLVER => '''' ' ||
742 ' ,P_REQUIRED => ''N'' ' ||
743 ' ,P_VISIBLE => ''Y'' ' ||
744 ' ,P_MODIFYABLE => ''Y'' ' ||
745 ' ,P_DEFAULT_STRING => :6 ' ||
746 ' ,P_DEFAULT_DATE => '''' ' ||
747 ' ,P_DEFAULT_NUM => '''' ' ||
748 ' ,P_DEFAULT_BOOLEAN => '''' ' ||
749 ' ,P_DEFAULT_FORMULA => '''' ' ||
750 ' ,P_VAL_TYPE => 1 ' ||
751 ' ,P_VAL_VALUE => '''' ' ||
752 ' ,P_MAXIMUM_SIZE => :7 ' ||
753 ' ,P_DISPLAY_TYPE => 4 ' ||
754 ' ,P_DISPLAY_STYLE => 1 ' ||
755 ' ,P_DISPLAY_SIZE => :8 ' ||
756 ' ,P_HELP_URL => '''' ' ||
757 ' ,P_FORMAT_MASK => :9 ' ||
758 ' ,P_DEFAULT_DESC => '''' ' ||
759 ' ,P_PROMPT_LEFT => :10 ' ||
760 ' ,P_PROMPT_ABOVE => '''' ' ||
761 ' ,P_USER_NAME => :11 ' ||
762 ' ,P_USER_TIP => '''' ' ||
763 ' ,P_ACCESS_KEY => '''' ' ||
764 ' ); ' ||
765 'END; ';
766 --
767 EXECUTE IMMEDIATE l_plsql
768 USING out l_return_code,
769 IN p_application_id,
770 IN l_param_defn_code,
771 IN p_param_name,
772 IN p_param_type,
773 IN l_default_string,
774 IN l_max_size,
775 IN l_display_size,
776 IN l_format_mask,
777 IN p_param_prompt,
778 IN p_param_name;
779 --
780 ELSE
781 -- Have param_defn_code in format <appid>:<code>
782 l_param_defn_code := substr(l_param_defn_code,instr(l_param_defn_code,':')+1);
783 SELECT PARAM_NAME , DATATYPE
784 INTO l_param_name , l_data_type
785 FROM BNE_PARAM_DEFNS_B
786 WHERE PARAM_DEFN_CODE = l_param_defn_code
787 AND APPLICATION_ID = p_application_id;
788
789 IF l_data_type <> p_param_type THEN
790 fnd_message.set_name('PER','PER_ADI_PARAM_NAME_EXISTS');
791 fnd_message.raise_error;
792 END IF;
793 END IF;
794 --
795 -- Have param defn, now define item in list
796 --
797 l_attribute_code := 'HR_STANDARD_NO_SAVE';
798 --
799 l_plsql := 'BEGIN ' ||
800 ' :1 := BNE_PARAMETER_UTILS.CREATE_LIST_ITEMS_ALL' ||
801 ' (p_application_id => :2 '||
802 ' ,p_param_list_code => :3 ' ||
803 ' ,p_param_defn_app_id => :4 ' ||
804 ' ,p_param_defn_code => :5 ' ||
805 ' ,p_param_name => :6 ' ||
806 ' ,p_attribute_app_id => 800 ' ||
807 ' ,p_attribute_code => :7 ' ||
808 ' ,p_string_val => '''' ' ||
809 ' ,p_date_val => '''' ' ||
810 ' ,p_number_val => '''' ' ||
811 ' ,p_boolean_val => '''' ' ||
812 ' ,p_formula => '''' ' ||
813 ' ,p_desc_val => '''' ' ||
814 ' ); ' ||
815 'END; ';
816 --
817 EXECUTE IMMEDIATE l_plsql
818 USING out l_sequence,
819 IN p_application_id,
820 IN p_param_list_code,
821 IN p_application_id,
822 IN l_param_defn_code,
823 IN p_param_name,
824 IN l_attribute_code;
825 --
826 END create_param_list_item;
827 --
828 -- ---------------------------------------------------------------------------
829 -- |---------------------------< add_sql_to_content >------------------------|
830 -- ---------------------------------------------------------------------------
831 PROCEDURE add_sql_to_content
832 (p_application_id in number
833 ,p_intg_user_name in varchar2
834 ,p_sql in varchar2
835 ,p_param1_name in varchar2 default NULL
836 ,p_param1_type in varchar2 default NULL
837 ,p_param1_prompt in varchar2 default NULL
838 ,p_param2_name in varchar2 default NULL
839 ,p_param2_type in varchar2 default NULL
840 ,p_param2_prompt in varchar2 default NULL
841 ,p_param3_name in varchar2 default NULL
842 ,p_param3_type in varchar2 default NULL
843 ,p_param3_prompt in varchar2 default NULL
844 ,p_param4_name in varchar2 default NULL
845 ,p_param4_type in varchar2 default NULL
846 ,p_param4_prompt in varchar2 default NULL
847 ,p_param5_name in varchar2 default NULL
851 --
848 ,p_param5_type in varchar2 default NULL
849 ,p_param5_prompt in varchar2 default NULL
850 ) IS
852 TYPE CSR_TYP IS REF CURSOR;
853 csr_int CSR_TYP;
854 --
855 -- Local variables
856 --
857 l_integrator_code varchar2(30);
858 l_content_code varchar2(30);
859 l_user_id number;
860 l_param_list_code varchar2(30);
861 l_plsql varchar2(2000);
862 --
863 BEGIN
864 --
865 -- Determine integrator code
866 OPEN csr_int FOR
867 'SELECT b.integrator_code ' ||
868 ' FROM bne_integrators_tl t ' ||
869 ' , bne_integrators_b b ' ||
870 ' WHERE t.application_id = ' || p_application_id ||
871 ' AND t.user_name = ''' || p_intg_user_name || '''' ||
872 ' AND t.integrator_code = b.integrator_code ' ||
873 ' AND t.application_id = b.application_id ' ||
874 ' AND t.integrator_code like ''GENERAL%'' ' ||
875 ' AND b.enabled_flag = ''Y'' ';
876 FETCH csr_int INTO l_integrator_code;
877 --
878 IF csr_int%NOTFOUND THEN
879 --
880 CLOSE csr_int;
881 fnd_message.set_name('PER','PER_289428_ADI_INTG_NOT_EXIST');
882 fnd_message.raise_error;
883 --
884 END IF;
885 --
886 CLOSE csr_int;
887 --
888 -- Determine content code for this integrator;
889 OPEN csr_int FOR
890 'SELECT content_code ' ||
891 ' FROM bne_contents_b ' ||
892 ' WHERE integrator_app_id = ' || p_application_id ||
893 ' AND integrator_code = ''' || l_integrator_code || '''';
894 FETCH csr_int INTO l_content_code;
895 --
896 IF csr_int%NOTFOUND THEN
897 --
898 CLOSE csr_int;
899 fnd_message.set_name('PER','PER_289505_ADI_CONT_NOT_EXIST');
900 fnd_message.raise_error;
901 --
902 END IF;
903 CLOSE csr_int;
904 --
905 -- Get user
906 SELECT fnd_global.user_id
907 INTO l_user_id
908 FROM dual;
909 --
910 -- Store SQL in BNE schema
911 l_plsql := 'BEGIN ' ||
912 ' bne_content_utils.upsert_stored_sql_statement ' ||
913 ' (p_application_id => :1 ' ||
914 ' ,p_content_code => :2 ' ||
915 ' ,p_query => :3 ' ||
916 ' ,p_user_id => :4 ' ||
917 ' ); ' ||
918 'END; ';
919 --
920 EXECUTE IMMEDIATE l_plsql
921 USING IN p_application_id,
922 IN l_content_code,
923 IN p_sql,
924 IN l_user_id;
925 --
926 --
927 -- Create standard parameter list with hr:session_date and hr:where
928 -- parameters, and then assign to content
929 create_param_list_for_content(l_content_code,
930 p_application_id,
931 l_param_list_code);
932 --
933 -- Create param list items for each parameter in SQL
934 IF p_param1_name IS NOT NULL THEN
935 --
936 create_param_list_item(l_param_list_code
937 ,p_application_id
938 ,p_param1_name
939 ,p_param1_type
940 ,p_param1_prompt);
941 --
942 END IF;
943 --
944 IF p_param2_name IS NOT NULL THEN
945 --
946 create_param_list_item(l_param_list_code
947 ,p_application_id
948 ,p_param2_name
949 ,p_param2_type
950 ,p_param2_prompt);
951 --
952 END IF;
953 --
954 IF p_param3_name IS NOT NULL THEN
955 --
956 create_param_list_item(l_param_list_code
957 ,p_application_id
958 ,p_param3_name
959 ,p_param3_type
960 ,p_param3_prompt);
961 --
962 END IF;
963 --
964 IF p_param4_name IS NOT NULL THEN
965 --
966 create_param_list_item(l_param_list_code
967 ,p_application_id
968 ,p_param4_name
969 ,p_param4_type
970 ,p_param4_prompt);
971 --
972 END IF;
973 --
974 IF p_param5_name IS NOT NULL THEN
975 --
976 create_param_list_item(l_param_list_code
977 ,p_application_id
978 ,p_param5_name
979 ,p_param5_type
980 ,p_param5_prompt);
981 --
982 END IF;
983 --
984 -- Have param list and param list items - now assign
985 -- to our content
986 --
987 l_plsql := 'BEGIN ' ||
988 ' bne_content_utils.assign_param_list_to_content ' ||
989 ' (P_CONTENT_APP_ID => :1 ' ||
990 ' ,P_CONTENT_CODE => :2 ' ||
991 ' ,P_PARAM_LIST_APP_ID => :3 ' ||
992 ' ,P_PARAM_LIST_CODE => :4 ' ||
993 ' ); ' ||
994 'END; ';
995 --
996 EXECUTE IMMEDIATE l_plsql
997 USING IN p_application_id,
998 IN l_content_code,
999 IN p_application_id,
1000 IN l_param_list_code;
1001 --
1002 END add_sql_to_content;
1003 --
1004 --
1005 -- ------------------------------------------------------------------------
1006 -- |----------------------< hr_disable_integrator >-----------------------|
1010 --
1007 -- ------------------------------------------------------------------------
1008 --
1009 -- Description:
1011 -- Allows a customer to disable any customer defined integrator, by
1012 -- setting its enabled flag, and altering the user integrator name.
1013 -- The integrator will also be removed from any parameter lists.
1014 --
1015 -- -----------------------------------------------------------------------
1016 PROCEDURE hr_disable_integrator
1017 (p_application_short_name in varchar2
1018 ,p_integrator_user_name in varchar2
1019 ,p_disable in varchar2) IS
1020 --
1021 TYPE IntCurTyp IS REF CURSOR;
1022 l_int_csr IntCurTyp;
1023 l_intg_code varchar2(30);
1024 l_int_user_name varchar2(240);
1025 l_sql varchar2(2000);
1026 l_application_id number;
1027 --
1028 CURSOR csr_find_app_id IS
1029 SELECT application_id
1030 FROM fnd_application
1031 WHERE upper(application_short_name) = upper(p_application_short_name);
1032 --
1033 BEGIN
1034 --
1035 -- Find application_id for given app_short_name
1036 OPEN csr_find_app_id;
1037 FETCH csr_find_app_id INTO l_application_id;
1038 IF csr_find_app_id%NOTFOUND THEN
1039 CLOSE csr_find_app_id;
1040 --
1041 -- Unable to determine Application ID for given short name
1042 fnd_message.set_name('PER','PER_289189_HR_DISBLE_INVAL_APP');
1043 fnd_message.raise_error;
1044 --
1045 END IF;
1046 CLOSE csr_find_app_id;
1047 --
1048 -- If application id is an hrms application then error, as these should
1049 -- not be removed.
1050 --
1051 IF (hr_general.chk_application_id(l_application_id) = 'TRUE') THEN
1052 --
1053 -- Integrator belongs to HRMS, so disabling it is not allowed
1054 fnd_message.set_name('PER','PER_289190_NO_DISABLE_HR_INTG');
1055 fnd_message.raise_error;
1056 --
1057 END IF;
1058 --
1059 -- Have integrator user name - determine integrator code
1060 --
1061 -- Open cursor (should only be 1 integrator with this particular
1062 -- user name).
1063 OPEN l_int_csr FOR
1064 'SELECT integrator_code ' ||
1065 ' FROM bne_integrators_tl ' ||
1066 ' WHERE user_name = ''' || p_integrator_user_name || ''' ' ||
1067 ' AND application_id = ' || l_application_id;
1068 --
1069 FETCH l_int_csr INTO l_intg_code;
1070 --
1071 IF l_int_csr%NOTFOUND THEN
1072 --
1073 CLOSE l_int_csr;
1074 fnd_message.set_name('PER','PER_289428_ADI_INTG_NOT_EXIST');
1075 fnd_message.raise_error;
1076 --
1077 END IF;
1078 CLOSE l_int_csr;
1079 --
1080 -- Have integrator code, now set to disable
1081 l_sql := 'BEGIN ' ||
1082 ' UPDATE bne_integrators_b ' ||
1083 ' SET enabled_flag = ''N'' ' ||
1084 ' WHERE integrator_code = :1 ' ||
1085 ' AND application_id = :2 ; ' ||
1086 'END; ';
1087 --
1088 EXECUTE IMMEDIATE l_sql
1089 USING IN l_intg_code,
1090 l_application_id;
1091 --
1092 -- Now alter integrator user name to reflect this change
1093 --
1094 l_sql := 'BEGIN ' ||
1095 ' UPDATE bne_integrators_tl ' ||
1096 ' SET user_name = :1 ' ||
1097 ' WHERE integrator_code = :2 ' ||
1098 ' AND application_id = :3; ' ||
1099 'END;';
1100 --
1101 EXECUTE IMMEDIATE l_sql
1102 USING IN p_integrator_user_name || ' (DISABLED)',
1103 l_intg_code,
1104 l_application_id;
1105 --
1106 -- Integrator has been disabled. Now we want to remove it from any
1107 -- parameter lists that it may exist on (for forms integration).
1108 l_sql := 'BEGIN ' ||
1109 ' DELETE ' ||
1110 ' FROM bne_param_list_items ' ||
1111 ' WHERE string_value like :1 ' ||
1112 ' AND application_id = 800 ' ||
1113 ' AND param_list_code like ''HR%''; ' ||
1114 'END ;';
1115 --
1116 EXECUTE IMMEDIATE l_sql
1117 USING IN l_application_id || ':' || l_intg_code;
1118 --
1119 END hr_disable_integrator;
1120 --
1121 -- ----------------------------------------------------------------------------
1122 -- |-------------------< hr_create_resp_association >-------------------------|
1123 -- ----------------------------------------------------------------------------
1124 --
1125 -- Description:
1126 -- Called to populate an entry in the HR_ADI_INTG_RESP table, which is
1127 -- a table holding associations between integrators and responsibilities.
1128 --
1129 -- ----------------------------------------------------------------------------
1130 PROCEDURE hr_create_resp_association
1131 (p_intg_application IN varchar2
1132 ,p_integrator_user_name IN varchar2
1133 ,p_resp_application IN varchar2
1134 ,p_responsibility_name IN varchar2) IS
1135 --
1136 TYPE IntCurTyp IS REF CURSOR;
1137 l_int_csr IntCurTyp;
1138 l_intg_application_id number;
1139 l_resp_application_id number;
1140 l_integrator_code varchar2(30);
1141 l_responsibility_id number;
1142 --
1143 -- Cursor to determine app_id
1144 CURSOR csr_get_app_id(l_app_short_name IN varchar) IS
1145 SELECT application_id
1146 FROM fnd_application
1147 WHERE upper(application_short_name) = upper(l_app_short_name);
1148 --
1149 -- Cursor to determine resp_id
1150 CURSOR csr_get_resp_id(l_app_id IN number, l_resp_name IN varchar2) IS
1151 SELECT responsibility_id
1152 FROM fnd_responsibility_vl
1153 WHERE application_id = l_app_id
1154 AND responsibility_name = l_resp_name;
1155 --
1156 BEGIN
1157 -- Determine app_id for integrator
1158 OPEN csr_get_app_id(p_intg_application);
1162 CLOSE csr_get_app_id;
1159 FETCH csr_get_app_id INTO l_intg_application_id;
1160 IF csr_get_app_id%NOTFOUND THEN
1161 --
1163 -- Invalid application short name
1164 fnd_message.set_name('PER','PER_289514_ADI_INVAL_INTG_APPL');
1165 fnd_message.raise_error;
1166 END IF;
1167 CLOSE csr_get_app_id;
1168 --
1169 -- Determine app_id for responsibility
1170 OPEN csr_get_app_id(p_resp_application);
1171 FETCH csr_get_app_id INTO l_resp_application_id;
1172 IF csr_get_app_id%NOTFOUND THEN
1173 --
1174 CLOSE csr_get_app_id;
1175 -- Invalid application_short_name
1176 fnd_message.set_name('PER','PER_289516_ADI_INVAL_RESP_APPL');
1177 fnd_message.raise_error;
1178 END IF;
1179 CLOSE csr_get_app_id;
1180 --
1181 -- Now check if integrator exists
1182 OPEN l_int_csr FOR
1183 'SELECT integrator_code ' ||
1184 ' FROM bne_integrators_vl ' ||
1185 ' WHERE user_name = ''' || p_integrator_user_name || ''' ' ||
1186 ' AND application_id = ' || l_intg_application_id;
1187 --
1188 FETCH l_int_csr INTO l_integrator_code;
1189 IF l_int_csr%NOTFOUND THEN
1190 --
1191 CLOSE l_int_csr;
1192 fnd_message.set_name('PER','PER_289428_ADI_INTG_NOT_EXIST');
1193 fnd_message.raise_error;
1194 --
1195 END IF;
1196 CLOSE l_int_csr;
1197 --
1198 --
1199 -- Now check responsibility exists
1200 OPEN csr_get_resp_id(l_resp_application_id, p_responsibility_name);
1201 FETCH csr_get_resp_id INTO l_responsibility_id;
1202 IF csr_get_resp_id%NOTFOUND THEN
1203 --
1204 CLOSE csr_get_resp_id;
1205 --
1206 fnd_message.set_name('PER','PER_289517_ADI_INVAL_RESP');
1207 fnd_message.raise_error;
1208 END IF;
1209 CLOSE csr_get_resp_id;
1210 --
1211 -- Now have a valid app_id/integrator_code and app_id/resp_id
1212 -- combination, so create an entry in the HR_ADI_INTG_RESP table
1213 --
1214 INSERT INTO hr_adi_intg_resp
1215 (resp_association_id,
1216 intg_application_id,
1217 integrator_code,
1218 resp_application_id,
1219 responsibility_id)
1220 VALUES
1221 (hr_adi_intg_resp_s.nextval
1222 ,l_intg_application_id
1223 ,l_integrator_code
1224 ,l_resp_application_id
1225 ,l_responsibility_id);
1226 --
1227 END hr_create_resp_association;
1228 --
1229 -- ----------------------------------------------------------------------------
1230 -- |--------------------< hr_upd_or_del_resp_association >--------------------|
1231 -- ----------------------------------------------------------------------------
1232 --
1233 -- Description:
1234 -- Called to update or delete an entry in the HR_ADI_INTG_RESP table. If
1235 -- the resp associated with an integrator is updated to NULL, then it is
1236 -- removed from the table. Otherwise, the resp_application_id and resp_name
1237 -- fields are updated.
1238 --
1239 -- ----------------------------------------------------------------------------
1240 PROCEDURE hr_upd_or_del_resp_association
1241 (p_resp_association_id IN number
1242 ,p_resp_application IN varchar2 default null
1243 ,p_responsibility_name IN varchar2 default null
1244 ) IS
1245 --
1246 l_exists varchar2(1);
1247 l_application_id number;
1248 l_responsibility_id number;
1249 --
1250 CURSOR csr_chk_exists IS
1251 SELECT 'Y'
1252 FROM hr_adi_intg_resp
1253 WHERE resp_association_id = p_resp_association_id;
1254 --
1255 CURSOR csr_chk_app_exists IS
1256 SELECT application_id
1257 FROM fnd_application
1258 WHERE upper(application_short_name) = upper(p_resp_application);
1259 --
1260 CURSOR csr_chk_resp_exists(l_app_id IN number, l_resp_name IN varchar2) IS
1261 SELECT responsibility_id
1262 FROM fnd_responsibility_vl
1263 WHERE application_id = l_app_id
1264 AND responsibility_name = l_resp_name;
1265 --
1266 BEGIN
1267 --
1268 -- Check entry exists
1269 OPEN csr_chk_exists;
1270 FETCH csr_chk_exists INTO l_exists;
1271 IF csr_chk_exists%NOTFOUND THEN
1272 --
1273 CLOSE csr_chk_exists;
1274 fnd_message.set_name('PER','PER_449900_ADI_INVAL_RESP_ASSC');
1275 fnd_message.raise_error;
1276 --
1277 END IF;
1278 CLOSE csr_chk_exists;
1279 --
1280 -- Handle delete case
1281 --
1282 IF ((p_resp_application IS NULL)
1283 and (p_responsibility_name IS NULL)) THEN
1284 --
1285 -- Delete row from hr_adi_intg_resp
1286 DELETE FROM hr_adi_intg_resp
1287 WHERE resp_association_id = p_resp_association_id;
1288 --
1289 ELSE
1290 -- Update required
1291 IF ((p_resp_application IS NULL) or
1292 (p_responsibility_name IS NULL)) THEN
1293 --
1294 -- Invalid combination - must both have a value to update
1295 fnd_message.set_name('PER','PER_449901_ADI_RESP_VAL_NULL');
1296 fnd_message.raise_error;
1297 END IF;
1298 --
1299 -- Check resp_app_id exists
1300 OPEN csr_chk_app_exists;
1301 FETCH csr_chk_app_exists INTO l_application_id;
1302 IF csr_chk_app_exists%NOTFOUND THEN
1303 --
1304 CLOSE csr_chk_app_exists;
1305 fnd_message.set_name('PER','PER_289516_ADI_INVAL_RESP_APPL');
1306 fnd_message.raise_error;
1307 END IF;
1308 CLOSE csr_chk_app_exists;
1309 --
1310 -- Check resp exists
1311 OPEN csr_chk_resp_exists(l_application_id, p_responsibility_name);
1312 FETCH csr_chk_resp_exists INTO l_responsibility_id;
1313 IF csr_chk_resp_exists%NOTFOUND THEN
1314 --
1315 -- No matching resp, so error
1316 CLOSE csr_chk_resp_exists;
1317 fnd_message.set_name('PER','PER_289517_ADI_INVAL_RESP');
1318 fnd_message.raise_error;
1319 END IF;
1320 CLOSE csr_chk_resp_exists;
1321 --
1322 -- Update the resp
1323 UPDATE hr_adi_intg_resp
1324 SET resp_application_id = l_application_id,
1325 responsibility_id = l_responsibility_id
1326 WHERE resp_association_id = p_resp_association_id;
1327 --
1328 END IF;
1329 END hr_upd_or_del_resp_association;
1330 --
1331 --
1332 -- +--------------------------------------------------------------------------+
1333 -- |--------------------< hr_maint_form_func_association >--------------------|
1334 -- +--------------------------------------------------------------------------+
1335 --
1336 -- Description:
1337 -- Called to create, update or delete entries in the BNE tables that link
1338 -- form functions with integrators.
1339 --
1340 -- +--------------------------------------------------------------------------+
1341 PROCEDURE hr_maint_form_func_association
1342 (p_intg_application IN varchar2
1343 ,p_integrator_user_name IN varchar2
1344 ,p_security_value IN varchar2
1345 ) IS
1346 --
1347 TYPE IntCurTyp IS REF CURSOR;
1348 l_int_csr IntCurTyp;
1349 l_plsql varchar2(2000);
1350 l_intg_app_id number;
1351 l_integrator_code varchar2(30);
1352 l_exists varchar2(1);
1353 l_security_app_id number;
1354 l_security_code varchar2(30);
1355 l_user_id number;
1356 --
1357 --
1358 CURSOR csr_chk_app_exists IS
1359 SELECT application_id
1360 FROM fnd_application
1361 WHERE upper(application_short_name) = upper(p_intg_application);
1362 --
1363 --
1364 BEGIN
1365 --
1366 --
1367 SELECT fnd_global.user_id
1368 INTO l_user_id
1369 FROM dual;
1370 --
1371 -- Check for NULLS - Both Application and Integrator Name must have
1372 -- a value
1373 --
1374 IF p_intg_application IS NULL THEN
1375 fnd_message.set_name('PER','PER_289514_ADI_INVAL_INTG_APPL');
1376 fnd_message.raise_error;
1377 END IF;
1378 --
1379 IF p_integrator_user_name IS NULL THEN
1380 fnd_message.set_name('PER','PER_289428_ADI_INTG_NOT_EXIST');
1381 fnd_message.raise_error;
1382 END IF;
1383 --
1384 -- Check Integrator App exists
1385 --
1386 OPEN csr_chk_app_exists;
1387 FETCH csr_chk_app_exists INTO l_intg_app_id;
1388 IF csr_chk_app_exists%NOTFOUND THEN
1389 --
1390 CLOSE csr_chk_app_exists;
1391 fnd_message.set_name('PER','PER_289514_ADI_INVAL_INTG_APPL');
1392 fnd_message.raise_error;
1393 END IF;
1394 CLOSE csr_chk_app_exists;
1395 --
1396 -- Check Integrator exists
1397 --
1398 OPEN l_int_csr FOR
1399 'SELECT integrator_code ' ||
1400 ' FROM bne_integrators_vl ' ||
1401 ' WHERE user_name = ''' || p_integrator_user_name || ''' ' ||
1402 ' AND application_id = ' || l_intg_app_id;
1403 --
1404 FETCH l_int_csr INTO l_integrator_code;
1405 IF l_int_csr%NOTFOUND THEN
1406 --
1407 CLOSE l_int_csr;
1408 fnd_message.set_name('PER','PER_289428_ADI_INTG_NOT_EXIST');
1409 fnd_message.raise_error;
1410 END IF;
1411 CLOSE l_int_csr;
1412 --
1413 -- Check if Form Function rules exist for this Integrator
1414 --
1415 OPEN l_int_csr FOR
1416 'SELECT security_rule_app_id ' ||
1417 ' , security_rule_code ' ||
1418 ' FROM bne_secured_objects ' ||
1419 ' WHERE application_id = ' || l_intg_app_id ||
1420 ' AND object_code = ''' || l_integrator_code || ''' ' ||
1421 ' AND object_type = ''INTEGRATOR''';
1422 --
1423 FETCH l_int_csr INTO
1424 l_security_app_id
1425 , l_security_code;
1426 IF l_int_csr%NOTFOUND THEN
1427 l_exists := 'N';
1428 ELSE
1429 l_exists := 'Y';
1430 END IF;
1431 CLOSE l_int_csr;
1432 --
1433 IF (p_security_value IS NULL) THEN
1434 IF (l_exists = 'Y') THEN
1435 --
1436 -- p_security_value being NULL means that we are deleting a rule.
1437 -- Note that in this case, if l_exists were to be 'N' then we would
1438 -- be trying to delete a rule that does not exist so no action required
1439 -- hence there is no code here for
1440 -- p_security_value IS NULL and l_exists = 'N'
1441 --
1442 l_plsql :=
1443 'BEGIN ' ||
1444 'BNE_SECURITY_UTILS_PKG.DELETE_OBJECT_RULES' ||
1445 ' (p_object_app_id => :1 ' ||
1446 ' ,p_object_code => :2 ' ||
1447 ' ,p_object_type => ''' || 'INTEGRATOR' || ''' ' ||
1448 ' ,p_security_app_id => :3 ' ||
1449 ' ,p_security_code => :4 ' ||
1450 ' ); ' ||
1451 'END; ';
1452 --
1453 EXECUTE IMMEDIATE l_plsql
1454 USING IN l_intg_app_id,
1455 IN l_integrator_code,
1456 IN l_security_app_id,
1457 IN l_security_code;
1458 END IF;
1459 --
1460 ELSE
1461 IF (l_exists = 'Y') THEN
1462 --
1463 -- This means that user is updating a rule
1464 --
1465 l_plsql :=
1466 'BEGIN ' ||
1467 'BNE_SECURITY_UTILS_PKG.UPDATE_OBJECT_RULES' ||
1468 ' (p_object_app_id => :1 ' ||
1469 ' ,p_object_code => :2 ' ||
1470 ' ,p_object_type => ''' || 'INTEGRATOR' || ''' ' ||
1471 ' ,p_security_app_id => :3 ' ||
1472 ' ,p_security_code => :4 ' ||
1473 ' ,P_SECURITY_TYPE => ''' || 'FUNCTION' || ''' ' ||
1474 ' ,P_SECURITY_VALUE => :5 ' ||
1475 ' ,P_USER_ID => :6 ' ||
1476 ' ); ' ||
1477 'END; ';
1478 --
1479 EXECUTE IMMEDIATE l_plsql
1480 USING IN l_intg_app_id,
1481 IN l_integrator_code,
1482 IN l_security_app_id,
1483 IN l_security_code,
1484 IN p_security_value,
1485 IN l_user_id;
1486 --
1487 ELSE
1488 --
1489 -- This means that user is creating a rule
1490 --
1491 l_plsql :=
1492 'BEGIN ' ||
1493 'BNE_SECURITY_UTILS_PKG.ADD_OBJECT_RULES' ||
1494 ' (p_application_id => :1 ' ||
1495 ' ,p_object_code => :2 ' ||
1496 ' ,p_object_type => ''' || 'INTEGRATOR' || ''' ' ||
1497 ' ,p_security_code => :3 ' ||
1498 ' ,P_SECURITY_TYPE => ''' || 'FUNCTION' || ''' ' ||
1499 ' ,P_SECURITY_VALUE => :4 ' ||
1500 ' ,P_USER_ID => :5 ' ||
1501 ' ); ' ||
1502 'END; ';
1503 --
1504 EXECUTE IMMEDIATE l_plsql
1505 USING IN l_intg_app_id,
1506 IN l_integrator_code,
1507 IN l_integrator_code,
1508 IN p_security_value,
1509 IN l_user_id;
1510 --
1511 END IF;
1512 END IF;
1513 END hr_maint_form_func_association;
1514 --
1515 FUNCTION fetchname
1516 (p_number IN number
1517 ,p_application_id IN number
1518 ,p_param_list_code IN varchar2) RETURN varchar2 IS
1519 --
1520 TYPE IntCurTyp IS REF CURSOR;
1521 l_int_csr IntCurTyp;
1522 --
1523 l_param_name varchar2(240);
1524 l_datatype number;
1525 l_prompt varchar2(240);
1526 --
1527 l_return varchar2(240);
1528 --
1529 BEGIN
1530 --
1531 OPEN l_int_csr FOR
1532 'SELECT i.param_name, d.datatype, t.prompt_left ' ||
1533 ' FROM bne_param_list_items i, bne_param_defns_b d, bne_param_defns_tl t ' ||
1534 ' WHERE i.application_id = ' || p_application_id ||
1535 ' AND i.param_list_code = ''' || p_param_list_code || ''' ' ||
1536 ' AND i.sequence_num = ' || p_number ||
1537 ' AND i.param_defn_code = d.param_defn_code ' ||
1538 ' AND i.param_defn_app_id = d.application_id ' ||
1539 ' AND i.param_defn_code = t.param_defn_code ' ||
1540 ' AND i.param_defn_app_id = t.application_id ';
1541 --
1542 FETCH l_int_csr INTO l_param_name, l_datatype, l_prompt;
1543 --
1544 IF l_int_csr%NOTFOUND THEN
1545 l_return := '';
1546 ELSE
1547 l_return := l_param_name;
1548 END IF;
1549 CLOSE l_int_csr;
1550 --
1551 RETURN l_return;
1552 --
1553 END fetchname;
1554 --
1555 FUNCTION fetchtype
1556 (p_number IN number
1557 ,p_application_id IN number
1558 ,p_param_list_code IN varchar2) RETURN varchar2 IS
1559 --
1560 TYPE IntCurTyp IS REF CURSOR;
1561 l_int_csr IntCurTyp;
1562 --
1563 l_param_name varchar2(240);
1564 l_datatype varchar2(240);
1565 l_prompt varchar2(240);
1566 --
1567 l_return varchar2(240);
1568 --
1569 BEGIN
1570 --
1571 /* MODIFIED THIS CURSOR FOR FIXING BUG#4080461
1572 OPEN l_int_csr FOR
1573 'SELECT i.param_name, decode(d.datatype,1,''Varchar2'',' ||
1574 ' 2,''Number'',3,''Date'',''Varchar2''), t.prompt_left ' ||
1575 ' FROM bne_param_list_items i, bne_param_defns_b d, bne_param_defns_tl t ' ||
1576 ' WHERE i.application_id = ' || p_application_id ||
1577 ' AND i.param_list_code = ''' || p_param_list_code || ''' ' ||
1578 ' AND i.sequence_num = ' || p_number ||
1579 ' AND i.param_defn_code = d.param_defn_code ' ||
1580 ' AND i.param_defn_app_id = d.application_id ' ||
1581 ' AND i.param_defn_code = t.param_defn_code ' ||
1582 ' AND i.param_defn_app_id = t.application_id ';
1583 */
1584 OPEN l_int_csr FOR
1585 'SELECT i.param_name, decode(d.datatype, '||
1586 ' 1,hr_general.decode_lookup(''HR_WEB_ADI_TYPES'',''1''), '||
1587 ' 2,hr_general.decode_lookup(''HR_WEB_ADI_TYPES'',''2''), '||
1588 ' 3,hr_general.decode_lookup(''HR_WEB_ADI_TYPES'',''3''), '||
1589 ' hr_general.decode_lookup(''HR_WEB_ADI_TYPES'',''1'')), '||
1590 ' t.prompt_left ' ||
1591 ' FROM bne_param_list_items i, bne_param_defns_b d, bne_param_defns_tl t ' ||
1592 ' WHERE i.application_id = ' || p_application_id ||
1593 ' AND i.param_list_code = ''' || p_param_list_code || ''' ' ||
1594 ' AND i.sequence_num = ' || p_number ||
1595 ' AND i.param_defn_code = d.param_defn_code ' ||
1596 ' AND i.param_defn_app_id = d.application_id ' ||
1597 ' AND i.param_defn_code = t.param_defn_code ' ||
1598 ' AND i.param_defn_app_id = t.application_id ';
1599
1600 --
1601 FETCH l_int_csr INTO l_param_name, l_datatype, l_prompt;
1602 --
1603 IF l_int_csr%NOTFOUND THEN
1604 l_return := '';
1605 ELSE
1606 l_return := l_datatype;
1607 END IF;
1608 CLOSE l_int_csr;
1609 --
1610 RETURN l_return;
1611 --
1612 END fetchtype;
1613 --
1614 FUNCTION fetchprompt
1615 (p_number IN number
1616 ,p_application_id IN number
1617 ,p_param_list_code IN varchar2) RETURN varchar2 IS
1618 --
1619 TYPE IntCurTyp IS REF CURSOR;
1620 l_int_csr IntCurTyp;
1621 --
1622 l_param_name varchar2(240);
1623 l_datatype number;
1624 l_prompt varchar2(240);
1625 --
1626 l_return varchar2(240);
1627 --
1628 BEGIN
1629 --
1630 OPEN l_int_csr FOR
1631 'SELECT i.param_name, d.datatype, t.prompt_left ' ||
1632 ' FROM bne_param_list_items i, bne_param_defns_b d, bne_param_defns_tl t ' ||
1633 ' WHERE i.application_id = ' || p_application_id ||
1634 ' AND i.param_list_code = ''' || p_param_list_code || ''' ' ||
1635 ' AND i.sequence_num = ' || p_number ||
1636 ' AND i.param_defn_code = d.param_defn_code ' ||
1637 ' AND i.param_defn_app_id = d.application_id ' ||
1638 ' AND i.param_defn_code = t.param_defn_code ' ||
1639 ' AND i.param_defn_app_id = t.application_id ';
1640 --
1641 FETCH l_int_csr INTO l_param_name, l_datatype, l_prompt;
1642 --
1643 IF l_int_csr%NOTFOUND THEN
1644 l_return := '';
1645 ELSE
1646 l_return := l_prompt;
1647 END IF;
1648 CLOSE l_int_csr;
1649 --
1650 RETURN l_return;
1651 --
1652 END fetchprompt;
1653 --
1654
1655 --
1656 END hr_integration_utils;