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