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_GROUPS_PKG

Source


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