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