[Home] [Help]
PACKAGE BODY: APPS.JTS_CONFIG_VER_STATUS_PVT
Source
1 PACKAGE BODY JTS_CONFIG_VER_STATUS_PVT as
2 /* $Header: jtsvcvsb.pls 115.6 2002/04/10 18:10:23 pkm ship $ */
3
4
5 -- --------------------------------------------------------------------
6 -- Package name : JTS_CONFIG_VER_STATUS_PVT
7 -- Purpose : Version's Replay Statuses, Dates, By, and Version
8 -- Statuses.
9 -- History : 25-Feb-02 Sung Ha Huh Created.
10 -- NOTE :
11 -- --------------------------------------------------------------------
12
13
14 -- Checks if status is a Replay Status
15 FUNCTION IN_REPLAY_STATUS(p_api_version IN Number,
16 p_status IN Varchar2) RETURN BOOLEAN IS
17 l_api_version CONSTANT NUMBER := 1.0;
18 l_api_name CONSTANT VARCHAR2 (30) := 'IN_REPLAY_STATUS';
19 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
20 BEGIN
21 IF NOT fnd_api.compatible_api_call ( l_api_version,
22 p_api_version,
23 l_api_name,
24 G_PKG_NAME
25 )
26 THEN
27 RAISE fnd_api.g_exc_unexpected_error;
28 END IF;
29
30 -- Replay Status column, could change depending on the business logic
31 IF ( p_status IN (C_SUBMIT_REPLAY_STATUS, C_FAIL_REPLAY_STATUS, C_CANCEL_REPLAY_STATUS,
32 C_SUCCESS_REPLAY_STATUS, C_ERROR_REPLAY_STATUS,
33 C_NOSUBMIT_REPLAY_STATUS, C_RUNNING_REPLAY_STATUS)) THEN
34 return TRUE;
35 ELSE return FALSE;
36 END IF;
37
38 EXCEPTION
39 WHEN OTHERS THEN
40 APP_EXCEPTION.RAISE_EXCEPTION;
41 END IN_REPLAY_STATUS;
42
43 -- Checks if status is Version Status
44 FUNCTION IN_VERSION_STATUS(p_api_version IN Number,
45 p_status IN Varchar2) return BOOLEAN IS
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_api_name CONSTANT VARCHAR2 (30) := 'IN_VERSION_STATUS';
48 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
49 BEGIN
50 IF NOT fnd_api.compatible_api_call ( l_api_version,
51 p_api_version,
52 l_api_name,
53 G_PKG_NAME
54 )
55 THEN
56 RAISE fnd_api.g_exc_unexpected_error;
57 END IF;
58
59 -- Version Status column, could change depending on the business logic
60 IF (p_status IN (C_INIT_VERSION_STATUS, C_PROCESS_VERSION_STATUS, C_COMPLETE_VERSION_STATUS)) THEN
61 return TRUE;
62 ELSE return FALSE;
63 END IF;
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 APP_EXCEPTION.RAISE_EXCEPTION;
68 END IN_VERSION_STATUS;
69
70
71 -- Checks if status indicates that a version has not been replayed
72 -- Assumption: in_replay_status has already been called
73 PROCEDURE NOT_REPLAYED(p_api_version IN Number,
74 p_status IN Varchar2,
75 x_in_notreplayed OUT boolean) IS
76 l_api_version CONSTANT NUMBER := 1.0;
77 l_api_name CONSTANT VARCHAR2 (30) := 'NOT_REPLAYED';
78 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
79 BEGIN
80 IF NOT fnd_api.compatible_api_call ( l_api_version,
81 p_api_version,
82 l_api_name,
83 G_PKG_NAME
84 )
85 THEN
86 RAISE fnd_api.g_exc_unexpected_error;
87 END IF;
88
89 -- Replay has been cancelled or Replay was never submitted, could change depending on the business logic
90 IF (p_status = C_NOSUBMIT_REPLAY_STATUS OR p_status = C_CANCEL_REPLAY_STATUS) THEN
91 x_in_notreplayed := TRUE;
92 ELSE
93 x_in_notreplayed := FALSE;
94 END IF;
95 EXCEPTION
96 WHEN OTHERS THEN
97 APP_EXCEPTION.RAISE_EXCEPTION;
98 END NOT_REPLAYED;
99
100 -- Checks if any version under a configuration has been replayed
101 PROCEDURE ANY_VERSION_REPLAYED(p_api_version IN Number,
102 p_config_id IN Number,
103 x_replayed OUT BOOLEAN) IS
104 l_api_version CONSTANT NUMBER := 1.0;
105 l_api_name CONSTANT VARCHAR2 (30) := 'ANY_VERSION_REPLAYED';
106 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
107 l_versions_csr JTS_CONFIG_UTIL_PVT.Versions_Csr_Type;
108 l_version_id JTS_CONFIG_VERSIONS_B.version_id%TYPE;
109 l_status_code JTS_CONFIG_VERSION_STATUSES.status_code%TYPE;
110 l_replay_status FND_LOOKUP_VALUES.meaning%TYPE;
111 l_version_status_code JTS_CONFIG_VERSION_STATUSES.status_code%TYPE;
112 l_version_status FND_LOOKUP_VALUES.meaning%TYPE;
113 l_replayed_date JTS_CONFIG_VERSION_STATUSES.last_update_date%TYPE;
114 l_replayed_by_name FND_USER.user_name%TYPE;
115 l_not_replayed BOOLEAN := TRUE;
116 l_in_replay BOOLEAN := FALSE;
117 BEGIN
118 IF NOT fnd_api.compatible_api_call ( l_api_version,
119 p_api_version,
120 l_api_name,
121 G_PKG_NAME
122 )
123 THEN
124 RAISE fnd_api.g_exc_unexpected_error;
125 END IF;
126
127 JTS_CONFIG_UTIL_PVT.GET_VERSIONS_CURSOR(p_api_version,
128 p_config_id,
129 l_versions_csr);
130
131 x_replayed := FALSE;
132 LOOP
133 FETCH l_versions_csr INTO l_version_id;
134 EXIT WHEN l_versions_csr%NOTFOUND;
135 GET_VERSION_STATUS_DATA(p_api_version,
136 l_version_id,
137 l_status_code,
138 l_version_status_code,
139 l_replay_status,
140 l_version_status,
141 l_replayed_date,
142 l_replayed_by_name);
143 IF (IN_REPLAY_STATUS(p_api_version, l_status_code)) THEN
144 NOT_REPLAYED(p_api_version, l_status_code, l_not_replayed);
145 If (NOT l_not_replayed) then
146 x_replayed := TRUE;
147 EXIT; --exit out of the loop
148 END IF;
149 END IF;
150 END LOOP;
151 CLOSE l_versions_csr;
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 APP_EXCEPTION.RAISE_EXCEPTION;
156 END ANY_VERSION_REPLAYED;
157
158 -- Returns replay status, version status, replayed_date,
159 -- replayed_by for a version
160 PROCEDURE GET_VERSION_STATUS_DATA (
161 p_api_version IN Number,
162 p_version_id IN NUMBER,
163 x_replay_status_code OUT VARCHAR2,
164 x_version_status_code OUT VARCHAR2,
165 x_replay_status OUT VARCHAR2,
166 x_version_status OUT VARCHAR2,
167 x_replayed_date OUT DATE,
168 x_replayed_by_name OUT VARCHAR2) IS
169 l_api_version CONSTANT NUMBER := 1.0;
170 l_api_name CONSTANT VARCHAR2 (30) := 'GET_VERSION_STATUS_DATA';
171 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
172 l_status JTS_CONFIG_VERSION_STATUSES.status_code%TYPE;
173 l_meaning FND_LOOKUP_VALUES.meaning%TYPE;
174 l_replayed_date JTS_CONFIG_VERSION_STATUSES.last_update_date%TYPE;
175 l_replayed_by_name FND_USER.user_name%TYPE;
176 l_replay_done BOOLEAN := FALSE;
177 l_version_done BOOLEAN := FALSE;
178
179 CURSOR Statuses_Csr IS
180 SELECT vs.status_code, l.meaning, vs.creation_date, u.user_name
181 FROM jts_config_version_statuses vs,
182 fnd_lookup_values l,
183 fnd_user u
184 WHERE version_id = p_version_id
185 AND l.lookup_type = C_STATUS_TYPE
186 AND l.lookup_code = vs.status_code
187 AND u.user_id (+) = vs.created_by
188 ORDER BY vs.creation_date DESC;
189
190 BEGIN
191 IF NOT fnd_api.compatible_api_call ( l_api_version,
192 p_api_version,
193 l_api_name,
194 G_PKG_NAME
195 )
196 THEN
197 RAISE fnd_api.g_exc_unexpected_error;
198 END IF;
199
200 x_replay_status_code := NULL;
201 x_replay_status := NULL;
202 x_replayed_date := NULL;
203 x_replayed_by_name := NULL;
204 x_version_status_code := C_INIT_VERSION_STATUS;
205 --x_version_status := 'New';
206
207 l_replay_done := FALSE;
208 l_version_done := FALSE;
209 OPEN Statuses_Csr;
210 LOOP
211 FETCH Statuses_Csr INTO l_status, l_meaning, l_replayed_date, l_replayed_by_name;
212 --exist when both replay and version statuses are found or when we have reached
213 --the end of the cursor
214 EXIT WHEN (Statuses_Csr%NOTFOUND OR (l_replay_done AND l_version_done));
215
216 --status is either a version or replay status
217 IF ((NOT l_version_done) AND IN_VERSION_STATUS(p_api_version, l_status) ) THEN
218 x_version_status_code := l_status;
219 x_version_status := l_meaning;
220 l_version_done := TRUE;
221 ELSIF ((NOT l_replay_done) AND IN_REPLAY_STATUS(p_api_version, l_status) ) THEN
222 x_replay_status_code := l_status;
223 x_replay_status := l_meaning;
224 x_replayed_date := l_replayed_date;
225 x_replayed_by_name := l_replayed_by_name;
226 l_replay_done := TRUE;
227 END IF;
228 END LOOP;
229 CLOSE Statuses_Csr;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 APP_EXCEPTION.RAISE_EXCEPTION;
234 END GET_VERSION_STATUS_DATA;
235
236
237 -- Inserts a row into jts_config_version_statuses table with a
238 -- a certain version_id and status
239 PROCEDURE CREATE_VERSION_STATUS(p_api_version IN Number,
240 p_commit IN Varchar2,
241 p_version_id IN Number,
242 p_status IN Varchar2
243 ) IS
244 l_api_version CONSTANT NUMBER := 1.0;
245 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_VERSION_STATUS';
246 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
247 BEGIN
248 IF NOT fnd_api.compatible_api_call ( l_api_version,
249 p_api_version,
250 l_api_name,
251 G_PKG_NAME
252 )
253 THEN
254 RAISE fnd_api.g_exc_unexpected_error;
255 END IF;
256
257 INSERT INTO jts_config_version_statuses
258 (
259 VERSION_ID,
260 STATUS_CODE,
261 OBJECT_VERSION_NUMBER,
262 CREATION_DATE,
263 CREATED_BY,
264 LAST_UPDATE_DATE,
265 LAST_UPDATED_BY,
266 LAST_UPDATE_LOGIN
267 ) VALUES (
268 p_version_id,
269 nvl(p_status, C_INIT_VERSION_STATUS),
270 1,
271 sysdate,
272 FND_GLOBAL.user_id,
273 sysdate,
274 FND_GLOBAL.user_id,
275 FND_GLOBAL.user_id
276 );
277
278 IF (in_version_status(p_api_version, p_status)) THEN
279 JTS_CONFIG_VERSION_PVT.update_version_stat(p_api_version,
280 p_version_id,
281 p_status);
282 ELSIF (in_replay_status(p_api_version, p_status)) THEN
283 JTS_CONFIG_VERSION_PVT.update_replay_data(p_api_version,
284 p_version_id,
285 p_status);
286 END IF;
287
288 IF (FND_API.to_boolean(p_commit)) THEN
289 COMMIT;
290 END IF;
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 APP_EXCEPTION.RAISE_EXCEPTION;
295 END CREATE_VERSION_STATUS;
296
297 -- Deletes records from jts_config_version_statuses table
298 PROCEDURE DELETE_VERSION_STATUSES(p_api_version IN Number,
299 p_version_id IN Number) IS
300 l_api_version CONSTANT NUMBER := 1.0;
301 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_VERSION_STATUSES';
302 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
303 BEGIN
304 IF NOT fnd_api.compatible_api_call ( l_api_version,
305 p_api_version,
306 l_api_name,
307 G_PKG_NAME
308 )
309 THEN
310 RAISE fnd_api.g_exc_unexpected_error;
311 END IF;
312
313 DELETE FROM jts_config_version_statuses
314 WHERE version_id = p_version_id;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 APP_EXCEPTION.RAISE_EXCEPTION;
319 END DELETE_VERSION_STATUSES;
320
321 -- Deletes records from jts_config_version_statuses table for all
322 -- versions with a certain configuration id
323 PROCEDURE DELETE_CONFIG_VER_STATUSES(p_api_version IN Number,
324 p_config_id IN Number) IS
325 l_api_version CONSTANT NUMBER := 1.0;
326 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_CONFIG_VER_STATUSES';
327 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
328 versions_csr JTS_CONFIG_UTIL_PVT.Versions_Csr_Type;
329 l_version_id JTS_CONFIG_VERSION_STATUSES.version_id%TYPE;
330 BEGIN
331 IF NOT fnd_api.compatible_api_call ( l_api_version,
332 p_api_version,
333 l_api_name,
334 G_PKG_NAME
335 )
336 THEN
337 RAISE fnd_api.g_exc_unexpected_error;
338 END IF;
339
340 JTS_CONFIG_UTIL_PVT.GET_VERSIONS_CURSOR(p_api_version,
341 p_config_id,
342 versions_csr);
343 LOOP
344 FETCH versions_csr INTO l_version_id;
345 EXIT WHEN versions_csr%NOTFOUND;
346
347 DELETE FROM jts_config_version_flows
348 WHERE version_id = l_version_id;
349 END LOOP;
350
351 CLOSE versions_csr;
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 APP_EXCEPTION.RAISE_EXCEPTION;
356 END DELETE_CONFIG_VER_STATUSES;
357
358 END JTS_CONFIG_VER_STATUS_PVT;