1 PACKAGE BODY igs_pe_race_pkg AS
2 /* $Header: IGSNIB0B.pls 120.1 2005/10/17 02:22:31 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_race%ROWTYPE;
6 new_references igs_pe_race%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_person_id IN NUMBER,
12 x_race_cd IN VARCHAR2,
13 x_creation_date IN DATE,
14 x_created_by IN NUMBER,
15 x_last_update_date IN DATE,
16 x_last_updated_by IN NUMBER,
17 x_last_update_login IN NUMBER
18 ) AS
19 /*
20 || Created By : prabhat.patel
21 || Created On : 05-FEB-2003
22 || Purpose : Initialises the Old and New references for the columns of the table.
23 || Known limitations, enhancements or remarks :
24 || Change History :
25 || Who When What
26 || (reverse chronological order - newest change first)
27 */
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM igs_pe_race
32 WHERE rowid = x_rowid;
33
34 BEGIN
35
36 l_rowid := x_rowid;
37
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 OPEN cur_old_ref_values;
41 FETCH cur_old_ref_values INTO old_references;
42 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
43 CLOSE cur_old_ref_values;
44 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
45 igs_ge_msg_stack.add;
46 app_exception.raise_exception;
47 RETURN;
48 END IF;
49 CLOSE cur_old_ref_values;
50
51 -- Populate New Values.
52 new_references.person_id := x_person_id;
53 new_references.race_cd := x_race_cd;
54
55 IF (p_action = 'UPDATE') THEN
56 new_references.creation_date := old_references.creation_date;
57 new_references.created_by := old_references.created_by;
58 ELSE
59 new_references.creation_date := x_creation_date;
60 new_references.created_by := x_created_by;
61 END IF;
62
63 new_references.last_update_date := x_last_update_date;
64 new_references.last_updated_by := x_last_updated_by;
65 new_references.last_update_login := x_last_update_login;
66
67 END set_column_values;
68
69 PROCEDURE check_parent_existance AS
70 /*
71 || Created By : Prabhat.Patel
72 || Created On : 05-FEB-2003
73 || Purpose : Checks for the existance of Parent records.
74 || Known limitations, enhancements or remarks :
75 || Change History :
76 || Who When What
77 || (reverse chronological order - newest change first)
78 */
79 BEGIN
80
81 IF (((old_references.race_cd = new_references.race_cd)) OR
82 ((new_references.race_cd IS NULL))) THEN
83 NULL;
84 ELSE
85 IF NOT IGS_LOOKUPS_VIEW_PKG.get_pk_for_validation('PE_RACE',new_references.race_cd) THEN
86 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
87 IGS_GE_MSG_STACK.ADD;
88 APP_EXCEPTION.RAISE_EXCEPTION;
89 END IF;
90 END IF;
91
92 IF (((old_references.person_id = new_references.person_id)) OR
93 ((new_references.person_id IS NULL))) THEN
94 NULL;
95 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
96 new_references.person_id
97 ) THEN
98 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
99 IGS_GE_MSG_STACK.ADD;
100 App_Exception.Raise_Exception;
101 END IF;
102
103 END check_parent_existance;
104
105 FUNCTION get_pk_for_validation (
106 x_person_id IN NUMBER,
107 x_race_cd IN VARCHAR2
108 ) RETURN BOOLEAN AS
109 /*
110 || Created By : prabhat.patel
111 || Created On : 05-FEB-2003
112 || Purpose : Validates the Primary Key of the table.
113 || Known limitations, enhancements or remarks :
114 || Change History :
115 || Who When What
116 || (reverse chronological order - newest change first)
117 */
118 CURSOR cur_rowid IS
119 SELECT rowid
120 FROM igs_pe_race
121 WHERE person_id = x_person_id
122 AND race_cd = x_race_cd
123 FOR UPDATE NOWAIT;
124
125 lv_rowid cur_rowid%RowType;
126
127 BEGIN
128
129 OPEN cur_rowid;
130 FETCH cur_rowid INTO lv_rowid;
131 IF (cur_rowid%FOUND) THEN
132 CLOSE cur_rowid;
133 RETURN(TRUE);
134 ELSE
135 CLOSE cur_rowid;
136 RETURN(FALSE);
137 END IF;
138
139 END get_pk_for_validation;
140
141
142 PROCEDURE before_dml (
143 p_action IN VARCHAR2,
144 x_rowid IN VARCHAR2,
145 x_person_id IN NUMBER,
146 x_race_cd IN VARCHAR2,
147 x_creation_date IN DATE,
148 x_created_by IN NUMBER,
149 x_last_update_date IN DATE,
150 x_last_updated_by IN NUMBER,
151 x_last_update_login IN NUMBER
152 ) AS
153 /*
154 || Created By : prabhat.patel
155 || Created On : 05-FEB-2003
156 || Purpose : Initialises the columns, Checks Constraints, Calls the
157 || Trigger Handlers for the table, before any DML operation.
158 || Known limitations, enhancements or remarks :
159 || Change History :
160 || Who When What
161 || (reverse chronological order - newest change first)
162 */
163 BEGIN
164
165 set_column_values (
166 p_action,
167 x_rowid,
168 x_person_id,
169 x_race_cd,
170 x_creation_date,
171 x_created_by,
172 x_last_update_date,
173 x_last_updated_by,
174 x_last_update_login
175 );
176
177 IF (p_action = 'INSERT') THEN
178 -- Call all the procedures related to Before Insert.
179 IF ( get_pk_for_validation(
180 new_references.person_id,
181 new_references.race_cd
182 )
183 ) THEN
184 fnd_message.set_name('IGS','IGS_PE_RACE_DUP_EXISTS');
185 igs_ge_msg_stack.add;
186 app_exception.raise_exception;
187 END IF;
188
189 check_parent_existance;
190
191 ELSIF (p_action = 'VALIDATE_INSERT') THEN
192 -- Call all the procedures related to Before Insert.
193 IF ( get_pk_for_validation (
194 new_references.person_id,
195 new_references.race_cd
196 )
197 ) THEN
198 fnd_message.set_name('IGS','IGS_PE_RACE_DUP_EXISTS');
199 igs_ge_msg_stack.add;
200 app_exception.raise_exception;
201 END IF;
202 END IF;
203
204 check_parent_existance;
205
206 END before_dml;
207
208
209 PROCEDURE insert_row (
210 x_rowid IN OUT NOCOPY VARCHAR2,
211 x_person_id IN NUMBER,
212 x_race_cd IN VARCHAR2,
213 x_mode IN VARCHAR2
214 ) AS
215 /*
216 || Created By : prabhat.patel
217 || Created On : 05-FEB-2003
218 || Purpose : Handles the INSERT DML logic for the table.
219 || Known limitations, enhancements or remarks :
220 || Change History :
221 || Who When What
222 || (reverse chronological order - newest change first)
223 */
224
225 x_last_update_date DATE;
226 x_last_updated_by NUMBER;
227 x_last_update_login NUMBER;
228
229 BEGIN
230
231 x_last_update_date := SYSDATE;
232 IF (x_mode = 'I') THEN
233 x_last_updated_by := 1;
234 x_last_update_login := 0;
235 ELSIF (X_MODE IN ('R', 'S')) THEN
236 x_last_updated_by := fnd_global.user_id;
237 IF (x_last_updated_by IS NULL) THEN
238 x_last_updated_by := -1;
239 END IF;
240 x_last_update_login := fnd_global.login_id;
241 IF (x_last_update_login IS NULL) THEN
242 x_last_update_login := -1;
243 END IF;
244 ELSE
245 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
246 fnd_message.set_token ('ROUTINE', 'IGS_PE_RACE_PKG.INSERT_ROW');
247 igs_ge_msg_stack.add;
248 app_exception.raise_exception;
249 END IF;
250
251 before_dml(
252 p_action => 'INSERT',
253 x_rowid => x_rowid,
254 x_person_id => x_person_id,
255 x_race_cd => x_race_cd,
256 x_creation_date => x_last_update_date,
257 x_created_by => x_last_updated_by,
258 x_last_update_date => x_last_update_date,
259 x_last_updated_by => x_last_updated_by,
260 x_last_update_login => x_last_update_login
261 );
262
263 IF (x_mode = 'S') THEN
264 igs_sc_gen_001.set_ctx('R');
265 END IF;
266 INSERT INTO igs_pe_race (
267 person_id,
268 race_cd,
269 creation_date,
270 created_by,
271 last_update_date,
272 last_updated_by,
273 last_update_login
274 ) VALUES (
275 new_references.person_id,
276 new_references.race_cd,
277 x_last_update_date,
278 x_last_updated_by,
279 x_last_update_date,
280 x_last_updated_by,
281 x_last_update_login
282 ) RETURNING ROWID INTO x_rowid;
283 IF (x_mode = 'S') THEN
284 igs_sc_gen_001.unset_ctx('R');
285 END IF;
286
287
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
292 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
293 fnd_message.set_token ('ERR_CD', SQLCODE);
294 igs_ge_msg_stack.add;
295 igs_sc_gen_001.unset_ctx('R');
296 app_exception.raise_exception;
297 ELSE
298 igs_sc_gen_001.unset_ctx('R');
299 RAISE;
300 END IF;
301 END insert_row;
302
303
304 PROCEDURE lock_row (
305 x_rowid IN VARCHAR2,
306 x_person_id IN NUMBER,
307 x_race_cd IN VARCHAR2
308 ) AS
309 /*
310 || Created By : prabhat.patel
311 || Created On : 05-FEB-2003
312 || Purpose : Handles the LOCK mechanism for the table.
313 || Known limitations, enhancements or remarks :
314 || Change History :
315 || Who When What
316 || (reverse chronological order - newest change first)
317 */
318 CURSOR c1 IS
319 SELECT
320 rowid
321 FROM igs_pe_race
322 WHERE rowid = x_rowid
323 FOR UPDATE NOWAIT;
324
325 tlinfo c1%ROWTYPE;
326
327 BEGIN
328
329 OPEN c1;
330 FETCH c1 INTO tlinfo;
331 IF (c1%notfound) THEN
332 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
333 igs_ge_msg_stack.add;
334 CLOSE c1;
335 app_exception.raise_exception;
336 RETURN;
337 END IF;
338 CLOSE c1;
339
340
341 RETURN;
342
343 END lock_row;
344
345
346 PROCEDURE delete_row (
347 x_rowid IN VARCHAR2,
348 x_mode IN VARCHAR2
349 ) AS
350 /*
351 || Created By : prabhat.patel
352 || Created On : 05-FEB-2003
353 || Purpose : Handles the DELETE DML logic for the table.
354 || Known limitations, enhancements or remarks :
355 || Change History :
356 || Who When What
357 || (reverse chronological order - newest change first)
358 */
359 BEGIN
360
361 before_dml (
362 p_action => 'DELETE',
363 x_rowid => x_rowid
364 );
365
366 IF (x_mode = 'S') THEN
367 igs_sc_gen_001.set_ctx('R');
368 END IF;
369 DELETE FROM igs_pe_race
370 WHERE rowid = x_rowid;
371
372 IF (SQL%NOTFOUND) THEN
373 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
374 igs_ge_msg_stack.add;
375 igs_sc_gen_001.unset_ctx('R');
376 app_exception.raise_exception;
377 END IF;
378 IF (x_mode = 'S') THEN
379 igs_sc_gen_001.unset_ctx('R');
380 END IF;
381
382
383 END delete_row;
384
385
386 END igs_pe_race_pkg;
[Home] [Help]