[Home] [Help]
PACKAGE BODY: APPS.GMS_PERSONNEL_PKG
Source
1 package body GMS_PERSONNEL_PKG as
2 -- $Header: gmsawplb.pls 115.4 2002/11/26 18:49:56 jmuthuku ship $
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_PERSONNEL_ID in NUMBER,
6 X_AWARD_ID in NUMBER,
7 X_PERSON_ID in NUMBER,
8 X_AWARD_ROLE in VARCHAR2,
9 X_START_DATE_ACTIVE in DATE,
10 X_END_DATE_ACTIVE in DATE,
11 X_REQUIRED_FLAG in VARCHAR2,
12 X_MODE in VARCHAR2 default 'R'
13 ) is
14 cursor C is select ROWID from GMS_PERSONNEL
15 where PERSONNEL_ID = X_PERSONNEL_ID;
16 X_LAST_UPDATE_DATE DATE;
17 X_LAST_UPDATED_BY NUMBER;
18 X_LAST_UPDATE_LOGIN NUMBER;
19 begin
20 X_LAST_UPDATE_DATE := SYSDATE;
21 if(X_MODE = 'I') then
22 X_LAST_UPDATED_BY := 1;
23 X_LAST_UPDATE_LOGIN := 0;
24 elsif (X_MODE = 'R') then
25 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
26 if X_LAST_UPDATED_BY is NULL then
27 X_LAST_UPDATED_BY := -1;
28 end if;
29 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
30 if X_LAST_UPDATE_LOGIN is NULL then
31 X_LAST_UPDATE_LOGIN := -1;
32 end if;
33 else
34 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
35 app_exception.raise_exception;
36 end if;
37 insert into GMS_PERSONNEL (
38 AWARD_ID,
39 PERSON_ID,
40 AWARD_ROLE,
41 START_DATE_ACTIVE,
42 END_DATE_ACTIVE,
43 PERSONNEL_ID,
44 CREATION_DATE,
45 CREATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_LOGIN,
49 REQUIRED_FLAG
50 ) values (
51 X_AWARD_ID,
52 X_PERSON_ID,
53 X_AWARD_ROLE,
54 X_START_DATE_ACTIVE,
55 X_END_DATE_ACTIVE,
56 X_PERSONNEL_ID,
57 X_LAST_UPDATE_DATE,
58 X_LAST_UPDATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_LOGIN,
62 X_REQUIRED_FLAG
63 );
64
65 open c;
66 fetch c into X_ROWID;
67 if (c%notfound) then
68 close c;
69 raise no_data_found;
70 end if;
71 close c;
72
73 end INSERT_ROW;
74
75 procedure LOCK_ROW (
76 X_PERSONNEL_ID in NUMBER,
77 X_AWARD_ID in NUMBER,
78 X_PERSON_ID in NUMBER,
79 X_AWARD_ROLE in VARCHAR2,
80 X_START_DATE_ACTIVE in DATE,
81 X_END_DATE_ACTIVE in DATE,
82 X_REQUIRED_FLAG in VARCHAR2
83 ) is
84 cursor c1 is select
85 AWARD_ID,
86 PERSON_ID,
87 AWARD_ROLE,
88 START_DATE_ACTIVE,
89 END_DATE_ACTIVE,
90 REQUIRED_FLAG
91 from GMS_PERSONNEL
92 where PERSONNEL_ID = X_PERSONNEL_ID
93 for update of PERSONNEL_ID nowait;
94 tlinfo c1%rowtype;
95
96 begin
97 open c1;
98 fetch c1 into tlinfo;
99 if (c1%notfound) then
100 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
101 app_exception.raise_exception;
102 close c1;
103 return;
104 end if;
105 close c1;
106
107 if ( (tlinfo.AWARD_ID = X_AWARD_ID)
108 AND (tlinfo.PERSON_ID = X_PERSON_ID)
109 AND (tlinfo.AWARD_ROLE = X_AWARD_ROLE)
110 AND ((tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
111 OR ((tlinfo.START_DATE_ACTIVE is null)
112 AND (X_START_DATE_ACTIVE is null)))
113 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
114 OR ((tlinfo.END_DATE_ACTIVE is null)
115 AND (X_END_DATE_ACTIVE is null)))
116 AND ((tlinfo.REQUIRED_FLAG = X_REQUIRED_FLAG)
117 OR ((tlinfo.REQUIRED_FLAG is null)
118 AND (X_REQUIRED_FLAG is null)))
119 ) then
120 null;
121 else
122 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
123 app_exception.raise_exception;
124 end if;
125 return;
126 end LOCK_ROW;
127
128 procedure UPDATE_ROW (
129 X_PERSONNEL_ID in NUMBER,
130 X_AWARD_ID in NUMBER,
131 X_PERSON_ID in NUMBER,
132 X_AWARD_ROLE in VARCHAR2,
133 X_START_DATE_ACTIVE in DATE,
134 X_END_DATE_ACTIVE in DATE,
135 X_REQUIRED_FLAG in VARCHAR2,
136 X_MODE in VARCHAR2 default 'R'
137 ) is
138 X_LAST_UPDATE_DATE DATE;
139 X_LAST_UPDATED_BY NUMBER;
140 X_LAST_UPDATE_LOGIN NUMBER;
141 begin
142 X_LAST_UPDATE_DATE := SYSDATE;
143 if(X_MODE = 'I') then
144 X_LAST_UPDATED_BY := 1;
145 X_LAST_UPDATE_LOGIN := 0;
146 elsif (X_MODE = 'R') then
147 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
148 if X_LAST_UPDATED_BY is NULL then
149 X_LAST_UPDATED_BY := -1;
150 end if;
151 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
152 if X_LAST_UPDATE_LOGIN is NULL then
153 X_LAST_UPDATE_LOGIN := -1;
154 end if;
155 else
156 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
157 app_exception.raise_exception;
158 end if;
159 update GMS_PERSONNEL set
160 AWARD_ID = X_AWARD_ID,
161 PERSON_ID = X_PERSON_ID,
162 AWARD_ROLE = X_AWARD_ROLE,
163 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
164 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
165 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
166 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
167 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
168 REQUIRED_FLAG = X_REQUIRED_FLAG
169 where PERSONNEL_ID = X_PERSONNEL_ID
170 ;
171 if (sql%notfound) then
172 raise no_data_found;
173 end if;
174 end UPDATE_ROW;
175
176 procedure ADD_ROW (
177 X_ROWID in out NOCOPY VARCHAR2,
178 X_PERSONNEL_ID in NUMBER,
179 X_AWARD_ID in NUMBER,
180 X_PERSON_ID in NUMBER,
181 X_AWARD_ROLE in VARCHAR2,
182 X_START_DATE_ACTIVE in DATE,
183 X_END_DATE_ACTIVE in DATE,
184 X_REQUIRED_FLAG in VARCHAR2,
185 X_MODE in VARCHAR2 default 'R'
186 ) is
187 cursor c1 is select rowid from GMS_PERSONNEL
188 where PERSONNEL_ID = X_PERSONNEL_ID
189 ;
190 dummy c1%rowtype;
191 begin
192 open c1;
193 fetch c1 into dummy;
194 if (c1%notfound) then
195 close c1;
196 INSERT_ROW (
197 X_ROWID,
198 X_PERSONNEL_ID,
199 X_AWARD_ID,
200 X_PERSON_ID,
201 X_AWARD_ROLE,
202 X_START_DATE_ACTIVE,
203 X_END_DATE_ACTIVE,
204 X_REQUIRED_FLAG,
205 X_MODE);
206 return;
207 end if;
208 close c1;
209 UPDATE_ROW (
210 X_PERSONNEL_ID,
211 X_AWARD_ID,
212 X_PERSON_ID,
213 X_AWARD_ROLE,
214 X_START_DATE_ACTIVE,
215 X_END_DATE_ACTIVE,
216 X_REQUIRED_FLAG,
217 X_MODE);
218 end ADD_ROW;
219
220 procedure DELETE_ROW (
221 X_PERSONNEL_ID in NUMBER
222 ) is
223 begin
224 delete from GMS_PERSONNEL
225 where PERSONNEL_ID = X_PERSONNEL_ID;
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229 end DELETE_ROW;
230
231 end GMS_PERSONNEL_PKG;