[Home] [Help]
PACKAGE BODY: APPS.OKL_LDB_PVT
Source
1 PACKAGE BODY OKL_LDB_PVT AS
2 /* $Header: OKLSLDBB.pls 120.0 2005/11/30 17:18:01 stmathew noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn'tany
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKC_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKL_PARTY_PAYMENT_HDR_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_pphv_rec IN pphv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN pphv_rec_type IS
100 CURSOR okl_party_payment_hdr_v_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 OBJECT_VERSION_NUMBER,
104 DNZ_CHR_ID,
105 CLE_ID,
106 PPL_ID,
107 PASSTHRU_START_DATE,
108 PAYOUT_BASIS,
109 PAYOUT_BASIS_FORMULA,
110 EFFECTIVE_FROM,
111 EFFECTIVE_TO,
112 PASSTHRU_TERM,
113 PASSTHRU_STREAM_TYPE_ID,
114 CREATED_BY,
115 CREATION_DATE,
116 LAST_UPDATED_BY,
117 LAST_UPDATE_DATE,
118 LAST_UPDATE_LOGIN
119 FROM Okl_Party_Payment_Hdr_V
120 WHERE okl_party_payment_hdr_v.id = p_id;
121 l_okl_party_payment_hdr_v_pk okl_party_payment_hdr_v_pk_csr%ROWTYPE;
122 l_pphv_rec pphv_rec_type;
123 BEGIN
124 x_no_data_found := TRUE;
125 -- Get current database values
126 OPEN okl_party_payment_hdr_v_pk_csr (p_pphv_rec.id);
127 FETCH okl_party_payment_hdr_v_pk_csr INTO
128 l_pphv_rec.id,
129 l_pphv_rec.object_version_number,
130 l_pphv_rec.dnz_chr_id,
131 l_pphv_rec.cle_id,
132 l_pphv_rec.ppl_id,
133 l_pphv_rec.passthru_start_date,
134 l_pphv_rec.payout_basis,
135 l_pphv_rec.payout_basis_formula,
136 l_pphv_rec.effective_from,
137 l_pphv_rec.effective_to,
138 l_pphv_rec.passthru_term,
139 l_pphv_rec.passthru_stream_type_id,
140 l_pphv_rec.created_by,
141 l_pphv_rec.creation_date,
142 l_pphv_rec.last_updated_by,
143 l_pphv_rec.last_update_date,
144 l_pphv_rec.last_update_login;
145 x_no_data_found := okl_party_payment_hdr_v_pk_csr%NOTFOUND;
146 CLOSE okl_party_payment_hdr_v_pk_csr;
147 RETURN(l_pphv_rec);
148 END get_rec;
149
150 ------------------------------------------------------------------
151 -- This version of get_rec sets error messages if no data found --
152 ------------------------------------------------------------------
153 FUNCTION get_rec (
154 p_pphv_rec IN pphv_rec_type,
155 x_return_status OUT NOCOPY VARCHAR2
156 ) RETURN pphv_rec_type IS
157 l_pphv_rec pphv_rec_type;
158 l_row_notfound BOOLEAN := TRUE;
159 BEGIN
160 x_return_status := OKC_API.G_RET_STS_SUCCESS;
161 l_pphv_rec := get_rec(p_pphv_rec, l_row_notfound);
162 IF (l_row_notfound) THEN
163 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
164 x_return_status := OKC_API.G_RET_STS_ERROR;
165 END IF;
166 RETURN(l_pphv_rec);
167 END get_rec;
168 -----------------------------------------------------------
169 -- So we don't have to pass an "l_row_notfound" variable --
170 -----------------------------------------------------------
171 FUNCTION get_rec (
172 p_pphv_rec IN pphv_rec_type
173 ) RETURN pphv_rec_type IS
174 l_row_not_found BOOLEAN := TRUE;
175 BEGIN
176 RETURN(get_rec(p_pphv_rec, l_row_not_found));
177 END get_rec;
178 ---------------------------------------------------------------------------
179 -- FUNCTION get_rec for: OKL_PARTY_PAYMENT_HDR
180 ---------------------------------------------------------------------------
181 FUNCTION get_rec (
182 p_pph_rec IN pph_rec_type,
183 x_no_data_found OUT NOCOPY BOOLEAN
184 ) RETURN pph_rec_type IS
185 CURSOR okl_party_payment_hdr_pk_csr (p_id IN NUMBER) IS
186 SELECT
187 ID,
188 OBJECT_VERSION_NUMBER,
189 DNZ_CHR_ID,
190 CLE_ID,
191 PPL_ID,
192 PASSTHRU_START_DATE,
193 PAYOUT_BASIS,
194 PAYOUT_BASIS_FORMULA,
195 EFFECTIVE_FROM,
196 EFFECTIVE_TO,
197 PASSTHRU_TERM,
198 PASSTHRU_STREAM_TYPE_ID,
199 CREATED_BY,
200 CREATION_DATE,
201 LAST_UPDATED_BY,
202 LAST_UPDATE_DATE,
203 LAST_UPDATE_LOGIN
204 FROM Okl_Party_Payment_Hdr
205 WHERE okl_party_payment_hdr.id = p_id;
206 l_okl_party_payment_hdr_pk okl_party_payment_hdr_pk_csr%ROWTYPE;
207 l_pph_rec pph_rec_type;
208 BEGIN
209 x_no_data_found := TRUE;
210 -- Get current database values
211 OPEN okl_party_payment_hdr_pk_csr (p_pph_rec.id);
212 FETCH okl_party_payment_hdr_pk_csr INTO
213 l_pph_rec.id,
214 l_pph_rec.object_version_number,
215 l_pph_rec.dnz_chr_id,
216 l_pph_rec.cle_id,
217 l_pph_rec.ppl_id,
218 l_pph_rec.passthru_start_date,
219 l_pph_rec.payout_basis,
220 l_pph_rec.payout_basis_formula,
221 l_pph_rec.effective_from,
222 l_pph_rec.effective_to,
223 l_pph_rec.passthru_term,
224 l_pph_rec.passthru_stream_type_id,
225 l_pph_rec.created_by,
226 l_pph_rec.creation_date,
227 l_pph_rec.last_updated_by,
228 l_pph_rec.last_update_date,
229 l_pph_rec.last_update_login;
230 x_no_data_found := okl_party_payment_hdr_pk_csr%NOTFOUND;
231 CLOSE okl_party_payment_hdr_pk_csr;
232 RETURN(l_pph_rec);
233 END get_rec;
234
235 ------------------------------------------------------------------
236 -- This version of get_rec sets error messages if no data found --
237 ------------------------------------------------------------------
238 FUNCTION get_rec (
239 p_pph_rec IN pph_rec_type,
240 x_return_status OUT NOCOPY VARCHAR2
241 ) RETURN pph_rec_type IS
242 l_pph_rec pph_rec_type;
243 l_row_notfound BOOLEAN := TRUE;
244 BEGIN
245 x_return_status := OKC_API.G_RET_STS_SUCCESS;
246 l_pph_rec := get_rec(p_pph_rec, l_row_notfound);
247 IF (l_row_notfound) THEN
248 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
249 x_return_status := OKC_API.G_RET_STS_ERROR;
250 END IF;
251 RETURN(l_pph_rec);
252 END get_rec;
253 -----------------------------------------------------------
254 -- So we don't have to pass an "l_row_notfound" variable --
255 -----------------------------------------------------------
256 FUNCTION get_rec (
257 p_pph_rec IN pph_rec_type
258 ) RETURN pph_rec_type IS
259 l_row_not_found BOOLEAN := TRUE;
260 BEGIN
261 RETURN(get_rec(p_pph_rec, l_row_not_found));
262 END get_rec;
263 ---------------------------------------------------------------------------
264 -- FUNCTION null_out_defaults for: OKL_PARTY_PAYMENT_HDR_V
265 ---------------------------------------------------------------------------
266 FUNCTION null_out_defaults (
267 p_pphv_rec IN pphv_rec_type
268 ) RETURN pphv_rec_type IS
269 l_pphv_rec pphv_rec_type := p_pphv_rec;
270 BEGIN
271 IF (l_pphv_rec.id = OKC_API.G_MISS_NUM ) THEN
272 l_pphv_rec.id := NULL;
273 END IF;
274 IF (l_pphv_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
275 l_pphv_rec.object_version_number := NULL;
276 END IF;
277 IF (l_pphv_rec.dnz_chr_id = OKC_API.G_MISS_NUM ) THEN
278 l_pphv_rec.dnz_chr_id := NULL;
279 END IF;
280 IF (l_pphv_rec.cle_id = OKC_API.G_MISS_NUM ) THEN
281 l_pphv_rec.cle_id := NULL;
282 END IF;
283 IF (l_pphv_rec.ppl_id = OKC_API.G_MISS_NUM ) THEN
284 l_pphv_rec.ppl_id := NULL;
285 END IF;
286 IF (l_pphv_rec.passthru_start_date = OKC_API.G_MISS_DATE ) THEN
287 l_pphv_rec.passthru_start_date := NULL;
288 END IF;
289 IF (l_pphv_rec.payout_basis = OKC_API.G_MISS_CHAR ) THEN
290 l_pphv_rec.payout_basis := NULL;
291 END IF;
292 IF (l_pphv_rec.payout_basis_formula = OKC_API.G_MISS_CHAR ) THEN
293 l_pphv_rec.payout_basis_formula := NULL;
294 END IF;
295 IF (l_pphv_rec.effective_from = OKC_API.G_MISS_DATE ) THEN
296 l_pphv_rec.effective_from := NULL;
297 END IF;
298 IF (l_pphv_rec.effective_to = OKC_API.G_MISS_DATE ) THEN
299 l_pphv_rec.effective_to := NULL;
300 END IF;
301 IF (l_pphv_rec.passthru_term = OKC_API.G_MISS_CHAR ) THEN
302 l_pphv_rec.passthru_term := NULL;
303 END IF;
304 IF (l_pphv_rec.passthru_stream_type_id = OKC_API.G_MISS_NUM ) THEN
305 l_pphv_rec.passthru_stream_type_id := NULL;
306 END IF;
307 IF (l_pphv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
308 l_pphv_rec.created_by := NULL;
309 END IF;
310 IF (l_pphv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
311 l_pphv_rec.creation_date := NULL;
312 END IF;
313 IF (l_pphv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
314 l_pphv_rec.last_updated_by := NULL;
315 END IF;
316 IF (l_pphv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
317 l_pphv_rec.last_update_date := NULL;
318 END IF;
319 IF (l_pphv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
320 l_pphv_rec.last_update_login := NULL;
321 END IF;
322 RETURN(l_pphv_rec);
323 END null_out_defaults;
324 ---------------------------------
325 -- Validate_Attributes for: ID --
326 ---------------------------------
327 PROCEDURE validate_id(
328 x_return_status OUT NOCOPY VARCHAR2,
329 p_id IN NUMBER) IS
330 BEGIN
331 x_return_status := OKC_API.G_RET_STS_SUCCESS;
332 IF (p_id = OKC_API.G_MISS_NUM OR
333 p_id IS NULL)
334 THEN
335 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
336 x_return_status := OKC_API.G_RET_STS_ERROR;
337 RAISE G_EXCEPTION_HALT_VALIDATION;
338 END IF;
339 EXCEPTION
340 WHEN G_EXCEPTION_HALT_VALIDATION THEN
341 null;
342 WHEN OTHERS THEN
343 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
344 ,p_msg_name => G_UNEXPECTED_ERROR
345 ,p_token1 => G_SQLCODE_TOKEN
346 ,p_token1_value => SQLCODE
347 ,p_token2 => G_SQLERRM_TOKEN
348 ,p_token2_value => SQLERRM);
349 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
350 END validate_id;
351 ---------------------------------
352 -- Validate_Attributes for: DNZ_CHR_ID --
353 ---------------------------------
354 PROCEDURE validate_dnz_chr_id(
355 x_return_status OUT NOCOPY VARCHAR2,
356 p_dnz_chr_id IN NUMBER) IS
357 BEGIN
358 x_return_status := OKC_API.G_RET_STS_SUCCESS;
359 IF (p_dnz_chr_id = OKC_API.G_MISS_NUM OR
360 p_dnz_chr_id IS NULL)
361 THEN
362 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'dnz_chr_id');
363 x_return_status := OKC_API.G_RET_STS_ERROR;
364 RAISE G_EXCEPTION_HALT_VALIDATION;
365 END IF;
366 EXCEPTION
367 WHEN G_EXCEPTION_HALT_VALIDATION THEN
368 null;
369 WHEN OTHERS THEN
370 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
371 ,p_msg_name => G_UNEXPECTED_ERROR
372 ,p_token1 => G_SQLCODE_TOKEN
373 ,p_token1_value => SQLCODE
374 ,p_token2 => G_SQLERRM_TOKEN
375 ,p_token2_value => SQLERRM);
376 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
377 END validate_dnz_chr_id;
378 ---------------------------------
379 -- Validate_Attributes for: CLEID --
380 ---------------------------------
381 PROCEDURE validate_cle_id(
382 x_return_status OUT NOCOPY VARCHAR2,
383 p_cle_id IN NUMBER) IS
384 BEGIN
385 x_return_status := OKC_API.G_RET_STS_SUCCESS;
386 IF (p_cle_id = OKC_API.G_MISS_NUM OR
387 p_cle_id IS NULL)
388 THEN
389 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'cle_id');
390 x_return_status := OKC_API.G_RET_STS_ERROR;
391 RAISE G_EXCEPTION_HALT_VALIDATION;
392 END IF;
393 EXCEPTION
394 WHEN G_EXCEPTION_HALT_VALIDATION THEN
395 null;
396 WHEN OTHERS THEN
397 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
398 ,p_msg_name => G_UNEXPECTED_ERROR
399 ,p_token1 => G_SQLCODE_TOKEN
400 ,p_token1_value => SQLCODE
401 ,p_token2 => G_SQLERRM_TOKEN
402 ,p_token2_value => SQLERRM);
403 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
404 END validate_cle_id;
405 ----------------------------------------------------
406 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
407 ----------------------------------------------------
408 PROCEDURE validate_object_version_number(
409 x_return_status OUT NOCOPY VARCHAR2,
410 p_object_version_number IN NUMBER) IS
411 BEGIN
412 x_return_status := OKC_API.G_RET_STS_SUCCESS;
413 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
414 p_object_version_number IS NULL)
415 THEN
416 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
417 x_return_status := OKC_API.G_RET_STS_ERROR;
418 RAISE G_EXCEPTION_HALT_VALIDATION;
419 END IF;
420 EXCEPTION
421 WHEN G_EXCEPTION_HALT_VALIDATION THEN
422 null;
423 WHEN OTHERS THEN
424 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
425 ,p_msg_name => G_UNEXPECTED_ERROR
426 ,p_token1 => G_SQLCODE_TOKEN
427 ,p_token1_value => SQLCODE
428 ,p_token2 => G_SQLERRM_TOKEN
429 ,p_token2_value => SQLERRM);
430 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
431 END validate_object_version_number;
432 ---------------------------------------------
433 -- Validate_Attributes for: EFFECTIVE_FROM --
434 ---------------------------------------------
435 PROCEDURE validate_effective_from(
436 x_return_status OUT NOCOPY VARCHAR2,
437 p_effective_from IN DATE) IS
438 BEGIN
439 x_return_status := OKC_API.G_RET_STS_SUCCESS;
440 IF (p_effective_from = OKC_API.G_MISS_DATE OR
441 p_effective_from IS NULL)
442 THEN
443 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'effective_from');
444 x_return_status := OKC_API.G_RET_STS_ERROR;
445 RAISE G_EXCEPTION_HALT_VALIDATION;
446 END IF;
447 EXCEPTION
448 WHEN G_EXCEPTION_HALT_VALIDATION THEN
449 null;
450 WHEN OTHERS THEN
451 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
452 ,p_msg_name => G_UNEXPECTED_ERROR
453 ,p_token1 => G_SQLCODE_TOKEN
454 ,p_token1_value => SQLCODE
455 ,p_token2 => G_SQLERRM_TOKEN
456 ,p_token2_value => SQLERRM);
457 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
458 END validate_effective_from;
459 ----------------------------------------------------
460 -- Validate_Attributes for: payout_basis --
461 ----------------------------------------------------
462 PROCEDURE validate_payout_basis(
463 x_return_status OUT NOCOPY VARCHAR2,
464 p_payout_basis IN VARCHAR2) IS
465
466 CURSOR l_payout_basis_csr (p_payout_basis in varchar2) IS
467 SELECT 'Y'
468 From FND_LOOKUPS
469 Where lookup_type = 'OKL_PAYOUT_BASIS'
470 And nvl(enabled_flag,'N') = 'Y'
471 And sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
472 And lookup_code = p_payout_basis;
473
474 l_exists varchar2(1) default 'N';
475
476 BEGIN
477 x_return_status := OKL_API.G_RET_STS_SUCCESS;
478 IF (p_payout_basis = OKL_API.G_MISS_CHAR OR
479 p_payout_basis IS NULL)
480 THEN
481 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'payout_basis');
482 x_return_status := OKC_API.G_RET_STS_ERROR;
483 RAISE G_EXCEPTION_HALT_VALIDATION;
484 ELSIF (p_payout_basis <> OKL_API.G_MISS_CHAR AND p_payout_basis is NOT NULL) then
485 l_exists := 'N';
486 Open l_payout_basis_csr (p_payout_basis => p_payout_basis);
487 Fetch l_payout_basis_csr into l_exists;
488 If l_payout_basis_csr%NOTFOUND then
489 Null;
490 End If;
491 Close l_payout_basis_csr;
492 IF l_exists = 'N' then
493 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Payout Basis');
494 x_return_status := OKL_API.G_RET_STS_ERROR;
495 RAISE G_EXCEPTION_HALT_VALIDATION;
496 END IF;
497
498 END IF;
499 EXCEPTION
500 WHEN G_EXCEPTION_HALT_VALIDATION THEN
501 null;
502 WHEN OTHERS THEN
503 If l_payout_basis_csr%ISOPEN then
504 close l_payout_basis_csr;
505 End If;
506 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
507 ,p_msg_name => G_UNEXPECTED_ERROR
508 ,p_token1 => G_SQLCODE_TOKEN
509 ,p_token1_value => SQLCODE
510 ,p_token2 => G_SQLERRM_TOKEN
511 ,p_token2_value => SQLERRM);
512 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
513 END validate_payout_basis;
514
515 ---------------------------------------------------------------------------
516 -- FUNCTION Validate_Attributes
517 ---------------------------------------------------------------------------
518 -----------------------------------------------------
519 -- Validate_Attributes for:OKL_PARTY_PAYMENT_HDR_V --
520 -----------------------------------------------------
521 FUNCTION Validate_Attributes (
522 p_pphv_rec IN pphv_rec_type
523 ) RETURN VARCHAR2 IS
524 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
525 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
526 BEGIN
527 -----------------------------
528 -- Column Level Validation --
529 -----------------------------
530 -- ***
531 -- id
532 -- ***
533 validate_id(x_return_status, p_pphv_rec.id);
534 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
535 l_return_status := x_return_status;
536 RAISE G_EXCEPTION_HALT_VALIDATION;
537 END IF;
538
539 -- ***
540 -- object_version_number
541 -- ***
542 validate_object_version_number(x_return_status, p_pphv_rec.object_version_number);
543 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
544 l_return_status := x_return_status;
545 RAISE G_EXCEPTION_HALT_VALIDATION;
546 END IF;
547
548 -- ***
549 -- payout_basis
550 -- ***
551 validate_payout_basis (x_return_status, p_pphv_rec.payout_basis);
552 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
553 l_return_status := x_return_status;
554 RAISE G_EXCEPTION_HALT_VALIDATION;
555 END IF;
556
557 -- ***
558 -- effective_from
559 -- ***
560 validate_effective_from (x_return_status, p_pphv_rec.effective_from );
561 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
562 l_return_status := x_return_status;
563 RAISE G_EXCEPTION_HALT_VALIDATION;
564 END IF;
565
566 RETURN(l_return_status);
567 EXCEPTION
568 WHEN G_EXCEPTION_HALT_VALIDATION THEN
569 RETURN(l_return_status);
570 WHEN OTHERS THEN
571 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
572 ,p_msg_name => G_UNEXPECTED_ERROR
573 ,p_token1 => G_SQLCODE_TOKEN
574 ,p_token1_value => SQLCODE
575 ,p_token2 => G_SQLERRM_TOKEN
576 ,p_token2_value => SQLERRM);
577 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
578 RETURN(l_return_status);
579 END Validate_Attributes;
580 ---------------------------------------------------------------------------
581 -- PROCEDURE Validate_Record
582 ---------------------------------------------------------------------------
583 -------------------------------------------------
584 -- Validate Record for:OKL_PARTY_PAYMENT_HDR_V --
585 -------------------------------------------------
586 FUNCTION Validate_Record (
587 p_pphv_rec IN pphv_rec_type,
588 p_db_pphv_rec IN pphv_rec_type
589 ) RETURN VARCHAR2 IS
590 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
591 BEGIN
592 RETURN (l_return_status);
593 END Validate_Record;
594 --
595 FUNCTION Validate_Record (
596 p_pphv_rec IN pphv_rec_type
597 ) RETURN VARCHAR2 IS
598 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
599 l_db_pphv_rec pphv_rec_type := get_rec(p_pphv_rec);
600 BEGIN
601 l_return_status := Validate_Record(p_pphv_rec => p_pphv_rec,
602 p_db_pphv_rec => l_db_pphv_rec);
603 RETURN (l_return_status);
604 END Validate_Record;
605
606 ---------------------------------------------------------------------------
607 -- PROCEDURE Migrate
608 ---------------------------------------------------------------------------
609 PROCEDURE migrate (
610 p_from IN pphv_rec_type,
611 p_to IN OUT NOCOPY pph_rec_type
612 ) IS
613 BEGIN
614 p_to.id := p_from.id;
615 p_to.object_version_number := p_from.object_version_number;
616 p_to.dnz_chr_id := p_from.dnz_chr_id;
617 p_to.cle_id := p_from.cle_id;
618 p_to.ppl_id := p_from.ppl_id;
619 p_to.passthru_start_date := p_from.passthru_start_date;
620 p_to.payout_basis := p_from.payout_basis;
621 p_to.payout_basis_formula := p_from.payout_basis_formula;
622 p_to.effective_from := p_from.effective_from;
623 p_to.effective_to := p_from.effective_to;
624 p_to.passthru_term := p_from.passthru_term;
625 p_to.passthru_stream_type_id := p_from.passthru_stream_type_id;
626 p_to.created_by := p_from.created_by;
627 p_to.creation_date := p_from.creation_date;
628 p_to.last_updated_by := p_from.last_updated_by;
629 p_to.last_update_date := p_from.last_update_date;
630 p_to.last_update_login := p_from.last_update_login;
631 END migrate;
632 PROCEDURE migrate (
633 p_from IN pph_rec_type,
634 p_to IN OUT NOCOPY pphv_rec_type
635 ) IS
636 BEGIN
637 p_to.id := p_from.id;
638 p_to.object_version_number := p_from.object_version_number;
639 p_to.dnz_chr_id := p_from.dnz_chr_id;
640 p_to.cle_id := p_from.cle_id;
641 p_to.ppl_id := p_from.ppl_id;
642 p_to.passthru_start_date := p_from.passthru_start_date;
643 p_to.payout_basis := p_from.payout_basis;
644 p_to.payout_basis_formula := p_from.payout_basis_formula;
645 p_to.effective_from := p_from.effective_from;
646 p_to.effective_to := p_from.effective_to;
647 p_to.passthru_term := p_from.passthru_term;
648 p_to.passthru_stream_type_id := p_from.passthru_stream_type_id;
649 p_to.created_by := p_from.created_by;
650 p_to.creation_date := p_from.creation_date;
651 p_to.last_updated_by := p_from.last_updated_by;
652 p_to.last_update_date := p_from.last_update_date;
653 p_to.last_update_login := p_from.last_update_login;
654 END migrate;
655 ---------------------------------------------------------------------------
656 -- PROCEDURE validate_row
657 ---------------------------------------------------------------------------
658 ----------------------------------------------
659 -- validate_row for:OKL_PARTY_PAYMENT_HDR_V --
660 ----------------------------------------------
661 PROCEDURE validate_row(
662 p_api_version IN NUMBER,
663 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
664 x_return_status OUT NOCOPY VARCHAR2,
665 x_msg_count OUT NOCOPY NUMBER,
666 x_msg_data OUT NOCOPY VARCHAR2,
667 p_pphv_rec IN pphv_rec_type) IS
668
669 l_api_version CONSTANT NUMBER := 1;
670 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
671 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
672 l_pphv_rec pphv_rec_type := p_pphv_rec;
673 l_pph_rec pph_rec_type;
674 l_pph_rec pph_rec_type;
675 BEGIN
676 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
677 G_PKG_NAME,
678 p_init_msg_list,
679 l_api_version,
680 p_api_version,
681 '_PVT',
682 x_return_status);
683 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
684 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
685 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
686 RAISE OKC_API.G_EXCEPTION_ERROR;
687 END IF;
688 --- Validate all non-missing attributes (Item Level Validation)
689 l_return_status := Validate_Attributes(l_pphv_rec);
690 --- If any errors happen abort API
691 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
692 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
693 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
694 RAISE OKC_API.G_EXCEPTION_ERROR;
695 END IF;
696 l_return_status := Validate_Record(l_pphv_rec);
697 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
698 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
699 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
700 RAISE OKC_API.G_EXCEPTION_ERROR;
701 END IF;
702 x_return_status := l_return_status;
703 EXCEPTION
704 WHEN OKC_API.G_EXCEPTION_ERROR THEN
705 x_return_status := OKC_API.HANDLE_EXCEPTIONS
706 (
707 l_api_name,
708 G_PKG_NAME,
709 'OKC_API.G_RET_STS_ERROR',
710 x_msg_count,
711 x_msg_data,
712 '_PVT'
713 );
714 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
715 x_return_status := OKC_API.HANDLE_EXCEPTIONS
716 (
717 l_api_name,
718 G_PKG_NAME,
719 'OKC_API.G_RET_STS_UNEXP_ERROR',
720 x_msg_count,
721 x_msg_data,
722 '_PVT'
723 );
724 WHEN OTHERS THEN
725 x_return_status := OKC_API.HANDLE_EXCEPTIONS
726 (
727 l_api_name,
728 G_PKG_NAME,
729 'OTHERS',
730 x_msg_count,
731 x_msg_data,
732 '_PVT'
733 );
734 END validate_row;
735 ---------------------------------------------------------
736 -- PL/SQL TBL validate_row for:OKL_PARTY_PAYMENT_HDR_V --
737 ---------------------------------------------------------
738 PROCEDURE validate_row(
739 p_api_version IN NUMBER,
740 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
741 x_return_status OUT NOCOPY VARCHAR2,
742 x_msg_count OUT NOCOPY NUMBER,
743 x_msg_data OUT NOCOPY VARCHAR2,
744 p_pphv_tbl IN pphv_tbl_type,
745 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
746
747 l_api_version CONSTANT NUMBER := 1;
748 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
749 i NUMBER := 0;
750 BEGIN
751 OKC_API.init_msg_list(p_init_msg_list);
752 -- Make sure PL/SQL table has records in it before passing
753 IF (p_pphv_tbl.COUNT > 0) THEN
754 i := p_pphv_tbl.FIRST;
755 LOOP
756 DECLARE
757 l_error_rec OKC_API.ERROR_REC_TYPE;
758 BEGIN
759 l_error_rec.api_name := l_api_name;
760 l_error_rec.api_package := G_PKG_NAME;
761 l_error_rec.idx := i;
762 validate_row (
763 p_api_version => p_api_version,
764 p_init_msg_list => OKC_API.G_FALSE,
765 x_return_status => l_error_rec.error_type,
766 x_msg_count => l_error_rec.msg_count,
767 x_msg_data => l_error_rec.msg_data,
768 p_pphv_rec => p_pphv_tbl(i));
769 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
770 l_error_rec.sqlcode := SQLCODE;
771 load_error_tbl(l_error_rec, px_error_tbl);
772 ELSE
773 x_msg_count := l_error_rec.msg_count;
774 x_msg_data := l_error_rec.msg_data;
775 END IF;
776 EXCEPTION
777 WHEN OKC_API.G_EXCEPTION_ERROR THEN
778 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
779 l_error_rec.sqlcode := SQLCODE;
780 load_error_tbl(l_error_rec, px_error_tbl);
781 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
782 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
783 l_error_rec.sqlcode := SQLCODE;
784 load_error_tbl(l_error_rec, px_error_tbl);
785 WHEN OTHERS THEN
786 l_error_rec.error_type := 'OTHERS';
787 l_error_rec.sqlcode := SQLCODE;
788 load_error_tbl(l_error_rec, px_error_tbl);
789 END;
790 EXIT WHEN (i = p_pphv_tbl.LAST);
791 i := p_pphv_tbl.NEXT(i);
792 END LOOP;
793 END IF;
794 -- Loop through the error_tbl to find the error with the highest severity
795 -- and return it.
796 x_return_status := find_highest_exception(px_error_tbl);
797 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
798 EXCEPTION
799 WHEN OKC_API.G_EXCEPTION_ERROR THEN
800 x_return_status := OKC_API.HANDLE_EXCEPTIONS
801 (
802 l_api_name,
803 G_PKG_NAME,
804 'OKC_API.G_RET_STS_ERROR',
805 x_msg_count,
806 x_msg_data,
807 '_PVT'
808 );
809 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
810 x_return_status := OKC_API.HANDLE_EXCEPTIONS
811 (
812 l_api_name,
813 G_PKG_NAME,
814 'OKC_API.G_RET_STS_UNEXP_ERROR',
815 x_msg_count,
816 x_msg_data,
817 '_PVT'
818 );
819 WHEN OTHERS THEN
820 x_return_status := OKC_API.HANDLE_EXCEPTIONS
821 (
822 l_api_name,
823 G_PKG_NAME,
824 'OTHERS',
825 x_msg_count,
826 x_msg_data,
827 '_PVT'
828 );
829 END validate_row;
830
831 ---------------------------------------------------------
832 -- PL/SQL TBL validate_row for:OKL_PARTY_PAYMENT_HDR_V --
833 ---------------------------------------------------------
834 PROCEDURE validate_row(
835 p_api_version IN NUMBER,
836 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
837 x_return_status OUT NOCOPY VARCHAR2,
838 x_msg_count OUT NOCOPY NUMBER,
839 x_msg_data OUT NOCOPY VARCHAR2,
840 p_pphv_tbl IN pphv_tbl_type) IS
841
842 l_api_version CONSTANT NUMBER := 1;
843 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
844 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
845 l_error_tbl OKC_API.ERROR_TBL_TYPE;
846 BEGIN
847 OKC_API.init_msg_list(p_init_msg_list);
848 -- Make sure PL/SQL table has records in it before passing
849 IF (p_pphv_tbl.COUNT > 0) THEN
850 validate_row (
851 p_api_version => p_api_version,
852 p_init_msg_list => OKC_API.G_FALSE,
853 x_return_status => x_return_status,
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data,
856 p_pphv_tbl => p_pphv_tbl,
857 px_error_tbl => l_error_tbl);
858 END IF;
859 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
860 EXCEPTION
861 WHEN OKC_API.G_EXCEPTION_ERROR THEN
862 x_return_status := OKC_API.HANDLE_EXCEPTIONS
863 (
864 l_api_name,
865 G_PKG_NAME,
866 'OKC_API.G_RET_STS_ERROR',
867 x_msg_count,
868 x_msg_data,
869 '_PVT'
870 );
871 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
872 x_return_status := OKC_API.HANDLE_EXCEPTIONS
873 (
874 l_api_name,
875 G_PKG_NAME,
876 'OKC_API.G_RET_STS_UNEXP_ERROR',
877 x_msg_count,
878 x_msg_data,
879 '_PVT'
880 );
881 WHEN OTHERS THEN
882 x_return_status := OKC_API.HANDLE_EXCEPTIONS
883 (
884 l_api_name,
885 G_PKG_NAME,
886 'OTHERS',
887 x_msg_count,
888 x_msg_data,
889 '_PVT'
890 );
891 END validate_row;
892
893 ---------------------------------------------------------------------------
894 -- PROCEDURE insert_row
895 ---------------------------------------------------------------------------
896 ------------------------------------------
897 -- insert_row for:OKL_PARTY_PAYMENT_HDR --
898 ------------------------------------------
899 PROCEDURE insert_row(
900 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
901 x_return_status OUT NOCOPY VARCHAR2,
902 x_msg_count OUT NOCOPY NUMBER,
903 x_msg_data OUT NOCOPY VARCHAR2,
904 p_pph_rec IN pph_rec_type,
905 x_pph_rec OUT NOCOPY pph_rec_type) IS
906
907 l_api_version CONSTANT NUMBER := 1;
908 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
909 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
910 l_pph_rec pph_rec_type := p_pph_rec;
911 l_def_pph_rec pph_rec_type;
912 ----------------------------------------------
913 -- Set_Attributes for:OKL_PARTY_PAYMENT_HDR --
914 ----------------------------------------------
915 FUNCTION Set_Attributes (
916 p_pph_rec IN pph_rec_type,
917 x_pph_rec OUT NOCOPY pph_rec_type
918 ) RETURN VARCHAR2 IS
919 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
920 BEGIN
921 x_pph_rec := p_pph_rec;
922 RETURN(l_return_status);
923 END Set_Attributes;
924 BEGIN
925 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
926 p_init_msg_list,
927 '_PVT',
928 x_return_status);
929 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
930 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
931 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
932 RAISE OKC_API.G_EXCEPTION_ERROR;
933 END IF;
934 --- Setting item atributes
935 l_return_status := Set_Attributes(
936 p_pph_rec, -- IN
937 l_pph_rec); -- OUT
938 --- If any errors happen abort API
939 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
940 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
941 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
942 RAISE OKC_API.G_EXCEPTION_ERROR;
943 END IF;
944
945 ---Default effective_from to contract start date ---
946 SELECT start_date INTO l_pph_rec.effective_from
947 FROM okc_k_headers_b
948 WHERE id = l_pph_rec.dnz_chr_id;
949
950 INSERT INTO OKL_PARTY_PAYMENT_HDR(
951 id,
952 object_version_number,
953 dnz_chr_id,
954 cle_id,
955 ppl_id,
956 passthru_start_date,
957 payout_basis,
958 payout_basis_formula,
959 effective_from,
960 effective_to,
961 passthru_term,
962 passthru_stream_type_id,
963 created_by,
964 creation_date,
965 last_updated_by,
966 last_update_date,
967 last_update_login)
968 VALUES (
969 l_pph_rec.id,
970 l_pph_rec.object_version_number,
971 l_pph_rec.dnz_chr_id,
972 l_pph_rec.cle_id,
973 l_pph_rec.ppl_id,
974 l_pph_rec.passthru_start_date,
975 l_pph_rec.payout_basis,
976 l_pph_rec.payout_basis_formula,
977 l_pph_rec.effective_from,
978 l_pph_rec.effective_to,
979 l_pph_rec.passthru_term,
980 l_pph_rec.passthru_stream_type_id,
981 l_pph_rec.created_by,
982 l_pph_rec.creation_date,
983 l_pph_rec.last_updated_by,
984 l_pph_rec.last_update_date,
985 l_pph_rec.last_update_login);
986 -- Set OUT values
987 x_pph_rec := l_pph_rec;
988 x_return_status := l_return_status;
989 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
990 EXCEPTION
991 WHEN OKC_API.G_EXCEPTION_ERROR THEN
992 x_return_status := OKC_API.HANDLE_EXCEPTIONS
993 (
994 l_api_name,
995 G_PKG_NAME,
996 'OKC_API.G_RET_STS_ERROR',
997 x_msg_count,
998 x_msg_data,
999 '_PVT'
1000 );
1001 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1002 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1003 (
1004 l_api_name,
1005 G_PKG_NAME,
1006 'OKC_API.G_RET_STS_UNEXP_ERROR',
1007 x_msg_count,
1008 x_msg_data,
1009 '_PVT'
1010 );
1011 WHEN OTHERS THEN
1012 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1013 (
1014 l_api_name,
1015 G_PKG_NAME,
1016 'OTHERS',
1017 x_msg_count,
1018 x_msg_data,
1019 '_PVT'
1020 );
1021 END insert_row;
1022 ---------------------------------------------
1023 -- insert_row for :OKL_PARTY_PAYMENT_HDR_V --
1024 ---------------------------------------------
1025 PROCEDURE insert_row(
1026 p_api_version IN NUMBER,
1027 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1028 x_return_status OUT NOCOPY VARCHAR2,
1029 x_msg_count OUT NOCOPY NUMBER,
1030 x_msg_data OUT NOCOPY VARCHAR2,
1031 p_pphv_rec IN pphv_rec_type,
1032 x_pphv_rec OUT NOCOPY pphv_rec_type) IS
1033
1034 l_api_version CONSTANT NUMBER := 1;
1035 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1036 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1037 l_pphv_rec pphv_rec_type := p_pphv_rec;
1038 l_def_pphv_rec pphv_rec_type;
1039 l_pph_rec pph_rec_type;
1040 lx_pph_rec pph_rec_type;
1041 -------------------------------
1042 -- FUNCTION fill_who_columns --
1043 -------------------------------
1044 FUNCTION fill_who_columns (
1045 p_pphv_rec IN pphv_rec_type
1046 ) RETURN pphv_rec_type IS
1047 l_pphv_rec pphv_rec_type := p_pphv_rec;
1048 BEGIN
1049 l_pphv_rec.CREATION_DATE := SYSDATE;
1050 l_pphv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1051 l_pphv_rec.LAST_UPDATE_DATE := l_pphv_rec.CREATION_DATE;
1052 l_pphv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1053 l_pphv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1054 RETURN(l_pphv_rec);
1055 END fill_who_columns;
1056 ------------------------------------------------
1057 -- Set_Attributes for:OKL_PARTY_PAYMENT_HDR_V --
1058 ------------------------------------------------
1059 FUNCTION Set_Attributes (
1060 p_pphv_rec IN pphv_rec_type,
1061 x_pphv_rec OUT NOCOPY pphv_rec_type
1062 ) RETURN VARCHAR2 IS
1063 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1064 BEGIN
1065 x_pphv_rec := p_pphv_rec;
1066 x_pphv_rec.OBJECT_VERSION_NUMBER := 1;
1067 RETURN(l_return_status);
1068 END Set_Attributes;
1069 BEGIN
1070 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1071 G_PKG_NAME,
1072 p_init_msg_list,
1073 l_api_version,
1074 p_api_version,
1075 '_PVT',
1076 x_return_status);
1077 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1078 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1079 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1080 RAISE OKC_API.G_EXCEPTION_ERROR;
1081 END IF;
1082 l_pphv_rec := null_out_defaults(p_pphv_rec);
1083 -- Set primary key value
1084 l_pphv_rec.ID := get_seq_id;
1085 -- Setting item attributes
1086 l_return_Status := Set_Attributes(
1087 l_pphv_rec, -- IN
1088 l_def_pphv_rec); -- OUT
1089 --- If any errors happen abort API
1090 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1091 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1092 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1093 RAISE OKC_API.G_EXCEPTION_ERROR;
1094 END IF;
1095 l_def_pphv_rec := fill_who_columns(l_def_pphv_rec);
1096 --- Validate all non-missing attributes (Item Level Validation)
1097 l_return_status := Validate_Attributes(l_def_pphv_rec);
1098 --- If any errors happen abort API
1099 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1100 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1101 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1102 RAISE OKC_API.G_EXCEPTION_ERROR;
1103 END IF;
1104 l_return_status := Validate_Record(l_def_pphv_rec);
1105 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1106 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1107 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1108 RAISE OKC_API.G_EXCEPTION_ERROR;
1109 END IF;
1110 -----------------------------------------
1111 -- Move VIEW record to "Child" records --
1112 -----------------------------------------
1113 migrate(l_def_pphv_rec, l_pph_rec);
1114 -----------------------------------------------
1115 -- Call the INSERT_ROW for each child record --
1116 -----------------------------------------------
1117 insert_row(
1118 p_init_msg_list,
1119 l_return_status,
1120 x_msg_count,
1121 x_msg_data,
1122 l_pph_rec,
1123 lx_pph_rec
1124 );
1125 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1126 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1127 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1128 RAISE OKC_API.G_EXCEPTION_ERROR;
1129 END IF;
1130 migrate(lx_pph_rec, l_def_pphv_rec);
1131 -- Set OUT values
1132 x_pphv_rec := l_def_pphv_rec;
1133 x_return_status := l_return_status;
1134 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1135 EXCEPTION
1136 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1137 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1138 (
1139 l_api_name,
1140 G_PKG_NAME,
1141 'OKC_API.G_RET_STS_ERROR',
1142 x_msg_count,
1143 x_msg_data,
1144 '_PVT'
1145 );
1146 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1147 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1148 (
1149 l_api_name,
1150 G_PKG_NAME,
1151 'OKC_API.G_RET_STS_UNEXP_ERROR',
1152 x_msg_count,
1153 x_msg_data,
1154 '_PVT'
1155 );
1156 WHEN OTHERS THEN
1157 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1158 (
1159 l_api_name,
1160 G_PKG_NAME,
1161 'OTHERS',
1162 x_msg_count,
1163 x_msg_data,
1164 '_PVT'
1165 );
1166 END insert_row;
1167 ----------------------------------------
1168 -- PL/SQL TBL insert_row for:PPHV_TBL --
1169 ----------------------------------------
1170 PROCEDURE insert_row(
1171 p_api_version IN NUMBER,
1172 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1173 x_return_status OUT NOCOPY VARCHAR2,
1174 x_msg_count OUT NOCOPY NUMBER,
1175 x_msg_data OUT NOCOPY VARCHAR2,
1176 p_pphv_tbl IN pphv_tbl_type,
1177 x_pphv_tbl OUT NOCOPY pphv_tbl_type,
1178 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1179
1180 l_api_version CONSTANT NUMBER := 1;
1181 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1182 i NUMBER := 0;
1183 BEGIN
1184 OKC_API.init_msg_list(p_init_msg_list);
1185 -- Make sure PL/SQL table has records in it before passing
1186 IF (p_pphv_tbl.COUNT > 0) THEN
1187 i := p_pphv_tbl.FIRST;
1188 LOOP
1189 DECLARE
1190 l_error_rec OKC_API.ERROR_REC_TYPE;
1191 BEGIN
1192 l_error_rec.api_name := l_api_name;
1193 l_error_rec.api_package := G_PKG_NAME;
1194 l_error_rec.idx := i;
1195 insert_row (
1196 p_api_version => p_api_version,
1197 p_init_msg_list => OKC_API.G_FALSE,
1198 x_return_status => l_error_rec.error_type,
1199 x_msg_count => l_error_rec.msg_count,
1200 x_msg_data => l_error_rec.msg_data,
1201 p_pphv_rec => p_pphv_tbl(i),
1202 x_pphv_rec => x_pphv_tbl(i));
1203 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1204 l_error_rec.sqlcode := SQLCODE;
1205 load_error_tbl(l_error_rec, px_error_tbl);
1206 ELSE
1207 x_msg_count := l_error_rec.msg_count;
1208 x_msg_data := l_error_rec.msg_data;
1209 END IF;
1210 EXCEPTION
1211 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1212 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1213 l_error_rec.sqlcode := SQLCODE;
1214 load_error_tbl(l_error_rec, px_error_tbl);
1215 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1216 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1217 l_error_rec.sqlcode := SQLCODE;
1218 load_error_tbl(l_error_rec, px_error_tbl);
1219 WHEN OTHERS THEN
1220 l_error_rec.error_type := 'OTHERS';
1221 l_error_rec.sqlcode := SQLCODE;
1222 load_error_tbl(l_error_rec, px_error_tbl);
1223 END;
1224 EXIT WHEN (i = p_pphv_tbl.LAST);
1225 i := p_pphv_tbl.NEXT(i);
1226 END LOOP;
1227 END IF;
1228 -- Loop through the error_tbl to find the error with the highest severity
1229 -- and return it.
1230 x_return_status := find_highest_exception(px_error_tbl);
1231 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1232 EXCEPTION
1233 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1234 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1235 (
1236 l_api_name,
1237 G_PKG_NAME,
1238 'OKC_API.G_RET_STS_ERROR',
1239 x_msg_count,
1240 x_msg_data,
1241 '_PVT'
1242 );
1243 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1244 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1245 (
1246 l_api_name,
1247 G_PKG_NAME,
1248 'OKC_API.G_RET_STS_UNEXP_ERROR',
1249 x_msg_count,
1250 x_msg_data,
1251 '_PVT'
1252 );
1253 WHEN OTHERS THEN
1254 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1255 (
1256 l_api_name,
1257 G_PKG_NAME,
1258 'OTHERS',
1259 x_msg_count,
1260 x_msg_data,
1261 '_PVT'
1262 );
1263 END insert_row;
1264
1265 ----------------------------------------
1266 -- PL/SQL TBL insert_row for:PPHV_TBL --
1267 ----------------------------------------
1268 PROCEDURE insert_row(
1269 p_api_version IN NUMBER,
1270 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1271 x_return_status OUT NOCOPY VARCHAR2,
1272 x_msg_count OUT NOCOPY NUMBER,
1273 x_msg_data OUT NOCOPY VARCHAR2,
1274 p_pphv_tbl IN pphv_tbl_type,
1275 x_pphv_tbl OUT NOCOPY pphv_tbl_type) IS
1276
1277 l_api_version CONSTANT NUMBER := 1;
1278 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1279 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1280 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1281 BEGIN
1282 OKC_API.init_msg_list(p_init_msg_list);
1283 -- Make sure PL/SQL table has records in it before passing
1284 IF (p_pphv_tbl.COUNT > 0) THEN
1285 insert_row (
1286 p_api_version => p_api_version,
1287 p_init_msg_list => OKC_API.G_FALSE,
1288 x_return_status => x_return_status,
1289 x_msg_count => x_msg_count,
1290 x_msg_data => x_msg_data,
1291 p_pphv_tbl => p_pphv_tbl,
1292 x_pphv_tbl => x_pphv_tbl,
1293 px_error_tbl => l_error_tbl);
1294 END IF;
1295 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1296 EXCEPTION
1297 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1298 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1299 (
1300 l_api_name,
1301 G_PKG_NAME,
1302 'OKC_API.G_RET_STS_ERROR',
1303 x_msg_count,
1304 x_msg_data,
1305 '_PVT'
1306 );
1307 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1308 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1309 (
1310 l_api_name,
1311 G_PKG_NAME,
1312 'OKC_API.G_RET_STS_UNEXP_ERROR',
1313 x_msg_count,
1314 x_msg_data,
1315 '_PVT'
1316 );
1317 WHEN OTHERS THEN
1318 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1319 (
1320 l_api_name,
1321 G_PKG_NAME,
1322 'OTHERS',
1323 x_msg_count,
1324 x_msg_data,
1325 '_PVT'
1326 );
1327 END insert_row;
1328
1329 ---------------------------------------------------------------------------
1330 -- PROCEDURE lock_row
1331 ---------------------------------------------------------------------------
1332 ----------------------------------------
1333 -- lock_row for:OKL_PARTY_PAYMENT_HDR --
1334 ----------------------------------------
1335 PROCEDURE lock_row(
1336 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1337 x_return_status OUT NOCOPY VARCHAR2,
1338 x_msg_count OUT NOCOPY NUMBER,
1339 x_msg_data OUT NOCOPY VARCHAR2,
1340 p_pph_rec IN pph_rec_type) IS
1341
1342 E_Resource_Busy EXCEPTION;
1343 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1344 CURSOR lock_csr (p_pph_rec IN pph_rec_type) IS
1345 SELECT OBJECT_VERSION_NUMBER
1346 FROM OKL_PARTY_PAYMENT_HDR
1347 WHERE ID = p_pph_rec.id
1348 AND OBJECT_VERSION_NUMBER = p_pph_rec.object_version_number
1349 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1350
1351 CURSOR lchk_csr (p_pph_rec IN pph_rec_type) IS
1352 SELECT OBJECT_VERSION_NUMBER
1353 FROM OKL_PARTY_PAYMENT_HDR
1354 WHERE ID = p_pph_rec.id;
1355 l_api_version CONSTANT NUMBER := 1;
1356 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1357 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1358 l_object_version_number OKL_PARTY_PAYMENT_HDR.OBJECT_VERSION_NUMBER%TYPE;
1359 lc_object_version_number OKL_PARTY_PAYMENT_HDR.OBJECT_VERSION_NUMBER%TYPE;
1360 l_row_notfound BOOLEAN := FALSE;
1361 lc_row_notfound BOOLEAN := FALSE;
1362 BEGIN
1363 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1364 p_init_msg_list,
1365 '_PVT',
1366 x_return_status);
1367 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1368 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1369 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1370 RAISE OKC_API.G_EXCEPTION_ERROR;
1371 END IF;
1372 BEGIN
1373 OPEN lock_csr(p_pph_rec);
1374 FETCH lock_csr INTO l_object_version_number;
1375 l_row_notfound := lock_csr%NOTFOUND;
1376 CLOSE lock_csr;
1377 EXCEPTION
1378 WHEN E_Resource_Busy THEN
1379 IF (lock_csr%ISOPEN) THEN
1380 CLOSE lock_csr;
1381 END IF;
1382 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1383 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1384 END;
1385
1386 IF ( l_row_notfound ) THEN
1387 OPEN lchk_csr(p_pph_rec);
1388 FETCH lchk_csr INTO lc_object_version_number;
1389 lc_row_notfound := lchk_csr%NOTFOUND;
1390 CLOSE lchk_csr;
1391 END IF;
1392 IF (lc_row_notfound) THEN
1393 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1394 RAISE OKC_API.G_EXCEPTION_ERROR;
1395 ELSIF lc_object_version_number > p_pph_rec.object_version_number THEN
1396 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1397 RAISE OKC_API.G_EXCEPTION_ERROR;
1398 ELSIF lc_object_version_number <> p_pph_rec.object_version_number THEN
1399 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1400 RAISE OKC_API.G_EXCEPTION_ERROR;
1401 ELSIF lc_object_version_number = -1 THEN
1402 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1403 RAISE OKC_API.G_EXCEPTION_ERROR;
1404 END IF;
1405 x_return_status := l_return_status;
1406 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1407 EXCEPTION
1408 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1409 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1410 (
1411 l_api_name,
1412 G_PKG_NAME,
1413 'OKC_API.G_RET_STS_ERROR',
1414 x_msg_count,
1415 x_msg_data,
1416 '_PVT'
1417 );
1418 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1419 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1420 (
1421 l_api_name,
1422 G_PKG_NAME,
1423 'OKC_API.G_RET_STS_UNEXP_ERROR',
1424 x_msg_count,
1425 x_msg_data,
1426 '_PVT'
1427 );
1428 WHEN OTHERS THEN
1429 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1430 (
1431 l_api_name,
1432 G_PKG_NAME,
1433 'OTHERS',
1434 x_msg_count,
1435 x_msg_data,
1436 '_PVT'
1437 );
1438 END lock_row;
1439 -------------------------------------------
1440 -- lock_row for: OKL_PARTY_PAYMENT_HDR_V --
1441 -------------------------------------------
1442 PROCEDURE lock_row(
1443 p_api_version IN NUMBER,
1444 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1445 x_return_status OUT NOCOPY VARCHAR2,
1446 x_msg_count OUT NOCOPY NUMBER,
1447 x_msg_data OUT NOCOPY VARCHAR2,
1448 p_pphv_rec IN pphv_rec_type) IS
1449
1450 l_api_version CONSTANT NUMBER := 1;
1451 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1452 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1453 l_pph_rec pph_rec_type;
1454 BEGIN
1455 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1456 G_PKG_NAME,
1457 p_init_msg_list,
1458 l_api_version,
1459 p_api_version,
1460 '_PVT',
1461 x_return_status);
1462 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1463 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1464 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1465 RAISE OKC_API.G_EXCEPTION_ERROR;
1466 END IF;
1467 -----------------------------------------
1468 -- Move VIEW record to "Child" records --
1469 -----------------------------------------
1470 migrate(p_pphv_rec, l_pph_rec);
1471 ---------------------------------------------
1472 -- Call the LOCK_ROW for each child record --
1473 ---------------------------------------------
1474 lock_row(
1475 p_init_msg_list,
1476 l_return_status,
1477 x_msg_count,
1478 x_msg_data,
1479 l_pph_rec
1480 );
1481 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1482 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1483 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1484 RAISE OKC_API.G_EXCEPTION_ERROR;
1485 END IF;
1486 x_return_status := l_return_status;
1487 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1488 EXCEPTION
1489 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1490 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1491 (
1492 l_api_name,
1493 G_PKG_NAME,
1494 'OKC_API.G_RET_STS_ERROR',
1495 x_msg_count,
1496 x_msg_data,
1497 '_PVT'
1498 );
1499 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1500 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1501 (
1502 l_api_name,
1503 G_PKG_NAME,
1504 'OKC_API.G_RET_STS_UNEXP_ERROR',
1505 x_msg_count,
1506 x_msg_data,
1507 '_PVT'
1508 );
1509 WHEN OTHERS THEN
1510 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1511 (
1512 l_api_name,
1513 G_PKG_NAME,
1514 'OTHERS',
1515 x_msg_count,
1516 x_msg_data,
1517 '_PVT'
1518 );
1519 END lock_row;
1520 --------------------------------------
1521 -- PL/SQL TBL lock_row for:PPHV_TBL --
1522 --------------------------------------
1523 PROCEDURE lock_row(
1524 p_api_version IN NUMBER,
1525 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1526 x_return_status OUT NOCOPY VARCHAR2,
1527 x_msg_count OUT NOCOPY NUMBER,
1528 x_msg_data OUT NOCOPY VARCHAR2,
1529 p_pphv_tbl IN pphv_tbl_type,
1530 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1531
1532 l_api_version CONSTANT NUMBER := 1;
1533 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1534 i NUMBER := 0;
1535 BEGIN
1536 OKC_API.init_msg_list(p_init_msg_list);
1537 -- Make sure PL/SQL table has recrods in it before passing
1538 IF (p_pphv_tbl.COUNT > 0) THEN
1539 i := p_pphv_tbl.FIRST;
1540 LOOP
1541 DECLARE
1542 l_error_rec OKC_API.ERROR_REC_TYPE;
1543 BEGIN
1544 l_error_rec.api_name := l_api_name;
1545 l_error_rec.api_package := G_PKG_NAME;
1546 l_error_rec.idx := i;
1547 lock_row(
1548 p_api_version => p_api_version,
1549 p_init_msg_list => OKC_API.G_FALSE,
1550 x_return_status => l_error_rec.error_type,
1551 x_msg_count => l_error_rec.msg_count,
1552 x_msg_data => l_error_rec.msg_data,
1553 p_pphv_rec => p_pphv_tbl(i));
1554 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1555 l_error_rec.sqlcode := SQLCODE;
1556 load_error_tbl(l_error_rec, px_error_tbl);
1557 ELSE
1558 x_msg_count := l_error_rec.msg_count;
1559 x_msg_data := l_error_rec.msg_data;
1560 END IF;
1561 EXCEPTION
1562 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1563 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1564 l_error_rec.sqlcode := SQLCODE;
1565 load_error_tbl(l_error_rec, px_error_tbl);
1566 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1567 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1568 l_error_rec.sqlcode := SQLCODE;
1569 load_error_tbl(l_error_rec, px_error_tbl);
1570 WHEN OTHERS THEN
1571 l_error_rec.error_type := 'OTHERS';
1572 l_error_rec.sqlcode := SQLCODE;
1573 load_error_tbl(l_error_rec, px_error_tbl);
1574 END;
1575 EXIT WHEN (i = p_pphv_tbl.LAST);
1576 i := p_pphv_tbl.NEXT(i);
1577 END LOOP;
1578 END IF;
1579 -- Loop through the error_tbl to find the error with the highest severity
1580 -- and return it.
1581 x_return_status := find_highest_exception(px_error_tbl);
1582 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1583 EXCEPTION
1584 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1585 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1586 (
1587 l_api_name,
1588 G_PKG_NAME,
1589 'OKC_API.G_RET_STS_ERROR',
1590 x_msg_count,
1591 x_msg_data,
1592 '_PVT'
1593 );
1594 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1595 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1596 (
1597 l_api_name,
1598 G_PKG_NAME,
1599 'OKC_API.G_RET_STS_UNEXP_ERROR',
1600 x_msg_count,
1601 x_msg_data,
1602 '_PVT'
1603 );
1604 WHEN OTHERS THEN
1605 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1606 (
1607 l_api_name,
1608 G_PKG_NAME,
1609 'OTHERS',
1610 x_msg_count,
1611 x_msg_data,
1612 '_PVT'
1613 );
1614 END lock_row;
1615 --------------------------------------
1616 -- PL/SQL TBL lock_row for:PPHV_TBL --
1617 --------------------------------------
1618 PROCEDURE lock_row(
1619 p_api_version IN NUMBER,
1620 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1621 x_return_status OUT NOCOPY VARCHAR2,
1622 x_msg_count OUT NOCOPY NUMBER,
1623 x_msg_data OUT NOCOPY VARCHAR2,
1624 p_pphv_tbl IN pphv_tbl_type) IS
1625
1626 l_api_version CONSTANT NUMBER := 1;
1627 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1628 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1629 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1630 BEGIN
1631 OKC_API.init_msg_list(p_init_msg_list);
1632 -- Make sure PL/SQL table has recrods in it before passing
1633 IF (p_pphv_tbl.COUNT > 0) THEN
1634 lock_row(
1635 p_api_version => p_api_version,
1636 p_init_msg_list => OKC_API.G_FALSE,
1637 x_return_status => x_return_status,
1638 x_msg_count => x_msg_count,
1639 x_msg_data => x_msg_data,
1640 p_pphv_tbl => p_pphv_tbl,
1641 px_error_tbl => l_error_tbl);
1642 END IF;
1643 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1644 EXCEPTION
1645 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1646 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1647 (
1648 l_api_name,
1649 G_PKG_NAME,
1650 'OKC_API.G_RET_STS_ERROR',
1651 x_msg_count,
1652 x_msg_data,
1653 '_PVT'
1654 );
1655 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1656 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1657 (
1658 l_api_name,
1659 G_PKG_NAME,
1660 'OKC_API.G_RET_STS_UNEXP_ERROR',
1661 x_msg_count,
1662 x_msg_data,
1663 '_PVT'
1664 );
1665 WHEN OTHERS THEN
1666 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1667 (
1668 l_api_name,
1669 G_PKG_NAME,
1670 'OTHERS',
1671 x_msg_count,
1672 x_msg_data,
1673 '_PVT'
1674 );
1675 END lock_row;
1676 ---------------------------------------------------------------------------
1677 -- PROCEDURE update_row
1678 ---------------------------------------------------------------------------
1679 ------------------------------------------
1680 -- update_row for:OKL_PARTY_PAYMENT_HDR --
1681 ------------------------------------------
1682 PROCEDURE update_row(
1683 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1684 x_return_status OUT NOCOPY VARCHAR2,
1685 x_msg_count OUT NOCOPY NUMBER,
1686 x_msg_data OUT NOCOPY VARCHAR2,
1687 p_pph_rec IN pph_rec_type,
1688 x_pph_rec OUT NOCOPY pph_rec_type) IS
1689
1690 l_api_version CONSTANT NUMBER := 1;
1691 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1692 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1693 l_pph_rec pph_rec_type := p_pph_rec;
1694 l_def_pph_rec pph_rec_type;
1695 l_row_notfound BOOLEAN := TRUE;
1696 ----------------------------------
1697 -- FUNCTION populate_new_record --
1698 ----------------------------------
1699 FUNCTION populate_new_record (
1700 p_pph_rec IN pph_rec_type,
1701 x_pph_rec OUT NOCOPY pph_rec_type
1702 ) RETURN VARCHAR2 IS
1703 l_pph_rec pph_rec_type;
1704 l_row_notfound BOOLEAN := TRUE;
1705 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1706 BEGIN
1707 x_pph_rec := p_pph_rec;
1708 -- Get current database values
1709 l_pph_rec := get_rec(p_pph_rec, l_return_status);
1710 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1711 IF (x_pph_rec.id = OKC_API.G_MISS_NUM)
1712 THEN
1713 x_pph_rec.id := l_pph_rec.id;
1714 END IF;
1715 IF (x_pph_rec.object_version_number = OKC_API.G_MISS_NUM)
1716 THEN
1717 x_pph_rec.object_version_number := l_pph_rec.object_version_number;
1718 END IF;
1719 IF (x_pph_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1720 THEN
1721 x_pph_rec.dnz_chr_id := l_pph_rec.dnz_chr_id;
1722 END IF;
1723 IF (x_pph_rec.cle_id = OKC_API.G_MISS_NUM)
1724 THEN
1725 x_pph_rec.cle_id := l_pph_rec.cle_id;
1726 END IF;
1727 IF (x_pph_rec.ppl_id = OKC_API.G_MISS_NUM)
1728 THEN
1729 x_pph_rec.ppl_id := l_pph_rec.ppl_id;
1730 END IF;
1731 IF (x_pph_rec.passthru_start_date = OKC_API.G_MISS_DATE)
1732 THEN
1733 x_pph_rec.passthru_start_date := l_pph_rec.passthru_start_date;
1734 END IF;
1735 IF (x_pph_rec.payout_basis = OKC_API.G_MISS_CHAR)
1736 THEN
1737 x_pph_rec.payout_basis := l_pph_rec.payout_basis;
1738 END IF;
1739 IF (x_pph_rec.payout_basis_formula = OKC_API.G_MISS_CHAR)
1740 THEN
1741 x_pph_rec.payout_basis_formula := l_pph_rec.payout_basis;
1742 END IF;
1743 IF (x_pph_rec.effective_from = OKC_API.G_MISS_DATE)
1744 THEN
1745 x_pph_rec.effective_from := l_pph_rec.effective_from;
1746 END IF;
1747 IF (x_pph_rec.effective_to = OKC_API.G_MISS_DATE)
1748 THEN
1749 x_pph_rec.effective_to := l_pph_rec.effective_to;
1750 END IF;
1751 IF (x_pph_rec.passthru_term = OKC_API.G_MISS_CHAR ) THEN
1752 x_pph_rec.passthru_term := l_pph_rec.passthru_term;
1753 END IF;
1754 IF (x_pph_rec.passthru_stream_type_id = OKC_API.G_MISS_NUM ) THEN
1755 x_pph_rec.passthru_stream_type_id := l_pph_rec.passthru_stream_type_id;
1756 END IF;
1757 IF (x_pph_rec.created_by = OKC_API.G_MISS_NUM)
1758 THEN
1759 x_pph_rec.created_by := l_pph_rec.created_by;
1760 END IF;
1761 IF (x_pph_rec.creation_date = OKC_API.G_MISS_DATE)
1762 THEN
1763 x_pph_rec.creation_date := l_pph_rec.creation_date;
1764 END IF;
1765 IF (x_pph_rec.last_updated_by = OKC_API.G_MISS_NUM)
1766 THEN
1767 x_pph_rec.last_updated_by := l_pph_rec.last_updated_by;
1768 END IF;
1769 IF (x_pph_rec.last_update_date = OKC_API.G_MISS_DATE)
1770 THEN
1771 x_pph_rec.last_update_date := l_pph_rec.last_update_date;
1772 END IF;
1773 IF (x_pph_rec.last_update_login = OKC_API.G_MISS_NUM)
1774 THEN
1775 x_pph_rec.last_update_login := l_pph_rec.last_update_login;
1776 END IF;
1777 END IF;
1778 RETURN(l_return_status);
1779 END populate_new_record;
1780 ----------------------------------------------
1781 -- Set_Attributes for:OKL_PARTY_PAYMENT_HDR --
1782 ----------------------------------------------
1783 FUNCTION Set_Attributes (
1784 p_pph_rec IN pph_rec_type,
1785 x_pph_rec OUT NOCOPY pph_rec_type
1786 ) RETURN VARCHAR2 IS
1787 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1788 BEGIN
1789 x_pph_rec := p_pph_rec;
1790 RETURN(l_return_status);
1791 END Set_Attributes;
1792 BEGIN
1793 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1794 p_init_msg_list,
1795 '_PVT',
1796 x_return_status);
1797 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1798 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1799 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1800 RAISE OKC_API.G_EXCEPTION_ERROR;
1801 END IF;
1802 --- Setting item attributes
1803 l_return_status := Set_Attributes(
1804 p_pph_rec, -- IN
1805 l_pph_rec); -- OUT
1806 --- If any errors happen abort API
1807 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1808 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1809 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1810 RAISE OKC_API.G_EXCEPTION_ERROR;
1811 END IF;
1812 l_return_status := populate_new_record(l_pph_rec, l_def_pph_rec);
1813 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1814 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1815 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1816 RAISE OKC_API.G_EXCEPTION_ERROR;
1817 END IF;
1818 UPDATE OKL_PARTY_PAYMENT_HDR
1819 SET OBJECT_VERSION_NUMBER = l_def_pph_rec.object_version_number,
1820 DNZ_CHR_ID = l_def_pph_rec.dnz_chr_id,
1821 CLE_ID = l_def_pph_rec.cle_id,
1822 PPL_ID = l_def_pph_rec.ppl_id,
1823 PASSTHRU_START_DATE = l_def_pph_rec.passthru_start_date,
1824 PAYOUT_BASIS = l_def_pph_rec.payout_basis,
1825 PAYOUT_BASIS_FORMULA = l_def_pph_rec.payout_basis_formula,
1826 EFFECTIVE_FROM = l_def_pph_rec.effective_from,
1827 EFFECTIVE_TO = l_def_pph_rec.effective_to,
1828 PASSTHRU_TERM = l_def_pph_rec.passthru_term,
1829 PASSTHRU_STREAM_TYPE_ID = l_def_pph_rec.passthru_stream_type_id,
1830 CREATED_BY = l_def_pph_rec.created_by,
1831 CREATION_DATE = l_def_pph_rec.creation_date,
1832 LAST_UPDATED_BY = l_def_pph_rec.last_updated_by,
1833 LAST_UPDATE_DATE = l_def_pph_rec.last_update_date,
1834 LAST_UPDATE_LOGIN = l_def_pph_rec.last_update_login
1835 WHERE ID = l_def_pph_rec.id;
1836
1837 x_pph_rec := l_pph_rec;
1838 x_return_status := l_return_status;
1839 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1840 EXCEPTION
1841 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1842 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1843 (
1844 l_api_name,
1845 G_PKG_NAME,
1846 'OKC_API.G_RET_STS_ERROR',
1847 x_msg_count,
1848 x_msg_data,
1849 '_PVT'
1850 );
1851 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1852 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1853 (
1854 l_api_name,
1855 G_PKG_NAME,
1856 'OKC_API.G_RET_STS_UNEXP_ERROR',
1857 x_msg_count,
1858 x_msg_data,
1859 '_PVT'
1860 );
1861 WHEN OTHERS THEN
1862 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1863 (
1864 l_api_name,
1865 G_PKG_NAME,
1866 'OTHERS',
1867 x_msg_count,
1868 x_msg_data,
1869 '_PVT'
1870 );
1871 END update_row;
1872 --------------------------------------------
1873 -- update_row for:OKL_PARTY_PAYMENT_HDR_V --
1874 --------------------------------------------
1875 PROCEDURE update_row(
1876 p_api_version IN NUMBER,
1877 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1878 x_return_status OUT NOCOPY VARCHAR2,
1879 x_msg_count OUT NOCOPY NUMBER,
1880 x_msg_data OUT NOCOPY VARCHAR2,
1881 p_pphv_rec IN pphv_rec_type,
1882 x_pphv_rec OUT NOCOPY pphv_rec_type) IS
1883
1884 l_api_version CONSTANT NUMBER := 1;
1885 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1886 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1887 l_pphv_rec pphv_rec_type := p_pphv_rec;
1888 l_def_pphv_rec pphv_rec_type;
1889 l_db_pphv_rec pphv_rec_type;
1890 l_pph_rec pph_rec_type;
1891 lx_pph_rec pph_rec_type;
1892 -------------------------------
1893 -- FUNCTION fill_who_columns --
1894 -------------------------------
1895 FUNCTION fill_who_columns (
1896 p_pphv_rec IN pphv_rec_type
1897 ) RETURN pphv_rec_type IS
1898 l_pphv_rec pphv_rec_type := p_pphv_rec;
1899 BEGIN
1900 l_pphv_rec.LAST_UPDATE_DATE := SYSDATE;
1901 l_pphv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1902 l_pphv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1903 RETURN(l_pphv_rec);
1904 END fill_who_columns;
1905 ----------------------------------
1906 -- FUNCTION populate_new_record --
1907 ----------------------------------
1908 FUNCTION populate_new_record (
1909 p_pphv_rec IN pphv_rec_type,
1910 x_pphv_rec OUT NOCOPY pphv_rec_type
1911 ) RETURN VARCHAR2 IS
1912 l_row_notfound BOOLEAN := TRUE;
1913 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1914 BEGIN
1915 x_pphv_rec := p_pphv_rec;
1916 -- Get current database values
1917 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
1918 -- so it may be verified through LOCK_ROW.
1919 l_db_pphv_rec := get_rec(p_pphv_rec, l_return_status);
1920 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1921 IF (x_pphv_rec.id = OKC_API.G_MISS_NUM)
1922 THEN
1923 x_pphv_rec.id := l_db_pphv_rec.id;
1924 END IF;
1925 IF (x_pphv_rec.object_version_number = OKC_API.G_MISS_NUM)
1926 THEN
1927 x_pphv_rec.object_version_number := l_db_pphv_rec.object_version_number;
1928 END IF;
1929 IF (x_pphv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1930 THEN
1931 x_pphv_rec.dnz_chr_id := l_db_pphv_rec.dnz_chr_id;
1932 END IF;
1933 IF (x_pphv_rec.cle_id = OKC_API.G_MISS_NUM)
1934 THEN
1935 x_pphv_rec.cle_id := l_db_pphv_rec.cle_id;
1936 END IF;
1937 IF (x_pphv_rec.ppl_id = OKC_API.G_MISS_NUM)
1938 THEN
1939 x_pphv_rec.ppl_id := l_db_pphv_rec.ppl_id;
1940 END IF;
1941 IF (x_pphv_rec.passthru_start_date = OKC_API.G_MISS_DATE)
1942 THEN
1943 x_pphv_rec.passthru_start_date := l_db_pphv_rec.passthru_start_date;
1944 END IF;
1945 IF (x_pphv_rec.payout_basis = OKC_API.G_MISS_CHAR)
1946 THEN
1947 x_pphv_rec.payout_basis := l_db_pphv_rec.payout_basis;
1948 END IF;
1949 IF (x_pphv_rec.payout_basis_formula = OKC_API.G_MISS_CHAR)
1950 THEN
1951 x_pphv_rec.payout_basis_formula := l_db_pphv_rec.payout_basis_formula;
1952 END IF;
1953 IF (x_pphv_rec.effective_from = OKC_API.G_MISS_DATE)
1954 THEN
1955 x_pphv_rec.effective_from := l_db_pphv_rec.effective_from;
1956 END IF;
1957 IF (x_pphv_rec.effective_to = OKC_API.G_MISS_DATE)
1958 THEN
1959 x_pphv_rec.effective_to := l_db_pphv_rec.effective_to;
1960 END IF;
1961 IF (x_pphv_rec.passthru_term = OKC_API.G_MISS_CHAR)
1962 THEN
1963 x_pphv_rec.passthru_term := l_db_pphv_rec.passthru_term;
1964 END IF;
1965 IF (x_pphv_rec.passthru_stream_type_id = OKC_API.G_MISS_NUM)
1966 THEN
1967 x_pphv_rec.passthru_stream_type_id := l_db_pphv_rec.passthru_stream_type_id;
1968 END IF;
1969 IF (x_pphv_rec.created_by = OKC_API.G_MISS_NUM)
1970 THEN
1971 x_pphv_rec.created_by := l_db_pphv_rec.created_by;
1972 END IF;
1973 IF (x_pphv_rec.creation_date = OKC_API.G_MISS_DATE)
1974 THEN
1975 x_pphv_rec.creation_date := l_db_pphv_rec.creation_date;
1976 END IF;
1977 IF (x_pphv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1978 THEN
1979 x_pphv_rec.last_updated_by := l_db_pphv_rec.last_updated_by;
1980 END IF;
1981 IF (x_pphv_rec.last_update_date = OKC_API.G_MISS_DATE)
1982 THEN
1983 x_pphv_rec.last_update_date := l_db_pphv_rec.last_update_date;
1984 END IF;
1985 IF (x_pphv_rec.last_update_login = OKC_API.G_MISS_NUM)
1986 THEN
1987 x_pphv_rec.last_update_login := l_db_pphv_rec.last_update_login;
1988 END IF;
1989 END IF;
1990 RETURN(l_return_status);
1991 END populate_new_record;
1992 ------------------------------------------------
1993 -- Set_Attributes for:OKL_PARTY_PAYMENT_HDR_V --
1994 ------------------------------------------------
1995 FUNCTION Set_Attributes (
1996 p_pphv_rec IN pphv_rec_type,
1997 x_pphv_rec OUT NOCOPY pphv_rec_type
1998 ) RETURN VARCHAR2 IS
1999 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2000 BEGIN
2001 x_pphv_rec := p_pphv_rec;
2002 x_pphv_rec.OBJECT_VERSION_NUMBER := NVL(x_pphv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2003 RETURN(l_return_status);
2004 END Set_Attributes;
2005 BEGIN
2006 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2007 G_PKG_NAME,
2008 p_init_msg_list,
2009 l_api_version,
2010 p_api_version,
2011 '_PVT',
2012 x_return_status);
2013 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2014 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2015 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2016 RAISE OKC_API.G_EXCEPTION_ERROR;
2017 END IF;
2018 --- Setting item attributes
2019 l_return_status := Set_Attributes(
2020 p_pphv_rec, -- IN
2021 x_pphv_rec); -- OUT
2022 --- If any errors happen abort API
2023 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2024 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2025 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2026 RAISE OKC_API.G_EXCEPTION_ERROR;
2027 END IF;
2028 l_return_status := populate_new_record(l_pphv_rec, l_def_pphv_rec);
2029 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2030 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2031 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2032 RAISE OKC_API.G_EXCEPTION_ERROR;
2033 END IF;
2034 l_def_pphv_rec := fill_who_columns(l_def_pphv_rec);
2035 --- Validate all non-missing attributes (Item Level Validation)
2036 l_return_status := Validate_Attributes(l_def_pphv_rec);
2037 --- If any errors happen abort API
2038 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2039 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2040 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2041 RAISE OKC_API.G_EXCEPTION_ERROR;
2042 END IF;
2043 l_return_status := Validate_Record(l_def_pphv_rec, l_db_pphv_rec);
2044 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2045 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2046 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2047 RAISE OKC_API.G_EXCEPTION_ERROR;
2048 END IF;
2049 /*
2050 -- Lock the Record
2051 lock_row(
2052 p_api_version => p_api_version,
2053 p_init_msg_list => p_init_msg_list,
2054 x_return_status => l_return_status,
2055 x_msg_count => x_msg_count,
2056 x_msg_data => x_msg_data,
2057 p_pphv_rec => p_pphv_rec);
2058 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2059 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2060 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2061 RAISE OKC_API.G_EXCEPTION_ERROR;
2062 END IF;
2063 */
2064 -----------------------------------------
2065 -- Move VIEW record to "Child" records --
2066 -----------------------------------------
2067 migrate(l_def_pphv_rec, l_pph_rec);
2068 -----------------------------------------------
2069 -- Call the UPDATE_ROW for each child record --
2070 -----------------------------------------------
2071 update_row(
2072 p_init_msg_list,
2073 l_return_status,
2074 x_msg_count,
2075 x_msg_data,
2076 l_pph_rec,
2077 lx_pph_rec
2078 );
2079 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2080 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2081 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2082 RAISE OKC_API.G_EXCEPTION_ERROR;
2083 END IF;
2084 migrate(lx_pph_rec, l_def_pphv_rec);
2085 x_pphv_rec := l_def_pphv_rec;
2086 x_return_status := l_return_status;
2087 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2088 EXCEPTION
2089 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2090 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2091 (
2092 l_api_name,
2093 G_PKG_NAME,
2094 'OKC_API.G_RET_STS_ERROR',
2095 x_msg_count,
2096 x_msg_data,
2097 '_PVT'
2098 );
2099 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2100 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2101 (
2102 l_api_name,
2103 G_PKG_NAME,
2104 'OKC_API.G_RET_STS_UNEXP_ERROR',
2105 x_msg_count,
2106 x_msg_data,
2107 '_PVT'
2108 );
2109 WHEN OTHERS THEN
2110 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2111 (
2112 l_api_name,
2113 G_PKG_NAME,
2114 'OTHERS',
2115 x_msg_count,
2116 x_msg_data,
2117 '_PVT'
2118 );
2119 END update_row;
2120 ----------------------------------------
2121 -- PL/SQL TBL update_row for:pphv_tbl --
2122 ----------------------------------------
2123 PROCEDURE update_row(
2124 p_api_version IN NUMBER,
2125 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2126 x_return_status OUT NOCOPY VARCHAR2,
2127 x_msg_count OUT NOCOPY NUMBER,
2128 x_msg_data OUT NOCOPY VARCHAR2,
2129 p_pphv_tbl IN pphv_tbl_type,
2130 x_pphv_tbl OUT NOCOPY pphv_tbl_type,
2131 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2132
2133 l_api_version CONSTANT NUMBER := 1;
2134 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2135 i NUMBER := 0;
2136 BEGIN
2137 OKC_API.init_msg_list(p_init_msg_list);
2138 -- Make sure PL/SQL table has records in it before passing
2139 IF (p_pphv_tbl.COUNT > 0) THEN
2140 i := p_pphv_tbl.FIRST;
2141 LOOP
2142 DECLARE
2143 l_error_rec OKC_API.ERROR_REC_TYPE;
2144 BEGIN
2145 l_error_rec.api_name := l_api_name;
2146 l_error_rec.api_package := G_PKG_NAME;
2147 l_error_rec.idx := i;
2148 update_row (
2149 p_api_version => p_api_version,
2150 p_init_msg_list => OKC_API.G_FALSE,
2151 x_return_status => l_error_rec.error_type,
2152 x_msg_count => l_error_rec.msg_count,
2153 x_msg_data => l_error_rec.msg_data,
2154 p_pphv_rec => p_pphv_tbl(i),
2155 x_pphv_rec => x_pphv_tbl(i));
2156 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2157 l_error_rec.sqlcode := SQLCODE;
2158 load_error_tbl(l_error_rec, px_error_tbl);
2159 ELSE
2160 x_msg_count := l_error_rec.msg_count;
2161 x_msg_data := l_error_rec.msg_data;
2162 END IF;
2163 EXCEPTION
2164 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2165 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2166 l_error_rec.sqlcode := SQLCODE;
2167 load_error_tbl(l_error_rec, px_error_tbl);
2168 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2169 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2170 l_error_rec.sqlcode := SQLCODE;
2171 load_error_tbl(l_error_rec, px_error_tbl);
2172 WHEN OTHERS THEN
2173 l_error_rec.error_type := 'OTHERS';
2174 l_error_rec.sqlcode := SQLCODE;
2175 load_error_tbl(l_error_rec, px_error_tbl);
2176 END;
2177 EXIT WHEN (i = p_pphv_tbl.LAST);
2178 i := p_pphv_tbl.NEXT(i);
2179 END LOOP;
2180 END IF;
2181 -- Loop through the error_tbl to find the error with the highest severity
2182 -- and return it.
2183 x_return_status := find_highest_exception(px_error_tbl);
2184 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2185 EXCEPTION
2186 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2187 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2188 (
2189 l_api_name,
2190 G_PKG_NAME,
2191 'OKC_API.G_RET_STS_ERROR',
2192 x_msg_count,
2193 x_msg_data,
2194 '_PVT'
2195 );
2196 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2197 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2198 (
2199 l_api_name,
2200 G_PKG_NAME,
2201 'OKC_API.G_RET_STS_UNEXP_ERROR',
2202 x_msg_count,
2203 x_msg_data,
2204 '_PVT'
2205 );
2206 WHEN OTHERS THEN
2207 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2208 (
2209 l_api_name,
2210 G_PKG_NAME,
2211 'OTHERS',
2212 x_msg_count,
2213 x_msg_data,
2214 '_PVT'
2215 );
2216 END update_row;
2217
2218 ----------------------------------------
2219 -- PL/SQL TBL update_row for:PPHV_TBL --
2220 ----------------------------------------
2221 PROCEDURE update_row(
2222 p_api_version IN NUMBER,
2223 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2224 x_return_status OUT NOCOPY VARCHAR2,
2225 x_msg_count OUT NOCOPY NUMBER,
2226 x_msg_data OUT NOCOPY VARCHAR2,
2227 p_pphv_tbl IN pphv_tbl_type,
2228 x_pphv_tbl OUT NOCOPY pphv_tbl_type) IS
2229
2230 l_api_version CONSTANT NUMBER := 1;
2231 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2232 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2233 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2234 BEGIN
2235 OKC_API.init_msg_list(p_init_msg_list);
2236 -- Make sure PL/SQL table has records in it before passing
2237 IF (p_pphv_tbl.COUNT > 0) THEN
2238 update_row (
2239 p_api_version => p_api_version,
2240 p_init_msg_list => OKC_API.G_FALSE,
2241 x_return_status => x_return_status,
2242 x_msg_count => x_msg_count,
2243 x_msg_data => x_msg_data,
2244 p_pphv_tbl => p_pphv_tbl,
2245 x_pphv_tbl => x_pphv_tbl,
2246 px_error_tbl => l_error_tbl);
2247 END IF;
2248 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2249 EXCEPTION
2250 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2251 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2252 (
2253 l_api_name,
2254 G_PKG_NAME,
2255 'OKC_API.G_RET_STS_ERROR',
2256 x_msg_count,
2257 x_msg_data,
2258 '_PVT'
2259 );
2260 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2261 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2262 (
2263 l_api_name,
2264 G_PKG_NAME,
2265 'OKC_API.G_RET_STS_UNEXP_ERROR',
2266 x_msg_count,
2267 x_msg_data,
2268 '_PVT'
2269 );
2270 WHEN OTHERS THEN
2271 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2272 (
2273 l_api_name,
2274 G_PKG_NAME,
2275 'OTHERS',
2276 x_msg_count,
2277 x_msg_data,
2278 '_PVT'
2279 );
2280 END update_row;
2281
2282 ---------------------------------------------------------------------------
2283 -- PROCEDURE delete_row
2284 ---------------------------------------------------------------------------
2285 ------------------------------------------
2286 -- delete_row for:OKL_PARTY_PAYMENT_HDR --
2287 ------------------------------------------
2288 PROCEDURE delete_row(
2289 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2290 x_return_status OUT NOCOPY VARCHAR2,
2291 x_msg_count OUT NOCOPY NUMBER,
2292 x_msg_data OUT NOCOPY VARCHAR2,
2293 p_pph_rec IN pph_rec_type) IS
2294
2295 l_api_version CONSTANT NUMBER := 1;
2296 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2297 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2298 l_pph_rec pph_rec_type := p_pph_rec;
2299 l_row_notfound BOOLEAN := TRUE;
2300 BEGIN
2301 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2302 p_init_msg_list,
2303 '_PVT',
2304 x_return_status);
2305 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2306 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2307 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2308 RAISE OKC_API.G_EXCEPTION_ERROR;
2309 END IF;
2310
2311 DELETE FROM OKL_PARTY_PAYMENT_HDR
2312 WHERE ID = p_pph_rec.id;
2313
2314 x_return_status := l_return_status;
2315 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2316 EXCEPTION
2317 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2318 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2319 (
2320 l_api_name,
2321 G_PKG_NAME,
2322 'OKC_API.G_RET_STS_ERROR',
2323 x_msg_count,
2324 x_msg_data,
2325 '_PVT'
2326 );
2327 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2328 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2329 (
2330 l_api_name,
2331 G_PKG_NAME,
2332 'OKC_API.G_RET_STS_UNEXP_ERROR',
2333 x_msg_count,
2334 x_msg_data,
2335 '_PVT'
2336 );
2337 WHEN OTHERS THEN
2338 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2339 (
2340 l_api_name,
2341 G_PKG_NAME,
2342 'OTHERS',
2343 x_msg_count,
2344 x_msg_data,
2345 '_PVT'
2346 );
2347 END delete_row;
2348 --------------------------------------------
2349 -- delete_row for:OKL_PARTY_PAYMENT_HDR_V --
2350 --------------------------------------------
2351 PROCEDURE delete_row(
2352 p_api_version IN NUMBER,
2353 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2354 x_return_status OUT NOCOPY VARCHAR2,
2355 x_msg_count OUT NOCOPY NUMBER,
2356 x_msg_data OUT NOCOPY VARCHAR2,
2357 p_pphv_rec IN pphv_rec_type) IS
2358
2359 l_api_version CONSTANT NUMBER := 1;
2360 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2361 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2362 l_pphv_rec pphv_rec_type := p_pphv_rec;
2363 l_pph_rec pph_rec_type;
2364 BEGIN
2365 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2366 G_PKG_NAME,
2367 p_init_msg_list,
2368 l_api_version,
2369 p_api_version,
2370 '_PVT',
2371 x_return_status);
2372 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2373 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2374 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2375 RAISE OKC_API.G_EXCEPTION_ERROR;
2376 END IF;
2377 -----------------------------------------
2378 -- Move VIEW record to "Child" records --
2379 -----------------------------------------
2380 migrate(l_pphv_rec, l_pph_rec);
2381 -----------------------------------------------
2382 -- Call the DELETE_ROW for each child record --
2383 -----------------------------------------------
2384 delete_row(
2385 p_init_msg_list,
2386 l_return_status,
2387 x_msg_count,
2388 x_msg_data,
2389 l_pph_rec
2390 );
2391 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2392 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2393 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2394 RAISE OKC_API.G_EXCEPTION_ERROR;
2395 END IF;
2396 x_return_status := l_return_status;
2397 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2398 EXCEPTION
2399 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2400 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2401 (
2402 l_api_name,
2403 G_PKG_NAME,
2404 'OKC_API.G_RET_STS_ERROR',
2405 x_msg_count,
2406 x_msg_data,
2407 '_PVT'
2408 );
2409 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2410 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2411 (
2412 l_api_name,
2413 G_PKG_NAME,
2414 'OKC_API.G_RET_STS_UNEXP_ERROR',
2415 x_msg_count,
2416 x_msg_data,
2417 '_PVT'
2418 );
2419 WHEN OTHERS THEN
2420 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2421 (
2422 l_api_name,
2423 G_PKG_NAME,
2424 'OTHERS',
2425 x_msg_count,
2426 x_msg_data,
2427 '_PVT'
2428 );
2429 END delete_row;
2430 -------------------------------------------------------
2431 -- PL/SQL TBL delete_row for:OKL_PARTY_PAYMENT_HDR_V --
2432 -------------------------------------------------------
2433 PROCEDURE delete_row(
2434 p_api_version IN NUMBER,
2435 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2436 x_return_status OUT NOCOPY VARCHAR2,
2437 x_msg_count OUT NOCOPY NUMBER,
2438 x_msg_data OUT NOCOPY VARCHAR2,
2439 p_pphv_tbl IN pphv_tbl_type,
2440 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2441
2442 l_api_version CONSTANT NUMBER := 1;
2443 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2444 i NUMBER := 0;
2445 BEGIN
2446 OKC_API.init_msg_list(p_init_msg_list);
2447 -- Make sure PL/SQL table has records in it before passing
2448 IF (p_pphv_tbl.COUNT > 0) THEN
2449 i := p_pphv_tbl.FIRST;
2450 LOOP
2451 DECLARE
2452 l_error_rec OKC_API.ERROR_REC_TYPE;
2453 BEGIN
2454 l_error_rec.api_name := l_api_name;
2455 l_error_rec.api_package := G_PKG_NAME;
2456 l_error_rec.idx := i;
2457 delete_row (
2458 p_api_version => p_api_version,
2459 p_init_msg_list => OKC_API.G_FALSE,
2460 x_return_status => l_error_rec.error_type,
2461 x_msg_count => l_error_rec.msg_count,
2462 x_msg_data => l_error_rec.msg_data,
2463 p_pphv_rec => p_pphv_tbl(i));
2464 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2465 l_error_rec.sqlcode := SQLCODE;
2466 load_error_tbl(l_error_rec, px_error_tbl);
2467 ELSE
2468 x_msg_count := l_error_rec.msg_count;
2469 x_msg_data := l_error_rec.msg_data;
2470 END IF;
2471 EXCEPTION
2472 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2473 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2474 l_error_rec.sqlcode := SQLCODE;
2475 load_error_tbl(l_error_rec, px_error_tbl);
2476 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2477 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2478 l_error_rec.sqlcode := SQLCODE;
2479 load_error_tbl(l_error_rec, px_error_tbl);
2480 WHEN OTHERS THEN
2481 l_error_rec.error_type := 'OTHERS';
2482 l_error_rec.sqlcode := SQLCODE;
2483 load_error_tbl(l_error_rec, px_error_tbl);
2484 END;
2485 EXIT WHEN (i = p_pphv_tbl.LAST);
2486 i := p_pphv_tbl.NEXT(i);
2487 END LOOP;
2488 END IF;
2489 -- Loop through the error_tbl to find the error with the highest severity
2490 -- and return it.
2491 x_return_status := find_highest_exception(px_error_tbl);
2492 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2493 EXCEPTION
2494 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2495 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2496 (
2497 l_api_name,
2498 G_PKG_NAME,
2499 'OKC_API.G_RET_STS_ERROR',
2500 x_msg_count,
2501 x_msg_data,
2502 '_PVT'
2503 );
2504 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2505 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2506 (
2507 l_api_name,
2508 G_PKG_NAME,
2509 'OKC_API.G_RET_STS_UNEXP_ERROR',
2510 x_msg_count,
2511 x_msg_data,
2512 '_PVT'
2513 );
2514 WHEN OTHERS THEN
2515 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2516 (
2517 l_api_name,
2518 G_PKG_NAME,
2519 'OTHERS',
2520 x_msg_count,
2521 x_msg_data,
2522 '_PVT'
2523 );
2524 END delete_row;
2525
2526 -------------------------------------------------------
2527 -- PL/SQL TBL delete_row for:OKL_PARTY_PAYMENT_HDR_V --
2528 -------------------------------------------------------
2529 PROCEDURE delete_row(
2530 p_api_version IN NUMBER,
2531 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2532 x_return_status OUT NOCOPY VARCHAR2,
2533 x_msg_count OUT NOCOPY NUMBER,
2534 x_msg_data OUT NOCOPY VARCHAR2,
2535 p_pphv_tbl IN pphv_tbl_type) IS
2536
2537 l_api_version CONSTANT NUMBER := 1;
2538 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2539 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2540 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2541 BEGIN
2542 OKC_API.init_msg_list(p_init_msg_list);
2543 -- Make sure PL/SQL table has records in it before passing
2544 IF (p_pphv_tbl.COUNT > 0) THEN
2545 delete_row (
2546 p_api_version => p_api_version,
2547 p_init_msg_list => OKC_API.G_FALSE,
2548 x_return_status => x_return_status,
2549 x_msg_count => x_msg_count,
2550 x_msg_data => x_msg_data,
2551 p_pphv_tbl => p_pphv_tbl,
2552 px_error_tbl => l_error_tbl);
2553 END IF;
2554 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2555 EXCEPTION
2556 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2557 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2558 (
2559 l_api_name,
2560 G_PKG_NAME,
2561 'OKC_API.G_RET_STS_ERROR',
2562 x_msg_count,
2563 x_msg_data,
2564 '_PVT'
2565 );
2566 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2567 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2568 (
2569 l_api_name,
2570 G_PKG_NAME,
2571 'OKC_API.G_RET_STS_UNEXP_ERROR',
2572 x_msg_count,
2573 x_msg_data,
2574 '_PVT'
2575 );
2576 WHEN OTHERS THEN
2577 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2578 (
2579 l_api_name,
2580 G_PKG_NAME,
2581 'OTHERS',
2582 x_msg_count,
2583 x_msg_data,
2584 '_PVT'
2585 );
2586 END delete_row;
2587
2588 ---------------------------------------------------------------------------
2589 -- PROCEDURE versioning
2590 ---------------------------------------------------------------------------
2591
2592 FUNCTION create_version(
2593 p_chr_id IN NUMBER,
2594 p_major_version IN NUMBER) RETURN VARCHAR2 IS
2595
2596 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2597
2598 BEGIN
2599 INSERT INTO OKL_PARTY_PAYMENT_HDR_H
2600 (
2601 ID,
2602 DNZ_CHR_ID,
2603 CLE_ID,
2604 PPL_ID,
2605 PASSTHRU_START_DATE,
2606 PAYOUT_BASIS,
2607 PAYOUT_BASIS_FORMULA,
2608 EFFECTIVE_FROM,
2609 EFFECTIVE_TO,
2610 OBJECT_VERSION_NUMBER,
2611 PASSTHRU_TERM,
2612 PASSTHRU_STREAM_TYPE_ID,
2613 CREATED_BY,
2614 CREATION_DATE,
2615 LAST_UPDATED_BY,
2616 LAST_UPDATE_DATE,
2617 LAST_UPDATE_LOGIN,
2618 MAJOR_VERSION)
2619 SELECT
2620 ID,
2621 DNZ_CHR_ID,
2622 CLE_ID, PPL_ID,
2623 PASSTHRU_START_DATE,
2624 PAYOUT_BASIS,
2625 PAYOUT_BASIS_FORMULA,
2626 EFFECTIVE_FROM,
2627 EFFECTIVE_TO,
2628 OBJECT_VERSION_NUMBER,
2629 PASSTHRU_TERM,
2630 PASSTHRU_STREAM_TYPE_ID,
2631 CREATED_BY,
2632 CREATION_DATE,
2633 LAST_UPDATED_BY,
2634 LAST_UPDATE_DATE,
2635 LAST_UPDATE_LOGIN,
2636 p_major_version
2637 FROM okl_party_payment_hdr
2638 WHERE dnz_chr_id = p_chr_id;
2639
2640 RETURN l_return_status;
2641 EXCEPTION
2642 -- other appropriate handlers
2643 WHEN OTHERS THEN
2644 -- store SQL error message on message stack
2645 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2646 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2647 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2648 p_token1_value => sqlcode,
2649 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2650 p_token2_value => sqlerrm);
2651
2652 -- notify UNEXPECTED error
2653 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2654 return l_return_status;
2655 END create_version;
2656
2657 --------------------------------------------------------------------
2658 --Restore version
2659 --------------------------------------------------------------------
2660 FUNCTION restore_version(
2661 p_chr_id IN NUMBER,
2662 p_major_version IN NUMBER) RETURN VARCHAR2 IS
2663
2664 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2665
2666 BEGIN
2667 INSERT INTO OKL_PARTY_PAYMENT_HDR
2668 (
2669 ID,
2670 DNZ_CHR_ID,
2671 CLE_ID,
2672 PPL_ID,
2673 PASSTHRU_START_DATE,
2674 PAYOUT_BASIS,
2675 PAYOUT_BASIS_FORMULA,
2676 EFFECTIVE_FROM,
2677 EFFECTIVE_TO,
2678 OBJECT_VERSION_NUMBER,
2679 PASSTHRU_TERM,
2680 PASSTHRU_STREAM_TYPE_ID,
2681 CREATED_BY,
2682 CREATION_DATE,
2683 LAST_UPDATED_BY,
2684 LAST_UPDATE_DATE,
2685 LAST_UPDATE_LOGIN
2686 )
2687 SELECT
2688 ID,
2689 DNZ_CHR_ID,
2690 CLE_ID,
2691 PPL_ID,
2692 PASSTHRU_START_DATE,
2693 PAYOUT_BASIS,
2694 PAYOUT_BASIS_FORMULA,
2695 EFFECTIVE_FROM,
2696 EFFECTIVE_TO,
2697 OBJECT_VERSION_NUMBER,
2698 PASSTHRU_TERM,
2699 PASSTHRU_STREAM_TYPE_ID,
2700 CREATED_BY,
2701 CREATION_DATE,
2702 LAST_UPDATED_BY,
2703 LAST_UPDATE_DATE,
2704 LAST_UPDATE_LOGIN
2705 FROM OKL_PARTY_PAYMENT_HDR_H
2706 WHERE dnz_chr_id = p_chr_id and major_version = p_major_version;
2707
2708 RETURN l_return_status;
2709 EXCEPTION
2710 -- other appropriate handlers
2711 WHEN OTHERS THEN
2712 -- store SQL error message on message stack
2713 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2714 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2715 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2716 p_token1_value => sqlcode,
2717 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2718 p_token2_value => sqlerrm);
2719
2720 -- notify UNEXPECTED error
2721 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2722 return l_return_status;
2723 END restore_version;
2724
2725
2726 END OKL_LDB_PVT;