[Home] [Help]
PACKAGE BODY: APPS.HR_ADE_ADI_DATA_SETUP
Source
1 PACKAGE BODY hr_ade_adi_data_setup AS
2 /* $Header: peadeset.pkb 120.3 2008/04/29 13:04:18 dbansal ship $ */
3 -------------------------------------------------------------------------------
4 -------------------------< Procedures private to package >--------------------
5 -------------------------------------------------------------------------------
6 --
7 -- ---------------------------------------------------------------------------
8 -- -- set_new_session_flag --
9 -- ---------------------------------------------------------------------------
10 --
11 PROCEDURE set_new_session_flag
12 (p_application_id IN number
13 ,p_integrator_code IN varchar2) IS
14 --
15 -- Find if NEW SESSION functionality installed on environment and,
16 -- if it is, update the integrator to initiate cloning
17 --
18 l_cloning VARCHAR2(1);
19 l_out_industry VARCHAR2(30);
20 l_out_oracle_schema VARCHAR2(30);
21 l_out_status VARCHAR2(30);
22 l_plsql varchar2(2000);
23 l_value BOOLEAN;
24 --
25 --
26 CURSOR csr_test_for_flag (l_oracle_schema IN varchar) IS
27 SELECT 'Y'
28 FROM all_tab_columns tc
29 WHERE tc.table_name = 'BNE_INTEGRATORS_B'
30 AND tc.column_name = 'NEW_SESSION_FLAG'
31 AND tc.owner = l_oracle_schema;
32 --
33 --
34 BEGIN
35 --
36 l_value := FND_INSTALLATION.GET_APP_INFO ('BNE', l_out_status,
37 l_out_industry, l_out_oracle_schema);
38 --
39 l_cloning := 'N';
40 --
41 OPEN csr_test_for_flag(l_out_oracle_schema);
42 FETCH csr_test_for_flag INTO l_cloning;
43 IF csr_test_for_flag%NOTFOUND THEN
44 --
45 l_cloning := 'N';
46 --
47 END IF;
48 CLOSE csr_test_for_flag;
49 --
50 IF l_cloning = 'Y' THEN
51 --
52 l_plsql :=
53 'BEGIN ' ||
54 ' UPDATE bne_integrators_b ' ||
55 ' SET new_session_flag = ''Y'' ' ||
56 ' WHERE application_id = :1 ' ||
57 ' AND integrator_code = :2; ' ||
58 'END;';
59 --
60 EXECUTE IMMEDIATE l_plsql
61 USING IN p_application_id,
62 p_integrator_code;
63 --
64 END IF;
65 --
66 END set_new_session_flag;
67 --
68 -------------------------------------------------------------------
69 -- create_download_data --
70 -------------------------------------------------------------------
71 --
72 PROCEDURE create_download_data
73 (p_application_id IN number
74 ,p_integrator_user_name IN varchar2
75 ,p_view_name IN varchar2
76 ,p_form_name IN varchar2 default null
77 ,p_language IN varchar2
78 ,p_user_id IN number ) IS
79 --
80 TYPE CSR_TYP IS REF CURSOR;
81 csr_int CSR_TYP;
82 -- constants
83 c_hr_content CONSTANT VARCHAR2(50) :=
84 'oracle.apps.per.webui.control.BneHrSQLControl';
85 -- local variables
86 l_content_code varchar2(30);
87 l_interface_code varchar2(30);
88 l_mapping_code varchar2(30);
89 --
90 l_integrator_code varchar2(60);
91 l_igr_csr_id varchar2(60);
92 l_num_columns NUMBER;
93 l_num_content NUMBER;
94 l_param_list_code varchar2(30) DEFAULT NULL;
95 --
96 l_user_integrator_name varchar2(240);
97 l_object_code varchar2(20) ;
98 l_object_num number;
99 --
100 l_bne_value VARCHAR2(2000);
101 l_sql_statement VARCHAR2(2000);
102 --
103 BEGIN
104 --
105 ------< Validate parameters >------
106 --
107 IF p_view_name IS NULL THEN
108 fnd_message.set_name('PER','PER_289873_INVALID_VIEW_NAME');
109 fnd_message.raise_error;
110 END IF;
111 --
112 IF p_form_name <> 'LETTER' AND p_form_name <> 'GENERAL' THEN
113 --Check Form Name exists
114 l_bne_value := 'FND_FORM_VL';
115 OPEN csr_int FOR
116 ' select FORM_NAME' ||
117 ' from '||l_bne_value ||
118 ' where FORM_NAME = '''||
119 p_form_name || '''';
120 FETCH csr_int INTO l_igr_csr_id;
121 IF csr_int%NOTFOUND THEN
122 --invalid form name supplied
123 fnd_message.set_name('PER','PER_289922_INVAL_FM_NAME');
124 fnd_message.raise_error;
125 END IF;
126 END IF;
127 --
128 --Check Application ID
129 l_bne_value := 'FND_APPLICATION';
130 OPEN csr_int FOR
131 ' select APPLICATION_SHORT_NAME' ||
132 ' from '||l_bne_value ||
133 ' where APPLICATION_ID ='||
134 p_application_id;
135 FETCH csr_int INTO l_igr_csr_id;
136 IF csr_int%NOTFOUND THEN
137 --invalid form name supplied
138 fnd_message.set_name('PER','PER_289921_INVAL_APP_ID');
139 fnd_message.raise_error;
140 END IF;
141 --
142 --Check integrator_user_name
143 l_bne_value := 'bne_integrators_tl';
144 OPEN csr_int FOR
145 ' select USER_NAME' ||
146 ' from '||l_bne_value ||
147 ' where USER_NAME ='''||
148 p_integrator_user_name ||'''';
149 FETCH csr_int INTO l_igr_csr_id;
150 IF csr_int%FOUND THEN
151 --invalid form name supplied
152 fnd_message.set_name('PER','PER_289923_USER_NAME_EXISTS');
153 fnd_message.raise_error;
154 END IF;
155 --
156 ---------< Create MetaData >-------
157 --
158 --create object code
159 SELECT hr_adi_object_code_s.NEXTVAL
160 INTO l_object_num
161 FROM dual;
162 -- If Style is "GENERAL", prepend object code with "GENERAL_"
163 IF p_form_name = 'GENERAL' THEN
164 l_object_code := 'GENERAL_'||to_char(l_object_num);
165 ELSE
166 l_object_code := 'HR_'||to_char(l_object_num);
167 END IF;
168 l_user_integrator_name := p_integrator_user_name;
169 --
170 -- STEP 1. Create Integrator
171 --
172 -- check to see if integrator name already exists
173 l_bne_value := 'bne_integrators_b';
174 OPEN csr_int FOR
175 'SELECT integrator_code' ||
176 ' FROM ' || l_bne_value ||
177 ' WHERE integrator_code = ''' ||
178 l_object_code ||'_INTG'||
179 ''' AND application_id = '
180 || p_application_id;
181 --
182 FETCH csr_int INTO l_igr_csr_id;
183 IF csr_int%FOUND THEN
184 CLOSE csr_int;
185 fnd_message.set_name('PER','PER_289872_ADI_INTGR_EXISTS');
186 fnd_message.raise_error;
187 END IF;
188 CLOSE csr_int;
189 --
190 l_bne_value := 'bne_integrator_utils.create_integrator_no_content';
191 l_sql_statement :=
192 'BEGIN '||
193 l_bne_value||
194 ' (:1,:2,:3,:4,:5,:6,:7);' ||
195 'END;';
196 --
197 EXECUTE IMMEDIATE l_sql_statement
198 USING IN p_application_id
199 , IN l_object_code
200 , IN l_user_integrator_name
201 , IN p_user_id
202 , IN p_language
203 , IN p_language
204 , OUT l_integrator_code;
205 --
206 l_bne_value := 'bne_integrators_b';
207 OPEN csr_int FOR
208 'SELECT integrator_code' ||
209 ' FROM ' || l_bne_value ||
210 ' WHERE integrator_code = ''' ||
211 l_integrator_code ||
212 ''' AND application_id = '
213 || p_application_id;
214 --
215 FETCH csr_int INTO l_igr_csr_id;
216 IF csr_int%NOTFOUND THEN
217 CLOSE csr_int;
218 fnd_message.set_name('PER','PER_289864_ADI_INTGR_INVAL');
219 fnd_message.raise_error;
220 /*ELSE
221 set_new_session_flag
222 (p_application_id => p_application_id
223 ,p_integrator_code => l_integrator_code);*/
224 END IF;
225 CLOSE csr_int;
226 -- ??? maybe also check for null???
227 --
228 -- STEP 2. Create Content
229 --
230 l_bne_value := 'bne_content_utils.create_content';
231 l_sql_statement :=
232 'BEGIN '||
233 l_bne_value||
234 ' (:1,:2,:3,:4,:5,:6,:7,:8,:9);' ||
235 'END;';
236 EXECUTE IMMEDIATE l_sql_statement
237 USING IN p_application_id
238 ,IN l_object_code
239 ,IN l_integrator_code
240 ,IN upper(p_view_name)
241 ,IN p_language
242 ,IN p_language
243 ,IN c_hr_content
244 ,IN p_user_id
245 , OUT l_content_code;
246 --
247 l_bne_value := 'bne_contents_b';
248 OPEN csr_int FOR
249 'SELECT content_code ' ||
250 ' FROM ' || l_bne_value ||
251 ' WHERE integrator_code =''' ||
252 l_integrator_code ||
253 ''' AND application_id = '|| p_application_id;
254
255 FETCH csr_int INTO l_igr_csr_id;
256 IF csr_int%NOTFOUND THEN
257 CLOSE csr_int;
258 fnd_message.set_name('PER','PER_289865_ADI_CONTENT_FAIL');
259 fnd_message.raise_error;
260 END IF;
261 CLOSE csr_int;
262 --
263 -- STEP 3. Create Content Columns
264 --
265 l_bne_value := 'bne_content_utils.create_content_cols_from_view';
266 l_sql_statement :=
267 'BEGIN ' ||
268 l_bne_value ||
269 ' (:1,:2,:3,:4,:5,:6);' ||
270 'END;';
271 EXECUTE IMMEDIATE l_sql_statement
272 USING IN p_application_id
273 ,IN l_content_code
274 ,IN upper(p_view_name)
275 ,IN p_language
276 ,IN p_language
277 ,IN p_user_id;
278 --
279 l_bne_value := 'bne_content_cols_b';
280 OPEN csr_int FOR
281 'SELECT count(*) ' ||
282 ' FROM '|| l_bne_value ||
283 ' WHERE content_code =''' ||
284 l_content_code ||''' AND ' ||
285 ' application_id = '|| p_application_id;
286 FETCH csr_int INTO l_num_columns;
287 IF l_num_columns <1 THEN
288 CLOSE csr_int;
289 fnd_message.set_name('PER','PER_289866_ADI_CONT_COL_FAIL');
290 fnd_message.raise_error;
291 END IF;
292 CLOSE csr_int;
293 --
294 -- Step 4. Enable content for reporting
295 --
296 l_bne_value := 'bne_content_utils.enable_content_for_reporting';
297 l_sql_statement :=
298 'BEGIN ' ||
299 l_bne_value ||
300 ' (:1,:2,:3,:4,:5,:6,:7,:8,:9);' ||
301 'END;';
302 EXECUTE IMMEDIATE l_sql_statement
303 USING IN p_application_id
304 ,IN l_object_code
305 ,IN l_integrator_code
306 ,IN l_content_code
307 ,IN p_language
308 ,IN p_language
309 ,IN p_user_id
310 , OUT l_interface_code
311 , OUT l_mapping_code;
312 --
313 l_bne_value := 'bne_interface_cols_b';
314 --check interface code
315 OPEN csr_int FOR
316 'SELECT count(*) ' ||
317 ' FROM ' || l_bne_value ||
318 ' WHERE interface_code = ' ||
319 ' (SELECT interface_code' ||
320 ' FROM bne_interfaces_b' ||
321 ' WHERE integrator_code = ''' ||
322 l_integrator_code ||
323 ''' AND application_id = '||
324 p_application_id || ')';
325 FETCH csr_int INTO l_num_content;
326 IF l_num_content <> l_num_columns THEN
327 CLOSE csr_int;
328 fnd_message.set_name('PER','PER_289867_ADI_CONT_ENABL_FAIL');
329 fnd_message.raise_error;
330 END IF;
331 CLOSE csr_int;
332 --check mapping code
333 l_bne_value := 'bne_mappings_b';
334 OPEN csr_int FOR
335 ' SELECT mapping_code'||
336 ' FROM ' || l_bne_value ||
337 ' WHERE mapping_code = ''' ||
338 l_mapping_code ||
339 ''' AND application_id ='||
340 p_application_id;
341 FETCH csr_int INTO l_igr_csr_id;
342 IF csr_int%NOTFOUND THEN
343 CLOSE csr_int;
344 fnd_message.set_name('PER','PER_289868_ADI_CONT_MAP_FAIL');
345 fnd_message.raise_error;
346 END IF;
347 CLOSE csr_int;
348 --
349 -- Step 5. Add HR Param list to content
350 --
351 --if not general
352 l_bne_value := 'hr_integration_utils.add_hr_param_list_to_content';
353 l_sql_statement :=
354 'BEGIN ' ||
355 l_bne_value ||
356 ' (:1,:2);' ||
357 'END;';
358 EXECUTE IMMEDIATE l_sql_statement
359 USING IN p_application_id
360 ,IN l_content_code;
361 --
362 l_bne_value := 'bne_contents_b';
363 OPEN csr_int FOR
364 'SELECT param_list_code ' ||
365 ' FROM ' || l_bne_value ||
366 ' WHERE content_code = ''' ||
367 l_content_code ||
368 ''' AND application_id = '||
369 p_application_id;
370
371 FETCH csr_int INTO l_param_list_code;
372 IF l_param_list_code IS NULL THEN
373 CLOSE csr_int;
374 fnd_message.set_name('PER','PER_289869_ADI_ADD_PARAM_FAIL');
375 fnd_message.raise_error;
376 END IF;
377 CLOSE csr_int;
378 --
379 -- Step 6. Add Integrator to LETTER group
380 --
381 IF p_form_name IS NOT NULL and
382 p_form_name <> 'GENERAL' THEN
383 l_user_integrator_name := to_char(p_application_id) || ':' || l_integrator_code;
384 l_bne_value := 'hr_integration_utils.register_integrator_to_form';
385 l_sql_statement :=
386 'BEGIN ' ||
387 l_bne_value ||
388 ' (:1,:2); ' ||
389 'END;';
390 EXECUTE IMMEDIATE l_sql_statement
391 USING IN l_user_integrator_name
392 ,IN p_form_name;
393 END IF;
394 --
395 END create_download_data;
396 --
397 -----------------------------------------------------------------------------
398 -- create_upload_data --
399 -----------------------------------------------------------------------------
400 --
401 PROCEDURE create_upload_data
402 (p_application_id IN number
403 ,p_integrator_user_name IN varchar2
404 ,p_api_package_name IN varchar2
405 ,p_api_procedure_name IN varchar2
406 ,p_interface_user_name IN varchar2
407 ,p_interface_param_name IN varchar2
408 ,p_api_type IN varchar2
409 ,p_api_return_type IN varchar2
410 ,p_language IN varchar2
411 ,p_user_id IN number ) IS
412 --
413 --
414 -- Upload api, with "none" and "text file" content
415 --
416 -- Required actions by user:
417 -- - Create Layout
418 -- - create mapping for text file mapping
419 --
420 TYPE CSR_TYP IS REF CURSOR;
421 csr_int CSR_TYP;
422 -- local variables
423 l_integrator_code varchar2(60);
424 l_param_list_code varchar2(60) DEFAULT NULL;
425 l_interface_code varchar2(60);
426 l_content_code varchar2(60);
427 l_igr_csr_id varchar2(60);
428 --
429 l_object_code varchar2(30);
430 l_object_num number;
431 --
432 l_bne_value VARCHAR2(2000);
433 l_sql_statement VARCHAR2(2000);
434 --
435 BEGIN
436 --
437 --------< validate parameters >---------------
441 ELSIF p_api_procedure_name IS NULL THEN
438 IF p_api_package_name IS NULL THEN
439 fnd_message.set_name('PER','PER_289874_INVAL_PGK_NAME');
440 fnd_message.raise_error;
442 fnd_message.set_name('PER','PER_289875_INVAL_PROC_NAME');
443 fnd_message.raise_error;
444 ELSIF p_interface_user_name IS NULL THEN
445 fnd_message.set_name('PER','PER_289876_INVAL_IFACE_NAME');
446 fnd_message.raise_error;
447 ELSIF p_interface_param_name IS NULL THEN
448 fnd_message.set_name('PER','PER_289877_INVAL_PARAM_LIST');
449 fnd_message.raise_error;
450 ELSIF p_api_type IS NULL THEN
451 fnd_message.set_name('PER','PER_289878_INVAL_API_TYPE');
452 fnd_message.raise_error;
453 END IF;
454 --Check Application ID
455 l_bne_value := 'FND_APPLICATION';
456 OPEN csr_int FOR
457 ' select APPLICATION_SHORT_NAME' ||
458 ' from '||l_bne_value ||
459 ' where APPLICATION_ID ='||
460 p_application_id;
461 FETCH csr_int INTO l_igr_csr_id;
462 IF csr_int%NOTFOUND THEN
463 --invalid form name supplied
464 fnd_message.set_name('PER','PER_289921_INVAL_APP_ID');
465 fnd_message.raise_error;
466 END IF;
467 --Check integrator_user_name
468 l_bne_value := 'bne_integrators_tl';
469 OPEN csr_int FOR
470 ' select USER_NAME' ||
471 ' from '||l_bne_value ||
472 ' where USER_NAME ='''||
473 p_integrator_user_name ||'''';
474 FETCH csr_int INTO l_igr_csr_id;
475 IF csr_int%FOUND THEN
476 --invalid form name supplied
477 fnd_message.set_name('PER','PER_289923_USER_NAME_EXISTS');
478 fnd_message.raise_error;
479 END IF;
480 --
481 --Check package and procedure name
482 l_bne_value := 'SYS.ARGUMENT$ A, DBA_OBJECTS B';
483 OPEN csr_int FOR
484 ' select 1' ||
485 ' from '||l_bne_value ||
486 ' where A.OBJ# = B.OBJECT_ID' ||
487 ' AND B.OBJECT_NAME = '''||P_API_PACKAGE_NAME ||
488 ''' AND A.PROCEDURE$ = ''' ||P_API_PROCEDURE_NAME || '''';
489 FETCH csr_int INTO l_igr_csr_id;
490 IF csr_int%NOTFOUND THEN
491 --invalid package/procedure name supplied
492 fnd_message.set_name('PER','PER_289931_API_NOT_EXIST');
493 fnd_message.raise_error;
494 END IF;
495 --
496 -------< Create MetaData >---------
497 --
498 --create object code
499 SELECT hr_adi_object_code_s.NEXTVAL
500 INTO l_object_num
501 FROM dual;
502 l_object_code := 'GENERAL_'||to_char(l_object_num);
503 --
504 -- STEP 1. Create Integrator
505 --
506 -- check to see if integrator name already exists
507 l_bne_value := 'bne_integrators_b';
508 OPEN csr_int FOR
509 'SELECT integrator_code' ||
510 ' FROM ' || l_bne_value ||
511 ' WHERE integrator_code = ''' ||
512 l_object_code ||'_INTG'||
513 ''' AND application_id = '
514 || p_application_id;
515 --
516 FETCH csr_int INTO l_igr_csr_id;
517 IF csr_int%FOUND THEN
518 CLOSE csr_int;
519 fnd_message.set_name('PER','PER_289872_ADI_INTGR_EXISTS');
520 fnd_message.raise_error;
521 END IF;
522 CLOSE csr_int;
523 --
524 l_bne_value := 'bne_integrator_utils.create_integrator';
525 l_sql_statement :=
526 'BEGIN '||
527 l_bne_value||
528 ' (:1,:2,:3,:4,:5,:6,:7); ' ||
529 'END;';
530
531 EXECUTE IMMEDIATE l_sql_statement
532 USING IN p_application_id
533 ,IN l_object_code
534 ,IN p_integrator_user_name
535 ,IN p_language
536 ,IN p_language
537 ,IN p_user_id
538 , OUT l_integrator_code;
539 --
540 l_bne_value := 'bne_contents_b';
541 OPEN csr_int FOR
542 --check integrator created and has content
543 'SELECT integrator_code' ||
544 ' FROM ' || l_bne_value ||
545 ' WHERE integrator_code = '||
546 '(SELECT integrator_code '||
547 'FROM bne_integrators_b '||
548 'WHERE integrator_code =''' ||
549 l_integrator_code ||
550 ''' AND application_id = ' ||
551 p_application_id||
552 ') AND application_id ='||
553 p_application_id;
554
555 FETCH csr_int INTO l_igr_csr_id;
556 IF csr_int%NOTFOUND THEN
557 CLOSE csr_int;
558 fnd_message.set_name('PER','PER_289864_ADI_INTGR_INVAL');
559 fnd_message.raise_error;
560 /*ELSE
561 set_new_session_flag
562 (p_application_id => p_application_id
563 ,p_integrator_code => l_integrator_code);*/
564 END IF;
565 CLOSE csr_int;
566 --
567 -- STEP 2. Create Interface for API
568 --
569 l_bne_value := 'bne_integrator_utils.create_interface_for_api';
570 l_sql_statement :=
571 'BEGIN ' ||
572 l_bne_value ||
573 ' (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15); ' ||
574 'END;';
575 --
576 EXECUTE IMMEDIATE l_sql_statement
577 USING IN p_application_id
581 ,IN upper(p_api_procedure_name)
578 ,IN l_object_code
579 ,IN l_integrator_code
580 ,IN upper(p_api_package_name)
582 ,IN p_interface_user_name
583 ,IN p_interface_param_name
584 ,IN p_api_type
585 ,IN p_api_return_type
586 ,IN 5
587 ,IN p_language
588 ,IN p_language
589 ,IN p_user_id
590 , OUT l_param_list_code
591 , OUT l_interface_code;
592 --
593 l_bne_value := 'bne_interface_cols_b';
594 OPEN csr_int FOR
595 --check interface created and has columns
596 ' SELECT interface_code '||
597 ' FROM '|| l_bne_value ||
598 ' WHERE interface_code = (' ||
599 ' SELECT interface_code'||
600 ' FROM bne_interfaces_b' ||
601 ' WHERE interface_code = ''' ||
602 l_interface_code ||
603 ''' AND application_id ='||
604 p_application_id ||
605 ') AND application_id = '||
606 p_application_id;
607 FETCH csr_int INTO l_igr_csr_id;
608 IF csr_int%NOTFOUND THEN
609 CLOSE csr_int;
610 fnd_message.set_name('PER','PER_289870_ADI_IFACE_FAIL');
611 fnd_message.raise_error;
612 END IF;
613 CLOSE csr_int;
614 --
615 -- Step 3. Set upload param list for integrator
616 --
617 l_bne_value := 'hr_integration_utils.add_hr_upload_list_to_integ';
618 l_sql_statement :=
619 'BEGIN ' ||
620 l_bne_value ||
621 ' (:1,:2); ' ||
622 'END;';
623 --
624 EXECUTE IMMEDIATE l_sql_statement
625 USING IN p_application_id
626 ,In l_integrator_code;
627 --
628 -- Step 4: Create content for text file input
629 --
630 l_bne_value := 'bne_content_utils.create_content_text';
631 l_sql_statement :=
632 'BEGIN ' ||
633 l_bne_value ||
634 ' (:1,:2,:3,:4,:5,null,:6,:7,:8,:9); ' ||
635 'END;';
636 --
637 --create content_code object code
638 l_object_code := l_object_code ||'A';
639 --
640 EXECUTE IMMEDIATE l_sql_statement
641 USING IN p_application_id
642 ,IN l_object_code
643 ,IN l_integrator_code
644 ,IN 'Text File'
645 ,IN 128
646 -- col prefix is null
647 ,IN p_language
648 ,IN p_language
649 ,IN p_user_id
650 , OUT l_content_code;
651 --
652 l_bne_value := 'bne_contents_b';
653 OPEN csr_int FOR
654 ' SELECT content_code'||
655 ' FROM ' || l_bne_value ||
656 ' WHERE content_code = ''' ||
657 l_content_code ||
658 ''' AND application_id ='||
659 p_application_id;
660 FETCH csr_int INTO l_igr_csr_id;
661 IF csr_int%NOTFOUND THEN
662 CLOSE csr_int;
663 fnd_message.set_name('PER','PER_289871_CREATE_TEXT_FAIL');
664 fnd_message.raise_error;
665 END IF;
666 CLOSE csr_int;
667 --
668 --
669 END create_upload_data;
670 --
671 -----------------------------------------------------------------------------
672 -- create_update_data --
673 -----------------------------------------------------------------------------
674 --
675 PROCEDURE create_update_data
676 (p_application_id IN number
677 ,p_integrator_user_name IN varchar2
678 ,p_api_package_name IN varchar2
679 ,p_api_procedure_name IN varchar2
680 ,p_interface_user_name IN varchar2
681 ,p_interface_param_name IN varchar2
682 ,p_api_type IN varchar2
683 ,p_api_return_type IN varchar2
684 ,p_view_name IN varchar2
685 ,p_form_name IN varchar2
686 ,p_language IN varchar2
687 ,p_user_id IN number) IS
688 --
689 -- Upload api (update-style api's)
690 --
691 TYPE CSR_TYP IS REF CURSOR;
692 csr_int CSR_TYP;
693 -- constants
694 c_hr_content CONSTANT VARCHAR2(50) :=
695 'oracle.apps.per.webui.control.BneHrSQLControl';
696 -- local variables
697 l_integrator_code varchar2(60);
698 l_param_list_code varchar2(60) DEFAULT NULL;
699 l_interface_code varchar2(60);
700 l_content_code varchar2(60);
701 l_igr_csr_id varchar2(60);
702 l_mapping_code varchar2(60);
703 --
704 l_user_integrator_name varchar2(100);
705 l_num_columns number;
706 l_object_code varchar2(30) ;
707 l_object_num number;
708 --
709 l_bne_value VARCHAR2(2000);
710 l_sql_statement VARCHAR2(2000);
711 --
712 BEGIN
713 --
714 --------< Validate Parameters >--------------
715 IF p_view_name IS NULL THEN
716 fnd_message.set_name('PER','PER_289873_INVALID_VIEW_NAME');
717 fnd_message.raise_error;
718 ELSIF p_api_package_name IS NULL THEN
719 fnd_message.set_name('PER','PER_289874_INVAL_PGK_NAME');
720 fnd_message.raise_error;
721 ELSIF p_api_procedure_name IS NULL THEN
725 fnd_message.set_name('PER','PER_289876_INVAL_IFACE_NAME');
722 fnd_message.set_name('PER','PER_289875_INVAL_PROC_NAME');
723 fnd_message.raise_error;
724 ELSIF p_interface_user_name IS NULL THEN
726 fnd_message.raise_error;
727 ELSIF p_interface_param_name IS NULL THEN
728 fnd_message.set_name('PER','PER_289877_INVAL_PARAM_LIST');
729 fnd_message.raise_error;
730 ELSIF p_api_type IS NULL THEN
731 fnd_message.set_name('PER','PER_289878_INVAL_API_TYPE');
732 fnd_message.raise_error;
733 END IF;
734 --Check Application ID
735 l_bne_value := 'FND_APPLICATION';
736 OPEN csr_int FOR
737 ' select APPLICATION_SHORT_NAME' ||
738 ' from '||l_bne_value ||
739 ' where APPLICATION_ID ='||
740 p_application_id;
741 FETCH csr_int INTO l_igr_csr_id;
742 IF csr_int%NOTFOUND THEN
743 --invalid form name supplied
744 fnd_message.set_name('PER','PER_289921_INVAL_APP_ID');
745 fnd_message.raise_error;
746 END IF;
747 IF p_form_name <> 'LETTER' AND p_form_name <> 'GENERAL' THEN
748 --Check Form Name exists
749 l_bne_value := 'FND_FORM_VL';
750 OPEN csr_int FOR
751 ' select FORM_NAME' ||
752 ' from '||l_bne_value ||
753 ' where FORM_NAME = '''||
754 p_form_name || '''';
755 FETCH csr_int INTO l_igr_csr_id;
756 IF csr_int%NOTFOUND THEN
757 --invalid form name supplied
758 fnd_message.set_name('PER','PER_289922_INVAL_FM_NAME');
759 fnd_message.raise_error;
760 END IF;
761 END IF;
762 --
763 --Check integrator_user_name
764 l_bne_value := 'bne_integrators_tl';
765 OPEN csr_int FOR
766 ' select USER_NAME' ||
767 ' from '||l_bne_value ||
768 ' where USER_NAME ='''||
769 p_integrator_user_name ||'''';
770 FETCH csr_int INTO l_igr_csr_id;
771 IF csr_int%FOUND THEN
772 --invalid form name supplied
773 fnd_message.set_name('PER','PER_289923_USER_NAME_EXISTS');
774 fnd_message.raise_error;
775 END IF;
776 --
777 --Check package and procedure name
778 l_bne_value := 'SYS.ARGUMENT$ A, DBA_OBJECTS B';
779 OPEN csr_int FOR
780 ' select 1' ||
781 ' from '||l_bne_value ||
782 ' where A.OBJ# = B.OBJECT_ID' ||
783 ' AND B.OBJECT_NAME = '''||P_API_PACKAGE_NAME ||
784 ''' AND A.PROCEDURE$ = ''' ||P_API_PROCEDURE_NAME ||'''';
785 FETCH csr_int INTO l_igr_csr_id;
786 IF csr_int%NOTFOUND THEN
787 --invalid package/procedure name supplied
788 fnd_message.set_name('PER','PER_289931_API_NOT_EXIST');
789 fnd_message.raise_error;
790 END IF;
791 --
792 ---------< Create MetaData >----------
793 --
794 --create object code
795 SELECT hr_adi_object_code_s.NEXTVAL
796 INTO l_object_num
797 FROM dual;
798 -- If Style is "GENERAL", prepend object code with "GENERAL_"
799 IF p_form_name = 'GENERAL' THEN
800 l_object_code := 'GENERAL_'||to_char(l_object_num);
801 ELSE
802 l_object_code := 'HR_'||to_char(l_object_num);
803 END IF;
804 --
805 -- STEP 1. Create Integrator
806 --
807 l_user_integrator_name := p_integrator_user_name;
808 -- check to see if integrator name already exists
809 l_bne_value := 'bne_integrators_b';
810 OPEN csr_int FOR
811 'SELECT integrator_code' ||
812 ' FROM ' || l_bne_value ||
813 ' WHERE integrator_code = ''' ||
814 l_object_code ||'_INTG'||
815 ''' AND application_id = '
816 || p_application_id;
817 --
818 FETCH csr_int INTO l_igr_csr_id;
819 IF csr_int%FOUND THEN
820 CLOSE csr_int;
821 fnd_message.set_name('PER','PER_289872_ADI_INTGR_EXISTS');
822 fnd_message.raise_error;
823 END IF;
824 CLOSE csr_int;
825 --
826 l_bne_value := 'bne_integrator_utils.create_integrator_no_content';
827 l_sql_statement :=
828 'BEGIN '||
829 l_bne_value||
830 ' (:1,:2,:3,:4,:5,:6,:7);' ||
831 'END;';
832 --
833 EXECUTE IMMEDIATE l_sql_statement
834 USING IN p_application_id
835 , IN l_object_code
836 , IN l_user_integrator_name --use created integrator name
837 , IN p_user_id
838 , IN p_language
839 , IN p_language
840 , OUT l_integrator_code;
841 --
842 l_bne_value := 'bne_integrators_b';
843 OPEN csr_int FOR
844 'SELECT integrator_code' ||
845 ' FROM ' || l_bne_value ||
846 ' WHERE integrator_code = ''' ||
847 l_integrator_code ||
848 ''' AND application_id = '
849 || p_application_id;
850 --
851 FETCH csr_int INTO l_igr_csr_id;
852 IF csr_int%NOTFOUND THEN
853 CLOSE csr_int;
854 fnd_message.set_name('PER','PER_289864_ADI_INTGR_INVAL');
855 fnd_message.raise_error;
856 /*ELSE
857 set_new_session_flag
858 (p_application_id => p_application_id
859 ,p_integrator_code => l_integrator_code);*/
860 END IF;
861 CLOSE csr_int;
865 --
862 -- ??? maybe also check for null???
863 --
864 -- Step 2: Create Content for view
866 l_bne_value := 'bne_content_utils.create_content';
867 l_sql_statement :=
868 'BEGIN '||
869 l_bne_value||
870 ' (:1,:2,:3,:4,:5,:6,:7,:8,:9);' ||
871 'END;';
872 EXECUTE IMMEDIATE l_sql_statement
873 USING IN p_application_id
874 ,IN l_object_code
875 ,IN l_integrator_code
876 ,IN upper(p_view_name)
877 ,IN p_language
878 ,IN p_language
879 ,IN c_hr_content
880 ,IN p_user_id
881 , OUT l_content_code;
882 --
883 l_bne_value := 'bne_contents_b';
884 OPEN csr_int FOR
885 'SELECT content_code ' ||
886 ' FROM ' || l_bne_value ||
887 ' WHERE integrator_code =''' ||
888 l_integrator_code ||
889 ''' AND application_id = '|| p_application_id;
890
891 FETCH csr_int INTO l_igr_csr_id;
892 IF csr_int%NOTFOUND THEN
893 CLOSE csr_int;
894 fnd_message.set_name('PER','PER_289865_ADI_CONTENT_FAIL');
895 fnd_message.raise_error;
896 END IF;
897 CLOSE csr_int;
898 --
899 -- STEP 3. Create Content Columns
900 --
901 l_bne_value := 'bne_content_utils.create_content_cols_from_view';
902 l_sql_statement :=
903 'BEGIN ' ||
904 l_bne_value ||
905 ' (:1,:2,:3,:4,:5,:6);' ||
906 'END;';
907 EXECUTE IMMEDIATE l_sql_statement
908 USING IN p_application_id
909 ,IN l_content_code
910 ,IN upper(p_view_name)
911 ,IN p_language
912 ,IN p_language
913 ,IN p_user_id;
914 --
915 l_bne_value := 'bne_content_cols_b';
916 OPEN csr_int FOR
917 'SELECT count(*) ' ||
918 ' FROM '|| l_bne_value ||
919 ' WHERE content_code =''' ||
920 l_content_code ||''' AND ' ||
921 ' application_id = '|| p_application_id;
922 FETCH csr_int INTO l_num_columns;
923 IF l_num_columns <1 THEN
924 CLOSE csr_int;
925 fnd_message.set_name('PER','PER_289866_ADI_CONT_COL_FAIL');
926 fnd_message.raise_error;
927 END IF;
928 CLOSE csr_int;
929 --
930 -- Step 4. Add HR Param List to content
931 --
932 l_bne_value := 'hr_integration_utils.add_hr_param_list_to_content';
933 l_sql_statement :=
934 'BEGIN ' ||
935 l_bne_value ||
936 ' (:1,:2);' ||
937 'END;';
938 EXECUTE IMMEDIATE l_sql_statement
939 USING IN p_application_id
940 ,IN l_content_code;
941 --
942 l_bne_value := 'bne_contents_b';
943 OPEN csr_int FOR
944 'SELECT param_list_code ' ||
945 ' FROM ' || l_bne_value ||
946 ' WHERE content_code = ''' ||
947 l_content_code ||
948 ''' AND application_id = '||
949 p_application_id;
950 --
951 FETCH csr_int INTO l_param_list_code;
952 IF l_param_list_code IS NULL THEN
953 CLOSE csr_int;
954 fnd_message.set_name('PER','PER_289869_ADI_ADD_PARAM_FAIL');
955 fnd_message.raise_error;
956 END IF;
957 CLOSE csr_int;
958 --
959 -- STEP 5. Create Interface for API
960 --
961 l_bne_value := 'bne_integrator_utils.create_api_interface_and_map';
962 l_sql_statement :=
963 'BEGIN ' ||
964 l_bne_value ||
965 ' (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18); ' ||
966 'END;';
967 --
968 EXECUTE IMMEDIATE l_sql_statement
969 USING IN p_application_id
970 ,IN l_object_code
971 ,IN l_integrator_code
972 ,IN upper(p_api_package_name)
973 ,IN upper(p_api_procedure_name)
974 ,IN p_interface_user_name
975 ,IN l_content_code
976 ,IN upper(p_view_name)
977 ,IN p_interface_param_name
978 ,IN p_api_type
979 ,IN p_api_return_type
980 ,IN 5
981 ,IN p_language
982 ,IN p_language
983 ,IN p_user_id
984 , OUT l_param_list_code
985 , OUT l_interface_code
986 , OUT l_mapping_code;
987 --
988 l_bne_value := 'bne_interface_cols_b';
989 OPEN csr_int FOR
990 --check interface created and has columns
991 ' SELECT interface_code '||
992 ' FROM '|| l_bne_value ||
993 ' WHERE interface_code = (' ||
994 ' SELECT interface_code'||
995 ' FROM bne_interfaces_b' ||
996 ' WHERE interface_code = ''' ||
997 l_interface_code ||
998 ''' AND application_id ='||
999 p_application_id ||
1000 ') AND application_id = '||
1001 p_application_id;
1002 FETCH csr_int INTO l_igr_csr_id;
1003 IF csr_int%NOTFOUND THEN
1004 CLOSE csr_int;
1005 fnd_message.set_name('PER','PER_289870_ADI_IFACE_FAIL');
1006 fnd_message.raise_error;
1007 END IF;
1008 CLOSE csr_int;
1009 --
1013 l_sql_statement :=
1010 -- Step 6. Set upload param list for integrator
1011 --
1012 l_bne_value := 'hr_integration_utils.add_hr_upload_list_to_integ';
1014 'BEGIN ' ||
1015 l_bne_value ||
1016 ' (:1,:2); ' ||
1017 'END;';
1018 --
1019 EXECUTE IMMEDIATE l_sql_statement
1020 USING IN p_application_id
1021 ,In l_integrator_code;
1022 --
1023 -- STEP 7. Associate integrator with a particular form.
1024 --
1025 IF p_form_name IS NOT NULL and
1026 p_form_name <> 'GENERAL' THEN
1027 l_user_integrator_name := to_char(p_application_id) || ':' || l_integrator_code;
1028 l_bne_value := 'hr_integration_utils.register_integrator_to_form';
1029 l_sql_statement :=
1030 'BEGIN ' ||
1031 l_bne_value ||
1032 ' (:1,:2); ' ||
1033 'END;';
1034 EXECUTE IMMEDIATE l_sql_statement
1035 USING IN l_user_integrator_name
1036 ,IN p_form_name;
1037 END IF;
1038 --
1039 END create_update_data;
1040 --
1041 -------------------------------------------------------------------------------
1042 -------------------------<Public procedure visible in header>------------------
1043 -------------------------------------------------------------------------------
1044 PROCEDURE create_metadata(
1045 p_metadata_type IN varchar2
1046 ,p_application_id IN number
1047 ,p_integrator_user_name IN varchar2
1048 ,p_view_name IN varchar2 default null
1049 ,p_form_name IN varchar2 default null
1050 ,p_api_package_name IN varchar2 default null
1051 ,p_api_procedure_name IN varchar2 default null
1052 ,p_interface_user_name IN varchar2 default null
1053 ,p_interface_param_name IN varchar2 default null
1054 ,p_api_type IN varchar2 default null
1055 ,p_api_return_type IN varchar2 default null
1056 ) IS
1057 --vars
1058 l_language varchar2(100);
1059 l_user_id number;
1060 l_package_name varchar2(240) default null;
1061 l_procedure_name varchar2(240) default null;
1062 l_form_name varchar2(240) default null;
1063 l_api_type varchar2(240) default null;
1064 l_api_return_type varchar2(240) default null;
1065 --
1066 BEGIN
1067 --get language and user_id
1068 SELECT
1069 fnd_global.user_id,
1070 userenv('LANG')
1071 INTO
1072 l_user_id,
1073 l_language
1074 FROM
1075 dual;
1076 -- UPPER parameters
1077 IF p_api_package_name IS NOT NULL THEN
1078 l_package_name := UPPER(p_api_package_name);
1079 END IF;
1080 IF p_api_procedure_name IS NOT NULL THEN
1081 l_procedure_name := UPPER(p_api_procedure_name);
1082 END IF;
1083 IF p_form_name IS NOT NULL THEN
1084 l_form_name := UPPER(p_form_name);
1085 END IF;
1086 IF p_api_type IS NOT NULL THEN
1087 l_api_type := UPPER(p_api_type);
1088 END IF;
1089 IF p_api_return_type IS NOT NULL THEN
1090 l_api_return_type := UPPER(p_api_return_type);
1091 END IF;
1092 --Validate return type if api type is function
1093 IF l_api_type ='FUNCTION' THEN
1094 IF p_api_return_type IS NULL THEN
1095 fnd_message.set_name('PER','PER_289928_INVAL_RET_TYPE');
1096 fnd_message.raise_error;
1097 END IF;
1098 END IF;
1099 --
1100 IF (UPPER(p_metadata_type) = 'DOWNLOAD')
1101 THEN
1102 create_download_data
1103 (p_application_id => p_application_id
1104 ,p_integrator_user_name => p_integrator_user_name
1105 ,p_view_name => p_view_name
1106 ,p_form_name => l_form_name
1107 ,p_language => l_language
1108 ,p_user_id => l_user_id );
1109 --
1110 ELSIF (UPPER(p_metadata_type) = 'CREATE')
1111 THEN
1112 create_upload_data
1113 (p_application_id => p_application_id
1114 ,p_integrator_user_name => p_integrator_user_name
1115 ,p_api_package_name => l_package_name
1116 ,p_api_procedure_name => l_procedure_name
1117 ,p_interface_user_name => p_interface_user_name
1118 ,p_interface_param_name => p_interface_param_name
1119 ,p_api_type => l_api_type
1120 ,p_api_return_type => l_api_return_type
1121 ,p_language => l_language
1122 ,p_user_id => l_user_id);
1123 --
1124 ELSIF (UPPER(p_metadata_type) = 'UPDATE')
1125 THEN NULL;
1126 create_update_data
1127 (p_application_id => p_application_id
1128 ,p_integrator_user_name => p_integrator_user_name
1129 ,p_api_package_name => l_package_name
1130 ,p_api_procedure_name => l_procedure_name
1131 ,p_interface_user_name => p_interface_user_name
1132 ,p_interface_param_name => p_interface_param_name
1133 ,p_api_type => l_api_type
1134 ,p_api_return_type => l_api_return_type
1135 ,p_view_name => p_view_name
1136 ,p_form_name => l_form_name
1137 ,p_language => l_language
1138 ,p_user_id => l_user_id);
1139 ELSE
1140 fnd_message.set_name('PER','PER_289879_INVAL_META_TYPE');
1144 --
1141 fnd_message.raise_error;
1142 END IF;
1143 END create_metadata;
1145 --
1146 -- ---------------------------------------------------------------------------
1147 -- |------------------------< create_standalone_query >----------------------|
1148 -- ---------------------------------------------------------------------------
1149 PROCEDURE create_standalone_query
1150 (p_application_id in number
1151 ,p_intg_user_name in varchar2
1152 ,p_sql in varchar2
1153 ,p_param1_name in varchar2 default NULL
1154 ,p_param1_type in varchar2 default NULL
1155 ,p_param1_prompt in varchar2 default NULL
1156 ,p_param2_name in varchar2 default NULL
1157 ,p_param2_type in varchar2 default NULL
1158 ,p_param2_prompt in varchar2 default NULL
1159 ,p_param3_name in varchar2 default NULL
1160 ,p_param3_type in varchar2 default NULL
1161 ,p_param3_prompt in varchar2 default NULL
1162 ,p_param4_name in varchar2 default NULL
1163 ,p_param4_type in varchar2 default NULL
1164 ,p_param4_prompt in varchar2 default NULL
1165 ,p_param5_name in varchar2 default NULL
1166 ,p_param5_type in varchar2 default NULL
1167 ,p_param5_prompt in varchar2 default NULL
1168 ) IS
1169 --
1170 -- Local variables
1171 l_param_identifier varchar2(20) := '$PARAM$.';
1172 --
1173 BEGIN
1174 --
1175 -- Check that params appear in the query.
1176 --
1177 IF ((p_param1_name IS NOT NULL) and
1178 (instr(p_sql,l_param_identifier||p_param1_name) = 0)) THEN
1179 --
1180 -- Param 1 does not appear in WHERE clause
1181 fnd_message.set_name('PER','PER_289713_ADI_INVALID_PARAM');
1182 fnd_message.raise_error;
1183 --
1184 ELSIF ((p_param2_name IS NOT NULL) and
1185 (instr(p_sql,l_param_identifier||p_param2_name) = 0)) THEN
1186 --
1187 -- Param 2 does not appear in WHERE clause
1188 fnd_message.set_name('PER','PER_289713_ADI_INVALID_PARAM');
1189 fnd_message.raise_error;
1190 --
1191 ELSIF ((p_param3_name IS NOT NULL) and
1192 (instr(p_sql,l_param_identifier||p_param3_name) = 0)) THEN
1193 --
1194 -- Param 3 does not appear in WHERE clause
1195 fnd_message.set_name('PER','PER_289713_ADI_INVALID_PARAM');
1196 fnd_message.raise_error;
1197 --
1198 ELSIF ((p_param4_name IS NOT NULL) and
1199 (instr(p_sql,l_param_identifier||p_param4_name) = 0)) THEN
1200 --
1201 -- Param 4 does not appear in WHERE clause
1202 fnd_message.set_name('PER','PER_289713_ADI_INVALID_PARAM');
1203 fnd_message.raise_error;
1204 --
1205 ELSIF ((p_param5_name IS NOT NULL) and
1206 (instr(p_sql,l_param_identifier||p_param5_name) = 0)) THEN
1207 --
1208 -- Param 5 does not appear in WHERE clause
1209 fnd_message.set_name('PER','PER_289713_ADI_INVALID_PARAM');
1210 fnd_message.raise_error;
1211 --
1212 ELSE
1213 hr_integration_utils.add_sql_to_content
1214 (p_application_id => p_application_id
1215 ,p_intg_user_name => p_intg_user_name
1216 ,p_sql => p_sql
1217 ,p_param1_name => p_param1_name
1218 ,p_param1_type => p_param1_type
1219 ,p_param1_prompt => p_param1_prompt
1220 ,p_param2_name => p_param2_name
1221 ,p_param2_type => p_param2_type
1222 ,p_param2_prompt => p_param2_prompt
1223 ,p_param3_name => p_param3_name
1224 ,p_param3_type => p_param3_type
1225 ,p_param3_prompt => p_param3_prompt
1226 ,p_param4_name => p_param4_name
1227 ,p_param4_type => p_param4_type
1228 ,p_param4_prompt => p_param4_prompt
1229 ,p_param5_name => p_param5_name
1230 ,p_param5_type => p_param5_type
1231 ,p_param5_prompt => p_param5_prompt
1232 );
1233 --
1234 END IF;
1235 END create_standalone_query;
1236 --
1237 -- ---------------------------------------------------------------------------
1238 -- |----------------------< maintain_standalone_query >----------------------|
1239 -- ---------------------------------------------------------------------------
1240 PROCEDURE maintain_standalone_query
1241 (p_application_id in number
1242 ,p_intg_user_name in varchar2
1243 ,p_sql in varchar2 default null
1244 ,p_param1_name in varchar2 default null
1245 ,p_param1_type in varchar2 default null
1246 ,p_param1_prompt in varchar2 default null
1247 ,p_param2_name in varchar2 default null
1248 ,p_param2_type in varchar2 default null
1249 ,p_param2_prompt in varchar2 default null
1250 ,p_param3_name in varchar2 default null
1251 ,p_param3_type in varchar2 default null
1252 ,p_param3_prompt in varchar2 default null
1253 ,p_param4_name in varchar2 default null
1254 ,p_param4_type in varchar2 default null
1255 ,p_param4_prompt in varchar2 default null
1256 ,p_param5_name in varchar2 default null
1257 ,p_param5_type in varchar2 default null
1258 ,p_param5_prompt in varchar2 default null
1259 ) IS
1260 --
1261 --
1262 TYPE CSR_TYP IS REF CURSOR;
1263 csr_int CSR_TYP;
1264 --
1265 -- Local variables
1266 --
1267 l_integrator_code varchar2(30);
1271 l_param_list_app_id number;
1268 l_content_code varchar2(30);
1269 l_sql varchar2(2000);
1270 l_param_list_code varchar2(30);
1272 --
1273 BEGIN
1274 --
1275 -- Have integrator user name - determine integrator code
1276 --
1277 OPEN csr_int FOR
1278 'SELECT b.integrator_code ' ||
1279 ' FROM bne_integrators_tl t ' ||
1280 ' , bne_integrators_b b ' ||
1281 ' WHERE t.application_id = ' || p_application_id ||
1282 ' AND t.user_name = ''' || p_intg_user_name || '''' ||
1283 ' AND t.integrator_code = b.integrator_code ' ||
1284 ' AND t.application_id = b.application_id ' ||
1285 ' AND t.integrator_code like ''GENERAL%'' ' ||
1286 ' AND b.enabled_flag = ''Y'' ';
1287 FETCH csr_int INTO l_integrator_code;
1288 --
1289 IF csr_int%NOTFOUND THEN
1290 --
1291 CLOSE csr_int;
1292 fnd_message.set_name('PER','PER_289428_ADI_INTG_NOT_EXIST');
1293 fnd_message.raise_error;
1294 --
1295 END IF;
1296 CLOSE csr_int;
1297 --
1298 -- Have integrator code, and integrator exists. Now we
1299 -- want to determine the parameter list for the content
1300 -- for the integrator, and remove it completely.
1301 -- This will be rebuilt when we create the query again.
1302 --
1303 OPEN csr_int FOR
1304 'SELECT param_list_app_id, ' ||
1305 ' param_list_code, ' ||
1306 ' content_code ' ||
1307 ' FROM bne_contents_b ' ||
1308 ' WHERE integrator_app_id = '||p_application_id ||
1309 ' AND integrator_code = ''' ||l_integrator_code || ''' ';
1310 --
1311 FETCH csr_int INTO l_param_list_app_id, l_param_list_code, l_content_code;
1312 --
1313 IF csr_int%FOUND THEN
1314 IF l_param_list_code <> 'HR_STANDARD' Then
1315 --
1316 -- Parameter list exists for this content, so delete it.
1317 --
1318 l_sql := 'BEGIN ' ||
1319 ' DELETE FROM bne_param_list_items ' ||
1320 ' WHERE application_id = :1 ' ||
1321 ' AND param_list_code = :2 ;' ||
1322 'END; ';
1323 EXECUTE IMMEDIATE l_sql
1324 USING IN l_param_list_app_id,
1325 IN l_param_list_code;
1326 --
1327 l_sql := 'BEGIN ' ||
1328 ' DELETE from bne_param_lists_tl ' ||
1329 ' WHERE application_id = :1 ' ||
1330 ' AND param_list_code = :2 ;' ||
1331 'END; ';
1332 EXECUTE IMMEDIATE l_sql
1333 USING IN l_param_list_app_id,
1334 IN l_param_list_code;
1335 --
1336 l_sql := 'BEGIN ' ||
1337 ' DELETE from bne_param_lists_b ' ||
1338 ' WHERE application_id = :1 ' ||
1339 ' AND param_list_code = :2 ;' ||
1340 'END; ';
1341 EXECUTE IMMEDIATE l_sql
1342 USING IN l_param_list_app_id,
1343 IN l_param_list_code;
1344 --
1345 -- Update content to reflect deleted param list
1346 l_sql := 'BEGIN ' ||
1347 ' UPDATE bne_contents_b ' ||
1348 ' SET param_list_app_id = 800 ' ||
1349 ' , param_list_code = ''HR_STANDARD'' ' ||
1350 ' , object_version_number = object_version_number + 1 ' ||
1351 ' WHERE integrator_app_id = :1 ' ||
1352 ' AND integrator_code = :2; ' ||
1353 'END; ';
1354 EXECUTE IMMEDIATE l_sql
1355 USING IN p_application_id,
1356 IN l_integrator_code;
1357 END IF;
1358 --
1359 --
1360 -- Update SQL and re-create param list if necessary
1361 --
1362 IF p_sql IS NOT NULL THEN
1363 --
1364 -- Can call the create one, as it will update the
1365 -- stored SQL
1366 create_standalone_query
1367 (p_application_id => p_application_id,
1368 p_intg_user_name => p_intg_user_name,
1369 p_sql => p_sql,
1370 p_param1_name => p_param1_name,
1371 p_param1_type => p_param1_type,
1372 p_param1_prompt => p_param1_prompt,
1373 p_param2_name => p_param2_name,
1374 p_param2_type => p_param2_type,
1375 p_param2_prompt => p_param2_prompt,
1376 p_param3_name => p_param3_name,
1377 p_param3_type => p_param3_type,
1378 p_param3_prompt => p_param3_prompt,
1379 p_param4_name => p_param4_name,
1380 p_param4_type => p_param4_type,
1381 p_param4_prompt => p_param4_prompt,
1382 p_param5_name => p_param5_name,
1383 p_param5_type => p_param5_type,
1384 p_param5_prompt => p_param5_prompt);
1385 ELSE
1386 -- update to null
1387 l_sql := 'BEGIN ' ||
1388 ' DELETE FROM bne_stored_sql ' ||
1389 ' WHERE content_code = :1 ' ||
1390 ' AND application_id = :2; ' ||
1391 'END; ';
1392 EXECUTE IMMEDIATE l_sql
1393 USING IN l_content_code,
1394 IN p_application_id;
1395 --
1396 END IF;
1397 ELSE
1398 -- Could not find a query, etc for given integrator.
1399 -- Therefore create one, if query is not null
1400 --
1404 --
1401 IF p_sql IS NOT NULL THEN
1402 --
1403 -- Call to create
1405 create_standalone_query
1406 (p_application_id => p_application_id,
1407 p_intg_user_name => p_intg_user_name,
1408 p_sql => p_sql,
1409 p_param1_name => p_param1_name,
1410 p_param1_type => p_param1_type,
1411 p_param1_prompt => p_param1_prompt,
1412 p_param2_name => p_param2_name,
1413 p_param2_type => p_param2_type,
1414 p_param2_prompt => p_param2_prompt,
1415 p_param3_name => p_param3_name,
1416 p_param3_type => p_param3_type,
1417 p_param3_prompt => p_param3_prompt,
1418 p_param4_name => p_param4_name,
1419 p_param4_type => p_param4_type,
1420 p_param4_prompt => p_param4_prompt,
1421 p_param5_name => p_param5_name,
1422 p_param5_type => p_param5_type,
1423 p_param5_prompt => p_param5_prompt);
1424 --
1425 END IF;
1426 --
1427 END IF;
1428 CLOSE csr_int;
1429 --
1430 END maintain_standalone_query;
1431 --
1432 END hr_ade_adi_data_setup;