[Home] [Help]
Skip to content
PACKAGE BODY: APPS.OKL_CONTRACT_PARTY_PVT
Source
1 PACKAGE BODY OKL_CONTRACT_PARTY_PVT AS
2 /* $Header: OKLRCPCB.pls 120.2 2005/10/20 09:38:41 sjalasut noship $ */
3 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_CONTRACT_PARTY_PVT';
5 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
6 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
7 -- Start of comments
8 --
9 -- Procedure Name : create_contact
10 -- Description :
11 -- Business Rules :
12 -- Parameters :
13 -- Version : 1.0
14 -- End of comments
15 PROCEDURE create_contact(p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2,
20 p_ctcv_rec IN ctcv_rec_type,
21 x_ctcv_rec OUT NOCOPY ctcv_rec_type) is
22 l_api_name CONSTANT VARCHAR2(30) := 'create_contact';
23 l_api_version CONSTANT NUMBER := 1;
24 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
25
26 CURSOR cur_ctcv IS
27 SELECT 'x'
28 FROM okc_contacts
29 WHERE cpl_id = p_ctcv_rec.CPL_ID
30 AND cro_code = p_ctcv_rec.CRO_CODE
31 AND NVL(jtot_object1_code,'X') = NVL(p_ctcv_rec.JTOT_OBJECT1_CODE,'X')
32 AND NVL(object1_id1,'X') = NVL(p_ctcv_rec.OBJECT1_ID1,'X')
33 AND NVL(object1_id2,'X') = NVL(p_ctcv_rec.OBJECT1_ID2,'X')
34 AND id <> NVL(p_ctcv_rec.ID,-9999);
35
36 l_row_found BOOLEAN := FALSE;
37 l_dummy VARCHAR2(1);
38
39 BEGIN
40 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
41 ,p_init_msg_list => p_init_msg_list
42 ,p_api_type => '_PVT'
43 ,x_return_status => x_return_status
44 );
45 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
46 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
47 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
48 RAISE OKL_API.G_EXCEPTION_ERROR;
49 END IF;
50
51 x_return_status := OKL_API.G_RET_STS_SUCCESS;
52
53 -- Not null Validation for Contact Role
54 IF ((p_ctcv_rec.cro_code = OKL_API.G_MISS_CHAR) OR (p_ctcv_rec.cro_code IS NULL)) THEN
55 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_CONTACT_ROLE_REQUIRED');
56 l_return_status :=okl_api.g_ret_sts_error;
57 RAISE OKL_API.G_EXCEPTION_ERROR;
58 END IF;
59
60 -- Not null Validation for Contact Name
61 IF ((p_ctcv_rec.object1_id1 = OKL_API.G_MISS_CHAR) OR (p_ctcv_rec.object1_id1 IS NULL)) OR
62 ((p_ctcv_rec.object1_id2 = OKL_API.G_MISS_CHAR) OR (p_ctcv_rec.object1_id2 IS NULL)) THEN
63 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_CONTACT_NAME_REQUIRED');
64 l_return_status :=okl_api.g_ret_sts_error;
65 RAISE OKL_API.G_EXCEPTION_ERROR;
66 END IF;
67
68 IF ( (p_ctcv_rec.CPL_ID IS NOT NULL) AND (p_ctcv_rec.CPL_ID <> OKC_API.G_MISS_NUM) )
69 AND ( (p_ctcv_rec.CRO_CODE IS NOT NULL) AND (p_ctcv_rec.CRO_CODE <> OKC_API.G_MISS_CHAR) ) THEN
70 OPEN cur_ctcv;
71 FETCH cur_ctcv INTO l_dummy;
72 l_row_found := cur_ctcv%FOUND;
73 CLOSE cur_ctcv;
74 IF (l_row_found) THEN
75 -- Display the newly defined error message
76 OKL_API.set_message(G_APP_NAME, 'OKL_DUP_CONTACT_ROLE');
77 x_return_status := OKL_API.G_RET_STS_ERROR;
78 RAISE OKL_API.G_EXCEPTION_ERROR;
79 END IF;
80 END IF;
81
82 OKL_OKC_MIGRATION_PVT.create_contact(p_api_version,
83 p_init_msg_list,
84 x_return_status,
85 x_msg_count,
86 x_msg_data,
87 p_ctcv_rec,
88 x_ctcv_rec);
89
90 /* Manu 29-Jun-2005 Begin */
91 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
92 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(p_api_version => p_api_version
93 ,p_init_msg_list => p_init_msg_list
94 ,x_return_status => x_return_status
95 ,x_msg_count => x_msg_count
96 ,x_msg_data => x_msg_data
97 ,p_program_id => x_ctcv_rec.dnz_chr_id
98 );
99 END IF;
100 /* Manu 29-Jun-2005 END */
101
102 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
103 EXCEPTION
104 WHEN OKL_API.G_EXCEPTION_ERROR THEN
105 x_return_status := OKL_API.HANDLE_EXCEPTIONS
106 (p_api_name => l_api_name
107 ,p_pkg_name => G_PKG_NAME
108 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
109 ,x_msg_count => x_msg_count
110 ,x_msg_data => x_msg_data
111 ,p_api_type => '_PVT'
112 );
113 end create_contact;
114 -- Start of comments
115 --
116 -- Procedure Name : create_contact
117 -- Description :
118 -- Business Rules :
119 -- Parameters :
120 -- Version : 1.0
121 -- End of comments
122 PROCEDURE create_contact(p_api_version IN NUMBER,
123 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
124 x_return_status OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_msg_data OUT NOCOPY VARCHAR2,
127 p_ctcv_tbl IN ctcv_tbl_type,
128 x_ctcv_tbl OUT NOCOPY ctcv_tbl_type) IS
129 i NUMBER;
130 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
131 BEGIN
132 OKC_API.init_msg_list(p_init_msg_list);
133 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
134 IF (p_ctcv_tbl.COUNT>0) THEN
135 i := p_ctcv_tbl.FIRST;
136 LOOP
137 create_contact(p_api_version=>p_api_version,
138 p_init_msg_list=>OKC_API.G_FALSE,
139 x_return_status=>l_return_status,
140 x_msg_count=>x_msg_count,
141 x_msg_data=>x_msg_data,
142 p_ctcv_rec=>p_ctcv_tbl(i),
143 x_ctcv_rec=>x_ctcv_tbl(i));
144 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
145 x_return_status := l_return_status;
146 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
147 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
148 x_return_status := l_return_status;
149 END IF;
150 EXIT WHEN (i=p_ctcv_tbl.LAST);
151 i := p_ctcv_tbl.NEXT(i);
152 END LOOP;
153 END IF;
154 EXCEPTION
155 WHEN OTHERS THEN NULL;
156
157 END create_contact;
158 -- Start of comments
159 --
160 -- Procedure Name : update_contact
161 -- Description :
162 -- Business Rules :
163 -- Parameters :
164 -- Version : 1.0
165 -- End of comments
166 PROCEDURE update_contact(p_api_version IN NUMBER,
167 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
168 x_return_status OUT NOCOPY VARCHAR2,
169 x_msg_count OUT NOCOPY NUMBER,
170 x_msg_data OUT NOCOPY VARCHAR2,
171 p_ctcv_rec IN ctcv_rec_type,
172 x_ctcv_rec OUT NOCOPY ctcv_rec_type) IS
173 l_api_name CONSTANT VARCHAR2(30) := 'update_contact';
174 l_api_version CONSTANT NUMBER := 1;
175 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
176 BEGIN
177 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
178 ,p_init_msg_list => p_init_msg_list
179 ,p_api_type => '_PVT'
180 ,x_return_status => x_return_status
181 );
182 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
183 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
184 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
185 RAISE OKL_API.G_EXCEPTION_ERROR;
186 END IF;
187
188 x_return_status := OKL_API.G_RET_STS_SUCCESS;
189 OKL_OKC_MIGRATION_PVT.update_contact(p_api_version,
190 p_init_msg_list,
191 x_return_status,
192 x_msg_count,
193 x_msg_data,
194 p_ctcv_rec,
195 x_ctcv_rec);
196
197 /* Manu 29-Jun-2005 Begin */
198 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
199 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(p_api_version => p_api_version
200 ,p_init_msg_list => p_init_msg_list
201 ,x_return_status => x_return_status
202 ,x_msg_count => x_msg_count
203 ,x_msg_data => x_msg_data
204 ,p_program_id => x_ctcv_rec.dnz_chr_id
205 );
206 END IF;
207 /* Manu 29-Jun-2005 END */
208
209 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
210 EXCEPTION
211 WHEN OKL_API.G_EXCEPTION_ERROR THEN
212 x_return_status := OKL_API.HANDLE_EXCEPTIONS
213 (p_api_name => l_api_name
214 ,p_pkg_name => G_PKG_NAME
215 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
216 ,x_msg_count => x_msg_count
217 ,x_msg_data => x_msg_data
218 ,p_api_type => '_PVT'
219 );
220 END update_contact;
221
222 -- Start of comments
223 --
224 -- Procedure Name : update_contact
225 -- Description :
226 -- Business Rules :
227 -- Parameters :
228 -- Version : 1.0
229 -- End of comments
230 PROCEDURE update_contact(p_api_version IN NUMBER,
231 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2,
235 p_ctcv_tbl IN ctcv_tbl_type,
236 x_ctcv_tbl OUT NOCOPY ctcv_tbl_type) IS
237 i NUMBER;
238 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
239 begin
240 OKC_API.init_msg_list(p_init_msg_list);
241 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
242 IF (p_ctcv_tbl.COUNT>0) THEN
243 i := p_ctcv_tbl.FIRST;
244 LOOP
245 update_contact(p_api_version=>p_api_version,
246 p_init_msg_list=>OKC_API.G_FALSE,
247 x_return_status=>l_return_status,
248 x_msg_count=>x_msg_count,
249 x_msg_data=>x_msg_data,
250 p_ctcv_rec=>p_ctcv_tbl(i),
251 x_ctcv_rec=>x_ctcv_tbl(i));
252 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
253 x_return_status := l_return_status;
254 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
255 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
256 x_return_status := l_return_status;
257 END IF;
258 EXIT WHEN (i=p_ctcv_tbl.LAST);
259 i := p_ctcv_tbl.NEXT(i);
260 END LOOP;
261 END IF;
262 EXCEPTION
263 WHEN OTHERS THEN NULL;
264 END update_contact;
265
266 -- Start of comments
267 --
268 -- Procedure Name : delete_contact
269 -- Description :
270 -- Business Rules :
271 -- Parameters :
272 -- Version : 1.0
273 -- End of comments
274 PROCEDURE delete_contact(p_api_version IN NUMBER,
275 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2,
279 p_ctcv_rec IN ctcv_rec_type) IS
280 l_api_name CONSTANT VARCHAR2(30) := 'delete_contact';
281 l_api_version CONSTANT NUMBER := 1;
282 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
283 l_dnz_chr_id OKC_CONTACTS.DNZ_CHR_ID%TYPE;
284
285 CURSOR cur_get_dnz_chr_id IS
286 SELECT dnz_chr_id
287 FROM okc_contacts
288 WHERE id = p_ctcv_rec.id;
289
290 BEGIN
291 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
292 ,p_init_msg_list => p_init_msg_list
293 ,p_api_type => '_PVT'
294 ,x_return_status => x_return_status
295 );
296 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
297 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
298 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
299 RAISE OKL_API.G_EXCEPTION_ERROR;
300 END IF;
301
302 /* Manu 29-Jun-2005 Begin */
303 OPEN cur_get_dnz_chr_id;
304 FETCH cur_get_dnz_chr_id INTO l_dnz_chr_id;
305 CLOSE cur_get_dnz_chr_id;
306 /* Manu 29-Jun-2005 END */
307
308 x_return_status := OKL_API.G_RET_STS_SUCCESS;
309 OKL_OKC_MIGRATION_PVT.delete_contact(p_api_version,
310 p_init_msg_list,
311 x_return_status,
312 x_msg_count,
313 x_msg_data,
314 p_ctcv_rec);
315
316
317
318 /* Manu 29-Jun-2005 Begin */
319 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
320 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(p_api_version => p_api_version
321 ,p_init_msg_list => p_init_msg_list
322 ,x_return_status => x_return_status
323 ,x_msg_count => x_msg_count
324 ,x_msg_data => x_msg_data
325 ,p_program_id => l_dnz_chr_id
326 );
327 END IF;
328 /* Manu 29-Jun-2005 END */
329
330 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
331 EXCEPTION
332 WHEN OKL_API.G_EXCEPTION_ERROR THEN
333 x_return_status := OKL_API.HANDLE_EXCEPTIONS
334 (p_api_name => l_api_name
335 ,p_pkg_name => G_PKG_NAME
336 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
337 ,x_msg_count => x_msg_count
338 ,x_msg_data => x_msg_data
339 ,p_api_type => '_PVT'
340 );
341 END delete_contact;
342
343 -- Start of comments
344 --
345 -- Procedure Name : delete_contact
346 -- Description :
347 -- Business Rules :
348 -- Parameters :
349 -- Version : 1.0
350 -- End of comments
351 PROCEDURE delete_contact(p_api_version IN NUMBER,
352 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2,
356 p_ctcv_tbl IN ctcv_tbl_type) IS
357 i NUMBER;
358 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
359 BEGIN
360 OKC_API.init_msg_list(p_init_msg_list);
361 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
362 IF (p_ctcv_tbl.COUNT>0) THEN
363 i := p_ctcv_tbl.FIRST;
364 LOOP
365 delete_contact(p_api_version=>p_api_version,
366 p_init_msg_list=>OKC_API.G_FALSE,
367 x_return_status=>l_return_status,
368 x_msg_count=>x_msg_count,
369 x_msg_data=>x_msg_data,
370 p_ctcv_rec=>p_ctcv_tbl(i));
371 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
372 x_return_status := l_return_status;
373 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
374 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) then
375 x_return_status := l_return_status;
376 END IF;
377 EXIT WHEN (i=p_ctcv_tbl.LAST);
378 i := p_ctcv_tbl.NEXT(i);
379 END LOOP;
380 END IF;
381 EXCEPTION
382 WHEN OTHERS THEN NULL;
383 END delete_contact;
384
385 -- Start of comments
386 --
387 -- Procedure Name : create_k_party_role
388 -- Description :
389 -- Business Rules :
390 -- Parameters :
391 -- Version : 1.0
392 -- End of comments
393 PROCEDURE create_k_party_role(p_api_version IN NUMBER,
394 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
395 x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2,
398 p_cplv_rec IN cplv_rec_type,
399 x_cplv_rec OUT NOCOPY cplv_rec_type) is
400 l_api_name CONSTANT VARCHAR2(30) := 'create_k_party_role';
401 l_api_version CONSTANT NUMBER := 1;
402 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
403
404 CURSOR C1(p_chr_id okc_k_party_roles_b.chr_id%TYPE,
405 p_rle_code okc_k_party_roles_b.rle_code%TYPE,
406 p_jtot_object1_code okc_k_party_roles_b.jtot_object1_code%TYPE,
407 p_object1_id1 okc_k_party_roles_b.object1_id1%TYPE,
408 p_object1_id2 okc_k_party_roles_b.object1_id2%TYPE) is
409 SELECT id
410 FROM okc_k_party_roles_b
411 WHERE chr_id = p_chr_id
412 AND rle_code = p_rle_code
413 AND jtot_object1_code = p_jtot_object1_code
414 AND object1_id1 = p_object1_id1
415 AND object1_id2 = p_object1_id2;
416
417 l_id NUMBER;
418 l_row_found BOOLEAN := FALSE;
419
420 BEGIN
421
422
423 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
424 ,p_init_msg_list => p_init_msg_list
425 ,p_api_type => '_PVT'
426 ,x_return_status => x_return_status
427 );
428 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
429 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
430 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
431 RAISE OKL_API.G_EXCEPTION_ERROR;
432 END IF;
433
434 x_return_status := OKL_API.G_RET_STS_SUCCESS;
435
436 -- Not null Validation for Party Role
437 IF ((p_cplv_rec.rle_code = OKL_API.G_MISS_CHAR) OR (p_cplv_rec.rle_code IS NULL)) THEN
438 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_PARTY_ROLE_REQUIRED');
439 l_return_status :=okl_api.g_ret_sts_error;
440 RAISE OKL_API.G_EXCEPTION_ERROR;
441 END IF;
442
443 -- Not null Validation for Party Name
444 IF ((p_cplv_rec.object1_id1 = OKL_API.G_MISS_CHAR) OR (p_cplv_rec.object1_id1 IS NULL)) OR
445 ((p_cplv_rec.object1_id2 = OKL_API.G_MISS_CHAR) OR (p_cplv_rec.object1_id2 IS NULL)) THEN
446 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_PARTY_NAME_REQUIRED');
447 l_return_status :=okl_api.g_ret_sts_error;
448 RAISE OKL_API.G_EXCEPTION_ERROR;
449 END IF;
450
451 IF p_cplv_rec.chr_id IS NOT NULL AND p_cplv_rec.chr_id <> OKC_API.G_MISS_NUM THEN
452 OPEN c1(p_cplv_rec.chr_id,
453 p_cplv_rec.rle_code,
454 p_cplv_rec.jtot_object1_code,
455 p_cplv_rec.object1_id1,
456 p_cplv_rec.object1_id2);
457 FETCH c1 INTO l_id;
458 l_row_found := c1%FOUND;
459 CLOSE c1;
460 END IF;
461
462 IF l_row_found THEN
463 IF l_id <> p_cplv_rec.id THEN
464 OKL_API.set_message(p_app_name => g_app_name,
465 p_msg_name => 'OKL_DUP_PARTY'
466 );
467 x_return_status := OKL_API.G_RET_STS_ERROR;
468 RAISE OKL_API.G_EXCEPTION_ERROR;
469 END IF;
470 END IF;
471
472 OKL_OKC_MIGRATION_PVT.create_k_party_role(p_api_version,
473 p_init_msg_list,
474 x_return_status,
475 x_msg_count,
476 x_msg_data,
477 p_cplv_rec,
478 x_cplv_rec);
479
480 /* Manu 29-Jun-2005 Begin */
481 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
482 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(p_api_version => p_api_version
483 ,p_init_msg_list => p_init_msg_list
484 ,x_return_status => x_return_status
485 ,x_msg_count => x_msg_count
486 ,x_msg_data => x_msg_data
487 ,p_program_id => x_cplv_rec.dnz_chr_id
488 );
489 END IF;
490 /* Manu 29-Jun-2005 END */
491
492 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
493 EXCEPTION
494 WHEN OKL_API.G_EXCEPTION_ERROR THEN
495 x_return_status := OKL_API.HANDLE_EXCEPTIONS
496 (p_api_name => l_api_name
497 ,p_pkg_name => G_PKG_NAME
498 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
499 ,x_msg_count => x_msg_count
500 ,x_msg_data => x_msg_data
501 ,p_api_type => '_PVT'
502 );
503 WHEN OTHERS THEN
504 x_return_status := OKL_API.HANDLE_EXCEPTIONS
505 (p_api_name => l_api_name
506 ,p_pkg_name => G_PKG_NAME
507 ,p_exc_name => 'OTHERS'
508 ,x_msg_count => x_msg_count
509 ,x_msg_data => x_msg_data
510 ,p_api_type => '_PVT'
511 );
512 END create_k_party_role;
513
514 -- Start of comments
515 --
516 -- Procedure Name : create_k_party_role
517 -- Description :
518 -- Business Rules :
519 -- Parameters :
520 -- Version : 1.0
521 -- End of comments
522 PROCEDURE create_k_party_role(p_api_version IN NUMBER,
523 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
524 x_return_status OUT NOCOPY VARCHAR2,
525 x_msg_count OUT NOCOPY NUMBER,
526 x_msg_data OUT NOCOPY VARCHAR2,
527 p_cplv_tbl IN cplv_tbl_type,
528 x_cplv_tbl OUT NOCOPY cplv_tbl_type) IS
529 i NUMBER;
530 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
531 BEGIN
532 OKC_API.init_msg_list(p_init_msg_list);
533 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
534 IF (p_cplv_tbl.COUNT>0) THEN
535 i := p_cplv_tbl.FIRST;
536 LOOP
537 create_k_party_role(p_api_version=>p_api_version,
538 p_init_msg_list=>OKC_API.G_FALSE,
539 x_return_status=>l_return_status,
540 x_msg_count=>x_msg_count,
541 x_msg_data=>x_msg_data,
542 p_cplv_rec=>p_cplv_tbl(i),
543 x_cplv_rec=>x_cplv_tbl(i));
544 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
545 x_return_status := l_return_status;
546 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
547 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
548 x_return_status := l_return_status;
549 END IF;
550 EXIT WHEN (i=p_cplv_tbl.LAST);
551 i := p_cplv_tbl.NEXT(i);
552 END LOOP;
553 END IF;
554 EXCEPTION
555 WHEN OTHERS THEN NULL;
556 END create_k_party_role;
557
558 -- Start of comments
559 --
560 -- Procedure Name : update_k_party_role
561 -- Description :
562 -- Business Rules :
563 -- Parameters :
564 -- Version : 1.0
565 -- End of comments
566 PROCEDURE update_k_party_role(p_api_version IN NUMBER,
567 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
568 x_return_status OUT NOCOPY VARCHAR2,
569 x_msg_count OUT NOCOPY NUMBER,
570 x_msg_data OUT NOCOPY VARCHAR2,
571 p_cplv_rec IN cplv_rec_type,
572 x_cplv_rec OUT NOCOPY cplv_rec_type) IS
573 l_api_name CONSTANT VARCHAR2(30) := 'update_k_party_role';
574 l_api_version CONSTANT NUMBER := 1;
575 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
576 BEGIN
577 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
578 ,p_init_msg_list => p_init_msg_list
579 ,p_api_type => '_PVT'
580 ,x_return_status => x_return_status
581 );
582 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
583 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
584 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
585 RAISE OKL_API.G_EXCEPTION_ERROR;
586 END IF;
587
588 x_return_status := OKL_API.G_RET_STS_SUCCESS;
589 OKL_OKC_MIGRATION_PVT.update_k_party_role(p_api_version,
590 p_init_msg_list,
591 x_return_status,
592 x_msg_count,
593 x_msg_data,
594 p_cplv_rec,
595 x_cplv_rec);
596
597 /* Manu 29-Jun-2005 Begin */
598
599 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
600 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(p_api_version => p_api_version
601 ,p_init_msg_list => p_init_msg_list
602 ,x_return_status => x_return_status
603 ,x_msg_count => x_msg_count
604 ,x_msg_data => x_msg_data
605 ,p_program_id => x_cplv_rec.dnz_chr_id
606 );
607 END IF;
608
609 /* Manu 29-Jun-2005 END */
610
611 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
612 EXCEPTION
613 WHEN OKL_API.G_EXCEPTION_ERROR THEN
614 x_return_status := OKL_API.HANDLE_EXCEPTIONS
615 (p_api_name => l_api_name
616 ,p_pkg_name => G_PKG_NAME
617 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
618 ,x_msg_count => x_msg_count
619 ,x_msg_data => x_msg_data
620 ,p_api_type => '_PVT'
621 );
622 END update_k_party_role;
623
624 -- Start of comments
625 --
626 -- Procedure Name : update_k_party_role
627 -- Description :
628 -- Business Rules :
629 -- Parameters :
630 -- Version : 1.0
631 -- End of comments
632 procedure update_k_party_role(p_api_version IN NUMBER,
633 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
634 x_return_status OUT NOCOPY VARCHAR2,
635 x_msg_count OUT NOCOPY NUMBER,
636 x_msg_data OUT NOCOPY VARCHAR2,
637 p_cplv_tbl IN cplv_tbl_type,
638 x_cplv_tbl OUT NOCOPY cplv_tbl_type) is
639 i NUMBER;
640 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
641 begin
642 OKC_API.init_msg_list(p_init_msg_list);
643 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
644 IF (p_cplv_tbl.COUNT>0) THEN
645 i := p_cplv_tbl.FIRST;
646 LOOP
647 update_k_party_role(p_api_version=>p_api_version,
648 p_init_msg_list=>OKC_API.G_FALSE,
649 x_return_status=>l_return_status,
650 x_msg_count=>x_msg_count,
651 x_msg_data=>x_msg_data,
652 p_cplv_rec=>p_cplv_tbl(i),
653 x_cplv_rec=>x_cplv_tbl(i));
654 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
655 x_return_status := l_return_status;
656 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
657 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
658 x_return_status := l_return_status;
659 END IF;
660 EXIT WHEN (i=p_cplv_tbl.LAST);
661 i := p_cplv_tbl.NEXT(i);
662 END LOOP;
663 END IF;
664 EXCEPTION
665 WHEN OTHERS THEN NULL;
666 END update_k_party_role;
667
668 -- Start of comments
669 --
670 -- Procedure Name : delete_k_party_role
671 -- Description :
672 -- Business Rules :
673 -- Parameters :
674 -- Version : 1.0
675 -- End of comments
676 PROCEDURE delete_k_party_role(p_api_version IN NUMBER,
677 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
678 x_return_status OUT NOCOPY VARCHAR2,
679 x_msg_count OUT NOCOPY NUMBER,
680 x_msg_data OUT NOCOPY VARCHAR2,
681 p_cplv_rec IN cplv_rec_type) IS
682
683 l_api_name CONSTANT VARCHAR2(30) := 'delete_k_party_role';
684 l_api_version CONSTANT NUMBER := 1;
685 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
686
687
688 l_dummy_var VARCHAR2(1) := '?';
689 l_rle_code_var VARCHAR2(30) DEFAULT NULL;
690 l_dnz_chr_id okc_k_party_roles_v.dnz_chr_id%TYPE;
691
692 CURSOR l_cpl_csr IS
693 SELECT 'x'
694 FROM OKC_CONTACTS_V
695 WHERE CPL_ID = p_cplv_rec.id;
696
697 CURSOR l_rle_code_csr IS
698 SELECT rle_code
699 FROM OKC_K_PARTY_ROLES_B
700 WHERE ID = p_cplv_rec.id;
701
702 CURSOR cur_get_dnz_chr_id IS
703 SELECT dnz_chr_id
704 FROM okc_k_party_roles_v
705 WHERE id = p_cplv_rec.id;
706
707 CURSOR c_party_share_exist_csr(cp_chr_id okc_k_headers_b.id%TYPE, cp_cpl_id okc_k_party_roles_b.id%TYPE)IS
708 SELECT okl_vp_jtf_party_name_pub.get_party_name(pr.rle_code,chr.buy_or_sell,pr.object1_id1,pr.object1_id2) party_name
709 FROM okc_k_party_roles_b pr
710 ,okc_k_headers_b chr
711 ,okc_rules_b rule
712 WHERE pr.id = cp_cpl_id
713 AND rule.rule_information1 = pr.id
714 AND rule.rule_information_category = 'VGLRSP'
715 AND rule.dnz_chr_id = cp_chr_id
716 AND pr.dnz_chr_id = chr.id
717 AND chr.id = cp_chr_id;
718 lv_party_name hz_parties.party_name%TYPE;
719
720 BEGIN
721 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
722 ,p_init_msg_list => p_init_msg_list
723 ,p_api_type => '_PVT'
724 ,x_return_status => x_return_status
725 );
726 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
727 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
728 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
729 RAISE OKL_API.G_EXCEPTION_ERROR;
730 END IF;
731
732 x_return_status := OKL_API.G_RET_STS_SUCCESS;
733
734 open l_cpl_csr;
735 fetch l_cpl_csr into l_dummy_var;
736 close l_cpl_csr;
737 IF (l_dummy_var = 'x') then
738 OKL_API.set_message(p_app_name => g_app_name,
739 p_msg_name => 'OKL_PARTY_CONTACTS_EXIST'
740 );
741 x_return_status := OKL_API.G_RET_STS_ERROR;
742 RAISE OKL_API.G_EXCEPTION_ERROR;
743 END IF;
744
745 --bug#2455475
746 OPEN l_rle_code_csr;
747 FETCH l_rle_code_csr INTO l_rle_code_var;
748 CLOSE l_rle_code_csr;
749 IF (l_rle_code_var = 'LESSOR' OR l_rle_code_var = 'OKL_VENDOR' OR l_rle_code_var IS NULL) THEN
750 OKL_API.set_message(p_app_name => g_app_name,
751 p_msg_name => 'OKL_CANNOT_DEL_LESSOR_VENDOR'
752 );
753 x_return_status := OKL_API.G_RET_STS_ERROR;
754 RAISE OKL_API.G_EXCEPTION_ERROR;
755 END IF;
756
757 /* Manu 29-Jun-2005 Begin */
758 OPEN cur_get_dnz_chr_id;
759 FETCH cur_get_dnz_chr_id INTO l_dnz_chr_id;
760 CLOSE cur_get_dnz_chr_id;
761 /* Manu 29-Jun-2005 END */
762
763 -- sjalasut, added logic to restrict deletion of party role when the party is involved in residual share
764 -- check to see if cpl_id exists in okc_rules_b (even if his percentage is 0 ??) and error out in such a case
765 OPEN c_party_share_exist_csr(cp_chr_id => l_dnz_chr_id, cp_cpl_id => p_cplv_rec.id);
766 FETCH c_party_share_exist_csr INTO lv_party_name;
767 IF(c_party_share_exist_csr%FOUND)THEN
768 CLOSE c_party_share_exist_csr;
769 okl_api.set_message(p_app_name => G_APP_NAME
770 ,p_msg_name => 'OKL_VN_NODELPARTY_SHARE_EXISTS'
771 ,p_token1 => 'PARTY_NAME'
772 ,p_token1_value => lv_party_name
773 );
774 x_return_status := OKL_API.G_RET_STS_ERROR;
775 RAISE OKL_API.G_EXCEPTION_ERROR;
776 ELSE
777 CLOSE c_party_share_exist_csr;
778 END IF;
779 -- sjalasut. end code changes for Vendor Residual Share Enhancement
780
781 OKL_OKC_MIGRATION_PVT.delete_k_party_role(p_api_version,
782 p_init_msg_list,
783 x_return_status,
784 x_msg_count,
785 x_msg_data,
786 p_cplv_rec);
787
788
789 /* Manu 29-Jun-2005 Begin */
790 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
791 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(p_api_version => p_api_version
792 ,p_init_msg_list => p_init_msg_list
793 ,x_return_status => x_return_status
794 ,x_msg_count => x_msg_count
795 ,x_msg_data => x_msg_data
796 ,p_program_id => l_dnz_chr_id
797 );
798 END IF;
799 /* Manu 29-Jun-2005 END */
800
801 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
802 EXCEPTION
803 WHEN OKL_API.G_EXCEPTION_ERROR THEN
804 x_return_status := OKL_API.HANDLE_EXCEPTIONS
805 (p_api_name => l_api_name
806 ,p_pkg_name => G_PKG_NAME
807 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
808 ,x_msg_count => x_msg_count
809 ,x_msg_data => x_msg_data
810 ,p_api_type => '_PVT'
811 );
812 END delete_k_party_role;
813
814 -- Start of comments
815 --
816 -- Procedure Name : delete_k_party_role
817 -- Description :
818 -- Business Rules :
819 -- Parameters :
820 -- Version : 1.0
821 -- End of comments
822 PROCEDURE delete_k_party_role(p_api_version IN NUMBER,
823 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
824 x_return_status OUT NOCOPY VARCHAR2,
825 x_msg_count OUT NOCOPY NUMBER,
826 x_msg_data OUT NOCOPY VARCHAR2,
827 p_cplv_tbl IN cplv_tbl_type) IS
828 i NUMBER;
829 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
830 BEGIN
831 OKC_API.init_msg_list(p_init_msg_list);
832 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
833 IF (p_cplv_tbl.COUNT>0) THEN
834 i := p_cplv_tbl.FIRST;
835 LOOP
836 delete_k_party_role(p_api_version=>p_api_version,
837 p_init_msg_list=>OKC_API.G_FALSE,
838 x_return_status=>l_return_status,
839 x_msg_count=>x_msg_count,
840 x_msg_data=>x_msg_data,
841 p_cplv_rec=>p_cplv_tbl(i));
842 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
843 x_return_status := l_return_status;
844 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
845 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
846 x_return_status := l_return_status;
847 END IF;
848 EXIT WHEN (i=p_cplv_tbl.LAST);
849 i := p_cplv_tbl.NEXT(i);
850 END LOOP;
851 END IF;
852 EXCEPTION
853 WHEN OTHERS THEN NULL;
854 END delete_k_party_role;
855
856 END; -- Package Body OKL_CONTRACT_PARTY_PVT