[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_IMPORT_UTIL
Source
1 package body ENG_CHANGE_IMPORT_UTIL as
2 /*$Header: ENGUCMIB.pls 120.51 2012/03/09 03:12:20 lanhuang ship $ */
3
4 --------------------------------------------
5 -- This is Database Session Language. --
6 --------------------------------------------
7 G_SESSION_LANG CONSTANT VARCHAR2(99) := USERENV('LANG');
8
9 --------------------------------------------
10 -- This is the UI language. --
11 --------------------------------------------
12 G_LANGUAGE_CODE VARCHAR2(3);
13
14 ----------------------------------------------------------------------------
15 -- Debug Profile option used to write Error_Handler.Write_Debug --
16 -- Profile option name = INV_DEBUG_TRACE ; --
17 -- User Profile Option Name = INV: Debug Trace --
18 -- Values: 1 (True) ; 0 (False) --
19 -- NOTE: This better than MRP_DEBUG which is used at many places. --
20 ----------------------------------------------------------------------------
21 G_DEBUG CONSTANT VARCHAR2(10) := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
22
23 -----------------------------------------------------------------------
24 -- These are the Constants to generate a New Line Character. --
25 -----------------------------------------------------------------------
26 G_CARRIAGE_RETURN VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(13);
27 G_LINE_FEED VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
28 -- Following prints ^M characters in the log file.
29 G_NEWLINE VARCHAR2(2) := G_LINE_FEED;
30
31
32 ---------------------------------------------------------------
33 -- API Return Status . --
34 ---------------------------------------------------------------
35 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
36 G_RET_STS_WARNING CONSTANT VARCHAR2(1) := 'W';
37 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
38 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
39
40 ---------------------------------------------------------------
41 -- Used for Error Reporting. --
42 ---------------------------------------------------------------
43 G_ERROR_TABLE_NAME VARCHAR2(30) ;
44 G_ERROR_ENTITY_CODE VARCHAR2(30) := 'EGO_ITEM';
45 G_OUTPUT_DIR VARCHAR2(512) ;
46 G_ERROR_FILE_NAME VARCHAR2(400) ;
47 G_BO_IDENTIFIER VARCHAR2(30) := 'ENG_CHANGE';
48
49
50 ---------------------------------------------------------------
51 -- Introduced for 11.5.10, so that Java Conc Program can --
52 -- continue writing to the same Error Log File. --
53 ---------------------------------------------------------------
54 G_ERRFILE_PATH_AND_NAME VARCHAR2(10000);
55
56
57 ---------------------------------------------------------------
58 -- Message Type Text . --
59 ---------------------------------------------------------------
60 G_FND_MSG_TYPE_CONFIRMATION VARCHAR2(100) ;
61 G_FND_MSG_TYPE_ERROR VARCHAR2(100) ;
62 G_FND_MSG_TYPE_WARNING VARCHAR2(100) ;
63 G_FND_MSG_TYPE_INFORMATION VARCHAR2(100) ;
64
65 ---------------------------------------------------------------
66 -- Message Type Text . --
67 ---------------------------------------------------------------
68 G_ENG_MSG_TYPE_ERROR CONSTANT VARCHAR2(1) := Error_Handler.G_STATUS_ERROR ;
69 G_ENG_MSG_TYPE_WARNING CONSTANT VARCHAR2(1) := Error_Handler.G_STATUS_WARNING ;
70 G_ENG_MSG_TYPE_UNEXPECTED CONSTANT VARCHAR2(1) := Error_Handler.G_STATUS_UNEXPECTED ;
71 G_ENG_MSG_TYPE_FATAL CONSTANT VARCHAR2(1) := Error_Handler.G_STATUS_FATAL ;
72 G_ENG_MSG_TYPE_CONFIRMATION CONSTANT VARCHAR2(1) := 'C';
73 G_ENG_MSG_TYPE_INFORMATION CONSTANT VARCHAR2(1) := 'I' ;
74
75
76 ---------------------------------------------------------------
77 -- Special Miss Values . --
78 ---------------------------------------------------------------
79 G_MISS_NUM NUMBER := EGO_ITEM_PUB.G_INTF_NULL_NUM;
80 G_MISS_CHAR VARCHAR2(1) := EGO_ITEM_PUB.G_INTF_NULL_CHAR;
81 G_MISS_DATE DATE := EGO_ITEM_PUB.G_INTF_NULL_DATE;
82
83 G_ENTITY_CODE VARCHAR2(30);
84 G_ENTITY_ID NUMBER;
85 G_APPLICATION_CONTEXT VARCHAR2(30);
86
87 -----------------------------------------------------------------
88 -- Write Debug statements to Log using Error Handler procedure --
89 -----------------------------------------------------------------
90 PROCEDURE Write_Debug (p_msg IN VARCHAR2) IS
91
92 BEGIN
93
94 -- NOTE: No need to check for profile now, as Error_Handler checks
95 -- for Error_Handler.Get_Debug = 'Y' before writing to Debug Log.
96 -- If Profile set to TRUE --
97 -- IF (G_DEBUG = 1) THEN
98 -- Error_Handler.Write_Debug('['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] '|| p_msg);
99 -- END IF;
100
101 Error_Handler.Write_Debug('['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] '|| p_msg);
102
103
104 /*------------------------------------------------------------------
105 -- Comment Out
106 -- BEGIN
107 -- FND Standard Log
108 -- FND_LOG.LEVEL_UNEXPECTED;
109 -- FND_LOG.LEVEL_ERROR;
110 -- FND_LOG.LEVEL_EXCEPTION;
111 -- FND_LOG.LEVEL_EVENT;
112 -- FND_LOG.LEVEL_PROCEDURE;
113 -- FND_LOG.LEVEL_STATEMENT;
114 -- G_DEBUG_LOG_HEAD := 'fnd.plsql.'||G_PKG_NAME||'.';
115
116 -- IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
117 -- fnd_log.string(log_level => p_log_level
118 -- ,module => G_DEBUG_LOG_HEAD||p_module
119 -- ,message => p_message
120 -- );
121 -- END IF;
122 -- NULL;
123 -- EXCEPTION
124 -- WHEN OTHERS THEN
125 -- RAISE;
126 -- END log_now;
127 ------------------------------------------------------------------*/
128
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 FND_FILE.put_line(FND_FILE.LOG, 'Write_Debug LOGGING ERROR => '|| SUBSTRB(SQLERRM, 1,240) );
133
134 END Write_Debug ;
135
136 ----------------------------------------------------------
137 -- Write to Concurrent Log --
138 ----------------------------------------------------------
139
140 PROCEDURE Developer_Debug (p_msg IN VARCHAR2) IS
141 BEGIN
142
143 FND_FILE.put_line(FND_FILE.LOG, p_msg);
144
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 FND_FILE.put_line(FND_FILE.LOG, 'Developer_Debug LOGGING SQL ERROR => '|| SUBSTRB(SQLERRM, 1,240));
149
150 END Developer_Debug ;
151
152 ----------------------------------------------------------
153 -- Internal procedure to open Debug Session. --
154 ----------------------------------------------------------
155 PROCEDURE open_debug_session_internal IS
156
157 CURSOR c_get_utl_file_dir IS
158 SELECT VALUE
159 FROM V$PARAMETER
160 WHERE NAME = 'utl_file_dir';
161
162 --local variables
163 -- Bug 13074883 swuppala : Modified the datatype with the database column datatype
164 l_log_output_dir v$parameter.value%type;
165 l_log_return_status VARCHAR2(99);
166 l_errbuff VARCHAR2(999);
167 BEGIN
168
169 Error_Handler.initialize();
170 Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
171
172 ---------------------------------------------------------------------------------
173 -- Commented on 12/17/2003 (PPEDDAMA). Open_Debug_Session should set the value
174 -- appropriately, so that when the Debug Session is successfully opened :
175 -- will return Error_Handler.Get_Debug = 'Y', else Error_Handler.Get_Debug = 'N'
176 ---------------------------------------------------------------------------------
177 -- Error_Handler.Set_Debug('Y');
178
179 OPEN c_get_utl_file_dir;
180 FETCH c_get_utl_file_dir INTO l_log_output_dir;
181 --developer_debug('UTL_FILE_DIR : '||l_log_output_dir);
182 IF c_get_utl_file_dir%FOUND THEN
183 ------------------------------------------------------
184 -- Trim to get only the first directory in the list --
185 ------------------------------------------------------
186 IF INSTR(l_log_output_dir,',') <> 0 THEN
187 l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
188 --developer_debug('Log Output Dir : '||l_log_output_dir);
189 END IF;
190
191
192 IF G_OUTPUT_DIR IS NOT NULL
193 THEN
194 l_log_output_dir := G_OUTPUT_DIR ;
195 END IF ;
196
197
198
199 IF G_ERROR_FILE_NAME IS NULL
200 THEN
201 G_ERROR_FILE_NAME := G_BO_IDENTIFIER ||'_'
202 -- || G_ERROR_TABLE_NAME||'_'
203 || to_char(sysdate, 'DDMONYYYY_HH24MISS')||'.err';
204 END IF ;
205
206 --developer_debug('Trying to open the Error File => '||G_ERROR_FILE_NAME);
207
208 -----------------------------------------------------------------------
209 -- To open the Debug Session to write the Debug Log. --
210 -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --
211 -----------------------------------------------------------------------
212 Error_Handler.Open_Debug_Session(
213 p_debug_filename => G_ERROR_FILE_NAME
214 ,p_output_dir => l_log_output_dir
215 ,x_return_status => l_log_return_status
216 ,x_error_mesg => l_errbuff
217 );
218
219 ---------------------------------------------------------------
220 -- Introduced for 11.5.10, so that Java Conc Program can --
221 -- continue writing to the same Error Log File. --
222 ---------------------------------------------------------------
223 G_ERRFILE_PATH_AND_NAME := l_log_output_dir||'/'||G_ERROR_FILE_NAME;
224
225 developer_debug(' Log file location --> '||l_log_output_dir||'/'||G_ERROR_FILE_NAME ||' created with status '|| l_log_return_status);
226
227 IF (l_log_return_status <> G_RET_STS_SUCCESS) THEN
228 developer_debug('Unable to open error log file. Error => '||l_errbuff);
229 END IF;
230
231 END IF;--IF c_get_utl_file_dir%FOUND THEN
232 -- Bug : 4099546
233 CLOSE c_get_utl_file_dir;
234
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 FND_FILE.put_line(FND_FILE.LOG, 'open_debug_session_internal LOGGING SQL ERROR => ' || SUBSTRB(SQLERRM, 1,240));
239
240 END open_debug_session_internal;
241
242
243 -----------------------------------------------------------
244 -- Open the Debug Session, conditionally if the profile: --
245 -- INV Debug Trace is set to TRUE --
246 -----------------------------------------------------------
247 PROCEDURE Open_Debug_Session
248 ( p_debug_flag IN VARCHAR2 := NULL
249 , p_output_dir IN VARCHAR2 := NULL
250 , p_file_name IN VARCHAR2 := NULL
251 )
252 IS
253
254 BEGIN
255 ----------------------------------------------------------------
256 -- Open the Debug Log Session, only if Profile is set to TRUE --
257 ----------------------------------------------------------------
258 IF (G_DEBUG = 1 OR FND_API.to_Boolean(p_debug_flag)) THEN
259
260
261 G_OUTPUT_DIR := p_output_dir ;
262 G_ERROR_FILE_NAME := p_file_name ;
263 ----------------------------------------------------------------------------------
264 -- Opens Error_Handler debug session, only if Debug session is not already open.
265 -- Suggested by RFAROOK, so that multiple debug sessions are not open PER
266 -- Concurrent Request.
267 ----------------------------------------------------------------------------------
268 IF (Error_Handler.Get_Debug <> 'Y') THEN
269 Open_Debug_Session_Internal;
270 END IF;
271
272 END IF;
273
274 EXCEPTION
275 WHEN OTHERS THEN
276 FND_FILE.put_line(FND_FILE.LOG, 'Open_Debug_Session LOGGING SQL ERROR => ' || SUBSTRB(SQLERRM, 1,240) );
277
278 END Open_Debug_Session;
279
280 -----------------------------------------------------------------
281 -- Close the Debug Session, only if Debug is already Turned ON --
282 -----------------------------------------------------------------
283 PROCEDURE Close_Debug_Session IS
284
285 BEGIN
286 -----------------------------------------------------------------------------
287 -- Close Error_Handler debug session, only if Debug session is already open.
288 -----------------------------------------------------------------------------
289 IF (Error_Handler.Get_Debug = 'Y') THEN
290 Error_Handler.Close_Debug_Session;
291 END IF;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 FND_FILE.put_line(FND_FILE.LOG, 'Close_Debug_Session LOGGING SQL ERROR => ' || SUBSTRB(SQLERRM, 1,240) );
296
297 END Close_Debug_Session;
298
299 -----------------------------------------------------------------
300 -- Replace all Single Quote to TWO Single Quotes, for Escaping --
301 -- NOTE: Used while inserting Strings using Dynamic SQL. --
302 -----------------------------------------------------------------
303 FUNCTION Escape_Single_Quote (p_String IN VARCHAR2)
304 RETURN VARCHAR2 IS
305
306 BEGIN
307
308 IF (p_String IS NOT NULL) THEN
309 ---------------------------------------------------
310 -- Replace all Single Quotes to 2 Single Quotes --
311 ---------------------------------------------------
312 RETURN REPLACE(p_String, '''', '''''');
313
314 ELSE
315 ----------------------------------------------
316 -- Return NULL, if the String is NULL or '' --
317 ----------------------------------------------
318 RETURN NULL;
319 END IF;
320
321 END Escape_Single_Quote;
322
323
324 -----------------------------------------------------------------
325 -- Get Message Type Meaning Text from Message Type --
326 -----------------------------------------------------------------
327 FUNCTION Get_Msg_Type_Text(p_msg_type IN VARCHAR2 )
328 RETURN VARCHAR2
329 IS
330 l_msg_type_text VARCHAR2(100) ;
331 BEGIN
332
333 IF p_msg_type = ERROR
334 THEN
335 l_msg_type_text := G_ENG_MSG_TYPE_ERROR ;
336
337 ELSIF p_msg_type = SEVERE
338 THEN
339
340 l_msg_type_text := G_ENG_MSG_TYPE_UNEXPECTED ;
341
342 ELSIF p_msg_type = WARNING
343 THEN
344
345 l_msg_type_text := G_ENG_MSG_TYPE_WARNING ;
346
347 ELSIF p_msg_type = INFORMATION
348 THEN
349
350 l_msg_type_text := G_ENG_MSG_TYPE_INFORMATION ;
351
352 ELSIF p_msg_type = CONFIRMATION
353 THEN
354
355 l_msg_type_text := G_ENG_MSG_TYPE_CONFIRMATION ;
356
357 ELSE
358 l_msg_type_text := p_msg_type ;
359
360 END IF ;
361
362
363 /***************************************************
364 -- In R12, MESSAGE_TYPE in MTL_INTERFACE_ERRORS Table
365 -- is VARCHAR2(1), so following does not work
366 IF p_msg_type = ERROR OR p_msg_type = SEVERE
367 THEN
368
369 IF G_FND_MSG_TYPE_ERROR IS NULL
370 THEN
371 FND_MESSAGE.SET_NAME('FND','FND_MESSAGE_TYPE_ERROR');
372 G_FND_MSG_TYPE_ERROR := FND_MESSAGE.GET;
373 END IF ;
374
375 l_msg_type_text := G_FND_MSG_TYPE_ERROR ;
376
377 ELSIF p_msg_type = WARNING
378 THEN
379 IF G_FND_MSG_TYPE_WARNING IS NULL
380 THEN
381 FND_MESSAGE.SET_NAME('FND','FND_MESSAGE_TYPE_WARNING');
382 G_FND_MSG_TYPE_WARNING := FND_MESSAGE.GET;
383 END IF ;
384
385 l_msg_type_text := G_FND_MSG_TYPE_WARNING ;
386
387
388 ELSIF p_msg_type = INFORMATION
389 THEN
390 IF G_FND_MSG_TYPE_INFORMATION IS NULL
391 THEN
392 FND_MESSAGE.SET_NAME('FND','FND_MESSAGE_TYPE_INFORMATION');
393 G_FND_MSG_TYPE_INFORMATION := FND_MESSAGE.GET;
394 END IF ;
395
396 l_msg_type_text := G_FND_MSG_TYPE_INFORMATION ;
397
398 ELSIF p_msg_type = CONFIRMATION
399 THEN
400 IF G_FND_MSG_TYPE_CONFIRMATION IS NULL
401 THEN
402 FND_MESSAGE.SET_NAME('FND','FND_MESSAGE_TYPE_CONFIRMATION');
403 G_FND_MSG_TYPE_CONFIRMATION := FND_MESSAGE.GET;
404 END IF ;
405
406 l_msg_type_text := G_FND_MSG_TYPE_CONFIRMATION ;
407
408 ELSE
409 l_msg_type_text := p_msg_type ;
410
411 END IF ;
412 ****************************************************/
413
414 RETURN l_msg_type_text ;
415
416 END Get_Msg_Type_Text;
417
418
419 -----------------------------------------------------------------
420 -- Conver Interface Table Transaction Type to ACD Type --
421 -----------------------------------------------------------------
422 FUNCTION Convert_TxType_To_AcdType(p_tx_type IN VARCHAR2 )
423 RETURN VARCHAR2
424 IS
425 l_acd_type VARCHAR2(30) ;
426
427 BEGIN
428 IF p_tx_type = G_CREATE
429 THEN
430 l_acd_type := G_ADD_ACD_TYPE;
431
432 ELSIF p_tx_type = G_UPDATE
433 THEN
434 l_acd_type := G_CHANGE_ACD_TYPE;
435
436 ELSIF p_tx_type = G_DELETE
437 THEN
438 l_acd_type := G_DELETE_ACD_TYPE;
439
440 ELSE
441 l_acd_type := 'INVALID' ;
442
443 END IF ;
444
445 RETURN l_acd_type ;
446
447 END Convert_TxType_To_AcdType ;
448
449
450 -----------------------------------------------------------------
451 -- Check the entity is processed or not --
452 -----------------------------------------------------------------
453 FUNCTION Is_Processed (p_check_entity IN VARCHAR2
454 , p_process_entity IN VARCHAR2 := NULL
455 )
456 RETURN BOOLEAN
457 IS
458
459
460 BEGIN
461
462
463 IF (p_process_entity IS NULL OR p_process_entity = G_IMPORT_ALL)
464 THEN
465 RETURN TRUE ;
466
467 ELSIF (p_check_entity = G_ALL_ITEM_ENTITY)
468 THEN
469 RETURN (p_process_entity = G_ALL_ITEM_ENTITY OR
470 p_check_entity = p_process_entity) ;
471
472 ELSIF (p_check_entity = G_ITEM_ENTITY)
473 THEN
474
475 RETURN (p_process_entity = G_ALL_ITEM_ENTITY OR
476 p_check_entity = p_process_entity) ;
477
478
479 ELSIF (p_check_entity = G_ITEM_REV_ENTITY)
480 THEN
481
482 RETURN (p_process_entity = G_ALL_ITEM_ENTITY OR
483 p_check_entity = p_process_entity) ;
484
485
486 ELSIF (p_check_entity = G_GDSN_ATTR_ENTITY)
487 THEN
488 RETURN (p_process_entity = G_ALL_ITEM_ENTITY OR
489 p_check_entity = p_process_entity) ;
490
491
492 ELSIF (p_check_entity = G_USER_ATTR_ENTITY)
493 THEN
494 RETURN (p_process_entity = G_ALL_ITEM_ENTITY OR
495 p_check_entity = p_process_entity) ;
496
497
498 ELSIF (p_check_entity = G_MFG_PARTT_NUM_ENTITY)
499 THEN
500 RETURN (p_process_entity = G_ALL_ITEM_ENTITY OR
501 p_check_entity = p_process_entity) ;
502
503
504 ELSIF (p_check_entity = G_BOM_ENTITY)
505 THEN
506 RETURN (p_process_entity = G_ALL_BOM_ENTITY OR
507 p_check_entity = p_process_entity) ;
508
509
510 ELSIF (p_check_entity = G_COMP_ENTITY)
511 THEN
512 RETURN (p_process_entity = G_ALL_BOM_ENTITY OR
513 p_check_entity = p_process_entity) ;
514
515
516 ELSIF (p_check_entity = G_REF_DESG_ENTITY)
517 THEN
518 RETURN (p_process_entity = G_ALL_BOM_ENTITY OR
519 p_check_entity = p_process_entity) ;
520
521
522 ELSIF (p_check_entity = G_SUB_COMP_ENTITY)
523 THEN
524 RETURN (p_process_entity = G_ALL_BOM_ENTITY OR
525 p_check_entity = p_process_entity) ;
526
527 ELSE
528 RETURN FALSE ;
529 END IF;
530
531 END Is_Processed ;
532
533
534 -----------------------------------------------------------------
535 -- Check the entity is processed or not --
536 -----------------------------------------------------------------
537 FUNCTION Get_Attr_Group_Type_Condition (p_table_alias IN VARCHAR2
538 , p_attr_group_type IN VARCHAR2
539 )
540 RETURN VARCHAR2
541 IS
542
543 l_clause VARCHAR2(150);
544
545 BEGIN
546
547 IF p_attr_group_type = G_EGO_ITEM_GTIN_ATTRS
548 THEN
549 l_clause := ' (' || p_table_alias || 'ATTR_GROUP_TYPE = ''' || G_EGO_ITEM_GTIN_ATTRS || '''';
550 l_clause := l_clause || ' OR ' || p_table_alias || 'ATTR_GROUP_TYPE = ''' || G_EGO_ITEM_GTIN_MULTI_ATTRS || '''';
551 l_clause := l_clause || ') ' ;
552
553 ELSIF p_attr_group_type IS NOT NULL
554 THEN
555 -- p_table_alias is like 'INTF.'
556 l_clause := p_table_alias || 'ATTR_GROUP_TYPE = ''' || p_attr_group_type || '''';
557
558 ELSE
559 -- p_table_alias is like 'INTF.'
560 l_clause := p_table_alias || 'ATTR_GROUP_TYPE IS NULL ';
561
562 END IF ;
563
564 RETURN l_clause ;
565
566
567 END Get_Attr_Group_Type_Condition ;
568
569
570
571 -----------------------------------------------------------------
572 -- Get Process Entity Table Definitions --
573 -----------------------------------------------------------------
574 PROCEDURE Get_Process_IntfTable_Def ( p_process_entity IN VARCHAR2 := NULL
575 , x_intf_table IN OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE
576 , x_intf_batch_id_col IN OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE
577 , x_intf_proc_flag_col IN OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE
578 , x_intf_ri_seq_id_col IN OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE
579 , x_intf_attr_grp_type IN OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE
580 , x_intf_chg_notice_col IN OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE
581 )
582 IS
583 I PLS_INTEGER ;
584
585 BEGIN
586 -- Init Index Text Item Attributes
587 I := 0 ;
588
589 -- Set Interface Table
590 IF (Is_Processed(G_ITEM_ENTITY, p_process_entity))
591 THEN
592 I := I + 1 ;
593 x_intf_table(I) := G_ITEM_INTF ;
594 x_intf_batch_id_col(I) := G_ITEM_INTF_BACTH_ID ;
595 x_intf_proc_flag_col(I) := G_ITEM_INTF_PROC_FLAG ;
596 x_intf_ri_seq_id_col(I) := G_ITEM_INTF_RI_SEQ_ID ;
597 x_intf_attr_grp_type(I) := NULL ;
598 x_intf_chg_notice_col(I) := NULL ;
599 END IF ;
600
601 IF (Is_Processed(G_ITEM_REV_ENTITY, p_process_entity))
602 THEN
603 I := I + 1 ;
604 x_intf_table(I) := G_ITEM_REV_INTF ;
605 x_intf_batch_id_col(I) := G_ITEM_REV_INTF_BACTH_ID ;
606 x_intf_proc_flag_col(I) := G_ITEM_REV_INTF_PROC_FLAG ;
607 x_intf_ri_seq_id_col(I) := G_ITEM_REV_INTF_RI_SEQ_ID ;
608 x_intf_attr_grp_type(I) := NULL ;
609 x_intf_chg_notice_col(I) := NULL ;
610 END IF ;
611
612 IF (Is_Processed(G_GDSN_ATTR_ENTITY, p_process_entity))
613 THEN
614 I := I + 1 ;
615 x_intf_table(I) := G_ITEM_USR_ATTR_INTF ;
616 x_intf_batch_id_col(I) := G_ITEM_USR_ATTR_INTF_BACTH_ID ;
617 x_intf_proc_flag_col(I) := G_ITEM_USR_ATTR_INTF_PROC_FLAG ;
618 x_intf_ri_seq_id_col(I) := G_ITEM_USR_ATTR_INTF_RI_SEQ_ID ;
619 x_intf_attr_grp_type(I) := G_EGO_ITEM_GTIN_ATTRS ;
620 x_intf_chg_notice_col(I) := NULL ;
621 END IF ;
622
623
624 IF (Is_Processed(G_USER_ATTR_ENTITY, p_process_entity))
625 THEN
626 I := I + 1 ;
627 x_intf_table(I) := G_ITEM_USR_ATTR_INTF ;
628 x_intf_batch_id_col(I) := G_ITEM_USR_ATTR_INTF_BACTH_ID ;
629 x_intf_proc_flag_col(I) := G_ITEM_USR_ATTR_INTF_PROC_FLAG ;
630 x_intf_ri_seq_id_col(I) := G_ITEM_USR_ATTR_INTF_RI_SEQ_ID ;
631 x_intf_attr_grp_type(I) := G_EGO_ITEMMGMT_GROUP ;
632 x_intf_chg_notice_col(I) := NULL ;
633 END IF ;
634
635 IF (Is_Processed(G_MFG_PARTT_NUM_ENTITY, p_process_entity))
636 THEN
637 I := I + 1 ;
638 x_intf_table(I) := G_ITEM_AML_INTF ;
639 x_intf_batch_id_col(I) := G_ITEM_AML_INTF_BACTH_ID ;
640 x_intf_proc_flag_col(I) := G_ITEM_AML_INTF_PROC_FLAG ;
641 x_intf_ri_seq_id_col(I) := G_ITEM_AML_INTF_RI_SEQ_ID ;
642 x_intf_attr_grp_type(I) := NULL ;
643 x_intf_chg_notice_col(I) := NULL ;
644 END IF ;
645
646
647 IF (Is_Processed(G_BOM_ENTITY, p_process_entity))
648 THEN
649 I := I + 1 ;
650 x_intf_table(I) := G_BOM_INTF ;
651 x_intf_batch_id_col(I) := G_BOM_INTF_BACTH_ID ;
652 x_intf_proc_flag_col(I) := G_BOM_INTF_PROC_FLAG ;
653 x_intf_ri_seq_id_col(I) := NULL ;
654 x_intf_attr_grp_type(I) := NULL ;
655 x_intf_chg_notice_col(I) := G_BOM_INTF_CHG_NOTICE ;
656 END IF ;
657
658 IF (Is_Processed(G_COMP_ENTITY, p_process_entity))
659 THEN
660 I := I + 1 ;
661 x_intf_table(I) := G_COMP_INTF ;
662 x_intf_batch_id_col(I) := G_COMP_INTF_BACTH_ID ;
663 x_intf_proc_flag_col(I) := G_COMP_INTF_PROC_FLAG ;
664 x_intf_ri_seq_id_col(I) := G_COMP_INTF_RI_SEQ_ID ;
665 x_intf_attr_grp_type(I) := NULL ;
666 x_intf_chg_notice_col(I) := G_COMP_INTF_CHG_NOTICE ;
667 END IF ;
668
669 IF (Is_Processed(G_REF_DESG_ENTITY, p_process_entity))
670 THEN
671 I := I + 1 ;
672 x_intf_table(I) := G_REF_DESG_INTF ;
673 x_intf_batch_id_col(I) := G_REF_DESG_INTF_BACTH_ID ;
674 x_intf_proc_flag_col(I) := G_REF_DESG_INTF_PROC_FLAG ;
675 x_intf_ri_seq_id_col(I) := NULL ;
676 x_intf_attr_grp_type(I) := NULL ;
677 x_intf_chg_notice_col(I) := G_REF_DESG_INTF_CHG_NOTICE ;
678 END IF ;
679
680 IF (Is_Processed(G_SUB_COMP_ENTITY, p_process_entity))
681 THEN
682 I := I + 1 ;
683 x_intf_table(I) := G_SUB_COMP_INTF ;
684 x_intf_batch_id_col(I) := G_SUB_COMP_INTF_BACTH_ID ;
685 x_intf_proc_flag_col(I) := G_SUB_COMP_INTF_PROC_FLAG ;
686 x_intf_ri_seq_id_col(I) := NULL ;
687 x_intf_attr_grp_type(I) := NULL ;
688 x_intf_chg_notice_col(I) := G_SUB_COMP_INTF_CHG_NOTICE ;
689 END IF ;
690
691
692
693 END Get_Process_IntfTable_Def ;
694
695 -----------------------------------------------------------------
696 -- Get Revision Import Policy for Batch --
697 -----------------------------------------------------------------
698 FUNCTION GET_REVISION_IMPORT_POLICY ( p_batch_id IN NUMBER )
699 RETURN VARCHAR2
700 IS
701 l_revision_import_policy VARCHAR2(1) ;
702
703 CURSOR c_batch_option ( c_batch_id IN NUMBER)
704 IS
705 SELECT REVISION_IMPORT_POLICY
706 FROM EGO_IMPORT_OPTION_SETS
707 WHERE BATCH_ID = c_batch_id ;
708
709 BEGIN
710
711 Write_Debug(G_PKG_NAME || 'ENG_CHANGE_IMPORT_UTIL.GET_REVISION_IMPORT_POLICY. . . ');
712 Write_Debug('-----------------------------------------' );
713 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
714 Write_Debug('-----------------------------------------' );
715
716 OPEN c_batch_option(p_batch_id);
717 FETCH c_batch_option INTO l_revision_import_policy ;
718 IF (c_batch_option%NOTFOUND)
719 THEN
720 CLOSE c_batch_option ;
721 END IF;
722
723 IF (c_batch_option%ISOPEN)
724 THEN
725 CLOSE c_batch_option;
726 END IF ;
727
728
729 Write_Debug('Revision Import Policy: ' || l_revision_import_policy );
730
731 RETURN l_revision_import_policy ;
732
733 END GET_REVISION_IMPORT_POLICY ;
734
735
736
737 -----------------------------------------------------------------
738 -- Get Revision Import Policy for Batch --
739 -----------------------------------------------------------------
740 FUNCTION GET_CM_IMPORT_OPTION ( p_batch_id IN NUMBER )
741 RETURN VARCHAR2
742 IS
743 l_cm_import_option VARCHAR2(30) ;
744
745 CURSOR c_batch_option ( c_batch_id IN NUMBER)
746 IS
747 SELECT CHANGE_ORDER_CREATION
748 FROM EGO_IMPORT_OPTION_SETS
749 WHERE BATCH_ID = c_batch_id ;
750
751 BEGIN
752
753 Write_Debug(G_PKG_NAME || 'ENG_CHANGE_IMPORT_UTIL.GET_CM_IMPORT_OPTION. . . ');
754 Write_Debug('-----------------------------------------' );
755 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
756 Write_Debug('-----------------------------------------' );
757
758 OPEN c_batch_option(p_batch_id);
759 FETCH c_batch_option INTO l_cm_import_option ;
760
761 IF (c_batch_option%NOTFOUND)
762 THEN
763 l_cm_import_option := G_NO_BATCH ;
764 CLOSE c_batch_option ;
765 END IF;
766
767 IF (c_batch_option%ISOPEN)
768 THEN
769 CLOSE c_batch_option;
770 END IF ;
771
772 Write_Debug('CM Import Import: ' || l_cm_import_option );
773
774 IF l_cm_import_option IS NULL
775 THEN
776
777 l_cm_import_option := G_NO_CHANGE ;
778
779 END IF ;
780
781 RETURN l_cm_import_option ;
782
783 END GET_CM_IMPORT_OPTION ;
784
785
786
787
788
789 /********************************************************************
790 * API Type : Error and Message Handling APIs
791 * Purpose : Error and Message Handling for Change Import
792 *********************************************************************/
793 PROCEDURE Insert_Mtl_Intf_Err
794 ( p_transaction_id IN NUMBER
795 , p_bo_identifier IN VARCHAR2 := NULL
796 , p_error_entity_code IN VARCHAR2 := NULL
797 , p_error_table_name IN VARCHAR2 := NULL
798 , p_error_column_name IN VARCHAR2 := NULL
799 , p_error_msg IN VARCHAR2 := NULL
800 , p_error_msg_type IN VARCHAR2 := NULL
801 , p_error_msg_name IN VARCHAR2 := NULL
802 )
803 IS
804 PRAGMA AUTONOMOUS_TRANSACTION;
805
806 BEGIN
807
808 INSERT INTO MTL_INTERFACE_ERRORS
809 ( ORGANIZATION_ID
810 , UNIQUE_ID
811 , LAST_UPDATE_DATE
812 , LAST_UPDATED_BY
813 , CREATION_DATE
814 , CREATED_BY
815 , LAST_UPDATE_LOGIN
816 , TABLE_NAME
817 , MESSAGE_NAME
818 , COLUMN_NAME
819 , REQUEST_ID
820 , PROGRAM_APPLICATION_ID
821 , PROGRAM_ID
822 , PROGRAM_UPDATE_DATE
823 , ERROR_MESSAGE
824 , TRANSACTION_ID
825 , ENTITY_IDENTIFIER
826 , BO_IDENTIFIER
827 , MESSAGE_TYPE
828 )
829 VALUES
830 ( NULL
831 , NULL
832 , SYSDATE
833 , FND_GLOBAL.user_id
834 , SYSDATE
835 , FND_GLOBAL.user_id
836 , FND_GLOBAL.login_id
837 , p_error_table_name
838 , p_error_msg_name
839 , p_error_table_name
840 , FND_GLOBAL.conc_request_id
841 , FND_GLOBAL.prog_appl_id
842 , FND_GLOBAL.conc_program_id
843 , SYSDATE
844 , SUBSTR(p_error_msg ,1, 2000)
845 , p_transaction_id
846 , p_error_entity_code
847 , p_bo_identifier
848 , p_error_msg_type
849 );
850
851
852 COMMIT ;
853
854 END Insert_Mtl_Intf_Err ;
855
856
857
858 PROCEDURE WRITE_MSG_TO_INTF_TBL
859 ( p_api_version IN NUMBER
860 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
861 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
862 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
863 , x_return_status OUT NOCOPY VARCHAR2
864 , x_msg_count OUT NOCOPY NUMBER
865 , x_msg_data OUT NOCOPY VARCHAR2
866 , p_api_caller IN VARCHAR2 := NULL
867 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
868 , p_output_dir IN VARCHAR2 := NULL
869 , p_debug_filename IN VARCHAR2 := NULL
870 , p_batch_id IN NUMBER
871 , p_transaction_id IN NUMBER
872 , p_bo_identifier IN VARCHAR2 := NULL
873 , p_error_entity_code IN VARCHAR2 := NULL
874 , p_error_table_name IN VARCHAR2 := NULL
875 , p_error_column_name IN VARCHAR2 := NULL
876 , p_error_msg IN VARCHAR2 := NULL
877 , p_error_msg_type IN VARCHAR2 := NULL
878 , p_error_msg_name IN VARCHAR2 := NULL
879 )
880 IS
881 l_api_name CONSTANT VARCHAR2(30) := 'WRITE_MSG_TO_INTF_TBL';
882 l_api_version CONSTANT NUMBER := 1.0;
883
884 l_init_msg_list VARCHAR2(1) ;
885 l_validation_level NUMBER ;
886 l_commit VARCHAR2(1) ;
887
888
889 BEGIN
890
891
892 -- Standard Start of API savepoint
893 SAVEPOINT WRITE_MSG_TO_INTF_TBL;
894
895 -- Standard call to check for call compatibility.
896 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
897 p_api_version ,
898 l_api_name ,
899 G_PKG_NAME )
900 THEN
901 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
902 END IF;
903
904 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
905 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
906 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
907
908
909
910 -- Initialize message list if p_init_msg_list is set to TRUE.
911 IF FND_API.to_Boolean( l_init_msg_list ) THEN
912 FND_MSG_PUB.initialize;
913 END IF;
914
915 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
916
917 Write_Debug('After Open_Debug_Session');
918 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
919 Write_Debug('-----------------------------------------' );
920 Write_Debug('p_api_version: ' || to_char(p_api_version));
921 Write_Debug('p_init_msg_list:' || p_init_msg_list);
922 Write_Debug('p_commit:' || p_commit);
923 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
924 Write_Debug('p_api_caller:' || p_api_caller);
925 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
926 Write_Debug('p_transaction_id: ' || to_char(p_transaction_id));
927 Write_Debug('p_bo_identifier:' || p_bo_identifier);
928 Write_Debug('p_error_entity_code:' || p_bo_identifier);
929 Write_Debug('p_error_table_name:' || p_error_table_name);
930 Write_Debug('p_error_column_name:' || p_error_column_name);
931 Write_Debug('p_error_msg:' || p_error_msg);
932 Write_Debug('p_error_msg_type:' || p_error_msg_type);
933 Write_Debug('p_error_msg_name:' || p_error_msg_name);
934 Write_Debug('-----------------------------------------' );
935
936
937 -- Initialize API return status to success
938 x_return_status := G_RET_STS_SUCCESS;
939
940
941 -- API body
942 -- Logic Here
943 -- Enahanced EGO_ITEM_BULKLOAD_PKG.Insert_Mtl_Intf_Err
944 --
945 Insert_Mtl_Intf_Err
946 ( p_transaction_id => p_transaction_id
947 , p_bo_identifier => p_bo_identifier
948 , p_error_entity_code => p_error_entity_code
949 , p_error_table_name => p_error_table_name
950 , p_error_column_name => p_error_column_name
951 , p_error_msg => SUBSTR(p_error_msg ,1, 2000)
952 , p_error_msg_type => Get_Msg_Type_Text(p_error_msg_type)
953 , p_error_msg_name => p_error_msg_name
954 ) ;
955
956 -- End of API body.
957
958
959 -- Standard check of p_commit.
960 IF FND_API.To_Boolean( p_commit ) THEN
961 COMMIT WORK;
962 END IF;
963
964 -- Standard call to get message count and if count is 1, get message info.
965 FND_MSG_PUB.Count_And_Get
966 ( p_count => x_msg_count ,
967 p_data => x_msg_data
968 );
969
970
971
972 -----------------------------------------------------
973 -- Close Error Handler Debug Session.
974 -----------------------------------------------------
975 Close_Debug_Session;
976
977
978 EXCEPTION
979 WHEN FND_API.G_EXC_ERROR THEN
980 ROLLBACK TO WRITE_MSG_TO_INTF_TBL;
981 x_return_status := G_RET_STS_ERROR ;
982 FND_MSG_PUB.Count_And_Get
983 ( p_count => x_msg_count ,
984 p_data => x_msg_data
985 );
986
987 Close_Debug_Session;
988
989 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
990 ROLLBACK TO WRITE_MSG_TO_INTF_TBL;
991 x_return_status := G_RET_STS_UNEXP_ERROR ;
992 FND_MSG_PUB.Count_And_Get
993 ( p_count => x_msg_count ,
994 p_data => x_msg_data
995 );
996
997 Close_Debug_Session;
998
999 WHEN OTHERS THEN
1000 ROLLBACK TO WRITE_MSG_TO_INTF_TBL;
1001 x_return_status := G_RET_STS_UNEXP_ERROR ;
1002
1003 IF FND_MSG_PUB.Check_Msg_Level
1004 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1005 THEN
1006 FND_MSG_PUB.Add_Exc_Msg
1007 ( G_PKG_NAME ,
1008 l_api_name
1009 );
1010 END IF;
1011
1012 FND_MSG_PUB.Count_And_Get
1013 ( p_count => x_msg_count ,
1014 p_data => x_msg_data
1015 );
1016
1017 Close_Debug_Session;
1018
1019
1020 END WRITE_MSG_TO_INTF_TBL ;
1021
1022
1023
1024
1025 /********************************************************************
1026 * API Type : Validation APIs
1027 * Purpose : Perform Validation for Change Import
1028 *********************************************************************/
1029 PROCEDURE VALIDATE_RECORDS
1030 ( p_api_version IN NUMBER
1031 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
1032 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
1033 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
1034 , x_return_status OUT NOCOPY VARCHAR2
1035 , x_msg_count OUT NOCOPY NUMBER
1036 , x_msg_data OUT NOCOPY VARCHAR2
1037 , p_write_msg_to_intftbl IN VARCHAR2 := NULL -- FND_API.G_FALSE
1038 , p_api_caller IN VARCHAR2 := NULL
1039 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
1040 , p_output_dir IN VARCHAR2 := NULL
1041 , p_debug_filename IN VARCHAR2 := NULL
1042 , p_batch_id IN NUMBER
1043 , p_batch_type IN VARCHAR2 := NULL
1044 , p_process_entity IN VARCHAR2 := NULL
1045 , p_cm_process_type IN VARCHAR2 := NULL
1046 )
1047 IS
1048 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RECORDS';
1049 l_api_version CONSTANT NUMBER := 1.0;
1050
1051 l_init_msg_list VARCHAR2(1) ;
1052 l_validation_level NUMBER ;
1053 l_commit VARCHAR2(1) ;
1054 l_write_msg_to_intftbl VARCHAR2(1) ;
1055
1056
1057 l_cm_import_option VARCHAR2(30) ;
1058 l_msg_process_flag BOOLEAN ;
1059
1060 --------------------------------------------
1061 -- Long Dynamic SQL String
1062 --------------------------------------------
1063 l_dyn_sql VARCHAR2(10000);
1064
1065 I PLS_INTEGER ;
1066 l_intf_table DBMS_SQL.VARCHAR2_TABLE;
1067 l_intf_batch_id_col DBMS_SQL.VARCHAR2_TABLE;
1068 l_intf_proc_flag_col DBMS_SQL.VARCHAR2_TABLE;
1069 l_intf_ri_seq_id_col DBMS_SQL.VARCHAR2_TABLE;
1070 l_intf_attr_grp_type DBMS_SQL.VARCHAR2_TABLE;
1071 l_intf_change_number_col DBMS_SQL.VARCHAR2_TABLE;
1072
1073 l_error_msg_text VARCHAR2(2000) ;
1074 l_error_msg_name VARCHAR2(30) ;
1075
1076 BEGIN
1077
1078
1079 -- Standard Start of API savepoint
1080 SAVEPOINT VALIDATE_RECORDS;
1081
1082 -- Standard call to check for call compatibility.
1083 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1084 p_api_version ,
1085 l_api_name ,
1086 G_PKG_NAME )
1087 THEN
1088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1089 END IF;
1090
1091
1092 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
1093 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
1094 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
1095 l_write_msg_to_intftbl := NVL(p_write_msg_to_intftbl,FND_API.G_FALSE) ;
1096 l_msg_process_flag := FALSE ;
1097
1098
1099 -- Initialize message list if l_init_msg_list is set to TRUE.
1100 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1101 FND_MSG_PUB.initialize;
1102 END IF;
1103
1104 -- Open Debug Session by a give param or profile option.
1105 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1106
1107 Write_Debug('After Open_Debug_Session');
1108 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
1109 Write_Debug('-----------------------------------------' );
1110 Write_Debug('p_api_version: ' || to_char(p_api_version));
1111 Write_Debug('p_init_msg_list:' || p_init_msg_list);
1112 Write_Debug('p_commit:' || p_commit);
1113 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
1114 Write_Debug('p_write_msg_to_intftbl:' || p_write_msg_to_intftbl);
1115 Write_Debug('p_api_caller:' || p_api_caller);
1116 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
1117 Write_Debug('p_batch_type: ' || p_batch_type);
1118
1119 -- Initialize API return status to success
1120 x_return_status := G_RET_STS_SUCCESS;
1121
1122
1123 -- API body
1124 -- Logic Here
1125
1126 -- Perform following validations
1127 -- Get Process Interface Table Definitions
1128 Get_Process_IntfTable_Def(p_process_entity => p_process_entity
1129 , x_intf_table => l_intf_table
1130 , x_intf_batch_id_col => l_intf_batch_id_col
1131 , x_intf_proc_flag_col => l_intf_proc_flag_col
1132 , x_intf_ri_seq_id_col => l_intf_ri_seq_id_col
1133 , x_intf_attr_grp_type => l_intf_attr_grp_type
1134 , x_intf_chg_notice_col => l_intf_change_number_col
1135 ) ;
1136
1137
1138
1139 -- Get Change Mgmt Import Option
1140 l_cm_import_option := GET_CM_IMPORT_OPTION(p_batch_id => p_batch_id) ;
1141
1142
1143 FOR i IN 1..l_intf_table.COUNT LOOP
1144
1145 -- 1. If CM Batch Type is NONE (Batch does not exist),
1146 -- OR CM Option is None
1147 -- OR CM Option is NULL
1148 -- , we will make the records with process_flag 5
1149 -- to ERROR
1150 IF G_NO_BATCH = p_batch_type OR
1151 G_NO_BATCH = l_cm_import_option OR
1152 G_NO_CHANGE = l_cm_import_option OR
1153 l_cm_import_option IS NULL
1154 THEN
1155 l_dyn_sql := '' ;
1156 l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
1157 l_dyn_sql := l_dyn_sql || ' SET INTF.change_id = -100 ' ;
1158 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
1159 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED ;
1160
1161 IF l_intf_attr_grp_type(i) IS NOT NULL
1162 THEN
1163 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
1164 END IF ;
1165
1166
1167 Write_Debug(l_dyn_sql);
1168
1169 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
1170
1171
1172 -- Set the msg process Flag to TRUE to process the error msg later
1173 l_msg_process_flag := TRUE ;
1174
1175
1176
1177 END IF ;
1178
1179
1180 IF l_validation_level > FND_API.G_VALID_LEVEL_NONE AND
1181 FND_API.to_Boolean(l_write_msg_to_intftbl) AND
1182 l_msg_process_flag
1183 THEN
1184
1185 Write_Debug('Insert Error Message for the records which is set to CM Process per Change ID value set above validation. . .' );
1186
1187 --
1188 -- CHANGE_ID = -100 is ENG_IMPT_INVALID_BATCH_ID
1189 --
1190 -- In R12, it's only one validate
1191 -- We did not implementing decode in SQL statement
1192 --
1193 IF ( G_NO_BATCH = p_batch_type OR
1194 G_NO_BATCH = l_cm_import_option )
1195 THEN
1196
1197 l_error_msg_name := 'ENG_IMPT_INVALID_BATCH_ID' ;
1198 FND_MESSAGE.SET_NAME('ENG',l_error_msg_name);
1199 l_error_msg_text := FND_MESSAGE.GET;
1200
1201 ELSIF ( G_NO_CHANGE = l_cm_import_option OR
1202 l_cm_import_option IS NULL )
1203 THEN
1204
1205 l_error_msg_name := 'ENG_IMPT_NO_CM_OPTION' ;
1206 FND_MESSAGE.SET_NAME('ENG',l_error_msg_name);
1207 l_error_msg_text := FND_MESSAGE.GET;
1208
1209 END IF ;
1210
1211
1212 l_dyn_sql := '';
1213 l_dyn_sql := 'INSERT INTO MTL_INTERFACE_ERRORS ';
1214 l_dyn_sql := l_dyn_sql || '( ';
1215 l_dyn_sql := l_dyn_sql || ' ORGANIZATION_ID , ';
1216 l_dyn_sql := l_dyn_sql || ' UNIQUE_ID , ';
1217 l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID , ';
1218 l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE , ';
1219 l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY , ';
1220 l_dyn_sql := l_dyn_sql || ' CREATION_DATE, ';
1221 l_dyn_sql := l_dyn_sql || ' CREATED_BY , ';
1222 l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN , ';
1223 l_dyn_sql := l_dyn_sql || ' TABLE_NAME , ';
1224 l_dyn_sql := l_dyn_sql || ' MESSAGE_NAME , ';
1225 l_dyn_sql := l_dyn_sql || ' COLUMN_NAME , ';
1226 l_dyn_sql := l_dyn_sql || ' REQUEST_ID , ';
1227 l_dyn_sql := l_dyn_sql || ' PROGRAM_APPLICATION_ID , ';
1228 l_dyn_sql := l_dyn_sql || ' PROGRAM_ID , ';
1229 l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE , ';
1230 l_dyn_sql := l_dyn_sql || ' ERROR_MESSAGE , ';
1231 l_dyn_sql := l_dyn_sql || ' ENTITY_IDENTIFIER , ';
1232 l_dyn_sql := l_dyn_sql || ' BO_IDENTIFIER , ';
1233 l_dyn_sql := l_dyn_sql || ' MESSAGE_TYPE ';
1234 l_dyn_sql := l_dyn_sql || ') ';
1235 l_dyn_sql := l_dyn_sql || 'SELECT ';
1236 l_dyn_sql := l_dyn_sql || ' INTF.ORGANIZATION_ID , ';
1237 l_dyn_sql := l_dyn_sql || ' NULL , ';
1238 l_dyn_sql := l_dyn_sql || ' INTF.TRANSACTION_ID , ';
1239 l_dyn_sql := l_dyn_sql || ' SYSDATE , ';
1240 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.user_id , ';
1241 l_dyn_sql := l_dyn_sql || ' SYSDATE , ';
1242 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.user_id , ';
1243 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.login_id , ';
1244 l_dyn_sql := l_dyn_sql || '''' ||l_intf_table(i) ||''', ' ;
1245 l_dyn_sql := l_dyn_sql || ''''||l_error_msg_name|| ''', ';
1246 l_dyn_sql := l_dyn_sql || ' NULL , ';
1247 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.conc_request_id , ';
1248 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.prog_appl_id, ';
1249 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.conc_program_id , ';
1250 l_dyn_sql := l_dyn_sql || ' SYSDATE , ';
1251 l_dyn_sql := l_dyn_sql || ''''||Escape_Single_Quote(l_error_msg_text)||''' , ' ;
1252 l_dyn_sql := l_dyn_sql || ''''||G_ERROR_ENTITY_CODE||''' , ' ;
1253 l_dyn_sql := l_dyn_sql || ''''||G_BO_IDENTIFIER||''', ' ;
1254 l_dyn_sql := l_dyn_sql || ''''||G_ENG_MSG_TYPE_ERROR||'''' ;
1255
1256 l_dyn_sql := l_dyn_sql || ' FROM ' || l_intf_table(i) || ' INTF ';
1257 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
1258 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED;
1259 l_dyn_sql := l_dyn_sql || ' AND INTF.change_id = -100 ';
1260
1261 IF l_intf_attr_grp_type(i) IS NOT NULL
1262 THEN
1263 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
1264 END IF ;
1265
1266
1267 Write_Debug(l_dyn_sql);
1268
1269 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
1270
1271
1272 END IF;
1273
1274 Write_Debug('Update Process Flag to Error for records which can not find CO for the Org. . .' );
1275
1276 l_dyn_sql := '' ;
1277 l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
1278 l_dyn_sql := l_dyn_sql || ' SET INTF.change_id = null ' ;
1279
1280 -- If validation level is more than None
1281 -- set the process flag to ERROR:3
1282 IF l_validation_level > FND_API.G_VALID_LEVEL_NONE
1283 THEN
1284 l_dyn_sql := l_dyn_sql || ' , INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_PS_ERROR ;
1285 END IF ;
1286
1287 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
1288 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED;
1289 l_dyn_sql := l_dyn_sql || ' AND INTF.change_id = -100 ';
1290
1291 IF l_intf_attr_grp_type(i) IS NOT NULL
1292 THEN
1293 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
1294 END IF ;
1295
1296 Write_Debug(l_dyn_sql);
1297
1298 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
1299 IF SQL%FOUND THEN
1300 x_return_status := G_RET_STS_WARNING ;
1301 END IF ;
1302
1303 END LOOP ;
1304
1305 -- End of API body.
1306
1307
1308 -- Standard check of l_commit.
1309 IF FND_API.To_Boolean( l_commit ) THEN
1310 COMMIT WORK;
1311 END IF;
1312
1313 -- Standard call to get message count and if count is 1, get message info.
1314 FND_MSG_PUB.Count_And_Get
1315 ( p_count => x_msg_count ,
1316 p_data => x_msg_data
1317 );
1318
1319
1320 -----------------------------------------------------
1321 -- Close Error Handler Debug Session.
1322 -----------------------------------------------------
1323 Close_Debug_Session;
1324
1325
1326 EXCEPTION
1327 WHEN FND_API.G_EXC_ERROR THEN
1328 ROLLBACK TO VALIDATE_RECORDS;
1329 x_return_status := G_RET_STS_ERROR ;
1330 FND_MSG_PUB.Count_And_Get
1331 ( p_count => x_msg_count ,
1332 p_data => x_msg_data
1333 );
1334
1335 Close_Debug_Session;
1336
1337
1338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1339 ROLLBACK TO VALIDATE_RECORDS;
1340 x_return_status := G_RET_STS_UNEXP_ERROR ;
1341 FND_MSG_PUB.Count_And_Get
1342 ( p_count => x_msg_count ,
1343 p_data => x_msg_data
1344 );
1345
1346
1347 Close_Debug_Session;
1348
1349 WHEN OTHERS THEN
1350 ROLLBACK TO VALIDATE_RECORDS;
1351 x_return_status := G_RET_STS_UNEXP_ERROR ;
1352
1353 IF FND_MSG_PUB.Check_Msg_Level
1354 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1355 THEN
1356 FND_MSG_PUB.Add_Exc_Msg
1357 ( G_PKG_NAME ,
1358 l_api_name
1359 );
1360 END IF;
1361
1362 FND_MSG_PUB.Count_And_Get
1363 ( p_count => x_msg_count ,
1364 p_data => x_msg_data
1365 );
1366
1367
1368 Close_Debug_Session;
1369
1370 END VALIDATE_RECORDS ;
1371
1372
1373
1374
1375 PROCEDURE MERGE_GDSN_PENDING_CHG_ROWS
1376 ( p_inventory_item_id IN NUMBER
1377 ,p_organization_id IN NUMBER
1378 ,p_change_id IN NUMBER
1379 ,p_change_line_id IN NUMBER
1380 ,p_acd_type IN VARCHAR2 := NULL
1381 ,x_single_row_attrs_rec IN OUT NOCOPY EGO_ITEM_PUB.UCCNET_ATTRS_SINGL_ROW_REC_TYP
1382 ,x_multi_row_attrs_tbl IN OUT NOCOPY EGO_ITEM_PUB.UCCNET_ATTRS_MULTI_ROW_TBL_TYP
1383 ,x_extra_attrs_rec IN OUT NOCOPY EGO_ITEM_PUB.UCCNET_EXTRA_ATTRS_REC_TYP
1384 )
1385 IS
1386 CURSOR c_pending_single_row_attrs(c_inventory_item_id IN NUMBER
1387 ,c_organization_id IN NUMBER
1388 ,c_change_id IN NUMBER
1389 ,c_change_line_id IN NUMBER
1390 ,c_acd_type IN VARCHAR2
1391 ) IS
1392 SELECT
1393 -- EXTENSION_ID,
1394 -- INVENTORY_ITEM_ID,
1395 -- ORGANIZATION_ID,
1396 -- CREATED_BY,
1397 -- CREATION_DATE,
1398 -- LAST_UPDATED_BY,
1399 -- LAST_UPDATE_DATE,
1400 -- LAST_UPDATE_LOGIN,
1401 -- ITEM_CATALOG_GROUP_ID,
1402 -- REVISION_ID,
1403 IS_TRADE_ITEM_A_CONSUMER_UNIT,
1404 IS_TRADE_ITEM_INFO_PRIVATE,
1405 GROSS_WEIGHT,
1406 UOM_GROSS_WEIGHT,
1407 EFFECTIVE_DATE,
1408 CANCELED_DATE,
1409 DISCONTINUED_DATE,
1410 END_AVAILABILITY_DATE_TIME,
1411 START_AVAILABILITY_DATE_TIME,
1412 BRAND_NAME,
1413 IS_TRADE_ITEM_A_BASE_UNIT,
1414 IS_TRADE_ITEM_A_VARIABLE_UNIT,
1415 IS_PACK_MARKED_WITH_EXP_DATE,
1416 IS_PACK_MARKED_WITH_GREEN_DOT,
1417 IS_PACK_MARKED_WITH_INGRED,
1418 IS_PACKAGE_MARKED_AS_REC,
1419 IS_PACKAGE_MARKED_RET,
1420 STACKING_FACTOR,
1421 STACKING_WEIGHT_MAXIMUM,
1422 UOM_STACKING_WEIGHT_MAXIMUM,
1423 ORDERING_LEAD_TIME,
1424 UOM_ORDERING_LEAD_TIME,
1425 ORDER_QUANTITY_MAX,
1426 ORDER_QUANTITY_MIN,
1427 ORDER_QUANTITY_MULTIPLE,
1428 ORDER_SIZING_FACTOR,
1429 EFFECTIVE_START_DATE,
1430 CATALOG_PRICE,
1431 EFFECTIVE_END_DATE,
1432 SUGGESTED_RETAIL_PRICE,
1433 MATERIAL_SAFETY_DATA_SHEET_NO,
1434 HAS_BATCH_NUMBER,
1435 IS_NON_SOLD_TRADE_RET_FLAG,
1436 IS_TRADE_ITEM_MAR_REC_FLAG,
1437 DIAMETER,
1438 UOM_DIAMETER,
1439 DRAINED_WEIGHT,
1440 UOM_DRAINED_WEIGHT,
1441 GENERIC_INGREDIENT,
1442 GENERIC_INGREDIENT_STRGTH,
1443 UOM_GENERIC_INGREDIENT_STRGTH,
1444 INGREDIENT_STRENGTH,
1445 IS_NET_CONTENT_DEC_FLAG,
1446 NET_CONTENT,
1447 UOM_NET_CONTENT,
1448 PEG_HORIZONTAL,
1449 UOM_PEG_HORIZONTAL,
1450 PEG_VERTICAL,
1451 UOM_PEG_VERTICAL,
1452 CONSUMER_AVAIL_DATE_TIME,
1453 DEL_TO_DIST_CNTR_TEMP_MAX,
1454 UOM_DEL_TO_DIST_CNTR_TEMP_MAX,
1455 DEL_TO_DIST_CNTR_TEMP_MIN,
1456 UOM_DEL_TO_DIST_CNTR_TEMP_MIN,
1457 DELIVERY_TO_MRKT_TEMP_MAX,
1458 UOM_DELIVERY_TO_MRKT_TEMP_MAX,
1459 DELIVERY_TO_MRKT_TEMP_MIN,
1460 UOM_DELIVERY_TO_MRKT_TEMP_MIN,
1461 SUB_BRAND,
1462 -- TRADE_ITEM_DESCRIPTOR,
1463 EANUCC_CODE,
1464 EANUCC_TYPE,
1465 RETAIL_PRICE_ON_TRADE_ITEM,
1466 QUANTITY_OF_COMP_LAY_ITEM,
1467 QUANITY_OF_ITEM_IN_LAYER,
1468 QUANTITY_OF_ITEM_INNER_PACK,
1469 TARGET_MARKET_DESC,
1470 QUANTITY_OF_INNER_PACK,
1471 BRAND_OWNER_GLN,
1472 BRAND_OWNER_NAME,
1473 STORAGE_HANDLING_TEMP_MAX,
1474 UOM_STORAGE_HANDLING_TEMP_MAX,
1475 STORAGE_HANDLING_TEMP_MIN,
1476 UOM_STORAGE_HANDLING_TEMP_MIN,
1477 TRADE_ITEM_COUPON,
1478 DEGREE_OF_ORIGINAL_WORT,
1479 FAT_PERCENT_IN_DRY_MATTER,
1480 PERCENT_OF_ALCOHOL_BY_VOL,
1481 ISBN_NUMBER,
1482 ISSN_NUMBER,
1483 IS_INGREDIENT_IRRADIATED,
1484 IS_RAW_MATERIAL_IRRADIATED,
1485 IS_TRADE_ITEM_GENETICALLY_MOD,
1486 IS_TRADE_ITEM_IRRADIATED,
1487 PUBLICATION_STATUS,
1488 TOP_GTIN,
1489 SECURITY_TAG_LOCATION,
1490 URL_FOR_WARRANTY,
1491 NESTING_INCREMENT,
1492 UOM_NESTING_INCREMENT,
1493 IS_TRADE_ITEM_RECALLED,
1494 MODEL_NUMBER,
1495 PIECES_PER_TRADE_ITEM,
1496 UOM_PIECES_PER_TRADE_ITEM,
1497 DEPT_OF_TRNSPRT_DANG_GOODS_NUM,
1498 RETURN_GOODS_POLICY,
1499 IS_OUT_OF_BOX_PROVIDED,
1500 REGISTRATION_UPDATE_DATE,
1501 TP_NEUTRAL_UPDATE_DATE,
1502 MASTER_ORG_EXTENSION_ID,
1503 IS_BARCODE_SYMBOLOGY_DERIVABLE,
1504 INVOICE_NAME,
1505 DESCRIPTIVE_SIZE,
1506 FUNCTIONAL_NAME,
1507 TRADE_ITEM_FORM_DESCRIPTION,
1508 WARRANTY_DESCRIPTION,
1509 TRADE_ITEM_FINISH_DESCRIPTION ,
1510 DESCRIPTION_SHORT -- ,
1511 -- CHANGE_ID,
1512 -- CHANGE_LINE_ID,
1513 -- ACD_TYPE,
1514 -- IMPLEMENTATION_DATE
1515 FROM EGO_GTN_ATTR_CHG_VL
1516 WHERE INVENTORY_ITEM_ID = c_inventory_item_id
1517 AND ORGANIZATION_ID = c_organization_id
1518 AND CHANGE_ID = c_change_id
1519 AND CHANGE_LINE_ID = c_change_line_Id
1520 AND ( ACD_TYPE = c_acd_type OR c_acd_type IS NULL)
1521 AND ACD_TYPE <>'HISTORY';
1522
1523 CURSOR c_pending_multi_row_attrs(c_inventory_item_id IN NUMBER
1524 ,c_organization_id IN NUMBER
1525 ,c_change_id IN NUMBER
1526 ,c_change_line_id IN NUMBER
1527 ,c_acd_type IN VARCHAR2
1528 ) IS
1529 SELECT
1530 DECODE(ACD_TYPE,'ADD',null,pend.EXTENSION_ID) AS EXTENSION_ID,
1531 -- INVENTORY_ITEM_ID,
1532 -- ORGANIZATION_ID,
1533 -- CREATED_BY,
1534 -- CREATION_DATE,
1535 -- LAST_UPDATED_BY,
1536 -- LAST_UPDATE_DATE,
1537 -- LAST_UPDATE_LOGIN,
1538 -- ITEM_CATALOG_GROUP_ID,
1539 -- REVISION_ID,
1540 pend.ATTR_GROUP_ID,
1541 NVL(pend.MANUFACTURER_GLN, prod.MANUFACTURER_GLN) MANUFACTURER_GLN,
1542 NVL(pend.MANUFACTURER_ID, prod.MANUFACTURER_ID) MANUFACTURER_ID,
1543 NVL(pend.PARTY_RECEIVING_PRIVATE_DATA, prod.PARTY_RECEIVING_PRIVATE_DATA) PARTY_RECEIVING_PRIVATE_DATA,
1544 NVL(pend.BAR_CODE_TYPE, prod.BAR_CODE_TYPE) BAR_CODE_TYPE,
1545 NVL(pend.COLOR_CODE_LIST_AGENCY, prod.COLOR_CODE_LIST_AGENCY) COLOR_CODE_LIST_AGENCY,
1546 NVL(pend.COLOR_CODE_VALUE, prod.COLOR_CODE_VALUE) COLOR_CODE_VALUE,
1547 NVL(pend.CLASS_OF_DANGEROUS_CODE, prod.CLASS_OF_DANGEROUS_CODE) CLASS_OF_DANGEROUS_CODE,
1548 NVL(pend.DANGEROUS_GOODS_MARGIN_NUMBER, prod.DANGEROUS_GOODS_MARGIN_NUMBER)DANGEROUS_GOODS_MARGIN_NUMBER,
1549 NVL(pend.DANGEROUS_GOODS_HAZARDOUS_CODE,prod.DANGEROUS_GOODS_HAZARDOUS_CODE)DANGEROUS_GOODS_HAZARDOUS_CODE,
1550 NVL(pend.DANGEROUS_GOODS_PACK_GROUP, prod.DANGEROUS_GOODS_PACK_GROUP) DANGEROUS_GOODS_PACK_GROUP,
1551 NVL(pend.DANGEROUS_GOODS_REG_CODE, prod.DANGEROUS_GOODS_REG_CODE) DANGEROUS_GOODS_REG_CODE,
1552 NVL(pend.DANGEROUS_GOODS_SHIPPING_NAME, prod.DANGEROUS_GOODS_SHIPPING_NAME)DANGEROUS_GOODS_SHIPPING_NAME,
1553 NVL(pend.UNITED_NATIONS_DANG_GOODS_NO, prod.UNITED_NATIONS_DANG_GOODS_NO) UNITED_NATIONS_DANG_GOODS_NO,
1554 NVL(pend.FLASH_POINT_TEMP, prod.FLASH_POINT_TEMP) FLASH_POINT_TEMP,
1555 NVL(pend.UOM_FLASH_POINT_TEMP, prod.UOM_FLASH_POINT_TEMP) UOM_FLASH_POINT_TEMP,
1556 NVL(pend.COUNTRY_OF_ORIGIN, prod.COUNTRY_OF_ORIGIN) COUNTRY_OF_ORIGIN,
1557 NVL(pend.HARMONIZED_TARIFF_SYS_ID_CODE, prod.HARMONIZED_TARIFF_SYS_ID_CODE)HARMONIZED_TARIFF_SYS_ID_CODE,
1558 NVL(pend.SIZE_CODE_LIST_AGENCY, prod.SIZE_CODE_LIST_AGENCY) SIZE_CODE_LIST_AGENCY,
1559 NVL(pend.SIZE_CODE_VALUE, prod.SIZE_CODE_VALUE) SIZE_CODE_VALUE,
1560 pend.MASTER_ORG_EXTENSION_ID,
1561 NVL(pend.HANDLING_INSTRUCTIONS_CODE, prod.HANDLING_INSTRUCTIONS_CODE) HANDLING_INSTRUCTIONS_CODE,
1562 NVL(pend.DANGEROUS_GOODS_TECHNICAL_NAME,prod.DANGEROUS_GOODS_TECHNICAL_NAME)DANGEROUS_GOODS_TECHNICAL_NAME,
1563 NVL(pend.DELIVERY_METHOD_INDICATOR , prod.DELIVERY_METHOD_INDICATOR ) DELIVERY_METHOD_INDICATOR,
1564 -- CHANGE_ID,
1565 -- CHANGE_LINE_ID,
1566 DECODE(ACD_TYPE,'DELETE',ACD_TYPE,null) TRANSACTION_TYPE
1567 -- IMPLEMENTATION_DATE
1568 FROM EGO_GTN_MUL_ATTR_CHG_VL pend,
1569 EGO_ITM_GTN_MUL_ATTRS_VL prod
1570 WHERE pend.EXTENSION_ID = prod.EXTENSION_ID (+)
1571 AND pend.INVENTORY_ITEM_ID = c_inventory_item_id
1572 AND pend.ORGANIZATION_ID = c_organization_id
1573 AND pend.CHANGE_ID = c_change_id
1574 AND pend.CHANGE_LINE_ID = c_change_line_Id
1575 AND ( pend.ACD_TYPE = c_acd_type OR c_acd_type IS NULL)
1576 AND ACD_TYPE <>'HISTORY'
1577 ORDER BY ATTR_GROUP_ID;
1578
1579
1580 CURSOR c_pending_gdsn_extra_row_attrs(c_inventory_item_id IN NUMBER
1581 ,c_organization_id IN NUMBER
1582 ,c_change_id IN NUMBER
1583 ,c_change_line_id IN NUMBER
1584 ,c_acd_type IN VARCHAR2
1585 ) IS
1586 SELECT
1587 UNIT_WEIGHT
1588 FROM EGO_MTL_SY_ITEMS_CHG_B
1589 WHERE INVENTORY_ITEM_ID = c_inventory_item_id
1590 AND ORGANIZATION_ID = c_organization_id
1591 AND CHANGE_ID = c_change_id
1592 AND CHANGE_LINE_ID = c_change_line_Id
1593 AND ACD_TYPE = G_CHANGE_ACD_TYPE ;
1594
1595
1596 k BINARY_INTEGER;
1597 l_found BOOLEAN ;
1598 l_merge_target_tbl_empty BOOLEAN ;
1599
1600
1601 BEGIN
1602
1603 -- MK. NEED TO WORK ON THIS PROCEDURE
1604 -- Need to modify following logic using meta data
1605 -- Need to modify UOM logic, if base value is miss value, we may need to set UOM value as null
1606 -- Don't do hard-coding for column name etc not to have any dependency
1607
1608 Write_Debug('Begin MERGE_GDSN_PENDING_CHG_ROWS') ;
1609
1610 FOR j IN c_pending_single_row_attrs(p_inventory_item_id, p_organization_id, p_change_id, p_change_line_id, p_acd_type)
1611 LOOP
1612 Write_Debug('Merging Single row . . .') ;
1613
1614 -- x_single_row_attrs.LANGUAGE_CODE := USERENV('LANG');
1615 x_single_row_attrs_rec.IS_TRADE_ITEM_A_CONSUMER_UNIT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_TRADE_ITEM_A_CONSUMER_UNIT, j.IS_TRADE_ITEM_A_CONSUMER_UNIT));
1616 x_single_row_attrs_rec.IS_TRADE_ITEM_INFO_PRIVATE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_TRADE_ITEM_INFO_PRIVATE, j.IS_TRADE_ITEM_INFO_PRIVATE));
1617 x_single_row_attrs_rec.GROSS_WEIGHT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.GROSS_WEIGHT, j.GROSS_WEIGHT));
1618
1619 -- UOM:
1620 x_single_row_attrs_rec.UOM_GROSS_WEIGHT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_GROSS_WEIGHT, j.UOM_GROSS_WEIGHT));
1621
1622
1623 x_single_row_attrs_rec.EFFECTIVE_DATE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.EFFECTIVE_DATE, j.EFFECTIVE_DATE));
1624 x_single_row_attrs_rec.END_AVAILABILITY_DATE_TIME := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.END_AVAILABILITY_DATE_TIME, j.END_AVAILABILITY_DATE_TIME));
1625 x_single_row_attrs_rec.START_AVAILABILITY_DATE_TIME := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.START_AVAILABILITY_DATE_TIME, j.START_AVAILABILITY_DATE_TIME));
1626 x_single_row_attrs_rec.BRAND_NAME := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.BRAND_NAME, j.BRAND_NAME));
1627 x_single_row_attrs_rec.IS_TRADE_ITEM_A_BASE_UNIT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_TRADE_ITEM_A_BASE_UNIT, j.IS_TRADE_ITEM_A_BASE_UNIT));
1628 x_single_row_attrs_rec.IS_TRADE_ITEM_A_VARIABLE_UNIT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_TRADE_ITEM_A_VARIABLE_UNIT, j.IS_TRADE_ITEM_A_VARIABLE_UNIT));
1629 x_single_row_attrs_rec.IS_PACK_MARKED_WITH_EXP_DATE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_PACK_MARKED_WITH_EXP_DATE, j.IS_PACK_MARKED_WITH_EXP_DATE));
1630 x_single_row_attrs_rec.IS_PACK_MARKED_WITH_GREEN_DOT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_PACK_MARKED_WITH_GREEN_DOT, j.IS_PACK_MARKED_WITH_GREEN_DOT));
1631 x_single_row_attrs_rec.IS_PACK_MARKED_WITH_INGRED := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_PACK_MARKED_WITH_INGRED, j.IS_PACK_MARKED_WITH_INGRED));
1632 x_single_row_attrs_rec.IS_PACKAGE_MARKED_AS_REC := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_PACKAGE_MARKED_AS_REC, j.IS_PACKAGE_MARKED_AS_REC));
1633 x_single_row_attrs_rec.IS_PACKAGE_MARKED_RET := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_PACKAGE_MARKED_RET, j.IS_PACKAGE_MARKED_RET));
1634 x_single_row_attrs_rec.STACKING_FACTOR := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.STACKING_FACTOR, j.STACKING_FACTOR));
1635 x_single_row_attrs_rec.STACKING_WEIGHT_MAXIMUM := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.STACKING_WEIGHT_MAXIMUM, j.STACKING_WEIGHT_MAXIMUM));
1636
1637 -- UOM:
1638 x_single_row_attrs_rec.UOM_STACKING_WEIGHT_MAXIMUM := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_STACKING_WEIGHT_MAXIMUM, j.UOM_STACKING_WEIGHT_MAXIMUM));
1639
1640 x_single_row_attrs_rec.ORDERING_LEAD_TIME := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.ORDERING_LEAD_TIME, j.ORDERING_LEAD_TIME));
1641 -- UOM:
1642 x_single_row_attrs_rec.UOM_ORDERING_LEAD_TIME := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_ORDERING_LEAD_TIME, j.UOM_ORDERING_LEAD_TIME));
1643
1644 x_single_row_attrs_rec.ORDER_QUANTITY_MAX := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.ORDER_QUANTITY_MAX, j.ORDER_QUANTITY_MAX));
1645 x_single_row_attrs_rec.ORDER_QUANTITY_MIN := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.ORDER_QUANTITY_MIN, j.ORDER_QUANTITY_MIN));
1646 x_single_row_attrs_rec.ORDER_QUANTITY_MULTIPLE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.ORDER_QUANTITY_MULTIPLE, j.ORDER_QUANTITY_MULTIPLE));
1647 x_single_row_attrs_rec.ORDER_SIZING_FACTOR := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.ORDER_SIZING_FACTOR, j.ORDER_SIZING_FACTOR));
1648 x_single_row_attrs_rec.EFFECTIVE_START_DATE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.EFFECTIVE_START_DATE, j.EFFECTIVE_START_DATE));
1649 x_single_row_attrs_rec.CATALOG_PRICE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.CATALOG_PRICE, j.CATALOG_PRICE));
1650 x_single_row_attrs_rec.EFFECTIVE_END_DATE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.EFFECTIVE_END_DATE, j.EFFECTIVE_END_DATE));
1651 x_single_row_attrs_rec.SUGGESTED_RETAIL_PRICE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.SUGGESTED_RETAIL_PRICE, j.SUGGESTED_RETAIL_PRICE));
1652 x_single_row_attrs_rec.MATERIAL_SAFETY_DATA_SHEET_NO := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.MATERIAL_SAFETY_DATA_SHEET_NO, j.MATERIAL_SAFETY_DATA_SHEET_NO));
1653 x_single_row_attrs_rec.HAS_BATCH_NUMBER := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.HAS_BATCH_NUMBER, j.HAS_BATCH_NUMBER));
1654 x_single_row_attrs_rec.IS_NON_SOLD_TRADE_RET_FLAG := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_NON_SOLD_TRADE_RET_FLAG, j.IS_NON_SOLD_TRADE_RET_FLAG));
1655 x_single_row_attrs_rec.IS_TRADE_ITEM_MAR_REC_FLAG := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_TRADE_ITEM_MAR_REC_FLAG, j.IS_TRADE_ITEM_MAR_REC_FLAG));
1656 x_single_row_attrs_rec.DIAMETER := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DIAMETER, j.DIAMETER));
1657 -- UOM:
1658 x_single_row_attrs_rec.UOM_DIAMETER := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_DIAMETER, j.UOM_DIAMETER));
1659
1660 x_single_row_attrs_rec.DRAINED_WEIGHT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DRAINED_WEIGHT, j.DRAINED_WEIGHT));
1661 -- UOM:
1662 x_single_row_attrs_rec.UOM_DRAINED_WEIGHT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_DRAINED_WEIGHT, j.UOM_DRAINED_WEIGHT));
1663
1664 x_single_row_attrs_rec.GENERIC_INGREDIENT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.GENERIC_INGREDIENT, j.GENERIC_INGREDIENT));
1665
1666 x_single_row_attrs_rec.GENERIC_INGREDIENT_STRGTH := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.GENERIC_INGREDIENT_STRGTH, j.GENERIC_INGREDIENT_STRGTH));
1667 -- UOM:
1668 x_single_row_attrs_rec.UOM_GENERIC_INGREDIENT_STRGTH := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_GENERIC_INGREDIENT_STRGTH, j.UOM_GENERIC_INGREDIENT_STRGTH));
1669
1670 x_single_row_attrs_rec.INGREDIENT_STRENGTH := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.INGREDIENT_STRENGTH, j.INGREDIENT_STRENGTH));
1671 x_single_row_attrs_rec.IS_NET_CONTENT_DEC_FLAG := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_NET_CONTENT_DEC_FLAG, j.IS_NET_CONTENT_DEC_FLAG));
1672 x_single_row_attrs_rec.NET_CONTENT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.NET_CONTENT, j.NET_CONTENT));
1673 -- UOM:
1674 x_single_row_attrs_rec.UOM_NET_CONTENT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_NET_CONTENT, j.UOM_NET_CONTENT));
1675
1676 x_single_row_attrs_rec.PEG_HORIZONTAL := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.PEG_HORIZONTAL, j.PEG_HORIZONTAL));
1677 -- UOM:
1678 x_single_row_attrs_rec.UOM_PEG_HORIZONTAL := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_PEG_HORIZONTAL, j.UOM_PEG_HORIZONTAL));
1679
1680 x_single_row_attrs_rec.PEG_VERTICAL := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.PEG_VERTICAL, j.PEG_VERTICAL));
1681 -- UOM:
1682 x_single_row_attrs_rec.UOM_PEG_VERTICAL := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_PEG_VERTICAL, j.UOM_PEG_VERTICAL));
1683
1684 x_single_row_attrs_rec.CONSUMER_AVAIL_DATE_TIME := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.CONSUMER_AVAIL_DATE_TIME, j.CONSUMER_AVAIL_DATE_TIME));
1685
1686 x_single_row_attrs_rec.DEL_TO_DIST_CNTR_TEMP_MAX := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DEL_TO_DIST_CNTR_TEMP_MAX, j.DEL_TO_DIST_CNTR_TEMP_MAX));
1687 -- UOM:
1688 x_single_row_attrs_rec.UOM_DEL_TO_DIST_CNTR_TEMP_MAX := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_DEL_TO_DIST_CNTR_TEMP_MAX, j.UOM_DEL_TO_DIST_CNTR_TEMP_MAX));
1689
1690 x_single_row_attrs_rec.DEL_TO_DIST_CNTR_TEMP_MIN := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DEL_TO_DIST_CNTR_TEMP_MIN, j.DEL_TO_DIST_CNTR_TEMP_MIN));
1691 -- UOM:
1692 x_single_row_attrs_rec.UOM_DEL_TO_DIST_CNTR_TEMP_MIN := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_DEL_TO_DIST_CNTR_TEMP_MIN, j.UOM_DEL_TO_DIST_CNTR_TEMP_MIN));
1693 x_single_row_attrs_rec.DELIVERY_TO_MRKT_TEMP_MAX := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DELIVERY_TO_MRKT_TEMP_MAX, j.DELIVERY_TO_MRKT_TEMP_MAX));
1694 -- UOM:
1695 x_single_row_attrs_rec.UOM_DELIVERY_TO_MRKT_TEMP_MAX := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_DELIVERY_TO_MRKT_TEMP_MAX, j.UOM_DELIVERY_TO_MRKT_TEMP_MAX));
1696
1697 x_single_row_attrs_rec.DELIVERY_TO_MRKT_TEMP_MIN := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DELIVERY_TO_MRKT_TEMP_MIN, j.DELIVERY_TO_MRKT_TEMP_MIN));
1698 -- UOM:
1699 x_single_row_attrs_rec.UOM_DELIVERY_TO_MRKT_TEMP_MIN := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_DELIVERY_TO_MRKT_TEMP_MIN, j.UOM_DELIVERY_TO_MRKT_TEMP_MIN));
1700
1701
1702 x_single_row_attrs_rec.SUB_BRAND := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.SUB_BRAND, j.SUB_BRAND));
1703 -- x_single_row_attrs_rec.TRADE_ITEM_DESCRIPTOR := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.TRADE_ITEM_DESCRIPTOR, j.TRADE_ITEM_DESCRIPTOR));
1704 x_single_row_attrs_rec.EANUCC_CODE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.EANUCC_CODE, j.EANUCC_CODE));
1705 x_single_row_attrs_rec.EANUCC_TYPE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.EANUCC_TYPE, j.EANUCC_TYPE));
1706 x_single_row_attrs_rec.RETAIL_PRICE_ON_TRADE_ITEM := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.RETAIL_PRICE_ON_TRADE_ITEM, j.RETAIL_PRICE_ON_TRADE_ITEM));
1707 x_single_row_attrs_rec.QUANTITY_OF_COMP_LAY_ITEM := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.QUANTITY_OF_COMP_LAY_ITEM, j.QUANTITY_OF_COMP_LAY_ITEM));
1708 x_single_row_attrs_rec.QUANITY_OF_ITEM_IN_LAYER := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.QUANITY_OF_ITEM_IN_LAYER, j.QUANITY_OF_ITEM_IN_LAYER));
1709 x_single_row_attrs_rec.QUANTITY_OF_ITEM_INNER_PACK := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.QUANTITY_OF_ITEM_INNER_PACK, j.QUANTITY_OF_ITEM_INNER_PACK));
1710 x_single_row_attrs_rec.QUANTITY_OF_INNER_PACK := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.QUANTITY_OF_INNER_PACK, j.QUANTITY_OF_INNER_PACK));
1711 x_single_row_attrs_rec.BRAND_OWNER_GLN := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.BRAND_OWNER_GLN, j.BRAND_OWNER_GLN));
1712 x_single_row_attrs_rec.BRAND_OWNER_NAME := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.BRAND_OWNER_NAME, j.BRAND_OWNER_NAME));
1713 x_single_row_attrs_rec.STORAGE_HANDLING_TEMP_MAX := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.STORAGE_HANDLING_TEMP_MAX, j.STORAGE_HANDLING_TEMP_MAX));
1714
1715 -- UOM:
1716 x_single_row_attrs_rec.UOM_STORAGE_HANDLING_TEMP_MAX := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_STORAGE_HANDLING_TEMP_MAX, j.UOM_STORAGE_HANDLING_TEMP_MAX));
1717
1718 x_single_row_attrs_rec.STORAGE_HANDLING_TEMP_MIN := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.STORAGE_HANDLING_TEMP_MIN, j.STORAGE_HANDLING_TEMP_MIN));
1719 -- UOM:
1720 x_single_row_attrs_rec.UOM_STORAGE_HANDLING_TEMP_MIN := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_STORAGE_HANDLING_TEMP_MIN, j.UOM_STORAGE_HANDLING_TEMP_MIN ));
1721
1722 x_single_row_attrs_rec.TRADE_ITEM_COUPON := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.TRADE_ITEM_COUPON, j.TRADE_ITEM_COUPON));
1723 x_single_row_attrs_rec.DEGREE_OF_ORIGINAL_WORT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DEGREE_OF_ORIGINAL_WORT, j.DEGREE_OF_ORIGINAL_WORT));
1724 x_single_row_attrs_rec.FAT_PERCENT_IN_DRY_MATTER := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.FAT_PERCENT_IN_DRY_MATTER, j.FAT_PERCENT_IN_DRY_MATTER));
1725 x_single_row_attrs_rec.PERCENT_OF_ALCOHOL_BY_VOL := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.PERCENT_OF_ALCOHOL_BY_VOL, j.PERCENT_OF_ALCOHOL_BY_VOL));
1726 x_single_row_attrs_rec.ISBN_NUMBER := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.ISBN_NUMBER, j.ISBN_NUMBER));
1727 x_single_row_attrs_rec.ISSN_NUMBER := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.ISSN_NUMBER, j.ISSN_NUMBER));
1728 x_single_row_attrs_rec.IS_INGREDIENT_IRRADIATED := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_INGREDIENT_IRRADIATED, j.IS_INGREDIENT_IRRADIATED));
1729 x_single_row_attrs_rec.IS_RAW_MATERIAL_IRRADIATED := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_RAW_MATERIAL_IRRADIATED, j.IS_RAW_MATERIAL_IRRADIATED));
1730 x_single_row_attrs_rec.IS_TRADE_ITEM_GENETICALLY_MOD := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_TRADE_ITEM_GENETICALLY_MOD, j.IS_TRADE_ITEM_GENETICALLY_MOD));
1731 x_single_row_attrs_rec.IS_TRADE_ITEM_IRRADIATED := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_TRADE_ITEM_IRRADIATED, j.IS_TRADE_ITEM_IRRADIATED));
1732 x_single_row_attrs_rec.SECURITY_TAG_LOCATION := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.SECURITY_TAG_LOCATION, j.SECURITY_TAG_LOCATION));
1733 x_single_row_attrs_rec.URL_FOR_WARRANTY := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.URL_FOR_WARRANTY, j.URL_FOR_WARRANTY));
1734 x_single_row_attrs_rec.NESTING_INCREMENT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.NESTING_INCREMENT, j.NESTING_INCREMENT));
1735 -- UOM:
1736 x_single_row_attrs_rec.UOM_NESTING_INCREMENT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_NESTING_INCREMENT, j.UOM_NESTING_INCREMENT));
1737
1738 x_single_row_attrs_rec.IS_TRADE_ITEM_RECALLED := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_TRADE_ITEM_RECALLED, j.IS_TRADE_ITEM_RECALLED));
1739 x_single_row_attrs_rec.MODEL_NUMBER := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.MODEL_NUMBER, j.MODEL_NUMBER));
1740 x_single_row_attrs_rec.PIECES_PER_TRADE_ITEM := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.PIECES_PER_TRADE_ITEM, j.PIECES_PER_TRADE_ITEM));
1741 -- UOM:
1742 x_single_row_attrs_rec.UOM_PIECES_PER_TRADE_ITEM := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.UOM_PIECES_PER_TRADE_ITEM, j.UOM_PIECES_PER_TRADE_ITEM));
1743
1744 x_single_row_attrs_rec.DEPT_OF_TRNSPRT_DANG_GOODS_NUM := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DEPT_OF_TRNSPRT_DANG_GOODS_NUM, j.DEPT_OF_TRNSPRT_DANG_GOODS_NUM));
1745 x_single_row_attrs_rec.RETURN_GOODS_POLICY := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.RETURN_GOODS_POLICY, j.RETURN_GOODS_POLICY));
1746 x_single_row_attrs_rec.IS_OUT_OF_BOX_PROVIDED := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_OUT_OF_BOX_PROVIDED, j.IS_OUT_OF_BOX_PROVIDED));
1747 x_single_row_attrs_rec.INVOICE_NAME := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.INVOICE_NAME, j.INVOICE_NAME));
1748 x_single_row_attrs_rec.DESCRIPTIVE_SIZE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DESCRIPTIVE_SIZE, j.DESCRIPTIVE_SIZE));
1749 x_single_row_attrs_rec.FUNCTIONAL_NAME := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.FUNCTIONAL_NAME, j.FUNCTIONAL_NAME));
1750 x_single_row_attrs_rec.TRADE_ITEM_FORM_DESCRIPTION := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.TRADE_ITEM_FORM_DESCRIPTION, j.TRADE_ITEM_FORM_DESCRIPTION));
1751 x_single_row_attrs_rec.WARRANTY_DESCRIPTION := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.WARRANTY_DESCRIPTION, j.WARRANTY_DESCRIPTION));
1752 x_single_row_attrs_rec.TRADE_ITEM_FINISH_DESCRIPTION := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.TRADE_ITEM_FINISH_DESCRIPTION, j.TRADE_ITEM_FINISH_DESCRIPTION));
1753 x_single_row_attrs_rec.DESCRIPTION_SHORT := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.DESCRIPTION_SHORT, j.DESCRIPTION_SHORT));
1754 x_single_row_attrs_rec.IS_BARCODE_SYMBOLOGY_DERIVABLE := Get_Nulled_out_Value(NVL(x_single_row_attrs_rec.IS_BARCODE_SYMBOLOGY_DERIVABLE, j.IS_BARCODE_SYMBOLOGY_DERIVABLE));
1755
1756 Write_Debug('After Merging Single row . . .');
1757
1758
1759 END LOOP ; -- end loop single row attributes
1760
1761
1762
1763 Write_Debug('Merging Multi-Row Attrs row. . . ');
1764
1765
1766
1767 IF ( x_multi_row_attrs_tbl IS NULL OR x_multi_row_attrs_tbl.COUNT = 0 )
1768 THEN
1769 Write_Debug('Merging target row tbl is empty . . . ');
1770 l_merge_target_tbl_empty := TRUE ;
1771 END IF ;
1772
1773 k := 0;
1774 FOR j IN c_pending_multi_row_attrs(p_inventory_item_id
1775 , p_organization_id, p_change_id, p_change_line_id, p_acd_type)
1776 LOOP
1777
1778 IF l_merge_target_tbl_empty
1779 THEN
1780 k := k+1 ;
1781 ELSE
1782
1783 -- MK. NEED TO WORK ON THIS PROCEDURE
1784 -- Need to modify following logic using meta data
1785 -- Need to modify UOM logic, if base value is miss value, we may need to set UOM value as null
1786 -- Need to identify the record using pk values derived from meta data
1787 -- Don't do hard-coding for column name etc not to have any dependency
1788 -- Find exsting pending change records for this attribute group rec
1789 l_found := FALSE ;
1790
1791 END IF ;
1792
1793
1794 x_multi_row_attrs_tbl(k).LANGUAGE_CODE := USERENV('LANG') ;
1795 x_multi_row_attrs_tbl(k).MANUFACTURER_GLN := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).MANUFACTURER_GLN, j.MANUFACTURER_GLN)) ;
1796 x_multi_row_attrs_tbl(k).MANUFACTURER_ID := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).MANUFACTURER_ID, j.MANUFACTURER_ID)) ;
1797 x_multi_row_attrs_tbl(k).BAR_CODE_TYPE := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).BAR_CODE_TYPE, j.BAR_CODE_TYPE)) ;
1798 x_multi_row_attrs_tbl(k).COLOR_CODE_LIST_AGENCY := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).COLOR_CODE_LIST_AGENCY, j.COLOR_CODE_LIST_AGENCY)) ;
1799 x_multi_row_attrs_tbl(k).COLOR_CODE_VALUE := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).COLOR_CODE_VALUE, j.COLOR_CODE_VALUE)) ;
1800 x_multi_row_attrs_tbl(k).CLASS_OF_DANGEROUS_CODE := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).CLASS_OF_DANGEROUS_CODE, j.CLASS_OF_DANGEROUS_CODE)) ;
1801 x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_MARGIN_NUMBER := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_MARGIN_NUMBER, j.DANGEROUS_GOODS_MARGIN_NUMBER)) ;
1802 x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_HAZARDOUS_CODE := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_HAZARDOUS_CODE, j.DANGEROUS_GOODS_HAZARDOUS_CODE)) ;
1803 x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_PACK_GROUP := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_PACK_GROUP, j.DANGEROUS_GOODS_PACK_GROUP)) ;
1804 x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_REG_CODE := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_REG_CODE, j.DANGEROUS_GOODS_REG_CODE)) ;
1805 x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_SHIPPING_NAME := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_SHIPPING_NAME, j.DANGEROUS_GOODS_SHIPPING_NAME)) ;
1806 x_multi_row_attrs_tbl(k).UNITED_NATIONS_DANG_GOODS_NO := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).UNITED_NATIONS_DANG_GOODS_NO, j.UNITED_NATIONS_DANG_GOODS_NO)) ;
1807 x_multi_row_attrs_tbl(k).FLASH_POINT_TEMP := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).FLASH_POINT_TEMP, j.FLASH_POINT_TEMP)) ;
1808
1809 IF x_multi_row_attrs_tbl(k).FLASH_POINT_TEMP IS NOT NULL THEN
1810 x_multi_row_attrs_tbl(k).UOM_FLASH_POINT_TEMP := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).UOM_FLASH_POINT_TEMP, j.UOM_FLASH_POINT_TEMP)) ;
1811 ELSE
1812 x_multi_row_attrs_tbl(k).UOM_FLASH_POINT_TEMP := NULL;
1813 END IF ;
1814
1815 x_multi_row_attrs_tbl(k).COUNTRY_OF_ORIGIN := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).COUNTRY_OF_ORIGIN, j.COUNTRY_OF_ORIGIN)) ;
1816 x_multi_row_attrs_tbl(k).HARMONIZED_TARIFF_SYS_ID_CODE := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).HARMONIZED_TARIFF_SYS_ID_CODE, j.HARMONIZED_TARIFF_SYS_ID_CODE)) ;
1817 x_multi_row_attrs_tbl(k).SIZE_CODE_LIST_AGENCY := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).SIZE_CODE_LIST_AGENCY, j.SIZE_CODE_LIST_AGENCY)) ;
1818 x_multi_row_attrs_tbl(k).SIZE_CODE_VALUE := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).SIZE_CODE_VALUE, j.SIZE_CODE_VALUE)) ;
1819 x_multi_row_attrs_tbl(k).HANDLING_INSTRUCTIONS_CODE := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).HANDLING_INSTRUCTIONS_CODE, j.HANDLING_INSTRUCTIONS_CODE)) ;
1820 x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_TECHNICAL_NAME := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).DANGEROUS_GOODS_TECHNICAL_NAME, j.DANGEROUS_GOODS_TECHNICAL_NAME)) ;
1821 x_multi_row_attrs_tbl(k).DELIVERY_METHOD_INDICATOR := Get_Nulled_out_Value(NVL(x_multi_row_attrs_tbl(k).DELIVERY_METHOD_INDICATOR, j.DELIVERY_METHOD_INDICATOR)) ;
1822
1823 x_multi_row_attrs_tbl(k).EXTENSION_ID := NVL(x_multi_row_attrs_tbl(k).EXTENSION_ID, j.EXTENSION_ID);
1824 x_multi_row_attrs_tbl(k).TRANSACTION_TYPE := NVL(x_multi_row_attrs_tbl(k).TRANSACTION_TYPE, j.TRANSACTION_TYPE);
1825
1826
1827 END LOOP; -- end loop multi row attributes
1828
1829 --Setting the value for x_extra_attrs_rec
1830 --Logic needs to be added to work as following:
1831 --(a) unit_weight is present in pending table with value Null-Out Value -> EGO_ITEM_PUB.G_INTF_NULL_NUM
1832 --(b) unit_weight is not present in pending table -> NULL
1833 --(c) unit_weight is present in pending table with non-null value -> pass the value present in the table.
1834
1835 x_extra_attrs_rec.UNIT_WEIGHT := NULL ;
1836 FOR k IN c_pending_gdsn_extra_row_attrs(p_inventory_item_id, p_organization_id, p_change_id, p_change_line_id, p_acd_type)
1837 LOOP
1838 Write_Debug('Merging Extra Row . . .') ;
1839
1840 IF k.UNIT_WEIGHT = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_NUM
1841 THEN
1842 x_extra_attrs_rec.UNIT_WEIGHT := EGO_ITEM_PUB.G_INTF_NULL_NUM ;
1843 ELSE
1844 x_extra_attrs_rec.UNIT_WEIGHT := k.UNIT_WEIGHT ;
1845 END IF ;
1846
1847 Write_Debug('Extra Attr UNIT_WEIGHT: ' || k.UNIT_WEIGHT ) ;
1848
1849 END LOOP ;
1850
1851
1852
1853 END MERGE_GDSN_PENDING_CHG_ROWS;
1854
1855
1856
1857 PROCEDURE VALIDATE_GDSN_ATTR_CHGS
1858 ( p_api_version IN NUMBER
1859 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
1860 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
1861 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
1862 , x_return_status OUT NOCOPY VARCHAR2
1863 , x_msg_count OUT NOCOPY NUMBER
1864 , x_msg_data OUT NOCOPY VARCHAR2
1865 , p_write_msg_to_intftbl IN VARCHAR2 := NULL -- FND_API.G_FALSE
1866 , p_api_caller IN VARCHAR2 := NULL
1867 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
1868 , p_output_dir IN VARCHAR2 := NULL
1869 , p_debug_filename IN VARCHAR2 := NULL
1870 , p_batch_id IN NUMBER
1871 , p_cm_process_type IN VARCHAR2 := NULL
1872 )
1873 IS
1874
1875 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_GDSN_ATTR_CHGS';
1876 l_api_version CONSTANT NUMBER := 1.0;
1877
1878 l_init_msg_list VARCHAR2(1) ;
1879 l_validation_level NUMBER ;
1880 l_commit VARCHAR2(1) ;
1881 l_write_msg_to_intftbl VARCHAR2(1) ;
1882
1883 l_return_status VARCHAR2(1);
1884 l_msg_count NUMBER;
1885 l_msg_data VARCHAR2(4000);
1886 l_msg_text VARCHAR2(4000);
1887 l_acd_type VARCHAR2(30) ;
1888
1889 l_single_row_attrs EGO_ITEM_PUB.UCCNET_ATTRS_SINGL_ROW_REC_TYP ;
1890 l_multi_row_attrs EGO_ITEM_PUB.UCCNET_ATTRS_MULTI_ROW_TBL_TYP ;
1891 l_extra_attrs_rec EGO_ITEM_PUB.UCCNET_EXTRA_ATTRS_REC_TYP;
1892
1893 CURSOR c_intf_rows IS
1894 SELECT
1895 INVENTORY_ITEM_ID
1896 ,ORGANIZATION_ID
1897 ,CHANGE_ID
1898 ,CHANGE_LINE_ID
1899 ,TRANSACTION_TYPE
1900 ,MAX(TRANSACTION_ID) AS TRANSACTION_ID
1901 FROM EGO_ITM_USR_ATTR_INTRFC
1902 WHERE ATTR_GROUP_TYPE IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
1903 AND DATA_SET_ID = p_batch_id
1904 AND PROCESS_STATUS = G_CM_TO_BE_PROCESSED
1905 AND CHANGE_ID IS NOT NULL
1906 AND CHANGE_LINE_ID IS NOT NULL
1907 GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID,CHANGE_ID, CHANGE_LINE_ID, TRANSACTION_TYPE ;
1908
1909
1910 BEGIN
1911
1912 -- Standard Start of API savepoint
1913 SAVEPOINT VALIDATE_GDSN_ATTR_CHGS;
1914
1915 -- Standard call to check for call compatibility.
1916 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1917 p_api_version ,
1918 l_api_name ,
1919 G_PKG_NAME )
1920 THEN
1921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1922 END IF;
1923
1924
1925 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
1926 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
1927 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
1928 l_write_msg_to_intftbl := NVL(p_write_msg_to_intftbl,FND_API.G_FALSE) ;
1929
1930
1931 -- Initialize message list if l_init_msg_list is set to TRUE.
1932 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1933 FND_MSG_PUB.initialize;
1934 END IF;
1935
1936 -- Open Debug Session by a give param or profile option.
1937 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1938
1939 Write_Debug('After Open_Debug_Session');
1940 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
1941 Write_Debug('-----------------------------------------' );
1942 Write_Debug('p_api_version: ' || to_char(p_api_version));
1943 Write_Debug('p_init_msg_list:' || p_init_msg_list);
1944 Write_Debug('p_commit:' || p_commit);
1945 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
1946 Write_Debug('p_write_msg_to_intftbl:' || p_write_msg_to_intftbl);
1947 Write_Debug('p_api_caller:' || p_api_caller);
1948 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
1949 Write_Debug('-----------------------------------------' );
1950
1951 -- Initialize API return status to success
1952 x_return_status := G_RET_STS_SUCCESS;
1953
1954
1955 -- API body
1956 -- Logic Here
1957 FOR i IN c_intf_rows LOOP
1958
1959 Write_Debug('Populating local array for Item, Org='||i.INVENTORY_ITEM_ID||','||i.ORGANIZATION_ID);
1960 Write_Debug('EGO_GTIN_ATTRS_PVT.Get_Gdsn_Intf_Rows. . . : ' || to_char(p_batch_id));
1961
1962
1963 l_return_status := NULL;
1964 l_msg_count := NULL;
1965 l_msg_data := NULL;
1966 l_single_row_attrs := NULL ;
1967 l_multi_row_attrs.DELETE ;
1968 l_extra_attrs_rec := NULL;
1969
1970
1971 BEGIN
1972
1973 EGO_GTIN_ATTRS_PVT.Get_Gdsn_Intf_Rows
1974 ( p_data_set_id => p_batch_id
1975 , p_target_proc_status => G_CM_TO_BE_PROCESSED
1976 , p_inventory_item_id => i.INVENTORY_ITEM_ID
1977 , p_organization_id => i.ORGANIZATION_ID
1978 , x_singe_row_attrs_rec => l_single_row_attrs
1979 , x_multi_row_attrs_tbl => l_multi_row_attrs
1980 , x_return_status => l_return_status
1981 , x_msg_count => l_msg_count
1982 , x_msg_data => l_msg_data
1983 ) ;
1984
1985 EXCEPTION
1986
1987 WHEN OTHERS THEN
1988 Write_Debug('While calling Validation API EGO_GTIN_ATTRS_PVT.Validate_Attributes: ' || to_char(p_batch_id));
1989 Write_Debug('Error:' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)) ;
1990
1991 FND_MSG_PUB.Add_Exc_Msg
1992 ( 'EGO_GTIN_ATTRS_PVT',
1993 'Get_Gdsn_Intf_Rows' ,
1994 SQLERRM ) ;
1995
1996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1997
1998
1999 END ;
2000
2001
2002 Write_Debug('After EGO_GTIN_ATTRS_PVT.Get_Gdsn_Intf_Rows: ' || l_return_status);
2003
2004 IF l_return_status <> G_RET_STS_SUCCESS THEN
2005
2006 x_return_status := G_RET_STS_WARNING ;
2007 UPDATE EGO_ITM_USR_ATTR_INTRFC
2008 SET PROCESS_STATUS = G_PS_ERROR
2009 WHERE DATA_SET_ID = p_batch_id
2010 AND ATTR_GROUP_TYPE IN (G_EGO_ITEM_GTIN_ATTRS , G_EGO_ITEM_GTIN_MULTI_ATTRS)
2011 AND INVENTORY_ITEM_ID = i.INVENTORY_ITEM_ID
2012 AND ORGANIZATION_ID = i.ORGANIZATION_ID
2013 AND CHANGE_ID = i.CHANGE_ID
2014 AND CHANGE_LINE_ID = i.CHANGE_LINE_ID;
2015
2016 Write_Debug('Marked Item as error in Interface table');
2017
2018 IF l_msg_count > 0 AND l_return_status <> G_RET_STS_UNEXP_ERROR THEN
2019 FOR cnt IN 1..l_msg_count LOOP
2020 Write_Debug('Error msg - '||cnt ||': '|| FND_MSG_PUB.Get(p_msg_index => cnt, p_encoded => 'F'));
2021
2022 l_msg_text := FND_MSG_PUB.Get(p_msg_index => cnt, p_encoded => 'F');
2023 IF FND_API.to_Boolean(l_write_msg_to_intftbl)
2024 THEN
2025
2026 Insert_Mtl_Intf_Err
2027 ( p_transaction_id => i.TRANSACTION_ID
2028 , p_bo_identifier => G_BO_IDENTIFIER
2029 , p_error_entity_code => G_GDSN_ATTR_ENTITY
2030 , p_error_table_name => G_ITEM_USR_ATTR_INTF
2031 , p_error_column_name => NULL
2032 , p_error_msg => l_msg_text
2033 , p_error_msg_type => G_ENG_MSG_TYPE_ERROR
2034 , p_error_msg_name => null
2035 ) ;
2036
2037 END IF ;
2038
2039 END LOOP;
2040
2041 ELSIF l_msg_count > 0 AND l_return_status = G_RET_STS_UNEXP_ERROR THEN
2042
2043 Write_Debug('Unexpected Error msg - '|| l_msg_data);
2044
2045 l_msg_text := l_msg_data;
2046 IF FND_API.to_Boolean(l_write_msg_to_intftbl)
2047 THEN
2048
2049 Insert_Mtl_Intf_Err
2050 ( p_transaction_id => i.TRANSACTION_ID
2051 , p_bo_identifier => G_BO_IDENTIFIER
2052 , p_error_entity_code => G_GDSN_ATTR_ENTITY
2053 , p_error_table_name => G_ITEM_USR_ATTR_INTF
2054 , p_error_column_name => NULL
2055 , p_error_msg => l_msg_text
2056 , p_error_msg_type => G_ENG_MSG_TYPE_ERROR
2057 , p_error_msg_name => null
2058 ) ;
2059
2060 END IF ;
2061
2062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2063
2064
2065 END IF; -- IF l_msg_count
2066
2067 END IF; -- IF l_return_status <> 'S
2068
2069 l_acd_type := Convert_TxType_To_AcdType(p_tx_type => i.TRANSACTION_TYPE) ;
2070
2071 Write_Debug('Merging existing pending change data for ' || 'CO:' || to_char(i.CHANGE_ID)
2072 || '-Line:' || to_char(i.CHANGE_LINE_ID)
2073 || '-AcdType:' || l_acd_type
2074 );
2075
2076 MERGE_GDSN_PENDING_CHG_ROWS( p_inventory_item_id => i.INVENTORY_ITEM_ID
2077 , p_organization_id => i.ORGANIZATION_ID
2078 , p_change_id => i.CHANGE_ID
2079 , p_change_line_id => i.CHANGE_LINE_ID
2080 , p_acd_type => l_acd_type
2081 , x_single_row_attrs_rec => l_single_row_attrs
2082 , x_multi_row_attrs_tbl => l_multi_row_attrs
2083 , x_extra_attrs_rec => l_extra_attrs_rec
2084 ) ;
2085
2086 Write_Debug('After Merging existing pending change data') ;
2087
2088 BEGIN
2089
2090 Write_Debug('Calling Validation API EGO_GTIN_ATTRS_PVT.Validate_Attributes: ' || to_char(p_batch_id));
2091 EGO_GTIN_ATTRS_PVT.Validate_Attributes(
2092 p_inventory_item_id => i.INVENTORY_ITEM_ID
2093 ,p_organization_id => i.ORGANIZATION_ID
2094 ,p_singe_row_attrs_rec => l_single_row_attrs
2095 ,p_multi_row_attrs_tbl => l_multi_row_attrs
2096 ,p_extra_attrs_rec => l_extra_attrs_rec
2097 ,x_return_status => l_return_status
2098 ,x_msg_count => l_msg_count
2099 ,x_msg_data => l_msg_data
2100 );
2101
2102 EXCEPTION
2103
2104 WHEN OTHERS THEN
2105 Write_Debug('While calling Validation API EGO_GTIN_ATTRS_PVT.Validate_Attributes: ' || to_char(p_batch_id));
2106 Write_Debug('Error:' || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)) ;
2107
2108 FND_MSG_PUB.Add_Exc_Msg
2109 ( 'EGO_GTIN_ATTRS_PVT',
2110 'Validate_Attributes' ,
2111 SQLERRM ) ;
2112
2113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2114
2115
2116 END ;
2117
2118 Write_Debug('After calling Validation API EGO_GTIN_ATTRS_PVT.Validate_Attributes l_return_status, l_msg_count='||l_return_status||','||l_msg_count);
2119
2120 IF l_return_status <> G_RET_STS_SUCCESS THEN
2121
2122 x_return_status := G_RET_STS_WARNING ;
2123 UPDATE EGO_ITM_USR_ATTR_INTRFC
2124 SET PROCESS_STATUS = G_PS_ERROR
2125 WHERE DATA_SET_ID = p_batch_id
2126 AND ATTR_GROUP_TYPE IN (G_EGO_ITEM_GTIN_ATTRS , G_EGO_ITEM_GTIN_MULTI_ATTRS)
2127 AND INVENTORY_ITEM_ID = i.INVENTORY_ITEM_ID
2128 AND ORGANIZATION_ID = i.ORGANIZATION_ID
2129 AND CHANGE_ID = i.CHANGE_ID
2130 AND CHANGE_LINE_ID = i.CHANGE_LINE_ID;
2131
2132 Write_Debug('Marked Item as error in Interface table');
2133
2134
2135 IF l_msg_count > 0 AND l_return_status <> G_RET_STS_UNEXP_ERROR THEN
2136 FOR cnt IN 1..l_msg_count LOOP
2137 Write_Debug('Error msg - '||cnt ||': '|| FND_MSG_PUB.Get(p_msg_index => cnt, p_encoded => 'F'));
2138
2139 l_msg_text := FND_MSG_PUB.Get(p_msg_index => cnt, p_encoded => 'F');
2140
2141 IF FND_API.to_Boolean(l_write_msg_to_intftbl)
2142 THEN
2143
2144 Insert_Mtl_Intf_Err
2145 ( p_transaction_id => i.TRANSACTION_ID
2146 , p_bo_identifier => G_BO_IDENTIFIER
2147 , p_error_entity_code => G_GDSN_ATTR_ENTITY
2148 , p_error_table_name => G_ITEM_USR_ATTR_INTF
2149 , p_error_column_name => NULL
2150 , p_error_msg => l_msg_text
2151 , p_error_msg_type => G_ENG_MSG_TYPE_ERROR
2152 , p_error_msg_name => null
2153 ) ;
2154
2155 END IF ;
2156
2157 END LOOP;
2158
2159 ELSIF l_msg_count > 0 AND l_return_status = G_RET_STS_UNEXP_ERROR THEN
2160
2161 Write_Debug('Unexpected Error msg - '|| l_msg_data);
2162
2163 l_msg_text := l_msg_data;
2164 IF FND_API.to_Boolean(l_write_msg_to_intftbl)
2165 THEN
2166
2167 Insert_Mtl_Intf_Err
2168 ( p_transaction_id => i.TRANSACTION_ID
2169 , p_bo_identifier => G_BO_IDENTIFIER
2170 , p_error_entity_code => G_GDSN_ATTR_ENTITY
2171 , p_error_table_name => G_ITEM_USR_ATTR_INTF
2172 , p_error_column_name => NULL
2173 , p_error_msg => l_msg_text
2174 , p_error_msg_type => G_ENG_MSG_TYPE_ERROR
2175 , p_error_msg_name => null
2176 ) ;
2177
2178 END IF ;
2179
2180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2181
2182
2183 END IF; -- IF l_msg_count
2184
2185 END IF; -- IF l_return_status <> 'S
2186 END LOOP; -- end loop intf_rows
2187
2188
2189 -- End of API body.
2190
2191
2192 -- Standard check of l_commit.
2193 IF FND_API.To_Boolean( l_commit ) THEN
2194 COMMIT WORK;
2195 END IF;
2196
2197 -- Standard call to get message count and if count is 1, get message info.
2198 FND_MSG_PUB.Count_And_Get
2199 ( p_count => x_msg_count ,
2200 p_data => x_msg_data
2201 );
2202
2203
2204
2205 -----------------------------------------------------
2206 -- Close Error Handler Debug Session.
2207 -----------------------------------------------------
2208 Close_Debug_Session;
2209
2210
2211 EXCEPTION
2212 WHEN FND_API.G_EXC_ERROR THEN
2213 ROLLBACK TO VALIDATE_GDSN_ATTR_CHGS;
2214 x_return_status := G_RET_STS_ERROR ;
2215 FND_MSG_PUB.Count_And_Get
2216 ( p_count => x_msg_count ,
2217 p_data => x_msg_data
2218 );
2219
2220
2221 Close_Debug_Session;
2222
2223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2224 ROLLBACK TO VALIDATE_GDSN_ATTR_CHGS;
2225 x_return_status := G_RET_STS_UNEXP_ERROR ;
2226 FND_MSG_PUB.Count_And_Get
2227 ( p_count => x_msg_count ,
2228 p_data => x_msg_data
2229 );
2230
2231
2232 Close_Debug_Session;
2233
2234 WHEN OTHERS THEN
2235 ROLLBACK TO VALIDATE_GDSN_ATTR_CHGS;
2236 x_return_status := G_RET_STS_UNEXP_ERROR ;
2237
2238 IF FND_MSG_PUB.Check_Msg_Level
2239 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2240 THEN
2241 FND_MSG_PUB.Add_Exc_Msg
2242 ( G_PKG_NAME ,
2243 l_api_name
2244 );
2245 END IF;
2246
2247 FND_MSG_PUB.Count_And_Get
2248 ( p_count => x_msg_count ,
2249 p_data => x_msg_data
2250 );
2251
2252 Close_Debug_Session;
2253
2254 END VALIDATE_GDSN_ATTR_CHGS ;
2255
2256
2257
2258 /********************************************************************
2259 * API Type : Derive and Populate Values APIs
2260 * Purpose : Perform Deriving and Populating values to Interface table
2261 *********************************************************************/
2262
2263 -- API name : POPULATE_MFGPARTNUM_INTF
2264 -- Type : Public
2265 -- Pre-reqs : None.
2266 -- Function : Create and Start Workflow Process
2267 -- Parameters :
2268 -- IN : p_api_version IN NUMBER Required
2269 -- p_init_msg_list IN VARCHAR2 Optional
2270 -- Default = NULL
2271 -- FND_API.G_FALSE
2272 -- FND_API.G_TRUE
2273 -- p_commit IN VARCHAR2 Optional
2274 -- Default = NULL
2275 -- FND_API.G_FALSE
2276 -- FND_API.G_TRUE
2277 -- p_validation_level IN NUMBER Optional
2278 -- Default = NULL
2279 -- Values:
2280 -- FND_API.G_VALID_LEVEL_NONE 0
2281 -- FND_API.G_VALID_LEVEL_FULL 100
2282 -- p_write_msg_to_intftbl IN VARCHAR2 Optional
2283 -- Default = NULL
2284 -- FND_API.G_FALSE
2285 -- FND_API.G_TRUE
2286 -- p_api_caller IN VARCHAR2 Optional
2287 --
2288 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
2289 -- x_msg_count OUT NOCOPY NUMBER
2290 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
2291 -- Version : Current version 1.0 Initial Creation
2292 -- Initial version 1.0
2293 --
2294 -- Notes : Note text-- API name : POPULATE_MFGPARTNUM_INTF
2295 PROCEDURE POPULATE_MFGPARTNUM_INTF
2296 ( p_api_version IN NUMBER
2297 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
2298 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
2299 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
2300 , x_return_status OUT NOCOPY VARCHAR2
2301 , x_msg_count OUT NOCOPY NUMBER
2302 , x_msg_data OUT NOCOPY VARCHAR2
2303 , p_write_msg_to_intftbl IN VARCHAR2 := NULL -- FND_API.G_FALSE
2304 , p_api_caller IN VARCHAR2 := NULL
2305 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
2306 , p_output_dir IN VARCHAR2 := NULL
2307 , p_debug_filename IN VARCHAR2 := NULL
2308 , p_batch_id IN NUMBER
2309 )
2310 IS
2311
2312
2313 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_MFGPARTNUM_INTF';
2314 l_api_version CONSTANT NUMBER := 1.0;
2315
2316 l_init_msg_list VARCHAR2(1) ;
2317 l_validation_level NUMBER ;
2318 l_commit VARCHAR2(1) ;
2319 l_write_msg_to_intftbl VARCHAR2(1) ;
2320
2321 NO_ROWS_IN_INTF_TABLE EXCEPTION;
2322
2323 l_msg_data VARCHAR2(4000);
2324 l_msg_count NUMBER;
2325 l_return_status VARCHAR2(1);
2326
2327
2328 BEGIN
2329
2330 -- Standard Start of API savepoint
2331 SAVEPOINT POPULATE_MFGPARTNUM_INTF;
2332
2333 -- Standard call to check for call compatibility.
2334 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2335 p_api_version ,
2336 l_api_name ,
2337 G_PKG_NAME )
2338 THEN
2339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2340 END IF;
2341
2342 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
2343 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
2344 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
2345 l_write_msg_to_intftbl := NVL(p_write_msg_to_intftbl,FND_API.G_FALSE) ;
2346
2347
2348 -- Initialize message list if l_init_msg_list is set to TRUE.
2349 IF FND_API.to_Boolean( l_init_msg_list ) THEN
2350 FND_MSG_PUB.initialize;
2351 END IF;
2352
2353 -- Open Debug Session by a give param or profile option.
2354 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
2355
2356 Write_Debug('After Open_Debug_Session');
2357 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
2358 Write_Debug('-----------------------------------------' );
2359 Write_Debug('p_api_version: ' || to_char(p_api_version));
2360 Write_Debug('p_init_msg_list:' || p_init_msg_list);
2361 Write_Debug('p_commit:' || p_commit);
2362 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
2363 Write_Debug('p_write_msg_to_intftbl:' || p_write_msg_to_intftbl);
2364 Write_Debug('p_api_caller:' || p_api_caller);
2365 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
2366 Write_Debug('-----------------------------------------' );
2367
2368 -- Initialize API return status to success
2369 x_return_status := G_RET_STS_SUCCESS;
2370
2371 -- API body
2372 -- Logic Here
2373 -- Init Local Vars
2374 -- Init Index Text Item Attributes
2375 IF (p_batch_id IS NULL) THEN
2376 fnd_message.set_name('EGO', 'EGO_DATA_SET_ID');
2377 l_msg_data := fnd_msg_pub.get();
2378 fnd_message.set_name('EGO','EGO_PKG_MAND_VALUES_MISS1');
2379 fnd_message.set_token('PACKAGE', G_PKG_NAME ||'.'|| l_api_name);
2380 fnd_message.set_token('VALUE', l_msg_data);
2381 x_msg_data := fnd_message.get();
2382 x_msg_count := 1;
2383 x_return_status := G_RET_STS_ERROR;
2384 RETURN;
2385 END IF;
2386
2387
2388 BEGIN
2389 SELECT 'S' INTO l_return_status
2390 FROM EGO_AML_INTF
2391 WHERE DATA_SET_ID = p_batch_id
2392 AND PROCESS_FLAG = G_CM_TO_BE_PROCESSED
2393 AND ROWNUM = 1;
2394 EXCEPTION
2395 WHEN NO_DATA_FOUND THEN
2396 fnd_message.set_name('EGO','EGO_IPI_NO_LINES');
2397 x_msg_count := 1;
2398 x_msg_data := fnd_message.get();
2399 x_return_status := G_RET_STS_SUCCESS;
2400 RAISE NO_ROWS_IN_INTF_TABLE;
2401 END;
2402
2403
2404 -- Pre-Processing for Pending Change Data Poplulation
2405 UPDATE ego_aml_intf intf
2406 SET intf.process_flag = G_CM_DATA_POPULATION
2407 WHERE intf.data_set_id = p_batch_id
2408 AND intf.process_flag = G_CM_TO_BE_PROCESSED
2409 AND EXISTS ( SELECT 'x'
2410 FROM EGO_MFG_PART_NUM_CHGS pending_change2
2411 WHERE intf.inventory_item_id = pending_change2.inventory_item_id
2412 AND intf.organization_id = pending_change2.organization_id
2413 AND intf.manufacturer_id = pending_change2.manufacturer_id
2414 AND intf.mfg_part_num = pending_change2.mfg_part_num
2415 AND intf.change_line_id = pending_change2.change_line_id
2416 AND intf.transaction_type = DECODE(pending_change2.ACD_TYPE
2417 , 'ADD', 'CREATE'
2418 , 'CHANGE', 'UPDATE'
2419 , 'DELETE', 'DELETE', 'INVALID')
2420 ) ;
2421
2422
2423
2424
2425
2426 Write_Debug('Before calling EGO_ITEM_AML_GRP.Populate_Intf_With_Proddata');
2427
2428
2429 EGO_ITEM_AML_GRP.Populate_Intf_With_Proddata (
2430 p_api_version => 1.0
2431 ,p_commit => FND_API.G_FALSE
2432 ,p_data_set_id => p_batch_id
2433 ,p_pf_to_process => G_CM_TO_BE_PROCESSED -- p_pf_to_process
2434 ,p_pf_after_population => G_CM_TO_BE_PROCESSED -- p_pf_after_population
2435 ,x_return_status => l_return_status
2436 ,x_msg_count => l_msg_count
2437 ,x_msg_data => l_msg_data
2438 ) ;
2439
2440 Write_Debug('After calling EGO_ITEM_AML_GRP.Populate_Intf_With_Proddata: Return Status: ' || l_return_status );
2441
2442
2443 IF l_return_status <> G_RET_STS_SUCCESS
2444 THEN
2445
2446 x_return_status := l_return_status ;
2447 x_msg_count := l_msg_count ;
2448 x_msg_data := l_msg_data ;
2449
2450 RAISE FND_API.G_EXC_ERROR ;
2451 END IF ;
2452
2453
2454
2455 Write_Debug('Populate intf table with prod data for UPDATE done ' );
2456
2457
2458 UPDATE ego_aml_intf intf
2459 SET (intf.mrp_planning_code
2460 ,intf.description
2461 ,intf.first_article_status
2462 ,intf.approval_status
2463 ,intf.start_date
2464 ,intf.end_date
2465 ,intf.process_flag
2466 -- ,attribute_category
2467 -- ,attribute1
2468 -- ,attribute2
2469 -- ,attribute3
2470 -- ,attribute4
2471 -- ,attribute5
2472 -- ,attribute6
2473 -- ,attribute7
2474 -- ,attribute8
2475 -- ,attribute9
2476 -- ,attribute10
2477 -- ,attribute11
2478 -- ,attribute12
2479 -- ,attribute13
2480 -- ,attribute14
2481 -- ,attribute15
2482 )
2483 = (SELECT
2484 DECODE(intf.mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM,NULL,
2485 NULL,pending_change.mrp_planning_code,
2486 intf.mrp_planning_code),
2487 DECODE(intf.description,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2488 NULL,pending_change.description,
2489 intf.description),
2490 DECODE(intf.first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2491 NULL,pending_change.first_article_status,
2492 intf.first_article_status),
2493 DECODE(intf.approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2494 NULL,pending_change.approval_status,
2495 intf.approval_status),
2496 DECODE(intf.start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
2497 NULL,pending_change.start_date,
2498 intf.start_date),
2499 DECODE(intf.end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
2500 NULL,pending_change.end_date,
2501 intf.end_date),
2502 G_CM_TO_BE_PROCESSED
2503 -- NO Needt copy for DFF in R12
2504 -- , DECODE(intf.attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2505 -- NULL,pending_change.attribute_category,
2506 -- intf.attribute_category),
2507 -- DECODE(intf.attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2508 -- NULL,pending_change.attribute1,
2509 -- intf.attribute1),
2510 -- DECODE(intf.attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2511 -- NULL,pending_change.attribute2,
2512 -- intf.attribute2),
2513 -- DECODE(intf.attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2514 -- NULL,pending_change.attribute3,
2515 -- intf.attribute3),
2516 -- DECODE(intf.attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2517 -- NULL,pending_change.attribute4,
2518 -- intf.attribute4),
2519 -- DECODE(intf.attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2520 -- NULL,pending_change.attribute5,
2521 -- intf.attribute5),
2522 -- DECODE(intf.attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2523 -- NULL,pending_change.attribute6,
2524 -- intf.attribute6),
2525 -- DECODE(intf.attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2526 -- NULL,pending_change.attribute7,
2527 -- intf.attribute7),
2528 -- DECODE(intf.attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2529 -- NULL,pending_change.attribute8,
2530 -- intf.attribute8),
2531 -- DECODE(intf.attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2532 -- NULL,pending_change.attribute9,
2533 -- intf.attribute9),
2534 -- DECODE(intf.attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2535 -- NULL,pending_change.attribute10,
2536 -- intf.attribute10),
2537 -- DECODE(intf.attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2538 -- NULL,pending_change.attribute11,
2539 -- intf.attribute11),
2540 -- DECODE(intf.attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2541 -- NULL,pending_change.attribute12,
2542 -- intf.attribute12),
2543 -- DECODE(intf.attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2544 -- NULL,pending_change.attribute13,
2545 -- intf.attribute13),
2546 -- DECODE(intf.attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2547 -- NULL,pending_change.attribute14,
2548 -- intf.attribute14),
2549 -- DECODE(intf.attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
2550 -- NULL,pending_change.attribute15,
2551 -- intf.attribute15)
2552 FROM EGO_MFG_PART_NUM_CHGS pending_change
2553 WHERE intf.inventory_item_id = pending_change.inventory_item_id
2554 AND intf.organization_id = pending_change.organization_id
2555 AND intf.manufacturer_id = pending_change.manufacturer_id
2556 AND intf.mfg_part_num = pending_change.mfg_part_num
2557 AND intf.change_line_id = pending_change.change_line_id
2558 AND intf.transaction_type = DECODE(pending_change.ACD_TYPE, 'ADD', 'CREATE'
2559 , 'CHANGE', 'UPDATE'
2560 , 'DELETE', 'DELETE', 'INVALID')
2561 )
2562 WHERE data_set_id = p_batch_id
2563 AND process_flag = G_CM_DATA_POPULATION
2564 -- AND transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
2565 AND ( NVL(mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM)
2566 <> EGO_ITEM_PUB.G_INTF_NULL_NUM
2567 OR
2568 NVL(description,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2569 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2570 OR
2571 NVL(first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2572 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2573 OR
2574 NVL(approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2575 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2576 OR
2577 NVL(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
2578 <> EGO_ITEM_PUB.G_INTF_NULL_DATE
2579 OR
2580 NVL(end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
2581 <> EGO_ITEM_PUB.G_INTF_NULL_DATE
2582 -- OR
2583 -- NVL(attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2584 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2585 -- OR
2586 -- NVL(attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2587 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2588 -- OR
2589 -- NVL(attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2590 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2591 -- OR
2592 -- NVL(attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2593 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2594 -- OR
2595 -- NVL(attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2596 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2597 -- OR
2598 -- NVL(attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2599 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2600 -- OR
2601 -- NVL(attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2602 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2603 -- OR
2604 -- NVL(attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2605 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2606 -- OR
2607 -- NVL(attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2608 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2609 -- OR
2610 -- NVL(attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2611 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2612 -- OR
2613 -- NVL(attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2614 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2615 -- OR
2616 -- NVL(attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2617 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2618 -- OR
2619 -- NVL(attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2620 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2621 -- OR
2622 -- NVL(attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2623 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2624 -- OR
2625 -- NVL(attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2626 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2627 -- OR
2628 -- NVL(attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
2629 -- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
2630 ) ;
2631 -- I don't think we need this
2632 -- AND EXISTS WHEN ( SELECT 'x'
2633 -- FROM EGO_MFG_PART_NUM_CHGS pending_change2
2634 -- WHERE intf.inventory_item_id = pending_change2.inventory_item_id
2635 -- AND intf.organization_id = pending_change2.organization_id
2636 -- AND intf.manufacturer_id = pending_change2.manufacturer_id
2637 -- AND intf.mfg_part_num = pending_change2.mfg_part_num
2638 -- AND intf.change_line_id = pending_change2.change_line_id
2639 -- AND intf.transaction_type = DECODE(pending_change2.ACD_TYPE, 'ADD', 'CREATE'
2640 -- , 'CHANGE', 'UPDATE'
2641 -- , 'DELETE', 'DELETE', 'INVALID')
2642 -- ;
2643
2644
2645 Write_Debug('After Populating intf table with prod data for UPDATE done ' );
2646
2647 -- End of API body.
2648
2649
2650 -- Standard check of p_commit.
2651 IF FND_API.To_Boolean( p_commit ) THEN
2652 COMMIT WORK;
2653 END IF;
2654
2655 -- Standard call to get message count and if count is 1, get message info.
2656 FND_MSG_PUB.Count_And_Get
2657 ( p_count => x_msg_count ,
2658 p_data => x_msg_data
2659 );
2660
2661
2662 -----------------------------------------------------
2663 -- Close Error Handler Debug Session.
2664 -----------------------------------------------------
2665 Close_Debug_Session;
2666
2667
2668 EXCEPTION
2669 WHEN NO_ROWS_IN_INTF_TABLE THEN
2670 NULL;
2671
2672 WHEN FND_API.G_EXC_ERROR THEN
2673 ROLLBACK TO POPULATE_MFGPARTNUM_INTF;
2674 x_return_status := G_RET_STS_ERROR ;
2675 FND_MSG_PUB.Count_And_Get
2676 ( p_count => x_msg_count ,
2677 p_data => x_msg_data
2678 );
2679
2680 -----------------------------------------------------
2681 -- Close Error Handler Debug Session.
2682 -----------------------------------------------------
2683 Close_Debug_Session;
2684
2685 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2686 ROLLBACK TO POPULATE_MFGPARTNUM_INTF;
2687 x_return_status := G_RET_STS_UNEXP_ERROR ;
2688 FND_MSG_PUB.Count_And_Get
2689 ( p_count => x_msg_count ,
2690 p_data => x_msg_data
2691 );
2692
2693 -----------------------------------------------------
2694 -- Close Error Handler Debug Session.
2695 -----------------------------------------------------
2696 Close_Debug_Session;
2697
2698 WHEN OTHERS THEN
2699 ROLLBACK TO POPULATE_MFGPARTNUM_INTF;
2700 x_return_status := G_RET_STS_UNEXP_ERROR ;
2701
2702 IF FND_MSG_PUB.Check_Msg_Level
2703 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2704 THEN
2705 FND_MSG_PUB.Add_Exc_Msg
2706 ( G_PKG_NAME ,
2707 l_api_name
2708 );
2709 END IF;
2710
2711 FND_MSG_PUB.Count_And_Get
2712 ( p_count => x_msg_count ,
2713 p_data => x_msg_data
2714 );
2715
2716 -----------------------------------------------------
2717 -- Close Error Handler Debug Session.
2718 -----------------------------------------------------
2719 Close_Debug_Session;
2720
2721
2722
2723 END POPULATE_MFGPARTNUM_INTF ;
2724
2725
2726
2727
2728 -- API name : POPULATE_EXISTING_CHANGE
2729 -- Type : Public
2730 -- Pre-reqs : None.
2731 -- Function : Create and Start Workflow Process
2732 -- Parameters :
2733 -- IN : p_api_version IN NUMBER Required
2734 -- p_init_msg_list IN VARCHAR2 Optional
2735 -- Default = NULL
2736 -- FND_API.G_FALSE
2737 -- FND_API.G_TRUE
2738 -- p_commit IN VARCHAR2 Optional
2739 -- Default = NULL
2740 -- FND_API.G_FALSE
2741 -- FND_API.G_TRUE
2742 -- p_validation_level IN NUMBER Optional
2743 -- Default = NULL
2744 -- Values:
2745 -- FND_API.G_VALID_LEVEL_NONE 0
2746 -- FND_API.G_VALID_LEVEL_FULL 100
2747 -- p_write_msg_to_intftbl IN VARCHAR2 Optional
2748 -- Default = NULL
2749 -- FND_API.G_FALSE
2750 -- FND_API.G_TRUE
2751 -- p_api_caller IN VARCHAR2 Optional
2752 --
2753 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
2754 -- x_msg_count OUT NOCOPY NUMBER
2755 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
2756 -- IN OUT :
2757 -- x_item_key IN OUT NOCOPY VARCHAR2
2758 -- Identifies workflow item key
2759 -- Version : Current version 1.0 Initial Creation
2760 -- Initial version 1.0
2761 --
2762 PROCEDURE POPULATE_EXISTING_CHANGE
2763 ( p_api_version IN NUMBER
2764 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
2765 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
2766 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
2767 , x_return_status OUT NOCOPY VARCHAR2
2768 , x_msg_count OUT NOCOPY NUMBER
2769 , x_msg_data OUT NOCOPY VARCHAR2
2770 , p_write_msg_to_intftbl IN VARCHAR2 := NULL -- FND_API.G_FALSE
2771 , p_api_caller IN VARCHAR2 := NULL
2772 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
2773 , p_output_dir IN VARCHAR2 := NULL
2774 , p_debug_filename IN VARCHAR2 := NULL
2775 , p_batch_id IN NUMBER
2776 , p_change_number IN VARCHAR2 := NULL
2777 , p_process_entity IN VARCHAR2 := NULL
2778 , p_cm_process_type IN VARCHAR2 := NULL
2779 , p_item_id IN NUMBER := NULL
2780 , p_org_id IN NUMBER := NULL
2781 , p_create_new_flag IN VARCHAR2 := NULL -- N: New, E: Add to Existing
2782 )
2783 IS
2784
2785 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_EXISTING_CHANGE';
2786 l_api_version CONSTANT NUMBER := 1.0;
2787
2788 l_init_msg_list VARCHAR2(1) ;
2789 l_validation_level NUMBER ;
2790 l_commit VARCHAR2(1) ;
2791 l_write_msg_to_intftbl VARCHAR2(1) ;
2792
2793 --------------------------------------------
2794 -- Long Dynamic SQL String
2795 --------------------------------------------
2796 l_dyn_sql VARCHAR2(10000);
2797
2798 l_intf_table DBMS_SQL.VARCHAR2_TABLE;
2799 l_intf_batch_id_col DBMS_SQL.VARCHAR2_TABLE;
2800 l_intf_proc_flag_col DBMS_SQL.VARCHAR2_TABLE;
2801 l_intf_ri_seq_id_col DBMS_SQL.VARCHAR2_TABLE;
2802 l_intf_attr_grp_type DBMS_SQL.VARCHAR2_TABLE;
2803 l_intf_change_number_col DBMS_SQL.VARCHAR2_TABLE;
2804
2805 l_error_msg VARCHAR2(2000) ;
2806
2807 -- add for bug 13004839
2808
2809 l_change_id NUMBER ;
2810 l_inventory_item_id NUMBER ;
2811
2812 BEGIN
2813
2814 -- Standard Start of API savepoint
2815 SAVEPOINT POPULATE_EXISTING_CHANGE;
2816
2817 -- Standard call to check for call compatibility.
2818 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2819 p_api_version ,
2820 l_api_name ,
2821 G_PKG_NAME )
2822 THEN
2823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2824 END IF;
2825
2826 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
2827 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
2828 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
2829 l_write_msg_to_intftbl := NVL(p_write_msg_to_intftbl,FND_API.G_FALSE) ;
2830
2831
2832 -- Initialize message list if p_init_msg_list is set to TRUE.
2833 IF FND_API.to_Boolean( l_init_msg_list ) THEN
2834 FND_MSG_PUB.initialize;
2835 END IF;
2836
2837 -- Open Debug Session by a give param or profile option.
2838 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
2839 Write_Debug('After Open_Debug_Session');
2840
2841
2842 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
2843 Write_Debug('-----------------------------------------' );
2844 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
2845 Write_Debug('p_change_number: ' || p_change_number );
2846 Write_Debug('p_process_entity:' || p_process_entity);
2847 Write_Debug('-----------------------------------------' );
2848
2849 -- Initialize API return status to success
2850 x_return_status := G_RET_STS_SUCCESS;
2851
2852
2853 -- API body
2854 -- Logic Here
2855
2856 -- Get Process Interface Table Definitions
2857 Get_Process_IntfTable_Def(p_process_entity => p_process_entity
2858 , x_intf_table => l_intf_table
2859 , x_intf_batch_id_col => l_intf_batch_id_col
2860 , x_intf_proc_flag_col => l_intf_proc_flag_col
2861 , x_intf_ri_seq_id_col => l_intf_ri_seq_id_col
2862 , x_intf_attr_grp_type => l_intf_attr_grp_type
2863 , x_intf_chg_notice_col => l_intf_change_number_col
2864 ) ;
2865
2866
2867 FOR i IN 1..l_intf_table.COUNT LOOP
2868
2869 Write_Debug('Check Existing CO and populate CO Id to INTF Table. . .' );
2870
2871
2872 -- If it's BOM, we need to first derive Change Id based on Change Notice populated
2873 -- in Bom's Interface Tables
2874 IF l_intf_table(i) LIKE 'BOM%' THEN
2875
2876 Write_Debug('Check Existing CO and populate CO Id for BOM INTF Table using Change Notice Info populated to Intf Table. . .' );
2877
2878 l_dyn_sql := '' ;
2879 l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
2880 l_dyn_sql := l_dyn_sql || ' SET change_id = NVL(( SELECT eec.change_id ' ;
2881 l_dyn_sql := l_dyn_sql || ' FROM eng_engineering_changes eec ' ;
2882 l_dyn_sql := l_dyn_sql || ' WHERE eec.organization_id = INTF.organization_id ' ;
2883 l_dyn_sql := l_dyn_sql || ' AND eec.change_notice = INTF.' || l_intf_change_number_col(i) ;
2884 l_dyn_sql := l_dyn_sql || ' ) , -100) ' ;
2885 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
2886 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED ;
2887 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_change_number_col(i) || ' IS NOT NULL ' ;
2888 l_dyn_sql := l_dyn_sql || ' AND INTF.change_id IS NULL ';
2889 Write_Debug(l_dyn_sql);
2890 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
2891 END IF ;
2892
2893
2894 Write_Debug('Populate CO Id from the batch details. . .' );
2895 l_dyn_sql := '' ;
2896 l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
2897 l_dyn_sql := l_dyn_sql || ' SET change_id = NVL(( SELECT eec.change_id ' ;
2898 l_dyn_sql := l_dyn_sql || ' FROM eng_engineering_changes eec ' ;
2899 l_dyn_sql := l_dyn_sql || ' WHERE eec.organization_id = INTF.organization_id ' ;
2900 l_dyn_sql := l_dyn_sql || ' AND eec.change_notice = ''' || p_change_number ||'''';
2901 l_dyn_sql := l_dyn_sql || ' ) , -100) ' ;
2902 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
2903 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED ;
2904 l_dyn_sql := l_dyn_sql || ' AND INTF.change_id IS NULL ';
2905 IF l_intf_attr_grp_type(i) IS NOT NULL
2906 THEN
2907 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
2908 END IF ;
2909
2910
2911 Write_Debug(l_dyn_sql);
2912
2913 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
2914
2915 ---bug fix 13004839 start, populate change_id into item interface table from bom interface table
2916
2917 IF l_intf_table(i) in (G_BOM_INTF, G_COMP_INTF, G_SUB_COMP_INTF) THEN
2918 --1---
2919 IF l_intf_table(i) in (G_BOM_INTF) THEN
2920 Write_Debug('Populate CO Id to item interface table by using CO Id in bom insterface ');
2921 begin
2922 SELECT NVL(bom.change_id, -100), bom.assembly_item_id
2923 into l_change_id, l_inventory_item_id
2924 FROM BOM_BILL_OF_MTLS_INTERFACE bom,
2925 MTL_SYSTEM_ITEMS_INTERFACE item
2926 WHERE item.set_process_id = p_batch_id
2927 and item.organization_id = bom.organization_id
2928 and item.inventory_item_id =
2929 (select assembly_item_id
2930 from (select assembly_item_id
2931 FROM BOM_BILL_OF_MTLS_INTERFACE bom_itfc
2932 WHERE bom_itfc.batch_id = p_batch_id
2933 order by creation_date desc)
2934 where rownum = 1)
2935 and item.inventory_item_id = bom.assembly_item_id
2936 and bom.batch_id = p_batch_id
2937 AND (item.change_id IS NULL or item.change_id = -100);
2938 EXCEPTION
2939 WHEN NO_DATA_FOUND THEN
2940 l_change_id := -100;
2941 l_inventory_item_id := -100;
2942 END;
2943 --2--
2944 ELSIF l_intf_table(i) in (G_COMP_INTF) THEN
2945 Write_Debug('Populate CO Id to item interface table by using CO Id in bom component insterface ');
2946 begin
2947 SELECT NVL(bom.change_id, -100), bom.component_item_id
2948 into l_change_id, l_inventory_item_id
2949 FROM BOM_INVENTORY_COMPS_INTERFACE bom,
2950 MTL_SYSTEM_ITEMS_INTERFACE item
2951 WHERE item.set_process_id = p_batch_id
2952 and item.organization_id = bom.organization_id
2953 and item.inventory_item_id =
2954 (select component_item_id
2955 from (select component_item_id
2956 FROM BOM_INVENTORY_COMPS_INTERFACE bom_itfc
2957 WHERE bom_itfc.batch_id = p_batch_id
2958 order by creation_date desc)
2959 where rownum = 1)
2960 and item.inventory_item_id = bom.component_item_id
2961 and bom.batch_id = p_batch_id
2962 AND (item.change_id IS NULL or item.change_id = -100);
2963 EXCEPTION
2964 WHEN NO_DATA_FOUND THEN
2965 l_change_id := -100;
2966 l_inventory_item_id := -100;
2967 END;
2968 --3--
2969 ELSE
2970 Write_Debug('Populate CO Id to item interface table by using CO Id in bom substitute component insterface ');
2971 begin
2972 SELECT NVL(bom.change_id, -100), bom.substitute_component_id
2973 into l_change_id, l_inventory_item_id
2974 FROM BOM_SUB_COMPS_INTERFACE bom,
2975 MTL_SYSTEM_ITEMS_INTERFACE item
2976 WHERE item.set_process_id = p_batch_id
2977 and item.organization_id = bom.organization_id
2978 and item.inventory_item_id =
2979 (select substitute_component_id
2980 from (select substitute_component_id
2981 FROM BOM_SUB_COMPS_INTERFACE bom_itfc
2982 WHERE bom_itfc.batch_id = p_batch_id
2983 order by creation_date desc)
2984 where rownum = 1)
2985 and item.inventory_item_id = bom.substitute_component_id
2986 and bom.batch_id = p_batch_id
2987 AND (item.change_id IS NULL or item.change_id = -100);
2988 EXCEPTION
2989 WHEN NO_DATA_FOUND THEN
2990 l_change_id := -100;
2991 l_inventory_item_id := -100;
2992 END;
2993 END IF;
2994
2995 l_dyn_sql := '';
2996 l_dyn_sql := ' UPDATE ' || G_ITEM_INTF || ' item ';
2997 l_dyn_sql := l_dyn_sql || ' SET change_id = :CHANGE_ID ';
2998 l_dyn_sql := l_dyn_sql || ' WHERE item.set_process_id = :BATCH_ID ';
2999 l_dyn_sql := l_dyn_sql ||
3000 ' AND item.inventory_item_id = :INVENTORY_ITEM_ID ';
3001 l_dyn_sql := l_dyn_sql ||
3002 ' AND (item.change_id IS NULL or item.change_id = -100) ';
3003
3004 Write_Debug(l_dyn_sql);
3005 EXECUTE IMMEDIATE l_dyn_sql
3006 USING l_change_id, p_batch_id, l_inventory_item_id;
3007 END IF;
3008
3009 ---bug fix 13004839 end
3010
3011
3012 IF l_validation_level > FND_API.G_VALID_LEVEL_NONE AND
3013 FND_API.to_Boolean(l_write_msg_to_intftbl)
3014 THEN
3015
3016 Write_Debug('Insert Error Message for the records which can not find CO for the Org. . .' );
3017
3018 FND_MESSAGE.SET_NAME('ENG','ENG_IMPT_CO_NOT_FOUND');
3019 FND_MESSAGE.SET_TOKEN('CHANGE_NUMBER', p_change_number);
3020 FND_MESSAGE.SET_TOKEN('BATCH_ID', to_char(p_batch_id));
3021 l_error_msg := FND_MESSAGE.GET;
3022
3023 l_dyn_sql := '';
3024 l_dyn_sql := 'INSERT INTO MTL_INTERFACE_ERRORS ';
3025 l_dyn_sql := l_dyn_sql || '( ';
3026 l_dyn_sql := l_dyn_sql || ' ORGANIZATION_ID , ';
3027 l_dyn_sql := l_dyn_sql || ' UNIQUE_ID , ';
3028 l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID , ';
3029 l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE , ';
3030 l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY , ';
3031 l_dyn_sql := l_dyn_sql || ' CREATION_DATE, ';
3032 l_dyn_sql := l_dyn_sql || ' CREATED_BY , ';
3033 l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN , ';
3034 l_dyn_sql := l_dyn_sql || ' TABLE_NAME , ';
3035 l_dyn_sql := l_dyn_sql || ' MESSAGE_NAME , ';
3036 l_dyn_sql := l_dyn_sql || ' COLUMN_NAME , ';
3037 l_dyn_sql := l_dyn_sql || ' REQUEST_ID , ';
3038 l_dyn_sql := l_dyn_sql || ' PROGRAM_APPLICATION_ID , ';
3039 l_dyn_sql := l_dyn_sql || ' PROGRAM_ID , ';
3040 l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE , ';
3041 l_dyn_sql := l_dyn_sql || ' ERROR_MESSAGE , ';
3042 l_dyn_sql := l_dyn_sql || ' ENTITY_IDENTIFIER , ';
3043 l_dyn_sql := l_dyn_sql || ' BO_IDENTIFIER , ';
3044 l_dyn_sql := l_dyn_sql || ' MESSAGE_TYPE ';
3045 l_dyn_sql := l_dyn_sql || ') ';
3046 l_dyn_sql := l_dyn_sql || 'SELECT ';
3047 l_dyn_sql := l_dyn_sql || ' INTF.ORGANIZATION_ID , ';
3048 l_dyn_sql := l_dyn_sql || ' NULL , ';
3049 l_dyn_sql := l_dyn_sql || ' INTF.TRANSACTION_ID , ';
3050 l_dyn_sql := l_dyn_sql || ' SYSDATE , ';
3051 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.user_id , ';
3052 l_dyn_sql := l_dyn_sql || ' SYSDATE , ';
3053 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.user_id , ';
3054 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.login_id , ';
3055 l_dyn_sql := l_dyn_sql || '''' ||l_intf_table(i) ||''', ' ;
3056 l_dyn_sql := l_dyn_sql || ' ''ENG_IMPT_CO_NOT_FOUND'', ';
3057 l_dyn_sql := l_dyn_sql || ' NULL , ';
3058 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.conc_request_id , ';
3059 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.prog_appl_id, ';
3060 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.conc_program_id , ';
3061 l_dyn_sql := l_dyn_sql || ' SYSDATE , ';
3062 l_dyn_sql := l_dyn_sql || ''''||Escape_Single_Quote(l_error_msg)||''' , ' ;
3063 l_dyn_sql := l_dyn_sql || ''''||G_ERROR_ENTITY_CODE||''' , ' ;
3064 l_dyn_sql := l_dyn_sql || ''''||G_BO_IDENTIFIER||''' , ' ;
3065 l_dyn_sql := l_dyn_sql || ''''||G_ENG_MSG_TYPE_ERROR||'''' ;
3066 l_dyn_sql := l_dyn_sql || ' FROM ' || l_intf_table(i) || ' INTF ';
3067 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
3068 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED;
3069 l_dyn_sql := l_dyn_sql || ' AND INTF.change_id = -100 ';
3070
3071 IF l_intf_attr_grp_type(i) IS NOT NULL
3072 THEN
3073 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
3074 END IF ;
3075
3076
3077 Write_Debug(l_dyn_sql);
3078
3079 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
3080
3081
3082 END IF;
3083
3084 Write_Debug('Update Process Flag to Error for records which can not find CO for the Org. . .' );
3085
3086 l_dyn_sql := '' ;
3087 l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
3088 l_dyn_sql := l_dyn_sql || ' SET INTF.change_id = null ' ;
3089
3090 -- If validation level is more than None
3091 -- set the process flag to ERROR:3
3092 IF l_validation_level > FND_API.G_VALID_LEVEL_NONE
3093 THEN
3094 l_dyn_sql := l_dyn_sql || ' , INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_PS_ERROR ;
3095 END IF ;
3096
3097 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
3098 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED;
3099 l_dyn_sql := l_dyn_sql || ' AND INTF.change_id = -100 ';
3100
3101 IF l_intf_attr_grp_type(i) IS NOT NULL
3102 THEN
3103 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
3104 END IF ;
3105
3106 Write_Debug(l_dyn_sql);
3107
3108 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
3109 IF SQL%FOUND AND NVL(p_create_new_flag, 'E') = 'E' THEN -- Changed for bug 9398720
3110 x_return_status := G_RET_STS_WARNING ;
3111 END IF ;
3112
3113 END LOOP ;
3114
3115
3116 Write_Debug('set change number to batch option temporarily. . .' );
3117 -- Need to temporarily set change number to batch
3118 -- to derive the change order in subsequence CM Import API call
3119 -- like Item Import
3120 -- First Item Import would call CM Import API to process Item/Item Rev Entity,
3121 -- Second it would then call CM Import API to process other entity (all entity)
3122 UPDATE EGO_IMPORT_OPTION_SETS
3123 SET CHANGE_NOTICE = p_change_number
3124 , LAST_UPDATE_DATE = SYSDATE
3125 , LAST_UPDATED_BY = FND_GLOBAL.user_id
3126 , LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
3127 WHERE BATCH_ID = p_batch_id ;
3128
3129
3130 -- End of API body.
3131
3132
3133 -- Standard check of p_commit.
3134 IF FND_API.To_Boolean( p_commit ) THEN
3135 COMMIT WORK;
3136 END IF;
3137
3138 -- Standard call to get message count and if count is 1, get message info.
3139 FND_MSG_PUB.Count_And_Get
3140 ( p_count => x_msg_count ,
3141 p_data => x_msg_data
3142 );
3143
3144 -----------------------------------------------------
3145 -- Close Error Handler Debug Session.
3146 -----------------------------------------------------
3147 Close_Debug_Session;
3148
3149
3150 EXCEPTION
3151 WHEN FND_API.G_EXC_ERROR THEN
3152 ROLLBACK TO POPULATE_EXISTING_CHANGE;
3153 x_return_status := G_RET_STS_ERROR ;
3154 FND_MSG_PUB.Count_And_Get
3155 ( p_count => x_msg_count ,
3156 p_data => x_msg_data
3157 );
3158
3159 -----------------------------------------------------
3160 -- Close Error Handler Debug Session.
3161 -----------------------------------------------------
3162 Close_Debug_Session;
3163
3164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3165 ROLLBACK TO POPULATE_EXISTING_CHANGE;
3166 x_return_status := G_RET_STS_UNEXP_ERROR ;
3167 FND_MSG_PUB.Count_And_Get
3168 ( p_count => x_msg_count ,
3169 p_data => x_msg_data
3170 );
3171
3172 -----------------------------------------------------
3173 -- Close Error Handler Debug Session.
3174 -----------------------------------------------------
3175 Close_Debug_Session;
3176
3177 WHEN OTHERS THEN
3178 ROLLBACK TO POPULATE_EXISTING_CHANGE;
3179 x_return_status := G_RET_STS_UNEXP_ERROR ;
3180
3181 IF FND_MSG_PUB.Check_Msg_Level
3182 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3183 THEN
3184 FND_MSG_PUB.Add_Exc_Msg
3185 ( G_PKG_NAME ,
3186 l_api_name
3187 );
3188 END IF;
3189
3190 FND_MSG_PUB.Count_And_Get
3191 ( p_count => x_msg_count ,
3192 p_data => x_msg_data
3193 );
3194
3195 -----------------------------------------------------
3196 -- Close Error Handler Debug Session.
3197 -----------------------------------------------------
3198 Close_Debug_Session;
3199
3200
3201 END POPULATE_EXISTING_CHANGE ;
3202
3203
3204
3205 -- API name : POPULATE_EXISTING_REV_ITEMS
3206 -- Type : Public
3207 -- Pre-reqs : None.
3208 -- Function :
3209 -- Parameters :
3210 -- IN : p_api_version IN NUMBER Required
3211 -- p_init_msg_list IN VARCHAR2 Optional
3212 -- Default = NULL
3213 -- FND_API.G_FALSE
3214 -- FND_API.G_TRUE
3215 -- p_commit IN VARCHAR2 Optional
3216 -- Default = NULL
3217 -- FND_API.G_FALSE
3218 -- FND_API.G_TRUE
3219 -- p_validation_level IN NUMBER Optional
3220 -- Default = NULL
3221 -- Values:
3222 -- FND_API.G_VALID_LEVEL_NONE 0
3223 -- FND_API.G_VALID_LEVEL_FULL 100
3224 -- p_write_msg_to_intftbl IN VARCHAR2 Optional
3225 -- Default = NULL
3226 -- FND_API.G_FALSE
3227 -- FND_API.G_TRUE
3228 -- p_api_caller IN VARCHAR2 Optional
3229 --
3230 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
3231 -- x_msg_count OUT NOCOPY NUMBER
3232 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
3233 -- IN OUT :
3234 -- x_item_key IN OUT NOCOPY VARCHAR2
3235 -- Identifies workflow item key
3236 -- Version : Current version 1.0 Initial Creation
3237 -- Initial version 1.0
3238 --
3239 PROCEDURE POPULATE_EXISTING_REV_ITEMS
3240 ( p_api_version IN NUMBER
3241 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
3242 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
3243 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
3244 , x_return_status OUT NOCOPY VARCHAR2
3245 , x_msg_count OUT NOCOPY NUMBER
3246 , x_msg_data OUT NOCOPY VARCHAR2
3247 , p_write_msg_to_intftbl IN VARCHAR2 := NULL -- FND_API.G_FALSE
3248 , p_api_caller IN VARCHAR2 := NULL
3249 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
3250 , p_output_dir IN VARCHAR2 := NULL
3251 , p_debug_filename IN VARCHAR2 := NULL
3252 , p_batch_id IN NUMBER
3253 , p_process_entity IN VARCHAR2 := NULL
3254 , p_cm_process_type IN VARCHAR2 := NULL
3255 , p_item_id IN NUMBER := NULL
3256 , p_org_id IN NUMBER := NULL
3257 )
3258 IS
3259
3260 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_EXISTING_REV_ITEMS';
3261 l_api_version CONSTANT NUMBER := 1.0;
3262
3263 l_init_msg_list VARCHAR2(1) ;
3264 l_validation_level NUMBER ;
3265 l_commit VARCHAR2(1) ;
3266 l_write_msg_to_intftbl VARCHAR2(1) ;
3267 l_revision_import_policy VARCHAR2(1) ;
3268
3269 --------------------------------------------
3270 -- Long Dynamic SQL String
3271 --------------------------------------------
3272 l_dyn_sql VARCHAR2(32000);
3273
3274 I PLS_INTEGER ;
3275 l_intf_table DBMS_SQL.VARCHAR2_TABLE;
3276 l_intf_batch_id_col DBMS_SQL.VARCHAR2_TABLE;
3277 l_intf_proc_flag_col DBMS_SQL.VARCHAR2_TABLE;
3278 l_intf_ri_seq_id_col DBMS_SQL.VARCHAR2_TABLE;
3279 l_intf_attr_grp_type DBMS_SQL.VARCHAR2_TABLE;
3280 l_intf_change_number_col DBMS_SQL.VARCHAR2_TABLE;
3281
3282 l_msg_data VARCHAR2(4000);
3283 l_msg_count NUMBER;
3284 l_return_status VARCHAR2(1);
3285
3286 l_error_msg VARCHAR2(2000) ;
3287
3288 BEGIN
3289
3290 -- Standard Start of API savepoint
3291 SAVEPOINT POPULATE_EXISTING_REV_ITEMS;
3292
3293 -- Standard call to check for call compatibility.
3294 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3295 p_api_version ,
3296 l_api_name ,
3297 G_PKG_NAME )
3298 THEN
3299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3300 END IF;
3301
3302 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
3303 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
3304 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
3305 l_write_msg_to_intftbl := NVL(p_write_msg_to_intftbl,FND_API.G_FALSE) ;
3306
3307
3308 -- Initialize message list if p_init_msg_list is set to TRUE.
3309 IF FND_API.to_Boolean( l_init_msg_list ) THEN
3310 FND_MSG_PUB.initialize;
3311 END IF;
3312
3313 -- Open Debug Session by a give param or profile option.
3314 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
3315
3316 Write_Debug('After Open_Debug_Session');
3317 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
3318 Write_Debug('-----------------------------------------' );
3319 Write_Debug('p_api_version: ' || to_char(p_api_version));
3320 Write_Debug('p_init_msg_list:' || p_init_msg_list);
3321 Write_Debug('p_commit:' || p_commit);
3322 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
3323 Write_Debug('p_write_msg_to_intftbl:' || p_write_msg_to_intftbl);
3324 Write_Debug('p_api_caller:' || p_api_caller);
3325 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
3326 Write_Debug('p_process_entity:' || p_process_entity);
3327 Write_Debug('p_item_id: ' || to_char(p_item_id));
3328 Write_Debug('p_org_id: ' || to_char(p_org_id));
3329 Write_Debug('-----------------------------------------' );
3330
3331 -- Initialize API return status to success
3332 x_return_status := G_RET_STS_SUCCESS;
3333
3334
3335 -- API body
3336 -- Logic Here
3337 -- Init Local Vars
3338
3339 -- Get Process Interface Table Definitions
3340 -- Get Process Interface Table Definitions
3341 Get_Process_IntfTable_Def(p_process_entity => p_process_entity
3342 , x_intf_table => l_intf_table
3343 , x_intf_batch_id_col => l_intf_batch_id_col
3344 , x_intf_proc_flag_col => l_intf_proc_flag_col
3345 , x_intf_ri_seq_id_col => l_intf_ri_seq_id_col
3346 , x_intf_attr_grp_type => l_intf_attr_grp_type
3347 , x_intf_chg_notice_col => l_intf_change_number_col
3348 ) ;
3349
3350
3351 -- Get Revision Import Policy
3352 l_revision_import_policy := GET_REVISION_IMPORT_POLICY(p_batch_id => p_batch_id) ;
3353
3354 Write_Debug('Get Batch Import Option: Revision Import Policy: ' || l_revision_import_policy );
3355
3356
3357
3358 FOR i IN 1..l_intf_table.COUNT LOOP
3359
3360 Write_Debug('Check Existing Revised Item and populate REVISED_ITEM_SEQUENCE_ID to INTF Table. . .' );
3361
3362 l_dyn_sql := '' ;
3363 l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
3364 l_dyn_sql := l_dyn_sql || ' SET INTF.' || l_intf_ri_seq_id_col(i) ;
3365 -- Bug 5193662 : Populate the rev item id to the child recs and change id to the parent record.
3366 if l_intf_table(i) = G_ITEM_INTF then
3367 l_dyn_sql := l_dyn_sql || ' = ENG_CHANGE_IMPORT_UTIL.FIND_REV_ITEM_REC( INTF.change_id ';
3368 else
3369 l_dyn_sql := l_dyn_sql || ' = ENG_CHANGE_IMPORT_UTIL.get_Rev_item_update_parent( INTF.change_id ';
3370 END IF;
3371
3372 l_dyn_sql := l_dyn_sql || ', INTF.organization_id ';
3373 l_dyn_sql := l_dyn_sql || ', INTF.inventory_item_id';
3374
3375
3376 -- p_revision or p_revision_id
3377 IF l_intf_table(i) = G_ITEM_REV_INTF
3378 THEN
3379 l_dyn_sql := l_dyn_sql || ', INTF.revision ';
3380 ELSIF l_intf_table(i) = G_ITEM_USR_ATTR_INTF
3381 THEN
3382 l_dyn_sql := l_dyn_sql || ', INTF.revision ';
3383 ELSE
3384 l_dyn_sql := l_dyn_sql || ', TO_NUMBER(null)';
3385 END IF ;
3386
3387
3388 -- param: p_default_seq_id
3389 IF l_validation_level > FND_API.G_VALID_LEVEL_NONE
3390 THEN
3391 l_dyn_sql := l_dyn_sql || ', -100';
3392 ELSE
3393 l_dyn_sql := l_dyn_sql || ', TO_NUMBER(null)';
3394 END IF ;
3395
3396 -- param: p_revision_import_policy
3397 l_dyn_sql := l_dyn_sql || ', ''' || l_revision_import_policy || '''';
3398
3399 l_dyn_sql := l_dyn_sql || ' ) ' ;
3400 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
3401 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED ;
3402 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_ri_seq_id_col(i) || ' IS NULL ';
3403 l_dyn_sql := l_dyn_sql || ' AND INTF.change_id IS NOT NULL ';
3404 IF l_intf_attr_grp_type(i) IS NOT NULL
3405 THEN
3406 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
3407 END IF ;
3408
3409
3410 Write_Debug(l_dyn_sql);
3411
3412 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
3413
3414
3415 IF l_validation_level > FND_API.G_VALID_LEVEL_NONE
3416 THEN
3417 -- Initialize message list if p_init_msg_list is set to TRUE.
3418 IF FND_API.to_Boolean(l_write_msg_to_intftbl) THEN
3419
3420 Write_Debug('Insert Error Message for the records which can not find CO for the Org. . .' );
3421
3422 FND_MESSAGE.SET_NAME('ENG','ENG_IMPT_RI_NOT_FOUND');
3423 FND_MESSAGE.SET_TOKEN('BATCH_ID', to_char(p_batch_id));
3424 l_error_msg := FND_MESSAGE.GET;
3425
3426 l_dyn_sql := '';
3427 l_dyn_sql := 'INSERT INTO MTL_INTERFACE_ERRORS ';
3428 l_dyn_sql := l_dyn_sql || '( ';
3429 l_dyn_sql := l_dyn_sql || ' ORGANIZATION_ID , ';
3430 l_dyn_sql := l_dyn_sql || ' UNIQUE_ID , ';
3431 l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID , ';
3432 l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE , ';
3433 l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY , ';
3434 l_dyn_sql := l_dyn_sql || ' CREATION_DATE, ';
3435 l_dyn_sql := l_dyn_sql || ' CREATED_BY , ';
3436 l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN , ';
3437 l_dyn_sql := l_dyn_sql || ' TABLE_NAME , ';
3438 l_dyn_sql := l_dyn_sql || ' MESSAGE_NAME , ';
3439 l_dyn_sql := l_dyn_sql || ' COLUMN_NAME , ';
3440 l_dyn_sql := l_dyn_sql || ' REQUEST_ID , ';
3441 l_dyn_sql := l_dyn_sql || ' PROGRAM_APPLICATION_ID , ';
3442 l_dyn_sql := l_dyn_sql || ' PROGRAM_ID , ';
3443 l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE , ';
3444 l_dyn_sql := l_dyn_sql || ' ERROR_MESSAGE , ';
3445 l_dyn_sql := l_dyn_sql || ' ENTITY_IDENTIFIER , ';
3446 l_dyn_sql := l_dyn_sql || ' BO_IDENTIFIER , ';
3447 l_dyn_sql := l_dyn_sql || ' MESSAGE_TYPE ';
3448 l_dyn_sql := l_dyn_sql || ') ';
3449 l_dyn_sql := l_dyn_sql || 'SELECT ';
3450 l_dyn_sql := l_dyn_sql || ' INTF.ORGANIZATION_ID , ';
3451 l_dyn_sql := l_dyn_sql || ' NULL , ';
3452 l_dyn_sql := l_dyn_sql || ' INTF.TRANSACTION_ID , ';
3453 l_dyn_sql := l_dyn_sql || ' SYSDATE , ';
3454 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.user_id , ';
3455 l_dyn_sql := l_dyn_sql || ' SYSDATE , ';
3456 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.user_id , ';
3457 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.login_id , ';
3458 l_dyn_sql := l_dyn_sql || '''' ||l_intf_table(i) ||''', ' ;
3459 l_dyn_sql := l_dyn_sql || ' ''ENG_IMPT_RI_NOT_FOUND'', ';
3460 l_dyn_sql := l_dyn_sql || ' NULL , ';
3461 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.conc_request_id , ';
3462 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.prog_appl_id, ';
3463 l_dyn_sql := l_dyn_sql || ' FND_GLOBAL.conc_program_id , ';
3464 l_dyn_sql := l_dyn_sql || ' SYSDATE , ';
3465 l_dyn_sql := l_dyn_sql || ''''||Escape_Single_Quote(l_error_msg)||''' , ' ;
3466 l_dyn_sql := l_dyn_sql || ''''||G_ERROR_ENTITY_CODE||''' , ' ;
3467 l_dyn_sql := l_dyn_sql || ''''||G_BO_IDENTIFIER||''' , ' ;
3468 l_dyn_sql := l_dyn_sql || ''''||G_ENG_MSG_TYPE_ERROR||'''' ;
3469 l_dyn_sql := l_dyn_sql || ' FROM ' || l_intf_table(i) || ' INTF ';
3470 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
3471 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED;
3472 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_ri_seq_id_col(i) || ' = -100' ;
3473 IF l_intf_attr_grp_type(i) IS NOT NULL
3474 THEN
3475 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
3476 END IF ;
3477
3478 Write_Debug(l_dyn_sql);
3479
3480 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
3481
3482 END IF;
3483
3484 Write_Debug('Update Process Flag to Error for records which can not find CO for the Org. . .' );
3485
3486 l_dyn_sql := '' ;
3487 l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
3488 l_dyn_sql := l_dyn_sql || ' SET INTF.' || l_intf_ri_seq_id_col(i) || ' = null ' ;
3489 l_dyn_sql := l_dyn_sql || ' , INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_PS_ERROR ;
3490 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
3491 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED;
3492 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_ri_seq_id_col(i) || ' = -100' ;
3493 IF l_intf_attr_grp_type(i) IS NOT NULL
3494 THEN
3495 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
3496 END IF ;
3497
3498 Write_Debug(l_dyn_sql);
3499
3500 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
3501
3502 IF SQL%FOUND THEN
3503 x_return_status := G_RET_STS_WARNING ;
3504 END IF ;
3505
3506
3507 END IF ; -- Validation Mode is not NONE
3508
3509
3510
3511 IF l_intf_table(i) = G_ITEM_AML_INTF
3512 THEN
3513
3514 Write_Debug('Calling POPULATE_MFGPARTNUM_INTF. . . ' );
3515
3516 POPULATE_MFGPARTNUM_INTF
3517 ( p_api_version => 1.0
3518 , p_init_msg_list => FND_API.G_FALSE
3519 , p_commit => FND_API.G_FALSE
3520 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3521 , x_return_status => l_return_status
3522 , x_msg_count => l_msg_count
3523 , x_msg_data => l_msg_data
3524 , p_write_msg_to_intftbl => FND_API.G_TRUE
3525 , p_api_caller => NULL
3526 , p_debug => p_debug
3527 , p_output_dir => p_output_dir
3528 , p_debug_filename => p_debug_filename
3529 , p_batch_id => p_batch_id
3530 ) ;
3531
3532
3533 Write_Debug('After POPULATE_MFGPARTNUM_INTF: Return Staus ' || l_return_status );
3534
3535
3536 IF l_return_status <> G_RET_STS_SUCCESS
3537 THEN
3538
3539 x_return_status := l_return_status ;
3540 x_msg_count := l_msg_count ;
3541 x_msg_data := l_msg_data ;
3542
3543 RAISE FND_API.G_EXC_ERROR ;
3544 END IF ;
3545
3546 END IF ;
3547
3548
3549
3550 END LOOP ;
3551
3552
3553 -- End of API body.
3554
3555
3556 -- Standard check of p_commit.
3557 IF FND_API.To_Boolean( p_commit ) THEN
3558 COMMIT WORK;
3559 END IF;
3560
3561 -- Standard call to get message count and if count is 1, get message info.
3562 FND_MSG_PUB.Count_And_Get
3563 ( p_count => x_msg_count ,
3564 p_data => x_msg_data
3565 );
3566
3567
3568 -----------------------------------------------------
3569 -- Close Error Handler Debug Session.
3570 -----------------------------------------------------
3571 Close_Debug_Session;
3572
3573
3574 EXCEPTION
3575 WHEN FND_API.G_EXC_ERROR THEN
3576 ROLLBACK TO POPULATE_EXISTING_REV_ITEMS;
3577 x_return_status := G_RET_STS_ERROR ;
3578 FND_MSG_PUB.Count_And_Get
3579 ( p_count => x_msg_count ,
3580 p_data => x_msg_data
3581 );
3582
3583 -----------------------------------------------------
3584 -- Close Error Handler Debug Session.
3585 -----------------------------------------------------
3586 Close_Debug_Session;
3587
3588 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3589
3590 ROLLBACK TO POPULATE_EXISTING_REV_ITEMS;
3591 x_return_status := G_RET_STS_UNEXP_ERROR ;
3592 FND_MSG_PUB.Count_And_Get
3593 ( p_count => x_msg_count ,
3594 p_data => x_msg_data
3595 );
3596
3597 -----------------------------------------------------
3598 -- Close Error Handler Debug Session.
3599 -----------------------------------------------------
3600 Close_Debug_Session;
3601
3602 WHEN OTHERS THEN
3603
3604 ROLLBACK TO POPULATE_EXISTING_REV_ITEMS;
3605 x_return_status := G_RET_STS_UNEXP_ERROR ;
3606
3607 IF FND_MSG_PUB.Check_Msg_Level
3608 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3609 THEN
3610 FND_MSG_PUB.Add_Exc_Msg
3611 ( G_PKG_NAME ,
3612 l_api_name
3613 );
3614 END IF;
3615
3616 FND_MSG_PUB.Count_And_Get
3617 ( p_count => x_msg_count ,
3618 p_data => x_msg_data
3619 );
3620
3621 -----------------------------------------------------
3622 -- Close Error Handler Debug Session.
3623 -----------------------------------------------------
3624 Close_Debug_Session;
3625
3626
3627 END POPULATE_EXISTING_REV_ITEMS;
3628
3629
3630
3631 -- API name : UPDATE_PROCESS_STATUS
3632 -- Type : Public
3633 -- Pre-reqs : None.
3634 -- Function :
3635 -- Parameters :
3636 -- IN : p_api_version IN NUMBER Required
3637 -- p_init_msg_list IN VARCHAR2 Optional
3638 -- Default = NULL
3639 -- FND_API.G_FALSE
3640 -- FND_API.G_TRUE
3641 -- p_commit IN VARCHAR2 Optional
3642 -- Default = NULL
3643 -- FND_API.G_FALSE
3644 -- FND_API.G_TRUE
3645 -- p_validation_level IN NUMBER Optional
3646 -- Default = NULL
3647 -- Values:
3648 -- FND_API.G_VALID_LEVEL_NONE 0
3649 -- FND_API.G_VALID_LEVEL_FULL 100
3650 -- p_write_msg_to_intftbl IN VARCHAR2 Optional
3651 -- Default = NULL
3652 -- FND_API.G_FALSE
3653 -- FND_API.G_TRUE
3654 -- p_api_caller IN VARCHAR2 Optional
3655 --
3656 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
3657 -- x_msg_count OUT NOCOPY NUMBER
3658 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
3659 -- IN OUT :
3660 -- x_item_key IN OUT NOCOPY VARCHAR2
3661 -- Identifies workflow item key
3662 -- Version : Current version 1.0 Initial Creation
3663 -- Initial version 1.0
3664 --
3665 PROCEDURE UPDATE_PROCESS_STATUS
3666 ( p_api_version IN NUMBER
3667 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
3668 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
3669 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
3670 , x_return_status OUT NOCOPY VARCHAR2
3671 , x_msg_count OUT NOCOPY NUMBER
3672 , x_msg_data OUT NOCOPY VARCHAR2
3673 , p_write_msg_to_intftbl IN VARCHAR2 := NULL -- FND_API.G_FALSE
3674 , p_api_caller IN VARCHAR2 := NULL
3675 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
3676 , p_output_dir IN VARCHAR2 := NULL
3677 , p_debug_filename IN VARCHAR2 := NULL
3678 , p_batch_id IN NUMBER
3679 , p_from_status IN NUMBER
3680 , p_to_status IN NUMBER
3681 , p_process_entity IN VARCHAR2 := NULL
3682 , p_item_id IN NUMBER := NULL
3683 , p_org_id IN NUMBER := NULL
3684 , p_transaction_id IN NUMBER := NULL
3685 )
3686 IS
3687
3688 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PROCESS_STATUS';
3689 l_api_version CONSTANT NUMBER := 1.0;
3690
3691 l_init_msg_list VARCHAR2(1) ;
3692 l_validation_level NUMBER ;
3693 l_commit VARCHAR2(1) ;
3694 l_write_msg_to_intftbl VARCHAR2(1) ;
3695
3696 --------------------------------------------
3697 -- Long Dynamic SQL String
3698 --------------------------------------------
3699 l_dyn_sql VARCHAR2(10000);
3700
3701 I PLS_INTEGER ;
3702 l_intf_table DBMS_SQL.VARCHAR2_TABLE;
3703 l_intf_batch_id_col DBMS_SQL.VARCHAR2_TABLE;
3704 l_intf_proc_flag_col DBMS_SQL.VARCHAR2_TABLE;
3705 l_intf_ri_seq_id_col DBMS_SQL.VARCHAR2_TABLE;
3706 l_intf_attr_grp_type DBMS_SQL.VARCHAR2_TABLE;
3707 l_intf_change_number_col DBMS_SQL.VARCHAR2_TABLE;
3708
3709 l_msg_data VARCHAR2(4000);
3710 l_msg_count NUMBER;
3711 l_return_status VARCHAR2(1);
3712
3713 l_error_msg VARCHAR2(2000) ;
3714
3715 BEGIN
3716
3717 -- Standard Start of API savepoint
3718 SAVEPOINT UPDATE_PROCESS_STATUS;
3719
3720 -- Standard call to check for call compatibility.
3721 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3722 p_api_version ,
3723 l_api_name ,
3724 G_PKG_NAME )
3725 THEN
3726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3727 END IF;
3728
3729 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
3730 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
3731 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
3732 l_write_msg_to_intftbl := NVL(p_write_msg_to_intftbl,FND_API.G_FALSE) ;
3733
3734
3735 -- Initialize message list if p_init_msg_list is set to TRUE.
3736 IF FND_API.to_Boolean( l_init_msg_list ) THEN
3737 FND_MSG_PUB.initialize;
3738 END IF;
3739
3740 -- Open Debug Session by a give param or profile option.
3741 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
3742
3743 Write_Debug('After Open_Debug_Session');
3744 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
3745 Write_Debug('-----------------------------------------' );
3746 Write_Debug('p_api_version: ' || to_char(p_api_version));
3747 Write_Debug('p_init_msg_list:' || p_init_msg_list);
3748 Write_Debug('p_commit:' || p_commit);
3749 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
3750 Write_Debug('p_write_msg_to_intftbl:' || p_write_msg_to_intftbl);
3751 Write_Debug('p_api_caller:' || p_api_caller);
3752 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
3753 Write_Debug('p_from_status: ' || to_char(p_from_status));
3754 Write_Debug('p_to_status: ' || to_char(p_to_status));
3755 Write_Debug('p_process_entity:' || p_process_entity);
3756 Write_Debug('p_item_id: ' || to_char(p_item_id));
3757 Write_Debug('p_org_id: ' || to_char(p_org_id));
3758 Write_Debug('p_transaction_id: ' || to_char(p_transaction_id));
3759 Write_Debug('-----------------------------------------' );
3760
3761 -- Initialize API return status to success
3762 x_return_status := G_RET_STS_SUCCESS;
3763
3764
3765 -- API body
3766 -- Logic Here
3767 -- Init Local Vars
3768
3769 -- Get Process Interface Table Definitions
3770 Get_Process_IntfTable_Def(p_process_entity => p_process_entity
3771 , x_intf_table => l_intf_table
3772 , x_intf_batch_id_col => l_intf_batch_id_col
3773 , x_intf_proc_flag_col => l_intf_proc_flag_col
3774 , x_intf_ri_seq_id_col => l_intf_ri_seq_id_col
3775 , x_intf_attr_grp_type => l_intf_attr_grp_type
3776 , x_intf_chg_notice_col => l_intf_change_number_col
3777 ) ;
3778
3779
3780 FOR i IN 1..l_intf_table.COUNT LOOP
3781
3782 Write_Debug('Update process status to INTF Table. . .' );
3783
3784 l_dyn_sql := '' ;
3785 l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
3786 l_dyn_sql := l_dyn_sql || ' SET INTF.' || l_intf_proc_flag_col(i) || ' = ' || p_to_status ;
3787
3788
3789 IF l_intf_table(i) = G_ITEM_USR_ATTR_INTF AND p_transaction_id IS NOT NULL
3790 THEN
3791
3792 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_proc_flag_col(i) || ' = ' || p_from_status ;
3793 l_dyn_sql := l_dyn_sql || ' AND (INTF.DATA_SET_ID, INTF.ROW_IDENTIFIER, INTF.ATTR_GROUP_ID ) ' ;
3794 l_dyn_sql := l_dyn_sql || ' IN (SELECT rec_grp.DATA_SET_ID, rec_grp.ROW_IDENTIFIER, rec_grp.ATTR_GROUP_ID ' ;
3795 l_dyn_sql := l_dyn_sql || ' FROM ' || l_intf_table(i) || ' rec_grp ' ;
3796 l_dyn_sql := l_dyn_sql || ' WHERE rec_grp.transaction_id = :TRANSACTION_ID ) ' ;
3797
3798 EXECUTE IMMEDIATE l_dyn_sql USING p_transaction_id ;
3799
3800 ELSIF l_intf_table(i) <> G_ITEM_USR_ATTR_INTF AND p_transaction_id IS NOT NULL
3801 THEN
3802 l_dyn_sql := l_dyn_sql || ' WHERE INTF.transaction_id = :TRANSACTION_ID ' ;
3803 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || p_from_status ;
3804
3805 EXECUTE IMMEDIATE l_dyn_sql USING p_transaction_id ;
3806
3807 ELSE
3808 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
3809 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || p_from_status ;
3810 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_ri_seq_id_col(i) || ' IS NOT NULL ';
3811 l_dyn_sql := l_dyn_sql || ' AND INTF.change_id IS NOT NULL ';
3812
3813 IF l_intf_attr_grp_type(i) IS NOT NULL
3814 THEN
3815 l_dyn_sql := l_dyn_sql || ' AND ' || Get_Attr_Group_Type_Condition('INTF.', l_intf_attr_grp_type(I));
3816 END IF ;
3817
3818 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id ;
3819
3820 END IF ;
3821
3822
3823 Write_Debug(l_dyn_sql);
3824
3825
3826 END LOOP ;
3827 -- End of API body.
3828
3829
3830 -- Standard check of p_commit.
3831 IF FND_API.To_Boolean( p_commit ) THEN
3832 COMMIT WORK;
3833 END IF;
3834
3835 -- Standard call to get message count and if count is 1, get message info.
3836 FND_MSG_PUB.Count_And_Get
3837 ( p_count => x_msg_count ,
3838 p_data => x_msg_data
3839 );
3840
3841
3842 -----------------------------------------------------
3843 -- Close Error Handler Debug Session.
3844 -----------------------------------------------------
3845 Close_Debug_Session;
3846
3847
3848 EXCEPTION
3849 WHEN FND_API.G_EXC_ERROR THEN
3850 ROLLBACK TO UPDATE_PROCESS_STATUS;
3851 x_return_status := G_RET_STS_ERROR ;
3852 FND_MSG_PUB.Count_And_Get
3853 ( p_count => x_msg_count ,
3854 p_data => x_msg_data
3855 );
3856
3857 -----------------------------------------------------
3858 -- Close Error Handler Debug Session.
3859 -----------------------------------------------------
3860 Close_Debug_Session;
3861
3862 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3863 ROLLBACK TO UPDATE_PROCESS_STATUS;
3864 x_return_status := G_RET_STS_UNEXP_ERROR ;
3865 FND_MSG_PUB.Count_And_Get
3866 ( p_count => x_msg_count ,
3867 p_data => x_msg_data
3868 );
3869
3870 -----------------------------------------------------
3871 -- Close Error Handler Debug Session.
3872 -----------------------------------------------------
3873 Close_Debug_Session;
3874
3875 WHEN OTHERS THEN
3876 ROLLBACK TO UPDATE_PROCESS_STATUS;
3877 x_return_status := G_RET_STS_UNEXP_ERROR ;
3878
3879 IF FND_MSG_PUB.Check_Msg_Level
3880 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3881 THEN
3882 FND_MSG_PUB.Add_Exc_Msg
3883 ( G_PKG_NAME ,
3884 l_api_name
3885 );
3886 END IF;
3887
3888 FND_MSG_PUB.Count_And_Get
3889 ( p_count => x_msg_count ,
3890 p_data => x_msg_data
3891 );
3892
3893 -----------------------------------------------------
3894 -- Close Error Handler Debug Session.
3895 -----------------------------------------------------
3896 Close_Debug_Session;
3897
3898
3899 END UPDATE_PROCESS_STATUS;
3900
3901 /* Bug 5193662 : This function calls FIND_REV_ITEM_REC to populate revision id in the Intf recs with process'
3902 flag as 5. But same time the parent row is marked with the change id as its sure that the
3903 Item has been added to the change as rev item if the rev item record is found.
3904 */
3905 FUNCTION get_Rev_item_update_parent ( p_change_id IN NUMBER
3906 , p_organization_id IN NUMBER
3907 , p_revised_item_id IN NUMBER
3908 , p_revision IN VARCHAR2
3909 , p_default_seq_id IN NUMBER := NULL
3910 , p_revision_import_policy IN VARCHAR2 := NULL
3911 )
3912 RETURN NUMBER
3913 IS
3914
3915 l_rev_item_seq_id NUMBER ;
3916 l_request_id NUMBER;
3917 BEGIN
3918
3919 l_rev_item_seq_id := FIND_REV_ITEM_REC ( p_change_id => p_change_id
3920 , p_organization_id => p_organization_id
3921 , p_revised_item_id => p_revised_item_id
3922 , p_revision => p_revision
3923 , p_default_seq_id => p_default_seq_id
3924 , p_revision_import_policy => p_revision_import_policy
3925 ) ;
3926
3927 select FND_GLOBAL.CONC_REQUEST_ID INTO l_request_id from dual;
3928 if l_rev_item_seq_id is not NULL
3929 then
3930
3931 UPDATE mtl_system_items_interface
3932 SET change_id = p_change_id
3933 where inventory_item_id = p_revised_item_id
3934 AND organization_id = p_organization_id
3935 AND request_id = l_request_id
3936 AND change_id is NULL;
3937 END IF;
3938
3939 return l_rev_item_seq_id;
3940
3941 END get_Rev_item_update_parent;
3942
3943
3944 -----------------------------------------------------------------
3945 -- Find Revised Item Record --
3946 -----------------------------------------------------------------
3947 FUNCTION FIND_REV_ITEM_REC ( p_change_notice IN VARCHAR2
3948 , p_organization_id IN NUMBER
3949 , p_revised_item_id IN NUMBER
3950 , p_revision_id IN NUMBER := NULL
3951 , p_default_seq_id IN NUMBER := NULL
3952 , p_revision_import_policy IN VARCHAR2 := NULL
3953 )
3954 RETURN NUMBER
3955 IS
3956 l_change_id ENG_ENGINEERING_CHANGES.CHANGE_ID%TYPE;
3957 BEGIN
3958 SELECT change_id INTO l_change_id FROM eng_engineering_changes WHERE change_notice = p_change_notice AND organization_id = p_organization_id;
3959
3960 RETURN FIND_REV_ITEM_REC ( p_change_id => l_change_id
3961 , p_organization_id => p_organization_id
3962 , p_revised_item_id => p_revised_item_id
3963 , p_revision_id => p_revision_id
3964 , p_default_seq_id => p_default_seq_id
3965 , p_revision_import_policy => p_revision_import_policy
3966 ) ;
3967
3968 END FIND_REV_ITEM_REC;
3969
3970 FUNCTION FIND_REV_ITEM_REC ( p_change_id IN NUMBER
3971 , p_organization_id IN NUMBER
3972 , p_revised_item_id IN NUMBER
3973 , p_revision IN VARCHAR2
3974 , p_default_seq_id IN NUMBER := NULL
3975 , p_revision_import_policy IN VARCHAR2 := NULL
3976 )
3977 RETURN NUMBER
3978 IS
3979
3980 l_revision_id NUMBER ;
3981
3982 CURSOR c_rev_id ( p_item_id IN NUMBER
3983 , p_org_id IN NUMBER
3984 , p_rev IN VARCHAR2
3985 )
3986 IS
3987 SELECT REVISION_ID
3988 FROM MTL_ITEM_REVISIONS_B
3989 WHERE INVENTORY_ITEM_ID = p_item_id
3990 AND ORGANIZATION_ID = p_org_id
3991 AND REVISION = p_rev ;
3992
3993
3994 BEGIN
3995
3996 Write_Debug(G_PKG_NAME || '.Find_Rev_Item_Rec for Item Revision Code. . . ');
3997 Write_Debug('-----------------------------------------' );
3998 Write_Debug('p_change_id: ' || to_char(p_change_id));
3999 Write_Debug('p_organization_id: ' || to_char(p_organization_id));
4000 Write_Debug('p_revised_item_id: ' || to_char(p_revised_item_id));
4001 Write_Debug('p_revision: ' || p_revision);
4002 Write_Debug('p_default_seq_id: ' || to_char(p_default_seq_id));
4003 Write_Debug('-----------------------------------------' );
4004
4005 IF p_revision IS NOT NULL
4006 THEN
4007
4008 OPEN c_rev_id (p_revised_item_id, p_organization_id , p_revision);
4009 FETCH c_rev_id INTO l_revision_id ;
4010 IF (c_rev_id%NOTFOUND)
4011 THEN
4012 CLOSE c_rev_id;
4013 Write_Debug('Revision Id is not found. . .' );
4014 END IF;
4015
4016 IF (c_rev_id%ISOPEN)
4017 THEN
4018 CLOSE c_rev_id;
4019 END IF ;
4020
4021 END IF ;
4022
4023 RETURN FIND_REV_ITEM_REC ( p_change_id => p_change_id
4024 , p_organization_id => p_organization_id
4025 , p_revised_item_id => p_revised_item_id
4026 , p_revision_id => l_revision_id
4027 , p_default_seq_id => p_default_seq_id
4028 , p_revision_import_policy => p_revision_import_policy
4029 ) ;
4030
4031 END FIND_REV_ITEM_REC ;
4032
4033
4034 FUNCTION FIND_REV_ITEM_REC ( p_change_id IN NUMBER
4035 , p_organization_id IN NUMBER
4036 , p_revised_item_id IN NUMBER
4037 , p_revision_id IN NUMBER := NULL
4038 , p_default_seq_id IN NUMBER := NULL
4039 , p_revision_import_policy IN VARCHAR2 := NULL
4040 )
4041 RETURN NUMBER
4042 IS
4043
4044 l_rev_item_seq_id NUMBER ;
4045 l_msg_data VARCHAR2(4000) ;
4046 l_msg_count NUMBER ;
4047 l_return_status VARCHAR2(1) ;
4048 l_revision_id NUMBER ;
4049
4050 CURSOR c_max_rev_id ( c_item_id IN NUMBER
4051 , c_org_id IN NUMBER
4052 )
4053 IS
4054 SELECT rev_b.REVISION_ID
4055 FROM MTL_ITEM_REVISIONS_B rev_b
4056 WHERE rev_b.INVENTORY_ITEM_ID = c_item_id
4057 AND rev_b.ORGANIZATION_ID = c_org_id
4058 AND rev_b.REVISION = ( SELECT max(max_rev.revision)
4059 FROM MTL_ITEM_REVISIONS_B max_rev
4060 WHERE max_rev.INVENTORY_ITEM_ID = rev_b.INVENTORY_ITEM_ID
4061 AND max_rev.ORGANIZATION_ID = rev_b.ORGANIZATION_ID
4062 ) ;
4063
4064
4065 BEGIN
4066
4067 Write_Debug(G_PKG_NAME || '.Find_Rev_Item_Rec. . . ');
4068 Write_Debug('-----------------------------------------' );
4069 Write_Debug('p_change_id: ' || to_char(p_change_id));
4070 Write_Debug('p_organization_id: ' || to_char(p_organization_id));
4071 Write_Debug('p_revised_item_id: ' || to_char(p_revised_item_id));
4072 Write_Debug('p_revision_id: ' || to_char(p_revision_id));
4073 Write_Debug('p_default_seq_id: ' || to_char(p_default_seq_id));
4074 Write_Debug('-----------------------------------------' );
4075
4076
4077 l_revision_id := p_revision_id ;
4078
4079 IF p_revision_import_policy IS NOT NULL
4080 AND G_REV_IMPT_POLICY_LATEST = p_revision_import_policy
4081 AND l_revision_id IS NULL
4082 THEN
4083 Write_Debug('Batch Import Revision Import Policy is Latest and revision id is null. So get the latest revision for this item ') ;
4084
4085 OPEN c_max_rev_id(p_revised_item_id, p_organization_id);
4086 FETCH c_max_rev_id INTO l_revision_id ;
4087 IF (c_max_rev_id%NOTFOUND)
4088 THEN
4089 CLOSE c_max_rev_id ;
4090 Write_Debug('Latest Revision Id is not found. . .' );
4091 END IF;
4092
4093 IF (c_max_rev_id%ISOPEN)
4094 THEN
4095 CLOSE c_max_rev_id ;
4096 END IF ;
4097
4098 Write_Debug('Latest Revision Id: ' || to_char(l_revision_id)) ;
4099
4100 END IF ;
4101
4102 Write_Debug('Before calling Eng_Revised_Item_Pkg.Query_Target_Revised_Item') ;
4103
4104 Eng_Revised_Items_Pkg.Query_Target_Revised_Item (
4105 p_api_version => 1.0
4106 , p_init_msg_list => FND_API.G_FALSE
4107 , x_return_status => l_return_status
4108 , x_msg_count => l_msg_count
4109 , x_msg_data => l_msg_data
4110 , p_change_id => p_change_id
4111 , p_organization_id => p_organization_id
4112 , p_revised_item_id => p_revised_item_id
4113 , p_revision_id => p_revision_id
4114 , x_revised_item_seq_id => l_rev_item_seq_id
4115 );
4116
4117
4118 Write_Debug('After calling Eng_Revised_Item_Pkg.Query_Target_Revised_Item') ;
4119 Write_Debug('Return Rev Item Seq Id: ' || TO_CHAR(l_rev_item_seq_id) );
4120 Write_Debug('Return Status: ' || l_return_status );
4121
4122 IF l_return_status <> G_RET_STS_SUCCESS
4123 THEN
4124 Write_Debug('After calling Eng_Revised_Item_Pkg.Query_Target_Revised_Item: Return Msg: ' || l_msg_data );
4125 RAISE FND_API.G_EXC_ERROR ;
4126 END IF ;
4127
4128 IF l_rev_item_seq_id IS NULL OR l_rev_item_seq_id <= 0
4129 THEN
4130 l_rev_item_seq_id := p_default_seq_id ;
4131 END IF ;
4132
4133 RETURN l_rev_item_seq_id ;
4134
4135 EXCEPTION
4136 WHEN OTHERS THEN
4137 Write_Debug('Exception in FIND_REV_ITEM_REC: '|| Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
4138 RETURN p_default_seq_id ;
4139
4140 END FIND_REV_ITEM_REC ;
4141
4142 --
4143 -- Procedure to create component interface rows given the component details
4144 --
4145 PROCEDURE CREATE_ORPHAN_COMPONENT_INTF
4146 ( p_api_version IN NUMBER
4147 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
4148 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
4149 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
4150 , x_return_status OUT NOCOPY VARCHAR2
4151 , x_msg_count OUT NOCOPY NUMBER
4152 , x_msg_data OUT NOCOPY VARCHAR2
4153 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
4154 , p_output_dir IN VARCHAR2 := NULL
4155 , p_debug_filename IN VARCHAR2 := NULL
4156 , p_organization_id IN NUMBER
4157 , p_assembly_item_id IN NUMBER
4158 , p_alternate_bom_designator IN VARCHAR2
4159 , p_bill_sequence_id IN NUMBER
4160 , p_component_item_id IN NUMBER
4161 , p_op_seq_number IN NUMBER
4162 , p_effectivity_date IN DATE := NULL
4163 , p_component_seq_id IN NUMBER
4164 , p_from_end_item_unit_number IN VARCHAR2 := NULL
4165 , p_from_end_item_rev_id IN NUMBER := NULL
4166 , p_batch_id IN NUMBER
4167 )
4168 IS
4169
4170 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ORPHAN_COMPONENT_INTF';
4171 l_api_version CONSTANT NUMBER := 1.0;
4172
4173 l_init_msg_list VARCHAR2(1) ;
4174 l_validation_level NUMBER ;
4175 l_commit VARCHAR2(1) ;
4176
4177 l_bill_sequence_id NUMBER;
4178 l_effectivity_control NUMBER;
4179 l_component_seq_id NUMBER;
4180
4181 l_component_item_id NUMBER;
4182 l_op_seq_number NUMBER;
4183 l_effectivity_date DATE;
4184 l_from_end_item_unit_number VARCHAR2(30);
4185 l_from_end_item_rev_id NUMBER;
4186
4187 BEGIN
4188
4189 -- Standard Start of API savepoint
4190 SAVEPOINT CREATE_ORPHAN_COMPONENT_INTF;
4191
4192 -- Standard call to check for call compatibility.
4193 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
4194 p_api_version ,
4195 l_api_name ,
4196 G_PKG_NAME )
4197 THEN
4198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4199 END IF;
4200
4201 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
4202 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
4203 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
4204
4205
4206
4207 -- Initialize message list if p_init_msg_list is set to TRUE.
4208 IF FND_API.to_Boolean( l_init_msg_list ) THEN
4209 FND_MSG_PUB.initialize;
4210 END IF;
4211
4212 -- Open Debug Session by a give param or profile option.
4213 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
4214
4215 Write_Debug('After Open_Debug_Session');
4216 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
4217 Write_Debug('-----------------------------------------' );
4218 Write_Debug('p_api_version: ' || to_char(p_api_version));
4219 Write_Debug('p_init_msg_list:' || p_init_msg_list);
4220 Write_Debug('p_commit:' || p_commit);
4221 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
4222 Write_Debug('p_organization_id: ' || to_char(p_organization_id));
4223 Write_Debug('p_assembly_item_id: ' || to_char(p_assembly_item_id));
4224 Write_Debug('p_alternate_bom_designator:' || p_alternate_bom_designator);
4225 Write_Debug('p_bill_sequence_id: ' || to_char(p_bill_sequence_id));
4226 Write_Debug('p_effectivity_date: ' || to_char(p_effectivity_date, 'YYYY-MM-DD HH24:MI:SS'));
4227 Write_Debug('p_from_end_item_unit_number:' || p_from_end_item_unit_number);
4228 Write_Debug('p_from_end_item_rev_id: ' || to_char(p_from_end_item_rev_id));
4229 Write_Debug('p_component_item_id: ' || to_char(p_component_item_id));
4230 Write_Debug('p_op_seq_number: ' || to_char(p_op_seq_number));
4231 Write_Debug('p_component_seq_id: ' || to_char(p_component_seq_id));
4232 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
4233 Write_Debug('-----------------------------------------' );
4234
4235 -- Initialize API return status to success
4236 x_return_status := G_RET_STS_SUCCESS;
4237
4238
4239 -- API body
4240 -- Logic Here
4241 -- Init Local Vars
4242
4243 -- Get the bill sequence id
4244 Write_Debug('Getting Bill Sequence Id: ');
4245 l_bill_sequence_id := NULL;
4246 l_effectivity_control := 1;
4247 BEGIN
4248 IF p_bill_sequence_id IS NOT NULL
4249 THEN
4250 l_bill_sequence_id := p_bill_sequence_id;
4251 SELECT effectivity_control
4252 INTO l_effectivity_control
4253 FROM BOM_STRUCTURES_B
4254 WHERE bill_sequence_id = p_bill_sequence_id;
4255 ELSE
4256 SELECT bill_sequence_id, effectivity_control
4257 INTO l_bill_sequence_id, l_effectivity_control
4258 FROM BOM_STRUCTURES_B
4259 WHERE assembly_item_id = p_assembly_item_id
4260 AND organization_id = p_organization_id
4261 AND ((alternate_bom_designator IS NULL AND
4262 p_alternate_bom_designator IS NULL) OR
4263 alternate_bom_designator = p_alternate_bom_designator);
4264 END IF;
4265 EXCEPTION
4266 WHEN NO_DATA_FOUND
4267 THEN
4268 l_bill_sequence_id := NULL;
4269 END;
4270
4271 Write_Debug('l_bill_sequence_id: ' || to_char(l_bill_sequence_id));
4272
4273 -- Get the component sequence id
4274 Write_Debug('Getting Component Sequence Id: ');
4275 l_component_seq_id := NULL;
4276 l_component_item_id := p_component_item_id;
4277 l_op_seq_number := p_op_seq_number;
4278 l_effectivity_date := p_effectivity_date;
4279 l_from_end_item_unit_number := p_from_end_item_unit_number;
4280 l_from_end_item_rev_id := p_from_end_item_rev_id;
4281
4282 BEGIN
4283 IF p_component_seq_id IS NOT NULL
4284 THEN
4285 l_component_seq_id := p_component_seq_id;
4286 SELECT component_item_id, operation_seq_num, effectivity_date, from_end_item_unit_number, from_end_item_rev_id
4287 INTO l_component_item_id, l_op_seq_number, l_effectivity_date, l_from_end_item_unit_number, l_from_end_item_rev_id
4288 FROM bom_components_b
4289 WHERE component_sequence_id = p_component_seq_id;
4290 ELSE
4291 IF l_effectivity_control = 2 -- Unit Effective
4292 THEN
4293 SELECT component_sequence_id
4294 INTO l_component_seq_id
4295 FROM bom_components_b bcb
4296 WHERE bcb.bill_sequence_id = l_bill_sequence_id
4297 AND bcb.operation_seq_num = p_op_seq_number
4298 AND bcb.component_item_id = p_component_item_id
4299 AND bcb.from_end_item_unit_number = p_from_end_item_unit_number
4300 AND bcb.implementation_date IS NOT NULL;
4301
4302 ELSIF l_effectivity_control = 4 -- Rev Effective
4303 THEN
4304 SELECT component_sequence_id
4305 INTO l_component_seq_id
4306 FROM bom_components_b bcb
4307 WHERE bcb.bill_sequence_id = l_bill_sequence_id
4308 AND bcb.operation_seq_num = p_op_seq_number
4309 AND bcb.component_item_id = p_component_item_id
4310 AND bcb.from_end_item_rev_id = p_from_end_item_rev_id
4311 AND bcb.implementation_date IS NOT NULL;
4312
4313 ELSE
4314 SELECT component_sequence_id
4315 INTO l_component_seq_id
4316 FROM bom_components_b bcb
4317 WHERE bcb.bill_sequence_id = l_bill_sequence_id
4318 AND bcb.operation_seq_num = p_op_seq_number
4319 AND bcb.component_item_id = p_component_item_id
4320 AND bcb.effectivity_date = p_effectivity_date
4321 AND bcb.implementation_date IS NOT NULL;
4322
4323 END IF;
4324 END IF;
4325 EXCEPTION
4326 WHEN NO_DATA_FOUND
4327 THEN
4328 l_component_seq_id := NULL;
4329 END;
4330 Write_Debug('l_component_seq_id: ' || to_char(l_component_seq_id));
4331
4332 Write_Debug('Inserting data into interface tables:');
4333 INSERT INTO bom_inventory_comps_interface
4334 (OPERATION_SEQ_NUM,
4335 COMPONENT_ITEM_ID,
4336 EFFECTIVITY_DATE,
4337 OLD_COMPONENT_SEQUENCE_ID,
4338 COMPONENT_SEQUENCE_ID,
4339 BILL_SEQUENCE_ID,
4340 ASSEMBLY_ITEM_ID,
4341 ALTERNATE_BOM_DESIGNATOR,
4342 ORGANIZATION_ID,
4343 PROCESS_FLAG,
4344 TRANSACTION_TYPE,
4345 FROM_END_ITEM_UNIT_NUMBER,
4346 FROM_END_ITEM_ID,
4347 FROM_END_ITEM_REV_ID,
4348 BATCH_ID
4349 ) VALUES
4350 (l_op_seq_number,
4351 l_component_item_id,
4352 l_effectivity_date,
4353 l_component_seq_id,
4354 l_component_seq_id,
4355 l_bill_sequence_id,
4356 p_assembly_item_id,
4357 p_alternate_bom_designator,
4358 p_organization_id,
4359 G_CM_TO_BE_PROCESSED,
4360 'UPDATE',
4361 l_from_end_item_unit_number,
4362 p_assembly_item_id,
4363 l_from_end_item_rev_id,
4364 p_batch_id
4365 );
4366 Write_Debug('Done Inserting data into interface tables:');
4367 -- End of API body.
4368
4369 -- Standard check of p_commit.
4370 IF FND_API.To_Boolean( p_commit ) THEN
4371 COMMIT WORK;
4372 END IF;
4373
4374 -- Standard call to get message count and if count is 1, get message info.
4375 FND_MSG_PUB.Count_And_Get
4376 ( p_count => x_msg_count ,
4377 p_data => x_msg_data
4378 );
4379
4380
4381 -----------------------------------------------------
4382 -- Close Error Handler Debug Session.
4383 -----------------------------------------------------
4384 Close_Debug_Session;
4385
4386 EXCEPTION
4387 WHEN FND_API.G_EXC_ERROR THEN
4388 ROLLBACK TO CREATE_ORPHAN_COMPONENT_INTF;
4389 x_return_status := G_RET_STS_ERROR ;
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;
4399
4400 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4401 ROLLBACK TO CREATE_ORPHAN_COMPONENT_INTF;
4402 x_return_status := G_RET_STS_UNEXP_ERROR ;
4403 FND_MSG_PUB.Count_And_Get
4404 ( p_count => x_msg_count ,
4405 p_data => x_msg_data
4406 );
4407
4408 -----------------------------------------------------
4409 -- Close Error Handler Debug Session.
4410 -----------------------------------------------------
4411 Close_Debug_Session;
4412
4413 WHEN OTHERS THEN
4414
4415 ROLLBACK TO CREATE_ORPHAN_COMPONENT_INTF;
4416 x_return_status := G_RET_STS_UNEXP_ERROR ;
4417
4418 IF FND_MSG_PUB.Check_Msg_Level
4419 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4420 THEN
4421 FND_MSG_PUB.Add_Exc_Msg
4422 ( G_PKG_NAME ,
4423 l_api_name
4424 );
4425 END IF;
4426
4427 FND_MSG_PUB.Count_And_Get
4428 ( p_count => x_msg_count ,
4429 p_data => x_msg_data
4430 );
4431
4432
4433 Write_Debug('Exception in CREATE_ORPHAN_COMPONENT_INTF: '|| Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
4434
4435 -----------------------------------------------------
4436 -- Close Error Handler Debug Session.
4437 -----------------------------------------------------
4438 Close_Debug_Session;
4439
4440
4441 END CREATE_ORPHAN_COMPONENT_INTF;
4442
4443
4444 --
4445 -- Procedure to create structure header interface rows given the component details
4446 --
4447 PROCEDURE CREATE_ORPHAN_HEADER_INTF
4448 ( p_api_version IN NUMBER
4449 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
4450 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
4451 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
4452 , x_return_status OUT NOCOPY VARCHAR2
4453 , x_msg_count OUT NOCOPY NUMBER
4454 , x_msg_data OUT NOCOPY VARCHAR2
4455 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
4456 , p_output_dir IN VARCHAR2 := NULL
4457 , p_debug_filename IN VARCHAR2 := NULL
4458 , p_organization_id IN NUMBER
4459 , p_assembly_item_id IN NUMBER
4460 , p_alternate_bom_designator IN VARCHAR2
4461 , p_bill_sequence_id IN NUMBER
4462 , p_batch_id IN NUMBER
4463 )
4464 IS
4465
4466 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ORPHAN_HEADER_INTF';
4467 l_api_version CONSTANT NUMBER := 1.0;
4468
4469 l_init_msg_list VARCHAR2(1) ;
4470 l_validation_level NUMBER ;
4471 l_commit VARCHAR2(1) ;
4472
4473 l_bill_sequence_id NUMBER;
4474 l_effectivity_control NUMBER;
4475 l_alt_bom_designator VARCHAR2(30);
4476 BEGIN
4477
4478 -- Standard Start of API savepoint
4479 SAVEPOINT CREATE_ORPHAN_HEADER_INTF;
4480
4481 -- Standard call to check for call compatibility.
4482 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
4483 p_api_version ,
4484 l_api_name ,
4485 G_PKG_NAME )
4486 THEN
4487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4488 END IF;
4489
4490 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
4491 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
4492 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
4493
4494
4495
4496 -- Initialize message list if p_init_msg_list is set to TRUE.
4497 IF FND_API.to_Boolean( l_init_msg_list ) THEN
4498 FND_MSG_PUB.initialize;
4499 END IF;
4500
4501 -- Open Debug Session by a give param or profile option.
4502 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
4503
4504 Write_Debug('After Open_Debug_Session');
4505 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
4506 Write_Debug('-----------------------------------------' );
4507 Write_Debug('p_api_version: ' || to_char(p_api_version));
4508 Write_Debug('p_init_msg_list:' || p_init_msg_list);
4509 Write_Debug('p_commit:' || p_commit);
4510 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
4511 Write_Debug('p_organization_id: ' || to_char(p_organization_id));
4512 Write_Debug('p_assembly_item_id: ' || to_char(p_assembly_item_id));
4513 Write_Debug('p_alternate_bom_designator:' || p_alternate_bom_designator);
4514 Write_Debug('p_bill_sequence_id: ' || to_char(p_bill_sequence_id));
4515 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
4516 Write_Debug('-----------------------------------------' );
4517
4518 -- Initialize API return status to success
4519 x_return_status := G_RET_STS_SUCCESS;
4520
4521
4522 -- API body
4523 -- Logic Here
4524 -- Init Local Vars
4525
4526 -- Get the bill sequence id
4527 Write_Debug('Getting Bill Sequence Id: ');
4528 l_bill_sequence_id := NULL;
4529 l_effectivity_control := 1;
4530 l_alt_bom_designator := p_alternate_bom_designator;
4531 BEGIN
4532 IF p_bill_sequence_id IS NOT NULL
4533 THEN
4534 l_bill_sequence_id := p_bill_sequence_id;
4535 SELECT effectivity_control, alternate_bom_Designator
4536 INTO l_effectivity_control, l_alt_bom_designator
4537 FROM BOM_STRUCTURES_B
4538 WHERE bill_sequence_id = p_bill_sequence_id;
4539 ELSE
4540 SELECT bill_sequence_id, effectivity_control
4541 INTO l_bill_sequence_id, l_effectivity_control
4542 FROM BOM_STRUCTURES_B
4543 WHERE assembly_item_id = p_assembly_item_id
4544 AND organization_id = p_organization_id
4545 AND ((alternate_bom_designator IS NULL AND
4546 p_alternate_bom_designator IS NULL) OR
4547 alternate_bom_designator = p_alternate_bom_designator);
4548 END IF;
4549 EXCEPTION
4550 WHEN NO_DATA_FOUND
4551 THEN
4552 l_bill_sequence_id := NULL;
4553 END;
4554
4555 Write_Debug('l_bill_sequence_id: ' || to_char(l_bill_sequence_id));
4556
4557 Write_Debug('Inserting data into interface tables:');
4558 INSERT INTO bom_bill_of_mtls_interface
4559 (ASSEMBLY_ITEM_ID,
4560 ORGANIZATION_ID,
4561 ALTERNATE_BOM_DESIGNATOR,
4562 BILL_SEQUENCE_ID,
4563 EFFECTIVITY_CONTROL,
4564 PROCESS_FLAG,
4565 TRANSACTION_TYPE,
4566 BATCH_ID
4567 ) VALUES
4568 (p_assembly_item_id,
4569 p_organization_id,
4570 l_alt_bom_designator,
4571 l_bill_sequence_id,
4572 l_effectivity_control,
4573 G_CM_TO_BE_PROCESSED,
4574 'NO_OP',
4575 p_batch_id
4576 );
4577 Write_Debug('Done Inserting data into interface tables:');
4578 -- End of API body.
4579
4580 -- Standard check of p_commit.
4581 IF FND_API.To_Boolean( p_commit ) THEN
4582 COMMIT WORK;
4583 END IF;
4584
4585 -- Standard call to get message count and if count is 1, get message info.
4586 FND_MSG_PUB.Count_And_Get
4587 ( p_count => x_msg_count ,
4588 p_data => x_msg_data
4589 );
4590
4591
4592 -----------------------------------------------------
4593 -- Close Error Handler Debug Session.
4594 -----------------------------------------------------
4595 Close_Debug_Session;
4596
4597 EXCEPTION
4598 WHEN FND_API.G_EXC_ERROR THEN
4599 ROLLBACK TO CREATE_ORPHAN_HEADER_INTF;
4600 x_return_status := G_RET_STS_ERROR ;
4601 FND_MSG_PUB.Count_And_Get
4602 ( p_count => x_msg_count ,
4603 p_data => x_msg_data
4604 );
4605
4606 -----------------------------------------------------
4607 -- Close Error Handler Debug Session.
4608 -----------------------------------------------------
4609 Close_Debug_Session;
4610
4611 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4612 ROLLBACK TO CREATE_ORPHAN_HEADER_INTF;
4613 x_return_status := G_RET_STS_UNEXP_ERROR ;
4614 FND_MSG_PUB.Count_And_Get
4615 ( p_count => x_msg_count ,
4616 p_data => x_msg_data
4617 );
4618
4619 -----------------------------------------------------
4620 -- Close Error Handler Debug Session.
4621 -----------------------------------------------------
4622 Close_Debug_Session;
4623
4624 WHEN OTHERS THEN
4625
4626 ROLLBACK TO CREATE_ORPHAN_HEADER_INTF;
4627 x_return_status := G_RET_STS_UNEXP_ERROR ;
4628
4629 IF FND_MSG_PUB.Check_Msg_Level
4630 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4631 THEN
4632 FND_MSG_PUB.Add_Exc_Msg
4633 ( G_PKG_NAME ,
4634 l_api_name
4635 );
4636 END IF;
4637
4638 FND_MSG_PUB.Count_And_Get
4639 ( p_count => x_msg_count ,
4640 p_data => x_msg_data
4641 );
4642
4643
4644 Write_Debug('Exception in CREATE_ORPHAN_HEADER_INTF: '|| Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
4645
4646 -----------------------------------------------------
4647 -- Close Error Handler Debug Session.
4648 -----------------------------------------------------
4649 Close_Debug_Session;
4650
4651
4652 END CREATE_ORPHAN_HEADER_INTF;
4653
4654
4655 --
4656 -- Procedure to create structure header interface rows given the component details
4657 --
4658 PROCEDURE PREPROCESS_COMP_CHILD_ROWS
4659 ( p_api_version IN NUMBER
4660 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
4661 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
4662 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
4663 , x_return_status OUT NOCOPY VARCHAR2
4664 , x_msg_count OUT NOCOPY NUMBER
4665 , x_msg_data OUT NOCOPY VARCHAR2
4666 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
4667 , p_output_dir IN VARCHAR2 := NULL
4668 , p_debug_filename IN VARCHAR2 := NULL
4669 , p_organization_id IN NUMBER
4670 , p_assembly_item_id IN NUMBER
4671 , p_alternate_bom_designator IN VARCHAR2
4672 , p_bill_sequence_id IN NUMBER
4673 , p_change_id IN NUMBER
4674 , p_change_notice IN VARCHAR2
4675 , p_batch_id IN NUMBER
4676 )
4677 IS
4678
4679 l_api_name CONSTANT VARCHAR2(30) := 'PREPROCESS_COMP_CHILD_ROWS';
4680 l_api_version CONSTANT NUMBER := 1.0;
4681
4682 l_init_msg_list VARCHAR2(1) ;
4683 l_validation_level NUMBER ;
4684 l_commit VARCHAR2(1) ;
4685
4686 BEGIN
4687
4688 -- Standard Start of API savepoint
4689 SAVEPOINT PREPROCESS_COMP_CHILD_ROWS;
4690
4691 -- Standard call to check for call compatibility.
4692 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
4693 p_api_version ,
4694 l_api_name ,
4695 G_PKG_NAME )
4696 THEN
4697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4698 END IF;
4699
4700 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
4701 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
4702 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
4703
4704
4705
4706 -- Initialize message list if p_init_msg_list is set to TRUE.
4707 IF FND_API.to_Boolean( l_init_msg_list ) THEN
4708 FND_MSG_PUB.initialize;
4709 END IF;
4710
4711 -- Open Debug Session by a give param or profile option.
4712 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
4713
4714 Write_Debug('After Open_Debug_Session');
4715 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
4716 Write_Debug('-----------------------------------------' );
4717 Write_Debug('p_api_version: ' || to_char(p_api_version));
4718 Write_Debug('p_init_msg_list:' || p_init_msg_list);
4719 Write_Debug('p_commit:' || p_commit);
4720 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
4721 Write_Debug('p_organization_id: ' || to_char(p_organization_id));
4722 Write_Debug('p_assembly_item_id: ' || to_char(p_assembly_item_id));
4723 Write_Debug('p_alternate_bom_designator:' || p_alternate_bom_designator);
4724 Write_Debug('p_bill_sequence_id: ' || to_char(p_bill_sequence_id));
4725 Write_Debug('p_change_id: ' || to_char(p_change_id));
4726 Write_Debug('p_change_notice: ' || to_char(p_change_notice));
4727 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
4728 Write_Debug('-----------------------------------------' );
4729
4730 -- Initialize API return status to success
4731 x_return_status := G_RET_STS_SUCCESS;
4732
4733
4734 -- API body
4735 -- Logic Here
4736 -- Init Local Vars
4737
4738 -----------------------------------------------------------------------------------------
4739 -- Transaction Type is CREATE/DELETE/UPDATE
4740 -----------------------------------------------------------------------------------------
4741 Write_Debug('Updating Reference Designator Interface records for Create... ' );
4742 -- (a) Reference Designators: CREATE/DELETE/UPDATE
4743 -----------------------------------------------------------------------------------------
4744 UPDATE bom_ref_desgs_interface brdi
4745 SET
4746 acd_type = decode(transaction_type, 'DELETE', G_BOM_DISABLE_ACD_TYPE,
4747 'UPDATE', G_BOM_CHANGE_ACD_TYPE
4748 ,G_BOM_ADD_ACD_TYPE),
4749 change_transaction_type = 'CREATE',
4750 change_notice = p_change_notice,
4751 bill_sequence_id = p_bill_sequence_id
4752 WHERE (bill_sequence_id = p_bill_sequence_id OR
4753 ( bill_sequence_id IS NULL
4754 AND assembly_item_id = p_assembly_item_id
4755 AND organization_id = p_organization_id
4756 AND (alternate_bom_designator = p_alternate_bom_designator
4757 OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
4758 AND change_id = p_change_id
4759 AND batch_id = p_batch_id
4760 AND process_flag = G_CM_TO_BE_PROCESSED
4761 AND transaction_type in ('CREATE', 'UPDATE', 'DELETE');
4762
4763
4764 Write_Debug('Updating Substitute Components Interface records for Create... ' );
4765 -- (b) Substitute Components: CREATE/DELETE/UPDATE
4766 -----------------------------------------------------------------------------------------
4767 UPDATE bom_sub_comps_interface
4768 SET
4769 acd_type = decode(transaction_type, 'DELETE', G_BOM_DISABLE_ACD_TYPE,
4770 'UPDATE', G_BOM_CHANGE_ACD_TYPE
4771 ,G_BOM_ADD_ACD_TYPE),
4772 change_transaction_type = 'CREATE',
4773 change_notice = p_change_notice,
4774 bill_sequence_id = p_bill_sequence_id
4775 WHERE (bill_sequence_id = p_bill_sequence_id OR
4776 ( bill_sequence_id IS NULL
4777 AND assembly_item_id = p_assembly_item_id
4778 AND organization_id = p_organization_id
4779 AND (alternate_bom_designator = p_alternate_bom_designator
4780 OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
4781 AND change_id = p_change_id
4782 AND batch_id = p_batch_id
4783 AND process_flag = G_CM_TO_BE_PROCESSED
4784 AND transaction_type in ('CREATE', 'UPDATE', 'DELETE');
4785
4786 -- End of API body.
4787
4788 -- Standard check of p_commit.
4789 IF FND_API.To_Boolean( p_commit ) THEN
4790 COMMIT WORK;
4791 END IF;
4792
4793 -- Standard call to get message count and if count is 1, get message info.
4794 FND_MSG_PUB.Count_And_Get
4795 ( p_count => x_msg_count ,
4796 p_data => x_msg_data
4797 );
4798
4799
4800 -----------------------------------------------------
4801 -- Close Error Handler Debug Session.
4802 -----------------------------------------------------
4803 Close_Debug_Session;
4804
4805 EXCEPTION
4806 WHEN FND_API.G_EXC_ERROR THEN
4807 ROLLBACK TO PREPROCESS_COMP_CHILD_ROWS;
4808 x_return_status := G_RET_STS_ERROR ;
4809 FND_MSG_PUB.Count_And_Get
4810 ( p_count => x_msg_count ,
4811 p_data => x_msg_data
4812 );
4813
4814 -----------------------------------------------------
4815 -- Close Error Handler Debug Session.
4816 -----------------------------------------------------
4817 Close_Debug_Session;
4818
4819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4820 ROLLBACK TO PREPROCESS_COMP_CHILD_ROWS;
4821 x_return_status := G_RET_STS_UNEXP_ERROR ;
4822 FND_MSG_PUB.Count_And_Get
4823 ( p_count => x_msg_count ,
4824 p_data => x_msg_data
4825 );
4826
4827 -----------------------------------------------------
4828 -- Close Error Handler Debug Session.
4829 -----------------------------------------------------
4830 Close_Debug_Session;
4831
4832 WHEN OTHERS THEN
4833
4834 ROLLBACK TO PREPROCESS_COMP_CHILD_ROWS;
4835 x_return_status := G_RET_STS_UNEXP_ERROR ;
4836
4837 IF FND_MSG_PUB.Check_Msg_Level
4838 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4839 THEN
4840 FND_MSG_PUB.Add_Exc_Msg
4841 ( G_PKG_NAME ,
4842 l_api_name
4843 );
4844 END IF;
4845
4846 FND_MSG_PUB.Count_And_Get
4847 ( p_count => x_msg_count ,
4848 p_data => x_msg_data
4849 );
4850
4851
4852 Write_Debug('Exception in PREPROCESS_COMP_CHILD_ROWS: '|| Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
4853
4854 -----------------------------------------------------
4855 -- Close Error Handler Debug Session.
4856 -----------------------------------------------------
4857 Close_Debug_Session;
4858
4859
4860 END PREPROCESS_COMP_CHILD_ROWS;
4861
4862
4863 --
4864 -- Procedure to preprocess the bom interface rows:
4865 -- The following entries are to be populated:
4866 -- 1. Revised Item sequence id - same as the rev item seq id passed
4867 -- 2. ACD TYPE - consult following matrix
4868 -- 3. Change Trasnaction Type - consult following matrix
4869 -- ================================================================
4870 -- | Txn Type | Present in ECO | ACD Type | Change Txn Type |
4871 -- =============+================+=============+==================|
4872 -- | CREATE | N | ADD | CREATE |
4873 -- | | Y/ERROR | *N/A* | *N/A* |
4874 -- +------------+----------------+-------------+------------------|
4875 -- | UPDATE | N | CHANGE | CREATE |
4876 -- | | Y | existing | UPDATE |
4877 -- +------------+----------------+-------------+------------------|
4878 -- | DELETE | N | DISABLE | CREATE |
4879 -- | | Y | existing | DELETE |
4880 -- +============+================+=============+==================|
4881 --
4882 --
4883 PROCEDURE PREPROCESS_BOM_INTERFACE_ROWS
4884 ( p_api_version IN NUMBER
4885 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
4886 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
4887 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
4888 , x_return_status OUT NOCOPY VARCHAR2
4889 , x_msg_count OUT NOCOPY NUMBER
4890 , x_msg_data OUT NOCOPY VARCHAR2
4891 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
4892 , p_output_dir IN VARCHAR2 := NULL
4893 , p_debug_filename IN VARCHAR2 := NULL
4894 , p_change_id IN NUMBER
4895 , p_change_notice IN VARCHAR2
4896 , p_organization_id IN NUMBER
4897 , p_revised_item_id IN NUMBER
4898 , p_alternate_bom_designator IN VARCHAR2
4899 , p_bill_sequence_id IN NUMBER
4900 , p_effectivity_date IN DATE := NULL
4901 , p_from_end_item_unit_number IN VARCHAR2 := NULL
4902 , p_from_end_item_rev_id IN NUMBER := NULL
4903 , p_current_date IN DATE := NULL
4904 , p_revised_item_sequence_id IN NUMBER
4905 , p_parent_rev_eff_date IN DATE := NULL
4906 , p_parent_revision_id IN NUMBER := NULL
4907 , p_batch_id IN NUMBER
4908 , p_request_id IN NUMBER
4909 )
4910 IS
4911
4912 l_api_name CONSTANT VARCHAR2(30) := 'PREPROCESS_BOM_INTERFACE_ROWS';
4913 l_api_version CONSTANT NUMBER := 1.0;
4914
4915 l_init_msg_list VARCHAR2(1) ;
4916 l_validation_level NUMBER ;
4917 l_commit VARCHAR2(1) ;
4918
4919
4920 l_acd_type NUMBER;
4921 l_comp_seq_id NUMBER;
4922 l_change_transaction_type VARCHAR2(10);
4923
4924 BEGIN
4925
4926 -- Standard Start of API savepoint
4927 SAVEPOINT PREPROCESS_BOM_INTERFACE_ROWS;
4928
4929 -- Standard call to check for call compatibility.
4930 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
4931 p_api_version ,
4932 l_api_name ,
4933 G_PKG_NAME )
4934 THEN
4935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4936 END IF;
4937
4938 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
4939 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
4940 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
4941
4942
4943
4944 -- Initialize message list if p_init_msg_list is set to TRUE.
4945 IF FND_API.to_Boolean( l_init_msg_list ) THEN
4946 FND_MSG_PUB.initialize;
4947 END IF;
4948
4949 -- Open Debug Session by a give param or profile option.
4950 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
4951
4952 Write_Debug('After Open_Debug_Session');
4953 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
4954 Write_Debug('-----------------------------------------' );
4955 Write_Debug('p_api_version: ' || to_char(p_api_version));
4956 Write_Debug('p_init_msg_list:' || p_init_msg_list);
4957 Write_Debug('p_commit:' || p_commit);
4958 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
4959 Write_Debug('p_change_id: ' || to_char(p_change_id));
4960 Write_Debug('p_change_notice:' || p_change_notice);
4961 Write_Debug('p_organization_id: ' || to_char(p_organization_id));
4962 Write_Debug('p_revised_item_id: ' || to_char(p_revised_item_id));
4963 Write_Debug('p_alternate_bom_designator:' || p_alternate_bom_designator);
4964 Write_Debug('p_bill_sequence_id: ' || to_char(p_bill_sequence_id));
4965 Write_Debug('p_effectivity_date: ' || to_char(p_effectivity_date, 'YYYY-MM-DD HH24:MI:SS'));
4966 Write_Debug('p_from_end_item_unit_number:' || p_from_end_item_unit_number);
4967 Write_Debug('p_from_end_item_rev_id: ' || to_char(p_from_end_item_rev_id));
4968 Write_Debug('p_current_date: ' || to_char(p_current_date, 'YYYY-MM-DD HH24:MI:SS'));
4969 Write_Debug('p_revised_item_sequence_id: ' || to_char(p_revised_item_sequence_id));
4970 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
4971 Write_Debug('p_request_id: ' || to_char(p_request_id));
4972 Write_Debug('-----------------------------------------' );
4973
4974 -- Initialize API return status to success
4975 x_return_status := G_RET_STS_SUCCESS;
4976
4977
4978 -- API body
4979 -- Logic Here
4980 -- Init Local Vars
4981
4982
4983 -----------------------------------------------------------------------------------------
4984 -- Transaction Type is CREATE
4985 -----------------------------------------------------------------------------------------
4986 Write_Debug('Updating Bom Components Interface records for Create... ' );
4987 -- (a) Components: CREATE
4988 -----------------------------------------------------------------------------------------
4989 UPDATE bom_inventory_comps_interface
4990 SET
4991 acd_type = G_BOM_ADD_ACD_TYPE,
4992 change_transaction_type = 'CREATE',
4993 change_notice = p_change_notice,
4994 revised_item_sequence_id = p_revised_item_sequence_id,
4995 bill_sequence_id = p_bill_sequence_id,
4996 new_effectivity_date = p_effectivity_date
4997 WHERE (bill_sequence_id = p_bill_sequence_id OR
4998 ( bill_sequence_id IS NULL
4999 AND assembly_item_id = p_revised_item_id
5000 AND organization_id = p_organization_id
5001 AND (alternate_bom_designator = p_alternate_bom_designator
5002 OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
5003 AND ((p_effectivity_date IS NULL AND effectivity_date IS NULL)
5004 OR (decode (new_effectivity_date,
5005 null,
5006 decode(parent_revision_id,
5007 null,
5008 nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
5009 (SELECT effectivity_date + 1/(24*3600)
5010 FROM mtl_item_revisions
5011 WHERE revision_id = parent_revision_id )),
5012 new_effectivity_date) = p_effectivity_date)
5013 OR (decode (new_effectivity_date,
5014 null,
5015 decode(parent_revision_id,
5016 null,
5017 nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
5018 (SELECT effectivity_date + 1/(24*3600)
5019 FROM mtl_item_revisions
5020 WHERE revision_id = parent_revision_id )),
5021 new_effectivity_date) <= p_current_date AND
5022 p_current_date = p_effectivity_date))
5023 AND NVL(new_from_end_item_unit_number, NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR))
5024 = NVL(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
5025 AND NVL(from_end_item_rev_id, '-1') = NVL(p_from_end_item_rev_id, '-1')
5026 AND change_id = p_change_id
5027 AND batch_id = p_batch_id
5028 AND process_flag = G_CM_TO_BE_PROCESSED
5029 AND transaction_type = 'CREATE';
5030
5031 -----------------------------------------------------------------------------------------
5032 -- Transaction Type is DELETE/UPDATE
5033 -----------------------------------------------------------------------------------------
5034 Write_Debug('Deleting/Updating Component Interface records for Create(Not present in ECO)... ' );
5035 -- (a) Components: DELETE/UPDATE: NOT already present in ECO
5036 -----------------------------------------------------------------------------------------
5037 UPDATE bom_inventory_comps_interface bici
5038 SET
5039 acd_type = decode(transaction_type, 'DELETE', G_BOM_DISABLE_ACD_TYPE, G_BOM_CHANGE_ACD_TYPE),
5040 change_transaction_type = 'CREATE',
5041 revised_item_sequence_id = p_revised_item_sequence_id,
5042 bill_sequence_id = p_bill_sequence_id,
5043 change_notice = p_change_notice,
5044 new_effectivity_date = p_effectivity_date
5045 WHERE (bill_sequence_id = p_bill_sequence_id OR
5046 ( bill_sequence_id IS NULL
5047 AND assembly_item_id = p_revised_item_id
5048 AND organization_id = p_organization_id
5049 AND (alternate_bom_designator = p_alternate_bom_designator
5050 OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
5051 AND NVL(new_from_end_item_unit_number, NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR))
5052 = NVL(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
5053 AND NVL(from_end_item_rev_id, '-1')
5054 = NVL(p_from_end_item_rev_id, '-1')
5055 AND ((p_effectivity_date IS NULL AND effectivity_date IS NULL)
5056 OR (decode (new_effectivity_date,
5057 null,
5058 decode(parent_revision_id,
5059 null,
5060 nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
5061 (SELECT effectivity_date + 1/(24*3600)
5062 FROM mtl_item_revisions
5063 WHERE revision_id = parent_revision_id )),
5064 new_effectivity_date) = p_effectivity_date)
5065 OR (decode (new_effectivity_date,
5066 null,
5067 decode(parent_revision_id,
5068 null,
5069 nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
5070 (SELECT effectivity_date + 1/(24*3600)
5071 FROM mtl_item_revisions
5072 WHERE revision_id = parent_revision_id )),
5073 new_effectivity_date) <= p_current_date AND
5074 p_current_date = p_effectivity_date))
5075 AND change_id = p_change_id
5076 AND batch_id = p_batch_id
5077 AND process_flag = G_CM_TO_BE_PROCESSED
5078 AND transaction_type in ('DELETE', 'UPDATE')
5079 AND not exists (SELECT 1
5080 FROM bom_inventory_components bic
5081 WHERE
5082 bic.component_sequence_id = bici.component_sequence_id
5083 AND bic.revised_item_sequence_id = p_revised_item_sequence_id);
5084
5085
5086 Write_Debug('Deleting/Updating Component Interface records for Create(Present in ECO)... ' );
5087 -- (b) Components: DELETE/UPDATE: already present in ECO
5088 -----------------------------------------------------------------------------------------
5089
5090 UPDATE bom_inventory_comps_interface bici
5091 SET
5092 acd_type = decode(transaction_type, 'DELETE', G_BOM_DISABLE_ACD_TYPE,
5093 (SELECT bic.acd_type
5094 FROM bom_inventory_components bic
5095 WHERE
5096 bic.component_sequence_id = bici.component_sequence_id
5097 AND bic.revised_item_sequence_id = p_revised_item_sequence_id)),
5098 change_transaction_type = decode(transaction_type, 'DELETE', 'DELETE', 'UPDATE'),
5099 revised_item_sequence_id = p_revised_item_sequence_id,
5100 bill_sequence_id = p_bill_sequence_id,
5101 change_notice = p_change_notice,
5102 new_effectivity_date = p_effectivity_date
5103 WHERE (bill_sequence_id = p_bill_sequence_id OR
5104 ( bill_sequence_id IS NULL
5105 AND assembly_item_id = p_revised_item_id
5106 AND organization_id = p_organization_id
5107 AND (alternate_bom_designator = p_alternate_bom_designator
5108 OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
5109 AND NVL(new_from_end_item_unit_number, NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR))
5110 = NVL(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
5111 AND NVL(from_end_item_rev_id, '-1')
5112 = NVL(p_from_end_item_rev_id, '-1')
5113 AND ((p_effectivity_date IS NULL AND effectivity_date IS NULL)
5114 OR (decode (new_effectivity_date,
5115 null,
5116 decode(parent_revision_id,
5117 null,
5118 nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
5119 (SELECT effectivity_date + 1/(24*3600)
5120 FROM mtl_item_revisions
5121 WHERE revision_id = parent_revision_id )),
5122 new_effectivity_date) = p_effectivity_date)
5123 OR (decode (new_effectivity_date,
5124 null,
5125 decode(parent_revision_id,
5126 null,
5127 nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
5128 (SELECT effectivity_date + 1/(24*3600)
5129 FROM mtl_item_revisions
5130 WHERE revision_id = parent_revision_id )),
5131 new_effectivity_date) <= p_current_date AND
5132 p_current_date = p_effectivity_date))
5133 AND change_id = p_change_id
5134 AND batch_id = p_batch_id
5135 AND process_flag = G_CM_TO_BE_PROCESSED
5136 AND transaction_type in ('DELETE', 'UPDATE')
5137 AND exists (SELECT 1
5138 FROM bom_inventory_components bic
5139 WHERE
5140 bic.component_sequence_id = bici.component_sequence_id
5141 AND bic.revised_item_sequence_id = p_revised_item_sequence_id);
5142
5143 -----------------------------------------------------------------------------------------
5144 -- Transaction Type is DELETE/UPDATE
5145 -----------------------------------------------------------------------------------------
5146 Write_Debug('Deleting/Updating Reference Designator records for Create(Present in ECO)... ' );
5147 -- (d) Reference Designators:: DELETE/UPDATE: already present in ECO
5148 -----------------------------------------------------------------------------------------
5149 UPDATE bom_ref_desgs_interface brdi
5150 SET
5151 acd_type = (SELECT brd.acd_type
5152 FROM bom_reference_designators brd,
5153 bom_inventory_components bic
5154 WHERE brd.component_sequence_id = bic.component_sequence_id
5155 AND brd.component_reference_designator = brdi.component_reference_designator
5156 AND bic.revised_item_sequence_id = p_revised_item_sequence_id
5157 AND bic.component_item_id = brdi.component_item_id),
5158 change_transaction_type = decode(transaction_type, 'DELETE', 'DELETE', 'UPDATE'),
5159 change_notice = p_change_notice
5160 WHERE (brdi.bill_sequence_id = p_bill_sequence_id OR
5161 ( brdi.bill_sequence_id IS NULL
5162 AND brdi.assembly_item_id = p_revised_item_id
5163 AND brdi.organization_id = p_organization_id
5164 AND (brdi.alternate_bom_designator = p_alternate_bom_designator
5165 OR (brdi.alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
5166 AND brdi.change_id = p_change_id
5167 AND brdi.batch_id = p_batch_id
5168 AND brdi.process_flag = G_CM_TO_BE_PROCESSED
5169 AND brdi.transaction_type in ('DELETE', 'UPDATE')
5170 AND exists (SELECT 1
5171 FROM bom_reference_designators brd,
5172 bom_inventory_components bic
5173 WHERE brd.component_sequence_id = bic.component_sequence_id
5174 AND brd.component_reference_designator = brdi.component_reference_designator
5175 AND bic.revised_item_sequence_id = p_revised_item_sequence_id
5176 AND bic.component_item_id = brdi.component_item_id);
5177
5178
5179 Write_Debug('Deleting/Updating Substitute Components records for Create(Present in ECO)... ' );
5180 -- (f) Substitute Components:: DELETE/UPDATE: already present in ECO
5181 -----------------------------------------------------------------------------------------
5182 UPDATE bom_sub_comps_interface bsci
5183 SET
5184 acd_type = (SELECT bsc.acd_type
5185 FROM bom_substitute_components bsc,
5186 bom_inventory_components bic
5187 WHERE bsc.component_sequence_id = bic.component_sequence_id
5188 AND bsc.substitute_component_id = bsci.substitute_component_id
5189 AND bic.revised_item_sequence_id = p_revised_item_sequence_id
5190 AND bic.component_item_id = bsci.component_item_id),
5191 change_transaction_type = decode(transaction_type, 'DELETE', 'DELETE', 'UPDATE'),
5192 change_notice = p_change_notice
5193 WHERE (bsci.bill_sequence_id = p_bill_sequence_id OR
5194 ( bsci.bill_sequence_id IS NULL
5195 AND bsci.assembly_item_id = p_revised_item_id
5196 AND bsci.organization_id = p_organization_id
5197 AND (bsci.alternate_bom_designator = p_alternate_bom_designator
5198 OR (bsci.alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
5199 AND bsci.change_id = p_change_id
5200 AND bsci.batch_id = p_batch_id
5201 AND bsci.process_flag = G_CM_TO_BE_PROCESSED
5202 AND bsci.transaction_type in ('DELETE', 'UPDATE')
5203 AND exists (SELECT 1
5204 FROM bom_substitute_components bsc,
5205 bom_inventory_components bic
5206 WHERE bsc.component_sequence_id = bic.component_sequence_id
5207 AND bsc.substitute_component_id = bsci.substitute_component_id
5208 AND bic.revised_item_sequence_id = p_revised_item_sequence_id
5209 AND bic.component_item_id = bsci.component_item_id);
5210
5211
5212
5213 Write_Debug('After Updating All Interface records . . .. ' );
5214
5215 -- End of API body.
5216
5217 -- Standard check of p_commit.
5218 IF FND_API.To_Boolean( p_commit ) THEN
5219 COMMIT WORK;
5220 END IF;
5221
5222 -- Standard call to get message count and if count is 1, get message info.
5223 FND_MSG_PUB.Count_And_Get
5224 ( p_count => x_msg_count ,
5225 p_data => x_msg_data
5226 );
5227
5228
5229 -----------------------------------------------------
5230 -- Close Error Handler Debug Session.
5231 -----------------------------------------------------
5232 Close_Debug_Session;
5233
5234 EXCEPTION
5235 WHEN FND_API.G_EXC_ERROR THEN
5236 ROLLBACK TO PREPROCESS_BOM_INTERFACE_ROWS;
5237 x_return_status := G_RET_STS_ERROR ;
5238 FND_MSG_PUB.Count_And_Get
5239 ( p_count => x_msg_count ,
5240 p_data => x_msg_data
5241 );
5242
5243 -----------------------------------------------------
5244 -- Close Error Handler Debug Session.
5245 -----------------------------------------------------
5246 Close_Debug_Session;
5247
5248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5249 ROLLBACK TO PREPROCESS_BOM_INTERFACE_ROWS;
5250 x_return_status := G_RET_STS_UNEXP_ERROR ;
5251 FND_MSG_PUB.Count_And_Get
5252 ( p_count => x_msg_count ,
5253 p_data => x_msg_data
5254 );
5255
5256 -----------------------------------------------------
5257 -- Close Error Handler Debug Session.
5258 -----------------------------------------------------
5259 Close_Debug_Session;
5260
5261 WHEN OTHERS THEN
5262
5263 ROLLBACK TO PREPROCESS_BOM_INTERFACE_ROWS;
5264 x_return_status := G_RET_STS_UNEXP_ERROR ;
5265
5266 IF FND_MSG_PUB.Check_Msg_Level
5267 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5268 THEN
5269 FND_MSG_PUB.Add_Exc_Msg
5270 ( G_PKG_NAME ,
5271 l_api_name
5272 );
5273 END IF;
5274
5275 FND_MSG_PUB.Count_And_Get
5276 ( p_count => x_msg_count ,
5277 p_data => x_msg_data
5278 );
5279
5280
5281 Write_Debug('Exception in PREPROCESS_BOM_INTERFACE_ROWS: '|| Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
5282
5283 -----------------------------------------------------
5284 -- Close Error Handler Debug Session.
5285 -----------------------------------------------------
5286 Close_Debug_Session;
5287
5288
5289 END PREPROCESS_BOM_INTERFACE_ROWS;
5290
5291
5292
5293
5294 /********************************************************************
5295 * API Type : Import Change Handler APIs
5296 * Purpose : Perform Import Change Table Handler
5297 *********************************************************************/
5298 PROCEDURE INSERT_IMPORTED_CHANGE_HISTORY
5299 ( p_api_version IN NUMBER
5300 , p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
5301 , p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
5302 , p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
5303 , x_return_status OUT NOCOPY VARCHAR2
5304 , x_msg_count OUT NOCOPY NUMBER
5305 , x_msg_data OUT NOCOPY VARCHAR2
5306 , p_write_msg_to_intftbl IN VARCHAR2 := NULL -- FND_API.G_FALSE
5307 , p_api_caller IN VARCHAR2 := NULL
5308 , p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
5309 , p_output_dir IN VARCHAR2 := NULL
5310 , p_debug_filename IN VARCHAR2 := NULL
5311 , p_batch_id IN NUMBER
5312 , p_change_ids IN FND_ARRAY_OF_NUMBER_25
5313 )
5314 IS
5315 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_IMPORTED_CHANGE_HISTORY';
5316 l_api_version CONSTANT NUMBER := 1.0;
5317
5318
5319 l_init_msg_list VARCHAR2(1) ;
5320 l_validation_level NUMBER ;
5321 l_commit VARCHAR2(1) ;
5322 l_write_msg_to_intftbl VARCHAR2(1) ;
5323
5324
5325 l_change_id NUMBER ;
5326 l_dummy_rowid VARCHAR2(100) ;
5327 l_msg_text VARCHAR2(2000) ;
5328
5329 l_hist_insert_flag VARCHAR2(1) ;
5330
5331 CURSOR check_existence_c (c_batch_id NUMBER, c_change_id NUMBER)
5332 IS
5333 select 'N'
5334 from EGO_IMPORT_BATCH_CHANGES
5335 where BATCH_ID = c_batch_id
5336 and CHANGE_ID = c_change_id
5337 ;
5338
5339 --Begin code change for bug 9398720
5340 l_change_number ENG_ENGINEERING_CHANGES.CHANGE_NOTICE%TYPE;
5341
5342 CURSOR get_change_number(c_change_id NUMBER)
5343 IS
5344 SELECT CHANGE_NOTICE
5345 FROM ENG_ENGINEERING_CHANGES
5346 WHERE CHANGE_ID = c_change_id;
5347 --End code change for bug 9398720
5348
5349 BEGIN
5350
5351 -- Standard Start of API savepoint
5352 SAVEPOINT INSERT_IMPORTED_CHANGE_HISTORY;
5353
5354 -- Standard call to check for call compatibility.
5355 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
5356 p_api_version ,
5357 l_api_name ,
5358 G_PKG_NAME )
5359 THEN
5360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5361 END IF;
5362
5363 l_init_msg_list := NVL(p_init_msg_list,FND_API.G_FALSE) ;
5364 l_validation_level := NVL(p_validation_level,FND_API.G_VALID_LEVEL_FULL) ;
5365 l_commit := NVL(p_commit,FND_API.G_FALSE) ;
5366 l_write_msg_to_intftbl := NVL(p_write_msg_to_intftbl,FND_API.G_FALSE) ;
5367
5368
5369 -- Initialize message list if p_init_msg_list is set to TRUE.
5370 IF FND_API.to_Boolean( l_init_msg_list ) THEN
5371 FND_MSG_PUB.initialize;
5372 END IF;
5373
5374 -- Open Debug Session by a give param or profile option.
5375 Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
5376
5377 Write_Debug('After Open_Debug_Session');
5378 Write_Debug(G_PKG_NAME || '.' || l_api_name || '. . . ');
5379 Write_Debug('-----------------------------------------' );
5380 Write_Debug('p_api_version: ' || to_char(p_api_version));
5381 Write_Debug('p_init_msg_list:' || p_init_msg_list);
5382 Write_Debug('p_commit:' || p_commit);
5383 Write_Debug('p_validation_level: ' || to_char(p_validation_level));
5384 Write_Debug('p_write_msg_to_intftbl:' || p_write_msg_to_intftbl);
5385 Write_Debug('p_api_caller:' || p_api_caller);
5386 Write_Debug('p_batch_id: ' || to_char(p_batch_id));
5387 Write_Debug('-----------------------------------------' );
5388
5389 -- Initialize API return status to success
5390 x_return_status := G_RET_STS_SUCCESS;
5391
5392 -- API body
5393 -- Logic Here
5394 FOR i IN p_change_ids.FIRST..p_change_ids.LAST
5395 LOOP
5396
5397 l_change_id := p_change_ids(i) ;
5398
5399 open check_existence_c(c_batch_id => p_batch_id, c_change_id => l_change_id) ;
5400 fetch check_existence_c into l_hist_insert_flag ;
5401 if (check_existence_c%notfound) then
5402 close check_existence_c ;
5403 l_hist_insert_flag := 'Y' ;
5404 else
5405
5406 Write_Debug('Import Hsotry exists for ChangeId: '|| to_char(l_change_id) ) ;
5407 l_hist_insert_flag := 'N' ;
5408
5409 end if;
5410
5411 if (check_existence_c%ISOPEN)
5412 then
5413 close check_existence_c ;
5414 end if ;
5415
5416
5417 IF l_hist_insert_flag = 'Y' THEN
5418
5419 Write_Debug('Inserting Import Hsotry: ChangeId: '|| to_char(l_change_id) ) ;
5420
5421 INSERT_IMPORT_CHANGE_ROW
5422 ( X_ROWID => l_dummy_rowid ,
5423 X_BATCH_ID => p_batch_id,
5424 X_CHANGE_ID => l_change_id ,
5425 X_CREATION_DATE => SYSDATE,
5426 X_CREATED_BY => FND_GLOBAL.user_id,
5427 X_LAST_UPDATE_DATE => SYSDATE,
5428 X_LAST_UPDATED_BY => FND_GLOBAL.user_id ,
5429 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
5430 ) ;
5431
5432
5433 IF FND_API.to_Boolean(l_write_msg_to_intftbl)
5434 THEN
5435
5436 Write_Debug('Insert Info Message for the record inserted as history. . .' );
5437 --Begin code change for bug 9398720
5438 OPEN get_change_number(l_change_id);
5439 FETCH get_change_number INTO l_change_number;
5440 IF (get_change_number%NOTFOUND) THEN
5441 CLOSE get_change_number;
5442 l_change_number := NULL;
5443 END IF;
5444
5445 IF (get_change_number%ISOPEN) THEN
5446 CLOSE get_change_number;
5447 END IF;
5448 --End code change for bug 9398720
5449
5450 FND_MESSAGE.SET_NAME('ENG','ENG_IMPT_INS_HIST');
5451 --Begin code change for bug 9398720, use change number to replace change id in this message.
5452 --FND_MESSAGE.SET_TOKEN('CHANGE_NUMBER', to_char(l_change_id));
5453 FND_MESSAGE.SET_TOKEN('CHANGE_NUMBER', NVL(l_change_number, to_char(l_change_id)));
5454 --End code change for bug 9398720
5455 FND_MESSAGE.SET_TOKEN('BATCH_ID', to_char(p_batch_id));
5456 l_msg_text := FND_MESSAGE.GET;
5457
5458 Insert_Mtl_Intf_Err
5459 ( p_transaction_id => null
5460 , p_bo_identifier => G_BO_IDENTIFIER
5461 , p_error_entity_code => null
5462 , p_error_table_name => null
5463 , p_error_column_name => NULL
5464 , p_error_msg => l_msg_text
5465 , p_error_msg_type => G_ENG_MSG_TYPE_INFORMATION
5466 , p_error_msg_name => null
5467 ) ;
5468 END IF ;
5469 END IF ; -- l_hist_insert_flag is 'Y'
5470
5471 END LOOP ;
5472 -- End of API body.
5473
5474
5475 -- Standard check of p_commit.
5476 IF FND_API.To_Boolean( p_commit ) THEN
5477 COMMIT WORK;
5478 END IF;
5479
5480 -- Standard call to get message count and if count is 1, get message info.
5481 FND_MSG_PUB.Count_And_Get
5482 ( p_count => x_msg_count ,
5483 p_data => x_msg_data
5484 );
5485
5486 EXCEPTION
5487 WHEN FND_API.G_EXC_ERROR THEN
5488 ROLLBACK TO INSERT_IMPORTED_CHANGE_HISTORY;
5489 x_return_status := G_RET_STS_ERROR ;
5490 FND_MSG_PUB.Count_And_Get
5491 ( p_count => x_msg_count ,
5492 p_data => x_msg_data
5493 );
5494
5495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5496 ROLLBACK TO INSERT_IMPORTED_CHANGE_HISTORY;
5497 x_return_status := G_RET_STS_UNEXP_ERROR ;
5498 FND_MSG_PUB.Count_And_Get
5499 ( p_count => x_msg_count ,
5500 p_data => x_msg_data
5501 );
5502
5503 WHEN OTHERS THEN
5504 ROLLBACK TO INSERT_IMPORTED_CHANGE_HISTORY;
5505 x_return_status := G_RET_STS_UNEXP_ERROR ;
5506
5507 IF FND_MSG_PUB.Check_Msg_Level
5508 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5509 THEN
5510 FND_MSG_PUB.Add_Exc_Msg
5511 ( G_PKG_NAME ,
5512 l_api_name
5513 );
5514 END IF;
5515
5516 FND_MSG_PUB.Count_And_Get
5517 ( p_count => x_msg_count ,
5518 p_data => x_msg_data
5519 );
5520
5521 END INSERT_IMPORTED_CHANGE_HISTORY ;
5522
5523
5524
5525 procedure INSERT_IMPORT_CHANGE_ROW (
5526 X_ROWID in out nocopy VARCHAR2,
5527 X_BATCH_ID in NUMBER,
5528 X_CHANGE_ID in NUMBER ,
5529 X_CREATION_DATE in DATE,
5530 X_CREATED_BY in NUMBER,
5531 X_LAST_UPDATE_DATE in DATE,
5532 X_LAST_UPDATED_BY in NUMBER,
5533 X_LAST_UPDATE_LOGIN in NUMBER
5534 ) is
5535
5536 cursor C is select ROWID from EGO_IMPORT_BATCH_CHANGES
5537 where BATCH_ID = X_BATCH_ID
5538 and CHANGE_ID = X_CHANGE_ID
5539 ;
5540
5541 begin
5542
5543 insert into EGO_IMPORT_BATCH_CHANGES (
5544 BATCH_ID,
5545 CHANGE_ID,
5546 OBJECT_VERSION_NUMBER,
5547 CREATION_DATE,
5548 CREATED_BY,
5549 LAST_UPDATE_DATE,
5550 LAST_UPDATED_BY,
5551 LAST_UPDATE_LOGIN
5552 ) values (
5553 X_BATCH_ID,
5554 X_CHANGE_ID,
5555 1.0,
5556 X_CREATION_DATE,
5557 X_CREATED_BY,
5558 X_LAST_UPDATE_DATE,
5559 X_LAST_UPDATED_BY,
5560 X_LAST_UPDATE_LOGIN
5561 );
5562
5563 open c;
5564 fetch c into X_ROWID;
5565 if (c%notfound) then
5566 close c;
5567 raise no_data_found;
5568 end if;
5569 close c;
5570
5571
5572 end INSERT_IMPORT_CHANGE_ROW;
5573
5574 /***************************
5575 procedure LOCK_IMPORT_CHANGE_ROW (
5576 X_BATCH_ID in NUMBER,
5577 X_CHANGE_ID in NUMBER,
5578 ) is
5579
5580 cursor c is select
5581 -- OBJECT_VERSION_NUMBER
5582 from EGO_IMPORT_BATCH_CHANGES
5583 where OBJECT_ID = X_BATCH_ID
5584 and CHANGE_ID = X_CHANGE_ID
5585 for update of OBJECT_ID nowait;
5586 recinfo c%rowtype;
5587
5588 begin
5589 open c;
5590 fetch c into recinfo;
5591 if (c%notfound) then
5592 close c;
5593 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
5594 app_exception.raise_exception;
5595 end if;
5596 close c;
5597
5598 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
5599 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
5600 ) then
5601 null;
5602 else
5603 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
5604 app_exception.raise_exception;
5605 end if;
5606
5607 return;
5608 end LOCK_IMPORT_CHANGE_ROW;
5609 ***************************/
5610
5611 /***************************
5612 procedure UPDATE_IMPORT_CHANGE_ROW (
5613 X_BATCH_ID in NUMBER,
5614 X_CHANGE_ID in NUMBER,
5615 X_LAST_UPDATE_DATE in DATE,
5616 X_LAST_UPDATED_BY in NUMBER,
5617 X_LAST_UPDATE_LOGIN in NUMBER
5618 ) is
5619
5620 begin
5621 update EGO_IMPORT_BATCH_CHANGES
5622 set -- XXXX
5623 where OBJECT_ID = X_BATCH_ID
5624 and CHANGE_ID = X_CHANGE_ID ;
5625
5626 if (sql%notfound) then
5627 raise no_data_found;
5628 end if;
5629
5630 end UPDATE_IMPORT_CHANGE_ROW;
5631 *******************************/
5632
5633
5634 procedure DELETE_IMPORT_CHANGE_ROW (
5635 X_BATCH_ID in NUMBER,
5636 X_CHANGE_ID in NUMBER
5637 ) is
5638 begin
5639
5640 delete from EGO_IMPORT_BATCH_CHANGES
5641 where BATCH_ID = X_BATCH_ID
5642 and CHANGE_ID = X_CHANGE_ID ;
5643
5644 if (sql%notfound) then
5645 raise no_data_found;
5646 end if;
5647 end DELETE_IMPORT_CHANGE_ROW ;
5648
5649
5650 /********************************************************************
5651 * API Type : Nulling out values for GDSN records
5652 * Purpose : Nulling out values for GDSN records
5653 *********************************************************************/
5654 FUNCTION Get_Nulled_out_Value(value IN VARCHAR2)
5655 RETURN VARCHAR2 IS
5656 l_null_out_value VARCHAR2(1000) ;
5657 BEGIN
5658 l_null_out_value := value;
5659 if ( value = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_CHAR)
5660 THEN
5661 l_null_out_value := EGO_ITEM_PUB.G_INTF_NULL_CHAR;
5662 END IF;
5663 return l_null_out_value;
5664 END Get_Nulled_out_Value;
5665
5666
5667 FUNCTION Get_Nulled_out_Value(value IN DATE)
5668 RETURN DATE IS
5669 l_null_out_value DATE ;
5670 BEGIN
5671 l_null_out_value := value;
5672 if ( value = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_DATE)
5673 THEN
5674 l_null_out_value := EGO_ITEM_PUB.G_INTF_NULL_DATE;
5675 END IF;
5676 return l_null_out_value;
5677 END Get_Nulled_out_Value;
5678
5679
5680 FUNCTION Get_Nulled_out_Value(value IN NUMBER)
5681 RETURN NUMBER IS
5682 l_null_out_value NUMBER ;
5683 BEGIN
5684 l_null_out_value := value;
5685 if ( value = ENG_CHANGE_ATTR_UTIL.G_ATTR_NULL_NUM)
5686 THEN
5687 l_null_out_value := EGO_ITEM_PUB.G_INTF_NULL_NUM;
5688 END IF;
5689 return l_null_out_value;
5690
5691 END Get_Nulled_out_Value;
5692
5693 procedure code_debug (msg IN VARCHAR2
5694 ,debug_level IN NUMBER default 3
5695 ) IS
5696 BEGIN
5697 null;
5698
5699 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
5700 IF NVL(FND_GLOBAL.conc_request_id, -1) <> -1 THEN
5701
5702 FND_FILE.put_line(which => FND_FILE.LOG
5703 ,buff => '[ENG_CHANGE_IMPORT_UTIL] '||msg);
5704 END IF;
5705 END IF;
5706 EXCEPTION
5707 WHEN OTHERS THEN
5708 NULL;
5709 END code_debug;
5710
5711 --ER 9489112, called in ChangeImportManager/ChangeImportUtil before UDA changes created
5712 --Used to update SYNC transaction type of rev level Change order controlled UDA from SYNC to CREATE OR UPDATE
5713 PROCEDURE Update_Rev_Level_Trans_Type(
5714 p_api_version IN NUMBER
5715 ,p_application_id IN NUMBER
5716 ,p_attr_group_type IN VARCHAR2
5717 ,p_object_name IN VARCHAR2
5718 ,p_data_set_id IN NUMBER
5719 ,p_entity_id IN NUMBER DEFAULT NULL
5720 ,p_entity_code IN VARCHAR2 DEFAULT NULL
5721 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
5722 ,x_return_status OUT NOCOPY VARCHAR2
5723 ,x_msg_data OUT NOCOPY VARCHAR2
5724 ) IS
5725 TYPE DYNAMIC_CUR IS REF CURSOR;
5726 TYPE DIST_MR_ATTR_GR_REC IS RECORD
5727 (
5728 ATTR_GROUP_INT_NAME VARCHAR2(30)
5729 );
5730
5731 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rev_Level_Trans_Type';
5732 wierd_constant VARCHAR2(100);
5733 wierd_constant_2 VARCHAR2(100);
5734 l_dynamic_sql VARCHAR2(32767);
5735 l_ext_table_select VARCHAR2(32767);
5736 l_dummy NUMBER;
5737
5738 l_pk1_column_name VARCHAR2(30);
5739 l_pk2_column_name VARCHAR2(30);
5740 l_pk3_column_name VARCHAR2(30);
5741 l_pk4_column_name VARCHAR2(30);
5742 l_pk5_column_name VARCHAR2(30);
5743 l_pk1_column_type VARCHAR2(8);
5744 l_pk2_column_type VARCHAR2(8);
5745 l_pk3_column_type VARCHAR2(8);
5746 l_pk4_column_type VARCHAR2(8);
5747 l_pk5_column_type VARCHAR2(8);
5748 l_item_rev_dl_id NUMBER;
5749 l_sync_count NUMBER;
5750 l_intf_column_name VARCHAR2(50);
5751 l_intf_tbl_select VARCHAR2(32767);
5752
5753
5754 l_attr_group_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
5755 l_attr_metadata_table EGO_ATTR_METADATA_TABLE;
5756 l_attr_metadata_table_sr EGO_ATTR_METADATA_TABLE;
5757 l_attr_metadata_table_1 EGO_ATTR_METADATA_TABLE;
5758
5759
5760 l_data_level_metadata_obj EGO_DATA_LEVEL_METADATA_OBJ;
5761
5762
5763 l_dynamic_cursor DYNAMIC_CUR;
5764 l_dynamic_dist_ag_cursor DYNAMIC_CUR;
5765 l_mr_dynamic_cursor DYNAMIC_CUR;
5766 l_attr_group_intf_rec DIST_MR_ATTR_GR_REC;
5767 begin
5768
5769 /* Bug 12939031 start */
5770 UPDATE ego_itm_usr_attr_intrfc ua1
5771 SET transaction_type = 'UPDATE'
5772 WHERE transaction_type = 'CREATE'
5773 AND data_set_id = p_data_set_id
5774 AND process_status = 5
5775 AND EXISTS (
5776 SELECT 1
5777 FROM ego_itm_usr_attr_intrfc ua2
5778 WHERE process_status = 4
5779 AND ua1.row_identifier = ua2.row_identifier
5780 AND data_set_id = p_data_set_id
5781 AND transaction_type = 'CREATE');
5782 /* Bug 12939031 End */
5783
5784 SELECT DATA_LEVEL_ID
5785 INTO l_item_rev_dl_id
5786 FROM EGO_DATA_LEVEL_B
5787 WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
5788 AND APPLICATION_ID = p_application_id
5789 AND DATA_LEVEL_NAME = 'ITEM_REVISION_LEVEL';
5790
5791
5792 --quit the procedure if no SYNC transtype found for rev level UDA with process_status 5
5793 select count(1) into l_sync_count
5794 from ego_itm_usr_attr_intrfc
5795 where transaction_type = 'SYNC'
5796 and data_level_id = l_item_rev_dl_id
5797 and process_status = 5;
5798 IF l_sync_count = 0 THEN
5799 code_debug (l_api_name ||' returning as there are no records to process ');
5800 x_return_status := FND_API.G_RET_STS_SUCCESS;
5801 RETURN;
5802 END IF;
5803
5804
5805 IF FND_API.To_Boolean(p_commit) THEN
5806 SAVEPOINT Update_Rev_Level_User_Attrs;
5807 END IF;
5808
5809 G_ENTITY_ID := p_entity_id;
5810
5811 BEGIN
5812 SELECT application_short_name
5813 INTO G_APPLICATION_CONTEXT
5814 FROM fnd_application
5815 WHERE application_id = p_application_id;
5816 EXCEPTION
5817 WHEN OTHERS THEN
5818 G_APPLICATION_CONTEXT := p_application_id;
5819 END;
5820
5821 SELECT NVL(p_entity_code, DECODE(G_ENTITY_ID, NULL, G_APPLICATION_CONTEXT||'_EXTFWK_USER_ATTRS',NULL))
5822 INTO G_ENTITY_CODE
5823 FROM DUAL;
5824
5825
5826 --Update SYNC/CREATE transaction type for Single Row AG to UPDATE/CREATE
5827 --
5828
5829 UPDATE EGO_ITM_USR_ATTR_INTRFC uai2
5830 SET uai2.transaction_type =
5831 DECODE ((SELECT COUNT (1)
5832 FROM ego_mtl_sy_items_ext_vl
5833 WHERE attr_group_id = uai2.attr_group_id
5834 AND ROWNUM < 2
5835 AND inventory_item_id = uai2.inventory_item_id
5836 AND organization_id = uai2.organization_id
5837 AND NVL (data_level_id, 9.97e125) =
5838 NVL (uai2.data_level_id, 9.97e125)
5839 AND NVL (pk1_value, 9.97e125) =
5840 NVL (uai2.pk1_value, 9.97e125)
5841 AND NVL (pk2_value, 9.97e125) =
5842 NVL (uai2.pk2_value, 9.97e125)
5843 AND NVL (revision_id, 9.97e125) =
5844 NVL (uai2.revision_id, 9.97e125)),
5845 0, EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE,
5846 EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE
5847 )
5848 WHERE uai2.data_set_id = p_data_set_id
5849 AND transaction_id IS NOT NULL
5850 AND uai2.attr_group_type =p_attr_group_type
5851 AND uai2.process_status = 5
5852 AND uai2.transaction_type in (EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE,EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE)
5853 AND NVL (uai2.data_level_id,9.97e125) = l_item_rev_dl_id
5854 AND (SELECT multi_row
5855 FROM ego_fnd_dsc_flx_ctx_ext flx_ext
5856 WHERE descriptive_flex_context_code = uai2.attr_group_int_name
5857 AND application_id = p_application_id
5858 AND descriptive_flexfield_name =p_attr_group_type) <>'Y' ;
5859 --Step3, update SYNC transaction type for Single Row AG to UPDATE/CREATE
5860 -- Need check unique key
5861 -----------------------------------------
5862 -- Fetch the PK column names and data --
5863 -- types for the passed-in object name --
5864 -----------------------------------------
5865 SELECT PK1_COLUMN_NAME, PK1_COLUMN_TYPE,
5866 PK2_COLUMN_NAME, PK2_COLUMN_TYPE,
5867 PK3_COLUMN_NAME, PK3_COLUMN_TYPE,
5868 PK4_COLUMN_NAME, PK4_COLUMN_TYPE,
5869 PK5_COLUMN_NAME, PK5_COLUMN_TYPE
5870 INTO l_pk1_column_name, l_pk1_column_type,
5871 l_pk2_column_name, l_pk2_column_type,
5872 l_pk3_column_name, l_pk3_column_type,
5873 l_pk4_column_name, l_pk4_column_type,
5874 l_pk5_column_name, l_pk5_column_type
5875 FROM FND_OBJECTS
5876 WHERE OBJ_NAME = p_object_name;
5877
5878 l_data_level_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Get_Data_Level_Metadata( p_data_level_id => l_item_rev_dl_id);
5879
5880 OPEN l_dynamic_dist_ag_cursor FOR
5881 'SELECT DISTINCT ATTR_GROUP_INT_NAME
5882 FROM EGO_ITM_USR_ATTR_INTRFC UAI1
5883 WHERE DATA_SET_ID = :data_set_id AND data_level_id = '||l_item_rev_dl_id||'
5884 AND transaction_id IS NOT NULL
5885 AND UAI1.PROCESS_STATUS = 5 AND UAI1.TRANSACTION_TYPE = '''||
5886 EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE||''''
5887 USING p_data_set_id;
5888 LOOP
5889 FETCH l_dynamic_dist_ag_cursor INTO l_attr_group_intf_rec;
5890 EXIT WHEN l_dynamic_dist_ag_cursor%NOTFOUND;
5891
5892 l_attr_group_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(
5893 p_attr_group_id => NULL
5894 ,p_application_id => p_application_id
5895 ,p_attr_group_type => p_attr_group_type
5896 ,p_attr_group_name => l_attr_group_intf_rec.ATTR_GROUP_INT_NAME
5897 );
5898 l_attr_metadata_table := l_attr_group_metadata_obj.ATTR_METADATA_TABLE;
5899 l_attr_metadata_table_sr := l_attr_group_metadata_obj.ATTR_METADATA_TABLE;
5900 l_attr_metadata_table_1 := l_attr_group_metadata_obj.ATTR_METADATA_TABLE;
5901
5902 IF (l_attr_group_metadata_obj.MULTI_ROW_CODE = 'Y') THEN
5903 l_ext_table_select := ' EGO_MTL_SY_ITEMS_EXT_VL EXTVL1 WHERE EXTVL1.ATTR_GROUP_ID='||l_attr_group_metadata_obj.ATTR_GROUP_ID||' ';
5904
5905 --LOOP THROUGH ALL THE ATTRS METADATA FOR THIS MR AG
5906 FOR i IN l_attr_metadata_table.FIRST .. l_attr_metadata_table.LAST
5907 LOOP
5908 IF (l_attr_metadata_table(i).UNIQUE_KEY_FLAG = 'Y' ) THEN
5909
5910 IF (l_attr_metadata_table(i).DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_NUMBER_DATA_TYPE ) THEN
5911 l_intf_column_name := ' ATTR_VALUE_NUM ';
5912 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM;
5913 ELSIF (l_attr_metadata_table(i).DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_DATE_DATA_TYPE OR l_attr_metadata_table(i).DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_DATE_TIME_DATA_TYPE ) THEN
5914 l_intf_column_name := ' ATTR_VALUE_DATE ';
5915 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_DATE;
5916 ELSE
5917 l_intf_column_name := ' ATTR_VALUE_STR ';
5918 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_STR;
5919 END IF;
5920
5921 l_intf_tbl_select :=
5922 ' (SELECT '||l_intf_column_name||' FROM EGO_ITM_USR_ATTR_INTRFC WHERE DATA_SET_ID = '||p_data_set_id||
5923 ' AND ATTR_GROUP_INT_NAME = '''||
5924 l_attr_group_metadata_obj.ATTR_GROUP_NAME||
5925 ''' AND ATTR_INT_NAME = '''||
5926 l_attr_metadata_table(i).ATTR_NAME||
5927 ''' AND ROW_IDENTIFIER = UAI1.ROW_IDENTIFIER';
5928
5929 l_intf_tbl_select := l_intf_tbl_select || ')';
5930
5931 l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_attr_metadata_table(i).DATABASE_COLUMN||','||wierd_constant||') = NVL('||l_intf_tbl_select||','||wierd_constant||')';
5932 END IF;
5933 END LOOP; -- to fetch Unique key for MR Row
5934
5935
5936 IF (l_pk1_column_name IS NOT NULL) THEN
5937 l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk1_column_name||' = UAI1.'||l_pk1_column_name;
5938 END IF;
5939 IF (l_pk2_column_name IS NOT NULL) THEN
5940 l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk2_column_name||' = UAI1.'||l_pk2_column_name;
5941 END IF;
5942 IF (l_pk3_column_name IS NOT NULL) THEN
5943 l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk3_column_name||' = UAI1.'||l_pk3_column_name;
5944 END IF;
5945 IF (l_pk4_column_name IS NOT NULL) THEN
5946 l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk4_column_name||' = UAI1.'||l_pk4_column_name;
5947 END IF;
5948 IF (l_pk5_column_name IS NOT NULL) THEN
5949 l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk5_column_name||' = UAI1.'||l_pk5_column_name;
5950 END IF;
5951
5952 l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.DATA_LEVEL_ID,'||EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM||') '||
5953 ' = NVL(UAI1.DATA_LEVEL_ID,'||EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM||')';
5954
5955 /* Append data level PK condition for Rev Item level - 43106*/
5956 IF (l_data_level_metadata_obj.PK_COLUMN_NAME1 IS NOT NULL AND l_data_level_metadata_obj.PK_COLUMN_NAME1 <> 'NONE'
5957 AND INSTR(l_ext_table_select,l_data_level_metadata_obj.PK_COLUMN_NAME1) = 0) THEN
5958
5959 IF(l_data_level_metadata_obj.PK_COLUMN_TYPE1 = 'NUMBER') THEN
5960 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM;
5961 ELSIF (l_data_level_metadata_obj.PK_COLUMN_TYPE1 = 'DATE' OR l_data_level_metadata_obj.PK_COLUMN_TYPE1 = 'DATETIME') THEN
5962 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_DATE;
5963 ELSE
5964 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_STR;
5965 END IF;
5966 l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_metadata_obj.PK_COLUMN_NAME1||','||wierd_constant||') '||
5967 ' = NVL(UAI1.'||l_data_level_metadata_obj.PK_COLUMN_NAME1||','||wierd_constant||')';
5968 END IF;
5969 IF (l_data_level_metadata_obj.PK_COLUMN_NAME2 IS NOT NULL AND l_data_level_metadata_obj.PK_COLUMN_NAME2 <> 'NONE'
5970 AND INSTR(l_ext_table_select,l_data_level_metadata_obj.PK_COLUMN_NAME2) = 0) THEN
5971 IF(l_data_level_metadata_obj.PK_COLUMN_TYPE2 = 'NUMBER') THEN
5972 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM;
5973 ELSIF (l_data_level_metadata_obj.PK_COLUMN_TYPE2 = 'DATE' OR l_data_level_metadata_obj.PK_COLUMN_TYPE2 = 'DATETIME') THEN
5974 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_DATE;
5975 ELSE
5976 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_STR;
5977 END IF;
5978 l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_metadata_obj.PK_COLUMN_NAME2||','||wierd_constant||') '||
5979 ' = NVL(UAI1.'||l_data_level_metadata_obj.PK_COLUMN_NAME2||','||wierd_constant||')';
5980 END IF;
5981 IF (l_data_level_metadata_obj.PK_COLUMN_NAME3 IS NOT NULL AND l_data_level_metadata_obj.PK_COLUMN_NAME3 <> 'NONE'
5982 AND INSTR(l_ext_table_select,l_data_level_metadata_obj.PK_COLUMN_NAME3) = 0) THEN
5983 IF(l_data_level_metadata_obj.PK_COLUMN_TYPE3 = 'NUMBER') THEN
5984 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM;
5985 ELSIF (l_data_level_metadata_obj.PK_COLUMN_TYPE3 = 'DATE' OR l_data_level_metadata_obj.PK_COLUMN_TYPE3 = 'DATETIME') THEN
5986 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_DATE;
5987 ELSE
5988 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_STR;
5989 END IF;
5990 l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_metadata_obj.PK_COLUMN_NAME3||','||wierd_constant||') '||
5991 ' = NVL(UAI1.'||l_data_level_metadata_obj.PK_COLUMN_NAME3||','||wierd_constant||')';
5992 END IF;
5993 IF (l_data_level_metadata_obj.PK_COLUMN_NAME4 IS NOT NULL AND l_data_level_metadata_obj.PK_COLUMN_NAME4 <> 'NONE'
5994 AND INSTR(l_ext_table_select,l_data_level_metadata_obj.PK_COLUMN_NAME4) = 0) THEN
5995 IF(l_data_level_metadata_obj.PK_COLUMN_TYPE4 = 'NUMBER') THEN
5996 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM;
5997 ELSIF (l_data_level_metadata_obj.PK_COLUMN_TYPE4 = 'DATE' OR l_data_level_metadata_obj.PK_COLUMN_TYPE4 = 'DATETIME') THEN
5998 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_DATE;
5999 ELSE
6000 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_STR;
6001 END IF;
6002 l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_metadata_obj.PK_COLUMN_NAME4||','||wierd_constant||') '||
6003 ' = NVL(UAI1.'||l_data_level_metadata_obj.PK_COLUMN_NAME4||','||wierd_constant||')';
6004 END IF;
6005 IF (l_data_level_metadata_obj.PK_COLUMN_NAME5 IS NOT NULL AND l_data_level_metadata_obj.PK_COLUMN_NAME5 <> 'NONE'
6006 AND INSTR(l_ext_table_select,l_data_level_metadata_obj.PK_COLUMN_NAME5) = 0) THEN
6007 IF(l_data_level_metadata_obj.PK_COLUMN_TYPE5 = 'NUMBER') THEN
6008 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM;
6009 ELSIF (l_data_level_metadata_obj.PK_COLUMN_TYPE5 = 'DATE' OR l_data_level_metadata_obj.PK_COLUMN_TYPE5 = 'DATETIME') THEN
6010 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_DATE;
6011 ELSE
6012 wierd_constant := EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_STR;
6013 END IF;
6014 l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_metadata_obj.PK_COLUMN_NAME5||','||wierd_constant||') '||
6015 ' = NVL(UAI1.'||l_data_level_metadata_obj.PK_COLUMN_NAME5||','||wierd_constant||')';
6016 END IF;
6017
6018 --------------------------------------------------
6019 -- Here we update the INTF table transaction_type
6020 -- from SYNC to CREATE or UPDATE
6021 --------------------------------------------------
6022 l_dynamic_sql :=
6023 ' UPDATE ego_itm_usr_attr_intrfc UAI1
6024 SET UAI1.TRANSACTION_TYPE = DECODE((SELECT COUNT(*) FROM '||l_ext_table_select||'),0,'''||
6025 EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''','''||
6026 EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''')
6027 WHERE UAI1.transaction_id IS NOT NULL AND UAI1.DATA_SET_ID = '||p_data_set_id||
6028 ' AND NVL(UAI1.DATA_LEVEL_ID,'||EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM ||') = '||l_item_rev_dl_id ||
6029 ' AND UAI1.ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||
6030 ''' AND UAI1.PROCESS_STATUS = 5 AND UAI1.TRANSACTION_TYPE IN ('''||EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE||''', '''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE ||''')';
6031 code_debug('l_dynamic_sql FOR MR '||l_attr_group_intf_rec.ATTR_GROUP_INT_NAME || ' IS:' ||l_dynamic_sql ,2);
6032 EXECUTE IMMEDIATE l_dynamic_sql;
6033 END IF;
6034 END LOOP;
6035 --NVL (uai2.data_level_id,9.97e125) = l_item_rev_dl_id
6036 EXCEPTION
6037 WHEN OTHERS THEN
6038 code_debug('######## Oops ... came into the when others block-'||SQLERRM ,2);
6039 IF FND_API.To_Boolean(p_commit) THEN
6040 ROLLBACK TO Update_Rev_Level_User_Attrs;
6041 END IF;
6042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6043 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
6044
6045 -----------------------------------------------------------------
6046 -- Get a default row identifier to use in logging this message --
6047 -----------------------------------------------------------------
6048 l_dynamic_sql :=
6049 'SELECT TRANSACTION_ID
6050 FROM EGO_ITM_USR_ATTR_INTRFC UAI1
6051 WHERE UAI1.DATA_SET_ID = :data_set_id
6052 AND ROWNUM = 1';
6053 EXECUTE IMMEDIATE l_dynamic_sql
6054 INTO l_dummy
6055 USING p_data_set_id;
6056 ERROR_HANDLER.Add_Error_Message(
6057 p_message_text => x_msg_data
6058 ,p_row_identifier => l_dummy
6059 ,p_application_id => 'EGO'
6060 ,p_message_type => FND_API.G_RET_STS_ERROR
6061 ,p_entity_id => G_ENTITY_ID
6062 ,p_table_name => 'ego_itm_usr_attr_intrfc'
6063 ,p_entity_code => G_ENTITY_CODE
6064 );
6065
6066 END Update_Rev_Level_Trans_Type;
6067
6068
6069 PROCEDURE IS_CHANGE_REC_EXIST_FOR_BAT (
6070 p_batch_id IN NUMBER,
6071 p_batch_type IN VARCHAR2 := NULL,
6072 p_process_entity IN VARCHAR2 := NULL,
6073 p_cm_process_type IN VARCHAR2 := NULL,
6074 x_change_rec_exist OUT NOCOPY NUMBER --1=YES, 2=NO
6075 )
6076 IS
6077
6078
6079 l_cm_import_option VARCHAR2(30) ;
6080 l_dyn_sql VARCHAR2(10000);
6081
6082 I PLS_INTEGER ;
6083 l_intf_table DBMS_SQL.VARCHAR2_TABLE;
6084 l_intf_batch_id_col DBMS_SQL.VARCHAR2_TABLE;
6085 l_intf_proc_flag_col DBMS_SQL.VARCHAR2_TABLE;
6086 l_intf_ri_seq_id_col DBMS_SQL.VARCHAR2_TABLE;
6087 l_intf_attr_grp_type DBMS_SQL.VARCHAR2_TABLE;
6088 l_intf_change_number_col DBMS_SQL.VARCHAR2_TABLE;
6089 l_dummy NUMBER := 0;
6090 l_exist NUMBER := 2;
6091
6092 BEGIN
6093
6094 -- Get Process Interface Table Definitions
6095 Get_Process_IntfTable_Def(p_process_entity => p_process_entity
6096 , x_intf_table => l_intf_table
6097 , x_intf_batch_id_col => l_intf_batch_id_col
6098 , x_intf_proc_flag_col => l_intf_proc_flag_col
6099 , x_intf_ri_seq_id_col => l_intf_ri_seq_id_col
6100 , x_intf_attr_grp_type => l_intf_attr_grp_type
6101 , x_intf_chg_notice_col => l_intf_change_number_col
6102 ) ;
6103
6104 -- Get Change Mgmt Import Option
6105 l_cm_import_option := GET_CM_IMPORT_OPTION(p_batch_id => p_batch_id) ;
6106
6107 FOR i IN 1..l_intf_table.COUNT LOOP
6108 l_dyn_sql := '';
6109 l_dyn_sql := ' SELECT count(*) ';
6110 l_dyn_sql := l_dyn_sql || ' FROM ' || l_intf_table(i) || ' INTF ';
6111 l_dyn_sql := l_dyn_sql || ' WHERE INTF.' || l_intf_batch_id_col(i) || ' = :BATCH_ID ' ;
6112 l_dyn_sql := l_dyn_sql || ' AND INTF.' || l_intf_proc_flag_col(i) || ' = ' || G_CM_TO_BE_PROCESSED ;
6113 l_dyn_sql := l_dyn_sql || ' AND ROWNUM = 1 ' ;
6114
6115 EXECUTE IMMEDIATE l_dyn_sql
6116 INTO l_dummy
6117 USING p_batch_id ;
6118
6119 --IF one table is found to have change records, it satisifies the condition. Exit loop
6120 IF l_dummy > 0 THEN
6121 EXIT;
6122 END IF;
6123
6124 END LOOP;
6125
6126 IF nvl(l_dummy,0) > 0 THEN
6127 x_change_rec_exist := 1;
6128 ELSE
6129 x_change_rec_exist := 2;
6130 END IF;
6131
6132 EXCEPTION
6133 WHEN OTHERS THEN
6134 x_change_rec_exist := 1; --non-fatal issue - continue the normal validation even if any error happens here
6135
6136
6137 END IS_CHANGE_REC_EXIST_FOR_BAT ;
6138
6139
6140
6141
6142 END ENG_CHANGE_IMPORT_UTIL ;