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_SCHEDULES_PKG

Source


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