[Home] [Help]
PACKAGE BODY: APPS.OKC_CVM_PVT
Source
1 PACKAGE BODY OKC_CVM_PVT AS
2 /* $Header: OKCSCVMB.pls 120.2 2006/05/24 23:06:54 tweichen noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --bugfix 5243626
7 g_defer_min_vers_upd VARCHAR2(1) := FND_API.G_FALSE;
8
9
10 /************************ HAND-CODED *********************************/
11 FUNCTION Validate_Attributes ( p_cvmv_rec IN cvmv_rec_type)
12 RETURN VARCHAR2;
13 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
14 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
15 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
16 G_EXCEPTION_HALT_VALIDATION exception;
17 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
18 G_NO_VERSOIN_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_VERSOIN_RECORD';
19 G_NULL_CHR_ID CONSTANT VARCHAR2(200) := 'OKC_NULL_CHR_ID';
20
21 -- Global transaction id
22 -- g_trans_id VARCHAR2(100) := 'XXX';
23 /************************ HAND-CODED ENDS ****************************/
24
25 -- Validate Coulumn procedures
26
27 -- Start of comments
28 --
29 -- Procedure Name : validate_chr_id
30 -- Description :
31 -- Business Rules :
32 -- Parameters :
33 -- Version : 1.0
34 -- End of comments
35 PROCEDURE validate_chr_id(x_return_status OUT NOCOPY VARCHAR2,
36 p_cvmv_rec IN cvmv_rec_type) is
37
38 l_dummy_var VARCHAR2(1) := '?';
39 Cursor l_chrv_csr Is
40 select 'x'
41 from OKC_K_HEADERS_ALL_B -- Modified by Jvorugan for Bug:4645341 okc_k_headers_b
42 where ID = p_cvmv_rec.chr_id;
43 Begin
44 -- initialize return status
45 x_return_status := OKC_API.G_RET_STS_SUCCESS;
46
47 -- check that data exists
48 If (p_cvmv_rec.chr_id = OKC_API.G_MISS_NUM or
49 p_cvmv_rec.chr_id IS NULL)
50 Then
51 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
52 p_msg_name => g_required_value,
53 p_token1 => g_col_name_token,
54 p_token1_value => 'chr_id');
55 -- set error flag
56 x_return_status := OKC_API.G_RET_STS_ERROR;
57
58 -- halt validation
59 raise G_EXCEPTION_HALT_VALIDATION;
60 End If;
61
62 -- enforce foreign key
63 Open l_chrv_csr;
64 Fetch l_chrv_csr Into l_dummy_var;
65 Close l_chrv_csr;
66
67 -- if l_dummy_var still set to default, data was not found
68 If (l_dummy_var = '?') Then
69 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
70 p_msg_name => g_no_parent_record,
71 p_token1 => g_col_name_token,
72 p_token1_value => 'chr_id',
73 p_token2 => g_child_table_token,
74 p_token2_value => 'OKC_K_HEADERS_V',
75 p_token3 => g_parent_table_token,
76 p_token3_value => 'OKC_K_HEADERS_V');
77
78 -- notify caller of an error
79 x_return_status := OKC_API.G_RET_STS_ERROR;
80 End If;
81
82 exception
83 when G_EXCEPTION_HALT_VALIDATION then
84 -- no processing necessary; validation can continue with next column
85 null;
86
87 when OTHERS then
88 -- store SQL error message on message stack
89 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
90 p_msg_name => g_unexpected_error,
91 p_token1 => g_sqlcode_token,
92 p_token1_value => sqlcode,
93 p_token2 => g_sqlerrm_token,
94 p_token2_value => sqlerrm);
95 -- set error flag as UNEXPETED error
96 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
97
98 -- verify that cursor was closed
99 if l_chrv_csr%ISOPEN then
100 close l_chrv_csr;
101 end if;
102
103 End validate_chr_id;
104
105 ---------------------------------------------------------------------------
106 -- FUNCTION get_rec for: OKC_K_VERS_NUMBERS
107 ---------------------------------------------------------------------------
108 FUNCTION get_rec (
109 p_cvm_rec IN cvm_rec_type,
110 x_no_data_found OUT NOCOPY BOOLEAN
111 ) RETURN cvm_rec_type IS
112 CURSOR cvm_pk_csr (p_chr_id IN NUMBER) IS
113 SELECT
114 CHR_ID,
115 MAJOR_VERSION,
116 MINOR_VERSION,
117 OBJECT_VERSION_NUMBER,
118 CREATED_BY,
119 CREATION_DATE,
120 LAST_UPDATED_BY,
121 LAST_UPDATE_DATE,
122 LAST_UPDATE_LOGIN
123 FROM Okc_K_Vers_Numbers
124 WHERE okc_k_vers_numbers.chr_id = p_chr_id;
125 l_cvm_pk cvm_pk_csr%ROWTYPE;
126 l_cvm_rec cvm_rec_type;
127 BEGIN
128 x_no_data_found := TRUE;
129 -- Get current database values
130 OPEN cvm_pk_csr (p_cvm_rec.chr_id);
131 FETCH cvm_pk_csr INTO
132 l_cvm_rec.CHR_ID,
133 l_cvm_rec.MAJOR_VERSION,
134 l_cvm_rec.MINOR_VERSION,
135 l_cvm_rec.OBJECT_VERSION_NUMBER,
136 l_cvm_rec.CREATED_BY,
137 l_cvm_rec.CREATION_DATE,
138 l_cvm_rec.LAST_UPDATED_BY,
139 l_cvm_rec.LAST_UPDATE_DATE,
140 l_cvm_rec.LAST_UPDATE_LOGIN;
141 x_no_data_found := cvm_pk_csr%NOTFOUND;
142 CLOSE cvm_pk_csr;
143 RETURN(l_cvm_rec);
144 END get_rec;
145
146 FUNCTION get_rec (
147 p_cvm_rec IN cvm_rec_type
148 ) RETURN cvm_rec_type IS
149 l_row_notfound BOOLEAN := TRUE;
150 BEGIN
151 RETURN(get_rec(p_cvm_rec, l_row_notfound));
152 END get_rec;
153 ---------------------------------------------------------------------------
154 -- FUNCTION get_rec for: OKC_K_VERS_NUMBERS_V
155 ---------------------------------------------------------------------------
156 FUNCTION get_rec (
157 p_cvmv_rec IN cvmv_rec_type,
158 x_no_data_found OUT NOCOPY BOOLEAN
159 ) RETURN cvmv_rec_type IS
160 CURSOR okc_cvmv_pk_csr (p_chr_id IN NUMBER) IS
161 SELECT
162 CHR_ID,
163 OBJECT_VERSION_NUMBER,
164 MAJOR_VERSION,
165 MINOR_VERSION,
166 CREATED_BY,
167 CREATION_DATE,
168 LAST_UPDATED_BY,
169 LAST_UPDATE_DATE,
170 LAST_UPDATE_LOGIN
171 FROM Okc_K_Vers_Numbers_V
172 WHERE okc_k_vers_numbers_v.chr_id = p_chr_id;
173 l_okc_cvmv_pk okc_cvmv_pk_csr%ROWTYPE;
174 l_cvmv_rec cvmv_rec_type;
175 BEGIN
176 x_no_data_found := TRUE;
177 -- Get current database values
178 OPEN okc_cvmv_pk_csr (p_cvmv_rec.chr_id);
179 FETCH okc_cvmv_pk_csr INTO
180 l_cvmv_rec.CHR_ID,
181 l_cvmv_rec.OBJECT_VERSION_NUMBER,
182 l_cvmv_rec.MAJOR_VERSION,
183 l_cvmv_rec.MINOR_VERSION,
184 l_cvmv_rec.CREATED_BY,
185 l_cvmv_rec.CREATION_DATE,
186 l_cvmv_rec.LAST_UPDATED_BY,
187 l_cvmv_rec.LAST_UPDATE_DATE,
188 l_cvmv_rec.LAST_UPDATE_LOGIN;
189 x_no_data_found := okc_cvmv_pk_csr%NOTFOUND;
190 CLOSE okc_cvmv_pk_csr;
191 RETURN(l_cvmv_rec);
192 END get_rec;
193
194 FUNCTION get_rec (
195 p_cvmv_rec IN cvmv_rec_type
196 ) RETURN cvmv_rec_type IS
197 l_row_notfound BOOLEAN := TRUE;
198 BEGIN
199 RETURN(get_rec(p_cvmv_rec, l_row_notfound));
200 END get_rec;
201
202 ----------------------------------------------------------
203 -- FUNCTION null_out_defaults for: OKC_K_VERS_NUMBERS_V --
204 ----------------------------------------------------------
205 FUNCTION null_out_defaults (
206 p_cvmv_rec IN cvmv_rec_type
207 ) RETURN cvmv_rec_type IS
208 l_cvmv_rec cvmv_rec_type := p_cvmv_rec;
209 BEGIN
210 IF (l_cvmv_rec.chr_id = OKC_API.G_MISS_NUM) THEN
211 l_cvmv_rec.chr_id := NULL;
212 END IF;
213 IF (l_cvmv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
214 l_cvmv_rec.object_version_number := NULL;
215 END IF;
216 IF (l_cvmv_rec.major_version = OKC_API.G_MISS_NUM) THEN
217 l_cvmv_rec.major_version := NULL;
218 END IF;
219 IF (l_cvmv_rec.minor_version = OKC_API.G_MISS_NUM) THEN
220 l_cvmv_rec.minor_version := NULL;
221 END IF;
222 IF (l_cvmv_rec.created_by = OKC_API.G_MISS_NUM) THEN
223 l_cvmv_rec.created_by := NULL;
224 END IF;
225 IF (l_cvmv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
226 l_cvmv_rec.creation_date := NULL;
227 END IF;
228 IF (l_cvmv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
229 l_cvmv_rec.last_updated_by := NULL;
230 END IF;
231 IF (l_cvmv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
232 l_cvmv_rec.last_update_date := NULL;
233 END IF;
234 IF (l_cvmv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
235 l_cvmv_rec.last_update_login := NULL;
236 END IF;
237 RETURN(l_cvmv_rec);
238 END null_out_defaults;
239 ---------------------------------------------------------------------------
240 -- PROCEDURE Validate_Attributes
241 ---------------------------------------------------------------------------
242 --------------------------------------------------
243 -- Validate_Attributes for:OKC_K_VERS_NUMBERS_V --
244 --------------------------------------------------
245 FUNCTION Validate_Attributes (
246 p_cvmv_rec IN cvmv_rec_type
247 ) RETURN VARCHAR2 IS
248 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
249 BEGIN
250 validate_chr_id (x_return_status => l_return_status,
251 p_cvmv_rec => p_cvmv_rec);
252 RETURN(l_return_status);
253 END Validate_Attributes;
254
255 ---------------------------------------------------------------------------
256 -- PROCEDURE Validate_Record
257 ---------------------------------------------------------------------------
258 ----------------------------------------------
259 -- Validate_Record for:OKC_K_VERS_NUMBERS_V --
260 ----------------------------------------------
261 FUNCTION Validate_Record (
262 p_cvmv_rec IN cvmv_rec_type
263 ) RETURN VARCHAR2 IS
264 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
265 BEGIN
266 RETURN (l_return_status);
267 END Validate_Record;
268
269 ---------------------------------------------------------------------------
270 -- PROCEDURE Migrate
271 ---------------------------------------------------------------------------
272 PROCEDURE migrate (
273 p_from IN cvmv_rec_type,
274 p_to OUT NOCOPY cvm_rec_type
275 ) IS
276 BEGIN
277 p_to.chr_id := p_from.chr_id;
278 p_to.major_version := p_from.major_version;
279 p_to.minor_version := p_from.minor_version;
280 p_to.object_version_number := p_from.object_version_number;
281 p_to.created_by := p_from.created_by;
282 p_to.creation_date := p_from.creation_date;
283 p_to.last_updated_by := p_from.last_updated_by;
284 p_to.last_update_date := p_from.last_update_date;
285 p_to.last_update_login := p_from.last_update_login;
286 END migrate;
287 PROCEDURE migrate (
288 p_from IN cvm_rec_type,
289 p_to OUT NOCOPY cvmv_rec_type
290 ) IS
291 BEGIN
292 p_to.chr_id := p_from.chr_id;
293 p_to.major_version := p_from.major_version;
294 p_to.minor_version := p_from.minor_version;
295 p_to.object_version_number := p_from.object_version_number;
296 p_to.created_by := p_from.created_by;
297 p_to.creation_date := p_from.creation_date;
298 p_to.last_updated_by := p_from.last_updated_by;
299 p_to.last_update_date := p_from.last_update_date;
300 p_to.last_update_login := p_from.last_update_login;
301 END migrate;
302 PROCEDURE migrate (
303 p_from IN cvm_rec_type,
304 p_to OUT NOCOPY okc_k_vers_numbers_h_rec_type
305 ) IS
306 BEGIN
307 p_to.chr_id := p_from.chr_id;
308 p_to.major_version := p_from.major_version;
309 p_to.minor_version := p_from.minor_version;
310 p_to.object_version_number := p_from.object_version_number;
311 p_to.created_by := p_from.created_by;
312 p_to.creation_date := p_from.creation_date;
313 p_to.last_updated_by := p_from.last_updated_by;
314 p_to.last_update_date := p_from.last_update_date;
315 p_to.last_update_login := p_from.last_update_login;
316 END migrate;
317
318 ---------------------------------------------------------------------------
319 -- PROCEDURE insert_row
320 ---------------------------------------------------------------------------
321 -----------------------------------------
322 -- insert_row for:OKC_K_VERS_NUMBERS_H --
323 -----------------------------------------
324 PROCEDURE insert_row(
325 p_init_msg_list IN VARCHAR2 ,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2,
329 p_okc_k_vers_numbers_h_rec IN okc_k_vers_numbers_h_rec_type,
330 x_okc_k_vers_numbers_h_rec OUT NOCOPY okc_k_vers_numbers_h_rec_type) IS
331
332 l_api_version CONSTANT NUMBER := 1;
333 l_api_name CONSTANT VARCHAR2(30) := 'H_insert_row';
334 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
335 l_okc_k_vers_numbers_h_rec okc_k_vers_numbers_h_rec_type := p_okc_k_vers_numbers_h_rec;
336 ldefokckversnumbershrec okc_k_vers_numbers_h_rec_type;
337 ---------------------------------------------
338 -- Set_Attributes for:OKC_K_VERS_NUMBERS_H --
339 ---------------------------------------------
340 FUNCTION Set_Attributes (
341 p_okc_k_vers_numbers_h_rec IN okc_k_vers_numbers_h_rec_type,
342 x_okc_k_vers_numbers_h_rec OUT NOCOPY okc_k_vers_numbers_h_rec_type
343 ) RETURN VARCHAR2 IS
344 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
345 BEGIN
346 x_okc_k_vers_numbers_h_rec := p_okc_k_vers_numbers_h_rec;
347 RETURN(l_return_status);
348 END Set_Attributes;
349 BEGIN
350 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
351 p_init_msg_list,
352 '_PVT',
353 x_return_status);
354 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
355 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
356 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
357 RAISE OKC_API.G_EXCEPTION_ERROR;
358 END IF;
359 --- Setting item attributes
360 l_return_status := Set_Attributes(
361 p_okc_k_vers_numbers_h_rec, -- IN
362 l_okc_k_vers_numbers_h_rec); -- OUT
363 --- If any errors happen abort API
364 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
365 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
366 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
367 RAISE OKC_API.G_EXCEPTION_ERROR;
368 END IF;
369 INSERT INTO OKC_K_VERS_NUMBERS_H(
370 chr_id,
371 major_version,
372 minor_version,
373 object_version_number,
374 created_by,
375 creation_date,
376 last_updated_by,
377 last_update_date,
378 last_update_login)
379 VALUES (
380 l_okc_k_vers_numbers_h_rec.chr_id,
381 l_okc_k_vers_numbers_h_rec.major_version,
382 l_okc_k_vers_numbers_h_rec.minor_version,
383 l_okc_k_vers_numbers_h_rec.object_version_number,
384 l_okc_k_vers_numbers_h_rec.created_by,
385 l_okc_k_vers_numbers_h_rec.creation_date,
386 l_okc_k_vers_numbers_h_rec.last_updated_by,
387 l_okc_k_vers_numbers_h_rec.last_update_date,
388 l_okc_k_vers_numbers_h_rec.last_update_login);
389 -- Set OUT values
390 x_okc_k_vers_numbers_h_rec := l_okc_k_vers_numbers_h_rec;
391 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
392 EXCEPTION
393 WHEN OKC_API.G_EXCEPTION_ERROR THEN
394 x_return_status := OKC_API.HANDLE_EXCEPTIONS
395 (
396 l_api_name,
397 G_PKG_NAME,
398 'OKC_API.G_RET_STS_ERROR',
399 x_msg_count,
400 x_msg_data,
401 '_PVT'
402 );
403 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
404 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
405 (
406 l_api_name,
407 G_PKG_NAME,
408 'OKC_API.G_RET_STS_UNEXP_ERROR',
409 x_msg_count,
410 x_msg_data,
411 '_PVT'
412 );
413 WHEN OTHERS THEN
414 --
415 -- If another user is updating the contract, this will cause
416 -- unique constrained violation on this table (SQLCODE = -1)
417 --
418 If SQLCODE = -1 Then
419 x_return_status := OKC_API.G_RET_STS_ERROR;
420 OKC_API.set_message(G_APP_NAME,'OKC_OP_FAILED');
421 Else
422 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
423 (
424 l_api_name,
425 G_PKG_NAME,
426 'OTHERS',
427 x_msg_count,
428 x_msg_data,
429 '_PVT'
430 );
431 End If;
432 END insert_row;
433 ---------------------------------------
434 -- insert_row for:OKC_K_VERS_NUMBERS --
435 ---------------------------------------
436 PROCEDURE insert_row(
437 p_init_msg_list IN VARCHAR2 ,
438 x_return_status OUT NOCOPY VARCHAR2,
439 x_msg_count OUT NOCOPY NUMBER,
440 x_msg_data OUT NOCOPY VARCHAR2,
441 p_cvm_rec IN cvm_rec_type,
442 x_cvm_rec OUT NOCOPY cvm_rec_type) IS
443
444 l_api_version CONSTANT NUMBER := 1;
445 l_api_name CONSTANT VARCHAR2(30) := 'NUMBERS_insert_row';
446 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
447 l_cvm_rec cvm_rec_type := p_cvm_rec;
448 l_def_cvm_rec cvm_rec_type;
449 -------------------------------------------
450 -- Set_Attributes for:OKC_K_VERS_NUMBERS --
451 -------------------------------------------
452 FUNCTION Set_Attributes (
453 p_cvm_rec IN cvm_rec_type,
454 x_cvm_rec OUT NOCOPY cvm_rec_type
455 ) RETURN VARCHAR2 IS
456 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
457 BEGIN
458 x_cvm_rec := p_cvm_rec;
459 x_cvm_rec.MAJOR_VERSION := 0;
460 x_cvm_rec.MINOR_VERSION := 0;
461 RETURN(l_return_status);
462 END Set_Attributes;
463 BEGIN
464 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
465 p_init_msg_list,
466 '_PVT',
467 x_return_status);
468 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
469 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
470 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
471 RAISE OKC_API.G_EXCEPTION_ERROR;
472 END IF;
473 --- Setting item attributes
474 l_return_status := Set_Attributes(
475 p_cvm_rec, -- IN
476 l_cvm_rec); -- OUT
477 --- If any errors happen abort API
478 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
479 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
480 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
481 RAISE OKC_API.G_EXCEPTION_ERROR;
482 END IF;
483 INSERT INTO OKC_K_VERS_NUMBERS(
484 chr_id,
485 major_version,
486 minor_version,
487 object_version_number,
488 created_by,
489 creation_date,
490 last_updated_by,
491 last_update_date,
492 last_update_login)
493 VALUES (
494 l_cvm_rec.chr_id,
495 l_cvm_rec.major_version,
496 l_cvm_rec.minor_version,
497 l_cvm_rec.object_version_number,
498 l_cvm_rec.created_by,
499 l_cvm_rec.creation_date,
500 l_cvm_rec.last_updated_by,
501 l_cvm_rec.last_update_date,
502 l_cvm_rec.last_update_login);
503 -- Set OUT values
504 x_cvm_rec := l_cvm_rec;
505 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
506 EXCEPTION
507 WHEN OKC_API.G_EXCEPTION_ERROR THEN
508 x_return_status := OKC_API.HANDLE_EXCEPTIONS
509 (
510 l_api_name,
511 G_PKG_NAME,
512 'OKC_API.G_RET_STS_ERROR',
513 x_msg_count,
514 x_msg_data,
515 '_PVT'
516 );
517 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
518 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
519 (
520 l_api_name,
521 G_PKG_NAME,
522 'OKC_API.G_RET_STS_UNEXP_ERROR',
523 x_msg_count,
524 x_msg_data,
525 '_PVT'
526 );
527 WHEN OTHERS THEN
528 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
529 (
530 l_api_name,
531 G_PKG_NAME,
532 'OTHERS',
533 x_msg_count,
534 x_msg_data,
535 '_PVT'
536 );
537 END insert_row;
538 -----------------------------------------
539 -- insert_row for:OKC_K_VERS_NUMBERS_V --
540 -----------------------------------------
541 PROCEDURE insert_row(
542 p_api_version IN NUMBER,
543 p_init_msg_list IN VARCHAR2 ,
544 x_return_status OUT NOCOPY VARCHAR2,
545 x_msg_count OUT NOCOPY NUMBER,
546 x_msg_data OUT NOCOPY VARCHAR2,
547 p_cvmv_rec IN cvmv_rec_type,
548 x_cvmv_rec OUT NOCOPY cvmv_rec_type) IS
549
550 l_api_version CONSTANT NUMBER := 1;
551 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
552 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
553 l_cvmv_rec cvmv_rec_type;
554 l_def_cvmv_rec cvmv_rec_type;
555 l_cvm_rec cvm_rec_type;
556 lx_cvm_rec cvm_rec_type;
557 -------------------------------
558 -- FUNCTION fill_who_columns --
559 -------------------------------
560 FUNCTION fill_who_columns (
561 p_cvmv_rec IN cvmv_rec_type
562 ) RETURN cvmv_rec_type IS
563 l_cvmv_rec cvmv_rec_type := p_cvmv_rec;
564 BEGIN
565 l_cvmv_rec.CREATION_DATE := SYSDATE;
566 l_cvmv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
567 l_cvmv_rec.LAST_UPDATE_DATE := l_cvmv_rec.CREATION_DATE;
568 l_cvmv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
569 l_cvmv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
570 RETURN(l_cvmv_rec);
571 END fill_who_columns;
572 ---------------------------------------------
573 -- Set_Attributes for:OKC_K_VERS_NUMBERS_V --
574 ---------------------------------------------
575 FUNCTION Set_Attributes (
576 p_cvmv_rec IN cvmv_rec_type,
577 x_cvmv_rec OUT NOCOPY cvmv_rec_type
578 ) RETURN VARCHAR2 IS
579 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
580 BEGIN
581 x_cvmv_rec := p_cvmv_rec;
582 x_cvmv_rec.OBJECT_VERSION_NUMBER := 1;
583 RETURN(l_return_status);
584 END Set_Attributes;
585 BEGIN
586 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
587 G_PKG_NAME,
588 p_init_msg_list,
589 l_api_version,
590 p_api_version,
591 '_PVT',
592 x_return_status);
593 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
594 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
595 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
596 RAISE OKC_API.G_EXCEPTION_ERROR;
597 END IF;
598 l_cvmv_rec := null_out_defaults(p_cvmv_rec);
599 --- Setting item attributes
600 l_return_status := Set_Attributes(
601 l_cvmv_rec, -- IN
602 l_def_cvmv_rec); -- OUT
603 --- If any errors happen abort API
604 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
605 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
606 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
607 RAISE OKC_API.G_EXCEPTION_ERROR;
608 END IF;
609 l_def_cvmv_rec := fill_who_columns(l_def_cvmv_rec);
610 --- Validate all non-missing attributes (Item Level Validation)
611 ---l_return_status := Validate_Attributes(l_def_cvmv_rec);
612 --- If any errors happen abort API
613 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
614 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
615 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
616 RAISE OKC_API.G_EXCEPTION_ERROR;
617 END IF;
618 l_return_status := Validate_Record(l_def_cvmv_rec);
619 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
620 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
621 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
622 RAISE OKC_API.G_EXCEPTION_ERROR;
623 END IF;
624 --------------------------------------
625 -- Move VIEW record to "Child" records
626 --------------------------------------
627 migrate(l_def_cvmv_rec, l_cvm_rec);
628 --------------------------------------------
629 -- Call the INSERT_ROW for each child record
630 --------------------------------------------
631 insert_row(
632 p_init_msg_list,
633 x_return_status,
634 x_msg_count,
635 x_msg_data,
636 l_cvm_rec,
637 lx_cvm_rec
638 );
639 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
640 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
641 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
642 RAISE OKC_API.G_EXCEPTION_ERROR;
643 END IF;
644 migrate(lx_cvm_rec, l_def_cvmv_rec);
645 -- Set OUT values
646 x_cvmv_rec := l_def_cvmv_rec;
647 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
648 EXCEPTION
649 WHEN OKC_API.G_EXCEPTION_ERROR THEN
650 x_return_status := OKC_API.HANDLE_EXCEPTIONS
651 (
652 l_api_name,
653 G_PKG_NAME,
654 'OKC_API.G_RET_STS_ERROR',
655 x_msg_count,
656 x_msg_data,
657 '_PVT'
658 );
659 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
660 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
661 (
662 l_api_name,
663 G_PKG_NAME,
664 'OKC_API.G_RET_STS_UNEXP_ERROR',
665 x_msg_count,
666 x_msg_data,
667 '_PVT'
668 );
669 WHEN OTHERS THEN
670 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
671 (
672 l_api_name,
673 G_PKG_NAME,
674 'OTHERS',
675 x_msg_count,
676 x_msg_data,
677 '_PVT'
678 );
679 END insert_row;
680
681 ---------------------------------------------------------------------------
682 -- PROCEDURE lock_row
683 ---------------------------------------------------------------------------
684 -------------------------------------
685 -- lock_row for:OKC_K_VERS_NUMBERS --
686 -------------------------------------
687 PROCEDURE lock_row(
688 p_init_msg_list IN VARCHAR2 ,
689 x_return_status OUT NOCOPY VARCHAR2,
690 x_msg_count OUT NOCOPY NUMBER,
691 x_msg_data OUT NOCOPY VARCHAR2,
692 p_cvm_rec IN cvm_rec_type) IS
693
694 E_Resource_Busy EXCEPTION;
695 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
696 CURSOR lock_csr (p_cvm_rec IN cvm_rec_type) IS
697 SELECT OBJECT_VERSION_NUMBER
698 FROM OKC_K_VERS_NUMBERS
699 WHERE CHR_ID = p_cvm_rec.chr_id
700 AND OBJECT_VERSION_NUMBER IN (p_cvm_rec.object_version_number,
701 OKC_API.G_MISS_NUM)
702 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
703
704 CURSOR lchk_csr (p_cvm_rec IN cvm_rec_type) IS
705 SELECT OBJECT_VERSION_NUMBER
706 FROM OKC_K_VERS_NUMBERS
707 WHERE CHR_ID = p_cvm_rec.chr_id;
708 l_api_version CONSTANT NUMBER := 1;
709 l_api_name CONSTANT VARCHAR2(30) := 'NUMBERS_lock_row';
710 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
711 l_object_version_number OKC_K_VERS_NUMBERS.OBJECT_VERSION_NUMBER%TYPE;
712 lc_object_version_number OKC_K_VERS_NUMBERS.OBJECT_VERSION_NUMBER%TYPE;
713 l_row_notfound BOOLEAN := FALSE;
714 lc_row_notfound BOOLEAN := FALSE;
715 BEGIN
716 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
717 p_init_msg_list,
718 '_PVT',
719 x_return_status);
720 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
721 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
722 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
723 RAISE OKC_API.G_EXCEPTION_ERROR;
724 END IF;
725 BEGIN
726 OPEN lock_csr(p_cvm_rec);
727 FETCH lock_csr INTO l_object_version_number;
728 l_row_notfound := lock_csr%NOTFOUND;
729 CLOSE lock_csr;
730 EXCEPTION
731 WHEN E_Resource_Busy THEN
732 IF (lock_csr%ISOPEN) THEN
733 CLOSE lock_csr;
734 END IF;
735 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
736 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
737 END;
738
739 IF ( l_row_notfound ) THEN
740 OPEN lchk_csr(p_cvm_rec);
741 FETCH lchk_csr INTO lc_object_version_number;
742 lc_row_notfound := lchk_csr%NOTFOUND;
743 CLOSE lchk_csr;
744 END IF;
745 IF (lc_row_notfound) THEN
746 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
747 RAISE OKC_API.G_EXCEPTION_ERROR;
748 ELSIF lc_object_version_number > p_cvm_rec.object_version_number THEN
749 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
750 RAISE OKC_API.G_EXCEPTION_ERROR;
751 ELSIF lc_object_version_number <> p_cvm_rec.object_version_number THEN
752 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
753 RAISE OKC_API.G_EXCEPTION_ERROR;
754 ELSIF lc_object_version_number = -1 THEN
755 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
756 RAISE OKC_API.G_EXCEPTION_ERROR;
757 END IF;
758 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
759 EXCEPTION
760 WHEN OKC_API.G_EXCEPTION_ERROR THEN
761 x_return_status := OKC_API.HANDLE_EXCEPTIONS
762 (
763 l_api_name,
764 G_PKG_NAME,
765 'OKC_API.G_RET_STS_ERROR',
766 x_msg_count,
767 x_msg_data,
768 '_PVT'
769 );
770 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
771 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
772 (
773 l_api_name,
774 G_PKG_NAME,
775 'OKC_API.G_RET_STS_UNEXP_ERROR',
776 x_msg_count,
777 x_msg_data,
778 '_PVT'
779 );
780 WHEN OTHERS THEN
781 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
782 (
783 l_api_name,
784 G_PKG_NAME,
785 'OTHERS',
786 x_msg_count,
787 x_msg_data,
788 '_PVT'
789 );
790 END lock_row;
791 ---------------------------------------
792 -- lock_row for:OKC_K_VERS_NUMBERS_V --
793 ---------------------------------------
794 PROCEDURE lock_row(
795 p_api_version IN NUMBER,
796 p_init_msg_list IN VARCHAR2 ,
797 x_return_status OUT NOCOPY VARCHAR2,
798 x_msg_count OUT NOCOPY NUMBER,
799 x_msg_data OUT NOCOPY VARCHAR2,
800 p_cvmv_rec IN cvmv_rec_type) IS
801
802 l_api_version CONSTANT NUMBER := 1;
803 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
804 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
805 l_cvm_rec cvm_rec_type;
806 BEGIN
807 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
808 G_PKG_NAME,
809 p_init_msg_list,
810 l_api_version,
811 p_api_version,
812 '_PVT',
813 x_return_status);
814 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
815 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
816 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
817 RAISE OKC_API.G_EXCEPTION_ERROR;
818 END IF;
819 --------------------------------------
820 -- Move VIEW record to "Child" records
821 --------------------------------------
822 migrate(p_cvmv_rec, l_cvm_rec);
823 --------------------------------------------
824 -- Call the LOCK_ROW for each child record
825 --------------------------------------------
826 lock_row(
827 p_init_msg_list,
828 x_return_status,
829 x_msg_count,
830 x_msg_data,
831 l_cvm_rec
832 );
833 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
834 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
835 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
836 RAISE OKC_API.G_EXCEPTION_ERROR;
837 END IF;
838 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
839 EXCEPTION
840 WHEN OKC_API.G_EXCEPTION_ERROR THEN
841 x_return_status := OKC_API.HANDLE_EXCEPTIONS
842 (
843 l_api_name,
844 G_PKG_NAME,
845 'OKC_API.G_RET_STS_ERROR',
846 x_msg_count,
847 x_msg_data,
848 '_PVT'
849 );
850 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
851 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
852 (
853 l_api_name,
854 G_PKG_NAME,
855 'OKC_API.G_RET_STS_UNEXP_ERROR',
856 x_msg_count,
857 x_msg_data,
858 '_PVT'
859 );
860 WHEN OTHERS THEN
861 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
862 (
863 l_api_name,
864 G_PKG_NAME,
865 'OTHERS',
866 x_msg_count,
867 x_msg_data,
868 '_PVT'
869 );
870 END lock_row;
871
872 --------------------------------------
873 -- PL/SQL TBL lock_row for:CVMV_TBL --
874 --------------------------------------
875 PROCEDURE lock_row(
876 p_api_version IN NUMBER,
877 p_init_msg_list IN VARCHAR2 ,
878 x_return_status OUT NOCOPY VARCHAR2,
879 x_msg_count OUT NOCOPY NUMBER,
880 x_msg_data OUT NOCOPY VARCHAR2,
881 p_cvmv_tbl IN cvmv_tbl_type) IS
882
883 l_api_version CONSTANT NUMBER := 1;
884 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
885 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
886 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
887 i NUMBER := 0;
888 BEGIN
889 OKC_API.init_msg_list(p_init_msg_list);
890 -- Make sure PL/SQL table has records in it before passing
891 IF (p_cvmv_tbl.COUNT > 0) THEN
892 i := p_cvmv_tbl.FIRST;
893 LOOP
894 lock_row (
895 p_api_version => p_api_version,
896 p_init_msg_list => OKC_API.G_FALSE,
897 x_return_status => x_return_status,
898 x_msg_count => x_msg_count,
899 x_msg_data => x_msg_data,
900 p_cvmv_rec => p_cvmv_tbl(i));
901
902 -- store the highest degree of error
903 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
904 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
905 l_overall_status := x_return_status;
906 End If;
907 End If;
908
909 EXIT WHEN (i = p_cvmv_tbl.LAST);
910 i := p_cvmv_tbl.NEXT(i);
911 END LOOP;
912 -- return overall status
913 x_return_status := l_overall_status;
914 END IF;
915 EXCEPTION
916 WHEN OKC_API.G_EXCEPTION_ERROR THEN
917 x_return_status := OKC_API.HANDLE_EXCEPTIONS
918 (
919 l_api_name,
920 G_PKG_NAME,
921 'OKC_API.G_RET_STS_ERROR',
922 x_msg_count,
923 x_msg_data,
924 '_PVT'
925 );
926 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
927 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
928 (
929 l_api_name,
930 G_PKG_NAME,
931 'OKC_API.G_RET_STS_UNEXP_ERROR',
932 x_msg_count,
933 x_msg_data,
934 '_PVT'
935 );
936 WHEN OTHERS THEN
937 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
938 (
939 l_api_name,
940 G_PKG_NAME,
941 'OTHERS',
942 x_msg_count,
943 x_msg_data,
944 '_PVT'
945 );
946 END lock_row;
947
948 ---------------------------------------------------------------------------
949 -- PROCEDURE update_row
950 ---------------------------------------------------------------------------
951 ---------------------------------------
952 -- update_row for:OKC_K_VERS_NUMBERS --
953 ---------------------------------------
954 PROCEDURE update_row(
955 p_init_msg_list IN VARCHAR2 ,
956 x_return_status OUT NOCOPY VARCHAR2,
957 x_msg_count OUT NOCOPY NUMBER,
958 x_msg_data OUT NOCOPY VARCHAR2,
959 p_cvm_rec IN cvm_rec_type,
960 x_cvm_rec OUT NOCOPY cvm_rec_type) IS
961
962 l_api_version CONSTANT NUMBER := 1;
963 l_api_name CONSTANT VARCHAR2(30) := 'NUMBERS_update_row';
964 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
965 l_cvm_rec cvm_rec_type := p_cvm_rec;
966 l_def_cvm_rec cvm_rec_type;
967 l_row_notfound BOOLEAN := TRUE;
968 l_okc_k_vers_numbers_h_rec okc_k_vers_numbers_h_rec_type;
969 lx_okc_k_vers_numbers_h_rec okc_k_vers_numbers_h_rec_type;
970 ----------------------------------
971 -- FUNCTION populate_new_record --
972 ----------------------------------
973 FUNCTION populate_new_record (
974 p_cvm_rec IN cvm_rec_type,
975 x_cvm_rec OUT NOCOPY cvm_rec_type
976 ) RETURN VARCHAR2 IS
977 l_cvm_rec cvm_rec_type;
978 l_row_notfound BOOLEAN := TRUE;
979 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
980 BEGIN
981 x_cvm_rec := p_cvm_rec;
982 -- Get current database values
983 l_cvm_rec := get_rec(p_cvm_rec, l_row_notfound);
984 IF (l_row_notfound) THEN
985 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
986 END IF;
987 -- Move the "old" record to the history record:
988 -- (1) to get the "old" version
989 -- (2) to avoid 2 hits to the database
990 migrate(l_cvm_rec, l_okc_k_vers_numbers_h_rec);
991 IF (x_cvm_rec.chr_id = OKC_API.G_MISS_NUM)
992 THEN
993 x_cvm_rec.chr_id := l_cvm_rec.chr_id;
994 END IF;
995 IF (x_cvm_rec.major_version = OKC_API.G_MISS_NUM)
996 THEN
997 x_cvm_rec.major_version := l_cvm_rec.major_version;
998 END IF;
999 IF (x_cvm_rec.minor_version = OKC_API.G_MISS_NUM)
1000 THEN
1001 x_cvm_rec.minor_version := l_cvm_rec.minor_version;
1002 END IF;
1003 IF (x_cvm_rec.object_version_number = OKC_API.G_MISS_NUM)
1004 THEN
1005 x_cvm_rec.object_version_number := l_cvm_rec.object_version_number;
1006 END IF;
1007 IF (x_cvm_rec.created_by = OKC_API.G_MISS_NUM)
1008 THEN
1009 x_cvm_rec.created_by := l_cvm_rec.created_by;
1010 END IF;
1011 IF (x_cvm_rec.creation_date = OKC_API.G_MISS_DATE)
1012 THEN
1013 x_cvm_rec.creation_date := l_cvm_rec.creation_date;
1014 END IF;
1015 IF (x_cvm_rec.last_updated_by = OKC_API.G_MISS_NUM)
1016 THEN
1017 x_cvm_rec.last_updated_by := l_cvm_rec.last_updated_by;
1018 END IF;
1019 IF (x_cvm_rec.last_update_date = OKC_API.G_MISS_DATE)
1020 THEN
1021 x_cvm_rec.last_update_date := l_cvm_rec.last_update_date;
1022 END IF;
1023 IF (x_cvm_rec.last_update_login = OKC_API.G_MISS_NUM)
1024 THEN
1025 x_cvm_rec.last_update_login := l_cvm_rec.last_update_login;
1026 END IF;
1027 RETURN(l_return_status);
1028 END populate_new_record;
1029 -------------------------------------------
1030 -- Set_Attributes for:OKC_K_VERS_NUMBERS --
1031 -------------------------------------------
1032 FUNCTION Set_Attributes (
1033 p_cvm_rec IN cvm_rec_type,
1034 x_cvm_rec OUT NOCOPY cvm_rec_type
1035 ) RETURN VARCHAR2 IS
1036 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1037 BEGIN
1038 x_cvm_rec := p_cvm_rec;
1039 x_cvm_rec.MINOR_VERSION := NVL(x_cvm_rec.MINOR_VERSION, -1) + 1;
1040 RETURN(l_return_status);
1041 END Set_Attributes;
1042 BEGIN
1043 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1044 p_init_msg_list,
1045 '_PVT',
1046 x_return_status);
1047 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1048 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1049 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1050 RAISE OKC_API.G_EXCEPTION_ERROR;
1051 END IF;
1052 /*
1053 --- Setting item attributes
1054 l_return_status := Set_Attributes(
1055 p_cvm_rec, -- IN
1056 l_cvm_rec); -- OUT
1057 --- If any errors happen abort API
1058 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1059 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1060 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1061 RAISE OKC_API.G_EXCEPTION_ERROR;
1062 END IF;
1063 */
1064 l_return_status := populate_new_record(p_cvm_rec, l_cvm_rec);
1065 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1066 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1067 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1068 RAISE OKC_API.G_EXCEPTION_ERROR;
1069 END IF;
1070
1071 --- Setting item attributes
1072 l_return_status := Set_Attributes(
1073 l_cvm_rec, -- IN
1074 l_def_cvm_rec); -- OUT
1075 --- If any errors happen abort API
1076 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1077 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1078 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1079 RAISE OKC_API.G_EXCEPTION_ERROR;
1080 END IF;
1081
1082 UPDATE OKC_K_VERS_NUMBERS
1083 SET MAJOR_VERSION = l_def_cvm_rec.major_version,
1084 MINOR_VERSION = l_def_cvm_rec.minor_version,
1085 OBJECT_VERSION_NUMBER = l_def_cvm_rec.object_version_number,
1086 CREATED_BY = l_def_cvm_rec.created_by,
1087 CREATION_DATE = l_def_cvm_rec.creation_date,
1088 LAST_UPDATED_BY = l_def_cvm_rec.last_updated_by,
1089 LAST_UPDATE_DATE = l_def_cvm_rec.last_update_date,
1090 LAST_UPDATE_LOGIN = l_def_cvm_rec.last_update_login
1091 WHERE CHR_ID = l_def_cvm_rec.chr_id;
1092
1093 -- Insert into History table
1094 insert_row(
1095 p_init_msg_list,
1096 x_return_status,
1097 x_msg_count,
1098 x_msg_data,
1099 l_okc_k_vers_numbers_h_rec,
1100 lx_okc_k_vers_numbers_h_rec
1101 );
1102 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1103 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1104 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1105 RAISE OKC_API.G_EXCEPTION_ERROR;
1106 END IF;
1107 x_cvm_rec := l_def_cvm_rec;
1108 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1109 EXCEPTION
1110 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1111 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1112 (
1113 l_api_name,
1114 G_PKG_NAME,
1115 'OKC_API.G_RET_STS_ERROR',
1116 x_msg_count,
1117 x_msg_data,
1118 '_PVT'
1119 );
1120 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1121 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1122 (
1123 l_api_name,
1124 G_PKG_NAME,
1125 'OKC_API.G_RET_STS_UNEXP_ERROR',
1126 x_msg_count,
1127 x_msg_data,
1128 '_PVT'
1129 );
1130 WHEN OTHERS THEN
1131 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1132 (
1133 l_api_name,
1134 G_PKG_NAME,
1135 'OTHERS',
1136 x_msg_count,
1137 x_msg_data,
1138 '_PVT'
1139 );
1140 END update_row;
1141 -----------------------------------------
1142 -- update_row for:OKC_K_VERS_NUMBERS_V --
1143 -----------------------------------------
1144 PROCEDURE update_row(
1145 p_api_version IN NUMBER,
1146 p_init_msg_list IN VARCHAR2 ,
1147 x_return_status OUT NOCOPY VARCHAR2,
1148 x_msg_count OUT NOCOPY NUMBER,
1149 x_msg_data OUT NOCOPY VARCHAR2,
1150 p_cvmv_rec IN cvmv_rec_type,
1151 x_cvmv_rec OUT NOCOPY cvmv_rec_type) IS
1152
1153 l_api_version CONSTANT NUMBER := 1;
1154 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1155 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1156 l_cvmv_rec cvmv_rec_type := p_cvmv_rec;
1157 l_def_cvmv_rec cvmv_rec_type;
1158 l_cvm_rec cvm_rec_type;
1159 lx_cvm_rec cvm_rec_type;
1160 -------------------------------
1161 -- FUNCTION fill_who_columns --
1162 -------------------------------
1163 FUNCTION fill_who_columns (
1164 p_cvmv_rec IN cvmv_rec_type
1165 ) RETURN cvmv_rec_type IS
1166 l_cvmv_rec cvmv_rec_type := p_cvmv_rec;
1167 BEGIN
1168 l_cvmv_rec.LAST_UPDATE_DATE := SYSDATE;
1169 l_cvmv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1170 l_cvmv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1171 RETURN(l_cvmv_rec);
1172 END fill_who_columns;
1173 ----------------------------------
1174 -- FUNCTION populate_new_record --
1175 ----------------------------------
1176 FUNCTION populate_new_record (
1177 p_cvmv_rec IN cvmv_rec_type,
1178 x_cvmv_rec OUT NOCOPY cvmv_rec_type
1179 ) RETURN VARCHAR2 IS
1180 l_cvmv_rec cvmv_rec_type;
1181 l_row_notfound BOOLEAN := TRUE;
1182 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1183 BEGIN
1184 x_cvmv_rec := p_cvmv_rec;
1185 -- Get current database values
1186 l_cvmv_rec := get_rec(p_cvmv_rec, l_row_notfound);
1187 IF (l_row_notfound) THEN
1188 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1189 END IF;
1190 IF (x_cvmv_rec.chr_id = OKC_API.G_MISS_NUM)
1191 THEN
1192 x_cvmv_rec.chr_id := l_cvmv_rec.chr_id;
1193 END IF;
1194 IF (x_cvmv_rec.object_version_number = OKC_API.G_MISS_NUM)
1195 THEN
1196 x_cvmv_rec.object_version_number := l_cvmv_rec.object_version_number;
1197 END IF;
1198 IF (x_cvmv_rec.major_version = OKC_API.G_MISS_NUM)
1199 THEN
1200 x_cvmv_rec.major_version := l_cvmv_rec.major_version;
1201 END IF;
1202 IF (x_cvmv_rec.minor_version = OKC_API.G_MISS_NUM)
1203 THEN
1204 x_cvmv_rec.minor_version := l_cvmv_rec.minor_version;
1205 END IF;
1206 IF (x_cvmv_rec.created_by = OKC_API.G_MISS_NUM)
1207 THEN
1208 x_cvmv_rec.created_by := l_cvmv_rec.created_by;
1209 END IF;
1210 IF (x_cvmv_rec.creation_date = OKC_API.G_MISS_DATE)
1211 THEN
1212 x_cvmv_rec.creation_date := l_cvmv_rec.creation_date;
1213 END IF;
1214 IF (x_cvmv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1215 THEN
1216 x_cvmv_rec.last_updated_by := l_cvmv_rec.last_updated_by;
1217 END IF;
1218 IF (x_cvmv_rec.last_update_date = OKC_API.G_MISS_DATE)
1219 THEN
1220 x_cvmv_rec.last_update_date := l_cvmv_rec.last_update_date;
1221 END IF;
1222 IF (x_cvmv_rec.last_update_login = OKC_API.G_MISS_NUM)
1223 THEN
1224 x_cvmv_rec.last_update_login := l_cvmv_rec.last_update_login;
1225 END IF;
1226 RETURN(l_return_status);
1227 END populate_new_record;
1228 ---------------------------------------------
1229 -- Set_Attributes for:OKC_K_VERS_NUMBERS_V --
1230 ---------------------------------------------
1231 FUNCTION Set_Attributes (
1232 p_cvmv_rec IN cvmv_rec_type,
1233 x_cvmv_rec OUT NOCOPY cvmv_rec_type
1234 ) RETURN VARCHAR2 IS
1235 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1236 BEGIN
1237 x_cvmv_rec := p_cvmv_rec;
1238 x_cvmv_rec.OBJECT_VERSION_NUMBER := NVL(x_cvmv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1239 RETURN(l_return_status);
1240 END Set_Attributes;
1241 BEGIN
1242 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1243 G_PKG_NAME,
1244 p_init_msg_list,
1245 l_api_version,
1246 p_api_version,
1247 '_PVT',
1248 x_return_status);
1249 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1250 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1251 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1252 RAISE OKC_API.G_EXCEPTION_ERROR;
1253 END IF;
1254 /*
1255 --- Setting item attributes
1256 l_return_status := Set_Attributes(
1257 p_cvmv_rec, -- IN
1258 l_cvmv_rec); -- OUT
1259 --- If any errors happen abort API
1260 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1261 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1262 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1263 RAISE OKC_API.G_EXCEPTION_ERROR;
1264 END IF;
1265 */
1266 l_return_status := populate_new_record(p_cvmv_rec, l_cvmv_rec);
1267 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1268 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1269 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1270 RAISE OKC_API.G_EXCEPTION_ERROR;
1271 END IF;
1272
1273 --- Setting item attributes
1274 l_return_status := Set_Attributes(
1275 l_cvmv_rec, -- IN
1276 l_def_cvmv_rec); -- OUT
1277 --- If any errors happen abort API
1278 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1279 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1280 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1281 RAISE OKC_API.G_EXCEPTION_ERROR;
1282 END IF;
1283
1284 l_def_cvmv_rec := fill_who_columns(l_def_cvmv_rec);
1285 --- Validate all non-missing attributes (Item Level Validation)
1286 l_return_status := Validate_Attributes(l_def_cvmv_rec);
1287 --- If any errors happen abort API
1288 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1289 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1290 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1291 RAISE OKC_API.G_EXCEPTION_ERROR;
1292 END IF;
1293 l_return_status := Validate_Record(l_def_cvmv_rec);
1294 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1295 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1296 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1297 RAISE OKC_API.G_EXCEPTION_ERROR;
1298 END IF;
1299
1300 --------------------------------------
1301 -- Move VIEW record to "Child" records
1302 --------------------------------------
1303 migrate(l_def_cvmv_rec, l_cvm_rec);
1304 --------------------------------------------
1305 -- Call the UPDATE_ROW for each child record
1306 --------------------------------------------
1307 update_row(
1308 p_init_msg_list,
1309 x_return_status,
1310 x_msg_count,
1311 x_msg_data,
1312 l_cvm_rec,
1313 lx_cvm_rec
1314 );
1315 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1316 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1317 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1318 RAISE OKC_API.G_EXCEPTION_ERROR;
1319 END IF;
1320 migrate(lx_cvm_rec, l_def_cvmv_rec);
1321 x_cvmv_rec := l_def_cvmv_rec;
1322 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1323 EXCEPTION
1324 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1325 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1326 (
1327 l_api_name,
1328 G_PKG_NAME,
1329 'OKC_API.G_RET_STS_ERROR',
1330 x_msg_count,
1331 x_msg_data,
1332 '_PVT'
1333 );
1334 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1335 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1336 (
1337 l_api_name,
1338 G_PKG_NAME,
1339 'OKC_API.G_RET_STS_UNEXP_ERROR',
1340 x_msg_count,
1341 x_msg_data,
1342 '_PVT'
1343 );
1344 WHEN OTHERS THEN
1345 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1346 (
1347 l_api_name,
1348 G_PKG_NAME,
1349 'OTHERS',
1350 x_msg_count,
1351 x_msg_data,
1352 '_PVT'
1353 );
1354 END update_row;
1355
1356 ---------------------------------------------------------------------------
1357 -- PROCEDURE delete_row
1358 ---------------------------------------------------------------------------
1359 ---------------------------------------
1360 -- delete_row for:OKC_K_VERS_NUMBERS --
1361 ---------------------------------------
1362 PROCEDURE delete_row(
1363 p_init_msg_list IN VARCHAR2 ,
1364 x_return_status OUT NOCOPY VARCHAR2,
1365 x_msg_count OUT NOCOPY NUMBER,
1366 x_msg_data OUT NOCOPY VARCHAR2,
1367 p_cvm_rec IN cvm_rec_type) IS
1368
1369 l_api_version CONSTANT NUMBER := 1;
1370 l_api_name CONSTANT VARCHAR2(30) := 'NUMBERS_delete_row';
1371 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1372 l_cvm_rec cvm_rec_type:= p_cvm_rec;
1373 l_row_notfound BOOLEAN := TRUE;
1374 l_okc_k_vers_numbers_h_rec okc_k_vers_numbers_h_rec_type;
1375 lx_okc_k_vers_numbers_h_rec okc_k_vers_numbers_h_rec_type;
1376 BEGIN
1377 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1378 p_init_msg_list,
1379 '_PVT',
1380 x_return_status);
1381 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1382 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1383 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1384 RAISE OKC_API.G_EXCEPTION_ERROR;
1385 END IF;
1386 -- Insert into History table
1387 l_cvm_rec := get_rec(l_cvm_rec, l_row_notfound);
1388 IF (l_row_notfound) THEN
1389 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1390 END IF;
1391 migrate(l_cvm_rec, l_okc_k_vers_numbers_h_rec);
1392 insert_row(
1393 p_init_msg_list,
1394 x_return_status,
1395 x_msg_count,
1396 x_msg_data,
1397 l_okc_k_vers_numbers_h_rec,
1398 lx_okc_k_vers_numbers_h_rec
1399 );
1400 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1401 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1402 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1403 RAISE OKC_API.G_EXCEPTION_ERROR;
1404 END IF;
1405 DELETE FROM OKC_K_VERS_NUMBERS
1406 WHERE CHR_ID = l_cvm_rec.chr_id;
1407
1408 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1409 EXCEPTION
1410 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1411 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1412 (
1413 l_api_name,
1414 G_PKG_NAME,
1415 'OKC_API.G_RET_STS_ERROR',
1416 x_msg_count,
1417 x_msg_data,
1418 '_PVT'
1419 );
1420 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1421 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1422 (
1423 l_api_name,
1424 G_PKG_NAME,
1425 'OKC_API.G_RET_STS_UNEXP_ERROR',
1426 x_msg_count,
1427 x_msg_data,
1428 '_PVT'
1429 );
1430 WHEN OTHERS THEN
1431 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1432 (
1433 l_api_name,
1434 G_PKG_NAME,
1435 'OTHERS',
1436 x_msg_count,
1437 x_msg_data,
1438 '_PVT'
1439 );
1440 END delete_row;
1441 -----------------------------------------
1442 -- delete_row for:OKC_K_VERS_NUMBERS_V --
1443 -----------------------------------------
1444 PROCEDURE delete_row(
1445 p_api_version IN NUMBER,
1446 p_init_msg_list IN VARCHAR2 ,
1447 x_return_status OUT NOCOPY VARCHAR2,
1448 x_msg_count OUT NOCOPY NUMBER,
1449 x_msg_data OUT NOCOPY VARCHAR2,
1450 p_cvmv_rec IN cvmv_rec_type) IS
1451
1452 l_api_version CONSTANT NUMBER := 1;
1453 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1454 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1455 l_cvmv_rec cvmv_rec_type := p_cvmv_rec;
1456 l_cvm_rec cvm_rec_type;
1457 BEGIN
1458 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1459 G_PKG_NAME,
1460 p_init_msg_list,
1461 l_api_version,
1462 p_api_version,
1463 '_PVT',
1464 x_return_status);
1465 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1466 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1467 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1468 RAISE OKC_API.G_EXCEPTION_ERROR;
1469 END IF;
1470 --------------------------------------
1471 -- Move VIEW record to "Child" records
1472 --------------------------------------
1473 migrate(l_cvmv_rec, l_cvm_rec);
1474 --------------------------------------------
1475 -- Call the DELETE_ROW for each child record
1476 --------------------------------------------
1477 delete_row(
1478 p_init_msg_list,
1479 x_return_status,
1480 x_msg_count,
1481 x_msg_data,
1482 l_cvm_rec
1483 );
1484 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1485 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1486 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1487 RAISE OKC_API.G_EXCEPTION_ERROR;
1488 END IF;
1489 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1490 EXCEPTION
1491 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1492 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1493 (
1494 l_api_name,
1495 G_PKG_NAME,
1496 'OKC_API.G_RET_STS_ERROR',
1497 x_msg_count,
1498 x_msg_data,
1499 '_PVT'
1500 );
1501 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1502 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1503 (
1504 l_api_name,
1505 G_PKG_NAME,
1506 'OKC_API.G_RET_STS_UNEXP_ERROR',
1507 x_msg_count,
1508 x_msg_data,
1509 '_PVT'
1510 );
1511 WHEN OTHERS THEN
1512 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1513 (
1514 l_api_name,
1515 G_PKG_NAME,
1516 'OTHERS',
1517 x_msg_count,
1518 x_msg_data,
1519 '_PVT'
1520 );
1521 END delete_row;
1522
1523 PROCEDURE create_contract_version(
1524 p_api_version IN NUMBER,
1525 p_init_msg_list IN VARCHAR2 ,
1526 x_return_status OUT NOCOPY VARCHAR2,
1527 x_msg_count OUT NOCOPY NUMBER,
1528 x_msg_data OUT NOCOPY VARCHAR2,
1529 p_cvmv_rec IN cvmv_rec_type,
1530 x_cvmv_rec OUT NOCOPY cvmv_rec_type) IS
1531 BEGIN
1532 OKC_CVM_PVT.insert_row(
1533 p_api_version,
1534 p_init_msg_list,
1535 x_return_status,
1536 x_msg_count,
1537 x_msg_data,
1538 p_cvmv_rec,
1539 x_cvmv_rec);
1540
1541 END create_contract_version;
1542
1543
1544 PROCEDURE update_contract_version(
1545 p_api_version IN NUMBER,
1546 p_init_msg_list IN VARCHAR2 ,
1547 x_return_status OUT NOCOPY VARCHAR2,
1548 x_msg_count OUT NOCOPY NUMBER,
1549 x_msg_data OUT NOCOPY VARCHAR2,
1550 p_cvmv_rec IN cvmv_rec_type,
1551 x_cvmv_rec OUT NOCOPY cvmv_rec_type) IS
1552
1553 v_trans_id VARCHAR2(100);
1554 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
1555 BEGIN
1556 IF (l_debug = 'Y') THEN
1557 okc_debug.Set_Indentation('OKC_CVM_PVT');
1558 okc_debug.log('1000: Entered update_contract_version, g_defer_min_vers_upd='||g_defer_min_vers_upd, 2);
1559 END IF;
1560
1561 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1562
1563 --bug 5218723, if minor version update is deferred, donot update
1564 IF (g_defer_min_vers_upd = FND_API.G_TRUE) THEN
1565 return;
1566 END IF;
1567
1568 -- get id of local transaction
1569 v_trans_id := dbms_transaction.local_transaction_id(TRUE);
1570
1571 -- v_trans_id will be null if no transaction. In that case, do nothing
1572 --
1573 -- If v_trans_id = g_trans_id, then this routine has been called before
1574 -- from this transaction. In that case, do nothing
1575 --
1576 -- All other cases, set g_trans_id to the current transaction id and
1577 -- update the contract version number
1578
1579 IF v_trans_id IS NOT NULL AND v_trans_id <> g_trans_id THEN
1580 -- save current transaction id
1581 g_trans_id := v_trans_id;
1582 l_cvmv_rec := p_cvmv_rec;
1583 l_cvmv_rec.major_version := OKC_API.G_MISS_NUM;
1584 -- Major version update is not allowed
1585
1586 -- update contract version number
1587 OKC_CVM_PVT.update_row(
1588 p_api_version,
1589 p_init_msg_list,
1590 x_return_status,
1591 x_msg_count,
1592 x_msg_data,
1593 l_cvmv_rec,
1594 x_cvmv_rec);
1595 END IF;
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1599 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1600 p_msg_name => g_unexpected_error,
1601 p_token1 => g_sqlcode_token,
1602 p_token1_value => sqlcode,
1603 p_token2 => g_sqlerrm_token,
1604 p_token2_value => sqlerrm);
1605 END update_contract_version;
1606
1607 PROCEDURE version_contract_version(
1608 p_api_version IN NUMBER,
1609 p_init_msg_list IN VARCHAR2 ,
1610 x_return_status OUT NOCOPY VARCHAR2,
1611 x_msg_count OUT NOCOPY NUMBER,
1612 x_msg_data OUT NOCOPY VARCHAR2,
1613 p_cvmv_rec IN cvmv_rec_type,
1614 x_cvmv_rec OUT NOCOPY cvmv_rec_type) IS
1615
1616 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
1617 l_major_version NUMBER;
1618 l_not_found BOOLEAN := FALSE;
1619
1620 Cursor l_cvmv_csr(p_chr_id NUMBER) Is
1621 SELECT major_version
1622 FROM OKC_K_VERS_NUMBERS_V
1623 WHERE chr_id = p_chr_id;
1624 BEGIN
1625
1626 l_cvmv_rec := p_cvmv_rec;
1627 open l_cvmv_csr(l_cvmv_rec.chr_id);
1628 fetch l_cvmv_csr into l_major_version;
1629 l_not_found := l_cvmv_csr%NOTFOUND;
1630 close l_cvmv_csr;
1631 If (l_not_found) Then
1632 OKC_API.SET_MESSAGE(
1633 p_app_name => g_app_name,
1634 p_msg_name => 'G_NO_VERSOIN_RECORD');
1635 -- notify caller of an error
1636 x_return_status := OKC_API.G_RET_STS_ERROR;
1637 Else
1638 --
1639 -- Increment major_version and set minor_version to -1.
1640 -- -1 increments in simple API to zero
1641 --
1642 l_cvmv_rec.major_version := l_major_version + 1;
1643 l_cvmv_rec.minor_version := -1;
1644
1645 OKC_CVM_PVT.update_row(
1646 p_api_version,
1647 p_init_msg_list,
1648 x_return_status,
1649 x_msg_count,
1650 x_msg_data,
1651 l_cvmv_rec,
1652 x_cvmv_rec);
1653 End If;
1654
1655 EXCEPTION
1656 WHEN OTHERS THEN
1657 NULL;
1658
1659 END version_contract_version;
1660
1661 PROCEDURE delete_contract_version(
1662 p_api_version IN NUMBER,
1663 p_init_msg_list IN VARCHAR2 ,
1664 x_return_status OUT NOCOPY VARCHAR2,
1665 x_msg_count OUT NOCOPY NUMBER,
1666 x_msg_data OUT NOCOPY VARCHAR2,
1667 p_cvmv_rec IN cvmv_rec_type) IS
1668 BEGIN
1669
1670 OKC_CVM_PVT.delete_row(
1671 p_api_version,
1672 p_init_msg_list,
1673 x_return_status,
1674 x_msg_count,
1675 x_msg_data,
1676 p_cvmv_rec);
1677
1678 END delete_contract_version;
1679
1680 PROCEDURE clear_g_transaction_id IS --added for bug 3658108
1681 BEGIN
1682
1683 g_trans_id:='XXX';
1684
1685 END clear_g_transaction_id;
1686
1687
1688 PROCEDURE defer_minor_version_update
1689 (
1690 p_defer IN VARCHAR2 DEFAULT FND_API.G_FALSE
1691 )
1692 IS
1693 BEGIN
1694
1695 IF (l_debug = 'Y') THEN
1696 okc_debug.Set_Indentation('OKC_CVM_PVT');
1697 okc_debug.log('8000: Entered defer_minor_version_update, p_defer='||p_defer||' ,g_defer_min_vers_upd='||g_defer_min_vers_upd, 2);
1698 END IF;
1699
1700 IF (FND_API.G_TRUE = p_defer) THEN
1701 g_defer_min_vers_upd := FND_API.G_TRUE;
1702 ELSE
1703 g_defer_min_vers_upd := FND_API.G_FALSE;
1704 END IF;
1705
1706 IF (l_debug = 'Y') THEN
1707 okc_debug.log('8100: Leaving defer_minor_version_update, ,g_defer_min_vers_upd='||g_defer_min_vers_upd, 2);
1708 okc_debug.Reset_Indentation;
1709 END IF;
1710
1711 END defer_minor_version_update;
1712
1713 FUNCTION Update_Minor_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
1714 l_api_version NUMBER := 1;
1715 l_init_msg_list VARCHAR2(1) := 'F';
1716 x_return_status VARCHAR2(1);
1717 x_msg_count NUMBER;
1718 x_msg_data VARCHAR2(2000);
1719 x_out_rec OKC_CVM_PVT.cvmv_rec_type;
1720 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
1721 BEGIN
1722
1723 IF (l_debug = 'Y') THEN
1724 okc_debug.Set_Indentation('OKC_CVM_PVT');
1725 okc_debug.log('9000: Entered Update_Minor_Version(p_chr_id), p_chr_id='||p_chr_id, 2);
1726 END IF;
1727
1728 -- initialize return status
1729 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1730
1731 -- assign/populate contract header id
1732 l_cvmv_rec.chr_id := p_chr_id;
1733
1734 OKC_CVM_PVT.update_contract_version(
1735 p_api_version => l_api_version,
1736 p_init_msg_list => l_init_msg_list,
1737 x_return_status => x_return_status,
1738 x_msg_count => x_msg_count,
1739 x_msg_data => x_msg_data,
1740 p_cvmv_rec => l_cvmv_rec,
1741 x_cvmv_rec => x_out_rec);
1742
1743 IF (l_debug = 'Y') THEN
1744 okc_debug.log('9100: Leaving Update_Minor_Version(p_chr_id), x_return_status='||x_return_status, 2);
1745 okc_debug.Reset_Indentation;
1746 END IF;
1747
1748 return (x_return_status);
1749 EXCEPTION
1750 when OTHERS then
1751 -- notify caller of an error
1752 x_return_status := OKC_API.G_RET_STS_ERROR;
1753
1754 -- store SQL error message on message stack
1755 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1756 p_msg_name => g_unexpected_error,
1757 p_token1 => g_sqlcode_token,
1758 p_token1_value => sqlcode,
1759 p_token2 => g_sqlerrm_token,
1760 p_token2_value => sqlerrm);
1761
1762 IF (l_debug = 'Y') THEN
1763 okc_debug.log('9200: Leaving Update_Minor_Version(p_chr_id):other_error: x_return_status='||x_return_status, 2);
1764 okc_debug.Reset_Indentation;
1765 END IF;
1766
1767 return (x_return_status);
1768
1769 END Update_Minor_Version;
1770
1771
1772 END OKC_CVM_PVT;