1 PACKAGE BODY JTM_MESSAGE_LOG_PKG AS
2 /* $Header: jtmmlpb.pls 120.2 2006/09/20 16:54:12 rsripada noship $ */
3
4 /* This procedure inserts a record into the FND_LOG_MESSAGES table
5 FND uses an autonomous transaction so even when the hookinsert is
6 rolled back because of an error the log messages still exists
7 */
8 g_initialize_log BOOLEAN :=FALSE;
9 g_session_id NUMBER :=0;
10
11 PROCEDURE LOG_MSG( v_object_id IN VARCHAR2
12 , v_object_name IN VARCHAR2
13 , v_message IN VARCHAR2
14 , v_level_id IN NUMBER
15 , v_module IN VARCHAR2)
16 IS
17 l_log_level NUMBER;
18 l_module VARCHAR2(64);
19 l_message VARCHAR2(4000);
20 BEGIN
21 l_module := v_module;
22 -- Convert to the FND_LOG LEVEL
23 l_log_level := 5 - v_level_id;
24 --Create the message text
25 l_message := 'Object '||v_object_name||'-'||v_object_id||' : '||v_message;
26
27 --Bug 5532003
28 IF g_initialize_log = TRUE THEN
29 IF (l_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
30 FND_LOG.STRING(l_log_level, l_module, l_message);
31 END IF;
32 ELSE
33 fnd_log_repository.init();
34 g_initialize_log := TRUE;
35 IF (l_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
36 fnd_log.string(l_log_level, l_module, l_message);
37 END IF;
38 END IF;
39
40 EXCEPTION WHEN OTHERS THEN
41 NULL;
42 END LOG_MSG;
43
44 /* This procedure deletes all records in the message table */
45 /* use truncate to prevent the database from running out of rollback segments*/
46 /* PWU: these queries are costly and there are issues with performance
47 we take them out since we only provide for developer to conveniently
48 remove the log records. */
49 /*
50 PROCEDURE PURGE
51 IS
52 PRAGMA AUTONOMOUS_TRANSACTION;
53 BEGIN
54 DELETE FND_LOG_MESSAGES
55 WHERE lower(MODULE) LIKE 'jtm%';
56 COMMIT;
57
58 DELETE FND_LOG_MESSAGES
59 WHERE lower(MODULE) LIKE 'csl%';
60 COMMIT;
61 EXCEPTION WHEN OTHERS THEN
62 ROLLBACK;
63 END PURGE;
64 */
65
66 PROCEDURE INSERT_CONC_STATUS_LOG(v_package_name IN VARCHAR2
67 ,v_procedure_name IN VARCHAR2
68 ,v_con_query_id IN NUMBER
69 ,v_query_stmt IN VARCHAR2
70 ,v_start_time IN DATE
71 ,v_end_time IN DATE
72 ,v_status IN VARCHAR2
73 ,v_message IN VARCHAR2
74 ,x_log_id OUT NOCOPY NUMBER
75 ,x_status OUT NOCOPY VARCHAR2
76 ,x_msg_data OUT NOCOPY VARCHAR2)
77 IS
78 l_dml varchar2(2000);
79 l_log_id number;
80 BEGIN
81
82 l_dml := 'INSERT INTO JTM_CONC_RUN_STATUS_LOG' ||
83 '(LOG_ID,PACKAGE_NAME,PROCEDURE_NAME,CONC_QUERY_ID,QUERY_STMT, ' ||
84 'CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,' ||
85 'START_TIME,END_TIME,STATUS, MESSAGE)' ||
86 'VALUES (JTM_CONC_RUN_STATUS_LOG_S.nextval, :1, :2, :3, :4,-1, sysdate, -1, sysdate, :5, :6, :7, :8) RETURNING LOG_ID INTO :9';
87 EXECUTE IMMEDIATE l_dml using v_package_name, v_procedure_name, v_con_query_id, v_query_stmt, v_start_time,v_end_time, v_status, v_message RETURNING INTO l_log_id;
88 commit;
89
90 x_log_id := l_log_id;
91 x_status := 'S';
92 x_msg_data := 'Insert record into JTM_CONC_RUN_STATUS_LOG successfully';
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 x_status := 'E';
97 x_log_id := -1;
98 x_msg_data := 'Error:' || sqlerrm;
99 RAISE;
100 END INSERT_CONC_STATUS_LOG;
101
102
103 PROCEDURE UPDATE_CONC_STATUS_LOG(v_log_id IN NUMBER
104 ,v_query_stmt IN VARCHAR2
105 ,v_start_time IN DATE
106 ,v_end_time IN DATE
107 ,v_status IN VARCHAR2
108 ,v_message IN VARCHAR2
109 ,x_status OUT NOCOPY VARCHAR2
110 ,x_msg_data OUT NOCOPY VARCHAR2
111 )
112 IS
113 l_dml varchar2(2000);
114 BEGIN
115 l_dml := 'UPDATE JTM_CONC_RUN_STATUS_LOG SET QUERY_STMT = :1, START_TIME = :2 ,END_TIME= :3' ||
116 ',STATUS= :4 , MESSAGE= :5 WHERE LOG_ID = :5';
117 EXECUTE IMMEDIATE l_dml USING v_query_stmt, v_start_time, v_end_time, v_status, v_message, v_log_id;
118 commit;
119 x_status := 'S';
120 x_msg_data := 'UPDATE UPDATE_CONC_STATUS_LOG ' || v_log_id || ' successfully';
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 x_status := 'E';
125 x_msg_data := 'Error:' || sqlerrm;
126 RAISE;
127 END UPDATE_CONC_STATUS_LOG;
128
129 PROCEDURE DELETE_CONC_STATUS_LOG(v_log_id IN NUMBER)
130 IS
131 l_dml varchar2(2000);
132 BEGIN
133 /* Testing show that the code below does not work. comment out
134 l_dml := 'DELETE FROM JTM_CONC_RUN_STATUS_LOG WHERE LOG_ID = :1';
135 EXECUTE IMMEDIATE l_dml USING v_log_id;
136 */
137 DELETE FROM JTM_CONC_RUN_STATUS_LOG WHERE LOG_ID = v_log_id;
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 RAISE;
142 END DELETE_CONC_STATUS_LOG;
143
144 END JTM_MESSAGE_LOG_PKG;
[Home] [Help]