1 PACKAGE BODY igf_ap_isircor_tmp_pkg AS
2 /* $Header: IGFAI74B.pls 120.0 2005/06/02 15:44:07 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_ap_isircor_tmp%ROWTYPE;
6 new_references igf_ap_isircor_tmp%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_ict_id IN VARCHAR2 DEFAULT NULL,
12 x_corr_text IN VARCHAR2 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 || Created By : skoppula
21 || Created On : 09-JUL-2001
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 || masehgal 19-Mar-2002 # 2167635 Added ow_id column
27 || (reverse chronological order - newest change first)
28 */
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM igf_ap_isircor_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.ict_id := x_ict_id;
54 new_references.corr_text := x_corr_text;
55
56 IF (p_action = 'UPDATE') THEN
57 new_references.creation_date := old_references.creation_date;
58 new_references.created_by := old_references.created_by;
59 ELSE
60 new_references.creation_date := x_creation_date;
61 new_references.created_by := x_created_by;
62 END IF;
63
64 new_references.last_update_date := x_last_update_date;
65 new_references.last_updated_by := x_last_updated_by;
66 new_references.last_update_login := x_last_update_login;
67
68 END set_column_values;
69
70 FUNCTION get_pk_for_validation (
71 x_ict_id IN NUMBER
72 ) RETURN BOOLEAN AS
73 /*
74 || Created By : masehgal
75 || Created On : 19-Mar-2002
76 || Purpose : Validates the Primary Key of the table.
77 || Known limitations, enhancements or remarks :
78 || Change History :
79 || Who When What
80 || (reverse chronological order - newest change first)
81 */
82 CURSOR cur_rowid IS
83 SELECT rowid
84 FROM igf_ap_isircor_tmp
85 WHERE ict_id = x_ict_id
86 FOR UPDATE NOWAIT;
87
88 lv_rowid cur_rowid%RowType;
89
90 BEGIN
91
92 OPEN cur_rowid;
93 FETCH cur_rowid INTO lv_rowid;
94
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
106 PROCEDURE before_dml (
107 p_action IN VARCHAR2,
108 x_rowid IN VARCHAR2 DEFAULT NULL,
109 x_ict_id IN VARCHAR2 DEFAULT NULL,
110 x_corr_text IN VARCHAR2 DEFAULT NULL,
111 x_creation_date IN DATE DEFAULT NULL,
112 x_created_by IN NUMBER DEFAULT NULL,
113 x_last_update_date IN DATE DEFAULT NULL,
114 x_last_updated_by IN NUMBER DEFAULT NULL,
115 x_last_update_login IN NUMBER DEFAULT NULL
116 ) AS
117 /*
118 || Created By : skoppula
119 || Created On : 09-JUL-2001
120 || Purpose : Initialises the columns, Checks Constraints, Calls the
121 || Trigger Handlers for the table, before any DML operation.
122 || Known limitations, enhancements or remarks :
123 || Change History :
124 || Who When What
125 || masehgal 19-Mar-2002 # 2167635 Added get_pk_for_validation call , ow_id column
126 || (reverse chronological order - newest change first)
127 */
128 BEGIN
129
130 set_column_values (
131 p_action,
132 x_rowid,
133 x_ict_id,
134 x_corr_text,
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' OR p_action = 'VALIDATE_INSERT') THEN
143 -- Call all the procedures related to Before Insert.
144 IF ( get_pk_for_validation(
145 new_references.ict_id
146 )
147 ) THEN
148 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
149 igs_ge_msg_stack.add;
150 app_exception.raise_exception;
151
152 END IF;
153 END IF;
154 END before_dml;
155
156
157 PROCEDURE insert_row (
158 x_rowid IN OUT NOCOPY VARCHAR2,
159 x_ict_id IN OUT NOCOPY VARCHAR2,
160 x_corr_text IN VARCHAR2,
161 x_mode IN VARCHAR2 DEFAULT 'R'
162 ) AS
163 /*
164 || Created By : skoppula
165 || Created On : 09-JUL-2001
166 || Purpose : Handles the INSERT DML logic for the table.
167 || Known limitations, enhancements or remarks :
168 || Change History :
169 || Who When What
170 || masehgal 19-Mar-2002 # 2167635 Added ow_id column
171 || (reverse chronological order - newest change first)
172 */
173 CURSOR c IS
174 SELECT rowid
175 FROM igf_ap_isircor_tmp
176 WHERE ict_id = x_ict_id;
177
178
179 x_last_update_date DATE;
180 x_last_updated_by NUMBER;
181 x_last_update_login NUMBER;
182
183 BEGIN
184
185 x_last_update_date := SYSDATE;
186 IF (x_mode = 'I') THEN
187 x_last_updated_by := 1;
188 x_last_update_login := 0;
189 ELSIF (x_mode = 'R') THEN
190 x_last_updated_by := fnd_global.user_id;
191 IF (x_last_updated_by IS NULL) THEN
192 x_last_updated_by := -1;
193 END IF;
194 x_last_update_login := fnd_global.login_id;
195 IF (x_last_update_login IS NULL) THEN
196 x_last_update_login := -1;
197 END IF;
198 ELSE
199 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
200 igs_ge_msg_stack.add;
201 app_exception.raise_exception;
202 END IF;
203
204 select igf_ap_isircor_tmp_s.nextval INTO x_ict_id FROM dual;
205
206 before_dml(
207 p_action => 'INSERT',
208 x_rowid => x_rowid,
209 x_ict_id => x_ict_id,
210 x_corr_text => x_corr_text,
211 x_creation_date => x_last_update_date,
212 x_created_by => x_last_updated_by,
213 x_last_update_date => x_last_update_date,
214 x_last_updated_by => x_last_updated_by,
215 x_last_update_login => x_last_update_login
216 );
217
218 INSERT INTO igf_ap_isircor_tmp (
219 ict_id,
220 corr_text,
221 creation_date,
222 created_by,
223 last_update_date,
224 last_updated_by,
225 last_update_login
226 ) VALUES (
227 new_references.ict_id,
228 new_references.corr_text,
229 x_last_update_date,
230 x_last_updated_by,
231 x_last_update_date,
232 x_last_updated_by,
233 x_last_update_login
234 );
235
236 OPEN c;
237 FETCH c INTO x_rowid;
238 IF (c%NOTFOUND) THEN
239 CLOSE c;
240 RAISE NO_DATA_FOUND;
241 END IF;
242 CLOSE c;
243
244 END insert_row;
245
246 PROCEDURE delete_row (
247 x_rowid IN VARCHAR2
248 ) AS
249 /*
250 || Created By : skoppula
251 || Created On : 09-JUL-2001
252 || Purpose : Handles the DELETE DML logic 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 BEGIN
259
260 before_dml (
261 p_action => 'DELETE',
262 x_rowid => x_rowid
263 );
264
265 DELETE FROM igf_ap_isircor_tmp
266 WHERE rowid = x_rowid;
267
268 IF (SQL%NOTFOUND) THEN
269 RAISE NO_DATA_FOUND;
270 END IF;
271
272 END delete_row;
273
274 END igf_ap_isircor_tmp_pkg;
[Home] [Help]