1 PACKAGE BODY EGO_IMPORT_BATCH_COMPARE_PVT AS
2 /* $Header: EGOVCMPB.pls 120.32 2007/08/21 06:59:27 nshariff ship $ */
3
4 G_NUMBER_FORMAT VARCHAR2(1) := 'N';
5 G_CHAR_FORMAT VARCHAR2(1) := 'C';
6 G_DATE_FORMAT VARCHAR2(1) := 'D';
7 G_TIME_FORMAT VARCHAR2(1) := 'X';
8 G_DATE_TIME_FORMAT VARCHAR2(1) := 'Y';
9 G_ITEM_LEVEL VARCHAR2(20) := 'ITEM_LEVEL';
10 G_ORG_LEVEL VARCHAR2(20) := 'ITEM_ORG';
11 G_ITEM_SUPPLIER_LEVEL VARCHAR2(20) := 'ITEM_SUP';
12 G_ITEM_SUPPLIER_SITE_LEVEL VARCHAR2(20) := 'ITEM_SUP_SITE';
13 G_ITEM_SUPPLIER_SITE_ORG_LEVEL VARCHAR2(20) := 'ITEM_SUP_SITE_ORG';
14 G_ITEM_REVISION_LEVEL VARCHAR2(20) := 'ITEM_REVISION_LEVEL';
15 G_ITEM_LEVEL_ID NUMBER := 43101;
16 G_ORG_LEVEL_ID NUMBER := 43102;
17 G_ITEM_SUPPLIER_LEVEL_ID NUMBER := 43103;
18 G_ITEM_SUPPLIER_SITE_LEVEL_ID NUMBER := 43104;
19 G_ITEM_SUPSITEORG_LEVEL_ID NUMBER := 43105;
20
21
22 PROCEDURE Debug_Message(message IN VARCHAR2) IS
23 BEGIN
24 NULL;
25 --nisar_debug_proc(message);
26 END Debug_Message;
27
28
29 /**
30 * Nisar - Bug 5139813.
31 * Returns current production revision for an item.
32 * Returns - VARCHAR2.
33 */
34 FUNCTION GET_CURRENT_PDH_REVISION(p_inventory_item_id NUMBER
35 ,p_organization_id NUMBER)
36 RETURN VARCHAR2 IS
37 l_rev VARCHAR2(5);
38 BEGIN
39 EXECUTE IMMEDIATE 'SELECT IR.REVISION FROM MTL_ITEM_REVISIONS_B IR
40 WHERE IR.INVENTORY_ITEM_ID = :1
41 AND IR.ORGANIZATION_ID = :2
42 AND IR.REVISION_ID IN
43 (SELECT MAX(IR2.REVISION_ID)
44 KEEP (DENSE_RANK LAST ORDER BY IR2.EFFECTIVITY_DATE )
45 FROM MTL_ITEM_REVISIONS_B IR2
46 WHERE IR2.ORGANIZATION_ID = IR.ORGANIZATION_ID
47 AND IR2.INVENTORY_ITEM_ID = IR.INVENTORY_ITEM_ID
48 AND IR2.EFFECTIVITY_DATE <= SYSDATE
49 AND IR2.IMPLEMENTATION_DATE IS NOT NULL
50 )' INTO l_rev USING p_inventory_item_id, p_organization_id;
51 RETURN l_rev;
52 END GET_CURRENT_PDH_REVISION;
53
54
55 /**
56 * Nisar - Bug. 5139813.
57 * RETURNS BOOLEAN - To find out if certain revision exists for a given
58 * item passing its inventory_item_id and organization_id
59 */
60 FUNCTION REV_EXISTS_IN_PDH ( p_revision VARCHAR2,
61 p_inventory_item_id NUMBER,
62 p_organization_id NUMBER
63 )
64 RETURN VARCHAR2 IS
65 l_temp VARCHAR2(5);
66 BEGIN
67 EXECUTE IMMEDIATE 'SELECT REVISION
68 FROM MTL_ITEM_REVISIONS
69 WHERE INVENTORY_ITEM_ID = :1
70 AND ORGANIZATION_ID = :2
71 AND REVISION = :3' INTO l_temp USING p_inventory_item_id, p_organization_id, p_revision;
72 RETURN 'Y'; -- Return true
73 EXCEPTION
74 WHEN OTHERS THEN
75 RETURN 'N'; -- return false
76 END REV_EXISTS_IN_PDH;
77
78 /**
79 To get the Source System value for the internal value given
80 */
81 FUNCTION Get_SS_Data_For_Val_set (
82 p_value_set_id IN NUMBER
83 ,p_validation_code IN VARCHAR2
84 ,p_str_val IN VARCHAR2 DEFAULT NULL
85 ,p_date_val IN DATE DEFAULT NULL
86 ,p_num_val IN NUMBER DEFAULT NULL
87 ) RETURN VARCHAR2
88 IS
89 l_sql VARCHAR2(32767);
90 l_disp_value VARCHAR2(150);
91 l_attr_group_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
92 l_attr_metadata_obj EGO_ATTR_METADATA_OBJ;
93 BEGIN
94 l_attr_metadata_obj := EGO_ATTR_METADATA_OBJ(
95 NULL-- ATTR_ID
96 ,NULL -- ATTR_GROUP_ID
97 ,NULL -- ATTR_GROUP_NAME
98 ,NULL -- ATTR_NAME
99 ,NULL -- ATTR_DISP_NAME
100 ,NULL -- DATA_TYPE_CODE
101 ,NULL -- DATA_TYPE_MEANING
102 ,NULL -- SEQUENCE
103 ,NULL -- UNIQUE_KEY_FLAG
104 ,NULL -- DEFAULT_VALUE
105 ,NULL -- INFO_1
106 ,NULL -- MAXIMUM_SIZE
107 ,NULL -- REQUIRED_FLAG
108 ,NULL -- DATABASE_COLUMN
109 ,NULL -- VALUE_SET_ID
110 ,NULL -- VALIDATION_CODE
111 ,NULL -- MINIMUM_VALUE
112 ,NULL -- MAXIMUM_VALUE
113 ,NULL -- UNIT_OF_MEASURE_CLASS
114 ,NULL -- UNIT_OF_MEASURE_BASE
115 ,NULL -- DISP_TO_INT_VAL_QUERY
116 ,NULL -- INT_TO_DISP_VAL_QUERY
117 ,NULL -- VS_BIND_VALUES_CODE
118 ,NULL -- VIEW_IN_HIERARCHY_CODE
119 ,NULL -- EDIT_IN_HIERARCHY_CODE
120 );
121 l_attr_group_metadata_obj := EGO_ATTR_GROUP_METADATA_OBJ(
122 NULL -- ATTR_GROUP_ID
123 ,NULL -- APPLICATION_ID
124 ,NULL -- ATTR_GROUP_TYPE
125 ,NULL -- ATTR_GROUP_NAME
126 ,NULL -- ATTR_GROUP_DISP_NAME
127 ,NULL -- AGV_NAME
128 ,NULL -- MULTI_ROW_CODE
129 ,NULL -- VIEW_PRIVILEGE
130 ,NULL -- EDIT_PRIVILEGE
131 ,NULL -- EXT_TABLE_B_NAME
132 ,NULL -- EXT_TABLE_TL_NAME
133 ,NULL -- EXT_TABLE_VL_NAME
134 ,NULL -- SORT_ATTR_VALUES_FLAG
135 ,NULL -- UNIQUE_KEY_ATTRS_COUNT
136 ,NULL -- TRANS_ATTRS_COUNT
137 ,NULL -- attr_metadata_table
138 ,NULL -- ATTR_GROUP_ID_FLAG
139 ,NULL -- HIERARCHY_NODE_QUERY
140 ,NULL -- HIERARCHY_PROPAGATION_API
141 ,NULL -- HIERARCHY_PROPAGATE_FLAG
142 ,NULL -- ENABLED_DATA_LEVELS
143 ,NULL -- VARIANT
144 );
145 EGO_USER_ATTRS_COMMON_PVT.Build_Sql_Queries_For_Value(
146 p_value_set_id => p_value_set_id
147 ,p_validation_code => p_validation_code
148 ,px_attr_group_metadata_obj => l_attr_group_metadata_obj-- EGO_ATTR_GROUP_METADATA_OBJ
149 ,px_attr_metadata_obj => l_attr_metadata_obj ); -- EGO_ATTR_METADATA_OBJ
150 l_sql := l_attr_metadata_obj.INT_TO_DISP_VAL_QUERY ;
151 IF l_sql IS NOT NULL THEN
152 l_sql := l_sql || ' :1' ;
153 IF( INSTR(l_sql, '$') > 0 ) THEN
154 IF p_num_val IS NOT NULL THEN
155 l_disp_value := TO_CHAR(p_num_val);
156 ELSIF p_str_val IS NOT NULL THEN
157 l_disp_value := p_str_val;
158 ELSIF p_date_val IS NOT NULL THEN
159 l_disp_value := TO_CHAR(p_date_val, 'MM/DD/YYYY HH24:MI:SS');
160 END IF; --IF p_num_val IS NOT NULL THEN
161 ELSIF p_num_val IS NOT NULL THEN
162 BEGIN
163 EXECUTE IMMEDIATE l_sql INTO l_disp_value USING p_num_val;
164 EXCEPTION WHEN NO_DATA_FOUND THEN
165 l_disp_value := TO_CHAR(p_num_val);
166 END;
167 ELSIF p_str_val IS NOT NULL THEN
168 BEGIN
169 EXECUTE IMMEDIATE l_sql INTO l_disp_value USING p_str_val;
170 EXCEPTION WHEN NO_DATA_FOUND THEN
171 l_disp_value := p_str_val;
172 END;
173 ELSIF p_date_val IS NOT NULL THEN
174 BEGIN
175 EXECUTE IMMEDIATE l_sql INTO l_disp_value USING p_date_val;
176 EXCEPTION WHEN NO_DATA_FOUND THEN
177 l_disp_value := TO_CHAR(p_date_val, 'MM/DD/YYYY HH24:MI:SS');
178 END;
179 END IF; --IF( INSTR(l_sql, '$') > 0 ) THEN
180 ELSE
181 IF p_num_val IS NOT NULL THEN
182 l_disp_value := TO_CHAR(p_num_val);
183 ELSIF p_str_val IS NOT NULL THEN
184 l_disp_value := p_str_val;
185 ELSIF p_date_val IS NOT NULL THEN
186 l_disp_value := TO_CHAR(p_date_val, 'MM/DD/YYYY HH24:MI:SS');
187 END IF; --IF p_num_val IS NOT NULL THEN
188 END IF; -- IF l_sql IS NOT NULL THEN
189
190 -- if we get NULL as the display value THEN send back the input value as output
191 IF l_disp_value IS NULL THEN
192 IF p_num_val IS NOT NULL THEN
193 l_disp_value := TO_CHAR(p_num_val);
194 ELSIF p_str_val IS NOT NULL THEN
195 l_disp_value := p_str_val;
196 ELSIF p_date_val IS NOT NULL THEN
197 l_disp_value := TO_CHAR(p_date_val, 'MM/DD/YYYY HH24:MI:SS');
198 END IF;
199 END IF;
200
201 RETURN l_disp_value;
202 END Get_SS_Data_For_Val_set;
203
204 /******************************************************************************
205 Populate data in sql table type to return the data.
206 *****************************************************************************/
207 PROCEDURE populate_compare_tbl ( p_compare_table IN OUT NOCOPY SYSTEM.EGO_COMPARE_VIEW_TABLE ,
208 p_index IN NUMBER ,
209 p_sel_item IN NUMBER ,
210 p_value IN VARCHAR2 ,
211 p_item1 IN NUMBER ,
212 p_item2 IN NUMBER ,
213 p_item3 IN NUMBER ,
214 p_item4 IN NUMBER )
215 IS
216 BEGIN
217 IF p_sel_item = p_item1 THEN
218 p_compare_table(p_index).item1 := p_value ;
219 ELSIF p_sel_item = p_item2 THEN
220 p_compare_table(p_index).item2 := p_value ;
221 ELSIF p_sel_item = p_item3 THEN
222 p_compare_table(p_index).item3 := p_value ;
223 ELSIF p_sel_item = p_item4 THEN
224 p_compare_table(p_index).item4 := p_value ;
225 END IF;
226 END populate_compare_tbl;
227
228
229 /***************************************************************************
230 By Nisar - Get the function (or Privilege) name ... Privilege of
231 a user to access attributes of an Attribute Group
232 *****************************************************************************/
233 FUNCTION get_privilege_name ( p_priv_id IN NUMBER) RETURN VARCHAR2
234 IS
235 l_name VARCHAR2(100);
236 BEGIN
237 SELECT FUNCTION_NAME INTO l_name
238 FROM FND_FORM_FUNCTIONS
239 WHERE FUNCTION_ID = p_priv_id;
240 RETURN l_name;
241 EXCEPTION WHEN NO_DATA_FOUND THEN
242 RETURN NULL;
243 END get_privilege_name;
244
245
246
247 /***************************************************************************
248 To get the data(primary, opearational,
249 , and developer defined
250 attrs, for the source system item and matched item ids passed.
251 *****************************************************************************/
252
253 FUNCTION GET_COMPARED_DATA (p_ss_code NUMBER,
254 p_ss_record VARCHAR2 ,
255 p_batch_id NUMBER,
256 p_mode NUMBER,
257 p_item1 NUMBER,
258 p_item2 NUMBER,
259 p_item3 NUMBER,
260 p_item4 NUMBER,
261 p_org_Id NUMBER,
262 p_pdh_revision VARCHAR2,
263 p_supplier_id NUMBER DEFAULT NULL, -- R12C: New Parameter: Supplier Id
264 p_supplier_site_id NUMBER DEFAULT NULL, -- R12C: New Parameter: Supplier Site Id
265 p_bundle_id NUMBER DEFAULT NULL -- R12C: New Parameter passed only in case of GDSN Enable batches.
266 )
267 RETURN SYSTEM.EGO_COMPARE_VIEW_TABLE
268 IS
269 --------------------------------------------------------------
270 -- CURSOR FOR GETTING META DATA OF ITEM OPERATIONAL ATTRIBUTES
271 --------------------------------------------------------------
272 CURSOR cr_attr_info IS
273 SELECT
274 AG.ATTR_GROUP_NAME,
275 AG.ATTR_GROUP_DISP_NAME,
276 AG.VIEW_PRIVILEGE_ID,
277 A.ATTR_ID,
278 A.DATABASE_COLUMN,
279 A.ATTR_DISPLAY_NAME ,
280 A.VALIDATION_CODE,
281 A.ATTR_NAME,
282 A.VALUE_SET_ID,
283 A.DATA_TYPE_CODE,
284 A.UOM_CLASS
285 FROM
286 EGO_ATTR_GROUPS_DL_V AG ,
287 EGO_ATTRS_V A
288 WHERE A.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
289 AND A.ATTR_GROUP_TYPE = 'EGO_MASTER_ITEMS'
290 AND A.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
291 AND A.APPLICATION_ID = AG.APPLICATION_ID
292 AND A.APPLICATION_ID = 431
293 AND AG.ATTR_GROUP_NAME <> 'Main'
294 ORDER BY AG.ATTR_GROUP_NAME;
295
296 -----------------------------------------------------
297 -- CURSOR FOR GETTING ATTRIBUTE GROUP ID AND NAME OF
298 -- ITEM OPERATIONAL ATTRIBUTE GROUPS
299 -----------------------------------------------------
300 CURSOR cr_op_attr_grps IS
301 SELECT
302 AG.ATTR_GROUP_ID,
303 AG.ATTR_GROUP_NAME
304 FROM EGO_ATTR_GROUPS_DL_V AG
305 WHERE AG.ATTR_GROUP_TYPE IN ('EGO_MASTER_ITEMS')
306 AND AG.ATTR_GROUP_NAME <> 'Main'
307 AND AG.APPLICATION_ID = 431 ;
308
309 ----------------------------------------------------------------------
310 -- CURSOR FOR GETTING THE NAME AND MEANING FOR ITEM PRIMARY ATTRIBUTES
311 ----------------------------------------------------------------------
312 CURSOR cr_primary_attr IS
313 SELECT
314 LOOKUP_CODE,
315 MEANING
316 FROM FND_LOOKUPS
317 WHERE LOOKUP_TYPE ='EGO_ITEM_PRIMARY_ATTRIBUTE_GRP'
318 AND LOOKUP_CODE NOT IN ('APPROVAL_STATUS','ITEM_NUMBER', 'NEW_ITEM_REQUEST') -- 'LONG_DESCRIPTION',
319 AND ENABLED_FLAG = 'Y'
320 AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
321 AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE);
322
323 ------------------------------------------------------------
324 -- CURSOR TO GET THE USER DEFINED ATTRIBUTE GROUPS
325 -- PRESENT IN THE INTERFACE TABLE FOR A SOURCE SYSTEM ITEM
326 ------------------------------------------------------------
327 CURSOR cr_attr_groups(c_revision IN VARCHAR2, c_bundle_id IN NUMBER) IS
328 SELECT DISTINCT
329 I.ATTR_GROUP_INT_NAME,
330 I.REVISION,
331 AG.DATA_LEVEL_INTERNAL_NAME, -- R12C: Added
332 AG.DATA_LEVEL_ID, -- R12C: Added
333 AG.ATTR_GROUP_DISP_NAME,
334 AG.ATTR_GROUP_ID,
335 AG.VIEW_PRIVILEGE_ID
336 FROM
337 EGO_ITM_USR_ATTR_INTRFC I,
338 EGO_ATTR_GROUPS_DL_V AG
339 WHERE NVL(AG.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = 'EGO_ITEMMGMT_GROUP'
340 AND I.SOURCE_SYSTEM_ID = p_ss_code
341 AND I.SOURCE_SYSTEM_REFERENCE = p_ss_record
342 AND I.DATA_SET_ID = p_batch_id
343 AND NVL(I.PROCESS_STATUS, -1) < 1
344 AND (REVISION IS NULL OR REVISION = c_revision)
345 AND I.organization_id = p_org_id
346 AND I.ATTR_GROUP_INT_NAME = AG.ATTR_GROUP_NAME
347 AND ( I.DATA_LEVEL_ID = AG.DATA_LEVEL_ID
348 OR I.DATA_LEVEL_NAME = AG.DATA_LEVEL_INTERNAL_NAME )
349 AND AG.APPLICATION_ID = 431
350 AND AG.MULTI_ROW_CODE = 'N'
351 AND ( I.BUNDLE_ID IS NULL OR I.BUNDLE_ID = c_bundle_id );
352
353 ----------------------------------------------------------------------
354 -- CURSOR TO GET USER DEFINED ATTRIBUTES FROM THE INTERFACE TABLE
355 -- FOR GIVEN ATTRIBUTE GROUP
356 ----------------------------------------------------------------------
357 CURSOR cr_usr_intf (p_attr_group_int_name VARCHAR2, p_data_level_id NUMBER, c_bundle_id IN NUMBER ) IS
358 SELECT
359 AV.ATTR_ID,
360 AGV.ATTR_GROUP_ID,
361 I.ATTR_VALUE_STR,
362 I.ATTR_VALUE_NUM,
363 I.ATTR_VALUE_DATE,
364 I.ATTR_DISP_VALUE,
365 I.REVISION_ID,
366 I.REVISION,
367 I.ATTR_VALUE_UOM,
368 I.ATTR_UOM_DISP_VALUE,
369 AV.ATTR_DISPLAY_NAME,
370 AGV.ATTR_GROUP_DISP_NAME,
371 I.ATTR_INT_NAME ,
372 I.ATTR_GROUP_INT_NAME,
373 AGV.DATA_LEVEL_ID, -- R12C: Added
374 AGV.DATA_LEVEL_INTERNAL_NAME, -- R12C: Added
375 AV.DATABASE_COLUMN,
376 AV.VALUE_SET_ID,
377 AV.VALIDATION_CODE,
378 AV.DATA_TYPE_CODE,
379 AV.UOM_CLASS
380 FROM
381 EGO_ITM_USR_ATTR_INTRFC I,
382 EGO_ATTRS_V AV,
383 EGO_ATTR_GROUPS_DL_V AGV
384 WHERE AV.ATTR_GROUP_NAME = AGV.ATTR_GROUP_NAME
385 AND AV.ATTR_NAME = I.ATTR_INT_NAME
386 AND AV.ATTR_GROUP_NAME = I.ATTR_GROUP_INT_NAME
387 AND I.ATTR_GROUP_INT_NAME = p_attr_group_int_name
388 AND I.DATA_LEVEL_ID = p_data_level_id
389 AND NVL(AGV.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = 'EGO_ITEMMGMT_GROUP'
390 AND I.SOURCE_SYSTEM_ID = p_ss_code
391 AND I.SOURCE_SYSTEM_REFERENCE = p_ss_record
392 AND I.DATA_SET_ID = p_batch_id
393 AND NVL(I.PROCESS_STATUS, -1) < 1
394 AND I.REVISION IS NULL
395 AND I.ORGANIZATION_ID = p_org_id
396 AND ( I.DATA_LEVEL_ID = AGV.DATA_LEVEL_ID
397 OR I.DATA_LEVEL_NAME = AGV.DATA_LEVEL_INTERNAL_NAME )
398 AND AV.APPLICATION_ID = 431
399 AND AGV.APPLICATION_ID = AV.APPLICATION_ID
400 AND ( I.BUNDLE_ID IS NULL OR I.BUNDLE_ID = c_bundle_id );
401
402 ------------------------------------------------------
403 -- TO GET THE ATTRIBUTES FOR GIVEN ATTRIBUTE GROUP --
404 -- USER DEFINED ATTRIBUTES WITH RIVISION --
405 ------------------------------------------------------
406 CURSOR cr_rev_usr_intf (p_attr_group_int_name VARCHAR2, c_revision VARCHAR2, c_bundle_id IN NUMBER ) IS
407 SELECT
408 AV.ATTR_ID,
409 AGV.ATTR_GROUP_ID,
410 I.ATTR_VALUE_STR,
411 I.ATTR_VALUE_NUM,
412 I.ATTR_VALUE_DATE,
413 I.ATTR_DISP_VALUE,
414 I.REVISION_ID,
415 I.REVISION,
416 I.ATTR_VALUE_UOM,
417 I.ATTR_UOM_DISP_VALUE,
418 AV.ATTR_DISPLAY_NAME,
419 AV.UOM_CLASS,
420 AGV.ATTR_GROUP_DISP_NAME,
421 I.ATTR_INT_NAME ,
422 I.ATTR_GROUP_INT_NAME,
423 AGV.DATA_LEVEL_ID, -- R12C: Added
424 AGV.DATA_LEVEL_INTERNAL_NAME, -- R12C: Added
425 AV.DATABASE_COLUMN,
426 AV.VALUE_SET_ID,
427 AV.VALIDATION_CODE,
428 AV.DATA_TYPE_CODE
429 FROM
430 EGO_ITM_USR_ATTR_INTRFC I,
431 EGO_ATTRS_V AV,
432 EGO_ATTR_GROUPS_DL_V AGV
433 WHERE AV.ATTR_GROUP_NAME = AGV.ATTR_GROUP_NAME
434 AND AV.ATTR_NAME = I.ATTR_INT_NAME
435 AND AV.ATTR_GROUP_NAME = I.ATTR_GROUP_INT_NAME
436 AND I.ATTR_GROUP_INT_NAME = p_attr_group_int_name
437 AND NVL(AGV.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = 'EGO_ITEMMGMT_GROUP'
438 AND I.SOURCE_SYSTEM_ID = p_ss_code
439 AND I.SOURCE_SYSTEM_REFERENCE = p_ss_record
440 AND I.DATA_SET_ID = p_batch_id
441 AND NVL(I.PROCESS_STATUS, -1) < 1
442 AND I.REVISION = c_revision
443 AND I.organization_id = p_org_id
444 AND ( I.DATA_LEVEL_ID = AGV.DATA_LEVEL_ID
445 OR I.DATA_LEVEL_NAME = AGV.DATA_LEVEL_INTERNAL_NAME )
446 AND AV.APPLICATION_ID = 431
447 AND AGV.APPLICATION_ID = AV.APPLICATION_ID
448 AND ( I.BUNDLE_ID IS NULL OR I.BUNDLE_ID = c_bundle_id );
449
450 -------------------------------------------------------
451 -- CURSOR TO GET THE GDSN ATTRIBUTES IN INTERFACE TABLE
452 -------------------------------------------------------
453 CURSOR cr_dd_intf( c_bundle_id IN NUMBER ) IS
454 SELECT
455 AV.ATTR_ID,
456 AGV.ATTR_GROUP_ID,
457 I.ATTR_VALUE_STR,
458 I.ATTR_VALUE_NUM,
459 I.ATTR_VALUE_DATE,
460 I.ATTR_DISP_VALUE,
461 I.REVISION_ID,
462 I.ATTR_VALUE_UOM,
463 I.ATTR_UOM_DISP_VALUE,
464 AV.ATTR_DISPLAY_NAME,
465 AGV.ATTR_GROUP_DISP_NAME,
466 AGV.VIEW_PRIVILEGE_ID,
467 I.ATTR_INT_NAME ,
468 I.ATTR_GROUP_INT_NAME,
469 AV.DATABASE_COLUMN,
470 AV.VALUE_SET_ID,
471 AV.VALIDATION_CODE,
472 AV.DATA_TYPE_CODE,
473 AV.UOM_CLASS
474 FROM
475 EGO_ITM_USR_ATTR_INTRFC I,
476 EGO_ATTRS_V AV,
477 EGO_ATTR_GROUPS_DL_V AGV
478 WHERE AV.ATTR_GROUP_NAME = AGV.ATTR_GROUP_NAME
479 AND AGV.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
480 AND AV.ATTR_NAME = I.ATTR_INT_NAME
481 AND AV.ATTR_GROUP_NAME = I.ATTR_GROUP_INT_NAME
482 AND I.SOURCE_SYSTEM_ID = p_ss_code
483 AND I.SOURCE_SYSTEM_REFERENCE = p_ss_record
484 AND I.DATA_SET_ID = p_batch_id
485 AND I.organization_id = p_org_id
486 AND NVL(I.PROCESS_STATUS, -1) < 1
487 AND AV.APPLICATION_ID = 431
488 AND AGV.APPLICATION_ID = AV.APPLICATION_ID
489 AND ( I.BUNDLE_ID IS NULL OR I.BUNDLE_ID = c_bundle_id )
490 ORDER BY AGV.ATTR_GROUP_ID;
491
492 --------------------------------------------------
493 -- SAME CURSORS AS ABOVE MODIFIED FOR PDH ITEMS --
494 --------------------------------------------------
495
496 ------------------------------------------------------------
497 -- CURSOR TO GET THE USER DEFINED ATTRIBUTE GROUPS
498 -- PRESENT IN THE INTERFACE TABLE FOR A PDH ITEM
499 ------------------------------------------------------------
500 CURSOR cr_attr_groups_pdh(c_revision IN VARCHAR2) IS
501 SELECT DISTINCT
502 I.ATTR_GROUP_INT_NAME,
503 I.REVISION,
504 AG.DATA_LEVEL_INTERNAL_NAME, -- R12C: Added
505 AG.DATA_LEVEL_ID, -- R12C: Added
506 AG.ATTR_GROUP_DISP_NAME,
507 AG.ATTR_GROUP_ID,
508 AG.VIEW_PRIVILEGE_ID
509 FROM
510 EGO_ITM_USR_ATTR_INTRFC I,
511 EGO_ATTR_GROUPS_DL_V AG
512 WHERE NVL(AG.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = 'EGO_ITEMMGMT_GROUP'
513 AND ((I.INVENTORY_ITEM_ID IS NOT NULL AND I.INVENTORY_ITEM_ID = p_item1)
514 OR
515 (I.ITEM_NUMBER IS NOT NULL AND I.ITEM_NUMBER = p_ss_record)
516 )
517 AND I.DATA_SET_ID = p_batch_id
518 AND NVL(I.PROCESS_STATUS, -1) <= 1
519 AND (REVISION IS NULL OR REVISION = c_revision)
520 AND I.ORGANIZATION_ID = p_org_id
521 AND I.ATTR_GROUP_INT_NAME = AG.ATTR_GROUP_name
522 AND ( I.DATA_LEVEL_ID = AG.DATA_LEVEL_ID
523 OR I.DATA_LEVEL_NAME = AG.DATA_LEVEL_INTERNAL_NAME )
524 AND AG.APPLICATION_ID = 431
525 AND AG.MULTI_ROW_CODE = 'N';
526
527 ----------------------------------------------------------------------
528 -- CURSOR TO GET USER DEFINED ATTRIBUTES FROM THE INTERFACE TABLE
529 -- FOR GIVEN ATTRIBUTE GROUP FOR A PDH ITEM
530 ----------------------------------------------------------------------
531 CURSOR cr_usr_intf_pdh (c_attr_group_int_name VARCHAR2, p_data_level_id NUMBER) IS
532 SELECT
533 AV.ATTR_ID,
534 AGV.ATTR_GROUP_ID,
535 I.ATTR_VALUE_STR,
536 I.ATTR_VALUE_NUM,
537 I.ATTR_VALUE_DATE,
538 I.ATTR_DISP_VALUE,
539 I.REVISION_ID,
540 I.REVISION,
541 I.ATTR_VALUE_UOM,
542 I.ATTR_UOM_DISP_VALUE,
543 AV.ATTR_DISPLAY_NAME,
544 AGV.ATTR_GROUP_DISP_NAME,
545 I.ATTR_INT_NAME ,
546 I.ATTR_GROUP_INT_NAME,
547 AGV.DATA_LEVEL_ID, -- R12C: Added
548 AGV.DATA_LEVEL_INTERNAL_NAME, -- R12C: Added
549 AV.DATABASE_COLUMN,
550 AV.VALUE_SET_ID,
551 AV.VALIDATION_CODE,
552 AV.DATA_TYPE_CODE,
553 AV.UOM_CLASS
554 FROM
555 EGO_ITM_USR_ATTR_INTRFC I,
556 EGO_ATTRS_V AV,
557 EGO_ATTR_GROUPS_DL_V AGV
558 WHERE AV.ATTR_GROUP_NAME = AGV.ATTR_GROUP_NAME
559 AND ((I.INVENTORY_ITEM_ID IS NOT NULL AND I.INVENTORY_ITEM_ID = p_item1)
560 OR
561 (I.ITEM_NUMBER IS NOT NULL AND I.ITEM_NUMBER = p_ss_record)
562 )
563 AND AV.ATTR_NAME = I.ATTR_INT_NAME
564 AND AV.ATTR_GROUP_NAME = I.ATTR_GROUP_INT_NAME
565 AND I.ATTR_GROUP_INT_NAME = c_attr_group_int_name
566 AND I.DATA_LEVEL_ID = p_data_level_id
567 AND NVL(AGV.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = 'EGO_ITEMMGMT_GROUP'
568 AND I.DATA_SET_ID = p_batch_id
569 AND NVL(I.PROCESS_STATUS, -1) <= 1
570 AND I.REVISION IS NULL
571 AND I.ORGANIZATION_ID = p_org_id
572 AND ( I.DATA_LEVEL_ID = AGV.DATA_LEVEL_ID
573 OR I.DATA_LEVEL_NAME = AGV.DATA_LEVEL_INTERNAL_NAME )
574 AND AV.APPLICATION_ID = 431
575 AND AGV.APPLICATION_ID = AV.APPLICATION_ID;
576
577
578 ------------------------------------------------------
579 -- TO GET THE ATTRIBUTES FOR GIVEN ATTRIBUTE GROUP --
580 -- USER DEFINED ATTRIBUTES WITH RIVISION --
581 ------------------------------------------------------
582 CURSOR cr_rev_usr_intf_pdh (c_attr_group_int_name VARCHAR2, c_revision VARCHAR2) IS
583 SELECT
584 AV.ATTR_ID,
585 AGV.ATTR_GROUP_ID,
586 I.ATTR_VALUE_STR,
587 I.ATTR_VALUE_NUM,
588 I.ATTR_VALUE_DATE,
589 I.ATTR_DISP_VALUE,
590 I.REVISION_ID,
591 I.REVISION,
592 I.ATTR_VALUE_UOM,
593 I.ATTR_UOM_DISP_VALUE,
594 AV.ATTR_DISPLAY_NAME,
595 AGV.ATTR_GROUP_DISP_NAME,
596 I.ATTR_INT_NAME ,
597 I.ATTR_GROUP_INT_NAME,
598 AGV.DATA_LEVEL_ID, -- R12C: Added
599 AGV.DATA_LEVEL_INTERNAL_NAME, -- R12C: Added
600 AV.DATABASE_COLUMN,
601 AV.VALUE_SET_ID,
602 AV.VALIDATION_CODE,
603 AV.DATA_TYPE_CODE,
604 AV.UOM_CLASS
605 FROM
606 EGO_ITM_USR_ATTR_INTRFC I,
607 EGO_ATTRS_V AV,
608 EGO_ATTR_GROUPS_DL_V AGV
609 WHERE AV.ATTR_GROUP_NAME = AGV.ATTR_GROUP_NAME
610 AND AV.ATTR_NAME = I.ATTR_INT_NAME
611 AND AV.ATTR_GROUP_NAME = I.ATTR_GROUP_INT_NAME
612 AND I.ATTR_GROUP_INT_NAME = c_attr_group_int_name
613 AND NVL(AGV.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = 'EGO_ITEMMGMT_GROUP'
614 AND I.DATA_SET_ID = p_batch_id
615 AND NVL(I.PROCESS_STATUS, -1) <= 1
616 AND I.REVISION = c_revision
617 AND ((I.INVENTORY_ITEM_ID IS NOT NULL AND I.INVENTORY_ITEM_ID = p_item1)
618 OR
619 (I.ITEM_NUMBER IS NOT NULL AND I.ITEM_NUMBER = p_ss_record)
620 )
621 AND I.ORGANIZATION_ID = p_org_id
622 AND ( I.DATA_LEVEL_ID = AGV.DATA_LEVEL_ID
623 OR I.DATA_LEVEL_NAME = AGV.DATA_LEVEL_INTERNAL_NAME )
624 AND AV.APPLICATION_ID = 431
625 AND AGV.APPLICATION_ID = AV.APPLICATION_ID;
626
627 -------------------------------------------------------
628 -- CURSOR TO GET THE GTIN ATTRIBUTES IN INTERFACE TABLE
629 -------------------------------------------------------
630 CURSOR cr_dd_intf_pdh IS
631 SELECT
632 AV.ATTR_ID,
633 AGV.ATTR_GROUP_ID,
634 I.ATTR_VALUE_STR,
635 I.ATTR_VALUE_NUM,
636 I.ATTR_VALUE_DATE,
637 I.ATTR_DISP_VALUE,
638 I.REVISION_ID,
639 I.ATTR_VALUE_UOM,
640 I.ATTR_UOM_DISP_VALUE,
641 AV.ATTR_DISPLAY_NAME,
642 AGV.ATTR_GROUP_DISP_NAME,
643 AGV.VIEW_PRIVILEGE_ID,
644 I.ATTR_INT_NAME ,
645 I.ATTR_GROUP_INT_NAME,
646 AV.DATABASE_COLUMN,
647 AV.VALUE_SET_ID,
648 AV.VALIDATION_CODE,
649 AV.DATA_TYPE_CODE,
650 AV.UOM_CLASS
651 FROM
652 EGO_ITM_USR_ATTR_INTRFC I,
653 EGO_ATTRS_V AV,
654 EGO_ATTR_GROUPS_DL_V AGV
655 WHERE AV.ATTR_GROUP_NAME = AGV.ATTR_GROUP_NAME
656 AND AGV.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
657 AND AV.ATTR_NAME = I.ATTR_INT_NAME
658 AND AV.ATTR_GROUP_NAME = I.ATTR_GROUP_INT_NAME
659 AND I.DATA_SET_ID = p_batch_id
660 AND ((I.INVENTORY_ITEM_ID IS NOT NULL AND I.INVENTORY_ITEM_ID = p_item1)
661 OR
662 (I.ITEM_NUMBER IS NOT NULL AND I.ITEM_NUMBER = p_ss_record)
663 )
664 AND I.organization_id = p_org_id
665 AND NVL(I.PROCESS_STATUS, -1) <= 1
666 AND AV.APPLICATION_ID = 431
667 AND AGV.APPLICATION_ID = AV.APPLICATION_ID
668 ORDER BY AGV.ATTR_GROUP_ID;
669
670 CURSOR cr_match_item_rev IS
671 SELECT
672 INVENTORY_ITEM_ID,
673 REVISION_ID,
674 SOURCE_SYSTEM_REFERENCE
675 FROM EGO_ITEM_MATCHES
676 WHERE INVENTORY_ITEM_ID IN (p_item1, p_item2, p_item3, p_item4)
677 AND SOURCE_SYSTEM_ID = p_ss_code
678 AND SOURCE_SYSTEM_REFERENCE = p_ss_record
679 AND BATCH_ID = p_batch_id
680 AND ORGANIZATION_id = p_org_id;
681
682 l_compare_tbl SYSTEM.EGO_COMPARE_VIEW_TABLE ;
683 err_compare_tbl SYSTEM.EGO_COMPARE_VIEW_TABLE ;
684 l_compare_REc SYSTEM.EGO_COMPARE_VIEW_REC;
685 err_compare_rec SYSTEM.EGO_COMPARE_VIEW_REC;
686 l_str_value VARCHAR2(4000); --EGO_MTL_SY_ITEMS_EXT_B.C_EXT_ATTR40%TYPE;
687 l_sel_clause VARCHAR2(32000); --keeping the max limit
688 l_int_val VARCHAR2(4000); --EGO_MTL_SY_ITEMS_EXT_B.C_EXT_ATTR40%TYPE;
689 l_val_set_clause VARCHAR2(12000); --keeping it that long for safety
690 l_sql_query VARCHAR2(32000); --keeping the max limit
691 l_val VARCHAR2(4000);
692
693 l_attr_id NUMBER;
694 l_n_tbl NUMBER;
695 l_num_value NUMBER;
696 l_ignore NUMBER;
697 l_cnt NUMBER;
698 l_count NUMBER;
699 l_idx NUMBER;
700 l_start NUMBER;
701
702 --varibles for Dynamic Cursors
703 cr_ud_attr INTEGER;
704 cr_dd_attr INTEGER;
705 cr_msi_attr INTEGER;
706 cr_msi_intf INTEGER;
707
708 l_date_value DATE;
709 l_sql_msi VARCHAR2(32000); --keeping the max limit
710 l_default_sel NUMBER;
711 l_item_ID NUMBER;
712 l_temp VARCHAR2(50);
713 l_col_idx NUMBER;
714 i NUMBER;
715 l_fmt VARCHAR2(1);
716 l_disp_val VARCHAR2(4000);
717 l_msii_sql VARCHAR2(20000);
718 l_lkup_str VARCHAR2(400);
719 l_temp_query VARCHAR2(400);
720 l_catalog_id NUMBER;
721 l_hier_catalog_id NUMBER;
722 l_lifecycle_id NUMBER;
723 l_phase_id NUMBER;
724 l_primay_ag_disp_name FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
725 l_primary_ag_int_name FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
726 l_revision MTL_ITEM_REVISIONS.REVISION%TYPE;
727 l_rev_attr_count NUMBER;
728 l_attr_group_display_name VARCHAR2(200);
729
730 -- Variables for Security issues
731 l_attGrp_old VARCHAR2(80);
732 l_attGrp_new VARCHAR2(80);
733 l_priv_name VARCHAR2(480);
734 l_priv_item1 VARCHAR2(1);
735 l_priv_item2 VARCHAR2(1);
736 l_priv_item3 VARCHAR2(1);
737 l_priv_item4 VARCHAR2(1);
738 l_party_id VARCHAR2(100);
739 l_user_id NUMBER;
740 l_pdh_revision VARCHAR(5);
741 --Bug#5043002
742 TYPE TypeNum IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
743 l_inv_rev_id_tbl TypeNum ;
744 k NUMBER ;
745 l_item_lable VARCHAR2(50);
746 l_itemOrg_lable VARCHAR2(50);
747 l_itemSup_lable VARCHAR2(50);
748 l_itemSupSite_lable VARCHAR2(50);
749 l_itemSupSiteOrg_lable VARCHAR2(50);
750 l_itemRev_lable VARCHAR2(50);
751
752 --Bug#5043002
753
754
755 TYPE ATTR_META IS RECORD
756 ( ATTR_DISPLAY_NAME VARCHAR2(240) := ''
757 ,ATTR_GROUP_NAME VARCHAR2(240)
758 ,ATTR_ID NUMBER
759 ,VIEW_PRIVILEGE_NAME VARCHAR2(480)
760 ,UOM_CLASS VARCHAR2(10)
761 ,ATTR_NAME VARCHAR2(240)
762 ,VALUE_SET_ID NUMBER
763 ,VALIDATION_CODE VARCHAR2(1)
764 ,DATA_TYPE_CODE VARCHAR2(1)
765 ,ATTR_GROUP_DISP_NAME VARCHAR2(240)
766 ,DATABASE_COLUMN VARCHAR2(240)
767 ,FLAG VARCHAR2(1) );
768 TYPE ATTR_M_DATA_TBL IS TABLE OF ATTR_META ;
769 TYPE T IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
770 TYPE x IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
771 TYPE A IS TABLE OF VARCHAR2(1000) INDEX BY VARCHAR2(50);
772 TYPE C IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
773 TYPE UOM_CLASS_NAMES IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
774 TYPE VALUE_SET_ID_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
775 G_META X;
776 UOM UOM_CLASS_NAMES;
777 UOM_USER_CODE UOM_CLASS_NAMES; --saves UOM code entered by user
778 UOM_DISP_VAL UOM_CLASS_NAMES; --saves UOM display value entered by user.
779 VSID VALUE_SET_ID_TABLE;
780
781 l_attr_data ATTR_META;
782 l_attr_data_tbl ATTR_M_DATA_TBL; -- Saves meta data for all attributes
783 l_attr_meta_tbl ATTR_M_DATA_TBL; -- Saves meta data for attributes for which SS data Exists
784 l_p_atr_sql A ;
785 l_ch_policy_tbl C;
786 l_ch_policy VARCHAR2(100);
787 l_is_policy VARCHAR2(1);
788 l_party_id_num NUMBER;
789 l_ss_id NUMBER;
790 is_pdh_batch BOOLEAN;
791 -- This is required to find out index of each row in case we add these
792 -- extra rows.
793 l_supplier_rows_count NUMBER; -- Keep Count of number of supplier rows added.
794 -- If supplierId is passed l_supplier_rows_count :=1
795 -- if supplierSiteUd is passed l_supplier_rows_count := 2
796
797
798 BEGIN
799 Debug_Message('Starting GET_COMPARED_DATA at - '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
800 Debug_Message('Parameters are p_ss_code, p_ss_record, p_batch_id, p_mode, p_item1, p_item2, p_item3, p_item4, p_org_Id=');
801 Debug_Message(TO_CHAR(p_ss_code)||', '||p_ss_record||', '||TO_CHAR(p_batch_id)||', '||TO_CHAR(p_mode)||
802 ', '||TO_CHAR(p_item1)||', '||TO_CHAR(p_item2)||', '||TO_CHAR(p_item3)||', '||TO_CHAR(p_item4)||', '||TO_CHAR(p_org_Id));
803 -- GETTING THE PARTY_ID FOR THE USER --
804 l_user_id := FND_GLOBAL.USER_ID;
805 BEGIN
806 SELECT party_id INTO l_party_id_num
807 FROM ego_user_v
808 WHERE user_id = l_user_id;
809 EXCEPTION WHEN NO_DATA_FOUND THEN
810 err_compare_tbl := SYSTEM.EGO_COMPARE_VIEW_TABLE();
811 err_compare_rec := SYSTEM.EGO_COMPARE_VIEW_REC('', '', '','', '', '','', '', '','', '','','','','');
812 --err_compare_rec.ATTR_GROUP_DISP_NAME := 'Encountered error, No search conducted';
813 err_compare_rec.ATTR_GROUP_DISP_NAME := FND_MESSAGE.GET_STRING('EGO', 'EGO_PERSON_INVALID');
814 err_compare_tbl.EXTEND();
815 err_compare_tbl(1) := err_compare_rec;
816 Debug_Message('Error - '||err_compare_rec.ATTR_GROUP_DISP_NAME);
817 Debug_Message('Done GET_COMPARED_DATA with error');
818 RETURN err_compare_tbl;
819 END;
820
821 l_party_id := 'HZ_PARTY:' || TO_CHAR(l_party_id_num);
822
823 Debug_Message('Party_id = '||l_party_id);
824
825 -- Query for getting the source_system_id to which this item do belong to
826 SELECT source_system_id INTO l_ss_id
827 FROM ego_import_batches_b
828 WHERE batch_id = p_batch_id;
829
830 IF l_ss_id = EGO_IMPORT_PVT.get_pdh_source_system_id THEN
831 is_pdh_batch := TRUE;
832 ELSE
833 is_pdh_batch := FALSE;
834 END IF; -- IF p_ss_code = EGO_IMPORT_PVT.get_pdh_source_system_id THEN
835
836 l_is_policy := 'N';
837 FND_MESSAGE.SET_NAME('EGO', 'EGO_ERP_MAIN_ATTR_GRP');
838 l_primay_ag_disp_name := FND_MESSAGE.GET();
839 FND_MESSAGE.SET_NAME('EGO', 'EGO_ERP_MAIN_ATTR_GRP');
840 l_primary_ag_int_name := FND_MESSAGE.GET();
841 FND_MESSAGE.SET_NAME('EGO', 'EGO_ITEM');
842 l_item_lable := FND_MESSAGE.GET();
843 FND_MESSAGE.SET_NAME('EGO', 'EGO_ITEM_ORGANIZATION');
844 l_itemOrg_lable := FND_MESSAGE.GET();
845 FND_MESSAGE.SET_NAME('EGO', 'EGO_ITEM_SUPPLIER');
846 l_itemSup_lable := FND_MESSAGE.GET();
847 FND_MESSAGE.SET_NAME('EGO', 'EGO_ITEM_SUPPLIR_SITE');
848 l_itemSupSite_lable := FND_MESSAGE.GET();
849 FND_MESSAGE.SET_NAME('EGO', 'EGO_ITEM_SUPPLIR_SITE_STORE');
850 l_itemSupSiteOrg_lable := FND_MESSAGE.GET();
851 FND_MESSAGE.SET_NAME('EGO', 'EGO_ITEM_REVISION');
852 l_itemRev_lable := FND_MESSAGE.GET();
853
854 l_compare_tbl := SYSTEM.EGO_COMPARE_VIEW_TABLE();
855 l_compare_rec := SYSTEM.EGO_COMPARE_VIEW_REC('', '', '','','','','','','','','','','','','');
856
857 Debug_Message('Processing Item attributes (Primary and Operational) for Source System');
858 -- If called form confirmed tab - p_mode = 1
859 -- when p_mode is 1, THEN we need to compute the change policy
860 -- so finding out the change policy on each item operational attribute group
861 IF P_MODE = 1 AND p_item1 IS NOT NULL THEN
862 Debug_Message('Mode is 1, so computing the change policy');
863 -- getting the item_catalog_group, lifecycle_id and phase_id
864 SELECT LIFECYCLE_ID, ITEM_CATALOG_GROUP_ID, CURRENT_PHASE_ID
865 INTO l_lifecycle_id, l_catalog_id, l_phase_id
866 FROM MTL_SYSTEM_ITEMS_B
867 WHERE INVENTORY_ITEM_ID = p_item1
868 AND ORGANIZATION_ID = p_org_id;
869
870 IF l_lifecycle_id IS NOT NULL AND l_catalog_id IS NOT NULL THEN
871 BEGIN
872 SELECT ic.item_catalog_group_id
873 INTO l_hier_catalog_id
874 FROM mtl_item_catalog_groups_b ic
875 WHERE EXISTS
876 (SELECT olc.object_classification_code CatalogId
877 FROM ego_obj_type_lifecycles olc
878 WHERE olc.object_id = (select object_id from fnd_objects where obj_name = 'EGO_ITEM')
879 AND olc.lifecycle_id = l_lifecycle_id
880 AND olc.object_classification_code = ic.item_catalog_group_id
881 )
882 AND ROWNUM = 1
883 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
884 START WITH item_catalog_group_id = l_catalog_id;
885 EXCEPTION WHEN NO_DATA_FOUND THEN
886 l_hier_catalog_id := l_catalog_id;
887 END;
888 END IF; --IF l_lifecycle_id IS NOT NULL AND l_catalog_id IS NOT NULL THEN
889
890 IF l_hier_catalog_id IS NOT NULL THEN
891 l_catalog_id := l_hier_catalog_id;
892 END IF; --IF l_hier_catalog_id IS NOT NULL THEN
893
894 Debug_Message('Lifecycle_id, Item_Catalog_Group_Id, Current_Phase_id='||TO_CHAR(l_lifecycle_id)||', '||TO_CHAR(l_catalog_id)||', '||TO_CHAR(l_phase_id));
895 IF (l_phase_id IS NOT NULL) THEN
896 -- if lifecycle phase is not NULL THEN the Change Policy can exists
897 l_is_policy := 'Y';
898 -- finding the policy for each operational attribute
899 FOR rec in cr_op_attr_grps LOOP
900 Debug_Message('Getting change Policy for operational attribute Attr_Group_Id, Attr_Group_Name='||TO_CHAR(rec.ATTR_GROUP_ID)||', '||rec.ATTR_GROUP_NAME);
901 ENG_CHANGE_POLICY_PKG.GetChangePolicy
902 ( p_policy_object_name => 'CATALOG_LIFECYCLE_PHASE'
903 , p_policy_code => 'CHANGE_POLICY'
904 , p_policy_pk1_value => l_catalog_id
905 , p_policy_pk2_value => l_lifecycle_id
906 , p_policy_pk3_value => l_phase_id
907 , p_policy_pk4_value => NULL
908 , p_policy_pk5_value => NULL
909 , p_attribute_object_name => 'EGO_CATALOG_GROUP'
910 , p_attribute_code => 'ATTRIBUTE_GROUP'
911 , p_attribute_value => rec.ATTR_GROUP_ID
912 , x_policy_value => l_ch_policy
913 );
914
915 Debug_Message('Change Policy is '||l_ch_policy);
916 IF INSTR(l_ch_policy,'NOT') > 0 THEN
917 l_ch_policy_tbl(rec.ATTR_GROUP_NAME) := 'N';
918 ELSIF INSTR(l_ch_policy,'ALLOWED') > 0 THEN
919 l_ch_policy_tbl(rec.ATTR_GROUP_NAME) := 'Y';
920 ELSIF INSTR(l_ch_policy,'CHANGE') > 0 THEN
921 l_ch_policy_tbl(rec.ATTR_GROUP_NAME) := 'C';
922 END IF; -- IF INSTR(l_ch_policy,'NOT') > 0 THEN
923 END LOOP; -- FOR rec in cr_op_attr_grps LOOP
924 END IF; --IF (l_phase_id IS NOT NULL) THEN
925 Debug_Message('Computing Change Policy for operational attribute groups - Done');
926 END IF; -- IF P_MODE = 1 AND p_item1 IS NOT NULL THEN
927
928 -- for some item attributes, the display value does not comes from value set
929 -- but comes from some specific SQLs. So storing these SQLs in local array
930 l_lkup_str :=' SELECT F.MEANING '||
931 ' FROM FND_LOOKUP_VALUES F'||
932 ' WHERE F.LANGUAGE = USERENV(''LANG'')'||
933 ' AND F.LOOKUP_TYPE = ';
934
935 l_p_atr_sql('ITEM_TYPE') := l_lkup_str || ' ''ITEM_TYPE''' ||
936 ' AND F.LOOKUP_CODE = ';
937 l_p_atr_sql('ALLOWED_UNITS_LOOKUP_CODE') := l_lkup_str || '''MTL_CONVERSION_TYPE'' ' ||
938 ' AND F.LOOKUP_CODE = ';
939 l_p_atr_sql('ONT_PRICING_QTY_SOURCE') := l_lkup_str || '''INV_PRICING_UOM_TYPE'' ' ||
940 ' AND F.LOOKUP_CODE = ';
941 l_p_atr_sql('SECONDARY_DEFAULT_IND') := l_lkup_str || '''INV_DEFAULTING_UOM_TYPE'' ' ||
942 ' AND F.LOOKUP_CODE = ';
943 l_p_atr_sql('TRACKING_QUANTITY_IND') := l_lkup_str || '''INV_TRACKING_UOM_TYPE'' ' ||
944 ' AND F.LOOKUP_CODE = ';
945
946 l_p_atr_sql('PRIMARY_UOM_CODE') := ' SELECT UOMTL.UNIT_OF_MEASURE_TL '||
947 ' FROM MTL_UNITS_OF_MEASURE_TL UOMTL' ||
948 ' WHERE UOMTL.LANGUAGE = USERENV(''LANG'') ' ||
949 ' AND UOMTL.UOM_CODE = ';
950 l_p_atr_sql('SECONDARY_UOM_CODE') := l_p_atr_sql('PRIMARY_UOM_CODE');
951
952 l_p_atr_sql('UOM_CODE') := l_p_atr_sql('PRIMARY_UOM_CODE') || ':1';
953
954 l_p_atr_sql('UOM_CLASS') := ' SELECT UOMTL.UNIT_OF_MEASURE_TL '||
955 ' FROM MTL_UNITS_OF_MEASURE_TL UOMTL' ||
956 ' WHERE UOMTL.LANGUAGE = USERENV(''LANG'') ' ||
957 ' AND UOMTL.BASE_UOM_FLAG = ''Y''' ||
958 ' AND UOMTL.UOM_CLASS = :1 ';
959
960 l_p_atr_sql('ITEM_CATALOG_GROUP_ID') := ' SELECT ICGKFV.CONCATENATED_SEGMENTS ' ||
961 ' FROM MTL_ITEM_CATALOG_GROUPS_B_KFV ICGKFV ' ||
962 ' WHERE ICGKFV.ITEM_CATALOG_GROUP_ID = ' ;
963 l_p_atr_sql('CURRENT_PHASE_ID') := ' SELECT LCP.NAME '||
964 ' FROM PA_EGO_LIFECYCLES_PHASES_V LCP '||
965 ' WHERE LCP.OBJECT_TYPE = ''PA_TASKS'' '||
966 ' AND LCP.PROJ_ELEMENT_ID = ';
967 l_p_atr_sql('LIFECYCLE_ID') := ' SELECT LC.NAME ' ||
968 ' FROM PA_EGO_LIFECYCLES_PHASES_V LC ' ||
969 ' WHERE LC.OBJECT_TYPE = ''PA_STRUCTURES'''||
970 ' AND LC.PROJ_ELEMENT_ID = ' ;
971
972 l_p_atr_sql('LONG_DESCRIPTION') := ' SELECT ITL.LONG_DESCRIPTION '||
973 ' FROM MTL_SYSTEM_ITEMS_TL ITL '||
974 ' WHERE ITL.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID '||
975 ' AND ITL.ORGANIZATION_ID = I.ORGANIZATION_ID '||
976 ' AND ITL.LANGUAGE = USERENV(''LANG'') ';
977 l_p_atr_sql('DESCRIPTION') := ' SELECT ITL.DESCRIPTION '||
978 ' FROM MTL_SYSTEM_ITEMS_TL ITL '||
979 ' WHERE ITL.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID '||
980 ' AND ITL.ORGANIZATION_ID = I.ORGANIZATION_ID '||
981 ' AND ITL.LANGUAGE = USERENV(''LANG'') ';
982 l_p_atr_sql('ITEM_NUMBER') := ' I.CONCATENATED_SEGMENTS ';
983 l_p_atr_sql('TRADE_ITEM_DESCRIPTOR') := ' SELECT DISPLAY_NAME ' ||
984 ' FROM EGO_VALUE_SET_VALUES_V ' ||
985 ' WHERE VALUE_SET_NAME = ''TradeItemDescVS'' ' ||
986 ' AND INTERNAL_NAME = ';
987
988 l_p_atr_sql('STYLE_ITEM_FLAG') := l_lkup_str || ' ''EGO_YES_NO'' ' ||
989 ' AND F.LOOKUP_CODE = ';
990
991 l_p_atr_sql('STYLE_ITEM_NUMBER') := '( SELECT CONCATENATED_SEGMENTS ' ||
992 ' FROM MTL_SYSTEM_ITEMS_KFV MSIKFV ' ||
993 ' WHERE MSIKFV.INVENTORY_ITEM_ID = I.STYLE_ITEM_ID ' ||
994 ' AND MSIKFV.ORGANIZATION_ID = I.ORGANIZATION_ID '||
995 ') ';
996
997 l_p_atr_sql('GDSN_OUTBOUND_ENABLED_FLAG') := l_lkup_str || ' ''EGO_YES_NO'' ' ||
998 ' AND F.LOOKUP_CODE = ';
999
1000 l_p_atr_sql('INVENTORY_ITEM_STATUS_CODE'):= ' SELECT INVENTORY_ITEM_STATUS_CODE_TL ' ||
1001 ' FROM mtl_item_status ' ||
1002 ' WHERE INVENTORY_ITEM_STATUS_CODE = ' ;
1003 l_default_sel := 0;
1004 l_attr_data_tbl := ATTR_M_DATA_TBL();
1005 l_attr_meta_tbl := ATTR_M_DATA_TBL();
1006 l_msii_sql := 'SELECT INVENTORY_ITEM_ID ';
1007 l_default_sel := 1;
1008
1009 -------------------------------------------------------------------------------
1010 -- Getting meda data for primary attributes
1011 -- Saving the meta data about each attribute in meta data table l_attr_data_tbl
1012 -- Based on this metadata, also preparing the query l_msii_sql for getting data
1013 -- from interface and production table
1014 -------------------------------------------------------------------------------
1015 Debug_Message('Started getting metadata for Primary Attributes');
1016 FOR l_pr_attr_rec IN cr_primary_attr LOOP
1017 l_attr_data_tbl.extend();
1018 l_attr_data.ATTR_DISPLAY_NAME := l_pr_attr_rec.MEANING;
1019 --Debug_Message('Primary Attribute - ' || l_attr_data.ATTR_DISPLAY_NAME);
1020 -- Saving value set ids for primary attributes in meta data table
1021 IF l_pr_attr_rec.LOOKUP_CODE IN ('DUAL_UOM_DEVIATION_HIGH', 'DUAL_UOM_DEVIATION_LOW', 'CREATION_DATE', 'CREATED_BY') THEN
1022 l_attr_data.VALUE_SET_ID := 0; -- VALUE SET DOES NOT EXISTS
1023 ELSE
1024 l_attr_data.VALUE_SET_ID := -1; -- NO VALUE SET, but display value comes from special SQLs
1025 END IF; -- IF l_pr_attr_rec.LOOKUP_CODE IN ('DUAL
1026
1027 -- Saving data type code in meta data table
1028 IF l_pr_attr_rec.LOOKUP_CODE IN ('ITEM_TYPE', 'DESCRIPTION', 'LONG_DESCRIPTION', 'APPROVAL_STATUS',
1029 'INVENTORY_ITEM_STATUS_CODE', 'ONT_PRICING_QTY_SOURCE',
1030 'PRIMARY_UOM_CODE', 'SECONDARY_DEFAULT_IND', 'SECONDARY_UOM_CODE',
1031 'TRACKING_QUANTITY_IND', 'ALLOWED_UNITS_LOOKUP_CODE',
1032 'ITEM_CATALOG_GROUP_ID', 'CURRENT_PHASE_ID', 'LIFECYCLE_ID' ,
1033 'TRADE_ITEM_DESCRIPTOR', 'STYLE_ITEM_FLAG', 'STYLE_ITEM_NUMBER' ,
1034 'GDSN_OUTBOUND_ENABLED_FLAG' ) THEN
1035 l_attr_data.DATA_TYPE_CODE := G_CHAR_FORMAT;
1036 ELSIF l_pr_attr_rec.LOOKUP_CODE IN ('CREATION_DATE') THEN
1037 l_attr_data.DATA_TYPE_CODE := G_DATE_FORMAT;
1038 ELSE
1039 l_attr_data.DATA_TYPE_CODE := G_NUMBER_FORMAT;
1040 END IF; -- IF l_pr_attr_rec.LOOKUP_CODE IN
1041
1042 l_attr_data.ATTR_GROUP_DISP_NAME := l_primay_ag_disp_name;
1043 l_attr_data.ATTR_GROUP_NAME := l_primary_ag_int_name;
1044 l_attr_data.DATABASE_COLUMN := l_pr_attr_rec.LOOKUP_CODE;
1045 l_attr_data.FLAG := 'N';
1046 l_attr_data.VIEW_PRIVILEGE_NAME := 'NOTAPPLICABLE';
1047 l_attr_data_tbl(l_attr_data_tbl.LAST) := l_attr_data;
1048 l_msii_sql := l_msii_sql || ', ' || l_pr_attr_rec.LOOKUP_CODE ;
1049 END LOOP; -- FOR l_pr_attr_rec IN cr_primary_attr LOOP
1050 Debug_Message('Done getting metadata for Primary Attributes');
1051
1052 ------------------------------------------------------------------------------
1053 -- Get meta data for operational attributes.
1054 -- Saving the meta data about each attribute in meta data table l_attr_data_tbl
1055 -- Based on this metadata, also preparing the query l_msii_sql for getting data
1056 -- from interface and production table
1057 -------------------------------------------------------------------------------
1058 Debug_Message('Started getting metadata for Operational Attributes');
1059 FOR l_attr_name_rec IN cr_attr_info LOOP
1060 l_attr_data_tbl.extend();
1061 l_attr_data.ATTR_DISPLAY_NAME := l_attr_name_rec.ATTR_DISPLAY_NAME;
1062 l_attr_data.VALUE_SET_ID := NVL(l_attr_name_rec.VALUE_SET_ID, 0);
1063 l_attr_data.VALIDATION_CODE := l_attr_name_rec.VALIDATION_CODE ;
1064 l_attr_data.DATA_TYPE_CODE := l_attr_name_rec.DATA_TYPE_CODE;
1065 l_attr_data.ATTR_GROUP_DISP_NAME := l_attr_name_rec.ATTR_GROUP_DISP_NAME;
1066 l_attr_data.ATTR_GROUP_NAME := l_attr_name_rec.ATTR_GROUP_NAME;
1067 l_attr_data.DATABASE_COLUMN := l_attr_name_rec.DATABASE_COLUMN;
1068 l_attr_data.FLAG := 'N';
1069 l_attr_data.VIEW_PRIVILEGE_NAME := 'NOTAPPLICABLE';
1070 l_attr_data.UOM_CLASS := l_attr_name_rec.UOM_CLASS;
1071 l_attr_data.ATTR_NAME := l_attr_name_rec.ATTR_NAME;
1072 l_attr_data.ATTR_ID := l_attr_name_rec.ATTR_ID;
1073 l_attr_data_tbl(l_attr_data_tbl.LAST) := l_attr_data;
1074 l_msii_sql := l_msii_sql || ', ' || l_attr_name_rec.DATABASE_COLUMN ;
1075 END LOOP; -- FOR l_attr_name_rec IN cr_attr_info LOOP
1076 Debug_Message('Done getting metadata for Operationl Attributes');
1077
1078 -- Preaparing dynamic sql cursor from query l_msii_sql
1079 -- to get data from interface table
1080 Debug_Message('Preparing SQL to get data from interface table for item primary and operational attributes.');
1081 cr_msi_intf := DBMS_SQL.OPEN_CURSOR;
1082
1083 -- if the batch is PDH batch
1084 -- we need to match inventory_item_id or item number in interface table
1085 -- with the p_item1 or p_ss_record
1086 IF is_pdh_batch THEN
1087 -- PDH item
1088 Debug_Message('Prepared query for Primary and Operational Attributes for PDH item');
1089 l_msii_sql := l_msii_sql || ' FROM MTL_SYSTEM_ITEMS_INTERFACE I '
1090 || ' WHERE ORGANIZATION_ID = :1'
1091 || ' AND SET_PROCESS_ID = :2 '
1092 || ' AND ((I.INVENTORY_ITEM_ID IS NOT NULL AND I.INVENTORY_ITEM_ID = :3) '
1093 || ' OR '
1094 || ' (I.ITEM_NUMBER IS NOT NULL AND I.ITEM_NUMBER = :4)) '
1095 || ' AND NVL(PROCESS_FLAG, -1) <= 1 ' ;
1096
1097
1098 ELSE
1099 -- NON PDH item
1100 Debug_Message('Prepared query for Primary and Operational Attributes for NON-PDH item');
1101 l_msii_sql := l_msii_sql || ' FROM MTL_SYSTEM_ITEMS_INTERFACE I '
1102 || ' WHERE ORGANIZATION_ID = :1'
1103 || ' AND SOURCE_SYSTEM_ID = :2 '
1104 || ' AND SOURCE_SYSTEM_REFERENCE = :3'
1105 || ' AND SET_PROCESS_ID = :4 '
1106 || ' AND NVL(PROCESS_FLAG, -1) < 1 ' ;
1107 -- Adding bundleId only in case bundleId is passed.
1108 IF p_bundle_id IS NOT NULL THEN
1109 l_msii_sql := l_msii_sql || ' AND BUNDLE_ID = :5 ';
1110 END IF;
1111 END IF; --IF is_pdh_batch THEN
1112
1113 Debug_Message('SQL is - ');
1114 FOR l in 1..(CEIL(LENGTH(l_msii_sql)/1000)) LOOP
1115 Debug_Message(SUBSTR(l_msii_sql, ((l-1)*1000) + 1, 1000));
1116 END LOOP; --FOR l in 1..(CEIL(LENGTH(l_msii_sql)/1000)) LOOP
1117
1118 Debug_Message('Parsing the SQL');
1119 DBMS_SQL.PARSE(cr_msi_intf, l_msii_sql, DBMS_SQL.native);
1120 Debug_Message('Done Parsing the SQL');
1121
1122 -- Defining columns for l_msii_sql
1123 -- First column will be number as l_msii_sql has inventory_item_id as first selected column always.
1124 -- depending on the metadata of primary and operational attributes, we define here the type of column
1125 Debug_Message('Defining columns of SQL');
1126 DBMS_SQL.DEFINE_COLUMN(cr_msi_intf ,1 ,l_num_value);
1127 l_count := l_attr_data_tbl.LAST;
1128 Debug_Message('Total columns = '||TO_CHAR(l_count));
1129 FOR i IN 1..l_count LOOP
1130 l_fmt := l_attr_data_tbl(i).DATA_TYPE_CODE ;
1131 IF l_fmt = G_NUMBER_FORMAT THEN
1132 DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_num_value);
1133 ELSIF l_fmt = G_CHAR_FORMAT THEN
1134 DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_str_value, 4000);
1135 ELSIF l_fmt = G_TIME_FORMAT OR l_fmt = G_DATE_TIME_FORMAT THEN
1136 DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_date_value);
1137 END IF; --IF l_fmt = G_NUMBER_FORMAT THEN
1138 END LOOP; --FOR i IN 1..l_count LOOP
1139 Debug_Message('Done defining columns of SQL');
1140
1141 Debug_Message('Binding variables');
1142 -- Binding Variables to query.
1143 IF is_pdh_batch THEN
1144 -- For PDH item.
1145 DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1146 DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_batch_id);
1147 DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_item1);
1148 DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_ss_record);
1149 ELSE
1150 -- Non PDH Item.
1151 DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1152 DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_ss_code);
1153 DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_ss_record);
1154 DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_batch_id);
1155 IF p_bundle_id IS NOT NULL THEN
1156 DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':5', p_bundle_id);
1157 END IF;
1158 END IF; --IF is_pdh_batch THEN
1159 Debug_Message('Done Binding variables');
1160
1161 l_ignore := DBMS_SQL.EXECUTE(cr_msi_intf);
1162 Debug_Message('Query Execution Complete');
1163
1164 ------------------------------------------------------------------------------------
1165 -- While finding Privileges of the user upon items, do find privilege one time --
1166 -- for all attributes in an attribute group. Since the attributes are all ordered --
1167 -- by attribute group, We compare the earlier attribute group with present one --
1168 -- and if they are same there is no need to recalculate the privileges --- for --
1169 -- which l_attrGrp_old and l_attrGrp_new are used --
1170 ------------------------------------------------------------------------------------
1171
1172 l_attGrp_old := '';
1173
1174 -----------------------------------------------------------------
1175 -- Fetch Source System Data and keep entering in l_compare_tbl --
1176 -- Since it is required to enter data for other items we do --
1177 -- keep required meta data for the attributes for which source --
1178 -- system data is not NULL. --
1179 -----------------------------------------------------------------
1180 Debug_Message('Fetching the Rows');
1181 WHILE DBMS_SQL.FETCH_ROWS(cr_msi_intf) > 0 LOOP
1182 -- for each column defined previously, get the value of column in l_disp_val
1183
1184 -- Inserting rows for Supplier and SupplierSite in case we are showing
1185 -- any supplier and supplier site information
1186 l_supplier_rows_count := 0;
1187
1188 IF p_supplier_id IS NOT NULL THEN
1189 l_supplier_rows_count := l_supplier_rows_count + 1;
1190 l_compare_tbl.extend();
1191 FND_MESSAGE.SET_NAME('EGO', 'EGO_ITEM_SUPPLIER');
1192 l_compare_rec.ATTR_GROUP_DISP_NAME := FND_MESSAGE.GET();
1193 FND_MESSAGE.SET_NAME('EGO', 'EGO_SUPPLIER_NAME');
1194 l_compare_rec.ATTR_DISP_NAME := FND_MESSAGE.GET();
1195 -- Get the supplier Information
1196 l_temp_query := 'SELECT VENDOR_NAME FROM PO_VENDORS WHERE VENDOR_ID = :1 ';
1197 EXECUTE IMMEDIATE l_temp_query into l_temp using p_supplier_id;
1198 l_compare_rec.SOURCE_SYS_VAL := l_temp;
1199 l_compare_rec.ITEM1 := l_temp;
1200 l_compare_rec.ITEM2 := l_temp;
1201 l_compare_rec.ITEM3 := l_temp;
1202 l_compare_rec.ITEM4 := l_temp;
1203 l_compare_rec.PRIV_ITEM1 := 'T';
1204 l_compare_rec.PRIV_ITEM2 := 'T';
1205 l_compare_rec.PRIV_ITEM3 := 'T';
1206 l_compare_rec.PRIV_ITEM4 := 'T';
1207 l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
1208 END IF;
1209
1210 IF p_supplier_site_id IS NOT NULL THEN
1211 l_supplier_rows_count := l_supplier_rows_count + 1;
1212 l_compare_tbl.extend();
1213 FND_MESSAGE.SET_NAME('EGO', 'EGO_ITEM_SUPPLIR_SITE');
1214 l_compare_rec.ATTR_GROUP_DISP_NAME := FND_MESSAGE.GET();
1215 FND_MESSAGE.SET_NAME('EGO', 'EGO_SUPPLIER_SITE');
1216 l_compare_rec.ATTR_DISP_NAME := FND_MESSAGE.GET();
1217 -- Get the supplier Information
1218 l_temp_query := 'SELECT VENDOR_SITE_CODE FROM PO_VENDOR_SITES_ALL WHERE VENDOR_SITE_ID = :1 ';
1219 EXECUTE IMMEDIATE l_temp_query into l_temp using p_supplier_site_id;
1220 l_compare_rec.SOURCE_SYS_VAL := l_temp;
1221 l_compare_rec.ITEM1 := l_temp;
1222 l_compare_rec.ITEM2 := l_temp;
1223 l_compare_rec.ITEM3 := l_temp;
1224 l_compare_rec.ITEM4 := l_temp;
1225 l_compare_rec.PRIV_ITEM1 := 'T';
1226 l_compare_rec.PRIV_ITEM2 := 'T';
1227 l_compare_rec.PRIV_ITEM3 := 'T';
1228 l_compare_rec.PRIV_ITEM4 := 'T';
1229 l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
1230 END IF;
1231
1232 FOR i IN 1..l_count LOOP
1233 l_fmt := l_attr_data_tbl(i).DATA_TYPE_CODE ;
1234 IF l_fmt = G_NUMBER_FORMAT THEN
1235 DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i+l_default_sel, l_num_value);
1236 l_disp_val := TO_CHAR(l_num_value);
1237 ELSIF l_fmt = G_CHAR_FORMAT THEN
1238 DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i +l_default_sel, l_str_value);
1239 l_disp_val := l_str_value;
1240 ELSIF l_fmt = G_TIME_FORMAT OR l_fmt = G_DATE_TIME_FORMAT THEN
1241 DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i+l_default_sel, l_date_value);
1242 l_disp_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
1243 END IF; -- IF l_fmt = G_NUMBER_FORMAT THEN
1244
1245 IF l_disp_val IS NOT NULL THEN
1246 --Debug_Message('Source System Data for Attribute - Value are ' || l_attr_data_tbl(i).ATTR_DISPLAY_NAME ||' - '|| l_disp_val);
1247 -- Set the flag=Y in the meta data table indicating the source system data is present
1248 -- for this attribute.
1249 l_attr_data_tbl(i).FLAG := 'Y';
1250 l_compare_tbl.extend();
1251 -- Since there exists data for this attribute for Source System
1252 -- Save the meta data so that we can use this when retrieving data from production table.
1253 l_attr_meta_tbl.extend();
1254 l_attr_meta_tbl(l_attr_meta_tbl.LAST) := l_attr_data_tbl(i);
1255 l_compare_rec.ATTR_GROUP_DISP_NAME := l_attr_data_tbl(i).ATTR_GROUP_DISP_NAME ;
1256 l_compare_rec.ATTR_DISP_NAME := l_attr_data_tbl(i).ATTR_DISPLAY_NAME ;
1257
1258 -- for each attribute group there can be a privilege attached
1259 -- so, finding if user has privilege for this attribute group
1260 -- Setting properly the privilege (either T or F) in compare table
1261 l_attGrp_new := l_attr_data_tbl(i).ATTR_GROUP_DISP_NAME;
1262 l_priv_name := l_attr_data_tbl(i).VIEW_PRIVILEGE_NAME;
1263 IF(l_priv_name IS NULL OR l_priv_name = 'NOTAPPLICABLE') THEN
1264 l_priv_item1 := 'T';
1265 l_priv_item2 := 'T';
1266 l_priv_item3 := 'T';
1267 l_priv_item4 := 'T';
1268 ELSIF(l_attGrp_old <> l_attGrp_new) THEN
1269 l_priv_item1 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item1,
1270 p_org_Id, NULL, NULL, NULL, l_party_id);
1271 l_priv_item2 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item2,
1272 p_org_Id, NULL, NULL, NULL, l_party_id);
1273 l_priv_item3 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item3,
1274 p_org_Id, NULL, NULL, NULL, l_party_id);
1275 l_priv_item4 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item4,
1276 p_org_Id, NULL, NULL, NULL, l_party_id);
1277 END IF; --IF(l_priv_name IS NULL OR l_priv_name = 'NOTAPPLICABLE') THEN
1278
1279 l_attGrp_old := l_attGrp_new;
1280 l_compare_rec.PRIV_ITEM1 := l_priv_item1;
1281 l_compare_rec.PRIV_ITEM2 := l_priv_item2;
1282 l_compare_rec.PRIV_ITEM3 := l_priv_item3;
1283 l_compare_rec.PRIV_ITEM4 := l_priv_item4;
1284
1285 Debug_Message('Value Set for this attribute is ' || TO_CHAR(l_attr_data_tbl(i).VALUE_SET_ID));
1286 -- if a value set is attached to an attribute, THEN getting its display value and storing
1287 -- that value in compare_table. Because we need to display the display values
1288 -- value_set_id = 0 means value set is not associated
1289 -- value_set_id = -1 means value set is not associated, but there is some other SQL to get the
1290 -- display value. We have already stored such SQLs in l_p_atr_sql
1291 -- value set is not associated for columns 'DESCRIPTION','ITEM_NUMBER', 'LONG_DESCRIPTION'
1292 IF l_attr_data_tbl(i).VALUE_SET_ID = -1
1293 AND l_attr_data_tbl(i).DATABASE_COLUMN NOT IN ('DESCRIPTION', 'ITEM_NUMBER', 'LONG_DESCRIPTION', 'APPROVAL_STATUS', 'CREATION_DATE' ,
1294 'STYLE_ITEM_NUMBER' )
1295 THEN
1296 -- Value set not associated but there is some other SQL to get the
1297 -- display value. We have already stored such SQLs in l_p_atr_sql
1298 l_temp_query := l_p_atr_sql(l_attr_data_tbl(i).DATABASE_COLUMN) || ' :1';
1299 EXECUTE IMMEDIATE l_temp_query into l_temp using l_disp_val;
1300 l_compare_rec.source_sys_val := l_temp;
1301 Debug_Message('Value in the View table for this Attribute - '|| l_temp);
1302 ELSIF l_attr_data_tbl(i).VALUE_SET_ID = -1
1303 OR l_attr_data_tbl(i).VALIDATION_CODE = EGO_EXT_FWK_PUB.G_NONE_VALIDATION_CODE --G_NUMBER_FORMAT
1304 OR l_attr_data_tbl(i).DATABASE_COLUMN IN ( 'DESCRIPTION','ITEM_NUMBER', 'LONG_DESCRIPTION', 'APPROVAL_STATUS', 'CREATION_DATE',
1305 'STYLE_ITEM_FLAG' , 'STYLE_ITEM_NUMBER', 'GDSN_OUTBOUND_ENABLED_FLAG',
1306 'TRADE_ITEM_DESCRIPTOR' )
1307 THEN
1308 -- Value set not associated Or NO Validation required.
1309 l_compare_rec.source_sys_val := l_disp_val;
1310 Debug_Message('Value in the View table for this Attribute - '||l_disp_val);
1311 ELSIF l_attr_data_tbl(i).VALUE_SET_ID <> 0 THEN
1312 --Value set is associated.
1313 l_temp := Get_SS_Data_For_Val_set
1314 ( p_value_set_id => l_attr_data_tbl(i).VALUE_SET_ID
1315 ,p_validation_code => l_attr_data_tbl(i).VALIDATION_CODE
1316 ,p_str_val => l_disp_val);
1317 IF l_temp IS NOT NULL THEN
1318 l_compare_rec.source_sys_val := l_temp;
1319 ELSE
1320 l_compare_rec.source_sys_val := l_disp_val;
1321 END IF; --IF l_temp IS NOT NULL THEN
1322 Debug_Message('Value in the View table for this Attribute - '|| l_compare_rec.SOURCE_SYS_VAL);
1323 ELSE
1324 l_compare_rec.source_sys_val := l_disp_val;
1325 Debug_Message('Value in the View table for this Attribute - '|| l_disp_val);
1326 END IF; --IF l_attr_data_tbl(i).VALUE_SET_ID = -1
1327
1328 -- If UOM class is associated with this attribute THEN appending base Unit Of Measure to the value
1329 IF(l_attr_data_tbl(i).UOM_CLASS IS NOT NULL) THEN
1330 l_temp_query := l_p_atr_sql('UOM_CLASS');
1331 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING l_attr_data_tbl(i).UOM_CLASS;
1332 l_compare_rec.source_sys_val := l_disp_val || ' ' || l_temp;
1333 END IF; --IF(l_attr_data_tbl(i).UOM_CLASS is not NULL) THEN
1334
1335 IF l_is_policy = 'Y' AND --Change policy needs to be populated
1336 (l_attr_data_tbl(i).ATTR_GROUP_NAME <> l_primary_ag_int_name)
1337 THEN
1338 l_compare_rec.CHANGE_POLICY := l_ch_policy_tbl(l_attr_data_tbl(i).ATTR_GROUP_NAME);
1339 END IF; --IF l_is_policy = 'Y' AND
1340
1341 l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
1342 END IF; --IF l_disp_val IS NOT NULL THEN
1343 END LOOP; --FOR i IN 1..l_count LOOP
1344 Debug_Message('Completed Entering records for Source System');
1345 END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_msi_intf) > 0 LOOP
1346 DBMS_SQL.close_cursor(cr_msi_intf);
1347
1348 Debug_Message('Done processing Item attributes (Primary and Operational) for Source System');
1349 Debug_Message('Processing Item attributes (Primary and Operational) for Production Items');
1350 -- Building query to get values from production table MTL_SYSTEM_ITEMS
1351 l_default_sel := 0; -- To keep track of total number of attributes in Compare Table
1352
1353 -- If atleast one attribute for Source System item is populated
1354 -- in l_compare_tbl, Proceed to fill table for other items
1355 Debug_Message('Number of attributes populated for Source System item in Compare Table - ' || TO_CHAR(l_compare_tbl.LAST));
1356 IF (l_compare_tbl.LAST > 0) THEN
1357 Debug_Message('Preparing SQL for Primary and Operational attributes of Production Items ');
1358 l_sql_msi := 'SELECT I.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID ' ;
1359 l_default_sel := l_default_sel + 1;
1360 l_col_idx := 1;
1361 FOR i in 1..l_count LOOP
1362 Debug_Message('Primary Attr : ' || l_attr_data_tbl(i).DATABASE_COLUMN );
1363 -- If source system contains some value for this attribute the FLAG would be Y
1364 IF l_attr_data_tbl(i).FLAG = 'Y' THEN
1365 G_META(l_col_idx + l_default_sel) := l_attr_data_tbl(i).DATA_TYPE_CODE;
1366 IF l_attr_data_tbl(i).VALUE_SET_ID = -1 THEN
1367 --Primary attribute
1368 l_sql_msi := l_sql_msi || ' , ( '||l_p_atr_sql(l_attr_data_tbl(i).DATABASE_COLUMN);
1369 IF l_attr_data_tbl(i).DATABASE_COLUMN NOT IN ('DESCRIPTION','ITEM_NUMBER', 'LONG_DESCRIPTION' ,
1370 'STYLE_ITEM_NUMBER' ) THEN
1371 l_sql_msi := l_sql_msi ||'I.'||l_attr_data_tbl(i).DATABASE_COLUMN ;
1372 END IF; -- IF l_attr_data_tbl(i).DATABASE_COLUMN NOT IN ('DESCRIPTION
1373 l_sql_msi := l_sql_msi ||')AS '|| l_attr_data_tbl(i).DATABASE_COLUMN;
1374 ELSE
1375 l_sql_msi := l_sql_msi || ' , '|| ' I.'||l_attr_data_tbl(i).DATABASE_COLUMN;
1376 END IF; --IF l_attr_data_tbl(i).VALUE_SET_ID = -1 THEN
1377
1378 -- Saving Unit Of Measure UOM Class associated with this attribute in UOM table
1379 IF (l_attr_data_tbl(i).UOM_CLASS IS NOT NULL) THEN
1380 UOM(l_col_idx+ l_default_sel) := l_attr_data_tbl(i).UOM_CLASS;
1381 ELSE
1382 UOM(l_col_idx+ l_default_sel) := NULL;
1383 END IF; --IF (l_attr_data_tbl(i).UOM_CLASS IS NOT NULL) THEN
1384 l_col_idx := l_col_idx + 1;
1385 END IF; --IF l_attr_data_tbl(i).FLAG = 'Y' THEN
1386 END LOOP; --FOR i in 1..l_count LOOP
1387
1388 -- if the batch is PDH batch
1389 -- we need to bind only one inventory_item_id
1390 IF is_pdh_batch THEN
1391 -- PDH Case
1392 l_sql_msi := l_sql_msi || ' FROM MTL_SYSTEM_ITEMS_B_KFV I '
1393 || ' WHERE INVENTORY_ITEM_ID = :1 '
1394 || ' AND ORGANIZATION_ID = :2';
1395 ELSE
1396 -- Non PDH Case
1397 l_sql_msi := l_sql_msi || ' FROM MTL_SYSTEM_ITEMS_B_KFV I '
1398 || ' WHERE INVENTORY_ITEM_ID IN( :1,:2,:3,:4 ) '
1399 || ' AND ORGANIZATION_ID = :5';
1400 END IF; --IF is_pdh_batch THEN
1401
1402 Debug_Message('Done preparing SQL for Primary and Operational attributes of Production Items ');
1403
1404 Debug_Message('SQL is - ');
1405 FOR l in 1..(CEIL(LENGTH(l_sql_msi)/1000)) LOOP
1406 Debug_Message(SUBSTR(l_sql_msi, ((l-1)*1000) + 1, 1000));
1407 END LOOP; --FOR l in 1..(CEIL(LENGTH(l_sql_msi)/1000)) LOOP
1408
1409 -- Opening a Dynamic Cursor for handling Query l_sql_msi
1410 cr_msi_attr := dbms_sql.open_cursor;
1411 Debug_Message('Parsing the SQL');
1412 DBMS_SQL.PARSE(cr_msi_attr, l_sql_msi, DBMS_SQL.native);
1413 Debug_Message('Done parsing the SQL');
1414
1415 Debug_Message('Binding variables');
1416 -- Binding the variables
1417 IF is_pdh_batch THEN
1418 DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1419 DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_org_id);
1420 ELSE
1421 DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1422 DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_item2);
1423 DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':3',p_item3);
1424 DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':4',p_item4);
1425 DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':5',p_org_id);
1426 END IF; --IF is_pdh_batch THEN
1427 Debug_Message('Done binding variables');
1428
1429 -- First columnn is inventory item id.
1430 Debug_Message('Defining columns for SQL');
1431 DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, 1, l_num_value);
1432 -- Defining Columns for Dynamic Cursor
1433 Debug_Message('Total columns = '||TO_CHAR(l_col_idx + 1));
1434 FOR i in 2..l_col_idx LOOP
1435 IF G_META(i) = G_CHAR_FORMAT THEN
1436 DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_str_value, 4000);
1437 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1438 DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_num_value);
1439 ELSIF G_META(i) in(G_DATE_FORMAT, G_TIME_FORMAT, G_DATE_TIME_FORMAT) THEN
1440 DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_date_value);
1441 END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1442 END LOOP; --FOR i in 2..l_col_idx LOOP
1443
1444 l_ignore := DBMS_SQL.EXECUTE(cr_msi_attr);
1445 Debug_Message('Done Execution of the Query');
1446
1447 Debug_Message('Fetching rows');
1448 WHILE DBMS_SQL.FETCH_ROWS(cr_msi_attr) > 0 LOOP
1449 -- first column is inventory_item_id
1450 Debug_Message('Getting value for inventory_item_id');
1451 DBMS_SQL.COLUMN_VALUE(cr_msi_attr, 1, l_item_id);
1452 Debug_Message('Selected Row and started entering into Compare View table for item : ' || TO_CHAR(l_item_id));
1453
1454 FOR i IN 2..l_col_idx LOOP
1455 -- for each column get the value into appropriate variable depending upon the format of column
1456 IF G_META(i) = G_CHAR_FORMAT THEN
1457 DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_str_value);
1458 l_val := l_str_value;
1459 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1460 DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_num_value);
1461 l_val := TO_CHAR(l_num_value);
1462 ELSIF G_META(i) = G_DATE_FORMAT THEN
1463 DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_date_value);
1464 l_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
1465 END IF; -- IF G_META(i) = G_CHAR_FORMAT THEN
1466
1467 -- To populate into the Compare View Table
1468 IF l_attr_meta_tbl(i-1).VALUE_SET_ID <> -1
1469 AND l_attr_meta_tbl(i-1).VALUE_SET_ID <> 0
1470 AND l_attr_meta_tbl(i-1).VALIDATION_CODE <> EGO_EXT_FWK_PUB.G_NONE_VALIDATION_CODE
1471 THEN
1472 Debug_Message('Value Set is associated, so getting display value for internal value: ' || l_val);
1473 IF(G_META(i) = G_DATE_FORMAT) THEN
1474 l_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
1475 431, l_val, NULL, NULL, l_attr_meta_tbl(i-1).ATTR_NAME, 'EGO_MASTER_ITEMS'
1476 ,l_attr_meta_tbl(i-1).ATTR_GROUP_NAME, l_attr_meta_tbl(i-1).ATTR_ID
1477 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
1478 , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1479 ELSIF (G_META(i) = G_CHAR_FORMAT) THEN
1480 l_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
1481 431, NULL, l_val, NULL, l_attr_meta_tbl(i-1).ATTR_NAME, 'EGO_MASTER_ITEMS'
1482 ,l_attr_meta_tbl(i-1).ATTR_GROUP_NAME, l_attr_meta_tbl(i-1).ATTR_ID
1483 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
1484 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1485 ELSIF (G_META(i) = G_NUMBER_FORMAT) THEN
1486 l_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
1487 431, NULL, NULL, l_val, l_attr_meta_tbl(i-1).ATTR_NAME, 'EGO_MASTER_ITEMS'
1488 ,l_attr_meta_tbl(i-1).ATTR_GROUP_NAME, l_attr_meta_tbl(i-1).ATTR_ID
1489 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
1490 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1491 END IF; --IF(G_META(i) = G_DATE_FORMAT) THEN
1492 Debug_Message('Display value is: ' || l_val);
1493 END IF; --IF l_attr_meta_tbl(i-1).VALUE_SET_ID <> -1
1494
1495 -- if UOM class is attached to attribute THEN append the base UOM to the value
1496 IF ( UOM(i) IS NOT NULL ) THEN
1497 -- UOM is associated to this Attribute.
1498 l_temp_query := l_p_atr_sql('UOM_CLASS');
1499 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING UOM(i);
1500 l_val := l_val || ' ' || l_temp;
1501 END IF; -- IF ( UOM(i) IS NOT NULL ) THEN
1502
1503 populate_compare_tbl(p_compare_table => l_compare_tbl ,
1504 p_index => i-1+l_supplier_rows_count,
1505 p_sel_item => l_item_id ,
1506 p_value => l_val ,
1507 p_item1 => p_item1 ,
1508 p_item2 => p_item2 ,
1509 p_item3 => p_item3 ,
1510 p_item4 => p_item4);
1511 END LOOP; --FOR i IN 2..l_col_idx LOOP
1512 END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_msi_attr) > 0 LOOP
1513 DBMS_SQL.close_cursor(cr_msi_attr);
1514 Debug_Message('Completed inserting information for primary and operational attributes for all items');
1515 END IF; --IF (l_compare_tbl.LAST > 0) THEN
1516 Debug_Message('Done processing Item attributes (Primary and Operational) for Production Items');
1517
1518 Debug_Message('Processing User Defined attributes.');
1519 Debug_Message('Finding out the max revision And Pdh Revi : ' || p_pdh_revision );
1520 -- To find out if there are any attributes have Revisions associated with them
1521 IF is_pdh_batch THEN
1522 -- If the item is of PDH Type
1523 /* l_revision := EGO_IMPORT_PVT.GET_LATEST_EIUAI_REV_PDH(
1524 p_batch_id => p_batch_id
1525 ,p_inventory_item_id => p_item1
1526 ,p_item_number => p_ss_record
1527 ,p_organization_id => p_org_id);*/
1528 -- l_revision is revision of the source system item.
1529 -- p_pdh_revision is the revision of the PDH item.
1530 l_revision := p_pdh_revision;
1531
1532 -- If l_revision is null or l_revision revision do not present in PDH
1533 -- l_pdh_revision = production revision. else l_pdh_revision = l_revision.
1534 IF (p_item1 IS NOT NULL AND
1535 ( p_pdh_revision IS NULL
1536 OR
1537 'N' = REV_EXISTS_IN_PDH ( p_revision => p_pdh_revision
1538 ,p_inventory_item_id => p_item1
1539 ,p_organization_id => p_org_id
1540 )
1541 )
1542 )
1543 THEN
1544 l_pdh_revision := GET_CURRENT_PDH_REVISION( p_item1, p_org_id);
1545 ELSE
1546 l_pdh_revision := p_pdh_revision;
1547 END IF;
1548 ELSE
1549 -- For NON PDH Type
1550 l_revision := EGO_IMPORT_PVT.GET_LATEST_EIUAI_REV_SS(
1551 p_batch_id => p_batch_id
1552 ,p_source_system_id => p_ss_code
1553 ,p_source_system_reference => p_ss_record
1554 ,p_organization_id => p_org_id);
1555 END IF; --IF is_pdh_batch THEN
1556
1557 Debug_Message('Max revision is '||l_revision);
1558
1559 -- Process accessing User Defined attributes one attribute grp at a time
1560 l_val_set_clause := NULL;
1561
1562 IF is_pdh_batch THEN
1563 -- Handling User Defined Attributes for PDH batch
1564 Debug_Message('Batch is PDH batch ');
1565 FOR rec_attr IN cr_attr_groups_pdh(l_revision) LOOP
1566 -- R12C: Show Item_Supplier attributes only if 'Supplier Id' is passed. (Supplier is selected)
1567 -- R12C: Show Item Supplier site attributes and item supplier site org attributes only if
1568 -- 'Supplier Site Id' is passed.
1569 -- NOTE: SupplierSiteId do not exist without SupplierId.
1570 -- Added the following if condition for item intersection support in R12C.
1571 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID AND p_supplier_id IS NOT NULL
1572 OR rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID AND p_supplier_site_id IS NOT NULL
1573 OR rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID AND p_supplier_site_id IS NOT NULL
1574 OR rec_attr.DATA_LEVEL_ID IN ( G_ITEM_LEVEL_ID, G_ORG_LEVEL_ID )
1575 THEN
1576 Debug_Message('Processing attribute group - '||rec_attr.ATTR_GROUP_INT_NAME);
1577 -- if change policy may be present THEN get the Change policy for each attr grp
1578 IF (l_is_policy = 'Y') THEN
1579 Debug_Message('Getting change policy for attribute group - '||rec_attr.ATTR_GROUP_INT_NAME);
1580 ENG_CHANGE_POLICY_PKG.GetChangePolicy
1581 ( p_policy_object_name => 'CATALOG_LIFECYCLE_PHASE'
1582 , p_policy_code => 'CHANGE_POLICY'
1583 , p_policy_pk1_value => l_catalog_id
1584 , p_policy_pk2_value => l_lifecycle_id
1585 , p_policy_pk3_value => l_phase_id
1586 , p_policy_pk4_value => NULL
1587 , p_policy_pk5_value => NULL
1588 , p_attribute_object_name => 'EGO_CATALOG_GROUP'
1589 , p_attribute_code => 'ATTRIBUTE_GROUP'
1590 , p_attribute_value => rec_attr.ATTR_GROUP_ID
1591 , x_policy_value => l_ch_policy
1592 );
1593 Debug_Message('Change Policy for attribute group : '||rec_attr.ATTR_GROUP_INT_NAME || ' is : ' || l_ch_policy );
1594 IF INSTR(l_ch_policy,'NOT') > 0 THEN
1595 l_ch_policy_tbl(rec_attr.ATTR_GROUP_INT_NAME) := 'N';
1596 ELSIF INSTR(l_ch_policy,'ALLOWED') > 0 THEN
1597 l_ch_policy_tbl(rec_attr.ATTR_GROUP_INT_NAME) := 'Y';
1598 ELSIF INSTR(l_ch_policy,'CHANGE') > 0 THEN
1599 l_ch_policy_tbl(rec_attr.ATTR_GROUP_INT_NAME) := 'C';
1600 END IF; -- IF INSTR(l_ch_policy,'NOT') > 0 THEN
1601 END IF; --IF (l_is_policy = 'Y') THEN
1602
1603 l_sel_clause := NULL;
1604 l_sql_query := NULL;
1605 l_temp_query := NULL;
1606 l_idx := 1;
1607 l_start := NVL(l_compare_tbl.LAST, 0) ;
1608 cr_ud_attr := dbms_sql.open_cursor;
1609
1610 --R12C: Finding attr Group display name with prefix to identify
1611 -- the Attribute group data level.
1612 IF rec_attr.DATA_LEVEL_ID = G_ITEM_LEVEL_ID THEN
1613 l_attr_group_display_name := l_item_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
1614 ELSIF rec_attr.DATA_LEVEL_INTERNAL_NAME = G_ITEM_REVISION_LEVEL THEN
1615 l_attr_group_display_name := l_itemRev_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
1616 ELSIF rec_attr.DATA_LEVEL_ID = G_ORG_LEVEL_ID THEN
1617 l_attr_group_display_name := l_itemOrg_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
1618 ELSIF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
1619 l_attr_group_display_name := l_itemSup_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
1620 ELSIF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
1621 l_attr_group_display_name := l_itemSupSite_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
1622 ELSIF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
1623 l_attr_group_display_name := l_itemSupSiteOrg_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
1624 END IF;
1625
1626 -- Since Each AG is handled at each time. Find once privileges over all items
1627 -- of a particular Attribute Group
1628 Debug_Message('Finding and Populating privilege');
1629 l_priv_name := get_privilege_name(rec_attr.VIEW_PRIVILEGE_ID);
1630 IF(l_priv_name IS NULL) THEN
1631 l_priv_item1 := 'T';
1632 l_priv_item2 := 'T';
1633 l_priv_item3 := 'T';
1634 l_priv_item4 := 'T';
1635 ELSE
1636 l_priv_item1 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item1,
1637 p_org_Id, NULL, NULL, NULL, l_party_id);
1638 l_priv_item2 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item2,
1639 p_org_Id, NULL, NULL, NULL, l_party_id);
1640 l_priv_item3 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item3,
1641 p_org_Id, NULL, NULL, NULL, l_party_id);
1642 l_priv_item4 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item4,
1643 p_org_Id, NULL, NULL, NULL, l_party_id);
1644 END IF; --IF(l_priv_name IS NULL) THEN
1645 Debug_Message('Done finding and Populating privilege');
1646
1647 -- If is no revision level records for this attribute group
1648 IF rec_attr.REVISION IS NULL THEN
1649 -- For Each attribute Group selected. Get all the attributes in it and
1650 -- Populate the l_compare_table for these attributes
1651 -- The cusor cr_usr_intf_pdh returns
1652 -- Also Perparing Query clause <l_sel_clause> to query for same attributes over other Items.
1653 l_sql_query := ' FROM EGO_MTL_SY_ITEMS_EXT_VL I, EGO_ATTR_GROUPS_DL_V AG '||
1654 ' WHERE AG.APPLICATION_ID = 431 '||
1655 ' AND NVL(AG.ATTR_GROUP_TYPE, ''EGO_ITEMMGMT_GROUP'') = ''EGO_ITEMMGMT_GROUP'' '||
1656 ' AND AG.ATTR_GROUP_ID = I.ATTR_GROUP_ID'||
1657 ' AND I.DATA_LEVEL_ID = :98' || -- Added for R12C: Data_level_id
1658 ' AND AG.ATTR_GROUP_NAME = :99'; --Bug#5043002 '' || rec_attr.ATTR_GROUP_INT_NAME ||'''';
1659
1660 -- R12C: BEGIN
1661 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
1662 l_sql_query := l_sql_query || ' AND I.PK1_VALUE = :96';
1663 END IF;
1664
1665 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
1666 l_sql_query := l_sql_query || ' AND I.PK2_VALUE = :97';
1667 END IF;
1668
1669 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
1670 l_sql_query := l_sql_query || ' AND I.PK1_VALUE = :96 AND I.PK2_VALUE = :97';
1671 END IF;
1672 -- R12C: END
1673
1674 Debug_Message('Revision is NULL');
1675 Debug_Message('Populating all the attribute values for this attribute group for source system');
1676 FOR rec_ud_attrs IN cr_usr_intf_pdh( rec_attr.ATTR_GROUP_INT_NAME, rec_attr.DATA_LEVEL_ID ) LOOP
1677 Debug_Message('Processing the attribute: '||rec_ud_attrs.ATTR_INT_NAME);
1678 l_idx := l_idx + 1;
1679 l_compare_tbl.extend();
1680 l_compare_rec.ATTR_GROUP_DISP_NAME := l_attr_group_display_name;
1681 l_compare_rec.ATTR_DISP_NAME := rec_ud_attrs.ATTR_DISPLAY_NAME;
1682 l_compare_rec.ATTRIBUTE_CODE := rec_ud_attrs.ATTR_ID;
1683 l_compare_rec.ATTR_INT_NAME := rec_ud_attrs.ATTR_INT_NAME;
1684 l_compare_rec.ATTR_GROUP_INT_NAME := rec_ud_attrs.ATTR_GROUP_INT_NAME;
1685
1686 -- Setting properly the privilege in compare table. privileges are calculated
1687 -- for each AG earlier
1688 l_compare_rec.PRIV_ITEM1 := l_priv_item1;
1689 l_compare_rec.PRIV_ITEM2 := l_priv_item2;
1690 l_compare_rec.PRIV_ITEM3 := l_priv_item3;
1691 l_compare_rec.PRIV_ITEM4 := l_priv_item4;
1692
1693 -- Saving UOM Class and value set Associated with this attribute
1694 UOM(l_idx) := rec_ud_attrs.UOM_CLASS;
1695 UOM_USER_CODE(l_idx) := rec_ud_attrs.ATTR_VALUE_UOM;
1696 UOM_DISP_VAL(l_idx) := rec_ud_attrs.ATTR_UOM_DISP_VALUE;
1697
1698 IF rec_ud_attrs.VALIDATION_CODE = EGO_EXT_FWK_PUB.G_NONE_VALIDATION_CODE THEN
1699 VSId(l_idx) := 0;
1700 ELSE
1701 VSId(l_idx) := rec_ud_attrs.VALUE_SET_ID;
1702 END IF;
1703
1704 -- getting and setting source system value
1705 -- If Value set is not associated
1706 IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
1707 Debug_Message('Value set is NOT attached');
1708 IF rec_ud_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
1709 G_META(l_idx) := G_CHAR_FORMAT;
1710 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_DISP_VALUE;
1711 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'C%' OR rec_ud_attrs.DATABASE_COLUMN LIKE 'T%' THEN
1712 G_META(l_idx) := G_CHAR_FORMAT;
1713 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_STR;
1714 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'D%' THEN
1715 G_META(l_idx) := G_DATE_FORMAT;
1716 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_DATE;
1717 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'N%' THEN
1718 G_META(l_idx) := G_NUMBER_FORMAT;
1719 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_NUM;
1720 END IF; --IF rec_ud_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
1721
1722 -- Adding column to sql string to get production values
1723 l_sel_clause := l_sel_clause || ' , ' || rec_ud_attrs.DATABASE_COLUMN;
1724 ELSE
1725 Debug_Message('Value set is attached');
1726 -- If Value set is Associated
1727 l_sel_clause := l_sel_clause || ' , '|| rec_ud_attrs.DATABASE_COLUMN;
1728 G_META(l_idx) := SUBSTR(rec_ud_attrs.DATABASE_COLUMN, 1, 1);
1729 IF rec_ud_attrs.ATTR_DISP_VALUE IS NULL THEN
1730 l_compare_rec.SOURCE_SYS_VAL := Get_SS_Data_For_Val_set
1731 ( p_value_set_id => rec_ud_attrs.VALUE_SET_ID
1732 ,p_validation_code => rec_ud_attrs.VALIDATION_CODE
1733 ,p_str_val => rec_ud_attrs.ATTR_VALUE_STR
1734 ,p_date_val => rec_ud_attrs.ATTR_VALUE_DATE
1735 ,p_num_val => rec_ud_attrs.ATTR_VALUE_NUM );
1736 ELSE
1737 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_DISP_VALUE;
1738 END IF; --IF rec_ud_attrs.ATTR_DISP_VALUE IS NULL THEN
1739 Debug_Message('Display value is: '||l_compare_rec.SOURCE_SYS_VAL);
1740 END IF; --IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
1741
1742 -- if UOM class is attached to the attribute, THEN appending the base UOM to attribute value.
1743 IF rec_ud_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
1744 Debug_Message('UOM Display Value is attached');
1745 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || rec_ud_attrs.ATTR_UOM_DISP_VALUE;
1746 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
1747 ELSIF rec_ud_attrs.ATTR_VALUE_UOM IS NOT NULL THEN
1748 Debug_Message('UOM code is attached is attached');
1749 l_temp_query := l_p_atr_sql('UOM_CODE');
1750 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_ud_attrs.ATTR_VALUE_UOM;
1751 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
1752 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
1753 ELSIF rec_ud_attrs.UOM_CLASS IS NOT NULL THEN
1754 Debug_Message('UOM Class is attached');
1755 l_temp_query := l_p_atr_sql('UOM_CLASS');
1756 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_ud_attrs.UOM_CLASS;
1757 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
1758 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
1759 END IF; --IF rec_ud_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
1760
1761 IF l_is_policy = 'Y' THEN
1762 l_compare_rec.CHANGE_POLICY := l_ch_policy_tbl(rec_attr.ATTR_GROUP_INT_NAME);
1763 END IF; --IF l_is_policy = 'Y' THEN
1764 l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
1765 Debug_Message('Done processing the attribute: '||rec_ud_attrs.ATTR_INT_NAME);
1766 END LOOP; --FOR rec_ud_attrs IN cr_usr_intf_pdh(rec_attr.ATTR_GROUP_INT_NAME) LOOP
1767
1768 l_sql_query := 'SELECT I.INVENTORY_ITEM_ID ' || l_sel_clause || l_sql_query ||
1769 ' AND I.INVENTORY_ITEM_ID = :1'||
1770 ' AND I.ORGANIZATION_ID = :2';
1771 ELSE --IF rec_attr.REVISION IS NULL THEN
1772 -- If there are attributes with Revisions ... Get Attributes in for given Attribute Group.
1773 l_sql_query := ' FROM EGO_MTL_SY_ITEMS_EXT_VL I, EGO_ATTR_GROUPS_DL_V AG, MTL_ITEM_REVISIONS_B REV'||
1774 ' WHERE AG.APPLICATION_ID = 431 '||
1775 ' AND NVL(AG.ATTR_GROUP_TYPE, ''EGO_ITEMMGMT_GROUP'') = ''EGO_ITEMMGMT_GROUP'' '||
1776 ' AND AG.ATTR_GROUP_ID = I.ATTR_GROUP_ID'||
1777 ' AND I.INVENTORY_ITEM_ID = REV.INVENTORY_ITEM_ID ' ||
1778 ' AND I.ORGANIZATION_ID = REV.ORGANIZATION_ID ' ||
1779 ' AND I.REVISION_ID = REV.REVISION_ID ' ||
1780 ' AND REV.REVISION = ''' || l_pdh_revision || '''' ||
1781 ' AND I.DATA_LEVEL_ID = :98' ||
1782 ' AND AG.ATTR_GROUP_NAME = :99' ; --Bug#5043002 '' || rec_attr.ATTR_GROUP_INT_NAME ||'''';
1783
1784 -- R12C: BEGIN
1785 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
1786 l_sql_query := l_sql_query || ' AND I.PK1_VALUE = :96';
1787 END IF;
1788
1789 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
1790 l_sql_query := l_sql_query || ' AND I.PK2_VALUE = :97';
1791 END IF;
1792
1793 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
1794 l_sql_query := l_sql_query || ' AND I.PK1_VALUE = :96 AND I.PK2_VALUE = :97';
1795 END IF;
1796 -- R12C: END
1797
1798 Debug_Message('Revision is NOT NULL, revision is: '||rec_attr.REVISION);
1799 Debug_Message('Populating all the attribute values for this attribute group for source system');
1800 FOR rec_ud_attrs IN cr_rev_usr_intf_pdh(rec_attr.ATTR_GROUP_INT_NAME, l_revision) LOOP
1801 Debug_Message('Processing the attribute: '||rec_ud_attrs.ATTR_INT_NAME);
1802 l_idx := l_idx + 1;
1803 l_compare_tbl.extend();
1804 l_compare_rec.ATTR_GROUP_DISP_NAME := l_attr_group_display_name;
1805 l_compare_rec.ATTR_DISP_NAME := rec_ud_attrs.ATTR_DISPLAY_NAME;
1806 l_compare_rec.ATTRIBUTE_CODE := rec_ud_attrs.ATTR_ID;
1807 l_compare_rec.ATTR_INT_NAME := rec_ud_attrs.ATTR_INT_NAME ;
1808 l_compare_rec.ATTR_GROUP_INT_NAME := rec_ud_attrs.ATTR_GROUP_INT_NAME;
1809
1810 -- Setting properly the privilege in compare table. privileges are calculated
1811 -- for each AG earlier
1812 l_compare_rec.PRIV_ITEM1 := l_priv_item1;
1813 l_compare_rec.PRIV_ITEM2 := l_priv_item2;
1814 l_compare_rec.PRIV_ITEM3 := l_priv_item3;
1815 l_compare_rec.PRIV_ITEM4 := l_priv_item4;
1816
1817 -- Saving UOM Class and value set Associated with this attribute
1818 IF rec_ud_attrs.VALIDATION_CODE = EGO_EXT_FWK_PUB.G_NONE_VALIDATION_CODE THEN
1819 VSId(l_idx) := 0;
1820 ELSE
1821 VSId(l_idx) := rec_ud_attrs.VALUE_SET_ID;
1822 END IF;
1823 UOM(l_idx) := rec_ud_attrs.UOM_CLASS;
1824 UOM_USER_CODE(l_idx) := rec_ud_attrs.ATTR_VALUE_UOM;
1825 UOM_DISP_VAL(l_idx) := rec_ud_attrs.ATTR_UOM_DISP_VALUE;
1826
1827 -- getting and setting source system value
1828 -- If Value set is not associated
1829 IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
1830 Debug_Message('Value set is NOT attached');
1831 IF rec_ud_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
1832 G_META(l_idx) := G_CHAR_FORMAT;
1833 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_DISP_VALUE;
1834 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'C%' OR rec_ud_attrs.DATABASE_COLUMN LIKE 'T%' THEN
1835 G_META(l_idx) := G_CHAR_FORMAT;
1836 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_STR;
1837 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'D%' THEN
1838 G_META(l_idx) := G_DATE_FORMAT;
1839 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_DATE;
1840 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'N%' THEN
1841 G_META(l_idx) := G_NUMBER_FORMAT;
1842 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_NUM;
1843 END IF; --IF rec_ud_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
1844
1845 l_sel_clause := l_sel_clause || ' , ' || rec_ud_attrs.DATABASE_COLUMN;
1846 ELSE --IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
1847 Debug_Message('Value set is attached');
1848 l_sel_clause := l_sel_clause || ' , '|| rec_ud_attrs.DATABASE_COLUMN;
1849 G_META(l_idx) := SUBSTR(rec_ud_attrs.DATABASE_COLUMN, 1, 1);
1850 --G_CHAR_FORMAT;
1851 IF rec_ud_attrs.ATTR_DISP_VALUE IS NULL THEN
1852 l_compare_rec.SOURCE_SYS_VAL := Get_SS_Data_For_Val_set
1853 ( p_value_set_id => rec_ud_attrs.VALUE_SET_ID
1854 ,p_validation_code => rec_ud_attrs.VALIDATION_CODE
1855 ,p_str_val => rec_ud_attrs.ATTR_VALUE_STR
1856 ,p_date_val => rec_ud_attrs.ATTR_VALUE_DATE
1857 ,p_num_val => rec_ud_attrs.ATTR_VALUE_NUM );
1858 ELSE
1859 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_DISP_VALUE;
1860 END IF; --IF rec_ud_attrs.ATTR_DISP_VALUE IS NULL THEN
1861 Debug_Message('Display value is: '||l_compare_rec.SOURCE_SYS_VAL);
1862 END IF; --IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
1863
1864 -- if UOM class is attached to the attribute, THEN appending the base UOM to attribute value.
1865 IF rec_ud_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
1866 Debug_Message('UOM Display Value is attached');
1867 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || rec_ud_attrs.ATTR_UOM_DISP_VALUE;
1868 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
1869 ELSIF rec_ud_attrs.ATTR_VALUE_UOM IS NOT NULL THEN
1870 Debug_Message('UOM code is attached is attached');
1871 l_temp_query := l_p_atr_sql('UOM_CODE');
1872 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_ud_attrs.ATTR_VALUE_UOM;
1873 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
1874 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
1875 ELSIF rec_ud_attrs.UOM_CLASS IS NOT NULL THEN
1876 Debug_Message('UOM Class is attached');
1877 l_temp_query := l_p_atr_sql('UOM_CLASS');
1878 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_ud_attrs.UOM_CLASS;
1879 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
1880 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
1881 END IF; --IF rec_ud_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
1882
1883 IF l_is_policy = 'Y' THEN
1884 l_compare_rec.CHANGE_POLICY := l_ch_policy_tbl(rec_attr.ATTR_GROUP_INT_NAME);
1885 END IF; --IF l_is_policy = 'Y' THEN
1886
1887 l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
1888 END LOOP; --FOR rec_ud_attrs IN cr_rev_usr_intf_pdh(rec_attr.ATTR_GROUP_INT_NAME, l_revision) LOOP
1889 l_sql_query := 'SELECT I.INVENTORY_ITEM_ID ' || l_sel_clause || l_sql_query;
1890 l_sql_query := l_sql_query ||' AND I.INVENTORY_ITEM_ID = :1 AND I.ORGANIZATION_ID = :2' ;
1891 END IF; --IF rec_attr.REVISION IS NULL THEN
1892
1893 Debug_Message('Done populating attribute values for this attribute group for source system');
1894 Debug_Message('Getting values from production table');
1895
1896 Debug_Message('SQL is - ');
1897 FOR l in 1..(CEIL(LENGTH(l_sql_query)/1000)) LOOP
1898 Debug_Message(SUBSTR(l_sql_query, ((l-1)*1000) + 1, 1000));
1899 END LOOP; --FOR l in 1..(CEIL(LENGTH(l_sql_query)/1000)) LOOP
1900
1901 -- Define Dynamic SQL for querying for other Items.
1902 Debug_Message('Parsing SQL');
1903 DBMS_SQL.PARSE(cr_ud_attr, l_sql_query, DBMS_SQL.native);
1904 Debug_Message('Done Parsing SQL');
1905
1906 Debug_Message('Defining columns for SQL');
1907 DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, 1, l_num_value);
1908 -- Defining columns in Dynamic Cursor
1909 Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));
1910 FOR i IN 2..l_idx LOOP
1911 IF G_META(i) = G_CHAR_FORMAT THEN
1912 DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value,4000);
1913 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1914 DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);
1915 ELSIF G_META(i) = G_DATE_FORMAT THEN
1916 DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);
1917 END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1918 END LOOP; --FOR i IN 2..l_idx LOOP
1919
1920 Debug_Message('Binding variables');
1921 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);
1922 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_org_id);
1923 --Bug#5043002
1924 --R12C: BEGIN
1925 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
1926 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
1927 END IF;
1928
1929 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
1930 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
1931 END IF;
1932
1933 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
1934 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
1935 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
1936 END IF;
1937 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
1938 --R12C: END
1939
1940 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':99', rec_attr.ATTR_GROUP_INT_NAME);
1941
1942 -- Execution of the Query (Cursor) for UD attrs for Items
1943 l_ignore := DBMS_SQL.EXECUTE(cr_ud_attr);
1944 Debug_Message('Executed SQL, fetching rows');
1945 WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
1946 l_cnt := l_start + 1;
1947 l_item_id := NULL;
1948 DBMS_SQL.COLUMN_VALUE(cr_ud_attr, 1, l_item_id);
1949 FOR i IN 2..l_idx LOOP
1950 l_str_value := NULL;
1951 l_num_value := NULL;
1952 l_date_value := NULL;
1953 IF G_META(i) = G_CHAR_FORMAT THEN
1954 DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_str_value);
1955 l_int_val := l_str_value;
1956 ELSIF G_META(i) = G_DATE_FORMAT THEN
1957 DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);
1958 l_int_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
1959 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1960 DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_num_value);
1961 l_int_val := TO_CHAR(l_num_value);
1962 END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1963
1964 -- if a value set is associated, then get the display value
1965 IF VSID(i) IS NOT NULL AND VSID(i) <> 0 THEN
1966 IF G_META(i) = G_DATE_FORMAT THEN
1967 l_int_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
1968 431, l_int_val, NULL, NULL, l_compare_tbl(l_cnt).ATTR_INT_NAME, 'EGO_ITEMMGMT_GROUP'
1969 ,l_compare_tbl(l_cnt).ATTR_GROUP_INT_NAME, l_compare_tbl(l_cnt).ATTRIBUTE_CODE
1970 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
1971 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1972 ELSIF G_META(i) = G_CHAR_FORMAT THEN
1973 l_int_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
1974 431, NULL, l_int_val, NULL, l_compare_tbl(l_cnt).ATTR_INT_NAME, 'EGO_ITEMMGMT_GROUP'
1975 ,l_compare_tbl(l_cnt).ATTR_GROUP_INT_NAME, l_compare_tbl(l_cnt).ATTRIBUTE_CODE
1976 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
1977 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1978 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1979 l_int_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
1980 431, NULL, NULL, l_int_val, l_compare_tbl(l_cnt).ATTR_INT_NAME, 'EGO_ITEMMGMT_GROUP'
1981 ,l_compare_tbl(l_cnt).ATTR_GROUP_INT_NAME, l_compare_tbl(l_cnt).ATTRIBUTE_CODE
1982 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
1983 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1984 END IF;
1985 END IF; --IF VSID(i) IS NOT NULL
1986
1987 --if UOM class is attached to the attribute, THEN appending the base UOM to attribute value.
1988 IF UOM(i) IS NOT NULL THEN
1989 l_temp_query := l_p_atr_sql('UOM_CLASS');
1990 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING UOM(i);
1991 l_int_val := l_int_val || ' ' || l_temp;
1992 END IF; --IF UOM_DISP_VAL(i) IS NOT NULL THEN
1993
1994 populate_compare_tbl(
1995 p_compare_table => l_compare_tbl
1996 ,p_index => l_cnt
1997 ,p_sel_item => l_item_id
1998 ,p_value => l_int_val
1999 ,p_item1 => p_item1
2000 ,p_item2 => p_item2
2001 ,p_item3 => p_item3
2002 ,p_item4 => p_item4);
2003 l_cnt := l_cnt + 1;
2004 END LOOP; --FOR i IN 2..l_idx LOOP
2005 END LOOP; -- WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2006 DBMS_SQL.close_cursor(cr_ud_attr);
2007 Debug_Message('Done Getting values from production table');
2008 END IF; -- IF DATA_LEVEL CHECK AGIANST p_supplier_id, p_supplier_site_id
2009 END LOOP; --FOR rec_attr IN cr_attr_groups_pdh(l_revision) LOOP
2010 Debug_Message('Done getting User Defined Attributes for Items ');
2011 ELSE --IF is_pdh_batch THEN
2012 ------------------------------------------------------------------------------------
2013 -- Following is the code for NON PDH type and total of the code in this else case --
2014 -- is similar to the one in above if claue --
2015 ------------------------------------------------------------------------------------
2016 Debug_Message('Batch is NON-PDH batch ');
2017 FOR rec_attr IN cr_attr_groups(l_revision, p_bundle_id ) LOOP
2018 -- R12C: Show Item_Supplier attributes only if 'Supplier Id' is passed. (Supplier is selected)
2019 -- R12C: Show Item Supplier site attributes and item supplier site org attributes only if
2020 -- 'Supplier Site Id' is passed.
2021 -- NOTE: SupplierSiteId do not exist without SupplierId.
2022 -- Added the following if condition for item intersection support in R12C.
2023 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID AND p_supplier_id IS NOT NULL
2024 OR rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID AND p_supplier_site_id IS NOT NULL
2025 OR rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID AND p_supplier_site_id IS NOT NULL
2026 OR rec_attr.DATA_LEVEL_ID IN ( G_ITEM_LEVEL_ID, G_ORG_LEVEL_ID )
2027 OR rec_attr.DATA_LEVEL_INTERNAL_NAME IN ( G_ITEM_REVISION_LEVEL )
2028 THEN
2029 Debug_Message('Processing attribute group - '||rec_attr.ATTR_GROUP_INT_NAME);
2030 Debug_Message('With Data Level: ' || rec_attr.DATA_LEVEL_ID);
2031 -- if change policy may be present THEN get the Change policy for each attr grp
2032 IF (l_is_policy = 'Y') THEN
2033 Debug_Message('Getting change policy for attribute group - '||rec_attr.ATTR_GROUP_INT_NAME);
2034 ENG_CHANGE_POLICY_PKG.GetChangePolicy
2035 ( p_policy_object_name => 'CATALOG_LIFECYCLE_PHASE'
2036 , p_policy_code => 'CHANGE_POLICY'
2037 , p_policy_pk1_value => l_catalog_id
2038 , p_policy_pk2_value => l_lifecycle_id
2039 , p_policy_pk3_value => l_phase_id
2040 , p_policy_pk4_value => NULL
2041 , p_policy_pk5_value => NULL
2042 , p_attribute_object_name => 'EGO_CATALOG_GROUP'
2043 , p_attribute_code => 'ATTRIBUTE_GROUP'
2044 , p_attribute_value => rec_attr.ATTR_GROUP_ID
2045 , x_policy_value => l_ch_policy
2046 );
2047 Debug_Message('Change Policy for attribute group : '||rec_attr.ATTR_GROUP_INT_NAME || ' is : ' || l_ch_policy );
2048 IF INSTR(l_ch_policy,'NOT') > 0 THEN
2049 l_ch_policy_tbl(rec_attr.ATTR_GROUP_INT_NAME) := 'N';
2050 ELSIF INSTR(l_ch_policy,'ALLOWED') > 0 THEN
2051 l_ch_policy_tbl(rec_attr.ATTR_GROUP_INT_NAME) := 'Y';
2052 ELSIF INSTR(l_ch_policy,'CHANGE') > 0 THEN
2053 l_ch_policy_tbl(rec_attr.ATTR_GROUP_INT_NAME) := 'C';
2054 END IF; --IF INSTR(l_ch_policy,'NOT') > 0 THEN
2055 END IF; --IF (l_is_policy = 'Y') THEN
2056
2057 l_sel_clause := NULL;
2058 l_sql_query := NULL;
2059 l_temp_query := NULL;
2060 l_idx := 1;
2061 l_start := NVL(l_compare_tbl.LAST,0);
2062 cr_ud_attr := dbms_sql.open_cursor;
2063 l_sql_query :=' FROM EGO_MTL_SY_ITEMS_EXT_VL I, EGO_ATTR_GROUPS_DL_V AG '||
2064 ' WHERE AG.APPLICATION_ID = 431 '||
2065 ' AND NVL(AG.ATTR_GROUP_TYPE, ''EGO_ITEMMGMT_GROUP'') = ''EGO_ITEMMGMT_GROUP'' '||
2066 ' AND AG.ATTR_GROUP_ID = I.ATTR_GROUP_ID'||
2067 ' AND I.DATA_LEVEL_ID = :98' || -- Added for R12C: Data_level_id
2068 ' AND AG.ATTR_GROUP_NAME = :99' ; --Bug#5043002'' || rec_attr.ATTR_GROUP_INT_NAME ||'''';
2069 -- Since Each AG is handled at each time. Find once privileges over all items.
2070
2071 -- R12C: BEGIN
2072 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
2073 l_sql_query := l_sql_query || ' AND I.PK1_VALUE = :96';
2074 END IF;
2075
2076 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
2077 l_sql_query := l_sql_query || ' AND I.PK2_VALUE = :97';
2078 END IF;
2079
2080 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
2081 l_sql_query := l_sql_query || ' AND I.PK1_VALUE = :96 AND I.PK2_VALUE = :97';
2082 END IF;
2083 -- R12C: END
2084
2085 --R12C: Finding attr Group display name with prefix to identify
2086 -- the Attribute group data level.
2087 IF rec_attr.DATA_LEVEL_ID = G_ITEM_LEVEL_ID THEN
2088 l_attr_group_display_name := l_item_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
2089 ELSIF rec_attr.DATA_LEVEL_INTERNAL_NAME = G_ITEM_REVISION_LEVEL THEN
2090 l_attr_group_display_name := l_itemRev_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
2091 ELSIF rec_attr.DATA_LEVEL_ID = G_ORG_LEVEL_ID THEN
2092 l_attr_group_display_name := l_itemOrg_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
2093 ELSIF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
2094 l_attr_group_display_name := l_itemSup_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
2095 ELSIF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
2096 l_attr_group_display_name := l_itemSupSite_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
2097 ELSIF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
2098 l_attr_group_display_name := l_itemSupSiteOrg_lable || ':' || rec_attr.ATTR_GROUP_DISP_NAME;
2099 END IF;
2100
2101 Debug_Message('Finding and Populating privilege');
2102 l_priv_name := get_privilege_name(rec_attr.VIEW_PRIVILEGE_ID);
2103 IF(l_priv_name IS NULL) THEN
2104 l_priv_item1 := 'T';
2105 l_priv_item2 := 'T';
2106 l_priv_item3 := 'T';
2107 l_priv_item4 := 'T';
2108 ELSE
2109 l_priv_item1 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item1,
2110 p_org_Id, NULL, NULL, NULL, l_party_id);
2111 l_priv_item2 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item2,
2112 p_org_Id, NULL, NULL, NULL, l_party_id);
2113 l_priv_item3 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item3,
2114 p_org_Id, NULL, NULL, NULL, l_party_id);
2115 l_priv_item4 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item4,
2116 p_org_Id, NULL, NULL, NULL, l_party_id);
2117 END IF;
2118 Debug_Message('Done finding and Populating privilege');
2119 --Deleting all the values of temporary table that might have values for last loop.
2120 l_inv_rev_id_tbl.DELETE ;
2121 -- If is no revision level records for this attribute group
2122 IF rec_attr.REVISION IS NULL THEN
2123 -- For Each attribute Group selected. Get all the attributes in it and
2124 -- Populate the l_compare_table for these attributes
2125 -- The cusor cr_usr_intf_pdh returns
2126 -- Also Perparing Query clause <l_sel_clause> to query for same attributes over other Items.
2127
2128 Debug_Message('Revision is NULL');
2129 Debug_Message('Populating all the attribute values for this attribute group for source system');
2130 FOR rec_ud_attrs IN cr_usr_intf( rec_attr.ATTR_GROUP_INT_NAME, rec_attr.DATA_LEVEL_ID, p_bundle_id ) LOOP
2131 Debug_Message('Processing the attribute: '||rec_ud_attrs.ATTR_INT_NAME);
2132 l_idx := l_idx + 1;
2133 l_compare_tbl.extend();
2134 l_compare_rec.ATTR_GROUP_DISP_NAME := l_attr_group_display_name;
2135 l_compare_rec.ATTR_DISP_NAME := rec_ud_attrs.ATTR_DISPLAY_NAME;
2136 l_compare_rec.ATTRIBUTE_CODE := rec_ud_attrs.ATTR_ID;
2137 l_compare_rec.ATTR_INT_NAME := rec_ud_attrs.ATTR_INT_NAME ;
2138 l_compare_rec.ATTR_GROUP_INT_NAME := rec_ud_attrs.ATTR_GROUP_INT_NAME;
2139
2140 -- Setting properly the privilege in compare table. privileges are calculated
2141 -- for each AG earlier
2142 l_compare_rec.PRIV_ITEM1 := l_priv_item1;
2143 l_compare_rec.PRIV_ITEM2 := l_priv_item2;
2144 l_compare_rec.PRIV_ITEM3 := l_priv_item3;
2145 l_compare_rec.PRIV_ITEM4 := l_priv_item4;
2146
2147 -- Saving UOM Class and value set Associated with this attribute
2148 UOM(l_idx) := rec_ud_attrs.UOM_CLASS;
2149 UOM_USER_CODE(l_idx) := rec_ud_attrs.ATTR_VALUE_UOM;
2150 UOM_DISP_VAL(l_idx) := rec_ud_attrs.ATTR_UOM_DISP_VALUE;
2151 IF rec_ud_attrs.VALIDATION_CODE = EGO_EXT_FWK_PUB.G_NONE_VALIDATION_CODE THEN
2152 VSId(l_idx) := 0;
2153 ELSE
2154 VSId(l_idx) := rec_ud_attrs.VALUE_SET_ID;
2155 END IF;
2156
2157 -- getting and setting source system value
2158 -- If Value set is not associated
2159 IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
2160 Debug_Message('Value set is NOT attached');
2161 IF rec_ud_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
2162 G_META(l_idx) := G_CHAR_FORMAT;
2163 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_DISP_VALUE;
2164 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'C%' OR rec_ud_attrs.DATABASE_COLUMN LIKE 'T%' THEN
2165 G_META(l_idx) := G_CHAR_FORMAT;
2166 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_STR;
2167 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'D%' THEN
2168 G_META(l_idx) := G_DATE_FORMAT;
2169 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_DATE;
2170 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'N%' THEN
2171 G_META(l_idx) := G_NUMBER_FORMAT;
2172 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_NUM;
2173 END IF; --IF rec_ud_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
2174
2175 -- Adding column to sql string to get production values
2176 l_sel_clause := l_sel_clause || ' , ' || rec_ud_attrs.DATABASE_COLUMN;
2177 ELSE --IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
2178 Debug_Message('Value set is attached');
2179 -- If Value set is Associated
2180 l_sel_clause := l_sel_clause || ' , '|| rec_ud_attrs.DATABASE_COLUMN;
2181 G_META(l_idx) := SUBSTR(rec_ud_attrs.DATABASE_COLUMN, 1, 1);
2182 IF rec_ud_attrs.ATTR_DISP_VALUE IS NULL THEN
2183 l_compare_rec.SOURCE_SYS_VAL := Get_SS_Data_For_Val_set
2184 ( p_value_set_id => rec_ud_attrs.VALUE_SET_ID
2185 ,p_validation_code => rec_ud_attrs.VALIDATION_CODE
2186 ,p_str_val => rec_ud_attrs.ATTR_VALUE_STR
2187 ,p_date_val => rec_ud_attrs.ATTR_VALUE_DATE
2188 ,p_num_val => rec_ud_attrs.ATTR_VALUE_NUM );
2189 ELSE
2190 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_DISP_VALUE;
2191 END IF; --IF rec_ud_attrs.ATTR_DISP_VALUE IS NULL THEN
2192 Debug_Message('Display value is: '||l_compare_rec.SOURCE_SYS_VAL);
2193 END IF; --IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
2194
2195
2196 -- if UOM class is attached to the attribute, THEN appending the base UOM to attribute value.
2197 IF rec_ud_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
2198 Debug_Message('UOM Display Value is attached');
2199 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || rec_ud_attrs.ATTR_UOM_DISP_VALUE;
2200 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2201 ELSIF rec_ud_attrs.ATTR_VALUE_UOM IS NOT NULL THEN
2202 Debug_Message('UOM code is attached is attached');
2203 l_temp_query := l_p_atr_sql('UOM_CODE');
2204 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_ud_attrs.ATTR_VALUE_UOM;
2205 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
2206 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2207 ELSIF rec_ud_attrs.UOM_CLASS IS NOT NULL THEN
2208 Debug_Message('UOM Class is attached');
2209 l_temp_query := l_p_atr_sql('UOM_CLASS');
2210 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_ud_attrs.UOM_CLASS;
2211 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
2212 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2213 END IF; --IF rec_ud_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
2214
2215
2216 IF l_is_policy = 'Y' THEN
2217 l_compare_rec.CHANGE_POLICY := l_ch_policy_tbl(rec_ud_attrs.ATTR_GROUP_INT_NAME);
2218 END IF; --IF l_is_policy = 'Y' THEN
2219
2220 l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
2221 Debug_Message('Done processing the attribute: '||rec_ud_attrs.ATTR_INT_NAME);
2222 END LOOP; --FOR rec_ud_attrs IN cr_usr_intf(rec_attr.ATTR_GROUP_INT_NAME) LOOP
2223
2224 Debug_Message('With out Revision - End inserting Attr for Source System for Attr Grp : '||rec_attr.ATTR_GROUP_INT_NAME);
2225 l_sql_query := 'SELECT INVENTORY_ITEM_ID ' || l_sel_clause || l_sql_query ||
2226 ' AND I.INVENTORY_ITEM_ID in (:1,:2,:3,:4)'||
2227 ' AND I.ORGANIZATION_ID = :5' ;
2228
2229 ELSE --IF rec_attr.REVISION IS NULL THEN
2230 -- If there are attributes with Revisions ... Get Attributes in for given Attribute Group.
2231 Debug_Message('Revision is NOT NULL, revision is: '||rec_attr.REVISION);
2232 Debug_Message('Populating all the attribute values for this attribute group for source system');
2233 FOR rec_ud_attrs IN cr_rev_usr_intf( rec_attr.ATTR_GROUP_INT_NAME, l_revision, p_bundle_id ) LOOP
2234 Debug_Message('Processing the attribute: '||rec_ud_attrs.ATTR_INT_NAME);
2235 l_idx := l_idx + 1;
2236 l_compare_tbl.extend();
2237 l_compare_rec.ATTR_GROUP_DISP_NAME := l_attr_group_display_name;
2238 l_compare_rec.ATTR_DISP_NAME := rec_ud_attrs.ATTR_DISPLAY_NAME;
2239 l_compare_rec.ATTRIBUTE_CODE := rec_ud_attrs.ATTR_ID;
2240 l_compare_rec.ATTR_INT_NAME := rec_ud_attrs.ATTR_INT_NAME ;
2241 l_compare_rec.ATTR_GROUP_INT_NAME := rec_ud_attrs.ATTR_GROUP_INT_NAME;
2242
2243 -- setting properly the privilege in compare table. privileges are calculated
2244 -- for each AG earlier
2245 l_compare_rec.PRIV_ITEM1 := l_priv_item1;
2246 l_compare_rec.PRIV_ITEM2 := l_priv_item2;
2247 l_compare_rec.PRIV_ITEM3 := l_priv_item3;
2248 l_compare_rec.PRIV_ITEM4 := l_priv_item4;
2249 -- Nisar End
2250
2251 -- Saving UOM Class and value set Associated with this attribute
2252 IF rec_ud_attrs.VALIDATION_CODE = EGO_EXT_FWK_PUB.G_NONE_VALIDATION_CODE THEN
2253 VSId(l_idx) := 0;
2254 ELSE
2255 VSId(l_idx) := rec_ud_attrs.VALUE_SET_ID;
2256 END IF;
2257 UOM(l_idx) := rec_ud_attrs.UOM_CLASS;
2258 UOM_USER_CODE(l_idx) := rec_ud_attrs.ATTR_VALUE_UOM;
2259 UOM_DISP_VAL(l_idx) := rec_ud_attrs.ATTR_UOM_DISP_VALUE;
2260
2261 -- getting and setting source system value
2262 -- If Value set is not associated
2263 IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
2264 Debug_Message('Value set is NOT attached');
2265 IF rec_ud_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
2266 G_META(l_idx) := G_CHAR_FORMAT;
2267 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_DISP_VALUE;
2268 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'C%' OR rec_ud_attrs.DATABASE_COLUMN LIKE 'T%' THEN
2269 G_META(l_idx) := G_CHAR_FORMAT;
2270 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_STR;
2271 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'D%' THEN
2272 G_META(l_idx) := G_DATE_FORMAT;
2273 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_DATE;
2274 ELSIF rec_ud_attrs.DATABASE_COLUMN LIKE 'N%' THEN
2275 G_META(l_idx) := G_NUMBER_FORMAT;
2276 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_VALUE_NUM;
2277 END IF; --IF rec_ud_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
2278
2279 l_sel_clause := l_sel_clause || ' , ' || rec_ud_attrs.DATABASE_COLUMN;
2280 ELSE --IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
2281 Debug_Message('Value set is attached');
2282 l_sel_clause := l_sel_clause || ' , '|| rec_ud_attrs.DATABASE_COLUMN;
2283 G_META(l_idx) := SUBSTR(rec_ud_attrs.DATABASE_COLUMN, 1, 1);
2284 IF rec_ud_attrs.ATTR_DISP_VALUE IS NULL THEN
2285 l_compare_rec.SOURCE_SYS_VAL := Get_SS_Data_For_Val_set
2286 ( p_value_set_id => rec_ud_attrs.VALUE_SET_ID
2287 ,p_validation_code => rec_ud_attrs.VALIDATION_CODE
2288 ,p_str_val => rec_ud_attrs.ATTR_VALUE_STR
2289 ,p_date_val => rec_ud_attrs.ATTR_VALUE_DATE
2290 ,p_num_val => rec_ud_attrs.ATTR_VALUE_NUM );
2291 ELSE
2292 l_compare_rec.SOURCE_SYS_VAL := rec_ud_attrs.ATTR_DISP_VALUE;
2293 END IF; --IF rec_ud_attrs.ATTR_DISP_VALUE IS NULL THEN
2294 Debug_Message('Display value is: '||l_compare_rec.SOURCE_SYS_VAL);
2295 END IF; --IF rec_ud_attrs.VALUE_SET_ID IS NULL OR rec_ud_attrs.VALUE_SET_ID = 0 THEN
2296
2297 -- if UOM class is attached to the attribute, THEN appending the base UOM to attribute value.
2298 IF rec_ud_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
2299 Debug_Message('UOM Display Value is attached');
2300 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || rec_ud_attrs.ATTR_UOM_DISP_VALUE;
2301 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2302 ELSIF rec_ud_attrs.ATTR_VALUE_UOM IS NOT NULL THEN
2303 Debug_Message('UOM code is attached is attached');
2304 l_temp_query := l_p_atr_sql('UOM_CODE');
2305 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_ud_attrs.ATTR_VALUE_UOM;
2306 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
2307 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2308 ELSIF rec_ud_attrs.UOM_CLASS IS NOT NULL THEN
2309 Debug_Message('UOM Class is attached');
2310 l_temp_query := l_p_atr_sql('UOM_CLASS');
2311 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_ud_attrs.UOM_CLASS;
2312 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
2313 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2314 END IF; --IF rec_ud_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
2315
2316 IF l_is_policy = 'Y' THEN
2317 l_compare_rec.CHANGE_POLICY := l_ch_policy_tbl(rec_ud_attrs.ATTR_GROUP_INT_NAME);
2318 END IF; --IF l_is_policy = 'Y' THEN
2319
2320 l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
2321 END LOOP; --FOR rec_ud_attrs IN cr_rev_usr_intf(rec_attr.ATTR_GROUP_INT_NAME, l_revision) LOOP
2322
2323 -- preparing query to get user defined attribute values from production table
2324 l_sql_query := 'SELECT INVENTORY_ITEM_ID ' || l_sel_clause || l_sql_query;
2325 l_temp_query := NULL;
2326 -- Bug#5043002
2327 k := 50 ;
2328 FOR match_rec IN cr_match_item_rev LOOP
2329 l_temp_query := l_temp_query || ' (I.INVENTORY_ITEM_ID = :'||k ; -- Bug#5043002|| match_rec.INVENTORY_ITEM_ID;
2330 l_inv_rev_id_tbl(k) := match_rec.INVENTORY_ITEM_ID ; -- Bug#5043002
2331 IF match_rec.REVISION_ID IS NOT NULL THEN
2332 k:=k+1 ;
2333 l_temp_query := l_temp_query || ' AND I.REVISION_id = :'||k ; -- Bug#5043002|| match_rec.REVISION_ID;
2334 l_inv_rev_id_tbl(k) := match_rec.REVISION_ID ; -- Bug#5043002
2335 END IF;
2336 l_temp_query := l_temp_query || ' ) ';
2337 l_temp_query := l_temp_query || ' OR ';
2338 k:= k+1 ;
2339 EXIT WHEN cr_match_item_rev%ROWCOUNT = 4;
2340 END LOOP; --FOR match_rec IN cr_match_item_rev LOOP
2341 -- bug#5043002
2342
2343 IF l_temp_query IS NOT NULL THEN
2344 -- The last 'OR' has to be deleted from the l_temp_query for the query to be valid
2345 -- the following code does that
2346 l_temp_query := substr(l_temp_query,1,length(l_temp_query)-3);
2347 l_temp_query := ' AND ('|| l_temp_query ||' ) ' ;
2348 END IF; --IF l_temp_query IS NOT NULL THEN
2349 l_sql_query := l_sql_query || l_temp_query || 'AND I.ORGANIZATION_ID = :1' ;
2350 END IF; --IF rec_attr.REVISION IS NULL THEN
2351
2352 Debug_Message('Done populating attribute values for this attribute group for source system');
2353 Debug_Message('Getting values from production table');
2354
2355 Debug_Message('SQL is - ');
2356 FOR l in 1..(CEIL(LENGTH(l_sql_query)/1000)) LOOP
2357 Debug_Message(SUBSTR(l_sql_query, ((l-1)*1000) + 1, 1000));
2358 END LOOP; --FOR l in 1..(CEIL(LENGTH(l_sql_query)/1000)) LOOP
2359
2360 Debug_Message('Entering User Defined Attributes for Items ');
2361 -- Define Dynamic SQL for querying for other Items.
2362 Debug_Message('Parsing SQL');
2363 DBMS_SQL.PARSE(cr_ud_attr, l_sql_query, DBMS_SQL.native);
2364 Debug_Message('Done parsing SQL');
2365
2366 Debug_Message('Defining columns for SQL');
2367 DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, 1 , l_num_value); --inventory_item_id
2368 Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));
2369 FOR i IN 2..l_idx LOOP
2370 IF G_META(i) = G_CHAR_FORMAT THEN
2371 DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value, 4000);
2372 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2373 DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);
2374 ELSIF G_META(i) = G_DATE_FORMAT THEN
2375 DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);
2376 END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2377 END LOOP; --FOR i IN 2..l_idx LOOP
2378
2379 Debug_Message('Binding variables');
2380 IF rec_attr.REVISION IS NULL THEN
2381 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);
2382 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_item2);
2383 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':3', p_item3);
2384 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':4', p_item4);
2385 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':5', p_org_id);
2386 ELSE
2387 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_org_id);
2388 END IF; --IF rec_attr.REVISION IS NULL THEN
2389
2390 --R12C: BEGIN
2391 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
2392 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
2393 END IF;
2394
2395 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
2396 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
2397 END IF;
2398
2399 IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
2400 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
2401 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
2402 END IF;
2403 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
2404 --R12C: END
2405
2406 --Bug#5043002
2407 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':99', rec_attr.ATTR_GROUP_INT_NAME);
2408
2409 -- Bug#5043002
2410 IF nvl(l_inv_rev_id_tbl.LAST,0)>0 THEN
2411 FOR j in l_inv_rev_id_tbl.FIRST..l_inv_rev_id_tbl.LAST LOOP
2412 DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':'||j, l_inv_rev_id_tbl(j));
2413 END LOOP ;
2414 END IF ;
2415 -- Bug#5043002
2416 Debug_Message('Done binding variables');
2417
2418 l_ignore := DBMS_SQL.EXECUTE(cr_ud_attr);
2419 Debug_Message('Executed SQL, fetching rows');
2420 WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2421 l_cnt := l_start + 1 ;
2422 l_item_id := NULL;
2423 DBMS_SQL.COLUMN_VALUE(cr_ud_attr, 1, l_item_id);
2424 FOR i IN 2..l_idx LOOP
2425 l_str_value := NULL;
2426 l_num_value := NULL;
2427 l_date_value := NULL;
2428 IF G_META(i) = G_CHAR_FORMAT THEN
2429 DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_str_value);
2430 l_int_val := l_str_value;
2431 ELSIF G_META(i) = G_DATE_FORMAT THEN
2432 DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);
2433 l_int_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
2434 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2435 DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_num_value);
2436 l_int_val := TO_CHAR(l_num_value);
2437 END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2438
2439 -- if a value set is associated, then get the display value
2440 IF VSID(i) IS NOT NULL AND VSID(i) <> 0 THEN
2441 IF G_META(i) = G_DATE_FORMAT THEN
2442 l_int_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
2443 431, l_int_val, NULL, NULL, l_compare_tbl(l_cnt).ATTR_INT_NAME, 'EGO_ITEMMGMT_GROUP'
2444 ,l_compare_tbl(l_cnt).ATTR_GROUP_INT_NAME, l_compare_tbl(l_cnt).ATTRIBUTE_CODE
2445 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
2446 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2447 ELSIF G_META(i) = G_CHAR_FORMAT THEN
2448 l_int_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
2449 431, NULL, l_int_val, NULL, l_compare_tbl(l_cnt).ATTR_INT_NAME, 'EGO_ITEMMGMT_GROUP'
2450 ,l_compare_tbl(l_cnt).ATTR_GROUP_INT_NAME, l_compare_tbl(l_cnt).ATTRIBUTE_CODE
2451 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
2452 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2453 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2454 l_int_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
2455 431, NULL, NULL, l_int_val, l_compare_tbl(l_cnt).ATTR_INT_NAME, 'EGO_ITEMMGMT_GROUP'
2456 ,l_compare_tbl(l_cnt).ATTR_GROUP_INT_NAME, l_compare_tbl(l_cnt).ATTRIBUTE_CODE
2457 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
2458 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2459 END IF; --IF G_META(i) = G_DATE_FORMAT THEN
2460 END IF; --IF VSID(i) IS NOT NULL AND VSID(i) <> 0 THEN
2461
2462 --if UOM class is attached to the attribute, THEN appending the base UOM to attribute value.
2463 IF UOM(i) IS NOT NULL THEN
2464 l_temp_query := l_p_atr_sql('UOM_CLASS');
2465 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING UOM(i);
2466 l_int_val := l_int_val || ' ' || l_temp;
2467 END IF; --IF UOM_DISP_VAL(i) IS NOT NULL THEN
2468
2469 populate_compare_tbl( p_compare_table => l_compare_tbl
2470 ,p_index => l_cnt
2471 ,p_sel_item => l_item_id
2472 ,p_value => l_int_val
2473 ,p_item1 => p_item1
2474 ,p_item2 => p_item2
2475 ,p_item3 => p_item3
2476 ,p_item4 => p_item4);
2477 l_cnt := l_cnt + 1;
2478 END LOOP; --FOR i IN 2..l_idx LOOP
2479 END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2480
2481 DBMS_SQL.CLOSE_CURSOR(cr_ud_attr);
2482 Debug_Message('Done Getting values from production table');
2483 END IF; -- IF DATA_LEVEL check against p_supplier_site_id and p_supplier_id
2484 END LOOP; --FOR rec_attr IN cr_attr_groups(l_revision) LOOP
2485 Debug_Message('Done getting User Defined Attributes for Items ');
2486 END IF; --IF is_pdh_batch THEN
2487 Debug_Message('Done processing User Defined attributes.');
2488
2489 Debug_Message('Processing GDSN attributes.');
2490 -- processing GTIN attributes - single row only
2491 l_sel_clause := NULL;
2492 l_val_set_clause := NULL;
2493 l_sql_query := NULL;
2494 l_idx := 1;
2495 l_start := NVL(l_compare_tbl.LAST, 0);
2496
2497 IF is_pdh_batch THEN
2498 Debug_Message('Batch is PDH batch ');
2499 -- Getting GTIN Attributes and inserting into l_compare_table
2500 -- Also preparing query for GTIN attribute values for production Items
2501 Debug_Message('Getting GTIN attributes for Source System');
2502 FOR rec_dd_attrs IN cr_dd_intf_pdh LOOP
2503 Debug_Message('Processing attribute - '||rec_dd_attrs.ATTR_INT_NAME);
2504 l_idx := l_idx + 1;
2505 l_compare_tbl.extend();
2506 l_compare_rec.ATTR_GROUP_DISP_NAME := rec_dd_attrs.ATTR_GROUP_DISP_NAME;
2507 l_compare_rec.ATTR_DISP_NAME := rec_dd_attrs.ATTR_DISPLAY_NAME;
2508 l_compare_rec.ATTRIBUTE_CODE := rec_dd_attrs.ATTR_ID;
2509 l_compare_rec.ATTR_INT_NAME := rec_dd_attrs.ATTR_INT_NAME;
2510 l_compare_rec.ATTR_GROUP_INT_NAME := rec_dd_attrs.ATTR_GROUP_INT_NAME;
2511
2512 -- getting and setting the privilege in compare table
2513 l_attGrp_new := rec_dd_attrs.ATTR_GROUP_DISP_NAME;
2514 l_priv_name := get_privilege_name(rec_dd_attrs.VIEW_PRIVILEGE_ID);
2515
2516 IF (l_priv_name IS NULL) THEN
2517 l_priv_item1 := 'T';
2518 l_priv_item2 := 'T';
2519 l_priv_item3 := 'T';
2520 l_priv_item4 := 'T';
2521 ELSIF(l_attGrp_old <> l_attGrp_new) THEN
2522 l_priv_item1 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item1,
2523 p_org_Id, NULL, NULL, NULL, l_party_id);
2524 l_priv_item2 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item2,
2525 p_org_Id, NULL, NULL, NULL, l_party_id);
2526 l_priv_item3 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item3,
2527 p_org_Id, NULL, NULL, NULL, l_party_id);
2528 l_priv_item4 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item4,
2529 p_org_Id, NULL, NULL, NULL, l_party_id);
2530 END IF; --IF (l_priv_name IS NULL) THEN
2531
2532 -- Finding the Change Policy for an Attribute Group
2533 -- The condition (l_attGrp_old <> l_attGrp_new) is to calculate Change Policy only once for
2534 -- each Attribute Group.
2535 IF l_attGrp_old <> l_attGrp_new AND l_is_policy = 'Y' THEN
2536 ENG_CHANGE_POLICY_PKG.GetChangePolicy
2537 ( p_policy_object_name => 'CATALOG_LIFECYCLE_PHASE'
2538 , p_policy_code => 'CHANGE_POLICY'
2539 , p_policy_pk1_value => l_catalog_id
2540 , p_policy_pk2_value => l_lifecycle_id
2541 , p_policy_pk3_value => l_phase_id
2542 , p_policy_pk4_value => NULL
2543 , p_policy_pk5_value => NULL
2544 , p_attribute_object_name => 'EGO_CATALOG_GROUP'
2545 , p_attribute_code => 'ATTRIBUTE_GROUP'
2546 , p_attribute_value => rec_dd_attrs.ATTR_GROUP_ID
2547 , x_policy_value => l_ch_policy
2548 );
2549
2550 Debug_Message('Change Policy for attribute group : '||rec_dd_attrs.ATTR_GROUP_INT_NAME || ' is : ' || l_ch_policy );
2551 IF INSTR(l_ch_policy,'NOT') > 0 THEN
2552 l_ch_policy_tbl(rec_dd_attrs.ATTR_GROUP_INT_NAME) := 'N';
2553 ELSIF INSTR(l_ch_policy,'ALLOWED') > 0 THEN
2554 l_ch_policy_tbl(rec_dd_attrs.ATTR_GROUP_INT_NAME) := 'Y';
2555 ELSIF INSTR(l_ch_policy,'CHANGE') > 0 THEN
2556 l_ch_policy_tbl(rec_dd_attrs.ATTR_GROUP_INT_NAME) := 'C';
2557 END IF; --IF INSTR(l_ch_policy,'NOT') > 0 THEN
2558 END IF; --IF (l_attGrp_old <> l_attGrp_new) THEN
2559 l_attGrp_old := l_attGrp_new;
2560
2561 l_compare_rec.PRIV_ITEM1 := l_priv_item1;
2562 l_compare_rec.PRIV_ITEM2 := l_priv_item2;
2563 l_compare_rec.PRIV_ITEM3 := l_priv_item3;
2564 l_compare_rec.PRIV_ITEM4 := l_priv_item4;
2565
2566 -- Saving UOM Class and value set Associated with this attribute
2567 UOM(l_idx) := rec_dd_attrs.UOM_CLASS;
2568 UOM_USER_CODE(l_idx) := rec_dd_attrs.ATTR_VALUE_UOM;
2569 UOM_DISP_VAL(l_idx) := rec_dd_attrs.ATTR_UOM_DISP_VALUE;
2570 IF rec_dd_attrs.VALIDATION_CODE = EGO_EXT_FWK_PUB.G_NONE_VALIDATION_CODE THEN
2571 VSId(l_idx) := 0;
2572 ELSE
2573 VSId(l_idx) := rec_dd_attrs.VALUE_SET_ID;
2574 END IF;
2575
2576 -- getting and setting source system value
2577 -- If Value set is not associated
2578 IF rec_dd_attrs.VALUE_SET_ID = 0 OR rec_dd_attrs.VALUE_SET_ID IS NULL THEN
2579 Debug_Message('Value set is NOT attached');
2580 IF rec_dd_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
2581 G_META(l_idx) := G_CHAR_FORMAT;
2582 l_compare_rec.SOURCE_SYS_VAL := rec_dd_attrs.ATTR_DISP_VALUE;
2583 ELSIF rec_dd_attrs.DATA_TYPE_CODE IN (G_CHAR_FORMAT , 'A') THEN
2584 G_META(l_idx) := G_CHAR_FORMAT;
2585 l_compare_rec.SOURCE_SYS_VAL := rec_dd_attrs.ATTR_VALUE_STR;
2586 ELSIF rec_dd_attrs.DATA_TYPE_CODE IN (G_TIME_FORMAT,G_DATE_TIME_FORMAT) THEN
2587 G_META(l_idx) := G_DATE_FORMAT;
2588 l_compare_rec.SOURCE_SYS_VAL := TO_CHAR(rec_dd_attrs.ATTR_VALUE_DATE, 'MM/DD/YYYY HH24:MI:SS');
2589 ELSIF rec_dd_attrs.DATA_TYPE_CODE = G_NUMBER_FORMAT THEN
2590 G_META(l_idx) := G_NUMBER_FORMAT;
2591 l_compare_rec.SOURCE_SYS_VAL := TO_CHAR(rec_dd_attrs.ATTR_VALUE_NUM);
2592 END IF; --IF rec_dd_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
2593
2594 -- Adding column to sql string to get production values
2595 l_sel_clause := l_sel_clause || ' , '|| rec_dd_attrs.DATABASE_COLUMN;
2596 ELSE --IF rec_dd_attrs.VALUE_SET_ID = 0 OR rec_dd_attrs.VALUE_SET_ID IS NULL THEN
2597 Debug_Message('Value set is attached');
2598 l_sel_clause := l_sel_clause || ' , '|| rec_dd_attrs.DATABASE_COLUMN;
2599 G_META(l_idx) := rec_dd_attrs.DATA_TYPE_CODE; --G_CHAR_FORMAT;
2600 IF rec_dd_attrs.ATTR_DISP_VALUE IS NULL THEN
2601 l_compare_rec.SOURCE_SYS_VAL := Get_SS_Data_For_Val_set
2602 ( p_value_set_id => rec_dd_attrs.VALUE_SET_ID
2603 ,p_validation_code => rec_dd_attrs.VALIDATION_CODE
2604 ,p_str_val => rec_dd_attrs.ATTR_VALUE_STR
2605 ,p_date_val => rec_dd_attrs.ATTR_VALUE_DATE
2606 ,p_num_val => rec_dd_attrs.ATTR_VALUE_NUM );
2607 ELSE
2608 l_compare_rec.SOURCE_SYS_VAL := rec_dd_attrs.ATTR_DISP_VALUE;
2609 END IF; --IF rec_dd_attrs.ATTR_DISP_VALUE IS NULL THEN
2610 Debug_Message('Display value is: '||l_compare_rec.SOURCE_SYS_VAL);
2611 END IF; --IF rec_dd_attrs.VALUE_SET_ID = 0 OR rec_dd_attrs.VALUE_SET_ID IS NULL THEN
2612
2613 -- if UOM class is attached to the attribute, THEN appending the base UOM to attribute value.
2614 IF rec_dd_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
2615 Debug_Message('UOM Display Value is attached');
2616 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || rec_dd_attrs.ATTR_UOM_DISP_VALUE;
2617 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2618 ELSIF rec_dd_attrs.ATTR_VALUE_UOM IS NOT NULL THEN
2619 Debug_Message('UOM code is attached is attached');
2620 l_temp_query := l_p_atr_sql('UOM_CODE');
2621 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_dd_attrs.ATTR_VALUE_UOM;
2622 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
2623 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2624 ELSIF rec_dd_attrs.UOM_CLASS IS NOT NULL THEN
2625 Debug_Message('UOM Class is attached');
2626 l_temp_query := l_p_atr_sql('UOM_CLASS');
2627 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_dd_attrs.UOM_CLASS;
2628 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
2629 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2630 END IF; --IF rec_dd_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
2631
2632 IF l_is_policy = 'Y' THEN
2633 l_compare_rec.CHANGE_POLICY := l_ch_policy_tbl(rec_dd_attrs.ATTR_GROUP_INT_NAME);
2634 END IF; --IF l_is_policy = 'Y' THEN
2635
2636 l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
2637
2638 END LOOP; --FOR rec_dd_attrs IN cr_dd_intf_pdh LOOP
2639 Debug_Message('Done getting GDSN attributes for Source System');
2640 ELSE --IF is_pdh_batch THEN
2641 Debug_Message('Batch is NON-PDH batch');
2642 Debug_Message('Getting GDSN attributes for Source System');
2643 FOR rec_dd_attrs IN cr_dd_intf( p_bundle_id ) LOOP
2644 Debug_Message('Processing attribute - '||rec_dd_attrs.ATTR_INT_NAME);
2645 l_idx := l_idx + 1;
2646 l_compare_tbl.extend();
2647 l_compare_rec.ATTR_GROUP_DISP_NAME := rec_dd_attrs.ATTR_GROUP_DISP_NAME;
2648 l_compare_rec.ATTR_DISP_NAME := rec_dd_attrs.ATTR_DISPLAY_NAME;
2649 l_compare_rec.ATTRIBUTE_CODE := rec_dd_attrs.ATTR_ID;
2650 l_compare_rec.ATTR_INT_NAME := rec_dd_attrs.ATTR_INT_NAME ;
2651 l_compare_rec.ATTR_GROUP_INT_NAME := rec_dd_attrs.ATTR_GROUP_INT_NAME;
2652
2653 -- getting and setting the privilege in compare table
2654 l_attGrp_new := rec_dd_attrs.ATTR_GROUP_DISP_NAME;
2655 l_priv_name := get_privilege_name(rec_dd_attrs.VIEW_PRIVILEGE_ID);
2656 IF (l_priv_name IS NULL) THEN
2657 l_priv_item1 := 'T';
2658 l_priv_item2 := 'T';
2659 l_priv_item3 := 'T';
2660 l_priv_item4 := 'T';
2661 ELSIF(l_attGrp_old <> l_attGrp_new) THEN
2662 l_priv_item1 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item1,
2663 p_org_Id, NULL, NULL, NULL, l_party_id);
2664 l_priv_item2 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item2,
2665 p_org_Id, NULL, NULL, NULL, l_party_id);
2666 l_priv_item3 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item3,
2667 p_org_Id, NULL, NULL, NULL, l_party_id);
2668 l_priv_item4 := EGO_DATA_SECURITY.CHECK_FUNCTION( 1.0, l_priv_name, 'EGO_ITEM', p_item4,
2669 p_org_Id, NULL, NULL, NULL, l_party_id);
2670 END IF; -- IF (l_priv_name IS NULL) THEN
2671
2672 -- Finding the Change Policy for an Attribute Group
2673 -- The condition (l_attGrp_old <> l_attGrp_new) is to calculate Change Policy only once for
2674 -- each Attribute Group.
2675 IF l_attGrp_old <> l_attGrp_new AND l_is_policy = 'Y' THEN
2676 ENG_CHANGE_POLICY_PKG.GetChangePolicy
2677 ( p_policy_object_name => 'CATALOG_LIFECYCLE_PHASE'
2678 , p_policy_code => 'CHANGE_POLICY'
2679 , p_policy_pk1_value => l_catalog_id
2680 , p_policy_pk2_value => l_lifecycle_id
2681 , p_policy_pk3_value => l_phase_id
2682 , p_policy_pk4_value => NULL
2683 , p_policy_pk5_value => NULL
2684 , p_attribute_object_name => 'EGO_CATALOG_GROUP'
2685 , p_attribute_code => 'ATTRIBUTE_GROUP'
2686 , p_attribute_value => rec_dd_attrs.ATTR_GROUP_ID
2687 , x_policy_value => l_ch_policy
2688 );
2689
2690 Debug_Message('Change Policy for attribute group : '||rec_dd_attrs.ATTR_GROUP_INT_NAME || ' is : ' || l_ch_policy );
2691 IF INSTR(l_ch_policy,'NOT') > 0 THEN
2692 l_ch_policy_tbl(rec_dd_attrs.ATTR_GROUP_INT_NAME) := 'N';
2693 ELSIF INSTR(l_ch_policy,'ALLOWED') > 0 THEN
2694 l_ch_policy_tbl(rec_dd_attrs.ATTR_GROUP_INT_NAME) := 'Y';
2695 ELSIF INSTR(l_ch_policy,'CHANGE') > 0 THEN
2696 l_ch_policy_tbl(rec_dd_attrs.ATTR_GROUP_INT_NAME) := 'C';
2697 END IF; --IF INSTR(l_ch_policy,'NOT') > 0 THEN
2698 END IF; --IF (l_attGrp_old <> l_attGrp_new) THEN
2699 l_attGrp_old := l_attGrp_new;
2700
2701 l_compare_rec.PRIV_ITEM1 := l_priv_item1;
2702 l_compare_rec.PRIV_ITEM2 := l_priv_item2;
2703 l_compare_rec.PRIV_ITEM3 := l_priv_item3;
2704 l_compare_rec.PRIV_ITEM4 := l_priv_item4;
2705
2706 -- Saving UOM Class and value set Associated with this attribute
2707 UOM(l_idx) := rec_dd_attrs.UOM_CLASS;
2708 UOM_USER_CODE(l_idx) := rec_dd_attrs.ATTR_VALUE_UOM;
2709 UOM_DISP_VAL(l_idx) := rec_dd_attrs.ATTR_UOM_DISP_VALUE;
2710 IF rec_dd_attrs.VALIDATION_CODE = EGO_EXT_FWK_PUB.G_NONE_VALIDATION_CODE THEN
2711 VSId(l_idx) := 0;
2712 ELSE
2713 VSId(l_idx) := rec_dd_attrs.VALUE_SET_ID;
2714 END IF;
2715
2716 -- getting and setting source system value
2717 -- If Value set is not associated
2718 IF rec_dd_attrs.VALUE_SET_ID = 0 OR rec_dd_attrs.VALUE_SET_ID IS NULL THEN
2719 Debug_Message('Value set is NOT attached');
2720 IF rec_dd_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
2721 G_META(l_idx) := G_CHAR_FORMAT;
2722 l_compare_rec.SOURCE_SYS_VAL := rec_dd_attrs.ATTR_DISP_VALUE;
2723 ELSIF rec_dd_attrs.DATA_TYPE_CODE IN (G_CHAR_FORMAT, 'A') THEN
2724 G_META(l_idx) := G_CHAR_FORMAT;
2725 l_compare_rec.SOURCE_SYS_VAL := rec_dd_attrs.ATTR_VALUE_STR;
2726 ELSIF rec_dd_attrs.DATA_TYPE_CODE IN (G_TIME_FORMAT, G_DATE_TIME_FORMAT) THEN
2727 G_META(l_idx) := G_DATE_FORMAT;
2728 l_compare_rec.SOURCE_SYS_VAL := TO_CHAR(rec_dd_attrs.ATTR_VALUE_DATE, 'MM/DD/YYYY HH24:MI:SS');
2729 ELSIF rec_dd_attrs.DATA_TYPE_CODE = G_NUMBER_FORMAT THEN
2730 G_META(l_idx) := G_NUMBER_FORMAT;
2731 l_compare_rec.SOURCE_SYS_VAL := TO_CHAR(rec_dd_attrs.ATTR_VALUE_NUM);
2732 END IF; --IF rec_dd_attrs.ATTR_DISP_VALUE IS NOT NULL THEN
2733
2734 -- Adding column to sql string to get production values
2735 l_sel_clause := l_sel_clause || ' , '|| rec_dd_attrs.DATABASE_COLUMN;
2736 ELSE --IF rec_dd_attrs.VALUE_SET_ID = 0 OR rec_dd_attrs.VALUE_SET_ID IS NULL THEN
2737 Debug_Message('Value set is attached');
2738 l_sel_clause := l_sel_clause || ' , '|| rec_dd_attrs.DATABASE_COLUMN;
2739 G_META(l_idx) := rec_dd_attrs.DATA_TYPE_CODE; --G_CHAR_FORMAT;
2740 IF rec_dd_attrs.ATTR_DISP_VALUE IS NULL THEN
2741 l_compare_rec.SOURCE_SYS_VAL := Get_SS_Data_For_Val_set
2742 ( p_value_set_id => rec_dd_attrs.VALUE_SET_ID
2743 ,p_validation_code => rec_dd_attrs.VALIDATION_CODE
2744 ,p_str_val => rec_dd_attrs.ATTR_VALUE_STR
2745 ,p_date_val => rec_dd_attrs.ATTR_VALUE_DATE
2746 ,p_num_val => rec_dd_attrs.ATTR_VALUE_NUM );
2747 ELSE
2748 l_compare_rec.SOURCE_SYS_VAL := rec_dd_attrs.ATTR_DISP_VALUE;
2749 END IF; --IF rec_dd_attrs.ATTR_DISP_VALUE IS NULL THEN
2750 END IF; --IF rec_dd_attrs.VALUE_SET_ID = 0 OR rec_dd_attrs.VALUE_SET_ID IS NULL THEN
2751
2752 -- if UOM class is attached to the attribute, THEN appending the base UOM to attribute value.
2753 IF rec_dd_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
2754 Debug_Message('UOM Display Value is attached');
2755 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || rec_dd_attrs.ATTR_UOM_DISP_VALUE;
2756 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2757 ELSIF rec_dd_attrs.ATTR_VALUE_UOM IS NOT NULL THEN
2758 Debug_Message('UOM code is attached is attached');
2759 l_temp_query := l_p_atr_sql('UOM_CODE');
2760 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_dd_attrs.ATTR_VALUE_UOM;
2761 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
2762 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2763 ELSIF rec_dd_attrs.UOM_CLASS IS NOT NULL THEN
2764 Debug_Message('UOM Class is attached');
2765 l_temp_query := l_p_atr_sql('UOM_CLASS');
2766 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING rec_dd_attrs.UOM_CLASS;
2767 l_compare_rec.SOURCE_SYS_VAL := l_compare_rec.SOURCE_SYS_VAL || ' ' || l_temp;
2768 Debug_Message('Value after appending UOM is: '||l_compare_rec.SOURCE_SYS_VAL);
2769 END IF; --IF rec_dd_attrs.ATTR_UOM_DISP_VALUE IS NOT NULL THEN
2770
2771 IF l_is_policy = 'Y' THEN
2772 l_compare_rec.CHANGE_POLICY := l_ch_policy_tbl(rec_dd_attrs.ATTR_GROUP_INT_NAME);
2773 END IF; --IF l_is_policy = 'Y' THEN
2774
2775 l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
2776 END LOOP; --FOR rec_dd_attrs IN cr_dd_intf LOOP
2777 Debug_Message('Done getting GDSN attributes for Source System');
2778 END IF; --IF is_pdh_batch THEN
2779
2780 Debug_Message('Preparing SQL for getting attribute values for production items.');
2781 -- If Selection clause if NULL THEN there are no GTIN Attrs to be selected
2782 IF l_sel_clause IS NOT NULL THEN
2783 IF is_pdh_batch THEN
2784 l_sql_query := 'SELECT INVENTORY_ITEM_ID '|| l_sel_clause ||
2785 ' FROM EGO_ITEM_GTN_ATTRS_VL I ' ||
2786 ' WHERE I.INVENTORY_ITEM_ID = :1 ' ||
2787 ' AND I.ORGANIZATION_ID = :2 ' ;
2788 ELSE
2789 l_sql_query := 'SELECT INVENTORY_ITEM_ID '|| l_sel_clause ||
2790 ' FROM EGO_ITEM_GTN_ATTRS_VL I ' ||
2791 ' WHERE I.INVENTORY_ITEM_ID IN (:1,:2,:3,:4)' ||
2792 ' AND I.ORGANIZATION_ID = :5 ' ;
2793 END IF; --IF is_pdh_batch THEN
2794
2795 Debug_Message('SQL is - ');
2796 FOR l in 1..(CEIL(LENGTH(l_sql_query)/1000)) LOOP
2797 Debug_Message(SUBSTR(l_sql_query, ((l-1)*1000) + 1, 1000));
2798 END LOOP; --FOR l in 1..(CEIL(LENGTH(l_sql_query)/1000)) LOOP
2799
2800 -- Defining a Dynamic Cursor -
2801 cr_dd_attr := DBMS_SQL.OPEN_CURSOR;
2802 Debug_Message('Parsing SQL');
2803 DBMS_SQL.PARSE(cr_dd_attr, l_sql_query, DBMS_SQL.native);
2804 Debug_Message('Done Parsing SQL');
2805 Debug_Message('Defining columns');
2806 DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, 1 , l_num_value); --inventory _item _id
2807 Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));
2808 FOR i IN 2..l_idx LOOP
2809 IF G_META(i) = G_CHAR_FORMAT THEN
2810 DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_str_value, 4000);
2811 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2812 DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_num_value);
2813 ELSIF G_META(i) = G_DATE_FORMAT THEN
2814 DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_date_value);
2815 END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2816 END LOOP; --FOR i IN 2..l_idx LOOP
2817
2818 Debug_Message('Binding variables');
2819 IF is_pdh_batch THEN
2820 DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2821 DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_org_id);
2822 ELSE
2823 DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2824 DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_item2);
2825 DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':3', p_item3);
2826 DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':4', p_item4);
2827 DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':5', p_org_id);
2828 END IF; --IF is_pdh_batch THEN
2829 Debug_Message('Done binding variables');
2830
2831 l_ignore := DBMS_SQL.EXECUTE(cr_dd_attr);
2832 Debug_Message('Executed SQL, fetching rows');
2833 WHILE DBMS_SQL.FETCH_ROWS(cr_dd_attr) > 0 LOOP
2834 l_cnt := l_start + 1;
2835 FOR i IN 2..l_idx LOOP
2836 l_str_value := NULL;
2837 l_num_value := NULL;
2838 l_date_value := NULL;
2839 IF G_META(i) = G_CHAR_FORMAT THEN
2840 DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_str_value);
2841 l_val := l_str_value;
2842 ELSIF G_META(i) = G_DATE_FORMAT THEN
2843 DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_date_value);
2844 l_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
2845 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2846 DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_num_value);
2847 l_val := TO_CHAR(l_num_value);
2848 END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2849
2850 -- if a value set is associated, then get the display value
2851 IF VSID(i) IS NOT NULL AND VSID(i) <> 0 THEN
2852 IF G_META(i) = G_DATE_FORMAT THEN
2853 l_int_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
2854 431, l_int_val, NULL, NULL, l_compare_tbl(l_cnt).ATTR_INT_NAME, 'EGO_ITEM_GTIN_ATTRS'
2855 ,l_compare_tbl(l_cnt).ATTR_GROUP_INT_NAME, l_compare_tbl(l_cnt).ATTRIBUTE_CODE
2856 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
2857 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2858 ELSIF G_META(i) = G_CHAR_FORMAT THEN
2859 l_int_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
2860 431, NULL, l_int_val, NULL, l_compare_tbl(l_cnt).ATTR_INT_NAME, 'EGO_ITEM_GTIN_ATTRS'
2861 ,l_compare_tbl(l_cnt).ATTR_GROUP_INT_NAME, l_compare_tbl(l_cnt).ATTRIBUTE_CODE
2862 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
2863 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2864 ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2865 l_int_val := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
2866 431, NULL, NULL, l_int_val, l_compare_tbl(l_cnt).ATTR_INT_NAME, 'EGO_ITEM_GTIN_ATTRS'
2867 ,l_compare_tbl(l_cnt).ATTR_GROUP_INT_NAME, l_compare_tbl(l_cnt).ATTRIBUTE_CODE
2868 ,'EGO_ITEM' ,'ORGANIZATION_ID', p_org_id, 'INVENTORY_ITEM_ID', l_item_id, NULL
2869 ,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2870 END IF; --IF G_META(i) = G_DATE_FORMAT THEN
2871 END IF; --IF VSID(i) IS NOT NULL AND VSID(i) <> 0 THEN
2872
2873 --if UOM class is attached to the attribute, THEN appending the base UOM to attribute value.
2874 IF UOM(i) IS NOT NULL THEN
2875 l_temp_query := l_p_atr_sql('UOM_CLASS');
2876 EXECUTE IMMEDIATE l_temp_query INTO l_temp USING UOM(i);
2877 l_val := l_val || ' ' || l_temp;
2878 END IF; --IF UOM_DISP_VAL(i) IS NOT NULL THEN
2879
2880 populate_compare_tbl( p_compare_table => l_compare_tbl ,
2881 p_index => l_cnt,
2882 p_sel_item => l_item_id ,
2883 p_value => l_val ,
2884 p_item1 => p_item1 ,
2885 p_item2 => p_item2 ,
2886 p_item3 => p_item3 ,
2887 p_item4 => p_item4);
2888 l_cnt := l_cnt + 1;
2889 END LOOP; --FOR i IN 2..l_idx LOOP
2890 END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_dd_attr) > 0 LOOP
2891 DBMS_SQL.CLOSE_CURSOR(cr_dd_attr);
2892 Debug_Message('Done getting attribute values for production items.');
2893 END IF; --IF l_sel_clause IS NOT NULL THEN
2894 Debug_Message('Done Processing GDSN attributes.');
2895 Debug_Message('Done GET_COMPARED_DATA Successfully at - '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
2896 RETURN l_compare_tbl;
2897 EXCEPTION
2898 WHEN OTHERS THEN
2899 err_compare_tbl := SYSTEM.EGO_COMPARE_VIEW_TABLE();
2900 err_compare_rec := SYSTEM.EGO_COMPARE_VIEW_REC('', '', '','', '', '','', '', '','', '','','','','');
2901 --err_compare_rec.ATTR_GROUP_DISP_NAME := 'Encountered error, No search conducted';
2902 err_compare_rec.ATTR_GROUP_DISP_NAME := SQLERRM;
2903 err_compare_tbl.EXTEND();
2904 err_compare_tbl(1) := err_compare_rec;
2905 Debug_Message('Error - '||SQLERRM);
2906 Debug_Message('Done GET_COMPARED_DATA with error at - '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
2907 RETURN err_compare_tbl;
2908 END GET_COMPARED_DATA;
2909 END EGO_IMPORT_BATCH_COMPARE_PVT;
[Home] [Help]