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