[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ATTR_UTIL
Source
1 PACKAGE BODY ENG_CHANGE_ATTR_UTIL AS
2 /* $Header: ENGVCAUB.pls 120.48.12020000.2 2012/07/13 02:21:07 mshirkol ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ENG_CHANGE_ATTR_UTIL' ;
5
6 -- For Debug
7 g_debug_file UTL_FILE.FILE_TYPE ;
8 g_debug_flag BOOLEAN := FALSE ; -- For Debug, set TRUE
9 g_output_dir VARCHAR2(240) := null ;
10 g_debug_filename VARCHAR2(30) := 'EngChangeAttrUtil.log' ;
11 g_debug_errmesg VARCHAR2(400);
12
13 /* Cache object to cache the sql bult based on the attr group id*/
14 TYPE CACHED_PLSQL_RECORD IS RECORD
15 ( ATTR_GROUP_ID NUMBER
16 ,ATTR_GROUP_SQL VARCHAR2(32000)
17 );
18 TYPE CACHED_PLSQL_TABLE IS TABLE OF CACHED_PLSQL_RECORD
19 INDEX BY BINARY_INTEGER;
20 G_CACHED_PLSQL_table CACHED_PLSQL_TABLE;
21
22 /********************************************************************
23 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
24 * Write_Debug
25 * Parameters IN :
26 * Parameters OUT:
27 * Purpose : These procedures are for test and debug
28 *********************************************************************/
29
30 -- Open_Debug_Session
31 PROCEDURE Open_Debug_Session
32 ( p_output_dir IN VARCHAR2 := NULL
33 , p_file_name IN VARCHAR2 := NULL
34 )
35 IS
36 l_found NUMBER := 0;
37 l_utl_file_dir VARCHAR2(2000);
38 l_error_mesg VARCHAR2(400) ;
39
40 BEGIN
41 NULL ;
42
43 /*************************************************
44 -- COMMENT OUT
45 -- NEED TO ENHANCE THIS
46
47 IF p_output_dir IS NOT NULL THEN
48 g_output_dir := p_output_dir ;
49
50 END IF ;
51
52 IF p_file_name IS NOT NULL THEN
53 g_debug_filename := p_file_name ;
54 END IF ;
55
56 IF g_output_dir IS NULL
57 THEN
58
59 g_output_dir := FND_PROFILE.VALUE('ECX_UTL_LOG_DIR') ;
60
61 END IF;
62
63 select value
64 INTO l_utl_file_dir
65 FROM v$parameter
66 WHERE name = 'utl_file_dir';
67
68 l_found := INSTR(l_utl_file_dir, g_output_dir);
69
70 IF l_found = 0
71 THEN
72 l_error_mesg := 'Debug Session could not be started. ' ||
73 'The output directory is invalid.';
74
75 -- 'Debug Session could not be started because the ' ||
76 -- ' output directory name is invalid. ' ||
77 -- ' Output directory must be one of the directory ' ||
78 -- ' value in v$parameter for name = utl_file_dir ';
79
80 -- FND_MSG_PUB.Add_Exc_Msg
81 -- ( G_PKG_NAME ,
82 -- 'Open_Debug_Session' ,
83 -- l_error_mesg ) ;
84
85 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
86 -- RETURN;
87 END IF;
88
89 g_debug_file := utl_file.fopen( g_output_dir
90 , g_debug_filename
91 , 'w');
92
93 g_debug_flag := TRUE ;
94 *************************************************/
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
99 g_debug_flag := FALSE;
100 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
101
102 END Open_Debug_Session ;
103
104
105 -- Close Debug_Session
106 PROCEDURE Close_Debug_Session
107 IS
108 l_error_mesg VARCHAR2(400) ;
109 BEGIN
110
111 IF utl_file.is_open(g_debug_file)
112 THEN
113 utl_file.fclose(g_debug_file);
114 END IF ;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
119 g_debug_flag := FALSE;
120
121
122 -- l_error_mesg := 'Debug Session could not be closed because the ' ||
123 -- Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240) ;
124
125 -- FND_MSG_PUB.Add_Exc_Msg
126 -- ( G_PKG_NAME ,
127 -- 'Close_Debug_Session' ,
128 -- l_error_mesg ) ;
129 --
130 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
131
132 END Close_Debug_Session ;
133
134
135 -- Write Debug Message
136 PROCEDURE Write_Debug
137 ( p_debug_message IN VARCHAR2 )
138 IS
139 l_error_mesg VARCHAR2(400) ;
140 BEGIN
141
142 NULL ;
143 -- NEED TO ENHANCE THIS LATER
144 -- FND_FILE.put_line(FND_FILE.LOG, 'Write_Debug => '|| p_debug_message ) ;
145
146 --
147 -- IF utl_file.is_open(g_debug_file)
148 -- THEN
149 -- utl_file.put_line(g_debug_file, p_debug_message);
150 -- END IF ;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
155 g_debug_flag := FALSE;
156
157 -- l_error_mesg := 'In Debug Mode, Write_Debug procedure faild closed because the ' ||
158 -- Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240) ;
159
160 -- FND_MSG_PUB.Add_Exc_Msg
161 -- ( G_PKG_NAME ,
162 -- 'Write_Debug' ,
163 -- l_error_mesg ) ;
164
165 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
166
167 END Write_Debug;
168
169 --------------------------
170 --- GET LIST OF PRIVILEGES
171 --------------------------
172
173 FUNCTION Get_User_Attrs_Privs (
174 p_inventory_item_id IN NUMBER
175 ,p_organization_id IN NUMBER
176 ,p_entity_id IN NUMBER DEFAULT NULL
177 ,p_entity_index IN NUMBER DEFAULT NULL
178 ,p_entity_code IN VARCHAR2 DEFAULT NULL
179 )
180 RETURN EGO_VARCHAR_TBL_TYPE
181 IS
182
183 l_party_id VARCHAR2(30);
184 l_return_status VARCHAR2(1);
185 l_user_privileges_table EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE;
186 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
187 l_privilege_table_index NUMBER;
188 l_token_table ERROR_HANDLER.Token_Tbl_Type;
189
190 BEGIN
191
192 -------------------------------------------------------------
193 -- This query assumes that the user is logged in correctly --
194 -------------------------------------------------------------
195 BEGIN
196 SELECT 'HZ_PARTY:'||TO_CHAR(PARTY_ID)
197 INTO l_party_id
198 FROM EGO_USER_V
199 WHERE USER_NAME = FND_GLOBAL.USER_NAME;
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202
203 ERROR_HANDLER.Add_Error_Message(
204 p_message_name => 'EGO_EF_NO_NAME_TO_VALIDATE'
205 ,p_application_id => 'EGO'
206 ,p_message_type => FND_API.G_RET_STS_ERROR
207 ,p_entity_id => p_entity_id
208 ,p_entity_index => p_entity_index
209 ,p_entity_code => p_entity_code
210 );
211
212 RAISE FND_API.G_EXC_ERROR;
213
214 END;
215
216 EGO_DATA_SECURITY.Get_Functions(
217 p_api_version => 1.0
218 ,p_object_name => 'EGO_ITEM'
219 ,p_instance_pk1_value => p_inventory_item_id
220 ,p_instance_pk2_value => p_organization_id
221 ,p_user_name => l_party_id
222 ,x_return_status => l_return_status
223 ,x_privilege_tbl => l_user_privileges_table
224 );
225
226 ---------------------------------------------------------------------
227 -- If the user has privileges on this instance, we need to convert --
228 -- the table we have into a table of type EGO_VARCHAR_TBL_TYPE --
229 ---------------------------------------------------------------------
230 IF (l_return_status = 'T' AND
231 l_user_privileges_table.COUNT > 0) THEN
232
233 l_user_privileges_on_object := EGO_VARCHAR_TBL_TYPE();
234
235 l_privilege_table_index := l_user_privileges_table.FIRST;
236 WHILE (l_privilege_table_index <= l_user_privileges_table.LAST)
237 LOOP
238 l_user_privileges_on_object.EXTEND();
239 l_user_privileges_on_object(l_user_privileges_on_object.LAST) := l_user_privileges_table(l_privilege_table_index);
240 l_privilege_table_index := l_user_privileges_table.NEXT(l_privilege_table_index);
241 END LOOP;
242
243 ELSE
244
245 -----------------------------------------------
246 -- If Get_Functions failed, report the error --
247 -----------------------------------------------
248 DECLARE
249
250 l_error_message_name VARCHAR2(30);
251 l_org_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
252 l_item_number MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
253
254 BEGIN
255
256 IF (l_return_status = 'F') THEN
257 l_error_message_name := 'EGO_EF_BL_NO_PRIVS_ON_INSTANCE';
258 ELSE
259 l_error_message_name := 'EGO_EF_BL_PRIV_CHECK_ERROR';
260 END IF;
261
262 SELECT CONCATENATED_SEGMENTS
263 INTO l_item_number
264 FROM MTL_SYSTEM_ITEMS_KFV
265 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
266 AND ORGANIZATION_ID = p_organization_id;
267
268 SELECT ORGANIZATION_CODE
269 INTO l_org_code
270 FROM MTL_PARAMETERS
271 WHERE ORGANIZATION_ID = p_organization_id;
272
273 l_token_table(1).TOKEN_NAME := 'USER_NAME';
274 l_token_table(1).TOKEN_VALUE := FND_GLOBAL.USER_NAME;
275 l_token_table(2).TOKEN_NAME := 'ITEM_NUMBER';
276 l_token_table(2).TOKEN_VALUE := l_item_number;
277 l_token_table(3).TOKEN_NAME := 'ORG_CODE';
278 l_token_table(3).TOKEN_VALUE := l_org_code;
279
280 ERROR_HANDLER.Add_Error_Message(
281 p_message_name => l_error_message_name
282 ,p_application_id => 'EGO'
283 ,p_token_tbl => l_token_table
284 ,p_message_type => FND_API.G_RET_STS_ERROR
285 ,p_entity_id => p_entity_id
286 ,p_entity_index => p_entity_index
287 ,p_entity_code => p_entity_code
288 );
289
290 RAISE FND_API.G_EXC_ERROR;
291
292 END;
293 END IF;
294
295 RETURN l_user_privileges_on_object;
296
297 END Get_User_Attrs_Privs;
298
299
300
301 PROCEDURE INSERT_ITEM_ATTRS
302 ( p_api_version IN NUMBER
303 ,p_object_name IN VARCHAR2
304 ,p_application_id IN NUMBER
305 ,p_attr_group_type IN VARCHAR2
306 ,p_base_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
307 ,p_tl_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
308 ,x_return_status OUT NOCOPY VARCHAR2
309 ,x_errorcode OUT NOCOPY NUMBER
310 ,x_msg_count OUT NOCOPY NUMBER
311 ,x_msg_data OUT NOCOPY VARCHAR2
312 ,p_exec_mode IN VARCHAR2
313 )
314
315 IS
316
317 l_attr_name_dml VARCHAR2(3200);
318 l_attr_value_dml VARCHAR2(3200);
319 l_attr_base_dml VARCHAR2(3200);
320 l_attr_tl_dml VARCHAR2(3200);
321 l_pending_base_tbl VARCHAR2(30);
322 l_pending_tl_tbl VARCHAR2(30);
323 l_extension_id NUMBER :=-1000;
324 l_temp_tl_dml VARCHAR2(3200);
325 l_inventory_item_id NUMBER;
326 l_organization_id NUMBER;
327
328
329 cursor C_LANGUAGES IS
330 SELECT LANGUAGE_CODE
331 FROM FND_LANGUAGES
332 WHERE INSTALLED_FLAG='I'
333 or INSTALLED_FLAG='B';
334
335
336
337 l_pk_column_index NUMBER;
338 l_lang_code C_LANGUAGES%rowtype;
339
340 BEGIN
341
342 SELECT CHANGE_B_TABLE_NAME ,
343 CHANGE_TL_TABLE_NAME
344 INTO l_pending_base_tbl,l_pending_tl_tbl
345 from ENG_PENDING_CHANGE_CTX
346 where CHANGE_ATTRIBUTE_GROUP_TYPE= p_attr_group_type
347 AND APPLICATION_ID = p_application_id;
348
349 if (FND_PROFILE.value('FND_DIAGNOSTICS')='Y')
350 THEN
351 OPEN_DEBUG_SESSION( p_output_dir => g_output_dir,
352 p_file_name => g_debug_filename);
353 END IF;
354
355 x_return_status := FND_API.G_RET_STS_SUCCESS;
356
357 --Validate GDSN SINGLE ROW START
358 ---------------------------------
359 IF (p_attr_group_type = 'EGO_ITEM_GTIN_ATTRS' AND p_exec_mode = 'PWB')
360 THEN
361 getValue(p_base_attr_names_values,l_inventory_item_id,'INVENTORY_ITEM_ID');
362 getValue(p_base_attr_names_values,l_organization_id,'ORGANIZATION_ID');
363 VALIDATE_GDSN_RECORDS(p_inventory_item_id => l_inventory_item_id
364 ,p_organization_id => l_organization_id
365 ,p_attr_group_type => p_attr_group_type
366 ,p_attr_name_value_pairs => p_base_attr_names_values
367 ,p_tl_attr_names_values => p_tl_attr_names_values
368 ,x_return_status => x_return_status
369 ,x_msg_count => x_msg_count
370 ,x_msg_data => x_msg_data
371 );
372 END IF;
373
374 ---------------------------------
375 --Validate GDSN SINGLE ROW END
376 ---------------------------------
377
378 IF x_return_status = FND_API.G_RET_STS_SUCCESS
379 THEN
380
381 FOR i IN p_base_attr_names_values.FIRST .. p_base_attr_names_values.LAST
382 LOOP
383 IF (l_attr_name_dml is null)
384 THEN
385 l_attr_name_dml := p_base_attr_names_values(i).ATTR_NAME;
386 ELSE
387 l_attr_name_dml := l_attr_name_dml ||','|| p_base_attr_names_values(i).ATTR_NAME;
388 END IF;
389 if p_base_attr_names_values(i).ATTR_VALUE_NUM is not NULL AND
390 p_base_attr_names_values(i).ATTR_UNIT_OF_MEASURE IS NOT NULL
391 THEN
392 l_attr_name_dml := l_attr_name_dml ||','|| 'UOM_'|| p_base_attr_names_values(i).ATTR_NAME ;
393 END IF;
394 IF (l_attr_value_dml is NULL)
395 THEN
396 l_attr_value_dml := ' ';
397 ELSE
398 l_attr_value_dml := l_attr_value_dml || ',';
399 END IF;
400
401 if p_base_attr_names_values(i).ATTR_NAME = 'EXTENSION_ID'
402 THEN
403 l_extension_id := p_base_attr_names_values(i).ATTR_VALUE_NUM;
404
405
406 if (( p_attr_group_type = 'EGO_ITEM_GTIN_ATTRS'
407 OR p_attr_group_type = 'EGO_ITEM_GTIN_MULTI_ATTRS' )
408 AND (l_extension_id is null OR l_extension_id=-1000))
409 THEN
410 SELECT EGO_EXTFWK_S.NEXTVAL
411 into l_extension_id
412 FROM dual;
413 END IF;
414 l_attr_value_dml := l_attr_value_dml|| l_extension_id;
415 ELSE
416 IF (p_base_attr_names_values(i).ATTR_VALUE_STR is not NULL)
417 THEN
418 l_attr_value_dml := l_attr_value_dml||'''' ||p_base_attr_names_values(i).ATTR_VALUE_STR||'''';
419 ELSIF (p_base_attr_names_values(i).ATTR_VALUE_NUM is not NULL)
420 THEN
421 l_attr_value_dml := l_attr_value_dml|| p_base_attr_names_values(i).ATTR_VALUE_NUM;
422 if p_base_attr_names_values(i).ATTR_UNIT_OF_MEASURE IS NOT NULL
423 THEN
424 l_attr_value_dml := l_attr_value_dml || ',''' ||p_base_attr_names_values(i).ATTR_UNIT_OF_MEASURE || '''';
425 END IF;
426 ELSIF (p_base_attr_names_values(i).ATTR_VALUE_DATE is not NULL)
427 THEN
428 l_attr_value_dml := l_attr_value_dml|| ''''||p_base_attr_names_values(i).ATTR_VALUE_DATE||'''';
429 END IF;
430 END IF;
431 IF g_debug_flag THEN
432 Write_Debug('Insert base name DML : '||l_attr_name_dml);
433 Write_Debug('Insert base value DML : '||l_attr_value_dml);
434 END IF;
435
436
437 END LOOP;
438
439 l_attr_base_dml := 'INSERT INTO '|| l_pending_base_tbl || '('||
440 l_attr_name_dml || ') VALUES ( ' || l_attr_value_dml || ')';
441 IF g_debug_flag THEN
442 Write_Debug('Insert base DML : '||l_attr_base_dml);
443 END IF;
444 EXECUTE IMMEDIATE l_attr_base_dml;
445
446
447 l_attr_value_dml :=NULL;
448 l_attr_name_dml := NULL;
449 FOR i IN p_tl_attr_names_values.FIRST .. p_tl_attr_names_values.LAST
450 LOOP
451 IF (l_attr_name_dml is NULL)
452 THEN
453 l_attr_name_dml := p_tl_attr_names_values(i).ATTR_NAME;
454 ELSE
455 l_attr_name_dml := l_attr_name_dml ||','|| p_tl_attr_names_values(i).ATTR_NAME;
456 END IF;
457
458 IF (l_attr_value_dml is NULL)
459 THEN
460 l_attr_value_dml := ' ';
461 ELSE
462 l_attr_value_dml := l_attr_value_dml || ',';
463 END IF;
464
465 if p_tl_attr_names_values(i).ATTR_NAME = 'EXTENSION_ID'
466 THEN
467 l_attr_value_dml := l_attr_value_dml|| l_extension_id;
468 ELSE
469
470 IF (p_tl_attr_names_values(i).ATTR_VALUE_STR is not NULL)
471 THEN
472 l_attr_value_dml := l_attr_value_dml||'''' || p_tl_attr_names_values(i).ATTR_VALUE_STR||'''';
473 ELSIF (p_tl_attr_names_values(i).ATTR_VALUE_NUM is not NULL)
474 THEN
475 l_attr_value_dml := l_attr_value_dml|| p_tl_attr_names_values(i).ATTR_VALUE_NUM;
476 ELSIF (p_tl_attr_names_values(i).ATTR_VALUE_DATE is not NULL)
477 THEN
478 l_attr_value_dml := l_attr_value_dml|| '''' ||p_tl_attr_names_values(i).ATTR_VALUE_DATE||'''';
479 END IF;
480 END IF;
481
482 IF g_debug_flag THEN
483 Write_Debug('Insert tl name DML : '||l_attr_name_dml);
484 Write_Debug('Insert tl value DML : '||l_attr_value_dml);
485 END IF;
486
487 END LOOP;
488 l_attr_name_dml := l_attr_name_dml ||',SOURCE_LANG'||',LANGUAGE';
489 l_attr_value_dml := l_attr_value_dml ||',USERENV(''LANG'')';
490 l_temp_tl_dml := l_attr_value_dml;
491 FOR l_lang_code IN C_LANGUAGES
492 LOOP
493 l_attr_value_dml := l_temp_tl_dml;
494 l_attr_value_dml := l_attr_value_dml ||','''|| l_lang_code.LANGUAGE_CODE||'''';
495 l_attr_tl_dml := 'INSERT INTO '|| l_pending_tl_tbl || '('||l_attr_name_dml || ' ) VALUES ( ' || l_attr_value_dml ||')';
496 IF g_debug_flag THEN
497 Write_Debug('Insert base DML : '||l_attr_tl_dml);
498 END IF;
499
500 EXECUTE IMMEDIATE l_attr_tl_dml;
501 END LOOP;
502 END IF;
503
504 IF g_debug_flag THEN
505 Write_Debug('Closing debug session '
506 || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
507 Close_Debug_Session ;
508 END IF ;
509
510
511
512 END INSERT_ITEM_ATTRS;
513
514 PROCEDURE UPDATE_ITEM_ATTRS
515 ( p_api_version IN NUMBER
516 ,p_object_name IN VARCHAR2
517 ,p_application_id IN NUMBER
518 ,p_attr_group_type IN VARCHAR2
519 ,p_base_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
520 ,p_tl_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
521 ,p_pk_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
522 ,x_return_status OUT NOCOPY VARCHAR2
523 ,x_errorcode OUT NOCOPY NUMBER
524 ,x_msg_count OUT NOCOPY NUMBER
525 ,x_msg_data OUT NOCOPY VARCHAR2
526 ,p_exec_mode IN VARCHAR2
527 )
528 IS
529 l_attr_b_update_dml VARCHAR2(3200);
530 l_attr_tl_update_dml VARCHAR2(3200);
531 l_attr_update_where_dml VARCHAR2(3200);
532 l_pending_base_tbl VARCHAR2(30);
533 l_pending_tl_tbl VARCHAR2(30);
534 l_cursor_id INTEGER := DBMS_SQL.OPEN_CURSOR;
535 l_number_of_rows NUMBER;
536 l_bind_count NUMBER;
537 l_inventory_item_id NUMBER;
538 l_organization_id NUMBER;
539
540
541 BEGIN
542
543 SELECT CHANGE_B_TABLE_NAME , CHANGE_TL_TABLE_NAME
544 INTO l_pending_base_tbl, l_pending_tl_tbl
545 from ENG_PENDING_CHANGE_CTX
546 where CHANGE_ATTRIBUTE_GROUP_TYPE= p_attr_group_type
547 AND APPLICATION_ID = p_application_id;
548
549 if (FND_PROFILE.value('FND_DIAGNOSTICS')='Y')
550 THEN
551 OPEN_DEBUG_SESSION( p_output_dir => g_output_dir,
552 p_file_name => g_debug_filename);
553 END IF;
554
555 x_return_status := FND_API.G_RET_STS_SUCCESS;
556 ---------------------------------
557 --Validate GDSN SINGLE ROW START
558 ---------------------------------
559 IF (p_attr_group_type = 'EGO_ITEM_GTIN_ATTRS' AND p_exec_mode = 'PWB')
560 THEN
561 getValue(p_base_attr_names_values,l_inventory_item_id,'INVENTORY_ITEM_ID');
562 getValue(p_base_attr_names_values,l_organization_id,'ORGANIZATION_ID');
563 VALIDATE_GDSN_RECORDS(p_inventory_item_id => l_inventory_item_id
564 ,p_organization_id => l_organization_id
565 ,p_attr_group_type => p_attr_group_type
566 ,p_attr_name_value_pairs => p_base_attr_names_values
567 ,p_tl_attr_names_values => p_tl_attr_names_values
568 ,x_return_status => x_return_status
569 ,x_msg_count => x_msg_count
570 ,x_msg_data => x_msg_data
571 );
572 END IF;
573 ---------------------------------
574 --Validate GDSN SINGLE ROW END
575 ---------------------------------
576 IF x_return_status = FND_API.G_RET_STS_SUCCESS
577 THEN
578
579 IF p_pk_attr_names_values IS NOT NULL
580 THEN
581 FOR i IN p_pk_attr_names_values.FIRST .. p_pk_attr_names_values.LAST
582 LOOP
583
584 IF (l_attr_update_where_dml IS NULL)
585 THEN
586 l_attr_update_where_dml := p_pk_attr_names_values(i).ATTR_NAME;
587 ELSE
588 l_attr_update_where_dml := l_attr_update_where_dml ||' AND '|| p_pk_attr_names_values(i).ATTR_NAME;
589 END IF ;
590
591 if (p_pk_attr_names_values(i).ATTR_VALUE_STR is not NULL)
592 THEN
593 l_attr_update_where_dml := l_attr_update_where_dml || ' = ''' || p_pk_attr_names_values(i).ATTR_VALUE_STR || '''';
594 ELSIF (p_pk_attr_names_values(i).ATTR_VALUE_NUM is not NULL)
595 THEN
596 l_attr_update_where_dml := l_attr_update_where_dml || ' = ' || p_pk_attr_names_values(i).ATTR_VALUE_NUM;
597 ELSIF (p_pk_attr_names_values(i).ATTR_VALUE_DATE is not NULL)
598 THEN
599 l_attr_update_where_dml := l_attr_update_where_dml || ' = '|| p_pk_attr_names_values(i).ATTR_VALUE_DATE;
600 END IF;
601
602
603 END LOOP;
604 END IF;
605
606 IF p_base_attr_names_values IS NOT NULL
607 THEN
608 l_bind_count := 0;
609 FOR i IN p_base_attr_names_values.FIRST .. p_base_attr_names_values.LAST
610 LOOP
611 IF (l_attr_b_update_dml IS NULL)
612 THEN
613 l_attr_b_update_dml := p_base_attr_names_values(i).ATTR_NAME;
614 ELSE
615 l_attr_b_update_dml := l_attr_b_update_dml ||' , '|| p_base_attr_names_values(i).ATTR_NAME;
616 END IF;
617 l_bind_count := l_bind_count +1;
618 l_attr_b_update_dml := l_attr_b_update_dml ||' = :FND_BIND'||l_bind_count ;
619 IF p_base_attr_names_values(i).ATTR_VALUE_NUM IS NOT NULL AND
620 p_base_attr_names_values(i).ATTR_UNIT_OF_MEASURE IS NOT NULL
621 THEN
622 l_bind_count := l_bind_count +1;
623 l_attr_b_update_dml := l_attr_b_update_dml ||' , '|| 'UOM_'|| p_base_attr_names_values(i).ATTR_NAME;
624 l_attr_b_update_dml := l_attr_b_update_dml ||' = :FND_BIND'||l_bind_count ;
625 END IF;
626
627 END LOOP;
628
629 l_attr_b_update_dml := 'UPDATE '|| l_pending_base_tbl || ' SET '|| l_attr_b_update_dml || ' WHERE '|| l_attr_update_where_dml;
630
631 DBMS_SQL.Parse(l_cursor_id, l_attr_b_update_dml, DBMS_SQL.Native);
632
633 l_bind_count :=0;
634 FOR i IN p_base_attr_names_values.FIRST .. p_base_attr_names_values.LAST
635 LOOP
636 l_bind_count := l_bind_count +1;
637 if (p_base_attr_names_values(i).ATTR_VALUE_STR is not NULL)
638 THEN
639 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_count ,p_base_attr_names_values(i).ATTR_VALUE_STR);
640 ELSIF (p_base_attr_names_values(i).ATTR_VALUE_NUM is not NULL)
641 THEN
642 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_count ,p_base_attr_names_values(i).ATTR_VALUE_NUM);
643 if p_base_attr_names_values(i).ATTR_UNIT_OF_MEASURE IS NOT NULL
644 THEN
645 l_bind_count := l_bind_count +1;
646 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_count ,p_base_attr_names_values(i).ATTR_UNIT_OF_MEASURE );
647 END IF;
648
649 ELSIF (p_base_attr_names_values(i).ATTR_VALUE_DATE is not NULL)
650 THEN
651 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_count ,p_base_attr_names_values(i).ATTR_VALUE_DATE);
652 ELSE
653 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_count ,to_char(NULL));
654 END IF;
655 END LOOP;
656 IF l_attr_b_update_dml IS NOT NULL
657 THEN
658 -- l_attr_b_update_dml := 'UPDATE '|| l_pending_base_tbl || ' SET '|| l_attr_b_update_dml || ' WHERE '|| l_attr_update_where_dml;
659
660 IF g_debug_flag THEN
661 Write_Debug('UPDATE base DML : '|| l_attr_b_update_dml);
662 END IF;
663
664 l_number_of_rows := DBMS_SQL.Execute(l_cursor_id);
665 END IF;
666 END IF;
667
668 IF p_base_attr_names_values IS NOT NULL
669 THEN
670 DBMS_SQL.Close_Cursor(l_cursor_id);
671
672 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
673 FOR i IN p_tl_attr_names_values.FIRST .. p_tl_attr_names_values.LAST
674 LOOP
675 IF (l_attr_tl_update_dml IS NULL)
676 THEN
677 l_attr_tl_update_dml := p_tl_attr_names_values(i).ATTR_NAME;
678 ELSE
679 l_attr_tl_update_dml := l_attr_tl_update_dml ||' , '|| p_tl_attr_names_values(i).ATTR_NAME;
680 END IF;
681 l_attr_tl_update_dml := l_attr_tl_update_dml ||'= :FND_BIND'||i;
682 END LOOP;
683 IF l_attr_tl_update_dml IS NOT NULL
684 THEN
685 l_attr_update_where_dml := l_attr_update_where_dml ||' AND LANGUAGE = USERENV(''LANG'')' ;
686 l_attr_tl_update_dml := 'UPDATE '|| l_pending_tl_tbl || ' SET '|| l_attr_tl_update_dml || ' WHERE '|| l_attr_update_where_dml;
687
688 DBMS_SQL.Parse(l_cursor_id, l_attr_tl_update_dml, DBMS_SQL.Native);
689 END IF;
690
691 FOR i IN p_tl_attr_names_values.FIRST .. p_tl_attr_names_values.LAST
692 LOOP
693 IF (p_tl_attr_names_values(i).ATTR_VALUE_STR is not NULL)
694 THEN
695 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||i,p_tl_attr_names_values(i).ATTR_VALUE_STR);
696 ELSIF (p_tl_attr_names_values(i).ATTR_VALUE_NUM is not NULL)
697 THEN
698 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||i,p_tl_attr_names_values(i).ATTR_VALUE_NUM);
699 ELSIF (p_tl_attr_names_values(i).ATTR_VALUE_DATE is not NULL)
700 THEN
701 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||i,p_tl_attr_names_values(i).ATTR_VALUE_DATE);
702 ELSE
703 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||i,to_CHAR(NULL));
704 END IF;
705 END LOOP;
706 IF g_debug_flag THEN
707 Write_Debug('UPDATE base DML : '|| l_attr_tl_update_dml);
708 END IF;
709
710 l_number_of_rows := DBMS_SQL.Execute(l_cursor_id);
711 --begin add for bug 9127677
712 IF DBMS_SQL.IS_OPEN(l_cursor_id) then
713 DBMS_SQL.Close_Cursor(l_cursor_id);
714 END IF;
715 --end add for bug 9127677
716
717 END IF;
718 END IF;
719 IF g_debug_flag THEN
720 Write_Debug('Closing Debug Session: '
721 || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
722 Close_Debug_Session ;
723 END IF;
724 END UPDATE_ITEM_ATTRS;
725
726 PROCEDURE VALIDATE_USER_ATTRS
727 (
728 p_api_version IN NUMBER
729 ,p_object_name IN VARCHAR2
730 ,p_attr_group_id IN NUMBER
731 ,p_attr_group_type IN VARCHAR2
732 ,p_application_id IN NUMBER
733 ,p_attr_group_name IN VARCHAR2
734 ,p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE
735 ,p_extension_id IN NUMBER
736 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
737 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
738 ,p_extra_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
739 ,p_extra_attr_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
740 ,p_alternate_ext_b_table_name IN VARCHAR2 DEFAULT NULL
741 ,p_alternate_ext_tl_table_name IN VARCHAR2 DEFAULT NULL
742 ,p_alternate_ext_vl_name IN VARCHAR2 DEFAULT NULL
743 ,p_user_privileges_on_object IN EGO_VARCHAR_TBL_TYPE DEFAULT NULL
744 ,p_row_identifier IN NUMBER DEFAULT NULL
745 ,p_validate_only IN VARCHAR2
746 ,p_mode IN VARCHAR2
747 ,p_acd_type IN VARCHAR2
748 ,p_init_fnd_msg_list IN VARCHAR2
749 ,p_add_errors_to_fnd_stack IN VARCHAR2
750 ,x_return_status OUT NOCOPY VARCHAR2
751 ,x_errorcode OUT NOCOPY NUMBER
752 ,x_msg_count OUT NOCOPY NUMBER
753 ,x_msg_data OUT NOCOPY VARCHAR2
754 ,p_key_attr_upd IN VARCHAR2
755 ,p_data_level_name IN VARCHAR2
756 ,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
757
758 )
759 IS
760 l_failed_row_id_list VARCHAR2(3200);
761 l_exist_extension_id NUMBER;
762 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
763 l_row_identifier NUMBER ;
764 l_curr_ag_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
765 l_attr_meta_data EGO_ATTR_METADATA_TABLE;
766 l_attr_data_table EGO_USER_ATTR_DATA_TABLE;
767 l_mode VARCHAR2(10);
768 l_extra_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
769 l_delete_index NUMBER;
770 l_attr_db_col VARCHAR2(30);
771 l_retrieved_value_char VARCHAR2(4000);
772 l_retrieved_value_num NUMBER;
773 l_retrieved_value_date DATE;
774 l_bind_index NUMBER;
775 l_bind_values EGO_USER_ATTR_DATA_TABLE;
776 l_dynamic_sql VARCHAR2(20000);
777 l_column_count NUMBER;
778 l_uk_where_clause VARCHAR2(4000):= null;
779 l_cursor_id NUMBER;
780 l_desc_table DBMS_SQL.Desc_Tab;
781 l_dummy NUMBER;
782 L_CACHED_SQL_FOUND VARCHAR2(1) := 'N';
783 p_prod_vl_name VARCHAR2(40);
784 l_DataLevelColumnExists VARCHAR2(5);
785 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
786 CURSOR C_DATALEVEL_COLUMN_EXISTS(TABLENAME IN VARCHAR2) IS
787 SELECT 'Y' FROM FND_TABLES FT,FND_COLUMNS FC
788 WHERE FT.TABLE_NAME = UPPER(TABLENAME) AND FT.TABLE_ID = FC.TABLE_ID
789 AND COLUMN_NAME = 'DATA_LEVEL_ID';
790
791 BEGIN
792
793
794 x_return_status := FND_API.G_RET_STS_SUCCESS ;
795 -- get the Production vl name.
796 BEGIN
797 SELECT FLEX_EXT.APPLICATION_VL_NAME
798 INTO p_prod_vl_name
799 FROM FND_DESCRIPTIVE_FLEXS FLEX,
800 EGO_FND_DESC_FLEXS_EXT FLEX_EXT
801 WHERE FLEX.APPLICATION_ID = FLEX_EXT.APPLICATION_ID(+)
802 AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = FLEX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+)
803 AND FLEX.APPLICATION_ID = p_application_id
804 AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type ;
805 END ;
806
807 l_DataLevelColumnExists := 'N';
808 OPEN C_DATALEVEL_COLUMN_EXISTS(p_alternate_ext_b_table_name);
809 FETCH C_DATALEVEL_COLUMN_EXISTS into l_DataLevelColumnExists;
810 --begin add for bug 9127677
811 CLOSE C_DATALEVEL_COLUMN_EXISTS;
812 --end add for bug 9127677
813 --prg_debug('Add datalevel column='||l_DataLevelColumnExists);
814
815 IF p_row_identifier IS NULL
816 THEN
817 -- Set Default as 1
818 l_row_identifier := 1 ;
819 ELSE
820 l_row_identifier := p_row_identifier ;
821 END IF ;
822
823 l_curr_ag_metadata_obj :=
824 EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(P_ATTR_GROUP_ID => P_ATTR_GROUP_ID
825 ,P_APPLICATION_ID => p_application_id
826 ,P_ATTR_GROUP_TYPE => p_attr_group_type
827 ,P_ATTR_GROUP_NAME => p_attr_group_name);
828
829 -- Uniqueness check required for the multi row attr groups.
830
831 if l_curr_ag_metadata_obj.MULTI_ROW_CODE = 'Y'
832 THEN
833 l_attr_meta_data := l_curr_ag_metadata_obj.ATTR_METADATA_TABLE;
834
835 -- Get cached sql for the attribute group.
836 IF G_CACHED_PLSQL_TABLE.EXISTS(P_ATTR_GROUP_ID)
837
838 THEN
839 l_dynamic_sql := G_CACHED_PLSQL_TABLE(P_ATTR_GROUP_ID).attr_group_sql;
840 L_CACHED_SQL_FOUND := 'Y';
841 END IF;
842
843
844 /* if cached sql does not exist then create sql query using
845 a merged record created using production and pending table
846 having the key attibutes in select and where condition
847 with a join using EXTENSION_ID AND PRIMARY KEYS AND a
848 condition to get rows other than the current EXTENSION_ID
849
850
851 */
852 if L_CACHED_SQL_FOUND <>'Y' THEN
853 l_dynamic_sql := 'SELECT ';
854 END IF;
855 for i in l_attr_meta_data.first .. l_attr_meta_data.last
856 LOOP
857
858 if l_attr_meta_data(i).UNIQUE_KEY_FLAG = 'Y' AND L_CACHED_SQL_FOUND <> 'Y'
859 THEN
860
861 if l_dynamic_sql <> 'SELECT ' THEN
862 l_dynamic_sql := l_dynamic_sql ||', ';
863 END IF;
864
865 l_attr_db_col := l_attr_meta_data(i).DATABASE_COLUMN;
866
867 l_dynamic_sql := l_dynamic_sql || ' DECODE(PEND.'||l_attr_db_col ||
868 ',null, PROD.'||l_attr_db_col ||
869 ',DECODE(PEND.'||l_attr_db_col||
870 ',';
871 IF l_attr_meta_data(i).DATA_TYPE_CODE ='A' OR l_attr_meta_data(i).DATA_TYPE_CODE = 'C'
872 THEN
873 l_dynamic_sql := l_dynamic_sql ||'''' || ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_CHAR ||'''';
874
875 ELSIF l_attr_meta_data(i).DATA_TYPE_CODE = 'N'
876 THEN
877 l_dynamic_sql := l_dynamic_sql || TO_CHAR(ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_NUM);
878
879 ELSIF l_attr_meta_data(i).DATA_TYPE_CODE = 'X' OR l_attr_meta_data(i).DATA_TYPE_CODE = 'Y'
880 THEN
881 l_dynamic_sql := l_dynamic_sql || ' ''' || TO_CHAR(ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_DATE) || '''';
882 END IF;
883
884 l_dynamic_sql := l_dynamic_sql || ', NULL , PEND.'|| l_attr_db_col || ')) '|| l_attr_db_col;
885
886 END IF;
887
888
889 if i = l_attr_meta_data.last
890
891 THEN
892 l_bind_index := 1;
893 l_bind_values := EGO_USER_ATTR_DATA_TABLE();
894 IF L_CACHED_SQL_FOUND <> 'Y' THEN
895 IF l_dynamic_sql = 'SELECT ' THEN
896 l_dynamic_sql := l_dynamic_sql || ' PEND.EXTENSION_ID ';
897 ELSE
898 l_dynamic_sql := l_dynamic_sql || ', PEND.EXTENSION_ID ';
899 END IF;
900 l_dynamic_sql := l_dynamic_sql || ' FROM '|| p_alternate_ext_vl_name ||' PEND , '|| p_prod_vl_name||' PROD';
901 IF l_DataLevelColumnExists = 'Y' THEN l_dynamic_sql := l_dynamic_sql || ' , ego_data_level_b DATA_LEVELS ';
902 END IF;
903 l_dynamic_sql := l_dynamic_sql ||' WHERE PEND.EXTENSION_ID = PROD.EXTENSION_ID '||
904 ' AND PEND.EXTENSION_ID <> :1' ;
905 END IF;
906 l_bind_values.extend();
907 l_bind_values(l_bind_values.LAST) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
908 ,null
909 ,null
910 ,p_extension_id
911 ,null
912 ,null
913 ,null
914 ,1);
915 for pk_index in p_pk_column_name_value_pairs.FIRST .. p_pk_column_name_value_pairs.LAST
916 LOOP
917 l_bind_index := l_bind_index +1;
918 IF L_CACHED_SQL_FOUND <> 'Y' THEN
919 l_dynamic_sql := l_dynamic_sql || ' AND PEND.'||
920 p_pk_column_name_value_pairs(pk_index).NAME ||' = :' ||l_bind_index ;
921 END IF;
922 l_bind_values.extend();
923 l_bind_values(l_bind_values.LAST) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
924 ,null
925 ,null
926 ,to_number(p_pk_column_name_value_pairs(pk_index).VALUE)
927 ,null
928 ,null
929 ,null
930 ,1);
931
932 END LOOP;
933 IF (l_DataLevelColumnExists = 'Y') THEN
934 if p_data_level_name is not null
935 then
936 l_bind_index := l_bind_index +1;
937 IF L_CACHED_SQL_FOUND <> 'Y' then
938 l_dynamic_sql := l_dynamic_sql || ' AND PEND.DATA_LEVEL_ID = DATA_LEVELS.DATA_LEVEL_ID'
939 ||' AND DATA_LEVELS.APPLICATION_ID = ' || P_APPLICATION_ID
940 ||' AND DATA_LEVELS.ATTR_GROUP_TYPE = '''||p_attr_group_type||''''
941 ||' AND DATA_LEVELS.DATA_LEVEL_NAME = :' ||l_bind_index ;
942 END IF;
943 l_bind_values.extend();
944 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
945 ,null
946 ,P_DATA_LEVEL_NAME
947 ,NULL
948 ,null
949 ,null
950 ,null
951 ,-1);
952
953 end if ; -- p_data_level_name is not null;
954 END IF; -- l_DataLevelColumnExists is 'Y'
955 IF p_extra_pk_col_name_val_pairs is NOT NULL AND p_extra_pk_col_name_val_pairs.COUNT>0
956 THEN
957 FOR pk_extra_index in p_extra_pk_col_name_val_pairs.FIRST .. p_extra_pk_col_name_val_pairs.LAST
958 LOOP
959 IF p_extra_pk_col_name_val_pairs(pk_extra_index).NAME <> 'ACD_TYPE'
960 THEN
961 l_bind_index := l_bind_index +1;
962 IF L_CACHED_SQL_FOUND <> 'Y' THEN
963 l_dynamic_sql := l_dynamic_sql || ' AND PEND.'||
964 p_extra_pk_col_name_val_pairs(pk_extra_index).NAME ||' = :' ||l_bind_index ;
965 END IF;
966 l_bind_values.extend();
967 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
968 ,null
969 ,null
970 ,to_number(p_extra_pk_col_name_val_pairs(pk_extra_index).VALUE)
971 ,null
972 ,null
973 ,null
974 ,-1);
975 END IF;
976 END LOOP;
977 END IF;
978 END IF;
979
980 END LOOP;
981
982 /* The final query would actualy select EXTENSION_ID from the
983 previous query using alais 'dy_sql' and would filter the rows
984 based on the key attr values of the current row being valildated.
985 */
986 l_uk_where_clause := ' 1=1 ';
987 for j in l_attr_meta_data.first .. l_attr_meta_data.last
988 LOOP
989 if l_attr_meta_data(j).UNIQUE_KEY_FLAG = 'Y'
990 THEN
991
992 IF l_uk_where_clause IS NOT NULL AND L_CACHED_SQL_FOUND <> 'Y'
993 THEN
994 l_uk_where_clause := l_uk_where_clause || ' AND ' ;
995 ELSIF l_uk_where_clause IS NULL AND L_CACHED_SQL_FOUND <> 'Y' THEN
996 l_uk_where_clause := ' WHERE ';
997 END IF;
998
999
1000 FOR p_attr_index in p_attributes_data_table.FIRST .. p_attributes_data_table.LAST
1001 LOOP
1002 IF p_attributes_data_table(p_attr_index).ATTR_NAME = l_attr_meta_data(j).ATTR_NAME
1003 AND (l_attr_meta_data(j).DATA_TYPE_CODE ='A' OR l_attr_meta_data(j).DATA_TYPE_CODE = 'C')
1004 THEN
1005 --if p_attributes_data_table(p_attr_index).ATTR_VALUE_STR IS NOT NULL
1006 --THEN
1007 l_bind_index := l_bind_index +1;
1008 l_bind_values.extend();
1009 IF L_CACHED_SQL_FOUND <> 'Y' THEN
1010 l_uk_where_clause := l_uk_where_clause || 'NVL ( PEND.'||l_attr_meta_data(j).DATABASE_COLUMN ||',''-1'') = NVL(:'|| l_bind_index||',''-1'')';
1011 END IF;
1012 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
1013 ,null
1014 ,p_attributes_data_table(p_attr_index).ATTR_VALUE_STR
1015 ,NULL
1016 ,null
1017 ,null
1018 ,null
1019 ,-1);
1020 -- ELSE
1021 -- l_uk_where_clause := l_uk_where_clause || l_attr_meta_data(j).DATABASE_COLUMN ||' IS NULL';
1022 -- END IF;
1023 EXIT;
1024 ELSIF p_attributes_data_table(p_attr_index).ATTR_NAME = l_attr_meta_data(j).ATTR_NAME
1025 AND l_attr_meta_data(j).DATA_TYPE_CODE = 'N'
1026 THEN
1027 --if p_attributes_data_table(p_attr_index).ATTR_VALUE_NUM IS NOT NULL
1028 -- THEN
1029 l_bind_index := l_bind_index +1;
1030 l_bind_values.extend();
1031 IF L_CACHED_SQL_FOUND <> 'Y' THEN
1032 l_uk_where_clause := l_uk_where_clause || ' NVL(PEND.'|| l_attr_meta_data(j).DATABASE_COLUMN || ',-1) = NVL(:'|| l_bind_index || ', -1)';
1033 END IF;
1034 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
1035 ,null
1036 ,null
1037 ,p_attributes_data_table(p_attr_index).ATTR_VALUE_NUM
1038 ,null
1039 ,null
1040 ,null
1041 ,-1);
1042 --ELSE
1043 -- l_uk_where_clause := l_uk_where_clause || ' ' || l_attr_meta_data(j).DATABASE_COLUMN ||' IS NULL';
1044 --END IF;
1045 EXIT;
1046 ELSIF p_attributes_data_table(p_attr_index).ATTR_NAME = l_attr_meta_data(j).ATTR_NAME
1047 AND (l_attr_meta_data(j).DATA_TYPE_CODE ='X' OR l_attr_meta_data(j).DATA_TYPE_CODE = 'Y')
1048 THEN
1049 -- if p_attributes_data_table(p_attr_index).ATTR_VALUE_DATE IS NOT NULL
1050 -- THEN
1051 l_bind_index := l_bind_index +1;
1052 l_bind_values.extend();
1053 IF L_CACHED_SQL_FOUND <> 'Y' THEN
1054 l_uk_where_clause := l_uk_where_clause || ' NVL(PEND.' || l_attr_meta_data(j).DATABASE_COLUMN || ',to_date(''1'',''j'')) = NVL(:'|| l_bind_index ||',to_date(''1'',''j''))';
1055 END IF;
1056 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
1057 ,null
1058 ,null
1059 ,NULL
1060 ,p_attributes_data_table(p_attr_index).ATTR_VALUE_DATE
1061 ,null
1062 ,null
1063 ,-1);
1064 -- ELSE
1065 -- l_uk_where_clause := l_uk_where_clause || ' ' || l_attr_meta_data(j).DATABASE_COLUMN ||' IS NULL';
1066 --END IF;
1067 EXIT;
1068 END IF;
1069 END LOOP;
1070 END IF;
1071 END LOOP;
1072 l_bind_index := l_bind_index + 1;
1073 IF L_CACHED_SQL_FOUND <> 'Y' THEN
1074
1075 l_dynamic_sql := l_dynamic_sql || ' AND '|| l_uk_where_clause;
1076
1077 --Start Changes, Bug 8977714
1078 --Add attr_group_id
1079 l_dynamic_sql := l_dynamic_sql || ' AND PEND.ATTR_GROUP_ID = :' ||l_bind_index;
1080 --End Changes, Bug 8977714
1081
1082 G_CACHED_PLSQL_TABLE(P_ATTR_GROUP_ID).ATTR_GROUP_ID := P_ATTR_GROUP_ID;
1083 G_CACHED_PLSQL_TABLE(P_ATTR_GROUP_ID).ATTR_GROUP_SQL := l_dynamic_sql;
1084 END IF;
1085
1086 -- For bug 9387014, put bind variable out from condition L_CACHED_SQL_FOUND <> 'Y'
1087 l_bind_values.extend();
1088 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
1089 ,null
1090 ,null
1091 ,P_ATTR_GROUP_ID
1092 ,null
1093 ,null
1094 ,null
1095 ,-1);
1096
1097 IF p_data_level_name_value_pairs is NOT NULL AND p_data_level_name_value_pairs.COUNT>0
1098 THEN
1099 for data_index in p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
1100 LOOP
1101 l_bind_index := l_bind_index +1;
1102 l_dynamic_sql := l_dynamic_sql || ' AND PEND.'||
1103 p_data_level_name_value_pairs(data_index).NAME ||' = :' ||l_bind_index ;
1104 l_bind_values.extend();
1105 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
1106 ,null
1107 ,null
1108 ,to_number(p_data_level_name_value_pairs(data_index).VALUE)
1109 ,null
1110 ,null
1111 ,null
1112 ,-1);
1113
1114 END LOOP;
1115 END IF;
1116
1117
1118 l_cursor_id := DBMS_SQL.Open_Cursor;
1119 -- prg_debug(l_dynamic_sql);
1120 -- prg_debug(l_bind_index);
1121 DBMS_SQL.Parse(l_cursor_id, l_dynamic_sql, DBMS_SQL.Native);
1122 DBMS_SQL.Describe_Columns(l_cursor_id, l_column_count, l_desc_table);
1123 FOR i IN 1 .. l_column_count
1124 LOOP
1125
1126 DBMS_SQL.Define_Column(l_cursor_id, i, l_retrieved_value_char, 1000);
1127
1128 END LOOP;
1129
1130 FOR l_bind_index IN l_bind_values.FIRST .. l_bind_values.LAST
1131 LOOP
1132 IF ( l_bind_values(l_bind_index).ATTR_VALUE_STR is not NULL)
1133 THEN
1134 -- prg_debug(l_bind_values(l_bind_index).ATTR_VALUE_STR);
1135 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':'||l_bind_index,l_bind_values(l_bind_index).ATTR_VALUE_STR);
1136 ELSIF (l_bind_values(l_bind_index).ATTR_VALUE_NUM is not NULL)
1137 THEN
1138 -- prg_debug(l_bind_values(l_bind_index).ATTR_VALUE_NUM);
1139 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':'||l_bind_index,l_bind_values(l_bind_index).ATTR_VALUE_NUM);
1140 ELSIF (l_bind_values(l_bind_index).ATTR_VALUE_DATE is not NULL)
1141 THEN
1142 --prg_debug(l_bind_values(l_bind_index).ATTR_VALUE_DATE);
1143 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':'||l_bind_index,l_bind_values(l_bind_index).ATTR_VALUE_DATE);
1144 ELSE
1145 -- prg_debug(l_bind_values(l_bind_index).ATTR_VALUE_STR);
1146 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':'||l_bind_index,l_bind_values(l_bind_index).ATTR_VALUE_STR);
1147 END IF;
1148
1149 END LOOP;
1150
1151
1152
1153 l_dummy := DBMS_SQL.Execute(l_cursor_id);
1154
1155 if ( DBMS_SQL.Fetch_Rows(l_cursor_id) > 0)
1156 THEN
1157 x_return_status := FND_API.G_RET_STS_ERROR;
1158 ERROR_HANDLER.Initialize();
1159 ERROR_HANDLER.Set_Bo_Identifier(EGO_USER_ATTRS_DATA_PVT.G_BO_IDENTIFIER);
1160 ERROR_HANDLER.Add_Error_Message(
1161 p_message_name => 'EGO_EF_ROW_ALREADY_EXISTS'
1162 ,p_application_id => 'EGO'
1163 ,p_message_type => FND_API.G_RET_STS_ERROR
1164 ,p_addto_fnd_stack => 'Y'
1165 );
1166
1167 x_msg_count := ERROR_HANDLER.Get_Message_Count();
1168 IF (x_msg_count = 1) THEN
1169 DECLARE
1170 message_list ERROR_HANDLER.Error_Tbl_Type;
1171 BEGIN
1172 ERROR_HANDLER.Get_Message_List(message_list);
1173 x_msg_data := message_list(message_list.FIRST).message_text;
1174 END;
1175 ELSE
1176 x_msg_data := NULL;
1177 END IF;
1178
1179 END IF;
1180 --begin add for bug 9127677
1181 IF DBMS_SQL.IS_OPEN(l_cursor_id) then
1182 DBMS_SQL.Close_Cursor(l_cursor_id);
1183 END IF;
1184 --end add for bug 9127677
1185 END IF ; -- attribute group is MULTI ROW.
1186
1187 -- if the row is unique in pending validate it against the production data.
1188
1189 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1190 THEN
1191
1192 IF g_debug_flag THEN
1193 Write_Debug('Start VALIDATE_USER_ATTRS');
1194 END IF ;
1195
1196
1197 /* IF p_data_level_name_value_pairs.FIRST is NOT NULL
1198 THEN*/
1199
1200 IF g_debug_flag THEN
1201 Write_Debug('p_data_level_name_value_pairs.FIRST is NOT NULL');
1202
1203 END IF ;
1204
1205 if p_acd_type = 'ADD'
1206 then
1207 l_mode := 'CREATE';
1208 ELSIF p_acd_type = 'CHANGE' AND p_key_attr_upd = 'Y'
1209 then
1210 l_mode := 'CREATE';
1211 ELSIF p_acd_type = 'CHANGE' AND p_key_attr_upd = 'N'
1212 then
1213 l_mode := 'UPDATE';
1214 END IF;
1215
1216 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE
1217 (
1218 EGO_USER_ATTR_ROW_OBJ( l_row_identifier
1219 , p_attr_group_id
1220 , p_application_id
1221 , p_attr_group_type
1222 , null
1223 , p_data_level_name
1224 , null
1225 , null
1226 , null
1227 , null
1228 , null
1229 ,l_mode)
1230
1231 );
1232
1233 if p_data_level_name_value_pairs is not null
1234 then
1235 for i in p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
1236 LOOP
1237 if i=1
1238 then
1239 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_1 :=
1240 p_data_level_name_value_pairs(i).VALUE ;
1241 elsif i=2
1242 then
1243 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_2 :=
1244 p_data_level_name_value_pairs(i).VALUE;
1245 elsif i=3
1246 then
1247 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_3 :=
1248 p_data_level_name_value_pairs(i).VALUE ;
1249 elsif i=4
1250 then
1251 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_4 :=
1252 p_data_level_name_value_pairs(i).VALUE ;
1253 elsif i=5
1254 then
1255 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_5 :=
1256 p_data_level_name_value_pairs(i).VALUE ;
1257 end if;
1258 end loop;
1259 end if;
1260
1261 /* ELSE
1262
1263 IF g_debug_flag THEN
1264 Write_Debug('p_data_level_name_value_pairs.FIRST is NULL');
1265 END IF ;
1266
1267 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE(
1268 EGO_USER_ATTR_ROW_OBJ( l_row_identifier
1269 , p_attr_group_id
1270 , p_application_id
1271 , p_attr_group_type
1272 , null
1273 , null
1274 , null
1275 , null
1276 ,l_mode)
1277 );
1278 -- END IF;*/
1279
1280 IF g_debug_flag THEN
1281 Write_Debug('calling EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data');
1282 END IF ;
1283
1284 ---------------------------------------------------------------
1285 -- Next, we build our privileges table for the current user; --
1286 -- any error in this helper function will be raised as an --
1287 -- exception, which will prevent us from calling PUAD at all --
1288 ---------------------------------------------------------------
1289 /*l_user_privileges_on_object := Get_User_Attrs_Privs(
1290 p_pk_column_name_value_pairs(1).VALUE,
1291 p_pk_column_name_value_pairs(2).VALUE
1292 );*/
1293 -- moved to java layer as, it needs to be called befoe the value is set in the EO.
1294 -- AS the pages would be rendered read only and the value is set from server side, so no way to change
1295 -- Although the changes from java are also reverted back and same resulted in revrting the comments on this file as well by mistake.
1296
1297 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data (
1298 p_api_version => p_api_version
1299 ,p_object_name => p_object_name
1300 ,p_attributes_row_table => l_attributes_row_table
1301 ,p_attributes_data_table => p_attributes_data_table
1302 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1303 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1304 -- ,p_user_privileges_on_object => l_user_privileges_on_object
1305 ,p_validate_only => p_validate_only
1306 ,p_commit => FND_API.G_FALSE
1307 ,p_init_fnd_msg_list => FND_API.G_TRUE
1308 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
1309 ,x_failed_row_id_list => l_failed_row_id_list
1310 ,x_return_status => x_return_status
1311 ,x_errorcode => x_errorcode
1312 ,x_msg_count => x_msg_count
1313 ,x_msg_data => x_msg_data
1314 );
1315
1316 IF g_debug_flag THEN
1317 Write_Debug('After calling EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data');
1318 Write_Debug('x_return_status: ' || x_return_status);
1319 Write_Debug('x_msg_count: ' || TO_CHAR(x_msg_count));
1320 Write_Debug('x_msg_data: ' || x_msg_data);
1321 END IF ;
1322 END IF;
1323
1324 END VALIDATE_USER_ATTRS;
1325
1326
1327 PROCEDURE INSERT_ITEM_USER_ATTRS
1328 (
1329 p_api_version IN NUMBER
1330 ,p_object_name IN VARCHAR2
1331 ,p_attr_group_id IN NUMBER
1332 ,p_application_id IN NUMBER
1333 ,p_attr_group_type IN VARCHAR2
1334 ,p_attr_group_name IN VARCHAR2
1335 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1336 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1337 ,P_DATA_LEVEL_NAME IN VARCHAR2
1338 ,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
1339 ,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
1340 ,p_mode IN VARCHAR2
1341 ,p_extra_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1342 ,p_extension_id IN NUMBER
1343 ,p_pending_b_table_name IN VARCHAR2
1344 ,p_pending_tl_table_name IN VARCHAR2
1345 ,p_pending_vl_name IN VARCHAR2
1346 ,p_acd_type IN VARCHAR2
1347 ,p_dml_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
1348 ,p_api_caller IN VARCHAR2
1349 ,p_key_attr_upd IN VARCHAR2
1350 ,x_return_status OUT NOCOPY VARCHAR2
1351 ,x_errorcode OUT NOCOPY NUMBER
1352 ,x_msg_count OUT NOCOPY NUMBER
1353 ,x_msg_data OUT NOCOPY VARCHAR2
1354 )
1355 IS
1356
1357 l_b_dml_for_ag VARCHAR2(30000);
1358 l_tl_dml_for_ag VARCHAR2(30000);
1359 l_bind_index NUMBER;
1360 l_bind_value NUMBER;
1361 l_b_bind_count NUMBER;
1362 l_tl_bind_count NUMBER;
1363 l_b_bind_attr_table EGO_USER_ATTR_DATA_TABLE;
1364 l_tl_bind_attr_table EGO_USER_ATTR_DATA_TABLE;
1365
1366 l_pending_base_tbl VARCHAR2(100);
1367 l_pending_tl_tbl VARCHAR2(100);
1368 l_pending_vl VARCHAR2(100);
1369 l_cursor_id INTEGER := DBMS_SQL.OPEN_CURSOR;
1370 l_number_of_rows NUMBER :=0;
1371 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
1372 L_ATTRIBUTES_DATA_TABLE EGO_USER_ATTR_DATA_TABLE;
1373 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1374 l_extension_id NUMBER := NULL;
1375 l_temp_extension_id NUMBER := NULL;
1376 l_row_identifier NUMBER ;
1377
1378 BEGIN
1379 -- IF (FND_PROFILE.value('FND_DIAGNOSTICS')='Y')
1380 -- THEN
1381 -- OPEN_DEBUG_SESSION( p_output_dir => g_output_dir,
1382 -- p_file_name => g_debug_filename);
1383 -- END IF;
1384 IF g_debug_flag THEN
1385 Write_Debug('Start INSERT_ITEM_USER_ATTRS');
1386 Write_Debug('-----------------------------------------' );
1387 END IF ;
1388 Error_Handler.Initialize; --Bug :13925859
1389
1390 SELECT CHANGE_B_TABLE_NAME
1391 , CHANGE_TL_TABLE_NAME
1392 , CHANGE_VL_TABLE_NAME
1393 INTO l_pending_base_tbl
1394 ,l_pending_tl_tbl
1395 ,l_pending_vl
1396 FROM ENG_PENDING_CHANGE_CTX
1397 WHERE CHANGE_ATTRIBUTE_GROUP_TYPE= p_attr_group_type
1398 AND APPLICATION_ID = p_application_id;
1399
1400
1401 x_return_status := FND_API.G_RET_STS_SUCCESS;
1402 l_extension_id := p_extension_id;
1403
1404
1405 IF P_MODE <> 'DELETE' -- AND (P_API_CALLER = G_EXEC_MODE_IMPORT OR P_API_CALLER = 'PWB')
1406 THEN
1407
1408 IF g_debug_flag THEN
1409 Write_Debug('p_extension_id ' || to_char(p_extension_id));
1410 END IF ;
1411
1412 IF (p_extension_id is NULL OR
1413 (P_API_CALLER = G_EXEC_MODE_IMPORT AND p_extension_id < 0) )
1414 AND (p_acd_type='CHANGE' OR p_acd_type='DELETE')
1415 THEN
1416 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE(
1417 EGO_ATTR_GROUP_REQUEST_OBJ(p_attr_group_id
1418 , p_application_id
1419 , p_attr_group_type
1420 , null
1421 , p_data_level_name
1422 , null
1423 , null
1424 , null
1425 , null
1426 , null
1427 , null
1428 ));
1429 if p_data_level_name_value_pairs is not null
1430 then
1431 for i in p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
1432 LOOP
1433 if i=1
1434 then
1435 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_1 :=
1436 p_data_level_name_value_pairs(i).VALUE ;
1437 elsif i=2
1438 then
1439 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_2 :=
1440 p_data_level_name_value_pairs(i).VALUE;
1441 elsif i=3
1442 then
1443 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_3 :=
1444 p_data_level_name_value_pairs(i).VALUE ;
1445 elsif i=4
1446 then
1447 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_4 :=
1448 p_data_level_name_value_pairs(i).VALUE ;
1449 elsif i=5
1450 then
1451 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_5 :=
1452 p_data_level_name_value_pairs(i).VALUE ;
1453 end if;
1454 end loop;
1455 end if;
1456
1457 l_extension_id := EGO_USER_ATTRS_DATA_PVT.Get_Extension_Id (
1458 p_object_name => p_object_name
1459 ,p_attr_group_id => p_attr_group_id
1460 ,p_application_id => p_application_id
1461 ,p_attr_group_type => p_attr_group_type
1462 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1463 ,P_DATA_LEVEL => P_DATA_LEVEL_NAME
1464 ,p_data_level_name_value_pairs => p_data_level_name_value_pairs
1465 ,p_attr_name_value_pairs => p_attr_name_value_pairs);
1466
1467 IF g_debug_flag THEN
1468 Write_Debug('Got new extension id ' || to_char(l_extension_id));
1469 END IF ;
1470
1471 END IF;
1472 L_ATTRIBUTES_DATA_TABLE := p_attr_name_value_pairs;
1473 if p_acd_type='CHANGE'
1474 THEN
1475
1476 FOR i IN L_ATTRIBUTES_DATA_TABLE.FIRST .. L_ATTRIBUTES_DATA_TABLE.LAST
1477 LOOP
1478 L_ATTRIBUTES_DATA_TABLE(i).ROW_IDENTIFIER := l_extension_id ;
1479 END LOOP;
1480 END IF;
1481
1482 IF P_ACD_TYPE<>'DELETE'
1483 THEN
1484 -- l_row_identifier := l_extension_id;
1485 IF p_acd_type = 'CHANGE' AND (P_API_CALLER = G_EXEC_MODE_IMPORT OR P_API_CALLER = 'PWB')
1486 THEN
1487
1488 IF g_debug_flag THEN
1489 Write_Debug('Calling SETUP_IMPL_ATTR_DATA_ROW ');
1490 END IF ;
1491
1492
1493 SETUP_IMPL_ATTR_DATA_ROW
1494 (
1495 p_api_version => p_api_version
1496 ,p_object_name => p_object_name
1497 ,p_attr_group_id => p_attr_group_id
1498 ,p_application_id => p_application_id
1499 ,p_attr_group_type => p_attr_group_type
1500 ,p_attr_group_name => p_attr_group_name
1501 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1502 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1503 ,P_DATA_LEVEL_NAME => P_DATA_LEVEL_NAME
1504 ,p_data_level_name_value_pairs => p_data_level_name_value_pairs
1505 ,p_attr_name_value_pairs => p_attr_name_value_pairs
1506 ,x_setup_attr_data => L_ATTRIBUTES_DATA_TABLE
1507 ,x_return_status => x_return_status
1508 ,x_errorcode => x_errorcode
1509 ,x_msg_count => x_msg_count
1510 ,x_msg_data => x_msg_data
1511 );
1512
1513 IF g_debug_flag THEN
1514 Write_Debug('After Calling SETUP_IMPL_ATTR_DATA_ROW ');
1515 END IF ;
1516
1517 END IF; -- p_acd_type = 'CHANGE'
1518
1519
1520 IF g_debug_flag THEN
1521 Write_Debug('Calling VALIDATE_USER_ATTRS ');
1522 END IF ;
1523
1524 -- Set Row Identier -1000
1525 -- This is passed thr Import in case of below condition
1526 --
1527 /* IF p_mode = 'CREATE'
1528 AND p_acd_type ='ADD'
1529 AND p_api_caller = ENG_CHANGE_ATTR_UTIL.G_EXEC_MODE_IMPORT
1530 THEN
1531
1532 l_row_identifier := -1000 ;
1533
1534 END IF ;*/
1535
1536
1537 VALIDATE_USER_ATTRS
1538 (
1539 p_api_version => p_api_version
1540 ,p_object_name => p_object_name
1541 ,p_attr_group_id => p_attr_group_id
1542 ,p_attr_group_type => p_attr_group_type
1543 ,p_application_id => p_application_id
1544 ,p_attr_group_name => p_attr_group_name
1545 ,p_attributes_data_table => L_ATTRIBUTES_DATA_TABLE
1546 ,p_extension_id => l_extension_id
1547 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1548 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1549 ,P_DATA_LEVEL_NAME => P_DATA_LEVEL_NAME
1550 ,p_data_level_name_value_pairs => p_data_level_name_value_pairs
1551 ,p_extra_pk_col_name_val_pairs => p_extra_pk_col_name_val_pairs
1552 ,p_extra_attr_name_value_pairs => NULL
1553 ,p_alternate_ext_b_table_name => l_pending_base_tbl
1554 ,p_alternate_ext_tl_table_name => l_pending_tl_tbl
1555 ,p_alternate_ext_vl_name => l_pending_vl
1556 ,p_user_privileges_on_object => NULL
1557 ,p_row_identifier => l_extension_id
1558 ,p_validate_only => FND_API.G_TRUE
1559 ,p_mode => p_mode
1560 ,p_acd_type => p_acd_type
1561 ,p_init_fnd_msg_list => FND_API.G_TRUE
1562 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
1563 ,x_return_status => x_return_status
1564 ,x_errorcode => x_errorcode
1565 ,x_msg_count => x_msg_count
1566 ,x_msg_data => x_msg_data
1567 ,p_key_attr_upd => p_key_attr_upd
1568 ) ;
1569
1570 END IF; -- P_ACD_TYPE<>'DELETE'
1571
1572 END IF; -- P_MODE<>'DELETE'
1573
1574
1575 IF g_debug_flag THEN
1576 Write_Debug('After calling VALIDATE_USER_ATTRS');
1577 Write_Debug('x_return_status: ' || x_return_status);
1578 Write_Debug('x_msg_count: ' || TO_CHAR(x_msg_count));
1579 Write_Debug('x_msg_data: ' || x_msg_data);
1580 END IF ;
1581
1582 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1583 THEN
1584
1585 IF g_debug_flag THEN
1586 Write_Debug('Now Generate_DML_For_Row . . . ');
1587 END IF ;
1588
1589 IF x_return_status = FND_API.G_RET_STS_SUCCESS OR x_return_status is NULL
1590 THEN
1591
1592
1593 IF g_debug_flag THEN
1594 Write_Debug('Now Generate_DML_For_Row 2 . . . ');
1595 END IF ;
1596 if(p_mode <> 'DELETE') THEN
1597 FOR i IN L_ATTRIBUTES_DATA_TABLE.FIRST .. L_ATTRIBUTES_DATA_TABLE.LAST
1598 LOOP
1599 FOR l_attr_index IN p_dml_attr_name_value_pairs.FIRST .. p_dml_attr_name_value_pairs.LAST
1600 LOOP
1601 if L_ATTRIBUTES_DATA_TABLE(i).ATTR_NAME = p_dml_attr_name_value_pairs(l_attr_index).ATTR_NAME
1602 AND ((L_ATTRIBUTES_DATA_TABLE(i).ATTR_VALUE_STR is NULL AND p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_STR is NOT NULL)
1603 OR(L_ATTRIBUTES_DATA_TABLE(i).ATTR_VALUE_NUM is NULL AND p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM is NOT NULL)
1604 OR(L_ATTRIBUTES_DATA_TABLE(i).ATTR_VALUE_DATE is NULL AND p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_DATE is NOT NULL))
1605 THEN
1606 L_ATTRIBUTES_DATA_TABLE(i):= p_dml_attr_name_value_pairs(l_attr_index);
1607
1608 END IF;
1609 END LOOP;
1610
1611 END LOOP;
1612 END IF;
1613 IF p_mode ='UPDATE' OR p_mode ='DELETE'
1614 THEN
1615 l_temp_extension_id := l_extension_id;
1616 END IF;
1617 EGO_USER_ATTRS_DATA_PVT.Generate_DML_For_Row (
1618 p_api_version => 1.0
1619 ,p_object_name => p_object_name
1620 ,p_attr_group_id => p_attr_group_id
1621 ,p_application_id => p_application_id
1622 ,p_attr_group_type => p_attr_group_type
1623 ,p_attr_group_name => p_attr_group_name
1624 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1625 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1626 ,P_DATA_LEVEL => P_DATA_LEVEL_NAME
1627 ,p_data_level_name_value_pairs => p_data_level_name_value_pairs
1628 ,p_extension_id => l_temp_extension_id
1629 ,p_attr_name_value_pairs => L_ATTRIBUTES_DATA_TABLE
1630 ,p_mode => p_mode
1631 ,p_extra_pk_col_name_val_pairs => p_extra_pk_col_name_val_pairs
1632 ,p_alternate_ext_b_table_name => l_pending_base_tbl
1633 ,p_alternate_ext_tl_table_name => l_pending_tl_tbl
1634 ,p_alternate_ext_vl_name => l_pending_vl
1635 ,p_execute_dml => FND_API.G_FALSE
1636 ,p_init_fnd_msg_list => FND_API.G_FALSE
1637 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
1638 ,p_raise_business_event => FALSE
1639 ,x_return_status => x_return_status
1640 ,x_errorcode => x_errorcode
1641 ,x_msg_count => x_msg_count
1642 ,x_msg_data => x_msg_data
1643 ,x_b_dml_for_ag => l_b_dml_for_ag
1644 ,x_tl_dml_for_ag => l_tl_dml_for_ag
1645 ,x_b_bind_count => l_b_bind_count
1646 ,x_tl_bind_count => l_tl_bind_count
1647 ,x_b_bind_attr_table => l_b_bind_attr_table
1648 ,x_tl_bind_attr_table => l_tl_bind_attr_table
1649 );
1650 IF g_debug_flag THEN
1651 Write_Debug('Insert base DML : '|| l_b_dml_for_ag);
1652 Write_Debug('Insert tl DML : '|| l_tl_dml_for_ag);
1653 END IF;
1654 if p_mode = 'CREATE' and (p_acd_type ='CHANGE' OR p_acd_type ='DELETE') AND l_b_bind_attr_table is NOT NULL
1655 THEN
1656
1657 l_bind_index := l_b_bind_attr_table.FIRST;
1658 l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := l_extension_id;
1659 END IF;
1660 IF l_b_dml_for_ag IS NOT NULL
1661 THEN
1662 DBMS_SQL.Parse(l_cursor_id, l_b_dml_for_ag, DBMS_SQL.Native);
1663
1664 if l_b_bind_attr_table is NOT NULL AND p_mode <>'DELETE'
1665 THEN
1666
1667
1668 FOR l_bind_index IN l_b_bind_attr_table.FIRST .. l_b_bind_attr_table.LAST
1669 LOOP
1670 FOR l_attr_index IN p_dml_attr_name_value_pairs.FIRST .. p_dml_attr_name_value_pairs.LAST
1671 LOOP
1672
1673 if l_b_bind_attr_table(l_bind_index).ATTR_DISP_VALUE is NOT NULL
1674 AND SUBSTR(l_b_bind_attr_table(l_bind_index).ATTR_DISP_VALUE,INSTR(l_b_bind_attr_table(l_bind_index).ATTR_DISP_VALUE,'$$')+2)= p_dml_attr_name_value_pairs(l_attr_Index).ATTR_NAME
1675 THEN
1676 l_b_bind_attr_table(l_bind_index).ATTR_VALUE_STR := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_STR;
1677 l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM;
1678 l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM;
1679 exit;
1680 END IF;
1681 END LOOP;
1682 IF ( l_b_bind_attr_table(l_bind_index).ATTR_VALUE_STR is not NULL)
1683 THEN
1684 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_b_bind_attr_table(l_bind_index).ATTR_VALUE_STR);
1685 ELSIF (l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM is not NULL)
1686 THEN
1687 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM);
1688 ELSIF (l_b_bind_attr_table(l_bind_index).ATTR_VALUE_DATE is not NULL)
1689 THEN
1690 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_b_bind_attr_table(l_bind_index).ATTR_VALUE_DATE);
1691 ELSE
1692 IF l_bind_index = l_b_bind_attr_table.LAST AND (p_mode ='UPDATE' OR p_mode ='DELETE')
1693 THEN
1694 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_extension_id);
1695 ELSE
1696 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_b_bind_attr_table(l_bind_index).ATTR_VALUE_STR);
1697 END IF;
1698 END IF;
1699
1700 END LOOP;
1701 END IF;
1702
1703 l_number_of_rows := DBMS_SQL.Execute(l_cursor_id);
1704 DBMS_SQL.Close_Cursor(l_cursor_id);
1705 END IF;
1706
1707 if p_mode = 'CREATE' and (p_acd_type ='CHANGE' OR p_acd_type ='DELETE') AND l_tl_bind_attr_table is NOT NULL
1708 THEN
1709 l_bind_index := l_tl_bind_attr_table.FIRST;
1710 l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := l_extension_id;
1711 END IF;
1712
1713
1714 IF l_tl_dml_for_ag is NOT NULL
1715 THEN
1716 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1717
1718 DBMS_SQL.Parse(l_cursor_id, l_tl_dml_for_ag, DBMS_SQL.Native);
1719
1720 IF l_tl_bind_attr_table is NOT NULL AND p_mode <>'DELETE'
1721 THEN
1722 FOR l_bind_index IN l_tl_bind_attr_table.FIRST .. l_tl_bind_attr_table.LAST
1723 LOOP
1724 FOR l_attr_index IN p_dml_attr_name_value_pairs.FIRST .. p_dml_attr_name_value_pairs.LAST
1725 LOOP
1726 if l_tl_bind_attr_table(l_bind_index).ATTR_DISP_VALUE is NOT NULL
1727 AND SUBSTR(l_tl_bind_attr_table(l_bind_index).ATTR_DISP_VALUE,INSTR(l_tl_bind_attr_table(l_bind_index).ATTR_DISP_VALUE,'$$')+2)= p_dml_attr_name_value_pairs(l_attr_Index).ATTR_NAME
1728 THEN
1729 l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_STR := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_STR;
1730 l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM;
1731 l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM;
1732 exit;
1733 END IF;
1734 END LOOP;
1735 IF ( l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_STR is not NULL)
1736 THEN
1737
1738 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_STR);
1739 ELSIF (l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM is not NULL)
1740 THEN
1741
1742 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM);
1743 ELSIF (l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_DATE is not NULL)
1744 THEN
1745
1746 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_DATE);
1747 ELSE
1748 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_STR);
1749 END IF;
1750
1751
1752 END LOOP;
1753 END IF;
1754 l_number_of_rows := DBMS_SQL.Execute(l_cursor_id);
1755
1756 DBMS_SQL.Close_Cursor(l_cursor_id);
1757 END IF;
1758
1759 END IF;
1760 END IF;
1761
1762
1763
1764 IF g_debug_flag THEN
1765 Write_Debug('Closing Debug Session: '
1766 || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
1767 Close_Debug_Session ;
1768 END IF ;
1769
1770
1771 END INSERT_ITEM_USER_ATTRS;
1772
1773
1774 PROCEDURE DELETE_ITEM_ATTRS
1775 ( p_api_version IN NUMBER
1776 ,p_object_name IN VARCHAR2
1777 ,p_application_id IN NUMBER
1778 ,p_attr_group_type IN VARCHAR2
1779 ,p_pk_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
1780 ,x_return_status OUT NOCOPY VARCHAR2
1781 ,x_errorcode OUT NOCOPY NUMBER
1782 ,x_msg_count OUT NOCOPY NUMBER
1783 ,x_msg_data OUT NOCOPY VARCHAR2
1784 )
1785 IS
1786 BEGIN
1787 NULL;
1788 END DELETE_ITEM_ATTRS;
1789
1790 PROCEDURE SETUP_IMPL_ATTR_DATA_ROW
1791 (
1792 p_api_version IN NUMBER
1793 ,p_object_name IN VARCHAR2
1794 ,p_attr_group_id IN NUMBER
1795 ,p_application_id IN NUMBER
1796 ,p_attr_group_type IN VARCHAR2
1797 ,p_attr_group_name IN VARCHAR2
1798 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1799 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1800 ,P_DATA_LEVEL_NAME IN VARCHAR2
1801 ,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1802 ,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE DEFAULT NULL
1803 ,x_setup_attr_data OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
1804 ,x_return_status OUT NOCOPY VARCHAR2
1805 ,x_errorcode OUT NOCOPY NUMBER
1806 ,x_msg_count OUT NOCOPY NUMBER
1807 ,x_msg_data OUT NOCOPY VARCHAR2
1808 )
1809 IS
1810
1811 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE ;
1812 l_attributes_data_table EGO_USER_ATTR_DATA_TABLE ;
1813 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1814 l_extension_id NUMBER := NULL;
1815 l_temp_extension_id NUMBER := NULL;
1816
1817 BEGIN
1818
1819 l_attributes_data_table := EGO_USER_ATTR_DATA_TABLE();
1820 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE() ;
1821
1822 x_setup_attr_data := p_attr_name_value_pairs;
1823 /*if p_data_level_name_value_pairs is NOT NULL
1824 --then
1825 l_attr_group_request_table
1826 := EGO_ATTR_GROUP_REQUEST_TABLE
1827 (
1828 EGO_ATTR_GROUP_REQUEST_OBJ(p_attr_group_id
1829 , p_application_id
1830 , p_attr_group_type
1831 , null
1832 ,
1833 , p_data_level_name_value_pairs
1834 (p_data_level_name_value_pairs.first).VALUE
1835 , null
1836 , null
1837 , null)
1838 );
1839 --else */
1840 l_attr_group_request_table
1841 := EGO_ATTR_GROUP_REQUEST_TABLE
1842 (
1843 EGO_ATTR_GROUP_REQUEST_OBJ(p_attr_group_id
1844 , p_application_id
1845 , p_attr_group_type
1846 , null
1847 , p_data_level_name
1848 , null
1849 , null
1850 , null
1851 , null
1852 , null
1853 , null
1854 )
1855 );
1856 --end if ;
1857 if p_data_level_name_value_pairs is not null
1858 then
1859 for i in p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
1860 LOOP
1861 if i=1
1862 then
1863 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_1 :=
1864 p_data_level_name_value_pairs(i).VALUE ;
1865 elsif i=2
1866 then
1867 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_2 :=
1868 p_data_level_name_value_pairs(i).VALUE;
1869 elsif i=3
1870 then
1871 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_3 :=
1872 p_data_level_name_value_pairs(i).VALUE ;
1873 elsif i=4
1874 then
1875 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_4 :=
1876 p_data_level_name_value_pairs(i).VALUE ;
1877 elsif i=5
1878 then
1879 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_5 :=
1880 p_data_level_name_value_pairs(i).VALUE ;
1881 end if;
1882 end loop;
1883 end if;
1884
1885
1886 EGO_USER_ATTRS_DATA_PVT.Get_User_Attrs_Data
1887 (
1888 p_api_version => p_api_version
1889 ,p_object_name => p_object_name
1890 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1891 ,p_attr_group_request_table => l_attr_group_request_table
1892 ,x_attributes_row_table => l_attributes_row_table
1893 ,x_attributes_data_table => l_attributes_data_table
1894 ,p_init_fnd_msg_list => FND_API.G_FALSE
1895 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
1896 ,x_return_status => x_return_status
1897 ,x_errorcode => x_errorcode
1898 ,x_msg_count => x_msg_count
1899 ,x_msg_data => x_msg_data
1900 );
1901
1902
1903 IF ( x_setup_attr_data IS NULL OR l_attributes_data_table IS NULL )
1904 THEN
1905 RETURN ;
1906 END IF ;
1907
1908
1909 FOR i IN x_setup_attr_data.FIRST .. x_setup_attr_data.LAST
1910 LOOP
1911
1912
1913 FOR j IN l_attributes_data_table.FIRST .. l_attributes_data_table.LAST
1914 LOOP
1915
1916 IF (x_setup_attr_data(i).ROW_IDENTIFIER= l_attributes_data_table(j).ROW_IDENTIFIER)
1917 AND x_setup_attr_data(i).ATTR_NAME = l_attributes_data_table(j).ATTR_NAME
1918 THEN
1919
1920 if (x_setup_attr_data(i).ATTR_VALUE_STR IS NULL
1921 and x_setup_attr_data(i).ATTR_VALUE_NUM IS NULL
1922 and x_setup_attr_data(i).ATTR_VALUE_DATE IS NULL
1923 )
1924 then
1925
1926 -- Copy whole prod attribute data beause there is no change for this attr
1927 x_setup_attr_data (i) := l_attributes_data_table(j);
1928
1929 elsif x_setup_attr_data(i).ATTR_VALUE_STR is NOT NULL
1930 and x_setup_attr_data(i).ATTR_VALUE_STR = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_CHAR
1931 then
1932
1933 x_setup_attr_data(i).ATTR_VALUE_STR := NULL;
1934
1935 elsif x_setup_attr_data(i).ATTR_VALUE_NUM is NOT NULL
1936 and x_setup_attr_data(i).ATTR_VALUE_NUM = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_NUM
1937 then
1938
1939 x_setup_attr_data(i).ATTR_VALUE_NUM := NULL;
1940
1941 elsif x_setup_attr_data(i).ATTR_VALUE_DATE is NOT NULL
1942 and x_setup_attr_data(i).ATTR_VALUE_DATE = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_DATE
1943 then
1944
1945 x_setup_attr_data(i).ATTR_VALUE_DATE := NULL;
1946
1947 else
1948 -- Keep the pending change attribute value
1949 null ;
1950
1951 end if;
1952
1953 EXIT;
1954
1955 END IF ;
1956
1957 END LOOP;
1958 END LOOP;
1959
1960 END SETUP_IMPL_ATTR_DATA_ROW;
1961
1962
1963 PROCEDURE VALIDATE_GDSN_RECORDS(p_inventory_item_id IN NUMBER
1964 ,p_organization_id IN NUMBER
1965 ,p_attr_group_type IN VARCHAR2
1966 ,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
1967 ,p_tl_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
1968 ,x_return_status OUT NOCOPY VARCHAR2
1969 ,x_msg_count OUT NOCOPY NUMBER
1970 ,x_msg_data OUT NOCOPY VARCHAR2
1971
1972 )
1973 is
1974 l_single_row_attrs EGO_ITEM_PUB.UCCNET_ATTRS_SINGL_ROW_REC_TYP ;
1975 l_multi_row_attrs EGO_ITEM_PUB.UCCNET_ATTRS_MULTI_ROW_TBL_TYP ;
1976 l_extra_attrs_rec EGO_ITEM_PUB.UCCNET_EXTRA_ATTRS_REC_TYP;
1977 p_index NUMBER;
1978 BEGIN
1979 if p_attr_group_type='EGO_ITEM_GTIN_MULTI_ATTRS'
1980 THEN
1981 p_index := 1;
1982 l_multi_row_attrs(p_index).LANGUAGE_CODE := USERENV('LANG') ;
1983 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).MANUFACTURER_GLN,'MANUFACTURER_GLN');
1984 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).MANUFACTURER_ID, 'MANUFACTURER_ID') ;
1985 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).BAR_CODE_TYPE,'BAR_CODE_TYPE');
1986 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).COLOR_CODE_LIST_AGENCY,'COLOR_CODE_LIST_AGENCY') ;
1987 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).COLOR_CODE_VALUE,'COLOR_CODE_VALUE');
1988 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).CLASS_OF_DANGEROUS_CODE,'CLASS_OF_DANGEROUS_CODE') ;
1989 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_MARGIN_NUMBER, 'DANGEROUS_GOODS_MARGIN_NUMBER');
1990 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_HAZARDOUS_CODE,'DANGEROUS_GOODS_HAZARDOUS_CODE');
1991 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_PACK_GROUP ,'DANGEROUS_GOODS_PACK_GROUP') ;
1992 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_REG_CODE ,'DANGEROUS_GOODS_REG_CODE') ;
1993 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_SHIPPING_NAME ,'DANGEROUS_GOODS_SHIPPING_NAME') ;
1994 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).UNITED_NATIONS_DANG_GOODS_NO ,'UNITED_NATIONS_DANG_GOODS_NO') ;
1995 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).FLASH_POINT_TEMP,'FLASH_POINT_TEMP') ;
1996 -- UOM
1997 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).UOM_FLASH_POINT_TEMP,'UOM_FLASH_POINT_TEMP');
1998 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).COUNTRY_OF_ORIGIN,'COUNTRY_OF_ORIGIN');
1999 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).HARMONIZED_TARIFF_SYS_ID_CODE,'HARMONIZED_TARIFF_SYS_ID_CODE');
2000 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).SIZE_CODE_LIST_AGENCY,'SIZE_CODE_LIST_AGENCY');
2001 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).SIZE_CODE_VALUE,'SIZE_CODE_VALUE');
2002 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).HANDLING_INSTRUCTIONS_CODE,'HANDLING_INSTRUCTIONS_CODE') ;
2003 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_TECHNICAL_NAME,'DANGEROUS_GOODS_TECHNICAL_NAME');
2004 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DELIVERY_METHOD_INDICATOR,'DELIVERY_METHOD_INDICATOR');
2005
2006 ELSIF p_attr_group_type='EGO_ITEM_GTIN_ATTRS'
2007 THEN
2008 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_A_CONSUMER_UNIT,'IS_TRADE_ITEM_A_CONSUMER_UNIT');
2009 getValue(p_attr_name_value_pairs, l_single_row_attrs.IS_TRADE_ITEM_INFO_PRIVATE,'IS_TRADE_ITEM_INFO_PRIVATE');
2010 getValue(p_attr_name_value_pairs, l_single_row_attrs.GROSS_WEIGHT ,'GROSS_WEIGHT');
2011 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_GROSS_WEIGHT,'UOM_GROSS_WEIGHT');
2012
2013 getValue(p_attr_name_value_pairs,l_single_row_attrs.EFFECTIVE_DATE,'EFFECTIVE_DATE');
2014 getValue(p_attr_name_value_pairs,l_single_row_attrs.END_AVAILABILITY_DATE_TIME,'END_AVAILABILITY_DATE_TIME');
2015 getValue(p_attr_name_value_pairs,l_single_row_attrs.START_AVAILABILITY_DATE_TIME,'START_AVAILABILITY_DATE_TIME');
2016 getValue(p_attr_name_value_pairs,l_single_row_attrs.BRAND_NAME,'BRAND_NAME');
2017 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_A_BASE_UNIT,'IS_TRADE_ITEM_A_BASE_UNIT');
2018 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_A_VARIABLE_UNIT,'.IS_TRADE_ITEM_A_VARIABLE_UNIT');
2019 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACK_MARKED_WITH_EXP_DATE,'IS_PACK_MARKED_WITH_EXP_DATE');
2020 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACK_MARKED_WITH_GREEN_DOT,'IS_PACK_MARKED_WITH_GREEN_DOT');
2021 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACK_MARKED_WITH_INGRED ,'IS_PACK_MARKED_WITH_INGRED');
2022 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACKAGE_MARKED_AS_REC, 'IS_PACKAGE_MARKED_AS_REC');
2023 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACKAGE_MARKED_RET,'IS_PACKAGE_MARKED_RET');
2024 getValue(p_attr_name_value_pairs,l_single_row_attrs.STACKING_FACTOR ,'STACKING_FACTOR');
2025 getValue(p_attr_name_value_pairs,l_single_row_attrs.STACKING_WEIGHT_MAXIMUM,'STACKING_WEIGHT_MAXIMUM');
2026 -- UOM:
2027 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_STACKING_WEIGHT_MAXIMUM,'UOM_STACKING_WEIGHT_MAXIMUM');
2028
2029 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDERING_LEAD_TIME,'ORDERING_LEAD_TIME');
2030 -- UOM:
2031 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_ORDERING_LEAD_TIME,'UOM_ORDERING_LEAD_TIME');
2032
2033 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDER_QUANTITY_MAX,'ORDER_QUANTITY_MAX');
2034 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDER_QUANTITY_MIN,'ORDER_QUANTITY_MIN');
2035 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDER_QUANTITY_MULTIPLE,'ORDER_QUANTITY_MULTIPLE');
2036 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDER_SIZING_FACTOR,'ORDER_SIZING_FACTOR');
2037 getValue(p_attr_name_value_pairs,l_single_row_attrs.EFFECTIVE_START_DATE,'EFFECTIVE_START_DATE');
2038 getValue(p_attr_name_value_pairs,l_single_row_attrs.CATALOG_PRICE,'CATALOG_PRICE');
2039 getValue(p_attr_name_value_pairs,l_single_row_attrs.EFFECTIVE_END_DATE,'EFFECTIVE_END_DATE');
2040 getValue(p_attr_name_value_pairs,l_single_row_attrs.SUGGESTED_RETAIL_PRICE,'SUGGESTED_RETAIL_PRICE');
2041 getValue(p_attr_name_value_pairs,l_single_row_attrs.MATERIAL_SAFETY_DATA_SHEET_NO,'MATERIAL_SAFETY_DATA_SHEET_NO');
2042 getValue(p_attr_name_value_pairs,l_single_row_attrs.HAS_BATCH_NUMBER,'HAS_BATCH_NUMBER');
2043 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_NON_SOLD_TRADE_RET_FLAG,'IS_NON_SOLD_TRADE_RET_FLAG');
2044 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_MAR_REC_FLAG,'IS_TRADE_ITEM_MAR_REC_FLAG');
2045 getValue(p_attr_name_value_pairs,l_single_row_attrs.DIAMETER,'DIAMETER');
2046 -- UOM:
2047 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DIAMETER,'UOM_DIAMETER');
2048
2049 getValue(p_attr_name_value_pairs,l_single_row_attrs.DRAINED_WEIGHT,'DRAINED_WEIGHT');
2050 -- UOM:
2051 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DRAINED_WEIGHT,'UOM_DRAINED_WEIGHT');
2052
2053 getValue(p_attr_name_value_pairs,l_single_row_attrs.GENERIC_INGREDIENT,'GENERIC_INGREDIENT');
2054
2055 getValue(p_attr_name_value_pairs,l_single_row_attrs.GENERIC_INGREDIENT_STRGTH,'GENERIC_INGREDIENT_STRGTH');
2056 -- UOM:
2057 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_GENERIC_INGREDIENT_STRGTH,'UOM_GENERIC_INGREDIENT_STRGTH');
2058
2059 getValue(p_attr_name_value_pairs,l_single_row_attrs.INGREDIENT_STRENGTH,'INGREDIENT_STRENGTH');
2060 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_NET_CONTENT_DEC_FLAG,'IS_NET_CONTENT_DEC_FLAG');
2061 getValue(p_attr_name_value_pairs,l_single_row_attrs.NET_CONTENT,'NET_CONTENT');
2062 -- UOM:
2063 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_NET_CONTENT,'UOM_NET_CONTENT');
2064
2065 getValue(p_attr_name_value_pairs,l_single_row_attrs.PEG_HORIZONTAL,'PEG_HORIZONTAL');
2066 -- UOM:
2067 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_PEG_HORIZONTAL,'UOM_PEG_HORIZONTAL');
2068
2069 getValue(p_attr_name_value_pairs,l_single_row_attrs.PEG_VERTICAL,'PEG_VERTICAL');
2070 -- UOM:
2071 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_PEG_VERTICAL,'UOM_PEG_VERTICAL');
2072
2073 getValue(p_attr_name_value_pairs,l_single_row_attrs.CONSUMER_AVAIL_DATE_TIME,'CONSUMER_AVAIL_DATE_TIME');
2074
2075 getValue(p_attr_name_value_pairs,l_single_row_attrs.DEL_TO_DIST_CNTR_TEMP_MAX,'DEL_TO_DIST_CNTR_TEMP_MAX');
2076 -- UOM:
2077 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DEL_TO_DIST_CNTR_TEMP_MAX,'UOM_DEL_TO_DIST_CNTR_TEMP_MAX');
2078
2079 getValue(p_attr_name_value_pairs,l_single_row_attrs.DEL_TO_DIST_CNTR_TEMP_MIN,'DEL_TO_DIST_CNTR_TEMP_MIN');
2080 -- UOM:
2081 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DEL_TO_DIST_CNTR_TEMP_MIN,'UOM_DEL_TO_DIST_CNTR_TEMP_MIN');
2082 getValue(p_attr_name_value_pairs,l_single_row_attrs.DELIVERY_TO_MRKT_TEMP_MAX,'DELIVERY_TO_MRKT_TEMP_MAX');
2083 -- UOM:
2084 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DELIVERY_TO_MRKT_TEMP_MAX,'UOM_DELIVERY_TO_MRKT_TEMP_MAX');
2085
2086 getValue(p_attr_name_value_pairs,l_single_row_attrs.DELIVERY_TO_MRKT_TEMP_MIN,'DELIVERY_TO_MRKT_TEMP_MIN');
2087 -- UOM:
2088 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DELIVERY_TO_MRKT_TEMP_MIN,'UOM_DELIVERY_TO_MRKT_TEMP_MIN');
2089
2090
2091 getValue(p_attr_name_value_pairs,l_single_row_attrs.SUB_BRAND,'SUB_BRAND');
2092 --getValue(p_attr_name_value_pairs,l_single_row_attrs.TRADE_ITEM_DESCRIPTOR,'TRADE_ITEM_DESCRIPTOR');
2093 getValue(p_attr_name_value_pairs,l_single_row_attrs.EANUCC_CODE,'EANUCC_CODE');
2094 getValue(p_attr_name_value_pairs,l_single_row_attrs.EANUCC_TYPE,'EANUCC_TYPE');
2095 getValue(p_attr_name_value_pairs,l_single_row_attrs.RETAIL_PRICE_ON_TRADE_ITEM,'RETAIL_PRICE_ON_TRADE_ITEM');
2096 getValue(p_attr_name_value_pairs,l_single_row_attrs.QUANTITY_OF_COMP_LAY_ITEM,'QUANTITY_OF_COMP_LAY_ITEM');
2097 getValue(p_attr_name_value_pairs,l_single_row_attrs.QUANITY_OF_ITEM_IN_LAYER,'QUANITY_OF_ITEM_IN_LAYER');
2098 getValue(p_attr_name_value_pairs,l_single_row_attrs.QUANTITY_OF_ITEM_INNER_PACK,'QUANTITY_OF_ITEM_INNER_PACK');
2099 getValue(p_attr_name_value_pairs,l_single_row_attrs.QUANTITY_OF_INNER_PACK,'QUANTITY_OF_INNER_PACK');
2100 getValue(p_attr_name_value_pairs,l_single_row_attrs.BRAND_OWNER_GLN,'BRAND_OWNER_GLN');
2101 getValue(p_attr_name_value_pairs,l_single_row_attrs.BRAND_OWNER_NAME,'BRAND_OWNER_NAME');
2102 getValue(p_attr_name_value_pairs,l_single_row_attrs.STORAGE_HANDLING_TEMP_MAX,'STORAGE_HANDLING_TEMP_MAX');
2103
2104 -- UOM:
2105 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_STORAGE_HANDLING_TEMP_MAX,'UOM_STORAGE_HANDLING_TEMP_MAX');
2106
2107 getValue(p_attr_name_value_pairs,l_single_row_attrs.STORAGE_HANDLING_TEMP_MIN,'STORAGE_HANDLING_TEMP_MIN');
2108 -- UOM:
2109 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_STORAGE_HANDLING_TEMP_MIN,'UOM_STORAGE_HANDLING_TEMP_MIN');
2110
2111 getValue(p_attr_name_value_pairs,l_single_row_attrs.TRADE_ITEM_COUPON,'TRADE_ITEM_COUPON');
2112 getValue(p_attr_name_value_pairs,l_single_row_attrs.DEGREE_OF_ORIGINAL_WORT,'DEGREE_OF_ORIGINAL_WORT');
2113 getValue(p_attr_name_value_pairs,l_single_row_attrs.FAT_PERCENT_IN_DRY_MATTER,'FAT_PERCENT_IN_DRY_MATTER');
2114 getValue(p_attr_name_value_pairs,l_single_row_attrs.PERCENT_OF_ALCOHOL_BY_VOL,'PERCENT_OF_ALCOHOL_BY_VOL');
2115 getValue(p_attr_name_value_pairs,l_single_row_attrs.ISBN_NUMBER,'ISBN_NUMBER');
2116 getValue(p_attr_name_value_pairs,l_single_row_attrs.ISSN_NUMBER,'ISSN_NUMBER');
2117 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_INGREDIENT_IRRADIATED,'IS_INGREDIENT_IRRADIATED');
2118 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_RAW_MATERIAL_IRRADIATED,'IS_RAW_MATERIAL_IRRADIATED');
2119 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_GENETICALLY_MOD,'IS_TRADE_ITEM_GENETICALLY_MOD');
2120 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_IRRADIATED,'IS_TRADE_ITEM_IRRADIATED');
2121 getValue(p_attr_name_value_pairs,l_single_row_attrs.SECURITY_TAG_LOCATION,'SECURITY_TAG_LOCATION');
2122 getValue(p_attr_name_value_pairs,l_single_row_attrs.URL_FOR_WARRANTY,'URL_FOR_WARRANTY');
2123 getValue(p_attr_name_value_pairs,l_single_row_attrs.NESTING_INCREMENT,'NESTING_INCREMENT');
2124 -- UOM:
2125 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_NESTING_INCREMENT,'UOM_NESTING_INCREMENT');
2126
2127 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_RECALLED,'IS_TRADE_ITEM_RECALLED');
2128 getValue(p_attr_name_value_pairs,l_single_row_attrs.MODEL_NUMBER,'MODEL_NUMBER');
2129 getValue(p_attr_name_value_pairs,l_single_row_attrs.PIECES_PER_TRADE_ITEM,'PIECES_PER_TRADE_ITEM');
2130 -- UOM:
2131 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_PIECES_PER_TRADE_ITEM,'UOM_PIECES_PER_TRADE_ITEM');
2132
2133 getValue(p_attr_name_value_pairs,l_single_row_attrs.DEPT_OF_TRNSPRT_DANG_GOODS_NUM,'DEPT_OF_TRNSPRT_DANG_GOODS_NUM');
2134 getValue(p_attr_name_value_pairs,l_single_row_attrs.RETURN_GOODS_POLICY,'RETURN_GOODS_POLICY');
2135 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_OUT_OF_BOX_PROVIDED,'IS_OUT_OF_BOX_PROVIDED');
2136 getValue(p_tl_attr_names_values,l_single_row_attrs.INVOICE_NAME,'INVOICE_NAME');
2137 getValue(p_tl_attr_names_values,l_single_row_attrs.DESCRIPTIVE_SIZE,'DESCRIPTIVE_SIZE');
2138 getValue(p_tl_attr_names_values,l_single_row_attrs.FUNCTIONAL_NAME,'FUNCTIONAL_NAME');
2139 getValue(p_tl_attr_names_values,l_single_row_attrs.TRADE_ITEM_FORM_DESCRIPTION,'TRADE_ITEM_FORM_DESCRIPTION');
2140 getValue(p_tl_attr_names_values,l_single_row_attrs.WARRANTY_DESCRIPTION,'WARRANTY_DESCRIPTION');
2141 getValue(p_tl_attr_names_values,l_single_row_attrs.TRADE_ITEM_FINISH_DESCRIPTION,'TRADE_ITEM_FINISH_DESCRIPTION');
2142 getValue(p_tl_attr_names_values,l_single_row_attrs.DESCRIPTION_SHORT,'DESCRIPTION_SHORT');
2143 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_BARCODE_SYMBOLOGY_DERIVABLE,'IS_BARCODE_SYMBOLOGY_DERIVABLE');
2144
2145
2146 END IF;
2147
2148 EGO_GTIN_ATTRS_PVT.Validate_Attributes(
2149 p_inventory_item_id => p_inventory_item_id
2150 ,p_organization_id => p_organization_id
2151 ,p_singe_row_attrs_rec => l_single_row_attrs
2152 ,p_multi_row_attrs_tbl => l_multi_row_attrs
2153 ,p_extra_attrs_rec => l_extra_attrs_rec
2154 ,x_return_status => x_return_status
2155 ,x_msg_count => x_msg_count
2156 ,x_msg_data => x_msg_data
2157 );
2158 END VALIDATE_GDSN_RECORDS;
2159
2160
2161 PROCEDURE UPDATE_DATA_LEVEL(P_PK_ATTR_NAME_VALUE_PAIRS EGO_COL_NAME_VALUE_PAIR_ARRAY
2162 ,P_NEW_DL_NAME_VALUE_PAIRS EGO_COL_NAME_VALUE_PAIR_ARRAY
2163 ,P_OLD_DL_NAME_VALUE_PAIRS EGO_COL_NAME_VALUE_PAIR_ARRAY
2164 ,P_OBJECT_NAME VARCHAR2
2165 ,P_APPLICATION_ID NUMBER)
2166 IS
2167 CURSOR C_DATA_LEVELS (p_objet_name VARCHAR2)
2168 IS
2169 SELECT DATA_LEVEL_INTERNAL_NAME
2170 ,DATA_LEVEL_DISPLAY_NAME
2171 ,DATA_LEVEL_COLUMN
2172 ,DL_COL_DATA_TYPE
2173 FROM (SELECT LOOKUP_CODE DATA_LEVEL_INTERNAL_NAME
2174 ,MEANING DATA_LEVEL_DISPLAY_NAME
2175 ,DECODE(ATTRIBUTE2, 1, ATTRIBUTE3,
2176 2, ATTRIBUTE5,
2177 3, ATTRIBUTE7,
2178 'NONE') DATA_LEVEL_COLUMN
2179 ,DECODE(ATTRIBUTE2, 1, ATTRIBUTE4,
2180 2, ATTRIBUTE6,
2181 3, ATTRIBUTE8,
2182 'NONE') DL_COL_DATA_TYPE
2183 FROM FND_LOOKUP_VALUES
2184 WHERE LOOKUP_TYPE = 'EGO_EF_DATA_LEVEL'
2185 AND ATTRIBUTE1 = p_objet_name
2186 AND LANGUAGE = USERENV('LANG')) DATA_LEVELS
2187 WHERE DATA_LEVEL_COLUMN <>'NONE';
2188
2189
2190
2191
2192 l_curr_ag_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
2193 l_attr_meta_data EGO_ATTR_METADATA_TABLE;
2194 l_pending_base_tbl VARCHAR2(30);
2195 l_pending_tl_tbl VARCHAR2(30);
2196 l_B_data_level_dml VARCHAR2(10000);
2197 L_B_WHERE_CLAUSE VARCHAR2(4000);
2198 l_TL_data_level_dml VARCHAR2(10000);
2199 L_DYN_ATTR_GRP_SQL VARCHAR2(10000);
2200 L_PEND_BIND_INDEX NUMBER :=0;
2201 L_PEND_BIND_VALUES EGO_COL_NAME_VALUE_PAIR_ARRAY;
2202 L_BIND_INDEX NUMBER :=0;
2203 L_BIND_VALUES EGO_COL_NAME_VALUE_PAIR_ARRAY;
2204 L_ATTR_CURSOR_ID NUMBER;
2205 L_PROD_CURSOR_ID NUMBER;
2206 L_B_CURSOR_ID NUMBER;
2207 L_TL_CURSOR_ID NUMBER;
2208 l_retrieved_value_char VARCHAR(1000);
2209 l_column_count NUMBER;
2210 l_dummy NUMBER;
2211 l_desc_table DBMS_SQL.Desc_Tab;
2212 l_b_update_dml VARCHAR2(10000);
2213 l_tl_update_dml VARCHAR2(10000);
2214 L_UPDATE_WHERE_CLAUSE VARCHAR2(4000) := NULL;
2215 L_UPDATE_BIND_INDEX NUMBER :=0;
2216 L_UPDATE_BIND_VALUES EGO_COL_NAME_VALUE_PAIR_ARRAY;
2217 L_ADDED_UPDATE_B_DML VARCHAR2(10000);
2218 L_ADDED_UPDATE_TL_DML VARCHAR2(10000);
2219 L_ADDED_WHERE_CLAUSE VARCHAR2(4000);
2220 L_B_TEMP_WHERE_CLAUSE VARCHAR2(4000);
2221 L_B_TEMP_DATA_LEVEL_DML VARCHAR2(4000);
2222
2223 BEGIN
2224 SELECT CHANGE_B_TABLE_NAME ,
2225 CHANGE_TL_TABLE_NAME
2226 INTO l_pending_base_tbl,l_pending_tl_tbl
2227 from ENG_PENDING_CHANGE_CTX
2228 where CHANGE_ATTRIBUTE_GROUP_TYPE= 'EGO_ITEMMGMT_GROUP'
2229 AND APPLICATION_ID = p_application_id;
2230
2231 L_B_UPDATE_DML := ' UPDATE '|| l_pending_base_tbl || ' SET EXTENSION_ID=:1';
2232 L_TL_UPDATE_DML := ' UPDATE '|| l_pending_TL_tbl || ' SET EXTENSION_ID=:1 ';
2233 L_UPDATE_BIND_INDEX := L_UPDATE_BIND_INDEX+1;
2234
2235 L_ADDED_UPDATE_B_DML := 'UPDATE ' || l_pending_base_tbl || ' SET ';
2236 L_ADDED_UPDATE_TL_DML := 'UPDATE ' || l_pending_base_tbl|| ' SET ';
2237 L_ADDED_WHERE_CLAUSE := ' WHERE ';
2238
2239 L_DYN_ATTR_GRP_SQL := ' SELECT DISTINCT ATTR_GROUP_ID ' ||
2240 ' FROM ' || l_pending_base_tbl ||
2241 ' WHERE ';
2242
2243 l_B_data_level_dml := ' SELECT A.EXTENSION_ID NEW_EXT_ID, ';
2244 -- ||' B.EXTENSION_ID OLD_EXT_ID, ';
2245
2246 L_B_WHERE_CLAUSE := ' FROM EGO_MTL_SY_ITEMS_EXT_VL A ,'
2247 ||' EGO_MTL_SY_ITEMS_EXT_VL B '
2248 ||' WHERE A.EXTENSION_ID <> B.EXTENSION_ID '
2249 ||' AND A.ATTR_GROUP_ID = B.ATTR_GROUP_ID ';
2250
2251 L_BIND_VALUES := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2252 L_PEND_BIND_VALUES := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2253 IF P_PK_ATTR_NAME_VALUE_PAIRS IS NOT NULL
2254 THEN
2255 FOR i IN 1 .. P_PK_ATTR_NAME_VALUE_PAIRS.LAST
2256 LOOP
2257 IF P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME <> 'CHANGE_LINE_ID'
2258 THEN
2259 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE || ' AND ';
2260 L_BIND_INDEX := L_BIND_INDEX+1;
2261 L_BIND_VALUES.EXTEND();
2262 L_BIND_VALUES(L_BIND_VALUES.LAST) :=
2263 EGO_COL_NAME_VALUE_PAIR_OBJ(P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2264 ,P_PK_ATTR_NAME_VALUE_PAIRS(i).VALUE);
2265 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE
2266 || 'A.'|| P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2267 || '= B.' || P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME;
2268
2269 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE || ' AND '
2270 || 'A.'
2271 || P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2272 || ' = :' ||L_BIND_INDEX;
2273 ELSE
2274 if L_UPDATE_WHERE_CLAUSE is NULL THEN
2275 L_UPDATE_WHERE_CLAUSE := ' WHERE ' ;
2276 END IF;
2277
2278 L_UPDATE_WHERE_CLAUSE := L_UPDATE_WHERE_CLAUSE ||
2279 P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME ||
2280 ' = ''' || P_PK_ATTR_NAME_VALUE_PAIRS(i).VALUE ||'''';
2281
2282 END IF;
2283 IF i >1
2284 THEN
2285
2286 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE || ' AND ';
2287
2288 END IF;
2289 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE
2290 || P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2291 || ' = ' || P_PK_ATTR_NAME_VALUE_PAIRS(i).VALUE;
2292 IF i > 1
2293 THEN
2294 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL || ' AND ';
2295 END IF;
2296
2297 L_PEND_BIND_INDEX := L_PEND_BIND_INDEX+1 ;
2298 L_PEND_BIND_VALUES.EXTEND();
2299 L_PEND_BIND_VALUES(L_PEND_BIND_VALUES.LAST) :=
2300 EGO_COL_NAME_VALUE_PAIR_OBJ(P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2301 ,P_PK_ATTR_NAME_VALUE_PAIRS(i).VALUE);
2302 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL
2303 || P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2304 || ' = :' ||L_PEND_BIND_INDEX;
2305
2306 END LOOP;
2307 END IF;
2308 if P_NEW_DL_NAME_VALUE_PAIRS is NOT NULL
2309 THEN
2310 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL ||' AND (';
2311 FOR i IN 1 .. P_NEW_DL_NAME_VALUE_PAIRS.LAST
2312 LOOP
2313 IF i > 1
2314 THEN
2315 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL ||' OR ';
2316 END IF;
2317 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL
2318 || P_NEW_DL_NAME_VALUE_PAIRS(i).NAME ||' IS NOT NULL ';
2319 END LOOP;
2320 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL ||')';
2321 END IF;
2322
2323 L_ATTR_CURSOR_ID := DBMS_SQL.Open_Cursor;
2324 DBMS_SQL.Parse(L_ATTR_CURSOR_ID, L_DYN_ATTR_GRP_SQL, DBMS_SQL.Native);
2325 DBMS_SQL.Describe_Columns(L_ATTR_CURSOR_ID, l_column_count, l_desc_table);
2326 FOR i IN 1 .. l_column_count
2327 LOOP
2328 DBMS_SQL.Define_Column(L_ATTR_CURSOR_ID, i, l_retrieved_value_char, 1000);
2329
2330 END LOOP;
2331 FOR l_bind_index IN L_PEND_BIND_VALUES.FIRST .. L_PEND_BIND_VALUES.LAST
2332 LOOP
2333 DBMS_SQL.BIND_VARIABLE(L_ATTR_CURSOR_ID, ':'||l_bind_index,L_PEND_BIND_VALUES(l_bind_index).VALUE);
2334
2335 END LOOP;
2336
2337 l_dummy := DBMS_SQL.Execute(L_ATTR_CURSOR_ID);
2338
2339
2340 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE
2341 ||' AND ACD_TYPE = ''ADD'' AND ATTR_GROUP_ID IN (-1';
2342
2343 FOR REC IN C_DATA_LEVELS(P_OBJECT_NAME)
2344 LOOP
2345 FOR i IN 1 .. P_NEW_DL_NAME_VALUE_PAIRS.LAST
2346 LOOP
2347 IF P_NEW_DL_NAME_VALUE_PAIRS(i).NAME = REC.DATA_LEVEL_COLUMN
2348 THEN
2349 l_B_data_level_dml := l_B_data_level_dml
2350 || ' A.'||REC.DATA_LEVEL_COLUMN || ' NEW_'||REC.DATA_LEVEL_COLUMN;
2351 --|| ',B.'||REC.DATA_LEVEL_COLUMN || ' OLD_'||REC.DATA_LEVEL_COLUMN;
2352
2353 L_UPDATE_BIND_INDEX := L_UPDATE_BIND_INDEX+1;
2354 L_B_UPDATE_DML := L_B_UPDATE_DML ||' , '|| REC.DATA_LEVEL_COLUMN || ' = :'||L_UPDATE_BIND_INDEX;
2355 L_TL_UPDATE_DML := L_TL_UPDATE_DML ||' , '|| REC.DATA_LEVEL_COLUMN|| ' = :'||L_UPDATE_BIND_INDEX;
2356 L_ADDED_UPDATE_B_DML := L_ADDED_UPDATE_B_DML
2357 ||' '|| REC.DATA_LEVEL_COLUMN
2358 ||' = ' || P_NEW_DL_NAME_VALUE_PAIRS(i).VALUE;
2359 L_ADDED_UPDATE_TL_DML := L_ADDED_UPDATE_TL_DML
2360 ||' '|| REC.DATA_LEVEL_COLUMN
2361 ||' = ' || P_NEW_DL_NAME_VALUE_PAIRS(i).VALUE;
2362 L_BIND_INDEX := L_BIND_INDEX+1;
2363 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE
2364 || ' AND A.'|| P_NEW_DL_NAME_VALUE_PAIRS(i).NAME
2365 || ' = :' ||L_BIND_INDEX;
2366 L_BIND_VALUES.EXTEND();
2367 L_BIND_VALUES(L_BIND_VALUES.LAST) :=
2368 EGO_COL_NAME_VALUE_PAIR_OBJ(P_NEW_DL_NAME_VALUE_PAIRS(i).NAME
2369 ,P_NEW_DL_NAME_VALUE_PAIRS(i).VALUE);
2370
2371
2372
2373 EXIT;
2374 END IF;
2375
2376
2377 END LOOP; -- DATA LEVEL ATTR VALUES
2378 FOR i IN 1 .. P_OLD_DL_NAME_VALUE_PAIRS.LAST
2379 LOOP
2380 IF P_OLD_DL_NAME_VALUE_PAIRS(i).NAME = REC.DATA_LEVEL_COLUMN
2381 THEN
2382 L_BIND_INDEX := L_BIND_INDEX+1;
2383 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE
2384 || ' AND B.'|| P_OLD_DL_NAME_VALUE_PAIRS(i).NAME
2385 || ' = :' ||L_BIND_INDEX;
2386 L_BIND_VALUES.EXTEND();
2387 L_BIND_VALUES(L_BIND_VALUES.LAST) :=
2388 EGO_COL_NAME_VALUE_PAIR_OBJ(P_OLD_DL_NAME_VALUE_PAIRS(i).NAME
2389 ,P_OLD_DL_NAME_VALUE_PAIRS(i).VALUE);
2390
2391
2392 EXIT;
2393 END IF;
2394
2395 END LOOP; -- DATA LEVEL ATTR VALUES
2396 END LOOP; -- DATA LEVELS
2397
2398 L_UPDATE_BIND_INDEX := L_UPDATE_BIND_INDEX +1;
2399
2400 if L_UPDATE_WHERE_CLAUSE is NULL then
2401 L_UPDATE_WHERE_CLAUSE := ' WHERE ';
2402 ELSE
2403 L_UPDATE_WHERE_CLAUSE := L_UPDATE_WHERE_CLAUSE || ' AND ';
2404 END IF;
2405
2406 L_UPDATE_WHERE_CLAUSE := L_UPDATE_WHERE_CLAUSE || ' EXTENSION_ID =:' || L_UPDATE_BIND_INDEX;
2407
2408 L_B_UPDATE_DML := L_B_UPDATE_DML || L_UPDATE_WHERE_CLAUSE;
2409 L_TL_UPDATE_DML := L_TL_UPDATE_DML || L_UPDATE_WHERE_CLAUSE;
2410
2411 l_B_data_level_dml := l_B_data_level_dml || ', B.EXTENSION_ID ';
2412
2413 l_B_cursor_id:= DBMS_SQL.OPEN_CURSOR;
2414 DBMS_SQL.Parse(l_B_cursor_id, L_B_UPDATE_DML, DBMS_SQL.Native);
2415
2416 l_TL_cursor_id:= DBMS_SQL.OPEN_CURSOR;
2417 DBMS_SQL.Parse(l_TL_cursor_id, L_TL_UPDATE_DML, DBMS_SQL.Native);
2418
2419
2420 L_BIND_INDEX := L_BIND_INDEX +1;
2421 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE ||
2422 ' AND A.ATTR_GROUP_ID '||
2423 ' = :' ||L_BIND_INDEX;
2424
2425 l_bind_values.extend();
2426
2427 L_B_TEMP_WHERE_CLAUSE := L_B_WHERE_CLAUSE;
2428 L_B_TEMP_DATA_LEVEL_DML := l_B_data_level_dml;
2429
2430 WHILE (DBMS_SQL.Fetch_Rows(L_ATTR_CURSOR_ID) > 0)
2431 LOOP
2432 DBMS_SQL.Column_Value(L_ATTR_CURSOR_ID, 1, l_retrieved_value_char);
2433 l_curr_ag_metadata_obj :=
2434 EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(P_ATTR_GROUP_ID => to_number(l_retrieved_value_char)
2435 ,P_APPLICATION_ID => p_application_id
2436 ,P_ATTR_GROUP_TYPE => 'EGO_ITEMMGMT_GROUP'
2437 );
2438 l_attr_meta_data := l_curr_ag_metadata_obj.ATTR_METADATA_TABLE;
2439 IF L_B_WHERE_CLAUSE = L_B_TEMP_WHERE_CLAUSE
2440 THEN
2441 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE ||'-1,'|| l_retrieved_value_char;
2442 ELSE
2443 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE || ' , ' ||l_retrieved_value_char ;
2444 END IF;
2445
2446 L_B_WHERE_CLAUSE := L_B_TEMP_WHERE_CLAUSE;
2447 l_B_data_level_dml := L_B_TEMP_DATA_LEVEL_DML;
2448
2449 l_bind_values(l_bind_values.LAST) := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_GROUP_ID'
2450 ,to_number(l_retrieved_value_char));
2451
2452 FOR i IN 1 .. l_attr_meta_data.LAST
2453 LOOP
2454 IF l_attr_meta_data(i).UNIQUE_KEY_FLAG = 'Y'
2455 THEN
2456 L_B_WHERE_CLAUSE := L_B_TEMP_WHERE_CLAUSE
2457 || ' AND A.'|| l_attr_meta_data(i).DATABASE_COLUMN
2458 || ' = B.'||l_attr_meta_data(i).DATABASE_COLUMN ;
2459
2460 END IF;
2461 END LOOP;
2462
2463 l_B_data_level_dml := l_B_data_level_dml || L_B_WHERE_CLAUSE;
2464
2465 l_prod_cursor_id := DBMS_SQL.Open_Cursor;
2466 DBMS_SQL.Parse(l_prod_cursor_id, l_B_data_level_dml, DBMS_SQL.Native);
2467 DBMS_SQL.Describe_Columns(l_prod_cursor_id, l_column_count, l_desc_table);
2468
2469 FOR i IN 1 .. l_column_count
2470 LOOP
2471 DBMS_SQL.Define_Column(l_prod_cursor_id, i, l_retrieved_value_char, 1000);
2472
2473 END LOOP;
2474 FOR l_bind_index IN L_BIND_VALUES.FIRST .. L_BIND_VALUES.LAST
2475 LOOP
2476 DBMS_SQL.BIND_VARIABLE(l_prod_cursor_id, ':'||l_bind_index,L_BIND_VALUES(l_bind_index).VALUE);
2477
2478 END LOOP;
2479 l_dummy := DBMS_SQL.Execute(l_prod_cursor_id);
2480
2481
2482 WHILE (DBMS_SQL.Fetch_Rows(l_prod_cursor_id) > 0)
2483 LOOP
2484 FOR i IN 1 .. l_column_count
2485 LOOP
2486 DBMS_SQL.Column_Value(l_prod_cursor_id, i, l_retrieved_value_char);
2487 DBMS_SQL.BIND_VARIABLE(l_B_cursor_id, ':'||i,TO_NUMBER(l_retrieved_value_char));
2488 DBMS_SQL.BIND_VARIABLE(l_TL_cursor_id, ':'||i,TO_NUMBER(l_retrieved_value_char));
2489
2490 END LOOP;
2491 l_dummy := DBMS_SQL.Execute(l_B_cursor_id);
2492 L_dummy := DBMS_SQL.Execute(l_TL_cursor_id);
2493 END LOOP;
2494
2495 END LOOP;-- WHILE ATTR GROUP CURSOR
2496 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE || ')';
2497 L_ADDED_UPDATE_B_DML := L_ADDED_UPDATE_B_DML ||' '|| L_ADDED_WHERE_CLAUSE;
2498 L_ADDED_UPDATE_TL_DML := L_ADDED_UPDATE_TL_DML ||' ' || L_ADDED_WHERE_CLAUSE;
2499
2500 EXECUTE IMMEDIATE L_ADDED_UPDATE_B_DML;
2501 EXECUTE IMMEDIATE L_ADDED_UPDATE_TL_DML;
2502 END UPDATE_DATA_LEVEL;
2503
2504 PROCEDURE getValue(p_attrs_data_tbl IN EGO_USER_ATTR_DATA_TABLE
2505 ,x_rec_column OUT NOCOPY VARCHAR2
2506 ,p_attr_name IN VARCHAR2)
2507 IS
2508 BEGIN
2509 for i in p_attrs_data_tbl.FIRST .. p_attrs_data_tbl.LAST
2510 LOOP
2511 IF p_attrs_data_tbl(i).attr_name = p_attr_name
2512 then
2513 x_rec_column := p_attrs_data_tbl(i).attr_value_str;
2514 END IF;
2515 END LOOP;
2516
2517
2518 END getValue;
2519
2520 PROCEDURE getValue(p_attrs_data_tbl IN EGO_USER_ATTR_DATA_TABLE
2521 ,x_rec_column OUT NOCOPY NUMBER
2522 ,p_attr_name IN VARCHAR2)
2523 IS
2524 l_attr_name VARCHAR2(80);
2525 BEGIN
2526 for i in p_attrs_data_tbl.FIRST .. p_attrs_data_tbl.LAST
2527 LOOP
2528
2529 IF INSTR(p_attr_name,'UOM')>0
2530 THEN
2531 l_attr_name := SUBSTR(p_attr_name,INSTR(p_attr_name,'UOM')+4);
2532 ELSE
2533 l_attr_name := p_attr_name;
2534 END IF;
2535 IF p_attrs_data_tbl(i).attr_name = l_attr_name
2536 then
2537 IF INSTR(p_attr_name,'UOM')>0
2538 THEN
2539 x_rec_column := p_attrs_data_tbl(i).ATTR_UNIT_OF_MEASURE;
2540 ELSE
2541 x_rec_column := p_attrs_data_tbl(i).attr_value_num;
2542 END IF;
2543 END IF;
2544 END LOOP;
2545
2546 END getValue;
2547 PROCEDURE getValue(p_attrs_data_tbl IN EGO_USER_ATTR_DATA_TABLE
2548 ,x_rec_column OUT NOCOPY DATE
2549 ,p_attr_name IN VARCHAR2)
2550 IS
2551 BEGIN
2552 for i in p_attrs_data_tbl.FIRST .. p_attrs_data_tbl.LAST
2553 LOOP
2554 IF p_attrs_data_tbl(i).attr_name = p_attr_name
2555 then
2556 x_rec_column := p_attrs_data_tbl(i).attr_value_date;
2557 END IF;
2558 END LOOP;
2559
2560 END getValue;
2561
2562
2563
2564 PROCEDURE GET_ATTR_GRP_VO_DEF
2565 (
2566 p_change_attr_group_type IN VARCHAR2
2567 ,p_object_name IN VARCHAR2
2568 ,p_application_short_name IN VARCHAR2
2569 ,x_vo_def OUT NOCOPY VARCHAR2
2570 )
2571 IS
2572 BEGIN
2573 select change_vo_def_name into x_vo_def
2574 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2575 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2576 and fnd_obj.obj_name = p_object_name
2577 and fnd_appl.application_short_name = p_application_short_name;
2578
2579 END GET_ATTR_GRP_VO_DEF;
2580
2581 PROCEDURE GET_ATTR_GRP_VO_INSTANCE
2582 (
2583 p_change_attr_group_type IN VARCHAR2
2584 ,p_object_name IN VARCHAR2
2585 ,p_application_short_name IN VARCHAR2
2586 ,x_vo_instance OUT NOCOPY VARCHAR2
2587 )
2588 IS
2589 BEGIN
2590 select change_vo_inst_name into x_vo_instance
2591 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2592 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2593 and fnd_obj.obj_name = p_object_name
2594 and fnd_appl.application_short_name = p_application_short_name;
2595
2596 END GET_ATTR_GRP_VO_INSTANCE;
2597
2598 PROCEDURE GET_ATTR_GRP_VO_ROW_CLASS
2599 (
2600 p_change_attr_group_type IN VARCHAR2
2601 ,p_object_name IN VARCHAR2
2602 ,p_application_short_name IN VARCHAR2
2603 ,x_vo_row_class OUT NOCOPY VARCHAR2
2604 )
2605 IS
2606 BEGIN
2607 select change_vo_row_class_name into x_vo_row_class
2608 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2609 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2610 and fnd_obj.obj_name = p_object_name
2611 and fnd_appl.application_short_name = p_application_short_name;
2612
2613 END GET_ATTR_GRP_VO_ROW_CLASS;
2614
2615 PROCEDURE GET_ATTR_GRP_EO_DEF
2616 (
2617 p_change_attr_group_type IN VARCHAR2
2618 ,p_object_name IN VARCHAR2
2619 ,p_application_short_name IN VARCHAR2
2620 ,x_eo_def OUT NOCOPY VARCHAR2
2621 )
2622 IS
2623 BEGIN
2624 select change_eo_def_name into x_eo_def
2625 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2626 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2627 and fnd_obj.obj_name = p_object_name
2628 and fnd_appl.application_short_name = p_application_short_name;
2629
2630 END GET_ATTR_GRP_EO_DEF;
2631
2632 PROCEDURE GET_ATTR_GRP_BASE_TABLE
2633 (
2634 p_change_attr_group_type IN VARCHAR2
2635 ,p_object_name IN VARCHAR2
2636 ,p_application_short_name IN VARCHAR2
2637 ,x_base_table OUT NOCOPY VARCHAR2
2638 )
2639 IS
2640 BEGIN
2641 select change_b_table_name into x_base_table
2642 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2643 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2644 and fnd_obj.obj_name = p_object_name
2645 and fnd_appl.application_short_name = p_application_short_name;
2646
2647 END GET_ATTR_GRP_BASE_TABLE;
2648
2649 PROCEDURE GET_ATTR_GRP_TL_TABLE
2650 (
2651 p_change_attr_group_type IN VARCHAR2
2652 ,p_object_name IN VARCHAR2
2653 ,p_application_short_name IN VARCHAR2
2654 ,x_tl_table OUT NOCOPY VARCHAR2
2655 )
2656 IS
2657 BEGIN
2658 select change_tl_table_name into x_tl_table
2659 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2660 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2661 and fnd_obj.obj_name = p_object_name
2662 and fnd_appl.application_short_name = p_application_short_name;
2663
2664 END GET_ATTR_GRP_TL_TABLE;
2665
2666 PROCEDURE GET_ATTR_GRP_VL_NAME
2667 (
2668 p_change_attr_group_type IN VARCHAR2
2669 ,p_object_name IN VARCHAR2
2670 ,p_application_short_name IN VARCHAR2
2671 ,x_vl_name OUT NOCOPY VARCHAR2
2672 )
2673 IS
2674 BEGIN
2675 select change_vl_table_name into x_vl_name
2676 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2677 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2678 and fnd_obj.obj_name = p_object_name
2679 and fnd_appl.application_short_name = p_application_short_name;
2680
2681 END GET_ATTR_GRP_VL_NAME;
2682
2683 PROCEDURE GET_CONTEXT_VALUE
2684 (
2685 p_change_attr_group_type IN VARCHAR2
2686 ,p_object_name IN VARCHAR2
2687 ,p_application_short_name IN VARCHAR2
2688 ,p_context_type IN VARCHAR2 -- column name in the eng_pending_change_ctx table
2689 ,x_context_value OUT NOCOPY VARCHAR2
2690 )
2691 IS
2692 BEGIN
2693
2694 CASE p_context_type
2695 WHEN 'CHANGE_B_TABLE_NAME' THEN
2696 select change_b_table_name into x_context_value
2697 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2698 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2699 and fnd_obj.obj_name = p_object_name
2700 and fnd_appl.application_short_name = p_application_short_name;
2701 WHEN 'CHANGE_TL_TABLE_NAME' THEN
2702 select change_tl_table_name into x_context_value
2703 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2704 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2705 and fnd_obj.obj_name = p_object_name
2706 and fnd_appl.application_short_name = p_application_short_name;
2707 WHEN 'CHANGE_VL_TABLE_NAME' THEN
2708 select change_vl_table_name into x_context_value
2709 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2710 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2711 and fnd_obj.obj_name = p_object_name
2712 and fnd_appl.application_short_name = p_application_short_name;
2713 WHEN 'CHANGE_VO_DEF_NAME' THEN
2714 select change_vo_def_name into x_context_value
2715 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2716 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2717 and fnd_obj.obj_name = p_object_name
2718 and fnd_appl.application_short_name = p_application_short_name;
2719 WHEN 'CHANGE_VO_INST_NAME' THEN
2720 select change_vo_inst_name into x_context_value
2721 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2722 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2723 and fnd_obj.obj_name = p_object_name
2724 and fnd_appl.application_short_name = p_application_short_name;
2725 WHEN 'CHANGE_VO_ROW_CLASS_NAME' THEN
2726 select change_vo_row_class_name into x_context_value
2727 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2728 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2729 and fnd_obj.obj_name = p_object_name
2730 and fnd_appl.application_short_name = p_application_short_name;
2731 WHEN 'CHANGE_EO_DEF_NAME' THEN
2732 select change_eo_def_name into x_context_value
2733 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2734 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2735 and fnd_obj.obj_name = p_object_name
2736 and fnd_appl.application_short_name = p_application_short_name;
2737 END CASE;
2738
2739 END GET_CONTEXT_VALUE;
2740
2741 PROCEDURE DEL_PEND_ATTR_CHGS
2742 (
2743 P_MODE IN VARCHAR2
2744 ,P_CHANGE_ID IN NUMBER
2745 ,P_CHANGE_LINE_ID IN NUMBER
2746 ,P_ORG_ID IN NUMBER
2747 ,P_DATA_LEVEL_NAME IN VARCHAR2
2748 ,P_DATA_LEVEL_NAME_VALUE_PAIRS IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
2749 )
2750 IS
2751 l_dynamic_sql VARCHAR2(4000);
2752 l_data_level_id NUMBER;
2753 l_data_level_pk1 NUMBER := -1;
2754 l_data_level_pk2 NUMBER := -1;
2755 l_data_level_pk3 NUMBER := -1;
2756 l_data_level_pk4 NUMBER := -1;
2757 l_data_level_pk5 NUMBER := -1;
2758 CURSOR c_getDataLevelId(dataLevelName IN VARCHAR2) IS
2759 SELECT DATA_LEVEL_ID FROM EGO_DATA_LEVEL_B WHERE DATA_LEVEL_NAME = dataLevelName;
2760 BEGIN
2761 l_dynamic_sql := 'WHERE CHANGE_ID= :1 '
2762 || 'AND CHANGE_LINE_ID= :2 ';
2763
2764 --pass p_mode as LINE to delete pending changes for revised item
2765 --pass p_mode as ASSOC to delete pending changes for the association
2766
2767 IF P_MODE = 'ASSOC' THEN
2768 l_dynamic_sql := l_dynamic_sql || 'AND ORGANIZATION_ID= :3 '
2769 || 'AND DATA_LEVEL_ID = :4 '
2770 || 'AND NVL(PK1_VALUE, -1) = :5 '
2771 || 'AND NVL(PK2_VALUE, -1) = :6 '
2772 || 'AND NVL(PK3_VALUE, -1) = :7 '
2773 || 'AND NVL(PK4_VALUE, -1) = :8 '
2774 || 'AND NVL(PK5_VALUE, -1) = :9 ';
2775
2776 OPEN c_getDataLevelId(P_DATA_LEVEL_NAME);
2777 FETCH c_getDataLevelId INTO l_data_level_id;
2778 CLOSE c_getDataLevelId;
2779
2780 IF p_data_level_name_value_pairs is NOT NULL AND p_data_level_name_value_pairs.COUNT>0
2781 THEN
2782 FOR data_index IN p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
2783 LOOP
2784 IF (p_data_level_name_value_pairs(data_index) IS NOT NULL AND p_data_level_name_value_pairs(data_index).value IS NOT NULL) THEN
2785 IF (data_index = 1) THEN
2786 l_data_level_pk1 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2787 ELSIF (data_index = 2) THEN
2788 l_data_level_pk2 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2789 ELSIF (data_index = 3) THEN
2790 l_data_level_pk3 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2791 ELSIF (data_index = 4) THEN
2792 l_data_level_pk4 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2793 ELSIF (data_index = 5) THEN
2794 l_data_level_pk5 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2795 END IF;
2796 END IF;
2797 END LOOP;
2798 END IF;
2799 EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_B '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID, P_ORG_ID,
2800 l_data_level_id, l_data_level_pk1, l_data_level_pk2,
2801 l_data_level_pk3, l_data_level_pk4, l_data_level_pk5;
2802 EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_TL '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID, P_ORG_ID,
2803 l_data_level_id, l_data_level_pk1, l_data_level_pk2,
2804 l_data_level_pk3, l_data_level_pk4, l_data_level_pk5;
2805 ELSIF P_MODE = 'LINE' THEN
2806 EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_B '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID;
2807 EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_TL '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID;
2808 END IF;
2809
2810 END DEL_PEND_ATTR_CHGS;
2811
2812 PROCEDURE SAVE_ITEM_NUM_DESC(P_CHANGE_ID IN NUMBER
2813 , P_CHANGE_LINE_ID IN NUMBER
2814 , P_ORGANIZATION_ID IN NUMBER
2815 , P_ITEM_ID IN NUMBER
2816 , P_ITEM_NUM IN VARCHAR2 DEFAULT NULL
2817 , P_ITEM_DESC IN VARCHAR2 DEFAULT NULL
2818 , p_transaction_mode IN VARCHAR2
2819 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
2820 )
2821 IS
2822 CURSOR langauges is
2823 SELECT LANGS.LANGUAGE_CODE
2824 FROM FND_LANGUAGES LANGS
2825 WHERE LANGS.installed_flag IN ('B','I');
2826
2827 BEGIN
2828
2829 x_return_status := FND_API.G_RET_STS_SUCCESS;
2830 if 'CREATE' = p_transaction_mode
2831 then
2832 insert into EGO_MTL_SY_ITEMS_CHG_B(
2833 INVENTORY_ITEM_ID,
2834 ORGANIZATION_ID,
2835 CHANGE_ID,
2836 CHANGE_LINE_ID,
2837 ACD_TYPE,
2838 CREATED_BY,
2839 CREATION_DATE,
2840 LAST_UPDATED_BY,
2841 LAST_UPDATE_LOGIN,
2842 LAST_UPDATE_DATE,
2843 DESCRIPTION,
2844 ITEM_NUMBER)
2845 values
2846 (
2847 p_item_id,
2848 p_organization_id,
2849 p_change_id,
2850 p_change_line_id,
2851 'CHANGE',
2852 FND_GLOBAL.user_id,
2853 SYSDATE,
2854 FND_GLOBAL.user_id,
2855 FND_GLOBAL.user_id,
2856 SYSDATE,
2857 P_ITEM_DESC,
2858 P_ITEM_NUM
2859 );
2860 FOR LANG_CODE IN langauges
2861 loop
2862 insert into EGO_MTL_SY_ITEMS_CHG_TL(
2863 INVENTORY_ITEM_ID,
2864 ORGANIZATION_ID,
2865 CHANGE_ID,
2866 CHANGE_LINE_ID,
2867 ACD_TYPE,
2868 CREATED_BY,
2869 CREATION_DATE,
2870 LAST_UPDATED_BY,
2871 LAST_UPDATE_LOGIN,
2872 LAST_UPDATE_DATE,
2873 LANGUAGE,
2874 SOURCE_LANG
2875 )
2876 values
2877 (
2878 p_item_id,
2879 p_organization_id,
2880 p_change_id,
2881 p_change_line_id,
2882 'CHANGE',
2883 FND_GLOBAL.user_id,
2884 SYSDATE,
2885 FND_GLOBAL.user_id,
2886 FND_GLOBAL.user_id,
2887 SYSDATE,
2888 LANG_CODE.LANGUAGE_CODE,
2889 USERENV('LANG')
2890 );
2891 end loop;
2892 ELSIF 'UPDATE' = p_transaction_mode
2893 THEN
2894
2895 UPDATE EGO_MTL_SY_ITEMS_CHG_B
2896 SET DESCRIPTION = p_item_desc,
2897 ITEM_NUMBER = p_item_num
2898 where change_line_id = p_change_line_id;
2899
2900 END IF;
2901
2902
2903 END SAVE_ITEM_NUM_DESC;
2904
2905 END ENG_CHANGE_ATTR_UTIL;