[Home] [Help]
PACKAGE BODY: APPS.OKC_CTC_PVT
Source
1 PACKAGE BODY OKC_CTC_PVT AS
2 /* $Header: OKCSCTCB.pls 120.4 2011/03/10 18:11:34 harchand ship $ */
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
18 RETURN(okc_p_util.raw_to_number(sys_guid()));
19
20 END get_seq_id;
21
22 ---------------------------------------------------------------------------
23 -- PROCEDURE qc
24 ---------------------------------------------------------------------------
25 PROCEDURE qc IS
26 BEGIN
27
28 null;
29
30 END qc;
31
32 ---------------------------------------------------------------------------
33 -- PROCEDURE change_version
34 ---------------------------------------------------------------------------
35 PROCEDURE change_version IS
36 BEGIN
37
38 null;
39
40 END change_version;
41
42 ---------------------------------------------------------------------------
43 -- PROCEDURE api_copy
44 ---------------------------------------------------------------------------
45 PROCEDURE api_copy IS
46 BEGIN
47
48 null;
49
50 END api_copy;
51
52 -- PROCEDURE update_contact_stecode
53 /*bugfix for 6882512*/
54 PROCEDURE update_contact_stecode(
55 p_chr_id IN NUMBER,
56 x_return_status OUT NOCOPY VARCHAR2) IS
57
58 CURSOR c_get_contract_status(p_chr_id IN NUMBER) IS
59 SELECT ste_code
60 FROM okc_k_headers_all_b,okc_statuses_b
61 WHERE id= p_chr_id AND okc_statuses_b.code=okc_k_headers_all_b.sts_code;
62
63 l_ste_code varchar2(30);
64
65 BEGIN
66 IF (l_debug = 'Y') THEN
67 okc_debug.Set_Indentation('OKC_CTC_PVT');
68 okc_debug.log('100: Entered update_contact_stecode', 2);
69 END IF;
70
71 x_return_status := OKC_API.G_RET_STS_SUCCESS;
72
73 OPEN c_get_contract_status(p_chr_id);
74 FETCH c_get_contract_status INTO l_ste_code;
75 CLOSE c_get_contract_status;
76
77 IF l_ste_code is null then
78 RAISE OKC_API.G_EXCEPTION_ERROR;
79 END IF;
80
81 UPDATE OKC_CONTACTS SET dnz_ste_code=l_ste_code WHERE DNZ_CHR_ID=p_chr_id;
82
83 IF (l_debug = 'Y') THEN
84 okc_debug.Set_Indentation('OKC_CTC_PVT');
85 okc_debug.log('110: Exiting update_contact_stecode', 2);
86 END IF;
87
88 EXCEPTION
89 WHEN OKC_API.G_EXCEPTION_ERROR THEN
90
91 IF (l_debug = 'Y') THEN
92 okc_debug.log('120: Exiting update_contact_stecode:OKC_API.G_EXCEPTION_ERROR Exception', 2);
93 okc_debug.Reset_Indentation;
94 END IF;
95
96 x_return_status := 'E';
97
98 WHEN OTHERS THEN
99
100 IF (l_debug = 'Y') THEN
101 okc_debug.log('130: Exiting update_contact_stecode:OTHERS Exception', 2);
102 okc_debug.Reset_Indentation;
103 END IF;
104
105 END update_contact_stecode;
106 /*bugfix for 6882512*/
107
108
109 ---------------------------------------------------------------------------
110 -- FUNCTION get_rec for: OKC_CONTACTS
111 ---------------------------------------------------------------------------
112 FUNCTION get_rec (
113 p_ctc_rec IN ctc_rec_type,
114 x_no_data_found OUT NOCOPY BOOLEAN
115 ) RETURN ctc_rec_type IS
116 CURSOR ctc_pk_csr (p_id IN NUMBER) IS
117 SELECT
118 ID,
119 CPL_ID,
120 CRO_CODE,
121 DNZ_CHR_ID,
122 OBJECT1_ID1,
123 OBJECT1_ID2,
124 JTOT_OBJECT1_CODE,
125 OBJECT_VERSION_NUMBER,
126 CREATED_BY,
127 CREATION_DATE,
128 LAST_UPDATED_BY,
129 LAST_UPDATE_DATE,
130 CONTACT_SEQUENCE,
131 LAST_UPDATE_LOGIN,
132 PRIMARY_YN,
133 RESOURCE_CLASS,
134 SALES_GROUP_ID,
135 ATTRIBUTE_CATEGORY,
136 ATTRIBUTE1,
137 ATTRIBUTE2,
138 ATTRIBUTE3,
139 ATTRIBUTE4,
140 ATTRIBUTE5,
141 ATTRIBUTE6,
142 ATTRIBUTE7,
143 ATTRIBUTE8,
144 ATTRIBUTE9,
145 ATTRIBUTE10,
146 ATTRIBUTE11,
147 ATTRIBUTE12,
148 ATTRIBUTE13,
149 ATTRIBUTE14,
150 ATTRIBUTE15,
151 START_DATE,
152 END_DATE
153 FROM Okc_Contacts
154 WHERE okc_contacts.id = p_id;
155 l_ctc_pk ctc_pk_csr%ROWTYPE;
156 l_ctc_rec ctc_rec_type;
157 BEGIN
158
159 IF (l_debug = 'Y') THEN
160 okc_debug.Set_Indentation('OKC_CTC_PVT');
161 okc_debug.log('500: Entered get_rec', 2);
162 END IF;
163
164 x_no_data_found := TRUE;
165 -- Get current database values
166 OPEN ctc_pk_csr (p_ctc_rec.id);
167 FETCH ctc_pk_csr INTO
168 l_ctc_rec.ID,
169 l_ctc_rec.CPL_ID,
170 l_ctc_rec.CRO_CODE,
171 l_ctc_rec.DNZ_CHR_ID,
172 l_ctc_rec.OBJECT1_ID1,
173 l_ctc_rec.OBJECT1_ID2,
174 l_ctc_rec.JTOT_OBJECT1_CODE,
175 l_ctc_rec.OBJECT_VERSION_NUMBER,
176 l_ctc_rec.CREATED_BY,
177 l_ctc_rec.CREATION_DATE,
178 l_ctc_rec.LAST_UPDATED_BY,
179 l_ctc_rec.LAST_UPDATE_DATE,
180 l_ctc_rec.CONTACT_SEQUENCE,
181 l_ctc_rec.LAST_UPDATE_LOGIN,
182 l_ctc_rec.PRIMARY_YN,
183 l_ctc_rec.RESOURCE_CLASS,
184 l_ctc_rec.SALES_GROUP_ID,
185 l_ctc_rec.ATTRIBUTE_CATEGORY,
186 l_ctc_rec.ATTRIBUTE1,
187 l_ctc_rec.ATTRIBUTE2,
188 l_ctc_rec.ATTRIBUTE3,
189 l_ctc_rec.ATTRIBUTE4,
190 l_ctc_rec.ATTRIBUTE5,
191 l_ctc_rec.ATTRIBUTE6,
192 l_ctc_rec.ATTRIBUTE7,
193 l_ctc_rec.ATTRIBUTE8,
194 l_ctc_rec.ATTRIBUTE9,
195 l_ctc_rec.ATTRIBUTE10,
196 l_ctc_rec.ATTRIBUTE11,
197 l_ctc_rec.ATTRIBUTE12,
198 l_ctc_rec.ATTRIBUTE13,
199 l_ctc_rec.ATTRIBUTE14,
200 l_ctc_rec.ATTRIBUTE15,
201 l_ctc_rec.START_DATE,
202 l_ctc_rec.END_DATE;
203
204 x_no_data_found := ctc_pk_csr%NOTFOUND;
205 CLOSE ctc_pk_csr;
206
207 IF (l_debug = 'Y') THEN
208 okc_debug.log('550: Leaving Get_Rec ', 2);
209 okc_debug.Reset_Indentation;
210 END IF;
211
212 RETURN(l_ctc_rec);
213
214 END get_rec;
215
216 FUNCTION get_rec (
217 p_ctc_rec IN ctc_rec_type
218 ) RETURN ctc_rec_type IS
219 l_row_notfound BOOLEAN := TRUE;
220 BEGIN
221
222 RETURN(get_rec(p_ctc_rec, l_row_notfound));
223
224 END get_rec;
225 ---------------------------------------------------------------------------
226 -- FUNCTION get_rec for: OKC_CONTACTS_V
227 ---------------------------------------------------------------------------
228 FUNCTION get_rec (
229 p_ctcv_rec IN ctcv_rec_type,
230 x_no_data_found OUT NOCOPY BOOLEAN
231 ) RETURN ctcv_rec_type IS
232 CURSOR okc_ctcv_pk_csr (p_id IN NUMBER) IS
233 SELECT
234 ID,
235 OBJECT_VERSION_NUMBER,
236 CPL_ID,
237 CRO_CODE,
238 DNZ_CHR_ID,
239 CONTACT_SEQUENCE,
240 OBJECT1_ID1,
241 OBJECT1_ID2,
242 JTOT_OBJECT1_CODE,
243 PRIMARY_YN,
244 RESOURCE_CLASS,
245 SALES_GROUP_ID,
246 ATTRIBUTE_CATEGORY,
247 ATTRIBUTE1,
248 ATTRIBUTE2,
249 ATTRIBUTE3,
250 ATTRIBUTE4,
251 ATTRIBUTE5,
252 ATTRIBUTE6,
253 ATTRIBUTE7,
254 ATTRIBUTE8,
255 ATTRIBUTE9,
256 ATTRIBUTE10,
257 ATTRIBUTE11,
258 ATTRIBUTE12,
259 ATTRIBUTE13,
260 ATTRIBUTE14,
261 ATTRIBUTE15,
262 CREATED_BY,
263 CREATION_DATE,
264 LAST_UPDATED_BY,
265 LAST_UPDATE_DATE,
266 LAST_UPDATE_LOGIN,
267 START_DATE,
268 END_DATE
269 FROM Okc_Contacts
270 WHERE okc_contacts.id = p_id;
271 l_okc_ctcv_pk okc_ctcv_pk_csr%ROWTYPE;
272 l_ctcv_rec ctcv_rec_type;
273 BEGIN
274
275 IF (l_debug = 'Y') THEN
276 okc_debug.Set_Indentation('OKC_CTC_PVT');
277 okc_debug.log('700: Entered get_rec', 2);
278 END IF;
279
280 x_no_data_found := TRUE;
281 -- Get current database values
282 OPEN okc_ctcv_pk_csr (p_ctcv_rec.id);
283 FETCH okc_ctcv_pk_csr INTO
284 l_ctcv_rec.ID,
285 l_ctcv_rec.OBJECT_VERSION_NUMBER,
286 l_ctcv_rec.CPL_ID,
287 l_ctcv_rec.CRO_CODE,
288 l_ctcv_rec.DNZ_CHR_ID,
289 l_ctcv_rec.CONTACT_SEQUENCE,
290 l_ctcv_rec.OBJECT1_ID1,
291 l_ctcv_rec.OBJECT1_ID2,
292 l_ctcv_rec.JTOT_OBJECT1_CODE,
293 l_ctcv_rec.PRIMARY_YN,
294 l_ctcv_rec.RESOURCE_CLASS,
295 l_ctcv_rec.SALES_GROUP_ID,
296 l_ctcv_rec.ATTRIBUTE_CATEGORY,
297 l_ctcv_rec.ATTRIBUTE1,
298 l_ctcv_rec.ATTRIBUTE2,
299 l_ctcv_rec.ATTRIBUTE3,
300 l_ctcv_rec.ATTRIBUTE4,
301 l_ctcv_rec.ATTRIBUTE5,
302 l_ctcv_rec.ATTRIBUTE6,
303 l_ctcv_rec.ATTRIBUTE7,
304 l_ctcv_rec.ATTRIBUTE8,
305 l_ctcv_rec.ATTRIBUTE9,
306 l_ctcv_rec.ATTRIBUTE10,
307 l_ctcv_rec.ATTRIBUTE11,
308 l_ctcv_rec.ATTRIBUTE12,
309 l_ctcv_rec.ATTRIBUTE13,
310 l_ctcv_rec.ATTRIBUTE14,
311 l_ctcv_rec.ATTRIBUTE15,
312 l_ctcv_rec.CREATED_BY,
313 l_ctcv_rec.CREATION_DATE,
314 l_ctcv_rec.LAST_UPDATED_BY,
315 l_ctcv_rec.LAST_UPDATE_DATE,
316 l_ctcv_rec.LAST_UPDATE_LOGIN,
317 l_ctcv_rec.START_DATE,
318 l_ctcv_rec.END_DATE;
319 x_no_data_found := okc_ctcv_pk_csr%NOTFOUND;
320 CLOSE okc_ctcv_pk_csr;
321
322 IF (l_debug = 'Y') THEN
323 okc_debug.log('750: Leaving Get_Rec ', 2);
324 okc_debug.Reset_Indentation;
325 END IF;
326
327 RETURN(l_ctcv_rec);
328
329 END get_rec;
330
331 FUNCTION get_rec (
332 p_ctcv_rec IN ctcv_rec_type
333 ) RETURN ctcv_rec_type IS
334 l_row_notfound BOOLEAN := TRUE;
335 BEGIN
336
337 RETURN(get_rec(p_ctcv_rec, l_row_notfound));
338
339 END get_rec;
340
341 ----------------------------------------------------
342 -- FUNCTION null_out_defaults for: OKC_CONTACTS_V --
343 ----------------------------------------------------
344 FUNCTION null_out_defaults (
345 p_ctcv_rec IN ctcv_rec_type
346 ) RETURN ctcv_rec_type IS
347 l_ctcv_rec ctcv_rec_type := p_ctcv_rec;
348 BEGIN
349
350 IF (l_debug = 'Y') THEN
351 okc_debug.Set_Indentation('OKC_CTC_PVT');
352 okc_debug.log('900: Entered null_out_defaults', 2);
353 END IF;
354
355 IF (l_ctcv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
356 l_ctcv_rec.object_version_number := NULL;
357 END IF;
358 IF (l_ctcv_rec.cpl_id = OKC_API.G_MISS_NUM) THEN
359 l_ctcv_rec.cpl_id := NULL;
360 END IF;
361 IF (l_ctcv_rec.cro_code = OKC_API.G_MISS_CHAR) THEN
362 l_ctcv_rec.cro_code := NULL;
363 END IF;
364 IF (l_ctcv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
365 l_ctcv_rec.dnz_chr_id := NULL;
366 END IF;
367 IF (l_ctcv_rec.contact_sequence = OKC_API.G_MISS_NUM) THEN
368 l_ctcv_rec.contact_sequence := NULL;
369 END IF;
370 IF (l_ctcv_rec.object1_id1 = OKC_API.G_MISS_CHAR) THEN
371 l_ctcv_rec.object1_id1 := NULL;
372 END IF;
373 IF (l_ctcv_rec.object1_id2 = OKC_API.G_MISS_CHAR) THEN
374 l_ctcv_rec.object1_id2 := NULL;
375 END IF;
376 IF (l_ctcv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR) THEN
377 l_ctcv_rec.JTOT_OBJECT1_CODE := NULL;
378 END IF;
379 IF (l_ctcv_rec.primary_yn = OKC_API.G_MISS_CHAR) THEN
380 l_ctcv_rec.PRIMARY_YN :=NULL;
381 END IF;
382 IF (l_ctcv_rec.resource_class = OKC_API.G_MISS_CHAR) THEN
383 l_ctcv_rec.RESOURCE_CLASS :=NULL;
384 END IF;
385 IF (l_ctcv_rec.SALES_GROUP_ID = OKC_API.G_MISS_NUM) THEN
386 l_ctcv_rec.SALES_GROUP_ID :=NULL;
387 END IF;
388 IF (l_ctcv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
389 l_ctcv_rec.attribute_category := NULL;
390 END IF;
391 IF (l_ctcv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
392 l_ctcv_rec.attribute1 := NULL;
393 END IF;
394 IF (l_ctcv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
395 l_ctcv_rec.attribute2 := NULL;
396 END IF;
397 IF (l_ctcv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
398 l_ctcv_rec.attribute3 := NULL;
399 END IF;
400 IF (l_ctcv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
401 l_ctcv_rec.attribute4 := NULL;
402 END IF;
403 IF (l_ctcv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
404 l_ctcv_rec.attribute5 := NULL;
405 END IF;
406 IF (l_ctcv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
407 l_ctcv_rec.attribute6 := NULL;
408 END IF;
409 IF (l_ctcv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
410 l_ctcv_rec.attribute7 := NULL;
411 END IF;
412 IF (l_ctcv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
413 l_ctcv_rec.attribute8 := NULL;
414 END IF;
415 IF (l_ctcv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
416 l_ctcv_rec.attribute9 := NULL;
417 END IF;
418 IF (l_ctcv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
419 l_ctcv_rec.attribute10 := NULL;
420 END IF;
421 IF (l_ctcv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
422 l_ctcv_rec.attribute11 := NULL;
423 END IF;
424 IF (l_ctcv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
425 l_ctcv_rec.attribute12 := NULL;
426 END IF;
427 IF (l_ctcv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
428 l_ctcv_rec.attribute13 := NULL;
429 END IF;
430 IF (l_ctcv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
431 l_ctcv_rec.attribute14 := NULL;
432 END IF;
433 IF (l_ctcv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
434 l_ctcv_rec.attribute15 := NULL;
435 END IF;
436 IF (l_ctcv_rec.created_by = OKC_API.G_MISS_NUM) THEN
437 l_ctcv_rec.created_by := NULL;
438 END IF;
439 IF (l_ctcv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
440 l_ctcv_rec.creation_date := NULL;
441 END IF;
442 IF (l_ctcv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
443 l_ctcv_rec.last_updated_by := NULL;
444 END IF;
445 IF (l_ctcv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
446 l_ctcv_rec.last_update_date := NULL;
447 END IF;
448 IF (l_ctcv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
449 l_ctcv_rec.last_update_login := NULL;
450 END IF;
451 IF (l_ctcv_rec.start_date = OKC_API.G_MISS_DATE) THEN
452 l_ctcv_rec.start_date := NULL;
453 END IF;
454 IF (l_ctcv_rec.end_date = OKC_API.G_MISS_DATE) THEN
455 l_ctcv_rec.end_date := NULL;
456 END IF;
457
458 IF (l_debug = 'Y') THEN
459 okc_debug.log('950: Leaving null_out_defaults ', 2);
460 okc_debug.Reset_Indentation;
461 END IF;
462
463 RETURN(l_ctcv_rec);
464
465 END null_out_defaults;
466 ---------------------------------------------------------------------------
467 -- PROCEDURE Validate_Attributes
468 ---------------------------------------------------------------------------
469 /*+++++++++++++Start of hand code +++++++++++++++++*/
470
471 -- Start of comments
472 --
473 -- Procedure Name : validate_cpl_id
474 -- Description :
475 -- Business Rules :
476 -- Parameters :
477 -- Version : 1.0
478 -- End of comments
479
480 procedure validate_cpl_id(x_return_status OUT NOCOPY VARCHAR2,
481 p_ctcv_rec IN CTCV_REC_TYPE) is
482 l_dummy_var varchar2(1) := '?';
483 cursor l_cpl_csr is
484 select 'x'
485 from OKC_K_PARTY_ROLES_B
486 where id = p_ctcv_rec.cpl_id;
487 begin
488
489 IF (l_debug = 'Y') THEN
490 okc_debug.Set_Indentation('OKC_CTC_PVT');
491 okc_debug.log('1000: Entered validate_cpl_id', 2);
492 END IF;
493
494 x_return_status := OKC_API.G_RET_STS_SUCCESS;
495 if (p_ctcv_rec.cpl_id = OKC_API.G_MISS_NUM) then
496 return;
497 end if;
498 if (p_ctcv_rec.cpl_id is NULL) then
499 OKC_API.set_message(p_app_name => g_app_name,
500 p_msg_name => G_REQUIRED_VALUE,
501 p_token1 => G_COL_NAME_TOKEN,
502 p_token1_value => 'CPL_ID');
503 raise G_EXCEPTION_HALT_VALIDATION;
504 end if;
505 open l_cpl_csr;
506 fetch l_cpl_csr into l_dummy_var;
507 close l_cpl_csr;
508 if (l_dummy_var = '?') then
509 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CPL_ID');
510 raise G_EXCEPTION_HALT_VALIDATION;
511 end if;
512
513 IF (l_debug = 'Y') THEN
514 okc_debug.log('1100: Leaving validate_cpl_id', 2);
515 okc_debug.Reset_Indentation;
516 END IF;
517
518 exception
519 when G_EXCEPTION_HALT_VALIDATION then
520
521 IF (l_debug = 'Y') THEN
522 okc_debug.log('1200: Exiting validate_cpl_id:G_EXCEPTION_HALT_VALIDATION Exception', 2);
523 okc_debug.Reset_Indentation;
524 END IF;
525
526 x_return_status := OKC_API.G_RET_STS_ERROR;
527 when OTHERS then
528
529 IF (l_debug = 'Y') THEN
530 okc_debug.log('1300: Exiting validate_cpl_id:OTHERS Exception', 2);
531 okc_debug.Reset_Indentation;
532 END IF;
533
534 if l_cpl_csr%ISOPEN then
535 close l_cpl_csr;
536 end if;
537 OKC_API.set_message(p_app_name => g_app_name,
538 p_msg_name => g_unexpected_error,
539 p_token1 => g_sqlcode_token,
540 p_token1_value => sqlcode,
541 p_token2 => g_sqlerrm_token,
542 p_token2_value => sqlerrm);
543 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
544
545 end validate_cpl_id;
546
547 -- Start of comments
548 --
549 -- Procedure Name : validate_cro_code
550 -- Description :
551 -- Business Rules :
552 -- Parameters :
553 -- Version : 1.0
554 -- End of comments
555
556 procedure validate_cro_code(x_return_status OUT NOCOPY VARCHAR2,
557 p_ctcv_rec IN CTCV_REC_TYPE) is
558 l_dummy_var varchar2(1) := '?';
559 --
560 begin
561
562 IF (l_debug = 'Y') THEN
563 okc_debug.Set_Indentation('OKC_CTC_PVT');
564 okc_debug.log('1400: Entered validate_cro_code', 2);
565 END IF;
566
567 x_return_status := OKC_API.G_RET_STS_SUCCESS;
568 if (p_ctcv_rec.cro_code = OKC_API.G_MISS_CHAR) then
569 return;
570 end if;
571 if (p_ctcv_rec.cro_code is NULL) then
572 OKC_API.set_message(p_app_name => g_app_name,
573 p_msg_name => G_REQUIRED_VALUE,
574 p_token1 => G_COL_NAME_TOKEN,
575 p_token1_value => 'CRO_CODE');
576 raise G_EXCEPTION_HALT_VALIDATION;
577 end if;
578
579 --
580 x_return_status := OKC_UTIL.check_lookup_code('OKC_CONTACT_ROLE',p_ctcv_rec.cro_code);
581 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
582 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CRO_CODE');
583 x_return_status := OKC_API.G_RET_STS_ERROR;
584 end if;
585 IF (l_debug = 'Y') THEN
586 okc_debug.log('1500: Leaving validate_cro_code', 2);
587 okc_debug.Reset_Indentation;
588 END IF;
589
590 exception
591 when G_EXCEPTION_HALT_VALIDATION then
592
593 IF (l_debug = 'Y') THEN
594 okc_debug.log('1600: Exiting validate_cro_code:G_EXCEPTION_HALT_VALIDATION Exception', 2);
595 okc_debug.Reset_Indentation;
596 END IF;
597
598 x_return_status := OKC_API.G_RET_STS_ERROR;
599 when OTHERS then
600
601 IF (l_debug = 'Y') THEN
602 okc_debug.log('1700: Exiting validate_cro_code:OTHERS Exception', 2);
603 okc_debug.Reset_Indentation;
604 END IF;
605
606 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
607 p_msg_name => g_unexpected_error,
608 p_token1 => g_sqlcode_token,
609 p_token1_value => sqlcode,
610 p_token2 => g_sqlerrm_token,
611 p_token2_value => sqlerrm);
612 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
613
614 end validate_cro_code;
615
616 -- Start of comments
617 --
618 -- Procedure Name : validate_JTOT_OBJECT1_CODE
619 -- Description :
620 -- Business Rules :
621 -- Parameters :
622 -- Version : 1.0
623 -- End of comments
624
625 procedure validate_JTOT_OBJECT1_CODE(x_return_status OUT NOCOPY VARCHAR2,
626 p_ctcv_rec IN ctcv_rec_TYPE) is
627 l_dummy_var varchar2(1) := '?';
628 --
629 cursor l_object1_csr is
630 select '!'
631 from
632 okc_k_party_roles_b PR
633 ,okc_contact_sources CS
634 ,okc_k_headers_all_b KH -- Modified by Jvorugan for Bug:4645341 okc_k_headers_b KH
635 where
636 PR.ID = p_ctcv_rec.CPL_ID
637 and CS.CRO_CODE = p_ctcv_rec.CRO_CODE
638 and CS.RLE_CODE = PR.RLE_CODE
639 and CS.jtot_object_code = p_ctcv_rec.jtot_object1_code
640 and sysdate >= CS.start_date
641 and (CS.end_date is NULL or CS.end_date>=sysdate)
642 and KH.ID = p_ctcv_rec.DNZ_CHR_ID
643 and CS.BUY_OR_SELL = KH.BUY_OR_SELL
644 ;
645 begin
646
647 IF (l_debug = 'Y') THEN
648 okc_debug.Set_Indentation('OKC_CTC_PVT');
649 okc_debug.log('1800: Entered validate_JTOT_OBJECT1_CODE', 2);
650 END IF;
651
652 x_return_status := OKC_API.G_RET_STS_SUCCESS;
653 if (p_ctcv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR or p_ctcv_rec.jtot_object1_code is NULL) then
654 return;
655 end if;
656 --
657 open l_object1_csr;
658 fetch l_object1_csr into l_dummy_var;
659 close l_object1_csr;
660 if (l_dummy_var = '?') then
661 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'JTOT_OBJECT1_CODE');
662 x_return_status := OKC_API.G_RET_STS_ERROR;
663 end if;
664
665 IF (l_debug = 'Y') THEN
666 okc_debug.log('1900: Leaving validate_JTOT_OBJECT1_CODE', 2);
667 okc_debug.Reset_Indentation;
668 END IF;
669
670 exception
671 when OTHERS then
672
673 IF (l_debug = 'Y') THEN
674 okc_debug.log('2000: Exiting validate_JTOT_OBJECT1_CODE:OTHERS Exception', 2);
675 okc_debug.Reset_Indentation;
676 END IF;
677
678 if l_object1_csr%ISOPEN then
679 close l_object1_csr;
680 end if;
681 OKC_API.set_message(p_app_name => g_app_name,
682 p_msg_name => g_unexpected_error,
683 p_token1 => g_sqlcode_token,
684 p_token1_value => sqlcode,
685 p_token2 => g_sqlerrm_token,
686 p_token2_value => sqlerrm);
687 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
688
689 end validate_JTOT_OBJECT1_CODE;
690
691 -- Start of comments
692 --
693 -- Procedure Name : validate_object1_id1
694 -- Description : to be called from validate record
695 -- Business Rules :
696 -- Parameters :
697 -- Version : 1.0
698 -- End of comments
699
700 procedure validate_object1_id1(x_return_status OUT NOCOPY VARCHAR2,
701 p_ctcv_rec IN ctcv_rec_TYPE) is
702 l_dummy_var varchar2(1) := '?';
703 L_FROM_TABLE VARCHAR2(200);
704 L_WHERE_CLAUSE VARCHAR2(2000);
705 cursor l_object1_csr is
706 select
707 from_table
708 ,trim(where_clause) where_clause
709 from
710 jtf_objects_vl OB
711 where
712 OB.OBJECT_CODE = p_ctcv_rec.JTOT_OBJECT1_CODE
713 ;
714 l_party_id1 varchar2(40);
715 l_party_id2 varchar2(200);
716 l_buy_sell varchar2(3);
717 l_rle_code varchar2(30);
718 l_jtot_object1_code varchar2(30);
719 l_party_id varchar2(100);
720 l_scs_code varchar2(30);
721
722 cursor l2_object1_csr is
723 select
724 CS.constrained_yn
725 ,PR.OBJECT1_ID1
726 ,PR.OBJECT1_ID2
727 ,PR.JTOT_OBJECT1_CODE
728 ,PR.RLE_CODE
729 ,KH.BUY_OR_SELL
730 ,KH.SCS_CODE
731 from
732 okc_k_party_roles_b PR
733 ,okc_contact_sources CS
734 ,okc_k_headers_all_b KH -- Modified by Jvorugan for Bug:4645341 okc_k_headers_b KH
735 where
736 PR.ID = p_ctcv_rec.CPL_ID
737 and CS.CRO_CODE = p_ctcv_rec.CRO_CODE
738 and CS.RLE_CODE = PR.RLE_CODE
739 and CS.jtot_object_code = p_ctcv_rec.jtot_object1_code
740 and sysdate >= CS.start_date
741 and (CS.end_date is NULL or CS.end_date>=sysdate)
742 and KH.ID = p_ctcv_rec.DNZ_CHR_ID
743 and CS.BUY_OR_SELL = KH.BUY_OR_SELL
744 ;
745 e_no_data_found EXCEPTION;
746 PRAGMA EXCEPTION_INIT(e_no_data_found,100);
747 e_too_many_rows EXCEPTION;
748 PRAGMA EXCEPTION_INIT(e_too_many_rows,-1422);
749 e_source_not_exists EXCEPTION;
750 PRAGMA EXCEPTION_INIT(e_source_not_exists,-942);
751 e_source_not_exists1 EXCEPTION;
752 PRAGMA EXCEPTION_INIT(e_source_not_exists1,-903);
753 e_column_not_exists EXCEPTION;
754 PRAGMA EXCEPTION_INIT(e_column_not_exists,-904);
755
756 /** this part of the code attempts to retrieve party_id from
757 the parent party role **/
758
759 FUNCTION Retrieve_Party_ID (P_jtot_object_code IN VARCHAR2,
760 P_object_id1 IN VARCHAR2,
761 P_object_id2 IN VARCHAR2) return VARCHAR2
762 IS
763
764 i NUMBER;
765 l_sql_stmt VARCHAR2(1000);
766 v_cursorID INTEGER;
767 v_party_id VARCHAR2(100) := '00';
768 v_dummy INTEGER;
769
770 FUNCTION COLUMN_EXISTS
771 ( p_object_code VARCHAR2
772 , p_column_name VARCHAR2
773 ) RETURN BOOLEAN IS
774 l_view_name varchar2(200);
775 l_found NUMBER;
776 i NUMBER;
777
778 Cursor l_jtfv_csr Is
779 SELECT from_table
780 FROM jtf_objects_vl
781 WHERE object_code = p_object_code
782 AND sysdate between nvl(start_date_active , sysdate-1)
783 and nvl(end_date_active , sysdate+1);
784
785 Cursor l_jtf_source_csr Is
786 SELECT 1 FROM USER_TAB_COLUMNS
787 WHERE table_name = l_view_name
788 AND column_name = p_column_name;
789
790 BEGIN
791 open l_jtfv_csr;
792 fetch l_jtfv_csr into l_view_name;
793 close l_jtfv_csr;
794
795 -- Trim any space and character after that
796 i := INSTR(l_view_name,' ');
797 If (i > 0) Then
798 l_view_name := substr(l_view_name,1,i - 1);
799 End If;
800
801 open l_jtf_source_csr;
802 fetch l_jtf_source_csr into l_found;
803 close l_jtf_source_csr;
804 If (l_found = 1) Then
805 return TRUE;
806 Else
807 return FALSE;
808 End If;
809 EXCEPTION
810 when NO_DATA_FOUND Then
811 If (l_jtfv_csr%ISOPEN) Then
812 close l_jtfv_csr;
813 End If;
814 If (l_jtf_source_csr%ISOPEN) Then
815 close l_jtf_source_csr;
816 End If;
817 return FALSE;
818
819 when OTHERS then
820 If (l_jtfv_csr%ISOPEN) Then
821 close l_jtfv_csr;
822 End If;
823 If (l_jtf_source_csr%ISOPEN) Then
824 close l_jtf_source_csr;
825 End If;
826 return FALSE;
827 END;
828
829
830 BEGIN
831
832
833 l_sql_stmt := OKC_UTIL.GET_SQL_FROM_JTFV(P_jtot_object_code);
834
835 IF l_sql_stmt is null THEN
836 return '-1';
837 END IF;
838
839 IF (column_exists(p_jtot_object_code,'PARTY_ID')) THEN
840
841 i := INSTR(l_sql_stmt,'WHERE');
842 If (i > 0) Then
843 l_sql_stmt := SUBSTR(l_sql_stmt,1, i + 5) ||
844 ' ID1 = ' ||''''|| P_OBJECT_ID1 ||''''|| ' AND ' ||
845 ' ID2 = ' ||''''|| P_OBJECT_ID2 ||'''';
846 -- || ' AND ' ||
847 -- SUBSTR(l_sql_stmt,i + 5);
848 Else
849 -- no where clause. Add before ORDER BY if any
850 i := INSTR(l_sql_stmt,'ORDER BY');
851 If (i > 0) Then
852 l_sql_stmt := SUBSTR(l_sql_stmt,1,i-1) ||
853 ' WHERE ID1 = ' ||''''|| P_OBJECT_ID1 ||''''|| ' AND '||
854 ' ID2 = ' ||''''|| P_OBJECT_ID2 ||''''||
855 ' ' || SUBSTR(l_sql_stmt,i);
856 Else
857 -- no where and no order by
858 l_sql_stmt := l_sql_stmt || ' WHERE ID1 = '||''''|| P_OBJECT_ID1 ||''''
859 || ' AND '|| ' ID2 = '||'''' || P_OBJECT_ID2 ||'''';
860 End If;
861 End If;
862
863 END IF;
864
865 l_sql_stmt := 'SELECT PARTY_ID FROM '|| l_sql_stmt;
866
867 v_cursorID := DBMS_SQL.OPEN_CURSOR;
868 -- dbms_output.put_line(l_sql_stmt);
869 DBMS_SQL.PARSE(v_cursorID,l_sql_stmt,dbms_sql.native);
870 DBMS_SQL.DEFINE_COLUMN(v_cursorID,1,v_party_id,100);
871 v_dummy := DBMS_SQL.EXECUTE(v_cursorID);
872 IF DBMS_SQL.FETCH_ROWS(v_cursorID)= 0 THEN
873 RETURN '-1';
874 END IF;
875 DBMS_SQL.COLUMN_VALUE(v_cursorID,1,v_party_id);
876 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
877
878 return v_party_id;
879
880 EXCEPTION
881 WHEN OTHERS THEN
882 RETURN '-1';
883
884 END;
885 begin
886
887 IF (l_debug = 'Y') THEN
888 okc_debug.Set_Indentation('OKC_CTC_PVT');
889 okc_debug.log('2100: Entered validate_object1_id1', 2);
890 END IF;
891
892 x_return_status := OKC_API.G_RET_STS_SUCCESS;
893 if (p_ctcv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR or p_ctcv_rec.JTOT_OBJECT1_CODE is NULL) then
894 return;
895 end if;
896 if (p_ctcv_rec.object1_id1 = OKC_API.G_MISS_CHAR or p_ctcv_rec.object1_id1 is NULL) then
897 return;
898 end if;
899 open l_object1_csr;
900 fetch l_object1_csr into l_from_table, l_where_clause;
901 close l_object1_csr;
902 if (l_where_clause is not null) then
903 l_where_clause := ' and '||l_where_clause;
904 end if;
905 --
906 l_dummy_var := 'N';
907 open l2_object1_csr;
908 fetch l2_object1_csr into l_dummy_var,l_party_id1,l_party_id2,l_jtot_object1_code,l_rle_code,l_buy_sell,l_scs_code;
909 close l2_object1_csr;
910 if (l_dummy_var = 'N') then
911 begin
912
913 IF (l_debug = 'Y') THEN
914 okc_debug.Set_Indentation('OKC_CTC_PVT');
915 okc_debug.log('2200: Entered validate_object1_id1', 2);
916 END IF;
917
918 EXECUTE IMMEDIATE 'select ''x'' from '||l_from_table||
919 ' where id1=:object1_id1 and id2=:object1_id2'||l_where_clause
920 into l_dummy_var
921 USING p_ctcv_rec.object1_id1, p_ctcv_rec.object1_id2;
922
923 IF (l_debug = 'Y') THEN
924 okc_debug.log('2300: Leaving validate_object1_id1', 2);
925 okc_debug.Reset_Indentation;
926 END IF;
927
928 exception
929 when e_column_not_exists then
930
931 IF (l_debug = 'Y') THEN
932 okc_debug.log('2400: Exiting validate_object1_id1:e_column_not_exists Exception', 2);
933 okc_debug.Reset_Indentation;
934 END IF;
935
936 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_from_table||'.ID1, .ID2');
937 x_return_status := OKC_API.G_RET_STS_ERROR;
938 return;
939 when e_too_many_rows then
940
941 IF (l_debug = 'Y') THEN
942 okc_debug.log('2500: Exiting validate_object1_id1:e_too_many_rows Exception', 2);
943 okc_debug.Reset_Indentation;
944 END IF;
945
946 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_from_table||'.ID1, .ID2');
947 x_return_status := OKC_API.G_RET_STS_ERROR;
948 return;
949 end;
950 else
951 begin
952
953 IF (l_debug = 'Y') THEN
954 okc_debug.Set_Indentation('OKC_CTC_PVT');
955 okc_debug.log('2600: Entered validate_object1_id1', 2);
956 END IF;
957
958
959 IF l_scs_code='PROJECT' and (l_jtot_object1_code IN ( 'OKE_BILLTO','OKE_CUST_KADMIN','OKE_MARKFOR','OKE_SHIPTO','OKE_CUSTACCT','OKE_VENDSITE','OKX_CUSTACCT'))
960 THEN
961
962 l_party_id:= retrieve_party_id(l_jtot_object1_code,l_party_id1,l_party_id2);
963
964 EXECUTE IMMEDIATE 'select ''x'' from '||l_from_table||
965 ' where id1=:object1_id1 and id2=:object1_id2 and party_id=:l_party1_id1 '
966 ||l_where_clause into l_dummy_var
967 USING p_ctcv_rec.object1_id1, p_ctcv_rec.object1_id2,l_party_id;
968
969 ELSE
970 EXECUTE IMMEDIATE 'select ''x'' from '||l_from_table||
971 ' where id1=:object1_id1 and id2=:object1_id2 and party_id=:l_party1_id1 and party_id2=:l_party1_id2'
972 ||l_where_clause into l_dummy_var
973 USING p_ctcv_rec.object1_id1, p_ctcv_rec.object1_id2,l_party_id1,l_party_id2;
974 END IF;
975
976 IF (l_debug = 'Y') THEN
977 okc_debug.log('2700: Leaving validate_object1_id1', 2);
978 okc_debug.Reset_Indentation;
979 END IF;
980
981 exception
982 when e_column_not_exists then
983
984 IF (l_debug = 'Y') THEN
985 okc_debug.log('2800: Exiting validate_object1_id1:e_column_not_exists Exception', 2);
986 okc_debug.Reset_Indentation;
987 END IF;
988
989 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_from_table||'.ID1, .ID2, .PARTY_ID, .PARTY_ID2');
990 x_return_status := OKC_API.G_RET_STS_ERROR;
991 return;
992 when e_too_many_rows then
993
994 IF (l_debug = 'Y') THEN
995 okc_debug.log('2900: Exiting validate_object1_id1:e_too_many_rows Exception', 2);
996 okc_debug.Reset_Indentation;
997 END IF;
998
999 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_from_table||'.ID1, .ID2, .PARTY_ID, .PARTY_ID2');
1000 x_return_status := OKC_API.G_RET_STS_ERROR;
1001 return;
1002 end;
1003 end if;
1004 --
1005 IF (l_debug = 'Y') THEN
1006 okc_debug.log('3000: Exiting validate_object1_id1', 2);
1007 okc_debug.Reset_Indentation;
1008 END IF;
1009
1010 exception
1011 when e_source_not_exists then
1012
1013 IF (l_debug = 'Y') THEN
1014 okc_debug.log('3100: Exiting validate_object1_id1:e_source_not_exists Exception', 2);
1015 okc_debug.Reset_Indentation;
1016 END IF;
1017
1018 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'JTOT_OBJECT1_CODE');
1019 x_return_status := OKC_API.G_RET_STS_ERROR;
1020 when e_source_not_exists1 then
1021
1022 IF (l_debug = 'Y') THEN
1023 okc_debug.log('3200: Exiting validate_object1_id1:e_source_not_exists1 Exception', 2);
1024 okc_debug.Reset_Indentation;
1025 END IF;
1026
1027 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'JTOT_OBJECT1_CODE');
1028 x_return_status := OKC_API.G_RET_STS_ERROR;
1029 when e_no_data_found then
1030
1031 IF (l_debug = 'Y') THEN
1032 okc_debug.log('3300: Exiting validate_object1_id1:e_no_data_found Exception', 2);
1033 okc_debug.Reset_Indentation;
1034 END IF;
1035
1036 OKC_API.set_message(G_APP_NAME,'OKC_INVALID_CONTACT');
1037 x_return_status := OKC_API.G_RET_STS_ERROR;
1038 when OTHERS then
1039
1040 IF (l_debug = 'Y') THEN
1041 okc_debug.log('3400: Exiting validate_object1_id1:OTHERS Exception', 2);
1042 okc_debug.Reset_Indentation;
1043 END IF;
1044
1045 if l_object1_csr%ISOPEN then
1046 close l_object1_csr;
1047 end if;
1048 if l2_object1_csr%ISOPEN then
1049 close l2_object1_csr;
1050 end if;
1051 OKC_API.set_message(p_app_name => g_app_name,
1052 p_msg_name => g_unexpected_error,
1053 p_token1 => g_sqlcode_token,
1054 p_token1_value => sqlcode,
1055 p_token2 => g_sqlerrm_token,
1056 p_token2_value => sqlerrm);
1057 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1058
1059 end validate_object1_id1;
1060
1061 -- Start of comments
1062 --
1063 -- Procedure Name : validate_dnz_chr_id
1064 -- Description :
1065 -- Business Rules :
1066 -- Parameters :
1067 -- Version : 1.0
1068 -- End of comments
1069
1070 procedure validate_dnz_chr_id(x_return_status OUT NOCOPY VARCHAR2,
1071 p_ctcv_rec IN CTCV_REC_TYPE) is
1072 l_dummy varchar2(1) := '?';
1073 cursor Kt_Hr_Mj_Vr is
1074 select '!'
1075 from okc_k_headers_all_b -- Modified by Jvorugan for Bug:4645341 okc_k_headers_b
1076 where id = p_ctcv_rec.dnz_chr_id;
1077 begin
1078
1079 IF (l_debug = 'Y') THEN
1080 okc_debug.Set_Indentation('OKC_CTC_PVT');
1081 okc_debug.log('3500: Entered validate_dnz_chr_id', 2);
1082 END IF;
1083
1084 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1085 if (p_ctcv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) then
1086 return;
1087 end if;
1088 if (p_ctcv_rec.dnz_chr_id is NULL) then
1089 OKC_API.set_message(p_app_name => g_app_name,
1090 p_msg_name => G_REQUIRED_VALUE,
1091 p_token1 => G_COL_NAME_TOKEN,
1092 p_token1_value => 'DNZ_CHR_ID');
1093 x_return_status := OKC_API.G_RET_STS_ERROR;
1094 return;
1095 end if;
1096 open Kt_Hr_Mj_Vr;
1097 fetch Kt_Hr_Mj_Vr into l_dummy;
1098 close Kt_Hr_Mj_Vr;
1099 if (l_dummy='?') then
1100 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DNZ_CHR_ID');
1101 x_return_status := OKC_API.G_RET_STS_ERROR;
1102 return;
1103 end if;
1104
1105 IF (l_debug = 'Y') THEN
1106 okc_debug.log('3600: Leaving validate_dnz_chr_id', 2);
1107 okc_debug.Reset_Indentation;
1108 END IF;
1109
1110 exception
1111 when OTHERS then
1112
1113 IF (l_debug = 'Y') THEN
1114 okc_debug.log('3700: Exiting validate_dnz_chr_id:OTHERS Exception', 2);
1115 okc_debug.Reset_Indentation;
1116 END IF;
1117
1118 OKC_API.set_message(p_app_name => g_app_name,
1119 p_msg_name => g_unexpected_error,
1120 p_token1 => g_sqlcode_token,
1121 p_token1_value => sqlcode,
1122 p_token2 => g_sqlerrm_token,
1123 p_token2_value => sqlerrm);
1124 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1125
1126 end validate_dnz_chr_id;
1127
1128 -- Start of comments
1129 --
1130 -- Procedure Name : validate_primary_yn
1131 -- Description :
1132 -- Business Rules :
1133 -- Parameters :
1134 -- Version : 1.0
1135 -- End of comments
1136 PROCEDURE validate_primary_yn(x_return_status OUT NOCOPY VARCHAR2,
1137 p_ctcv_rec IN CTCV_REC_TYPE) IS
1138
1139 l_dummy varchar2(1) := '?';
1140
1141 CURSOR l_contacts_csr IS
1142 SELECT '!'
1143 FROM OKC_CONTACTS ctc
1144 WHERE id <> NVL(p_ctcv_rec.id,-99999)
1145 AND cpl_id = p_ctcv_rec.cpl_id
1146 AND dnz_chr_id = p_ctcv_rec.dnz_chr_id
1147 AND primary_yn = 'Y'
1148 AND cro_code = p_ctcv_rec.cro_code
1149 AND NVL(TO_DATE(p_ctcv_rec.start_date,'YYYY/MM/DD'),TRUNC(sysdate)) <=
1150 NVL(ctc.end_date,to_date('01-01-4713','DD-MM-YYYY'))
1151 AND NVL(TO_DATE(p_ctcv_rec.end_date,'YYYY/MM/DD'),TO_DATE('01-01-4713','DD-MM-YYYY')) >=
1152 NVL(ctc.start_date,TRUNC(sysdate));
1153
1154 Begin
1155 IF (l_debug = 'Y') THEN
1156 okc_debug.Set_Indentation('OKC_CPL_PVT');
1157 okc_debug.log('4150: Entered validate_primary_yn', 2);
1158 END IF;
1159
1160 -- initialize return status
1161 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1162
1163 -- check that data exists
1164 IF (p_ctcv_rec.primary_yn IS NOT NULL AND
1165 p_ctcv_rec.primary_yn <> OKC_API.G_MISS_CHAR) THEN
1166
1167 IF p_ctcv_rec.primary_yn NOT IN ('Y','N') Then
1168 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1169 p_msg_name => g_invalid_value,
1170 p_token1 => g_col_name_token,
1171 p_token1_value => 'PRIMARY_YN');
1172 -- notify caller of an error
1173 x_return_status := OKC_API.G_RET_STS_ERROR;
1174 -- halt validation
1175 raise G_EXCEPTION_HALT_VALIDATION;
1176 END IF;
1177 END IF; -- end of (p_ctcv_rec.primary_yn <> OKC_API.G_MISS_CHAR OR ---
1178
1179 IF (p_ctcv_rec.primary_yn = 'Y') THEN
1180 --- (p_ctcv_rec.end_date >= trunc(SYSDATE)) THEN ---Bug#2284573
1181 -----(SYSDATE between p_ctcv_rec.start_date AND p_ctcv_rec.end_date) THEN ---Bug#2284573
1182 OPEN l_contacts_csr;
1183 FETCH l_contacts_csr INTO l_dummy;
1184 CLOSE l_contacts_csr;
1185 IF (l_dummy='!') THEN
1186 OKC_API.set_message(G_APP_NAME,'OKC_PRIMARY_CONTACT_ERROR');
1187 x_return_status := OKC_API.G_RET_STS_ERROR;
1188 IF (l_debug = 'Y') THEN
1189 okc_debug.Reset_Indentation;
1190 END IF;
1191 RETURN;
1192 END IF;
1193 END IF; --end of (p_ctcv_rec.primary_yn = 'Y')
1194 IF (l_debug = 'Y') THEN
1195 okc_debug.log('4160: Leaving validate_primary_yn', 2);
1196 okc_debug.Reset_Indentation;
1197 END IF;
1198
1199 exception
1200 when G_EXCEPTION_HALT_VALIDATION then
1201 IF (l_debug = 'Y') THEN
1202 okc_debug.log('4170: Exiting validate_primary_yn:G_EXCEPTION_HALT_VALIDATION Exception', 2);
1203 okc_debug.Reset_Indentation;
1204 END IF;
1205
1206 -- no processing necessary; validation can continue with next column
1207 null;
1208 when OTHERS then
1209 IF (l_debug = 'Y') THEN
1210 okc_debug.log('4180: Exiting validate_primary_yn:OTHERS Exception', 2);
1211 okc_debug.Reset_Indentation;
1212 END IF;
1213
1214 -- store SQL error message on message stack
1215 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1216 p_msg_name => g_unexpected_error,
1217 p_token1 => g_sqlcode_token,
1218 p_token1_value => sqlcode,
1219 p_token2 => g_sqlerrm_token,
1220 p_token2_value => sqlerrm);
1221
1222 -- notify caller of an error as UNEXPETED error
1223 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1224
1225 End validate_primary_yn;
1226
1227
1228
1229 -- Start of comments
1230 --
1231 -- Procedure Name : validate_resource_class
1232 -- Description :
1233 -- Business Rules :
1234 -- Parameters :
1235 -- Version : 1.0
1236 -- End of comments
1237 PROCEDURE validate_resource_class(x_return_status OUT NOCOPY VARCHAR2,
1238 p_ctcv_rec IN CTCV_REC_TYPE) IS
1239 Begin
1240 IF (l_debug = 'Y') THEN
1241 okc_debug.Set_Indentation('OKC_CPL_PVT');
1242 okc_debug.log('4250: Entered validate_resource_class', 2);
1243 END IF;
1244
1245 -- initialize return status
1246 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1247
1248 -- check that data exists
1249 IF (p_ctcv_rec.resource_class IS NOT NULL AND
1250 p_ctcv_rec.resource_class <> OKC_API.G_MISS_CHAR) THEN
1251
1252 -- Check If the value is a valid code from lookup table
1253 x_return_status := OKC_UTIL.check_lookup_code('OKS_RESOURCE_CLASS',
1254 p_ctcv_rec.resource_class);
1255
1256 If (x_return_status = OKC_API.G_RET_STS_ERROR) Then
1257 --set error message in message stack
1258 OKC_API.SET_MESSAGE(
1259 p_app_name => G_APP_NAME,
1260 p_msg_name => G_INVALID_VALUE,
1261 p_token1 => G_COL_NAME_TOKEN,
1262 p_token1_value => 'RESOURCE_CLASS');
1263
1264 RAISE G_EXCEPTION_HALT_VALIDATION;
1265 Elsif (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1266 raise G_EXCEPTION_HALT_VALIDATION;
1267 End If;
1268 End If;
1269 IF (l_debug = 'Y') THEN
1270 okc_debug.log('4260: Exiting validate_resource_class', 2);
1271 okc_debug.Reset_Indentation;
1272 END IF;
1273
1274 EXCEPTION
1275 when OTHERS then
1276
1277 IF (l_debug = 'Y') THEN
1278 okc_debug.log('4270: Exiting validate_resource_class:OTHERS Exception', 2);
1279 okc_debug.Reset_Indentation;
1280 END IF;
1281
1282 -- store SQL error message on message stack
1283 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1284 p_msg_name => g_unexpected_error,
1285 p_token1 => g_sqlcode_token,
1286 p_token2 => g_sqlerrm_token,
1287 p_token2_value => sqlerrm);
1288
1289 -- notify caller of an error as UNEXPETED error
1290 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1291
1292 End validate_resource_class;
1293
1294 /*+++++++++++++End of hand code +++++++++++++++++++*/
1295 --------------------------------------------
1296 -- alidate_Attributes for:OKC_CONTACTS_V --
1297 --------------------------------------------
1298 FUNCTION Validate_Attributes (
1299 p_ctcv_rec IN ctcv_rec_type
1300 ) RETURN VARCHAR2 IS
1301 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1302
1303 /*+++++++++++++Start of hand code +++++++++++++++++*/
1304 x_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1305 BEGIN
1306
1307 IF (l_debug = 'Y') THEN
1308 okc_debug.Set_Indentation('OKC_CTC_PVT');
1309 okc_debug.log('3800: Entered Validate_Attributes', 2);
1310 END IF;
1311
1312 -- call each column-level validation
1313 validate_cpl_id(x_return_status => l_return_status,
1314 p_ctcv_rec => p_ctcv_rec);
1315 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1316 return OKC_API.G_RET_STS_UNEXP_ERROR;
1317 end if;
1318 if (l_return_status = OKC_API.G_RET_STS_ERROR
1319 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1320 x_return_status := OKC_API.G_RET_STS_ERROR;
1321 end if;
1322 --
1323 validate_cro_code(x_return_status => l_return_status,
1324 p_ctcv_rec => p_ctcv_rec);
1325 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1326 return OKC_API.G_RET_STS_UNEXP_ERROR;
1327 end if;
1328 if (l_return_status = OKC_API.G_RET_STS_ERROR
1329 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1330 x_return_status := OKC_API.G_RET_STS_ERROR;
1331 end if;
1332 --
1333 validate_dnz_chr_id(x_return_status => l_return_status,
1334 p_ctcv_rec => p_ctcv_rec);
1335 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1336 return OKC_API.G_RET_STS_UNEXP_ERROR;
1337 end if;
1338 if (l_return_status = OKC_API.G_RET_STS_ERROR
1339 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1340 x_return_status := OKC_API.G_RET_STS_ERROR;
1341 end if;
1342 --
1343 validate_primary_yn(x_return_status => l_return_status,
1344 p_ctcv_rec => p_ctcv_rec);
1345 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1346 return OKC_API.G_RET_STS_UNEXP_ERROR;
1347 end If;
1348 If (l_return_status = OKC_API.G_RET_STS_ERROR
1349 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
1350 x_return_status := OKC_API.G_RET_STS_ERROR;
1351 end If;
1352 --
1353 validate_resource_class(x_return_status => l_return_status,
1354 p_ctcv_rec => p_ctcv_rec);
1355 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1356 RETURN OKC_API.G_RET_STS_UNEXP_ERROR;
1357 END IF;
1358 IF (l_return_status = OKC_API.G_RET_STS_ERROR
1359 AND x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1360 x_return_status := OKC_API.G_RET_STS_ERROR;
1361 END IF;
1362 --
1363 return x_return_status;
1364
1365 IF (l_debug = 'Y') THEN
1366 okc_debug.log('3900: Leaving Validate_Attributes', 2);
1367 okc_debug.Reset_Indentation;
1368 END IF;
1369
1370 exception
1371 when OTHERS then
1372
1373 IF (l_debug = 'Y') THEN
1374 okc_debug.log('4000: Exiting Validate_Attributes:OTHERS Exception', 2);
1375 okc_debug.Reset_Indentation;
1376 END IF;
1377
1378 -- store SQL error message on message stack for caller
1379 OKC_API.set_message(p_app_name => g_app_name,
1380 p_msg_name => g_unexpected_error,
1381 p_token1 => g_sqlcode_token,
1382 p_token1_value => sqlcode,
1383 p_token2 => g_sqlerrm_token,
1384 p_token2_value => sqlerrm);
1385 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1386 return x_return_status;
1387
1388 END Validate_Attributes;
1389 /*+++++++++++++End of hand code +++++++++++++++++*/
1390
1391 ---------------------------------------------------------------------------
1392 -- PROCEDURE Validate_Record
1393 ---------------------------------------------------------------------------
1394 ----------------------------------------
1395 -- Validate_Record for:OKC_CONTACTS_V --
1396 ----------------------------------------
1397 FUNCTION Validate_Record (
1398 p_ctcv_rec IN ctcv_rec_type
1399 ) RETURN VARCHAR2 IS
1400
1401 /*+++++++++++++Start of hand code +++++++++++++++++*/
1402 -- ------------------------------------------------------
1403 -- To check for any matching row, for unique check
1404 -- The cursor includes id check filter to handle updates
1405 -- for case K2 should not overwrite already existing K1
1406 -- There is an index OKC_CONTACTS_U2 on these five fields
1407 -- with three fields nullable. Hence NVL added around.
1408 -- ------------------------------------------------------
1409 -- Added for Bug 3177402,2900541 conditions to check for overlapping dates
1410 -- Start date has to be non-null ,end date can be Null
1411 CURSOR cur_ctcv IS
1412 SELECT 'x'
1413 FROM okc_contacts
1414 WHERE cpl_id = p_ctcv_rec.CPL_ID
1415 AND cro_code = p_ctcv_rec.CRO_CODE
1416 AND NVL(jtot_object1_code,'X') = NVL(p_ctcv_rec.JTOT_OBJECT1_CODE,'X')
1417 AND NVL(object1_id1,'X') = NVL(p_ctcv_rec.OBJECT1_ID1,'X')
1418 AND NVL(object1_id2,'X') = NVL(p_ctcv_rec.OBJECT1_ID2,'X')
1419 AND id <> NVL(p_ctcv_rec.ID,-9999)
1420 AND (((p_ctcv_rec.START_DATE BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE))
1421 OR ((TRUNC(START_DATE) BETWEEN p_ctcv_rec.START_DATE AND p_ctcv_rec.END_DATE)
1422 OR (TRUNC(END_DATE) BETWEEN p_ctcv_rec.START_DATE AND p_ctcv_rec.END_DATE))
1423 OR (p_ctcv_rec.END_DATE BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)))
1424 OR ((END_DATE IS NULL AND (p_ctcv_rec.END_DATE = START_DATE))
1425 OR (p_ctcv_rec.END_DATE IS NULL AND (p_ctcv_rec.START_DATE <=END_DATE))
1426 OR (p_ctcv_rec.END_DATE IS NULL AND END_DATE IS NULL)));
1427
1428
1429 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1430 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1431 l_row_found BOOLEAN := FALSE;
1432 l_dummy VARCHAR2(1);
1433 -- indirection
1434 l_rle_code varchar2(30);
1435 l_buy_or_sell varchar2(3);
1436 l_access_level varchar2(1);
1437
1438 Cursor c_buy_or_sell Is
1439 Select buy_or_sell
1440 From okc_k_headers_all_b --Modified by Jvorugan for Bug:4645341 okc_k_headers_b
1441 Where id = p_ctcv_rec.dnz_chr_id;
1442
1443 Cursor c_rle_code Is
1444 Select rle_code
1445 From okc_k_party_roles_b
1446 Where dnz_chr_id = p_ctcv_rec.dnz_chr_id;
1447
1448 Cursor c_access_level(p_rle_code varchar2, p_intent varchar2) Is
1449 Select access_level
1450 From okc_contact_sources
1451 Where rle_code = p_rle_code
1452 and cro_code = p_ctcv_rec.cro_code
1453 and buy_or_sell = p_intent;
1454 --
1455
1456 BEGIN
1457
1458 IF (l_debug = 'Y') THEN
1459 okc_debug.Set_Indentation('OKC_CTC_PVT');
1460 okc_debug.log('4100: Entered Validate_Record', 2);
1461 END IF;
1462
1463 -- indirection
1464 Open c_buy_or_sell;
1465 Fetch c_buy_or_sell Into l_buy_or_sell;
1466 Close c_buy_or_sell;
1467
1468 Open c_rle_code;
1469 Fetch c_rle_code Into l_rle_code;
1470 Close c_rle_code;
1471
1472 Open c_access_level(l_rle_code, l_buy_or_sell);
1473 Fetch c_access_level Into l_access_level;
1474 Close c_access_level;
1475
1476 If l_access_level = 'U' Then -- if user defined
1477 --
1478
1479 validate_JTOT_OBJECT1_CODE(x_return_status => l_return_status,
1480 p_ctcv_rec => p_ctcv_rec);
1481
1482 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
1483 THEN
1484 IF (l_debug = 'Y') THEN
1485 okc_debug.log('4150: Exiting Validate_jtot_object1_code in validate_record:unexp err', 2);
1486 okc_debug.Reset_Indentation;
1487 END IF;
1488 RETURN OKC_API.G_RET_STS_UNEXP_ERROR;
1489 END IF;
1490
1491 IF ( l_return_status = OKC_API.G_RET_STS_ERROR
1492 AND x_return_status = OKC_API.G_RET_STS_SUCCESS)
1493 THEN
1494 x_return_status := OKC_API.G_RET_STS_ERROR;
1495 END IF;
1496
1497 -- -----------------------------------------------------
1498 validate_OBJECT1_ID1(x_return_status => l_return_status,
1499 p_ctcv_rec => p_ctcv_rec);
1500
1501 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
1502 THEN
1503 IF (l_debug = 'Y') THEN
1504 okc_debug.log('4170: Exiting Validate_object1_id1 in validate_record:unexp err', 2);
1505 okc_debug.Reset_Indentation;
1506 END IF;
1507 RETURN OKC_API.G_RET_STS_UNEXP_ERROR;
1508 END IF;
1509
1510 IF ( l_return_status = OKC_API.G_RET_STS_ERROR
1511 AND x_return_status = OKC_API.G_RET_STS_SUCCESS)
1512 THEN
1513 x_return_status := OKC_API.G_RET_STS_ERROR;
1514 END IF;
1515 End If; -- if user defined
1516 -- ---------------------------------------------------------------------
1517 -- Bug 1636056 related changes - Shyam
1518 -- OKC_UTIL.check_comp_unique call is replaced with
1519 -- the explicit cursors above, for identical function to check
1520 -- uniqueness in OKC_CONTACTS table.
1521 -- ---------------------------------------------------------------------
1522 IF ( (p_ctcv_rec.CPL_ID IS NOT NULL)
1523 AND (p_ctcv_rec.CPL_ID <> OKC_API.G_MISS_NUM) )
1524 AND
1525 ( (p_ctcv_rec.CRO_CODE IS NOT NULL)
1526 AND (p_ctcv_rec.CRO_CODE <> OKC_API.G_MISS_CHAR) )
1527 THEN
1528 OPEN cur_ctcv;
1529 FETCH cur_ctcv INTO l_dummy;
1530 l_row_found := cur_ctcv%FOUND;
1531 CLOSE cur_ctcv;
1532
1533 IF (l_row_found)
1534 THEN
1535 -- Display the newly defined error message
1536 OKC_API.set_message(G_APP_NAME,
1537 'OKC_DUP_CONTACT_PARTY_ID');
1538 x_return_status := OKC_API.G_RET_STS_ERROR;
1539 END IF;
1540 END IF;
1541
1542 -- ---------------------------------------------------------------------
1543 -- Bug 1785323 related changes - jogeorge
1544 -- Validating End_date against Start_Date
1545 -- ---------------------------------------------------------------------
1546
1547 IF p_ctcv_rec.START_DATE IS NOT NULL AND p_ctcv_rec.END_DATE IS NOT NULL THEN
1548 IF p_ctcv_rec.END_DATE < p_ctcv_rec.START_DATE THEN
1549 OKC_API.set_message(G_APP_NAME,'OKC_INVALID_END_DATE');
1550 x_return_status := OKC_API.G_RET_STS_ERROR;
1551 END IF;
1552 END IF;
1553 RETURN x_return_status;
1554
1555 IF (l_debug = 'Y') THEN
1556 okc_debug.log('4200: Leaving Validate_Record', 2);
1557 okc_debug.Reset_Indentation;
1558 END IF;
1559
1560 EXCEPTION
1561 WHEN OTHERS THEN
1562
1563 IF (l_debug = 'Y') THEN
1564 okc_debug.log('4300: Exiting Validate_Record:OTHERS Exception', 2);
1565 okc_debug.Reset_Indentation;
1566 END IF;
1567
1568 -- store SQL error message on message stack for caller
1569 OKC_API.set_message(p_app_name => g_app_name,
1570 p_msg_name => g_unexpected_error,
1571 p_token1 => g_sqlcode_token,
1572 p_token1_value => sqlcode,
1573 p_token2 => g_sqlerrm_token,
1574 p_token2_value => sqlerrm);
1575
1576 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1577
1578 RETURN x_return_status;
1579
1580 END Validate_Record;
1581
1582 /*+++++++++++++End of hand code +++++++++++++++++*/
1583
1584 ---------------------------------------------------------------------------
1585 -- PROCEDURE Migrate
1586 ---------------------------------------------------------------------------
1587 PROCEDURE migrate (
1588 p_from IN ctcv_rec_type,
1589 p_to IN OUT NOCOPY ctc_rec_type
1590 ) IS
1591 BEGIN
1592
1593 p_to.id := p_from.id;
1594 p_to.cpl_id := p_from.cpl_id;
1595 p_to.cro_code := p_from.cro_code;
1596 p_to.dnz_chr_id := p_from.dnz_chr_id;
1597 p_to.object1_id1 := p_from.object1_id1;
1598 p_to.object1_id2 := p_from.object1_id2;
1599 p_to.JTOT_OBJECT1_CODE := p_from.JTOT_OBJECT1_CODE;
1600 p_to.object_version_number := p_from.object_version_number;
1601 p_to.created_by := p_from.created_by;
1602 p_to.creation_date := p_from.creation_date;
1603 p_to.last_updated_by := p_from.last_updated_by;
1604 p_to.last_update_date := p_from.last_update_date;
1605 p_to.contact_sequence := p_from.contact_sequence;
1606 p_to.last_update_login := p_from.last_update_login;
1607 p_to.primary_yn := p_from.primary_yn;
1608 p_to.RESOURCE_CLASS := p_from.RESOURCE_CLASS;
1609 p_to.SALES_GROUP_ID := p_from.SALES_GROUP_ID;
1610 p_to.attribute_category := p_from.attribute_category;
1611 p_to.attribute1 := p_from.attribute1;
1612 p_to.attribute2 := p_from.attribute2;
1613 p_to.attribute3 := p_from.attribute3;
1614 p_to.attribute4 := p_from.attribute4;
1615 p_to.attribute5 := p_from.attribute5;
1616 p_to.attribute6 := p_from.attribute6;
1617 p_to.attribute7 := p_from.attribute7;
1618 p_to.attribute8 := p_from.attribute8;
1619 p_to.attribute9 := p_from.attribute9;
1620 p_to.attribute10 := p_from.attribute10;
1621 p_to.attribute11 := p_from.attribute11;
1622 p_to.attribute12 := p_from.attribute12;
1623 p_to.attribute13 := p_from.attribute13;
1624 p_to.attribute14 := p_from.attribute14;
1625 p_to.attribute15 := p_from.attribute15;
1626 p_to.start_date := p_from.start_date;
1627 p_to.end_date := p_from.end_date;
1628
1629 END migrate;
1630
1631 PROCEDURE migrate (
1632 p_from IN ctc_rec_type,
1633 p_to IN OUT NOCOPY ctcv_rec_type
1634 ) IS
1635 BEGIN
1636
1637 p_to.id := p_from.id;
1638 p_to.cpl_id := p_from.cpl_id;
1639 p_to.cro_code := p_from.cro_code;
1640 p_to.dnz_chr_id := p_from.dnz_chr_id;
1641 p_to.object1_id1 := p_from.object1_id1;
1642 p_to.object1_id2 := p_from.object1_id2;
1643 p_to.JTOT_OBJECT1_CODE := p_from.JTOT_OBJECT1_CODE;
1644 p_to.object_version_number := p_from.object_version_number;
1645 p_to.created_by := p_from.created_by;
1646 p_to.creation_date := p_from.creation_date;
1647 p_to.last_updated_by := p_from.last_updated_by;
1648 p_to.last_update_date := p_from.last_update_date;
1649 p_to.contact_sequence := p_from.contact_sequence;
1650 p_to.last_update_login := p_from.last_update_login;
1651 p_to.primary_yn := p_from.primary_yn;
1652 p_to.RESOURCE_CLASS := p_from.RESOURCE_CLASS;
1653 p_to.SALES_GROUP_ID := p_from.SALES_GROUP_ID;
1654 p_to.attribute_category := p_from.attribute_category;
1655 p_to.attribute1 := p_from.attribute1;
1656 p_to.attribute2 := p_from.attribute2;
1657 p_to.attribute3 := p_from.attribute3;
1658 p_to.attribute4 := p_from.attribute4;
1659 p_to.attribute5 := p_from.attribute5;
1660 p_to.attribute6 := p_from.attribute6;
1661 p_to.attribute7 := p_from.attribute7;
1662 p_to.attribute8 := p_from.attribute8;
1663 p_to.attribute9 := p_from.attribute9;
1664 p_to.attribute10 := p_from.attribute10;
1665 p_to.attribute11 := p_from.attribute11;
1666 p_to.attribute12 := p_from.attribute12;
1667 p_to.attribute13 := p_from.attribute13;
1668 p_to.attribute14 := p_from.attribute14;
1669 p_to.attribute15 := p_from.attribute15;
1670 p_to.start_date := p_from.start_date;
1671 p_to.end_date := p_from.end_date;
1672
1673 END migrate;
1674
1675 ---------------------------------------------------------------------------
1676 -- PROCEDURE validate_row
1677 ---------------------------------------------------------------------------
1678 -------------------------------------
1679 -- validate_row for:OKC_CONTACTS_V --
1680 -------------------------------------
1681 PROCEDURE validate_row(
1682 p_api_version IN NUMBER,
1683 p_init_msg_list IN VARCHAR2 ,
1684 x_return_status OUT NOCOPY VARCHAR2,
1685 x_msg_count OUT NOCOPY NUMBER,
1686 x_msg_data OUT NOCOPY VARCHAR2,
1687 p_ctcv_rec IN ctcv_rec_type) IS
1688
1689 l_api_version CONSTANT NUMBER := 1;
1690 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1691 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1692 l_ctcv_rec ctcv_rec_type := p_ctcv_rec;
1693 l_ctc_rec ctc_rec_type;
1694 BEGIN
1695
1696 IF (l_debug = 'Y') THEN
1697 okc_debug.Set_Indentation('OKC_CTC_PVT');
1698 okc_debug.log('4600: Entered validate_row', 2);
1699 END IF;
1700
1701 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1702 G_PKG_NAME,
1703 p_init_msg_list,
1704 l_api_version,
1705 p_api_version,
1706 '_PVT',
1707 x_return_status);
1708 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1709 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1710 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1711 RAISE OKC_API.G_EXCEPTION_ERROR;
1712 END IF;
1713 --- Validate all non-missing attributes (Item Level Validation)
1714 l_return_status := Validate_Attributes(l_ctcv_rec);
1715 --- If any errors happen abort API
1716 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1717 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1718 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1719 RAISE OKC_API.G_EXCEPTION_ERROR;
1720 END IF;
1721 l_return_status := Validate_Record(l_ctcv_rec);
1722 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1723 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1724 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1725 RAISE OKC_API.G_EXCEPTION_ERROR;
1726 END IF;
1727 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1728
1729 IF (l_debug = 'Y') THEN
1730 okc_debug.log('4700: Leaving validate_row', 2);
1731 okc_debug.Reset_Indentation;
1732 END IF;
1733
1734 EXCEPTION
1735 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1736
1737 IF (l_debug = 'Y') THEN
1738 okc_debug.log('4800: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1739 okc_debug.Reset_Indentation;
1740 END IF;
1741
1742 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1743 (
1744 l_api_name,
1745 G_PKG_NAME,
1746 'OKC_API.G_RET_STS_ERROR',
1747 x_msg_count,
1748 x_msg_data,
1749 '_PVT'
1750 );
1751 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1752
1753 IF (l_debug = 'Y') THEN
1754 okc_debug.log('4900: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1755 okc_debug.Reset_Indentation;
1756 END IF;
1757
1758 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1759 (
1760 l_api_name,
1761 G_PKG_NAME,
1762 'OKC_API.G_RET_STS_UNEXP_ERROR',
1763 x_msg_count,
1764 x_msg_data,
1765 '_PVT'
1766 );
1767 WHEN OTHERS THEN
1768
1769 IF (l_debug = 'Y') THEN
1770 okc_debug.log('5000: Exiting validate_row:OTHERS Exception', 2);
1771 okc_debug.Reset_Indentation;
1772 END IF;
1773
1774 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1775 (
1776 l_api_name,
1777 G_PKG_NAME,
1778 'OTHERS',
1779 x_msg_count,
1780 x_msg_data,
1781 '_PVT'
1782 );
1783
1784 END validate_row;
1785 ------------------------------------------
1786 -- PL/SQL TBL validate_row for:CTCV_TBL --
1787 ------------------------------------------
1788 PROCEDURE validate_row(
1789 p_api_version IN NUMBER,
1790 p_init_msg_list IN VARCHAR2 ,
1791 x_return_status OUT NOCOPY VARCHAR2,
1792 x_msg_count OUT NOCOPY NUMBER,
1793 x_msg_data OUT NOCOPY VARCHAR2,
1794 p_ctcv_tbl IN ctcv_tbl_type) IS
1795
1796 l_api_version CONSTANT NUMBER := 1;
1797 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1798 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1799 i NUMBER := 0;
1800 BEGIN
1801
1802 IF (l_debug = 'Y') THEN
1803 okc_debug.Set_Indentation('OKC_CTC_PVT');
1804 okc_debug.log('5100: Entered validate_row', 2);
1805 END IF;
1806
1807 OKC_API.init_msg_list(p_init_msg_list);
1808 -- Make sure PL/SQL table has records in it before passing
1809 IF (p_ctcv_tbl.COUNT > 0) THEN
1810 i := p_ctcv_tbl.FIRST;
1811 LOOP
1812 validate_row (
1813 p_api_version => p_api_version,
1814 p_init_msg_list => OKC_API.G_FALSE,
1815 x_return_status => x_return_status,
1816 x_msg_count => x_msg_count,
1817 x_msg_data => x_msg_data,
1818 p_ctcv_rec => p_ctcv_tbl(i));
1819 EXIT WHEN (i = p_ctcv_tbl.LAST);
1820 i := p_ctcv_tbl.NEXT(i);
1821 END LOOP;
1822 END IF;
1823
1824 IF (l_debug = 'Y') THEN
1825 okc_debug.log('5200: Leaving validate_row', 2);
1826 okc_debug.Reset_Indentation;
1827 END IF;
1828
1829 EXCEPTION
1830 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1831
1832 IF (l_debug = 'Y') THEN
1833 okc_debug.log('5300: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1834 okc_debug.Reset_Indentation;
1835 END IF;
1836
1837 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1838 (
1839 l_api_name,
1840 G_PKG_NAME,
1841 'OKC_API.G_RET_STS_ERROR',
1842 x_msg_count,
1843 x_msg_data,
1844 '_PVT'
1845 );
1846 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1847
1848 IF (l_debug = 'Y') THEN
1849 okc_debug.log('5400: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1850 okc_debug.Reset_Indentation;
1851 END IF;
1852
1853 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1854 (
1855 l_api_name,
1856 G_PKG_NAME,
1857 'OKC_API.G_RET_STS_UNEXP_ERROR',
1858 x_msg_count,
1859 x_msg_data,
1860 '_PVT'
1861 );
1862 WHEN OTHERS THEN
1863
1864 IF (l_debug = 'Y') THEN
1865 okc_debug.log('5500: Exiting validate_row:OTHERS Exception', 2);
1866 okc_debug.Reset_Indentation;
1867 END IF;
1868
1869 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1870 (
1871 l_api_name,
1872 G_PKG_NAME,
1873 'OTHERS',
1874 x_msg_count,
1875 x_msg_data,
1876 '_PVT'
1877 );
1878
1879 END validate_row;
1880
1881 ---------------------------------------------------------------------------
1882 -- PROCEDURE insert_row
1883 ---------------------------------------------------------------------------
1884 ---------------------------------
1885 -- insert_row for:OKC_CONTACTS --
1886 ---------------------------------
1887 PROCEDURE insert_row(
1888 p_init_msg_list IN VARCHAR2 ,
1889 x_return_status OUT NOCOPY VARCHAR2,
1890 x_msg_count OUT NOCOPY NUMBER,
1891 x_msg_data OUT NOCOPY VARCHAR2,
1892 p_ctc_rec IN ctc_rec_type,
1893 x_ctc_rec OUT NOCOPY ctc_rec_type) IS
1894
1895 l_api_version CONSTANT NUMBER := 1;
1896 l_api_name CONSTANT VARCHAR2(30) := 'CONTACTS_insert_row';
1897 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1898 l_ctc_rec ctc_rec_type := p_ctc_rec;
1899 l_def_ctc_rec ctc_rec_type;
1900 -------------------------------------
1901 -- Set_Attributes for:OKC_CONTACTS --
1902 -------------------------------------
1903 FUNCTION Set_Attributes (
1904 p_ctc_rec IN ctc_rec_type,
1905 x_ctc_rec OUT NOCOPY ctc_rec_type
1906 ) RETURN VARCHAR2 IS
1907 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1908 BEGIN
1909
1910 x_ctc_rec := p_ctc_rec;
1911 x_ctc_rec.primary_yn := UPPER(x_ctc_rec.PRIMARY_YN);
1912
1913 RETURN(l_return_status);
1914
1915 END Set_Attributes;
1916 BEGIN
1917
1918 IF (l_debug = 'Y') THEN
1919 okc_debug.Set_Indentation('OKC_CTC_PVT');
1920 okc_debug.log('5700: Entered insert_row', 2);
1921 END IF;
1922
1923 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1924 p_init_msg_list,
1925 '_PVT',
1926 x_return_status);
1927 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1928 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1929 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1930 RAISE OKC_API.G_EXCEPTION_ERROR;
1931 END IF;
1932 --- Setting item attributes
1933 l_return_status := Set_Attributes(
1934 p_ctc_rec, -- IN
1935 l_ctc_rec); -- OUT
1936 --- If any errors happen abort API
1937 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1938 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1939 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1940 RAISE OKC_API.G_EXCEPTION_ERROR;
1941 END IF;
1942 INSERT INTO OKC_CONTACTS(
1943 id,
1944 cpl_id,
1945 cro_code,
1946 dnz_chr_id,
1947 object1_id1,
1948 object1_id2,
1949 JTOT_OBJECT1_CODE,
1950 object_version_number,
1951 created_by,
1952 creation_date,
1953 last_updated_by,
1954 last_update_date,
1955 contact_sequence,
1956 last_update_login,
1957 primary_yn,
1958 RESOURCE_CLASS,
1959 SALES_GROUP_ID,
1960 attribute_category,
1961 attribute1,
1962 attribute2,
1963 attribute3,
1964 attribute4,
1965 attribute5,
1966 attribute6,
1967 attribute7,
1968 attribute8,
1969 attribute9,
1970 attribute10,
1971 attribute11,
1972 attribute12,
1973 attribute13,
1974 attribute14,
1975 attribute15,
1976 start_date,
1977 end_date)
1978 VALUES (
1979 l_ctc_rec.id,
1980 l_ctc_rec.cpl_id,
1981 l_ctc_rec.cro_code,
1982 l_ctc_rec.dnz_chr_id,
1983 l_ctc_rec.object1_id1,
1984 l_ctc_rec.object1_id2,
1985 l_ctc_rec.JTOT_OBJECT1_CODE,
1986 l_ctc_rec.object_version_number,
1987 l_ctc_rec.created_by,
1988 l_ctc_rec.creation_date,
1989 l_ctc_rec.last_updated_by,
1990 l_ctc_rec.last_update_date,
1991 l_ctc_rec.contact_sequence,
1992 l_ctc_rec.last_update_login,
1993 l_ctc_rec.primary_yn,
1994 l_ctc_rec.RESOURCE_CLASS,
1995 l_ctc_rec.SALES_GROUP_ID,
1996 l_ctc_rec.attribute_category,
1997 l_ctc_rec.attribute1,
1998 l_ctc_rec.attribute2,
1999 l_ctc_rec.attribute3,
2000 l_ctc_rec.attribute4,
2001 l_ctc_rec.attribute5,
2002 l_ctc_rec.attribute6,
2003 l_ctc_rec.attribute7,
2004 l_ctc_rec.attribute8,
2005 l_ctc_rec.attribute9,
2006 l_ctc_rec.attribute10,
2007 l_ctc_rec.attribute11,
2008 l_ctc_rec.attribute12,
2009 l_ctc_rec.attribute13,
2010 l_ctc_rec.attribute14,
2011 l_ctc_rec.attribute15,
2012 l_ctc_rec.start_date,
2013 l_ctc_rec.end_date);
2014 -- Set OUT values
2015 /* Bug fix for 6882512*/
2016 update_contact_stecode(p_chr_id => l_ctc_rec.dnz_chr_id,
2017 x_return_status=>l_return_status);
2018
2019 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2020 RAISE OKC_API.G_EXCEPTION_ERROR;
2021 END IF;
2022 /* Bug fix for 6882512*/
2023 x_ctc_rec := l_ctc_rec;
2024 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2025
2026 IF (l_debug = 'Y') THEN
2027 okc_debug.log('5800: Leaving insert_row', 2);
2028 okc_debug.Reset_Indentation;
2029 END IF;
2030
2031 EXCEPTION
2032 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2033
2034 IF (l_debug = 'Y') THEN
2035 okc_debug.log('5900: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2036 okc_debug.Reset_Indentation;
2037 END IF;
2038
2039 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2040 (
2041 l_api_name,
2042 G_PKG_NAME,
2043 'OKC_API.G_RET_STS_ERROR',
2044 x_msg_count,
2045 x_msg_data,
2046 '_PVT'
2047 );
2048 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2049
2050 IF (l_debug = 'Y') THEN
2051 okc_debug.log('6000: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2052 okc_debug.Reset_Indentation;
2053 END IF;
2054
2055 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2056 (
2057 l_api_name,
2058 G_PKG_NAME,
2059 'OKC_API.G_RET_STS_UNEXP_ERROR',
2060 x_msg_count,
2061 x_msg_data,
2062 '_PVT'
2063 );
2064 WHEN OTHERS THEN
2065
2066 IF (l_debug = 'Y') THEN
2067 okc_debug.log('6100: Exiting insert_row:OTHERS Exception', 2);
2068 okc_debug.Reset_Indentation;
2069 END IF;
2070
2071 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2072 (
2073 l_api_name,
2074 G_PKG_NAME,
2075 'OTHERS',
2076 x_msg_count,
2077 x_msg_data,
2078 '_PVT'
2079 );
2080
2081 END insert_row;
2082 -----------------------------------
2083 -- insert_row for:OKC_CONTACTS_V --
2084 -----------------------------------
2085 PROCEDURE insert_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_ctcv_rec IN ctcv_rec_type,
2092 x_ctcv_rec OUT NOCOPY ctcv_rec_type) IS
2093
2094 l_api_version CONSTANT NUMBER := 1;
2095 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2096 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2097 l_ctcv_rec ctcv_rec_type;
2098 l_def_ctcv_rec ctcv_rec_type;
2099 l_ctc_rec ctc_rec_type;
2100 lx_ctc_rec ctc_rec_type;
2101 -------------------------------
2102 -- FUNCTION fill_who_columns --
2103 -------------------------------
2104 FUNCTION fill_who_columns (
2105 p_ctcv_rec IN ctcv_rec_type
2106 ) RETURN ctcv_rec_type IS
2107 l_ctcv_rec ctcv_rec_type := p_ctcv_rec;
2108 BEGIN
2109
2110 l_ctcv_rec.CREATION_DATE := SYSDATE;
2111 l_ctcv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2112 l_ctcv_rec.LAST_UPDATE_DATE := SYSDATE;
2113 l_ctcv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2114 l_ctcv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2115
2116 RETURN(l_ctcv_rec);
2117
2118 END fill_who_columns;
2119 ---------------------------------------
2120 -- Set_Attributes for:OKC_CONTACTS_V --
2121 ---------------------------------------
2122 FUNCTION Set_Attributes (
2123 p_ctcv_rec IN ctcv_rec_type,
2124 x_ctcv_rec OUT NOCOPY ctcv_rec_type
2125 ) RETURN VARCHAR2 IS
2126 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2127 BEGIN
2128
2129 x_ctcv_rec := p_ctcv_rec;
2130 x_ctcv_rec.OBJECT_VERSION_NUMBER := 1;
2131
2132 RETURN(l_return_status);
2133
2134 END Set_Attributes;
2135 BEGIN
2136
2137 IF (l_debug = 'Y') THEN
2138 okc_debug.Set_Indentation('OKC_CTC_PVT');
2139 okc_debug.log('6400: Entered insert_row', 2);
2140 END IF;
2141
2142 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2143 G_PKG_NAME,
2144 p_init_msg_list,
2145 l_api_version,
2146 p_api_version,
2147 '_PVT',
2148 x_return_status);
2149 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2150 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2151 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2152 RAISE OKC_API.G_EXCEPTION_ERROR;
2153 END IF;
2154 l_ctcv_rec := null_out_defaults(p_ctcv_rec);
2155 -- Set primary key value
2156 l_ctcv_rec.ID := get_seq_id;
2157 --- Setting item attributes
2158 l_return_status := Set_Attributes(
2159 l_ctcv_rec, -- IN
2160 l_def_ctcv_rec); -- OUT
2161 --- If any errors happen abort API
2162 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2163 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2164 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2165 RAISE OKC_API.G_EXCEPTION_ERROR;
2166 END IF;
2167 l_def_ctcv_rec := fill_who_columns(l_def_ctcv_rec);
2168 --- Validate all non-missing attributes (Item Level Validation)
2169 l_return_status := Validate_Attributes(l_def_ctcv_rec);
2170 --- If any errors happen abort API
2171 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2172 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2173 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2174 RAISE OKC_API.G_EXCEPTION_ERROR;
2175 END IF;
2176 l_return_status := Validate_Record(l_def_ctcv_rec);
2177 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2178 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2179 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2180 RAISE OKC_API.G_EXCEPTION_ERROR;
2181 END IF;
2182 --------------------------------------
2183 -- Move VIEW record to "Child" records
2184 --------------------------------------
2185 migrate(l_def_ctcv_rec, l_ctc_rec);
2186 --------------------------------------------
2187 -- Call the INSERT_ROW for each child record
2188 --------------------------------------------
2189 insert_row(
2190 p_init_msg_list,
2191 x_return_status,
2192 x_msg_count,
2193 x_msg_data,
2194 l_ctc_rec,
2195 lx_ctc_rec
2196 );
2197 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2198 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2199 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2200 RAISE OKC_API.G_EXCEPTION_ERROR;
2201 END IF;
2202 migrate(lx_ctc_rec, l_def_ctcv_rec);
2203 -- Set OUT values
2204 x_ctcv_rec := l_def_ctcv_rec;
2205 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2206
2207 IF (l_debug = 'Y') THEN
2208 okc_debug.log('6500: Leaving insert_row', 2);
2209 okc_debug.Reset_Indentation;
2210 END IF;
2211
2212 EXCEPTION
2213 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2214
2215 IF (l_debug = 'Y') THEN
2216 okc_debug.log('6600: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2217 okc_debug.Reset_Indentation;
2218 END IF;
2219
2220 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2221 (
2222 l_api_name,
2223 G_PKG_NAME,
2224 'OKC_API.G_RET_STS_ERROR',
2225 x_msg_count,
2226 x_msg_data,
2227 '_PVT'
2228 );
2229 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2230
2231 IF (l_debug = 'Y') THEN
2232 okc_debug.log('6700: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2233 okc_debug.Reset_Indentation;
2234 END IF;
2235
2236 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2237 (
2238 l_api_name,
2239 G_PKG_NAME,
2240 'OKC_API.G_RET_STS_UNEXP_ERROR',
2241 x_msg_count,
2242 x_msg_data,
2243 '_PVT'
2244 );
2245 WHEN OTHERS THEN
2246
2247 IF (l_debug = 'Y') THEN
2248 okc_debug.log('6800: Exiting insert_row:OTHERS Exception', 2);
2249 okc_debug.Reset_Indentation;
2250 END IF;
2251
2252 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2253 (
2254 l_api_name,
2255 G_PKG_NAME,
2256 'OTHERS',
2257 x_msg_count,
2258 x_msg_data,
2259 '_PVT'
2260 );
2261
2262 END insert_row;
2263 ----------------------------------------
2264 -- PL/SQL TBL insert_row for:CTCV_TBL --
2265 ----------------------------------------
2266 PROCEDURE insert_row(
2267 p_api_version IN NUMBER,
2268 p_init_msg_list IN VARCHAR2 ,
2269 x_return_status OUT NOCOPY VARCHAR2,
2270 x_msg_count OUT NOCOPY NUMBER,
2271 x_msg_data OUT NOCOPY VARCHAR2,
2272 p_ctcv_tbl IN ctcv_tbl_type,
2273 x_ctcv_tbl OUT NOCOPY ctcv_tbl_type) IS
2274
2275 l_api_version CONSTANT NUMBER := 1;
2276 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2277 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2278 i NUMBER := 0;
2279 BEGIN
2280
2281 IF (l_debug = 'Y') THEN
2282 okc_debug.Set_Indentation('OKC_CTC_PVT');
2283 okc_debug.log('6900: Entered insert_row', 2);
2284 END IF;
2285
2286 OKC_API.init_msg_list(p_init_msg_list);
2287 -- Make sure PL/SQL table has records in it before passing
2288 IF (p_ctcv_tbl.COUNT > 0) THEN
2289 i := p_ctcv_tbl.FIRST;
2290 LOOP
2291 insert_row (
2292 p_api_version => p_api_version,
2293 p_init_msg_list => OKC_API.G_FALSE,
2294 x_return_status => x_return_status,
2295 x_msg_count => x_msg_count,
2296 x_msg_data => x_msg_data,
2297 p_ctcv_rec => p_ctcv_tbl(i),
2298 x_ctcv_rec => x_ctcv_tbl(i));
2299 EXIT WHEN (i = p_ctcv_tbl.LAST);
2300 i := p_ctcv_tbl.NEXT(i);
2301 END LOOP;
2302 END IF;
2303
2304 IF (l_debug = 'Y') THEN
2305 okc_debug.log('7000: Leaving insert_row', 2);
2306 okc_debug.Reset_Indentation;
2307 END IF;
2308
2309 EXCEPTION
2310 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2311
2312 IF (l_debug = 'Y') THEN
2313 okc_debug.log('7100: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2314 okc_debug.Reset_Indentation;
2315 END IF;
2316
2317 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2318 (
2319 l_api_name,
2320 G_PKG_NAME,
2321 'OKC_API.G_RET_STS_ERROR',
2322 x_msg_count,
2323 x_msg_data,
2324 '_PVT'
2325 );
2326 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2327
2328 IF (l_debug = 'Y') THEN
2329 okc_debug.log('7200: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2330 okc_debug.Reset_Indentation;
2331 END IF;
2332
2333 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2334 (
2335 l_api_name,
2336 G_PKG_NAME,
2337 'OKC_API.G_RET_STS_UNEXP_ERROR',
2338 x_msg_count,
2339 x_msg_data,
2340 '_PVT'
2341 );
2342 WHEN OTHERS THEN
2343
2344 IF (l_debug = 'Y') THEN
2345 okc_debug.log('7300: Exiting insert_row:OTHERS Exception', 2);
2346 okc_debug.Reset_Indentation;
2347 END IF;
2348
2349 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2350 (
2351 l_api_name,
2352 G_PKG_NAME,
2353 'OTHERS',
2354 x_msg_count,
2355 x_msg_data,
2356 '_PVT'
2357 );
2358
2359 END insert_row;
2360
2361 ---------------------------------------------------------------------------
2362 -- PROCEDURE lock_row
2363 ---------------------------------------------------------------------------
2364 -------------------------------
2365 -- lock_row for:OKC_CONTACTS --
2366 -------------------------------
2367 PROCEDURE lock_row(
2368 p_init_msg_list IN VARCHAR2 ,
2369 x_return_status OUT NOCOPY VARCHAR2,
2370 x_msg_count OUT NOCOPY NUMBER,
2371 x_msg_data OUT NOCOPY VARCHAR2,
2372 p_ctc_rec IN ctc_rec_type) IS
2373
2374 E_Resource_Busy EXCEPTION;
2375 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2376 CURSOR lock_csr (p_ctc_rec IN ctc_rec_type) IS
2377 SELECT OBJECT_VERSION_NUMBER
2378 FROM OKC_CONTACTS
2379 WHERE ID = p_ctc_rec.id
2380 AND OBJECT_VERSION_NUMBER = p_ctc_rec.object_version_number
2381 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2382
2383 CURSOR lchk_csr (p_ctc_rec IN ctc_rec_type) IS
2384 SELECT OBJECT_VERSION_NUMBER
2385 FROM OKC_CONTACTS
2386 WHERE ID = p_ctc_rec.id;
2387 l_api_version CONSTANT NUMBER := 1;
2388 l_api_name CONSTANT VARCHAR2(30) := 'CONTACTS_lock_row';
2389 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2390 l_object_version_number OKC_CONTACTS.OBJECT_VERSION_NUMBER%TYPE;
2391 lc_object_version_number OKC_CONTACTS.OBJECT_VERSION_NUMBER%TYPE;
2392 l_row_notfound BOOLEAN := FALSE;
2393 lc_row_notfound BOOLEAN := FALSE;
2394 BEGIN
2395
2396 IF (l_debug = 'Y') THEN
2397 okc_debug.Set_Indentation('OKC_CTC_PVT');
2398 okc_debug.log('7400: Entered lock_row', 2);
2399 END IF;
2400
2401 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2402 p_init_msg_list,
2403 '_PVT',
2404 x_return_status);
2405 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2406 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2407 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2408 RAISE OKC_API.G_EXCEPTION_ERROR;
2409 END IF;
2410 BEGIN
2411
2412 IF (l_debug = 'Y') THEN
2413 okc_debug.Set_Indentation('OKC_CTC_PVT');
2414 okc_debug.log('7500: Entered lock_row', 2);
2415 END IF;
2416
2417 OPEN lock_csr(p_ctc_rec);
2418 FETCH lock_csr INTO l_object_version_number;
2419 l_row_notfound := lock_csr%NOTFOUND;
2420 CLOSE lock_csr;
2421
2422 IF (l_debug = 'Y') THEN
2423 okc_debug.log('7600: Leaving lock_row', 2);
2424 okc_debug.Reset_Indentation;
2425 END IF;
2426
2427 EXCEPTION
2428 WHEN E_Resource_Busy THEN
2429
2430 IF (l_debug = 'Y') THEN
2431 okc_debug.log('7700: Exiting lock_row:E_Resource_Busy Exception', 2);
2432 okc_debug.Reset_Indentation;
2433 END IF;
2434
2435 IF (lock_csr%ISOPEN) THEN
2436 CLOSE lock_csr;
2437 END IF;
2438 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2439 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2440 END;
2441
2442 IF ( l_row_notfound ) THEN
2443 OPEN lchk_csr(p_ctc_rec);
2444 FETCH lchk_csr INTO lc_object_version_number;
2445 lc_row_notfound := lchk_csr%NOTFOUND;
2446 CLOSE lchk_csr;
2447 END IF;
2448 IF (lc_row_notfound) THEN
2449 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2450 RAISE OKC_API.G_EXCEPTION_ERROR;
2451 ELSIF lc_object_version_number > p_ctc_rec.object_version_number THEN
2452 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2453 RAISE OKC_API.G_EXCEPTION_ERROR;
2454 ELSIF lc_object_version_number <> p_ctc_rec.object_version_number THEN
2455 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2456 RAISE OKC_API.G_EXCEPTION_ERROR;
2457 ELSIF lc_object_version_number = -1 THEN
2458 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2459 RAISE OKC_API.G_EXCEPTION_ERROR;
2460 END IF;
2461 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2462
2463 IF (l_debug = 'Y') THEN
2464 okc_debug.log('7800: Leaving lock_row', 2);
2465 okc_debug.Reset_Indentation;
2466 END IF;
2467
2468 EXCEPTION
2469 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2470
2471 IF (l_debug = 'Y') THEN
2472 okc_debug.log('7900: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2473 okc_debug.Reset_Indentation;
2474 END IF;
2475
2476 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2477 (
2478 l_api_name,
2479 G_PKG_NAME,
2480 'OKC_API.G_RET_STS_ERROR',
2481 x_msg_count,
2482 x_msg_data,
2483 '_PVT'
2484 );
2485 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2486
2487 IF (l_debug = 'Y') THEN
2488 okc_debug.log('8000: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2489 okc_debug.Reset_Indentation;
2490 END IF;
2491
2492 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2493 (
2494 l_api_name,
2495 G_PKG_NAME,
2496 'OKC_API.G_RET_STS_UNEXP_ERROR',
2497 x_msg_count,
2498 x_msg_data,
2499 '_PVT'
2500 );
2501 WHEN OTHERS THEN
2502
2503 IF (l_debug = 'Y') THEN
2504 okc_debug.log('8100: Exiting lock_row:OTHERS Exception', 2);
2505 okc_debug.Reset_Indentation;
2506 END IF;
2507
2508 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2509 (
2510 l_api_name,
2511 G_PKG_NAME,
2512 'OTHERS',
2513 x_msg_count,
2514 x_msg_data,
2515 '_PVT'
2516 );
2517
2518 END lock_row;
2519 ---------------------------------
2520 -- lock_row for:OKC_CONTACTS_V --
2521 ---------------------------------
2522 PROCEDURE lock_row(
2523 p_api_version IN NUMBER,
2524 p_init_msg_list IN VARCHAR2 ,
2525 x_return_status OUT NOCOPY VARCHAR2,
2526 x_msg_count OUT NOCOPY NUMBER,
2527 x_msg_data OUT NOCOPY VARCHAR2,
2528 p_ctcv_rec IN ctcv_rec_type) IS
2529
2530 l_api_version CONSTANT NUMBER := 1;
2531 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2532 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2533 l_ctc_rec ctc_rec_type;
2534 BEGIN
2535
2536 IF (l_debug = 'Y') THEN
2537 okc_debug.Set_Indentation('OKC_CTC_PVT');
2538 okc_debug.log('8200: Entered lock_row', 2);
2539 END IF;
2540
2541 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2542 G_PKG_NAME,
2543 p_init_msg_list,
2544 l_api_version,
2545 p_api_version,
2546 '_PVT',
2547 x_return_status);
2548 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2549 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2550 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2551 RAISE OKC_API.G_EXCEPTION_ERROR;
2552 END IF;
2553 --------------------------------------
2554 -- Move VIEW record to "Child" records
2555 --------------------------------------
2556 migrate(p_ctcv_rec, l_ctc_rec);
2557 --------------------------------------------
2558 -- Call the LOCK_ROW for each child record
2559 --------------------------------------------
2560 lock_row(
2561 p_init_msg_list,
2562 x_return_status,
2563 x_msg_count,
2564 x_msg_data,
2565 l_ctc_rec
2566 );
2567 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2568 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2569 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2570 RAISE OKC_API.G_EXCEPTION_ERROR;
2571 END IF;
2572 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2573
2574 IF (l_debug = 'Y') THEN
2575 okc_debug.log('8300: Leaving lock_row', 2);
2576 okc_debug.Reset_Indentation;
2577 END IF;
2578
2579 EXCEPTION
2580 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2581
2582 IF (l_debug = 'Y') THEN
2583 okc_debug.log('8400: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2584 okc_debug.Reset_Indentation;
2585 END IF;
2586
2587 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2588 (
2589 l_api_name,
2590 G_PKG_NAME,
2591 'OKC_API.G_RET_STS_ERROR',
2592 x_msg_count,
2593 x_msg_data,
2594 '_PVT'
2595 );
2596 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2597
2598 IF (l_debug = 'Y') THEN
2599 okc_debug.log('8500: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2600 okc_debug.Reset_Indentation;
2601 END IF;
2602
2603 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2604 (
2605 l_api_name,
2606 G_PKG_NAME,
2607 'OKC_API.G_RET_STS_UNEXP_ERROR',
2608 x_msg_count,
2609 x_msg_data,
2610 '_PVT'
2611 );
2612 WHEN OTHERS THEN
2613
2614 IF (l_debug = 'Y') THEN
2615 okc_debug.log('8600: Exiting lock_row:OTHERS Exception', 2);
2616 okc_debug.Reset_Indentation;
2617 END IF;
2618
2619 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2620 (
2621 l_api_name,
2622 G_PKG_NAME,
2623 'OTHERS',
2624 x_msg_count,
2625 x_msg_data,
2626 '_PVT'
2627 );
2628
2629 END lock_row;
2630 --------------------------------------
2631 -- PL/SQL TBL lock_row for:CTCV_TBL --
2632 --------------------------------------
2633 PROCEDURE lock_row(
2634 p_api_version IN NUMBER,
2635 p_init_msg_list IN VARCHAR2 ,
2636 x_return_status OUT NOCOPY VARCHAR2,
2637 x_msg_count OUT NOCOPY NUMBER,
2638 x_msg_data OUT NOCOPY VARCHAR2,
2639 p_ctcv_tbl IN ctcv_tbl_type) IS
2640
2641 l_api_version CONSTANT NUMBER := 1;
2642 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2643 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2644 i NUMBER := 0;
2645 BEGIN
2646
2647 IF (l_debug = 'Y') THEN
2648 okc_debug.Set_Indentation('OKC_CTC_PVT');
2649 okc_debug.log('8700: Entered lock_row', 2);
2650 END IF;
2651
2652 OKC_API.init_msg_list(p_init_msg_list);
2653 -- Make sure PL/SQL table has records in it before passing
2654 IF (p_ctcv_tbl.COUNT > 0) THEN
2655 i := p_ctcv_tbl.FIRST;
2656 LOOP
2657 lock_row (
2658 p_api_version => p_api_version,
2659 p_init_msg_list => OKC_API.G_FALSE,
2660 x_return_status => x_return_status,
2661 x_msg_count => x_msg_count,
2662 x_msg_data => x_msg_data,
2663 p_ctcv_rec => p_ctcv_tbl(i));
2664 EXIT WHEN (i = p_ctcv_tbl.LAST);
2665 i := p_ctcv_tbl.NEXT(i);
2666 END LOOP;
2667 END IF;
2668
2669 IF (l_debug = 'Y') THEN
2670 okc_debug.log('8800: Leaving lock_row', 2);
2671 okc_debug.Reset_Indentation;
2672 END IF;
2673
2674 EXCEPTION
2675 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2676
2677 IF (l_debug = 'Y') THEN
2678 okc_debug.log('8900: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2679 okc_debug.Reset_Indentation;
2680 END IF;
2681
2682 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2683 (
2684 l_api_name,
2685 G_PKG_NAME,
2686 'OKC_API.G_RET_STS_ERROR',
2687 x_msg_count,
2688 x_msg_data,
2689 '_PVT'
2690 );
2691 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2692
2693 IF (l_debug = 'Y') THEN
2694 okc_debug.log('9000: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2695 okc_debug.Reset_Indentation;
2696 END IF;
2697
2698 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2699 (
2700 l_api_name,
2701 G_PKG_NAME,
2702 'OKC_API.G_RET_STS_UNEXP_ERROR',
2703 x_msg_count,
2704 x_msg_data,
2705 '_PVT'
2706 );
2707 WHEN OTHERS THEN
2708
2709 IF (l_debug = 'Y') THEN
2710 okc_debug.log('9100: Exiting lock_row:OTHERS Exception', 2);
2711 okc_debug.Reset_Indentation;
2712 END IF;
2713
2714 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2715 (
2716 l_api_name,
2717 G_PKG_NAME,
2718 'OTHERS',
2719 x_msg_count,
2720 x_msg_data,
2721 '_PVT'
2722 );
2723
2724 END lock_row;
2725
2726 ---------------------------------------------------------------------------
2727 -- PROCEDURE update_row
2728 ---------------------------------------------------------------------------
2729 ---------------------------------
2730 -- update_row for:OKC_CONTACTS --
2731 ---------------------------------
2732 PROCEDURE update_row(
2733 p_init_msg_list IN VARCHAR2 ,
2734 x_return_status OUT NOCOPY VARCHAR2,
2735 x_msg_count OUT NOCOPY NUMBER,
2736 x_msg_data OUT NOCOPY VARCHAR2,
2737 p_ctc_rec IN ctc_rec_type,
2738 x_ctc_rec OUT NOCOPY ctc_rec_type) IS
2739
2740 l_api_version CONSTANT NUMBER := 1;
2741 l_api_name CONSTANT VARCHAR2(30) := 'CONTACTS_update_row';
2742 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2743 l_ctc_rec ctc_rec_type := p_ctc_rec;
2744 l_def_ctc_rec ctc_rec_type;
2745 l_row_notfound BOOLEAN := TRUE;
2746
2747 /*added for bug 6882512*/
2748
2749 l_ste_code VARCHAR2(30);
2750
2751 CURSOR get_k_status(p_chr_id IN NUMBER) IS
2752 SELECT ste_code
2753 FROM okc_k_headers_all_b KH,okc_statuses_b STB
2754 WHERE KH.sts_code = STB.code
2755 AND id= p_chr_id;
2756
2757 /*added for bug 6882512*/
2758 ----------------------------------
2759 -- FUNCTION populate_new_record --
2760 ----------------------------------
2761 FUNCTION populate_new_record (
2762 p_ctc_rec IN ctc_rec_type,
2763 x_ctc_rec OUT NOCOPY ctc_rec_type
2764 ) RETURN VARCHAR2 IS
2765 l_ctc_rec ctc_rec_type;
2766 l_row_notfound BOOLEAN := TRUE;
2767 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2768 BEGIN
2769
2770 IF (l_debug = 'Y') THEN
2771 okc_debug.Set_Indentation('OKC_CTC_PVT');
2772 okc_debug.log('9200: Entered populate_new_record', 2);
2773 END IF;
2774
2775 x_ctc_rec := p_ctc_rec;
2776 -- Get current database values
2777 l_ctc_rec := get_rec(p_ctc_rec, l_row_notfound);
2778 IF (l_row_notfound) THEN
2779 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2780 END IF;
2781 IF (x_ctc_rec.id = OKC_API.G_MISS_NUM)
2782 THEN
2783 x_ctc_rec.id := l_ctc_rec.id;
2784 END IF;
2785 IF (x_ctc_rec.cpl_id = OKC_API.G_MISS_NUM)
2786 THEN
2787 x_ctc_rec.cpl_id := l_ctc_rec.cpl_id;
2788 END IF;
2789 IF (x_ctc_rec.cro_code = OKC_API.G_MISS_CHAR)
2790 THEN
2791 x_ctc_rec.cro_code := l_ctc_rec.cro_code;
2792 END IF;
2793 IF (x_ctc_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2794 THEN
2795 x_ctc_rec.dnz_chr_id := l_ctc_rec.dnz_chr_id;
2796 END IF;
2797 IF (x_ctc_rec.object1_id1 = OKC_API.G_MISS_CHAR)
2798 THEN
2799 x_ctc_rec.object1_id1 := l_ctc_rec.object1_id1;
2800 END IF;
2801 IF (x_ctc_rec.object1_id2 = OKC_API.G_MISS_CHAR)
2802 THEN
2803 x_ctc_rec.object1_id2 := l_ctc_rec.object1_id2;
2804 END IF;
2805 IF (x_ctc_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
2806 THEN
2807 x_ctc_rec.JTOT_OBJECT1_CODE := l_ctc_rec.JTOT_OBJECT1_CODE;
2808 END IF;
2809 IF (x_ctc_rec.object_version_number = OKC_API.G_MISS_NUM)
2810 THEN
2811 x_ctc_rec.object_version_number := l_ctc_rec.object_version_number;
2812 END IF;
2813 IF (x_ctc_rec.created_by = OKC_API.G_MISS_NUM)
2814 THEN
2815 x_ctc_rec.created_by := l_ctc_rec.created_by;
2816 END IF;
2817 IF (x_ctc_rec.creation_date = OKC_API.G_MISS_DATE)
2818 THEN
2819 x_ctc_rec.creation_date := l_ctc_rec.creation_date;
2820 END IF;
2821 IF (x_ctc_rec.last_updated_by = OKC_API.G_MISS_NUM)
2822 THEN
2823 x_ctc_rec.last_updated_by := l_ctc_rec.last_updated_by;
2824 END IF;
2825 IF (x_ctc_rec.last_update_date = OKC_API.G_MISS_DATE)
2826 THEN
2827 x_ctc_rec.last_update_date := l_ctc_rec.last_update_date;
2828 END IF;
2829 IF (x_ctc_rec.contact_sequence = OKC_API.G_MISS_NUM)
2830 THEN
2831 x_ctc_rec.contact_sequence := l_ctc_rec.contact_sequence;
2832 END IF;
2833 IF (x_ctc_rec.last_update_login = OKC_API.G_MISS_NUM)
2834 THEN
2835 x_ctc_rec.last_update_login := l_ctc_rec.last_update_login;
2836 END IF;
2837 IF (x_ctc_rec.primary_yn = OKC_API.G_MISS_CHAR)
2838 THEN
2839 x_ctc_rec.primary_yn := l_ctc_rec.primary_yn;
2840 END IF;
2841 --
2842 IF (x_ctc_rec.resource_class = OKC_API.G_MISS_CHAR)
2843 THEN
2844 x_ctc_rec.resource_class := l_ctc_rec.resource_class;
2845 END IF;
2846 --
2847 IF (x_ctc_rec.SALES_GROUP_ID = OKC_API.G_MISS_NUM)
2848 THEN
2849 x_ctc_rec.SALES_GROUP_ID :=l_ctc_rec.SALES_GROUP_ID;
2850 END IF;
2851 --
2852 IF (x_ctc_rec.attribute_category = OKC_API.G_MISS_CHAR)
2853 THEN
2854 x_ctc_rec.attribute_category := l_ctc_rec.attribute_category;
2855 END IF;
2856 IF (x_ctc_rec.attribute1 = OKC_API.G_MISS_CHAR)
2857 THEN
2858 x_ctc_rec.attribute1 := l_ctc_rec.attribute1;
2859 END IF;
2860 IF (x_ctc_rec.attribute2 = OKC_API.G_MISS_CHAR)
2861 THEN
2862 x_ctc_rec.attribute2 := l_ctc_rec.attribute2;
2863 END IF;
2864 IF (x_ctc_rec.attribute3 = OKC_API.G_MISS_CHAR)
2865 THEN
2866 x_ctc_rec.attribute3 := l_ctc_rec.attribute3;
2867 END IF;
2868 IF (x_ctc_rec.attribute4 = OKC_API.G_MISS_CHAR)
2869 THEN
2870 x_ctc_rec.attribute4 := l_ctc_rec.attribute4;
2871 END IF;
2872 IF (x_ctc_rec.attribute5 = OKC_API.G_MISS_CHAR)
2873 THEN
2874 x_ctc_rec.attribute5 := l_ctc_rec.attribute5;
2875 END IF;
2876 IF (x_ctc_rec.attribute6 = OKC_API.G_MISS_CHAR)
2877 THEN
2878 x_ctc_rec.attribute6 := l_ctc_rec.attribute6;
2879 END IF;
2880 IF (x_ctc_rec.attribute7 = OKC_API.G_MISS_CHAR)
2881 THEN
2882 x_ctc_rec.attribute7 := l_ctc_rec.attribute7;
2883 END IF;
2884 IF (x_ctc_rec.attribute8 = OKC_API.G_MISS_CHAR)
2885 THEN
2886 x_ctc_rec.attribute8 := l_ctc_rec.attribute8;
2887 END IF;
2888 IF (x_ctc_rec.attribute9 = OKC_API.G_MISS_CHAR)
2889 THEN
2890 x_ctc_rec.attribute9 := l_ctc_rec.attribute9;
2891 END IF;
2892 IF (x_ctc_rec.attribute10 = OKC_API.G_MISS_CHAR)
2893 THEN
2894 x_ctc_rec.attribute10 := l_ctc_rec.attribute10;
2895 END IF;
2896 IF (x_ctc_rec.attribute11 = OKC_API.G_MISS_CHAR)
2897 THEN
2898 x_ctc_rec.attribute11 := l_ctc_rec.attribute11;
2899 END IF;
2900 IF (x_ctc_rec.attribute12 = OKC_API.G_MISS_CHAR)
2901 THEN
2902 x_ctc_rec.attribute12 := l_ctc_rec.attribute12;
2903 END IF;
2904 IF (x_ctc_rec.attribute13 = OKC_API.G_MISS_CHAR)
2905 THEN
2906 x_ctc_rec.attribute13 := l_ctc_rec.attribute13;
2907 END IF;
2908 IF (x_ctc_rec.attribute14 = OKC_API.G_MISS_CHAR)
2909 THEN
2910 x_ctc_rec.attribute14 := l_ctc_rec.attribute14;
2911 END IF;
2912 IF (x_ctc_rec.attribute15 = OKC_API.G_MISS_CHAR)
2913 THEN
2914 x_ctc_rec.attribute15 := l_ctc_rec.attribute15;
2915 END IF;
2916
2917 IF (x_ctc_rec.start_date = OKC_API.G_MISS_DATE)
2918 THEN
2919 x_ctc_rec.start_date := l_ctc_rec.start_date;
2920 END IF;
2921
2922 IF (x_ctc_rec.end_date = OKC_API.G_MISS_DATE)
2923 THEN
2924 x_ctc_rec.end_date := l_ctc_rec.end_date;
2925 END IF;
2926
2927 IF (l_debug = 'Y') THEN
2928 okc_debug.log('9250: Leaving populate_new_record ', 2);
2929 okc_debug.Reset_Indentation;
2930 END IF;
2931
2932 RETURN(l_return_status);
2933
2934 END populate_new_record;
2935 -------------------------------------
2936 -- Set_Attributes for:OKC_CONTACTS --
2937 -------------------------------------
2938 FUNCTION Set_Attributes (
2939 p_ctc_rec IN ctc_rec_type,
2940 x_ctc_rec OUT NOCOPY ctc_rec_type
2941 ) RETURN VARCHAR2 IS
2942 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2943 BEGIN
2944
2945 x_ctc_rec := p_ctc_rec;
2946
2947 RETURN(l_return_status);
2948
2949 END Set_Attributes;
2950 BEGIN
2951
2952 IF (l_debug = 'Y') THEN
2953 okc_debug.Set_Indentation('OKC_CTC_PVT');
2954 okc_debug.log('9400: Entered update_row', 2);
2955 END IF;
2956
2957 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2958 p_init_msg_list,
2959 '_PVT',
2960 x_return_status);
2961 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2962 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2963 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2964 RAISE OKC_API.G_EXCEPTION_ERROR;
2965 END IF;
2966 --- Setting item attributes
2967 l_return_status := Set_Attributes(
2968 p_ctc_rec, -- IN
2969 l_ctc_rec); -- OUT
2970 --- If any errors happen abort API
2971 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2972 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2973 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2974 RAISE OKC_API.G_EXCEPTION_ERROR;
2975 END IF;
2976 l_return_status := populate_new_record(l_ctc_rec, l_def_ctc_rec);
2977 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2978 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2979 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2980 RAISE OKC_API.G_EXCEPTION_ERROR;
2981 END IF;
2982 /*bugfix for 6882512*/
2983 OPEN get_k_status(l_def_ctc_rec.dnz_chr_id);
2984 FETCH get_k_status INTO l_ste_code;
2985 CLOSE get_k_status;
2986 /*bugfix for 6882512*/
2987 UPDATE OKC_CONTACTS
2988 SET CPL_ID = l_def_ctc_rec.cpl_id,
2989 CRO_CODE = l_def_ctc_rec.cro_code,
2990 DNZ_CHR_ID = l_def_ctc_rec.dnz_chr_id,
2991 OBJECT1_ID1 = l_def_ctc_rec.object1_id1,
2992 OBJECT1_ID2 = l_def_ctc_rec.object1_id2,
2993 JTOT_OBJECT1_CODE = l_def_ctc_rec.JTOT_OBJECT1_CODE,
2994 OBJECT_VERSION_NUMBER = l_def_ctc_rec.object_version_number,
2995 CREATED_BY = l_def_ctc_rec.created_by,
2996 CREATION_DATE = l_def_ctc_rec.creation_date,
2997 LAST_UPDATED_BY = l_def_ctc_rec.last_updated_by,
2998 LAST_UPDATE_DATE = l_def_ctc_rec.last_update_date,
2999 CONTACT_SEQUENCE = l_def_ctc_rec.contact_sequence,
3000 LAST_UPDATE_LOGIN = l_def_ctc_rec.last_update_login,
3001 PRIMARY_YN = l_def_ctc_rec.primary_yn,
3002 RESOURCE_CLASS = l_def_ctc_rec.RESOURCE_CLASS,
3003 SALES_GROUP_ID = l_def_ctc_rec.SALES_GROUP_ID,
3004 ATTRIBUTE_CATEGORY = l_def_ctc_rec.attribute_category,
3005 ATTRIBUTE1 = l_def_ctc_rec.attribute1,
3006 ATTRIBUTE2 = l_def_ctc_rec.attribute2,
3007 ATTRIBUTE3 = l_def_ctc_rec.attribute3,
3008 ATTRIBUTE4 = l_def_ctc_rec.attribute4,
3009 ATTRIBUTE5 = l_def_ctc_rec.attribute5,
3010 ATTRIBUTE6 = l_def_ctc_rec.attribute6,
3011 ATTRIBUTE7 = l_def_ctc_rec.attribute7,
3012 ATTRIBUTE8 = l_def_ctc_rec.attribute8,
3013 ATTRIBUTE9 = l_def_ctc_rec.attribute9,
3014 ATTRIBUTE10 = l_def_ctc_rec.attribute10,
3015 ATTRIBUTE11 = l_def_ctc_rec.attribute11,
3016 ATTRIBUTE12 = l_def_ctc_rec.attribute12,
3017 ATTRIBUTE13 = l_def_ctc_rec.attribute13,
3018 ATTRIBUTE14 = l_def_ctc_rec.attribute14,
3019 ATTRIBUTE15 = l_def_ctc_rec.attribute15,
3020 START_DATE = l_def_ctc_rec.start_date,
3021 END_DATE = l_def_ctc_rec.end_date,
3022 DNZ_STE_CODE = l_ste_code
3023 WHERE ID = l_def_ctc_rec.id;
3024
3025 x_ctc_rec := l_def_ctc_rec;
3026 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3027
3028 IF (l_debug = 'Y') THEN
3029 okc_debug.log('9500: Leaving update_row', 2);
3030 okc_debug.Reset_Indentation;
3031 END IF;
3032
3033 EXCEPTION
3034 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3035
3036 IF (l_debug = 'Y') THEN
3037 okc_debug.log('9600: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3038 okc_debug.Reset_Indentation;
3039 END IF;
3040
3041 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3042 (
3043 l_api_name,
3044 G_PKG_NAME,
3045 'OKC_API.G_RET_STS_ERROR',
3046 x_msg_count,
3047 x_msg_data,
3048 '_PVT'
3049 );
3050 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3051
3052 IF (l_debug = 'Y') THEN
3053 okc_debug.log('9700: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3054 okc_debug.Reset_Indentation;
3055 END IF;
3056
3057 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3058 (
3059 l_api_name,
3060 G_PKG_NAME,
3061 'OKC_API.G_RET_STS_UNEXP_ERROR',
3062 x_msg_count,
3063 x_msg_data,
3064 '_PVT'
3065 );
3066 WHEN OTHERS THEN
3067
3068 IF (l_debug = 'Y') THEN
3069 okc_debug.log('9800: Exiting update_row:OTHERS Exception', 2);
3070 okc_debug.Reset_Indentation;
3071 END IF;
3072
3073 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3074 (
3075 l_api_name,
3076 G_PKG_NAME,
3077 'OTHERS',
3078 x_msg_count,
3079 x_msg_data,
3080 '_PVT'
3081 );
3082
3083 END update_row;
3084 -----------------------------------
3085 -- update_row for:OKC_CONTACTS_V --
3086 -----------------------------------
3087 PROCEDURE update_row(
3088 p_api_version IN NUMBER,
3089 p_init_msg_list IN VARCHAR2 ,
3090 x_return_status OUT NOCOPY VARCHAR2,
3091 x_msg_count OUT NOCOPY NUMBER,
3092 x_msg_data OUT NOCOPY VARCHAR2,
3093 p_ctcv_rec IN ctcv_rec_type,
3094 x_ctcv_rec OUT NOCOPY ctcv_rec_type) IS
3095
3096 l_api_version CONSTANT NUMBER := 1;
3097 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3098 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3099 l_ctcv_rec ctcv_rec_type := p_ctcv_rec;
3100 l_def_ctcv_rec ctcv_rec_type;
3101 l_ctc_rec ctc_rec_type;
3102 lx_ctc_rec ctc_rec_type;
3103 -------------------------------
3104 -- FUNCTION fill_who_columns --
3105 -------------------------------
3106 FUNCTION fill_who_columns (
3107 p_ctcv_rec IN ctcv_rec_type
3108 ) RETURN ctcv_rec_type IS
3109 l_ctcv_rec ctcv_rec_type := p_ctcv_rec;
3110 BEGIN
3111
3112 l_ctcv_rec.LAST_UPDATE_DATE := SYSDATE;
3113 l_ctcv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3114 l_ctcv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3115
3116 RETURN(l_ctcv_rec);
3117
3118 END fill_who_columns;
3119 ----------------------------------
3120 -- FUNCTION populate_new_record --
3121 ----------------------------------
3122 FUNCTION populate_new_record (
3123 p_ctcv_rec IN ctcv_rec_type,
3124 x_ctcv_rec OUT NOCOPY ctcv_rec_type
3125 ) RETURN VARCHAR2 IS
3126 l_ctcv_rec ctcv_rec_type;
3127 l_row_notfound BOOLEAN := TRUE;
3128 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3129 BEGIN
3130
3131 IF (l_debug = 'Y') THEN
3132 okc_debug.Set_Indentation('OKC_CTC_PVT');
3133 okc_debug.log('10000: Entered populate_new_record', 2);
3134 END IF;
3135
3136 x_ctcv_rec := p_ctcv_rec;
3137 -- Get current database values
3138 l_ctcv_rec := get_rec(p_ctcv_rec, l_row_notfound);
3139 IF (l_row_notfound) THEN
3140 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3141 END IF;
3142 IF (x_ctcv_rec.id = OKC_API.G_MISS_NUM)
3143 THEN
3144 x_ctcv_rec.id := l_ctcv_rec.id;
3145 END IF;
3146 IF (x_ctcv_rec.object_version_number = OKC_API.G_MISS_NUM)
3147 THEN
3148 x_ctcv_rec.object_version_number := l_ctcv_rec.object_version_number;
3149 END IF;
3150 IF (x_ctcv_rec.cpl_id = OKC_API.G_MISS_NUM)
3151 THEN
3152 x_ctcv_rec.cpl_id := l_ctcv_rec.cpl_id;
3153 END IF;
3154 IF (x_ctcv_rec.cro_code = OKC_API.G_MISS_CHAR)
3155 THEN
3156 x_ctcv_rec.cro_code := l_ctcv_rec.cro_code;
3157 END IF;
3158 IF (x_ctcv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
3159 THEN
3160 x_ctcv_rec.dnz_chr_id := l_ctcv_rec.dnz_chr_id;
3161 END IF;
3162 IF (x_ctcv_rec.contact_sequence = OKC_API.G_MISS_NUM)
3163 THEN
3164 x_ctcv_rec.contact_sequence := l_ctcv_rec.contact_sequence;
3165 END IF;
3166 IF (x_ctcv_rec.object1_id1 = OKC_API.G_MISS_CHAR)
3167 THEN
3168 x_ctcv_rec.object1_id1 := l_ctcv_rec.object1_id1;
3169 END IF;
3170 IF (x_ctcv_rec.object1_id2 = OKC_API.G_MISS_CHAR)
3171 THEN
3172 x_ctcv_rec.object1_id2 := l_ctcv_rec.object1_id2;
3173 END IF;
3174 IF (x_ctcv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
3175 THEN
3176 x_ctcv_rec.JTOT_OBJECT1_CODE := l_ctcv_rec.JTOT_OBJECT1_CODE;
3177 END IF;
3178 IF (x_ctcv_rec.primary_yn = OKC_API.G_MISS_CHAR)
3179 THEN
3180 x_ctcv_rec.PRIMARY_YN := l_ctcv_rec.PRIMARY_YN;
3181 END IF;
3182 IF (x_ctcv_rec.RESOURCE_CLASS= OKC_API.G_MISS_CHAR)
3183 THEN
3184 x_ctcv_rec.RESOURCE_CLASS := l_ctcv_rec.RESOURCE_CLASS;
3185 END IF;
3186 --
3187 IF (x_ctcv_rec.SALES_GROUP_ID = OKC_API.G_MISS_NUM) THEN
3188 x_ctcv_rec.SALES_GROUP_ID := l_ctcv_rec.SALES_GROUP_ID;
3189 END IF;
3190 --
3191 IF (x_ctcv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3192 THEN
3193 x_ctcv_rec.attribute_category := l_ctcv_rec.attribute_category;
3194 END IF;
3195 IF (x_ctcv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3196 THEN
3197 x_ctcv_rec.attribute1 := l_ctcv_rec.attribute1;
3198 END IF;
3199 IF (x_ctcv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3200 THEN
3201 x_ctcv_rec.attribute2 := l_ctcv_rec.attribute2;
3202 END IF;
3203 IF (x_ctcv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3204 THEN
3205 x_ctcv_rec.attribute3 := l_ctcv_rec.attribute3;
3206 END IF;
3207 IF (x_ctcv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3208 THEN
3209 x_ctcv_rec.attribute4 := l_ctcv_rec.attribute4;
3210 END IF;
3211 IF (x_ctcv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3212 THEN
3213 x_ctcv_rec.attribute5 := l_ctcv_rec.attribute5;
3214 END IF;
3215 IF (x_ctcv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3216 THEN
3217 x_ctcv_rec.attribute6 := l_ctcv_rec.attribute6;
3218 END IF;
3219 IF (x_ctcv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3220 THEN
3221 x_ctcv_rec.attribute7 := l_ctcv_rec.attribute7;
3222 END IF;
3223 IF (x_ctcv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3224 THEN
3225 x_ctcv_rec.attribute8 := l_ctcv_rec.attribute8;
3226 END IF;
3227 IF (x_ctcv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3228 THEN
3229 x_ctcv_rec.attribute9 := l_ctcv_rec.attribute9;
3230 END IF;
3231 IF (x_ctcv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3232 THEN
3233 x_ctcv_rec.attribute10 := l_ctcv_rec.attribute10;
3234 END IF;
3235 IF (x_ctcv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3236 THEN
3237 x_ctcv_rec.attribute11 := l_ctcv_rec.attribute11;
3238 END IF;
3239 IF (x_ctcv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3240 THEN
3241 x_ctcv_rec.attribute12 := l_ctcv_rec.attribute12;
3242 END IF;
3243 IF (x_ctcv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3244 THEN
3245 x_ctcv_rec.attribute13 := l_ctcv_rec.attribute13;
3246 END IF;
3247 IF (x_ctcv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3248 THEN
3249 x_ctcv_rec.attribute14 := l_ctcv_rec.attribute14;
3250 END IF;
3251 IF (x_ctcv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3252 THEN
3253 x_ctcv_rec.attribute15 := l_ctcv_rec.attribute15;
3254 END IF;
3255 IF (x_ctcv_rec.created_by = OKC_API.G_MISS_NUM)
3256 THEN
3257 x_ctcv_rec.created_by := l_ctcv_rec.created_by;
3258 END IF;
3259 IF (x_ctcv_rec.creation_date = OKC_API.G_MISS_DATE)
3260 THEN
3261 x_ctcv_rec.creation_date := l_ctcv_rec.creation_date;
3262 END IF;
3263 IF (x_ctcv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3264 THEN
3265 x_ctcv_rec.last_updated_by := l_ctcv_rec.last_updated_by;
3266 END IF;
3267 IF (x_ctcv_rec.last_update_date = OKC_API.G_MISS_DATE)
3268 THEN
3269 x_ctcv_rec.last_update_date := l_ctcv_rec.last_update_date;
3270 END IF;
3271 IF (x_ctcv_rec.last_update_login = OKC_API.G_MISS_NUM)
3272 THEN
3273 x_ctcv_rec.last_update_login := l_ctcv_rec.last_update_login;
3274 END IF;
3275 IF (x_ctcv_rec.start_date = OKC_API.G_MISS_DATE)
3276 THEN
3277 x_ctcv_rec.start_date := l_ctcv_rec.start_date;
3278 END IF;
3279 IF (x_ctcv_rec.end_date = OKC_API.G_MISS_DATE)
3280 THEN
3281 x_ctcv_rec.end_date := l_ctcv_rec.end_date;
3282 END IF;
3283
3284 IF (l_debug = 'Y') THEN
3285 okc_debug.log('10050: Leaving populate_new_record ', 2);
3286 okc_debug.Reset_Indentation;
3287 END IF;
3288
3289 RETURN(l_return_status);
3290
3291 END populate_new_record;
3292 ---------------------------------------
3293 -- Set_Attributes for:OKC_CONTACTS_V --
3294 ---------------------------------------
3295 FUNCTION Set_Attributes (
3296 p_ctcv_rec IN ctcv_rec_type,
3297 x_ctcv_rec OUT NOCOPY ctcv_rec_type
3298 ) RETURN VARCHAR2 IS
3299 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3300 BEGIN
3301
3302 x_ctcv_rec := p_ctcv_rec;
3303 x_ctcv_rec.OBJECT_VERSION_NUMBER := NVL(x_ctcv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3304
3305 RETURN(l_return_status);
3306
3307 END Set_Attributes;
3308 BEGIN
3309
3310 IF (l_debug = 'Y') THEN
3311 okc_debug.Set_Indentation('OKC_CTC_PVT');
3312 okc_debug.log('10200: Entered update_row', 2);
3313 END IF;
3314
3315 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3316 G_PKG_NAME,
3317 p_init_msg_list,
3318 l_api_version,
3319 p_api_version,
3320 '_PVT',
3321 x_return_status);
3322 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3323 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3324 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3325 RAISE OKC_API.G_EXCEPTION_ERROR;
3326 END IF;
3327 --- Setting item attributes
3328 l_return_status := Set_Attributes(
3329 p_ctcv_rec, -- IN
3330 l_ctcv_rec); -- OUT
3331 --- If any errors happen abort API
3332 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3333 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3334 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3335 RAISE OKC_API.G_EXCEPTION_ERROR;
3336 END IF;
3337 l_return_status := populate_new_record(l_ctcv_rec, l_def_ctcv_rec);
3338 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3339 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3340 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3341 RAISE OKC_API.G_EXCEPTION_ERROR;
3342 END IF;
3343 l_def_ctcv_rec := fill_who_columns(l_def_ctcv_rec);
3344 --- Validate all non-missing attributes (Item Level Validation)
3345 l_return_status := Validate_Attributes(l_def_ctcv_rec);
3346 --- If any errors happen abort API
3347 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3348 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3349 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3350 RAISE OKC_API.G_EXCEPTION_ERROR;
3351 END IF;
3352 l_return_status := Validate_Record(l_def_ctcv_rec);
3353 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3354 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3355 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3356 RAISE OKC_API.G_EXCEPTION_ERROR;
3357 END IF;
3358
3359 --------------------------------------
3360 -- Move VIEW record to "Child" records
3361 --------------------------------------
3362 migrate(l_def_ctcv_rec, l_ctc_rec);
3363 --------------------------------------------
3364 -- Call the UPDATE_ROW for each child record
3365 --------------------------------------------
3366 update_row(
3367 p_init_msg_list,
3368 x_return_status,
3369 x_msg_count,
3370 x_msg_data,
3371 l_ctc_rec,
3372 lx_ctc_rec
3373 );
3374 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3375 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3376 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3377 RAISE OKC_API.G_EXCEPTION_ERROR;
3378 END IF;
3379 migrate(lx_ctc_rec, l_def_ctcv_rec);
3380 x_ctcv_rec := l_def_ctcv_rec;
3381 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3382
3383 IF (l_debug = 'Y') THEN
3384 okc_debug.log('10300: Leaving update_row', 2);
3385 okc_debug.Reset_Indentation;
3386 END IF;
3387
3388 EXCEPTION
3389 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3390
3391 IF (l_debug = 'Y') THEN
3392 okc_debug.log('10400: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3393 okc_debug.Reset_Indentation;
3394 END IF;
3395
3396 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3397 (
3398 l_api_name,
3399 G_PKG_NAME,
3400 'OKC_API.G_RET_STS_ERROR',
3401 x_msg_count,
3402 x_msg_data,
3403 '_PVT'
3404 );
3405 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3406
3407 IF (l_debug = 'Y') THEN
3408 okc_debug.log('10500: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3409 okc_debug.Reset_Indentation;
3410 END IF;
3411
3412 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3413 (
3414 l_api_name,
3415 G_PKG_NAME,
3416 'OKC_API.G_RET_STS_UNEXP_ERROR',
3417 x_msg_count,
3418 x_msg_data,
3419 '_PVT'
3420 );
3421 WHEN OTHERS THEN
3422
3423 IF (l_debug = 'Y') THEN
3424 okc_debug.log('10600: Exiting update_row:OTHERS Exception', 2);
3425 okc_debug.Reset_Indentation;
3426 END IF;
3427
3428 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3429 (
3430 l_api_name,
3431 G_PKG_NAME,
3432 'OTHERS',
3433 x_msg_count,
3434 x_msg_data,
3435 '_PVT'
3436 );
3437
3438 END update_row;
3439 ----------------------------------------
3440 -- PL/SQL TBL update_row for:CTCV_TBL --
3441 ----------------------------------------
3442 PROCEDURE update_row(
3443 p_api_version IN NUMBER,
3444 p_init_msg_list IN VARCHAR2 ,
3445 x_return_status OUT NOCOPY VARCHAR2,
3446 x_msg_count OUT NOCOPY NUMBER,
3447 x_msg_data OUT NOCOPY VARCHAR2,
3448 p_ctcv_tbl IN ctcv_tbl_type,
3449 x_ctcv_tbl OUT NOCOPY ctcv_tbl_type) IS
3450
3451 l_api_version CONSTANT NUMBER := 1;
3452 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3453 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3454 i NUMBER := 0;
3455 BEGIN
3456
3457 IF (l_debug = 'Y') THEN
3458 okc_debug.Set_Indentation('OKC_CTC_PVT');
3459 okc_debug.log('10700: Entered update_row', 2);
3460 END IF;
3461
3462 OKC_API.init_msg_list(p_init_msg_list);
3463 -- Make sure PL/SQL table has records in it before passing
3464 IF (p_ctcv_tbl.COUNT > 0) THEN
3465 i := p_ctcv_tbl.FIRST;
3466 LOOP
3467 update_row (
3468 p_api_version => p_api_version,
3469 p_init_msg_list => OKC_API.G_FALSE,
3470 x_return_status => x_return_status,
3471 x_msg_count => x_msg_count,
3472 x_msg_data => x_msg_data,
3473 p_ctcv_rec => p_ctcv_tbl(i),
3474 x_ctcv_rec => x_ctcv_tbl(i));
3475 EXIT WHEN (i = p_ctcv_tbl.LAST);
3476 i := p_ctcv_tbl.NEXT(i);
3477 END LOOP;
3478 END IF;
3479
3480 IF (l_debug = 'Y') THEN
3481 okc_debug.log('10800: Leaving update_row', 2);
3482 okc_debug.Reset_Indentation;
3483 END IF;
3484
3485 EXCEPTION
3486 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3487
3488 IF (l_debug = 'Y') THEN
3489 okc_debug.log('10900: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3490 okc_debug.Reset_Indentation;
3491 END IF;
3492
3493 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3494 (
3495 l_api_name,
3496 G_PKG_NAME,
3497 'OKC_API.G_RET_STS_ERROR',
3498 x_msg_count,
3499 x_msg_data,
3500 '_PVT'
3501 );
3502 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3503
3504 IF (l_debug = 'Y') THEN
3505 okc_debug.log('11000: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3506 okc_debug.Reset_Indentation;
3507 END IF;
3508
3509 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3510 (
3511 l_api_name,
3512 G_PKG_NAME,
3513 'OKC_API.G_RET_STS_UNEXP_ERROR',
3514 x_msg_count,
3515 x_msg_data,
3516 '_PVT'
3517 );
3518 WHEN OTHERS THEN
3519
3520 IF (l_debug = 'Y') THEN
3521 okc_debug.log('11100: Exiting update_row:OTHERS Exception', 2);
3522 okc_debug.Reset_Indentation;
3523 END IF;
3524
3525 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3526 (
3527 l_api_name,
3528 G_PKG_NAME,
3529 'OTHERS',
3530 x_msg_count,
3531 x_msg_data,
3532 '_PVT'
3533 );
3534
3535 END update_row;
3536
3537 ---------------------------------------------------------------------------
3538 -- PROCEDURE delete_row
3539 ---------------------------------------------------------------------------
3540 ---------------------------------
3541 -- delete_row for:OKC_CONTACTS --
3542 ---------------------------------
3543 PROCEDURE delete_row(
3544 p_init_msg_list IN VARCHAR2 ,
3545 x_return_status OUT NOCOPY VARCHAR2,
3546 x_msg_count OUT NOCOPY NUMBER,
3547 x_msg_data OUT NOCOPY VARCHAR2,
3548 p_ctc_rec IN ctc_rec_type) IS
3549
3550 l_api_version CONSTANT NUMBER := 1;
3551 l_api_name CONSTANT VARCHAR2(30) := 'CONTACTS_delete_row';
3552 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3553 l_ctc_rec ctc_rec_type:= p_ctc_rec;
3554 l_row_notfound BOOLEAN := TRUE;
3555 BEGIN
3556
3557 IF (l_debug = 'Y') THEN
3558 okc_debug.Set_Indentation('OKC_CTC_PVT');
3559 okc_debug.log('11200: Entered delete_row', 2);
3560 END IF;
3561
3562 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3563 p_init_msg_list,
3564 '_PVT',
3565 x_return_status);
3566 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3567 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3568 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3569 RAISE OKC_API.G_EXCEPTION_ERROR;
3570 END IF;
3571 DELETE FROM OKC_CONTACTS
3572 WHERE ID = l_ctc_rec.id;
3573
3574 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3575
3576 IF (l_debug = 'Y') THEN
3577 okc_debug.log('11300: Leaving delete_row', 2);
3578 okc_debug.Reset_Indentation;
3579 END IF;
3580
3581 EXCEPTION
3582 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3583
3584 IF (l_debug = 'Y') THEN
3585 okc_debug.log('11400: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3586 okc_debug.Reset_Indentation;
3587 END IF;
3588
3589 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3590 (
3591 l_api_name,
3592 G_PKG_NAME,
3593 'OKC_API.G_RET_STS_ERROR',
3594 x_msg_count,
3595 x_msg_data,
3596 '_PVT'
3597 );
3598 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3599
3600 IF (l_debug = 'Y') THEN
3601 okc_debug.log('11500: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3602 okc_debug.Reset_Indentation;
3603 END IF;
3604
3605 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3606 (
3607 l_api_name,
3608 G_PKG_NAME,
3609 'OKC_API.G_RET_STS_UNEXP_ERROR',
3610 x_msg_count,
3611 x_msg_data,
3612 '_PVT'
3613 );
3614 WHEN OTHERS THEN
3615
3616 IF (l_debug = 'Y') THEN
3617 okc_debug.log('11600: Exiting delete_row:OTHERS Exception', 2);
3618 okc_debug.Reset_Indentation;
3619 END IF;
3620
3621 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3622 (
3623 l_api_name,
3624 G_PKG_NAME,
3625 'OTHERS',
3626 x_msg_count,
3627 x_msg_data,
3628 '_PVT'
3629 );
3630
3631 END delete_row;
3632 -----------------------------------
3633 -- delete_row for:OKC_CONTACTS_V --
3634 -----------------------------------
3635 PROCEDURE delete_row(
3636 p_api_version IN NUMBER,
3637 p_init_msg_list IN VARCHAR2 ,
3638 x_return_status OUT NOCOPY VARCHAR2,
3639 x_msg_count OUT NOCOPY NUMBER,
3640 x_msg_data OUT NOCOPY VARCHAR2,
3641 p_ctcv_rec IN ctcv_rec_type) IS
3642
3643 l_api_version CONSTANT NUMBER := 1;
3644 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3645 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3646 l_ctcv_rec ctcv_rec_type := p_ctcv_rec;
3647 l_ctc_rec ctc_rec_type;
3648 BEGIN
3649
3650 IF (l_debug = 'Y') THEN
3651 okc_debug.Set_Indentation('OKC_CTC_PVT');
3652 okc_debug.log('11700: Entered delete_row', 2);
3653 END IF;
3654
3655 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3656 G_PKG_NAME,
3657 p_init_msg_list,
3658 l_api_version,
3659 p_api_version,
3660 '_PVT',
3661 x_return_status);
3662 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3663 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3664 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3665 RAISE OKC_API.G_EXCEPTION_ERROR;
3666 END IF;
3667 --------------------------------------
3668 -- Move VIEW record to "Child" records
3669 --------------------------------------
3670 migrate(l_ctcv_rec, l_ctc_rec);
3671 --------------------------------------------
3672 -- Call the DELETE_ROW for each child record
3673 --------------------------------------------
3674 delete_row(
3675 p_init_msg_list,
3676 x_return_status,
3677 x_msg_count,
3678 x_msg_data,
3679 l_ctc_rec
3680 );
3681 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3682 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3683 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3684 RAISE OKC_API.G_EXCEPTION_ERROR;
3685 END IF;
3686 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3687
3688 IF (l_debug = 'Y') THEN
3689 okc_debug.log('11800: Leaving delete_row', 2);
3690 okc_debug.Reset_Indentation;
3691 END IF;
3692
3693 EXCEPTION
3694 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3695
3696 IF (l_debug = 'Y') THEN
3697 okc_debug.log('11900: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3698 okc_debug.Reset_Indentation;
3699 END IF;
3700
3701 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3702 (
3703 l_api_name,
3704 G_PKG_NAME,
3705 'OKC_API.G_RET_STS_ERROR',
3706 x_msg_count,
3707 x_msg_data,
3708 '_PVT'
3709 );
3710 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3711
3712 IF (l_debug = 'Y') THEN
3713 okc_debug.log('12000: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3714 okc_debug.Reset_Indentation;
3715 END IF;
3716
3717 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3718 (
3719 l_api_name,
3720 G_PKG_NAME,
3721 'OKC_API.G_RET_STS_UNEXP_ERROR',
3722 x_msg_count,
3723 x_msg_data,
3724 '_PVT'
3725 );
3726 WHEN OTHERS THEN
3727
3728 IF (l_debug = 'Y') THEN
3729 okc_debug.log('12100: Exiting delete_row:OTHERS Exception', 2);
3730 okc_debug.Reset_Indentation;
3731 END IF;
3732
3733 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3734 (
3735 l_api_name,
3736 G_PKG_NAME,
3737 'OTHERS',
3738 x_msg_count,
3739 x_msg_data,
3740 '_PVT'
3741 );
3742
3743 END delete_row;
3744 ----------------------------------------
3745 -- PL/SQL TBL delete_row for:CTCV_TBL --
3746 ----------------------------------------
3747 PROCEDURE delete_row(
3748 p_api_version IN NUMBER,
3749 p_init_msg_list IN VARCHAR2 ,
3750 x_return_status OUT NOCOPY VARCHAR2,
3751 x_msg_count OUT NOCOPY NUMBER,
3752 x_msg_data OUT NOCOPY VARCHAR2,
3753 p_ctcv_tbl IN ctcv_tbl_type) IS
3754
3755 l_api_version CONSTANT NUMBER := 1;
3756 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3757 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3758 i NUMBER := 0;
3759 BEGIN
3760
3761 IF (l_debug = 'Y') THEN
3762 okc_debug.Set_Indentation('OKC_CTC_PVT');
3763 okc_debug.log('12200: Entered delete_row', 2);
3764 END IF;
3765
3766 OKC_API.init_msg_list(p_init_msg_list);
3767 -- Make sure PL/SQL table has records in it before passing
3768 IF (p_ctcv_tbl.COUNT > 0) THEN
3769 i := p_ctcv_tbl.FIRST;
3770 LOOP
3771 delete_row (
3772 p_api_version => p_api_version,
3773 p_init_msg_list => OKC_API.G_FALSE,
3774 x_return_status => x_return_status,
3775 x_msg_count => x_msg_count,
3776 x_msg_data => x_msg_data,
3777 p_ctcv_rec => p_ctcv_tbl(i));
3778 EXIT WHEN (i = p_ctcv_tbl.LAST);
3779 i := p_ctcv_tbl.NEXT(i);
3780 END LOOP;
3781 END IF;
3782
3783 IF (l_debug = 'Y') THEN
3784 okc_debug.log('12300: Exiting delete_row', 2);
3785 okc_debug.Reset_Indentation;
3786 END IF;
3787
3788 EXCEPTION
3789 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3790
3791 IF (l_debug = 'Y') THEN
3792 okc_debug.log('12400: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3793 okc_debug.Reset_Indentation;
3794 END IF;
3795
3796 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3797 (
3798 l_api_name,
3799 G_PKG_NAME,
3800 'OKC_API.G_RET_STS_ERROR',
3801 x_msg_count,
3802 x_msg_data,
3803 '_PVT'
3804 );
3805 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3806
3807 IF (l_debug = 'Y') THEN
3808 okc_debug.log('12500: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3809 okc_debug.Reset_Indentation;
3810 END IF;
3811
3812 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3813 (
3814 l_api_name,
3815 G_PKG_NAME,
3816 'OKC_API.G_RET_STS_UNEXP_ERROR',
3817 x_msg_count,
3818 x_msg_data,
3819 '_PVT'
3820 );
3821 WHEN OTHERS THEN
3822
3823 IF (l_debug = 'Y') THEN
3824 okc_debug.log('12600: Exiting delete_row:OTHERS Exception', 2);
3825 okc_debug.Reset_Indentation;
3826 END IF;
3827
3828 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3829 (
3830 l_api_name,
3831 G_PKG_NAME,
3832 'OTHERS',
3833 x_msg_count,
3834 x_msg_data,
3835 '_PVT'
3836 );
3837
3838 END delete_row;
3839
3840 ---------------------------------------------------------------
3841 -- Procedure for mass insert in OKC_CONTACTS _B and TL tables
3842 ---------------------------------------------------------------
3843 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_ctcv_tbl ctcv_tbl_type) IS
3844 l_tabsize NUMBER := p_ctcv_tbl.COUNT;
3845 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
3846 l_ste_code VARCHAR2(30);/*added for bug 6882512*/
3847 in_id OKC_DATATYPES.NumberTabTyp;
3848 in_object_version_number OKC_DATATYPES.NumberTabTyp;
3849 in_cpl_id OKC_DATATYPES.NumberTabTyp;
3850 in_cro_code OKC_DATATYPES.Var30TabTyp;
3851 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
3852 in_contact_sequence OKC_DATATYPES.NumberTabTyp;
3853 in_object1_id1 OKC_DATATYPES.Var40TabTyp;
3854 in_object1_id2 OKC_DATATYPES.Var200TabTyp;
3855 in_jtot_object1_code OKC_DATATYPES.Var30TabTyp;
3856 in_primary_yn OKC_DATATYPES.Var3TabTyp;
3857 in_resource_class OKC_DATATYPES.Var30TabTyp;
3858 in_SALES_GROUP_ID OKC_DATATYPES.NumberTabTyp;
3859 in_attribute_category OKC_DATATYPES.Var90TabTyp;
3860 in_attribute1 OKC_DATATYPES.Var450TabTyp;
3861 in_attribute2 OKC_DATATYPES.Var450TabTyp;
3862 in_attribute3 OKC_DATATYPES.Var450TabTyp;
3863 in_attribute4 OKC_DATATYPES.Var450TabTyp;
3864 in_attribute5 OKC_DATATYPES.Var450TabTyp;
3865 in_attribute6 OKC_DATATYPES.Var450TabTyp;
3866 in_attribute7 OKC_DATATYPES.Var450TabTyp;
3867 in_attribute8 OKC_DATATYPES.Var450TabTyp;
3868 in_attribute9 OKC_DATATYPES.Var450TabTyp;
3869 in_attribute10 OKC_DATATYPES.Var450TabTyp;
3870 in_attribute11 OKC_DATATYPES.Var450TabTyp;
3871 in_attribute12 OKC_DATATYPES.Var450TabTyp;
3872 in_attribute13 OKC_DATATYPES.Var450TabTyp;
3873 in_attribute14 OKC_DATATYPES.Var450TabTyp;
3874 in_attribute15 OKC_DATATYPES.Var450TabTyp;
3875 in_created_by OKC_DATATYPES.NumberTabTyp;
3876 in_creation_date OKC_DATATYPES.DateTabTyp;
3877 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
3878 in_last_update_date OKC_DATATYPES.DateTabTyp;
3879 in_last_update_login OKC_DATATYPES.NumberTabTyp;
3880 in_start_date OKC_DATATYPES.DateTabTyp;
3881 in_end_date OKC_DATATYPES.DateTabTyp;
3882 in_dnz_ste_code OKC_DATATYPES.Var30TabTyp;
3883 i number;
3884 j number;
3885 /*added for bug 6882512*/
3886 CURSOR get_k_status(p_chr_id IN NUMBER) IS
3887 SELECT ste_code
3888 FROM okc_k_headers_all_b KH,okc_statuses_b STB
3889 WHERE KH.sts_code = STB.code
3890 AND id= p_chr_id;
3891 /*added for bug 6882512*/
3892
3893 BEGIN
3894 -- initialize return status
3895 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3896
3897 IF (l_debug = 'Y') THEN
3898 okc_debug.Set_Indentation('OKC_CTC_PVT');
3899 okc_debug.log('12700: Entered INSERT_ROW_UPG', 2);
3900 END IF;
3901
3902 i := p_ctcv_tbl.FIRST; j:=0;
3903 while i is not null
3904 LOOP
3905 j:=j+1;
3906 in_id (j) := p_ctcv_tbl(i).id;
3907 in_object_version_number (j) := p_ctcv_tbl(i).object_version_number;
3908 in_cpl_id (j) := p_ctcv_tbl(i).cpl_id;
3909 in_cro_code (j) := p_ctcv_tbl(i).cro_code;
3910 in_dnz_chr_id (j) := p_ctcv_tbl(i).dnz_chr_id;
3911 in_contact_sequence (j) := p_ctcv_tbl(i).contact_sequence;
3912 in_object1_id1 (j) := p_ctcv_tbl(i).object1_id1;
3913 in_object1_id2 (j) := p_ctcv_tbl(i).object1_id2;
3914 in_jtot_object1_code (j) := p_ctcv_tbl(i).jtot_object1_code;
3915 in_primary_yn (j) := p_ctcv_tbl(i).primary_yn;
3916 in_resource_class (j) := p_ctcv_tbl(i).resource_class;
3917 in_SALES_GROUP_ID (j) := p_ctcv_tbl(i).SALES_GROUP_ID;
3918 in_attribute_category (j) := p_ctcv_tbl(i).attribute_category;
3919 in_attribute1 (j) := p_ctcv_tbl(i).attribute1;
3920 in_attribute2 (j) := p_ctcv_tbl(i).attribute2;
3921 in_attribute3 (j) := p_ctcv_tbl(i).attribute3;
3922 in_attribute4 (j) := p_ctcv_tbl(i).attribute4;
3923 in_attribute5 (j) := p_ctcv_tbl(i).attribute5;
3924 in_attribute6 (j) := p_ctcv_tbl(i).attribute6;
3925 in_attribute7 (j) := p_ctcv_tbl(i).attribute7;
3926 in_attribute8 (j) := p_ctcv_tbl(i).attribute8;
3927 in_attribute9 (j) := p_ctcv_tbl(i).attribute9;
3928 in_attribute10 (j) := p_ctcv_tbl(i).attribute10;
3929 in_attribute11 (j) := p_ctcv_tbl(i).attribute11;
3930 in_attribute12 (j) := p_ctcv_tbl(i).attribute12;
3931 in_attribute13 (j) := p_ctcv_tbl(i).attribute13;
3932 in_attribute14 (j) := p_ctcv_tbl(i).attribute14;
3933 in_attribute15 (j) := p_ctcv_tbl(i).attribute15;
3934 in_created_by (j) := p_ctcv_tbl(i).created_by;
3935 in_creation_date (j) := p_ctcv_tbl(i).creation_date;
3936 in_last_updated_by (j) := p_ctcv_tbl(i).last_updated_by;
3937 in_last_update_date (j) := p_ctcv_tbl(i).last_update_date;
3938 in_last_update_login (j) := p_ctcv_tbl(i).last_update_login;
3939 in_start_date (j) := p_ctcv_tbl(i).start_date;
3940 in_end_date (j) := p_ctcv_tbl(i).end_date;
3941 /*Bugfix for 6882512*/
3942 /*Making sure that the previous ste_code doesnt get copied*/
3943 l_ste_code := NULL;
3944
3945 OPEN get_k_status(in_dnz_chr_id(j));
3946 FETCH get_k_status INTO l_ste_code;
3947 CLOSE get_k_status;
3948 in_dnz_ste_code (j) := l_ste_code;
3949
3950 /*Bugfix for 6882512*/
3951 i:=p_ctcv_tbl.next(i);
3952 END LOOP;
3953
3954 FORALL i in 1..l_tabsize
3955 INSERT
3956 INTO OKC_CONTACTS
3957 (
3958 id,
3959 cpl_id,
3960 cro_code,
3961 dnz_chr_id,
3962 object1_id1,
3963 object1_id2,
3964 jtot_object1_code,
3965 object_version_number,
3966 created_by,
3967 creation_date,
3968 last_updated_by,
3969 last_update_date,
3970 contact_sequence,
3971 last_update_login,
3972 primary_yn,
3973 resource_class,
3974 SALES_GROUP_ID,
3975 attribute_category,
3976 attribute1,
3977 attribute2,
3978 attribute3,
3979 attribute4,
3980 attribute5,
3981 attribute6,
3982 attribute7,
3983 attribute8,
3984 attribute9,
3985 attribute10,
3986 attribute11,
3987 attribute12,
3988 attribute13,
3989 attribute14,
3990 attribute15,
3991 start_date,
3992 end_date,
3993 dnz_ste_code
3994
3995 )
3996 VALUES (
3997 in_id(i),
3998 in_cpl_id(i),
3999 in_cro_code(i),
4000 in_dnz_chr_id(i),
4001 in_object1_id1(i),
4002 in_object1_id2(i),
4003 in_jtot_object1_code(i),
4004 in_object_version_number(i),
4005 in_created_by(i),
4006 in_creation_date(i),
4007 in_last_updated_by(i),
4008 in_last_update_date(i),
4009 in_contact_sequence(i),
4010 in_last_update_login(i),
4011 in_primary_yn(i),
4012 in_resource_class(i),
4013 in_SALES_GROUP_ID(i),
4014 in_attribute_category(i),
4015 in_attribute1(i),
4016 in_attribute2(i),
4017 in_attribute3(i),
4018 in_attribute4(i),
4019 in_attribute5(i),
4020 in_attribute6(i),
4021 in_attribute7(i),
4022 in_attribute8(i),
4023 in_attribute9(i),
4024 in_attribute10(i),
4025 in_attribute11(i),
4026 in_attribute12(i),
4027 in_attribute13(i),
4028 in_attribute14(i),
4029 in_attribute15(i),
4030 in_start_date(i),
4031 in_end_date(i),
4032 in_dnz_ste_code(i)
4033 );
4034
4035 IF (l_debug = 'Y') THEN
4036 okc_debug.log('12800: Leaving INSERT_ROW_UPG', 2);
4037 okc_debug.Reset_Indentation;
4038 END IF;
4039
4040 EXCEPTION
4041 WHEN OTHERS THEN
4042 -- store SQL error message on message stack
4043 OKC_API.SET_MESSAGE(
4044 p_app_name => G_APP_NAME,
4045 p_msg_name => G_UNEXPECTED_ERROR,
4046 p_token1 => G_SQLCODE_TOKEN,
4047 p_token1_value => SQLCODE,
4048 p_token2 => G_SQLERRM_TOKEN,
4049 p_token2_value => SQLERRM);
4050 -- notify caller of an error as UNEXPECTED error
4051 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4052
4053
4054 /*okc_debug.log('12900: Exiting INSERT_ROW_UPG:OTHERS Exception', 2);
4055 IF (l_debug = 'Y') THEN
4056 okc_debug.Reset_Indentation;
4057 END IF;
4058
4059 RAISE*/
4060
4061 END INSERT_ROW_UPG;
4062
4063
4064 --This function is called from versioning API OKC_VERSION_PVT
4065 --Old Location: OKCRVERB.pls
4066 --New Location: Base Table API
4067
4068 FUNCTION create_version(
4069 p_chr_id IN NUMBER,
4070 p_major_version IN NUMBER
4071 ) RETURN VARCHAR2 IS
4072
4073 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
4074
4075 BEGIN
4076
4077 IF (l_debug = 'Y') THEN
4078 okc_debug.Set_Indentation('OKC_CTC_PVT');
4079 okc_debug.log('13000: Entered create_version', 2);
4080 END IF;
4081
4082 INSERT INTO okc_contacts_h
4083 (
4084 major_version,
4085 id,
4086 cpl_id,
4087 cro_code,
4088 dnz_chr_id,
4089 object1_id1,
4090 object1_id2,
4091 jtot_object1_code,
4092 object_version_number,
4093 created_by,
4094 creation_date,
4095 last_updated_by,
4096 last_update_date,
4097 contact_sequence,
4098 last_update_login,
4099 primary_yn,
4100 resource_class,
4101 SALES_GROUP_ID,
4102 attribute_category,
4103 attribute1,
4104 attribute2,
4105 attribute3,
4106 attribute4,
4107 attribute5,
4108 attribute6,
4109 attribute7,
4110 attribute8,
4111 attribute9,
4112 attribute10,
4113 attribute11,
4114 attribute12,
4115 attribute13,
4116 attribute14,
4117 attribute15,
4118 start_date,
4119 end_date
4120 )
4121 SELECT
4122 p_major_version,
4123 id,
4124 cpl_id,
4125 cro_code,
4126 dnz_chr_id,
4127 object1_id1,
4128 object1_id2,
4129 jtot_object1_code,
4130 object_version_number,
4131 created_by,
4132 creation_date,
4133 last_updated_by,
4134 last_update_date,
4135 contact_sequence,
4136 last_update_login,
4137 primary_yn,
4138 resource_class,
4139 SALES_GROUP_ID,
4140 attribute_category,
4141 attribute1,
4142 attribute2,
4143 attribute3,
4144 attribute4,
4145 attribute5,
4146 attribute6,
4147 attribute7,
4148 attribute8,
4149 attribute9,
4150 attribute10,
4151 attribute11,
4152 attribute12,
4153 attribute13,
4154 attribute14,
4155 attribute15,
4156 start_date,
4157 end_date
4158 FROM okc_contacts
4159 WHERE dnz_chr_id = p_chr_id;
4160
4161 IF (l_debug = 'Y') THEN
4162 okc_debug.log('13100: Exiting create_version', 2);
4163 okc_debug.Reset_Indentation;
4164 END IF;
4165
4166 RETURN l_return_status;
4167
4168 EXCEPTION
4169 -- other appropriate handlers
4170 WHEN OTHERS THEN
4171
4172 IF (l_debug = 'Y') THEN
4173 okc_debug.log('13200: Exiting create_version:OTHERS Exception', 2);
4174 okc_debug.Reset_Indentation;
4175 END IF;
4176
4177 -- store SQL error message on message stack
4178 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4179 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4180 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4181 p_token1_value => sqlcode,
4182 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4183 p_token2_value => sqlerrm);
4184
4185 -- notify UNEXPECTED error
4186 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4187 return l_return_status;
4188
4189 END create_version;
4190
4191 --This Function is called from Versioning API OKC_VERSION_PVT
4192 --Old Location:OKCRVERB.pls
4193 --New Location:Base Table API
4194
4195 FUNCTION restore_version(
4196 p_chr_id IN NUMBER,
4197 p_major_version IN NUMBER
4198 ) RETURN VARCHAR2 IS
4199
4200 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
4201
4202 BEGIN
4203
4204 IF (l_debug = 'Y') THEN
4205 okc_debug.Set_Indentation('OKC_CTC_PVT');
4206 okc_debug.log('13300: Entered restore_version', 2);
4207 END IF;
4208
4209 INSERT INTO okc_contacts
4210 (
4211 id,
4212 cpl_id,
4213 cro_code,
4214 dnz_chr_id,
4215 object1_id1,
4216 object1_id2,
4217 jtot_object1_code,
4218 object_version_number,
4219 created_by,
4220 creation_date,
4221 last_updated_by,
4222 last_update_date,
4223 contact_sequence,
4224 last_update_login,
4225 primary_yn,
4226 resource_class,
4227 SALES_GROUP_ID,
4228 attribute_category,
4229 attribute1,
4230 attribute2,
4231 attribute3,
4232 attribute4,
4233 attribute5,
4234 attribute6,
4235 attribute7,
4236 attribute8,
4237 attribute9,
4238 attribute10,
4239 attribute11,
4240 attribute12,
4241 attribute13,
4242 attribute14,
4243 attribute15,
4244 start_date,
4245 end_date
4246 )
4247 SELECT
4248 id,
4249 cpl_id,
4250 cro_code,
4251 dnz_chr_id,
4252 object1_id1,
4253 object1_id2,
4254 jtot_object1_code,
4255 object_version_number,
4256 created_by,
4257 creation_date,
4258 last_updated_by,
4259 last_update_date,
4260 contact_sequence,
4261 last_update_login,
4262 primary_yn,
4263 resource_class,
4264 SALES_GROUP_ID,
4265 attribute_category,
4266 attribute1,
4267 attribute2,
4268 attribute3,
4269 attribute4,
4270 attribute5,
4271 attribute6,
4272 attribute7,
4273 attribute8,
4274 attribute9,
4275 attribute10,
4276 attribute11,
4277 attribute12,
4278 attribute13,
4279 attribute14,
4280 attribute15,
4281 start_date,
4282 end_date
4283 FROM okc_contacts_h
4284 WHERE dnz_chr_id = p_chr_id
4285 AND major_version = p_major_version;
4286
4287 /* Bug fix for 6882512*/
4288 OKC_CTC_PVT.update_contact_stecode(p_chr_id => p_chr_id,
4289 x_return_status=>l_return_status);
4290
4291 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4292 RAISE OKC_API.G_EXCEPTION_ERROR;
4293 END IF;
4294 /* Bug fix for 6882512*/
4295
4296 IF (l_debug = 'Y') THEN
4297 okc_debug.log('13400: Exiting restore_version', 2);
4298 okc_debug.Reset_Indentation;
4299 END IF;
4300
4301 RETURN l_return_status;
4302
4303 EXCEPTION
4304 -- other appropriate handlers
4305 WHEN OTHERS THEN
4306
4307 IF (l_debug = 'Y') THEN
4308 okc_debug.log('13500: Exiting restore_version:OTHERS Exception', 2);
4309 okc_debug.Reset_Indentation;
4310 END IF;
4311
4312 -- store SQL error message on message stack
4313 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4314 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4315 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4316 p_token1_value => sqlcode,
4317 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4318 p_token2_value => sqlerrm);
4319
4320 -- notify UNEXPECTED error
4321 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4322 return l_return_status;
4323
4324 END restore_version;
4325
4326 END OKC_CTC_PVT;