[Home] [Help]
PACKAGE BODY: APPS.ENG_IMPL_ITEM_CHANGES_PKG
Source
1 PACKAGE BODY ENG_IMPL_ITEM_CHANGES_PKG AS
2 /* $Header: ENGITMIB.pls 120.41 2008/07/16 14:17:05 vggarg ship $ */
3
4
5 ----------------------------------------------------------------------------
6 -- Debug Profile option used to write Error_Handler.Write_Debug --
7 -- Profile option name = INV_DEBUG_TRACE ; --
8 -- User Profile Option Name = INV: Debug Trace --
9 -- Values: 1 (True) ; 0 (False) --
10 -- NOTE: This better than MRP_DEBUG which is used at many places. --
11 ----------------------------------------------------------------------------
12 G_DEBUG CONSTANT VARCHAR2(10) := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
13
14 -----------------------------------------------------------------------
15 -- These are the Constants to generate a New Line Character. --
16 -----------------------------------------------------------------------
17 G_CARRIAGE_RETURN VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(13);
18 G_LINE_FEED VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
19 -- Following prints ^M characters in the log file.
20 G_NEWLINE VARCHAR2(2) := G_LINE_FEED;
21
22
23 ---------------------------------------------------------------
24 -- API Return Status . --
25 ---------------------------------------------------------------
26 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
27 G_RET_STS_WARNING CONSTANT VARCHAR2(1) := 'W';
28 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
29 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
30
31 ---------------------------------------------------------------
32 -- Used for Error Reporting. --
33 ---------------------------------------------------------------
34 G_ERROR_TABLE_NAME VARCHAR2(30) ;
35 G_ERROR_ENTITY_CODE VARCHAR2(30) := 'EGO_ITEM';
36 G_OUTPUT_DIR VARCHAR2(512) ;
37 G_ERROR_FILE_NAME VARCHAR2(400) ;
38 G_BO_IDENTIFIER VARCHAR2(30) := 'ENG_CHANGE_IMPL';
39
40
41 ---------------------------------------------------------------
42 -- Introduced for 11.5.10, so that Java Conc Program can --
43 -- continue writing to the same Error Log File. --
44 ---------------------------------------------------------------
45 G_ERRFILE_PATH_AND_NAME VARCHAR2(10000);
46
47
48
49 ---------------------------------------------------------------
50 -- Message Type Text . --
51 ---------------------------------------------------------------
52 G_FND_MSG_TYPE_CONFIRMATION VARCHAR2(100) ;
53 G_FND_MSG_TYPE_ERROR VARCHAR2(100) ;
54 G_FND_MSG_TYPE_WARNING VARCHAR2(100) ;
55 G_FND_MSG_TYPE_INFORMATION VARCHAR2(100) ;
56
57 ---------------------------------------------------------------
58 -- Message Type Text . --
59 ---------------------------------------------------------------
60 G_ENG_MSG_TYPE_ERROR CONSTANT VARCHAR2(1) := Error_Handler.G_STATUS_ERROR ;
61 G_ENG_MSG_TYPE_WARNING CONSTANT VARCHAR2(1) := Error_Handler.G_STATUS_WARNING ;
62 G_ENG_MSG_TYPE_UNEXPECTED CONSTANT VARCHAR2(1) := Error_Handler.G_STATUS_UNEXPECTED ;
63 G_ENG_MSG_TYPE_FATAL CONSTANT VARCHAR2(1) := Error_Handler.G_STATUS_FATAL ;
64 G_ENG_MSG_TYPE_CONFIRMATION CONSTANT VARCHAR2(1) := 'C';
65 G_ENG_MSG_TYPE_INFORMATION CONSTANT VARCHAR2(1) := 'I' ;
66
67
68
69
70 ---------------------------------------------------------------
71 -- Private Global Variables . --
72 ---------------------------------------------------------------
73 TYPE LOCAL_VARCHAR_TABLE IS TABLE OF VARCHAR2(30)
74 INDEX BY BINARY_INTEGER;
75
76 G_OBJECT_NAME_TO_ID_CACHE LOCAL_VARCHAR_TABLE;
77
78
79 G_EGO_ITEM CONSTANT VARCHAR2(30) := 'EGO_ITEM';
80 G_EGO_APPL_ID CONSTANT NUMBER := 431 ;
81 G_ITEM_APPL_ID CONSTANT NUMBER := 401 ;
82 G_EGO_MASTER_ITEMS CONSTANT VARCHAR2(30) := 'EGO_MASTER_ITEMS' ;
83 G_EGO_ITEMMGMT_GROUP CONSTANT VARCHAR2(30) := 'EGO_ITEMMGMT_GROUP' ;
84 G_EGO_ITEM_GTIN_ATTRS CONSTANT VARCHAR2(30) := 'EGO_ITEM_GTIN_ATTRS' ;
85 G_EGO_ITEM_GTIN_MULTI_ATTRS CONSTANT VARCHAR2(30) := 'EGO_ITEM_GTIN_MULTI_ATTRS' ;
86
87
88 ---------------------------------------------------------------
89 -- Change Management ACD TYpe --
90 ---------------------------------------------------------------
91 G_ADD_ACD_TYPE CONSTANT VARCHAR2(10) := 'ADD';
92 G_CHANGE_ACD_TYPE CONSTANT VARCHAR2(10) := 'CHANGE';
93 G_DELETE_ACD_TYPE CONSTANT VARCHAR2(10) := 'DELETE';
94 G_HISTORY_ACD_TYPE CONSTANT VARCHAR2(10) := 'HISTORY';
95
96 ---------------------------------------------------------------
97 -- Change Management Tx TYpe --
98 ---------------------------------------------------------------
99 G_CREATE_TX_TYPE CONSTANT VARCHAR2(10) := 'CREATE'; --4th
100 G_UPDATE_TX_TYPE CONSTANT VARCHAR2(10) := 'UPDATE'; --2nd
101 G_DELETE_TX_TYPE CONSTANT VARCHAR2(10) := 'DELETE'; --1st
102 G_SYNC_TX_TYPE CONSTANT VARCHAR2(10) := 'SYNC'; --3rd
103
104 ---------------------------------------------------------------
105 -- Seesion Lang Info --
106 ---------------------------------------------------------------
107
108 -- Cached NLS values
109 -- 64 is drawned from v$nls_valid_values view.
110 G_NLS_LANGUAGE VARCHAR2(64);
111 G_NLS_TERRITORY VARCHAR2(64);
112 G_NLS_CHARSET VARCHAR2(64);
113
114
115 -----------------------------------------------------
116 -- This is a private additional mode for use in --
117 -- calls to Process_Row from Implement_Change_Line --
118 -----------------------------------------------------
119 G_IMPLEMENT_CREATE_MODE CONSTANT VARCHAR2(10) := 'IMP_CREATE';
120
121
122 ------------------------
123 -- Private Data Types --
124 ------------------------
125 TYPE LOCAL_COL_NV_PAIR_TABLE IS TABLE OF EGO_COL_NAME_VALUE_PAIR_OBJ
126 INDEX BY BINARY_INTEGER;
127
128
129
130 ----------------------------------------------------------
131 -- Write to Concurrent Log --
132 ----------------------------------------------------------
133
134 PROCEDURE Developer_Debug (p_msg IN VARCHAR2) IS
135 l_err_msg VARCHAR2(240);
136 BEGIN
137
138 FND_FILE.put_line(FND_FILE.LOG, p_msg);
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 l_err_msg := SUBSTRB(SQLERRM, 1,240);
143 FND_FILE.put_line(FND_FILE.LOG, 'LOGGING SQL ERROR => '||l_err_msg);
144 END;
145
146
147 -----------------------------------------------------------------
148 -- Write Debug statements to Log using Error Handler procedure --
149 -----------------------------------------------------------------
150 PROCEDURE Write_Debug (p_msg IN VARCHAR2)
151 IS
152 BEGIN
153
154 -- NOTE: No need to check for profile now, as Error_Handler checks
155 -- for Error_Handler.Get_Debug = 'Y' before writing to Debug Log.
156 -- If Profile set to TRUE --
157 -- IF (G_DEBUG = 1) THEN
158 -- Error_Handler.Write_Debug('['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] '|| p_msg);
159 -- END IF;
160
161 Error_Handler.Write_Debug('['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] '|| p_msg);
162
163 -- For Concurrent Request Log
164 Developer_Debug('['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] '|| p_msg);
165
166
167 /*
168 -- For OA/FND Log
169 BEGIN
170
171 -- FND Standard Log
172 -- FND_LOG.LEVEL_UNEXPECTED;
173 -- FND_LOG.LEVEL_ERROR;
174 -- FND_LOG.LEVEL_EXCEPTION;
175 -- FND_LOG.LEVEL_EVENT;
176 -- FND_LOG.LEVEL_PROCEDURE;
177 -- FND_LOG.LEVEL_STATEMENT;
178 -- G_DEBUG_LOG_HEAD := 'fnd.plsql.'||G_PKG_NAME||'.';
179
180
181 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
182 FND_LOG.STRING(log_level => p_log_level
183 ,module => G_DEBUG_LOG_HEAD||p_module
184 ,message => p_message
185 );
186 END IF;
187 NULL;
188 EXCEPTION
189 WHEN OTHERS THEN
190 RAISE;
191 END log_now;
192 */
193
194
195
196 END;
197
198
199 ----------------------------------------------------------
200 -- Internal procedure to open Debug Session. --
201 ----------------------------------------------------------
202 PROCEDURE open_debug_session_internal IS
203
204 CURSOR c_get_utl_file_dir IS
205 SELECT VALUE
206 FROM V$PARAMETER
207 WHERE NAME = 'utl_file_dir';
208
209 --local variables
210 l_log_output_dir VARCHAR2(512);
211 l_log_return_status VARCHAR2(99);
212 l_errbuff VARCHAR2(999);
213 BEGIN
214
215 Error_Handler.initialize();
216 Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
217
218 ---------------------------------------------------------------------------------
219 -- Commented on 12/17/2003 (PPEDDAMA). Open_Debug_Session should set the value
220 -- appropriately, so that when the Debug Session is successfully opened :
221 -- will return Error_Handler.Get_Debug = 'Y', else Error_Handler.Get_Debug = 'N'
222 ---------------------------------------------------------------------------------
223 -- Error_Handler.Set_Debug('Y');
224
225 OPEN c_get_utl_file_dir;
226 FETCH c_get_utl_file_dir INTO l_log_output_dir;
227 --developer_debug('UTL_FILE_DIR : '||l_log_output_dir);
228 IF c_get_utl_file_dir%FOUND THEN
229 ------------------------------------------------------
230 -- Trim to get only the first directory in the list --
231 ------------------------------------------------------
232 IF INSTR(l_log_output_dir,',') <> 0 THEN
233 l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
234 --developer_debug('Log Output Dir : '||l_log_output_dir);
235 END IF;
236
237
238 IF G_OUTPUT_DIR IS NOT NULL
239 THEN
240 l_log_output_dir := G_OUTPUT_DIR ;
241 END IF ;
242
243
244
245 IF G_ERROR_FILE_NAME IS NULL
246 THEN
247 G_ERROR_FILE_NAME := G_BO_IDENTIFIER ||'_'||
248 to_char(sysdate, 'DDMONYYYY_HH24MISS')||'.err';
249 END IF ;
250
251 --developer_debug('Trying to open the Error File => '||G_ERROR_FILE_NAME);
252
253 -----------------------------------------------------------------------
254 -- To open the Debug Session to write the Debug Log. --
255 -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --
256 -----------------------------------------------------------------------
257 Error_Handler.Open_Debug_Session(
258 p_debug_filename => G_ERROR_FILE_NAME
259 ,p_output_dir => l_log_output_dir
260 ,x_return_status => l_log_return_status
261 ,x_error_mesg => l_errbuff
262 );
263
264 ---------------------------------------------------------------
265 -- Introduced for 11.5.10, so that Java Conc Program can --
266 -- continue writing to the same Error Log File. --
267 ---------------------------------------------------------------
268 G_ERRFILE_PATH_AND_NAME := l_log_output_dir||'/'||G_ERROR_FILE_NAME;
269
270 developer_debug(' Log file location --> '||l_log_output_dir||'/'||G_ERROR_FILE_NAME ||' created with status '|| l_log_return_status);
271
272 IF (l_log_return_status <> G_RET_STS_SUCCESS) THEN
273 developer_debug('Unable to open error log file. Error => '||l_errbuff);
274 END IF;
275
276 END IF;--IF c_get_utl_file_dir%FOUND THEN
277 -- Bug : 4099546
278 CLOSE c_get_utl_file_dir;
279 END open_debug_session_internal;
280
281
282 -----------------------------------------------------------
283 -- Open the Debug Session, conditionally if the profile: --
284 -- INV Debug Trace is set to TRUE --
285 -----------------------------------------------------------
286 PROCEDURE Open_Debug_Session
287 ( p_debug IN VARCHAR2 := NULL
288 , p_output_dir IN VARCHAR2 := NULL
289 , p_file_name IN VARCHAR2 := NULL
290 )
291 IS
292
293 BEGIN
294 ----------------------------------------------------------------
295 -- Open the Debug Log Session, only if Profile is set to TRUE --
296 ----------------------------------------------------------------
297 IF (G_DEBUG = 1 OR FND_API.to_Boolean(p_debug)) THEN
298
299
300 G_OUTPUT_DIR := p_output_dir ;
301 G_ERROR_FILE_NAME := p_file_name ;
302 ----------------------------------------------------------------------------------
303 -- Opens Error_Handler debug session, only if Debug session is not already open.
304 -- Suggested by RFAROOK, so that multiple debug sessions are not open PER
305 -- Concurrent Request.
306 ----------------------------------------------------------------------------------
307 IF (Error_Handler.Get_Debug <> 'Y') THEN
308 Open_Debug_Session_Internal;
309 END IF;
310
311 END IF;
312
313 END Open_Debug_Session;
314
315 -----------------------------------------------------------------
316 -- Close the Debug Session, only if Debug is already Turned ON --
317 -----------------------------------------------------------------
318 PROCEDURE Close_Debug_Session IS
319
320 BEGIN
321 -----------------------------------------------------------------------------
322 -- Close Error_Handler debug session, only if Debug session is already open.
323 -----------------------------------------------------------------------------
324 IF (Error_Handler.Get_Debug = 'Y') THEN
325 Error_Handler.Close_Debug_Session;
326 END IF;
327
328 END Close_Debug_Session;
329
330
331 procedure GetNLSLanguage(language out nocopy varchar2,
332 territory out nocopy varchar2,
333 charset out nocopy varchar2)
334 is
335 tmpbuf varchar2(240);
336 pos1 number; -- position for '_'
337 pos2 number; -- position for '.'
338 begin
339 if (G_NLS_LANGUAGE is null) then
340 tmpbuf := userenv('LANGUAGE');
341 pos1 := instr(tmpbuf, '_');
342 pos2 := instr(tmpbuf, '.');
343
344 G_NLS_LANGUAGE := substr(tmpbuf, 1, pos1-1);
345 G_NLS_TERRITORY := substr(tmpbuf, pos1+1, pos2-pos1-1);
346 G_NLS_CHARSET := substr(tmpbuf, pos2+1);
347 end if;
348
349 GetNLSLanguage.language := G_NLS_LANGUAGE;
350 GetNLSLanguage.territory := G_NLS_TERRITORY;
351 GetNLSLanguage.charset := G_NLS_CHARSET;
352 end GetNLSLanguage;
353
354
355 --
356 -- GetSessionLanguage (PRIVATE)
357 -- Try to return the cached session language value.
358 -- If it is not cached yet, call the real query function.
359 --
360 function GetSessionLanguage
361 return varchar2
362 is
363 l_lang varchar2(64);
364 l_terr varchar2(64);
365 l_chrs varchar2(64);
366 begin
367 if (G_NLS_LANGUAGE is not null) then
368 return G_NLS_LANGUAGE;
369 end if;
370
371 GetNLSLanguage(l_lang, l_terr, l_chrs);
372 return l_lang;
373
374 end GetSessionLanguage;
375
376
377 --
378 -- SetNLSLanguage (PRIVATE)
379 -- Set the NLS Lanugage setting of current session
380 --
381 procedure SetNLSLanguage(p_language in varchar2,
382 p_territory in varchar2)
383 is
384 l_language varchar2(30);
385 l_territory varchar2(30);
386 begin
387 if (p_language = G_NLS_LANGUAGE) then
388 return;
389 end if;
390
391 l_language := ''''||p_language||'''';
392 l_territory := ''''||p_territory||'''';
393
394 DBMS_SESSION.SET_NLS('NLS_LANGUAGE', l_language);
395 DBMS_SESSION.SET_NLS('NLS_TERRITORY', l_territory);
396
397 -- update cache
398 G_NLS_LANGUAGE := p_language;
399 G_NLS_TERRITORY := p_territory;
400 end SetNLSLanguage;
401
402
403 -----------------------------------------------------------------
404 -- Get Change Id --
405 -----------------------------------------------------------------
406 FUNCTION GetChangeId (p_change_line_id IN NUMBER)
407 RETURN NUMBER
408 IS
409
410 l_change_id NUMBER;
411
412 CURSOR C (c_change_line_id IN NUMBER)
413 IS
414 SELECT change_id
415 FROM ENG_REVISED_ITEMS
416 WHERE revised_item_sequence_id = c_change_line_id ;
417
418
419
420 BEGIN
421
422
423 open c (p_change_line_id);
424 fetch c into l_change_id;
425 close c;
426
427
428 IF l_change_id IS NULL
429 THEN
430
431 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
432 FND_MESSAGE.Set_Token('OBJECT_NAME', 'GetChangeId:'|| 'no change Id');
433 FND_MSG_PUB.Add;
434 RAISE FND_API.G_EXC_ERROR;
435
436 END IF ;
437
438 RETURN l_change_id ;
439
440 END GetChangeId;
441
442
443 -----------------------------------------------------------------
444 -- Get Item Number --
445 -----------------------------------------------------------------
446 FUNCTION GetItemNumber
447 ( p_inventory_item_id IN NUMBER
448 , p_organization_id IN NUMBER
449 ) RETURN VARCHAR2
450 IS
451
452 CURSOR c_item_info (c_inventory_item_id NUMBER ,
453 c_organization_id NUMBER )
454 IS
455 SELECT item.concatenated_segments item_name
456 FROM MTL_SYSTEM_ITEMS_KFV item
457 WHERE item.organization_id = c_organization_id
458 AND item.inventory_item_id = c_inventory_item_id ;
459
460
461 l_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
462
463
464 BEGIN
465
466
467 FOR item_rec IN c_item_info ( c_inventory_item_id => p_inventory_item_id
468 , c_organization_id => p_organization_id)
469 LOOP
470
471 l_item_name := item_rec.item_name ;
472
473 END LOOP ;
474
475 return l_item_name ;
476
477 END GetItemNumber ;
478
479
480 -----------------------------------------------------------------
481 -- Get_Object_Id_From_Name --
482 -----------------------------------------------------------------
483
484 FUNCTION Get_Object_Id_From_Name (
485 p_object_name IN VARCHAR2
486 )
487 RETURN NUMBER
488 IS
489 l_object_name_table_index NUMBER;
490 l_object_id NUMBER;
491
492 BEGIN
493
494 Write_Debug('In Get_Object_Id_From_Name, starting for p_object_name '||p_object_name);
495
496 IF (G_OBJECT_NAME_TO_ID_CACHE.FIRST IS NOT NULL) THEN
497 l_object_name_table_index := G_OBJECT_NAME_TO_ID_CACHE.FIRST;
498 WHILE (l_object_name_table_index <= G_OBJECT_NAME_TO_ID_CACHE.LAST)
499 LOOP
500 EXIT WHEN (l_object_id IS NOT NULL);
501
502 IF (G_OBJECT_NAME_TO_ID_CACHE(l_object_name_table_index) = p_object_name) THEN
503 l_object_id := l_object_name_table_index;
504 END IF;
505
506 l_object_name_table_index := G_OBJECT_NAME_TO_ID_CACHE.NEXT(l_object_name_table_index);
507 END LOOP;
508 END IF;
509
510 IF (l_object_id IS NULL) THEN
511
512 SELECT OBJECT_ID INTO l_object_id
513 FROM FND_OBJECTS
514 WHERE OBJ_NAME = p_object_name;
515
516 G_OBJECT_NAME_TO_ID_CACHE(l_object_id) := p_object_name;
517
518 END IF;
519
520 Write_Debug('In Get_Object_Id_From_Name, done: returning l_object_id as '||l_object_id);
521
522 RETURN l_object_id;
523
524 EXCEPTION
525 WHEN NO_DATA_FOUND THEN
526 RETURN NULL;
527
528 END Get_Object_Id_From_Name;
529
530 -----------------------------------------------------------------
531 -- Get_Table_Columns_List --
532 -----------------------------------------------------------------
533 -- Copied from EGO_USER_ATTRS_DATA_PVT
534 -- Need to ask Ext Team to make this global
535 FUNCTION Get_Table_Columns_List (
536 p_application_id IN NUMBER
537 ,p_from_table_name IN VARCHAR2
538 ,p_from_cols_to_exclude_list IN VARCHAR2 DEFAULT NULL
539 ,p_from_table_alias_prefix IN VARCHAR2 DEFAULT NULL
540 ,p_to_table_name IN VARCHAR2 DEFAULT NULL
541 ,p_to_table_alias_prefix IN VARCHAR2 DEFAULT NULL
542 ,p_in_line_view_where_clause IN VARCHAR2 DEFAULT NULL
543 ,p_cast_date_cols_to_char IN BOOLEAN DEFAULT FALSE
544 ,p_exclude_dff IN BOOLEAN DEFAULT FALSE
545 )
546 RETURN VARCHAR2
547 IS
548
549 l_dynamic_sql VARCHAR2(20000);
550 l_table_column_names_list VARCHAR2(32767);
551 l_in_update_mode BOOLEAN;
552 l_update_expression VARCHAR2(32767);
553 l_column_name VARCHAR2(30);
554 l_column_type VARCHAR2(1);
555 l_exclude_table_name VARCHAR(30);
556
557 TYPE DYNAMIC_CUR IS REF CURSOR;
558 l_dynamic_cursor DYNAMIC_CUR;
559 l_skip_common_b_tl_cols VARCHAR2(32767);
560
561 BEGIN
562 -------------------------------------------------------------------
563 -- Build a query to fetch names of all columns we want to append --
564 -------------------------------------------------------------------
565 l_skip_common_b_tl_cols := '''INVENTORY_ITEM_ID'', ''ORGANIZATION_ID'', ''REVISION_ID'', ''LAST_UPDATED_BY'', ''CREATED_BY'' , ''CREATION_DATE'', ''LAST_UPDATE_LOGIN'' , ''LAST_UPDATE_DATE'' ,''ITEM_CATALOG_GROUP_ID'', ''ATTR_GROUP_ID''';
566 l_dynamic_sql := ' SELECT C.COLUMN_NAME, C.COLUMN_TYPE' ||
567 ' FROM FND_COLUMNS C, FND_TABLES T' ||
568 ' WHERE T.TABLE_NAME = :1'||
569 ' AND T.APPLICATION_ID = :2'||
570 ' AND C.APPLICATION_ID = T.APPLICATION_ID'||
571 ' AND C.TABLE_ID = T.TABLE_ID';
572
573 IF (p_from_cols_to_exclude_list IS NOT NULL) THEN
574 l_dynamic_sql := l_dynamic_sql||' AND C.COLUMN_NAME NOT IN ('||
575 p_from_cols_to_exclude_list||')';
576
577 END IF;
578
579 IF (SUBSTR(p_from_table_name,LENGTH(p_from_table_name)-1)='_B') THEN
580 l_exclude_table_name := SUBSTR(p_from_table_name,0,LENGTH(p_from_table_name)-1)||'TL';
581
582 l_dynamic_sql := l_dynamic_sql||' AND C.COLUMN_NAME NOT IN ('||
583 ' SELECT C_TL.COLUMN_NAME ' ||
584 ' FROM FND_COLUMNS C_TL, FND_TABLES T_TL' ||
585 ' WHERE T_TL.TABLE_NAME = '''|| l_exclude_table_name ||''''||
586 ' AND C_TL.APPLICATION_ID = T_TL.APPLICATION_ID'||
587 ' AND T_TL.APPLICATION_ID = T.APPLICATION_ID ' ||
588 ' AND C_TL.TABLE_ID = T_TL.TABLE_ID' ||
589 ' AND C_TL.COLUMN_NAME NOT IN ('||l_skip_common_b_tl_cols || '))';
590
591 END IF;
592
593 IF (p_exclude_dff) THEN
594 l_dynamic_sql := l_dynamic_sql||' AND C.COLUMN_NAME NOT LIKE ''ATTRIBUTE%'' ';
595 END IF;
596
597
598 l_dynamic_sql := l_dynamic_sql||' ORDER BY COLUMN_NAME';
599
600 -----------------------------------------------------------------------
601 -- Determine whether we're in update mode (in which, instead of just --
602 -- making a list of column names, we make an update expression using --
603 -- the two table names (and possibly aliases) passed in) --
604 -----------------------------------------------------------------------
605 l_in_update_mode := (p_to_table_name IS NOT NULL);
606
607 ----------------------------------------------------
608 -- Fetch all the table column names, prefixing or --
609 -- building an update expression as appropriate --
610 ----------------------------------------------------
611 OPEN l_dynamic_cursor FOR l_dynamic_sql USING p_from_table_name, p_application_id;
612 LOOP
613 FETCH l_dynamic_cursor INTO l_column_name, l_column_type;
614 EXIT WHEN l_dynamic_cursor%NOTFOUND;
615
616 -------------------------------------------
617 -- If we're casting Dates to char, do so --
618 -------------------------------------------
619 IF (p_cast_date_cols_to_char AND l_column_type = 'D') THEN
620 l_table_column_names_list := l_table_column_names_list||' TO_CHAR(';
621 END IF;
622
623 -------------------------------------------
624 -- If there's a from table alias, add it --
625 -------------------------------------------
626 IF (p_from_table_alias_prefix IS NOT NULL) THEN
627 l_table_column_names_list := l_table_column_names_list||p_from_table_alias_prefix||'.';
628 END IF;
629
630 ------------------------------------------------------------------
631 -- Whether or not there's an alias, add the current column name --
632 ------------------------------------------------------------------
633 l_table_column_names_list := l_table_column_names_list || l_column_name || ' ';
634
635 -----------------------------------------------------------
636 -- If we're in update mode, add a from column alias, the --
637 -- to column and its alias (we assume table aliases in --
638 -- update mode), and append to our update expression --
639 -----------------------------------------------------------
640 IF (l_in_update_mode) THEN
641 l_table_column_names_list := l_table_column_names_list ||
642 p_from_table_alias_prefix || '_' ||
643 l_column_name || ',' ||
644 p_to_table_alias_prefix || '.' ||
645 l_column_name || ' ' ||
646 p_to_table_alias_prefix || '_' ||
647 l_column_name;
648
649 l_update_expression := l_update_expression ||
650 p_to_table_alias_prefix || '_' ||
651 l_column_name || '=' ||
652 p_from_table_alias_prefix || '_' ||
653 l_column_name || ',';
654
655 END IF;
656
657 ---------------------------------------------------------------------
658 -- If we're casting Dates to char, close the parentheses correctly --
659 ---------------------------------------------------------------------
660 IF (p_cast_date_cols_to_char) THEN
661 IF (l_column_type = 'D') THEN
662 l_table_column_names_list := l_table_column_names_list||','''||
663 EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||''') '||
664 l_column_name;
665 END IF;
666 END IF;
667
668 ---------------------------------------------------------
669 -- Add a comma to end each loop regardless of the mode --
670 ---------------------------------------------------------
671 l_table_column_names_list := l_table_column_names_list || ',';
672
673 END LOOP;
674 CLOSE l_dynamic_cursor;
675
676 -----------------------------------------------------------------------
677 -- Trim the trailing ',' from l_table_column_names_list if necessary --
678 -----------------------------------------------------------------------
679 IF (LENGTH(l_table_column_names_list) > 0) THEN
680 l_table_column_names_list := RTRIM(l_table_column_names_list, ',');
681 END IF;
682
683 -----------------------------------------------------------------
684 -- Trim the trailing ',' from l_update_expression if necessary --
685 -----------------------------------------------------------------
686 IF (LENGTH(l_update_expression) > 0) THEN
687 l_update_expression := RTRIM(l_update_expression, ',');
688 END IF;
689
690 ----------------------------------------------------------------------
691 -- If we're in update mode, assemble the complete update expression --
692 ----------------------------------------------------------------------
693
694 IF (l_in_update_mode) THEN
695 l_table_column_names_list := 'UPDATE /*+ BYPASS_UJVC */ (SELECT '||l_table_column_names_list||
696 ' FROM '||p_from_table_name||' '||p_from_table_alias_prefix||
697 ','||p_to_table_name||' '||p_to_table_alias_prefix||' '||
698 p_in_line_view_where_clause||') SET '||l_update_expression;
699 END IF;
700 RETURN l_table_column_names_list;
701
702 END Get_Table_Columns_List;
703
704
705
706 PROCEDURE Build_Attr_Metadata_Table
707 ( p_application_id IN NUMBER
708 , p_attr_group_type IN VARCHAR2
709 , p_attr_group_name IN VARCHAR2 DEFAULT NULL
710 , x_attr_metadata_table OUT NOCOPY EGO_ATTR_METADATA_TABLE
711 ) IS
712
713 l_attr_metadata_table EGO_ATTR_METADATA_TABLE := EGO_ATTR_METADATA_TABLE();
714 l_attr_metadata_obj EGO_ATTR_METADATA_OBJ;
715 l_sql_query LONG;
716
717 CURSOR attrs_cursor (
718 cp_application_id IN NUMBER
719 ,cp_attr_group_type IN VARCHAR2
720 ,cp_attr_group_name IN VARCHAR2
721 ) IS
722 SELECT EXT.ATTR_ID,
723 FLX_EXT.ATTR_GROUP_ID,
724 FLX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME,
725 A.END_USER_COLUMN_NAME,
726 TL.FORM_LEFT_PROMPT,
727 EXT.DATA_TYPE,
728 FC.MEANING DATA_TYPE_MEANING,
729 A.COLUMN_SEQ_NUM,
730 EXT.UNIQUE_KEY_FLAG,
731 A.DEFAULT_VALUE,
732 EXT.INFO_1,
733 VS.MAXIMUM_SIZE,
734 A.REQUIRED_FLAG,
735 A.APPLICATION_COLUMN_NAME,
736 VS.FLEX_VALUE_SET_ID,
737 VS.VALIDATION_TYPE,
738 VS.MINIMUM_VALUE,
739 VS.MAXIMUM_VALUE,
740 EXT.UOM_CLASS,
741 UOM.UOM_CODE,
742 EXT.VIEW_IN_HIERARCHY_CODE,
743 EXT.EDIT_IN_HIERARCHY_CODE
744 FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT,
745 FND_DESCR_FLEX_COLUMN_USAGES A,
746 FND_DESCR_FLEX_COL_USAGE_TL TL,
747 EGO_FND_DF_COL_USGS_EXT EXT,
748 EGO_VS_FORMAT_CODES_V FC,
749 FND_FLEX_VALUE_SETS VS,
750 MTL_UNITS_OF_MEASURE UOM
751 WHERE FLX_EXT.APPLICATION_ID = cp_application_id
752 AND FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
753 AND ( FLX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
754 OR cp_attr_group_name IS NULL )
755 AND A.APPLICATION_ID = cp_application_id
756 AND A.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
757 AND ( A.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
758 OR cp_attr_group_name IS NULL )
759 AND TL.APPLICATION_ID = cp_application_id
760 AND TL.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
761 AND ( TL.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
762 OR cp_attr_group_name IS NULL )
763 AND EXT.APPLICATION_ID = cp_application_id
764 AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
765 AND ( EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
766 OR cp_attr_group_name IS NULL )
767 AND FC.LOOKUP_CODE(+) = EXT.DATA_TYPE
768 AND A.ENABLED_FLAG = 'Y'
769 AND TL.APPLICATION_COLUMN_NAME = A.APPLICATION_COLUMN_NAME
770 AND TL.LANGUAGE = USERENV('LANG')
771 AND EXT.APPLICATION_COLUMN_NAME = A.APPLICATION_COLUMN_NAME
772 AND A.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID (+)
773 AND UOM.UOM_CLASS(+) = EXT.UOM_CLASS
774 AND UOM.BASE_UOM_FLAG(+) = 'Y'
775 ORDER BY A.COLUMN_SEQ_NUM;
776
777 BEGIN
778
779
780 Write_Debug('Build_Attr_Metadata_Table . .. ' );
781 Write_Debug('-----------------------------------' );
782 Write_Debug('p_application_id: ' || to_char(p_application_id));
783 Write_Debug('p_attr_group_type:' || p_attr_group_type);
784 Write_Debug('p_attr_group_name:' || p_attr_group_name);
785 Write_Debug('-----------------------------------' );
786
787 ----------------------------------------------------------------------------
788 -- The SORT_ATTR_VALUES_FLAG flag records whether any Attributes in this --
789 -- collection have a Value Set of type "Table"; if so, we will need to --
790 -- sort the Attr values when we process a row in order to ensure that any --
791 -- bind values needed by the Value Set are converted before the Value Set --
792 -- is processed --
793 ----------------------------------------------------------------------------
794
795 -------------------------------------------------------
796 -- The UNIQUE_KEY_ATTRS_COUNT records how many Attrs --
797 -- in this Attribute Group are part of a Unique Key --
798 -------------------------------------------------------
799
800 --------------------------------------------------------------------
801 -- The TRANS_ATTRS_COUNT records how many translatable Attributes --
802 -- this Attribute Group has; it will be used in Update_Row --
803 --------------------------------------------------------------------
804
805 FOR attrs_rec IN attrs_cursor(p_application_id
806 ,p_attr_group_type
807 ,p_attr_group_name)
808 LOOP
809 l_attr_metadata_obj := EGO_ATTR_METADATA_OBJ(
810 attrs_rec.ATTR_ID
811 ,attrs_rec.ATTR_GROUP_ID
812 ,attrs_rec.ATTR_GROUP_NAME
813 ,attrs_rec.END_USER_COLUMN_NAME
814 ,attrs_rec.FORM_LEFT_PROMPT
815 ,attrs_rec.DATA_TYPE
816 ,attrs_rec.DATA_TYPE_MEANING
817 ,attrs_rec.COLUMN_SEQ_NUM
818 ,attrs_rec.UNIQUE_KEY_FLAG
819 ,attrs_rec.DEFAULT_VALUE
820 ,attrs_rec.INFO_1
821 ,attrs_rec.MAXIMUM_SIZE
822 ,attrs_rec.REQUIRED_FLAG
823 ,attrs_rec.APPLICATION_COLUMN_NAME
824 ,attrs_rec.FLEX_VALUE_SET_ID
825 ,attrs_rec.VALIDATION_TYPE
826 ,attrs_rec.MINIMUM_VALUE
827 ,attrs_rec.MAXIMUM_VALUE
828 ,attrs_rec.UOM_CLASS
829 ,attrs_rec.UOM_CODE
830 ,null -- DISP_TO_INT_VAL_QUERY
831 ,null -- INT_TO_DISP_VAL_QUERY
832 ,'N'
833 ,attrs_rec.VIEW_IN_HIERARCHY_CODE
834 ,attrs_rec.EDIT_IN_HIERARCHY_CODE
835 );
836
837 /*
838 IF (attrs_rec.UNIQUE_KEY_FLAG = 'Y') THEN
839
840 px_attr_group_metadata_obj.UNIQUE_KEY_ATTRS_COUNT :=
841 px_attr_group_metadata_obj.UNIQUE_KEY_ATTRS_COUNT + 1;
842
843 END IF;
844
845 IF (attrs_rec.DATA_TYPE = EGO_EXT_FWK_PUB.G_TRANS_TEXT_DATA_TYPE) THEN
846
847 px_attr_group_metadata_obj.TRANS_ATTRS_COUNT :=
848 px_attr_group_metadata_obj.TRANS_ATTRS_COUNT + 1;
849
850 END IF;
851
852
853 IF (attrs_rec.VALIDATION_TYPE = EGO_EXT_FWK_PUB.G_INDEPENDENT_VALIDATION_CODE OR
854 attrs_rec.VALIDATION_TYPE = EGO_EXT_FWK_PUB.G_TABLE_VALIDATION_CODE) THEN
855
856 -----------------------------------------------------------------
857 -- If this Attribute has a Value Set with Internal and Display --
858 -- Values, we build SQL to transform one into the other (and --
859 -- if the Value Set is of type "Table", we set the sort flag --
860 -- in our Attribute Group metadata object to 'Y') --
861 -----------------------------------------------------------------
862
863 Build_Sql_Queries_For_Value(attrs_rec.FLEX_VALUE_SET_ID
864 ,attrs_rec.VALIDATION_TYPE
865 ,px_attr_group_metadata_obj
866 ,l_attr_metadata_obj);
867 END IF;
868
869 ------------------------------------------------------------------
870 -- For hierarchy security, we need to keep track of whether any --
871 -- of the attributes requires propagation (EIH code of LP/AP) --
872 -- for leaf/all propagation --
873 ------------------------------------------------------------------
874 IF (attrs_rec.EDIT_IN_HIERARCHY_CODE = 'LP' OR
875 attrs_rec.EDIT_IN_HIERARCHY_CODE = 'AP') THEN
876
877 px_attr_group_metadata_obj.HIERARCHY_PROPAGATE_FLAG := 'Y';
878 EGO_USER_ATTRS_DATA_PVT.
879 -- DEBUG_MSG('In Build_Attr_Metadata_Table, found LP/AP: '||px_attr_group_metadata_obj.ATTR_GROUP_NAME||' '||attrs_rec.ATTR_ID, 2);
880
881 END IF;
882 */
883
884 l_attr_metadata_table.EXTEND();
885 l_attr_metadata_table(l_attr_metadata_table.LAST) := l_attr_metadata_obj;
886
887 END LOOP;
888
889 x_attr_metadata_table := l_attr_metadata_table ;
890
891 END Build_Attr_Metadata_Table;
892
893
894 -----------------------------------------------------------------
895 -- Check if Item Attr Change exists --
896 -----------------------------------------------------------------
897 FUNCTION CheckItemAttrChange (p_change_line_id IN NUMBER)
898 RETURN BOOLEAN
899 IS
900
901 l_change_line_id NUMBER;
902
903 CURSOR C IS
904 SELECT change_line_id
905 FROM EGO_MTL_SY_ITEMS_CHG_VL
906 WHERE change_line_id = p_change_line_id
907 AND implementation_date IS NULL
908 AND acd_type <> 'HISTORY'
909 AND rownum = 1;
910
911 BEGIN
912
913 open c;
914 fetch c into l_change_line_id;
915 if (c%notfound) then
916 close c;
917 RETURN FALSE;
918 end if;
919 close c;
920 RETURN TRUE ;
921
922 END CheckItemAttrChange ;
923
924
925
926 -----------------------------------------------------------------
927 -- Check if Item User Attr Change exists --
928 -----------------------------------------------------------------
929 FUNCTION CheckItemUserAttrChange (p_change_line_id IN NUMBER)
930 RETURN BOOLEAN
931 IS
932
933 l_change_line_id NUMBER;
934
935 CURSOR C IS
936 SELECT change_line_id
937 FROM EGO_ITEMS_ATTRS_CHANGES_VL
938 WHERE change_line_id = p_change_line_id
939 AND implementation_date IS NULL
940 AND acd_type <> 'HISTORY'
941 AND rownum = 1;
942
943 BEGIN
944
945 open c;
946 fetch c into l_change_line_id;
947 if (c%notfound) then
948 close c;
949 RETURN FALSE;
950 end if;
951 close c;
952 RETURN TRUE ;
953
954 END CheckItemUserAttrChange ;
955
956
957
958 -----------------------------------------------------------------
959 -- Check if Item GDSN Attr Change exists --
960 -----------------------------------------------------------------
961 FUNCTION CheckItemGDSNAttrChange (p_change_line_id IN NUMBER
962 ,p_gdsn_attr_group_type IN VARCHAR2 := NULL
963 )
964 RETURN BOOLEAN
965 IS
966
967 l_change_line_id NUMBER;
968
969 CURSOR C IS
970 SELECT change_line_id
971 FROM EGO_GTN_ATTR_CHG_VL
972 WHERE change_line_id = p_change_line_id
973 AND implementation_date IS NULL
974 AND acd_type <> 'HISTORY'
975 AND rownum = 1;
976
977
978 CURSOR C2 IS
979 SELECT change_line_id
980 FROM EGO_GTN_MUL_ATTR_CHG_VL
981 WHERE change_line_id = p_change_line_id
982 AND implementation_date IS NULL
983 AND acd_type <> 'HISTORY'
984 AND rownum = 1;
985
986
987 BEGIN
988
989 IF ( p_gdsn_attr_group_type IS NULL OR
990 p_gdsn_attr_group_type = G_EGO_ITEM_GTIN_ATTRS )
991 THEN
992 -- Check Single Row GDSN Attr Change
993 open c;
994 fetch c into l_change_line_id;
995 if (c%found) then
996 close c;
997 RETURN TRUE ;
998 end if;
999 close c;
1000
1001 END IF ;
1002
1003 IF ( p_gdsn_attr_group_type IS NULL OR
1004 p_gdsn_attr_group_type = G_EGO_ITEM_GTIN_MULTI_ATTRS )
1005 THEN
1006
1007 -- Check Multi Row GDSN Attr Change
1008 open c2;
1009 fetch c2 into l_change_line_id;
1010 if (c2%found) then
1011 close c2;
1012 RETURN TRUE ;
1013 end if;
1014 close c2;
1015
1016 END IF ;
1017
1018
1019 RETURN FALSE ;
1020
1021 END CheckItemGDSNAttrChange ;
1022
1023
1024 -----------------------------------------------------------------
1025 -- Check if Item Mfg Part Num Change exists --
1026 -----------------------------------------------------------------
1027 FUNCTION CheckItemMfgPartNumChange (p_change_line_id IN NUMBER)
1028 RETURN BOOLEAN
1029 IS
1030
1031 l_change_line_id NUMBER;
1032
1033 CURSOR C IS
1034 SELECT change_line_id
1035 FROM EGO_MFG_PART_NUM_CHGS
1036 WHERE change_line_id = p_change_line_id
1037 AND implmentation_date IS NULL -- Spell Miss implementation
1038 AND acd_type <> 'HISTORY'
1039 AND rownum = 1;
1040
1041 BEGIN
1042
1043 open c;
1044 fetch c into l_change_line_id;
1045 if (c%notfound) then
1046 close c;
1047 RETURN FALSE;
1048 end if;
1049 close c;
1050 RETURN TRUE ;
1051
1052 END CheckItemMfgPartNumChange ;
1053
1054
1055 -----------------------------------------------------------------
1056 -- Get Ext Id for Item GDSN Attr Production --
1057 -----------------------------------------------------------------
1058 FUNCTION Get_Ext_Id_For_GDSN_Single_Row ( p_inventory_item_id IN NUMBER
1059 , p_organization_id IN NUMBER )
1060 RETURN NUMBER
1061 IS
1062
1063 l_ext_id NUMBER ;
1064
1065 CURSOR C IS
1066 SELECT extension_id
1067 FROM EGO_ITEM_GTN_ATTRS_B
1068 WHERE inventory_item_id = p_inventory_item_id
1069 AND organization_id = p_organization_id
1070 AND revision_id IS NULL ;
1071
1072 BEGIN
1073
1074 open c;
1075 fetch c into l_ext_id;
1076 close c;
1077 RETURN l_ext_id ;
1078
1079 END Get_Ext_Id_For_GDSN_Single_Row ;
1080
1081
1082
1083 PROCEDURE impl_item_changes
1084 ( p_api_version IN NUMBER
1085 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
1086 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
1087 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
1088 , x_return_status OUT NOCOPY VARCHAR2
1089 , x_msg_count OUT NOCOPY NUMBER
1090 , x_msg_data OUT NOCOPY VARCHAR2
1091 , p_api_caller IN VARCHAR2 := NULL
1092 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
1093 , p_output_dir IN VARCHAR2 := NULL
1094 , p_debug_filename IN VARCHAR2 := NULL
1095 , p_change_id IN NUMBER := NULL
1096 , p_change_line_id IN NUMBER
1097 )
1098 IS
1099 l_api_name CONSTANT VARCHAR2(30) := 'IMPL_ITEM_CHANGES';
1100 l_api_version CONSTANT NUMBER := 1.0;
1101
1102 l_init_msg_list VARCHAR2(1) ;
1103 l_validation_level NUMBER ;
1104 l_commit VARCHAR2(1) ;
1105 l_write_msg_to_intftbl VARCHAR2(1) ;
1106
1107
1108 l_msg_data VARCHAR2(4000);
1109 l_msg_count NUMBER;
1110 l_return_status VARCHAR2(1);
1111 l_error_msg VARCHAR2(2000) ;
1112
1113 l_change_id NUMBER ;
1114 l_change_line_id NUMBER ;
1115
1116 l_found BOOLEAN ;
1117
1118 CURSOR getChangeLines (c_change_id NUMBER
1119 ,c_change_line_id NUMBER)
1120 IS
1121 SELECT revised_item_sequence_id
1122 FROM eng_revised_items
1123 WHERE change_id = c_change_id
1124 AND ( revised_item_sequence_id = c_change_line_id
1125 OR c_change_line_id IS NULL )
1126 AND implementation_date IS NULL
1127 ORDER BY scheduled_date ;
1128
1129
1130 BEGIN
1131 -- Standard call to check for call compatibility.
1132 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1133 p_api_version ,
1134 l_api_name ,
1135 G_PKG_NAME )
1136 THEN
1137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1138 END IF;
1139
1140 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
1141 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
1142 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
1143
1144
1145 IF FND_API.To_Boolean( l_commit ) THEN
1146 SAVEPOINT IMPL_ITEM_CHANGES;
1147 END IF;
1148
1149
1150 -- Initialize message list if p_init_msg_list is set to TRUE.
1151 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1152 FND_MSG_PUB.initialize;
1153 END IF;
1154
1155 -- Open Debug Session by a give param or profile option.
1156 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1157
1158 Write_Debug('After Open_Debug_Session');
1159 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
1160 Write_Debug('-----------------------------------------' );
1161 Write_Debug('p_api_version: ' || to_char(p_api_version));
1162 Write_Debug('p_init_msg_list:' || p_init_msg_list);
1163 Write_Debug('p_commit:' || p_commit);
1164 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
1165 Write_Debug('p_api_caller:' || p_api_caller);
1166 Write_Debug('p_change_id: ' || to_char(p_change_id));
1167 Write_Debug('p_change_line_id: ' || to_char(p_change_line_id));
1168 Write_Debug('-----------------------------------------' );
1169
1170 -- Initialize API return status to success
1171 x_return_status := G_RET_STS_SUCCESS;
1172 l_change_id := p_change_id ;
1173 l_change_line_id := p_change_line_id ;
1174
1175 -- API body
1176 -- Logic Here
1177 -- Init Local Vars
1178
1179 IF ( l_change_id IS NULL OR l_change_id <= 0 ) AND
1180 ( l_change_line_id IS NULL OR l_change_line_id <= 0 )
1181 THEN
1182 return ;
1183 END IF ;
1184
1185
1186 IF ( l_change_id IS NULL OR l_change_id <= 0 )
1187 THEN
1188 l_change_id := GetChangeId(p_change_line_id => l_change_line_id) ;
1189
1190 Write_Debug('Got Change Id: ' || to_char(l_change_id));
1191
1192 END IF ;
1193
1194
1195
1196 FOR revised_line_rec IN getChangeLines(l_change_id, l_change_line_id)
1197 LOOP
1198 l_change_line_id := revised_line_rec.revised_item_sequence_id;
1199
1200
1201 Write_Debug('Calling impl_rev_item_user_attr_chgs for Rev Item: ' || to_char(l_change_line_id));
1202
1203 impl_rev_item_user_attr_chgs
1204 (
1205 p_api_version => 1.0
1206 ,p_commit => FND_API.G_FALSE
1207 ,x_return_status => l_return_status
1208 ,x_msg_count => l_msg_count
1209 ,x_msg_data => l_msg_data
1210 ,p_change_id => l_change_id
1211 ,p_change_line_id => l_change_line_id
1212 ) ;
1213
1214
1215 Write_Debug('After Calling impl_rev_item_user_attr_chgs, Return Status: ' || l_return_status);
1216
1217
1218 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1219 THEN
1220
1221 x_return_status := l_return_status;
1222 x_msg_count := l_msg_count;
1223 x_msg_data := l_msg_data;
1224 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
1225 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_user_attr_chgs');
1226 FND_MSG_PUB.Add;
1227
1228 RAISE FND_API.G_EXC_ERROR;
1229
1230 END IF;
1231
1232
1233
1234 Write_Debug('Calling impl_rev_item_attr_changes for Rev Item: ' || to_char(l_change_line_id));
1235
1236
1237 impl_rev_item_attr_changes
1238 (
1239 p_api_version => 1.0
1240 ,p_commit => FND_API.G_FALSE
1241 ,x_return_status => l_return_status
1242 ,x_msg_count => l_msg_count
1243 ,x_msg_data => l_msg_data
1244 ,p_change_id => l_change_id
1245 ,p_change_line_id => l_change_line_id
1246 ) ;
1247
1248
1249 Write_Debug('After Calling impl_rev_item_attr_changes, Return Status: ' || l_return_status);
1250
1251
1252
1253 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1254 THEN
1255
1256 x_return_status := l_return_status;
1257 x_msg_count := l_msg_count;
1258 x_msg_data := l_msg_data;
1259 RAISE FND_API.G_EXC_ERROR;
1260
1261 END IF;
1262
1263 impl_rev_item_gdsn_attr_chgs
1264 (
1265 p_api_version => 1.0
1266 ,p_commit => FND_API.G_FALSE
1267 ,x_return_status => l_return_status
1268 ,x_msg_count => l_msg_count
1269 ,x_msg_data => l_msg_data
1270 ,p_change_id => l_change_id
1271 ,p_change_line_id => l_change_line_id
1272 ) ;
1273
1274
1275 Write_Debug('After Calling impl_rev_item_gdsn_attr_chgs, Return Status: ' || l_return_status);
1276
1277
1278 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1279 THEN
1280
1281 x_return_status := l_return_status;
1282 x_msg_count := l_msg_count;
1283 x_msg_data := l_msg_data;
1284 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
1285 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_gdsn_attr_chgs');
1286 FND_MSG_PUB.Add;
1287
1288 RAISE FND_API.G_EXC_ERROR;
1289
1290 END IF;
1291
1292
1293
1294 Write_Debug('Calling impl_rev_item_aml_changes for Rev Item: ' || to_char(l_change_line_id));
1295
1296 impl_rev_item_aml_changes
1297 (
1298 p_api_version => 1.0
1299 ,p_commit => FND_API.G_FALSE
1300 ,x_return_status => l_return_status
1301 ,x_msg_count => l_msg_count
1302 ,x_msg_data => l_msg_data
1303 ,p_change_id => l_change_id
1304 ,p_change_line_id => l_change_line_id
1305 ) ;
1306
1307
1308 Write_Debug('After calling impl_rev_item_aml_changes, Return Status: ' || l_return_status);
1309
1310
1311 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1312 THEN
1313
1314 x_return_status := l_return_status;
1315 x_msg_count := l_msg_count;
1316 x_msg_data := l_msg_data;
1317
1318 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
1319 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_aml_changes');
1320 FND_MSG_PUB.Add;
1321
1322 RAISE FND_API.G_EXC_ERROR;
1323
1324 END IF;
1325
1326
1327 END LOOP ; -- revised_line_rec LOOP
1328
1329 -- End of API body.
1330
1331
1332 -- Standard check of p_commit.
1333 IF FND_API.To_Boolean( l_commit ) THEN
1334
1335 Write_Debug('Commit impl_item_changes. . . ' );
1336
1337 COMMIT WORK;
1338 END IF;
1339
1340 -- Standard call to get message count and if count is 1, get message info.
1341 FND_MSG_PUB.Count_And_Get
1342 ( p_count => x_msg_count ,
1343 p_data => x_msg_data
1344 );
1345
1346
1347 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1348
1349 Write_Debug('Dumping Message number : '|| I );
1350 Write_Debug('DATA = '||replace(substr(FND_MSG_PUB.Get(I), 1, 200), chr(0), ' '));
1351
1352 END LOOP;
1353
1354
1355 Write_Debug('End of impl_item_changes . . . ' );
1356
1357
1358 -----------------------------------------------------
1359 -- Close Error Handler Debug Session.
1360 -----------------------------------------------------
1361 Close_Debug_Session;
1362
1363
1364 EXCEPTION
1365 WHEN FND_API.G_EXC_ERROR THEN
1366 Write_Debug('When G_EXC_ERROR Exception in impl_item_changes');
1367
1368 x_return_status := G_RET_STS_ERROR ;
1369
1370
1371 IF FND_API.To_Boolean( l_commit ) THEN
1372 Write_Debug('ROLLBACK Impl Item Changes to IMPL_ITEM_CHANGES. . ');
1373 ROLLBACK TO IMPL_ITEM_CHANGES;
1374 END IF;
1375
1376 FND_MSG_PUB.Count_And_Get
1377 ( p_count => x_msg_count ,
1378 p_data => x_msg_data
1379 );
1380
1381
1382
1383
1384 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1385
1386 Write_Debug('Dumping Message number : '|| I );
1387 Write_Debug('DATA = '||replace(substr(FND_MSG_PUB.Get(I), 1, 200), chr(0), ' '));
1388
1389 END LOOP;
1390
1391
1392 -----------------------------------------------------
1393 -- Close Error Handler Debug Session.
1394 -----------------------------------------------------
1395 Close_Debug_Session;
1396
1397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1398
1399 Write_Debug('When G_EXC_UNEXPECTED_ERROR Exception in impl_item_changes');
1400 x_return_status := G_RET_STS_UNEXP_ERROR ;
1401
1402 IF FND_API.To_Boolean( l_commit ) THEN
1403 Write_Debug('ROLLBACK Impl Item Changes to IMPL_ITEM_CHANGES. . ');
1404 ROLLBACK TO IMPL_ITEM_CHANGES;
1405 END IF;
1406
1407 FND_MSG_PUB.Count_And_Get
1408 ( p_count => x_msg_count ,
1409 p_data => x_msg_data
1410 );
1411
1412
1413 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1414
1415 Write_Debug('Dumping Message number : '|| I );
1416 Write_Debug('DATA = '||replace(substr(FND_MSG_PUB.Get(I), 1, 200), chr(0), ' '));
1417
1418 END LOOP;
1419
1420 -----------------------------------------------------
1421 -- Close Error Handler Debug Session.
1422 -----------------------------------------------------
1423 Close_Debug_Session;
1424
1425 WHEN OTHERS THEN
1426 Write_Debug('When OTHERS Exception in impl_item_changes');
1427 x_return_status := G_RET_STS_UNEXP_ERROR ;
1428
1429 IF FND_API.To_Boolean( l_commit ) THEN
1430 Write_Debug('ROLLBACK Impl Item Changes to IMPL_ITEM_CHANGES. . ');
1431 ROLLBACK TO IMPL_ITEM_CHANGES;
1432 END IF;
1433
1434 Write_Debug('When Others Exception ' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
1435
1436 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
1437 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_item_changes for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
1438 FND_MSG_PUB.Add;
1439
1440
1441 IF FND_MSG_PUB.Check_Msg_Level
1442 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1443 THEN
1444 FND_MSG_PUB.Add_Exc_Msg
1445 ( G_PKG_NAME ,
1446 l_api_name
1447 );
1448 END IF;
1449
1450 FND_MSG_PUB.Count_And_Get
1451 ( p_count => x_msg_count ,
1452 p_data => x_msg_data
1453 );
1454
1455
1456 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1457
1458 Write_Debug('Dumping Message number : '|| I );
1459 Write_Debug('DATA = '||replace(substr(FND_MSG_PUB.Get(I), 1, 200), chr(0), ' '));
1460
1461 END LOOP;
1462
1463
1464 -----------------------------------------------------
1465 -- Close Error Handler Debug Session.
1466 -----------------------------------------------------
1467 Close_Debug_Session;
1468
1469 END impl_item_changes;
1470
1471
1472 FUNCTION Get_Process_Item_Param(p_db_col_name IN VARCHAR2)
1473 RETURN VARCHAR2
1474 IS
1475 l_param_name VARCHAR2(31) ;
1476
1477 BEGIN
1478
1479 IF (p_db_col_name = 'ENFORCE_SHIP_TO_LOCATION_CODE' )
1480 THEN
1481
1482 l_param_name := 'P_' || SUBSTR(p_db_col_name, 1, 26) ;
1483
1484 ELSIF (p_db_col_name = 'PROCESS_EXECUTION_ENABLED_FLAG' )
1485 THEN
1486
1487 l_param_name := 'P_' || SUBSTR(p_db_col_name, 1, 28) ;
1488
1489 --added this for bug 5177385
1490 ELSIF (p_db_col_name = 'RESTRICT_SUBINVENTORIES_CODE' OR p_db_col_name = 'SERVICE_DURATION_PERIOD_CODE' )
1491 THEN
1492
1493 l_param_name := 'P_' || SUBSTR(p_db_col_name, 1, 27) ;
1494
1495 ELSIF (LENGTH(p_db_col_name) >= 29)
1496 THEN
1497
1498 l_param_name := 'P_' || SUBSTR(p_db_col_name, 1, 27) ;
1499
1500 ELSE
1501 l_param_name := 'P_' || p_db_col_name ;
1502
1503 END IF ;
1504
1505
1506 RETURN l_param_name ;
1507
1508 END Get_Process_Item_Param ;
1509
1510
1511 PROCEDURE impl_rev_item_attr_changes
1512 ( p_api_version IN NUMBER
1513 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
1514 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
1515 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
1516 , x_return_status OUT NOCOPY VARCHAR2
1517 , x_msg_count OUT NOCOPY NUMBER
1518 , x_msg_data OUT NOCOPY VARCHAR2
1519 , p_api_caller IN VARCHAR2 := NULL
1520 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
1521 , p_output_dir IN VARCHAR2 := NULL
1522 , p_debug_filename IN VARCHAR2 := NULL
1523 , p_change_id IN NUMBER := NULL
1524 , p_change_line_id IN NUMBER
1525 )
1526 IS
1527
1528 l_api_name CONSTANT VARCHAR2(30) := 'IMPL_REV_ITEM_ATTR_CHGS';
1529 l_api_version CONSTANT NUMBER := 1.0;
1530
1531 l_init_msg_list VARCHAR2(1) ;
1532 l_validation_level NUMBER ;
1533 l_commit VARCHAR2(1) ;
1534
1535 l_msg_data VARCHAR2(4000);
1536 l_msg_count NUMBER;
1537 l_return_status VARCHAR2(1);
1538 l_message_list Error_Handler.Error_Tbl_Type ;
1539 l_msg_index NUMBER ;
1540
1541 l_found BOOLEAN ;
1542
1543 l_change_id NUMBER;
1544 l_change_line_id NUMBER;
1545
1546 l_dummy_c NUMBER;
1547 l_dummy_r NUMBER;
1548 l_object_id NUMBER;
1549
1550
1551 l_production_b_table_name VARCHAR2(30);
1552 l_production_tl_table_name VARCHAR2(30);
1553 l_production_vl_name VARCHAR2(30);
1554 l_change_b_table_name VARCHAR2(30);
1555 l_change_tl_table_name VARCHAR2(30);
1556
1557 l_cols_to_exclude_list VARCHAR2(2000);
1558 l_chg_col_names_list VARCHAR2(32767);
1559 l_b_chg_cols_list VARCHAR2(32767);
1560 l_tl_chg_cols_list VARCHAR2(10000);
1561 l_history_b_chg_cols_list VARCHAR2(32767);
1562 l_history_tl_chg_cols_list VARCHAR2(10000);
1563 l_history_b_prod_cols_list VARCHAR2(32767);
1564 l_history_tl_prod_cols_list VARCHAR2(10000);
1565
1566 l_dynamic_sql VARCHAR2(32767); --the largest a VARCHAR2 can be
1567 l_utility_dynamic_sql VARCHAR2(32767); --the largest a VARCHAR2 can be
1568 l_cursor_id NUMBER;
1569 l_column_count NUMBER;
1570 l_dummy NUMBER;
1571 l_desc_table DBMS_SQL.Desc_Tab;
1572 l_retrieved_value VARCHAR2(1000);
1573 l_current_column_index NUMBER;
1574
1575 l_inventory_item_id NUMBER ;
1576 l_organization_id NUMBER ;
1577 l_item_number MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
1578 l_item_desc MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE;
1579 l_out_inventory_item_id NUMBER ;
1580 l_out_organization_id NUMBER ;
1581 l_process_control VARCHAR2(30) ;
1582 l_process_item NUMBER ;
1583
1584 -- l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1585 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE := EGO_USER_ATTR_DATA_TABLE();
1586 l_attr_metadata_obj EGO_ATTR_METADATA_OBJ;
1587
1588 l_mode_for_current_row VARCHAR2(10);
1589 l_current_acd_type VARCHAR2(30);
1590 l_current_row_language VARCHAR2(30);
1591 l_current_row_source_lang VARCHAR2(30);
1592 l_current_column_name VARCHAR2(30);
1593 l_attr_group_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
1594 l_attr_metadata_table EGO_ATTR_METADATA_TABLE := EGO_ATTR_METADATA_TABLE() ;
1595
1596 l_num_value NUMBER ;
1597 l_char_value VARCHAR2(1000) ;
1598 l_date_value DATE ;
1599 l_date_value_char VARCHAR2(30) ;
1600
1601
1602 -- l_uom_column_nv_pairs LOCAL_COL_NV_PAIR_TABLE;
1603 -- l_uom_nv_pairs_index NUMBER := 0;
1604 -- l_data_level_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1605 -- l_current_dl_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1606 -- l_ext_table_metadata_obj EGO_EXT_TABLE_METADATA_OBJ;
1607 -- l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1608 -- l_dummy_err_msg_name VARCHAR2(30);
1609 -- l_token_table ERROR_HANDLER.Token_Tbl_Type;
1610 -- l_current_uom_col_nv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
1611 -- l_attr_col_name_for_uom_col VARCHAR2(30);
1612
1613 BEGIN
1614
1615 -- Standard call to check for call compatibility.
1616 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1617 p_api_version ,
1618 l_api_name ,
1619 G_PKG_NAME )
1620 THEN
1621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1622 END IF;
1623
1624 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
1625 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
1626 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
1627
1628 IF FND_API.To_Boolean( l_commit ) THEN
1629 -- Standard Start of API savepoint
1630 SAVEPOINT IMPL_REV_ITEM_ATTR_CHGS;
1631 END IF;
1632
1633
1634 -- Initialize message list if p_init_msg_list is set to TRUE.
1635 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1636 FND_MSG_PUB.initialize;
1637 END IF;
1638
1639 -- Open Debug Session by a give param or profile option.
1640 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1641
1642 Write_Debug('After Open_Debug_Session');
1643 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
1644 Write_Debug('-----------------------------------------' );
1645 Write_Debug('p_api_version: ' || to_char(p_api_version));
1646 Write_Debug('p_init_msg_list:' || p_init_msg_list);
1647 Write_Debug('p_commit:' || p_commit);
1648 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
1649 Write_Debug('p_api_caller:' || p_api_caller);
1650 Write_Debug('p_change_id: ' || to_char(p_change_id));
1651 Write_Debug('p_change_line_id: ' || to_char(p_change_line_id));
1652 Write_Debug('-----------------------------------------' );
1653
1654 -- Initialize API return status to success
1655 x_return_status := G_RET_STS_SUCCESS;
1656
1657 -- API body
1658 -- Logic Here
1659 -- Init Local Vars
1660
1661
1662 l_change_id := p_change_id;
1663 l_change_line_id := p_change_line_id;
1664
1665 IF ( l_change_id IS NULL OR l_change_id <= 0 )
1666 THEN
1667 l_change_id := GetChangeId(p_change_line_id => l_change_line_id) ;
1668
1669 Write_Debug('Got Change Id: ' || to_char(l_change_id));
1670
1671 END IF ;
1672
1673
1674 Write_Debug('Check Item Attr Change exists for Rev Item: ' || to_char(l_change_line_id));
1675
1676 l_found := CheckItemAttrChange(p_change_line_id => l_change_line_id) ;
1677 IF NOT l_found THEN
1678
1679 Write_Debug('Item Attr Change not found for ' || to_char(l_change_line_id));
1680 RETURN ;
1681
1682 END IF ;
1683
1684 -----------------------------------------------
1685 -- First, we get the Object ID for our calls --
1686 -----------------------------------------------
1687 l_object_id := Get_Object_Id_From_Name(G_EGO_ITEM);
1688
1689
1690
1691 -- So far TL and VL are not registerd.
1692 -- We will use this in future
1693 -----------------------------------------------
1694 --We get the meta data for Object ID for our calls --
1695 -----------------------------------------------
1696 SELECT EXT_TABLE_NAME, EXT_TL_TABLE_NAME, EXT_VL_NAME
1697 INTO l_production_b_table_name, l_production_tl_table_name, l_production_vl_name
1698 FROM EGO_ATTR_GROUP_TYPES_V
1699 WHERE APPLICATION_ID = G_EGO_APPL_ID
1700 AND ATTR_GROUP_TYPE = G_EGO_MASTER_ITEMS ;
1701
1702
1703
1704 -- MK Need to get from Change Context
1705 l_change_b_table_name := 'EGO_MTL_SY_ITEMS_CHG_B' ;
1706 l_change_tl_table_name := 'EGO_MTL_SY_ITEMS_CHG_TL' ;
1707 -- l_production_b_table_name := 'MTL_SYSTEM_ITEMS_B' ;
1708 l_production_tl_table_name := 'MTL_SYSTEM_ITEMS_TL' ;
1709
1710 ---------------------------------------------------------------
1711 -- Next, we add to the lists the rest of the columns that we --
1712 -- either want to get explicitly or don't want to get at all --
1713 ---------------------------------------------------------------
1714 l_chg_col_names_list := 'B.INVENTORY_ITEM_ID,B.ORGANIZATION_ID,B.ACD_TYPE,B.ITEM_NUMBER,B.DESCRIPTION';
1715
1716 l_cols_to_exclude_list := '''INVENTORY_ITEM_ID'',''ORGANIZATION_ID'','||
1717 '''ACD_TYPE'',''ATTR_GROUP_ID'',''EXTENSION_ID'','||
1718 '''CHANGE_ID'',''CHANGE_LINE_ID'',''IMPLEMENTATION_DATE'','||
1719 '''CREATED_BY'',''CREATION_DATE'',''LAST_UPDATED_BY'','||
1720 '''LAST_UPDATE_DATE'',''LAST_UPDATE_LOGIN'','||
1721 '''PROGRAM_ID'',''PROGRAM_UPDATE_DATE'',''REQUEST_ID'' ,'||
1722 '''PROGRAM_APPLICATION_ID'',''EGO_MASTER_ITEMS_DFF_CTX'', '||
1723 '''STYLE_ITEM_FLAG'',''STYLE_ITEM_ID'',''GDSN_OUTBOUND_ENABLED_FLAG'','||
1724 '''ITEM_NUMBER'',''DESCRIPTION'',''LAST_SUBMITTED_NIR_ID'',''DEFAULT_MATERIAL_STATUS_ID''' ;
1725 /*Added for bug 6764240 the column LAST_SUBMITTED_NIR_ID*/
1726 /*Added for bug 7257989 the column DEFAULT_MATERIAL_STATUS_ID*/
1727
1728 ----------------------------------------------------------
1729 -- Get lists of columns for the B and TL pending tables --
1730 -- (i.e., all Attr cols and the language cols from TL) --
1731 ----------------------------------------------------------
1732
1733 Write_Debug('Get lists of columns for the Pending Change B ' || l_change_b_table_name );
1734
1735 l_b_chg_cols_list := Get_Table_Columns_List(
1736 -- p_application_id => G_EGO_APPL_ID
1737 p_application_id => G_ITEM_APPL_ID
1738 -- ,p_from_table_name => l_change_b_table_name
1739 -- Need to chagne this later
1740 ,p_from_table_name => l_production_b_table_name
1741 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
1742 ,p_from_table_alias_prefix => 'B'
1743 ,p_cast_date_cols_to_char => TRUE
1744 ,p_exclude_dff => TRUE
1745 );
1746
1747
1748 Write_Debug('Get lists of columns for the Pending Change TL ' || l_change_tl_table_name );
1749
1750 l_tl_chg_cols_list := Get_Table_Columns_List(
1751 -- p_application_id => G_EGO_APPL_ID
1752 p_application_id => G_ITEM_APPL_ID
1753 -- ,p_from_table_name => l_change_tl_table_name
1754 ,p_from_table_name => 'MTL_SYSTEM_ITEMS_TL'
1755 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
1756 ,p_from_table_alias_prefix => 'TL'
1757 ,p_cast_date_cols_to_char => TRUE
1758 );
1759
1760 --------------------------------------------------------
1761 -- While we're getting lists of columns, we also get --
1762 -- lists for later use in copying old production rows --
1763 -- into the pending tables as HISTORY rows --
1764 --------------------------------------------------------
1765
1766 Write_Debug('Get lists of columns for the Pending Change History B ' || l_change_b_table_name );
1767
1768 l_history_b_chg_cols_list := Get_Table_Columns_List(
1769 -- p_application_id => G_EGO_APPL_ID
1770 p_application_id => G_ITEM_APPL_ID
1771 -- ,p_from_table_name => l_change_b_table_name
1772 -- Need to chagne this later
1773 ,p_from_table_name => l_production_b_table_name
1774 ,p_from_table_alias_prefix => 'CT'
1775 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
1776 -- For Now
1777 ,p_exclude_dff => TRUE
1778 );
1779
1780 Write_Debug('Get lists of columns for the Pending Change History TL ' || l_change_b_table_name );
1781
1782 l_history_tl_chg_cols_list := Get_Table_Columns_List(
1783 -- p_application_id => G_EGO_APPL_ID
1784 p_application_id => G_ITEM_APPL_ID
1785 -- ,p_from_table_name => l_change_tl_table_name
1786 ,p_from_table_name => 'MTL_SYSTEM_ITEMS_TL'
1787 ,p_from_table_alias_prefix => 'CT'
1788 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
1789 );
1790
1791
1792 Write_Debug('Get lists of columns for the Production History B ' || l_change_b_table_name );
1793
1794 l_history_b_prod_cols_list := Get_Table_Columns_List(
1795 p_application_id => G_ITEM_APPL_ID
1796 ,p_from_table_name => l_production_b_table_name
1797 ,p_from_table_alias_prefix => 'PT'
1798 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
1799 -- For Now
1800 ,p_exclude_dff => TRUE
1801 );
1802
1803 Write_Debug('Get lists of columns for the Production History TL ' || l_change_b_table_name );
1804
1805
1806 l_history_tl_prod_cols_list := Get_Table_Columns_List(
1807 p_application_id => G_ITEM_APPL_ID
1808 ,p_from_table_name => l_production_tl_table_name
1809 ,p_from_table_alias_prefix => 'PT'
1810 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
1811 );
1812
1813
1814
1815 Write_Debug('Get Attribute Meta for All Master Item AG . .. ' );
1816
1817 -------------------------------------------------
1818 -- Get Attribute Meta for All Master Item AG --
1819 -------------------------------------------------
1820 Build_Attr_Metadata_Table
1821 (
1822 p_application_id => G_EGO_APPL_ID
1823 , p_attr_group_type => G_EGO_MASTER_ITEMS
1824 , p_attr_group_name => NULL
1825 , x_attr_metadata_table => l_attr_metadata_table
1826 ) ;
1827
1828 Write_Debug('After getting Attribute Meta for All Master Item AG . . . ' );
1829
1830
1831
1832 -------------------------------------------------
1833 -- Now we build the SQL for our dynamic cursor --
1834 -------------------------------------------------
1835
1836 Write_Debug('Now we build the SQL for our dynamic cursor . . . ' );
1837
1838 l_dynamic_sql := 'SELECT '||l_chg_col_names_list||','||
1839 l_b_chg_cols_list||','||
1840 l_tl_chg_cols_list||
1841 ' FROM '||l_change_b_table_name ||' B,'||
1842 l_change_tl_table_name ||' TL'||
1843 ' WHERE B.ACD_TYPE <> ''HISTORY'' AND B.IMPLEMENTATION_DATE IS NULL'||
1844 ' AND B.ACD_TYPE = TL.ACD_TYPE'||
1845 ' AND B.CHANGE_LINE_ID = TL.CHANGE_LINE_ID'||
1846 ' AND TL.LANGUAGE = userenv(''LANG'')'||
1847 ' AND B.CHANGE_LINE_ID = :1';
1848
1849
1850
1851 Write_Debug('SQL:' || l_dynamic_sql );
1852
1853
1854 l_cursor_id := DBMS_SQL.Open_Cursor;
1855 DBMS_SQL.Parse(l_cursor_id, l_dynamic_sql, DBMS_SQL.Native);
1856 DBMS_SQL.Bind_Variable(l_cursor_id, ':1', l_change_line_id);
1857 DBMS_SQL.Describe_Columns(l_cursor_id, l_column_count, l_desc_table);
1858
1859
1860 FOR i IN 1 .. l_column_count
1861 LOOP
1862
1863 --
1864 -- NOTE: ASSUMPTION: no PKs will ever be DATE objects
1865 --
1866 -------------------------------------------------------------
1867 -- We define all columns as VARCHAR2(1000) for convenience --
1868 -------------------------------------------------------------
1869 DBMS_SQL.Define_Column(l_cursor_id, i, l_retrieved_value, 1000);
1870 END LOOP;
1871
1872
1873 Write_Debug('Execute our dynamic query . . .' );
1874
1875
1876 ----------------------------------
1877 -- Execute our dynamic query... --
1878 ----------------------------------
1879 l_dummy := DBMS_SQL.Execute(l_cursor_id);
1880
1881
1882 Write_Debug('After Executing our dynamic query . . .' );
1883
1884 ----------------------------------------------------
1885 -- ...then loop through the result set, gathering
1886 -- the column values
1887 ----------------------------------------------------
1888 WHILE (DBMS_SQL.Fetch_Rows(l_cursor_id) > 0)
1889 LOOP
1890
1891
1892 Write_Debug('loop through the result set, gathering the column values . . .' );
1893
1894 l_current_column_index := 1;
1895 l_attr_name_value_pairs.DELETE();
1896
1897 ------------------------------------
1898 -- Get the PK values for this row --
1899 ------------------------------------
1900 -- Item Id
1901 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
1902 l_current_column_index := l_current_column_index + 1;
1903 -- l_pk_column_name_value_pairs(1).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
1904 l_inventory_item_id := TO_NUMBER(l_retrieved_value) ;
1905
1906
1907
1908 Write_Debug('Got Item Id ' || to_char(l_inventory_item_id));
1909
1910 -- Org Id
1911 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
1912 l_current_column_index := l_current_column_index + 1;
1913 -- l_pk_column_name_value_pairs(2).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
1914 l_organization_id := TO_NUMBER(l_retrieved_value) ;
1915
1916
1917 Write_Debug('Got Org Id ' || to_char(l_organization_id));
1918
1919 ----------------------------
1920 -- Determine the ACD Type --
1921 ----------------------------
1922 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
1923 l_current_column_index := l_current_column_index + 1;
1924 l_current_acd_type := l_retrieved_value;
1925
1926 Write_Debug('Got Acd Id ' || l_current_acd_type);
1927
1928 ----------------------------
1929 -- Determine the generated Item Number
1930 ----------------------------
1931 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
1932 l_current_column_index := l_current_column_index + 1;
1933 l_item_number := l_retrieved_value;
1934
1935 Write_Debug('Got Item Number ' || l_item_number);
1936
1937 ----------------------------
1938 -- Determine the generated Item Number
1939 ----------------------------
1940 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
1941 l_current_column_index := l_current_column_index + 1;
1942 l_item_desc := l_retrieved_value;
1943
1944 Write_Debug('Got Item desc ' || l_item_desc);
1945
1946
1947
1948 -------------------------------------------------------------------
1949 -- Now we loop through the rest of the columns assigning values --
1950 -- to Attr data objects, which we add to a table of such objects --
1951 -------------------------------------------------------------------
1952 FOR i IN l_current_column_index .. l_column_count
1953 LOOP
1954
1955 -----------------------------------------------
1956 -- Get the current column name and its value --
1957 -----------------------------------------------
1958 l_current_column_name := l_desc_table(i).COL_NAME;
1959 DBMS_SQL.Column_Value(l_cursor_id, i, l_retrieved_value);
1960
1961 ------------------------------------------------------------------------
1962 -- See whether the current column belongs to a User-Defined Attribute --
1963 ------------------------------------------------------------------------
1964 l_attr_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Find_Metadata_For_Attr(
1965 p_attr_metadata_table => l_attr_metadata_table
1966 ,p_db_column_name => l_current_column_name
1967 );
1968
1969
1970 ------------------------------------------------
1971 -- If the current column is an Attr column... --
1972 ------------------------------------------------
1973 IF (l_attr_metadata_obj IS NOT NULL AND
1974 l_attr_metadata_obj.ATTR_NAME IS NOT NULL AND
1975 l_current_column_index IS NOT NULL )
1976 THEN
1977
1978 -----------------------------------------------------
1979 -- ...then we add its value to our Attr data table
1980 -- Note: set column name as ATTR_NAME to use it for Proces_Item param
1981 -----------------------------------------------------
1982 l_attr_name_value_pairs.EXTEND();
1983 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST) :=
1984 EGO_USER_ATTR_DATA_OBJ(
1985 1
1986 ,l_current_column_name -- ,l_attr_metadata_obj.ATTR_NAME
1987 ,null -- ATTR_VALUE_STR
1988 ,null -- ATTR_VALUE_NUM
1989 ,null -- ATTR_VALUE_DATE
1990 ,null -- ATTR_DISP_VALUE
1991 ,null -- ATTR_UNIT_OF_MEASURE (will be set below if necessary)
1992 ,-1
1993 );
1994
1995 --------------------------------------------------------
1996 -- We assign l_retrieved_value according to data type --
1997 --------------------------------------------------------
1998 IF (l_attr_metadata_obj.DATA_TYPE_CODE = 'N') THEN
1999 -----------------------------
2000 -- We deal with UOMs below --
2001 -----------------------------
2002 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_NUM :=
2003 TO_NUMBER(l_retrieved_value);
2004 ELSIF (l_attr_metadata_obj.DATA_TYPE_CODE = 'X') THEN
2005 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_DATE :=
2006 TRUNC(TO_DATE(l_retrieved_value, EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT));
2007 ELSIF (l_attr_metadata_obj.DATA_TYPE_CODE = 'Y') THEN
2008 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_DATE :=
2009 TO_DATE(l_retrieved_value, EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT);
2010 ELSE
2011 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_STR :=
2012 l_retrieved_value;
2013 END IF;
2014
2015 -------------------------------------------------------------------------
2016 -- No need to process UOM for Item Master AG Type
2017 -- ELSIF (INSTR(l_current_column_name, 'UOM_') = 1) THEN
2018 --
2019 --------------------------------------------
2020 -- Store the UOM column's name and value --
2021 -- in a PL/SQL table for assignment below --
2022 --------------------------------------------
2023 -- l_uom_nv_pairs_index := l_uom_nv_pairs_index + 1;
2024 -- l_uom_column_nv_pairs(l_uom_nv_pairs_index) :=
2025 -- EGO_COL_NAME_VALUE_PAIR_OBJ(l_current_column_name, l_retrieved_value);
2026 --
2027 -------------------------------------------------------------------------
2028
2029 ELSIF (l_current_column_name = 'LANGUAGE') THEN
2030
2031 -------------------------------------------------------
2032 -- Determine the Language for passing to Process_Row --
2033 -------------------------------------------------------
2034 l_current_row_language := l_retrieved_value;
2035
2036 ELSIF (l_current_column_name = 'SOURCE_LANG') THEN
2037
2038 ------------------------------------------------
2039 -- Determine the Source Lang for knowing when --
2040 -- to insert a History row into the B table --
2041 ------------------------------------------------
2042 l_current_row_source_lang := l_retrieved_value;
2043
2044 END IF;
2045 END LOOP; -- l_current_column_index
2046
2047
2048
2049 -- NO need to process UOM
2050 ---------------------------------------------------------
2051 -- If we gathered any UOM data, we assign all gathered --
2052 -- UOM values to the appropriate Attr data object --
2053 ---------------------------------------------------------
2054 -- IF (l_uom_nv_pairs_index > 0) THEN
2055 --
2056 -- FOR i IN 1 .. l_uom_nv_pairs_index
2057 -- LOOP
2058 --
2059 --
2060 -- l_current_uom_col_nv_obj := l_uom_column_nv_pairs(i);
2061 --
2062 ----------------------------------------------
2063 -- We derive the Attr's DB column name from --
2064 -- the UOM column name in one of two ways --
2065 ----------------------------------------------
2066 -- IF (INSTR(l_current_uom_col_nv_obj.NAME, 'UOM_EXT_ATTR') = 1) THEN
2067 -- l_attr_col_name_for_uom_col := 'N_'||SUBSTR(l_current_uom_col_nv_obj.NAME, 5);
2068 -- ELSE
2069 -- l_attr_col_name_for_uom_col := SUBSTR(l_current_uom_col_nv_obj.NAME, 5);
2070 -- END IF;
2071 --
2072 -------------------------------------------------------------
2073 -- Now we find the Attr from the column name we've derived --
2074 -- and set its Attr data object's UOM field with our value --
2075 -------------------------------------------------------------
2076 -- IF (l_attr_name_value_pairs IS NOT NULL AND
2077 -- l_attr_name_value_pairs.COUNT > 0) THEN
2078 --
2079 -- l_attr_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Find_Metadata_For_Attr(
2080 -- p_attr_metadata_table => l_attr_group_metadata_obj.attr_metadata_table
2081 -- ,p_db_column_name => l_attr_col_name_for_uom_col
2082 -- );
2083 --
2084 ------------------------------------------------------------------
2085 -- If we found the metadata object, we look for the data object --
2086 ------------------------------------------------------------------
2087 -- IF (l_attr_metadata_obj IS NOT NULL AND
2088 -- l_attr_metadata_obj.ATTR_NAME IS NOT NULL) THEN
2089 --
2090 -- FOR j IN l_attr_name_value_pairs.FIRST .. l_attr_name_value_pairs.LAST
2091 -- LOOP
2092 -- IF (l_attr_name_value_pairs(j).ATTR_NAME =
2093 -- l_attr_metadata_obj.ATTR_NAME) THEN
2094 --
2095 -----------------------------------------------------------
2096 -- When we find the data object, we set its UOM and exit --
2097 -----------------------------------------------------------
2098 -- l_attr_name_value_pairs(j).ATTR_UNIT_OF_MEASURE :=
2099 -- l_current_uom_col_nv_obj.VALUE;
2100 -- EXIT;
2101
2102 -- END IF;
2103 -- END LOOP;
2104 -- END IF;
2105 -- END IF;
2106 -- END LOOP;
2107 -- END IF; -- End of UOM Data
2108 --
2109
2110
2111 -------------------------------------------------------------------
2112 -- Now that we've got all necessary data and metadata, we try to --
2113 -- find a corresponding production row for this pending row; we --
2114 -- use the new data level values if we have them, because we are --
2115 -- trying to see whether or not the row we're about to move into --
2116 -- the production table already exists there --
2117 -------------------------------------------------------------------
2118 IF (l_current_acd_type = G_CHANGE_ACD_TYPE)
2119 THEN
2120
2121 Write_Debug('Item Attr Change Imple is processing rec with ACD Type: ' || l_current_acd_type);
2122
2123 -----------------------------------------------------
2124 -- If ACD Type is CHANGE and there's
2125 -- a production row, we change it
2126 -- In case of Item Op Attr Change, we can assume this
2127 -----------------------------------------------------
2128 l_mode_for_current_row := G_UPDATE_TX_TYPE;
2129
2130
2131 ELSE
2132 -- Acd Type maybe ADD or DELETE or Invalid one
2133 Write_Debug('Item Attr Change Imple does not support ACD Type: ' || l_current_acd_type);
2134
2135 -- We don't support this in R12
2136 FND_MESSAGE.Set_Name('ENG','ENG_IMPL_INVALID_ACD_TYPE');
2137 FND_MESSAGE.Set_Token('ACD_TYPE', l_current_acd_type);
2138 FND_MSG_PUB.Add;
2139 RAISE FND_API.G_EXC_ERROR;
2140
2141 END IF;
2142
2143
2144 IF (l_mode_for_current_row <> 'SKIP')
2145 THEN
2146
2147 ---------------------------------------------------------------------
2148 -- Now at last we're ready to call Process_Row on this pending row --
2149 ---------------------------------------------------------------------
2150 l_dynamic_sql :=
2151 ' BEGIN ' ||
2152 ' EGO_ITEM_PUB.Process_Item ' ||
2153 ' ( ' ||
2154 ' p_api_version => 1' ||
2155 ' ,p_init_msg_list => FND_API.G_FALSE ' ||
2156 ' ,p_commit => FND_API.G_FALSE ' ||
2157 ' ,p_transaction_type => :l_mode_for_current_row ' ||
2158 -- ' ,p_Language_Code => :l_current_row_language ' ||
2159 ' ,p_inventory_item_id => :l_inventory_item_id ' ||
2160 ' ,p_item_number => :l_item_number ' ||
2161 ' ,p_organization_id => :l_organization_id ' ||
2162 -- ' ,p_process_control => ''PLM_UI:Y''' || Bug 4723028
2163 ' ,p_process_control => ''PLM_UI:N$ENG_CALL:Y''' ||
2164 --' ,p_process_item => :l_process_item ' ||
2165 -- ' ,p_object_version_number => null ' ||
2166 ' ,x_inventory_item_id=> :l_out_inventory_item_id ' ||
2167 ' ,x_organization_id => :l_out_organization_id ' ;
2168 if l_item_desc is not null
2169 then
2170 l_dynamic_sql := l_dynamic_sql || ' ,P_DESCRIPTION => :l_item_desc ' ;
2171 end if;
2172 l_dynamic_sql := l_dynamic_sql || ' ,x_return_status => :l_return_status ' ||
2173 ' ,x_msg_count => :l_msg_count ' ||
2174 ' ,x_msg_data => :l_msg_data ' ;
2175
2176
2177 FOR j IN l_attr_name_value_pairs.FIRST .. l_attr_name_value_pairs.LAST
2178 LOOP
2179
2180 ---------------------------------------------------------------------
2181 -- Assumption. EGO_ITEM_PUB.Process_Item's param for attribute data
2182 -- is P_ + DB Column Name
2183 ---------------------------------------------------------------------
2184
2185 -----------------------------
2186 -- Got Data --
2187 -----------------------------
2188 l_num_value := l_attr_name_value_pairs(j).ATTR_VALUE_NUM ;
2189 l_char_value := l_attr_name_value_pairs(j).ATTR_VALUE_STR ;
2190 l_date_value := l_attr_name_value_pairs(j).ATTR_VALUE_DATE ;
2191 l_date_value_char := TO_CHAR(l_attr_name_value_pairs(j).ATTR_VALUE_DATE, EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT) ;
2192
2193
2194 -- ATTR_NAME is set as DB Column Name
2195 IF l_num_value IS NOT NULL
2196 THEN
2197 IF l_num_value = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_NUM
2198 THEN
2199 l_num_value := EGO_ITEM_PUB.G_INTF_NULL_NUM;
2200 END IF;
2201
2202 l_dynamic_sql := l_dynamic_sql ||
2203 ' , ' || Get_Process_Item_Param(l_attr_name_value_pairs(j).ATTR_NAME) || ' => ' || TO_CHAR(l_num_value) ;
2204
2205 ELSIF l_char_value IS NOT NULL
2206 THEN
2207 IF l_char_value = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_CHAR
2208 THEN
2209 l_char_value := EGO_ITEM_PUB.G_INTF_NULL_CHAR;
2210 END IF;
2211
2212 l_dynamic_sql := l_dynamic_sql ||
2213 ' , ' || Get_Process_Item_Param(l_attr_name_value_pairs(j).ATTR_NAME) || ' => ' || '''' || l_char_value || '''' ;
2214
2215 ELSIF l_date_value IS NOT NULL
2216 THEN
2217 IF l_date_value = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_DATE
2218 THEN
2219 l_date_value := EGO_ITEM_PUB.G_INTF_NULL_DATE;
2220 END IF;
2221
2222 l_dynamic_sql := l_dynamic_sql ||
2223 ' , ' || Get_Process_Item_Param(l_attr_name_value_pairs(j).ATTR_NAME) || ' => ' || ' TO_DATE( ''' || l_date_value_char || ''', ''' || EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT || ''') ';
2224
2225 -- l_dynamic_sql := l_dynamic_sql || ''' || l_date_value || '''' ;
2226
2227 END IF ;
2228
2229 END LOOP;
2230
2231 l_dynamic_sql := l_dynamic_sql || ' ); END; ' ;
2232
2233
2234 if l_item_number is null
2235 then
2236 l_item_number := GetItemNumber(p_inventory_item_id => l_inventory_item_id
2237 , p_organization_id => l_organization_id) ;
2238 end if;
2239
2240 Write_Debug('EGO_ITEM_PUB.Process_Item call: ' || l_dynamic_sql);
2241 Write_Debug('---------------------------------- ' );
2242 Write_Debug('l_mode_for_current_row: ' || l_mode_for_current_row);
2243 Write_Debug('l_inventory_item_id: ' || to_char(l_inventory_item_id));
2244 Write_Debug('l_organization_id: ' || to_char(l_organization_id));
2245 Write_Debug('l_item_number: ' || l_item_number);
2246 Write_Debug('---------------------------------- ' );
2247
2248
2249 BEGIN
2250
2251 l_dummy_c := DBMS_SQL.OPEN_CURSOR;
2252 DBMS_SQL.PARSE(l_dummy_c, l_dynamic_sql, 2);
2253 DBMS_SQL.BIND_VARIABLE (l_dummy_c, 'l_mode_for_current_row', l_mode_for_current_row);
2254 DBMS_SQL.BIND_VARIABLE (l_dummy_c, 'l_inventory_item_id', l_inventory_item_id);
2255 DBMS_SQL.BIND_VARIABLE (l_dummy_c, 'l_item_number', l_item_number);
2256 DBMS_SQL.BIND_VARIABLE (l_dummy_c, 'l_organization_id', l_organization_id);
2257 DBMS_SQL.BIND_VARIABLE (l_dummy_c, 'l_out_inventory_item_id', l_out_inventory_item_id);
2258 DBMS_SQL.BIND_VARIABLE (l_dummy_c, 'l_out_organization_id', l_out_organization_id);
2259 if l_item_desc is not null
2260 then
2261 DBMS_SQL.BIND_VARIABLE(l_dummy_c, 'l_item_desc', l_item_desc);
2262 end if;
2263 DBMS_SQL.BIND_VARIABLE (l_dummy_c, 'l_return_status', l_return_status, 1);
2264 DBMS_SQL.BIND_VARIABLE (l_dummy_c, 'l_msg_count', l_msg_count);
2265 DBMS_SQL.BIND_VARIABLE (l_dummy_c, 'l_msg_data', l_msg_data, 2000);
2266 l_dummy_r := DBMS_SQL.EXECUTE(l_dummy_c);
2267 DBMS_SQL.VARIABLE_VALUE(l_dummy_c, 'l_return_status', l_return_status);
2268 DBMS_SQL.VARIABLE_VALUE(l_dummy_c, 'l_msg_count', l_msg_count);
2269 DBMS_SQL.VARIABLE_VALUE(l_dummy_c, 'l_msg_data', l_msg_data);
2270
2271
2272 Write_Debug('After calling EGO_ITEM_PUB.Process_Item' );
2273 Write_Debug('---------------------------------- ' );
2274 Write_Debug('l_return_status: ' || l_return_status);
2275 Write_Debug('l_msg_count: ' || to_char(l_msg_count));
2276 Write_Debug('l_msg_data: ' || l_msg_data);
2277 Write_Debug('l_out_inventory_item_id: ' || to_char(l_out_inventory_item_id));
2278 Write_Debug('l_out_organization_id: ' || to_char(l_out_organization_id));
2279 Write_Debug('---------------------------------- ' );
2280
2281 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2282 RAISE FND_API.G_EXC_ERROR;
2283 ELSE
2284 -- Bug 6157001 Moved here - if l_return_status = 'S' Add history record only if process_item returns S
2285 -- History record was getting saved somehow even when the process_item is returning E and rollback is being executed.
2286 -----------------------------------------------------------
2287 -- If we're altering a production row, we first copy the --
2288 -- row into the pending tables with the ACD Type HISTORY --
2289 -----------------------------------------------------------
2290 IF (l_mode_for_current_row = G_DELETE_TX_TYPE OR
2291 l_mode_for_current_row = G_UPDATE_TX_TYPE) THEN
2292
2293
2294 Write_Debug('Copy the row into the pending tables with the ACD Type HISTORY');
2295
2296
2297 -----------------------------------------------------------
2298 -- Process_Row will only process our pending B table row --
2299 -- in the loop when LANGUAGE is NULL or when LANGUAGE = --
2300 -- SOURCE_LANG, so we insert a History row in that loop --
2301 -----------------------------------------------------------
2302 IF (l_current_row_language IS NULL OR
2303 l_current_row_language = l_current_row_source_lang)
2304 THEN
2305
2306
2307 Write_Debug('Inserting History Row with the ACD Type HISTORY for B table');
2308
2309 l_utility_dynamic_sql := ' INSERT INTO '||l_change_b_table_name||' CT ('||
2310 l_history_b_chg_cols_list||
2311 ', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
2312 ', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
2313 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
2314 ', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID ' ||
2315 ' ) SELECT ' || l_history_b_prod_cols_list||
2316 ', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
2317 ', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
2318 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
2319 ', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID FROM ' ||
2320 l_production_b_table_name ||' PT, '||
2321 l_change_b_table_name || ' CT ' ||
2322 ' WHERE PT.INVENTORY_ITEM_ID = :1'||
2323 ' AND PT.ORGANIZATION_ID = :2'||
2324 ' AND CT.INVENTORY_ITEM_ID = :3'||
2325 ' AND CT.ORGANIZATION_ID = :4'||
2326 ' AND CT.CHANGE_LINE_ID = :5'||
2327 ' AND CT.ACD_TYPE = :6' ;
2328
2329
2330
2331 EXECUTE IMMEDIATE l_utility_dynamic_sql
2332 USING l_inventory_item_id, l_organization_id,
2333 l_inventory_item_id, l_organization_id,
2334 l_change_line_id, l_current_acd_type ;
2335
2336
2337 Write_Debug('After Inserting History Row with the ACD Type HISTORY for B table');
2338
2339 END IF;
2340
2341
2342 ------------------------------------------------------------
2343 -- Process_Row will only process the pending TL table row --
2344 -- whose language matches LANGUAGE, so we only insert a --
2345 -- History row for that row --
2346 ------------------------------------------------------------
2347
2348 Write_Debug('Inserting History Row with the ACD Type HISTORY for TL table');
2349
2350
2351 l_utility_dynamic_sql := ' INSERT INTO '||l_change_tl_table_name||' CT ('||
2352 l_history_tl_chg_cols_list||
2353 ', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
2354 ', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
2355 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
2356 ', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID '||
2357 ' ) SELECT ' || l_history_tl_prod_cols_list||
2358 ', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
2359 ', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
2360 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
2361 ', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID FROM '||
2362 l_production_tl_table_name||' PT, '||
2363 l_change_tl_table_name||' CT ' ||
2364 ' WHERE PT.INVENTORY_ITEM_ID = :1'||
2365 ' AND PT.ORGANIZATION_ID = :2'||
2366 ' AND CT.INVENTORY_ITEM_ID = :3'||
2367 ' AND CT.ORGANIZATION_ID = :4'||
2368 ' AND CT.CHANGE_LINE_ID = :5'||
2369 ' AND CT.ACD_TYPE = :6'||
2370 ' AND CT.LANGUAGE = PT.LANGUAGE AND CT.LANGUAGE = :7';
2371
2372 EXECUTE IMMEDIATE l_utility_dynamic_sql
2373 USING l_inventory_item_id, l_organization_id,
2374 l_inventory_item_id, l_organization_id,
2375 l_change_line_id, l_current_acd_type,
2376 l_current_row_language;
2377
2378 Write_Debug('After Inserting History Row with the ACD Type HISTORY for TL table');
2379
2380 END IF; -- Check l_mode_for_current_row
2381
2382 END IF;
2383
2384 EXCEPTION
2385
2386 WHEN FND_API.G_EXC_ERROR THEN
2387 x_return_status := l_return_status ;
2388 RAISE FND_API.G_EXC_ERROR;
2389
2390 WHEN OTHERS THEN
2391
2392 FND_MSG_PUB.Add_Exc_Msg
2393 ( p_pkg_name => 'EGO_ITEM_PUB' ,
2394 p_procedure_name => 'Process_Item',
2395 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
2396 );
2397
2398
2399 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2400 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_ITEM_PUB.Process_Item');
2401 FND_MSG_PUB.Add;
2402
2403 Write_Debug('When Others Exception while calling EGO_ITEM_PUB.Process_Item' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
2404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2405
2406 END ;
2407
2408
2409 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2410 THEN
2411
2412 -- Get message list from Error_Hanlder
2413 Error_Handler.Get_Message_List(x_Message_List => l_message_list ) ;
2414 l_msg_index := l_message_list.FIRST ;
2415
2416 WHILE l_msg_index IS NOT NULL
2417 LOOP
2418 FND_MSG_PUB.Add_Exc_Msg
2419 ( p_pkg_name => null ,
2420 p_procedure_name => null ,
2421 p_error_text => l_message_list(l_msg_index).message_text
2422 );
2423
2424 l_msg_index := l_message_list.NEXT(l_msg_index);
2425
2426 END LOOP;
2427
2428 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
2429
2430 -- FND_MSG_PUB.dump_Msg(I);
2431 Write_Debug('Dumping Message number : '|| I );
2432 Write_Debug('DATA = '||replace(substr(FND_MSG_PUB.Get(I), 1, 200), chr(0), ' '));
2433
2434 END LOOP;
2435
2436
2437 x_return_status := l_return_status;
2438 x_msg_count := l_msg_count;
2439 x_msg_data := l_msg_data;
2440
2441 Write_Debug('EGO_ITEM_PUB.Process_Item failed . .. ' );
2442 Write_Debug('Output - Return Stattus: ' || l_return_status);
2443 Write_Debug('Output - Return Stattus: ' || to_char(l_msg_count));
2444 Write_Debug('Output - Return Stattus: ' || substr(l_msg_data,1,200));
2445
2446 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2447 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_ITEM_PUB.Process_Item');
2448 FND_MSG_PUB.Add;
2449
2450 RAISE FND_API.G_EXC_ERROR ;
2451
2452 END IF;
2453
2454 END IF; -- l_mode_for_current_row <> 'SKIP'
2455
2456
2457 END LOOP; -- DBMS_SQL.Fetch_Rows Loop
2458 DBMS_SQL.Close_Cursor(l_cursor_id);
2459
2460
2461 ---------------------------------------------------------------------------
2462 -- Finally, set the IMPLEMENTATION_DATE for all rows we just implemented --
2463 ---------------------------------------------------------------------------
2464 -- If the record is queried
2465 IF l_current_column_index IS NOT NULL
2466 THEN
2467
2468 Write_Debug('set the IMPLEMENTATION_DATE for all rows we just implemented');
2469
2470
2471 EXECUTE IMMEDIATE ' UPDATE '||l_change_b_table_name||
2472 ' SET IMPLEMENTATION_DATE = :1'||
2473 ' WHERE CHANGE_LINE_ID = :2'
2474 USING SYSDATE, p_change_line_id;
2475 EXECUTE IMMEDIATE ' UPDATE '||l_change_tl_table_name||
2476 ' SET IMPLEMENTATION_DATE = :1'||
2477 ' WHERE CHANGE_LINE_ID = :2'
2478 USING SYSDATE, p_change_line_id;
2479
2480
2481 END IF ;
2482 Write_Debug('In Implement Item Attribute Change, Done');
2483
2484 -- End of API body.
2485
2486
2487 -- Standard check of p_commit.
2488 IF FND_API.To_Boolean( l_commit ) THEN
2489
2490 Write_Debug('Commit Item Attribute Change Implementation ');
2491
2492 COMMIT WORK;
2493 END IF;
2494
2495 -- Standard call to get message count and if count is 1, get message info.
2496 FND_MSG_PUB.Count_And_Get
2497 ( p_count => x_msg_count ,
2498 p_data => x_msg_data
2499 );
2500
2501
2502 -----------------------------------------------------
2503 -- Close Error Handler Debug Session.
2504 -----------------------------------------------------
2505 -- Close debug session only explicitly open the debug session for
2506 -- this API.
2507 IF FND_API.to_Boolean(p_debug)
2508 THEN
2509 Close_Debug_Session;
2510 END IF ;
2511
2512
2513 EXCEPTION
2514 WHEN FND_API.G_EXC_ERROR THEN
2515 Write_Debug('When G_EXC_ERROR Exception in impl_rev_item_attr_changes');
2516
2517 x_return_status := G_RET_STS_ERROR ;
2518
2519 IF FND_API.To_Boolean( l_commit ) THEN
2520 Write_Debug('Rollback to IMPL_REV_ITEM_ATTR_CHGS Item Attribute Change Implementation ');
2521 ROLLBACK TO IMPL_REV_ITEM_ATTR_CHGS;
2522 END IF;
2523
2524 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2525 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_attr_changes for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
2526 FND_MSG_PUB.Add;
2527
2528
2529 FND_MSG_PUB.Count_And_Get
2530 ( p_count => x_msg_count ,
2531 p_data => x_msg_data
2532 );
2533
2534 -----------------------------------------------------
2535 -- Close Error Handler Debug Session.
2536 -----------------------------------------------------
2537 -- Close debug session only explicitly open the debug session for
2538 -- this API.
2539 IF FND_API.to_Boolean(p_debug)
2540 THEN
2541 Close_Debug_Session;
2542 END IF ;
2543
2544 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2545 Write_Debug('When G_EXC_UNEXPECTED_ERROR Exception in impl_rev_item_attr_changes');
2546
2547 x_return_status := G_RET_STS_UNEXP_ERROR ;
2548
2549 IF FND_API.To_Boolean( l_commit ) THEN
2550 Write_Debug('Rollback to IMPL_REV_ITEM_ATTR_CHGS Item Attribute Change Implementation ');
2551 ROLLBACK TO IMPL_REV_ITEM_ATTR_CHGS;
2552 END IF;
2553
2554
2555 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2556 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_attr_changes for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
2557 FND_MSG_PUB.Add;
2558
2559 FND_MSG_PUB.Count_And_Get
2560 ( p_count => x_msg_count ,
2561 p_data => x_msg_data
2562 );
2563
2564 -----------------------------------------------------
2565 -- Close Error Handler Debug Session.
2566 -----------------------------------------------------
2567 -- Close debug session only explicitly open the debug session for
2568 -- this API.
2569 IF FND_API.to_Boolean(p_debug)
2570 THEN
2571 Close_Debug_Session;
2572 END IF ;
2573
2574 WHEN OTHERS THEN
2575 Write_Debug('When G_EXC_ERROR Exception in impl_rev_item_attr_changes');
2576
2577 x_return_status := G_RET_STS_UNEXP_ERROR ;
2578
2579 Write_Debug('Rollback to IMPL_REV_ITEM_ATTR_CHGS Item Attribute Change Implementation ');
2580
2581 IF FND_API.To_Boolean( l_commit ) THEN
2582 Write_Debug('Rollback to IMPL_REV_ITEM_ATTR_CHGS Item Attribute Change Implementation ');
2583 ROLLBACK TO IMPL_REV_ITEM_ATTR_CHGS;
2584 END IF;
2585
2586
2587 Write_Debug('When Others Exception ' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
2588
2589
2590 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2591 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_attr_changes for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
2592 FND_MSG_PUB.Add;
2593
2594
2595 IF FND_MSG_PUB.Check_Msg_Level
2596 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2597 THEN
2598 FND_MSG_PUB.Add_Exc_Msg
2599 ( G_PKG_NAME ,
2600 l_api_name
2601 );
2602 END IF;
2603
2604 FND_MSG_PUB.Count_And_Get
2605 ( p_count => x_msg_count ,
2606 p_data => x_msg_data
2607 );
2608
2609 -----------------------------------------------------
2610 -- Close Error Handler Debug Session.
2611 -----------------------------------------------------
2612 -- Close debug session only explicitly open the debug session for
2613 -- this API.
2614 IF FND_API.to_Boolean(p_debug)
2615 THEN
2616 Close_Debug_Session;
2617 END IF ;
2618
2619
2620 END impl_rev_item_attr_changes;
2621
2622
2623
2624
2625 PROCEDURE impl_rev_item_aml_changes
2626 ( p_api_version IN NUMBER
2627 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
2628 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
2629 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
2630 , x_return_status OUT NOCOPY VARCHAR2
2631 , x_msg_count OUT NOCOPY NUMBER
2632 , x_msg_data OUT NOCOPY VARCHAR2
2633 , p_api_caller IN VARCHAR2 := NULL
2634 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
2635 , p_output_dir IN VARCHAR2 := NULL
2636 , p_debug_filename IN VARCHAR2 := NULL
2637 , p_change_id IN NUMBER := NULL
2638 , p_change_line_id IN NUMBER
2639 )
2640 IS
2641
2642 l_api_name CONSTANT VARCHAR2(30) := 'IMPL_REV_ITEM_AML_CHANGES';
2643 l_api_version CONSTANT NUMBER := 1.0;
2644
2645 l_init_msg_list VARCHAR2(1) ;
2646 l_validation_level NUMBER ;
2647 l_commit VARCHAR2(1) ;
2648
2649
2650 l_msg_data VARCHAR2(4000);
2651 l_msg_count NUMBER;
2652 l_return_status VARCHAR2(1);
2653 l_errorcode NUMBER;
2654
2655 l_found BOOLEAN ;
2656
2657 l_change_id NUMBER;
2658 l_change_line_id NUMBER;
2659
2660 l_check_aml_changes NUMBER := 2;
2661 plsql_block VARCHAR2(5000);
2662
2663 BEGIN
2664
2665
2666 -- Standard call to check for call compatibility.
2667 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2668 p_api_version ,
2669 l_api_name ,
2670 G_PKG_NAME )
2671 THEN
2672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2673 END IF;
2674
2675 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
2676 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
2677 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
2678
2679 IF FND_API.To_Boolean( l_commit ) THEN
2680 -- Standard Start of API savepoint
2681 SAVEPOINT IMPL_REV_ITEM_AML_CHANGES;
2682 END IF;
2683
2684 -- Initialize message list if p_init_msg_list is set to TRUE.
2685 IF FND_API.to_Boolean( l_init_msg_list ) THEN
2686 FND_MSG_PUB.initialize;
2687 END IF;
2688
2689 -- Open Debug Session by a give param or profile option.
2690 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
2691
2692 Write_Debug('After Open_Debug_Session');
2693 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
2694 Write_Debug('-----------------------------------------' );
2695 Write_Debug('p_api_version: ' || to_char(p_api_version));
2696 Write_Debug('p_init_msg_list:' || p_init_msg_list);
2697 Write_Debug('p_commit:' || p_commit);
2698 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
2699 Write_Debug('p_api_caller:' || p_api_caller);
2700 Write_Debug('p_change_id: ' || to_char(p_change_id));
2701 Write_Debug('p_change_line_id: ' || to_char(p_change_line_id));
2702 Write_Debug('-----------------------------------------' );
2703
2704 -- Initialize API return status to success
2705 x_return_status := G_RET_STS_SUCCESS;
2706
2707 -- API body
2708 -- Logic Here
2709 -- Init Local Vars
2710
2711
2712 l_change_id := p_change_id;
2713 l_change_line_id := p_change_line_id;
2714
2715 IF ( l_change_id IS NULL OR l_change_id <= 0 )
2716 THEN
2717 l_change_id := GetChangeId(p_change_line_id => l_change_line_id) ;
2718
2719 Write_Debug('Got Change Id: ' || to_char(l_change_id));
2720
2721 END IF ;
2722
2723
2724 Write_Debug('Check Item AML Change exists for Rev Item: ' || to_char(l_change_line_id));
2725
2726 l_found := CheckItemMfgPartNumChange(p_change_line_id => l_change_line_id) ;
2727 IF NOT l_found THEN
2728
2729 Write_Debug('Item AML Change not found for ' || to_char(l_change_line_id));
2730 RETURN ;
2731
2732 END IF ;
2733
2734
2735 --
2736 -- Call implement aml changes api
2737 --
2738
2739 Write_Debug('calling EGO_ITEM_AML_PUB.Implement_AML_Changes for Rev Item: ' || to_char(l_change_line_id));
2740 BEGIN
2741 plsql_block := 'BEGIN
2742 EGO_ITEM_AML_PUB.Implement_AML_Changes(
2743 :a,
2744 :b,
2745 :c,
2746 :d,
2747 :e,
2748 :f,
2749 :g,
2750 :h);
2751 END;';
2752
2753 EXECUTE IMMEDIATE plsql_block USING
2754 '1',
2755 '',
2756 FND_API.G_FALSE,
2757 p_change_id,
2758 p_change_line_id,
2759 OUT l_return_status,
2760 OUT l_msg_count,
2761 OUT l_msg_data;
2762
2763 Write_Debug('After calling EGO_ITEM_AML_PUB.Implement_AML_Changes for Return Status: ' || l_return_status);
2764 IF l_msg_count > 1 THEN
2765 for i in 1..l_msg_count loop
2766 Write_Debug(' msg no '||i ||': '|| fnd_msg_pub.Get(p_msg_index => i,p_encoded => 'F'));
2767 end loop;
2768 else
2769 Write_Debug('Implement_AML_Changes Error message : ' || l_msg_data);
2770 END IF;
2771
2772
2773 EXCEPTION
2774 WHEN OTHERS THEN
2775
2776 FND_MSG_PUB.Add_Exc_Msg
2777 ( p_pkg_name => 'EGO_ITEM_AML_PUB' ,
2778 p_procedure_name => 'Implement_AML_Changes',
2779 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
2780 );
2781
2782
2783 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2784 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_ITEM_AML_PUB.Implement_AML_Changes');
2785 FND_MSG_PUB.Add;
2786
2787 Write_Debug('When Others Exception while calling EGO_ITEM_AML_PUB.Implement_AML_Changes:' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
2788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2789 END ;
2790
2791 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2792 THEN
2793 x_return_status := l_return_status;
2794 x_msg_count := l_msg_count;
2795 x_msg_data := l_msg_data;
2796
2797 Write_Debug('EGO_ITEM_AML_PUB.Implement_AML_Changes failed . .. ' );
2798 Write_Debug('Output - Return Stattus: ' || l_return_status);
2799 Write_Debug('Output - Return Stattus: ' || to_char(l_msg_count));
2800 Write_Debug('Output - Return Stattus: ' || substr(l_msg_data,1,200));
2801
2802
2803 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2804 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_ITEM_AML_PUB.Implement_AML_Changes');
2805 FND_MSG_PUB.Add;
2806
2807 RAISE FND_API.G_EXC_ERROR;
2808 END IF;
2809
2810
2811 -- End of API body.
2812
2813
2814 -- Standard check of p_commit.
2815 IF FND_API.To_Boolean( p_commit ) THEN
2816 Write_Debug('COMMIT Item AML Changes implementation');
2817
2818 COMMIT WORK;
2819 END IF;
2820
2821 -- Standard call to get message count and if count is 1, get message info.
2822 FND_MSG_PUB.Count_And_Get
2823 ( p_count => x_msg_count ,
2824 p_data => x_msg_data
2825 );
2826
2827
2828 -----------------------------------------------------
2829 -- Close Error Handler Debug Session.
2830 -----------------------------------------------------
2831 -- Close debug session only explicitly open the debug session for
2832 -- this API.
2833 IF FND_API.to_Boolean(p_debug)
2834 THEN
2835 Close_Debug_Session;
2836 END IF ;
2837
2838
2839 EXCEPTION
2840 WHEN FND_API.G_EXC_ERROR THEN
2841 Write_Debug('When G_EXC_ERROR Exception in impl_rev_item_aml_changes');
2842
2843 x_return_status := G_RET_STS_ERROR ;
2844
2845 IF FND_API.To_Boolean( l_commit ) THEN
2846 Write_Debug('ROLLBACK Item AML Changes implementation to IMPL_REV_ITEM_AML_CHANGES');
2847 ROLLBACK TO IMPL_REV_ITEM_AML_CHANGES;
2848 END IF;
2849
2850 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2851 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_aml_changes for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
2852 FND_MSG_PUB.Add;
2853
2854
2855 FND_MSG_PUB.Count_And_Get
2856 ( p_count => x_msg_count ,
2857 p_data => x_msg_data
2858 );
2859
2860 -----------------------------------------------------
2861 -- Close Error Handler Debug Session.
2862 -----------------------------------------------------
2863 -- Close debug session only explicitly open the debug session for
2864 -- this API.
2865 IF FND_API.to_Boolean(p_debug)
2866 THEN
2867 Close_Debug_Session;
2868 END IF ;
2869
2870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2871 Write_Debug('When G_EXC_UNEXPECTED_ERROR Exception in impl_rev_item_aml_changes');
2872
2873 x_return_status := G_RET_STS_UNEXP_ERROR ;
2874
2875 IF FND_API.To_Boolean( l_commit ) THEN
2876 Write_Debug('ROLLBACK Item AML Changes implementation to IMPL_REV_ITEM_AML_CHANGES');
2877 ROLLBACK TO IMPL_REV_ITEM_AML_CHANGES;
2878 END IF;
2879
2880
2881 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2882 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_aml_changes for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
2883 FND_MSG_PUB.Add;
2884
2885 FND_MSG_PUB.Count_And_Get
2886 ( p_count => x_msg_count ,
2887 p_data => x_msg_data
2888 );
2889
2890 -----------------------------------------------------
2891 -- Close Error Handler Debug Session.
2892 -----------------------------------------------------
2893 -- Close debug session only explicitly open the debug session for
2894 -- this API.
2895 IF FND_API.to_Boolean(p_debug)
2896 THEN
2897 Close_Debug_Session;
2898 END IF ;
2899
2900 WHEN OTHERS THEN
2901 Write_Debug('When OTHERS Exception in impl_rev_item_aml_changes');
2902
2903 x_return_status := G_RET_STS_UNEXP_ERROR ;
2904
2905 IF FND_API.To_Boolean( l_commit ) THEN
2906 Write_Debug('ROLLBACK Item AML Changes implementation to IMPL_REV_ITEM_AML_CHANGES');
2907 ROLLBACK TO IMPL_REV_ITEM_AML_CHANGES;
2908 END IF;
2909
2910
2911 Write_Debug('When Others Exception ' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
2912
2913 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
2914 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_aml_changes for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
2915 FND_MSG_PUB.Add;
2916
2917
2918 IF FND_MSG_PUB.Check_Msg_Level
2919 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2920 THEN
2921 FND_MSG_PUB.Add_Exc_Msg
2922 ( G_PKG_NAME ,
2923 l_api_name
2924 );
2925 END IF;
2926
2927 FND_MSG_PUB.Count_And_Get
2928 ( p_count => x_msg_count ,
2929 p_data => x_msg_data
2930 );
2931
2932 -----------------------------------------------------
2933 -- Close Error Handler Debug Session.
2934 -----------------------------------------------------
2935 -- Close debug session only explicitly open the debug session for
2936 -- this API.
2937 IF FND_API.to_Boolean(p_debug)
2938 THEN
2939 Close_Debug_Session;
2940 END IF ;
2941
2942 END impl_rev_item_aml_changes ;
2943
2944
2945
2946
2947 PROCEDURE impl_rev_item_gdsn_attr_chgs
2948 ( p_api_version IN NUMBER
2949 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
2950 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
2951 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
2952 , x_return_status OUT NOCOPY VARCHAR2
2953 , x_msg_count OUT NOCOPY NUMBER
2954 , x_msg_data OUT NOCOPY VARCHAR2
2955 , p_api_caller IN VARCHAR2 := NULL
2956 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
2957 , p_output_dir IN VARCHAR2 := NULL
2958 , p_debug_filename IN VARCHAR2 := NULL
2959 , p_change_id IN NUMBER := NULL
2960 , p_change_line_id IN NUMBER
2961 )
2962 IS
2963
2964
2965 l_api_name CONSTANT VARCHAR2(30) := 'IMPL_REV_ITEM_GDSN_ATTR_CHGS';
2966 l_api_version CONSTANT NUMBER := 1.0;
2967
2968 l_init_msg_list VARCHAR2(1) ;
2969 l_validation_level NUMBER ;
2970 l_commit VARCHAR2(1) ;
2971
2972
2973 l_msg_data VARCHAR2(4000);
2974 l_msg_count NUMBER;
2975 l_return_status VARCHAR2(1);
2976 l_errorcode NUMBER;
2977
2978 l_single_row_change_found BOOLEAN ;
2979 l_multi_row_change_found BOOLEAN ;
2980
2981 l_change_id NUMBER;
2982 l_change_line_id NUMBER;
2983 l_inventory_item_id NUMBER ;
2984 l_organization_id NUMBER ;
2985 l_attr_group_id NUMBER ;
2986
2987 l_cols_to_exclude_list VARCHAR2(2000);
2988 l_chg_col_names_list VARCHAR2(32767);
2989 l_mul_chg_col_names_list VARCHAR2(32767);
2990
2991
2992 l_mode_for_current_row VARCHAR2(10);
2993 l_current_acd_type VARCHAR2(30);
2994 l_current_row_language VARCHAR2(30);
2995 l_current_row_source_lang VARCHAR2(30);
2996 l_current_column_name VARCHAR2(30);
2997 l_attr_group_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
2998 l_attr_metadata_table EGO_ATTR_METADATA_TABLE ;
2999 l_current_pending_ext_id NUMBER;
3000 l_current_production_ext_id NUMBER;
3001 l_ext_id_for_current_row NUMBER;
3002
3003
3004 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3005 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE := EGO_USER_ATTR_DATA_TABLE();
3006 l_attr_metadata_obj EGO_ATTR_METADATA_OBJ;
3007
3008 l_num_value NUMBER ;
3009 l_char_value VARCHAR2(1000) ;
3010 l_date_value DATE ;
3011 l_date_value_char VARCHAR2(30) ;
3012
3013
3014 l_utility_dynamic_sql VARCHAR2(32767); --the largest a VARCHAR2 can be
3015 l_cursor_id NUMBER;
3016 l_column_count NUMBER;
3017 l_dummy NUMBER;
3018 l_desc_table DBMS_SQL.Desc_Tab;
3019 l_retrieved_value VARCHAR2(1000);
3020 l_current_column_index NUMBER;
3021
3022 l_prod_b_table_name CONSTANT VARCHAR2(30) := 'EGO_ITEM_GTN_ATTRS_B' ;
3023 l_prod_tl_table_name CONSTANT VARCHAR2(30) := 'EGO_ITEM_GTN_ATTRS_TL' ;
3024 l_chg_b_table_name CONSTANT VARCHAR2(30) := 'EGO_GTN_ATTR_CHG_B' ;
3025 l_chg_tl_table_name CONSTANT VARCHAR2(30) := 'EGO_GTN_ATTR_CHG_TL' ;
3026 l_mul_prod_b_table_name CONSTANT VARCHAR2(30) := 'EGO_ITM_GTN_MUL_ATTRS_B' ;
3027 l_mul_prod_tl_table_name CONSTANT VARCHAR2(30) := 'EGO_ITM_GTN_MUL_ATTRS_TL' ;
3028 l_mul_chg_b_table_name CONSTANT VARCHAR2(30) := 'EGO_GTN_MUL_ATTR_CHG_B' ;
3029 l_mul_chg_tl_table_name CONSTANT VARCHAR2(30) := 'EGO_GTN_MUL_ATTR_CHG_TL' ;
3030
3031 l_b_chg_cols_list VARCHAR2(32767);
3032 l_tl_chg_cols_list VARCHAR2(10000);
3033 l_hist_b_chg_cols_list VARCHAR2(32767);
3034 l_hist_tl_chg_cols_list VARCHAR2(10000);
3035 l_hist_b_prod_cols_list VARCHAR2(32767);
3036 l_hist_tl_prod_cols_list VARCHAR2(10000);
3037
3038 l_mul_b_chg_cols_list VARCHAR2(32767);
3039 l_mul_tl_chg_cols_list VARCHAR2(10000);
3040 l_mul_hist_b_chg_cols_list VARCHAR2(32767);
3041 l_mul_hist_tl_chg_cols_list VARCHAR2(10000);
3042 l_mul_hist_b_prod_cols_list VARCHAR2(32767);
3043 l_mul_hist_tl_prod_cols_list VARCHAR2(10000);
3044
3045
3046 l_dynamic_sql VARCHAR2(32767); --the largest a VARCHAR2 can be
3047 l_mul_dynamic_sql VARCHAR2(32767); --the largest a VARCHAR2 can be
3048
3049 l_single_row_attrs_rec EGO_ITEM_PUB.UCCNET_ATTRS_SINGL_ROW_REC_TYP ;
3050 l_multi_row_attrs_tbl EGO_ITEM_PUB.UCCNET_ATTRS_MULTI_ROW_TBL_TYP;
3051 l_extra_attrs_rec EGO_ITEM_PUB.UCCNET_EXTRA_ATTRS_REC_TYP;
3052
3053 l_installed_flag VARCHAR2(1) ;
3054 l_lang_code VARCHAR2(4);
3055 l_nls_lang VARCHAR2(64);
3056 l_territory VARCHAR2(64);
3057
3058 l_orig_nls_lang VARCHAR2(64);
3059 l_orig_territory VARCHAR2(64);
3060 l_orig_chrs VARCHAR2(64);
3061
3062 ind NUMBER;
3063 -- l_current_dl_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3064 -- l_object_id NUMBER;
3065 -- l_ext_table_metadata_obj EGO_EXT_TABLE_METADATA_OBJ;
3066
3067
3068 CURSOR c_lang
3069 IS
3070 SELECT L.LANGUAGE_CODE, L.INSTALLED_FLAG, L.NLS_LANGUAGE, L.NLS_TERRITORY
3071 FROM FND_LANGUAGES L
3072 WHERE L.INSTALLED_FLAG IN ('B', 'I') ;
3073
3074
3075 BEGIN
3076
3077 -- Standard call to check for call compatibility.
3078 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3079 p_api_version ,
3080 l_api_name ,
3081 G_PKG_NAME )
3082 THEN
3083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3084 END IF;
3085
3086 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
3087 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
3088 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
3089
3090
3091 IF FND_API.To_Boolean( l_commit ) THEN
3092 -- Standard Start of API savepoint
3093 SAVEPOINT IMPL_REV_ITEM_GDSN_ATTR_CHGS;
3094 END IF;
3095
3096 -- Initialize message list if p_init_msg_list is set to TRUE.
3097 IF FND_API.to_Boolean( l_init_msg_list ) THEN
3098 FND_MSG_PUB.initialize;
3099 END IF;
3100
3101 -- Open Debug Session by a give param or profile option.
3102 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
3103
3104 Write_Debug('After Open_Debug_Session');
3105 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
3106 Write_Debug('-----------------------------------------' );
3107 Write_Debug('p_api_version: ' || to_char(p_api_version));
3108 Write_Debug('p_init_msg_list:' || p_init_msg_list);
3109 Write_Debug('p_commit:' || p_commit);
3110 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
3111 Write_Debug('p_api_caller:' || p_api_caller);
3112 Write_Debug('p_change_id: ' || to_char(p_change_id));
3113 Write_Debug('p_change_line_id: ' || to_char(p_change_line_id));
3114 Write_Debug('-----------------------------------------' );
3115
3116 -- Initialize API return status to success
3117 x_return_status := G_RET_STS_SUCCESS;
3118
3119 -- API body
3120 -- Logic Here
3121 -- Init Local Vars
3122
3123
3124 l_change_id := p_change_id;
3125 l_change_line_id := p_change_line_id;
3126
3127 IF ( l_change_id IS NULL OR l_change_id <= 0 )
3128 THEN
3129 l_change_id := GetChangeId(p_change_line_id => l_change_line_id) ;
3130
3131 Write_Debug('Got Change Id: ' || to_char(l_change_id));
3132
3133 END IF ;
3134
3135
3136 Write_Debug('Check Item GDSN Attr Change exists for Rev Item: ' || to_char(l_change_line_id));
3137
3138 l_single_row_change_found := CheckItemGDSNAttrChange(p_change_line_id => l_change_line_id
3139 , p_gdsn_attr_group_type => G_EGO_ITEM_GTIN_ATTRS
3140 ) ;
3141
3142
3143 l_multi_row_change_found := CheckItemGDSNAttrChange(p_change_line_id => l_change_line_id
3144 , p_gdsn_attr_group_type => G_EGO_ITEM_GTIN_MULTI_ATTRS
3145 ) ;
3146
3147 IF NOT l_single_row_change_found AND
3148 NOT l_multi_row_change_found
3149 THEN
3150
3151 Write_Debug('Item GDSN Attr Change not found for ' || to_char(l_change_line_id));
3152 RETURN ;
3153
3154 END IF ;
3155
3156
3157 -----------------------------------------------
3158 --We get the meta data for Object ID for our calls --
3159 -----------------------------------------------
3160 -- In R12, we will do hardcoding, anyway we need to put chg table name by hardcoding
3161 --
3162 -- SELECT EXT_TABLE_NAME, EXT_TL_TABLE_NAME, EXT_VL_NAME
3163 -- INTO l_b_table_name, l_tl_table_name, l_vl_name
3164 -- FROM EGO_ATTR_GROUP_TYPES_V
3165 -- WHERE APPLICATION_ID = G_EGO_APPL_ID
3166 -- AND ATTR_GROUP_TYPE = G_EGO_ITEM_GTIN_ATTRS ;
3167 --
3168 --
3169 -- SELECT EXT_TABLE_NAME, EXT_TL_TABLE_NAME, EXT_VL_NAME
3170 -- INTO l_mul_b_table_name, l_mul_tl_table_name, l_mul_vl_name
3171 -- FROM EGO_ATTR_GROUP_TYPES_V
3172 -- WHERE APPLICATION_ID = G_EGO_APPL_ID
3173 -- AND ATTR_GROUP_TYPE = G_EGO_ITEM_GTIN_MULTI_ATTRS ;
3174 --
3175
3176
3177 ---------------------------------------------------------------
3178 -- Next, we add to the lists the rest of the columns that we --
3179 -- either want to get explicitly or don't want to get at all --
3180 ---------------------------------------------------------------
3181 l_mul_chg_col_names_list:= 'B.INVENTORY_ITEM_ID,B.ORGANIZATION_ID,B.ACD_TYPE,B.EXTENSION_ID,B.ATTR_GROUP_ID';
3182 l_chg_col_names_list := 'B.INVENTORY_ITEM_ID,B.ORGANIZATION_ID,B.ACD_TYPE,B.EXTENSION_ID';
3183
3184
3185 l_cols_to_exclude_list := '''INVENTORY_ITEM_ID'',''ORGANIZATION_ID'','||
3186 '''ACD_TYPE'',''ATTR_GROUP_ID'',''EXTENSION_ID'','||
3187 '''CHANGE_ID'',''CHANGE_LINE_ID'','||
3188 '''IMPLEMENTATION_DATE'',''CREATED_BY'','||
3189 '''CREATION_DATE'',''LAST_UPDATED_BY'','||
3190 '''LAST_UPDATE_DATE'',''LAST_UPDATE_LOGIN'','||
3191 '''PROGRAM_ID'',''PROGRAM_UPDATE_DATE'',''REQUEST_ID'' ,''PROGRAM_APPLICATION_ID''';
3192
3193
3194 ----------------------------------------------------------
3195 -- Get lists of columns for the B and TL pending tables --
3196 -- (i.e., all Attr cols and the language cols from TL) --
3197 ----------------------------------------------------------
3198 l_b_chg_cols_list := Get_Table_Columns_List(
3199 p_application_id => G_EGO_APPL_ID
3200 -- ,p_from_table_name => l_chg_b_table_name
3201 ,p_from_table_name => l_prod_b_table_name
3202 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3203 ,p_from_table_alias_prefix => 'B'
3204 ,p_cast_date_cols_to_char => TRUE
3205 );
3206 l_tl_chg_cols_list := Get_Table_Columns_List(
3207 p_application_id => G_EGO_APPL_ID
3208 -- ,p_from_table_name => l_chg_tl_table_name
3209 ,p_from_table_name => l_prod_tl_table_name
3210 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3211 ,p_from_table_alias_prefix => 'TL'
3212 ,p_cast_date_cols_to_char => TRUE
3213 );
3214
3215 l_mul_b_chg_cols_list := Get_Table_Columns_List(
3216 p_application_id => G_EGO_APPL_ID
3217 -- ,p_from_table_name => l_mul_chg_b_table_name
3218 ,p_from_table_name => l_mul_prod_b_table_name
3219 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3220 ,p_from_table_alias_prefix => 'B'
3221 ,p_cast_date_cols_to_char => TRUE
3222 );
3223 l_mul_tl_chg_cols_list := Get_Table_Columns_List(
3224 p_application_id => G_EGO_APPL_ID
3225 -- ,p_from_table_name => l_mul_chg_tl_table_name
3226 ,p_from_table_name => l_mul_prod_tl_table_name
3227 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3228 ,p_from_table_alias_prefix => 'TL'
3229 ,p_cast_date_cols_to_char => TRUE
3230 );
3231
3232
3233 --------------------------------------------------------
3234 -- While we're getting lists of columns, we also get --
3235 -- lists for later use in copying old prod rows --
3236 -- into the pending tables as HISTORY rows --
3237 --------------------------------------------------------
3238
3239
3240 l_hist_b_chg_cols_list := Get_Table_Columns_List(
3241 p_application_id => G_EGO_APPL_ID
3242 -- MK
3243 --,p_from_table_name => l_chg_b_table_name
3244 ,p_from_table_name => l_prod_b_table_name
3245 ,p_from_table_alias_prefix => 'CT'
3246 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3247 );
3248
3249 l_hist_tl_chg_cols_list := Get_Table_Columns_List(
3250 p_application_id => G_EGO_APPL_ID
3251 -- ,p_from_table_name => l_chg_tl_table_name
3252 ,p_from_table_name => l_prod_tl_table_name
3253 ,p_from_table_alias_prefix => 'CT'
3254 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3255 );
3256
3257 l_hist_b_prod_cols_list := Get_Table_Columns_List(
3258 p_application_id => G_EGO_APPL_ID
3259 ,p_from_table_name => l_prod_b_table_name
3260 ,p_from_table_alias_prefix => 'PT'
3261 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3262 );
3263
3264 l_hist_tl_prod_cols_list := Get_Table_Columns_List(
3265 p_application_id => G_EGO_APPL_ID
3266 ,p_from_table_name => l_prod_tl_table_name
3267 ,p_from_table_alias_prefix => 'PT'
3268 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3269 );
3270
3271
3272 l_mul_hist_b_chg_cols_list := Get_Table_Columns_List(
3273 p_application_id => G_EGO_APPL_ID
3274 -- ,p_from_table_name => l_mul_chg_b_table_name
3275 ,p_from_table_name => l_mul_prod_b_table_name
3276 ,p_from_table_alias_prefix => 'CT'
3277 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3278 );
3279
3280 l_mul_hist_tl_chg_cols_list := Get_Table_Columns_List(
3281 p_application_id => G_EGO_APPL_ID
3282 -- MK
3283 -- ,p_from_table_name => l_mul_chg_tl_table_name
3284 ,p_from_table_name => l_mul_prod_tl_table_name
3285 ,p_from_table_alias_prefix => 'CT'
3286 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3287 );
3288
3289 l_mul_hist_b_prod_cols_list := Get_Table_Columns_List(
3290 p_application_id => G_EGO_APPL_ID
3291 ,p_from_table_name => l_mul_prod_b_table_name
3292 ,p_from_table_alias_prefix => 'PT'
3293 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3294 );
3295
3296 l_mul_hist_tl_prod_cols_list := Get_Table_Columns_List(
3297 p_application_id => G_EGO_APPL_ID
3298 ,p_from_table_name => l_mul_prod_tl_table_name
3299 ,p_from_table_alias_prefix => 'PT'
3300 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
3301 );
3302
3303
3304
3305
3306 IF l_single_row_change_found THEN
3307
3308 Write_Debug('Processing Item GDSN Single Change . . . ' );
3309
3310 -----------------------------------------------------------------
3311 -- Now we build the SQL for our dynamic cursor for Single Row --
3312 -----------------------------------------------------------------
3313 l_dynamic_sql := 'SELECT '||l_chg_col_names_list||','||
3314 l_b_chg_cols_list||','||
3315 l_tl_chg_cols_list||
3316 ' FROM '||l_chg_b_table_name ||' B,'||
3317 l_chg_tl_table_name ||' TL'||
3318 ' WHERE B.ACD_TYPE <> ''HISTORY'' AND B.IMPLEMENTATION_DATE IS NULL'||
3319 ' AND B.ACD_TYPE = TL.ACD_TYPE'||
3320 ' AND B.EXTENSION_ID = TL.EXTENSION_ID' ||
3321 ' AND B.CHANGE_LINE_ID = TL.CHANGE_LINE_ID'||
3322 ' AND B.CHANGE_LINE_ID = :1';
3323
3324
3325 Write_Debug('Item GDSN Single Change Query:' || l_dynamic_sql );
3326
3327 l_cursor_id := DBMS_SQL.Open_Cursor;
3328 DBMS_SQL.Parse(l_cursor_id, l_dynamic_sql, DBMS_SQL.Native);
3329 DBMS_SQL.Bind_Variable(l_cursor_id, ':1', l_change_line_id);
3330 DBMS_SQL.Describe_Columns(l_cursor_id, l_column_count, l_desc_table);
3331
3332 FOR i IN 1 .. l_column_count
3333 LOOP
3334 --
3335 -- NOTE: ASSUMPTION: no PKs will ever be DATE objects
3336 --
3337 -------------------------------------------------------------
3338 -- We define all columns as VARCHAR2(1000) for convenience --
3339 -------------------------------------------------------------
3340 DBMS_SQL.Define_Column(l_cursor_id, i, l_retrieved_value, 1000);
3341 END LOOP;
3342
3343 ----------------------------------
3344 -- Execute our dynamic query... --
3345 ----------------------------------
3346 l_dummy := DBMS_SQL.Execute(l_cursor_id);
3347
3348 ----------------------------------------------------
3349 -- ...then loop through the result set, gathering --
3350 -- the column values and then calling Process_Row --
3351 ----------------------------------------------------
3352 WHILE (DBMS_SQL.Fetch_Rows(l_cursor_id) > 0)
3353 LOOP
3354 l_current_column_index := 1;
3355 l_attr_name_value_pairs.DELETE();
3356 ------------------------------------
3357 -- Get the PK values for this row --
3358 ------------------------------------
3359 -- Item Id
3360 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
3361 l_current_column_index := l_current_column_index + 1;
3362 -- l_pk_column_name_value_pairs(1).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
3363 l_inventory_item_id := TO_NUMBER(l_retrieved_value) ;
3364
3365 Write_Debug('Item GDSN Single Change Item Id: ' || to_char(l_inventory_item_id));
3366
3367 -- Org Id
3368 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
3369 l_current_column_index := l_current_column_index + 1;
3370 -- l_pk_column_name_value_pairs(2).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
3371 l_organization_id := TO_NUMBER(l_retrieved_value) ;
3372 Write_Debug('Item GDSN Single Change Org Id: ' || to_char(l_organization_id));
3373
3374 ----------------------------
3375 -- Determine the ACD Type --
3376 ----------------------------
3377 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
3378 l_current_column_index := l_current_column_index + 1;
3379 l_current_acd_type := l_retrieved_value;
3380
3381 Write_Debug('Item GDSN Single Change ACD Type: ' || l_current_acd_type);
3382 --------------------------
3383 -- Get the extension ID --
3384 --------------------------
3385 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
3386 l_current_column_index := l_current_column_index + 1;
3387 l_current_pending_ext_id := TO_NUMBER(l_retrieved_value);
3388
3389 Write_Debug('Item GDSN Single Change Extension Id: ' || to_char(l_current_pending_ext_id));
3390
3391
3392 -------------------------------------------------------------------
3393 -- Now we loop through the rest of the columns assigning values --
3394 -- to Attr data objects, which we add to a table of such objects --
3395 -------------------------------------------------------------------
3396 FOR i IN l_current_column_index .. l_column_count
3397 LOOP
3398
3399 -----------------------------------------------
3400 -- Get the current column name and its value --
3401 -----------------------------------------------
3402 l_current_column_name := l_desc_table(i).COL_NAME;
3403 DBMS_SQL.Column_Value(l_cursor_id, i, l_retrieved_value);
3404
3405
3406 ------------------------------------------------------------------------
3407 -- See whether the current column belongs to a User-Defined Attribute --
3408 ------------------------------------------------------------------------
3409 -- l_attr_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Find_Metadata_For_Attr(
3410 -- p_attr_metadata_table => l_attr_metadata_table
3411 -- ,p_db_column_name => l_current_column_name
3412 -- );
3413 --
3414 ------------------------------------------------
3415 -- If the current column is an Attr column... --
3416 ------------------------------------------------
3417 IF (l_current_column_name = 'LANGUAGE') THEN
3418
3419 -------------------------------------------------------
3420 -- Determine the Language for passing to Process_Row --
3421 -------------------------------------------------------
3422 l_current_row_language := l_retrieved_value;
3423
3424 Write_Debug('Current Lang: ' || l_current_row_language );
3425
3426 ELSIF (l_current_column_name = 'SOURCE_LANG') THEN
3427
3428 ------------------------------------------------
3429 -- Determine the Source Lang for knowing when --
3430 -- to insert a History row into the B table --
3431 ------------------------------------------------
3432 l_current_row_source_lang := l_retrieved_value;
3433
3434 Write_Debug('Current Row Source Lang: ' || l_current_row_source_lang);
3435
3436 --
3437 -- WE CAN NOT CONSTRUCT following PL/SQL Object dynamically
3438 -- using dynamic SQL
3439 -- So NO NEED TO Constuct this
3440 -- l_single_row_attrs_rec EGO_ITEM_PUB.UCCNET_ATTRS_SINGL_ROW_REC_TYP ;
3441 -- l_multi_row_attrs_tbl EGO_ITEM_PUB.UCCNET_ATTRS_MULTI_ROW_TBL_TYP;
3442 --
3443 -- ELSIF (l_attr_metadata_obj IS NOT NULL AND
3444 -- l_attr_metadata_obj.ATTR_NAME IS NOT NULL AND
3445 -- l_current_column_index IS NOT NULL )
3446 -- THEN
3447 --
3448 -----------------------------------------------------
3449 -- ...then we add its value to our Attr data table --
3450 -----------------------------------------------------
3451 -- l_attr_name_value_pairs.EXTEND();
3452 -- l_attr_name_value_pairs(l_attr_name_value_pairs.LAST) :=
3453 -- EGO_USER_ATTR_DATA_OBJ(
3454 -- 1
3455 -- ,l_current_column_name -- ,l_attr_metadata_obj.ATTR_NAME
3456 -- ,null -- ATTR_VALUE_STR
3457 -- ,null -- ATTR_VALUE_NUM
3458 -- ,null -- ATTR_VALUE_DATE
3459 -- ,null -- ATTR_DISP_VALUE
3460 -- ,null -- ATTR_UNIT_OF_MEASURE (will be set below if necessary)
3461 -- ,-1
3462 -- );
3463
3464 --------------------------------------------------------
3465 -- We assign l_retrieved_value according to data type --
3466 --------------------------------------------------------
3467 -- IF (l_attr_metadata_obj.DATA_TYPE_CODE = 'N') THEN
3468 -----------------------------
3469 -- We deal with UOMs below --
3470 -----------------------------
3471 -- l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_NUM :=
3472 -- TO_NUMBER(l_retrieved_value);
3473 -- ELSIF (l_attr_metadata_obj.DATA_TYPE_CODE = 'X') THEN
3474 -- l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_DATE :=
3475 -- TRUNC(TO_DATE(l_retrieved_value, EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT));
3476 -- ELSIF (l_attr_metadata_obj.DATA_TYPE_CODE = 'Y') THEN
3477 -- l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_DATE :=
3478 -- TO_DATE(l_retrieved_value, EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT);
3479 -- ELSE
3480 -- l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_STR :=
3481 -- l_retrieved_value;
3482 -- END IF;
3483
3484 -------------------------------------------------------------------------
3485 -- No need to process UOM for Item Master AG Type
3486 -- ELSIF (INSTR(l_current_column_name, 'UOM_') = 1) THEN
3487 --
3488 --------------------------------------------
3489 -- Store the UOM column's name and value --
3490 -- in a PL/SQL table for assignment below --
3491 --------------------------------------------
3492 -- l_uom_nv_pairs_index := l_uom_nv_pairs_index + 1;
3493 -- l_uom_column_nv_pairs(l_uom_nv_pairs_index) :=
3494 -- EGO_COL_NAME_VALUE_PAIR_OBJ(l_current_column_name, l_retrieved_value);
3495 --
3496 -------------------------------------------------------------------------
3497
3498 END IF;
3499 END LOOP; -- l_current_column_index
3500
3501
3502 -------------------------------------------------------------------
3503 -- Now that we've got all necessary data and metadata, we try to --
3504 -- find a corresponding production row for this pending row; we --
3505 -- use the new data level values if we have them, because we are --
3506 -- trying to see whether or not the row we're about to move into --
3507 -- the production table already exists there --
3508 -------------------------------------------------------------------
3509
3510 l_current_production_ext_id :=
3511 Get_Ext_Id_For_GDSN_Single_Row(
3512 p_inventory_item_id => l_inventory_item_id
3513 ,p_organization_id => l_organization_id
3514 );
3515
3516 Write_Debug('Item GDSN Single Production Extension Id: ' || to_char(l_current_production_ext_id));
3517
3518 ---------------------------------------------------------------------
3519 -- The mode and extension ID we pass to Process_Row are determined --
3520 -- by the existence of a production row, the ACD Type, and in some --
3521 -- cases by whether the Attr Group is single-row or multi-row --
3522 ---------------------------------------------------------------------
3523 IF (l_current_acd_type = G_ADD_ACD_TYPE) THEN
3524
3525 IF (l_current_production_ext_id IS NULL) THEN
3526 ---------------------------------------
3527 -- If ACD Type is CREATE and there's --
3528 -- no production row, we create one --
3529 ---------------------------------------
3530 l_mode_for_current_row := G_CREATE_TX_TYPE ;
3531 l_ext_id_for_current_row := l_current_pending_ext_id;
3532 ELSE
3533 ------------------------------------------------------
3534 -- If ACD Type is CREATE, there's a production row, --
3535 -- and it's a single-row Attr Group, then someone --
3536 -- created the row after this change was proposed, --
3537 -- so we'll update the production row; we'll also --
3538 -- copy the production Ext ID into the pending row --
3539 -- to record the fact that this pending row updated --
3540 -- this production row --
3541 ------------------------------------------------------
3542 l_mode_for_current_row := G_UPDATE_TX_TYPE;
3543 l_ext_id_for_current_row := l_current_production_ext_id;
3544
3545 ------------------------------------------------------------
3546 -- Process_Row will only process our pending B table row --
3547 -- in the loop when LANGUAGE is NULL or when LANGUAGE = --
3548 -- SOURCE_LANG, so we change the pending row in that loop --
3549 ------------------------------------------------------------
3550 IF (l_current_row_language IS NULL OR
3551 l_current_row_language = l_current_row_source_lang) THEN
3552
3553 Write_Debug('Updating Pending Extenstion Id for Pending B table ...' );
3554
3555 l_utility_dynamic_sql := 'UPDATE '||l_chg_b_table_name||
3556 ' SET EXTENSION_ID = :1'||
3557 ' WHERE EXTENSION_ID = :2'||
3558 ' AND ACD_TYPE = ''ADD'''||
3559 ' AND CHANGE_LINE_ID = :3';
3560 EXECUTE IMMEDIATE l_utility_dynamic_sql
3561 USING l_current_production_ext_id
3562 ,l_current_pending_ext_id
3563 ,p_change_line_id;
3564
3565 END IF;
3566
3567 Write_Debug('Updating Pending Extenstion Id for Pending TL table ...' );
3568
3569 l_utility_dynamic_sql := 'UPDATE '||l_chg_tl_table_name||
3570 ' SET EXTENSION_ID = :1'||
3571 ' WHERE EXTENSION_ID = :2'||
3572 ' AND ACD_TYPE = ''ADD'''||
3573 ' AND CHANGE_LINE_ID = :3'||
3574 ' AND LANGUAGE = :4';
3575 EXECUTE IMMEDIATE l_utility_dynamic_sql
3576 USING l_current_production_ext_id
3577 ,l_current_pending_ext_id
3578 ,p_change_line_id
3579 ,l_current_row_language;
3580
3581 END IF; -- l_current_production_ext_id IS NULL)
3582
3583 ELSIF (l_current_acd_type = G_CHANGE_ACD_TYPE) THEN
3584
3585 IF (l_current_production_ext_id IS NULL) THEN
3586 -------------------------------------------------------------
3587 -- In every case below, we'll use the pending extension ID --
3588 -------------------------------------------------------------
3589 l_ext_id_for_current_row := l_current_pending_ext_id;
3590
3591 -------------------------------------------------------
3592 -- If ACD Type is CHANGE, there's no production row, --
3593 -- and it's a single-row Attr Group, that means that --
3594 -- the row was somehow deleted since this change was --
3595 -- proposed, so we'll need to re-insert the row. --
3596 -------------------------------------------------------
3597 l_mode_for_current_row := G_CREATE_TX_TYPE;
3598 ELSE
3599 ---------------------------------------
3600 -- If ACD Type is CHANGE and there's --
3601 -- a production row, we change it --
3602 ---------------------------------------
3603 l_mode_for_current_row := G_UPDATE_TX_TYPE;
3604 l_ext_id_for_current_row := l_current_production_ext_id;
3605 END IF;
3606
3607 ELSIF (l_current_acd_type = G_DELETE_ACD_TYPE) THEN
3608 IF (l_current_production_ext_id IS NULL) THEN
3609 ---------------------------------------
3610 -- If ACD Type is DELETE and there's --
3611 -- no production row, we do nothing --
3612 ---------------------------------------
3613 l_mode_for_current_row := 'SKIP';
3614
3615 ELSE
3616 ---------------------------------------
3617 -- If ACD Type is DELETE and there's --
3618 -- a production row, we delete it --
3619 ---------------------------------------
3620 l_mode_for_current_row := G_DELETE_TX_TYPE;
3621 l_ext_id_for_current_row := l_current_production_ext_id;
3622 END IF;
3623
3624
3625 ELSE
3626 Write_Debug('Item Attr Change Imple does not support ACD Type: ' || l_current_acd_type);
3627
3628 -- We don't support this in R12
3629 FND_MESSAGE.Set_Name('ENG','ENG_IMPL_INVALID_ACD_TYPE');
3630 FND_MESSAGE.Set_Token('ACD_TYPE', l_current_acd_type);
3631 FND_MSG_PUB.Add;
3632 RAISE FND_API.G_EXC_ERROR;
3633
3634
3635 END IF;
3636
3637 Write_Debug('Mode for Current Row operation: ' || l_mode_for_current_row);
3638
3639 IF (l_mode_for_current_row <> 'SKIP')
3640 THEN
3641
3642 -----------------------------------------------------------
3643 -- If we're altering a production row, we first copy the --
3644 -- row into the pending tables with the ACD Type HISTORY --
3645 -----------------------------------------------------------
3646 IF (l_mode_for_current_row = G_DELETE_TX_TYPE OR
3647 l_mode_for_current_row = G_UPDATE_TX_TYPE) THEN
3648
3649 -----------------------------------------------------------
3650 -- Process_Row will only process our pending B table row --
3651 -- in the loop when LANGUAGE is NULL or when LANGUAGE = --
3652 -- SOURCE_LANG, so we insert a History row in that loop --
3653 -----------------------------------------------------------
3654 IF (l_current_row_language IS NULL OR
3655 l_current_row_language = l_current_row_source_lang)
3656 THEN
3657
3658 Write_Debug('Inserting History Record with ACD Type HISTORY into Pending B Table... ' );
3659
3660 l_utility_dynamic_sql := ' INSERT INTO '||l_chg_b_table_name||' CT ('||
3661 l_hist_b_chg_cols_list||
3662 ', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
3663 ', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
3664 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
3665 ', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID, CT.EXTENSION_ID) SELECT '||
3666 l_hist_b_prod_cols_list||
3667 ', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
3668 ', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
3669 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
3670 ', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID, PT.EXTENSION_ID FROM '||
3671 l_prod_b_table_name||' PT, '||
3672 l_chg_b_table_name || ' CT ' ||
3673 ' WHERE PT.INVENTORY_ITEM_ID = :1'||
3674 ' AND PT.ORGANIZATION_ID = :2'||
3675 ' AND CT.INVENTORY_ITEM_ID = :3'||
3676 ' AND CT.ORGANIZATION_ID = :4'||
3677 ' AND CT.CHANGE_LINE_ID = :5'||
3678 ' AND CT.ACD_TYPE = :6' ||
3679 ' AND PT.EXTENSION_ID = :7'||
3680 ' AND CT.EXTENSION_ID = :8' ;
3681
3682 EXECUTE IMMEDIATE l_utility_dynamic_sql
3683 USING l_inventory_item_id, l_organization_id,
3684 l_inventory_item_id, l_organization_id,
3685 l_change_line_id, l_current_acd_type,
3686 l_ext_id_for_current_row, l_current_pending_ext_id ;
3687
3688 END IF;
3689
3690
3691 ------------------------------------------------------------
3692 -- Process_Row will only process the pending TL table row --
3693 -- whose language matches LANGUAGE, so we only insert a --
3694 -- History row for that row --
3695 ------------------------------------------------------------
3696 Write_Debug('Inserting History Record with ACD Type HISTORY into Pending TL Table... ' );
3697
3698 l_utility_dynamic_sql := ' INSERT INTO '||l_chg_tl_table_name||' CT ('||
3699 l_hist_tl_chg_cols_list||
3700 ', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
3701 ', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
3702 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
3703 ', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID, CT.EXTENSION_ID) ' ||
3704 ' SELECT '||
3705 l_hist_tl_prod_cols_list||
3706 ', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
3707 ', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
3708 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
3709 ', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID, PT.EXTENSION_ID FROM '||
3710 l_prod_tl_table_name||' PT, '||
3711 l_chg_tl_table_name||' CT ' ||
3712 ' WHERE PT.INVENTORY_ITEM_ID = :1'||
3713 ' AND PT.ORGANIZATION_ID = :2'||
3714 ' AND CT.INVENTORY_ITEM_ID = :3'||
3715 ' AND CT.ORGANIZATION_ID = :4'||
3716 ' AND CT.CHANGE_LINE_ID = :5'||
3717 ' AND CT.ACD_TYPE = :6'||
3718 ' AND PT.EXTENSION_ID = :7'||
3719 ' AND CT.EXTENSION_ID = :8' ||
3720 ' AND CT.LANGUAGE = PT.LANGUAGE AND CT.LANGUAGE = :9';
3721
3722 EXECUTE IMMEDIATE l_utility_dynamic_sql
3723 USING l_inventory_item_id, l_organization_id,
3724 l_inventory_item_id, l_organization_id,
3725 l_change_line_id, l_current_acd_type,
3726 l_ext_id_for_current_row, l_current_pending_ext_id ,
3727 l_current_row_language;
3728
3729
3730 END IF; -- Check l_mode_for_current_row
3731
3732
3733 END IF; -- l_mode_for_current_row <> 'SKIP'
3734
3735
3736 END LOOP; -- DBMS_SQL.Fetch_Rows Loop
3737 DBMS_SQL.Close_Cursor(l_cursor_id);
3738
3739
3740 Write_Debug('After Processing Item GDSN Single Change . . . ' );
3741
3742 END IF ; -- l_single_row_change_found
3743
3744
3745 IF l_multi_row_change_found THEN
3746
3747 Write_Debug('Processing Item GDSN Mult-Row Change . . . ' );
3748
3749
3750 -----------------------------------------------------------------
3751 -- Now we build the SQL for our dynamic cursor for Multi Row --
3752 -----------------------------------------------------------------
3753 l_mul_dynamic_sql := 'SELECT '||l_mul_chg_col_names_list||','||
3754 l_mul_b_chg_cols_list||','||
3755 l_mul_tl_chg_cols_list||
3756 ' FROM '||l_mul_chg_b_table_name ||' B,'||
3757 l_mul_chg_tl_table_name ||' TL'||
3758 ' WHERE B.ACD_TYPE <> ''HISTORY'' AND B.IMPLEMENTATION_DATE IS NULL'||
3759 ' AND B.ACD_TYPE = TL.ACD_TYPE'||
3760 ' AND B.EXTENSION_ID = TL.EXTENSION_ID' ||
3761 ' AND B.CHANGE_LINE_ID = TL.CHANGE_LINE_ID'||
3762 ' AND B.CHANGE_LINE_ID = :1 ORDER BY B.ATTR_GROUP_ID';
3763
3764
3765 -- Write_Debug('Item GDSN Multi-Row Change Query:' || l_mul_dynamic_sql );
3766
3767 l_cursor_id := DBMS_SQL.Open_Cursor;
3768 DBMS_SQL.Parse(l_cursor_id, l_mul_dynamic_sql, DBMS_SQL.Native);
3769 DBMS_SQL.Bind_Variable(l_cursor_id, ':1', l_change_line_id);
3770 DBMS_SQL.Describe_Columns(l_cursor_id, l_column_count, l_desc_table);
3771
3772 FOR i IN 1 .. l_column_count
3773 LOOP
3774 --
3775 -- NOTE: ASSUMPTION: no PKs will ever be DATE objects
3776 --
3777 -------------------------------------------------------------
3778 -- We define all columns as VARCHAR2(1000) for convenience --
3779 -------------------------------------------------------------
3780 DBMS_SQL.Define_Column(l_cursor_id, i, l_retrieved_value, 1000);
3781 END LOOP;
3782
3783 ----------------------------------
3784 -- Execute our dynamic query... --
3785 ----------------------------------
3786 l_dummy := DBMS_SQL.Execute(l_cursor_id);
3787
3788 ----------------------------------------------------
3789 -- ...then loop through the result set, gathering --
3790 -- the column values and then calling Process_Row --
3791 ----------------------------------------------------
3792
3793 l_pk_column_name_value_pairs :=
3794 EGO_COL_NAME_VALUE_PAIR_ARRAY(
3795 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', NULL)
3796 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', NULL)
3797 );
3798
3799
3800 -- l_object_id := Get_Object_Id_From_Name(G_EGO_ITEM);
3801 -- l_ext_table_metadata_obj
3802 -- := EGO_USER_ATTRS_COMMON_PVT.Get_Ext_Table_Metadata(l_object_id);
3803
3804
3805 WHILE (DBMS_SQL.Fetch_Rows(l_cursor_id) > 0)
3806 LOOP
3807
3808 Write_Debug('----------- Fetch Mutl-Row Attr Change Rercord ---------- ' );
3809
3810 l_current_column_index := 1;
3811 l_attr_name_value_pairs.DELETE();
3812 ------------------------------------
3813 -- Get the PK values for this row --
3814 ------------------------------------
3815 -- Item Id
3816 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
3817 l_current_column_index := l_current_column_index + 1;
3818 l_pk_column_name_value_pairs(1).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
3819 l_inventory_item_id := TO_NUMBER(l_retrieved_value) ;
3820
3821 Write_Debug('Item GDSN Multi-Row Change Item Id: ' || to_char(l_inventory_item_id));
3822
3823
3824 -- Org Id
3825 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
3826 l_current_column_index := l_current_column_index + 1;
3827 l_pk_column_name_value_pairs(2).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
3828 l_organization_id := TO_NUMBER(l_retrieved_value) ;
3829
3830
3831 Write_Debug('Item GDSN Multi-Row Change Org Id: ' || to_char(l_organization_id));
3832
3833
3834 ----------------------------
3835 -- Determine the ACD Type --
3836 ----------------------------
3837 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
3838 l_current_column_index := l_current_column_index + 1;
3839 l_current_acd_type := l_retrieved_value;
3840
3841 Write_Debug('Item GDSN Multi-Row Change ACD Type: ' || l_current_acd_type);
3842
3843 --------------------------
3844 -- Get the extension ID --
3845 --------------------------
3846 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
3847 l_current_column_index := l_current_column_index + 1;
3848 l_current_pending_ext_id := TO_NUMBER(l_retrieved_value);
3849
3850
3851 Write_Debug('Item GDSN Multi-Row Change Extension Id: ' || to_char(l_current_pending_ext_id));
3852
3853 ---------------------------------------------------------
3854 -- Find the Attr Group metadata from the Attr Group ID --
3855 ---------------------------------------------------------
3856
3857 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
3858 l_current_column_index := l_current_column_index + 1;
3859 l_attr_group_id := TO_NUMBER(l_retrieved_value) ;
3860
3861 Write_Debug('EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata for ' || to_char(l_attr_group_id));
3862
3863 l_attr_group_metadata_obj :=
3864 EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(
3865 p_attr_group_id => l_attr_group_id
3866 );
3867
3868 Write_Debug('After EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata ') ;
3869
3870 -------------------------------------------------------------------
3871 -- Now we loop through the rest of the columns assigning values --
3872 -- to Attr data objects, which we add to a table of such objects --
3873 -------------------------------------------------------------------
3874 FOR i IN l_current_column_index .. l_column_count
3875 LOOP
3876
3877 -----------------------------------------------
3878 -- Get the current column name and its value --
3879 -----------------------------------------------
3880 l_current_column_name := l_desc_table(i).COL_NAME;
3881 DBMS_SQL.Column_Value(l_cursor_id, i, l_retrieved_value);
3882
3883 ------------------------------------------------------------------------
3884 -- See whether the current column belongs to a User-Defined Attribute --
3885 ------------------------------------------------------------------------
3886 l_attr_metadata_table := l_attr_group_metadata_obj.ATTR_METADATA_TABLE;
3887 l_attr_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Find_Metadata_For_Attr(
3888 p_attr_metadata_table => l_attr_metadata_table
3889 ,p_db_column_name => l_current_column_name
3890 );
3891
3892 ------------------------------------------------
3893 -- If the current column is an Attr column... --
3894 ------------------------------------------------
3895 IF (l_current_column_name = 'LANGUAGE') THEN
3896
3897 -------------------------------------------------------
3898 -- Determine the Language for passing to Process_Row --
3899 -------------------------------------------------------
3900 l_current_row_language := l_retrieved_value;
3901
3902 Write_Debug('Current Lang: ' || l_current_row_language );
3903
3904
3905 ELSIF (l_current_column_name = 'SOURCE_LANG') THEN
3906
3907 ------------------------------------------------
3908 -- Determine the Source Lang for knowing when --
3909 -- to insert a History row into the B table --
3910 ------------------------------------------------
3911 l_current_row_source_lang := l_retrieved_value;
3912 END IF;
3913
3914 Write_Debug('Current Row Source Lang: ' || l_current_row_source_lang);
3915
3916 --
3917 -- WE CAN NOT CONSTRUCT following PL/SQL Object dynamically
3918 -- using dynamic SQL
3919 -- So NO NEED TO Constuct this
3920 -- l_single_row_attrs_rec EGO_ITEM_PUB.UCCNET_ATTRS_SINGL_ROW_REC_TYP ;
3921 -- l_multi_row_attrs_tbl EGO_ITEM_PUB.UCCNET_ATTRS_MULTI_ROW_TBL_TYP;
3922 --
3923 /************************************************************/
3924 IF (l_attr_metadata_obj IS NOT NULL AND
3925 l_attr_metadata_obj.ATTR_NAME IS NOT NULL AND
3926 l_current_column_index IS NOT NULL )
3927 THEN
3928
3929 -----------------------------------------------------
3930 -- ...then we add its value to our Attr data table --
3931 -----------------------------------------------------
3932 l_attr_name_value_pairs.EXTEND();
3933 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST) :=
3934 EGO_USER_ATTR_DATA_OBJ(
3935 1
3936 ,l_attr_metadata_obj.ATTR_NAME
3937 ,null -- ATTR_VALUE_STR
3938 ,null -- ATTR_VALUE_NUM
3939 ,null -- ATTR_VALUE_DATE
3940 ,null -- ATTR_DISP_VALUE
3941 ,null -- ATTR_UNIT_OF_MEASURE (will be set below if necessary)
3942 ,-1
3943 );
3944
3945 --------------------------------------------------------
3946 -- We assign l_retrieved_value according to data type --
3947 --------------------------------------------------------
3948 IF (l_attr_metadata_obj.DATA_TYPE_CODE = 'N') THEN
3949 -----------------------------
3950 -- We deal with UOMs below --
3951 -----------------------------
3952 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_NUM :=
3953 TO_NUMBER(l_retrieved_value);
3954 ELSIF (l_attr_metadata_obj.DATA_TYPE_CODE = 'X') THEN
3955 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_DATE :=
3956 TRUNC(TO_DATE(l_retrieved_value, EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT));
3957 ELSIF (l_attr_metadata_obj.DATA_TYPE_CODE = 'Y') THEN
3958 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_DATE :=
3959 TO_DATE(l_retrieved_value, EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT);
3960 ELSE
3961 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_STR :=
3962 l_retrieved_value;
3963 END IF;
3964
3965 -------------------------------------------------------------------------
3966 -- No need to process UOM for Item Master AG Type
3967 -- ELSIF (INSTR(l_current_column_name, 'UOM_') = 1) THEN
3968 --
3969 --------------------------------------------
3970 -- Store the UOM column's name and value --
3971 -- in a PL/SQL table for assignment below --
3972 --------------------------------------------
3973 -- l_uom_nv_pairs_index := l_uom_nv_pairs_index + 1;
3974 -- l_uom_column_nv_pairs(l_uom_nv_pairs_index) :=
3975 -- EGO_COL_NAME_VALUE_PAIR_OBJ(l_current_column_name, l_retrieved_value);
3976 --
3977 -------------------------------------------------------------------------
3978 /***********************************************/
3979
3980 END IF;
3981 END LOOP; -- l_current_column_index
3982
3983 -------------------------------------------------------------------
3984 -- Now that we've got all necessary data and metadata, we try to --
3985 -- find a corresponding production row for this pending row; we --
3986 -- use the new data level values if we have them, because we are --
3987 -- trying to see whether or not the row we're about to move into --
3988 -- the production table already exists there --
3989 -------------------------------------------------------------------
3990
3991 Write_Debug('calling EGO_USER_ATTRS_DATA_PVT.Get_Extension_Id. . . ');
3992
3993 l_current_production_ext_id
3994 := EGO_USER_ATTRS_DATA_PVT.Get_Extension_Id (
3995 p_object_name => G_EGO_ITEM
3996 ,p_attr_group_id => l_attr_group_id
3997 ,p_application_id => G_EGO_APPL_ID
3998 ,p_attr_group_type => G_EGO_ITEM_GTIN_MULTI_ATTRS
3999 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
4000 ,p_data_level_name_value_pairs => null
4001 ,p_attr_name_value_pairs => l_attr_name_value_pairs
4002 ) ;
4003 Write_Debug('After calling EGO_USER_ATTRS_DATA_PVT.Get_Extension_Id. Current Prod Ext Id: ' || to_char(l_current_production_ext_id));
4004
4005 ---------------------------------------------------------------------
4006 -- The mode and extension ID we pass to Process_Row are determined --
4007 -- by the existence of a production row, the ACD Type, and in some --
4008 -- cases by whether the Attr Group is single-row or multi-row --
4009 ---------------------------------------------------------------------
4010 IF (l_current_acd_type = G_ADD_ACD_TYPE) THEN
4011
4012 IF (l_current_production_ext_id IS NULL) THEN
4013 ---------------------------------------
4014 -- If ACD Type is CREATE and there's --
4015 -- no production row, we create one --
4016 ---------------------------------------
4017 l_mode_for_current_row := G_CREATE_TX_TYPE ;
4018 l_ext_id_for_current_row := l_current_pending_ext_id;
4019 ELSE
4020 ---------------------------------------------------------------
4021 -- We let the ADD + multi-row + existing production row case --
4022 -- through so Get_Extension_Id_And_Mode can throw the error --
4023 ---------------------------------------------------------------
4024 l_mode_for_current_row := G_CREATE_TX_TYPE;
4025 l_ext_id_for_current_row := l_current_pending_ext_id;
4026
4027 END IF; -- l_current_production_ext_id IS NULL)
4028
4029 ELSIF (l_current_acd_type = G_CHANGE_ACD_TYPE) THEN
4030
4031 IF (l_current_production_ext_id IS NULL) THEN
4032 -------------------------------------------------------------
4033 -- In every case below, we'll use the pending extension ID --
4034 -------------------------------------------------------------
4035 l_ext_id_for_current_row := l_current_pending_ext_id;
4036
4037 -------------------------------------------------------
4038 -- If ACD Type is CHANGE, there's no production row, --
4039 -- and it's a multi-row Attr Group, there are two --
4040 -- possibilities: either the row was deleted since --
4041 -- this change was proposed (in which case we will --
4042 -- re-insert the row) or else this change involves --
4043 -- changing Unique Key values (in which case the --
4044 -- production row really does still exist, and we --
4045 -- really do want to change it); we look for the --
4046 -- production row using the pending extension ID to --
4047 -- see which of these two possibilities we face now --
4048 -------------------------------------------------------
4049 EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||l_mul_chg_b_table_name||
4050 ' WHERE EXTENSION_ID = :1'
4051 INTO l_dummy
4052 USING l_current_pending_ext_id;
4053
4054 IF (l_dummy > 0) THEN
4055 l_mode_for_current_row := G_UPDATE_TX_TYPE;
4056
4057 ELSE
4058 l_mode_for_current_row := G_CREATE_TX_TYPE;
4059 END IF;
4060
4061 ELSE
4062 ---------------------------------------
4063 -- If ACD Type is CHANGE and there's --
4064 -- a production row, we change it --
4065 ---------------------------------------
4066 l_mode_for_current_row := G_UPDATE_TX_TYPE;
4067 l_ext_id_for_current_row := l_current_production_ext_id;
4068 END IF;
4069
4070 ELSIF (l_current_acd_type = G_DELETE_ACD_TYPE) THEN
4071 IF (l_current_production_ext_id IS NULL) THEN
4072 ---------------------------------------
4073 -- If ACD Type is DELETE and there's --
4074 -- no production row, we do nothing --
4075 ---------------------------------------
4076 l_mode_for_current_row := 'SKIP';
4077 ELSE
4078 ---------------------------------------
4079 -- If ACD Type is DELETE and there's --
4080 -- a production row, we delete it --
4081 ---------------------------------------
4082 l_mode_for_current_row := G_DELETE_TX_TYPE;
4083 l_ext_id_for_current_row := l_current_production_ext_id;
4084 END IF;
4085
4086
4087 ELSE
4088 -- Invalid Case
4089 Write_Debug('Item Attr Change Imple does not support ACD Type: ' || l_current_acd_type);
4090
4091 -- We don't support this in R12
4092 FND_MESSAGE.Set_Name('ENG','ENG_IMPL_INVALID_ACD_TYPE');
4093 FND_MESSAGE.Set_Token('ACD_TYPE', l_current_acd_type);
4094 FND_MSG_PUB.Add;
4095 RAISE FND_API.G_EXC_ERROR;
4096
4097 END IF;
4098
4099
4100 IF (l_mode_for_current_row <> 'SKIP')
4101 THEN
4102
4103 -----------------------------------------------------------
4104 -- If we're altering a production row, we first copy the --
4105 -- row into the pending tables with the ACD Type HISTORY --
4106 -----------------------------------------------------------
4107 IF (l_mode_for_current_row = G_DELETE_TX_TYPE OR
4108 l_mode_for_current_row = G_UPDATE_TX_TYPE) THEN
4109
4110 -----------------------------------------------------------
4111 -- Process_Row will only process our pending B table row --
4112 -- in the loop when LANGUAGE is NULL or when LANGUAGE = --
4113 -- SOURCE_LANG, so we insert a History row in that loop --
4114 -----------------------------------------------------------
4115 IF (l_current_row_language IS NULL OR
4116 l_current_row_language = l_current_row_source_lang)
4117 THEN
4118
4119 Write_Debug('Inserting History Row with the ACD Type HISTORY for Multi-Row B table');
4120
4121 l_utility_dynamic_sql := ' INSERT INTO '||l_mul_chg_b_table_name||' CT ('||
4122 l_mul_hist_b_chg_cols_list||
4123 ', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
4124 ', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
4125 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
4126 ', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID, CT.EXTENSION_ID, CT.ATTR_GROUP_ID) ' ||
4127 ' SELECT '||
4128 l_mul_hist_b_prod_cols_list||
4129 ', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
4130 ', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
4131 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'' '||
4132 ', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID, PT.EXTENSION_ID, PT.ATTR_GROUP_ID FROM '||
4133 l_mul_prod_b_table_name||' PT, '||
4134 l_mul_chg_b_table_name || ' CT ' ||
4135 ' WHERE PT.INVENTORY_ITEM_ID = :1'||
4136 ' AND PT.ORGANIZATION_ID = :2'||
4137 ' AND CT.INVENTORY_ITEM_ID = :3'||
4138 ' AND CT.ORGANIZATION_ID = :4'||
4139 ' AND CT.CHANGE_LINE_ID = :5'||
4140 ' AND CT.ACD_TYPE = :6' ||
4141 ' AND PT.EXTENSION_ID = :7'||
4142 ' AND CT.EXTENSION_ID = :8' ;
4143
4144
4145
4146 -- Write_Debug('Insert Stme:' || l_utility_dynamic_sql);
4147 -- Write_Debug('l_ext_id_for_current_row:' || to_char(l_ext_id_for_current_row));
4148 -- Write_Debug('l_current_pending_ext_id:' || to_char(l_current_pending_ext_id));
4149
4150
4151 EXECUTE IMMEDIATE l_utility_dynamic_sql
4152 USING l_inventory_item_id, l_organization_id,
4153 l_inventory_item_id, l_organization_id,
4154 l_change_line_id, l_current_acd_type,
4155 l_ext_id_for_current_row, l_current_pending_ext_id ;
4156
4157
4158 END IF;
4159
4160
4161 ------------------------------------------------------------
4162 -- Process_Row will only process the pending TL table row --
4163 -- whose language matches LANGUAGE, so we only insert a --
4164 -- History row for that row --
4165 ------------------------------------------------------------
4166
4167 Write_Debug('Inserting History Row with the ACD Type HISTORY for Multi-Row TL table');
4168
4169 l_utility_dynamic_sql := ' INSERT INTO '||l_mul_chg_tl_table_name||' CT ('||
4170 l_mul_hist_tl_chg_cols_list||
4171 ', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
4172 ', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
4173 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE '||
4174 ', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID, CT.EXTENSION_ID, CT.ATTR_GROUP_ID ) ' ||
4175 ' SELECT '||
4176 l_mul_hist_tl_prod_cols_list||
4177 ', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
4178 ', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
4179 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
4180 ', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID, PT.EXTENSION_ID, PT.ATTR_GROUP_ID FROM '||
4181 l_mul_prod_tl_table_name||' PT, '||
4182 l_mul_chg_tl_table_name||' CT ' ||
4183 ' WHERE PT.INVENTORY_ITEM_ID = :1'||
4184 ' AND PT.ORGANIZATION_ID = :2'||
4185 ' AND CT.INVENTORY_ITEM_ID = :3'||
4186 ' AND CT.ORGANIZATION_ID = :4'||
4187 ' AND CT.CHANGE_LINE_ID = :5'||
4188 ' AND CT.ACD_TYPE = :6'||
4189 ' AND PT.EXTENSION_ID = :7'||
4190 ' AND CT.EXTENSION_ID = :8' ||
4191 ' AND CT.LANGUAGE = PT.LANGUAGE AND CT.LANGUAGE = :9';
4192
4193 EXECUTE IMMEDIATE l_utility_dynamic_sql
4194 USING l_inventory_item_id, l_organization_id,
4195 l_inventory_item_id, l_organization_id,
4196 l_change_line_id, l_current_acd_type,
4197 l_ext_id_for_current_row, l_current_pending_ext_id ,
4198 l_current_row_language;
4199
4200 END IF; -- Check l_mode_for_current_row
4201
4202
4203 END IF; -- l_mode_for_current_row <> 'SKIP'
4204
4205
4206 END LOOP; -- DBMS_SQL.Fetch_Rows Loop
4207 DBMS_SQL.Close_Cursor(l_cursor_id);
4208
4209 Write_Debug('--------------------------------------------------' );
4210 Write_Debug('After Processing Item GDSN Mult-Row Changes . . . ' );
4211
4212 END IF ; -- l_multi_row_change_found
4213
4214
4215
4216 GetNLSLanguage(l_orig_nls_lang,l_orig_territory,l_orig_chrs);
4217
4218
4219
4220 FOR l_rec IN c_lang
4221 LOOP
4222 l_installed_flag := l_rec.INSTALLED_FLAG ;
4223 l_lang_code := l_rec.LANGUAGE_CODE;
4224 l_nls_lang := l_rec.NLS_LANGUAGE;
4225 l_territory := l_rec.NLS_TERRITORY ;
4226
4227 -- Set NLS Lang so that the row for lang is processed correctly
4228 SetNLSLanguage(l_nls_lang ,l_territory);
4229
4230 Write_Debug('Constructing GDSN Attribute Rows PL/SQL Objects . .. ' ) ;
4231 Write_Debug('for Lang Code: ' || l_lang_code || ' - Installed Flag: ' || l_installed_flag );
4232 Write_Debug('NLS Lang : ' || l_nls_lang || ' - NLS Territory: ' || l_territory );
4233 Write_Debug('Calling ENG_CHANGE_IMPORT_UTIL.MERGE_GDSN_PENDING_CHG_ROWS. . . ' );
4234
4235 l_multi_row_attrs_tbl.DELETE;
4236 ENG_CHANGE_IMPORT_UTIL.MERGE_GDSN_PENDING_CHG_ROWS
4237 ( p_inventory_item_id => l_inventory_item_id
4238 ,p_organization_id => l_organization_id
4239 ,p_change_id => l_change_id
4240 ,p_change_line_id => l_change_line_id
4241 ,p_acd_type => NULL
4242 ,x_single_row_attrs_rec => l_single_row_attrs_rec
4243 ,x_multi_row_attrs_tbl => l_multi_row_attrs_tbl
4244 ,x_extra_attrs_rec => l_extra_attrs_rec
4245 ) ;
4246
4247 Write_Debug('After ENG_CHANGE_IMPORT_UTIL.MERGE_GDSN_PENDING_CHG_ROWS. . . ' );
4248
4249
4250
4251 BEGIN
4252 Write_Debug('Calling EGO_GTIN_ATTRS_PVT.Process_UCCnet_Attrs_For_Item . . . ' );
4253
4254 EGO_GTIN_ATTRS_PVT.Process_UCCnet_Attrs_For_Item(
4255 p_api_version => p_api_version
4256 ,p_check_policy => FND_API.G_FALSE
4257 ,p_inventory_item_id => l_inventory_item_id
4258 ,p_organization_id => l_organization_id
4259 ,p_single_row_attrs_rec => l_single_row_attrs_rec
4260 ,p_multi_row_attrs_table => l_multi_row_attrs_tbl
4261 ,p_entity_id => null
4262 ,p_entity_index => null
4263 ,p_entity_code => G_BO_IDENTIFIER
4264 ,p_init_error_handler => FND_API.G_FALSE
4265 ,p_commit => FND_API.G_FALSE
4266 ,x_return_status => l_return_status
4267 ,x_errorcode => l_errorcode
4268 ,x_msg_count => l_msg_count
4269 ,x_msg_data => l_msg_data);
4270
4271
4272 EXCEPTION
4273 WHEN OTHERS THEN
4274
4275 FND_MSG_PUB.Add_Exc_Msg
4276 ( p_pkg_name => 'EGO_GTIN_ATTRS_PVT' ,
4277 p_procedure_name => 'Process_UCCnet_Attrs_For_Item',
4278 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
4279 );
4280
4281 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
4282 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_GTIN_ATTRS_PVT.Process_UCCnet_Attrs_For_Item');
4283 FND_MSG_PUB.Add;
4284
4285 Write_Debug('When Others Exception while calling EGO_GTIN_ATTRS_PVT.Process_UCCnet_Attrs_For_Item' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
4286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4287
4288
4289 END ;
4290
4291
4292 Write_Debug('After Calling EGO_GTIN_ATTRS_PVT.Process_UCCnet_Attrs_For_Item. Return Status: ' || l_return_status );
4293
4294 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
4295 THEN
4296
4297 Write_Debug('EGO_GTIN_ATTRS_PVT.Process_UCCnet_Attrs_For_Item failed . .. ' );
4298 Write_Debug('Output - Return Stattus: ' || l_return_status);
4299 Write_Debug('Output - Return Stattus: ' || to_char(l_msg_count));
4300 -- Write_Debug('Output - Return Stattus: ' || substr(l_msg_data,1,200));
4301 FOR cnt IN 1..l_msg_count LOOP
4302 Write_Debug('Error msg - '||cnt ||': '|| FND_MSG_PUB.Get(p_msg_index => cnt, p_encoded => 'F'));
4303 END LOOP;
4304
4305
4306
4307 x_return_status := l_return_status;
4308 x_msg_count := l_msg_count;
4309 x_msg_data := l_msg_data;
4310 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
4311 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_GTIN_ATTRS_PVT.Process_UCCnet_Attrs_For_Item');
4312 FND_MSG_PUB.Add;
4313
4314 RAISE FND_API.G_EXC_ERROR ;
4315
4316 END IF;
4317
4318
4319 END LOOP ; -- installed lang loop
4320
4321 -- reset the existing session language
4322 WF_Notification.SetNLSLanguage(l_orig_nls_lang,l_orig_territory);
4323
4324
4325
4326 ---------------------------------------------------------------------------
4327 -- Finally, set the IMPLEMENTATION_DATE for all rows we just implemented --
4328 ---------------------------------------------------------------------------
4329 IF l_single_row_change_found
4330 THEN
4331
4332 EXECUTE IMMEDIATE ' UPDATE '||l_chg_b_table_name||
4333 ' SET IMPLEMENTATION_DATE = :1'||
4334 ' WHERE CHANGE_LINE_ID = :2'
4335 USING SYSDATE, p_change_line_id;
4336
4337 EXECUTE IMMEDIATE ' UPDATE '||l_chg_tl_table_name||
4338 ' SET IMPLEMENTATION_DATE = :1'||
4339 ' WHERE CHANGE_LINE_ID = :2'
4340 USING SYSDATE, p_change_line_id;
4341
4342
4343 END IF ;
4344
4345 IF l_multi_row_change_found
4346 THEN
4347
4348 Write_Debug('set the IMPLEMENTATION_DATE for all rows we just implemented');
4349
4350 EXECUTE IMMEDIATE ' UPDATE '||l_mul_chg_b_table_name||
4351 ' SET IMPLEMENTATION_DATE = :1'||
4352 ' WHERE CHANGE_LINE_ID = :2'
4353 USING SYSDATE, p_change_line_id;
4354
4355 EXECUTE IMMEDIATE ' UPDATE '||l_mul_chg_tl_table_name||
4356 ' SET IMPLEMENTATION_DATE = :1'||
4357 ' WHERE CHANGE_LINE_ID = :2'
4358 USING SYSDATE, p_change_line_id;
4359
4360
4361 END IF ;
4362 Write_Debug('In Implement Item GDSN Attribute Change, Done');
4363 -- End of API body.
4364
4365
4366 -- Standard check of p_commit.
4367 IF FND_API.To_Boolean( l_commit ) THEN
4368 Write_Debug('COMMIT Item GDSN Attribute Change Implementation');
4369 COMMIT WORK;
4370 END IF;
4371
4372 -- Standard call to get message count and if count is 1, get message info.
4373 FND_MSG_PUB.Count_And_Get
4374 ( p_count => x_msg_count ,
4375 p_data => x_msg_data
4376 );
4377
4378
4379 -----------------------------------------------------
4380 -- Close Error Handler Debug Session.
4381 -----------------------------------------------------
4382 -- Close debug session only explicitly open the debug session for
4383 -- this API.
4384 IF FND_API.to_Boolean(p_debug)
4385 THEN
4386 Close_Debug_Session;
4387 END IF ;
4388
4389
4390 EXCEPTION
4391 WHEN FND_API.G_EXC_ERROR THEN
4392 Write_Debug('When G_EXC_ERROR Exception in impl_rev_item_gdsn_attr_chgs');
4393
4394 x_return_status := G_RET_STS_ERROR ;
4395
4396 IF FND_API.To_Boolean( l_commit ) THEN
4397 Write_Debug('ROLLBACK Item GDSN Attribute Change Implementation TO IMPL_REV_ITEM_GDSN_ATTR_CHGS');
4398 ROLLBACK TO IMPL_REV_ITEM_GDSN_ATTR_CHGS;
4399 END IF;
4400
4401 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
4402 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_gdsn_attr_chgs for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
4403 FND_MSG_PUB.Add;
4404
4405
4406 FND_MSG_PUB.Count_And_Get
4407 ( p_count => x_msg_count ,
4408 p_data => x_msg_data
4409 );
4410
4411 -----------------------------------------------------
4412 -- Close Error Handler Debug Session.
4413 -----------------------------------------------------
4414 -- Close debug session only explicitly open the debug session for
4415 -- this API.
4416 IF FND_API.to_Boolean(p_debug)
4417 THEN
4418 Close_Debug_Session;
4419 END IF ;
4420
4421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4422 Write_Debug('When G_EXC_UNEXPECTED_ERROR Exception in impl_rev_item_gdsn_attr_chgs');
4423
4424 x_return_status := G_RET_STS_UNEXP_ERROR ;
4425
4426 IF FND_API.To_Boolean( l_commit ) THEN
4427 Write_Debug('ROLLBACK Item GDSN Attribute Change Implementation TO IMPL_REV_ITEM_GDSN_ATTR_CHGS');
4428 ROLLBACK TO IMPL_REV_ITEM_GDSN_ATTR_CHGS;
4429 END IF;
4430
4431
4432 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
4433 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_gdsn_attr_chgs for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
4434 FND_MSG_PUB.Add;
4435
4436 FND_MSG_PUB.Count_And_Get
4437 ( p_count => x_msg_count ,
4438 p_data => x_msg_data
4439 );
4440
4441 -----------------------------------------------------
4442 -- Close Error Handler Debug Session.
4443 -----------------------------------------------------
4444 -- Close debug session only explicitly open the debug session for
4445 -- this API.
4446 IF FND_API.to_Boolean(p_debug)
4447 THEN
4448 Close_Debug_Session;
4449 END IF ;
4450
4451 WHEN OTHERS THEN
4452
4453 Write_Debug('When Others Exception in impl_rev_item_gdsn_attr_chgs');
4454
4455 x_return_status := G_RET_STS_UNEXP_ERROR ;
4456
4457 IF FND_API.To_Boolean( l_commit ) THEN
4458 Write_Debug('ROLLBACK Item GDSN Attribute Change Implementation TO IMPL_REV_ITEM_GDSN_ATTR_CHGS');
4459 ROLLBACK TO IMPL_REV_ITEM_GDSN_ATTR_CHGS;
4460 END IF;
4461
4462
4463 Write_Debug('When Others Exception ' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
4464
4465 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
4466 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_gdsn_attr_chgs for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
4467 FND_MSG_PUB.Add;
4468
4469
4470 IF FND_MSG_PUB.Check_Msg_Level
4471 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4472 THEN
4473 FND_MSG_PUB.Add_Exc_Msg
4474 ( G_PKG_NAME ,
4475 l_api_name
4476 );
4477 END IF;
4478
4479 FND_MSG_PUB.Count_And_Get
4480 ( p_count => x_msg_count ,
4481 p_data => x_msg_data
4482 );
4483
4484 -----------------------------------------------------
4485 -- Close Error Handler Debug Session.
4486 -----------------------------------------------------
4487 -- Close debug session only explicitly open the debug session for
4488 -- this API.
4489 IF FND_API.to_Boolean(p_debug)
4490 THEN
4491 Close_Debug_Session;
4492 END IF ;
4493
4494 END impl_rev_item_gdsn_attr_chgs ;
4495
4496
4497 PROCEDURE Implement_Change_Line (
4498 p_api_version IN NUMBER
4499 ,p_object_name IN VARCHAR2
4500 ,p_production_b_table_name IN VARCHAR2
4501 ,p_production_tl_table_name IN VARCHAR2
4502 ,p_change_b_table_name IN VARCHAR2
4503 ,p_change_tl_table_name IN VARCHAR2
4504 ,p_tables_application_id IN NUMBER
4505 ,p_change_line_id IN NUMBER
4506 ,p_old_data_level_nv_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
4507 ,p_new_data_level_nv_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
4508 ,p_related_class_code_function IN VARCHAR2
4509 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
4510 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
4511 ,x_return_status OUT NOCOPY VARCHAR2
4512 ,x_errorcode OUT NOCOPY NUMBER
4513 ,x_msg_count OUT NOCOPY NUMBER
4514 ,x_msg_data OUT NOCOPY VARCHAR2
4515 ) IS
4516
4517 l_api_name CONSTANT VARCHAR2(30) := 'Implement_Change_Line';
4518
4519 --we don't use l_api_version yet, but eventually we might:
4520 --if we change required parameters, version goes FROM n.x to (n+1).x
4521 --if we change optional parameters, version goes FROM x.n to x.(n+1)
4522 l_api_version CONSTANT NUMBER := 1.0;
4523
4524 l_object_id NUMBER;
4525 l_data_level_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY ;
4526 l_current_dl_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
4527 l_ext_table_metadata_obj EGO_EXT_TABLE_METADATA_OBJ;
4528 l_chng_col_names_list VARCHAR2(20000);
4529 l_cols_to_exclude_list VARCHAR2(2000);
4530 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
4531 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
4532 l_b_chng_cols_list VARCHAR2(10000);
4533 l_tl_chng_cols_list VARCHAR2(10000);
4534 l_history_b_chng_cols_list VARCHAR2(10000);
4535 l_history_tl_chng_cols_list VARCHAR2(10000);
4536 l_history_b_prod_cols_list VARCHAR2(10000);
4537 l_history_tl_prod_cols_list VARCHAR2(10000);
4538 l_dynamic_sql VARCHAR2(32767); --the largest a VARCHAR2 can be
4539 l_cursor_id NUMBER;
4540 l_column_count NUMBER;
4541 l_desc_table DBMS_SQL.Desc_Tab;
4542 l_retrieved_value VARCHAR2(1000);
4543 l_dummy NUMBER;
4544 l_current_column_index NUMBER;
4545 l_current_row_language VARCHAR2(30);
4546 l_current_row_source_lang VARCHAR2(30);
4547 l_current_acd_type VARCHAR2(30);
4548 l_attr_group_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
4549 l_current_pending_ext_id NUMBER;
4550 l_current_column_name VARCHAR2(30);
4551 l_attr_metadata_obj EGO_ATTR_METADATA_OBJ;
4552 l_dummy_err_msg_name VARCHAR2(30);
4553 l_token_table ERROR_HANDLER.Token_Tbl_Type;
4554 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE := EGO_USER_ATTR_DATA_TABLE();
4555 l_impl_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE := EGO_USER_ATTR_DATA_TABLE();
4556 l_uom_column_nv_pairs LOCAL_COL_NV_PAIR_TABLE;
4557 l_uom_nv_pairs_index NUMBER := 0;
4558 l_current_uom_col_nv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
4559 l_attr_col_name_for_uom_col VARCHAR2(30);
4560 l_current_production_ext_id NUMBER;
4561 l_mode_for_current_row VARCHAR2(10);
4562 l_ext_id_for_current_row NUMBER;
4563 l_utility_dynamic_sql VARCHAR2(32767); --the largest a VARCHAR2 can be
4564 l_return_status VARCHAR2(1);
4565 l_errorcode NUMBER;
4566 l_msg_count NUMBER;
4567 l_msg_data VARCHAR2(1000);
4568 L_ATTR_GROUP_ID NUMBER;
4569 L_PREV_EXT_ID NUMBER:=-1;
4570 L_DATA_LEVEL_ID NUMBER;
4571 L_DATA_LEVEL_NAME VARCHAR2(80);
4572 L_DATA_LEVEL_META_DATA EGO_DATA_LEVEL_METADATA_OBJ;
4573
4574 BEGIN
4575
4576 IF FND_API.To_Boolean( p_commit ) THEN
4577 -- Standard start of API savepoint
4578 SAVEPOINT Implement_Change_Line_PUB;
4579 END IF;
4580
4581 -- Initialize FND_MSG_PUB and ERROR_HANDLER if necessary
4582 IF (FND_API.To_Boolean(p_init_msg_list)) THEN
4583 FND_MSG_PUB.Initialize;
4584 ERROR_HANDLER.Initialize;
4585 END IF;
4586
4587 -- Check for call compatibility
4588 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
4589 l_api_name, G_PKG_NAME)
4590 THEN
4591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4592 END IF;
4593
4594 -----------------------------------
4595
4596 Write_Debug('In Implement_Change_Line, starting');
4597
4598 -----------------------------------------------
4599 -- First, we get the Object ID for our calls --
4600 -----------------------------------------------
4601 l_object_id := Get_Object_Id_From_Name(p_object_name);
4602
4603 ----------------------------------------------------
4604 -- Determine whether we got new Data Level values --
4605 ----------------------------------------------------
4606 IF (p_new_data_level_nv_pairs IS NOT NULL) THEN
4607 l_data_level_name_value_pairs := p_new_data_level_nv_pairs;
4608 ELSE
4609 l_data_level_name_value_pairs := p_old_data_level_nv_pairs;
4610 END IF;
4611
4612 ---------------------------------------------------------
4613 -- Get the necessary metadata for our production table --
4614 ---------------------------------------------------------
4615 l_ext_table_metadata_obj :=
4616 EGO_USER_ATTRS_COMMON_PVT.Get_Ext_Table_Metadata(l_object_id);
4617
4618 ----------------------------------------------------------
4619 -- Build a PK name/value pair array and begin the lists --
4620 -- of column names to fetch explicitly instead of from --
4621 -- our constructed table columns list --
4622 ----------------------------------------------------------
4623
4624 --
4625 -- ASSUMPTION: no PKs will ever be DATE objects
4626 --
4627 IF (l_ext_table_metadata_obj.pk_column_metadata.COUNT = 5) THEN
4628 l_pk_column_name_value_pairs :=
4629 EGO_COL_NAME_VALUE_PAIR_ARRAY(
4630 EGO_COL_NAME_VALUE_PAIR_OBJ(
4631 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME, NULL
4632 )
4633 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4634 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME, NULL
4635 )
4636 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4637 l_ext_table_metadata_obj.pk_column_metadata(3).COL_NAME, NULL
4638 )
4639 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4640 l_ext_table_metadata_obj.pk_column_metadata(4).COL_NAME, NULL
4641 )
4642 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4643 l_ext_table_metadata_obj.pk_column_metadata(5).COL_NAME, NULL
4644 )
4645 );
4646 l_chng_col_names_list := 'B.'||
4647 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME||
4648 ',B.'||
4649 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME||
4650 ',B.'||
4651 l_ext_table_metadata_obj.pk_column_metadata(3).COL_NAME||
4652 ',B.'||
4653 l_ext_table_metadata_obj.pk_column_metadata(4).COL_NAME||
4654 ',B.'||
4655 l_ext_table_metadata_obj.pk_column_metadata(5).COL_NAME;
4656 l_cols_to_exclude_list := ''''||
4657 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME||
4658 ''','''||
4659 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME||
4660 ''','''||
4661 l_ext_table_metadata_obj.pk_column_metadata(3).COL_NAME||
4662 ''','''||
4663 l_ext_table_metadata_obj.pk_column_metadata(4).COL_NAME||
4664 ''','''||
4665 l_ext_table_metadata_obj.pk_column_metadata(5).COL_NAME||
4666 '''';
4667 ELSIF (l_ext_table_metadata_obj.pk_column_metadata.COUNT = 4) THEN
4668 l_pk_column_name_value_pairs :=
4669 EGO_COL_NAME_VALUE_PAIR_ARRAY(
4670 EGO_COL_NAME_VALUE_PAIR_OBJ(
4671 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME, NULL
4672 )
4673 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4674 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME, NULL
4675 )
4676 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4677 l_ext_table_metadata_obj.pk_column_metadata(3).COL_NAME, NULL
4678 )
4679 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4680 l_ext_table_metadata_obj.pk_column_metadata(4).COL_NAME, NULL
4681 )
4682 );
4683 l_chng_col_names_list := 'B.'||
4684 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME||
4685 ',B.'||
4686 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME||
4687 ',B.'||
4688 l_ext_table_metadata_obj.pk_column_metadata(3).COL_NAME||
4689 ',B.'||
4690 l_ext_table_metadata_obj.pk_column_metadata(4).COL_NAME;
4691 l_cols_to_exclude_list := ''''||
4692 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME||
4693 ''','''||
4694 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME||
4695 ''','''||
4696 l_ext_table_metadata_obj.pk_column_metadata(3).COL_NAME||
4697 ''','''||
4698 l_ext_table_metadata_obj.pk_column_metadata(4).COL_NAME||
4699 '''';
4700 ELSIF (l_ext_table_metadata_obj.pk_column_metadata.COUNT = 3) THEN
4701 l_pk_column_name_value_pairs :=
4702 EGO_COL_NAME_VALUE_PAIR_ARRAY(
4703 EGO_COL_NAME_VALUE_PAIR_OBJ(
4704 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME, NULL
4705 )
4706 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4707 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME, NULL
4708 )
4709 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4710 l_ext_table_metadata_obj.pk_column_metadata(3).COL_NAME, NULL
4711 )
4712 );
4713 l_chng_col_names_list := 'B.'||
4714 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME||
4715 ',B.'||
4716 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME||
4717 ',B.'||
4718 l_ext_table_metadata_obj.pk_column_metadata(3).COL_NAME;
4719 l_cols_to_exclude_list := ''''||
4720 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME||
4721 ''','''||
4722 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME||
4723 ''','''||
4724 l_ext_table_metadata_obj.pk_column_metadata(3).COL_NAME||
4725 '''';
4726 ELSIF (l_ext_table_metadata_obj.pk_column_metadata.COUNT = 2) THEN
4727 l_pk_column_name_value_pairs :=
4728 EGO_COL_NAME_VALUE_PAIR_ARRAY(
4729 EGO_COL_NAME_VALUE_PAIR_OBJ(
4730 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME, NULL
4731 )
4732 ,EGO_COL_NAME_VALUE_PAIR_OBJ(
4733 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME, NULL
4734 )
4735 );
4736 l_chng_col_names_list := 'B.'||
4737 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME||
4738 ',B.'||
4739 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME;
4740 l_cols_to_exclude_list := ''''||
4741 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME||
4742 ''','''||
4743 l_ext_table_metadata_obj.pk_column_metadata(2).COL_NAME||
4744 '''';
4745 ELSIF (l_ext_table_metadata_obj.pk_column_metadata.COUNT = 1) THEN
4746 l_pk_column_name_value_pairs :=
4747 EGO_COL_NAME_VALUE_PAIR_ARRAY(
4748 EGO_COL_NAME_VALUE_PAIR_OBJ(
4749 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME, NULL
4750 )
4751 );
4752 l_chng_col_names_list := 'B.'||
4753 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME;
4754 l_cols_to_exclude_list := ''''||
4755 l_ext_table_metadata_obj.pk_column_metadata(1).COL_NAME||
4756 '''';
4757 END IF;
4758
4759
4760 Write_Debug('After PKs data. . .');
4761
4762 ----------------------------------------------------------
4763 -- Now we add Classification Code columns to the lists, --
4764 -- if necessary; this includes room for a list of Class --
4765 -- Codes that are related to the current Class Code --
4766 ----------------------------------------------------------
4767 IF (l_ext_table_metadata_obj.class_code_metadata IS NOT NULL AND
4768 l_ext_table_metadata_obj.class_code_metadata.COUNT > 0 AND
4769 l_ext_table_metadata_obj.class_code_metadata(1).COL_NAME IS NOT NULL) THEN
4770 l_class_code_name_value_pairs :=
4771 EGO_COL_NAME_VALUE_PAIR_ARRAY(
4772 EGO_COL_NAME_VALUE_PAIR_OBJ(
4773 l_ext_table_metadata_obj.class_code_metadata(1).COL_NAME, NULL
4774 ),
4775 EGO_COL_NAME_VALUE_PAIR_OBJ(
4776 'RELATED_CLASS_CODE_LIST_1', NULL
4777 )
4778 );
4779
4780 l_chng_col_names_list := l_chng_col_names_list||',B.'||
4781 l_ext_table_metadata_obj.class_code_metadata(1).COL_NAME;
4782
4783 l_cols_to_exclude_list := l_cols_to_exclude_list||','''||
4784 l_ext_table_metadata_obj.class_code_metadata(1).COL_NAME||
4785 '''';
4786 END IF;
4787
4788
4789 ---------------------------------------------------------------
4790 -- Next, we add to the lists the rest of the columns that we --
4791 -- either want to get explicitly or don't want to get at all --
4792 ---------------------------------------------------------------
4793 l_chng_col_names_list := l_chng_col_names_list||
4794 ',B.ACD_TYPE,B.ATTR_GROUP_ID,B.EXTENSION_ID' ||
4795 ',B.DATA_LEVEL_ID' ||
4796 ',B.REVISION_ID,B.PK1_VALUE'||
4797 ',B.PK2_VALUE,B.PK3_VALUE'||
4798 ',B.PK4_VALUE,B.PK5_VALUE';
4799
4800 l_cols_to_exclude_list := l_cols_to_exclude_list||
4801 ',''DATA_LEVEL_ID''' ||
4802 ',''REVISION_ID'',''PK1_VALUE'''||
4803 ',''PK2_VALUE'',''PK3_VALUE'''||
4804 ',''PK4_VALUE'',''PK5_VALUE'''||
4805 ',''ACD_TYPE'',''ATTR_GROUP_ID'',''EXTENSION_ID'''||
4806 ',''CHANGE_ID'',''CHANGE_LINE_ID'''||
4807 ',''IMPLEMENTATION_DATE'',''CREATED_BY'''||
4808 ',''CREATION_DATE'',''LAST_UPDATED_BY'''||
4809 ',''LAST_UPDATE_DATE'',''LAST_UPDATE_LOGIN''';
4810
4811 ----------------------------------------------------------
4812 -- Get lists of columns for the B and TL pending tables --
4813 -- (i.e., all Attr cols and the language cols from TL) --
4814 ----------------------------------------------------------
4815 l_b_chng_cols_list := Get_Table_Columns_List(
4816 p_application_id => p_tables_application_id
4817 ,p_from_table_name => p_change_b_table_name
4818 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
4819 ,p_cast_date_cols_to_char => TRUE
4820 );
4821 l_tl_chng_cols_list := Get_Table_Columns_List(
4822 p_application_id => p_tables_application_id
4823 ,p_from_table_name => p_change_tl_table_name
4824 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
4825 ,p_cast_date_cols_to_char => TRUE
4826 );
4827
4828 --------------------------------------------------------
4829 -- While we're getting lists of columns, we also get --
4830 -- lists for later use in copying old production rows --
4831 -- into the pending tables as HISTORY rows --
4832 --------------------------------------------------------
4833 l_cols_to_exclude_list := '''CHANGE_ID'', ''CHANGE_LINE_ID'', ''ACD_TYPE'', ''IMPLEMENTATION_DATE'''||
4834 ', ''EXTENSION_ID'' ,''DATA_LEVEL_ID'',''PK1_VALUE'',''PK2_VALUE'',''PK3_VALUE'''||
4835 ',''PK4_VALUE'', ''PK5_VALUE'',''PROGRAM_ID'', ''PROGRAM_UPDATE_DATE'' , ''REQUEST_ID'' ,''PROGRAM_APPLICATION_ID'' ';
4836 l_history_b_chng_cols_list := Get_Table_Columns_List(
4837 p_application_id => p_tables_application_id
4838 ,p_from_table_name => p_change_b_table_name
4839 ,p_from_table_alias_prefix => 'CT'
4840 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
4841 );
4842
4843 l_history_tl_chng_cols_list := Get_Table_Columns_List(
4844 p_application_id => p_tables_application_id
4845 ,p_from_table_name => p_change_tl_table_name
4846 ,p_from_table_alias_prefix => 'CT'
4847 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
4848 );
4849 l_history_b_prod_cols_list := Get_Table_Columns_List(
4850 p_application_id => p_tables_application_id
4851 ,p_from_table_name => p_production_b_table_name
4852 ,p_from_table_alias_prefix => 'PT'
4853 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
4854 );
4855 l_history_tl_prod_cols_list := Get_Table_Columns_List(
4856 p_application_id => p_tables_application_id
4857 ,p_from_table_name => p_production_tl_table_name
4858 ,p_from_table_alias_prefix => 'PT'
4859 ,p_from_cols_to_exclude_list => l_cols_to_exclude_list
4860 );
4861
4862
4863 Write_Debug('After get meta data for user def attr production and table meta data. . .');
4864
4865 -------------------------------------------------
4866 -- Now we build the SQL for our dynamic cursor --
4867 -------------------------------------------------
4868 l_dynamic_sql := 'SELECT '||l_chng_col_names_list||','||
4869 l_b_chng_cols_list||','||
4870 l_tl_chng_cols_list||
4871 ' FROM '||p_change_b_table_name||' B,'||
4872 p_change_tl_table_name||' TL'||
4873 ' WHERE B.ACD_TYPE <> ''HISTORY'' AND B.IMPLEMENTATION_DATE IS NULL'||
4874 ' AND B.EXTENSION_ID = TL.EXTENSION_ID'||
4875 ' AND B.ACD_TYPE = TL.ACD_TYPE'||
4876 ' AND B.CHANGE_LINE_ID = TL.CHANGE_LINE_ID'||
4877 ' AND B.CHANGE_LINE_ID = :1 ' ||
4878 ' ORDER BY B.EXTENSION_ID';
4879
4880 l_cursor_id := DBMS_SQL.Open_Cursor;
4881 DBMS_SQL.Parse(l_cursor_id, l_dynamic_sql, DBMS_SQL.Native);
4882 DBMS_SQL.Bind_Variable(l_cursor_id, ':1', p_change_line_id);
4883 DBMS_SQL.Describe_Columns(l_cursor_id, l_column_count, l_desc_table);
4884 write_debug('Sql formed for the attr changes : ' || l_dynamic_sql);
4885 FOR i IN 1 .. l_column_count
4886 LOOP
4887
4888 --
4889 -- ASSUMPTION: no PKs will ever be DATE objects
4890 --
4891 -------------------------------------------------------------
4892 -- We define all columns as VARCHAR2(1000) for convenience --
4893 -------------------------------------------------------------
4894 DBMS_SQL.Define_Column(l_cursor_id, i, l_retrieved_value, 1000);
4895 END LOOP;
4896
4897 ----------------------------------
4898 -- Execute our dynamic query... --
4899 ----------------------------------
4900 l_dummy := DBMS_SQL.Execute(l_cursor_id);
4901
4902 ----------------------------------------------------
4903 -- ...then loop through the result set, gathering --
4904 -- the column values and then calling Process_Row --
4905 ----------------------------------------------------
4906 WHILE (DBMS_SQL.Fetch_Rows(l_cursor_id) > 0)
4907 LOOP
4908
4909 l_current_column_index := 1;
4910 l_attr_name_value_pairs.DELETE();
4911
4912 ------------------------------------
4913 -- Get the PK values for this row --
4914 ------------------------------------
4915 IF (l_pk_column_name_value_pairs.COUNT > 0) THEN
4916 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4917 l_current_column_index := l_current_column_index + 1;
4918 l_pk_column_name_value_pairs(1).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
4919 END IF;
4920 IF (l_pk_column_name_value_pairs.COUNT > 1) THEN
4921 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4922 l_current_column_index := l_current_column_index + 1;
4923 l_pk_column_name_value_pairs(2).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
4924 END IF;
4925 IF (l_pk_column_name_value_pairs.COUNT > 2) THEN
4926 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4927 l_current_column_index := l_current_column_index + 1;
4928 l_pk_column_name_value_pairs(3).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
4929 END IF;
4930 IF (l_pk_column_name_value_pairs.COUNT > 3) THEN
4931 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4932 l_current_column_index := l_current_column_index + 1;
4933 l_pk_column_name_value_pairs(4).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
4934 END IF;
4935 IF (l_pk_column_name_value_pairs.COUNT > 4) THEN
4936 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4937 l_current_column_index := l_current_column_index + 1;
4938 l_pk_column_name_value_pairs(5).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
4939 END IF;
4940
4941 ------------------------------------------------
4942 -- Get the Class Code value, if there is one, --
4943 -- and try to get related Class Codes as well --
4944 ------------------------------------------------
4945 IF (l_class_code_name_value_pairs IS NOT NULL AND
4946 l_class_code_name_value_pairs.COUNT > 0) THEN
4947 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4948 l_current_column_index := l_current_column_index + 1;
4949 l_class_code_name_value_pairs(1).VALUE := SUBSTRB(l_retrieved_value, 1, 150);
4950
4951 Write_Debug('Current Item catalog category id in : ' || TO_CHAR(l_class_code_name_value_pairs(1).VALUE));
4952 EXECUTE IMMEDIATE 'BEGIN '||p_related_class_code_function||'(:1, :2); END;'
4953 USING IN l_class_code_name_value_pairs(1).VALUE,
4954 OUT l_class_code_name_value_pairs(2).VALUE;
4955 if l_class_code_name_value_pairs(2).VALUE is not null
4956 then
4957 Write_Debug('Current p_related_class_code in : ' || TO_CHAR(l_class_code_name_value_pairs(2).VALUE));
4958 end if;
4959 END IF;
4960
4961 ----------------------------
4962 -- Determine the ACD Type --
4963 ----------------------------
4964 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4965 l_current_column_index := l_current_column_index + 1;
4966 l_current_acd_type := l_retrieved_value;
4967
4968 ---------------------------------------------------------
4969 -- Find the Attr Group metadata from the Attr Group ID --
4970 ---------------------------------------------------------
4971 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4972 l_current_column_index := l_current_column_index + 1;
4973 L_ATTR_GROUP_ID := TO_NUMBER(l_retrieved_value);
4974 l_attr_group_metadata_obj :=
4975 EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(
4976 p_attr_group_id => L_ATTR_GROUP_ID
4977 );
4978
4979 --------------------------
4980 -- Get the extension ID --
4981 --------------------------
4982 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4983 l_current_column_index := l_current_column_index + 1;
4984 l_current_pending_ext_id := TO_NUMBER(l_retrieved_value);
4985
4986 Write_Debug('Current Pending Ext Id : ' || TO_CHAR(l_current_pending_ext_id));
4987
4988
4989 ---------------------------------------------------------
4990 -- Find the Attr Data Level and Data Level Pks
4991 ---------------------------------------------------------
4992 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
4993 l_current_column_index := l_current_column_index + 1;
4994 L_DATA_LEVEL_ID := TO_NUMBER(l_retrieved_value);
4995 Write_Debug('Current data level Id : ' || TO_CHAR(L_DATA_LEVEL_ID));
4996 if L_DATA_LEVEL_ID is not NULL
4997 then
4998 L_DATA_LEVEL_META_DATA := EGO_USER_ATTRS_COMMON_PVT.Get_Data_Level_Metadata(L_DATA_LEVEL_ID);
4999 L_DATA_LEVEL_NAME := L_DATA_LEVEL_META_DATA.DATA_LEVEL_NAME;
5000 Write_Debug('Current data level name : ' || TO_CHAR(L_DATA_LEVEL_NAME));
5001
5002 IF L_DATA_LEVEL_NAME='ITEM_REVISION_LEVEL'
5003 THEN
5004 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
5005 l_current_column_index := l_current_column_index + 5;
5006 l_data_level_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ(
5007 L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME1,
5008 l_retrieved_value));
5009
5010
5011 ELSE
5012 l_data_level_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ(
5013 null,
5014 null));
5015 l_current_column_index := l_current_column_index + 1;
5016 if L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME1 IS NOT NULL
5017 THEN
5018 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
5019
5020 --l_current_column_index := l_current_column_index + 1;
5021 l_data_level_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ(
5022 L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME1,
5023 l_retrieved_value));
5024 -- ELSE
5025 -- l_current_column_index := l_current_column_index + 1;
5026
5027 END IF;
5028 l_current_column_index := l_current_column_index + 1;
5029 if L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME2 IS NOT NULL
5030 THEN
5031
5032 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
5033 --l_current_column_index := l_current_column_index + 1;
5034 l_data_level_name_value_pairs.EXTEND;
5035 l_data_level_name_value_pairs(l_data_level_name_value_pairs.LAST):= EGO_COL_NAME_VALUE_PAIR_OBJ(
5036 L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME2,
5037 l_retrieved_value);
5038 -- else
5039 --l_current_column_index := l_current_column_index + 1;
5040
5041 END IF;
5042 l_current_column_index := l_current_column_index + 1;
5043 if L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME3 IS NOT NULL
5044 THEN
5045
5046 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
5047 -- l_current_column_index := l_current_column_index + 1;
5048 l_data_level_name_value_pairs.EXTEND;
5049 l_data_level_name_value_pairs(l_data_level_name_value_pairs.LAST):= EGO_COL_NAME_VALUE_PAIR_OBJ(
5050 L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME3,
5051 l_retrieved_value);
5052 -- else
5053 -- l_current_column_index := l_current_column_index + 1;
5054
5055 END IF;
5056 l_current_column_index := l_current_column_index + 1;
5057 if L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME4 IS NOT NULL
5058 THEN
5059
5060 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
5061 --l_current_column_index := l_current_column_index + 1;
5062 l_data_level_name_value_pairs.EXTEND;
5063 l_data_level_name_value_pairs(l_data_level_name_value_pairs.LAST):= EGO_COL_NAME_VALUE_PAIR_OBJ(
5064 L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME4,
5065 l_retrieved_value);
5066 --else
5067 --l_current_column_index := l_current_column_index + 1;
5068
5069 END IF;
5070 l_current_column_index := l_current_column_index + 1;
5071 if L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME5 IS NOT NULL
5072 THEN
5073
5074 DBMS_SQL.Column_Value(l_cursor_id, l_current_column_index, l_retrieved_value);
5075 --l_current_column_index := l_current_column_index + 1;
5076 l_data_level_name_value_pairs.EXTEND;
5077 l_data_level_name_value_pairs(l_data_level_name_value_pairs.LAST):= EGO_COL_NAME_VALUE_PAIR_OBJ(
5078 L_DATA_LEVEL_META_DATA.PK_COLUMN_NAME5,
5079 l_retrieved_value);
5080 --else
5081 -- l_current_column_index := l_current_column_index + 1;
5082 END IF;
5083 l_current_column_index := l_current_column_index + 1;
5084 END IF;
5085 END if ;
5086
5087 ---------------------------------------------------------------
5088 -- Determine whether this Attr Group needs Data Level values --
5089 ---------------------------------------------------------------
5090
5091
5092 IF (EGO_USER_ATTRS_DATA_PVT.Is_Data_Level_Correct(l_object_id
5093 ,l_attr_group_metadata_obj.ATTR_GROUP_ID
5094 ,l_ext_table_metadata_obj
5095 ,l_class_code_name_value_pairs
5096 ,l_data_level_name
5097 ,l_data_level_name_value_pairs
5098 ,l_attr_group_metadata_obj.ATTR_GROUP_DISP_NAME
5099 ,l_dummy_err_msg_name
5100 ,l_token_table)) THEN
5101 l_current_dl_name_value_pairs := l_data_level_name_value_pairs;
5102 ELSE
5103 --------------------------------------------------------------------
5104 -- If the passed-in Data Levels are incorrect (e.g., they include --
5105 -- Revision ID for an Attr Group associated at the Item level), --
5106 -- we will try to pass NULL and hope it works. NOTE: this is an --
5107 -- imperfect fix; it'll work for Items, but maybe not in general --
5108 --------------------------------------------------------------------
5109
5110 --
5111 -- TO DO: make this logic more robust; right now it assumes that either
5112 -- we use all the passed-in Data Levels or none of them, but what about
5113 -- someday if there's a multi-DL implementation (i.e., one in which there's
5114 -- more than a binary situation of "passing DL" or "not passing DL"--e.g.,
5115 -- "passing some but not all DL")?
5116 --
5117 l_token_table.DELETE();
5118 l_current_dl_name_value_pairs := NULL;
5119 END IF;
5120
5121
5122 -------------------------------------------------------------------
5123 -- Now we loop through the rest of the columns assigning values --
5124 -- to Attr data objects, which we add to a table of such objects --
5125 -------------------------------------------------------------------
5126 FOR i IN l_current_column_index .. l_column_count
5127 LOOP
5128
5129 -----------------------------------------------
5130 -- Get the current column name and its value --
5131 -----------------------------------------------
5132
5133 l_current_column_name := l_desc_table(i).COL_NAME;
5134 DBMS_SQL.Column_Value(l_cursor_id, i, l_retrieved_value);
5135 -- write_debug(l_current_column_name||' : ' || l_retrieved_value);
5136 ------------------------------------------------------------------------
5137 -- See whether the current column belongs to a User-Defined Attribute --
5138 ------------------------------------------------------------------------
5139
5140 l_attr_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Find_Metadata_For_Attr(
5141 p_attr_metadata_table => l_attr_group_metadata_obj.attr_metadata_table
5142 ,p_db_column_name => l_current_column_name
5143 );
5144
5145 ------------------------------------------------
5146 -- If the current column is an Attr column... --
5147 ------------------------------------------------
5148 IF (l_attr_metadata_obj IS NOT NULL AND
5149 l_attr_metadata_obj.ATTR_NAME IS NOT NULL) THEN
5150
5151 -----------------------------------------------------
5152 -- ...then we add its value to our Attr data table --
5153 -----------------------------------------------------
5154 l_attr_name_value_pairs.EXTEND();
5155 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST) :=
5156 EGO_USER_ATTR_DATA_OBJ(
5157 l_current_pending_ext_id -- Current Pending Ext Id
5158 ,l_attr_metadata_obj.ATTR_NAME
5159 ,null -- ATTR_VALUE_STR
5160 ,null -- ATTR_VALUE_NUM
5161 ,null -- ATTR_VALUE_DATE
5162 ,null -- ATTR_DISP_VALUE
5163 ,null -- ATTR_UNIT_OF_MEASURE (will be set below if necessary)
5164 ,-1
5165 );
5166
5167 --------------------------------------------------------
5168 -- We assign l_retrieved_value according to data type --
5169 --------------------------------------------------------
5170 IF (l_attr_metadata_obj.DATA_TYPE_CODE = 'N') THEN
5171 -----------------------------
5172 -- We deal with UOMs below --
5173 -----------------------------
5174 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_NUM :=
5175 TO_NUMBER(l_retrieved_value);
5176 ELSIF (l_attr_metadata_obj.DATA_TYPE_CODE = 'X') THEN
5177 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_DATE :=
5178 TRUNC(TO_DATE(l_retrieved_value, EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT));
5179 ELSIF (l_attr_metadata_obj.DATA_TYPE_CODE = 'Y') THEN
5180 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_DATE :=
5181 TO_DATE(l_retrieved_value, EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT);
5182 ELSE
5183 l_attr_name_value_pairs(l_attr_name_value_pairs.LAST).ATTR_VALUE_STR :=
5184 l_retrieved_value;
5185 END IF;
5186 ELSIF (INSTR(l_current_column_name, 'UOM_') = 1) THEN
5187
5188 --------------------------------------------
5189 -- Store the UOM column's name and value --
5190 -- in a PL/SQL table for assignment below --
5191 --------------------------------------------
5192 l_uom_nv_pairs_index := l_uom_nv_pairs_index + 1;
5193 l_uom_column_nv_pairs(l_uom_nv_pairs_index) :=
5194 EGO_COL_NAME_VALUE_PAIR_OBJ(l_current_column_name, l_retrieved_value);
5195
5196 ELSIF (l_current_column_name = 'LANGUAGE') THEN
5197
5198 -------------------------------------------------------
5199 -- Determine the Language for passing to Process_Row --
5200 -------------------------------------------------------
5201 l_current_row_language := l_retrieved_value;
5202
5203 ELSIF (l_current_column_name = 'SOURCE_LANG') THEN
5204
5205 ------------------------------------------------
5206 -- Determine the Source Lang for knowing when --
5207 -- to insert a History row into the B table --
5208 ------------------------------------------------
5209 l_current_row_source_lang := l_retrieved_value;
5210
5211 END IF;
5212 END LOOP;
5213
5214 ---------------------------------------------------------
5215 -- If we gathered any UOM data, we assign all gathered --
5216 -- UOM values to the appropriate Attr data object --
5217 ---------------------------------------------------------
5218 IF (l_uom_nv_pairs_index > 0) THEN
5219
5220 FOR i IN 1 .. l_uom_nv_pairs_index
5221 LOOP
5222
5223 l_current_uom_col_nv_obj := l_uom_column_nv_pairs(i);
5224
5225 ----------------------------------------------
5226 -- We derive the Attr's DB column name from --
5227 -- the UOM column name in one of two ways --
5228 ----------------------------------------------
5229 IF (INSTR(l_current_uom_col_nv_obj.NAME, 'UOM_EXT_ATTR') = 1) THEN
5230 l_attr_col_name_for_uom_col := 'N_'||SUBSTR(l_current_uom_col_nv_obj.NAME, 5);
5231 ELSE
5232 l_attr_col_name_for_uom_col := SUBSTR(l_current_uom_col_nv_obj.NAME, 5);
5233 END IF;
5234
5235 -------------------------------------------------------------
5236 -- Now we find the Attr from the column name we've derived --
5237 -- and set its Attr data object's UOM field with our value --
5238 -------------------------------------------------------------
5239 IF (l_attr_name_value_pairs IS NOT NULL AND
5240 l_attr_name_value_pairs.COUNT > 0) THEN
5241
5242 l_attr_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Find_Metadata_For_Attr(
5243 p_attr_metadata_table => l_attr_group_metadata_obj.attr_metadata_table
5244 ,p_db_column_name => l_attr_col_name_for_uom_col
5245 );
5246
5247 ------------------------------------------------------------------
5248 -- If we found the metadata object, we look for the data object --
5249 ------------------------------------------------------------------
5250 IF (l_attr_metadata_obj IS NOT NULL AND
5251 l_attr_metadata_obj.ATTR_NAME IS NOT NULL) THEN
5252
5253 FOR j IN l_attr_name_value_pairs.FIRST .. l_attr_name_value_pairs.LAST
5254 LOOP
5255 IF (l_attr_name_value_pairs(j).ATTR_NAME =
5256 l_attr_metadata_obj.ATTR_NAME) THEN
5257
5258 -----------------------------------------------------------
5259 -- When we find the data object, we set its UOM and exit --
5260 -----------------------------------------------------------
5261 l_attr_name_value_pairs(j).ATTR_UNIT_OF_MEASURE :=
5262 l_current_uom_col_nv_obj.VALUE;
5263 EXIT;
5264
5265 END IF;
5266 END LOOP;
5267 END IF;
5268 END IF;
5269 END LOOP;
5270 END IF;
5271
5272 -------------------------------------------------------------------
5273 -- Now that we've got all necessary data and metadata, we try to --
5274 -- find a corresponding production row for this pending row; we --
5275 -- use the new data level values if we have them, because we are --
5276 -- trying to see whether or not the row we're about to move into --
5277 -- the production table already exists there --
5278 -------------------------------------------------------------------
5279 IF (l_current_acd_type = 'CHANGE')
5280 THEN
5281 Write_Debug('Calling ENG_CHANGE_ATTR_UTIL.SETUP_IMPL_ATTR_DATA_ROW. . .');
5282
5283
5284
5285
5286 ENG_CHANGE_ATTR_UTIL.SETUP_IMPL_ATTR_DATA_ROW
5287 (
5288 p_api_version => p_api_version
5289 ,p_object_name => p_object_name
5290 ,p_attr_group_id => l_attr_group_metadata_obj.ATTR_GROUP_ID
5291 ,p_application_id => l_attr_group_metadata_obj.APPLICATION_ID
5292 ,p_attr_group_type => l_attr_group_metadata_obj.ATTR_GROUP_TYPE
5293 ,p_attr_group_name => l_attr_group_metadata_obj.ATTR_GROUP_NAME
5294 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
5295 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
5296 ,p_data_level_name => L_DATA_LEVEL_NAME
5297 ,p_data_level_name_value_pairs => l_current_dl_name_value_pairs
5298 ,p_attr_name_value_pairs => l_attr_name_value_pairs
5299 ,x_setup_attr_data => l_impl_attr_name_value_pairs
5300 ,x_return_status => x_return_status
5301 ,x_errorcode => x_errorcode
5302 ,x_msg_count => x_msg_count
5303 ,x_msg_data => x_msg_data
5304 );
5305
5306 ELSIF (l_current_acd_type = 'ADD' OR l_current_acd_type = 'DELETE' )
5307 THEN
5308 l_impl_attr_name_value_pairs := l_attr_name_value_pairs;
5309 END IF;
5310
5311 l_current_production_ext_id :=
5312 EGO_USER_ATTRS_DATA_PVT.Get_Extension_Id_For_Row(
5313 p_attr_group_metadata_obj => l_attr_group_metadata_obj
5314 ,p_ext_table_metadata_obj => l_ext_table_metadata_obj
5315 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
5316 ,p_data_level => l_data_level_name
5317 ,p_data_level_name_value_pairs => l_current_dl_name_value_pairs
5318 ,p_attr_name_value_pairs => l_impl_attr_name_value_pairs
5319 );
5320
5321
5322 Write_Debug('Current Production Ext Id : ' || TO_CHAR(l_current_production_ext_id));
5323
5324
5325 ---------------------------------------------------------------------
5326 -- The mode and extension ID we pass to Process_Row are determined --
5327 -- by the existence of a production row, the ACD Type, and in some --
5328 -- cases by whether the Attr Group is single-row or multi-row --
5329 ---------------------------------------------------------------------
5330 IF (l_current_acd_type = 'ADD') THEN
5331
5332
5333 IF (l_current_production_ext_id IS NULL) THEN
5334 ---------------------------------------
5335 -- If ACD Type is CREATE and there's --
5336 -- no production row, we create one --
5337 ---------------------------------------
5338 Write_Debug('Current Acd Type : ADD and there is no production row with pk same values ');
5339
5340 l_mode_for_current_row := G_IMPLEMENT_CREATE_MODE;
5341 l_ext_id_for_current_row := l_current_pending_ext_id;
5342 ELSE
5343 IF (l_attr_group_metadata_obj.MULTI_ROW_CODE = 'N') THEN
5344 ------------------------------------------------------
5345 -- If ACD Type is CREATE, there's a production row, --
5346 -- and it's a single-row Attr Group, then someone --
5347 -- created the row after this change was proposed, --
5348 -- so we'll update the production row; we'll also --
5349 -- copy the production Ext ID into the pending row --
5350 -- to record the fact that this pending row updated --
5351 -- this production row --
5352 ------------------------------------------------------
5353 Write_Debug('Current Acd Type : ADD and there is a production row with pk same values ');
5354 Write_Debug('IN case of single-row Attr Group, we will update the value');
5355
5356
5357 l_mode_for_current_row := G_UPDATE_TX_TYPE;
5358 l_ext_id_for_current_row := l_current_production_ext_id;
5359
5360 ------------------------------------------------------------
5361 -- Process_Row will only process our pending B table row --
5362 -- in the loop when LANGUAGE is NULL or when LANGUAGE = --
5363 -- SOURCE_LANG, so we change the pending row in that loop --
5364 ------------------------------------------------------------
5365 IF (l_current_row_language IS NULL OR
5366 l_current_row_language = l_current_row_source_lang) THEN
5367
5368 l_utility_dynamic_sql := 'UPDATE '||p_change_b_table_name||
5369 ' SET EXTENSION_ID = :1'||
5370 ' WHERE EXTENSION_ID = :2'||
5371 ' AND ACD_TYPE = ''ADD'''||
5372 ' AND CHANGE_LINE_ID = :3';
5373 EXECUTE IMMEDIATE l_utility_dynamic_sql
5374 USING l_current_production_ext_id
5375 ,l_current_pending_ext_id
5376 ,p_change_line_id;
5377
5378 END IF;
5379
5380 l_utility_dynamic_sql := 'UPDATE '||p_change_tl_table_name||
5381 ' SET EXTENSION_ID = :1'||
5382 ' WHERE EXTENSION_ID = :2'||
5383 ' AND ACD_TYPE = ''ADD'''||
5384 ' AND CHANGE_LINE_ID = :3'||
5385 ' AND LANGUAGE = :4';
5386 EXECUTE IMMEDIATE l_utility_dynamic_sql
5387 USING l_current_production_ext_id
5388 ,l_current_pending_ext_id
5389 ,p_change_line_id
5390 ,l_current_row_language;
5391
5392 ELSE
5393
5394 Write_Debug('Current Acd Type : ADD and there is a production row with pk same values ');
5395 Write_Debug('IN case of multi-row Attr Group, we will let it trough and get exception later');
5396
5397 ---------------------------------------------------------------
5398 -- We let the ADD + multi-row + existing production row case --
5399 -- through so Get_Extension_Id_And_Mode can throw the error --
5400 ---------------------------------------------------------------
5401 l_mode_for_current_row := G_IMPLEMENT_CREATE_MODE;
5402 l_ext_id_for_current_row := l_current_pending_ext_id;
5403 END IF;
5404 END IF;
5405 ELSIF (l_current_acd_type = 'CHANGE') THEN
5406 IF (l_current_production_ext_id IS NULL ) THEN
5407 -------------------------------------------------------------
5408 -- In every case below, we'll use the pending extension ID --
5409 -------------------------------------------------------------
5410 l_ext_id_for_current_row := l_current_pending_ext_id;
5411 --
5412 -- TO DO: check if pendingExtID is in prod; if so, error
5413 --
5414
5415 IF (l_attr_group_metadata_obj.MULTI_ROW_CODE = 'N') THEN
5416
5417 Write_Debug('Current Acd Type : CHANGE and there is a production row with pk same values ');
5418 Write_Debug('IN case of multi-row Attr Group, we will let it trough and get exception later');
5419 -------------------------------------------------------
5420 -- If ACD Type is CHANGE, there's no production row, --
5421 -- and it's a single-row Attr Group, that means that --
5422 -- the row was somehow deleted since this change was --
5423 -- proposed, so we'll need to re-insert the row. --
5424 -------------------------------------------------------
5425 l_mode_for_current_row := G_IMPLEMENT_CREATE_MODE;
5426 ELSE
5427
5428 Write_Debug('Current Acd Type CHANGE: and there is NO production row with pk same values ');
5429 -------------------------------------------------------
5430 -- If ACD Type is CHANGE, there's no production row, --
5431 -- and it's a multi-row Attr Group, there are two --
5432 -- possibilities: either the row was deleted since --
5433 -- this change was proposed (in which case we will --
5434 -- re-insert the row) or else this change involves --
5435 -- changing Unique Key values (in which case the --
5436 -- production row really does still exist, and we --
5437 -- really do want to change it); we look for the --
5438 -- production row using the pending extension ID to --
5439 -- see which of these two possibilities we face now --
5440 -------------------------------------------------------
5441 EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||p_change_b_table_name||
5442 ' WHERE EXTENSION_ID = :1'
5443 INTO l_dummy
5444 USING l_current_pending_ext_id;
5445
5446 IF (l_dummy > 0) THEN
5447 Write_Debug('Set Update mode to this row');
5448 l_mode_for_current_row := G_UPDATE_TX_TYPE;
5449 ELSE
5450 Write_Debug('Set Create mode to this row');
5451 l_mode_for_current_row := G_IMPLEMENT_CREATE_MODE;
5452 END IF;
5453 END IF;
5454 ELSE
5455 ---------------------------------------
5456 -- If ACD Type is CHANGE and there's --
5457 -- a production row, we change it --
5458 ---------------------------------------
5459 l_mode_for_current_row := G_UPDATE_TX_TYPE;
5460 l_ext_id_for_current_row := l_current_production_ext_id;
5461 END IF;
5462 ELSIF (l_current_acd_type = 'DELETE') THEN
5463
5464 Write_Debug('Current Acd Type DELETE: check the pending ext id row exits in prod table');
5465
5466 -- R12, we don't store no-change attr in pending table
5467 -- l_current_production_ext_id is always null
5468 -- So we check if a record with pend ext id does exist in prod table
5469 -- If yes, delete the prod record
5470
5471 -- IF (l_current_production_ext_id IS NULL) THEN
5472 ---------------------------------------
5473 -- If ACD Type is DELETE and there's --
5474 -- no production row, we do nothing --
5475 ---------------------------------------
5476 EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '|| p_production_b_table_name ||
5477 ' WHERE EXTENSION_ID = :1'
5478 INTO l_dummy
5479 USING l_current_pending_ext_id;
5480
5481 IF (l_dummy > 0) THEN
5482
5483 Write_Debug('Set Delete mode to this row');
5484
5485 l_mode_for_current_row := G_DELETE_TX_TYPE ;
5486 l_current_production_ext_id := l_current_pending_ext_id ;
5487 l_ext_id_for_current_row := l_current_pending_ext_id ;
5488
5489 ELSE
5490
5491 Write_Debug('Set Skip mode to this row ');
5492 l_mode_for_current_row := 'SKIP';
5493 END IF;
5494
5495 -- ELSE
5496 ---------------------------------------
5497 -- If ACD Type is DELETE and there's --
5498 -- a production row, we delete it --
5499 ---------------------------------------
5500 -- l_mode_for_current_row := G_DELETE_TX_TYPE;
5501 -- l_ext_id_for_current_row := l_current_production_ext_id;
5502 -- END IF;
5503 END IF;
5504
5505 IF (l_mode_for_current_row <> 'SKIP') THEN
5506
5507 -----------------------------------------------------------
5508 -- If we're altering a production row, we first copy the --
5509 -- row into the pending tables with the ACD Type HISTORY --
5510 -----------------------------------------------------------
5511 IF (l_mode_for_current_row = G_DELETE_TX_TYPE OR
5512 l_mode_for_current_row = G_UPDATE_TX_TYPE) THEN
5513 -----------------------------------------------------------
5514 -- Process_Row will only process our pending B table row --
5515 -- in the loop when LANGUAGE is NULL or when LANGUAGE = --
5516 -- SOURCE_LANG, so we insert a History row in that loop --
5517 -----------------------------------------------------------
5518 /* BUG 5388684 As the source lang may not be the first record to get processed.
5519 in case its add case for Single row then if the other language record goes first
5520 which is used to insert history record which is wrong.
5521 */
5522 /*IF (l_current_row_language IS NULL OR
5523 l_current_row_language = l_current_row_source_lang) THEN*/
5524 IF L_PREV_EXT_ID <> l_ext_id_for_current_row THEN
5525 l_utility_dynamic_sql := ' INSERT INTO '||p_change_b_table_name||' CT ('||
5526 l_history_b_chng_cols_list||
5527 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
5528 ', CT.EXTENSION_ID,CT.DATA_LEVEL_ID '||
5529 ', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
5530 ', CT.PK4_VALUE, CT.PK5_VALUE )SELECT '||
5531 l_history_b_prod_cols_list||
5532 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
5533 ', PT.EXTENSION_ID, CT.DATA_LEVEL_ID '||
5534 ', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
5535 ', CT.PK4_VALUE, CT.PK5_VALUE FROM '||
5536 p_production_b_table_name||' PT, '||
5537 p_change_b_table_name||
5538 ' CT WHERE PT.EXTENSION_ID = :1'||
5539 ' AND CT.EXTENSION_ID = :2'||
5540 ' AND CT.CHANGE_LINE_ID = :3'||
5541 ' AND CT.ACD_TYPE = :4';
5542
5543 EXECUTE IMMEDIATE l_utility_dynamic_sql
5544 USING l_ext_id_for_current_row, l_current_pending_ext_id,
5545 p_change_line_id, l_current_acd_type;
5546
5547 END IF;
5548
5549 ------------------------------------------------------------
5550 -- Process_Row will only process the pending TL table row --
5551 -- whose language matches LANGUAGE, so we only insert a --
5552 -- History row for that row --
5553 ------------------------------------------------------------
5554 l_utility_dynamic_sql := ' INSERT INTO '||p_change_tl_table_name||' CT ('||
5555 l_history_tl_chng_cols_list||
5556 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
5557 ', CT.EXTENSION_ID,CT.DATA_LEVEL_ID '||
5558 ', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
5559 ', CT.PK4_VALUE, CT.PK5_VALUE ) SELECT '||
5560 l_history_tl_prod_cols_list||
5561 ', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
5562 ', PT.EXTENSION_ID, CT.DATA_LEVEL_ID '||
5563 ', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
5564 ', CT.PK4_VALUE, CT.PK5_VALUE FROM '||
5565 p_production_tl_table_name||' PT, '||
5566 p_change_tl_table_name||
5567 ' CT WHERE PT.EXTENSION_ID = :1'||
5568 ' AND CT.EXTENSION_ID = :2'||
5569 ' AND CT.CHANGE_LINE_ID = :3'||
5570 ' AND CT.ACD_TYPE = :4'||
5571 ' AND CT.LANGUAGE = PT.LANGUAGE AND CT.LANGUAGE = :5';
5572
5573 EXECUTE IMMEDIATE l_utility_dynamic_sql
5574 USING l_ext_id_for_current_row, l_current_pending_ext_id,
5575 p_change_line_id, l_current_acd_type, l_current_row_language;
5576
5577 ELSIF (l_mode_for_current_row = G_IMPLEMENT_CREATE_MODE) THEN -- BUG 5340167
5578 IF L_PREV_EXT_ID <> l_ext_id_for_current_row THEN
5579 l_utility_dynamic_sql := ' INSERT INTO '||p_change_b_table_name||' CT ('||
5580 ' CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
5581 ', CT.EXTENSION_ID,CT.ATTR_GROUP_ID '||
5582 ', CT.ORGANIZATION_ID ,CT.INVENTORY_ITEM_ID '||
5583 ', CT.ITEM_CATALOG_GROUP_ID, CT.REVISION_ID, CT.CREATED_BY '||
5584 ', CT.CREATION_DATE , CT.LAST_UPDATE_DATE ' ||
5585 ', CT.LAST_UPDATE_LOGIN, CT.LAST_UPDATED_BY, CT.DATA_LEVEL_ID '||
5586 ', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
5587 ', CT.PK4_VALUE, CT.PK5_VALUE '||
5588 ' ) SELECT '||
5589 ' CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
5590 ', CT.EXTENSION_ID ,CT.ATTR_GROUP_ID '||
5591 ', CT.ORGANIZATION_ID , CT.INVENTORY_ITEM_ID '||
5592 ', CT.ITEM_CATALOG_GROUP_ID,CT.REVISION_ID, CT.CREATED_BY '||
5593 ', CT.CREATION_DATE , CT.LAST_UPDATE_DATE ' ||
5594 ', CT.LAST_UPDATE_LOGIN, CT.LAST_UPDATED_BY, CT.DATA_LEVEL_ID '||
5595 ', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
5596 ', CT.PK4_VALUE, CT.PK5_VALUE '||
5597 ' FROM '||
5598 p_change_b_table_name || ' CT '||
5599 ' WHERE CT.EXTENSION_ID = :1'||
5600 ' AND CT.CHANGE_LINE_ID = :2'||
5601 ' AND CT.ACD_TYPE = :3';
5602
5603 EXECUTE IMMEDIATE l_utility_dynamic_sql
5604 USING l_current_pending_ext_id,
5605 p_change_line_id, l_current_acd_type;
5606
5607 END IF;
5608
5609 l_utility_dynamic_sql := ' INSERT INTO '|| p_change_tl_table_name||' CT ('||
5610 ' CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
5611 ', CT.EXTENSION_ID, CT.ATTR_GROUP_ID '||
5612 ', CT.ORGANIZATION_ID ,CT.INVENTORY_ITEM_ID '||
5613 ', CT.ITEM_CATALOG_GROUP_ID, CT.REVISION_ID, CT.CREATED_BY '||
5614 ', CT.CREATION_DATE , CT.LAST_UPDATE_DATE ' ||
5615 ', CT.LAST_UPDATE_LOGIN, CT.LAST_UPDATED_BY '||
5616 ', CT.SOURCE_LANG,CT.LANGUAGE,CT.DATA_LEVEL_ID '||
5617 ', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
5618 ', CT.PK4_VALUE, CT.PK5_VALUE ) SELECT '||
5619 ' CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
5620 ', CT.EXTENSION_ID, CT.ATTR_GROUP_ID '||
5621 ', CT.ORGANIZATION_ID , CT.INVENTORY_ITEM_ID '||
5622 ', CT.ITEM_CATALOG_GROUP_ID, CT.REVISION_ID, CT.CREATED_BY '||
5623 ', CT.CREATION_DATE , CT.LAST_UPDATE_DATE ' ||
5624 ', CT.LAST_UPDATE_LOGIN, CT.LAST_UPDATED_BY '||
5625 ', CT.SOURCE_LANG , CT.LANGUAGE , CT.DATA_LEVEL_ID '||
5626 ', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
5627 ', CT.PK4_VALUE, CT.PK5_VALUE '||
5628 ' FROM '||
5629 p_change_tl_table_name || ' CT '||
5630 ' WHERE CT.EXTENSION_ID = :1'||
5631 ' AND CT.CHANGE_LINE_ID = :2'||
5632 ' AND CT.ACD_TYPE = :3'||
5633 ' AND CT.LANGUAGE = :4';
5634
5635 EXECUTE IMMEDIATE l_utility_dynamic_sql
5636 USING l_ext_id_for_current_row, p_change_line_id, l_current_acd_type, l_current_row_language;
5637 -- BUG 5340167
5638 END IF;
5639
5640 Write_Debug('After Calling ENG_CHANGE_ATTR_UTIL.SETUP_IMPL_ATTR_DATA_ROW: Return Status: ' || x_return_status );
5641
5642 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
5643 THEN
5644
5645 Write_Debug('Raising error after ENG_CHANGE_ATTR_UTIL ' );
5646
5647 RAISE FND_API.G_EXC_ERROR;
5648 END IF;
5649
5650
5651 Write_Debug('Calling EGO_USER_ATTRS_DATA_PVT.Process_Row ' );
5652 if l_mode_for_current_row = G_IMPLEMENT_CREATE_MODE AND L_PREV_EXT_ID = l_ext_id_for_current_row
5653 THEN
5654 l_mode_for_current_row := G_UPDATE_TX_TYPE;
5655 END IF;
5656 Write_Debug('ATTR_GROUP_ID ' || to_char( l_attr_group_metadata_obj.ATTR_GROUP_ID));
5657 Write_Debug('ATTR_GROUP_TYPE ' || l_attr_group_metadata_obj.ATTR_GROUP_TYPE );
5658 Write_Debug('ATTR_GROUP_NAME ' || l_attr_group_metadata_obj.ATTR_GROUP_NAME );
5659 Write_Debug('l_data_level_name '|| l_data_level_name );
5660 Write_Debug('Transaction Mode '|| l_mode_for_current_row );
5661 Write_Debug('Extension ID '|| to_char(l_ext_id_for_current_row ));
5662
5663 ---------------------------------------------------------------------
5664 -- Now at last we're ready to call Process_Row on this pending row --
5665 ---------------------------------------------------------------------
5666 for i in l_impl_attr_name_value_pairs.first .. l_impl_attr_name_value_pairs.last
5667 loop
5668 Write_Debug('Name ' || to_char( l_impl_attr_name_value_pairs(i).ATTR_NAME));
5669 Write_Debug('value ' || to_char( l_impl_attr_name_value_pairs(i).ATTR_VALUE_STR));
5670 end loop;
5671 EGO_USER_ATTRS_DATA_PVT.Process_Row(
5672 p_api_version => 1.0
5673 ,p_object_name => p_object_name
5674 ,p_attr_group_id => l_attr_group_metadata_obj.ATTR_GROUP_ID
5675 ,p_application_id => l_attr_group_metadata_obj.APPLICATION_ID
5676 ,p_attr_group_type => l_attr_group_metadata_obj.ATTR_GROUP_TYPE
5677 ,p_attr_group_name => l_attr_group_metadata_obj.ATTR_GROUP_NAME
5678 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
5679 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
5680 ,p_data_level => l_data_level_name
5681 ,p_data_level_name_value_pairs => l_current_dl_name_value_pairs
5682 ,p_extension_id => l_ext_id_for_current_row
5683 ,p_attr_name_value_pairs => l_impl_attr_name_value_pairs
5684 ,p_language_to_process => l_current_row_language
5685 ,p_mode => l_mode_for_current_row
5686 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
5687 ,x_return_status => x_return_status
5688 ,x_errorcode => x_errorcode
5689 ,x_msg_count => x_msg_count
5690 ,x_msg_data => x_msg_data
5691 );
5692
5693 Write_Debug('After Calling EGO_USER_ATTRS_DATA_PVT.Process_Row: Return Status: ' || x_return_status );
5694 L_PREV_EXT_ID := l_ext_id_for_current_row;
5695
5696 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5697 RAISE FND_API.G_EXC_ERROR;
5698 END IF;
5699 END IF;
5700 END LOOP;
5701 DBMS_SQL.Close_Cursor(l_cursor_id);
5702
5703 ---------------------------------------------------------------------------
5704 -- Finally, set the IMPLEMENTATION_DATE for all rows we just implemented --
5705 ---------------------------------------------------------------------------
5706 EXECUTE IMMEDIATE ' UPDATE '||p_change_b_table_name||
5707 ' SET IMPLEMENTATION_DATE = :1'||
5708 ' WHERE CHANGE_LINE_ID = :2'
5709 USING SYSDATE, p_change_line_id;
5710 EXECUTE IMMEDIATE ' UPDATE '||p_change_tl_table_name||
5711 ' SET IMPLEMENTATION_DATE = :1'||
5712 ' WHERE CHANGE_LINE_ID = :2'
5713 USING SYSDATE, p_change_line_id;
5714
5715 Write_Debug('In Implement_Change_Line, done');
5716
5717 -----------------------------------
5718
5719 -- Standard check of p_commit
5720 IF FND_API.To_Boolean(p_commit) THEN
5721 Write_Debug('COMMIT Implement_Change_Line');
5722 COMMIT WORK;
5723 END IF;
5724
5725 x_return_status := FND_API.G_RET_STS_SUCCESS;
5726
5727 EXCEPTION
5728 WHEN FND_API.G_EXC_ERROR THEN
5729
5730 Write_Debug('When G_EXC_ERROR Exception in Implement_Change_Line');
5731
5732 x_return_status := FND_API.G_RET_STS_ERROR;
5733
5734 IF FND_API.To_Boolean( p_commit ) THEN
5735 Write_Debug('ROLLBACK TO Implement_Change_Line');
5736 ROLLBACK TO Implement_Change_Line_PUB;
5737 END IF;
5738
5739 -----------------------------------------------------------------
5740 -- If Process_Row didn't return any errors, make one ourselves --
5741 -----------------------------------------------------------------
5742 IF (x_msg_data IS NULL AND x_msg_count = 0) THEN
5743 ERROR_HANDLER.Add_Error_Message(
5744 p_message_name => 'EGO_EF_IMPLEMENT_ERR'
5745 ,p_application_id => 'EGO'
5746 ,p_message_type => FND_API.G_RET_STS_ERROR
5747 ,p_addto_fnd_stack => 'Y'
5748 );
5749 END IF;
5750
5751 -------------------------------------------------------------------
5752 -- If Process_Row had more than one error, return the first one --
5753 -- (or else return the one we just added to ERROR_HANDLER above) --
5754 -------------------------------------------------------------------
5755 IF (x_msg_data IS NULL AND x_msg_count > 0) THEN
5756 DECLARE
5757 message_list ERROR_HANDLER.Error_Tbl_Type;
5758 BEGIN
5759 ERROR_HANDLER.Get_Message_List(message_list);
5760 x_msg_data := message_list(message_list.FIRST).message_text;
5761 END;
5762 END IF;
5763
5764 Write_Debug('In Implement_Change_Line, got expected error: x_msg_data is '||x_msg_data);
5765
5766 WHEN OTHERS THEN
5767 Write_Debug('When G_RET_STS_UNEXP_ERROR Exception in Implement_Change_Line');
5768
5769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5770
5771 IF FND_API.To_Boolean( p_commit ) THEN
5772 Write_Debug('ROLLBACK TO Implement_Change_Line');
5773 ROLLBACK TO Implement_Change_Line_PUB;
5774 END IF;
5775
5776
5777 DECLARE
5778 l_dummy_entity_index NUMBER;
5779 l_dummy_entity_id VARCHAR2(60);
5780 l_dummy_message_type VARCHAR2(1);
5781 BEGIN
5782 l_token_table(1).TOKEN_NAME := 'PKG_NAME';
5783 l_token_table(1).TOKEN_VALUE := G_PKG_NAME;
5784 l_token_table(2).TOKEN_NAME := 'API_NAME';
5785 l_token_table(2).TOKEN_VALUE := l_api_name;
5786 l_token_table(3).TOKEN_NAME := 'SQL_ERR_MSG';
5787 l_token_table(3).TOKEN_VALUE := SQLERRM;
5788
5789 ERROR_HANDLER.Add_Error_Message(
5790 p_message_name => 'EGO_PLSQL_ERR'
5791 ,p_application_id => 'EGO'
5792 ,p_token_tbl => l_token_table
5793 ,p_message_type => FND_API.G_RET_STS_ERROR
5794 ,p_addto_fnd_stack => 'Y'
5795 );
5796
5797 ERROR_HANDLER.Get_Message(x_message_text => x_msg_data
5798 ,x_entity_index => l_dummy_entity_index
5799 ,x_entity_id => l_dummy_entity_id
5800 ,x_message_type => l_dummy_message_type);
5801 END;
5802
5803 Write_Debug('In Implement_Change_Line, got unexpected error: x_msg_data is '||x_msg_data);
5804
5805 END Implement_Change_Line;
5806
5807
5808 PROCEDURE impl_rev_item_user_attr_chgs
5809 ( p_api_version IN NUMBER
5810 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
5811 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
5812 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
5813 , x_return_status OUT NOCOPY VARCHAR2
5814 , x_msg_count OUT NOCOPY NUMBER
5815 , x_msg_data OUT NOCOPY VARCHAR2
5816 , p_api_caller IN VARCHAR2 := NULL
5817 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
5818 , p_output_dir IN VARCHAR2 := NULL
5819 , p_debug_filename IN VARCHAR2 := NULL
5820 , p_change_id IN NUMBER := NULL
5821 , p_change_line_id IN NUMBER
5822 )
5823 IS
5824 l_api_name CONSTANT VARCHAR2(30) := 'IMPL_REV_ITEM_USER_ATTR_CHGS';
5825 l_api_version CONSTANT NUMBER := 1.0;
5826
5827 l_init_msg_list VARCHAR2(1) ;
5828 l_validation_level NUMBER ;
5829 l_commit VARCHAR2(1) ;
5830
5831 l_msg_data VARCHAR2(4000);
5832 l_msg_count NUMBER;
5833 l_return_status VARCHAR2(1);
5834 l_error_msg VARCHAR2(2000) ;
5835
5836 l_found BOOLEAN ;
5837
5838
5839 l_check_item_attr_change NUMBER := 2;
5840 l_new_revision_id NUMBER;
5841 l_old_revision_id NUMBER;
5842
5843 l_errorcode NUMBER;
5844 l_change_id NUMBER;
5845 l_change_line_id NUMBER;
5846
5847 plsql_block VARCHAR2(5000);
5848
5849
5850 l_old_data_level_nv_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
5851 l_new_data_level_nv_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
5852
5853 BEGIN
5854
5855
5856 -- Standard call to check for call compatibility.
5857 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
5858 p_api_version ,
5859 l_api_name ,
5860 G_PKG_NAME )
5861 THEN
5862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5863 END IF;
5864
5865 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
5866 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
5867 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
5868
5869
5870 -- Standard check of p_commit.
5871 IF FND_API.To_Boolean( l_commit ) THEN
5872 -- Standard Start of API savepoint
5873 SAVEPOINT IMPL_REV_ITEM_USER_ATTR_CHGS;
5874 END IF;
5875
5876 -- Initialize message list if p_init_msg_list is set to TRUE.
5877 IF FND_API.to_Boolean( l_init_msg_list ) THEN
5878 FND_MSG_PUB.initialize;
5879 END IF;
5880
5881 -- Open Debug Session by a give param or profile option.
5882 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
5883
5884 Write_Debug('After Open_Debug_Session');
5885 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
5886 Write_Debug('-----------------------------------------' );
5887 Write_Debug('p_api_version: ' || to_char(p_api_version));
5888 Write_Debug('p_init_msg_list:' || p_init_msg_list);
5889 Write_Debug('p_commit:' || p_commit);
5890 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
5891 Write_Debug('p_api_caller:' || p_api_caller);
5892 Write_Debug('p_change_id: ' || to_char(p_change_id));
5893 Write_Debug('p_change_line_id: ' || to_char(p_change_line_id));
5894 Write_Debug('-----------------------------------------' );
5895
5896 -- Initialize API return status to success
5897 x_return_status := G_RET_STS_SUCCESS;
5898
5899 -- API body
5900 -- Logic Here
5901 -- Init Local Vars
5902 l_change_id := p_change_id;
5903 l_change_line_id := p_change_line_id;
5904
5905 IF ( l_change_id IS NULL OR l_change_id <= 0 )
5906 THEN
5907 l_change_id := GetChangeId(p_change_line_id => l_change_line_id) ;
5908
5909 Write_Debug('Got Change Id: ' || to_char(l_change_id));
5910
5911 END IF ;
5912
5913
5914 Write_Debug('Check Item User Attr Change exists for Rev Item: ' || to_char(l_change_line_id));
5915 l_found := CheckItemUserAttrChange(p_change_line_id => l_change_line_id) ;
5916 IF NOT l_found THEN
5917 Write_Debug('Item User Attr Change not found for ' || to_char(l_change_line_id));
5918 RETURN ;
5919 END IF ;
5920
5921
5922 IF (l_found ) THEN
5923 BEGIN
5924 --
5925 -- if new revision is created for this revised Item
5926 --
5927 select new_item_revision_id, current_item_revision_id
5928 into l_new_revision_id, l_old_revision_id
5929 from eng_revised_items
5930 where revised_item_sequence_id = p_change_line_id;
5931
5932 EXCEPTION WHEN others THEN
5933 null;
5934 END;
5935 END IF;
5936
5937
5938 --
5939 -- Call item user attribute changes implement API
5940 --
5941
5942
5943 -- we should pass revised_item_sequence_id as line_id and l_revision_id as
5944 -- the new revision_id
5945
5946 l_change_id := p_change_id;
5947 l_change_line_id := p_change_line_id;
5948
5949
5950 Write_Debug('Before calling EGO_USER_ATTRS_DATA_PUB.Implement_Change_Line for ' || to_char(l_change_line_id));
5951
5952
5953
5954
5955 ---------------------------------------------------------------------
5956 -- Build data structures to pass in Data Level info, if applicable --
5957 ---------------------------------------------------------------------
5958 IF (l_old_revision_id IS NOT NULL) THEN
5959 l_old_data_level_nv_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
5960 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID',
5961 l_old_revision_id)
5962 );
5963 END IF;
5964
5965 IF (l_new_revision_id IS NOT NULL) THEN
5966 l_new_data_level_nv_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
5967 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID',
5968 l_new_revision_id)
5969 );
5970 END IF;
5971
5972
5973 BEGIN
5974
5975 -- In R12, moved EGO_USER_ATTRS_DATA_PVT.Implement_Change_Line to
5976 -- this package
5977 -------------------------------------------------------------------------
5978 -- Now we invoke the UserAttrs procedure, passing Item-specific params --
5979 -------------------------------------------------------------------------
5980 Implement_Change_Line(
5981 p_api_version => 1.0
5982 ,p_object_name => 'EGO_ITEM'
5983 ,p_production_b_table_name => 'EGO_MTL_SY_ITEMS_EXT_B'
5984 ,p_production_tl_table_name => 'EGO_MTL_SY_ITEMS_EXT_TL'
5985 ,p_change_b_table_name => 'EGO_ITEMS_ATTRS_CHANGES_B'
5986 ,p_change_tl_table_name => 'EGO_ITEMS_ATTRS_CHANGES_TL'
5987 ,p_tables_application_id => 431
5988 ,p_change_line_id => l_change_line_id
5989 ,p_old_data_level_nv_pairs => l_old_data_level_nv_pairs
5990 ,p_new_data_level_nv_pairs => l_new_data_level_nv_pairs
5991 ,p_related_class_code_function => 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Related_Class_Codes'
5992 ,p_init_msg_list => FND_API.G_FALSE
5993 ,p_commit => FND_API.G_FALSE
5994 ,x_return_status => l_return_status
5995 ,x_errorcode => l_errorcode
5996 ,x_msg_count => l_msg_count
5997 ,x_msg_data => l_msg_data
5998 ) ;
5999
6000 EXCEPTION
6001 WHEN OTHERS THEN
6002
6003 FND_MSG_PUB.Add_Exc_Msg
6004 ( p_pkg_name => 'EGO_USER_ATTRS_DATA_PVT' ,
6005 p_procedure_name => 'Implement_Change_Line',
6006 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
6007 );
6008
6009
6010 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
6011 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_USER_ATTRS_DATA_PVT.Implement_Change_Line');
6012 FND_MSG_PUB.Add;
6013
6014 Write_Debug('When Others Exception while calling EGO_USER_ATTRS_DATA_PVT.Implement_Change_Line:' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
6015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6016 END ;
6017
6018 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
6019 THEN
6020
6021 Write_Debug('Implement_Change_Line failed . .. ' );
6022 Write_Debug('Output - Return Stattus: ' || l_return_status);
6023 Write_Debug('Output - Return Stattus: ' || to_char(l_msg_count));
6024 Write_Debug('Output - Return Stattus: ' || substr(l_msg_data,1,200));
6025
6026
6027 x_return_status := l_return_status;
6028 x_msg_count := l_msg_count;
6029 x_msg_data := l_msg_data;
6030 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
6031 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_USER_ATTRS_DATA_PVT.Implement_Change_Line');
6032 FND_MSG_PUB.Add;
6033
6034 RAISE FND_API.G_EXC_ERROR ;
6035 END IF;
6036
6037
6038 -- End of API body.
6039
6040
6041 -- Standard check of p_commit.
6042 IF FND_API.To_Boolean( l_commit ) THEN
6043 COMMIT WORK;
6044 END IF;
6045
6046 -- Standard call to get message count and if count is 1, get message info.
6047 FND_MSG_PUB.Count_And_Get
6048 ( p_count => x_msg_count ,
6049 p_data => x_msg_data
6050 );
6051
6052
6053 -----------------------------------------------------
6054 -- Close Error Handler Debug Session.
6055 -----------------------------------------------------
6056 -- Close debug session only explicitly open the debug session for
6057 -- this API.
6058 IF FND_API.to_Boolean(p_debug)
6059 THEN
6060 Close_Debug_Session;
6061 END IF ;
6062
6063
6064 EXCEPTION
6065 WHEN FND_API.G_EXC_ERROR THEN
6066 Write_Debug('When G_EXC_ERROR Exception in impl_rev_item_user_attr_chgs');
6067 x_return_status := G_RET_STS_ERROR ;
6068
6069 IF FND_API.To_Boolean( l_commit ) THEN
6070 Write_Debug('ROLLBACK Item User Attribute Change Implementation TO IMPL_REV_ITEM_USER_ATTR_CHGS');
6071 ROLLBACK TO IMPL_REV_ITEM_USER_ATTR_CHGS;
6072 END IF;
6073
6074 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
6075 FND_MESSAGE.Set_Token('OBJECT_NAME', 'impl_rev_item_attr_changes'|| 'error code '||l_errorcode);
6076 FND_MSG_PUB.Add;
6077
6078
6079 FND_MSG_PUB.Count_And_Get
6080 ( p_count => x_msg_count ,
6081 p_data => x_msg_data
6082 );
6083
6084 -----------------------------------------------------
6085 -- Close Error Handler Debug Session.
6086 -----------------------------------------------------
6087 -- Close debug session only explicitly open the debug session for
6088 -- this API.
6089 IF FND_API.to_Boolean(p_debug)
6090 THEN
6091 Close_Debug_Session;
6092 END IF ;
6093
6094 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6095
6096 Write_Debug('When G_EXC_UNEXPECTED_ERROR Exception in impl_rev_item_user_attr_chgs');
6097 x_return_status := G_RET_STS_UNEXP_ERROR ;
6098
6099 IF FND_API.To_Boolean( l_commit ) THEN
6100 Write_Debug('ROLLBACK Item User Attribute Change Implementation TO IMPL_REV_ITEM_USER_ATTR_CHGS');
6101 ROLLBACK TO IMPL_REV_ITEM_USER_ATTR_CHGS;
6102 END IF;
6103
6104
6105 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
6106 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_attr_changes for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
6107 FND_MSG_PUB.Add;
6108
6109
6110 FND_MSG_PUB.Count_And_Get
6111 ( p_count => x_msg_count ,
6112 p_data => x_msg_data
6113 );
6114
6115 -----------------------------------------------------
6116 -- Close Error Handler Debug Session.
6117 -----------------------------------------------------
6118 -- Close debug session only explicitly open the debug session for
6119 -- this API.
6120 IF FND_API.to_Boolean(p_debug)
6121 THEN
6122 Close_Debug_Session;
6123 END IF ;
6124
6125 WHEN OTHERS THEN
6126
6127 Write_Debug('When Others Exception in impl_rev_item_user_attr_chgs');
6128 x_return_status := G_RET_STS_UNEXP_ERROR ;
6129
6130 IF FND_API.To_Boolean( l_commit ) THEN
6131 Write_Debug('ROLLBACK Item User Attribute Change Implementation TO IMPL_REV_ITEM_USER_ATTR_CHGS');
6132 ROLLBACK TO IMPL_REV_ITEM_USER_ATTR_CHGS;
6133 END IF;
6134
6135
6136
6137 Write_Debug('When Others Exception ' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
6138
6139 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_PS_API');
6140 FND_MESSAGE.Set_Token('OBJECT_NAME', 'ENG_IMPL_ITEM_CHANGES_PKG.impl_rev_item_attr_changes for ChangeId: '||l_change_id || '- ChangeLineId: '||l_change_line_id);
6141 FND_MSG_PUB.Add;
6142
6143
6144 IF FND_MSG_PUB.Check_Msg_Level
6145 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6146 THEN
6147 FND_MSG_PUB.Add_Exc_Msg
6148 ( G_PKG_NAME ,
6149 l_api_name
6150 );
6151 END IF;
6152
6153 FND_MSG_PUB.Count_And_Get
6154 ( p_count => x_msg_count ,
6155 p_data => x_msg_data
6156 );
6157
6158 -----------------------------------------------------
6159 -- Close Error Handler Debug Session.
6160 -----------------------------------------------------
6161 -- Close debug session only explicitly open the debug session for
6162 -- this API.
6163 IF FND_API.to_Boolean(p_debug)
6164 THEN
6165 Close_Debug_Session;
6166 END IF ;
6167
6168 END impl_rev_item_user_attr_chgs ;
6169
6170
6171
6172 END ENG_IMPL_ITEM_CHANGES_PKG;