1 PACKAGE BODY igs_co_type_jo_pkg AS
2 /* $Header: IGSLI24B.pls 115.2 2002/11/29 01:07:41 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_co_type_jo%ROWTYPE;
6 new_references igs_co_type_jo%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
12 x_s_job_name IN VARCHAR2 DEFAULT NULL,
13 x_output_num IN NUMBER DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20 /*
21 || Created By : [email protected]
22 || Created On : 14-DEC-2000
23 || Purpose : Initialises the Old and New references for the columns of the table.
24 || Known limitations, enhancements or remarks :
25 || Change History :
26 || Who When What
27 || (reverse chronological order - newest change first)
28 */
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM IGS_CO_TYPE_JO
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 OPEN cur_old_ref_values;
42 FETCH cur_old_ref_values INTO old_references;
43 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
44 CLOSE cur_old_ref_values;
45 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
46 igs_ge_msg_stack.add;
47 app_exception.raise_exception;
48 RETURN;
49 END IF;
50 CLOSE cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.correspondence_type := x_correspondence_type;
54 new_references.s_job_name := x_s_job_name;
55 new_references.output_num := x_output_num;
56
57 IF (p_action = 'UPDATE') THEN
58 new_references.creation_date := old_references.creation_date;
59 new_references.created_by := old_references.created_by;
60 ELSE
61 new_references.creation_date := x_creation_date;
62 new_references.created_by := x_created_by;
63 END IF;
64
65 new_references.last_update_date := x_last_update_date;
66 new_references.last_updated_by := x_last_updated_by;
67 new_references.last_update_login := x_last_update_login;
68
69 END set_column_values;
70
71
72 FUNCTION get_pk_for_validation (
73 x_correspondence_type IN VARCHAR2,
74 x_s_job_name IN VARCHAR2,
75 x_output_num IN NUMBER
76 ) RETURN BOOLEAN AS
77 /*
78 || Created By : [email protected]
79 || Created On : 14-DEC-2000
80 || Purpose : Validates the Primary Key of the table.
81 || Known limitations, enhancements or remarks :
82 || Change History :
83 || Who When What
84 || (reverse chronological order - newest change first)
85 */
86 CURSOR cur_rowid IS
87 SELECT rowid
88 FROM igs_co_type_jo
89 WHERE correspondence_type = x_correspondence_type
90 AND s_job_name = x_s_job_name
91 AND output_num = x_output_num
92 FOR UPDATE NOWAIT;
93
94 lv_rowid cur_rowid%RowType;
95
96 BEGIN
97
98 OPEN cur_rowid;
99 FETCH cur_rowid INTO lv_rowid;
100 IF (cur_rowid%FOUND) THEN
101 CLOSE cur_rowid;
102 RETURN(TRUE);
103 ELSE
104 CLOSE cur_rowid;
105 RETURN(FALSE);
106 END IF;
107
108 END get_pk_for_validation;
109
110
111 PROCEDURE before_dml (
112 p_action IN VARCHAR2,
113 x_rowid IN VARCHAR2 DEFAULT NULL,
114 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
115 x_s_job_name IN VARCHAR2 DEFAULT NULL,
116 x_output_num IN NUMBER DEFAULT NULL,
117 x_creation_date IN DATE DEFAULT NULL,
118 x_created_by IN NUMBER DEFAULT NULL,
119 x_last_update_date IN DATE DEFAULT NULL,
120 x_last_updated_by IN NUMBER DEFAULT NULL,
121 x_last_update_login IN NUMBER DEFAULT NULL
122 ) AS
123 /*
124 || Created By : [email protected]
125 || Created On : 14-DEC-2000
126 || Purpose : Initialises the columns, Checks Constraints, Calls the
127 || Trigger Handlers for the table, before any DML operation.
128 || Known limitations, enhancements or remarks :
129 || Change History :
130 || Who When What
131 || (reverse chronological order - newest change first)
132 */
133 BEGIN
134
135 set_column_values (
136 p_action,
137 x_rowid,
138 x_correspondence_type,
139 x_s_job_name,
140 x_output_num,
141 x_creation_date,
142 x_created_by,
143 x_last_update_date,
144 x_last_updated_by,
145 x_last_update_login
146 );
147
148 IF (p_action = 'INSERT') THEN
149 -- Call all the procedures related to Before Insert.
150 IF ( get_pk_for_validation(
151 new_references.correspondence_type,
152 new_references.s_job_name,
153 new_references.output_num
154 )
155 ) THEN
156 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
157 igs_ge_msg_stack.add;
158 app_exception.raise_exception;
159 END IF;
160 ELSIF (p_action = 'VALIDATE_INSERT') THEN
161 -- Call all the procedures related to Before Insert.
162 IF ( get_pk_for_validation (
163 new_references.correspondence_type,
164 new_references.s_job_name,
165 new_references.output_num
166 )
167 ) THEN
168 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
169 igs_ge_msg_stack.add;
170 app_exception.raise_exception;
171 END IF;
172 END IF;
173
174 END before_dml;
175
176
177 PROCEDURE insert_row (
178 x_rowid IN OUT NOCOPY VARCHAR2,
179 x_correspondence_type IN VARCHAR2,
180 x_s_job_name IN VARCHAR2,
181 x_output_num IN NUMBER,
182 x_mode IN VARCHAR2 DEFAULT 'R'
183 ) AS
184 /*
185 || Created By : [email protected]
186 || Created On : 14-DEC-2000
187 || Purpose : Handles the INSERT DML logic for the table.
188 || Known limitations, enhancements or remarks :
189 || Change History :
190 || Who When What
191 || (reverse chronological order - newest change first)
192 */
193 CURSOR c IS
194 SELECT rowid
195 FROM igs_co_type_jo
196 WHERE correspondence_type = x_correspondence_type
197 AND s_job_name = x_s_job_name
198 AND output_num = x_output_num;
199
200 x_last_update_date DATE;
201 x_last_updated_by NUMBER;
202 x_last_update_login NUMBER;
203
204 BEGIN
205
206 x_last_update_date := SYSDATE;
207 IF (x_mode = 'I') THEN
208 x_last_updated_by := 1;
209 x_last_update_login := 0;
210 ELSIF (x_mode = 'R') THEN
211 x_last_updated_by := fnd_global.user_id;
212 IF (x_last_updated_by IS NULL) THEN
213 x_last_updated_by := -1;
214 END IF;
215 x_last_update_login := fnd_global.login_id;
216 IF (x_last_update_login IS NULL) THEN
217 x_last_update_login := -1;
218 END IF;
219 ELSE
220 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
221 igs_ge_msg_stack.add;
222 app_exception.raise_exception;
223 END IF;
224
225 before_dml(
226 p_action => 'INSERT',
227 x_rowid => x_rowid,
228 x_correspondence_type => x_correspondence_type,
229 x_s_job_name => x_s_job_name,
230 x_output_num => x_output_num,
231 x_creation_date => x_last_update_date,
232 x_created_by => x_last_updated_by,
233 x_last_update_date => x_last_update_date,
234 x_last_updated_by => x_last_updated_by,
235 x_last_update_login => x_last_update_login
236 );
237
238 INSERT INTO igs_co_type_jo (
239 correspondence_type,
240 s_job_name,
241 output_num,
242 creation_date,
243 created_by,
244 last_update_date,
245 last_updated_by,
246 last_update_login
247 ) VALUES (
248 new_references.correspondence_type,
249 new_references.s_job_name,
250 new_references.output_num,
251 x_last_update_date,
252 x_last_updated_by,
253 x_last_update_date,
254 x_last_updated_by,
255 x_last_update_login
256 );
257
258 OPEN c;
259 FETCH c INTO x_rowid;
260 IF (c%NOTFOUND) THEN
261 CLOSE c;
262 RAISE NO_DATA_FOUND;
263 END IF;
264 CLOSE c;
265
266 END insert_row;
267
268
269 PROCEDURE lock_row (
270 x_rowid IN VARCHAR2,
271 x_correspondence_type IN VARCHAR2,
272 x_s_job_name IN VARCHAR2,
273 x_output_num IN NUMBER
274 ) AS
275 /*
276 || Created By : [email protected]
277 || Created On : 14-DEC-2000
278 || Purpose : Handles the LOCK mechanism for the table.
279 || Known limitations, enhancements or remarks :
280 || Change History :
281 || Who When What
282 || (reverse chronological order - newest change first)
283 */
284 CURSOR c1 IS
285 SELECT
286 rowid
287 FROM igs_co_type_jo
288 WHERE rowid = x_rowid
289 FOR UPDATE NOWAIT;
290
291 tlinfo c1%ROWTYPE;
292
293 BEGIN
294
295 OPEN c1;
296 FETCH c1 INTO tlinfo;
297 IF (c1%notfound) THEN
298 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
299 igs_ge_msg_stack.add;
300 CLOSE c1;
301 app_exception.raise_exception;
302 RETURN;
303 END IF;
304 CLOSE c1;
305
306
307 RETURN;
308
309 END lock_row;
310
311
312 PROCEDURE delete_row (
313 x_rowid IN VARCHAR2
314 ) AS
315 /*
316 || Created By : [email protected]
317 || Created On : 14-DEC-2000
318 || Purpose : Handles the DELETE DML logic for the table.
319 || Known limitations, enhancements or remarks :
320 || Change History :
321 || Who When What
322 || (reverse chronological order - newest change first)
323 */
324 BEGIN
325
326 before_dml (
327 p_action => 'DELETE',
328 x_rowid => x_rowid
329 );
330
331 DELETE FROM igs_co_type_jo
332 WHERE rowid = x_rowid;
333
334 IF (SQL%NOTFOUND) THEN
335 RAISE NO_DATA_FOUND;
336 END IF;
337
338 END delete_row;
339
340
341 END igs_co_type_jo_pkg;
[Home] [Help]