[Home] [Help]
PACKAGE BODY: APPS.PSP_ELEMENT_TYPES_PKG
Source
1 package body PSP_ELEMENT_TYPES_PKG as
2 /* $Header: PSPSUELB.pls 115.8 2003/09/08 16:10:57 spchakra ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_ELEMENT_TYPE_ID in NUMBER,
6 X_START_DATE_ACTIVE in DATE,
7 X_END_DATE_ACTIVE in DATE,
8 X_COMMENTS in VARCHAR2,
9 X_ADJUST in VARCHAR2, -- 2634557
10 X_ATTRIBUTE_CATEGORY in VARCHAR2,
11 X_ATTRIBUTE1 in VARCHAR2,
12 X_ATTRIBUTE2 in VARCHAR2,
13 X_ATTRIBUTE3 in VARCHAR2,
14 X_ATTRIBUTE4 in VARCHAR2,
15 X_ATTRIBUTE5 in VARCHAR2,
16 X_ATTRIBUTE6 in VARCHAR2,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE8 in VARCHAR2,
19 X_ATTRIBUTE9 in VARCHAR2,
20 X_ATTRIBUTE10 in VARCHAR2,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_ATTRIBUTE14 in VARCHAR2,
25 X_ATTRIBUTE15 in VARCHAR2,
26 X_MODE in VARCHAR2 default 'R',
27 X_BUSINESS_GROUP_ID IN NUMBER, -- Introduced for bug fix 3098050
28 X_SET_OF_BOOKS_ID IN NUMBER -- Introduced for bug fix 3098050
29 ) is
30 cursor C is select ROWID from PSP_ELEMENT_TYPES
31 where ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID
32 and START_DATE_ACTIVE = X_START_DATE_ACTIVE;
33 X_LAST_UPDATE_DATE DATE;
34 X_LAST_UPDATED_BY NUMBER;
35 X_LAST_UPDATE_LOGIN NUMBER;
36 begin
37 X_LAST_UPDATE_DATE := SYSDATE;
38 if(X_MODE = 'I') then
39 X_LAST_UPDATED_BY := 1;
40 X_LAST_UPDATE_LOGIN := 0;
41 elsif (X_MODE = 'R') then
42 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
43 if X_LAST_UPDATED_BY is NULL then
44 X_LAST_UPDATED_BY := -1;
45 end if;
46 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
47 if X_LAST_UPDATE_LOGIN is NULL then
48 X_LAST_UPDATE_LOGIN := -1;
49 end if;
50 else
51 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
52 app_exception.raise_exception;
53 end if;
54 insert into PSP_ELEMENT_TYPES (
55 ELEMENT_TYPE_ID,
56 START_DATE_ACTIVE,
57 END_DATE_ACTIVE,
58 COMMENTS,
59 ADJUST, --- 2634557
60 ATTRIBUTE_CATEGORY,
61 ATTRIBUTE1,
62 ATTRIBUTE2,
63 ATTRIBUTE3,
64 ATTRIBUTE4,
65 ATTRIBUTE5,
66 ATTRIBUTE6,
67 ATTRIBUTE7,
68 ATTRIBUTE8,
69 ATTRIBUTE9,
70 ATTRIBUTE10,
71 ATTRIBUTE11,
72 ATTRIBUTE12,
73 ATTRIBUTE13,
74 ATTRIBUTE14,
75 ATTRIBUTE15,
76 BUSINESS_GROUP_ID, -- Introduced for bug fix 3098050
77 SET_OF_BOOKS_ID, -- Introduced for bug fix 3098050
78 CREATION_DATE,
79 CREATED_BY,
80 LAST_UPDATE_DATE,
81 LAST_UPDATED_BY,
82 LAST_UPDATE_LOGIN
83 ) values (
84 X_ELEMENT_TYPE_ID,
85 X_START_DATE_ACTIVE,
86 X_END_DATE_ACTIVE,
87 X_COMMENTS,
88 X_ADJUST,
89 X_ATTRIBUTE_CATEGORY,
90 X_ATTRIBUTE1,
91 X_ATTRIBUTE2,
92 X_ATTRIBUTE3,
93 X_ATTRIBUTE4,
94 X_ATTRIBUTE5,
95 X_ATTRIBUTE6,
96 X_ATTRIBUTE7,
97 X_ATTRIBUTE8,
98 X_ATTRIBUTE9,
99 X_ATTRIBUTE10,
100 X_ATTRIBUTE11,
101 X_ATTRIBUTE12,
102 X_ATTRIBUTE13,
103 X_ATTRIBUTE14,
104 X_ATTRIBUTE15,
105 X_BUSINESS_GROUP_ID, -- Introduced for bug fix 3098050
106 X_SET_OF_BOOKS_ID, -- Introduced for bug fix 3098050
107 X_LAST_UPDATE_DATE,
108 X_LAST_UPDATED_BY,
109 X_LAST_UPDATE_DATE,
110 X_LAST_UPDATED_BY,
111 X_LAST_UPDATE_LOGIN
112 );
113
114 open c;
115 fetch c into X_ROWID;
116 if (c%notfound) then
117 close c;
118 raise no_data_found;
119 end if;
120 close c;
121
122 end INSERT_ROW;
123
124 procedure LOCK_ROW (
125 X_ELEMENT_TYPE_ID in NUMBER,
126 X_START_DATE_ACTIVE in DATE,
127 X_END_DATE_ACTIVE in DATE,
128 X_COMMENTS in VARCHAR2,
129 X_ADJUST in VARCHAR2, -- 2634557
130 X_ATTRIBUTE_CATEGORY in VARCHAR2,
131 X_ATTRIBUTE1 in VARCHAR2,
132 X_ATTRIBUTE2 in VARCHAR2,
133 X_ATTRIBUTE3 in VARCHAR2,
134 X_ATTRIBUTE4 in VARCHAR2,
135 X_ATTRIBUTE5 in VARCHAR2,
136 X_ATTRIBUTE6 in VARCHAR2,
137 X_ATTRIBUTE7 in VARCHAR2,
138 X_ATTRIBUTE8 in VARCHAR2,
139 X_ATTRIBUTE9 in VARCHAR2,
140 X_ATTRIBUTE10 in VARCHAR2,
141 X_ATTRIBUTE11 in VARCHAR2,
142 X_ATTRIBUTE12 in VARCHAR2,
143 X_ATTRIBUTE13 in VARCHAR2,
144 X_ATTRIBUTE14 in VARCHAR2,
145 X_ATTRIBUTE15 in VARCHAR2,
146 X_BUSINESS_GROUP_ID IN NUMBER, -- Introduced for bug fix 3098050
147 X_SET_OF_BOOKS_ID IN NUMBER -- Introduced for bug fix 3098050
148 ) is
149 cursor c1 is select
150 END_DATE_ACTIVE,
151 COMMENTS,
152 ADJUST,
153 ATTRIBUTE_CATEGORY,
154 ATTRIBUTE1,
155 ATTRIBUTE2,
156 ATTRIBUTE3,
157 ATTRIBUTE4,
158 ATTRIBUTE5,
159 ATTRIBUTE6,
160 ATTRIBUTE7,
161 ATTRIBUTE8,
162 ATTRIBUTE9,
163 ATTRIBUTE10,
164 ATTRIBUTE11,
165 ATTRIBUTE12,
166 ATTRIBUTE13,
167 ATTRIBUTE14,
168 ATTRIBUTE15,
169 BUSINESS_GROUP_ID, -- Introduced for bug fix 3098050
170 SET_OF_BOOKS_ID -- Introduced for bug fix 3098050
171 from PSP_ELEMENT_TYPES
172 where ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID
173 and START_DATE_ACTIVE = X_START_DATE_ACTIVE
174 -- Introduced BG/SOB check for bug fix 3098050
175 AND BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
176 AND SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID
177 for update of ELEMENT_TYPE_ID nowait;
178 tlinfo c1%rowtype;
179
180 begin
181 open c1;
182 fetch c1 into tlinfo;
183 if (c1%notfound) then
184 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
185 app_exception.raise_exception;
186 close c1;
187 return;
188 end if;
189 close c1;
190
191 if ( ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
192 OR ((tlinfo.END_DATE_ACTIVE is null)
193 AND (X_END_DATE_ACTIVE is null)))
194 AND ((tlinfo.COMMENTS = X_COMMENTS)
195 OR ((tlinfo.COMMENTS is null)
196 AND (X_COMMENTS is null)))
197 AND ((tlinfo.ADJUST = X_ADJUST)
198 OR ((tlinfo.ADJUST is null)
199 AND (X_ADJUST is null)))
200 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
201 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
202 AND (X_ATTRIBUTE_CATEGORY is null)))
203 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
204 OR ((tlinfo.ATTRIBUTE1 is null)
205 AND (X_ATTRIBUTE1 is null)))
206 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
207 OR ((tlinfo.ATTRIBUTE2 is null)
208 AND (X_ATTRIBUTE2 is null)))
209 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
210 OR ((tlinfo.ATTRIBUTE3 is null)
211 AND (X_ATTRIBUTE3 is null)))
212 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
213 OR ((tlinfo.ATTRIBUTE4 is null)
214 AND (X_ATTRIBUTE4 is null)))
215 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
216 OR ((tlinfo.ATTRIBUTE5 is null)
217 AND (X_ATTRIBUTE5 is null)))
218 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
219 OR ((tlinfo.ATTRIBUTE6 is null)
220 AND (X_ATTRIBUTE6 is null)))
221 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
222 OR ((tlinfo.ATTRIBUTE7 is null)
223 AND (X_ATTRIBUTE7 is null)))
224 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
225 OR ((tlinfo.ATTRIBUTE8 is null)
226 AND (X_ATTRIBUTE8 is null)))
227 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
228 OR ((tlinfo.ATTRIBUTE9 is null)
229 AND (X_ATTRIBUTE9 is null)))
230 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
231 OR ((tlinfo.ATTRIBUTE10 is null)
232 AND (X_ATTRIBUTE10 is null)))
233 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
234 OR ((tlinfo.ATTRIBUTE11 is null)
235 AND (X_ATTRIBUTE11 is null)))
236 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
237 OR ((tlinfo.ATTRIBUTE12 is null)
238 AND (X_ATTRIBUTE12 is null)))
239 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
240 OR ((tlinfo.ATTRIBUTE13 is null)
241 AND (X_ATTRIBUTE13 is null)))
242 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
243 OR ((tlinfo.ATTRIBUTE14 is null)
244 AND (X_ATTRIBUTE14 is null)))
245 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
246 OR ((tlinfo.ATTRIBUTE15 is null)
247 AND (X_ATTRIBUTE15 is null)))
248 ) then
249 null;
250 else
251 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
252 app_exception.raise_exception;
253 end if;
254 return;
255 end LOCK_ROW;
256
257 procedure UPDATE_ROW (
258 X_ELEMENT_TYPE_ID in NUMBER,
259 X_START_DATE_ACTIVE in DATE,
260 X_END_DATE_ACTIVE in DATE,
261 X_COMMENTS in VARCHAR2,
262 X_ADJUST in VARCHAR2,
263 X_ATTRIBUTE_CATEGORY in VARCHAR2,
264 X_ATTRIBUTE1 in VARCHAR2,
265 X_ATTRIBUTE2 in VARCHAR2,
266 X_ATTRIBUTE3 in VARCHAR2,
267 X_ATTRIBUTE4 in VARCHAR2,
268 X_ATTRIBUTE5 in VARCHAR2,
269 X_ATTRIBUTE6 in VARCHAR2,
270 X_ATTRIBUTE7 in VARCHAR2,
271 X_ATTRIBUTE8 in VARCHAR2,
272 X_ATTRIBUTE9 in VARCHAR2,
273 X_ATTRIBUTE10 in VARCHAR2,
274 X_ATTRIBUTE11 in VARCHAR2,
275 X_ATTRIBUTE12 in VARCHAR2,
276 X_ATTRIBUTE13 in VARCHAR2,
277 X_ATTRIBUTE14 in VARCHAR2,
278 X_ATTRIBUTE15 in VARCHAR2,
279 X_MODE in VARCHAR2 default 'R',
280 X_BUSINESS_GROUP_ID IN NUMBER, -- Introduced for bug fix 3098050
281 X_SET_OF_BOOKS_ID IN NUMBER -- Introduced for bug fix 3098050
282 ) is
283 X_LAST_UPDATE_DATE DATE;
284 X_LAST_UPDATED_BY NUMBER;
285 X_LAST_UPDATE_LOGIN NUMBER;
286 begin
287 X_LAST_UPDATE_DATE := SYSDATE;
288 if(X_MODE = 'I') then
289 X_LAST_UPDATED_BY := 1;
290 X_LAST_UPDATE_LOGIN := 0;
291 elsif (X_MODE = 'R') then
292 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
293 if X_LAST_UPDATED_BY is NULL then
294 X_LAST_UPDATED_BY := -1;
295 end if;
296 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
297 if X_LAST_UPDATE_LOGIN is NULL then
298 X_LAST_UPDATE_LOGIN := -1;
299 end if;
300 else
301 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
302 app_exception.raise_exception;
303 end if;
304 update PSP_ELEMENT_TYPES set
305 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
306 COMMENTS = X_COMMENTS,
307 ADJUST = X_ADJUST,
308 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
309 ATTRIBUTE1 = X_ATTRIBUTE1,
310 ATTRIBUTE2 = X_ATTRIBUTE2,
311 ATTRIBUTE3 = X_ATTRIBUTE3,
312 ATTRIBUTE4 = X_ATTRIBUTE4,
313 ATTRIBUTE5 = X_ATTRIBUTE5,
314 ATTRIBUTE6 = X_ATTRIBUTE6,
315 ATTRIBUTE7 = X_ATTRIBUTE7,
316 ATTRIBUTE8 = X_ATTRIBUTE8,
317 ATTRIBUTE9 = X_ATTRIBUTE9,
318 ATTRIBUTE10 = X_ATTRIBUTE10,
319 ATTRIBUTE11 = X_ATTRIBUTE11,
320 ATTRIBUTE12 = X_ATTRIBUTE12,
321 ATTRIBUTE13 = X_ATTRIBUTE13,
322 ATTRIBUTE14 = X_ATTRIBUTE14,
323 ATTRIBUTE15 = X_ATTRIBUTE15,
324 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
325 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
326 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
327 where ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID
328 and START_DATE_ACTIVE = X_START_DATE_ACTIVE
329 -- Introduced BG/SOB check for bug fix 3098050
330 AND BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
331 AND SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID
332 ;
333 if (sql%notfound) then
334 raise no_data_found;
335 end if;
336 end UPDATE_ROW;
337
338 procedure ADD_ROW (
339 X_ROWID in out NOCOPY VARCHAR2,
340 X_ELEMENT_TYPE_ID in NUMBER,
341 X_START_DATE_ACTIVE in DATE,
342 X_END_DATE_ACTIVE in DATE,
343 X_COMMENTS in VARCHAR2,
344 X_ADJUST in VARCHAR2,
345 X_ATTRIBUTE_CATEGORY in VARCHAR2,
346 X_ATTRIBUTE1 in VARCHAR2,
347 X_ATTRIBUTE2 in VARCHAR2,
348 X_ATTRIBUTE3 in VARCHAR2,
349 X_ATTRIBUTE4 in VARCHAR2,
350 X_ATTRIBUTE5 in VARCHAR2,
351 X_ATTRIBUTE6 in VARCHAR2,
352 X_ATTRIBUTE7 in VARCHAR2,
353 X_ATTRIBUTE8 in VARCHAR2,
354 X_ATTRIBUTE9 in VARCHAR2,
355 X_ATTRIBUTE10 in VARCHAR2,
356 X_ATTRIBUTE11 in VARCHAR2,
357 X_ATTRIBUTE12 in VARCHAR2,
358 X_ATTRIBUTE13 in VARCHAR2,
359 X_ATTRIBUTE14 in VARCHAR2,
363 X_SET_OF_BOOKS_ID IN NUMBER -- Introduced for bug fix 3098050
360 X_ATTRIBUTE15 in VARCHAR2,
361 X_MODE in VARCHAR2 default 'R',
362 X_BUSINESS_GROUP_ID IN NUMBER, -- Introduced for bug fix 3098050
364 ) is
365 cursor c1 is select rowid from PSP_ELEMENT_TYPES
366 where ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID
367 and START_DATE_ACTIVE = X_START_DATE_ACTIVE
368 ;
369 dummy c1%rowtype;
370 begin
371 open c1;
372 fetch c1 into dummy;
373 if (c1%notfound) then
374 close c1;
375 INSERT_ROW (
376 X_ROWID,
377 X_ELEMENT_TYPE_ID,
378 X_START_DATE_ACTIVE,
379 X_END_DATE_ACTIVE,
380 X_COMMENTS,
381 X_ADJUST,
382 X_ATTRIBUTE_CATEGORY,
383 X_ATTRIBUTE1,
384 X_ATTRIBUTE2,
385 X_ATTRIBUTE3,
386 X_ATTRIBUTE4,
387 X_ATTRIBUTE5,
388 X_ATTRIBUTE6,
389 X_ATTRIBUTE7,
390 X_ATTRIBUTE8,
391 X_ATTRIBUTE9,
392 X_ATTRIBUTE10,
393 X_ATTRIBUTE11,
394 X_ATTRIBUTE12,
395 X_ATTRIBUTE13,
396 X_ATTRIBUTE14,
397 X_ATTRIBUTE15,
398 X_MODE,
399 X_BUSINESS_GROUP_ID, -- Introduced for bug fix 3098050
400 X_SET_OF_BOOKS_ID); -- Introduced for bug fix 3098050
401 return;
402 end if;
403 close c1;
404 UPDATE_ROW (
405 X_ELEMENT_TYPE_ID,
406 X_START_DATE_ACTIVE,
407 X_END_DATE_ACTIVE,
408 X_COMMENTS,
409 X_ADJUST,
410 X_ATTRIBUTE_CATEGORY,
411 X_ATTRIBUTE1,
412 X_ATTRIBUTE2,
413 X_ATTRIBUTE3,
414 X_ATTRIBUTE4,
415 X_ATTRIBUTE5,
416 X_ATTRIBUTE6,
417 X_ATTRIBUTE7,
418 X_ATTRIBUTE8,
419 X_ATTRIBUTE9,
420 X_ATTRIBUTE10,
421 X_ATTRIBUTE11,
422 X_ATTRIBUTE12,
423 X_ATTRIBUTE13,
424 X_ATTRIBUTE14,
425 X_ATTRIBUTE15,
426 X_MODE,
427 X_BUSINESS_GROUP_ID, -- Introduced for bug fix 3098050
428 X_SET_OF_BOOKS_ID); -- Introduced for bug fix 3098050
429 end ADD_ROW;
430
431 procedure DELETE_ROW (
432 X_ELEMENT_TYPE_ID in NUMBER,
433 X_START_DATE_ACTIVE in DATE
434 ) is
435 begin
436 delete from PSP_ELEMENT_TYPES
437 where ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID
438 and START_DATE_ACTIVE = X_START_DATE_ACTIVE;
439 if (sql%notfound) then
440 raise no_data_found;
441 end if;
442 end DELETE_ROW;
443
444 end PSP_ELEMENT_TYPES_PKG;