[Home] [Help]
PACKAGE BODY: APPS.CS_KB_SESSION_ATTR_PKG
Source
1 PACKAGE BODY CS_KB_SESSION_ATTR_PKG AS
2 /* $Header: cskbssab.pls 115.5 2002/12/02 22:41:15 mkettle noship $ */
3
4 /* HIGH LEVEL TABLE HANDLERS */
5
6 function add_km_session_attr
7 (
8 X_SESSION_ATTR_ID OUT NOCOPY NUMBER,
9 P_SESSION_ID in NUMBER,
10 P_ATTRIBUTE_TYPE in VARCHAR2,
11 P_ATTRIBUTE_NAME in VARCHAR2,
12 P_VALUE1 in VARCHAR2,
13 P_VALUE2 in VARCHAR2
14 ) return number
15 is
16 l_rowid varchar2(30);
17 l_current_date date;
18 l_current_user number;
19 l_current_login number;
20 begin
21 l_current_date := sysdate;
22 l_current_user := fnd_global.user_id;
23 l_current_login := fnd_global.login_id;
24 insert_row
25 (
26 x_rowid => l_rowid,
27 x_session_attr_id => x_session_attr_id,
28 p_session_id => p_session_id,
29 p_attribute_type => p_attribute_type,
30 p_attribute_name => p_attribute_name,
31 p_value1 => p_value1,
32 p_value2 => p_value2,
33 p_creation_date => l_current_date,
34 p_created_by => l_current_user,
35 p_last_update_date => l_current_date,
36 p_last_updated_by => l_current_user,
37 p_last_update_login => l_current_login
38 );
39 return OKAY_STATUS;
40 exception
41 when others then
42 return ERROR_STATUS;
43 end ;
44
45 function update_km_session_attr
46 (
47 P_SESSION_ATTR_ID in NUMBER,
48 P_SESSION_ID in NUMBER,
49 P_ATTRIBUTE_TYPE in VARCHAR2,
50 P_ATTRIBUTE_NAME in VARCHAR2,
51 P_VALUE1 in VARCHAR2,
52 P_VALUE2 in VARCHAR2
53 ) return number
54 is
55 l_current_date date;
56 l_current_user number;
57 l_current_login number;
58 begin
59 l_current_date := sysdate;
60 l_current_user := fnd_global.user_id;
61 l_current_login := fnd_global.login_id;
62 update_row
63 (
64 p_session_attr_id => p_session_attr_id,
65 p_session_id => p_session_id,
66 p_attribute_type => p_attribute_type,
67 p_attribute_name => p_attribute_name,
68 p_value1 => p_value1,
69 p_value2 => p_value2,
70 p_last_update_date => l_current_date,
71 p_last_updated_by => l_current_user,
72 p_last_update_login => l_current_login
73 );
74 return OKAY_STATUS;
75 exception
76 when others then
77 return ERROR_STATUS;
78 end ;
79
80 function remove_km_session_attr
81 (
82 P_SESSION_ATTR_ID in NUMBER
83 ) return number
84 is
85 l_current_date date;
86 l_current_user number;
87 l_current_login number;
88 begin
89 l_current_date := sysdate;
90 l_current_user := fnd_global.user_id;
91 l_current_login := fnd_global.login_id;
92 delete_row
93 (
94 p_session_attr_id => p_session_attr_id
95 );
96 return OKAY_STATUS;
97 exception
98 when others then
99 return ERROR_STATUS;
100 end ;
101
102 function remove_all_km_session_attrs
103 (
104 P_SESSION_ID in NUMBER
105 ) return number
106 is
107 cursor getSessionAttrIds( c_session_id number ) is
108 select session_attr_id
109 from cs_kb_session_attrs
110 where session_id = c_session_id;
111 begin
112 for rec in getSessionAttrIds( p_session_id ) loop
113 delete_row
114 (
115 p_session_attr_id => rec.session_attr_id
116 );
117 end loop;
118 return OKAY_STATUS;
119 exception
120 when others then
121 return ERROR_STATUS;
122 end ;
123
124
125 /* LOW LEVEL TABLE HANDLERS */
126
127 procedure INSERT_ROW
128 (
129 X_ROWID OUT NOCOPY VARCHAR2,
130 X_SESSION_ATTR_ID OUT NOCOPY NUMBER,
131 P_SESSION_ID in NUMBER,
132 P_ATTRIBUTE_TYPE in VARCHAR2,
133 P_ATTRIBUTE_NAME in VARCHAR2,
134 P_VALUE1 in VARCHAR2,
135 P_VALUE2 in VARCHAR2,
136 P_CREATION_DATE in DATE,
137 P_CREATED_BY in NUMBER,
138 P_LAST_UPDATE_DATE in DATE,
139 P_LAST_UPDATED_BY in NUMBER,
140 P_LAST_UPDATE_LOGIN in NUMBER
141 )
142 is
143 cursor getNewSessionAttrIdCsr is
144 select cs_kb_session_attrs_s.nextval
145 from dual;
146
147 cursor verifyRowCursor is
148 select ROWID
149 from CS_KB_SESSION_ATTRS
150 where SESSION_ATTR_ID = X_SESSION_ATTR_ID;
151 begin
152
153 /* Get a new data id */
154 OPEN getNewSessionAttrIdCsr;
155 FETCH getNewSessionAttrIdCsr INTO X_SESSION_ATTR_ID;
156 CLOSE getNewSessionAttrIdCsr;
157
158 insert into cs_kb_session_attrs
159 (
160 session_attr_id,
161 session_id,
162 attribute_type,
163 attribute_name,
164 value1,
165 value2,
166 creation_date,
167 created_by,
168 last_update_date,
169 last_updated_by,
170 last_update_login
171 )
172 values
173 (
174 x_session_attr_id,
175 p_session_id,
176 p_attribute_type,
177 p_attribute_name,
178 p_value1,
179 p_value2,
180 p_creation_date,
181 p_created_by,
182 p_last_update_date,
183 p_last_updated_by,
184 p_last_update_login
185 );
186
187 OPEN verifyRowCursor;
188 FETCH verifyRowCursor INTO x_rowid;
189 IF ( verifyRowCursor%NOTFOUND)
190 THEN
191 CLOSE verifyRowCursor;
192 RAISE NO_DATA_FOUND;
193 ELSE
194 CLOSE verifyRowCursor;
195 END IF;
196
197 end INSERT_ROW;
198
199 procedure UPDATE_ROW
200 (
201 P_SESSION_ATTR_ID in NUMBER,
202 P_SESSION_ID in NUMBER,
203 P_ATTRIBUTE_TYPE in VARCHAR2,
204 P_ATTRIBUTE_NAME in VARCHAR2,
205 P_VALUE1 in VARCHAR2,
206 P_VALUE2 in VARCHAR2,
207 P_LAST_UPDATE_DATE in DATE,
208 P_LAST_UPDATED_BY in NUMBER,
209 P_LAST_UPDATE_LOGIN in NUMBER
210 )
211 is
212 begin
213 update cs_kb_session_attrs
214 set
215 session_id = p_session_id,
216 attribute_type = p_attribute_type,
217 attribute_name = p_attribute_name,
218 value1 = p_value1,
219 value2 = p_value2,
220 last_update_date = p_last_update_date,
221 last_updated_by = p_last_updated_by,
222 last_update_login = p_last_update_login
223 where session_attr_id = P_SESSION_ATTR_ID;
224
225 if(SQL%NOTFOUND)
226 then
227 raise NO_DATA_FOUND;
228 end if;
229 end UPDATE_ROW;
230
231 procedure DELETE_ROW
232 (
233 P_SESSION_ATTR_ID in NUMBER
234 )
235 is
236 begin
237 delete from cs_kb_session_attrs
238 where session_attr_id = P_SESSION_ATTR_ID;
239 if(sql%notfound) then
240 raise no_data_found;
241 end if;
242 end DELETE_ROW;
243
244
245 procedure LOAD_ROW
246 (
247 P_SESSION_ATTR_ID in NUMBER,
248 P_SESSION_ID in NUMBER,
249 P_ATTRIBUTE_TYPE in VARCHAR2,
250 P_ATTRIBUTE_NAME in VARCHAR2,
251 P_VALUE1 in VARCHAR2,
252 P_VALUE2 in VARCHAR2,
253 P_OWNER in VARCHAR2
254 )
255 is
256 l_user_id number;
257 l_rowid varchar2(100);
258 l_session_attr_id number := p_session_attr_id;
259 begin
260 if ( p_owner = 'SEED' ) then
261 l_user_id := 1;
262 else
263 l_user_id := 0;
264 end if;
265
266 update_row
267 (
268 p_session_attr_id => p_session_attr_id,
269 p_session_id => p_session_id,
270 p_attribute_type => p_attribute_type,
271 p_attribute_name => p_attribute_name,
272 p_value1 => p_value1,
273 p_value2 => p_value2,
277 );
274 p_last_update_date => sysdate,
275 p_last_updated_by => l_user_id,
276 p_last_update_login => 0
278
279 exception
280 when no_data_found
281 then
282 insert_row
283 (
284 x_rowid => l_rowid,
285 x_session_attr_id => l_session_attr_id,
286 p_session_id => p_session_id,
287 p_attribute_type => p_attribute_type,
288 p_attribute_name => p_attribute_name,
289 p_value1 => p_value1,
290 p_value2 => p_value2,
291 p_creation_date => sysdate,
292 p_created_by => l_user_id,
293 p_last_update_date => sysdate,
294 p_last_updated_by => l_user_id,
295 p_last_update_login => 0
296 );
297
298 end LOAD_ROW;
299
300
301 END CS_KB_SESSION_ATTR_PKG;