1 PACKAGE BODY biv_version_specific_pkg AS
2 -- $Header: bivvrsnb.pls 115.1 2003/02/18 23:39:16 smisra noship $ */
3 function get_sr_table return varchar2 as
4 begin
5 return('cs_incidents_all_b');
6 end;
7 procedure set_update_program( p_sr_rec in out nocopy
8 CS_ServiceRequest_PVT.service_request_rec_type) as
9 begin
10 p_sr_rec.last_update_program_code := 'BIV_SR_UPDATE';
11 end;
12 procedure status_lov(p_sr_id number,
13 p_lov_sttmnt out nocopy varchar2) as
14 l_status_group_id cs_sr_type_mapping.status_group_id % type;
15 l_type_id cs_incidents_all_b.incident_type_id % type;
16 l_old_status_id cs_incident_statuses_b.incident_status_id % type;
17 --
18 cursor c_type is
19 select incident_type_id, incident_status_id
20 from cs_incidents_all_b
21 where incident_id = p_sr_id;
22 ---
23 cursor c_status_group1 is
24 select status_group_id
25 from cs_sr_type_mapping
26 where incident_type_id = l_type_id
27 and responsibility_id = fnd_profile.value('RESP_ID');
28 --
29 cursor c_status_group2 is
30 select status_group_id
31 from cs_incident_types_b
32 where incident_type_id = l_type_id;
33 --
34 cursor c_trans_ind is
35 select transition_ind
36 from cs_sr_status_groups_b
37 where status_group_id = l_status_group_id
38 and sysdate between nvl(start_date,sysdate-1) and nvl(end_date,sysdate+1);
39 l_trans_id cs_sr_status_groups_b.transition_ind % type;
40 l_lov_sttmnt1 varchar2(2000);
41 l_lov_sttmnt2 varchar2(2000);
42 l_lov_sttmnt3 varchar2(2000);
43 begin
44 open c_type;
45 fetch c_type into l_type_id, l_old_status_id;
46 close c_type;
47 -- set lov statements
48 l_lov_sttmnt1 := 'select incident_status_id, name, '' ''
49 from cs_incident_statuses_vl
50 where incident_subtype = ''INC''
51 and sysdate between nvl(start_date_active,sysdate-1)
52 and nvl(end_date_active,sysdate+1)
53 and upper(name) like upper(?)
54 order by name asc';
55 -- used when trans_id is Y
56 l_lov_sttmnt2 := '
57 select st.incident_status_id, st.name, '' ''
58 from cs_incident_statuses st,
59 cs_sr_status_transitions st_tran
60 where st_tran.from_incident_status_id = st.incident_status_id
61 and st_tran.from_incident_status_id=' || to_char(l_old_status_id) ||'
62 and trunc(sysdate) between trunc(nvl(st.start_date_active, sysdate))
63 and trunc(nvl(st.end_date_active, sysdate))
64 and trunc(sysdate) between trunc(nvl(st_tran.start_date, sysdate))
65 and trunc(nvl(st_tran.end_date, sysdate))
66 and upper(st.name) like upper(?)
67 union
68 select st.incident_status_id, st.name, '' ''
69 from cs_incident_statuses st
70 where st.incident_status_id = ' || to_char(l_old_status_id) ||'
71 order by 2';
72 -- used when trans_id is NOT Y
73 l_lov_sttmnt3 := '
74 select st.incident_status_id, st.name, '' ''
75 from cs_incident_statuses st,
76 cs_incident_types_b typ,
77 cs_sr_allowed_statuses allowd_st
78 where typ.incident_type_id = ' || to_char(l_type_id) || '
79 and typ.status_group_id = allowd_st.status_group_id
80 and allowd_st.incident_status_id = st.incident_status_id
81 and trunc(sysdate) between trunc(nvl(allowd_st.start_date,sysdate))
82 and trunc(nvl(allowd_st.end_date,sysdate))
83 and trunc(sysdate) between trunc(nvl(st.start_date_active, sysdate))
84 and trunc(nvl(st.end_date_active, sysdate))
85 and upper(st.name) like upper(?)
86 order by 2';
87 if (l_type_id is null) then
88 p_lov_sttmnt := l_lov_sttmnt1;
89 else
90 open c_status_group1;
91 fetch c_status_group1 into l_status_group_id;
92 if (c_status_group1%notfound or l_status_group_id is null) then
93 open c_status_group2;
94 fetch c_status_group2 into l_status_group_id;
95 close c_status_group2;
96 end if;
97 close c_status_group1;
98 if (l_status_group_id is null) then
99 p_lov_sttmnt := l_lov_sttmnt1;
100 else
101 open c_trans_ind;
102 fetch c_trans_ind into l_trans_id;
103 close c_trans_ind;
104 if (nvl(l_trans_id,'N') = 'Y') then
105 p_lov_sttmnt := l_lov_sttmnt2;
106 else
107 p_lov_sttmnt := l_lov_sttmnt3;
108 end if;
109 end if; -- for status group id
110 end if; -- for type id
111
112 biv_core_pkg.biv_debug(p_lov_sttmnt,'STATUS_LOV');
113 exception
114 when no_data_found then
115 p_lov_sttmnt := l_lov_sttmnt1;
116 end;
117 end;
[Home] [Help]