[Home] [Help]
PACKAGE BODY: APPS.OKL_PARTY_PAYMENTS_PVT
Source
1 PACKAGE BODY Okl_Party_Payments_Pvt AS
2 /* $Header: OKLRPPMB.pls 120.2 2006/02/24 21:31:35 rpillay noship $ */
3
4
5 --------------------------------------------------------
6 -- Get cle related infor for evg migration --
7 --------------------------------------------------------
8
9 PROCEDURE create_evgrn_party_roles(
10 p_api_version IN NUMBER,
11 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 p_chr_id IN NUMBER,
16 p_vendor_id IN NUMBER,
17 x_cpl_id OUT NOCOPY NUMBER
18 ) IS
19 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_EVGRN_PARTY_ROLES';
20 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
21 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
22
23 CURSOR chr_party_role_csr (p_chr_id IN NUMBER, p_vendor_id IN NUMBER) IS
24 SELECT '1'
25 FROM okc_k_party_roles_b cpl
26 WHERE cpl.chr_id = p_chr_id
27 AND cpl.rle_code='OKL_VENDOR'
28 AND cpl.object1_id1 = p_vendor_id;
29
30 l_chr_role_exists VARCHAR2(1);
31 l_cplv_rec cplv_rec_type;
32 x_cplv_rec cplv_rec_type;
33
34 --Bug# 4558486
35 l_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
36 x_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
37
38 BEGIN
39 -- Create party if evg vendor does not exists in contract parties --
40 OPEN chr_party_role_csr (p_chr_id, p_vendor_id);
41 FETCH chr_party_role_csr INTO l_chr_role_exists;
42 CLOSE chr_party_role_csr;
43
44 IF (l_chr_role_exists IS NULL) THEN
45 l_cplv_rec := NULL;
46 l_cplv_rec.chr_id := p_chr_id;
47 l_cplv_rec.dnz_chr_id := p_chr_id;
48 l_cplv_rec.cle_id := NULL;
49
50 l_cplv_rec.object1_id1 := TO_CHAR(p_vendor_id);
51 l_cplv_rec.object1_id2 := '#';
52 l_cplv_rec.jtot_object1_code := 'OKX_VENDOR';
53 l_cplv_rec.rle_code := 'OKL_VENDOR';
54
55 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
56 -- to create records in tables
57 -- okc_k_party_roles_b and okl_k_party_roles
58 /*
59 Okl_Okc_Migration_Pvt.create_k_party_role(
60 p_api_version => 1.0,
61 p_init_msg_list => Okl_Api.G_FALSE,
62 x_return_status => x_return_status,
63 x_msg_count => x_msg_count,
64 x_msg_data => x_msg_data,
65 p_cplv_rec => l_cplv_rec,
66 x_cplv_rec => x_cplv_rec
67 );
68 */
69
70 okl_k_party_roles_pvt.create_k_party_role(
71 p_api_version => 1.0,
72 p_init_msg_list => Okl_Api.G_FALSE,
73 x_return_status => x_return_status,
74 x_msg_count => x_msg_count,
75 x_msg_data => x_msg_data,
76 p_cplv_rec => l_cplv_rec,
77 x_cplv_rec => x_cplv_rec,
78 p_kplv_rec => l_kplv_rec,
79 x_kplv_rec => x_kplv_rec);
80
81 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
82 RAISE Okl_Api.G_EXCEPTION_ERROR;
83 END IF;
84 END IF;
85 x_cpl_id := x_cplv_rec.id;
86
87 EXCEPTION
88 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
89
90 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
91 (l_api_name,
92 G_PKG_NAME,
93 'OKC_API.G_RET_STS_ERROR',
94 x_msg_count,
95 x_msg_data,
96 '_PVT');
97 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
98 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
99 (l_api_name,
100 G_PKG_NAME,
101 'OKC_API.G_RET_STS_UNEXP_ERROR',
102 x_msg_count,
103 x_msg_data,
104 '_PVT');
105 WHEN OTHERS THEN
106 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
107 (l_api_name,
108 G_PKG_NAME,
109 'OTHERS',
110 x_msg_count,
111 x_msg_data,
112 '_PVT');
113 END create_evgrn_party_roles;
114
115 --------------------------------------------------------
116 -- Get cle related infor for evg migration --
117 --------------------------------------------------------
118
119 PROCEDURE create_evgrn_party_roles(
120 p_api_version IN NUMBER,
121 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 p_chr_id IN NUMBER,
126 p_vendor_id IN NUMBER,
127 x_cle_tbl OUT NOCOPY evg_cle_tbl_type
128 ) IS
129 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_EVGRN_PARTY_ROLES';
130 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
131 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
132 i NUMBER := 0;
133 lx_cle_tbl evg_cle_tbl_type;
134
135 CURSOR chr_party_role_csr (p_chr_id IN NUMBER, p_vendor_id IN NUMBER) IS
136 SELECT '1'
137 FROM okc_k_party_roles_b cpl
138 WHERE cpl.chr_id = p_chr_id
139 AND cpl.rle_code='OKL_VENDOR'
140 AND cpl.object1_id1 = p_vendor_id;
141
142 l_chr_role_exists VARCHAR2(1);
143 l_cplv_rec cplv_rec_type;
144 x_cplv_rec cplv_rec_type;
145
146 CURSOR lines_csr (p_chr_id IN NUMBER) IS
147 SELECT cle.id,
148 cle.start_date
149 FROM okc_k_lines_v cle,
150 OKL_K_LINES kle,
151 okc_line_styles_b lse
152 WHERE cle.id = kle.id
153 AND lse.id = cle.lse_id
154 AND cle.dnz_chr_id = p_chr_id
155 --AND lse.lty_code IN ('FREE_FORM1','SOLD_SERVICE')
156 AND lse.lty_code = 'SOLD_SERVICE'
157 AND cle.sts_code NOT IN ('ABANDONED','CANCELLED','EXPIRED','TERMINATED');
158
159 TYPE line_tbl_type IS TABLE OF lines_csr%ROWTYPE;
160 l_line_tbl line_tbl_type;
161
162 CURSOR cle_party_role_csr (p_chr_id IN NUMBER, p_cle_id IN NUMBER,p_vendor_id IN NUMBER) IS
163 SELECT id
164 FROM okc_k_party_roles_b
165 WHERE dnz_chr_id = p_chr_id
166 AND cle_id = p_cle_id
167 AND rle_code='OKL_VENDOR'
168 AND object1_id1 = p_vendor_id;
169
170 l_cpl_id NUMBER;
171
172 --Bug# 4558486
173 l_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
174 x_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
175
176 BEGIN
177 -- Create party if evg vendor does not exists in contract parties --
178 OPEN chr_party_role_csr (p_chr_id, p_vendor_id);
179 FETCH chr_party_role_csr INTO l_chr_role_exists;
180 CLOSE chr_party_role_csr;
181
182 IF (l_chr_role_exists IS NULL) THEN
183 l_cplv_rec := NULL;
184 l_cplv_rec.chr_id := p_chr_id;
185 l_cplv_rec.dnz_chr_id := p_chr_id;
186 l_cplv_rec.cle_id := NULL;
187
188 l_cplv_rec.object1_id1 := TO_CHAR(p_vendor_id);
189 l_cplv_rec.object1_id2 := '#';
190 l_cplv_rec.jtot_object1_code := 'OKX_VENDOR';
191 l_cplv_rec.rle_code := 'OKL_VENDOR';
192
193 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
194 -- to create records in tables
195 -- okc_k_party_roles_b and okl_k_party_roles
196 /*
197 Okl_Okc_Migration_Pvt.create_k_party_role(
198 p_api_version => 1.0,
199 p_init_msg_list => Okl_Api.G_FALSE,
200 x_return_status => x_return_status,
201 x_msg_count => x_msg_count,
202 x_msg_data => x_msg_data,
203 p_cplv_rec => l_cplv_rec,
204 x_cplv_rec => x_cplv_rec
205 );
206 */
207
208 okl_k_party_roles_pvt.create_k_party_role(
209 p_api_version => 1.0,
210 p_init_msg_list => Okl_Api.G_FALSE,
211 x_return_status => x_return_status,
212 x_msg_count => x_msg_count,
213 x_msg_data => x_msg_data,
214 p_cplv_rec => l_cplv_rec,
215 x_cplv_rec => x_cplv_rec,
216 p_kplv_rec => l_kplv_rec,
217 x_kplv_rec => x_kplv_rec);
218
219 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
220 RAISE Okl_Api.G_EXCEPTION_ERROR;
221 END IF;
222 END IF;
223
224 -- Find out service lines for the contract --
225 OPEN lines_csr (p_chr_id);
226 FETCH lines_csr BULK COLLECT INTO l_line_tbl;
227 CLOSE lines_csr;
228
229 i:= 0;
230 IF l_line_tbl.COUNT > 0 THEN
231 i := l_line_tbl.FIRST;
232 LOOP
233 -- find out same party as evg vendor for the line--
234 OPEN cle_party_role_csr (p_chr_id, l_line_tbl(i).id, p_vendor_id);
235 FETCH cle_party_role_csr INTO l_cpl_id;
236 CLOSE cle_party_role_csr;
237
238 IF (l_cpl_id IS NULL) THEN
239 l_cplv_rec := NULL;
240 l_cplv_rec.chr_id := NULL;
241 l_cplv_rec.dnz_chr_id := p_chr_id;
242 l_cplv_rec.cle_id := l_line_tbl(i).id;
243
244 l_cplv_rec.object1_id1 := TO_CHAR(p_vendor_id);
245 l_cplv_rec.object1_id2 := '#';
246 l_cplv_rec.jtot_object1_code := 'OKX_VENDOR';
247 l_cplv_rec.rle_code := 'OKL_VENDOR';
248
249 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
250 -- to create records in tables
251 -- okc_k_party_roles_b and okl_k_party_roles
252 /*
253 Okl_Okc_Migration_Pvt.create_k_party_role(
254 p_api_version => 1.0,
255 p_init_msg_list => Okl_Api.G_FALSE,
256 x_return_status => x_return_status,
257 x_msg_count => x_msg_count,
258 x_msg_data => x_msg_data,
259 p_cplv_rec => l_cplv_rec,
260 x_cplv_rec => x_cplv_rec
261 );
262 */
263
264 okl_k_party_roles_pvt.create_k_party_role(
265 p_api_version => 1.0,
266 p_init_msg_list => Okl_Api.G_FALSE,
267 x_return_status => x_return_status,
268 x_msg_count => x_msg_count,
269 x_msg_data => x_msg_data,
270 p_cplv_rec => l_cplv_rec,
271 x_cplv_rec => x_cplv_rec,
272 p_kplv_rec => l_kplv_rec,
273 x_kplv_rec => x_kplv_rec);
274
275 lx_cle_tbl(i).cle_id := x_cplv_rec.cle_id;
276 lx_cle_tbl(i).cpl_id := x_cplv_rec.id;
277 lx_cle_tbl(i).cle_start_date := l_line_tbl(i).start_date;
278
279 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
280 RAISE Okl_Api.G_EXCEPTION_ERROR;
281 END IF;
282 ELSE
283 lx_cle_tbl(i).cle_id := l_line_tbl(i).id;
284 lx_cle_tbl(i).cpl_id := l_cpl_id;
285 lx_cle_tbl(i).cle_start_date := l_line_tbl(i).start_date;
286 END IF;
287
288 -- populate the out parameter --
289 x_cle_tbl(i).cle_id := lx_cle_tbl(i).cle_id;
290 x_cle_tbl(i).cpl_id := lx_cle_tbl(i).cpl_id;
291 x_cle_tbl(i).cle_start_date := lx_cle_tbl(i).cle_start_date;
292
293 EXIT WHEN (i = l_line_tbl.LAST);
294 i := l_line_tbl.NEXT(i);
295 END LOOP;
296 END IF;
297
298 EXCEPTION
299 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
300
301 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
302 (l_api_name,
303 G_PKG_NAME,
304 'OKC_API.G_RET_STS_ERROR',
305 x_msg_count,
306 x_msg_data,
307 '_PVT');
308 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
309 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
310 (l_api_name,
311 G_PKG_NAME,
312 'OKC_API.G_RET_STS_UNEXP_ERROR',
313 x_msg_count,
314 x_msg_data,
315 '_PVT');
316 WHEN OTHERS THEN
317 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
318 (l_api_name,
319 G_PKG_NAME,
320 'OTHERS',
321 x_msg_count,
322 x_msg_data,
323 '_PVT');
324 END create_evgrn_party_roles;
325
326
327 --------------------------------------------------------
328 -- Validations for the header
329 --------------------------------------------------------
330
331 PROCEDURE validate_hdr_record (
332 x_return_status OUT NOCOPY VARCHAR2,
333 p_pphv_rec IN pphv_rec_type
334 ) IS
335 l_api_name VARCHAR2(30) := 'VALIDATE_HDR_RECORD';
336 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
337
338 BEGIN
339 x_return_status := l_return_status;
340 IF (p_pphv_rec.passthru_term = 'BASE' ) THEN
341 IF (p_pphv_rec.payout_basis NOT IN ('BILLING','DUE_DATE','PARTIAL_RECEIPT','FULL_RECEIPT')) THEN
342 Okl_Api.set_message(G_APP_NAME, G_INVALID_VALUE,
343 G_COL_NAME_TOKEN,'Payout Basis');
344 x_return_status := Okl_Api.G_RET_STS_ERROR;
345 RAISE G_EXCEPTION_HALT_VALIDATION;
346 END IF;
347
348 IF (p_pphv_rec.passthru_start_date IS NULL) THEN
349 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Passthru Start Date');
350 x_return_status := Okl_Api.G_RET_STS_ERROR;
351 RAISE G_EXCEPTION_HALT_VALIDATION;
352 END IF;
353
354 END IF;
355
356 IF (p_pphv_rec.passthru_term = 'EVERGREEN') THEN
357
358 IF (p_pphv_rec.payout_basis NOT IN ('BILLING','FORMULA','PARTIAL_RECEIPT','FULL_RECEIPT')) THEN
359 Okl_Api.set_message(G_APP_NAME, G_INVALID_VALUE,
360 G_COL_NAME_TOKEN,'Payout Basis');
361 x_return_status := Okl_Api.G_RET_STS_ERROR;
362 RAISE G_EXCEPTION_HALT_VALIDATION;
363 END IF;
364
365 IF (p_pphv_rec.payout_basis = 'FORMULA') THEN
366 IF (p_pphv_rec.passthru_stream_type_id IS NULL) THEN
367 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Passthru Stream Type');
368 x_return_status := Okl_Api.G_RET_STS_ERROR;
369 RAISE G_EXCEPTION_HALT_VALIDATION;
370 END IF;
371
372 IF (p_pphv_rec.payout_basis_formula IS NULL) THEN
373 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Payout Basis Formula');
374 x_return_status := Okl_Api.G_RET_STS_ERROR;
375 RAISE G_EXCEPTION_HALT_VALIDATION;
376 END IF;
377 END IF;
378 END IF;
379
380 EXCEPTION
381 WHEN G_EXCEPTION_HALT_VALIDATION THEN
382 NULL;
383 WHEN OTHERS THEN
384 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
385 ,p_msg_name => G_UNEXPECTED_ERROR
386 ,p_token1 => G_SQLCODE_TOKEN
387 ,p_token1_value => SQLCODE
388 ,p_token2 => G_SQLERRM_TOKEN
389 ,p_token2_value => SQLERRM);
390 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
391 END validate_hdr_record;
392
393 PROCEDURE create_party_payment_hdr(
394 p_api_version IN NUMBER,
395 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
396 x_return_status OUT NOCOPY VARCHAR2,
397 x_msg_count OUT NOCOPY NUMBER,
398 x_msg_data OUT NOCOPY VARCHAR2,
399 p_pphv_rec IN pphv_rec_type,
400 x_pphv_rec OUT NOCOPY pphv_rec_type
401 ) IS
402 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PARTY_PAYMENT_HDR';
403 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
404
405 CURSOR get_start_date_csr (p_chr_id NUMBER)IS
406 SELECT start_date
407 FROM okc_k_headers_b
408 WHERE id = p_chr_id;
409 l_start_date DATE;
410 l_pphv_rec pphv_rec_type;
411
412 -- fmiao 25-OCT-2005 cle_id can be null in case of EVERGREEN
413 -- for all asset line on contract level, hence the DEFAILT --
414 CURSOR check_duplicate_csr (p_chr_id NUMBER,
415 p_cle_id NUMBER,
416 p_passthru_term VARCHAR2) IS
417 SELECT '1'
418 FROM OKL_PARTY_PAYMENT_HDR
419 WHERE dnz_chr_id = p_chr_id
420 AND NVL(cle_id,-1) = NVL(p_cle_id,-1)
421 AND passthru_term = p_passthru_term;
422 l_exist VARCHAR2(1);
423 BEGIN
424
425 x_return_status := l_return_status;
426
427 -- Call start_activity to create savepoint, check compatibility
428 -- and initialize message list
429 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
430 ,p_init_msg_list
431 ,'_PVT'
432 ,x_return_status);
433
434 -- Check if activity started successfully
435 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
436 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
437 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
438 RAISE Okc_Api.G_EXCEPTION_ERROR;
439 END IF;
440
441 --Bug# 4959361
442 IF p_pphv_rec.cle_id IS NOT NULL THEN
443 OKL_LLA_UTIL_PVT.check_line_update_allowed
444 (p_api_version => p_api_version,
445 p_init_msg_list => p_init_msg_list,
446 x_return_status => x_return_status,
447 x_msg_count => x_msg_count,
448 x_msg_data => x_msg_data,
449 p_cle_id => p_pphv_rec.cle_id);
450
451 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
452 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
453 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
454 RAISE OKL_API.G_EXCEPTION_ERROR;
455 END IF;
456 END IF;
457 --Bug# 4959361
458
459 -- Check duplidates: each line should have only 1 BASE or/and 1 EVERGREEN --
460 OPEN check_duplicate_csr (p_pphv_rec.dnz_chr_id,
461 p_pphv_rec.cle_id,
462 p_pphv_rec.passthru_term);
463 FETCH check_duplicate_csr INTO l_exist;
464 CLOSE check_duplicate_csr;
465 IF (l_exist IS NOT NULL) THEN
466 Okc_Api.SET_MESSAGE( p_app_name => g_app_name,
467 p_msg_name => 'OKL_PPM_PASSTHRU_TERM_EXISTS'
468 );
469 x_return_status := Okl_Api.G_RET_STS_ERROR;
470 RAISE G_EXCEPTION_HALT_VALIDATION;
471 END IF;
472
473 -- Defaulting effective_from from contract start date--
474 OPEN get_start_date_csr (p_pphv_rec.dnz_chr_id);
475 FETCH get_start_date_csr INTO l_start_date;
476 CLOSE get_start_date_csr;
477
478 l_pphv_rec := p_pphv_rec;
479 l_pphv_rec.effective_from := l_start_date;
480
481 Okl_Ldb_Pvt.insert_row(p_api_version,
482 p_init_msg_list,
483 x_return_status,
484 x_msg_count,
485 x_msg_data,
486 l_pphv_rec,
487 x_pphv_rec);
488
489 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
490 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
491 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
492 RAISE Okl_Api.G_EXCEPTION_ERROR;
493 END IF;
494
495 validate_hdr_record(x_return_status, x_pphv_rec);
496
497 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
498 l_return_status := x_return_status;
499 RAISE Okc_Api.G_EXCEPTION_ERROR;
500 END IF;
501 x_return_status := l_return_status;
502
503 -- Bug 4917691: fmiao start
504 -- Need to change contract status to INCOMPLETE when create/update ppy
505 -- cascade edit status on to lines
506 okl_contract_status_pub.cascade_lease_status_edit
507 (p_api_version => p_api_version,
508 p_init_msg_list => p_init_msg_list,
509 x_return_status => x_return_status,
510 x_msg_count => x_msg_count,
511 x_msg_data => x_msg_data,
512 p_chr_id => l_pphv_rec.dnz_chr_id);
513
514 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
515 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
516 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
517 raise OKL_API.G_EXCEPTION_ERROR;
518 End If;
519 -- Bug 4917691: fmiao end
520
521 Okc_Api.END_ACTIVITY (x_msg_count
522 ,x_msg_data );
523
524 EXCEPTION
525 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
526 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
527 (l_api_name,
528 G_PKG_NAME,
529 'OKC_API.G_RET_STS_ERROR',
530 x_msg_count,
531 x_msg_data,
532 '_PVT');
533 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
534 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
535 (l_api_name,
536 G_PKG_NAME,
537 'OKC_API.G_RET_STS_UNEXP_ERROR',
538 x_msg_count,
539 x_msg_data,
540 '_PVT');
541 WHEN OTHERS THEN
542 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
543 (l_api_name,
544 G_PKG_NAME,
545 'OTHERS',
546 x_msg_count,
547 x_msg_data,
548 '_PVT');
549 END create_party_payment_hdr;
550
551 PROCEDURE create_party_payment_hdr(
552 p_api_version IN NUMBER,
553 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
554 x_return_status OUT NOCOPY VARCHAR2,
555 x_msg_count OUT NOCOPY NUMBER,
556 x_msg_data OUT NOCOPY VARCHAR2,
557 p_pphv_tbl IN pphv_tbl_type,
558 x_pphv_tbl OUT NOCOPY pphv_tbl_type
559 ) IS
560 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PARTY_PAYMENT_HDR';
561 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
562 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
563 i NUMBER;
564 BEGIN
565
566 IF p_pphv_tbl.COUNT > 0 THEN
567 i := p_pphv_tbl.FIRST;
568 LOOP
569 -- call procedure in complex API for a record
570 create_party_payment_hdr(p_api_version,
571 p_init_msg_list,
572 x_return_status,
573 x_msg_count,
574 x_msg_data,
575 p_pphv_tbl(i),
576 x_pphv_tbl(i));
577 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
578 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
579 l_overall_status := x_return_status;
580 END IF;
581 END IF;
582
583 EXIT WHEN (i = p_pphv_tbl.LAST);
584 i := p_pphv_tbl.NEXT(i);
585 END LOOP;
586
587 -- return overall status
588 x_return_status := l_overall_status;
589 END IF;
590
591 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
592 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
593 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
594 RAISE Okl_Api.G_EXCEPTION_ERROR;
595 END IF;
596
597 EXCEPTION
598 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
599 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
600 (l_api_name,
601 G_PKG_NAME,
602 'OKC_API.G_RET_STS_ERROR',
603 x_msg_count,
604 x_msg_data,
605 '_PVT');
606 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
607 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
608 (l_api_name,
609 G_PKG_NAME,
610 'OKC_API.G_RET_STS_UNEXP_ERROR',
611 x_msg_count,
612 x_msg_data,
613 '_PVT');
614 WHEN OTHERS THEN
615 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
616 (l_api_name,
617 G_PKG_NAME,
618 'OTHERS',
619 x_msg_count,
620 x_msg_data,
621 '_PVT');
622 END create_party_payment_hdr;
623
624 PROCEDURE lock_party_payment_hdr(
625 p_api_version IN NUMBER,
626 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
627 x_return_status OUT NOCOPY VARCHAR2,
628 x_msg_count OUT NOCOPY NUMBER,
629 x_msg_data OUT NOCOPY VARCHAR2,
630 p_pphv_rec IN pphv_rec_type
631 ) IS
632 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_PARTY_PAYMENT_HDR';
633 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
634 BEGIN
635 x_return_status := l_return_status;
636 -- Call start_activity to create savepoint, check compatibility
637 -- and initialize message list
638 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
639 ,p_init_msg_list
640 ,'_PVT'
641 ,x_return_status);
642 -- Check if activity started successfully
643 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
644 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
645 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
646 RAISE Okc_Api.G_EXCEPTION_ERROR;
647 END IF;
648
649 Okl_Ldb_Pvt.lock_row(p_api_version,
650 p_init_msg_list,
651 x_return_status,
652 x_msg_count,
653 x_msg_data,
654 p_pphv_rec);
655
656 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
657 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
658 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
659 RAISE Okl_Api.G_EXCEPTION_ERROR;
660 END IF;
661
662 Okc_Api.END_ACTIVITY (x_msg_count
663 ,x_msg_data );
664
665 EXCEPTION
666 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
667 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
668 (l_api_name,
669 G_PKG_NAME,
670 'OKC_API.G_RET_STS_ERROR',
671 x_msg_count,
672 x_msg_data,
673 '_PVT');
674 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
675 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
676 (l_api_name,
677 G_PKG_NAME,
678 'OKC_API.G_RET_STS_UNEXP_ERROR',
679 x_msg_count,
680 x_msg_data,
681 '_PVT');
682 WHEN OTHERS THEN
683 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
684 (l_api_name,
685 G_PKG_NAME,
686 'OTHERS',
687 x_msg_count,
688 x_msg_data,
689 '_PVT');
690 END lock_party_payment_hdr;
691
692 PROCEDURE lock_party_payment_hdr(
693 p_api_version IN NUMBER,
694 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
695 x_return_status OUT NOCOPY VARCHAR2,
696 x_msg_count OUT NOCOPY NUMBER,
697 x_msg_data OUT NOCOPY VARCHAR2,
698 p_pphv_tbl IN pphv_tbl_type
699 ) IS
700 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_PARTY_PAYMENT_HDR';
701 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
702 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
703 i NUMBER;
704 BEGIN
705
706 IF p_pphv_tbl.COUNT > 0 THEN
707 i := p_pphv_tbl.FIRST;
708 LOOP
709 -- call procedure in complex API for a record
710 lock_party_payment_hdr(p_api_version,
711 p_init_msg_list,
712 x_return_status,
713 x_msg_count,
714 x_msg_data,
715 p_pphv_tbl(i));
716 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
717 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
718 l_overall_status := x_return_status;
719 END IF;
720 END IF;
721
722 EXIT WHEN (i = p_pphv_tbl.LAST);
723 i := p_pphv_tbl.NEXT(i);
724 END LOOP;
725
726 -- return overall status
727 x_return_status := l_overall_status;
728 END IF;
729
730 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
731 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
732 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
733 RAISE Okl_Api.G_EXCEPTION_ERROR;
734 END IF;
735
736 EXCEPTION
737 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
738 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
739 (l_api_name,
740 G_PKG_NAME,
741 'OKC_API.G_RET_STS_ERROR',
742 x_msg_count,
743 x_msg_data,
744 '_PVT');
745 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
746 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
747 (l_api_name,
748 G_PKG_NAME,
749 'OKC_API.G_RET_STS_UNEXP_ERROR',
750 x_msg_count,
751 x_msg_data,
752 '_PVT');
753 WHEN OTHERS THEN
754 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
755 (l_api_name,
756 G_PKG_NAME,
757 'OTHERS',
758 x_msg_count,
759 x_msg_data,
760 '_PVT');
761 END lock_party_payment_hdr;
762
763 PROCEDURE delete_party_payment_hdr(
764 p_api_version IN NUMBER,
765 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
766 x_return_status OUT NOCOPY VARCHAR2,
767 x_msg_count OUT NOCOPY NUMBER,
768 x_msg_data OUT NOCOPY VARCHAR2,
769 p_pphv_rec IN pphv_rec_type
770 ) IS
771 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY_PAYMENT_HDR';
772 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
773
774 --Bug# 4959361
775 CURSOR l_pph_csr(p_pph_id IN NUMBER) IS
776 SELECT cle_id
777 FROM okl_party_payment_hdr pph
778 WHERE pph.id = p_pph_id;
779
780 l_pph_rec l_pph_csr%ROWTYPE;
781 --Bug# 4959361
782
783 BEGIN
784 x_return_status := l_return_status;
785 -- Call start_activity to create savepoint, check compatibility
786 -- and initialize message list
787 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
788 ,p_init_msg_list
789 ,'_PVT'
790 ,x_return_status);
791 -- Check if activity started successfully
792 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
793 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
794 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
795 RAISE Okc_Api.G_EXCEPTION_ERROR;
796 END IF;
797
798 --Bug# 4959361
799 OPEN l_pph_csr(p_pph_id => p_pphv_rec.id);
800 FETCH l_pph_csr INTO l_pph_rec;
801 CLOSE l_pph_csr;
802
803 IF l_pph_rec.cle_id IS NOT NULL THEN
804 OKL_LLA_UTIL_PVT.check_line_update_allowed
805 (p_api_version => p_api_version,
806 p_init_msg_list => p_init_msg_list,
807 x_return_status => x_return_status,
808 x_msg_count => x_msg_count,
809 x_msg_data => x_msg_data,
810 p_cle_id => l_pph_rec.cle_id);
811
812 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
813 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
814 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
815 RAISE OKL_API.G_EXCEPTION_ERROR;
816 END IF;
817 END IF;
818 --Bug# 4959361
819
820 Okl_Ldb_Pvt.delete_row(p_api_version,
821 p_init_msg_list,
822 x_return_status,
823 x_msg_count,
824 x_msg_data,
825 p_pphv_rec);
826
827 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
828 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
829 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
830 RAISE Okl_Api.G_EXCEPTION_ERROR;
831 END IF;
832
833 Okc_Api.END_ACTIVITY (x_msg_count
834 ,x_msg_data );
835
836 EXCEPTION
837 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
838 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
839 (l_api_name,
840 G_PKG_NAME,
841 'OKC_API.G_RET_STS_ERROR',
842 x_msg_count,
843 x_msg_data,
844 '_PVT');
845 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
846 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
847 (l_api_name,
848 G_PKG_NAME,
849 'OKC_API.G_RET_STS_UNEXP_ERROR',
850 x_msg_count,
851 x_msg_data,
852 '_PVT');
853 WHEN OTHERS THEN
854 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
855 (l_api_name,
856 G_PKG_NAME,
857 'OTHERS',
858 x_msg_count,
859 x_msg_data,
860 '_PVT');
861 END delete_party_payment_hdr;
862
863 PROCEDURE delete_party_payment_hdr(
864 p_api_version IN NUMBER,
865 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
866 x_return_status OUT NOCOPY VARCHAR2,
867 x_msg_count OUT NOCOPY NUMBER,
868 x_msg_data OUT NOCOPY VARCHAR2,
869 p_pphv_tbl IN pphv_tbl_type
870 ) IS
871 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY_PAYMENT_HDR';
872 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
873 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
874 i NUMBER;
875 BEGIN
876
877 IF p_pphv_tbl.COUNT > 0 THEN
878 i := p_pphv_tbl.FIRST;
879 LOOP
880 -- call procedure in complex API for a record
881 delete_party_payment_hdr(p_api_version,
882 p_init_msg_list,
883 x_return_status,
884 x_msg_count,
885 x_msg_data,
886 p_pphv_tbl(i));
887 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
888 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
889 l_overall_status := x_return_status;
890 END IF;
891 END IF;
892
893 EXIT WHEN (i = p_pphv_tbl.LAST);
894 i := p_pphv_tbl.NEXT(i);
895 END LOOP;
896
897 -- return overall status
898 x_return_status := l_overall_status;
899 END IF;
900
901 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
902 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
903 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
904 RAISE Okl_Api.G_EXCEPTION_ERROR;
905 END IF;
906
907 EXCEPTION
908 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
909 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
910 (l_api_name,
911 G_PKG_NAME,
912 'OKC_API.G_RET_STS_ERROR',
913 x_msg_count,
914 x_msg_data,
915 '_PVT');
916 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
917 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
918 (l_api_name,
919 G_PKG_NAME,
920 'OKC_API.G_RET_STS_UNEXP_ERROR',
921 x_msg_count,
922 x_msg_data,
923 '_PVT');
924 WHEN OTHERS THEN
925 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
926 (l_api_name,
927 G_PKG_NAME,
928 'OTHERS',
929 x_msg_count,
930 x_msg_data,
931 '_PVT');
932 END delete_party_payment_hdr;
933
934 PROCEDURE update_party_payment_hdr(
935 p_api_version IN NUMBER,
936 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
937 x_return_status OUT NOCOPY VARCHAR2,
938 x_msg_count OUT NOCOPY NUMBER,
939 x_msg_data OUT NOCOPY VARCHAR2,
940 p_pphv_rec IN pphv_rec_type,
941 x_pphv_rec OUT NOCOPY pphv_rec_type
942 ) IS
943 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY_PAYMENT_HDR';
944 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
945
946 --Bug# 4959361
947 CURSOR l_pph_csr(p_pph_id IN NUMBER) IS
948 SELECT cle_id
949 FROM okl_party_payment_hdr pph
950 WHERE pph.id = p_pph_id;
951
952 l_pph_rec l_pph_csr%ROWTYPE;
953 --Bug# 4959361
954
955 BEGIN
956 x_return_status := l_return_status;
957 -- Call start_activity to create savepoint, check compatibility
958 -- and initialize message list
959 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
960 ,p_init_msg_list
961 ,'_PVT'
962 ,x_return_status);
963 -- Check if activity started successfully
964 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
965 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
966 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
967 RAISE Okc_Api.G_EXCEPTION_ERROR;
968 END IF;
969
970 --Bug# 4959361
971 OPEN l_pph_csr(p_pph_id => p_pphv_rec.id);
972 FETCH l_pph_csr INTO l_pph_rec;
973 CLOSE l_pph_csr;
974
975 IF l_pph_rec.cle_id IS NOT NULL THEN
976 OKL_LLA_UTIL_PVT.check_line_update_allowed
977 (p_api_version => p_api_version,
978 p_init_msg_list => p_init_msg_list,
979 x_return_status => x_return_status,
980 x_msg_count => x_msg_count,
981 x_msg_data => x_msg_data,
982 p_cle_id => l_pph_rec.cle_id);
983
984 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
985 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
986 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
987 RAISE OKL_API.G_EXCEPTION_ERROR;
988 END IF;
989 END IF;
990 --Bug# 4959361
991
992 Okl_Ldb_Pvt.update_row(p_api_version,
993 p_init_msg_list,
994 x_return_status,
995 x_msg_count,
996 x_msg_data,
997 p_pphv_rec,
998 x_pphv_rec);
999
1000 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1001 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1002 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
1003 RAISE Okl_Api.G_EXCEPTION_ERROR;
1004 END IF;
1005
1006 validate_hdr_record(x_return_status, x_pphv_rec);
1007
1008 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1009 l_return_status := x_return_status;
1010 RAISE Okc_Api.G_EXCEPTION_ERROR;
1011 END IF;
1012
1013 -- Bug 4917691: fmiao start
1014 -- Need to change contract status to INCOMPLETE when create/update ppy
1015 -- cascade edit status on to lines
1016 okl_contract_status_pub.cascade_lease_status_edit
1017 (p_api_version => p_api_version,
1018 p_init_msg_list => p_init_msg_list,
1019 x_return_status => x_return_status,
1020 x_msg_count => x_msg_count,
1021 x_msg_data => x_msg_data,
1022 p_chr_id => x_pphv_rec.dnz_chr_id);
1023
1024 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
1025 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1026 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
1027 raise OKL_API.G_EXCEPTION_ERROR;
1028 End If;
1029 -- Bug 4917691: fmiao end
1030
1031 Okc_Api.END_ACTIVITY (x_msg_count
1032 ,x_msg_data );
1033
1034 EXCEPTION
1035 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1036 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
1037 (l_api_name,
1038 G_PKG_NAME,
1039 'OKC_API.G_RET_STS_ERROR',
1040 x_msg_count,
1041 x_msg_data,
1042 '_PVT');
1043 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1044 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1045 (l_api_name,
1046 G_PKG_NAME,
1047 'OKC_API.G_RET_STS_UNEXP_ERROR',
1048 x_msg_count,
1049 x_msg_data,
1050 '_PVT');
1051 WHEN OTHERS THEN
1052 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1053 (l_api_name,
1054 G_PKG_NAME,
1055 'OTHERS',
1056 x_msg_count,
1057 x_msg_data,
1058 '_PVT');
1059 END update_party_payment_hdr;
1060
1061 PROCEDURE update_party_payment_hdr(
1062 p_api_version IN NUMBER,
1063 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1064 x_return_status OUT NOCOPY VARCHAR2,
1065 x_msg_count OUT NOCOPY NUMBER,
1066 x_msg_data OUT NOCOPY VARCHAR2,
1067 p_pphv_tbl IN pphv_tbl_type,
1068 x_pphv_tbl OUT NOCOPY pphv_tbl_type
1069 ) IS
1070 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY_PAYMENT_HDR';
1071 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1072 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1073 i NUMBER;
1074
1075 BEGIN
1076
1077 IF p_pphv_tbl.COUNT > 0 THEN
1078 i := p_pphv_tbl.FIRST;
1079 LOOP
1080 -- call procedure in complex API for a record
1081 update_party_payment_hdr(p_api_version,
1082 p_init_msg_list,
1083 x_return_status,
1084 x_msg_count,
1085 x_msg_data,
1086 p_pphv_tbl(i),
1087 x_pphv_tbl(i));
1088 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1089 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1090 l_overall_status := x_return_status;
1091 END IF;
1092 END IF;
1093
1094 EXIT WHEN (i = p_pphv_tbl.LAST);
1095 i := p_pphv_tbl.NEXT(i);
1096 END LOOP;
1097
1098 -- return overall status
1099 x_return_status := l_overall_status;
1100 END IF;
1101
1102 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1103 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1104 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
1105 RAISE Okl_Api.G_EXCEPTION_ERROR;
1106 END IF;
1107
1108 EXCEPTION
1109 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1110 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
1111 (l_api_name,
1112 G_PKG_NAME,
1113 'OKC_API.G_RET_STS_ERROR',
1114 x_msg_count,
1115 x_msg_data,
1116 '_PVT');
1117 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1118 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1119 (l_api_name,
1120 G_PKG_NAME,
1121 'OKC_API.G_RET_STS_UNEXP_ERROR',
1122 x_msg_count,
1123 x_msg_data,
1124 '_PVT');
1125 WHEN OTHERS THEN
1126 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1127 (l_api_name,
1128 G_PKG_NAME,
1129 'OTHERS',
1130 x_msg_count,
1131 x_msg_data,
1132 '_PVT');
1133 END update_party_payment_hdr;
1134
1135 PROCEDURE validate_party_payment_hdr(
1136 p_api_version IN NUMBER,
1137 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1138 x_return_status OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 x_msg_data OUT NOCOPY VARCHAR2,
1141 p_pphv_rec IN pphv_rec_type
1142 ) IS
1143 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_PARTY_PAYMENT_HDR';
1144 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1145 BEGIN
1146 x_return_status := l_return_status;
1147 -- Call start_activity to create savepoint, check compatibility
1148 -- and initialize message list
1149 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
1150 ,p_init_msg_list
1151 ,'_PVT'
1152 ,x_return_status);
1153 -- Check if activity started successfully
1154 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1155 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1156 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1157 RAISE Okc_Api.G_EXCEPTION_ERROR;
1158 END IF;
1159
1160 Okl_Ldb_Pvt.validate_row(p_api_version,
1161 p_init_msg_list,
1162 x_return_status,
1163 x_msg_count,
1164 x_msg_data,
1165 p_pphv_rec);
1166
1167 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1168 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1169 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
1170 RAISE Okl_Api.G_EXCEPTION_ERROR;
1171 END IF;
1172
1173 Okc_Api.END_ACTIVITY (x_msg_count
1174 ,x_msg_data );
1175
1176 EXCEPTION
1177 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1178 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
1179 (l_api_name,
1180 G_PKG_NAME,
1181 'OKC_API.G_RET_STS_ERROR',
1182 x_msg_count,
1183 x_msg_data,
1184 '_PVT');
1185 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1186 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1187 (l_api_name,
1188 G_PKG_NAME,
1189 'OKC_API.G_RET_STS_UNEXP_ERROR',
1190 x_msg_count,
1191 x_msg_data,
1192 '_PVT');
1193 WHEN OTHERS THEN
1194 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1195 (l_api_name,
1196 G_PKG_NAME,
1197 'OTHERS',
1198 x_msg_count,
1199 x_msg_data,
1200 '_PVT');
1201 END validate_party_payment_hdr;
1202
1203 PROCEDURE validate_party_payment_hdr(
1204 p_api_version IN NUMBER,
1205 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1206 x_return_status OUT NOCOPY VARCHAR2,
1207 x_msg_count OUT NOCOPY NUMBER,
1208 x_msg_data OUT NOCOPY VARCHAR2,
1209 p_pphv_tbl IN pphv_tbl_type
1210 ) IS
1211 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_PARTY_PAYMENT_HDR';
1212 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1213 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1214 i NUMBER;
1215 BEGIN
1216
1217 IF p_pphv_tbl.COUNT > 0 THEN
1218 i := p_pphv_tbl.FIRST;
1219 LOOP
1220 -- call procedure in complex API for a record
1221 validate_party_payment_hdr(p_api_version,
1222 p_init_msg_list,
1223 x_return_status,
1224 x_msg_count,
1225 x_msg_data,
1226 p_pphv_tbl(i));
1227 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1228 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1229 l_overall_status := x_return_status;
1230 END IF;
1231 END IF;
1232
1233 EXIT WHEN (i = p_pphv_tbl.LAST);
1234 i := p_pphv_tbl.NEXT(i);
1235 END LOOP;
1236
1237 -- return overall status
1238 x_return_status := l_overall_status;
1239 END IF;
1240
1241 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1242 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1243 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
1244 RAISE Okl_Api.G_EXCEPTION_ERROR;
1245 END IF;
1246
1247 EXCEPTION
1248 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1249 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
1250 (l_api_name,
1251 G_PKG_NAME,
1252 'OKC_API.G_RET_STS_ERROR',
1253 x_msg_count,
1254 x_msg_data,
1255 '_PVT');
1256 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1257 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1258 (l_api_name,
1259 G_PKG_NAME,
1260 'OKC_API.G_RET_STS_UNEXP_ERROR',
1261 x_msg_count,
1262 x_msg_data,
1263 '_PVT');
1264 WHEN OTHERS THEN
1265 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1266 (l_api_name,
1267 G_PKG_NAME,
1268 'OTHERS',
1269 x_msg_count,
1270 x_msg_data,
1271 '_PVT');
1272 END validate_party_payment_hdr;
1273
1274 ------------------------------------------------------------------
1275 --Processing for the details -- for qa checker --
1276 --not used currently --
1277 --NOTES: handle cle_id optional when use this procedure --
1278 ------------------------------------------------------------------
1279 PROCEDURE validate_passthru_qa (p_api_version IN NUMBER,
1280 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1281 x_return_status OUT NOCOPY VARCHAR2,
1282 x_msg_count OUT NOCOPY NUMBER,
1283 x_msg_data OUT NOCOPY VARCHAR2,
1284 p_chr_id IN NUMBER
1285 ) AS
1286 l_api_name VARCHAR2(30) := 'validate_passthru_qa';
1287 l_api_version CONSTANT NUMBER := 1.0;
1288
1289 CURSOR line_amount_csr(p_chr_id IN NUMBER) IS
1290 SELECT kle.amount,
1291 kle.id
1292 FROM okc_k_lines_b cle,
1293 OKL_K_LINES kle,
1294 okc_line_styles_b lse
1295 WHERE cle.dnz_chr_id = p_chr_id
1296 AND cle.chr_id = p_chr_id
1297 AND kle.id = cle.id
1298 AND cle.lse_id = lse.id
1299 AND lse.lty_code IN ('FEE', 'SOLD_SERVICE');
1300
1301 l_kle_amount OKL_K_LINES.amount%TYPE := 0;
1302 l_kle_id OKL_K_LINES.id%TYPE;
1303
1304 CURSOR vendor_amount_csr(p_cle_id IN NUMBER, p_chr_id IN NUMBER) IS
1305 SELECT pyd.disbursement_basis,
1306 pyd.disbursement_fixed_amount,
1307 pyd.disbursement_percent,
1308 pyd.vendor_id
1309 FROM OKL_PARTY_PAYMENT_HDR pph, OKL_PARTY_PAYMENT_DTLS pyd
1310 WHERE pph.dnz_chr_id = p_chr_id
1311 AND pph.cle_id = p_cle_id
1312 AND pph.id = pyd.payment_hdr_id;
1313
1314 l_disbursement_basis OKL_PARTY_PAYMENT_DTLS.disbursement_basis%TYPE;
1315 l_disbursement_fixed_amount OKL_PARTY_PAYMENT_DTLS.disbursement_fixed_amount%TYPE;
1316 l_disbursement_percent OKL_PARTY_PAYMENT_DTLS.disbursement_percent%TYPE;
1317 l_vendor_id OKL_PARTY_PAYMENT_DTLS.vendor_id%TYPE;
1318 l_vendor_total_amt NUMBER := 0;
1319 l_chr_id NUMBER;
1320
1321 CURSOR line_type_csr(p_chr_id IN NUMBER) IS
1322 SELECT DISTINCT(pph.cle_id),pph.payout_basis,lse.lty_code
1323 FROM okc_line_styles_b lse, okc_k_lines_b cle, OKL_PARTY_PAYMENT_HDR pph
1324 WHERE lse.id = cle.lse_id
1325 AND lse.lty_code = 'FEE'
1326 AND cle.id= pph.cle_id
1327 AND pph.dnz_chr_id =p_chr_id;
1328
1329 CURSOR fee_line_amount_csr(p_chr_id IN NUMBER) IS
1330 SELECT kle.amount,
1331 kle.id
1332 FROM okc_k_lines_b cle,
1333 OKL_K_LINES kle,
1334 okc_line_styles_b lse
1335 WHERE cle.dnz_chr_id = p_chr_id
1336 AND cle.chr_id = p_chr_id
1337 AND kle.id = cle.id
1338 AND cle.lse_id = lse.id
1339 AND lse.lty_code = 'FEE';
1340
1341 l_fee_line_amt OKL_K_LINES.amount%TYPE := 0;
1342 l_fee_line_id NUMBER;
1343
1344 CURSOR fee_payment_amt_csr (p_cle_id IN NUMBER, p_chr_id IN NUMBER) IS
1345 SELECT TO_NUMBER(sll.rule_information3) periods,
1346 TO_NUMBER(sll.rule_information6) amount,
1347 TO_NUMBER(sll.rule_information8) stub_amount
1348 FROM okc_rules_b sll,
1349 okc_rule_groups_b rgp
1350 WHERE rgp.dnz_chr_id = p_chr_id
1351 AND rgp.cle_id = p_cle_id
1352 AND rgp.rgd_code = 'LALEVL'
1353 AND sll.rule_information_category = 'LASLL'
1354 AND sll.rgp_id = rgp.id;
1355
1356 l_periods NUMBER := 0;
1357 l_amount NUMBER := 0;
1358 l_stub_amount NUMBER := 0;
1359 l_payment_amt NUMBER := 0;
1360
1361 CURSOR s_line_type_csr(p_chr_id IN NUMBER) IS
1362 SELECT DISTINCT(pph.cle_id),pph.payout_basis
1363 FROM okc_line_styles_b lse, okc_k_lines_b cle, OKL_PARTY_PAYMENT_HDR pph
1364 WHERE lse.id = cle.lse_id
1365 AND lse.lty_code = 'SOLD_SERVICE'
1366 AND cle.id= pph.cle_id
1367 AND pph.dnz_chr_id =p_chr_id;
1368
1369 CURSOR s_payment_amt_csr (p_cle_id IN NUMBER, p_chr_id IN NUMBER) IS
1370 SELECT TO_NUMBER(sll.rule_information6) amount
1371 FROM okc_rules_b sll,
1372 okc_rules_b slh,
1373 okc_rule_groups_b rgp
1374 WHERE rgp.dnz_chr_id = p_chr_id
1375 AND rgp.cle_id = p_cle_id
1376 AND rgp.rgd_code = 'LALEVL'
1377 AND slh.rgp_id = rgp.id
1378 AND slh.rule_information_category = 'LASLH'
1379 AND sll.object2_id1 = slh.id
1380 AND sll.rule_information_category = 'LASLL'
1381 AND sll.rgp_id = rgp.id;
1382
1383 BEGIN
1384
1385 l_chr_id := p_chr_id;
1386 IF Okl_Context.get_okc_org_id IS NULL THEN
1387 Okl_Context.set_okc_org_context(p_chr_id => l_chr_id );
1388 END IF;
1389
1390 x_return_status := Okc_Api.START_ACTIVITY(
1391 p_api_name => l_api_name,
1392 p_pkg_name => g_pkg_name,
1393 p_init_msg_list => p_init_msg_list,
1394 l_api_version => l_api_version,
1395 p_api_version => p_api_version,
1396 p_api_type => g_api_type,
1397 x_return_status => x_return_status);
1398
1399 -- check if activity started successfully
1400 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1401 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1402 ELSIF (x_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1403 RAISE Okc_Api.G_EXCEPTION_ERROR;
1404 END IF;
1405 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1406
1407 -- Sum of the vendors amounts for one cle id should match line amount--
1408 FOR line_amount_rec IN line_amount_csr (p_chr_id => l_chr_id)
1409 LOOP
1410 l_kle_id := line_amount_rec.id;
1411 l_kle_amount := line_amount_rec.amount;
1412 l_vendor_total_amt := 0;
1413 FOR vendor_amount_rec IN vendor_amount_csr (p_cle_id => l_kle_id,
1414 p_chr_id => l_chr_id)
1415 LOOP
1416 l_disbursement_basis := vendor_amount_rec.disbursement_basis;
1417 l_disbursement_fixed_amount := vendor_amount_rec.disbursement_fixed_amount;
1418 l_disbursement_percent := vendor_amount_rec.disbursement_percent;
1419 l_vendor_id := vendor_amount_rec.vendor_id;
1420 IF (l_disbursement_fixed_amount IS NOT NULL OR l_disbursement_percent IS NOT NULL) THEN
1421 IF (l_disbursement_basis = 'PERCENT') THEN
1422 l_vendor_total_amt := l_vendor_total_amt + l_disbursement_percent*l_kle_amount;
1423 ELSE
1424 l_vendor_total_amt := l_vendor_total_amt + l_disbursement_fixed_amount;
1425 END IF;
1426 END IF;
1427 END LOOP;
1428 --dbms_output.put_line('l_vendor_total_amt: '||l_vendor_total_amt||
1429 -- ' l_kle_amount: '||l_kle_amount);
1430 IF (l_vendor_total_amt <> 0 AND l_vendor_total_amt <> l_kle_amount) THEN
1431 x_return_status := Okc_Api.G_RET_STS_ERROR;
1432 Okc_Api.SET_MESSAGE( p_app_name => g_app_name
1433 , p_msg_name => 'OKL_PPM_AMT_NOT_MATCH'
1434 );
1435 RAISE Okc_Api.G_EXCEPTION_ERROR;
1436 END IF;
1437 END LOOP;
1438
1439 --Disbursement amount (line amount) should not exceed fee payemnt amount
1440 FOR line_type_rec IN line_type_csr (p_chr_id => l_chr_id)
1441 LOOP
1442 OPEN fee_line_amount_csr (p_chr_id => l_chr_id);
1443 FETCH fee_line_amount_csr INTO l_fee_line_amt, l_fee_line_id;
1444 CLOSE fee_line_amount_csr;
1445
1446 OPEN fee_payment_amt_csr(p_chr_id => l_chr_id,
1447 p_cle_id => l_fee_line_id);
1448 FETCH fee_payment_amt_csr INTO l_periods, l_amount, l_stub_amount;
1449 CLOSE fee_payment_amt_csr;
1450
1451 IF (l_stub_amount IS NOT NULL) THEN
1452 l_payment_amt := l_amount*l_periods + l_stub_amount;
1453 ELSE
1454 l_payment_amt := l_amount*l_periods;
1455 END IF;
1456
1457 --dbms_output.put_line('l_fee_line_amt: '||l_fee_line_amt||
1458 -- ' l_payment_amt: '||l_payment_amt);
1459 IF (l_payment_amt <> 0) THEN
1460 IF (l_fee_line_amt > l_payment_amt) THEN
1461 x_return_status := Okc_Api.G_RET_STS_ERROR;
1462 Okc_Api.SET_MESSAGE( p_app_name => g_app_name
1463 , p_msg_name => 'OKL_PPM_LINE_GT_PMNT'
1464 );
1465 RAISE Okc_Api.G_EXCEPTION_ERROR;
1466 END IF;
1467 END IF;
1468
1469 -- payment required if payout basis in the folling values
1470 IF (line_type_rec.payout_basis IN ('DUE_DATE','BILLING',
1471 'FULL_RECEIPT','PARTIAL_RECEIPT') AND
1472 NVL(l_amount,0) = 0) THEN
1473 x_return_status := Okc_Api.G_RET_STS_ERROR;
1474 Okc_Api.SET_MESSAGE( p_app_name => g_app_name
1475 , p_msg_name => 'OKL_PPM_PMNT_REQ'
1476 );
1477 RAISE Okc_Api.G_EXCEPTION_ERROR;
1478 END IF;
1479 END LOOP;
1480
1481 l_amount := 0;
1482 --service line, only the following values can have amount --
1483 FOR s_line_type_rec IN s_line_type_csr (p_chr_id => l_chr_id)
1484 LOOP
1485 OPEN s_payment_amt_csr(p_chr_id => l_chr_id,
1486 p_cle_id => s_line_type_rec.cle_id);
1487 FETCH s_payment_amt_csr INTO l_amount;
1488 CLOSE s_payment_amt_csr;
1489 --dbms_output.put_line('s_line_type_rec.payout_basis: '||s_line_type_rec.payout_basis||
1490 -- ' l_amount: '||l_amount);
1491 IF (s_line_type_rec.payout_basis NOT IN ('DUE_DATE','BILLING',
1492 'PARTIAL_RECEIPT','FULL_RECEIPT') AND
1493 l_amount <> 0) THEN
1494 x_return_status := Okc_Api.G_RET_STS_ERROR;
1495 Okc_Api.SET_MESSAGE( p_app_name => g_app_name
1496 , p_msg_name => 'OKL_PPM_PAYOUT_NO_PMNT'
1497 );
1498 RAISE Okc_Api.G_EXCEPTION_ERROR;
1499 END IF;
1500 END LOOP;
1501
1502 Okc_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1503 x_msg_data => x_msg_data);
1504 EXCEPTION
1505 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1506 x_return_status := Okc_Api.HANDLE_EXCEPTIONS(
1507 p_api_name => l_api_name,
1508 p_pkg_name => g_pkg_name,
1509 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1510 x_msg_count => x_msg_count,
1511 x_msg_data => x_msg_data,
1512 p_api_type => g_api_type);
1513
1514 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1515 x_return_status := Okc_Api.HANDLE_EXCEPTIONS(
1516 p_api_name => l_api_name,
1517 p_pkg_name => g_pkg_name,
1518 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1519 x_msg_count => x_msg_count,
1520 x_msg_data => x_msg_data,
1521 p_api_type => g_api_type);
1522
1523 WHEN OTHERS THEN
1524 x_return_status := Okc_Api.HANDLE_EXCEPTIONS(
1525 p_api_name => l_api_name,
1526 p_pkg_name => g_pkg_name,
1527 p_exc_name => 'OTHERS',
1528 x_msg_count => x_msg_count,
1529 x_msg_data => x_msg_data,
1530 p_api_type => g_api_type);
1531
1532 END validate_passthru_qa;
1533
1534
1535 ------------------------------------------------------------------
1536 --Get passthru parameters --
1537 ------------------------------------------------------------------
1538 PROCEDURE get_passthru_parameters(
1539 p_api_version IN NUMBER,
1540 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1541 x_return_status OUT NOCOPY VARCHAR2,
1542 x_msg_count OUT NOCOPY NUMBER,
1543 x_msg_data OUT NOCOPY VARCHAR2,
1544 p_chr_id IN NUMBER,
1545 p_cle_id IN NUMBER,
1546 p_vendor_id IN NUMBER,
1547 x_passthru_param_tbl OUT NOCOPY passthru_param_tbl_type
1548 ) IS
1549 l_api_name CONSTANT VARCHAR2(30) := 'GET_PASSTHRU_PARAMETERS';
1550 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1551
1552 CURSOR get_passthru_param_csr (p_chr_id NUMBER) IS
1553 SELECT pph.dnz_chr_id,
1554 pph.cle_id,
1555 pph.ppl_id,
1556 pph.passthru_term,
1557 pph.passthru_stream_type_id,
1558 pph.passthru_start_date,
1559 pph.payout_basis,
1560 pph.payout_basis_formula,
1561 pph.effective_from,
1562 pph.effective_to,
1563 pyd.id,
1564 pyd.cpl_id,
1565 pyd.vendor_id,
1566 pyd.pay_site_id,
1567 pyd.payment_term_id,
1568 pyd.payment_method_code,
1569 pyd.pay_group_code,
1570 pyd.payment_hdr_id,
1571 pyd.payment_basis,
1572 pyd.payment_start_date,
1573 pyd.payment_frequency,
1574 pyd.remit_days,
1575 pyd.disbursement_basis,
1576 pyd.disbursement_fixed_amount,
1577 pyd.disbursement_percent,
1578 pyd.processing_fee_basis,
1579 pyd.processing_fee_fixed_amount,
1580 pyd.processing_fee_percent,
1581 --pyd.processing_fee_formula,
1582 --pyd.include_in_yield_flag,
1583 pyd.attribute_category,
1584 pyd.attribute1,
1585 pyd.attribute2,
1586 pyd.attribute3,
1587 pyd.attribute4,
1588 pyd.attribute5,
1589 pyd.attribute6,
1590 pyd.attribute7,
1591 pyd.attribute8,
1592 pyd.attribute9,
1593 pyd.attribute10,
1594 pyd.attribute11,
1595 pyd.attribute12,
1596 pyd.attribute13,
1597 pyd.attribute14,
1598 pyd.attribute15
1599 FROM OKL_PARTY_PAYMENT_HDR pph,
1600 OKL_PARTY_PAYMENT_DTLS pyd
1601 WHERE pph.dnz_chr_id = p_chr_id
1602 AND pph.id = pyd.payment_hdr_id;
1603
1604 i NUMBER;
1605 l_passthru_param_rec passthru_param_rec_type;
1606 BEGIN
1607
1608 x_return_status := l_return_status;
1609
1610 i:= 1;
1611 FOR get_passthru_param_rec IN get_passthru_param_csr (p_chr_id)
1612 LOOP
1613 IF (p_cle_id IS NULL OR (p_cle_id IS NOT NULL AND p_cle_id = get_passthru_param_rec.cle_id)) AND
1614 (p_vendor_id IS NULL OR (p_vendor_id IS NOT NULL AND p_vendor_id = get_passthru_param_rec.vendor_id)) THEN
1615 l_passthru_param_rec.dnz_chr_id := get_passthru_param_rec.dnz_chr_id;
1616 l_passthru_param_rec.cle_id := get_passthru_param_rec.cle_id;
1617 l_passthru_param_rec.ppl_id := get_passthru_param_rec.ppl_id;
1618 l_passthru_param_rec.passthru_term := get_passthru_param_rec.passthru_term;
1619 l_passthru_param_rec.passthru_stream_type_id := get_passthru_param_rec.passthru_stream_type_id;
1620 l_passthru_param_rec.passthru_start_date := get_passthru_param_rec.passthru_start_date;
1621 l_passthru_param_rec.payout_basis := get_passthru_param_rec.payout_basis;
1622 l_passthru_param_rec.payout_basis_formula := get_passthru_param_rec.payout_basis_formula;
1623 l_passthru_param_rec.effective_from := get_passthru_param_rec.effective_from;
1624 l_passthru_param_rec.effective_to := get_passthru_param_rec.effective_to;
1625 l_passthru_param_rec.payment_dtls_id:= get_passthru_param_rec.id;
1626 l_passthru_param_rec.cpl_id:=get_passthru_param_rec.cpl_id;
1627 l_passthru_param_rec.vendor_id:=get_passthru_param_rec.vendor_id;
1628 l_passthru_param_rec.pay_site_id:=get_passthru_param_rec.pay_site_id;
1629 l_passthru_param_rec.payment_term_id:= get_passthru_param_rec.payment_term_id;
1630 l_passthru_param_rec.payment_method_code:= get_passthru_param_rec.payment_method_code;
1631 l_passthru_param_rec.pay_group_code:= get_passthru_param_rec.pay_group_code;
1632 l_passthru_param_rec.payment_hdr_id:= get_passthru_param_rec.payment_hdr_id;
1633 l_passthru_param_rec.payment_basis:= get_passthru_param_rec.payment_basis;
1634 l_passthru_param_rec.payment_start_date:= get_passthru_param_rec.payment_start_date;
1635 l_passthru_param_rec.payment_frequency:= get_passthru_param_rec.payment_frequency;
1636 l_passthru_param_rec.remit_days:= get_passthru_param_rec.remit_days;
1637 l_passthru_param_rec.disbursement_basis:= get_passthru_param_rec.disbursement_basis;
1638 l_passthru_param_rec.disbursement_fixed_amount:= get_passthru_param_rec.disbursement_fixed_amount;
1639 l_passthru_param_rec.disbursement_percent:= get_passthru_param_rec.disbursement_percent;
1640 l_passthru_param_rec.processing_fee_basis:= get_passthru_param_rec.processing_fee_basis;
1641 l_passthru_param_rec.processing_fee_fixed_amount:= get_passthru_param_rec.processing_fee_fixed_amount;
1642 l_passthru_param_rec.processing_fee_percent:= get_passthru_param_rec.processing_fee_percent;
1643 --l_passthru_param_rec.processing_fee_formula:= get_passthru_param_rec.processing_fee_formula;
1644 --l_passthru_param_rec.include_in_yield_flag:= get_passthru_param_rec.include_in_yield_flag;
1645 l_passthru_param_rec.attribute_category:= get_passthru_param_rec.attribute_category;
1646 l_passthru_param_rec.attribute1:= get_passthru_param_rec.attribute1;
1647 l_passthru_param_rec.attribute2:= get_passthru_param_rec.attribute2;
1648 l_passthru_param_rec.attribute3:= get_passthru_param_rec.attribute3;
1649 l_passthru_param_rec.attribute4:= get_passthru_param_rec.attribute4;
1650 l_passthru_param_rec.attribute5:= get_passthru_param_rec.attribute5;
1651 l_passthru_param_rec.attribute6:= get_passthru_param_rec.attribute6;
1652 l_passthru_param_rec.attribute7:= get_passthru_param_rec.attribute7;
1653 l_passthru_param_rec.attribute8:= get_passthru_param_rec.attribute8;
1654 l_passthru_param_rec.attribute9:= get_passthru_param_rec.attribute9;
1655 l_passthru_param_rec.attribute10:= get_passthru_param_rec.attribute10;
1656 l_passthru_param_rec.attribute11:= get_passthru_param_rec.attribute11;
1657 l_passthru_param_rec.attribute12:= get_passthru_param_rec.attribute12;
1658 l_passthru_param_rec.attribute13:= get_passthru_param_rec.attribute13;
1659 l_passthru_param_rec.attribute14:= get_passthru_param_rec.attribute14;
1660 l_passthru_param_rec.attribute15:= get_passthru_param_rec.attribute15;
1661 x_passthru_param_tbl(i) := l_passthru_param_rec;
1662 END IF;
1663 i := i+1;
1664 END LOOP;
1665
1666 x_return_status := l_return_status;
1667
1668 EXCEPTION
1669 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1670 NULL;
1671 WHEN OTHERS THEN
1672 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
1673 ,p_msg_name => G_UNEXPECTED_ERROR
1674 ,p_token1 => G_SQLCODE_TOKEN
1675 ,p_token1_value => SQLCODE
1676 ,p_token2 => G_SQLERRM_TOKEN
1677 ,p_token2_value => SQLERRM);
1678 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1679 END get_passthru_parameters;
1680
1681 ----------------------------------------------------------
1682 -- Validate detail record before insert/update
1683 ----------------------------------------------------------
1684 PROCEDURE validate_dtls_record(
1685 x_return_status OUT NOCOPY VARCHAR2,
1686 p_ppydv_rec IN ppydv_rec_type
1687 ) IS
1688 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DTLS_RECORD';
1689 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1690
1691 --cursor to find if vendor id is proper party --
1692 CURSOR l_cplb_csr(p_vendor_id IN NUMBER,
1693 p_cpl_id IN NUMBER) IS
1694 SELECT 'Y'
1695 FROM okc_k_party_roles_b cplb
1696 WHERE id = p_cpl_id
1697 AND object1_id1 = TO_CHAR(p_vendor_id);
1698
1699 l_exists VARCHAR2(1) DEFAULT 'N';
1700
1701 -- find out whether header exists --
1702 CURSOR l_hdr_csr(p_payment_hdr_id IN NUMBER) IS
1703 SELECT 'Y'
1704 FROM OKL_PARTY_PAYMENT_HDR
1705 WHERE id = p_payment_hdr_id;
1706
1707 --find out the passthru term --
1708 CURSOR passthru_term_csr (p_hdr_id IN NUMBER) IS
1709 SELECT passthru_term
1710 FROM OKL_PARTY_PAYMENT_HDR
1711 WHERE id = p_hdr_id;
1712
1713 l_passthru_term OKL_PARTY_PAYMENT_HDR.passthru_term%TYPE := NULL;
1714
1715 BEGIN
1716
1717 -- check for required columns --
1718 x_return_status := l_return_status;
1719 IF( p_ppydv_rec.pay_site_id IS NULL) THEN
1720 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Pay Site');
1721 x_return_status := Okl_Api.G_RET_STS_ERROR;
1722 RAISE G_EXCEPTION_HALT_VALIDATION;
1723 END IF;
1724 IF( p_ppydv_rec.payment_term_id IS NULL) THEN
1725 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Payment Term');
1726 x_return_status := Okl_Api.G_RET_STS_ERROR;
1727 RAISE G_EXCEPTION_HALT_VALIDATION;
1728 END IF;
1729 IF( p_ppydv_rec.pay_group_code IS NULL) THEN
1730 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Pay Group Code');
1731 x_return_status := Okl_Api.G_RET_STS_ERROR;
1732 RAISE G_EXCEPTION_HALT_VALIDATION;
1733 END IF;
1734
1735 IF (p_ppydv_rec.payment_hdr_id IS NULL) THEN
1736 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Payment Header Id');
1737 x_return_status := Okl_Api.G_RET_STS_ERROR;
1738 RAISE G_EXCEPTION_HALT_VALIDATION;
1739 ELSE
1740 --find out whether header exists
1741 l_exists := 'N';
1742 OPEN l_hdr_csr(p_payment_hdr_id => p_ppydv_rec.payment_hdr_id);
1743 FETCH l_hdr_csr INTO l_exists;
1744 IF l_hdr_csr%NOTFOUND THEN
1745 NULL;
1746 END IF;
1747 CLOSE l_hdr_csr;
1748 IF l_exists = 'N' THEN
1749 Okc_Api.SET_MESSAGE( p_app_name => g_app_name
1750 , p_msg_name => 'OKL_PPM_HEADER_MISSING'
1751 );
1752 x_return_status := Okl_Api.G_RET_STS_ERROR;
1753 RAISE G_EXCEPTION_HALT_VALIDATION;
1754 END IF;
1755
1756 END IF;
1757
1758 IF (p_ppydv_rec.payment_basis IS NULL) THEN
1759 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Payment Basis');
1760 x_return_status := Okl_Api.G_RET_STS_ERROR;
1761 RAISE G_EXCEPTION_HALT_VALIDATION;
1762 END IF;
1763 IF (p_ppydv_rec.disbursement_basis IS NULL) THEN
1764 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Disbursement Basis');
1765 x_return_status := Okl_Api.G_RET_STS_ERROR;
1766 RAISE G_EXCEPTION_HALT_VALIDATION;
1767 END IF;
1768
1769 --can be done from UI--
1770 -- Remittance Days required if basis is 'PROCESSINGDATE'--
1771 IF (p_ppydv_rec.payment_basis = 'PROCESS_DATE' AND
1772 p_ppydv_rec.REMIT_DAYS IS NULL) THEN
1773 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1774 p_msg_name => 'OKL_PPM_REMIT_DAYS_REQED'
1775 );
1776 x_return_status := Okc_Api.g_ret_sts_error;
1777 RAISE G_EXCEPTION_HALT_VALIDATION;
1778 END IF;
1779 --Validate Disbursement Fixed Amount
1780 IF (p_ppydv_rec.disbursement_basis = 'AMOUNT' AND
1781 p_ppydv_rec.disbursement_fixed_amount IS NULL) THEN
1782 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Disbursement Fixed Amount');
1783 x_return_status := Okl_Api.G_RET_STS_ERROR;
1784 RAISE G_EXCEPTION_HALT_VALIDATION;
1785 END IF;
1786 --Validate Disbursement Percent
1787 IF (p_ppydv_rec.disbursement_basis = 'PERCENT' AND
1788 p_ppydv_rec.disbursement_percent IS NULL) THEN
1789 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Disbursement Percent');
1790 x_return_status := Okl_Api.G_RET_STS_ERROR;
1791 RAISE G_EXCEPTION_HALT_VALIDATION;
1792 END IF;
1793 --Validate Processing fee fixed Amount
1794 IF (p_ppydv_rec.processing_fee_basis = 'AMOUNT' AND
1795 p_ppydv_rec.processing_fee_fixed_amount IS NULL) THEN
1796 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Processing Fee Fixed Amount');
1797 x_return_status := Okl_Api.G_RET_STS_ERROR;
1798 RAISE G_EXCEPTION_HALT_VALIDATION;
1799 END IF;
1800 --Validate Processing fee Percent
1801 IF (p_ppydv_rec.processing_fee_basis = 'PERCENT' AND
1802 p_ppydv_rec.processing_fee_percent IS NULL) THEN
1803 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Processing Fee Percent');
1804 x_return_status := Okl_Api.G_RET_STS_ERROR;
1805 RAISE G_EXCEPTION_HALT_VALIDATION;
1806 END IF;
1807 --Validate Processing fee Formula
1808 /*
1809 IF (p_ppydv_rec.processing_fee_basis = 'FORMULA' AND
1810 p_ppydv_rec.processing_fee_formula IS NULL) THEN
1811 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Processing Fee Formula');
1812 x_return_status := OKL_API.G_RET_STS_ERROR;
1813 RAISE G_EXCEPTION_HALT_VALIDATION;
1814 End If; */
1815 --the above can be done from UI--
1816
1817 --validate vendor id and cpl id--
1818 l_exists := 'N';
1819 OPEN l_cplb_csr(p_vendor_id => p_ppydv_rec.vendor_id,
1820 p_cpl_id => p_ppydv_rec.cpl_id);
1821 FETCH l_cplb_csr INTO l_exists;
1822 IF l_cplb_csr%NOTFOUND THEN
1823 NULL;
1824 END IF;
1825 CLOSE l_cplb_csr;
1826 IF l_exists = 'N' THEN
1827 Okl_Api.set_message(G_APP_NAME, G_INVALID_VALUE,
1828 G_COL_NAME_TOKEN,'Vendor_Id');
1829 x_return_status := Okl_Api.G_RET_STS_ERROR;
1830 RAISE G_EXCEPTION_HALT_VALIDATION;
1831 END IF;
1832
1833 -- Velidation based on payment basis --
1834 OPEN passthru_term_csr(p_ppydv_rec.payment_hdr_id);
1835 FETCH passthru_term_csr INTO l_passthru_term;
1836 CLOSE passthru_term_csr;
1837
1838 IF (l_passthru_term = 'BASE') THEN
1839 -- Start date and frequency are mandatory if basis is 'SCHEDULED'
1840 IF (p_ppydv_rec.payment_basis = 'SCHEDULED') AND
1841 (p_ppydv_rec.payment_start_date IS NULL OR
1842 p_ppydv_rec.payment_frequency IS NULL) THEN
1843 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1844 p_msg_name => 'OKL_PPM_DF_REQED'
1845 );
1846 x_return_status := Okc_Api.g_ret_sts_error;
1847 RAISE G_EXCEPTION_HALT_VALIDATION;
1848 END IF;
1849
1850 -- Processing fee basis not 'FORMULA' for 'BASE' --
1851 /*
1852 IF (p_ppydv_rec.processing_fee_basis = 'FORMULA') THEN
1853 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1854 p_msg_name => 'OKL_PPM_INV_PROC_FEE_BASIS'
1855 );
1856 x_return_status := OKC_API.g_ret_sts_error;
1857 RAISE G_EXCEPTION_HALT_VALIDATION;
1858 END IF;
1859
1860 -- disbursment basis should not have 'FORMULA' value
1861 IF (p_ppydv_rec.disbursement_basis = 'FORMULA') THEN
1862 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1863 p_msg_name => 'OKL_PPM_INV_DISB_BASIS'
1864 );
1865 x_return_status := OKC_API.g_ret_sts_error;
1866 RAISE G_EXCEPTION_HALT_VALIDATION;
1867 END IF; */
1868 -- Processing fee basis not 'FORMULA' for 'BASE' if INCLUDE_IN_YIELD_FLAG = 'Y'--
1869 /*IF (p_ppydv_rec.include_in_yield_flag = 'Y' AND
1870 p_ppydv_rec.PROCESSING_FEE_BASIS = 'FORMULA') THEN
1871 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1872 p_msg_name => 'OKL_PPM_INV_PRO_FEE_BASIS'
1873 );
1874 x_return_status := OKC_API.g_ret_sts_error;
1875 RAISE G_EXCEPTION_HALT_VALIDATION;
1876 END IF; */
1877 ELSE
1878 -- payment basis has processing days only
1879 IF (p_ppydv_rec.payment_basis = 'SCHEDULED') THEN
1880 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1881 p_msg_name => 'OKL_PPM_INV_PMNT_BASIS'
1882 );
1883 x_return_status := Okc_Api.g_ret_sts_error;
1884 RAISE G_EXCEPTION_HALT_VALIDATION;
1885 END IF;
1886 END IF;
1887
1888 -- raise the exception if status is not s
1889 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1890 l_return_status := x_return_status;
1891 RAISE G_EXCEPTION_HALT_VALIDATION;
1892 END IF;
1893 --x_return_status := l_return_status;
1894 EXCEPTION
1895 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1896 NULL;
1897 WHEN OTHERS THEN
1898 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
1899 ,p_msg_name => G_UNEXPECTED_ERROR
1900 ,p_token1 => G_SQLCODE_TOKEN
1901 ,p_token1_value => SQLCODE
1902 ,p_token2 => G_SQLERRM_TOKEN
1903 ,p_token2_value => SQLERRM);
1904 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1905 END validate_dtls_record;
1906
1907 PROCEDURE create_party_payment_dtls(
1908 p_api_version IN NUMBER,
1909 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1910 x_return_status OUT NOCOPY VARCHAR2,
1911 x_msg_count OUT NOCOPY NUMBER,
1912 x_msg_data OUT NOCOPY VARCHAR2,
1913 p_ppydv_rec IN ppydv_rec_type,
1914 x_ppydv_rec OUT NOCOPY ppydv_rec_type
1915 ) IS
1916 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PARTY_PAYMENT_DTLS';
1917 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1918
1919 CURSOR check_duplicate_csr (p_payment_hdr_id NUMBER,
1920 p_vendor_id NUMBER) IS
1921 SELECT '1'
1922 FROM OKL_PARTY_PAYMENT_DTLS
1923 WHERE payment_hdr_id = p_payment_hdr_id
1924 AND vendor_id = p_vendor_id;
1925 l_exist VARCHAR2(1);
1926
1927 -- Bug 4917691: fmiao start
1928 CURSOR chr_id_csr (p_payment_hdr_id NUMBER) IS
1929 SELECT distinct(pph.dnz_chr_id)
1930 FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph
1931 WHERE ppy.payment_hdr_id = p_payment_hdr_id
1932 AND ppy.payment_hdr_id = pph.id;
1933 l_chr_id NUMBER;
1934 -- Bug 4917691: fmiao end
1935
1936 --Bug# 4959361
1937 CURSOR l_pph_csr(p_pph_id IN NUMBER) IS
1938 SELECT cle_id
1939 FROM okl_party_payment_hdr pph
1940 WHERE pph.id = p_pph_id;
1941
1942 l_pph_rec l_pph_csr%ROWTYPE;
1943 --Bug# 4959361
1944
1945 BEGIN
1946 x_return_status := l_return_status;
1947 -- Call start_activity to create savepoint, check compatibility
1948 -- and initialize message list
1949 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
1950 ,p_init_msg_list
1951 ,'_PVT'
1952 ,x_return_status);
1953 -- Check if activity started successfully
1954 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1955 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1956 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1957 RAISE Okc_Api.G_EXCEPTION_ERROR;
1958 END IF;
1959
1960 --Bug# 4959361
1961 OPEN l_pph_csr(p_pph_id => p_ppydv_rec.payment_hdr_id);
1962 FETCH l_pph_csr INTO l_pph_rec;
1963 CLOSE l_pph_csr;
1964
1965 IF l_pph_rec.cle_id IS NOT NULL THEN
1966 OKL_LLA_UTIL_PVT.check_line_update_allowed
1967 (p_api_version => p_api_version,
1968 p_init_msg_list => p_init_msg_list,
1969 x_return_status => x_return_status,
1970 x_msg_count => x_msg_count,
1971 x_msg_data => x_msg_data,
1972 p_cle_id => l_pph_rec.cle_id);
1973
1974 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1975 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1976 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1977 RAISE OKL_API.G_EXCEPTION_ERROR;
1978 END IF;
1979 END IF;
1980 --Bug# 4959361
1981
1982 -- Check duplidates: each line should have only 1 BASE or/and 1 EVERGREEN --
1983 OPEN check_duplicate_csr (p_ppydv_rec.payment_hdr_id,
1984 p_ppydv_rec.vendor_id);
1985 FETCH check_duplicate_csr INTO l_exist;
1986 CLOSE check_duplicate_csr;
1987 IF (l_exist IS NOT NULL) THEN
1988 Okc_Api.SET_MESSAGE( p_app_name => g_app_name,
1989 p_msg_name => 'OKL_PPM_VENDOR_EXISTS'
1990 );
1991 x_return_status := Okl_Api.G_RET_STS_ERROR;
1992 RAISE G_EXCEPTION_HALT_VALIDATION;
1993 END IF;
1994
1995 Okl_Pyd_Pvt.insert_row(p_api_version,
1996 p_init_msg_list,
1997 x_return_status,
1998 x_msg_count,
1999 x_msg_data,
2000 p_ppydv_rec,
2001 x_ppydv_rec);
2002
2003 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2004 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2005 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2006 RAISE Okl_Api.G_EXCEPTION_ERROR;
2007 END IF;
2008
2009 validate_dtls_record(x_return_status, x_ppydv_rec);
2010
2011 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2012 l_return_status := x_return_status;
2013 RAISE Okc_Api.G_EXCEPTION_ERROR;
2014 END IF;
2015 x_return_status := l_return_status;
2016
2017 -- Bug 4917691: fmiao start
2018 -- Need to change contract status to INCOMPLETE when create/update ppy
2019 -- cascade edit status on to lines
2020 IF (p_ppydv_rec.payment_hdr_id IS NOT NULL) THEN
2021 OPEN chr_id_csr (p_ppydv_rec.payment_hdr_id);
2022 FETCH chr_id_csr INTO l_chr_id;
2023 CLOSE chr_id_csr;
2024
2025 okl_contract_status_pub.cascade_lease_status_edit
2026 (p_api_version => p_api_version,
2027 p_init_msg_list => p_init_msg_list,
2028 x_return_status => x_return_status,
2029 x_msg_count => x_msg_count,
2030 x_msg_data => x_msg_data,
2031 p_chr_id => l_chr_id);
2032
2033 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
2034 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2035 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
2036 raise OKL_API.G_EXCEPTION_ERROR;
2037 End If;
2038 END IF;
2039 -- Bug 4917691: fmiao end
2040
2041 Okc_Api.END_ACTIVITY (x_msg_count
2042 ,x_msg_data );
2043
2044 EXCEPTION
2045 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2046 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2047 (l_api_name,
2048 G_PKG_NAME,
2049 'OKC_API.G_RET_STS_ERROR',
2050 x_msg_count,
2051 x_msg_data,
2052 '_PVT');
2053 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2054 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2055 (l_api_name,
2056 G_PKG_NAME,
2057 'OKC_API.G_RET_STS_UNEXP_ERROR',
2058 x_msg_count,
2059 x_msg_data,
2060 '_PVT');
2061 WHEN OTHERS THEN
2062 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2063 (l_api_name,
2064 G_PKG_NAME,
2065 'OTHERS',
2066 x_msg_count,
2067 x_msg_data,
2068 '_PVT');
2069 END create_party_payment_dtls;
2070
2071 PROCEDURE create_party_payment_dtls(
2072 p_api_version IN NUMBER,
2073 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2074 x_return_status OUT NOCOPY VARCHAR2,
2075 x_msg_count OUT NOCOPY NUMBER,
2076 x_msg_data OUT NOCOPY VARCHAR2,
2077 p_ppydv_tbl IN ppydv_tbl_type,
2078 x_ppydv_tbl OUT NOCOPY ppydv_tbl_type
2079 ) IS
2080 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PARTY_PAYMENT_DTLS';
2081 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2082 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2083 i NUMBER;
2084 BEGIN
2085
2086 IF p_ppydv_tbl.COUNT > 0 THEN
2087 i := p_ppydv_tbl.FIRST;
2088 LOOP
2089 -- call procedure in complex API for a record
2090 create_party_payment_dtls(p_api_version,
2091 p_init_msg_list,
2092 x_return_status,
2093 x_msg_count,
2094 x_msg_data,
2095 p_ppydv_tbl(i),
2096 x_ppydv_tbl(i));
2097 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2098 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2099 l_overall_status := x_return_status;
2100 END IF;
2101 END IF;
2102
2103 EXIT WHEN (i = p_ppydv_tbl.LAST);
2104 i := p_ppydv_tbl.NEXT(i);
2105 END LOOP;
2106
2107 -- return overall status
2108 x_return_status := l_overall_status;
2109 END IF;
2110
2111 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2112 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2113 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2114 RAISE Okl_Api.G_EXCEPTION_ERROR;
2115 END IF;
2116
2117 EXCEPTION
2118 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2119 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2120 (l_api_name,
2121 G_PKG_NAME,
2122 'OKC_API.G_RET_STS_ERROR',
2123 x_msg_count,
2124 x_msg_data,
2125 '_PVT');
2126 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2127 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2128 (l_api_name,
2129 G_PKG_NAME,
2130 'OKC_API.G_RET_STS_UNEXP_ERROR',
2131 x_msg_count,
2132 x_msg_data,
2133 '_PVT');
2134 WHEN OTHERS THEN
2135 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2136 (l_api_name,
2137 G_PKG_NAME,
2138 'OTHERS',
2139 x_msg_count,
2140 x_msg_data,
2141 '_PVT');
2142 END create_party_payment_dtls;
2143
2144 PROCEDURE lock_party_payment_dtls(
2145 p_api_version IN NUMBER,
2146 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2147 x_return_status OUT NOCOPY VARCHAR2,
2148 x_msg_count OUT NOCOPY NUMBER,
2149 x_msg_data OUT NOCOPY VARCHAR2,
2150 p_ppydv_rec IN ppydv_rec_type
2151 ) IS
2152 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_PARTY_PAYMENT_DTLS';
2153 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2154 BEGIN
2155 x_return_status := l_return_status;
2156 -- Call start_activity to create savepoint, check compatibility
2157 -- and initialize message list
2158 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
2159 ,p_init_msg_list
2160 ,'_PVT'
2161 ,x_return_status);
2162 -- Check if activity started successfully
2163 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2164 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2165 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2166 RAISE Okc_Api.G_EXCEPTION_ERROR;
2167 END IF;
2168
2169 Okl_Pyd_Pvt.lock_row (p_api_version,
2170 p_init_msg_list,
2171 x_return_status,
2172 x_msg_count,
2173 x_msg_data,
2174 p_ppydv_rec);
2175
2176 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2177 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2178 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2179 RAISE Okl_Api.G_EXCEPTION_ERROR;
2180 END IF;
2181
2182 Okc_Api.END_ACTIVITY (x_msg_count
2183 ,x_msg_data );
2184
2185 EXCEPTION
2186 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2187 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2188 (l_api_name,
2189 G_PKG_NAME,
2190 'OKC_API.G_RET_STS_ERROR',
2191 x_msg_count,
2192 x_msg_data,
2193 '_PVT');
2194 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2195 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2196 (l_api_name,
2197 G_PKG_NAME,
2198 'OKC_API.G_RET_STS_UNEXP_ERROR',
2199 x_msg_count,
2200 x_msg_data,
2201 '_PVT');
2202 WHEN OTHERS THEN
2203 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2204 (l_api_name,
2205 G_PKG_NAME,
2206 'OTHERS',
2207 x_msg_count,
2208 x_msg_data,
2209 '_PVT');
2210 END lock_party_payment_dtls;
2211
2212 PROCEDURE lock_party_payment_dtls(
2213 p_api_version IN NUMBER,
2214 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2215 x_return_status OUT NOCOPY VARCHAR2,
2216 x_msg_count OUT NOCOPY NUMBER,
2217 x_msg_data OUT NOCOPY VARCHAR2,
2218 p_ppydv_tbl IN ppydv_tbl_type
2219 ) IS
2220 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_PARTY_PAYMENT_DTLS';
2221 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2222 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2223 i NUMBER;
2224 BEGIN
2225
2226 IF p_ppydv_tbl.COUNT > 0 THEN
2227 i := p_ppydv_tbl.FIRST;
2228 LOOP
2229 -- call procedure in complex API for a record
2230 lock_party_payment_dtls(p_api_version,
2231 p_init_msg_list,
2232 x_return_status,
2233 x_msg_count,
2234 x_msg_data,
2235 p_ppydv_tbl(i));
2236 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2237 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2238 l_overall_status := x_return_status;
2239 END IF;
2240 END IF;
2241
2242 EXIT WHEN (i = p_ppydv_tbl.LAST);
2243 i := p_ppydv_tbl.NEXT(i);
2244 END LOOP;
2245
2246 -- return overall status
2247 x_return_status := l_overall_status;
2248 END IF;
2249
2250 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2251 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2252 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2253 RAISE Okl_Api.G_EXCEPTION_ERROR;
2254 END IF;
2255
2256 EXCEPTION
2257 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2258 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2259 (l_api_name,
2260 G_PKG_NAME,
2261 'OKC_API.G_RET_STS_ERROR',
2262 x_msg_count,
2263 x_msg_data,
2264 '_PVT');
2265 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2266 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2267 (l_api_name,
2268 G_PKG_NAME,
2269 'OKC_API.G_RET_STS_UNEXP_ERROR',
2270 x_msg_count,
2271 x_msg_data,
2272 '_PVT');
2273 WHEN OTHERS THEN
2274 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2275 (l_api_name,
2276 G_PKG_NAME,
2277 'OTHERS',
2278 x_msg_count,
2279 x_msg_data,
2280 '_PVT');
2281 END lock_party_payment_dtls;
2282
2283 PROCEDURE delete_party_payment_dtls(
2284 p_api_version IN NUMBER,
2285 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2286 x_return_status OUT NOCOPY VARCHAR2,
2287 x_msg_count OUT NOCOPY NUMBER,
2288 x_msg_data OUT NOCOPY VARCHAR2,
2289 p_ppydv_rec IN ppydv_rec_type
2290 ) IS
2291 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY_PAYMENT_DTLS';
2292 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2293
2294 --Bug# 4959361
2295 CURSOR l_pyd_csr(p_pyd_id IN NUMBER) IS
2296 SELECT pph.cle_id
2297 FROM okl_party_payment_hdr pph,
2298 okl_party_payment_dtls pyd
2299 WHERE pyd.id = p_pyd_id
2300 AND pph.id = pyd.payment_hdr_id;
2301
2302 l_pyd_rec l_pyd_csr%ROWTYPE;
2303 --Bug# 4959361
2304
2305 BEGIN
2306 x_return_status := l_return_status;
2307 -- Call start_activity to create savepoint, check compatibility
2308 -- and initialize message list
2309 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
2310 ,p_init_msg_list
2311 ,'_PVT'
2312 ,x_return_status);
2313 -- Check if activity started successfully
2314 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2315 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2316 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2317 RAISE Okc_Api.G_EXCEPTION_ERROR;
2318 END IF;
2319
2320 --Bug# 4959361
2321 OPEN l_pyd_csr(p_pyd_id => p_ppydv_rec.id);
2322 FETCH l_pyd_csr INTO l_pyd_rec;
2323 CLOSE l_pyd_csr;
2324
2325 IF l_pyd_rec.cle_id IS NOT NULL THEN
2326 OKL_LLA_UTIL_PVT.check_line_update_allowed
2327 (p_api_version => p_api_version,
2328 p_init_msg_list => p_init_msg_list,
2329 x_return_status => x_return_status,
2330 x_msg_count => x_msg_count,
2331 x_msg_data => x_msg_data,
2332 p_cle_id => l_pyd_rec.cle_id);
2333
2334 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2335 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2336 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2337 RAISE OKL_API.G_EXCEPTION_ERROR;
2338 END IF;
2339 END IF;
2340 --Bug# 4959361
2341
2342 Okl_Pyd_Pvt.delete_row(p_api_version,
2343 p_init_msg_list,
2344 x_return_status,
2345 x_msg_count,
2346 x_msg_data,
2347 p_ppydv_rec);
2348
2349 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2350 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2351 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2352 RAISE Okl_Api.G_EXCEPTION_ERROR;
2353 END IF;
2354
2355 Okc_Api.END_ACTIVITY (x_msg_count
2356 ,x_msg_data );
2357
2358 EXCEPTION
2359 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2360 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2361 (l_api_name,
2362 G_PKG_NAME,
2363 'OKC_API.G_RET_STS_ERROR',
2364 x_msg_count,
2365 x_msg_data,
2366 '_PVT');
2367 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2368 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2369 (l_api_name,
2370 G_PKG_NAME,
2371 'OKC_API.G_RET_STS_UNEXP_ERROR',
2372 x_msg_count,
2373 x_msg_data,
2374 '_PVT');
2375 WHEN OTHERS THEN
2376 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2377 (l_api_name,
2378 G_PKG_NAME,
2379 'OTHERS',
2380 x_msg_count,
2381 x_msg_data,
2382 '_PVT');
2383 END delete_party_payment_dtls;
2384
2385 PROCEDURE delete_party_payment_dtls(
2386 p_api_version IN NUMBER,
2387 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2388 x_return_status OUT NOCOPY VARCHAR2,
2389 x_msg_count OUT NOCOPY NUMBER,
2390 x_msg_data OUT NOCOPY VARCHAR2,
2391 p_ppydv_tbl IN ppydv_tbl_type
2392 ) IS
2393 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY_PAYMENT_DTLS';
2394 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2395 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2396 i NUMBER;
2397 BEGIN
2398
2399 IF p_ppydv_tbl.COUNT > 0 THEN
2400 i := p_ppydv_tbl.FIRST;
2401 LOOP
2402 -- call procedure in complex API for a record
2403 delete_party_payment_dtls(p_api_version,
2404 p_init_msg_list,
2405 x_return_status,
2406 x_msg_count,
2407 x_msg_data,
2408 p_ppydv_tbl(i));
2409 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2410 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2411 l_overall_status := x_return_status;
2412 END IF;
2413 END IF;
2414
2415 EXIT WHEN (i = p_ppydv_tbl.LAST);
2416 i := p_ppydv_tbl.NEXT(i);
2417 END LOOP;
2418
2419 -- return overall status
2420 x_return_status := l_overall_status;
2421 END IF;
2422
2423 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2424 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2425 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2426 RAISE Okl_Api.G_EXCEPTION_ERROR;
2427 END IF;
2428
2429 EXCEPTION
2430 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2431 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2432 (l_api_name,
2433 G_PKG_NAME,
2434 'OKC_API.G_RET_STS_ERROR',
2435 x_msg_count,
2436 x_msg_data,
2437 '_PVT');
2438 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2439 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2440 (l_api_name,
2441 G_PKG_NAME,
2442 'OKC_API.G_RET_STS_UNEXP_ERROR',
2443 x_msg_count,
2444 x_msg_data,
2445 '_PVT');
2446 WHEN OTHERS THEN
2447 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2448 (l_api_name,
2449 G_PKG_NAME,
2450 'OTHERS',
2451 x_msg_count,
2452 x_msg_data,
2453 '_PVT');
2454 END delete_party_payment_dtls;
2455
2456 PROCEDURE update_party_payment_dtls(
2457 p_api_version IN NUMBER,
2458 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2459 x_return_status OUT NOCOPY VARCHAR2,
2460 x_msg_count OUT NOCOPY NUMBER,
2461 x_msg_data OUT NOCOPY VARCHAR2,
2462 p_ppydv_rec IN ppydv_rec_type,
2463 x_ppydv_rec OUT NOCOPY ppydv_rec_type
2464 ) IS
2465 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY_PAYMENT_DTLS';
2466 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2467
2468
2469 -- Bug 4917691: fmiao start
2470 CURSOR chr_id_csr (p_payment_hdr_id NUMBER) IS
2471 SELECT distinct(pph.dnz_chr_id)
2472 FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph
2473 WHERE ppy.payment_hdr_id = p_payment_hdr_id
2474 AND ppy.payment_hdr_id = pph.id;
2475 l_chr_id NUMBER;
2476 -- Bug 4917691: fmiao end
2477
2478 --Bug# 4959361
2479 CURSOR l_pyd_csr(p_pyd_id IN NUMBER) IS
2480 SELECT pph.cle_id
2481 FROM okl_party_payment_hdr pph,
2482 okl_party_payment_dtls pyd
2483 WHERE pyd.id = p_pyd_id
2484 AND pph.id = pyd.payment_hdr_id;
2485
2486 l_pyd_rec l_pyd_csr%ROWTYPE;
2487 --Bug# 4959361
2488
2489 BEGIN
2490 x_return_status := l_return_status;
2491 -- Call start_activity to create savepoint, check compatibility
2492 -- and initialize message list
2493 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
2494 ,p_init_msg_list
2495 ,'_PVT'
2496 ,x_return_status);
2497 -- Check if activity started successfully
2498 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2499 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2500 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2501 RAISE Okc_Api.G_EXCEPTION_ERROR;
2502 END IF;
2503
2504 --Bug# 4959361
2505 OPEN l_pyd_csr(p_pyd_id => p_ppydv_rec.id);
2506 FETCH l_pyd_csr INTO l_pyd_rec;
2507 CLOSE l_pyd_csr;
2508
2509 IF l_pyd_rec.cle_id IS NOT NULL THEN
2510 OKL_LLA_UTIL_PVT.check_line_update_allowed
2511 (p_api_version => p_api_version,
2512 p_init_msg_list => p_init_msg_list,
2513 x_return_status => x_return_status,
2514 x_msg_count => x_msg_count,
2515 x_msg_data => x_msg_data,
2516 p_cle_id => l_pyd_rec.cle_id);
2517
2518 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2519 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2520 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2521 RAISE OKL_API.G_EXCEPTION_ERROR;
2522 END IF;
2523 END IF;
2524 --Bug# 4959361
2525
2526 Okl_Pyd_Pvt.update_row(p_api_version,
2527 p_init_msg_list,
2528 x_return_status,
2529 x_msg_count,
2530 x_msg_data,
2531 p_ppydv_rec,
2532 x_ppydv_rec);
2533
2534 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2535 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2536 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2537 RAISE Okl_Api.G_EXCEPTION_ERROR;
2538 END IF;
2539
2540 validate_dtls_record(x_return_status, x_ppydv_rec);
2541 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2542 l_return_status := x_return_status;
2543 RAISE Okc_Api.G_EXCEPTION_ERROR;
2544 END IF;
2545
2546 -- Bug 4917691: fmiao start
2547 -- Need to change contract status to INCOMPLETE when create/update ppy
2548 -- cascade edit status on to lines
2549 IF (p_ppydv_rec.payment_hdr_id IS NOT NULL) THEN
2550 OPEN chr_id_csr (x_ppydv_rec.payment_hdr_id);
2551 FETCH chr_id_csr INTO l_chr_id;
2552 CLOSE chr_id_csr;
2553
2554 okl_contract_status_pub.cascade_lease_status_edit
2555 (p_api_version => p_api_version,
2556 p_init_msg_list => p_init_msg_list,
2557 x_return_status => x_return_status,
2558 x_msg_count => x_msg_count,
2559 x_msg_data => x_msg_data,
2560 p_chr_id => l_chr_id);
2561
2562 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
2563 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2564 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
2565 raise OKL_API.G_EXCEPTION_ERROR;
2566 End If;
2567 END IF;
2568 -- Bug 4917691: fmiao end
2569
2570
2571 Okc_Api.END_ACTIVITY (x_msg_count
2572 ,x_msg_data );
2573
2574 EXCEPTION
2575 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2576 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2577 (l_api_name,
2578 G_PKG_NAME,
2579 'OKC_API.G_RET_STS_ERROR',
2580 x_msg_count,
2581 x_msg_data,
2582 '_PVT');
2583 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2584 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2585 (l_api_name,
2586 G_PKG_NAME,
2587 'OKC_API.G_RET_STS_UNEXP_ERROR',
2588 x_msg_count,
2589 x_msg_data,
2590 '_PVT');
2591 WHEN OTHERS THEN
2592 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2593 (l_api_name,
2594 G_PKG_NAME,
2595 'OTHERS',
2596 x_msg_count,
2597 x_msg_data,
2598 '_PVT');
2599 END update_party_payment_dtls;
2600
2601 PROCEDURE update_party_payment_dtls(
2602 p_api_version IN NUMBER,
2603 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2604 x_return_status OUT NOCOPY VARCHAR2,
2605 x_msg_count OUT NOCOPY NUMBER,
2606 x_msg_data OUT NOCOPY VARCHAR2,
2607 p_ppydv_tbl IN ppydv_tbl_type,
2608 x_ppydv_tbl OUT NOCOPY ppydv_tbl_type
2609 ) IS
2610 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY_PAYMENT_DTLS';
2611 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2612 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2613 i NUMBER;
2614
2615 BEGIN
2616
2617 IF p_ppydv_tbl.COUNT > 0 THEN
2618 i := p_ppydv_tbl.FIRST;
2619 LOOP
2620 -- call procedure in complex API for a record
2621 update_party_payment_dtls(p_api_version,
2622 p_init_msg_list,
2623 x_return_status,
2624 x_msg_count,
2625 x_msg_data,
2626 p_ppydv_tbl(i),
2627 x_ppydv_tbl(i));
2628 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2629 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2630 l_overall_status := x_return_status;
2631 END IF;
2632 END IF;
2633
2634 EXIT WHEN (i = p_ppydv_tbl.LAST);
2635 i := p_ppydv_tbl.NEXT(i);
2636 END LOOP;
2637
2638 -- return overall status
2639 x_return_status := l_overall_status;
2640 END IF;
2641
2642 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2643 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2644 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2645 RAISE Okl_Api.G_EXCEPTION_ERROR;
2646 END IF;
2647
2648 EXCEPTION
2649 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2650 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2651 (l_api_name,
2652 G_PKG_NAME,
2653 'OKC_API.G_RET_STS_ERROR',
2654 x_msg_count,
2655 x_msg_data,
2656 '_PVT');
2657 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2658 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2659 (l_api_name,
2660 G_PKG_NAME,
2661 'OKC_API.G_RET_STS_UNEXP_ERROR',
2662 x_msg_count,
2663 x_msg_data,
2664 '_PVT');
2665 WHEN OTHERS THEN
2666 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2667 (l_api_name,
2668 G_PKG_NAME,
2669 'OTHERS',
2670 x_msg_count,
2671 x_msg_data,
2672 '_PVT');
2673 END update_party_payment_dtls;
2674
2675 PROCEDURE validate_party_payment_dtls(
2676 p_api_version IN NUMBER,
2677 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2678 x_return_status OUT NOCOPY VARCHAR2,
2679 x_msg_count OUT NOCOPY NUMBER,
2680 x_msg_data OUT NOCOPY VARCHAR2,
2681 p_ppydv_rec IN ppydv_rec_type
2682 ) IS
2683 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_PARTY_PAYMENT_DTLS';
2684 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2685 BEGIN
2686 x_return_status := l_return_status;
2687 -- Call start_activity to create savepoint, check compatibility
2688 -- and initialize message list
2689 l_return_status := Okc_Api.START_ACTIVITY (l_api_name
2690 ,p_init_msg_list
2691 ,'_PVT'
2692 ,x_return_status);
2693 -- Check if activity started successfully
2694 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2695 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2696 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2697 RAISE Okc_Api.G_EXCEPTION_ERROR;
2698 END IF;
2699
2700 Okl_Pyd_Pvt.validate_row(p_api_version,
2701 p_init_msg_list,
2702 x_return_status,
2703 x_msg_count,
2704 x_msg_data,
2705 p_ppydv_rec);
2706
2707 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2708 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2709 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2710 RAISE Okl_Api.G_EXCEPTION_ERROR;
2711 END IF;
2712
2713 Okc_Api.END_ACTIVITY (x_msg_count
2714 ,x_msg_data );
2715
2716 EXCEPTION
2717 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2718 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2719 (l_api_name,
2720 G_PKG_NAME,
2721 'OKC_API.G_RET_STS_ERROR',
2722 x_msg_count,
2723 x_msg_data,
2724 '_PVT');
2725 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2726 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2727 (l_api_name,
2728 G_PKG_NAME,
2729 'OKC_API.G_RET_STS_UNEXP_ERROR',
2730 x_msg_count,
2731 x_msg_data,
2732 '_PVT');
2733 WHEN OTHERS THEN
2734 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2735 (l_api_name,
2736 G_PKG_NAME,
2737 'OTHERS',
2738 x_msg_count,
2739 x_msg_data,
2740 '_PVT');
2741 END validate_party_payment_dtls;
2742
2743 PROCEDURE validate_party_payment_dtls(
2744 p_api_version IN NUMBER,
2745 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2746 x_return_status OUT NOCOPY VARCHAR2,
2747 x_msg_count OUT NOCOPY NUMBER,
2748 x_msg_data OUT NOCOPY VARCHAR2,
2749 p_ppydv_tbl IN ppydv_tbl_type
2750 ) IS
2751 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_PARTY_PAYMENT_DTLS';
2752 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2753 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2754 i NUMBER;
2755 BEGIN
2756
2757 IF p_ppydv_tbl.COUNT > 0 THEN
2758 i := p_ppydv_tbl.FIRST;
2759 LOOP
2760 -- call procedure in complex API for a record
2761 validate_party_payment_dtls(p_api_version,
2762 p_init_msg_list,
2763 x_return_status,
2764 x_msg_count,
2765 x_msg_data,
2766 p_ppydv_tbl(i));
2767 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2768 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2769 l_overall_status := x_return_status;
2770 END IF;
2771 END IF;
2772
2773 EXIT WHEN (i = p_ppydv_tbl.LAST);
2774 i := p_ppydv_tbl.NEXT(i);
2775 END LOOP;
2776
2777 -- return overall status
2778 x_return_status := l_overall_status;
2779 END IF;
2780
2781 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2782 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2783 ELSIF x_return_status = Okl_Api.G_RET_STS_ERROR THEN
2784 RAISE Okl_Api.G_EXCEPTION_ERROR;
2785 END IF;
2786
2787 EXCEPTION
2788 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2789 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2790 (l_api_name,
2791 G_PKG_NAME,
2792 'OKC_API.G_RET_STS_ERROR',
2793 x_msg_count,
2794 x_msg_data,
2795 '_PVT');
2796 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2797 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2798 (l_api_name,
2799 G_PKG_NAME,
2800 'OKC_API.G_RET_STS_UNEXP_ERROR',
2801 x_msg_count,
2802 x_msg_data,
2803 '_PVT');
2804 WHEN OTHERS THEN
2805 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2806 (l_api_name,
2807 G_PKG_NAME,
2808 'OTHERS',
2809 x_msg_count,
2810 x_msg_data,
2811 '_PVT');
2812 END validate_party_payment_dtls;
2813
2814 END Okl_Party_Payments_Pvt;