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