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