[Home] [Help]
PACKAGE BODY: APPS.ENG_PROPAGATION_LOG_UTIL
Source
1 PACKAGE BODY ENG_PROPAGATION_LOG_UTIL AS
2 /*$Header: ENGVPRLB.pls 120.6 2006/01/24 03:46:26 lkasturi noship $ */
3
4 ---------------------------------------------------------------
5 -- Global constant holding the package name --
6 ---------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_PROPAGATION_LOG_UTIL' ;
8
9 ---------------------------------------------------------------
10 G_ERROR_CLASSIFICATION_CODE CONSTANT VARCHAR2(10) := 'PROPAGATN';
11
12 ---------------------------------------------------------------
13 -- API Return Status . --
14 ---------------------------------------------------------------
15 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
16 G_RET_STS_WARNING CONSTANT VARCHAR2(1) := 'W';
17 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
18 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
19
20 G_LOG_LEVEL CONSTANT NUMBER := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
21 G_LOG_MODE VARCHAR2(3);
22
23 ---------------------------------------------------------------
24 -- Propagation Log Map Table --
25 ---------------------------------------------------------------
26 G_Entity_Map_Log_Table ENG_PROPAGATION_LOG_UTIL.Entity_Map_Log_Tbl_Type;
27 ----------------------------------------------------------
28 -- Write to Concurrent Log --
29 ----------------------------------------------------------
30 PROCEDURE Developer_Debug (
31 p_debug_message IN VARCHAR2
32 ) IS
33 l_err_msg VARCHAR2(240);
34 BEGIN
35 Fnd_File.Put_Line(which => Fnd_File.LOG,
36 buff => p_debug_message );
37 --Fnd_File.New_Line(which => Fnd_File.LOG );
38 EXCEPTION
39 WHEN OTHERS THEN
40 l_err_msg := SUBSTRB(SQLERRM, 1, 240);
41 Fnd_File.Put_Line(which => Fnd_File.LOG,
42 buff => l_err_msg );
43 END Developer_Debug;
44
45 --========================================================================
46 -- PROCEDURE : Log_Initialize PUBLIC
47 -- COMMENT : Initializes the log facility. It should be called from
48 -- the top level procedure of each concurrent program
49 --=======================================================================--
50 PROCEDURE Log_Initialize
51 IS
52 BEGIN
53 IF G_LOG_LEVEL IS NULL
54 THEN
55 G_LOG_MODE := 'OFF';
56 ELSIF (TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')) <> 0)
57 THEN
58 G_LOG_MODE := 'SRS';
59 ELSE
60 G_LOG_MODE := 'SQL';
61 END IF;
62 END Log_Initialize;
63
64
65 --========================================================================
66 -- PROCEDURE : Log PUBLIC
67 -- PARAMETERS: p_level IN priority of the message - from
68 -- highest to lowest:
69 -- -- G_LOG_ERROR
70 -- -- G_LOG_EXCEPTION
71 -- -- G_LOG_EVENT
72 -- -- G_LOG_PROCEDURE
73 -- -- G_LOG_STATEMENT
74 -- p_msg IN message to be print on the log
75 -- file
76 -- COMMENT : Add an entry to the log
77 --=======================================================================--
78 PROCEDURE Debug_Log
79 ( p_priority IN NUMBER
80 , p_msg IN VARCHAR2
81 )
82 IS
83 BEGIN
84 --Developer_Debug(p_debug_message => p_msg);
85 --Additional IF clause is added to print log message if Priority is
86 --Error or Exception. Bug: 3555234
87 IF ((p_priority = G_LOG_ERROR) OR
88 (p_priority = G_LOG_EXCEPTION) OR
89 (p_priority = G_LOG_PRINT) OR
90 ((G_LOG_MODE <> 'OFF') AND (p_priority >= G_LOG_LEVEL)))
91 THEN
92 IF G_LOG_MODE = 'SQL'
93 THEN
94 -- SQL*Plus session: uncomment the next line during unit test
95 -- DBMS_OUTPUT.put_line(p_msg);
96 NULL;
97 ELSE
98 -- Concurrent request
99 Developer_Debug(p_debug_message => p_msg);
100 END IF;
101 END IF;
102 EXCEPTION
103 WHEN OTHERS THEN
104 NULL;
105 END Debug_Log;
106
107
108 ----------------------------------------------------------
109 -- Validate Entity Map Record --
110 ----------------------------------------------------------
111 PROCEDURE Check_Entity_Map_Record (
112 p_entity_map_rec IN OUT NOCOPY Entity_Map_Log_Rec_Type
113 , x_return_status OUT NOCOPY VARCHAR2
114 ) IS
115 l_local_change_id NUMBER;
116 BEGIN
117 Debug_Log(G_LOG_PROCEDURE, 'Check_Entity_Map_Record.Begin');
118 x_return_status := G_RET_STS_SUCCESS;
119 IF p_entity_map_rec.change_id IS NULL
120 THEN
121 -- Change id cannot be null
122 x_return_status := G_RET_STS_ERROR;
123 Debug_Log(G_LOG_STATEMENT, 'p_entity_map_rec.change_id IS NULL x_return_status'|| x_return_status);
124 ELSIF p_entity_map_rec.local_organization_id IS NULL
125 THEN
126 -- Local Org Id cannot be null
127 x_return_status := G_RET_STS_ERROR;
128 Debug_Log(G_LOG_STATEMENT, 'p_entity_map_rec.local_organization_id IS NULL x_return_status'||x_return_status);
129 ELSIF p_entity_map_rec.revised_line_type IS NOT NULL AND p_entity_map_rec.revised_line_id1 IS NULL
130 THEN
131 -- Dependant params revised_line_type and revised_line_id1
132 x_return_status := G_RET_STS_ERROR;
133 Debug_Log(G_LOG_STATEMENT, 'p_entity_map_rec.revised line IS NULL x_return_status'|| x_return_status);
134 END IF;
135
136 IF x_return_status = G_RET_STS_ERROR
137 THEN
138 Debug_Log(G_LOG_PROCEDURE, 'Check_Entity_Map_Record.End');
139 RETURN;
140 END IF;
141
142 BEGIN
143 SELECT change_id
144 INTO l_local_change_id
145 FROM eng_engineering_changes
146 WHERE change_id =
147 (SELECT object_to_id1
148 FROM eng_change_obj_relationships
149 WHERE change_id = p_entity_map_rec.change_id
150 AND (relationship_code = 'PROPAGATED_TO' OR relationship_code = 'TRANSFERRED_TO')
151 AND object_to_name = 'ENG_CHANGE'
152 AND object_to_id3 = p_entity_map_rec.local_organization_id);
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 l_local_change_id := NULL;
156 END;
157
158 IF nvl(l_local_change_id, -1) <> nvl(p_entity_map_rec.local_change_id, -1)
159 THEN
160 -- Local Change Id is incorrect, reset to correct value
161 Debug_Log(G_LOG_STATEMENT, 'Value l_local_change_id:'|| l_local_change_id);
162 p_entity_map_rec.local_change_id := l_local_change_id;
163 END IF;
164 Debug_Log(G_LOG_PROCEDURE, 'Check_Entity_Map_Record.End');
165
166 END Check_Entity_Map_Record;
167
168 ----------------------------------------------------------
169 -- Validate Entity Map Record --
170 ----------------------------------------------------------
171 PROCEDURE Check_Entity_Map_Existance (
172 p_change_id IN NUMBER
173 , p_entity_name IN eng_change_propagation_maps.entity_name%TYPE
174 , p_revised_item_sequence_id IN NUMBER := NULL
175 , p_revised_line_type IN eng_change_propagation_maps.revised_line_type%TYPE := NULL
176 , p_revised_line_id1 IN eng_change_propagation_maps.revised_line_id1%TYPE := NULL
177 , p_revised_line_id2 IN eng_change_propagation_maps.revised_line_id2%TYPE := NULL
178 , p_revised_line_id3 IN eng_change_propagation_maps.revised_line_id3%TYPE := NULL
179 , p_revised_line_id4 IN eng_change_propagation_maps.revised_line_id4%TYPE := NULL
180 , p_revised_line_id5 IN eng_change_propagation_maps.revised_line_id5%TYPE := NULL
181 , p_local_organization_id IN NUMBER
182 , x_change_map_id OUT NOCOPY NUMBER
183 )
184 IS
185 CURSOR c_check_entity_map IS
186 SELECT ecpm.change_propagation_map_id
187 FROM eng_change_propagation_maps ecpm
188 WHERE ecpm.change_id = p_change_id
189 AND ecpm.local_organization_id = p_local_organization_id
190 AND ecpm.entity_name = p_entity_name
191 AND nvl(ecpm.revised_item_sequence_id, -1) = nvl(p_revised_item_sequence_id, -1)
192 AND nvl(ecpm.revised_line_type, '-1') = nvl(p_revised_line_type, '-1')
193 AND nvl(ecpm.revised_line_id1, -1) = nvl(p_revised_line_id1, -1)
194 AND nvl(ecpm.revised_line_id2, -1) = nvl(p_revised_line_id2, -1)
195 AND nvl(ecpm.revised_line_id3, -1) = nvl(p_revised_line_id3, -1)
196 AND nvl(ecpm.revised_line_id4, -1) = nvl(p_revised_line_id4, -1)
197 AND nvl(ecpm.revised_line_id5, -1) = nvl(p_revised_line_id5, -1);
198 BEGIN
199 -- Initialize Out variable
200 x_change_map_id := null;
201
202 -- Query for change_map_id
203 OPEN c_check_entity_map;
204 FETCH c_check_entity_map INTO x_change_map_id;
205 CLOSE c_check_entity_map;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 IF c_check_entity_map%ISOPEN
210 THEN
211 CLOSE c_check_entity_map;
212 END IF;
213
214 END Check_Entity_Map_Existance;
215
216
217 PROCEDURE Perform_Writes_For_Entity_Map (
218 p_entity_map_rec IN OUT NOCOPY Entity_Map_Log_Rec_Type
219 ) IS
220 BEGIN
221 Check_Entity_Map_Existance(
222 p_change_id => p_entity_map_rec.change_id
223 , p_entity_name => p_entity_map_rec.entity_name
224 , p_revised_item_sequence_id => p_entity_map_rec.revised_item_sequence_id
225 , p_revised_line_type => p_entity_map_rec.revised_line_type
226 , p_revised_line_id1 => p_entity_map_rec.revised_line_id1
227 , p_revised_line_id2 => p_entity_map_rec.revised_line_id2
228 , p_revised_line_id3 => p_entity_map_rec.revised_line_id3
229 , p_revised_line_id4 => p_entity_map_rec.revised_line_id4
230 , p_revised_line_id5 => p_entity_map_rec.revised_line_id5
231 , p_local_organization_id => p_entity_map_rec.local_organization_id
232 , x_change_map_id => p_entity_map_rec.change_propagation_map_id
233 );
234 --
235 -- If map id is fetched , then update the same record
236 -- Insert a new map otherwise
237 --
238 IF p_entity_map_rec.change_propagation_map_id IS NOT NULL
239 THEN
240 UPDATE eng_change_propagation_maps
241 SET local_change_id = p_entity_map_rec.local_change_id
242 , local_revised_item_sequence_id = p_entity_map_rec.local_revised_item_seq_id
243 , local_revised_line_id1 = p_entity_map_rec.local_revised_line_id1
244 , local_revised_line_id2 = p_entity_map_rec.local_revised_line_id2
245 , local_revised_line_id3 = p_entity_map_rec.local_revised_line_id3
246 , local_revised_line_id4 = p_entity_map_rec.local_revised_line_id4
247 , local_revised_line_id5 = p_entity_map_rec.local_revised_line_id5
248 , entity_action_status = p_entity_map_rec.entity_action_status
249 , creation_date = SYSDATE
250 , created_by = FND_GLOBAL.USER_ID
251 , last_update_date = SYSDATE
252 , last_updated_by = FND_GLOBAL.USER_ID
253 , last_update_login = FND_GLOBAL.LOGIN_ID
254 , program_id = FND_GLOBAL.CONC_PROGRAM_ID--FND_PROFILE.value('CONC_PROGRAM_ID')
255 , program_application_id = FND_GLOBAL.PROG_APPL_ID--FND_PROFILE.value('PROG_APPL_ID')
256 , program_update_date = SYSDATE
257 , request_id = FND_GLOBAL.CONC_REQUEST_ID--FND_PROFILE.value('CONC_REQUEST_ID')
258 WHERE change_propagation_map_id = p_entity_map_rec.change_propagation_map_id;
259 ELSE
260 SELECT eng_change_propagation_maps_s.nextval
261 INTO p_entity_map_rec.change_propagation_map_id
262 FROM DUAL;
263
264 INSERT INTO eng_change_propagation_maps(
265 change_propagation_map_id
266 , change_id
267 , revised_item_sequence_id
268 , local_change_id
269 , local_revised_item_sequence_id
270 , local_organization_id
271 , program_id
272 , program_application_id
273 , program_update_date
274 , request_id
275 , revised_line_type
276 , revised_line_id1
277 , revised_line_id2
278 , revised_line_id3
279 , revised_line_id4
280 , revised_line_id5
281 , local_revised_line_id1
282 , local_revised_line_id2
283 , local_revised_line_id3
284 , local_revised_line_id4
285 , local_revised_line_id5
286 , entity_name
287 , entity_action_status
288 , creation_date
289 , created_by
290 , last_update_date
291 , last_updated_by
292 , last_update_login
293 )
294 VALUES(
295 p_entity_map_rec.change_propagation_map_id
296 , p_entity_map_rec.CHANGE_ID
297 , p_entity_map_rec.revised_item_sequence_id
298 , p_entity_map_rec.local_change_id
299 , p_entity_map_rec.local_revised_item_seq_id
300 , p_entity_map_rec.LOCAL_ORGANIZATION_ID
301 , FND_GLOBAL.CONC_PROGRAM_ID--FND_PROFILE.value('CONC_PROGRAM_ID')
302 , FND_GLOBAL.PROG_APPL_ID--FND_PROFILE.value('PROG_APPL_ID')
303 , SYSDATE
304 , FND_GLOBAL.CONC_REQUEST_ID--FND_PROFILE.value('CONC_REQUEST_ID')
305 , p_entity_map_rec.revised_line_type
306 , p_entity_map_rec.revised_line_id1
307 , p_entity_map_rec.revised_line_id2
308 , p_entity_map_rec.revised_line_id3
309 , p_entity_map_rec.revised_line_id4
310 , p_entity_map_rec.revised_line_id5
311 , p_entity_map_rec.local_revised_line_id1
312 , p_entity_map_rec.local_revised_line_id2
313 , p_entity_map_rec.local_revised_line_id3
314 , p_entity_map_rec.local_revised_line_id4
315 , p_entity_map_rec.local_revised_line_id5
316 , p_entity_map_rec.ENTITY_NAME
317 , p_entity_map_rec.entity_action_status
318 , SYSDATE
319 , FND_GLOBAL.USER_ID
320 , SYSDATE
321 , FND_GLOBAL.USER_ID
322 , FND_GLOBAL.LOGIN_ID
323 );
324 END IF;
325 EXCEPTION
326 WHEN OTHERS THEN
327 null;
328 Developer_Debug('Unexpected Error in Perform_Writes_For_Entity_Map'|| SQLERRM);
329 END Perform_Writes_For_Entity_Map;
330
331 ----------------------------------------------------------
332 -- Write to Log Table --
333 ----------------------------------------------------------
334
335 PROCEDURE Perform_Writes_For_Log (
336 p_entity_map_rec IN Entity_Map_Log_Rec_Type
337 , p_delete_map_logs IN VARCHAR2 := FND_API.G_TRUE
338 , x_return_status IN OUT NOCOPY VARCHAR2
339 )
340 IS
341 CURSOR c_change_logs (
342 cp_change_id NUMBER
343 , cp_local_org_id NUMBER
344 , cp_change_map_id NUMBER)
345 IS
346 SELECT change_log_id
347 FROM eng_change_logs_b
348 WHERE change_id = cp_change_id
349 AND local_organization_id = cp_local_org_id
350 AND log_type_code <> 'INFO'
351 AND FND_API.G_TRUE = p_delete_map_logs
352 AND change_propagation_map_id = cp_change_map_id;
353
354 l_new_log_id NUMBER;
355 l_val VARCHAR2(200);
356 l_log_type_code eng_change_logs_b.log_type_code%TYPE;
357 BEGIN
358
359 --
360 -- Delete Log messages if required.
361 -- In future this step may have to be changed to inactivate the old log messages
362 -- so as to maintain history
363 --
364 Debug_Log(G_LOG_PROCEDURE, 'Perform_Writes_For_Log.Begin');
365
366 Debug_Log(G_LOG_STATEMENT, 'Delete Errors for Map p_entity_map_rec.change_propagation_map_id'|| p_entity_map_rec.change_propagation_map_id);
367 FOR clog IN c_change_logs( cp_change_id => p_entity_map_rec.change_id
368 , cp_local_org_id => p_entity_map_rec.local_organization_id
369 , cp_change_map_id => p_entity_map_rec.change_propagation_map_id)
370 LOOP
371
372 Eng_change_logs_pkg.Delete_row (
373 X_change_log_id => clog.change_log_id
374 );
375 END LOOP;
376
377 --
378 -- Loop through the specified message list and insert
379 -- the messages corresponding to the change_map_id
380 --
381 FOR i IN 1..p_entity_map_rec.message_list.COUNT
382 LOOP
383 SELECT eng_change_logs_s.nextval
384 INTO l_new_log_id
385 FROM dual;
386 l_log_type_code := CASE p_entity_map_rec.message_list(i).message_type
387 WHEN 'W' THEN G_LOG_TYPE_WARNING
388 WHEN 'I' THEN G_LOG_TYPE_INFO
389 ELSE G_LOG_TYPE_ERROR END;
390 Debug_Log(G_LOG_STATEMENT, 'Log type code:'|| l_log_type_code);
391 Debug_Log(G_LOG_STATEMENT, 'Message:'|| p_entity_map_rec.message_list(i).message_text);
392
393 Eng_change_logs_pkg.Insert_row (
394 X_rowid => l_val
395 , X_change_log_id => l_new_log_id
396 , X_change_id => p_entity_map_rec.change_id
397 , X_change_line_id => null
398 , X_local_revised_item_sequence_ => p_entity_map_rec.local_revised_item_seq_id
399 , X_log_classification_code => G_ERROR_CLASSIFICATION_CODE
400 , X_log_type_code => l_log_type_code
401 , X_local_change_id => p_entity_map_rec.local_change_id
402 , X_local_change_line_id => null
403 , X_revised_item_sequence_id => p_entity_map_rec.revised_item_sequence_id
404 , X_local_organization_id => p_entity_map_rec.local_organization_id
405 , X_log_text => p_entity_map_rec.message_list(i).message_text
406 , X_creation_date => SYSDATE
407 , X_created_by => FND_GLOBAL.USER_ID
408 , X_last_update_date => SYSDATE
409 , X_last_updated_by => FND_GLOBAL.USER_ID
410 , X_last_update_login => FND_GLOBAL.LOGIN_ID
411 , X_change_propagation_map_id => p_entity_map_rec.change_propagation_map_id
412 );
413
414 END LOOP; -- End of 1..p_message_list.COUNT
415 Debug_Log(G_LOG_PROCEDURE, 'Perform_Writes_For_Log.End');
416 EXCEPTION
417 WHEN OTHERS THEN
418 null;
419 Developer_Debug('Unexpected Error in Perform_Writes_For_Log'|| SQLERRM);
420 END Perform_Writes_For_Log;
421
422
423 ----------------------------------------------------------
424 -- Propagation messages Logging --
425 ----------------------------------------------------------
426 PROCEDURE Write_Propagation_Log (
427 p_entity_map_rec IN OUT NOCOPY Entity_Map_Log_Rec_Type
428 ) IS
429 l_return_status VARCHAR2(1);
430 l_entity_map_rec Entity_Map_Log_Rec_Type;
431
432 BEGIN
433 Debug_Log(G_LOG_PROCEDURE, 'Write_Propagation_Log.Begin');
434 Debug_Log(G_LOG_STATEMENT, 'Write_Propagation_Log.Organization_id'|| p_entity_map_rec.local_organization_id);
435 Debug_Log(G_LOG_STATEMENT, 'Write_Propagation_Log.Processing Entity'|| nvl(p_entity_map_rec.revised_line_type, p_entity_map_rec.entity_name));
436
437 l_entity_map_rec := p_entity_map_rec;
438 -- Check whether the Entity Map Record is Well-formed
439 Check_Entity_Map_Record(
440 p_entity_map_rec => l_entity_map_rec
441 , x_return_status => l_return_status
442 );
443
444 IF l_return_status = G_RET_STS_ERROR
445 THEN
446 -- return processing as the entity rec is not well formed
447 RETURN;
448 END IF;
449
450 Perform_Writes_For_Entity_Map(
451 p_entity_map_rec => l_entity_map_rec
452 );
453
454 Perform_Writes_For_Log(
455 p_entity_map_rec => l_entity_map_rec
456 , p_delete_map_logs => FND_API.G_TRUE
457 , x_return_status => l_return_status
458 );
459 Debug_Log(G_LOG_PROCEDURE, 'Write_Propagation_Log.End');
460 EXCEPTION
461 WHEN OTHERS THEN
462 null;
463 Debug_Log(G_LOG_ERROR, 'Write_Propagation_Log.Unexpected Error'|| SQLERRM);
464 END Write_Propagation_Log;
465
466 PROCEDURE Reset_Entity_Map_Record (
467 p_entity_level IN NUMBER
468 , p_entity_map_rec IN OUT NOCOPY Entity_Map_Log_Rec_Type
469 ) IS
470
471 BEGIN
472 --
473 -- Always reset change_map_id and entity_action_status
474 --
475 p_entity_map_rec.change_propagation_map_id := null;
476 p_entity_map_rec.entity_action_status := null;
477
478 -- Now set other attributes of the record based on the entity level
479 IF p_entity_level = Error_Handler.G_BO_LEVEL
480 THEN
481 p_entity_map_rec.change_id := null;
482 END IF;
483
484 IF p_entity_level <= Error_Handler.G_ECO_LEVEL
485 THEN
486 p_entity_map_rec.local_change_id := null;
487 p_entity_map_rec.local_organization_id := null;
488 END IF;
489
490 IF p_entity_level <= Error_Handler.G_RI_LEVEL
491 THEN
492 p_entity_map_rec.revised_item_sequence_id := null;
493 p_entity_map_rec.local_revised_item_seq_id := null;
494 END IF;
495
496 IF p_entity_level <= Error_Handler.G_RC_LEVEL
497 THEN
498 p_entity_map_rec.revised_line_type := null;
499 p_entity_map_rec.revised_line_id1 := null;
500 p_entity_map_rec.revised_line_id2 := null;
501 p_entity_map_rec.revised_line_id3 := null;
502 p_entity_map_rec.revised_line_id4 := null;
503 p_entity_map_rec.revised_line_id5 := null;
504 p_entity_map_rec.local_revised_line_id1 := null;
505 p_entity_map_rec.local_revised_line_id2 := null;
506 p_entity_map_rec.local_revised_line_id3 := null;
507 p_entity_map_rec.local_revised_line_id4 := null;
508 p_entity_map_rec.local_revised_line_id5 := null;
509 END IF;
510 -- Delete message list
511 p_entity_map_rec.message_list.DELETE;
512 END Reset_Entity_Map_Record;
513
514 /*********************************************************************
515 * Procedure : Initialize
516 * Parameters : None
517 * Purpose : This procedure will initialize the global message
518 * list and reset the index variables to 0.
519 * User must initialize the message list before using
520 * it.
521 **********************************************************************/
522 PROCEDURE Initialize
523 IS
524 BEGIN
525 G_Entity_Map_Log_Table.DELETE;
526 END Initialize;
527
528 ----------------------------------------------------------
529 -- Propagation messages Logging --
530 ----------------------------------------------------------
531 PROCEDURE Write_Propagation_Log IS
532
533 BEGIN
534
535 FOR i IN 1..G_Entity_Map_Log_Table.COUNT
536 LOOP
537 Write_Propagation_Log(G_Entity_Map_Log_Table(i));
538 END LOOP;
539 Initialize;
540
541 END;
542
543 PROCEDURE Add_Entity_Map (
544 p_change_id IN NUMBER
545 , p_revised_item_sequence_id IN NUMBER := NULL
546 , p_revised_line_type IN eng_change_propagation_maps.revised_line_type%TYPE := NULL
547 , p_revised_line_id1 IN eng_change_propagation_maps.revised_line_id1%TYPE := NULL
548 , p_revised_line_id2 IN eng_change_propagation_maps.revised_line_id2%TYPE := NULL
549 , p_revised_line_id3 IN eng_change_propagation_maps.revised_line_id3%TYPE := NULL
550 , p_revised_line_id4 IN eng_change_propagation_maps.revised_line_id4%TYPE := NULL
551 , p_revised_line_id5 IN eng_change_propagation_maps.revised_line_id5%TYPE := NULL
552 , p_local_organization_id IN NUMBER
553 , p_local_change_id IN NUMBER := NULL
554 , p_local_revised_item_seq_id IN NUMBER := NULL
555 , p_local_revised_line_id1 IN eng_change_propagation_maps.local_revised_line_id1%TYPE := NULL
556 , p_local_revised_line_id2 IN eng_change_propagation_maps.local_revised_line_id2%TYPE := NULL
557 , p_local_revised_line_id3 IN eng_change_propagation_maps.local_revised_line_id3%TYPE := NULL
558 , p_local_revised_line_id4 IN eng_change_propagation_maps.local_revised_line_id4%TYPE := NULL
559 , p_local_revised_line_id5 IN eng_change_propagation_maps.local_revised_line_id5%TYPE := NULL
560 , p_entity_name IN eng_change_propagation_maps.entity_name%TYPE
561 , p_entity_action_status IN NUMBER
562 , p_bo_entity_identifier IN VARCHAR2
563 ) IS
564 l_Nxt_Idx NUMBER;
565 l_message_list Error_Handler.Error_Tbl_Type;
566 l_Nxt_Mesg_Cnt NUMBER;
567 l_error_table Error_Handler.Error_Tbl_Type;
568 BEGIN
569 l_Nxt_Idx := G_Entity_Map_Log_Table.COUNT+1;
570
571 G_Entity_Map_Log_Table(l_Nxt_Idx).change_id := p_change_id;
572 G_Entity_Map_Log_Table(l_Nxt_Idx).revised_item_sequence_id := p_revised_item_sequence_id;
573 G_Entity_Map_Log_Table(l_Nxt_Idx).revised_line_type := p_revised_line_type;
574 G_Entity_Map_Log_Table(l_Nxt_Idx).revised_line_id1 := p_revised_line_id1;
575 G_Entity_Map_Log_Table(l_Nxt_Idx).revised_line_id2 := p_revised_line_id2;
576 G_Entity_Map_Log_Table(l_Nxt_Idx).revised_line_id3 := p_revised_line_id3;
577 G_Entity_Map_Log_Table(l_Nxt_Idx).revised_line_id4 := p_revised_line_id4;
578 G_Entity_Map_Log_Table(l_Nxt_Idx).revised_line_id5 := p_revised_line_id5;
579 G_Entity_Map_Log_Table(l_Nxt_Idx).local_organization_id := p_local_organization_id;
580 G_Entity_Map_Log_Table(l_Nxt_Idx).local_change_id := p_local_change_id;
581 G_Entity_Map_Log_Table(l_Nxt_Idx).local_revised_item_seq_id := p_local_revised_item_seq_id;
582 G_Entity_Map_Log_Table(l_Nxt_Idx).local_revised_line_id1 := p_local_revised_line_id1;
583 G_Entity_Map_Log_Table(l_Nxt_Idx).local_revised_line_id2 := p_local_revised_line_id2;
584 G_Entity_Map_Log_Table(l_Nxt_Idx).local_revised_line_id3 := p_local_revised_line_id3;
585 G_Entity_Map_Log_Table(l_Nxt_Idx).local_revised_line_id4 := p_local_revised_line_id4;
586 G_Entity_Map_Log_Table(l_Nxt_Idx).local_revised_line_id5 := p_local_revised_line_id5;
587 G_Entity_Map_Log_Table(l_Nxt_Idx).entity_name := p_entity_name;
588 G_Entity_Map_Log_Table(l_Nxt_Idx).entity_action_status := p_entity_action_status;
589
590 Debug_Log(G_LOG_STATEMENT, 'Number of messages'||Error_Handler.Get_Message_Count);
591 Debug_Log(G_LOG_STATEMENT, 'p_bo_entity_identifier'||p_bo_entity_identifier);
592 Error_Handler.Get_Entity_Message(
593 p_entity_id => p_bo_entity_identifier
594 , x_message_list => G_Entity_Map_Log_Table(l_Nxt_Idx).message_list);
595
596 -- Special handling for Revised component level to log all the children messages
597 IF p_bo_entity_identifier = 'RC'--Eco_Error_Handler.G_RC_LEVEL--Fixed for bug 4968251
598 THEN
599 -- Generally, the message count for component will be minimal. So no issues
600 l_Nxt_Mesg_Cnt := G_Entity_Map_Log_Table(l_Nxt_Idx).message_list.COUNT+1;
601 Error_Handler.Get_Entity_Message(
602 p_entity_id => Eco_Error_Handler.G_RD_LEVEL
603 , x_message_list => l_message_list);
604 FOR i IN 1..l_message_list.COUNT
605 LOOP
606 G_Entity_Map_Log_Table(l_Nxt_Idx).message_list(l_Nxt_Mesg_Cnt) := l_message_list(i);
607 l_Nxt_Mesg_Cnt := l_Nxt_Mesg_Cnt+1;
608
609 END LOOP;
610 l_message_list.delete;
611 Error_Handler.Get_Entity_Message(
612 p_entity_id => Eco_Error_Handler.G_SC_LEVEL
613 , x_message_list => l_message_list);
614 FOR i IN 1..l_message_list.COUNT
615 LOOP
616 G_Entity_Map_Log_Table(l_Nxt_Idx).message_list(l_Nxt_Mesg_Cnt) := l_message_list(i);
617 l_Nxt_Mesg_Cnt := l_Nxt_Mesg_Cnt+1;
618 END LOOP;
619 END IF;
620
621 /*FOR i IN 1..G_Entity_Map_Log_Table(l_Next_Idx).message_list.COUNT
622 LOOP
623 Debug_Log(G_LOG_STATEMENT, 'there are messages now p_revised_item_sequence_id'||p_revised_item_sequence_id);
624 END LOOP;*/ --For Debugging
625 IF p_bo_entity_identifier = 'ECO'
626 THEN
627 Error_Handler.Get_Message_List( x_message_list => l_error_table);
628 FOR i IN 1..l_error_table.COUNT
629 LOOP
630 FND_FILE.New_line(which => Fnd_File.LOG );
631 FND_FILE.PUT_LINE(FND_FILE.LOG,'Entity Id: '||l_error_table(i).entity_id);
632 FND_FILE.PUT_LINE(FND_FILE.LOG,'Index: '||l_error_table(i).entity_index);
633 FND_FILE.PUT_LINE(FND_FILE.LOG,'Mesg: '||l_error_table(i).message_text);
634 END LOOP;
635 END IF;
636 EXCEPTION
637 WHEN OTHERS THEN
638 Debug_Log(G_LOG_ERROR, 'Error occurred in Add_Entity_Map for p_bo_entity_identifier:'||p_bo_entity_identifier||' Error:'||SQLERRM);
639 END Add_Entity_Map;
640
641 PROCEDURE Mark_Component_Change_Transfer (
642 p_api_version IN NUMBER
643 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
644 , p_commit IN VARCHAR2 := FND_API.G_FALSE
645 , x_return_status OUT NOCOPY VARCHAR2 --
646 , x_msg_count OUT NOCOPY NUMBER --
647 , x_msg_data OUT NOCOPY VARCHAR2 --
648 , p_change_id IN NUMBER
649 , p_revised_item_sequence_id IN NUMBER
650 , p_component_sequence_id IN NUMBER
651 , p_local_organization_id IN NUMBER
652
653 ) IS
654 l_api_name CONSTANT VARCHAR2(30) := 'Mark_Component_Change_Transfer';
655 l_api_version CONSTANT NUMBER := 1.0;
656
657 BEGIN
658
659 -- Standard Start of API savepoint
660 SAVEPOINT Mark_Component_Change_Transfer;
661
662 -- Standard call to check for call compatibility
663 IF NOT FND_API.Compatible_API_Call ( l_api_version
664 ,p_api_version
665 ,l_api_name
666 ,G_PKG_NAME )
667 THEN
668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669 END IF;
670
671 -- Initialize message list if p_init_msg_list is set to TRUE.
672 IF FND_API.to_Boolean( p_init_msg_list )
673 THEN
674 FND_MSG_PUB.initialize;
675 END IF ;
676
677 Initialize;
678 Add_Entity_Map(
679 p_change_id => p_change_id
680 , p_revised_item_sequence_id => p_revised_item_sequence_id
681 , p_revised_line_type => Eng_Propagation_Log_Util.G_REV_LINE_CMP_CHG
682 , p_revised_line_id1 => p_component_sequence_id
683 , p_local_organization_id => p_local_organization_id
684 , p_entity_name => Eng_Propagation_Log_Util.G_ENTITY_REVISED_LINE
685 , p_entity_action_status => 4
686 , p_bo_entity_identifier => 'RC'--Eco_Error_Handler.G_RC_LEVEL
687 );
688 Write_Propagation_Log;
689
690 -- Standard ending code ------------------------------------------------
691 IF FND_API.To_Boolean ( p_commit )
692 THEN
693 COMMIT WORK;
694 END IF;
695 EXCEPTION
696 WHEN OTHERS THEN
697 ROLLBACK TO Mark_Component_Change_Transfer;
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
699 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
700 THEN
701 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
702 END IF;
703 FND_MSG_PUB.Count_And_Get(
704 p_count => x_msg_count
705 , p_data => x_msg_data );
706
707 END Mark_Component_Change_Transfer;
708
709 FUNCTION Get_Composite_Logs_For_Map (
710 p_change_propagation_map_id IN NUMBER
711 ) RETURN VARCHAR2 IS
712 CURSOR c_get_logs IS
713 SELECT eclv.log_text
714 FROM eng_change_logs_vl eclv
715 WHERE eclv.change_propagation_map_id = p_change_propagation_map_id;
716 l_composite_log_text VARCHAR2(20000);
717 BEGIN
718 FOR cgl IN c_get_logs
719 LOOP
720 l_composite_log_text := l_composite_log_text||'
721 '||cgl.log_text;
722 -- Added in next line for new line in UI.. do not Indent.
723 END LOOP;
724 RETURN l_composite_log_text;
725 EXCEPTION
726 WHEN OTHERS THEN
727 RETURN NULL;
728 END Get_Composite_Logs_For_Map;
729
730 -- bug 4704390
731 /******************************************************************************
732 * Procedure : Get_Propagate_Action_Flag
733 * Parameters : p_conc_request_phase_code IN VARCHAR2
734 * , p_entity_action_status IN NUMBER
735 * , p_global_change_id IN NUMBER
736 * , p_local_organization_id IN NUMBER
737 *
738 * Purpose : This function is used to fetch the propagate action flag to
739 * determine if propagation is to be allowed or not for a given
740 * header and a local organization.
741 * Case 1 : Entity action status is 1 : G_PRP_PRC_STS_SUCCESS
742 * Sub case a: Propagation request has not been submitted
743 * This can happen in case of create new for Copy
744 * Structure process.
745 * In this case the phase code of the request is null
746 * Sub case b: Propagation request has been submitted and completed
747 * In these cases , check if there exists new revised items.
748 * If Yes, enable repropagation. Otherwise Action is set to success
749 * Case 2 : For all other scenarios
750 * --------------------------------------------------------------|
751 * | Entity Status | Conc Request Phase | Action |
752 * |---------------|--------------------|------------------------|
753 * | Not Success | Null | Propagation Enabled |
754 * | Not Success | Completed | Repropagation Enabled |
755 * | All statuses | Other | Repropagation disabled |
756 * |-------------------------------------------------------------|
757 *******************************************************************************/
758 FUNCTION Get_Propagate_Action_Flag (
759 p_conc_request_phase_code IN VARCHAR2
760 , p_entity_action_status IN NUMBER
761 , p_global_change_id IN NUMBER
762 , p_local_organization_id IN NUMBER
763 ) RETURN VARCHAR2 IS
764
765 CURSOR c_new_revised_items_exist IS
766 SELECT count(1)
767 FROM eng_revised_items new_ri
768 WHERE new_ri.change_id= p_global_change_id -- global change id
769 AND NOT EXISTS (
770 SELECT 1
771 FROM eng_change_propagation_maps new_ri_map
772 WHERE new_ri_map.change_id = new_ri.change_id
773 AND new_ri_map.revised_item_sequence_id = new_ri.revised_item_sequence_id
774 AND new_ri_map.entity_name = 'ENG_REVISED_ITEM'
775 AND new_ri_map.local_organization_id = p_local_organization_id)
776 AND ROWNUM <2;
777
778 l_propagate_action_flag VARCHAR2(3);
779 l_revised_items_exist NUMBER;
780 BEGIN
781 l_propagate_action_flag := 'S';
782 -- Case 1
783 IF ((p_conc_request_phase_code IS NULL OR p_conc_request_phase_code = 'C')
784 AND p_entity_action_status = 1)
785 THEN
786 OPEN c_new_revised_items_exist;
787 FETCH c_new_revised_items_exist INTO l_revised_items_exist;
788 CLOSE c_new_revised_items_exist;
789 IF l_revised_items_exist > 0 -- new revised items exists
790 THEN
791 l_propagate_action_flag := 'RPE';
792 ELSE
793 l_propagate_action_flag := 'S';
794 END IF;
795 ELSE
796 -- Case 2: For all other scenarios , need not check for new revsied items
797 IF p_conc_request_phase_code IS NULL -- not submitted yet
798 THEN
799 l_propagate_action_flag := 'PE';
800 ELSIF p_conc_request_phase_code = 'C' -- submitted and completed but not successful (Success handled above)
801 THEN
802 l_propagate_action_flag := 'RPE';
803 ELSE -- if not completed
804 l_propagate_action_flag := 'RPD';
805 END IF;
806 END IF;
807
808 RETURN l_propagate_action_flag;
809 EXCEPTION
810 WHEN OTHERS THEN
811 IF c_new_revised_items_exist%ISOPEN
812 THEN
813 CLOSE c_new_revised_items_exist;
814 END IF;
815 RETURN NULL;
816 END Get_Propagate_Action_Flag;
817
818 END ENG_PROPAGATION_LOG_UTIL;
819