eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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,
360   X_ATTRIBUTE15 in VARCHAR2,
361   X_MODE in VARCHAR2 default 'R',
362   X_BUSINESS_GROUP_ID IN NUMBER,	-- Introduced for bug fix 3098050
363   X_SET_OF_BOOKS_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;