1 package body IGS_PE_PERSON_IMAGE_PKG AS
2 /* $Header: IGSNI17B.pls 115.3 2002/11/29 01:18:23 nsidana ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_PE_PERSON_IMAGE%RowType;
7 new_references IGS_PE_PERSON_IMAGE%RowType;
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_image_dt IN DATE DEFAULT NULL,
13 x_creation_date IN DATE DEFAULT NULL,
14 x_created_by IN NUMBER DEFAULT NULL,
15 x_last_update_date IN DATE DEFAULT NULL,
16 x_last_updated_by IN NUMBER DEFAULT NULL,
17 x_last_update_login IN NUMBER DEFAULT NULL
18 ) AS
19
20 CURSOR cur_old_ref_values IS
21 SELECT *
22 FROM IGS_PE_PERSON_IMAGE
23 WHERE rowid = x_rowid;
24
25 BEGIN
26
27 l_rowid := x_rowid;
28
29 -- Code for setting the Old and New Reference Values.
30 -- Populate Old Values.
31 Open cur_old_ref_values;
32 Fetch cur_old_ref_values INTO old_references;
33 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
34 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35 IGS_GE_MSG_STACK.ADD;
36 Close cur_old_ref_values;
37 App_Exception.Raise_Exception;
38 Return;
39 END IF;
40 Close cur_old_ref_values;
41
42 -- Populate New Values.
43 new_references.person_id := x_person_id;
44 new_references.image_dt := x_image_dt;
45 IF (p_action = 'UPDATE') THEN
46 new_references.creation_date := old_references.creation_date;
47 new_references.created_by := old_references.created_by;
48 ELSE
49 new_references.creation_date := x_creation_date;
50 new_references.created_by := x_created_by;
51 END IF;
52 new_references.last_update_date := x_last_update_date;
53 new_references.last_updated_by := x_last_updated_by;
54 new_references.last_update_login := x_last_update_login;
55
56 END Set_Column_Values;
57
58 PROCEDURE Check_Parent_Existance AS
59 BEGIN
60
61 IF (((old_references.person_id = new_references.person_id)) OR
62 ((new_references.person_id IS NULL))) THEN
63 NULL;
64 ELSE
65
66 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
67 new_references.person_id) THEN
68 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
69
70 IGS_GE_MSG_STACK.ADD;
71 App_Exception.Raise_Exception;
72 END IF;
73 END IF;
74
75 END Check_Parent_Existance;
76
77 FUNCTION Get_PK_For_Validation (
78 x_person_id IN NUMBER,
79 x_image_dt IN DATE
80 ) RETURN BOOLEAN AS
81
82 CURSOR cur_rowid IS
83 SELECT rowid
84 FROM IGS_PE_PERSON_IMAGE
85 WHERE person_id = x_person_id
86 AND image_dt = x_image_dt
87 FOR UPDATE NOWAIT;
88
89 lv_rowid cur_rowid%RowType;
90
91 BEGIN
92
93 Open cur_rowid;
94 Fetch cur_rowid INTO lv_rowid;
95 IF (cur_rowid%FOUND) THEN
96 Close cur_rowid;
97 Return (TRUE);
98 ELSE
99 Close cur_rowid;
100 Return (FALSE);
101 END IF;
102
103 END Get_PK_For_Validation;
104
105 PROCEDURE GET_FK_IGS_PE_PERSON (
106 x_person_id IN NUMBER
107 ) AS
108
109 CURSOR cur_rowid IS
110 SELECT rowid
111 FROM IGS_PE_PERSON_IMAGE
112 WHERE person_id = x_person_id ;
113
114 lv_rowid cur_rowid%RowType;
115
116 BEGIN
117
118 Open cur_rowid;
119 Fetch cur_rowid INTO lv_rowid;
120 IF (cur_rowid%FOUND) THEN
121 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PEI_PE_FK');
122 IGS_GE_MSG_STACK.ADD;
123
124 Close cur_rowid;
125 App_Exception.Raise_Exception;
126 Return;
127 END IF;
128 Close cur_rowid;
129
130 END GET_FK_IGS_PE_PERSON;
131
132 PROCEDURE Before_DML (
133 p_action IN VARCHAR2,
134 x_rowid IN VARCHAR2 DEFAULT NULL,
135 x_person_id IN NUMBER DEFAULT NULL,
136 x_image_dt IN DATE DEFAULT NULL,
137 x_creation_date IN DATE DEFAULT NULL,
138 x_created_by IN NUMBER DEFAULT NULL,
139 x_last_update_date IN DATE DEFAULT NULL,
140 x_last_updated_by IN NUMBER DEFAULT NULL,
141 x_last_update_login IN NUMBER DEFAULT NULL
142 ) AS
143 BEGIN
144
145 Set_Column_Values (
146 p_action,
147 x_rowid,
148 x_person_id,
149 x_image_dt,
150 x_creation_date,
151 x_created_by,
152 x_last_update_date,
153 x_last_updated_by,
154 x_last_update_login
155 );
156
157 IF (p_action = 'INSERT') THEN
158 -- Call all the procedures related to Before Insert.
159
160 IF Get_PK_For_Validation (
161 new_references.person_id ,
162 new_references.image_dt) THEN
163 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
164 IGS_GE_MSG_STACK.ADD;
165 App_Exception.Raise_Exception;
166 END IF;
167
168 Check_Parent_Existance; -- if procedure present
169
170 ELSIF (p_action = 'DELETE') THEN
171 -- Call all the procedures related to Before Delete.
172
173 NULL;
174 ELSIF (p_action = 'VALIDATE_INSERT') THEN
175 IF Get_PK_For_Validation (
176 new_references.person_id ,
177 new_references.image_dt) THEN
178 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182
183 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
184 NULL;
185 ELSIF (p_action = 'VALIDATE_DELETE') THEN
186 NULL;
187 END IF;
188
189 END Before_DML;
190
191 PROCEDURE After_DML (
192 p_action IN VARCHAR2,
193 x_rowid IN VARCHAR2
194 ) AS
195 BEGIN
196
197 l_rowid := x_rowid;
198
199 IF (p_action = 'INSERT') THEN
200 -- Call all the procedures related to After Insert.
201 Null;
202 ELSIF (p_action = 'UPDATE') THEN
203 -- Call all the procedures related to After Update.
204 Null;
205 ELSIF (p_action = 'DELETE') THEN
206 -- Call all the procedures related to After Delete.
207 Null;
208 END IF;
209
210 END After_DML;
211
212
213 procedure INSERT_ROW (
214 X_ROWID in out NOCOPY VARCHAR2,
215 X_PERSON_ID in NUMBER,
216 X_IMAGE_DT in out NOCOPY DATE,
217 X_MODE in VARCHAR2 default 'R'
218 ) AS
219 cursor C is select ROWID from IGS_PE_PERSON_IMAGE
220 where PERSON_ID = X_PERSON_ID
221 and IMAGE_DT = NEW_REFERENCES.IMAGE_DT;
222 X_LAST_UPDATE_DATE DATE;
223 X_LAST_UPDATED_BY NUMBER;
224 X_LAST_UPDATE_LOGIN NUMBER;
225 begin
226 X_LAST_UPDATE_DATE := SYSDATE;
227 if(X_MODE = 'I') then
228 X_LAST_UPDATED_BY := 1;
229 X_LAST_UPDATE_LOGIN := 0;
230 elsif (X_MODE = 'R') then
231 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
232 if X_LAST_UPDATED_BY is NULL then
233 X_LAST_UPDATED_BY := -1;
234 end if;
235 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
236 if X_LAST_UPDATE_LOGIN is NULL then
237 X_LAST_UPDATE_LOGIN := -1;
238 end if;
239 else
240 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
241 IGS_GE_MSG_STACK.ADD;
242 app_exception.raise_exception;
243 end if;
244 Before_DML(
245 p_action=>'INSERT',
246 x_rowid=>X_ROWID,
247 x_image_dt=> NVL(X_IMAGE_DT,SYSDATE),
248 x_person_id=>X_PERSON_ID,
249 x_creation_date=>X_LAST_UPDATE_DATE,
250 x_created_by=>X_LAST_UPDATED_BY,
251 x_last_update_date=>X_LAST_UPDATE_DATE,
252 x_last_updated_by=>X_LAST_UPDATED_BY,
253 x_last_update_login=>X_LAST_UPDATE_LOGIN
254 );
255
256 insert into IGS_PE_PERSON_IMAGE (
257 PERSON_ID,
258 IMAGE_DT,
259 CREATION_DATE,
260 CREATED_BY,
261 LAST_UPDATE_DATE,
262 LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN
264 ) values (
265 NEW_REFERENCES.PERSON_ID,
266 NEW_REFERENCES.IMAGE_DT,
267 X_LAST_UPDATE_DATE,
268 X_LAST_UPDATED_BY,
269 X_LAST_UPDATE_DATE,
270 X_LAST_UPDATED_BY,
271 X_LAST_UPDATE_LOGIN
272 );
273
274 open c;
275 fetch c into X_ROWID;
276 if (c%notfound) then
277 close c;
278 raise no_data_found;
279 end if;
280 close c;
281 After_DML(
282 p_action => 'INSERT',
283 x_rowid => X_ROWID
284 );
285
286 end INSERT_ROW;
287
288 procedure LOCK_ROW (
289 X_ROWID in VARCHAR2,
290 X_PERSON_ID in NUMBER,
291 X_IMAGE_DT in DATE
292 ) AS
293 cursor c1 is select rowid
294 from IGS_PE_PERSON_IMAGE
295 where ROWID = X_ROWID
296 for update nowait;
297 tlinfo c1%rowtype;
298
299 begin
300 open c1;
301 fetch c1 into tlinfo;
302 if (c1%notfound) then
303 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
304
305 close c1;
306 App_Exception.Raise_Exception;
307 return;
308 end if;
309 close c1;
310
311
312 return;
313 end LOCK_ROW;
314
315
316 procedure DELETE_ROW (
317 X_ROWID in VARCHAR2
318 ) AS
319 begin
320 Before_DML(
321 p_action => 'DELETE',
322 x_rowid => X_ROWID
323 );
324 delete from IGS_PE_PERSON_IMAGE
325 where ROWID = X_ROWID;
326 if (sql%notfound) then
327 raise no_data_found;
328 end if;
329 After_DML(
330 p_action => 'DELETE',
331 x_rowid => X_ROWID
332 );
333 end DELETE_ROW;
334
335 end IGS_PE_PERSON_IMAGE_PKG;
[Home] [Help]