1 package body CS_SR_LOAD_BALANCE_WT_PKG as
2 /* $Header: csviwetb.pls 115.6 2002/12/11 02:01:37 dejoseph noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_WEIGHT_ID in NUMBER,
6 X_INCIDENT_TYPE_ID in NUMBER,
7 X_INCIDENT_SEVERITY_ID in NUMBER,
8 X_START_DATE_ACTIVE in DATE,
9 X_END_DATE_ACTIVE in DATE,
10 X_PRODUCT_SKILL_WT in NUMBER,
11 X_PLATFORM_SKILL_WT in NUMBER,
12 X_PROB_CODE_SKILL_WT in NUMBER,
13 X_LAST_LOGIN_TIME_WT in NUMBER,
14 X_SEVERITY1_COUNT_WT in NUMBER,
15 X_SEVERITY2_COUNT_WT in NUMBER,
16 X_SEVERITY3_COUNT_WT in NUMBER,
17 X_SEVERITY4_COUNT_WT in NUMBER,
18 X_TIME_ZONE_DIFF_WT in NUMBER,
19 X_CATEGORY_SKILL_WT in NUMBER,
20 X_ATTRIBUTE1 in VARCHAR2,
21 X_ATTRIBUTE2 in VARCHAR2,
22 X_ATTRIBUTE3 in VARCHAR2,
23 X_ATTRIBUTE4 in VARCHAR2,
24 X_ATTRIBUTE5 in VARCHAR2,
25 X_ATTRIBUTE6 in VARCHAR2,
26 X_ATTRIBUTE7 in VARCHAR2,
27 X_ATTRIBUTE8 in VARCHAR2,
28 X_ATTRIBUTE9 in VARCHAR2,
29 X_ATTRIBUTE10 in VARCHAR2,
30 X_ATTRIBUTE11 in VARCHAR2,
31 X_ATTRIBUTE12 in VARCHAR2,
32 X_ATTRIBUTE13 in VARCHAR2,
33 X_ATTRIBUTE14 in VARCHAR2,
34 X_ATTRIBUTE15 in VARCHAR2,
35 X_CONTEXT in VARCHAR2,
36 X_OBJECT_VERSION_NUMBER in NUMBER,
37 X_CREATION_DATE in DATE,
38 X_CREATED_BY in NUMBER,
39 X_LAST_UPDATE_DATE in DATE,
40 X_LAST_UPDATED_BY in NUMBER,
41 X_LAST_UPDATE_LOGIN in NUMBER)
42 is
43 cursor C is select ROWID from CS_SR_LOAD_BALANCE_WT
44 where WEIGHT_ID = X_WEIGHT_ID
45 ;
46 begin
47 insert into CS_SR_LOAD_BALANCE_WT (
48 WEIGHT_ID ,
49 INCIDENT_TYPE_ID ,
50 INCIDENT_SEVERITY_ID ,
51 START_DATE_ACTIVE ,
52 END_DATE_ACTIVE ,
53 PRODUCT_SKILL_WT ,
54 PLATFORM_SKILL_WT ,
55 PROB_CODE_SKILL_WT ,
56 LAST_LOGIN_TIME_WT ,
57 SEVERITY1_COUNT_WT ,
58 SEVERITY2_COUNT_WT ,
59 SEVERITY3_COUNT_WT ,
60 SEVERITY4_COUNT_WT ,
61 TIME_ZONE_DIFF_WT ,
62 CATEGORY_SKILL_WT ,
63 ATTRIBUTE1 ,
64 ATTRIBUTE2 ,
65 ATTRIBUTE3 ,
66 ATTRIBUTE4 ,
67 ATTRIBUTE5 ,
68 ATTRIBUTE6 ,
69 ATTRIBUTE7 ,
70 ATTRIBUTE8 ,
71 ATTRIBUTE9 ,
72 ATTRIBUTE10 ,
73 ATTRIBUTE11 ,
74 ATTRIBUTE12 ,
75 ATTRIBUTE13 ,
76 ATTRIBUTE14 ,
77 ATTRIBUTE15 ,
78 CONTEXT ,
79 OBJECT_VERSION_NUMBER,
80 CREATION_DATE ,
81 CREATED_BY ,
82 LAST_UPDATE_DATE ,
83 LAST_UPDATED_BY ,
84 LAST_UPDATE_LOGIN )
85 values (
86 X_WEIGHT_ID ,
87 X_INCIDENT_TYPE_ID ,
88 X_INCIDENT_SEVERITY_ID ,
89 X_START_DATE_ACTIVE ,
90 X_END_DATE_ACTIVE ,
91 X_PRODUCT_SKILL_WT,
92 X_PLATFORM_SKILL_WT ,
93 X_PROB_CODE_SKILL_WT,
94 X_LAST_LOGIN_TIME_WT ,
95 X_SEVERITY1_COUNT_WT ,
96 X_SEVERITY2_COUNT_WT ,
97 X_SEVERITY3_COUNT_WT ,
98 X_SEVERITY4_COUNT_WT ,
99 X_TIME_ZONE_DIFF_WT ,
100 X_CATEGORY_SKILL_WT ,
101 X_ATTRIBUTE1 ,
102 X_ATTRIBUTE2 ,
103 X_ATTRIBUTE3 ,
104 X_ATTRIBUTE4 ,
105 X_ATTRIBUTE5 ,
106 X_ATTRIBUTE6 ,
107 X_ATTRIBUTE7 ,
108 X_ATTRIBUTE8 ,
109 X_ATTRIBUTE9 ,
110 X_ATTRIBUTE10 ,
111 X_ATTRIBUTE11 ,
112 X_ATTRIBUTE12 ,
113 X_ATTRIBUTE13 ,
114 X_ATTRIBUTE14 ,
115 X_ATTRIBUTE15 ,
116 X_CONTEXT ,
117 X_OBJECT_VERSION_NUMBER,
118 X_CREATION_DATE ,
119 X_CREATED_BY ,
120 X_LAST_UPDATE_DATE ,
121 X_LAST_UPDATED_BY ,
122 X_LAST_UPDATE_LOGIN );
123
124 open c;
125 fetch c into X_ROWID;
126 if (c%notfound) then
127 close c;
128 raise no_data_found;
129 end if;
130 close c;
131
132 end INSERT_ROW;
133
134 procedure LOCK_ROW (
135 X_WEIGHT_ID in NUMBER,
136 X_INCIDENT_TYPE_ID in NUMBER,
137 X_INCIDENT_SEVERITY_ID in NUMBER,
138 X_START_DATE_ACTIVE in DATE,
139 X_END_DATE_ACTIVE in DATE,
140 X_PRODUCT_SKILL_WT in NUMBER,
141 X_PLATFORM_SKILL_WT in NUMBER,
142 X_PROB_CODE_SKILL_WT in NUMBER,
143 X_LAST_LOGIN_TIME_WT in NUMBER,
144 X_SEVERITY1_COUNT_WT in NUMBER,
145 X_SEVERITY2_COUNT_WT in NUMBER,
146 X_SEVERITY3_COUNT_WT in NUMBER,
147 X_SEVERITY4_COUNT_WT in NUMBER,
148 X_TIME_ZONE_DIFF_WT in NUMBER,
149 X_CATEGORY_SKILL_WT in NUMBER,
150 X_ATTRIBUTE1 in VARCHAR2,
151 X_ATTRIBUTE2 in VARCHAR2,
152 X_ATTRIBUTE3 in VARCHAR2,
153 X_ATTRIBUTE4 in VARCHAR2,
154 X_ATTRIBUTE5 in VARCHAR2,
155 X_ATTRIBUTE6 in VARCHAR2,
156 X_ATTRIBUTE7 in VARCHAR2,
157 X_ATTRIBUTE8 in VARCHAR2,
158 X_ATTRIBUTE9 in VARCHAR2,
159 X_ATTRIBUTE10 in VARCHAR2,
160 X_ATTRIBUTE11 in VARCHAR2,
161 X_ATTRIBUTE12 in VARCHAR2,
162 X_ATTRIBUTE13 in VARCHAR2,
163 X_ATTRIBUTE14 in VARCHAR2,
164 X_ATTRIBUTE15 in VARCHAR2,
165 X_CONTEXT in VARCHAR2,
166 X_OBJECT_VERSION_NUMBER in NUMBER
167 ) is
168 cursor c is select
169 INCIDENT_TYPE_ID ,
170 INCIDENT_SEVERITY_ID ,
171 START_DATE_ACTIVE ,
172 END_DATE_ACTIVE ,
173 PRODUCT_SKILL_WT ,
174 PLATFORM_SKILL_WT ,
175 PROB_CODE_SKILL_WT ,
176 LAST_LOGIN_TIME_WT ,
177 SEVERITY1_COUNT_WT ,
178 SEVERITY2_COUNT_WT ,
179 SEVERITY3_COUNT_WT ,
180 SEVERITY4_COUNT_WT ,
181 TIME_ZONE_DIFF_WT ,
182 CATEGORY_SKILL_WT ,
183 ATTRIBUTE1 ,
184 ATTRIBUTE2 ,
185 ATTRIBUTE3 ,
186 ATTRIBUTE4 ,
187 ATTRIBUTE5 ,
188 ATTRIBUTE6 ,
189 ATTRIBUTE7 ,
190 ATTRIBUTE8 ,
191 ATTRIBUTE9 ,
192 ATTRIBUTE10 ,
193 ATTRIBUTE11 ,
194 ATTRIBUTE12 ,
195 ATTRIBUTE13 ,
196 ATTRIBUTE14 ,
197 ATTRIBUTE15 ,
198 CONTEXT,
199 OBJECT_VERSION_NUMBER
200 from CS_SR_LOAD_BALANCE_WT
201 where WEIGHT_ID = X_WEIGHT_ID
202 for update of WEIGHT_ID nowait;
203 recinfo c%rowtype;
204
205 begin
206 open c;
207 fetch c into recinfo;
208 if (c%notfound) then
209 close c;
210 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
211 app_exception.raise_exception;
212 end if;
213 close c;
214
215 if ((recinfo.INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID)
216 AND (recinfo.INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID)
217 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
218 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
219 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
220 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
221 AND ((recinfo.PRODUCT_SKILL_WT = X_PRODUCT_SKILL_WT)
222 OR ((recinfo.PRODUCT_SKILL_WT is null) AND (X_PRODUCT_SKILL_WT is null)))
223 AND ((recinfo.PLATFORM_SKILL_WT = X_PLATFORM_SKILL_WT)
224 OR ((recinfo.PLATFORM_SKILL_WT is null) AND (X_PLATFORM_SKILL_WT is null)))
225 AND ((recinfo.PROB_CODE_SKILL_WT = X_PROB_CODE_SKILL_WT)
226 OR ((recinfo.PROB_CODE_SKILL_WT is null) AND (X_PROB_CODE_SKILL_WT is null)))
227 AND ((recinfo.LAST_LOGIN_TIME_WT = X_LAST_LOGIN_TIME_WT)
228 OR ((recinfo.LAST_LOGIN_TIME_WT is null) AND (X_LAST_LOGIN_TIME_WT is null)))
229 AND ((recinfo.SEVERITY1_COUNT_WT = X_SEVERITY1_COUNT_WT)
230 OR ((recinfo.SEVERITY1_COUNT_WT is null) AND (X_SEVERITY1_COUNT_WT is null)))
231 AND ((recinfo.SEVERITY2_COUNT_WT = X_SEVERITY2_COUNT_WT)
232 OR ((recinfo.SEVERITY2_COUNT_WT is null) AND (X_SEVERITY2_COUNT_WT is null)))
233 AND ((recinfo.SEVERITY3_COUNT_WT = X_SEVERITY3_COUNT_WT)
234 OR ((recinfo.SEVERITY3_COUNT_WT is null) AND (X_SEVERITY3_COUNT_WT is null)))
235 AND ((recinfo.SEVERITY4_COUNT_WT = X_SEVERITY4_COUNT_WT)
236 OR ((recinfo.SEVERITY4_COUNT_WT is null) AND (X_SEVERITY4_COUNT_WT is null)))
237 AND ((recinfo.TIME_ZONE_DIFF_WT = X_TIME_ZONE_DIFF_WT)
238 OR ((recinfo.TIME_ZONE_DIFF_WT is null) AND (X_TIME_ZONE_DIFF_WT is null)))
239 AND ((recinfo.CATEGORY_SKILL_WT = X_CATEGORY_SKILL_WT)
240 OR ((recinfo.CATEGORY_SKILL_WT is null) AND(X_CATEGORY_SKILL_WT is null)))
241 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
242 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
243 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
244 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
245 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
246 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
247 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
248 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
249 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
250 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
251 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
252 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
253 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
254 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
255 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
256 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
257 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
258 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
259 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
260 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
261 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
262 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
263 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
264 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
265 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
266 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
267 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
268 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
269 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
270 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
271 AND ((recinfo.CONTEXT = X_CONTEXT)
272 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
273 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
274 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
275 ) then
276 null;
277 else
278 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
279 app_exception.raise_exception;
280 end if;
281
282 return;
283 end LOCK_ROW;
284
285 procedure UPDATE_ROW (
286 X_WEIGHT_ID in NUMBER,
287 X_INCIDENT_TYPE_ID in NUMBER,
288 X_INCIDENT_SEVERITY_ID in NUMBER,
289 X_START_DATE_ACTIVE in DATE,
290 X_END_DATE_ACTIVE in DATE,
291 X_PRODUCT_SKILL_WT in NUMBER,
292 X_PLATFORM_SKILL_WT in NUMBER,
293 X_PROB_CODE_SKILL_WT in NUMBER,
294 X_LAST_LOGIN_TIME_WT in NUMBER,
298 X_SEVERITY4_COUNT_WT in NUMBER,
295 X_SEVERITY1_COUNT_WT in NUMBER,
296 X_SEVERITY2_COUNT_WT in NUMBER,
297 X_SEVERITY3_COUNT_WT in NUMBER,
299 X_TIME_ZONE_DIFF_WT in NUMBER,
300 X_CATEGORY_SKILL_WT in NUMBER,
301 X_ATTRIBUTE1 in VARCHAR2,
302 X_ATTRIBUTE2 in VARCHAR2,
303 X_ATTRIBUTE3 in VARCHAR2,
304 X_ATTRIBUTE4 in VARCHAR2,
305 X_ATTRIBUTE5 in VARCHAR2,
306 X_ATTRIBUTE6 in VARCHAR2,
307 X_ATTRIBUTE7 in VARCHAR2,
308 X_ATTRIBUTE8 in VARCHAR2,
309 X_ATTRIBUTE9 in VARCHAR2,
310 X_ATTRIBUTE10 in VARCHAR2,
311 X_ATTRIBUTE11 in VARCHAR2,
312 X_ATTRIBUTE12 in VARCHAR2,
313 X_ATTRIBUTE13 in VARCHAR2,
314 X_ATTRIBUTE14 in VARCHAR2,
315 X_ATTRIBUTE15 in VARCHAR2,
316 X_CONTEXT in VARCHAR2,
317 X_OBJECT_VERSION_NUMBER in NUMBER,
318 X_LAST_UPDATE_DATE in DATE,
319 X_LAST_UPDATED_BY in NUMBER,
320 X_LAST_UPDATE_LOGIN in NUMBER
321 ) is
322 begin
323 update CS_SR_LOAD_BALANCE_WT set
324 INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID,
325 INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID,
326 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
327 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
328 PRODUCT_SKILL_WT = X_PRODUCT_SKILL_WT,
329 PLATFORM_SKILL_WT = X_PLATFORM_SKILL_WT,
330 PROB_CODE_SKILL_WT = X_PROB_CODE_SKILL_WT,
331 LAST_LOGIN_TIME_WT = X_LAST_LOGIN_TIME_WT,
332 SEVERITY1_COUNT_WT = X_SEVERITY1_COUNT_WT,
333 SEVERITY2_COUNT_WT = X_SEVERITY2_COUNT_WT,
334 SEVERITY3_COUNT_WT = X_SEVERITY3_COUNT_WT,
335 SEVERITY4_COUNT_WT = X_SEVERITY4_COUNT_WT,
336 TIME_ZONE_DIFF_WT = X_TIME_ZONE_DIFF_WT,
337 CATEGORY_SKILL_WT = X_CATEGORY_SKILL_WT,
338 ATTRIBUTE1 = X_ATTRIBUTE1,
339 ATTRIBUTE2 = X_ATTRIBUTE2,
340 ATTRIBUTE3 = X_ATTRIBUTE3,
341 ATTRIBUTE4 = X_ATTRIBUTE4,
342 ATTRIBUTE5 = X_ATTRIBUTE5,
343 ATTRIBUTE6 = X_ATTRIBUTE6,
344 ATTRIBUTE7 = X_ATTRIBUTE7,
345 ATTRIBUTE8 = X_ATTRIBUTE8,
346 ATTRIBUTE9 = X_ATTRIBUTE9,
347 ATTRIBUTE10 = X_ATTRIBUTE10,
348 ATTRIBUTE11 = X_ATTRIBUTE11,
349 ATTRIBUTE12 = X_ATTRIBUTE12,
350 ATTRIBUTE13 = X_ATTRIBUTE13,
351 ATTRIBUTE14 = X_ATTRIBUTE14,
352 ATTRIBUTE15 = X_ATTRIBUTE15,
353 CONTEXT = X_CONTEXT,
354 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
355 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
356 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
357 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
358 where WEIGHT_ID = X_WEIGHT_ID;
359
360 if (sql%notfound) then
361 raise no_data_found;
362 end if;
363 end UPDATE_ROW;
364
365 procedure DELETE_ROW(
366 X_WEIGHT_ID in NUMBER)
367 is
368 begin
369 delete from CS_SR_LOAD_BALANCE_WT
370 where WEIGHT_ID = X_WEIGHT_ID;
371
372 if(sql%notfound) then
373 raise no_data_found;
374 end if;
375
376 end DELETE_ROW;
377
378 end CS_SR_LOAD_BALANCE_WT_PKG;