[Home] [Help]
PACKAGE BODY: APPS.OKL_CAT_PVT
Source
1 PACKAGE BODY OKL_CAT_PVT AS
2 /* $Header: OKLSCATB.pls 120.6 2006/07/13 12:54:46 adagur noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 NULL;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 NULL;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 NULL;
33 END api_copy;
34
35 /************************** BEGIN HAND-CODED *****************************/
36
37 ---------------------------------------------------------------------------
38 -- PROCEDURE Validate_Object_Version_Number
39 ---------------------------------------------------------------------------
40 -- Start of comments
41 --
42 -- Procedure Name : Validate_Object_Version_Number
43 -- Description :
44 -- Business Rules :
45 -- Parameters :
46 -- Version : 1.0
47 -- End of comments
48 ---------------------------------------------------------------------------
49 PROCEDURE Validate_Object_Version_Number(x_return_status OUT NOCOPY VARCHAR2
50 ,p_catv_rec IN catv_rec_type)
51 IS
52 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
53
54 BEGIN
55 -- initialize return status
56 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
57
58 -- check for data before processing
59 IF (p_catv_rec.object_version_number IS NULL) OR
60 (p_catv_rec.object_version_Number = Okl_Api.G_MISS_NUM) THEN
61 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
62 ,p_msg_name => g_required_value
63 ,p_token1 => g_col_name_token
64 ,p_token1_value => 'object_version_number');
65 x_return_status := Okl_Api.G_RET_STS_ERROR;
66 RAISE G_EXCEPTION_HALT_VALIDATION;
67 END IF;
68
69 EXCEPTION
70 WHEN G_EXCEPTION_HALT_VALIDATION THEN
71 -- no processing necessary; validation can continue
72 -- with the next column
73 NULL;
74
75 WHEN OTHERS THEN
76 -- store SQL error message on message stack for caller
77 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
78 p_msg_name => g_unexpected_error,
79 p_token1 => g_sqlcode_token,
80 p_token1_value => SQLCODE,
81 p_token2 => g_sqlerrm_token,
82 p_token2_value => SQLERRM);
83
84 -- notify caller of an UNEXPECTED error
85 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
86
87 END Validate_Object_Version_Number;
88
89 ---------------------------------------------------------------------------
90 -- PROCEDURE Validate_Name
91 ---------------------------------------------------------------------------
92 -- Start of comments
93 --
94 -- Procedure Name : Validate_Name
95 -- Description :
96 -- Business Rules :
97 -- Parameters :
98 -- Version : 1.0
99 -- End of comments
100 ---------------------------------------------------------------------------
101 PROCEDURE Validate_Name(x_return_status OUT NOCOPY VARCHAR2
102 ,p_catv_rec IN catv_rec_type)
103 IS
104
105 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
106 l_dummy VARCHAR2(1);
107 l_row_found BOOLEAN := FALSE;
108 CURSOR c1( p_name OKL_CASH_ALLCTN_RLS.name%TYPE) IS
109 SELECT 1
110 FROM OKL_CASH_ALLCTN_RLS
111 WHERE name = p_name
112 AND id <> NVL(p_catv_rec.id,-9999);
113
114 BEGIN
115 -- initialize return status
116 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
117
118 -- check for data before processing
119 IF (p_catv_rec.name IS NULL) OR
120 (p_catv_rec.name = Okl_Api.G_MISS_NUM) THEN
121 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
122 ,p_msg_name => g_required_value
123 ,p_token1 => g_col_name_token
124 ,p_token1_value => 'name');
125 x_return_status := Okl_Api.G_RET_STS_ERROR;
126 RAISE G_EXCEPTION_HALT_VALIDATION;
127 END IF;
128
129 -- check if name is unique
130
131 OPEN c1(p_catv_rec.name);
132 FETCH c1 INTO l_dummy;
133 l_row_found := c1%FOUND;
134 CLOSE c1;
135 IF l_row_found THEN
136 Okl_Api.set_message(G_APP_NAME,G_UNQS);
137 x_return_status := Okl_Api.G_RET_STS_ERROR;
138 END IF;
139
140 EXCEPTION
141 WHEN G_EXCEPTION_HALT_VALIDATION THEN
142 -- no processing necessary; validation can continue
143 -- with the next column
144 NULL;
145
146 WHEN OTHERS THEN
147 -- store SQL error message on message stack for caller
148 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
149 p_msg_name => g_unexpected_error,
150 p_token1 => g_sqlcode_token,
151 p_token1_value => SQLCODE,
152 p_token2 => g_sqlerrm_token,
153 p_token2_value => SQLERRM);
154
155 -- notify caller of an UNEXPECTED error
156 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
157
158 END Validate_Name;
159
160
161 ---------------------------------------------------------------------------
162 -- PROCEDURE Validate_Und_Pymnt_Alloc_Code
163 ---------------------------------------------------------------------------
164 -- Start of comments
165 --
166 -- Procedure Name : Validate_Und_Pymnt_Alloc_Code
167 -- Description :
168 -- Business Rules :
169 -- Parameters :
170 -- Version : 1.0
171 -- End of comments
172 ---------------------------------------------------------------------------
173 PROCEDURE Validate_Und_Pymnt_Alloc_Code(x_return_status OUT NOCOPY VARCHAR2
174 ,p_catv_rec IN catv_rec_type)
175 IS
176 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
177
178 BEGIN
179 -- initialize return status
180 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
181
182 -- check for data before processing
183 IF ((p_catv_rec.under_payment_allocation_code IS NULL) OR
184 (p_catv_rec.under_payment_allocation_code = Okl_Api.G_MISS_NUM)) OR
185 (p_catv_rec.under_payment_allocation_code NOT IN ('t','T','p','P','u','U')) THEN
186 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
187 ,p_msg_name => g_required_value
188 ,p_token1 => g_col_name_token
189 ,p_token1_value => 'under_payment_allocation_code');
190 x_return_status := Okl_Api.G_RET_STS_ERROR;
191 RAISE G_EXCEPTION_HALT_VALIDATION;
192 END IF;
193
194 EXCEPTION
195 WHEN G_EXCEPTION_HALT_VALIDATION THEN
196 -- no processing necessary; validation can continue
197 -- with the next column
198 NULL;
199
200 WHEN OTHERS THEN
201 -- store SQL error message on message stack for caller
202 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
203 p_msg_name => g_unexpected_error,
204 p_token1 => g_sqlcode_token,
205 p_token1_value => SQLCODE,
206 p_token2 => g_sqlerrm_token,
207 p_token2_value => SQLERRM);
208
209 -- notify caller of an UNEXPECTED error
210 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
211
212 END Validate_Und_Pymnt_Alloc_Code;
213
214 ---------------------------------------------------------------------------
215 -- PROCEDURE Validate_Ovr_Pymnt_Alloc_Code
216 ---------------------------------------------------------------------------
217 -- Start of comments
218 --
219 -- Procedure Name : Validate_Ovr_Pymnt_Alloc_Code
220 -- Description :
221 -- Business Rules :
222 -- Parameters :
223 -- Version : 1.0
224 -- End of comments
225 ---------------------------------------------------------------------------
226 PROCEDURE Validate_Ovr_Pymnt_Alloc_Code(x_return_status OUT NOCOPY VARCHAR2
227 ,p_catv_rec IN catv_rec_type)
228 IS
229 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
230
231 BEGIN
232 -- initialize return status
233 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
234 -- check for data before processing
235 IF ((p_catv_rec.over_payment_allocation_code IS NULL) OR
236 (p_catv_rec.over_payment_allocation_code = Okl_Api.G_MISS_NUM)) OR
237 (p_catv_rec.over_payment_allocation_code NOT IN ('m','M','b','B','f','F')) THEN
238 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
239 ,p_msg_name => g_required_value
240 ,p_token1 => g_col_name_token
241 ,p_token1_value => 'over_payment_allocation_code');
242 x_return_status := Okl_Api.G_RET_STS_ERROR;
243 RAISE G_EXCEPTION_HALT_VALIDATION;
244 END IF;
245 EXCEPTION
246 WHEN G_EXCEPTION_HALT_VALIDATION THEN
247 -- no processing necessary; validation can continue
248 -- with the next column
249 NULL;
250
251 WHEN OTHERS THEN
252 -- store SQL error message on message stack for caller
253 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
254 p_msg_name => g_unexpected_error,
255 p_token1 => g_sqlcode_token,
256 p_token1_value => SQLCODE,
257 p_token2 => g_sqlerrm_token,
258 p_token2_value => SQLERRM);
259
260 -- notify caller of an UNEXPECTED error
261 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
262
263 END Validate_Ovr_Pymnt_Alloc_Code;
264
265 ---------------------------------------------------------------------------
266 -- PROCEDURE Validate_Rec_Mismatch_Code
267 ---------------------------------------------------------------------------
268 -- Start of comments
269 --
270 -- Procedure Name : Validate_Rec_Mismatch_Code
271 -- Description :
272 -- Business Rules :
273 -- Parameters :
274 -- Version : 1.0
275 -- End of comments
276 ---------------------------------------------------------------------------
277 PROCEDURE Validate_Rec_Mismatch_Code(x_return_status OUT NOCOPY VARCHAR2
278 ,p_catv_rec IN catv_rec_type)
279 IS
280 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
281
282 BEGIN
283 -- initialize return status
284 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
285
286 -- check for data before processing
287 IF ((p_catv_rec.receipt_msmtch_allocation_code IS NULL) OR
288 (p_catv_rec.receipt_msmtch_allocation_code = Okl_Api.G_MISS_NUM)) OR
289 (p_catv_rec.receipt_msmtch_allocation_code NOT IN ('a','A','o','O','n','N')) THEN
290 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
291 ,p_msg_name => g_required_value
292 ,p_token1 => g_col_name_token
293 ,p_token1_value => 'receipt_mismatch_allocation_code');
294 x_return_status := Okl_Api.G_RET_STS_ERROR;
295 RAISE G_EXCEPTION_HALT_VALIDATION;
296 END IF;
297
298 EXCEPTION
299 WHEN G_EXCEPTION_HALT_VALIDATION THEN
300 -- no processing necessary; validation can continue
301 -- with the next column
302 NULL;
303
304 WHEN OTHERS THEN
305 -- store SQL error message on message stack for caller
306 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
307 p_msg_name => g_unexpected_error,
308 p_token1 => g_sqlcode_token,
309 p_token1_value => SQLCODE,
310 p_token2 => g_sqlerrm_token,
311 p_token2_value => SQLERRM);
312
313 -- notify caller of an UNEXPECTED error
314 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
315
316 END Validate_Rec_Mismatch_Code;
317
318 ---------------------------------------------------------------------------
319 -- PROCEDURE Validate_Amt_Tol_Percent
320 ---------------------------------------------------------------------------
321 -- Start of comments
322 --
323 -- Procedure Name : Validate_Amt_Tol_Percent
324 -- Description :
325 -- Business Rules :
326 -- Parameters :
327 -- Version : 1.0
328 -- End of comments
329 ---------------------------------------------------------------------------
330 PROCEDURE Validate_Amt_Tol_Percent(x_return_status OUT NOCOPY VARCHAR2
331 ,p_catv_rec IN catv_rec_type)
332 IS
333 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
334
335 BEGIN
336
337 -- initialize return status
338 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
339 -- check for data before processing
340 IF ((p_catv_rec.amount_tolerance_percent IS NULL) OR
341 (p_catv_rec.amount_tolerance_percent = Okl_Api.G_MISS_NUM)) OR
342 ((p_catv_rec.amount_tolerance_percent < 0) OR
343 (p_catv_rec.amount_tolerance_percent > 100)) THEN
344
345 Okl_Api.SET_MESSAGE(p_app_name => 'OKL'
346 ,p_msg_name => 'OKL_BPD_VALID_TOLERANCE');
347 -- ,p_token1 => g_col_name_token
348 -- ,p_token1_value => 'amount_tolerance_percent');
349
350 x_return_status := Okl_Api.G_RET_STS_ERROR;
351
352 -- halt further validation of this column
353 -- RAISE G_EXCEPTION_HALT_VALIDATION;
354 END IF;
355
356 END Validate_Amt_Tol_Percent;
357
358 ---------------------------------------------------------------------------
359 -- PROCEDURE Validate_days_pst_code_val_tol
360 ---------------------------------------------------------------------------
361 -- Start of comments
362 --
363 -- Procedure Name : Validate_days_pst_code_val_tol
364 -- Description :
365 -- Business Rules :
366 -- Parameters :
367 -- Version : 1.
368 -- End of comments
369 ---------------------------------------------------------------------------
370 PROCEDURE Validate_days_pst_code_val_tol(x_return_status OUT NOCOPY VARCHAR2
371 ,p_catv_rec IN catv_rec_type)
372 IS
373 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
374
375 BEGIN
376 -- initialize return status
377 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
378
379 -- check for data before processing
380 IF ((p_catv_rec.days_past_quote_valid_toleranc IS NULL) OR
381 (p_catv_rec.days_past_quote_valid_toleranc = Okl_Api.G_MISS_NUM)) OR
382 (p_catv_rec.days_past_quote_valid_toleranc < 0) THEN
383
384 Okl_Api.SET_MESSAGE(p_app_name => 'OKL'
385 ,p_msg_name => 'OKL_BPD_VALID_TERMINATION');
386 -- ,p_msg_name => 'OKL_BPD_VALID_TOLERANCE');
387 -- ,p_token1 => g_col_name_token
388 -- ,p_token1_value => 'days_past_quote_valid_toleranc');
389 x_return_status := okl_api.G_RET_STS_ERROR;
390 --x_return_status := 'U';
391 -- halt further validation of this column
392 -- RAISE G_EXCEPTION_HALT_VALIDATION;
393 END IF;
394
395 END Validate_days_pst_code_val_tol;
396
397 ---------------------------------------------------------------------------
398 -- PROCEDURE Validate_mnths_bill_ahead
399 ---------------------------------------------------------------------------
400 -- Start of comments
401 --
402 -- Procedure Name : Validate_mnths_bill_ahead
403 -- Description :
404 -- Business Rules :
405 -- Parameters :
406 -- Version : 1.0
407 -- End of comments
408 ---------------------------------------------------------------------------
409 PROCEDURE Validate_mnths_bill_ahead(x_return_status OUT NOCOPY VARCHAR2
410 ,p_catv_rec IN catv_rec_type)
411 IS
412 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
413
414 BEGIN
415 -- initialize return status
416 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
417 -- check for data before processing
418
419 -- sjalasut, commented code as part of user defined streams build. this item
420 -- is no more being displayed on the migrated OAFWK Cash Application Rule Create/ Update page.
421 /*IF ((p_catv_rec.months_to_bill_ahead IS NULL) OR
422 (p_catv_rec.months_to_bill_ahead = Okl_Api.G_MISS_NUM)) OR
423 (p_catv_rec.months_to_bill_ahead < 0) THEN
424
425 Okl_Api.SET_MESSAGE(p_app_name => 'OKL'
426 ,p_msg_name => 'OKL_BPD_VALID_MNTHS');
427 -- ,p_token1 => g_col_name_token
428 -- ,p_token1_value => 'months_to_bill_ahead');
429 x_return_status := Okl_Api.G_RET_STS_ERROR;
430 -- halt further validation of this column
431 -- RAISE G_EXCEPTION_HALT_VALIDATION;
432 END IF;*/
433
434 END Validate_mnths_bill_ahead;
435
436
437 ---------------------------------------------------------------------------
438 -- PROCEDURE Validate_CAU_ID
439 ---------------------------------------------------------------------------
440 -- Start of comments
441 --
442 -- Procedure Name : Validate_CAU_ID
443 -- Description :
444 -- Business Rules :
445 -- Parameters :
446 -- Version : 1.0
447 -- End of comments
448 ---------------------------------------------------------------------------
449 PROCEDURE Validate_CAU_ID(x_return_status OUT NOCOPY VARCHAR2
450 ,p_catv_rec IN catv_rec_type)
451 IS
452
453 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
454 l_dummy NUMBER;
455 l_row_found BOOLEAN := FALSE;
456
457 CURSOR c1( p_CAU_ID OKL_CSH_ALLCTN_RL_HDR.ID%TYPE) IS
458 SELECT 1
459 FROM OKL_CSH_ALLCTN_RL_HDR
460 WHERE id = p_CAU_ID;
461
462 BEGIN
463 IF (p_catv_rec.CAU_ID = OKL_API.G_MISS_NUM OR
464 p_catv_rec.CAU_ID IS NULL)
465 THEN
466 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'CAU_ID');
467 l_return_status := OKL_API.G_RET_STS_ERROR;
468 END IF;
469
470 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
471 OPEN c1(p_catv_rec.CAU_ID);
472 FETCH c1 INTO l_dummy;
473 IF (c1%NOTFOUND) THEN
474 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
475 p_msg_name => G_NO_PARENT_RECORD,
476 p_token1 => G_COL_NAME_TOKEN,
477 p_token1_value => 'CAU_ID',
478 p_token2 => G_CHILD_TABLE_TOKEN,
479 p_token2_value => 'OKL_CASH_ALLCTN_RLS',
480 p_token3 => G_PARENT_TABLE_TOKEN,
481 p_token3_value => 'OKL_CSH_ALLCTN_RL_HDR');
482 l_return_status := OKL_API.G_RET_STS_ERROR;
483 END IF;
484 CLOSE c1;
485 END IF;
486
487 x_return_status := l_return_status;
488 EXCEPTION
489 WHEN G_EXCEPTION_HALT_VALIDATION THEN
490 -- no processing necessary; validation can continue
491 -- with the next column
492 NULL;
493
494 WHEN OTHERS THEN
495 -- store SQL error message on message stack for caller
496 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
497 p_msg_name => g_unexpected_error,
498 p_token1 => g_sqlcode_token,
499 p_token1_value => SQLCODE,
500 p_token2 => g_sqlerrm_token,
501 p_token2_value => SQLERRM);
502
503 -- notify caller of an UNEXPECTED error
504 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
505
506 END Validate_CAU_ID;
507
508
509 --------------------------------------------
510 -- Validate_Attributes for: START_DATE --
511 --------------------------------------------
512 PROCEDURE validate_start_date(
513 x_return_status OUT NOCOPY VARCHAR2,
514 p_catv_rec IN catv_rec_type) AS
515 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
516 BEGIN
517 IF (p_catv_rec.start_date = OKC_API.G_MISS_DATE OR
518 p_catv_rec.start_date IS NULL)
519 THEN
520 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'start_date');
521 l_return_status := OKC_API.G_RET_STS_ERROR;
522 END IF;
523
524 x_return_status := l_return_status;
525 EXCEPTION
526 WHEN OTHERS THEN
527 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
528 ,p_msg_name => G_UNEXPECTED_ERROR
529 ,p_token1 => G_SQLCODE_TOKEN
530 ,p_token1_value => SQLCODE
531 ,p_token2 => G_SQLERRM_TOKEN
532 ,p_token2_value => SQLERRM);
533 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
534 END validate_start_date;
535
536 /************************** END HAND-CODED *****************************/
537
538 ---------------------------------------------------------------------------
539 -- FUNCTION get_rec for: OKL_CASH_ALLCTN_RLS
540 ---------------------------------------------------------------------------
541 -- Start of comments
542 --
543 -- Function Name : get_rec
544 -- Description : To get the record from the okl_cash_allctn_rls table.
545 -- Business Rules :
546 -- Parameters : p_cat_rec, x_no_data_found
547 -- Version : 1.0
548 -- History : 25-AUG-04 abindal modified to include NUM_OF_DAYS_TO_HOLD_ADV_PAY column.
549 -- : 12-oct-04 sjalasut changed the column NUM_OF_DAYS_TO_HOLD_ADV_PAY
550 -- to NUM_DAYS_HOLD_ADV_PAY per user defined streams build
551 -- End of comments
552 ---------------------------------------------------------------------------
553
554 FUNCTION get_rec (
555 p_cat_rec IN cat_rec_type,
556 x_no_data_found OUT NOCOPY BOOLEAN
557 ) RETURN cat_rec_type IS
558 CURSOR cat_pk_csr (p_id IN NUMBER) IS
559 SELECT
560 ID,
561 NAME,
562 OBJECT_VERSION_NUMBER,
563 DESCRIPTION,
564 START_DATE,
565 END_DATE,
566 AMOUNT_TOLERANCE_PERCENT,
567 DAYS_PAST_QUOTE_VALID_TOLERANC,
568 MONTHS_TO_BILL_AHEAD,
569 UNDER_PAYMENT_ALLOCATION_CODE,
570 OVER_PAYMENT_ALLOCATION_CODE,
571 RECEIPT_MSMTCH_ALLOCATION_CODE,
572 DEFAULT_RULE,
573 ATTRIBUTE_CATEGORY,
574 ATTRIBUTE1,
575 ATTRIBUTE2,
576 ATTRIBUTE3,
577 ATTRIBUTE4,
578 ATTRIBUTE5,
579 ATTRIBUTE6,
580 ATTRIBUTE7,
581 ATTRIBUTE8,
582 ATTRIBUTE9,
583 ATTRIBUTE10,
584 ATTRIBUTE11,
585 ATTRIBUTE12,
586 ATTRIBUTE13,
587 ATTRIBUTE14,
588 ATTRIBUTE15,
589 ORG_ID,
590 CREATED_BY,
591 CREATION_DATE,
592 LAST_UPDATED_BY,
593 LAST_UPDATE_DATE,
594 LAST_UPDATE_LOGIN,
595 CAU_ID,
596 -- column added to hold the number of days for advanced receipts
597 NUM_DAYS_HOLD_ADV_PAY
598 FROM Okl_Cash_Allctn_Rls
599 WHERE okl_cash_allctn_rls.id = p_id;
600 l_cat_pk cat_pk_csr%ROWTYPE;
601 l_cat_rec cat_rec_type;
602 BEGIN
603 x_no_data_found := TRUE;
604 -- Get current database values
605 OPEN cat_pk_csr (p_cat_rec.id);
606 FETCH cat_pk_csr INTO
607 l_cat_rec.ID,
608 l_cat_rec.NAME,
609 l_cat_rec.OBJECT_VERSION_NUMBER,
610 l_cat_rec.DESCRIPTION,
611 l_cat_rec.start_date,
612 l_cat_rec.end_date,
613 l_cat_rec.AMOUNT_TOLERANCE_PERCENT,
614 l_cat_rec.DAYS_PAST_QUOTE_VALID_TOLERANC,
615 l_cat_rec.MONTHS_TO_BILL_AHEAD,
616 l_cat_rec.UNDER_PAYMENT_ALLOCATION_CODE,
617 l_cat_rec.OVER_PAYMENT_ALLOCATION_CODE,
618 l_cat_rec.RECEIPT_MSMTCH_ALLOCATION_CODE,
619 l_cat_rec.DEFAULT_RULE,
620 l_cat_rec.ATTRIBUTE_CATEGORY,
621 l_cat_rec.ATTRIBUTE1,
622 l_cat_rec.ATTRIBUTE2,
623 l_cat_rec.ATTRIBUTE3,
624 l_cat_rec.ATTRIBUTE4,
625 l_cat_rec.ATTRIBUTE5,
626 l_cat_rec.ATTRIBUTE6,
627 l_cat_rec.ATTRIBUTE7,
628 l_cat_rec.ATTRIBUTE8,
629 l_cat_rec.ATTRIBUTE9,
630 l_cat_rec.ATTRIBUTE10,
631 l_cat_rec.ATTRIBUTE11,
632 l_cat_rec.ATTRIBUTE12,
633 l_cat_rec.ATTRIBUTE13,
634 l_cat_rec.ATTRIBUTE14,
635 l_cat_rec.ATTRIBUTE15,
636 l_cat_rec.ORG_ID,
637 l_cat_rec.CREATED_BY,
638 l_cat_rec.CREATION_DATE,
639 l_cat_rec.LAST_UPDATED_BY,
640 l_cat_rec.LAST_UPDATE_DATE,
641 l_cat_rec.LAST_UPDATE_LOGIN,
642 l_cat_rec.CAU_ID,
643 -- new column to hold number of days to reserve advanced payment for contract.
644 l_cat_rec.NUM_DAYS_HOLD_ADV_PAY;
645 x_no_data_found := cat_pk_csr%NOTFOUND;
646 CLOSE cat_pk_csr;
647 RETURN(l_cat_rec);
648 END get_rec;
649
650 FUNCTION get_rec (
651 p_cat_rec IN cat_rec_type
652 ) RETURN cat_rec_type IS
653 l_row_notfound BOOLEAN := TRUE;
654 BEGIN
655 RETURN(get_rec(p_cat_rec, l_row_notfound));
656 END get_rec;
657 ---------------------------------------------------------------------------
658 -- FUNCTION get_rec for: OKL_CASH_ALLCTN_RLS_V
659 ---------------------------------------------------------------------------
660 ---------------------------------------------------------------------------
661 -- Start of comments
662 --
663 -- Function Name : get_rec
664 -- Description : To get the record from the okl_cash_allctn_rls table.
665 -- Business Rules :
666 -- Parameters : p_catv_rec, x_no_data_found
667 -- Version : 1.0
668 -- History : 25-AUG-04 abindal modified to include NUM_OF_DAYS_TO_HOLD_ADV_PAY column.
669 -- : 12-oct-04 sjalasut changed the column NUM_OF_DAYS_TO_HOLD_ADV_PAY
670 -- to NUM_DAYS_HOLD_ADV_PAY per user defined streams build
671 -- End of comments
672 ---------------------------------------------------------------------------
673
674 FUNCTION get_rec (
675 p_catv_rec IN catv_rec_type,
676 x_no_data_found OUT NOCOPY BOOLEAN
677 ) RETURN catv_rec_type IS
678 CURSOR okl_catv_pk_csr (p_id IN NUMBER) IS
679 SELECT
680 ID,
681 OBJECT_VERSION_NUMBER,
682 NAME,
683 DESCRIPTION,
684 start_date,
685 end_date,
686 AMOUNT_TOLERANCE_PERCENT,
687 DAYS_PAST_QUOTE_VALID_TOLERANC,
688 MONTHS_TO_BILL_AHEAD,
689 UNDER_PAYMENT_ALLOCATION_CODE,
690 OVER_PAYMENT_ALLOCATION_CODE,
691 RECEIPT_MSMTCH_ALLOCATION_CODE,
692 DEFAULT_RULE,
693 ATTRIBUTE_CATEGORY,
694 ATTRIBUTE1,
695 ATTRIBUTE2,
696 ATTRIBUTE3,
697 ATTRIBUTE4,
698 ATTRIBUTE5,
699 ATTRIBUTE6,
700 ATTRIBUTE7,
701 ATTRIBUTE8,
702 ATTRIBUTE9,
703 ATTRIBUTE10,
704 ATTRIBUTE11,
705 ATTRIBUTE12,
706 ATTRIBUTE13,
707 ATTRIBUTE14,
708 ATTRIBUTE15,
709 ORG_ID,
710 CREATED_BY,
711 CREATION_DATE,
712 LAST_UPDATED_BY,
713 LAST_UPDATE_DATE,
714 LAST_UPDATE_LOGIN,
715 CAU_ID,
716 -- new column to hold number of days to reserve advanced payment for contract.
717 NUM_DAYS_HOLD_ADV_PAY
718 FROM OKL_CASH_ALLCTN_RLS
719 WHERE OKL_CASH_ALLCTN_RLS.id = p_id;
720 l_okl_catv_pk okl_catv_pk_csr%ROWTYPE;
721 l_catv_rec catv_rec_type;
722 BEGIN
723 x_no_data_found := TRUE;
724 -- Get current database values
725 OPEN okl_catv_pk_csr (p_catv_rec.id);
726 FETCH okl_catv_pk_csr INTO
727 l_catv_rec.ID,
728 l_catv_rec.OBJECT_VERSION_NUMBER,
729 l_catv_rec.NAME,
730 l_catv_rec.DESCRIPTION,
731 l_catv_rec.start_date,
732 l_catv_rec.end_date,
733 l_catv_rec.AMOUNT_TOLERANCE_PERCENT,
734 l_catv_rec.DAYS_PAST_QUOTE_VALID_TOLERANC,
735 l_catv_rec.MONTHS_TO_BILL_AHEAD,
736 l_catv_rec.UNDER_PAYMENT_ALLOCATION_CODE,
737 l_catv_rec.OVER_PAYMENT_ALLOCATION_CODE,
738 l_catv_rec.RECEIPT_MSMTCH_ALLOCATION_CODE,
739 l_catv_rec.DEFAULT_RULE,
740 l_catv_rec.ATTRIBUTE_CATEGORY,
741 l_catv_rec.ATTRIBUTE1,
742 l_catv_rec.ATTRIBUTE2,
743 l_catv_rec.ATTRIBUTE3,
744 l_catv_rec.ATTRIBUTE4,
745 l_catv_rec.ATTRIBUTE5,
746 l_catv_rec.ATTRIBUTE6,
747 l_catv_rec.ATTRIBUTE7,
748 l_catv_rec.ATTRIBUTE8,
749 l_catv_rec.ATTRIBUTE9,
750 l_catv_rec.ATTRIBUTE10,
751 l_catv_rec.ATTRIBUTE11,
752 l_catv_rec.ATTRIBUTE12,
753 l_catv_rec.ATTRIBUTE13,
754 l_catv_rec.ATTRIBUTE14,
755 l_catv_rec.ATTRIBUTE15,
756 l_catv_rec.ORG_ID,
757 l_catv_rec.CREATED_BY,
758 l_catv_rec.CREATION_DATE,
759 l_catv_rec.LAST_UPDATED_BY,
760 l_catv_rec.LAST_UPDATE_DATE,
761 l_catv_rec.LAST_UPDATE_LOGIN,
762 l_catv_rec.CAU_ID,
763 -- new column to hold number of days to reserve advanced payment for contract.
764 l_catv_rec.NUM_DAYS_HOLD_ADV_PAY;
765 x_no_data_found := okl_catv_pk_csr%NOTFOUND;
766 CLOSE okl_catv_pk_csr;
767 RETURN(l_catv_rec);
768 END get_rec;
769
770 FUNCTION get_rec (
771 p_catv_rec IN catv_rec_type
772 ) RETURN catv_rec_type IS
773 l_row_notfound BOOLEAN := TRUE;
774 BEGIN
775 RETURN(get_rec(p_catv_rec, l_row_notfound));
776 END get_rec;
777
778 -----------------------------------------------------------
779 -- FUNCTION null_out_defaults for: OKL_CASH_ALLCTN_RLS_V --
780 -----------------------------------------------------------
781 ---------------------------------------------------------------------------
782 -- Start of comments
783 --
784 -- Function Name : null_out_defaults
785 -- Description : If the field has default values then equate it to null.
786 -- Business Rules :
787 -- Parameters : p_catv_rec
788 -- Version : 1.0
789 -- History : 25-AUG-04 abindal modified to include the validation
790 -- for NUM_OF_DAYS_TO_HOLD_ADV_PAY column also.
791 -- : 12-oct-04 sjalasut changed the column NUM_OF_DAYS_TO_HOLD_ADV_PAY
792 -- to NUM_DAYS_HOLD_ADV_PAY per user defined streams build
793 -- End of comments
794 ---------------------------------------------------------------------------
795
796 FUNCTION null_out_defaults (
797 p_catv_rec IN catv_rec_type
798 ) RETURN catv_rec_type IS
799 l_catv_rec catv_rec_type := p_catv_rec;
800 BEGIN
801 IF (l_catv_rec.object_version_number = Okl_Api.G_MISS_NUM) THEN
802 l_catv_rec.object_version_number := NULL;
803 END IF;
804 IF (l_catv_rec.name = Okl_Api.G_MISS_CHAR) THEN
805 l_catv_rec.name := NULL;
806 END IF;
807 IF (l_catv_rec.description = Okl_Api.G_MISS_CHAR) THEN
808 l_catv_rec.description := NULL;
809 END IF;
810
811 IF (l_catv_rec.start_date = Okl_Api.G_MISS_DATE) THEN
812 l_catv_rec.start_date := NULL;
813 END IF;
814
815 IF (l_catv_rec.end_date = Okl_Api.G_MISS_DATE) THEN
816 l_catv_rec.end_date := NULL;
817 END IF;
818
819 IF (l_catv_rec.amount_tolerance_percent = Okl_Api.G_MISS_NUM) THEN
820 l_catv_rec.amount_tolerance_percent := NULL;
821 END IF;
822 IF (l_catv_rec.days_past_quote_valid_toleranc = Okl_Api.G_MISS_NUM) THEN
823 l_catv_rec.days_past_quote_valid_toleranc := NULL;
824 END IF;
825 IF (l_catv_rec.months_to_bill_ahead = Okl_Api.G_MISS_NUM) THEN
826 l_catv_rec.months_to_bill_ahead := NULL;
827 END IF;
828 IF (l_catv_rec.under_payment_allocation_code = Okl_Api.G_MISS_CHAR) THEN
829 l_catv_rec.under_payment_allocation_code := NULL;
830 END IF;
831 IF (l_catv_rec.over_payment_allocation_code = Okl_Api.G_MISS_CHAR) THEN
832 l_catv_rec.over_payment_allocation_code := NULL;
833 END IF;
834 IF (l_catv_rec.receipt_msmtch_allocation_code = Okl_Api.G_MISS_CHAR) THEN
835 l_catv_rec.receipt_msmtch_allocation_code := NULL;
836 END IF;
837 IF (l_catv_rec.default_rule = Okl_Api.G_MISS_CHAR) THEN
838 l_catv_rec.default_rule := NULL;
839 END IF;
840 IF (l_catv_rec.attribute_category = Okl_Api.G_MISS_CHAR) THEN
841 l_catv_rec.attribute_category := NULL;
842 END IF;
843 IF (l_catv_rec.attribute1 = Okl_Api.G_MISS_CHAR) THEN
844 l_catv_rec.attribute1 := NULL;
845 END IF;
846 IF (l_catv_rec.attribute2 = Okl_Api.G_MISS_CHAR) THEN
847 l_catv_rec.attribute2 := NULL;
848 END IF;
849 IF (l_catv_rec.attribute3 = Okl_Api.G_MISS_CHAR) THEN
850 l_catv_rec.attribute3 := NULL;
851 END IF;
852 IF (l_catv_rec.attribute4 = Okl_Api.G_MISS_CHAR) THEN
853 l_catv_rec.attribute4 := NULL;
854 END IF;
855 IF (l_catv_rec.attribute5 = Okl_Api.G_MISS_CHAR) THEN
856 l_catv_rec.attribute5 := NULL;
857 END IF;
858 IF (l_catv_rec.attribute6 = Okl_Api.G_MISS_CHAR) THEN
859 l_catv_rec.attribute6 := NULL;
860 END IF;
861 IF (l_catv_rec.attribute7 = Okl_Api.G_MISS_CHAR) THEN
862 l_catv_rec.attribute7 := NULL;
863 END IF;
864 IF (l_catv_rec.attribute8 = Okl_Api.G_MISS_CHAR) THEN
865 l_catv_rec.attribute8 := NULL;
866 END IF;
867 IF (l_catv_rec.attribute9 = Okl_Api.G_MISS_CHAR) THEN
868 l_catv_rec.attribute9 := NULL;
869 END IF;
870 IF (l_catv_rec.attribute10 = Okl_Api.G_MISS_CHAR) THEN
871 l_catv_rec.attribute10 := NULL;
872 END IF;
873 IF (l_catv_rec.attribute11 = Okl_Api.G_MISS_CHAR) THEN
874 l_catv_rec.attribute11 := NULL;
875 END IF;
876 IF (l_catv_rec.attribute12 = Okl_Api.G_MISS_CHAR) THEN
877 l_catv_rec.attribute12 := NULL;
878 END IF;
879 IF (l_catv_rec.attribute13 = Okl_Api.G_MISS_CHAR) THEN
880 l_catv_rec.attribute13 := NULL;
881 END IF;
882 IF (l_catv_rec.attribute14 = Okl_Api.G_MISS_CHAR) THEN
883 l_catv_rec.attribute14 := NULL;
884 END IF;
885 IF (l_catv_rec.attribute15 = Okl_Api.G_MISS_CHAR) THEN
886 l_catv_rec.attribute15 := NULL;
887 END IF;
888 IF (l_catv_rec.org_id = Okl_Api.G_MISS_NUM) THEN
889 l_catv_rec.org_id := NULL;
890 END IF;
891 IF (l_catv_rec.created_by = Okl_Api.G_MISS_NUM) THEN
892 l_catv_rec.created_by := NULL;
893 END IF;
894 IF (l_catv_rec.creation_date = Okl_Api.G_MISS_DATE) THEN
895 l_catv_rec.creation_date := NULL;
896 END IF;
897 IF (l_catv_rec.last_updated_by = Okl_Api.G_MISS_NUM) THEN
898 l_catv_rec.last_updated_by := NULL;
899 END IF;
900 IF (l_catv_rec.last_update_date = Okl_Api.G_MISS_DATE) THEN
901 l_catv_rec.last_update_date := NULL;
902 END IF;
903 IF (l_catv_rec.last_update_login = Okl_Api.G_MISS_NUM) THEN
904 l_catv_rec.last_update_login := NULL;
905 END IF;
906
907 IF (l_catv_rec.CAU_ID = Okl_Api.G_MISS_NUM) THEN
908 l_catv_rec.CAU_ID := NULL;
909 END IF;
910 -- new column to hold number of days to reserve advanced payment for contract.
911 IF (l_catv_rec.num_days_hold_adv_pay = Okl_Api.G_MISS_NUM) THEN
912 l_catv_rec.num_days_hold_adv_pay := NULL;
913 END IF;
914 RETURN(l_catv_rec);
915 END null_out_defaults;
916 ---------------------------------------------------------------------------
917 -- PROCEDURE Validate_Attributes
918 ---------------------------------------------------------------------------
919 ---------------------------------------------------
920 -- Validate_Attributes for:OKL_CASH_ALLCTN_RLS_V --
921 ---------------------------------------------------
922 FUNCTION Validate_Attributes (
923 p_catv_rec IN catv_rec_type
924 ) RETURN VARCHAR2 IS
925 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
926 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
927 BEGIN
928
929 -- call each column-level validation
930
931 IF p_catv_rec.id = Okl_Api.G_MISS_NUM OR
932 p_catv_rec.id IS NULL
933 THEN
934 Okl_Api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
935 l_return_status := Okl_Api.G_RET_STS_ERROR;
936 END IF;
937
938
939 -- Validate_Object_Version_Number
940
941 Validate_Object_Version_Number(x_return_status, p_catv_rec);
942
943 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
944 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
945 -- need to leave
946 l_return_status := x_return_status;
947 RAISE G_EXCEPTION_HALT_VALIDATION;
948 ELSE
949 -- record that there was an error
950 l_return_status := x_return_status;
951 END IF;
952 END IF;
953
954
955 -- ***
956 -- start_date
957 -- ***
958 validate_start_date(l_return_status, p_catv_rec);
959 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
960 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
961 x_return_status := l_return_status;
962 RAISE G_EXCEPTION_HALT_VALIDATION;
963 ELSE
964 x_return_status := l_return_status;
965 END IF;
966 END IF;
967
968
969 /*
970 -- Validate_Name and check for uniqueness
971
972 Validate_Name (x_return_status, p_catv_rec);
973
974 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
975 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
976 -- need to leave
977 l_return_status := x_return_status;
978 RAISE G_EXCEPTION_HALT_VALIDATION;
979 ELSE
980 -- record that there was an error
981 l_return_status := x_return_status;
982 END IF;
983 END IF;
984
985 RETURN(l_return_status);
986
987
988 -- Validate_Under_Payment_Allocation_Code
989
990 Validate_Und_Pymnt_Alloc_Code (x_return_status, p_catv_rec);
991
992 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
993 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
994 -- need to leave
995 l_return_status := x_return_status;
996 RAISE G_EXCEPTION_HALT_VALIDATION;
997 ELSE
998 -- record that there was an error
999 l_return_status := x_return_status;
1000 END IF;
1001 END IF;
1002
1003 RETURN(l_return_status);
1004
1005 -- Validate_Over_Payment_Allocation_Code
1006
1007 Validate_Ovr_Pymnt_Alloc_Code (x_return_status, p_catv_rec);
1008
1009 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1010 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1011 -- need to leave
1012 l_return_status := x_return_status;
1013 RAISE G_EXCEPTION_HALT_VALIDATION;
1014 ELSE
1015 -- record that there was an error
1016 l_return_status := x_return_status;
1017 END IF;
1018 END IF;
1019
1020 RETURN(l_return_status);
1021
1022 -- Validate_Rec_Mismatch_Allocation_Code
1023
1024 Validate_Rec_Mismatch_Code (x_return_status, p_catv_rec);
1025
1026 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1027 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1028 -- need to leave
1029 l_return_status := x_return_status;
1030 RAISE G_EXCEPTION_HALT_VALIDATION;
1031 ELSE
1032 -- record that there was an error
1033 l_return_status := x_return_status;
1034 END IF;
1035 END IF;
1036
1037 RETURN(l_return_status);
1038
1039 */
1040
1041 Validate_Amt_Tol_Percent(x_return_status => x_return_status,
1042 p_catv_rec => p_catv_rec);
1043
1044 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1045 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1046 -- need to exit
1047 l_return_status := x_return_status;
1048 RAISE G_EXCEPTION_HALT_VALIDATION;
1049 ELSE
1050 -- there was an error
1051 l_return_status := x_return_status;
1052 END IF;
1053 END IF;
1054
1055 Validate_days_pst_code_val_tol(x_return_status => x_return_status,
1056 p_catv_rec => p_catv_rec);
1057
1058
1059 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1060 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1061 -- need to exit
1062 l_return_status := x_return_status;
1063 RAISE G_EXCEPTION_HALT_VALIDATION;
1064 ELSE
1065 -- there was an error
1066 l_return_status := x_return_status;
1067 END IF;
1068 END IF;
1069
1070
1071 Validate_mnths_bill_ahead(x_return_status => x_return_status,
1072 p_catv_rec => p_catv_rec);
1073 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1074 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1075 -- need to exit
1076 l_return_status := x_return_status;
1077 RAISE G_EXCEPTION_HALT_VALIDATION;
1078 ELSE
1079 -- there was an error
1080 l_return_status := x_return_status;
1081 END IF;
1082 END IF;
1083
1084 -- Validate_CAU_ID
1085
1086 Validate_CAU_ID (x_return_status, p_catv_rec);
1087
1088 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1089 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1090 -- need to leave
1091 l_return_status := x_return_status;
1092 RAISE G_EXCEPTION_HALT_VALIDATION;
1093 ELSE
1094 -- record that there was an error
1095 l_return_status := x_return_status;
1096 END IF;
1097 END IF;
1098
1099 RETURN(l_return_status);
1100
1101 EXCEPTION
1102 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1103 -- exit with return status
1104 NULL;
1105 RETURN (l_return_status);
1106
1107 WHEN OTHERS THEN
1108 -- store SQL error message on message stack for caller
1109 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
1110 p_msg_name => g_unexpected_error,
1111 p_token1 => g_sqlcode_token,
1112 p_token1_value => SQLCODE,
1113 p_token2 => g_sqlerrm_token,
1114 p_token2_value => SQLERRM);
1115 -- notify caller of an UNEXPECTED error
1116 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1117 RETURN(l_return_status);
1118
1119 END Validate_Attributes;
1120
1121 ---------------------------------------------------------------------------
1122 -- PROCEDURE Validate_Record
1123 ---------------------------------------------------------------------------
1124 -----------------------------------------------
1125 -- Validate_Record for:OKL_CASH_ALLCTN_RLS_V --
1126 -----------------------------------------------
1127 FUNCTION Validate_Record (
1128 p_catv_rec IN catv_rec_type
1129 ) RETURN VARCHAR2 IS
1130 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1131 BEGIN
1132 RETURN (l_return_status);
1133 END Validate_Record;
1134
1135 ---------------------------------------------------------------------------
1136 -- PROCEDURE Migrate
1137 ---------------------------------------------------------------------------
1138 ---------------------------------------------------------------------------
1139 -- Start of comments
1140 --
1141 -- Procedure Name : migrate
1142 -- Description : This procedure is used for copying the record structure.
1143 -- Business Rules :
1144 -- Parameters : p_from, p_to
1145 -- Version : 1.0
1146 -- History : 25-AUG-04 abindal modified to include NUM_OF_DAYS_TO_HOLD_ADV_PAY column.
1147 -- : 12-oct-04 sjalasut changed the column NUM_OF_DAYS_TO_HOLD_ADV_PAY
1148 -- to NUM_DAYS_HOLD_ADV_PAY per user defined streams build
1149 -- End of comments
1150 ---------------------------------------------------------------------------
1151
1152 PROCEDURE migrate (
1153 p_from IN catv_rec_type,
1154 p_to IN OUT NOCOPY cat_rec_type
1155 ) IS
1156 BEGIN
1157 p_to.id := p_from.id;
1158 p_to.name := p_from.name;
1159 p_to.object_version_number := p_from.object_version_number;
1160 p_to.description := p_from.description;
1161
1162 p_to.start_date := p_from.start_date;
1163 p_to.end_date := p_from.end_date;
1164
1165 p_to.amount_tolerance_percent := p_from.amount_tolerance_percent;
1166 p_to.days_past_quote_valid_toleranc := p_from.days_past_quote_valid_toleranc;
1167 p_to.months_to_bill_ahead := p_from.months_to_bill_ahead;
1168 p_to.under_payment_allocation_code := p_from.under_payment_allocation_code;
1169 p_to.over_payment_allocation_code := p_from.over_payment_allocation_code;
1170 p_to.receipt_msmtch_allocation_code := p_from.receipt_msmtch_allocation_code;
1171 p_to.default_rule := p_from.default_rule;
1172 p_to.attribute_category := p_from.attribute_category;
1173 p_to.attribute1 := p_from.attribute1;
1174 p_to.attribute2 := p_from.attribute2;
1175 p_to.attribute3 := p_from.attribute3;
1176 p_to.attribute4 := p_from.attribute4;
1177 p_to.attribute5 := p_from.attribute5;
1178 p_to.attribute6 := p_from.attribute6;
1179 p_to.attribute7 := p_from.attribute7;
1180 p_to.attribute8 := p_from.attribute8;
1181 p_to.attribute9 := p_from.attribute9;
1182 p_to.attribute10 := p_from.attribute10;
1183 p_to.attribute11 := p_from.attribute11;
1184 p_to.attribute12 := p_from.attribute12;
1185 p_to.attribute13 := p_from.attribute13;
1186 p_to.attribute14 := p_from.attribute14;
1187 p_to.attribute15 := p_from.attribute15;
1188 p_to.org_id := p_from.org_id;
1189 p_to.created_by := p_from.created_by;
1190 p_to.creation_date := p_from.creation_date;
1191 p_to.last_updated_by := p_from.last_updated_by;
1192 p_to.last_update_date := p_from.last_update_date;
1193 p_to.last_update_login := p_from.last_update_login;
1194 p_to.CAU_ID := p_from.CAU_ID;
1195 -- new column to hold number of days to reserve advanced payment for contract.
1196 p_to.num_days_hold_adv_pay := p_from.num_days_hold_adv_pay;
1197 END migrate;
1198 ---------------------------------------------------------------------------
1199 -- PROCEDURE Migrate
1200 ---------------------------------------------------------------------------
1201 ---------------------------------------------------------------------------
1202 -- Start of comments
1203 --
1204 -- Procedure Name : migrate
1205 -- Description : This procedure is used for copying the record structure.
1206 -- Business Rules :
1207 -- Parameters : p_from, p_to
1208 -- Version : 1.0
1209 -- History : 25-AUG-04 abindal modified to include NUM_OF_DAYS_TO_HOLD_ADV_PAY column.
1210 -- : 12-oct-04 sjalasut changed the column NUM_OF_DAYS_TO_HOLD_ADV_PAY
1211 -- to NUM_DAYS_HOLD_ADV_PAY per user defined streams build
1212 -- End of comments
1213 ---------------------------------------------------------------------------
1214
1215 PROCEDURE migrate (
1216 p_from IN cat_rec_type,
1217 p_to IN OUT NOCOPY catv_rec_type
1218 ) IS
1219 BEGIN
1220 p_to.id := p_from.id;
1221 p_to.name := p_from.name;
1222 p_to.object_version_number := p_from.object_version_number;
1223 p_to.description := p_from.description;
1224
1225 p_to.start_date := p_from.start_date;
1226 p_to.end_date := p_from.end_date;
1227
1228 p_to.amount_tolerance_percent := p_from.amount_tolerance_percent;
1229 p_to.days_past_quote_valid_toleranc := p_from.days_past_quote_valid_toleranc;
1230 p_to.months_to_bill_ahead := p_from.months_to_bill_ahead;
1231 p_to.under_payment_allocation_code := p_from.under_payment_allocation_code;
1232 p_to.over_payment_allocation_code := p_from.over_payment_allocation_code;
1233 p_to.receipt_msmtch_allocation_code := p_from.receipt_msmtch_allocation_code;
1234 p_to.default_rule := p_from.default_rule;
1235 p_to.attribute_category := p_from.attribute_category;
1236 p_to.attribute1 := p_from.attribute1;
1237 p_to.attribute2 := p_from.attribute2;
1238 p_to.attribute3 := p_from.attribute3;
1239 p_to.attribute4 := p_from.attribute4;
1240 p_to.attribute5 := p_from.attribute5;
1241 p_to.attribute6 := p_from.attribute6;
1242 p_to.attribute7 := p_from.attribute7;
1243 p_to.attribute8 := p_from.attribute8;
1244 p_to.attribute9 := p_from.attribute9;
1245 p_to.attribute10 := p_from.attribute10;
1246 p_to.attribute11 := p_from.attribute11;
1247 p_to.attribute12 := p_from.attribute12;
1248 p_to.attribute13 := p_from.attribute13;
1249 p_to.attribute14 := p_from.attribute14;
1250 p_to.attribute15 := p_from.attribute15;
1251 p_to.org_id := p_from.org_id;
1252 p_to.created_by := p_from.created_by;
1253 p_to.creation_date := p_from.creation_date;
1254 p_to.last_updated_by := p_from.last_updated_by;
1255 p_to.last_update_date := p_from.last_update_date;
1256 p_to.last_update_login := p_from.last_update_login;
1257 p_to.CAU_ID := p_from.CAU_ID;
1258 -- new column to hold number of days for advanced receipts.
1259 p_to.num_days_hold_adv_pay := p_from.num_days_hold_adv_pay;
1260 END migrate;
1261 /*
1262 PROCEDURE migrate (
1263 p_from IN cat_rec_type,
1264 p_to IN OUT NOCOPY okl_cash_allctn_rls_h_rec_type
1265 ) IS
1266 BEGIN
1267 p_to.id := p_from.id;
1268 p_to.name := p_from.name;
1269 p_to.object_version_number := p_from.object_version_number;
1270 p_to.description := p_from.description;
1271 p_to.amount_tolerance_percent := p_from.amount_tolerance_percent;
1272 p_to.days_past_quote_valid_toleranc := p_from.days_past_quote_valid_toleranc;
1273 p_to.months_to_bill_ahead := p_from.months_to_bill_ahead;
1274 p_to.under_payment_allocation_code := p_from.under_payment_allocation_code;
1275 p_to.over_payment_allocation_code := p_from.over_payment_allocation_code;
1276 p_to.receipt_msmtch_allocation_code := p_from.receipt_msmtch_allocation_code;
1277 p_to.attribute_category := p_from.attribute_category;
1278 p_to.attribute1 := p_from.attribute1;
1279 p_to.attribute2 := p_from.attribute2;
1280 p_to.attribute3 := p_from.attribute3;
1281 p_to.attribute4 := p_from.attribute4;
1282 p_to.attribute5 := p_from.attribute5;
1283 p_to.attribute6 := p_from.attribute6;
1284 p_to.attribute7 := p_from.attribute7;
1285 p_to.attribute8 := p_from.attribute8;
1286 p_to.attribute9 := p_from.attribute9;
1287 p_to.attribute10 := p_from.attribute10;
1288 p_to.attribute11 := p_from.attribute11;
1289 p_to.attribute12 := p_from.attribute12;
1290 p_to.attribute13 := p_from.attribute13;
1291 p_to.attribute14 := p_from.attribute14;
1292 p_to.attribute15 := p_from.attribute15;
1293 p_to.created_by := p_from.created_by;
1294 p_to.creation_date := p_from.creation_date;
1295 p_to.last_updated_by := p_from.last_updated_by;
1296 p_to.last_update_date := p_from.last_update_date;
1297 p_to.last_update_login := p_from.last_update_login;
1298 END migrate;
1299 */
1300 ---------------------------------------------------------------------------
1301 -- PROCEDURE validate_row
1302 ---------------------------------------------------------------------------
1303 --------------------------------------------
1304 -- validate_row for:OKL_CASH_ALLCTN_RLS_V --
1305 --------------------------------------------
1306 PROCEDURE validate_row(
1307 p_api_version IN NUMBER,
1308 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1309 x_return_status OUT NOCOPY VARCHAR2,
1310 x_msg_count OUT NOCOPY NUMBER,
1311 x_msg_data OUT NOCOPY VARCHAR2,
1312 p_catv_rec IN catv_rec_type) IS
1313
1314 l_api_version CONSTANT NUMBER := 1;
1315 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1316 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1317 l_catv_rec catv_rec_type := p_catv_rec;
1318 l_cat_rec cat_rec_type;
1319 BEGIN
1320 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1321 G_PKG_NAME,
1322 p_init_msg_list,
1323 l_api_version,
1324 p_api_version,
1325 '_PVT',
1326 x_return_status);
1327 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1328 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1329 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1330 RAISE Okl_Api.G_EXCEPTION_ERROR;
1331 END IF;
1332 --- Validate all non-missing attributes (Item Level Validation)
1333 l_return_status := Validate_Attributes(l_catv_rec);
1334 --- If any errors happen abort API
1335 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1336 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1337 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1338 RAISE Okl_Api.G_EXCEPTION_ERROR;
1339 END IF;
1340 l_return_status := Validate_Record(l_catv_rec);
1341 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1342 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1343 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1344 RAISE Okl_Api.G_EXCEPTION_ERROR;
1345 END IF;
1346 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1347 EXCEPTION
1348 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1349 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1350 (
1351 l_api_name,
1352 G_PKG_NAME,
1353 'Okl_Api.G_RET_STS_ERROR',
1354 x_msg_count,
1355 x_msg_data,
1356 '_PVT'
1357 );
1358 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1359 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1360 (
1361 l_api_name,
1362 G_PKG_NAME,
1363 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1364 x_msg_count,
1365 x_msg_data,
1366 '_PVT'
1367 );
1368 WHEN OTHERS THEN
1369 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1370 (
1371 l_api_name,
1372 G_PKG_NAME,
1373 'OTHERS',
1374 x_msg_count,
1375 x_msg_data,
1376 '_PVT'
1377 );
1378 END validate_row;
1379 ------------------------------------------
1380 -- PL/SQL TBL validate_row for:CATV_TBL --
1381 ------------------------------------------
1382 PROCEDURE validate_row(
1383 p_api_version IN NUMBER,
1384 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1385 x_return_status OUT NOCOPY VARCHAR2,
1386 x_msg_count OUT NOCOPY NUMBER,
1387 x_msg_data OUT NOCOPY VARCHAR2,
1388 p_catv_tbl IN catv_tbl_type) IS
1389
1390 l_api_version CONSTANT NUMBER := 1;
1391 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1392 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1393 -- Begin Post-Generation Change
1394 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1395 -- End Post-Generation Change
1396 i NUMBER := 0;
1397 BEGIN
1398 Okl_Api.init_msg_list(p_init_msg_list);
1399 -- Make sure PL/SQL table has records in it before passing
1400 IF (p_catv_tbl.COUNT > 0) THEN
1401 i := p_catv_tbl.FIRST;
1402 LOOP
1403 validate_row (
1404 p_api_version => p_api_version,
1405 p_init_msg_list => Okl_Api.G_FALSE,
1406 x_return_status => x_return_status,
1407 x_msg_count => x_msg_count,
1408 x_msg_data => x_msg_data,
1409 p_catv_rec => p_catv_tbl(i));
1410 -- Begin Post-Generation Change
1411 -- store the highest degree of error
1412 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1413 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1414 l_overall_status := x_return_status;
1415 END IF;
1416 END IF;
1417 -- End Post-Generation Change
1418 EXIT WHEN (i = p_catv_tbl.LAST);
1419 i := p_catv_tbl.NEXT(i);
1420 END LOOP;
1421 -- Begin Post-Generation Change
1422 -- return overall status
1423 x_return_status := l_overall_status;
1424 -- End Post-Generation Change
1425 END IF;
1426 EXCEPTION
1427 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1428 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1429 (
1430 l_api_name,
1431 G_PKG_NAME,
1432 'Okl_Api.G_RET_STS_ERROR',
1433 x_msg_count,
1434 x_msg_data,
1435 '_PVT'
1436 );
1437 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1438 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1439 (
1440 l_api_name,
1441 G_PKG_NAME,
1442 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1443 x_msg_count,
1444 x_msg_data,
1445 '_PVT'
1446 );
1447 WHEN OTHERS THEN
1448 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1449 (
1450 l_api_name,
1451 G_PKG_NAME,
1452 'OTHERS',
1453 x_msg_count,
1454 x_msg_data,
1455 '_PVT'
1456 );
1457 END validate_row;
1458
1459 ---------------------------------------------------------------------------
1460 -- PROCEDURE insert_row
1461 ---------------------------------------------------------------------------
1462 ------------------------------------------
1463 -- insert_row for:OKL_CASH_ALLCTN_RLS_H --
1464 ------------------------------------------
1465 /* -- history tables not supported -- 04 APR 2002
1466 PROCEDURE insert_row(
1467 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1468 x_return_status OUT NOCOPY VARCHAR2,
1469 x_msg_count OUT NOCOPY NUMBER,
1470 x_msg_data OUT NOCOPY VARCHAR2,
1471 p_okl_cash_allctn_rls_h_rec IN okl_cash_allctn_rls_h_rec_type,
1472 x_okl_cash_allctn_rls_h_rec OUT NOCOPY okl_cash_allctn_rls_h_rec_type) IS
1473
1474 l_api_version CONSTANT NUMBER := 1;
1475 l_api_name CONSTANT VARCHAR2(30) := 'H_insert_row';
1476 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1477 l_okl_cash_allctn_rls_h_rec okl_cash_allctn_rls_h_rec_type := p_okl_cash_allctn_rls_h_rec;
1478 ldefoklcashallctnrlshrec okl_cash_allctn_rls_h_rec_type;
1479 ----------------------------------------------
1480 -- Set_Attributes for:OKL_CASH_ALLCTN_RLS_H --
1481 ----------------------------------------------
1482 FUNCTION Set_Attributes (
1483 p_okl_cash_allctn_rls_h_rec IN okl_cash_allctn_rls_h_rec_type,
1484 x_okl_cash_allctn_rls_h_rec OUT NOCOPY okl_cash_allctn_rls_h_rec_type
1485 ) RETURN VARCHAR2 IS
1486 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1487 BEGIN
1488 x_okl_cash_allctn_rls_h_rec := p_okl_cash_allctn_rls_h_rec;
1489 RETURN(l_return_status);
1490 END Set_Attributes;
1491 BEGIN
1492 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1493 p_init_msg_list,
1494 '_PVT',
1495 x_return_status);
1496
1497 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1498 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1499 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1500 RAISE Okl_Api.G_EXCEPTION_ERROR;
1501 END IF;
1502 --- Setting item attributes
1503 l_return_status := Set_Attributes(
1504 p_okl_cash_allctn_rls_h_rec, -- IN
1505 l_okl_cash_allctn_rls_h_rec); -- OUT
1506 --- If any errors happen abort API
1507
1508 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1509 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1510 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1511 RAISE Okl_Api.G_EXCEPTION_ERROR;
1512 END IF;
1513
1514 l_okl_cash_allctn_rls_h_rec.ID := get_seq_id;
1515
1516 INSERT INTO OKL_CASH_ALLCTN_RLS_H(
1517 id,
1518 major_version,
1519 name,
1520 object_version_number,
1521 description,
1522 amount_tolerance_percent,
1523 days_past_quote_valid_toleranc,
1524 months_to_bill_ahead,
1525 under_payment_allocation_code,
1526 over_payment_allocation_code,
1527 receipt_msmtch_allocation_code,
1528 attribute_category,
1529 attribute1,
1530 attribute2,
1531 attribute3,
1532 attribute4,
1533 attribute5,
1534 attribute6,
1535 attribute7,
1536 attribute8,
1537 attribute9,
1538 attribute10,
1539 attribute11,
1540 attribute12,
1541 attribute13,
1542 attribute14,
1543 attribute15,
1544 created_by,
1545 creation_date,
1546 last_updated_by,
1547 last_update_date,
1548 last_update_login)
1549 VALUES (
1550 l_okl_cash_allctn_rls_h_rec.id,
1551 -- l_okl_cash_allctn_rls_h_rec.major_version,
1552 1,
1553 l_okl_cash_allctn_rls_h_rec.name,
1554 l_okl_cash_allctn_rls_h_rec.object_version_number,
1555 l_okl_cash_allctn_rls_h_rec.description,
1556 l_okl_cash_allctn_rls_h_rec.amount_tolerance_percent,
1557 l_okl_cash_allctn_rls_h_rec.days_past_quote_valid_toleranc,
1558 l_okl_cash_allctn_rls_h_rec.months_to_bill_ahead,
1559 l_okl_cash_allctn_rls_h_rec.under_payment_allocation_code,
1560 l_okl_cash_allctn_rls_h_rec.over_payment_allocation_code,
1561 l_okl_cash_allctn_rls_h_rec.receipt_msmtch_allocation_code,
1562 l_okl_cash_allctn_rls_h_rec.attribute_category,
1563 l_okl_cash_allctn_rls_h_rec.attribute1,
1564 l_okl_cash_allctn_rls_h_rec.attribute2,
1565 l_okl_cash_allctn_rls_h_rec.attribute3,
1566 l_okl_cash_allctn_rls_h_rec.attribute4,
1567 l_okl_cash_allctn_rls_h_rec.attribute5,
1568 l_okl_cash_allctn_rls_h_rec.attribute6,
1569 l_okl_cash_allctn_rls_h_rec.attribute7,
1570 l_okl_cash_allctn_rls_h_rec.attribute8,
1571 l_okl_cash_allctn_rls_h_rec.attribute9,
1572 l_okl_cash_allctn_rls_h_rec.attribute10,
1573 l_okl_cash_allctn_rls_h_rec.attribute11,
1574 l_okl_cash_allctn_rls_h_rec.attribute12,
1575 l_okl_cash_allctn_rls_h_rec.attribute13,
1576 l_okl_cash_allctn_rls_h_rec.attribute14,
1577 l_okl_cash_allctn_rls_h_rec.attribute15,
1578 l_okl_cash_allctn_rls_h_rec.created_by,
1579 l_okl_cash_allctn_rls_h_rec.creation_date,
1580 l_okl_cash_allctn_rls_h_rec.last_updated_by,
1581 l_okl_cash_allctn_rls_h_rec.last_update_date,
1582 l_okl_cash_allctn_rls_h_rec.last_update_login);
1583 -- Set OUT values
1584 x_okl_cash_allctn_rls_h_rec := l_okl_cash_allctn_rls_h_rec;
1585
1586 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1587
1588 EXCEPTION
1589 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1590 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1591 (
1592 l_api_name,
1593 G_PKG_NAME,
1594 'Okl_Api.G_RET_STS_ERROR',
1595 x_msg_count,
1596 x_msg_data,
1597 '_PVT'
1598 );
1599 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1600 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1601 (
1602 l_api_name,
1603 G_PKG_NAME,
1604 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1605 x_msg_count,
1606 x_msg_data,
1607 '_PVT'
1608 );
1609 WHEN OTHERS THEN
1610 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1611 (
1612 l_api_name,
1613 G_PKG_NAME,
1614 'OTHERS',
1615 x_msg_count,
1616 x_msg_data,
1617 '_PVT'
1618 );
1619 END insert_row;
1620 */
1621
1622 ----------------------------------------
1623 -- insert_row for:OKL_CASH_ALLCTN_RLS --
1624 ----------------------------------------
1625 ---------------------------------------------------------------------------
1626 -- Start of comments
1627 --
1628 -- Procedure Name : insert_row
1629 -- Description : Inserts the row in the table OKL_CASH_ALLCTN_RLS.
1630 -- Business Rules :
1631 -- Parameters : p_init_msg_list, x_return_status, x_msg_count, x_msg_data,
1632 -- p_cat_rec, x_cat_rec
1633 -- Version : 1.0
1634 -- History : 25-AUG-04 abindal modified to include NUM_OF_DAYS_TO_HOLD_ADV_PAY column.
1635 -- : 12-oct-04 sjalasut changed the column NUM_OF_DAYS_TO_HOLD_ADV_PAY
1636 -- to NUM_DAYS_HOLD_ADV_PAY per user defined streams build
1637 -- End of comments
1638 ---------------------------------------------------------------------------
1639
1640 PROCEDURE insert_row(
1641 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1642 x_return_status OUT NOCOPY VARCHAR2,
1643 x_msg_count OUT NOCOPY NUMBER,
1644 x_msg_data OUT NOCOPY VARCHAR2,
1645 p_cat_rec IN cat_rec_type,
1646 x_cat_rec OUT NOCOPY cat_rec_type) IS
1647
1648 l_api_version CONSTANT NUMBER := 1;
1649 l_api_name CONSTANT VARCHAR2(30) := 'RLS_insert_row';
1650 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1651 l_cat_rec cat_rec_type := p_cat_rec;
1652 l_def_cat_rec cat_rec_type;
1653 --------------------------------------------
1654 -- Set_Attributes for:OKL_CASH_ALLCTN_RLS --
1655 --------------------------------------------
1656
1657 FUNCTION Set_Attributes (
1658 p_cat_rec IN cat_rec_type,
1659 x_cat_rec OUT NOCOPY cat_rec_type
1660 ) RETURN VARCHAR2 IS
1661 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1662 BEGIN
1663 x_cat_rec := p_cat_rec;
1664 RETURN(l_return_status);
1665 END Set_Attributes;
1666 BEGIN
1667 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1668 p_init_msg_list,
1669 '_PVT',
1670 x_return_status);
1671 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1672 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1673 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1674 RAISE Okl_Api.G_EXCEPTION_ERROR;
1675 END IF;
1676 --- Setting item attributes
1677 l_return_status := Set_Attributes(
1678 p_cat_rec, -- IN
1679 l_cat_rec); -- OUT
1680 --- If any errors happen abort API
1681 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1682 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1683 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1684 RAISE Okl_Api.G_EXCEPTION_ERROR;
1685 END IF;
1686 INSERT INTO OKL_CASH_ALLCTN_RLS(
1687 id,
1688 name,
1689 object_version_number,
1690 description,
1691 start_date,
1692 end_date,
1693 amount_tolerance_percent,
1694 days_past_quote_valid_toleranc,
1695 months_to_bill_ahead,
1696 under_payment_allocation_code,
1697 over_payment_allocation_code,
1698 receipt_msmtch_allocation_code,
1699 default_rule,
1700 attribute_category,
1701 attribute1,
1702 attribute2,
1703 attribute3,
1704 attribute4,
1705 attribute5,
1706 attribute6,
1707 attribute7,
1708 attribute8,
1709 attribute9,
1710 attribute10,
1711 attribute11,
1712 attribute12,
1713 attribute13,
1714 attribute14,
1715 attribute15,
1716 org_id,
1717 created_by,
1718 creation_date,
1719 last_updated_by,
1720 last_update_date,
1721 last_update_login,
1722 CAU_ID,
1723 -- new column to hold number of days to reserve advanced payment for contract.
1724 num_days_hold_adv_pay)
1725 VALUES (
1726 l_cat_rec.id,
1727 l_cat_rec.name,
1728 l_cat_rec.object_version_number,
1729 l_cat_rec.description,
1730 l_cat_rec.start_date,
1731 l_cat_rec.end_date,
1732 l_cat_rec.amount_tolerance_percent,
1733 l_cat_rec.days_past_quote_valid_toleranc,
1734 l_cat_rec.months_to_bill_ahead,
1735 l_cat_rec.under_payment_allocation_code,
1736 l_cat_rec.over_payment_allocation_code,
1737 l_cat_rec.receipt_msmtch_allocation_code,
1738 l_cat_rec.default_rule,
1739 l_cat_rec.attribute_category,
1740 l_cat_rec.attribute1,
1741 l_cat_rec.attribute2,
1742 l_cat_rec.attribute3,
1743 l_cat_rec.attribute4,
1744 l_cat_rec.attribute5,
1745 l_cat_rec.attribute6,
1746 l_cat_rec.attribute7,
1747 l_cat_rec.attribute8,
1748 l_cat_rec.attribute9,
1749 l_cat_rec.attribute10,
1750 l_cat_rec.attribute11,
1751 l_cat_rec.attribute12,
1752 l_cat_rec.attribute13,
1753 l_cat_rec.attribute14,
1754 l_cat_rec.attribute15,
1755 l_cat_rec.org_id,
1756 l_cat_rec.created_by,
1757 l_cat_rec.creation_date,
1758 l_cat_rec.last_updated_by,
1759 l_cat_rec.last_update_date,
1760 l_cat_rec.last_update_login,
1761 l_cat_rec.CAU_ID,
1762 -- new column to hold number of days to reserve advanced payment for contract.
1763 l_cat_rec.num_days_hold_adv_pay);
1764 -- Set OUT values
1765 x_cat_rec := l_cat_rec;
1766 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1767 EXCEPTION
1768 WHEN okl_api.G_EXCEPTION_ERROR THEN
1769 x_return_status := 'E';
1770 NULL;
1771 /*
1772 x_return_status := okl_api.HANDLE_EXCEPTIONS
1773 (
1774 l_api_name,
1775 G_PKG_NAME,
1776 'okl_api.G_RET_STS_ERROR',
1777 x_msg_count,
1778 x_msg_data,
1779 '_PVT'
1780 );
1781 */
1782 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1783 x_return_status := 'U';
1784 NULL;
1785 /*
1786 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1787 (
1788 l_api_name,
1789 G_PKG_NAME,
1790 'okl_api.G_RET_STS_UNEXP_ERROR',
1791 x_msg_count,
1792 x_msg_data,
1793 '_PVT'
1794 );
1795 */
1796 WHEN OTHERS THEN
1797 x_return_status := 'U';
1798 NULL;
1799 /*
1800 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1801 (
1802 l_api_name,
1803 G_PKG_NAME,
1804 'OTHERS',
1805 x_msg_count,
1806 x_msg_data,
1807 '_PVT'
1808 );
1809 */
1810 END insert_row;
1811 ------------------------------------------
1812 -- insert_row for:OKL_CASH_ALLCTN_RLS_V --
1813 ------------------------------------------
1814 PROCEDURE insert_row(
1815 p_api_version IN NUMBER,
1816 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1817 x_return_status OUT NOCOPY VARCHAR2,
1818 x_msg_count OUT NOCOPY NUMBER,
1819 x_msg_data OUT NOCOPY VARCHAR2,
1820 p_catv_rec IN catv_rec_type,
1821 x_catv_rec OUT NOCOPY catv_rec_type) IS
1822
1823 l_api_version CONSTANT NUMBER := 1;
1824 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1825 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1826 l_catv_rec catv_rec_type;
1827 l_def_catv_rec catv_rec_type;
1828 l_cat_rec cat_rec_type;
1829 lx_cat_rec cat_rec_type;
1830 -------------------------------
1831 -- FUNCTION fill_who_columns --
1832 -------------------------------
1833 FUNCTION fill_who_columns (
1834 p_catv_rec IN catv_rec_type
1835 ) RETURN catv_rec_type IS
1836 l_catv_rec catv_rec_type := p_catv_rec;
1837 BEGIN
1838 l_catv_rec.CREATION_DATE := SYSDATE;
1839 l_catv_rec.CREATED_BY := Fnd_Global.USER_ID;
1840 l_catv_rec.LAST_UPDATE_DATE := l_catv_rec.CREATION_DATE;
1841 l_catv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
1842 l_catv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1843 RETURN(l_catv_rec);
1844 END fill_who_columns;
1845 ----------------------------------------------
1846 -- Set_Attributes for:OKL_CASH_ALLCTN_RLS_V --
1847 ----------------------------------------------
1848 FUNCTION Set_Attributes (
1849 p_catv_rec IN catv_rec_type,
1850 x_catv_rec OUT NOCOPY catv_rec_type
1851 ) RETURN VARCHAR2 IS
1852 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1853 BEGIN
1854 x_catv_rec := p_catv_rec;
1855 x_catv_rec.OBJECT_VERSION_NUMBER := 1;
1856 RETURN(l_return_status);
1857 END Set_Attributes;
1858 BEGIN
1859 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
1860 G_PKG_NAME,
1861 p_init_msg_list,
1862 l_api_version,
1863 p_api_version,
1864 '_PVT',
1865 x_return_status);
1866 /*
1867 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1868 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1869 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1870 RAISE Okl_Api.G_EXCEPTION_ERROR;
1871 END IF;
1872 */
1873 l_catv_rec := null_out_defaults(p_catv_rec);
1874 -- Set primary key value
1875 l_catv_rec.ID := get_seq_id;
1876 --- Setting item attributes
1877 l_return_status := Set_Attributes(
1878 l_catv_rec, -- IN
1879 l_def_catv_rec); -- OUT
1880 --- If any errors happen abort API
1881 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1882 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1883 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1884 RAISE Okl_Api.G_EXCEPTION_ERROR;
1885 END IF;
1886 l_def_catv_rec := fill_who_columns(l_def_catv_rec);
1887 --- Validate all non-missing attributes (Item Level Validation)
1888 l_return_status := Validate_Attributes(l_def_catv_rec);
1889 --- If any errors happen abort API
1890 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1891 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1892 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1893 RAISE Okl_Api.G_EXCEPTION_ERROR;
1894 END IF;
1895 l_return_status := Validate_Record(l_def_catv_rec);
1896 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1897 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1898 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1899 RAISE Okl_Api.G_EXCEPTION_ERROR;
1900 END IF;
1901 --------------------------------------
1902 -- Move VIEW record to "Child" records
1903 --------------------------------------
1904 migrate(l_def_catv_rec, l_cat_rec);
1905 --------------------------------------------
1906 -- Call the INSERT_ROW for each child record
1907 --------------------------------------------
1908 insert_row(
1909 p_init_msg_list,
1910 x_return_status,
1911 x_msg_count,
1912 x_msg_data,
1913 l_cat_rec,
1914 lx_cat_rec
1915 );
1916 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1917 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1918 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1919 RAISE Okl_Api.G_EXCEPTION_ERROR;
1920 END IF;
1921 migrate(lx_cat_rec, l_def_catv_rec);
1922 -- Set OUT values
1923 x_catv_rec := l_def_catv_rec;
1924 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1925 EXCEPTION
1926 WHEN okl_api.G_EXCEPTION_ERROR THEN
1927 x_return_status := 'E';
1928 /*
1929 x_return_status := okl_api.HANDLE_EXCEPTIONS
1930 (
1931 l_api_name,
1932 G_PKG_NAME,
1933 'okl_api.G_RET_STS_ERROR',
1934 x_msg_count,
1935 x_msg_data,
1936 '_PVT'
1937 );
1938 */
1939 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1940 x_return_status := 'U';
1941 /*
1942 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1943 (
1944 l_api_name,
1945 G_PKG_NAME,
1946 'okl_api.G_RET_STS_UNEXP_ERROR',
1947 x_msg_count,
1948 x_msg_data,
1949 '_PVT'
1950 );
1951 */
1952 WHEN OTHERS THEN
1953 x_return_status := 'U';
1954 /*
1955 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1956 (
1957 l_api_name,
1958 G_PKG_NAME,
1959 'OTHERS',
1960 x_msg_count,
1961 x_msg_data,
1962 '_PVT'
1963 );
1964 */
1965 END insert_row;
1966 ----------------------------------------
1967 -- PL/SQL TBL insert_row for:CATV_TBL --
1968 ----------------------------------------
1969 PROCEDURE insert_row(
1970 p_api_version IN NUMBER,
1971 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1972 x_return_status OUT NOCOPY VARCHAR2,
1973 x_msg_count OUT NOCOPY NUMBER,
1974 x_msg_data OUT NOCOPY VARCHAR2,
1975 p_catv_tbl IN catv_tbl_type,
1976 x_catv_tbl OUT NOCOPY catv_tbl_type) IS
1977
1978 l_api_version CONSTANT NUMBER := 1;
1979 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1980 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1981 -- Begin Post-Generation Change
1982 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1983 -- End Post-Generation Change
1984 i NUMBER := 0;
1985 BEGIN
1986 Okl_Api.init_msg_list(p_init_msg_list);
1987 -- Make sure PL/SQL table has records in it before passing
1988 IF (p_catv_tbl.COUNT > 0) THEN
1989 i := p_catv_tbl.FIRST;
1990 LOOP
1991 insert_row (
1992 p_api_version => p_api_version,
1993 p_init_msg_list => Okl_Api.G_FALSE,
1994 x_return_status => x_return_status,
1995 x_msg_count => x_msg_count,
1996 x_msg_data => x_msg_data,
1997 p_catv_rec => p_catv_tbl(i),
1998 x_catv_rec => x_catv_tbl(i));
1999 -- Begin Post-Generation Change
2000 -- store the highest degree of error
2001 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2002 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2003 l_overall_status := x_return_status;
2004 END IF;
2005 END IF;
2006 -- End Post-Generation Change
2007 EXIT WHEN (i = p_catv_tbl.LAST);
2008 i := p_catv_tbl.NEXT(i);
2009 END LOOP;
2010 -- Begin Post-Generation Change
2011 -- return overall status
2012 x_return_status := l_overall_status;
2013 -- End Post-Generation Change
2014 END IF;
2015 EXCEPTION
2016 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2017 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2018 (
2019 l_api_name,
2020 G_PKG_NAME,
2021 'Okl_Api.G_RET_STS_ERROR',
2022 x_msg_count,
2023 x_msg_data,
2024 '_PVT'
2025 );
2026 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2027 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2028 (
2029 l_api_name,
2030 G_PKG_NAME,
2031 'Okl_Api.G_RET_STS_UNEXP_ERROR',
2032 x_msg_count,
2033 x_msg_data,
2034 '_PVT'
2035 );
2036 WHEN OTHERS THEN
2037 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2038 (
2039 l_api_name,
2040 G_PKG_NAME,
2041 'OTHERS',
2042 x_msg_count,
2043 x_msg_data,
2044 '_PVT'
2045 );
2046 END insert_row;
2047
2048 ---------------------------------------------------------------------------
2049 -- PROCEDURE lock_row
2050 ---------------------------------------------------------------------------
2051 --------------------------------------
2052 -- lock_row for:OKL_CASH_ALLCTN_RLS --
2053 --------------------------------------
2054 PROCEDURE lock_row(
2055 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2056 x_return_status OUT NOCOPY VARCHAR2,
2057 x_msg_count OUT NOCOPY NUMBER,
2058 x_msg_data OUT NOCOPY VARCHAR2,
2059 p_cat_rec IN cat_rec_type) IS
2060
2061 E_Resource_Busy EXCEPTION;
2062 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2063 CURSOR lock_csr (p_cat_rec IN cat_rec_type) IS
2064 SELECT OBJECT_VERSION_NUMBER
2065 FROM OKL_CASH_ALLCTN_RLS
2066 WHERE ID = p_cat_rec.id
2067 AND OBJECT_VERSION_NUMBER = p_cat_rec.object_version_number
2068 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2069
2070 CURSOR lchk_csr (p_cat_rec IN cat_rec_type) IS
2071 SELECT OBJECT_VERSION_NUMBER
2072 FROM OKL_CASH_ALLCTN_RLS
2073 WHERE ID = p_cat_rec.id;
2074 l_api_version CONSTANT NUMBER := 1;
2075 l_api_name CONSTANT VARCHAR2(30) := 'RLS_lock_row';
2076 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2077 l_object_version_number OKL_CASH_ALLCTN_RLS.OBJECT_VERSION_NUMBER%TYPE;
2078 lc_object_version_number OKL_CASH_ALLCTN_RLS.OBJECT_VERSION_NUMBER%TYPE;
2079 l_row_notfound BOOLEAN := FALSE;
2080 lc_row_notfound BOOLEAN := FALSE;
2081 BEGIN
2082 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
2083 p_init_msg_list,
2084 '_PVT',
2085 x_return_status);
2086 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2087 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2088 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2089 RAISE Okl_Api.G_EXCEPTION_ERROR;
2090 END IF;
2091 BEGIN
2092 OPEN lock_csr(p_cat_rec);
2093 FETCH lock_csr INTO l_object_version_number;
2094 l_row_notfound := lock_csr%NOTFOUND;
2095 CLOSE lock_csr;
2096 EXCEPTION
2097 WHEN E_Resource_Busy THEN
2098 IF (lock_csr%ISOPEN) THEN
2099 CLOSE lock_csr;
2100 END IF;
2101 Okl_Api.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2102 RAISE App_Exceptions.RECORD_LOCK_EXCEPTION;
2103 END;
2104
2105 IF ( l_row_notfound ) THEN
2106 OPEN lchk_csr(p_cat_rec);
2107 FETCH lchk_csr INTO lc_object_version_number;
2108 lc_row_notfound := lchk_csr%NOTFOUND;
2109 CLOSE lchk_csr;
2110 END IF;
2111 IF (lc_row_notfound) THEN
2112 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2113 RAISE Okl_Api.G_EXCEPTION_ERROR;
2114 ELSIF lc_object_version_number > p_cat_rec.object_version_number THEN
2115 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2116 RAISE Okl_Api.G_EXCEPTION_ERROR;
2117 ELSIF lc_object_version_number <> p_cat_rec.object_version_number THEN
2118 Okl_Api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2119 RAISE Okl_Api.G_EXCEPTION_ERROR;
2120 ELSIF lc_object_version_number = -1 THEN
2121 Okl_Api.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2122 RAISE Okl_Api.G_EXCEPTION_ERROR;
2123 END IF;
2124 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2125 EXCEPTION
2126 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2127 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2128 (
2129 l_api_name,
2130 G_PKG_NAME,
2131 'Okl_Api.G_RET_STS_ERROR',
2132 x_msg_count,
2133 x_msg_data,
2134 '_PVT'
2135 );
2136 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2137 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2138 (
2139 l_api_name,
2140 G_PKG_NAME,
2141 'Okl_Api.G_RET_STS_UNEXP_ERROR',
2142 x_msg_count,
2143 x_msg_data,
2144 '_PVT'
2145 );
2146 WHEN OTHERS THEN
2147 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2148 (
2149 l_api_name,
2150 G_PKG_NAME,
2151 'OTHERS',
2152 x_msg_count,
2153 x_msg_data,
2154 '_PVT'
2155 );
2156 END lock_row;
2157 ----------------------------------------
2158 -- lock_row for:OKL_CASH_ALLCTN_RLS_V --
2159 ----------------------------------------
2160 PROCEDURE lock_row(
2161 p_api_version IN NUMBER,
2162 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2163 x_return_status OUT NOCOPY VARCHAR2,
2164 x_msg_count OUT NOCOPY NUMBER,
2165 x_msg_data OUT NOCOPY VARCHAR2,
2166 p_catv_rec IN catv_rec_type) IS
2167
2168 l_api_version CONSTANT NUMBER := 1;
2169 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2170 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2171 l_cat_rec cat_rec_type;
2172 BEGIN
2173 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
2174 G_PKG_NAME,
2175 p_init_msg_list,
2176 l_api_version,
2177 p_api_version,
2178 '_PVT',
2179 x_return_status);
2180 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2181 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2182 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2183 RAISE Okl_Api.G_EXCEPTION_ERROR;
2184 END IF;
2185 --------------------------------------
2186 -- Move VIEW record to "Child" records
2187 --------------------------------------
2188 migrate(p_catv_rec, l_cat_rec);
2189 --------------------------------------------
2190 -- Call the LOCK_ROW for each child record
2191 --------------------------------------------
2192 lock_row(
2193 p_init_msg_list,
2194 x_return_status,
2195 x_msg_count,
2196 x_msg_data,
2197 l_cat_rec
2198 );
2199 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2200 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2201 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2202 RAISE Okl_Api.G_EXCEPTION_ERROR;
2203 END IF;
2204 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2205 EXCEPTION
2206 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2207 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2208 (
2209 l_api_name,
2210 G_PKG_NAME,
2211 'Okl_Api.G_RET_STS_ERROR',
2212 x_msg_count,
2213 x_msg_data,
2214 '_PVT'
2215 );
2216 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2217 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2218 (
2219 l_api_name,
2220 G_PKG_NAME,
2221 'Okl_Api.G_RET_STS_UNEXP_ERROR',
2222 x_msg_count,
2223 x_msg_data,
2224 '_PVT'
2225 );
2226 WHEN OTHERS THEN
2227 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2228 (
2229 l_api_name,
2230 G_PKG_NAME,
2231 'OTHERS',
2232 x_msg_count,
2233 x_msg_data,
2234 '_PVT'
2235 );
2236 END lock_row;
2237 --------------------------------------
2238 -- PL/SQL TBL lock_row for:CATV_TBL --
2239 --------------------------------------
2240 PROCEDURE lock_row(
2241 p_api_version IN NUMBER,
2242 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2243 x_return_status OUT NOCOPY VARCHAR2,
2244 x_msg_count OUT NOCOPY NUMBER,
2245 x_msg_data OUT NOCOPY VARCHAR2,
2246 p_catv_tbl IN catv_tbl_type) IS
2247
2248 l_api_version CONSTANT NUMBER := 1;
2249 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2250 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2251 -- Begin Post-Generation Change
2252 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2253 -- End Post-Generation Change
2254 i NUMBER := 0;
2255 BEGIN
2256 Okl_Api.init_msg_list(p_init_msg_list);
2257 -- Make sure PL/SQL table has records in it before passing
2258 IF (p_catv_tbl.COUNT > 0) THEN
2259 i := p_catv_tbl.FIRST;
2260 LOOP
2261 lock_row (
2262 p_api_version => p_api_version,
2263 p_init_msg_list => Okl_Api.G_FALSE,
2264 x_return_status => x_return_status,
2265 x_msg_count => x_msg_count,
2266 x_msg_data => x_msg_data,
2267 p_catv_rec => p_catv_tbl(i));
2268 -- Begin Post-Generation Change
2269 -- store the highest degree of error
2270 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2271 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2272 l_overall_status := x_return_status;
2273 END IF;
2274 END IF;
2275 -- End Post-Generation Change
2276 EXIT WHEN (i = p_catv_tbl.LAST);
2277 i := p_catv_tbl.NEXT(i);
2278 END LOOP;
2279 -- Begin Post-Generation Change
2280 -- return overall status
2281 x_return_status := l_overall_status;
2282 -- End Post-Generation Change
2283 END IF;
2284 EXCEPTION
2285 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2286 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2287 (
2288 l_api_name,
2289 G_PKG_NAME,
2290 'Okl_Api.G_RET_STS_ERROR',
2291 x_msg_count,
2292 x_msg_data,
2293 '_PVT'
2294 );
2295 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2296 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2297 (
2298 l_api_name,
2299 G_PKG_NAME,
2300 'Okl_Api.G_RET_STS_UNEXP_ERROR',
2301 x_msg_count,
2302 x_msg_data,
2303 '_PVT'
2304 );
2305 WHEN OTHERS THEN
2306 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2307 (
2308 l_api_name,
2309 G_PKG_NAME,
2310 'OTHERS',
2311 x_msg_count,
2312 x_msg_data,
2313 '_PVT'
2314 );
2315 END lock_row;
2316
2317 ---------------------------------------------------------------------------
2318 -- PROCEDURE update_row
2319 ---------------------------------------------------------------------------
2320 ----------------------------------------
2321 -- update_row for:OKL_CASH_ALLCTN_RLS --
2322 ----------------------------------------
2323 ---------------------------------------------------------------------------
2324 -- Start of comments
2325 --
2326 -- Procedure Name : update_row
2327 -- Description : Update the existing row in the table OKL_CASH_ALLCTN_RLS
2328 -- Business Rules :
2329 -- Parameters : p_init_msg_list, x_return_status, x_msg_count, x_msg_data,
2330 -- p_cat_rec, x_cat_rec
2331 -- Version : 1.0
2332 -- History : 25-AUG-04 abindal modified to include NUM_OF_DAYS_TO_HOLD_ADV_PAY column.
2333 -- : 12-oct-04 sjalasut changed the column NUM_OF_DAYS_TO_HOLD_ADV_PAY
2334 -- to NUM_DAYS_HOLD_ADV_PAY per user defined streams build
2335 -- End of comments
2336 ---------------------------------------------------------------------------
2337
2338 PROCEDURE update_row(
2339 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2340 x_return_status OUT NOCOPY VARCHAR2,
2341 x_msg_count OUT NOCOPY NUMBER,
2342 x_msg_data OUT NOCOPY VARCHAR2,
2343 p_cat_rec IN cat_rec_type,
2344 x_cat_rec OUT NOCOPY cat_rec_type) IS
2345
2346 l_api_version CONSTANT NUMBER := 1;
2347 l_api_name CONSTANT VARCHAR2(30) := 'RLS_update_row';
2348 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2349 l_cat_rec cat_rec_type := p_cat_rec;
2350 l_def_cat_rec cat_rec_type;
2351 l_row_notfound BOOLEAN := TRUE;
2352
2353 -- history tables not supported -- 04 APR 2002
2354 -- l_okl_cash_allctn_rls_h_rec okl_cash_allctn_rls_h_rec_type;
2355 -- lx_okl_cash_allctn_rls_h_rec okl_cash_allctn_rls_h_rec_type;
2356 ----------------------------------
2357 -- FUNCTION populate_new_record --
2358 ----------------------------------
2359
2360 FUNCTION populate_new_record (
2361 p_cat_rec IN cat_rec_type,
2362 x_cat_rec OUT NOCOPY cat_rec_type
2363 ) RETURN VARCHAR2 IS
2364 l_cat_rec cat_rec_type;
2365 l_row_notfound BOOLEAN := TRUE;
2366 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2367 BEGIN
2368 x_cat_rec := p_cat_rec;
2369 -- Get current database values
2370 l_cat_rec := get_rec(p_cat_rec, l_row_notfound);
2371 IF (l_row_notfound) THEN
2372 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2373 END IF;
2374 -- Move the "old" record to the history record:
2375 -- (1) to get the "old" version
2376 -- (2) to avoid 2 hits to the database
2377
2378 -- history tables not supported -- 04 APR 2002
2379 -- migrate(l_cat_rec, l_okl_cash_allctn_rls_h_rec);
2380
2381 IF (x_cat_rec.id = Okl_Api.G_MISS_NUM)
2382 THEN
2383 x_cat_rec.id := l_cat_rec.id;
2384 END IF;
2385 IF (x_cat_rec.name = Okl_Api.G_MISS_CHAR)
2386 THEN
2387 x_cat_rec.name := l_cat_rec.name;
2388 END IF;
2389 IF (x_cat_rec.object_version_number = Okl_Api.G_MISS_NUM)
2390 THEN
2391 x_cat_rec.object_version_number := l_cat_rec.object_version_number;
2392 END IF;
2393 IF (x_cat_rec.description = Okl_Api.G_MISS_CHAR)
2394 THEN
2395 x_cat_rec.description := l_cat_rec.description;
2396 END IF;
2397
2398 IF (x_cat_rec.start_date = Okl_Api.G_MISS_DATE)
2399 THEN
2400 x_cat_rec.start_date := l_cat_rec.start_date;
2401 END IF;
2402
2403 IF (x_cat_rec.end_date = Okl_Api.G_MISS_DATE)
2404 THEN
2405 x_cat_rec.end_date := l_cat_rec.end_date;
2406 END IF;
2407
2408 IF (x_cat_rec.amount_tolerance_percent = Okl_Api.G_MISS_NUM)
2409 THEN
2410 x_cat_rec.amount_tolerance_percent := l_cat_rec.amount_tolerance_percent;
2411 END IF;
2412 IF (x_cat_rec.days_past_quote_valid_toleranc = Okl_Api.G_MISS_NUM)
2413 THEN
2414 x_cat_rec.days_past_quote_valid_toleranc := l_cat_rec.days_past_quote_valid_toleranc;
2415 END IF;
2416 IF (x_cat_rec.months_to_bill_ahead = Okl_Api.G_MISS_NUM)
2417 THEN
2418 x_cat_rec.months_to_bill_ahead := l_cat_rec.months_to_bill_ahead;
2419 END IF;
2420 IF (x_cat_rec.under_payment_allocation_code = Okl_Api.G_MISS_CHAR)
2421 THEN
2422 x_cat_rec.under_payment_allocation_code := l_cat_rec.under_payment_allocation_code;
2423 END IF;
2424 IF (x_cat_rec.over_payment_allocation_code = Okl_Api.G_MISS_CHAR)
2425 THEN
2426 x_cat_rec.over_payment_allocation_code := l_cat_rec.over_payment_allocation_code;
2427 END IF;
2428 IF (x_cat_rec.receipt_msmtch_allocation_code = Okl_Api.G_MISS_CHAR)
2429 THEN
2430 x_cat_rec.receipt_msmtch_allocation_code := l_cat_rec.receipt_msmtch_allocation_code;
2431 END IF;
2432 IF (x_cat_rec.default_rule = Okl_Api.G_MISS_CHAR)
2433 THEN
2434 x_cat_rec.default_rule := l_cat_rec.default_rule;
2435 END IF;
2436 IF (x_cat_rec.attribute_category = Okl_Api.G_MISS_CHAR)
2437 THEN
2438 x_cat_rec.attribute_category := l_cat_rec.attribute_category;
2439 END IF;
2440 IF (x_cat_rec.attribute1 = Okl_Api.G_MISS_CHAR)
2441 THEN
2442 x_cat_rec.attribute1 := l_cat_rec.attribute1;
2443 END IF;
2444 IF (x_cat_rec.attribute2 = Okl_Api.G_MISS_CHAR)
2445 THEN
2446 x_cat_rec.attribute2 := l_cat_rec.attribute2;
2447 END IF;
2448 IF (x_cat_rec.attribute3 = Okl_Api.G_MISS_CHAR)
2449 THEN
2450 x_cat_rec.attribute3 := l_cat_rec.attribute3;
2451 END IF;
2452 IF (x_cat_rec.attribute4 = Okl_Api.G_MISS_CHAR)
2453 THEN
2454 x_cat_rec.attribute4 := l_cat_rec.attribute4;
2455 END IF;
2456 IF (x_cat_rec.attribute5 = Okl_Api.G_MISS_CHAR)
2457 THEN
2458 x_cat_rec.attribute5 := l_cat_rec.attribute5;
2459 END IF;
2460 IF (x_cat_rec.attribute6 = Okl_Api.G_MISS_CHAR)
2461 THEN
2462 x_cat_rec.attribute6 := l_cat_rec.attribute6;
2463 END IF;
2464 IF (x_cat_rec.attribute7 = Okl_Api.G_MISS_CHAR)
2465 THEN
2466 x_cat_rec.attribute7 := l_cat_rec.attribute7;
2467 END IF;
2468 IF (x_cat_rec.attribute8 = Okl_Api.G_MISS_CHAR)
2469 THEN
2470 x_cat_rec.attribute8 := l_cat_rec.attribute8;
2471 END IF;
2472 IF (x_cat_rec.attribute9 = Okl_Api.G_MISS_CHAR)
2473 THEN
2474 x_cat_rec.attribute9 := l_cat_rec.attribute9;
2475 END IF;
2476 IF (x_cat_rec.attribute10 = Okl_Api.G_MISS_CHAR)
2477 THEN
2478 x_cat_rec.attribute10 := l_cat_rec.attribute10;
2479 END IF;
2480 IF (x_cat_rec.attribute11 = Okl_Api.G_MISS_CHAR)
2481 THEN
2482 x_cat_rec.attribute11 := l_cat_rec.attribute11;
2483 END IF;
2484 IF (x_cat_rec.attribute12 = Okl_Api.G_MISS_CHAR)
2485 THEN
2486 x_cat_rec.attribute12 := l_cat_rec.attribute12;
2487 END IF;
2488 IF (x_cat_rec.attribute13 = Okl_Api.G_MISS_CHAR)
2489 THEN
2490 x_cat_rec.attribute13 := l_cat_rec.attribute13;
2491 END IF;
2492 IF (x_cat_rec.attribute14 = Okl_Api.G_MISS_CHAR)
2493 THEN
2494 x_cat_rec.attribute14 := l_cat_rec.attribute14;
2495 END IF;
2496 IF (x_cat_rec.attribute15 = Okl_Api.G_MISS_CHAR)
2497 THEN
2498 x_cat_rec.attribute15 := l_cat_rec.attribute15;
2499 END IF;
2500 IF (x_cat_rec.org_id = Okl_Api.G_MISS_NUM)
2501 THEN
2502 x_cat_rec.org_id := l_cat_rec.org_id;
2503 END IF;
2504 IF (x_cat_rec.created_by = Okl_Api.G_MISS_NUM)
2505 THEN
2506 x_cat_rec.created_by := l_cat_rec.created_by;
2507 END IF;
2508 IF (x_cat_rec.creation_date = Okl_Api.G_MISS_DATE)
2509 THEN
2510 x_cat_rec.creation_date := l_cat_rec.creation_date;
2511 END IF;
2512 IF (x_cat_rec.last_updated_by = Okl_Api.G_MISS_NUM)
2513 THEN
2514 x_cat_rec.last_updated_by := l_cat_rec.last_updated_by;
2515 END IF;
2516 IF (x_cat_rec.last_update_date = Okl_Api.G_MISS_DATE)
2517 THEN
2518 x_cat_rec.last_update_date := l_cat_rec.last_update_date;
2519 END IF;
2520 IF (x_cat_rec.last_update_login = Okl_Api.G_MISS_NUM)
2521 THEN
2522 x_cat_rec.last_update_login := l_cat_rec.last_update_login;
2523 END IF;
2524
2525 IF (x_cat_rec.CAU_ID = Okl_Api.G_MISS_NUM)
2526 THEN
2527 x_cat_rec.CAU_ID := l_cat_rec.CAU_ID;
2528 END IF;
2529 -- new column to hold number of days to reserve advanced payment for contract.
2530 IF (x_cat_rec.num_days_hold_adv_pay = Okl_Api.G_MISS_NUM)
2531 THEN
2532 x_cat_rec.num_days_hold_adv_pay := l_cat_rec.num_days_hold_adv_pay;
2533 END IF;
2534
2535 RETURN(l_return_status);
2536 END populate_new_record;
2537 --------------------------------------------
2538 -- Set_Attributes for:OKL_CASH_ALLCTN_RLS --
2539 --------------------------------------------
2540
2541 FUNCTION Set_Attributes (
2542 p_cat_rec IN cat_rec_type,
2543 x_cat_rec OUT NOCOPY cat_rec_type
2544 ) RETURN VARCHAR2 IS
2545 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2546 BEGIN
2547 x_cat_rec := p_cat_rec;
2548 RETURN(l_return_status);
2549 END Set_Attributes;
2550 BEGIN
2551 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
2552 p_init_msg_list,
2553 '_PVT',
2554 x_return_status);
2555
2556 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2557 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2558 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2559 RAISE Okl_Api.G_EXCEPTION_ERROR;
2560 END IF;
2561 --- Setting item attributes
2562 l_return_status := Set_Attributes(
2563 p_cat_rec, -- IN
2564 l_cat_rec); -- OUT
2565
2566 --- If any errors happen abort API
2567 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2568 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2569 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2570 RAISE Okl_Api.G_EXCEPTION_ERROR;
2571 END IF;
2572 l_return_status := populate_new_record(l_cat_rec, l_def_cat_rec);
2573
2574 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2575 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2576 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2577 RAISE Okl_Api.G_EXCEPTION_ERROR;
2578 END IF;
2579
2580 UPDATE OKL_CASH_ALLCTN_RLS
2581 SET NAME = l_def_cat_rec.name,
2582 OBJECT_VERSION_NUMBER = l_def_cat_rec.object_version_number,
2583 DESCRIPTION = l_def_cat_rec.description,
2584 start_date = l_def_cat_rec.start_date,
2585 end_date = l_def_cat_rec.end_date,
2586 AMOUNT_TOLERANCE_PERCENT = l_def_cat_rec.amount_tolerance_percent,
2587 DAYS_PAST_QUOTE_VALID_TOLERANC = l_def_cat_rec.days_past_quote_valid_toleranc,
2588 MONTHS_TO_BILL_AHEAD = l_def_cat_rec.months_to_bill_ahead,
2589 UNDER_PAYMENT_ALLOCATION_CODE = l_def_cat_rec.under_payment_allocation_code,
2590 OVER_PAYMENT_ALLOCATION_CODE = l_def_cat_rec.over_payment_allocation_code,
2591 RECEIPT_MSMTCH_ALLOCATION_CODE = l_def_cat_rec.receipt_msmtch_allocation_code,
2592 DEFAULT_RULE = l_def_cat_rec.default_rule,
2593 ATTRIBUTE_CATEGORY = l_def_cat_rec.attribute_category,
2594 ATTRIBUTE1 = l_def_cat_rec.attribute1,
2595 ATTRIBUTE2 = l_def_cat_rec.attribute2,
2596 ATTRIBUTE3 = l_def_cat_rec.attribute3,
2597 ATTRIBUTE4 = l_def_cat_rec.attribute4,
2598 ATTRIBUTE5 = l_def_cat_rec.attribute5,
2599 ATTRIBUTE6 = l_def_cat_rec.attribute6,
2600 ATTRIBUTE7 = l_def_cat_rec.attribute7,
2601 ATTRIBUTE8 = l_def_cat_rec.attribute8,
2602 ATTRIBUTE9 = l_def_cat_rec.attribute9,
2603 ATTRIBUTE10 = l_def_cat_rec.attribute10,
2604 ATTRIBUTE11 = l_def_cat_rec.attribute11,
2605 ATTRIBUTE12 = l_def_cat_rec.attribute12,
2606 ATTRIBUTE13 = l_def_cat_rec.attribute13,
2607 ATTRIBUTE14 = l_def_cat_rec.attribute14,
2608 ATTRIBUTE15 = l_def_cat_rec.attribute15,
2609 ORG_ID = l_def_cat_rec.org_id,
2610 CREATED_BY = l_def_cat_rec.created_by,
2611 CREATION_DATE = l_def_cat_rec.creation_date,
2612 LAST_UPDATED_BY = l_def_cat_rec.last_updated_by,
2613 LAST_UPDATE_DATE = l_def_cat_rec.last_update_date,
2614 LAST_UPDATE_LOGIN = l_def_cat_rec.last_update_login,
2615 CAU_ID = l_def_cat_rec.CAU_ID,
2616 -- new column to hold number of days to reserve advanced payment for contract.
2617 NUM_DAYS_HOLD_ADV_PAY = l_def_cat_rec.num_days_hold_adv_pay
2618 WHERE ID = l_def_cat_rec.id;
2619
2620 /* -- history tables not supported -- 04 APR 2002
2621 -- Insert into History table
2622
2623 insert_row(
2624 p_init_msg_list,
2625 x_return_status,
2626 x_msg_count,
2627 x_msg_data,
2628 l_okl_cash_allctn_rls_h_rec,
2629 lx_okl_cash_allctn_rls_h_rec
2630 );
2631
2632 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2633 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2634 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2635 RAISE Okl_Api.G_EXCEPTION_ERROR;
2636 END IF;
2637 */
2638
2639 x_cat_rec := l_def_cat_rec;
2640 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2641 EXCEPTION
2642 WHEN okl_api.G_EXCEPTION_ERROR THEN
2643 x_return_status := 'E';
2644 NULL;
2645 /*
2646 x_return_status := okl_api.HANDLE_EXCEPTIONS
2647 (
2648 l_api_name,
2649 G_PKG_NAME,
2650 'okl_api.G_RET_STS_ERROR',
2651 x_msg_count,
2652 x_msg_data,
2653 '_PVT'
2654 );
2655 */
2656 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2657 x_return_status := 'U';
2658 NULL;
2659 /*
2660 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2661 (
2662 l_api_name,
2663 G_PKG_NAME,
2664 'okl_api.G_RET_STS_UNEXP_ERROR',
2665 x_msg_count,
2666 x_msg_data,
2667 '_PVT'
2668 );
2669 */
2670 WHEN OTHERS THEN
2671 x_return_status := 'U';
2672 NULL;
2673 /*
2674 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2675 (
2676 l_api_name,
2677 G_PKG_NAME,
2678 'OTHERS',
2679 x_msg_count,
2680 x_msg_data,
2681 '_PVT'
2682 );
2683 */
2684
2685 END update_row;
2686 ------------------------------------------
2687 -- update_row for:OKL_CASH_ALLCTN_RLS_V --
2688 ------------------------------------------
2689 ---------------------------------------------------------------------------
2690 -- Start of comments
2691 --
2692 -- Procedure Name : update_row
2693 -- Description : Update the row in the table OKL_CASH_ALLCTN_RLS.
2694 -- Business Rules :
2695 -- Parameters : p_init_msg_list, x_return_status, x_msg_count, x_msg_data,
2696 -- p_catv_rec, x_catv_rec
2697 -- Version : 1.0
2698 -- History : 25-AUG-04 abindal modified to include NUM_OF_DAYS_TO_HOLD_ADV_PAY column.
2699 -- : 12-oct-04 sjalasut changed the column NUM_OF_DAYS_TO_HOLD_ADV_PAY
2700 -- to NUM_DAYS_HOLD_ADV_PAY per user defined streams build
2701 -- End of comments
2702 ---------------------------------------------------------------------------
2703
2704 PROCEDURE update_row(
2705 p_api_version IN NUMBER,
2706 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2707 x_return_status OUT NOCOPY VARCHAR2,
2708 x_msg_count OUT NOCOPY NUMBER,
2709 x_msg_data OUT NOCOPY VARCHAR2,
2710 p_catv_rec IN catv_rec_type,
2711 x_catv_rec OUT NOCOPY catv_rec_type) IS
2712
2713 l_api_version CONSTANT NUMBER := 1;
2714 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2715 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2716 l_catv_rec catv_rec_type := p_catv_rec;
2717 l_def_catv_rec catv_rec_type;
2718 l_cat_rec cat_rec_type;
2719 lx_cat_rec cat_rec_type;
2720 -------------------------------
2721 -- FUNCTION fill_who_columns --
2722 -------------------------------
2723 FUNCTION fill_who_columns (
2724 p_catv_rec IN catv_rec_type
2725 ) RETURN catv_rec_type IS
2726 l_catv_rec catv_rec_type := p_catv_rec;
2727 BEGIN
2728 l_catv_rec.LAST_UPDATE_DATE := SYSDATE;
2729 l_catv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
2730 l_catv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2731 RETURN(l_catv_rec);
2732 END fill_who_columns;
2733 ----------------------------------
2734 -- FUNCTION populate_new_record --
2735 ----------------------------------
2736
2737 FUNCTION populate_new_record (
2738 p_catv_rec IN catv_rec_type,
2739 x_catv_rec OUT NOCOPY catv_rec_type
2740 ) RETURN VARCHAR2 IS
2741 l_catv_rec catv_rec_type;
2742 l_row_notfound BOOLEAN := TRUE;
2743 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2744 BEGIN
2745 x_catv_rec := p_catv_rec;
2746 -- Get current database values
2747 l_catv_rec := get_rec(p_catv_rec, l_row_notfound);
2748 IF (l_row_notfound) THEN
2749 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2750 END IF;
2751 IF (x_catv_rec.id = Okl_Api.G_MISS_NUM)
2752 THEN
2753 x_catv_rec.id := l_catv_rec.id;
2754 END IF;
2755 IF (x_catv_rec.object_version_number = Okl_Api.G_MISS_NUM)
2756 THEN
2757 x_catv_rec.object_version_number := l_catv_rec.object_version_number;
2758 END IF;
2759 IF (x_catv_rec.name = Okl_Api.G_MISS_CHAR)
2760 THEN
2761 x_catv_rec.name := l_catv_rec.name;
2762 END IF;
2763 IF (x_catv_rec.description = Okl_Api.G_MISS_CHAR)
2764 THEN
2765 x_catv_rec.description := l_catv_rec.description;
2766 END IF;
2767
2768 IF (x_catv_rec.start_date = Okl_Api.G_MISS_DATE)
2769 THEN
2770 x_catv_rec.start_date := l_catv_rec.start_date;
2771 END IF;
2772
2773 IF (x_catv_rec.end_date = Okl_Api.G_MISS_DATE)
2774 THEN
2775 x_catv_rec.end_date := l_catv_rec.end_date;
2776 END IF;
2777
2778 IF (x_catv_rec.amount_tolerance_percent = Okl_Api.G_MISS_NUM)
2779 THEN
2780 x_catv_rec.amount_tolerance_percent := l_catv_rec.amount_tolerance_percent;
2781 END IF;
2782 IF (x_catv_rec.days_past_quote_valid_toleranc = Okl_Api.G_MISS_NUM)
2783 THEN
2784 x_catv_rec.days_past_quote_valid_toleranc := l_catv_rec.days_past_quote_valid_toleranc;
2785 END IF;
2786 IF (x_catv_rec.months_to_bill_ahead = Okl_Api.G_MISS_NUM)
2787 THEN
2788 x_catv_rec.months_to_bill_ahead := l_catv_rec.months_to_bill_ahead;
2789 END IF;
2790 IF (x_catv_rec.under_payment_allocation_code = Okl_Api.G_MISS_CHAR)
2791 THEN
2792 x_catv_rec.under_payment_allocation_code := l_catv_rec.under_payment_allocation_code;
2793 END IF;
2794 IF (x_catv_rec.over_payment_allocation_code = Okl_Api.G_MISS_CHAR)
2795 THEN
2796 x_catv_rec.over_payment_allocation_code := l_catv_rec.over_payment_allocation_code;
2797 END IF;
2798 IF (x_catv_rec.receipt_msmtch_allocation_code = Okl_Api.G_MISS_CHAR)
2799 THEN
2800 x_catv_rec.receipt_msmtch_allocation_code := l_catv_rec.receipt_msmtch_allocation_code;
2801 END IF;
2802 IF (x_catv_rec.default_rule = Okl_Api.G_MISS_CHAR)
2803 THEN
2804 x_catv_rec.default_rule := l_catv_rec.default_rule;
2805 END IF;
2806 IF (x_catv_rec.attribute_category = Okl_Api.G_MISS_CHAR)
2807 THEN
2808 x_catv_rec.attribute_category := l_catv_rec.attribute_category;
2809 END IF;
2810 IF (x_catv_rec.attribute1 = Okl_Api.G_MISS_CHAR)
2811 THEN
2812 x_catv_rec.attribute1 := l_catv_rec.attribute1;
2813 END IF;
2814 IF (x_catv_rec.attribute2 = Okl_Api.G_MISS_CHAR)
2815 THEN
2816 x_catv_rec.attribute2 := l_catv_rec.attribute2;
2817 END IF;
2818 IF (x_catv_rec.attribute3 = Okl_Api.G_MISS_CHAR)
2819 THEN
2820 x_catv_rec.attribute3 := l_catv_rec.attribute3;
2821 END IF;
2822 IF (x_catv_rec.attribute4 = Okl_Api.G_MISS_CHAR)
2823 THEN
2824 x_catv_rec.attribute4 := l_catv_rec.attribute4;
2825 END IF;
2826 IF (x_catv_rec.attribute5 = Okl_Api.G_MISS_CHAR)
2827 THEN
2828 x_catv_rec.attribute5 := l_catv_rec.attribute5;
2829 END IF;
2830 IF (x_catv_rec.attribute6 = Okl_Api.G_MISS_CHAR)
2831 THEN
2832 x_catv_rec.attribute6 := l_catv_rec.attribute6;
2833 END IF;
2834 IF (x_catv_rec.attribute7 = Okl_Api.G_MISS_CHAR)
2835 THEN
2836 x_catv_rec.attribute7 := l_catv_rec.attribute7;
2837 END IF;
2838 IF (x_catv_rec.attribute8 = Okl_Api.G_MISS_CHAR)
2839 THEN
2840 x_catv_rec.attribute8 := l_catv_rec.attribute8;
2841 END IF;
2842 IF (x_catv_rec.attribute9 = Okl_Api.G_MISS_CHAR)
2843 THEN
2844 x_catv_rec.attribute9 := l_catv_rec.attribute9;
2845 END IF;
2846 IF (x_catv_rec.attribute10 = Okl_Api.G_MISS_CHAR)
2847 THEN
2848 x_catv_rec.attribute10 := l_catv_rec.attribute10;
2849 END IF;
2850 IF (x_catv_rec.attribute11 = Okl_Api.G_MISS_CHAR)
2851 THEN
2852 x_catv_rec.attribute11 := l_catv_rec.attribute11;
2853 END IF;
2854 IF (x_catv_rec.attribute12 = Okl_Api.G_MISS_CHAR)
2855 THEN
2856 x_catv_rec.attribute12 := l_catv_rec.attribute12;
2857 END IF;
2858 IF (x_catv_rec.attribute13 = Okl_Api.G_MISS_CHAR)
2859 THEN
2860 x_catv_rec.attribute13 := l_catv_rec.attribute13;
2861 END IF;
2862 IF (x_catv_rec.attribute14 = Okl_Api.G_MISS_CHAR)
2863 THEN
2864 x_catv_rec.attribute14 := l_catv_rec.attribute14;
2865 END IF;
2866 IF (x_catv_rec.attribute15 = Okl_Api.G_MISS_CHAR)
2867 THEN
2868 x_catv_rec.attribute15 := l_catv_rec.attribute15;
2869 END IF;
2870 IF (x_catv_rec.org_id = Okl_Api.G_MISS_NUM)
2871 THEN
2872 x_catv_rec.org_id := l_catv_rec.org_id;
2873 END IF;
2874 IF (x_catv_rec.created_by = Okl_Api.G_MISS_NUM)
2875 THEN
2876 x_catv_rec.created_by := l_catv_rec.created_by;
2877 END IF;
2878 IF (x_catv_rec.creation_date = Okl_Api.G_MISS_DATE)
2879 THEN
2880 x_catv_rec.creation_date := l_catv_rec.creation_date;
2881 END IF;
2882 IF (x_catv_rec.last_updated_by = Okl_Api.G_MISS_NUM)
2883 THEN
2884 x_catv_rec.last_updated_by := l_catv_rec.last_updated_by;
2885 END IF;
2886 IF (x_catv_rec.last_update_date = Okl_Api.G_MISS_DATE)
2887 THEN
2888 x_catv_rec.last_update_date := l_catv_rec.last_update_date;
2889 END IF;
2890 IF (x_catv_rec.last_update_login = Okl_Api.G_MISS_NUM)
2891 THEN
2892 x_catv_rec.last_update_login := l_catv_rec.last_update_login;
2893 END IF;
2894
2895 IF (x_catv_rec.CAU_ID = Okl_Api.G_MISS_NUM)
2896 THEN
2897 x_catv_rec.CAU_ID := l_catv_rec.CAU_ID;
2898 END IF;
2899 -- new column to hold number of days to reserve advanced payment for contract.
2900 IF (x_catv_rec.num_days_hold_adv_pay = Okl_Api.G_MISS_NUM)
2901 THEN
2902 x_catv_rec.num_days_hold_adv_pay := l_catv_rec.num_days_hold_adv_pay;
2903 END IF;
2904
2905 RETURN(l_return_status);
2906 END populate_new_record;
2907 ----------------------------------------------
2908 -- Set_Attributes for:OKL_CASH_ALLCTN_RLS_V --
2909 ----------------------------------------------
2910 FUNCTION Set_Attributes (
2911 p_catv_rec IN catv_rec_type,
2912 x_catv_rec OUT NOCOPY catv_rec_type
2913 ) RETURN VARCHAR2 IS
2914 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2915 BEGIN
2916 x_catv_rec := p_catv_rec;
2917 x_catv_rec.OBJECT_VERSION_NUMBER := NVL(x_catv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2918 RETURN(l_return_status);
2919 END Set_Attributes;
2920 BEGIN
2921
2922 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
2923 G_PKG_NAME,
2924 p_init_msg_list,
2925 l_api_version,
2926 p_api_version,
2927 '_PVT',
2928 x_return_status);
2929
2930 -- this caused probs before.
2931
2932 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2933 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2934 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2935 RAISE Okl_Api.G_EXCEPTION_ERROR;
2936 END IF;
2937
2938 --- Setting item attributes
2939 l_return_status := Set_Attributes(
2940 p_catv_rec, -- IN
2941 l_catv_rec); -- OUT
2942 --- If any errors happen abort API
2943 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2944 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2945 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2946 RAISE Okl_Api.G_EXCEPTION_ERROR;
2947 END IF;
2948
2949 l_return_status := populate_new_record(l_catv_rec, l_def_catv_rec);
2950 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2951 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2952 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2953 RAISE Okl_Api.G_EXCEPTION_ERROR;
2954 END IF;
2955 l_def_catv_rec := fill_who_columns(l_def_catv_rec);
2956 --- Validate all non-missing attributes (Item Level Validation)
2957 l_return_status := Validate_Attributes(l_def_catv_rec); -- this is failing ....
2958 --- If any errors happen abort API
2959 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2960 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2961 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2962 RAISE Okl_Api.G_EXCEPTION_ERROR;
2963 END IF;
2964 l_return_status := Validate_Record(l_def_catv_rec);
2965 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2966 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2967 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2968 RAISE Okl_Api.G_EXCEPTION_ERROR;
2969 END IF;
2970
2971 --------------------------------------
2972 -- Move VIEW record to "Child" records
2973 --------------------------------------
2974 migrate(l_def_catv_rec, l_cat_rec);
2975 --------------------------------------------
2976 -- Call the UPDATE_ROW for each child record
2977 --------------------------------------------
2978
2979
2980 update_row(
2981 p_init_msg_list,
2982 x_return_status,
2983 x_msg_count,
2984 x_msg_data,
2985 l_cat_rec,
2986 lx_cat_rec
2987 );
2988
2989 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2990 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2991 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2992 RAISE Okl_Api.G_EXCEPTION_ERROR;
2993 END IF;
2994 migrate(lx_cat_rec, l_def_catv_rec);
2995 x_catv_rec := l_def_catv_rec;
2996 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2997 EXCEPTION
2998 WHEN okl_api.G_EXCEPTION_ERROR THEN
2999 null;
3000 x_return_status := 'U';
3001 /*
3002 x_return_status := okl_api.HANDLE_EXCEPTIONS
3003 (
3004 l_api_name,
3005 G_PKG_NAME,
3006 'okl_api.G_RET_STS_ERROR',
3007 x_msg_count,
3008 x_msg_data,
3009 '_PVT'
3010 );
3011 */
3012 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3013 null;
3014 x_return_status := 'U';
3015 /*
3016 x_return_status :=okl_api.HANDLE_EXCEPTIONS
3017 (
3018 l_api_name,
3019 G_PKG_NAME,
3020 'okl_api.G_RET_STS_UNEXP_ERROR',
3021 x_msg_count,
3022 x_msg_data,
3023 '_PVT'
3024 );
3025 */
3026 WHEN OTHERS THEN
3027 NULL;
3028 x_return_status := 'U';
3029 /*
3030 x_return_status :=okl_api.HANDLE_EXCEPTIONS
3031 (
3032 l_api_name,
3033 G_PKG_NAME,
3034 'OTHERS',
3035 x_msg_count,
3036 x_msg_data,
3037 '_PVT'
3038 );
3039
3040 */
3041 END update_row;
3042 ----------------------------------------
3043 -- PL/SQL TBL update_row for:CATV_TBL --
3044 ----------------------------------------
3045 PROCEDURE update_row(
3046 p_api_version IN NUMBER,
3047 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3048 x_return_status OUT NOCOPY VARCHAR2,
3049 x_msg_count OUT NOCOPY NUMBER,
3050 x_msg_data OUT NOCOPY VARCHAR2,
3051 p_catv_tbl IN catv_tbl_type,
3052 x_catv_tbl OUT NOCOPY catv_tbl_type) IS
3053
3054 l_api_version CONSTANT NUMBER := 1;
3055 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3056 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3057 -- Begin Post-Generation Change
3058 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3059 -- End Post-Generation Change
3060 i NUMBER := 0;
3061 BEGIN
3062 Okl_Api.init_msg_list(p_init_msg_list);
3063 -- Make sure PL/SQL table has records in it before passing
3064 IF (p_catv_tbl.COUNT > 0) THEN
3065 i := p_catv_tbl.FIRST;
3066 LOOP
3067 update_row (
3068 p_api_version => p_api_version,
3069 p_init_msg_list => Okl_Api.G_FALSE,
3070 x_return_status => x_return_status,
3071 x_msg_count => x_msg_count,
3072 x_msg_data => x_msg_data,
3073 p_catv_rec => p_catv_tbl(i),
3074 x_catv_rec => x_catv_tbl(i));
3075 -- Begin Post-Generation Change
3076 -- store the highest degree of error
3077 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
3078 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3079 l_overall_status := x_return_status;
3080 END IF;
3081 END IF;
3082 -- End Post-Generation Change
3083 EXIT WHEN (i = p_catv_tbl.LAST);
3084 i := p_catv_tbl.NEXT(i);
3085 END LOOP;
3086 -- Begin Post-Generation Change
3087 -- return overall status
3088 x_return_status := l_overall_status;
3089 -- End Post-Generation Change
3090 END IF;
3091 EXCEPTION
3092 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3093 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3094 (
3095 l_api_name,
3096 G_PKG_NAME,
3097 'Okl_Api.G_RET_STS_ERROR',
3098 x_msg_count,
3099 x_msg_data,
3100 '_PVT'
3101 );
3102 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3103 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3104 (
3105 l_api_name,
3106 G_PKG_NAME,
3107 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3108 x_msg_count,
3109 x_msg_data,
3110 '_PVT'
3111 );
3112 WHEN OTHERS THEN
3113 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3114 (
3115 l_api_name,
3116 G_PKG_NAME,
3117 'OTHERS',
3118 x_msg_count,
3119 x_msg_data,
3120 '_PVT'
3121 );
3122 END update_row;
3123
3124 ---------------------------------------------------------------------------
3125 -- PROCEDURE delete_row
3126 ---------------------------------------------------------------------------
3127 ----------------------------------------
3128 -- delete_row for:OKL_CASH_ALLCTN_RLS --
3129 ----------------------------------------
3130 PROCEDURE delete_row(
3131 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3132 x_return_status OUT NOCOPY VARCHAR2,
3133 x_msg_count OUT NOCOPY NUMBER,
3134 x_msg_data OUT NOCOPY VARCHAR2,
3135 p_cat_rec IN cat_rec_type) IS
3136
3137 l_api_version CONSTANT NUMBER := 1;
3138 l_api_name CONSTANT VARCHAR2(30) := 'RLS_delete_row';
3139 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3140 l_cat_rec cat_rec_type:= p_cat_rec;
3141 l_row_notfound BOOLEAN := TRUE;
3142
3143 -- history tables not supported -- 04 APR 2002
3144 -- l_okl_cash_allctn_rls_h_rec okl_cash_allctn_rls_h_rec_type;
3145 -- lx_okl_cash_allctn_rls_h_rec okl_cash_allctn_rls_h_rec_type;
3146
3147 BEGIN
3148 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
3149 p_init_msg_list,
3150 '_PVT',
3151 x_return_status);
3152 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3153 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3154 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3155 RAISE Okl_Api.G_EXCEPTION_ERROR;
3156 END IF;
3157 -- Insert into History table
3158 l_cat_rec := get_rec(l_cat_rec, l_row_notfound);
3159 IF (l_row_notfound) THEN
3160 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3161 END IF;
3162
3163 -- history tables not supported -- 04 APR 2002
3164 -- migrate(l_cat_rec, l_okl_cash_allctn_rls_h_rec);
3165 /*
3166 insert_row(
3167 p_init_msg_list,
3168 x_return_status,
3169 x_msg_count,
3170 x_msg_data,
3171 l_okl_cash_allctn_rls_h_rec,
3172 lx_okl_cash_allctn_rls_h_rec
3173 );
3174 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3175 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3176 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3177 RAISE Okl_Api.G_EXCEPTION_ERROR;
3178 END IF;
3179 */
3180
3181 DELETE FROM OKL_CASH_ALLCTN_RLS
3182 WHERE ID = l_cat_rec.id;
3183
3184 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
3185 EXCEPTION
3186 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3187 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3188 (
3189 l_api_name,
3190 G_PKG_NAME,
3191 'Okl_Api.G_RET_STS_ERROR',
3192 x_msg_count,
3193 x_msg_data,
3194 '_PVT'
3195 );
3196 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3197 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3198 (
3199 l_api_name,
3200 G_PKG_NAME,
3201 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3202 x_msg_count,
3203 x_msg_data,
3204 '_PVT'
3205 );
3206 WHEN OTHERS THEN
3207 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3208 (
3209 l_api_name,
3210 G_PKG_NAME,
3211 'OTHERS',
3212 x_msg_count,
3213 x_msg_data,
3214 '_PVT'
3215 );
3216 END delete_row;
3217 ------------------------------------------
3218 -- delete_row for:OKL_CASH_ALLCTN_RLS_V --
3219 ------------------------------------------
3220 PROCEDURE delete_row(
3221 p_api_version IN NUMBER,
3222 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3223 x_return_status OUT NOCOPY VARCHAR2,
3224 x_msg_count OUT NOCOPY NUMBER,
3225 x_msg_data OUT NOCOPY VARCHAR2,
3226 p_catv_rec IN catv_rec_type) IS
3227
3228 l_api_version CONSTANT NUMBER := 1;
3229 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3230 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3231 l_catv_rec catv_rec_type := p_catv_rec;
3232 l_cat_rec cat_rec_type;
3233 BEGIN
3234 l_return_status := Okl_Api.START_ACTIVITY(l_api_name,
3235 G_PKG_NAME,
3236 p_init_msg_list,
3237 l_api_version,
3238 p_api_version,
3239 '_PVT',
3240 x_return_status);
3241 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3242 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3243 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3244 RAISE Okl_Api.G_EXCEPTION_ERROR;
3245 END IF;
3246 --------------------------------------
3247 -- Move VIEW record to "Child" records
3248 --------------------------------------
3249 migrate(l_catv_rec, l_cat_rec);
3250 --------------------------------------------
3251 -- Call the DELETE_ROW for each child record
3252 --------------------------------------------
3253 delete_row(
3254 p_init_msg_list,
3255 x_return_status,
3256 x_msg_count,
3257 x_msg_data,
3258 l_cat_rec
3259 );
3260 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3261 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3262 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3263 RAISE Okl_Api.G_EXCEPTION_ERROR;
3264 END IF;
3265 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
3266 EXCEPTION
3267 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3268 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3269 (
3270 l_api_name,
3271 G_PKG_NAME,
3272 'Okl_Api.G_RET_STS_ERROR',
3273 x_msg_count,
3274 x_msg_data,
3275 '_PVT'
3276 );
3277 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3278 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3279 (
3280 l_api_name,
3281 G_PKG_NAME,
3282 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3283 x_msg_count,
3284 x_msg_data,
3285 '_PVT'
3286 );
3287 WHEN OTHERS THEN
3288 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3289 (
3290 l_api_name,
3291 G_PKG_NAME,
3292 'OTHERS',
3293 x_msg_count,
3294 x_msg_data,
3295 '_PVT'
3296 );
3297 END delete_row;
3298 ----------------------------------------
3299 -- PL/SQL TBL delete_row for:CATV_TBL --
3300 ----------------------------------------
3301 PROCEDURE delete_row(
3302 p_api_version IN NUMBER,
3303 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3304 x_return_status OUT NOCOPY VARCHAR2,
3305 x_msg_count OUT NOCOPY NUMBER,
3306 x_msg_data OUT NOCOPY VARCHAR2,
3307 p_catv_tbl IN catv_tbl_type) IS
3308
3309 l_api_version CONSTANT NUMBER := 1;
3310 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3311 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3312 -- Begin Post-Generation Change
3313 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3314 -- End Post-Generation Change
3315 i NUMBER := 0;
3316 BEGIN
3317 Okl_Api.init_msg_list(p_init_msg_list);
3318 -- Make sure PL/SQL table has records in it before passing
3319 IF (p_catv_tbl.COUNT > 0) THEN
3320 i := p_catv_tbl.FIRST;
3321 LOOP
3322 delete_row (
3323 p_api_version => p_api_version,
3324 p_init_msg_list => Okl_Api.G_FALSE,
3325 x_return_status => x_return_status,
3326 x_msg_count => x_msg_count,
3327 x_msg_data => x_msg_data,
3328 p_catv_rec => p_catv_tbl(i));
3329 -- Begin Post-Generation Change
3330 -- store the highest degree of error
3331 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
3332 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3333 l_overall_status := x_return_status;
3334 END IF;
3335 END IF;
3336 -- End Post-Generation Change
3337 EXIT WHEN (i = p_catv_tbl.LAST);
3338 i := p_catv_tbl.NEXT(i);
3339 END LOOP;
3340 -- Begin Post-Generation Change
3341 -- return overall status
3342 x_return_status := l_overall_status;
3343 -- End Post-Generation Change
3344 END IF;
3345 EXCEPTION
3346 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3347 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
3348 (
3349 l_api_name,
3350 G_PKG_NAME,
3351 'Okl_Api.G_RET_STS_ERROR',
3352 x_msg_count,
3353 x_msg_data,
3354 '_PVT'
3355 );
3356 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3357 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3358 (
3359 l_api_name,
3360 G_PKG_NAME,
3361 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3362 x_msg_count,
3363 x_msg_data,
3364 '_PVT'
3365 );
3366 WHEN OTHERS THEN
3367 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3368 (
3369 l_api_name,
3370 G_PKG_NAME,
3371 'OTHERS',
3372 x_msg_count,
3373 x_msg_data,
3374 '_PVT'
3375 );
3376 END delete_row;
3377 END Okl_Cat_Pvt;