[Home] [Help]
PACKAGE BODY: APPS.GR_EIN_ASL_RISKS_PKG
Source
1 PACKAGE BODY GR_EIN_ASL_RISKS_PKG AS
2 /*$Header: GRHIEARB.pls 115.5 2002/10/29 16:37:34 mgrosser noship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_sequence_number IN NUMBER,
7 p_risk_phrase_code IN VARCHAR2,
8 p_created_by IN NUMBER,
9 p_creation_date IN DATE,
10 p_last_updated_by IN NUMBER,
11 p_last_update_date IN DATE,
12 p_last_update_login IN NUMBER,
13 x_rowid OUT NOCOPY VARCHAR2,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_oracle_error OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2)
17 IS
18 /* Declare Alpha Variables */
19
20 L_RETURN_STATUS VARCHAR2(1) := 'S';
21 L_KEY_EXISTS VARCHAR2(1);
22 L_MSG_DATA VARCHAR2(2000);
23 L_ROWID VARCHAR2(18);
24
25 /* Declare Number Variables */
26
27 L_ORACLE_ERROR NUMBER;
28
29 /* Declare Exceptions */
30
31 FOREIGN_KEY_ERROR EXCEPTION;
32 KEY_EXISTS_ERROR EXCEPTION;
33 ROW_MISSING_ERROR EXCEPTION;
34
35
36 BEGIN
37
38 /* Initialization Routine */
39
40 SAVEPOINT Insert_Row;
41 x_return_status := 'S';
42 x_oracle_error := 0;
43 x_msg_data := NULL;
44
45 /* Call the check foreign key procedure
46 This will check that any codes being inserted into this table do indeed
47 exist in the tables that they reference. */
48
49 Check_Foreign_Keys
50 (p_sequence_number,
51 p_risk_phrase_code,
52 l_return_status,
53 l_oracle_error,
54 l_msg_data);
55
56 IF l_return_status <> 'S' THEN
57 RAISE Foreign_Key_Error;
58 END IF;
59
60
61
62 /* Check the primary key doesn't already exist */
63
64 Check_Primary_Key
65 (p_sequence_number,
66 p_risk_phrase_code,
67 'F',
68 l_rowid,
69 l_key_exists);
70
71 IF FND_API.To_Boolean(l_key_exists) THEN
72 RAISE Key_Exists_Error;
73 END IF;
74
75 INSERT INTO gr_ein_asl_risks
76 ( sequence_number,
77 risk_phrase_code,
78 created_by,
79 creation_date,
80 last_updated_by,
81 last_update_date,
82 last_update_login)
83
84 VALUES
85 (p_sequence_number,
86 p_risk_phrase_code,
87 p_created_by,
88 p_creation_date,
89 p_last_updated_by,
90 p_last_update_date,
91 p_last_update_login);
92
93 /* Now get the row id of the inserted record */
94
95 Check_Primary_Key
96 (p_sequence_number,
97 p_risk_phrase_code,
98 'F',
99 l_rowid,
100 l_key_exists);
101
102 IF FND_API.To_Boolean(l_key_exists) THEN
103 x_rowid := l_rowid;
104 ELSE
105 RAISE Row_Missing_Error;
106 END IF;
107
108 /* Check the commit flag and if set, then commit the work. */
109
110 IF FND_API.To_Boolean(p_commit) THEN
111 COMMIT WORK;
112 END IF;
113
114 EXCEPTION
115
116 WHEN Foreign_Key_Error THEN
117 ROLLBACK TO SAVEPOINT Insert_Row;
118 x_return_status := l_return_status;
119 x_oracle_error := l_oracle_error;
120 FND_MESSAGE.SET_NAME('GR',
121 'GR_FOREIGN_KEY_ERROR');
122 FND_MESSAGE.SET_TOKEN('TEXT',
123 l_msg_data,
124 FALSE);
125 IF FND_API.To_Boolean(p_called_by_form) THEN
126 APP_EXCEPTION.Raise_Exception;
127 ELSE
128 x_msg_data := FND_MESSAGE.Get;
129 END IF;
130
131 WHEN Key_Exists_Error THEN
132 ROLLBACK TO SAVEPOINT Insert_Row;
133 x_return_status := 'E';
134 x_oracle_error := APP_EXCEPTION.Get_Code;
135 FND_MESSAGE.SET_NAME('GR',
136 'GR_RECORD_EXISTS');
137 FND_MESSAGE.SET_TOKEN('CODE',
138 p_sequence_number || ',' || p_risk_phrase_code,
139 FALSE);
140 IF FND_API.To_Boolean(p_called_by_form) THEN
141 APP_EXCEPTION.Raise_Exception;
142 ELSE
143 x_msg_data := FND_MESSAGE.Get;
144 END IF;
145
146 WHEN Row_Missing_Error THEN
147 ROLLBACK TO SAVEPOINT Insert_Row;
148 x_return_status := 'E';
149 x_oracle_error := APP_EXCEPTION.Get_Code;
150 FND_MESSAGE.SET_NAME('GR',
151 'GR_NO_RECORD_INSERTED');
152 FND_MESSAGE.SET_TOKEN('CODE',
153 p_sequence_number || ',' || p_risk_phrase_code,
154 FALSE);
155 IF FND_API.To_Boolean(p_called_by_form) THEN
156 APP_EXCEPTION.Raise_Exception;
157 ELSE
158 x_msg_data := FND_MESSAGE.Get;
159 END IF;
160
161 WHEN OTHERS THEN
162 ROLLBACK TO SAVEPOINT Insert_Row;
163 x_return_status := 'U';
164 x_oracle_error := SQLCODE;
165 l_msg_data := SUBSTR(SQLERRM, 1, 200);
166 FND_MESSAGE.SET_NAME('GR',
167 'GR_UNEXPECTED_ERROR');
168 FND_MESSAGE.SET_TOKEN('TEXT',
169 l_msg_data,
170 FALSE);
171 IF FND_API.To_Boolean(p_called_by_form) THEN
172 APP_EXCEPTION.Raise_Exception;
173 ELSE
174 x_msg_data := FND_MESSAGE.Get;
175 END IF;
176
177 END Insert_Row;
178
179
180 PROCEDURE Check_Foreign_Keys
181 (p_sequence_number IN NUMBER,
182 p_risk_phrase_code IN VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_oracle_error OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2)
186 IS
187
188 /* Declare Alpha Variables */
189
190 L_RETURN_STATUS VARCHAR2(1) := 'S';
191 L_MSG_DATA VARCHAR2(2000);
192 L_ROWID VARCHAR2(18);
193 L_KEY_EXISTS VARCHAR2(1);
194
195 /* Declare Number Variables */
196
197 L_ORACLE_ERROR NUMBER;
198
199 /* Define the cursors */
200 /* Risk Phrase cursor */
201
202 CURSOR c_get_risk_phrase
203 IS
204 SELECT rp.risk_phrase_code
205 FROM gr_risk_phrases_b rp
206 WHERE rp.risk_phrase_code = p_risk_phrase_code;
207 RiskPhraseRcd c_get_risk_phrase%ROWTYPE;
208
209
210 BEGIN
211
212 /* Initialization Routine */
213
214 SAVEPOINT Check_Foreign_Keys;
215 x_return_status := 'S';
216 x_oracle_error := 0;
217 x_msg_data := NULL;
218
219 /* Check the risk phrase code */
220
221
222 OPEN c_get_risk_phrase;
223 FETCH c_get_risk_phrase INTO RiskPhraseRcd;
224 IF c_get_risk_phrase%NOTFOUND THEN
225 x_return_status := 'E';
226 FND_MESSAGE.SET_NAME('GR',
227 'GR_RECORD_NOT_FOUND');
228 FND_MESSAGE.SET_TOKEN('CODE',
229 p_risk_phrase_code,
230 FALSE);
231 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
232 END IF;
233 CLOSE c_get_risk_phrase;
234
235
236 IF x_return_status <> 'S' THEN
237 x_msg_data := l_msg_data;
238 END IF;
239
240 EXCEPTION
241
242 WHEN OTHERS THEN
243 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
244 x_return_status := 'U';
245 x_oracle_error := SQLCODE;
246 l_msg_data := SUBSTR(SQLERRM, 1, 200);
247 FND_MESSAGE.SET_NAME('GR',
248 'GR_UNEXPECTED_ERROR');
249 FND_MESSAGE.SET_TOKEN('TEXT',
250 l_msg_data,
251 FALSE);
252 x_msg_data := FND_MESSAGE.Get;
253
254 END Check_Foreign_Keys;
255
256
257
258
259 PROCEDURE Delete_Row
260 (p_commit IN VARCHAR2,
261 p_called_by_form IN VARCHAR2,
262 p_rowid IN VARCHAR2,
263 p_sequence_number IN NUMBER,
264 p_risk_phrase_code IN VARCHAR2,
265 p_created_by IN NUMBER,
266 p_creation_date IN DATE,
267 p_last_updated_by IN NUMBER,
268 p_last_update_date IN DATE,
269 p_last_update_login IN NUMBER,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_oracle_error OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY VARCHAR2)
273 IS
274
275 /* Alpha Variables */
276
277 L_RETURN_STATUS VARCHAR2(1) := 'S';
278 L_MSG_DATA VARCHAR2(2000);
279 L_CALLED_BY_FORM VARCHAR2(1);
280
281 /* Number Variables */
282
283 L_ORACLE_ERROR NUMBER;
284
285 /* Exceptions */
286
287 CHECK_INTEGRITY_ERROR EXCEPTION;
288 ROW_MISSING_ERROR EXCEPTION;
289 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
290
291 BEGIN
292
293 /* Initialization Routine */
294
295 SAVEPOINT Delete_Row;
296 x_return_status := 'S';
297 l_called_by_form := 'F';
298 x_oracle_error := 0;
299 x_msg_data := NULL;
300
301
302
303 DELETE FROM gr_ein_asl_risks
304 WHERE rowid = p_rowid;
305
306 /* Check the commit flag and if set, then commit the work. */
307
308 IF FND_API.TO_Boolean(p_commit) THEN
309 COMMIT WORK;
310 END IF;
311
312 EXCEPTION
313
314 WHEN Check_Integrity_Error THEN
315 ROLLBACK TO SAVEPOINT Delete_Row;
316 x_return_status := l_return_status;
317 x_oracle_error := l_oracle_error;
318 IF FND_API.To_Boolean(p_called_by_form) THEN
319 APP_EXCEPTION.Raise_Exception;
320 ELSE
321 x_msg_data := FND_MESSAGE.Get;
322 END IF;
323
324 WHEN Row_Missing_Error THEN
325 ROLLBACK TO SAVEPOINT Delete_Row;
326 x_return_status := 'E';
327 x_oracle_error := APP_EXCEPTION.Get_Code;
328 FND_MESSAGE.SET_NAME('GR',
329 'GR_RECORD_NOT_FOUND');
330 FND_MESSAGE.SET_TOKEN('CODE',
331 p_sequence_number || ', '
332 || p_risk_phrase_code,
333 FALSE);
334 IF FND_API.To_Boolean(p_called_by_form) THEN
335 APP_EXCEPTION.Raise_Exception;
336 ELSE
337 x_msg_data := FND_MESSAGE.Get;
338 END IF;
339
340 WHEN OTHERS THEN
341 ROLLBACK TO SAVEPOINT Delete_Row;
342 x_return_status := 'U';
343 x_oracle_error := SQLCODE;
344 l_msg_data := SUBSTR(SQLERRM, 1, 200);
345 FND_MESSAGE.SET_NAME('GR',
346 'GR_UNEXPECTED_ERROR');
347 FND_MESSAGE.SET_TOKEN('TEXT',
348 l_msg_data,
349 FALSE);
350 IF FND_API.To_Boolean(p_called_by_form) THEN
351 APP_EXCEPTION.Raise_Exception;
352 ELSE
353 x_msg_data := FND_MESSAGE.Get;
354 END IF;
355
356 END Delete_Row;
357
358
359
360 PROCEDURE Check_Primary_Key
361 /* p_sequence_number and p_risk_phrase_code is the key to check.
362 p_called_by_form is 'T' if called by a form or 'F' if not.
363 x_rowid is the row id of the record if found.
364 x_key_exists is 'T' is the record is found, 'F' if not. */
365
366 (p_sequence_number IN NUMBER,
367 p_risk_phrase_code IN VARCHAR2,
368 p_called_by_form IN VARCHAR2,
369 x_rowid OUT NOCOPY VARCHAR2,
370 x_key_exists OUT NOCOPY VARCHAR2)
371 IS
372 /* Alphanumeric variables */
373
374 L_MSG_DATA VARCHAR2(80);
375
376 /* Declare any variables and the cursor */
377
378
379 CURSOR c_get_einaslrisk_rowid
380 IS
381 SELECT ear.rowid
382 FROM gr_ein_asl_risks ear
383 WHERE ear.sequence_number = p_sequence_number
384 AND ear.risk_phrase_code = p_risk_phrase_code;
385
386 EinAslRiskRecord c_get_einaslrisk_rowid%ROWTYPE;
387
388 BEGIN
389
390 x_key_exists := 'F';
391 l_msg_data := p_sequence_number || ', ' || p_risk_phrase_code;
392 OPEN c_get_einaslrisk_rowid;
393 FETCH c_get_einaslrisk_rowid INTO EinAslRiskRecord;
394 IF c_get_einaslrisk_rowid%FOUND THEN
395 x_key_exists := 'T';
396 x_rowid := EinAslRiskRecord.rowid;
397 ELSE
398 x_key_exists := 'F';
399 END IF;
400 CLOSE c_get_einaslrisk_rowid;
401
402 EXCEPTION
403
404 WHEN Others THEN
405 l_msg_data := SUBSTR(SQLERRM, 1, 200);
406 FND_MESSAGE.SET_NAME('GR',
407 'GR_UNEXPECTED_ERROR');
408 FND_MESSAGE.SET_TOKEN('TEXT',
409 l_msg_data,
410 FALSE);
411 IF FND_API.To_Boolean(p_called_by_form) THEN
412 APP_EXCEPTION.Raise_Exception;
413 END IF;
414
415 END Check_Primary_Key;
416
417
418 END GR_EIN_ASL_RISKS_PKG;