[Home] [Help]
PACKAGE BODY: APPS.CS_ALW_STS_TRANSITIONS_PVT
Source
1 PACKAGE BODY CS_ALW_STS_TRANSITIONS_PVT AS
2 /* $Header: csvalstb.pls 115.14 2004/05/17 07:55:37 vmuruges noship $ */
3
4 Procedure Copy_Status_Group(p_statusGroupId IN VARCHAR2,
5 x_statusGroupId OUT NOCOPY VARCHAR2,
6 x_errorCode OUT NOCOPY NUMBER,
7 x_errorMessage OUT NOCOPY VARCHAR2)
8
9 IS
10
11 CURSOR c_statusGroups(p_statusGroupId VARCHAR2) IS
12 SELECT csgb.created_by
13 ,csgb.creation_date
14 ,csgb.start_date
15 ,csgb.end_date
16 ,csgb.last_update_date
17 ,csgb.last_update_login
18 ,csgb.last_updated_by
19 ,csgb.default_incident_status_id
20 ,csgb.security_group_id
21 ,csgb.group_name
22 ,csgb.description
23 ,csgb.language
24 ,csgb.source_lang
25 FROM CS_SR_STATUS_GROUPS_VL csgb
26 WHERE csgb.status_group_id = p_statusGroupId ;
27
28
29 CURSOR c_allowedStatuses(p_statusGroupId VARCHAR2) IS
30 SELECT *
31 FROM CS_SR_ALLOWED_STATUSES
32 WHERE status_group_id = p_statusGroupId;
33
34
35 CURSOR c_StatusTransitions(p_statusGroupId VARCHAR2) IS
36 SELECT *
37 FROM CS_SR_STATUS_TRANSITIONS
38 WHERE status_group_id = p_statusGroupId;
39
40 lv_new_statusgroupID NUMBER:=NULL;
41 --lv_new_allowstatusID NUMBER:=NULL;
42 --lv_new_statustransitionsID NUMBER:NULL;
43 lv_copy_of_group_name VARCHAR2(240);
44 l_copy_of VARCHAR2(20);
45 l_from_allowed_status_id NUMBER;
46 l_to_allowed_status_id NUMBER;
47 l_row_id VARCHAR2(1000);
48 BEGIN
49
50 SELECT CS_SR_STATUS_GROUPS_B_S.nextval INTO lv_new_statusgroupID FROM DUAL;
51
52 FND_MESSAGE.SET_NAME('CS','CS_SR_COPY_OF');
53 -- l_copy_of :=FND_MESSAGE.GET || fnd_global.local_CHR(10);
54 l_copy_of :=FND_MESSAGE.GET;
55
56 FOR c_statusGroups_rec in c_statusGroups(p_statusGroupId) LOOP
57 lv_copy_of_group_name := l_copy_of||' '||c_statusGroups_rec.group_name||'-'||lv_new_statusgroupID;
58
59 -- Create a Status group record in cs_sr_status_groups_b and cs_sr_status_groups_tl table
60
61 -- bug 3625236 - Reverse the parameters passed to start and end date
62 BEGIN
63 CS_SR_STATUS_GROUPS_PKG.INSERT_ROW
64 ( X_ROWID => l_row_id ,
65 X_STATUS_GROUP_ID => lv_new_statusgroupID ,
66 X_SECURITY_GROUP_ID => null ,
67 X_TRANSITION_IND => c_statusGroups_rec.default_incident_status_id ,
68 X_OBJECT_VERSION_NUMBER => 1 ,
69 X_ORIG_SYSTEM_REFERENCE_ID => null ,
70 X_END_DATE => c_statusGroups_rec.end_date,
71 X_START_DATE => c_statusGroups_rec.start_date,
72 X_DEFAULT_INCIDENT_STATUS_ID => c_statusGroups_rec.default_incident_status_id,
73 X_GROUP_NAME => lv_copy_of_group_name ,
74 X_DESCRIPTION => c_statusGroups_rec.description,
75 X_LANGUAGE => c_statusGroups_rec.language ,
76 X_SOURCE_LANG => c_statusGroups_rec.source_lang ,
77 X_CREATION_DATE => sysdate,
78 X_CREATED_BY => FND_GLOBAL.USER_ID ,
79 X_LAST_UPDATE_DATE => sysdate ,
80 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
81 X_LAST_UPDATE_LOGIN => null) ;
82 EXCEPTION
83 WHEN others THEN
84 x_errorCode := 1;
85 x_errorMessage := SQLERRM;
86 EXIT ;
87
88 END ;
89
90
91 /*** Commented since the insert into cs_sr_status_groups_b and cs_sr_status_groups_tl will be
92 done using table handlers as above
93
94 --Insert a new record into CS_SR_STATUS_GROUPS_B
95
96 INSERT INTO CS_SR_STATUS_GROUPS_B(
97 status_group_id,
98 created_by,
99 creation_date,
100 start_date,
101 end_date,
102 last_update_date,
103 last_update_login,
104 last_updated_by,
105 default_incident_status_id,
106 security_group_id,
107 object_version_number)
108 VALUES (
109 lv_new_statusgroupID,
110 FND_GLOBAL.USER_ID,
111 sysdate,
112 c_statusGroups_rec.start_date,
113 c_statusGroups_rec.end_date,
114 sysdate,
115 FND_GLOBAL.LOGIN_ID,
116 FND_GLOBAL.USER_ID,
117 c_statusGroups_rec.default_incident_status_id,
118 c_statusGroups_rec.security_group_id,
119 0);
120
121 --Insert a new record into CS_SR_STATUS_GROUPS_TL
122
123 INSERT INTO CS_SR_STATUS_GROUPS_TL(
124 status_group_id,
125 created_by,
126 creation_date,
127 last_update_date,
128 last_update_login,
129 last_updated_by,
130 group_name,
131 description,
132 language,
133 source_lang )
134 VALUES (
135 lv_new_statusgroupID,
136 FND_GLOBAL.USER_ID,
137 sysdate,
138 sysdate,
139 FND_GLOBAL.LOGIN_ID,
140 FND_GLOBAL.USER_ID,
141 lv_copy_of_group_name,
142 c_statusGroups_rec.description,
143 c_statusGroups_rec.language,
144 c_statusGroups_rec.source_lang );
145 ***/
146
147 END LOOP;
148
149 FOR c_allowedStatuses_rec in c_allowedStatuses(p_statusGroupId) LOOP
150
151 --Insert a new record into CS_SR_ALLOWED_STATUSES
152
153 INSERT INTO CS_SR_ALLOWED_STATUSES(
154 status_group_id,
155 created_by,
156 creation_date,
157 start_date,
158 end_date,
159 last_update_date,
160 last_update_login,
161 last_updated_by,
162 allowed_status_id,
163 incident_status_id,
164 object_version_number)
165 VALUES (
166 lv_new_statusgroupID,
167 FND_GLOBAL.USER_ID,
168 sysdate,
169 c_allowedStatuses_rec.start_date,
170 c_allowedStatuses_rec.end_date,
171 sysdate,
172 FND_GLOBAL.LOGIN_ID,
173 FND_GLOBAL.USER_ID,
174 CS_SR_ALLOWED_STATUSES_S.nextval,
175 c_allowedStatuses_rec.incident_status_id,
176 0);
177 END LOOP;
178
179
180 FOR c_StatusTransitions_rec in c_StatusTransitions(p_statusGroupId) LOOP
181
182 l_from_allowed_status_id := null;
183 l_to_allowed_status_id := null;
184
185 --Insert a new record into CS_SR_STATUS_TRANSITIONS
186
187 -- get from_allowed_status_id from the allowed statuses for the new status group
188
189 BEGIN
190
191 SELECT allowed_status_id
192 INTO l_from_allowed_status_id
193 FROM cs_sr_allowed_statuses
194 WHERE status_group_id = lv_new_statusgroupID
195 AND incident_status_id = c_StatusTransitions_rec.from_incident_status_id ;
196
197 EXCEPTION
198 WHEN others THEN
199 x_errorCode := 1;
200 x_errorMessage := SUBSTR(SQLERRM,1,280);
201 EXIT ;
202 END ;
203
204 BEGIN
205
206 SELECT allowed_status_id
207 INTO l_to_allowed_status_id
208 FROM cs_sr_allowed_statuses
209 WHERE status_group_id = lv_new_statusgroupID
210 AND incident_status_id = c_StatusTransitions_rec.to_incident_status_id ;
211
212 EXCEPTION
213 WHEN others THEN
214 x_errorCode := 1;
215 x_errorMessage := SUBSTR(SQLERRM,1,280);
216 EXIT ;
217 END ;
218
219 INSERT INTO CS_SR_STATUS_TRANSITIONS(
220 status_group_id,
221 created_by,
222 creation_date,
223 start_date,
224 end_date,
225 last_update_date,
226 last_update_login,
227 last_updated_by,
228 status_transition_id,
229 from_allowed_status_id,
230 to_allowed_status_id,
231 from_incident_status_id,
232 to_incident_status_id,
233 object_version_number)
234 VALUES (
235 lv_new_statusgroupID,
236 FND_GLOBAL.USER_ID,
237 sysdate,
238 c_statusTransitions_rec.start_date,
239 c_statusTransitions_rec.end_date,
240 sysdate,
241 FND_GLOBAL.LOGIN_ID,
242 FND_GLOBAL.USER_ID,
243 CS_SR_STATUS_TRANSITIONS_S.nextval,
244 l_from_allowed_status_id,
245 l_to_allowed_status_id,
246 c_StatusTransitions_rec.from_incident_status_id,
247 c_StatusTransitions_rec.to_incident_status_id,
248 0);
249 END LOOP;
250
251 x_statusGroupId := lv_new_statusgroupID;
252
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 x_errorCode := 1;
257 x_errorMessage := SUBSTR(SQLERRM,1,280);
258 END Copy_Status_Group;
259
260
261 Procedure AllowedStatus_StartDate_Valid(p_statusGroupId IN number,
262 p_allowed_status_id IN number,
263 p_new_start_date IN date,
264 x_errorCode OUT NOCOPY number,
265 x_errorMessage OUT NOCOPY varchar2,
266 x_return_code OUT NOCOPY varchar2)
267 IS
268
269 CURSOR c_Findstatus(p_allowed_status_id number,p_statusGroupId number) IS
270 SELECT start_date
271 FROM cs_sr_status_transitions
272 WHERE (from_allowed_status_id = p_allowed_status_id
273 OR to_allowed_status_id = p_allowed_status_id)
274 AND status_group_id = p_statusGroupId;
275
276 BEGIN
277
278 x_return_code := 'S';
279
280 FOR c_Findstatus_rec in C_Findstatus(p_allowed_status_id,p_statusGroupId) LOOP
281 IF (p_new_start_date > c_Findstatus_rec.start_date) OR
282 (c_Findstatus_rec.start_date IS NULL) THEN
283 x_return_code := 'E';
284 exit;
285 END IF;
286 END LOOP;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 x_errorCode := 1;
291 x_errorMessage := SUBSTR(SQLERRM,1,280);
292
293 END AllowedStatus_StartDate_Valid;
294
295 Procedure AllowedStatus_EndDate_Valid(p_statusGroupId IN number,
296 p_allowed_status_id IN number,
297 p_new_end_date IN date,
298 p_incident_status_id IN NUMBER,
299 x_errorCode OUT NOCOPY number,
300 x_errorMessage OUT NOCOPY varchar2,
301 x_return_code OUT NOCOPY varchar2)
302 IS
303
304 CURSOR c_FindEndDate(p_allowed_status_id number,p_statusGroupId number) IS
305 SELECT end_date,status_group_id
306 FROM cs_sr_status_transitions
307 WHERE (from_allowed_status_id = p_allowed_status_id
308 OR to_allowed_status_id = p_allowed_status_id)
309 AND status_group_id = p_statusGroupId;
310
311 CURSOR c_def_active (p_statusgroupid number) IS
312 SELECT default_incident_status_id
313 FROM cs_sr_status_groups_b
314 WHERE status_group_id = p_statusgroupid ;
315
316 BEGIN
317
318 x_return_code := 'S';
319
320 FOR c_FindEndDate_rec in C_FindEndDate(p_allowed_status_id,p_statusGroupId) LOOP
321 --IF (to_char(c_FindEndDate_rec.end_date,'DD-MON-RRRR') = '01-JAN-1000') THEN
322 IF (c_FindEndDate_rec.end_date is null) THEN
323 x_return_code := 'N';
324 exit;
325 ELSIF (to_char(c_FindEndDate_rec.end_date,'DD-MON-RRRR') > to_char(p_new_end_date,'DD-MON-RRRR')) THEN
326 x_return_code := 'E';
327 exit;
328 END IF;
329 END LOOP;
330
331 FOR c_def_active_rec IN c_def_active(p_statusGroupId)
332 LOOP
333 IF c_def_active_rec.default_incident_status_id = p_incident_status_id THEN
334 x_return_code := 'D' ;
335 exit ;
336 END IF ;
337 END LOOP ;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 x_errorCode := 1;
342 x_errorMessage := SUBSTR(SQLERRM,1,280);
343
344 END AllowedStatus_EndDate_Valid;
345
346
347 Function returnStartDate(p_allowed_status_id NUMBER) RETURN DATE IS
348
349 l_returnStartDate DATE;
350
351 BEGIN
352 SELECT start_date
353 INTO l_returnStartDate
354 FROM cs_sr_allowed_statuses
355 WHERE allowed_status_id = p_allowed_status_id
356 AND ROWNUM < 2;
357
358 return l_returnStartDate;
359
360 END;
361
362
363 Function returnEndDate(p_allowed_status_id NUMBER) RETURN DATE IS
364
365
366 l_returnEndDate DATE;
367
368 BEGIN
369 SELECT end_date
370 INTO l_returnEndDate
371 FROM cs_sr_allowed_statuses
372 WHERE allowed_status_id = p_allowed_status_id
373 AND ROWNUM < 2;
374
375 return l_returnEndDate;
376
377 END;
378
379 Procedure StatusTrans_StartDate_Valid(p_statusGroupId IN number,
380 p_from_allowed_status_id IN number,
381 p_to_allowed_status_id IN number,
382 p_new_start_date IN date,
383 x_errorCode OUT NOCOPY number,
384 x_errorMessage OUT NOCOPY varchar2,
385 x_return_code OUT NOCOPY varchar2)
386 IS
387
388 l_FromDate DATE;
389 l_ToDate DATE;
390
391 BEGIN
392 l_FromDate := TRUNC(NVL(returnStartDate(p_from_allowed_status_id),sysdate));
393 l_ToDate := TRUNC(NVL(returnStartDate(p_to_allowed_status_id),sysdate));
394
395 x_return_code := 'S';
396 IF ( (TRUNC(NVL(p_new_start_date,sysdate)) < l_FromDate) OR (TRUNC(NVL(p_new_start_date,sysdate)) < l_ToDate) ) THEN
397 x_return_code := 'E';
398 END IF;
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 x_errorCode := 1;
403 x_errorMessage := SUBSTR(SQLERRM,1,280);
404
405 END StatusTrans_StartDate_Valid;
406
407
408 Procedure StatusTrans_EndDate_Valid(p_statusGroupId IN number,
409 p_from_allowed_status_id IN number,
410 p_to_allowed_status_id IN number,
411 p_new_end_date IN date,
412 x_errorCode OUT NOCOPY number,
413 x_errorMessage OUT NOCOPY varchar2,
414 x_return_code OUT NOCOPY varchar2)
415
416 IS
417
418 l_FromDate DATE;
419 l_ToDate DATE;
420 BEGIN
421 l_FromDate := returnEndDate(p_from_allowed_status_id);
422 l_ToDate := returnEndDate(p_to_allowed_status_id);
423
424
425 x_return_code := 'S';
426
427
431 WHERE allowed_status_id in (p_to_allowed_status_id,p_from_allowed_status_id)
428 IF (p_new_end_date is null) THEN
429 UPDATE cs_sr_allowed_statuses
430 SET end_date = null
432 AND status_group_id = p_statusGroupId;
433 ELSE
434
435 IF (l_FromDate is null) THEN
436 l_FromDate := p_new_end_date + 1;
437 END IF;
438
439 IF (l_ToDate is null) THEN
440 l_ToDate := p_new_end_date + 1;
441 END IF;
442
443 IF ((to_char(p_new_end_date,'DD-MON-RRRR') > to_char(l_FromDate,'DD-MON-RRRR'))
444 OR (to_char(p_new_end_date,'DD-MON-RRRR') > to_char(l_ToDate,'DD-MON-RRRR'))) THEN
445 x_return_code := 'E';
446 END IF;
447 END IF;
448
449 EXCEPTION
450 WHEN OTHERS THEN
451 x_errorCode := 1;
452 x_errorMessage := SUBSTR(SQLERRM,1,280);
453
454
455
456 END StatusTrans_EndDate_Valid;
457
458
459 Procedure Set_Transition_Ind(p_statusGroupId IN number)
460 IS
461 Status_Transition_Counter NUMBER:=0;
462 Indicator varchar2(1):=null;
463
464 BEGIN
465 SELECT count(0)
466 INTO Status_Transition_Counter
467 FROM cs_sr_status_transitions
468 WHERE status_group_id = p_statusGroupId;
469
470 IF (Status_Transition_Counter > 0) THEN
471 Indicator := 'Y';
472 ELSE
473 Indicator := null;
474 END IF;
475
476 UPDATE cs_sr_status_groups_b
477 SET transition_ind = Indicator
478 WHERE status_group_id = p_statusGroupId;
479
480 END Set_Transition_Ind;
481
482
483 END CS_ALW_STS_TRANSITIONS_PVT;