1 PACKAGE BODY OKS_RENEW_CONTRACT_PUB AS
2 /* $Header: OKSPRENKB.pls 120.1 2005/09/27 14:31 anjkumar noship $*/
3
4 /*
5 From R12 onwards, this procedure should be used to renew service contracts.
6 It will be redesigned to do the following
7 1. Improve performance
8 2. Reduce dependence on OKC code
9 3. Incorporate functional design changes for R12
10 4. Comply with current Oracle Applications coding and logging standards
11 5. Ease of maintenance
12
13 Parameters
14 p_chr_id : id of the contract being renewed, mandatory
15 p_new_contract_number : contract number for the renewed contract, optional
16 p_new_contract_modifier : contract modifier for the renewed contract, optional
17 p_new_start_date : start date for the renewed contract, optional
18 p_new_end_date : end date for the renewed contract, optional
19 p_new_duration : duration for renewed contract, optional
20 p_new_uom_code : period for the renewed contract, optional
21 p_renewal_called_from_ui : 'Y' - called from UI, N - called from Events
22 x_chr_id : id of the renewed contract
23
24 Defaulting rules
25 1. If p_new_contract_number is not passed, uses the source contract_number
26 2. If p_new_contract_modifier is not passed, generated this as
27 fnd_profile.VALUE('OKC_CONTRACT_IDENTIFIER') || to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
28 3. If p_new_start_date is not passed, defaults to source contract end_date +1
29 4. If p_new_end_date is not passed, derived from p_new_duration/p_new_uom_code
30 and p_new_start_date. If p_new_duration/p_new_uom_code are also not passed
31 used the source contract duration/period
32 */
33
34 PROCEDURE RENEW_CONTRACT
35 (
36 p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
38 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
39 p_chr_id IN NUMBER,
40 p_new_contract_number IN okc_k_headers_b.contract_number%TYPE,
41 p_new_contract_modifier IN okc_k_headers_b.contract_number_modifier%TYPE,
42 p_new_start_date IN DATE,
43 p_new_end_date IN DATE,
44 p_new_duration IN NUMBER,
45 p_new_uom_code IN MTL_UNITS_OF_MEASURE_TL.uom_code%TYPE,
46 p_renewal_called_from_ui IN VARCHAR2 DEFAULT 'Y',
47 x_chr_id OUT NOCOPY NUMBER,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2,
50 x_return_status OUT NOCOPY VARCHAR2
51 )
52 IS
53 l_api_name CONSTANT VARCHAR2(30) := 'RENEW_CONTRACT';
54 l_api_version CONSTANT NUMBER := 1;
55 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
56 l_error_text VARCHAR2(512);
57 BEGIN
58
59 --log key input parameters
60 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
61 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_chr_id=' || p_chr_id||' ,p_new_contract_number='||p_new_contract_number||' ,p_new_contract_modifier='||p_new_contract_modifier||
62 ' ,p_new_start_date='||p_new_start_date||' ,p_new_end_date='||p_new_end_date||' ,p_new_duration='||p_new_duration||' ,p_new_uom_code='||p_new_uom_code||' ,p_renewal_called_from_ui='||p_renewal_called_from_ui);
63 END IF;
64
65 --standard api initilization and checks
66 SAVEPOINT renew_contract_PUB;
67 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70 IF FND_API.to_boolean(p_init_msg_list ) THEN
71 FND_MSG_PUB.initialize;
72 END IF;
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74
75 OKS_RENEW_CONTRACT_PVT.renew_contract(
76 p_api_version => 1,
77 p_init_msg_list => FND_API.G_FALSE,
78 p_commit => FND_API.G_FALSE,
79 p_chr_id => p_chr_id,
80 p_new_contract_number => p_new_contract_number,
81 p_new_contract_modifier => p_new_contract_modifier,
82 p_new_start_date => p_new_start_date,
83 p_new_end_date => p_new_end_date,
84 p_new_duration => p_new_duration,
85 p_new_uom_code => p_new_uom_code,
86 p_renewal_called_from_ui => p_renewal_called_from_ui,
87 x_chr_id => x_chr_id,
88 x_msg_count => x_msg_count,
89 x_msg_data => x_msg_data,
90 x_return_status => x_return_status);
91
92 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
93 RAISE FND_API.g_exc_unexpected_error;
94 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
95 RAISE FND_API.g_exc_error;
96 END IF;
97
98 --standard check of p_commit
99 IF FND_API.to_boolean( p_commit ) THEN
100 COMMIT;
101 END IF;
102
103 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
104 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', 'x_return_status='|| x_return_status);
105 END IF;
106 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
107
108 EXCEPTION
109 WHEN FND_API.g_exc_error THEN
110 ROLLBACK TO renew_contract_PUB;
111 x_return_status := FND_API.g_ret_sts_error ;
112
113 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
114 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
115 END IF;
116 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
117
118 WHEN FND_API.g_exc_unexpected_error THEN
119 ROLLBACK TO renew_contract_PUB;
120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
121
122 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
123 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
124 END IF;
125 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
126
127 WHEN OTHERS THEN
128 ROLLBACK TO renew_contract_PUB;
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
130
131 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
132 --first log the sqlerrm
133 l_error_text := substr (SQLERRM, 1, 240);
134 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
135 --then add it to the message api list
136 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
137 END IF;
138 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
139
140 END RENEW_CONTRACT;
141
142
143 /* R12 procedure that validates if a contract can be renewed
144 p_chr_id : contract id of the contract being renewed
145 p_date : start date of the renewal, if not passed defaults to end date + 1 of the source contract
146 p_validation_level : A - do all checks including warnings, E - do only error checks
147 x_rnrl_rec : returns the effective renewal rules for the contract
148 x_validation_status : S - Success (OK for renewal), W - Warnings (Ok for renewal)
149 E - Erros (Cannot be renewed)
150 x_validation_tbl : Validation error and warning messages
151 */
152 PROCEDURE VALIDATE_RENEWAL
153 (
154 p_api_version IN NUMBER DEFAULT 1,
155 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2,
159 p_chr_id IN NUMBER,
160 p_date IN DATE,
161 p_validation_level IN VARCHAR2 DEFAULT G_VALIDATE_ALL,
162 x_rnrl_rec OUT NOCOPY OKS_RENEW_UTIL_PVT.rnrl_rec_type,
163 x_validation_status OUT NOCOPY VARCHAR2,
164 x_validation_tbl OUT NOCOPY validation_tbl_type
165 )
166 IS
167 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RENEWAL';
168 l_api_version CONSTANT NUMBER := 1;
169 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
170 l_error_text VARCHAR2(512);
171 BEGIN
172
173 --log key input parameters
174 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
175 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_chr_id=' || p_chr_id||' ,p_date='||p_date||' ,p_validation_level='||p_validation_level);
176 END IF;
177
178 --standard api initilization and checks
179 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END IF;
182 IF FND_API.to_boolean(p_init_msg_list ) THEN
183 FND_MSG_PUB.initialize;
184 END IF;
185 x_return_status := FND_API.G_RET_STS_SUCCESS;
186
187 OKS_RENEW_CONTRACT_PVT.validate_renewal(
188 p_api_version => 1,
189 p_init_msg_list => FND_API.G_FALSE,
190 x_return_status => x_return_status,
191 x_msg_count => x_msg_count,
192 x_msg_data => x_msg_data,
193 p_chr_id => p_chr_id,
194 p_date => p_date,
195 p_validation_level => p_validation_level,
196 x_rnrl_rec => x_rnrl_rec,
197 x_validation_status => x_validation_status,
198 x_validation_tbl => x_validation_tbl);
199
200 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
201 RAISE FND_API.g_exc_unexpected_error;
202 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
203 RAISE FND_API.g_exc_error;
204 END IF;
205
206
207 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
208 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', 'x_return_status='|| x_return_status);
209 END IF;
210 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
211
212 EXCEPTION
213 WHEN FND_API.g_exc_error THEN
214 x_return_status := FND_API.g_ret_sts_error ;
215
216 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
217 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
218 END IF;
219 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
220
221 WHEN FND_API.g_exc_unexpected_error THEN
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
223
224 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
225 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
226 END IF;
227 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
228
229 WHEN OTHERS THEN
230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
231
232 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
233 --first log the sqlerrm
234 l_error_text := substr (SQLERRM, 1, 240);
235 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
236 --then add it to the message api list
237 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
238 END IF;
239 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
240
241 END VALIDATE_RENEWAL;
242
243 /*
244 Utility function that returns
245 FND_API.G_TRUE = T, if a contract is valid for renewal,
246 FND_API.G_FALSE = F, if contract cannot be renewed because of warnings (for e.g., all the
247 lines in the contract have terminated) or errors (for e.g., the contract is in ENTERED status)
248 In case of other errors, logs the error message and returns F.
249
250 This function should be used when setting up independent conditions (events) for contract
251 renewal. It will filter out contracts that are not eligible for renewal. Internally calls the
252 validate_renewal procedure and returns T only if x_validation_status = S, returns F otherwise.
253 */
254 FUNCTION VALID_FOR_RENEWAL
255 (
256 p_chr_id IN NUMBER
257 ) RETURN VARCHAR2
258 IS
259 l_api_name CONSTANT VARCHAR2(30) := 'VALID_FOR_RENEWAL';
260 l_api_version CONSTANT NUMBER := 1;
261 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
262 l_error_text VARCHAR2(512);
263
264 l_return_status VARCHAR2(1);
265 l_msg_count NUMBER;
266 l_msg_data VARCHAR2(4000);
267 l_rnrl_rec OKS_RENEW_UTIL_PVT.rnrl_rec_type;
268 l_validation_status VARCHAR2(1);
269 l_validation_tbl validation_tbl_type;
270
271 l_return_value VARCHAR2(1);
272
273 BEGIN
274
275 --log key input parameters
276 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
280 l_return_status := FND_API.G_RET_STS_SUCCESS;
277 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_chr_id=' || p_chr_id);
278 END IF;
279
281 OKS_RENEW_CONTRACT_PVT.validate_renewal(p_api_version => 1,
282 p_init_msg_list => FND_API.G_FALSE,
283 x_return_status => l_return_status,
284 x_msg_count => l_msg_count,
285 x_msg_data => l_msg_data,
286 p_chr_id => p_chr_id,
287 p_date => null,
288 p_validation_level => null,
289 x_rnrl_rec => l_rnrl_rec,
290 x_validation_status => l_validation_status,
291 x_validation_tbl => l_validation_tbl);
292
293 IF( (l_return_status = FND_API.g_ret_sts_success) AND
294 (l_validation_status = G_VALID_STS_SUCCESS) ) THEN
295 l_return_value := FND_API.G_TRUE; --T
296 ELSE
297 --all other conditions return false
298 l_return_value := FND_API.G_FALSE; --F
299 END IF;
300
301 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
302 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', 'l_return_status='||l_return_status||' ,l_validation_status='||l_validation_status||' ,l_return_value='||l_return_value);
303 END IF;
304
305 RETURN l_return_value;
306
307 EXCEPTION
308
309 WHEN OTHERS THEN
310 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
311 --first log the sqlerrm
312 l_error_text := substr (SQLERRM, 1, 240);
313 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
314 --then add it to the message api list
315 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
316 END IF;
317
318 l_return_value := FND_API.G_FALSE; --F
319 RETURN l_return_value;
320
321 END VALID_FOR_RENEWAL;
322
323
324 /*
325 Procedure for updating invoice_text col in table OKC_K_LINES_TL
326 with the current line start date and end date. Called during renewal,
327 after line dates are adjusted. Uses bulk calls to get and set the invoice text
328 Parameters
329 p_chr_id : id of the contract whose lines need to be updated
330 */
331 PROCEDURE UPDATE_INVOICE_TEXT
332 (
333 p_api_version IN NUMBER DEFAULT 1,
334 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
335 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
336 x_return_status OUT NOCOPY VARCHAR2,
337 x_msg_count OUT NOCOPY NUMBER,
338 x_msg_data OUT NOCOPY VARCHAR2,
339 p_chr_id IN NUMBER
340 )
341 IS
342 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INVOICE_TEXT';
343 l_api_version CONSTANT NUMBER := 1;
344 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
345 l_error_text VARCHAR2(512);
346 BEGIN
347
348 --log key input parameters
349 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
350 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_chr_id=' || p_chr_id||' ,p_commit='||p_commit);
351 END IF;
352
353 --standard api initilization and checks
354 SAVEPOINT update_invoice_text_PUB;
355 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357 END IF;
358 IF FND_API.to_boolean(p_init_msg_list ) THEN
359 FND_MSG_PUB.initialize;
360 END IF;
361 x_return_status := FND_API.G_RET_STS_SUCCESS;
362
363 OKS_RENEW_CONTRACT_PVT.update_invoice_text(
364 p_api_version => 1,
365 p_init_msg_list => FND_API.G_FALSE,
366 p_commit => FND_API.G_FALSE,
367 x_return_status => x_return_status,
368 x_msg_count => x_msg_count,
369 x_msg_data => x_msg_data,
370 p_chr_id => p_chr_id);
371
372 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
373 RAISE FND_API.g_exc_unexpected_error;
374 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
375 RAISE FND_API.g_exc_error;
376 END IF;
377
378 --standard check of p_commit
379 IF FND_API.to_boolean( p_commit ) THEN
380 COMMIT;
381 END IF;
382
383 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
384 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', 'x_return_status='|| x_return_status);
385 END IF;
386 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
387
388 EXCEPTION
389 WHEN FND_API.g_exc_error THEN
390 ROLLBACK TO update_invoice_text_PUB;
391 x_return_status := FND_API.g_ret_sts_error ;
392
393 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
394 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
395 END IF;
396 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
397
398 WHEN FND_API.g_exc_unexpected_error THEN
399 ROLLBACK TO update_invoice_text_PUB;
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
401
402 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
403 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
404 END IF;
405 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
406
407 WHEN OTHERS THEN
408 ROLLBACK TO update_invoice_text_PUB;
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
410
411 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
412 --first log the sqlerrm
413 l_error_text := substr (SQLERRM, 1, 240);
414 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
415 --then add it to the message api list
416 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
417 END IF;
418 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
419
420 END UPDATE_INVOICE_TEXT;
421
422 /*
423 Procedure for getting the user id and name of the contact on whose behalf the
424 contract workflow is launched during renewal
425 Parameters
426 p_chr_id : id of the contract for which the workflow is launched
427 p_hdesk_user_id : fnd user id of the help desk user id setup in GCD. Optional,
428 if not passed will be derived from GCD.
429
430 If no vendor/merchant contact bases on jtf object 'OKX_SALEPERS' can be found for the contract
431 header, the help desk user is used. This behaviour is from R12 onwards, prior to this if a
432 salesrep was not found, contract admin and then contract approver would be used.
433 */
434 PROCEDURE GET_USER_NAME
435 (
436 p_api_version IN NUMBER DEFAULT 1,
437 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
438 x_return_status OUT NOCOPY VARCHAR2,
439 x_msg_count OUT NOCOPY NUMBER,
440 x_msg_data OUT NOCOPY VARCHAR2,
441 p_chr_id IN NUMBER,
442 p_hdesk_user_id IN NUMBER,
443 x_user_id OUT NOCOPY NUMBER,
444 x_user_name OUT NOCOPY VARCHAR2
445 )
446 IS
447 l_api_name CONSTANT VARCHAR2(30) := 'GET_USER_NAME';
448 l_api_version CONSTANT NUMBER := 1;
449 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
450 l_error_text VARCHAR2(512);
451 BEGIN
452
453 --log key input parameters
454 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
455 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_chr_id=' || p_chr_id||' ,p_hdesk_user_id='||p_hdesk_user_id);
456 END IF;
457
458 --standard api initilization and checks
459 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462 IF FND_API.to_boolean(p_init_msg_list ) THEN
463 FND_MSG_PUB.initialize;
464 END IF;
465 x_return_status := FND_API.G_RET_STS_SUCCESS;
466
467 OKS_RENEW_CONTRACT_PVT.get_user_name(
468 p_api_version => 1,
469 p_init_msg_list => FND_API.G_FALSE,
470 x_return_status => x_return_status,
471 x_msg_count => x_msg_count,
472 x_msg_data => x_msg_data,
473 p_chr_id => p_chr_id,
474 p_hdesk_user_id => p_hdesk_user_id,
475 x_user_id => x_user_id,
476 x_user_name => x_user_name);
477
478 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
479 RAISE FND_API.g_exc_unexpected_error;
480 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
481 RAISE FND_API.g_exc_error;
482 END IF;
483
484 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
485 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', 'x_return_status='|| x_return_status||' ,x_user_id='||x_user_id||' ,x_user_name='||x_user_name);
486 END IF;
487 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
488
489 EXCEPTION
490 WHEN FND_API.g_exc_error THEN
491 x_return_status := FND_API.g_ret_sts_error ;
492
493 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
494 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
495 END IF;
496 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
497
498 WHEN FND_API.g_exc_unexpected_error THEN
499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
500
501 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
502 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
503 END IF;
504 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
505
506 WHEN OTHERS THEN
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
508
509 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
510 --first log the sqlerrm
511 l_error_text := substr (SQLERRM, 1, 240);
512 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
513 --then add it to the message api list
514 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
515 END IF;
516 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
517
518 END GET_USER_NAME;
519
520 END OKS_RENEW_CONTRACT_PUB;
[Home] [Help]