[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_WS_PVT
Source
1 PACKAGE BODY EGO_ITEM_WS_PVT AS
2 /* $Header: EGOVIWSB.pls 120.7 2011/09/05 15:11:39 nendrapu noship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : EGOVIWSB.pls |
9 | DESCRIPTION : This file contains the procedures required for |
10 | Item Web service. |
11 | |
12 | Hisrtory |
13 | NENDRAPU 17-Mar-2011 Performance Changes: |
14 | Instead of ego_all_attr_base_v use ext_b table. |
15 | No Need of query to check for ego_all_attr_lang_v |
16 | Changed all the remaning queries to use tables |
17 | instead of views. |
18 | Bug 11738301 : Introduced query to get the |
19 | AG details from Style to SKU. |
20 | |
21 | NENDRAPU 14-Apr-2011 Bug 12359959 : Changed the way of reading the |
22 | inputs in List mode. |
23 | |
24 | NENDRAPU 15-Aug-2011 Bug 12770968 : Introduced the code to publish |
25 | variant attributes for style items |
26 | |
27 +==========================================================================*/
28 e_invalid_invocation_mode EXCEPTION;
29 e_no_org_details EXCEPTION;
30 e_no_rev_details EXCEPTION;
31
32 /* Procedure Inserts the UDA values for the publishing items,
33 * for a given business entity level in to the table EGO_PUB_WS_FLAT_RECS.
34 */
35
36 /* Below are the different values stored in EGO_PUB_WS_FLAT_RECS for AG, UDA and transalateble UDAs.
37
38 Column in AG UDA UDA-For Translatable value
39 Flat Rec table
40 ============== ============= ============== =============================
41 REF1_VALUE : attr_grp_id attr_grp_id attr_grp_id
42 REF2_VALUE : exension id exension id exension id
43 REF3_VALUE : data_level_id data_level_id data_level_id
44 REF4_VALUE : org id org id org id
45 REF5_VALUE : ATTRIBUTE_ID ATTRIBUTE_ID
46 REF6_VALUE : "TRANSLATED_CHAR_VALUE"
47
48 */
49
50 PROCEDURE POPULATE_AGS(sessionId IN NUMBER,
51 odisessionId IN NUMBER,
52 dataLevelId IN NUMBER
53 )
54 AS
55
56 v_count NUMBER;
57 v_publish_udas VARCHAR2(100);
58 v_query_string_b VARCHAR2(32767);
59 v_query_string VARCHAR2(32767);
60 v_query_string_Inherit_b VARCHAR2(32767); -- Bug 11738301
61
62 v_attribute_group_id EGO_MTL_SY_ITEMS_EXT_B.ATTR_GROUP_ID%TYPE;
63 v_attribute_group_name EGO_ATTR_GROUPS_V.ATTR_GROUP_NAME%TYPE;
64 v_organization_id EGO_MTL_SY_ITEMS_EXT_B.organization_id%TYPE;
65 v_inventory_item_id EGO_MTL_SY_ITEMS_EXT_B.inventory_item_id%TYPE;
66 v_revision_id EGO_MTL_SY_ITEMS_EXT_B.revision_id%TYPE;
67 v_application_id EGO_ATTR_GROUPS_V.application_id%TYPE;
68 v_extension_id EGO_MTL_SY_ITEMS_EXT_B.extension_id%TYPE;
69 v_data_level_id EGO_MTL_SY_ITEMS_EXT_B.data_level_id%TYPE;
70 v_pk1_value EGO_MTL_SY_ITEMS_EXT_B.pk1_value%TYPE;
71 v_pk2_value EGO_MTL_SY_ITEMS_EXT_B.pk2_value%TYPE;
72 v_data_level_name EGO_DATA_LEVEL_VL.user_data_level_name%TYPE;
73 v_sequence_id EGO_PUB_WS_FLAT_RECS.sequence_id%TYPE;
74 v_pk3_value EGO_ODI_WS_ENTITIES.pk3_value%TYPE;
75 v_org_id EGO_PUB_WS_FLAT_RECS.PK2_VALUE%TYPE;
76
77 -- Performance changes
78 -- TYPE DYNAMIC_CUR IS REF CURSOR;
79 -- v_dynamic_cursor DYNAMIC_CUR;
80
81 BEGIN
82
83 select char_value INTO v_publish_udas
84 from EGO_PUB_WS_CONFIG
85 where session_id = sessionId
86 and PARAMETER_NAME = 'PUBLISH_UDA_GROUPS';
87
88 IF ( v_publish_udas = 'Y')
89 THEN
90
91 SELECT Count(*) INTO v_count
92 FROM EGO_PUB_WS_CONFIG
93 WHERE SESSION_ID = sessionId
94 AND PARAMETER_NAME = 'PUBLISH_AG_NAME';
95
96 -- Bug 12770968 : Start
97 -- If datalevel is 43101 (i.e Item level), then get the varinat attributes for style items if any.
98 IF (dataLevelId = 43101)
99 THEN
100 -- Insert the variant AG details for style items if any.
101 insert into EGO_PUB_WS_FLAT_RECS
102 (
103 SEQUENCE_ID,
104 SESSION_ID,
105 ODI_SESSION_ID,
106 ENTITY_TYPE,
107 PK1_VALUE,
108 PK2_VALUE,
109 PK3_VALUE,
110 REF1_VALUE,
111 REF3_VALUE,
112 REF4_VALUE,
113 REF6_VALUE,
114 PARENT_SEQUENCE_ID,
115 VALUE,
116 CREATION_DATE
117 )
118 select
119 EGO_PUB_WS_FLAT_RECS_S.nextval,
120 sessionId,
121 odiSessionId,
122 'ATTRIBUTE_GROUP',
123 INVENTORY_ITEM_ID,
124 ORGANIZATION_ID,
125 PK3_VALUE,
126 ATTRIBUTEGROUP_ID,
127 43101,
128 ORGANIZATION_ID,
129 'VARIANT',
130 SEQUENCE_ID,
131 XMLForest(
132 ATTRIBUTEGROUP_ID AS "ATTRIBUTEGROUP_ID",
133 ATTRIBUTE_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME",
134 NULL AS "EXTENSION_ID",
135 DATA_LEVEL_NAME AS "DATA_LEVEL_NAME"
136 ).getclobval() ,
137 SYSDATE
138 FROM
139 (
140
141 SELECT DISTINCT
142 agv.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID",
143 'Item' AS "DATA_LEVEL_NAME",
144 attr.ATTR_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME",
145 ent.PK1_VALUE AS "INVENTORY_ITEM_ID",
146 ent.PK2_VALUE AS "ORGANIZATION_ID",
147 ent.PK3_VALUE, FLAT.SEQUENCE_ID
148 FROM
149 ego_attrs_v attr, EGO_STYLE_VARIANT_ATTR_VS var,
150 ego_attr_groups_v agv, mtl_system_items_b msib,
151 EGO_ODI_WS_ENTITIES ent, EGO_PUB_WS_FLAT_RECS flat
152 WHERE
153 var.ATTRIBUTE_ID = attr.ATTR_ID
154 AND agv.ATTR_GROUP_NAME = attr.ATTR_GROUP_NAME
155 AND attr.APPLICATION_ID = agv.APPLICATION_ID
156 AND agv.variant = 'Y'
157 AND var.INVENTORY_ITEM_ID = ent.PK1_VALUE
158 AND ent.SESSION_ID = sessionId
159 AND ent.SESSION_ID = FLAT.SESSION_ID
160 AND ent.PK1_VALUE = FLAT.PK1_VALUE
161 AND ent.PK2_VALUE = FLAT.PK2_VALUE
162 AND ent.PK3_VALUE = FLAT.PK3_VALUE
163 AND FLAT.ENTITY_TYPE = 'ITEM'
164 AND msib.INVENTORY_ITEM_ID = To_Number(ent.PK1_VALUE)
165 AND msib.ORGANIZATION_ID = To_Number(ent.PK2_VALUE)
166 AND msib.style_item_flag = 'Y'
167 );
168
169 /* If any varinat AG details are inserted in above query, then execute the below query to insert their
170 attributes and value set names.
171 */
172 IF (SQL%ROWCOUNT > 0 ) THEN
173 insert INTO EGO_PUB_WS_FLAT_RECS
174 (
175 SEQUENCE_ID,
176 SESSION_ID,
177 ODI_SESSION_ID,
178 ENTITY_TYPE,
179 PK1_VALUE,
180 PK2_VALUE,
181 PK3_VALUE,
182 REF1_VALUE,
183 REF3_VALUE ,
184 REF4_VALUE ,
185 REF5_VALUE ,
186 PARENT_SEQUENCE_ID,
187 VALUE,
188 CREATION_DATE
189 )
190 SELECT
191 EGO_PUB_WS_FLAT_RECS_S.nextval,
192 sessionId,
193 odisessionId,
194 'UDA',
195 INVENTORY_ITEM_ID ,
196 ORGANIZATION_ID ,
197 PK3_VALUE ,
198 ATTRIBUTEGROUP_ID ,
199 43101 ,
200 ORGANIZATION_ID,
201 ATTRIBUTE_ID ,
202 SEQUENCE_ID,
203 XMLForest(
204 ATTR_NAME AS "ATTRIBUTE_NAME",
205 ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
206 NULL AS "ATTRIBUTE_NUMBER_VALUE",
207 NULL AS "ATTRIBUTE_UOM_VALUE",
208 NULL AS "ATTRIBUTE_DATE_VALUE",
209 NULL AS "ATTRIBUTE_DATETIME_VALUE" ,
210 NULL AS "DISPLAY_VALUE"
211 ).getclobval() ,
212 SYSDATE
213 FROM
214 (
215 SELECT
216 agv.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID",
217 attr.ATTR_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME",
218 attr.VALUE_SET_NAME AS "ATTRIBUTE_CHAR_VALUE",
219 ent.PK1_VALUE AS "INVENTORY_ITEM_ID",
220 ent.PK2_VALUE AS "ORGANIZATION_ID",
221 var.ATTRIBUTE_ID , attr.ATTR_NAME ,
222 var.VALUE_SET_ID , ent.PK3_VALUE ,
223 FLAT.SEQUENCE_ID
224 FROM
225 ego_attrs_v attr, EGO_STYLE_VARIANT_ATTR_VS var, ego_attr_groups_v agv,
226 EGO_ODI_WS_ENTITIES ent, EGO_PUB_WS_FLAT_RECS flat
227 WHERE
228 var.ATTRIBUTE_ID = attr.ATTR_ID
229 AND agv.ATTR_GROUP_NAME = attr.ATTR_GROUP_NAME
230 AND attr.APPLICATION_ID = agv.APPLICATION_ID
231 AND agv.variant = 'Y'
232 AND var.INVENTORY_ITEM_ID = To_Number(ent.PK1_VALUE)
233 AND ent.SESSION_ID = sessionId
234 AND ent.SESSION_ID = FLAT.SESSION_ID
235 AND ent.PK1_VALUE = FLAT.PK1_VALUE
236 AND ent.PK2_VALUE = FLAT.PK2_VALUE
237 AND ent.PK3_VALUE = FLAT.PK3_VALUE
238 AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
239 AND FLAT.REF6_VALUE = 'VARIANT'
240 AND FLAT.REF3_VALUE = 43101
241 AND agv.ATTR_GROUP_ID = To_Number(FLAT.REF1_VALUE)
242 );
243 END IF; -- end of IF (SQL%ROWCOUNT > 0)
244 END IF; -- end of IF (dataLevelId = 43101)
245 -- Bug 12770968 : End
246
247 v_query_string_b := 'SELECT egob.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" , ' ||
248 ' AG.ATTR_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME", '||
249 ' egob.ORGANIZATION_ID , '||
250 ' egob.INVENTORY_ITEM_ID , '||
251 ' egob.REVISION_ID , '||
252 ' AG.APPLICATION_ID , '||
253 ' egob.EXTENSION_ID , '||
254 ' egob.DATA_LEVEL_ID , '||
255 ' egob.PK1_VALUE , '||
256 ' egob.PK2_VALUE , '||
257 ' edlv.USER_DATA_LEVEL_NAME AS "DATA_LEVEL_NAME" , '||
258 ' flat.SEQUENCE_ID , '||
259 ' ent.PK3_VALUE AS "PK3_VALUE" , '||
260 ' flat.PK2_VALUE AS "ORG_ID" '||
261 ' FROM EGO_MTL_SY_ITEMS_EXT_B egob, EGO_ATTR_GROUPS_V AG, '||
262 ' EGO_DATA_LEVEL_VL edlv , EGO_ODI_WS_ENTITIES ent, EGO_PUB_WS_FLAT_RECS flat '||
263 ' WHERE egob.DATA_LEVEL_ID = edlv.DATA_LEVEL_ID '||
264 ' AND AG.APPLICATION_ID = edlv.APPLICATION_ID '||
265 ' AND egob.ATTR_GROUP_ID = AG.ATTR_GROUP_ID '||
266 ' AND ent.SESSION_ID = :session_id '||
267 ' AND ent.SESSION_ID=FLAT.SESSION_ID AND ent.PK1_VALUE=FLAT.PK1_VALUE '||
268 ' AND ent.PK2_VALUE=FLAT.PK2_VALUE AND ent.PK3_VALUE=FLAT.PK3_VALUE ';
269
270
271 /* Bug 11738301 : Start - Added code to get the UDAs of SKU which are inherited from style */
272 /* Revision level AGs cannot be Inherited from style to SKU, So no need of inherit query for datalevel 43106 */
273 IF (datalevelId <> 43106) THEN
274 v_query_string_Inherit_b := 'SELECT egob.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" , ' ||
275 ' AG.ATTR_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME", '||
276 ' egob.ORGANIZATION_ID , '||
277 ' egob.INVENTORY_ITEM_ID , '||
278 ' egob.REVISION_ID , '||
279 ' AG.APPLICATION_ID , '||
280 ' egob.EXTENSION_ID , '||
281 ' egob.DATA_LEVEL_ID , '||
282 ' egob.PK1_VALUE , '||
283 ' egob.PK2_VALUE , '||
284 ' edlv.USER_DATA_LEVEL_NAME AS "DATA_LEVEL_NAME" , '||
285 ' flat.SEQUENCE_ID , '||
286 ' ent.PK3_VALUE AS "PK3_VALUE" , '||
287 ' flat.PK2_VALUE AS "ORG_ID" '||
288 ' FROM EGO_MTL_SY_ITEMS_EXT_B egob, EGO_ATTR_GROUPS_V AG, ego_attr_group_dl AGDL, mtl_system_items_b msib, '||
289 ' EGO_DATA_LEVEL_VL edlv , EGO_ODI_WS_ENTITIES ent, EGO_PUB_WS_FLAT_RECS flat '||
290 ' WHERE egob.DATA_LEVEL_ID = edlv.DATA_LEVEL_ID '||
291 ' AND egob.DATA_LEVEL_ID = AGDL.DATA_LEVEL_ID '||
292 ' AND AG.APPLICATION_ID = edlv.APPLICATION_ID '||
293 ' AND egob.ATTR_GROUP_ID = AG.ATTR_GROUP_ID '||
294 ' AND ent.SESSION_ID = :session_id '||
295 ' AND ent.SESSION_ID=FLAT.SESSION_ID AND ent.PK1_VALUE=FLAT.PK1_VALUE '||
296 ' AND ent.PK2_VALUE=FLAT.PK2_VALUE AND ent.PK3_VALUE=FLAT.PK3_VALUE '||
297 ' AND msib.INVENTORY_ITEM_ID = To_Number(ent.PK1_VALUE) '||
298 ' AND msib.ORGANIZATION_ID = To_Number(ent.PK2_VALUE) '||
299 ' AND msib.style_item_flag = ''N'' '||
300 ' AND AG.ATTR_GROUP_ID = AGDL.ATTR_GROUP_ID '||
301 ' AND AGDL.DEFAULTING = ''I'' ';
302 END IF;
303 -- Bug 11738301 : End
304
305 IF (datalevelId = 43101) THEN
306 /* for Item level Attribute Groups, datalevelId = 43101 */
307 v_query_string_b := v_query_string_b || 'AND egob.data_level_id = 43101 '||
308 ' AND To_Number(flat.PK1_VALUE) = egob.INVENTORY_ITEM_ID '||
309 ' AND To_Number(flat.REF1_VALUE) = egob.ORGANIZATION_ID '||
310 ' AND (nvl(egob.REVISION_ID,-1) = decode(nvl(egob.REVISION_ID,-1),-1,-1,flat.PK3_VALUE)) '||
311 ' AND FLAT.ENTITY_TYPE = ''ITEM'' ';
312
313 -- Bug 11738301 : Start
314 v_query_string_Inherit_b := v_query_string_Inherit_b || 'AND egob.data_level_id = 43101 '||
315 ' AND msib.style_item_id = egob.INVENTORY_ITEM_ID '||
316 ' AND To_Number(flat.REF1_VALUE) = egob.ORGANIZATION_ID '||
317 ' AND FLAT.ENTITY_TYPE = ''ITEM'' ';
318
319 -- Bug 11738301 : End
320 ELSIF (datalevelId = 43102) THEN
321 /* for Organization level Attribute Groups, datalevelId = 43102 */
322 v_query_string_b := v_query_string_b || ' AND egob.data_level_id = 43102 AND '||
323 ' To_Number(flat.PK1_VALUE) = egob.INVENTORY_ITEM_ID AND '||
324 ' To_Number(flat.PK2_VALUE) = egob.ORGANIZATION_ID AND '||
325 ' FLAT.ENTITY_TYPE = ''ORGANIZATION'' ';
326
327 -- Bug 11738301 : Start
328 v_query_string_Inherit_b := v_query_string_Inherit_b || 'AND egob.data_level_id = 43102 '||
329 ' AND msib.style_item_id = egob.INVENTORY_ITEM_ID '||
330 ' AND To_Number(flat.PK2_VALUE) = egob.ORGANIZATION_ID '||
331 ' AND FLAT.ENTITY_TYPE = ''ORGANIZATION'' ';
332 -- Bug 11738301 : End
333 ELSIF (datalevelId = 43103) THEN
334 /* for Supplier level Attribute Groups, datalevelId = 43103 */
335 v_query_string_b := v_query_string_b || ' AND egob.data_level_id = 43103 AND '||
336 ' To_Number(flat.PK1_VALUE) = egob.INVENTORY_ITEM_ID AND '||
337 ' flat.REF2_VALUE = egob.ORGANIZATION_ID AND '||
338 ' To_Number(flat.REF1_VALUE) = egob.PK1_VALUE AND '||
339 ' FLAT.ENTITY_TYPE = ''SUPPLIER_ASSIGNMNET'' ';
340
341 -- Bug 11738301 : Start
342 v_query_string_Inherit_b := v_query_string_Inherit_b || 'AND egob.data_level_id = 43103 '||
343 ' AND msib.style_item_id = egob.INVENTORY_ITEM_ID AND'||
344 ' flat.REF2_VALUE = egob.ORGANIZATION_ID AND '||
345 ' To_Number(flat.REF1_VALUE) = egob.PK1_VALUE AND '||
346 ' FLAT.ENTITY_TYPE = ''SUPPLIER_ASSIGNMNET'' ';
347
348 -- Bug 11738301 : End
349 ELSIF (datalevelId = 43104) THEN
350 /* for Supplier Site level Attribute Groups, datalevelId = 43104 */
351 v_query_string_b := v_query_string_b || ' AND egob.data_level_id = 43104 AND '||
352 ' To_Number(flat.PK1_VALUE) = egob.INVENTORY_ITEM_ID AND '||
353 ' flat.REF2_VALUE = egob.ORGANIZATION_ID AND '||
354 ' To_Number(flat.REF1_VALUE) = egob.PK1_VALUE AND '||
355 ' flat.REF3_VALUE = egob.PK2_VALUE AND '||
356 ' FLAT.ENTITY_TYPE = ''SUPPLIER_SITE_ASSIGNMNET'' ';
357
358 -- Bug 11738301 : Start
359 v_query_string_Inherit_b := v_query_string_Inherit_b || ' AND egob.data_level_id = 43104 AND '||
360 ' msib.style_item_id = egob.INVENTORY_ITEM_ID AND '||
361 ' flat.REF2_VALUE = egob.ORGANIZATION_ID AND '||
362 ' To_Number(flat.REF1_VALUE) = egob.PK1_VALUE AND '||
363 ' flat.REF3_VALUE = egob.PK2_VALUE AND '||
364 ' FLAT.ENTITY_TYPE = ''SUPPLIER_SITE_ASSIGNMNET'' ';
365
366 -- Bug 11738301 : End
367 ELSIF (datalevelId = 43105) THEN
368 /* for Supplier Site org level Attribute Groups, datalevelId = 43105 */
369 v_query_string_b := v_query_string_b || ' AND egob.data_level_id = 43105 AND '||
370 ' To_Number(flat.PK1_VALUE) = egob.INVENTORY_ITEM_ID AND '||
371 ' To_Number(flat.PK2_VALUE) = egob.ORGANIZATION_ID AND '||
372 ' To_Number(flat.REF1_VALUE) = egob.PK1_VALUE AND '||
373 ' flat.REF2_VALUE = egob.PK2_VALUE AND '||
374 ' FLAT.ENTITY_TYPE = ''SUPPLIER_SITE_ORG_ASSIGNMNET'' ';
375
376 -- Bug 11738301 : Start
377 v_query_string_Inherit_b := v_query_string_Inherit_b || ' AND egob.data_level_id = 43105 AND '||
378 ' msib.style_item_id = egob.INVENTORY_ITEM_ID AND '||
379 ' To_Number(flat.PK2_VALUE) = egob.ORGANIZATION_ID AND '||
380 ' To_Number(flat.REF1_VALUE) = egob.PK1_VALUE AND '||
381 ' flat.REF2_VALUE = egob.PK2_VALUE AND '||
382 ' FLAT.ENTITY_TYPE = ''SUPPLIER_SITE_ORG_ASSIGNMNET'' ';
383
384 -- Bug 11738301 : End
385 ELSIF (datalevelId = 43106) THEN
386 /* for Item Revision level Attribute Groups, datalevelId = 43106 */
387 /* Revision level AGs cannot be Inherited from style to SKU, So no need of inherit query. */
388 v_query_string_b := v_query_string_b || 'AND egob.data_level_id = 43106 '||
389 ' AND To_Number(flat.PK1_VALUE) = egob.INVENTORY_ITEM_ID '||
390 ' AND To_Number(flat.PK2_VALUE) = egob.ORGANIZATION_ID '|| -- Bug: 8656001
391 ' AND flat.PK3_VALUE = egob.REVISION_ID '||
392 ' AND FLAT.ENTITY_TYPE = ''ITEM_REVISION'' ';
393 END IF;
394
395 -- Performance Changes : Start
396 IF (v_count <> 0) THEN
397 v_query_string_b := v_query_string_b || ' AND '||
398 ' AG.ATTR_GROUP_NAME IN (SELECT char_value FROM EGO_PUB_WS_CONFIG '||
399 ' WHERE session_id = '||sessionId||
400 ' and PARAMETER_NAME = ''PUBLISH_AG_NAME'' ) ';
401 END IF;
402 -- Performance Changes : End
403
404 -- Bug 11738301 : Start
405 IF (datalevelId <> 43106) THEN
406 IF (v_count <> 0) THEN
407 v_query_string_Inherit_b := v_query_string_Inherit_b || ' AND '||
408 ' AG.ATTR_GROUP_NAME IN (SELECT char_value FROM EGO_PUB_WS_CONFIG '||
409 ' WHERE session_id = '||sessionId||
410 ' and PARAMETER_NAME = ''PUBLISH_AG_NAME'' ) ';
411 END IF;
412
413 v_query_string := '( '||
414 v_query_string_b ||
415 ') '||
416 ' UNION '||
417 '( '||
418 v_query_string_Inherit_b ||
419 ')';
420 ELSE
421 v_query_string := '( '||
422 v_query_string_b ||
423 ') ';
424 END IF;
425 -- Bug 11738301 : End
426
427 -- Performance Changes : Start
428 -- Instead of loop, having single query to insert AG details.
429 v_query_string := 'insert into EGO_PUB_WS_FLAT_RECS
430 (
431 SEQUENCE_ID,
432 SESSION_ID,
433 ODI_SESSION_ID,
434 ENTITY_TYPE,
435 PK1_VALUE,
436 PK2_VALUE,
437 PK3_VALUE,
438 REF1_VALUE,
439 REF2_VALUE,
440 REF3_VALUE,
441 REF4_VALUE,
442 PARENT_SEQUENCE_ID,
443 VALUE,
444 CREATION_DATE
445 )
446 select
447 EGO_PUB_WS_FLAT_RECS_S.nextval,'
448 ||sessionId||','
449 ||odiSessionId||',
450 ''ATTRIBUTE_GROUP'',
451 INVENTORY_ITEM_ID,
452 ORG_ID,
453 PK3_VALUE,
454 ATTRIBUTEGROUP_ID,
455 EXTENSION_ID ,
456 DATA_LEVEL_ID,
457 ORGANIZATION_ID,
458 SEQUENCE_ID,
459 XMLForest(
460 ATTRIBUTEGROUP_ID AS "ATTRIBUTEGROUP_ID",
461 ATTRIBUTE_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME",
462 EXTENSION_ID AS "EXTENSION_ID",
463 DATA_LEVEL_NAME AS "DATA_LEVEL_NAME"
464 ).getclobval() ,
465 SYSDATE '||'
466 FROM
467 ( '||v_query_string||')';
468
469
470 -- Bug 11738301 : Start
471 IF (datalevelId <> 43106) THEN
472 EXECUTE IMMEDIATE v_query_string USING sessionId ,
473 sessionId ;
474 ELSE
475 EXECUTE IMMEDIATE v_query_string USING sessionId ;
476 END IF;
477 -- Bug 11738301 : End
478
479 /*
480 BEGIN
481 OPEN v_dynamic_cursor FOR v_query_string USING sessionId ,
482 v_count ,
483 sessionId ,
484 sessionId ,
485 v_count ,
486 sessionId ;
487 LOOP
488 FETCH v_dynamic_cursor INTO v_attribute_group_id,
489 v_attribute_group_name,
490 v_organization_id ,
491 v_inventory_item_id ,
492 v_revision_id ,
493 v_application_id ,
494 v_extension_id ,
495 v_data_level_id ,
496 v_pk1_value ,
497 v_pk2_value ,
498 v_data_level_name ,
499 v_sequence_id ,
500 v_pk3_value ,
501 v_org_id ;
502
503 EXIT WHEN v_dynamic_cursor%NOTFOUND;
504
505
506
507 insert into EGO_PUB_WS_FLAT_RECS
508 (
509 SEQUENCE_ID,
510 SESSION_ID,
511 ODI_SESSION_ID,
512 ENTITY_TYPE,
513 PK1_VALUE,
514 PK2_VALUE,
515 PK3_VALUE,
516 REF1_VALUE,
517 REF2_VALUE,
518 REF3_VALUE,
519 REF4_VALUE,
520 PARENT_SEQUENCE_ID,
521 VALUE,
522 CREATION_DATE
523 )
524 select
525 EGO_PUB_WS_FLAT_RECS_S.nextval,
526 sessionId,
527 odiSessionId,
528 'ATTRIBUTE_GROUP',
529 v_inventory_item_id,
530 v_org_id,
531 v_pk3_value,
532 v_attribute_group_id,
533 v_extension_id ,
534 v_data_level_id,
535 v_organization_id,
536 v_sequence_id,
537 XMLForest(
538 v_attribute_group_id AS "ATTRIBUTEGROUP_ID",
539 v_attribute_group_name AS "ATTRIBUTE_GROUP_NAME",
540 v_extension_id AS "EXTENSION_ID",
541 v_data_level_name AS "DATA_LEVEL_NAME"
542 ).getclobval() ,
543 SYSDATE
544 FROM dual;
545
546 END LOOP;
547 CLOSE v_dynamic_cursor;
548 EXCEPTION
549 WHEN OTHERS THEN
550 IF (v_dynamic_cursor%ISOPEN) THEN
551 CLOSE v_dynamic_cursor;
552 END IF;
553 END;
554 */
555 -- Performance Changes : End
556
557 -- Performance Changes : Start
558 -- If any AG details are inserted into flat table then only process for attribute details.
559 IF (SQL%ROWCOUNT > 0 ) THEN
560 /* Insert The UDA values other than translated char value, for all the AGs that were inserted into the flat table for the corresponding level */
561 /*
562 insert into EGO_PUB_WS_FLAT_RECS
563 (
564 SEQUENCE_ID,
565 SESSION_ID,
566 ODI_SESSION_ID,
567 ENTITY_TYPE,
568 PK1_VALUE,
569 PK2_VALUE,
570 PK3_VALUE,
571 REF1_VALUE,
572 REF2_VALUE,
573 REF3_VALUE ,
574 REF4_VALUE ,
575 REF5_VALUE ,
576 PARENT_SEQUENCE_ID,
577 VALUE,
578 CREATION_DATE
579 )
580 select
581 EGO_PUB_WS_FLAT_RECS_S.nextval,
582 sessionId,
583 odisessionId,
584 'UDA',
585 flat.PK1_VALUE ,
586 flat.PK2_VALUE ,
587 flat.PK3_VALUE ,
588 list.ATTRIBUTEGROUP_ID ,
589 list.EXTENSION_ID ,
590 dataLevelId ,
591 flat.REF4_VALUE ,
592 list.ATTRIBUTE_ID ,
593 flat.SEQUENCE_ID,
594 XMLForest(
595 list.ATTRIBUTE_NAME AS "ATTRIBUTE_NAME",
596 list.ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
597 list.ATTRIBUTE_NUMBER_VALUE AS "ATTRIBUTE_NUMBER_VALUE",
598 list.ATTRIBUTE_UOM_VALUE AS "ATTRIBUTE_UOM_VALUE",
599 list.ATTRIBUTE_DATE_VALUE AS "ATTRIBUTE_DATE_VALUE",
600 list.ATTRIBUTE_DATETIME_VALUE AS "ATTRIBUTE_DATETIME_VALUE" ,
601 evsv.DISPLAY_NAME AS "DISPLAY_VALUE"
602 ).getclobval() ,
603 SYSDATE
604 FROM (
605 (SELECT
606 ATTRIBUTEGROUP_ID,
607 EXTENSION_ID,
608 ORGANIZATION_ID,
609 INVENTORY_ITEM_ID,
610 APPLICATION_ID,
611 DATA_LEVEL_ID,
612 ATTRIBUTE_ID,
613 ATTRIBUTE_NAME,
614 ATTRIBUTE_CHAR_VALUE,
615 ATTRIBUTE_NUMBER_VALUE,
616 ATTRIBUTE_UOM_VALUE,
617 ATTRIBUTE_DATE_VALUE,
618 ATTRIBUTE_DATETIME_VALUE,
619 null as TRANSLATED_CHAR_VALUE,
620 REVISION_ID
621 FROM ego_all_attr_base_v)
622 ) list , EGO_PUB_WS_FLAT_RECS flat ,
623 ego_attrs_v eav, EGO_VALUE_SET_VALUES_V evsv
624 WHERE
625 list.ATTRIBUTEGROUP_ID = FLAT.REF1_VALUE
626 AND list.EXTENSION_ID = FLAT.REF2_VALUE
627 AND list.INVENTORY_ITEM_ID = FLAT.PK1_VALUE
628 AND list.ORGANIZATION_ID = Decode(dataLevelId , 43101, flat.REF4_VALUE,
629 43103 , flat.REF4_VALUE,
630 43104 , flat.REF4_VALUE,
631 FLAT.PK2_VALUE )
632 AND nvl(list.REVISION_ID, -1) = Decode(nvl(list.REVISION_ID, -1), -1,-1,flat.PK3_VALUE)
633 AND list.DATA_LEVEL_ID = dataLevelId
634 AND FLAT.session_id = sessionId
635 AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
636
637 AND eav.VALUE_SET_ID = evsv.VALUE_SET_ID (+)
638 AND eav.attr_id = list.ATTRIBUTE_ID
639 AND Nvl(eav.enabled_flag, 'Y') = 'Y' -- Bug 9542020
640 AND
641 (
642 ( list.ATTRIBUTE_CHAR_VALUE IS NOT NULL
643 AND Nvl(evsv.FORMAT_TYPE, 'C') = 'C' -- Bug 9539538
644 AND Nvl(evsv.INTERNAL_NAME,list.ATTRIBUTE_CHAR_VALUE ) = list.ATTRIBUTE_CHAR_VALUE
645 )
646 OR
647 ( list.ATTRIBUTE_NUMBER_VALUE IS NOT NULL
648 AND Nvl(evsv.FORMAT_TYPE, 'N') = 'N' -- Bug 9539538
649 AND Nvl(evsv.INTERNAL_NAME,list.ATTRIBUTE_NUMBER_VALUE) = list.ATTRIBUTE_NUMBER_VALUE
650 )
651 OR
652 ( list.ATTRIBUTE_DATE_VALUE IS NOT NULL
653 AND Nvl(evsv.FORMAT_TYPE, 'X') = 'X' -- Bug 9539538
654 -- Bug 9615220 Should change parameters to DATEor DATETIME, then compare
655 AND Nvl(To_Date(evsv.INTERNAL_NAME, 'YYYY-MM-DD HH24:MI:SS'),To_Date(list.ATTRIBUTE_DATE_VALUE, 'MM/DD/YYYY')) = To_Date(list.ATTRIBUTE_DATE_VALUE, 'MM/DD/YYYY')
656 )
657 OR
658 ( list.ATTRIBUTE_DATETIME_VALUE IS NOT NULL
659 AND Nvl(evsv.FORMAT_TYPE, 'Y') = 'Y' -- Bug 9539538
660 -- Bug 9615220 Should change parameters to DATEor DATETIME, then compare
661 AND Nvl(To_Date(evsv.INTERNAL_NAME, 'YYYY-MM-DD HH24:MI:SS'),To_Date(list.ATTRIBUTE_DATETIME_VALUE, 'MM/DD/YYYY HH24:MI:SS')) = To_Date(list.ATTRIBUTE_DATETIME_VALUE, 'MM/DD/YYYY HH24:MI:SS')
662 )
663 );
664
665 */
666
667 /* Insert The UDA values other than translated char value, for all the AGs that were inserted into the flat table for the corresponding level */
668 insert into EGO_PUB_WS_FLAT_RECS
669 (
670 SEQUENCE_ID,
671 SESSION_ID,
672 ODI_SESSION_ID,
673 ENTITY_TYPE,
674 PK1_VALUE,
675 PK2_VALUE,
676 PK3_VALUE,
677 REF1_VALUE,
678 REF2_VALUE,
679 REF3_VALUE ,
680 REF4_VALUE ,
681 REF5_VALUE ,
682 PARENT_SEQUENCE_ID,
683 VALUE,
684 CREATION_DATE
685 )
686
687 SELECT
688 EGO_PUB_WS_FLAT_RECS_S.nextval,
689 sessionId,
690 odisessionId,
691 'UDA',
692 list.PK1_VALUE ,
693 list.PK2_VALUE ,
694 list.PK3_VALUE ,
695 list.ATTRIBUTEGROUP_ID ,
696 list.EXTENSION_ID ,
697 dataLevelId ,
698 list.REF4_VALUE ,
699 list.ATTRIBUTE_ID ,
700 list.SEQUENCE_ID,
701 XMLForest(
702 list.ATTR_NAME AS "ATTRIBUTE_NAME",
703 list.ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
704 list.ATTRIBUTE_NUMBER_VALUE AS "ATTRIBUTE_NUMBER_VALUE",
705 list.ATTRIBUTE_UOM_VALUE AS "ATTRIBUTE_UOM_VALUE",
706 list.ATTRIBUTE_DATE_VALUE AS "ATTRIBUTE_DATE_VALUE",
707 list.ATTRIBUTE_DATETIME_VALUE AS "ATTRIBUTE_DATETIME_VALUE" ,
708 evsv.DISPLAY_NAME AS "DISPLAY_VALUE"
709 ).getclobval() ,
710 SYSDATE
711 FROM
712 ( select
713 flat.PK1_VALUE ,
714 flat.PK2_VALUE ,
715 flat.PK3_VALUE ,
716 UDA.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" ,
717 UDA.EXTENSION_ID ,
718 flat.REF4_VALUE ,
719 AGC.attr_id AS "ATTRIBUTE_ID",
720 flat.SEQUENCE_ID,
721 AGC.VALUE_SET_ID,
722 AGC.ATTR_NAME,
723 DECODE (AGC.DATA_TYPE_CODE, 'C', DECODE (AGC.DATABASE_COLUMN, 'C_EXT_ATTR1', UDA.C_EXT_ATTR1, 'C_EXT_ATTR2', UDA.C_EXT_ATTR2, 'C_EXT_ATTR3', UDA.C_EXT_ATTR3, 'C_EXT_ATTR4', UDA.C_EXT_ATTR4, 'C_EXT_ATTR5', UDA.C_EXT_ATTR5,
724 'C_EXT_ATTR6', UDA.C_EXT_ATTR6, 'C_EXT_ATTR7', UDA.C_EXT_ATTR7, 'C_EXT_ATTR8', UDA.C_EXT_ATTR8, 'C_EXT_ATTR9', UDA.C_EXT_ATTR9, 'C_EXT_ATTR10', UDA.C_EXT_ATTR10,
725 'C_EXT_ATTR11', UDA.C_EXT_ATTR11, 'C_EXT_ATTR12', UDA.C_EXT_ATTR12, 'C_EXT_ATTR13', UDA.C_EXT_ATTR13, 'C_EXT_ATTR14', UDA.C_EXT_ATTR14, 'C_EXT_ATTR15', UDA.C_EXT_ATTR15,
726 'C_EXT_ATTR16', UDA.C_EXT_ATTR16, 'C_EXT_ATTR17', UDA.C_EXT_ATTR17, 'C_EXT_ATTR18', UDA.C_EXT_ATTR18, 'C_EXT_ATTR19', UDA.C_EXT_ATTR19, 'C_EXT_ATTR20', UDA.C_EXT_ATTR20,
727 'C_EXT_ATTR21', UDA.C_EXT_ATTR21, 'C_EXT_ATTR22', UDA.C_EXT_ATTR22, 'C_EXT_ATTR23', UDA.C_EXT_ATTR23, 'C_EXT_ATTR24', UDA.C_EXT_ATTR24, 'C_EXT_ATTR25', UDA.C_EXT_ATTR25,
728 'C_EXT_ATTR26', UDA.C_EXT_ATTR26, 'C_EXT_ATTR27', UDA.C_EXT_ATTR27, 'C_EXT_ATTR28', UDA.C_EXT_ATTR28, 'C_EXT_ATTR29', UDA.C_EXT_ATTR29, 'C_EXT_ATTR30', UDA.C_EXT_ATTR30,
729 'C_EXT_ATTR31', UDA.C_EXT_ATTR31, 'C_EXT_ATTR32', UDA.C_EXT_ATTR32, 'C_EXT_ATTR33', UDA.C_EXT_ATTR33, 'C_EXT_ATTR34', UDA.C_EXT_ATTR34, 'C_EXT_ATTR35', UDA.C_EXT_ATTR35,
730 'C_EXT_ATTR36', UDA.C_EXT_ATTR36, 'C_EXT_ATTR37', UDA.C_EXT_ATTR37, 'C_EXT_ATTR38', UDA.C_EXT_ATTR38, 'C_EXT_ATTR39', UDA.C_EXT_ATTR39, 'C_EXT_ATTR40', UDA.C_EXT_ATTR40 )
731 ) AS ATTRIBUTE_CHAR_VALUE,
732
733 (DECODE (AGC.DATA_TYPE_CODE, 'N', DECODE (AGC.DATABASE_COLUMN, 'N_EXT_ATTR1', UDA.N_EXT_ATTR1, 'N_EXT_ATTR2', UDA.N_EXT_ATTR2, 'N_EXT_ATTR3', UDA.N_EXT_ATTR3, 'N_EXT_ATTR4', UDA.N_EXT_ATTR4, 'N_EXT_ATTR5', UDA.N_EXT_ATTR5,
734 'N_EXT_ATTR6', UDA.N_EXT_ATTR6, 'N_EXT_ATTR7', UDA.N_EXT_ATTR7, 'N_EXT_ATTR8', UDA.N_EXT_ATTR8, 'N_EXT_ATTR9', UDA.N_EXT_ATTR9, 'N_EXT_ATTR10', UDA.N_EXT_ATTR10,
735 'N_EXT_ATTR11', UDA.N_EXT_ATTR11, 'N_EXT_ATTR12', UDA.N_EXT_ATTR12, 'N_EXT_ATTR13', UDA.N_EXT_ATTR13, 'N_EXT_ATTR14', UDA.N_EXT_ATTR14, 'N_EXT_ATTR15', UDA.N_EXT_ATTR15,
736 'N_EXT_ATTR16', UDA.N_EXT_ATTR16, 'N_EXT_ATTR17', UDA.N_EXT_ATTR17, 'N_EXT_ATTR18', UDA.N_EXT_ATTR18, 'N_EXT_ATTR19', UDA.N_EXT_ATTR19, 'N_EXT_ATTR20', UDA.N_EXT_ATTR20 )
737 )/NVL(
738 (SELECT CONVERSION_RATE
739 FROM MTL_UOM_CONVERSIONS UOMLIST
740 WHERE UOMLIST.UOM_CODE =
741 DECODE (AGC.DATA_TYPE_CODE, 'N',
742 DECODE (AGC.DATABASE_COLUMN,'N_EXT_ATTR1', UDA.UOM_EXT_ATTR1, 'N_EXT_ATTR2', UDA.UOM_EXT_ATTR2, 'N_EXT_ATTR3', UDA.UOM_EXT_ATTR3, 'N_EXT_ATTR4', UDA.UOM_EXT_ATTR4, 'N_EXT_ATTR5', UDA.UOM_EXT_ATTR5,
743 'N_EXT_ATTR6', UDA.UOM_EXT_ATTR6, 'N_EXT_ATTR7', UDA.UOM_EXT_ATTR7, 'N_EXT_ATTR8', UDA.UOM_EXT_ATTR8, 'N_EXT_ATTR9', UDA.UOM_EXT_ATTR9, 'N_EXT_ATTR10', UDA.UOM_EXT_ATTR10,
744 'N_EXT_ATTR11', UDA.UOM_EXT_ATTR11, 'N_EXT_ATTR12', UDA.UOM_EXT_ATTR12, 'N_EXT_ATTR13', UDA.UOM_EXT_ATTR13, 'N_EXT_ATTR14', UDA.UOM_EXT_ATTR14, 'N_EXT_ATTR15', UDA.UOM_EXT_ATTR15,
745 'N_EXT_ATTR16', UDA.UOM_EXT_ATTR16, 'N_EXT_ATTR17', UDA.UOM_EXT_ATTR17, 'N_EXT_ATTR18', UDA.UOM_EXT_ATTR18, 'N_EXT_ATTR19', UDA.UOM_EXT_ATTR19, 'N_EXT_ATTR20', UDA.UOM_EXT_ATTR20)
746 )
747 AND INVENTORY_ITEM_ID = 0
748 ), 1)
749 ) AS ATTRIBUTE_NUMBER_VALUE,
750
751 DECODE (AGC.DATA_TYPE_CODE, 'N', DECODE (AGC.DATABASE_COLUMN, 'N_EXT_ATTR1', UDA.UOM_EXT_ATTR1, 'N_EXT_ATTR2', UDA.UOM_EXT_ATTR2, 'N_EXT_ATTR3', UDA.UOM_EXT_ATTR3, 'N_EXT_ATTR4', UDA.UOM_EXT_ATTR4, 'N_EXT_ATTR5', UDA.UOM_EXT_ATTR5,
752 'N_EXT_ATTR6', UDA.UOM_EXT_ATTR6, 'N_EXT_ATTR7', UDA.UOM_EXT_ATTR7, 'N_EXT_ATTR8', UDA.UOM_EXT_ATTR8, 'N_EXT_ATTR9', UDA.UOM_EXT_ATTR9, 'N_EXT_ATTR10', UDA.UOM_EXT_ATTR10,
753 'N_EXT_ATTR11', UDA.UOM_EXT_ATTR11, 'N_EXT_ATTR12', UDA.UOM_EXT_ATTR12, 'N_EXT_ATTR13', UDA.UOM_EXT_ATTR13, 'N_EXT_ATTR14', UDA.UOM_EXT_ATTR14,
754 'N_EXT_ATTR15', UDA.UOM_EXT_ATTR15, 'N_EXT_ATTR16', UDA.UOM_EXT_ATTR16, 'N_EXT_ATTR17', UDA.UOM_EXT_ATTR17, 'N_EXT_ATTR18', UDA.UOM_EXT_ATTR18,
755 'N_EXT_ATTR19', UDA.UOM_EXT_ATTR19, 'N_EXT_ATTR20', UDA.UOM_EXT_ATTR20 )
756 ) AS ATTRIBUTE_UOM_VALUE,
757
758 TO_CHAR(DECODE(AGC.DATA_TYPE_CODE, 'X', DECODE(AGC.DATABASE_COLUMN, 'D_EXT_ATTR1', UDA.D_EXT_ATTR1, 'D_EXT_ATTR2', UDA.D_EXT_ATTR2, 'D_EXT_ATTR3', UDA.D_EXT_ATTR3, 'D_EXT_ATTR4', UDA.D_EXT_ATTR4, 'D_EXT_ATTR5', UDA.D_EXT_ATTR5,
759 'D_EXT_ATTR6', UDA.D_EXT_ATTR6, 'D_EXT_ATTR7', UDA.D_EXT_ATTR7, 'D_EXT_ATTR8', UDA.D_EXT_ATTR8, 'D_EXT_ATTR9', UDA.D_EXT_ATTR9, 'D_EXT_ATTR10', UDA.D_EXT_ATTR10)
760 ), 'MM/DD/YYYY'
761 ) AS ATTRIBUTE_DATE_VALUE,
762
763 TO_CHAR(DECODE(AGC.DATA_TYPE_CODE, 'Y', DECODE(AGC.DATABASE_COLUMN, 'D_EXT_ATTR1', UDA.D_EXT_ATTR1, 'D_EXT_ATTR2', UDA.D_EXT_ATTR2, 'D_EXT_ATTR3', UDA.D_EXT_ATTR3, 'D_EXT_ATTR4', UDA.D_EXT_ATTR4, 'D_EXT_ATTR5', UDA.D_EXT_ATTR5,
764 'D_EXT_ATTR6', UDA.D_EXT_ATTR6, 'D_EXT_ATTR7', UDA.D_EXT_ATTR7, 'D_EXT_ATTR8', UDA.D_EXT_ATTR8, 'D_EXT_ATTR9', UDA.D_EXT_ATTR9, 'D_EXT_ATTR10', UDA.D_EXT_ATTR10)
765 ), 'MM/DD/YYYY HH24:MI:SS'
766 ) AS ATTRIBUTE_DATETIME_VALUE
767
768 FROM EGO_MTL_SY_ITEMS_EXT_B UDA , EGO_PUB_WS_FLAT_RECS flat ,
769 EGO_ATTRS_V AGC, EGO_ATTR_GROUPS_V AG
770
771 WHERE UDA.ATTR_GROUP_ID = AG.ATTR_GROUP_ID
772 AND AGC.APPLICATION_ID = AG.APPLICATION_ID
773 AND AGC.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
774 AND AGC.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
775 AND AGC.DATA_TYPE_CODE <> 'A'
776 AND AG.ATTR_GROUP_ID = To_Number(flat.REF1_VALUE)
777 AND UDA.EXTENSION_ID = To_Number(FLAT.REF2_VALUE)
778 AND FLAT.REF3_VALUE = To_Char(dataLevelId)
779 AND FLAT.session_id = sessionId
780 AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
781 AND Nvl(AGC.enabled_flag, 'Y') = 'Y' -- Bug 9542020
782 ) list, EGO_VALUE_SET_VALUES_V evsv
783 WHERE list.VALUE_SET_ID = evsv.VALUE_SET_ID (+)
784 AND
785 (
786 ( list.ATTRIBUTE_CHAR_VALUE IS NOT NULL
787 AND Nvl(evsv.FORMAT_TYPE, 'C') = 'C' -- Bug 9539538
788 AND Nvl(evsv.INTERNAL_NAME,list.ATTRIBUTE_CHAR_VALUE ) = list.ATTRIBUTE_CHAR_VALUE
789 )
790 OR
791 ( list.ATTRIBUTE_NUMBER_VALUE IS NOT NULL
792 AND Nvl(evsv.FORMAT_TYPE, 'N') = 'N' -- Bug 9539538
793 AND Nvl(evsv.INTERNAL_NAME,list.ATTRIBUTE_NUMBER_VALUE) = list.ATTRIBUTE_NUMBER_VALUE
794 )
795 OR
796 ( list.ATTRIBUTE_DATE_VALUE IS NOT NULL
797 AND Nvl(evsv.FORMAT_TYPE, 'X') = 'X' -- Bug 9539538
798 -- Bug 9615220 Should change parameters to DATEor DATETIME, then compare
799 AND Nvl(To_Date(evsv.INTERNAL_NAME, 'YYYY-MM-DD HH24:MI:SS'),To_Date(list.ATTRIBUTE_DATE_VALUE, 'MM/DD/YYYY')) = To_Date(list.ATTRIBUTE_DATE_VALUE, 'MM/DD/YYYY')
800 )
801 OR
802 ( list.ATTRIBUTE_DATETIME_VALUE IS NOT NULL
803 AND Nvl(evsv.FORMAT_TYPE, 'Y') = 'Y' -- Bug 9539538
804 -- Bug 9615220 Should change parameters to DATEor DATETIME, then compare
805 AND Nvl(To_Date(evsv.INTERNAL_NAME, 'YYYY-MM-DD HH24:MI:SS'),To_Date(list.ATTRIBUTE_DATETIME_VALUE, 'MM/DD/YYYY HH24:MI:SS')) = To_Date(list.ATTRIBUTE_DATETIME_VALUE, 'MM/DD/YYYY HH24:MI:SS')
806 )
807 );
808
809
810 -- Bug 8791039 : Below Query added to get the UDAs having NULL Values
811 /*
812 insert into EGO_PUB_WS_FLAT_RECS
813 (
814 SEQUENCE_ID,
815 SESSION_ID,
816 ODI_SESSION_ID,
817 ENTITY_TYPE,
818 PK1_VALUE,
819 PK2_VALUE,
820 PK3_VALUE,
821 REF1_VALUE,
822 REF2_VALUE,
823 REF3_VALUE ,
824 REF4_VALUE ,
825 REF5_VALUE ,
826 PARENT_SEQUENCE_ID,
827 VALUE,
828 CREATION_DATE
829 )
830 select
831 EGO_PUB_WS_FLAT_RECS_S.nextval,
832 sessionId,
833 odisessionId,
834 'UDA',
835 flat.PK1_VALUE ,
836 flat.PK2_VALUE ,
837 flat.PK3_VALUE ,
838 list.ATTRIBUTEGROUP_ID ,
839 list.EXTENSION_ID ,
840 dataLevelId ,
841 flat.REF4_VALUE ,
842 list.ATTRIBUTE_ID ,
843 flat.SEQUENCE_ID,
844 XMLForest(
845 list.ATTRIBUTE_NAME AS "ATTRIBUTE_NAME",
846 list.ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
847 list.ATTRIBUTE_NUMBER_VALUE AS "ATTRIBUTE_NUMBER_VALUE",
848 list.ATTRIBUTE_UOM_VALUE AS "ATTRIBUTE_UOM_VALUE",
849 list.ATTRIBUTE_DATE_VALUE AS "ATTRIBUTE_DATE_VALUE",
850 list.ATTRIBUTE_DATETIME_VALUE AS "ATTRIBUTE_DATETIME_VALUE" ,
851 NULL AS "DISPLAY_VALUE"
852 ).getclobval() ,
853 SYSDATE
854 FROM (
855 (SELECT
856 ATTRIBUTEGROUP_ID,
857 EXTENSION_ID,
858 ORGANIZATION_ID,
859 INVENTORY_ITEM_ID,
860 APPLICATION_ID,
861 DATA_LEVEL_ID,
862 ATTRIBUTE_ID,
863 ATTRIBUTE_NAME,
864 ATTRIBUTE_CHAR_VALUE,
865 ATTRIBUTE_NUMBER_VALUE,
866 ATTRIBUTE_UOM_VALUE,
867 ATTRIBUTE_DATE_VALUE,
868 ATTRIBUTE_DATETIME_VALUE,
869 null as TRANSLATED_CHAR_VALUE,
870 REVISION_ID
871 FROM ego_all_attr_base_v)
872 ) list , EGO_PUB_WS_FLAT_RECS flat ,
873 ego_attrs_v eav
874 WHERE
875 list.ATTRIBUTEGROUP_ID = FLAT.REF1_VALUE
876 AND list.EXTENSION_ID = FLAT.REF2_VALUE
877 AND list.INVENTORY_ITEM_ID = FLAT.PK1_VALUE
878 AND list.ORGANIZATION_ID = Decode(dataLevelId , 43101, flat.REF4_VALUE,
879 43103 , flat.REF4_VALUE,
880 43104 , flat.REF4_VALUE,
881 FLAT.PK2_VALUE )
882 AND nvl(list.REVISION_ID, -1) = Decode(nvl(list.REVISION_ID, -1), -1,-1,flat.PK3_VALUE)
883 AND list.DATA_LEVEL_ID = dataLevelId
884 AND FLAT.session_id = sessionId
885 AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
886 AND eav.attr_id = list.ATTRIBUTE_ID
887 AND Nvl(eav.enabled_flag, 'Y') = 'Y' -- Bug 9542020
888 AND list.ATTRIBUTE_CHAR_VALUE IS NULL
889 AND list.ATTRIBUTE_NUMBER_VALUE IS NULL
890 AND list.ATTRIBUTE_DATE_VALUE IS NULL
891 AND list.ATTRIBUTE_DATETIME_VALUE IS NULL;
892
893 */
894
895 -- Bug 8791039 : Below Query added to get the UDAs having NULL Values
896 insert into EGO_PUB_WS_FLAT_RECS
897 (
898 SEQUENCE_ID,
899 SESSION_ID,
900 ODI_SESSION_ID,
901 ENTITY_TYPE,
902 PK1_VALUE,
903 PK2_VALUE,
904 PK3_VALUE,
905 REF1_VALUE,
906 REF2_VALUE,
907 REF3_VALUE ,
908 REF4_VALUE ,
909 REF5_VALUE ,
910 PARENT_SEQUENCE_ID,
911 VALUE,
912 CREATION_DATE
913 )
914 SELECT
915 EGO_PUB_WS_FLAT_RECS_S.nextval,
916 sessionId,
917 odisessionId,
918 'UDA',
919 list.PK1_VALUE ,
920 list.PK2_VALUE ,
921 list.PK3_VALUE ,
922 list.ATTRIBUTEGROUP_ID ,
923 list.EXTENSION_ID ,
924 dataLevelId ,
925 list.REF4_VALUE ,
926 list.ATTRIBUTE_ID ,
927 list.SEQUENCE_ID,
928 XMLForest(
929 list.ATTR_NAME AS "ATTRIBUTE_NAME",
930 list.ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
931 list.ATTRIBUTE_NUMBER_VALUE AS "ATTRIBUTE_NUMBER_VALUE",
932 list.ATTRIBUTE_UOM_VALUE AS "ATTRIBUTE_UOM_VALUE",
933 list.ATTRIBUTE_DATE_VALUE AS "ATTRIBUTE_DATE_VALUE",
934 list.ATTRIBUTE_DATETIME_VALUE AS "ATTRIBUTE_DATETIME_VALUE" ,
935 NULL AS "DISPLAY_VALUE"
936 ).getclobval() ,
937 SYSDATE
938 FROM
939 ( select
940 flat.PK1_VALUE ,
941 flat.PK2_VALUE ,
942 flat.PK3_VALUE ,
943 UDA.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" ,
944 UDA.EXTENSION_ID ,
945 flat.REF4_VALUE ,
946 AGC.attr_id AS "ATTRIBUTE_ID",
947 flat.SEQUENCE_ID,
948 AGC.VALUE_SET_ID,
949 AGC.ATTR_NAME,
950 DECODE (AGC.DATA_TYPE_CODE, 'C', DECODE (AGC.DATABASE_COLUMN, 'C_EXT_ATTR1', UDA.C_EXT_ATTR1, 'C_EXT_ATTR2', UDA.C_EXT_ATTR2, 'C_EXT_ATTR3', UDA.C_EXT_ATTR3, 'C_EXT_ATTR4', UDA.C_EXT_ATTR4, 'C_EXT_ATTR5', UDA.C_EXT_ATTR5,
951 'C_EXT_ATTR6', UDA.C_EXT_ATTR6, 'C_EXT_ATTR7', UDA.C_EXT_ATTR7, 'C_EXT_ATTR8', UDA.C_EXT_ATTR8, 'C_EXT_ATTR9', UDA.C_EXT_ATTR9, 'C_EXT_ATTR10', UDA.C_EXT_ATTR10,
952 'C_EXT_ATTR11', UDA.C_EXT_ATTR11, 'C_EXT_ATTR12', UDA.C_EXT_ATTR12, 'C_EXT_ATTR13', UDA.C_EXT_ATTR13, 'C_EXT_ATTR14', UDA.C_EXT_ATTR14, 'C_EXT_ATTR15', UDA.C_EXT_ATTR15,
953 'C_EXT_ATTR16', UDA.C_EXT_ATTR16, 'C_EXT_ATTR17', UDA.C_EXT_ATTR17, 'C_EXT_ATTR18', UDA.C_EXT_ATTR18, 'C_EXT_ATTR19', UDA.C_EXT_ATTR19, 'C_EXT_ATTR20', UDA.C_EXT_ATTR20,
954 'C_EXT_ATTR21', UDA.C_EXT_ATTR21, 'C_EXT_ATTR22', UDA.C_EXT_ATTR22, 'C_EXT_ATTR23', UDA.C_EXT_ATTR23, 'C_EXT_ATTR24', UDA.C_EXT_ATTR24, 'C_EXT_ATTR25', UDA.C_EXT_ATTR25,
955 'C_EXT_ATTR26', UDA.C_EXT_ATTR26, 'C_EXT_ATTR27', UDA.C_EXT_ATTR27, 'C_EXT_ATTR28', UDA.C_EXT_ATTR28, 'C_EXT_ATTR29', UDA.C_EXT_ATTR29, 'C_EXT_ATTR30', UDA.C_EXT_ATTR30,
956 'C_EXT_ATTR31', UDA.C_EXT_ATTR31, 'C_EXT_ATTR32', UDA.C_EXT_ATTR32, 'C_EXT_ATTR33', UDA.C_EXT_ATTR33, 'C_EXT_ATTR34', UDA.C_EXT_ATTR34, 'C_EXT_ATTR35', UDA.C_EXT_ATTR35,
957 'C_EXT_ATTR36', UDA.C_EXT_ATTR36, 'C_EXT_ATTR37', UDA.C_EXT_ATTR37, 'C_EXT_ATTR38', UDA.C_EXT_ATTR38, 'C_EXT_ATTR39', UDA.C_EXT_ATTR39, 'C_EXT_ATTR40', UDA.C_EXT_ATTR40 )
958 ) AS ATTRIBUTE_CHAR_VALUE,
959
960 (DECODE (AGC.DATA_TYPE_CODE, 'N', DECODE (AGC.DATABASE_COLUMN, 'N_EXT_ATTR1', UDA.N_EXT_ATTR1, 'N_EXT_ATTR2', UDA.N_EXT_ATTR2, 'N_EXT_ATTR3', UDA.N_EXT_ATTR3, 'N_EXT_ATTR4', UDA.N_EXT_ATTR4, 'N_EXT_ATTR5', UDA.N_EXT_ATTR5,
961 'N_EXT_ATTR6', UDA.N_EXT_ATTR6, 'N_EXT_ATTR7', UDA.N_EXT_ATTR7, 'N_EXT_ATTR8', UDA.N_EXT_ATTR8, 'N_EXT_ATTR9', UDA.N_EXT_ATTR9, 'N_EXT_ATTR10', UDA.N_EXT_ATTR10,
962 'N_EXT_ATTR11', UDA.N_EXT_ATTR11, 'N_EXT_ATTR12', UDA.N_EXT_ATTR12, 'N_EXT_ATTR13', UDA.N_EXT_ATTR13, 'N_EXT_ATTR14', UDA.N_EXT_ATTR14, 'N_EXT_ATTR15', UDA.N_EXT_ATTR15,
963 'N_EXT_ATTR16', UDA.N_EXT_ATTR16, 'N_EXT_ATTR17', UDA.N_EXT_ATTR17, 'N_EXT_ATTR18', UDA.N_EXT_ATTR18, 'N_EXT_ATTR19', UDA.N_EXT_ATTR19, 'N_EXT_ATTR20', UDA.N_EXT_ATTR20 )
964 )/NVL(
965 (SELECT CONVERSION_RATE
966 FROM MTL_UOM_CONVERSIONS UOMLIST
967 WHERE UOMLIST.UOM_CODE =
968 DECODE (AGC.DATA_TYPE_CODE, 'N',
969 DECODE (AGC.DATABASE_COLUMN,'N_EXT_ATTR1', UDA.UOM_EXT_ATTR1, 'N_EXT_ATTR2', UDA.UOM_EXT_ATTR2, 'N_EXT_ATTR3', UDA.UOM_EXT_ATTR3, 'N_EXT_ATTR4', UDA.UOM_EXT_ATTR4, 'N_EXT_ATTR5', UDA.UOM_EXT_ATTR5,
970 'N_EXT_ATTR6', UDA.UOM_EXT_ATTR6, 'N_EXT_ATTR7', UDA.UOM_EXT_ATTR7, 'N_EXT_ATTR8', UDA.UOM_EXT_ATTR8, 'N_EXT_ATTR9', UDA.UOM_EXT_ATTR9, 'N_EXT_ATTR10', UDA.UOM_EXT_ATTR10,
971 'N_EXT_ATTR11', UDA.UOM_EXT_ATTR11, 'N_EXT_ATTR12', UDA.UOM_EXT_ATTR12, 'N_EXT_ATTR13', UDA.UOM_EXT_ATTR13, 'N_EXT_ATTR14', UDA.UOM_EXT_ATTR14, 'N_EXT_ATTR15', UDA.UOM_EXT_ATTR15,
972 'N_EXT_ATTR16', UDA.UOM_EXT_ATTR16, 'N_EXT_ATTR17', UDA.UOM_EXT_ATTR17, 'N_EXT_ATTR18', UDA.UOM_EXT_ATTR18, 'N_EXT_ATTR19', UDA.UOM_EXT_ATTR19, 'N_EXT_ATTR20', UDA.UOM_EXT_ATTR20)
973 )
974 AND INVENTORY_ITEM_ID = 0
975 ), 1)
976 ) AS ATTRIBUTE_NUMBER_VALUE,
977
978 DECODE (AGC.DATA_TYPE_CODE, 'N', DECODE (AGC.DATABASE_COLUMN, 'N_EXT_ATTR1', UDA.UOM_EXT_ATTR1, 'N_EXT_ATTR2', UDA.UOM_EXT_ATTR2, 'N_EXT_ATTR3', UDA.UOM_EXT_ATTR3, 'N_EXT_ATTR4', UDA.UOM_EXT_ATTR4, 'N_EXT_ATTR5', UDA.UOM_EXT_ATTR5,
979 'N_EXT_ATTR6', UDA.UOM_EXT_ATTR6, 'N_EXT_ATTR7', UDA.UOM_EXT_ATTR7, 'N_EXT_ATTR8', UDA.UOM_EXT_ATTR8, 'N_EXT_ATTR9', UDA.UOM_EXT_ATTR9, 'N_EXT_ATTR10', UDA.UOM_EXT_ATTR10,
980 'N_EXT_ATTR11', UDA.UOM_EXT_ATTR11, 'N_EXT_ATTR12', UDA.UOM_EXT_ATTR12, 'N_EXT_ATTR13', UDA.UOM_EXT_ATTR13, 'N_EXT_ATTR14', UDA.UOM_EXT_ATTR14,
981 'N_EXT_ATTR15', UDA.UOM_EXT_ATTR15, 'N_EXT_ATTR16', UDA.UOM_EXT_ATTR16, 'N_EXT_ATTR17', UDA.UOM_EXT_ATTR17, 'N_EXT_ATTR18', UDA.UOM_EXT_ATTR18,
982 'N_EXT_ATTR19', UDA.UOM_EXT_ATTR19, 'N_EXT_ATTR20', UDA.UOM_EXT_ATTR20 )
983 ) AS ATTRIBUTE_UOM_VALUE,
984
985 TO_CHAR(DECODE(AGC.DATA_TYPE_CODE, 'X', DECODE(AGC.DATABASE_COLUMN, 'D_EXT_ATTR1', UDA.D_EXT_ATTR1, 'D_EXT_ATTR2', UDA.D_EXT_ATTR2, 'D_EXT_ATTR3', UDA.D_EXT_ATTR3, 'D_EXT_ATTR4', UDA.D_EXT_ATTR4, 'D_EXT_ATTR5', UDA.D_EXT_ATTR5,
986 'D_EXT_ATTR6', UDA.D_EXT_ATTR6, 'D_EXT_ATTR7', UDA.D_EXT_ATTR7, 'D_EXT_ATTR8', UDA.D_EXT_ATTR8, 'D_EXT_ATTR9', UDA.D_EXT_ATTR9, 'D_EXT_ATTR10', UDA.D_EXT_ATTR10)
987 ), 'MM/DD/YYYY'
988 ) AS ATTRIBUTE_DATE_VALUE,
989
990 TO_CHAR(DECODE(AGC.DATA_TYPE_CODE, 'Y', DECODE(AGC.DATABASE_COLUMN, 'D_EXT_ATTR1', UDA.D_EXT_ATTR1, 'D_EXT_ATTR2', UDA.D_EXT_ATTR2, 'D_EXT_ATTR3', UDA.D_EXT_ATTR3, 'D_EXT_ATTR4', UDA.D_EXT_ATTR4, 'D_EXT_ATTR5', UDA.D_EXT_ATTR5,
991 'D_EXT_ATTR6', UDA.D_EXT_ATTR6, 'D_EXT_ATTR7', UDA.D_EXT_ATTR7, 'D_EXT_ATTR8', UDA.D_EXT_ATTR8, 'D_EXT_ATTR9', UDA.D_EXT_ATTR9, 'D_EXT_ATTR10', UDA.D_EXT_ATTR10)
992 ), 'MM/DD/YYYY HH24:MI:SS'
993 ) AS ATTRIBUTE_DATETIME_VALUE
994
995 FROM EGO_MTL_SY_ITEMS_EXT_B UDA , EGO_PUB_WS_FLAT_RECS flat ,
996 EGO_ATTRS_V AGC, EGO_ATTR_GROUPS_V AG
997
998 WHERE UDA.ATTR_GROUP_ID = AG.ATTR_GROUP_ID
999 AND AGC.APPLICATION_ID = AG.APPLICATION_ID
1000 AND AGC.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
1001 AND AGC.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
1002 AND AGC.DATA_TYPE_CODE <> 'A'
1003 AND AG.ATTR_GROUP_ID = To_Number(flat.REF1_VALUE)
1004 AND UDA.EXTENSION_ID = To_Number(FLAT.REF2_VALUE)
1005 AND FLAT.REF3_VALUE = To_Char(dataLevelId)
1006 AND FLAT.session_id = sessionId
1007 AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
1008 AND Nvl(AGC.enabled_flag, 'Y') = 'Y' -- Bug 9542020
1009 ) list
1010 WHERE list.ATTRIBUTE_CHAR_VALUE IS NULL
1011 AND list.ATTRIBUTE_NUMBER_VALUE IS NULL
1012 AND list.ATTRIBUTE_DATE_VALUE IS NULL
1013 AND list.ATTRIBUTE_DATETIME_VALUE IS NULL;
1014
1015 /* Insert the translated char value if exists, for all the AGs that were inserted into the flat table for the corresponding level */
1016 /*
1017 insert into EGO_PUB_WS_FLAT_RECS
1018 (
1019 SEQUENCE_ID,
1020 SESSION_ID,
1021 ODI_SESSION_ID,
1022 ENTITY_TYPE,
1023 PK1_VALUE,
1024 PK2_VALUE,
1025 PK3_VALUE,
1026 REF1_VALUE,
1027 REF2_VALUE,
1028 REF3_VALUE,
1029 REF4_VALUE,
1030 REF5_VALUE,
1031 REF6_VALUE,
1032 PARENT_SEQUENCE_ID,
1033 VALUE,
1034 CREATION_DATE
1035 )
1036 select
1037 EGO_PUB_WS_FLAT_RECS_S.nextval,
1038 sessionId,
1039 odisessionId,
1040 'UDA',
1041 flat.PK1_VALUE ,
1042 flat.PK2_VALUE ,
1043 flat.PK3_VALUE ,
1044 list.ATTRIBUTEGROUP_ID ,
1045 list.EXTENSION_ID ,
1046 dataLevelId ,
1047 flat.REF4_VALUE ,
1048 list.ATTRIBUTE_ID ,
1049 'TRANSLATED_CHAR_VALUE' ,
1050 flat.SEQUENCE_ID,
1051 XMLForest(
1052 list.ATTRIBUTE_NAME AS "ATTRIBUTE_NAME",
1053 list.TRANSLATED_CHAR_VALUE AS TRANSLATED_CHAR_VALUE
1054 ).getclobval() ,
1055 SYSDATE
1056 FROM (
1057 (SELECT
1058 ATTRIBUTEGROUP_ID,
1059 EXTENSION_ID,
1060 ORGANIZATION_ID,
1061 INVENTORY_ITEM_ID,
1062 APPLICATION_ID,
1063 DATA_LEVEL_ID,
1064 ATTRIBUTE_ID,
1065 ATTRIBUTE_NAME,
1066 ATTRIBUTE_TRANSLATABLE_VALUE as TRANSLATED_CHAR_VALUE,
1067 REVISION_ID
1068 FROM ego_all_attr_lang_v
1069 where language = userenv('LANG'))
1070 ) list , EGO_PUB_WS_FLAT_RECS flat
1071 , ego_attrs_v eav -- Bug 9542020
1072 WHERE
1073 list.ATTRIBUTEGROUP_ID = FLAT.REF1_VALUE
1074 AND list.EXTENSION_ID = FLAT.REF2_VALUE
1075 AND list.INVENTORY_ITEM_ID = FLAT.PK1_VALUE
1076 AND list.ORGANIZATION_ID = Decode(dataLevelId , 43101, flat.REF4_VALUE,
1077 43103 , flat.REF4_VALUE,
1078 43104 , flat.REF4_VALUE,
1079 FLAT.PK2_VALUE )
1080 AND nvl(list.REVISION_ID, -1) = Decode(nvl(list.REVISION_ID, -1), -1,-1,flat.PK3_VALUE)
1081 AND list.DATA_LEVEL_ID = dataLevelId
1082 -- Bug 9542020 - Start
1083 AND eav.attr_id = list.ATTRIBUTE_ID
1084 AND Nvl(eav.enabled_flag, 'Y') = 'Y'
1085 -- Bug 9542020 - End
1086 AND FLAT.session_id = sessionId
1087 AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP';
1088 */
1089
1090 /* Insert the translated char value if exists, for all the AGs that were inserted into the flat table for the corresponding level */
1091 insert into EGO_PUB_WS_FLAT_RECS
1092 (
1093 SEQUENCE_ID,
1094 SESSION_ID,
1095 ODI_SESSION_ID,
1096 ENTITY_TYPE,
1097 PK1_VALUE,
1098 PK2_VALUE,
1099 PK3_VALUE,
1100 REF1_VALUE,
1101 REF2_VALUE,
1102 REF3_VALUE,
1103 REF4_VALUE,
1104 REF5_VALUE,
1105 REF6_VALUE,
1106 PARENT_SEQUENCE_ID,
1107 VALUE,
1108 CREATION_DATE
1109 )
1110 select
1111 EGO_PUB_WS_FLAT_RECS_S.nextval,
1112 sessionId,
1113 odisessionId,
1114 'UDA',
1115 list.PK1_VALUE ,
1116 list.PK2_VALUE ,
1117 list.PK3_VALUE ,
1118 list.ATTRIBUTEGROUP_ID ,
1119 list.EXTENSION_ID ,
1120 dataLevelId ,
1121 list.REF4_VALUE ,
1122 list.ATTRIBUTE_ID ,
1123 'TRANSLATED_CHAR_VALUE' ,
1124 list.SEQUENCE_ID,
1125 XMLForest(
1126 list.ATTR_NAME AS "ATTRIBUTE_NAME",
1127 list.ATTRIBUTE_TRANSLATABLE_VALUE AS TRANSLATED_CHAR_VALUE
1128 ).getclobval() ,
1129 SYSDATE
1130 FROM
1131 (
1132 select
1133 flat.PK1_VALUE ,
1134 flat.PK2_VALUE ,
1135 flat.PK3_VALUE ,
1136 UDA.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" ,
1137 UDA.EXTENSION_ID ,
1138 flat.REF4_VALUE ,
1139 AGC.attr_id AS "ATTRIBUTE_ID",
1140 flat.SEQUENCE_ID,
1141 AGC.VALUE_SET_ID,
1142 AGC.ATTR_NAME,
1143 DECODE (AGC.DATA_TYPE_CODE, 'A', DECODE (AGC.DATABASE_COLUMN, 'TL_EXT_ATTR1', UDA.TL_EXT_ATTR1, 'TL_EXT_ATTR2', UDA.TL_EXT_ATTR2, 'TL_EXT_ATTR3', UDA.TL_EXT_ATTR3, 'TL_EXT_ATTR4', UDA.TL_EXT_ATTR4, 'TL_EXT_ATTR5', UDA.TL_EXT_ATTR5,
1144 'TL_EXT_ATTR6', UDA.TL_EXT_ATTR6, 'TL_EXT_ATTR7', UDA.TL_EXT_ATTR7, 'TL_EXT_ATTR8', UDA.TL_EXT_ATTR8, 'TL_EXT_ATTR9', UDA.TL_EXT_ATTR9, 'TL_EXT_ATTR10', UDA.TL_EXT_ATTR10,
1145 'TL_EXT_ATTR11', UDA.TL_EXT_ATTR11, 'TL_EXT_ATTR12', UDA.TL_EXT_ATTR12, 'TL_EXT_ATTR13', UDA.TL_EXT_ATTR13, 'TL_EXT_ATTR14', UDA.TL_EXT_ATTR14,
1146 'TL_EXT_ATTR15', UDA.TL_EXT_ATTR15, 'TL_EXT_ATTR16', UDA.TL_EXT_ATTR16, 'TL_EXT_ATTR17', UDA.TL_EXT_ATTR17, 'TL_EXT_ATTR18', UDA.TL_EXT_ATTR18,
1147 'TL_EXT_ATTR19', UDA.TL_EXT_ATTR19, 'TL_EXT_ATTR20', UDA.TL_EXT_ATTR20, 'TL_EXT_ATTR21', UDA.TL_EXT_ATTR21, 'TL_EXT_ATTR22', UDA.TL_EXT_ATTR22,
1148 'TL_EXT_ATTR23', UDA.TL_EXT_ATTR23, 'TL_EXT_ATTR24', UDA.TL_EXT_ATTR24, 'TL_EXT_ATTR25', UDA.TL_EXT_ATTR25, 'TL_EXT_ATTR26', UDA.TL_EXT_ATTR26,
1149 'TL_EXT_ATTR27', UDA.TL_EXT_ATTR27, 'TL_EXT_ATTR28', UDA.TL_EXT_ATTR28, 'TL_EXT_ATTR29', UDA.TL_EXT_ATTR29, 'TL_EXT_ATTR30', UDA.TL_EXT_ATTR30,
1150 'TL_EXT_ATTR31', UDA.TL_EXT_ATTR31, 'TL_EXT_ATTR32', UDA.TL_EXT_ATTR32, 'TL_EXT_ATTR33', UDA.TL_EXT_ATTR33, 'TL_EXT_ATTR34', UDA.TL_EXT_ATTR34,
1151 'TL_EXT_ATTR35', UDA.TL_EXT_ATTR35, 'TL_EXT_ATTR36', UDA.TL_EXT_ATTR36, 'TL_EXT_ATTR37', UDA.TL_EXT_ATTR37, 'TL_EXT_ATTR38', UDA.TL_EXT_ATTR38,
1152 'TL_EXT_ATTR39', UDA.TL_EXT_ATTR39, 'TL_EXT_ATTR40', UDA.TL_EXT_ATTR40 )
1153 ) AS ATTRIBUTE_TRANSLATABLE_VALUE
1154 FROM EGO_MTL_SY_ITEMS_EXT_TL UDA , EGO_PUB_WS_FLAT_RECS flat ,
1155 EGO_ATTRS_V AGC, EGO_ATTR_GROUPS_V AG
1156
1157 WHERE UDA.ATTR_GROUP_ID = AG.ATTR_GROUP_ID
1158 AND AGC.APPLICATION_ID = AG.APPLICATION_ID
1159 AND AGC.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
1160 AND AGC.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
1161 AND AGC.DATA_TYPE_CODE = 'A'
1162 AND UDA.language = userenv('LANG')
1163 AND AG.ATTR_GROUP_ID = To_Number(flat.REF1_VALUE)
1164 AND UDA.EXTENSION_ID = To_Number(FLAT.REF2_VALUE)
1165 AND FLAT.REF3_VALUE = To_Char(dataLevelId)
1166 AND FLAT.session_id = sessionId
1167 AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
1168 AND Nvl(AGC.enabled_flag, 'Y') = 'Y' -- Bug 9542020
1169 ) list;
1170
1171 /* Insert Translatable values ( for Trabslated char value )*/
1172 /*
1173 insert into EGO_PUB_WS_FLAT_RECS
1174 (
1175 SEQUENCE_ID,
1176 SESSION_ID,
1177 ODI_SESSION_ID,
1178 ENTITY_TYPE,
1179 PK1_VALUE,
1180 PK2_VALUE,
1181 PK3_VALUE,
1182 REF1_VALUE,
1183 PARENT_SEQUENCE_ID,
1184 VALUE,
1185 CREATION_DATE
1186 )
1187 select
1188 EGO_PUB_WS_FLAT_RECS_S.nextval,
1189 sessionId,
1190 odisessionId,
1191 'UDA_TRANSLATIONS',
1192 flat.PK1_VALUE ,
1193 flat.PK2_VALUE ,
1194 flat.PK3_VALUE ,
1195 dataLevelId ,
1196 flat.SEQUENCE_ID,
1197 XMLForest(
1198 CONFIG.CHAR_VALUE AS "Language" ,
1199 list.TRANSLATED_CHAR_VALUE AS "Value"
1200 ).getclobval() ,
1201 SYSDATE
1202 FROM (SELECT
1203 ATTRIBUTEGROUP_ID,
1204 EXTENSION_ID,
1205 ORGANIZATION_ID,
1206 INVENTORY_ITEM_ID,
1207 APPLICATION_ID,
1208 DATA_LEVEL_ID,
1209 ATTRIBUTE_ID,
1210 ATTRIBUTE_NAME,
1211 ATTRIBUTE_TRANSLATABLE_VALUE as TRANSLATED_CHAR_VALUE,
1212 REVISION_ID,
1213 LANGUAGE
1214 FROM ego_all_attr_lang_v ealv
1215 ) list , EGO_PUB_WS_FLAT_RECS flat , EGO_PUB_WS_CONFIG CONFIG
1216 WHERE list.ATTRIBUTEGROUP_ID = FLAT.REF1_VALUE
1217 AND list.EXTENSION_ID = FLAT.REF2_VALUE
1218 AND list.INVENTORY_ITEM_ID = FLAT.PK1_VALUE
1219 AND list.ORGANIZATION_ID = Decode(dataLevelId , 43101, flat.REF4_VALUE,
1220 43103 , flat.REF4_VALUE,
1221 43104 , flat.REF4_VALUE,
1222 FLAT.PK2_VALUE )
1223 AND nvl(list.REVISION_ID, -1) = Decode(nvl(list.REVISION_ID, -1), -1,-1,flat.PK3_VALUE)
1224 AND To_Char(list.ATTRIBUTE_ID) = flat.REF5_VALUE
1225 AND To_Char(list.DATA_LEVEL_ID) = flat.REF3_VALUE
1226 AND list.DATA_LEVEL_ID = dataLevelId
1227 AND To_Char(list.INVENTORY_ITEM_ID) = flat.PK1_VALUE
1228 AND FLAT.session_id = sessionId
1229 AND CONFIG.SESSION_ID = FLAT.SESSION_ID
1230 AND CONFIG.CHAR_VALUE = list.LANGUAGE
1231 AND FLAT.ENTITY_TYPE = 'UDA'
1232 AND CONFIG.PARAMETER_NAME = 'LANGUAGE_CODE'
1233 AND FLAT.REF6_VALUE = 'TRANSLATED_CHAR_VALUE';
1234
1235 */
1236
1237 IF (SQL%ROWCOUNT > 0) Then
1238 /* Insert Translatable values ( for Trabslated char value )*/
1239 insert into EGO_PUB_WS_FLAT_RECS
1240 (
1241 SEQUENCE_ID,
1242 SESSION_ID,
1243 ODI_SESSION_ID,
1244 ENTITY_TYPE,
1245 PK1_VALUE,
1246 PK2_VALUE,
1247 PK3_VALUE,
1248 REF1_VALUE,
1249 PARENT_SEQUENCE_ID,
1250 VALUE,
1251 CREATION_DATE
1252 )
1253 select
1254 EGO_PUB_WS_FLAT_RECS_S.nextval,
1255 sessionId,
1256 odisessionId,
1257 'UDA_TRANSLATIONS',
1258 list.PK1_VALUE ,
1259 list.PK2_VALUE ,
1260 list.PK3_VALUE ,
1261 dataLevelId ,
1262 list.SEQUENCE_ID,
1263 XMLForest(
1264 list.CHAR_VALUE AS "Language" ,
1265 list.ATTRIBUTE_TRANSLATABLE_VALUE AS "Value"
1266 ).getclobval() ,
1267 SYSDATE
1268 FROM
1269 (
1270 select
1271 flat.PK1_VALUE ,
1272 flat.PK2_VALUE ,
1273 flat.PK3_VALUE ,
1274 flat.SEQUENCE_ID,
1275 CONFIG.CHAR_VALUE,
1276 DECODE (AGC.DATA_TYPE_CODE, 'A', DECODE (AGC.DATABASE_COLUMN, 'TL_EXT_ATTR1', UDA.TL_EXT_ATTR1, 'TL_EXT_ATTR2', UDA.TL_EXT_ATTR2, 'TL_EXT_ATTR3', UDA.TL_EXT_ATTR3, 'TL_EXT_ATTR4', UDA.TL_EXT_ATTR4, 'TL_EXT_ATTR5', UDA.TL_EXT_ATTR5,
1277 'TL_EXT_ATTR6', UDA.TL_EXT_ATTR6, 'TL_EXT_ATTR7', UDA.TL_EXT_ATTR7, 'TL_EXT_ATTR8', UDA.TL_EXT_ATTR8, 'TL_EXT_ATTR9', UDA.TL_EXT_ATTR9, 'TL_EXT_ATTR10', UDA.TL_EXT_ATTR10,
1278 'TL_EXT_ATTR11', UDA.TL_EXT_ATTR11, 'TL_EXT_ATTR12', UDA.TL_EXT_ATTR12, 'TL_EXT_ATTR13', UDA.TL_EXT_ATTR13, 'TL_EXT_ATTR14', UDA.TL_EXT_ATTR14,
1279 'TL_EXT_ATTR15', UDA.TL_EXT_ATTR15, 'TL_EXT_ATTR16', UDA.TL_EXT_ATTR16, 'TL_EXT_ATTR17', UDA.TL_EXT_ATTR17, 'TL_EXT_ATTR18', UDA.TL_EXT_ATTR18,
1280 'TL_EXT_ATTR19', UDA.TL_EXT_ATTR19, 'TL_EXT_ATTR20', UDA.TL_EXT_ATTR20, 'TL_EXT_ATTR21', UDA.TL_EXT_ATTR21, 'TL_EXT_ATTR22', UDA.TL_EXT_ATTR22,
1281 'TL_EXT_ATTR23', UDA.TL_EXT_ATTR23, 'TL_EXT_ATTR24', UDA.TL_EXT_ATTR24, 'TL_EXT_ATTR25', UDA.TL_EXT_ATTR25, 'TL_EXT_ATTR26', UDA.TL_EXT_ATTR26,
1282 'TL_EXT_ATTR27', UDA.TL_EXT_ATTR27, 'TL_EXT_ATTR28', UDA.TL_EXT_ATTR28, 'TL_EXT_ATTR29', UDA.TL_EXT_ATTR29, 'TL_EXT_ATTR30', UDA.TL_EXT_ATTR30,
1283 'TL_EXT_ATTR31', UDA.TL_EXT_ATTR31, 'TL_EXT_ATTR32', UDA.TL_EXT_ATTR32, 'TL_EXT_ATTR33', UDA.TL_EXT_ATTR33, 'TL_EXT_ATTR34', UDA.TL_EXT_ATTR34,
1284 'TL_EXT_ATTR35', UDA.TL_EXT_ATTR35, 'TL_EXT_ATTR36', UDA.TL_EXT_ATTR36, 'TL_EXT_ATTR37', UDA.TL_EXT_ATTR37, 'TL_EXT_ATTR38', UDA.TL_EXT_ATTR38,
1285 'TL_EXT_ATTR39', UDA.TL_EXT_ATTR39, 'TL_EXT_ATTR40', UDA.TL_EXT_ATTR40 )
1286 ) AS ATTRIBUTE_TRANSLATABLE_VALUE
1287
1288 FROM EGO_MTL_SY_ITEMS_EXT_TL UDA , EGO_PUB_WS_FLAT_RECS flat ,
1289 EGO_ATTRS_V AGC, EGO_ATTR_GROUPS_V AG , EGO_PUB_WS_CONFIG CONFIG
1290
1291 WHERE UDA.ATTR_GROUP_ID = AG.ATTR_GROUP_ID
1292 AND AGC.APPLICATION_ID = AG.APPLICATION_ID
1293 AND AGC.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
1294 AND AGC.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
1295 AND AGC.DATA_TYPE_CODE = 'A'
1296 AND AG.ATTR_GROUP_ID = To_Number(flat.REF1_VALUE)
1297 AND UDA.EXTENSION_ID = To_Number(FLAT.REF2_VALUE)
1298 AND To_Char(AGC.ATTR_ID) = flat.REF5_VALUE
1299 AND To_Char(dataLevelId) = flat.REF3_VALUE
1300 AND FLAT.session_id = sessionId
1301 AND FLAT.ENTITY_TYPE = 'UDA'
1302 AND FLAT.REF6_VALUE = 'TRANSLATED_CHAR_VALUE'
1303 AND CONFIG.SESSION_ID = FLAT.SESSION_ID
1304 AND CONFIG.CHAR_VALUE = UDA.LANGUAGE
1305 AND CONFIG.PARAMETER_NAME = 'LANGUAGE_CODE'
1306 ) list;
1307 END IF;
1308 END IF; -- End of SQL%ROWCOUNT > 0
1309 -- Performance Changes : End
1310 COMMIT;
1311 END IF; -- if for v_publish_udas
1312 END;
1313
1314
1315 -- This Procedure inserts the GTIN XRef Deetails associated to an Item into the table EGO_PUB_WS_FLAT_RECS
1316 PROCEDURE POPULATE_GTIN_DETAILS
1317 (sessionId IN NUMBER,
1318 odisessionId IN NUMBER
1319 )
1320 AS
1321
1322 BEGIN
1323
1324 insert into EGO_PUB_WS_FLAT_RECS
1325 (
1326 SEQUENCE_ID,
1327 SESSION_ID,
1328 ODI_SESSION_ID,
1329 ENTITY_TYPE,
1330 PK1_VALUE,
1331 PK2_VALUE,
1332 PK3_VALUE,
1333 REF1_VALUE,
1334 PARENT_SEQUENCE_ID,
1335 VALUE,
1336 CREATION_DATE
1337 )
1338 SELECT
1339 EGO_PUB_WS_FLAT_RECS_S.nextval,
1340 sessionId,
1341 odiSessionId,
1342 'GTIN_CROSS_REFERENCE',
1343 flat.PK1_VALUE,
1344 flat.PK2_VALUE,
1345 flat.PK3_VALUE,
1346 CROSS_REFERENCE_ID,
1347 flat.SEQUENCE_ID,
1348 XMLForest(
1349 EgoGtinEO.CROSS_REFERENCE AS "CrossReference" ,
1350 A.ITEM_NUMBER AS "PackItemNumber" ,
1351 EgoGtinEO.DESCRIPTION AS "GTINDescription" ,
1352 EgoGtinEO.UOM_CODE AS "UnitOfMeasure" ,
1353 (SELECT MIR.REVISION
1354 FROM MTL_ITEM_REVISIONS_B MIR
1355 WHERE EgoGtinEO.REVISION_ID = MIR.REVISION_ID
1356 ) AS "Revision" ,
1357 EgoGtinEO.CROSS_REFERENCE_ID AS "CrossReferenceId" ,
1358 EgoGtinEO.EPC_GTIN_SERIAL AS "EpcGtinSerial"
1359 ).getclobval() ,
1360 SYSDATE
1361 FROM
1362 MTL_CROSS_REFERENCES EgoGtinEO ,
1363 MTL_SYSTEM_ITEMS_B MSIB ,
1364 MTL_PARAMETERS MP ,
1365 ( SELECT DISTINCT MCR1.CROSS_REFERENCE ,
1366 MSIK.INVENTORY_ITEM_ID ,
1367 MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER ,
1368 MSIK.ORGANIZATION_ID MASTER_ORGANIZATION_ID,
1369 MSIK.PRIMARY_UOM_CODE
1370 FROM MTL_CROSS_REFERENCES_B MCR1,
1371 MTL_SYSTEM_ITEMS_KFV MSIK ,
1372 MTL_PARAMETERS MP1
1373 WHERE MCR1.CROSS_REFERENCE_TYPE = 'GTIN'
1374 AND MCR1.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
1375 AND MSIK.ORGANIZATION_ID = MP1.MASTER_ORGANIZATION_ID
1376 AND MSIK.PRIMARY_UOM_CODE = trim(MCR1.UOM_CODE)
1377 ) A,
1378 EGO_PUB_WS_FLAT_RECS flat
1379 WHERE EgoGtinEO.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
1380 AND MP.ORGANIZATION_ID = flat.PK2_VALUE
1381 AND MSIB.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
1382 AND EgoGtinEO.CROSS_REFERENCE_TYPE = 'GTIN'
1383 AND MP.MASTER_ORGANIZATION_ID = flat.PK2_VALUE
1384 AND EgoGtinEO.CROSS_REFERENCE = A.CROSS_REFERENCE(+)
1385 AND flat.PK1_VALUE = EgoGtinEO.INVENTORY_ITEM_ID
1386 AND flat.ENTITY_TYPE = 'ITEM'
1387 AND flat.SESSION_ID = sessionId ;
1388
1389
1390 /* Insert Translations for GTIN */
1391 insert into EGO_PUB_WS_FLAT_RECS
1392 (
1393 SEQUENCE_ID,
1394 SESSION_ID,
1395 ODI_SESSION_ID,
1396 ENTITY_TYPE,
1397 PK1_VALUE,
1398 PK2_VALUE,
1399 PK3_VALUE,
1400 PARENT_SEQUENCE_ID,
1401 VALUE,
1402 CREATION_DATE
1403 )
1404 SELECT
1405 EGO_PUB_WS_FLAT_RECS_S.nextval,
1406 sessionId,
1407 odiSessionId,
1408 'GTIN_CROSS_REFERENCE_TRANSLATIONS',
1409 flat.PK1_VALUE,
1410 flat.PK2_VALUE,
1411 flat.PK3_VALUE,
1412 flat.SEQUENCE_ID,
1413 XMLForest(
1414 config.char_value AS "Language" ,
1415 mcrt.DESCRIPTION AS "GTINDescription"
1416 ).getclobval() ,
1417 SYSDATE
1418 FROM MTL_CROSS_REFERENCES_TL mcrt, EGO_PUB_WS_CONFIG config, EGO_PUB_WS_FLAT_RECS flat
1419 WHERE config.SESSION_ID = flat.SESSION_ID
1420 AND config.PARAMETER_NAME = 'LANGUAGE_CODE'
1421 AND config.CHAR_VALUE = mcrt.LANGUAGE
1422 AND flat.ENTITY_TYPE = 'GTIN_CROSS_REFERENCE'
1423 AND flat.REF1_VALUE = mcrt.CROSS_REFERENCE_ID
1424 AND flat.SESSION_ID = sessionId
1425 AND Nvl(mcrt.DESCRIPTION, 1) = Nvl(mcrt.DESCRIPTION, 2);
1426
1427 COMMIT;
1428
1429 END;
1430
1431
1432 -- This Procedure inserts the Transaction Attributee details associated to an Item into the table EGO_PUB_WS_FLAT_RECS
1433 PROCEDURE POPULATE_Transaction_Attrs
1434 (sessionId IN NUMBER,
1435 odisessionId IN NUMBER
1436 )
1437 AS
1438 l_item_start_date DATE ;/*Item start effective date*/
1439 l_item_create_date DATE ;/*Item create date*/
1440 l_version_seq_id VARCHAR2(5);
1441 l_ta_entered_count NUMBER;
1442 l_ta_count NUMBER;
1443
1444 CURSOR Cur_TA_List (cp_item_catalog_category_id NUMBER,
1445 cp_icc_version_number NUMBER,
1446 cp_creation_date DATE ,
1447 cp_start_date DATE )
1448 IS
1449
1450 SELECT * FROM
1451 (
1452 SELECT *
1453 FROM
1454 (
1455 SELECT versions.item_catalog_group_id,
1456 versions.icc_version_NUMBER ,
1457 attrs.attr_id ,
1458 attrs.attr_name ,
1459 hier.lev lev
1460 FROM ego_obj_AG_assocs_b assocs ,
1461 ego_attrs_v attrs ,
1462 ego_attr_groups_v ag ,
1463 EGO_TRANS_ATTR_VERS_B versions,
1464 mtl_item_catalog_groups_kfv icv ,
1465 (
1466 SELECT item_catalog_group_id,
1467 LEVEL lev
1468 FROM mtl_item_catalog_groups_b
1469 START WITH item_catalog_group_id = cp_item_catalog_category_id
1470 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
1471 )
1472 hier
1473 WHERE ag.attr_group_type = 'EGO_ITEM_TRANS_ATTR_GROUP'
1474 AND assocs.attr_group_id = ag.attr_group_id
1475 AND assocs.classification_code = TO_CHAR(hier.item_catalog_group_id)
1476 AND attrs.attr_group_name = ag.attr_group_name
1477 AND TO_CHAR(icv.item_catalog_group_id) = assocs.classification_code
1478 AND TO_CHAR(versions.association_id) = assocs.association_id
1479 AND TO_CHAR(versions.item_catalog_group_id) = assocs.classification_code
1480 AND attrs.attr_id = versions.attr_id
1481
1482 )
1483
1484
1485 )
1486 WHERE
1487 (
1488 ( LEV = 1 AND ICC_VERSION_number =cp_icc_version_number )
1489 OR
1490 ( LEV > 1 AND ( item_catalog_group_id, ICC_VERSION_NUMBER )
1491 IN ( SELECT item_catalog_group_id, VERSION_SEQ_ID
1492 FROM EGO_MTL_CATALOG_GRP_VERS_B
1493 WHERE (item_catalog_group_id,start_active_date )
1494 IN
1495 (SELECT item_catalog_group_id, MAX(start_active_date) start_active_date
1496 FROM EGO_MTL_CATALOG_GRP_VERS_B
1497 WHERE creation_date <= cp_creation_date
1498 AND version_seq_id > 0
1499 AND start_active_date <= cp_start_date
1500 GROUP BY item_catalog_group_id
1501 HAVING MAX(start_active_date)<=cp_start_date
1502 )
1503 AND version_seq_id > 0
1504 )
1505 )
1506 );
1507
1508
1509 CURSOR cur_item_list
1510 IS
1511 SELECT sequence_id,parent_sequence_id,pk1_value item_id ,pk2_value org_id, pk3_value rev_id
1512 FROM EGO_PUB_WS_FLAT_RECS
1513 WHERE session_id= sessionId
1514 AND odi_session_id = odisessionId
1515 AND entity_type ='ITEM_REVISION';
1516
1517
1518 l_item_ta_metadata_tbl EGO_TRAN_ATTR_TBL;
1519 l_return_status VARCHAR2(1):=NULL ;
1520 l_item_catalog_group_id VARCHAR2(10);
1521 l_is_modified VARCHAR2(2);
1522 l_is_inherited VARCHAR2(2);
1523
1524
1525 BEGIN
1526 l_item_ta_metadata_tbl := EGO_TRAN_ATTR_TBL(NULL);
1527 /* If input parameter has been passed then process data*/
1528 FOR j IN cur_item_list
1529 LOOP
1530 IF(j.item_id IS NOT NULL AND j.org_id IS NOT NULL AND j.rev_id IS NOT null)
1531 THEN
1532 --Finding which ICC is associated ot the item.
1533 SELECT ITEM_CATALOG_GROUP_ID INTO l_item_catalog_group_id FROM MTL_SYSTEM_ITEMS_VL
1534 WHERE INVENTORY_ITEM_ID = j.item_id AND ORGANIZATION_ID = j.org_id ;
1535
1536 IF(l_item_catalog_group_id IS NULL ) THEN
1537 NULL;
1538 ELSE
1539 SELECT EFFECTIVITY_DATE ,CREATION_DATE
1540 INTO l_item_start_date, l_item_create_date
1541 FROM MTL_ITEM_REVISIONS_VL
1542 WHERE INVENTORY_ITEM_ID = j.item_id
1543 AND ORGANIZATION_ID = j.org_id
1544 AND REVISION_ID = j.rev_id;
1545
1546 -- Bug 8690445 : Added the Exception Block
1547 BEGIN
1548 --Finding out which ICC version is effective at a time of item creation.
1549 SELECT VERSION_SEQ_ID INTO l_version_seq_id
1550 FROM EGO_MTL_CATALOG_GRP_VERS_B
1551 WHERE (ITEM_CATALOG_GROUP_ID, start_active_date) IN
1552 (
1553 SELECT ITEM_CATALOG_GROUP_ID,Max(START_ACTIVE_DATE) START_ACTIVE_DATE
1554 FROM EGO_MTL_CATALOG_GRP_VERS_B
1555 WHERE CREATION_DATE <= l_item_create_date AND
1556 ITEM_CATALOG_GROUP_ID = l_item_catalog_group_id AND
1557 START_ACTIVE_DATE <= l_item_start_date AND VERSION_SEQ_ID >0
1558 GROUP BY ITEM_CATALOG_GROUP_ID
1559 HAVING MAX(START_ACTIVE_DATE) <= l_item_start_date
1560 );
1561 EXCEPTION
1562 WHEN NO_DATA_FOUND THEN
1563 l_version_seq_id := NULL;
1564 END;
1565 END IF;
1566 IF (l_item_catalog_group_id IS NOT NULL AND l_version_seq_id IS NOT NULL)
1567 THEN
1568 SELECT Count(*)
1569 INTO l_ta_entered_count
1570 FROM EGO_PUB_WS_CONFIG
1571 WHERE PARAMETER_NAME = 'PUBLISH_TA_ID'
1572 AND SESSION_ID = sessionId;
1573
1574 FOR k IN Cur_TA_List(l_item_catalog_group_id, l_version_seq_id, l_item_create_date,l_item_start_date)
1575 LOOP
1576 SELECT Count(*)
1577 INTO l_ta_count
1578 FROM EGO_PUB_WS_CONFIG
1579 WHERE PARAMETER_NAME = 'PUBLISH_TA_ID'
1580 AND SESSION_ID = sessionId
1581 AND NUMERIC_VALUE = k.attr_id;
1582
1583 IF (l_ta_entered_count = 0 OR l_ta_count <> 0) THEN
1584 EGO_TRANSACTION_ATTRS_PVT.GET_TRANS_ATTR_METADATA(
1585 x_ta_metadata_tbl =>l_item_ta_metadata_tbl
1586 , p_item_catalog_category_id => NULL
1587 , p_icc_version => NULL
1588 , p_attribute_id => k.attr_id
1589 , p_inventory_item_id => j.item_id
1590 , p_organization_id => j.org_id
1591 , p_revision_id => j.rev_id
1592 , x_is_inherited => l_is_inherited
1593 , x_is_modified => l_is_modified
1594 , x_return_status => l_return_status );
1595
1596
1597 FOR i IN l_item_ta_metadata_tbl.first..l_item_ta_metadata_tbl.last
1598 LOOP
1599 INSERT INTO EGO_PUB_WS_FLAT_RECS
1600 (
1601 SEQUENCE_ID,
1602 SESSION_ID,
1603 ODI_SESSION_ID,
1604 ENTITY_TYPE,
1605 PK1_VALUE,
1606 PK2_VALUE,
1607 PK3_VALUE,
1608 REF1_VALUE,
1609 PARENT_SEQUENCE_ID,
1610 VALUE,
1611 CREATION_DATE
1612 )
1613 SELECT
1614 EGO_PUB_WS_FLAT_RECS_S.nextval,
1615 sessionId ,
1616 odisessionId,
1617 'TRANSACTION_ATTRIBUTE',
1618 j.item_id,
1619 j.org_id,
1620 j.rev_id,
1621 k.attr_id,
1622 j.sequence_id,
1623 xmlforest(l_item_ta_metadata_tbl(i).attrid as "AttributeId",
1624 k.attr_name as "AttrName",
1625 l_item_ta_metadata_tbl(i).AttrDisplayName as "AttrDisplayName",
1626 l_item_ta_metadata_tbl(i).SEQUENCE as "AttrSequence",
1627 l_item_ta_metadata_tbl(i).ValueSetName AS "ValueSetName", -- Bug 8643860
1628 l_item_ta_metadata_tbl(i).UomClass AS "UomClass", -- Bug 8643860
1629 l_item_ta_metadata_tbl(i).defaultvalue as "DeafultValue",
1630 l_item_ta_metadata_tbl(i).rejectedvalue as "RejectedValue",
1631 l_item_ta_metadata_tbl(i).requiredflag as "RequiredFlag",
1632 l_item_ta_metadata_tbl(i).readonlyflag as "ReadOnlyFlag",
1633 l_item_ta_metadata_tbl(i).hiddenflag as "HiddenFlag",
1634 l_item_ta_metadata_tbl(i).searchableflag as "SearchableFlag",
1635 l_item_ta_metadata_tbl(i).checkeligibility as "CheckEligibility" ,
1636 l_is_inherited AS "Inherited",
1637 l_is_modified AS "Modified"
1638 ).getclobval()
1639 ,SYSDATE
1640 FROM dual ;
1641 END LOOP; -- loop i
1642 END IF; -- end of if (l_ta_entered_count = 0 OR l_ta_count <> 0)
1643 END LOOP; -- loop k
1644 END IF;
1645 END IF;
1646 END LOOP; -- loop j
1647
1648 COMMIT;
1649 EXCEPTION
1650 WHEN OTHERS
1651 THEN
1652 RAISE;
1653 END;
1654
1655
1656 /* Procedure to get invocation Mode and setting batch_id based on invocation mode
1657 If mode is 'BATCH' then it will give some Batch Id, If mode is 'HMDM' or 'LIST' then
1658 Batch Id will be -1*/
1659 PROCEDURE Invocation_Mode ( p_session_id IN NUMBER,
1660 p_odi_session_id IN NUMBER,
1661 p_search_str IN VARCHAR2,
1662 x_mode OUT NOCOPY VARCHAR2,
1663 x_batch_id OUT NOCOPY NUMBER )
1664 IS
1665
1666 --Local Variable
1667 l_mode VARCHAR2(20) := 'MODE';
1668 l_batch_id NUMBER := -1;
1669 l_exists NUMBER;
1670 l_exists_inv_id NUMBER;
1671 l_exists_inv_name NUMBER;
1672 l_exists_items_list NUMBER;
1673 l_exists_org_id NUMBER;
1674 l_exists_org_code NUMBER;
1675 l_exists_rev_id NUMBER;
1676 l_exists_revision NUMBER;
1677 l_exists_rev_date NUMBER;
1678 l_inv_id NUMBER := -1;
1679 l_segments_provided BOOLEAN := FALSE;
1680
1681 l_segment_1 mtl_system_items_b.segment1%TYPE;
1682 l_segment_2 mtl_system_items_b.segment2%TYPE;
1683 l_segment_3 mtl_system_items_b.segment3%TYPE;
1684 l_segment_4 mtl_system_items_b.segment4%TYPE;
1685 l_segment_5 mtl_system_items_b.segment5%TYPE;
1686 l_segment_6 mtl_system_items_b.segment6%TYPE;
1687 l_segment_7 mtl_system_items_b.segment7%TYPE;
1688 l_segment_8 mtl_system_items_b.segment8%TYPE;
1689 l_segment_9 mtl_system_items_b.segment9%TYPE;
1690 l_segment_10 mtl_system_items_b.segment10%TYPE;
1691 l_segment_11 mtl_system_items_b.segment11%TYPE;
1692 l_segment_12 mtl_system_items_b.segment12%TYPE;
1693 l_segment_13 mtl_system_items_b.segment13%TYPE;
1694 l_segment_14 mtl_system_items_b.segment14%TYPE;
1695 l_segment_15 mtl_system_items_b.segment15%TYPE;
1696
1697 BEGIN
1698
1699 --if BatchId node exist and It has some value then we are in 'BATCH' mode
1700 -- p_search_str = '/itemQueryParameters/BatchId' for Batch
1701
1702 SELECT existsNode(xmlcontent, p_search_str)
1703 INTO l_exists
1704 FROM EGO_PUB_WS_PARAMS
1705 WHERE session_id = p_session_id;
1706
1707 IF l_exists=1 THEN
1708 /*If node exist for 'BatchId' then extractValue for BatchId'*/
1709 SELECT Nvl(extractValue(xmlcontent,p_search_str),-1)
1710 INTO l_batch_id
1711 FROM EGO_PUB_WS_PARAMS
1712 WHERE session_id = p_session_id;
1713 END IF;
1714
1715 IF(l_exists <> 1 or (l_exists = 1 and l_batch_id = -1)) THEN
1716
1717 SELECT existsNode(xmlcontent, '/itemQueryParameters/InventoryItemId') ,
1718 existsNode(xmlcontent, '/itemQueryParameters/InventoryItemName')
1719 INTO l_exists_inv_id , l_exists_inv_name
1720 FROM EGO_PUB_WS_PARAMS
1721 WHERE session_id = p_session_id;
1722
1723 SELECT existsNode(xmlcontent, '/itemQueryParameters/OrganizationId') ,
1724 existsNode(xmlcontent, '/itemQueryParameters/OrganizationCode')
1725 INTO l_exists_org_id , l_exists_org_code
1726 FROM EGO_PUB_WS_PARAMS
1727 WHERE session_id = p_session_id;
1728
1729 SELECT existsNode(xmlcontent, '/itemQueryParameters/RevisionId') ,
1730 existsNode(xmlcontent, '/itemQueryParameters/Revision') ,
1731 existsNode(xmlcontent, '/itemQueryParameters/RevisionDate')
1732 INTO l_exists_rev_id , l_exists_revision, l_exists_rev_date
1733 FROM EGO_PUB_WS_PARAMS
1734 WHERE session_id = p_session_id;
1735
1736 process_non_batch_flow(p_session_id,
1737 p_odi_session_id,
1738 l_exists_inv_id,
1739 l_exists_inv_name ,
1740 l_exists_org_id,
1741 l_exists_org_code,
1742 l_exists_rev_id,
1743 l_exists_revision ,
1744 l_exists_rev_date ,
1745 l_mode
1746 );
1747
1748 IF l_mode <> 'LIST' AND l_mode <> 'HMDM'
1749 THEN
1750 raise e_invalid_invocation_mode;
1751 END IF ;
1752 ELSE
1753 l_mode:= 'BATCH';
1754 END IF;
1755
1756 x_mode := l_mode;
1757 x_batch_id:= l_batch_id;
1758 EXCEPTION
1759 WHEN e_invalid_invocation_mode THEN
1760 RAISE e_invalid_invocation_mode;
1761
1762 END Invocation_Mode;
1763
1764
1765 PROCEDURE process_bom_explosions(p_session_id IN NUMBER,
1766 p_odi_session_id IN NUMBER,
1767 p_index IN NUMBER,
1768 pk1_value IN VARCHAR2 ,
1769 pk2_value IN varchar2,
1770 pk3_value IN varchar2,
1771 rev_date IN Date,
1772 alternate_desg IN VARCHAR2 DEFAULT NULL,
1773 levels_explode IN NUMBER DEFAULT 60,
1774 explode_option IN NUMBER,
1775 explode_std_bom IN VARCHAR2, -- Bug 8752314 : CMR Change
1776 group_id OUT NOCOPY NUMBER,
1777 x_error_code OUT NOCOPY VARCHAR2 ,
1778 x_error_message OUT NOCOPY VARCHAR2
1779 )
1780 IS
1781
1782 g_id number;
1783 top_bill_seq_id NUMBER;
1784
1785 BEGIN
1786
1787 bom_exploder_pub.exploder_userexit
1788 ( org_id => to_number(pk2_value)
1789 , rev_date => rev_date
1790 ,order_by => 1
1791 ,levels_to_explode => levels_explode
1792 ,impl_flag => 1 /* 1 - Imp Only, 2 - imp and unimpl */
1793 ,alt_desg => alternate_desg
1794 ,error_code => x_error_code
1795 ,err_msg => x_error_message
1796 ,bom_or_eng => 2 /* 1- BOM , 2 - ENG */
1797 ,explode_option => explode_option
1798 ,grp_id => g_id
1799 ,material_ctrl => 1
1800 ,pk_value1 => pk1_value
1801 ,pk_value2 => pk2_value
1802 ,std_bom_explode_flag => explode_std_bom -- Bug 8752314 : CMR Change
1803 );
1804
1805 if( To_Number(Nvl(x_error_code,0)) <> 0) THEN
1806 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
1807 p_odi_session_id => p_odi_session_id,
1808 p_input_id => p_index ,
1809 p_err_code => 'BOM_EXPLOSION_ERROR',
1810 p_err_message => 'Error: Error Occured while exploding the '||alternate_desg||' structure for the Item ');
1811
1812 DELETE ego_odi_ws_entities
1813 WHERE session_id = p_session_id
1814 AND pk1_value = pk1_value
1815 AND pk2_value = pk2_value
1816 AND pk3_value = pk3_value;
1817 else
1818 if( g_id is not null) then
1819 group_id := g_id;
1820 end if;
1821 end if;
1822
1823 exception
1824 when others then
1825 RAISE;
1826 END process_bom_explosions;
1827
1828
1829
1830 PROCEDURE Preprocess_Item_Input ( p_session_id IN NUMBER,
1831 p_odi_session_id IN NUMBER )
1832
1833 IS
1834 l_batch_id NUMBER;
1835 l_mode VARCHAR2(20);
1836 l_item_id_tab dbms_sql.VARCHAR2_table;
1837 l_org_id_tab dbms_sql.VARCHAR2_table;
1838 l_rev_id_tab dbms_sql.VARCHAR2_table;
1839 l_seq_num_tab dbms_sql.VARCHAR2_table;
1840 l_rev_date_tab dbms_sql.VARCHAR2_table; -- Bug 8659192
1841
1842 batch_entity_rec EGO_PUB_FWK_PK.TBL_OF_BAT_ENT_OBJ_TYPE;
1843 l_alt_desg VARCHAR2(100) := null;
1844 l_rev_date DATE;
1845 l_levels_to_explode NUMBER;
1846 l_group_id NUMBER;
1847 l_error_code VARCHAR2(100);
1848 l_error_message VARCHAR2(2000);
1849 x_return_status VARCHAR2(1);
1850 x_msg_count NUMBER;
1851 x_msg_data VARCHAR2(500);
1852 l_duplicates_count NUMBER;
1853 v_count NUMBER;
1854 l_exists_struct_name NUMBER;
1855 l_is_valid_structure BOOLEAN := TRUE;
1856
1857 l_explode_option NUMBER;
1858 l_exists_levels_to_explode NUMBER;
1859 l_exists_explode_option NUMBER;
1860 v_index NUMBER; -- Bug 8667104
1861 p_input_id NUMBER;
1862
1863 l_expl_std_bom VARCHAR2(10); -- Bug 8752314: CMR Change
1864 l_exists_explode_std NUMBER; -- Bug 8752314: CMR Change
1865
1866 e_invalid_batch_id EXCEPTION;
1867
1868 -- Bug 8706557 : Added below variables
1869 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
1870 l_parameter_t WF_PARAMETER_T := WF_PARAMETER_T(null, null);
1871 l_event_name VARCHAR2(240);
1872 l_event_key VARCHAR2(240);
1873 l_event_num NUMBER;
1874
1875 CURSOR cur_exploded_records (grp_id NUMBER, levels NUMBER )
1876 IS
1877 SELECT
1878 ego.inventory_item_id AS inventory_item_id
1879 ,ego.organization_id AS org_id
1880 ,bom_exploder_pub.get_component_revision_id(nvl(be.component_sequence_id, 0)) AS rev_id
1881 , bom_exploder_pub.get_component_revision_label(nvl(be.component_sequence_id, 0)) AS rev_label
1882 , be.plan_level AS plan_level
1883 FROM bom_explosions_all be , mtl_system_items_b_kfv ego
1884 WHERE be.group_id = grp_id
1885 AND ego.inventory_item_id = be.component_item_id
1886 AND ego.organization_id = be.organization_id
1887 AND be.plan_level <= levels
1888 AND be.plan_level > 0
1889 AND /* This whereclause for filter criteria: Start */
1890 ( bom_exploder_pub.get_explode_option = 1 OR
1891 be.plan_level = 0 OR
1892 /* Date Effectivity */
1893 ( nvl(be.effectivity_control,1) = 1 AND
1894 ( ( be.implementation_date IS NULL AND
1895 be.acd_type = 3 AND
1896 decode(be.comp_fixed_revision_id,
1897 NULL,bom_exploder_pub.get_explosion_date,
1898 bom_exploder_pub.get_revision_highdate(be.comp_fixed_revision_id)) >= be.trimmed_effectivity_date
1899 ) OR
1900 ( bom_exploder_pub.get_explode_option = 2 AND
1901 decode(be.comp_fixed_revision_id,
1902 NULL,bom_exploder_pub.get_explosion_date,
1903 bom_exploder_pub.get_revision_highdate(be.comp_fixed_revision_id)) >= be.trimmed_effectivity_date AND
1904 decode(be.comp_fixed_revision_id,
1905 NULL,bom_exploder_pub.get_explosion_date,
1906 bom_exploder_pub.get_revision_highdate(be.comp_fixed_revision_id)) < nvl(be.trimmed_disable_date,to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'))
1907 ) OR
1908 ( bom_exploder_pub.get_explode_option = 3 AND
1909 decode(be.comp_fixed_revision_id,
1910 NULL,bom_exploder_pub.get_explosion_date,
1911 bom_exploder_pub.get_revision_highdate(be.comp_fixed_revision_id)) < nvl(be.trimmed_disable_date,to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'))
1912 )
1913 )
1914 ) OR
1915 /* Rev Effectivity */
1916 ( nvl(be.effectivity_control,1) = 4 AND
1917 ( ( bom_exploder_pub.get_explode_option = 2 AND
1918 ( ( bom_exploder_pub.get_expl_end_item_rev_code >= (SELECT revision FROM mtl_item_revisions_b
1919 WHERE inventory_item_id = be.end_item_id
1920 AND organization_id = be.end_item_org_id
1921 AND revision_id = be.from_end_item_rev_id) AND
1922 ( be.to_end_item_rev_id IS NULL OR
1923 bom_exploder_pub.get_expl_end_item_rev_code <= (SELECT revision FROM mtl_item_revisions_b
1924 WHERE inventory_item_id = be.end_item_id
1925 AND organization_id = be.end_item_org_id
1926 AND revision_id = be.to_end_item_rev_id)
1927 )
1928 ) OR
1929 ( be.plan_level > 1 AND
1930 bom_exploder_pub.get_component_revision(nvl(be.parent_comp_seq_id,0)) >= (SELECT revision FROM mtl_item_revisions_b
1931 WHERE inventory_item_id = be.assembly_item_id
1932 AND organization_id = be.organization_id
1933 AND revision_id = be.from_end_item_rev_id) AND
1934 ( be.to_end_item_rev_id IS NULL OR
1935 bom_exploder_pub.get_component_revision(nvl(be.parent_comp_seq_id,0)) <= (SELECT revision FROM mtl_item_revisions_b
1936 WHERE inventory_item_id = be.assembly_item_id
1937 AND organization_id = be.organization_id
1938 AND revision_id = be.to_end_item_rev_id)
1939
1940 )
1941 )
1942 )
1943 ) OR
1944 ( bom_exploder_pub.get_explode_option = 3 AND
1945 ( ( be.to_end_item_rev_id IS NULL
1946 ) OR
1947 ( bom_exploder_pub.get_expl_end_item_rev_code <= (SELECT revision FROM mtl_item_revisions_b
1948 WHERE inventory_item_id = be.end_item_id
1949 AND organization_id = be.end_item_org_id
1950 AND revision_id = be.to_end_item_rev_id)
1951 ) OR
1952 ( be.plan_level > 1 AND
1953 bom_exploder_pub.get_component_revision(nvl(be.parent_comp_seq_id,0)) <= (SELECT revision FROM mtl_item_revisions_b
1954 WHERE inventory_item_id = be.assembly_item_id
1955 AND organization_id = be.organization_id
1956 AND revision_id = be.to_end_item_rev_id)
1957 )
1958 )
1959 )
1960 )
1961 ) OR
1962 /* Unit/Serial Effectivity */
1963 ( nvl(be.effectivity_control,1) = 2 AND
1964 ( ( bom_exploder_pub.get_explode_option = 2 AND
1965 bom_exploder_pub.get_expl_unit_number BETWEEN be.trimmed_from_unit_number AND nvl(be.trimmed_to_unit_number,bom_exploder_pub.get_expl_unit_number)
1966 ) OR
1967 ( bom_exploder_pub.get_explode_option = 3 AND
1968 bom_exploder_pub.get_expl_unit_number <= nvl(be.trimmed_to_unit_number,bom_exploder_pub.get_expl_unit_number)
1969 )
1970 )
1971 )
1972 );
1973
1974 BEGIN
1975 -- Bug 9752177 : Commenting the below code
1976 -- EXECUTE IMMEDIATE 'alter session set nls_date_format=''YYYY.MM.DD HH24:MI:SS'''; -- Bug 8659192
1977
1978 /* Call API to find invocation mode and batch_id, Batch_Id will be -1 if mode is HMDM or LIST*/
1979 Invocation_Mode( p_session_id,p_odi_session_id,'/itemQueryParameters/BatchId',l_mode,l_batch_id);
1980
1981 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
1982 VALUES (p_session_id,p_odi_session_id,'INVOCATION_MODE',2,NULL,l_mode,NULL,SYSDATE,G_CURRENT_USER_ID);
1983
1984 IF l_batch_id > -1 THEN
1985
1986 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
1987 VALUES (p_session_id,p_odi_session_id,'BATCH_ID',2,NULL,NULL,l_batch_id,SYSDATE,G_CURRENT_USER_ID);
1988
1989 SELECT pk1_value , pk2_value ,pk3_value
1990 BULK COLLECT INTO l_item_id_tab, l_org_id_tab, l_rev_id_tab
1991 FROM Ego_Pub_Bat_Ent_Objs_v --Find OUT NOCOPY if any other PK's
1992 WHERE batch_id = l_batch_id
1993 AND USER_ENTERED = 'Y';
1994
1995 -- Bug 8670655
1996 IF (l_item_id_tab.Count = 0) THEN
1997 RAISE e_invalid_batch_id;
1998 END IF;
1999
2000 SELECT CHAR_VALUE INTO l_alt_desg FROM EGO_PUB_BAT_PARAMS_B
2001 WHERE type_id=l_batch_id AND Upper(parameter_name) ='STRUCTURE_NAME';
2002
2003 /* Need to check for below */
2004 SELECT DATE_VALUE INTO l_rev_date FROM EGO_PUB_BAT_PARAMS_B
2005 WHERE type_id=l_batch_id AND Upper(parameter_name) ='EXPLOSION_DATE';
2006
2007 -- Bug 8683213 : Start
2008 BEGIN
2009 SELECT NUMERIC_VALUE INTO l_levels_to_explode FROM EGO_PUB_BAT_PARAMS_B
2010 WHERE type_id = l_batch_id AND Upper(parameter_name) ='LEVELS_TO_EXPLODE';
2011
2012 IF (l_levels_to_explode < 0) OR (l_levels_to_explode > 60) THEN
2013 l_levels_to_explode := 60;
2014 END IF;
2015 EXCEPTION
2016 WHEN NO_DATA_FOUND THEN
2017 l_levels_to_explode := 60;
2018 END;
2019 -- Bug 8683213 : End
2020
2021 -- Bug 12922355 : Start honor explode option in batch mode also.
2022 BEGIN
2023 -- Read the value for explode option.
2024 SELECT NUMERIC_VALUE INTO l_explode_option FROM EGO_PUB_BAT_PARAMS_B
2025 WHERE type_id = l_batch_id AND Upper(parameter_name) ='EXPLODE_OPTION';
2026 EXCEPTION
2027 WHEN NO_DATA_FOUND THEN
2028 -- Default Current option if user do not provide any value.
2029 l_explode_option := 2;
2030 END;
2031 -- Bug 12922355 : End
2032
2033 -- Bug 8752314 : CMR Change
2034 SELECT CHAR_VALUE INTO l_expl_std_bom FROM EGO_PUB_BAT_PARAMS_B
2035 WHERE type_id=l_batch_id AND Upper(parameter_name) ='EXPLODE_STD_BOM';
2036
2037 FOR i IN 1..l_item_id_tab.Count
2038 LOOP
2039 INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value,pk4_value, SEQUENCE_NUMBER)
2040 VALUES (p_session_id,p_odi_session_id,'ITEM',l_item_id_tab(i),l_org_id_tab(i),l_rev_id_tab(i), i, i);
2041 END LOOP;
2042
2043 ELSE
2044
2045 IF (l_mode <> 'MODE') THEN
2046 -- Process the details for NON - Batch Mode
2047 SELECT existsNode(xmlcontent, '/itemQueryParameters/StructureName')
2048 INTO l_exists_struct_name
2049 FROM EGO_PUB_WS_PARAMS
2050 WHERE session_id = p_session_id;
2051
2052 IF (l_exists_struct_name = 1) THEN
2053 SELECT extractValue(xmlcontent, '/itemQueryParameters/StructureName')
2054 INTO l_alt_desg
2055 FROM EGO_PUB_WS_PARAMS
2056 WHERE session_id = p_session_id;
2057 END IF;
2058
2059 SELECT existsNode(xmlcontent, '/itemQueryParameters/BomExploderParameters/LevelsToExplode'),
2060 existsNode(xmlcontent, '/itemQueryParameters/BomExploderParameters/ExplodeOption'),
2061 existsNode(xmlcontent, '/itemQueryParameters/BomExploderParameters/ExplodeStandard') -- Bug 8752314 : CMR Change
2062 INTO l_exists_levels_to_explode, l_exists_explode_option, l_exists_explode_std
2063 FROM EGO_PUB_WS_PARAMS
2064 WHERE session_id = p_session_id;
2065
2066 IF (l_exists_levels_to_explode = 1) THEN
2067 SELECT nvl(extractValue(xmlcontent, '/itemQueryParameters/BomExploderParameters/LevelsToExplode'),60)
2068 INTO l_levels_to_explode
2069 FROM EGO_PUB_WS_PARAMS
2070 WHERE session_id = p_session_id;
2071 ELSE
2072 l_levels_to_explode := 60;
2073 END IF;
2074
2075 IF (l_levels_to_explode < 0) OR (l_levels_to_explode > 60) THEN
2076 l_levels_to_explode := 60;
2077 END IF;
2078
2079 -- By Default the explode option should be Current.
2080 IF (l_exists_explode_option = 1) THEN
2081 SELECT nvl(extractValue(xmlcontent, '/itemQueryParameters/BomExploderParameters/ExplodeOption'),2)
2082 INTO l_explode_option
2083 FROM EGO_PUB_WS_PARAMS
2084 WHERE session_id = p_session_id;
2085 ELSE
2086 l_explode_option := 2;
2087 END IF;
2088
2089 -- Bug 8752314 : CMR Change
2090 IF (l_exists_explode_std = 1) THEN
2091 SELECT nvl(extractValue(xmlcontent, '/itemQueryParameters/BomExploderParameters/ExplodeStandard'),'Y')
2092 INTO l_expl_std_bom
2093 FROM EGO_PUB_WS_PARAMS
2094 WHERE session_id = p_session_id;
2095 ELSE
2096 l_expl_std_bom := 'Y';
2097 END IF;
2098
2099 END IF; -- end of (l_mode <> 'MODE')
2100
2101 END IF; -- end of l_batch_id > -1
2102
2103
2104 Init_Security_details(p_session_id, p_odi_session_id, x_return_status); -- Bug 8659248
2105
2106 IF (x_return_status = 'S') THEN
2107 -- check security for all items
2108 check_security( p_session_id => p_session_id,
2109 p_odi_session_id => p_odi_session_id,
2110 p_priv_check => 'EGO_PUBLISH_ITEM',
2111 p_for_exploded_items => 'N',
2112 x_return_status => x_return_status
2113 );
2114
2115 IF (x_return_status = 'S') THEN
2116 -- Fetch the entity details, for non batch get the rev date also for each entity
2117 IF l_batch_id = -1 THEN
2118 SELECT pk1_value , pk2_value ,pk3_value, SEQUENCE_NUMBER, To_Date(pk5_value,'YYYY.MM.DD HH24:MI:SS') -- Bug 8659192
2119 BULK COLLECT INTO l_item_id_tab, l_org_id_tab, l_rev_id_tab, l_seq_num_tab, l_rev_date_tab
2120 FROM ego_odi_ws_entities
2121 WHERE session_id = p_session_id
2122 AND nvl(REF1_VALUE, 'Y') = 'Y';
2123 ELSE
2124 SELECT pk1_value , pk2_value ,pk3_value, SEQUENCE_NUMBER
2125 BULK COLLECT INTO l_item_id_tab, l_org_id_tab, l_rev_id_tab , l_seq_num_tab
2126 FROM ego_odi_ws_entities
2127 WHERE session_id = p_session_id
2128 AND nvl(REF1_VALUE, 'Y') = 'Y';
2129 END IF;
2130
2131 IF (l_alt_desg IS NOT NULL) THEN
2132 FOR i IN 1..l_item_id_tab.Count
2133 LOOP
2134 IF ((l_alt_desg IS NOT NULL) AND (Upper(l_alt_desg) = 'PRIMARY')) THEN
2135 -- For Primary Structure, No Need to validate and assign null value
2136 l_alt_desg := '';
2137 ELSE
2138 -- validate structure details given
2139 l_is_valid_structure := validate_structure_name(p_session_id => p_session_id,
2140 p_odi_session_id => p_odi_session_id,
2141 p_org_id => l_org_id_tab(i),
2142 p_structure_name => l_alt_desg,
2143 p_input_id => l_seq_num_tab(i)
2144 );
2145 END IF;
2146
2147 IF (l_is_valid_structure) THEN
2148 -- Bug 8659192
2149 IF l_batch_id = -1 THEN
2150 l_rev_date := l_rev_date_tab(i);
2151 END IF;
2152
2153 /* Call Bom Exploder Procedure */
2154 process_bom_explosions(p_session_id => p_session_id,
2155 p_odi_session_id => p_odi_session_id,
2156 p_index => l_seq_num_tab(i),
2157 pk1_value => l_item_id_tab(i),
2158 pk2_value => l_org_id_tab(i) ,
2159 pk3_value => l_rev_id_tab(i),
2160 rev_date => l_rev_date ,
2161 alternate_desg => l_alt_desg ,
2162 levels_explode => l_levels_to_explode ,
2163 explode_option => l_explode_option ,
2164 explode_std_bom => l_expl_std_bom , -- Bug 8752314 : CMR Change
2165 group_id => l_group_id,
2166 x_error_code => l_error_code,
2167 x_error_message => l_error_message
2168 );
2169
2170 FOR j IN cur_exploded_records(l_group_id, l_levels_to_explode)
2171 LOOP
2172 -- Do not publish components exploded with null revisions.
2173 IF(j.rev_id IS NOT NULL) THEN
2174 INSERT INTO ego_odi_ws_entities ( session_id, odi_session_id, entity_type, pk1_value, pk2_value, pk3_value, pk4_value)
2175 VALUES (p_session_id,p_odi_session_id,'ITEM',j.inventory_item_id,j.org_id,j.rev_id, l_seq_num_tab(i) );
2176 END IF;
2177 END LOOP; -- end of loop j
2178 END IF; -- end of (l_is_valid_structure)
2179 END LOOP; -- end of loop i
2180
2181 /* Performance Change: Start
2182 -- Below Code is moved out of the loop, To improve the performance */
2183 -- check security for all exploded items
2184 check_security( p_session_id => p_session_id,
2185 p_odi_session_id => p_odi_session_id,
2186 p_priv_check => 'EGO_PUBLISH_ITEM',
2187 p_for_exploded_items => 'Y',
2188 x_return_status => x_return_status
2189 );
2190 IF (l_batch_id > -1 and x_return_status = 'S') THEN
2191 v_index := 1; -- Bug 8667104
2192 for k in (SELECT DISTINCT pk1_value , pk2_value ,pk3_value -- Bug 9530282
2193 FROM ego_odi_ws_entities
2194 WHERE session_id = p_session_id
2195 AND nvl(REF1_VALUE, 'Y') = 'Y')
2196 loop
2197 SELECT Count(*) INTO v_count
2198 FROM Ego_Pub_Bat_Ent_Objs_v
2199 WHERE batch_id = l_batch_id
2200 AND pk1_value = k.pk1_value
2201 AND pk2_value = k.pk2_value
2202 AND pk3_value = k.pk3_value;
2203
2204 IF (v_count = 0) THEN
2205 -- Bug 8667104 : Prepare the record only for derived entities
2206 batch_entity_rec(v_index).batch_id := l_batch_id;
2207 batch_entity_rec(v_index).pk1_value := k.pk1_value;
2208 batch_entity_rec(v_index).pk2_value := k.pk2_value;
2209 batch_entity_rec(v_index).pk3_value := k.pk3_value ;
2210 batch_entity_rec(v_index).pk4_value := NULL ;
2211 batch_entity_rec(v_index).pk5_value := NULL ;
2212 batch_entity_rec(v_index).user_entered := 'N';
2213
2214 v_index := v_index + 1;
2215
2216 END IF;
2217 end loop; -- end of loop k
2218
2219 -- Bug 8667104 : Calling the Below API for all the derived entities at a time, i.e in bulk
2220 EGO_PUB_FWK_PK.add_derived_entities(batch_entity_rec,x_return_status,x_msg_count,x_msg_data);
2221 IF (x_return_status <> 'S') THEN
2222 NULL;
2223 END IF;
2224 END IF; -- end of if (l_batch_id > -1 and x_return_status = 'S')
2225 -- Performance Change: End:
2226 END IF; -- end of if l_alt_desg IS NOT NULL
2227 END IF; -- end of (x_return_status = 'S')
2228
2229 /* Check for duplicate records in ego_odi_ws_entities */
2230 FOR i IN (SELECT pk1_value, pk2_value, pk3_value FROM ego_odi_ws_entities
2231 WHERE session_id = p_session_id AND entity_type = 'ITEM')
2232 LOOP
2233 select Count(*) INTO l_duplicates_count FROM ego_odi_ws_entities
2234 WHERE session_id = p_session_id and pk1_value = i.pk1_value and pk2_value = i.pk2_value AND pk3_value = i.pk3_value
2235 AND nvl(REF1_VALUE, 'Y') = 'Y'; -- Bug 8658259
2236
2237 IF l_duplicates_count > 0
2238 THEN
2239 DELETE ego_odi_ws_entities
2240 WHERE session_id = p_session_id
2241 AND pk1_value = i.pk1_value
2242 AND pk2_value = i.pk2_value
2243 AND pk3_value = i.pk3_value
2244 AND ROWNUM < l_duplicates_count;
2245 END IF;
2246 END LOOP;
2247 /* End of checking duplicate records */
2248 END IF;
2249
2250 select count(*) into v_count
2251 FROM ego_odi_ws_entities
2252 WHERE session_id = p_session_id
2253 AND nvl(REF1_VALUE, 'Y') = 'Y';
2254
2255 IF (v_count <> 0) THEN
2256 /* Insert all the configurations into the table in below procedure */
2257 process_configurations(p_session_id, p_odi_session_id);
2258
2259 -- Bug 8706557 : Start - Raise the business event for bacth mode
2260 IF (l_batch_id <> -1) THEN
2261 l_event_name := 'oracle.apps.ego.item.FreezePublishedItems' ;
2262
2263 SELECT MTL_BUSINESS_EVENTS_S.NEXTVAL into l_event_num FROM dual;
2264
2265 l_event_key := SUBSTRB(l_event_name, 1, 255) || '-' || l_event_num;
2266
2267 wf_event.AddParameterToList( p_name => 'BATCH_ID'
2268 ,p_value => l_batch_id
2269 ,p_ParameterList => l_parameter_List);
2270
2271 WF_EVENT.Raise( p_event_name => l_event_name
2272 ,p_event_key => l_event_key
2273 ,p_parameters => l_parameter_list);
2274
2275 l_parameter_list.DELETE;
2276 END IF;
2277 -- Bug 8706557 : End
2278 END IF;
2279
2280 COMMIT;
2281
2282 EXCEPTION
2283
2284 WHEN e_invalid_invocation_mode THEN
2285 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_input_id
2286 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
2287 WHERE session_id = p_session_id;
2288
2289 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
2290 p_odi_session_id => p_odi_session_id,
2291 p_input_id => p_input_id,
2292 p_err_code => 'EGO_INVALID_INVOCATION_MODE',
2293 p_err_message => 'Invalid Invocation Mode, No valid details of Items to be published are given');
2294
2295 WHEN e_no_org_details THEN
2296 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_input_id
2297 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
2298 WHERE session_id = p_session_id;
2299
2300 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
2301 p_odi_session_id => p_odi_session_id,
2302 p_input_id => p_input_id,
2303 p_err_code => 'EGO_NO_ORG_DETAILS',
2304 p_err_message => 'Organization details are not provided');
2305
2306 WHEN e_no_rev_details THEN
2307 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_input_id
2308 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
2309 WHERE session_id = p_session_id;
2310
2311 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
2312 p_odi_session_id => p_odi_session_id,
2313 p_input_id => p_input_id,
2314 p_err_code => 'EGO_NO_REV_DETAILS',
2315 p_err_message => 'Revision details are not provided');
2316
2317 WHEN e_invalid_batch_id THEN
2318 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_input_id
2319 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
2320 WHERE session_id = p_session_id;
2321
2322 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
2323 p_odi_session_id => p_odi_session_id,
2324 p_input_id => p_input_id,
2325 p_param_name => 'BatchId',
2326 p_param_value => l_batch_id );
2327
2328 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
2329 p_odi_session_id => p_odi_session_id,
2330 p_input_id => p_input_id,
2331 p_err_code => 'EGO_INVALID_BATCH_ID',
2332 p_err_message => 'Invalid Batch Id');
2333 WHEN OTHERS THEN
2334 RAISE;
2335 END Preprocess_Item_Input;
2336
2337
2338 PROCEDURE process_configurations ( p_session_id IN NUMBER,
2339 p_odi_session_id IN NUMBER)
2340
2341 IS
2342
2343 l_lang_code_tab dbms_sql.varchar2_table;
2344 l_lang_name_tab dbms_sql.varchar2_table; -- Bug 8670897
2345 l_uda_attr_name_tab dbms_sql.varchar2_table;
2346 l_uda_attr_id_tab dbms_sql.varchar2_table;
2347 l_ta_attr_id_tab dbms_sql.varchar2_table;
2348 l_ta_attr_name_tab dbms_sql.varchar2_table;
2349
2350 PUBLISH_OP_ATTR_GROUPS VARCHAR2(10);
2351
2352 l_node_exists NUMBER;
2353 l_node_exists_ag_id NUMBER;
2354 l_node_exists_ag_name NUMBER;
2355 l_node_exists_ta_id NUMBER;
2356 l_node_exists_ta_name NUMBER;
2357 l_lang_count NUMBER; -- Bug 8670897
2358
2359 L_PUBLISH_OP_ATTR_GROUPS VARCHAR2(10);
2360 L_PUBLISH_ITEM_CATALOG VARCHAR2(10);
2361 L_PUBLISH_INV_CHARS VARCHAR2(10);
2362 L_PUBLISH_PHY_CHARS VARCHAR2(10);
2363 L_PUBLISH_BOM_CHARS VARCHAR2(10);
2364 L_PUBLISH_WIP_CHARS VARCHAR2(10);
2365 L_PUBLISH_COST_CHARS VARCHAR2(10);
2366 L_PUBLISH_PLT_CHARS VARCHAR2(10);
2367 L_PUBLISH_PLAN_CHARS VARCHAR2(10);
2368 L_PUBLISH_PURCHASE_CHARS VARCHAR2(10);
2369 L_PUBLISH_RECEIVE_CHARS VARCHAR2(10);
2370 L_PUBLISH_OM_CHARS VARCHAR2(10);
2371 L_PUBLISH_INVOICE_CHARS VARCHAR2(10);
2372 L_PUBLISH_WEBOPT_CHARS VARCHAR2(10);
2373 L_PUBLISH_SERVICE_CHARS VARCHAR2(10);
2374 L_PUBLISH_ASSET_CHARS VARCHAR2(10);
2375 L_PUBLISH_PMFG_CHARS VARCHAR2(10);
2376 L_PUBLISH_UDA_GROUPS VARCHAR2(10);
2377 L_PUBLISH_ITEM_REVISION VARCHAR2(10);
2378 L_PUBLISH_TRANSACTION_ATTRS VARCHAR2(10);
2379 L_PUBLISH_RELATED_ITEMS VARCHAR2(10);
2380 L_PUBLISH_CUSTOMER_ITEMS VARCHAR2(10);
2381 L_PUBLISH_MFGPART_NUMBERS VARCHAR2(10);
2382 L_PUBLISH_GTIN_XREFS VARCHAR2(10);
2383 L_PUBLISH_ALTCAT_ASSIGNMENTS VARCHAR2(10);
2384 L_PUBLISH_SUPPLIER_ASSIGNMNETS VARCHAR2(10);
2385
2386 v_ags_count NUMBER;
2387 v_ag_null_cnt NUMBER;
2388 v_publish_udas VARCHAR2(10);
2389 v_publish_tas VARCHAR2(10);
2390 l_retpayload VARCHAR2(10); -- Added for Chunking
2391 p_index NUMBER;
2392
2393 BEGIN
2394
2395 /* Below Code is added for Chunking, extract configurable parameter 'ReturnPayload' */
2396 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ReturnPayload')
2397 INTO l_node_exists
2398 FROM EGO_PUB_WS_PARAMS
2399 WHERE session_id = p_session_id;
2400
2401 IF (l_node_exists = 0) THEN
2402 INSERT INTO EGO_PUB_WS_CONFIG (session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2403 VALUES (p_session_id,p_odi_session_id,'return_payload',2,NULL,'TRUE',NULL,SYSDATE,G_CURRENT_USER_ID);
2404 ELSE
2405 SELECT Upper(Nvl(extractValue(ret_pay, '/ReturnPayload'),'Y'))
2406 INTO l_retpayload
2407 FROM (SELECT Value(retpay) ret_pay
2408 FROM EGO_PUB_WS_PARAMS i,
2409 TABLE(XMLSequence(
2410 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ReturnPayload') )) retpay
2411 WHERE session_id=p_session_id
2412 );
2413
2414 --Insert record for configurable parameter 'ReturnPayload'
2415 If (Upper(l_retpayload)='Y' ) then
2416 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2417 VALUES (p_session_id,p_odi_session_id,'return_payload',2,NULL,'TRUE',NULL,SYSDATE,G_CURRENT_USER_ID);
2418 else
2419 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2420 VALUES (p_session_id,p_odi_session_id,'return_payload',2,NULL,'FALSE',NULL,SYSDATE,G_CURRENT_USER_ID);
2421 end if;
2422 END IF;
2423 /* End of Code added for Chunking */
2424
2425 -- Bug 12749057 : Start
2426 SELECT extractValue(lang, '/ListOfLanguages/LanguageCode'), extractValue(lang, '/ListOfLanguages/LanguageName')
2427 BULK COLLECT INTO l_lang_code_tab, l_lang_name_tab
2428 FROM (SELECT Value(lang) lang
2429 FROM EGO_PUB_WS_PARAMS i,
2430 TABLE(XMLSequence(
2431 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ListOfLanguages') )) lang
2432 WHERE session_id= p_session_id
2433 );
2434 -- Bug 12749057 : End
2435
2436 --Insert record into config table for parameter language
2437 -- Bug 8670897 : Below code is modified to handle Language Name along with Language Code
2438 IF ((l_lang_name_tab.Count = l_lang_code_tab.Count) AND l_lang_code_tab.Count > 0 AND l_lang_name_tab.Count > 0 ) THEN
2439 l_lang_count := 0;
2440 FOR i IN 1..l_lang_code_tab.Count
2441 LOOP
2442 IF (l_lang_code_tab(i) IS NULL) THEN
2443 IF (l_lang_name_tab(i) IS NULL) THEN
2444 l_lang_count := l_lang_count + 1;
2445 ELSE
2446 BEGIN
2447 SELECT language_code INTO l_lang_code_tab(i)
2448 FROM FND_LANGUAGES WHERE NLS_LANGUAGE = l_lang_name_tab(i);
2449
2450 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2451 VALUES (p_session_id,p_odi_session_id,'LANGUAGE_CODE',2,NULL,l_lang_code_tab(i),NULL,SYSDATE,G_CURRENT_USER_ID);
2452 EXCEPTION
2453 WHEN No_Data_Found THEN
2454
2455 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
2456 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
2457 WHERE session_id = p_session_id;
2458
2459 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
2460 p_odi_session_id => p_odi_session_id,
2461 p_input_id => p_index,
2462 p_param_name => 'LanguageName',
2463 p_param_value => l_lang_name_tab(i) );
2464
2465 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
2466 p_odi_session_id => p_odi_session_id,
2467 p_input_id => p_index,
2468 p_err_code => 'EGO_INVALID_LANGUAGE_NAME',
2469 p_err_message => 'Invalid Language Name');
2470
2471 END;
2472 END IF;
2473 ELSE
2474 BEGIN
2475 SELECT language_code INTO l_lang_code_tab(i)
2476 FROM FND_LANGUAGES WHERE language_code = l_lang_code_tab(i);
2477
2478 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2479 VALUES (p_session_id,p_odi_session_id,'LANGUAGE_CODE',2,NULL,l_lang_code_tab(i),NULL,SYSDATE,G_CURRENT_USER_ID);
2480 EXCEPTION
2481 WHEN NO_DATA_FOUND THEN
2482 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
2483 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
2484 WHERE session_id = p_session_id;
2485
2486 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
2487 p_odi_session_id => p_odi_session_id,
2488 p_input_id => p_index,
2489 p_param_name => 'LanguageCode',
2490 p_param_value => l_lang_code_tab(i) );
2491
2492 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
2493 p_odi_session_id => p_odi_session_id,
2494 p_input_id => p_index,
2495 p_err_code => 'EGO_INVALID_LANGUAGE_CODE',
2496 p_err_message => 'Invalid Language Code');
2497 END;
2498
2499 END IF;
2500 END LOOP;
2501
2502 IF (l_lang_count = l_lang_code_tab.Count) THEN
2503 FOR i IN (SELECT language_code FROM FND_LANGUAGES WHERE INSTALLED_FLAG IN ('I','B') ) LOOP
2504 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2505 VALUES (p_session_id,p_odi_session_id,'LANGUAGE_CODE',2,NULL,i.language_code,NULL,SYSDATE,G_CURRENT_USER_ID);
2506 END LOOP;
2507 END IF;
2508 -- Bug 12749057 : Start : When user doens't provide any labguage details.
2509 ELSE
2510 FOR i IN (SELECT language_code FROM FND_LANGUAGES WHERE INSTALLED_FLAG IN ('I','B') ) LOOP
2511 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2512 VALUES (p_session_id,p_odi_session_id,'LANGUAGE_CODE',2,NULL,i.language_code,NULL,SYSDATE,G_CURRENT_USER_ID);
2513 END LOOP;
2514 -- Bug 12749057 : End
2515 END IF;
2516
2517 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/OperationalAttributeGroups')
2518 INTO l_node_exists
2519 FROM EGO_PUB_WS_PARAMS
2520 WHERE session_id = p_session_id;
2521
2522 IF (l_node_exists = 1) THEN
2523 SELECT Nvl(extractValue(uda_ag, '/OperationalAttributeGroups'),'Y')
2524 INTO L_PUBLISH_OP_ATTR_GROUPS
2525 FROM (SELECT Value(udaag) uda_ag
2526 FROM EGO_PUB_WS_PARAMS i,
2527 TABLE(XMLSequence(
2528 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/OperationalAttributeGroups') )) udaag
2529 WHERE session_id=p_session_id
2530 );
2531
2532 -- Need to validate the values entered by user and throw error for wrong values. ???????????
2533
2534 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2535 VALUES (p_session_id,p_odi_session_id,'PUBLISH_OP_ATTR_GROUPS',2,NULL,Upper(L_PUBLISH_OP_ATTR_GROUPS),NULL,SYSDATE,G_CURRENT_USER_ID);
2536 ELSE
2537 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2538 VALUES (p_session_id,p_odi_session_id,'PUBLISH_OP_ATTR_GROUPS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2539 END IF; -- (l_node_exists = 1) for Operational Ags
2540
2541 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups')
2542 INTO l_node_exists
2543 FROM EGO_PUB_WS_PARAMS
2544 WHERE session_id = p_session_id;
2545
2546 IF (l_node_exists = 0) THEN
2547 -- insert 'Y' for all the operational attributes groups
2548 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2549 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ITEM_CATALOG',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2550
2551 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2552 VALUES (p_session_id,p_odi_session_id,'PUBLISH_INV_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2553
2554 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2555 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PHY_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2556
2557 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2558 VALUES (p_session_id,p_odi_session_id,'PUBLISH_BOM_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2559
2560 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2561 VALUES (p_session_id,p_odi_session_id,'PUBLISH_WIP_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2562
2563 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2564 VALUES (p_session_id,p_odi_session_id,'PUBLISH_COST_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2565
2566 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2567 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PLT_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2568
2569 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2570 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PLAN_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2571
2572 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2573 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PURCHASE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2574
2575 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2576 VALUES (p_session_id,p_odi_session_id,'PUBLISH_OM_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2577
2578 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2579 VALUES (p_session_id,p_odi_session_id,'PUBLISH_RECEIVE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2580
2581 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2582 VALUES (p_session_id,p_odi_session_id,'PUBLISH_INVOICE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2583
2584 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2585 VALUES (p_session_id,p_odi_session_id,'PUBLISH_WEBOPT_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2586
2587 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2588 VALUES (p_session_id,p_odi_session_id,'PUBLISH_SERVICE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2589
2590 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2591 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ASSET_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2592
2593 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2594 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PMFG_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2595
2596 ELSE
2597 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ItemCatalog')
2598 INTO l_node_exists
2599 FROM EGO_PUB_WS_PARAMS
2600 WHERE session_id = p_session_id;
2601
2602 IF (l_node_exists = 0) THEN
2603 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2604 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ITEM_CATALOG',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2605 ELSE
2606 SELECT Nvl(extractValue(uda_ag, '/ItemCatalog'),'Y')
2607 INTO L_PUBLISH_ITEM_CATALOG
2608 FROM (SELECT Value(udaag) uda_ag
2609 FROM EGO_PUB_WS_PARAMS i,
2610 TABLE(XMLSequence(
2611 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ItemCatalog') )) udaag
2612 WHERE session_id=p_session_id
2613 );
2614
2615 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2616 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ITEM_CATALOG',2,NULL,Upper(L_PUBLISH_ITEM_CATALOG),NULL,SYSDATE,G_CURRENT_USER_ID);
2617 END IF;
2618
2619 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/InventoryCharacteristics')
2620 INTO l_node_exists
2621 FROM EGO_PUB_WS_PARAMS
2622 WHERE session_id = p_session_id;
2623
2624 IF (l_node_exists = 0) THEN
2625 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2626 VALUES (p_session_id,p_odi_session_id,'PUBLISH_INV_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2627 ELSE
2628 SELECT Nvl(extractValue(uda_ag, '/InventoryCharacteristics'),'Y')
2629 INTO L_PUBLISH_INV_CHARS
2630 FROM (SELECT Value(udaag) uda_ag
2631 FROM EGO_PUB_WS_PARAMS i,
2632 TABLE(XMLSequence(
2633 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/InventoryCharacteristics') )) udaag
2634 WHERE session_id=p_session_id
2635 );
2636
2637 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2638 VALUES (p_session_id,p_odi_session_id,'PUBLISH_INV_CHARS',2,NULL,Upper(L_PUBLISH_INV_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2639
2640 END IF;
2641
2642 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PhysicalCharacteristics')
2643 INTO l_node_exists
2644 FROM EGO_PUB_WS_PARAMS
2645 WHERE session_id = p_session_id;
2646
2647 IF (l_node_exists = 0) THEN
2648 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2649 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PHY_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2650 ELSE
2651 SELECT Nvl(extractValue(uda_ag, '/PhysicalCharacteristics'),'Y')
2652 INTO L_PUBLISH_PHY_CHARS
2653 FROM (SELECT Value(udaag) uda_ag
2654 FROM EGO_PUB_WS_PARAMS i,
2655 TABLE(XMLSequence(
2656 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PhysicalCharacteristics') )) udaag
2657 WHERE session_id=p_session_id
2658 );
2659
2660 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2661 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PHY_CHARS',2,NULL,Upper(L_PUBLISH_PHY_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2662 END IF;
2663
2664
2665 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/BillsOfMaterialCharacteristics')
2666 INTO l_node_exists
2667 FROM EGO_PUB_WS_PARAMS
2668 WHERE session_id = p_session_id;
2669
2670 IF (l_node_exists = 0) THEN
2671 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2672 VALUES (p_session_id,p_odi_session_id,'PUBLISH_BOM_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2673 ELSE
2674 SELECT Nvl(extractValue(uda_ag, '/BillsOfMaterialCharacteristics'),'Y')
2675 INTO L_PUBLISH_BOM_CHARS
2676 FROM (SELECT Value(udaag) uda_ag
2677 FROM EGO_PUB_WS_PARAMS i,
2678 TABLE(XMLSequence(
2679 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/BillsOfMaterialCharacteristics') )) udaag
2680 WHERE session_id=p_session_id
2681 );
2682
2683 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2684 VALUES (p_session_id,p_odi_session_id,'PUBLISH_BOM_CHARS',2,NULL,Upper(L_PUBLISH_BOM_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2685 END IF;
2686
2687
2688 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/WorkInProcessCharacteristics')
2689 INTO l_node_exists
2690 FROM EGO_PUB_WS_PARAMS
2691 WHERE session_id = p_session_id;
2692
2693 IF (l_node_exists = 0) THEN
2694 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2695 VALUES (p_session_id,p_odi_session_id,'PUBLISH_WIP_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2696 ELSE
2697 SELECT Nvl(extractValue(uda_ag, '/WorkInProcessCharacteristics'),'Y')
2698 INTO L_PUBLISH_WIP_CHARS
2699 FROM (SELECT Value(udaag) uda_ag
2700 FROM EGO_PUB_WS_PARAMS i,
2701 TABLE(XMLSequence(
2702 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/WorkInProcessCharacteristics') )) udaag
2703 WHERE session_id=p_session_id
2704 );
2705
2706 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2707 VALUES (p_session_id,p_odi_session_id,'PUBLISH_WIP_CHARS',2,NULL,Upper(L_PUBLISH_WIP_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2708 END IF;
2709
2710 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/CostingCharacteristics')
2711 INTO l_node_exists
2712 FROM EGO_PUB_WS_PARAMS
2713 WHERE session_id = p_session_id;
2714
2715 IF (l_node_exists = 0) THEN
2716 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2717 VALUES (p_session_id,p_odi_session_id,'PUBLISH_COST_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2718 ELSE
2719 SELECT Nvl(extractValue(uda_ag, '/CostingCharacteristics'),'Y')
2720 INTO L_PUBLISH_COST_CHARS
2721 FROM (SELECT Value(udaag) uda_ag
2722 FROM EGO_PUB_WS_PARAMS i,
2723 TABLE(XMLSequence(
2724 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/CostingCharacteristics') )) udaag
2725 WHERE session_id=p_session_id
2726 );
2727
2728 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2729 VALUES (p_session_id,p_odi_session_id,'PUBLISH_COST_CHARS',2,NULL,Upper(L_PUBLISH_COST_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2730 END IF;
2731
2732 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ProcessingLeadTimeCharacteristics')
2733 INTO l_node_exists
2734 FROM EGO_PUB_WS_PARAMS
2735 WHERE session_id = p_session_id;
2736
2737 IF (l_node_exists = 0) THEN
2738 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2739 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PLT_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2740 ELSE
2741 SELECT Nvl(extractValue(uda_ag, '/ProcessingLeadTimeCharacteristics'),'Y')
2742 INTO L_PUBLISH_PLT_CHARS
2743 FROM (SELECT Value(udaag) uda_ag
2744 FROM EGO_PUB_WS_PARAMS i,
2745 TABLE(XMLSequence(
2746 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ProcessingLeadTimeCharacteristics') )) udaag
2747 WHERE session_id=p_session_id
2748 );
2749
2750 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2751 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PLT_CHARS',2,NULL,Upper(L_PUBLISH_PLT_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2752 END IF;
2753
2754 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PlanningCharacteristics')
2755 INTO l_node_exists
2756 FROM EGO_PUB_WS_PARAMS
2757 WHERE session_id = p_session_id;
2758
2759 IF (l_node_exists = 0) THEN
2760 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2761 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PLAN_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2762 ELSE
2763 SELECT Nvl(extractValue(uda_ag, '/PlanningCharacteristics'),'Y')
2764 INTO L_PUBLISH_PLAN_CHARS
2765 FROM (SELECT Value(udaag) uda_ag
2766 FROM EGO_PUB_WS_PARAMS i,
2767 TABLE(XMLSequence(
2768 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PlanningCharacteristics') )) udaag
2769 WHERE session_id=p_session_id
2770 );
2771
2772 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2773 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PLAN_CHARS',2,NULL,Upper(L_PUBLISH_PLAN_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2774 END IF;
2775
2776 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PurchasingCharacteristics')
2777 INTO l_node_exists
2778 FROM EGO_PUB_WS_PARAMS
2779 WHERE session_id = p_session_id;
2780
2781 IF (l_node_exists = 0) THEN
2782 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2783 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PURCHASE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2784 ELSE
2785 SELECT Nvl(extractValue(uda_ag, '/PurchasingCharacteristics'),'Y')
2786 INTO L_PUBLISH_PURCHASE_CHARS
2787 FROM (SELECT Value(udaag) uda_ag
2788 FROM EGO_PUB_WS_PARAMS i,
2789 TABLE(XMLSequence(
2790 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PurchasingCharacteristics') )) udaag
2791 WHERE session_id=p_session_id
2792 );
2793
2794 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2795 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PURCHASE_CHARS',2,NULL,Upper(L_PUBLISH_PURCHASE_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2796 END IF;
2797
2798 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/OrderManagementCharacteristics')
2799 INTO l_node_exists
2800 FROM EGO_PUB_WS_PARAMS
2801 WHERE session_id = p_session_id;
2802
2803 IF (l_node_exists = 0) THEN
2804 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2805 VALUES (p_session_id,p_odi_session_id,'PUBLISH_OM_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2806 ELSE
2807 SELECT Nvl(extractValue(uda_ag, '/OrderManagementCharacteristics'),'Y')
2808 INTO L_PUBLISH_OM_CHARS
2809 FROM (SELECT Value(udaag) uda_ag
2810 FROM EGO_PUB_WS_PARAMS i,
2811 TABLE(XMLSequence(
2812 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/OrderManagementCharacteristics') )) udaag
2813 WHERE session_id=p_session_id
2814 );
2815
2816 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2817 VALUES (p_session_id,p_odi_session_id,'PUBLISH_OM_CHARS',2,NULL,Upper(L_PUBLISH_OM_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2818 END IF;
2819
2820 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ReceivingCharacteristics')
2821 INTO l_node_exists
2822 FROM EGO_PUB_WS_PARAMS
2823 WHERE session_id = p_session_id;
2824
2825 IF (l_node_exists = 0) THEN
2826 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2827 VALUES (p_session_id,p_odi_session_id,'PUBLISH_RECEIVE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2828 ELSE
2829 SELECT Nvl(extractValue(uda_ag, '/ReceivingCharacteristics'),'Y')
2830 INTO L_PUBLISH_RECEIVE_CHARS
2831 FROM (SELECT Value(udaag) uda_ag
2832 FROM EGO_PUB_WS_PARAMS i,
2833 TABLE(XMLSequence(
2834 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ReceivingCharacteristics') )) udaag
2835 WHERE session_id=p_session_id
2836 );
2837
2838 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2839 VALUES (p_session_id,p_odi_session_id,'PUBLISH_RECEIVE_CHARS',2,NULL,Upper(L_PUBLISH_RECEIVE_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2840 END IF;
2841
2842 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/InvoicingCharacteristics')
2843 INTO l_node_exists
2844 FROM EGO_PUB_WS_PARAMS
2845 WHERE session_id = p_session_id;
2846
2847 IF (l_node_exists = 0) THEN
2848 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2849 VALUES (p_session_id,p_odi_session_id,'PUBLISH_INVOICE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2850 ELSE
2851 SELECT Nvl(extractValue(uda_ag, '/InvoicingCharacteristics'),'Y')
2852 INTO L_PUBLISH_INVOICE_CHARS
2853 FROM (SELECT Value(udaag) uda_ag
2854 FROM EGO_PUB_WS_PARAMS i,
2855 TABLE(XMLSequence(
2856 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/InvoicingCharacteristics') )) udaag
2857 WHERE session_id=p_session_id
2858 );
2859
2860 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2861 VALUES (p_session_id,p_odi_session_id,'PUBLISH_INVOICE_CHARS',2,NULL,Upper(L_PUBLISH_INVOICE_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2862 END IF;
2863
2864 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/WebOptionsCharacteristics')
2865 INTO l_node_exists
2866 FROM EGO_PUB_WS_PARAMS
2867 WHERE session_id = p_session_id;
2868
2869 IF (l_node_exists = 0) THEN
2870 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2871 VALUES (p_session_id,p_odi_session_id,'PUBLISH_WEBOPT_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2872 ELSE
2873 SELECT Nvl(extractValue(uda_ag, '/WebOptionsCharacteristics'),'Y')
2874 INTO L_PUBLISH_WEBOPT_CHARS
2875 FROM (SELECT Value(udaag) uda_ag
2876 FROM EGO_PUB_WS_PARAMS i,
2877 TABLE(XMLSequence(
2878 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/WebOptionsCharacteristics') )) udaag
2879 WHERE session_id=p_session_id
2880 );
2881
2882 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2883 VALUES (p_session_id,p_odi_session_id,'PUBLISH_WEBOPT_CHARS',2,NULL,Upper(L_PUBLISH_WEBOPT_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2884 END IF;
2885
2886 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ServiceCharacteristics')
2887 INTO l_node_exists
2888 FROM EGO_PUB_WS_PARAMS
2889 WHERE session_id = p_session_id;
2890
2891 IF (l_node_exists = 0) THEN
2892 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2893 VALUES (p_session_id,p_odi_session_id,'PUBLISH_SERVICE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2894 ELSE
2895 SELECT Nvl(extractValue(uda_ag, '/ServiceCharacteristics'),'Y')
2896 INTO L_PUBLISH_SERVICE_CHARS
2897 FROM (SELECT Value(udaag) uda_ag
2898 FROM EGO_PUB_WS_PARAMS i,
2899 TABLE(XMLSequence(
2900 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ServiceCharacteristics') )) udaag
2901 WHERE session_id=p_session_id
2902 );
2903
2904 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2905 VALUES (p_session_id,p_odi_session_id,'PUBLISH_SERVICE_CHARS',2,NULL,Upper(L_PUBLISH_SERVICE_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2906 END IF;
2907
2908 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/AssetCharacteristics')
2909 INTO l_node_exists
2910 FROM EGO_PUB_WS_PARAMS
2911 WHERE session_id = p_session_id;
2912
2913 IF (l_node_exists = 0) THEN
2914 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2915 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ASSET_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2916 ELSE
2917 SELECT Nvl(extractValue(uda_ag, '/AssetCharacteristics'),'Y')
2918 INTO L_PUBLISH_ASSET_CHARS
2919 FROM (SELECT Value(udaag) uda_ag
2920 FROM EGO_PUB_WS_PARAMS i,
2921 TABLE(XMLSequence(
2922 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/AssetCharacteristics') )) udaag
2923 WHERE session_id=p_session_id
2924 );
2925
2926 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2927 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ASSET_CHARS',2,NULL,Upper(L_PUBLISH_ASSET_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2928 END IF;
2929
2930 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ProcessMfgCharacteristics')
2931 INTO l_node_exists
2932 FROM EGO_PUB_WS_PARAMS
2933 WHERE session_id = p_session_id;
2934
2935 IF (l_node_exists = 0) THEN
2936 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2937 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PMFG_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2938 ELSE
2939 SELECT Nvl(extractValue(uda_ag, '/ProcessMfgCharacteristics'),'Y')
2940 INTO L_PUBLISH_PMFG_CHARS
2941 FROM (SELECT Value(udaag) uda_ag
2942 FROM EGO_PUB_WS_PARAMS i,
2943 TABLE(XMLSequence(
2944 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ProcessMfgCharacteristics') )) udaag
2945 WHERE session_id=p_session_id
2946 );
2947
2948 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2949 VALUES (p_session_id,p_odi_session_id,'PUBLISH_PMFG_CHARS',2,NULL,Upper(L_PUBLISH_PMFG_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
2950 END IF;
2951
2952 END IF; -- (l_node_exists = 0) for Publish Operational Ags
2953
2954 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/UserDefinedAttributeGroups')
2955 INTO l_node_exists
2956 FROM EGO_PUB_WS_PARAMS
2957 WHERE session_id = p_session_id;
2958
2959 IF (l_node_exists = 0) THEN
2960 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2961 VALUES (p_session_id,p_odi_session_id,'PUBLISH_UDA_GROUPS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2962 ELSE
2963 SELECT Nvl(extractValue(uda_ag, '/UserDefinedAttributeGroups'),'Y')
2964 INTO L_PUBLISH_UDA_GROUPS
2965 FROM (SELECT Value(udaag) uda_ag
2966 FROM EGO_PUB_WS_PARAMS i,
2967 TABLE(XMLSequence(
2968 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/UserDefinedAttributeGroups') )) udaag
2969 WHERE session_id=p_session_id
2970 );
2971
2972 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2973 VALUES (p_session_id,p_odi_session_id,'PUBLISH_UDA_GROUPS',2,NULL,Upper(L_PUBLISH_UDA_GROUPS),NULL,SYSDATE,G_CURRENT_USER_ID);
2974 END IF;
2975
2976
2977 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ItemRevision')
2978 INTO l_node_exists
2979 FROM EGO_PUB_WS_PARAMS
2980 WHERE session_id = p_session_id;
2981
2982 IF (l_node_exists = 0) THEN
2983 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2984 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ITEM_REVISION',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
2985 ELSE
2986 SELECT Nvl(extractValue(uda_ag, '/ItemRevision'),'Y')
2987 INTO L_PUBLISH_ITEM_REVISION
2988 FROM (SELECT Value(udaag) uda_ag
2989 FROM EGO_PUB_WS_PARAMS i,
2990 TABLE(XMLSequence(
2991 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ItemRevision') )) udaag
2992 WHERE session_id=p_session_id
2993 );
2994
2995 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
2996 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ITEM_REVISION',2,NULL,Upper(L_PUBLISH_ITEM_REVISION),NULL,SYSDATE,G_CURRENT_USER_ID);
2997 END IF;
2998
2999 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/TransactionAttributes')
3000 INTO l_node_exists
3001 FROM EGO_PUB_WS_PARAMS
3002 WHERE session_id = p_session_id;
3003
3004 IF (l_node_exists = 0) THEN
3005 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3006 VALUES (p_session_id,p_odi_session_id,'PUBLISH_TRANSACTION_ATTRS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
3007 ELSE
3008 SELECT Nvl(extractValue(uda_ag, '/TransactionAttributes'),'Y')
3009 INTO L_PUBLISH_TRANSACTION_ATTRS
3010 FROM (SELECT Value(udaag) uda_ag
3011 FROM EGO_PUB_WS_PARAMS i,
3012 TABLE(XMLSequence(
3013 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/TransactionAttributes') )) udaag
3014 WHERE session_id=p_session_id
3015 );
3016
3017 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3018 VALUES (p_session_id,p_odi_session_id,'PUBLISH_TRANSACTION_ATTRS',2,NULL,Upper(L_PUBLISH_TRANSACTION_ATTRS),NULL,SYSDATE,G_CURRENT_USER_ID);
3019
3020 END IF;
3021
3022 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/RelatedItems')
3023 INTO l_node_exists
3024 FROM EGO_PUB_WS_PARAMS
3025 WHERE session_id = p_session_id;
3026
3027 IF (l_node_exists = 0) THEN
3028 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3029 VALUES (p_session_id,p_odi_session_id,'PUBLISH_RELATED_ITEMS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
3030 ELSE
3031 SELECT Nvl(extractValue(uda_ag, '/RelatedItems'),'Y')
3032 INTO L_PUBLISH_RELATED_ITEMS
3033 FROM (SELECT Value(udaag) uda_ag
3034 FROM EGO_PUB_WS_PARAMS i,
3035 TABLE(XMLSequence(
3036 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/RelatedItems') )) udaag
3037 WHERE session_id=p_session_id
3038 );
3039
3040 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3041 VALUES (p_session_id,p_odi_session_id,'PUBLISH_RELATED_ITEMS',2,NULL,Upper(L_PUBLISH_RELATED_ITEMS),NULL,SYSDATE,G_CURRENT_USER_ID);
3042 END IF;
3043
3044
3045 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/CustomerItems')
3046 INTO l_node_exists
3047 FROM EGO_PUB_WS_PARAMS
3048 WHERE session_id = p_session_id;
3049
3050 IF (l_node_exists = 0) THEN
3051 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3052 VALUES (p_session_id,p_odi_session_id,'PUBLISH_CUSTOMER_ITEMS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
3053 ELSE
3054 SELECT Nvl(extractValue(uda_ag, '/CustomerItems'),'Y')
3055 INTO L_PUBLISH_CUSTOMER_ITEMS
3056 FROM (SELECT Value(udaag) uda_ag
3057 FROM EGO_PUB_WS_PARAMS i,
3058 TABLE(XMLSequence(
3059 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/CustomerItems') )) udaag
3060 WHERE session_id=p_session_id
3061 );
3062
3063 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3064 VALUES (p_session_id,p_odi_session_id,'PUBLISH_CUSTOMER_ITEMS',2,NULL,Upper(L_PUBLISH_CUSTOMER_ITEMS),NULL,SYSDATE,G_CURRENT_USER_ID);
3065
3066 END IF;
3067
3068 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ManufacturerPartNumbers')
3069 INTO l_node_exists
3070 FROM EGO_PUB_WS_PARAMS
3071 WHERE session_id = p_session_id;
3072
3073 IF (l_node_exists = 0) THEN
3074 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3075 VALUES (p_session_id,p_odi_session_id,'PUBLISH_MFGPART_NUMBERS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
3076 ELSE
3077 SELECT Nvl(extractValue(uda_ag, '/ManufacturerPartNumbers'),'Y')
3078 INTO L_PUBLISH_MFGPART_NUMBERS
3079 FROM (SELECT Value(udaag) uda_ag
3080 FROM EGO_PUB_WS_PARAMS i,
3081 TABLE(XMLSequence(
3082 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ManufacturerPartNumbers') )) udaag
3083 WHERE session_id=p_session_id
3084 );
3085
3086 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3087 VALUES (p_session_id,p_odi_session_id,'PUBLISH_MFGPART_NUMBERS',2,NULL,Upper(L_PUBLISH_MFGPART_NUMBERS),NULL,SYSDATE,G_CURRENT_USER_ID);
3088 END IF;
3089
3090 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/GTINCrossReferences')
3091 INTO l_node_exists
3092 FROM EGO_PUB_WS_PARAMS
3093 WHERE session_id = p_session_id;
3094
3095 IF (l_node_exists = 0) THEN
3096 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3097 VALUES (p_session_id,p_odi_session_id,'PUBLISH_GTIN_XREFS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
3098 ELSE
3099 SELECT Nvl(extractValue(uda_ag, '/GTINCrossReferences'),'Y')
3100 INTO L_PUBLISH_GTIN_XREFS
3101 FROM (SELECT Value(udaag) uda_ag
3102 FROM EGO_PUB_WS_PARAMS i,
3103 TABLE(XMLSequence(
3104 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/GTINCrossReferences') )) udaag
3105 WHERE session_id=p_session_id
3106 );
3107
3108 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3109 VALUES (p_session_id,p_odi_session_id,'PUBLISH_GTIN_XREFS',2,NULL,Upper(L_PUBLISH_GTIN_XREFS),NULL,SYSDATE,G_CURRENT_USER_ID);
3110 END IF;
3111
3112 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/AlternateCategoryAssignments')
3113 INTO l_node_exists
3114 FROM EGO_PUB_WS_PARAMS
3115 WHERE session_id = p_session_id;
3116
3117 IF (l_node_exists = 0) THEN
3118 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3119 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ALTCAT_ASSIGNMENTS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
3120
3121 ELSE
3122 SELECT Nvl(extractValue(uda_ag, '/AlternateCategoryAssignments'),'Y')
3123 INTO L_PUBLISH_ALTCAT_ASSIGNMENTS
3124 FROM (SELECT Value(udaag) uda_ag
3125 FROM EGO_PUB_WS_PARAMS i,
3126 TABLE(XMLSequence(
3127 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/AlternateCategoryAssignments') )) udaag
3128 WHERE session_id=p_session_id
3129 );
3130
3131 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3132 VALUES (p_session_id,p_odi_session_id,'PUBLISH_ALTCAT_ASSIGNMENTS',2,NULL,Upper(L_PUBLISH_ALTCAT_ASSIGNMENTS),NULL,SYSDATE,G_CURRENT_USER_ID);
3133 END IF;
3134
3135 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/SupplierAssignments')
3136 INTO l_node_exists
3137 FROM EGO_PUB_WS_PARAMS
3138 WHERE session_id = p_session_id;
3139
3140 IF (l_node_exists = 0) THEN
3141 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3142 VALUES (p_session_id,p_odi_session_id,'PUBLISH_SUPPLIER_ASSIGNMNETS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
3143 ELSE
3144 SELECT Nvl(extractValue(uda_ag, '/SupplierAssignments'),'Y')
3145 INTO L_PUBLISH_SUPPLIER_ASSIGNMNETS
3146 FROM (SELECT Value(udaag) uda_ag
3147 FROM EGO_PUB_WS_PARAMS i,
3148 TABLE(XMLSequence(
3149 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/SupplierAssignments') )) udaag
3150 WHERE session_id=p_session_id
3151 );
3152
3153 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3154 VALUES (p_session_id,p_odi_session_id,'PUBLISH_SUPPLIER_ASSIGNMNETS',2,NULL,Upper(L_PUBLISH_SUPPLIER_ASSIGNMNETS),NULL,SYSDATE,G_CURRENT_USER_ID);
3155 END IF;
3156
3157 SELECT CHAR_VALUE INTO v_publish_udas
3158 FROM EGO_PUB_WS_CONFIG
3159 WHERE Parameter_Name = 'PUBLISH_UDA_GROUPS'
3160 AND session_id = p_session_id;
3161
3162 -- Validate the List of UDAS provided only if publishing UDAS
3163 IF (v_publish_udas = 'Y') THEN
3164 /* Process for UDAS */
3165
3166 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups')
3167 INTO l_node_exists
3168 FROM EGO_PUB_WS_PARAMS
3169 WHERE session_id = p_session_id;
3170
3171
3172 IF (l_node_exists = 1) THEN -- (l_node_exists = 1) for list of pub udags
3173 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups/AttributeGroupId') ,
3174 existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups/AttributeGroupName')
3175 INTO l_node_exists_ag_id, l_node_exists_ag_name
3176 FROM EGO_PUB_WS_PARAMS
3177 WHERE session_id = p_session_id;
3178
3179 IF (l_node_exists_ag_id <> 0 AND l_node_exists_ag_name <> 0) THEN
3180 SELECT extractValue(uda_ag, '/AttributeGroupId')
3181 BULK COLLECT INTO l_uda_attr_id_tab
3182 FROM (SELECT Value(udaag) uda_ag
3183 FROM EGO_PUB_WS_PARAMS i,
3184 TABLE(XMLSequence(
3185 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups/AttributeGroupId') )) udaag
3186 WHERE session_id=p_session_id
3187 );
3188
3189 SELECT extractValue(uda_ag, '/AttributeGroupName')
3190 BULK COLLECT INTO l_uda_attr_name_tab
3191 FROM (SELECT Value(udaag) uda_ag
3192 FROM EGO_PUB_WS_PARAMS i,
3193 TABLE(XMLSequence(
3194 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups/AttributeGroupName') )) udaag
3195 WHERE session_id=p_session_id
3196 );
3197
3198 IF ((l_uda_attr_id_tab.Count = l_uda_attr_name_tab.Count) AND l_uda_attr_id_tab.Count > 0 AND l_uda_attr_name_tab.Count > 0 )
3199 THEN
3200 v_ag_null_cnt := 0;
3201 FOR i IN 1..l_uda_attr_id_tab.Count
3202 LOOP
3203
3204 IF (l_uda_attr_name_tab(i) IS NOT NULL)
3205 THEN
3206 BEGIN
3207 /* Need to validate for valid input values */
3208 SELECT ATTR_GROUP_NAME
3209 INTO l_uda_attr_name_tab(i) -- v_attr_group_name
3210 FROM ego_attr_groups_v
3211 WHERE ATTR_GROUP_NAME = l_uda_attr_name_tab(i)
3212 AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';
3213
3214 EXCEPTION
3215 WHEN No_Data_Found THEN
3216 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
3217 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
3218 WHERE session_id = p_session_id;
3219
3220 -- Throw error : Attribute Id given is wrong.????? or will ignore the attribute
3221 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
3222 p_odi_session_id => p_odi_session_id,
3223 p_input_id => p_index,
3224 p_param_name => 'AttributeGroupName',
3225 p_param_value => l_uda_attr_name_tab(i) );
3226
3227 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
3228 p_odi_session_id => p_odi_session_id,
3229 p_input_id => p_index,
3230 p_err_code => 'EGO_INVALID_AG_NAME',
3231 p_err_message => 'Invalid Attribute Group Name');
3232
3233 l_uda_attr_name_tab(i) := NULL;
3234 l_uda_attr_name_tab(i) := NULL;
3235 END;
3236 ELSIF (l_uda_attr_id_tab(i) IS NOT NULL) THEN
3237 BEGIN
3238 /* Need to validate for valid input values */
3239 SELECT ATTR_GROUP_NAME
3240 INTO l_uda_attr_name_tab(i) -- v_attr_group_name
3241 FROM ego_attr_groups_v
3242 WHERE ATTR_GROUP_ID = l_uda_attr_id_tab(i)
3243 AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';
3244
3245 EXCEPTION
3246 WHEN No_Data_Found THEN
3247 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
3248 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
3249 WHERE session_id = p_session_id;
3250
3251 -- Throw error : Attribute Id given is wrong.????? or will ignore the attribute
3252 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
3253 p_odi_session_id => p_odi_session_id,
3254 p_input_id => p_index,
3255 p_param_name => 'AttributeGroupId',
3256 p_param_value => l_uda_attr_id_tab(i) );
3257
3258 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
3259 p_odi_session_id => p_odi_session_id,
3260 p_input_id => p_index,
3261 p_err_code => 'EGO_INVALID_AG_ID',
3262 p_err_message => 'Invalid Attribute Group Id');
3263
3264 l_uda_attr_name_tab(i) := NULL;
3265 l_uda_attr_name_tab(i) := NULL;
3266 END;
3267 ELSE
3268 v_ag_null_cnt := v_ag_null_cnt + 1;
3269 END IF;
3270
3271 IF (l_uda_attr_name_tab(i) IS NOT NULL)
3272 THEN
3273 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3274 VALUES (p_session_id,p_odi_session_id,'PUBLISH_AG_NAME',2,NULL, l_uda_attr_name_tab(i),NULL,SYSDATE,G_CURRENT_USER_ID);
3275 END IF;
3276
3277 END LOOP;
3278
3279 -- 8667733 : Start
3280 SELECT Count(*) INTO v_ags_count
3281 FROM EGO_PUB_WS_CONFIG
3282 WHERE Parameter_Name = 'PUBLISH_AG_NAME'
3283 AND session_id = p_session_id;
3284
3285 -- If all the AGs provided are invalid, then Do not fetch UDA at all.
3286 IF (v_ags_count = 0 AND (v_ag_null_cnt <> l_uda_attr_id_tab.Count)) THEN
3287 UPDATE EGO_PUB_WS_CONFIG
3288 SET Char_value = 'N'
3289 WHERE session_id = p_session_id
3290 AND Parameter_Name = 'PUBLISH_UDA_GROUPS';
3291 END IF;
3292 -- 8667733 : End
3293 END IF;
3294 END IF; -- end of (l_node_exists <> 0 AND l_node_exists_ag_name <> 0)
3295 END IF; -- end of (l_node_exists = 1) for list of pub udags
3296 END IF; -- end of (v_publish_udas = 'Y')
3297
3298
3299 SELECT CHAR_VALUE INTO v_publish_tas
3300 FROM EGO_PUB_WS_CONFIG
3301 WHERE Parameter_Name = 'PUBLISH_TRANSACTION_ATTRS'
3302 AND session_id = p_session_id;
3303
3304 -- Validate the List of TAs provided only if publishing TAs
3305 IF (v_publish_tas = 'Y') THEN
3306 /* Process for Transaction Attributes */
3307
3308 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes')
3309 INTO l_node_exists
3310 FROM EGO_PUB_WS_PARAMS
3311 WHERE session_id = p_session_id;
3312
3313
3314 IF (l_node_exists = 1) THEN -- (l_node_exists = 1) for list of pub udags
3315 SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes/AttributeId') ,
3316 existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes/AttributeName')
3317 INTO l_node_exists_ta_id, l_node_exists_ta_name
3318 FROM EGO_PUB_WS_PARAMS
3319 WHERE session_id = p_session_id;
3320
3321 IF (l_node_exists_ta_id <> 0 AND l_node_exists_ta_name <> 0) THEN
3322 SELECT extractValue(ta_attr, '/AttributeId')
3323 BULK COLLECT INTO l_ta_attr_id_tab
3324 FROM (SELECT Value(ta) ta_attr
3325 FROM EGO_PUB_WS_PARAMS i,
3326 TABLE(XMLSequence(
3327 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes/AttributeId') )) ta
3328 WHERE session_id=p_session_id
3329 );
3330
3331 SELECT extractValue(ta_attr, '/AttributeName')
3332 BULK COLLECT INTO l_ta_attr_name_tab
3333 FROM (SELECT Value(ta) ta_attr
3334 FROM EGO_PUB_WS_PARAMS i,
3335 TABLE(XMLSequence(
3336 extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes/AttributeName') )) ta
3337 WHERE session_id=p_session_id
3338 );
3339
3340 IF ((l_ta_attr_id_tab.Count = l_ta_attr_name_tab.Count) AND l_ta_attr_id_tab.Count > 0 AND l_ta_attr_name_tab.Count > 0 )
3341 THEN
3342 FOR i IN 1..l_ta_attr_name_tab.Count
3343 LOOP
3344
3345 IF (l_ta_attr_name_tab(i) IS NOT NULL )
3346 THEN
3347 BEGIN
3348 /* Need to validate for valid input values */
3349 SELECT ATTR_ID
3350 INTO l_ta_attr_id_tab(i)
3351 FROM ego_attrs_v
3352 WHERE ATTR_NAME = l_ta_attr_name_tab(i)
3353 AND ATTR_GROUP_TYPE = 'EGO_ITEM_TRANS_ATTR_GROUP';
3354
3355 EXCEPTION
3356 WHEN No_Data_Found THEN
3357 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
3358 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
3359 WHERE session_id = p_session_id;
3360
3361 -- Throw error : Attribute Id given is wrong.????? or will ignore the attribute
3362 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
3363 p_odi_session_id => p_odi_session_id,
3364 p_input_id => p_index,
3365 p_param_name => 'AttributeName',
3366 p_param_value => l_ta_attr_name_tab(i) );
3367
3368 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
3369 p_odi_session_id => p_odi_session_id,
3370 p_input_id => p_index,
3371 p_err_code => 'EGO_INVALID_TA_NAME',
3372 p_err_message => 'Invalid Transaction Attribute Name');
3373
3374 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3375 VALUES (p_session_id,p_odi_session_id,'PUBLISH_TA_ID',2,NULL, l_ta_attr_name_tab(i), NULL , SYSDATE, G_CURRENT_USER_ID);
3376 l_ta_attr_id_tab(i) := NULL;
3377 l_ta_attr_name_tab(i) := NULL;
3378
3379 WHEN Too_Many_Rows THEN
3380 FOR j IN ( SELECT ATTR_ID
3381 FROM ego_attrs_v
3382 WHERE ATTR_NAME = l_ta_attr_name_tab(i)
3383 AND ATTR_GROUP_TYPE = 'EGO_ITEM_TRANS_ATTR_GROUP')
3384 LOOP
3385 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3386 VALUES (p_session_id,p_odi_session_id,'PUBLISH_TA_ID',1,NULL, NULL,j.ATTR_ID, SYSDATE, G_CURRENT_USER_ID);
3387 END LOOP;
3388
3389 l_ta_attr_id_tab(i) := NULL;
3390 l_ta_attr_name_tab(i) := NULL;
3391
3392 END;
3393 ELSIF (l_ta_attr_id_tab(i) IS NOT NULL) THEN
3394 BEGIN
3395 /* Need to validate for valid input values */
3396 SELECT ATTR_ID
3397 INTO l_ta_attr_id_tab(i)
3398 FROM ego_attrs_v
3399 WHERE ATTR_ID = l_ta_attr_id_tab(i)
3400 AND ATTR_GROUP_TYPE = 'EGO_ITEM_TRANS_ATTR_GROUP';
3401
3402 EXCEPTION
3403 WHEN No_Data_Found THEN
3404 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
3405 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
3406 WHERE session_id = p_session_id;
3407
3408 -- Throw error : Attribute Id given is wrong.????? or will ignore the attribute
3409 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
3410 p_odi_session_id => p_odi_session_id,
3411 p_input_id => p_index,
3412 p_param_name => 'AttributeId',
3413 p_param_value => l_ta_attr_id_tab(i) );
3414
3415 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
3416 p_odi_session_id => p_odi_session_id,
3417 p_input_id => p_index,
3418 p_err_code => 'EGO_INVALID_TA_ID',
3419 p_err_message => 'Invalid Transaction Attribute Id');
3420
3421 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3422 VALUES (p_session_id,p_odi_session_id,'PUBLISH_TA_ID',2,NULL, l_ta_attr_id_tab(i), NULL , SYSDATE, G_CURRENT_USER_ID);
3423 l_ta_attr_id_tab(i) := NULL;
3424 l_ta_attr_name_tab(i) := NULL;
3425 END;
3426 END IF;
3427
3428 IF (l_ta_attr_id_tab(i) IS NOT NULL)
3429 THEN
3430 INSERT INTO EGO_PUB_WS_CONFIG ( session_id,odi_session_id,Parameter_Name,Data_Type,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
3431 VALUES (p_session_id,p_odi_session_id,'PUBLISH_TA_ID',1,NULL, NULL, l_ta_attr_id_tab(i), SYSDATE, G_CURRENT_USER_ID);
3432 END IF;
3433
3434 END LOOP;
3435 END IF;
3436 END IF; -- end of (l_node_exists_ta_id <> 0 AND l_node_exists_ag_name <> 0)
3437 END IF; -- end of (l_node_exists = 1) for list of pub TAs
3438 END IF; -- end of (v_publish_tas = 'Y')
3439
3440 EXCEPTION
3441 WHEN OTHERS THEN
3442 RAISE;
3443 END process_configurations;
3444
3445 PROCEDURE process_non_batch_flow ( p_session_id IN NUMBER,
3446 p_odi_session_id IN NUMBER,
3447 p_exists_inv_id IN NUMBER,
3448 p_exists_inv_name IN NUMBER,
3449 p_exists_org_id IN NUMBER,
3450 p_exists_org_code IN NUMBER,
3451 p_exists_rev_id IN NUMBER,
3452 p_exists_revision IN NUMBER,
3453 p_exists_rev_date IN NUMBER ,
3454 p_mode OUT NOCOPY VARCHAR2
3455 )
3456 IS
3457 l_inv_id NUMBER := -1;
3458 l_segments_provided BOOLEAN := FALSE;
3459 l_org_id NUMBER := -1;
3460 l_org_code VARCHAR2(10) := NULL;
3461 l_rev_id NUMBER := -1;
3462 l_revision VARCHAR2(10) := NULL;
3463 l_rev_date DATE := NULL;
3464 l_exists_items_list NUMBER;
3465
3466 l_segment_1 mtl_system_items_b.segment1%TYPE;
3467 l_segment_2 mtl_system_items_b.segment2%TYPE;
3468 l_segment_3 mtl_system_items_b.segment3%TYPE;
3469 l_segment_4 mtl_system_items_b.segment4%TYPE;
3470 l_segment_5 mtl_system_items_b.segment5%TYPE;
3471 l_segment_6 mtl_system_items_b.segment6%TYPE;
3472 l_segment_7 mtl_system_items_b.segment7%TYPE;
3473 l_segment_8 mtl_system_items_b.segment8%TYPE;
3474 l_segment_9 mtl_system_items_b.segment9%TYPE;
3475 l_segment_10 mtl_system_items_b.segment10%TYPE;
3476 l_segment_11 mtl_system_items_b.segment11%TYPE;
3477 l_segment_12 mtl_system_items_b.segment12%TYPE;
3478 l_segment_13 mtl_system_items_b.segment13%TYPE;
3479 l_segment_14 mtl_system_items_b.segment14%TYPE;
3480 l_segment_15 mtl_system_items_b.segment15%TYPE;
3481 l_segment_16 mtl_system_items_b.segment16%TYPE;
3482 l_segment_17 mtl_system_items_b.segment17%TYPE;
3483 l_segment_18 mtl_system_items_b.segment18%TYPE;
3484 l_segment_19 mtl_system_items_b.segment19%TYPE;
3485 l_segment_20 mtl_system_items_b.segment20%TYPE;
3486
3487 l_item_id_tab dbms_sql.VARCHAR2_table;
3488 l_org_id_tab dbms_sql.VARCHAR2_table;
3489 l_org_code_tab dbms_sql.VARCHAR2_table;
3490 l_rev_id_tab dbms_sql.VARCHAR2_table;
3491 l_rev_tab dbms_sql.VARCHAR2_table;
3492
3493 l_list_inv_id NUMBER;
3494 l_list_org_id NUMBER;
3495 l_list_rev_id NUMBER;
3496 l_list_rev_date DATE;
3497
3498 l_segment_1_tab dbms_sql.VARCHAR2_table;
3499 l_segment_2_tab dbms_sql.VARCHAR2_table;
3500 l_segment_3_tab dbms_sql.VARCHAR2_table;
3501 l_segment_4_tab dbms_sql.VARCHAR2_table;
3502 l_segment_5_tab dbms_sql.VARCHAR2_table;
3503 l_segment_6_tab dbms_sql.VARCHAR2_table;
3504 l_segment_7_tab dbms_sql.VARCHAR2_table;
3505 l_segment_8_tab dbms_sql.VARCHAR2_table;
3506 l_segment_9_tab dbms_sql.VARCHAR2_table;
3507 l_segment_10_tab dbms_sql.VARCHAR2_table;
3508 l_segment_11_tab dbms_sql.VARCHAR2_table;
3509 l_segment_12_tab dbms_sql.VARCHAR2_table;
3510 l_segment_13_tab dbms_sql.VARCHAR2_table;
3511 l_segment_14_tab dbms_sql.VARCHAR2_table;
3512 l_segment_15_tab dbms_sql.VARCHAR2_table;
3513 l_segment_16_tab dbms_sql.VARCHAR2_table;
3514 l_segment_17_tab dbms_sql.VARCHAR2_table;
3515 l_segment_18_tab dbms_sql.VARCHAR2_table;
3516 l_segment_19_tab dbms_sql.VARCHAR2_table;
3517 l_segment_20_tab dbms_sql.VARCHAR2_table;
3518
3519 v_is_valid_item BOOLEAN;
3520 v_is_valid_org BOOLEAN;
3521 v_is_valid_rev BOOLEAN;
3522 l_list_segments BOOLEAN;
3523
3524 l_inv_item_id NUMBER;
3525 l_revision_id NUMBER;
3526 l_revision_date DATE;
3527 l_organization_id NUMBER;
3528 l_invalid_items_count NUMBER; -- Bug 12359959
3529 l_seg1_count NUMBER;
3530
3531 BEGIN
3532
3533 IF((p_exists_inv_id = 1 OR p_exists_inv_name = 1) AND
3534 (p_exists_org_id = 1 OR p_exists_org_code = 1) AND
3535 (p_exists_rev_id = 1 OR p_exists_revision = 1 OR p_exists_rev_date = 1)
3536 )
3537 THEN
3538 -- HMDM Flow
3539 IF (p_exists_inv_id = 1) THEN
3540 SELECT Nvl(extractValue(xmlcontent, '/itemQueryParameters/InventoryItemId'), -1)
3541 INTO l_inv_id
3542 FROM EGO_PUB_WS_PARAMS
3543 WHERE session_id = p_session_id;
3544 END IF; -- end of p_exists_inv_id = 1
3545
3546 IF (p_exists_inv_name = 1) THEN
3547
3548 SELECT extractValue(segments, '/InventoryItemName/Segment1'), extractValue(segments, '/InventoryItemName/Segment2') ,
3549 extractValue(segments, '/InventoryItemName/Segment3'), extractValue(segments, '/InventoryItemName/Segment4') ,
3550 extractValue(segments, '/InventoryItemName/Segment5'), extractValue(segments, '/InventoryItemName/Segment6') ,
3551 extractValue(segments, '/InventoryItemName/Segment7'), extractValue(segments, '/InventoryItemName/Segment8') ,
3552 extractValue(segments, '/InventoryItemName/Segment9'), extractValue(segments, '/InventoryItemName/Segment10') ,
3553 extractValue(segments, '/InventoryItemName/Segment11'), extractValue(segments, '/InventoryItemName/Segment12') ,
3554 extractValue(segments, '/InventoryItemName/Segment13'), extractValue(segments, '/InventoryItemName/Segment14') ,
3555 extractValue(segments, '/InventoryItemName/Segment15'), extractValue(segments, '/InventoryItemName/Segment16') ,
3556 extractValue(segments, '/InventoryItemName/Segment17'), extractValue(segments, '/InventoryItemName/Segment18') ,
3557 extractValue(segments, '/InventoryItemName/Segment19'), extractValue(segments, '/InventoryItemName/Segment20')
3558 INTO l_segment_1, l_segment_2 ,
3559 l_segment_3, l_segment_4 ,
3560 l_segment_5, l_segment_6 ,
3561 l_segment_7, l_segment_8 ,
3562 l_segment_9, l_segment_10 ,
3563 l_segment_11, l_segment_12 ,
3564 l_segment_13, l_segment_14 ,
3565 l_segment_15, l_segment_16 ,
3566 l_segment_17, l_segment_18 ,
3567 l_segment_19, l_segment_20
3568 FROM (SELECT Value(itemName) segments
3569 FROM EGO_PUB_WS_PARAMS i,
3570 TABLE(XMLSequence(extract(i.xmlcontent, '/itemQueryParameters/InventoryItemName'))) itemName
3571 WHERE session_id = p_session_id
3572 );
3573
3574 IF ( l_segment_1 IS NULL AND l_segment_2 IS NULL AND
3575 l_segment_3 IS NULL AND l_segment_4 IS NULL AND
3576 l_segment_5 IS NULL AND l_segment_6 IS NULL AND
3577 l_segment_7 IS NULL AND l_segment_8 IS NULL AND
3578 l_segment_9 IS NULL AND l_segment_10 IS NULL AND
3579 l_segment_11 IS NULL AND l_segment_12 IS NULL AND
3580 l_segment_13 IS NULL AND l_segment_14 IS NULL AND
3581 l_segment_15 IS NULL AND l_segment_16 IS NULL AND
3582 l_segment_17 IS NULL AND l_segment_18 IS NULL AND
3583 l_segment_19 IS NULL AND l_segment_20 IS NULL ) THEN
3584
3585 l_segments_provided := FALSE;
3586 ELSE
3587 l_segments_provided := TRUE;
3588 END IF;
3589 END IF; -- end of p_exists_inv_name = 1
3590
3591 IF (l_inv_id = -1 AND l_segments_provided = FALSE) THEN
3592
3593 -- If Inventory Item Id or Item Name are not given then Check for List Flow
3594 SELECT existsNode(xmlcontent, '/itemQueryParameters/ItemsList')
3595 INTO l_exists_items_list
3596 FROM EGO_PUB_WS_PARAMS
3597 WHERE session_id = p_session_id;
3598
3599 IF l_exists_items_list = 1
3600 THEN
3601 p_mode:= 'LIST';
3602 ELSE
3603 p_mode := 'MODE';
3604 END IF; -- l_exists_items_list = 1
3605
3606 ELSE
3607 -- If Inventory Item Id or Item Name are given then consider as HMDM Flow
3608 p_mode := 'HMDM';
3609
3610 IF (p_exists_org_id = 1) THEN
3611 SELECT Nvl(extractValue(xmlcontent, '/itemQueryParameters/OrganizationId'), -1)
3612 INTO l_org_id
3613 FROM EGO_PUB_WS_PARAMS
3614 WHERE session_id = p_session_id;
3615 END IF; -- end of p_exists_org_id = 1
3616
3617 IF (p_exists_org_code = 1) THEN
3618 SELECT extractValue(xmlcontent, '/itemQueryParameters/OrganizationCode')
3619 INTO l_org_code
3620 FROM EGO_PUB_WS_PARAMS
3621 WHERE session_id = p_session_id;
3622 END IF; -- end of p_exists_org_code = 1
3623
3624 IF (l_org_id = -1 AND l_org_code IS NULL) THEN
3625 -- error
3626 RAISE e_no_org_details;
3627 ELSE
3628 IF (p_exists_rev_id = 1) THEN
3629 SELECT Nvl(extractValue(xmlcontent, '/itemQueryParameters/RevisionId'), -1)
3630 INTO l_rev_id
3631 FROM EGO_PUB_WS_PARAMS
3632 WHERE session_id = p_session_id;
3633 END IF; -- end of (p_exists_rev_id = 1)
3634
3635 IF (p_exists_revision = 1) THEN
3636 SELECT extractValue(xmlcontent, '/itemQueryParameters/Revision')
3637 INTO l_revision
3638 FROM EGO_PUB_WS_PARAMS
3639 WHERE session_id = p_session_id;
3640 END IF; -- end of (p_exists_revision = 1)
3641
3642 IF (p_exists_rev_date = 1) THEN
3643 SELECT To_Date(extractValue(xmlcontent, '/itemQueryParameters/RevisionDate'), 'YYYY.MM.DD HH24:MI:SS')
3644 INTO l_rev_date
3645 FROM EGO_PUB_WS_PARAMS
3646 WHERE session_id = p_session_id;
3647 END IF; -- end of (p_exists_rev_date = 1)
3648
3649 IF (l_rev_id = -1 AND l_revision IS NULL AND l_rev_date IS NULL) THEN
3650 -- error
3651 RAISE e_no_rev_details;
3652 END IF; -- end of (l_rev_id = -1 AND l_revision IS NULL AND l_rev_date IS NULL)
3653
3654 END IF; -- end of (l_org_id = -1 AND l_org_code IS NULL)
3655
3656 -- validate for the inputs (inv item , org, rev)
3657 v_is_valid_org := Validate_organization(p_session_id => p_session_id,
3658 p_odi_session_id => p_odi_session_id,
3659 p_org_id => l_org_id,
3660 p_org_code => l_org_code,
3661 p_index => 1,
3662 p_organization_id => l_organization_id
3663 );
3664 IF (v_is_valid_org) THEN
3665 v_is_valid_item := Validate_Item(p_session_id => p_session_id,
3666 p_odi_session_id => p_odi_session_id,
3667 p_inv_id => l_inv_id ,
3668 p_org_id => l_organization_id ,
3669 p_segment1 => l_segment_1 ,
3670 p_segment2 => l_segment_2 ,
3671 p_segment3 => l_segment_3 ,
3672 p_segment4 => l_segment_4 ,
3673 p_segment5 => l_segment_5 ,
3674 p_segment6 => l_segment_6 ,
3675 p_segment7 => l_segment_7 ,
3676 p_segment8 => l_segment_8 ,
3677 p_segment9 => l_segment_9 ,
3678 p_segment10 => l_segment_10 ,
3679 p_segment11 => l_segment_11 ,
3680 p_segment12 => l_segment_12 ,
3681 p_segment13 => l_segment_13 ,
3682 p_segment14 => l_segment_14 ,
3683 p_segment15 => l_segment_15 ,
3684 p_segment16 => l_segment_16 ,
3685 p_segment17 => l_segment_17 ,
3686 p_segment18 => l_segment_18 ,
3687 p_segment19 => l_segment_19 ,
3688 p_segment20 => l_segment_20 ,
3689 p_index => 1,
3690 p_inv_item_id => l_inv_item_id
3691 ) ;
3692
3693 IF (v_is_valid_item) THEN
3694 v_is_valid_rev := validate_revision_details (p_session_id => p_session_id,
3695 p_odi_session_id => p_odi_session_id,
3696 p_inv_id => l_inv_item_id,
3697 p_org_id => l_organization_id ,
3698 p_rev_id => l_rev_id,
3699 p_revision => l_revision,
3700 p_rev_date => l_rev_date,
3701 p_index => 1,
3702 p_revision_id => l_revision_id,
3703 p_revision_date => l_revision_date
3704 ) ;
3705
3706 IF (v_is_valid_rev) THEN
3707 POPULATE_REVISION_DETAILS(p_session_id ,
3708 p_odi_session_id ,
3709 p_rev_id => l_rev_id ,
3710 p_revision => l_revision ,
3711 p_rev_date => l_rev_date ,
3712 p_index => 1);
3713
3714 INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value, pk4_value, pk5_value, SEQUENCE_NUMBER)
3715 VALUES (p_session_id,p_odi_session_id,'ITEM',l_inv_item_id,l_organization_id,l_revision_id, 1, To_Char(l_revision_date,'YYYY.MM.DD HH24:MI:SS'), 1);
3716 END IF;
3717 ELSE
3718 POPULATE_REVISION_DETAILS(p_session_id ,
3719 p_odi_session_id ,
3720 p_rev_id => l_rev_id ,
3721 p_revision => l_revision ,
3722 p_rev_date => l_rev_date ,
3723 p_index => 1);
3724 END IF; --end of (v_is_valid_item)
3725 ELSE
3726 IF (l_inv_id <> -1) THEN
3727 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
3728 p_odi_session_id => p_odi_session_id,
3729 p_input_id => 1,
3730 p_param_name => 'InventoryItemId',
3731 p_param_value => l_inv_id );
3732 ELSE
3733 POPULATE_SEGMENTS(p_session_id ,
3734 p_odi_session_id ,
3735 p_segment1 => l_segment_1 ,
3736 p_segment2 => l_segment_2 ,
3737 p_segment3 => l_segment_3 ,
3738 p_segment4 => l_segment_4 ,
3739 p_segment5 => l_segment_5 ,
3740 p_segment6 => l_segment_6 ,
3741 p_segment7 => l_segment_7 ,
3742 p_segment8 => l_segment_8 ,
3743 p_segment9 => l_segment_9 ,
3744 p_segment10 => l_segment_10 ,
3745 p_segment11 => l_segment_11 ,
3746 p_segment12 => l_segment_12 ,
3747 p_segment13 => l_segment_13 ,
3748 p_segment14 => l_segment_14 ,
3749 p_segment15 => l_segment_15 ,
3750 p_segment16 => l_segment_16 ,
3751 p_segment17 => l_segment_17 ,
3752 p_segment18 => l_segment_18 ,
3753 p_segment19 => l_segment_19 ,
3754 p_segment20 => l_segment_20 ,
3755 p_index => 1 );
3756
3757 END IF;
3758
3759 POPULATE_REVISION_DETAILS(p_session_id ,
3760 p_odi_session_id ,
3761 p_rev_id => l_rev_id ,
3762 p_revision => l_revision ,
3763 p_rev_date => l_rev_date ,
3764 p_index => 1);
3765 END IF; -- end of (v_is_valid_org)
3766 END IF; -- end of (l_inv_id = -1 AND l_segments_provided = FALSE)
3767
3768 ELSE
3769 -- List Flow
3770 SELECT existsNode(xmlcontent, '/itemQueryParameters/ItemsList')
3771 INTO l_exists_items_list
3772 FROM EGO_PUB_WS_PARAMS
3773 WHERE session_id = p_session_id;
3774
3775 IF l_exists_items_list = 1
3776 THEN
3777 -- IN LIST MODE
3778 p_mode:= 'LIST';
3779 ELSE
3780 p_mode := 'MODE';
3781 END IF;
3782
3783 END IF; /* end of ((p_exists_inv_id = 1 OR p_exists_inv_name = 1) AND
3784 (p_exists_org_id = 1 OR p_exists_org_code = 1) AND
3785 (p_exists_rev_id = 1 OR p_exists_revision = 1 OR p_exists_rev_date = 1))
3786 */
3787 IF (p_mode = 'LIST') THEN
3788
3789 -- Bug 12359959 : Start
3790 /*
3791 SELECT Nvl(extractValue(item_id, '/InventoryItemId'),-1)
3792 BULK COLLECT INTO l_item_id_tab
3793 FROM (SELECT Value(itemId) item_id
3794 FROM EGO_PUB_WS_PARAMS i,
3795 TABLE(XMLSequence(
3796 extract(i.xmlcontent, '/itemQueryParameters/ItemsList/InventoryItemId') )) itemId
3797 WHERE session_id=p_session_id
3798 );
3799
3800 BEGIN
3801 SELECT extractValue(segments, '/InventoryItemName/Segment1'), extractValue(segments, '/InventoryItemName/Segment2') ,
3802 extractValue(segments, '/InventoryItemName/Segment3'), extractValue(segments, '/InventoryItemName/Segment4') ,
3803 extractValue(segments, '/InventoryItemName/Segment5'), extractValue(segments, '/InventoryItemName/Segment6') ,
3804 extractValue(segments, '/InventoryItemName/Segment7'), extractValue(segments, '/InventoryItemName/Segment8') ,
3805 extractValue(segments, '/InventoryItemName/Segment9'), extractValue(segments, '/InventoryItemName/Segment10') ,
3806 extractValue(segments, '/InventoryItemName/Segment11'), extractValue(segments, '/InventoryItemName/Segment12') ,
3807 extractValue(segments, '/InventoryItemName/Segment13'), extractValue(segments, '/InventoryItemName/Segment14') ,
3808 extractValue(segments, '/InventoryItemName/Segment15'), extractValue(segments, '/InventoryItemName/Segment16') ,
3809 extractValue(segments, '/InventoryItemName/Segment17'), extractValue(segments, '/InventoryItemName/Segment18') ,
3810 extractValue(segments, '/InventoryItemName/Segment19'), extractValue(segments, '/InventoryItemName/Segment20')
3811 BULK COLLECT INTO
3812 l_segment_1_tab, l_segment_2_tab,
3813 l_segment_3_tab, l_segment_4_tab,
3814 l_segment_5_tab, l_segment_6_tab,
3815 l_segment_7_tab, l_segment_8_tab,
3816 l_segment_9_tab, l_segment_10_tab,
3817 l_segment_11_tab, l_segment_12_tab,
3818 l_segment_13_tab, l_segment_14_tab,
3819 l_segment_15_tab, l_segment_16_tab,
3820 l_segment_17_tab, l_segment_18_tab,
3821 l_segment_19_tab, l_segment_20_tab
3822 FROM (SELECT Value(itemName) segments
3823 FROM EGO_PUB_WS_PARAMS i,
3824 TABLE(XMLSequence(extract(i.xmlcontent, '/itemQueryParameters/ItemsList/InventoryItemName'))) itemName
3825 WHERE session_id = p_session_id
3826 );
3827 IF l_segment_1_tab.Count > 0 THEN
3828 l_list_segments := TRUE;
3829 ELSE
3830 l_list_segments := FALSE;
3831 END IF;
3832 EXCEPTION
3833 WHEN OTHERS THEN
3834 l_list_segments := FALSE;
3835 END;
3836
3837
3838 SELECT Nvl(extractValue(org_id, '/OrganizationId'), -1)
3839 BULK COLLECT INTO l_org_id_tab
3840 FROM (SELECT Value(orgId) org_id
3841 FROM EGO_PUB_WS_PARAMS i,
3842 TABLE(XMLSequence(
3843 extract(i.xmlcontent, '/itemQueryParameters/ItemsList/OrganizationId') )) orgId
3844 WHERE session_id=p_session_id
3845 );
3846
3847 SELECT extractValue(org_code, '/OrganizationCode')
3848 BULK COLLECT INTO l_org_code_tab
3849 FROM (SELECT Value(orgCode) org_code
3850 FROM EGO_PUB_WS_PARAMS i,
3851 TABLE(XMLSequence(
3852 extract(i.xmlcontent, '/itemQueryParameters/ItemsList/OrganizationCode') )) orgCode
3853 WHERE session_id=p_session_id
3854 );
3855
3856 SELECT Nvl(extractValue(rev_id, '/RevisionId'), -1)
3857 BULK COLLECT INTO l_rev_id_tab
3858 FROM (SELECT Value(revId) rev_id
3859 FROM EGO_PUB_WS_PARAMS i,
3860 TABLE(XMLSequence(
3861 extract(i.xmlcontent, '/itemQueryParameters/ItemsList/RevisionId') )) revId
3862 WHERE session_id=p_session_id
3863 );
3864
3865 SELECT extractValue(rev_lable, '/Revision')
3866 BULK COLLECT INTO l_rev_tab
3867 FROM (SELECT Value(rev) rev_lable
3868 FROM EGO_PUB_WS_PARAMS i,
3869 TABLE(XMLSequence(
3870 extract(i.xmlcontent, '/itemQueryParameters/ItemsList/Revision') )) rev
3871 WHERE session_id=p_session_id
3872 );
3873
3874 l_item_ids_count := 0;
3875 FOR id IN 1..l_item_id_tab.Count LOOP
3876 IF (l_item_id_tab(id) = -1) THEN
3877 l_item_ids_count := l_item_ids_count + 1;
3878 END IF;
3879 END LOOP;
3880
3881 IF (l_item_ids_count = l_item_id_tab.Count) THEN
3882 IF (l_list_segments = FALSE) THEN
3883 p_mode := 'MODE';
3884 RAISE e_invalid_invocation_mode;
3885 ELSE
3886 l_seg1_count := 0;
3887 FOR seg in 1..l_segment_1_tab.Count LOOP
3888 IF (l_segment_1_tab(seg) IS NULL) THEN
3889 l_seg1_count := l_seg1_count + 1;
3890 END IF;
3891 END LOOP;
3892
3893 IF (l_seg1_count = l_segment_1_tab.Count) THEN
3894 p_mode := 'MODE';
3895 RAISE e_invalid_invocation_mode;
3896 END IF;
3897 END IF;
3898 END IF;
3899 */
3900
3901 SELECT
3902 Decode(existsNode(items_list, '/ItemsList/InventoryItemId'), 1, Nvl(extractValue(items_list, '/ItemsList/InventoryItemId'), -1), 0, -1),
3903 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment1'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment1'), 0, NULL),
3904 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment2'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment2'), 0, NULL),
3905 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment3'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment3'), 0, NULL),
3906 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment4'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment4'), 0, NULL),
3907 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment5'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment5'), 0, NULL),
3908 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment6'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment6'), 0, NULL),
3909 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment7'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment7'), 0, NULL),
3910 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment8'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment8'), 0, NULL),
3911 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment9'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment9'), 0, NULL),
3912 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment10'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment10'), 0, NULL),
3913 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment11'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment11'), 0, NULL),
3914 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment12'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment12'), 0, NULL),
3915 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment13'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment13'), 0, NULL),
3916 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment14'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment14'), 0, NULL),
3917 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment15'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment15'), 0, NULL),
3918 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment16'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment16'), 0, NULL),
3919 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment17'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment17'), 0, NULL),
3920 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment18'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment18'), 0, NULL),
3921 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment19'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment19'), 0, NULL),
3922 Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment20'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment20'), 0, NULL),
3923 Decode(existsNode(items_list, '/ItemsList/OrganizationId'), 1, Nvl(extractValue(items_list, '/ItemsList/OrganizationId'), -1), 0, -1),
3924 Decode(existsNode(items_list, '/ItemsList/OrganizationCode'), 1, extractValue(items_list, '/ItemsList/OrganizationCode'), 0, NULL),
3925 Decode(existsNode(items_list, '/ItemsList/RevisionId'), 1, Nvl(extractValue(items_list, '/ItemsList/RevisionId'), -1), 0, -1),
3926 Decode(existsNode(items_list, '/ItemsList/Revision'), 1, extractValue(items_list, '/ItemsList/Revision'), 0, NULL)
3927 BULK COLLECT INTO l_item_id_tab,
3928 l_segment_1_tab, l_segment_2_tab,
3929 l_segment_3_tab, l_segment_4_tab,
3930 l_segment_5_tab, l_segment_6_tab,
3931 l_segment_7_tab, l_segment_8_tab,
3932 l_segment_9_tab, l_segment_10_tab,
3933 l_segment_11_tab, l_segment_12_tab,
3934 l_segment_13_tab, l_segment_14_tab,
3935 l_segment_15_tab, l_segment_16_tab,
3936 l_segment_17_tab, l_segment_18_tab,
3937 l_segment_19_tab, l_segment_20_tab,
3938 l_org_id_tab,
3939 l_org_code_tab,
3940 l_rev_id_tab,
3941 l_rev_tab
3942 FROM ( SELECT Value(itemsList) items_list
3943 FROM EGO_PUB_WS_PARAMS i,
3944 TABLE(XMLSequence(extract(i.xmlcontent, '/itemQueryParameters/ItemsList') )) itemsList
3945 WHERE session_id = p_session_id
3946 );
3947
3948 l_invalid_items_count := 0;
3949
3950 FOR id IN 1..l_item_id_tab.Count LOOP
3951 IF (l_item_id_tab(id) = -1 AND
3952 l_segment_1_tab(id) IS NULL AND l_segment_2_tab(id) IS NULL AND
3953 l_segment_3_tab(id) IS NULL AND l_segment_4_tab(id) IS NULL AND
3954 l_segment_5_tab(id) IS NULL AND l_segment_6_tab(id) IS NULL AND
3955 l_segment_7_tab(id) IS NULL AND l_segment_8_tab(id) IS NULL AND
3956 l_segment_9_tab(id) IS NULL AND l_segment_10_tab(id) IS NULL AND
3957 l_segment_11_tab(id) IS NULL AND l_segment_12_tab(id) IS NULL AND
3958 l_segment_13_tab(id) IS NULL AND l_segment_14_tab(id) IS NULL AND
3959 l_segment_15_tab(id) IS NULL AND l_segment_16_tab(id) IS NULL AND
3960 l_segment_17_tab(id) IS NULL AND l_segment_18_tab(id) IS NULL AND
3961 l_segment_19_tab(id) IS NULL AND l_segment_20_tab(id) IS NULL) THEN
3962 l_invalid_items_count := l_invalid_items_count + 1;
3963 END IF;
3964 END LOOP;
3965
3966 -- If the user didn't provide item details at all, then throw invalid invocation mode.
3967 IF (l_invalid_items_count = l_item_id_tab.Count) THEN
3968 p_mode := 'MODE';
3969 RAISE e_invalid_invocation_mode;
3970 END IF;
3971 -- Bug 12359959 : End
3972
3973 -- Bug 8659192 : Start
3974 IF (p_exists_rev_date = 1) THEN
3975 SELECT To_Date(extractValue(xmlcontent, '/itemQueryParameters/RevisionDate'),'YYYY.MM.DD HH24:MI:SS')
3976 INTO l_rev_date
3977 FROM EGO_PUB_WS_PARAMS
3978 WHERE session_id = p_session_id;
3979 END IF; -- end of (p_exists_rev_date = 1)
3980 -- Bug 8659192 : End
3981
3982 FOR i in 1..l_item_id_tab.Count LOOP
3983 l_list_inv_id := l_item_id_tab(i);
3984 l_list_org_id := l_org_id_tab(i);
3985 l_list_rev_id := l_rev_id_tab(i);
3986
3987 -- validate for the inputs (inv item , org, rev)
3988 v_is_valid_org := Validate_organization(p_session_id => p_session_id,
3989 p_odi_session_id => p_odi_session_id,
3990 p_org_id => l_list_org_id,
3991 p_org_code => l_org_code_tab(i),
3992 p_index => i,
3993 p_organization_id => l_organization_id
3994 );
3995
3996 IF (v_is_valid_org) THEN
3997 -- Need to do for segemnts also , do the validation and log the errors.
3998 --validate_items(l_item_id_tab(i), l_org_id_tab(i));
3999 -- Bug 12359959 : Start
4000 /*
4001 IF (l_list_segments = FALSE) THEN
4002 v_is_valid_item := Validate_Item(p_session_id => p_session_id,
4003 p_odi_session_id => p_odi_session_id,
4004 p_inv_id => l_list_inv_id ,
4005 p_org_id => l_organization_id ,
4006 p_segment1 => NULL,
4007 p_segment2 => NULL,
4008 p_segment3 => NULL,
4009 p_segment4 => NULL,
4010 p_segment5 => NULL,
4011 p_segment6 => NULL,
4012 p_segment7 => NULL,
4013 p_segment8 => NULL,
4014 p_segment9 => NULL,
4015 p_segment10 => NULL,
4016 p_segment11 => NULL,
4017 p_segment12 => NULL,
4018 p_segment13 => NULL,
4019 p_segment14 => NULL,
4020 p_segment15 => NULL,
4021 p_segment16 => NULL ,
4022 p_segment17 => NULL ,
4023 p_segment18 => NULL ,
4024 p_segment19 => NULL ,
4025 p_segment20 => NULL ,
4026 p_index => i,
4027 p_inv_item_id => l_inv_item_id
4028 ) ;
4029
4030 */
4031 -- For any one record in list mode, if item details are not given then throw EGO_NO_ITEM_DETAILS.
4032 IF (l_item_id_tab(i) = -1 AND
4033 l_segment_1_tab(i) IS NULL AND l_segment_2_tab(i) IS NULL AND
4034 l_segment_3_tab(i) IS NULL AND l_segment_4_tab(i) IS NULL AND
4035 l_segment_5_tab(i) IS NULL AND l_segment_6_tab(i) IS NULL AND
4036 l_segment_7_tab(i) IS NULL AND l_segment_8_tab(i) IS NULL AND
4037 l_segment_9_tab(i) IS NULL AND l_segment_10_tab(i) IS NULL AND
4038 l_segment_11_tab(i) IS NULL AND l_segment_12_tab(i) IS NULL AND
4039 l_segment_13_tab(i) IS NULL AND l_segment_14_tab(i) IS NULL AND
4040 l_segment_15_tab(i) IS NULL AND l_segment_16_tab(i) IS NULL AND
4041 l_segment_17_tab(i) IS NULL AND l_segment_18_tab(i) IS NULL AND
4042 l_segment_19_tab(i) IS NULL AND l_segment_20_tab(i) IS NULL)
4043 THEN
4044 v_is_valid_item := FALSE;
4045 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4046 p_odi_session_id => p_odi_session_id,
4047 p_input_id => i,
4048 p_err_code => 'EGO_NO_ITEM_DETAILS',
4049 p_err_message => 'Item details are not provided');
4050 ELSE
4051 v_is_valid_item := Validate_Item(p_session_id => p_session_id,
4052 p_odi_session_id => p_odi_session_id,
4053 p_inv_id => l_list_inv_id ,
4054 p_org_id => l_organization_id ,
4055 p_segment1 => l_segment_1_tab(i),
4056 p_segment2 => l_segment_2_tab(i),
4057 p_segment3 => l_segment_3_tab(i),
4058 p_segment4 => l_segment_4_tab(i),
4059 p_segment5 => l_segment_5_tab(i),
4060 p_segment6 => l_segment_6_tab(i),
4061 p_segment7 => l_segment_7_tab(i),
4062 p_segment8 => l_segment_8_tab(i),
4063 p_segment9 => l_segment_9_tab(i),
4064 p_segment10 => l_segment_10_tab(i),
4065 p_segment11 => l_segment_11_tab(i),
4066 p_segment12 => l_segment_12_tab(i),
4067 p_segment13 => l_segment_13_tab(i),
4068 p_segment14 => l_segment_14_tab(i),
4069 p_segment15 => l_segment_15_tab(i),
4070 p_segment16 => l_segment_16_tab(i) ,
4071 p_segment17 => l_segment_17_tab(i) ,
4072 p_segment18 => l_segment_18_tab(i) ,
4073 p_segment19 => l_segment_19_tab(i) ,
4074 p_segment20 => l_segment_20_tab(i) ,
4075 p_index => i,
4076 p_inv_item_id => l_inv_item_id
4077 ) ;
4078 END IF;
4079 IF (v_is_valid_item) THEN
4080 v_is_valid_rev := validate_revision_details(p_session_id => p_session_id,
4081 p_odi_session_id => p_odi_session_id,
4082 p_inv_id => l_inv_item_id,
4083 p_org_id => l_organization_id ,
4084 p_rev_id => l_list_rev_id,
4085 p_revision => l_rev_tab(i),
4086 p_rev_date => l_rev_date, -- Bug 8659192
4087 p_index => i,
4088 p_revision_id => l_revision_id ,
4089 p_revision_date => l_revision_date
4090 ) ;
4091
4092 IF (v_is_valid_rev) THEN
4093 POPULATE_REVISION_DETAILS(p_session_id ,
4094 p_odi_session_id ,
4095 p_rev_id => l_list_rev_id ,
4096 p_revision => l_rev_tab(i) ,
4097 p_rev_date => l_rev_date ,
4098 p_index => i);
4099
4100 -- Bug 8659192 : Insert the Rev Date for the Item, in the column pk5_value
4101 INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value, pk4_value, pk5_value, SEQUENCE_NUMBER)
4102 VALUES (p_session_id,p_odi_session_id,'ITEM',l_inv_item_id,l_organization_id,l_revision_id, i, To_Char(l_revision_date,'YYYY.MM.DD HH24:MI:SS') , i);
4103 END IF;
4104 ELSE
4105 SELECT To_Date(extractValue(xmlcontent, '/itemQueryParameters/RevisionDate'),'YYYY.MM.DD HH24:MI:SS')
4106 INTO l_rev_date
4107 FROM EGO_PUB_WS_PARAMS
4108 WHERE session_id = p_session_id;
4109
4110 POPULATE_REVISION_DETAILS(p_session_id ,
4111 p_odi_session_id ,
4112 p_rev_id => l_list_rev_id ,
4113 p_revision => l_rev_tab(i) ,
4114 p_rev_date => l_rev_date ,
4115 p_index => i);
4116
4117 END IF; -- (v_is_valid_item)
4118
4119 ELSE
4120 IF (l_list_inv_id <> -1) THEN
4121 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4122 p_odi_session_id => p_odi_session_id,
4123 p_input_id => i,
4124 p_param_name => 'InventoryItemId',
4125 p_param_value => l_list_inv_id );
4126 ELSE
4127
4128 POPULATE_SEGMENTS(p_session_id ,
4129 p_odi_session_id ,
4130 p_segment1 => l_segment_1_tab(i),
4131 p_segment2 => l_segment_2_tab(i),
4132 p_segment3 => l_segment_3_tab(i),
4133 p_segment4 => l_segment_4_tab(i),
4134 p_segment5 => l_segment_5_tab(i),
4135 p_segment6 => l_segment_6_tab(i),
4136 p_segment7 => l_segment_7_tab(i),
4137 p_segment8 => l_segment_8_tab(i),
4138 p_segment9 => l_segment_9_tab(i),
4139 p_segment10 => l_segment_10_tab(i),
4140 p_segment11 => l_segment_11_tab(i),
4141 p_segment12 => l_segment_12_tab(i),
4142 p_segment13 => l_segment_13_tab(i),
4143 p_segment14 => l_segment_14_tab(i),
4144 p_segment15 => l_segment_15_tab(i),
4145 p_segment16 => l_segment_16_tab(i) ,
4146 p_segment17 => l_segment_17_tab(i) ,
4147 p_segment18 => l_segment_18_tab(i) ,
4148 p_segment19 => l_segment_19_tab(i) ,
4149 p_segment20 => l_segment_20_tab(i) ,
4150 p_index => i );
4151 END IF;
4152
4153 SELECT To_Date(extractValue(xmlcontent, '/itemQueryParameters/RevisionDate'),'YYYY.MM.DD HH24:MI:SS')
4154 INTO l_rev_date
4155 FROM EGO_PUB_WS_PARAMS
4156 WHERE session_id = p_session_id;
4157
4158 POPULATE_REVISION_DETAILS(p_session_id ,
4159 p_odi_session_id ,
4160 p_rev_id => l_list_rev_id ,
4161 p_revision => l_rev_tab(i) ,
4162 p_rev_date => l_rev_date ,
4163 p_index => i);
4164
4165 END IF; -- (v_is_valid_org)
4166
4167 END LOOP;
4168
4169 END IF;
4170
4171 END process_non_batch_flow;
4172
4173
4174
4175 FUNCTION Validate_Item(p_session_id IN NUMBER,
4176 p_odi_session_id IN NUMBER,
4177 p_inv_id in number,
4178 p_org_id in NUMBER ,
4179 p_segment1 in varchar2,
4180 p_segment2 in varchar2,
4181 p_segment3 in varchar2,
4182 p_segment4 in varchar2,
4183 p_segment5 in varchar2,
4184 p_segment6 in varchar2,
4185 p_segment7 in varchar2,
4186 p_segment8 in varchar2,
4187 p_segment9 in varchar2,
4188 p_segment10 in varchar2,
4189 p_segment11 in varchar2,
4190 p_segment12 in varchar2,
4191 p_segment13 in varchar2,
4192 p_segment14 in varchar2,
4193 p_segment15 in varchar2,
4194 p_segment16 in varchar2,
4195 p_segment17 in varchar2,
4196 p_segment18 in varchar2,
4197 p_segment19 in varchar2,
4198 p_segment20 in varchar2,
4199 p_index in number,
4200 p_inv_item_id OUT NOCOPY number
4201 ) RETURN BOOLEAN
4202 IS
4203
4204 BEGIN
4205 IF (p_inv_id <> -1 AND p_org_id <> -1) THEN
4206
4207 select inventory_item_id
4208 INTO p_inv_item_id
4209 from mtl_system_items_kfv
4210 WHERE organization_id = p_org_id
4211 AND inventory_item_id = p_inv_id;
4212
4213 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4214 p_odi_session_id => p_odi_session_id,
4215 p_input_id => p_index,
4216 p_param_name => 'InventoryItemId',
4217 p_param_value => p_inv_item_id );
4218
4219 ELSE
4220 select inventory_item_id
4221 INTO p_inv_item_id
4222 from mtl_system_items_kfv
4223 WHERE organization_id = p_org_id
4224 AND Nvl(segment1, 0) = Nvl(p_segment1, 0)
4225 AND Nvl(segment2, 0) = Nvl(p_segment2, 0)
4226 AND Nvl(segment3, 0) = Nvl(p_segment3, 0)
4227 AND Nvl(segment4, 0) = Nvl(p_segment4, 0)
4228 AND Nvl(segment5, 0) = Nvl(p_segment5, 0)
4229 AND Nvl(segment6, 0) = Nvl(p_segment6, 0)
4230 AND Nvl(segment7, 0) = Nvl(p_segment7, 0)
4231 AND Nvl(segment8, 0) = Nvl(p_segment8, 0)
4232 AND Nvl(segment9, 0) = Nvl(p_segment9, 0)
4233 AND Nvl(segment10, 0) = Nvl(p_segment10, 0)
4234 AND Nvl(segment11, 0) = Nvl(p_segment11, 0)
4235 AND Nvl(segment12, 0) = Nvl(p_segment12, 0)
4236 AND Nvl(segment13, 0) = Nvl(p_segment13, 0)
4237 AND Nvl(segment14, 0) = Nvl(p_segment14, 0)
4238 AND Nvl(segment15, 0) = Nvl(p_segment15, 0)
4239 AND Nvl(segment16, 0) = Nvl(p_segment16, 0)
4240 AND Nvl(segment17, 0) = Nvl(p_segment17, 0)
4241 AND Nvl(segment18, 0) = Nvl(p_segment18, 0)
4242 AND Nvl(segment19, 0) = Nvl(p_segment19, 0)
4243 AND Nvl(segment20, 0) = Nvl(p_segment20, 0) ;
4244
4245 POPULATE_SEGMENTS(p_session_id ,
4246 p_odi_session_id ,
4247 p_segment1 ,
4248 p_segment2 ,
4249 p_segment3 ,
4250 p_segment4 ,
4251 p_segment5 ,
4252 p_segment6 ,
4253 p_segment7 ,
4254 p_segment8 ,
4255 p_segment9 ,
4256 p_segment10 ,
4257 p_segment11 ,
4258 p_segment12 ,
4259 p_segment13 ,
4260 p_segment14 ,
4261 p_segment15 ,
4262 p_segment16 ,
4263 p_segment17 ,
4264 p_segment18 ,
4265 p_segment19 ,
4266 p_segment20 ,
4267 p_index );
4268
4269 END IF;
4270 RETURN TRUE;
4271 EXCEPTION
4272 WHEN No_Data_Found THEN
4273 IF (p_inv_id <> -1 AND p_org_id <> -1) THEN
4274 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4275 p_odi_session_id => p_odi_session_id,
4276 p_input_id => p_index,
4277 p_param_name => 'InventoryItemId',
4278 p_param_value => p_inv_id );
4279
4280 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4281 p_odi_session_id => p_odi_session_id,
4282 p_input_id => p_index,
4283 p_err_code => 'EGO_INVALID_ITEM_ID',
4284 p_err_message => 'Invalid Inventory Item Id');
4285
4286 ELSE
4287 POPULATE_SEGMENTS(p_session_id ,
4288 p_odi_session_id ,
4289 p_segment1 ,
4290 p_segment2 ,
4291 p_segment3 ,
4292 p_segment4 ,
4293 p_segment5 ,
4294 p_segment6 ,
4295 p_segment7 ,
4296 p_segment8 ,
4297 p_segment9 ,
4298 p_segment10 ,
4299 p_segment11 ,
4300 p_segment12 ,
4301 p_segment13 ,
4302 p_segment14 ,
4303 p_segment15 ,
4304 p_segment16 ,
4305 p_segment17 ,
4306 p_segment18 ,
4307 p_segment19 ,
4308 p_segment20 ,
4309 p_index );
4310
4311 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4312 p_odi_session_id => p_odi_session_id,
4313 p_input_id => p_index,
4314 p_err_code => 'EGO_INVALID_ITEM_NAME',
4315 p_err_message => 'Invalid Inventory Item Name');
4316 END IF;
4317
4318 RETURN FALSE;
4319
4320 WHEN OTHERS THEN
4321 RAISE;
4322
4323 END Validate_Item;
4324
4325
4326 function Validate_organization(p_session_id IN NUMBER,
4327 p_odi_session_id IN NUMBER,
4328 p_org_id in NUMBER ,
4329 p_org_code IN VARCHAR2,
4330 p_index in number,
4331 p_organization_id OUT NOCOPY number
4332 ) RETURN BOOLEAN
4333 IS
4334
4335 BEGIN
4336 IF (p_org_id <> -1) THEN
4337 select organization_id
4338 INTO p_organization_id
4339 from mtl_parameters
4340 WHERE organization_id = p_org_id;
4341
4342 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4343 p_odi_session_id => p_odi_session_id,
4344 p_input_id => p_index,
4345 p_param_name => 'OrganizationId',
4346 p_param_value => p_org_id );
4347 ELSE
4348 select organization_id
4349 INTO p_organization_id
4350 from mtl_parameters
4351 WHERE organization_code = p_org_code;
4352
4353 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4354 p_odi_session_id => p_odi_session_id,
4355 p_input_id => p_index,
4356 p_param_name => 'OrganizationCode',
4357 p_param_value => p_org_code );
4358
4359 END IF;
4360 RETURN TRUE;
4361 EXCEPTION
4362 WHEN No_Data_Found THEN
4363 IF (p_org_id <> -1) THEN
4364 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4365 p_odi_session_id => p_odi_session_id,
4366 p_input_id => p_index,
4367 p_param_name => 'OrganizationId',
4368 p_param_value => p_org_id );
4369
4370 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4371 p_odi_session_id => p_odi_session_id,
4372 p_input_id => p_index,
4373 p_err_code => 'EGO_INVALID_ORG_ID',
4374 p_err_message => 'Invalid Organization Id');
4375 ELSE
4376 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4377 p_odi_session_id => p_odi_session_id,
4378 p_input_id => p_index,
4379 p_param_name => 'OrganizationCode',
4380 p_param_value => p_org_code );
4381
4382 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4383 p_odi_session_id => p_odi_session_id,
4384 p_input_id => p_index,
4385 p_err_code => 'EGO_INVALID_ORG_CODE',
4386 p_err_message => 'Invalid Organization Code');
4387
4388 END IF;
4389
4390 RETURN FALSE;
4391 WHEN OTHERS THEN
4392 RAISE;
4393
4394 END Validate_organization;
4395
4396
4397 function validate_revision_details(p_session_id IN NUMBER,
4398 p_odi_session_id IN NUMBER,
4399 p_inv_id IN NUMBER,
4400 p_org_id IN NUMBER,
4401 p_rev_id in NUMBER ,
4402 p_revision IN varchar2,
4403 p_rev_date IN DATE,
4404 p_index in number,
4405 p_revision_id OUT NOCOPY NUMBER ,
4406 p_revision_date OUT NOCOPY DATE
4407 ) RETURN BOOLEAN
4408 is
4409
4410 v_effectivity_date DATE;
4411 v_end_date DATE;
4412 v_impl_date DATE;
4413 v_is_valid_rev_date BOOLEAN;
4414 v_count NUMBER;
4415 v_current_date DATE;
4416 e_no_revision EXCEPTION;
4417 e_unimpl_revision EXCEPTION;
4418
4419 BEGIN
4420 IF (p_rev_date IS NOT NULL) THEN
4421 BEGIN
4422 SELECT *
4423 INTO p_revision_id, v_effectivity_date, v_end_date, v_impl_date
4424 FROM
4425 (SELECT revision_id ,
4426 effectivity_date ,
4427 (SELECT NVL( MIN(b.effectivity_date)-(1/86400),to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss')) end_date
4428 FROM mtl_item_revisions_b b
4429 WHERE b.inventory_item_id = a.inventory_item_id
4430 AND b.organization_id = a.organization_id
4431 AND b.effectivity_date > a.effectivity_date
4432 ) end_date,
4433 implementation_date
4434 from mtl_item_revisions_b a
4435 WHERE organization_id = p_org_id
4436 AND inventory_item_id = p_inv_id
4437 AND effectivity_date < p_rev_date
4438 AND implementation_date IS NOT NULL
4439 ORDER BY effectivity_date DESC) list
4440 WHERE ROWNUM = 1;
4441
4442 p_revision_date := p_rev_date;
4443
4444 EXCEPTION
4445 WHEN NO_DATA_FOUND THEN
4446 RAISE e_no_revision;
4447 END;
4448 ELSE
4449 IF (p_rev_id <> -1) THEN
4450 SELECT revision_id ,
4451 effectivity_date ,
4452 (SELECT NVL( MIN(b.effectivity_date)-(1/86400),to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss')) end_date
4453 FROM mtl_item_revisions_b b
4454 WHERE b.inventory_item_id = a.inventory_item_id
4455 AND b.organization_id = a.organization_id
4456 AND b.effectivity_date > a.effectivity_date
4457 ) end_date,
4458 implementation_date
4459 INTO p_revision_id, v_effectivity_date, v_end_date, v_impl_date
4460 from mtl_item_revisions_b a
4461 WHERE organization_id = p_org_id
4462 AND inventory_item_id = p_inv_id
4463 AND revision_id = p_rev_id;
4464 ELSIF (p_revision IS NOT NULL) THEN
4465 SELECT revision_id ,
4466 effectivity_date ,
4467 (SELECT NVL( MIN(b.effectivity_date)-(1/86400),to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss')) end_date
4468 FROM mtl_item_revisions_b b
4469 WHERE b.inventory_item_id = a.inventory_item_id
4470 AND b.organization_id = a.organization_id
4471 AND b.effectivity_date > a.effectivity_date
4472 ) end_date,
4473 implementation_date
4474 INTO p_revision_id, v_effectivity_date, v_end_date, v_impl_date
4475 from mtl_item_revisions_b a
4476 WHERE organization_id = p_org_id
4477 AND inventory_item_id = p_inv_id
4478 AND revision = p_revision;
4479 ELSE
4480 p_revision_date := SYSDATE;
4481
4482 -- Do we need to fetch the latest revision based on sysdate if rev id and revision are null????
4483 SELECT *
4484 INTO p_revision_id, v_effectivity_date, v_end_date, v_impl_date
4485 FROM
4486 (SELECT revision_id ,
4487 effectivity_date ,
4488 (SELECT NVL( MIN(b.effectivity_date)-(1/86400),to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss')) end_date
4489 FROM mtl_item_revisions_b b
4490 WHERE b.inventory_item_id = a.inventory_item_id
4491 AND b.organization_id = a.organization_id
4492 AND b.effectivity_date > a.effectivity_date
4493 ) end_date,
4494 implementation_date
4495 from mtl_item_revisions_b a
4496 WHERE organization_id = p_org_id
4497 AND inventory_item_id = p_inv_id
4498 AND effectivity_date < SYSDATE
4499 AND implementation_date IS NOT NULL
4500 ORDER BY effectivity_date DESC) list
4501 WHERE ROWNUM = 1;
4502 END IF;
4503
4504 IF ((p_rev_id <> -1) OR p_revision IS NOT NULL) THEN
4505 IF (v_impl_date IS NULL ) THEN
4506 RAISE e_unimpl_revision;
4507 END IF;
4508
4509 SELECT SYSDATE INTO v_current_date from dual;
4510
4511 IF(v_end_date <= v_current_date) THEN
4512 p_revision_date := v_end_date;
4513 ELSE
4514 -- Bug 8659192: Start
4515 IF (v_current_date >= v_effectivity_date) THEN
4516 -- For Current Revision Take Sysdate
4517 p_revision_date := SYSDATE;
4518 ELSE
4519 p_revision_date := v_effectivity_date;
4520 END IF;
4521 -- Bug 8659192: End
4522 END IF;
4523 END IF;
4524 END IF; -- end of if (p_rev_date IS NOT NULL)
4525 RETURN TRUE;
4526 EXCEPTION
4527 WHEN e_no_revision THEN
4528 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4529 p_odi_session_id => p_odi_session_id,
4530 p_input_id => p_index,
4531 p_param_name => 'RevisionDate',
4532 p_param_value => to_char(p_rev_date,'YYYY.MM.DD HH24:MI:SS')
4533 );
4534
4535 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4536 p_odi_session_id => p_odi_session_id,
4537 p_input_id => p_index,
4538 p_err_code => 'EGO_NO_REV_EXISTS',
4539 p_err_message => 'No revision exists for the revision date');
4540
4541 RETURN FALSE;
4542
4543 WHEN e_unimpl_revision then
4544 IF (p_rev_id <> -1) THEN
4545 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4546 p_odi_session_id => p_odi_session_id,
4547 p_input_id => p_index,
4548 p_param_name => 'RevisionId',
4549 p_param_value => p_rev_id
4550 );
4551 END IF;
4552
4553 IF (p_revision IS NOT NULL) THEN
4554 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4555 p_odi_session_id => p_odi_session_id,
4556 p_input_id => p_index,
4557 p_param_name => 'Revision',
4558 p_param_value => p_revision
4559 );
4560 END IF;
4561
4562 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4563 p_odi_session_id => p_odi_session_id,
4564 p_input_id => p_index,
4565 p_err_code => 'EGO_UNIMPL_REVISION',
4566 p_err_message => 'Unimplemented Revisions are not published');
4567
4568 RETURN FALSE;
4569
4570 WHEN No_Data_Found THEN
4571 IF (p_rev_id <> -1) THEN
4572 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4573 p_odi_session_id => p_odi_session_id,
4574 p_input_id => p_index,
4575 p_param_name => 'RevisionId',
4576 p_param_value => p_rev_id
4577 );
4578 ELSIF (p_revision IS NOT NULL) THEN
4579 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4580 p_odi_session_id => p_odi_session_id,
4581 p_input_id => p_index,
4582 p_param_name => 'Revision',
4583 p_param_value => p_revision
4584 );
4585 ELSE
4586 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4587 p_odi_session_id => p_odi_session_id,
4588 p_input_id => p_index,
4589 p_param_name => 'RevisionDate',
4590 p_param_value => to_char(p_rev_date,'YYYY.MM.DD HH24:MI:SS')
4591 );
4592 END IF;
4593
4594 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4595 p_odi_session_id => p_odi_session_id,
4596 p_input_id => p_index,
4597 p_err_code => 'EGO_INVALID_REV_DETAILS',
4598 p_err_message => 'Invalid Revision Details');
4599
4600 RETURN FALSE;
4601
4602 WHEN OTHERS THEN
4603 RAISE;
4604
4605 END validate_revision_details;
4606
4607
4608 function validate_structure_name(p_session_id IN NUMBER,
4609 p_odi_session_id IN NUMBER,
4610 p_org_id IN NUMBER,
4611 p_structure_name IN varchar2,
4612 p_input_id IN NUMBER
4613 ) RETURN BOOLEAN
4614 is
4615
4616 v_count NUMBER;
4617 v_org_code VARCHAR2(10);
4618 p_index NUMBER;
4619
4620 BEGIN
4621
4622 IF(p_structure_name is not null) then
4623 select 1 INTO v_count
4624 from
4625 bom_alternate_designators
4626 WHERE organization_id = p_org_id
4627 AND ALTERNATE_DESIGNATOR_CODE = p_structure_name;
4628
4629 RETURN TRUE;
4630 ELSE
4631 RETURN FALSE;
4632 END IF;
4633
4634 EXCEPTION
4635 WHEN No_Data_Found THEN
4636 SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
4637 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
4638 WHERE session_id = p_session_id;
4639
4640 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4641 p_odi_session_id => p_odi_session_id,
4642 p_input_id => p_index,
4643 p_param_name => 'StructureName',
4644 p_param_value => p_structure_name
4645 );
4646
4647 BEGIN
4648
4649 SELECT PARAM_VALUE into v_org_code
4650 FROM EGO_PUB_WS_INPUT_IDENTIFIERS
4651 WHERE SESSION_ID = p_session_id
4652 AND INPUT_ID = p_input_id
4653 AND PARAM_NAME = 'OrganizationCode';
4654
4655 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4656 p_odi_session_id => p_odi_session_id,
4657 p_input_id => p_index,
4658 p_param_name => 'OrganizationCode',
4659 p_param_value => v_org_code
4660 );
4661 EXCEPTION
4662 WHEN NO_DATA_FOUND THEN
4663 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
4664 p_odi_session_id => p_odi_session_id,
4665 p_input_id => p_index,
4666 p_param_name => 'OrganizationId',
4667 p_param_value => p_org_id
4668 );
4669 END;
4670
4671 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4672 p_odi_session_id => p_odi_session_id,
4673 p_input_id => p_index,
4674 p_err_code => 'EGO_INVALID_STRUCTURE',
4675 p_err_message => 'Invalid Structure Name and Org Combination');
4676 RETURN FALSE;
4677
4678 WHEN OTHERS THEN
4679 RAISE;
4680
4681 END validate_structure_name;
4682
4683 PROCEDURE check_security(p_session_id IN NUMBER,
4684 p_odi_session_id IN NUMBER,
4685 p_priv_check IN VARCHAR2,
4686 p_for_exploded_items IN VARCHAR2,
4687 x_return_status OUT NOCOPY VARCHAR2
4688 )
4689 IS
4690
4691 l_sec_predicate VARCHAR2(32767);
4692 l_dynamic_update_sql VARCHAR2(32767);
4693 l_dynamic_sql VARCHAR2(32767);
4694 l_item_id NUMBER;
4695 l_org_id NUMBER;
4696 l_rev_id NUMBER;
4697
4698 l_mode VARCHAR2(10);
4699 l_batch_id NUMBER;
4700 l_batch_ent_obj_id NUMBER;
4701 l_user_name VARCHAR2(100);
4702 l_structure_name VARCHAR2(100);
4703 p_index number;
4704 l_seq_number NUMBER;
4705 l_component_name mtl_system_items_b_kfv.CONCATENATED_SEGMENTS%TYPE;
4706
4707 TYPE DYNAMIC_CUR IS REF CURSOR;
4708 v_dynamic_cursor DYNAMIC_CUR;
4709
4710 BEGIN
4711
4712 select char_value
4713 into l_mode
4714 from EGO_PUB_WS_CONFIG
4715 where parameter_name = 'INVOCATION_MODE'
4716 and session_id = p_session_id;
4717
4718 IF (l_mode = 'BATCH') THEN
4719 SELECT party_name INTO l_user_name
4720 FROM EGO_USER_V WHERE USER_ID = FND_GLOBAL.USER_ID;
4721
4722 select Numeric_Value
4723 into l_batch_id
4724 from EGO_PUB_WS_CONFIG
4725 where parameter_name = 'BATCH_ID'
4726 and session_id = p_session_id;
4727
4728 SELECT CHAR_VALUE INTO l_structure_name FROM EGO_PUB_BAT_PARAMS_B
4729 WHERE type_id=l_batch_id AND Upper(parameter_name) ='STRUCTURE_NAME';
4730 END IF;
4731
4732 EGO_DATA_SECURITY.get_security_predicate
4733 (p_api_version => 1.0
4734 ,p_function => p_priv_check
4735 ,p_object_name => 'EGO_ITEM'
4736 ,p_user_name => 'HZ_PARTY:'||TO_CHAR(FND_GLOBAL.PARTY_ID)
4737 ,p_statement_type => 'EXISTS'
4738 ,p_pk1_alias => 'i.pk1_value'
4739 ,p_pk2_alias => 'i.pk2_value'
4740 ,p_pk3_alias => NULL
4741 ,p_pk4_alias => NULL
4742 ,p_pk5_alias => NULL
4743 ,x_predicate => l_sec_predicate
4744 ,x_return_status => x_return_status );
4745
4746 IF x_return_status IN ('T','F') THEN
4747
4748 IF l_sec_predicate IS NOT NULL THEN
4749
4750 BEGIN
4751 l_dynamic_sql := ' select distinct PK4_VALUE ' ||
4752 ' from EGO_ODI_WS_ENTITIES i ' ||
4753 ' where i.session_id = :1 ' ||
4754 ' AND nvl(i.REF1_VALUE, ''Y'') = ''Y'' ' ||
4755 ' AND NOT ' || l_sec_predicate;
4756
4757 OPEN v_dynamic_cursor FOR l_dynamic_sql
4758 USING p_session_id;
4759 LOOP
4760 FETCH v_dynamic_cursor INTO l_seq_number;
4761 EXIT WHEN v_dynamic_cursor%NOTFOUND;
4762
4763 IF (p_for_exploded_items = 'N') THEN
4764 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4765 p_odi_session_id => p_odi_session_id,
4766 p_input_id => l_seq_number,
4767 p_err_code => 'EGO_NO_PUBLISH_PRIV',
4768 p_err_message => 'User does not have the publish privilege for item');
4769
4770 ELSE
4771 IF (l_mode = 'BATCH') THEN
4772 SELECT BATCH_ENTITY_OBJECT_ID
4773 INTO l_batch_ent_obj_id
4774 FROM Ego_Pub_Bat_Ent_Objs_v
4775 WHERE batch_id = l_batch_id
4776 AND (PK1_VALUE, PK2_VALUE, PK3_VALUE) in (select pk1_value, pk2_value, pk3_value
4777 from EGO_ODI_WS_ENTITIES
4778 where session_id = p_session_id
4779 and SEQUENCE_NUMBER = l_seq_number);
4780
4781 -- Need to use an API - which will be provided by PUB FWK
4782 UPDATE EGO_PUB_BAT_STATUS_B
4783 SET STATUS_CODE = 'F' , MESSAGE = 'User ' || l_user_name ||' does not have the publilsh privilege on few components of the structure ' ||
4784 l_structure_name || ' for this Item.'
4785 WHERE batch_id = l_batch_id AND BATCH_ENTITY_OBJECT_ID = l_batch_ent_obj_id;
4786
4787 END IF;
4788 END IF;
4789 END LOOP;
4790 CLOSE v_dynamic_cursor;
4791 x_return_status := 'S';
4792 EXCEPTION
4793 WHEN OTHERS THEN
4794 x_return_status := 'E';
4795 RAISE;
4796 IF (v_dynamic_cursor%ISOPEN) THEN
4797 CLOSE v_dynamic_cursor;
4798 END IF;
4799 END; -- end of BEGIN
4800
4801 l_dynamic_update_sql := ' update EGO_ODI_WS_ENTITIES i ' ||
4802 ' set REF1_VALUE = ''N'' ' ||
4803 ' where i.session_id = :1 ' ||
4804 ' AND nvl(i.REF1_VALUE, ''Y'') = ''Y'' ' ||
4805 ' AND NOT ' || l_sec_predicate;
4806
4807 EXECUTE IMMEDIATE l_dynamic_update_sql
4808 USING IN p_session_id;
4809
4810 IF (p_for_exploded_items = 'Y') THEN
4811 EGO_DATA_SECURITY.get_security_predicate
4812 (p_api_version => 1.0
4813 ,p_function => 'EGO_VIEW_ITEM'
4814 ,p_object_name => 'EGO_ITEM'
4815 ,p_user_name => 'HZ_PARTY:'||TO_CHAR(FND_GLOBAL.PARTY_ID)
4816 ,p_statement_type => 'EXISTS'
4817 ,p_pk1_alias => 'i.pk1_value'
4818 ,p_pk2_alias => 'i.pk2_value'
4819 ,p_pk3_alias => NULL
4820 ,p_pk4_alias => NULL
4821 ,p_pk5_alias => NULL
4822 ,x_predicate => l_sec_predicate
4823 ,x_return_status => x_return_status );
4824
4825 IF x_return_status IN ('T','F') THEN
4826
4827 IF l_sec_predicate IS NOT NULL THEN
4828 BEGIN
4829 l_dynamic_sql := ' select pk1_value, pk2_value, pk3_value, PK4_VALUE ' ||
4830 ' from EGO_ODI_WS_ENTITIES i ' ||
4831 ' where i.session_id = :1 ' ||
4832 ' AND nvl(i.REF1_VALUE, ''Y'') = ''N'' ' ||
4833 ' AND SEQUENCE_NUMBER IS NULL ' ||
4834 ' AND ' || l_sec_predicate;
4835
4836 OPEN v_dynamic_cursor FOR l_dynamic_sql
4837 USING p_session_id;
4838 LOOP
4839 FETCH v_dynamic_cursor INTO l_item_id , l_org_id, l_rev_id, l_seq_number;
4840 EXIT WHEN v_dynamic_cursor%NOTFOUND;
4841
4842 select CONCATENATED_SEGMENTS
4843 into l_component_name
4844 from mtl_system_items_b_kfv
4845 WHERE inventory_item_id = l_item_id
4846 AND ORGANIZATION_ID = l_org_id;
4847
4848 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4849 p_odi_session_id => p_odi_session_id,
4850 p_input_id => l_seq_number,
4851 p_err_code => 'EGO_NO_PUBLISH_PRIV_COMP',
4852 p_err_message => 'User does not have the publish privilege for Component - '|| l_component_name);
4853 END LOOP;
4854 CLOSE v_dynamic_cursor;
4855 x_return_status := 'S';
4856 EXCEPTION
4857 WHEN OTHERS THEN
4858 x_return_status := 'E';
4859 RAISE;
4860 IF (v_dynamic_cursor%ISOPEN) THEN
4861 CLOSE v_dynamic_cursor;
4862 END IF;
4863 END; -- end of BEGIN
4864
4865 l_dynamic_sql := ' select distinct PK4_VALUE ' ||
4866 ' from EGO_ODI_WS_ENTITIES i ' ||
4867 ' where i.session_id = :1 ' ||
4868 ' AND nvl(i.REF1_VALUE, ''Y'') = ''N'' ' ||
4869 ' AND SEQUENCE_NUMBER IS NULL ' ||
4870 ' AND NOT ' || l_sec_predicate;
4871
4872 OPEN v_dynamic_cursor FOR l_dynamic_sql
4873 USING p_session_id;
4874 LOOP
4875 FETCH v_dynamic_cursor INTO l_seq_number;
4876 EXIT WHEN v_dynamic_cursor%NOTFOUND;
4877
4878 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4879 p_odi_session_id => p_odi_session_id,
4880 p_input_id => l_seq_number,
4881 p_err_code => 'EGO_NO_PUBLISH_PRIV',
4882 p_err_message => 'User does not have the publilsh privilege on few components of the structure for the item');
4883 END LOOP;
4884 CLOSE v_dynamic_cursor;
4885 x_return_status := 'S';
4886 ELSE
4887 for i in (SELECT PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE
4888 FROM EGO_ODI_WS_ENTITIES
4889 WHERE SESSION_ID = P_SESSION_ID
4890 AND NVL(REF1_VALUE, 'Y') = 'N'
4891 AND SEQUENCE_NUMBER IS NULL)
4892 loop
4893 select CONCATENATED_SEGMENTS
4894 into l_component_name
4895 from mtl_system_items_b_kfv
4896 WHERE inventory_item_id = i.PK1_VALUE
4897 AND ORGANIZATION_ID = i.PK2_VALUE;
4898
4899 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
4900 p_odi_session_id => p_odi_session_id,
4901 p_input_id => l_seq_number,
4902 p_err_code => 'EGO_NO_PUBLISH_PRIV_COMP',
4903 p_err_message => 'User does not have the publish privilege for Component - '|| l_component_name);
4904 end loop;
4905 x_return_status := 'S';
4906 END IF; -- end of l_sec_predicate IS NOT NULL
4907 END IF;
4908
4909 UPDATE EGO_ODI_WS_ENTITIES ent1
4910 SET REF1_VALUE = 'N'
4911 WHERE Nvl(REF1_VALUE, 'Y') <> 'N'
4912 AND session_id = p_session_id
4913 AND PK4_VALUE IN (SELECT PK4_VALUE FROM EGO_ODI_WS_ENTITIES ent2
4914 WHERE Nvl(REF1_VALUE, 'Y') = 'N'
4915 AND SEQUENCE_NUMBER IS NULL
4916 AND ent1.session_id = ent2.session_id
4917 );
4918 END IF;
4919 ELSE
4920 x_return_status := 'S';
4921 END IF; -- end of l_sec_predicate IS NOT NULL
4922 END IF;
4923
4924 END check_security;
4925
4926 /* Bug 8659248 : Added the Below procedure for getting the security details of the
4927 user who is publishing the Items */
4928 PROCEDURE Init_Security_details(p_session_id IN NUMBER,
4929 p_odi_session_id IN NUMBER,
4930 p_return_status OUT NOCOPY VARCHAR2) -- Bug 8776414
4931 IS
4932
4933 l_mode VARCHAR2(100);
4934 l_application_id NUMBER;
4935 l_responsibility_id NUMBER;
4936 l_user_id NUMBER;
4937 l_security_group_id NUMBER;
4938 l_batch_id NUMBER;
4939 l_fnd_user_name VARCHAR2(100);
4940 l_responsibility_name VARCHAR2(100);
4941 l_responsibility_appl_name VARCHAR2(100);
4942 l_security_group_name VARCHAR2(100);
4943
4944 BEGIN
4945
4946 --retrieving invocation mode
4947 select char_value
4948 into l_mode
4949 from EGO_PUB_WS_CONFIG
4950 where parameter_name = 'INVOCATION_MODE'
4951 and session_id = p_session_id;
4952
4953 --if mode is batch, get security related information from publication
4954 --framework using batch_id
4955 IF l_mode = 'BATCH' THEN
4956
4957 --retrieving batchId from input XML
4958 select to_number(extractValue(xmlcontent, '/itemQueryParameters/BatchId'))
4959 into l_batch_id
4960 from EGO_PUB_WS_PARAMS
4961 where session_id = p_session_id;
4962
4963 --retrieving user_id and responsability
4964 select created_by, responsibility_id
4965 into l_user_id,l_responsibility_id
4966 from EGO_PUB_BAT_HDR_B
4967 where batch_id = l_batch_id;
4968
4969 --retrieving responsability_id
4970 Select application_id
4971 into l_application_id
4972 from FND_RESPONSIBILITY
4973 where responsibility_id = l_responsibility_id;
4974
4975 -- Bug 8776414 : Start
4976 --Initializing security context
4977 FND_GLOBAL.APPS_INITIALIZE(
4978 USER_ID=>l_user_id,
4979 RESP_ID=>l_responsibility_id,
4980 RESP_APPL_ID=>l_application_id
4981 );
4982
4983 p_return_status := 'S';
4984 -- Bug 8776414: End
4985
4986 ELSIF l_mode = 'LIST' OR l_mode = 'HMDM' THEN
4987
4988 select fnd_user_name, responsibility_name, responsibility_appl_name, security_group_name
4989 into l_fnd_user_name, l_responsibility_name, l_responsibility_appl_name, l_security_group_name
4990 from EGO_PUB_WS_PARAMS
4991 where session_id = p_session_id;
4992
4993 INSERT INTO EGO_PUB_WS_CONFIG (session_id,
4994 odi_session_id,
4995 Parameter_Name,
4996 Data_Type,
4997 Char_value,
4998 creation_date,
4999 created_by)
5000 VALUES (p_session_id,
5001 p_odi_session_id,
5002 'FND_USER_NAME',
5003 2,
5004 l_fnd_user_name,
5005 sysdate,
5006 0);
5007
5008 INSERT INTO EGO_PUB_WS_CONFIG (session_id,
5009 odi_session_id,
5010 Parameter_Name,
5011 Data_Type,
5012 Char_value,
5013 creation_date,
5014 created_by)
5015 VALUES (p_session_id,
5016 p_odi_session_id,
5017 'RESPONSIBILITY_NAME',
5018 2,
5019 l_responsibility_name,
5020 sysdate,
5021 0);
5022
5023 INSERT INTO EGO_PUB_WS_CONFIG (session_id,
5024 odi_session_id,
5025 Parameter_Name,
5026 Data_Type,
5027 Char_value,
5028 creation_date,
5029 created_by)
5030 VALUES (p_session_id,
5031 p_odi_session_id,
5032 'RESPONSIBILITY_APP_NAME',
5033 2,
5034 l_responsibility_appl_name,
5035 sysdate,
5036 0);
5037
5038 INSERT INTO EGO_PUB_WS_CONFIG (session_id,
5039 odi_session_id,
5040 Parameter_Name,
5041 Data_Type,
5042 Char_value,
5043 creation_date,
5044 created_by)
5045 VALUES (p_session_id,
5046 p_odi_session_id,
5047 'SECURITY_GROUP_NAME',
5048 2,
5049 l_security_group_name,
5050 sysdate,
5051 0);
5052
5053 BEGIN
5054
5055 --retrieving user id from user name
5056 select user_id
5057 into l_user_id
5058 from fnd_user
5059 where user_name = l_fnd_user_name;
5060
5061 --retrieving responsibility id from responsability name
5062 Select responsibility_id
5063 into l_responsibility_id
5064 from FND_RESPONSIBILITY
5065 where responsibility_key = l_responsibility_name;
5066
5067 --retrieving application id from application name
5068 select application_id
5069 into l_application_id
5070 from FND_APPLICATION
5071 where application_short_name = l_responsibility_appl_name;
5072
5073 -- Bug 8776414 : Start
5074 --Initializing security context
5075 FND_GLOBAL.APPS_INITIALIZE(
5076 USER_ID=>l_user_id,
5077 RESP_ID=>l_responsibility_id,
5078 RESP_APPL_ID=>l_application_id
5079 );
5080
5081 p_return_status := 'S';
5082 -- Bug 8776414: End
5083 exception
5084 when no_data_found then
5085 -- Bug 8776414 : Start
5086 DELETE EGO_PUB_WS_INPUT_IDENTIFIERS
5087 WHERE session_id = p_session_id;
5088
5089 DELETE EGO_PUB_WS_ERRORS
5090 WHERE session_id = p_session_id;
5091
5092 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5093 p_odi_session_id => p_odi_session_id,
5094 p_input_id => 1,
5095 p_param_name => 'RESPONSIBILITY_NAME',
5096 p_param_value => l_responsibility_name );
5097
5098 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5099 p_odi_session_id => p_odi_session_id,
5100 p_input_id => 1,
5101 p_param_name => 'RESPONSIBILITY_APPL_NAME',
5102 p_param_value => l_responsibility_appl_name );
5103
5104 EGO_ODI_PUB.Log_Error(p_session_id => p_session_id,
5105 p_odi_session_id => p_odi_session_id,
5106 p_input_id => 1,
5107 p_err_code => 'EGO_INVALID_SECURITY_DETAILS',
5108 p_err_message => 'Invalid Security Details');
5109
5110 -- Do not publish any item, So delete all the records.
5111 DELETE ego_odi_ws_entities
5112 WHERE session_id = p_session_id;
5113
5114 p_return_status := 'E';
5115 -- Bug 8776414 : End
5116 end;
5117 END IF;
5118
5119 END Init_Security_Details;
5120
5121 PROCEDURE POPULATE_SEGMENTS(p_session_id IN NUMBER,
5122 p_odi_session_id IN NUMBER,
5123 p_segment1 in varchar2,
5124 p_segment2 in varchar2,
5125 p_segment3 in varchar2,
5126 p_segment4 in varchar2,
5127 p_segment5 in varchar2,
5128 p_segment6 in varchar2,
5129 p_segment7 in varchar2,
5130 p_segment8 in varchar2,
5131 p_segment9 in varchar2,
5132 p_segment10 in varchar2,
5133 p_segment11 in varchar2,
5134 p_segment12 in varchar2,
5135 p_segment13 in varchar2,
5136 p_segment14 in varchar2,
5137 p_segment15 in varchar2,
5138 p_segment16 in varchar2,
5139 p_segment17 in varchar2,
5140 p_segment18 in varchar2,
5141 p_segment19 in varchar2,
5142 p_segment20 in varchar2,
5143 p_index in number )
5144 IS
5145
5146 BEGIN
5147 IF (p_segment1 IS NOT NULL) THEN
5148 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5149 p_odi_session_id => p_odi_session_id,
5150 p_input_id => p_index,
5151 p_param_name => 'Segment1',
5152 p_param_value => p_segment1 );
5153 END IF;
5154
5155 IF (p_segment2 IS NOT NULL) THEN
5156 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5157 p_odi_session_id => p_odi_session_id,
5158 p_input_id => p_index,
5159 p_param_name => 'Segment2',
5160 p_param_value => p_segment2 );
5161 END IF;
5162
5163 IF (p_segment3 IS NOT NULL) THEN
5164 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5165 p_odi_session_id => p_odi_session_id,
5166 p_input_id => p_index,
5167 p_param_name => 'Segment3',
5168 p_param_value => p_segment3 );
5169 END IF;
5170
5171 IF (p_segment4 IS NOT NULL) THEN
5172 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5173 p_odi_session_id => p_odi_session_id,
5174 p_input_id => p_index,
5175 p_param_name => 'Segment4',
5176 p_param_value => p_segment4 );
5177 END IF;
5178
5179 IF (p_segment5 IS NOT NULL) THEN
5180 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5181 p_odi_session_id => p_odi_session_id,
5182 p_input_id => p_index,
5183 p_param_name => 'Segment5',
5184 p_param_value => p_segment5 );
5185 END IF;
5186
5187 IF (p_segment6 IS NOT NULL) THEN
5188 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5189 p_odi_session_id => p_odi_session_id,
5190 p_input_id => p_index,
5191 p_param_name => 'Segment6',
5192 p_param_value => p_segment6 );
5193 END IF;
5194
5195 IF (p_segment7 IS NOT NULL) THEN
5196 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5197 p_odi_session_id => p_odi_session_id,
5198 p_input_id => p_index,
5199 p_param_name => 'Segment7',
5200 p_param_value => p_segment7 );
5201 END IF;
5202
5203 IF (p_segment8 IS NOT NULL) THEN
5204 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5205 p_odi_session_id => p_odi_session_id,
5206 p_input_id => p_index,
5207 p_param_name => 'Segment8',
5208 p_param_value => p_segment8 );
5209 END IF;
5210
5211 IF (p_segment9 IS NOT NULL) THEN
5212 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5213 p_odi_session_id => p_odi_session_id,
5214 p_input_id => p_index,
5215 p_param_name => 'Segment9',
5216 p_param_value => p_segment9 );
5217 END IF;
5218
5219 IF (p_segment10 IS NOT NULL) THEN
5220 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5221 p_odi_session_id => p_odi_session_id,
5222 p_input_id => p_index,
5223 p_param_name => 'Segment10',
5224 p_param_value => p_segment10 );
5225 END IF;
5226
5227 IF (p_segment11 IS NOT NULL) THEN
5228 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5229 p_odi_session_id => p_odi_session_id,
5230 p_input_id => p_index,
5231 p_param_name => 'Segment11',
5232 p_param_value => p_segment11 );
5233 END IF;
5234
5235 IF (p_segment12 IS NOT NULL) THEN
5236 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5237 p_odi_session_id => p_odi_session_id,
5238 p_input_id => p_index,
5239 p_param_name => 'Segment12',
5240 p_param_value => p_segment12 );
5241 END IF;
5242
5243 IF (p_segment13 IS NOT NULL) THEN
5244 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5245 p_odi_session_id => p_odi_session_id,
5246 p_input_id => p_index,
5247 p_param_name => 'Segment13',
5248 p_param_value => p_segment13 );
5249 END IF;
5250
5251 IF (p_segment14 IS NOT NULL) THEN
5252 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5253 p_odi_session_id => p_odi_session_id,
5254 p_input_id => p_index,
5255 p_param_name => 'Segment14',
5256 p_param_value => p_segment14 );
5257 END IF;
5258
5259 IF (p_segment15 IS NOT NULL) THEN
5260 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5261 p_odi_session_id => p_odi_session_id,
5262 p_input_id => p_index,
5263 p_param_name => 'Segment15',
5264 p_param_value => p_segment15 );
5265
5266 END IF;
5267
5268 IF (p_segment16 IS NOT NULL) THEN
5269 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5270 p_odi_session_id => p_odi_session_id,
5271 p_input_id => p_index,
5272 p_param_name => 'Segment16',
5273 p_param_value => p_segment16 );
5274
5275 END IF;
5276
5277 IF (p_segment17 IS NOT NULL) THEN
5278 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5279 p_odi_session_id => p_odi_session_id,
5280 p_input_id => p_index,
5281 p_param_name => 'Segment17',
5282 p_param_value => p_segment17 );
5283
5284 END IF;
5285
5286 IF (p_segment18 IS NOT NULL) THEN
5287 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5288 p_odi_session_id => p_odi_session_id,
5289 p_input_id => p_index,
5290 p_param_name => 'Segment18',
5291 p_param_value => p_segment18 );
5292
5293 END IF;
5294
5295 IF (p_segment19 IS NOT NULL) THEN
5296 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5297 p_odi_session_id => p_odi_session_id,
5298 p_input_id => p_index,
5299 p_param_name => 'Segment19',
5300 p_param_value => p_segment19 );
5301
5302 END IF;
5303
5304 IF (p_segment20 IS NOT NULL) THEN
5305 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5306 p_odi_session_id => p_odi_session_id,
5307 p_input_id => p_index,
5308 p_param_name => 'Segment20',
5309 p_param_value => p_segment20 );
5310
5311 END IF;
5312 END POPULATE_SEGMENTS;
5313
5314
5315 PROCEDURE POPULATE_REVISION_DETAILS(p_session_id IN NUMBER,
5316 p_odi_session_id IN NUMBER,
5317 p_rev_id NUMBER,
5318 p_revision VARCHAR,
5319 p_rev_date DATE,
5320 p_index NUMBER)
5321 IS
5322 BEGIN
5323
5324 IF (p_rev_id <> -1) THEN
5325 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5326 p_odi_session_id => p_odi_session_id,
5327 p_input_id => p_index,
5328 p_param_name => 'RevisionId',
5329 p_param_value => p_rev_id );
5330 END IF;
5331
5332 IF (p_revision IS NOT NULL) THEN
5333 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5334 p_odi_session_id => p_odi_session_id,
5335 p_input_id => p_index,
5336 p_param_name => 'Revision',
5337 p_param_value => p_revision );
5338 END IF;
5339
5340 IF (p_rev_date IS NOT NULL) THEN
5341 EGO_ODI_PUB.Populate_Input_Identifier(p_session_id => p_session_id,
5342 p_odi_session_id => p_odi_session_id,
5343 p_input_id => p_index,
5344 p_param_name => 'RevisionDate',
5345 p_param_value => p_rev_date );
5346 END IF;
5347 END POPULATE_REVISION_DETAILS;
5348
5349 END EGO_ITEM_WS_PVT;