1 package body EGO_PUB_WS_ICC AS
2 /* $Header: EGOPICCB.pls 120.9.12020000.3 2012/11/26 19:51:59 vijoshi ship $ --kjonnala testing*/
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : EGOPICCB.pls |
10 | DESCRIPTION : This package is meant for pre-processing the ICC entity |
11 | records in the Metadata Sync flow. |
12 | Created By : kjonnala |
13 | Created On : 25-Mar-2011 |
14 | |
15 +==========================================================================*/
16
17 --
18 -- Bug 12377269. Added below TYPEs to support
19 -- methods to get heirarchical ICCs
20 --
21 TYPE t_icc_id_rec IS RECORD (
22 item_catalog_group_id ego_mtl_catalog_grp_vers_b.item_catalog_group_id%TYPE,
23 version_seq_id ego_mtl_catalog_grp_vers_b.version_seq_id%TYPE,
24 catalog_concat_segs mtl_item_catalog_groups_v.catalog_concat_segs%TYPE
25 );
26
27 TYPE t_icc_id_recs IS TABLE OF t_icc_id_rec INDEX BY BINARY_INTEGER;
28
29
30 /* Procedure for writing debug msgs*/
31 PROCEDURE debug ( p_msg IN VARCHAR2)
32 IS
33 BEGIN
34
35 --DBMS_OUTPUT.PUT_LINE(G_PKG_NAME||'.'||p_msg);
36 --debug_proc_kk(G_PKG_NAME||'.'||p_msg);
37 NULL;
38 EXCEPTION
39 WHEN OTHERS THEN
40 --DBMS_OUTPUT.PUT_LINE(G_PKG_NAME||' Unknown exception while writing debug msg =>'||SQLERRM);
41 --debug_proc_kk(G_PKG_NAME||' Unknown exception while writingdebug msg =>'||SQLERRM);
42 NULL;
43 END debug;
44
45
46 --- ===========================================================================
47 -- Name : Get_Current_ICC_Version
48 -- Description : This function gets the effective version for an ICC.
49 -- if none is found or is a non-versioned ICC
50 -- then NULL is returned
51 -- else the current effective version is returned
52 --
53 -- Scope : Private
54 --
55 --- ===========================================================================
56
57 FUNCTION Get_Current_ICC_Version( p_icc_id IN NUMBER) RETURN NUMBER
58 IS
59 l_cur_icc_ver_id EGO_ICC_VERS_INTERFACE.VER_SEQ_ID%TYPE;
60 L_PROC_NAME VARCHAR2(50);
61 BEGIN
62
63 L_PROC_NAME := ' Get_Current_ICC_Version => ';
64 --debug(L_PROC_NAME||' Start');
65 --debug(L_PROC_NAME||' Parameters - p_icc_id: '||p_icc_id);
66
67 BEGIN
68 SELECT version_seq_id INTO l_cur_icc_ver_id
69 FROM
70 ( SELECT item_catalog_group_id,version_seq_id,MAX(start_active_date) start_active_date
71 FROM EGO_MTL_CATALOG_GRP_VERS_B
72 WHERE NVL(end_active_date, sysdate) >= SYSDATE
73 AND start_active_date <= SYSDATE
74 AND ITEM_CATALOG_GROUP_ID = p_icc_id
75 AND version_seq_id > 0
76 GROUP BY item_catalog_group_id,version_seq_id
77 HAVING MAX(start_active_date)<=SYSDATE
78 );
79 EXCEPTION
80 WHEN No_Data_Found THEN
81 --debug(L_PROC_NAME||'no current effective version for this icc');
82 l_cur_icc_ver_id:=NULL;
83 END;
84
85
86 --debug(L_PROC_NAME||'version seq id=>'||l_cur_icc_ver_id);
87 --debug(L_PROC_NAME||'End');
88
89 return l_cur_icc_ver_id;
90
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 --debug(L_PROC_NAME||'No current effective version found for ICC id=> '||p_icc_id);
94 RETURN NULL;
95 WHEN OTHERS THEN
96 --debug(L_PROC_NAME||'Unexpected exception => '||SQLERRM);
97 RETURN NULL;
98 END Get_Current_ICC_Version;
99
100
101 --- =================================================================================
102 -- Name : Validate_ICC
103 -- Description : Validates the provided ICC id or name and returns TRUE
104 -- if they are valid. If the id is provided,
105 -- the name is derived and returned in p_icc_name
106 -- To validate the ICC name, the attribute p_icc_id must be null.
107 -- If ICC version p_icc_ver_id is provided, then it is validated too.
108 --
109 -- Scope : Private
110 --
111 --- =================================================================================
112 FUNCTION Validate_ICC(p_session_id IN NUMBER,
113 p_icc_id IN OUT NOCOPY VARCHAR2,
114 p_icc_name IN OUT NOCOPY VARCHAR2,
115 p_icc_ver_id IN OUT NOCOPY VARCHAR2
116 ) RETURN BOOLEAN
117 IS
118 l_index NUMBER;
119 l_mode VARCHAR2(10);
120 l_valid_icc_ver_id NUMBER;
121 l_end_date DATE;
122 L_PROC_NAME VARCHAR2(50);
123
124 BEGIN
125
126 L_PROC_NAME := ' Validate_ICC=> ';
127 --debug(L_PROC_NAME||' Start');
128 --debug(L_PROC_NAME||' p_session_id: '||p_session_id||' ,p_icc_id: '||p_icc_id||' ,p_icc_name: '||p_icc_name||' ,p_icc_ver_id: '||p_icc_ver_id);
129
130 l_mode := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'MODE', NULL, NULL);
131 --debug(L_PROC_NAME||' MODE: '||l_mode);
132
133 /* SOAPUI inserts '?' for null values and
134 in create_entities_icc procedure we read null or non-existant tag as '-1' */
135 IF ((p_icc_id = '?') or (p_icc_id = '-1')) THEN
136 p_icc_id := NULL;
137 END IF;
138
139 IF ((p_icc_ver_id = '?') or (p_icc_ver_id = '-1')) THEN
140 p_icc_ver_id := NULL;
141 END IF;
142
143 IF p_icc_name = '?' THEN
144 p_icc_name := NULL;
145 END IF;
146
147 --debug(L_PROC_NAME||' p_icc_id: '||p_icc_id||' ,p_icc_ver_id: '||p_icc_ver_id||' ,p_icc_name: '||p_icc_name);
148 /* Either icc_id or icc_name have to be provided*/
149 IF p_icc_id IS NULL AND p_icc_name IS NULL THEN
150 --debug(L_PROC_NAME||' both p_icc_id and p_icc_name are null');
151 RETURN FALSE;
152 END IF;
153
154 /* icc_id is given most priority. If it is given, then we derive the icc_name
155 Since catalog_group_name is not translatable, mtl_item_catalog_groups_v can also be used
156 instead of mtl_item_catalog_groups_b_kfv */
157
158 IF (p_icc_id IS NOT NULL) THEN
159
160 BEGIN
161 SELECT catalog_concat_segs
162 INTO p_icc_name
163 FROM mtl_item_catalog_groups_v
164 WHERE item_catalog_group_id = To_Number(p_icc_id);
165 /* kkq - do we have to check for inactive_date in the above query */
166 --debug(L_PROC_NAME||' p_icc_id: '||p_icc_id||' ,p_icc_name: '||p_icc_name);
167
168 EXCEPTION
169 WHEN NO_DATA_FOUND
170 THEN
171 --debug(L_PROC_NAME||' icc_name not found for p_icc_id: '||p_icc_id);
172 l_index := EGO_PUB_WS_UTIL.get_max_input_identifier(p_session_id);
173
174 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
175 p_input_id => l_index,
176 p_param_name => 'ICCId',
177 p_param_value => p_icc_id);
178
179 EGO_PUB_WS_UTIL.Log_Error( p_session_id => p_session_id,
180 p_input_id => l_index,
181 p_err_code => 'EGO_INVALID_ICC_ID',
182 p_err_message => 'Invalid Item Catalog Category ID.
183 The ICC Id entered by user is invalid in input XML.');
184
185 RETURN FALSE;
186 END;
187
188 END IF; /* p_icc_id IS NOT NULL */
189
190 /*if icc_name is given but not icc_id, then dervive icc_id.
191 This scenario will occur only in list mode but not batch mode*/
192 IF (p_icc_name IS NOT NULL AND p_icc_id IS NULL) THEN
193 BEGIN
194 SELECT To_Char(item_catalog_group_id)
195 INTO p_icc_id
196 FROM mtl_item_catalog_groups_v
197 WHERE catalog_concat_segs = p_icc_name;
198 /* kkq - do we have to check for inactive_date in the above query */
199 --debug(L_PROC_NAME||' p_icc_id: '||p_icc_id||' ,p_icc_name: '||p_icc_name);
200
201 EXCEPTION
202 WHEN NO_DATA_FOUND
203 THEN
204 --debug(L_PROC_NAME||' icc_id not found for p_icc_name: '||p_icc_name);
205 l_index := EGO_PUB_WS_UTIL.get_max_input_identifier(p_session_id);
206
207 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
208 p_input_id => l_index,
209 p_param_name => 'ICCName',
210 p_param_value => p_icc_name);
211
212 EGO_PUB_WS_UTIL.Log_Error( p_session_id => p_session_id,
213 p_input_id => l_index,
214 p_err_code => 'EGO_INVALID_ICC_NAME',
215 p_err_message => 'Invalid Item Catalog Category Name.
216 The ICC Name entered by user is invalid. ');
217
218 RETURN FALSE;
219 END;
220 END IF; /*p_icc_name IS NOT NULL AND p_icc_id IS NULL*/
221
222 /*Validating icc_ver_id against icc_id is needed only for LIST mode, since in BATCH mode, this validation
223 would have been done at the UI layer itself*/
224 IF (l_mode <> 'BATCH') THEN
225 IF ( ( p_icc_ver_id IS NOT NULL) AND (p_icc_id IS NOT NULL ) )THEN
226
227 BEGIN
228 SELECT version_seq_id, end_active_date
229 INTO l_valid_icc_ver_id, l_end_date
230 FROM ego_mtl_catalog_grp_vers_b
231 WHERE item_catalog_group_id = To_Number(p_icc_id)
232 AND version_seq_id = To_Number(p_icc_ver_id)
233 /*AND start_active_date <= SYSDATE commented this filter since we should be able to sync future eff versions*/
234 AND version_seq_id > 0;
235 --debug(L_PROC_NAME||' p_icc_id: '||p_icc_id||' ,p_icc_ver_id: '||p_icc_ver_id||' with end_date: '||l_end_date);
236
237 /* If version exists but is a past effective version, then log the error */
238 IF (l_end_date IS NOT NULL AND (l_end_date < SYSDATE)) THEN
239 --debug(L_PROC_NAME||'Versions with past end date cannot be synced');
240 l_index := EGO_PUB_WS_UTIL.get_max_input_identifier(p_session_id);
241
242 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
243 p_input_id => l_index,
244 p_param_name => 'ICCId',
245 p_param_value => p_icc_id);
246
247 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
248 p_input_id => l_index,
249 p_param_name => 'ICCVer',
250 p_param_value => p_icc_ver_id);
251
252 EGO_PUB_WS_UTIL.Log_Error( p_session_id => p_session_id,
253 p_input_id => l_index,
254 p_err_code => 'EGO_PAST_VER_NOT_SYNC',
255 p_err_message => 'Unable to publish versions with an invalid effective date.
256 User is trying to sync past effective ICC versions.');
257
258 RETURN FALSE;
259
260 END IF; /*l_end_date IS NOT NULL AND (l_end_date < SYSDATE)*/
261
262 EXCEPTION
263 WHEN NO_DATA_FOUND
264 THEN
265 /* When the version itself does not exist for that icc */
266 --debug(L_PROC_NAME||'Invalid ICC Version');
267 l_index := EGO_PUB_WS_UTIL.get_max_input_identifier(p_session_id);
268
269 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
270 p_input_id => l_index,
271 p_param_name => 'ICCId',
272 p_param_value => p_icc_id);
273
274 EGO_PUB_WS_UTIL.Populate_Input_Identifier( p_session_id => p_session_id,
275 p_input_id => l_index,
276 p_param_name => 'ICCVer',
277 p_param_value => p_icc_ver_id);
278
279 EGO_PUB_WS_UTIL.Log_Error( p_session_id => p_session_id,
280 p_input_id => l_index,
281 p_err_code => 'EGO_INVALID_ICC_VER',
282 p_err_message => 'Invalid Item Catalog Category Version.
283 The ICC Version Number entered by user is invalid. ');
284
285 RETURN FALSE;
286 END;
287 ELSIF (p_icc_ver_id IS NULL) THEN
288 /* derive the current effective version. */
289 --debug(L_PROC_NAME||'calling Get_Current_ICC_Version with p_icc_id: '||p_icc_id);
290 p_icc_ver_id := To_char(Get_Current_ICC_Version( To_Number(p_icc_id)));
291 --debug(L_PROC_NAME||'returned from Get_Current_ICC_Version with p_icc_ver_id: '||p_icc_ver_id);
292 /* If p_icc_ver_id from Get_Current_ICC_Version() comes out as NULL, it implies that either
293 - the ICC is non-versioned (non Telco envs)
294 - or the ICC is versioned but only has a draft version
295 In both cases, we don't throw an error, but simply treat the version as NULL
296 Since this is a valid scenario, we are NOT logging any error and NOT returning FALSE. */
297 END IF; /*( p_icc_ver_id IS NOT NULL) AND (p_icc_id IS NOT NULL )*/
298 END IF; /*l_mode <> 'BATCH*/
299
300 --debug(L_PROC_NAME||' p_session_id: '||p_session_id||' ,p_icc_id: '||p_icc_id||' ,p_icc_name: '||p_icc_name||' ,p_icc_ver_id: '||p_icc_ver_id);
301 --debug(L_PROC_NAME||' End');
302
303 RETURN TRUE;
304
305 EXCEPTION
306
307 WHEN OTHERS THEN
308 --debug(L_PROC_NAME||' When Others Exception');
309 --debug(' Error : '|| SQLERRM);
310 RETURN FALSE;
311
312 END Validate_ICC;
313
314
315 -----------------------------------------------------------------------------------------------
316 -- Procedure Name: Check_Duplicate_And_Insert --
317 -- This procedure will check if the given ICC ID and Version combination already --
318 -- exists in PL/SQL tables (p_icc_id_tab, p_icc_ver_id_tab). If it does not exits, it will --
319 -- insert ICC ID and version combination into PL/SQL tables (p_icc_id_tab, p_icc_ver_id_tab) --
320 -- Parameters: --
321 -- IN --
322 -- p_icc_id: Item Catalog Category ID. --
323 -- p_icc_ver_id: Item Catalog Category Version. --
324 -- p_icc_name: Item Catalog Category Name. --
325 -- --
326 -- Scope : Private --
327 -- --
328 -----------------------------------------------------------------------------------------------
329 PROCEDURE Check_Duplicate_And_Insert(
330 p_session_id NUMBER,
331 p_icc_id NUMBER,
332 p_icc_ver_id NUMBER,
333 p_icc_name VARCHAR2,
334 p_icc_id_tab IN OUT NOCOPY DBMS_SQL.number_table,
335 p_icc_ver_id_tab IN OUT NOCOPY DBMS_SQL.number_table,
336 p_icc_name_tab IN OUT NOCOPY DBMS_SQL.varchar2_table,
337 p_show_error_flag IN VARCHAR2,
338 p_user_entered_flag IN VARCHAR2
339 )
340 IS
341 l_is_duplicate BOOLEAN := FALSE;
342 num_of_records NUMBER;
343 L_PROC_NAME VARCHAR2(50);
344 BEGIN
345
346 L_PROC_NAME := ' Check_Duplicate_And_Insert=> ';
347 --debug(L_PROC_NAME||' Start');
348 --debug(L_PROC_NAME||' Parameters - p_session_id: '||p_session_id||' ,p_icc_id: '||p_icc_id||' ,p_icc_ver_id: '||p_icc_ver_id||' ,p_icc_name: '||p_icc_name);
349 --debug(L_PROC_NAME||' p_icc_id_tab.count: '||p_icc_id_tab.COUNT||' ,p_icc_ver_id_tab.count: '||p_icc_ver_id_tab.COUNT||' ,p_icc_name_tab.count: '||p_icc_name_tab.count);
350 --debug(L_PROC_NAME||' p_show_error_flag: '||p_show_error_flag||' ,p_user_entered_flag: '||p_user_entered_flag);
351
352 num_of_records := p_icc_id_tab.COUNT;
353
354 FOR counter IN 1 .. num_of_records
355 LOOP
356 IF ( p_icc_id = p_icc_id_tab(counter)
357 AND ( (p_icc_ver_id IS NULL
358 AND p_icc_ver_id_tab (counter) IS NULL
359 )
360 OR (
361 (p_icc_ver_id IS NOT NULL
362 AND p_icc_ver_id_tab (counter) IS NOT NULL
363 )
364 AND p_icc_ver_id = p_icc_ver_id_tab(counter)
365 )
366 )
367 )
368 THEN
369 l_is_duplicate := TRUE;
370
371 IF (p_show_error_flag = G_SHOW_ERROR_FLAG_YES) THEN
372 /* p_show_error_flag will be Y only
373 - for version records for versioned ICCs
374 - for ICC records for non-versioned ICCs */
375 UPDATE ego_pub_ws_entities
376 SET ref10_value = G_SHOW_ERROR_FLAG_YES
377 WHERE (session_id = p_session_id
378 AND pk1_value = p_icc_id
379 AND (p_icc_ver_id IS NULL
380 OR (p_icc_ver_id IS NOT NULL AND pk2_value = p_icc_ver_id))
381 );
382 END IF; /* p_show_error_flag */
383
384 END IF; /* p_icc_id main condition*/
385 END LOOP; /* counter*/
386
387 /*If not duplicate then, insert the record into entities table.
388 Note that parent_sequence_id column is inserted as NULL.
389 This is because we do not maintain info about parent-child ICC hierarchy info
390 in this ego_pub_ws_entities table. Every ICC (though it was derived as a parent/child ICC
391 in create_entities_icc) is treated as an independent ICC.*/
392 IF (NOT l_is_duplicate)
393 THEN
394 p_icc_id_tab (num_of_records + 1) := p_icc_id;
395 p_icc_ver_id_tab (num_of_records + 1) := p_icc_ver_id;
396 p_icc_name_tab (num_of_records + 1) := p_icc_name;
397
398 /*In ego_pub_ws_entities, we are reusing the ref10_value column for storing show_error_flag*/
399 INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
400 ODI_SESSION_ID,
401 SEQUENCE_ID,
402 PARENT_SEQUENCE_ID,
403 ENTITY_TYPE,
404 PK1_VALUE,
405 PK2_VALUE,
406 PK3_VALUE,
407 PK4_VALUE,
408 PK5_VALUE,
409 REF1_VALUE,
410 REF2_VALUE,
411 REF3_VALUE,
412 REF4_VALUE,
413 REF5_VALUE,
414 REF6_VALUE,
415 REF7_VALUE,
416 REF8_VALUE,
417 REF9_VALUE,
418 REF10_VALUE,
419 CREATION_DATE,
420 CREATED_BY,
421 USER_ENTERED)
422 VALUES( p_session_id,
423 p_session_id,
424 EGO_PUB_WS_ENTITIES_S.nextval,
425 NULL,
426 G_ENTITY_TYPE_ICC,
427 p_icc_id,
428 p_icc_ver_id,
429 NULL,
430 NULL,
431 NULL,
432 p_icc_name,
433 NULL,
434 NULL,
435 NULL,
436 NULL,
437 NULL,
438 NULL,
439 NULL,
440 NULL,
441 p_show_error_flag,
442 SYSDATE,
443 G_CURRENT_USER_ID,
444 p_user_entered_flag
445 );
446
447 END IF; /* NOT l_is_duplicate*/
448
449 --debug(L_PROC_NAME||' End');
450
451 END check_duplicate_and_insert;
452
453 -- ============================================================================
454 -- Name : Create_Params_ICC
455 -- Description : This procedure will determine the mode- BATCH/LIST and insert
456 -- the appropriate config parameters in the ego_pub_ws_config table
457 -- Called from Preprocess_Input_ICC
458 --
459 -- Scope : Private
460 --
461 -- Parameters:
462 -- IN :
463 -- p_session_id IN NUMBER
464 -- An Unique DB sequence generated at Java wrapper.
465 --
466 -- ============================================================================
467 PROCEDURE Create_Params_ICC(p_session_id IN NUMBER)
468 IS
469 l_web_service_name VARCHAR2(100);
470 l_mode VARCHAR2(10);
471 l_batch_id NUMBER ;
472 l_config_option VARCHAR2(100);
473 l_language_search_str VARCHAR2(1000);
474 l_param_parent_hier VARCHAR2(5);
475 l_param_child_hier VARCHAR2(5);
476 l_trigger_import VARCHAR2(1);
477 l_publish_sync VARCHAR2(10);
478
479 --array to store XML path expressions to retrieve single-value params
480 l_xpath_expr EGO_PUB_WS_UTIL.xpath_expr_array_type;
481
482 --array to store single_value parameter names
483 l_param_names EGO_PUB_WS_UTIL.parameter_name_array_type;
484
485 L_PROC_NAME VARCHAR2(50);
486 BEGIN
487
488 L_PROC_NAME := ' Create_Params_ICC=> ';
489 --debug(L_PROC_NAME||' Start');
490 --debug(L_PROC_NAME||' Parameters - p_session_id: '||p_session_id);
491
492 l_mode := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'MODE', NULL, NULL);
493 --debug(L_PROC_NAME||'Param Value of MODE: '||l_mode);
494
495 --get web service name
496 SELECT WEB_SERVICE_NAME
497 INTO l_web_service_name
498 FROM EGO_PUB_WS_PARAMS
499 WHERE SESSION_ID = p_session_id;
500
501 --debug(L_PROC_NAME||' WEB_SERVICE_NAME: '||l_web_service_name);
502
503
504 IF l_mode = 'BATCH' THEN
505 /*BATCH_ID param is available only in ego_pub_ws_config and not in ego_pub_bat_params_b.
506 The Batch UI does not insert this param. The ServiceUtil inserts it into ego_pub_ws_config.
507 Hence for fetching the BATCH_ID, we are passing the mode as NULL.*/
508 l_batch_id := EGO_PUB_WS_UTIL.Get_Numeric_Param_Value(p_session_id, 'BATCH_ID', NULL, NULL);
509 --debug(L_PROC_NAME||'Batch_Id: '||l_batch_id);
510
511 /* In Batch Mode, the following parameters have to be taken from EGO_PUB_BAT_PARAMS_B table
512 PARENTICCS,CHILDICCS,TRIGGER_IMPORT(aka LAUNCH_SYNC_CONC_PROG),SYNC (aka BATCH_PROCESS_TYPE)
513 and the following params have to be defaulted to TRUE only
514 USERDEFATTRGRPS,VALUESETS,ICCVERSIONS, TRANSATTRS,ICCPAGES,ICCFUNCTIONS,ICCSTRUCTURE, RETURN_PAYLOAD
515 */
516
517 --initialize arrays of parameter names
518 --debug(L_PROC_NAME||'Batch Mode Calling EGO_PUB_WS_UTIL.Get_Parameter_Names procedure');
519 l_param_names := EGO_PUB_WS_UTIL.parameter_name_array_type();
520 EGO_PUB_WS_UTIL.Get_Parameter_Names(l_web_service_name, l_param_names);
521 --debug(L_PROC_NAME||'Batch Mode, Returned from EGO_PUB_WS_UTIL.Get_Parameter_Names procedure, l_param_names.count: '||l_param_names.count);
522
523
524 --retrieve all single-value parameters of interest from EGO_PUB_BAT_PARAMS_B table
525 --and store them in table EGO_PUB_WS_CONFIG
526 FOR position IN 1..l_param_names.COUNT
527 LOOP
528 --debug(L_PROC_NAME||'Batch Mode, Inside For Loop on l_param_names.count, position: '||position);
529
530 /* The below insert takes care of all params that have to be defaulted to TRUE */
531 IF l_param_names(position) NOT IN ('PARENTICCS','CHILDICCS','SYNC', 'TRIGGER_IMPORT') THEN
532 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
533 odi_session_id,
534 Parameter_Name,
535 Data_Type,
536 Char_value,
537 creation_date,
538 created_by,
539 web_service_name)
540 VALUES (p_session_id,
541 p_session_id,
542 l_param_names(position),
543 2,
544 G_CHAR_TRUE,
545 sysdate,
546 G_CURRENT_USER_ID,
547 l_web_service_name);
548
549 END IF;
550 END LOOP;
551
552 /* The below insert statements take care of the params that have to be taken from EGO_PUB_BAT_PARAMS_B table*/
553
554 /* Batch Mode param PublishParent will be inserted into ego_pub_ws_config as PARENTICCS */
555 l_param_parent_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id => p_session_id,
556 p_param_name => 'PublishParent',
557 p_batch_id => l_batch_id,
558 p_mode => 'BATCH'
559 );
560
561 BEGIN
562 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
563 odi_session_id,
564 Parameter_Name,
565 Data_Type,
566 Char_value,
567 creation_date,
568 created_by,
569 web_service_name)
570 VALUES (p_session_id,
571 p_session_id,
572 'PARENTICCS',
573 2,
574 upper(l_param_parent_hier), -- bug 12377269
575 sysdate,
576 G_CURRENT_USER_ID,
577 l_web_service_name);
578 --- Added exception block bug 12755038
579 EXCEPTION
580 WHEN DUP_VAL_ON_INDEX then
581 NULL;
582 END;
583
584
585 /* Batch Mode param PublishChild will be inserted into ego_pub_ws_config as CHILDICCS */
586 l_param_child_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value ( p_session_id => p_session_id,
587 p_param_name => 'PublishChild',
588 p_batch_id => l_batch_id,
589 p_mode => 'BATCH'
590 );
591
592 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
593 odi_session_id,
594 Parameter_Name,
595 Data_Type,
596 Char_value,
597 creation_date,
598 created_by,
599 web_service_name)
600 VALUES (p_session_id,
601 p_session_id,
602 'CHILDICCS',
603 2,
604 upper(l_param_child_hier),-- bug 12377269
605 sysdate,
606 G_CURRENT_USER_ID,
607 l_web_service_name);
608
609 /* Batch Mode param Launch Sync Program will be inserted into ego_pub_ws_config as TRIGGER_IMPORT */
610 l_trigger_import := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id => p_session_id,
611 p_param_name => EGO_PUB_WS_UTIL.G_TRIGGER_IMPORT_PARAM,
612 p_batch_id => l_batch_id,
613 p_mode => 'BATCH'
614 );
615
616 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
617 odi_session_id,
618 Parameter_Name,
619 Data_Type,
620 Char_value,
621 creation_date,
622 created_by,
623 web_service_name)
624 VALUES (p_session_id,
625 p_session_id,
626 'TRIGGER_IMPORT',
627 2,
628 NVL(l_trigger_import,'N'),
629 sysdate,
630 G_CURRENT_USER_ID,
631 l_web_service_name);
632
633
634 /* Batch Mode param BATCH_PROCESS_TYPE=PUBLISH/SYNC will be inserted into ego_pub_ws_config as SYNC=Y/N */
635 l_publish_sync := EGO_PUB_WS_UTIL.Get_Char_Param_Value ( p_session_id => p_session_id,
636 p_param_name => EGO_PUB_WS_UTIL.G_SYNC_PARAM,
637 p_batch_id => l_batch_id,
638 p_mode => 'BATCH'
639 );
640 IF l_publish_sync = 'PUBLISH' THEN
641 l_publish_sync := 'N';
642 ELSIF l_publish_sync = 'SYNC' THEN
643 l_publish_sync := 'Y';
644 END IF;
645
646 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
647 odi_session_id,
648 Parameter_Name,
649 Data_Type,
650 Char_value,
651 creation_date,
652 created_by,
653 web_service_name)
654 VALUES (p_session_id,
655 p_session_id,
656 'SYNC',
657 2,
658 l_publish_sync,
659 sysdate,
660 G_CURRENT_USER_ID,
661 l_web_service_name);
662
663 /*End of populating config_params for batch mode*/
664 ELSE /* If l_mode='LIST' THEN */
665
666 l_batch_id := NULL;
667 --
668 --STEP ONE: RETRIEVE ALL SINGLE-VALUE CONFIGURATION PARAMETERS
669 -- AND STORE THEM IN TABLE EGO_PUB_WS_CONFIG
670 --
671
672 --initialize arrays of parameter names
673 --debug(L_PROC_NAME||'List Mode, Calling EGO_PUB_WS_UTIL.Get_Parameter_Names procedure');
674 l_param_names := EGO_PUB_WS_UTIL.parameter_name_array_type();
675 EGO_PUB_WS_UTIL.Get_Parameter_Names(l_web_service_name, l_param_names);
676 --debug(L_PROC_NAME||'List Mode, Returned from EGO_PUB_WS_UTIL.Get_Parameter_Names procedure, l_param_names.count: '||l_param_names.count);
677
678
679 --debug(L_PROC_NAME||'List Mode, Calling EGO_PUB_WS_UTIL.Get_Xpath_Expr procedure');
680 l_xpath_expr := EGO_PUB_WS_UTIL.xpath_expr_array_type();
681 EGO_PUB_WS_UTIL.Get_Xpath_Expr(l_web_service_name, l_xpath_expr);
682 --debug(L_PROC_NAME||'List Mode, Returned from EGO_PUB_WS_UTIL.Get_Xpath_Expr procedure, l_xpath_expr.count: '||l_xpath_expr.count);
683
684 --retrieve all single-value parameters of interest from XML
685 --and store them in table EGO_PUB_WS_CONFIG
686 FOR position IN 1..l_param_names.COUNT
687 LOOP
688 --debug(L_PROC_NAME||'List Mode, Inside For Loop on l_param_names.count, position: '||position||' ,l_xpath_expr: '||l_xpath_expr(position));
689
690 l_config_option := upper(EGO_PUB_WS_UTIL.Get_ODI_Input_Parameter(p_session_id, l_xpath_expr(position)));
691 --debug(L_PROC_NAME||'List Mode, l_config_option: '||l_config_option);
692
693 --if parameter is not provided, assume a default value of 'TRUE'
694
695 IF l_config_option IS NOT NULL AND l_config_option <> '?' THEN
696 --debug(L_PROC_NAME||'List Mode, Insert into EGO_PUB_WS_CONFIG, Parameter_Name: '||l_param_names(position)||' ,l_config_option: '||l_config_option);
697
698 /* In the below insert stmt, we are hardcoding data_type as 2 and providing the char value,
699 it implies that we are assuming all config params are of string type.
700 All config params except TRIGGER_IMPORT, SYNC take TRUE/FALSE (as strings not boolean).
701 TRIGGER_IMPORT alone takes it as Y/N. SYNC takes SYNC/PUBLISH */
702 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
703 odi_session_id,
704 Parameter_Name,
705 Data_Type,
706 Char_value,
707 creation_date,
708 created_by,
709 web_service_name)
710 VALUES (p_session_id,
711 p_session_id,
712 l_param_names(position),
713 2,
714 l_config_option,
715 sysdate,
716 G_CURRENT_USER_ID,
717 l_web_service_name);
718 END IF;
719 END LOOP;
720 END IF; /* l_mode */
721
722 /*In both Batch and List Mode, hard-code the param CHILD_VALUESETS as Y.
723 This param is NOT available in ICC xml. So, always set it to Y so that
724 child VS will get synced. Explode_ICC() will call EGO_PUB_WS_AG.Explode_Attribute_Group() procedure.
725 It inturn looks for this param and decides whether to call EGO_PUB_WS_VS.Explode_Value_Set() or not
726 If ICC does not hard-code this param, Explode_Attribute_Group will raise a no-data-found exception.*/
727 --debug(L_PROC_NAME||'Batch,List Mode, Insert into EGO_PUB_WS_CONFIG, Parameter_Name: CHILD_VALUESETS with Value =TRUE');
728 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
729 odi_session_id,
730 Parameter_Name,
731 Data_Type,
732 Char_value,
733 creation_date,
734 created_by,
735 web_service_name)
736 VALUES (p_session_id,
737 p_session_id,
738 'CHILD_VALUESETS',
739 2,
740 G_CHAR_YES,
741 sysdate,
742 G_CURRENT_USER_ID,
743 l_web_service_name);
744
745 --debug(L_PROC_NAME||'calling EGO_PUB_WS_UTIL.Create_Fnd_Security procedure');
746 EGO_PUB_WS_UTIL.Create_Fnd_Security(p_session_id,
747 l_mode,
748 l_batch_id,
749 l_web_service_name
750 );
751 --
752 --STEP TWO: RETRIEVE ALL MULTI-VALUE CONFIGURATION PARAMETERS
753 -- AND STORE THEM IN TABLE EGO_PUB_WS_CONFIG
754 --
755
756 --RETRIEVING LIST OF LANGUAGES
757 l_language_search_str := EGO_PUB_WS_UTIL.Get_Language_Search_Str(l_web_service_name);
758
759 --Inserts language options in Config table
760 --debug(L_PROC_NAME||'calling EGO_PUB_WS_UTIL.Config_Languages procedure');
761 EGO_PUB_WS_UTIL.Config_Languages( p_session_id,
762 l_language_search_str,
763 l_web_service_name);
764
765 --debug(L_PROC_NAME||' End');
766
767
768 EXCEPTION
769 --- bug 12755038 , if parallel session is trying to insert the same
770 --- record into EGO_PUB_WS_CONFIG, consume the exception , unique
771 --- index added to prevent duplicates from getting inerted which would subsequently fail
772 --- when the rows were queried later.
773 WHEN DUP_VAL_ON_INDEX then
774 NULL;
775 END Create_Params_ICC;
776
777 --
778 -- Bug 12377269. Added below methods to support
779 -- fetching herirarchical ICCs
780 -- sreharih. Tue Apr 19 18:26:45 PDT 2011
781 --
782
783 /*
784 PROCEDURE test (p_item_catalog_group_id IN NUMBER,
785 p_version_seq_id IN NUMBER,
786 p_get_children IN VARCHAR2,
787 p_get_parents IN VARCHAR2,
788 p_only_latest IN VARCHAR2) IS
789
790 l_hierarchical_iccs t_icc_id_recs;
791 BEGIN
792 get_hierarchical_iccs (p_item_catalog_group_id ,
793 p_version_seq_id ,
794 p_get_children ,
795 p_get_parents ,
796 p_only_latest ,
797 l_hierarchical_iccs);
798
799 IF l_hierarchical_iccs IS NOT NULL AND l_hierarchical_iccs.COUNT > 0 THEN
800 FOR i IN l_hierarchical_iccs.FIRST .. l_hierarchical_iccs.LAST LOOP
801 --debug('ICC: ' || l_hierarchical_iccs(i).catalog_concat_segs || ' version ' || l_hierarchical_iccs(i).version_seq_id || ' id ' || l_hierarchical_iccs(i).item_catalog_group_id);
802 END LOOP;
803 END IF;
804
805 END test;
806
807 */
808
809 --
810 -- Get active date for given ICC and version
811 --
812 FUNCTION get_active_date(
813 p_item_catalog_group_id IN NUMBER,
814 p_version_seq_id IN NUMBER) RETURN DATE IS
815
816 l_ret ego_mtl_catalog_grp_vers_b.end_active_date%TYPE;
817
818 CURSOR c
819 IS
820 SELECT NVL(vers.end_active_date,SYSDATE)
821 FROM ego_mtl_catalog_grp_vers_b vers
822 WHERE item_catalog_group_id = p_item_catalog_group_id
823 AND version_seq_id = NVL(p_version_seq_id,0);
824
825 BEGIN
826
827 OPEN c;
828 FETCH c INTO l_ret;
829 CLOSE c;
830
831 RETURN l_ret;
832
833 END get_active_date;
834
835 --
836 -- Append source to target
837 --
838 PROCEDURE append_recs(
839 x_target IN OUT NOCOPY t_icc_id_recs,
840 p_source IN t_icc_id_recs) IS
841
842 j INTEGER;
843 BEGIN
844 IF p_source IS NOT NULL AND p_source.COUNT > 0 THEN
845 IF x_target IS NOT NULL THEN
846 j := x_target.COUNT + 1;
847 ELSE
848 j := 1;
849 END IF;
850 FOR i IN p_source.FIRST .. p_source.LAST
851 LOOP
852 x_target(j) := p_source(i);
853 j := j + 1;
854 END LOOP;
855 END IF;
856
857 END append_recs;
858
859
860 --
861 -- Return only the latest version of the ICC
862 -- Assumes ICC is sent in desc order of active date.
863 --
864 FUNCTION get_latest_versions(
865 p_icc_id_recs IN t_icc_id_recs ) RETURN t_icc_id_recs IS
866
867 j INTEGER;
868 l_icc_id_recs t_icc_id_recs;
869 BEGIN
870 IF p_icc_id_recs IS NOT NULL AND p_icc_id_recs.COUNT > 0 THEN
871
872 j := 1;
873
874 FOR i IN p_icc_id_recs.FIRST .. p_icc_id_recs.LAST
875 LOOP
876 IF j = 1 OR l_icc_id_recs(j-1).item_catalog_group_id <> p_icc_id_recs(i).item_catalog_group_id THEN
877 l_icc_id_recs(j) := p_icc_id_recs(i);
878 j := j + 1;
879 END IF;
880 END LOOP;
881
882 END IF;
883
884 RETURN l_icc_id_recs;
885
886 END get_latest_versions;
887
888 --
889 -- Get all applicable children
890 --
891 FUNCTION get_children(
892 p_item_catalog_group_id IN NUMBER,
893 P_ACTIVE_DATE IN DATE,
894 p_versions_from IN NUMBER DEFAULT 0,
895 p_get_versions IN BOOLEAN DEFAULT TRUE) -- for bug12693782
896 RETURN t_icc_id_recs
897 IS
898 l_ret t_icc_id_recs;
899
900
901 --
902 -- Bug 12693782. Child 0 version was also getting picked up.
903 -- Split the cursor into two for better readability. That will
904 -- automatically fix the issue when we remove the outer join.
905 -- sreharih. Tue Jul 5 13:17:32 PDT 2011
906 --
907 CURSOR c_with_version
908 IS
909 SELECT hier.item_catalog_group_id,
910 iccb.version_seq_id,
911 cat.catalog_concat_segs
912 FROM mtl_item_catalog_groups_v cat,
913 (SELECT item_catalog_group_id,
914 LEVEL lev
915 FROM mtl_item_catalog_groups_b
916 START WITH item_catalog_group_id = p_item_catalog_group_id
917 CONNECT BY PRIOR item_catalog_group_id = parent_catalog_group_id
918 ) hier,
919 ego_mtl_catalog_grp_vers_b iccb
920 WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
921 AND hier.lev > 1
922 AND iccb.item_catalog_group_id = hier.item_catalog_group_id
923 AND iccb.version_seq_id >= p_versions_from
924 AND iccb.start_active_date <= p_active_date
925 ORDER BY hier.item_catalog_group_id,
926 iccb.start_active_date DESC;
927
928 CURSOR c_without_version
929 IS
930 SELECT hier.item_catalog_group_id,
931 null version_seq_id,
932 cat.catalog_concat_segs
933 FROM mtl_item_catalog_groups_v cat,
934 (SELECT item_catalog_group_id,
935 LEVEL lev
936 FROM mtl_item_catalog_groups_b
937 START WITH item_catalog_group_id = p_item_catalog_group_id
938 CONNECT BY PRIOR item_catalog_group_id = parent_catalog_group_id
939 ) hier
940 WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
941 AND hier.lev > 1
942 ORDER BY hier.item_catalog_group_id;
943
944 BEGIN
945
946 IF p_get_versions THEN
947
948 OPEN c_with_version;
949 FETCH c_with_version BULK COLLECT INTO l_ret;
950 CLOSE c_with_version;
951
952 ELSE
953
954 OPEN c_without_version;
955 FETCH c_without_version BULK COLLECT INTO l_ret;
956 CLOSE c_without_version;
957
958 END IF;
959
960 RETURN l_ret;
961
962 END get_children;
963
964 --
965 -- Get all applicable Parents
966 --
967 FUNCTION get_parents(
968 p_item_catalog_group_id IN NUMBER,
969 p_active_date IN DATE,
970 p_versions_from IN NUMBER DEFAULT 0,
971 p_get_versions IN BOOLEAN DEFAULT TRUE) -- for bug12693782
972 RETURN t_icc_id_recs
973 IS
974
975 l_ret t_icc_id_recs;
976
977 --
978 -- Bug 12693782. Child 0 version was also getting picked up.
979 -- Split the cursor into two for better readability. That will
980 -- automatically fix the issue when we remove the outer join.
981 -- sreharih. Tue Jul 5 13:17:32 PDT 2011
982 --
983
984 CURSOR c_with_version
985 IS
986 SELECT hier.item_catalog_group_id,
987 iccb.version_seq_id,
988 cat.catalog_concat_segs
989 FROM mtl_item_catalog_groups_v cat,
990 (SELECT item_catalog_group_id,
991 LEVEL lev
992 FROM mtl_item_catalog_groups_b
993 START WITH item_catalog_group_id = p_item_catalog_group_id
994 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
995 ) hier,
996 ego_mtl_catalog_grp_vers_b iccb
997 WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
998 AND hier.lev > 1
999 AND iccb.item_catalog_group_id = hier.item_catalog_group_id
1000 AND iccb.version_seq_id >= p_versions_from
1001 AND iccb.start_active_date <= p_active_date
1002 ORDER BY hier.item_catalog_group_id,
1003 iccb.start_active_date DESC;
1004
1005 CURSOR c_without_version
1006 IS
1007 SELECT hier.item_catalog_group_id,
1008 null version_seq_id,
1009 cat.catalog_concat_segs
1010 FROM mtl_item_catalog_groups_v cat,
1011 (SELECT item_catalog_group_id,
1012 LEVEL lev
1013 FROM mtl_item_catalog_groups_b
1014 START WITH item_catalog_group_id = p_item_catalog_group_id
1015 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
1016 ) hier
1017 WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
1018 AND hier.lev > 1
1019 ORDER BY hier.item_catalog_group_id;
1020
1021 BEGIN
1022
1023 IF p_get_versions THEN
1024
1025 OPEN c_with_version;
1026 FETCH c_with_version BULK COLLECT INTO l_ret;
1027 CLOSE c_with_version;
1028
1029 ELSE
1030
1031 OPEN c_without_version;
1032 FETCH c_without_version BULK COLLECT INTO l_ret;
1033 CLOSE c_without_version;
1034
1035 END IF;
1036
1037 RETURN l_ret;
1038
1039 END get_parents;
1040
1041 --
1042 -- Get all applicable children and parents.
1043 --
1044 PROCEDURE get_hierarchical_iccs(
1045 p_item_catalog_group_id IN NUMBER,
1046 p_version_seq_id IN NUMBER,
1047 p_get_children IN VARCHAR2,
1048 p_get_parents IN VARCHAR2,
1049 p_only_latest IN VARCHAR2,
1050 x_hierarchical_iccs OUT NOCOPY t_icc_id_recs)
1051 IS
1052 l_children t_icc_id_recs;
1053 l_parents t_icc_id_recs;
1054 l_hier_iccs t_icc_id_recs;
1055 l_active_date ego_mtl_catalog_grp_vers_b.end_active_date%TYPE;
1056 l_versions_from NUMBER;
1057 l_get_versions BOOLEAN;
1058 L_PROC_NAME VARCHAR2(50);
1059
1060 BEGIN
1061 L_PROC_NAME := ' get_hierarchical_iccs=> ';
1062 --debug(L_PROC_NAME||' Start');
1063 --debug(L_PROC_NAME||' Parameters - p_item_catalog_group_id: '||p_item_catalog_group_id||' p_version_seq_id: '||p_version_seq_id);
1064 --debug(L_PROC_NAME||' Parameters - p_get_children: '||p_get_children||' p_get_parents: '||p_get_parents||' p_only_latest: '|| p_only_latest);
1065
1066 l_active_date := get_active_date(p_item_catalog_group_id => p_item_catalog_group_id, p_version_seq_id => p_version_seq_id);
1067
1068 --debug(L_PROC_NAME||' after proc get_active_date, l_active_date: '||To_Char(l_active_date,'dd-mon-yyyy hh24:mi:ss'));
1069
1070 IF p_version_seq_id IS NULL THEN
1071 l_get_versions := FALSE;
1072 l_versions_from := NULL;
1073 ELSE
1074 l_get_versions := TRUE;
1075 l_versions_from := 1;
1076 END IF;
1077
1078 --debug(L_PROC_NAME||' l_versions_from: '||l_versions_from);
1079
1080 IF UPPER(p_get_children) = 'TRUE' THEN
1081 --debug(L_PROC_NAME||' before call to proc get_children');
1082 l_children := get_children( p_item_catalog_group_id => p_item_catalog_group_id, p_active_date => l_active_date, p_versions_from => l_versions_from, p_get_versions => l_get_versions );
1083 --debug(L_PROC_NAME||' after call to proc get_children');
1084
1085 IF l_children IS NOT NULL AND l_children.COUNT > 0 THEN
1086 --debug(L_PROC_NAME||' before call to proc append_recs for get_children');
1087 append_recs(x_target => l_hier_iccs, p_source => l_children);
1088 --debug(L_PROC_NAME||' after call to proc append_recs for get_children');
1089 END IF;
1090
1091 END IF;
1092
1093 IF UPPER(p_get_parents) = 'TRUE' THEN
1094 --debug(L_PROC_NAME||' before call to proc get_parents');
1095 l_parents := get_parents( p_item_catalog_group_id => p_item_catalog_group_id, p_active_date => l_active_date, p_versions_from => l_versions_from, p_get_versions => l_get_versions );
1096 --debug(L_PROC_NAME||' after call to proc get_parents');
1097
1098 IF l_parents IS NOT NULL AND l_parents.COUNT > 0 THEN
1099 --debug(L_PROC_NAME||' before call to proc append_recs for get_parents');
1100 append_recs(x_target => l_hier_iccs, p_source => l_parents);
1101 --debug(L_PROC_NAME||' after call to proc append_recs for get_parents');
1102 END IF;
1103 END IF;
1104
1105 IF UPPER(p_only_latest) = 'TRUE' THEN
1106 --debug(L_PROC_NAME||' p_only_latest is TRUE, before call to proc get_latest_versions');
1107 x_hierarchical_iccs := get_latest_versions(l_hier_iccs);
1108 --debug(L_PROC_NAME||' after call to proc get_latest_versions');
1109 ELSE
1110 --debug(L_PROC_NAME||' p_only_latest is FALSE');
1111 x_hierarchical_iccs := l_hier_iccs;
1112 END IF;
1113
1114 --debug(L_PROC_NAME||' End');
1115 END get_hierarchical_iccs;
1116
1117
1118
1119
1120 -- =========================================================================================================
1121 -- Name : Create_Entities_ICC
1122 -- Description : This procedure performs the following actions:
1123 -- 1. Populates ODI input table EGO_PUB_WS_ENTITIES for entity ICC
1124 -- based on the invokation type (e.g. batch, list)
1125 -- 2. Based on the config params PARENTICCS (PublishParent) and CHILDICCS (PublishChild)
1126 -- derive the parent and child hierarchy of the given ICCs and
1127 -- populate them into EGO_PUB_WS_ENTITIES
1128 -- This procedure is called from Preprocess_Input_ICC
1129 --
1130 -- Scope : Private
1131 --
1132 -- Parameters:
1133 -- IN :
1134 -- p_session_id IN NUMBER
1135 -- An Unique DB sequence generated at Java wrapper.
1136 --
1137 -- ===========================================================================================================
1138 PROCEDURE Create_Entities_ICC(p_session_id IN NUMBER)
1139 IS
1140
1141 l_mode VARCHAR2(100);
1142 l_batch_id NUMBER;
1143 l_param_parent_hier VARCHAR2(5);
1144 l_param_child_hier VARCHAR2(5);
1145
1146 /*Netsed table of varchar2 to store ICCId's.
1147 It is a varchar2 table b'cos we have to handle '?' when icc_id null.
1148 SOAP UI tool passes null values as '?' */
1149 l_dup_icc_id_tab DBMS_SQL.varchar2_table;
1150 l_dup_icc_name_tab DBMS_SQL.varchar2_table;
1151 l_dup_icc_ver_id_tab DBMS_SQL.varchar2_table;
1152
1153 /* The below tables are for storing validated and non-duplicate values.
1154 Note that we are using number tables for icc_id, icc_ver_id */
1155 l_icc_id_tab DBMS_SQL.number_table;
1156 l_icc_name_tab DBMS_SQL.varchar2_table;
1157 l_icc_ver_id_tab DBMS_SQL.number_table;
1158
1159 l_is_duplicate BOOLEAN := FALSE;
1160 l_is_valid BOOLEAN;
1161
1162 L_PROC_NAME VARCHAR2(50);
1163 l_heirarchical_iccs t_icc_id_recs; -- bug 12377269
1164
1165 BEGIN
1166 L_PROC_NAME := ' Create_Entities_ICC=> ';
1167 --debug(L_PROC_NAME||' Start');
1168 --debug(L_PROC_NAME||' Parameters - p_session_id: '||p_session_id);
1169
1170 l_mode := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'MODE', NULL, NULL);
1171 --debug(L_PROC_NAME||'MODE: '||l_mode);
1172
1173 --populate odi input table ego_pub_ws_entities depending on mode from different data sources (i.e.) batch, list
1174 CASE
1175
1176 --if mode is batch, get information from publication framework using batch_id
1177 WHEN l_mode = 'BATCH' THEN
1178 l_batch_id := EGO_PUB_WS_UTIL.Get_Numeric_Param_Value(p_session_id,'BATCH_ID', NULL, NULL);
1179 --debug(L_PROC_NAME||'Batch_Id: '||l_batch_id);
1180
1181 --retrieving all ICCs in batch from publication framework entity tables
1182 --and inserting data into ODI metadata input table ego_pub_ws_entities
1183
1184
1185 IF l_batch_id IS NOT NULL THEN
1186 /*we are using to_char since l_dup_icc_id_tab, l_dup_icc_ver_id_tab are varchar2 tables
1187 In batch mode, icc_name will not be present in Ego_Pub_Bat_Ent_Objs_v, so we are collecting it as null*/
1188 SELECT To_Char(pk1_value) , NULL, To_Char(pk2_value)
1189 BULK COLLECT INTO l_dup_icc_id_tab, l_dup_icc_name_tab, l_dup_icc_ver_id_tab
1190 FROM Ego_Pub_Bat_Ent_Objs_v
1191 WHERE batch_id = l_batch_id
1192 AND user_entered = G_USER_ENTERED_FLAG_YES;
1193 --debug(L_PROC_NAME||'l_dup_icc_id_tab.count: '||l_dup_icc_id_tab.Count||' ,l_dup_icc_name_tab.count: '||l_dup_icc_name_tab.Count||' ,l_dup_icc_ver_id_tab.count: '||l_dup_icc_ver_id_tab.Count);
1194 END IF; /*l_batch_id IS NOT NULL*/
1195
1196 WHEN l_mode = 'LIST' THEN
1197
1198 /*Extract value into array if ICCId node exist in Input table
1199 If the ICCId node does not exist or has a null value, insert -1 as value*/
1200 SELECT Decode(existsNode(ICC_Identifier, '/ICCIdentifier/ICCId'), 1, Nvl(extractValue(ICC_Identifier, '/ICCIdentifier/ICCId'), -1), 0, -1)
1201 BULK COLLECT INTO l_dup_icc_id_tab
1202 FROM (SELECT VALUE (ICCIdentifier) ICC_Identifier
1203 FROM ego_pub_ws_params i,
1204 TABLE
1205 (XMLSEQUENCE
1206 (EXTRACT
1207 (i.xmlcontent,
1208 '/ICCQueryParam/ICCIdentifiersList/ICCIdentifier'
1209 )
1210 )
1211 ) ICCIdentifier
1212 WHERE session_id = p_session_id);
1213
1214 /*Extract value into array if ICCName node exist in Input table*/
1215 SELECT EXTRACTVALUE (icc_name, '/ICCName')
1216 BULK COLLECT INTO l_dup_icc_name_tab
1217 FROM (SELECT VALUE (iccname) icc_name
1218 FROM ego_pub_ws_params i,
1219 TABLE
1220 (XMLSEQUENCE
1221 (EXTRACT
1222 (i.xmlcontent,
1223 '/ICCQueryParam/ICCIdentifiersList/ICCIdentifier/ICCName'
1224 )
1225 )
1226 ) iccname
1227 WHERE session_id = p_session_id);
1228
1229 /*Extract value into array if VersionId node exist in Input table.
1230 If the VersionId node does not exist or has a null value, insert -1 as value*/
1231 SELECT Decode(existsNode(ICC_Identifier, '/ICCIdentifier/VersionSequence'), 1, Nvl(extractValue(ICC_Identifier, '/ICCIdentifier/VersionSequence'), -1), 0, -1)
1232 BULK COLLECT INTO l_dup_icc_ver_id_tab
1233 FROM (SELECT VALUE (ICCIdentifier) ICC_Identifier
1234 FROM ego_pub_ws_params i,
1235 TABLE
1236 (XMLSEQUENCE
1237 (EXTRACT
1238 (i.xmlcontent,
1239 '/ICCQueryParam/ICCIdentifiersList/ICCIdentifier'
1240 )
1241 )
1242 ) ICCIdentifier
1243 WHERE session_id = p_session_id);
1244
1245 --debug(L_PROC_NAME||'l_dup_icc_id_tab.count: '||l_dup_icc_id_tab.Count||' ,l_dup_icc_name_tab.count: '||l_dup_icc_name_tab.Count||' ,l_dup_icc_ver_id_tab.count: '||l_dup_icc_ver_id_tab.Count);
1246
1247 END CASE; /*End of Case - beyond this point, we need NOT differentiate between batch and list modes for further processing*/
1248
1249 --debug(L_PROC_NAME||'Before For Loop on l_dup_icc_id_tab.count');
1250 FOR i IN 1 .. l_dup_icc_id_tab.COUNT
1251 LOOP
1252 --debug(L_PROC_NAME||'calling procedure Validate_ICC, counter i: '||i);
1253 /* Validating the collected ICC data */
1254 l_is_valid := Validate_ICC( p_session_id => p_session_id,
1255 p_icc_id => l_dup_icc_id_tab(i),
1256 p_icc_name => l_dup_icc_name_tab(i),
1257 p_icc_ver_id => l_dup_icc_ver_id_tab(i)
1258 );
1259 --debug(L_PROC_NAME||'Returned from procedure Validate_ICC');
1260
1261 /* If above is valid data, then check for duplicates and insert the data */
1262 IF (l_is_valid) THEN
1263 --debug(L_PROC_NAME||'inside If condition on l_is_valid, l_dup_icc_ver_id_tab (i): '||l_dup_icc_ver_id_tab (i));
1264
1265 IF (l_dup_icc_ver_id_tab (i) IS NOT NULL)
1266 THEN
1267 /* If Version is given along with the ICC, insert an additional record with version as null.
1268 This entry is to sync up the ICC details.
1269 Though this record is not user entered, we still are inserting user_entered_flag in ego_pub_ws_entities
1270 as Y, so that this record does not get copied back batch fwk tables.
1271 This record should NOT be seen in batch fwk entity objects table
1272 */
1273 --debug(L_PROC_NAME||' calling check_duplicate_and_insert for an additional ICC record with version as null');
1274 check_duplicate_and_insert( p_session_id => p_session_id,
1275 p_icc_id => To_Number(l_dup_icc_id_tab (i)),
1276 p_icc_ver_id => NULL,
1277 p_icc_name => l_dup_icc_name_tab (i),
1278 p_icc_id_tab => l_icc_id_tab,
1279 p_icc_ver_id_tab => l_icc_ver_id_tab,
1280 p_icc_name_tab => l_icc_name_tab,
1281 p_show_error_flag => G_SHOW_ERROR_FLAG_NO,
1282 p_user_entered_flag => G_USER_ENTERED_FLAG_YES
1283 );
1284 --debug(L_PROC_NAME||' returned from check_duplicate_and_insert procedure');
1285 END IF;
1286
1287 /* Below call is for the following cases
1288 (a) Versioned ICC - actual version record, here l_dup_icc_ver_id_tab(i) will be NOT NULL
1289 The ICC record was inserted in the above call
1290 (b) Non Versioned ICC - just the ICC record, here l_dup_icc_ver_id_tab(i) will be NULL
1291 These records are always user entered.
1292 */
1293 --debug(L_PROC_NAME||' calling check_duplicate_and_insert for the user entered record');
1294 check_duplicate_and_insert( p_session_id => p_session_id,
1295 p_icc_id => To_Number(l_dup_icc_id_tab (i)),
1296 p_icc_ver_id => To_Number(l_dup_icc_ver_id_tab(i)),
1297 p_icc_name => l_dup_icc_name_tab (i),
1298 p_icc_id_tab => l_icc_id_tab,
1299 p_icc_ver_id_tab => l_icc_ver_id_tab,
1300 p_icc_name_tab => l_icc_name_tab,
1301 p_show_error_flag => G_SHOW_ERROR_FLAG_YES,
1302 p_user_entered_flag => G_USER_ENTERED_FLAG_YES
1303 );
1304 --debug(L_PROC_NAME||' returned from check_duplicate_and_insert procedure');
1305 ELSE
1306 --debug(L_PROC_NAME||' Validate_ICC procedure failed');
1307 NULL; /*validation failed*/
1308 END IF;
1309
1310 END LOOP; /* end of For loop on l_dup_icc_id_tab */
1311 --debug(L_PROC_NAME||' End of For Loop on l_dup_icc_id_tab.count');
1312
1313 /* If config param ParentICCs or ChildICCs is set to TRUE, then derive the parent or child icc
1314 and insert into ego_pub_ws_entities table */
1315
1316 /* In Create_Params_ICC() procedure, we have dumped config params in both modes (batch,list) into EGO_PUB_WS_CONFIG table.
1317 So, now while fetching the PARENTICCS,CHILDICCS params, we can just get from this table without reference to any mode*/
1318 l_param_parent_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'PARENTICCS', NULL, NULL);
1319 l_param_child_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'CHILDICCS', NULL, NULL);
1320 --debug(L_PROC_NAME||'l_param_parent_hier: '||l_param_parent_hier||' ,l_param_child_hier: '||l_param_child_hier);
1321
1322 -- bug 12377269. added UPPER
1323 IF ( (Nvl(upper(l_param_parent_hier),'FALSE')='TRUE') OR (Nvl(upper(l_param_child_hier),'FALSE')='TRUE')) THEN
1324
1325 --debug(L_PROC_NAME||' Parent-child ICC Explosion, Before For Loop on l_icc_id_tab.COUNT: '||l_icc_id_tab.COUNT);
1326 FOR counter_icc IN 1 .. l_icc_id_tab.COUNT
1327 LOOP
1328 --debug(L_PROC_NAME||' Outer For Loop, counter_icc: '||counter_icc);
1329
1330 /*Fix for Bug 12424380 - We need to ensure that get_hierarchical_iccs procedure gets called
1331 only for either - versioned records of versioned iccs or icc header records for non-ver iccs.
1332 It should not get called for dummy icc header record for ver iccs.
1333 For ver iccs, l_icc_id_tab contains these dummy icc headers for ver iccs too
1334 - first record will be dummy icc header and the next record will be the actual ver record.
1335 For non ver iccs, l_icc_id_tab will contain only icc header records, these are not dummy.
1336 The below IF condition checks
1337 - condition 1 : the last record cannot be a dummy icc header record, so always call get_hierarchical_iccs for it.
1338 - condition 2: l_icc_ver_id_tab(counter_icc) IS NOT NULL implies it is a version record, so always call get_hierarchical_iccs
1339 - condition 3: l_icc_ver_id(count_icc) IS NULL implies it is either a dummy icc header of a ver icc
1340 or the icc header of a non-ver icc.
1341 So, compare the icc_id of the current record with the icc_id of the next record.
1342 If both are same, then it is a dummy icc header of a ver icc, otherwise it is icc header of a non-ver icc
1343
1344 Alternative approach if needed: show_error_flag will be N for dummy icc header records only. It is a sure shot way
1345 of identifying dummy icc header records. So, like l_icc_id_tab, l_icc_ver_id_tab maintain an array for
1346 show_error_flag for every icc record. Pass it to check_duplicate_and_insert procedure and populate when the other
1347 arrays are getting populated. Then p_show_error_flag(counter_icc) can be used to check for dummy icc header records */
1348
1349 IF ( ( counter_icc = l_icc_id_tab.Count )
1350 OR (l_icc_ver_id_tab(counter_icc) IS NOT NULL )
1351 OR ( (l_icc_ver_id_tab(counter_icc) IS NULL
1352 ) AND (l_icc_id_tab(counter_icc)<> l_icc_id_tab(counter_icc+1)
1353 )
1354 )
1355 )THEN
1356
1357 --
1358 -- Bug 12377269. Get parent and children ICC of given ICC
1359 -- sreharih. Tue Apr 19 18:26:45 PDT 2011
1360 --
1361 get_hierarchical_iccs(
1362 p_item_catalog_group_id => l_icc_id_tab(counter_icc),
1363 p_version_seq_id => l_icc_ver_id_tab(counter_icc),
1364 p_get_children => l_param_child_hier,
1365 p_get_parents => l_param_parent_hier,
1366 p_only_latest => 'TRUE',
1367 x_hierarchical_iccs => l_heirarchical_iccs);
1368
1369 IF l_heirarchical_iccs IS NOT NULL AND l_heirarchical_iccs.COUNT > 0 THEN
1370
1371 FOR counter_hier IN l_heirarchical_iccs.FIRST..l_heirarchical_iccs.LAST
1372 LOOP
1373 --debug(L_PROC_NAME||' Inner loop, counter_hier: '||counter_hier);
1374
1375 /* For every versioned ICC , insert an additional record with version as null.
1376 This entry is to sync up the ICC details.
1377 show_error flag will be No for these records.
1378 Though this record is not user entered, we still are inserting user_entered_flag in ego_pub_ws_entities
1379 as Y, so that this record does not get copied back batch fwk tables.
1380 This record should NOT be seen in batch fwk entity objects table
1381 */
1382 IF ( l_heirarchical_iccs(counter_hier).version_seq_id IS NOT NULL)
1383 THEN
1384 --debug(L_PROC_NAME||' Parent-child ICC Explosion, calling check_duplicate_and_insert for an additional ICC record with version as null');
1385 check_duplicate_and_insert(p_session_id => p_session_id,
1386 p_icc_id => l_heirarchical_iccs(counter_hier).item_catalog_group_id,
1387 p_icc_ver_id => NULL,
1388 p_icc_name => l_heirarchical_iccs(counter_hier).catalog_concat_segs,
1389 p_icc_id_tab => l_icc_id_tab,
1390 p_icc_ver_id_tab => l_icc_ver_id_tab,
1391 p_icc_name_tab => l_icc_name_tab,
1392 p_show_error_flag => G_SHOW_ERROR_FLAG_NO,
1393 p_user_entered_flag =>G_USER_ENTERED_FLAG_YES
1394 );
1395 --debug(L_PROC_NAME||' returned from check_duplicate_and_insert procedure for additional ICC record');
1396 END IF;
1397
1398 /* Below call inserts ICC version record.
1399 show_error flag will be Y for these records.
1400 user_entered flag is N, for these records since they are derived ICCs
1401 and have to be copied back to batch fwk entity objects table.
1402 This record should be seen in batch fwk entity objects table
1403 */
1404 --debug(L_PROC_NAME||' Parent-child ICC Explosion, calling check_duplicate_and_insert for user entered record');
1405 check_duplicate_and_insert(p_session_id => p_session_id,
1406 p_icc_id => l_heirarchical_iccs(counter_hier).item_catalog_group_id,
1407 p_icc_ver_id => l_heirarchical_iccs(counter_hier).version_seq_id,
1408 p_icc_name => l_heirarchical_iccs(counter_hier).catalog_concat_segs,
1409 p_icc_id_tab => l_icc_id_tab,
1410 p_icc_ver_id_tab => l_icc_ver_id_tab,
1411 p_icc_name_tab => l_icc_name_tab,
1412 p_show_error_flag => G_SHOW_ERROR_FLAG_YES,
1413 p_user_entered_flag =>G_USER_ENTERED_FLAG_NO
1414 );
1415 --debug(L_PROC_NAME||' returned from check_duplicate_and_insert procedure for user entered record');
1416 END LOOP; -- heir loop
1417 END IF; -- heir null check
1418 END IF; /*three conditions to check if get_hierarchical_iccs should be called*/
1419 END LOOP; /*counter_icc */
1420 END IF; /*l_parent_hier or l_child_hier*/
1421
1422 --debug(L_PROC_NAME||' End');
1423
1424 END Create_Entities_ICC;
1425
1426
1427
1428 -- =========================================================================================================
1429 -- Name : Explode_ICC
1430 -- Description : This procedure performs the following actions:
1431 -- 1. Explode the given ICCs and fetches the AGs associated to them.
1432 -- Insert these AGs into EGO_PUB_WS_ENTITIES table
1433 -- 2. Based on the config param VALUESETS, finds out whether Valuesets have to be exploded
1434 -- from the derived AGs and calls EGO_PUB_WS_AG.Explode_Attribute_Group procedure.
1435 -- This procedure is called from Preprocess_Input_ICC
1436 --
1437 -- Scope : Private
1438 --
1439 -- Parameters:
1440 -- IN :
1441 -- p_session_id IN NUMBER
1442 -- An Unique DB sequence generated at Java wrapper.
1443 --
1444 -- ===========================================================================================================
1445 PROCEDURE Explode_ICC(p_session_id IN NUMBER)
1446 IS
1447
1448 l_mode VARCHAR2(10);
1449 l_batch_id NUMBER ;
1450
1451 l_prev_ag_id NUMBER;
1452 l_prev_ag_seq_id NUMBER;
1453 l_param_vs VARCHAR2(2000); /*VS param configurability*/
1454 l_seq_id NUMBER;
1455
1456
1457 x_err_msg VARCHAR2(1000);
1458 x_error_code NUMBER;
1459 -- x_group_id NUMBER :=654;
1460 x_return_status VARCHAR2(1);
1461 L_PROC_NAME VARCHAR2(50);
1462
1463 /* The below cursor fetches the AGs associated to all ICCs in ego_pub_ws_entities table
1464 epwe.pk1_value is icc_id, epwe.ref1_value is icc_name, epwe.sequence_id is the sequence_id of icc record
1465 eoaab.attr_group_id is ag_id, efdfce.descriptive_flex_context_code is ag_name */
1466 CURSOR cur_derived_ags(p_session_id NUMBER) IS
1467 SELECT epwe.pk1_value , epwe.ref1_value ,epwe.sequence_id
1468 ,eoaab.attr_group_id, efdfce.descriptive_flex_context_code
1469 FROM ego_obj_ag_assocs_b eoaab,
1470 fnd_objects fo,
1471 ego_fnd_dsc_flx_ctx_ext efdfce,
1472 ego_pub_ws_entities epwe
1473 WHERE eoaab.object_id=fo.object_id
1474 AND fo.obj_name ='EGO_ITEM'
1475 AND eoaab.attr_group_id=efdfce.attr_group_id
1476 AND efdfce.descriptive_flexfield_name = 'EGO_ITEMMGMT_GROUP'
1477 AND efdfce.application_id = G_APPLICATION_ID_EGO
1478 AND eoaab.classification_code = epwe.pk1_value
1479 AND epwe.SESSION_ID = p_session_id
1480 AND epwe.entity_type = G_ENTITY_TYPE_ICC
1481 AND epwe.pk2_value IS NULL /*PK2_VALUE will not be null for icc ver records*/
1482 AND epwe.parent_sequence_id IS NULL
1483 ORDER BY eoaab.attr_group_id, epwe.sequence_id;
1484
1485 /* Bug 12403644 ASSOCIATED TRANSACTION ATTRIBUTES NOT SYNCED TO TARGET SYSTEM START
1486 The below cursor fetches all the Value sets associated to the Trasaction attributes
1487 for a given ICC version.
1488
1489 */
1490
1491 CURSOR cur_trans_attr_vs(p_session_id NUMBER) IS
1492 SELECT EMCGVB.ITEM_CATALOG_GROUP_ID,EMCGVB.VERSION_SEQ_ID AS icc_ver_seq_id,
1493 EMCGVB.START_ACTIVE_DATE AS icc_strt_ac_date,EMCGVB.END_ACTIVE_DATE AS icc_end_date
1494 ,ETAVB.ASSOCIATION_ID,ETAVB.ATTR_ID,ETAVB.ATTR_DISPLAY_NAME,ETAVB.SEQUENCE
1495 , FFVS.FLEX_VALUE_SET_ID, FFVS.FLEX_VALUE_SET_NAME
1496 , EFVVB.VERSION_SEQ_ID AS vs_seq_id, EFVVB.START_ACTIVE_DATE AS vs_start_date, EFVVB.END_ACTIVE_DATE AS vs_end_date
1497 , EPWE.PK1_VALUE, EPWE.PK2_VALUE
1498 FROM EGO_MTL_CATALOG_GRP_VERS_B EMCGVB,
1499 EGO_TRANS_ATTR_VERS_B ETAVB
1500 , FND_FLEX_VALUE_SETS FFVS,
1501 EGO_FLEX_VALUESET_VERSION_B EFVVB
1502 , EGO_PUB_WS_ENTITIES EPWE
1503 WHERE EMCGVB.ITEM_CATALOG_GROUP_ID=ETAVB.ITEM_CATALOG_GROUP_ID
1504 AND EMCGVB.VERSION_SEQ_ID=ETAVB.ICC_VERSION_NUMBER
1505 AND ETAVB.VALUE_SET_ID=FFVS.FLEX_VALUE_SET_ID
1506 AND FFVS.VALIDATION_TYPE IN ('X','I')
1507 AND FFVS.FLEX_VALUE_SET_ID = EFVVB.FLEX_VALUE_SET_ID
1508 AND EFVVB.VERSION_SEQ_ID >= 1
1509 AND EFVVB.START_ACTIVE_DATE <= EMCGVB.START_ACTIVE_DATE
1510 AND Nvl(EFVVB.END_ACTIVE_DATE,Nvl(EMCGVB.START_ACTIVE_DATE,SYSDATE)) >=
1511 Nvl(EMCGVB.START_ACTIVE_DATE,SYSDATE)
1512 AND EMCGVB.ITEM_CATALOG_GROUP_ID = EPWE.PK1_VALUE
1513 AND EMCGVB.VERSION_SEQ_ID = EPWE.PK2_VALUE
1514 AND EPWE.ENTITY_TYPE = 'ICC'--G_ENTITY_TYPE_ICC
1515 AND EPWE.PK2_VALUE IS NOT NULL
1516 AND EPWE.SESSION_ID=p_session_id
1517 ORDER BY FFVS.FLEX_VALUE_SET_ID, EFVVB.VERSION_SEQ_ID;
1518
1519 /* Bug 12403644 END */
1520 --rec_derived_ags cur_derived_ags%ROWTYPE;
1521
1522 BEGIN
1523 L_PROC_NAME := ' Explode_ICC=> ';
1524 --debug(L_PROC_NAME||' Start');
1525 --debug(L_PROC_NAME||' Parameters - p_session_id: '||p_session_id);
1526
1527 l_prev_ag_id := 0;
1528
1529 FOR rec_derived_ags IN cur_derived_ags(p_session_id) LOOP
1530 --debug(L_PROC_NAME||'enter cur_derived_ags, attr_group_id: '||rec_derived_ags.attr_group_id||' ,l_prev_ag_id : '||l_prev_ag_id);
1531
1532 IF rec_derived_ags.attr_group_id <> l_prev_ag_id THEN
1533 /* Current AG record does not already exist in ego_pub_ws_entities table.
1534 So, insert a AG record and an AG-ICC association record into ego_pub_ws_entities table.*/
1535
1536 -- get next value for the sequence_id
1537 SELECT EGO_PUB_WS_ENTITIES_S.nextval
1538 INTO l_seq_id
1539 FROM dual;
1540
1541 --debug(L_PROC_NAME||'l_seq_id: '||l_seq_id||' ,rec_derived_ags.sequence_id: '||rec_derived_ags.sequence_id);
1542
1543 INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
1544 ODI_SESSION_ID,
1545 SEQUENCE_ID,
1546 PARENT_SEQUENCE_ID,
1547 ENTITY_TYPE,
1548 PK1_VALUE,
1549 PK2_VALUE,
1550 PK3_VALUE,
1551 PK4_VALUE,
1552 PK5_VALUE,
1553 REF1_VALUE,
1554 REF2_VALUE,
1555 REF3_VALUE,
1556 REF4_VALUE,
1557 REF5_VALUE,
1558 REF6_VALUE,
1559 REF7_VALUE,
1560 REF8_VALUE,
1561 REF9_VALUE,
1562 REF10_VALUE,
1563 CREATION_DATE,
1564 CREATED_BY,
1565 USER_ENTERED)
1566 VALUES( p_session_id,
1567 p_session_id,
1568 l_seq_id,
1569 NULL,
1570 G_ENTITY_TYPE_AG,
1571 rec_derived_ags.attr_group_id,
1572 NULL,
1573 NULL,
1574 NULL,
1575 NULL,
1576 rec_derived_ags.descriptive_flex_context_code,
1577 NULL,
1578 NULL,
1579 NULL,
1580 NULL,
1581 NULL,
1582 NULL,
1583 NULL,
1584 NULL,
1585 NULL,
1586 sysdate,
1587 G_CURRENT_USER_ID,
1588 G_USER_ENTERED_FLAG_NO
1589 );
1590
1591 INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
1592 ODI_SESSION_ID,
1593 SEQUENCE_ID,
1594 PARENT_SEQUENCE_ID,
1595 ENTITY_TYPE,
1596 PK1_VALUE,
1597 PK2_VALUE,
1598 PK3_VALUE,
1599 PK4_VALUE,
1600 PK5_VALUE,
1601 REF1_VALUE,
1602 REF2_VALUE,
1603 REF3_VALUE,
1604 REF4_VALUE,
1605 REF5_VALUE,
1606 REF6_VALUE,
1607 REF7_VALUE,
1608 REF8_VALUE,
1609 REF9_VALUE,
1610 REF10_VALUE,
1611 CREATION_DATE,
1612 CREATED_BY,
1613 USER_ENTERED)
1614 VALUES( p_session_id,
1615 p_session_id,
1616 l_seq_id,
1617 rec_derived_ags.sequence_id,
1618 G_ENTITY_TYPE_AG,
1619 rec_derived_ags.attr_group_id,
1620 NULL,
1621 NULL,
1622 NULL,
1623 NULL,
1624 rec_derived_ags.descriptive_flex_context_code,
1625 NULL,
1626 NULL,
1627 NULL,
1628 NULL,
1629 NULL,
1630 NULL,
1631 NULL,
1632 NULL,
1633 NULL,
1634 sysdate,
1635 G_CURRENT_USER_ID,
1636 G_USER_ENTERED_FLAG_NO
1637 );
1638
1639 --debug(L_PROC_NAME||'Inserted both AG and ICC-AG association records');
1640
1641 ELSE /* rec_derived_ags.ag_id l_prev_ag_id */
1642 /* Current AG already exists in ego_pub_ws_entities table,
1643 so insert only the AG-ICC assocation record*/
1644 INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
1645 ODI_SESSION_ID,
1646 SEQUENCE_ID,
1647 PARENT_SEQUENCE_ID,
1648 ENTITY_TYPE,
1649 PK1_VALUE,
1650 PK2_VALUE,
1651 PK3_VALUE,
1652 PK4_VALUE,
1653 PK5_VALUE,
1654 REF1_VALUE,
1655 REF2_VALUE,
1656 REF3_VALUE,
1657 REF4_VALUE,
1658 REF5_VALUE,
1659 REF6_VALUE,
1660 REF7_VALUE,
1661 REF8_VALUE,
1662 REF9_VALUE,
1663 REF10_VALUE,
1664 CREATION_DATE,
1665 CREATED_BY,
1666 USER_ENTERED)
1667 VALUES( p_session_id,
1668 p_session_id,
1669 l_prev_ag_seq_id,
1670 rec_derived_ags.sequence_id,
1671 G_ENTITY_TYPE_AG,
1672 rec_derived_ags.attr_group_id,
1673 NULL,
1674 NULL,
1675 NULL,
1676 NULL,
1677 rec_derived_ags.descriptive_flex_context_code,
1678 NULL,
1679 NULL,
1680 NULL,
1681 NULL,
1682 NULL,
1683 NULL,
1684 NULL,
1685 NULL,
1686 NULL,
1687 sysdate,
1688 G_CURRENT_USER_ID,
1689 G_USER_ENTERED_FLAG_NO
1690 );
1691 --debug(L_PROC_NAME||'Inserted only ICC-AG association record');
1692 END IF; /* rec_derived_ags.ag_id <> l_prev_ag_id */
1693
1694 l_prev_ag_id := rec_derived_ags.attr_group_id; /* assign the current ag_id to l_prev_ag_id */
1695 l_prev_ag_seq_id := l_seq_id;
1696 /*Technically, this l_prev_ag_seq_id is not needed, we can pass l_seq_id in the else part insert stmt too
1697 since the l_seq_id is not incremented for the else part. Using l_prev_ag_seq_id for better readability only*/
1698 --debug(L_PROC_NAME||' End of an iteration for cur_derived_ags cursor, l_prev_ag_id: '||l_prev_ag_id||' ,l_prev_ag_seq_id: '||l_prev_ag_seq_id);
1699 END LOOP; /* rec_derived_ags */
1700 --debug(L_PROC_NAME||' End of Loop on cur_derived_ags cursor');
1701
1702 /*For all the AGs exploded above, VSs have to be derived, so calling EGO_PUB_WS_AG.Explode_Attribute_Group procedure
1703 only when Publish Valuesets config param is set to Yes.
1704 For both modes - batch and list, VALUESETS param has been added to EGO_PUB_WS_CONFIG table in Create_params_ICC procedure.
1705 So, we don't differentiate the mode while fetching this param below*/
1706
1707 l_param_vs := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'VALUESETS', NULL, NULL);
1708 --debug(L_PROC_NAME||' l_param_vs: '||l_param_vs);
1709
1710 IF l_param_vs = G_CHAR_TRUE THEN
1711 --debug(L_PROC_NAME||' Calling EGO_PUB_WS_AG.Explode_Attribute_Group');
1712 EGO_PUB_WS_AG.Explode_Attribute_Group(p_session_id);
1713 --debug(L_PROC_NAME||' Returned from EGO_PUB_WS_AG.Explode_Attribute_Group');
1714 END IF;
1715
1716
1717 /*
1718 Bug 12403644 ASSOCIATED TRANSACTION ATTRIBUTES NOT SYNCED TO TARGET SYSTEM START
1719 Get all the value sets associated to the transaction attributes and explode those VS.
1720 */
1721
1722 FOR trans_attr_vs IN cur_trans_attr_vs(p_session_id) LOOP
1723
1724 --inserting records in to EGO_PUB_WS_ENTITIES
1725 ego_pub_ws_vs.Insert_VS_Entity ( p_session_id => p_session_id
1726 ,p_vs_id => trans_attr_vs.FLEX_VALUE_SET_ID
1727 ,p_vs_name => trans_attr_vs.FLEX_VALUE_SET_NAME
1728 ,p_vs_version => trans_attr_vs.vs_seq_id
1729 ,p_user_entered_flag => G_USER_ENTERED_FLAG_NO
1730 );
1731
1732 END LOOP;
1733
1734 --Bug12403644 END
1735
1736 --debug(L_PROC_NAME||' End');
1737
1738 END Explode_ICC;
1739
1740
1741 PROCEDURE Write_Derived_Entites_ToBatFwk( p_session_id IN NUMBER,
1742 p_batch_id IN NUMBER)
1743 IS
1744
1745 l_batch_id NUMBER := p_batch_id;
1746 v_index NUMBER;
1747 v_count NUMBER;
1748 batch_entity_rec EGO_PUB_FWK_PK.TBL_OF_BAT_ENT_OBJ_TYPE;
1749 l_entity_type_id_tbl EGO_PUB_FWK_PK.NUMBER_ARR_TBL_TYPE;
1750 l_entity_type_id NUMBER;
1751
1752 x_return_status VARCHAR2(1);
1753 x_msg_count NUMBER;
1754 x_msg_data VARCHAR2(500);
1755 L_PROC_NAME VARCHAR2(50);
1756
1757 /* In the below cursor, a filter on entity_type is NOT being used,
1758 since from an ICC, we can get ICC,AG and VS as derived entities.
1759 The select clause has entity_type column to identify the entity being fetched*/
1760 CURSOR c_derived_entities(l_session_id NUMBER) IS
1761 SELECT PK1_VALUE,
1762 PK2_VALUE,
1763 ENTITY_TYPE
1764 FROM EGO_PUB_WS_ENTITIES
1765 WHERE SESSION_ID = l_session_id
1766 AND NVL(USER_ENTERED, 'N') = 'N'
1767 AND PARENT_SEQUENCE_ID IS NULL;
1768
1769 BEGIN
1770
1771 L_PROC_NAME := ' Write_Derived_Entites_ToBatFwk=> ';
1772 --debug(L_PROC_NAME||' Start');
1773 --debug(L_PROC_NAME||' Parameters - p_session_id: '||p_session_id||' ,p_batch_id: '||p_batch_id);
1774
1775 IF (l_batch_id > -1 ) THEN
1776 v_index := 1;
1777
1778 /*Fetch the entity_id for ICC,AG,VS on this instance. This value is constant per entity, hence fetching outside the For loop
1779 The output of below query is expected to be
1780 l_entity_id_tbl(1) = entity_id of AG, l_entity_id_tbl(2) = entity_id of ICC, l_entity_id_tbl(3) = entity_id of VS
1781 For now, we are not handling exceptions for this select query, since we assume these records should always be present
1782 */
1783 SELECT entity_id
1784 BULK COLLECT INTO l_entity_type_id_tbl
1785 FROM EGO_PUB_BAT_ENTS_B
1786 WHERE entity_name IN ('Attribute Group','ICC','Value Set')
1787 ORDER BY entity_name;
1788
1789 --debug(L_PROC_NAME||'AG type id: '||l_entity_type_id_tbl(1)||' ICC type id: '||l_entity_type_id_tbl(2)||' VS type id: '||l_entity_type_id_tbl(3));
1790
1791 FOR k IN c_derived_entities(p_session_id)
1792 LOOP
1793
1794 /* Get the entity_id based on the entity_type */
1795 IF (k.entity_type = G_ENTITY_TYPE_AG) THEN
1796 l_entity_type_id := l_entity_type_id_tbl(1);
1797 ELSIF (k.entity_type = G_ENTITY_TYPE_ICC) THEN
1798 l_entity_type_id := l_entity_type_id_tbl(2);
1799 ELSIF (k.entity_type = G_ENTITY_TYPE_VS) THEN
1800 l_entity_type_id := l_entity_type_id_tbl(3);
1801 ELSE /*ideally, this else part should never get executed*/
1802 l_entity_type_id := 0;
1803 END IF;
1804
1805 --debug(L_PROC_NAME||' k.entity_type: '||k.entity_type||' l_entity_type_id: '||l_entity_type_id);
1806
1807 /*changing the below select statement to use the base table instead of view.
1808 Using an NVL on pk2_value since it can be null for AG and VS but not ICC
1809 kkq- do we really need below select stmt, since
1810 ego_pub_ws_entities will initially have the same records as ego_pub_bat_ent_objs_b (user_entered=Y)
1811 and as we derive new entity records (user_entered=N), we do not insert them into ego_pub_ws_entities
1812 if they are already present. We can safely assume that all records fetched by cursor c_derived_entities
1813 will not be present in ego_pub_bat_ent_objs_b.*/
1814
1815 SELECT COUNT(*) INTO v_count
1816 FROM EGO_PUB_BAT_ENT_OBJS_B
1817 WHERE BATCH_ID = l_batch_id
1818 AND entity_type_id = l_entity_type_id
1819 AND PK1_VALUE = k.pk1_value
1820 AND NVL(PK2_VALUE,-999) = NVL(k.pk2_value,-999);
1821
1822 --debug(L_PROC_NAME||' v_count: '||v_count);
1823
1824 IF (v_count = 0) THEN
1825 -- Prepare the record only for derived entities
1826 batch_entity_rec(v_index).batch_id := l_batch_id;
1827 batch_entity_rec(v_index).pk1_value := k.pk1_value;
1828 batch_entity_rec(v_index).pk2_value := k.pk2_value;
1829 batch_entity_rec(v_index).pk3_value := NULL;
1830 batch_entity_rec(v_index).pk4_value := NULL;
1831 batch_entity_rec(v_index).pk5_value := NULL;
1832 batch_entity_rec(v_index).user_entered := 'N';
1833 batch_entity_rec(v_index).entity_type_id := l_entity_type_id;
1834
1835 --debug(L_PROC_NAME||'done preparing the batch_entity_rec for v_index: '||v_index);
1836
1837 v_index := v_index + 1;
1838
1839 END IF;
1840 END LOOP; -- end of loop k
1841
1842 -- Calling the Below API for all the derived entities at a time, i.e in bulk
1843
1844 --debug(L_PROC_NAME||' calling procedure EGO_PUB_FWK_PK.add_derived_entities ');
1845 EGO_PUB_FWK_PK.add_derived_entities(batch_entity_rec,x_return_status,x_msg_count,x_msg_data);
1846 --debug(L_PROC_NAME||' returned from procedure EGO_PUB_FWK_PK.add_derived_entities ');
1847
1848 IF (x_return_status <> 'S') THEN
1849 NULL;
1850 --debug(L_PROC_NAME||'EGO_PUB_FWK_PK.add_derived_entities x_return_status=>'||x_return_status);
1851 --debug(L_PROC_NAME||'EGO_PUB_FWK_PK.add_derived_entities x_msg_data=>'||x_msg_data);
1852 END IF;
1853 END IF; -- end of if (l_batch_id > -1 and x_return_status = 'S')
1854
1855 --debug(L_PROC_NAME||' End');
1856
1857 END Write_Derived_Entites_ToBatFwk;
1858
1859
1860 -- ================================================================================================================
1861 -- Name : Preprocess_Input_ICC
1862 -- Description : This is the main procedure for pre-processing the ICC entitiy records.
1863 -- This procedure performs the following actions:
1864 -- 1. Calls Create_Params_ICC() to insert config params to EGO_PUB_WS_CONFIG table.
1865 -- 2. Calls Create_Entities_ICC procedure, to populate ODI input table EGO_PUB_WS_ENTITIES
1866 -- for entity ICC, based on the invokation type (e.g. batch, list)
1867 -- 3. Calls Explode_ICC procedure to explode the ICCs and populate AGs into EGO_PUB_WS_ENTITIES
1868 -- 4. In Batch Mode flow, calls Write_Derived_Entites_ToBatFwk procedure to
1869 -- add the derived entities (ICCs,AGs) to Batch Fwk Entities table - EGO_PUB_BAT_ENT_OBJS_B
1870 -- This procedure gets called from ODI project EGO_SYNCICCDETAILS.
1871 --
1872 -- Scope : Public
1873 --
1874 -- Parameters:
1875 -- IN :
1876 -- p_session_id IN NUMBER
1877 -- An Unique DB sequence generated at Java wrapper.
1878 --
1879 -- ================================================================================================================
1880 PROCEDURE Preprocess_Input_ICC(p_session_id IN NUMBER)
1881 IS
1882
1883 l_mode VARCHAR2(10);
1884 l_batch_id NUMBER;
1885 l_param_ag VARCHAR2(10);
1886 L_PROC_NAME VARCHAR2(50);
1887
1888 l_param_rec_count NUMBER;
1889 l_entity_rec_count NUMBER;
1890
1891 BEGIN
1892 L_PROC_NAME := ' Preprocess_Input_ICC=> ';
1893 --debug(L_PROC_NAME||' Start');
1894 --debug(L_PROC_NAME||' Parameters - p_session_id: '||p_session_id);
1895
1896 /*initialize both counts to zero*/
1897 l_param_rec_count :=0;
1898 l_entity_rec_count :=0;
1899
1900 /* -- Get the number of records for configuration parameters
1901 -- for the session
1902 -- If no records exist in EGO_PUB_WS_CONFIG for the session then only
1903 -- create configuration parameters for the session.
1904 */
1905
1906 --debug(L_PROC_NAME||'before fetching l_param_rec_count');
1907
1908 SELECT COUNT(1)
1909 INTO l_param_rec_count
1910 FROM EGO_PUB_WS_CONFIG
1911 WHERE SESSION_ID = p_session_id
1912 AND PARAMETER_NAME NOT IN('ODI_SESSION_ID', 'SYSTEM_CODE', 'MODE', 'BATCH_ID');
1913
1914 --debug(L_PROC_NAME||'after fetching l_param_rec_count, count: '|| l_param_rec_count);
1915
1916
1917 /* When sync'ing to multiple systems, we have to ensure that params get inserted into ego_pub_ws_config table
1918 only once. Once they are inserted for first system, they can be reused for later systems.
1919 Hence checking for l_param_rec_count = 0*/
1920 IF l_param_rec_count = 0 THEN
1921 --Create Input parameters for ODI
1922 --debug(L_PROC_NAME||'Calling Create_Params_ICC procedure');
1923 Create_Params_ICC(p_session_id);
1924 --debug(L_PROC_NAME||'Returned from Create_Params_ICC procedure');
1925 END IF;
1926
1927 -- bug 12755038 , moved the code after the param rec count
1928 -- it is highly likely that the entities count <> 0 when
1929 -- the parallel session for create_params_icc has finished.
1930 /* -- Get the number of records for Entity AG
1931 -- If no records exist for the session then only
1932 -- create input entities for the session.
1933 */
1934 --debug(L_PROC_NAME||'before fetching l_entity_rec_count');
1935
1936 SELECT COUNT(1)
1937 INTO l_entity_rec_count
1938 FROM EGO_PUB_WS_ENTITIES
1939 WHERE SESSION_ID = p_session_id
1940 AND ENTITY_TYPE = G_ENTITY_TYPE_ICC;
1941
1942 --debug(L_PROC_NAME||'after fetching l_entity_rec_count, count: '|| l_entity_rec_count);
1943 /* When sync'ing to multiple systems, we have to ensure that entities get inserted into ego_pub_ws_entities table
1944 only once. Once they are inserted for first system, they can be reused for later systems.
1945 Hence checking for l_entity_rec_count = 0*/
1946 IF l_entity_rec_count = 0 THEN
1947 --Create ODI Input table containing entities to process
1948 --debug(L_PROC_NAME||'Calling Create_Entities_ICC procedure');
1949 Create_Entities_ICC(p_session_id);
1950 --debug(L_PROC_NAME||'Returned from Create_Entities_ICC procedure');
1951
1952 --Explode ICCs to get associated AGs
1953 l_mode := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'MODE', NULL, NULL);
1954 --debug(L_PROC_NAME||'Mode: '||l_mode);
1955
1956 IF l_mode = 'LIST' THEN
1957 l_param_ag := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'USERDEFATTRGRPS', NULL, l_mode);
1958 --debug(L_PROC_NAME||'List mode, l_param_ag: '||l_param_ag );
1959 ELSIF l_mode = 'BATCH' THEN
1960 l_batch_id := EGO_PUB_WS_UTIL.Get_Numeric_Param_Value(p_session_id,'BATCH_ID', NULL, NULL);
1961 --debug(L_PROC_NAME||'Batch mode, l_batch_id: '||l_batch_id);
1962 l_param_ag := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'USERDEFATTRGRPS', NULL, NULL);
1963 --debug(L_PROC_NAME||'Batch mode, l_param_ag: '||l_param_ag );
1964 END IF;
1965
1966 /* check for the explosion of the immediate child entity only. In this case, check if AG have to be
1967 synced and derive them. Once AGs are exploded call the EGO_PUB_WS_AG.explode_attribute_group for
1968 VS explosion */
1969 IF l_param_ag = G_CHAR_TRUE THEN
1970 --debug(L_PROC_NAME||'Calling Explode_ICC procedure');
1971 Explode_ICC(p_session_id);
1972 --debug(L_PROC_NAME||'Returned from Explode_ICC procedure');
1973 END IF;
1974
1975 END IF; /* end of if on l_entity_rec_count = 0 */
1976
1977 -- For BATCH Mode, write derived entities to EGO_PUB_BAT_ENT_OBJS_B table
1978 IF l_mode = 'BATCH' THEN
1979 --debug(L_PROC_NAME||'Calling Write_Derived_Entites_ToBatFwk procedure');
1980 Write_Derived_Entites_ToBatFwk(p_session_id,l_batch_id);
1981 --debug(L_PROC_NAME||'Returned from Write_Derived_Entites_ToBatFwk procedure');
1982 END IF;
1983
1984 --debug(L_PROC_NAME||' End');
1985 END Preprocess_Input_ICC;
1986
1987
1988 /* Writing an ICC specific Write_Errors_ToBatFwk procedure as below instead of calling the
1989 generic procedure Write_Errors_ToBatFwk in EGO_PUB_WS_UTIL. This is because for the versioned
1990 ICCs, we create two records - an ICC record with version as null and a second record with
1991 version populated. To handle this difference in representing entities, we need to code
1992 Write_Errors_ToBatFwk() specific to ICC
1993
1994 At a later point in time (post 12.2), review the feasibility of merging this procedure
1995 with EGO_PUB_WS_UTIL.Write_Errors_ToBatFwk().
1996 It can be done by changing the code of EGO_PUB_WS_UTIL.Write_Errors_ToBatFwk() to handle
1997 - CURSOR c_pub_ws_entities changes
1998 - handling update of EGO_PUBLICATION_BATCH_GT seperately differently than AG,VS using a IF-ELSE condition.
1999 - in ODI pkg EGO_SYNCICCDETAILS , procedure Write_Errors_ToBatFwk() make a call to the procedure in EGO_PUB_WS_UTIL
2000 */
2001
2002 /* Algorithm of Write_Errors_ToBatFwk in EGO_PUB_WS_UTIL
2003 1) Select value of trigger_import param
2004 2) loop on systems
2005 2.1 check for invalid sys
2006 2.1.1 if valid sys then
2007 2.1.1.1 if trigger_import = N/False then
2008 2.1.1.1.1 loop on entities
2009 insert all records as INPROCESS
2010 end loop on entities
2011 2.1.1.2 else if trigger_import = Y/True then
2012 2.1.1.2.1 loop on entities
2013 insert all records as SUCCESS
2014 end loop on entities
2015 End if on trigger_import
2016 end if on valid sys
2017 2.1.2 if invalid sys then
2018 2.1.2.1 loop on entities
2019 insert all record with FAILURE
2020 end loop on entities
2021 end if on invalid sys
2022 end loop on systems
2023 3) if trigger_import=Y then
2024 3.1 loop on sys
2025 3.1.1 check for invalid sys
2026 3.1.2 if valid sys then
2027 3.1.2.1 loop on input identifiers then
2028 3.1.2.1.1 select count of errors (E,E1 )
2029 3.1.2.1.2 loop on errors then
2030 3.1.2.1.2.1 if error then
2031 3.1.2.1.2.1.1 if err count > 1 and not E1 then
2032 3.1.2.1.2.1.1.1 if ( entity=icc and pk2_value is null ) then
2033 update GT based on pk1_value alone
2034 3.1.2.1.2.1.1.2 else if ( entity <> icc or pk2_value is not null ) then
2035 update GT based on all pk_values
2036 end if;
2037 3.1.2.1.2.1.2 if err count =1 then
2038 3.1.2.1.2.1.2.1 if ( entity=icc and pk2_value is null ) then
2039 update GT based on pk1_value alone
2040 3.1.2.1.2.1.2.2 else if ( entity <> icc or pk2_value is not null ) then
2041 update GT based on all pk_values
2042 end if;
2043 end if on error;
2044 3.1.2.1.2.2 if warning then
2045 3.1.2.1.2.2.1 if ( entity=icc and pk2_value is null ) then
2046 update GT based on pk1_value alone
2047 3.1.2.1.2.1.2.2 else if ( entity <> icc or pk2_value is not null ) then
2048 update GT based on all pk_values
2049 end if;
2050 end loop on errors;
2051 end loop on input identifiers;
2052 end if on valid sys;
2053 end loop on sys;
2054 end if on trigger_import=Y
2055 4) loop on GT
2056 build bat_ent_stat_rec array;
2057 end loop
2058 call EGO_PUB_FWK_PK.Update_Pub_Status with bat_ent_stat_rec array;
2059 5) End of algorithm.
2060 */
2061
2062 PROCEDURE Write_Errors_ToBatFwk(p_session_id IN NUMBER,
2063 p_batch_id IN NUMBER)
2064 IS
2065
2066 l_batch_id NUMBER := p_batch_id;
2067 l_trigger_import VARCHAR2(10) := NULL;
2068 l_INPUT_ID NUMBER;
2069 l_param_name VARCHAR2(100) := NULL;
2070 l_param_value VARCHAR2(100) := NULL;
2071 l_message VARCHAR2(2000);
2072 l_return_status VARCHAR2(1);
2073 l_invld_sys_cnt NUMBER;
2074 l_error_rec_count NUMBER :=0;
2075
2076 v_index NUMBER;
2077 v_count NUMBER;
2078 l_system_code VARCHAR2(40);
2079 bat_ent_stat_rec EGO_PUB_FWK_PK.TBL_OF_BAT_ENT_OBJ_STAT_TYPE;
2080 x_bat_status_out EGO_PUB_FWK_PK.TBL_OF_BAT_ENT_OBJ_RSTS_TYPE;
2081
2082 x_return_status VARCHAR2(1);
2083 x_msg_count NUMBER;
2084 x_msg_data VARCHAR2(500);
2085 L_PROC_NAME VARCHAR2(50);
2086
2087 -- Get System_code from EGO_PUB_WS_CONFIG table
2088
2089 CURSOR c_system_codes(l_session_id NUMBER)
2090 IS
2091 SELECT CHAR_VALUE
2092 FROM EGO_PUB_WS_CONFIG
2093 WHERE SESSION_ID = l_session_id
2094 AND PARAMETER_NAME = 'SYSTEM_CODE';
2095
2096 /* -- Get input and derived entities from EGO_PUB_WS_ENTITIES
2097 -- consider only the ICC records that user has entered (ref10_value=Y)
2098 -- consider all AG and VS records */
2099 CURSOR c_pub_ws_entities(l_session_id NUMBER)
2100 IS
2101 SELECT PK1_VALUE,
2102 PK2_VALUE,
2103 PK3_VALUE,
2104 PK4_VALUE,
2105 PK5_VALUE,
2106 USER_ENTERED
2107 FROM EGO_PUB_WS_ENTITIES
2108 WHERE SESSION_ID = l_session_id
2109 AND ( ( NVL(ENTITY_TYPE,'XYZ') <> G_ENTITY_TYPE_ICC )
2110 OR (ENTITY_TYPE=G_ENTITY_TYPE_ICC AND REF10_VALUE=G_CHAR_YES) );
2111
2112 -- Get PKs, SYSTEM_CODE from EGO_PUB_WS_INPUT_IDENTIFIERS
2113 CURSOR c_input_identifier(l_session_id NUMBER, l_system_code VARCHAR2)
2114 IS
2115 SELECT SESSION_ID,
2116 INPUT_ID,
2117 SYSTEM_CODE,
2118 PARAM_NAME,
2119 PARAM_VALUE,
2120 PK1_VALUE,
2121 PK2_VALUE,
2122 PK3_VALUE,
2123 PK4_VALUE,
2124 PK5_VALUE
2125 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
2126 WHERE SESSION_ID = l_session_id
2127 AND SYSTEM_CODE = l_system_code;
2128
2129 -- Get ERROR_CODE, ERROR_MESSAGE from EGO_PUB_WS_ERRORS
2130 CURSOR c_pub_ws_error(l_session_id NUMBER, l_input_id NUMBER, l_system_code VARCHAR2)
2131 IS
2132 SELECT SESSION_ID,
2133 INPUT_ID,
2134 SYSTEM_CODE,
2135 ERR_CODE,
2136 ERR_MESSAGE
2137 FROM EGO_PUB_WS_ERRORS
2138 WHERE SESSION_ID = l_session_id
2139 AND INPUT_ID = l_input_id
2140 AND SYSTEM_CODE = l_system_code;
2141
2142 -- Get all entities of EGO_PUBLICATION_BATCH_GT
2143 CURSOR c_pub_bat_gt(l_batch_id NUMBER)
2144 IS
2145 SELECT BATCH_ID,
2146 PK1_VALUE,
2147 PK2_VALUE,
2148 PK3_VALUE,
2149 PK4_VALUE,
2150 PK5_VALUE,
2151 SYSTEM_CODE,
2152 STATUS,
2153 MESSAGE
2154 FROM EGO_PUBLICATION_BATCH_GT
2155 WHERE BATCH_ID = l_batch_id;
2156
2157 BEGIN
2158 L_PROC_NAME := ' Write_Errors_ToBatFwk=> ';
2159 --debug(L_PROC_NAME||' Start');
2160 --debug(L_PROC_NAME||' Parameters - p_session_id: '||p_session_id||' p_batch_id: '||p_batch_id);
2161
2162
2163 SELECT CHAR_VALUE
2164 INTO l_trigger_import
2165 FROM EGO_PUB_WS_CONFIG
2166 WHERE SESSION_ID = p_session_id
2167 AND PARAMETER_NAME = 'TRIGGER_IMPORT';
2168
2169 --debug(L_PROC_NAME||' l_trigger_import: '||l_trigger_import);
2170
2171 -- use global temporary table (reuse the one currently of batch publication framework)
2172 -- and copy records in EGO_PUB_WS_ENTITIES into it while setting columns status equal to SUCCESS and
2173 -- SYSTEM_CODE equal to current target system for all records.
2174
2175 FOR sys IN c_system_codes(p_session_id)
2176 LOOP
2177 --debug(L_PROC_NAME||' inside first For Loop on c_system_codes ');
2178 l_system_code := sys.CHAR_VALUE;
2179 l_invld_sys_cnt := 0;
2180 --debug(L_PROC_NAME||' l_system_code: '||sys.CHAR_VALUE);
2181
2182 -- invalid system stored in EGO_PUB_WS_ERRORS for the p_session_id
2183 -- get count for the l_system_code
2184 -- if it is equal to zero then only for that system code
2185 -- send the status back to batch fwk table EGO_PUB_BAT_STATUS_B
2186
2187 BEGIN
2188 SELECT count(1)
2189 INTO l_invld_sys_cnt
2190 FROM EGO_PUB_WS_ERRORS
2191 WHERE SESSION_ID = p_session_id
2192 AND ERR_CODE = 'D'
2193 AND SYSTEM_CODE = l_system_code;
2194 EXCEPTION
2195 WHEN NO_DATA_FOUND THEN
2196 NULL;
2197 WHEN OTHERS THEN
2198 NULL;
2199 END;
2200
2201 --debug(L_PROC_NAME||' l_invld_sys_cnt: '||l_invld_sys_cnt);
2202
2203 -- if the system is valid
2204 IF (l_invld_sys_cnt = 0) THEN
2205
2206 -- If concurrent import program is not run on target system
2207 -- for all entities of the batch provide common message conveying
2208 -- to run concurrent import program manually
2209
2210 IF (l_trigger_import = 'N' OR l_trigger_import = 'FALSE') THEN
2211 --debug(L_PROC_NAME||' when l_trigger_import is N/FALSE : '||l_trigger_import);
2212 FND_MESSAGE.SET_NAME('EGO', 'EGO_NO_AUTO_IMPORT_ON_TARGET');
2213 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2214
2215 FOR ent IN c_pub_ws_entities(p_session_id)
2216 LOOP
2217 --debug(L_PROC_NAME||' entered first For Loop on c_pub_ws_entities, ');
2218
2219 --debug(L_PROC_NAME||' ent.pk1_value: ' || to_char(ent.pk1_value));
2220 INSERT INTO EGO_PUBLICATION_BATCH_GT
2221 (BATCH_ID,
2222 BATCH_ENTITY_OBJECT_ID,
2223 PK1_VALUE,
2224 PK2_VALUE,
2225 PK3_VALUE,
2226 PK4_VALUE,
2227 PK5_VALUE,
2228 USER_ENTERED,
2229 SYSTEM_CODE,
2230 STATUS,
2231 MESSAGE,
2232 PROCESS_FLAG,
2233 RETURN_STATUS,
2234 RETURN_ERROR_MESSAGE,
2235 CREATED_BY,
2236 CREATION_DATE,
2237 LAST_UPDATED_BY,
2238 LAST_UPDATE_DATE
2239 )
2240 VALUES (l_batch_id,
2241 NULL,
2242 ent.PK1_VALUE,
2243 ent.PK2_VALUE,
2244 ent.PK3_VALUE,
2245 ent.PK4_VALUE,
2246 ent.PK5_VALUE,
2247 ent.USER_ENTERED,
2248 l_system_code,
2249 'I',
2250 l_message,
2251 NULL,
2252 NULL,
2253 NULL,
2254 -1,
2255 SYSDATE,
2256 -1,
2257 SYSDATE
2258 );
2259 END LOOP;
2260
2261 ELSIF (l_trigger_import = 'Y' OR l_trigger_import = 'TRUE') THEN
2262 --debug(L_PROC_NAME||' when l_trigger_import is Y/TRUE : '||l_trigger_import);
2263
2264 -- if concurrent import program is run on target system then
2265 -- for all the entities of the batch, initial status would be Success
2266 -- in to Global Temporary (GT) table,
2267 -- after that correspondig status for error or warning will be posted
2268 -- for the entities as a next transaction in to GT table.
2269 FOR ent IN c_pub_ws_entities(p_session_id)
2270 LOOP
2271 --debug(L_PROC_NAME||' entered second For Loop on c_pub_ws_entities ');
2272 --debug(L_PROC_NAME||'ent.pk1_value: ' || to_char(ent.pk1_value));
2273
2274 INSERT INTO EGO_PUBLICATION_BATCH_GT
2275 (BATCH_ID,
2276 BATCH_ENTITY_OBJECT_ID,
2277 PK1_VALUE,
2278 PK2_VALUE,
2279 PK3_VALUE,
2280 PK4_VALUE,
2281 PK5_VALUE,
2282 USER_ENTERED,
2283 SYSTEM_CODE,
2284 STATUS,
2285 MESSAGE,
2286 PROCESS_FLAG,
2287 RETURN_STATUS,
2288 RETURN_ERROR_MESSAGE,
2289 CREATED_BY,
2290 CREATION_DATE,
2291 LAST_UPDATED_BY,
2292 LAST_UPDATE_DATE
2293 )
2294 VALUES (l_batch_id,
2295 NULL,
2296 ent.PK1_VALUE,
2297 ent.PK2_VALUE,
2298 ent.PK3_VALUE,
2299 ent.PK4_VALUE,
2300 ent.PK5_VALUE,
2301 ent.USER_ENTERED,
2302 l_system_code,
2303 'S',
2304 NULL,
2305 NULL,
2306 NULL,
2307 NULL,
2308 -1,
2309 SYSDATE,
2310 -1,
2311 SYSDATE
2312 );
2313 END LOOP;
2314
2315 END IF; /*end of if on trigger_import*/
2316 --debug(L_PROC_NAME||' end of if on trigger_import');
2317
2318 END IF; /*end of if on valid sys*/
2319 --debug(L_PROC_NAME||' end of if on valid sys');
2320
2321 -- if the system is invalid
2322 -- insert records for invalid system with error message
2323 IF (l_invld_sys_cnt > 0) THEN
2324 --debug(L_PROC_NAME||' if l_invld_sys_cnt > 0 '||l_invld_sys_cnt);
2325
2326 FND_MESSAGE.SET_NAME('EGO', 'EGO_SYS_CODE_NO_CONTEXT');
2327 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2328 FOR ent IN c_pub_ws_entities(p_session_id)
2329 LOOP
2330 --debug(L_PROC_NAME||' entered third For Loop on c_pub_ws_entities ');
2331 --debug('ent.pk1_value: ' || to_char(ent.pk1_value));
2332
2333 INSERT INTO EGO_PUBLICATION_BATCH_GT
2334 ( BATCH_ID,
2335 BATCH_ENTITY_OBJECT_ID,
2336 PK1_VALUE,
2337 PK2_VALUE,
2338 PK3_VALUE,
2339 PK4_VALUE,
2340 PK5_VALUE,
2341 USER_ENTERED,
2342 SYSTEM_CODE,
2343 STATUS,
2344 MESSAGE,
2345 PROCESS_FLAG,
2346 RETURN_STATUS,
2347 RETURN_ERROR_MESSAGE,
2348 CREATED_BY,
2349 CREATION_DATE,
2350 LAST_UPDATED_BY,
2351 LAST_UPDATE_DATE
2352 )
2353 VALUES (l_batch_id,
2354 NULL,
2355 ent.PK1_VALUE,
2356 ent.PK2_VALUE,
2357 ent.PK3_VALUE,
2358 ent.PK4_VALUE,
2359 ent.PK5_VALUE,
2360 ent.USER_ENTERED,
2361 l_system_code,
2362 'F',
2363 l_message,
2364 NULL,
2365 NULL,
2366 NULL,
2367 -1,
2368 SYSDATE,
2369 -1,
2370 SYSDATE
2371 );
2372 END LOOP; /* end of if on c_pub_entities*/
2373 --debug(L_PROC_NAME||' end of third For Loop on c_pub_ws_entities ');
2374 END IF; /* end of if on invalid sys*/
2375 --debug(L_PROC_NAME||' end of if on invalid sys - 2 ');
2376 END LOOP; /* end of loop on systems*/
2377 --debug(L_PROC_NAME||' end of Loop on c_system_codes ');
2378
2379 -- For each record in EGO_PUB_WS_ERRORS , join with EGO_PUB_WS_INPUT_IDENTIFIERS
2380 -- using INPUT_ID, SESSION_ID, and SYSTEM_CODE
2381 -- to retrieve PKs from EGO_PUB_WS_INPUT_IDENTIFIERS, and ERR_CODE and ERR_MESSAGE from EGO_PUB_WS_ERRORS
2382 -- and update records in global temporary table accordingly using PKs.
2383
2384 IF (l_trigger_import = 'Y' OR l_trigger_import = 'TRUE') THEN
2385 --debug(L_PROC_NAME||' entered second l_trigger_import = Y/TRUE ');
2386
2387 FOR sys IN c_system_codes(p_session_id)
2388 LOOP
2389 --debug(L_PROC_NAME||' entered second For Loop on c_system_codes, l_system_code: '||sys.CHAR_VALUE);
2390 l_system_code := sys.CHAR_VALUE;
2391 l_invld_sys_cnt := 0;
2392
2393 -- invalid system stored in EGO_PUB_WS_ERRORS for the p_session_id
2394 -- get count for the l_system_code
2395 -- if it is equal to zero then only for that system code
2396 -- send the status back to batch fwk table EGO_PUB_BAT_STATUS_B
2397
2398 BEGIN
2399 SELECT count(1)
2400 INTO l_invld_sys_cnt
2401 FROM EGO_PUB_WS_ERRORS
2402 WHERE SESSION_ID = p_session_id
2403 AND ERR_CODE = 'D'
2404 AND SYSTEM_CODE = l_system_code;
2405 EXCEPTION
2406 WHEN NO_DATA_FOUND THEN
2407 NULL;
2408 WHEN OTHERS THEN
2409 NULL;
2410 END;
2411
2412 -- if the system is valid
2413 --debug(L_PROC_NAME||' second l_invld_sys_cnt: '||l_invld_sys_cnt);
2414 IF (l_invld_sys_cnt = 0) THEN
2415
2416 --debug(L_PROC_NAME||' entered second if on l_invld_sys_cnt = 0');
2417 FOR ip IN c_input_identifier(p_session_id, l_system_code)
2418 LOOP
2419 --debug(L_PROC_NAME||' entered For Loop on c_input_identifier, p_session_id: '||p_session_id||' l_system_code: '||l_system_code);
2420
2421 l_input_id := ip.INPUT_ID;
2422 l_param_value := ip.PARAM_VALUE;
2423 --debug(L_PROC_NAME||' l_input_id ' || to_char(l_input_id)||' l_param_value: '||ip.PARAM_VALUE);
2424
2425 BEGIN
2426 SELECT count(1)
2427 INTO l_error_rec_count
2428 FROM EGO_PUB_WS_ERRORS
2429 WHERE session_id = p_session_id
2430 AND system_code = l_system_code
2431 AND input_id = l_input_id;
2432 EXCEPTION
2433 WHEN NO_DATA_FOUND THEN
2434 NULL;
2435 WHEN OTHERS THEN
2436 NULL;
2437 END;
2438
2439 FOR err IN c_pub_ws_error(p_session_id, l_input_id, l_system_code)
2440 LOOP
2441 --debug(L_PROC_NAME||' entered For Loop on c_pub_ws_error');
2442 /*Note that for every entity, ego_pub_ws_errors will either have an Error or a Warning
2443 but not both. This is ensured in the ODI interface EGO_LOG_ICC_ERROR_MSGS.
2444 It marks an entity as a warning, only if it is not already marked as an error.*/
2445 IF (instr(err.ERR_CODE, '_ERROR') <> 0) THEN
2446 --debug(L_PROC_NAME||' entered ERROR scenario');
2447 IF ((l_error_rec_count > 1) AND (err.ERR_CODE <> 'EGO_SYNC_MIB_STAT_1_ERROR')) THEN
2448
2449 --debug(L_PROC_NAME||' entered if on l_error_rec_count > 1 AND err.ERR_CODE <> EGO_SYNC_MIB_STAT_1_ERROR');
2450 FND_MESSAGE.SET_NAME('EGO', err.ERR_CODE);
2451 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', l_param_value);
2452 FND_MESSAGE.SET_TOKEN ('SYSTEM_CODE', l_system_code);
2453 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2454
2455 /* ip.PK2_VALUE is NULL implies that the ip record is for the additional ICC alone record.
2456 Note that in create_entities_icc procedure, for every ICC Ver record, we inserted an
2457 additional ICC alone record for syncing up the non-versioned info like Pages, AGs etc.
2458 Any error marked for this additional ICC record has to be copied to the actual ICC Ver
2459 record since only the ICC Version record will be present in the batch.
2460 So, in the below update statement, we have the filter only on PK1_VALUE, so that
2461 all ICC Ver records having PK2_VALUE will also get updated with Failure status
2462 if the additional ICC alone record gets failed.
2463 ICC Ver records can only have a Error msg logged, they cannot have a warning message logged.
2464 This is because warning messages are only logged during hierarchy traversal by AG.
2465 AG is always associated to the additional ICC alone record and not any ICC Ver record.*/
2466 /*kkq - below update should also have filter on param_name (eg) ICC.
2467 Otherwise, in rare scenarios, pk1_value may be same for ICC,AG,VS etc*/
2468
2469 --debug(L_PROC_NAME||' ip.PARAM_NAME: '||ip.PARAM_NAME||' ip.PK2_VALUE: '||To_Char(ip.PK2_VALUE));
2470 IF ( (ip.PARAM_NAME=G_ENTITY_TYPE_ICC) AND (ip.PK2_VALUE IS NULL) ) THEN
2471
2472 --debug(L_PROC_NAME||' entered if on param_name=ICC AND PK2_VALUE IS NULL - first time');
2473 UPDATE EGO_PUBLICATION_BATCH_GT
2474 SET STATUS = 'F', MESSAGE = l_message
2475 WHERE BATCH_ID = l_batch_id
2476 AND PK1_VALUE = ip.PK1_VALUE
2477 AND SYSTEM_CODE = ip.SYSTEM_CODE;
2478
2479 ELSE /* if (ip.param_name <> G_ENTITY_ICC) OR (ip.param_name=G_ENTITY_ICC AND ip.PK2_VALUE is not NULL) */
2480
2481 --debug(L_PROC_NAME||' entered if on param_name<>ICC or PK2_VALUE IS not NULL - first time');
2482 UPDATE EGO_PUBLICATION_BATCH_GT
2483 SET STATUS = 'F', MESSAGE = l_message
2484 WHERE BATCH_ID = l_batch_id
2485 AND PK1_VALUE = ip.PK1_VALUE
2486 AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
2487 AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
2488 AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
2489 AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
2490 AND SYSTEM_CODE = ip.SYSTEM_CODE;
2491 END IF; /*end of if ip.param_name = G_ENTITY_ICC*/
2492 -- Update Err_Message in EGO_PUB_WS_ERROR table
2493
2494 UPDATE EGO_PUB_WS_ERRORS
2495 SET ERR_MESSAGE = l_message
2496 WHERE SESSION_ID = p_session_id
2497 AND INPUT_ID = l_input_id
2498 AND SYSTEM_CODE = l_system_code;
2499 --debug(L_PROC_NAME||' updated EGO_PUB_WS_ERRORS with ERR_MESSAGE - first time');
2500
2501 END IF; /*end of if on l_error_rec_count > 1*/
2502
2503 /*if l_error_rec_count=1 implies that it can be either be
2504 EGO_SYNC_MIB_STAT_1_ERROR or EGO_SYNC_MIB_STAT_ERROR */
2505 IF (l_error_rec_count = 1) THEN
2506 --debug(L_PROC_NAME||' entered if l_error_rec_count=1');
2507
2508 FND_MESSAGE.SET_NAME('EGO', err.ERR_CODE);
2509 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', l_param_value);
2510 FND_MESSAGE.SET_TOKEN ('SYSTEM_CODE', l_system_code);
2511 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2512
2513 --debug(L_PROC_NAME||'l_batch_id: ' || to_char(l_batch_id));
2514 --debug(L_PROC_NAME||'ip.PK1_VALUE: ' || to_char(ip.PK1_VALUE));
2515 --debug(L_PROC_NAME||'ip.SYSTEM_CODE: ' || to_char(ip.SYSTEM_CODE));
2516
2517 IF ( (ip.PARAM_NAME=G_ENTITY_TYPE_ICC) AND (ip.PK2_VALUE IS NULL) ) THEN
2518 --debug(L_PROC_NAME||' entered if on param_name=ICC AND PK2_VALUE IS NULL - second time');
2519 UPDATE EGO_PUBLICATION_BATCH_GT
2520 SET STATUS = 'F', MESSAGE = l_message
2521 WHERE BATCH_ID = l_batch_id
2522 AND PK1_VALUE = ip.PK1_VALUE
2523 AND SYSTEM_CODE = ip.SYSTEM_CODE;
2524
2525 ELSE /* if (ip.param_name <> G_ENTITY_ICC) OR (ip.param_name=G_ENTITY_ICC AND ip.PK2_VALUE is not NULL) */
2526
2527 --debug(L_PROC_NAME||' entered if on param_name<>ICC or PK2_VALUE IS not NULL - second time');
2528 UPDATE EGO_PUBLICATION_BATCH_GT
2529 SET STATUS = 'F', MESSAGE = l_message
2530 WHERE BATCH_ID = l_batch_id
2531 AND PK1_VALUE = ip.PK1_VALUE
2532 AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
2533 AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
2534 AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
2535 AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
2536 AND SYSTEM_CODE = ip.SYSTEM_CODE;
2537 END IF; /*end of if ip.param_name = G_ENTITY_ICC*/
2538
2539 -- Update Err_Message in EGO_PUB_WS_ERROR table
2540
2541 UPDATE EGO_PUB_WS_ERRORS
2542 SET ERR_MESSAGE = l_message
2543 WHERE SESSION_ID = p_session_id
2544 AND INPUT_ID = l_input_id
2545 AND SYSTEM_CODE = l_system_code;
2546 --debug(L_PROC_NAME||' updated EGO_PUB_WS_ERRORS with ERR_MESSAGE - second time');
2547
2548 END IF; /*end of if on l_error_rec_count=1*/
2549 --debug(L_PROC_NAME||' end of if on l_error_rec_count=1');
2550
2551 END IF; /*end of if on (instr(err.ERR_CODE, '_ERROR') <> 0)*/
2552 --debug(L_PROC_NAME||' end of if on instr err.ERR_CODE, _ERROR <> 0');
2553
2554 IF (instr(err.ERR_CODE, '_WARNING') <> 0) THEN
2555 --debug(L_PROC_NAME||' entered WARNING scenario');
2556
2557 FND_MESSAGE.SET_NAME ('EGO', err.ERR_CODE);
2558 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', l_param_value);
2559 FND_MESSAGE.SET_TOKEN ('SYSTEM_CODE', l_system_code);
2560 l_message := SUBSTR(FND_MESSAGE.Get, 1, 2000);
2561
2562 --debug(L_PROC_NAME||' ip.PARAM_NAME: '||ip.PARAM_NAME||' ip.PK2_VALUE: '||To_Char(ip.PK2_VALUE));
2563 IF ( (ip.PARAM_NAME=G_ENTITY_TYPE_ICC) AND (ip.PK2_VALUE IS NULL) ) THEN
2564 --debug(L_PROC_NAME||' entered if on param_name=ICC AND PK2_VALUE IS NULL - third time');
2565
2566 UPDATE EGO_PUBLICATION_BATCH_GT
2567 SET STATUS = 'W', MESSAGE = l_message
2568 WHERE BATCH_ID = l_batch_id
2569 AND PK1_VALUE = ip.PK1_VALUE
2570 AND SYSTEM_CODE = ip.SYSTEM_CODE;
2571
2572 ELSE /* if (ip.param_name <> G_ENTITY_ICC) OR (ip.param_name=G_ENTITY_ICC AND ip.PK2_VALUE is not NULL) */
2573 --debug(L_PROC_NAME||' entered if on param_name<>ICC or PK2_VALUE IS not NULL - third time');
2574
2575 /* ip.PK2_VALUE is not null implies ICC Ver record.
2576 Ideally, this else condition should never be reached
2577 since ICC Ver record can only have an Error msg not Warning*/
2578 UPDATE EGO_PUBLICATION_BATCH_GT
2579 SET STATUS = 'W', MESSAGE = l_message
2580 WHERE BATCH_ID = l_batch_id
2581 AND PK1_VALUE = ip.PK1_VALUE
2582 AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
2583 AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
2584 AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
2585 AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
2586 AND SYSTEM_CODE = ip.SYSTEM_CODE;
2587 END IF; /*end of if ip.param_name = G_ENTITY_ICC*/
2588
2589 -- Update Err_Message in EGO_PUB_WS_ERROR table
2590
2591 UPDATE EGO_PUB_WS_ERRORS
2592 SET ERR_MESSAGE = l_message
2593 WHERE SESSION_ID = p_session_id
2594 AND INPUT_ID = l_input_id
2595 AND SYSTEM_CODE = l_system_code;
2596 --debug(L_PROC_NAME||' updated EGO_PUB_WS_ERRORS with ERR_MESSAGE - third time');
2597
2598 END IF; /*end of if on (instr(err.ERR_CODE, '_WARNING') <> 0)*/
2599 --debug(L_PROC_NAME||' end of if on (instr(err.ERR_CODE, _WARNING) <> 0');
2600 END LOOP; /*end of loop on c_pub_ws_error*/
2601 --debug(L_PROC_NAME||' end of loop on c_pub_ws_error');
2602
2603 l_error_rec_count := 0;
2604
2605 END LOOP; /* end of loop on c_input_identifiers*/
2606 --debug(L_PROC_NAME||' end of loop on c_input_identifiers');
2607
2608 END IF; /* end of if on l_invld_sys_code=0*/
2609 --debug(L_PROC_NAME||' end of if on l_invld_sys_code=0');
2610
2611 END LOOP; /* end loop on c_system_codes*/
2612 --debug(L_PROC_NAME||' end loop on c_system_codes');
2613
2614 END IF; /*end of if trigger_import=Y/TRUE */
2615 --debug(L_PROC_NAME||' end of if trigger_import=Y/TRUE');
2616
2617 -- For each record in the temporary global table, invoke Update_Pub_Status API with Batch_id,
2618 -- PKs, and Status, and Message to log the errors per entity per target system to batch
2619 -- publication framework.
2620
2621 v_index := 1;
2622 FOR gt IN c_pub_bat_gt(l_batch_id)
2623 LOOP
2624 --debug(L_PROC_NAME||' entered For Loop on c_pub_bat_gt');
2625
2626 -- Prepare the record for each entity
2627 --debug(L_PROC_NAME||'v_index: ' || to_char(v_index));
2628 --debug(L_PROC_NAME||'gt.batch_id: ' || to_char(gt.batch_id));
2629 --debug(L_PROC_NAME||'gt.pk1_value: ' || gt.pk1_value);
2630 --debug(L_PROC_NAME||'gt.SYSTEM_CODE: ' || gt.SYSTEM_CODE);
2631 --debug(L_PROC_NAME||'gt.status: ' || gt.status);
2632 --debug(L_PROC_NAME||'gt.message: ' || gt.message);
2633
2634 bat_ent_stat_rec(v_index).batch_id := gt.batch_id;
2635 bat_ent_stat_rec(v_index).pk1_value := gt.pk1_value;
2636 bat_ent_stat_rec(v_index).pk2_value := gt.pk2_value;
2637 bat_ent_stat_rec(v_index).pk3_value := gt.pk3_value;
2638 bat_ent_stat_rec(v_index).pk4_value := gt.pk4_value;
2639 bat_ent_stat_rec(v_index).pk5_value := gt.pk5_value;
2640 bat_ent_stat_rec(v_index).system_code := gt.system_code;
2641 bat_ent_stat_rec(v_index).status := gt.status;
2642 bat_ent_stat_rec(v_index).message := gt.message;
2643
2644 --debug(L_PROC_NAME||'bat_ent_stat_rec('||to_char(v_index)||').batch_id:' || to_char(bat_ent_stat_rec(v_index).batch_id));
2645 --debug(L_PROC_NAME||'bat_ent_stat_rec('||to_char(v_index)||').pk1_value: ' || bat_ent_stat_rec(v_index).pk1_value);
2646 --debug(L_PROC_NAME||'bat_ent_stat_rec('||to_char(v_index)||').system_code: ' || bat_ent_stat_rec(v_index).system_code);
2647 --debug(L_PROC_NAME||'bat_ent_stat_rec('||to_char(v_index)||').status: ' || bat_ent_stat_rec(v_index).status);
2648 --debug(L_PROC_NAME||'bat_ent_stat_rec('||to_char(v_index)||').message: ' || bat_ent_stat_rec(v_index).message);
2649
2650 v_index := v_index + 1;
2651 --debug(L_PROC_NAME||' at the end of loop iteration, incremented v_index to :'||v_index);
2652
2653 END LOOP; /*end of loop on c_pub_bat_gt*/
2654 --debug(L_PROC_NAME||' end of loop on c_pub_bat_gt, calling EGO_PUB_FWK_PK.Update_Pub_Status');
2655
2656 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);
2657 --debug(L_PROC_NAME||' returned from EGO_PUB_FWK_PK.Update_Pub_Status with x_return_status: '||x_return_status);
2658
2659 -- do we need to get x_bat_status_out (TBL_OF_BAT_ENT_OBJ_RSTS_TYPE --- TABLE OF BAT_ENT_OBJ_RET_STAT_TYPE )
2660 IF (x_return_status <> 'S') THEN
2661 --debug(L_PROC_NAME||'return status : ' || x_return_status);
2662 l_return_status := x_return_status;
2663 END IF;
2664 --debug(L_PROC_NAME||' End');
2665 END Write_Errors_ToBatFwk;
2666
2667 END EGO_PUB_WS_ICC;
[Home] [Help]