[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ATTR_UTIL
Source
1 PACKAGE BODY ENG_CHANGE_ATTR_UTIL AS
2 /* $Header: ENGVCAUB.pls 120.45 2007/07/12 19:20:17 asjohal 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
712 END IF;
713 END IF;
714 IF g_debug_flag THEN
715 Write_Debug('Closing Debug Session: '
716 || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
717 Close_Debug_Session ;
718 END IF;
719 END UPDATE_ITEM_ATTRS;
720
721 PROCEDURE VALIDATE_USER_ATTRS
722 (
723 p_api_version IN NUMBER
724 ,p_object_name IN VARCHAR2
725 ,p_attr_group_id IN NUMBER
726 ,p_attr_group_type IN VARCHAR2
727 ,p_application_id IN NUMBER
728 ,p_attr_group_name IN VARCHAR2
729 ,p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE
730 ,p_extension_id IN NUMBER
731 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
732 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
733 ,p_extra_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
734 ,p_extra_attr_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
735 ,p_alternate_ext_b_table_name IN VARCHAR2 DEFAULT NULL
736 ,p_alternate_ext_tl_table_name IN VARCHAR2 DEFAULT NULL
737 ,p_alternate_ext_vl_name IN VARCHAR2 DEFAULT NULL
738 ,p_user_privileges_on_object IN EGO_VARCHAR_TBL_TYPE DEFAULT NULL
739 ,p_row_identifier IN NUMBER DEFAULT NULL
740 ,p_validate_only IN VARCHAR2
741 ,p_mode IN VARCHAR2
742 ,p_acd_type IN VARCHAR2
743 ,p_init_fnd_msg_list IN VARCHAR2
744 ,p_add_errors_to_fnd_stack IN VARCHAR2
745 ,x_return_status OUT NOCOPY VARCHAR2
746 ,x_errorcode OUT NOCOPY NUMBER
747 ,x_msg_count OUT NOCOPY NUMBER
748 ,x_msg_data OUT NOCOPY VARCHAR2
749 ,p_key_attr_upd IN VARCHAR2
750 ,p_data_level_name IN VARCHAR2
751 ,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
752
753 )
754 IS
755 l_failed_row_id_list VARCHAR2(3200);
756 l_exist_extension_id NUMBER;
757 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
758 l_row_identifier NUMBER ;
759 l_curr_ag_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
760 l_attr_meta_data EGO_ATTR_METADATA_TABLE;
761 l_attr_data_table EGO_USER_ATTR_DATA_TABLE;
762 l_mode VARCHAR2(10);
763 l_extra_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
764 l_delete_index NUMBER;
765 l_attr_db_col VARCHAR2(30);
766 l_retrieved_value_char VARCHAR2(4000);
767 l_retrieved_value_num NUMBER;
768 l_retrieved_value_date DATE;
769 l_bind_index NUMBER;
770 l_bind_values EGO_USER_ATTR_DATA_TABLE;
771 l_dynamic_sql VARCHAR2(20000);
772 l_column_count NUMBER;
773 l_uk_where_clause VARCHAR2(4000):= null;
774 l_cursor_id NUMBER;
775 l_desc_table DBMS_SQL.Desc_Tab;
776 l_dummy NUMBER;
777 L_CACHED_SQL_FOUND VARCHAR2(1) := 'N';
778 p_prod_vl_name VARCHAR2(40);
779 l_DataLevelColumnExists VARCHAR2(5);
780 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
781 CURSOR C_DATALEVEL_COLUMN_EXISTS(TABLENAME IN VARCHAR2) IS
782 SELECT 'Y' FROM FND_TABLES FT,FND_COLUMNS FC
783 WHERE FT.TABLE_NAME = UPPER(TABLENAME) AND FT.TABLE_ID = FC.TABLE_ID
784 AND COLUMN_NAME = 'DATA_LEVEL_ID';
785
786 BEGIN
787
788
789 x_return_status := FND_API.G_RET_STS_SUCCESS ;
790 -- get the Production vl name.
791 BEGIN
792 SELECT FLEX_EXT.APPLICATION_VL_NAME
793 INTO p_prod_vl_name
794 FROM FND_DESCRIPTIVE_FLEXS FLEX,
795 EGO_FND_DESC_FLEXS_EXT FLEX_EXT
796 WHERE FLEX.APPLICATION_ID = FLEX_EXT.APPLICATION_ID(+)
797 AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = FLEX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+)
798 AND FLEX.APPLICATION_ID = p_application_id
799 AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type ;
800 END ;
801
802 l_DataLevelColumnExists := 'N';
803 OPEN C_DATALEVEL_COLUMN_EXISTS(p_alternate_ext_b_table_name);
804 FETCH C_DATALEVEL_COLUMN_EXISTS into l_DataLevelColumnExists;
805 --prg_debug('Add datalevel column='||l_DataLevelColumnExists);
806
807 IF p_row_identifier IS NULL
808 THEN
809 -- Set Default as 1
810 l_row_identifier := 1 ;
811 ELSE
812 l_row_identifier := p_row_identifier ;
813 END IF ;
814
815 l_curr_ag_metadata_obj :=
816 EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(P_ATTR_GROUP_ID => P_ATTR_GROUP_ID
817 ,P_APPLICATION_ID => p_application_id
818 ,P_ATTR_GROUP_TYPE => p_attr_group_type
819 ,P_ATTR_GROUP_NAME => p_attr_group_name);
820
821 -- Uniqueness check required for the multi row attr groups.
822
823 if l_curr_ag_metadata_obj.MULTI_ROW_CODE = 'Y'
824 THEN
825 l_attr_meta_data := l_curr_ag_metadata_obj.ATTR_METADATA_TABLE;
826
827 -- Get cached sql for the attribute group.
828 IF G_CACHED_PLSQL_TABLE.EXISTS(P_ATTR_GROUP_ID)
829
830 THEN
831 l_dynamic_sql := G_CACHED_PLSQL_TABLE(P_ATTR_GROUP_ID).attr_group_sql;
832 L_CACHED_SQL_FOUND := 'Y';
833 END IF;
834
835
836 /* if cached sql does not exist then create sql query using
837 a merged record created using production and pending table
838 having the key attibutes in select and where condition
839 with a join using EXTENSION_ID AND PRIMARY KEYS AND a
840 condition to get rows other than the current EXTENSION_ID
841
842
843 */
844 if L_CACHED_SQL_FOUND <>'Y' THEN
845 l_dynamic_sql := 'SELECT ';
846 END IF;
847 for i in l_attr_meta_data.first .. l_attr_meta_data.last
848 LOOP
849
850 if l_attr_meta_data(i).UNIQUE_KEY_FLAG = 'Y' AND L_CACHED_SQL_FOUND <> 'Y'
851 THEN
852
853 if l_dynamic_sql <> 'SELECT ' THEN
854 l_dynamic_sql := l_dynamic_sql ||', ';
855 END IF;
856
857 l_attr_db_col := l_attr_meta_data(i).DATABASE_COLUMN;
858
859 l_dynamic_sql := l_dynamic_sql || ' DECODE(PEND.'||l_attr_db_col ||
860 ',null, PROD.'||l_attr_db_col ||
861 ',DECODE(PEND.'||l_attr_db_col||
862 ',';
863 IF l_attr_meta_data(i).DATA_TYPE_CODE ='A' OR l_attr_meta_data(i).DATA_TYPE_CODE = 'C'
864 THEN
865 l_dynamic_sql := l_dynamic_sql ||'''' || ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_CHAR ||'''';
866
867 ELSIF l_attr_meta_data(i).DATA_TYPE_CODE = 'N'
868 THEN
869 l_dynamic_sql := l_dynamic_sql || TO_CHAR(ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_NUM);
870
871 ELSIF l_attr_meta_data(i).DATA_TYPE_CODE = 'X' OR l_attr_meta_data(i).DATA_TYPE_CODE = 'Y'
872 THEN
873 l_dynamic_sql := l_dynamic_sql || ' ''' || TO_CHAR(ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_DATE) || '''';
874 END IF;
875
876 l_dynamic_sql := l_dynamic_sql || ', NULL , PEND.'|| l_attr_db_col || ')) '|| l_attr_db_col;
877
878 END IF;
879
880
881 if i = l_attr_meta_data.last
882
883 THEN
884 l_bind_index := 1;
885 l_bind_values := EGO_USER_ATTR_DATA_TABLE();
886 IF L_CACHED_SQL_FOUND <> 'Y' THEN
887 IF l_dynamic_sql = 'SELECT ' THEN
888 l_dynamic_sql := l_dynamic_sql || ' PEND.EXTENSION_ID ';
889 ELSE
890 l_dynamic_sql := l_dynamic_sql || ', PEND.EXTENSION_ID ';
891 END IF;
892 l_dynamic_sql := l_dynamic_sql || ' FROM '|| p_alternate_ext_vl_name ||' PEND , '|| p_prod_vl_name||' PROD';
893 IF l_DataLevelColumnExists = 'Y' THEN l_dynamic_sql := l_dynamic_sql || ' , ego_data_level_b DATA_LEVELS ';
894 END IF;
895 l_dynamic_sql := l_dynamic_sql ||' WHERE PEND.EXTENSION_ID = PROD.EXTENSION_ID '||
896 ' AND PEND.EXTENSION_ID <> :1' ;
897 END IF;
898 l_bind_values.extend();
899 l_bind_values(l_bind_values.LAST) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
900 ,null
901 ,null
902 ,p_extension_id
903 ,null
904 ,null
905 ,null
906 ,1);
907 for pk_index in p_pk_column_name_value_pairs.FIRST .. p_pk_column_name_value_pairs.LAST
908 LOOP
909 l_bind_index := l_bind_index +1;
910 IF L_CACHED_SQL_FOUND <> 'Y' THEN
911 l_dynamic_sql := l_dynamic_sql || ' AND PEND.'||
912 p_pk_column_name_value_pairs(pk_index).NAME ||' = :' ||l_bind_index ;
913 END IF;
914 l_bind_values.extend();
915 l_bind_values(l_bind_values.LAST) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
916 ,null
917 ,null
918 ,to_number(p_pk_column_name_value_pairs(pk_index).VALUE)
919 ,null
920 ,null
921 ,null
922 ,1);
923
924 END LOOP;
925 IF (l_DataLevelColumnExists = 'Y') THEN
926 if p_data_level_name is not null
927 then
928 l_bind_index := l_bind_index +1;
929 IF L_CACHED_SQL_FOUND <> 'Y' then
930 l_dynamic_sql := l_dynamic_sql || ' AND PEND.DATA_LEVEL_ID = DATA_LEVELS.DATA_LEVEL_ID'
931 ||' AND DATA_LEVELS.APPLICATION_ID = ' || P_APPLICATION_ID
932 ||' AND DATA_LEVELS.ATTR_GROUP_TYPE = '''||p_attr_group_type||''''
933 ||' AND DATA_LEVELS.DATA_LEVEL_NAME = :' ||l_bind_index ;
934 END IF;
935 l_bind_values.extend();
936 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
937 ,null
938 ,P_DATA_LEVEL_NAME
939 ,NULL
940 ,null
941 ,null
942 ,null
943 ,-1);
944
945 end if ; -- p_data_level_name is not null;
946 END IF; -- l_DataLevelColumnExists is 'Y'
947 IF p_extra_pk_col_name_val_pairs is NOT NULL AND p_extra_pk_col_name_val_pairs.COUNT>0
948 THEN
949 FOR pk_extra_index in p_extra_pk_col_name_val_pairs.FIRST .. p_extra_pk_col_name_val_pairs.LAST
950 LOOP
951 IF p_extra_pk_col_name_val_pairs(pk_extra_index).NAME <> 'ACD_TYPE'
952 THEN
953 l_bind_index := l_bind_index +1;
954 IF L_CACHED_SQL_FOUND <> 'Y' THEN
955 l_dynamic_sql := l_dynamic_sql || ' AND PEND.'||
956 p_extra_pk_col_name_val_pairs(pk_extra_index).NAME ||' = :' ||l_bind_index ;
957 END IF;
958 l_bind_values.extend();
959 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
960 ,null
961 ,null
962 ,to_number(p_extra_pk_col_name_val_pairs(pk_extra_index).VALUE)
963 ,null
964 ,null
965 ,null
966 ,-1);
967 END IF;
968 END LOOP;
969 END IF;
970 END IF;
971
972 END LOOP;
973
974 /* The final query would actualy select EXTENSION_ID from the
975 previous query using alais 'dy_sql' and would filter the rows
976 based on the key attr values of the current row being valildated.
977 */
978 l_uk_where_clause := ' 1=1 ';
979 for j in l_attr_meta_data.first .. l_attr_meta_data.last
980 LOOP
981 if l_attr_meta_data(j).UNIQUE_KEY_FLAG = 'Y'
982 THEN
983
984 IF l_uk_where_clause IS NOT NULL AND L_CACHED_SQL_FOUND <> 'Y'
985 THEN
986 l_uk_where_clause := l_uk_where_clause || ' AND ' ;
987 ELSIF l_uk_where_clause IS NULL AND L_CACHED_SQL_FOUND <> 'Y' THEN
988 l_uk_where_clause := ' WHERE ';
989 END IF;
990
991
992 FOR p_attr_index in p_attributes_data_table.FIRST .. p_attributes_data_table.LAST
993 LOOP
994 IF p_attributes_data_table(p_attr_index).ATTR_NAME = l_attr_meta_data(j).ATTR_NAME
995 AND (l_attr_meta_data(j).DATA_TYPE_CODE ='A' OR l_attr_meta_data(j).DATA_TYPE_CODE = 'C')
996 THEN
997 --if p_attributes_data_table(p_attr_index).ATTR_VALUE_STR IS NOT NULL
998 --THEN
999 l_bind_index := l_bind_index +1;
1000 l_bind_values.extend();
1001 IF L_CACHED_SQL_FOUND <> 'Y' THEN
1002 l_uk_where_clause := l_uk_where_clause || 'NVL ( PEND.'||l_attr_meta_data(j).DATABASE_COLUMN ||',''-1'') = NVL(:'|| l_bind_index||',''-1'')';
1003 END IF;
1004 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
1005 ,null
1006 ,p_attributes_data_table(p_attr_index).ATTR_VALUE_STR
1007 ,NULL
1008 ,null
1009 ,null
1010 ,null
1011 ,-1);
1012 -- ELSE
1013 -- l_uk_where_clause := l_uk_where_clause || l_attr_meta_data(j).DATABASE_COLUMN ||' IS NULL';
1014 -- END IF;
1015 EXIT;
1016 ELSIF p_attributes_data_table(p_attr_index).ATTR_NAME = l_attr_meta_data(j).ATTR_NAME
1017 AND l_attr_meta_data(j).DATA_TYPE_CODE = 'N'
1018 THEN
1019 --if p_attributes_data_table(p_attr_index).ATTR_VALUE_NUM IS NOT NULL
1020 -- THEN
1021 l_bind_index := l_bind_index +1;
1022 l_bind_values.extend();
1023 IF L_CACHED_SQL_FOUND <> 'Y' THEN
1024 l_uk_where_clause := l_uk_where_clause || ' NVL(PEND.'|| l_attr_meta_data(j).DATABASE_COLUMN || ',-1) = NVL(:'|| l_bind_index || ', -1)';
1025 END IF;
1026 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
1027 ,null
1028 ,null
1029 ,p_attributes_data_table(p_attr_index).ATTR_VALUE_NUM
1030 ,null
1031 ,null
1032 ,null
1033 ,-1);
1034 --ELSE
1035 -- l_uk_where_clause := l_uk_where_clause || ' ' || l_attr_meta_data(j).DATABASE_COLUMN ||' IS NULL';
1036 --END IF;
1037 EXIT;
1038 ELSIF p_attributes_data_table(p_attr_index).ATTR_NAME = l_attr_meta_data(j).ATTR_NAME
1039 AND (l_attr_meta_data(j).DATA_TYPE_CODE ='X' OR l_attr_meta_data(j).DATA_TYPE_CODE = 'Y')
1040 THEN
1041 -- if p_attributes_data_table(p_attr_index).ATTR_VALUE_DATE IS NOT NULL
1042 -- THEN
1043 l_bind_index := l_bind_index +1;
1044 l_bind_values.extend();
1045 IF L_CACHED_SQL_FOUND <> 'Y' THEN
1046 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''))';
1047 END IF;
1048 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
1049 ,null
1050 ,null
1051 ,NULL
1052 ,p_attributes_data_table(p_attr_index).ATTR_VALUE_DATE
1053 ,null
1054 ,null
1055 ,-1);
1056 -- ELSE
1057 -- l_uk_where_clause := l_uk_where_clause || ' ' || l_attr_meta_data(j).DATABASE_COLUMN ||' IS NULL';
1058 --END IF;
1059 EXIT;
1060 END IF;
1061 END LOOP;
1062 END IF;
1063 END LOOP;
1064 IF L_CACHED_SQL_FOUND <> 'Y' THEN
1065
1066 l_dynamic_sql := l_dynamic_sql || ' AND '|| l_uk_where_clause;
1067
1068 G_CACHED_PLSQL_TABLE(P_ATTR_GROUP_ID).ATTR_GROUP_ID := P_ATTR_GROUP_ID;
1069 G_CACHED_PLSQL_TABLE(P_ATTR_GROUP_ID).ATTR_GROUP_SQL := l_dynamic_sql;
1070 END IF;
1071
1072 IF p_data_level_name_value_pairs is NOT NULL AND p_data_level_name_value_pairs.COUNT>0
1073 THEN
1074 for data_index in p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
1075 LOOP
1076 l_bind_index := l_bind_index +1;
1077 l_dynamic_sql := l_dynamic_sql || ' AND PEND.'||
1078 p_data_level_name_value_pairs(data_index).NAME ||' = :' ||l_bind_index ;
1079 l_bind_values.extend();
1080 l_bind_values(l_bind_values.last) := EGO_USER_ATTR_DATA_OBJ(l_bind_index
1081 ,null
1082 ,null
1083 ,to_number(p_data_level_name_value_pairs(data_index).VALUE)
1084 ,null
1085 ,null
1086 ,null
1087 ,-1);
1088
1089 END LOOP;
1090 END IF;
1091
1092
1093 l_cursor_id := DBMS_SQL.Open_Cursor;
1094 -- prg_debug(l_dynamic_sql);
1095 -- prg_debug(l_bind_index);
1096 DBMS_SQL.Parse(l_cursor_id, l_dynamic_sql, DBMS_SQL.Native);
1097 DBMS_SQL.Describe_Columns(l_cursor_id, l_column_count, l_desc_table);
1098 FOR i IN 1 .. l_column_count
1099 LOOP
1100
1101 DBMS_SQL.Define_Column(l_cursor_id, i, l_retrieved_value_char, 1000);
1102
1103 END LOOP;
1104
1105 FOR l_bind_index IN l_bind_values.FIRST .. l_bind_values.LAST
1106 LOOP
1107 IF ( l_bind_values(l_bind_index).ATTR_VALUE_STR is not NULL)
1108 THEN
1109 -- prg_debug(l_bind_values(l_bind_index).ATTR_VALUE_STR);
1110 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':'||l_bind_index,l_bind_values(l_bind_index).ATTR_VALUE_STR);
1111 ELSIF (l_bind_values(l_bind_index).ATTR_VALUE_NUM is not NULL)
1112 THEN
1113 -- prg_debug(l_bind_values(l_bind_index).ATTR_VALUE_NUM);
1114 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':'||l_bind_index,l_bind_values(l_bind_index).ATTR_VALUE_NUM);
1115 ELSIF (l_bind_values(l_bind_index).ATTR_VALUE_DATE is not NULL)
1116 THEN
1117 --prg_debug(l_bind_values(l_bind_index).ATTR_VALUE_DATE);
1118 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':'||l_bind_index,l_bind_values(l_bind_index).ATTR_VALUE_DATE);
1119 ELSE
1120 -- prg_debug(l_bind_values(l_bind_index).ATTR_VALUE_STR);
1121 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':'||l_bind_index,l_bind_values(l_bind_index).ATTR_VALUE_STR);
1122 END IF;
1123
1124 END LOOP;
1125
1126
1127
1128 l_dummy := DBMS_SQL.Execute(l_cursor_id);
1129
1130 if ( DBMS_SQL.Fetch_Rows(l_cursor_id) > 0)
1131 THEN
1132 x_return_status := FND_API.G_RET_STS_ERROR;
1133 ERROR_HANDLER.Initialize();
1134 ERROR_HANDLER.Set_Bo_Identifier(EGO_USER_ATTRS_DATA_PVT.G_BO_IDENTIFIER);
1135 ERROR_HANDLER.Add_Error_Message(
1136 p_message_name => 'EGO_EF_ROW_ALREADY_EXISTS'
1137 ,p_application_id => 'EGO'
1138 ,p_message_type => FND_API.G_RET_STS_ERROR
1139 ,p_addto_fnd_stack => 'Y'
1140 );
1141
1142 x_msg_count := ERROR_HANDLER.Get_Message_Count();
1143 IF (x_msg_count = 1) THEN
1144 DECLARE
1145 message_list ERROR_HANDLER.Error_Tbl_Type;
1146 BEGIN
1147 ERROR_HANDLER.Get_Message_List(message_list);
1148 x_msg_data := message_list(message_list.FIRST).message_text;
1149 END;
1150 ELSE
1151 x_msg_data := NULL;
1152 END IF;
1153
1154 END IF;
1155 END IF ; -- attribute group is MULTI ROW.
1156
1157 -- if the row is unique in pending validate it against the production data.
1158
1159 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1160 THEN
1161
1162 IF g_debug_flag THEN
1163 Write_Debug('Start VALIDATE_USER_ATTRS');
1164 END IF ;
1165
1166
1167 /* IF p_data_level_name_value_pairs.FIRST is NOT NULL
1168 THEN*/
1169
1170 IF g_debug_flag THEN
1171 Write_Debug('p_data_level_name_value_pairs.FIRST is NOT NULL');
1172
1173 END IF ;
1174
1175 if p_acd_type = 'ADD'
1176 then
1177 l_mode := 'CREATE';
1178 ELSIF p_acd_type = 'CHANGE' AND p_key_attr_upd = 'Y'
1179 then
1180 l_mode := 'CREATE';
1181 ELSIF p_acd_type = 'CHANGE' AND p_key_attr_upd = 'N'
1182 then
1183 l_mode := 'UPDATE';
1184 END IF;
1185
1186 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE
1187 (
1188 EGO_USER_ATTR_ROW_OBJ( l_row_identifier
1189 , p_attr_group_id
1190 , p_application_id
1191 , p_attr_group_type
1192 , null
1193 , p_data_level_name
1194 , null
1195 , null
1196 , null
1197 , null
1198 , null
1199 ,l_mode)
1200
1201 );
1202
1203 if p_data_level_name_value_pairs is not null
1204 then
1205 for i in p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
1206 LOOP
1207 if i=1
1208 then
1209 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_1 :=
1210 p_data_level_name_value_pairs(i).VALUE ;
1211 elsif i=2
1212 then
1213 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_2 :=
1214 p_data_level_name_value_pairs(i).VALUE;
1215 elsif i=3
1216 then
1217 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_3 :=
1218 p_data_level_name_value_pairs(i).VALUE ;
1219 elsif i=4
1220 then
1221 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_4 :=
1222 p_data_level_name_value_pairs(i).VALUE ;
1223 elsif i=5
1224 then
1225 l_attributes_row_table(l_attributes_row_table.FIRST).data_level_5 :=
1226 p_data_level_name_value_pairs(i).VALUE ;
1227 end if;
1228 end loop;
1229 end if;
1230
1231 /* ELSE
1232
1233 IF g_debug_flag THEN
1234 Write_Debug('p_data_level_name_value_pairs.FIRST is NULL');
1235 END IF ;
1236
1237 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE(
1238 EGO_USER_ATTR_ROW_OBJ( l_row_identifier
1239 , p_attr_group_id
1240 , p_application_id
1241 , p_attr_group_type
1242 , null
1243 , null
1244 , null
1245 , null
1246 ,l_mode)
1247 );
1248 -- END IF;*/
1249
1250 IF g_debug_flag THEN
1251 Write_Debug('calling EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data');
1252 END IF ;
1253
1254 ---------------------------------------------------------------
1255 -- Next, we build our privileges table for the current user; --
1256 -- any error in this helper function will be raised as an --
1257 -- exception, which will prevent us from calling PUAD at all --
1258 ---------------------------------------------------------------
1259 /*l_user_privileges_on_object := Get_User_Attrs_Privs(
1260 p_pk_column_name_value_pairs(1).VALUE,
1261 p_pk_column_name_value_pairs(2).VALUE
1262 );*/
1263 -- moved to java layer as, it needs to be called befoe the value is set in the EO.
1264 -- AS the pages would be rendered read only and the value is set from server side, so no way to change
1265 -- Although the changes from java are also reverted back and same resulted in revrting the comments on this file as well by mistake.
1266
1267 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data (
1268 p_api_version => p_api_version
1269 ,p_object_name => p_object_name
1270 ,p_attributes_row_table => l_attributes_row_table
1271 ,p_attributes_data_table => p_attributes_data_table
1272 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1273 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1274 -- ,p_user_privileges_on_object => l_user_privileges_on_object
1275 ,p_validate_only => p_validate_only
1276 ,p_commit => FND_API.G_FALSE
1277 ,p_init_fnd_msg_list => FND_API.G_TRUE
1278 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
1279 ,x_failed_row_id_list => l_failed_row_id_list
1280 ,x_return_status => x_return_status
1281 ,x_errorcode => x_errorcode
1282 ,x_msg_count => x_msg_count
1283 ,x_msg_data => x_msg_data
1284 );
1285
1286 IF g_debug_flag THEN
1287 Write_Debug('After calling EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data');
1288 Write_Debug('x_return_status: ' || x_return_status);
1289 Write_Debug('x_msg_count: ' || TO_CHAR(x_msg_count));
1290 Write_Debug('x_msg_data: ' || x_msg_data);
1291 END IF ;
1292 END IF;
1293
1294 END VALIDATE_USER_ATTRS;
1295
1296
1297 PROCEDURE INSERT_ITEM_USER_ATTRS
1298 (
1299 p_api_version IN NUMBER
1300 ,p_object_name IN VARCHAR2
1301 ,p_attr_group_id IN NUMBER
1302 ,p_application_id IN NUMBER
1303 ,p_attr_group_type IN VARCHAR2
1304 ,p_attr_group_name IN VARCHAR2
1305 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1306 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1307 ,P_DATA_LEVEL_NAME IN VARCHAR2
1308 ,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
1309 ,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
1310 ,p_mode IN VARCHAR2
1311 ,p_extra_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1312 ,p_extension_id IN NUMBER
1313 ,p_pending_b_table_name IN VARCHAR2
1314 ,p_pending_tl_table_name IN VARCHAR2
1315 ,p_pending_vl_name IN VARCHAR2
1316 ,p_acd_type IN VARCHAR2
1317 ,p_dml_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
1318 ,p_api_caller IN VARCHAR2
1319 ,p_key_attr_upd IN VARCHAR2
1320 ,x_return_status OUT NOCOPY VARCHAR2
1321 ,x_errorcode OUT NOCOPY NUMBER
1322 ,x_msg_count OUT NOCOPY NUMBER
1323 ,x_msg_data OUT NOCOPY VARCHAR2
1324 )
1325 IS
1326
1327 l_b_dml_for_ag VARCHAR2(30000);
1328 l_tl_dml_for_ag VARCHAR2(30000);
1329 l_bind_index NUMBER;
1330 l_bind_value NUMBER;
1331 l_b_bind_count NUMBER;
1332 l_tl_bind_count NUMBER;
1333 l_b_bind_attr_table EGO_USER_ATTR_DATA_TABLE;
1334 l_tl_bind_attr_table EGO_USER_ATTR_DATA_TABLE;
1335
1336 l_pending_base_tbl VARCHAR2(100);
1337 l_pending_tl_tbl VARCHAR2(100);
1338 l_pending_vl VARCHAR2(100);
1339 l_cursor_id INTEGER := DBMS_SQL.OPEN_CURSOR;
1340 l_number_of_rows NUMBER :=0;
1341 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
1342 L_ATTRIBUTES_DATA_TABLE EGO_USER_ATTR_DATA_TABLE;
1343 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1344 l_extension_id NUMBER := NULL;
1345 l_temp_extension_id NUMBER := NULL;
1346 l_row_identifier NUMBER ;
1347
1348 BEGIN
1349 -- IF (FND_PROFILE.value('FND_DIAGNOSTICS')='Y')
1350 -- THEN
1351 -- OPEN_DEBUG_SESSION( p_output_dir => g_output_dir,
1352 -- p_file_name => g_debug_filename);
1353 -- END IF;
1354 IF g_debug_flag THEN
1355 Write_Debug('Start INSERT_ITEM_USER_ATTRS');
1356 Write_Debug('-----------------------------------------' );
1357 END IF ;
1358
1359 SELECT CHANGE_B_TABLE_NAME
1360 , CHANGE_TL_TABLE_NAME
1361 , CHANGE_VL_TABLE_NAME
1362 INTO l_pending_base_tbl
1363 ,l_pending_tl_tbl
1364 ,l_pending_vl
1365 FROM ENG_PENDING_CHANGE_CTX
1366 WHERE CHANGE_ATTRIBUTE_GROUP_TYPE= p_attr_group_type
1367 AND APPLICATION_ID = p_application_id;
1368
1369
1370 x_return_status := FND_API.G_RET_STS_SUCCESS;
1371 l_extension_id := p_extension_id;
1372
1373
1374 IF P_MODE <> 'DELETE' -- AND (P_API_CALLER = G_EXEC_MODE_IMPORT OR P_API_CALLER = 'PWB')
1375 THEN
1376
1377 IF g_debug_flag THEN
1378 Write_Debug('p_extension_id ' || to_char(p_extension_id));
1379 END IF ;
1380
1381 IF (p_extension_id is NULL OR
1382 (P_API_CALLER = G_EXEC_MODE_IMPORT AND p_extension_id < 0) )
1383 AND (p_acd_type='CHANGE' OR p_acd_type='DELETE')
1384 THEN
1385 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE(
1386 EGO_ATTR_GROUP_REQUEST_OBJ(p_attr_group_id
1387 , p_application_id
1388 , p_attr_group_type
1389 , null
1390 , p_data_level_name
1391 , null
1392 , null
1393 , null
1394 , null
1395 , null
1396 , null
1397 ));
1398 if p_data_level_name_value_pairs is not null
1399 then
1400 for i in p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
1401 LOOP
1402 if i=1
1403 then
1404 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_1 :=
1405 p_data_level_name_value_pairs(i).VALUE ;
1406 elsif i=2
1407 then
1408 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_2 :=
1409 p_data_level_name_value_pairs(i).VALUE;
1410 elsif i=3
1411 then
1412 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_3 :=
1413 p_data_level_name_value_pairs(i).VALUE ;
1414 elsif i=4
1415 then
1416 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_4 :=
1417 p_data_level_name_value_pairs(i).VALUE ;
1418 elsif i=5
1419 then
1420 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_5 :=
1421 p_data_level_name_value_pairs(i).VALUE ;
1422 end if;
1423 end loop;
1424 end if;
1425
1426 l_extension_id := EGO_USER_ATTRS_DATA_PVT.Get_Extension_Id (
1427 p_object_name => p_object_name
1428 ,p_attr_group_id => p_attr_group_id
1429 ,p_application_id => p_application_id
1430 ,p_attr_group_type => p_attr_group_type
1431 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1432 ,P_DATA_LEVEL => P_DATA_LEVEL_NAME
1433 ,p_data_level_name_value_pairs => p_data_level_name_value_pairs
1434 ,p_attr_name_value_pairs => p_attr_name_value_pairs);
1435
1436 IF g_debug_flag THEN
1437 Write_Debug('Got new extension id ' || to_char(l_extension_id));
1438 END IF ;
1439
1440 END IF;
1441 L_ATTRIBUTES_DATA_TABLE := p_attr_name_value_pairs;
1442 if p_acd_type='CHANGE'
1443 THEN
1444
1445 FOR i IN L_ATTRIBUTES_DATA_TABLE.FIRST .. L_ATTRIBUTES_DATA_TABLE.LAST
1446 LOOP
1447 L_ATTRIBUTES_DATA_TABLE(i).ROW_IDENTIFIER := l_extension_id ;
1448 END LOOP;
1449 END IF;
1450
1451 IF P_ACD_TYPE<>'DELETE'
1452 THEN
1453 -- l_row_identifier := l_extension_id;
1454 IF p_acd_type = 'CHANGE' AND (P_API_CALLER = G_EXEC_MODE_IMPORT OR P_API_CALLER = 'PWB')
1455 THEN
1456
1457 IF g_debug_flag THEN
1458 Write_Debug('Calling SETUP_IMPL_ATTR_DATA_ROW ');
1459 END IF ;
1460
1461
1462 SETUP_IMPL_ATTR_DATA_ROW
1463 (
1464 p_api_version => p_api_version
1465 ,p_object_name => p_object_name
1466 ,p_attr_group_id => p_attr_group_id
1467 ,p_application_id => p_application_id
1468 ,p_attr_group_type => p_attr_group_type
1469 ,p_attr_group_name => p_attr_group_name
1470 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1471 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1472 ,P_DATA_LEVEL_NAME => P_DATA_LEVEL_NAME
1473 ,p_data_level_name_value_pairs => p_data_level_name_value_pairs
1474 ,p_attr_name_value_pairs => p_attr_name_value_pairs
1475 ,x_setup_attr_data => L_ATTRIBUTES_DATA_TABLE
1476 ,x_return_status => x_return_status
1477 ,x_errorcode => x_errorcode
1478 ,x_msg_count => x_msg_count
1479 ,x_msg_data => x_msg_data
1480 );
1481
1482 IF g_debug_flag THEN
1483 Write_Debug('After Calling SETUP_IMPL_ATTR_DATA_ROW ');
1484 END IF ;
1485
1486 END IF; -- p_acd_type = 'CHANGE'
1487
1488
1489 IF g_debug_flag THEN
1490 Write_Debug('Calling VALIDATE_USER_ATTRS ');
1491 END IF ;
1492
1493 -- Set Row Identier -1000
1494 -- This is passed thr Import in case of below condition
1495 --
1496 /* IF p_mode = 'CREATE'
1497 AND p_acd_type ='ADD'
1498 AND p_api_caller = ENG_CHANGE_ATTR_UTIL.G_EXEC_MODE_IMPORT
1499 THEN
1500
1501 l_row_identifier := -1000 ;
1502
1503 END IF ;*/
1504
1505
1506 VALIDATE_USER_ATTRS
1507 (
1508 p_api_version => p_api_version
1509 ,p_object_name => p_object_name
1510 ,p_attr_group_id => p_attr_group_id
1511 ,p_attr_group_type => p_attr_group_type
1512 ,p_application_id => p_application_id
1513 ,p_attr_group_name => p_attr_group_name
1514 ,p_attributes_data_table => L_ATTRIBUTES_DATA_TABLE
1515 ,p_extension_id => l_extension_id
1516 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1517 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1518 ,P_DATA_LEVEL_NAME => P_DATA_LEVEL_NAME
1519 ,p_data_level_name_value_pairs => p_data_level_name_value_pairs
1520 ,p_extra_pk_col_name_val_pairs => p_extra_pk_col_name_val_pairs
1521 ,p_extra_attr_name_value_pairs => NULL
1522 ,p_alternate_ext_b_table_name => l_pending_base_tbl
1523 ,p_alternate_ext_tl_table_name => l_pending_tl_tbl
1524 ,p_alternate_ext_vl_name => l_pending_vl
1525 ,p_user_privileges_on_object => NULL
1526 ,p_row_identifier => l_extension_id
1527 ,p_validate_only => FND_API.G_TRUE
1528 ,p_mode => p_mode
1529 ,p_acd_type => p_acd_type
1530 ,p_init_fnd_msg_list => FND_API.G_TRUE
1531 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
1532 ,x_return_status => x_return_status
1533 ,x_errorcode => x_errorcode
1534 ,x_msg_count => x_msg_count
1535 ,x_msg_data => x_msg_data
1536 ,p_key_attr_upd => p_key_attr_upd
1537 ) ;
1538
1539 END IF; -- P_ACD_TYPE<>'DELETE'
1540
1541 END IF; -- P_MODE<>'DELETE'
1542
1543
1544 IF g_debug_flag THEN
1545 Write_Debug('After calling VALIDATE_USER_ATTRS');
1546 Write_Debug('x_return_status: ' || x_return_status);
1547 Write_Debug('x_msg_count: ' || TO_CHAR(x_msg_count));
1548 Write_Debug('x_msg_data: ' || x_msg_data);
1549 END IF ;
1550
1551 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1552 THEN
1553
1554 IF g_debug_flag THEN
1555 Write_Debug('Now Generate_DML_For_Row . . . ');
1556 END IF ;
1557
1558 IF x_return_status = FND_API.G_RET_STS_SUCCESS OR x_return_status is NULL
1559 THEN
1560
1561
1562 IF g_debug_flag THEN
1563 Write_Debug('Now Generate_DML_For_Row 2 . . . ');
1564 END IF ;
1565 if(p_mode <> 'DELETE') THEN
1566 FOR i IN L_ATTRIBUTES_DATA_TABLE.FIRST .. L_ATTRIBUTES_DATA_TABLE.LAST
1567 LOOP
1568 FOR l_attr_index IN p_dml_attr_name_value_pairs.FIRST .. p_dml_attr_name_value_pairs.LAST
1569 LOOP
1570 if L_ATTRIBUTES_DATA_TABLE(i).ATTR_NAME = p_dml_attr_name_value_pairs(l_attr_index).ATTR_NAME
1571 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)
1572 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)
1573 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))
1574 THEN
1575 L_ATTRIBUTES_DATA_TABLE(i):= p_dml_attr_name_value_pairs(l_attr_index);
1576
1577 END IF;
1578 END LOOP;
1579
1580 END LOOP;
1581 END IF;
1582 IF p_mode ='UPDATE' OR p_mode ='DELETE'
1583 THEN
1584 l_temp_extension_id := l_extension_id;
1585 END IF;
1586 EGO_USER_ATTRS_DATA_PVT.Generate_DML_For_Row (
1587 p_api_version => 1.0
1588 ,p_object_name => p_object_name
1589 ,p_attr_group_id => p_attr_group_id
1590 ,p_application_id => p_application_id
1591 ,p_attr_group_type => p_attr_group_type
1592 ,p_attr_group_name => p_attr_group_name
1593 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1594 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1595 ,P_DATA_LEVEL => P_DATA_LEVEL_NAME
1596 ,p_data_level_name_value_pairs => p_data_level_name_value_pairs
1597 ,p_extension_id => l_temp_extension_id
1598 ,p_attr_name_value_pairs => L_ATTRIBUTES_DATA_TABLE
1599 ,p_mode => p_mode
1600 ,p_extra_pk_col_name_val_pairs => p_extra_pk_col_name_val_pairs
1601 ,p_alternate_ext_b_table_name => l_pending_base_tbl
1602 ,p_alternate_ext_tl_table_name => l_pending_tl_tbl
1603 ,p_alternate_ext_vl_name => l_pending_vl
1604 ,p_execute_dml => FND_API.G_FALSE
1605 ,p_init_fnd_msg_list => FND_API.G_FALSE
1606 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
1607 ,p_raise_business_event => FALSE
1608 ,x_return_status => x_return_status
1609 ,x_errorcode => x_errorcode
1610 ,x_msg_count => x_msg_count
1611 ,x_msg_data => x_msg_data
1612 ,x_b_dml_for_ag => l_b_dml_for_ag
1613 ,x_tl_dml_for_ag => l_tl_dml_for_ag
1614 ,x_b_bind_count => l_b_bind_count
1615 ,x_tl_bind_count => l_tl_bind_count
1616 ,x_b_bind_attr_table => l_b_bind_attr_table
1617 ,x_tl_bind_attr_table => l_tl_bind_attr_table
1618 );
1619 IF g_debug_flag THEN
1620 Write_Debug('Insert base DML : '|| l_b_dml_for_ag);
1621 Write_Debug('Insert tl DML : '|| l_tl_dml_for_ag);
1622 END IF;
1623 if p_mode = 'CREATE' and (p_acd_type ='CHANGE' OR p_acd_type ='DELETE') AND l_b_bind_attr_table is NOT NULL
1624 THEN
1625
1626 l_bind_index := l_b_bind_attr_table.FIRST;
1627 l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := l_extension_id;
1628 END IF;
1629 IF l_b_dml_for_ag IS NOT NULL
1630 THEN
1631 DBMS_SQL.Parse(l_cursor_id, l_b_dml_for_ag, DBMS_SQL.Native);
1632
1633 if l_b_bind_attr_table is NOT NULL AND p_mode <>'DELETE'
1634 THEN
1635
1636
1637 FOR l_bind_index IN l_b_bind_attr_table.FIRST .. l_b_bind_attr_table.LAST
1638 LOOP
1639 FOR l_attr_index IN p_dml_attr_name_value_pairs.FIRST .. p_dml_attr_name_value_pairs.LAST
1640 LOOP
1641
1642 if l_b_bind_attr_table(l_bind_index).ATTR_DISP_VALUE is NOT NULL
1643 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
1644 THEN
1645 l_b_bind_attr_table(l_bind_index).ATTR_VALUE_STR := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_STR;
1646 l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM;
1647 l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM;
1648 exit;
1649 END IF;
1650 END LOOP;
1651 IF ( l_b_bind_attr_table(l_bind_index).ATTR_VALUE_STR is not NULL)
1652 THEN
1653 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_b_bind_attr_table(l_bind_index).ATTR_VALUE_STR);
1654 ELSIF (l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM is not NULL)
1655 THEN
1656 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_b_bind_attr_table(l_bind_index).ATTR_VALUE_NUM);
1657 ELSIF (l_b_bind_attr_table(l_bind_index).ATTR_VALUE_DATE is not NULL)
1658 THEN
1659 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_b_bind_attr_table(l_bind_index).ATTR_VALUE_DATE);
1660 ELSE
1661 IF l_bind_index = l_b_bind_attr_table.LAST AND (p_mode ='UPDATE' OR p_mode ='DELETE')
1662 THEN
1663 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_extension_id);
1664 ELSE
1665 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_b_bind_attr_table(l_bind_index).ATTR_VALUE_STR);
1666 END IF;
1667 END IF;
1668
1669 END LOOP;
1670 END IF;
1671
1672 l_number_of_rows := DBMS_SQL.Execute(l_cursor_id);
1673 DBMS_SQL.Close_Cursor(l_cursor_id);
1674 END IF;
1675
1676 if p_mode = 'CREATE' and (p_acd_type ='CHANGE' OR p_acd_type ='DELETE') AND l_tl_bind_attr_table is NOT NULL
1677 THEN
1678 l_bind_index := l_tl_bind_attr_table.FIRST;
1679 l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := l_extension_id;
1680 END IF;
1681
1682
1683 IF l_tl_dml_for_ag is NOT NULL
1684 THEN
1685 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1686
1687 DBMS_SQL.Parse(l_cursor_id, l_tl_dml_for_ag, DBMS_SQL.Native);
1688
1689 IF l_tl_bind_attr_table is NOT NULL AND p_mode <>'DELETE'
1690 THEN
1691 FOR l_bind_index IN l_tl_bind_attr_table.FIRST .. l_tl_bind_attr_table.LAST
1692 LOOP
1693 FOR l_attr_index IN p_dml_attr_name_value_pairs.FIRST .. p_dml_attr_name_value_pairs.LAST
1694 LOOP
1695 if l_tl_bind_attr_table(l_bind_index).ATTR_DISP_VALUE is NOT NULL
1696 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
1697 THEN
1698 l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_STR := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_STR;
1699 l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM;
1700 l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM;
1701 exit;
1702 END IF;
1703 END LOOP;
1704 IF ( l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_STR is not NULL)
1705 THEN
1706
1707 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_STR);
1708 ELSIF (l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM is not NULL)
1709 THEN
1710
1711 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_NUM);
1712 ELSIF (l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_DATE is not NULL)
1713 THEN
1714
1715 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_DATE);
1716 ELSE
1717 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_STR);
1718 END IF;
1719
1720
1721 END LOOP;
1722 END IF;
1723 l_number_of_rows := DBMS_SQL.Execute(l_cursor_id);
1724
1725 DBMS_SQL.Close_Cursor(l_cursor_id);
1726 END IF;
1727
1728 END IF;
1729 END IF;
1730
1731
1732
1733 IF g_debug_flag THEN
1734 Write_Debug('Closing Debug Session: '
1735 || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
1736 Close_Debug_Session ;
1737 END IF ;
1738
1739
1740 END INSERT_ITEM_USER_ATTRS;
1741
1742
1743 PROCEDURE DELETE_ITEM_ATTRS
1744 ( p_api_version IN NUMBER
1745 ,p_object_name IN VARCHAR2
1746 ,p_application_id IN NUMBER
1747 ,p_attr_group_type IN VARCHAR2
1748 ,p_pk_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
1749 ,x_return_status OUT NOCOPY VARCHAR2
1750 ,x_errorcode OUT NOCOPY NUMBER
1751 ,x_msg_count OUT NOCOPY NUMBER
1752 ,x_msg_data OUT NOCOPY VARCHAR2
1753 )
1754 IS
1755 BEGIN
1756 NULL;
1757 END DELETE_ITEM_ATTRS;
1758
1759 PROCEDURE SETUP_IMPL_ATTR_DATA_ROW
1760 (
1761 p_api_version IN NUMBER
1762 ,p_object_name IN VARCHAR2
1763 ,p_attr_group_id IN NUMBER
1764 ,p_application_id IN NUMBER
1765 ,p_attr_group_type IN VARCHAR2
1766 ,p_attr_group_name IN VARCHAR2
1767 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1768 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1769 ,P_DATA_LEVEL_NAME IN VARCHAR2
1770 ,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1771 ,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE DEFAULT NULL
1772 ,x_setup_attr_data OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
1773 ,x_return_status OUT NOCOPY VARCHAR2
1774 ,x_errorcode OUT NOCOPY NUMBER
1775 ,x_msg_count OUT NOCOPY NUMBER
1776 ,x_msg_data OUT NOCOPY VARCHAR2
1777 )
1778 IS
1779
1780 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE ;
1781 l_attributes_data_table EGO_USER_ATTR_DATA_TABLE ;
1782 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1783 l_extension_id NUMBER := NULL;
1784 l_temp_extension_id NUMBER := NULL;
1785
1786 BEGIN
1787
1788 l_attributes_data_table := EGO_USER_ATTR_DATA_TABLE();
1789 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE() ;
1790
1791 x_setup_attr_data := p_attr_name_value_pairs;
1792 /*if p_data_level_name_value_pairs is NOT NULL
1793 --then
1794 l_attr_group_request_table
1795 := EGO_ATTR_GROUP_REQUEST_TABLE
1796 (
1797 EGO_ATTR_GROUP_REQUEST_OBJ(p_attr_group_id
1798 , p_application_id
1799 , p_attr_group_type
1800 , null
1801 ,
1802 , p_data_level_name_value_pairs
1803 (p_data_level_name_value_pairs.first).VALUE
1804 , null
1805 , null
1806 , null)
1807 );
1808 --else */
1809 l_attr_group_request_table
1810 := EGO_ATTR_GROUP_REQUEST_TABLE
1811 (
1812 EGO_ATTR_GROUP_REQUEST_OBJ(p_attr_group_id
1813 , p_application_id
1814 , p_attr_group_type
1815 , null
1816 , p_data_level_name
1817 , null
1818 , null
1819 , null
1820 , null
1821 , null
1822 , null
1823 )
1824 );
1825 --end if ;
1826 if p_data_level_name_value_pairs is not null
1827 then
1828 for i in p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
1829 LOOP
1830 if i=1
1831 then
1832 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_1 :=
1833 p_data_level_name_value_pairs(i).VALUE ;
1834 elsif i=2
1835 then
1836 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_2 :=
1837 p_data_level_name_value_pairs(i).VALUE;
1838 elsif i=3
1839 then
1840 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_3 :=
1841 p_data_level_name_value_pairs(i).VALUE ;
1842 elsif i=4
1843 then
1844 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_4 :=
1845 p_data_level_name_value_pairs(i).VALUE ;
1846 elsif i=5
1847 then
1848 l_attr_group_request_table(l_attr_group_request_table.FIRST).data_level_5 :=
1849 p_data_level_name_value_pairs(i).VALUE ;
1850 end if;
1851 end loop;
1852 end if;
1853
1854
1855 EGO_USER_ATTRS_DATA_PVT.Get_User_Attrs_Data
1856 (
1857 p_api_version => p_api_version
1858 ,p_object_name => p_object_name
1859 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1860 ,p_attr_group_request_table => l_attr_group_request_table
1861 ,x_attributes_row_table => l_attributes_row_table
1862 ,x_attributes_data_table => l_attributes_data_table
1863 ,p_init_fnd_msg_list => FND_API.G_FALSE
1864 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
1865 ,x_return_status => x_return_status
1866 ,x_errorcode => x_errorcode
1867 ,x_msg_count => x_msg_count
1868 ,x_msg_data => x_msg_data
1869 );
1870
1871
1872 IF ( x_setup_attr_data IS NULL OR l_attributes_data_table IS NULL )
1873 THEN
1874 RETURN ;
1875 END IF ;
1876
1877
1878 FOR i IN x_setup_attr_data.FIRST .. x_setup_attr_data.LAST
1879 LOOP
1880
1881
1882 FOR j IN l_attributes_data_table.FIRST .. l_attributes_data_table.LAST
1883 LOOP
1884
1885 IF (x_setup_attr_data(i).ROW_IDENTIFIER= l_attributes_data_table(j).ROW_IDENTIFIER)
1886 AND x_setup_attr_data(i).ATTR_NAME = l_attributes_data_table(j).ATTR_NAME
1887 THEN
1888
1889 if (x_setup_attr_data(i).ATTR_VALUE_STR IS NULL
1890 and x_setup_attr_data(i).ATTR_VALUE_NUM IS NULL
1891 and x_setup_attr_data(i).ATTR_VALUE_DATE IS NULL
1892 )
1893 then
1894
1895 -- Copy whole prod attribute data beause there is no change for this attr
1896 x_setup_attr_data (i) := l_attributes_data_table(j);
1897
1898 elsif x_setup_attr_data(i).ATTR_VALUE_STR is NOT NULL
1899 and x_setup_attr_data(i).ATTR_VALUE_STR = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_CHAR
1900 then
1901
1902 x_setup_attr_data(i).ATTR_VALUE_STR := NULL;
1903
1904 elsif x_setup_attr_data(i).ATTR_VALUE_NUM is NOT NULL
1905 and x_setup_attr_data(i).ATTR_VALUE_NUM = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_NUM
1906 then
1907
1908 x_setup_attr_data(i).ATTR_VALUE_NUM := NULL;
1909
1910 elsif x_setup_attr_data(i).ATTR_VALUE_DATE is NOT NULL
1911 and x_setup_attr_data(i).ATTR_VALUE_DATE = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_DATE
1912 then
1913
1914 x_setup_attr_data(i).ATTR_VALUE_DATE := NULL;
1915
1916 else
1917 -- Keep the pending change attribute value
1918 null ;
1919
1920 end if;
1921
1922 EXIT;
1923
1924 END IF ;
1925
1926 END LOOP;
1927 END LOOP;
1928
1929 END SETUP_IMPL_ATTR_DATA_ROW;
1930
1931
1932 PROCEDURE VALIDATE_GDSN_RECORDS(p_inventory_item_id IN NUMBER
1933 ,p_organization_id IN NUMBER
1934 ,p_attr_group_type IN VARCHAR2
1935 ,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
1936 ,p_tl_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
1937 ,x_return_status OUT NOCOPY VARCHAR2
1938 ,x_msg_count OUT NOCOPY NUMBER
1939 ,x_msg_data OUT NOCOPY VARCHAR2
1940
1941 )
1942 is
1943 l_single_row_attrs EGO_ITEM_PUB.UCCNET_ATTRS_SINGL_ROW_REC_TYP ;
1944 l_multi_row_attrs EGO_ITEM_PUB.UCCNET_ATTRS_MULTI_ROW_TBL_TYP ;
1945 l_extra_attrs_rec EGO_ITEM_PUB.UCCNET_EXTRA_ATTRS_REC_TYP;
1946 p_index NUMBER;
1947 BEGIN
1948 if p_attr_group_type='EGO_ITEM_GTIN_MULTI_ATTRS'
1949 THEN
1950 p_index := 1;
1951 l_multi_row_attrs(p_index).LANGUAGE_CODE := USERENV('LANG') ;
1952 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).MANUFACTURER_GLN,'MANUFACTURER_GLN');
1953 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).MANUFACTURER_ID, 'MANUFACTURER_ID') ;
1954 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).BAR_CODE_TYPE,'BAR_CODE_TYPE');
1955 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).COLOR_CODE_LIST_AGENCY,'COLOR_CODE_LIST_AGENCY') ;
1956 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).COLOR_CODE_VALUE,'COLOR_CODE_VALUE');
1957 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).CLASS_OF_DANGEROUS_CODE,'CLASS_OF_DANGEROUS_CODE') ;
1958 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_MARGIN_NUMBER, 'DANGEROUS_GOODS_MARGIN_NUMBER');
1959 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_HAZARDOUS_CODE,'DANGEROUS_GOODS_HAZARDOUS_CODE');
1960 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_PACK_GROUP ,'DANGEROUS_GOODS_PACK_GROUP') ;
1961 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_REG_CODE ,'DANGEROUS_GOODS_REG_CODE') ;
1962 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_SHIPPING_NAME ,'DANGEROUS_GOODS_SHIPPING_NAME') ;
1963 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).UNITED_NATIONS_DANG_GOODS_NO ,'UNITED_NATIONS_DANG_GOODS_NO') ;
1964 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).FLASH_POINT_TEMP,'FLASH_POINT_TEMP') ;
1965 -- UOM
1966 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).UOM_FLASH_POINT_TEMP,'UOM_FLASH_POINT_TEMP');
1967 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).COUNTRY_OF_ORIGIN,'COUNTRY_OF_ORIGIN');
1968 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).HARMONIZED_TARIFF_SYS_ID_CODE,'HARMONIZED_TARIFF_SYS_ID_CODE');
1969 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).SIZE_CODE_LIST_AGENCY,'SIZE_CODE_LIST_AGENCY');
1970 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).SIZE_CODE_VALUE,'SIZE_CODE_VALUE');
1971 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).HANDLING_INSTRUCTIONS_CODE,'HANDLING_INSTRUCTIONS_CODE') ;
1972 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DANGEROUS_GOODS_TECHNICAL_NAME,'DANGEROUS_GOODS_TECHNICAL_NAME');
1973 getValue(p_attr_name_value_pairs,l_multi_row_attrs(p_index).DELIVERY_METHOD_INDICATOR,'DELIVERY_METHOD_INDICATOR');
1974
1975 ELSIF p_attr_group_type='EGO_ITEM_GTIN_ATTRS'
1976 THEN
1977 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_A_CONSUMER_UNIT,'IS_TRADE_ITEM_A_CONSUMER_UNIT');
1978 getValue(p_attr_name_value_pairs, l_single_row_attrs.IS_TRADE_ITEM_INFO_PRIVATE,'IS_TRADE_ITEM_INFO_PRIVATE');
1979 getValue(p_attr_name_value_pairs, l_single_row_attrs.GROSS_WEIGHT ,'GROSS_WEIGHT');
1980 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_GROSS_WEIGHT,'UOM_GROSS_WEIGHT');
1981
1982 getValue(p_attr_name_value_pairs,l_single_row_attrs.EFFECTIVE_DATE,'EFFECTIVE_DATE');
1983 getValue(p_attr_name_value_pairs,l_single_row_attrs.END_AVAILABILITY_DATE_TIME,'END_AVAILABILITY_DATE_TIME');
1984 getValue(p_attr_name_value_pairs,l_single_row_attrs.START_AVAILABILITY_DATE_TIME,'START_AVAILABILITY_DATE_TIME');
1985 getValue(p_attr_name_value_pairs,l_single_row_attrs.BRAND_NAME,'BRAND_NAME');
1986 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_A_BASE_UNIT,'IS_TRADE_ITEM_A_BASE_UNIT');
1987 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_A_VARIABLE_UNIT,'.IS_TRADE_ITEM_A_VARIABLE_UNIT');
1988 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACK_MARKED_WITH_EXP_DATE,'IS_PACK_MARKED_WITH_EXP_DATE');
1989 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACK_MARKED_WITH_GREEN_DOT,'IS_PACK_MARKED_WITH_GREEN_DOT');
1990 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACK_MARKED_WITH_INGRED ,'IS_PACK_MARKED_WITH_INGRED');
1991 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACKAGE_MARKED_AS_REC, 'IS_PACKAGE_MARKED_AS_REC');
1992 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_PACKAGE_MARKED_RET,'IS_PACKAGE_MARKED_RET');
1993 getValue(p_attr_name_value_pairs,l_single_row_attrs.STACKING_FACTOR ,'STACKING_FACTOR');
1994 getValue(p_attr_name_value_pairs,l_single_row_attrs.STACKING_WEIGHT_MAXIMUM,'STACKING_WEIGHT_MAXIMUM');
1995 -- UOM:
1996 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_STACKING_WEIGHT_MAXIMUM,'UOM_STACKING_WEIGHT_MAXIMUM');
1997
1998 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDERING_LEAD_TIME,'ORDERING_LEAD_TIME');
1999 -- UOM:
2000 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_ORDERING_LEAD_TIME,'UOM_ORDERING_LEAD_TIME');
2001
2002 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDER_QUANTITY_MAX,'ORDER_QUANTITY_MAX');
2003 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDER_QUANTITY_MIN,'ORDER_QUANTITY_MIN');
2004 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDER_QUANTITY_MULTIPLE,'ORDER_QUANTITY_MULTIPLE');
2005 getValue(p_attr_name_value_pairs,l_single_row_attrs.ORDER_SIZING_FACTOR,'ORDER_SIZING_FACTOR');
2006 getValue(p_attr_name_value_pairs,l_single_row_attrs.EFFECTIVE_START_DATE,'EFFECTIVE_START_DATE');
2007 getValue(p_attr_name_value_pairs,l_single_row_attrs.CATALOG_PRICE,'CATALOG_PRICE');
2008 getValue(p_attr_name_value_pairs,l_single_row_attrs.EFFECTIVE_END_DATE,'EFFECTIVE_END_DATE');
2009 getValue(p_attr_name_value_pairs,l_single_row_attrs.SUGGESTED_RETAIL_PRICE,'SUGGESTED_RETAIL_PRICE');
2010 getValue(p_attr_name_value_pairs,l_single_row_attrs.MATERIAL_SAFETY_DATA_SHEET_NO,'MATERIAL_SAFETY_DATA_SHEET_NO');
2011 getValue(p_attr_name_value_pairs,l_single_row_attrs.HAS_BATCH_NUMBER,'HAS_BATCH_NUMBER');
2012 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_NON_SOLD_TRADE_RET_FLAG,'IS_NON_SOLD_TRADE_RET_FLAG');
2013 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_MAR_REC_FLAG,'IS_TRADE_ITEM_MAR_REC_FLAG');
2014 getValue(p_attr_name_value_pairs,l_single_row_attrs.DIAMETER,'DIAMETER');
2015 -- UOM:
2016 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DIAMETER,'UOM_DIAMETER');
2017
2018 getValue(p_attr_name_value_pairs,l_single_row_attrs.DRAINED_WEIGHT,'DRAINED_WEIGHT');
2019 -- UOM:
2020 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DRAINED_WEIGHT,'UOM_DRAINED_WEIGHT');
2021
2022 getValue(p_attr_name_value_pairs,l_single_row_attrs.GENERIC_INGREDIENT,'GENERIC_INGREDIENT');
2023
2024 getValue(p_attr_name_value_pairs,l_single_row_attrs.GENERIC_INGREDIENT_STRGTH,'GENERIC_INGREDIENT_STRGTH');
2025 -- UOM:
2026 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_GENERIC_INGREDIENT_STRGTH,'UOM_GENERIC_INGREDIENT_STRGTH');
2027
2028 getValue(p_attr_name_value_pairs,l_single_row_attrs.INGREDIENT_STRENGTH,'INGREDIENT_STRENGTH');
2029 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_NET_CONTENT_DEC_FLAG,'IS_NET_CONTENT_DEC_FLAG');
2030 getValue(p_attr_name_value_pairs,l_single_row_attrs.NET_CONTENT,'NET_CONTENT');
2031 -- UOM:
2032 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_NET_CONTENT,'UOM_NET_CONTENT');
2033
2034 getValue(p_attr_name_value_pairs,l_single_row_attrs.PEG_HORIZONTAL,'PEG_HORIZONTAL');
2035 -- UOM:
2036 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_PEG_HORIZONTAL,'UOM_PEG_HORIZONTAL');
2037
2038 getValue(p_attr_name_value_pairs,l_single_row_attrs.PEG_VERTICAL,'PEG_VERTICAL');
2039 -- UOM:
2040 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_PEG_VERTICAL,'UOM_PEG_VERTICAL');
2041
2042 getValue(p_attr_name_value_pairs,l_single_row_attrs.CONSUMER_AVAIL_DATE_TIME,'CONSUMER_AVAIL_DATE_TIME');
2043
2044 getValue(p_attr_name_value_pairs,l_single_row_attrs.DEL_TO_DIST_CNTR_TEMP_MAX,'DEL_TO_DIST_CNTR_TEMP_MAX');
2045 -- UOM:
2046 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DEL_TO_DIST_CNTR_TEMP_MAX,'UOM_DEL_TO_DIST_CNTR_TEMP_MAX');
2047
2048 getValue(p_attr_name_value_pairs,l_single_row_attrs.DEL_TO_DIST_CNTR_TEMP_MIN,'DEL_TO_DIST_CNTR_TEMP_MIN');
2049 -- UOM:
2050 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DEL_TO_DIST_CNTR_TEMP_MIN,'UOM_DEL_TO_DIST_CNTR_TEMP_MIN');
2051 getValue(p_attr_name_value_pairs,l_single_row_attrs.DELIVERY_TO_MRKT_TEMP_MAX,'DELIVERY_TO_MRKT_TEMP_MAX');
2052 -- UOM:
2053 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DELIVERY_TO_MRKT_TEMP_MAX,'UOM_DELIVERY_TO_MRKT_TEMP_MAX');
2054
2055 getValue(p_attr_name_value_pairs,l_single_row_attrs.DELIVERY_TO_MRKT_TEMP_MIN,'DELIVERY_TO_MRKT_TEMP_MIN');
2056 -- UOM:
2057 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_DELIVERY_TO_MRKT_TEMP_MIN,'UOM_DELIVERY_TO_MRKT_TEMP_MIN');
2058
2059
2060 getValue(p_attr_name_value_pairs,l_single_row_attrs.SUB_BRAND,'SUB_BRAND');
2061 --getValue(p_attr_name_value_pairs,l_single_row_attrs.TRADE_ITEM_DESCRIPTOR,'TRADE_ITEM_DESCRIPTOR');
2062 getValue(p_attr_name_value_pairs,l_single_row_attrs.EANUCC_CODE,'EANUCC_CODE');
2063 getValue(p_attr_name_value_pairs,l_single_row_attrs.EANUCC_TYPE,'EANUCC_TYPE');
2064 getValue(p_attr_name_value_pairs,l_single_row_attrs.RETAIL_PRICE_ON_TRADE_ITEM,'RETAIL_PRICE_ON_TRADE_ITEM');
2065 getValue(p_attr_name_value_pairs,l_single_row_attrs.QUANTITY_OF_COMP_LAY_ITEM,'QUANTITY_OF_COMP_LAY_ITEM');
2066 getValue(p_attr_name_value_pairs,l_single_row_attrs.QUANITY_OF_ITEM_IN_LAYER,'QUANITY_OF_ITEM_IN_LAYER');
2067 getValue(p_attr_name_value_pairs,l_single_row_attrs.QUANTITY_OF_ITEM_INNER_PACK,'QUANTITY_OF_ITEM_INNER_PACK');
2068 getValue(p_attr_name_value_pairs,l_single_row_attrs.QUANTITY_OF_INNER_PACK,'QUANTITY_OF_INNER_PACK');
2069 getValue(p_attr_name_value_pairs,l_single_row_attrs.BRAND_OWNER_GLN,'BRAND_OWNER_GLN');
2070 getValue(p_attr_name_value_pairs,l_single_row_attrs.BRAND_OWNER_NAME,'BRAND_OWNER_NAME');
2071 getValue(p_attr_name_value_pairs,l_single_row_attrs.STORAGE_HANDLING_TEMP_MAX,'STORAGE_HANDLING_TEMP_MAX');
2072
2073 -- UOM:
2074 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_STORAGE_HANDLING_TEMP_MAX,'UOM_STORAGE_HANDLING_TEMP_MAX');
2075
2076 getValue(p_attr_name_value_pairs,l_single_row_attrs.STORAGE_HANDLING_TEMP_MIN,'STORAGE_HANDLING_TEMP_MIN');
2077 -- UOM:
2078 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_STORAGE_HANDLING_TEMP_MIN,'UOM_STORAGE_HANDLING_TEMP_MIN');
2079
2080 getValue(p_attr_name_value_pairs,l_single_row_attrs.TRADE_ITEM_COUPON,'TRADE_ITEM_COUPON');
2081 getValue(p_attr_name_value_pairs,l_single_row_attrs.DEGREE_OF_ORIGINAL_WORT,'DEGREE_OF_ORIGINAL_WORT');
2082 getValue(p_attr_name_value_pairs,l_single_row_attrs.FAT_PERCENT_IN_DRY_MATTER,'FAT_PERCENT_IN_DRY_MATTER');
2083 getValue(p_attr_name_value_pairs,l_single_row_attrs.PERCENT_OF_ALCOHOL_BY_VOL,'PERCENT_OF_ALCOHOL_BY_VOL');
2084 getValue(p_attr_name_value_pairs,l_single_row_attrs.ISBN_NUMBER,'ISBN_NUMBER');
2085 getValue(p_attr_name_value_pairs,l_single_row_attrs.ISSN_NUMBER,'ISSN_NUMBER');
2086 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_INGREDIENT_IRRADIATED,'IS_INGREDIENT_IRRADIATED');
2087 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_RAW_MATERIAL_IRRADIATED,'IS_RAW_MATERIAL_IRRADIATED');
2088 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_GENETICALLY_MOD,'IS_TRADE_ITEM_GENETICALLY_MOD');
2089 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_IRRADIATED,'IS_TRADE_ITEM_IRRADIATED');
2090 getValue(p_attr_name_value_pairs,l_single_row_attrs.SECURITY_TAG_LOCATION,'SECURITY_TAG_LOCATION');
2091 getValue(p_attr_name_value_pairs,l_single_row_attrs.URL_FOR_WARRANTY,'URL_FOR_WARRANTY');
2092 getValue(p_attr_name_value_pairs,l_single_row_attrs.NESTING_INCREMENT,'NESTING_INCREMENT');
2093 -- UOM:
2094 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_NESTING_INCREMENT,'UOM_NESTING_INCREMENT');
2095
2096 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_TRADE_ITEM_RECALLED,'IS_TRADE_ITEM_RECALLED');
2097 getValue(p_attr_name_value_pairs,l_single_row_attrs.MODEL_NUMBER,'MODEL_NUMBER');
2098 getValue(p_attr_name_value_pairs,l_single_row_attrs.PIECES_PER_TRADE_ITEM,'PIECES_PER_TRADE_ITEM');
2099 -- UOM:
2100 getValue(p_attr_name_value_pairs,l_single_row_attrs.UOM_PIECES_PER_TRADE_ITEM,'UOM_PIECES_PER_TRADE_ITEM');
2101
2102 getValue(p_attr_name_value_pairs,l_single_row_attrs.DEPT_OF_TRNSPRT_DANG_GOODS_NUM,'DEPT_OF_TRNSPRT_DANG_GOODS_NUM');
2103 getValue(p_attr_name_value_pairs,l_single_row_attrs.RETURN_GOODS_POLICY,'RETURN_GOODS_POLICY');
2104 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_OUT_OF_BOX_PROVIDED,'IS_OUT_OF_BOX_PROVIDED');
2105 getValue(p_tl_attr_names_values,l_single_row_attrs.INVOICE_NAME,'INVOICE_NAME');
2106 getValue(p_tl_attr_names_values,l_single_row_attrs.DESCRIPTIVE_SIZE,'DESCRIPTIVE_SIZE');
2107 getValue(p_tl_attr_names_values,l_single_row_attrs.FUNCTIONAL_NAME,'FUNCTIONAL_NAME');
2108 getValue(p_tl_attr_names_values,l_single_row_attrs.TRADE_ITEM_FORM_DESCRIPTION,'TRADE_ITEM_FORM_DESCRIPTION');
2109 getValue(p_tl_attr_names_values,l_single_row_attrs.WARRANTY_DESCRIPTION,'WARRANTY_DESCRIPTION');
2110 getValue(p_tl_attr_names_values,l_single_row_attrs.TRADE_ITEM_FINISH_DESCRIPTION,'TRADE_ITEM_FINISH_DESCRIPTION');
2111 getValue(p_tl_attr_names_values,l_single_row_attrs.DESCRIPTION_SHORT,'DESCRIPTION_SHORT');
2112 getValue(p_attr_name_value_pairs,l_single_row_attrs.IS_BARCODE_SYMBOLOGY_DERIVABLE,'IS_BARCODE_SYMBOLOGY_DERIVABLE');
2113
2114
2115 END IF;
2116
2117 EGO_GTIN_ATTRS_PVT.Validate_Attributes(
2118 p_inventory_item_id => p_inventory_item_id
2119 ,p_organization_id => p_organization_id
2120 ,p_singe_row_attrs_rec => l_single_row_attrs
2121 ,p_multi_row_attrs_tbl => l_multi_row_attrs
2122 ,p_extra_attrs_rec => l_extra_attrs_rec
2123 ,x_return_status => x_return_status
2124 ,x_msg_count => x_msg_count
2125 ,x_msg_data => x_msg_data
2126 );
2127 END VALIDATE_GDSN_RECORDS;
2128
2129
2130 PROCEDURE UPDATE_DATA_LEVEL(P_PK_ATTR_NAME_VALUE_PAIRS EGO_COL_NAME_VALUE_PAIR_ARRAY
2131 ,P_NEW_DL_NAME_VALUE_PAIRS EGO_COL_NAME_VALUE_PAIR_ARRAY
2132 ,P_OLD_DL_NAME_VALUE_PAIRS EGO_COL_NAME_VALUE_PAIR_ARRAY
2133 ,P_OBJECT_NAME VARCHAR2
2134 ,P_APPLICATION_ID NUMBER)
2135 IS
2136 CURSOR C_DATA_LEVELS (p_objet_name VARCHAR2)
2137 IS
2138 SELECT DATA_LEVEL_INTERNAL_NAME
2139 ,DATA_LEVEL_DISPLAY_NAME
2140 ,DATA_LEVEL_COLUMN
2141 ,DL_COL_DATA_TYPE
2142 FROM (SELECT LOOKUP_CODE DATA_LEVEL_INTERNAL_NAME
2143 ,MEANING DATA_LEVEL_DISPLAY_NAME
2144 ,DECODE(ATTRIBUTE2, 1, ATTRIBUTE3,
2145 2, ATTRIBUTE5,
2146 3, ATTRIBUTE7,
2147 'NONE') DATA_LEVEL_COLUMN
2148 ,DECODE(ATTRIBUTE2, 1, ATTRIBUTE4,
2149 2, ATTRIBUTE6,
2150 3, ATTRIBUTE8,
2151 'NONE') DL_COL_DATA_TYPE
2152 FROM FND_LOOKUP_VALUES
2153 WHERE LOOKUP_TYPE = 'EGO_EF_DATA_LEVEL'
2154 AND ATTRIBUTE1 = p_objet_name
2155 AND LANGUAGE = USERENV('LANG')) DATA_LEVELS
2156 WHERE DATA_LEVEL_COLUMN <>'NONE';
2157
2158
2159
2160
2161 l_curr_ag_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
2162 l_attr_meta_data EGO_ATTR_METADATA_TABLE;
2163 l_pending_base_tbl VARCHAR2(30);
2164 l_pending_tl_tbl VARCHAR2(30);
2165 l_B_data_level_dml VARCHAR2(10000);
2166 L_B_WHERE_CLAUSE VARCHAR2(4000);
2167 l_TL_data_level_dml VARCHAR2(10000);
2168 L_DYN_ATTR_GRP_SQL VARCHAR2(10000);
2169 L_PEND_BIND_INDEX NUMBER :=0;
2170 L_PEND_BIND_VALUES EGO_COL_NAME_VALUE_PAIR_ARRAY;
2171 L_BIND_INDEX NUMBER :=0;
2172 L_BIND_VALUES EGO_COL_NAME_VALUE_PAIR_ARRAY;
2173 L_ATTR_CURSOR_ID NUMBER;
2174 L_PROD_CURSOR_ID NUMBER;
2175 L_B_CURSOR_ID NUMBER;
2176 L_TL_CURSOR_ID NUMBER;
2177 l_retrieved_value_char VARCHAR(1000);
2178 l_column_count NUMBER;
2179 l_dummy NUMBER;
2180 l_desc_table DBMS_SQL.Desc_Tab;
2181 l_b_update_dml VARCHAR2(10000);
2182 l_tl_update_dml VARCHAR2(10000);
2183 L_UPDATE_WHERE_CLAUSE VARCHAR2(4000) := NULL;
2184 L_UPDATE_BIND_INDEX NUMBER :=0;
2185 L_UPDATE_BIND_VALUES EGO_COL_NAME_VALUE_PAIR_ARRAY;
2186 L_ADDED_UPDATE_B_DML VARCHAR2(10000);
2187 L_ADDED_UPDATE_TL_DML VARCHAR2(10000);
2188 L_ADDED_WHERE_CLAUSE VARCHAR2(4000);
2189 L_B_TEMP_WHERE_CLAUSE VARCHAR2(4000);
2190 L_B_TEMP_DATA_LEVEL_DML VARCHAR2(4000);
2191
2192 BEGIN
2193 SELECT CHANGE_B_TABLE_NAME ,
2194 CHANGE_TL_TABLE_NAME
2195 INTO l_pending_base_tbl,l_pending_tl_tbl
2196 from ENG_PENDING_CHANGE_CTX
2197 where CHANGE_ATTRIBUTE_GROUP_TYPE= 'EGO_ITEMMGMT_GROUP'
2198 AND APPLICATION_ID = p_application_id;
2199
2200 L_B_UPDATE_DML := ' UPDATE '|| l_pending_base_tbl || ' SET EXTENSION_ID=:1';
2201 L_TL_UPDATE_DML := ' UPDATE '|| l_pending_TL_tbl || ' SET EXTENSION_ID=:1 ';
2202 L_UPDATE_BIND_INDEX := L_UPDATE_BIND_INDEX+1;
2203
2204 L_ADDED_UPDATE_B_DML := 'UPDATE ' || l_pending_base_tbl || ' SET ';
2205 L_ADDED_UPDATE_TL_DML := 'UPDATE ' || l_pending_base_tbl|| ' SET ';
2206 L_ADDED_WHERE_CLAUSE := ' WHERE ';
2207
2208 L_DYN_ATTR_GRP_SQL := ' SELECT DISTINCT ATTR_GROUP_ID ' ||
2209 ' FROM ' || l_pending_base_tbl ||
2210 ' WHERE ';
2211
2212 l_B_data_level_dml := ' SELECT A.EXTENSION_ID NEW_EXT_ID, ';
2213 -- ||' B.EXTENSION_ID OLD_EXT_ID, ';
2214
2215 L_B_WHERE_CLAUSE := ' FROM EGO_MTL_SY_ITEMS_EXT_VL A ,'
2216 ||' EGO_MTL_SY_ITEMS_EXT_VL B '
2217 ||' WHERE A.EXTENSION_ID <> B.EXTENSION_ID '
2218 ||' AND A.ATTR_GROUP_ID = B.ATTR_GROUP_ID ';
2219
2220 L_BIND_VALUES := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2221 L_PEND_BIND_VALUES := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2222 IF P_PK_ATTR_NAME_VALUE_PAIRS IS NOT NULL
2223 THEN
2224 FOR i IN 1 .. P_PK_ATTR_NAME_VALUE_PAIRS.LAST
2225 LOOP
2226 IF P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME <> 'CHANGE_LINE_ID'
2227 THEN
2228 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE || ' AND ';
2229 L_BIND_INDEX := L_BIND_INDEX+1;
2230 L_BIND_VALUES.EXTEND();
2231 L_BIND_VALUES(L_BIND_VALUES.LAST) :=
2232 EGO_COL_NAME_VALUE_PAIR_OBJ(P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2233 ,P_PK_ATTR_NAME_VALUE_PAIRS(i).VALUE);
2234 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE
2235 || 'A.'|| P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2236 || '= B.' || P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME;
2237
2238 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE || ' AND '
2239 || 'A.'
2240 || P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2241 || ' = :' ||L_BIND_INDEX;
2242 ELSE
2243 if L_UPDATE_WHERE_CLAUSE is NULL THEN
2244 L_UPDATE_WHERE_CLAUSE := ' WHERE ' ;
2245 END IF;
2246
2247 L_UPDATE_WHERE_CLAUSE := L_UPDATE_WHERE_CLAUSE ||
2248 P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME ||
2249 ' = ''' || P_PK_ATTR_NAME_VALUE_PAIRS(i).VALUE ||'''';
2250
2251 END IF;
2252 IF i >1
2253 THEN
2254
2255 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE || ' AND ';
2256
2257 END IF;
2258 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE
2259 || P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2260 || ' = ' || P_PK_ATTR_NAME_VALUE_PAIRS(i).VALUE;
2261 IF i > 1
2262 THEN
2263 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL || ' AND ';
2264 END IF;
2265
2266 L_PEND_BIND_INDEX := L_PEND_BIND_INDEX+1 ;
2267 L_PEND_BIND_VALUES.EXTEND();
2268 L_PEND_BIND_VALUES(L_PEND_BIND_VALUES.LAST) :=
2269 EGO_COL_NAME_VALUE_PAIR_OBJ(P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2270 ,P_PK_ATTR_NAME_VALUE_PAIRS(i).VALUE);
2271 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL
2272 || P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME
2273 || ' = :' ||L_PEND_BIND_INDEX;
2274
2275 END LOOP;
2276 END IF;
2277 if P_NEW_DL_NAME_VALUE_PAIRS is NOT NULL
2278 THEN
2279 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL ||' AND (';
2280 FOR i IN 1 .. P_NEW_DL_NAME_VALUE_PAIRS.LAST
2281 LOOP
2282 IF i > 1
2283 THEN
2284 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL ||' OR ';
2285 END IF;
2286 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL
2287 || P_NEW_DL_NAME_VALUE_PAIRS(i).NAME ||' IS NOT NULL ';
2288 END LOOP;
2289 L_DYN_ATTR_GRP_SQL := L_DYN_ATTR_GRP_SQL ||')';
2290 END IF;
2291
2292 L_ATTR_CURSOR_ID := DBMS_SQL.Open_Cursor;
2293 DBMS_SQL.Parse(L_ATTR_CURSOR_ID, L_DYN_ATTR_GRP_SQL, DBMS_SQL.Native);
2294 DBMS_SQL.Describe_Columns(L_ATTR_CURSOR_ID, l_column_count, l_desc_table);
2295 FOR i IN 1 .. l_column_count
2296 LOOP
2297 DBMS_SQL.Define_Column(L_ATTR_CURSOR_ID, i, l_retrieved_value_char, 1000);
2298
2299 END LOOP;
2300 FOR l_bind_index IN L_PEND_BIND_VALUES.FIRST .. L_PEND_BIND_VALUES.LAST
2301 LOOP
2302 DBMS_SQL.BIND_VARIABLE(L_ATTR_CURSOR_ID, ':'||l_bind_index,L_PEND_BIND_VALUES(l_bind_index).VALUE);
2303
2304 END LOOP;
2305
2306 l_dummy := DBMS_SQL.Execute(L_ATTR_CURSOR_ID);
2307
2308
2309 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE
2310 ||' AND ACD_TYPE = ''ADD'' AND ATTR_GROUP_ID IN (-1';
2311
2312 FOR REC IN C_DATA_LEVELS(P_OBJECT_NAME)
2313 LOOP
2314 FOR i IN 1 .. P_NEW_DL_NAME_VALUE_PAIRS.LAST
2315 LOOP
2316 IF P_NEW_DL_NAME_VALUE_PAIRS(i).NAME = REC.DATA_LEVEL_COLUMN
2317 THEN
2318 l_B_data_level_dml := l_B_data_level_dml
2319 || ' A.'||REC.DATA_LEVEL_COLUMN || ' NEW_'||REC.DATA_LEVEL_COLUMN;
2320 --|| ',B.'||REC.DATA_LEVEL_COLUMN || ' OLD_'||REC.DATA_LEVEL_COLUMN;
2321
2322 L_UPDATE_BIND_INDEX := L_UPDATE_BIND_INDEX+1;
2323 L_B_UPDATE_DML := L_B_UPDATE_DML ||' , '|| REC.DATA_LEVEL_COLUMN || ' = :'||L_UPDATE_BIND_INDEX;
2324 L_TL_UPDATE_DML := L_TL_UPDATE_DML ||' , '|| REC.DATA_LEVEL_COLUMN|| ' = :'||L_UPDATE_BIND_INDEX;
2325 L_ADDED_UPDATE_B_DML := L_ADDED_UPDATE_B_DML
2326 ||' '|| REC.DATA_LEVEL_COLUMN
2327 ||' = ' || P_NEW_DL_NAME_VALUE_PAIRS(i).VALUE;
2328 L_ADDED_UPDATE_TL_DML := L_ADDED_UPDATE_TL_DML
2329 ||' '|| REC.DATA_LEVEL_COLUMN
2330 ||' = ' || P_NEW_DL_NAME_VALUE_PAIRS(i).VALUE;
2331 L_BIND_INDEX := L_BIND_INDEX+1;
2332 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE
2333 || ' AND A.'|| P_NEW_DL_NAME_VALUE_PAIRS(i).NAME
2334 || ' = :' ||L_BIND_INDEX;
2335 L_BIND_VALUES.EXTEND();
2336 L_BIND_VALUES(L_BIND_VALUES.LAST) :=
2337 EGO_COL_NAME_VALUE_PAIR_OBJ(P_NEW_DL_NAME_VALUE_PAIRS(i).NAME
2338 ,P_NEW_DL_NAME_VALUE_PAIRS(i).VALUE);
2339
2340
2341
2342 EXIT;
2343 END IF;
2344
2345
2346 END LOOP; -- DATA LEVEL ATTR VALUES
2347 FOR i IN 1 .. P_OLD_DL_NAME_VALUE_PAIRS.LAST
2348 LOOP
2349 IF P_OLD_DL_NAME_VALUE_PAIRS(i).NAME = REC.DATA_LEVEL_COLUMN
2350 THEN
2351 L_BIND_INDEX := L_BIND_INDEX+1;
2352 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE
2353 || ' AND B.'|| P_OLD_DL_NAME_VALUE_PAIRS(i).NAME
2354 || ' = :' ||L_BIND_INDEX;
2355 L_BIND_VALUES.EXTEND();
2356 L_BIND_VALUES(L_BIND_VALUES.LAST) :=
2357 EGO_COL_NAME_VALUE_PAIR_OBJ(P_OLD_DL_NAME_VALUE_PAIRS(i).NAME
2358 ,P_OLD_DL_NAME_VALUE_PAIRS(i).VALUE);
2359
2360
2361 EXIT;
2362 END IF;
2363
2364 END LOOP; -- DATA LEVEL ATTR VALUES
2365 END LOOP; -- DATA LEVELS
2366
2367 L_UPDATE_BIND_INDEX := L_UPDATE_BIND_INDEX +1;
2368
2369 if L_UPDATE_WHERE_CLAUSE is NULL then
2370 L_UPDATE_WHERE_CLAUSE := ' WHERE ';
2371 ELSE
2372 L_UPDATE_WHERE_CLAUSE := L_UPDATE_WHERE_CLAUSE || ' AND ';
2373 END IF;
2374
2375 L_UPDATE_WHERE_CLAUSE := L_UPDATE_WHERE_CLAUSE || ' EXTENSION_ID =:' || L_UPDATE_BIND_INDEX;
2376
2377 L_B_UPDATE_DML := L_B_UPDATE_DML || L_UPDATE_WHERE_CLAUSE;
2378 L_TL_UPDATE_DML := L_TL_UPDATE_DML || L_UPDATE_WHERE_CLAUSE;
2379
2380 l_B_data_level_dml := l_B_data_level_dml || ', B.EXTENSION_ID ';
2381
2382 l_B_cursor_id:= DBMS_SQL.OPEN_CURSOR;
2383 DBMS_SQL.Parse(l_B_cursor_id, L_B_UPDATE_DML, DBMS_SQL.Native);
2384
2385 l_TL_cursor_id:= DBMS_SQL.OPEN_CURSOR;
2386 DBMS_SQL.Parse(l_TL_cursor_id, L_TL_UPDATE_DML, DBMS_SQL.Native);
2387
2388
2389 L_BIND_INDEX := L_BIND_INDEX +1;
2390 L_B_WHERE_CLAUSE := L_B_WHERE_CLAUSE ||
2391 ' AND A.ATTR_GROUP_ID '||
2392 ' = :' ||L_BIND_INDEX;
2393
2394 l_bind_values.extend();
2395
2396 L_B_TEMP_WHERE_CLAUSE := L_B_WHERE_CLAUSE;
2397 L_B_TEMP_DATA_LEVEL_DML := l_B_data_level_dml;
2398
2399 WHILE (DBMS_SQL.Fetch_Rows(L_ATTR_CURSOR_ID) > 0)
2400 LOOP
2401 DBMS_SQL.Column_Value(L_ATTR_CURSOR_ID, 1, l_retrieved_value_char);
2402 l_curr_ag_metadata_obj :=
2403 EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(P_ATTR_GROUP_ID => to_number(l_retrieved_value_char)
2404 ,P_APPLICATION_ID => p_application_id
2405 ,P_ATTR_GROUP_TYPE => 'EGO_ITEMMGMT_GROUP'
2406 );
2407 l_attr_meta_data := l_curr_ag_metadata_obj.ATTR_METADATA_TABLE;
2408 IF L_B_WHERE_CLAUSE = L_B_TEMP_WHERE_CLAUSE
2409 THEN
2410 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE ||'-1,'|| l_retrieved_value_char;
2411 ELSE
2412 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE || ' , ' ||l_retrieved_value_char ;
2413 END IF;
2414
2415 L_B_WHERE_CLAUSE := L_B_TEMP_WHERE_CLAUSE;
2416 l_B_data_level_dml := L_B_TEMP_DATA_LEVEL_DML;
2417
2418 l_bind_values(l_bind_values.LAST) := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_GROUP_ID'
2419 ,to_number(l_retrieved_value_char));
2420
2421 FOR i IN 1 .. l_attr_meta_data.LAST
2422 LOOP
2423 IF l_attr_meta_data(i).UNIQUE_KEY_FLAG = 'Y'
2424 THEN
2425 L_B_WHERE_CLAUSE := L_B_TEMP_WHERE_CLAUSE
2426 || ' AND A.'|| l_attr_meta_data(i).DATABASE_COLUMN
2427 || ' = B.'||l_attr_meta_data(i).DATABASE_COLUMN ;
2428
2429 END IF;
2430 END LOOP;
2431
2432 l_B_data_level_dml := l_B_data_level_dml || L_B_WHERE_CLAUSE;
2433
2434 l_prod_cursor_id := DBMS_SQL.Open_Cursor;
2435 DBMS_SQL.Parse(l_prod_cursor_id, l_B_data_level_dml, DBMS_SQL.Native);
2436 DBMS_SQL.Describe_Columns(l_prod_cursor_id, l_column_count, l_desc_table);
2437
2438 FOR i IN 1 .. l_column_count
2439 LOOP
2440 DBMS_SQL.Define_Column(l_prod_cursor_id, i, l_retrieved_value_char, 1000);
2441
2442 END LOOP;
2443 FOR l_bind_index IN L_BIND_VALUES.FIRST .. L_BIND_VALUES.LAST
2444 LOOP
2445 DBMS_SQL.BIND_VARIABLE(l_prod_cursor_id, ':'||l_bind_index,L_BIND_VALUES(l_bind_index).VALUE);
2446
2447 END LOOP;
2448 l_dummy := DBMS_SQL.Execute(l_prod_cursor_id);
2449
2450
2451 WHILE (DBMS_SQL.Fetch_Rows(l_prod_cursor_id) > 0)
2452 LOOP
2453 FOR i IN 1 .. l_column_count
2454 LOOP
2455 DBMS_SQL.Column_Value(l_prod_cursor_id, i, l_retrieved_value_char);
2456 DBMS_SQL.BIND_VARIABLE(l_B_cursor_id, ':'||i,TO_NUMBER(l_retrieved_value_char));
2457 DBMS_SQL.BIND_VARIABLE(l_TL_cursor_id, ':'||i,TO_NUMBER(l_retrieved_value_char));
2458
2459 END LOOP;
2460 l_dummy := DBMS_SQL.Execute(l_B_cursor_id);
2461 L_dummy := DBMS_SQL.Execute(l_TL_cursor_id);
2462 END LOOP;
2463
2464 END LOOP;-- WHILE ATTR GROUP CURSOR
2465 L_ADDED_WHERE_CLAUSE := L_ADDED_WHERE_CLAUSE || ')';
2466 L_ADDED_UPDATE_B_DML := L_ADDED_UPDATE_B_DML ||' '|| L_ADDED_WHERE_CLAUSE;
2467 L_ADDED_UPDATE_TL_DML := L_ADDED_UPDATE_TL_DML ||' ' || L_ADDED_WHERE_CLAUSE;
2468
2469 EXECUTE IMMEDIATE L_ADDED_UPDATE_B_DML;
2470 EXECUTE IMMEDIATE L_ADDED_UPDATE_TL_DML;
2471 END UPDATE_DATA_LEVEL;
2472
2473 PROCEDURE getValue(p_attrs_data_tbl IN EGO_USER_ATTR_DATA_TABLE
2474 ,x_rec_column OUT NOCOPY VARCHAR2
2475 ,p_attr_name IN VARCHAR2)
2476 IS
2477 BEGIN
2478 for i in p_attrs_data_tbl.FIRST .. p_attrs_data_tbl.LAST
2479 LOOP
2480 IF p_attrs_data_tbl(i).attr_name = p_attr_name
2481 then
2482 x_rec_column := p_attrs_data_tbl(i).attr_value_str;
2483 END IF;
2484 END LOOP;
2485
2486
2487 END getValue;
2488
2489 PROCEDURE getValue(p_attrs_data_tbl IN EGO_USER_ATTR_DATA_TABLE
2490 ,x_rec_column OUT NOCOPY NUMBER
2491 ,p_attr_name IN VARCHAR2)
2492 IS
2493 l_attr_name VARCHAR2(80);
2494 BEGIN
2495 for i in p_attrs_data_tbl.FIRST .. p_attrs_data_tbl.LAST
2496 LOOP
2497
2498 IF INSTR(p_attr_name,'UOM')>0
2499 THEN
2500 l_attr_name := SUBSTR(p_attr_name,INSTR(p_attr_name,'UOM')+4);
2501 ELSE
2502 l_attr_name := p_attr_name;
2503 END IF;
2504 IF p_attrs_data_tbl(i).attr_name = l_attr_name
2505 then
2506 IF INSTR(p_attr_name,'UOM')>0
2507 THEN
2508 x_rec_column := p_attrs_data_tbl(i).ATTR_UNIT_OF_MEASURE;
2509 ELSE
2510 x_rec_column := p_attrs_data_tbl(i).attr_value_num;
2511 END IF;
2512 END IF;
2513 END LOOP;
2514
2515 END getValue;
2516 PROCEDURE getValue(p_attrs_data_tbl IN EGO_USER_ATTR_DATA_TABLE
2517 ,x_rec_column OUT NOCOPY DATE
2518 ,p_attr_name IN VARCHAR2)
2519 IS
2520 BEGIN
2521 for i in p_attrs_data_tbl.FIRST .. p_attrs_data_tbl.LAST
2522 LOOP
2523 IF p_attrs_data_tbl(i).attr_name = p_attr_name
2524 then
2525 x_rec_column := p_attrs_data_tbl(i).attr_value_date;
2526 END IF;
2527 END LOOP;
2528
2529 END getValue;
2530
2531
2532
2533 PROCEDURE GET_ATTR_GRP_VO_DEF
2534 (
2535 p_change_attr_group_type IN VARCHAR2
2536 ,p_object_name IN VARCHAR2
2537 ,p_application_short_name IN VARCHAR2
2538 ,x_vo_def OUT NOCOPY VARCHAR2
2539 )
2540 IS
2541 BEGIN
2542 select change_vo_def_name into x_vo_def
2543 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2544 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2545 and fnd_obj.obj_name = p_object_name
2546 and fnd_appl.application_short_name = p_application_short_name;
2547
2548 END GET_ATTR_GRP_VO_DEF;
2549
2550 PROCEDURE GET_ATTR_GRP_VO_INSTANCE
2551 (
2552 p_change_attr_group_type IN VARCHAR2
2553 ,p_object_name IN VARCHAR2
2554 ,p_application_short_name IN VARCHAR2
2555 ,x_vo_instance OUT NOCOPY VARCHAR2
2556 )
2557 IS
2558 BEGIN
2559 select change_vo_inst_name into x_vo_instance
2560 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2561 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2562 and fnd_obj.obj_name = p_object_name
2563 and fnd_appl.application_short_name = p_application_short_name;
2564
2565 END GET_ATTR_GRP_VO_INSTANCE;
2566
2567 PROCEDURE GET_ATTR_GRP_VO_ROW_CLASS
2568 (
2569 p_change_attr_group_type IN VARCHAR2
2570 ,p_object_name IN VARCHAR2
2571 ,p_application_short_name IN VARCHAR2
2572 ,x_vo_row_class OUT NOCOPY VARCHAR2
2573 )
2574 IS
2575 BEGIN
2576 select change_vo_row_class_name into x_vo_row_class
2577 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2578 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2579 and fnd_obj.obj_name = p_object_name
2580 and fnd_appl.application_short_name = p_application_short_name;
2581
2582 END GET_ATTR_GRP_VO_ROW_CLASS;
2583
2584 PROCEDURE GET_ATTR_GRP_EO_DEF
2585 (
2586 p_change_attr_group_type IN VARCHAR2
2587 ,p_object_name IN VARCHAR2
2588 ,p_application_short_name IN VARCHAR2
2589 ,x_eo_def OUT NOCOPY VARCHAR2
2590 )
2591 IS
2592 BEGIN
2593 select change_eo_def_name into x_eo_def
2594 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2595 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2596 and fnd_obj.obj_name = p_object_name
2597 and fnd_appl.application_short_name = p_application_short_name;
2598
2599 END GET_ATTR_GRP_EO_DEF;
2600
2601 PROCEDURE GET_ATTR_GRP_BASE_TABLE
2602 (
2603 p_change_attr_group_type IN VARCHAR2
2604 ,p_object_name IN VARCHAR2
2605 ,p_application_short_name IN VARCHAR2
2606 ,x_base_table OUT NOCOPY VARCHAR2
2607 )
2608 IS
2609 BEGIN
2610 select change_b_table_name into x_base_table
2611 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2612 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2613 and fnd_obj.obj_name = p_object_name
2614 and fnd_appl.application_short_name = p_application_short_name;
2615
2616 END GET_ATTR_GRP_BASE_TABLE;
2617
2618 PROCEDURE GET_ATTR_GRP_TL_TABLE
2619 (
2620 p_change_attr_group_type IN VARCHAR2
2621 ,p_object_name IN VARCHAR2
2622 ,p_application_short_name IN VARCHAR2
2623 ,x_tl_table OUT NOCOPY VARCHAR2
2624 )
2625 IS
2626 BEGIN
2627 select change_tl_table_name into x_tl_table
2628 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2629 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2630 and fnd_obj.obj_name = p_object_name
2631 and fnd_appl.application_short_name = p_application_short_name;
2632
2633 END GET_ATTR_GRP_TL_TABLE;
2634
2635 PROCEDURE GET_ATTR_GRP_VL_NAME
2636 (
2637 p_change_attr_group_type IN VARCHAR2
2638 ,p_object_name IN VARCHAR2
2639 ,p_application_short_name IN VARCHAR2
2640 ,x_vl_name OUT NOCOPY VARCHAR2
2641 )
2642 IS
2643 BEGIN
2644 select change_vl_table_name into x_vl_name
2645 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2646 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2647 and fnd_obj.obj_name = p_object_name
2648 and fnd_appl.application_short_name = p_application_short_name;
2649
2650 END GET_ATTR_GRP_VL_NAME;
2651
2652 PROCEDURE GET_CONTEXT_VALUE
2653 (
2654 p_change_attr_group_type IN VARCHAR2
2655 ,p_object_name IN VARCHAR2
2656 ,p_application_short_name IN VARCHAR2
2657 ,p_context_type IN VARCHAR2 -- column name in the eng_pending_change_ctx table
2658 ,x_context_value OUT NOCOPY VARCHAR2
2659 )
2660 IS
2661 BEGIN
2662
2663 CASE p_context_type
2664 WHEN 'CHANGE_B_TABLE_NAME' THEN
2665 select change_b_table_name into x_context_value
2666 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2667 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2668 and fnd_obj.obj_name = p_object_name
2669 and fnd_appl.application_short_name = p_application_short_name;
2670 WHEN 'CHANGE_TL_TABLE_NAME' THEN
2671 select change_tl_table_name into x_context_value
2672 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2673 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2674 and fnd_obj.obj_name = p_object_name
2675 and fnd_appl.application_short_name = p_application_short_name;
2676 WHEN 'CHANGE_VL_TABLE_NAME' THEN
2677 select change_vl_table_name into x_context_value
2678 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2679 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2680 and fnd_obj.obj_name = p_object_name
2681 and fnd_appl.application_short_name = p_application_short_name;
2682 WHEN 'CHANGE_VO_DEF_NAME' THEN
2683 select change_vo_def_name into x_context_value
2684 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2685 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2686 and fnd_obj.obj_name = p_object_name
2687 and fnd_appl.application_short_name = p_application_short_name;
2688 WHEN 'CHANGE_VO_INST_NAME' THEN
2689 select change_vo_inst_name into x_context_value
2690 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2691 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2692 and fnd_obj.obj_name = p_object_name
2693 and fnd_appl.application_short_name = p_application_short_name;
2694 WHEN 'CHANGE_VO_ROW_CLASS_NAME' THEN
2695 select change_vo_row_class_name into x_context_value
2696 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2697 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2698 and fnd_obj.obj_name = p_object_name
2699 and fnd_appl.application_short_name = p_application_short_name;
2700 WHEN 'CHANGE_EO_DEF_NAME' THEN
2701 select change_eo_def_name into x_context_value
2702 from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
2703 where pc_ctx.change_attribute_group_type = p_change_attr_group_type
2704 and fnd_obj.obj_name = p_object_name
2705 and fnd_appl.application_short_name = p_application_short_name;
2706 END CASE;
2707
2708 END GET_CONTEXT_VALUE;
2709
2710 PROCEDURE DEL_PEND_ATTR_CHGS
2711 (
2712 P_MODE IN VARCHAR2
2713 ,P_CHANGE_ID IN NUMBER
2714 ,P_CHANGE_LINE_ID IN NUMBER
2715 ,P_ORG_ID IN NUMBER
2716 ,P_DATA_LEVEL_NAME IN VARCHAR2
2717 ,P_DATA_LEVEL_NAME_VALUE_PAIRS IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
2718 )
2719 IS
2720 l_dynamic_sql VARCHAR2(4000);
2721 l_data_level_id NUMBER;
2722 l_data_level_pk1 NUMBER := -1;
2723 l_data_level_pk2 NUMBER := -1;
2724 l_data_level_pk3 NUMBER := -1;
2725 l_data_level_pk4 NUMBER := -1;
2726 l_data_level_pk5 NUMBER := -1;
2727 CURSOR c_getDataLevelId(dataLevelName IN VARCHAR2) IS
2728 SELECT DATA_LEVEL_ID FROM EGO_DATA_LEVEL_B WHERE DATA_LEVEL_NAME = dataLevelName;
2729 BEGIN
2730 l_dynamic_sql := 'WHERE CHANGE_ID= :1 '
2731 || 'AND CHANGE_LINE_ID= :2 ';
2732
2733 --pass p_mode as LINE to delete pending changes for revised item
2734 --pass p_mode as ASSOC to delete pending changes for the association
2735
2736 IF P_MODE = 'ASSOC' THEN
2737 l_dynamic_sql := l_dynamic_sql || 'AND ORGANIZATION_ID= :3 '
2738 || 'AND DATA_LEVEL_ID = :4 '
2739 || 'AND NVL(PK1_VALUE, -1) = :5 '
2740 || 'AND NVL(PK2_VALUE, -1) = :6 '
2741 || 'AND NVL(PK3_VALUE, -1) = :7 '
2742 || 'AND NVL(PK4_VALUE, -1) = :8 '
2743 || 'AND NVL(PK5_VALUE, -1) = :9 ';
2744
2745 OPEN c_getDataLevelId(P_DATA_LEVEL_NAME);
2746 FETCH c_getDataLevelId INTO l_data_level_id;
2747 CLOSE c_getDataLevelId;
2748
2749 IF p_data_level_name_value_pairs is NOT NULL AND p_data_level_name_value_pairs.COUNT>0
2750 THEN
2751 FOR data_index IN p_data_level_name_value_pairs.FIRST .. p_data_level_name_value_pairs.LAST
2752 LOOP
2753 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
2754 IF (data_index = 1) THEN
2755 l_data_level_pk1 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2756 ELSIF (data_index = 2) THEN
2757 l_data_level_pk2 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2758 ELSIF (data_index = 3) THEN
2759 l_data_level_pk3 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2760 ELSIF (data_index = 4) THEN
2761 l_data_level_pk4 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2762 ELSIF (data_index = 5) THEN
2763 l_data_level_pk5 := TO_NUMBER(p_data_level_name_value_pairs(data_index).VALUE);
2764 END IF;
2765 END IF;
2766 END LOOP;
2767 END IF;
2768 EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_B '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID, P_ORG_ID,
2769 l_data_level_id, l_data_level_pk1, l_data_level_pk2,
2770 l_data_level_pk3, l_data_level_pk4, l_data_level_pk5;
2771 EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_TL '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID, P_ORG_ID,
2772 l_data_level_id, l_data_level_pk1, l_data_level_pk2,
2773 l_data_level_pk3, l_data_level_pk4, l_data_level_pk5;
2774 ELSIF P_MODE = 'LINE' THEN
2775 EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_B '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID;
2776 EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_TL '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID;
2777 END IF;
2778
2779 END DEL_PEND_ATTR_CHGS;
2780
2781 PROCEDURE SAVE_ITEM_NUM_DESC(P_CHANGE_ID IN NUMBER
2782 , P_CHANGE_LINE_ID IN NUMBER
2783 , P_ORGANIZATION_ID IN NUMBER
2784 , P_ITEM_ID IN NUMBER
2785 , P_ITEM_NUM IN VARCHAR2 DEFAULT NULL
2786 , P_ITEM_DESC IN VARCHAR2 DEFAULT NULL
2787 , p_transaction_mode IN VARCHAR2
2788 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
2789 )
2790 IS
2791 CURSOR langauges is
2792 SELECT LANGS.LANGUAGE_CODE
2793 FROM FND_LANGUAGES LANGS
2794 WHERE LANGS.installed_flag IN ('B','I');
2795
2796 BEGIN
2797
2798 x_return_status := FND_API.G_RET_STS_SUCCESS;
2799 if 'CREATE' = p_transaction_mode
2800 then
2801 insert into EGO_MTL_SY_ITEMS_CHG_B(
2802 INVENTORY_ITEM_ID,
2803 ORGANIZATION_ID,
2804 CHANGE_ID,
2805 CHANGE_LINE_ID,
2806 ACD_TYPE,
2807 CREATED_BY,
2808 CREATION_DATE,
2809 LAST_UPDATED_BY,
2810 LAST_UPDATE_LOGIN,
2811 LAST_UPDATE_DATE,
2812 DESCRIPTION,
2813 ITEM_NUMBER)
2814 values
2815 (
2816 p_item_id,
2817 p_organization_id,
2818 p_change_id,
2819 p_change_line_id,
2820 'CHANGE',
2821 FND_GLOBAL.user_id,
2822 SYSDATE,
2823 FND_GLOBAL.user_id,
2824 FND_GLOBAL.user_id,
2825 SYSDATE,
2826 P_ITEM_DESC,
2827 P_ITEM_NUM
2828 );
2829 FOR LANG_CODE IN langauges
2830 loop
2831 insert into EGO_MTL_SY_ITEMS_CHG_TL(
2832 INVENTORY_ITEM_ID,
2833 ORGANIZATION_ID,
2834 CHANGE_ID,
2835 CHANGE_LINE_ID,
2836 ACD_TYPE,
2837 CREATED_BY,
2838 CREATION_DATE,
2839 LAST_UPDATED_BY,
2840 LAST_UPDATE_LOGIN,
2841 LAST_UPDATE_DATE,
2842 LANGUAGE,
2843 SOURCE_LANG
2844 )
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 LANG_CODE.LANGUAGE_CODE,
2858 USERENV('LANG')
2859 );
2860 end loop;
2861 ELSIF 'UPDATE' = p_transaction_mode
2862 THEN
2863
2864 UPDATE EGO_MTL_SY_ITEMS_CHG_B
2865 SET DESCRIPTION = p_item_desc,
2866 ITEM_NUMBER = p_item_num
2867 where change_line_id = p_change_line_id;
2868
2869 END IF;
2870
2871
2872 END SAVE_ITEM_NUM_DESC;
2873
2874 END ENG_CHANGE_ATTR_UTIL;