[Home] [Help]
PACKAGE BODY: APPS.EGO_PUB_WS_VS
Source
1 package body ego_pub_ws_vs as
2 /* $Header: EGOPVSB.pls 120.7.12020000.2 2012/11/26 19:55:19 vijoshi ship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 2011 Oracle Corporation , California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : EGOPVSB.pls |
10 | DESCRIPTION : This file is a packaged procedure for the |
11 | PLM metadata Value Set exploders and validation |
12 | |
13 | 01-Mar-2011 vijoshi Initial Creation |
14 +==========================================================================*/
15
16
17
18 ---
19 --- Package level variables
20 ---
21
22 ---
23 --- These variables are assigned in create_Params_valueset and used in Preprocess_Input_ValueSet
24 ---
25 g_mode VARCHAR2(100) := NULL; -- this is used to store the mode of invocation batch/list for use between procedures
26 g_batch_id NUMBER := NULL; -- if invocation mode is BATCH batch_id is stored here
27
28
29 PROCEDURE debug ( p_index IN NUMBER, p_msg IN VARCHAR2)
30 IS
31 BEGIN
32
33 DBMS_OUTPUT.PUT_LINE(p_index||'=>'||p_msg);
34
35 EXCEPTION
36 WHEN OTHERS THEN
37 DBMS_OUTPUT.PUT_LINE('Unknown exception =>'||SQLERRM);
38 END debug;
39
40 ---
41 --- This function returns the next input identifier to be used
42 --- while inserting error records in EGO_PUB_WS_INPUT_IDENTIFIERS
43 --- the id used as input_id should be unique per session
44 ---
45
46 FUNCTION Get_Max_Input_Identifier ( p_session_id IN NUMBER)
47 RETURN NUMBER
48 IS
49 l_index NUMBER;
50 l_proc_name VARCHAR2(50) := 'Get_Max_Input_Identifier=>';
51 BEGIN
52
53 SELECT Nvl(Max(INPUT_ID),0) + 1
54 INTO l_index
55 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
56 WHERE session_id = p_session_id;
57
58 RETURN l_index;
59 EXCEPTION
60 WHEN OTHERS THEN
61 debug(1, 'Unexpected exception in '||l_proc_name||SQLERRM);
62 END Get_Max_Input_Identifier;
63
64
65
66 ---
67 --- This function gets the effective version for a
68 --- value set if none is found or is a non-versioned VS
69 --- then NULL is returned
70 --- the current effective version is returned
71
72 FUNCTION Get_Current_Effective_Version ( p_vs_id IN NUMBER
73 )
74 RETURN NUMBER
75 IS
76 l_version ego_flex_valueset_version_b.version_seq_id%type;
77 l_proc_name VARCHAR2(50) := 'get_current_effective_version=>';
78 BEGIN
79 debug (1, l_proc_name||'Start');
80
81 SELECT efvvb.version_seq_id
82 INTO l_version
83 FROM EGO_FLEX_VALUESET_VERSION_B efvvb
84 WHERE NVL(efvvb.end_active_date, sysdate) >= SYSDATE
85 AND efvvb.start_active_date <= SYSDATE
86 AND efvvb.FLEX_VALUE_SET_ID = p_vs_id
87 AND efvvb.version_seq_id > 0;
88
89 debug (2, l_proc_name||'version seq id=>'||l_version);
90 debug (3, l_proc_name||'End');
91
92 return l_version;
93
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 debug(4, 'No current effective version found for VS id=>'||p_vs_id);
97 RETURN NULL;
98 WHEN OTHERS THEN
99 debug(5, 'Unexpected exception in '||l_proc_name||SQLERRM);
100 END Get_Current_Effective_Version;
101
102 /*
103 Validates the provided value set id or name and returns TRUE if they are
104 valid. If the id is provided, the name is derived and returned in x_vs_name.
105 To validate the value set name, the attribute p_vs_id must be null.
106 */
107 FUNCTION Validate_Value_Set (p_session_id IN NUMBER,
108 p_vs_id IN VARCHAR2,
109 p_vs_name IN VARCHAR2,
110 p_vs_version IN VARCHAR2,
111 x_vs_id OUT NOCOPY NUMBER,
112 x_vs_name OUT NOCOPY VARCHAR2,
113 x_vs_version OUT NOCOPY VARCHAR2
114 )
115 RETURN BOOLEAN
116 IS
117
118 l_index NUMBER;
119 l_vs_id VARCHAR2(150);
120 l_vs_name VARCHAR2(150);
121 l_vs_version VARCHAR2(150);
122 l_proc_name VARCHAR2(50) := 'Validate_Value_Set=>';
123
124 BEGIN
125 debug(1, l_proc_name||'start');
126 l_vs_id := p_vs_id;
127 l_vs_name := p_vs_name;
128 l_vs_version := p_vs_version;
129
130 IF p_vs_id = '?' OR p_vs_id IS NULL OR p_vs_id = '-1' THEN
131 l_vs_id := NULL;
132 END IF;
133
134 IF p_vs_name = '?' OR p_vs_name IS NULL THEN
135 l_vs_name := NULL;
136 END IF;
137
138 IF p_vs_version = '?' OR p_vs_version = '-1' OR p_vs_version IS NULL THEN
139 l_vs_version := NULL;
140 END IF;
141
142 IF l_vs_id IS NULL AND l_vs_name IS NULL THEN
143 RETURN FALSE;
144 END IF;
145
146 IF l_vs_id IS NOT NULL THEN
147 BEGIN
148 SELECT evsv.value_set_name
149 INTO x_vs_name
150 FROM EGO_VALUE_SETS_V evsv
151 WHERE evsv.value_set_id = to_number(l_vs_id);
152
153 x_vs_id := l_vs_id;
154 debug(1, l_proc_name||'id is valid');
155 EXCEPTION
156 WHEN NO_DATA_FOUND THEN
157 l_index := get_max_input_identifier(p_session_id);
158 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
159 p_input_id => l_index,
160 p_param_name => 'ValueSetId',
161 p_param_value => l_vs_id
162 );
163
164 EGO_PUB_WS_UTIL.Log_Error( p_session_id => p_session_id,
165 p_input_id => l_index,
166 p_err_code => 'EGO_INVALID_VS_ID',
167 p_err_message => 'Invalid Value Set Id');
168
169 debug(1, l_proc_name||'Invalid value set id');
170 RETURN FALSE;
171 END;
172 END IF;
173
174 IF l_vs_id IS NULL AND l_vs_name IS NOT NULL THEN
175 debug(1, l_proc_name||'validating name');
176 <<name_validation>>
177 BEGIN
178 SELECT evsv.value_set_id
179 INTO l_vs_id
180 FROM EGO_VALUE_SETS_V evsv
181 WHERE evsv.value_set_name = l_vs_name;
182 x_vs_id := l_vs_id;
183 x_vs_name := l_vs_name;
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 l_index := get_max_input_identifier(p_session_id);
187 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
188 p_input_id => l_index,
189 p_param_name => 'ValueSetName',
190 p_param_value => l_vs_name
191 );
192
193 EGO_PUB_WS_UTIL.Log_Error( p_session_id => p_session_id,
194 p_input_id => l_index,
195 p_err_code => 'EGO_INVALID_VS_NAME',
196 p_err_message => 'Invalid Value Set Name');
197 debug(1, l_proc_name||'Invalid value set name');
198 RETURN FALSE;
199 END name_validation;
200 END IF;
201
202 --- This check will not be reached since name is
203 --- overwriten by the passed id
204 --- Check if the id passed is matching the id
205 --- derived from the name
206 --- in batch mode p_vs_name is NULL and name id combo need not
207 --- be validated
208 ---
209 IF l_vs_id IS NOT NULL
210 AND
211 p_vs_name IS NOT NULL
212 AND
213 p_vs_id <> l_vs_id THEN
214 l_index := get_max_input_identifier(p_session_id);
215
216 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
217 p_input_id => l_index,
218 p_param_name => 'ValueSetId',
219 p_param_value => l_vs_id
220 );
221
222 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
223 p_input_id => l_index,
224 p_param_name => 'ValueSetName',
225 p_param_value => l_vs_name
226 );
227
228
229 EGO_PUB_WS_UTIL.Log_Error( p_session_id => p_session_id,
230 p_input_id => l_index,
231 p_err_code => 'EGO_INVALID_VS_ID_COMBO',
232 p_err_message => 'Invalid Value Set Id name combination'
233 );
234 debug(1, l_proc_name||'id name mismatch');
235 RETURN FALSE;
236 END IF;
237
238 IF l_vs_version IS NOT NULL THEN
239 <<Version_Validation>>
240 BEGIN
241 SELECT efvvb.version_seq_id
242 INTO x_vs_version
243 FROM EGO_flex_VALUESET_VERSION_B efvvb
244 where efvvb.FLEX_VALUE_SET_ID = x_vs_id
245 AND efvvb.version_seq_id = l_vs_version;
246
247 debug(1, l_proc_name||'version retrieved=>'||x_vs_version||'#VALID');
248
249 EXCEPTION
250 WHEN NO_DATA_FOUND THEN
251
252 l_index := get_max_input_identifier(p_session_id);
253
254 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
255 p_input_id => l_index,
256 p_param_name => 'ValueSetId',
257 p_param_value => l_vs_id
258 );
259
260 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
261 p_input_id => l_index,
262 p_param_name => 'ValueSetVersion',
263 p_param_value => l_vs_version
264 );
265
266
267 EGO_PUB_WS_UTIL.Log_Error( p_session_id => p_session_id,
268 p_input_id => l_index,
269 p_err_code => 'EGO_INVALID_VS_VERSION',
270 p_err_message => 'Invalid Value Set Version'
271 );
272 debug(1, l_proc_name||'invalid version provided');
273 RETURN FALSE;
274
275 WHEN OTHERS THEN
276 debug(1, 'Unexpected exception validating version in '||l_proc_name||SQLERRM);
277 RETURN FALSE;
278 END Version_Validation;
279
280 ELSIF l_vs_version IS NULL THEN
281 --- derive the current effective version
282 ---
283 x_vs_version := Get_Current_Effective_Version ( l_vs_id);
284 debug(1, l_proc_name||'current effective version retrieved=>'||x_vs_version);
285
286 END IF;
287
288 RETURN TRUE;
289 debug(1, l_proc_name||'end');
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 debug(1, 'Unexpected exception in '||l_proc_name||SQLERRM);
294 RETURN FALSE;
295 END Validate_Value_Set;
296
297
298
299 PROCEDURE Write_Derived_Entites_ToBatFwk( p_session_id IN NUMBER,
300 p_batch_id IN NUMBER
301 )
302 IS
303 l_proc_name VARCHAR2(50) := 'Write_Derived_Entites_ToBatFwk=>';
304
305 l_batch_id NUMBER := p_batch_id;
306 v_index NUMBER;
307 v_count NUMBER;
308 l_entity_id NUMBER;
309 batch_entity_rec EGO_PUB_FWK_PK.TBL_OF_BAT_ENT_OBJ_TYPE;
310
311 x_return_status VARCHAR2(1);
312 x_msg_count NUMBER;
313 x_msg_data VARCHAR2(500);
314
315 -- For exploded VS entities parent sequence id is not null
316 -- and ref_6_value is not null
317 --
318
319 CURSOR c_odi_end_vs(l_session_id NUMBER) IS
320 SELECT PK1_VALUE,
321 PK2_VALUE
322 FROM EGO_PUB_WS_ENTITIES
323 WHERE SESSION_ID = l_session_id
324 AND ENTITY_TYPE = 'VS'
325 AND NVL(USER_ENTERED, 'N') = 'N'
326 AND PARENT_SEQUENCE_ID IS NOT NULL
327 AND REF6_VALUE IS NOT NULL ;
328
329
330
331 BEGIN
332 debug(1,l_proc_name||'Start');
333
334
335 IF (l_batch_id > -1 ) THEN
336 v_index := 1;
337
338 SELECT entity_id
339 INTO l_entity_id
340 FROM EGO_PUB_BAT_ENTS_B
341 WHERE ENTITY_NAME='Value Set';
342
343 FOR k IN c_odi_end_vs(p_session_id)
344 LOOP
345 SELECT COUNT(*) INTO v_count
346 FROM EGO_PUB_BAT_ENT_OBJS_V
347 WHERE BATCH_ID = l_batch_id
348 AND PK1_VALUE = k.pk1_value
349 AND NVL(PK2_VALUE, 'PK2_VALUEXYZ') = NVL(k.pk2_value, 'PK2_VALUEXYZ');
350
351 IF (v_count = 0) THEN
352 -- Prepare the record only for derived entities
353 batch_entity_rec(v_index).batch_id := l_batch_id;
354 batch_entity_rec(v_index).pk1_value := k.pk1_value;
355 batch_entity_rec(v_index).pk2_value := k.pk2_value;
356 batch_entity_rec(v_index).pk3_value := NULL;
357 batch_entity_rec(v_index).pk4_value := NULL;
358 batch_entity_rec(v_index).pk5_value := NULL;
359 batch_entity_rec(v_index).user_entered := 'N';
360 batch_entity_rec(v_index).entity_type_id := l_entity_id;
361
362 v_index := v_index + 1;
363
364 END IF;
365 END LOOP; -- end of loop k
366
367 -- Calling the Below API for all the derived entities at a time, i.e in bulk
368
369 EGO_PUB_FWK_PK.add_derived_entities(batch_entity_rec,x_return_status,x_msg_count,x_msg_data);
370
371 IF (x_return_status <> 'S') THEN
372 debug(1,l_proc_name||' x_return_status'||x_return_status);
373 debug(1,l_proc_name||' x_msg_count'||x_msg_count);
374 debug(1,l_proc_name||' x_msg_data'||x_msg_data);
375 END IF;
376 END IF; -- end of if (l_batch_id > -1 and x_return_status = 'S')
377
378
379
380 debug(1,l_proc_name||'End');
381 EXCEPTION
382 WHEN OTHERS THEN
383 debug(1,l_proc_name||' Unknown exception=>'||SQLERRM);
384 END Write_Derived_Entites_ToBatFwk;
385
386
387
388 PROCEDURE Explode_Value_Set ( p_session_id IN NUMBER)
389 IS
390 l_proc_name VARCHAR2(50) := 'Explode_Value_Set=>';
391 l_web_service_name VARCHAR2(100);
392 l_child_exists_in_entities BOOLEAN := FALSE;
393 l_mode VARCHAR2(100):= NULL;
394 l_batch_id NUMBER := NULL;
395
396 CURSOR cur_get_vs_to_explode
397 IS
398 SELECT EPWE.pk1_value vs_id
399 ,EPWE.ref1_value vs_name
400 ,EPWE.sequence_id sequence_id
401 ,VS_REL.value_set_id child_vs_id
402 ,FFVS.flex_value_set_name child_vs_name
403 FROM EGO_PUB_WS_ENTITIES EPWE
404 ,EGO_VALUE_SET_EXT VS_REL
405 ,FND_FLEX_VALUE_SETS FFVS
406 WHERE EPWE.session_id = p_session_id
407 AND EPWE.entity_type = G_ENTITY_TYPE_VS
408 AND EPWE.parent_sequence_id IS NULL
409 AND EPWE.pk1_value = VS_REL.parent_value_set_id
410 AND VS_REL.value_set_id = FFVS.flex_value_set_id;
411
412 CURSOR cur_fetch_vs_frm_entity ( p_vs_id NUMBER)
413 IS
414 SELECT 1
415 FROM EGO_PUB_WS_ENTITIES EPWE
416 WHERE EPWE.session_id = p_session_id
417 AND EPWE.entity_type = G_ENTITY_TYPE_VS
418 AND EPWE.parent_sequence_id IS NULL
419 AND EPWE.pk1_value = p_vs_id
420 ;
421
422
423 BEGIN
424 debug(1,l_proc_name||'start');
425
426 SELECT WEB_SERVICE_NAME
427 INTO l_web_service_name
428 FROM EGO_PUB_WS_PARAMS
429 WHERE SESSION_ID = p_session_id;
430 debug(1,l_proc_name||'derived web service name=>'||l_web_service_name);
431
432
433 ---
434 --- identify web service invocation mode
435 ---
436 l_mode := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'MODE', NULL, NULL);
437 debug(1, l_proc_name||'Finish fetch mode=>'||l_mode);
438
439 l_batch_id := NULL;
440 IF l_mode = 'BATCH' THEN
441
442 SELECT numeric_value
443 INTO l_batch_id
444 FROM EGO_PUB_WS_CONFIG
445 WHERE PARAMETER_NAME = 'BATCH_ID'
446 AND session_id = p_session_id;
447
448 debug(1, l_proc_name||'Finish fetch batch id=>'||l_batch_id);
449 END IF;
450
451 ---
452 --- Initialize FND security
453 ---
454 EGO_PUB_WS_UTIL.Init_Security(p_session_id, l_web_service_name);
455 debug(2,l_proc_name||'Check access and initialize security');
456
457
458 FOR rec_get_vs_to_explode IN cur_get_vs_to_explode
459 LOOP
460
461 Insert_VS_Entity ( p_session_id => p_session_id
462 ,p_vs_id => rec_get_vs_to_explode.child_vs_id
463 ,p_vs_name => rec_get_vs_to_explode.child_vs_name
464 ,p_vs_version => NULL
465 ,p_user_entered_flag => 'N'
466 );
467
468 /**
469 -- Check if the child value set
470 -- exists in the entity table
471 --
472 l_child_exists_in_entities := FALSE;
473 FOR rec_cur_fetch_vs_frm_entity in cur_fetch_vs_frm_entity ( rec_get_vs_to_explode.child_vs_id)
474 LOOP
475 l_child_exists_in_entities := TRUE;
476 END LOOP;
477
478 debug(2,l_proc_name||'Checking if the child exists in entities=>'||CASE l_child_exists_in_entities WHEN TRUE THEN 'TRUE'
479 ELSE 'FALSE' END);
480
481 IF NOT l_child_exists_in_entities THEN
482
483 INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
484 ODI_SESSION_ID,
485 SEQUENCE_ID,
486 PARENT_SEQUENCE_ID,
487 ENTITY_TYPE,
488 PK1_VALUE,
489 PK2_VALUE,
490 PK3_VALUE,
491 PK4_VALUE,
492 PK5_VALUE,
493 REF1_VALUE,
494 REF2_VALUE,
495 REF3_VALUE,
496 REF4_VALUE,
497 REF5_VALUE,
498 REF6_VALUE,
499 REF7_VALUE,
500 REF8_VALUE,
501 REF9_VALUE,
502 REF10_VALUE,
503 CREATION_DATE,
504 CREATED_BY,
505 USER_ENTERED)
506 VALUES( p_session_id,
507 p_session_id,
508 EGO_PUB_WS_ENTITIES_S.nextval,
509 rec_get_vs_to_explode.sequence_id,
510 G_ENTITY_TYPE_VS,
511 rec_get_vs_to_explode.child_vs_id,
512 NULL,
513 NULL,
514 NULL,
515 NULL,
516 rec_get_vs_to_explode.child_vs_name,
517 NULL,
518 NULL,
519 NULL,
520 NULL,
521 G_ENTITY_TYPE_CHILD_VS,
522 NULL,
523 NULL,
524 NULL,
525 NULL,
526 SYSDATE,
527 0,
528 'N'
529 );
530
531 debug(2,l_proc_name||'Inserted child vs entity');
532 END IF;
533 **/
534 END LOOP;
535
536 ---
537 --- If in batch mode then the derived entities have to be
538 --- written back to the batch FWK tables
539 ---
540
541 IF l_mode = 'BATCH' THEN
542
543 Write_Derived_Entites_ToBatFwk( p_session_id => p_session_id,
544 p_batch_id => l_batch_id
545 );
546
547 debug(1,l_proc_name||'end');
548 END IF;
549
550 EXCEPTION
551 WHEN OTHERS THEN
552 debug(1,l_proc_name||' Unknown exception=>'||SQLERRM);
553 end Explode_Value_Set;
554
555
556
557
558
559
560
561 PROCEDURE Create_Params_Value_Set(p_session_id IN NUMBER)
562 IS
563
564 l_web_service_name VARCHAR2(100);
565 l_mode VARCHAR2(10);
566 l_batch_id NUMBER ;
567 l_config_option VARCHAR2(100);
568 l_language_search_str VARCHAR2(1000);
569
570 --array to store XML path expressions to retrieve single-value params
571 l_xpath_expr EGO_PUB_WS_UTIL.xpath_expr_array_type;
572
573 --array to store single_value parameter names
574 l_param_names EGO_PUB_WS_UTIL.parameter_name_array_type;
575 l_proc_name VARCHAR2(50) := 'Create_Params_Value_Set=>';
576 l_trigger_import VARCHAR2(1) := NULL;
577 l_return_payload VARCHAR2(10) := NULL;
578 l_explode_child_vs VARCHAR2(10) := NULL;
579 l_publish_sync VARCHAR2(10) := NULL;
580
581
582 BEGIN
583 debug(0, l_proc_name||'start session id=>'||p_session_id);
584
585
586
587
588 --get invocation mode for the web service
589 /*
590 SELECT CHAR_VALUE
591 INTO l_mode
592 FROM EGO_PUB_WS_CONFIG
593 WHERE PARAMETER_NAME = 'MODE'
594 AND session_id = p_session_id;
595 */
596
597 l_mode := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'MODE', NULL, NULL);
598 g_mode := l_mode;
599 debug(1, l_proc_name||'Finish fetch mode=>'||l_mode);
600
601 --get invocation mode for the web service
602 SELECT WEB_SERVICE_NAME
603 INTO l_web_service_name
604 FROM EGO_PUB_WS_PARAMS
605 WHERE SESSION_ID = p_session_id;
606 debug(2, l_proc_name||'Finish fetch WS name=>'||l_web_service_name);
607
608 IF l_mode = 'BATCH' THEN
609
610 SELECT numeric_value
611 INTO l_batch_id
612 FROM EGO_PUB_WS_CONFIG
613 WHERE PARAMETER_NAME = 'BATCH_ID'
614 AND session_id = p_session_id;
615
616 g_batch_id := l_batch_id;
617
618 debug(3, l_proc_name||'Finish fetch batch id=>'||l_batch_id);
619 ELSE
620 l_batch_id := NULL;
621 g_batch_id := l_batch_id;
622 END IF;
623
624 IF l_batch_id > -1 THEN
625
626 l_explode_child_vs := 'TRUE'; --- Default value
627
628
629 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
630 odi_session_id,
631 Parameter_Name,
632 Data_Type,
633 Char_value,
634 creation_date,
635 created_by,
636 web_service_name)
637 VALUES (p_session_id,
638 p_session_id,
639 'CHILD_VALUESETS',
640 2,
641 l_explode_child_vs,
642 sysdate,
643 fnd_global.user_id,
644 l_web_service_name);
645 debug(3, l_proc_name||'Inserted CHILD_VALUESETS=>'||l_explode_child_vs);
646
647
648 l_trigger_import := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id => p_session_id,
649 p_param_name => EGO_PUB_WS_UTIL.G_TRIGGER_IMPORT_PARAM,
650 p_batch_id => l_batch_id,
651 p_mode => 'BATCH'
652 );
653
654 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
655 odi_session_id,
656 Parameter_Name,
657 Data_Type,
658 Char_value,
659 creation_date,
660 created_by,
661 web_service_name)
662 VALUES (p_session_id,
663 p_session_id,
664 'TRIGGER_IMPORT',
665 2,
666 NVL(l_trigger_import,'N'),
667 sysdate,
668 fnd_global.user_id,
669 l_web_service_name);
670
671 debug(3, l_proc_name||'Inserted TRIGGER_IMPORT=>'||NVL(l_trigger_import,'N'));
672
673 l_publish_sync := EGO_PUB_WS_UTIL.Get_Char_Param_Value ( p_session_id => p_session_id,
674 p_param_name => EGO_PUB_WS_UTIL.G_SYNC_PARAM,
675 p_batch_id => l_batch_id,
676 p_mode => 'BATCH'
677 );
678 IF l_publish_sync = 'PUBLISH' THEN
679 l_publish_sync := 'N';
680 ELSIF l_publish_sync = 'SYNC' THEN
681 l_publish_sync := 'Y';
682 END IF;
683
684
685 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
686 odi_session_id,
687 Parameter_Name,
688 Data_Type,
689 Char_value,
690 creation_date,
691 created_by,
692 web_service_name)
693 VALUES (p_session_id,
694 p_session_id,
695 EGO_PUB_WS_UTIL.G_SYNC_PARAM,
696 2,
697 l_publish_sync,
698 sysdate,
699 fnd_global.user_id,
700 l_web_service_name);
701 debug(3, l_proc_name||'Inserted SYNC=>'||l_publish_sync);
702
703
704
705 l_return_payload := 'TRUE';
706
707 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
708 odi_session_id,
709 Parameter_Name,
710 Data_Type,
711 Char_value,
712 creation_date,
713 created_by,
714 web_service_name)
715 VALUES (p_session_id,
716 p_session_id,
717 'RETURN_PAYLOAD',
718 2,
719 l_return_payload,
720 sysdate,
721 fnd_global.user_id,
722 l_web_service_name);
723 debug(3, l_proc_name||'Inserted RETURN_PAYLOAD=>'||l_publish_sync);
724
725
726 END IF;
727
728
729 IF l_mode = 'LIST' THEN
730 --
731 --STEP ONE: RETRIEVE ALL SINGLE-VALUE CONFIGURATION PARAMETERS
732 -- AND STORE THEM IN TABLE EGO_PUB_WS_CONFIG
733 --
734
735 --initialize arrays of parameter names
736 l_param_names := EGO_PUB_WS_UTIL.parameter_name_array_type();
737 EGO_PUB_WS_UTIL.Get_Parameter_Names(l_web_service_name, l_param_names);
738 debug(3, l_proc_name||'Finish fetch param names');
739
740 l_xpath_expr := EGO_PUB_WS_UTIL.xpath_expr_array_type();
741 EGO_PUB_WS_UTIL.Get_Xpath_Expr(l_web_service_name, l_xpath_expr);
742 debug(5, l_proc_name||'Finish fetch param XML tags');
743
744 --retrieve all single-value parameters of interest from XML
745 --and store them in table EGO_PUB_WS_CONFIG
746 FOR position IN 1..l_param_names.COUNT
747 LOOP
748
749 l_config_option := upper(EGO_PUB_WS_UTIL.Get_ODI_Input_Parameter(p_session_id, l_xpath_expr(position)));
750 debug(5, l_proc_name||'counter=>'||position||'Finish fetch config option=>'||l_config_option);
751
752 --if parameter is not provided, assume a default value of 'Y' (Yes)
753
754 IF l_config_option IS NOT NULL AND l_config_option <> '?' THEN
755
756 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
757 odi_session_id,
758 Parameter_Name,
759 Data_Type,
760 Char_value,
761 creation_date,
762 created_by,
763 web_service_name)
764 VALUES (p_session_id,
765 p_session_id,
766 l_param_names(position),
767 2,
768 l_config_option,
769 sysdate,
770 fnd_global.user_id,
771 l_web_service_name);
772 END IF;
773
774 END LOOP;
775 END IF;
776
777 debug(5, l_proc_name||'start security option');
778 EGO_PUB_WS_UTIL.Create_Fnd_Security(p_session_id,
779 l_mode,
780 l_batch_id,
781 l_web_service_name
782 );
783 debug(6, l_proc_name||'Finish security option');
784 --
785 --STEP TWO: RETRIEVE ALL MULTI-VALUE CONFIGURATION PARAMETERS
786 -- AND STORE THEM IN TABLE EGO_PUB_WS_CONFIG
787 --
788
789 --- this may not be required for Value Sets
790 ---
791 --RETRIEVING LIST OF LANGUAGES
792 l_language_search_str := EGO_PUB_WS_UTIL.Get_Language_Search_Str(l_web_service_name);
793 debug(7, l_proc_name||'Finish language search'||l_language_search_str);
794
795 --Inserts language options in Config table
796 EGO_PUB_WS_UTIL.Config_Languages( p_session_id,
797 l_language_search_str,
798 l_web_service_name
799 );
800 debug(7, l_proc_name||'Finish config language');
801
802 EXCEPTION
803 --- bug 12755038 , if parallel session is trying to insert the same
804 --- record into EGO_PUB_WS_CONFIG, consume the exception , unique
805 --- index added to prevent duplicates from getting inerted which would subsequently fail
806 --- when the rows were queried later.
807 WHEN DUP_VAL_ON_INDEX then
808 NULL;
809 END Create_Params_Value_Set;
810
811
812
813 FUNCTION is_child_vs ( p_vs_id IN NUMBER)
814 RETURN BOOLEAN IS
815
816 CURSOR CUR_CHILD_VS ( p_valueset_id NUMBER)
817 IS
818 SELECT 1
819 FROM DUAL WHERE EXISTS (
820 SELECT NULL
821 FROM ego_value_set_ext evse
822 WHERE evse.value_set_id = p_valueset_id
823 );
824
825 l_is_child_vs BOOLEAN := false;
826
827 BEGIN
828
829 FOR rec_CUR_CHILD_VS IN CUR_CHILD_VS ( p_vs_id)
830 LOOP
831 l_is_child_vs := TRUE;
832 EXIT;
833 END LOOP;
834
835 RETURN l_is_child_vs;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 RETURN FALSE;
840 END is_child_vs;
841
842
843 PROCEDURE Insert_Vs_Entity ( p_session_id IN NUMBER
844 ,p_vs_id IN NUMBER
845 ,p_vs_name IN VARCHAR2
846 ,p_vs_version IN NUMBER
847 ,p_user_entered_flag IN VARCHAR2
848 )
849 IS
850 l_proc_name VARCHAR2(50) := 'Insert_Vs_Entity=>';
851 l_derived_vs_id NUMBER := NULL;
852 l_is_child BOOLEAN := FALSE;
853 l_ref6_value ego_pub_ws_entities.ref6_value%TYPE;
854 BEGIN
855
856 -- if vs id exists in input table do not insert vs id
857 BEGIN
858 SELECT epwe.pk1_value
859 INTO l_derived_vs_id
860 FROM EGO_PUB_WS_ENTITIES epwe
861 WHERE epwe.pk1_value = p_vs_id
862 AND NVL(epwe.pk2_value, '-999') = NVL(p_vs_version, -999)
863 AND epwe.session_id = p_session_id
864 AND epwe.entity_type = G_ENTITY_TYPE_VS;
865 EXCEPTION
866 WHEN No_Data_Found THEN
867 l_derived_vs_id := NULL;
868 WHEN OTHERS THEN
869 l_derived_vs_id := NULL;
870 END;
871
872 IF l_derived_vs_id IS NULL THEN
873
874 l_is_child := is_child_vs (p_vs_id);
875
876 IF l_is_child THEN
877 l_ref6_value := G_ENTITY_TYPE_CHILD_VS;
878 l_is_child := FALSE;
879 ELSE
880 l_ref6_value := NULL;
881 END IF;
882
883
884 INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
885 ODI_SESSION_ID,
886 SEQUENCE_ID,
887 PARENT_SEQUENCE_ID,
888 ENTITY_TYPE,
889 PK1_VALUE,
890 PK2_VALUE,
891 PK3_VALUE,
892 PK4_VALUE,
893 PK5_VALUE,
894 REF1_VALUE,
895 REF2_VALUE,
896 REF3_VALUE,
897 REF4_VALUE,
898 REF5_VALUE,
899 REF6_VALUE,
900 REF7_VALUE,
901 REF8_VALUE,
902 REF9_VALUE,
903 REF10_VALUE,
904 CREATION_DATE,
905 CREATED_BY,
906 USER_ENTERED)
907 VALUES( p_session_id,
908 p_session_id,
909 EGO_PUB_WS_ENTITIES_S.nextval,
910 NULL,
911 G_ENTITY_TYPE_VS,
912 p_vs_id,
913 p_vs_version,
914 NULL,
915 NULL,
916 NULL,
917 p_vs_name,
918 NULL,
919 NULL,
920 NULL,
921 NULL,
922 l_ref6_value,
923 NULL,
924 NULL,
925 NULL,
926 NULL,
927 SYSDATE,
928 0,
929 p_user_entered_flag
930 );
931
932 END IF;
933
934 EXCEPTION
935 WHEN OTHERS THEN
936 debug(1,l_proc_name||'Unknown exception SQLERRM=>'||SQLERRM);
937 END Insert_Vs_Entity;
938
939
940 PROCEDURE Create_Entities_ValueSet(p_session_id IN NUMBER)
941 IS
942
943 l_mode VARCHAR(100);
944 l_batch_id NUMBER;
945 l_vs_id fnd_flex_value_sets.flex_value_set_id%TYPE;
946 x_vs_id fnd_flex_value_sets.flex_value_set_id%TYPE;
947 l_vs_version ego_flex_valueset_version_b.version_seq_id%TYPE;
948 x_vs_version ego_flex_valueset_version_b.version_seq_id%TYPE;
949 l_seq_id NUMBER;
950 l_comma_separated_str VARCHAR2(2000);
951 l_temp_varchar1 VARCHAR2(150);
952 l_temp_varchar2 VARCHAR2(150);
953 l_temp_varchar3 VARCHAR2(150);
954 l_validate_vs BOOLEAN := TRUE; --tells if ag name has to be validated
955 l_is_vs_id_valid BOOLEAN := TRUE; --tells if ag id has to be validated
956 l_is_valid BOOLEAN := FALSE;
957 l_valueset_id fnd_flex_value_sets.flex_value_set_id%TYPE;
958 l_derived_vs_id fnd_flex_value_sets.flex_value_set_id%TYPE;
959 l_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
960
961
962
963 --tables for LIST mode
964 l_vs_id_tab dbms_sql.varchar2_table;
965 l_vs_name_tab dbms_sql.varchar2_table;
966 l_vs_vers_tab dbms_sql.varchar2_table;
967 l_count NUMBER;
968 l_vs_index NUMBER;
969
970
971
972 l_tablen BINARY_INTEGER;
973 l_tab DBMS_UTILITY.uncl_array;
974 l_proc_name VARCHAR2(50) := 'Create_Entities_ValueSet=>';
975
976 --Cursor to retrieve list of value sets from Publication Framework
977 --
978 CURSOR cur_fwk_valuesets(p_batch_id NUMBER) IS
979 SELECT pk1_value,
980 pk2_value,
981 pk3_value
982 FROM EGO_PUB_BAT_ENT_OBJS_V
983 WHERE batch_id = p_batch_id
984 AND user_entered = 'Y'
985 ;
986
987 BEGIN
988 debug(1,l_proc_name||'start');
989
990
991 --identify web service invocation mode
992 l_mode := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'MODE', NULL, NULL);
993 debug(1, l_proc_name||'Finish fetch mode=>'||l_mode);
994
995 --populate odi input table depending on mode from different data sources
996 CASE
997
998 --if mode is batch, get information from publication framework using batch_id
999 WHEN l_mode = 'BATCH' THEN
1000
1001 SELECT numeric_value
1002 INTO l_batch_id
1003 FROM EGO_PUB_WS_CONFIG
1004 WHERE PARAMETER_NAME = 'BATCH_ID'
1005 AND session_id = p_session_id;
1006
1007 --retrieving all value sets batch from publication framework entity tables
1008 --and inserting data into ODI metadata input table
1009
1010 l_vs_index :=0;
1011 IF l_batch_id IS NOT NULL THEN
1012
1013 FOR rec_cur_fwk_valuesets in cur_fwk_valuesets(l_batch_id)
1014 LOOP
1015
1016 l_vs_id := NULL;
1017 l_vs_index := l_vs_index + 1;
1018 --retrieving l_valueset_id from publication framework tables
1019 l_valueset_id := to_number(rec_cur_fwk_valuesets.pk1_value);
1020 debug(2,l_proc_name||'processing vs id # version=>'||l_valueset_id||'#'||rec_cur_fwk_valuesets.pk2_value);
1021 --validate vs id
1022 l_is_valid := Validate_Value_Set( p_session_id => p_session_id,
1023 p_vs_id => l_valueset_id,
1024 p_vs_name => NULL,
1025 p_vs_version => rec_cur_fwk_valuesets.pk2_value,
1026 x_vs_id => l_vs_id,
1027 x_vs_name => l_vs_name,
1028 x_vs_version => l_vs_version
1029 );
1030
1031 --
1032 --if vs is valid, insert to input table, otherwise generate warning
1033 --
1034 IF l_is_valid = TRUE THEN
1035
1036 Insert_Vs_Entity ( p_session_id => p_session_id
1037 ,p_vs_id => l_vs_id
1038 ,p_vs_name => l_vs_name
1039 ,p_vs_version => l_vs_version
1040 ,p_user_entered_flag => 'Y'
1041 );
1042 END IF;
1043 END LOOP;
1044 END IF;
1045
1046 WHEN l_mode = 'LIST' THEN
1047
1048
1049 /*extract list of value set ids */
1050 SELECT Decode(existsNode(vs_id, 'ValuesetIdentifier/ValueSetId'),
1051 1, Nvl(extractValue(vs_id, 'ValuesetIdentifier/ValueSetId'), -1),
1052 0, -1)
1053 BULK COLLECT INTO l_vs_id_tab
1054 FROM( SELECT Value(vsid) vs_id
1055 FROM EGO_PUB_WS_PARAMS i,
1056 TABLE(XMLSequence(
1057 extract(i.xmlcontent, 'ValuesetQueryParam/ValuesetIdentifiersList/ValuesetIdentifier') )) vsid
1058 WHERE session_id=p_session_id
1059 );
1060 debug(1,l_proc_name||' got VS ids,names and vers into collection');
1061
1062
1063 /*extract list of value set names*/
1064
1065 SELECT Decode(existsNode(vs_name, 'ValuesetIdentifier/ValueSetName'), 1, extractValue(vs_name, 'ValuesetIdentifier/ValueSetName'), 0, NULL)
1066 BULK COLLECT INTO l_vs_name_tab
1067 FROM( SELECT Value(vsname) vs_name
1068 FROM EGO_PUB_WS_PARAMS i,
1069 TABLE(XMLSequence(
1070 extract(i.xmlcontent, 'ValuesetQueryParam/ValuesetIdentifiersList/ValuesetIdentifier') )) vsname
1071 WHERE session_id=p_session_id
1072 );
1073
1074 debug(1,l_proc_name||' got VS names into collection');
1075
1076 /* extract the version sequence id */
1077 SELECT Decode(existsNode(vs_vers, 'ValuesetIdentifier/VersionSeqId'),
1078 1, extractValue(vs_vers, 'ValuesetIdentifier/VersionSeqId'),
1079 0, NULL)
1080 BULK COLLECT INTO l_vs_vers_tab
1081 FROM( SELECT Value(vsvers) vs_vers
1082 FROM EGO_PUB_WS_PARAMS i,
1083 TABLE(XMLSequence(
1084 extract(i.xmlcontent, 'ValuesetQueryParam/ValuesetIdentifiersList/ValuesetIdentifier') )) vsvers
1085 WHERE session_id=p_session_id
1086 );
1087
1088 debug(1,l_proc_name||' got Version seq ids into collection');
1089
1090 l_count := l_vs_id_tab.Count;
1091 IF l_vs_name_tab.Count > l_count THEN
1092 l_count := l_vs_name_tab.Count;
1093 END IF;
1094
1095 debug(1,l_proc_name||' set the count=>'||l_count);
1096
1097 --inserting from XML into data into ODI structure input table
1098 IF l_count > 0 THEN
1099 FOR i IN 1..l_count
1100 LOOP
1101
1102 --START VALIDATIONS-----------------------
1103
1104
1105 --validate vs id and/or name
1106 IF l_vs_id_tab.Count >= i THEN
1107 l_temp_varchar1 := l_vs_id_tab(i);
1108 ELSE
1109 l_temp_varchar1 := NULL;
1110 END IF;
1111
1112 IF l_vs_name_tab.Count >= i THEN
1113 l_temp_varchar2 := l_vs_name_tab(i);
1114 ELSE
1115 l_temp_varchar2 := NULL;
1116 END IF;
1117
1118 IF l_vs_vers_tab.count >= i THEN
1119 l_temp_varchar3 := l_vs_vers_tab(i);
1120 ELSE
1121 l_temp_varchar3 := NULL;
1122 END IF;
1123
1124 debug(2,l_proc_name||'processing vs id, name, ver =>'||l_temp_varchar1||'#'||l_temp_varchar2||'#'||l_temp_varchar3);
1125
1126 l_is_valid := Validate_Value_Set( p_session_id => p_session_id,
1127 p_vs_id => l_temp_varchar1,
1128 p_vs_name => l_temp_varchar2,
1129 p_vs_version => l_temp_varchar3,
1130 x_vs_id => l_vs_id,
1131 x_vs_name => l_vs_name,
1132 x_vs_version => l_vs_version
1133 );
1134 debug(2,l_proc_name||'validity =>'||CASE WHEN l_is_valid = TRUE THEN 'TRUE' ELSE 'FALSE' END);
1135 debug(3,l_proc_name||'entity details id, name, version=>'||l_vs_id||','||l_vs_name||','||l_vs_version);
1136
1137 --IF l_is_valid = FALSE THEN
1138 --TODO: Generate warning only and skip loop
1139 --trudave: EGO_PUB_WS_VALIDATIONS.Validate_Attribute_Group inserts the relevant errors
1140 --trudave: Do we need to insert the errors again ? I think, no need to have redundant errors.
1141 --raise_application_error(-20104, 'Invalid ag Id or Name ' || l_temp_varchar2);
1142 --END IF;
1143
1144
1145 --END VALIDATIONS-----------------------
1146
1147
1148 --if vs is valid, insert to input table, otherwise generate warning
1149 IF l_is_valid = TRUE THEN
1150
1151 Insert_VS_Entity ( p_session_id => p_session_id
1152 ,p_vs_id => l_vs_id
1153 ,p_vs_name => l_vs_name
1154 ,p_vs_version => l_vs_version
1155 ,p_user_entered_flag => 'Y'
1156 );
1157 END IF;
1158 END LOOP;
1159 END IF;
1160 END CASE;
1161
1162 debug(10, l_proc_name||'end');
1163 END Create_Entities_ValueSet;
1164
1165
1166 PROCEDURE Write_Error_Entites_ToBatFwk( p_session_id IN NUMBER,
1167 p_batch_id IN NUMBER)
1168 IS
1169 l_proc_name VARCHAR2(50) := 'Write_Error_Entites_ToBatFwk=>';
1170
1171 BEGIN
1172
1173 EGO_PUB_WS_UTIL.Write_Errors_ToBatFwk ( p_session_id => p_session_id , p_batch_id => p_batch_id);
1174
1175 END Write_Error_Entites_ToBatFwk;
1176
1177
1178
1179
1180 PROCEDURE Preprocess_Input_ValueSet(p_session_id IN NUMBER)
1181 IS
1182 l_proc_name VARCHAR2(50) := 'Preprocess_Input_ValueSet=>';
1183 l_index NUMBER := 0;
1184 l_child_vs_param VARCHAR2(100) := NULL;
1185 l_entity_count NUMBER := 0;
1186 l_param_count NUMBER := 0;
1187 BEGIN
1188 debug(0, l_proc_name||'start');
1189
1190
1191
1192
1193 ---
1194 --- if the params are already present for the session
1195 --- do not insert them, this case is true for when
1196 --- publishing to multiple target systems
1197 ---
1198 ---
1199 SELECT COUNT(1)
1200 INTO l_param_count
1201 FROM EGO_PUB_WS_CONFIG
1202 WHERE SESSION_ID = p_session_id
1203 AND PARAMETER_NAME NOT IN('ODI_SESSION_ID', 'SYSTEM_CODE', 'MODE', 'BATCH_ID');
1204
1205
1206 IF l_param_count = 0 THEN
1207 debug(1, l_proc_name||'call Create_Params_Value_Set');
1208
1209 --Create Input parameters for ODI
1210 Create_Params_Value_Set(p_session_id);
1211
1212 debug(2, l_proc_name||'finish Create_Params_Value_Set');
1213 END IF;
1214
1215
1216
1217 SELECT count(1)
1218 INTO l_entity_count
1219 from EGO_PUB_WS_ENTITIES
1220 WHERE session_id = p_session_id
1221 AND ENTITY_TYPE = G_ENTITY_TYPE_VS;
1222
1223 IF l_entity_count = 0 THEN
1224 debug(3, l_proc_name||'call Create_Entities_ValueSet');
1225
1226 --Create ODI Input table containing entities to process
1227 Create_Entities_ValueSet(p_session_id);
1228
1229 debug(4, l_proc_name||'finish Create_Entities_ValueSet');
1230 END IF;
1231
1232
1233
1234 debug(5, l_proc_name||'call fetch CHILD_VALUESETS batch, mode=>'||g_batch_id||','||g_mode);
1235
1236 l_child_vs_param := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'CHILD_VALUESETS', NULL, NULL);
1237 debug(1, l_proc_name||'Finish fetch CHILD_VALUESETS=>'||l_child_vs_param);
1238
1239 IF l_child_vs_param = 'TRUE' THEN
1240 ---
1241 --- Explode Value Set for all end-valuesets selected
1242 ---
1243 debug(5, l_proc_name||'call Explode_Value_Set');
1244 Explode_Value_Set(p_session_id);
1245 debug(6, l_proc_name||'Finish Explode_Value_Set');
1246
1247 END IF;
1248
1249 debug(7, l_proc_name||'end');
1250 END Preprocess_Input_ValueSet;
1251
1252 END EGO_PUB_WS_VS;