[Home] [Help]
PACKAGE BODY: APPS.OKL_VIB_PVT
Source
1 PACKAGE BODY OKL_VIB_PVT AS
2 /* $Header: OKLSVIBB.pls 120.2 2006/11/13 07:38:09 dpsingh noship $ */
3 /************************ HAND-CODED *********************************/
4 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
5 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_API.G_CHILD_TABLE_TOKEN';
6 G_FIN_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FREE_FORM1';
7 G_MODEL_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'ITEM';
8 G_ADDON_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'ADD_ITEM';
9 G_FA_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FIXED_ASSET';
10 G_INST_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FREE_FORM2';
11 G_IB_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'INST_ITEM';
12 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
13 ---------------------------------------------------------------------------------
14 -- Start of Commnets
15 -- Badrinath Kuchibholta
16 -- Procedure Name : Validate_dnz_chr_id
17 -- Description : FK validation with OKL_K_HEADERS_V
18 -- Business Rules :
19 -- Parameters : OUT Return Status, IN Rec Info
20 -- Version : 1.0
21 -- End of Commnets
22
23 PROCEDURE validate_dnz_chr_id(x_return_status OUT NOCOPY VARCHAR2,
24 p_dnz_chr_id IN OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
25
26 ln_dummy number := 0;
27
28 CURSOR c_dnz_chr_id_validate(p_dnz_chr_id IN OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
29 SELECT 1
30 FROM DUAL
31 WHERE EXISTS (SELECT id
32 FROM okc_k_headers_b
33 WHERE id = p_dnz_chr_id);
34 BEGIN
35 -- initialize return status
36 x_return_status := OKL_API.G_RET_STS_SUCCESS;
37 -- data is required
38 IF (p_dnz_chr_id = OKL_API.G_MISS_NUM) OR
39 (p_dnz_chr_id IS NULL) THEN
40 -- halt validation as it is a required field
41 RAISE G_EXCEPTION_STOP_VALIDATION;
42 END IF;
43 OPEN c_dnz_chr_id_validate(p_dnz_chr_id);
44 IF c_dnz_chr_id_validate%NOTFOUND THEN
45 -- halt validation as it has no parent record
46 RAISE G_EXCEPTION_HALT_VALIDATION;
47 END IF;
48 FETCH c_dnz_chr_id_validate into ln_dummy;
49 CLOSE c_dnz_chr_id_validate;
50 IF (ln_dummy = 0) then
51 -- halt validation as it has no parent record
52 RAISE G_EXCEPTION_HALT_VALIDATION;
53 END IF;
54 EXCEPTION
55 WHEN G_EXCEPTION_STOP_VALIDATION then
56 -- We are here since the field is required
57 -- store SQL error message on message stack
58 OKL_API.set_message(p_app_name => G_APP_NAME,
59 p_msg_name => G_REQUIRED_VALUE,
60 p_token1 => G_COL_NAME_TOKEN,
61 p_token1_value => 'dnz_chr_id');
62 -- Notify Error
63 x_return_status := OKL_API.G_RET_STS_ERROR;
64 WHEN G_EXCEPTION_HALT_VALIDATION then
65 -- We are here b'cause we have no parent record
66 -- store SQL error message on message stack
67 OKL_API.set_message(p_app_name => G_APP_NAME,
68 p_msg_name => G_NO_MATCHING_RECORD,
69 p_token1 => G_COL_NAME_TOKEN,
70 p_token1_value => 'dnz_chr_id');
71 -- If the cursor is open then it has to be closed
72 IF c_dnz_chr_id_validate%ISOPEN THEN
73 CLOSE c_dnz_chr_id_validate;
74 END IF;
75 -- notify caller of an error
76 x_return_status := OKL_API.G_RET_STS_ERROR;
77 WHEN OTHERS THEN
78 -- store SQL error message on message stack
79 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
80 p_msg_name => G_UNEXPECTED_ERROR,
81 p_token1 => G_SQLCODE_TOKEN,
82 p_token1_value => SQLCODE,
83 p_token2 => G_SQLERRM_TOKEN,
84 p_token2_value => SQLERRM);
85 -- If the cursor is open then it has to be closed
86 IF c_dnz_chr_id_validate%ISOPEN THEN
87 CLOSE c_dnz_chr_id_validate;
88 END IF;
89 -- notify caller of an error as UNEXPETED error
90 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
91 END validate_dnz_chr_id;
92 ---------------------------------------------------------------------------------
93 -- Start of Commnets
94 -- Badrinath Kuchibholta
95 -- Procedure Name : Validate_ib_cle_id
96 -- Description : FK validation with OKL_K_LINES_V
97 -- Business Rules :
98 -- Parameters : OUT Return Status, IN Rec Info
99 -- Version : 1.0
100 -- End of Commnets
101
102 PROCEDURE validate_ib_cle_id(x_return_status OUT NOCOPY VARCHAR2,
103 p_id IN OKC_K_LINES_V.ID%TYPE) IS
104
105 ln_dummy number := 0;
106 l_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE;
107
108 CURSOR get_lty_code(p_cle_id IN OKC_K_LINES_V.ID%TYPE) IS
109 SELECT lse.lty_code
110 FROM okc_k_lines_b cle,
111 okc_line_styles_b lse
112 WHERE cle.id = p_cle_id
113 AND cle.lse_id = lse.id;
114
115
116 CURSOR c_cle_id_validate1(p_cle_id IN OKC_K_LINES_V.ID%TYPE,
117 p_code IN OKC_LINE_STYLES_V.LTY_CODE%TYPE,
118 p_code2 IN OKC_LINE_STYLES_V.LTY_CODE%TYPE) IS
119 SELECT 1
120 FROM DUAL
121 WHERE EXISTS (SELECT t1.id
122 FROM okc_line_styles_b t1
123 ,okc_line_styles_b t2
124 ,okc_line_styles_b t3
125 ,okc_subclass_top_line t4
126 ,okc_k_lines_b cle
127 WHERE t1.lty_code = p_code
128 AND cle.id = p_cle_id
129 AND cle.lse_id = t1.id
130 AND t2.lty_code = p_code2
131 AND t1.lse_parent_id = t2.id
132 AND t2.lse_parent_id = t3.id
133 AND t3.lty_code = G_FIN_LINE_LTY_CODE
134 AND t4.lse_id = t3.id
135 AND t4.scs_code = 'LEASE');
136 BEGIN
137 -- initialize return status
138 x_return_status := OKL_API.G_RET_STS_SUCCESS;
139 -- data is required
140 IF (p_id = OKL_API.G_MISS_NUM) OR
141 (p_id IS NULL) THEN
142 -- halt validation as it is a required field
143 RAISE G_EXCEPTION_STOP_VALIDATION;
144 END IF;
145
146 OPEN get_lty_code(p_id);
147 IF get_lty_code%NOTFOUND THEN
148 -- halt validation as it has no parent record
149 RAISE G_EXCEPTION_HALT_VALIDATION;
150 END IF;
151 FETCH get_lty_code into l_lty_code;
152 CLOSE get_lty_code;
153
154 IF l_lty_code = G_IB_LINE_LTY_CODE THEN
155 OPEN c_cle_id_validate1(p_id,
156 l_lty_code,
157 G_INST_LINE_LTY_CODE);
158 IF c_cle_id_validate1%NOTFOUND THEN
159 -- halt validation as it has no parent record
160 RAISE G_EXCEPTION_HALT_VALIDATION;
161 END IF;
162 FETCH c_cle_id_validate1 into ln_dummy;
163 CLOSE c_cle_id_validate1;
164 IF (ln_dummy = 0) then
165 -- halt validation as it has no parent record
166 RAISE G_EXCEPTION_HALT_VALIDATION;
167 END IF;
168 ELSE
169 -- halt validation as it has no parent record
170 RAISE G_EXCEPTION_HALT_VALIDATION;
171 END IF;
172 EXCEPTION
173 WHEN G_EXCEPTION_STOP_VALIDATION then
174 -- We are here since the field is required
175 -- store SQL error message on message stack
176 OKL_API.set_message(p_app_name => G_APP_NAME,
177 p_msg_name => G_REQUIRED_VALUE,
178 p_token1 => G_COL_NAME_TOKEN,
179 p_token1_value => 'ib_cle_id');
180 -- Notify Error
181 x_return_status := OKL_API.G_RET_STS_ERROR;
182 WHEN G_EXCEPTION_HALT_VALIDATION then
183 -- We are here b'cause we have no parent record
184 -- store SQL error message on message stack
185 OKL_API.set_message(p_app_name => G_APP_NAME,
186 p_msg_name => G_NO_MATCHING_RECORD,
187 p_token1 => G_COL_NAME_TOKEN,
188 p_token1_value => 'ib_cle_id');
189 -- If the cursor is open then it has to be closed
190 IF get_lty_code%ISOPEN THEN
191 CLOSE get_lty_code;
192 END IF;
193 IF c_cle_id_validate1%ISOPEN THEN
194 CLOSE c_cle_id_validate1;
195 END IF;
196 -- notify caller of an error
197 x_return_status := OKL_API.G_RET_STS_ERROR;
198 WHEN OTHERS THEN
199 -- store SQL error message on message stack
200 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
201 p_msg_name => G_UNEXPECTED_ERROR,
202 p_token1 => G_SQLCODE_TOKEN,
203 p_token1_value => SQLCODE,
204 p_token2 => G_SQLERRM_TOKEN,
205 p_token2_value => SQLERRM);
206 -- If the cursor is open then it has to be closed
207 IF get_lty_code%ISOPEN THEN
208 CLOSE get_lty_code;
209 END IF;
210 IF c_cle_id_validate1%ISOPEN THEN
211 CLOSE c_cle_id_validate1;
212 END IF;
213 -- notify caller of an error as UNEXPETED error
214 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
215 END validate_ib_cle_id;
216
217 -- Added by dpsingh
218 ---------------------------------------------------------------------------
219 -- PROCEDURE Validate_LE_Id
220 ---------------------------------------------------------------------------
221 -- Start of comments
222 --
223 -- Procedure Name : Validate_LE_Id
224 -- Description :
225 -- Business Rules :
226 -- Parameters :
227 -- Version : 1.0
228 -- End of comments
229 ---------------------------------------------------------------------------
230 PROCEDURE Validate_LE_Id(p_legal_entity_id IN NUMBER
231 ,x_return_status OUT NOCOPY VARCHAR2)
232 IS
233 l_exists NUMBER(1);
234 item_not_found_error EXCEPTION;
235 BEGIN
236
237 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
238 IF (p_legal_entity_id = OKL_API.G_MISS_NUM OR
239 p_legal_entity_id IS NULL)
240 THEN
241 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'legal_entity_id');
242 x_return_status := OKL_API.G_RET_STS_ERROR;
243 ELSIF (p_legal_entity_id IS NOT NULL) AND
244 (p_legal_entity_id <> Okl_Api.G_MISS_NUM) THEN
245 l_exists := OKL_LEGAL_ENTITY_UTIL.check_le_id_exists(p_legal_entity_id) ;
246 IF (l_exists<>1) THEN
247 Okl_Api.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'LEGAL_ENTITY_ID');
248 RAISE item_not_found_error;
249 END IF;
250 END IF;
251
252 EXCEPTION
253 WHEN item_not_found_error THEN
254 x_return_status := Okc_Api.G_RET_STS_ERROR;
255
256 WHEN OTHERS THEN
257 -- store SQL error message on message stack for caller
258 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
259 ,p_msg_name => g_unexpected_error
260 ,p_token1 => g_sqlcode_token
261 ,p_token1_value => SQLCODE
262 ,p_token2 => g_sqlerrm_token
263 ,p_token2_value => SQLERRM);
264
265 -- notify caller of an UNEXPECTED error
266 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
267
268 END Validate_LE_Id;
269
270 ------------------------------------------------------------------------------------------------
271 ---------------------------------------------------------------------------
272 -- FUNCTION get_seq_id
273 ---------------------------------------------------------------------------
274 FUNCTION get_seq_id RETURN NUMBER IS
275 BEGIN
276 RETURN(okc_p_util.raw_to_number(sys_guid()));
277 END get_seq_id;
278
279 ---------------------------------------------------------------------------
280 -- PROCEDURE qc
281 ---------------------------------------------------------------------------
282 PROCEDURE qc IS
283 BEGIN
284 null;
285 END qc;
286
287 ---------------------------------------------------------------------------
288 -- PROCEDURE change_version
289 ---------------------------------------------------------------------------
290 PROCEDURE change_version IS
291 BEGIN
292 null;
293 END change_version;
294
295 ---------------------------------------------------------------------------
296 -- PROCEDURE api_copy
297 ---------------------------------------------------------------------------
298 PROCEDURE api_copy IS
299 BEGIN
300 null;
301 END api_copy;
302
303 ---------------------------------------------------------------------------
304 -- FUNCTION get_rec for: OKL_CONTRACT_IB_HV
305 ---------------------------------------------------------------------------
306 FUNCTION get_rec (
307 p_vibv_rec IN vibv_rec_type,
308 x_no_data_found OUT NOCOPY BOOLEAN
309 ) RETURN vibv_rec_type IS
310 CURSOR okl_contract_ib_hv_pk_csr(p_id IN NUMBER) IS
311 SELECT ID,
312 MAJOR_VERSION,
313 OBJECT_VERSION_NUMBER,
314 DNZ_CHR_ID,
315 IB_CLE_ID,
316 NAME,
317 DESCRIPTION,
318 INVENTORY_ITEM_ID,
319 CURRENT_SERIAL_NUMBER,
320 INSTALL_SITE_USE_ID,
321 QUANTITY,
322 ATTRIBUTE_CATEGORY,
323 ATTRIBUTE1,
324 ATTRIBUTE2,
325 ATTRIBUTE3,
326 ATTRIBUTE4,
327 ATTRIBUTE5,
328 ATTRIBUTE6,
329 ATTRIBUTE7,
330 ATTRIBUTE8,
331 ATTRIBUTE9,
332 ATTRIBUTE10,
333 ATTRIBUTE11,
334 ATTRIBUTE12,
335 ATTRIBUTE13,
336 ATTRIBUTE14,
337 ATTRIBUTE15,
338 CREATED_BY,
339 CREATION_DATE,
340 LAST_UPDATED_BY,
341 LAST_UPDATE_DATE,
342 LAST_UPDATE_LOGIN,
343 --Added by dpsingh for LE uptake
344 LEGAL_ENTITY_ID
345 FROM Okl_Contract_Ib_Hv
346 WHERE Okl_Contract_Ib_Hv.ID = p_id;
347
348 l_vibv_rec vibv_rec_type;
349 BEGIN
350 x_no_data_found := TRUE;
351 -- Get current database values
352 OPEN okl_contract_ib_hv_pk_csr (p_vibv_rec.id);
353 FETCH okl_contract_ib_hv_pk_csr INTO
354 l_vibv_rec.id,
355 l_vibv_rec.major_version,
356 l_vibv_rec.object_version_number,
357 l_vibv_rec.dnz_chr_id,
358 l_vibv_rec.ib_cle_id,
359 l_vibv_rec.name,
360 l_vibv_rec.description,
361 l_vibv_rec.inventory_item_id,
362 l_vibv_rec.current_serial_number,
363 l_vibv_rec.install_site_use_id,
364 l_vibv_rec.quantity,
365 l_vibv_rec.attribute_category,
366 l_vibv_rec.attribute1,
367 l_vibv_rec.attribute2,
368 l_vibv_rec.attribute3,
369 l_vibv_rec.attribute4,
370 l_vibv_rec.attribute5,
371 l_vibv_rec.attribute6,
372 l_vibv_rec.attribute7,
373 l_vibv_rec.attribute8,
374 l_vibv_rec.attribute9,
375 l_vibv_rec.attribute10,
376 l_vibv_rec.attribute11,
377 l_vibv_rec.attribute12,
378 l_vibv_rec.attribute13,
379 l_vibv_rec.attribute14,
380 l_vibv_rec.attribute15,
381 l_vibv_rec.created_by,
382 l_vibv_rec.creation_date,
383 l_vibv_rec.last_updated_by,
384 l_vibv_rec.last_update_date,
385 l_vibv_rec.last_update_login,
386 --Added by dpsingh for LE uptake
387 l_vibv_rec.legal_entity_id;
388 x_no_data_found := okl_contract_ib_hv_pk_csr%NOTFOUND;
389 CLOSE okl_contract_ib_hv_pk_csr;
390 RETURN(l_vibv_rec);
391 END get_rec;
392 ------------------------------------------------------------------
393 -- This version of get_rec sets error messages if no data found --
394 ------------------------------------------------------------------
395 FUNCTION get_rec (
396 p_vibv_rec IN vibv_rec_type,
397 x_return_status OUT NOCOPY VARCHAR2
398 ) RETURN vibv_rec_type IS
399 l_vibv_rec vibv_rec_type;
400 l_row_notfound BOOLEAN := TRUE;
401 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
402 BEGIN
403 l_vibv_rec := get_rec(p_vibv_rec, l_row_notfound);
404 IF (l_row_notfound) THEN
405 x_return_status := OKL_API.G_RET_STS_ERROR;
406 END IF;
407 x_return_status:= l_return_status;
408 RETURN(l_vibv_rec);
409 END get_rec;
410 -----------------------------------------------------------
411 -- So we don't have to pass an "l_row_notfound" variable --
412 -----------------------------------------------------------
413 FUNCTION get_rec (
414 p_vibv_rec IN vibv_rec_type
415 ) RETURN vibv_rec_type IS
416 l_row_not_found BOOLEAN := TRUE;
417 BEGIN
418 RETURN(get_rec(p_vibv_rec, l_row_not_found));
419 END get_rec;
420 ---------------------------------------------------------------------------
421 -- FUNCTION get_rec for: OKL_CONTRACT_IB_H
422 ---------------------------------------------------------------------------
423 FUNCTION get_rec (
424 p_vib_rec IN vib_rec_type,
425 x_no_data_found OUT NOCOPY BOOLEAN
426 ) RETURN vib_rec_type IS
427 CURSOR okl_contract_ib_h_pk_csr(p_id IN NUMBER) IS
428 SELECT ID,
429 MAJOR_VERSION,
430 OBJECT_VERSION_NUMBER,
431 DNZ_CHR_ID,
432 IB_CLE_ID,
433 NAME,
434 DESCRIPTION,
435 INVENTORY_ITEM_ID,
436 CURRENT_SERIAL_NUMBER,
437 INSTALL_SITE_USE_ID,
438 QUANTITY,
439 ATTRIBUTE_CATEGORY,
440 ATTRIBUTE1,
441 ATTRIBUTE2,
442 ATTRIBUTE3,
443 ATTRIBUTE4,
444 ATTRIBUTE5,
445 ATTRIBUTE6,
446 ATTRIBUTE7,
447 ATTRIBUTE8,
448 ATTRIBUTE9,
449 ATTRIBUTE10,
450 ATTRIBUTE11,
451 ATTRIBUTE12,
452 ATTRIBUTE13,
453 ATTRIBUTE14,
454 ATTRIBUTE15,
455 CREATED_BY,
456 CREATION_DATE,
457 LAST_UPDATED_BY,
458 LAST_UPDATE_DATE,
459 LAST_UPDATE_LOGIN,
460 --Added by dpsingh for LE uptake
461 LEGAL_ENTITY_ID
462 FROM Okl_Contract_Ib_H
463 WHERE Okl_Contract_Ib_H.ID = p_id;
464
465 l_vib_rec vib_rec_type;
466 BEGIN
467 x_no_data_found := TRUE;
468 -- Get current database values
469 OPEN okl_contract_ib_h_pk_csr (p_vib_rec.id);
470 FETCH okl_contract_ib_h_pk_csr INTO
471 l_vib_rec.id,
472 l_vib_rec.major_version,
473 l_vib_rec.object_version_number,
474 l_vib_rec.dnz_chr_id,
475 l_vib_rec.ib_cle_id,
476 l_vib_rec.name,
477 l_vib_rec.description,
478 l_vib_rec.inventory_item_id,
479 l_vib_rec.current_serial_number,
480 l_vib_rec.install_site_use_id,
481 l_vib_rec.quantity,
482 l_vib_rec.attribute_category,
483 l_vib_rec.attribute1,
484 l_vib_rec.attribute2,
485 l_vib_rec.attribute3,
486 l_vib_rec.attribute4,
487 l_vib_rec.attribute5,
488 l_vib_rec.attribute6,
489 l_vib_rec.attribute7,
490 l_vib_rec.attribute8,
491 l_vib_rec.attribute9,
492 l_vib_rec.attribute10,
493 l_vib_rec.attribute11,
494 l_vib_rec.attribute12,
495 l_vib_rec.attribute13,
496 l_vib_rec.attribute14,
497 l_vib_rec.attribute15,
498 l_vib_rec.created_by,
499 l_vib_rec.creation_date,
500 l_vib_rec.last_updated_by,
501 l_vib_rec.last_update_date,
502 l_vib_rec.last_update_login,
503 --Added by dpsingh for LE uptake
504 l_vib_rec.legal_entity_id;
505 x_no_data_found := okl_contract_ib_h_pk_csr%NOTFOUND;
506 CLOSE okl_contract_ib_h_pk_csr;
507 RETURN(l_vib_rec);
508 END get_rec;
509 ------------------------------------------------------------------
510 -- This version of get_rec sets error messages if no data found --
511 ------------------------------------------------------------------
512 FUNCTION get_rec (
513 p_vib_rec IN vib_rec_type,
514 x_return_status OUT NOCOPY VARCHAR2
515 ) RETURN vib_rec_type IS
516 l_vib_rec vib_rec_type;
517 l_row_notfound BOOLEAN := TRUE;
518 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
519 BEGIN
520 l_vib_rec := get_rec(p_vib_rec, l_row_notfound);
521 IF (l_row_notfound) THEN
522 x_return_status := OKL_API.G_RET_STS_ERROR;
523 END IF;
524 x_return_status:= l_return_status;
525 RETURN(l_vib_rec);
526 END get_rec;
527 -----------------------------------------------------------
528 -- So we don't have to pass an "l_row_notfound" variable --
529 -----------------------------------------------------------
530 FUNCTION get_rec (
531 p_vib_rec IN vib_rec_type
532 ) RETURN vib_rec_type IS
533 l_row_not_found BOOLEAN := TRUE;
534 BEGIN
535 RETURN(get_rec(p_vib_rec, l_row_not_found));
536 END get_rec;
537 ---------------------------------------------------------------------------
538 -- FUNCTION null_out_defaults for: OKL_CONTRACT_IB_HV
539 ---------------------------------------------------------------------------
540 FUNCTION null_out_defaults (
541 p_vibv_rec IN vibv_rec_type
542 ) RETURN vibv_rec_type IS
543 l_vibv_rec vibv_rec_type := p_vibv_rec;
544 BEGIN
545 IF (l_vibv_rec.id = OKL_API.G_MISS_NUM ) THEN
546 l_vibv_rec.id := NULL;
547 END IF;
548 IF (l_vibv_rec.major_version = OKL_API.G_MISS_NUM ) THEN
549 l_vibv_rec.major_version := NULL;
550 END IF;
551 IF (l_vibv_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
552 l_vibv_rec.object_version_number := NULL;
553 END IF;
554 IF (l_vibv_rec.dnz_chr_id = OKL_API.G_MISS_NUM ) THEN
555 l_vibv_rec.dnz_chr_id := NULL;
556 END IF;
557 IF (l_vibv_rec.ib_cle_id = OKL_API.G_MISS_NUM ) THEN
558 l_vibv_rec.ib_cle_id := NULL;
559 END IF;
560 IF (l_vibv_rec.name = OKL_API.G_MISS_NUM ) THEN
561 l_vibv_rec.name := NULL;
562 END IF;
563 IF (l_vibv_rec.description = OKL_API.G_MISS_CHAR ) THEN
564 l_vibv_rec.description := NULL;
565 END IF;
566 IF (l_vibv_rec.inventory_item_id = OKL_API.G_MISS_NUM ) THEN
567 l_vibv_rec.inventory_item_id := NULL;
568 END IF;
569 IF (l_vibv_rec.current_serial_number = OKL_API.G_MISS_CHAR ) THEN
570 l_vibv_rec.current_serial_number := NULL;
571 END IF;
572 IF (l_vibv_rec.install_site_use_id = OKL_API.G_MISS_NUM ) THEN
573 l_vibv_rec.install_site_use_id := NULL;
574 END IF;
575 IF (l_vibv_rec.quantity = OKL_API.G_MISS_NUM ) THEN
576 l_vibv_rec.quantity := NULL;
577 END IF;
578 IF (l_vibv_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
579 l_vibv_rec.attribute_category := NULL;
580 END IF;
581 IF (l_vibv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
582 l_vibv_rec.attribute1 := NULL;
583 END IF;
584 IF (l_vibv_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
585 l_vibv_rec.attribute2 := NULL;
586 END IF;
587 IF (l_vibv_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
588 l_vibv_rec.attribute3 := NULL;
589 END IF;
590 IF (l_vibv_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
591 l_vibv_rec.attribute4 := NULL;
592 END IF;
593 IF (l_vibv_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
594 l_vibv_rec.attribute5 := NULL;
595 END IF;
596 IF (l_vibv_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
597 l_vibv_rec.attribute6 := NULL;
598 END IF;
599 IF (l_vibv_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
600 l_vibv_rec.attribute7 := NULL;
601 END IF;
602 IF (l_vibv_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
603 l_vibv_rec.attribute8 := NULL;
604 END IF;
605 IF (l_vibv_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
606 l_vibv_rec.attribute9 := NULL;
607 END IF;
608 IF (l_vibv_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
609 l_vibv_rec.attribute10 := NULL;
610 END IF;
611 IF (l_vibv_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
612 l_vibv_rec.attribute11 := NULL;
613 END IF;
614 IF (l_vibv_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
615 l_vibv_rec.attribute12 := NULL;
616 END IF;
617 IF (l_vibv_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
618 l_vibv_rec.attribute13 := NULL;
619 END IF;
620 IF (l_vibv_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
621 l_vibv_rec.attribute14 := NULL;
622 END IF;
623 IF (l_vibv_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
624 l_vibv_rec.attribute15 := NULL;
625 END IF;
626 IF (l_vibv_rec.created_by = OKL_API.G_MISS_NUM ) THEN
627 l_vibv_rec.created_by := NULL;
628 END IF;
629 IF (l_vibv_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
630 l_vibv_rec.creation_date := NULL;
631 END IF;
632 IF (l_vibv_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
633 l_vibv_rec.last_updated_by := NULL;
634 END IF;
635 IF (l_vibv_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
636 l_vibv_rec.last_update_date := NULL;
637 END IF;
638 IF (l_vibv_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
639 l_vibv_rec.last_update_login := NULL;
640 END IF;
641 --Added by dpsingh for LE uptake
642 IF (l_vibv_rec.legal_entity_id = OKL_API.G_MISS_NUM ) THEN
643 l_vibv_rec.legal_entity_id := NULL;
644 END IF;
645 RETURN(l_vibv_rec);
646 END null_out_defaults;
647 ---------------------------------------------------------------------------
648 -- FUNCTION Validate_Attributes
649 ---------------------------------------------------------------------------
650 ------------------------------------------------
651 -- Validate_Attributes for:OKL_CONTRACT_IB_HV --
652 ------------------------------------------------
653 FUNCTION Validate_Attributes (
654 p_vibv_rec IN vibv_rec_type
655 ) RETURN VARCHAR2 IS
656 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
657 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
658 BEGIN
659 -- Call OKC_UTIL.ADD_VIEW to prepare the PL/SQL table to hold columns of view
660 OKC_UTIL.ADD_VIEW('OKL_CONTRACT_IB_HV', x_return_status);
661 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
662 l_return_status := x_return_status;
663 END IF;
664 IF p_vibv_rec.id = OKL_API.G_MISS_NUM OR
665 p_vibv_rec.id IS NULL THEN
666 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
667 x_return_status := OKL_API.G_RET_STS_ERROR;
668 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
669 l_return_status := x_return_status;
670 END IF;
671 ELSIF p_vibv_rec.major_version = OKL_API.G_MISS_NUM OR
672 p_vibv_rec.major_version IS NULL THEN
673 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'major_version');
674 x_return_status := OKL_API.G_RET_STS_ERROR;
675 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
676 l_return_status := x_return_status;
677 END IF;
678 ELSIF p_vibv_rec.object_version_number = OKL_API.G_MISS_NUM OR
679 p_vibv_rec.object_version_number IS NULL THEN
680 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
681 x_return_status := OKL_API.G_RET_STS_ERROR;
682 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
683 l_return_status := x_return_status;
684 END IF;
685 END IF;
686 --*******************************Hand Code ***********************************--
687 validate_dnz_chr_id(x_return_status, p_vibv_rec.dnz_chr_id);
688 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
689 l_return_status := x_return_status;
690 END IF;
691 validate_ib_cle_id(x_return_status, p_vibv_rec.ib_cle_id);
692 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
693 l_return_status := x_return_status;
694 END IF;
695
696 --Added by dpsingh
697
698 -- Validate_LE_Id
699 Validate_LE_Id(p_vibv_rec.legal_entity_id,x_return_status);
700 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
701 l_return_status := x_return_status;
702 END IF;
703 --*******************************Hand Code ***********************************--
704 RETURN(l_return_status);
705 EXCEPTION
706 WHEN OTHERS THEN
707 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
708 ,p_msg_name => G_UNEXPECTED_ERROR
709 ,p_token1 => G_SQLCODE_TOKEN
710 ,p_token1_value => SQLCODE
711 ,p_token2 => G_SQLERRM_TOKEN
712 ,p_token2_value => SQLERRM);
713 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
714 RETURN(l_return_status);
715 END Validate_Attributes;
716 ---------------------------------------------------------------------------
717 -- PROCEDURE Validate_Record
718 ---------------------------------------------------------------------------
719 --------------------------------------------
720 -- Validate Record for:OKL_CONTRACT_IB_HV --
721 --------------------------------------------
722 FUNCTION Validate_Record (
723 p_vibv_rec IN vibv_rec_type) RETURN VARCHAR2 IS
724 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
725 BEGIN
726 RETURN (l_return_status);
727 END Validate_Record;
728 ---------------------------------------------------------------------------
729 -- PROCEDURE Migrate
730 ---------------------------------------------------------------------------
731 PROCEDURE migrate (
732 p_from IN vibv_rec_type,
733 p_to IN OUT NOCOPY vib_rec_type
734 ) IS
735 BEGIN
736 p_to.id := p_from.id;
737 p_to.major_version := p_from.major_version;
738 p_to.object_version_number := p_from.object_version_number;
739 p_to.dnz_chr_id := p_from.dnz_chr_id;
740 p_to.ib_cle_id := p_from.ib_cle_id;
741 p_to.name := p_from.name;
742 p_to.description := p_from.description;
743 p_to.inventory_item_id := p_from.inventory_item_id;
744 p_to.current_serial_number := p_from.current_serial_number;
745 p_to.install_site_use_id := p_from.install_site_use_id;
746 p_to.quantity := p_from.quantity;
747 p_to.attribute_category := p_from.attribute_category;
748 p_to.attribute1 := p_from.attribute1;
749 p_to.attribute2 := p_from.attribute2;
750 p_to.attribute3 := p_from.attribute3;
751 p_to.attribute4 := p_from.attribute4;
752 p_to.attribute5 := p_from.attribute5;
753 p_to.attribute6 := p_from.attribute6;
754 p_to.attribute7 := p_from.attribute7;
755 p_to.attribute8 := p_from.attribute8;
756 p_to.attribute9 := p_from.attribute9;
757 p_to.attribute10 := p_from.attribute10;
758 p_to.attribute11 := p_from.attribute11;
759 p_to.attribute12 := p_from.attribute12;
760 p_to.attribute13 := p_from.attribute13;
761 p_to.attribute14 := p_from.attribute14;
762 p_to.attribute15 := p_from.attribute15;
763 p_to.created_by := p_from.created_by;
764 p_to.creation_date := p_from.creation_date;
765 p_to.last_updated_by := p_from.last_updated_by;
766 p_to.last_update_date := p_from.last_update_date;
767 p_to.last_update_login := p_from.last_update_login;
768 --Added by dpsingh for LE uptake
769 p_to.legal_entity_id := p_from.legal_entity_id;
770 END migrate;
771 ---------------------------------------------------------------------------
772 -- PROCEDURE Migrate
773 ---------------------------------------------------------------------------
774 PROCEDURE migrate (
775 p_from IN vib_rec_type,
776 p_to IN OUT NOCOPY vibv_rec_type
777 ) IS
778 BEGIN
779 p_to.id := p_from.id;
780 p_to.major_version := p_from.major_version;
781 p_to.object_version_number := p_from.object_version_number;
782 p_to.dnz_chr_id := p_from.dnz_chr_id;
783 p_to.ib_cle_id := p_from.ib_cle_id;
784 p_to.name := p_from.name;
785 p_to.description := p_from.description;
786 p_to.inventory_item_id := p_from.inventory_item_id;
787 p_to.current_serial_number := p_from.current_serial_number;
788 p_to.install_site_use_id := p_from.install_site_use_id;
789 p_to.quantity := p_from.quantity;
790 p_to.attribute_category := p_from.attribute_category;
791 p_to.attribute1 := p_from.attribute1;
792 p_to.attribute2 := p_from.attribute2;
793 p_to.attribute3 := p_from.attribute3;
794 p_to.attribute4 := p_from.attribute4;
795 p_to.attribute5 := p_from.attribute5;
796 p_to.attribute6 := p_from.attribute6;
797 p_to.attribute7 := p_from.attribute7;
798 p_to.attribute8 := p_from.attribute8;
799 p_to.attribute9 := p_from.attribute9;
800 p_to.attribute10 := p_from.attribute10;
801 p_to.attribute11 := p_from.attribute11;
802 p_to.attribute12 := p_from.attribute12;
803 p_to.attribute13 := p_from.attribute13;
804 p_to.attribute14 := p_from.attribute14;
805 p_to.attribute15 := p_from.attribute15;
806 p_to.created_by := p_from.created_by;
807 p_to.creation_date := p_from.creation_date;
808 p_to.last_updated_by := p_from.last_updated_by;
809 p_to.last_update_date := p_from.last_update_date;
810 p_to.last_update_login := p_from.last_update_login;
811 --Added by dpsingh for LE uptake
812 p_to.legal_entity_id := p_from.legal_entity_id;
813 END migrate;
814 ---------------------------------------------------------------------------
815 -- PROCEDURE validate_row
816 ---------------------------------------------------------------------------
817 -----------------------------------------
818 -- validate_row for:OKL_CONTRACT_IB_HV --
819 -----------------------------------------
820 PROCEDURE validate_row(
821 p_api_version IN NUMBER,
822 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
823 x_return_status OUT NOCOPY VARCHAR2,
824 x_msg_count OUT NOCOPY NUMBER,
825 x_msg_data OUT NOCOPY VARCHAR2,
826 p_vibv_rec IN vibv_rec_type) IS
827
828 l_api_version CONSTANT NUMBER := 1;
829 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
830 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
831 l_vibv_rec vibv_rec_type := p_vibv_rec;
832 l_vib_rec vib_rec_type;
833 BEGIN
834 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
835 G_PKG_NAME,
836 p_init_msg_list,
837 l_api_version,
838 p_api_version,
839 '_PVT',
840 x_return_status);
841 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
842 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
843 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
844 RAISE OKL_API.G_EXCEPTION_ERROR;
845 END IF;
846 --- Validate all non-missing attributes (Item Level Validation)
847 l_return_status := Validate_Attributes(l_vibv_rec);
848 --- If any errors happen abort API
849 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
850 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
851 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
852 RAISE OKL_API.G_EXCEPTION_ERROR;
853 END IF;
854 l_return_status := Validate_Record(l_vibv_rec);
855 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
856 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
857 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
858 RAISE OKL_API.G_EXCEPTION_ERROR;
859 END IF;
860 x_return_status := l_return_status;
861 EXCEPTION
862 WHEN OKL_API.G_EXCEPTION_ERROR THEN
863 x_return_status := OKL_API.HANDLE_EXCEPTIONS
864 (
865 l_api_name,
866 G_PKG_NAME,
867 'OKL_API.G_RET_STS_ERROR',
868 x_msg_count,
869 x_msg_data,
870 '_PVT'
871 );
872 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
873 x_return_status := OKL_API.HANDLE_EXCEPTIONS
874 (
875 l_api_name,
876 G_PKG_NAME,
877 'OKL_API.G_RET_STS_UNEXP_ERROR',
878 x_msg_count,
879 x_msg_data,
880 '_PVT'
881 );
882 WHEN OTHERS THEN
883 x_return_status := OKL_API.HANDLE_EXCEPTIONS
884 (
885 l_api_name,
886 G_PKG_NAME,
887 'OTHERS',
888 x_msg_count,
889 x_msg_data,
890 '_PVT'
891 );
892 END validate_row;
893 ----------------------------------------------------
894 -- PL/SQL TBL validate_row for:OKL_CONTRACT_IB_HV --
895 ----------------------------------------------------
896 PROCEDURE validate_row(
897 p_api_version IN NUMBER,
898 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
899 x_return_status OUT NOCOPY VARCHAR2,
900 x_msg_count OUT NOCOPY NUMBER,
901 x_msg_data OUT NOCOPY VARCHAR2,
902 p_vibv_tbl IN vibv_tbl_type) IS
903
904 l_api_version CONSTANT NUMBER := 1;
905 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
906 i NUMBER := 0;
907 BEGIN
908 OKL_API.init_msg_list(p_init_msg_list);
909 -- Make sure PL/SQL table has records in it before passing
910 IF (p_vibv_tbl.COUNT > 0) THEN
911 i := p_vibv_tbl.FIRST;
912 LOOP
913 validate_row (
914 p_api_version => p_api_version,
915 p_init_msg_list => p_init_msg_list,
916 x_return_status => x_return_status,
917 x_msg_count => x_msg_count,
918 x_msg_data => x_msg_data,
919 p_vibv_rec => p_vibv_tbl(i));
920 EXIT WHEN (i = p_vibv_tbl.LAST);
921 i := p_vibv_tbl.NEXT(i);
922 END LOOP;
923 END IF;
924 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
925 EXCEPTION
926 WHEN OKL_API.G_EXCEPTION_ERROR THEN
927 x_return_status := OKL_API.HANDLE_EXCEPTIONS
928 (
929 l_api_name,
930 G_PKG_NAME,
931 'OKL_API.G_RET_STS_ERROR',
932 x_msg_count,
933 x_msg_data,
934 '_PVT'
935 );
936 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
937 x_return_status := OKL_API.HANDLE_EXCEPTIONS
938 (
939 l_api_name,
940 G_PKG_NAME,
941 'OKL_API.G_RET_STS_UNEXP_ERROR',
942 x_msg_count,
943 x_msg_data,
944 '_PVT'
945 );
946 WHEN OTHERS THEN
947 x_return_status := OKL_API.HANDLE_EXCEPTIONS
948 (
949 l_api_name,
950 G_PKG_NAME,
951 'OTHERS',
952 x_msg_count,
953 x_msg_data,
954 '_PVT'
955 );
956 END validate_row;
957 ---------------------------------------------------------------------------
958 -- PROCEDURE insert_row
959 ---------------------------------------------------------------------------
960 ---------------------------------------
961 -- insert_row for:OKL_CONTRACT_IB_HV --
962 ---------------------------------------
963 PROCEDURE insert_row(
964 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
965 x_return_status OUT NOCOPY VARCHAR2,
966 x_msg_count OUT NOCOPY NUMBER,
967 x_msg_data OUT NOCOPY VARCHAR2,
968 p_vib_rec IN vib_rec_type,
969 x_vib_rec OUT NOCOPY vib_rec_type) IS
970
971 l_api_version CONSTANT NUMBER := 1;
972 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
973 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
974 l_vib_rec vib_rec_type := p_vib_rec;
975 l_def_vib_rec vib_rec_type;
976 -------------------------------------------
977 -- Set_Attributes for:OKL_CONTRACT_IB_HV --
978 -------------------------------------------
979 FUNCTION Set_Attributes (
980 p_vib_rec IN vib_rec_type,
981 x_vib_rec OUT NOCOPY vib_rec_type
982 ) RETURN VARCHAR2 IS
983 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
984 BEGIN
985 x_vib_rec := p_vib_rec;
986 RETURN(l_return_status);
987 END Set_Attributes;
988 BEGIN
989 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
990 p_init_msg_list,
991 '_PVT',
992 x_return_status);
993 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
994 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
995 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
996 RAISE OKL_API.G_EXCEPTION_ERROR;
997 END IF;
998 --- Setting item atributes
999 l_return_status := Set_Attributes(
1000 p_vib_rec, -- IN
1001 l_vib_rec); -- OUT
1002 --- If any errors happen abort API
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 INSERT INTO OKL_CONTRACT_IB_H(
1009 id,
1010 major_version,
1011 object_version_number,
1012 dnz_chr_id,
1013 ib_cle_id,
1014 name,
1015 description,
1016 inventory_item_id,
1017 current_serial_number,
1018 install_site_use_id,
1019 quantity,
1020 attribute_category,
1021 attribute1,
1022 attribute2,
1023 attribute3,
1024 attribute4,
1025 attribute5,
1026 attribute6,
1027 attribute7,
1028 attribute8,
1029 attribute9,
1030 attribute10,
1031 attribute11,
1032 attribute12,
1033 attribute13,
1034 attribute14,
1035 attribute15,
1036 created_by,
1037 creation_date,
1038 last_updated_by,
1039 last_update_date,
1040 last_update_login,
1041 --Added by dpsingh for LE uptake
1042 legal_entity_id)
1043 VALUES (
1044 l_vib_rec.id,
1045 l_vib_rec.major_version,
1046 l_vib_rec.object_version_number,
1047 l_vib_rec.dnz_chr_id,
1048 l_vib_rec.ib_cle_id,
1049 l_vib_rec.name,
1050 l_vib_rec.description,
1051 l_vib_rec.inventory_item_id,
1052 l_vib_rec.current_serial_number,
1053 l_vib_rec.install_site_use_id,
1054 l_vib_rec.quantity,
1055 l_vib_rec.attribute_category,
1056 l_vib_rec.attribute1,
1057 l_vib_rec.attribute2,
1058 l_vib_rec.attribute3,
1059 l_vib_rec.attribute4,
1060 l_vib_rec.attribute5,
1061 l_vib_rec.attribute6,
1062 l_vib_rec.attribute7,
1063 l_vib_rec.attribute8,
1064 l_vib_rec.attribute9,
1065 l_vib_rec.attribute10,
1066 l_vib_rec.attribute11,
1067 l_vib_rec.attribute12,
1068 l_vib_rec.attribute13,
1069 l_vib_rec.attribute14,
1070 l_vib_rec.attribute15,
1071 l_vib_rec.created_by,
1072 l_vib_rec.creation_date,
1073 l_vib_rec.last_updated_by,
1074 l_vib_rec.last_update_date,
1075 l_vib_rec.last_update_login,
1076
1077 --Added by dpsingh for LE uptake
1078 l_vib_rec.legal_entity_id);
1079 -- Set OUT values
1080 x_vib_rec := l_vib_rec;
1081 x_return_status := l_return_status;
1082 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1083 EXCEPTION
1084 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1085 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1086 (
1087 l_api_name,
1088 G_PKG_NAME,
1089 'OKL_API.G_RET_STS_ERROR',
1090 x_msg_count,
1091 x_msg_data,
1092 '_PVT'
1093 );
1094 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1095 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1096 (
1097 l_api_name,
1098 G_PKG_NAME,
1099 'OKL_API.G_RET_STS_UNEXP_ERROR',
1100 x_msg_count,
1101 x_msg_data,
1102 '_PVT'
1103 );
1104 WHEN OTHERS THEN
1105 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1106 (
1107 l_api_name,
1108 G_PKG_NAME,
1109 'OTHERS',
1110 x_msg_count,
1111 x_msg_data,
1112 '_PVT'
1113 );
1114 END insert_row;
1115 ---------------------------------------------
1116 -- insert_row for :OKL_CONTRACT_IB_HV --
1117 ---------------------------------------------
1118 PROCEDURE insert_row(
1119 p_api_version IN NUMBER,
1120 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1121 x_return_status OUT NOCOPY VARCHAR2,
1122 x_msg_count OUT NOCOPY NUMBER,
1123 x_msg_data OUT NOCOPY VARCHAR2,
1124 p_vibv_rec IN vibv_rec_type,
1125 x_vibv_rec OUT NOCOPY vibv_rec_type) IS
1126
1127 l_api_version CONSTANT NUMBER := 1;
1128 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1129 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1130 l_vibv_rec vibv_rec_type := p_vibv_rec;
1131 l_def_vibv_rec vibv_rec_type;
1132 l_vib_rec vib_rec_type;
1133 lx_vib_rec vib_rec_type;
1134 -------------------------------
1135 -- FUNCTION fill_who_columns --
1136 -------------------------------
1137 FUNCTION fill_who_columns (
1138 p_vibv_rec IN vibv_rec_type
1139 ) RETURN vibv_rec_type IS
1140 l_vibv_rec vibv_rec_type := p_vibv_rec;
1141 BEGIN
1142 l_vibv_rec.CREATION_DATE := SYSDATE;
1143 l_vibv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1144 l_vibv_rec.LAST_UPDATE_DATE := l_vibv_rec.CREATION_DATE;
1145 l_vibv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1146 l_vibv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1147 RETURN(l_vibv_rec);
1148 END fill_who_columns;
1149 ------------------------------------------------
1150 -- Set_Attributes for:OKL_SUPP_INVOICE_DTLS_V --
1151 ------------------------------------------------
1152 FUNCTION Set_Attributes (
1153 p_vibv_rec IN vibv_rec_type,
1154 x_vibv_rec OUT NOCOPY vibv_rec_type
1155 ) RETURN VARCHAR2 IS
1156 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1157 BEGIN
1158 x_vibv_rec := p_vibv_rec;
1159 x_vibv_rec.OBJECT_VERSION_NUMBER := 1;
1160 RETURN(l_return_status);
1161 END Set_Attributes;
1162 BEGIN
1163 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1164 G_PKG_NAME,
1165 p_init_msg_list,
1166 l_api_version,
1167 p_api_version,
1168 '_PVT',
1169 x_return_status);
1170 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1171 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1172 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1173 RAISE OKL_API.G_EXCEPTION_ERROR;
1174 END IF;
1175 l_vibv_rec := null_out_defaults(p_vibv_rec);
1176 -- Set primary key value
1177 l_vibv_rec.ID := get_seq_id;
1178 -- Setting item attributes
1179 l_return_Status := Set_Attributes(
1180 l_vibv_rec, -- IN
1181 l_def_vibv_rec); -- OUT
1182 --- If any errors happen abort API
1183 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1184 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1185 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1186 RAISE OKL_API.G_EXCEPTION_ERROR;
1187 END IF;
1188 l_def_vibv_rec := fill_who_columns(l_def_vibv_rec);
1189 --- Validate all non-missing attributes (Item Level Validation)
1190 l_return_status := Validate_Attributes(l_def_vibv_rec);
1191 --- If any errors happen abort API
1192 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1193 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1194 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1195 RAISE OKL_API.G_EXCEPTION_ERROR;
1196 END IF;
1197 l_return_status := Validate_Record(l_def_vibv_rec);
1198 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1199 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1200 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1201 RAISE OKL_API.G_EXCEPTION_ERROR;
1202 END IF;
1203 -----------------------------------------
1204 -- Move VIEW record to "Child" records --
1205 -----------------------------------------
1206 migrate(l_def_vibv_rec, l_vib_rec);
1207 -----------------------------------------------
1208 -- Call the INSERT_ROW for each child record --
1209 -----------------------------------------------
1210 insert_row(
1211 p_init_msg_list,
1212 l_return_status,
1213 x_msg_count,
1214 x_msg_data,
1215 l_vib_rec,
1216 lx_vib_rec
1217 );
1218 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1219 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1220 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1221 RAISE OKL_API.G_EXCEPTION_ERROR;
1222 END IF;
1223 migrate(lx_vib_rec, l_def_vibv_rec);
1224 -- Set OUT values
1225 x_vibv_rec := l_def_vibv_rec;
1226 x_return_status := l_return_status;
1227 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1228 EXCEPTION
1229 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1230 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1231 (
1232 l_api_name,
1233 G_PKG_NAME,
1234 'OKL_API.G_RET_STS_ERROR',
1235 x_msg_count,
1236 x_msg_data,
1237 '_PVT'
1238 );
1239 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1240 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1241 (
1242 l_api_name,
1243 G_PKG_NAME,
1244 'OKL_API.G_RET_STS_UNEXP_ERROR',
1245 x_msg_count,
1246 x_msg_data,
1247 '_PVT'
1248 );
1249 WHEN OTHERS THEN
1250 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1251 (
1252 l_api_name,
1253 G_PKG_NAME,
1254 'OTHERS',
1255 x_msg_count,
1256 x_msg_data,
1257 '_PVT'
1258 );
1259 END insert_row;
1260 ----------------------------------------
1261 -- PL/SQL TBL insert_row for:vibV_TBL --
1262 ----------------------------------------
1263 PROCEDURE insert_row(
1264 p_api_version IN NUMBER,
1265 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1266 x_return_status OUT NOCOPY VARCHAR2,
1267 x_msg_count OUT NOCOPY NUMBER,
1268 x_msg_data OUT NOCOPY VARCHAR2,
1269 p_vibv_tbl IN vibv_tbl_type,
1270 x_vibv_tbl OUT NOCOPY vibv_tbl_type) IS
1271
1272 l_api_version CONSTANT NUMBER := 1;
1273 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1274 i NUMBER := 0;
1275 BEGIN
1276 OKL_API.init_msg_list(p_init_msg_list);
1277 -- Make sure PL/SQL table has records in it before passing
1278 IF (p_vibv_tbl.COUNT > 0) THEN
1279 i := p_vibv_tbl.FIRST;
1280 LOOP
1281 insert_row (
1282 p_api_version => p_api_version,
1283 p_init_msg_list => p_init_msg_list,
1284 x_return_status => x_return_status,
1285 x_msg_count => x_msg_count,
1286 x_msg_data => x_msg_data,
1287 p_vibv_rec => p_vibv_tbl(i),
1288 x_vibv_rec => x_vibv_tbl(i));
1289 EXIT WHEN (i = p_vibv_tbl.LAST);
1290 i := p_vibv_tbl.NEXT(i);
1291 END LOOP;
1292 END IF;
1293 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1294 EXCEPTION
1295 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1296 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1297 (
1298 l_api_name,
1299 G_PKG_NAME,
1300 'OKL_API.G_RET_STS_ERROR',
1301 x_msg_count,
1302 x_msg_data,
1303 '_PVT'
1304 );
1305 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1306 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1307 (
1308 l_api_name,
1309 G_PKG_NAME,
1310 'OKL_API.G_RET_STS_UNEXP_ERROR',
1311 x_msg_count,
1312 x_msg_data,
1313 '_PVT'
1314 );
1315 WHEN OTHERS THEN
1316 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1317 (
1318 l_api_name,
1319 G_PKG_NAME,
1320 'OTHERS',
1321 x_msg_count,
1322 x_msg_data,
1323 '_PVT'
1324 );
1325 END insert_row;
1326 ---------------------------------------------------------------------------
1327 -- PROCEDURE lock_row
1328 ---------------------------------------------------------------------------
1329 -------------------------------------
1330 -- lock_row for:OKL_CONTRACT_IB_HV --
1331 -------------------------------------
1332 PROCEDURE lock_row(
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_vib_rec IN vib_rec_type) IS
1338
1339 E_Resource_Busy EXCEPTION;
1340 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1341 CURSOR lock_csr (p_vib_rec IN vib_rec_type) IS
1342 SELECT OBJECT_VERSION_NUMBER
1343 FROM OKL_CONTRACT_IB_H
1344 WHERE OBJECT_VERSION_NUMBER = p_vib_rec.object_version_number
1345 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1346
1347 CURSOR lchk_csr (p_vib_rec IN vib_rec_type) IS
1348 SELECT OBJECT_VERSION_NUMBER
1349 FROM OKL_CONTRACT_IB_H
1350 WHERE ID = p_vib_rec.id;
1351 l_api_version CONSTANT NUMBER := 1;
1352 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1353 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1354 l_object_version_number OKL_CONTRACT_IB_H.OBJECT_VERSION_NUMBER%TYPE;
1355 lc_object_version_number OKL_CONTRACT_IB_H.OBJECT_VERSION_NUMBER%TYPE;
1356 l_row_notfound BOOLEAN := FALSE;
1357 lc_row_notfound BOOLEAN := FALSE;
1358 BEGIN
1359 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1360 p_init_msg_list,
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 BEGIN
1369 OPEN lock_csr(p_vib_rec);
1370 FETCH lock_csr INTO l_object_version_number;
1371 l_row_notfound := lock_csr%NOTFOUND;
1372 CLOSE lock_csr;
1373 EXCEPTION
1374 WHEN E_Resource_Busy THEN
1375 IF (lock_csr%ISOPEN) THEN
1376 CLOSE lock_csr;
1377 END IF;
1378 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1379 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1380 END;
1381 IF ( l_row_notfound ) THEN
1382 OPEN lchk_csr(p_vib_rec);
1383 FETCH lchk_csr INTO lc_object_version_number;
1384 lc_row_notfound := lchk_csr%NOTFOUND;
1385 CLOSE lchk_csr;
1386 END IF;
1387 IF (lc_row_notfound) THEN
1388 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1389 RAISE OKL_API.G_EXCEPTION_ERROR;
1390 ELSIF lc_object_version_number > p_vib_rec.object_version_number THEN
1391 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1392 RAISE OKL_API.G_EXCEPTION_ERROR;
1393 ELSIF lc_object_version_number <> p_vib_rec.object_version_number THEN
1394 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1395 RAISE OKL_API.G_EXCEPTION_ERROR;
1396 ELSIF lc_object_version_number = -1 THEN
1397 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1398 RAISE OKL_API.G_EXCEPTION_ERROR;
1399 END IF;
1400 x_return_status := l_return_status;
1401 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1402 EXCEPTION
1403 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1404 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1405 (
1406 l_api_name,
1407 G_PKG_NAME,
1408 'OKL_API.G_RET_STS_ERROR',
1409 x_msg_count,
1410 x_msg_data,
1411 '_PVT'
1412 );
1413 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1414 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1415 (
1416 l_api_name,
1417 G_PKG_NAME,
1418 'OKL_API.G_RET_STS_UNEXP_ERROR',
1419 x_msg_count,
1420 x_msg_data,
1421 '_PVT'
1422 );
1423 WHEN OTHERS THEN
1424 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1425 (
1426 l_api_name,
1427 G_PKG_NAME,
1428 'OTHERS',
1429 x_msg_count,
1430 x_msg_data,
1431 '_PVT'
1432 );
1433 END lock_row;
1434 --------------------------------------
1435 -- lock_row for: OKL_CONTRACT_IB_HV --
1436 --------------------------------------
1437 PROCEDURE lock_row(
1438 p_api_version IN NUMBER,
1439 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1440 x_return_status OUT NOCOPY VARCHAR2,
1441 x_msg_count OUT NOCOPY NUMBER,
1442 x_msg_data OUT NOCOPY VARCHAR2,
1443 p_vibv_rec IN vibv_rec_type) IS
1444
1445 l_api_version CONSTANT NUMBER := 1;
1446 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1447 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1448 l_vibv_rec vibv_rec_type;
1449 l_vib_rec vib_rec_type;
1450 BEGIN
1451 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1452 G_PKG_NAME,
1453 p_init_msg_list,
1454 l_api_version,
1455 p_api_version,
1456 '_PVT',
1457 x_return_status);
1458 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1459 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1460 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1461 RAISE OKL_API.G_EXCEPTION_ERROR;
1462 END IF;
1463 -----------------------------------------
1464 -- Move VIEW record to "Child" records --
1465 -----------------------------------------
1466 migrate(p_vibv_rec, l_vib_rec);
1467 ---------------------------------------------
1468 -- Call the LOCK_ROW for each child record --
1469 ---------------------------------------------
1470 lock_row(
1471 p_init_msg_list,
1472 l_return_status,
1473 x_msg_count,
1474 x_msg_data,
1475 l_vib_rec);
1476 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1477 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1478 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1479 RAISE OKL_API.G_EXCEPTION_ERROR;
1480 END IF;
1481 x_return_status := l_return_status;
1482 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1483 EXCEPTION
1484 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1485 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1486 (
1487 l_api_name,
1488 G_PKG_NAME,
1489 'OKL_API.G_RET_STS_ERROR',
1490 x_msg_count,
1491 x_msg_data,
1492 '_PVT'
1493 );
1494 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1495 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1496 (
1497 l_api_name,
1498 G_PKG_NAME,
1499 'OKL_API.G_RET_STS_UNEXP_ERROR',
1500 x_msg_count,
1501 x_msg_data,
1502 '_PVT'
1503 );
1504 WHEN OTHERS THEN
1505 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1506 (
1507 l_api_name,
1508 G_PKG_NAME,
1509 'OTHERS',
1510 x_msg_count,
1511 x_msg_data,
1512 '_PVT'
1513 );
1514 END lock_row;
1515 --------------------------------------
1516 -- PL/SQL TBL lock_row for:VIBV_TBL --
1517 --------------------------------------
1518 PROCEDURE lock_row(
1519 p_api_version IN NUMBER,
1520 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1521 x_return_status OUT NOCOPY VARCHAR2,
1522 x_msg_count OUT NOCOPY NUMBER,
1523 x_msg_data OUT NOCOPY VARCHAR2,
1524 p_vibv_tbl IN vibv_tbl_type) IS
1525
1526 l_api_version CONSTANT NUMBER := 1;
1527 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1528 i NUMBER := 0;
1529 BEGIN
1530 OKL_API.init_msg_list(p_init_msg_list);
1531 -- Make sure PL/SQL table has recrods in it before passing
1532 IF (p_vibv_tbl.COUNT > 0) THEN
1533 i := p_vibv_tbl.FIRST;
1534 LOOP
1535 lock_row(
1536 p_api_version => p_api_version,
1537 p_init_msg_list => p_init_msg_list,
1538 x_return_status => x_return_status,
1539 x_msg_count => x_msg_count,
1540 x_msg_data => x_msg_data,
1541 p_vibv_rec => p_vibv_tbl(i));
1542 EXIT WHEN (i = p_vibv_tbl.LAST);
1543 i := p_vibv_tbl.NEXT(i);
1544 END LOOP;
1545 END IF;
1546 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1547 EXCEPTION
1548 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1549 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1550 (
1551 l_api_name,
1552 G_PKG_NAME,
1553 'OKL_API.G_RET_STS_ERROR',
1554 x_msg_count,
1555 x_msg_data,
1556 '_PVT'
1557 );
1558 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1559 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1560 (
1561 l_api_name,
1562 G_PKG_NAME,
1563 'OKL_API.G_RET_STS_UNEXP_ERROR',
1564 x_msg_count,
1565 x_msg_data,
1566 '_PVT'
1567 );
1568 WHEN OTHERS THEN
1569 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1570 (
1571 l_api_name,
1572 G_PKG_NAME,
1573 'OTHERS',
1574 x_msg_count,
1575 x_msg_data,
1576 '_PVT'
1577 );
1578 END lock_row;
1579 ---------------------------------------------------------------------------
1580 -- PROCEDURE update_row
1581 ---------------------------------------------------------------------------
1582 ---------------------------------------
1583 -- update_row for:OKL_CONTRACT_IB_HV --
1584 ---------------------------------------
1585 PROCEDURE update_row(
1586 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1587 x_return_status OUT NOCOPY VARCHAR2,
1588 x_msg_count OUT NOCOPY NUMBER,
1589 x_msg_data OUT NOCOPY VARCHAR2,
1590 p_vib_rec IN vib_rec_type,
1591 x_vib_rec OUT NOCOPY vib_rec_type) IS
1592
1593 l_api_version CONSTANT NUMBER := 1;
1594 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1595 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1596 l_vib_rec vib_rec_type := p_vib_rec;
1597 l_def_vib_rec vib_rec_type;
1598 l_row_notfound BOOLEAN := TRUE;
1599 ----------------------------------
1600 -- FUNCTION populate_new_record --
1601 ----------------------------------
1602 FUNCTION populate_new_record (
1603 p_vib_rec IN vib_rec_type,
1604 x_vib_rec OUT NOCOPY vib_rec_type
1605 ) RETURN VARCHAR2 IS
1606 l_vib_rec vib_rec_type;
1607 l_row_notfound BOOLEAN := TRUE;
1608 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1609 BEGIN
1610 x_vib_rec := p_vib_rec;
1611 -- Get current database values
1612 l_vib_rec := get_rec(p_vib_rec, l_return_status);
1613 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1614 IF (x_vib_rec.id = OKL_API.G_MISS_NUM)
1615 THEN
1616 x_vib_rec.id := l_vib_rec.id;
1617 END IF;
1618 IF (x_vib_rec.major_version = OKL_API.G_MISS_NUM)
1619 THEN
1620 x_vib_rec.major_version := l_vib_rec.major_version;
1621 END IF;
1622 IF (x_vib_rec.object_version_number = OKL_API.G_MISS_NUM)
1623 THEN
1624 x_vib_rec.object_version_number := l_vib_rec.object_version_number;
1625 END IF;
1626 IF (x_vib_rec.dnz_chr_id = OKL_API.G_MISS_NUM)
1627 THEN
1628 x_vib_rec.dnz_chr_id := l_vib_rec.dnz_chr_id;
1629 END IF;
1630 IF (x_vib_rec.ib_cle_id = OKL_API.G_MISS_NUM)
1631 THEN
1632 x_vib_rec.ib_cle_id := l_vib_rec.ib_cle_id;
1633 END IF;
1634 IF (x_vib_rec.name = OKL_API.G_MISS_NUM)
1635 THEN
1636 x_vib_rec.name := l_vib_rec.name;
1637 END IF;
1638 IF (x_vib_rec.description = OKL_API.G_MISS_CHAR)
1639 THEN
1640 x_vib_rec.description := l_vib_rec.description;
1641 END IF;
1642 IF (x_vib_rec.inventory_item_id = OKL_API.G_MISS_NUM)
1643 THEN
1644 x_vib_rec.inventory_item_id := l_vib_rec.inventory_item_id;
1645 END IF;
1646 IF (x_vib_rec.current_serial_number = OKL_API.G_MISS_CHAR)
1647 THEN
1648 x_vib_rec.current_serial_number := l_vib_rec.current_serial_number;
1649 END IF;
1650 IF (x_vib_rec.install_site_use_id = OKL_API.G_MISS_NUM)
1651 THEN
1652 x_vib_rec.install_site_use_id := l_vib_rec.install_site_use_id;
1653 END IF;
1654 IF (x_vib_rec.quantity = OKL_API.G_MISS_NUM)
1655 THEN
1656 x_vib_rec.quantity := l_vib_rec.quantity;
1657 END IF;
1658 IF (x_vib_rec.attribute_category = OKL_API.G_MISS_CHAR)
1659 THEN
1660 x_vib_rec.attribute_category := l_vib_rec.attribute_category;
1661 END IF;
1662 IF (x_vib_rec.attribute1 = OKL_API.G_MISS_CHAR)
1663 THEN
1664 x_vib_rec.attribute1 := l_vib_rec.attribute1;
1665 END IF;
1666 IF (x_vib_rec.attribute2 = OKL_API.G_MISS_CHAR)
1667 THEN
1668 x_vib_rec.attribute2 := l_vib_rec.attribute2;
1669 END IF;
1670 IF (x_vib_rec.attribute3 = OKL_API.G_MISS_CHAR)
1671 THEN
1672 x_vib_rec.attribute3 := l_vib_rec.attribute3;
1673 END IF;
1674 IF (x_vib_rec.attribute4 = OKL_API.G_MISS_CHAR)
1675 THEN
1676 x_vib_rec.attribute4 := l_vib_rec.attribute4;
1677 END IF;
1678 IF (x_vib_rec.attribute5 = OKL_API.G_MISS_CHAR)
1679 THEN
1680 x_vib_rec.attribute5 := l_vib_rec.attribute5;
1681 END IF;
1682 IF (x_vib_rec.attribute6 = OKL_API.G_MISS_CHAR)
1683 THEN
1684 x_vib_rec.attribute6 := l_vib_rec.attribute6;
1685 END IF;
1686 IF (x_vib_rec.attribute7 = OKL_API.G_MISS_CHAR)
1687 THEN
1688 x_vib_rec.attribute7 := l_vib_rec.attribute7;
1689 END IF;
1690 IF (x_vib_rec.attribute8 = OKL_API.G_MISS_CHAR)
1691 THEN
1692 x_vib_rec.attribute8 := l_vib_rec.attribute8;
1693 END IF;
1694 IF (x_vib_rec.attribute9 = OKL_API.G_MISS_CHAR)
1695 THEN
1696 x_vib_rec.attribute9 := l_vib_rec.attribute9;
1697 END IF;
1698 IF (x_vib_rec.attribute10 = OKL_API.G_MISS_CHAR)
1699 THEN
1700 x_vib_rec.attribute10 := l_vib_rec.attribute10;
1701 END IF;
1702 IF (x_vib_rec.attribute11 = OKL_API.G_MISS_CHAR)
1703 THEN
1704 x_vib_rec.attribute11 := l_vib_rec.attribute11;
1705 END IF;
1706 IF (x_vib_rec.attribute12 = OKL_API.G_MISS_CHAR)
1707 THEN
1708 x_vib_rec.attribute12 := l_vib_rec.attribute12;
1709 END IF;
1710 IF (x_vib_rec.attribute13 = OKL_API.G_MISS_CHAR)
1711 THEN
1712 x_vib_rec.attribute13 := l_vib_rec.attribute13;
1713 END IF;
1714 IF (x_vib_rec.attribute14 = OKL_API.G_MISS_CHAR)
1715 THEN
1716 x_vib_rec.attribute14 := l_vib_rec.attribute14;
1717 END IF;
1718 IF (x_vib_rec.attribute15 = OKL_API.G_MISS_CHAR)
1719 THEN
1720 x_vib_rec.attribute15 := l_vib_rec.attribute15;
1721 END IF;
1722 IF (x_vib_rec.created_by = OKL_API.G_MISS_NUM)
1723 THEN
1724 x_vib_rec.created_by := l_vib_rec.created_by;
1725 END IF;
1726 IF (x_vib_rec.creation_date = OKL_API.G_MISS_DATE)
1727 THEN
1728 x_vib_rec.creation_date := l_vib_rec.creation_date;
1729 END IF;
1730 IF (x_vib_rec.last_updated_by = OKL_API.G_MISS_NUM)
1731 THEN
1732 x_vib_rec.last_updated_by := l_vib_rec.last_updated_by;
1733 END IF;
1734 IF (x_vib_rec.last_update_date = OKL_API.G_MISS_DATE)
1735 THEN
1736 x_vib_rec.last_update_date := l_vib_rec.last_update_date;
1737 END IF;
1738 IF (x_vib_rec.last_update_login = OKL_API.G_MISS_NUM)
1739 THEN
1740 x_vib_rec.last_update_login := l_vib_rec.last_update_login;
1741 END IF;
1742 --Added by dpsingh for LE uptake
1743 IF (x_vib_rec.legal_entity_id = OKL_API.G_MISS_NUM)
1744 THEN
1745 x_vib_rec.legal_entity_id := l_vib_rec.legal_entity_id;
1746 END IF;
1747 END IF;
1748 RETURN(l_return_status);
1749 END populate_new_record;
1750 -------------------------------------------
1751 -- Set_Attributes for:OKL_CONTRACT_IB_H --
1752 -------------------------------------------
1753 FUNCTION Set_Attributes (
1754 p_vib_rec IN vib_rec_type,
1755 x_vib_rec OUT NOCOPY vib_rec_type
1756 ) RETURN VARCHAR2 IS
1757 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1758 BEGIN
1759 x_vib_rec := p_vib_rec;
1760 x_vib_rec.OBJECT_VERSION_NUMBER := p_vib_rec.OBJECT_VERSION_NUMBER + 1;
1761 RETURN(l_return_status);
1762 END Set_Attributes;
1763 BEGIN
1764 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1765 p_init_msg_list,
1766 '_PVT',
1767 x_return_status);
1768 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1769 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1770 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1771 RAISE OKL_API.G_EXCEPTION_ERROR;
1772 END IF;
1773 --- Setting item attributes
1774 l_return_status := Set_Attributes(
1775 p_vib_rec, -- IN
1776 l_vib_rec); -- OUT
1777 --- If any errors happen abort API
1778 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1779 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1780 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1781 RAISE OKL_API.G_EXCEPTION_ERROR;
1782 END IF;
1783 l_return_status := populate_new_record(l_vib_rec, l_def_vib_rec);
1784 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1785 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1786 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1787 RAISE OKL_API.G_EXCEPTION_ERROR;
1788 END IF;
1789 UPDATE OKL_CONTRACT_IB_H
1790 SET ID = l_def_vib_rec.id,
1791 MAJOR_VERSION = l_def_vib_rec.major_version,
1792 OBJECT_VERSION_NUMBER = l_def_vib_rec.object_version_number,
1793 DNZ_CHR_ID = l_def_vib_rec.dnz_chr_id,
1794 IB_CLE_ID = l_def_vib_rec.ib_cle_id,
1795 NAME = l_def_vib_rec.name,
1796 DESCRIPTION = l_def_vib_rec.description,
1797 INVENTORY_ITEM_ID = l_def_vib_rec.inventory_item_id,
1798 CURRENT_SERIAL_NUMBER = l_def_vib_rec.current_serial_number,
1799 INSTALL_SITE_USE_ID = l_def_vib_rec.install_site_use_id,
1800 QUANTITY = l_def_vib_rec.quantity,
1801 ATTRIBUTE_CATEGORY = l_def_vib_rec.attribute_category,
1802 ATTRIBUTE1 = l_def_vib_rec.attribute1,
1803 ATTRIBUTE2 = l_def_vib_rec.attribute2,
1804 ATTRIBUTE3 = l_def_vib_rec.attribute3,
1805 ATTRIBUTE4 = l_def_vib_rec.attribute4,
1806 ATTRIBUTE5 = l_def_vib_rec.attribute5,
1807 ATTRIBUTE6 = l_def_vib_rec.attribute6,
1808 ATTRIBUTE7 = l_def_vib_rec.attribute7,
1809 ATTRIBUTE8 = l_def_vib_rec.attribute8,
1810 ATTRIBUTE9 = l_def_vib_rec.attribute9,
1811 ATTRIBUTE10 = l_def_vib_rec.attribute10,
1812 ATTRIBUTE11 = l_def_vib_rec.attribute11,
1813 ATTRIBUTE12 = l_def_vib_rec.attribute12,
1814 ATTRIBUTE13 = l_def_vib_rec.attribute13,
1815 ATTRIBUTE14 = l_def_vib_rec.attribute14,
1816 ATTRIBUTE15 = l_def_vib_rec.attribute15,
1817 CREATED_BY = l_def_vib_rec.created_by,
1818 CREATION_DATE = l_def_vib_rec.creation_date,
1819 LAST_UPDATED_BY = l_def_vib_rec.last_updated_by,
1820 LAST_UPDATE_DATE = l_def_vib_rec.last_update_date,
1821 LAST_UPDATE_LOGIN = l_def_vib_rec.last_update_login,
1822 --Added by dpsingh for LE uptake
1823 LEGAL_ENTITY_ID = l_def_vib_rec.legal_entity_id
1824 WHERE ID = l_def_vib_rec.id;
1825 x_vib_rec := l_vib_rec;
1826 x_return_status := l_return_status;
1827 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1828 EXCEPTION
1829 WHEN OKL_API.G_EXCEPTION_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_ERROR',
1835 x_msg_count,
1836 x_msg_data,
1837 '_PVT'
1838 );
1839 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1840 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1841 (
1842 l_api_name,
1843 G_PKG_NAME,
1844 'OKL_API.G_RET_STS_UNEXP_ERROR',
1845 x_msg_count,
1846 x_msg_data,
1847 '_PVT'
1848 );
1849 WHEN OTHERS THEN
1850 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1851 (
1852 l_api_name,
1853 G_PKG_NAME,
1854 'OTHERS',
1855 x_msg_count,
1856 x_msg_data,
1857 '_PVT'
1858 );
1859 END update_row;
1860 ---------------------------------------
1861 -- update_row for:OKL_CONTRACT_IB_HV --
1862 ---------------------------------------
1863 PROCEDURE update_row(
1864 p_api_version IN NUMBER,
1865 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1866 x_return_status OUT NOCOPY VARCHAR2,
1867 x_msg_count OUT NOCOPY NUMBER,
1868 x_msg_data OUT NOCOPY VARCHAR2,
1869 p_vibv_rec IN vibv_rec_type,
1870 x_vibv_rec OUT NOCOPY vibv_rec_type) IS
1871
1872 l_api_version CONSTANT NUMBER := 1;
1873 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1874 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1875 l_vibv_rec vibv_rec_type := p_vibv_rec;
1876 l_def_vibv_rec vibv_rec_type;
1877 l_db_vibv_rec vibv_rec_type;
1878 l_vib_rec vib_rec_type;
1879 lx_vib_rec vib_rec_type;
1880 -------------------------------
1881 -- FUNCTION fill_who_columns --
1882 -------------------------------
1883 FUNCTION fill_who_columns (
1884 p_vibv_rec IN vibv_rec_type
1885 ) RETURN vibv_rec_type IS
1886 l_vibv_rec vibv_rec_type := p_vibv_rec;
1887 BEGIN
1888 l_vibv_rec.LAST_UPDATE_DATE := SYSDATE;
1889 l_vibv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1890 l_vibv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1891 RETURN(l_vibv_rec);
1892 END fill_who_columns;
1893 ----------------------------------
1894 -- FUNCTION populate_new_record --
1895 ----------------------------------
1896 FUNCTION populate_new_record (
1897 p_vibv_rec IN vibv_rec_type,
1898 x_vibv_rec OUT NOCOPY vibv_rec_type
1899 ) RETURN VARCHAR2 IS
1900 l_row_notfound BOOLEAN := TRUE;
1901 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1902 BEGIN
1903 x_vibv_rec := p_vibv_rec;
1904 l_db_vibv_rec := get_rec(p_vibv_rec, l_return_status);
1905 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1906 IF (x_vibv_rec.id = OKL_API.G_MISS_NUM)
1907 THEN
1908 x_vibv_rec.id := l_db_vibv_rec.id;
1909 END IF;
1910 IF (x_vibv_rec.major_version = OKL_API.G_MISS_NUM)
1911 THEN
1912 x_vibv_rec.major_version := l_db_vibv_rec.major_version;
1913 END IF;
1914 IF (x_vibv_rec.object_version_number = OKL_API.G_MISS_NUM)
1915 THEN
1916 x_vibv_rec.object_version_number := l_db_vibv_rec.object_version_number;
1917 END IF;
1918 IF (x_vibv_rec.dnz_chr_id = OKL_API.G_MISS_NUM)
1919 THEN
1920 x_vibv_rec.dnz_chr_id := l_db_vibv_rec.dnz_chr_id;
1921 END IF;
1922 IF (x_vibv_rec.ib_cle_id = OKL_API.G_MISS_NUM)
1923 THEN
1924 x_vibv_rec.ib_cle_id := l_db_vibv_rec.ib_cle_id;
1925 END IF;
1926 IF (x_vibv_rec.name = OKL_API.G_MISS_NUM)
1927 THEN
1928 x_vibv_rec.name := l_db_vibv_rec.name;
1929 END IF;
1930 IF (x_vibv_rec.description = OKL_API.G_MISS_CHAR)
1931 THEN
1932 x_vibv_rec.description := l_db_vibv_rec.description;
1933 END IF;
1934 IF (x_vibv_rec.inventory_item_id = OKL_API.G_MISS_NUM)
1935 THEN
1936 x_vibv_rec.inventory_item_id := l_db_vibv_rec.inventory_item_id;
1937 END IF;
1938 IF (x_vibv_rec.current_serial_number = OKL_API.G_MISS_CHAR)
1939 THEN
1940 x_vibv_rec.current_serial_number := l_db_vibv_rec.current_serial_number;
1941 END IF;
1942 IF (x_vibv_rec.install_site_use_id = OKL_API.G_MISS_NUM)
1943 THEN
1944 x_vibv_rec.install_site_use_id := l_db_vibv_rec.install_site_use_id;
1945 END IF;
1946 IF (x_vibv_rec.quantity = OKL_API.G_MISS_NUM)
1947 THEN
1948 x_vibv_rec.quantity := l_db_vibv_rec.quantity;
1949 END IF;
1950 IF (x_vibv_rec.attribute_category = OKL_API.G_MISS_CHAR)
1951 THEN
1952 x_vibv_rec.attribute_category := l_db_vibv_rec.attribute_category;
1953 END IF;
1954 IF (x_vibv_rec.attribute1 = OKL_API.G_MISS_CHAR)
1955 THEN
1956 x_vibv_rec.attribute1 := l_db_vibv_rec.attribute1;
1957 END IF;
1958 IF (x_vibv_rec.attribute2 = OKL_API.G_MISS_CHAR)
1959 THEN
1960 x_vibv_rec.attribute2 := l_db_vibv_rec.attribute2;
1961 END IF;
1962 IF (x_vibv_rec.attribute3 = OKL_API.G_MISS_CHAR)
1963 THEN
1964 x_vibv_rec.attribute3 := l_db_vibv_rec.attribute3;
1965 END IF;
1966 IF (x_vibv_rec.attribute4 = OKL_API.G_MISS_CHAR)
1967 THEN
1968 x_vibv_rec.attribute4 := l_db_vibv_rec.attribute4;
1969 END IF;
1970 IF (x_vibv_rec.attribute5 = OKL_API.G_MISS_CHAR)
1971 THEN
1972 x_vibv_rec.attribute5 := l_db_vibv_rec.attribute5;
1973 END IF;
1974 IF (x_vibv_rec.attribute6 = OKL_API.G_MISS_CHAR)
1975 THEN
1976 x_vibv_rec.attribute6 := l_db_vibv_rec.attribute6;
1977 END IF;
1978 IF (x_vibv_rec.attribute7 = OKL_API.G_MISS_CHAR)
1979 THEN
1980 x_vibv_rec.attribute7 := l_db_vibv_rec.attribute7;
1981 END IF;
1982 IF (x_vibv_rec.attribute8 = OKL_API.G_MISS_CHAR)
1983 THEN
1984 x_vibv_rec.attribute8 := l_db_vibv_rec.attribute8;
1985 END IF;
1986 IF (x_vibv_rec.attribute9 = OKL_API.G_MISS_CHAR)
1987 THEN
1988 x_vibv_rec.attribute9 := l_db_vibv_rec.attribute9;
1989 END IF;
1990 IF (x_vibv_rec.attribute10 = OKL_API.G_MISS_CHAR)
1991 THEN
1992 x_vibv_rec.attribute10 := l_db_vibv_rec.attribute10;
1993 END IF;
1994 IF (x_vibv_rec.attribute11 = OKL_API.G_MISS_CHAR)
1995 THEN
1996 x_vibv_rec.attribute11 := l_db_vibv_rec.attribute11;
1997 END IF;
1998 IF (x_vibv_rec.attribute12 = OKL_API.G_MISS_CHAR)
1999 THEN
2000 x_vibv_rec.attribute12 := l_db_vibv_rec.attribute12;
2001 END IF;
2002 IF (x_vibv_rec.attribute13 = OKL_API.G_MISS_CHAR)
2003 THEN
2004 x_vibv_rec.attribute13 := l_db_vibv_rec.attribute13;
2005 END IF;
2006 IF (x_vibv_rec.attribute14 = OKL_API.G_MISS_CHAR)
2007 THEN
2008 x_vibv_rec.attribute14 := l_db_vibv_rec.attribute14;
2009 END IF;
2010 IF (x_vibv_rec.attribute15 = OKL_API.G_MISS_CHAR)
2011 THEN
2012 x_vibv_rec.attribute15 := l_db_vibv_rec.attribute15;
2013 END IF;
2014 IF (x_vibv_rec.created_by = OKL_API.G_MISS_NUM)
2015 THEN
2016 x_vibv_rec.created_by := l_db_vibv_rec.created_by;
2017 END IF;
2018 IF (x_vibv_rec.creation_date = OKL_API.G_MISS_DATE)
2019 THEN
2020 x_vibv_rec.creation_date := l_db_vibv_rec.creation_date;
2021 END IF;
2022 IF (x_vibv_rec.last_updated_by = OKL_API.G_MISS_NUM)
2023 THEN
2024 x_vibv_rec.last_updated_by := l_db_vibv_rec.last_updated_by;
2025 END IF;
2026 IF (x_vibv_rec.last_update_date = OKL_API.G_MISS_DATE)
2027 THEN
2028 x_vibv_rec.last_update_date := l_db_vibv_rec.last_update_date;
2029 END IF;
2030 IF (x_vibv_rec.last_update_login = OKL_API.G_MISS_NUM)
2031 THEN
2032 x_vibv_rec.last_update_login := l_db_vibv_rec.last_update_login;
2033 END IF;
2034 --Added by dpsingh for LE uptake
2035 IF (x_vibv_rec.legal_entity_id = OKL_API.G_MISS_NUM)
2036 THEN
2037 x_vibv_rec.legal_entity_id := l_db_vibv_rec.legal_entity_id;
2038 END IF;
2039 END IF;
2040 RETURN(l_return_status);
2041 END populate_new_record;
2042 -------------------------------------------
2043 -- Set_Attributes for:OKL_CONTRACT_IB_HV --
2044 -------------------------------------------
2045 FUNCTION Set_Attributes (
2046 p_vibv_rec IN vibv_rec_type,
2047 x_vibv_rec OUT NOCOPY vibv_rec_type
2048 ) RETURN VARCHAR2 IS
2049 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2050 BEGIN
2051 x_vibv_rec := p_vibv_rec;
2052 RETURN(l_return_status);
2053 END Set_Attributes;
2054 BEGIN
2055 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2056 G_PKG_NAME,
2057 p_init_msg_list,
2058 l_api_version,
2059 p_api_version,
2060 '_PVT',
2061 x_return_status);
2062 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2063 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2064 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2065 RAISE OKL_API.G_EXCEPTION_ERROR;
2066 END IF;
2067 --- Setting item attributes
2068 l_return_status := Set_Attributes(
2069 p_vibv_rec, -- IN
2070 x_vibv_rec); -- OUT
2071 --- If any errors happen abort API
2072 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2073 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2074 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2075 RAISE OKL_API.G_EXCEPTION_ERROR;
2076 END IF;
2077 l_return_status := populate_new_record(l_vibv_rec, l_def_vibv_rec);
2078 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2079 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2080 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2081 RAISE OKL_API.G_EXCEPTION_ERROR;
2082 END IF;
2083 l_def_vibv_rec := fill_who_columns(l_def_vibv_rec);
2084 --- Validate all non-missing attributes (Item Level Validation)
2085 l_return_status := Validate_Attributes(l_def_vibv_rec);
2086 --- If any errors happen abort API
2087 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2088 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2089 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2090 RAISE OKL_API.G_EXCEPTION_ERROR;
2091 END IF;
2092 l_return_status := Validate_Record(l_def_vibv_rec);
2093 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2094 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2095 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2096 RAISE OKL_API.G_EXCEPTION_ERROR;
2097 END IF;
2098 -----------------------------------------
2099 -- Move VIEW record to "Child" records --
2100 -----------------------------------------
2101 migrate(l_def_vibv_rec, l_vib_rec);
2102 -----------------------------------------------
2103 -- Call the UPDATE_ROW for each child record --
2104 -----------------------------------------------
2105 update_row(
2106 p_init_msg_list,
2107 l_return_status,
2108 x_msg_count,
2109 x_msg_data,
2110 l_vib_rec,
2111 lx_vib_rec
2112 );
2113 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2114 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2115 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2116 RAISE OKL_API.G_EXCEPTION_ERROR;
2117 END IF;
2118 migrate(lx_vib_rec, l_def_vibv_rec);
2119 x_vibv_rec := l_def_vibv_rec;
2120 x_return_status := l_return_status;
2121 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2122 EXCEPTION
2123 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2124 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2125 (
2126 l_api_name,
2127 G_PKG_NAME,
2128 'OKL_API.G_RET_STS_ERROR',
2129 x_msg_count,
2130 x_msg_data,
2131 '_PVT'
2132 );
2133 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2134 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2135 (
2136 l_api_name,
2137 G_PKG_NAME,
2138 'OKL_API.G_RET_STS_UNEXP_ERROR',
2139 x_msg_count,
2140 x_msg_data,
2141 '_PVT'
2142 );
2143 WHEN OTHERS THEN
2144 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2145 (
2146 l_api_name,
2147 G_PKG_NAME,
2148 'OTHERS',
2149 x_msg_count,
2150 x_msg_data,
2151 '_PVT'
2152 );
2153 END update_row;
2154 ----------------------------------------
2155 -- PL/SQL TBL update_row for:vibv_tbl --
2156 ----------------------------------------
2157 PROCEDURE update_row(
2158 p_api_version IN NUMBER,
2159 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2160 x_return_status OUT NOCOPY VARCHAR2,
2161 x_msg_count OUT NOCOPY NUMBER,
2162 x_msg_data OUT NOCOPY VARCHAR2,
2163 p_vibv_tbl IN vibv_tbl_type,
2164 x_vibv_tbl OUT NOCOPY vibv_tbl_type) IS
2165
2166 l_api_version CONSTANT NUMBER := 1;
2167 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2168 i NUMBER := 0;
2169 BEGIN
2170 OKL_API.init_msg_list(p_init_msg_list);
2171 -- Make sure PL/SQL table has records in it before passing
2172 IF (p_vibv_tbl.COUNT > 0) THEN
2173 i := p_vibv_tbl.FIRST;
2174 LOOP
2175 update_row (
2176 p_api_version => p_api_version,
2177 p_init_msg_list => p_init_msg_list,
2178 x_return_status => x_return_status,
2179 x_msg_count => x_msg_count,
2180 x_msg_data => x_msg_data,
2181 p_vibv_rec => p_vibv_tbl(i),
2182 x_vibv_rec => x_vibv_tbl(i));
2183 EXIT WHEN (i = p_vibv_tbl.LAST);
2184 i := p_vibv_tbl.NEXT(i);
2185 END LOOP;
2186 END IF;
2187 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2188 EXCEPTION
2189 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2190 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2191 (
2192 l_api_name,
2193 G_PKG_NAME,
2194 'OKL_API.G_RET_STS_ERROR',
2195 x_msg_count,
2196 x_msg_data,
2197 '_PVT'
2198 );
2199 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2200 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2201 (
2202 l_api_name,
2203 G_PKG_NAME,
2204 'OKL_API.G_RET_STS_UNEXP_ERROR',
2205 x_msg_count,
2206 x_msg_data,
2207 '_PVT'
2208 );
2209 WHEN OTHERS THEN
2210 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2211 (
2212 l_api_name,
2213 G_PKG_NAME,
2214 'OTHERS',
2215 x_msg_count,
2216 x_msg_data,
2217 '_PVT'
2218 );
2219 END update_row;
2220 ---------------------------------------------------------------------------
2221 -- PROCEDURE delete_row
2222 ---------------------------------------------------------------------------
2223 ---------------------------------------
2224 -- delete_row for:OKL_CONTRACT_IB_HV --
2225 ---------------------------------------
2226 PROCEDURE delete_row(
2227 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2228 x_return_status OUT NOCOPY VARCHAR2,
2229 x_msg_count OUT NOCOPY NUMBER,
2230 x_msg_data OUT NOCOPY VARCHAR2,
2231 p_vib_rec IN vib_rec_type) IS
2232
2233 l_api_version CONSTANT NUMBER := 1;
2234 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2235 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2236 l_vib_rec vib_rec_type := p_vib_rec;
2237 l_row_notfound BOOLEAN := TRUE;
2238 BEGIN
2239 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2240 p_init_msg_list,
2241 '_PVT',
2242 x_return_status);
2243 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2244 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2245 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2246 RAISE OKL_API.G_EXCEPTION_ERROR;
2247 END IF;
2248
2249 DELETE FROM OKL_CONTRACT_IB_H
2250 WHERE ID = l_vib_rec.id;
2251 x_return_status := l_return_status;
2252 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2253 EXCEPTION
2254 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2255 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2256 (
2257 l_api_name,
2258 G_PKG_NAME,
2259 'OKL_API.G_RET_STS_ERROR',
2260 x_msg_count,
2261 x_msg_data,
2262 '_PVT'
2263 );
2264 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2265 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2266 (
2267 l_api_name,
2268 G_PKG_NAME,
2269 'OKL_API.G_RET_STS_UNEXP_ERROR',
2270 x_msg_count,
2271 x_msg_data,
2272 '_PVT'
2273 );
2274 WHEN OTHERS THEN
2275 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2276 (
2277 l_api_name,
2278 G_PKG_NAME,
2279 'OTHERS',
2280 x_msg_count,
2281 x_msg_data,
2282 '_PVT'
2283 );
2284 END delete_row;
2285 ---------------------------------------
2286 -- delete_row for:OKL_CONTRACT_IB_HV --
2287 ---------------------------------------
2288 PROCEDURE delete_row(
2289 p_api_version IN NUMBER,
2290 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2291 x_return_status OUT NOCOPY VARCHAR2,
2292 x_msg_count OUT NOCOPY NUMBER,
2293 x_msg_data OUT NOCOPY VARCHAR2,
2294 p_vibv_rec IN vibv_rec_type) IS
2295
2296 l_api_version CONSTANT NUMBER := 1;
2297 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2298 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2299 l_vibv_rec vibv_rec_type := p_vibv_rec;
2300 l_vib_rec vib_rec_type;
2301 BEGIN
2302 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2303 G_PKG_NAME,
2304 p_init_msg_list,
2305 l_api_version,
2306 p_api_version,
2307 '_PVT',
2308 x_return_status);
2309 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2310 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2311 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2312 RAISE OKL_API.G_EXCEPTION_ERROR;
2313 END IF;
2314 -----------------------------------------
2315 -- Move VIEW record to "Child" records --
2316 -----------------------------------------
2317 migrate(l_vibv_rec, l_vib_rec);
2318 -----------------------------------------------
2319 -- Call the DELETE_ROW for each child record --
2320 -----------------------------------------------
2321 delete_row(
2322 p_init_msg_list,
2323 l_return_status,
2324 x_msg_count,
2325 x_msg_data,
2326 l_vib_rec
2327 );
2328 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2329 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2330 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2331 RAISE OKL_API.G_EXCEPTION_ERROR;
2332 END IF;
2333 x_return_status := l_return_status;
2334 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2335 EXCEPTION
2336 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2337 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2338 (
2339 l_api_name,
2340 G_PKG_NAME,
2341 'OKL_API.G_RET_STS_ERROR',
2342 x_msg_count,
2343 x_msg_data,
2344 '_PVT'
2345 );
2346 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2347 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2348 (
2349 l_api_name,
2350 G_PKG_NAME,
2351 'OKL_API.G_RET_STS_UNEXP_ERROR',
2352 x_msg_count,
2353 x_msg_data,
2354 '_PVT'
2355 );
2356 WHEN OTHERS THEN
2357 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2358 (
2359 l_api_name,
2360 G_PKG_NAME,
2361 'OTHERS',
2362 x_msg_count,
2363 x_msg_data,
2364 '_PVT'
2365 );
2366 END delete_row;
2367 --------------------------------------------------
2368 -- PL/SQL TBL delete_row for:OKL_CONTRACT_IB_HV --
2369 --------------------------------------------------
2370 PROCEDURE delete_row(
2371 p_api_version IN NUMBER,
2372 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2373 x_return_status OUT NOCOPY VARCHAR2,
2374 x_msg_count OUT NOCOPY NUMBER,
2375 x_msg_data OUT NOCOPY VARCHAR2,
2376 p_vibv_tbl IN vibv_tbl_type) IS
2377
2378 l_api_version CONSTANT NUMBER := 1;
2379 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2380 i NUMBER := 0;
2381 BEGIN
2382 OKL_API.init_msg_list(p_init_msg_list);
2383 -- Make sure PL/SQL table has records in it before passing
2384 IF (p_vibv_tbl.COUNT > 0) THEN
2385 i := p_vibv_tbl.FIRST;
2386 LOOP
2387 delete_row (
2388 p_api_version => p_api_version,
2389 p_init_msg_list => p_init_msg_list,
2390 x_return_status => x_return_status,
2391 x_msg_count => x_msg_count,
2392 x_msg_data => x_msg_data,
2393 p_vibv_rec => p_vibv_tbl(i));
2394 EXIT WHEN (i = p_vibv_tbl.LAST);
2395 i := p_vibv_tbl.NEXT(i);
2396 END LOOP;
2397 END IF;
2398 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2399 EXCEPTION
2400 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2401 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2402 (
2403 l_api_name,
2404 G_PKG_NAME,
2405 'OKL_API.G_RET_STS_ERROR',
2406 x_msg_count,
2407 x_msg_data,
2408 '_PVT'
2409 );
2410 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2411 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2412 (
2413 l_api_name,
2414 G_PKG_NAME,
2415 'OKL_API.G_RET_STS_UNEXP_ERROR',
2416 x_msg_count,
2417 x_msg_data,
2418 '_PVT'
2419 );
2420 WHEN OTHERS THEN
2421 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2422 (
2423 l_api_name,
2424 G_PKG_NAME,
2425 'OTHERS',
2426 x_msg_count,
2427 x_msg_data,
2428 '_PVT'
2429 );
2430 END delete_row;
2431 END OKL_VIB_PVT;