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