[Home] [Help]
PACKAGE BODY: APPS.EGO_PUB_WS_UTIL
Source
1 package body EGO_PUB_WS_UTIL AS
2 /* $Header: EGOPUTLB.pls 120.12 2011/07/26 05:04:20 trudave noship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : EGOPUTLB.pls |
10 | DESCRIPTION : This file contains the utility procedures needed for |
11 | all entities involved in Metadata Sync |
12 | Created By : TRUDAVE |
13 | |
14 +==========================================================================*/
15
16 --for a session id returns the Numeric configuration parameter value
17 --for the provided parameter name
18 --from ego_pub_ws_config table - For List mode
19 --from ego_pub_bat_params_b table - For Batch mode
20
21 FUNCTION Get_Numeric_Param_Value( p_session_id IN NUMBER,
22 p_param_name IN VARCHAR2,
23 p_batch_id IN NUMBER DEFAULT NULL,
24 p_mode IN VARCHAR2 DEFAULT NULL)
25 RETURN NUMBER
26 IS
27
28 x_param_value NUMBER := NULL;
29 l_param_name VARCHAR2(100) := p_param_name;
30 l_batch_id NUMBER := p_batch_id;
31 l_mode VARCHAR2(100) := p_mode;
32
33 BEGIN
34 IF l_mode IS NULL THEN
35 SELECT NUMERIC_VALUE
36 INTO x_param_value
37 FROM EGO_PUB_WS_CONFIG
38 WHERE PARAMETER_NAME = l_param_name
39 AND SESSION_ID = p_session_id;
40 ELSIF l_mode = 'BATCH' THEN
41 SELECT NUMERIC_VALUE
42 INTO x_param_value
43 FROM EGO_PUB_BAT_PARAMS_B
44 WHERE PARAMETER_NAME = l_param_name
45 AND DATA_TYPE = 1
46 AND TYPE_ID = l_batch_id;
47 END IF;
48
49 RETURN x_param_value;
50
51 END;
52
53 --for a session id returns the char configuration parameter value
54 --for the provided parameter name
55 --from ego_pub_ws_config table - For List mode
56 --from ego_pub_bat_params_b table - For Batch mode
57
58
59 FUNCTION Get_Char_Param_Value(p_session_id IN NUMBER,
60 p_param_name IN VARCHAR2,
61 p_batch_id IN NUMBER DEFAULT NULL,
62 p_mode IN VARCHAR2 DEFAULT NULL)
63 RETURN VARCHAR2
64 IS
65
66 x_param_value VARCHAR2(2000) := NULL;
67 l_param_name VARCHAR2(100) := p_param_name;
68 l_batch_id NUMBER := p_batch_id;
69 l_mode VARCHAR2(100) := p_mode;
70
71 BEGIN
72
73 -- dbms_output.put_line('Get_Char_Param_Value');
74 -- dbms_output.put_line('p_session_id=>'||p_session_id);
75 -- dbms_output.put_line('p_param_name=>'||p_param_name);
76 -- dbms_output.put_line('p_batch_id=>'||p_batch_id);
77 -- dbms_output.put_line('p_mode=>'||Nvl(p_mode, 'IS NULL'));
78
79 IF l_mode = 'BATCH' THEN
80 SELECT CHAR_VALUE
81 INTO x_param_value
82 FROM EGO_PUB_BAT_PARAMS_B
83 WHERE PARAMETER_NAME = l_param_name
84 AND DATA_TYPE = 2
85 AND TYPE_ID = l_batch_id;
86 ELSE
87 SELECT CHAR_VALUE
88 INTO x_param_value
89 FROM EGO_PUB_WS_CONFIG
90 WHERE PARAMETER_NAME = l_param_name
91 AND SESSION_ID = p_session_id;
92 END IF;
93
94 RETURN x_param_value;
95
96 END;
97
98 --for a session id returns the date configuration parameter value
99 --for the provided parameter name
100 --from ego_pub_ws_config table - For List mode
101 --from ego_pub_bat_params_b table - For Batch mode
102
103
104 FUNCTION Get_Char_Date_Value( p_session_id IN NUMBER,
105 p_param_name IN VARCHAR2,
106 p_batch_id IN NUMBER DEFAULT NULL,
107 p_mode IN VARCHAR2 DEFAULT NULL)
108 RETURN DATE
109 IS
110
111 x_param_value DATE := NULL;
112 l_param_name VARCHAR2(100) := p_param_name;
113 l_batch_id NUMBER := p_batch_id;
114 l_mode VARCHAR2(100) := p_mode;
115
116 BEGIN
117
118 IF l_mode IS NULL THEN
119 SELECT DATE_VALUE
120 INTO x_param_value
121 FROM EGO_PUB_WS_CONFIG
122 WHERE PARAMETER_NAME = l_param_name
123 AND SESSION_ID = p_session_id;
124 ELSIF l_mode = 'BATCH' THEN
125 SELECT DATE_VALUE
126 INTO x_param_value
127 FROM EGO_PUB_BAT_PARAMS_B
128 WHERE PARAMETER_NAME = l_param_name
129 AND DATA_TYPE = 3
130 AND TYPE_ID = l_batch_id;
131 END IF;
132
133 RETURN x_param_value;
134
135 END;
136
137 --returns the batch search string for the entity
138 --ODI scenario. The Batch Search String is needed to find out the
139 --invocation mode.
140
141 FUNCTION Get_Batch_Search_Str(p_odi_entscename IN VARCHAR2)
142 RETURN VARCHAR2
143 IS
144
145 x_batch_search_str VARCHAR2(100);
146
147 BEGIN
148 IF p_odi_entscename = 'EGO_SYNCICCDETAILS' THEN
149 x_batch_search_str := '/ICCQueryParam/BatchId';
150 ELSIF p_odi_entscename = 'EGO_SYNCAGDETAILS' THEN
151 x_batch_search_str := '/AGQueryParam/BatchId';
152 ELSIF p_odi_entscename = 'EGO_SYNCVSDETAILS' THEN
153 x_batch_search_str := '/ValuesetQueryParam/BatchId';
154 ELSE
155 --dbms_output.put_line('test');
156 null;
157 END IF;
158
159 RETURN x_batch_search_str;
160
161 END Get_Batch_Search_Str;
162
163
164 --returns the system search string for the entity
165 --ODI scenario. The System Search String is needed to find out the
166 --systems in list mode.
167
168 FUNCTION Get_System_Search_Str(p_odi_entscename IN VARCHAR2)
169 RETURN VARCHAR2
170 IS
171
172 x_system_search_str VARCHAR2(100);
173
174 BEGIN
175 IF p_odi_entscename = 'EGO_SYNCICCDETAILS' THEN
176 x_system_search_str := '/ICCQueryParam/ICCPubEntityObject/ListOfSystems/System';
177 ELSIF p_odi_entscename = 'EGO_SYNCAGDETAILS' THEN
178 x_system_search_str := '/AGQueryParam/AGPubEntityObject/ListOfSystems/System';
179 ELSIF p_odi_entscename = 'EGO_SYNCVSDETAILS' THEN
180 x_system_search_str := '/ValuesetQueryParam/ListOfSystems/System';
181 ELSE
182 --dbms_output.put_line('test');
183 null;
184 END IF;
185
186 RETURN x_system_search_str;
187
188 END Get_System_Search_Str;
189
190
191 --returns the language search string for the entity
192 --ODI scenario. The language Search String is needed to find out the
193 --languages in list mode.
194
195 FUNCTION Get_Language_Search_Str(p_odi_entscename IN VARCHAR2)
196 RETURN VARCHAR2
197 IS
198
199 x_system_search_str VARCHAR2(100);
200
201 BEGIN
202 IF p_odi_entscename = 'EGO_SYNCICCDETAILS' THEN
203 x_system_search_str := '/ICCQueryParam/ICCPubEntityObject/ListOfLanguages/Language';
204 ELSIF p_odi_entscename = 'EGO_SYNCAGDETAILS' THEN
205 x_system_search_str := '/AGQueryParam/AGPubEntityObject/ListOfLanguages/Language';
206 ELSIF p_odi_entscename = 'EGO_SYNCVSDETAILS' THEN
207 x_system_search_str := '/ValuesetQueryParam/ListOfLanguages/Language';
208 ELSE
209 --dbms_output.put_line('test');
210 null;
211 END IF;
212
213 RETURN x_system_search_str;
214
215 END Get_Language_Search_Str;
216
217
218 --Function that returns the specified ODI input parameter from table
219 --EGO_PUB_WS_PARAMS using the session_id.
220 --INPUT:
221 --p_session_id: unique identifier of input XML stored in table EGO_PUB_WS_PARAMS
222 --p_search_str: x_path expression pointing to parameter to be recovered
223 -- (e.g. '/ValuesetQueryParam/BatchId')
224 --OUTPUT:
225 --parameter value in VARCHAR or NULL if parameter specified does not exist
226
227 FUNCTION Get_ODI_Input_Parameter(p_session_id IN NUMBER, p_search_str IN VARCHAR2)
228 RETURN VARCHAR2
229 IS
230
231 x_value VARCHAR2(100);
232
233 BEGIN
234
235 select extractValue(xmlcontent, p_search_str)
236 into x_value
237 from EGO_PUB_WS_PARAMS
238 where session_id = p_session_id;
239
240 --check for parameter value not available condition generated by
241 --some web services when parameter value is not specified.
242 IF x_value = '?' THEN
243 x_value := NULL;
244 END IF;
245
246 RETURN x_value;
247
248 END Get_ODI_Input_Parameter;
249
250 --returns the mode in which the web service is being
251 --invoked. The procedure has two output parameter,
252 -- a string describing the invocation mode
253 -- batch id
254 --mode as follows:
255 --
256 -- 'BATCH' - The web service is being invoked by PIM publication
257 -- code triggered by publication framework GUI
258 -- 'LIST' - The web service is being invoked by PIM publication
259 -- code and the parameters are passed as list of items
260 -- 'NONE' - If none of the above modes were found in the payload
261
262
263 PROCEDURE Invocation_Mode( p_session_id IN NUMBER,
264 p_search_str IN VARCHAR2,
265 x_mode OUT NOCOPY VARCHAR2,
266 x_batch_id OUT NOCOPY NUMBER )
267 IS
268
269 --Local Variable
270 l_mode VARCHAR2(10) :='BATCH';
271 l_batch_id NUMBER:=-1;
272 l_exists NUMBER;
273
274 BEGIN
275
276
277 --if BatchId node exist and It has some value then we are in 'BATCH' mode
278 SELECT existsNode(xmlcontent, p_search_str)
279 INTO l_exists
280 FROM EGO_PUB_WS_PARAMS
281 WHERE session_id = p_session_id;
282
283
284 IF l_exists=1 THEN
285 /*If node exist for 'BatchId' then extractValue for BatchId'*/
286
287 -- trudave commenting following SELECT statement and replacing it with GET_ODI_Input_Parameter Function use.
288 -- trudave remove the commented following SELECT query later on
289 /*SELECT Nvl(extractValue(xmlcontent,p_search_str),-1)
290 INTO l_batch_id
291 FROM EGO_PUB_WS_PARAMS
292 WHERE session_id = p_session_id;*/
293
294 -- trudave - added following code by replacing above SELECT Statement.
295 l_batch_id := Nvl(Get_ODI_Input_Parameter(p_session_id, p_search_str),-1);
296
297 IF l_batch_id >-1 THEN
298 x_mode:= 'BATCH';
299 ELSE
300 x_mode:= 'LIST';
301 END IF;
302 x_batch_id:= l_batch_id;
303 ELSE
304 x_mode:= 'LIST';
305 x_batch_id:= l_batch_id;
306 END IF;
307
308 /*EXCEPTION
309 WHEN OTHERS THEN
310 NULL;*/
311
312 END Invocation_Mode;
313
314 PROCEDURE Get_Parameter_Names(p_web_service_name IN VARCHAR2,
315 p_param_names IN OUT NOCOPY parameter_name_array_type
316 )
317 IS
318
319 --array to store single_value parameter names
320 l_web_service_name VARCHAR2(100) := p_web_service_name;
321
322 BEGIN
323
324
325 IF l_web_service_name = 'EGO_SYNCICCDETAILS' THEN
326 --initialize arrays of parameter names
327 p_param_names := parameter_name_array_type('USERDEFATTRGRPS',
328 'VALUESETS',
329 'ICCVERSIONS',
330 'TRANSATTRS',
331 'ICCPAGES',
332 'ICCFUNCTIONS',
333 'ICCSTRUCTURE',
334 'PARENTICCS',
335 'CHILDICCS',
336 'SYNC',
337 'TRIGGER_IMPORT',
338 'RETURN_PAYLOAD'
339 );
340 END IF;
341
342
343 IF l_web_service_name = 'EGO_SYNCAGDETAILS' THEN
344 --initialize arrays of parameter names
345 p_param_names := parameter_name_array_type('VALUESETS',
346 'CHILD_VALUESETS',
347 'SYNC',
348 'TRIGGER_IMPORT',
349 'RETURN_PAYLOAD'
350 );
351 END IF;
352
353
354 IF l_web_service_name = 'EGO_SYNCVSDETAILS' THEN
355 --initialize arrays of parameter names
356 p_param_names := parameter_name_array_type('CHILD_VALUESETS',
357 'SYNC',
358 'TRIGGER_IMPORT',
359 'RETURN_PAYLOAD'
360 );
361
362 END IF;
363
364
365
366 END Get_Parameter_Names;
367
368
369 PROCEDURE Get_Xpath_Expr(p_web_service_name IN VARCHAR2,
370 p_xpath_expr IN OUT NOCOPY xpath_expr_array_type
371 )
372 IS
373
374 --array to store XML path expressions to retrieve single-value params
375 l_web_service_name VARCHAR2(100) := p_web_service_name;
376
377 BEGIN
378
379 IF l_web_service_name = 'EGO_SYNCICCDETAILS' THEN
380 --initialize arrays of parameter names
381 p_xpath_expr := xpath_expr_array_type('/ICCQueryParam/ICCPubEntityObject/UserDefAttrGrps',
382 '/ICCQueryParam/ICCPubEntityObject/Valuesets',
383 '/ICCQueryParam/ICCPubEntityObject/ICCVersions',
384 '/ICCQueryParam/ICCPubEntityObject/TransAttrs',
385 '/ICCQueryParam/ICCPubEntityObject/ICCPages',
386 '/ICCQueryParam/ICCPubEntityObject/ICCFunctions',
387 '/ICCQueryParam/ICCPubEntityObject/ICCStructure',
388 '/ICCQueryParam/ICCPubEntityObject/ParentICCs',
389 '/ICCQueryParam/ICCPubEntityObject/ChildICCs',
390 '/ICCQueryParam/ICCPubEntityObject/Sync',
391 '/ICCQueryParam/ICCPubEntityObject/TriggerImport',
392 '/ICCQueryParam/ICCPubEntityObject/ReturnPayload'
393 );
394 END IF;
395
396
397 IF l_web_service_name = 'EGO_SYNCAGDETAILS' THEN
398 --initialize arrays of parameter names
399 p_xpath_expr := xpath_expr_array_type('/AGQueryParam/AGPubEntityObject/Valuesets',
400 '/AGQueryParam/AGPubEntityObject/ChildValuesets',
401 '/AGQueryParam/AGPubEntityObject/Sync',
402 '/AGQueryParam/AGPubEntityObject/TriggerImport',
403 '/AGQueryParam/AGPubEntityObject/ReturnPayload'
404 );
405 END IF;
406
407 IF l_web_service_name = 'EGO_SYNCVSDETAILS' THEN
408 p_xpath_expr := xpath_expr_array_type(
409 '/ValuesetQueryParam/ChildValueSets',
410 '/ValuesetQueryParam/Sync',
411 '/ValuesetQueryParam/TriggerImport',
412 '/ValuesetQueryParam/ReturnPayload'
413 );
414 END IF;
415
416
417 END Get_Xpath_Expr;
418
419
420 PROCEDURE Create_Fnd_Security( p_session_id IN NUMBER,
421 p_mode IN VARCHAR2,
422 p_batch_id IN NUMBER DEFAULT NULL,
423 p_web_service_name IN VARCHAR2)
424 IS
425
426 l_mode VARCHAR2(10) := p_mode;
427 l_fnd_user_name VARCHAR2(100);
428 l_web_service_name VARCHAR2(100) := p_web_service_name;
429 l_responsibility_name VARCHAR2(200);
430 l_responsibility_appl_name VARCHAR2(200);
431 l_security_group_name VARCHAR2(200);
432 l_user_id NUMBER;
433 l_responsibility_id NUMBER;
434 l_batch_id NUMBER := p_batch_id;
435 l_application_id NUMBER;
436
437 BEGIN
438
439 /*SELECT CHAR_VALUE
440 INTO l_mode
441 FROM EGO_PUB_WS_CONFIG
442 WHERE session_id = p_session_id;*/
443
444 --RETRIEVING FND_USER_NAME, RESPONSIBILITY_NAME, RESPONSIBILITY_APPL_NAME, and
445 --SECURITY_GROUP_NAME depending on input mode
446
447 --if mode is LIST or H-MDM, get authentication information from
448 --user calling the web service. This info is stored in table
449 --EGO_PUB_WS_PARAMS and can be recovered with the session_id
450 IF Nvl(l_mode,'LIST') <> 'BATCH' THEN
451
452 --retrieving and storing FND_USER_NAME
453 SELECT fnd_user_name
454 INTO l_fnd_user_name
455 FROM EGO_PUB_WS_PARAMS
456 WHERE session_id = p_session_id;
457
458 /*INSERT INTO tr_temp (Session_id, message)
459 values (p_session_id, 'FND_USER_NAME:' || l_fnd_user_name);*/
460 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
461 odi_session_id,
462 Parameter_Name,
463 Data_Type,
464 Char_value,
465 creation_date,
466 created_by,
467 web_service_name)
468 VALUES( p_session_id,
469 p_session_id,
470 'FND_USER_NAME',
471 2,
472 l_fnd_user_name,
473 sysdate,
474 0,
475 l_web_service_name);
476
477 --retrieving and storing RESPONSIBILITY_NAME
478 SELECT responsibility_name
479 INTO l_responsibility_name
480 FROM EGO_PUB_WS_PARAMS
481 WHERE session_id = p_session_id;
482
483 /*INSERT INTO tr_temp (Session_id, message)
484 values (p_session_id, 'RESPONSIBILITY_NAME:' || l_responsibility_name);*/
485
486 INSERT INTO EGO_PUB_WS_CONFIG( session_id,
487 odi_session_id,
488 Parameter_Name,
489 Data_Type,
490 Char_value,
491 creation_date,
492 created_by,
493 web_service_name)
494 VALUES (p_session_id,
495 p_session_id,
496 'RESPONSIBILITY_NAME',
497 2,
498 l_responsibility_name,
499 sysdate,
500 0,
501 l_web_service_name);
502
503
504 --retrieving and storing RESPONSIBILITY_APPL_NAME
505 SELECT responsibility_appl_name
506 INTO l_responsibility_appl_name
507 FROM EGO_PUB_WS_PARAMS
508 WHERE session_id = p_session_id;
509
510 /*INSERT INTO tr_temp (Session_id, message)
511 values (p_session_id, 'RESPONSIBILITY_APPL_NAME:' || l_responsibility_appl_name);*/
512
513 INSERT INTO EGO_PUB_WS_CONFIG( session_id,
514 odi_session_id,
515 Parameter_Name,
516 Data_Type,
517 Char_value,
518 creation_date,
519 created_by,
520 web_service_name)
521 VALUES (p_session_id,
522 p_session_id,
523 'RESPONSIBILITY_APPL_NAME',
524 2,
525 l_responsibility_appl_name,
526 sysdate,
527 0,
528 l_web_service_name);
529
530
531 --retrieving and storing SECURITY_GROUP_NAME
532 SELECT security_group_name
533 INTO l_security_group_name
534 FROM EGO_PUB_WS_PARAMS
535 WHERE session_id = p_session_id;
536
537 /*INSERT INTO tr_temp (Session_id, message)
538 values (p_session_id, 'SECURITY_GROUP_NAME:' || l_security_group_name);*/
539
540 INSERT INTO EGO_PUB_WS_CONFIG( session_id,
541 odi_session_id,
542 Parameter_Name,
543 Data_Type,
544 Char_value,
545 creation_date,
546 created_by,
547 web_service_name)
548 VALUES (p_session_id,
549 p_session_id,
550 'SECURITY_GROUP_NAME',
551 2,
552 l_security_group_name,
553 sysdate,
554 0,
555 l_web_service_name);
556
557 --IF MODE IS BATCH, retrieve authentication data from Publication Framework tables
558 --This means the authentication information used corresponds to user who created the
559 --publication batch throught the publication UI.
560 ELSE
561
562 --retrieving user_id and responsibility
563 SELECT created_by, responsibility_id
564 INTO l_user_id, l_responsibility_id
565 FROM EGO_PUB_BAT_HDR_B
566 WHERE batch_id = l_batch_id;
567
568 --retrieving user name
569 SELECT USER_NAME
570 INTO l_fnd_user_name
571 FROM fnd_user
572 WHERE user_id = l_user_id;
573
574 /*INSERT INTO tr_temp (Session_id, message)
575 values (p_session_id, 'FND_USER_NAME:' || l_fnd_user_name);*/
576
577 --inserting user name
578 INSERT INTO EGO_PUB_WS_CONFIG( session_id,
579 odi_session_id,
580 Parameter_Name,
581 Data_Type,
582 Char_value,
583 creation_date,
584 created_by,
585 web_service_name)
586 VALUES (p_session_id,
587 p_session_id,
588 'FND_USER_NAME',
589 2,
590 l_fnd_user_name,
591 sysdate,
592 0,
593 l_web_service_name);
594
595 --retrieving responsibility name
596 SELECT responsibility_key
597 INTO l_responsibility_name
598 FROM FND_RESPONSIBILITY
599 WHERE responsibility_id = l_responsibility_id;
600
601 /*INSERT INTO tr_temp (Session_id, message)
602 values (p_session_id, 'RESPONSIBILITY_NAME:' || l_responsibility_name);*/
603
604 --inserting responsibility name
605 INSERT INTO EGO_PUB_WS_CONFIG( session_id,
606 odi_session_id,
607 Parameter_Name,
608 Data_Type,
609 Char_value,
610 creation_date,
611 created_by,
612 web_service_name)
613 VALUES (p_session_id,
614 p_session_id,
615 'RESPONSIBILITY_NAME',
616 2,
617 l_responsibility_name,
618 sysdate,
619 0,
620 l_web_service_name);
621
622 --retrieving application_id
623 SELECT application_id
624 INTO l_application_id
625 FROM FND_RESPONSIBILITY
626 WHERE responsibility_id = l_responsibility_id;
627
628 --retrieving responsibility_appl_name
629 SELECT APPLICATION_SHORT_NAME
630 INTO l_responsibility_appl_name
631 FROM FND_APPLICATION
632 WHERE application_id = l_application_id;
633
634 /*INSERT INTO tr_temp (Session_id, message)
635 values (p_session_id, 'RESPONSIBILITY_APPL_NAME:' || l_responsibility_appl_name);*/
636
637 /*INSERT INTO tr_temp (Session_id, message)
638 values (p_session_id, 'SECURITY_GROUP_NAME:' || l_security_group_name);*/
639
640 --inserting responsibility_appl_name
641 INSERT INTO EGO_PUB_WS_CONFIG( session_id,
642 odi_session_id,
643 Parameter_Name,
644 Data_Type,
645 Char_value,
646 creation_date,
647 created_by,
648 web_service_name)
649 VALUES (p_session_id,
650 p_session_id,
651 'RESPONSIBILITY_APPL_NAME',
652 2,
653 l_responsibility_appl_name,
654 sysdate,
655 0,
656 l_web_service_name);
657
658 --inserting security_group_name as NULL
659 l_security_group_name := NULL;
660 INSERT INTO EGO_PUB_WS_CONFIG( session_id,
661 odi_session_id,
662 Parameter_Name,
663 Data_Type,
664 Char_value,
665 creation_date,
666 created_by,
667 web_service_name)
668 VALUES (p_session_id,
669 p_session_id,
670 'SECURITY_GROUP_NAME',
671 2,
672 l_security_group_name,
673 sysdate,
674 0,
675 l_web_service_name);
676 END IF;
677
678 END Create_Fnd_Security;
679
680
681 FUNCTION Validate_Resp_Name(p_session_id IN NUMBER,
682 p_resp_name IN VARCHAR2,
683 x_resp_id OUT NOCOPY NUMBER) RETURN BOOLEAN
684 IS
685
686 l_index NUMBER;
687
688 BEGIN
689
690 IF p_resp_name IS NULL OR p_resp_name = '?' THEN
691 x_resp_id := NULL;
692 RETURN FALSE;
693 END IF;
694
695 --retrieving responsibility id from responsibility name
696 SELECT RESPONSIBILITY_ID
697 INTO x_resp_id
698 FROM FND_RESPONSIBILITY
699 WHERE RESPONSIBILITY_KEY = p_resp_name;
700
701 RETURN TRUE;
702
703 EXCEPTION
704 WHEN No_Data_Found THEN
705 --dbms_output.put_line(' Error : Invalid Revision Details ');
706
707 SELECT Nvl(Max(INPUT_ID),0) + 1
708 INTO l_index
709 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
710 WHERE session_id = p_session_id;
711
712 Populate_Input_Identifier(p_session_id => p_session_id,
713 p_input_id => l_index,
714 p_param_name => 'ResponsibilityName',
715 p_param_value => p_resp_name
716 );
717
718 Log_Error( p_session_id => p_session_id,
719 p_input_id => l_index,
720 p_err_code => 'EGO_INVALID_RESP_NAME',
721 p_err_message => 'Invalid Responsibility Name');
722
723 RETURN FALSE;
724 -- error OUT NOCOPY
725 WHEN OTHERS THEN
726 --dbms_output.put_line(' Error : '|| SQLERRM);
727 RETURN FALSE;
728
729 END Validate_Resp_Name;
730
731 --validates if the provided responsibility application name exists
732 FUNCTION Validate_Resp_Appl_Name(p_session_id IN NUMBER,
733 p_resp_appl_name IN VARCHAR2,
734 x_resp_appl_id OUT NOCOPY NUMBER) RETURN BOOLEAN
735 IS
736
737 l_index NUMBER;
738
739 BEGIN
740
741 IF p_resp_appl_name IS NULL OR p_resp_appl_name = '?' THEN
742 x_resp_appl_id := NULL;
743 RETURN FALSE;
744 END IF;
745
746 --retrieving application id from application name
747 SELECT APPLICATION_ID
748 INTO x_resp_appl_id
749 FROM FND_APPLICATION
750 WHERE APPLICATION_SHORT_NAME = p_resp_appl_name;
751
752 RETURN TRUE;
753
754 EXCEPTION
755 WHEN No_Data_Found THEN
756 --dbms_output.put_line(' Error : Invalid Revision Details ');
757
758 SELECT Nvl(Max(INPUT_ID),0) + 1
759 INTO l_index
760 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
761 WHERE SESSION_ID = p_session_id;
762
763 Populate_Input_Identifier( p_session_id => p_session_id,
764 p_input_id => l_index,
765 p_param_name => 'ResponsibilityApplicationName',
766 p_param_value => p_resp_appl_name
767 );
768
769 Log_Error( p_session_id => p_session_id,
770 p_input_id => l_index,
771 p_err_code => 'EGO_INVALID_RESP_APPL_NAME',
772 p_err_message => 'Invalid Responsibility Application Name');
773
774 RETURN FALSE;
775 -- error OUT NOCOPY
776 WHEN OTHERS THEN
777 --dbms_output.put_line(' Error : '|| SQLERRM);
778 RETURN FALSE;
779
780 END Validate_Resp_Appl_Name;
781
782 --validates if the provided security group name exists
783 FUNCTION Validate_Security_Group( p_session_id IN NUMBER,
784 p_sec_grp_name IN VARCHAR2,
785 x_sec_grp_id OUT NOCOPY NUMBER) RETURN BOOLEAN
786 IS
787
788 l_index NUMBER;
789
790 BEGIN
791
792 IF p_sec_grp_name IS NULL OR p_sec_grp_name = '?' THEN
793 x_sec_grp_id := NULL;
794 RETURN FALSE;
795 END IF;
796
797 --retrieving security group id from security group name
798 SELECT SECURITY_GROUP_ID
799 INTO x_sec_grp_id
800 FROM FND_SECURITY_GROUPS
801 WHERE SECURITY_GROUP_KEY = p_sec_grp_name;
802
803 RETURN TRUE;
804
805 EXCEPTION
806 WHEN No_Data_Found THEN
807 --dbms_output.put_line(' Error : Invalid Revision Details ');
808
809 SELECT Nvl(Max(INPUT_ID),0) + 1 into l_index
810 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
811 WHERE session_id = p_session_id;
812
813 Populate_Input_Identifier( p_session_id => p_session_id,
814 p_input_id => l_index,
815 p_param_name => 'SecurityGroup',
816 p_param_value => p_sec_grp_name
817 );
818
819 Log_Error( p_session_id => p_session_id,
820 p_input_id => l_index,
821 p_err_code => 'EGO_INVALID_SECURITY_GROUP',
822 p_err_message => 'Invalid Security Group');
823
824 RETURN FALSE;
825 -- error OUT NOCOPY
826 WHEN OTHERS THEN
827 --dbms_output.put_line(' Error : '|| SQLERRM);
828 RETURN FALSE;
829
830 END Validate_Security_Group;
831
832 --initializes the FND security context depending on the invokation mode.
833 --Depending on the invokation mode (e.g. BATCH, LIST), the
834 --credentials for initializing the security must be found in different places.
835 PROCEDURE Init_Security(p_session_id IN NUMBER,
836 p_web_service_name IN VARCHAR2)
837 IS
838
839 l_web_service_name VARCHAR2(100) := p_web_service_name;
840 l_application_id NUMBER;
841 l_responsibility_id NUMBER;
842 l_user_id NUMBER;
843 l_security_group_id NUMBER;
844 l_batch_id NUMBER;
845 l_user_name VARCHAR2(100);
846 l_responsibility_name VARCHAR2(100);
847 l_responsibility_appl_name VARCHAR2(100);
848 l_security_group_name VARCHAR2(100);
849 l_is_valid BOOLEAN;
850
851
852 BEGIN
853
854 --reading fnd user name
855 SELECT CHAR_VALUE
856 INTO l_user_name
857 FROM EGO_PUB_WS_CONFIG
858 WHERE SESSION_ID = p_session_id
859 AND WEB_SERVICE_NAME = l_web_service_name
860 AND parameter_name = 'FND_USER_NAME';
861
862 --retrieving user id from user name
863 SELECT USER_ID
864 INTO l_user_id
865 FROM FND_USER
866 WHERE USER_NAME = l_user_name;
867
868 --reading responsibility name
869 SELECT CHAR_VALUE
870 INTO l_responsibility_name
871 FROM EGO_PUB_WS_CONFIG
872 WHERE SESSION_ID = p_session_id
873 AND WEB_SERVICE_NAME = l_web_service_name
874 AND PARAMETER_NAME = 'RESPONSIBILITY_NAME';
875
876 --validating responsibility name
877 l_is_valid := Validate_Resp_Name( p_session_id => p_session_id,
878 p_resp_name => l_responsibility_name,
879 x_resp_id => l_responsibility_id);
880 --IF l_is_valid = FALSE THEN
881 --TODO: Handle properly
882 -- trudave: this step is already performed in validate_resp_name function
883 -- trudave: is there any need to insert the same error again by adding code here ?
884 -- raise_application_error(-20000, 'Invalid Responsibility Name');
885 --END IF;
886
887 --reading responsibility application name
888 SELECT CHAR_VALUE
889 INTO l_responsibility_appl_name
890 FROM EGO_PUB_WS_CONFIG
891 WHERE SESSION_ID = p_session_id
892 AND WEB_SERVICE_NAME = l_web_service_name
893 AND PARAMETER_NAME = 'RESPONSIBILITY_APPL_NAME';
894
895 --validating responsibility application name
896 l_is_valid := Validate_Resp_Appl_Name( p_session_id => p_session_id,
897 p_resp_appl_name => l_responsibility_appl_name,
898 x_resp_appl_id => l_application_id);
899 --IF l_is_valid = FALSE THEN
900 --TODO: Handle properly
901 -- trudave: this step is already performed in validate_resp_name function
902 -- trudave: is there any need to insert the same error again by adding code here ?
903 -- raise_application_error(-20000, 'Invalid Responsibility Application Name');
904 --END IF;
905
906 --reading security group name
907 SELECT CHAR_VALUE
908 INTO l_security_group_name
909 FROM EGO_PUB_WS_CONFIG
910 WHERE SESSION_ID = p_session_id
911 AND WEB_SERVICE_NAME = l_web_service_name
912 AND PARAMETER_NAME = 'SECURITY_GROUP_NAME';
913
914 --validating security group
915 l_is_valid := Validate_Security_Group( p_session_id => p_session_id,
916 p_sec_grp_name => l_security_group_name,
917 x_sec_grp_id => l_security_group_id);
918
919 --IF l_is_valid = FALSE THEN
920 --TODO: Raise error if provided security group is not valid
921 -- trudave: this step is already performed in validate_resp_name function
922 -- trudave: is there any need to insert the same error again by adding code here ?
923 --NULL;
924 --raise_application_error(-20000, 'security information unavailable for LIST mode');
925 --END IF;
926
927 --remove, debugging purposes only
928 /*INSERT INTO tr_temp (Session_id, message)
929 values (p_session_id, 'FND_SEC Userid: ' || l_user_id);
930 --remove, debugging purposes only
931 INSERT INTO tr_temp (Session_id, message)
932 values (p_session_id, 'FND_SEC respid: ' || l_responsibility_id);
933 --remove, debugging purposes only
934 INSERT INTO tr_temp (Session_id, message)
935 values (p_session_id, 'FND_SEC appid: ' || l_application_id);
936 */
937
938
939 --Initializing security context
940 FND_GLOBAL.APPS_INITIALIZE( USER_ID=>l_user_id,
941 RESP_ID=>l_responsibility_id,
942 RESP_APPL_ID=>l_application_id
943 );
944
945 --FND_GLOBAL.Apps_Initialize(user_id => 1006535, resp_id => 24089, resp_appl_id => 431);
946 --Dbms_Output.put_line('FND_GLOBAL.User_Id: ' || FND_GLOBAL.User_Id);
947 --Dbms_Output.put_line('FND_GLOBAL.Login_Id: ' || FND_GLOBAL.Login_Id);
948
949 END Init_Security;
950
951 -- Validates system code and system name in table HZ_ORIG_SYSTEMS_B
952 -- For invalid systems, writes parameter name and parameter value
953 -- in table EGO_PUB_WS_INPUT_IDENTIFIERS
954 -- And corresponding error code and error message in table EGO_PUB_WS_ERRORS
955
956 FUNCTION Validate_System_Info(p_session_id IN NUMBER,
957 p_system_code IN OUT NOCOPY VARCHAR2,
958 p_system_name VARCHAR2 DEFAULT NULL) RETURN BOOLEAN
959 IS
960
961 l_temp VARCHAR(100);
962 l_index NUMBER;
963
964 BEGIN
965
966 --dbms_output.put_line('Inside Validate_System_Info');
967 IF p_system_code IS NULL AND p_system_name IS NULL THEN
968 RETURN FALSE;
969 END IF;
970
971 IF p_system_code ='?' AND p_system_name ='?' THEN
972 RETURN FALSE;
973 END IF;
974
975 l_temp := p_system_code;
976
977 IF l_temp = '?' THEN
978 l_temp:= NULL;
979 END IF;
980
981 IF l_temp IS NOT NULL THEN
982 SELECT orig_system
983 INTO p_system_code
984 FROM hz_orig_systems_b where orig_system = l_temp;
985 ELSE
986 SELECT orig_system
987 INTO p_system_code
988 FROM hz_orig_systems_b where orig_system = upper(p_system_name);
989 END IF;
990
991 RETURN TRUE;
992
993 EXCEPTION
994 WHEN No_Data_Found THEN
995
996 SELECT Nvl(Max(INPUT_ID),0) + 1
997 INTO l_index
998 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
999 WHERE session_id = p_session_id;
1000
1001
1002 IF l_temp IS NOT NULL THEN
1003
1004 Populate_Input_Identifier( p_session_id => p_session_id,
1005 p_input_id => l_index,
1006 p_param_name => 'SystemCode',
1007 p_param_value => l_temp
1008 );
1009
1010 Log_Error( p_session_id => p_session_id,
1011 p_input_id => l_index,
1012 p_err_code => 'EGO_INVALID_SYSTEM_CODE',
1013 p_err_message => 'Invalid System Code');
1014
1015 ELSE
1016
1017 Populate_Input_Identifier( p_session_id => p_session_id,
1018 p_input_id => l_index,
1019 p_param_name => 'SystemName',
1020 p_param_value => p_system_name
1021 );
1022
1023 Log_Error( p_session_id => p_session_id,
1024 p_input_id => l_index,
1025 p_err_code => 'EGO_INVALID_SYSTEM_NAME',
1026 p_err_message => 'Invalid System Name');
1027
1028
1029 END IF;
1030
1031 RETURN FALSE;
1032 -- error OUT NOCOPY
1033 WHEN OTHERS THEN
1034 --dbms_output.put_line(' Error : '|| SQLERRM);
1035 RETURN FALSE;
1036
1037 END Validate_System_Info;
1038
1039 --Inserts system options in EGO_PUB_WS_CONFIG table
1040 PROCEDURE Config_Systems(p_session_id IN NUMBER,
1041 p_system_search_str IN VARCHAR2,
1042 p_web_service_name VARCHAR2 DEFAULT NULL)
1043 IS
1044
1045 l_mode VARCHAR2(10);
1046 l_system_code_tab dbms_sql.varchar2_table;
1047 l_system_name_tab dbms_sql.varchar2_table;
1048
1049 l_systemcode_xpath VARCHAR2(200):=p_system_search_str||'/SystemCode';
1050 l_systemname_xpath VARCHAR2(200):=p_system_search_str||'/SystemName';
1051 l_codes_provided VARCHAR(3):='Y';
1052 l_count NUMBER;
1053 l_is_valid BOOLEAN;
1054 l_valid_count NUMBER := 0;
1055 l_temp_code VARCHAR(200);
1056 l_temp_name VARCHAR(200);
1057 l_index NUMBER;
1058 l_batch_id NUMBER;
1059
1060 BEGIN
1061
1062 SELECT CHAR_VALUE
1063 INTO l_mode
1064 FROM EGO_PUB_WS_CONFIG
1065 WHERE PARAMETER_NAME = 'MODE'
1066 AND session_id = p_session_id;
1067
1068 IF l_mode = 'LIST' THEN
1069
1070 --extracting system Codes
1071 SELECT extractValue(system_code, '/SystemCode')
1072 BULK COLLECT INTO l_system_code_tab
1073 FROM (SELECT Value(systemcode) system_code
1074 FROM EGO_PUB_WS_PARAMS i,
1075 TABLE(XMLSequence(
1076 extract(i.xmlcontent, l_systemcode_xpath) )) systemcode
1077 WHERE session_id=p_session_id
1078 );
1079
1080 --extracting system names
1081 SELECT extractValue(system_name, '/SystemName')
1082 BULK COLLECT INTO l_system_name_tab
1083 FROM (SELECT Value(systemname) system_name
1084 FROM EGO_PUB_WS_PARAMS i,
1085 TABLE(XMLSequence(
1086 extract(i.xmlcontent, l_systemname_xpath) )) systemname
1087 WHERE session_id=p_session_id
1088 );
1089
1090
1091 l_count := l_system_code_tab.Count;
1092 --DBMS_OUTPUT.PUT_LINE('l_count for system_code_tab');
1093 --DBMS_OUTPUT.PUT_LINE(l_count);
1094 IF l_count < l_system_name_tab.Count THEN
1095 l_count := l_system_name_tab.Count;
1096 END IF;
1097
1098 --check if all system codes provided are different from null
1099 IF l_count > 0 THEN
1100
1101 FOR i IN 1..l_count
1102 LOOP
1103
1104 IF l_system_code_tab.Count >= i THEN
1105 l_temp_code := l_system_code_tab(i);
1106 --DBMS_OUTPUT.PUT_LINE('l_temp_code from system_code_tab');
1107 --DBMS_OUTPUT.PUT_LINE(l_temp_code);
1108
1109 ELSE
1110 l_temp_code := NULL;
1111 END IF;
1112 IF l_system_name_tab.Count >= i THEN
1113 l_temp_name := l_system_name_tab(i);
1114 ELSE
1115 l_temp_name := NULL;
1116 END IF;
1117
1118 /*INSERT INTO tr_temp (Session_id, message)
1119 values (p_session_id, ' l_temp_code: ' || l_temp_code);
1120 INSERT INTO tr_temp (Session_id, message)
1121 values (p_session_id, ' l_lang_name_tab(i): ' || l_lang_name_tab(i));
1122 INSERT INTO tr_temp (Session_id, message)
1123 values (p_session_id, ' l_temp_name: ' || l_temp_name);
1124 */
1125
1126 --validating system code or system name
1127 l_is_valid := Validate_system_Info( p_session_id => p_session_id,
1128 p_system_code => l_temp_code,
1129 p_system_name => l_temp_name);
1130
1131 IF l_is_valid = TRUE THEN
1132 l_valid_count := l_valid_count + 1;
1133 l_system_code_tab(i) := l_temp_code;
1134 ELSE
1135 l_system_code_tab(i) := NULL;
1136 --TODO: Generate warning message for non-valid system code/name
1137 --in l_temp_code or l_temp_name
1138 IF l_temp_code IS NULL THEN
1139 -- write error in odi error tables
1140 SELECT Nvl(Max(INPUT_ID),0) + 1
1141 INTO l_index
1142 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
1143 WHERE session_id = p_session_id;
1144
1145 Populate_Input_Identifier( p_session_id => p_session_id,
1146 p_input_id => l_index,
1147 p_param_name => 'SystemCode',
1148 p_param_value => l_temp_code
1149 );
1150
1151 Log_Error( p_session_id => p_session_id,
1152 p_input_id => l_index,
1153 p_err_code => 'EGO_INVALID_SYSTEM_CODE',
1154 p_err_message => 'Invalid System Code'
1155 );
1156 END IF;
1157 IF l_temp_name IS NULL THEN
1158 -- write error in odi error tables
1159 SELECT Nvl(Max(INPUT_ID),0) + 1
1160 INTO l_index
1161 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
1162 WHERE session_id = p_session_id;
1163
1164 Populate_Input_Identifier( p_session_id => p_session_id,
1165 p_input_id => l_index,
1166 p_param_name => 'SystemName',
1167 p_param_value => l_temp_name
1168 );
1169
1170 Log_Error( p_session_id => p_session_id,
1171 p_input_id => l_index,
1172 p_err_code => 'EGO_INVALID_SYSTEM_NAME',
1173 p_err_message => 'Invalid System Name'
1174 );
1175 END IF;
1176 END IF;
1177
1178 END LOOP;
1179
1180 --if no valid system codes or names were found, assume none were passed
1181 IF l_valid_count = 0 THEN
1182 l_codes_provided := 'N';
1183 END IF;
1184
1185 ELSE
1186
1187 l_codes_provided := 'N';
1188
1189 END IF;
1190
1191 --Insert record into config table for parameter language
1192 IF l_codes_provided = 'Y' THEN
1193
1194 FOR i IN 1..l_count
1195 LOOP
1196
1197 IF l_system_code_tab(i) IS NOT NULL THEN
1198
1199 /*INSERT INTO tr_temp (Session_id, message)
1200 values (p_session_id, 'SYSTEM_CODE:' || l_system_code_tab(i));*/
1201
1202 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
1203 odi_session_id,
1204 Parameter_Name,
1205 Data_Type,
1206 Date_Value,
1207 Char_value,
1208 Numeric_Value,
1209 creation_date,
1210 created_by,
1211 web_service_name)
1212 VALUES (p_session_id,
1213 p_session_id,
1214 'SYSTEM_CODE',
1215 2,
1216 NULL,
1217 l_system_code_tab(i),
1218 NULL,
1219 SYSDATE,
1220 G_CURRENT_USER_ID,
1221 p_web_service_name);
1222 END IF;
1223 END LOOP;
1224 END IF;
1225
1226 ELSIF l_mode = 'BATCH' THEN
1227
1228 -- trudave - read from Batch Publication Framework System table and write to EGO_PUB_WS_CONFIG table
1229 -- trudave - as batch publication fwk tables are not exposed to users and systems selected from UI and validated
1230 -- trudave - do we need to validate system information ? , as per my view we do not need to validate systems to avoid rework
1231 -- trudave - double check is always good as well.
1232 SELECT NUMERIC_VALUE
1233 INTO l_batch_id
1234 FROM EGO_PUB_WS_CONFIG
1235 WHERE PARAMETER_NAME = 'BATCH_ID'
1236 AND session_id = p_session_id;
1237
1238 SELECT system_code
1239 BULK COLLECT INTO l_system_code_tab
1240 FROM EGO_PUB_BAT_SYSTEMS_B
1241 WHERE batch_id = l_batch_id;
1242
1243 l_count := l_system_code_tab.Count;
1244
1245 IF l_count > 0 THEN
1246 FOR i IN 1..l_count
1247 LOOP
1248
1249 IF l_system_code_tab(i) IS NOT NULL THEN
1250
1251 /*INSERT INTO tr_temp (Session_id, message)
1252 values (p_session_id, 'SYSTEM_CODE:' || l_system_code_tab(i));*/
1253
1254 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
1255 odi_session_id,
1256 Parameter_Name,
1257 Data_Type,
1258 Date_Value,
1259 Char_value,
1260 Numeric_Value,
1261 creation_date,
1262 created_by,
1263 web_service_name)
1264 VALUES (p_session_id,
1265 p_session_id,
1266 'SYSTEM_CODE',
1267 2,
1268 NULL,
1269 l_system_code_tab(i),
1270 NULL,
1271 SYSDATE,
1272 G_CURRENT_USER_ID,
1273 p_web_service_name);
1274 END IF;
1275
1276 END LOOP;
1277 END IF;
1278
1279 ELSE
1280 -- trudave
1281 -- Write errors into ODI error tables
1282
1283
1284 -- trudave following FOR LOOP is commented by me
1285 -- trudave because, if system is invalid, we need to write errors in ODI error tables and shoud not get any systems from
1286 -- trudave HZ_ORIG_SYSTEMS_B
1287
1288 /*FOR i IN (SELECT orig_system FROM HZ_ORIG_SYSTEMS_B WHERE ( ( END_DATE_ACTIVE IS NULL OR TRUNC(END_DATE_ACTIVE) >=TRUNC(SYSDATE) )
1289 AND ( TRUNC(START_DATE_ACTIVE) <= TRUNC(SYSDATE) )
1290 AND ( STATUS = 'A' )
1291 AND ( CREATED_BY_MODULE LIKE 'EGO_%' ) ) )
1292 LOOP
1293 INSERT INTO tr_temp (Session_id, message)
1294 values (p_session_id, 'SYSTEM_CODE:' || i.orig_system);
1295
1296 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
1297 odi_session_id,
1298 Parameter_Name,
1299 Data_Type,
1300 Date_Value,
1301 Char_value,
1302 Numeric_Value,
1303 creation_date,
1304 created_by,
1305 web_service_name)
1306 VALUES (p_session_id,
1307 p_odi_session_id,
1308 'SYSTEM_CODE',
1309 2,
1310 NULL,
1311 i.orig_system,
1312 NULL,
1313 SYSDATE,
1314 G_CURRENT_USER_ID,
1315 p_web_service_name);
1316 END LOOP;*/
1317
1318 SELECT Nvl(Max(INPUT_ID),0) + 1
1319 INTO l_index
1320 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
1321 WHERE session_id = p_session_id;
1322
1323 Populate_Input_Identifier( p_session_id => p_session_id,
1324 p_input_id => l_index,
1325 p_param_name => 'MODE',
1326 p_param_value => l_mode
1327 );
1328
1329 Log_Error( p_session_id => p_session_id,
1330 p_input_id => l_index,
1331 p_err_code => 'EGO_INVALID_SYNC_MODE',
1332 p_err_message => 'Invalid Mode'
1333 );
1334
1335
1336 END IF;
1337
1338
1339 END Config_Systems;
1340
1341 function Validate_Language_Info(p_session_id IN NUMBER,
1342 p_language_code IN OUT NOCOPY VARCHAR2,
1343 p_language_name VARCHAR2 DEFAULT NULL) RETURN BOOLEAN
1344 is
1345
1346 l_temp VARCHAR(100);
1347 l_index NUMBER;
1348
1349 BEGIN
1350
1351 --dbms_output.put_line('Inside Validate_Language_Info');
1352 IF p_language_code IS NULL AND p_language_name IS NULL THEN
1353 RETURN FALSE;
1354 END IF;
1355
1356 IF p_language_code ='?' AND p_language_name ='?' THEN
1357 RETURN FALSE;
1358 END IF;
1359
1360
1361 l_temp := p_language_code;
1362
1363 IF l_temp = '?' THEN
1364 l_temp:= NULL;
1365 END IF;
1366
1367 IF l_temp IS NOT NULL THEN
1368 select language_code
1369 into p_language_code
1370 from fnd_languages where language_code = l_temp;
1371 ELSE
1372 select language_code
1373 into p_language_code
1374 from fnd_languages where nls_language = upper(p_language_name);
1375 END IF;
1376
1377 RETURN TRUE;
1378
1379 EXCEPTION
1380 WHEN No_Data_Found THEN
1381 --dbms_output.put_line(' Error : Invalid Language Code ');
1382
1383 SELECT Nvl(Max(INPUT_ID),0) + 1 into l_index
1384 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
1385 WHERE session_id = p_session_id;
1386
1387
1388 IF l_temp IS NOT NULL THEN
1389
1390 Populate_Input_Identifier( p_session_id => p_session_id,
1391 p_input_id => l_index,
1392 p_param_name => 'LanguageCode',
1393 p_param_value => l_temp
1394 );
1395
1396 Log_Error( p_session_id => p_session_id,
1397 p_input_id => l_index,
1398 p_err_code => 'EGO_INVALID_LANGUAGE_CODE',
1399 p_err_message => 'Invalid Language Code');
1400
1401 ELSE
1402
1403 Populate_Input_Identifier( p_session_id => p_session_id,
1404 p_input_id => l_index,
1405 p_param_name => 'LanguageName',
1406 p_param_value => p_language_name
1407 );
1408
1409 Log_Error( p_session_id => p_session_id,
1410 p_input_id => l_index,
1411 p_err_code => 'EGO_INVALID_LANGUAGE_NAME',
1412 p_err_message => 'Invalid Language Name');
1413
1414
1415 END IF;
1416
1417 RETURN FALSE;
1418 -- error OUT NOCOPY
1419 WHEN OTHERS THEN
1420 --dbms_output.put_line(' Error : '|| SQLERRM);
1421 RETURN FALSE;
1422
1423 END Validate_Language_Info;
1424
1425 --Inserts language options in EGO_PUB_WS_CONFIG table
1426 PROCEDURE Config_Languages(p_session_id IN NUMBER,
1427 p_lang_search_str IN VARCHAR2,
1428 p_web_service_name IN VARCHAR2 DEFAULT NULL)
1429 IS
1430
1431 l_lang_code_tab dbms_sql.varchar2_table;
1432 l_lang_name_tab dbms_sql.varchar2_table;
1433
1434 l_langcode_xpath VARCHAR2(200):=p_lang_search_str||'/LanguageCode';
1435 l_langname_xpath VARCHAR2(200):=p_lang_search_str||'/LanguageName';
1436 l_codes_provided VARCHAR(3):='Y';
1437 l_count NUMBER;
1438 l_is_valid BOOLEAN;
1439 l_valid_count NUMBER := 0;
1440 l_temp_code VARCHAR(200);
1441 l_temp_name VARCHAR(200);
1442 l_index NUMBER;
1443
1444 BEGIN
1445
1446 --extracting language Codes
1447 SELECT extractValue(lang_code, '/LanguageCode')
1448 BULK COLLECT INTO l_lang_code_tab
1449 FROM (SELECT Value(langcode) lang_code
1450 FROM EGO_PUB_WS_PARAMS i,
1451 TABLE(XMLSequence(
1452 extract(i.xmlcontent, l_langcode_xpath) )) langcode
1453 WHERE session_id=p_session_id
1454 );
1455
1456 --extracting language names
1457 SELECT extractValue(lang_name, '/LanguageName')
1458 BULK COLLECT INTO l_lang_name_tab
1459 FROM (SELECT Value(langname) lang_name
1460 FROM EGO_PUB_WS_PARAMS i,
1461 TABLE(XMLSequence(
1462 extract(i.xmlcontent, l_langname_xpath) )) langname
1463 WHERE session_id=p_session_id
1464 );
1465
1466
1467 l_count := l_lang_code_tab.Count;
1468 IF l_count < l_lang_name_tab.Count THEN
1469 l_count := l_lang_name_tab.Count;
1470 END IF;
1471
1472 --check if all language codes provided are different from null
1473 IF l_count > 0 THEN
1474
1475 FOR i IN 1..l_count
1476 LOOP
1477
1478 IF l_lang_code_tab.Count >= i THEN
1479 l_temp_code := l_lang_code_tab(i);
1480 ELSE
1481 l_temp_code := NULL;
1482 END IF;
1483 IF l_lang_name_tab.Count >= i THEN
1484 l_temp_name := l_lang_name_tab(i);
1485 ELSE
1486 l_temp_name := NULL;
1487 END IF;
1488
1489 /*INSERT INTO tr_temp (Session_id, message)
1490 values (p_session_id, ' l_temp_code: ' || l_temp_code);
1491 INSERT INTO tr_temp (Session_id, message)
1492 values (p_session_id, ' l_lang_name_tab(i): ' || l_lang_name_tab(i));
1493 INSERT INTO tr_temp (Session_id, message)
1494 values (p_session_id, ' l_temp_name: ' || l_temp_name);
1495 */
1496
1497 --validating language code or language name
1498 l_is_valid := Validate_Language_Info(p_session_id => p_session_id,
1499 p_language_code => l_temp_code,
1500 p_language_name => l_temp_name);
1501
1502 IF l_is_valid = TRUE THEN
1503 l_valid_count := l_valid_count + 1;
1504 l_lang_code_tab(i) := l_temp_code;
1505 ELSE
1506 l_lang_code_tab(i) := NULL;
1507 --TODO: Generate warning message for non-valid language code/name
1508 --in l_temp_code or l_temp_name
1509
1510 IF l_temp_code IS NULL THEN
1511 -- write error in odi error tables
1512 SELECT Nvl(Max(INPUT_ID),0) + 1
1513 INTO l_index
1514 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
1515 WHERE session_id = p_session_id;
1516
1517 Populate_Input_Identifier( p_session_id => p_session_id,
1518 p_input_id => l_index,
1519 p_param_name => 'LanguageCode',
1520 p_param_value => l_temp_code
1521 );
1522
1523 Log_Error( p_session_id => p_session_id,
1524 p_input_id => l_index,
1525 p_err_code => 'EGO_INVALID_LANGUAGE_CODE',
1526 p_err_message => 'Invalid Language Code'
1527 );
1528 END IF;
1529 IF l_temp_name IS NULL THEN
1530 -- write error in odi error tables
1531 SELECT Nvl(Max(INPUT_ID),0) + 1
1532 INTO l_index
1533 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
1534 WHERE session_id = p_session_id;
1535
1536 Populate_Input_Identifier( p_session_id => p_session_id,
1537 p_input_id => l_index,
1538 p_param_name => 'LanguageName',
1539 p_param_value => l_temp_name
1540 );
1541
1542 Log_Error( p_session_id => p_session_id,
1543 p_input_id => l_index,
1544 p_err_code => 'EGO_INVALID_LANGUAGE_NAME',
1545 p_err_message => 'Invalid Language Name'
1546 );
1547 END IF;
1548
1549
1550
1551 END IF;
1552
1553 END LOOP;
1554
1555 --if no valid language codes or names were found, assume none were passed
1556 IF l_valid_count = 0 THEN
1557 l_codes_provided := 'N';
1558 END IF;
1559
1560 ELSE
1561
1562 l_codes_provided := 'N';
1563
1564 END IF;
1565
1566 --Insert record into config table for parameter language
1567 --IF l_lang_code_tab.Count> 0 THEN
1568 IF l_codes_provided = 'Y' THEN
1569
1570 FOR i IN 1..l_count
1571 LOOP
1572 /*INSERT INTO tr_temp (Session_id, message)
1573 values (p_session_id, 'LANGUAGE_CODE:' || l_lang_code_tab(i));*/
1574
1575 IF l_lang_code_tab(i) IS NOT NULL THEN
1576 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
1577 odi_session_id,
1578 Parameter_Name,
1579 Data_Type,
1580 Date_Value,
1581 Char_value,
1582 Numeric_Value,
1583 creation_date,
1584 created_by,
1585 web_service_name)
1586 VALUES (p_session_id,
1587 p_session_id,
1588 'LANGUAGE_CODE',
1589 2,
1590 NULL,
1591 l_lang_code_tab(i),
1592 NULL,
1593 SYSDATE,
1594 G_CURRENT_USER_ID,
1595 p_web_service_name);
1596 END IF;
1597 END LOOP;
1598
1599 ELSE
1600
1601 FOR i IN (SELECT language_code FROM FND_LANGUAGES WHERE INSTALLED_FLAG IN ('I','B') ) LOOP
1602
1603 /*INSERT INTO tr_temp (Session_id, message)
1604 values (p_session_id, 'LANGUAGE_CODE:' || i.language_code);
1605 */
1606 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
1607 odi_session_id,
1608 Parameter_Name,
1609 Data_Type,
1610 Date_Value,
1611 Char_value,
1612 Numeric_Value,
1613 creation_date,
1614 created_by,
1615 web_service_name)
1616 VALUES (p_session_id,
1617 p_session_id,
1618 'LANGUAGE_CODE',
1619 2,
1620 NULL,
1621 i.language_code,
1622 NULL,
1623 SYSDATE,
1624 G_CURRENT_USER_ID,
1625 p_web_service_name);
1626 END LOOP;
1627
1628 END IF;
1629
1630 END Config_Languages;
1631
1632 ---
1633 --- This function returns the next input identifier to be used
1634 --- while inserting error records in EGO_PUB_WS_INPUT_IDENTIFIERS
1635 --- the id used as input_id should be unique per session
1636 ---
1637 FUNCTION Get_Max_Input_Identifier ( p_session_id IN NUMBER)
1638 RETURN NUMBER
1639 IS
1640 l_index NUMBER;
1641 --l_proc_name VARCHAR2(50) := 'Get_Max_Input_Identifier=>';
1642 BEGIN
1643
1644 SELECT Nvl(Max(INPUT_ID),0) + 1
1645 INTO l_index
1646 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
1647 WHERE session_id = p_session_id;
1648
1649 RETURN l_index;
1650 EXCEPTION
1651 WHEN OTHERS THEN
1652 --debug(1, 'Unexpected exception in '||l_proc_name||SQLERRM);
1653 RETURN -1;
1654 END Get_Max_Input_Identifier;
1655
1656 /*Procedure to insert records into Input Identifiers table*/
1657 PROCEDURE Populate_Input_Identifier(p_session_id IN NUMBER,
1658 p_input_id IN NUMBER,
1659 p_param_name IN VARCHAR2,
1660 p_param_value IN VARCHAR2)
1661 IS
1662
1663 BEGIN
1664
1665 INSERT INTO EGO_PUB_WS_INPUT_IDENTIFIERS(session_id,
1666 odi_session_id,
1667 input_id,
1668 param_name,
1669 param_value,
1670 creation_date,
1671 created_by)
1672 VALUES(p_session_id,
1673 p_session_id,
1674 p_input_id,
1675 p_param_name,
1676 p_param_value,
1677 SYSDATE,
1678 -1);
1679
1680
1681 END Populate_Input_Identifier;
1682
1683
1684 /*Procedure to Log Errors*/
1685 PROCEDURE Log_Error(p_session_id IN NUMBER,
1686 p_input_id IN NUMBER,
1687 p_err_code IN VARCHAR2,
1688 p_err_message IN VARCHAR2)
1689 IS
1690
1691 BEGIN
1692
1693 INSERT INTO EGO_PUB_WS_ERRORS(session_id,
1694 odi_session_id,
1695 input_id,
1696 err_code,
1697 err_message,
1698 creation_date,
1699 created_by)
1700 VALUES(p_session_id,
1701 p_session_id,
1702 p_input_id,
1703 p_err_code,
1704 p_err_message,
1705 SYSDATE,
1706 -1);
1707
1708 END Log_Error;
1709
1710 PROCEDURE Write_Errors_ToBatFwk(p_session_id IN NUMBER,
1711 p_batch_id IN NUMBER)
1712 IS
1713
1714 l_batch_id NUMBER := p_batch_id;
1715 l_trigger_import VARCHAR2(10) := NULL;
1716 l_INPUT_ID NUMBER;
1717 l_param_name VARCHAR2(100) := NULL;
1718 l_param_value VARCHAR2(100) := NULL;
1719 l_message VARCHAR2(2000);
1720 l_return_status VARCHAR2(1);
1721 l_invld_sys_cnt NUMBER;
1722 l_error_rec_count NUMBER :=0;
1723
1724 v_index NUMBER;
1725 v_count NUMBER;
1726 l_system_code VARCHAR2(40);
1727 bat_ent_stat_rec EGO_PUB_FWK_PK.TBL_OF_BAT_ENT_OBJ_STAT_TYPE;
1728 x_bat_status_out EGO_PUB_FWK_PK.TBL_OF_BAT_ENT_OBJ_RSTS_TYPE;
1729
1730 x_return_status VARCHAR2(1);
1731 x_msg_count NUMBER;
1732 x_msg_data VARCHAR2(500);
1733
1734 -- Get System_code from EGO_PUB_WS_CONFIG table
1735
1736 CURSOR c_system_codes(l_session_id NUMBER)
1737 IS
1738 SELECT CHAR_VALUE
1739 FROM EGO_PUB_WS_CONFIG
1740 WHERE SESSION_ID = l_session_id
1741 AND PARAMETER_NAME = 'SYSTEM_CODE';
1742
1743 -- Get input and derived entities from EGO_PUB_BAT_ENT_OBJS_V
1744 CURSOR c_pub_ws_entities(l_session_id NUMBER)
1745 IS
1746 SELECT PK1_VALUE,
1747 PK2_VALUE,
1748 PK3_VALUE,
1749 PK4_VALUE,
1750 PK5_VALUE,
1751 USER_ENTERED
1752 FROM EGO_PUB_WS_ENTITIES
1753 WHERE SESSION_ID = l_session_id;
1754
1755 -- Get PKs, SYSTEM_CODE from EGO_PUB_WS_INPUT_IDENTIFIERS
1756 CURSOR c_input_identifier(l_session_id NUMBER, l_system_code VARCHAR2)
1757 IS
1758 SELECT SESSION_ID,
1759 INPUT_ID,
1760 SYSTEM_CODE,
1761 PARAM_NAME,
1762 PARAM_VALUE,
1763 PK1_VALUE,
1764 PK2_VALUE,
1765 PK3_VALUE,
1766 PK4_VALUE,
1767 PK5_VALUE
1768 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
1769 WHERE SESSION_ID = l_session_id
1770 AND SYSTEM_CODE = l_system_code;
1771
1772 -- Get ERROR_CODE, ERROR_MESSAGE from EGO_PUB_WS_ERRORS
1773 CURSOR c_pub_ws_error(l_session_id NUMBER, l_input_id NUMBER, l_system_code VARCHAR2)
1774 IS
1775 SELECT SESSION_ID,
1776 INPUT_ID,
1777 SYSTEM_CODE,
1778 ERR_CODE,
1779 ERR_MESSAGE
1780 FROM EGO_PUB_WS_ERRORS
1781 WHERE SESSION_ID = l_session_id
1782 AND INPUT_ID = l_input_id
1783 AND SYSTEM_CODE = l_system_code;
1784
1785 -- Get all entities of EGO_PUBLICATION_BATCH_GT
1786 CURSOR c_pub_bat_gt(l_batch_id NUMBER)
1787 IS
1788 SELECT BATCH_ID,
1789 PK1_VALUE,
1790 PK2_VALUE,
1791 PK3_VALUE,
1792 PK4_VALUE,
1793 PK5_VALUE,
1794 SYSTEM_CODE,
1795 STATUS,
1796 MESSAGE
1797 FROM EGO_PUBLICATION_BATCH_GT
1798 WHERE BATCH_ID = l_batch_id;
1799
1800 BEGIN
1801
1802 SELECT CHAR_VALUE
1803 INTO l_trigger_import
1804 FROM EGO_PUB_WS_CONFIG
1805 WHERE SESSION_ID = p_session_id
1806 AND PARAMETER_NAME = 'TRIGGER_IMPORT';
1807
1808 -- use global temporary table (reuse the one currently of batch publication framework)
1809 -- and copy records in EGO_PUB_WS_ENTITIES into it while setting columns status equal to SUCCESS and
1810 -- SYSTEM_CODE equal to current target system for all records.
1811
1812 FOR sys IN c_system_codes(p_session_id)
1813 LOOP
1814 l_system_code := sys.CHAR_VALUE;
1815 l_invld_sys_cnt := 0;
1816
1817 -- invalid system stored in EGO_PUB_WS_ERRORS for the p_session_id
1818 -- get count for the l_system_code
1819 -- if it is equal to zero then only for that system code
1820 -- send the status back to batch fwk table EGO_PUB_BAT_STATUS_B
1821
1822 BEGIN
1823 SELECT count(1)
1824 INTO l_invld_sys_cnt
1825 FROM EGO_PUB_WS_ERRORS
1826 WHERE SESSION_ID = p_session_id
1827 AND ERR_CODE = 'D'
1828 AND SYSTEM_CODE = l_system_code;
1829 EXCEPTION
1830 WHEN NO_DATA_FOUND THEN
1831 NULL;
1832 WHEN OTHERS THEN
1833 NULL;
1834 END;
1835
1836 -- if the system is valid
1837
1838 IF (l_invld_sys_cnt = 0) THEN
1839
1840 -- If concurrent import program is not run on target system
1841 -- for all entities of the batch provide common message conveying
1842 -- to run concurrent import program manually
1843
1844 IF (l_trigger_import = 'N' OR l_trigger_import = 'FALSE') THEN
1845 FND_MESSAGE.SET_NAME('EGO', 'EGO_NO_AUTO_IMPORT_ON_TARGET');
1846 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
1847
1848 FOR ent IN c_pub_ws_entities(p_session_id)
1849 LOOP
1850
1851 --DBMS_OUTPUT.PUT_LINE('ent.pk1_value: ' || to_char(ent.pk1_value));
1852 INSERT INTO EGO_PUBLICATION_BATCH_GT
1853 (BATCH_ID,
1854 BATCH_ENTITY_OBJECT_ID,
1855 PK1_VALUE,
1856 PK2_VALUE,
1857 PK3_VALUE,
1858 PK4_VALUE,
1859 PK5_VALUE,
1860 USER_ENTERED,
1861 SYSTEM_CODE,
1862 STATUS,
1863 MESSAGE,
1864 PROCESS_FLAG,
1865 RETURN_STATUS,
1866 RETURN_ERROR_MESSAGE,
1867 CREATED_BY,
1868 CREATION_DATE,
1869 LAST_UPDATED_BY,
1870 LAST_UPDATE_DATE
1871 )
1872 VALUES (l_batch_id,
1873 NULL,
1874 ent.PK1_VALUE,
1875 ent.PK2_VALUE,
1876 ent.PK3_VALUE,
1877 ent.PK4_VALUE,
1878 ent.PK5_VALUE,
1879 ent.USER_ENTERED,
1880 l_system_code,
1881 'I',
1882 l_message,
1883 NULL,
1884 NULL,
1885 NULL,
1886 -1,
1887 SYSDATE,
1888 -1,
1889 SYSDATE
1890 );
1891 END LOOP;
1892
1893 ELSIF (l_trigger_import = 'Y' OR l_trigger_import = 'TRUE') THEN
1894
1895 -- if concurrent import program is run on target system then
1896 -- for all the entities of the batch, initial status would be Success
1897 -- in to Global Temporary (GT) table,
1898 -- after that correspondig status for error or warning will be posted
1899 -- for the entities as a next transaction in to GT table.
1900 FOR ent IN c_pub_ws_entities(p_session_id)
1901 LOOP
1902
1903 --DBMS_OUTPUT.PUT_LINE('ent.pk1_value: ' || to_char(ent.pk1_value));
1904 INSERT INTO EGO_PUBLICATION_BATCH_GT
1905 (BATCH_ID,
1906 BATCH_ENTITY_OBJECT_ID,
1907 PK1_VALUE,
1908 PK2_VALUE,
1909 PK3_VALUE,
1910 PK4_VALUE,
1911 PK5_VALUE,
1912 USER_ENTERED,
1913 SYSTEM_CODE,
1914 STATUS,
1915 MESSAGE,
1916 PROCESS_FLAG,
1917 RETURN_STATUS,
1918 RETURN_ERROR_MESSAGE,
1919 CREATED_BY,
1920 CREATION_DATE,
1921 LAST_UPDATED_BY,
1922 LAST_UPDATE_DATE
1923 )
1924 VALUES (l_batch_id,
1925 NULL,
1926 ent.PK1_VALUE,
1927 ent.PK2_VALUE,
1928 ent.PK3_VALUE,
1929 ent.PK4_VALUE,
1930 ent.PK5_VALUE,
1931 ent.USER_ENTERED,
1932 l_system_code,
1933 'S',
1934 NULL,
1935 NULL,
1936 NULL,
1937 NULL,
1938 -1,
1939 SYSDATE,
1940 -1,
1941 SYSDATE
1942 );
1943 END LOOP;
1944
1945 END IF;
1946
1947 END IF;
1948
1949 -- if the system is invalid
1950 -- insert records for invalid system with error message
1951 IF (l_invld_sys_cnt > 0) THEN
1952 FND_MESSAGE.SET_NAME('EGO', 'EGO_SYS_CODE_NO_CONTEXT');
1953 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
1954 FOR ent IN c_pub_ws_entities(p_session_id)
1955 LOOP
1956
1957 --DBMS_OUTPUT.PUT_LINE('ent.pk1_value: ' || to_char(ent.pk1_value));
1958 INSERT INTO EGO_PUBLICATION_BATCH_GT
1959 ( BATCH_ID,
1960 BATCH_ENTITY_OBJECT_ID,
1961 PK1_VALUE,
1962 PK2_VALUE,
1963 PK3_VALUE,
1964 PK4_VALUE,
1965 PK5_VALUE,
1966 USER_ENTERED,
1967 SYSTEM_CODE,
1968 STATUS,
1969 MESSAGE,
1970 PROCESS_FLAG,
1971 RETURN_STATUS,
1972 RETURN_ERROR_MESSAGE,
1973 CREATED_BY,
1974 CREATION_DATE,
1975 LAST_UPDATED_BY,
1976 LAST_UPDATE_DATE
1977 )
1978 VALUES (l_batch_id,
1979 NULL,
1980 ent.PK1_VALUE,
1981 ent.PK2_VALUE,
1982 ent.PK3_VALUE,
1983 ent.PK4_VALUE,
1984 ent.PK5_VALUE,
1985 ent.USER_ENTERED,
1986 l_system_code,
1987 'F',
1988 l_message,
1989 NULL,
1990 NULL,
1991 NULL,
1992 -1,
1993 SYSDATE,
1994 -1,
1995 SYSDATE
1996 );
1997 END LOOP;
1998 END IF;
1999
2000 END LOOP;
2001
2002 -- For each record in EGO_PUB_WS_ERRORS , join with EGO_PUB_WS_INPUT_IDENTIFIERS
2003 -- using INPUT_ID, SESSION_ID, and SYSTEM_CODE
2004 -- to retrieve PKs from EGO_PUB_WS_INPUT_IDENTIFIERS, and ERR_CODE and ERR_MESSAGE from EGO_PUB_WS_ERRORS
2005 -- and update records in global temporary table accordingly using PKs.
2006
2007 IF (l_trigger_import = 'Y' OR l_trigger_import = 'TRUE') THEN
2008
2009 FOR sys IN c_system_codes(p_session_id)
2010 LOOP
2011 l_system_code := sys.CHAR_VALUE;
2012 l_invld_sys_cnt := 0;
2013
2014 -- invalid system stored in EGO_PUB_WS_ERRORS for the p_session_id
2015 -- get count for the l_system_code
2016 -- if it is equal to zero then only for that system code
2017 -- send the status back to batch fwk table EGO_PUB_BAT_STATUS_B
2018
2019 BEGIN
2020 SELECT count(1)
2021 INTO l_invld_sys_cnt
2022 FROM EGO_PUB_WS_ERRORS
2023 WHERE SESSION_ID = p_session_id
2024 AND ERR_CODE = 'D'
2025 AND SYSTEM_CODE = l_system_code;
2026 EXCEPTION
2027 WHEN NO_DATA_FOUND THEN
2028 NULL;
2029 WHEN OTHERS THEN
2030 NULL;
2031 END;
2032
2033 -- if the system is valid
2034
2035 IF (l_invld_sys_cnt = 0) THEN
2036
2037 --DBMS_OUTPUT.PUT_LINE('l_system_code ' || l_system_code);
2038 FOR ip IN c_input_identifier(p_session_id, l_system_code)
2039 LOOP
2040 l_input_id := ip.INPUT_ID;
2041 l_param_value := ip.PARAM_VALUE;
2042 --DBMS_OUTPUT.PUT_LINE('l_input_id ' || to_char(l_input_id));
2043 BEGIN
2044 SELECT count(1)
2045 INTO l_error_rec_count
2046 FROM EGO_PUB_WS_ERRORS
2047 WHERE session_id = p_session_id
2048 AND system_code = l_system_code
2049 AND input_id = l_input_id;
2050 EXCEPTION
2051 WHEN NO_DATA_FOUND THEN
2052 NULL;
2053 WHEN OTHERS THEN
2054 NULL;
2055 END;
2056 FOR err IN c_pub_ws_error(p_session_id, l_input_id, l_system_code)
2057 LOOP
2058
2059 IF (instr(err.ERR_CODE, '_ERROR') <> 0) THEN
2060 IF ((l_error_rec_count > 1) AND (err.ERR_CODE <> 'EGO_SYNC_MIB_STAT_1_ERROR')) THEN
2061
2062 FND_MESSAGE.SET_NAME('EGO', err.ERR_CODE);
2063 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', l_param_value);
2064 FND_MESSAGE.SET_TOKEN ('SYSTEM_CODE', l_system_code);
2065 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2066
2067 UPDATE EGO_PUBLICATION_BATCH_GT
2068 SET STATUS = 'F', MESSAGE = l_message
2069 WHERE BATCH_ID = l_batch_id
2070 AND PK1_VALUE = ip.PK1_VALUE
2071 AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
2072 AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
2073 AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
2074 AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
2075 AND SYSTEM_CODE = ip.SYSTEM_CODE;
2076
2077 -- Update Err_Message in EGO_PUB_WS_ERROR table
2078
2079 UPDATE EGO_PUB_WS_ERRORS
2080 SET ERR_MESSAGE = l_message
2081 WHERE SESSION_ID = p_session_id
2082 AND INPUT_ID = l_input_id
2083 AND SYSTEM_CODE = l_system_code;
2084 END IF;
2085 IF (l_error_rec_count = 1) THEN
2086
2087 FND_MESSAGE.SET_NAME('EGO', err.ERR_CODE);
2088 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', l_param_value);
2089 FND_MESSAGE.SET_TOKEN ('SYSTEM_CODE', l_system_code);
2090 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2091
2092 --DBMS_OUTPUT.PUT_LINE('l_batch_id: ' || to_char(l_batch_id));
2093 --DBMS_OUTPUT.PUT_LINE('ip.PK1_VALUE: ' || to_char(ip.PK1_VALUE));
2094 --DBMS_OUTPUT.PUT_LINE('ip.SYSTEM_CODE: ' || to_char(ip.SYSTEM_CODE));
2095
2096 UPDATE EGO_PUBLICATION_BATCH_GT
2097 SET STATUS = 'F', MESSAGE = l_message
2098 WHERE BATCH_ID = l_batch_id
2099 AND PK1_VALUE = ip.PK1_VALUE
2100 AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
2101 AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
2102 AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
2103 AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
2104 AND SYSTEM_CODE = ip.SYSTEM_CODE;
2105
2106 -- Update Err_Message in EGO_PUB_WS_ERROR table
2107
2108 UPDATE EGO_PUB_WS_ERRORS
2109 SET ERR_MESSAGE = l_message
2110 WHERE SESSION_ID = p_session_id
2111 AND INPUT_ID = l_input_id
2112 AND SYSTEM_CODE = l_system_code;
2113
2114 END IF;
2115 END IF;
2116
2117 IF (instr(err.ERR_CODE, '_WARNING') <> 0) THEN
2118
2119 FND_MESSAGE.SET_NAME ('EGO', err.ERR_CODE);
2120 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', l_param_value);
2121 FND_MESSAGE.SET_TOKEN ('SYSTEM_CODE', l_system_code);
2122 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2123
2124 UPDATE EGO_PUBLICATION_BATCH_GT
2125 SET STATUS = 'W', MESSAGE = l_message
2126 WHERE BATCH_ID = l_batch_id
2127 AND PK1_VALUE = ip.PK1_VALUE
2128 AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
2129 AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
2130 AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
2131 AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
2132 AND SYSTEM_CODE = ip.SYSTEM_CODE;
2133
2134 -- Update Err_Message in EGO_PUB_WS_ERROR table
2135
2136 UPDATE EGO_PUB_WS_ERRORS
2137 SET ERR_MESSAGE = l_message
2138 WHERE SESSION_ID = p_session_id
2139 AND INPUT_ID = l_input_id
2140 AND SYSTEM_CODE = l_system_code;
2141
2142 END IF;
2143 END LOOP;
2144 l_error_rec_count := 0;
2145 END LOOP;
2146 END IF;
2147 END LOOP;
2148 END IF;
2149
2150 -- For each record in the temporary global table, invoke Update_Pub_Status API with Batch_id,
2151 -- PKs, and Status, and Message to log the errors per entity per target system to batch
2152 -- publication framework.
2153
2154 v_index := 1;
2155 FOR gt IN c_pub_bat_gt(l_batch_id)
2156 LOOP
2157 -- Prepare the record for each entity
2158 -- DBMS_OUTPUT.PUT_LINE('v_index: ' || to_char(v_index));
2159 --DBMS_OUTPUT.PUT_LINE('gt.batch_id: ' || to_char(gt.batch_id));
2160 --DBMS_OUTPUT.PUT_LINE('gt.pk1_value: ' || gt.pk1_value);
2161 --DBMS_OUTPUT.PUT_LINE('gt.SYSTEM_CODE: ' || gt.SYSTEM_CODE);
2162 -- DBMS_OUTPUT.PUT_LINE('gt.status: ' || gt.status);
2163 --DBMS_OUTPUT.PUT_LINE('gt.message: ' || gt.message);
2164
2165 bat_ent_stat_rec(v_index).batch_id := gt.batch_id;
2166 bat_ent_stat_rec(v_index).pk1_value := gt.pk1_value;
2167 bat_ent_stat_rec(v_index).pk2_value := gt.pk2_value;
2168 bat_ent_stat_rec(v_index).pk3_value := gt.pk3_value;
2169 bat_ent_stat_rec(v_index).pk4_value := gt.pk4_value;
2170 bat_ent_stat_rec(v_index).pk5_value := gt.pk5_value;
2171 bat_ent_stat_rec(v_index).system_code := gt.system_code;
2172 bat_ent_stat_rec(v_index).status := gt.status;
2173 bat_ent_stat_rec(v_index).message := gt.message;
2174
2175 --DBMS_OUTPUT.PUT_LINE('bat_ent_stat_rec('||to_char(v_index)||').batch_id:' || to_char(bat_ent_stat_rec(v_index).batch_id));
2176 -- DBMS_OUTPUT.PUT_LINE('bat_ent_stat_rec('||to_char(v_index)||').pk1_value: ' || bat_ent_stat_rec(v_index).pk1_value);
2177 -- DBMS_OUTPUT.PUT_LINE('bat_ent_stat_rec('||to_char(v_index)||').system_code: ' || bat_ent_stat_rec(v_index).system_code);
2178 --DBMS_OUTPUT.PUT_LINE('bat_ent_stat_rec('||to_char(v_index)||').status: ' || bat_ent_stat_rec(v_index).status);
2179 --DBMS_OUTPUT.PUT_LINE('bat_ent_stat_rec('||to_char(v_index)||').message: ' || bat_ent_stat_rec(v_index).message);
2180
2181
2182 v_index := v_index + 1;
2183 END LOOP;
2184
2185 EGO_PUB_FWK_PK.Update_Pub_Status(l_batch_id, 2, bat_ent_stat_rec,x_bat_status_out,x_return_status,x_msg_count,x_msg_data);
2186
2187 -- do we need to get x_bat_status_out (TBL_OF_BAT_ENT_OBJ_RSTS_TYPE --- TABLE OF BAT_ENT_OBJ_RET_STAT_TYPE )
2188 IF (x_return_status <> 'S') THEN
2189 --dbms_output.put_line('return status : ' || x_return_status);
2190 l_return_status := x_return_status;
2191 END IF;
2192
2193 END Write_Errors_ToBatFwk;
2194
2195 FUNCTION Get_Application_id(p_appl_short_name IN VARCHAR2) RETURN NUMBER
2196 IS
2197 l_application_id FND_APPLICATION.APPLICATION_ID%TYPE;
2198 BEGIN
2199 BEGIN
2200 SELECT application_id
2201 INTO l_application_id
2202 FROM fnd_application
2203 WHERE application_short_name=p_appl_short_name;
2204 EXCEPTION
2205 WHEN No_Data_Found THEN
2206 RETURN -1;
2207 WHEN OTHERS THEN
2208 RETURN -1;
2209 END;
2210
2211 RETURN l_application_id;
2212 END Get_Application_id;
2213
2214 PROCEDURE Write_ErrorMsgs_ToErrorTable(p_session_id IN NUMBER)
2215 IS
2216
2217 l_mode VARCHAR2(10) := NULL;
2218 l_INPUT_ID NUMBER;
2219 l_param_name VARCHAR2(100) := NULL;
2220 l_param_value VARCHAR2(100) := NULL;
2221 l_message VARCHAR2(2000);
2222 l_return_status VARCHAR2(1);
2223 l_invld_sys_cnt NUMBER;
2224
2225 v_index NUMBER;
2226 v_count NUMBER;
2227 l_system_code VARCHAR2(40);
2228
2229 -- Get System_code from EGO_PUB_WS_CONFIG table
2230
2231 CURSOR c_system_codes(l_session_id NUMBER)
2232 IS
2233 SELECT CHAR_VALUE
2234 FROM EGO_PUB_WS_CONFIG
2235 WHERE SESSION_ID = l_session_id
2236 AND PARAMETER_NAME = 'SYSTEM_CODE';
2237
2238
2239 -- Get PKs, SYSTEM_CODE from EGO_PUB_WS_INPUT_IDENTIFIERS
2240 CURSOR c_input_identifier(l_session_id NUMBER, l_system_code VARCHAR2)
2241 IS
2242 SELECT SESSION_ID,
2243 INPUT_ID,
2244 SYSTEM_CODE,
2245 PARAM_NAME,
2246 PARAM_VALUE,
2247 PK1_VALUE,
2248 PK2_VALUE,
2249 PK3_VALUE,
2250 PK4_VALUE,
2251 PK5_VALUE
2252 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
2253 WHERE SESSION_ID = l_session_id
2254 AND SYSTEM_CODE = l_system_code;
2255
2256 -- Get ERROR_CODE, ERROR_MESSAGE from EGO_PUB_WS_ERRORS
2257 CURSOR c_pub_ws_error(l_session_id NUMBER, l_input_id NUMBER, l_system_code VARCHAR2)
2258 IS
2259 SELECT SESSION_ID,
2260 INPUT_ID,
2261 SYSTEM_CODE,
2262 ERR_CODE,
2263 ERR_MESSAGE
2264 FROM EGO_PUB_WS_ERRORS
2265 WHERE SESSION_ID = l_session_id
2266 AND INPUT_ID = l_input_id
2267 AND SYSTEM_CODE = l_system_code;
2268
2269 BEGIN
2270 l_mode := Get_Char_Param_Value(p_session_id,'MODE',NULL, NULL);
2271 IF l_mode = 'LIST' THEN
2272 FOR sys IN c_system_codes(p_session_id)
2273 LOOP
2274 l_system_code := sys.CHAR_VALUE;
2275 l_invld_sys_cnt := 0;
2276
2277 -- invalid system stored in EGO_PUB_WS_ERRORS for the p_session_id
2278 -- get count for the l_system_code
2279 -- if it is equal to zero then only for that system code
2280 -- send the status back to batch fwk table EGO_PUB_BAT_STATUS_B
2281
2282 BEGIN
2283 SELECT count(1)
2284 INTO l_invld_sys_cnt
2285 FROM EGO_PUB_WS_ERRORS
2286 WHERE SESSION_ID = p_session_id
2287 AND ERR_CODE = 'D'
2288 AND SYSTEM_CODE = l_system_code;
2289 EXCEPTION
2290 WHEN NO_DATA_FOUND THEN
2291 NULL;
2292 WHEN OTHERS THEN
2293 NULL;
2294 END;
2295
2296 -- if the system is valid
2297
2298 IF (l_invld_sys_cnt = 0) THEN
2299
2300 --DBMS_OUTPUT.PUT_LINE('l_system_code ' || l_system_code);
2301 FOR ip IN c_input_identifier(p_session_id, l_system_code)
2302 LOOP
2303 l_input_id := ip.INPUT_ID;
2304 l_param_value := ip.PARAM_VALUE;
2305 --DBMS_OUTPUT.PUT_LINE('l_input_id ' || to_char(l_input_id));
2306 FOR err IN c_pub_ws_error(p_session_id, l_input_id, l_system_code)
2307 LOOP
2308
2309 IF (instr(err.ERR_CODE, '_ERROR') <> 0) THEN
2310
2311 FND_MESSAGE.SET_NAME('EGO', err.ERR_CODE);
2312 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', l_param_value);
2313 FND_MESSAGE.SET_TOKEN ('SYSTEM_CODE', l_system_code);
2314 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2315
2316 --DBMS_OUTPUT.PUT_LINE('l_batch_id: ' || to_char(l_batch_id));
2317 --DBMS_OUTPUT.PUT_LINE('ip.PK1_VALUE: ' || to_char(ip.PK1_VALUE));
2318 --DBMS_OUTPUT.PUT_LINE('ip.SYSTEM_CODE: ' || to_char(ip.SYSTEM_CODE));
2319
2320 -- Update Err_Message in EGO_PUB_WS_ERROR table
2321
2322 UPDATE EGO_PUB_WS_ERRORS
2323 SET ERR_MESSAGE = l_message
2324 WHERE SESSION_ID = p_session_id
2325 AND INPUT_ID = l_input_id
2326 AND SYSTEM_CODE = l_system_code
2327 AND ERR_CODE = err.ERR_CODE;
2328
2329 END IF;
2330
2331 IF (instr(err.ERR_CODE, '_WARNING') <> 0) THEN
2332
2333 FND_MESSAGE.SET_NAME ('EGO', err.ERR_CODE);
2334 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', l_param_value);
2335 FND_MESSAGE.SET_TOKEN ('SYSTEM_CODE', l_system_code);
2336 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2337
2338 -- Update Err_Message in EGO_PUB_WS_ERROR table
2339
2340 UPDATE EGO_PUB_WS_ERRORS
2341 SET ERR_MESSAGE = l_message
2342 WHERE SESSION_ID = p_session_id
2343 AND INPUT_ID = l_input_id
2344 AND SYSTEM_CODE = l_system_code;
2345
2346 END IF;
2347 END LOOP;
2348 END LOOP;
2349 END IF;
2350 END LOOP;
2351 END IF;
2352 END Write_ErrorMsgs_ToErrorTable;
2353
2354 PROCEDURE Generate_XML(p_session_id IN NUMBER, p_web_service_name IN VARCHAR2)
2355 IS
2356
2357 p_xml_root_element VARCHAR2(100) := 'Content';
2358 p_transform_xml BOOLEAN := TRUE;
2359
2360 l_session_id NUMBER := p_session_id;
2361 l_rec_count NUMBER := 0;
2362 --l_odi_session_id NUMBER := p_odi_session_id;
2363 l_web_service_name VARCHAR2(100) := p_web_service_name;
2364 l_entity_type VARCHAR2(100) := NULL;
2365 l_xml CLOB;
2366
2367 l_output_xml XmlType;
2368 l_cr VARCHAR2(10) := fnd_global.local_chr(10);
2369 l_qm VARCHAR2(1) := '?'; -- Question mark in a string may be interpreted by ODI
2370
2371 l_level_stack dbms_sql.number_table;
2372 l_tags_stack dbms_sql.varchar2_table;
2373 l_previous_level NUMBER := -1;
2374 l_previous_entity_type VARCHAR2(100);
2375 l_previous_seqid NUMBER := -1;
2376 l_return_payload VARCHAR2(10) :='TRUE';
2377 l_sequence NUMBER :=1;
2378
2379 l_no_of_entities NUMBER :=0; --No of entities for a session in case of sync
2380 l_actual_node VARCHAR2(100) :=NULL;
2381 l_end_xml VARCHAR2(100) :=NULL;
2382 l_add_info_xml VARCHAR2(100) :=NULL;
2383
2384 l_chunk_detail CLOB; --Chunking
2385 l_err_xml CLOB; --Error Handling
2386 l_warn_xml CLOB; --Error Handling
2387
2388 l_entity_type_tag EGO_PUB_WS_ENTITIES.ENTITY_TYPE%TYPE;
2389 l_xml_header CLOB;
2390
2391 -- Code changes to optimize transform_xml API.
2392 xsldoc VARCHAR2(2000);
2393 myParser dbms_xmlparser.Parser;
2394 indomdoc dbms_xmldom.domdocument;
2395 xsltdomdoc dbms_xmldom.domdocument;
2396 xsl dbms_xslprocessor.stylesheet;
2397 outdomdocf dbms_xmldom.domdocumentfragment;
2398 outnode dbms_xmldom.domnode;
2399 proc dbms_xslprocessor.processor;
2400 xslclob clob;
2401 outclob clob;
2402
2403
2404 /*Cursor to get configurable parameter 'return_payload' value*/
2405 CURSOR cur_config_param(cp_session_id NUMBER)
2406 IS
2407 SELECT CHAR_VALUE param_value
2408 FROM ego_pub_ws_config
2409 WHERE session_id = cp_session_id
2410 AND upper(parameter_name) = 'RETURN_PAYLOAD';
2411
2412 --Start error handling
2413 CURSOR c_err_detail(cp_session_id NUMBER)
2414 IS
2415 SELECT session_id,input_id, err_code,err_message, 'US' language_code, system_code
2416 FROM EGO_PUB_WS_ERRORS
2417 WHERE session_id=cp_session_id
2418 AND ERR_CODE IN('EGO_SYNC_ENTITY_ERROR', 'EGO_SYNC_MIB_STAT_1_ERROR');
2419
2420 --Start warning handling
2421 CURSOR c_warn_detail(cp_session_id NUMBER)
2422 IS
2423 SELECT session_id,input_id, err_code,err_message, 'US' language_code, system_code
2424 FROM EGO_PUB_WS_ERRORS
2425 WHERE session_id=cp_session_id
2426 AND ERR_CODE = 'EGO_SYNC_ENTITY_WARNING';
2427
2428 CURSOR c_err_identifier(cp_session_id NUMBER,
2429 cp_input_id NUMBER,
2430 cp_system_code VARCHAR2)
2431 IS
2432 SELECT session_id, param_name,param_value
2433 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
2434 WHERE session_id=cp_session_id
2435 AND input_id=cp_input_id
2436 AND system_code= cp_system_code;
2437
2438 --End error handling
2439
2440 BEGIN
2441
2442 Write_ErrorMsgs_ToErrorTable(l_session_id);
2443
2444 /*Get value for configureable parameter 'return_payload'*/
2445 FOR i IN cur_config_param(l_session_id)
2446 LOOP
2447
2448 l_return_payload:=i.param_value;
2449
2450 END LOOP; --END LOOP FOR i IN cur_config_param(l_session_id)
2451
2452
2453 IF(l_web_service_name=G_VS_WEBSERVICE) THEN
2454 l_entity_type :='VS';
2455 l_actual_node := 'ns1:ListOfValueSets';
2456 l_chunk_detail := '<ns1:ListOfValueSets xmlns:ns1="http://xmlns.oracle.com/apps/ego/extfwk/service/out"><AdditionalInfo><SessionId>'||l_session_id||'</SessionId>';
2457 END IF;
2458
2459 IF(l_web_service_name=G_ICC_WEBSERVICE) THEN
2460 l_entity_type :='ICC';
2461 l_actual_node := 'ns1:ListOfICCs';
2462 l_chunk_detail := '<ns1:ListOfICCs xmlns:ns1="http://xmlns.oracle.com/apps/ego/itemcatalog/service/out"><AdditionalInfo><SessionId>'||l_session_id||'</SessionId>';
2463 END IF;
2464
2465 IF(l_web_service_name=G_AG_WEBSERVICE) THEN
2466 l_entity_type :='AG';
2467 l_actual_node := 'ns1:ListOfAG';
2468 l_chunk_detail := '<ns1:ListOfAG xmlns:ns1="http://xmlns.oracle.com/apps/ego/extfwk/service/out"><AdditionalInfo><SessionId>'||l_session_id||'</SessionId>';
2469 END IF;
2470
2471 l_end_xml :='</AdditionalInfo>';
2472
2473 BEGIN
2474 SELECT COUNT(1)
2475 INTO l_no_of_entities
2476 FROM EGO_PUB_WS_ENTITIES
2477 WHERE ENTITY_TYPE = l_entity_type
2478 AND SESSION_ID = l_session_id;
2479 EXCEPTION
2480 WHEN NO_DATA_FOUND THEN
2481 NULL;
2482 WHEN OTHERS THEN
2483 NULL;
2484 END;
2485 --DBMS_OUTPUT.PUT_LINE('l_session_id: ' || l_session_id);
2486 --DBMS_OUTPUT.PUT_LINE('l_odi_session_id: ' || l_odi_session_id);
2487 --Error Handling
2488 l_err_xml := l_err_xml || '<Status>' || l_cr;
2489
2490 FOR i IN c_err_detail(l_session_id)
2491 LOOP
2492 l_err_xml := l_err_xml || '<Error>' || l_cr;
2493 --DBMS_OUTPUT.PUT_LINE('l_err_xml 1: ' || l_err_xml);
2494 l_err_xml := l_err_xml || '<Code>' || i.err_code||'</Code>'||l_cr;
2495 --DBMS_OUTPUT.PUT_LINE('l_err_xml 2: ' || l_err_xml);
2496 l_err_xml := l_err_xml || '<Message>' || i.err_message||'</Message>'||l_cr;
2497
2498 l_err_xml := l_err_xml || '<LanguageCode>' || i.language_code||'</LanguageCode>'||l_cr;
2499 l_err_xml := l_err_xml || '<SystemCode>' || i.system_code||'</SystemCode>'||l_cr;
2500
2501 l_err_xml := l_err_xml || '<InputIdentifier>'||l_cr;
2502 --Create identifier tag if parameter entries exist
2503 FOR j IN c_err_identifier(l_session_id,i.input_id,i.system_code)
2504 LOOP
2505 l_err_xml := l_err_xml || '<Parameter>'||l_cr;
2506 l_err_xml := l_err_xml || '<Name>'||j.param_name||'</Name>'||l_cr;
2507 l_err_xml := l_err_xml || '<Value>'||j.param_value||'</Value>'||l_cr;
2508 l_err_xml := l_err_xml || '</Parameter>'||l_cr;
2509 END LOOP; --End FOR j IN c_err_identifier
2510
2511 l_err_xml := l_err_xml || '</InputIdentifier>'|| l_cr;
2512 l_err_xml := l_err_xml || '</Error>' || l_cr;
2513 END LOOP; --End FOR i IN c_err_detail(l_session_id)
2514
2515 --trudave
2516 -- start
2517 --Warning Handling
2518
2519 FOR i IN c_warn_detail(l_session_id)
2520 LOOP
2521 l_err_xml := l_err_xml || '<Warning>' || l_cr;
2522 l_err_xml := l_err_xml || '<Code>' || i.err_code||'</Code>'||l_cr;
2523 l_err_xml := l_err_xml || '<Message>' || i.err_message||'</Message>'||l_cr;
2524
2525 l_err_xml := l_err_xml || '<LanguageCode>' || i.language_code||'</LanguageCode>'||l_cr;
2526 l_err_xml := l_err_xml || '<SystemCode>' || i.system_code||'</SystemCode>'||l_cr;
2527
2528 l_err_xml := l_err_xml || '<InputIdentifier>'||l_cr;
2529 --Create identifier tag if parameter entries exist
2530 FOR j IN c_err_identifier(l_session_id,i.input_id,i.system_code)
2531 LOOP
2532 l_err_xml := l_err_xml || '<Parameter>'||l_cr;
2533 l_err_xml := l_err_xml || '<Name>'||j.param_name||'</Name>'||l_cr;
2534 l_err_xml := l_err_xml || '<Value>'||j.param_value||'</Value>'||l_cr;
2535 l_err_xml := l_err_xml || '</Parameter>'||l_cr;
2536 END LOOP; --End FOR j IN c_err_identifier
2537
2538 l_err_xml := l_err_xml || '</InputIdentifier>'|| l_cr;
2539 l_err_xml := l_err_xml || '</Warning>' || l_cr;
2540 END LOOP; --End FOR i IN c_warn_detail(l_session_id)
2541
2542 --end
2543 --trudave
2544 --Warning Handling
2545
2546
2547 l_err_xml := l_err_xml || '</Status>' || l_cr;
2548 -- l_err_xml := l_err_xml || '</'||l_actual_node||'>';
2549 --Error Handling
2550
2551 -- Optimizing parsing statement for transformation of XML
2552
2553 --selecting XLS transformation data from
2554 --table EGO_ODI_WS_XSL for specified web service
2555 select x.xslcontent.getclobval()
2556 into xslclob
2557 from EGO_ODI_WS_XSL x
2558 where web_service_name = l_web_service_name;
2559
2560 myParser := dbms_xmlparser.newParser;
2561
2562 dbms_xmlparser.parseclob(myParser, xslclob);
2563 xsltdomdoc := dbms_xmlparser.getDocument(myParser);
2564 xsl := dbms_xslprocessor.newstylesheet(xsltdomdoc, '');
2565 proc := dbms_xslprocessor.newProcessor;
2566
2567 -- Using XML header appendation operation before loop.
2568 --l_xml_header :='<' || l_qm || 'xml version="1.0" encoding="UTF-8"' || l_qm || '>' || l_cr;
2569 --l_xml_header := l_xml_header || '<' || p_xml_root_element || '>' || l_cr;
2570
2571 l_xml_header := l_chunk_detail || l_cr;
2572 --l_xml_header := l_xml_header || '<' || p_xml_root_element || '>' || l_cr;
2573
2574 IF ( l_return_payload ='TRUE' OR l_return_payload ='Y') THEN
2575 --Case to return complete payload
2576 l_xml := l_xml_header;
2577 l_add_info_xml := '<EntityCount>'||l_no_of_entities||'</EntityCount>'||l_end_xml;
2578
2579 dbms_lob.append(l_xml,l_add_info_xml);
2580 dbms_lob.append(l_xml,l_err_xml);
2581
2582 l_entity_type_tag := '</'||l_actual_node||'>' || l_cr;
2583 dbms_lob.append(l_xml,to_clob(l_entity_type_tag));
2584
2585 l_output_xml := XmlType(l_xml);
2586
2587 -- Write final XML payload to output table in XML TYPE column
2588 -- with sequence id as zero
2589 -- This payload will be accessed using serviceutil.java with sequence_id
2590 -- as zero for return_payload as TRUE or Y
2591
2592 BEGIN
2593 SELECT count(1)
2594 INTO l_rec_count
2595 FROM EGO_PUB_WS_OUTPUT
2596 WHERE SESSION_ID = l_session_id;
2597 EXCEPTION
2598 WHEN NO_DATA_FOUND THEN
2599 NULL;
2600 WHEN OTHERS THEN
2601 NULL;
2602 END;
2603 IF l_rec_count > 0 THEN
2604 UPDATE ego_pub_ws_output
2605 SET xmlcontent = l_output_xml,
2606 xml_odi = XmlType(l_xml),
2607 creation_date = sysdate
2608 WHERE session_id = l_session_id
2609 AND web_service_name = l_web_service_name;
2610 ELSE
2611 INSERT INTO ego_pub_ws_output (session_id,
2612 odi_session_id,
2613 web_service_name,
2614 entity_type,
2615 sequence_id,
2616 xmlcontent,
2617 xml_odi,
2618 creation_date,
2619 created_by)
2620 VALUES (l_session_id,
2621 l_session_id,
2622 l_web_service_name,
2623 l_entity_type,
2624 0,
2625 l_output_xml,
2626 XmlType(l_xml),
2627 sysdate,
2628 0);
2629 END IF;
2630 END IF;
2631
2632 END Generate_XML;
2633
2634 END EGO_PUB_WS_UTIL;