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