[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_FAC_TCFT_TMP_PKG
Source
1 PACKAGE BODY igs_ps_fac_tcft_tmp_pkg AS
2 /* $Header: IGSPI3GB.pls 120.1 2005/06/16 23:20:58 appldev $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_fac_tcft_tmp%ROWTYPE;
6 new_references igs_ps_fac_tcft_tmp%ROWTYPE;
7
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_usec_occur_id1 IN NUMBER DEFAULT NULL,
13 x_usec_occur_id2 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 : smaddali
22 || Created On : 21-JAN-2002
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_ps_fac_tcft_tmp
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.person_id := x_person_id;
54 new_references.usec_occur_id1 := x_usec_occur_id1;
55 new_references.usec_occur_id2 := x_usec_occur_id2;
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 PROCEDURE insert_row (
72 x_rowid IN OUT NOCOPY VARCHAR2,
73 x_person_id IN NUMBER,
74 x_usec_occur_id1 IN NUMBER,
75 x_usec_occur_id2 IN NUMBER,
76 x_mode IN VARCHAR2 DEFAULT 'R'
77 ) AS
78 /*
79 || Created By : smaddali
80 || Created On : 21-JAN-2002
81 || Purpose : Handles the INSERT DML logic for the table.
82 || Known limitations, enhancements or remarks :
83 || Change History :
84 || Who When What
85 || (reverse chronological order - newest change first)
86 */
87
88 CURSOR c IS
89 SELECT rowid
90 FROM igs_ps_fac_tcft_tmp
91 WHERE person_id = new_references.person_id AND
92 usec_occur_id1 = new_references.usec_occur_id1 AND
93 usec_occur_id2 = new_references.usec_occur_id2 ;
94
95 x_last_update_date DATE;
96 x_last_updated_by NUMBER;
97 x_last_update_login NUMBER;
98
99 BEGIN
100
101 x_last_update_date := SYSDATE;
102 IF (x_mode = 'I') THEN
103 x_last_updated_by := 1;
104 x_last_update_login := 0;
105 ELSIF (x_mode = 'R') THEN
106 x_last_updated_by := fnd_global.user_id;
107 IF (x_last_updated_by IS NULL) THEN
108 x_last_updated_by := -1;
109 END IF;
110 x_last_update_login := fnd_global.login_id;
111 IF (x_last_update_login IS NULL) THEN
112 x_last_update_login := -1;
113 END IF;
114 ELSE
115 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 END IF;
119
120 set_column_values (
121 'INSERT',
122 x_rowid,
123 x_person_id,
124 x_usec_occur_id1,
125 x_usec_occur_id2,
126 x_last_update_date,
127 x_last_updated_by,
128 x_last_update_date,
129 x_last_updated_by,
130 x_last_update_login
131 );
132
133 INSERT INTO igs_ps_fac_tcft_tmp (
134 person_id,
135 usec_occur_id1,
136 usec_occur_id2,
137 creation_date,
138 created_by,
139 last_update_date,
140 last_updated_by,
141 last_update_login
142 ) VALUES (
143 new_references.person_id,
144 new_references.usec_occur_id1,
145 new_references.usec_occur_id2,
146 x_last_update_date,
147 x_last_updated_by,
148 x_last_update_date,
149 x_last_updated_by,
150 x_last_update_login
151 );
152
153 OPEN c;
154 FETCH c INTO x_rowid;
155 IF (c%NOTFOUND) THEN
156 CLOSE c;
157 RAISE NO_DATA_FOUND;
158 END IF;
159 CLOSE c;
160
161 END insert_row;
162
163
164 PROCEDURE lock_row (
165 x_rowid IN VARCHAR2,
166 x_person_id IN NUMBER,
167 x_usec_occur_id1 IN NUMBER,
168 x_usec_occur_id2 IN NUMBER
169 ) AS
170 /*
171 || Created By : smaddali
172 || Created On : 21-JAN-2002
173 || Purpose : Handles the LOCK mechanism for the table.
174 || Known limitations, enhancements or remarks :
175 || Change History :
176 || Who When What
177 || (reverse chronological order - newest change first)
178 */
179 CURSOR c1 IS
180 SELECT
181 person_id,
182 usec_occur_id1,
183 usec_occur_id2
184 FROM igs_ps_fac_tcft_tmp
185 WHERE rowid = x_rowid
186 FOR UPDATE NOWAIT;
187
188 tlinfo c1%ROWTYPE;
189
190 BEGIN
191
192 OPEN c1;
193 FETCH c1 INTO tlinfo;
194 IF (c1%notfound) THEN
195 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
196 igs_ge_msg_stack.add;
197 CLOSE c1;
198 app_exception.raise_exception;
199 RETURN;
200 END IF;
201 CLOSE c1;
202
203 IF (
204 (tlinfo.person_id = x_person_id)
205 AND (tlinfo.usec_occur_id1 = x_usec_occur_id1)
206 AND (tlinfo.usec_occur_id2 = x_usec_occur_id2)
207 ) THEN
208 NULL;
209 ELSE
210 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
211 igs_ge_msg_stack.add;
212 app_exception.raise_exception;
213 END IF;
214
215 RETURN;
216
217 END lock_row;
218
219
220 PROCEDURE update_row (
221 x_rowid IN VARCHAR2,
222 x_person_id IN NUMBER,
223 x_usec_occur_id1 IN NUMBER,
224 x_usec_occur_id2 IN NUMBER,
225 x_mode IN VARCHAR2 DEFAULT 'R'
226 ) AS
227 /*
228 || Created By : smaddali
229 || Created On : 21-JAN-2002
230 || Purpose : Handles the UPDATE DML logic for the table.
231 || Known limitations, enhancements or remarks :
232 || Change History :
233 || Who When What
234 || (reverse chronological order - newest change first)
235 */
236 x_last_update_date DATE ;
237 x_last_updated_by NUMBER;
238 x_last_update_login NUMBER;
239
240 BEGIN
241
242 x_last_update_date := SYSDATE;
243 IF (X_MODE = 'I') THEN
244 x_last_updated_by := 1;
245 x_last_update_login := 0;
246 ELSIF (x_mode = 'R') THEN
247 x_last_updated_by := fnd_global.user_id;
248 IF x_last_updated_by IS NULL THEN
249 x_last_updated_by := -1;
250 END IF;
251 x_last_update_login := fnd_global.login_id;
252 IF (x_last_update_login IS NULL) THEN
253 x_last_update_login := -1;
254 END IF;
255 ELSE
256 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
257 igs_ge_msg_stack.add;
258 app_exception.raise_exception;
259 END IF;
260
261 set_column_values (
262 'UPDATE',
263 x_rowid,
264 x_person_id,
265 x_usec_occur_id1,
266 x_usec_occur_id2,
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 UPDATE igs_ps_fac_tcft_tmp
274 SET person_id = new_references.person_id,
275 usec_occur_id1 = new_references.usec_occur_id1,
276 usec_occur_id2 = new_references.usec_occur_id2,
277 last_update_date = x_last_update_date,
278 last_updated_by = x_last_updated_by,
279 last_update_login = x_last_update_login
280 WHERE rowid = x_rowid;
281
282 IF (SQL%NOTFOUND) THEN
283 RAISE NO_DATA_FOUND;
284 END IF;
285
286 END update_row;
287
288
289 PROCEDURE add_row (
290 x_rowid IN OUT NOCOPY VARCHAR2,
291 x_person_id IN NUMBER,
292 x_usec_occur_id1 IN NUMBER,
293 x_usec_occur_id2 IN NUMBER,
294 x_mode IN VARCHAR2 DEFAULT 'R'
295 ) AS
296 /*
297 || Created By : smaddali
298 || Created On : 21-JAN-2002
299 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
300 || Known limitations, enhancements or remarks :
301 || Change History :
302 || Who When What
303 || (reverse chronological order - newest change first)
304 */
305 CURSOR c1 IS
306 SELECT rowid
307 FROM igs_ps_fac_tcft_tmp
308 WHERE person_id = x_person_id AND
309 usec_occur_id1 = x_usec_occur_id1 AND
310 usec_occur_id2 = x_usec_occur_id2 ;
311
312 BEGIN
313
314 OPEN c1;
315 FETCH c1 INTO x_rowid;
316 IF (c1%NOTFOUND) THEN
317 CLOSE c1;
318
319 insert_row (
320 x_rowid,
321 x_person_id,
322 x_usec_occur_id1,
323 x_usec_occur_id2,
324 x_mode
325 );
326 RETURN;
327 END IF;
328 CLOSE c1;
329
330 update_row (
331 x_rowid,
332 x_person_id,
333 x_usec_occur_id1,
334 x_usec_occur_id2,
335 x_mode
336 );
337
338 END add_row;
339
340
341 PROCEDURE delete_row (
342 x_rowid IN VARCHAR2
343 ) AS
344 /*
345 || Created By : smaddali
346 || Created On : 21-JAN-2002
347 || Purpose : Handles the DELETE DML logic for the table.
348 || Known limitations, enhancements or remarks :
349 || Change History :
350 || Who When What
351 || (reverse chronological order - newest change first)
352 */
353 BEGIN
354
355 DELETE FROM igs_ps_fac_tcft_tmp
356 WHERE rowid = x_rowid;
357
358 IF (SQL%NOTFOUND) THEN
359 RAISE NO_DATA_FOUND;
360 END IF;
361
362 END delete_row;
363
364 END igs_ps_fac_tcft_tmp_pkg;