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