1 PACKAGE BODY igs_en_splace_facs_pkg AS
2 /* $Header: IGSEI75B.pls 120.0 2005/06/01 20:57:18 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_splace_facs%ROWTYPE;
6 new_references igs_en_splace_facs%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_splacement_id IN NUMBER,
12 x_faculty_id IN NUMBER,
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 :
21 || Created On : 21-OCT-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_en_splace_facs
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.splacement_id := x_splacement_id;
53 new_references.faculty_id := x_faculty_id;
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
70 FUNCTION get_pk_for_validation (
71 x_splacement_id IN NUMBER,
72 x_faculty_id IN NUMBER
73 ) RETURN BOOLEAN AS
74 /*
75 || Created By :
76 || Created On : 21-OCT-2003
77 || Purpose : Validates the Primary Key of the table.
78 || Known limitations, enhancements or remarks :
79 || Change History :
80 || Who When What
81 || (reverse chronological order - newest change first)
82 */
83 CURSOR cur_rowid IS
84 SELECT rowid
85 FROM igs_en_splace_facs
86 WHERE splacement_id = x_splacement_id
87 AND faculty_id = x_faculty_id
88 FOR UPDATE NOWAIT;
89
90 lv_rowid cur_rowid%RowType;
91
92 BEGIN
93
94 OPEN cur_rowid;
95 FETCH cur_rowid INTO lv_rowid;
96 IF (cur_rowid%FOUND) THEN
97 CLOSE cur_rowid;
98 RETURN(TRUE);
99 ELSE
100 CLOSE cur_rowid;
101 RETURN(FALSE);
102 END IF;
103
104 END get_pk_for_validation;
105
106
107 PROCEDURE before_dml (
108 p_action IN VARCHAR2,
109 x_rowid IN VARCHAR2,
110 x_splacement_id IN NUMBER,
111 x_faculty_id IN NUMBER,
112 x_creation_date IN DATE,
113 x_created_by IN NUMBER,
114 x_last_update_date IN DATE,
115 x_last_updated_by IN NUMBER,
116 x_last_update_login IN NUMBER
117 ) AS
118 /*
119 || Created By :
120 || Created On : 21-OCT-2003
121 || Purpose : Initialises the columns, Checks Constraints, Calls the
122 || Trigger Handlers for the table, before any DML operation.
123 || Known limitations, enhancements or remarks :
124 || Change History :
125 || Who When What
126 || (reverse chronological order - newest change first)
127 */
128 BEGIN
129
130 set_column_values (
131 p_action,
132 x_rowid,
133 x_splacement_id,
134 x_faculty_id,
135 x_creation_date,
136 x_created_by,
137 x_last_update_date,
138 x_last_updated_by,
139 x_last_update_login
140 );
141
142 IF (p_action = 'INSERT') THEN
143 -- Call all the procedures related to Before Insert.
144 IF ( get_pk_for_validation(
145 new_references.splacement_id,
146 new_references.faculty_id
147 )
148 ) THEN
149 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
150 igs_ge_msg_stack.add;
151 app_exception.raise_exception;
152 END IF;
153 ELSIF (p_action = 'VALIDATE_INSERT') THEN
154 -- Call all the procedures related to Before Insert.
155 IF ( get_pk_for_validation (
156 new_references.splacement_id,
157 new_references.faculty_id
158 )
159 ) THEN
160 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
161 igs_ge_msg_stack.add;
162 app_exception.raise_exception;
163 END IF;
164 END IF;
165
166 END before_dml;
167
168
169 PROCEDURE insert_row (
170 x_rowid IN OUT NOCOPY VARCHAR2,
171 x_splacement_id IN NUMBER,
172 x_faculty_id IN NUMBER,
173 x_mode IN VARCHAR2
174 ) AS
175 /*
176 || Created By :
177 || Created On : 21-OCT-2003
178 || Purpose : Handles the INSERT DML logic for the table.
179 || Known limitations, enhancements or remarks :
180 || Change History :
181 || Who When What
182 || (reverse chronological order - newest change first)
183 */
184
185 x_last_update_date DATE;
186 x_last_updated_by NUMBER;
187 x_last_update_login NUMBER;
188
189 BEGIN
190
191 x_last_update_date := SYSDATE;
192 IF (x_mode = 'I') THEN
193 x_last_updated_by := 1;
194 x_last_update_login := 0;
195 ELSIF (x_mode = 'R') THEN
196 x_last_updated_by := fnd_global.user_id;
197 IF (x_last_updated_by IS NULL) THEN
198 x_last_updated_by := -1;
199 END IF;
200 x_last_update_login := fnd_global.login_id;
201 IF (x_last_update_login IS NULL) THEN
202 x_last_update_login := -1;
203 END IF;
204 ELSE
205 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
206 fnd_message.set_token ('ROUTINE', 'IGS_EN_SPLACE_FACS_PKG.INSERT_ROW');
207 igs_ge_msg_stack.add;
208 app_exception.raise_exception;
209 END IF;
210
211 before_dml(
212 p_action => 'INSERT',
213 x_rowid => x_rowid,
214 x_splacement_id => x_splacement_id,
215 x_faculty_id => x_faculty_id,
216 x_creation_date => x_last_update_date,
217 x_created_by => x_last_updated_by,
218 x_last_update_date => x_last_update_date,
219 x_last_updated_by => x_last_updated_by,
220 x_last_update_login => x_last_update_login
221 );
222
223 INSERT INTO igs_en_splace_facs (
224 splacement_id,
225 faculty_id,
226 creation_date,
227 created_by,
228 last_update_date,
229 last_updated_by,
230 last_update_login
231 ) VALUES (
232 new_references.splacement_id,
233 new_references.faculty_id,
234 x_last_update_date,
235 x_last_updated_by,
236 x_last_update_date,
237 x_last_updated_by,
238 x_last_update_login
239 ) RETURNING ROWID INTO x_rowid;
240
241 END insert_row;
242
243
244 PROCEDURE lock_row (
245 x_rowid IN VARCHAR2,
246 x_splacement_id IN NUMBER,
247 x_faculty_id IN NUMBER
248 ) AS
249 /*
250 || Created By :
251 || Created On : 21-OCT-2003
252 || Purpose : Handles the LOCK mechanism for the table.
253 || Known limitations, enhancements or remarks :
254 || Change History :
255 || Who When What
256 || (reverse chronological order - newest change first)
257 */
258 CURSOR c1 IS
259 SELECT
260 rowid
261 FROM igs_en_splace_facs
262 WHERE rowid = x_rowid
263 FOR UPDATE NOWAIT;
264
265 tlinfo c1%ROWTYPE;
266
267 BEGIN
268
269 OPEN c1;
270 FETCH c1 INTO tlinfo;
271 IF (c1%notfound) THEN
272 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
273 igs_ge_msg_stack.add;
274 CLOSE c1;
275 app_exception.raise_exception;
276 RETURN;
277 END IF;
278 CLOSE c1;
279
280
281 RETURN;
282
283 END lock_row;
284
285
286 PROCEDURE delete_row (
287 x_rowid IN VARCHAR2
288 ) AS
289 /*
290 || Created By :
291 || Created On : 21-OCT-2003
292 || Purpose : Handles the DELETE DML logic for the table.
293 || Known limitations, enhancements or remarks :
294 || Change History :
295 || Who When What
296 || (reverse chronological order - newest change first)
297 */
298 BEGIN
299
300 before_dml (
301 p_action => 'DELETE',
302 x_rowid => x_rowid
303 );
304
305 DELETE FROM igs_en_splace_facs
306 WHERE rowid = x_rowid;
307
308 IF (SQL%NOTFOUND) THEN
309 RAISE NO_DATA_FOUND;
310 END IF;
311
312 END delete_row;
313
314
315 END igs_en_splace_facs_pkg;
[Home] [Help]