[Home] [Help]
PACKAGE BODY: APPS.OKC_CSO_PVT
Source
1 PACKAGE BODY OKC_CSO_PVT AS
2 /* $Header: OKCSCSOB.pls 120.0 2005/05/26 09:31:28 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_CONTACT_SOURCES
46 ---------------------------------------------------------------------------
47 FUNCTION get_rec (
48 p_cso_rec IN cso_rec_type,
49 x_no_data_found OUT NOCOPY BOOLEAN
50 ) RETURN cso_rec_type IS
51 CURSOR cso_pk_csr (p_cro_code IN VARCHAR2,
52 p_rle_code IN VARCHAR2,
53 p_buy_or_sell IN VARCHAR2,
54 p_start_date IN DATE) IS
55 SELECT
56 CRO_CODE,
57 RLE_CODE,
58 BUY_OR_SELL,
59 START_DATE,
60 END_DATE,
61 JTOT_OBJECT_CODE,
62 OBJECT_VERSION_NUMBER,
63 CREATED_BY,
64 CREATION_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_DATE,
67 CONSTRAINED_YN,
68 LAST_UPDATE_LOGIN,
69 ACCESS_LEVEL
70 FROM Okc_Contact_Sources
71 WHERE okc_contact_sources.cro_code = p_cro_code
72 AND okc_contact_sources.rle_code = p_rle_code
73 AND okc_contact_sources.buy_or_sell = p_buy_or_sell
74 AND okc_contact_sources.start_date = p_start_date;
75 l_cso_pk cso_pk_csr%ROWTYPE;
76 l_cso_rec cso_rec_type;
77 BEGIN
78 x_no_data_found := TRUE;
79 -- Get current database values
80 OPEN cso_pk_csr (p_cso_rec.cro_code,
81 p_cso_rec.rle_code,
82 p_cso_rec.buy_or_sell,
83 p_cso_rec.start_date);
84 FETCH cso_pk_csr INTO
85 l_cso_rec.CRO_CODE,
86 l_cso_rec.RLE_CODE,
87 l_cso_rec.BUY_OR_SELL,
88 l_cso_rec.START_DATE,
89 l_cso_rec.END_DATE,
90 l_cso_rec.JTOT_OBJECT_CODE,
91 l_cso_rec.OBJECT_VERSION_NUMBER,
92 l_cso_rec.CREATED_BY,
93 l_cso_rec.CREATION_DATE,
94 l_cso_rec.LAST_UPDATED_BY,
95 l_cso_rec.LAST_UPDATE_DATE,
96 l_cso_rec.CONSTRAINED_YN,
97 l_cso_rec.LAST_UPDATE_LOGIN,
98 l_cso_rec.ACCESS_LEVEL;
99 x_no_data_found := cso_pk_csr%NOTFOUND;
100 CLOSE cso_pk_csr;
101 RETURN(l_cso_rec);
102 END get_rec;
103
104 FUNCTION get_rec (
105 p_cso_rec IN cso_rec_type
106 ) RETURN cso_rec_type IS
107 l_row_notfound BOOLEAN := TRUE;
108 BEGIN
109 RETURN(get_rec(p_cso_rec, l_row_notfound));
110 END get_rec;
111 ---------------------------------------------------------------------------
112 -- FUNCTION get_rec for: OKC_CONTACT_SOURCES_V
113 ---------------------------------------------------------------------------
114 FUNCTION get_rec (
115 p_csov_rec IN csov_rec_type,
116 x_no_data_found OUT NOCOPY BOOLEAN
117 ) RETURN csov_rec_type IS
118 CURSOR okc_csov_pk_csr (p_rle_code IN VARCHAR2,
119 p_cro_code IN VARCHAR2,
120 p_buy_or_sell IN VARCHAR2,
121 p_start_date IN DATE) IS
122 SELECT
123 RLE_CODE,
124 CRO_CODE,
125 BUY_OR_SELL,
126 START_DATE,
127 END_DATE,
128 JTOT_OBJECT_CODE,
129 OBJECT_VERSION_NUMBER,
130 CONSTRAINED_YN,
131 CREATED_BY,
132 CREATION_DATE,
133 LAST_UPDATED_BY,
134 LAST_UPDATE_DATE,
135 LAST_UPDATE_LOGIN,
136 ACCESS_LEVEL
137 FROM Okc_Contact_Sources
138 WHERE okc_contact_sources.rle_code = p_rle_code
139 AND okc_contact_sources.cro_code = p_cro_code
140 AND okc_contact_sources.buy_or_sell = p_buy_or_sell
141 AND okc_contact_sources.start_date = p_start_date;
142 l_okc_csov_pk okc_csov_pk_csr%ROWTYPE;
143 l_csov_rec csov_rec_type;
144 BEGIN
145 x_no_data_found := TRUE;
146 -- Get current database values
147 OPEN okc_csov_pk_csr (p_csov_rec.rle_code,
148 p_csov_rec.cro_code,
149 p_csov_rec.buy_or_sell,
150 p_csov_rec.start_date);
151 FETCH okc_csov_pk_csr INTO
152 l_csov_rec.RLE_CODE,
153 l_csov_rec.CRO_CODE,
154 l_csov_rec.BUY_OR_SELL,
155 l_csov_rec.START_DATE,
156 l_csov_rec.END_DATE,
157 l_csov_rec.JTOT_OBJECT_CODE,
158 l_csov_rec.OBJECT_VERSION_NUMBER,
159 l_csov_rec.CONSTRAINED_YN,
160 l_csov_rec.CREATED_BY,
161 l_csov_rec.CREATION_DATE,
162 l_csov_rec.LAST_UPDATED_BY,
163 l_csov_rec.LAST_UPDATE_DATE,
164 l_csov_rec.LAST_UPDATE_LOGIN,
165 l_csov_rec.ACCESS_LEVEL;
166 x_no_data_found := okc_csov_pk_csr%NOTFOUND;
167 CLOSE okc_csov_pk_csr;
168 RETURN(l_csov_rec);
169 END get_rec;
170
171 FUNCTION get_rec (
172 p_csov_rec IN csov_rec_type
173 ) RETURN csov_rec_type IS
174 l_row_notfound BOOLEAN := TRUE;
175 BEGIN
176 RETURN(get_rec(p_csov_rec, l_row_notfound));
177 END get_rec;
178
179 -----------------------------------------------------------
180 -- FUNCTION null_out_defaults for: OKC_CONTACT_SOURCES_V --
181 -----------------------------------------------------------
182 FUNCTION null_out_defaults (
183 p_csov_rec IN csov_rec_type
184 ) RETURN csov_rec_type IS
185 l_csov_rec csov_rec_type := p_csov_rec;
186 BEGIN
187 IF (l_csov_rec.rle_code = OKC_API.G_MISS_CHAR) THEN
188 l_csov_rec.rle_code := NULL;
189 END IF;
190 IF (l_csov_rec.cro_code = OKC_API.G_MISS_CHAR) THEN
191 l_csov_rec.cro_code := NULL;
192 END IF;
193 IF (l_csov_rec.end_date = OKC_API.G_MISS_DATE) THEN
194 l_csov_rec.end_date := NULL;
195 END IF;
196 IF (l_csov_rec.jtot_object_code = OKC_API.G_MISS_CHAR) THEN
197 l_csov_rec.JTOT_OBJECT_CODE := NULL;
198 END IF;
199 IF (l_csov_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
200 l_csov_rec.object_version_number := NULL;
201 END IF;
202 IF (l_csov_rec.constrained_yn = OKC_API.G_MISS_CHAR) THEN
203 l_csov_rec.constrained_yn := NULL;
204 END IF;
205 IF (l_csov_rec.created_by = OKC_API.G_MISS_NUM) THEN
206 l_csov_rec.created_by := NULL;
207 END IF;
208 IF (l_csov_rec.creation_date = OKC_API.G_MISS_DATE) THEN
209 l_csov_rec.creation_date := NULL;
210 END IF;
211 IF (l_csov_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
212 l_csov_rec.last_updated_by := NULL;
213 END IF;
214 IF (l_csov_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
215 l_csov_rec.last_update_date := NULL;
216 END IF;
217 IF (l_csov_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
218 l_csov_rec.last_update_login := NULL;
219 END IF;
220 IF (l_csov_rec.access_level = OKC_API.G_MISS_CHAR) THEN
221 l_csov_rec.access_level := NULL;
222 END IF;
223 RETURN(l_csov_rec);
224 END null_out_defaults;
225 ---------------------------------------------------------------------------
226 -- PROCEDURE Validate_Attributes
227 ---------------------------------------------------------------------------
228 /*+++++++++++++Start of hand code +++++++++++++++++*/
229 -- Start of comments
230 --
231 -- Procedure Name : validate_rle_code
232 -- Description :
233 -- Business Rules :
234 -- Parameters :
235 -- Version : 1.0
236 -- End of comments
237 procedure validate_rle_code(x_return_status OUT NOCOPY VARCHAR2,
238 p_csov_rec IN csov_REC_TYPE) is
239 begin
240 x_return_status := OKC_API.G_RET_STS_SUCCESS;
241 --1
242 if (p_csov_rec.rle_code = OKC_API.G_MISS_CHAR) then
243 return;
244 end if;
245 --2
246 if (p_csov_rec.rle_code is NULL) then
247 OKC_API.set_message(p_app_name => g_app_name,
248 p_msg_name => G_REQUIRED_VALUE,
249 p_token1 => G_COL_NAME_TOKEN,
250 p_token1_value => 'RLE_CODE');
251 raise G_EXCEPTION_HALT_VALIDATION;
252 end if;
253 --3
254 x_return_status := OKC_UTIL.check_lookup_code('OKC_ROLE',p_csov_rec.rle_code);
255 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
256 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RLE_CODE');
257 raise G_EXCEPTION_HALT_VALIDATION;
258 end if;
259 exception
260 when G_EXCEPTION_HALT_VALIDATION then
261 x_return_status := OKC_API.G_RET_STS_ERROR;
262 when OTHERS then
263 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
264 p_msg_name => g_unexpected_error,
265 p_token1 => g_sqlcode_token,
266 p_token1_value => sqlcode,
267 p_token2 => g_sqlerrm_token,
268 p_token2_value => sqlerrm);
269 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
270 end validate_rle_code;
271
272 -- Start of comments
273 --
274 -- Procedure Name : validate_cro_code
275 -- Description :
276 -- Business Rules :
277 -- Parameters :
278 -- Version : 1.0
279 -- End of comments
280 procedure validate_cro_code(x_return_status OUT NOCOPY VARCHAR2,
281 p_csov_rec IN csov_REC_TYPE) is
282 begin
283 x_return_status := OKC_API.G_RET_STS_SUCCESS;
284 --1
285 if (p_csov_rec.cro_code = OKC_API.G_MISS_CHAR) then
286 return;
287 end if;
288 --2
289 if (p_csov_rec.cro_code is NULL) then
290 OKC_API.set_message(p_app_name => g_app_name,
291 p_msg_name => G_REQUIRED_VALUE,
292 p_token1 => G_COL_NAME_TOKEN,
293 p_token1_value => 'CRO_CODE');
294 raise G_EXCEPTION_HALT_VALIDATION;
295 end if;
296 --3
297 x_return_status := OKC_UTIL.check_lookup_code('OKC_CONTACT_ROLE',p_csov_rec.cro_code);
298 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
299 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CRO_CODE');
300 raise G_EXCEPTION_HALT_VALIDATION;
301 end if;
302 exception
303 when G_EXCEPTION_HALT_VALIDATION then
304 x_return_status := OKC_API.G_RET_STS_ERROR;
305 when OTHERS then
306 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
307 p_msg_name => g_unexpected_error,
308 p_token1 => g_sqlcode_token,
309 p_token1_value => sqlcode,
310 p_token2 => g_sqlerrm_token,
311 p_token2_value => sqlerrm);
312 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
313 end validate_cro_code;
314
315 -- Start of comments
316 --
317 -- Procedure Name : validate_buy_or_sell
318 -- Description :
319 -- Business Rules :
320 -- Parameters :
321 -- Version : 1.0
322 -- End of comments
323 procedure validate_buy_or_sell(x_return_status OUT NOCOPY VARCHAR2,
324 p_csov_rec IN csov_REC_TYPE) is
325 begin
326 x_return_status := OKC_API.G_RET_STS_SUCCESS;
327 --1
328 if (P_csov_REC.buy_or_sell in ('B','S',OKC_API.G_MISS_CHAR)) then
329 return;
330 end if;
331 --2
332 if (P_csov_REC.buy_or_sell is NULL) then
333 OKC_API.set_message(p_app_name => g_app_name,
334 p_msg_name => G_REQUIRED_VALUE,
335 p_token1 => G_COL_NAME_TOKEN,
336 p_token1_value => 'BUY_OR_SELL');
337 x_return_status := OKC_API.G_RET_STS_ERROR;
338 return;
339 end if;
340 --3
341 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'BUY_OR_SELL');
342 x_return_status := OKC_API.G_RET_STS_ERROR;
343 end validate_buy_or_sell;
344
345 -- Start of comments
346 --
347 -- Procedure Name : validate_constrained_yn
348 -- Description :
349 -- Business Rules :
350 -- Parameters :
351 -- Version : 1.0
352 -- End of comments
353 procedure validate_constrained_yn(x_return_status OUT NOCOPY VARCHAR2,
354 p_csov_rec IN csov_REC_TYPE) is
355 begin
356 x_return_status := OKC_API.G_RET_STS_SUCCESS;
357 --1
358 if (P_csov_REC.constrained_yn in ('Y','N',OKC_API.G_MISS_CHAR)) then
359 return;
360 end if;
361 --2
362 if (P_csov_REC.constrained_yn is NULL) then
363 OKC_API.set_message(p_app_name => g_app_name,
364 p_msg_name => G_REQUIRED_VALUE,
365 p_token1 => G_COL_NAME_TOKEN,
366 p_token1_value => 'CONSTRAINED_YN');
367 x_return_status := OKC_API.G_RET_STS_ERROR;
368 return;
369 end if;
370 --3
371 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CONSTRAINED_YN');
372 x_return_status := OKC_API.G_RET_STS_ERROR;
373 end validate_constrained_yn;
374
375 ---------------------------------------------------------------------------
376 -- PROCEDURE Validate_ACCESS_LEVEL
377 ---------------------------------------------------------------------------
378 PROCEDURE validate_access_level(
379 p_csov_rec IN csov_rec_type,
380 x_return_status OUT NOCOPY VARCHAR2) IS
381 Begin
382
383 -- initialize return status
384 x_return_status := OKC_API.G_RET_STS_SUCCESS;
385
386 -- check that data exists
387 If (p_csov_rec.access_level <> OKC_API.G_MISS_CHAR and
388 p_csov_rec.access_level IS NOT NULL)
389 Then
390 If p_csov_rec.access_level NOT IN ('S','E', 'U') Then
391 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
392 p_msg_name => g_invalid_value,
393 p_token1 => g_col_name_token,
394 p_token1_value => 'access_level');
395 -- notify caller of an error
396 x_return_status := OKC_API.G_RET_STS_ERROR;
397
398 -- halt validation
399 raise G_EXCEPTION_HALT_VALIDATION;
400 end if;
401 End If;
402 exception
403 when G_EXCEPTION_HALT_VALIDATION then
404 -- no processing necessary; validation can continue with next column
405 null;
406
407 when OTHERS then
408 -- store SQL error message on message stack
409 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
410 p_msg_name => g_unexpected_error,
411 p_token1 => g_sqlcode_token,
412 p_token1_value => sqlcode,
413 p_token2 => g_sqlerrm_token,
414 p_token2_value => sqlerrm);
415 -- notify caller of an error as UNEXPETED error
416 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
417 End validate_access_level;
418
419 -- Start of comments
420 --
421 -- Procedure Name : validate_start_date
422 -- Description :
423 -- Business Rules :
424 -- Parameters :
425 -- Version : 1.0
426 -- End of comments
427 procedure validate_start_date(x_return_status OUT NOCOPY VARCHAR2,
428 p_csov_rec IN csov_REC_TYPE) is
429 begin
430 x_return_status := OKC_API.G_RET_STS_SUCCESS;
431 if (P_csov_REC.start_date is NULL) then
432 OKC_API.set_message(p_app_name => g_app_name,
433 p_msg_name => G_REQUIRED_VALUE,
434 p_token1 => G_COL_NAME_TOKEN,
435 p_token1_value => 'START_DATE');
436 x_return_status := OKC_API.G_RET_STS_ERROR;
437 return;
438 end if;
439 end validate_start_date;
440
441 -- Start of comments
442 --
443 -- Procedure Name : validate_JTOT_OBJECT_CODE
444 -- Description :
445 -- Business Rules :
446 -- Parameters :
447 -- Version : 1.0
448 -- End of comments
449
450 procedure validate_JTOT_OBJECT_CODE(x_return_status OUT NOCOPY VARCHAR2,
451 p_csov_rec IN csov_rec_TYPE) is
452 l_dummy_var varchar2(1) := '?';
453 --
454 cursor l_object_csr is
455 select '!'
456 from
457 jtf_objects_vl J, jtf_object_usages U
458 where
459 J.OBJECT_code = p_csov_rec.JTOT_OBJECT_CODE
460 and sysdate between NVL(J.START_DATE_ACTIVE,sysdate)
461 and NVL(J.END_DATE_ACTIVE,sysdate)
462 and U.OBJECT_code = p_csov_rec.JTOT_OBJECT_CODE
463 and U.object_user_code='OKX_CONTACTS';
464 begin
465 x_return_status := OKC_API.G_RET_STS_SUCCESS;
466 --1
467 if (p_csov_rec.jtot_object_code = OKC_API.G_MISS_CHAR) then
468 return;
469 end if;
470 --2
471 if (P_csov_REC.JTOT_OBJECT_CODE is NULL) then
472 OKC_API.set_message(p_app_name => g_app_name,
473 p_msg_name => G_REQUIRED_VALUE,
474 p_token1 => G_COL_NAME_TOKEN,
475 p_token1_value => 'JTOT_OBJECT_CODE');
476 x_return_status := OKC_API.G_RET_STS_ERROR;
477 return;
478 end if;
479 --3
480 open l_object_csr;
481 fetch l_object_csr into l_dummy_var;
482 close l_object_csr;
483 if (l_dummy_var = '?') then
484 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'JTOT_OBJECT_CODE');
485 x_return_status := OKC_API.G_RET_STS_ERROR;
486 return;
487 end if;
488 --
489 exception
490 when OTHERS then
491 if l_object_csr%ISOPEN then
492 close l_object_csr;
493 end if;
494 OKC_API.set_message(p_app_name => g_app_name,
495 p_msg_name => g_unexpected_error,
496 p_token1 => g_sqlcode_token,
497 p_token1_value => sqlcode,
498 p_token2 => g_sqlerrm_token,
499 p_token2_value => sqlerrm);
500 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
501 end validate_JTOT_OBJECT_CODE;
502 /*+++++++++++++End of hand code +++++++++++++++++++*/
503 ---------------------------------------------------
504 -- Validate_Attributes for:OKC_CONTACT_SOURCES_V --
505 ---------------------------------------------------
506 FUNCTION Validate_Attributes (
507 p_csov_rec IN csov_rec_type
508 ) RETURN VARCHAR2 IS
509 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
510 /*-------------Commented in favor of hand code------
511 BEGIN
512 IF p_csov_rec.rle_code = OKC_API.G_MISS_CHAR OR
513 p_csov_rec.rle_code IS NULL
514 THEN
515 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rle_code');
516 l_return_status := OKC_API.G_RET_STS_ERROR;
517 ELSIF p_csov_rec.cro_code = OKC_API.G_MISS_CHAR OR
518 p_csov_rec.cro_code IS NULL
519 THEN
520 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'cro_code');
521 l_return_status := OKC_API.G_RET_STS_ERROR;
522 ELSIF p_csov_rec.buy_or_sell = OKC_API.G_MISS_CHAR OR
523 p_csov_rec.buy_or_sell IS NULL
524 THEN
525 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'buy_or_sell');
526 l_return_status := OKC_API.G_RET_STS_ERROR;
527 ELSIF p_csov_rec.start_date = OKC_API.G_MISS_DATE OR
528 p_csov_rec.start_date IS NULL
529 THEN
530 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'start_date');
531 l_return_status := OKC_API.G_RET_STS_ERROR;
532 ELSIF p_csov_rec.jtot_object_code = OKC_API.G_MISS_CHAR OR
533 p_csov_rec.JTOT_OBJECT_CODE IS NULL
534 THEN
535 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'JTOT_OBJECT_CODE');
536 l_return_status := OKC_API.G_RET_STS_ERROR;
537 ELSIF p_csov_rec.object_version_number = OKC_API.G_MISS_NUM OR
538 p_csov_rec.object_version_number IS NULL
539 THEN
540 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
541 l_return_status := OKC_API.G_RET_STS_ERROR;
542 ELSIF p_csov_rec.constrained_yn = OKC_API.G_MISS_CHAR OR
543 p_csov_rec.constrained_yn IS NULL
544 THEN
545 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'constrained_yn');
546 l_return_status := OKC_API.G_RET_STS_ERROR;
547 ELSIF p_csov_rec.access_level = OKC_API.G_MISS_CHAR OR
548 p_csov_rec.access_level IS NULL
549 THEN
550 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'access_level');
551 l_return_status := OKC_API.G_RET_STS_ERROR;
552 END IF;
553 RETURN(l_return_status);
554 END Validate_Attributes;
555 /*+++++++++++++Start of hand code +++++++++++++++++*/
556 x_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
557 BEGIN
558 -- call each column-level validation
559 validate_rle_code(x_return_status => l_return_status,
560 p_csov_rec => p_csov_rec);
561 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
562 return OKC_API.G_RET_STS_UNEXP_ERROR;
563 end if;
564 if (l_return_status = OKC_API.G_RET_STS_ERROR
565 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
566 x_return_status := OKC_API.G_RET_STS_ERROR;
567 end if;
568 --
569 validate_cro_code(x_return_status => l_return_status,
570 p_csov_rec => p_csov_rec);
571 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
572 return OKC_API.G_RET_STS_UNEXP_ERROR;
573 end if;
574 if (l_return_status = OKC_API.G_RET_STS_ERROR
575 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
576 x_return_status := OKC_API.G_RET_STS_ERROR;
577 end if;
578 --
579 validate_buy_or_sell(x_return_status => l_return_status,
580 p_csov_rec => p_csov_rec);
581 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
582 return OKC_API.G_RET_STS_UNEXP_ERROR;
583 end if;
584 if (l_return_status = OKC_API.G_RET_STS_ERROR
585 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
586 x_return_status := OKC_API.G_RET_STS_ERROR;
587 end if;
588 --
589 validate_constrained_yn(x_return_status => l_return_status,
590 p_csov_rec => p_csov_rec);
591 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
592 return OKC_API.G_RET_STS_UNEXP_ERROR;
593 end if;
594 if (l_return_status = OKC_API.G_RET_STS_ERROR
595 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
596 x_return_status := OKC_API.G_RET_STS_ERROR;
597 end if;
598 --
599 validate_access_level(x_return_status => l_return_status,
600 p_csov_rec => p_csov_rec);
601 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
602 return OKC_API.G_RET_STS_UNEXP_ERROR;
603 end if;
604 if (l_return_status = OKC_API.G_RET_STS_ERROR
605 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
606 x_return_status := OKC_API.G_RET_STS_ERROR;
607 end if;
608 --
609 validate_start_date(x_return_status => l_return_status,
610 p_csov_rec => p_csov_rec);
611 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
612 return OKC_API.G_RET_STS_UNEXP_ERROR;
613 end if;
614 if (l_return_status = OKC_API.G_RET_STS_ERROR
615 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
616 x_return_status := OKC_API.G_RET_STS_ERROR;
617 end if;
618 --
619 validate_JTOT_OBJECT_CODE(x_return_status => l_return_status,
620 p_csov_rec => p_csov_rec);
621 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
622 return OKC_API.G_RET_STS_UNEXP_ERROR;
623 end if;
624 if (l_return_status = OKC_API.G_RET_STS_ERROR
625 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
626 x_return_status := OKC_API.G_RET_STS_ERROR;
627 end if;
628 return x_return_status;
629 exception
630 when OTHERS then
631 -- store SQL error message on message stack for caller
632 OKC_API.set_message(p_app_name => g_app_name,
633 p_msg_name => g_unexpected_error,
634 p_token1 => g_sqlcode_token,
635 p_token1_value => sqlcode,
636 p_token2 => g_sqlerrm_token,
637 p_token2_value => sqlerrm);
638 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
639 return x_return_status;
640 END Validate_Attributes;
641 /*+++++++++++++End of hand code +++++++++++++++++++*/
642
643 ---------------------------------------------------------------------------
644 -- PROCEDURE Validate_Record
645 ---------------------------------------------------------------------------
646 -----------------------------------------------
647 -- Validate_Record for:OKC_CONTACT_SOURCES_V --
648 -----------------------------------------------
649 FUNCTION Validate_Record (
650 p_csov_rec IN csov_rec_type
651 --+++++++++++++++Start handcode +++++++++++++++++++++++++++++++++++
652 ,p_mode IN varchar2 DEFAULT 'UPDATE' -- or 'INSERT'
653 --+++++++++++++++End handcode +++++++++++++++++++++++++++++++++++
654 ) RETURN VARCHAR2 IS
655 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
656 /*-------------Commented in favor of hand code------
657 ------------------------------------
658 -- FUNCTION validate_foreign_keys --
659 ------------------------------------
660 FUNCTION validate_foreign_keys (
661 p_csov_rec IN csov_rec_type
662 ) RETURN VARCHAR2 IS
663 item_not_found_error EXCEPTION;
664 CURSOR fnd_common_lookup_pk_csr (p_lookup_code IN VARCHAR2) IS
665 SELECT
666 APPLICATION_ID,
667 LOOKUP_TYPE,
668 LOOKUP_CODE,
669 MEANING,
670 DESCRIPTION,
671 ENABLED_FLAG,
672 START_DATE_ACTIVE,
673 END_DATE_ACTIVE,
674 CREATED_BY,
675 CREATION_DATE,
676 LAST_UPDATE_DATE,
677 LAST_UPDATED_BY,
678 LAST_UPDATE_LOGIN,
679 access_level,
680 FROM Fnd_Common_Lookups
681 WHERE fnd_common_lookups.lookup_code = p_lookup_code;
682 l_fnd_common_lookup_pk fnd_common_lookup_pk_csr%ROWTYPE;
683 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
684 l_row_notfound BOOLEAN := TRUE;
685 BEGIN
686 IF (p_csov_rec.CRO_CODE IS NOT NULL)
687 THEN
688 OPEN fnd_common_lookup_pk_csr(p_csov_rec.CRO_CODE);
689 FETCH fnd_common_lookup_pk_csr INTO l_fnd_common_lookup_pk;
690 l_row_notfound := fnd_common_lookup_pk_csr%NOTFOUND;
691 CLOSE fnd_common_lookup_pk_csr;
692 IF (l_row_notfound) THEN
693 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CRO_CODE');
694 RAISE item_not_found_error;
695 END IF;
696 END IF;
697 IF (p_csov_rec.RLE_CODE IS NOT NULL)
698 THEN
699 OPEN fnd_common_lookup_pk_csr(p_csov_rec.RLE_CODE);
700 FETCH fnd_common_lookup_pk_csr INTO l_fnd_common_lookup_pk;
701 l_row_notfound := fnd_common_lookup_pk_csr%NOTFOUND;
702 CLOSE fnd_common_lookup_pk_csr;
703 IF (l_row_notfound) THEN
704 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RLE_CODE');
705 RAISE item_not_found_error;
706 END IF;
707 END IF;
708 RETURN (l_return_status);
709 EXCEPTION
710 WHEN item_not_found_error THEN
711 l_return_status := OKC_API.G_RET_STS_ERROR;
712 RETURN (l_return_status);
713 END validate_foreign_keys;
714 BEGIN
715 l_return_status := validate_foreign_keys (p_csov_rec);
716 RETURN (l_return_status);
717 END Validate_Record;
718 ---------------End of the commented code-----------*/
719 /*+++++++++++++Start of hand code +++++++++++++++++*/
720 x_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
721 cursor pk_csr is
722 select '!' from okc_contact_sources
723 where rle_code = p_csov_rec.rle_code
724 and cro_code = p_csov_rec.cro_code
725 and buy_or_sell = p_csov_rec.buy_or_sell
726 and start_date = p_csov_rec.start_date;
727 l_dummy varchar2(1) := '?';
728 BEGIN
729 if (p_mode = 'INSERT') then
730 --
731 if (p_csov_rec.rle_code = OKC_API.G_MISS_CHAR
732 and p_csov_rec.cro_code = OKC_API.G_MISS_CHAR
733 and p_csov_rec.buy_or_sell = OKC_API.G_MISS_CHAR
734 and p_csov_rec.start_date = OKC_API.G_MISS_DATE )
735 then
736 return x_return_status;
737 end if;
738 --
739 open pk_csr;
740 fetch pk_csr into l_dummy;
741 close pk_csr;
742 --
743 if (l_dummy = '?')
744 then
745 return x_return_status;
746 end if;
747 --
748 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RLE_CODE, CRO_CODE, BUY_OR_SELL, START_DATE');
749 return OKC_API.G_RET_STS_ERROR;
750 else -- other mode than INSERT
751 return x_return_status;
752 end if;
753 exception
754 when OTHERS then
755 -- store SQL error message on message stack for caller
756 OKC_API.set_message(p_app_name => g_app_name,
757 p_msg_name => g_unexpected_error,
758 p_token1 => g_sqlcode_token,
759 p_token1_value => sqlcode,
760 p_token2 => g_sqlerrm_token,
761 p_token2_value => sqlerrm);
762 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
763 return x_return_status;
764 END Validate_Record;
765 /*+++++++++++++End of hand code +++++++++++++++++*/
766
767 ---------------------------------------------------------------------------
768 -- PROCEDURE Migrate
769 ---------------------------------------------------------------------------
770 PROCEDURE migrate (
771 p_from IN csov_rec_type,
772 p_to IN OUT NOCOPY cso_rec_type
773 ) IS
774 BEGIN
775 p_to.cro_code := p_from.cro_code;
776 p_to.rle_code := p_from.rle_code;
777 p_to.buy_or_sell := p_from.buy_or_sell;
778 p_to.start_date := p_from.start_date;
779 p_to.end_date := p_from.end_date;
780 p_to.JTOT_OBJECT_CODE := p_from.JTOT_OBJECT_CODE;
781 p_to.object_version_number := p_from.object_version_number;
782 p_to.created_by := p_from.created_by;
783 p_to.creation_date := p_from.creation_date;
784 p_to.last_updated_by := p_from.last_updated_by;
785 p_to.last_update_date := p_from.last_update_date;
786 p_to.constrained_yn := p_from.constrained_yn;
787 p_to.last_update_login := p_from.last_update_login;
788 p_to.access_level := p_from.access_level;
789 END migrate;
790 PROCEDURE migrate (
791 p_from IN cso_rec_type,
792 p_to IN OUT NOCOPY csov_rec_type
793 ) IS
794 BEGIN
795 p_to.cro_code := p_from.cro_code;
796 p_to.rle_code := p_from.rle_code;
797 p_to.buy_or_sell := p_from.buy_or_sell;
798 p_to.start_date := p_from.start_date;
799 p_to.end_date := p_from.end_date;
800 p_to.JTOT_OBJECT_CODE := p_from.JTOT_OBJECT_CODE;
801 p_to.object_version_number := p_from.object_version_number;
802 p_to.created_by := p_from.created_by;
803 p_to.creation_date := p_from.creation_date;
804 p_to.last_updated_by := p_from.last_updated_by;
805 p_to.last_update_date := p_from.last_update_date;
806 p_to.constrained_yn := p_from.constrained_yn;
807 p_to.last_update_login := p_from.last_update_login;
808 p_to.access_level := p_from.access_level;
809 END migrate;
810
811 ---------------------------------------------------------------------------
812 -- PROCEDURE validate_row
813 ---------------------------------------------------------------------------
814 --------------------------------------------
815 -- validate_row for:OKC_CONTACT_SOURCES_V --
816 --------------------------------------------
817 PROCEDURE validate_row(
818 p_api_version IN NUMBER,
819 p_init_msg_list IN VARCHAR2 ,
820 x_return_status OUT NOCOPY VARCHAR2,
821 x_msg_count OUT NOCOPY NUMBER,
822 x_msg_data OUT NOCOPY VARCHAR2,
823 p_csov_rec IN csov_rec_type) IS
824
825 l_api_version CONSTANT NUMBER := 1;
826 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
827 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
828 l_csov_rec csov_rec_type := p_csov_rec;
829 l_cso_rec cso_rec_type;
830 BEGIN
831 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
832 G_PKG_NAME,
833 p_init_msg_list,
834 l_api_version,
835 p_api_version,
836 '_PVT',
837 x_return_status);
838 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
839 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
840 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
841 RAISE OKC_API.G_EXCEPTION_ERROR;
842 END IF;
843 --- Validate all non-missing attributes (Item Level Validation)
844 l_return_status := Validate_Attributes(l_csov_rec);
845 --- If any errors happen abort API
846 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
847 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
848 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
849 RAISE OKC_API.G_EXCEPTION_ERROR;
850 END IF;
851 l_return_status := Validate_Record(l_csov_rec);
852 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
853 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
854 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
855 RAISE OKC_API.G_EXCEPTION_ERROR;
856 END IF;
857 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
858 EXCEPTION
859 WHEN OKC_API.G_EXCEPTION_ERROR THEN
860 x_return_status := OKC_API.HANDLE_EXCEPTIONS
861 (
862 l_api_name,
863 G_PKG_NAME,
864 'OKC_API.G_RET_STS_ERROR',
865 x_msg_count,
866 x_msg_data,
867 '_PVT'
868 );
869 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
870 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
871 (
872 l_api_name,
873 G_PKG_NAME,
874 'OKC_API.G_RET_STS_UNEXP_ERROR',
875 x_msg_count,
876 x_msg_data,
877 '_PVT'
878 );
879 WHEN OTHERS THEN
880 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
881 (
882 l_api_name,
883 G_PKG_NAME,
884 'OTHERS',
885 x_msg_count,
886 x_msg_data,
887 '_PVT'
888 );
889 END validate_row;
890 ------------------------------------------
891 -- PL/SQL TBL validate_row for:CSOV_TBL --
892 ------------------------------------------
893 PROCEDURE validate_row(
894 p_api_version IN NUMBER,
895 p_init_msg_list IN VARCHAR2 ,
896 x_return_status OUT NOCOPY VARCHAR2,
897 x_msg_count OUT NOCOPY NUMBER,
898 x_msg_data OUT NOCOPY VARCHAR2,
899 p_csov_tbl IN csov_tbl_type) IS
900
901 l_api_version CONSTANT NUMBER := 1;
902 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
903 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
904 i NUMBER := 0;
905 BEGIN
906 OKC_API.init_msg_list(p_init_msg_list);
907 -- Make sure PL/SQL table has records in it before passing
908 IF (p_csov_tbl.COUNT > 0) THEN
909 i := p_csov_tbl.FIRST;
910 LOOP
911 validate_row (
912 p_api_version => p_api_version,
913 p_init_msg_list => OKC_API.G_FALSE,
914 x_return_status => x_return_status,
915 x_msg_count => x_msg_count,
916 x_msg_data => x_msg_data,
917 p_csov_rec => p_csov_tbl(i));
918 EXIT WHEN (i = p_csov_tbl.LAST);
919 i := p_csov_tbl.NEXT(i);
920 END LOOP;
921 END IF;
922 EXCEPTION
923 WHEN OKC_API.G_EXCEPTION_ERROR THEN
924 x_return_status := OKC_API.HANDLE_EXCEPTIONS
925 (
926 l_api_name,
927 G_PKG_NAME,
928 'OKC_API.G_RET_STS_ERROR',
929 x_msg_count,
930 x_msg_data,
931 '_PVT'
932 );
933 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
934 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
935 (
936 l_api_name,
937 G_PKG_NAME,
938 'OKC_API.G_RET_STS_UNEXP_ERROR',
939 x_msg_count,
940 x_msg_data,
941 '_PVT'
942 );
943 WHEN OTHERS THEN
944 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
945 (
946 l_api_name,
947 G_PKG_NAME,
948 'OTHERS',
949 x_msg_count,
950 x_msg_data,
951 '_PVT'
952 );
953 END validate_row;
954
955 ---------------------------------------------------------------------------
956 -- PROCEDURE insert_row
957 ---------------------------------------------------------------------------
958 ----------------------------------------
959 -- insert_row for:OKC_CONTACT_SOURCES --
960 ----------------------------------------
961 PROCEDURE insert_row(
962 p_init_msg_list IN VARCHAR2 ,
963 x_return_status OUT NOCOPY VARCHAR2,
964 x_msg_count OUT NOCOPY NUMBER,
965 x_msg_data OUT NOCOPY VARCHAR2,
966 p_cso_rec IN cso_rec_type,
967 x_cso_rec OUT NOCOPY cso_rec_type) IS
968
969 l_api_version CONSTANT NUMBER := 1;
970 l_api_name CONSTANT VARCHAR2(30) := 'SOURCES_insert_row';
971 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
972 l_cso_rec cso_rec_type := p_cso_rec;
973 l_def_cso_rec cso_rec_type;
974 --------------------------------------------
975 -- Set_Attributes for:OKC_CONTACT_SOURCES --
976 --------------------------------------------
977 FUNCTION Set_Attributes (
978 p_cso_rec IN cso_rec_type,
979 x_cso_rec OUT NOCOPY cso_rec_type
980 ) RETURN VARCHAR2 IS
981 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
982 BEGIN
983 x_cso_rec := p_cso_rec;
984 RETURN(l_return_status);
985 END Set_Attributes;
986 BEGIN
987 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
988 p_init_msg_list,
989 '_PVT',
990 x_return_status);
991 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
992 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
993 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
994 RAISE OKC_API.G_EXCEPTION_ERROR;
995 END IF;
996 --- Setting item attributes
997 l_return_status := Set_Attributes(
998 p_cso_rec, -- IN
999 l_cso_rec); -- OUT
1000 --- If any errors happen abort API
1001 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1002 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1003 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1004 RAISE OKC_API.G_EXCEPTION_ERROR;
1005 END IF;
1006 INSERT INTO OKC_CONTACT_SOURCES(
1007 cro_code,
1008 rle_code,
1009 buy_or_sell,
1010 start_date,
1011 end_date,
1012 JTOT_OBJECT_CODE,
1013 object_version_number,
1014 created_by,
1015 creation_date,
1016 last_updated_by,
1017 last_update_date,
1018 constrained_yn,
1019 last_update_login,
1020 access_level)
1021 VALUES (
1022 l_cso_rec.cro_code,
1023 l_cso_rec.rle_code,
1024 l_cso_rec.buy_or_sell,
1025 l_cso_rec.start_date,
1026 l_cso_rec.end_date,
1027 l_cso_rec.JTOT_OBJECT_CODE,
1028 l_cso_rec.object_version_number,
1029 l_cso_rec.created_by,
1030 l_cso_rec.creation_date,
1031 l_cso_rec.last_updated_by,
1032 l_cso_rec.last_update_date,
1033 l_cso_rec.constrained_yn,
1034 l_cso_rec.last_update_login,
1035 l_cso_rec.access_level);
1036 -- Set OUT values
1037 x_cso_rec := l_cso_rec;
1038 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1039 EXCEPTION
1040 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1041 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1042 (
1043 l_api_name,
1044 G_PKG_NAME,
1045 'OKC_API.G_RET_STS_ERROR',
1046 x_msg_count,
1047 x_msg_data,
1048 '_PVT'
1049 );
1050 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1051 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1052 (
1053 l_api_name,
1054 G_PKG_NAME,
1055 'OKC_API.G_RET_STS_UNEXP_ERROR',
1056 x_msg_count,
1057 x_msg_data,
1058 '_PVT'
1059 );
1060 WHEN OTHERS THEN
1061 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1062 (
1063 l_api_name,
1064 G_PKG_NAME,
1065 'OTHERS',
1066 x_msg_count,
1067 x_msg_data,
1068 '_PVT'
1069 );
1070 END insert_row;
1071 ------------------------------------------
1072 -- insert_row for:OKC_CONTACT_SOURCES_V --
1073 ------------------------------------------
1074 PROCEDURE insert_row(
1075 p_api_version IN NUMBER,
1076 p_init_msg_list IN VARCHAR2 ,
1077 x_return_status OUT NOCOPY VARCHAR2,
1078 x_msg_count OUT NOCOPY NUMBER,
1079 x_msg_data OUT NOCOPY VARCHAR2,
1080 p_csov_rec IN csov_rec_type,
1081 x_csov_rec OUT NOCOPY csov_rec_type) IS
1082
1083 l_api_version CONSTANT NUMBER := 1;
1084 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1085 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1086 l_csov_rec csov_rec_type;
1087 l_def_csov_rec csov_rec_type;
1088 l_cso_rec cso_rec_type;
1089 lx_cso_rec cso_rec_type;
1090 -------------------------------
1091 -- FUNCTION fill_who_columns --
1092 -------------------------------
1093 FUNCTION fill_who_columns (
1094 p_csov_rec IN csov_rec_type
1095 ) RETURN csov_rec_type IS
1096 l_csov_rec csov_rec_type := p_csov_rec;
1097 BEGIN
1098 l_csov_rec.CREATION_DATE := SYSDATE;
1099 l_csov_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1100 l_csov_rec.LAST_UPDATE_DATE := SYSDATE;
1101 l_csov_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1102 l_csov_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1103 RETURN(l_csov_rec);
1104 END fill_who_columns;
1105 ----------------------------------------------
1106 -- Set_Attributes for:OKC_CONTACT_SOURCES_V --
1107 ----------------------------------------------
1108 FUNCTION Set_Attributes (
1109 p_csov_rec IN csov_rec_type,
1110 x_csov_rec OUT NOCOPY csov_rec_type
1111 ) RETURN VARCHAR2 IS
1112 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1113 BEGIN
1114 x_csov_rec := p_csov_rec;
1115 x_csov_rec.OBJECT_VERSION_NUMBER := 1;
1116 RETURN(l_return_status);
1117 END Set_Attributes;
1118 BEGIN
1119 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1120 G_PKG_NAME,
1121 p_init_msg_list,
1122 l_api_version,
1123 p_api_version,
1124 '_PVT',
1125 x_return_status);
1126 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1127 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1128 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1129 RAISE OKC_API.G_EXCEPTION_ERROR;
1130 END IF;
1131 l_csov_rec := null_out_defaults(p_csov_rec);
1132 --- Setting item attributes
1133 l_return_status := Set_Attributes(
1134 l_csov_rec, -- IN
1135 l_def_csov_rec); -- OUT
1136 --- If any errors happen abort API
1137 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1138 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1139 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1140 RAISE OKC_API.G_EXCEPTION_ERROR;
1141 END IF;
1142 l_def_csov_rec := fill_who_columns(l_def_csov_rec);
1143 --- Validate all non-missing attributes (Item Level Validation)
1144 l_return_status := Validate_Attributes(l_def_csov_rec);
1145 --- If any errors happen abort API
1146 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1147 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1148 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1149 RAISE OKC_API.G_EXCEPTION_ERROR;
1150 END IF;
1151 /*------------------------commented in favor of hand code-----------
1152 l_return_status := Validate_Record(l_def_csov_rec);
1153 ------------------------commented in favor of hand code-----------*/
1154 --++++++++++++++++++++++Hand code start+++++++++++++++++++++++++++++
1155 l_return_status := Validate_Record(l_def_csov_rec,'INSERT');
1156 --++++++++++++++++++++++Hand code end+++++++++++++++++++++++++++++
1157 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1158 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1159 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1160 RAISE OKC_API.G_EXCEPTION_ERROR;
1161 END IF;
1162 --------------------------------------
1163 -- Move VIEW record to "Child" records
1164 --------------------------------------
1165 migrate(l_def_csov_rec, l_cso_rec);
1166 --------------------------------------------
1167 -- Call the INSERT_ROW for each child record
1168 --------------------------------------------
1169 insert_row(
1170 p_init_msg_list,
1171 x_return_status,
1172 x_msg_count,
1173 x_msg_data,
1174 l_cso_rec,
1175 lx_cso_rec
1176 );
1177 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1178 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1179 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1180 RAISE OKC_API.G_EXCEPTION_ERROR;
1181 END IF;
1182 migrate(lx_cso_rec, l_def_csov_rec);
1183 -- Set OUT values
1184 x_csov_rec := l_def_csov_rec;
1185 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1186 EXCEPTION
1187 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1188 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1189 (
1190 l_api_name,
1191 G_PKG_NAME,
1192 'OKC_API.G_RET_STS_ERROR',
1193 x_msg_count,
1194 x_msg_data,
1195 '_PVT'
1196 );
1197 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1198 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1199 (
1200 l_api_name,
1201 G_PKG_NAME,
1202 'OKC_API.G_RET_STS_UNEXP_ERROR',
1203 x_msg_count,
1204 x_msg_data,
1205 '_PVT'
1206 );
1207 WHEN OTHERS THEN
1208 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1209 (
1210 l_api_name,
1211 G_PKG_NAME,
1212 'OTHERS',
1213 x_msg_count,
1214 x_msg_data,
1215 '_PVT'
1216 );
1217 END insert_row;
1218 ----------------------------------------
1219 -- PL/SQL TBL insert_row for:CSOV_TBL --
1220 ----------------------------------------
1221 PROCEDURE insert_row(
1222 p_api_version IN NUMBER,
1223 p_init_msg_list IN VARCHAR2 ,
1224 x_return_status OUT NOCOPY VARCHAR2,
1225 x_msg_count OUT NOCOPY NUMBER,
1226 x_msg_data OUT NOCOPY VARCHAR2,
1227 p_csov_tbl IN csov_tbl_type,
1228 x_csov_tbl OUT NOCOPY csov_tbl_type) IS
1229
1230 l_api_version CONSTANT NUMBER := 1;
1231 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1232 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1233 i NUMBER := 0;
1234 BEGIN
1235 OKC_API.init_msg_list(p_init_msg_list);
1236 -- Make sure PL/SQL table has records in it before passing
1237 IF (p_csov_tbl.COUNT > 0) THEN
1238 i := p_csov_tbl.FIRST;
1239 LOOP
1240 insert_row (
1241 p_api_version => p_api_version,
1242 p_init_msg_list => OKC_API.G_FALSE,
1243 x_return_status => x_return_status,
1244 x_msg_count => x_msg_count,
1245 x_msg_data => x_msg_data,
1246 p_csov_rec => p_csov_tbl(i),
1247 x_csov_rec => x_csov_tbl(i));
1248 EXIT WHEN (i = p_csov_tbl.LAST);
1249 i := p_csov_tbl.NEXT(i);
1250 END LOOP;
1251 END IF;
1252 EXCEPTION
1253 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1254 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1255 (
1256 l_api_name,
1257 G_PKG_NAME,
1258 'OKC_API.G_RET_STS_ERROR',
1259 x_msg_count,
1260 x_msg_data,
1261 '_PVT'
1262 );
1263 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1264 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1265 (
1266 l_api_name,
1267 G_PKG_NAME,
1268 'OKC_API.G_RET_STS_UNEXP_ERROR',
1269 x_msg_count,
1270 x_msg_data,
1271 '_PVT'
1272 );
1273 WHEN OTHERS THEN
1274 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1275 (
1276 l_api_name,
1277 G_PKG_NAME,
1278 'OTHERS',
1279 x_msg_count,
1280 x_msg_data,
1281 '_PVT'
1282 );
1283 END insert_row;
1284
1285 ---------------------------------------------------------------------------
1286 -- PROCEDURE lock_row
1287 ---------------------------------------------------------------------------
1288 --------------------------------------
1289 -- lock_row for:OKC_CONTACT_SOURCES --
1290 --------------------------------------
1291 PROCEDURE lock_row(
1292 p_init_msg_list IN VARCHAR2 ,
1293 x_return_status OUT NOCOPY VARCHAR2,
1294 x_msg_count OUT NOCOPY NUMBER,
1295 x_msg_data OUT NOCOPY VARCHAR2,
1296 p_cso_rec IN cso_rec_type) IS
1297
1298 E_Resource_Busy EXCEPTION;
1299 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1300 CURSOR lock_csr (p_cso_rec IN cso_rec_type) IS
1301 SELECT OBJECT_VERSION_NUMBER
1302 FROM OKC_CONTACT_SOURCES
1303 WHERE CRO_CODE = p_cso_rec.cro_code
1304 AND RLE_CODE = p_cso_rec.rle_code
1305 AND BUY_OR_SELL = p_cso_rec.buy_or_sell
1306 AND START_DATE = p_cso_rec.start_date
1307 AND OBJECT_VERSION_NUMBER = p_cso_rec.object_version_number
1308 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1309
1310 CURSOR lchk_csr (p_cso_rec IN cso_rec_type) IS
1311 SELECT OBJECT_VERSION_NUMBER
1312 FROM OKC_CONTACT_SOURCES
1313 WHERE CRO_CODE = p_cso_rec.cro_code
1314 AND RLE_CODE = p_cso_rec.rle_code
1315 AND BUY_OR_SELL = p_cso_rec.buy_or_sell
1316 AND START_DATE = p_cso_rec.start_date;
1317 l_api_version CONSTANT NUMBER := 1;
1318 l_api_name CONSTANT VARCHAR2(30) := 'SOURCES_lock_row';
1319 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1320 l_object_version_number OKC_CONTACT_SOURCES.OBJECT_VERSION_NUMBER%TYPE;
1321 lc_object_version_number OKC_CONTACT_SOURCES.OBJECT_VERSION_NUMBER%TYPE;
1322 l_row_notfound BOOLEAN := FALSE;
1323 lc_row_notfound BOOLEAN := FALSE;
1324 BEGIN
1325 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1326 p_init_msg_list,
1327 '_PVT',
1328 x_return_status);
1329 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1330 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1331 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1332 RAISE OKC_API.G_EXCEPTION_ERROR;
1333 END IF;
1334 BEGIN
1335 OPEN lock_csr(p_cso_rec);
1336 FETCH lock_csr INTO l_object_version_number;
1337 l_row_notfound := lock_csr%NOTFOUND;
1338 CLOSE lock_csr;
1339 EXCEPTION
1340 WHEN E_Resource_Busy THEN
1341 IF (lock_csr%ISOPEN) THEN
1342 CLOSE lock_csr;
1343 END IF;
1344 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1345 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1346 END;
1347
1348 IF ( l_row_notfound ) THEN
1349 OPEN lchk_csr(p_cso_rec);
1350 FETCH lchk_csr INTO lc_object_version_number;
1351 lc_row_notfound := lchk_csr%NOTFOUND;
1352 CLOSE lchk_csr;
1353 END IF;
1354 IF (lc_row_notfound) THEN
1355 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1356 RAISE OKC_API.G_EXCEPTION_ERROR;
1357 ELSIF lc_object_version_number > p_cso_rec.object_version_number THEN
1358 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1359 RAISE OKC_API.G_EXCEPTION_ERROR;
1360 ELSIF lc_object_version_number <> p_cso_rec.object_version_number THEN
1361 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1362 RAISE OKC_API.G_EXCEPTION_ERROR;
1363 ELSIF lc_object_version_number = -1 THEN
1364 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1365 RAISE OKC_API.G_EXCEPTION_ERROR;
1366 END IF;
1367 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1368 EXCEPTION
1369 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1370 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1371 (
1372 l_api_name,
1373 G_PKG_NAME,
1374 'OKC_API.G_RET_STS_ERROR',
1375 x_msg_count,
1376 x_msg_data,
1377 '_PVT'
1378 );
1379 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1380 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1381 (
1382 l_api_name,
1383 G_PKG_NAME,
1384 'OKC_API.G_RET_STS_UNEXP_ERROR',
1385 x_msg_count,
1386 x_msg_data,
1387 '_PVT'
1388 );
1389 WHEN OTHERS THEN
1390 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1391 (
1392 l_api_name,
1393 G_PKG_NAME,
1394 'OTHERS',
1395 x_msg_count,
1396 x_msg_data,
1397 '_PVT'
1398 );
1399 END lock_row;
1400 ----------------------------------------
1401 -- lock_row for:OKC_CONTACT_SOURCES_V --
1402 ----------------------------------------
1403 PROCEDURE lock_row(
1404 p_api_version IN NUMBER,
1405 p_init_msg_list IN VARCHAR2 ,
1406 x_return_status OUT NOCOPY VARCHAR2,
1407 x_msg_count OUT NOCOPY NUMBER,
1408 x_msg_data OUT NOCOPY VARCHAR2,
1409 p_csov_rec IN csov_rec_type) IS
1410
1411 l_api_version CONSTANT NUMBER := 1;
1412 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1413 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1414 l_cso_rec cso_rec_type;
1415 BEGIN
1416 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1417 G_PKG_NAME,
1418 p_init_msg_list,
1419 l_api_version,
1420 p_api_version,
1421 '_PVT',
1422 x_return_status);
1423 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1424 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1425 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1426 RAISE OKC_API.G_EXCEPTION_ERROR;
1427 END IF;
1428 --------------------------------------
1429 -- Move VIEW record to "Child" records
1430 --------------------------------------
1431 migrate(p_csov_rec, l_cso_rec);
1432 --------------------------------------------
1433 -- Call the LOCK_ROW for each child record
1434 --------------------------------------------
1435 lock_row(
1436 p_init_msg_list,
1437 x_return_status,
1438 x_msg_count,
1439 x_msg_data,
1440 l_cso_rec
1441 );
1442 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1443 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1444 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1445 RAISE OKC_API.G_EXCEPTION_ERROR;
1446 END IF;
1447 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1448 EXCEPTION
1449 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1450 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1451 (
1452 l_api_name,
1453 G_PKG_NAME,
1454 'OKC_API.G_RET_STS_ERROR',
1455 x_msg_count,
1456 x_msg_data,
1457 '_PVT'
1458 );
1459 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1460 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1461 (
1462 l_api_name,
1463 G_PKG_NAME,
1464 'OKC_API.G_RET_STS_UNEXP_ERROR',
1465 x_msg_count,
1466 x_msg_data,
1467 '_PVT'
1468 );
1469 WHEN OTHERS THEN
1470 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1471 (
1472 l_api_name,
1473 G_PKG_NAME,
1474 'OTHERS',
1475 x_msg_count,
1476 x_msg_data,
1477 '_PVT'
1478 );
1479 END lock_row;
1480 --------------------------------------
1481 -- PL/SQL TBL lock_row for:CSOV_TBL --
1482 --------------------------------------
1483 PROCEDURE lock_row(
1484 p_api_version IN NUMBER,
1485 p_init_msg_list IN VARCHAR2 ,
1486 x_return_status OUT NOCOPY VARCHAR2,
1487 x_msg_count OUT NOCOPY NUMBER,
1488 x_msg_data OUT NOCOPY VARCHAR2,
1489 p_csov_tbl IN csov_tbl_type) IS
1490
1491 l_api_version CONSTANT NUMBER := 1;
1492 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1493 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1494 i NUMBER := 0;
1495 BEGIN
1496 OKC_API.init_msg_list(p_init_msg_list);
1497 -- Make sure PL/SQL table has records in it before passing
1498 IF (p_csov_tbl.COUNT > 0) THEN
1499 i := p_csov_tbl.FIRST;
1500 LOOP
1501 lock_row (
1502 p_api_version => p_api_version,
1503 p_init_msg_list => OKC_API.G_FALSE,
1504 x_return_status => x_return_status,
1505 x_msg_count => x_msg_count,
1506 x_msg_data => x_msg_data,
1507 p_csov_rec => p_csov_tbl(i));
1508 EXIT WHEN (i = p_csov_tbl.LAST);
1509 i := p_csov_tbl.NEXT(i);
1510 END LOOP;
1511 END IF;
1512 EXCEPTION
1513 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1514 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1515 (
1516 l_api_name,
1517 G_PKG_NAME,
1518 'OKC_API.G_RET_STS_ERROR',
1519 x_msg_count,
1520 x_msg_data,
1521 '_PVT'
1522 );
1523 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1524 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1525 (
1526 l_api_name,
1527 G_PKG_NAME,
1528 'OKC_API.G_RET_STS_UNEXP_ERROR',
1529 x_msg_count,
1530 x_msg_data,
1531 '_PVT'
1532 );
1533 WHEN OTHERS THEN
1534 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1535 (
1536 l_api_name,
1537 G_PKG_NAME,
1538 'OTHERS',
1539 x_msg_count,
1540 x_msg_data,
1541 '_PVT'
1542 );
1543 END lock_row;
1544
1545 ---------------------------------------------------------------------------
1546 -- PROCEDURE update_row
1547 ---------------------------------------------------------------------------
1548 ----------------------------------------
1549 -- update_row for:OKC_CONTACT_SOURCES --
1550 ----------------------------------------
1551 PROCEDURE update_row(
1552 p_init_msg_list IN VARCHAR2 ,
1553 x_return_status OUT NOCOPY VARCHAR2,
1554 x_msg_count OUT NOCOPY NUMBER,
1555 x_msg_data OUT NOCOPY VARCHAR2,
1556 p_cso_rec IN cso_rec_type,
1557 x_cso_rec OUT NOCOPY cso_rec_type) IS
1558
1559 l_api_version CONSTANT NUMBER := 1;
1560 l_api_name CONSTANT VARCHAR2(30) := 'SOURCES_update_row';
1561 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1562 l_cso_rec cso_rec_type := p_cso_rec;
1563 l_def_cso_rec cso_rec_type;
1564 l_row_notfound BOOLEAN := TRUE;
1565 ----------------------------------
1566 -- FUNCTION populate_new_record --
1567 ----------------------------------
1568 FUNCTION populate_new_record (
1569 p_cso_rec IN cso_rec_type,
1570 x_cso_rec OUT NOCOPY cso_rec_type
1571 ) RETURN VARCHAR2 IS
1572 l_cso_rec cso_rec_type;
1573 l_row_notfound BOOLEAN := TRUE;
1574 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1575 BEGIN
1576 x_cso_rec := p_cso_rec;
1577 -- Get current database values
1578 l_cso_rec := get_rec(p_cso_rec, l_row_notfound);
1579 IF (l_row_notfound) THEN
1580 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1581 END IF;
1582 IF (x_cso_rec.cro_code = OKC_API.G_MISS_CHAR)
1583 THEN
1584 x_cso_rec.cro_code := l_cso_rec.cro_code;
1585 END IF;
1586 IF (x_cso_rec.rle_code = OKC_API.G_MISS_CHAR)
1587 THEN
1588 x_cso_rec.rle_code := l_cso_rec.rle_code;
1589 END IF;
1590 IF (x_cso_rec.buy_or_sell = OKC_API.G_MISS_CHAR)
1591 THEN
1592 x_cso_rec.buy_or_sell := l_cso_rec.buy_or_sell;
1593 END IF;
1594 IF (x_cso_rec.start_date = OKC_API.G_MISS_DATE)
1595 THEN
1596 x_cso_rec.start_date := l_cso_rec.start_date;
1597 END IF;
1598 IF (x_cso_rec.end_date = OKC_API.G_MISS_DATE)
1599 THEN
1600 x_cso_rec.end_date := l_cso_rec.end_date;
1601 END IF;
1602 IF (x_cso_rec.jtot_object_code = OKC_API.G_MISS_CHAR)
1603 THEN
1604 x_cso_rec.JTOT_OBJECT_CODE := l_cso_rec.JTOT_OBJECT_CODE;
1605 END IF;
1606 IF (x_cso_rec.object_version_number = OKC_API.G_MISS_NUM)
1607 THEN
1608 x_cso_rec.object_version_number := l_cso_rec.object_version_number;
1609 END IF;
1610 IF (x_cso_rec.created_by = OKC_API.G_MISS_NUM)
1611 THEN
1612 x_cso_rec.created_by := l_cso_rec.created_by;
1613 END IF;
1614 IF (x_cso_rec.creation_date = OKC_API.G_MISS_DATE)
1615 THEN
1616 x_cso_rec.creation_date := l_cso_rec.creation_date;
1617 END IF;
1618 IF (x_cso_rec.last_updated_by = OKC_API.G_MISS_NUM)
1619 THEN
1620 x_cso_rec.last_updated_by := l_cso_rec.last_updated_by;
1621 END IF;
1622 IF (x_cso_rec.last_update_date = OKC_API.G_MISS_DATE)
1623 THEN
1624 x_cso_rec.last_update_date := l_cso_rec.last_update_date;
1625 END IF;
1626 IF (x_cso_rec.constrained_yn = OKC_API.G_MISS_CHAR)
1627 THEN
1628 x_cso_rec.constrained_yn := l_cso_rec.constrained_yn;
1629 END IF;
1630 IF (x_cso_rec.last_update_login = OKC_API.G_MISS_NUM)
1631 THEN
1632 x_cso_rec.last_update_login := l_cso_rec.last_update_login;
1633 END IF;
1634 IF (x_cso_rec.access_level = OKC_API.G_MISS_CHAR)
1635 THEN
1636 x_cso_rec.access_level := l_cso_rec.access_level;
1637 END IF;
1638 RETURN(l_return_status);
1639 END populate_new_record;
1640 --------------------------------------------
1641 -- Set_Attributes for:OKC_CONTACT_SOURCES --
1642 --------------------------------------------
1643 FUNCTION Set_Attributes (
1644 p_cso_rec IN cso_rec_type,
1645 x_cso_rec OUT NOCOPY cso_rec_type
1646 ) RETURN VARCHAR2 IS
1647 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1648 BEGIN
1649 x_cso_rec := p_cso_rec;
1650 RETURN(l_return_status);
1651 END Set_Attributes;
1652 BEGIN
1653 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1654 p_init_msg_list,
1655 '_PVT',
1656 x_return_status);
1657 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1658 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1659 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1660 RAISE OKC_API.G_EXCEPTION_ERROR;
1661 END IF;
1662 --- Setting item attributes
1663 l_return_status := Set_Attributes(
1664 p_cso_rec, -- IN
1665 l_cso_rec); -- OUT
1666 --- If any errors happen abort API
1667 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1668 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1669 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1670 RAISE OKC_API.G_EXCEPTION_ERROR;
1671 END IF;
1672 l_return_status := populate_new_record(l_cso_rec, l_def_cso_rec);
1673 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1674 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1675 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1676 RAISE OKC_API.G_EXCEPTION_ERROR;
1677 END IF;
1678 UPDATE OKC_CONTACT_SOURCES
1679 SET END_DATE = l_def_cso_rec.end_date,
1680 JTOT_OBJECT_CODE = l_def_cso_rec.JTOT_OBJECT_CODE,
1681 OBJECT_VERSION_NUMBER = l_def_cso_rec.object_version_number,
1682 CREATED_BY = l_def_cso_rec.created_by,
1683 CREATION_DATE = l_def_cso_rec.creation_date,
1684 LAST_UPDATED_BY = l_def_cso_rec.last_updated_by,
1685 LAST_UPDATE_DATE = l_def_cso_rec.last_update_date,
1686 CONSTRAINED_YN = l_def_cso_rec.constrained_yn,
1687 LAST_UPDATE_LOGIN = l_def_cso_rec.last_update_login,
1688 ACCESS_LEVEL = l_def_cso_rec.access_level
1689 WHERE CRO_CODE = l_def_cso_rec.cro_code
1690 AND RLE_CODE = l_def_cso_rec.rle_code
1691 AND BUY_OR_SELL = l_def_cso_rec.buy_or_sell
1692 AND START_DATE = l_def_cso_rec.start_date;
1693
1694 x_cso_rec := l_def_cso_rec;
1695 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1696 EXCEPTION
1697 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1698 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1699 (
1700 l_api_name,
1701 G_PKG_NAME,
1702 'OKC_API.G_RET_STS_ERROR',
1703 x_msg_count,
1704 x_msg_data,
1705 '_PVT'
1706 );
1707 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1708 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1709 (
1710 l_api_name,
1711 G_PKG_NAME,
1712 'OKC_API.G_RET_STS_UNEXP_ERROR',
1713 x_msg_count,
1714 x_msg_data,
1715 '_PVT'
1716 );
1717 WHEN OTHERS THEN
1718 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1719 (
1720 l_api_name,
1721 G_PKG_NAME,
1722 'OTHERS',
1723 x_msg_count,
1724 x_msg_data,
1725 '_PVT'
1726 );
1727 END update_row;
1728 ------------------------------------------
1729 -- update_row for:OKC_CONTACT_SOURCES_V --
1730 ------------------------------------------
1731 PROCEDURE update_row(
1732 p_api_version IN NUMBER,
1733 p_init_msg_list IN VARCHAR2 ,
1734 x_return_status OUT NOCOPY VARCHAR2,
1735 x_msg_count OUT NOCOPY NUMBER,
1736 x_msg_data OUT NOCOPY VARCHAR2,
1737 p_csov_rec IN csov_rec_type,
1738 x_csov_rec OUT NOCOPY csov_rec_type) IS
1739
1740 l_api_version CONSTANT NUMBER := 1;
1741 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1742 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1743 l_csov_rec csov_rec_type := p_csov_rec;
1744 l_def_csov_rec csov_rec_type;
1745 l_cso_rec cso_rec_type;
1746 lx_cso_rec cso_rec_type;
1747 -------------------------------
1748 -- FUNCTION fill_who_columns --
1749 -------------------------------
1750 FUNCTION fill_who_columns (
1751 p_csov_rec IN csov_rec_type
1752 ) RETURN csov_rec_type IS
1753 l_csov_rec csov_rec_type := p_csov_rec;
1754 BEGIN
1755 l_csov_rec.LAST_UPDATE_DATE := SYSDATE;
1756 l_csov_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1757 l_csov_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1758 RETURN(l_csov_rec);
1759 END fill_who_columns;
1760 ----------------------------------
1761 -- FUNCTION populate_new_record --
1762 ----------------------------------
1763 FUNCTION populate_new_record (
1764 p_csov_rec IN csov_rec_type,
1765 x_csov_rec OUT NOCOPY csov_rec_type
1766 ) RETURN VARCHAR2 IS
1767 l_csov_rec csov_rec_type;
1768 l_row_notfound BOOLEAN := TRUE;
1769 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1770 BEGIN
1771 x_csov_rec := p_csov_rec;
1772 -- Get current database values
1773 l_csov_rec := get_rec(p_csov_rec, l_row_notfound);
1774 IF (l_row_notfound) THEN
1775 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1776 END IF;
1777 IF (x_csov_rec.rle_code = OKC_API.G_MISS_CHAR)
1778 THEN
1779 x_csov_rec.rle_code := l_csov_rec.rle_code;
1780 END IF;
1781 IF (x_csov_rec.cro_code = OKC_API.G_MISS_CHAR)
1782 THEN
1783 x_csov_rec.cro_code := l_csov_rec.cro_code;
1784 END IF;
1785 IF (x_csov_rec.buy_or_sell = OKC_API.G_MISS_CHAR)
1786 THEN
1787 x_csov_rec.buy_or_sell := l_csov_rec.buy_or_sell;
1788 END IF;
1789 IF (x_csov_rec.start_date = OKC_API.G_MISS_DATE)
1790 THEN
1791 x_csov_rec.start_date := l_csov_rec.start_date;
1792 END IF;
1793 IF (x_csov_rec.end_date = OKC_API.G_MISS_DATE)
1794 THEN
1795 x_csov_rec.end_date := l_csov_rec.end_date;
1796 END IF;
1797 IF (x_csov_rec.jtot_object_code = OKC_API.G_MISS_CHAR)
1798 THEN
1799 x_csov_rec.JTOT_OBJECT_CODE := l_csov_rec.JTOT_OBJECT_CODE;
1800 END IF;
1801 IF (x_csov_rec.object_version_number = OKC_API.G_MISS_NUM)
1802 THEN
1803 x_csov_rec.object_version_number := l_csov_rec.object_version_number;
1804 END IF;
1805 IF (x_csov_rec.constrained_yn = OKC_API.G_MISS_CHAR)
1806 THEN
1807 x_csov_rec.constrained_yn := l_csov_rec.constrained_yn;
1808 END IF;
1809 IF (x_csov_rec.created_by = OKC_API.G_MISS_NUM)
1810 THEN
1811 x_csov_rec.created_by := l_csov_rec.created_by;
1812 END IF;
1813 IF (x_csov_rec.creation_date = OKC_API.G_MISS_DATE)
1814 THEN
1815 x_csov_rec.creation_date := l_csov_rec.creation_date;
1816 END IF;
1817 IF (x_csov_rec.last_updated_by = OKC_API.G_MISS_NUM)
1818 THEN
1819 x_csov_rec.last_updated_by := l_csov_rec.last_updated_by;
1820 END IF;
1821 IF (x_csov_rec.last_update_date = OKC_API.G_MISS_DATE)
1822 THEN
1823 x_csov_rec.last_update_date := l_csov_rec.last_update_date;
1824 END IF;
1825 IF (x_csov_rec.last_update_login = OKC_API.G_MISS_NUM)
1826 THEN
1827 x_csov_rec.last_update_login := l_csov_rec.last_update_login;
1828 END IF;
1829 IF (x_csov_rec.access_level = OKC_API.G_MISS_CHAR)
1830 THEN
1831 x_csov_rec.access_level := l_csov_rec.access_level;
1832 END IF;
1833 RETURN(l_return_status);
1834 END populate_new_record;
1835 ----------------------------------------------
1836 -- Set_Attributes for:OKC_CONTACT_SOURCES_V --
1837 ----------------------------------------------
1838 FUNCTION Set_Attributes (
1839 p_csov_rec IN csov_rec_type,
1840 x_csov_rec OUT NOCOPY csov_rec_type
1841 ) RETURN VARCHAR2 IS
1842 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1843 BEGIN
1844 x_csov_rec := p_csov_rec;
1845 x_csov_rec.OBJECT_VERSION_NUMBER := NVL(x_csov_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1846 RETURN(l_return_status);
1847 END Set_Attributes;
1848 BEGIN
1849 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1850 G_PKG_NAME,
1851 p_init_msg_list,
1852 l_api_version,
1853 p_api_version,
1854 '_PVT',
1855 x_return_status);
1856 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1857 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1858 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1859 RAISE OKC_API.G_EXCEPTION_ERROR;
1860 END IF;
1861 --- Setting item attributes
1862 l_return_status := Set_Attributes(
1863 p_csov_rec, -- IN
1864 l_csov_rec); -- OUT
1865 --- If any errors happen abort API
1866 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1867 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1868 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1869 RAISE OKC_API.G_EXCEPTION_ERROR;
1870 END IF;
1871 l_return_status := populate_new_record(l_csov_rec, l_def_csov_rec);
1872 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1873 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1874 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1875 RAISE OKC_API.G_EXCEPTION_ERROR;
1876 END IF;
1877 l_def_csov_rec := fill_who_columns(l_def_csov_rec);
1878 --- Validate all non-missing attributes (Item Level Validation)
1879 l_return_status := Validate_Attributes(l_def_csov_rec);
1880 --- If any errors happen abort API
1881 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1882 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1883 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1884 RAISE OKC_API.G_EXCEPTION_ERROR;
1885 END IF;
1886 l_return_status := Validate_Record(l_def_csov_rec);
1887 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1888 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1889 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1890 RAISE OKC_API.G_EXCEPTION_ERROR;
1891 END IF;
1892
1893 --------------------------------------
1894 -- Move VIEW record to "Child" records
1895 --------------------------------------
1896 migrate(l_def_csov_rec, l_cso_rec);
1897 --------------------------------------------
1898 -- Call the UPDATE_ROW for each child record
1899 --------------------------------------------
1900 update_row(
1901 p_init_msg_list,
1902 x_return_status,
1903 x_msg_count,
1904 x_msg_data,
1905 l_cso_rec,
1906 lx_cso_rec
1907 );
1908 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1909 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1910 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1911 RAISE OKC_API.G_EXCEPTION_ERROR;
1912 END IF;
1913 migrate(lx_cso_rec, l_def_csov_rec);
1914 x_csov_rec := l_def_csov_rec;
1915 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1916 EXCEPTION
1917 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1918 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1919 (
1920 l_api_name,
1921 G_PKG_NAME,
1922 'OKC_API.G_RET_STS_ERROR',
1923 x_msg_count,
1924 x_msg_data,
1925 '_PVT'
1926 );
1927 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1928 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1929 (
1930 l_api_name,
1931 G_PKG_NAME,
1932 'OKC_API.G_RET_STS_UNEXP_ERROR',
1933 x_msg_count,
1934 x_msg_data,
1935 '_PVT'
1936 );
1937 WHEN OTHERS THEN
1938 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1939 (
1940 l_api_name,
1941 G_PKG_NAME,
1942 'OTHERS',
1943 x_msg_count,
1944 x_msg_data,
1945 '_PVT'
1946 );
1947 END update_row;
1948 ----------------------------------------
1949 -- PL/SQL TBL update_row for:CSOV_TBL --
1950 ----------------------------------------
1951 PROCEDURE update_row(
1952 p_api_version IN NUMBER,
1953 p_init_msg_list IN VARCHAR2 ,
1954 x_return_status OUT NOCOPY VARCHAR2,
1955 x_msg_count OUT NOCOPY NUMBER,
1956 x_msg_data OUT NOCOPY VARCHAR2,
1957 p_csov_tbl IN csov_tbl_type,
1958 x_csov_tbl OUT NOCOPY csov_tbl_type) IS
1959
1960 l_api_version CONSTANT NUMBER := 1;
1961 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1962 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1963 i NUMBER := 0;
1964 BEGIN
1965 OKC_API.init_msg_list(p_init_msg_list);
1966 -- Make sure PL/SQL table has records in it before passing
1967 IF (p_csov_tbl.COUNT > 0) THEN
1968 i := p_csov_tbl.FIRST;
1969 LOOP
1970 update_row (
1971 p_api_version => p_api_version,
1972 p_init_msg_list => OKC_API.G_FALSE,
1973 x_return_status => x_return_status,
1974 x_msg_count => x_msg_count,
1975 x_msg_data => x_msg_data,
1976 p_csov_rec => p_csov_tbl(i),
1977 x_csov_rec => x_csov_tbl(i));
1978 EXIT WHEN (i = p_csov_tbl.LAST);
1979 i := p_csov_tbl.NEXT(i);
1980 END LOOP;
1981 END IF;
1982 EXCEPTION
1983 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1984 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1985 (
1986 l_api_name,
1987 G_PKG_NAME,
1988 'OKC_API.G_RET_STS_ERROR',
1989 x_msg_count,
1990 x_msg_data,
1991 '_PVT'
1992 );
1993 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1994 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1995 (
1996 l_api_name,
1997 G_PKG_NAME,
1998 'OKC_API.G_RET_STS_UNEXP_ERROR',
1999 x_msg_count,
2000 x_msg_data,
2001 '_PVT'
2002 );
2003 WHEN OTHERS THEN
2004 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2005 (
2006 l_api_name,
2007 G_PKG_NAME,
2008 'OTHERS',
2009 x_msg_count,
2010 x_msg_data,
2011 '_PVT'
2012 );
2013 END update_row;
2014
2015 ---------------------------------------------------------------------------
2016 -- PROCEDURE delete_row
2017 ---------------------------------------------------------------------------
2018 ----------------------------------------
2019 -- delete_row for:OKC_CONTACT_SOURCES --
2020 ----------------------------------------
2021 PROCEDURE delete_row(
2022 p_init_msg_list IN VARCHAR2 ,
2023 x_return_status OUT NOCOPY VARCHAR2,
2024 x_msg_count OUT NOCOPY NUMBER,
2025 x_msg_data OUT NOCOPY VARCHAR2,
2026 p_cso_rec IN cso_rec_type) IS
2027
2028 l_api_version CONSTANT NUMBER := 1;
2029 l_api_name CONSTANT VARCHAR2(30) := 'SOURCES_delete_row';
2030 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2031 l_cso_rec cso_rec_type:= p_cso_rec;
2032 l_row_notfound BOOLEAN := TRUE;
2033 BEGIN
2034 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2035 p_init_msg_list,
2036 '_PVT',
2037 x_return_status);
2038 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2039 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2040 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2041 RAISE OKC_API.G_EXCEPTION_ERROR;
2042 END IF;
2043 DELETE FROM OKC_CONTACT_SOURCES
2044 WHERE CRO_CODE = l_cso_rec.cro_code AND
2045 RLE_CODE = l_cso_rec.rle_code AND
2046 BUY_OR_SELL = l_cso_rec.buy_or_sell AND
2047 START_DATE = l_cso_rec.start_date;
2048
2049 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2050 EXCEPTION
2051 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2052 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2053 (
2054 l_api_name,
2055 G_PKG_NAME,
2056 'OKC_API.G_RET_STS_ERROR',
2057 x_msg_count,
2058 x_msg_data,
2059 '_PVT'
2060 );
2061 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2062 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2063 (
2064 l_api_name,
2065 G_PKG_NAME,
2066 'OKC_API.G_RET_STS_UNEXP_ERROR',
2067 x_msg_count,
2068 x_msg_data,
2069 '_PVT'
2070 );
2071 WHEN OTHERS THEN
2072 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2073 (
2074 l_api_name,
2075 G_PKG_NAME,
2076 'OTHERS',
2077 x_msg_count,
2078 x_msg_data,
2079 '_PVT'
2080 );
2081 END delete_row;
2082 ------------------------------------------
2083 -- delete_row for:OKC_CONTACT_SOURCES_V --
2084 ------------------------------------------
2085 PROCEDURE delete_row(
2086 p_api_version IN NUMBER,
2087 p_init_msg_list IN VARCHAR2 ,
2088 x_return_status OUT NOCOPY VARCHAR2,
2089 x_msg_count OUT NOCOPY NUMBER,
2090 x_msg_data OUT NOCOPY VARCHAR2,
2091 p_csov_rec IN csov_rec_type) IS
2092
2093 l_api_version CONSTANT NUMBER := 1;
2094 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2095 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2096 l_csov_rec csov_rec_type := p_csov_rec;
2097 l_cso_rec cso_rec_type;
2098 BEGIN
2099 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2100 G_PKG_NAME,
2101 p_init_msg_list,
2102 l_api_version,
2103 p_api_version,
2104 '_PVT',
2105 x_return_status);
2106 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2107 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2108 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2109 RAISE OKC_API.G_EXCEPTION_ERROR;
2110 END IF;
2111 --------------------------------------
2112 -- Move VIEW record to "Child" records
2113 --------------------------------------
2114 migrate(l_csov_rec, l_cso_rec);
2115 --------------------------------------------
2116 -- Call the DELETE_ROW for each child record
2117 --------------------------------------------
2118 delete_row(
2119 p_init_msg_list,
2120 x_return_status,
2121 x_msg_count,
2122 x_msg_data,
2123 l_cso_rec
2124 );
2125 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2126 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2127 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2128 RAISE OKC_API.G_EXCEPTION_ERROR;
2129 END IF;
2130 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2131 EXCEPTION
2132 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2133 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2134 (
2135 l_api_name,
2136 G_PKG_NAME,
2137 'OKC_API.G_RET_STS_ERROR',
2138 x_msg_count,
2139 x_msg_data,
2140 '_PVT'
2141 );
2142 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2143 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2144 (
2145 l_api_name,
2146 G_PKG_NAME,
2147 'OKC_API.G_RET_STS_UNEXP_ERROR',
2148 x_msg_count,
2149 x_msg_data,
2150 '_PVT'
2151 );
2152 WHEN OTHERS THEN
2153 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2154 (
2155 l_api_name,
2156 G_PKG_NAME,
2157 'OTHERS',
2158 x_msg_count,
2159 x_msg_data,
2160 '_PVT'
2161 );
2162 END delete_row;
2163 ----------------------------------------
2164 -- PL/SQL TBL delete_row for:CSOV_TBL --
2165 ----------------------------------------
2166 PROCEDURE delete_row(
2167 p_api_version IN NUMBER,
2168 p_init_msg_list IN VARCHAR2 ,
2169 x_return_status OUT NOCOPY VARCHAR2,
2170 x_msg_count OUT NOCOPY NUMBER,
2171 x_msg_data OUT NOCOPY VARCHAR2,
2172 p_csov_tbl IN csov_tbl_type) IS
2173
2174 l_api_version CONSTANT NUMBER := 1;
2175 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2176 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2177 i NUMBER := 0;
2178 BEGIN
2179 OKC_API.init_msg_list(p_init_msg_list);
2180 -- Make sure PL/SQL table has records in it before passing
2181 IF (p_csov_tbl.COUNT > 0) THEN
2182 i := p_csov_tbl.FIRST;
2183 LOOP
2184 delete_row (
2185 p_api_version => p_api_version,
2186 p_init_msg_list => OKC_API.G_FALSE,
2187 x_return_status => x_return_status,
2188 x_msg_count => x_msg_count,
2189 x_msg_data => x_msg_data,
2190 p_csov_rec => p_csov_tbl(i));
2191 EXIT WHEN (i = p_csov_tbl.LAST);
2192 i := p_csov_tbl.NEXT(i);
2193 END LOOP;
2194 END IF;
2195 EXCEPTION
2196 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2197 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2198 (
2199 l_api_name,
2200 G_PKG_NAME,
2201 'OKC_API.G_RET_STS_ERROR',
2202 x_msg_count,
2203 x_msg_data,
2204 '_PVT'
2205 );
2206 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2207 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2208 (
2209 l_api_name,
2210 G_PKG_NAME,
2211 'OKC_API.G_RET_STS_UNEXP_ERROR',
2212 x_msg_count,
2213 x_msg_data,
2214 '_PVT'
2215 );
2216 WHEN OTHERS THEN
2217 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2218 (
2219 l_api_name,
2220 G_PKG_NAME,
2221 'OTHERS',
2222 x_msg_count,
2223 x_msg_data,
2224 '_PVT'
2225 );
2226 END delete_row;
2227 END OKC_CSO_PVT;