[Home] [Help]
PACKAGE BODY: APPS.PQP_RIW_WEBADI_UTILS
Source
1 Package Body PQP_RIW_WEBADI_UTILS as
2 /* $Header: pqpriwadiutl.pkb 120.15.12010000.7 2009/02/03 05:26:39 sravikum ship $ */
3 g_package Varchar2(30) := 'PQP_RIW_WEBADI_UTILS.';
4 g_ins_upd_flag varchar2(4) := 'D';
5
6
7
8 -- Cursor to get the XML tags - based on interface code passed
9 CURSOR csr_get_xml_tags (c_interface_code IN VARCHAR2,
10 c_layout_code IN VARCHAR2) IS
11 Select
12 fwc.FLXDU_COLUMN_XML_TAG
13 ,fwc.FLXDU_SEQ_NUM
14 ,fwc.FLXDU_GROUP_NAME
15 ,fwc.FLXDU_COLUMN_XML_DATA
16 from
17 PQP_FLXDU_COLUMNS fwc,
18 bne_layout_cols blc
19 where
20 blc.INTERFACE_SEQ_NUM = fwc.FLXDU_SEQ_NUM
21 and blc.LAYOUT_CODE =c_layout_code
22 and blc.interface_code =c_interface_code
23 and blc.INTERFACE_SEQ_NUM in (Select SEQUENCE_NUM from BNE_INTERFACE_cols_b where INTERFACE_CODE =c_interface_code
24 and DISPLAY_FLAG ='Y' AND sequence_num not in (19, 62, 93, 124, 155))
25 and fwc.DISPLAY_FLAG ='Y'
26 and fwc.entity_type in ('PERSON', 'ASSIGNMENT', 'ADDRESS')
27 order by blc.sequence_num,blc.BLOCK_ID;
28
29
30 -- Cursor to get the Flexi XML tags - based on group name passed
31 CURSOR csr_get_flex_xml_tags (c_flxdu_group_name IN VARCHAR2 ) IS
32 SELECT FLXDU_COLUMN_NAME
33 ,FLXDU_COLUMN_XML_TAG
34 ,FLXDU_SEQ_NUM
35 ,FLXDU_GROUP_NAME
36 ,flxdu_column_xml_data
37 FROM PQP_FLXDU_COLUMNS
38 WHERE FLXDU_GROUP_NAME = c_flxdu_group_name
39 AND DISPLAY_FLAG ='N'
40 ORDER BY FLXDU_SEQ_NUM;
41
42
43 -- =============================================================================
44 -- ~ Create_RIW_mappings_row:
45 -- =============================================================================
46 PROCEDURE Create_RIW_Mappings_row
47 (p_application_id IN NUMBER
48 ,p_new_mapping_code IN VARCHAR2
49 ,p_user_name IN VARCHAR2
50 ,p_data_source IN VARCHAR2
51 ,p_new_intg_code IN VARCHAR2
52 ,p_entity_name IN VARCHAR2 DEFAULT NULL) IS
53
54 -- cursor to get mappnigs row
55 CURSOR c_mapping_row(c_mapping_code in VARCHAR2)
56 IS
57 SELECT MAPPING_CODE
58 ,OBJECT_VERSION_NUMBER
59 ,INTEGRATOR_APP_ID
60 ,INTEGRATOR_CODE
61 ,REPORTING_FLAG
62 ,REPORTING_INTERFACE_APP_ID
63 ,REPORTING_INTERFACE_CODE
64 ,APPLICATION_ID
65 ,CREATION_DATE
66 ,CREATED_BY
67 ,LAST_UPDATE_DATE
68 ,LAST_UPDATED_BY
69 ,LAST_UPDATE_LOGIN
70 FROM BNE_MAPPINGS_B
71 WHERE application_id = p_application_id
72 AND MAPPING_CODE = c_mapping_code;
73
74 l_mapping_row c_mapping_row%ROWTYPE;
75 l_rowid VARCHAR2(200);
76 no_default_layout EXCEPTION;
77 l_proc VARCHAR2(72) := g_package||'Create_RIW_Mappings_row';
78 l_mapping_code VARCHAR2(50);
79 BEGIN
80
81 hr_utility.set_location('Entering Mapping Rows ', 30);
82 IF p_entity_name IS NULL THEN
83 IF p_data_source = 'XML' THEN
84 OPEN c_mapping_row(c_mapping_code => 'PQP_FLEXI_WEBADI_XML_MAP_KEY');
85 ELSIF p_data_source = 'CSV' THEN
86 OPEN c_mapping_row(c_mapping_code => 'PQP_FLEXI_WEBADI_CSV_MAP_KEY');
87 --$ Take into account the case when data pump mapping has to be created
88 ELSIF p_data_source = 'DP' THEN
89 OPEN c_mapping_row(c_mapping_code => 'PQP_FLEXI_WEBADI_DP_MAP_KEY');
90
91 ELSE
92 OPEN c_mapping_row(c_mapping_code => 'PQP_FLEXI_WEBADI_HR_MAP_KEY');
93 END IF;
94 ELSE
95 SELECT description INTO l_mapping_code FROM pqp_flxdu_columns where
96 entity_type = p_entity_name and
97 flxdu_column_name = 'MAPPING_CODE';
98 OPEN c_mapping_row(c_mapping_code => l_mapping_code);
99 END IF;
100
101 FETCH c_mapping_row INTO l_mapping_row;
102 IF c_mapping_row%NOTFOUND THEN
103 RAISE no_default_layout;
104 END IF;
105 CLOSE c_mapping_row;
106
107 -- insert the row
108 BNE_MAPPINGS_PKG.insert_row
109 (x_rowid => l_rowid
110 ,x_application_id => l_mapping_row.application_id
111 ,X_MAPPING_CODE => p_new_mapping_code
112 ,x_object_version_number => 1
113 ,X_INTEGRATOR_APP_ID => l_mapping_row.INTEGRATOR_APP_ID
114 ,X_INTEGRATOR_CODE => p_new_intg_code--l_mapping_row.INTEGRATOR_CODE
115 ,X_REPORTING_FLAG => l_mapping_row.REPORTING_FLAG
116 ,X_REPORTING_INTERFACE_APP_ID => l_mapping_row.REPORTING_INTERFACE_APP_ID
117 ,X_REPORTING_INTERFACE_CODE => l_mapping_row.REPORTING_INTERFACE_CODE
118 ,X_USER_NAME => p_user_name
119 ,X_CREATION_DATE => Sysdate
120 ,X_CREATED_BY => l_mapping_row.CREATED_BY
121 ,X_LAST_UPDATE_DATE => Sysdate
122 ,X_LAST_UPDATED_BY => l_mapping_row.CREATED_BY
123 ,X_LAST_UPDATE_LOGIN => l_mapping_row.CREATED_BY);
124
125 hr_utility.set_location('Exiting Mapping rows ', 40);
126
127 END Create_RIW_mappings_row;
128
129
130 -- =============================================================================
131 -- ~ Create_RIW_Mapping_Links_Rows:
132 -- =============================================================================
133 PROCEDURE Create_RIW_Mapping_Links_Rows
134 (p_application_id IN NUMBER
135 ,p_new_mapping_code IN VARCHAR2
136 ,p_new_interface_code IN VARCHAR2
137 ,p_data_source IN VARCHAR2
138 ,p_content_out IN VARCHAR2
139 ,p_entity_name IN VARCHAR2 DEFAULT NULL) IS
140
141 -- cursor to get mappings columns
142 CURSOR c_mapping_cols_row (c_mapping_code IN VARCHAR2)IS
143 SELECT INTERFACE_CODE
144 ,INTERFACE_SEQ_NUM
145 ,OBJECT_VERSION_NUMBER
146 ,CREATED_BY
147 ,CREATION_DATE
148 ,LAST_UPDATED_BY
149 ,LAST_UPDATE_LOGIN
150 ,LAST_UPDATE_DATE
151 ,SEQUENCE_NUM
152 ,INTERFACE_APP_ID
153 ,CONTENT_CODE
154 ,CONTENT_SEQ_NUM
155 ,APPLICATION_ID
156 ,MAPPING_CODE
157 ,CONTENT_APP_ID
158 ,DECODE_FLAG
159 FROM BNE_MAPPING_LINES
160 WHERE application_id = p_application_id
161 AND MAPPING_CODE = c_mapping_code;
162
163 l_temp_interface_seq_num BNE_MAPPING_LINES.INTERFACE_SEQ_NUM%TYPE;
164 l_mapping_cols_row c_mapping_cols_row%ROWTYPE;
165 l_rowid VARCHAR2(100);
166 l_mapping_code VARCHAR2(50);
167
168 BEGIN
169
170 IF p_entity_name IS NULL THEN
171 IF p_data_source = 'XML' THEN
172 OPEN c_mapping_cols_row(c_mapping_code => 'PQP_FLEXI_WEBADI_XML_MAP_KEY');
173 ELSIF p_data_source = 'CSV' THEN
174 OPEN c_mapping_cols_row(c_mapping_code => 'PQP_FLEXI_WEBADI_CSV_MAP_KEY');
175 --$ Take into account the case when data pump mapping has to be created
176 ELSIF p_data_source = 'DP' THEN
177 OPEN c_mapping_cols_row(c_mapping_code => 'PQP_FLEXI_WEBADI_DP_MAP_KEY');
178
179 ELSE
180 OPEN c_mapping_cols_row(c_mapping_code => 'PQP_FLEXI_WEBADI_HR_MAP_KEY');
181 END IF;
182 ELSE
183 SELECT description INTO l_mapping_code FROM pqp_flxdu_columns where
184 entity_type = p_entity_name and
185 flxdu_column_name = 'MAPPING_CODE';
186 OPEN c_mapping_cols_row(c_mapping_code => l_mapping_code);
187 END IF;
188
189 LOOP
190 FETCH c_mapping_cols_row INTO l_mapping_cols_row;
191 EXIT WHEN c_mapping_cols_row%NOTFOUND;
192 --Checking the base Interface Seq number exist in selected interface number
193 --or by default provided sequence number
194 l_temp_interface_seq_num := l_mapping_cols_row.INTERFACE_SEQ_NUM;
195 hr_utility.set_location(l_temp_interface_seq_num, 50);
196 IF p_entity_name IS NULL THEN
197 IF g_riw_data.EXISTS(l_temp_interface_seq_num) OR l_temp_interface_seq_num IN
198 (59,176,253,254,332,334,335,333,331,330,328,177,19, 62, 93, 124, 155,340,
199 350,351 --$ Include Batch Link and Exception
200 )
201 or l_temp_interface_seq_num between 192 and 221 --$ To include People Grp Segments
202 --in mapping to download the data
203 or l_temp_interface_seq_num between 223 and 252 --$ To include Soft Coded Kff Segments
204
205 THEN
206 BNE_MAPPING_LINES_PKG.INSERT_ROW
207 (x_rowid => l_rowid
208 ,x_application_id => p_application_id
209 ,X_MAPPING_CODE => p_new_mapping_code
210 ,X_INTERFACE_APP_ID => l_mapping_cols_row.INTERFACE_APP_ID
211 ,X_INTERFACE_CODE => p_new_interface_code
212 ,X_INTERFACE_SEQ_NUM => l_mapping_cols_row.INTERFACE_SEQ_NUM
213 ,X_DECODE_FLAG => l_mapping_cols_row.DECODE_FLAG
214 ,X_OBJECT_VERSION_NUMBER => 1
215 ,X_SEQUENCE_NUM => l_mapping_cols_row.SEQUENCE_NUM
216 ,X_CONTENT_SEQ_NUM => l_mapping_cols_row.CONTENT_SEQ_NUM
217 ,X_CONTENT_APP_ID => l_mapping_cols_row.CONTENT_APP_ID
218 ,X_CONTENT_CODE => p_content_out--l_mapping_cols_row.CONTENT_CODE
219 ,X_CREATION_DATE => sysdate
220 ,X_CREATED_BY => l_mapping_cols_row.CREATED_BY
221 ,X_LAST_UPDATE_DATE => Sysdate
222 ,X_LAST_UPDATED_BY => l_mapping_cols_row.CREATED_BY
223 ,X_LAST_UPDATE_LOGIN => l_mapping_cols_row.CREATED_BY);
224 END IF;
225 ELSE
226 IF g_riw_data.EXISTS(l_temp_interface_seq_num) THEN
227 BNE_MAPPING_LINES_PKG.INSERT_ROW
228 (x_rowid => l_rowid
229 ,x_application_id => p_application_id
230 ,X_MAPPING_CODE => p_new_mapping_code
231 ,X_INTERFACE_APP_ID => l_mapping_cols_row.INTERFACE_APP_ID
232 ,X_INTERFACE_CODE => p_new_interface_code
233 ,X_INTERFACE_SEQ_NUM => l_mapping_cols_row.INTERFACE_SEQ_NUM
234 ,X_DECODE_FLAG => l_mapping_cols_row.DECODE_FLAG
235 ,X_OBJECT_VERSION_NUMBER => 1
236 ,X_SEQUENCE_NUM => l_mapping_cols_row.SEQUENCE_NUM
237 ,X_CONTENT_SEQ_NUM => l_mapping_cols_row.CONTENT_SEQ_NUM
238 ,X_CONTENT_APP_ID => l_mapping_cols_row.CONTENT_APP_ID
239 ,X_CONTENT_CODE => p_content_out--l_mapping_cols_row.CONTENT_CODE
240 ,X_CREATION_DATE => sysdate
241 ,X_CREATED_BY => l_mapping_cols_row.CREATED_BY
242 ,X_LAST_UPDATE_DATE => Sysdate
243 ,X_LAST_UPDATED_BY => l_mapping_cols_row.CREATED_BY
244 ,X_LAST_UPDATE_LOGIN => l_mapping_cols_row.CREATED_BY);
245 END IF;
246 END IF;
247 hr_utility.set_location('Before finishing the loop', 60);
248 END LOOP;
249 CLOSE c_mapping_cols_row;
250
251 END Create_RIW_Mapping_Links_Rows;
252
253 -- =============================================================================
254 -- ~ Create_RIW_Layout_Cols_Row:
255 -- =============================================================================
256 PROCEDURE Create_RIW_Layout_Cols_Row
257 (p_application_id IN NUMBER
258 ,p_new_layout_code IN VARCHAR2
259 ,p_base_layout_code IN VARCHAR2
260 ,p_new_interface_code IN VARCHAR2
261 --$ Pass Data Source as well to make changes in layout accordingly
262 ,p_data_source IN VARCHAR2
263 ,p_entity_name IN VARCHAR2 DEFAULT NULL) IS
264
265 --cursor to get layout columns - interface seq is passed
266 CURSOR c_layout_cols_seq_row(c_interface_seq_num IN NUMBER) IS
267 SELECT application_id
268 ,layout_code
269 ,block_id
270 ,interface_app_id
271 ,interface_code
272 ,interface_seq_num
273 ,sequence_num
274 ,style
275 ,style_class
276 ,hint_style
277 ,hint_style_class
278 ,prompt_style
279 ,prompt_style_class
280 ,default_type
281 ,DEFAULT_VALUE
282 ,created_by
283 ,last_updated_by
284 ,last_update_login
285 ,display_width
286 ,READ_ONLY_FLAG --$ added new column as in BNE Layout Table
287 FROM bne_layout_cols
288 WHERE application_id = p_application_id
289 AND layout_code = p_base_layout_code
290 AND interface_seq_num =c_interface_seq_num
291 ORDER BY block_id;
292
293
294 -- cursor to get layout columns
295 CURSOR c_layout_cols_row IS
296 SELECT application_id
297 ,layout_code
298 ,block_id
299 ,interface_app_id
300 ,interface_code
301 ,interface_seq_num
302 ,sequence_num
303 ,style
304 ,style_class
305 ,hint_style
306 ,hint_style_class
307 ,prompt_style
308 ,prompt_style_class
309 ,default_type
310 ,DEFAULT_VALUE
311 ,created_by
312 ,last_updated_by
313 ,last_update_login
314 ,READ_ONLY_FLAG --$ added new column as in BNE Layout Table
315 FROM bne_layout_cols
316 WHERE application_id = p_application_id
317 AND layout_code = p_base_layout_code
318 AND INTERFACE_SEQ_NUM in (59,176,253,254,332,334,335,333,331,330,328,177,19,
319 62,93,124,155,340
320 ,351 --$ Add Batch Exception as well for correct errors
321 ,350 --$ Add Batch Link value for correct errors
322 )
323 ORDER BY block_id;
324
325 CURSOR c_layout_cols_row_others(c_seq_num IN VARCHAR2) IS
326 SELECT application_id
327 ,layout_code
328 ,block_id
329 ,interface_app_id
330 ,interface_code
331 ,interface_seq_num
332 ,sequence_num
333 ,style
334 ,style_class
335 ,hint_style
336 ,hint_style_class
337 ,prompt_style
338 ,prompt_style_class
339 ,default_type
340 ,DEFAULT_VALUE
341 ,created_by
342 ,last_updated_by
343 ,last_update_login
344 ,READ_ONLY_FLAG --$ added new column as in BNE Layout Table
345 FROM bne_layout_cols
346 WHERE application_id = p_application_id
347 AND layout_code = p_base_layout_code
348 AND INTERFACE_SEQ_NUM = c_seq_num
349 ORDER BY block_id;
350
351
352 l_layout_cols_seq_row c_layout_cols_seq_row%ROWTYPE;
353 l_layout_cols_row c_layout_cols_row%ROWTYPE;
354 l_layout_cols_row_others c_layout_cols_row%ROWTYPE;
355 l_rowid VARCHAR2(100);
356 l_placement_block NUMBER;
357 l_default_type VARCHAR2(20);
358 l_placement_value VARCHAR2(20);
359 l_default_value BNE_INTERFACE_cols_b.DEFAULT_VALUE%TYPE;
360 l_context_seq_num BNE_LAYOUT_COLS.INTERFACE_SEQ_NUM%TYPE;
361 l_header_seq_num BNE_LAYOUT_COLS.INTERFACE_SEQ_NUM%TYPE;
362 l_base_intf_code VARCHAR2(50);
363
364
365 --Paramaters to handle the display sequence order
366 l_line_display_seq NUMBER :=1600; --$ increase seq num as soft segments have been included
367 l_head_display_seq NUMBER :=1300;
368 l_context_display_seq NUMBER :=1300;
369
370 --Paramaters to handle the display sequence order
371 l_line_exist_seq NUMBER :=10;
372 l_head_exist_seq NUMBER :=10;
373 l_context_exist_seq NUMBER :=10;
374
375 l_dummy_seq_number NUMBER :=0;
376 l_final_seq_number NUMBER :=0;
377 l_allowance_index NUMBER :=0;
378 l_insert_flag VARCHAR2(10);
379 l_proc_name VARCHAR2(72) :=g_package||'Create_RIW_Layout_Cols_Row';
380 l_interface_seq_num NUMBER :=0;
381 l_bg_seq_num NUMBER(10);
382
383
384 BEGIN
385
386 hr_utility.set_location('Inside creating the layout cols',20);
387 hr_utility.set_location(p_new_interface_code, 25);
388 --Gettting the values for selected seq numbers
389 FOR l_interface_index IN g_temp_riw_data.FIRST..g_temp_riw_data.LAST
390 LOOP
391 l_interface_seq_num := g_temp_riw_data(l_interface_index).interface_seq;
392 OPEN c_layout_cols_seq_row(c_interface_seq_num => l_interface_seq_num);
393 LOOP
394 FETCH c_layout_cols_seq_row INTO l_layout_cols_seq_row;
395 EXIT WHEN c_layout_cols_seq_row%NOTFOUND;
396 hr_utility.set_location(l_interface_seq_num, 30);
397 l_default_value := g_riw_data(l_layout_cols_seq_row.interface_seq_num).default_value;
398 l_default_type := g_riw_data(l_layout_cols_seq_row.interface_seq_num).default_type;
399
400 IF l_default_type ='N' THEN
401 l_default_type := NULL;
402 ELSIF l_default_type ='C' THEN
403 l_default_type := 'CONSTANT';
404 ELSIF l_default_type ='E' THEN
405 l_default_type := 'ENVIRONMENT';
406 ELSIF l_default_type ='P' THEN
407 l_default_type := 'PARAMETER';
408 ELSIF l_default_type ='S' THEN
409 l_default_type := 'SQL';
410 END IF;
411
412 IF p_entity_name is NULL then
413 --$ IF "Hire Date" and "Address Date From" then set p_effective_date as default value
414 IF l_layout_cols_seq_row.interface_seq_num in (32,16)THEN
415 l_default_value := l_layout_cols_seq_row.default_value;
416 l_default_type := 'PARAMETER';
417
418 --
419 --$ When CSV or XML layout then 'Hire Date' should be read only but uploadable
420 IF (l_layout_cols_seq_row.interface_seq_num = 16
421 AND p_base_layout_code = 'PQP_FLEXIBLE_WEBADI_LAYOUT'
422 AND (p_data_source = 'XML' or p_data_source = 'CSV'))THEN
423 l_layout_cols_seq_row.READ_ONLY_FLAG := 'N';
424 END IF;
425 END IF;
426 END IF;
427 l_placement_value := g_riw_data(l_layout_cols_seq_row.interface_seq_num).placement;
428 hr_utility.set_location(l_placement_value, 56);
429
430 IF l_placement_value ='LINE' OR l_placement_value IS NULL THEN
431 hr_utility.set_location('The Value is Line', 67);
432 l_placement_block := 3;
433 l_line_exist_seq := l_line_exist_seq+10;
434 l_dummy_seq_number := l_line_exist_seq;
435 ELSIF l_placement_value ='HEADER' THEN
436 l_placement_block := 2;
437 l_head_exist_seq := l_head_exist_seq+10;
438 l_dummy_seq_number := l_head_exist_seq;
439 ELSE
440 hr_utility.set_location('The Value is Line', 67);
441 l_placement_block := 1;
442 l_context_exist_seq := l_context_exist_seq+10;
443 l_dummy_seq_number := l_context_exist_seq;
444 END IF;
445
446 l_final_seq_number := l_dummy_seq_number;
447 l_dummy_seq_number := 0;
448
449 bne_layout_cols_pkg.insert_row
450 (x_rowid => l_rowid
451 ,x_application_id => l_layout_cols_seq_row.application_id
452 ,x_layout_code => p_new_layout_code
453 ,x_block_id => l_placement_block --has to change
454 ,x_sequence_num => l_final_seq_number --has to change
455 ,x_object_version_number => 1
456 ,x_interface_app_id => l_layout_cols_seq_row.interface_app_id
457 ,x_interface_code => p_new_interface_code
458 ,x_interface_seq_num => l_layout_cols_seq_row.interface_seq_num
459 ,x_style_class => l_layout_cols_seq_row.style_class
460 ,x_hint_style => l_layout_cols_seq_row.hint_style
461 ,x_hint_style_class => l_layout_cols_seq_row.hint_style_class
462 ,x_prompt_style => l_layout_cols_seq_row.prompt_style
463 ,x_prompt_style_class => l_layout_cols_seq_row.prompt_style_class
464 ,x_default_type => l_default_type --has to change
465 ,x_default_value => l_default_value --has to change
466 ,x_style => l_layout_cols_seq_row.style
467 ,x_creation_date => SYSDATE
468 ,x_created_by => l_layout_cols_seq_row.created_by
469 ,x_last_update_date => SYSDATE
470 ,x_last_updated_by => l_layout_cols_seq_row.last_updated_by
471 ,x_last_update_login => l_layout_cols_seq_row.last_update_login
472 ,x_display_width => l_layout_cols_seq_row.display_width
473 --$ Added New Column as in BNE Layout Table
474 ,X_READ_ONLY_FLAG => l_layout_cols_seq_row.READ_ONLY_FLAG);
475
476 hr_utility.set_location('bne package is called', 10);
477 END LOOP;
478 CLOSE c_layout_cols_seq_row;
479 END LOOP;
480
481 IF p_entity_name is NULL THEN
482 --This logic to hanlde others which are not selected
483 OPEN c_layout_cols_row;
484 LOOP
485 FETCH c_layout_cols_row INTO l_layout_cols_row;
486 EXIT WHEN c_layout_cols_row%NOTFOUND;
487
488 IF g_riw_data.EXISTS(l_layout_cols_row.interface_seq_num) THEN
489 Hr_Utility.set_location('NON Exist --Exist Seq Number: '||l_layout_cols_row.interface_seq_num, 5);
490 ELSE
491 /* l_insert_flag := 'false';
492 --Checking to know the flexfields are selected by user
493 IF l_layout_cols_row.interface_seq_num = 155 THEN
494 IF g_riw_data.EXISTS(181) THEN
495 l_insert_flag := 'true';
496 END IF;
497 ELSIF l_layout_cols_row.interface_seq_num = 62 THEN
498 IF g_riw_data.EXISTS(190) THEN
499 l_insert_flag := 'true';
500 END IF;
501 ELSIF l_layout_cols_row.interface_seq_num = 19 THEN
502 IF g_riw_data.EXISTS(179) THEN
503 l_insert_flag := 'true';
504 END IF;
505 ELSIF l_layout_cols_row.interface_seq_num = 124 THEN
506 IF g_riw_data.EXISTS(180) THEN
507 l_insert_flag := 'true';
508 END IF;
509 ELSIF l_layout_cols_row.interface_seq_num = 93 THEN
510 IF g_riw_data.EXISTS(178) THEN
511 l_insert_flag := 'true';
512 END IF;
513 ELSE
514 l_insert_flag := 'true';
515 END IF;*/
516 --Insert the data only l_insert_flag is true
517 l_placement_block := l_layout_cols_row.block_id;
518 l_default_type := l_layout_cols_row.default_type;
519 l_default_value := l_layout_cols_row.DEFAULT_VALUE;
520
521 IF l_layout_cols_row.block_id = 3 then
522 l_line_display_seq := l_line_display_seq+10;
523 l_dummy_seq_number := l_line_display_seq;
524 ELSIF l_layout_cols_row.block_id = 2 THEN
525 l_head_display_seq := l_head_display_seq+10;
526 l_dummy_seq_number := l_head_display_seq;
527 ELSE
528 l_context_display_seq :=l_context_display_seq+10;
529 l_dummy_seq_number := l_context_display_seq;
530 END IF;
531
532 --Defaulting
533 l_final_seq_number := l_dummy_seq_number;
534 l_dummy_seq_number := 0;
535
536 bne_layout_cols_pkg.insert_row
537 (x_rowid => l_rowid
538 ,x_application_id => l_layout_cols_row.application_id
539 ,x_layout_code => p_new_layout_code
540 ,x_block_id => l_layout_cols_row.block_id
541 ,x_sequence_num => l_final_seq_number --has to change
542 ,x_object_version_number => 1
543 ,x_interface_app_id => l_layout_cols_row.interface_app_id
544 ,x_interface_code => p_new_interface_code
545 ,x_interface_seq_num => l_layout_cols_row.interface_seq_num
546 ,x_style_class => l_layout_cols_row.style_class
547 ,x_hint_style => l_layout_cols_row.hint_style
548 ,x_hint_style_class => l_layout_cols_row.hint_style_class
549 ,x_prompt_style => l_layout_cols_row.prompt_style
550 ,x_prompt_style_class => l_layout_cols_row.prompt_style_class
551 ,x_default_type => l_layout_cols_row.default_type
552 ,x_default_value => l_layout_cols_row.default_value
553 ,x_style => l_layout_cols_row.style
554 ,x_creation_date => SYSDATE
555 ,x_created_by => l_layout_cols_row.created_by
556 ,x_last_update_date => SYSDATE
557 ,x_last_updated_by => l_layout_cols_row.last_updated_by
558 ,x_last_update_login => l_layout_cols_row.last_update_login
559 --$ Added New Column as in BNE Layout Table
560 ,X_READ_ONLY_FLAG => l_layout_cols_row.READ_ONLY_FLAG);
561 hr_utility.trace('layout col'||l_layout_cols_row.interface_seq_num);
562 END IF;
563 END LOOP;
564 CLOSE c_layout_cols_row;
565 ELSE
566 SELECT distinct(interface_code) into l_base_intf_code
567 from bne_layout_cols where layout_code = p_base_layout_code;
568 Select sequence_num into l_context_seq_num from bne_interface_cols_b
569 where interface_code = l_base_intf_code and
570 interface_col_name = 'P_WEBADI_CONTEXT';
571 Select sequence_num into l_header_seq_num from bne_interface_cols_b
572 where interface_code = l_base_intf_code and
573 interface_col_name = 'P_WEBADI_HEADER';
574 Select sequence_num into l_bg_seq_num from bne_interface_cols_b
575 where interface_code = l_base_intf_code and
576 interface_col_name = 'BUSINESS_GRP_NAME';
577
578 OPEN c_layout_cols_row_others(c_seq_num => l_context_seq_num);
579 FETCH c_layout_cols_row_others
580 INTO l_layout_cols_row_others;
581 l_placement_block := l_layout_cols_row_others.block_id;
582 l_default_type := l_layout_cols_row_others.default_type;
583 l_default_value := l_layout_cols_row_others.DEFAULT_VALUE;
584 hr_utility.set_location('It is in the loop ', 88);
585 IF l_layout_cols_row_others.block_id = 3 then
586 l_line_display_seq := l_line_display_seq+10;
587 l_dummy_seq_number := l_line_display_seq;
588 ELSIF l_layout_cols_row_others.block_id = 2 THEN
589 l_head_display_seq := l_head_display_seq+10;
590 l_dummy_seq_number := l_head_display_seq;
591 ELSE
592 l_context_display_seq :=l_context_display_seq+10;
593 l_dummy_seq_number := l_context_display_seq;
594 END IF;
595
596 --Defaulting
597 l_final_seq_number := l_dummy_seq_number;
598 l_dummy_seq_number := 0;
599
600 bne_layout_cols_pkg.insert_row
601 (x_rowid => l_rowid
602 ,x_application_id => l_layout_cols_row_others.application_id
603 ,x_layout_code => p_new_layout_code
604 ,x_block_id => l_layout_cols_row_others.block_id
605 ,x_sequence_num => l_final_seq_number --has to change
606 ,x_object_version_number => 1
607 ,x_interface_app_id => l_layout_cols_row_others.interface_app_id
608 ,x_interface_code => p_new_interface_code
609 ,x_interface_seq_num => l_layout_cols_row_others.interface_seq_num
610 ,x_style_class => l_layout_cols_row_others.style_class
611 ,x_hint_style => l_layout_cols_row_others.hint_style
612 ,x_hint_style_class => l_layout_cols_row_others.hint_style_class
613 ,x_prompt_style => l_layout_cols_row_others.prompt_style
614 ,x_prompt_style_class => l_layout_cols_row_others.prompt_style_class
615 ,x_default_type => l_layout_cols_row_others.default_type
616 ,x_default_value => l_layout_cols_row_others.default_value
617 ,x_style => l_layout_cols_row_others.style
618 ,x_creation_date => SYSDATE
619 ,x_created_by => l_layout_cols_row_others.created_by
620 ,x_last_update_date => SYSDATE
621 ,x_last_updated_by => l_layout_cols_row_others.last_updated_by
622 ,x_last_update_login => l_layout_cols_row_others.last_update_login
623 --$ Added New Column as in BNE Layout Table
624 ,X_READ_ONLY_FLAG => l_layout_cols_row_others.READ_ONLY_FLAG);
625
626 CLOSE c_layout_cols_row_others;
627
628 OPEN c_layout_cols_row_others(c_seq_num => l_header_seq_num);
629 FETCH c_layout_cols_row_others
630 INTO l_layout_cols_row_others;
631 l_placement_block := l_layout_cols_row_others.block_id;
632 l_default_type := l_layout_cols_row_others.default_type;
633 l_default_value := l_layout_cols_row_others.DEFAULT_VALUE;
634 hr_utility.set_location('It is in the loop ', 88);
635 IF l_layout_cols_row_others.block_id = 3 then
636 l_line_display_seq := l_line_display_seq+10;
637 l_dummy_seq_number := l_line_display_seq;
638 ELSIF l_layout_cols_row_others.block_id = 2 THEN
639 l_head_display_seq := l_head_display_seq+10;
640 l_dummy_seq_number := l_head_display_seq;
641 ELSE
642 l_context_display_seq :=l_context_display_seq+10;
643 l_dummy_seq_number := l_context_display_seq;
644 END IF;
645
646 --Defaulting
647 l_final_seq_number := l_dummy_seq_number;
648 l_dummy_seq_number := 0;
649
650 bne_layout_cols_pkg.insert_row
651 (x_rowid => l_rowid
652 ,x_application_id => l_layout_cols_row_others.application_id
653 ,x_layout_code => p_new_layout_code
654 ,x_block_id => l_layout_cols_row_others.block_id
655 ,x_sequence_num => l_final_seq_number --has to change
656 ,x_object_version_number => 1
657 ,x_interface_app_id => l_layout_cols_row_others.interface_app_id
658 ,x_interface_code => p_new_interface_code
659 ,x_interface_seq_num => l_layout_cols_row_others.interface_seq_num
660 ,x_style_class => l_layout_cols_row_others.style_class
661 ,x_hint_style => l_layout_cols_row_others.hint_style
662 ,x_hint_style_class => l_layout_cols_row_others.hint_style_class
663 ,x_prompt_style => l_layout_cols_row_others.prompt_style
664 ,x_prompt_style_class => l_layout_cols_row_others.prompt_style_class
665 ,x_default_type => l_layout_cols_row_others.default_type
666 ,x_default_value => l_layout_cols_row_others.default_value
667 ,x_style => l_layout_cols_row_others.style
668 ,x_creation_date => SYSDATE
669 ,x_created_by => l_layout_cols_row_others.created_by
670 ,x_last_update_date => SYSDATE
671 ,x_last_updated_by => l_layout_cols_row_others.last_updated_by
672 ,x_last_update_login => l_layout_cols_row_others.last_update_login
673 --$ Added New Column as in BNE Layout Table
674 ,X_READ_ONLY_FLAG => l_layout_cols_row_others.READ_ONLY_FLAG);
675
676 CLOSE c_layout_cols_row_others;
677
678 OPEN c_layout_cols_row_others(c_seq_num => l_bg_seq_num);
679 FETCH c_layout_cols_row_others
680 INTO l_layout_cols_row_others;
681 l_placement_block := l_layout_cols_row_others.block_id;
682 l_default_type := l_layout_cols_row_others.default_type;
683 l_default_value := l_layout_cols_row_others.DEFAULT_VALUE;
684 hr_utility.set_location('It is in the loop ', 88);
685 IF l_layout_cols_row_others.block_id = 3 then
686 l_line_display_seq := l_line_display_seq+10;
687 l_dummy_seq_number := l_line_display_seq;
688 ELSIF l_layout_cols_row_others.block_id = 2 THEN
689 l_head_display_seq := l_head_display_seq+10;
690 l_dummy_seq_number := l_head_display_seq;
691 ELSE
692 l_context_display_seq :=l_context_display_seq+10;
693 l_dummy_seq_number := l_context_display_seq;
694 END IF;
695
696 --Defaulting
697 l_final_seq_number := l_dummy_seq_number;
698 l_dummy_seq_number := 0;
699
700 bne_layout_cols_pkg.insert_row
701 (x_rowid => l_rowid
702 ,x_application_id => l_layout_cols_row_others.application_id
703 ,x_layout_code => p_new_layout_code
704 ,x_block_id => l_layout_cols_row_others.block_id
705 ,x_sequence_num => l_final_seq_number --has to change
706 ,x_object_version_number => 1
707 ,x_interface_app_id => l_layout_cols_row_others.interface_app_id
708 ,x_interface_code => p_new_interface_code
709 ,x_interface_seq_num => l_layout_cols_row_others.interface_seq_num
710 ,x_style_class => l_layout_cols_row_others.style_class
711 ,x_hint_style => l_layout_cols_row_others.hint_style
712 ,x_hint_style_class => l_layout_cols_row_others.hint_style_class
713 ,x_prompt_style => l_layout_cols_row_others.prompt_style
714 ,x_prompt_style_class => l_layout_cols_row_others.prompt_style_class
715 ,x_default_type => l_layout_cols_row_others.default_type
716 ,x_default_value => l_layout_cols_row_others.default_value
717 ,x_style => l_layout_cols_row_others.style
718 ,x_creation_date => SYSDATE
719 ,x_created_by => l_layout_cols_row_others.created_by
720 ,x_last_update_date => SYSDATE
721 ,x_last_updated_by => l_layout_cols_row_others.last_updated_by
722 ,x_last_update_login => l_layout_cols_row_others.last_update_login
723 --$ Added New Column as in BNE Layout Table
724 ,X_READ_ONLY_FLAG => l_layout_cols_row_others.READ_ONLY_FLAG);
725
726 CLOSE c_layout_cols_row_others;
727
728 END IF;
729
730 END Create_RIW_Layout_Cols_Row;
731
732
733
734 -- =============================================================================
735 -- ~ Create_RIW_Layout_Blocks_Row:
736 -- =============================================================================
737 PROCEDURE Create_RIW_Layout_Blocks_Row
738 (p_application_id IN NUMBER
739 ,p_new_layout_code IN VARCHAR2
740 ,p_base_layout_code IN VARCHAR2
741 ,p_user_name IN VARCHAR2) IS
742
743 CURSOR c_layout_blocks_row IS
744 SELECT application_id
745 ,block_id
746 ,parent_id
747 ,layout_element
748 ,style_class
749 ,style
750 ,row_style_class
751 ,row_style
752 ,col_style_class
753 ,col_style
754 ,prompt_displayed_flag
755 ,prompt_style_class
756 ,prompt_style
757 ,hint_displayed_flag
758 ,hint_style_class
759 ,hint_style
760 ,orientation
761 ,layout_control
762 ,display_flag
763 ,BLOCKSIZE
764 ,minsize
765 ,MAXSIZE
766 ,sequence_num
767 ,prompt_colspan
768 ,hint_colspan
769 ,row_colspan
770 ,summary_style_class
771 ,summary_style
772 ,created_by
773 ,last_updated_by
774 ,last_update_login
775 ,user_name
776 FROM bne_layout_blocks_vl
777 WHERE application_id = p_application_id
778 AND layout_code = p_base_layout_code
779 ORDER BY block_id;
780
781 l_layout_blocks_row c_layout_blocks_row%ROWTYPE;
782 l_rowid VARCHAR2(200);
783
784 BEGIN
785 OPEN c_layout_blocks_row;
786 LOOP
787 FETCH c_layout_blocks_row INTO l_layout_blocks_row;
788 EXIT WHEN c_layout_blocks_row%NOTFOUND;
789
790 bne_layout_blocks_pkg.insert_row
791 (x_rowid => l_rowid
792 ,x_application_id => l_layout_blocks_row.application_id
793 ,x_layout_code => p_new_layout_code
794 ,x_block_id => l_layout_blocks_row.block_id
795 ,x_object_version_number => 1
796 ,x_parent_id => l_layout_blocks_row.parent_id
797 ,x_layout_element => l_layout_blocks_row.layout_element
798 ,x_style_class => l_layout_blocks_row.style_class
799 ,x_style => l_layout_blocks_row.style
800 ,x_row_style_class => l_layout_blocks_row.row_style_class
801 ,x_row_style => l_layout_blocks_row.row_style
802 ,x_col_style_class => l_layout_blocks_row.col_style_class
803 ,x_col_style => l_layout_blocks_row.col_style
804 ,x_prompt_displayed_flag => l_layout_blocks_row.prompt_displayed_flag
805 ,x_prompt_style_class => l_layout_blocks_row.prompt_style_class
806 ,x_prompt_style => l_layout_blocks_row.prompt_style
807 ,x_hint_displayed_flag => l_layout_blocks_row.hint_displayed_flag
808 ,x_hint_style_class => l_layout_blocks_row.hint_style_class
809 ,x_hint_style => l_layout_blocks_row.hint_style
810 ,x_orientation => l_layout_blocks_row.orientation
811 ,x_layout_control => l_layout_blocks_row.layout_control
812 ,x_display_flag => l_layout_blocks_row.display_flag
813 ,x_blocksize => l_layout_blocks_row.BLOCKSIZE
814 ,x_minsize => l_layout_blocks_row.minsize
815 ,x_maxsize => l_layout_blocks_row.MAXSIZE
816 ,x_sequence_num => l_layout_blocks_row.sequence_num
817 ,x_prompt_colspan => l_layout_blocks_row.prompt_colspan
818 ,x_hint_colspan => l_layout_blocks_row.hint_colspan
819 ,x_row_colspan => l_layout_blocks_row.row_colspan
820 ,x_summary_style_class => l_layout_blocks_row.summary_style_class
821 ,x_summary_style => l_layout_blocks_row.summary_style
822 ,x_user_name => l_layout_blocks_row.user_name
823 ,x_creation_date => SYSDATE
824 ,x_created_by => l_layout_blocks_row.created_by
825 ,x_last_update_date => SYSDATE
826 ,x_last_updated_by => l_layout_blocks_row.last_updated_by
827 ,x_last_update_login => l_layout_blocks_row.last_update_login);
828 END LOOP;
829
830 CLOSE c_layout_blocks_row;
831 END Create_RIW_Layout_Blocks_Row;
832
833
834
835 -- =============================================================================
836 -- ~ Create_RIW_Interface_Row:
837 -- =============================================================================
838 PROCEDURE Create_RIW_Interface_Row
839 (p_application_id IN NUMBER
840 ,p_new_interface_code IN VARCHAR2
841 ,p_base_interface_code IN VARCHAR2
842 ,p_user_name IN VARCHAR2
843 ,p_new_intg_code IN VARCHAR2) IS
844
845 CURSOR c_interface_row
846 IS
847 SELECT APPLICATION_ID
848 ,INTERFACE_CODE
849 ,OBJECT_VERSION_NUMBER
850 ,INTEGRATOR_APP_ID
851 ,INTEGRATOR_CODE
852 ,INTERFACE_NAME
853 ,UPLOAD_TYPE
854 ,UPLOAD_OBJ_NAME
855 ,UPLOAD_PARAM_LIST_APP_ID
856 ,UPLOAD_PARAM_LIST_CODE
857 ,UPLOAD_ORDER
858 ,CREATED_BY
859 ,CREATION_DATE
860 ,LAST_UPDATED_BY
861 ,LAST_UPDATE_LOGIN
862 ,LAST_UPDATE_DATE
863 FROM BNE_INTERFACES_B
864 WHERE application_id = p_application_id
865 AND INTERFACE_CODE = p_base_interface_code;
866
867 l_interface_row c_interface_row%ROWTYPE;
868 l_rowid VARCHAR2(200);
869 no_default_layout EXCEPTION;
870 VV_INTERFACE_CODE BNE_INTERFACES_B.INTERFACE_CODE%TYPE;
871 l_proc_name VARCHAR2(72) := g_proc_name||'Create_RIW_Interface_Row';
872
873 BEGIN
874 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
875 OPEN c_interface_row;
876 FETCH c_interface_row INTO l_interface_row;
877 IF c_interface_row%NOTFOUND THEN
878 RAISE no_default_layout;
879 END IF;
880 CLOSE c_interface_row;
881
882 -- Check that the OBJECT_CODE for this Interface is unique for the Application ID.
883 BEGIN
884 SELECT INTERFACE_CODE
885 INTO VV_INTERFACE_CODE
886 FROM BNE_INTERFACES_B
887 WHERE APPLICATION_ID = P_APPLICATION_ID
888 AND INTERFACE_CODE = p_new_interface_code;
889 EXCEPTION
890 WHEN NO_DATA_FOUND THEN NULL;
891 END;
892
893 -- Create the interface in the BNE_INTERFACES_B table
894 IF (VV_INTERFACE_CODE IS NULL) THEN
895 INSERT INTO BNE_INTERFACES_B
896 (APPLICATION_ID
897 ,INTERFACE_CODE
898 ,OBJECT_VERSION_NUMBER
899 ,INTEGRATOR_APP_ID
900 ,INTEGRATOR_CODE
901 ,INTERFACE_NAME
902 ,UPLOAD_TYPE
903 ,UPLOAD_PARAM_LIST_APP_ID
904 ,UPLOAD_PARAM_LIST_CODE
905 ,CREATED_BY
906 ,CREATION_DATE
907 ,LAST_UPDATED_BY
908 ,LAST_UPDATE_DATE)
909 VALUES
910 (l_interface_row.APPLICATION_ID
911 ,p_new_interface_code
912 ,1
913 ,l_interface_row.APPLICATION_ID
914 ,p_new_intg_code--l_interface_row.INTEGRATOR_CODE
915 ,l_interface_row.INTERFACE_NAME
916 ,l_interface_row.UPLOAD_TYPE
917 ,l_interface_row.UPLOAD_PARAM_LIST_APP_ID
918 ,l_interface_row.UPLOAD_PARAM_LIST_CODE
919 ,l_interface_row.CREATED_BY
920 ,SYSDATE
921 ,l_interface_row.CREATED_BY
922 ,SYSDATE);
923
924 -- Create the interface in the BNE_INTERFACES_TL table
925 INSERT INTO BNE_INTERFACES_TL
926 (APPLICATION_ID
927 ,INTERFACE_CODE
928 ,LANGUAGE
929 ,SOURCE_LANG
930 ,USER_NAME
931 ,CREATED_BY
932 ,CREATION_DATE
933 ,LAST_UPDATED_BY
934 ,LAST_UPDATE_DATE)
935 VALUES
936 (l_interface_row.APPLICATION_ID
937 ,p_new_interface_code
938 ,userenv('LANG')
939 ,userenv('LANG')
940 ,p_user_name
941 ,l_interface_row.CREATED_BY
942 ,SYSDATE
943 ,l_interface_row.CREATED_BY
944 ,SYSDATE);
945 END IF;
946 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
947
948 END Create_RIW_Interface_Row;
949
950
951 -- =============================================================================
952 -- ~ Create_RIW_Interface_Col_Rows:
953 -- =============================================================================
954 PROCEDURE Create_RIW_Interface_Col_Rows
955 (p_application_id IN NUMBER
956 ,p_new_interface_code IN VARCHAR2
957 ,p_base_interface_code IN VARCHAR2
958 ,p_entity_name IN VARCHAR2 DEFAULT NULL) IS
959
960 CURSOR c_interface_cols_row
961 IS
962 SELECT APPLICATION_ID
963 ,INTERFACE_CODE
964 ,OBJECT_VERSION_NUMBER
965 ,SEQUENCE_NUM
966 ,INTERFACE_COL_TYPE
967 ,INTERFACE_COL_NAME
968 ,ENABLED_FLAG
969 ,REQUIRED_FLAG
970 ,DISPLAY_FLAG
971 ,READ_ONLY_FLAG
972 ,NOT_NULL_FLAG
973 ,SUMMARY_FLAG
974 ,MAPPING_ENABLED_FLAG
975 ,DATA_TYPE
976 ,FIELD_SIZE
977 ,DEFAULT_TYPE
978 ,DEFAULT_VALUE
979 ,SEGMENT_NUMBER
980 ,GROUP_NAME
981 ,OA_FLEX_CODE
982 ,OA_CONCAT_FLEX
983 ,VAL_TYPE
984 ,VAL_ID_COL
985 ,VAL_MEAN_COL
986 ,VAL_DESC_COL
987 ,VAL_OBJ_NAME
988 ,VAL_ADDL_W_C
989 ,VAL_COMPONENT_APP_ID
990 ,VAL_COMPONENT_CODE
991 ,OA_FLEX_NUM
992 ,OA_FLEX_APPLICATION_ID
993 ,DISPLAY_ORDER
994 ,UPLOAD_PARAM_LIST_ITEM_NUM
995 ,EXPANDED_SQL_QUERY
996 ,CREATED_BY
997 ,CREATION_DATE
998 ,LAST_UPDATED_BY
999 ,LAST_UPDATE_LOGIN
1000 ,LAST_UPDATE_DATE
1001 ,LOV_TYPE
1002 ,OFFLINE_LOV_ENABLED_FLAG
1003 ,VARIABLE_DATA_TYPE_CLASS
1004 FROM BNE_INTERFACE_COLS_B
1005 WHERE application_id = p_application_id
1006 AND INTERFACE_CODE = p_base_interface_code;
1007
1008
1009 CURSOR c_interface_tl_cols_row(c_seq_num Number , user_lang varchar2)
1010 IS
1011 SELECT APPLICATION_ID
1012 ,INTERFACE_CODE
1013 ,SEQUENCE_NUM
1014 ,LANGUAGE
1015 ,SOURCE_LANG
1016 ,USER_HINT
1017 ,PROMPT_LEFT
1018 ,USER_HELP_TEXT
1019 ,PROMPT_ABOVE
1020 ,CREATED_BY
1021 ,CREATION_DATE
1022 ,LAST_UPDATED_BY
1023 ,LAST_UPDATE_DATE
1024 FROM BNE_INTERFACE_COLS_TL
1025 WHERE application_id = p_application_id
1026 AND INTERFACE_CODE = p_base_interface_code
1027 AND SEQUENCE_NUM = c_seq_num
1028 AND LANGUAGE = user_lang;
1029
1030
1031 l_interface_cols_row c_interface_cols_row%ROWTYPE;
1032 l_interface_tl_cols_row c_interface_tl_cols_row%ROWTYPE;
1033 l_rowid VARCHAR2(200);
1034 no_default_layout EXCEPTION;
1035 l_proc_name VARCHAR2(72) := g_package||'Create_RIW_Interface_Col_Rows';
1036 VN_NO_INTERFACE_COL_FLAG NUMBER ;
1037 l_display VARCHAR2(1);
1038 l_default_type BNE_INTERFACE_COLS_B.default_type%TYPE;
1039 l_default_value BNE_INTERFACE_COLS_B.default_value%TYPE;
1040 l_prompt_left BNE_INTERFACE_COLS_TL.PROMPT_LEFT%TYPE;
1041 l_prompt_above BNE_INTERFACE_COLS_TL.PROMPT_ABOVE%TYPE;
1042 l_crt_upd_seq_num BNE_INTERFACE_COLS_B.SEQUENCE_NUM%TYPE;
1043
1044
1045 BEGIN
1046 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1047 -- Check the BNE_INTERFACE_COLS_B table to ensure that the record
1048 -- does not already exist
1049 VN_NO_INTERFACE_COL_FLAG := 0;
1050
1051 SELECT sequence_num INTO l_crt_upd_seq_num FROM BNE_INTERFACE_COLS_B WHERE
1052 INTERFACE_CODE = p_base_interface_code AND
1053 INTERFACE_COL_NAME = 'P_CRT_UPD';
1054
1055 BEGIN
1056 SELECT 1
1057 INTO VN_NO_INTERFACE_COL_FLAG
1058 FROM BNE_INTERFACE_COLS_B
1059 WHERE APPLICATION_ID = P_APPLICATION_ID
1060 AND INTERFACE_CODE = p_new_interface_code;
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN NULL;
1063 END;
1064
1065 hr_utility.set_location('Loop Outside', 90);
1066 -- If the Interface Column was not found then create
1067 IF (VN_NO_INTERFACE_COL_FLAG = 0) THEN
1068 OPEN c_interface_cols_row;
1069 LOOP
1070 FETCH c_interface_cols_row INTO l_interface_cols_row;
1071 EXIT WHEN c_interface_cols_row%NOTFOUND;
1072 hr_utility.set_location(l_interface_cols_row.sequence_num, 78);
1073 IF p_entity_name IS NULL then
1074 IF l_interface_cols_row.SEQUENCE_NUM IN (352) THEN
1075 l_default_type := 'CONSTANT';
1076 l_default_value := g_ins_upd_flag;
1077 ELSE
1078 l_default_type := l_interface_cols_row.DEFAULT_TYPE;
1079 l_default_value := l_interface_cols_row.DEFAULT_VALUE;
1080 END IF;
1081
1082 IF g_riw_data.EXISTS(l_interface_cols_row.SEQUENCE_NUM) THEN
1083 l_display := 'Y';
1084 ELSIF l_interface_cols_row.SEQUENCE_NUM IN (59,176,253,254,332,334,335,333,
1085 331,330,328,177,340, --Displaying cols like Business Group Id, Batch ID,
1086 --Batch name , Instructions etc.
1087 351, --$ Display P_BATCH_EXCEPTION also
1088 350 --$ Display P_BATCH_LINK also as read only column
1089 ) THEN
1090 l_display := l_interface_cols_row.DISPLAY_FLAG;
1091 ELSIF g_riw_data.EXISTS(181) AND l_interface_cols_row.SEQUENCE_NUM IN (155) THEN
1092 l_display := 'Y';
1093 ELSIF g_riw_data.EXISTS(190) AND l_interface_cols_row.SEQUENCE_NUM IN (62) THEN
1094 l_display := 'Y';
1095 ELSIF g_riw_data.EXISTS(179) AND l_interface_cols_row.SEQUENCE_NUM IN (19) THEN
1096 l_display := 'Y';
1097 ELSIF g_riw_data.EXISTS(180) AND l_interface_cols_row.SEQUENCE_NUM IN (124) THEN
1098 l_display := 'Y';
1099 ELSIF g_riw_data.EXISTS(178) AND l_interface_cols_row.SEQUENCE_NUM IN (93) THEN
1100 l_display := 'Y';
1101 ELSE
1102 l_display := 'N';
1103 END IF;
1104 ELSE
1105
1106 IF l_interface_cols_row.SEQUENCE_NUM IN (l_crt_upd_seq_num) THEN
1107 hr_utility.set_location('The Flag in cols row is ' || g_ins_upd_flag, 89);
1108 l_default_type := 'CONSTANT';
1109 l_default_value := g_ins_upd_flag;
1110 ELSE
1111 l_default_type := l_interface_cols_row.DEFAULT_TYPE;
1112 l_default_value := l_interface_cols_row.DEFAULT_VALUE;
1113 END IF;
1114 IF g_riw_data.EXISTS(l_interface_cols_row.SEQUENCE_NUM) THEN
1115 l_display := 'Y';
1116 ELSE
1117 l_display := l_interface_cols_row.DISPLAY_FLAG;
1118 END IF;
1119 END IF;
1120
1121 -- l_display := l_interface_cols_row.DISPLAY_FLAG;
1122 -- Insert the required row in BNE_INTERFACE_COLS_B
1123 INSERT INTO BNE_INTERFACE_COLS_B
1124 (APPLICATION_ID
1125 ,INTERFACE_CODE
1126 ,OBJECT_VERSION_NUMBER
1127 ,SEQUENCE_NUM
1128 ,INTERFACE_COL_TYPE
1129 ,INTERFACE_COL_NAME
1130 ,ENABLED_FLAG
1131 ,REQUIRED_FLAG
1132 ,DISPLAY_FLAG
1133 ,READ_ONLY_FLAG
1134 ,NOT_NULL_FLAG
1135 ,SUMMARY_FLAG
1136 ,MAPPING_ENABLED_FLAG
1137 ,DATA_TYPE
1138 ,FIELD_SIZE
1139 ,DEFAULT_TYPE
1140 ,DEFAULT_VALUE
1141 ,SEGMENT_NUMBER
1142 ,GROUP_NAME
1143 ,OA_FLEX_CODE
1144 ,OA_CONCAT_FLEX
1145 ,VAL_TYPE
1146 ,VAL_ID_COL
1147 ,VAL_MEAN_COL
1148 ,VAL_DESC_COL
1149 ,VAL_OBJ_NAME
1150 ,VAL_ADDL_W_C
1151 ,VAL_COMPONENT_APP_ID
1152 ,VAL_COMPONENT_CODE
1153 ,OA_FLEX_NUM
1154 ,OA_FLEX_APPLICATION_ID
1155 ,DISPLAY_ORDER
1156 ,UPLOAD_PARAM_LIST_ITEM_NUM
1157 ,EXPANDED_SQL_QUERY
1158 ,LOV_TYPE
1159 ,OFFLINE_LOV_ENABLED_FLAG
1160 ,VARIABLE_DATA_TYPE_CLASS
1161 ,CREATED_BY
1162 ,CREATION_DATE
1163 ,LAST_UPDATED_BY
1164 ,LAST_UPDATE_DATE)
1165 VALUES
1166 (l_interface_cols_row.APPLICATION_ID
1167 ,p_new_interface_code
1168 ,1
1169 ,l_interface_cols_row.SEQUENCE_NUM
1170 ,l_interface_cols_row.INTERFACE_COL_TYPE
1171 ,l_interface_cols_row.INTERFACE_COL_NAME
1172 ,l_interface_cols_row.ENABLED_FLAG
1173 ,l_interface_cols_row.REQUIRED_FLAG
1174 ,l_display --have to change based on layout selection
1175 ,NVL(l_interface_cols_row.READ_ONLY_FLAG,'N')
1176 ,l_interface_cols_row.NOT_NULL_FLAG
1177 ,NVL(l_interface_cols_row.SUMMARY_FLAG,'N')
1178 ,l_interface_cols_row.MAPPING_ENABLED_FLAG
1179 ,l_interface_cols_row.DATA_TYPE
1180 ,l_interface_cols_row.FIELD_SIZE
1181 ,l_default_type
1182 ,l_default_value
1183 ,l_interface_cols_row.SEGMENT_NUMBER
1184 ,l_interface_cols_row.GROUP_NAME
1185 ,l_interface_cols_row.OA_FLEX_CODE
1186 ,l_interface_cols_row.OA_CONCAT_FLEX
1187 ,l_interface_cols_row.VAL_TYPE
1188 ,l_interface_cols_row.VAL_ID_COL
1189 ,l_interface_cols_row.VAL_MEAN_COL
1190 ,l_interface_cols_row.VAL_DESC_COL
1191 ,l_interface_cols_row.VAL_OBJ_NAME
1192 ,l_interface_cols_row.VAL_ADDL_W_C
1193 ,l_interface_cols_row.VAL_COMPONENT_APP_ID
1194 ,l_interface_cols_row.VAL_COMPONENT_CODE
1195 ,l_interface_cols_row.OA_FLEX_NUM
1196 ,l_interface_cols_row.OA_FLEX_APPLICATION_ID
1197 ,l_interface_cols_row.DISPLAY_ORDER
1198 ,l_interface_cols_row.UPLOAD_PARAM_LIST_ITEM_NUM
1199 ,l_interface_cols_row.EXPANDED_SQL_QUERY
1200 ,l_interface_cols_row.LOV_TYPE
1201 ,l_interface_cols_row.OFFLINE_LOV_ENABLED_FLAG
1202 ,l_interface_cols_row.VARIABLE_DATA_TYPE_CLASS
1203 ,l_interface_cols_row.CREATED_BY
1204 ,SYSDATE
1205 ,l_interface_cols_row.CREATED_BY
1206 ,SYSDATE);
1207 hr_utility.set_location('The cols_b got created successfully', 79) ;
1208
1209 -- Insert the required row in BNE_INTERFACE_COLS_TL only if P_LANGUAGE is populated
1210
1211 OPEN c_interface_tl_cols_row(l_interface_cols_row.SEQUENCE_NUM , userenv('LANG'));
1212 FETCH c_interface_tl_cols_row into l_interface_tl_cols_row;
1213 hr_utility.set_location(l_interface_tl_cols_row.sequence_num, 80);
1214 IF c_interface_tl_cols_row%NOTFOUND THEN
1215 RAISE no_default_layout;
1216 END IF;
1217 CLOSE c_interface_tl_cols_row;
1218
1219 IF g_riw_data.EXISTS(l_interface_cols_row.SEQUENCE_NUM) THEN
1220 l_prompt_left := g_riw_data(l_interface_cols_row.SEQUENCE_NUM).xml_tag ;
1221 l_prompt_above := l_prompt_left;
1222 ELSE
1223 l_prompt_left := l_interface_tl_cols_row.PROMPT_LEFT;
1224 l_prompt_above := l_prompt_left;
1225 END IF;
1226
1227 INSERT INTO BNE_INTERFACE_COLS_TL
1228 (APPLICATION_ID
1229 ,INTERFACE_CODE
1230 ,SEQUENCE_NUM
1231 ,LANGUAGE
1232 ,SOURCE_LANG
1233 ,USER_HINT
1234 ,PROMPT_LEFT
1235 ,USER_HELP_TEXT
1236 ,PROMPT_ABOVE
1237 ,CREATED_BY
1238 ,CREATION_DATE
1239 ,LAST_UPDATED_BY
1240 ,LAST_UPDATE_DATE)
1241 VALUES
1242 (l_interface_tl_cols_row.APPLICATION_ID
1243 ,p_new_interface_code
1244 ,l_interface_tl_cols_row.SEQUENCE_NUM
1245 ,userenv('LANG')
1246 ,userenv('LANG')
1247 ,l_interface_tl_cols_row.USER_HINT
1248 ,l_prompt_left --have to change based on layout selection
1249 ,l_interface_tl_cols_row.USER_HELP_TEXT
1250 ,l_prompt_above --have to change based on layout selection
1251 ,l_interface_cols_row.CREATED_BY
1252 ,SYSDATE
1253 ,l_interface_cols_row.CREATED_BY
1254 ,SYSDATE);
1255 hr_utility.set_location('TL Records succes', 81);
1256
1257
1258 END LOOP;
1259 CLOSE c_interface_cols_row;
1260 END IF;
1261 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1262
1263 END Create_RIW_Interface_Col_Rows;
1264
1265
1266 -- =============================================================================
1267 -- ~ Create_RIW_Layout_Row:
1268 -- =============================================================================
1269 PROCEDURE Create_RIW_Layout_Row
1270 (p_application_id IN NUMBER
1271 ,p_new_layout_code IN VARCHAR2
1272 ,p_base_layout_code IN VARCHAR2
1273 ,p_user_name IN VARCHAR2
1274 ,p_new_intg_code IN VARCHAR2) IS
1275
1276
1277 CURSOR c_layout_row
1278 IS
1279 SELECT application_id
1280 ,object_version_number
1281 ,stylesheet_app_id
1282 ,stylesheet_code
1283 ,integrator_app_id
1284 ,integrator_code
1285 ,style
1286 ,style_class
1287 ,reporting_flag
1288 ,reporting_interface_app_id
1289 ,reporting_interface_code
1290 ,created_by
1291 ,last_updated_by
1292 ,last_update_login
1293 ,create_doc_list_app_id
1294 ,create_doc_list_code
1295 FROM bne_layouts_b
1296 WHERE application_id = p_application_id
1297 AND layout_code = p_base_layout_code;
1298
1299 l_layout_row c_layout_row%ROWTYPE;
1300 l_rowid VARCHAR2(200);
1301 no_default_layout EXCEPTION;
1302 l_proc_name VARCHAR2(72) := g_package||'Create_RIW_Layout_Row';
1303
1304 BEGIN
1305 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1306
1307 OPEN c_layout_row;
1308 FETCH c_layout_row INTO l_layout_row;
1309 IF c_layout_row%NOTFOUND THEN
1310 RAISE no_default_layout;
1311 END IF;
1312 CLOSE c_layout_row;
1313
1314 bne_layouts_pkg.insert_row
1315 (x_rowid => l_rowid
1316 ,x_application_id => l_layout_row.application_id
1317 ,x_layout_code => p_new_layout_code
1318 ,x_object_version_number => 1
1319 ,x_stylesheet_app_id => l_layout_row.stylesheet_app_id
1320 ,x_stylesheet_code => l_layout_row.stylesheet_code
1321 ,x_integrator_app_id => l_layout_row.integrator_app_id
1322 ,x_integrator_code => p_new_intg_code--l_layout_row.integrator_code
1323 ,x_style => l_layout_row.style
1324 ,x_style_class => l_layout_row.style_class
1325 ,x_reporting_flag => l_layout_row.reporting_flag
1326 ,x_reporting_interface_app_id => l_layout_row.reporting_interface_app_id
1327 ,x_reporting_interface_code => l_layout_row.reporting_interface_code
1328 ,x_user_name => p_user_name
1329 ,x_creation_date => SYSDATE
1330 ,x_created_by => l_layout_row.created_by
1331 ,x_last_update_date => SYSDATE
1332 ,x_last_updated_by => l_layout_row.last_updated_by
1333 ,x_last_update_login => l_layout_row.last_update_login
1334 ,x_create_doc_list_app_id => l_layout_row.create_doc_list_app_id
1335 ,x_create_doc_list_code => l_layout_row.create_doc_list_code);
1336 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1337
1338 END Create_RIW_Layout_Row;
1339
1340
1341
1342 -- =============================================================================
1343 -- ~ Create_RIW_OAF_Function:
1344 -- =============================================================================
1345 PROCEDURE Create_RIW_OAF_Function
1346 (p_application_id IN NUMBER
1347 ,p_function_name IN VARCHAR2
1348 ,p_base_function_name IN VARCHAR2
1349 ,p_action_type IN VARCHAR2
1350 ,p_data_source IN VARCHAR2
1351 ,p_func_parameters IN VARCHAR2
1352 ,p_user_function_name IN VARCHAR2
1353 ,p_new_interface_code IN VARCHAR2
1354 ,p_new_layout_code IN VARCHAR2) IS
1355
1356 CURSOR c_function_row (c_function_name IN VARCHAR2)
1357 IS
1358 SELECT WEB_ICON
1359 ,WEB_HOST_NAME
1360 ,WEB_AGENT_NAME
1361 ,WEB_HTML_CALL
1362 ,WEB_ENCRYPT_PARAMETERS
1363 ,WEB_SECURED
1364 ,OBJECT_ID
1365 ,REGION_APPLICATION_ID
1366 ,REGION_CODE
1367 ,FUNCTION_ID
1368 ,FUNCTION_NAME
1369 ,APPLICATION_ID
1370 ,FORM_ID
1371 ,PARAMETERS
1372 ,TYPE
1373 ,CREATION_DATE
1374 ,CREATED_BY
1375 ,LAST_UPDATE_DATE
1376 ,LAST_UPDATED_BY
1377 ,LAST_UPDATE_LOGIN
1378 ,MAINTENANCE_MODE_SUPPORT
1379 ,CONTEXT_DEPENDENCE
1380 ,JRAD_REF_PATH
1381 FROM FND_FORM_FUNCTIONS
1382 WHERE FUNCTION_NAME = c_function_name;
1383
1384 l_function_row c_function_row%ROWTYPE;
1385 l_rowid VARCHAR2(200);
1386 no_default_layout EXCEPTION;
1387 l_fun_id NUMBER;
1388 l_func_parameters VARCHAR2(1000);
1389 l_function_name VARCHAR2(30);
1390 l_blob BLOB;
1391 l_text VARCHAR2(32767);
1392 poXML CLOB;
1393 l_xml_tag_name VARCHAR2(150);
1394 l_xml_seq_num VARCHAR2(150);
1395 l_seg_xml_tag_name VARCHAR2(150);
1396 L_GROUP_NAME VARCHAR2(150);
1397 l_FLXDU_COLUMN_XML_DATA VARCHAR2(150);
1398 l_seg_column_xml_data VARCHAR2(150);
1399 l_proc_name VARCHAR2(72) := g_package||'Create_RIW_OAF_Function';
1400
1401 BEGIN
1402 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1403
1404 IF p_action_type = 'Update' THEN
1405 SELECT fff.function_name into l_function_name
1406 FROM fnd_form_functions fff, fnd_form_functions_tl ffft
1407 WHERE fff.FUNCTION_ID = ffft.FUNCTION_ID
1408 AND ffft.SOURCE_LANG = userenv('LANG')
1409 AND ffft.LANGUAGE = userenv('LANG')
1410 AND ffft.USER_FUNCTION_NAME = p_user_function_name;
1411
1412 OPEN c_function_row(c_function_name => l_function_name);
1413 FETCH c_function_row INTO l_function_row;
1414 IF c_function_row%NOTFOUND THEN
1415 RAISE no_default_layout;
1416 END IF;
1417 CLOSE c_function_row;
1418 ELSE
1419 OPEN c_function_row(c_function_name => p_base_function_name);
1420 FETCH c_function_row INTO l_function_row;
1421 IF c_function_row%NOTFOUND THEN
1422 RAISE no_default_layout;
1423 END IF;
1424 CLOSE c_function_row;
1425 END IF;
1426
1427
1428 l_func_parameters := replace(p_func_parameters,'$','&');
1429
1430 IF p_action_type = 'Update' THEN
1431
1432 l_func_parameters := l_func_parameters||'$pFunctionId='|| l_function_row.FUNCTION_ID;
1433 l_func_parameters := replace(l_func_parameters,'$','&');
1434
1435
1436 fnd_form_functions_pkg.UPDATE_ROW
1437 (X_FUNCTION_ID => l_function_row.FUNCTION_ID
1438 ,X_WEB_HOST_NAME => l_function_row.WEB_HOST_NAME
1439 ,X_WEB_AGENT_NAME => l_function_row.WEB_AGENT_NAME
1440 ,X_WEB_HTML_CALL => l_function_row.WEB_HTML_CALL --can be changed later
1441 ,X_WEB_ENCRYPT_PARAMETERS => l_function_row.WEB_ENCRYPT_PARAMETERS
1442 ,X_WEB_SECURED => l_function_row.WEB_SECURED
1443 ,X_WEB_ICON => l_function_row.WEB_ICON
1444 ,X_OBJECT_ID => l_function_row.OBJECT_ID
1445 ,X_REGION_APPLICATION_ID => l_function_row.REGION_APPLICATION_ID
1446 ,X_REGION_CODE => l_function_row.REGION_CODE
1447 ,X_FUNCTION_NAME => l_function_row.FUNCTION_NAME
1448 ,X_APPLICATION_ID => l_function_row.APPLICATION_ID
1449 ,X_FORM_ID => l_function_row.FORM_ID
1450 ,X_PARAMETERS => l_func_parameters --can be changed later
1451 ,X_TYPE => l_function_row.TYPE
1452 ,X_USER_FUNCTION_NAME => p_user_function_name --can be changed later
1453 ,X_DESCRIPTION => p_user_function_name --can be changed later
1454 ,X_LAST_UPDATE_DATE => sysdate
1455 ,X_LAST_UPDATED_BY => l_function_row.CREATED_BY
1456 ,X_LAST_UPDATE_LOGIN => 0);
1457
1458 --$ Update Metadata ( FLXDU_FUNC_INTEGRATOR_CODE in PQP_FLXDU_FUNC_ATTRIBUTES)
1459 update PQP_FLXDU_FUNC_ATTRIBUTES
1460 set FLXDU_FUNC_INTEGRATOR_CODE = p_new_interface_code
1461 where flxdu_func_attribute_id = l_function_row.FUNCTION_ID
1462 and flxdu_func_name = l_function_row.FUNCTION_NAME;
1463 ELSE
1464
1465 SELECT fnd_form_functions_s.nextval
1466 INTO l_fun_id
1467 FROM dual;
1468 l_func_parameters := l_func_parameters||'$pFunctionId='|| l_fun_id;
1469 l_func_parameters := replace(l_func_parameters,'$','&');
1470
1471 fnd_form_functions_pkg.INSERT_ROW
1472 (X_ROWID => l_rowid
1473 ,X_FUNCTION_ID => l_fun_id
1474 ,X_WEB_HOST_NAME => l_function_row.WEB_HOST_NAME
1475 ,X_WEB_AGENT_NAME => l_function_row.WEB_AGENT_NAME
1476 ,X_WEB_HTML_CALL => l_function_row.WEB_HTML_CALL--can be changed later
1477 ,X_WEB_ENCRYPT_PARAMETERS => l_function_row.WEB_ENCRYPT_PARAMETERS
1478 ,X_WEB_SECURED => l_function_row.WEB_SECURED
1479 ,X_WEB_ICON => l_function_row.WEB_ICON
1480 ,X_OBJECT_ID => l_function_row.OBJECT_ID
1481 ,X_REGION_APPLICATION_ID => l_function_row.REGION_APPLICATION_ID
1482 ,X_REGION_CODE => l_function_row.REGION_CODE
1483 ,X_FUNCTION_NAME => p_function_name
1484 ,X_APPLICATION_ID => p_application_id
1485 ,X_FORM_ID => l_function_row.FORM_ID
1486 ,X_PARAMETERS => l_func_parameters --can be changed later
1487 ,X_TYPE => l_function_row.TYPE
1488 ,X_USER_FUNCTION_NAME => p_user_function_name --can be changed later
1489 ,X_DESCRIPTION => p_user_function_name --can be changed later
1490 ,X_CREATION_DATE => Sysdate
1491 ,X_CREATED_BY => l_function_row.CREATED_BY
1492 ,X_LAST_UPDATE_DATE => sysdate
1493 ,X_LAST_UPDATED_BY => l_function_row.CREATED_BY
1494 ,X_LAST_UPDATE_LOGIN => 0
1495 ,X_MAINTENANCE_MODE_SUPPORT => l_function_row.MAINTENANCE_MODE_SUPPORT
1496 ,X_CONTEXT_DEPENDENCE => l_function_row.CONTEXT_DEPENDENCE
1497 ,X_JRAD_REF_PATH => l_function_row.JRAD_REF_PATH);
1498
1499 END IF;
1500
1501 IF p_data_source = 'XML' THEN
1502 poXML := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
1503 <DataList>
1504 <Data>';
1505 FOR csr_xml_tags_rec IN csr_get_xml_tags
1506 (c_interface_code => p_new_interface_code
1507 ,c_layout_code => p_new_layout_code)
1508 LOOP
1509 l_xml_tag_name := csr_xml_tags_rec.FLXDU_COLUMN_XML_TAG;
1510 l_xml_seq_num := csr_xml_tags_rec.FLXDU_SEQ_NUM;
1511 l_group_name := csr_xml_tags_rec.FLXDU_GROUP_NAME;
1512 l_flxdu_column_xml_data := csr_xml_tags_rec.flxdu_column_xml_data;
1513
1514 IF l_flxdu_column_xml_data IS NULL THEN
1515 l_flxdu_column_xml_data := '';
1516 END IF;
1517 --Looping for Flexi context and segments
1518 IF l_group_name is not null THEN
1519 poXML := poXML ||
1520 '<'||l_xml_tag_name||'>';
1521 FOR csr_get_xml_tags_rec IN csr_get_flex_xml_tags
1522 (c_flxdu_group_name => l_group_name)
1523 LOOP
1524 l_seg_xml_tag_name := csr_get_xml_tags_rec.FLXDU_COLUMN_XML_TAG;
1525 l_seg_column_xml_data := csr_get_xml_tags_rec.flxdu_column_xml_data;
1526 IF l_seg_column_xml_data IS NULL THEN
1527 l_seg_column_xml_data := '';
1528 END IF;
1529 poXML := poXML ||
1530 '<'||l_seg_xml_tag_name||'> '||l_seg_column_xml_data||' </'|| l_seg_xml_tag_name||'>';
1531 END LOOP;
1532 poXML := poXML ||
1533 '</'|| l_xml_tag_name||'>';
1534 ELSE
1535 poXML := poXML ||
1536 '<'||l_xml_tag_name||'> '||l_flxdu_column_xml_data||' </'|| l_xml_tag_name||'>';
1537 END IF;
1538 END LOOP;
1539
1540 poXML :=poXML || '</Data>
1541 </DataList>';
1542 END IF;
1543
1544 IF p_action_type = 'Update' THEN
1545
1546 update PQP_FLXDU_FUNC_ATTRIBUTES set flxdu_func_xml_data = poXML
1547 where flxdu_func_attribute_id = l_function_row.FUNCTION_ID
1548 and flxdu_func_name = l_function_row.FUNCTION_NAME;
1549 ELSE
1550 INSERT INTO PQP_FLXDU_FUNC_ATTRIBUTES
1551 (FLXDU_FUNC_ATTRIBUTE_ID
1552 ,FLXDU_FUNC_NAME
1553 ,FLXDU_FUNC_SOURCE_TYPE
1554 ,FLXDU_FUNC_INTEGRATOR_CODE
1555 ,FLXDU_FUNC_XML_DATA
1556 ,LEGISLATION_CODE
1557 ,DESCRIPTION
1558 ,CREATED_BY
1559 ,CREATION_DATE
1560 ,LAST_UPDATED_BY
1561 ,LAST_UPDATE_DATE
1562 ,LAST_UPDATE_LOGIN
1563 ,OBJECT_VERSION_NUMBER)
1564 VALUES
1565 (l_fun_id
1566 ,p_function_name
1567 ,p_data_source
1568 ,p_new_interface_code
1569 ,poXML
1570 ,'US'
1571 ,p_user_function_name
1572 ,l_function_row.CREATED_BY
1573 ,SYSDATE
1574 ,l_function_row.CREATED_BY
1575 ,SYSDATE
1576 ,l_function_row.CREATED_BY
1577 ,1);
1578 END IF;
1579
1580 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1581
1582 END Create_RIW_OAF_Function;
1583
1584
1585
1586
1587 -- =============================================================================
1588 -- ~ Create_RIW_Menu_Entries:
1589 -- =============================================================================
1590 PROCEDURE Create_RIW_Menu_Entries
1591 (p_application_id IN NUMBER
1592 ,p_menu_id IN NUMBER
1593 ,p_function_name IN VARCHAR2
1594 ,p_user_function_name IN VARCHAR2) IS
1595
1596
1597 l_rowid VARCHAR2(200);
1598 no_default_layout EXCEPTION;
1599 l_fun_id NUMBER;
1600 l_ENTRY_SEQUENCE NUMBER := 0;
1601 l_proc_name VARCHAR2(72) := g_package||'Create_RIW_Menu_Entries';
1602
1603 BEGIN
1604 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1605
1606 SELECT function_id
1607 INTO l_fun_id
1608 FROM fnd_form_functions
1609 WHERE function_name = p_function_name;
1610
1611 SELECT max(ENTRY_SEQUENCE)
1612 INTO l_ENTRY_SEQUENCE
1613 FROM fnd_menu_entries
1614 WHERE MENU_ID= p_menu_id ;
1615
1616 fnd_menu_entries_pkg.insert_row
1617 (X_ROWID => l_rowid
1618 ,X_MENU_ID => p_menu_id
1619 ,X_ENTRY_SEQUENCE => l_ENTRY_SEQUENCE+1
1620 ,X_SUB_MENU_ID => null
1621 ,X_FUNCTION_ID => l_fun_id
1622 ,X_GRANT_FLAG => 'Y'
1623 ,X_PROMPT => p_user_function_name --Can be change later
1624 ,X_DESCRIPTION => p_user_function_name --can be change later
1625 ,X_CREATION_DATE => sysdate
1626 ,X_CREATED_BY => 1
1627 ,X_LAST_UPDATE_DATE => sysdate
1628 ,X_LAST_UPDATED_BY => 1
1629 ,X_LAST_UPDATE_LOGIN => 0);
1630
1631 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1632 END Create_RIW_Menu_Entries;
1633
1634
1635
1636
1637
1638 -- =============================================================================
1639 -- ~ Delete_riw_integrator:
1640 -- =============================================================================
1641 PROCEDURE Delete_riw_integrator(p_LAYOUT_CODE IN VARCHAR2 default null
1642 ,p_MAPPING_CODE IN VARCHAR2 default null
1643 ,p_INTERFACE_CODE IN VARCHAR2
1644 ,p_application_id IN NUMBER ) IS
1645
1646 --$ get Integrator Code to delete entire integrator setup in one shot using
1647 -- Function bne_integrator_utils.DELETE_INTEGRATOR
1648 CURSOR csr_get_integrator_code is
1649 select integrator_code from bne_interfaces_b where interface_code
1650 = p_INTERFACE_CODE and application_id = p_application_id;
1651
1652 l_proc varchar2(72) := g_package||'Delete_riw_integrator';
1653 l_intg_code varchar2(30);
1654 l_param_list_code varchar2(30);
1655 cnt number;
1656
1657 BEGIN
1658
1659 --$ Use bne_integrator_utils.DELETE_INTEGRATOR to delete entire setup
1660 OPEN csr_get_integrator_code;
1661 fetch csr_get_integrator_code into l_intg_code;
1662 Close csr_get_integrator_code;
1663
1664 l_param_list_code := l_intg_code || '_DP';
1665 DELETE FROM BNE_PARAM_LISTS_TL WHERE PARAM_LIST_CODE = l_param_list_code and application_id = p_application_id;
1666 DELETE FROM BNE_PARAM_LISTS_B WHERE PARAM_LIST_CODE = l_param_list_code and application_id = p_application_id;
1667
1668 cnt := bne_integrator_utils.DELETE_INTEGRATOR
1669 ( P_INTEGRATOR_CODE => l_intg_code,
1670 P_APPLICATION_ID => p_application_id);
1671
1672 --$ COMMENT MANUAL DELETION STEPS
1673 /* DELETE
1674 FROM BNE_MAPPING_LINES
1675 WHERE APPLICATION_ID = P_APPLICATION_ID
1676 AND MAPPING_CODE = p_MAPPING_CODE
1677 AND INTERFACE_CODE =p_INTERFACE_CODE;
1678
1679 DELETE
1680 FROM BNE_MAPPINGS_tl
1681 WHERE APPLICATION_ID = P_APPLICATION_ID
1682 AND MAPPING_CODE = p_MAPPING_CODE;
1683
1684 DELETE
1685 FROM BNE_MAPPINGS_B
1686 WHERE APPLICATION_ID = P_APPLICATION_ID
1687 AND MAPPING_CODE = p_MAPPING_CODE;
1688
1689
1690 DELETE
1691 FROM BNE_LAYOUT_COLS
1692 WHERE APPLICATION_ID = P_APPLICATION_ID
1693 AND LAYOUT_CODE = p_LAYOUT_CODE;
1694
1695 DELETE
1696 FROM bne_layout_blocks_tl
1697 WHERE APPLICATION_ID = P_APPLICATION_ID
1698 AND LAYOUT_CODE = p_LAYOUT_CODE;
1699
1700 DELETE
1701 FROM bne_layout_blocks_b
1702 WHERE APPLICATION_ID = P_APPLICATION_ID
1703 AND LAYOUT_CODE = p_LAYOUT_CODE;
1704
1705 DELETE
1706 FROM bne_layouts_tl
1707 WHERE APPLICATION_ID = P_APPLICATION_ID
1708 AND LAYOUT_CODE = p_LAYOUT_CODE;
1709
1710 DELETE
1711 FROM bne_layouts_b
1712 WHERE APPLICATION_ID = P_APPLICATION_ID
1713 AND LAYOUT_CODE = p_LAYOUT_CODE;
1714
1715 DELETE
1716 FROM BNE_INTERFACE_cols_tl
1717 WHERE APPLICATION_ID = P_APPLICATION_ID
1718 AND INTERFACE_CODE = p_INTERFACE_CODE;
1719
1720 DELETE
1721 FROM BNE_INTERFACE_cols_b
1722 WHERE APPLICATION_ID = P_APPLICATION_ID
1723 AND INTERFACE_CODE = p_INTERFACE_CODE;
1724
1725 DELETE
1726 FROM BNE_INTERFACES_tl
1727 WHERE APPLICATION_ID = P_APPLICATION_ID
1728 AND INTERFACE_CODE = p_INTERFACE_CODE;
1729
1730 DELETE
1731 FROM BNE_INTERFACES_B
1732 WHERE APPLICATION_ID = P_APPLICATION_ID
1733 AND INTERFACE_CODE = p_INTERFACE_CODE; */
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 hr_utility.set_location('ERROR occured',30);
1737 Null;
1738 END Delete_riw_integrator;
1739
1740
1741 -- =============================================================================
1742 -- ~ Create_RIW_Integrator_Row:
1743 -- =============================================================================
1744 PROCEDURE Create_RIW_Integrator_Row
1745 (p_application_id IN NUMBER
1746 ,p_new_intg_code IN VARCHAR2
1747 ,p_base_intg_code IN VARCHAR2
1748 ,p_integrator_name IN VARCHAR2) IS
1749
1750
1751 CURSOR c_intg_row
1752 IS
1753 SELECT APPLICATION_ID
1754 ,INTEGRATOR_CODE
1755 ,OBJECT_VERSION_NUMBER
1756 ,ENABLED_FLAG
1757 ,UPLOAD_PARAM_LIST_APP_ID
1758 ,UPLOAD_PARAM_LIST_CODE
1759 ,UPLOAD_SERV_PARAM_LIST_APP_ID
1760 ,UPLOAD_SERV_PARAM_LIST_CODE
1761 ,IMPORT_PARAM_LIST_APP_ID
1762 ,IMPORT_PARAM_LIST_CODE
1763 ,UPLOADER_CLASS
1764 ,DATE_FORMAT
1765 ,IMPORT_TYPE
1766 ,CREATED_BY
1767 ,CREATION_DATE
1768 ,LAST_UPDATED_BY
1769 ,LAST_UPDATE_LOGIN
1770 ,LAST_UPDATE_DATE
1771 ,CREATE_DOC_LIST_APP_ID
1772 ,CREATE_DOC_LIST_CODE
1773 ,NEW_SESSION_FLAG
1774 FROM BNE_INTEGRATORS_B
1775 WHERE application_id = p_application_id
1776 AND INTEGRATOR_CODE = p_base_intg_code;
1777
1778 l_intg_row c_intg_row%ROWTYPE;
1779 l_rowid VARCHAR2(200);
1780 no_default_layout EXCEPTION;
1781 VV_INTEGRATOR_CODE BNE_INTEGRATORS_B.INTEGRATOR_CODE%TYPE;
1782 l_proc_name VARCHAR2(72) := g_package||'Create_RIW_Integrator_Row';
1783
1784 BEGIN
1785 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1786 OPEN c_intg_row;
1787 FETCH c_intg_row INTO l_intg_row;
1788 IF c_intg_row%NOTFOUND THEN
1789 RAISE no_default_layout;
1790 END IF;
1791 CLOSE c_intg_row;
1792
1793 BEGIN
1794 SELECT INTEGRATOR_CODE
1795 INTO VV_INTEGRATOR_CODE
1796 FROM BNE_INTEGRATORS_B
1797 WHERE APPLICATION_ID = P_APPLICATION_ID
1798 AND INTEGRATOR_CODE = p_new_intg_code;
1799 EXCEPTION
1800 WHEN NO_DATA_FOUND THEN NULL;
1801 END;
1802
1803 -- If the Integrator does not exist then
1804
1805 IF ( VV_INTEGRATOR_CODE IS NULL) THEN
1806
1807 INSERT INTO BNE_INTEGRATORS_B
1808 (APPLICATION_ID
1809 ,INTEGRATOR_CODE
1810 ,OBJECT_VERSION_NUMBER
1811 ,ENABLED_FLAG
1812 ,UPLOAD_PARAM_LIST_APP_ID
1813 ,UPLOAD_PARAM_LIST_CODE
1814 ,UPLOAD_SERV_PARAM_LIST_APP_ID
1815 ,UPLOAD_SERV_PARAM_LIST_CODE
1816 ,IMPORT_PARAM_LIST_APP_ID
1817 ,IMPORT_PARAM_LIST_CODE
1818 ,UPLOADER_CLASS
1819 ,DATE_FORMAT
1820 ,IMPORT_TYPE
1821 ,CREATED_BY
1822 ,CREATION_DATE
1823 ,LAST_UPDATED_BY
1824 ,LAST_UPDATE_LOGIN
1825 ,LAST_UPDATE_DATE
1826 ,CREATE_DOC_LIST_APP_ID
1827 ,CREATE_DOC_LIST_CODE
1828 ,NEW_SESSION_FLAG )
1829 VALUES
1830 (l_intg_row.APPLICATION_ID
1831 ,p_new_intg_code
1832 ,1
1833 ,l_intg_row.ENABLED_FLAG
1834 ,l_intg_row.UPLOAD_PARAM_LIST_APP_ID
1835 ,l_intg_row.UPLOAD_PARAM_LIST_CODE
1836 ,l_intg_row.UPLOAD_SERV_PARAM_LIST_APP_ID
1837 ,l_intg_row.UPLOAD_SERV_PARAM_LIST_CODE
1838 ,l_intg_row.IMPORT_PARAM_LIST_APP_ID
1839 ,l_intg_row.IMPORT_PARAM_LIST_CODE
1840 ,l_intg_row.UPLOADER_CLASS
1841 ,l_intg_row.DATE_FORMAT
1842 ,l_intg_row.IMPORT_TYPE
1843 ,l_intg_row.CREATED_BY
1844 ,SYSDATE
1845 ,l_intg_row.LAST_UPDATED_BY
1846 ,l_intg_row.LAST_UPDATE_LOGIN
1847 ,SYSDATE
1848 ,l_intg_row.CREATE_DOC_LIST_APP_ID
1849 ,l_intg_row.CREATE_DOC_LIST_CODE
1850 ,l_intg_row.NEW_SESSION_FLAG
1851 );
1852
1853 INSERT INTO BNE_INTEGRATORS_TL
1854 (APPLICATION_ID
1855 ,INTEGRATOR_CODE
1856 ,LANGUAGE
1857 ,SOURCE_LANG
1858 ,USER_NAME
1859 ,UPLOAD_HEADER
1860 ,UPLOAD_TITLE_BAR
1861 ,CREATED_BY
1862 ,CREATION_DATE
1863 ,LAST_UPDATED_BY
1864 ,LAST_UPDATE_DATE)
1865 VALUES
1866 (l_intg_row.APPLICATION_ID
1867 ,p_new_intg_code
1868 ,userenv('LANG')
1869 ,userenv('LANG')
1870 ,p_integrator_name
1871 ,'Upload Parameters'
1872 ,'Upload Parameters'
1873 ,l_intg_row.CREATED_BY
1874 ,SYSDATE
1875 ,l_intg_row.LAST_UPDATED_BY
1876 ,SYSDATE);
1877
1878
1879
1880 BNE_SECURITY_UTILS_PKG.ADD_OBJECT_RULES (
1881 P_APPLICATION_ID =>l_intg_row.APPLICATION_ID,
1882 P_OBJECT_CODE =>p_new_intg_code,
1883 P_OBJECT_TYPE =>'INTEGRATOR',
1884 P_SECURITY_CODE =>p_new_intg_code,
1885 P_SECURITY_TYPE =>'FUNCTION',
1886 P_SECURITY_VALUE =>'PQP_FLEXIBLE_WEBADI_CREATE_DOC',
1887 P_USER_ID =>1);
1888 END IF;
1889 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1890
1891 END Create_RIW_Integrator_Row;
1892
1893
1894
1895
1896 -- =============================================================================
1897 -- ~ Create_RIW_Content_Row:
1898 -- =============================================================================
1899 PROCEDURE Create_RIW_Content_Row
1900 (p_application_id IN NUMBER
1901 ,p_new_content_code IN VARCHAR2
1902 ,p_base_content_code IN VARCHAR2
1903 ,p_intg_code IN VARCHAR2
1904 ,p_content_name IN VARCHAR2
1905 ,p_entity_name IN VARCHAR2 DEFAULT NULL
1906 ,p_content_out OUT NOCOPY VARCHAR2
1907 --$ Data Pump Correct Errors Content Code
1908 ,p_ce_content_out OUT NOCOPY VARCHAR2) IS
1909
1910
1911 CURSOR c_content_row(c_base_content_code in VARCHAR2) --$ based upon content code
1912 -- properties will be fetched as we have to create
1913 -- two contents -> hr/xml/csv and other is for
1914 -- correct errors for Data Pump
1915 IS
1916 SELECT APPLICATION_ID
1917 ,CONTENT_CODE
1918 ,OBJECT_VERSION_NUMBER
1919 ,INTEGRATOR_APP_ID
1920 ,INTEGRATOR_CODE
1921 ,PARAM_LIST_APP_ID
1922 ,PARAM_LIST_CODE
1923 ,CONTENT_CLASS
1924 ,LAST_UPDATE_DATE
1925 ,LAST_UPDATED_BY
1926 ,CREATION_DATE
1927 ,CREATED_BY
1928 ,LAST_UPDATE_LOGIN
1929 FROM bne_contents_b
1930 WHERE application_id = p_application_id
1931 AND CONTENT_CODE = c_base_content_code;
1932
1933 l_content_row c_content_row%ROWTYPE;
1934 l_rowid VARCHAR2(200);
1935 no_default_layout EXCEPTION;
1936 VV_INTEGRATOR_CODE BNE_INTEGRATORS_B.INTEGRATOR_CODE%TYPE;
1937 l_content_out VARCHAR2(50);
1938 --$ For Data Pump Correct Errors
1939 l_ce_content_out VARCHAR2(50);
1940
1941 l_temp_val VARCHAR2(4000);
1942 l_proc_name VARCHAR2(72) := g_package||'Create_RIW_Content_Row';
1943
1944 BEGIN
1945 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1946
1947 OPEN c_content_row(c_base_content_code => p_base_content_code); --$ create non data pump content
1948 FETCH c_content_row INTO l_content_row;
1949 IF c_content_row%NOTFOUND THEN
1950 RAISE no_default_layout;
1951 END IF;
1952 CLOSE c_content_row;
1953
1954 --Creating content for HR
1955 l_temp_val := 'BatchId,';
1956 --$ In case of XML use EffectiveDate as Content Column name to map it to the
1957 -- tag in "Sample Download XML" which is stored in PQP_FLXDU_COLUMNS table
1958 IF (p_base_content_code = 'PQP_FLEXI_WEBADI_XML_CNT') then
1959 l_temp_val := l_temp_val||'EffectiveDate,';
1960 ELSE
1961 l_temp_val := l_temp_val||'DateOfHire,';
1962 END IF;
1963 l_temp_val := l_temp_val||'LastName,Sex,PerComments,DateEmpDataVerified,DateOfBirth,EmailAddr,EmpNumber,';
1964
1965 l_temp_val := l_temp_val ||' ExpenseCheckSendToAddr,FirstName,PreferredName,MaritalStatus,MiddleName,Nationality,NationalIdentifier,PreviousLastName,';
1966 l_temp_val := l_temp_val ||'RegisteredDisabledFlag,Prefix,WorkTelephoneNumber,PplAttrCategory,PplAttr1,PplAttr2,PplAttr3,PplAttr4,PplAttr5,PplAttr6,';
1967 l_temp_val := l_temp_val ||'PplAttr7,PplAttr8,PplAttr9,PplAttr10,PplAttr11,PplAttr12,PplAttr13,PplAttr14,PplAttr15,PplAttr16,PplAttr17,PplAttr18,';
1968 l_temp_val := l_temp_val ||'PplAttr19,PplAttr20,PplAttr21,PplAttr22,PplAttr23,PplAttr24,PplAttr25,PplAttr26,PplAttr27,PplAttr28,PplAttr29,PplAttr30,';
1969 l_temp_val := l_temp_val ||'PerInfCategory,PerInf1,PerInf2,PerInf3,PerInf4,PerInf5,PerInf6,PerInf7,PerInf8,PerInf9,PerInf10,PerInf11,PerInf12,PerInf13,';
1970 l_temp_val := l_temp_val ||'PerInf14,PerInf15,PerInf16,PerInf17,PerInf18,PerInf19,PerInf20,PerInf21,PerInf22,PerInf23,PerInf24,PerInf25,PerInf26,PerInf27,';
1971 l_temp_val := l_temp_val ||'PerInf28,PerInf29,PerInf30,DateOfDeath,BackgroundCheckStatus,BackgroundCheckDate,BloodType,FastPathEmp,FTECapacity,Honours,';
1972 l_temp_val := l_temp_val ||'InternalLocation,LastMedicalTestBy,LastMedicalTestDate,MailStop,OfficeNumber,MilitaryService,Title,RehireRecommandation,';
1973 l_temp_val := l_temp_val ||'ProjectedStartDate,ResumeExists,ResumeLastUpdated,SecondPassportExists,StudentStatus,WorkSchedule,Suffix,';
1974 l_temp_val := l_temp_val ||'DeathCertificateRcptDate,CoordBenMedicalPlanNumber,CoordBenNoCVGFlag,CoordBenMedicalExtensionER,CoordBenMedicalPLName,';
1975 l_temp_val := l_temp_val ||'CoordBenInsuranceCRName,CoordBenInsuranceCRRIdentity,CoordBenInsuranceCVGStartDate,CoordBenInsuranceCVGEndDate,';
1976 l_temp_val := l_temp_val ||'TobaccoUsageFlag,DependentAdoptionDate,DependentVoluntaryServiceFlag,OriginalDateOfHire,AdjustedServiceDate,';
1977 l_temp_val := l_temp_val ||'TownOfBirth,RegionOfBirth,CountryOfBirth,GlobalPerId,UserPerType,VendorId,CorrespondenceLanguage,BenefitGroupId,';
1978 l_temp_val := l_temp_val ||'StudentNumber,PartyId,PrimaryAddrOverrideFlag,PrimaryAddrFlag,AddrStyle,AddrInfo1,AddrInfo2,';
1979 l_temp_val := l_temp_val ||'AddrInfo3,AddrInfo4,AddrInfo5,AddrInfo6,AddrInfo7,AddrInfo8,';
1980 l_temp_val := l_temp_val ||'AddrInfo9,AddrInfo10,AddrInfo11,AddrInfo12,AddrInfo13,AddrInfo14,AddrInfo15,AddrInfo16,';
1981 l_temp_val := l_temp_val ||'AddrInfo17,AddrInfo18,AddrInfo19,AddrInfo20,AddrType,AddrDateFrom,AddrDateTo,';
1982 l_temp_val := l_temp_val ||'AddrAttrCategory,AddrAttr1,AddrAttr2,AddrAttr3,AddrAttr4,AddrAttr5,AddrAttr6,AddrAttr7,AddrAttr8,AddrAttr9,AddrAttr10,';
1983 l_temp_val := l_temp_val ||'AddrAttr11,AddrAttr12,AddrAttr13,AddrAttr14,AddrAttr15,AddrAttr16,AddrAttr17,AddrAttr18,AddrAttr19,AddrAttr20,AddrComments,';
1984 l_temp_val := l_temp_val ||'AssgNumber,ChangeReason,AssgComments,ProbationEndDate,Frequency,InternalAddrLine,ManagerFlag,NormalHours,PerfReviewPeriod,';
1985 l_temp_val := l_temp_val ||'PerfReviewPeriodFrequency,ProbationPeriod,';
1986 l_temp_val := l_temp_val ||'ProbationUnit,SalaryReviewPeriod,SalaryReviewPeriodFrequency,SourceType,TimeNormalFinish,';
1987 l_temp_val := l_temp_val ||'TimeNormalStart,BargainingUnitCode,LabourUnionMemberFlag,HourlySalariedCode,AssgAttrCategory,AssgAttr1,AssgAttr2,';
1988 l_temp_val := l_temp_val ||'AssgAttr3,AssgAttr4,AssgAttr5,AssgAttr6,AssgAttr7,AssgAttr8,AssgAttr9,AssgAttr10,AssgAttr11,AssgAttr12,AssgAttr13,';
1989 l_temp_val := l_temp_val ||'AssgAttr14,AssgAttr15,AssgAttr16,AssgAttr17,AssgAttr18,AssgAttr19,AssgAttr20,AssgAttr21,AssgAttr22,AssgAttr23,';
1990 l_temp_val := l_temp_val ||'AssgAttr24,AssgAttr25,AssgAttr26,AssgAttr27,AssgAttr28,AssgAttr29,AssgAttr30,PplSeg1,PplSeg2,PplSeg3,PplSeg4,';
1991 l_temp_val := l_temp_val ||'PplSeg5,PplSeg6,PplSeg7,PplSeg8,PplSeg9,PplSeg10,PplSeg11,PplSeg12,PplSeg13,PplSeg14,PplSeg15,PplSeg16,PplSeg17,';
1992 l_temp_val := l_temp_val ||'PplSeg18,PplSeg19,PplSeg20,PplSeg21,PplSeg22,PplSeg23,PplSeg24,PplSeg25,PplSeg26,PplSeg27,PplSeg28,PplSeg29,';
1993 l_temp_val := l_temp_val ||'PplSeg30,Grade,Position,Job,Payroll,Location,Organization,SalaryBasis,Loc,ContactType,PrimaryContact,';
1994 l_temp_val := l_temp_val ||'PersnlRelationship,ContactName,DataPumpBatchLineId,PersnlAddrInfo,AddtnlAddrDtls,FurtherPerInfo,AddtnlPersnlDtls,';
1995 l_temp_val := l_temp_val ||'AddtnlAssgDtls,Status,AssgCategory,CollectiveAgreement,EmployeeCategory,';
1996
1997 -- For XML the content column GRE should be same as XML Tag used in
1998 -- PQP_FLXDU_COLUMNS
1999 IF (p_base_content_code = 'PQP_FLEXI_WEBADI_XML_CNT') then
2000 l_temp_val := l_temp_val || 'SoftKeySegment1,';
2001 else
2002 l_temp_val := l_temp_val || 'GRE,';
2003 end if;
2004
2005 l_temp_val := l_temp_val || 'SupervisorName,DefaultCodeCombinationId,SetOfBooksId,';
2006 l_temp_val := l_temp_val ||'ApplNum,ApplAssgNum,CntgntWrkNum';
2007 --$Content for Soft Coded KFF columns
2008 -- For XML the content columns should be same as XML Tags used in
2009 -- PQP_FLXDU_COLUMNS
2010 IF (p_base_content_code = 'PQP_FLEXI_WEBADI_XML_CNT') then
2011 l_temp_val := l_temp_val || ',SoftKeySegment2,SoftKeySegment3,SoftKeySegment4,SoftKeySegment5,SoftKeySegment6,SoftKeySegment7,SoftKeySegment8,';
2012 l_temp_val := l_temp_val || 'SoftKeySegment9,SoftKeySegment10,SoftKeySegment11,SoftKeySegment12,SoftKeySegment13,SoftKeySegment14,SoftKeySegment15,SoftKeySegment16,';
2013 l_temp_val := l_temp_val || 'SoftKeySegment17,SoftKeySegment18,SoftKeySegment19,SoftKeySegment20,SoftKeySegment21,SoftKeySegment22,SoftKeySegment23,SoftKeySegment24,';
2014 l_temp_val := l_temp_val || 'SoftKeySegment25,SoftKeySegment26,SoftKeySegment27,SoftKeySegment28,SoftKeySegment29,SoftKeySegment30';
2015 else
2016 l_temp_val := l_temp_val ||',sclSeg2,sclSeg3,sclSeg4,'; -- sclSeg1 or GRE is already included
2017 l_temp_val := l_temp_val ||'sclSeg5,sclSeg6,sclSeg7,sclSeg8,sclSeg9,sclSeg10,sclSeg11,sclSeg12,sclSeg13,sclSeg14,sclSeg15,sclSeg16,sclSeg17,';
2018 l_temp_val := l_temp_val ||'sclSeg18,sclSeg19,sclSeg20,sclSeg21,sclSeg22,sclSeg23,sclSeg24,sclSeg25,sclSeg26,sclSeg27,sclSeg28,sclSeg29,sclSeg30';
2019 end if;
2020 -- New column added to download Assignment effective start date and DupPerson
2021 l_temp_val := l_temp_val || ',AssgEfftDateFrom,DupPerson';
2022
2023 BNE_CONTENT_UTILS.CREATE_CONTENT_DYNAMIC_SQL
2024 (P_APPLICATION_ID =>l_content_row.APPLICATION_ID
2025 ,P_OBJECT_CODE =>p_new_content_code
2026 ,P_INTEGRATOR_CODE =>p_intg_code
2027 ,P_CONTENT_DESC =>p_content_name
2028 ,P_CONTENT_CLASS =>l_content_row.CONTENT_CLASS
2029 ,P_COL_LIST =>l_temp_val
2030 ,P_LANGUAGE =>userenv('LANG')
2031 ,P_SOURCE_LANGUAGE =>userenv('LANG')
2032 ,P_USER_ID =>1
2033 ,P_CONTENT_CODE =>l_content_out);
2034
2035 update bne_contents_b
2036 set param_list_code = l_content_row.param_list_code
2037 ,param_list_app_id = l_content_row.param_list_app_id
2038 where content_code =l_content_out;
2039
2040 --$ If XML, CSV or HR Content then 'Effective Date' and 'Assignment Effective Start
2041 -- Date' column should be uploadable and read only.
2042 if (p_base_content_code = 'PQP_FLEXI_WEBADI_XML_CNT' OR
2043 p_base_content_code = 'PQP_FLEXI_WEBADI_CSV_CNT' OR
2044 p_base_content_code = 'PQP_FLEXI_WEBADI_HR_CNT') then
2045 update bne_content_cols_b set read_only_flag = 'Y' where content_code =
2046 l_content_out and sequence_num in (2,316,317);
2047 end if;
2048
2049 p_content_out := l_content_out;
2050
2051 --$ Add Code to create Content for Correct Errors Spreadsheet for Data Pump
2052 OPEN c_content_row(c_base_content_code => 'PQP_FLEXI_WEBADI_CE_CNT'); --$ create
2053 -- data pump content
2054 FETCH c_content_row INTO l_content_row;
2055 IF c_content_row%NOTFOUND THEN
2056 RAISE no_default_layout;
2057 END IF;
2058 CLOSE c_content_row;
2059
2060 l_temp_val := 'bid,h_dt,l_name,sex,p_com,dt_emdt_vrfd,dob,email,empno,xpns_snd_add,fname,pname,p_mar_stts,mname,ntnlty,';
2061 l_temp_val := l_temp_val||
2062 'ni,pre_lname,rgst_disbl_flg,prfx,wrktel,atr_cat,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,';
2063 l_temp_val := l_temp_val||
2064 'a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,prinf_cat,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,';
2065 l_temp_val := l_temp_val||
2066 'p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,dof_death,bkgrnd_ck_stts,bkgrnd_dt_ck,bld_type,fst_pth_emp,fte_cap,';
2067 l_temp_val := l_temp_val||
2068 'hnrs,int_loc,lst_med_tst_by,lst_med_tst_dt,mlstop,offno,on_mil_ser,title,rehire_reco,prj_st_dt,res_xst,res_lst_upd,';
2069 l_temp_val := l_temp_val||
2070 'scnd_psp_ex,std_stts,wrk_schd,sffx,rcpt_dth_cert_dt,co_ben_med_pln_no,co_ben_no_cvg_flg,co_ben_med_ext_er,';
2071 l_temp_val := l_temp_val||
2072 'co_ben_med_pl_nm,co_ben_med_insr_crr_nm,co_ben_med_insr_crr_idnt,co_ben_med_cvg_st_dt,co_ben_med_cvg_end_dt,';
2073 l_temp_val := l_temp_val||
2074 'us_tobacc_flg,dpdnt_adopt_dt,dpdnt_vlntry_svc_flg,org_dt_hire,adj_svc_dt,to_birth,ro_birth,co_birth,glb_perid,';
2075 l_temp_val := l_temp_val||
2076 'uper_type,ven_nm,cors_lang,ben_grp,stno,perid,pradd_ovlp_ovrride,pr_flg,style,adlin1,adlin2,adlin3,town,';
2077 l_temp_val := l_temp_val||
2078 'reg1,reg2,reg3,postcode,cntry,tno1,tno2,tno3,d13,d14,d15,d16,d17,d18,d19,d20,ad_type,dt_from,dt_to,ad_at_cat,';
2079 l_temp_val := l_temp_val||
2080 'b1,b2,b3,b4,b5,b6,b7,b8,b9,b10,b11,b12,b13,b14,b15,b16,b17,b18,b19,b20,a_com,assignno,chng_rsn,as_com,dt_prob_end,';
2081 l_temp_val := l_temp_val||
2082 'freq,int_ad_line,mg_flg,nrml_hrs,perf_rev_prd,perf_rev_frq,prob_prd,prob_unit,sal_rev_prd,sal_rev_frq,src_type,';
2083 l_temp_val := l_temp_val||
2084 'time_nrml_fin,time_nrml_strt,bargunit_code,lbr_un_memb_flg,hrly_sal_code,asatr_cat,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,';
2085 l_temp_val := l_temp_val||
2086 's12,s13,s14,s15,s16,s17,s18,s19,s20,s21,s22,s23,s24,s25,s26,s27,s28,s29,s30,k1,k2,k3,k4,k5,k6,k7,k8,k9,k10,k11,k12,';
2087 l_temp_val := l_temp_val||
2088 'k13,k14,k15,k16,k17,k18,k19,k20,k21,k22,k23,k24,k25,k26,k27,k28,k29,k30,grdname,posname,jbname,payname,loccode,';
2089 l_temp_val := l_temp_val||
2090 'orgname,paybasis,loc,cnt_type,prm_cnt_flg,per_flg,cnt_name,';
2091
2092 l_temp_val := l_temp_val||'rid,'; -- row id or link value
2093
2094 l_temp_val := l_temp_val||
2095 'concat_hr_add,cnct_hr_add2,cnct_per_info_df,cnct_per_attr_df,cnct_per_ass_df,Status,AssgCategory,CollectiveAgreement,EmployeeCategory,GRE,SupervisorName,';
2096 l_temp_val := l_temp_val||
2097 'DefaultCodeCombinationId,SetOfBooksId,ApplNum,ApplAssgNum,CntgntWrkNum,sclSeg2,sclSeg3,sclSeg4,sclSeg5,sclSeg6,';
2098 l_temp_val := l_temp_val||
2099 'sclSeg7,sclSeg8,sclSeg9,sclSeg10,sclSeg11,sclSeg12,sclSeg13,sclSeg14,sclSeg15,sclSeg16,sclSeg17,sclSeg18,sclSeg19,';
2100 l_temp_val := l_temp_val||
2101 'sclSeg20,sclSeg21,sclSeg22,sclSeg23,sclSeg24,sclSeg25,sclSeg26,sclSeg27,sclSeg28,sclSeg29,sclSeg30,';
2102 l_temp_val := l_temp_val||'exp'; -- for batch exception
2103
2104 -- New column added to download Assignment effective start date and DupPerson
2105 l_temp_val := l_temp_val || ',AssgEfftDateFrom,DupPerson';
2106
2107 BNE_CONTENT_UTILS.CREATE_CONTENT_DYNAMIC_SQL
2108 (P_APPLICATION_ID => 8303 --l_content_row.APPLICATION_ID
2109 ,P_OBJECT_CODE => p_new_content_code||'_CE'
2110 ,P_INTEGRATOR_CODE => p_intg_code
2111 ,P_CONTENT_DESC => p_content_name||'_CE'
2112 ,P_CONTENT_CLASS => l_content_row.CONTENT_CLASS
2113 ,P_COL_LIST => l_temp_val
2114 ,P_LANGUAGE =>userenv('LANG')
2115 ,P_SOURCE_LANGUAGE =>userenv('LANG')
2116 ,P_USER_ID =>1
2117 ,P_CONTENT_CODE =>l_ce_content_out);
2118
2119 update bne_contents_b
2120 set param_list_code = l_content_row.param_list_code
2121 ,param_list_app_id = l_content_row.param_list_app_id
2122 where content_code = l_ce_content_out;
2123
2124 p_ce_content_out := l_ce_content_out;
2125
2126 -- $ to make "Effective Date",'Assignment Effective Start Date' and "Batch Link"
2127 -- Read Only but Uploadable field at the same time
2128 -- set Read Only flag to 'Y' in Content instead of Layout
2129 update bne_content_cols_b set read_only_flag = 'Y' where content_code in
2130 (l_ce_content_out) and sequence_num in (2,317,318);
2131 update bne_content_cols_b set read_only_flag = 'Y' where content_code in
2132 (l_ce_content_out) and sequence_num = 270;
2133
2134
2135 --------
2136 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
2137
2138 END Create_RIW_Content_Row;
2139
2140 -- =============================================================================
2141 -- ~ Create_RIW_BLNK_CONTENT_ROW
2142 -- =============================================================================
2143 PROCEDURE Create_RIW_BLNK_Content_row
2144 (p_application_id IN NUMBER
2145 ,p_new_blnk_content_code IN VARCHAR2
2146 ,p_intg_code IN VARCHAR2
2147 ,p_content_name IN VARCHAR2
2148 ,p_blnk_content_out OUT NOCOPY VARCHAR2) IS
2149
2150 l_blnk_content_out VARCHAR2(50);
2151 BEGIN
2152 BNE_CONTENT_UTILS.CREATE_CONTENT_DYNAMIC_SQL
2153 (P_APPLICATION_ID =>p_application_id
2154 ,P_OBJECT_CODE =>p_new_blnk_content_code
2155 ,P_INTEGRATOR_CODE =>p_intg_code
2156 ,P_CONTENT_DESC =>p_new_blnk_content_code
2157 ,P_CONTENT_CLASS =>null
2158 ,P_COL_LIST =>null
2159 ,P_LANGUAGE =>userenv('LANG')
2160 ,P_SOURCE_LANGUAGE =>userenv('LANG')
2161 ,P_USER_ID =>1
2162 ,P_CONTENT_CODE =>l_blnk_content_out);
2163
2164 p_blnk_content_out := l_blnk_content_out;
2165
2166 END Create_RIW_BLNK_Content_row;
2167
2168 -- =============================================================================
2169 -- ~ Create_Derived_Param_List:
2170 -- =============================================================================
2171 PROCEDURE Create_Derived_Param_List
2172 (p_application_id IN NUMBER
2173 ,p_param_list_code IN VARCHAR2
2174 ,p_parameter_value IN VARCHAR2) IS
2175
2176 l_sequence_num Number;
2177 l_param_list_code VARCHAR2(50);
2178 l_pl_sql varchar2(4000);
2179 BEGIN
2180 l_param_list_code := BNE_PARAMETER_UTILS.CREATE_PARAM_LIST_ALL(P_APPLICATION_ID => p_application_id
2181 ,P_PARAM_LIST_CODE => p_param_list_code
2182 ,P_PERSISTENT => 'Y'
2183 ,P_COMMENTS => ''
2184 ,P_ATTRIBUTE_APP_ID => ''
2185 ,P_ATTRIBUTE_CODE => ''
2186 ,P_LIST_RESOLVER => ''
2187 ,P_PROMPT_LEFT => ''
2188 ,P_PROMPT_ABOVE => ''
2189 ,P_USER_NAME => p_parameter_value
2190 ,P_USER_TIP => '');
2191
2192 end Create_Derived_Param_List;
2193
2194
2195 PROCEDURE Create_RIW_Content_Row_Others
2196 (p_application_id IN NUMBER
2197 ,p_new_content_code IN VARCHAR2
2198 ,p_base_content_code IN VARCHAR2
2199 ,p_intg_code IN VARCHAR2
2200 ,p_content_name IN VARCHAR2
2201 ,p_entity_name IN VARCHAR2 DEFAULT NULL
2202 ,p_content_out OUT NOCOPY VARCHAR2) IS
2203
2204
2205 CURSOR c_content_row(c_base_content_code in VARCHAR2) --$ based upon content code
2206 -- properties will be fetched as we have to create
2207 -- two contents -> hr/xml/csv and other is for
2208 -- correct errors for Data Pump
2209 IS
2210 SELECT APPLICATION_ID
2211 ,CONTENT_CODE
2212 ,OBJECT_VERSION_NUMBER
2213 ,INTEGRATOR_APP_ID
2214 ,INTEGRATOR_CODE
2215 ,PARAM_LIST_APP_ID
2216 ,PARAM_LIST_CODE
2217 ,CONTENT_CLASS
2218 ,LAST_UPDATE_DATE
2219 ,LAST_UPDATED_BY
2220 ,CREATION_DATE
2221 ,CREATED_BY
2222 ,LAST_UPDATE_LOGIN
2223 FROM bne_contents_b
2224 WHERE application_id = p_application_id
2225 AND CONTENT_CODE = c_base_content_code;
2226
2227 CURSOR c_content_cols_row(c_base_content_code IN VARCHAR2)
2228 IS
2229 SELECT col_name, read_only_flag, sequence_num
2230 FROM bne_content_cols_b
2231 WHERE application_id = p_application_id
2232 AND content_code = c_base_content_code
2233 ORDER BY sequence_num;
2234
2235 l_content_row c_content_row%ROWTYPE;
2236 l_rowid VARCHAR2(200);
2237 no_default_layout EXCEPTION;
2238 VV_INTEGRATOR_CODE BNE_INTEGRATORS_B.INTEGRATOR_CODE%TYPE;
2239 l_content_out VARCHAR2(50);
2240 l_first boolean := true;
2241 l_column bne_content_cols_b.col_name%TYPE;
2242 --$ For Data Pump Correct Errors
2243 l_ce_content_out VARCHAR2(50);
2244
2245 l_temp_val VARCHAR2(4000);
2246 l_proc_name VARCHAR2(72) := g_package||'Create_RIW_Content_Row';
2247 l_read_only_flag varchar2(3);
2248 l_sequence_num number(5);
2249
2250 BEGIN
2251 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2252
2253 OPEN c_content_row(c_base_content_code => p_base_content_code); --$ create non data pump content
2254 FETCH c_content_row INTO l_content_row;
2255 IF c_content_row%NOTFOUND THEN
2256 RAISE no_default_layout;
2257 END IF;
2258 CLOSE c_content_row;
2259
2260 OPEN c_content_cols_row(p_base_content_code);
2261 LOOP
2262 FETCH c_content_cols_row INTO l_column, l_read_only_flag, l_sequence_num;
2263 EXIT WHEN c_content_cols_row%NOTFOUND;
2264
2265 IF l_first then
2266 l_temp_val := l_temp_val || l_column;
2267 ELSE
2268 l_temp_val := l_temp_val || ',' || l_column;
2269 END IF;
2270 l_first := false;
2271 END LOOP;
2272 CLOSE c_content_cols_row;
2273
2274 hr_utility.set_location(l_temp_val, 44);
2275 BNE_CONTENT_UTILS.CREATE_CONTENT_DYNAMIC_SQL
2276 (P_APPLICATION_ID =>l_content_row.APPLICATION_ID
2277 ,P_OBJECT_CODE =>p_new_content_code
2278 ,P_INTEGRATOR_CODE =>p_intg_code
2279 ,P_CONTENT_DESC =>p_content_name
2280 ,P_CONTENT_CLASS =>l_content_row.CONTENT_CLASS
2281 ,P_COL_LIST =>l_temp_val
2282 ,P_LANGUAGE =>userenv('LANG')
2283 ,P_SOURCE_LANGUAGE =>userenv('LANG')
2284 ,P_USER_ID =>1
2285 ,P_CONTENT_CODE =>l_content_out);
2286
2287 hr_utility.set_location('The content got created sucessfully ' || l_content_out, 55);
2288
2289 update bne_contents_b
2290 set param_list_code = l_content_row.param_list_code
2291 ,param_list_app_id = l_content_row.param_list_app_id
2292 where content_code =l_content_out;
2293
2294 OPEN c_content_cols_row(p_base_content_code);
2295 LOOP
2296 FETCH c_content_cols_row INTO l_column, l_read_only_flag, l_sequence_num;
2297 EXIT WHEN c_content_cols_row%NOTFOUND;
2298 update bne_content_cols_b set read_only_flag = l_read_only_flag
2299 where content_code = l_content_out
2300 and sequence_num = l_sequence_num;
2301
2302 END LOOP;
2303 CLOSE c_content_cols_row;
2304
2305 hr_utility.set_location('The content got created sucessfully ' || l_content_out, 55);
2306 p_content_out := l_content_out;
2307
2308 END Create_RIW_Content_Row_Others;
2309 -- =============================================================================
2310 -- ~ Create_RIW_Webadi_Setup:
2311 -- =============================================================================
2312 PROCEDURE Create_RIW_Webadi_Setup
2313 (p_application_id IN NUMBER
2314 ,p_data_source IN VARCHAR2
2315 ,p_user_function_name IN VARCHAR2
2316 ,p_menu_id IN NUMBER
2317 ,p_seq_params IN VARCHAR2
2318 ,p_intrfce_seq_params IN VARCHAR2
2319 ,p_xml_tag_params IN pqp_prompt_array_tab
2320 ,p_defalut_type_params IN VARCHAR2
2321 ,p_defalut_value_params IN pqp_default_array_tab
2322 ,p_placement_params IN VARCHAR2
2323 ,p_group_params IN VARCHAR2
2324 ,p_action_type IN VARCHAR2
2325 ,p_upd_layout_code IN VARCHAR2
2326 ,p_upd_interface_code IN VARCHAR2
2327 ,p_upd_mapping_code IN VARCHAR2
2328 ,p_ins_upd_datapmp_flag IN VARCHAR2
2329 ,p_entity_name IN VARCHAR2 DEFAULT NULL
2330 ,p_return_status OUT NOCOPY VARCHAR2) IS
2331
2332 l_seq_params VARCHAR2(3000);
2333 l_intrfce_seq_params VARCHAR2(3000);
2334 l_defalut_type_params VARCHAR2(3000);
2335 l_placement_params VARCHAR2(3000);
2336 l_group_params VARCHAR2(3000);
2337 l_seq_param_len NUMBER(3);
2338 l_intr_param_len NUMBER(3);
2339 l_typ_param_len NUMBER(3);
2340 l_pla_param_len NUMBER(3);
2341 l_grp_param_len NUMBER(3);
2342 l_sq_location NUMBER(3);
2343 l_inter_location NUMBER(3);
2344 l_type_location NUMBER(3);
2345 l_place_location NUMBER(3);
2346 l_group_location NUMBER(3);
2347 l_seq_value VARCHAR2(1000);
2348 l_intr_value VARCHAR2(1000);
2349 l_xml_value VARCHAR2(200);
2350 l_type_value VARCHAR2(2000);
2351 l_def_value VARCHAR2(2000);
2352 l_pla_value VARCHAR2(1000);
2353 l_grp_value VARCHAR2(1000);
2354 l_new_intrfc_code VARCHAR2(30);
2355 l_new_layout_code VARCHAR2(30);
2356 l_new_mapping_code VARCHAR2(30);
2357 l_new_intg_code VARCHAR2(30);
2358 l_user_name VARCHAR2(30);
2359 l_function_name VARCHAR2(30);
2360 l_func_parameters VARCHAR2(1000);
2361 l_riw_seq_id NUMBER;
2362 l_count NUMBER :=1;
2363 l_ovn NUMBER;
2364 l_seq NUMBER;
2365 l_out_content_code VARCHAR2(50);
2366 l_out_blnk_content_code VARCHAR2(50);
2367 l_base_content_code VARCHAR2(50);
2368 l_new_content_code VARCHAR2(50);
2369 l_new_blnk_content_code VARCHAR2(50);
2370 l_ins_upd_datapmp_flag VARCHAR2(50);
2371 l_ins_upd_datapmp_flag_len NUMBER;
2372 l_derived_param_list_code VARCHAR2(50);
2373 l_allow_insert VARCHAR2(10);
2374 l_allow_update VARCHAR2(10);
2375 l_allow_data_pump VARCHAR2(10);
2376 l_allow_insert_loc NUMBER;
2377 l_allow_update_loc NUMBER;
2378 l_allow_data_pump_loc NUMBER;
2379 l_migration_flag_loc NUMBER(2);
2380 l_migration_flag VARCHAR2(10);
2381 l_proc_name constant VARCHAR2(150) := g_proc_name ||'Create_RIW_Webadi_Setup';
2382 l_base_intg_code VARCHAR2(50);
2383 l_base_intf_code VARCHAR2(50);
2384 l_base_layout_code VARCHAR2(50);
2385 l_base_blnk_content_code VARCHAR2(50);
2386 l_entity_name VARCHAR2(20);
2387 l_flag1 VARCHAR2(20);
2388
2389 --Integration with RI Run Data Pump and Correct Errors Page
2390 l_setup_sub_task_action varchar2(520) :=
2391 'form_function=PER_RI_CREATE_DOCUMENT&APP_ID=800&bne:page=BneCreateDoc'||
2392 '&bne:reporting=N&bne:noreview=Y&';
2393 l_out_ce_content_code VARCHAR2(50);
2394 l_new_dp_mapping_code VARCHAR2(30);
2395 l_new_ce_layout_code VARCHAR2(50);
2396
2397 no_code_found EXCEPTION;
2398 l_func_type VARCHAR2(50);
2399 l_search VARCHAR2(10);
2400 BEGIN
2401 --Hr_Utility.trace_on(null,'RI_Trace');
2402 hr_utility.set_location('Entering: '||l_proc_name, 5);
2403 hr_utility.set_location('The flag is ' ||p_ins_upd_datapmp_flag, 6);
2404 hr_utility.set_location(p_seq_params || ' First', 10);
2405 hr_utility.set_location(p_intrfce_seq_params || ' Second', 15);
2406 -- hr_utility.set_location(p_xml_tag_params || ' Third', 10);
2407 hr_utility.set_location(p_defalut_type_params || ' Fourth', 10);
2408 -- hr_utility.set_location(p_defalut_value_params || ' Fifth', 10);
2409 hr_utility.set_location(p_placement_params || ' Sixth', 10);
2410 -- get next seq value : INTERFACE_SEQ
2411 hr_utility.set_location(p_entity_name, 15);
2412
2413 BEGIN
2414 SELECT description into l_func_type
2415 FROM pqp_flxdu_columns WHERE
2416 entity_type = p_entity_name AND
2417 flxdu_column_name = 'PQP_TYPE_FLAG';
2418 EXCEPTION
2419 WHEN NO_DATA_FOUND THEN NULL;
2420 END;
2421
2422 IF l_func_type IS NOT NULL THEN
2423 l_search := SUBSTR(l_func_type, 11, 1);
2424 ELSE
2425 l_search := 'T';
2426 END IF;
2427 hr_utility.set_location('The search Value is ' || l_search, 99);
2428
2429 SELECT PQP_FLXDU_FUNCTIONS_S.nextval
2430 INTO l_riw_seq_id
2431 FROM dual;
2432
2433 IF p_entity_name IS NOT NULL THEN
2434 BEGIN
2435 SELECT description INTO l_base_intg_code FROM PQP_FLXDU_COLUMNS WHERE
2436 FLXDU_COLUMN_NAME = 'INTEGRATOR_CODE' and
2437 ENTITY_TYPE = p_entity_name;
2438 EXCEPTION
2439 WHEN NO_DATA_FOUND THEN
2440 raise no_code_found;
2441 END;
2442
2443 BEGIN
2444 SELECT description INTO l_base_layout_code FROM PQP_FLXDU_COLUMNS WHERE
2445 FLXDU_COLUMN_NAME = 'LAYOUT_CODE' and
2446 ENTITY_TYPE = p_entity_name;
2447 EXCEPTION
2448 WHEN NO_DATA_FOUND THEN
2449 raise no_code_found;
2450 END;
2451
2452 IF l_search = 'T' THEN
2453 BEGIN
2454 SELECT description INTO l_base_content_code FROM PQP_FLXDU_COLUMNS WHERE
2455 FLXDU_COLUMN_NAME = 'CONTENT_CODE' and
2456 ENTITY_TYPE = p_entity_name;
2457 EXCEPTION
2458 WHEN NO_DATA_FOUND THEN
2459 raise no_code_found;
2460 END;
2461 END IF;
2462
2463 BEGIN
2464 SELECT description INTO l_base_blnk_content_code FROM PQP_FLXDU_COLUMNS WHERE
2465 FLXDU_COLUMN_NAME = 'BLANK_CONTENT_CODE' and
2466 ENTITY_TYPE = p_entity_name;
2467 EXCEPTION
2468 WHEN NO_DATA_FOUND THEN
2469 raise no_code_found;
2470 END;
2471
2472 BEGIN
2473 SELECT description INTO l_base_intf_code FROM PQP_FLXDU_COLUMNS WHERE
2474 FLXDU_COLUMN_NAME = 'INTERFACE_CODE' and
2475 ENTITY_TYPE = p_entity_name;
2476 EXCEPTION
2477 WHEN NO_DATA_FOUND THEN
2478 raise no_code_found;
2479 END;
2480 END IF;
2481
2482 l_new_intg_code := 'PQP_RIW'||l_riw_seq_id||'_INTG';
2483 l_derived_param_list_code := l_new_intg_code || '_DP';
2484 l_new_intrfc_code := 'PQP_RIW'||l_riw_seq_id||'_DATA_INTF';
2485 l_new_layout_code := 'PQP_RIW'||l_riw_seq_id||'_DATA_LAYOUT';
2486 l_new_mapping_code := 'PQP_RIW'||l_riw_seq_id||'_DATA_MAP_KEY';
2487 l_entity_name := p_entity_name;
2488
2489 --$ Mapping for Data Pump Correct Errors
2490 l_new_dp_mapping_code := 'PQP_RIW'||l_riw_seq_id||'_DP_MAP_KEY';
2491
2492 l_new_content_code := 'PQP_RIW'||l_riw_seq_id||'_CON';
2493 l_new_blnk_content_code := 'PQP_RIW_BLNK'||l_riw_seq_id||'_CON';
2494 l_function_name := 'PQP_RIW'||l_riw_seq_id||'_SEED_FUNC';
2495 l_func_parameters := 'pIntegrator='||l_new_intg_code||'$pLayout=';
2496 l_func_parameters := l_func_parameters||l_new_layout_code||'$pInterface=';
2497 l_func_parameters := l_func_parameters||l_new_intrfc_code||'$pMapping=';
2498 l_func_parameters := l_func_parameters||l_new_mapping_code||'$pDatasource='||p_data_source;
2499 l_user_name := l_new_mapping_code;
2500 l_seq_params := p_seq_params;
2501 l_intrfce_seq_params := p_intrfce_seq_params;
2502 l_defalut_type_params := p_defalut_type_params;
2503 l_placement_params := p_placement_params;
2504 l_group_params := p_group_params;
2505 l_ins_upd_datapmp_flag := p_ins_upd_datapmp_flag;
2506 l_seq_param_len := LENGTH(l_seq_params);
2507 l_intr_param_len := LENGTH(l_intrfce_seq_params);
2508 l_typ_param_len := LENGTH(l_defalut_type_params);
2509 l_pla_param_len := LENGTH(l_placement_params);
2510 l_grp_param_len := LENGTH(l_group_params);
2511 l_ins_upd_datapmp_flag_len := LENGTH(l_ins_upd_datapmp_flag);
2512 l_flag1 := l_ins_upd_datapmp_flag;
2513 l_allow_insert_loc := INSTR(l_ins_upd_datapmp_flag, ':');
2514 l_allow_insert := SUBSTR(l_ins_upd_datapmp_flag, 1, l_allow_insert_loc-1);
2515 l_flag1 := SUBSTR(l_ins_upd_datapmp_flag, l_allow_insert_loc+1, LENGTH(l_ins_upd_datapmp_flag));
2516 hr_utility.set_location('Allow First Is '|| l_ins_upd_datapmp_flag, 5);
2517 l_migration_flag_loc := INSTR(l_flag1, ':');
2518 hr_utility.set_location('Allow Second Is '|| l_migration_flag_loc, 6);
2519 l_migration_flag := SUBSTR(l_flag1, 3);
2520 hr_utility.set_location('Allow Third Is '|| l_migration_flag, 6);
2521 hr_utility.set_location('Allow Insert Is '|| l_allow_insert, 7);
2522 hr_utility.set_location('Allow Migration Is '|| l_migration_flag, 8);
2523 IF l_allow_insert = 'Create' THEN
2524 g_ins_upd_flag := 'C';
2525 IF l_entity_name is null THEN
2526 g_ins_upd_flag := g_ins_upd_flag||':'||l_migration_flag;
2527 END IF;
2528 END IF;
2529 IF l_allow_insert = 'Update' THEN
2530 g_ins_upd_flag := 'U';
2531 IF l_entity_name is null THEN
2532 g_ins_upd_flag := g_ins_upd_flag||':'||l_migration_flag;
2533 END IF;
2534 END IF;
2535 IF l_allow_insert = 'Download' THEN
2536 g_ins_upd_flag := 'D';
2537 IF l_entity_name is null THEN
2538 g_ins_upd_flag := g_ins_upd_flag||':'||l_migration_flag;
2539 END IF;
2540 END IF;
2541 hr_utility.set_location('Allow Insert Is '|| g_ins_upd_flag, 7);
2542
2543 hr_utility.set_location('Before PL/SQL Records', 5);
2544 LOOP
2545 l_sq_location := INSTR(l_seq_params, '+');
2546 l_inter_location := INSTR(l_intrfce_seq_params, '+');
2547 l_type_location := INSTR(l_defalut_type_params, '+');
2548 l_place_location := INSTR(l_placement_params, '+');
2549 l_group_location := INSTR(l_group_params, '+');
2550
2551 -- Sequence number
2552 l_seq_value := SUBSTR(l_seq_params, 1, l_sq_location - 1);
2553 IF l_sq_location = 0 THEN
2554 l_seq_value := l_seq_params;
2555 END IF;
2556 l_seq_params := SUBSTR(l_seq_params, l_sq_location + 1, l_seq_param_len);
2557
2558 -- Interface Number
2559 l_intr_value := SUBSTR(l_intrfce_seq_params, 1, l_inter_location - 1);
2560 IF l_inter_location = 0 THEN
2561 l_intr_value := l_intrfce_seq_params;
2562 END IF;
2563 l_intrfce_seq_params := SUBSTR(l_intrfce_seq_params, l_inter_location + 1, l_intr_param_len);
2564
2565 -- Xml tags
2566 IF p_xml_tag_params(l_count) <> '+' THEN
2567 l_xml_value := p_xml_tag_params(l_count);
2568 ELSE
2569 l_xml_value := null;
2570 END IF;
2571
2572 --Default types
2573 l_type_value := SUBSTR(l_defalut_type_params, 1, l_type_location - 1);
2574 IF l_type_location = 0 THEN
2575 l_type_value := l_defalut_type_params;
2576 END IF;
2577 l_defalut_type_params := SUBSTR(l_defalut_type_params, l_type_location + 1, l_typ_param_len);
2578
2579 --Default Values
2580 IF p_defalut_value_params(l_count) <> '+' THEN
2581 l_def_value := p_defalut_value_params(l_count);
2582 ELSE
2583 l_def_value := null;
2584 END IF;
2585
2586 --Placements
2587 l_pla_value := SUBSTR(l_placement_params, 1, l_place_location - 1);
2588 IF l_place_location = 0 THEN
2589 l_pla_value := l_placement_params;
2590 END IF;
2591 l_placement_params := SUBSTR(l_placement_params, l_place_location + 1, l_pla_param_len);
2592
2593 --Group Name
2594 l_grp_value := SUBSTR(l_group_params, 1, l_group_location - 1);
2595 IF l_group_location = 0 THEN
2596 l_grp_value := l_group_params;
2597 END IF;
2598 l_group_params := SUBSTR(l_group_params, l_group_location + 1, l_grp_param_len);
2599
2600 g_riw_data(TO_NUMBER(l_intr_value)).sequence := TO_NUMBER(l_seq_value);
2601 g_riw_data(TO_NUMBER(l_intr_value)).interface_seq := TO_NUMBER(l_intr_value);
2602 g_riw_data(TO_NUMBER(l_intr_value)).xml_tag := l_xml_value;
2603 g_riw_data(TO_NUMBER(l_intr_value)).default_type := l_type_value;
2604 g_riw_data(TO_NUMBER(l_intr_value)).default_value := l_def_value;
2605 g_riw_data(TO_NUMBER(l_intr_value)).placement := l_pla_value;
2606 g_riw_data(TO_NUMBER(l_intr_value)).group_name := l_grp_value;
2607
2608 --Index by Sequence
2609 g_temp_riw_data(l_count).sequence := TO_NUMBER(l_seq_value);
2610 g_temp_riw_data(l_count).interface_seq := TO_NUMBER(l_intr_value);
2611 g_temp_riw_data(l_count).xml_tag := l_xml_value;
2612 g_temp_riw_data(l_count).default_type := l_type_value;
2613 g_temp_riw_data(l_count).default_value := l_def_value;
2614 g_temp_riw_data(l_count).placement := l_pla_value;
2615 g_temp_riw_data(l_count).group_name := l_grp_value;
2616
2617 l_count :=l_count+1;
2618
2619 EXIT WHEN l_sq_location = 0;
2620 END LOOP;
2621
2622 hr_utility.set_location('Before Calling functions', 5);
2623 IF p_action_type ='Update' THEN
2624 hr_utility.trace('Inside Update:');
2625 Delete_riw_integrator
2626 (p_LAYOUT_CODE => p_upd_layout_code
2627 ,p_MAPPING_CODE => p_upd_mapping_code
2628 ,p_INTERFACE_CODE => p_upd_interface_code
2629 ,p_application_id => p_application_id ) ;
2630 END IF;
2631
2632 --Create Integrator related stuff
2633 IF p_entity_name IS NULL then
2634 l_base_intg_code := 'PQP_FLEXIBLE_WEBADI_INTG';
2635 END IF;
2636 Create_RIW_Integrator_Row
2637 (p_application_id =>p_application_id
2638 ,p_new_intg_code =>l_new_intg_code
2639 ,p_base_intg_code =>l_base_intg_code
2640 ,p_integrator_name =>p_user_function_name);
2641
2642 --Create Interface related stuff
2643 IF p_entity_name IS NULL then
2644 l_base_intf_code := 'PQP_FLEXIBLE_WEBADI_INTF';
2645 END IF;
2646 Create_RIW_Interface_Row
2647 (p_application_id => p_application_id
2648 ,p_new_interface_code => l_new_intrfc_code
2649 ,p_base_interface_code => l_base_intf_code
2650 ,p_user_name => p_user_function_name
2651 ,p_new_intg_code => l_new_intg_code);
2652
2653 --Create Interface related stuff
2654 Create_RIW_Interface_Col_Rows
2655 (p_application_id => p_application_id
2656 ,p_new_interface_code => l_new_intrfc_code
2657 ,p_base_interface_code => l_base_intf_code
2658 ,p_entity_name => l_entity_name);
2659
2660
2661 --Create Context related
2662 IF l_search = 'T' THEN
2663 IF p_entity_name IS NULL then
2664 IF p_data_source = 'HR' THEN
2665 l_base_content_code :='PQP_FLEXI_WEBADI_HR_CNT';
2666 ELSIF p_data_source = 'XML' THEN
2667 l_base_content_code :='PQP_FLEXI_WEBADI_XML_CNT';
2668 ELSIF p_data_source = 'CSV' THEN
2669 l_base_content_code :='PQP_FLEXI_WEBADI_CSV_CNT';
2670 END IF;
2671 Create_RIW_Content_Row
2672 (p_application_id =>p_application_id
2673 ,p_new_content_code =>l_new_content_code
2674 ,p_base_content_code =>l_base_content_code
2675 ,p_intg_code =>l_new_intg_code
2676 ,p_content_name =>p_user_function_name
2677 ,p_content_out =>l_out_content_code
2678 --$ Data Pump Correct Errors Content
2679 ,p_ce_content_out =>l_out_ce_content_code);
2680 ELSE
2681 Create_RIW_Content_Row_Others
2682 (p_application_id =>p_application_id
2683 ,p_new_content_code =>l_new_content_code
2684 ,p_base_content_code =>l_base_content_code
2685 ,p_intg_code =>l_new_intg_code
2686 ,p_content_name =>p_user_function_name
2687 ,p_entity_name =>l_entity_name
2688 ,p_content_out =>l_out_content_code);
2689 hr_utility.set_location('Outside the call ' || l_out_content_code, 76);
2690 END IF;
2691 END IF;
2692
2693
2694
2695 l_func_parameters := l_func_parameters||'$pContent='||l_out_content_code;
2696
2697 IF p_data_source = 'HR' THEN
2698 Create_RIW_BLNK_Content_row(p_application_id =>p_application_id
2699 ,p_new_blnk_content_code =>l_new_blnk_content_code
2700 ,p_intg_code =>l_new_intg_code
2701 ,p_content_name =>p_user_function_name||' BLNK'
2702 ,p_blnk_content_out =>l_out_blnk_content_code);
2703
2704 l_func_parameters := l_func_parameters||'$pBlnkContent='||l_out_blnk_content_code;
2705 ELSE
2706 l_func_parameters := l_func_parameters||'$pBlnkContent='||'null';
2707 END IF;
2708
2709
2710 --Create Layout Stuff
2711 IF p_entity_name IS NULL THEN
2712 l_base_layout_code := 'PQP_FLEXIBLE_WEBADI_LAYOUT';
2713 END IF;
2714
2715 Create_RIW_Layout_Row
2716 (p_application_id => p_application_id
2717 ,p_new_layout_code => l_new_layout_code
2718 ,p_base_layout_code => l_base_layout_code
2719 ,p_user_name => p_user_function_name
2720 ,p_new_intg_code => l_new_intg_code);
2721
2722 Create_RIW_Layout_Blocks_Row
2723 (p_application_id => p_application_id
2724 ,p_new_layout_code => l_new_layout_code
2725 ,p_base_layout_code => l_base_layout_code
2726 ,p_user_name => p_user_function_name);
2727 hr_utility.set_location('Executed block API', 50);
2728 Create_RIW_Layout_Cols_Row
2729 (p_application_id => p_application_id
2730 ,p_new_layout_code => l_new_layout_code
2731 ,p_base_layout_code => l_base_layout_code
2732 ,p_new_interface_code => l_new_intrfc_code
2733 --$ Pass Data Source as well to make changes in layout accordingly
2734 ,p_data_source => p_data_source
2735 ,p_entity_name => l_entity_name);
2736
2737 --$ Create Correct Errors Layout (Only for Person/Assignment/Address)
2738 IF p_entity_name IS NULL THEN
2739 l_new_ce_layout_code := 'PQP_RIW'||l_riw_seq_id||'_CE_LAYOUT';
2740
2741 Create_RIW_Layout_Row
2742 (p_application_id => p_application_id
2743 ,p_new_layout_code => l_new_ce_layout_code
2744 ,p_base_layout_code => 'PQP_FLEXIBLE_WEBADI_CE_LAYOUT'
2745 ,p_user_name => p_user_function_name||' CE'
2746 ,p_new_intg_code => l_new_intg_code);
2747
2748 Create_RIW_Layout_Blocks_Row
2749 (p_application_id => p_application_id
2750 ,p_new_layout_code => l_new_ce_layout_code
2751 ,p_base_layout_code => 'PQP_FLEXIBLE_WEBADI_CE_LAYOUT'
2752 ,p_user_name => p_user_function_name||' CE');
2753
2754 Create_RIW_Layout_Cols_Row
2755 (p_application_id => p_application_id
2756 ,p_new_layout_code => l_new_ce_layout_code
2757 ,p_base_layout_code => 'PQP_FLEXIBLE_WEBADI_CE_LAYOUT'
2758 ,p_new_interface_code => l_new_intrfc_code
2759 --$
2760 ,p_data_source => p_data_source);
2761 END IF;
2762 --
2763 --Create Mappings Stuff
2764 IF l_search = 'T' THEN
2765 Create_RIW_mappings_row
2766 (p_application_id => p_application_id
2767 ,p_new_mapping_code => l_new_mapping_code
2768 ,p_user_name => p_user_function_name
2769 ,p_data_source => p_data_source
2770 ,p_new_intg_code => l_new_intg_code
2771 ,p_entity_name => l_entity_name);
2772
2773 hr_utility.set_location(l_out_content_code, 56);
2774 Create_RIW_Mapping_Links_Rows
2775 (p_application_id => p_application_id
2776 ,p_new_mapping_code => l_new_mapping_code
2777 ,p_new_interface_code => l_new_intrfc_code
2778 ,p_data_source => p_data_source
2779 ,p_content_out => l_out_content_code
2780 ,p_entity_name => l_entity_name);
2781
2782 IF p_entity_name IS NULL THEN
2783 --$ For Data Pump Correct Errors , Create another Mapping
2784 Create_RIW_mappings_row
2785 (p_application_id => p_application_id
2786 ,p_new_mapping_code => l_new_dp_mapping_code
2787 ,p_user_name => p_user_function_name||' CE MAP'
2788 ,p_data_source => 'DP'
2789 ,p_new_intg_code =>l_new_intg_code);
2790
2791 Create_RIW_Mapping_Links_Rows
2792 (p_application_id => p_application_id
2793 ,p_new_mapping_code => l_new_dp_mapping_code
2794 ,p_new_interface_code => l_new_intrfc_code
2795 ,p_data_source => 'DP'
2796 ,p_content_out => l_out_ce_content_code);
2797 --
2798 END IF;
2799 END IF;
2800 Create_RIW_OAF_Function
2801 (p_application_id =>p_application_id
2802 ,p_function_name =>l_function_name
2803 ,p_base_function_name =>'PQPRIWSEEDFUNC'
2804 ,p_action_type => p_action_type
2805 ,p_data_source => p_data_source
2806 ,p_func_parameters => l_func_parameters
2807 ,p_user_function_name => p_user_function_name
2808 ,p_new_interface_code => l_new_intrfc_code
2809 ,p_new_layout_code => l_new_layout_code);
2810
2811 --Integration with RI data pump mechanism
2812 -- After second '#' correct errors content,layout and mapping code have to be inserted
2813
2814 l_setup_sub_task_action := l_setup_sub_task_action || 'bne:integrator='||l_new_intg_code
2815 ||'#bne:layout='||l_new_layout_code||'&bne:content='||l_out_content_code
2816 ||'&bne:map='||l_new_mapping_code
2817 ||'#bne:layout='||l_new_ce_layout_code||'&bne:content='||l_out_ce_content_code
2818 ||'&bne:map='||l_new_dp_mapping_code;
2819
2820 --$In case of update, update setup_sub_task_action in per_ri_setup_sub_tasks
2821
2822 IF p_action_type = 'Update' THEN
2823
2824 SELECT fff.function_name into l_function_name
2825 FROM fnd_form_functions fff, fnd_form_functions_tl ffft
2826 WHERE fff.FUNCTION_ID = ffft.FUNCTION_ID
2827 AND ffft.SOURCE_LANG = userenv('LANG')
2828 AND ffft.LANGUAGE = userenv('LANG')
2829 AND ffft.USER_FUNCTION_NAME = p_user_function_name;
2830
2831 SELECT object_version_number
2832 into l_ovn
2833 FROM per_ri_setup_sub_tasks
2834 WHERE setup_sub_task_code = l_function_name;
2835
2836 per_ri_setup_sub_task_api.update_setup_sub_task(
2837 p_validate => false
2838 ,p_setup_sub_task_code => l_function_name
2839 ,p_setup_sub_task_action => l_setup_sub_task_action
2840 ,p_effective_date => sysdate
2841 ,p_object_version_number => l_ovn
2842 );
2843
2844 END IF;
2845
2846 IF p_action_type <> 'Update' THEN
2847 Create_RIW_Menu_Entries
2848 (p_application_id =>p_application_id
2849 ,p_menu_id =>p_menu_id
2850 ,p_function_name =>l_function_name
2851 ,p_user_function_name =>p_user_function_name);
2852
2853 SELECT max(SETUP_SUB_TASK_SEQUENCE)
2854 INTO l_seq
2855 FROM per_ri_setup_sub_tasks
2856 WHERE SETUP_TASK_CODE='LOAD_EMPLOYEE_DETAILS';
2857
2858
2859 per_ri_setup_sub_task_api.create_setup_sub_task
2860 (p_validate => false
2861 ,p_setup_sub_task_code => l_function_name
2862 ,p_setup_sub_task_name => p_user_function_name
2863 ,p_setup_sub_task_description => p_user_function_name
2864 ,p_setup_task_code => p_user_function_name --'LOAD_EMPLOYEE_DETAILS'
2865 ,p_setup_sub_task_sequence => l_seq+1
2866 ,p_setup_sub_task_status => 'NOT_STARTED'
2867 ,p_setup_sub_task_type => NULL
2868 ,p_setup_sub_task_dp_link => 'SPREADSHEET_LOADER' --NULL
2869 ,p_setup_sub_task_action => l_setup_sub_task_action --l_function_name
2870 ,p_setup_sub_task_creation_date => sysdate
2871 ,p_setup_sub_task_last_mod_date => sysdate
2872 ,p_legislation_code => NULL
2873 ,p_language_code => 'US'
2874 ,p_effective_date => sysdate
2875 ,p_object_version_number => l_ovn );
2876
2877 END IF;
2878 g_temp_riw_data.DELETE;
2879 g_riw_data.DELETE;
2880 p_return_status := 'Y';
2881 hr_utility.set_location('Leaving: '||l_proc_name, 5);
2882
2883 Create_Derived_Param_List(p_application_id => p_application_id
2884 ,p_param_list_code => l_derived_param_list_code
2885 ,p_parameter_value => l_ins_upd_datapmp_flag);
2886 Exception
2887 when others then
2888 hr_utility.set_location('Error: '||l_proc_name, 5);
2889 g_temp_riw_data.DELETE;
2890 g_riw_data.DELETE;
2891 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
2892 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
2893 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
2894 p_return_status := 'E';
2895 END Create_RIW_Webadi_Setup;
2896
2897
2898
2899 -- =============================================================================
2900 -- ~ Delete_RIW_Webadi_Setup:
2901 -- =============================================================================
2902 PROCEDURE Delete_RIW_Webadi_Setup
2903 (p_function_id IN NUMBER
2904 ,p_menu_id IN NUMBER) IS
2905
2906 CURSOR csr_get_fun_name IS
2907 SELECT setup_sub_task_code
2908 ,object_version_number
2909 FROM per_ri_setup_sub_tasks
2910 WHERE setup_sub_task_code --$ setup_sub_task_action
2911 = (SELECT FLXDU_FUNC_NAME
2912 FROM PQP_FLXDU_FUNC_ATTRIBUTES
2913 WHERE FLXDU_FUNC_ATTRIBUTE_ID = p_function_id );
2914
2915 --$ To fetch Interface Code from function id
2916 CURSOR csr_get_interface_code IS
2917 select flxdu_func_integrator_code from
2918 PQP_FLXDU_FUNC_ATTRIBUTES where FLXDU_FUNC_ATTRIBUTE_ID = p_function_id;
2919
2920 l_ENTRY_SEQUENCE NUMBER;
2921 l_menu_id NUMBER;
2922 l_count NUMBER;
2923 l_setup_sub_task_code VARCHAR2(60);
2924 l_ovn NUMBER;
2925 l_proc_name VARCHAR2(72) := g_package||'Delete_RIW_Webadi_Setup';
2926
2927 --$
2928 l_intf_code VARCHAR2(30);
2929
2930 BEGIN
2931 -- hr_utility.trace_on(null,'TTT');
2932 -- get the count of function_id from fnd_menu_entries
2933 SELECT count(function_id)
2934 INTO l_count
2935 FROM fnd_menu_entries
2936 WHERE function_id = p_function_id;
2937
2938
2939 --$ Call Delete_riw_integrator to delete entire integrator setup
2940 OPEN csr_get_interface_code;
2941 FETCH csr_get_interface_code into l_intf_code;
2942 CLOSE csr_get_interface_code;
2943
2944 IF l_count = 1 THEN
2945
2946 -- get menu id and entry seq from fnd_menu_entries
2947 SELECT ENTRY_SEQUENCE
2948 ,MENU_ID
2949 INTO l_ENTRY_SEQUENCE
2950 ,l_menu_id
2951 FROM fnd_menu_entries
2952 WHERE function_id = p_function_id;
2953
2954 -- delete row for menu id , entry seq
2955 FND_MENU_ENTRIES_PKG.DELETE_ROW(
2956 X_MENU_ID => l_menu_id
2957 ,X_ENTRY_SEQUENCE => l_ENTRY_SEQUENCE);
2958
2959 OPEN csr_get_fun_name;
2960 FETCH csr_get_fun_name INTO l_setup_sub_task_code ,l_ovn;
2961 CLOSE csr_get_fun_name;
2962
2963 IF l_setup_sub_task_code IS NOT NULL THEN
2964 per_ri_setup_sub_task_api.delete_setup_sub_task
2965 (p_validate => false
2966 ,p_setup_sub_task_code => l_setup_sub_task_code
2967 ,p_object_version_number => l_ovn);
2968 END IF;
2969
2970 DELETE
2971 FROM PQP_FLXDU_FUNC_ATTRIBUTES
2972 WHERE FLXDU_FUNC_ATTRIBUTE_ID = p_function_id;
2973
2974 FND_FORM_FUNCTIONS_PKG.DELETE_ROW(
2975 X_FUNCTION_ID => p_function_id);
2976
2977
2978 hr_utility.trace('DELETE: INTERFACE='|| l_intf_code);
2979
2980 if l_intf_code IS NOT NULL then
2981 Delete_riw_integrator(p_INTERFACE_CODE => l_intf_code
2982 ,p_application_id => 8303 );
2983 end if;
2984
2985 /*ELSE
2986
2987 SELECT ENTRY_SEQUENCE INTO l_ENTRY_SEQUENCE
2988 FROM fnd_menu_entries
2989 WHERE MENU_ID = l_menu_id
2990 AND function_id = p_function_id;
2991
2992 FND_MENU_ENTRIES_PKG.DELETE_ROW(
2993 X_MENU_ID => p_menu_id
2994 ,X_ENTRY_SEQUENCE => l_ENTRY_SEQUENCE);
2995 */
2996 END IF;
2997
2998 END Delete_RIW_Webadi_Setup;
2999
3000
3001
3002
3003 -- =============================================================================
3004 -- ~ Create_RIW_XML_Tags: to create / update user defined XML Tags
3005 -- =============================================================================
3006 PROCEDURE Create_RIW_XML_Tags
3007 (p_field_id IN NUMBER
3008 ,p_xml_tag_id IN NUMBER
3009 ,p_xml_tag_name IN VARCHAR2
3010 ,p_business_group_id IN VARCHAR2 ) IS
3011
3012
3013 l_riw_xml_id NUMBER := 0;
3014 l_count NUMBER;
3015 l_proc VARCHAR2(72) := g_package||'Create_RIW_XML_Tags';
3016
3017 BEGIN
3018
3019 -- get next sequence value
3020 SELECT PQP_FLXDU_XML_TAGS_S.nextval
3021 INTO l_riw_xml_id
3022 FROM dual;
3023
3024 -- get the count of FLXDU_XML_TAG_ID in PQP_FLXDU_XML_TAGS
3025 SELECT count(FLXDU_XML_TAG_ID)
3026 INTO l_count
3027 FROM PQP_FLXDU_XML_TAGS
3028 WHERE BUSINESS_GROUP_ID = p_business_group_id
3029 AND FLXDU_COLUMN_ID = p_field_id;
3030
3031 -- if count = 0 , then insert into PQP_FLXDU_XML_TAGS
3032 IF l_count = 0 THEN
3033 IF p_xml_tag_name IS NOT NULL THEN
3034 insert into PQP_FLXDU_XML_TAGS
3035 (FLXDU_COLUMN_ID
3036 ,FLXDU_XML_TAG_ID
3037 ,FLXDU_XML_TAG_NAME
3038 ,BUSINESS_GROUP_ID
3039 ,CREATED_BY
3040 ,CREATION_DATE
3041 ,LAST_UPDATED_BY
3042 ,LAST_UPDATE_DATE
3043 ,LAST_UPDATE_LOGIN
3044 ,OBJECT_VERSION_NUMBER)
3045 values
3046 (p_field_id
3047 ,l_riw_xml_id
3048 ,p_xml_tag_name
3049 ,p_business_group_id
3050 ,1
3051 ,sysdate
3052 ,1
3053 ,sysdate
3054 ,1
3055 ,1);
3056 END IF;
3057 -- if count > 0 then
3058 ELSE
3059 -- update PQP_FLXDU_XML_TAGS if p_xml_tag_name is not null
3060 IF p_xml_tag_name IS NOT NULL THEN
3061 UPDATE PQP_FLXDU_XML_TAGS
3062 SET FLXDU_XML_TAG_NAME = p_xml_tag_name
3063 WHERE FLXDU_XML_TAG_ID = p_xml_tag_id;
3064 -- delete frm PQP_FLXDU_XML_TAGS if p_xml_tag_name is null
3065 ELSE
3066 DELETE
3067 FROM PQP_FLXDU_XML_TAGS
3068 WHERE FLXDU_XML_TAG_ID = p_xml_tag_id;
3069 END IF;
3070 END IF;
3071 END Create_RIW_XML_Tags;
3072
3073
3074
3075
3076 -- =============================================================================
3077 -- ~ Delete_RIW_XML_Tag:
3078 -- =============================================================================
3079 PROCEDURE Delete_RIW_XML_Tag
3080 (p_xml_tag_id IN NUMBER
3081 ,p_business_group_id IN NUMBER) IS
3082
3083 l_count NUMBER;
3084 l_proc VARCHAR(72) := g_package||'Delete_RIW_XML_Tag';
3085
3086 BEGIN
3087
3088 -- delete mxl tag from PQP_FLXDU_XML_TAGS for tagId and bgId passed
3089 DELETE
3090 FROM PQP_FLXDU_XML_TAGS
3091 WHERE FLXDU_XML_TAG_ID = p_xml_tag_id
3092 AND BUSINESS_GROUP_ID = p_business_group_id;
3093
3094
3095 END Delete_RIW_XML_Tag;
3096
3097 -- =============================================================================
3098 -- ~ Get Concatenated Exception for the linked batch lines:
3099 -- =============================================================================
3100 FUNCTION Get_concatenated_exception(p_batch_id in number,p_batch_link in number)
3101 return varchar2
3102 is
3103 concatstr varchar2(1000) := '';
3104 cursor csr_exp is
3105 select exception_text from hr_pump_batch_exceptions where source_id in (select
3106 batch_line_id from hr_pump_batch_lines where batch_id = p_batch_id and
3107 link_value = p_batch_link);
3108 begin
3109 for rec_exp in csr_exp loop
3110 concatstr := concatstr||rec_exp.exception_text||' ';
3111 end loop;
3112 return concatstr;
3113 end;
3114 -- =============================================================================
3115 -- ~ Get Descriptive Flexfield concatanated data:
3116 -- =============================================================================
3117 FUNCTION Get_Concatanated_DFF_Segments
3118 (p_dff_name IN VARCHAR2
3119 ,p_app_id IN NUMBER
3120 ,p_context IN VARCHAR2
3121 ,p_effective_date IN DATE
3122 ,p_entity IN VARCHAR2
3123 ,p_entity_id IN NUMBER)
3124 RETURN Varchar2 IS
3125
3126 -- Cursor to get Delimiter and the Context for a given DFF
3127 CURSOR csr_get_delimiter_and_context (c_dff_name IN VARCHAR2
3128 ,c_app_id IN NUMBER) IS
3129 SELECT concatenated_segment_delimiter, context_column_name
3130 FROM fnd_descriptive_flexs
3131 WHERE descriptive_flexfield_name = (SELECT descriptive_flexfield_name
3132 FROM fnd_descriptive_flexs_tl
3133 WHERE title = c_dff_name
3134 AND language = userenv('LANG'))
3135 AND application_id = c_app_id;
3136
3137 -- Cursor to get the global segments and the context specific segments for the DFF
3138 CURSOR csr_get_dff_segments (c_dff_name IN VARCHAR2
3139 ,c_context IN VARCHAR2) IS
3140 SELECT fd.application_column_name
3141 FROM fnd_descr_flex_column_usages fd,
3142 fnd_descr_flex_col_usage_tl fdtl
3143 WHERE fd.descriptive_flexfield_name = fdtl.descriptive_flexfield_name
3144 AND fd.descriptive_flex_context_code=fdtl.descriptive_flex_context_code
3145 AND fd.descriptive_flexfield_name = (SELECT descriptive_flexfield_name
3146 FROM fnd_descriptive_flexs_tl
3147 WHERE title = c_dff_name
3148 AND language = userenv('LANG'))
3149 AND fd.descriptive_flex_context_code in (c_context)
3150 AND fd.application_column_name = fdtl.application_column_name
3151 AND fdtl.language = userenv('LANG')
3152 AND fd.ENABLED_FLAG = 'Y' --Changed by pkagrawa
3153 -- AND fd.DISPLAY_FLAG = 'Y' --Changed by pkagrawa
3154 ORDER BY fd.column_seq_num;
3155
3156 r_delim_ctx_rec r_delim_contxt;
3157 r_segment_list_rec r_segment_list;
3158 l_func_name CONSTANT VARCHAR2(150) := g_package || 'Get_Concatanated_DFF_Segments';
3159 -- l_segment_list VARCHAR2(1000);
3160 l_concat_segments VARCHAR2(300);
3161
3162 l_delimiter fnd_descriptive_flexs.concatenated_segment_delimiter%TYPE;
3163 l_dff_ctx_val fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE;
3164
3165 l_dyn_sql_qry Varchar(1000);
3166 l_effective_date DATE;
3167
3168 -- Dynamic Ref Cursor
3169 TYPE ref_cur_typ IS REF CURSOR;
3170 csr_get_cnct_segs ref_cur_typ;
3171
3172 l_segment_count NUMBER :=0;
3173 l_string_length NUMBER :=0;
3174
3175 --$1
3176 -- Escape delimiter and '\' in each segment's value
3177 replace_prefix varchar2(50) := 'replace(replace(';
3178 replace_suffix1 varchar2(50) := ',''\'',''\\''),''';
3179 -- ||l_delimiter||
3180 replace_suffix2 varchar2(50) := ''',''\';
3181 -- ||l_delimiter||
3182 replace_suffix3 varchar2(10) := ''')';
3183 -- replace_prefix|| csr_get_dff_segments_rec.application_column_name || replace_suffix1 || l_delimiter ||
3184 -- replace_suffix2 ||l_delimiter|| replace_suffix3
3185 -- increase size of segment list variable
3186 l_segment_list VARCHAR2(3000);
3187
3188
3189 BEGIN
3190
3191 -- Get the Delimiter and Context for the given DFF
3192 OPEN csr_get_delimiter_and_context(c_dff_name => p_dff_name
3193 ,c_app_id => p_app_id);
3194
3195 FETCH csr_get_delimiter_and_context INTO r_delim_ctx_rec;
3196
3197 -- If Delimiter is not found then that means the passed DFF doesn't exist for
3198 -- the passed application id. Raise an Error
3199 IF csr_get_delimiter_and_context%NOTFOUND THEN
3200 CLOSE csr_get_delimiter_and_context;
3201 Hr_Utility.raise_error;
3202 END IF;
3203 CLOSE csr_get_delimiter_and_context;
3204
3205 l_delimiter := r_delim_ctx_rec.con_seg_delim;
3206 l_dff_ctx_val := r_delim_ctx_rec.con_col_name;
3207
3208 --Initialize the segment list with the global segments
3209 l_segment_list := '''' || '''' || '''' || '''' || '||';
3210 FOR csr_get_dff_segments_rec IN csr_get_dff_segments(c_dff_name => p_dff_name
3211 ,c_context => 'Global Data Elements')
3212 LOOP
3213 l_segment_count := l_segment_count +1;
3214 -- l_segment_list := l_segment_list || csr_get_dff_segments_rec.application_column_name || '||''' || l_delimiter || '''||';
3215 --$2
3216 l_segment_list := l_segment_list ||
3217 replace_prefix|| csr_get_dff_segments_rec.application_column_name || replace_suffix1 || l_delimiter ||
3218 replace_suffix2 ||l_delimiter|| replace_suffix3 || '||''' || l_delimiter || '''||';
3219
3220 END LOOP;
3221
3222 -- If there are no global segments defined, and the context is null, then return empty string
3223 -- as there is no data in the DFF
3224 IF (l_segment_count = 0) AND ((p_context = 'Global Data Elements') OR (p_context IS NULL) OR (p_context = '')) THEN
3225 -- IF (l_segment_count = 0) AND ((p_context IS NULL) OR (p_context = '')) THEN
3226 RETURN '';
3227 END IF;
3228
3229
3230
3231 --IF condition added by pkagrawa to check for non global data context
3232 IF p_context <> 'Global Data Elements' THEN
3233
3234 hr_utility.trace('Inside non global condition');
3235
3236 --$3
3237 l_segment_count := l_segment_count +1; -- keeping no of delimiters and segments in sync
3238
3239 --Append the context soon after the global segments
3240 l_segment_list := l_segment_list || l_dff_ctx_val;
3241
3242 --Append the context specific segments
3243 FOR csr_get_dff_segments_rec IN csr_get_dff_segments(c_dff_name => p_dff_name
3244 ,c_context => p_context)
3245 LOOP
3246 l_segment_count := l_segment_count +1;
3247 -- l_segment_list := l_segment_list || '||''' || l_delimiter || '''||' || csr_get_dff_segments_rec.application_column_name;
3248 --$4
3249 l_segment_list := l_segment_list || '||''' || l_delimiter || '''||' ||
3250 replace_prefix|| csr_get_dff_segments_rec.application_column_name || replace_suffix1 || l_delimiter ||
3251 replace_suffix2 ||l_delimiter|| replace_suffix3 ;
3252
3253 END LOOP;
3254 ELSE
3255 -- remove the added delimiter for context value ( '||''' || l_delimiter || '''||' )
3256 -- i.e. ( ||'.'|| )from the end
3257 l_segment_list := SUBSTR(l_segment_list,1,LENGTH(l_segment_list)-7);
3258 END IF;
3259 -- l_effective_date := fnd_date.canonical_to_date(p_effective_date);
3260 l_effective_date := p_effective_date;
3261
3262 IF p_entity = 'PERSON' THEN
3263 l_dyn_sql_qry := ' SELECT ' || l_segment_list ||
3264 ' FROM per_people_f ' ||
3265 ' WHERE person_id = :p_entity_id' ||
3266 ' AND :dt BETWEEN effective_start_date AND effective_end_date';
3267
3268 ELSIF p_entity = 'ASSIGNMENT' THEN
3269 l_dyn_sql_qry := ' SELECT ' || l_segment_list ||
3270 ' FROM per_assignments_f ' ||
3271 ' WHERE assignment_id = :p_entity_id' ||
3272 ' AND :dt BETWEEN effective_start_date AND effective_end_date';
3273
3274 ELSIF p_entity = 'ENROLLMENT' THEN
3275 l_dyn_sql_qry := ' SELECT ' || l_segment_list ||
3276 ' FROM ota_delegate_bookings ' ||
3277 ' WHERE booking_id = :p_entity_id';
3278 ELSE
3279 l_dyn_sql_qry := ' SELECT ' || l_segment_list ||
3280 ' FROM per_addresses ' ||
3281 ' WHERE address_id = :p_entity_id';
3282 END IF;
3283
3284 IF p_entity = 'PERSON' OR p_entity = 'ASSIGNMENT' THEN
3285 OPEN csr_get_cnct_segs FOR l_dyn_sql_qry USING p_entity_id, p_effective_date;
3286 ELSE
3287 OPEN csr_get_cnct_segs FOR l_dyn_sql_qry USING p_entity_id;
3288 END IF;
3289
3290 FETCH csr_get_cnct_segs INTO l_concat_segments;
3291
3292 --Check if all the segments are blank. If so, return empty string
3293 l_string_length := LENGTH(REPLACE(l_concat_segments,p_context,''));
3294 -- IF l_segment_count + 1 = l_string_length THEN
3295
3296 -- $5
3297 -- IF l_segment_count + 1 = l_string_length THEN
3298 -- All the segments would be null implies the concatenated string contains only delimiters
3299 -- As between every two segments (including context value. e.g. a.b.context.d) we have added one delimiter
3300 -- so after replacing context value with '' , l_segment_count (4 here) should be equal to (l_string_length(3 here) + 1)
3301 -- for a null DFF concatenated segments
3302 IF l_segment_count = (l_string_length ) THEN
3303 RETURN '';
3304 END IF;
3305
3306 RETURN l_concat_segments;
3307
3308 Exception
3309 when others then
3310 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
3311 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
3312 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
3313
3314 END Get_Concatanated_DFF_Segments;
3315
3316 END PQP_RIW_WEBADI_UTILS;