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