[Home] [Help]
PACKAGE BODY: APPS.OKL_IRH_PVT
Source
1 PACKAGE BODY OKL_IRH_PVT AS
2 /* $Header: OKLSIRHB.pls 120.5 2006/08/09 14:18:45 pagarg noship $ */
3
4
5 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) :='OKC_NO_PARENT_RECORD';
6 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) :='OKC_CONTRACTS_UNEXPECTED_ERROR';
7 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
8 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
9 G_EXCEPTION_HALT_VALIDATION exception;
10 PROCEDURE api_copy IS
11 BEGIN
12 null;
13 END api_copy;
14
15 PROCEDURE change_version IS
16 BEGIN
17 null;
18 END change_version;
19
20 --------------------------------------------------------------------------------
21 -- Procedure get_rec for OKL_FE_ITEM_RESIDUAL_ALL
22 --------------------------------------------------------------------------------
23
24 FUNCTION get_rec(
25 p_irh_rec IN okl_irh_rec,
26 x_no_data_found OUT NOCOPY BOOLEAN
27 )RETURN okl_irh_rec IS
28 CURSOR irh_pk_csr(p_id IN NUMBER) IS
29 SELECT
30 ITEM_RESIDUAL_ID,
31 ORIG_ITEM_RESIDUAL_ID,
32 OBJECT_VERSION_NUMBER,
33 CREATED_BY,
34 CREATION_DATE,
35 LAST_UPDATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATE_LOGIN,
38 INVENTORY_ITEM_ID,
39 ORGANIZATION_ID,
40 CATEGORY_ID,
41 CATEGORY_SET_ID,
42 RESI_CATEGORY_SET_ID,
43 CATEGORY_TYPE_CODE,
44 RESIDUAL_TYPE_CODE,
45 CURRENCY_CODE,
46 STS_CODE,
47 EFFECTIVE_FROM_DATE,
48 EFFECTIVE_TO_DATE,
49 ORG_ID
50 FROM OKL_FE_ITEM_RESIDUAL_ALL WHERE OKL_FE_ITEM_RESIDUAL_ALL.item_residual_id=p_id;
51 l_irh_pk irh_pk_csr%ROWTYPE;
52 l_irh_rec okl_irh_rec;
53 BEGIN
54 x_no_data_found:= TRUE;
55 --Get current data base values
56 OPEN irh_pk_csr(p_irh_rec.item_residual_id);
57 FETCH irh_pk_csr INTO
58 l_irh_rec.ITEM_RESIDUAL_ID,
59 l_irh_rec.ORIG_ITEM_RESIDUAL_ID,
60 l_irh_rec.OBJECT_VERSION_NUMBER,
61 l_irh_rec.CREATED_BY,
62 l_irh_rec.CREATION_DATE,
63 l_irh_rec.LAST_UPDATED_BY,
64 l_irh_rec.LAST_UPDATE_DATE,
65 l_irh_rec.LAST_UPDATE_LOGIN,
66 l_irh_rec.INVENTORY_ITEM_ID,
67 l_irh_rec.ORGANIZATION_ID,
68 l_irh_rec.CATEGORY_ID,
69 l_irh_rec.CATEGORY_SET_ID,
70 l_irh_rec.RESI_CATEGORY_SET_ID,
71 l_irh_rec.CATEGORY_TYPE_CODE,
72 l_irh_rec.RESIDUAL_TYPE_CODE,
73 l_irh_rec.CURRENCY_CODE,
74 l_irh_rec.STS_CODE,
75 l_irh_rec.EFFECTIVE_FROM_DATE,
76 l_irh_rec.EFFECTIVE_TO_DATE,
77 l_irh_rec.ORG_ID;
78 x_no_data_found := irh_pk_csr%NOTFOUND;
79 CLOSE irh_pk_csr;
80 RETURN (l_irh_rec);
81 END get_rec;
82
83 FUNCTION get_rec(
84 p_irh_rec IN okl_irh_rec
85 )RETURN okl_irh_rec IS
86 l_row_notfound BOOLEAN:=TRUE; BEGIN
87 RETURN(get_rec(p_irh_rec,l_row_notfound));
88 END get_rec;
89
90 --------------------------------------------------------------------------------
91 -- Procedure get_rec for OKL_FE_ITEM_RESIDUAL
92 --------------------------------------------------------------------------------
93
94 FUNCTION get_rec(
95 p_irhv_rec IN okl_irhv_rec,
96 x_no_data_found OUT NOCOPY BOOLEAN
97 )RETURN okl_irhv_rec IS
98 CURSOR irhv_pk_csr(p_id IN NUMBER) IS
99 SELECT
100 ITEM_RESIDUAL_ID
101 , ORIG_ITEM_RESIDUAL_ID
102 , OBJECT_VERSION_NUMBER
103 , CREATED_BY
104 , CREATION_DATE
105 , LAST_UPDATED_BY
106 , LAST_UPDATE_DATE
107 , LAST_UPDATE_LOGIN
108 , INVENTORY_ITEM_ID
109 , ORGANIZATION_ID
110 , CATEGORY_ID
111 , CATEGORY_SET_ID
112 , RESI_CATEGORY_SET_ID
113 , CATEGORY_TYPE_CODE
114 , RESIDUAL_TYPE_CODE
115 , CURRENCY_CODE
116 , STS_CODE
117 , EFFECTIVE_FROM_DATE
118 , EFFECTIVE_TO_DATE
119 , ORG_ID
120 FROM
121 OKL_FE_ITEM_RESIDUAL_ALL IRH
122 WHERE
123 IRH.ITEM_RESIDUAL_ID = p_id;
124 l_irhv_pk irhv_pk_csr%ROWTYPE;
125 l_irhv_rec okl_irhv_rec;
126 BEGIN
127 x_no_data_found:= TRUE;
128 --Get current data base values
129 OPEN irhv_pk_csr(p_irhv_rec.item_residual_id);
130 FETCH irhv_pk_csr INTO
131 l_irhv_rec.item_residual_id
132 , l_irhv_rec.orig_item_residual_id
133 , l_irhv_rec.object_version_number
134 , l_irhv_rec.created_by
135 , l_irhv_rec.creation_date
136 , l_irhv_rec.last_updated_by
137 , l_irhv_rec.last_update_date
138 , l_irhv_rec.last_update_login
139 , l_irhv_rec.inventory_item_id
140 , l_irhv_rec.organization_id
141 , l_irhv_rec.category_id
142 , l_irhv_rec.category_set_id
143 , l_irhv_rec.resi_category_set_id
144 , l_irhv_rec.category_type_code
145 , l_irhv_rec.residual_type_code
146 , l_irhv_rec.currency_code
147 , l_irhv_rec.sts_code
148 , l_irhv_rec.effective_from_date
149 , l_irhv_rec.effective_to_date
150 , l_irhv_rec.org_id;
151 x_no_data_found := irhv_pk_csr%NOTFOUND;
152 CLOSE irhv_pk_csr;
153 RETURN (l_irhv_rec);
154 END get_rec;
155 FUNCTION get_rec(
156 p_irhv_rec IN okl_irhv_rec
157 )RETURN okl_irhv_rec IS
158 l_row_notfound BOOLEAN:=TRUE; BEGIN
159 RETURN(get_rec(p_irhv_rec,l_row_notfound));
160 END get_rec;
161
162 --------------------------------------------------------------------------------
163 -- Procedure migrate
164 --------------------------------------------------------------------------------
165
166 PROCEDURE migrate(
167 p_from IN okl_irhv_rec,
168 p_to IN OUT NOCOPY okl_irh_rec
169 )IS
170 BEGIN
171 p_to.ITEM_RESIDUAL_ID := p_from.ITEM_RESIDUAL_ID;
172 p_to.ORIG_ITEM_RESIDUAL_ID := p_from.ORIG_ITEM_RESIDUAL_ID;
173 p_to.OBJECT_VERSION_NUMBER := p_from.OBJECT_VERSION_NUMBER;
174 p_to.CREATED_BY := p_from.CREATED_BY;
175 p_to.CREATION_DATE := p_from.CREATION_DATE;
176 p_to.LAST_UPDATED_BY := p_from.LAST_UPDATED_BY;
177 p_to.LAST_UPDATE_DATE := p_from.LAST_UPDATE_DATE;
178 p_to.LAST_UPDATE_LOGIN := p_from.LAST_UPDATE_LOGIN;
179 p_to.INVENTORY_ITEM_ID := p_from.INVENTORY_ITEM_ID;
180 p_to.ORGANIZATION_ID := p_from.ORGANIZATION_ID;
181 p_to.CATEGORY_ID := p_from.CATEGORY_ID;
182 p_to.CATEGORY_SET_ID := p_from.CATEGORY_SET_ID;
183 p_to.RESI_CATEGORY_SET_ID := p_from.RESI_CATEGORY_SET_ID;
184 p_to.CATEGORY_TYPE_CODE := p_from.CATEGORY_TYPE_CODE;
185 p_to.RESIDUAL_TYPE_CODE := p_from.RESIDUAL_TYPE_CODE;
186 p_to.CURRENCY_CODE := p_from.CURRENCY_CODE;
187 p_to.STS_CODE := p_from.STS_CODE;
188 p_to.EFFECTIVE_FROM_DATE := p_from.EFFECTIVE_FROM_DATE;
189 p_to.EFFECTIVE_TO_DATE := p_from.EFFECTIVE_TO_DATE;
190 p_to.ORG_ID := p_from.ORG_ID;
191 END migrate;
192
193 PROCEDURE migrate(
194 p_from IN okl_irh_rec,
195 p_to IN OUT NOCOPY okl_irhv_rec
196 )IS
197 BEGIN
198 p_to.ITEM_RESIDUAL_ID := p_from.ITEM_RESIDUAL_ID;
199 p_to.ORIG_ITEM_RESIDUAL_ID := p_from.ORIG_ITEM_RESIDUAL_ID;
200 p_to.OBJECT_VERSION_NUMBER := p_from.OBJECT_VERSION_NUMBER;
201 p_to.CREATED_BY := p_from.CREATED_BY;
202 p_to.CREATION_DATE := p_from.CREATION_DATE;
203 p_to.LAST_UPDATED_BY := p_from.LAST_UPDATED_BY;
204 p_to.LAST_UPDATE_DATE := p_from.LAST_UPDATE_DATE;
205 p_to.LAST_UPDATE_LOGIN := p_from.LAST_UPDATE_LOGIN;
206 p_to.INVENTORY_ITEM_ID := p_from.INVENTORY_ITEM_ID;
207 p_to.ORGANIZATION_ID := p_from.ORGANIZATION_ID;
208 p_to.CATEGORY_ID := p_from.CATEGORY_ID;
209 p_to.CATEGORY_SET_ID := p_from.CATEGORY_SET_ID;
210 p_to.RESI_CATEGORY_SET_ID := p_from.RESI_CATEGORY_SET_ID;
211 p_to.CATEGORY_TYPE_CODE := p_from.CATEGORY_TYPE_CODE;
212 p_to.RESIDUAL_TYPE_CODE := p_from.RESIDUAL_TYPE_CODE;
213 p_to.CURRENCY_CODE := p_from.CURRENCY_CODE;
214 p_to.STS_CODE := p_from.STS_CODE;
215 p_to.EFFECTIVE_FROM_DATE := p_from.EFFECTIVE_FROM_DATE;
216 p_to.EFFECTIVE_TO_DATE := p_from.EFFECTIVE_TO_DATE;
217 p_to.ORG_ID := p_from.ORG_ID;
218 END migrate;
219
220 ---------------------------------
221 -- FUNCTION validate_item_residual_id
222 ---------------------------------
223 FUNCTION validate_item_residual_id (p_item_residual_id IN NUMBER) Return Varchar2 IS
224 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_item_residual_id';
225 BEGIN
226 IF p_item_residual_id IS NULL THEN
227 OKL_API.set_message(p_app_name => G_APP_NAME,
228 p_msg_name => G_REQUIRED_VALUE,
229 p_token1 => G_COL_NAME_TOKEN,
230 p_token1_value => 'item_residual_id');
231 RAISE OKL_API.G_EXCEPTION_ERROR;
232 END IF;
233 Return G_RET_STS_SUCCESS;
234 EXCEPTION
235 WHEN OKL_API.G_EXCEPTION_ERROR THEN
236 Return G_RET_STS_ERROR;
237 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
238 Return G_RET_STS_UNEXP_ERROR;
239 WHEN OTHERS THEN
240 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
241 p_msg_name => G_DB_ERROR,
242 p_token1 => G_PROG_NAME_TOKEN,
243 p_token1_value => l_api_name,
244 p_token2 => 'SQLCODE',
245 p_token2_value => sqlcode,
246 p_token3 => 'SQLERRM',
247 p_token3_value => sqlerrm);
248 Return G_RET_STS_UNEXP_ERROR;
249 END validate_item_residual_id;
250
251
252
253 -------------------------------------------
254 -- Function validate_object_version_number
255 -------------------------------------------
256 FUNCTION validate_object_version_number (p_object_version_number IN NUMBER) Return Varchar2 IS
257 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_object_version_number';
258 BEGIN
259 IF (p_object_version_number IS NULL) OR (p_object_version_number = OKL_API.G_MISS_NUM) THEN
260 OKL_API.set_message(p_app_name => G_APP_NAME,
261 p_msg_name => G_REQUIRED_VALUE,
262 p_token1 => G_COL_NAME_TOKEN,
263 p_token1_value => 'object_version_number');
264 RAISE OKL_API.G_EXCEPTION_ERROR;
265 END IF;
266 Return G_RET_STS_SUCCESS;
267 EXCEPTION
268 WHEN OKL_API.G_EXCEPTION_ERROR THEN
269 Return G_RET_STS_ERROR;
270 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
271 Return G_RET_STS_UNEXP_ERROR;
272 WHEN OTHERS THEN
273 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
274 p_msg_name => G_DB_ERROR,
275 p_token1 => G_PROG_NAME_TOKEN,
276 p_token1_value => l_api_name,
277 p_token2 => 'SQLCODE',
278 p_token2_value => sqlcode,
279 p_token3 => 'SQLERRM',
280 p_token3_value => sqlerrm);
281 Return G_RET_STS_UNEXP_ERROR;
282 END validate_object_version_number;
283
284
285 ---------------------------------
286 -- FUNCTION validate_category_type_code
287 ---------------------------------
288 FUNCTION validate_category_type_code (p_category_type_code IN VARCHAR2) Return Varchar2 IS
289 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_category_type_code';
290 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
291 BEGIN
292 IF p_category_type_code IS NULL THEN
293 OKL_API.set_message(p_app_name => G_APP_NAME,
294 p_msg_name => G_REQUIRED_VALUE,
295 p_token1 => G_COL_NAME_TOKEN,
296 p_token1_value => 'category_type_code');
297 RAISE OKL_API.G_EXCEPTION_ERROR;
298 END IF;
299 l_return_status := OKL_UTIL.check_lookup_code(
300 p_lookup_type => 'OKL_SOURCE_TYPES'
301 ,p_lookup_code => p_category_type_code);
302 IF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
303 OKL_API.set_message(p_app_name => G_APP_NAME,
304 p_msg_name => G_INVALID_VALUE,
305 p_token1 => G_COL_NAME_TOKEN,
306 p_token1_value => 'category_type_code');
307 RAISE OKL_API.G_EXCEPTION_ERROR;
308 END IF;
309 Return G_RET_STS_SUCCESS;
310 EXCEPTION
311 WHEN OKL_API.G_EXCEPTION_ERROR THEN
312 Return G_RET_STS_ERROR;
313 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
314 Return G_RET_STS_UNEXP_ERROR;
315 WHEN OTHERS THEN
316 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
317 p_msg_name => G_DB_ERROR,
318 p_token1 => G_PROG_NAME_TOKEN,
319 p_token1_value => l_api_name,
320 p_token2 => 'SQLCODE',
321 p_token2_value => sqlcode,
322 p_token3 => 'SQLERRM',
323 p_token3_value => sqlerrm);
324 Return G_RET_STS_UNEXP_ERROR;
325 END validate_category_type_code;
326
327 ---------------------------------
328 -- FUNCTION validate_residual_type_code
329 ---------------------------------
330 FUNCTION validate_residual_type_code (p_category_type_code IN VARCHAR2) Return Varchar2 IS
331 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_residual_type_code';
332 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
333 BEGIN
334 IF p_category_type_code IS NULL THEN
335 OKL_API.set_message(p_app_name => G_APP_NAME,
336 p_msg_name => G_REQUIRED_VALUE,
337 p_token1 => G_COL_NAME_TOKEN,
338 p_token1_value => 'residual_type_code');
339 RAISE OKL_API.G_EXCEPTION_ERROR;
340 END IF;
341 l_return_status := OKL_UTIL.check_lookup_code(
342 p_lookup_type => 'OKL_RESIDUAL_TYPES'
343 ,p_lookup_code => p_category_type_code);
344 IF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
345 OKL_API.set_message(p_app_name => G_APP_NAME,
346 p_msg_name => G_INVALID_VALUE,
347 p_token1 => G_COL_NAME_TOKEN,
348 p_token1_value => 'residual_type_code');
349 RAISE OKL_API.G_EXCEPTION_ERROR;
350 END IF;
351 Return G_RET_STS_SUCCESS;
352 EXCEPTION
353 WHEN OKL_API.G_EXCEPTION_ERROR THEN
354 Return G_RET_STS_ERROR;
355 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
356 Return G_RET_STS_UNEXP_ERROR;
357 WHEN OTHERS THEN
358 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
359 p_msg_name => G_DB_ERROR,
360 p_token1 => G_PROG_NAME_TOKEN,
361 p_token1_value => l_api_name,
362 p_token2 => 'SQLCODE',
363 p_token2_value => sqlcode,
364 p_token3 => 'SQLERRM',
365 p_token3_value => sqlerrm);
366 Return G_RET_STS_UNEXP_ERROR;
367 END validate_residual_type_code;
368
369 ---------------------------------
370 -- FUNCTION validate_sts_code
371 ---------------------------------
372 FUNCTION validate_sts_code(p_sts_code IN VARCHAR2) RETURN VARCHAR2 IS
373 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_sts_code';
374 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
375 BEGIN
376 -- Column is mandatory
377 IF (p_sts_code is null) THEN
378 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
379 p_msg_name => g_required_value,
380 p_token1 => g_col_name_token,
381 p_token1_value => 'sts_code');
382 -- notify caller of an error
383 RAISE OKL_API.G_EXCEPTION_ERROR;
384 END IF;
385 -- Lookup Code Validation
386 l_return_status := OKL_UTIL.check_lookup_code(
387 p_lookup_type => 'OKL_PRC_STATUS',
388 p_lookup_code => p_sts_code);
389
390 IF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
391 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
392 p_msg_name => g_invalid_value,
393 p_token1 => g_col_name_token,
394 p_token1_value => 'sts_code');
395 -- notify caller of an error
396 raise OKL_API.G_EXCEPTION_ERROR;
397 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
398 -- notify caller of an error
399 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
400 END IF;
401 Return G_RET_STS_SUCCESS;
402 EXCEPTION
403 WHEN OKL_API.G_EXCEPTION_ERROR THEN
404 Return G_RET_STS_ERROR;
405 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
406 Return G_RET_STS_UNEXP_ERROR;
407 WHEN OTHERS THEN
408 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
409 p_msg_name => G_DB_ERROR,
410 p_token1 => G_PROG_NAME_TOKEN,
411 p_token1_value => l_api_name,
412 p_token2 => 'SQLCODE',
413 p_token2_value => sqlcode,
414 p_token3 => 'SQLERRM',
415 p_token3_value => sqlerrm);
416 Return G_RET_STS_UNEXP_ERROR;
417 END validate_sts_code;
418
419 PROCEDURE validate_currency_code( x_return_status OUT NOCOPY VARCHAR2,
420 p_irhv_rec IN okl_irhv_rec) IS
421 BEGIN
422 -- initialize return status
423 x_return_status := OKL_API.G_RET_STS_SUCCESS;
424
425 -- data is required
426 IF (p_irhv_rec.currency_code IS NOT NULL) THEN
427 -- check from currency values using the generic okl_util.validate_currency_code
428 x_return_status := OKL_ACCOUNTING_UTIL.validate_currency_code(p_irhv_rec.currency_code);
429 IF (x_return_status <> OKL_API.G_TRUE) THEN
430 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
431 p_msg_name => G_INVALID_VALUE,
432 p_token1 => G_COL_NAME_TOKEN,
433 p_token1_value => 'currency_code');
434
435 -- halt further validation of this column
436 RAISE G_EXCEPTION_HALT_VALIDATION;
437 END IF;
438 END IF;
439 EXCEPTION
440 WHEN G_EXCEPTION_HALT_VALIDATION THEN
441 x_return_status := OKL_API.G_RET_STS_ERROR;
442 WHEN OTHERS THEN
443 -- store SQL error message on message stack for caller
444 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
445 p_msg_name => g_unexpected_error,
446 p_token1 => g_sqlcode_token,
447 p_token1_value => sqlcode,
448 p_token2 => g_sqlerrm_token,
449 p_token2_value => sqlerrm);
450 -- notify caller of an UNEXPECTED error
451 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
452
453 END validate_currency_code;
454
455 FUNCTION null_out_defaults(p_irhv_rec IN okl_irhv_rec) RETURN okl_irhv_rec IS
456 l_irhv_rec okl_irhv_rec:= p_irhv_rec;
457 BEGIN
458 IF (l_irhv_rec.ITEM_RESIDUAL_ID=OKL_API.G_MISS_NUM) THEN
459 l_irhv_rec.ITEM_RESIDUAL_ID:=NULL;
460 END IF;
461 IF (l_irhv_rec.ORIG_ITEM_RESIDUAL_ID=OKL_API.G_MISS_NUM) THEN
462 l_irhv_rec.ORIG_ITEM_RESIDUAL_ID:=NULL;
463 END IF;
464 IF (l_irhv_rec.ORG_ID=OKL_API.G_MISS_NUM) THEN
465 l_irhv_rec.ORG_ID:=NULL;
466 END IF;
467 IF (l_irhv_rec.CREATED_BY=OKL_API.G_MISS_NUM) THEN
468 l_irhv_rec.CREATED_BY:=NULL;
469 END IF;
470 IF (l_irhv_rec.CREATION_DATE=OKL_API.G_MISS_DATE) THEN
471 l_irhv_rec.CREATION_DATE:=NULL;
472 END IF;
473 IF (l_irhv_rec.LAST_UPDATED_BY=OKL_API.G_MISS_NUM) THEN
474 l_irhv_rec.LAST_UPDATED_BY:=NULL;
475 END IF;
476 IF (l_irhv_rec.LAST_UPDATE_DATE=OKL_API.G_MISS_DATE) THEN
477 l_irhv_rec.LAST_UPDATE_DATE:=NULL;
478 END IF;
479 IF (l_irhv_rec.LAST_UPDATE_LOGIN=OKL_API.G_MISS_NUM) THEN
480 l_irhv_rec.LAST_UPDATE_LOGIN:=NULL;
481 END IF;
482 IF (l_irhv_rec.INVENTORY_ITEM_ID = OKL_API.G_MISS_NUM) THEN
483 l_irhv_rec.INVENTORY_ITEM_ID:=NULL;
484 END IF;
485 IF (l_irhv_rec.ORGANIZATION_ID = OKL_API.G_MISS_NUM) THEN
486 l_irhv_rec.ORGANIZATION_ID:=NULL;
487 END IF;
488 IF (l_irhv_rec.CATEGORY_ID = OKL_API.G_MISS_NUM) THEN
489 l_irhv_rec.CATEGORY_ID:=NULL;
490 END IF;
491 IF (l_irhv_rec.CATEGORY_SET_ID = OKL_API.G_MISS_NUM) THEN
492 l_irhv_rec.CATEGORY_SET_ID:=NULL;
493 END IF;
494 IF (l_irhv_rec.RESI_CATEGORY_SET_ID = OKL_API.G_MISS_NUM) THEN
495 l_irhv_rec.RESI_CATEGORY_SET_ID:=NULL;
496 END IF;
497 IF (l_irhv_rec.CATEGORY_TYPE_CODE = OKL_API.G_MISS_CHAR) THEN
498 l_irhv_rec.CATEGORY_TYPE_CODE:=NULL;
499 END IF;
500 IF (l_irhv_rec.RESIDUAL_TYPE_CODE = OKL_API.G_MISS_CHAR) THEN
501 l_irhv_rec.RESIDUAL_TYPE_CODE:=NULL;
502 END IF;
503 IF (l_irhv_rec.CURRENCY_CODE = OKL_API.G_MISS_CHAR) THEN
504 l_irhv_rec.CURRENCY_CODE:=NULL;
505 END IF;
506 IF (l_irhv_rec.STS_CODE = OKL_API.G_MISS_CHAR) THEN
507 l_irhv_rec.STS_CODE:=NULL;
508 END IF;
509 IF (l_irhv_rec.EFFECTIVE_FROM_DATE = OKL_API.G_MISS_DATE) THEN
510 l_irhv_rec.EFFECTIVE_FROM_DATE:=NULL;
511 END IF;
512 IF (l_irhv_rec.EFFECTIVE_TO_DATE = OKL_API.G_MISS_DATE) THEN
513 l_irhv_rec.EFFECTIVE_TO_DATE:=NULL;
514 END IF;
515
516 RETURN(l_irhv_rec);
517 END null_out_defaults;
518
519 FUNCTION get_seq_id RETURN NUMBER IS
520 BEGIN
521 RETURN(okc_p_util.raw_to_number(sys_guid()));
522 END get_seq_id;
523
524 FUNCTION Validate_Attributes (p_irhv_rec IN okl_irhv_rec) RETURN VARCHAR2 IS
525 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
526 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
527 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_attributes';
528 BEGIN
529 -- ***
530 -- item_residual_id
531 -- ***
532 l_return_status := validate_item_residual_id(p_irhv_rec.item_residual_id);
533
534 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
535 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
536 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
537 RAISE OKL_API.G_EXCEPTION_ERROR;
538 END IF;
539 -- ***
540 -- validate_category_type_code
541 -- ***
542 l_return_status := validate_category_type_code(p_irhv_rec.category_type_code);
543
544 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
545 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
546 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
547 RAISE OKL_API.G_EXCEPTION_ERROR;
548 END IF;
549 -- ***
550 -- validate_residual_type_code
551 -- ***
552 l_return_status := validate_residual_type_code(p_irhv_rec.residual_type_code);
553
554 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
555 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
556 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
557 RAISE OKL_API.G_EXCEPTION_ERROR;
558 END IF;
559 -- ***
560 -- validate_sts_code
561 -- ***
562 l_return_status := validate_sts_code(p_irhv_rec.sts_code);
563
564 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
565 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
566 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
567 RAISE OKL_API.G_EXCEPTION_ERROR;
568 END IF;
569
570 -- ***
571 -- validate_currency_code
572 -- ***
573 validate_currency_code(l_return_status,p_irhv_rec);
574
575 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
576 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
577 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
578 RAISE OKL_API.G_EXCEPTION_ERROR;
579 END IF;
580
581 RETURN (x_return_status);
582
583 EXCEPTION
584
585 WHEN OKL_API.G_EXCEPTION_ERROR THEN
586
587 return G_RET_STS_ERROR;
588
589 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
590
591 return G_RET_STS_UNEXP_ERROR;
592
593 WHEN OTHERS THEN
594
595 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
596 p_msg_name => G_DB_ERROR,
597 p_token1 => G_PROG_NAME_TOKEN,
598 p_token1_value => l_api_name,
599 p_token2 => 'SQLCODE',
600 p_token2_value => sqlcode,
601 p_token3 => 'SQLERRM',
602 p_token3_value => sqlerrm);
603
604 return G_RET_STS_UNEXP_ERROR;
605
606
607 END Validate_Attributes;
608
609 FUNCTION validate_record(p_irhv_rec IN okl_irhv_rec) RETURN VARCHAR2 IS
610 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
611 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
612 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_record';
613 BEGIN
614 /* Check if the Effective From is null */
615 IF p_irhv_rec.effective_from_date IS NULL THEN
616 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
617 p_msg_name => 'OKL_REQUIRED_EFFECTIVE_FROM');
618 RAISE OKL_API.G_EXCEPTION_ERROR;
619 END IF;
620 /* Check if the Effective to date is prior to effective From */
621 IF p_irhv_rec.effective_to_date < p_irhv_rec.effective_from_date THEN
622
623 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
624 p_msg_name => 'OKL_INVALID_EFFECTIVE_TO');
625
626 RAISE OKL_API.G_EXCEPTION_ERROR;
627 END IF;
628 /* Check for currency code when the residual type is Amount */
629 IF p_irhv_rec.residual_type_code = G_RESD_AMOUNT AND p_irhv_rec.currency_code IS NULL THEN
630 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
631 ,p_msg_name => G_REQUIRED_VALUE
632 ,p_token1 => G_COL_NAME_TOKEN
633 ,p_token1_value => 'currency_code');
634 RAISE OKL_API.G_EXCEPTION_ERROR;
635 END IF;
636
637 RETURN x_return_status;
638
639 EXCEPTION
640 WHEN OKL_API.G_EXCEPTION_ERROR THEN
641 RETURN G_RET_STS_ERROR;
642 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
643 RETURN G_RET_STS_UNEXP_ERROR;
644 WHEN OTHERS THEN
645 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
646 p_msg_name => G_DB_ERROR,
647 p_token1 => G_PROG_NAME_TOKEN,
648 p_token1_value => l_api_name,
649 p_token2 => G_SQLCODE_TOKEN,
650 p_token2_value => sqlcode,
651 p_token3 => G_SQLERRM_TOKEN,
652 p_token3_value => sqlerrm);
653 RETURN G_RET_STS_UNEXP_ERROR;
654
655 END validate_record;
656
657 -- Returns G_RET_STS_SUCCESS if the residual is not existing.
658 FUNCTION check_existence (p_irhv_rec IN okl_irhv_rec) RETURN VARCHAR2 IS
659 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'check_existence';
660 l_residual_id NUMBER;
661 l_residual_exists BOOLEAN := false;
662 l_residual_type FND_LOOKUPS.MEANING%TYPE;
663 l_category_type FND_LOOKUPS.MEANING%TYPE;
664 l_source_type VARCHAR2(30);
665
666 -- Cursor to get look_up meaning
667 CURSOR get_lookup_meaning(p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
668 SELECT
669 FND.MEANING
670 FROM
671 FND_LOOKUPS FND
672 WHERE
673 FND.LOOKUP_TYPE = p_lookup_type
674 AND FND.LOOKUP_CODE = p_lookup_code;
675
676 -- Retrieves the Residual ID of the item passed.
677 -- Item is identified by the INVENTORY_ITEM_ID, ORAGANIZATION_ID and the CATEGORY_SET_ID
678 CURSOR get_residual_items (p_inventory_item_id NUMBER, p_organization_id NUMBER, p_category_set_id NUMBER, p_residual_type_code VARCHAR2, p_currency_code VARCHAR2) IS
679 SELECT
680 IRHV.ITEM_RESIDUAL_ID
681 FROM
682 OKL_FE_ITEM_RESIDUAL IRHV
683 -- , OKL_SYSTEM_PARAMS_ALL_V SYSOP
684 WHERE
685 IRHV.INVENTORY_ITEM_ID = p_inventory_item_id
686 AND IRHV.ORGANIZATION_ID = p_organization_id
687 AND IRHV.CATEGORY_SET_ID = p_category_set_id
688 AND IRHV.RESIDUAL_TYPE_CODE = p_residual_type_code
689 AND NVL(IRHV.CURRENCY_CODE,'NONE') = NVL(p_currency_code,'NONE')
690 -- AND SYSOP.CATEGORY_SET_ID = IRHV.CATEGORY_SET_ID
691 ; -- end of cursor get_residual_items
692
693
694 -- Retrieves the Residual ID of the item category passed.
695 -- Item category is identified by the CATEGORY_ID and the CATEGORY_SET_ID
696 CURSOR get_residual_categories (p_category_id NUMBER, p_category_set_id NUMBER, p_residual_type_code VARCHAR2, p_currency_code VARCHAR2) IS
697 SELECT
698 IRHV.ITEM_RESIDUAL_ID
699 FROM
700 OKL_FE_ITEM_RESIDUAL IRHV
701 -- , OKL_SYSTEM_PARAMS_ALL_V SYSOP
702 WHERE
703 IRHV.CATEGORY_ID = p_category_id
704 AND IRHV.CATEGORY_SET_ID = p_category_set_id
705 AND IRHV.RESIDUAL_TYPE_CODE = p_residual_type_code
706 AND NVL(IRHV.CURRENCY_CODE,'NONE') = NVL(p_currency_code,'NONE')
707 -- AND SYSOP.CATEGORY_SET_ID = IRHV.CATEGORY_SET_ID
708 ; -- end of cursor get_residual_categories
709
710
711 -- Retrieves the Residual ID of the residual category set passed.
712 -- Residual category set is identified by the RESI_CATEGORY_SET_ID.
713 CURSOR get_residual_rcs ( p_resi_category_set_id NUMBER, p_residual_type_code VARCHAR2, p_currency_code VARCHAR2) IS
714 SELECT
715 IRHV.ITEM_RESIDUAL_ID
716 FROM
717 OKL_FE_ITEM_RESIDUAL IRHV
718 -- , OKL_SYSTEM_PARAMS_ALL_V SYSOP
719 WHERE
720 IRHV.RESI_CATEGORY_SET_ID = p_resi_category_set_id
721 AND IRHV.RESIDUAL_TYPE_CODE = p_residual_type_code
722 AND NVL(IRHV.CURRENCY_CODE,'NONE') = NVL(p_currency_code,'NONE')
723 -- AND SYSOP.CATEGORY_SET_ID = IRHV.CATEGORY_SET_ID
724 ; -- end of cursor get_residual_rcs
725
726 BEGIN
727
728 IF p_irhv_rec.category_type_code = G_CAT_ITEM THEN
729 OPEN get_residual_items(p_irhv_rec.inventory_item_id, p_irhv_rec.organization_id, p_irhv_rec.category_set_id, p_irhv_rec.residual_type_code, p_irhv_rec.currency_code);
730 FETCH get_residual_items INTO l_residual_id;
731 IF get_residual_items%FOUND THEN
732 l_residual_exists := TRUE;
733 END IF; -- end of check for residuals for item
734 CLOSE get_residual_items;
735 ELSIF p_irhv_rec.category_type_code = G_CAT_ITEM_CAT THEN
736 OPEN get_residual_categories(p_irhv_rec.category_id, p_irhv_rec.category_set_id, p_irhv_rec.residual_type_code, p_irhv_rec.currency_code);
737 FETCH get_residual_categories INTO l_residual_id;
738 IF get_residual_categories%FOUND THEN
739 l_residual_exists := TRUE;
740 END IF; -- end of check for residuals for categories
741 CLOSE get_residual_categories;
742
743 ELSIF p_irhv_rec.category_type_code = G_CAT_RES_CAT THEN
744 OPEN get_residual_rcs(p_irhv_rec.resi_category_set_id, p_irhv_rec.residual_type_code, p_irhv_rec.currency_code);
745 FETCH get_residual_rcs INTO l_residual_id;
746 IF get_residual_rcs%FOUND THEN
747 l_residual_exists := TRUE;
748 END IF; -- end of check for residuals for Residual Category set
749 CLOSE get_residual_rcs;
750 END IF; -- end of check for source type = Item or Item Category or Residual Category Set
751
752 IF ( l_residual_exists ) THEN
753 OPEN get_lookup_meaning('OKL_SOURCE_TYPES',p_irhv_rec.category_type_code);
754 FETCH get_lookup_meaning INTO l_category_type;
755 CLOSE get_lookup_meaning;
756
757 OPEN get_lookup_meaning('OKL_RESIDUAL_TYPES',p_irhv_rec.residual_type_code);
758 FETCH get_lookup_meaning INTO l_residual_type;
759 CLOSE get_lookup_meaning;
760
761
762 IF p_irhv_rec.residual_type_code = G_RESD_AMOUNT THEN
763 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
764 p_msg_name => 'OKL_ITMRES_EXISTS_FOR_AMOUNT',
765 p_token1 => 'CATEGORY_TYPE',
766 p_token1_value => l_category_type);
767 ELSE
768 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
769 p_msg_name => 'OKL_ITMRES_EXISTS_FOR_PERCENT',
770 p_token1 => 'CATEGORY_TYPE',
771 p_token1_value => l_category_type);
772 END IF;
773 RAISE OKL_API.G_EXCEPTION_ERROR;
774 ELSE
775 RETURN G_RET_STS_SUCCESS;
776 END IF;
777
778 EXCEPTION
779 WHEN OKL_API.G_EXCEPTION_ERROR THEN
780 RETURN G_RET_STS_ERROR;
781 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
782 RETURN G_RET_STS_UNEXP_ERROR;
783 WHEN OTHERS THEN
784 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
785 p_msg_name => G_DB_ERROR,
786 p_token1 => G_PROG_NAME_TOKEN,
787 p_token1_value => l_api_name,
788 p_token2 => G_SQLCODE_TOKEN,
789 p_token2_value => sqlcode,
790 p_token3 => G_SQLERRM_TOKEN,
791 p_token3_value => sqlerrm);
792 RETURN G_RET_STS_UNEXP_ERROR;
793 END check_existence;
794 --------------------------------------------------------------------------------
795 -- Procedure insert_row
796 --------------------------------------------------------------------------------
797 PROCEDURE insert_row(
798 p_api_version IN NUMBER ,
799 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
800 x_return_status OUT NOCOPY VARCHAR2,
801 x_msg_count OUT NOCOPY NUMBER,
802 x_msg_data OUT NOCOPY VARCHAR2,
803 p_irh_rec IN okl_irh_rec,
804 x_irh_rec OUT NOCOPY okl_irh_rec)IS
805 l_api_version CONSTANT NUMBER:=1;
806 l_api_name CONSTANT VARCHAR2(30):='insert_row';
807 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
808 l_irh_rec okl_irh_rec := p_irh_rec;
809
810 FUNCTION Set_Attributes(
811 p_irh_rec IN okl_irh_rec,
812 x_irh_rec OUT NOCOPY okl_irh_rec
813 ) RETURN VARCHAR2 IS
814 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
815 BEGIN
816 x_irh_rec := p_irh_rec;
817 RETURN (l_return_status);
818 END Set_Attributes;
819
820 BEGIN
821 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
822 G_PKG_NAME,
823 p_init_msg_list,
824 l_api_version,
825 p_api_version,
826 '_PVT',
827 x_return_status);
828 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
829 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
830 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
831 RAISE OKL_API.G_EXCEPTION_ERROR;
832 END IF;
833 --Setting Item Attributes
834 l_return_status:=Set_Attributes(p_irh_rec,
835 l_irh_rec);
836 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
837 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
838 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
839 RAISE OKL_API.G_EXCEPTION_ERROR;
840 END IF;
841 INSERT INTO OKL_FE_ITEM_RESIDUAL_ALL(
842 ITEM_RESIDUAL_ID,
843 ORIG_ITEM_RESIDUAL_ID,
844 OBJECT_VERSION_NUMBER,
845 CREATED_BY,
846 CREATION_DATE,
847 LAST_UPDATED_BY,
848 LAST_UPDATE_DATE,
849 LAST_UPDATE_LOGIN,
850 INVENTORY_ITEM_ID,
851 ORGANIZATION_ID,
852 CATEGORY_ID,
853 CATEGORY_SET_ID,
854 RESI_CATEGORY_SET_ID,
855 CATEGORY_TYPE_CODE,
856 RESIDUAL_TYPE_CODE,
857 CURRENCY_CODE,
858 STS_CODE,
859 EFFECTIVE_FROM_DATE,
860 EFFECTIVE_TO_DATE,
861 ORG_ID)
862 VALUES (
863 l_irh_rec.ITEM_RESIDUAL_ID,
864 l_irh_rec.ORIG_ITEM_RESIDUAL_ID,
865 l_irh_rec.OBJECT_VERSION_NUMBER,
866 l_irh_rec.CREATED_BY,
867 l_irh_rec.CREATION_DATE,
868 l_irh_rec.LAST_UPDATED_BY,
869 l_irh_rec.LAST_UPDATE_DATE,
870 l_irh_rec.LAST_UPDATE_LOGIN,
871 l_irh_rec.INVENTORY_ITEM_ID,
872 l_irh_rec.ORGANIZATION_ID,
873 l_irh_rec.CATEGORY_ID,
874 l_irh_rec.CATEGORY_SET_ID,
875 l_irh_rec.RESI_CATEGORY_SET_ID,
876 l_irh_rec.CATEGORY_TYPE_CODE,
877 l_irh_rec.RESIDUAL_TYPE_CODE,
878 l_irh_rec.CURRENCY_CODE,
879 l_irh_rec.STS_CODE,
880 l_irh_rec.EFFECTIVE_FROM_DATE,
881 l_irh_rec.EFFECTIVE_TO_DATE,
882 l_irh_rec.ORG_ID);
883
884 --Set OUT Values
885 x_irh_rec:=l_irh_rec;
886 x_return_status := l_return_status;
887 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
888
889 EXCEPTION
890 WHEN G_EXCEPTION_HALT_VALIDATION then
891 -- No action necessary. Validation can continue to next attribute/column
892 null;
893
894 WHEN OKL_API.G_EXCEPTION_ERROR THEN
895 x_return_status := OKL_API.HANDLE_EXCEPTIONS
896 (
897 l_api_name,
898 G_PKG_NAME,
899 'OKL_API.G_RET_STS_ERROR',
900 x_msg_count,
901 x_msg_data,
902 '_PVT'
903 );
904
905 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
906 x_return_status := OKL_API.HANDLE_EXCEPTIONS
907 (
908 l_api_name,
909 G_PKG_NAME,
910 'OKL_API.G_RET_STS_UNEXP_ERROR',
911 x_msg_count,
912 x_msg_data,
913 '_PVT'
914 );
915
916 WHEN OTHERS THEN
917 x_return_status := OKL_API.HANDLE_EXCEPTIONS
918 (
919 l_api_name,
920 G_PKG_NAME,
921 'OTHERS',
922 x_msg_count,
923 x_msg_data,
924 '_PVT'
925 );
926 END insert_row;
927 --------------------------------------------------------------------------------
928 -- Procedure insert_row_v
929 --------------------------------------------------------------------------------
930 PROCEDURE insert_row(
931 p_api_version IN NUMBER ,
932 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
933 x_return_status OUT NOCOPY VARCHAR2,
934 x_msg_count OUT NOCOPY NUMBER,
935 x_msg_data OUT NOCOPY VARCHAR2,
936 p_irhv_rec IN okl_irhv_rec,
937 x_irhv_rec OUT NOCOPY okl_irhv_rec)IS
938 l_api_version CONSTANT NUMBER:=1;
939 l_api_name CONSTANT VARCHAR2(30):='v_insert_row';
940 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
941 l_irhv_rec okl_irhv_rec;
942 l_def_irhv_rec okl_irhv_rec;
943 l_irhrec okl_irh_rec;
944 lx_irh_rec okl_irh_rec;
945
946 FUNCTION fill_who_columns(
947 p_irhv_rec IN okl_irhv_rec
948 )RETURN okl_irhv_rec IS
949 l_irhv_rec okl_irhv_rec:=p_irhv_rec;
950 BEGIN
951 l_irhv_rec.CREATION_DATE := SYSDATE;
952 l_irhv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
953 l_irhv_rec.LAST_UPDATE_DATE := SYSDATE;
954 l_irhv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
955 l_irhv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
956 RETURN (l_irhv_rec);
957 END fill_who_columns;
958
959 FUNCTION Set_Attributes(
960 p_irhv_rec IN okl_irhv_rec,
961 x_irhv_rec OUT NOCOPY okl_irhv_rec
962 ) RETURN VARCHAR2 IS
963 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
964 BEGIN
965 x_irhv_rec := p_irhv_rec;
966 x_irhv_rec.ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID();
967 RETURN (l_return_status);
968 END Set_Attributes;
969 BEGIN
970 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
971 G_PKG_NAME,
972 p_init_msg_list,
973 l_api_version,
974 p_api_version,
975 '_PVT',
976 x_return_status);
977 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
978 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
979 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
980 RAISE OKL_API.G_EXCEPTION_ERROR;
981 END IF;
982 l_irhv_rec:=null_out_defaults(p_irhv_rec);
983 -- Set Primary key value
984 l_irhv_rec.ITEM_RESIDUAL_ID := get_seq_id;
985 -- Set the ORIG_ITEM_RESIDUAL_ID of the record for duplication
986 l_irhv_rec.orig_item_residual_id := p_irhv_rec.item_residual_id;
987 --Setting Item Attributes
988 l_return_status:=Set_Attributes(l_irhv_rec,l_def_irhv_rec);
989 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
990 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
991 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
992 RAISE OKL_API.G_EXCEPTION_ERROR;
993 END IF;
994 l_def_irhv_rec := fill_who_columns(l_def_irhv_rec);
995 l_return_status := Validate_Attributes(l_def_irhv_rec);
996 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
997 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
998 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
999 RAISE OKL_API.G_EXCEPTION_ERROR;
1000 END IF;
1001
1002 l_return_status := check_existence(l_def_irhv_rec);
1003 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1004 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1005 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1006 RAISE OKL_API.G_EXCEPTION_ERROR;
1007 END IF;
1008
1009 l_return_status := Validate_Record(l_def_irhv_rec);
1010 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1011 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1012 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1013 RAISE OKL_API.G_EXCEPTION_ERROR;
1014 END IF;
1015 migrate(l_def_irhv_rec,l_irhrec);
1016 insert_row(
1017 p_api_version,
1018 p_init_msg_list,
1019 l_return_status,
1020 x_msg_count,
1021 x_msg_data,
1022 l_irhrec,
1023 lx_irh_rec);
1024 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1025 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1026 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1027 RAISE OKL_API.G_EXCEPTION_ERROR;
1028 END IF;
1029 migrate(lx_irh_rec,l_def_irhv_rec);
1030
1031 --Set OUT Values
1032 x_irhv_rec:= l_def_irhv_rec;
1033 x_return_status := l_return_status;
1034 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1035
1036 EXCEPTION
1037 WHEN G_EXCEPTION_HALT_VALIDATION then
1038 -- No action necessary. Validation can continue to next attribute/column
1039 null;
1040
1041 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1042 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1043 (
1044 l_api_name,
1045 G_PKG_NAME,
1046 'OKL_API.G_RET_STS_ERROR',
1047 x_msg_count,
1048 x_msg_data,
1049 '_PVT'
1050 );
1051
1052 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1053 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1054 (
1055 l_api_name,
1056 G_PKG_NAME,
1057 'OKL_API.G_RET_STS_UNEXP_ERROR',
1058 x_msg_count,
1059 x_msg_data,
1060 '_PVT'
1061 );
1062
1063 WHEN OTHERS THEN
1064 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1065 (
1066 l_api_name,
1067 G_PKG_NAME,
1068 'OTHERS',
1069 x_msg_count,
1070 x_msg_data,
1071 '_PVT'
1072 );
1073 END insert_row;
1074 --------------------------------------------------------------------------------
1075 -- Procedure insert_row_tbl
1076 --------------------------------------------------------------------------------
1077 PROCEDURE insert_row(
1078 p_api_version IN NUMBER ,
1079 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1080 x_return_status OUT NOCOPY VARCHAR2,
1081 x_msg_count OUT NOCOPY NUMBER,
1082 x_msg_data OUT NOCOPY VARCHAR2,
1083 p_irhv_tbl IN okl_irhv_tbl,
1084 x_irhv_tbl OUT NOCOPY okl_irhv_tbl)IS
1085 l_api_version CONSTANT NUMBER:=1;
1086 l_api_name CONSTANT VARCHAR2(30):='v_insert_row';
1087 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1088 i NUMBER:=0;
1089 l_overall_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1090 BEGIN
1091 OKL_API.init_msg_list(p_init_msg_list);
1092 -- Make sure PL/SQL table has records in it before passing
1093 IF (p_irhv_tbl.COUNT > 0) THEN
1094 i := p_irhv_tbl.FIRST;
1095 LOOP
1096 insert_row (p_api_version => p_api_version,
1097 p_init_msg_list => OKL_API.G_FALSE,
1098 x_return_status => x_return_status,
1099 x_msg_count => x_msg_count,
1100 x_msg_data => x_msg_data,
1101 p_irhv_rec => p_irhv_tbl(i),
1102 x_irhv_rec => x_irhv_tbl(i));
1103 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1104 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1105 l_overall_status := x_return_status;
1106 END IF;
1107 END IF;
1108
1109 EXIT WHEN (i = p_irhv_tbl.LAST);
1110 i := p_irhv_tbl.NEXT(i);
1111 END LOOP;
1112 x_return_status := l_overall_status;
1113 END IF;
1114
1115 EXCEPTION
1116 WHEN G_EXCEPTION_HALT_VALIDATION then
1117 -- No action necessary. Validation can continue to next attribute/column
1118 null;
1119
1120 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1121 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1122 (
1123 l_api_name,
1124 G_PKG_NAME,
1125 'OKL_API.G_RET_STS_ERROR',
1126 x_msg_count,
1127 x_msg_data,
1128 '_PVT'
1129 );
1130
1131 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1132 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1133 (
1134 l_api_name,
1135 G_PKG_NAME,
1136 'OKL_API.G_RET_STS_UNEXP_ERROR',
1137 x_msg_count,
1138 x_msg_data,
1139 '_PVT'
1140 );
1141
1142 WHEN OTHERS THEN
1143 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1144 (
1145 l_api_name,
1146 G_PKG_NAME,
1147 'OTHERS',
1148 x_msg_count,
1149 x_msg_data,
1150 '_PVT'
1151 );
1152 END insert_row;
1153
1154
1155 -----------------
1156 -- lock_row (REC)
1157 -----------------
1158 PROCEDURE lock_row(
1159 p_api_version IN NUMBER,
1160 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1161 x_return_status OUT NOCOPY VARCHAR2,
1162 x_msg_count OUT NOCOPY NUMBER,
1163 x_msg_data OUT NOCOPY VARCHAR2,
1164 p_def_irh_rec IN okl_irh_rec) IS
1165
1166 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'lock_row (REC)';
1167
1168 E_Resource_Busy EXCEPTION;
1169
1170 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1171
1172 CURSOR lock_csr (p_def_irh_rec IN okl_irh_rec) IS
1173 SELECT OBJECT_VERSION_NUMBER
1174 FROM OKL_FE_ITEM_RESIDUAL_ALL
1175 WHERE ITEM_RESIDUAL_ID = p_def_irh_rec.item_residual_id
1176 AND OBJECT_VERSION_NUMBER = p_def_irh_rec.object_version_number
1177 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1178
1179 CURSOR lchk_csr (p_def_irh_rec IN okl_irh_rec) IS
1180 SELECT OBJECT_VERSION_NUMBER
1181 FROM OKL_FE_ITEM_RESIDUAL_ALL
1182 WHERE item_residual_id = p_def_irh_rec.item_residual_id;
1183
1184 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
1185 l_object_version_number OKL_FE_ITEM_RESIDUAL_ALL.OBJECT_VERSION_NUMBER%TYPE;
1186 lc_object_version_number OKL_FE_ITEM_RESIDUAL_ALL.OBJECT_VERSION_NUMBER%TYPE;
1187 l_row_notfound BOOLEAN := FALSE;
1188 lc_row_notfound BOOLEAN := FALSE;
1189
1190 BEGIN
1191
1192 BEGIN
1193 OPEN lock_csr(p_def_irh_rec);
1194 FETCH lock_csr INTO l_object_version_number;
1195 l_row_notfound := lock_csr%NOTFOUND;
1196 CLOSE lock_csr;
1197 EXCEPTION
1198 WHEN E_Resource_Busy THEN
1199 IF (lock_csr%ISOPEN) THEN
1200 CLOSE lock_csr;
1201 END IF;
1202 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1203 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1204 END;
1205
1206 IF ( l_row_notfound ) THEN
1207 OPEN lchk_csr(p_def_irh_rec);
1208 FETCH lchk_csr INTO lc_object_version_number;
1209 lc_row_notfound := lchk_csr%NOTFOUND;
1210 CLOSE lchk_csr;
1211 END IF;
1212 IF (lc_row_notfound) THEN
1213 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1214 RAISE OKL_API.G_EXCEPTION_ERROR;
1215 ELSIF lc_object_version_number > p_def_irh_rec.object_version_number THEN
1216 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1217 RAISE OKL_API.G_EXCEPTION_ERROR;
1218 ELSIF lc_object_version_number <> p_def_irh_rec.object_version_number THEN
1219 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1220 RAISE OKL_API.G_EXCEPTION_ERROR;
1221 ELSIF lc_object_version_number = -1 THEN
1222 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1223 RAISE OKL_API.G_EXCEPTION_ERROR;
1224 END IF;
1225
1226 x_return_status := l_return_status;
1227
1228 EXCEPTION
1229
1230 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1231
1232 x_return_status := G_RET_STS_ERROR;
1233
1234 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1235
1236 x_return_status := G_RET_STS_UNEXP_ERROR;
1237
1238 WHEN OTHERS THEN
1239
1240 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1241 p_msg_name => G_DB_ERROR,
1242 p_token1 => G_PROG_NAME_TOKEN,
1243 p_token1_value => l_api_name,
1244 p_token2 => G_SQLCODE_TOKEN,
1245 p_token2_value => sqlcode,
1246 p_token3 => G_SQLERRM_TOKEN,
1247 p_token3_value => sqlerrm);
1248
1249 x_return_status := G_RET_STS_UNEXP_ERROR;
1250
1251 END lock_row;
1252
1253
1254 -----------------
1255 -- lock_row (TBL)
1256 -----------------
1257 PROCEDURE lock_row(
1258 p_api_version IN NUMBER,
1259 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1260 x_return_status OUT NOCOPY VARCHAR2,
1261 x_msg_count OUT NOCOPY NUMBER,
1262 x_msg_data OUT NOCOPY VARCHAR2,
1263 okl_irh_tbl IN okl_irh_tbl) IS
1264
1265 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'lock_row (TBL)';
1266 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1267 i BINARY_INTEGER;
1268
1269 BEGIN
1270
1271 IF (okl_irh_tbl.COUNT > 0) THEN
1272
1273 i := okl_irh_tbl.FIRST;
1274
1275 LOOP
1276
1277 IF okl_irh_tbl.EXISTS(i) THEN
1278
1279 lock_row (p_api_version => G_API_VERSION,
1280 p_init_msg_list => G_FALSE,
1281 x_return_status => l_return_status,
1282 x_msg_count => x_msg_count,
1283 x_msg_data => x_msg_data,
1284 p_def_irh_rec => okl_irh_tbl(i));
1285
1286 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1287 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1288 ELSIF l_return_status = G_RET_STS_ERROR THEN
1289 RAISE OKL_API.G_EXCEPTION_ERROR;
1290 END IF;
1291
1292 EXIT WHEN (i = okl_irh_tbl.LAST);
1293 i := okl_irh_tbl.NEXT(i);
1294
1295 END IF;
1296
1297 END LOOP;
1298
1299 END IF;
1300
1301 x_return_status := l_return_status;
1302
1303 EXCEPTION
1304
1305 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1306
1307 x_return_status := G_RET_STS_ERROR;
1308
1309 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1310
1311 x_return_status := G_RET_STS_UNEXP_ERROR;
1312
1313 WHEN OTHERS THEN
1314
1315 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1316 p_msg_name => G_DB_ERROR,
1317 p_token1 => G_PROG_NAME_TOKEN,
1318 p_token1_value => l_api_name,
1319 p_token2 => G_SQLCODE_TOKEN,
1320 p_token2_value => sqlcode,
1321 p_token3 => G_SQLERRM_TOKEN,
1322 p_token3_value => sqlerrm);
1323
1324 x_return_status := G_RET_STS_UNEXP_ERROR;
1325
1326 END lock_row;
1327
1328 --------------------------------------------------------------------------------
1329 -- Procedure update_row
1330 --------------------------------------------------------------------------------
1331 PROCEDURE update_row(
1332 p_api_version IN NUMBER ,
1333 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1334 x_return_status OUT NOCOPY VARCHAR2,
1335 x_msg_count OUT NOCOPY NUMBER,
1336 x_msg_data OUT NOCOPY VARCHAR2,
1337 p_irh_rec IN okl_irh_rec,
1338 x_irh_rec OUT NOCOPY okl_irh_rec)IS
1339 l_api_version CONSTANT NUMBER:=1;
1340 l_api_name CONSTANT VARCHAR2(30):='update_row';
1341 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1342 l_def_irh_rec okl_irh_rec := p_irh_rec;
1343
1344 l_row_notfound BOOLEAN:=TRUE;
1345
1346 FUNCTION Set_Attributes(
1347 p_irh_rec IN okl_irh_rec,
1348 x_irh_rec OUT NOCOPY okl_irh_rec
1349 ) RETURN VARCHAR2 IS
1350 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1351 BEGIN
1352 x_irh_rec := p_irh_rec;
1353 RETURN (l_return_status);
1354 END Set_Attributes;
1355 BEGIN
1356 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1357 G_PKG_NAME,
1358 p_init_msg_list,
1359 l_api_version,
1360 p_api_version,
1361 '_PVT',
1362 x_return_status);
1363 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1364 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1365 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1366 RAISE OKL_API.G_EXCEPTION_ERROR;
1367 END IF;
1368
1369 --Setting Item Attributes
1370 l_return_status:=Set_Attributes(p_irh_rec,
1371 l_def_irh_rec);
1372 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1373 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1374 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1375 RAISE OKL_API.G_EXCEPTION_ERROR;
1376 END IF;
1377
1378 -- Lock the row before updating
1379 lock_row(p_api_version => G_API_VERSION,
1380 p_init_msg_list => G_FALSE,
1381 x_return_status => l_return_status,
1382 x_msg_count => x_msg_count,
1383 x_msg_data => x_msg_data,
1384 p_def_irh_rec => l_def_irh_rec);
1385
1386 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1387 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1388 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1389 RAISE OKL_API.G_EXCEPTION_ERROR;
1390 END IF;
1391
1392 UPDATE OKL_FE_ITEM_RESIDUAL_ALL
1393 SET
1394 ITEM_RESIDUAL_ID= l_def_irh_rec.ITEM_RESIDUAL_ID,
1395 ORIG_ITEM_RESIDUAL_ID = l_def_irh_rec.ORIG_ITEM_RESIDUAL_ID,
1396 OBJECT_VERSION_NUMBER= l_def_irh_rec.OBJECT_VERSION_NUMBER+1,
1397 CREATED_BY= l_def_irh_rec.CREATED_BY,
1398 CREATION_DATE= l_def_irh_rec.CREATION_DATE,
1399 LAST_UPDATED_BY= l_def_irh_rec.LAST_UPDATED_BY,
1400 LAST_UPDATE_DATE= l_def_irh_rec.LAST_UPDATE_DATE,
1401 LAST_UPDATE_LOGIN= l_def_irh_rec.LAST_UPDATE_LOGIN,
1402 INVENTORY_ITEM_ID= l_def_irh_rec.INVENTORY_ITEM_ID,
1403 ORGANIZATION_ID= l_def_irh_rec.ORGANIZATION_ID,
1404 CATEGORY_ID= l_def_irh_rec.CATEGORY_ID,
1405 CATEGORY_SET_ID= l_def_irh_rec.CATEGORY_SET_ID,
1406 RESI_CATEGORY_SET_ID= l_def_irh_rec.RESI_CATEGORY_SET_ID,
1407 CATEGORY_TYPE_CODE= l_def_irh_rec.CATEGORY_TYPE_CODE,
1408 RESIDUAL_TYPE_CODE= l_def_irh_rec.RESIDUAL_TYPE_CODE,
1409 CURRENCY_CODE= l_def_irh_rec.CURRENCY_CODE,
1410 STS_CODE= l_def_irh_rec.STS_CODE,
1411 EFFECTIVE_FROM_DATE= l_def_irh_rec.EFFECTIVE_FROM_DATE,
1412 EFFECTIVE_TO_DATE= l_def_irh_rec.EFFECTIVE_TO_DATE,
1413 ORG_ID= l_def_irh_rec.ORG_ID
1414 WHERE ITEM_RESIDUAL_ID = l_def_irh_rec.ITEM_RESIDUAL_ID;
1415 --Set OUT Values
1416 x_irh_rec:=l_def_irh_rec;
1417 x_return_status := l_return_status;
1418 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1419
1420 EXCEPTION
1421 WHEN G_EXCEPTION_HALT_VALIDATION then
1422 -- No action necessary. Validation can continue to next attribute/column
1423 null;
1424
1425 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1426 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1427 (
1428 l_api_name,
1429 G_PKG_NAME,
1430 'OKL_API.G_RET_STS_ERROR',
1431 x_msg_count,
1432 x_msg_data,
1433 '_PVT'
1434 );
1435
1436 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1437 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1438 (
1439 l_api_name,
1440 G_PKG_NAME,
1441 'OKL_API.G_RET_STS_UNEXP_ERROR',
1442 x_msg_count,
1443 x_msg_data,
1444 '_PVT'
1445 );
1446
1447 WHEN OTHERS THEN
1448 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1449 (
1450 l_api_name,
1451 G_PKG_NAME,
1452 'OTHERS',
1453 x_msg_count,
1454 x_msg_data,
1455 '_PVT'
1456 );
1457 END update_row;
1458 --------------------------------------------------------------------------------
1459 -- Procedure update_row_v
1460 --------------------------------------------------------------------------------
1461 PROCEDURE update_row(
1462 p_api_version IN NUMBER ,
1463 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1464 x_return_status OUT NOCOPY VARCHAR2,
1465 x_msg_count OUT NOCOPY NUMBER,
1466 x_msg_data OUT NOCOPY VARCHAR2,
1467 p_irhv_rec IN okl_irhv_rec,
1468 x_irhv_rec OUT NOCOPY okl_irhv_rec)IS
1469 l_api_version CONSTANT NUMBER:=1;
1470 l_api_name CONSTANT VARCHAR2(30):='v_insert_row';
1471 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1472 l_irhv_rec okl_irhv_rec:=p_irhv_rec;
1473 l_def_irhv_rec okl_irhv_rec;
1474 lx_def_irhv_rec okl_irhv_rec;
1475 l_irhrec okl_irh_rec;
1476 lx_irh_rec okl_irh_rec;
1477
1478 FUNCTION fill_who_columns(
1479 p_irhv_rec IN okl_irhv_rec
1480 )RETURN okl_irhv_rec IS
1481 l_irhv_rec okl_irhv_rec:=p_irhv_rec;
1482 BEGIN
1483 l_irhv_rec .LAST_UPDATE_DATE := SYSDATE;
1484 l_irhv_rec .LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1485 l_irhv_rec .LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1486 RETURN (l_irhv_rec );
1487 END fill_who_columns;
1488 FUNCTION populate_new_record(
1489 p_irhv_rec IN okl_irhv_rec,
1490 x_irhv_rec OUT NOCOPY okl_irhv_rec
1491 )RETURN VARCHAR2 is
1492 l_irhv_rec okl_irhv_rec;
1493 l_row_notfound BOOLEAN:=TRUE;
1494 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1495 BEGIN
1496
1497 x_irhv_rec := p_irhv_rec;
1498 --Get current database values
1499 l_irhv_rec := get_rec(p_irhv_rec,l_row_notfound);
1500 IF(l_row_notfound) THEN
1501 l_return_status:= OKL_API.G_RET_STS_UNEXP_ERROR;
1502 END IF;
1503 IF (x_irhv_rec.ITEM_RESIDUAL_ID IS NULL)
1504 THEN
1505 x_irhv_rec.ITEM_RESIDUAL_ID:=l_irhv_rec.ITEM_RESIDUAL_ID;
1506 END IF;
1507
1508 IF (x_irhv_rec.ORIG_ITEM_RESIDUAL_ID IS NULL)
1509 THEN
1510 x_irhv_rec.ORIG_ITEM_RESIDUAL_ID :=l_irhv_rec.ORIG_ITEM_RESIDUAL_ID;
1511 END IF;
1512
1513 IF (x_irhv_rec.OBJECT_VERSION_NUMBER IS NULL)
1514 THEN
1515 x_irhv_rec.OBJECT_VERSION_NUMBER:=l_irhv_rec.OBJECT_VERSION_NUMBER;
1516 END IF;
1517
1518 IF (x_irhv_rec.CREATED_BY IS NULL)
1519 THEN
1520 x_irhv_rec.CREATED_BY:=l_irhv_rec.CREATED_BY;
1521 END IF;
1522 IF (x_irhv_rec.CREATION_DATE IS NULL)
1523 THEN
1524 x_irhv_rec.CREATION_DATE:=l_irhv_rec.CREATION_DATE;
1525 END IF;
1526 IF (x_irhv_rec.LAST_UPDATED_BY IS NULL)
1527 THEN
1528 x_irhv_rec.LAST_UPDATED_BY:=l_irhv_rec.LAST_UPDATED_BY;
1529 END IF;
1530 IF (x_irhv_rec.LAST_UPDATE_DATE IS NULL)
1531 THEN
1532 x_irhv_rec.LAST_UPDATE_DATE:=l_irhv_rec.LAST_UPDATE_DATE;
1533 END IF;
1534 IF (x_irhv_rec.LAST_UPDATE_LOGIN IS NULL)
1535 THEN
1536 x_irhv_rec.LAST_UPDATE_LOGIN:=l_irhv_rec.LAST_UPDATE_LOGIN;
1537 END IF;
1538 IF (x_irhv_rec.INVENTORY_ITEM_ID IS NULL)
1539 THEN
1540 x_irhv_rec.INVENTORY_ITEM_ID:=l_irhv_rec.INVENTORY_ITEM_ID;
1541 END IF;
1542 IF (x_irhv_rec.ORGANIZATION_ID IS NULL)
1543 THEN
1544 x_irhv_rec.ORGANIZATION_ID:=l_irhv_rec.ORGANIZATION_ID;
1545 END IF;
1546 IF (x_irhv_rec.CATEGORY_ID IS NULL)
1547 THEN
1548 x_irhv_rec.CATEGORY_ID:=l_irhv_rec.CATEGORY_ID;
1549 END IF;
1550 IF (x_irhv_rec.CATEGORY_SET_ID IS NULL)
1551 THEN
1552 x_irhv_rec.CATEGORY_SET_ID:=l_irhv_rec.CATEGORY_SET_ID;
1553 END IF;
1554 IF (x_irhv_rec.RESI_CATEGORY_SET_ID IS NULL)
1555 THEN
1556 x_irhv_rec.RESI_CATEGORY_SET_ID:=l_irhv_rec.RESI_CATEGORY_SET_ID;
1557 END IF;
1558 IF (x_irhv_rec.CATEGORY_TYPE_CODE IS NULL)
1559 THEN
1560 x_irhv_rec.CATEGORY_TYPE_CODE:=l_irhv_rec.CATEGORY_TYPE_CODE;
1561 END IF;
1562 IF (x_irhv_rec.RESIDUAL_TYPE_CODE IS NULL)
1563 THEN
1564 x_irhv_rec.RESIDUAL_TYPE_CODE:=l_irhv_rec.RESIDUAL_TYPE_CODE;
1565 END IF;
1566 IF (x_irhv_rec.CURRENCY_CODE IS NULL)
1567 THEN
1568 x_irhv_rec.CURRENCY_CODE:=l_irhv_rec.CURRENCY_CODE;
1569 END IF;
1570 IF (x_irhv_rec.STS_CODE IS NULL)
1571 THEN
1572 x_irhv_rec.STS_CODE:=l_irhv_rec.STS_CODE;
1573 END IF;
1574 IF (x_irhv_rec.EFFECTIVE_FROM_DATE IS NULL)
1575 THEN
1576 x_irhv_rec.EFFECTIVE_FROM_DATE:=l_irhv_rec.EFFECTIVE_FROM_DATE;
1577 END IF;
1578 IF (x_irhv_rec.EFFECTIVE_TO_DATE IS NULL)
1579 THEN
1580 x_irhv_rec.EFFECTIVE_TO_DATE:=l_irhv_rec.EFFECTIVE_TO_DATE;
1581 END IF;
1582 IF (x_irhv_rec.ORG_ID IS NULL)
1583 THEN
1584 x_irhv_rec.ORG_ID:=l_irhv_rec.ORG_ID;
1585 END IF;
1586 RETURN(l_return_status);
1587 END populate_new_record;
1588
1589 FUNCTION Set_Attributes(
1590 p_irhv_rec IN okl_irhv_rec,
1591 x_irhv_rec OUT NOCOPY okl_irhv_rec
1592 ) RETURN VARCHAR2 IS
1593 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1594 BEGIN
1595 x_irhv_rec := p_irhv_rec;
1596 RETURN (l_return_status);
1597 END Set_Attributes;
1598 BEGIN
1599 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1600 G_PKG_NAME,
1601 p_init_msg_list,
1602 l_api_version,
1603 p_api_version,
1604 '_PVT',
1605 x_return_status);
1606 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1607 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1608 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1609 RAISE OKL_API.G_EXCEPTION_ERROR;
1610 END IF;
1611 --Setting Item Attributes
1612 l_return_status:=Set_Attributes(l_irhv_rec,l_def_irhv_rec);
1613 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1614 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1615 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1616 RAISE OKL_API.G_EXCEPTION_ERROR;
1617 END IF;
1618
1619 l_return_status := populate_new_record(l_def_irhv_rec,lx_def_irhv_rec);
1620 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1621 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1622 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1623 RAISE OKL_API.G_EXCEPTION_ERROR;
1624 END IF;
1625
1626 lx_def_irhv_rec := null_out_defaults(lx_def_irhv_rec);
1627
1628 lx_def_irhv_rec := fill_who_columns(lx_def_irhv_rec);
1629
1630 l_return_status := Validate_Attributes(lx_def_irhv_rec);
1631 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1632 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1633 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1634 RAISE OKL_API.G_EXCEPTION_ERROR;
1635 END IF;
1636 l_return_status := Validate_Record(lx_def_irhv_rec);
1637 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1638 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1639 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1640 RAISE OKL_API.G_EXCEPTION_ERROR;
1641 END IF;
1642 migrate(lx_def_irhv_rec,l_irhrec);
1643 update_row(
1644 p_api_version,
1645 p_init_msg_list,
1646 l_return_status,
1647 x_msg_count,
1648 x_msg_data,
1649 l_irhrec,
1650 lx_irh_rec);
1651 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1652 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1653 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1654 RAISE OKL_API.G_EXCEPTION_ERROR;
1655 END IF;
1656
1657 migrate(lx_irh_rec,lx_def_irhv_rec);
1658 --Set OUT Values
1659 x_irhv_rec:= lx_def_irhv_rec;
1660 x_return_status := l_return_status;
1661 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1662
1663 EXCEPTION
1664 WHEN G_EXCEPTION_HALT_VALIDATION then
1665 -- No action necessary. Validation can continue to next attribute/column
1666 null;
1667
1668 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1669 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1670 (
1671 l_api_name,
1672 G_PKG_NAME,
1673 'OKL_API.G_RET_STS_ERROR',
1674 x_msg_count,
1675 x_msg_data,
1676 '_PVT'
1677 );
1678
1679 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1680 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1681 (
1682 l_api_name,
1683 G_PKG_NAME,
1684 'OKL_API.G_RET_STS_UNEXP_ERROR',
1685 x_msg_count,
1686 x_msg_data,
1687 '_PVT'
1688 );
1689
1690 WHEN OTHERS THEN
1691 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1692 (
1693 l_api_name,
1694 G_PKG_NAME,
1695 'OTHERS',
1696 x_msg_count,
1697 x_msg_data,
1698 '_PVT'
1699 );
1700 END update_row;
1701 --------------------------------------------------------------------------------
1702 -- Procedure insert_row_tbl
1703 --------------------------------------------------------------------------------
1704 PROCEDURE update_row(
1705 p_api_version IN NUMBER ,
1706 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1707 x_return_status OUT NOCOPY VARCHAR2,
1708 x_msg_count OUT NOCOPY NUMBER,
1709 x_msg_data OUT NOCOPY VARCHAR2,
1710 p_irhv_tbl IN okl_irhv_tbl,
1711 x_irhv_tbl OUT NOCOPY okl_irhv_tbl)IS
1712 l_api_version CONSTANT NUMBER:=1;
1713 l_api_name CONSTANT VARCHAR2(30):='v_update_row';
1714 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1715 i NUMBER:=0;
1716 l_overall_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1717 BEGIN
1718 OKL_API.init_msg_list(p_init_msg_list);
1719 -- Make sure PL/SQL table has records in it before passing
1720 IF (p_irhv_tbl.COUNT > 0) THEN
1721 i := p_irhv_tbl.FIRST;
1722 LOOP
1723 update_row (p_api_version => p_api_version,
1724 p_init_msg_list => OKL_API.G_FALSE,
1725 x_return_status => x_return_status,
1726 x_msg_count => x_msg_count,
1727 x_msg_data => x_msg_data,
1728 p_irhv_rec => p_irhv_tbl(i),
1729 x_irhv_rec => x_irhv_tbl(i));
1730 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1731 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1732 l_overall_status := x_return_status;
1733 END IF;
1734 END IF;
1735
1736 EXIT WHEN (i = p_irhv_tbl.LAST);
1737 i := p_irhv_tbl.NEXT(i);
1738 END LOOP;
1739 x_return_status := l_overall_status;
1740 END IF;
1741
1742 EXCEPTION
1743 WHEN G_EXCEPTION_HALT_VALIDATION then
1744 -- No action necessary. Validation can continue to next attribute/column
1745 null;
1746
1747 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1748 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1749 (
1750 l_api_name,
1751 G_PKG_NAME,
1752 'OKL_API.G_RET_STS_ERROR',
1753 x_msg_count,
1754 x_msg_data,
1755 '_PVT'
1756 );
1757
1758 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1759 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1760 (
1761 l_api_name,
1762 G_PKG_NAME,
1763 'OKL_API.G_RET_STS_UNEXP_ERROR',
1764 x_msg_count,
1765 x_msg_data,
1766 '_PVT'
1767 );
1768
1769 WHEN OTHERS THEN
1770 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1771 (
1772 l_api_name,
1773 G_PKG_NAME,
1774 'OTHERS',
1775 x_msg_count,
1776 x_msg_data,
1777 '_PVT'
1778 );
1779 END update_row;
1780 --------------------------------------------------------------------------------
1781 -- Procedure delete_row
1782 --------------------------------------------------------------------------------
1783 PROCEDURE delete_row(
1784 p_api_version IN NUMBER ,
1785 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1786 x_return_status OUT NOCOPY VARCHAR2,
1787 x_msg_count OUT NOCOPY NUMBER,
1788 x_msg_data OUT NOCOPY VARCHAR2,
1789 p_irh_rec IN okl_irh_rec)IS
1790 l_api_version CONSTANT NUMBER:=1;
1791 l_api_name CONSTANT VARCHAR2(30):='delete_row';
1792 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1793 l_irh_rec okl_irh_rec := p_irh_rec;
1794 l_row_notfound BOOLEAN:=TRUE;
1795
1796 BEGIN
1797 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1798 G_PKG_NAME,
1799 p_init_msg_list,
1800 l_api_version,
1801 p_api_version,
1802 '_PVT',
1803 x_return_status);
1804 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1805 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1806 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1807 RAISE OKL_API.G_EXCEPTION_ERROR;
1808 END IF;
1809
1810 DELETE FROM OKL_FE_ITEM_RESIDUAL_ALL
1811 WHERE ITEM_RESIDUAL_ID=l_irh_rec.item_residual_id;
1812
1813 EXCEPTION
1814 WHEN G_EXCEPTION_HALT_VALIDATION then
1815 -- No action necessary. Validation can continue to next attribute/column
1816 null;
1817
1818 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1819 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1820 (
1821 l_api_name,
1822 G_PKG_NAME,
1823 'OKL_API.G_RET_STS_ERROR',
1824 x_msg_count,
1825 x_msg_data,
1826 '_PVT'
1827 );
1828
1829 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1830 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1831 (
1832 l_api_name,
1833 G_PKG_NAME,
1834 'OKL_API.G_RET_STS_UNEXP_ERROR',
1835 x_msg_count,
1836 x_msg_data,
1837 '_PVT'
1838 );
1839
1840 WHEN OTHERS THEN
1841 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1842 (
1843 l_api_name,
1844 G_PKG_NAME,
1845 'OTHERS',
1846 x_msg_count,
1847 x_msg_data,
1848 '_PVT'
1849 );
1850 END delete_row;
1851 --------------------------------------------------------------------------------
1852 -- Procedure delete_row_v
1853 --------------------------------------------------------------------------------
1854 PROCEDURE delete_row(
1855 p_api_version IN NUMBER ,
1856 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1857 x_return_status OUT NOCOPY VARCHAR2,
1858 x_msg_count OUT NOCOPY NUMBER,
1859 x_msg_data OUT NOCOPY VARCHAR2,
1860 p_irhv_rec IN okl_irhv_rec)IS
1861 l_api_version CONSTANT NUMBER:=1;
1862 l_api_name CONSTANT VARCHAR2(30):='v_delete_row';
1863 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1864 l_irhv_rec okl_irhv_rec:=p_irhv_rec;
1865 l_irhrec okl_irh_rec;
1866
1867 BEGIN
1868 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1869 G_PKG_NAME,
1870 p_init_msg_list,
1871 l_api_version,
1872 p_api_version,
1873 '_PVT',
1874 x_return_status);
1875 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1876 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1877 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1878 RAISE OKL_API.G_EXCEPTION_ERROR;
1879 END IF;
1880
1881 migrate(l_irhv_rec,l_irhrec);
1882 delete_row(
1883 p_api_version,
1884 p_init_msg_list,
1885 x_return_status,
1886 x_msg_count,
1887 x_msg_data,
1888 l_irhrec);
1889 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1890 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1891 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1892 RAISE OKL_API.G_EXCEPTION_ERROR;
1893 END IF;
1894 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1895
1896 EXCEPTION
1897 WHEN G_EXCEPTION_HALT_VALIDATION then
1898 -- No action necessary. Validation can continue to next attribute/column
1899 null;
1900
1901 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1902 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1903 (
1904 l_api_name,
1905 G_PKG_NAME,
1906 'OKL_API.G_RET_STS_ERROR',
1907 x_msg_count,
1908 x_msg_data,
1909 '_PVT'
1910 );
1911
1912 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1913 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1914 (
1915 l_api_name,
1916 G_PKG_NAME,
1917 'OKL_API.G_RET_STS_UNEXP_ERROR',
1918 x_msg_count,
1919 x_msg_data,
1920 '_PVT'
1921 );
1922
1923 WHEN OTHERS THEN
1924 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1925 (
1926 l_api_name,
1927 G_PKG_NAME,
1928 'OTHERS',
1929 x_msg_count,
1930 x_msg_data,
1931 '_PVT'
1932 );
1933 END delete_row;
1934 --------------------------------------------------------------------------------
1935 -- Procedure delete_row_tbl
1936 --------------------------------------------------------------------------------
1937 PROCEDURE delete_row(
1938 p_api_version IN NUMBER ,
1939 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1940 x_return_status OUT NOCOPY VARCHAR2,
1941 x_msg_count OUT NOCOPY NUMBER,
1942 x_msg_data OUT NOCOPY VARCHAR2,
1943 p_irhv_tbl IN okl_irhv_tbl)IS
1944 l_api_version CONSTANT NUMBER:=1;
1945 l_api_name CONSTANT VARCHAR2(30):='v_delete_row';
1946 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1947 i NUMBER:=0;
1948 l_overall_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1949 BEGIN
1950 OKL_API.init_msg_list(p_init_msg_list);
1951 -- Make sure PL/SQL table has records in it before passing
1952 IF (p_irhv_tbl.COUNT > 0) THEN
1953 i := p_irhv_tbl.FIRST;
1954 LOOP
1955 delete_row (p_api_version => p_api_version,
1956 p_init_msg_list => OKL_API.G_FALSE,
1957 x_return_status => x_return_status,
1958 x_msg_count => x_msg_count,
1959 x_msg_data => x_msg_data,
1960 p_irhv_rec => p_irhv_tbl(i));
1961 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1962 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1963 l_overall_status := x_return_status;
1964 END IF;
1965 END IF;
1966
1967 EXIT WHEN (i = p_irhv_tbl.LAST);
1968 i := p_irhv_tbl.NEXT(i);
1969 END LOOP;
1970 x_return_status := l_overall_status;
1971 END IF;
1972
1973 EXCEPTION
1974 WHEN G_EXCEPTION_HALT_VALIDATION then
1975 -- No action necessary. Validation can continue to next attribute/column
1976 null;
1977
1978 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1979 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1980 (
1981 l_api_name,
1982 G_PKG_NAME,
1983 'OKL_API.G_RET_STS_ERROR',
1984 x_msg_count,
1985 x_msg_data,
1986 '_PVT'
1987 );
1988
1989 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1990 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1991 (
1992 l_api_name,
1993 G_PKG_NAME,
1994 'OKL_API.G_RET_STS_UNEXP_ERROR',
1995 x_msg_count,
1996 x_msg_data,
1997 '_PVT'
1998 );
1999
2000 WHEN OTHERS THEN
2001 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2002 (
2003 l_api_name,
2004 G_PKG_NAME,
2005 'OTHERS',
2006 x_msg_count,
2007 x_msg_data,
2008 '_PVT'
2009 );
2010 END delete_row;
2011 END OKL_IRH_PVT;