[Home] [Help]
PACKAGE BODY: APPS.JTM_MASTER_CONC_PROG_PUB
Source
1 PACKAGE BODY JTM_MASTER_CONC_PROG_PUB AS
2 /* $Header: jtmpconb.pls 120.2 2006/01/13 01:16:36 utekumal noship $ */
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- JTM_MASTER_CONC_PROG_PUB
8 --
9 -- PURPOSE
10 -- Central Entry for Mobile Concurrent Programs.
11 --
12 -- PROCEDURES:
13 --
14 --
15 -- NOTES
16 --
17 --
18 -- HISTORY
19 -- 04-09-2002 YOHUANG Created.
20 -- 07-29-2002 PWU
21 -- Make change to catch "run away" interval program.
22 -- And add log message.
23 -- 01-14-2003 PWU
24 -- re-write the concurrent program. The package name is change, too.
25 -- we now accept parameter and get rid of the interval counter.
26 -- 09-21 DLIAO
27 -- populate data in txc_start, txc_end, completion_context
28 -- 03-01-2004 PWU
29 -- Change the protocol to call registered program by passing two output
30 -- parameters status and message to report the running status of the
31 -- program.
32 -- Notes
33 -- Do_Refresh_Mobile_Data will call each procedure according to EXECUTE_FLAG, EXECUTION_ORDER,
34 -- and FREQUENCY. Do_Refresh_Mobile_Data itself it runs every hour.
35 -- Each Procedure must make use of SAVEPOINT, because, at the end, Concurrent Manager will commit everything.
36 -- End of Comments
37 --
38 --
39 --
40 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTM_MASTER_CONC_PROG_PUB';
41
42 PROCEDURE Do_Refresh_Mobile_Data
43 (
44 errbuf OUT NOCOPY VARCHAR2,
45 retcode OUT NOCOPY NUMBER,
46 Category_Type IN VARCHAR2
47 ) as
48
49 L_API_NAME CONSTANT varchar2(30) := 'DO_REFRESH_MOBILE_DATA';
50
51 CURSOR X_TO_BE_RUN_REQUEST(p_Category_Type varchar2) IS
52 SELECT PACKAGE_NAME, PROCEDURE_NAME
53 FROM JTM_CON_REQUEST_DATA
54 WHERE Category = p_Category_Type
55 AND EXECUTE_FLAG = 'Y'
56 ORDER BY EXECUTION_ORDER, PRODUCT_CODE;
57
58 l_active_count number := 1;
59 l_proc_start Date;
60 l_start_log_id number;
61
62 dynamic_stmt VARCHAR2(2000);
63 l_api_start DATE;
64 l_api_end DATE := null;
65 l_api_status varchar2(30);
66 l_status_message VARCHAR2(2000);
67 l_full_procedure_name varchar2(60);
68 l_log_id NUMBER;
69
70 l_status VARCHAR2(30);
71 l_message VARCHAR2(2000);
72 l_successful_msg CONSTANT varchar2(200) := 'This program runs successfully.';
73 l_conc_message varchar2(2000) := l_successful_msg;
74
75 BEGIN
76 retcode := 0;
77 errbuf := 'OK';
78 l_proc_start := sysdate;
79
80 JTM_MESSAGE_LOG_PKG.log_msg(
81 v_object_id => L_API_NAME,
82 v_object_name => G_PKG_NAME,
83 v_message => 'Category = ' || Category_Type,
84 v_level_id => JTM_HOOK_UTIL_PKG.g_debug_level_sql,
85 v_module => G_PKG_NAME);
86 /* check to see if there are already any active session of the
87 same program with same parameter are running. If so, exit out
88 to avoid the multiple session
89 */
90 SELECT count(*)
91 INTO l_active_count
92 FROM fnd_concurrent_requests req, fnd_concurrent_programs cp
93 WHERE req.argument1 = Category_Type
94 AND req.PHASE_CODE = 'R'
95 AND req.STATUS_CODE = 'R'
96 AND req.PROGRAM_APPLICATION_ID = 874
97 AND req.PROGRAM_APPLICATION_ID = cp.APPLICATION_ID
98 AND req.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
99 AND cp.concurrent_program_name = 'JTM_MASTER_CONC_PROG';
100 IF (l_active_count > 1) THEN
101 errbuf := 'There are multiple active sessions of JTM concurrent program'
102 || ' with argument = ' || Category_Type;
103 return;
104 END IF;
105
106 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
107 (v_package_name => G_PKG_NAME
108 ,v_procedure_name => L_API_NAME
109 ,v_con_query_id => NULL
110 ,v_query_stmt => Category_Type
111 ,v_start_time => l_proc_start
112 ,v_end_time => NULL
113 ,v_status => 'Running'
114 ,v_message => 'JTM master concurrent program is called.'
115 ,x_log_id => l_start_log_id
116 ,x_status => l_status
117 ,x_msg_data => l_message);
118
119 IF (l_status = 'E') THEN
120 RAISE JTM_MESSAGE_LOG_PKG.G_EXC_ERROR;
121 END IF;
122
123 FOR x_request IN X_TO_BE_RUN_REQUEST(Category_Type) LOOP
124 l_api_start := sysdate;
125 l_api_end := null;
126 l_api_status := 'Running';
127 l_full_procedure_name := x_request.PACKAGE_NAME || '.' ||
128 x_request.PROCEDURE_NAME;
129
130 /* Add run time status into JTM concurrent prog log table */
131 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
132 (v_package_name => x_request.PACKAGE_NAME
133 ,v_procedure_name => x_request.PROCEDURE_NAME
134 ,v_con_query_id => NULL
135 ,v_query_stmt => Category_Type
136 ,v_start_time => l_api_start
137 ,v_end_time => l_api_end
138 ,v_status => l_api_status
139 ,v_message => 'The program is running.'
140 ,x_log_id => l_log_id
141 ,x_status => l_status
142 ,x_msg_data => l_message);
143
144 BEGIN
145 dynamic_stmt := 'BEGIN ' || l_full_procedure_name || '(:1,:2); END; ';
146 EXECUTE IMMEDIATE dynamic_stmt
147 using out l_api_status, out l_status_message;
148 l_api_end := sysdate;
149 IF (upper(l_api_status) = upper(JTM_CON_QUERY_REQUEST_PKG.G_ERROR) ) THEN
150 retcode := -1;
151 IF (l_conc_message = l_successful_msg) THEN
152 l_conc_message:= 'Error message from ' || l_full_procedure_name
153 || ': ' || l_status_message;
154 ELSE
155 l_conc_message:= l_conc_message || '. Error message from '
156 || l_full_procedure_name || ': ' || l_status_message;
157
158 END IF;
159 END IF;
160 EXCEPTION
161 WHEN OTHERS THEN
162 /* This is to handle the registered program which has not added
163 two output parameters */
164 IF (sqlcode = -6537 OR sqlcode = -6550) THEN
165 BEGIN
166 dynamic_stmt := 'BEGIN ' || l_full_procedure_name || '; END; ';
167 EXECUTE IMMEDIATE dynamic_stmt;
168 l_api_status := JTM_CON_QUERY_REQUEST_PKG.G_FINE;
169 l_status_message := 'No message from '|| l_full_procedure_name
170 || '. Assume all are fine.';
171 l_api_end := sysdate;
172 EXCEPTION
173 WHEN OTHERS THEN
174 JTM_MESSAGE_LOG_PKG.log_msg(
175 v_object_id => x_request.PROCEDURE_NAME,
176 v_object_name => x_request.PACKAGE_NAME,
177 v_message => 'Error ocurrs on execution',
178 v_level_id => JTM_HOOK_UTIL_PKG.g_debug_level_error,
179 v_module => G_PKG_NAME);
180 if (errbuf = 'OK') then
181 errbuf := 'Error in ' || l_full_procedure_name;
182 else
183 errbuf := errbuf ||', ' || l_full_procedure_name;
184 end if;
185 retcode := -1;
186
187 l_api_end := sysdate;
188 l_api_status := JTM_CON_QUERY_REQUEST_PKG.G_ERROR;
189 l_status_message := SQLERRM;
190 END;
191 ELSE
192 JTM_MESSAGE_LOG_PKG.log_msg(
193 v_object_id => x_request.PROCEDURE_NAME,
194 v_object_name => x_request.PACKAGE_NAME,
195 v_message => 'Error ocurrs on exection',
196 v_level_id => JTM_HOOK_UTIL_PKG.g_debug_level_error,
197 v_module => G_PKG_NAME);
198 if (errbuf = 'OK') then
199 errbuf := 'Error in ' || G_PKG_NAME ||
200 ' while running ' || l_full_procedure_name;
201 else
202 errbuf := errbuf ||', ' ||l_full_procedure_name;
203 end if;
204 retcode := -1;
205
206 l_api_end := sysdate;
207 l_api_status := JTM_CON_QUERY_REQUEST_PKG.G_ERROR;
208 l_status_message := SQLERRM;
209 END IF;
210 END;
211
212 dynamic_stmt := 'UPDATE JTM_CON_REQUEST_DATA SET LAST_TXC_START = :1, '
213 || 'LAST_TXC_END = :2, COMPLETION_TEXT = :3, STATUS = :4 '
214 || 'WHERE PACKAGE_NAME = :5 AND PROCEDURE_NAME = :6';
215
216 EXECUTE IMMEDIATE dynamic_stmt USING l_api_start, l_api_end,l_status_message,
217 l_api_status, x_request.PACKAGE_NAME,x_request.PROCEDURE_NAME;
218
219 JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
220 (v_log_id =>l_log_id
221 ,v_query_stmt => Category_Type
222 ,v_start_time => l_api_start
223 ,v_end_time => l_api_end
224 ,v_status => l_api_status
225 ,v_message => l_status_message
226 ,x_status => l_status
227 ,x_msg_data => l_message);
228
229 END LOOP;
230
231 if (retcode = 0) then
232 l_api_status := JTM_CON_QUERY_REQUEST_PKG.G_FINE;
233 else
234 l_api_status := JTM_CON_QUERY_REQUEST_PKG.G_ERROR;
235 end if;
236 if (errbuf = 'OK') then
237 errbuf := l_conc_message;
238 end if;
239 JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
240 (v_log_id =>l_start_log_id
241 ,v_query_stmt => Category_Type
242 ,v_start_time => l_proc_start
243 ,v_end_time => sysdate
244 ,v_status => l_api_status
245 ,v_message => errbuf
246 ,x_status => l_status
247 ,x_msg_data => l_message);
248
249 Exception
250 When others then
251 JTM_MESSAGE_LOG_PKG.log_msg(
252 v_object_id => L_API_NAME,
253 v_object_name => G_PKG_NAME,
254 v_message => 'Exception occurs.',
255 v_level_id => JTM_HOOK_UTIL_PKG.g_debug_level_error,
256 v_module => G_PKG_NAME);
257
258 errbuf := 'Exception in '||G_PKG_NAME ||'.'|| L_API_NAME ||': '||sqlerrm;
259 retcode := -1;
260
261 JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
262 (v_log_id =>l_start_log_id
263 ,v_query_stmt => Category_Type
264 ,v_start_time => l_proc_start
265 ,v_end_time => sysdate
266 ,v_status => JTM_CON_QUERY_REQUEST_PKG.G_ERROR
267 ,v_message => errbuf
268 ,x_status => l_status
269 ,x_msg_data => l_message);
270
271 END Do_Refresh_Mobile_Data;
272
273 END JTM_MASTER_CONC_PROG_PUB;