[Home] [Help]
PACKAGE BODY: APPS.OKL_SUB_PVT
Source
1 PACKAGE BODY OKL_SUB_PVT AS
2 /* $Header: OKLSSUBB.pls 120.18 2007/09/28 11:42:07 ssdeshpa noship $ */
3 ------------------------------------------------------------------------------
4 --global message constants : for custom validations
5 ------------------------------------------------------------------------------
6 G_SUBSIDY_INVALID_DATES CONSTANT VARCHAR2(200) := 'OKL_INVALID_END_DATE';
7 G_INVALID_RECEIPT_METHOD CONSTANT VARCHAR2(200) := 'OKL_SUB_INVALID_RECEIPT_METHOD';
8 G_INVALID_RECOURSE_FLAG CONSTANT VARCHAR2(200) := 'OKL_SUB_INVALID_RECOURSE_FLAG';
9 --cklee:start
10 G_NOT_UNIQUE CONSTANT VARCHAR2(30) := 'OKL_LLA_NOT_UNIQUE';
11 --cklee:end
12 -- sjalasut start
13 G_SUBSIDY_POOL_STATUS CONSTANT fnd_new_messages.message_name%TYPE := 'OKL_LLA_SUB_POOL_ACTIVE';
14 G_SUBSIDY_POOL_EFFECTIVE_DATES CONSTANT fnd_new_messages.message_name%TYPE := 'OKL_LLA_SUB_DATES_NO_OVERLAP';
15 --cklee 09/12/2005 G_SUBSIDY_ATTACH_ASSET_EXIST CONSTANT fnd_new_messages.message_name%TYPE := 'OKL_LL_SUB_ATTACH_ASSET';
16 G_SUBSIDY_ATTACH_ASSET_EXIST CONSTANT fnd_new_messages.message_name%TYPE := 'OKL_LLA_SUB_ATTACH_ASSET';
17 -- sjalasut end
18
19 --cklee:start 07/22/05
20 G_SUBSIDY_POOL_ASSOC_STATUS CONSTANT fnd_new_messages.message_name%TYPE := 'OKL_LLA_SUB_POOL_ASSOC_STATUS';
21 G_SUBSIDY_POOL_DISSOC_STATUS CONSTANT fnd_new_messages.message_name%TYPE := 'OKL_LLA_SUB_POOL_DISSOC_STATUS';
22 G_SUBSIDY_POOL_ASSOC_EXP_POOL CONSTANT fnd_new_messages.message_name%TYPE := 'OKL_LLA_SUB_POOL_ASSC_EXP_POOL';
23 G_SUBSIDY_POOL_DISOC_EXP_POOL CONSTANT fnd_new_messages.message_name%TYPE := 'OKL_LLA_SUB_POOL_DISC_EXP_POOL';
24 --cklee:end 07/22/05
25
26 ---------------------------------------------------------------------------
27 -- PROCEDURE load_error_tbl
28 ---------------------------------------------------------------------------
29 PROCEDURE load_error_tbl (
30 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
31 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
32
33 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
34 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
35 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
36 BEGIN
37 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
38 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
39 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
40 -- automatically increments the index by 1, (making it 2), however, when the GET function
41 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
42 -- message 2. To circumvent this problem, check the amount of messages and compensate.
43 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
44 -- will only update the index variable when 1 and only 1 message is on the stack.
45 IF (last_msg_idx = 1) THEN
46 l_msg_idx := FND_MSG_PUB.G_FIRST;
47 END IF;
48 LOOP
49 fnd_msg_pub.get(
50 p_msg_index => l_msg_idx,
51 p_encoded => fnd_api.g_false,
52 p_data => px_error_rec.msg_data,
53 p_msg_index_out => px_error_rec.msg_count);
54 px_error_tbl(j) := px_error_rec;
55 j := j + 1;
56 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
57 END LOOP;
58 END load_error_tbl;
59 ---------------------------------------------------------------------------
60 -- FUNCTION find_highest_exception
61 ---------------------------------------------------------------------------
62 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
63 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
64 FUNCTION find_highest_exception(
65 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
66 ) RETURN VARCHAR2 IS
67 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
68 i INTEGER := 1;
69 BEGIN
70 IF (p_error_tbl.COUNT > 0) THEN
71 i := p_error_tbl.FIRST;
72 LOOP
73 IF (p_error_tbl(i).error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
74 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
75 l_return_status := p_error_tbl(i).error_type;
76 END IF;
77 END IF;
78 EXIT WHEN (i = p_error_tbl.LAST);
79 i := p_error_tbl.NEXT(i);
80 END LOOP;
81 END IF;
82 RETURN(l_return_status);
83 END find_highest_exception;
84 ---------------------------------------------------------------------------
85 -- FUNCTION get_seq_id
86 ---------------------------------------------------------------------------
87 FUNCTION get_seq_id RETURN NUMBER IS
88 BEGIN
89 RETURN(okc_p_util.raw_to_number(sys_guid()));
90 END get_seq_id;
91
92 ---------------------------------------------------------------------------
93 -- PROCEDURE qc
94 ---------------------------------------------------------------------------
95 PROCEDURE qc IS
96 BEGIN
97 null;
98 END qc;
99
100 ---------------------------------------------------------------------------
101 -- PROCEDURE change_version
102 ---------------------------------------------------------------------------
103 PROCEDURE change_version IS
104 BEGIN
105 null;
106 END change_version;
107
108 ---------------------------------------------------------------------------
109 -- PROCEDURE api_copy
110 ---------------------------------------------------------------------------
111 PROCEDURE api_copy IS
112 BEGIN
113 null;
114 END api_copy;
115
116 ---------------------------------------------------------------------------
117 -- PROCEDURE add_language
118 ---------------------------------------------------------------------------
119 PROCEDURE add_language IS
120 BEGIN
121 DELETE FROM OKL_SUBSIDIES_TL T
122 WHERE NOT EXISTS (
123 SELECT NULL
124 FROM OKL_SUBSIDIES_ALL_B B
125 WHERE B.ID =T.ID
126 );
127
128 UPDATE OKL_SUBSIDIES_TL T SET(
129 SHORT_DESCRIPTION,
130 DESCRIPTION) = (SELECT
131 B.SHORT_DESCRIPTION,
132 B.DESCRIPTION
133 FROM OKL_SUBSIDIES_TL B
134 WHERE B.ID = T.ID
135 AND B.LANGUAGE = T.SOURCE_LANG)
136 WHERE ( T.ID,
137 T.LANGUAGE)
138 IN (SELECT
139 SUBT.ID,
140 SUBT.LANGUAGE
141 FROM OKL_SUBSIDIES_TL SUBB, OKL_SUBSIDIES_TL SUBT
142 WHERE SUBB.ID = SUBT.ID
143 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
144 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
145 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
146 OR (SUBB.SHORT_DESCRIPTION IS NOT NULL AND SUBT.SHORT_DESCRIPTION IS NULL)
147 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
148 ));
149
150 INSERT INTO OKL_SUBSIDIES_TL (
151 ID,
152 SHORT_DESCRIPTION,
153 DESCRIPTION,
154 LANGUAGE,
155 SOURCE_LANG,
156 SFWT_FLAG,
157 CREATED_BY,
158 CREATION_DATE,
159 LAST_UPDATED_BY,
160 LAST_UPDATE_DATE,
161 LAST_UPDATE_LOGIN)
162 SELECT
163 B.ID,
164 B.SHORT_DESCRIPTION,
165 B.DESCRIPTION,
166 L.LANGUAGE_CODE,
167 B.SOURCE_LANG,
168 B.SFWT_FLAG,
169 B.CREATED_BY,
170 B.CREATION_DATE,
171 B.LAST_UPDATED_BY,
172 B.LAST_UPDATE_DATE,
173 B.LAST_UPDATE_LOGIN
174 FROM OKL_SUBSIDIES_TL B, FND_LANGUAGES L
175 WHERE L.INSTALLED_FLAG IN ('I', 'B')
176 AND B.LANGUAGE = USERENV('LANG')
177 AND NOT EXISTS (
178 SELECT NULL
179 FROM OKL_SUBSIDIES_TL T
180 WHERE T.ID = B.ID
181 AND T.LANGUAGE = L.LANGUAGE_CODE
182 );
183 END add_language;
184
185 ---------------------------------------------------------------------------
186 -- FUNCTION get_rec for: OKL_SUBSIDIES_V
187 ---------------------------------------------------------------------------
188 FUNCTION get_rec (
189 p_subv_rec IN subv_rec_type,
190 x_no_data_found OUT NOCOPY BOOLEAN
191 ) RETURN subv_rec_type IS
192 CURSOR okl_subsidies_v_pk_csr (p_id IN NUMBER) IS
193 SELECT
194 ID,
195 OBJECT_VERSION_NUMBER,
196 SFWT_FLAG,
197 ORG_ID,
198 NAME,
199 SHORT_DESCRIPTION,
200 DESCRIPTION,
201 EFFECTIVE_FROM_DATE,
202 EFFECTIVE_TO_DATE,
203 EXPIRE_AFTER_DAYS,
204 CURRENCY_CODE,
205 EXCLUSIVE_YN,
206 APPLICABLE_TO_RELEASE_YN,
207 SUBSIDY_CALC_BASIS,
208 AMOUNT,
209 PERCENT,
210 FORMULA_ID,
211 rate_points,
212 MAXIMUM_TERM,
213 VENDOR_ID,
214 ACCOUNTING_METHOD_CODE,
215 RECOURSE_YN,
216 TERMINATION_REFUND_BASIS,
217 REFUND_FORMULA_ID,
218 STREAM_TYPE_ID,
219 RECEIPT_METHOD_CODE,
220 CUSTOMER_VISIBLE_YN,
221 MAXIMUM_FINANCED_AMOUNT,
222 MAXIMUM_SUBSIDY_AMOUNT,
223 --Start code changes for Subsidy by fmiao on 10/25/2004--
224 TRANSFER_BASIS_CODE,
225 --End code changes for Subsidy by fmiao on 10/25/2004--
226 ATTRIBUTE_CATEGORY,
227 ATTRIBUTE1,
228 ATTRIBUTE2,
229 ATTRIBUTE3,
230 ATTRIBUTE4,
231 ATTRIBUTE5,
232 ATTRIBUTE6,
233 ATTRIBUTE7,
234 ATTRIBUTE8,
235 ATTRIBUTE9,
236 ATTRIBUTE10,
237 ATTRIBUTE11,
238 ATTRIBUTE12,
239 ATTRIBUTE13,
240 ATTRIBUTE14,
241 ATTRIBUTE15,
242 CREATED_BY,
243 CREATION_DATE,
244 LAST_UPDATED_BY,
245 LAST_UPDATE_DATE,
246 LAST_UPDATE_LOGIN,
247 -- sjalasut added new column for subsidy pools enhancement. start
248 SUBSIDY_POOL_ID
249 -- sjalasut added new column for subsidy pools enhancement. end
250 FROM Okl_Subsidies_V
251 WHERE okl_subsidies_v.id = p_id;
252 l_okl_subsidies_v_pk okl_subsidies_v_pk_csr%ROWTYPE;
253 l_subv_rec subv_rec_type;
254 BEGIN
255 x_no_data_found := TRUE;
256 -- Get current database values
257 OPEN okl_subsidies_v_pk_csr (p_subv_rec.id);
258 FETCH okl_subsidies_v_pk_csr INTO
259 l_subv_rec.id,
260 l_subv_rec.object_version_number,
261 l_subv_rec.sfwt_flag,
262 l_subv_rec.org_id,
263 l_subv_rec.name,
264 l_subv_rec.short_description,
265 l_subv_rec.description,
266 l_subv_rec.effective_from_date,
267 l_subv_rec.effective_to_date,
268 l_subv_rec.expire_after_days,
269 l_subv_rec.currency_code,
270 l_subv_rec.exclusive_yn,
271 l_subv_rec.applicable_to_release_yn,
272 l_subv_rec.subsidy_calc_basis,
273 l_subv_rec.amount,
274 l_subv_rec.percent,
275 l_subv_rec.formula_id,
276 l_subv_rec.rate_points,
277 l_subv_rec.maximum_term,
278 l_subv_rec.vendor_id,
279 l_subv_rec.accounting_method_code,
280 l_subv_rec.recourse_yn,
281 l_subv_rec.termination_refund_basis,
282 l_subv_rec.refund_formula_id,
283 l_subv_rec.stream_type_id,
284 l_subv_rec.receipt_method_code,
285 l_subv_rec.customer_visible_yn,
286 l_subv_rec.maximum_financed_amount,
287 l_subv_rec.maximum_subsidy_amount,
288 --Start code changes for Subsidy by fmiao on 10/25/2004--
289 l_subv_rec.transfer_basis_code,
290 --End code changes for Subsidy by fmiao on 10/25/2004--
291 l_subv_rec.attribute_category,
292 l_subv_rec.attribute1,
293 l_subv_rec.attribute2,
294 l_subv_rec.attribute3,
295 l_subv_rec.attribute4,
296 l_subv_rec.attribute5,
297 l_subv_rec.attribute6,
298 l_subv_rec.attribute7,
299 l_subv_rec.attribute8,
300 l_subv_rec.attribute9,
301 l_subv_rec.attribute10,
302 l_subv_rec.attribute11,
303 l_subv_rec.attribute12,
304 l_subv_rec.attribute13,
305 l_subv_rec.attribute14,
306 l_subv_rec.attribute15,
307 l_subv_rec.created_by,
308 l_subv_rec.creation_date,
309 l_subv_rec.last_updated_by,
310 l_subv_rec.last_update_date,
311 l_subv_rec.last_update_login,
312 -- sjalasut added new column for subsidy pools enhancement. start
313 l_subv_rec.subsidy_pool_id;
314 -- sjalasut added new column for subsidy pools enhancement. end
315 x_no_data_found := okl_subsidies_v_pk_csr%NOTFOUND;
316 CLOSE okl_subsidies_v_pk_csr;
317 RETURN(l_subv_rec);
318 END get_rec;
319
320 ------------------------------------------------------------------
321 -- This version of get_rec sets error messages if no data found --
322 ------------------------------------------------------------------
323 FUNCTION get_rec (
324 p_subv_rec IN subv_rec_type,
325 x_return_status OUT NOCOPY VARCHAR2
326 ) RETURN subv_rec_type IS
327 l_subv_rec subv_rec_type;
328 l_row_notfound BOOLEAN := TRUE;
329 BEGIN
330 x_return_status := OKL_API.G_RET_STS_SUCCESS;
331 l_subv_rec := get_rec(p_subv_rec, l_row_notfound);
332 IF (l_row_notfound) THEN
333 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
334 x_return_status := OKL_API.G_RET_STS_ERROR;
335 END IF;
336 RETURN(l_subv_rec);
337 END get_rec;
338 -----------------------------------------------------------
339 -- So we don't have to pass an "l_row_notfound" variable --
340 -----------------------------------------------------------
341 FUNCTION get_rec (
342 p_subv_rec IN subv_rec_type
343 ) RETURN subv_rec_type IS
344 l_row_not_found BOOLEAN := TRUE;
345 BEGIN
346 RETURN(get_rec(p_subv_rec, l_row_not_found));
347 END get_rec;
348 ---------------------------------------------------------------------------
349 -- FUNCTION get_rec for: OKL_SUBSIDIES_TL
350 ---------------------------------------------------------------------------
351 FUNCTION get_rec (
352 p_subt_rec IN subt_rec_type,
353 x_no_data_found OUT NOCOPY BOOLEAN
354 ) RETURN subt_rec_type IS
355 CURSOR okl_subsidies_tl_pk_csr (p_id IN NUMBER,
356 p_language IN VARCHAR2) IS
357 SELECT
358 ID,
359 SHORT_DESCRIPTION,
360 DESCRIPTION,
361 LANGUAGE,
362 SOURCE_LANG,
363 SFWT_FLAG,
364 CREATED_BY,
365 CREATION_DATE,
366 LAST_UPDATED_BY,
367 LAST_UPDATE_DATE,
368 LAST_UPDATE_LOGIN
369 FROM Okl_Subsidies_Tl
370 WHERE okl_subsidies_tl.id = p_id
371 AND okl_subsidies_tl.language = p_language;
372 l_okl_subsidies_tl_pk okl_subsidies_tl_pk_csr%ROWTYPE;
373 l_subt_rec subt_rec_type;
374 BEGIN
375 x_no_data_found := TRUE;
376 -- Get current database values
377 OPEN okl_subsidies_tl_pk_csr (p_subt_rec.id,
378 p_subt_rec.language);
379 FETCH okl_subsidies_tl_pk_csr INTO
380 l_subt_rec.id,
381 l_subt_rec.short_description,
382 l_subt_rec.description,
383 l_subt_rec.language,
384 l_subt_rec.source_lang,
385 l_subt_rec.sfwt_flag,
386 l_subt_rec.created_by,
387 l_subt_rec.creation_date,
388 l_subt_rec.last_updated_by,
389 l_subt_rec.last_update_date,
390 l_subt_rec.last_update_login;
391 x_no_data_found := okl_subsidies_tl_pk_csr%NOTFOUND;
392 CLOSE okl_subsidies_tl_pk_csr;
393 RETURN(l_subt_rec);
394 END get_rec;
395
396 ------------------------------------------------------------------
397 -- This version of get_rec sets error messages if no data found --
398 ------------------------------------------------------------------
399 FUNCTION get_rec (
400 p_subt_rec IN subt_rec_type,
401 x_return_status OUT NOCOPY VARCHAR2
402 ) RETURN subt_rec_type IS
403 l_subt_rec subt_rec_type;
404 l_row_notfound BOOLEAN := TRUE;
405 BEGIN
406 x_return_status := OKL_API.G_RET_STS_SUCCESS;
407 l_subt_rec := get_rec(p_subt_rec, l_row_notfound);
408 IF (l_row_notfound) THEN
409 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
410 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'LANGUAGE');
411 x_return_status := OKL_API.G_RET_STS_ERROR;
412 END IF;
413 RETURN(l_subt_rec);
414 END get_rec;
415 -----------------------------------------------------------
416 -- So we don't have to pass an "l_row_notfound" variable --
417 -----------------------------------------------------------
418 FUNCTION get_rec (
419 p_subt_rec IN subt_rec_type
420 ) RETURN subt_rec_type IS
421 l_row_not_found BOOLEAN := TRUE;
422 BEGIN
423 RETURN(get_rec(p_subt_rec, l_row_not_found));
424 END get_rec;
425 ---------------------------------------------------------------------------
426 -- FUNCTION get_rec for: OKL_SUBSIDIES_B
427 ---------------------------------------------------------------------------
428 FUNCTION get_rec (
429 p_subb_rec IN subb_rec_type,
430 x_no_data_found OUT NOCOPY BOOLEAN
431 ) RETURN subb_rec_type IS
432 CURSOR okl_subsidies_b_pk_csr (p_id IN NUMBER) IS
433 SELECT
434 ID,
435 OBJECT_VERSION_NUMBER,
436 ORG_ID,
437 NAME,
438 EFFECTIVE_FROM_DATE,
439 EFFECTIVE_TO_DATE,
440 EXPIRE_AFTER_DAYS,
441 CURRENCY_CODE,
442 EXCLUSIVE_YN,
443 APPLICABLE_TO_RELEASE_YN,
444 SUBSIDY_CALC_BASIS,
445 AMOUNT,
446 PERCENT,
447 FORMULA_ID,
448 rate_points,
449 MAXIMUM_TERM,
450 VENDOR_ID,
451 ACCOUNTING_METHOD_CODE,
452 RECOURSE_YN,
453 TERMINATION_REFUND_BASIS,
454 REFUND_FORMULA_ID,
455 STREAM_TYPE_ID,
456 RECEIPT_METHOD_CODE,
457 CUSTOMER_VISIBLE_YN,
458 MAXIMUM_FINANCED_AMOUNT,
459 MAXIMUM_SUBSIDY_AMOUNT,
460 --Start code changes for Subsidy by fmiao on 10/25/2004--
461 TRANSFER_BASIS_CODE,
462 --End code changes for Subsidy by fmiao on 10/25/2004--
463 ATTRIBUTE_CATEGORY,
464 ATTRIBUTE1,
465 ATTRIBUTE2,
466 ATTRIBUTE3,
467 ATTRIBUTE4,
468 ATTRIBUTE5,
469 ATTRIBUTE6,
470 ATTRIBUTE7,
471 ATTRIBUTE8,
472 ATTRIBUTE9,
473 ATTRIBUTE10,
474 ATTRIBUTE11,
475 ATTRIBUTE12,
476 ATTRIBUTE13,
477 ATTRIBUTE14,
478 ATTRIBUTE15,
479 CREATED_BY,
480 CREATION_DATE,
481 LAST_UPDATED_BY,
482 LAST_UPDATE_DATE,
483 LAST_UPDATE_LOGIN,
484 -- sjalasut added new column for subsidy pools enhancement. start
485 SUBSIDY_POOL_ID
486 -- sjalasut added new column for subsidy pools enhancement. end
487 FROM Okl_Subsidies_B
488 WHERE okl_subsidies_b.id = p_id;
489 l_okl_subsidies_b_pk okl_subsidies_b_pk_csr%ROWTYPE;
490 l_subb_rec subb_rec_type;
491 BEGIN
492 x_no_data_found := TRUE;
493 -- Get current database values
494 OPEN okl_subsidies_b_pk_csr (p_subb_rec.id);
495 FETCH okl_subsidies_b_pk_csr INTO
496 l_subb_rec.id,
497 l_subb_rec.object_version_number,
498 l_subb_rec.org_id,
499 l_subb_rec.name,
500 l_subb_rec.effective_from_date,
501 l_subb_rec.effective_to_date,
502 l_subb_rec.expire_after_days,
503 l_subb_rec.currency_code,
504 l_subb_rec.exclusive_yn,
505 l_subb_rec.applicable_to_release_yn,
506 l_subb_rec.subsidy_calc_basis,
507 l_subb_rec.amount,
508 l_subb_rec.percent,
509 l_subb_rec.formula_id,
510 l_subb_rec.rate_points,
511 l_subb_rec.maximum_term,
512 l_subb_rec.vendor_id,
513 l_subb_rec.accounting_method_code,
514 l_subb_rec.recourse_yn,
515 l_subb_rec.termination_refund_basis,
516 l_subb_rec.refund_formula_id,
517 l_subb_rec.stream_type_id,
518 l_subb_rec.receipt_method_code,
519 l_subb_rec.customer_visible_yn,
520 l_subb_rec.maximum_financed_amount,
521 l_subb_rec.maximum_subsidy_amount,
522 --Start code changes for Subsidy by fmiao on 10/25/2004--
523 l_subb_rec.transfer_basis_code,
524 --End code changes for Subsidy by fmiao on 10/25/2004--
525 l_subb_rec.attribute_category,
526 l_subb_rec.attribute1,
527 l_subb_rec.attribute2,
528 l_subb_rec.attribute3,
529 l_subb_rec.attribute4,
530 l_subb_rec.attribute5,
531 l_subb_rec.attribute6,
532 l_subb_rec.attribute7,
533 l_subb_rec.attribute8,
534 l_subb_rec.attribute9,
535 l_subb_rec.attribute10,
536 l_subb_rec.attribute11,
537 l_subb_rec.attribute12,
538 l_subb_rec.attribute13,
539 l_subb_rec.attribute14,
540 l_subb_rec.attribute15,
541 l_subb_rec.created_by,
542 l_subb_rec.creation_date,
543 l_subb_rec.last_updated_by,
544 l_subb_rec.last_update_date,
545 l_subb_rec.last_update_login,
546 -- sjalasut added new column for subsidy pools enhancement. start
547 l_subb_rec.subsidy_pool_id;
548 -- sjalasut added new column for subsidy pools enhancement. end
549 x_no_data_found := okl_subsidies_b_pk_csr%NOTFOUND;
550 CLOSE okl_subsidies_b_pk_csr;
551 RETURN(l_subb_rec);
552 END get_rec;
553
554 ------------------------------------------------------------------
555 -- This version of get_rec sets error messages if no data found --
556 ------------------------------------------------------------------
557 FUNCTION get_rec (
558 p_subb_rec IN subb_rec_type,
559 x_return_status OUT NOCOPY VARCHAR2
560 ) RETURN subb_rec_type IS
561 l_subb_rec subb_rec_type;
562 l_row_notfound BOOLEAN := TRUE;
563 BEGIN
564 x_return_status := OKL_API.G_RET_STS_SUCCESS;
565 l_subb_rec := get_rec(p_subb_rec, l_row_notfound);
566 IF (l_row_notfound) THEN
567 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
568 x_return_status := OKL_API.G_RET_STS_ERROR;
569 END IF;
570 RETURN(l_subb_rec);
571 END get_rec;
572 -----------------------------------------------------------
573 -- So we don't have to pass an "l_row_notfound" variable --
574 -----------------------------------------------------------
575 FUNCTION get_rec (
576 p_subb_rec IN subb_rec_type
577 ) RETURN subb_rec_type IS
578 l_row_not_found BOOLEAN := TRUE;
579 BEGIN
580 RETURN(get_rec(p_subb_rec, l_row_not_found));
581 END get_rec;
582 ---------------------------------------------------------------------------
583 -- FUNCTION null_out_defaults for: OKL_SUBSIDIES_V
584 ---------------------------------------------------------------------------
585 FUNCTION null_out_defaults (
586 p_subv_rec IN subv_rec_type
587 ) RETURN subv_rec_type IS
588 l_subv_rec subv_rec_type := p_subv_rec;
589 BEGIN
590 IF (l_subv_rec.id = OKL_API.G_MISS_NUM ) THEN
591 l_subv_rec.id := NULL;
592 END IF;
593 IF (l_subv_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
594 l_subv_rec.object_version_number := NULL;
595 END IF;
596 IF (l_subv_rec.sfwt_flag = OKL_API.G_MISS_CHAR ) THEN
597 l_subv_rec.sfwt_flag := NULL;
598 END IF;
599 IF (l_subv_rec.org_id = OKL_API.G_MISS_NUM ) THEN
600 l_subv_rec.org_id := NULL;
601 END IF;
602 IF (l_subv_rec.name = OKL_API.G_MISS_CHAR ) THEN
603 l_subv_rec.name := NULL;
604 END IF;
605 IF (l_subv_rec.short_description = OKL_API.G_MISS_CHAR ) THEN
606 l_subv_rec.short_description := NULL;
607 END IF;
608 IF (l_subv_rec.description = OKL_API.G_MISS_CHAR ) THEN
609 l_subv_rec.description := NULL;
610 END IF;
611 IF (l_subv_rec.effective_from_date = OKL_API.G_MISS_DATE ) THEN
612 l_subv_rec.effective_from_date := NULL;
613 END IF;
614 IF (l_subv_rec.effective_to_date = OKL_API.G_MISS_DATE ) THEN
615 l_subv_rec.effective_to_date := NULL;
616 END IF;
617 IF (l_subv_rec.expire_after_days = OKL_API.G_MISS_NUM ) THEN
618 l_subv_rec.expire_after_days := NULL;
619 END IF;
620 IF (l_subv_rec.currency_code = OKL_API.G_MISS_CHAR ) THEN
621 l_subv_rec.currency_code := NULL;
622 END IF;
623 IF (l_subv_rec.exclusive_yn = OKL_API.G_MISS_CHAR ) THEN
624 l_subv_rec.exclusive_yn := NULL;
625 END IF;
626 IF (l_subv_rec.applicable_to_release_yn = OKL_API.G_MISS_CHAR ) THEN
627 l_subv_rec.applicable_to_release_yn := NULL;
628 END IF;
629 IF (l_subv_rec.subsidy_calc_basis = OKL_API.G_MISS_CHAR ) THEN
630 l_subv_rec.subsidy_calc_basis := NULL;
631 END IF;
632 IF (l_subv_rec.amount = OKL_API.G_MISS_NUM ) THEN
633 l_subv_rec.amount := NULL;
634 END IF;
635 IF (l_subv_rec.percent = OKL_API.G_MISS_NUM ) THEN
636 l_subv_rec.percent := NULL;
637 END IF;
638 IF (l_subv_rec.formula_id = OKL_API.G_MISS_NUM ) THEN
639 l_subv_rec.formula_id := NULL;
640 END IF;
641 IF (l_subv_rec.rate_points = OKL_API.G_MISS_NUM ) THEN
642 l_subv_rec.rate_points := NULL;
643 END IF;
644 IF (l_subv_rec.maximum_term = OKL_API.G_MISS_NUM ) THEN
645 l_subv_rec.maximum_term := NULL;
646 END IF;
647 IF (l_subv_rec.vendor_id = OKL_API.G_MISS_NUM ) THEN
648 l_subv_rec.vendor_id := NULL;
649 END IF;
650 IF (l_subv_rec.accounting_method_code = OKL_API.G_MISS_CHAR ) THEN
651 l_subv_rec.accounting_method_code := NULL;
652 END IF;
653 IF (l_subv_rec.recourse_yn = OKL_API.G_MISS_CHAR ) THEN
654 l_subv_rec.recourse_yn := NULL;
655 END IF;
656 IF (l_subv_rec.termination_refund_basis = OKL_API.G_MISS_CHAR ) THEN
657 l_subv_rec.termination_refund_basis := NULL;
658 END IF;
659 IF (l_subv_rec.refund_formula_id = OKL_API.G_MISS_NUM ) THEN
660 l_subv_rec.refund_formula_id := NULL;
661 END IF;
662 IF (l_subv_rec.stream_type_id = OKL_API.G_MISS_NUM ) THEN
663 l_subv_rec.stream_type_id := NULL;
664 END IF;
665 IF (l_subv_rec.receipt_method_code = OKL_API.G_MISS_CHAR ) THEN
666 l_subv_rec.receipt_method_code := NULL;
667 END IF;
668 IF (l_subv_rec.customer_visible_yn = OKL_API.G_MISS_CHAR ) THEN
669 l_subv_rec.customer_visible_yn := NULL;
670 END IF;
671 IF (l_subv_rec.maximum_financed_amount = OKL_API.G_MISS_NUM ) THEN
672 l_subv_rec.maximum_financed_amount := NULL;
673 END IF;
674 IF (l_subv_rec.maximum_subsidy_amount = OKL_API.G_MISS_NUM ) THEN
675 l_subv_rec.maximum_subsidy_amount := NULL;
676 END IF;
677 --Start code changes for Subsidy by fmiao on 10/25/2004--
678 IF (l_subv_rec.transfer_basis_code = OKL_API.G_MISS_CHAR ) THEN
679 l_subv_rec.transfer_basis_code := NULL;
680 END IF;
681 --End code changes for Subsidy by fmiao on 10/25/2004--
682 IF (l_subv_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
683 l_subv_rec.attribute_category := NULL;
684 END IF;
685 IF (l_subv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
686 l_subv_rec.attribute1 := NULL;
687 END IF;
688 IF (l_subv_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
689 l_subv_rec.attribute2 := NULL;
690 END IF;
691 IF (l_subv_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
692 l_subv_rec.attribute3 := NULL;
693 END IF;
694 IF (l_subv_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
695 l_subv_rec.attribute4 := NULL;
696 END IF;
697 IF (l_subv_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
698 l_subv_rec.attribute5 := NULL;
699 END IF;
700 IF (l_subv_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
701 l_subv_rec.attribute6 := NULL;
702 END IF;
703 IF (l_subv_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
704 l_subv_rec.attribute7 := NULL;
705 END IF;
706 IF (l_subv_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
707 l_subv_rec.attribute8 := NULL;
708 END IF;
709 IF (l_subv_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
710 l_subv_rec.attribute9 := NULL;
711 END IF;
712 IF (l_subv_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
713 l_subv_rec.attribute10 := NULL;
714 END IF;
715 IF (l_subv_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
716 l_subv_rec.attribute11 := NULL;
717 END IF;
718 IF (l_subv_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
719 l_subv_rec.attribute12 := NULL;
720 END IF;
721 IF (l_subv_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
722 l_subv_rec.attribute13 := NULL;
723 END IF;
724 IF (l_subv_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
725 l_subv_rec.attribute14 := NULL;
726 END IF;
727 IF (l_subv_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
728 l_subv_rec.attribute15 := NULL;
729 END IF;
730 IF (l_subv_rec.created_by = OKL_API.G_MISS_NUM ) THEN
731 l_subv_rec.created_by := NULL;
732 END IF;
733 IF (l_subv_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
734 l_subv_rec.creation_date := NULL;
735 END IF;
736 IF (l_subv_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
737 l_subv_rec.last_updated_by := NULL;
738 END IF;
739 IF (l_subv_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
740 l_subv_rec.last_update_date := NULL;
741 END IF;
742 IF (l_subv_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
743 l_subv_rec.last_update_login := NULL;
744 END IF;
745 -- sjalasut added new column for subsidy pools enhancement. start
746 IF (l_subv_rec.subsidy_pool_id = OKL_API.G_MISS_NUM ) THEN
747 l_subv_rec.subsidy_pool_id := NULL;
748 END IF;
749 -- sjalasut added new column for subsidy pools enhancement. end
750 RETURN(l_subv_rec);
751 END null_out_defaults;
752 ---------------------------------
753 -- Validate_Attributes for: ID --
754 ---------------------------------
755 PROCEDURE validate_id(
756 x_return_status OUT NOCOPY VARCHAR2,
757 p_id IN NUMBER) IS
758 BEGIN
759 x_return_status := OKL_API.G_RET_STS_SUCCESS;
760 IF (p_id = OKL_API.G_MISS_NUM OR
761 p_id IS NULL)
762 THEN
763 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
764 x_return_status := OKL_API.G_RET_STS_ERROR;
765 RAISE G_EXCEPTION_HALT_VALIDATION;
766 END IF;
767 EXCEPTION
768 WHEN G_EXCEPTION_HALT_VALIDATION THEN
769 null;
770 WHEN OTHERS THEN
771 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
772 ,p_msg_name => G_UNEXPECTED_ERROR
773 ,p_token1 => G_SQLCODE_TOKEN
774 ,p_token1_value => SQLCODE
775 ,p_token2 => G_SQLERRM_TOKEN
776 ,p_token2_value => SQLERRM);
777 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
778 END validate_id;
779 ----------------------------------------------------
780 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
781 ----------------------------------------------------
782 PROCEDURE validate_object_version_number(
783 x_return_status OUT NOCOPY VARCHAR2,
784 p_object_version_number IN NUMBER) IS
785 BEGIN
786 x_return_status := OKL_API.G_RET_STS_SUCCESS;
787 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
788 p_object_version_number IS NULL)
789 THEN
790 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
791 x_return_status := OKL_API.G_RET_STS_ERROR;
792 RAISE G_EXCEPTION_HALT_VALIDATION;
793 END IF;
794 EXCEPTION
795 WHEN G_EXCEPTION_HALT_VALIDATION THEN
796 null;
797 WHEN OTHERS THEN
798 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
799 ,p_msg_name => G_UNEXPECTED_ERROR
800 ,p_token1 => G_SQLCODE_TOKEN
801 ,p_token1_value => SQLCODE
802 ,p_token2 => G_SQLERRM_TOKEN
803 ,p_token2_value => SQLERRM);
804 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
805 END validate_object_version_number;
806 ----------------------------------------
807 -- Validate_Attributes for: SFWT_FLAG --
808 ----------------------------------------
809 PROCEDURE validate_sfwt_flag(
810 x_return_status OUT NOCOPY VARCHAR2,
811 p_sfwt_flag IN VARCHAR2) IS
812 BEGIN
813 x_return_status := OKL_API.G_RET_STS_SUCCESS;
814 IF (p_sfwt_flag = OKL_API.G_MISS_CHAR OR
815 p_sfwt_flag IS NULL)
816 THEN
817 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sfwt_flag');
818 x_return_status := OKL_API.G_RET_STS_ERROR;
819 RAISE G_EXCEPTION_HALT_VALIDATION;
820 END IF;
821 EXCEPTION
822 WHEN G_EXCEPTION_HALT_VALIDATION THEN
823 null;
824 WHEN OTHERS THEN
825 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
826 ,p_msg_name => G_UNEXPECTED_ERROR
827 ,p_token1 => G_SQLCODE_TOKEN
828 ,p_token1_value => SQLCODE
829 ,p_token2 => G_SQLERRM_TOKEN
830 ,p_token2_value => SQLERRM);
831 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
832 END validate_sfwt_flag;
833 -------------------------------------
834 -- Validate_Attributes for: ORG_ID --
835 -------------------------------------
836 PROCEDURE validate_org_id(
837 x_return_status OUT NOCOPY VARCHAR2,
838 p_org_id IN NUMBER) IS
839
840 CURSOR hou_csr (p_org_id in number) IS
841 SELECT 'Y'
842 FROM hr_operating_units hou
843 WHERE hou.organization_id = p_org_id
844 And sysdate between nvl(hou.date_from,sysdate) and nvl(hou.date_to,sysdate);
845
846 l_exists varchar2(1) default 'N';
847
848 BEGIN
849 x_return_status := OKL_API.G_RET_STS_SUCCESS;
850 IF (p_org_id = OKL_API.G_MISS_NUM OR
851 p_org_id IS NULL)
852 THEN
853 --OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'org_id');
854 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Operating Unit');
855 x_return_status := OKL_API.G_RET_STS_ERROR;
856 RAISE G_EXCEPTION_HALT_VALIDATION;
857
858 ELSIF (p_org_id <> OKL_API.G_MISS_NUM AND
859 p_org_id IS NOT NULL)
860 THEN
861 l_exists := 'N';
862 Open hou_csr (p_org_id => p_org_id);
863 Fetch hou_csr into l_exists;
864 If hou_csr%NOTFOUND then
865 Null;
866 End If;
867 Close hou_csr;
868 IF l_exists = 'N' then
869 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Operating Unit');
870 x_return_status := OKL_API.G_RET_STS_ERROR;
871 RAISE G_EXCEPTION_HALT_VALIDATION;
872 END IF;
873 END IF;
874 EXCEPTION
875 WHEN G_EXCEPTION_HALT_VALIDATION THEN
876 null;
877 WHEN OTHERS THEN
878 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
879 ,p_msg_name => G_UNEXPECTED_ERROR
880 ,p_token1 => G_SQLCODE_TOKEN
881 ,p_token1_value => SQLCODE
882 ,p_token2 => G_SQLERRM_TOKEN
883 ,p_token2_value => SQLERRM);
884 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
885 END validate_org_id;
886 -----------------------------------
887 -- Validate_Attributes for: NAME --
888 -----------------------------------
889 PROCEDURE validate_name(
890 x_return_status OUT NOCOPY VARCHAR2,
891 p_name IN VARCHAR2) IS
892 BEGIN
893 x_return_status := OKL_API.G_RET_STS_SUCCESS;
894 IF (p_name = OKL_API.G_MISS_CHAR OR
895 p_name IS NULL)
896 THEN
897 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Subsidy Name');
898 x_return_status := OKL_API.G_RET_STS_ERROR;
899 RAISE G_EXCEPTION_HALT_VALIDATION;
900 END IF;
901 EXCEPTION
902 WHEN G_EXCEPTION_HALT_VALIDATION THEN
903 null;
904 WHEN OTHERS THEN
905 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
906 ,p_msg_name => G_UNEXPECTED_ERROR
907 ,p_token1 => G_SQLCODE_TOKEN
908 ,p_token1_value => SQLCODE
909 ,p_token2 => G_SQLERRM_TOKEN
910 ,p_token2_value => SQLERRM);
911 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
912 END validate_name;
913
914 --cklee: start
915 -----------------------------------
916 -- Validate_Attributes for: NAME --
917 -----------------------------------
918 PROCEDURE validate_name_uniqueness(
919 x_return_status OUT NOCOPY VARCHAR2,
920 p_name IN VARCHAR2,
921 p_id IN NUMBER) IS
922
923 cursor c_record_exists is
924 select 1
925 from okl_subsidies_b sub
926 where sub.id = p_id
927 -- abindal start bug# 4873705 --
928 and sub.org_id = mo_global.get_current_org_id();
929 -- abindal end bug# 4873705 --
930
931 cursor c_unique_insert is
932 select 1
933 from okl_subsidies_b sub
934 where sub.name = p_name
935 -- abindal start bug# 4873705 --
936 and sub.org_id = mo_global.get_current_org_id();
937 -- abindal end bug# 4873705 --
938
939 cursor c_unique_update is
940 select 1
941 from okl_subsidies_b sub
942 where sub.id <> p_id
943 and sub.name = p_name
944 -- abindal start bug# 4873705 --
945 and sub.org_id = mo_global.get_current_org_id();
946 -- abindal end bug# 4873705 --
947
948 l_dup_row_found boolean;
949 l_row_found boolean;
950 l_dummy number;
951
952 BEGIN
953 x_return_status := OKL_API.G_RET_STS_SUCCESS;
954
955 -- 1. If it's a new ID but has not insert into DB yet
956 -- 2. If it's a g_miss_num (update mode must have ID)
957 open c_record_exists;
958 fetch c_record_exists into l_dummy;
959 l_row_found := c_record_exists%found;
960 close c_record_exists;
961
962 -- update mode
963 IF l_row_found THEN
964 open c_unique_update;
965 fetch c_unique_update into l_dummy;
966 l_dup_row_found := c_unique_update%found;
967 close c_unique_update;
968
969 ELSE -- insert mode
970 open c_unique_insert;
971 fetch c_unique_insert into l_dummy;
972 l_dup_row_found := c_unique_insert%found;
973 close c_unique_insert;
974 END IF;
975
976 IF l_dup_row_found THEN
977 OKL_API.set_message(G_APP_NAME, G_NOT_UNIQUE, G_COL_NAME_TOKEN, 'Subsidy Name');
978 x_return_status := OKL_API.G_RET_STS_ERROR;
979 RAISE G_EXCEPTION_HALT_VALIDATION;
980 END IF;
981
982 EXCEPTION
983 WHEN G_EXCEPTION_HALT_VALIDATION THEN
984 null;
985 WHEN OTHERS THEN
986 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
987 ,p_msg_name => G_UNEXPECTED_ERROR
988 ,p_token1 => G_SQLCODE_TOKEN
989 ,p_token1_value => SQLCODE
990 ,p_token2 => G_SQLERRM_TOKEN
991 ,p_token2_value => SQLERRM);
992 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
993 END validate_name_uniqueness;
994 --cklee: end
995
996 --------------------------------------------------
997 -- Validate_Attributes for: EFFECTIVE_FROM_DATE --
998 --------------------------------------------------
999 PROCEDURE validate_effective_from_date(
1000 x_return_status OUT NOCOPY VARCHAR2,
1001 p_effective_from_date IN DATE) IS
1002 BEGIN
1003 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1004 IF (p_effective_from_date = OKL_API.G_MISS_DATE OR
1005 p_effective_from_date IS NULL)
1006 THEN
1007 --OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'effective_from_date');
1008 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Effective From');
1009 x_return_status := OKL_API.G_RET_STS_ERROR;
1010 RAISE G_EXCEPTION_HALT_VALIDATION;
1011 END IF;
1012 EXCEPTION
1013 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1014 null;
1015 WHEN OTHERS THEN
1016 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1017 ,p_msg_name => G_UNEXPECTED_ERROR
1018 ,p_token1 => G_SQLCODE_TOKEN
1019 ,p_token1_value => SQLCODE
1020 ,p_token2 => G_SQLERRM_TOKEN
1021 ,p_token2_value => SQLERRM);
1022 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1023 END validate_effective_from_date;
1024 --------------------------------------------
1025 --Start of Hand Coded Attribute Validations
1026 ---------------------------------------------
1027
1028 --------------------------------------------------
1029 -- Validate_Attributes for: EXPIRE_AFTER_DAYS --
1030 --------------------------------------------------
1031 PROCEDURE validate_expire_after_days(
1032 x_return_status OUT NOCOPY VARCHAR2,
1033 p_expire_after_days IN NUMBER) IS
1034 BEGIN
1035 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1036 IF (p_expire_after_days = OKL_API.G_MISS_NUM OR
1037 p_expire_after_days IS NULL)
1038 THEN
1039 --OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'effective_from_date');
1040 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Expire After Days');
1041 x_return_status := OKL_API.G_RET_STS_ERROR;
1042 RAISE G_EXCEPTION_HALT_VALIDATION;
1043 END IF;
1044 EXCEPTION
1045 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1046 null;
1047 WHEN OTHERS THEN
1048 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1049 ,p_msg_name => G_UNEXPECTED_ERROR
1050 ,p_token1 => G_SQLCODE_TOKEN
1051 ,p_token1_value => SQLCODE
1052 ,p_token2 => G_SQLERRM_TOKEN
1053 ,p_token2_value => SQLERRM);
1054 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1055 END validate_expire_after_days;
1056
1057 -------------------------------------------------
1058 -- Validate_Attributes for: SUBSIDY_CALC_BASIS --
1059 -------------------------------------------------
1060 PROCEDURE validate_subsidy_calc_basis(
1061 x_return_status OUT NOCOPY VARCHAR2,
1062 p_subsidy_calc_basis IN VARCHAR2) IS
1063
1064 CURSOR flk_csr (p_lookup_type IN varchar2,p_lookup_code IN VARCHAR2) IS
1065 SELECT 'Y'
1066 FROM Fnd_Lookups flk
1067 WHERE flk.lookup_code = p_lookup_code
1068 And flk.lookup_type = p_lookup_type
1069 And flk.enabled_flag = 'Y'
1070 And sysdate between nvl(flk.start_date_active,sysdate) and nvl(flk.end_date_active,sysdate);
1071 l_exists varchar2(1) default 'N';
1072 BEGIN
1073 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1074 IF (p_subsidy_calc_basis = OKL_API.G_MISS_CHAR OR
1075 p_subsidy_calc_basis IS NULL)
1076 THEN
1077 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Subsidy Calculation Basis');
1078 x_return_status := OKL_API.G_RET_STS_ERROR;
1079 RAISE G_EXCEPTION_HALT_VALIDATION;
1080 ELSIF (p_subsidy_calc_basis <> OKL_API.G_MISS_CHAR AND
1081 p_subsidy_calc_basis IS NOT NULL)
1082 THEN
1083 l_exists := 'N';
1084 Open flk_csr (p_lookup_type => 'OKL_SUBCALC_BASIS',p_lookup_code => p_subsidy_calc_basis);
1085 Fetch flk_csr into l_exists;
1086 If flk_csr%NOTFOUND then
1087 Null;
1088 End If;
1089 Close flk_csr;
1090 IF l_exists = 'N' then
1091 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SUBSIDY_CALC_BASIS');
1092 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy Calculation Basis');
1093 x_return_status := OKL_API.G_RET_STS_ERROR;
1094 RAISE G_EXCEPTION_HALT_VALIDATION;
1095 END IF;
1096 END IF;
1097 EXCEPTION
1098 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1099 null;
1100 WHEN OTHERS THEN
1101 IF flk_csr%ISOPEN then
1102 close flk_csr;
1103 END IF;
1104 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1105 ,p_msg_name => G_UNEXPECTED_ERROR
1106 ,p_token1 => G_SQLCODE_TOKEN
1107 ,p_token1_value => SQLCODE
1108 ,p_token2 => G_SQLERRM_TOKEN
1109 ,p_token2_value => SQLERRM);
1110 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1111 END validate_subsidy_calc_basis;
1112
1113 -------------------------------------------------
1114 -- Validate_Attributes for: SUBSIDY_POOL_ID --
1115 -------------------------------------------------
1116 PROCEDURE validate_subsidy_pool_id(
1117 x_return_status OUT NOCOPY VARCHAR2,
1118 p_subsidy_pool_id IN NUMBER) IS
1119
1120 CURSOR c_get_pool_type_csr IS
1121 SELECT pool_type_code
1122 FROM okl_subsidy_pools_b
1123 WHERE id = p_subsidy_pool_id;
1124 lv_pool_type okl_subsidy_pools_b.pool_type_code%TYPE;
1125 BEGIN
1126 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1127 IF(p_subsidy_pool_id IS NOT NULL)THEN
1128 OPEN c_get_pool_type_csr; FETCH c_get_pool_type_csr INTO lv_pool_type;
1129 CLOSE c_get_pool_type_csr;
1130 IF(lv_pool_type <> 'BUDGET')THEN
1131 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy Pool');
1132 x_return_status := OKL_API.G_RET_STS_ERROR;
1133 RAISE G_EXCEPTION_HALT_VALIDATION;
1134 END IF;
1135 END IF;
1136 EXCEPTION
1137 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1138 null;
1139 WHEN OTHERS THEN
1140 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1141 ,p_msg_name => G_UNEXPECTED_ERROR
1142 ,p_token1 => G_SQLCODE_TOKEN
1143 ,p_token1_value => SQLCODE
1144 ,p_token2 => G_SQLERRM_TOKEN
1145 ,p_token2_value => SQLERRM);
1146 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1147 END validate_subsidy_pool_id;
1148
1149
1150 -----------------------------------------
1151 -- Validate_Attributes for: FORMULA_ID --
1152 -----------------------------------------
1153 PROCEDURE validate_formula_id(
1154 x_return_status OUT NOCOPY VARCHAR2,
1155 p_formula_id IN NUMBER) IS
1156
1157 CURSOR fmlb_csr (p_formula_id IN number) IS
1158 SELECT 'Y'
1159 FROM OKL_FORMULAE_B fmlb
1160 WHERE fmlb.id = p_formula_id
1161 And sysdate between nvl(fmlb.start_date,sysdate) and nvl(fmlb.end_date,sysdate);
1162 l_exists varchar2(1) default 'N';
1163
1164 BEGIN
1165 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1166 IF (p_formula_id <> OKL_API.G_MISS_NUM AND
1167 p_formula_id IS NOT NULL)
1168 THEN
1169 l_exists := 'N';
1170 Open fmlb_csr (p_formula_id => p_formula_id);
1171 Fetch fmlb_csr into l_exists;
1172 If fmlb_csr%NOTFOUND then
1173 Null;
1174 End If;
1175 Close fmlb_csr;
1176 IF l_exists = 'N' then
1177 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'FORMULA_ID');
1178 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy Calculation Formula');
1179 x_return_status := OKL_API.G_RET_STS_ERROR;
1180 RAISE G_EXCEPTION_HALT_VALIDATION;
1181 END IF;
1182 END IF;
1183 EXCEPTION
1184 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1185 null;
1186 WHEN OTHERS THEN
1187 If fmlb_csr%ISOPEN then
1188 close fmlb_csr;
1189 End If;
1190 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1191 ,p_msg_name => G_UNEXPECTED_ERROR
1192 ,p_token1 => G_SQLCODE_TOKEN
1193 ,p_token1_value => SQLCODE
1194 ,p_token2 => G_SQLERRM_TOKEN
1195 ,p_token2_value => SQLERRM);
1196 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1197 END validate_formula_id;
1198 -----------------------------------------------------
1199 -- Validate_Attributes for: ACCOUNTING_METHOD_CODE --
1200 -----------------------------------------------------
1201 PROCEDURE validate_accounting1(
1202 x_return_status OUT NOCOPY VARCHAR2,
1203 p_accounting_method_code IN VARCHAR2) IS
1204
1205 CURSOR flk_csr (p_lookup_type IN varchar2,p_lookup_code IN VARCHAR2) IS
1206 SELECT 'Y'
1207 FROM Fnd_Lookups flk
1208 WHERE flk.lookup_code = p_lookup_code
1209 And flk.lookup_type = p_lookup_type
1210 And flk.enabled_flag = 'Y'
1211 And sysdate between nvl(flk.start_date_active,sysdate) and nvl(flk.end_date_active,sysdate);
1212 l_exists varchar2(1) default 'N';
1213
1214 BEGIN
1215 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1216 IF (p_accounting_method_code = OKL_API.G_MISS_CHAR OR
1217 p_accounting_method_code IS NULL)
1218 THEN
1219 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Accounting Method');
1220 x_return_status := OKL_API.G_RET_STS_ERROR;
1221 RAISE G_EXCEPTION_HALT_VALIDATION;
1222 ELSIF (p_accounting_method_code <> OKL_API.G_MISS_CHAR AND
1223 p_accounting_method_code IS NOT NULL)
1224 THEN
1225 l_exists := 'N';
1226 Open flk_csr (p_lookup_type => 'OKL_SUBACCT_METHOD',p_lookup_code => p_accounting_method_code);
1227 Fetch flk_csr into l_exists;
1228 If flk_csr%NOTFOUND then
1229 Null;
1230 End If;
1231 Close flk_csr;
1232 IF l_exists = 'N' then
1233 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ACCOUNTING_METHOD_CODE');
1234 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Accounting Method');
1235 x_return_status := OKL_API.G_RET_STS_ERROR;
1236 RAISE G_EXCEPTION_HALT_VALIDATION;
1237 END IF;
1238 END IF;
1239 EXCEPTION
1240 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1241 null;
1242 WHEN OTHERS THEN
1243 IF flk_csr%ISOPEN then
1244 close flk_csr;
1245 END IF;
1246
1247 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1248 ,p_msg_name => G_UNEXPECTED_ERROR
1249 ,p_token1 => G_SQLCODE_TOKEN
1250 ,p_token1_value => SQLCODE
1251 ,p_token2 => G_SQLERRM_TOKEN
1252 ,p_token2_value => SQLERRM);
1253 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1254 END validate_accounting1;
1255 -------------------------------------------------------
1256 -- Validate_Attributes for: TERMINATION_REFUND_BASIS --
1257 -------------------------------------------------------
1258 PROCEDURE validate_terminatio3(
1259 x_return_status OUT NOCOPY VARCHAR2,
1260 p_termination_refund_basis IN VARCHAR2) IS
1261 CURSOR flk_csr (p_lookup_type IN varchar2,p_lookup_code IN VARCHAR2) IS
1262 SELECT 'Y'
1263 FROM Fnd_Lookups flk
1264 WHERE flk.lookup_code = p_lookup_code
1265 And flk.lookup_type = p_lookup_type
1266 And flk.enabled_flag = 'Y'
1267 And sysdate between nvl(flk.start_date_active,sysdate) and nvl(flk.end_date_active,sysdate);
1268 l_exists varchar2(1) default 'N';
1269 BEGIN
1270 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1271 IF (p_termination_refund_basis = OKL_API.G_MISS_CHAR OR
1272 p_termination_refund_basis IS NULL)
1273 THEN
1274 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Refund Basis');
1275 x_return_status := OKL_API.G_RET_STS_ERROR;
1276 RAISE G_EXCEPTION_HALT_VALIDATION;
1277
1278 ELSIF (p_termination_refund_basis <> OKL_API.G_MISS_CHAR AND
1279 p_termination_refund_basis IS NOT NULL)
1280 THEN
1281 l_exists := 'N';
1282 Open flk_csr (p_lookup_type => 'OKL_SUBRFND_BASIS',p_lookup_code => p_termination_refund_basis);
1283 Fetch flk_csr into l_exists;
1284 If flk_csr%NOTFOUND then
1285 Null;
1286 End If;
1287 Close flk_csr;
1288 IF l_exists = 'N' then
1289 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TERMINATION_REFUND_BASIS');
1290 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Refund Basis');
1291 x_return_status := OKL_API.G_RET_STS_ERROR;
1292 RAISE G_EXCEPTION_HALT_VALIDATION;
1293 END IF;
1294 END IF;
1295 EXCEPTION
1296 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1297 null;
1298 WHEN OTHERS THEN
1299 IF flk_csr%ISOPEN then
1300 close flk_csr;
1301 END IF;
1302 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1303 ,p_msg_name => G_UNEXPECTED_ERROR
1304 ,p_token1 => G_SQLCODE_TOKEN
1305 ,p_token1_value => SQLCODE
1306 ,p_token2 => G_SQLERRM_TOKEN
1307 ,p_token2_value => SQLERRM);
1308 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1309 END validate_terminatio3;
1310 ------------------------------------------------
1311 -- Validate_Attributes for: REFUND_FORMULA_ID --
1312 ------------------------------------------------
1313 PROCEDURE validate_refund_formula_id(
1314 x_return_status OUT NOCOPY VARCHAR2,
1315 p_refund_formula_id IN NUMBER) IS
1316
1317 CURSOR fmlb_csr (p_formula_id IN number) IS
1318 SELECT 'Y'
1319 FROM OKL_FORMULAE_B fmlb
1320 WHERE fmlb.id = p_formula_id
1321 And sysdate between nvl(fmlb.start_date,sysdate) and nvl(fmlb.end_date,sysdate);
1322 l_exists varchar2(1) default 'N';
1323
1324 BEGIN
1325 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1326 IF (p_refund_formula_id <> OKL_API.G_MISS_NUM AND
1327 p_refund_formula_id IS NOT NULL)
1328 THEN
1329 l_exists := 'N';
1330 Open fmlb_csr (p_formula_id => p_refund_formula_id);
1331 Fetch fmlb_csr into l_exists;
1332 If fmlb_csr%NOTFOUND then
1333 Null;
1334 End If;
1335 Close fmlb_csr;
1336 IF l_exists = 'N' then
1337 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'REFUND_FORMULA_ID');
1338 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Refund Formula');
1339 x_return_status := OKL_API.G_RET_STS_ERROR;
1340 RAISE G_EXCEPTION_HALT_VALIDATION;
1341 END IF;
1342 END IF;
1343 EXCEPTION
1344 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1345 null;
1346 WHEN OTHERS THEN
1347 If fmlb_csr%ISOPEN then
1348 close fmlb_csr;
1349 End If;
1350
1351 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1352 ,p_msg_name => G_UNEXPECTED_ERROR
1353 ,p_token1 => G_SQLCODE_TOKEN
1354 ,p_token1_value => SQLCODE
1355 ,p_token2 => G_SQLERRM_TOKEN
1356 ,p_token2_value => SQLERRM);
1357 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1358 END validate_refund_formula_id;
1359 ---------------------------------------------
1360 -- Validate_Attributes for: STREAM_TYPE_ID --
1361 ---------------------------------------------
1362 PROCEDURE validate_stream_type_id(
1363 x_return_status OUT NOCOPY VARCHAR2,
1364 p_stream_type_id IN NUMBER) IS
1365 -- sjalasut, changed the cursor to exclude stream type class
1366 -- and included stream type purpose as part of bug 3985580.
1367 CURSOR styb_csr (p_stream_type_id IN number) IS
1368 SELECT 'Y'
1369 FROM OKL_STRM_TYPE_B styb,
1370 FND_LOOKUPS lkup
1371 WHERE styb.id = p_stream_type_id
1372 AND sysdate between nvl(styb.start_date,sysdate) and nvl(styb.end_date,sysdate)
1373 AND lkup.lookup_code = styb.STREAM_TYPE_PURPOSE
1374 AND lkup.lookup_type = 'OKL_STREAM_TYPE_PURPOSE';
1375 --And styb.stream_type_class = 'SUBSIDY';
1376 l_exists varchar2(1) default 'N';
1377
1378 BEGIN
1379 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1380 IF (p_stream_type_id = OKL_API.G_MISS_NUM OR
1381 p_stream_type_id IS NULL)
1382 THEN
1383 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Stream Type');
1384 x_return_status := OKL_API.G_RET_STS_ERROR;
1385 RAISE G_EXCEPTION_HALT_VALIDATION;
1386
1387 ELSIF (p_stream_type_id <> OKL_API.G_MISS_NUM AND
1388 p_stream_type_id IS NOT NULL)
1389 THEN
1390 l_exists := 'N';
1391 Open styb_csr (p_stream_type_id => p_stream_type_id);
1392 Fetch styb_csr into l_exists;
1393 If styb_csr%NOTFOUND then
1394 Null;
1395 End If;
1396 Close styb_csr;
1397 IF l_exists = 'N' then
1398 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'STREAM_TYPE_ID');
1399 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Stream Type');
1400 x_return_status := OKL_API.G_RET_STS_ERROR;
1401 RAISE G_EXCEPTION_HALT_VALIDATION;
1402 END IF;
1403 END IF;
1404 EXCEPTION
1405 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1406 null;
1407 WHEN OTHERS THEN
1408 If styb_csr%ISOPEN then
1409 close styb_csr;
1410 End If;
1411
1412 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1413 ,p_msg_name => G_UNEXPECTED_ERROR
1414 ,p_token1 => G_SQLCODE_TOKEN
1415 ,p_token1_value => SQLCODE
1416 ,p_token2 => G_SQLERRM_TOKEN
1417 ,p_token2_value => SQLERRM);
1418 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1419 END validate_stream_type_id;
1420 --------------------------------------------------
1421 -- Validate_Attributes for: RECEIPT_METHOD_CODE --
1422 --------------------------------------------------
1423 PROCEDURE validate_receipt_method_code(
1424 x_return_status OUT NOCOPY VARCHAR2,
1425 p_receipt_method_code IN VARCHAR2) IS
1426 CURSOR flk_csr (p_lookup_type IN varchar2,p_lookup_code IN VARCHAR2) IS
1427 SELECT 'Y'
1428 FROM Fnd_Lookups flk
1429 WHERE flk.lookup_code = p_lookup_code
1430 And flk.lookup_type = p_lookup_type
1431 And flk.enabled_flag = 'Y'
1432 And sysdate between nvl(flk.start_date_active,sysdate) and nvl(flk.end_date_active,sysdate);
1433 l_exists varchar2(1) default 'N';
1434 BEGIN
1435 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1436 IF (p_receipt_method_code = OKL_API.G_MISS_CHAR OR
1437 p_receipt_method_code IS NULL)
1438 THEN
1439 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Net on Funding');
1440 x_return_status := OKL_API.G_RET_STS_ERROR;
1441 RAISE G_EXCEPTION_HALT_VALIDATION;
1442
1443 ELSIF (p_receipt_method_code <> OKL_API.G_MISS_CHAR AND
1444 p_receipt_method_code IS NOT NULL)
1445 THEN
1446 l_exists := 'N';
1447 Open flk_csr (p_lookup_type => 'OKL_SUBRCPT_METHOD',p_lookup_code => p_receipt_method_code);
1448 Fetch flk_csr into l_exists;
1449 If flk_csr%NOTFOUND then
1450 Null;
1451 End If;
1452 Close flk_csr;
1453 IF l_exists = 'N' then
1454 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RECEIPT_METHOD_CODE');
1455 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Net on Funding');
1456 x_return_status := OKL_API.G_RET_STS_ERROR;
1457 RAISE G_EXCEPTION_HALT_VALIDATION;
1458 END IF;
1459 END IF;
1460 EXCEPTION
1461 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1462 null;
1463 WHEN OTHERS THEN
1464 IF flk_csr%ISOPEN then
1465 close flk_csr;
1466 END IF;
1467 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1468 ,p_msg_name => G_UNEXPECTED_ERROR
1469 ,p_token1 => G_SQLCODE_TOKEN
1470 ,p_token1_value => SQLCODE
1471 ,p_token2 => G_SQLERRM_TOKEN
1472 ,p_token2_value => SQLERRM);
1473 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1474 END validate_receipt_method_code;
1475
1476 --------------------------------------------------
1477 -- Validate_Attributes for: EXCLUSIVE_YN --
1478 --------------------------------------------------
1479 PROCEDURE validate_exclusive_yn(
1480 x_return_status OUT NOCOPY VARCHAR2,
1481 p_exclusive_yn IN VARCHAR2) IS
1482 BEGIN
1483 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1484 IF (p_exclusive_yn = OKL_API.G_MISS_CHAR OR
1485 p_exclusive_yn IS NULL)
1486 THEN
1487
1488 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Exclusive');
1489 x_return_status := OKL_API.G_RET_STS_ERROR;
1490 RAISE G_EXCEPTION_HALT_VALIDATION;
1491
1492 ELSIF (p_exclusive_yn <> OKL_API.G_MISS_CHAR AND
1493 p_exclusive_yn IS NOT NULL)
1494 THEN
1495 If p_exclusive_yn not in ('Y','N') then
1496 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Exclusive');
1497 x_return_status := OKL_API.G_RET_STS_ERROR;
1498 RAISE G_EXCEPTION_HALT_VALIDATION;
1499 END IF;
1500 END IF;
1501 EXCEPTION
1502 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1503 null;
1504 WHEN OTHERS THEN
1505 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1506 ,p_msg_name => G_UNEXPECTED_ERROR
1507 ,p_token1 => G_SQLCODE_TOKEN
1508 ,p_token1_value => SQLCODE
1509 ,p_token2 => G_SQLERRM_TOKEN
1510 ,p_token2_value => SQLERRM);
1511 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1512 END validate_exclusive_yn;
1513
1514 --------------------------------------------------
1515 -- Validate_Attributes for: APPLICABLE_TO_RELEASE_YN --
1516 --------------------------------------------------
1517 PROCEDURE validate_release_yn(
1518 x_return_status OUT NOCOPY VARCHAR2,
1519 p_applicable_to_release_yn IN VARCHAR2) IS
1520 BEGIN
1521 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1522 IF (p_applicable_to_release_yn = OKL_API.G_MISS_CHAR OR
1523 p_applicable_to_release_yn IS NULL)
1524 THEN
1525
1526 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Available on Release');
1527 x_return_status := OKL_API.G_RET_STS_ERROR;
1528 RAISE G_EXCEPTION_HALT_VALIDATION;
1529 ELSIF (p_applicable_to_release_yn <> OKL_API.G_MISS_CHAR AND
1530 p_applicable_to_release_yn IS NOT NULL)
1531 THEN
1532 If p_applicable_to_release_yn not in ('Y','N') then
1533 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'APPLICABLE_TO_RELEASE_YN');
1534 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'vailable on Release');
1535 x_return_status := OKL_API.G_RET_STS_ERROR;
1536 RAISE G_EXCEPTION_HALT_VALIDATION;
1537 END IF;
1538 END IF;
1539 EXCEPTION
1540 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1541 null;
1542 WHEN OTHERS THEN
1543 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1544 ,p_msg_name => G_UNEXPECTED_ERROR
1545 ,p_token1 => G_SQLCODE_TOKEN
1546 ,p_token1_value => SQLCODE
1547 ,p_token2 => G_SQLERRM_TOKEN
1548 ,p_token2_value => SQLERRM);
1549 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1550 END validate_release_yn;
1551
1552 --------------------------------------------------
1553 -- Validate_Attributes for: RECOURSE_YN --
1554 --------------------------------------------------
1555 PROCEDURE validate_recourse_yn(
1556 x_return_status OUT NOCOPY VARCHAR2,
1557 p_recourse_yn IN VARCHAR2) IS
1558 BEGIN
1559 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1560 IF (p_recourse_yn = OKL_API.G_MISS_CHAR OR
1561 p_recourse_yn IS NULL)
1562 THEN
1563
1564 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Recourse');
1565 x_return_status := OKL_API.G_RET_STS_ERROR;
1566 RAISE G_EXCEPTION_HALT_VALIDATION;
1567 ELSIF (p_recourse_yn <> OKL_API.G_MISS_CHAR AND
1568 p_recourse_yn IS NOT NULL)
1569 THEN
1570 If p_recourse_yn not in ('Y','N') then
1571 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RECOURSE_YN');
1572 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Recourse');
1573 x_return_status := OKL_API.G_RET_STS_ERROR;
1574 RAISE G_EXCEPTION_HALT_VALIDATION;
1575 END IF;
1576 END IF;
1577 EXCEPTION
1578 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1579 null;
1580 WHEN OTHERS THEN
1581 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1582 ,p_msg_name => G_UNEXPECTED_ERROR
1583 ,p_token1 => G_SQLCODE_TOKEN
1584 ,p_token1_value => SQLCODE
1585 ,p_token2 => G_SQLERRM_TOKEN
1586 ,p_token2_value => SQLERRM);
1587 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1588 END validate_recourse_yn;
1589
1590 --------------------------------------------------
1591 -- Validate_Attributes for: CUSTOMER_VISIBLE_YN --
1592 --------------------------------------------------
1593 PROCEDURE validate_customer_visible_yn(
1594 x_return_status OUT NOCOPY VARCHAR2,
1595 p_customer_visible_yn IN VARCHAR2) IS
1596 BEGIN
1597 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1598 IF (p_customer_visible_yn = OKL_API.G_MISS_CHAR OR
1599 p_customer_visible_yn IS NULL)
1600 THEN
1601
1602 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Visible to Customer');
1603 x_return_status := OKL_API.G_RET_STS_ERROR;
1604 RAISE G_EXCEPTION_HALT_VALIDATION;
1605 ELSIF (p_customer_visible_yn <> OKL_API.G_MISS_CHAR AND
1606 p_customer_visible_yn IS NOT NULL)
1607 THEN
1608 If p_customer_visible_yn not in ('Y','N') then
1609 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CUSTOMER_VISIBLE_YN');
1610 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Visible to Customer');
1611 x_return_status := OKL_API.G_RET_STS_ERROR;
1612 RAISE G_EXCEPTION_HALT_VALIDATION;
1613 END IF;
1614 END IF;
1615 EXCEPTION
1616 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1617 null;
1618 WHEN OTHERS THEN
1619 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1620 ,p_msg_name => G_UNEXPECTED_ERROR
1621 ,p_token1 => G_SQLCODE_TOKEN
1622 ,p_token1_value => SQLCODE
1623 ,p_token2 => G_SQLERRM_TOKEN
1624 ,p_token2_value => SQLERRM);
1625 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1626 END validate_customer_visible_yn;
1627
1628 ------------------------------------------------
1629 -- Validate_Attributes for: VENDOR_ID --
1630 ------------------------------------------------
1631 PROCEDURE validate_vendor_id(
1632 x_return_status OUT NOCOPY VARCHAR2,
1633 p_vendor_id IN NUMBER) IS
1634
1635 CURSOR pov_csr (p_vendor_id IN number) IS
1636 SELECT 'Y'
1637 FROM PO_VENDORS pov
1638 WHERE pov.vendor_id = p_vendor_id;
1639
1640 l_exists varchar2(1) default 'N';
1641
1642 BEGIN
1643 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1644 IF (p_vendor_id <> OKL_API.G_MISS_NUM AND
1645 p_vendor_id IS NOT NULL)
1646 THEN
1647 l_exists := 'N';
1648 Open pov_csr (p_vendor_id => p_vendor_id);
1649 Fetch pov_csr into l_exists;
1650 If pov_csr%NOTFOUND then
1651 Null;
1652 End If;
1653 Close pov_csr;
1654 IF l_exists = 'N' then
1655 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'REFUND_FORMULA_ID');
1656 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Vendor');
1657 x_return_status := OKL_API.G_RET_STS_ERROR;
1658 RAISE G_EXCEPTION_HALT_VALIDATION;
1659 END IF;
1660 END IF;
1661 EXCEPTION
1662 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1663 null;
1664 WHEN OTHERS THEN
1665 If pov_csr%ISOPEN then
1666 close pov_csr;
1667 End If;
1668
1669 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1670 ,p_msg_name => G_UNEXPECTED_ERROR
1671 ,p_token1 => G_SQLCODE_TOKEN
1672 ,p_token1_value => SQLCODE
1673 ,p_token2 => G_SQLERRM_TOKEN
1674 ,p_token2_value => SQLERRM);
1675 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1676 END validate_vendor_id;
1677 -----------------------------------------------
1678 -- Validate_Attributes for: currency_code --
1679 ------------------------------------------------
1680 PROCEDURE validate_currency_code(
1681 x_return_status OUT NOCOPY VARCHAR2,
1682 p_currency_code IN VARCHAR2) IS
1683
1684 CURSOR curr_csr (p_currency_code IN varchar2) IS
1685 SELECT 'Y'
1686 FROM fnd_currencies curr
1687 WHERE curr.currency_code = p_currency_code
1688 AND SYSDATE BETWEEN NVL(curr.START_DATE_ACTIVE,SYSDATE) AND NVL(curr.END_DATE_ACTIVE,SYSDATE)
1689 AND NVL(curr.CURRENCY_FLAG,'N') = 'Y'
1690 AND NVL(curr.ENABLED_FLAG,'N') = 'Y';
1691
1692 l_exists varchar2(1) default 'N';
1693
1694 BEGIN
1695 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1696 IF (p_currency_code is NULL) OR (p_currency_code = OKL_API.G_MISS_CHAR) Then
1697 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Currency');
1698 x_return_status := OKL_API.G_RET_STS_ERROR;
1699 RAISE G_EXCEPTION_HALT_VALIDATION;
1700 ELSIF (p_currency_code <> OKL_API.G_MISS_CHAR AND
1701 p_currency_code IS NOT NULL)
1702 THEN
1703 l_exists := 'N';
1704 Open curr_csr (p_currency_code => p_currency_code);
1705 Fetch curr_csr into l_exists;
1706 If curr_csr%NOTFOUND then
1707 Null;
1708 End If;
1709 Close curr_csr;
1710 IF l_exists = 'N' then
1711 --OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'REFUND_FORMULA_ID');
1712 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Currency');
1713 x_return_status := OKL_API.G_RET_STS_ERROR;
1714 RAISE G_EXCEPTION_HALT_VALIDATION;
1715 END IF;
1716 END IF;
1717 EXCEPTION
1718 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1719 null;
1720 WHEN OTHERS THEN
1721 If curr_csr%ISOPEN then
1722 close curr_csr;
1723 End If;
1724
1725 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1726 ,p_msg_name => G_UNEXPECTED_ERROR
1727 ,p_token1 => G_SQLCODE_TOKEN
1728 ,p_token1_value => SQLCODE
1729 ,p_token2 => G_SQLERRM_TOKEN
1730 ,p_token2_value => SQLERRM);
1731 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1732 END validate_currency_code;
1733 --------------------------------------------
1734 --End of Hand Coded Attribute Validations
1735 ---------------------------------------------
1736 ---------------------------------------------------------------------------
1737 -- FUNCTION Validate_Attributes
1738 ---------------------------------------------------------------------------
1739 ---------------------------------------------
1740 -- Validate_Attributes for:OKL_SUBSIDIES_V --
1741 ---------------------------------------------
1742 FUNCTION Validate_Attributes (
1743 p_subv_rec IN subv_rec_type
1744 ) RETURN VARCHAR2 IS
1745 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1746 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1747 BEGIN
1748 -----------------------------
1749 -- Column Level Validation --
1750 -----------------------------
1751 -- ***
1752 -- id
1753 -- ***
1754 validate_id(x_return_status, p_subv_rec.id);
1755 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1756 l_return_status := x_return_status;
1757 RAISE G_EXCEPTION_HALT_VALIDATION;
1758 END IF;
1759
1760 -- ***
1761 -- object_version_number
1762 -- ***
1763 validate_object_version_number(x_return_status, p_subv_rec.object_version_number);
1764 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1765 l_return_status := x_return_status;
1766 RAISE G_EXCEPTION_HALT_VALIDATION;
1767 END IF;
1768
1769 -- ***
1770 -- sfwt_flag
1771 -- ***
1772 validate_sfwt_flag(x_return_status, p_subv_rec.sfwt_flag);
1773 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1774 l_return_status := x_return_status;
1775 RAISE G_EXCEPTION_HALT_VALIDATION;
1776 END IF;
1777
1778 -- ***
1779 -- org_id
1780 -- ***
1781 validate_org_id(x_return_status, p_subv_rec.org_id);
1782 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1783 l_return_status := x_return_status;
1784 RAISE G_EXCEPTION_HALT_VALIDATION;
1785 END IF;
1786
1787 -- ***
1788 -- name
1789 -- ***
1790 validate_name(x_return_status, p_subv_rec.name);
1791 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1792 l_return_status := x_return_status;
1793 RAISE G_EXCEPTION_HALT_VALIDATION;
1794 END IF;
1795
1796 --cklee: start
1797 -- ***
1798 -- name
1799 -- ***
1800 validate_name_uniqueness(x_return_status, p_subv_rec.name, p_subv_rec.id);
1801 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1802 l_return_status := x_return_status;
1803 RAISE G_EXCEPTION_HALT_VALIDATION;
1804 END IF;
1805 --cklee: end
1806
1807 -- ***
1808 -- effective_from_date
1809 -- ***
1810 validate_effective_from_date(x_return_status, p_subv_rec.effective_from_date);
1811 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1812 l_return_status := x_return_status;
1813 RAISE G_EXCEPTION_HALT_VALIDATION;
1814 END IF;
1815
1816 -- ***
1817 -- expire_after_days
1818 -- ***
1819 validate_expire_after_days(x_return_status, p_subv_rec.expire_after_days);
1820 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1821 l_return_status := x_return_status;
1822 RAISE G_EXCEPTION_HALT_VALIDATION;
1823 END IF;
1824
1825 -- ***
1826 -- subsidy_calc_basis
1827 -- ***
1828 validate_subsidy_calc_basis(x_return_status, p_subv_rec.subsidy_calc_basis);
1829 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1830 l_return_status := x_return_status;
1831 RAISE G_EXCEPTION_HALT_VALIDATION;
1832 END IF;
1833
1834 -- ***
1835 -- subsidy_pool_id
1836 -- ***
1837 validate_subsidy_pool_id(x_return_status, p_subv_rec.subsidy_pool_id);
1838 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1839 l_return_status := x_return_status;
1840 RAISE G_EXCEPTION_HALT_VALIDATION;
1841 END IF;
1842
1843
1844 -- ***
1845 -- formula_id
1846 -- ***
1847 validate_formula_id(x_return_status, p_subv_rec.formula_id);
1848 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1849 l_return_status := x_return_status;
1850 RAISE G_EXCEPTION_HALT_VALIDATION;
1851 END IF;
1852
1853 -- ***
1854 -- accounting_method_code
1855 -- ***
1856 validate_accounting1(x_return_status, p_subv_rec.accounting_method_code);
1857 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1858 l_return_status := x_return_status;
1859 RAISE G_EXCEPTION_HALT_VALIDATION;
1860 END IF;
1861
1862 -- ***
1863 -- termination_refund_basis
1864 -- ***
1865 validate_terminatio3(x_return_status, p_subv_rec.termination_refund_basis);
1866 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1867 l_return_status := x_return_status;
1868 RAISE G_EXCEPTION_HALT_VALIDATION;
1869 END IF;
1870
1871 -- ***
1872 -- refund_formula_id
1873 -- ***
1874 validate_refund_formula_id(x_return_status, p_subv_rec.refund_formula_id);
1875 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1876 l_return_status := x_return_status;
1877 RAISE G_EXCEPTION_HALT_VALIDATION;
1878 END IF;
1879
1880 -- ***
1881 -- stream_type_id
1882 -- ***
1883 validate_stream_type_id(x_return_status, p_subv_rec.stream_type_id);
1884 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1885 l_return_status := x_return_status;
1886 RAISE G_EXCEPTION_HALT_VALIDATION;
1887 END IF;
1888
1889 -- ***
1890 -- receipt_method_code
1891 -- ***
1892 validate_receipt_method_code(x_return_status, p_subv_rec.receipt_method_code);
1893 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1894 l_return_status := x_return_status;
1895 RAISE G_EXCEPTION_HALT_VALIDATION;
1896 END IF;
1897
1898 -- ***
1899 -- exclusive_yn
1900 -- ***
1901 validate_exclusive_yn(x_return_status, p_subv_rec.exclusive_yn);
1902 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1903 l_return_status := x_return_status;
1904 RAISE G_EXCEPTION_HALT_VALIDATION;
1905 END IF;
1906
1907 -- ***
1908 -- applicable_to_release_yn
1909 -- ***
1910 validate_release_yn(x_return_status, p_subv_rec.applicable_to_release_yn);
1911 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1912 l_return_status := x_return_status;
1913 RAISE G_EXCEPTION_HALT_VALIDATION;
1914 END IF;
1915
1916 -- ***
1917 -- recourse_yn
1918 -- ***
1919 validate_recourse_yn(x_return_status, p_subv_rec.recourse_yn);
1920 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1921 l_return_status := x_return_status;
1922 RAISE G_EXCEPTION_HALT_VALIDATION;
1923 END IF;
1924
1925 -- ***
1926 -- customer_visible_yn
1927 -- ***
1928 validate_recourse_yn(x_return_status, p_subv_rec.customer_visible_yn);
1929 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1930 l_return_status := x_return_status;
1931 RAISE G_EXCEPTION_HALT_VALIDATION;
1932 END IF;
1933
1934 -- ***
1935 -- vendor_id
1936 -- ***
1937 validate_vendor_id(x_return_status, p_subv_rec.vendor_id);
1938 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1939 l_return_status := x_return_status;
1940 RAISE G_EXCEPTION_HALT_VALIDATION;
1941 END IF;
1942
1943 -- ***
1944 -- currency_code
1945 -- ***
1946 validate_currency_code(x_return_status, p_subv_rec.currency_code);
1947 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1948 l_return_status := x_return_status;
1949 RAISE G_EXCEPTION_HALT_VALIDATION;
1950 END IF;
1951
1952 RETURN(l_return_status);
1953 EXCEPTION
1954 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1955 RETURN(l_return_status);
1956 WHEN OTHERS THEN
1957 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1958 ,p_msg_name => G_UNEXPECTED_ERROR
1959 ,p_token1 => G_SQLCODE_TOKEN
1960 ,p_token1_value => SQLCODE
1961 ,p_token2 => G_SQLERRM_TOKEN
1962 ,p_token2_value => SQLERRM);
1963 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1964 RETURN(l_return_status);
1965 END Validate_Attributes;
1966 -----------------------------------
1967 --Hand coded validate record proc
1968 -----------------------------------
1969 ---------------------------------------------------------------------------
1970 -- PROCEDURE Validate_Record
1971 ---------------------------------------------------------------------------
1972 -----------------------------------------
1973 -- Validate Record for:OKL_SUBSIDIES_V --
1974 -----------------------------------------
1975 FUNCTION Validate_Record (
1976 p_subv_rec IN subv_rec_type,
1977 p_db_subv_rec IN subv_rec_type
1978 ) RETURN VARCHAR2 IS
1979 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1980 ------------------------------------
1981 -- FUNCTION validate_foreign_keys and other relations --
1982 ------------------------------------
1983 FUNCTION validate_ref_integrity (
1984 p_subv_rec IN subv_rec_type,
1985 p_db_subv_rec IN subv_rec_type
1986 ) RETURN VARCHAR2 IS
1987 violated_ref_integrity EXCEPTION;
1988
1989 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1990 l_row_notfound BOOLEAN := TRUE;
1991
1992 -- sjalasut, added cursors for subsidy pools enhancement. START
1993 CURSOR c_get_pool_sts_csr
1994 -- START: cklee 07/28/05
1995 (p_subsidy_pool_id okl_subsidy_pools_b.id%TYPE)
1996 -- END: cklee 07/28/05
1997 IS
1998 SELECT SUBP.decision_status_code,
1999 -- start: cklee 07/22/2005
2000 FLK7.MEANING
2001 FROM okl_subsidy_pools_b SUBP,
2002 FND_LOOKUPS FLK7
2003 WHERE FLK7.LOOKUP_TYPE = 'OKL_SUBSIDY_POOL_STATUS'
2004 AND FLK7.LOOKUP_CODE = SUBP.DECISION_STATUS_CODE
2005 -- end: cklee 07/22/2005
2006 -- AND SUBP.id = p_subv_rec.subsidy_pool_id;
2007 -- START: cklee 07/28/05
2008 AND SUBP.id = p_subsidy_pool_id;
2009 -- END: cklee 07/28/05
2010 lv_pool_sts okl_subsidy_pools_b.decision_status_code%TYPE;
2011 lv_pool_sts_meaning fnd_lookups.MEANING%TYPE;
2012
2013 CURSOR c_get_pool_dates_csr
2014 -- START: cklee 07/28/05
2015 (p_subsidy_pool_id okl_subsidy_pools_b.id%TYPE)
2016 -- END: cklee 07/28/05
2017 IS
2018 SELECT effective_from_date, effective_to_date
2019 -- start: cklee 07/22/2005
2020 ,SUBSIDY_POOL_NAME
2021 -- end: cklee 07/22/2005
2022 FROM okl_subsidy_pools_b
2023 -- WHERE id = p_subv_rec.subsidy_pool_id;
2024 -- START: cklee 07/28/05
2025 WHERE id = p_subsidy_pool_id;
2026 -- END: cklee 07/28/05
2027 lv_pool_effective_from okl_subsidy_pools_b.effective_from_date%TYPE;
2028 lv_pool_effective_to okl_subsidy_pools_b.effective_to_date%TYPE;
2029 -- start: cklee 07/22/2005
2030 lv_pool_name okl_subsidy_pools_b.subsidy_pool_name%TYPE;
2031 -- end: cklee 07/22/2005
2032
2033 CURSOR c_chk_asset_subsidy_csr IS
2034 SELECT 1
2035 FROM okl_k_lines klines
2036 WHERE klines.subsidy_id = p_subv_rec.id;
2037 lv_asset_count NUMBER;
2038 -- sjalasut, added cursors for subsidy pools enhancement. END
2039
2040 -- 07/21/05 cklee, added cursors for subsidy pools enhancement. START
2041 --un-comment until 08/26/05
2042 -- check if it associate with a Sales Q/Lease App
2043 CURSOR c_chk_asset_sub_sq_la_csr IS
2044 SELECT 1
2045 FROM OKL_COST_ADJUSTMENTS_B
2046 WHERE ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY' AND
2047 ADJUSTMENT_SOURCE_ID = p_subv_rec.id;
2048 --
2049 -- 07/21/05 cklee, added cursors for subsidy pools enhancement. END
2050
2051 BEGIN
2052
2053 l_return_status := OKL_API.G_RET_STS_SUCCESS;
2054 --------------------------------------------------------------------------
2055 --1. Effective from date can not be greater than effective to date
2056 --------------------------------------------------------------------------
2057 IF (p_subv_rec.EFFECTIVE_FROM_DATE <> p_db_subv_rec.EFFECTIVE_FROM_DATE) OR
2058 (p_subv_rec.EFFECTIVE_TO_DATE is not null and
2059 p_subv_rec.EFFECTIVE_TO_DATE <> p_db_subv_rec.EFFECTIVE_TO_DATE)
2060 THEN
2061 IF p_subv_rec.EFFECTIVE_FROM_DATE > nvl(p_subv_rec.EFFECTIVE_TO_DATE,p_subv_rec.EFFECTIVE_FROM_DATE) then
2062 OKL_API.set_message(G_APP_NAME, G_SUBSIDY_INVALID_DATES);
2063 RAISE violated_ref_integrity;
2064 END IF;
2065 END IF;
2066
2067 --------------------------------------------------------------------------
2068 --2. Subsidy calculation basis
2069 --------------------------------------------------------------------------
2070 IF p_subv_rec.subsidy_calc_basis = 'FIXED' then
2071 If p_subv_rec.Amount is null then
2072 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Subsidy Amount');
2073 RAISE violated_ref_integrity;
2074 End If;
2075
2076 ELSIF p_subv_rec.subsidy_calc_basis = 'FORMULA' then
2077 If p_subv_rec.Formula_id is null then
2078 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Subsidy Calculation Formula');
2079 RAISE violated_ref_integrity;
2080 End If;
2081
2082 ELSIF p_subv_rec.subsidy_calc_basis = 'RATE' then
2083 If p_subv_rec.Rate_Points is null then
2084 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Rate Points');
2085 RAISE violated_ref_integrity;
2086 End If;
2087
2088 ELSIF p_subv_rec.subsidy_calc_basis = 'ASSETCOST' then
2089 If p_subv_rec.Percent is null then
2090 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Percent');
2091 RAISE violated_ref_integrity;
2092 End If;
2093 END IF;
2094
2095 --Bug# 3353781 :
2096 --------------------------------------------------------------------------
2097 --3. Net on Funding Vs (Receipt method code)
2098 --------------------------------------------------------------------------
2099 /*-------------Bug Fix# 3353781------------------------------------------
2100 --If p_subv_rec.receipt_method_code = 'FUND' Then
2101 --If p_subv_rec.accounting_method_code <> 'NET' Then
2102 --OKL_API.set_message(p_app_name => G_APP_NAME,
2103 --p_msg_name => G_INVALID_RECEIPT_METHOD
2104 --);
2105 --RAISE violated_ref_integrity;
2106 --End If;
2107 --End If;
2108 -------------------Bug Fix# 3353781---------------------------------------*/
2109 --START:| 12-Sep-2005 cklee Fixed bug#4928690 |
2110 /*commented out the below code for bug 4636697
2111 If p_subv_rec.accounting_method_code = 'NET' Then
2112 If p_subv_rec.receipt_method_code <> 'FUND' Then
2113 OKL_API.set_message(p_app_name => G_APP_NAME,
2114 p_msg_name => G_INVALID_RECEIPT_METHOD
2115 );
2116 RAISE violated_ref_integrity;
2117 End If;
2118 End If;
2119 */
2120 --END:| 12-Sep-2005 cklee Fixed bug#4928690 |
2121
2122 --------------------------------------------------------------------------
2123 --4. Recourse YN Vs(Receipt method code)
2124 --------------------------------------------------------------------------
2125 If p_subv_rec.recourse_yn = 'Y' Then
2126 If p_subv_rec.accounting_method_code = 'NET' Then
2127 OKL_API.set_message(p_app_name => G_APP_NAME,
2128 p_msg_name => G_INVALID_RECOURSE_FLAG
2129 );
2130 RAISE violated_ref_integrity;
2131 End If;
2132 End If;
2133
2134
2135 --------------------------------------------------------------------------
2136 --5. Refund Formula (Termination_Refund_basis)
2137 --------------------------------------------------------------------------
2138 If p_subv_rec.termination_refund_basis = 'FORMULA' Then
2139 -- cklee 12-12-2003 fixed bug#3313766, added p_subv_rec.recourse_yn = 'Y'
2140 If p_subv_rec.recourse_yn = 'Y' AND p_subv_rec.refund_formula_id is NULL Then
2141 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Refund Formula');
2142 RAISE violated_ref_integrity;
2143 End If;
2144 End If;
2145
2146 /*
2147 * sjalasut: jan 24, 05 added code to validate subsidy pool modification and also
2148 * date ranges of subsidy and subsidy pool
2149 */
2150 --------------------------------------------------------------------------
2151 --6. Subsidy Pool (subsidy_pool_id)
2152 --------------------------------------------------------------------------
2153 IF(p_subv_rec.subsidy_pool_id IS NULL AND p_db_subv_rec.subsidy_pool_id IS NOT NULL)THEN
2154 -- this is the case of dissociating a subsidy pool from the subsidy while the pool is not active.
2155 -- check if the earlier pool is not active, raise exception if the pool is active
2156 -- this is an extra cautionary check, in the ui, the subsidy pool field becomes readonly once active.
2157 OPEN c_get_pool_sts_csr
2158 -- START: cklee 07/28/05
2159 (p_db_subv_rec.subsidy_pool_id);
2160 -- END: cklee 07/28/05
2161 FETCH c_get_pool_sts_csr INTO lv_pool_sts, lv_pool_sts_meaning;
2162 CLOSE c_get_pool_sts_csr;
2163 IF(lv_pool_sts = 'ACTIVE')THEN
2164 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_POOL_STATUS,'SUBSIDY',p_subv_rec.name);
2165 RAISE violated_ref_integrity;
2166 END IF;
2167 END IF;
2168 IF(p_subv_rec.subsidy_pool_id IS NOT NULL AND p_db_subv_rec.subsidy_pool_id IS NOT NULL AND
2169 p_subv_rec.subsidy_pool_id <> OKL_API.G_MISS_NUM AND p_subv_rec.subsidy_pool_id <> p_db_subv_rec.subsidy_pool_id)THEN
2170 -- case when the subsidy pool id is being modified to another value from the LOV in the UI.
2171 -- check to see if this subsidy is attached to a valid asset. if attached, raise an error
2172 lv_asset_count := 0;
2173 OPEN c_chk_asset_subsidy_csr; FETCH c_chk_asset_subsidy_csr INTO lv_asset_count;
2174 CLOSE c_chk_asset_subsidy_csr;
2175 IF(lv_asset_count = 1)THEN
2176 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_ATTACH_ASSET_EXIST,'SUBSIDY',p_subv_rec.name);
2177 RAISE violated_ref_integrity;
2178 END IF;
2179 END IF;
2180 IF((p_subv_rec.subsidy_pool_id IS NOT NULL AND p_subv_rec.subsidy_pool_id <> NVL(p_db_subv_rec.subsidy_pool_id,-1))
2181 OR (p_subv_rec.EFFECTIVE_FROM_DATE <> p_db_subv_rec.EFFECTIVE_FROM_DATE) OR (NVL(p_subv_rec.EFFECTIVE_TO_DATE, SYSDATE) <> NVL(p_db_subv_rec.EFFECTIVE_TO_DATE, SYSDATE)))THEN
2182 -- this is the case when the subsidy pool is being modified to a new value or the effective dates on subsidy have been changed
2183 -- validate the date range. subsidy dates and pool dates must overlap
2184 OPEN c_get_pool_dates_csr
2185 -- START: cklee 07/28/05
2186 (p_subv_rec.subsidy_pool_id);
2187 -- END: cklee 07/28/05
2188 FETCH c_get_pool_dates_csr INTO lv_pool_effective_from, lv_pool_effective_to
2189 -- start: cklee 07/22/05
2190 ,lv_pool_name;
2191 -- end: cklee 07/22/05
2192 CLOSE c_get_pool_dates_csr;
2193 -- if either the pool effective from date is not between subsidy dates or subsidy effective from date is not between pool effective dates
2194 -- raise error
2195 IF((nvl(lv_pool_effective_to,OKL_ACCOUNTING_UTIL.g_final_date) < trunc(p_subv_rec.effective_from_date))OR
2196 (nvl(p_subv_rec.EFFECTIVE_TO_DATE,OKL_ACCOUNTING_UTIL.g_final_date) < lv_pool_effective_From)
2197 )THEN
2198 --cklee 09/12/2005 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_POOL_EFFECTIVE_DATES);
2199 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_POOL_EFFECTIVE_DATES,'SUBSIDY',p_subv_rec.name);
2200
2201 RAISE violated_ref_integrity;
2202 END IF;
2203 END IF;
2204 /*
2205 * sjalasut: jan 24, 05 added code to validate subsidy pool modification and also
2206 * date ranges of subsidy and subsidy pool
2207 */
2208 /*
2209 **
2210 cklee : 07/21/2005
2211 1) If pool status is New, then user allows to add/remove to/from pool
2212 2) If pool status is Active and pool is not expired, then user allows to add to pool.
2213 3) If subisdy is not associate with pool and doesn't have existing association
2214 with contract, Sales Quote, or Lease App, then user allows to choose subsidy from Subsidy LOV.
2215 4) Subsidy dates is overlap with pool dates
2216 **
2217 */
2218 /*
2219 * START: cklee: July 22, 05 added code to validate subsidy pool modification
2220 */
2221 -------------------------------------------------------
2222 -------------------------------------------------------
2223 -- create/update a subsidy -- associate/dissociate to/from a pool
2224 -------------------------------------------------------
2225 -------------------------------------------------------
2226 -------------------------------------------------------
2227 -- dissociating a subsidy from a pool
2228 -------------------------------------------------------
2229 IF(p_subv_rec.subsidy_pool_id IS NULL AND p_db_subv_rec.subsidy_pool_id IS NOT NULL)THEN
2230
2231 -------------------------------------------------------
2232 -- If the status is invalid when dissociating a subsidy from a pool
2233 -------------------------------------------------------
2234 OPEN c_get_pool_sts_csr
2235 -- START: cklee 07/28/05
2236 (p_db_subv_rec.subsidy_pool_id);
2237 -- END: cklee 07/28/05
2238 FETCH c_get_pool_sts_csr INTO lv_pool_sts, lv_pool_sts_meaning;
2239 CLOSE c_get_pool_sts_csr;
2240 IF(lv_pool_sts IN ('PENDING', 'REJECTED', 'EXPIRED', 'ACTIVE'))THEN
2241 -- You are not allowed to dissociate a subsidy from a pool if the pool status is STATUS.
2242 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_POOL_DISSOC_STATUS, 'STATUS',lv_pool_sts_meaning);
2243 RAISE violated_ref_integrity;
2244 END IF;
2245
2246 -------------------------------------------------------
2247 -- If the pool is expired when dissociating a subsidy to a pool
2248 -------------------------------------------------------
2249 OPEN c_get_pool_dates_csr
2250 -- START: cklee 07/28/05
2251 (p_db_subv_rec.subsidy_pool_id);
2252 -- END: cklee 07/28/05
2253 FETCH c_get_pool_dates_csr INTO lv_pool_effective_from, lv_pool_effective_to
2254 -- start: cklee 07/22/05
2255 ,lv_pool_name;
2256 -- end: cklee 07/22/05
2257 CLOSE c_get_pool_dates_csr;
2258 IF trunc(nvl(lv_pool_effective_to, sysdate)) < trunc(sysdate) THEN
2259 -- You are not allowed to dissociate a subsidy from a pool if the pool expired.
2260 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_POOL_DISOC_EXP_POOL, 'POOL',lv_pool_name);
2261 RAISE violated_ref_integrity;
2262 END IF;
2263 END IF;
2264
2265 -------------------------------------------------------
2266 -- associating a subsidy to a pool
2267 -------------------------------------------------------
2268 IF(p_subv_rec.subsidy_pool_id IS NOT NULL AND p_db_subv_rec.subsidy_pool_id IS NULL)THEN
2269
2270 -------------------------------------------------------
2271 -- If the status is invalid when associating a subsidy to a pool
2272 -------------------------------------------------------
2273 OPEN c_get_pool_sts_csr
2274 -- START: cklee 07/28/05
2275 (p_subv_rec.subsidy_pool_id);
2276 -- END: cklee 07/28/05
2277 FETCH c_get_pool_sts_csr INTO lv_pool_sts, lv_pool_sts_meaning;
2278 CLOSE c_get_pool_sts_csr;
2279 IF(lv_pool_sts IN ('PENDING', 'REJECTED', 'EXPIRED'))THEN
2280 -- You are not allowed to associate a subsidy to a pool if the pool status is STATUS.
2281 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_POOL_ASSOC_STATUS, 'STATUS',lv_pool_sts_meaning);
2282 RAISE violated_ref_integrity;
2283 END IF;
2284
2285 -------------------------------------------------------
2286 -- If the pool is expired when associating a subsidy to a pool
2287 -------------------------------------------------------
2288 OPEN c_get_pool_dates_csr
2289 -- START: cklee 07/28/05
2290 (p_subv_rec.subsidy_pool_id);
2291 -- END: cklee 07/28/05
2292 FETCH c_get_pool_dates_csr INTO lv_pool_effective_from, lv_pool_effective_to
2293 -- start: cklee 07/22/05
2294 ,lv_pool_name;
2295 -- end: cklee 07/22/05
2296 CLOSE c_get_pool_dates_csr;
2297 IF trunc(nvl(lv_pool_effective_to, sysdate)) < trunc(sysdate) THEN
2298 -- You are not allowed to associate a subsidy to a pool if the pool expired.
2299 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_POOL_ASSOC_EXP_POOL, 'POOL',lv_pool_name);
2300 RAISE violated_ref_integrity;
2301 END IF;
2302
2303 -------------------------------------------------------
2304 -- If there is any existing asset association when associating a subsidy to a pool -- Lease Contract
2305 -------------------------------------------------------
2306 lv_asset_count := 0;
2307 OPEN c_chk_asset_subsidy_csr; FETCH c_chk_asset_subsidy_csr INTO lv_asset_count;
2308 CLOSE c_chk_asset_subsidy_csr;
2309 IF(lv_asset_count = 1)THEN
2310 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_ATTACH_ASSET_EXIST,'SUBSIDY',p_subv_rec.name);
2311 RAISE violated_ref_integrity;
2312 END IF;
2313 --un-comment until 08/26/05
2314 -------------------------------------------------------
2315 -- If there is any existing asset association when associating a subsidy to a pool -- Sales Q/Lease App
2316 -------------------------------------------------------
2317 lv_asset_count := 0;
2318 OPEN c_chk_asset_sub_sq_la_csr; FETCH c_chk_asset_sub_sq_la_csr INTO lv_asset_count;
2319 CLOSE c_chk_asset_sub_sq_la_csr;
2320 IF(lv_asset_count = 1)THEN
2321 OKL_API.set_message(G_APP_NAME,G_SUBSIDY_ATTACH_ASSET_EXIST,'SUBSIDY',p_subv_rec.name);
2322 RAISE violated_ref_integrity;
2323 END IF;
2324 --
2325 END IF;
2326
2327 /*
2328 * END: cklee: July 22, 05 added code to validate subsidy pool modification
2329 */
2330
2331 RETURN (l_return_status);
2332 EXCEPTION
2333 WHEN violated_ref_integrity THEN
2334 l_return_status := OKL_API.G_RET_STS_ERROR;
2335 RETURN (l_return_status);
2336 END validate_ref_integrity;
2337 BEGIN
2338 l_return_status := validate_ref_integrity(p_subv_rec, p_db_subv_rec);
2339 RETURN (l_return_status);
2340 END Validate_Record;
2341
2342 FUNCTION Validate_Record (
2343 p_subv_rec IN subv_rec_type
2344 ) RETURN VARCHAR2 IS
2345 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2346 l_db_subv_rec subv_rec_type := get_rec(p_subv_rec);
2347 BEGIN
2348 l_return_status := Validate_Record(p_subv_rec => p_subv_rec,
2349 p_db_subv_rec => l_db_subv_rec);
2350 RETURN (l_return_status);
2351 END Validate_Record;
2352 -------------------------------------------------
2353 --***End of Handcoded validate record
2354 -------------------------------------------------
2355 /******************Commented generated validate record***
2356 ---------------------------------------------------------------------------
2357 -- PROCEDURE Validate_Record
2358 ---------------------------------------------------------------------------
2359 -----------------------------------------
2360 -- Validate Record for:OKL_SUBSIDIES_V --
2361 -----------------------------------------
2362 FUNCTION Validate_Record (
2363 p_subv_rec IN subv_rec_type,
2364 p_db_subv_rec IN subv_rec_type
2365 ) RETURN VARCHAR2 IS
2366 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2367 ------------------------------------
2368 -- FUNCTION validate_foreign_keys --
2369 ------------------------------------
2370 FUNCTION validate_foreign_keys (
2371 p_subv_rec IN subv_rec_type,
2372 p_db_subv_rec IN subv_rec_type
2373 ) RETURN VARCHAR2 IS
2374 item_not_found_error EXCEPTION;
2375 CURSOR okl_subsidies_v_fk1_csr (p_lookup_code IN VARCHAR2) IS
2376 SELECT 'x'
2377 FROM Fnd_Lookup_Values
2378 WHERE fnd_lookup_values.lookup_code = p_lookup_code;
2379 l_okl_subsidies_v_fk1 okl_subsidies_v_fk1_csr%ROWTYPE;
2380
2381 CURSOR okl_subsidies_v_fk2_csr (p_id IN NUMBER,
2382 p_org_id IN NUMBER) IS
2383 SELECT 'x'
2384 FROM Okl_Formulae_B
2385 WHERE okl_formulae_b.id = p_id
2386 AND okl_formulae_b.org_id = p_org_id;
2387 l_okl_subsidies_v_fk2 okl_subsidies_v_fk2_csr%ROWTYPE;
2388
2389 CURSOR okl_subsidies_v_fk6_csr (p_id IN NUMBER) IS
2390 SELECT 'x'
2391 FROM Okl_Strm_Type_B
2392 WHERE okl_strm_type_b.id = p_id;
2393 l_okl_subsidies_v_fk6 okl_subsidies_v_fk6_csr%ROWTYPE;
2394
2395 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2396 l_row_notfound BOOLEAN := TRUE;
2397 BEGIN
2398 IF ((p_subv_rec.SUBSIDY_CALC_BASIS IS NOT NULL)
2399 AND
2400 (p_subv_rec.SUBSIDY_CALC_BASIS <> p_db_subv_rec.SUBSIDY_CALC_BASIS))
2401 THEN
2402 OPEN okl_subsidies_v_fk1_csr (p_subv_rec.SUBSIDY_CALC_BASIS);
2403 FETCH okl_subsidies_v_fk1_csr INTO l_okl_subsidies_v_fk1;
2404 l_row_notfound := okl_subsidies_v_fk1_csr%NOTFOUND;
2405 CLOSE okl_subsidies_v_fk1_csr;
2406 IF (l_row_notfound) THEN
2407 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SUBSIDY_CALC_BASIS');
2408 RAISE item_not_found_error;
2409 END IF;
2410 END IF;
2411 IF (((p_subv_rec.FORMULA_ID IS NOT NULL) AND
2412 (p_subv_rec.ORG_ID IS NOT NULL))
2413 AND
2414 ((p_subv_rec.FORMULA_ID <> p_db_subv_rec.FORMULA_ID) OR
2415 (p_subv_rec.ORG_ID <> p_db_subv_rec.ORG_ID)))
2416 THEN
2417 OPEN okl_subsidies_v_fk2_csr (p_subv_rec.FORMULA_ID,
2418 p_subv_rec.ORG_ID);
2419 FETCH okl_subsidies_v_fk2_csr INTO l_okl_subsidies_v_fk2;
2420 l_row_notfound := okl_subsidies_v_fk2_csr%NOTFOUND;
2421 CLOSE okl_subsidies_v_fk2_csr;
2422 IF (l_row_notfound) THEN
2423 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'FORMULA_ID');
2424 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ORG_ID');
2425 RAISE item_not_found_error;
2426 END IF;
2427 END IF;
2428 IF ((p_subv_rec.STREAM_TYPE_ID IS NOT NULL)
2429 AND
2430 (p_subv_rec.STREAM_TYPE_ID <> p_db_subv_rec.STREAM_TYPE_ID))
2431 THEN
2432 OPEN okl_subsidies_v_fk6_csr (p_subv_rec.STREAM_TYPE_ID);
2433 FETCH okl_subsidies_v_fk6_csr INTO l_okl_subsidies_v_fk6;
2434 l_row_notfound := okl_subsidies_v_fk6_csr%NOTFOUND;
2435 CLOSE okl_subsidies_v_fk6_csr;
2436 IF (l_row_notfound) THEN
2437 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'STREAM_TYPE_ID');
2438 RAISE item_not_found_error;
2439 END IF;
2440 END IF;
2441 RETURN (l_return_status);
2442 EXCEPTION
2443 WHEN item_not_found_error THEN
2444 l_return_status := OKL_API.G_RET_STS_ERROR;
2445 RETURN (l_return_status);
2446 END validate_foreign_keys;
2447 BEGIN
2448 l_return_status := validate_foreign_keys(p_subv_rec, p_db_subv_rec);
2449 RETURN (l_return_status);
2450 END Validate_Record;
2451 FUNCTION Validate_Record (
2452 p_subv_rec IN subv_rec_type
2453 ) RETURN VARCHAR2 IS
2454 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2455 l_db_subv_rec subv_rec_type := get_rec(p_subv_rec);
2456 BEGIN
2457 l_return_status := Validate_Record(p_subv_rec => p_subv_rec,
2458 p_db_subv_rec => l_db_subv_rec);
2459 RETURN (l_return_status);
2460 END Validate_Record;
2461 ****************End of Commented generated validate record***/
2462 ---------------------------------------------------------------------------
2463 -- PROCEDURE Migrate
2464 ---------------------------------------------------------------------------
2465 PROCEDURE migrate (
2466 p_from IN subv_rec_type,
2467 p_to IN OUT NOCOPY subt_rec_type
2468 ) IS
2469 BEGIN
2470 p_to.id := p_from.id;
2471 p_to.short_description := p_from.short_description;
2472 p_to.description := p_from.description;
2473 p_to.sfwt_flag := p_from.sfwt_flag;
2474 p_to.created_by := p_from.created_by;
2475 p_to.creation_date := p_from.creation_date;
2476 p_to.last_updated_by := p_from.last_updated_by;
2477 p_to.last_update_date := p_from.last_update_date;
2478 p_to.last_update_login := p_from.last_update_login;
2479 END migrate;
2480 PROCEDURE migrate (
2481 p_from IN subt_rec_type,
2482 p_to IN OUT NOCOPY subv_rec_type
2483 ) IS
2484 BEGIN
2485 p_to.id := p_from.id;
2486 p_to.sfwt_flag := p_from.sfwt_flag;
2487 p_to.short_description := p_from.short_description;
2488 p_to.description := p_from.description;
2489 p_to.created_by := p_from.created_by;
2490 p_to.creation_date := p_from.creation_date;
2491 p_to.last_updated_by := p_from.last_updated_by;
2492 p_to.last_update_date := p_from.last_update_date;
2493 p_to.last_update_login := p_from.last_update_login;
2494 END migrate;
2495 PROCEDURE migrate (
2496 p_from IN subv_rec_type,
2497 p_to IN OUT NOCOPY subb_rec_type
2498 ) IS
2499 BEGIN
2500 p_to.id := p_from.id;
2501 p_to.object_version_number := p_from.object_version_number;
2502 p_to.org_id := p_from.org_id;
2503 p_to.name := p_from.name;
2504 p_to.effective_from_date := p_from.effective_from_date;
2505 p_to.effective_to_date := p_from.effective_to_date;
2506 p_to.expire_after_days := p_from.expire_after_days;
2507 p_to.currency_code := p_from.currency_code;
2508 p_to.exclusive_yn := p_from.exclusive_yn;
2509 p_to.applicable_to_release_yn := p_from.applicable_to_release_yn;
2510 p_to.subsidy_calc_basis := p_from.subsidy_calc_basis;
2511 p_to.amount := p_from.amount;
2512 p_to.percent := p_from.percent;
2513 p_to.formula_id := p_from.formula_id;
2514 p_to.rate_points := p_from.rate_points;
2515 p_to.maximum_term := p_from.maximum_term;
2516 p_to.vendor_id := p_from.vendor_id;
2517 p_to.accounting_method_code := p_from.accounting_method_code;
2518 p_to.recourse_yn := p_from.recourse_yn;
2519 p_to.termination_refund_basis := p_from.termination_refund_basis;
2520 p_to.refund_formula_id := p_from.refund_formula_id;
2521 p_to.stream_type_id := p_from.stream_type_id;
2522 p_to.receipt_method_code := p_from.receipt_method_code;
2523 p_to.customer_visible_yn := p_from.customer_visible_yn;
2524 p_to.maximum_financed_amount := p_from.maximum_financed_amount;
2525 p_to.maximum_subsidy_amount := p_from.maximum_subsidy_amount;
2526 --Start code changes for Subsidy by fmiao on 10/25/2004--
2527 p_to.transfer_basis_code := p_from.transfer_basis_code;
2528 --End code changes for Subsidy by fmiao on 10/25/2004--
2529 p_to.attribute_category := p_from.attribute_category;
2530 p_to.attribute1 := p_from.attribute1;
2531 p_to.attribute2 := p_from.attribute2;
2532 p_to.attribute3 := p_from.attribute3;
2533 p_to.attribute4 := p_from.attribute4;
2534 p_to.attribute5 := p_from.attribute5;
2535 p_to.attribute6 := p_from.attribute6;
2536 p_to.attribute7 := p_from.attribute7;
2537 p_to.attribute8 := p_from.attribute8;
2538 p_to.attribute9 := p_from.attribute9;
2539 p_to.attribute10 := p_from.attribute10;
2540 p_to.attribute11 := p_from.attribute11;
2541 p_to.attribute12 := p_from.attribute12;
2542 p_to.attribute13 := p_from.attribute13;
2543 p_to.attribute14 := p_from.attribute14;
2544 p_to.attribute15 := p_from.attribute15;
2545 p_to.created_by := p_from.created_by;
2546 p_to.creation_date := p_from.creation_date;
2547 p_to.last_updated_by := p_from.last_updated_by;
2548 p_to.last_update_date := p_from.last_update_date;
2549 p_to.last_update_login := p_from.last_update_login;
2550 -- sjalasut added new column for subsidy pools enhancement. start
2551 p_to.subsidy_pool_id := p_from.subsidy_pool_id;
2552 -- sjalasut added new column for subsidy pools enhancement. end
2553 END migrate;
2554 PROCEDURE migrate (
2555 p_from IN subb_rec_type,
2556 p_to IN OUT NOCOPY subv_rec_type
2557 ) IS
2558 BEGIN
2559 p_to.id := p_from.id;
2560 p_to.object_version_number := p_from.object_version_number;
2561 p_to.org_id := p_from.org_id;
2562 p_to.name := p_from.name;
2563 p_to.effective_from_date := p_from.effective_from_date;
2564 p_to.effective_to_date := p_from.effective_to_date;
2565 p_to.expire_after_days := p_from.expire_after_days;
2566 p_to.currency_code := p_from.currency_code;
2567 p_to.exclusive_yn := p_from.exclusive_yn;
2568 p_to.applicable_to_release_yn := p_from.applicable_to_release_yn;
2569 p_to.subsidy_calc_basis := p_from.subsidy_calc_basis;
2570 p_to.amount := p_from.amount;
2571 p_to.percent := p_from.percent;
2572 p_to.formula_id := p_from.formula_id;
2573 p_to.rate_points := p_from.rate_points;
2574 p_to.maximum_term := p_from.maximum_term;
2575 p_to.vendor_id := p_from.vendor_id;
2576 p_to.accounting_method_code := p_from.accounting_method_code;
2577 p_to.recourse_yn := p_from.recourse_yn;
2578 p_to.termination_refund_basis := p_from.termination_refund_basis;
2579 p_to.refund_formula_id := p_from.refund_formula_id;
2580 p_to.stream_type_id := p_from.stream_type_id;
2581 p_to.receipt_method_code := p_from.receipt_method_code;
2582 p_to.customer_visible_yn := p_from.customer_visible_yn;
2583 p_to.maximum_financed_amount := p_from.maximum_financed_amount;
2584 p_to.maximum_subsidy_amount := p_from.maximum_subsidy_amount;
2585 --Start code changes for Subsidy by fmiao on 10/25/2004--
2586 p_to.transfer_basis_code := p_from.transfer_basis_code;
2587 --End code changes for Subsidy by fmiao on 10/25/2004--
2588 p_to.attribute_category := p_from.attribute_category;
2589 p_to.attribute1 := p_from.attribute1;
2590 p_to.attribute2 := p_from.attribute2;
2591 p_to.attribute3 := p_from.attribute3;
2592 p_to.attribute4 := p_from.attribute4;
2593 p_to.attribute5 := p_from.attribute5;
2594 p_to.attribute6 := p_from.attribute6;
2595 p_to.attribute7 := p_from.attribute7;
2596 p_to.attribute8 := p_from.attribute8;
2597 p_to.attribute9 := p_from.attribute9;
2598 p_to.attribute10 := p_from.attribute10;
2599 p_to.attribute11 := p_from.attribute11;
2600 p_to.attribute12 := p_from.attribute12;
2601 p_to.attribute13 := p_from.attribute13;
2602 p_to.attribute14 := p_from.attribute14;
2603 p_to.attribute15 := p_from.attribute15;
2604 p_to.created_by := p_from.created_by;
2605 p_to.creation_date := p_from.creation_date;
2606 p_to.last_updated_by := p_from.last_updated_by;
2607 p_to.last_update_date := p_from.last_update_date;
2608 p_to.last_update_login := p_from.last_update_login;
2609 -- sjalasut added new column for subsidy pools enhancement. start
2610 p_to.subsidy_pool_id := p_from.subsidy_pool_id;
2611 -- sjalasut added new column for subsidy pools enhancement. end
2612 END migrate;
2613 ---------------------------------------------------------------------------
2614 -- PROCEDURE validate_row
2615 ---------------------------------------------------------------------------
2616 --------------------------------------
2617 -- validate_row for:OKL_SUBSIDIES_V --
2618 --------------------------------------
2619 PROCEDURE validate_row(
2620 p_api_version IN NUMBER,
2621 p_init_msg_list IN VARCHAR2,
2622 x_return_status OUT NOCOPY VARCHAR2,
2623 x_msg_count OUT NOCOPY NUMBER,
2624 x_msg_data OUT NOCOPY VARCHAR2,
2625 p_subv_rec IN subv_rec_type) IS
2626
2627 l_api_version CONSTANT NUMBER := 1;
2628 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2629 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2630 l_subv_rec subv_rec_type := p_subv_rec;
2631 l_subb_rec subb_rec_type;
2632 l_subt_rec subt_rec_type;
2633 BEGIN
2634 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2635 G_PKG_NAME,
2636 p_init_msg_list,
2637 l_api_version,
2638 p_api_version,
2639 '_PVT',
2640 x_return_status);
2641 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2642 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2643 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2644 RAISE OKL_API.G_EXCEPTION_ERROR;
2645 END IF;
2646 --- Validate all non-missing attributes (Item Level Validation)
2647 l_return_status := Validate_Attributes(l_subv_rec);
2648 --- If any errors happen abort API
2649 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2650 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2651 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2652 RAISE OKL_API.G_EXCEPTION_ERROR;
2653 END IF;
2654 l_return_status := Validate_Record(l_subv_rec);
2655 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2656 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2657 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2658 RAISE OKL_API.G_EXCEPTION_ERROR;
2659 END IF;
2660 x_return_status := l_return_status;
2661 EXCEPTION
2662 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2663 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2664 (
2665 l_api_name,
2666 G_PKG_NAME,
2667 'OKL_API.G_RET_STS_ERROR',
2668 x_msg_count,
2669 x_msg_data,
2670 '_PVT'
2671 );
2672 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2673 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2674 (
2675 l_api_name,
2676 G_PKG_NAME,
2677 'OKL_API.G_RET_STS_UNEXP_ERROR',
2678 x_msg_count,
2679 x_msg_data,
2680 '_PVT'
2681 );
2682 WHEN OTHERS THEN
2683 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2684 (
2685 l_api_name,
2686 G_PKG_NAME,
2687 'OTHERS',
2688 x_msg_count,
2689 x_msg_data,
2690 '_PVT'
2691 );
2692 END validate_row;
2693 -------------------------------------------------
2694 -- PL/SQL TBL validate_row for:OKL_SUBSIDIES_V --
2695 -------------------------------------------------
2696 PROCEDURE validate_row(
2697 p_api_version IN NUMBER,
2698 p_init_msg_list IN VARCHAR2,
2699 x_return_status OUT NOCOPY VARCHAR2,
2700 x_msg_count OUT NOCOPY NUMBER,
2701 x_msg_data OUT NOCOPY VARCHAR2,
2702 p_subv_tbl IN subv_tbl_type,
2703 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2704
2705 l_api_version CONSTANT NUMBER := 1;
2706 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
2707 i NUMBER := 0;
2708 BEGIN
2709 OKL_API.init_msg_list(p_init_msg_list);
2710 -- Make sure PL/SQL table has records in it before passing
2711 IF (p_subv_tbl.COUNT > 0) THEN
2712 i := p_subv_tbl.FIRST;
2713 LOOP
2714 DECLARE
2715 l_error_rec OKL_API.ERROR_REC_TYPE;
2716 BEGIN
2717 l_error_rec.api_name := l_api_name;
2718 l_error_rec.api_package := G_PKG_NAME;
2719 l_error_rec.idx := i;
2720 validate_row (
2721 p_api_version => p_api_version,
2722 p_init_msg_list => OKL_API.G_FALSE,
2723 x_return_status => l_error_rec.error_type,
2724 x_msg_count => l_error_rec.msg_count,
2725 x_msg_data => l_error_rec.msg_data,
2726 p_subv_rec => p_subv_tbl(i));
2727 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2728 l_error_rec.sqlcode := SQLCODE;
2729 load_error_tbl(l_error_rec, px_error_tbl);
2730 ELSE
2731 x_msg_count := l_error_rec.msg_count;
2732 x_msg_data := l_error_rec.msg_data;
2733 END IF;
2734 EXCEPTION
2735 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2736 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2737 l_error_rec.sqlcode := SQLCODE;
2738 load_error_tbl(l_error_rec, px_error_tbl);
2739 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2740 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2741 l_error_rec.sqlcode := SQLCODE;
2742 load_error_tbl(l_error_rec, px_error_tbl);
2743 WHEN OTHERS THEN
2744 l_error_rec.error_type := 'OTHERS';
2745 l_error_rec.sqlcode := SQLCODE;
2746 load_error_tbl(l_error_rec, px_error_tbl);
2747 END;
2748 EXIT WHEN (i = p_subv_tbl.LAST);
2749 i := p_subv_tbl.NEXT(i);
2750 END LOOP;
2751 END IF;
2752 -- Loop through the error_tbl to find the error with the highest severity
2753 -- and return it.
2754 x_return_status := find_highest_exception(px_error_tbl);
2755 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2756 EXCEPTION
2757 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2758 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2759 (
2760 l_api_name,
2761 G_PKG_NAME,
2762 'OKL_API.G_RET_STS_ERROR',
2763 x_msg_count,
2764 x_msg_data,
2765 '_PVT'
2766 );
2767 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2768 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2769 (
2770 l_api_name,
2771 G_PKG_NAME,
2772 'OKL_API.G_RET_STS_UNEXP_ERROR',
2773 x_msg_count,
2774 x_msg_data,
2775 '_PVT'
2776 );
2777 WHEN OTHERS THEN
2778 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2779 (
2780 l_api_name,
2781 G_PKG_NAME,
2782 'OTHERS',
2783 x_msg_count,
2784 x_msg_data,
2785 '_PVT'
2786 );
2787 END validate_row;
2788
2789 -------------------------------------------------
2790 -- PL/SQL TBL validate_row for:OKL_SUBSIDIES_V --
2791 -------------------------------------------------
2792 PROCEDURE validate_row(
2793 p_api_version IN NUMBER,
2794 p_init_msg_list IN VARCHAR2,
2795 x_return_status OUT NOCOPY VARCHAR2,
2796 x_msg_count OUT NOCOPY NUMBER,
2797 x_msg_data OUT NOCOPY VARCHAR2,
2798 p_subv_tbl IN subv_tbl_type) IS
2799
2800 l_api_version CONSTANT NUMBER := 1;
2801 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2802 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2803 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2804 BEGIN
2805 OKL_API.init_msg_list(p_init_msg_list);
2806 -- Make sure PL/SQL table has records in it before passing
2807 IF (p_subv_tbl.COUNT > 0) THEN
2808 validate_row (
2809 p_api_version => p_api_version,
2810 p_init_msg_list => OKL_API.G_FALSE,
2811 x_return_status => x_return_status,
2812 x_msg_count => x_msg_count,
2813 x_msg_data => x_msg_data,
2814 p_subv_tbl => p_subv_tbl,
2815 px_error_tbl => l_error_tbl);
2816 END IF;
2817 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2818 EXCEPTION
2819 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2820 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2821 (
2822 l_api_name,
2823 G_PKG_NAME,
2824 'OKL_API.G_RET_STS_ERROR',
2825 x_msg_count,
2826 x_msg_data,
2827 '_PVT'
2828 );
2829 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2830 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2831 (
2832 l_api_name,
2833 G_PKG_NAME,
2834 'OKL_API.G_RET_STS_UNEXP_ERROR',
2835 x_msg_count,
2836 x_msg_data,
2837 '_PVT'
2838 );
2839 WHEN OTHERS THEN
2840 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2841 (
2842 l_api_name,
2843 G_PKG_NAME,
2844 'OTHERS',
2845 x_msg_count,
2846 x_msg_data,
2847 '_PVT'
2848 );
2849 END validate_row;
2850
2851 ---------------------------------------------------------------------------
2852 -- PROCEDURE insert_row
2853 ---------------------------------------------------------------------------
2854 ------------------------------------
2855 -- insert_row for:OKL_SUBSIDIES_B --
2856 ------------------------------------
2857 PROCEDURE insert_row(
2858 p_init_msg_list IN VARCHAR2,
2859 x_return_status OUT NOCOPY VARCHAR2,
2860 x_msg_count OUT NOCOPY NUMBER,
2861 x_msg_data OUT NOCOPY VARCHAR2,
2862 p_subb_rec IN subb_rec_type,
2863 x_subb_rec OUT NOCOPY subb_rec_type) IS
2864
2865 l_api_version CONSTANT NUMBER := 1;
2866 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
2867 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2868 l_subb_rec subb_rec_type := p_subb_rec;
2869 l_def_subb_rec subb_rec_type;
2870 ----------------------------------------
2871 -- Set_Attributes for:OKL_SUBSIDIES_B --
2872 ----------------------------------------
2873 FUNCTION Set_Attributes (
2874 p_subb_rec IN subb_rec_type,
2875 x_subb_rec OUT NOCOPY subb_rec_type
2876 ) RETURN VARCHAR2 IS
2877 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2878 BEGIN
2879 x_subb_rec := p_subb_rec;
2880 RETURN(l_return_status);
2881 END Set_Attributes;
2882 BEGIN
2883 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2884 p_init_msg_list,
2885 '_PVT',
2886 x_return_status);
2887 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2888 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2889 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2890 RAISE OKL_API.G_EXCEPTION_ERROR;
2891 END IF;
2892 --- Setting item atributes
2893 l_return_status := Set_Attributes(
2894 p_subb_rec, -- IN
2895 l_subb_rec); -- OUT
2896 --- If any errors happen abort API
2897 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2898 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2899 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2900 RAISE OKL_API.G_EXCEPTION_ERROR;
2901 END IF;
2902 INSERT INTO OKL_SUBSIDIES_B(
2903 id,
2904 object_version_number,
2905 org_id,
2906 name,
2907 effective_from_date,
2908 effective_to_date,
2909 expire_after_days,
2910 currency_code,
2911 exclusive_yn,
2912 applicable_to_release_yn,
2913 subsidy_calc_basis,
2914 amount,
2915 percent,
2916 formula_id,
2917 rate_points,
2918 maximum_term,
2919 vendor_id,
2920 accounting_method_code,
2921 recourse_yn,
2922 termination_refund_basis,
2923 refund_formula_id,
2924 stream_type_id,
2925 receipt_method_code,
2926 customer_visible_yn,
2927 maximum_financed_amount,
2928 maximum_subsidy_amount,
2929 --Start code changes for Subsidy by fmiao on 10/25/2004--
2930 transfer_basis_code,
2931 --End code changes for Subsidy by fmiao on 10/25/2004--
2932 attribute_category,
2933 attribute1,
2934 attribute2,
2935 attribute3,
2936 attribute4,
2937 attribute5,
2938 attribute6,
2939 attribute7,
2940 attribute8,
2941 attribute9,
2942 attribute10,
2943 attribute11,
2944 attribute12,
2945 attribute13,
2946 attribute14,
2947 attribute15,
2948 created_by,
2949 creation_date,
2950 last_updated_by,
2951 last_update_date,
2952 last_update_login,
2953 -- sjalasut added new column for subsidy pools enhancement. start
2954 subsidy_pool_id
2955 -- sjalasut added new column for subsidy pools enhancement. end
2956 )
2957 VALUES (
2958 l_subb_rec.id,
2959 l_subb_rec.object_version_number,
2960 l_subb_rec.org_id,
2961 l_subb_rec.name,
2962 l_subb_rec.effective_from_date,
2963 l_subb_rec.effective_to_date,
2964 l_subb_rec.expire_after_days,
2965 l_subb_rec.currency_code,
2966 l_subb_rec.exclusive_yn,
2967 l_subb_rec.applicable_to_release_yn,
2968 l_subb_rec.subsidy_calc_basis,
2969 l_subb_rec.amount,
2970 l_subb_rec.percent,
2971 l_subb_rec.formula_id,
2972 l_subb_rec.rate_points,
2973 l_subb_rec.maximum_term,
2974 l_subb_rec.vendor_id,
2975 l_subb_rec.accounting_method_code,
2976 l_subb_rec.recourse_yn,
2977 l_subb_rec.termination_refund_basis,
2978 l_subb_rec.refund_formula_id,
2979 l_subb_rec.stream_type_id,
2980 l_subb_rec.receipt_method_code,
2981 l_subb_rec.customer_visible_yn,
2982 l_subb_rec.maximum_financed_amount,
2983 l_subb_rec.maximum_subsidy_amount,
2984 --Start code changes for Subsidy by fmiao on 10/25/2004--
2985 l_subb_rec.transfer_basis_code,
2986 --End code changes for Subsidy by fmiao on 10/25/2004--
2987 l_subb_rec.attribute_category,
2988 l_subb_rec.attribute1,
2989 l_subb_rec.attribute2,
2990 l_subb_rec.attribute3,
2991 l_subb_rec.attribute4,
2992 l_subb_rec.attribute5,
2993 l_subb_rec.attribute6,
2994 l_subb_rec.attribute7,
2995 l_subb_rec.attribute8,
2996 l_subb_rec.attribute9,
2997 l_subb_rec.attribute10,
2998 l_subb_rec.attribute11,
2999 l_subb_rec.attribute12,
3000 l_subb_rec.attribute13,
3001 l_subb_rec.attribute14,
3002 l_subb_rec.attribute15,
3003 l_subb_rec.created_by,
3004 l_subb_rec.creation_date,
3005 l_subb_rec.last_updated_by,
3006 l_subb_rec.last_update_date,
3007 l_subb_rec.last_update_login,
3008 -- sjalasut added new column for subsidy pools enhancement. start
3009 l_subb_rec.subsidy_pool_id
3010 -- sjalasut added new column for subsidy pools enhancement. end
3011 );
3012 -- Set OUT values
3013 x_subb_rec := l_subb_rec;
3014 x_return_status := l_return_status;
3015 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3016 EXCEPTION
3017 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3018 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3019 (
3020 l_api_name,
3021 G_PKG_NAME,
3022 'OKL_API.G_RET_STS_ERROR',
3023 x_msg_count,
3024 x_msg_data,
3025 '_PVT'
3026 );
3027 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3028 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3029 (
3030 l_api_name,
3031 G_PKG_NAME,
3032 'OKL_API.G_RET_STS_UNEXP_ERROR',
3033 x_msg_count,
3034 x_msg_data,
3035 '_PVT'
3036 );
3037 WHEN OTHERS THEN
3038 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3039 (
3040 l_api_name,
3041 G_PKG_NAME,
3042 'OTHERS',
3043 x_msg_count,
3044 x_msg_data,
3045 '_PVT'
3046 );
3047 END insert_row;
3048 -------------------------------------
3049 -- insert_row for:OKL_SUBSIDIES_TL --
3050 -------------------------------------
3051 PROCEDURE insert_row(
3052 p_init_msg_list IN VARCHAR2,
3053 x_return_status OUT NOCOPY VARCHAR2,
3054 x_msg_count OUT NOCOPY NUMBER,
3055 x_msg_data OUT NOCOPY VARCHAR2,
3056 p_subt_rec IN subt_rec_type,
3057 x_subt_rec OUT NOCOPY subt_rec_type) IS
3058
3059 l_api_version CONSTANT NUMBER := 1;
3060 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
3061 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3062 l_subt_rec subt_rec_type := p_subt_rec;
3063 l_def_subt_rec subt_rec_type;
3064 CURSOR get_languages IS
3065 SELECT *
3066 FROM FND_LANGUAGES
3067 WHERE INSTALLED_FLAG IN ('I', 'B');
3068 -----------------------------------------
3069 -- Set_Attributes for:OKL_SUBSIDIES_TL --
3070 -----------------------------------------
3071 FUNCTION Set_Attributes (
3072 p_subt_rec IN subt_rec_type,
3073 x_subt_rec OUT NOCOPY subt_rec_type
3074 ) RETURN VARCHAR2 IS
3075 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3076 BEGIN
3077 x_subt_rec := p_subt_rec;
3078 x_subt_rec.LANGUAGE := USERENV('LANG');
3079 x_subt_rec.SOURCE_LANG := USERENV('LANG');
3080 RETURN(l_return_status);
3081 END Set_Attributes;
3082 BEGIN
3083 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3084 p_init_msg_list,
3085 '_PVT',
3086 x_return_status);
3087 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3088 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3089 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3090 RAISE OKL_API.G_EXCEPTION_ERROR;
3091 END IF;
3092 --- Setting item attributes
3093 l_return_status := Set_Attributes(
3094 p_subt_rec, -- IN
3095 l_subt_rec); -- OUT
3096 --- If any errors happen abort API
3097 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3098 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3099 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3100 RAISE OKL_API.G_EXCEPTION_ERROR;
3101 END IF;
3102 FOR l_lang_rec IN get_languages LOOP
3103 l_subt_rec.language := l_lang_rec.language_code;
3104 INSERT INTO OKL_SUBSIDIES_TL(
3105 id,
3106 short_description,
3107 description,
3108 language,
3109 source_lang,
3110 sfwt_flag,
3111 created_by,
3112 creation_date,
3113 last_updated_by,
3114 last_update_date,
3115 last_update_login)
3116 VALUES (
3117 l_subt_rec.id,
3118 l_subt_rec.short_description,
3119 l_subt_rec.description,
3120 l_subt_rec.language,
3121 l_subt_rec.source_lang,
3122 l_subt_rec.sfwt_flag,
3123 l_subt_rec.created_by,
3124 l_subt_rec.creation_date,
3125 l_subt_rec.last_updated_by,
3126 l_subt_rec.last_update_date,
3127 l_subt_rec.last_update_login);
3128 END LOOP;
3129 -- Set OUT values
3130 x_subt_rec := l_subt_rec;
3131 x_return_status := l_return_status;
3132 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3133 EXCEPTION
3134 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3135 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3136 (
3137 l_api_name,
3138 G_PKG_NAME,
3139 'OKL_API.G_RET_STS_ERROR',
3140 x_msg_count,
3141 x_msg_data,
3142 '_PVT'
3143 );
3144 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3145 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3146 (
3147 l_api_name,
3148 G_PKG_NAME,
3149 'OKL_API.G_RET_STS_UNEXP_ERROR',
3150 x_msg_count,
3151 x_msg_data,
3152 '_PVT'
3153 );
3154 WHEN OTHERS THEN
3155 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3156 (
3157 l_api_name,
3158 G_PKG_NAME,
3159 'OTHERS',
3160 x_msg_count,
3161 x_msg_data,
3162 '_PVT'
3163 );
3164 END insert_row;
3165 -------------------------------------
3166 -- insert_row for :OKL_SUBSIDIES_V --
3167 -------------------------------------
3168 PROCEDURE insert_row(
3169 p_api_version IN NUMBER,
3170 p_init_msg_list IN VARCHAR2,
3171 x_return_status OUT NOCOPY VARCHAR2,
3172 x_msg_count OUT NOCOPY NUMBER,
3173 x_msg_data OUT NOCOPY VARCHAR2,
3174 p_subv_rec IN subv_rec_type,
3175 x_subv_rec OUT NOCOPY subv_rec_type) IS
3176
3177 l_api_version CONSTANT NUMBER := 1;
3178 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
3179 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3180 l_subv_rec subv_rec_type := p_subv_rec;
3181 l_def_subv_rec subv_rec_type;
3182 l_subb_rec subb_rec_type;
3183 lx_subb_rec subb_rec_type;
3184 l_subt_rec subt_rec_type;
3185 lx_subt_rec subt_rec_type;
3186 -------------------------------
3187 -- FUNCTION fill_who_columns --
3188 -------------------------------
3189 FUNCTION fill_who_columns (
3190 p_subv_rec IN subv_rec_type
3191 ) RETURN subv_rec_type IS
3192 l_subv_rec subv_rec_type := p_subv_rec;
3193 BEGIN
3194 l_subv_rec.CREATION_DATE := SYSDATE;
3195 l_subv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3196 l_subv_rec.LAST_UPDATE_DATE := l_subv_rec.CREATION_DATE;
3197 l_subv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3198 l_subv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3199 RETURN(l_subv_rec);
3200 END fill_who_columns;
3201 ----------------------------------------
3202 -- Set_Attributes for:OKL_SUBSIDIES_V --
3203 ----------------------------------------
3204 FUNCTION Set_Attributes (
3205 p_subv_rec IN subv_rec_type,
3206 x_subv_rec OUT NOCOPY subv_rec_type
3207 ) RETURN VARCHAR2 IS
3208 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3209 BEGIN
3210 x_subv_rec := p_subv_rec;
3211 x_subv_rec.OBJECT_VERSION_NUMBER := 1;
3212 x_subv_rec.SFWT_FLAG := 'N';
3213 RETURN(l_return_status);
3214 END Set_Attributes;
3215 BEGIN
3216 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3217 G_PKG_NAME,
3218 p_init_msg_list,
3219 l_api_version,
3220 p_api_version,
3221 '_PVT',
3222 x_return_status);
3223 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3224 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3225 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3226 RAISE OKL_API.G_EXCEPTION_ERROR;
3227 END IF;
3228 l_subv_rec := null_out_defaults(p_subv_rec);
3229 -- Set primary key value
3230 l_subv_rec.ID := get_seq_id;
3231 --Set the Org_ID
3232 l_subv_rec.org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
3233 -- Setting item attributes
3234 l_return_Status := Set_Attributes(
3235 l_subv_rec, -- IN
3236 l_def_subv_rec); -- OUT
3237 --- If any errors happen abort API
3238 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3239 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3240 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3241 RAISE OKL_API.G_EXCEPTION_ERROR;
3242 END IF;
3243 l_def_subv_rec := fill_who_columns(l_def_subv_rec);
3244 --- Validate all non-missing attributes (Item Level Validation)
3245 l_return_status := Validate_Attributes(l_def_subv_rec);
3246 --- If any errors happen abort API
3247 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3248 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3249 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3250 RAISE OKL_API.G_EXCEPTION_ERROR;
3251 END IF;
3252 l_return_status := Validate_Record(l_def_subv_rec);
3253 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3254 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3255 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3256 RAISE OKL_API.G_EXCEPTION_ERROR;
3257 END IF;
3258 -----------------------------------------
3259 -- Move VIEW record to "Child" records --
3260 -----------------------------------------
3261 migrate(l_def_subv_rec, l_subb_rec);
3262 migrate(l_def_subv_rec, l_subt_rec);
3263 -----------------------------------------------
3264 -- Call the INSERT_ROW for each child record --
3265 -----------------------------------------------
3266 insert_row(
3267 p_init_msg_list,
3268 l_return_status,
3269 x_msg_count,
3270 x_msg_data,
3271 l_subb_rec,
3272 lx_subb_rec
3273 );
3274 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3275 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3276 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3277 RAISE OKL_API.G_EXCEPTION_ERROR;
3278 END IF;
3279 migrate(lx_subb_rec, l_def_subv_rec);
3280 insert_row(
3281 p_init_msg_list,
3282 l_return_status,
3283 x_msg_count,
3284 x_msg_data,
3285 l_subt_rec,
3286 lx_subt_rec
3287 );
3288 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3289 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3290 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3291 RAISE OKL_API.G_EXCEPTION_ERROR;
3292 END IF;
3293 migrate(lx_subt_rec, l_def_subv_rec);
3294 -- Set OUT values
3295 x_subv_rec := l_def_subv_rec;
3296 x_return_status := l_return_status;
3297 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3298 EXCEPTION
3299 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3300 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3301 (
3302 l_api_name,
3303 G_PKG_NAME,
3304 'OKL_API.G_RET_STS_ERROR',
3305 x_msg_count,
3306 x_msg_data,
3307 '_PVT'
3308 );
3309 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3310 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3311 (
3312 l_api_name,
3313 G_PKG_NAME,
3314 'OKL_API.G_RET_STS_UNEXP_ERROR',
3315 x_msg_count,
3316 x_msg_data,
3317 '_PVT'
3318 );
3319 WHEN OTHERS THEN
3320 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3321 (
3322 l_api_name,
3323 G_PKG_NAME,
3324 'OTHERS',
3325 x_msg_count,
3326 x_msg_data,
3327 '_PVT'
3328 );
3329 END insert_row;
3330 ----------------------------------------
3331 -- PL/SQL TBL insert_row for:SUBV_TBL --
3332 ----------------------------------------
3333 PROCEDURE insert_row(
3334 p_api_version IN NUMBER,
3335 p_init_msg_list IN VARCHAR2,
3336 x_return_status OUT NOCOPY VARCHAR2,
3337 x_msg_count OUT NOCOPY NUMBER,
3338 x_msg_data OUT NOCOPY VARCHAR2,
3339 p_subv_tbl IN subv_tbl_type,
3340 x_subv_tbl OUT NOCOPY subv_tbl_type,
3341 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3342
3343 l_api_version CONSTANT NUMBER := 1;
3344 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
3345 i NUMBER := 0;
3346 BEGIN
3347 OKL_API.init_msg_list(p_init_msg_list);
3348 -- Make sure PL/SQL table has records in it before passing
3349 IF (p_subv_tbl.COUNT > 0) THEN
3350 i := p_subv_tbl.FIRST;
3351 LOOP
3352 DECLARE
3353 l_error_rec OKL_API.ERROR_REC_TYPE;
3354 BEGIN
3355 l_error_rec.api_name := l_api_name;
3356 l_error_rec.api_package := G_PKG_NAME;
3357 l_error_rec.idx := i;
3358 insert_row (
3359 p_api_version => p_api_version,
3360 p_init_msg_list => OKL_API.G_FALSE,
3361 x_return_status => l_error_rec.error_type,
3362 x_msg_count => l_error_rec.msg_count,
3363 x_msg_data => l_error_rec.msg_data,
3364 p_subv_rec => p_subv_tbl(i),
3365 x_subv_rec => x_subv_tbl(i));
3366 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
3367 l_error_rec.sqlcode := SQLCODE;
3368 load_error_tbl(l_error_rec, px_error_tbl);
3369 ELSE
3370 x_msg_count := l_error_rec.msg_count;
3371 x_msg_data := l_error_rec.msg_data;
3372 END IF;
3373 EXCEPTION
3374 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3375 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
3376 l_error_rec.sqlcode := SQLCODE;
3377 load_error_tbl(l_error_rec, px_error_tbl);
3378 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3379 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
3380 l_error_rec.sqlcode := SQLCODE;
3381 load_error_tbl(l_error_rec, px_error_tbl);
3382 WHEN OTHERS THEN
3383 l_error_rec.error_type := 'OTHERS';
3384 l_error_rec.sqlcode := SQLCODE;
3385 load_error_tbl(l_error_rec, px_error_tbl);
3386 END;
3387 EXIT WHEN (i = p_subv_tbl.LAST);
3388 i := p_subv_tbl.NEXT(i);
3389 END LOOP;
3390 END IF;
3391 -- Loop through the error_tbl to find the error with the highest severity
3392 -- and return it.
3393 x_return_status := find_highest_exception(px_error_tbl);
3394 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3395 EXCEPTION
3396 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3397 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3398 (
3399 l_api_name,
3400 G_PKG_NAME,
3401 'OKL_API.G_RET_STS_ERROR',
3402 x_msg_count,
3403 x_msg_data,
3404 '_PVT'
3405 );
3406 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3407 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3408 (
3409 l_api_name,
3410 G_PKG_NAME,
3411 'OKL_API.G_RET_STS_UNEXP_ERROR',
3412 x_msg_count,
3413 x_msg_data,
3414 '_PVT'
3415 );
3416 WHEN OTHERS THEN
3417 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3418 (
3419 l_api_name,
3420 G_PKG_NAME,
3421 'OTHERS',
3422 x_msg_count,
3423 x_msg_data,
3424 '_PVT'
3425 );
3426 END insert_row;
3427
3428 ----------------------------------------
3429 -- PL/SQL TBL insert_row for:SUBV_TBL --
3430 ----------------------------------------
3431 PROCEDURE insert_row(
3432 p_api_version IN NUMBER,
3433 p_init_msg_list IN VARCHAR2,
3434 x_return_status OUT NOCOPY VARCHAR2,
3435 x_msg_count OUT NOCOPY NUMBER,
3436 x_msg_data OUT NOCOPY VARCHAR2,
3437 p_subv_tbl IN subv_tbl_type,
3438 x_subv_tbl OUT NOCOPY subv_tbl_type) IS
3439
3440 l_api_version CONSTANT NUMBER := 1;
3441 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
3442 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3443 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3444 BEGIN
3445 OKL_API.init_msg_list(p_init_msg_list);
3446 -- Make sure PL/SQL table has records in it before passing
3447 IF (p_subv_tbl.COUNT > 0) THEN
3448 insert_row (
3449 p_api_version => p_api_version,
3450 p_init_msg_list => OKL_API.G_FALSE,
3451 x_return_status => x_return_status,
3452 x_msg_count => x_msg_count,
3453 x_msg_data => x_msg_data,
3454 p_subv_tbl => p_subv_tbl,
3455 x_subv_tbl => x_subv_tbl,
3456 px_error_tbl => l_error_tbl);
3457 END IF;
3458 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3459 EXCEPTION
3460 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3461 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3462 (
3463 l_api_name,
3464 G_PKG_NAME,
3465 'OKL_API.G_RET_STS_ERROR',
3466 x_msg_count,
3467 x_msg_data,
3468 '_PVT'
3469 );
3470 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3471 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3472 (
3473 l_api_name,
3474 G_PKG_NAME,
3475 'OKL_API.G_RET_STS_UNEXP_ERROR',
3476 x_msg_count,
3477 x_msg_data,
3478 '_PVT'
3479 );
3480 WHEN OTHERS THEN
3481 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3482 (
3483 l_api_name,
3484 G_PKG_NAME,
3485 'OTHERS',
3486 x_msg_count,
3487 x_msg_data,
3488 '_PVT'
3489 );
3490 END insert_row;
3491
3492 ---------------------------------------------------------------------------
3493 -- PROCEDURE lock_row
3494 ---------------------------------------------------------------------------
3495 ----------------------------------
3496 -- lock_row for:OKL_SUBSIDIES_B --
3497 ----------------------------------
3498 PROCEDURE lock_row(
3499 p_init_msg_list IN VARCHAR2,
3500 x_return_status OUT NOCOPY VARCHAR2,
3501 x_msg_count OUT NOCOPY NUMBER,
3502 x_msg_data OUT NOCOPY VARCHAR2,
3503 p_subb_rec IN subb_rec_type) IS
3504
3505 E_Resource_Busy EXCEPTION;
3506 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3507 CURSOR lock_csr (p_subb_rec IN subb_rec_type) IS
3508 SELECT OBJECT_VERSION_NUMBER
3509 FROM OKL_SUBSIDIES_B
3510 WHERE ID = p_subb_rec.id
3511 AND OBJECT_VERSION_NUMBER = p_subb_rec.object_version_number
3512 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3513
3514 CURSOR lchk_csr (p_subb_rec IN subb_rec_type) IS
3515 SELECT OBJECT_VERSION_NUMBER
3516 FROM OKL_SUBSIDIES_B
3517 WHERE ID = p_subb_rec.id;
3518 l_api_version CONSTANT NUMBER := 1;
3519 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
3520 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3521 l_object_version_number OKL_SUBSIDIES_B.OBJECT_VERSION_NUMBER%TYPE;
3522 lc_object_version_number OKL_SUBSIDIES_B.OBJECT_VERSION_NUMBER%TYPE;
3523 l_row_notfound BOOLEAN := FALSE;
3524 lc_row_notfound BOOLEAN := FALSE;
3525 BEGIN
3526 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3527 p_init_msg_list,
3528 '_PVT',
3529 x_return_status);
3530 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3531 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3532 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3533 RAISE OKL_API.G_EXCEPTION_ERROR;
3534 END IF;
3535 BEGIN
3536 OPEN lock_csr(p_subb_rec);
3537 FETCH lock_csr INTO l_object_version_number;
3538 l_row_notfound := lock_csr%NOTFOUND;
3539 CLOSE lock_csr;
3540 EXCEPTION
3541 WHEN E_Resource_Busy THEN
3542 IF (lock_csr%ISOPEN) THEN
3543 CLOSE lock_csr;
3544 END IF;
3545 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3546 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3547 END;
3548
3549 IF ( l_row_notfound ) THEN
3550 OPEN lchk_csr(p_subb_rec);
3551 FETCH lchk_csr INTO lc_object_version_number;
3552 lc_row_notfound := lchk_csr%NOTFOUND;
3553 CLOSE lchk_csr;
3554 END IF;
3555 IF (lc_row_notfound) THEN
3556 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3557 RAISE OKL_API.G_EXCEPTION_ERROR;
3558 ELSIF lc_object_version_number > p_subb_rec.object_version_number THEN
3559 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3560 RAISE OKL_API.G_EXCEPTION_ERROR;
3561 ELSIF lc_object_version_number <> p_subb_rec.object_version_number THEN
3562 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3563 RAISE OKL_API.G_EXCEPTION_ERROR;
3564 ELSIF lc_object_version_number = -1 THEN
3565 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
3566 RAISE OKL_API.G_EXCEPTION_ERROR;
3567 END IF;
3568 x_return_status := l_return_status;
3569 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3570 EXCEPTION
3571 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3572 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3573 (
3574 l_api_name,
3575 G_PKG_NAME,
3576 'OKL_API.G_RET_STS_ERROR',
3577 x_msg_count,
3578 x_msg_data,
3579 '_PVT'
3580 );
3581 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3582 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3583 (
3584 l_api_name,
3585 G_PKG_NAME,
3586 'OKL_API.G_RET_STS_UNEXP_ERROR',
3587 x_msg_count,
3588 x_msg_data,
3589 '_PVT'
3590 );
3591 WHEN OTHERS THEN
3592 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3593 (
3594 l_api_name,
3595 G_PKG_NAME,
3596 'OTHERS',
3597 x_msg_count,
3598 x_msg_data,
3599 '_PVT'
3600 );
3601 END lock_row;
3602 -----------------------------------
3603 -- lock_row for:OKL_SUBSIDIES_TL --
3604 -----------------------------------
3605 PROCEDURE lock_row(
3606 p_init_msg_list IN VARCHAR2,
3607 x_return_status OUT NOCOPY VARCHAR2,
3608 x_msg_count OUT NOCOPY NUMBER,
3609 x_msg_data OUT NOCOPY VARCHAR2,
3610 p_subt_rec IN subt_rec_type) IS
3611
3612 E_Resource_Busy EXCEPTION;
3613 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3614 CURSOR lock_csr (p_subt_rec IN subt_rec_type) IS
3615 SELECT *
3616 FROM OKL_SUBSIDIES_TL
3617 WHERE ID = p_subt_rec.id
3618 FOR UPDATE NOWAIT;
3619
3620 l_api_version CONSTANT NUMBER := 1;
3621 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
3622 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3623 l_lock_var lock_csr%ROWTYPE;
3624 l_row_notfound BOOLEAN := FALSE;
3625 lc_row_notfound BOOLEAN := FALSE;
3626 BEGIN
3627 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3628 p_init_msg_list,
3629 '_PVT',
3630 x_return_status);
3631 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3632 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3633 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3634 RAISE OKL_API.G_EXCEPTION_ERROR;
3635 END IF;
3636 BEGIN
3637 OPEN lock_csr(p_subt_rec);
3638 FETCH lock_csr INTO l_lock_var;
3639 l_row_notfound := lock_csr%NOTFOUND;
3640 CLOSE lock_csr;
3641 EXCEPTION
3642 WHEN E_Resource_Busy THEN
3643 IF (lock_csr%ISOPEN) THEN
3644 CLOSE lock_csr;
3645 END IF;
3646 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3647 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3648 END;
3649
3650 IF ( l_row_notfound ) THEN
3651 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3652 RAISE OKL_API.G_EXCEPTION_ERROR;
3653 END IF;
3654 x_return_status := l_return_status;
3655 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3656 EXCEPTION
3657 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3658 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3659 (
3660 l_api_name,
3661 G_PKG_NAME,
3662 'OKL_API.G_RET_STS_ERROR',
3663 x_msg_count,
3664 x_msg_data,
3665 '_PVT'
3666 );
3667 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3668 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3669 (
3670 l_api_name,
3671 G_PKG_NAME,
3672 'OKL_API.G_RET_STS_UNEXP_ERROR',
3673 x_msg_count,
3674 x_msg_data,
3675 '_PVT'
3676 );
3677 WHEN OTHERS THEN
3678 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3679 (
3680 l_api_name,
3681 G_PKG_NAME,
3682 'OTHERS',
3683 x_msg_count,
3684 x_msg_data,
3685 '_PVT'
3686 );
3687 END lock_row;
3688 -----------------------------------
3689 -- lock_row for: OKL_SUBSIDIES_V --
3690 -----------------------------------
3691 PROCEDURE lock_row(
3692 p_api_version IN NUMBER,
3693 p_init_msg_list IN VARCHAR2,
3694 x_return_status OUT NOCOPY VARCHAR2,
3695 x_msg_count OUT NOCOPY NUMBER,
3696 x_msg_data OUT NOCOPY VARCHAR2,
3697 p_subv_rec IN subv_rec_type) IS
3698
3699 l_api_version CONSTANT NUMBER := 1;
3700 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
3701 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3702 l_subt_rec subt_rec_type;
3703 l_subb_rec subb_rec_type;
3704 BEGIN
3705 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3706 G_PKG_NAME,
3707 p_init_msg_list,
3708 l_api_version,
3709 p_api_version,
3710 '_PVT',
3711 x_return_status);
3712 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3713 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3714 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3715 RAISE OKL_API.G_EXCEPTION_ERROR;
3716 END IF;
3717 -----------------------------------------
3718 -- Move VIEW record to "Child" records --
3719 -----------------------------------------
3720 migrate(p_subv_rec, l_subt_rec);
3721 migrate(p_subv_rec, l_subb_rec);
3722 ---------------------------------------------
3723 -- Call the LOCK_ROW for each child record --
3724 ---------------------------------------------
3725 lock_row(
3726 p_init_msg_list,
3727 l_return_status,
3728 x_msg_count,
3729 x_msg_data,
3730 l_subt_rec
3731 );
3732 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3733 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3734 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3735 RAISE OKL_API.G_EXCEPTION_ERROR;
3736 END IF;
3737 lock_row(
3738 p_init_msg_list,
3739 l_return_status,
3740 x_msg_count,
3741 x_msg_data,
3742 l_subb_rec
3743 );
3744 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3745 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3746 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3747 RAISE OKL_API.G_EXCEPTION_ERROR;
3748 END IF;
3749 x_return_status := l_return_status;
3750 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3751 EXCEPTION
3752 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3753 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3754 (
3755 l_api_name,
3756 G_PKG_NAME,
3757 'OKL_API.G_RET_STS_ERROR',
3758 x_msg_count,
3759 x_msg_data,
3760 '_PVT'
3761 );
3762 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3763 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3764 (
3765 l_api_name,
3766 G_PKG_NAME,
3767 'OKL_API.G_RET_STS_UNEXP_ERROR',
3768 x_msg_count,
3769 x_msg_data,
3770 '_PVT'
3771 );
3772 WHEN OTHERS THEN
3773 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3774 (
3775 l_api_name,
3776 G_PKG_NAME,
3777 'OTHERS',
3778 x_msg_count,
3779 x_msg_data,
3780 '_PVT'
3781 );
3782 END lock_row;
3783 --------------------------------------
3784 -- PL/SQL TBL lock_row for:SUBV_TBL --
3785 --------------------------------------
3786 PROCEDURE lock_row(
3787 p_api_version IN NUMBER,
3788 p_init_msg_list IN VARCHAR2,
3789 x_return_status OUT NOCOPY VARCHAR2,
3790 x_msg_count OUT NOCOPY NUMBER,
3791 x_msg_data OUT NOCOPY VARCHAR2,
3792 p_subv_tbl IN subv_tbl_type,
3793 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3794
3795 l_api_version CONSTANT NUMBER := 1;
3796 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
3797 i NUMBER := 0;
3798 BEGIN
3799 OKL_API.init_msg_list(p_init_msg_list);
3800 -- Make sure PL/SQL table has recrods in it before passing
3801 IF (p_subv_tbl.COUNT > 0) THEN
3802 i := p_subv_tbl.FIRST;
3803 LOOP
3804 DECLARE
3805 l_error_rec OKL_API.ERROR_REC_TYPE;
3806 BEGIN
3807 l_error_rec.api_name := l_api_name;
3808 l_error_rec.api_package := G_PKG_NAME;
3809 l_error_rec.idx := i;
3810 lock_row(
3811 p_api_version => p_api_version,
3812 p_init_msg_list => OKL_API.G_FALSE,
3813 x_return_status => l_error_rec.error_type,
3814 x_msg_count => l_error_rec.msg_count,
3815 x_msg_data => l_error_rec.msg_data,
3816 p_subv_rec => p_subv_tbl(i));
3817 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
3818 l_error_rec.sqlcode := SQLCODE;
3819 load_error_tbl(l_error_rec, px_error_tbl);
3820 ELSE
3821 x_msg_count := l_error_rec.msg_count;
3822 x_msg_data := l_error_rec.msg_data;
3823 END IF;
3824 EXCEPTION
3825 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3826 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
3827 l_error_rec.sqlcode := SQLCODE;
3828 load_error_tbl(l_error_rec, px_error_tbl);
3829 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3830 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
3831 l_error_rec.sqlcode := SQLCODE;
3832 load_error_tbl(l_error_rec, px_error_tbl);
3833 WHEN OTHERS THEN
3834 l_error_rec.error_type := 'OTHERS';
3835 l_error_rec.sqlcode := SQLCODE;
3836 load_error_tbl(l_error_rec, px_error_tbl);
3837 END;
3838 EXIT WHEN (i = p_subv_tbl.LAST);
3839 i := p_subv_tbl.NEXT(i);
3840 END LOOP;
3841 END IF;
3842 -- Loop through the error_tbl to find the error with the highest severity
3843 -- and return it.
3844 x_return_status := find_highest_exception(px_error_tbl);
3845 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3846 EXCEPTION
3847 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3848 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3849 (
3850 l_api_name,
3851 G_PKG_NAME,
3852 'OKL_API.G_RET_STS_ERROR',
3853 x_msg_count,
3854 x_msg_data,
3855 '_PVT'
3856 );
3857 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3858 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3859 (
3860 l_api_name,
3861 G_PKG_NAME,
3862 'OKL_API.G_RET_STS_UNEXP_ERROR',
3863 x_msg_count,
3864 x_msg_data,
3865 '_PVT'
3866 );
3867 WHEN OTHERS THEN
3868 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3869 (
3870 l_api_name,
3871 G_PKG_NAME,
3872 'OTHERS',
3873 x_msg_count,
3874 x_msg_data,
3875 '_PVT'
3876 );
3877 END lock_row;
3878 --------------------------------------
3879 -- PL/SQL TBL lock_row for:SUBV_TBL --
3880 --------------------------------------
3881 PROCEDURE lock_row(
3882 p_api_version IN NUMBER,
3883 p_init_msg_list IN VARCHAR2,
3884 x_return_status OUT NOCOPY VARCHAR2,
3885 x_msg_count OUT NOCOPY NUMBER,
3886 x_msg_data OUT NOCOPY VARCHAR2,
3887 p_subv_tbl IN subv_tbl_type) IS
3888
3889 l_api_version CONSTANT NUMBER := 1;
3890 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
3891 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3892 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3893 BEGIN
3894 OKL_API.init_msg_list(p_init_msg_list);
3895 -- Make sure PL/SQL table has recrods in it before passing
3896 IF (p_subv_tbl.COUNT > 0) THEN
3897 lock_row(
3898 p_api_version => p_api_version,
3899 p_init_msg_list => OKL_API.G_FALSE,
3900 x_return_status => x_return_status,
3901 x_msg_count => x_msg_count,
3902 x_msg_data => x_msg_data,
3903 p_subv_tbl => p_subv_tbl,
3904 px_error_tbl => l_error_tbl);
3905 END IF;
3906 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3907 EXCEPTION
3908 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3909 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3910 (
3911 l_api_name,
3912 G_PKG_NAME,
3913 'OKL_API.G_RET_STS_ERROR',
3914 x_msg_count,
3915 x_msg_data,
3916 '_PVT'
3917 );
3918 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3919 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3920 (
3921 l_api_name,
3922 G_PKG_NAME,
3923 'OKL_API.G_RET_STS_UNEXP_ERROR',
3924 x_msg_count,
3925 x_msg_data,
3926 '_PVT'
3927 );
3928 WHEN OTHERS THEN
3929 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3930 (
3931 l_api_name,
3932 G_PKG_NAME,
3933 'OTHERS',
3934 x_msg_count,
3935 x_msg_data,
3936 '_PVT'
3937 );
3938 END lock_row;
3939 ---------------------------------------------------------------------------
3940 -- PROCEDURE update_row
3941 ---------------------------------------------------------------------------
3942 ------------------------------------
3943 -- update_row for:OKL_SUBSIDIES_B --
3944 ------------------------------------
3945 PROCEDURE update_row(
3946 p_init_msg_list IN VARCHAR2,
3947 x_return_status OUT NOCOPY VARCHAR2,
3948 x_msg_count OUT NOCOPY NUMBER,
3949 x_msg_data OUT NOCOPY VARCHAR2,
3950 p_subb_rec IN subb_rec_type,
3951 x_subb_rec OUT NOCOPY subb_rec_type) IS
3952
3953 l_api_version CONSTANT NUMBER := 1;
3954 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
3955 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3956 l_subb_rec subb_rec_type := p_subb_rec;
3957 l_def_subb_rec subb_rec_type;
3958 l_row_notfound BOOLEAN := TRUE;
3959 ----------------------------------
3960 -- FUNCTION populate_new_record --
3961 ----------------------------------
3962 FUNCTION populate_new_record (
3963 p_subb_rec IN subb_rec_type,
3964 x_subb_rec OUT NOCOPY subb_rec_type
3965 ) RETURN VARCHAR2 IS
3966 l_subb_rec subb_rec_type;
3967 l_row_notfound BOOLEAN := TRUE;
3968 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3969 BEGIN
3970 x_subb_rec := p_subb_rec;
3971 -- Get current database values
3972 l_subb_rec := get_rec(p_subb_rec, l_return_status);
3973 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
3974 IF (x_subb_rec.id = OKL_API.G_MISS_NUM)
3975 THEN
3976 x_subb_rec.id := l_subb_rec.id;
3977 END IF;
3978 IF (x_subb_rec.object_version_number = OKL_API.G_MISS_NUM)
3979 THEN
3980 x_subb_rec.object_version_number := l_subb_rec.object_version_number;
3981 END IF;
3982 IF (x_subb_rec.org_id = OKL_API.G_MISS_NUM)
3983 THEN
3984 x_subb_rec.org_id := l_subb_rec.org_id;
3985 END IF;
3986 IF (x_subb_rec.name = OKL_API.G_MISS_CHAR)
3987 THEN
3988 x_subb_rec.name := l_subb_rec.name;
3989 END IF;
3990 IF (x_subb_rec.effective_from_date = OKL_API.G_MISS_DATE)
3991 THEN
3992 x_subb_rec.effective_from_date := l_subb_rec.effective_from_date;
3993 END IF;
3994 IF (x_subb_rec.effective_to_date = OKL_API.G_MISS_DATE)
3995 THEN
3996 x_subb_rec.effective_to_date := l_subb_rec.effective_to_date;
3997 END IF;
3998 IF (x_subb_rec.expire_after_days = OKL_API.G_MISS_NUM)
3999 THEN
4000 x_subb_rec.expire_after_days := l_subb_rec.expire_after_days;
4001 END IF;
4002 IF (x_subb_rec.currency_code = OKL_API.G_MISS_CHAR)
4003 THEN
4004 x_subb_rec.currency_code := l_subb_rec.currency_code;
4005 END IF;
4006 IF (x_subb_rec.exclusive_yn = OKL_API.G_MISS_CHAR)
4007 THEN
4008 x_subb_rec.exclusive_yn := l_subb_rec.exclusive_yn;
4009 END IF;
4010 IF (x_subb_rec.applicable_to_release_yn = OKL_API.G_MISS_CHAR)
4011 THEN
4012 x_subb_rec.applicable_to_release_yn := l_subb_rec.applicable_to_release_yn;
4013 END IF;
4014 IF (x_subb_rec.subsidy_calc_basis = OKL_API.G_MISS_CHAR)
4015 THEN
4016 x_subb_rec.subsidy_calc_basis := l_subb_rec.subsidy_calc_basis;
4017 END IF;
4018 IF (x_subb_rec.amount = OKL_API.G_MISS_NUM)
4019 THEN
4020 x_subb_rec.amount := l_subb_rec.amount;
4021 END IF;
4022 IF (x_subb_rec.percent = OKL_API.G_MISS_NUM)
4023 THEN
4024 x_subb_rec.percent := l_subb_rec.percent;
4025 END IF;
4026 IF (x_subb_rec.formula_id = OKL_API.G_MISS_NUM)
4027 THEN
4028 x_subb_rec.formula_id := l_subb_rec.formula_id;
4029 END IF;
4030 IF (x_subb_rec.rate_points = OKL_API.G_MISS_NUM)
4031 THEN
4032 x_subb_rec.rate_points := l_subb_rec.rate_points;
4033 END IF;
4034 IF (x_subb_rec.maximum_term = OKL_API.G_MISS_NUM)
4035 THEN
4036 x_subb_rec.maximum_term := l_subb_rec.maximum_term;
4037 END IF;
4038 IF (x_subb_rec.vendor_id = OKL_API.G_MISS_NUM)
4039 THEN
4040 x_subb_rec.vendor_id := l_subb_rec.vendor_id;
4041 END IF;
4042 IF (x_subb_rec.accounting_method_code = OKL_API.G_MISS_CHAR)
4043 THEN
4044 x_subb_rec.accounting_method_code := l_subb_rec.accounting_method_code;
4045 END IF;
4046 IF (x_subb_rec.recourse_yn = OKL_API.G_MISS_CHAR)
4047 THEN
4048 x_subb_rec.recourse_yn := l_subb_rec.recourse_yn;
4049 END IF;
4050 IF (x_subb_rec.termination_refund_basis = OKL_API.G_MISS_CHAR)
4051 THEN
4052 x_subb_rec.termination_refund_basis := l_subb_rec.termination_refund_basis;
4053 END IF;
4054 IF (x_subb_rec.refund_formula_id = OKL_API.G_MISS_NUM)
4055 THEN
4056 x_subb_rec.refund_formula_id := l_subb_rec.refund_formula_id;
4057 END IF;
4058 IF (x_subb_rec.stream_type_id = OKL_API.G_MISS_NUM)
4059 THEN
4060 x_subb_rec.stream_type_id := l_subb_rec.stream_type_id;
4061 END IF;
4062 IF (x_subb_rec.receipt_method_code = OKL_API.G_MISS_CHAR)
4063 THEN
4064 x_subb_rec.receipt_method_code := l_subb_rec.receipt_method_code;
4065 END IF;
4066 IF (x_subb_rec.customer_visible_yn = OKL_API.G_MISS_CHAR)
4067 THEN
4068 x_subb_rec.customer_visible_yn := l_subb_rec.customer_visible_yn;
4069 END IF;
4070 IF (x_subb_rec.maximum_financed_amount = OKL_API.G_MISS_NUM)
4071 THEN
4072 x_subb_rec.maximum_financed_amount:= l_subb_rec.maximum_financed_amount;
4073 END IF;
4074 IF (x_subb_rec.maximum_subsidy_amount = OKL_API.G_MISS_NUM)
4075 THEN
4076 x_subb_rec.maximum_subsidy_amount:= l_subb_rec.maximum_subsidy_amount;
4077 END IF;
4078 --Start code changes for Subsidy by fmiao on 10/25/2004--
4079 IF (x_subb_rec.transfer_basis_code = OKL_API.G_MISS_CHAR)
4080 THEN
4081 x_subb_rec.transfer_basis_code := l_subb_rec.transfer_basis_code;
4082 END IF;
4083 --End code changes for Subsidy by fmiao on 10/25/2004--
4084 IF (x_subb_rec.attribute_category = OKL_API.G_MISS_CHAR)
4085 THEN
4086 x_subb_rec.attribute_category := l_subb_rec.attribute_category;
4087 END IF;
4088 IF (x_subb_rec.attribute1 = OKL_API.G_MISS_CHAR)
4089 THEN
4090 x_subb_rec.attribute1 := l_subb_rec.attribute1;
4091 END IF;
4092 IF (x_subb_rec.attribute2 = OKL_API.G_MISS_CHAR)
4093 THEN
4094 x_subb_rec.attribute2 := l_subb_rec.attribute2;
4095 END IF;
4096 IF (x_subb_rec.attribute3 = OKL_API.G_MISS_CHAR)
4097 THEN
4098 x_subb_rec.attribute3 := l_subb_rec.attribute3;
4099 END IF;
4100 IF (x_subb_rec.attribute4 = OKL_API.G_MISS_CHAR)
4101 THEN
4102 x_subb_rec.attribute4 := l_subb_rec.attribute4;
4103 END IF;
4104 IF (x_subb_rec.attribute5 = OKL_API.G_MISS_CHAR)
4105 THEN
4106 x_subb_rec.attribute5 := l_subb_rec.attribute5;
4107 END IF;
4108 IF (x_subb_rec.attribute6 = OKL_API.G_MISS_CHAR)
4109 THEN
4110 x_subb_rec.attribute6 := l_subb_rec.attribute6;
4111 END IF;
4112 IF (x_subb_rec.attribute7 = OKL_API.G_MISS_CHAR)
4113 THEN
4114 x_subb_rec.attribute7 := l_subb_rec.attribute7;
4115 END IF;
4116 IF (x_subb_rec.attribute8 = OKL_API.G_MISS_CHAR)
4117 THEN
4118 x_subb_rec.attribute8 := l_subb_rec.attribute8;
4119 END IF;
4120 IF (x_subb_rec.attribute9 = OKL_API.G_MISS_CHAR)
4121 THEN
4122 x_subb_rec.attribute9 := l_subb_rec.attribute9;
4123 END IF;
4124 IF (x_subb_rec.attribute10 = OKL_API.G_MISS_CHAR)
4125 THEN
4126 x_subb_rec.attribute10 := l_subb_rec.attribute10;
4127 END IF;
4128 IF (x_subb_rec.attribute11 = OKL_API.G_MISS_CHAR)
4129 THEN
4130 x_subb_rec.attribute11 := l_subb_rec.attribute11;
4131 END IF;
4132 IF (x_subb_rec.attribute12 = OKL_API.G_MISS_CHAR)
4133 THEN
4134 x_subb_rec.attribute12 := l_subb_rec.attribute12;
4135 END IF;
4136 IF (x_subb_rec.attribute13 = OKL_API.G_MISS_CHAR)
4137 THEN
4138 x_subb_rec.attribute13 := l_subb_rec.attribute13;
4139 END IF;
4140 IF (x_subb_rec.attribute14 = OKL_API.G_MISS_CHAR)
4141 THEN
4142 x_subb_rec.attribute14 := l_subb_rec.attribute14;
4143 END IF;
4144 IF (x_subb_rec.attribute15 = OKL_API.G_MISS_CHAR)
4145 THEN
4146 x_subb_rec.attribute15 := l_subb_rec.attribute15;
4147 END IF;
4148 IF (x_subb_rec.created_by = OKL_API.G_MISS_NUM)
4149 THEN
4150 x_subb_rec.created_by := l_subb_rec.created_by;
4151 END IF;
4152 IF (x_subb_rec.creation_date = OKL_API.G_MISS_DATE)
4153 THEN
4154 x_subb_rec.creation_date := l_subb_rec.creation_date;
4155 END IF;
4156 IF (x_subb_rec.last_updated_by = OKL_API.G_MISS_NUM)
4157 THEN
4158 x_subb_rec.last_updated_by := l_subb_rec.last_updated_by;
4159 END IF;
4160 IF (x_subb_rec.last_update_date = OKL_API.G_MISS_DATE)
4161 THEN
4162 x_subb_rec.last_update_date := l_subb_rec.last_update_date;
4163 END IF;
4164 IF (x_subb_rec.last_update_login = OKL_API.G_MISS_NUM)
4165 THEN
4166 x_subb_rec.last_update_login := l_subb_rec.last_update_login;
4167 END IF;
4168 -- sjalasut added new column for subsidy pools enhancement. start
4169 IF (x_subb_rec.subsidy_pool_id = OKL_API.G_MISS_NUM)
4170 THEN
4171 x_subb_rec.subsidy_pool_id := l_subb_rec.subsidy_pool_id;
4172 END IF;
4173 -- sjalasut added new column for subsidy pools enhancement. end
4174 END IF;
4175 RETURN(l_return_status);
4176 END populate_new_record;
4177 ----------------------------------------
4178 -- Set_Attributes for:OKL_SUBSIDIES_B --
4179 ----------------------------------------
4180 FUNCTION Set_Attributes (
4181 p_subb_rec IN subb_rec_type,
4182 x_subb_rec OUT NOCOPY subb_rec_type
4183 ) RETURN VARCHAR2 IS
4184 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4185 BEGIN
4186 x_subb_rec := p_subb_rec;
4187 x_subb_rec.OBJECT_VERSION_NUMBER := p_subb_rec.OBJECT_VERSION_NUMBER + 1;
4188 RETURN(l_return_status);
4189 END Set_Attributes;
4190 BEGIN
4191 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
4192 p_init_msg_list,
4193 '_PVT',
4194 x_return_status);
4195 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4196 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4197 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4198 RAISE OKL_API.G_EXCEPTION_ERROR;
4199 END IF;
4200 --- Setting item attributes
4201 l_return_status := Set_Attributes(
4202 p_subb_rec, -- IN
4203 l_subb_rec); -- OUT
4204 --- If any errors happen abort API
4205 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4206 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4207 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4208 RAISE OKL_API.G_EXCEPTION_ERROR;
4209 END IF;
4210 l_return_status := populate_new_record(l_subb_rec, l_def_subb_rec);
4211 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4212 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4213 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4214 RAISE OKL_API.G_EXCEPTION_ERROR;
4215 END IF;
4216 UPDATE OKL_SUBSIDIES_B
4217 SET OBJECT_VERSION_NUMBER = l_def_subb_rec.object_version_number,
4218 ORG_ID = l_def_subb_rec.org_id,
4219 NAME = l_def_subb_rec.name,
4220 EFFECTIVE_FROM_DATE = l_def_subb_rec.effective_from_date,
4221 EFFECTIVE_TO_DATE = l_def_subb_rec.effective_to_date,
4222 EXPIRE_AFTER_DAYS = l_def_subb_rec.expire_after_days,
4223 CURRENCY_CODE = l_def_subb_rec.currency_code,
4224 EXCLUSIVE_YN = l_def_subb_rec.exclusive_yn,
4225 APPLICABLE_TO_RELEASE_YN = l_def_subb_rec.applicable_to_release_yn,
4226 SUBSIDY_CALC_BASIS = l_def_subb_rec.subsidy_calc_basis,
4227 AMOUNT = l_def_subb_rec.amount,
4228 PERCENT = l_def_subb_rec.percent,
4229 FORMULA_ID = l_def_subb_rec.formula_id,
4230 rate_points = l_def_subb_rec.rate_points,
4231 MAXIMUM_TERM = l_def_subb_rec.maximum_term,
4232 VENDOR_ID = l_def_subb_rec.vendor_id,
4233 ACCOUNTING_METHOD_CODE = l_def_subb_rec.accounting_method_code,
4234 RECOURSE_YN = l_def_subb_rec.recourse_yn,
4235 TERMINATION_REFUND_BASIS = l_def_subb_rec.termination_refund_basis,
4236 REFUND_FORMULA_ID = l_def_subb_rec.refund_formula_id,
4237 STREAM_TYPE_ID = l_def_subb_rec.stream_type_id,
4238 RECEIPT_METHOD_CODE = l_def_subb_rec.receipt_method_code,
4239 CUSTOMER_VISIBLE_YN = l_def_subb_rec.customer_visible_yn,
4240 MAXIMUM_FINANCED_AMOUNT = l_def_subb_rec.maximum_financed_amount,
4241 MAXIMUM_SUBSIDY_AMOUNT = l_def_subb_rec.maximum_subsidy_amount,
4242 --Start code changes for Subsidy by fmiao on 10/25/2004--
4243 TRANSFER_BASIS_CODE = l_def_subb_rec.transfer_basis_code,
4244 --End code changes for Subsidy by fmiao on 10/25/2004--
4245 ATTRIBUTE_CATEGORY = l_def_subb_rec.attribute_category,
4246 ATTRIBUTE1 = l_def_subb_rec.attribute1,
4247 ATTRIBUTE2 = l_def_subb_rec.attribute2,
4248 ATTRIBUTE3 = l_def_subb_rec.attribute3,
4249 ATTRIBUTE4 = l_def_subb_rec.attribute4,
4250 ATTRIBUTE5 = l_def_subb_rec.attribute5,
4251 ATTRIBUTE6 = l_def_subb_rec.attribute6,
4252 ATTRIBUTE7 = l_def_subb_rec.attribute7,
4253 ATTRIBUTE8 = l_def_subb_rec.attribute8,
4254 ATTRIBUTE9 = l_def_subb_rec.attribute9,
4255 ATTRIBUTE10 = l_def_subb_rec.attribute10,
4256 ATTRIBUTE11 = l_def_subb_rec.attribute11,
4257 ATTRIBUTE12 = l_def_subb_rec.attribute12,
4258 ATTRIBUTE13 = l_def_subb_rec.attribute13,
4259 ATTRIBUTE14 = l_def_subb_rec.attribute14,
4260 ATTRIBUTE15 = l_def_subb_rec.attribute15,
4261 CREATED_BY = l_def_subb_rec.created_by,
4262 CREATION_DATE = l_def_subb_rec.creation_date,
4263 LAST_UPDATED_BY = l_def_subb_rec.last_updated_by,
4264 LAST_UPDATE_DATE = l_def_subb_rec.last_update_date,
4265 LAST_UPDATE_LOGIN = l_def_subb_rec.last_update_login,
4266 SUBSIDY_POOL_ID = l_def_subb_rec.subsidy_pool_id
4267 WHERE ID = l_def_subb_rec.id;
4268
4269 x_subb_rec := l_subb_rec;
4270 x_return_status := l_return_status;
4271 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4272 EXCEPTION
4273 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4274 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4275 (
4276 l_api_name,
4277 G_PKG_NAME,
4278 'OKL_API.G_RET_STS_ERROR',
4279 x_msg_count,
4280 x_msg_data,
4281 '_PVT'
4282 );
4283 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4284 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4285 (
4286 l_api_name,
4287 G_PKG_NAME,
4288 'OKL_API.G_RET_STS_UNEXP_ERROR',
4289 x_msg_count,
4290 x_msg_data,
4291 '_PVT'
4292 );
4293 WHEN OTHERS THEN
4294 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4295 (
4296 l_api_name,
4297 G_PKG_NAME,
4298 'OTHERS',
4299 x_msg_count,
4300 x_msg_data,
4301 '_PVT'
4302 );
4303 END update_row;
4304 -------------------------------------
4305 -- update_row for:OKL_SUBSIDIES_TL --
4306 -------------------------------------
4307 PROCEDURE update_row(
4308 p_init_msg_list IN VARCHAR2,
4309 x_return_status OUT NOCOPY VARCHAR2,
4310 x_msg_count OUT NOCOPY NUMBER,
4311 x_msg_data OUT NOCOPY VARCHAR2,
4312 p_subt_rec IN subt_rec_type,
4313 x_subt_rec OUT NOCOPY subt_rec_type) IS
4314
4315 l_api_version CONSTANT NUMBER := 1;
4316 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
4317 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4318 l_subt_rec subt_rec_type := p_subt_rec;
4319 l_def_subt_rec subt_rec_type;
4320 l_row_notfound BOOLEAN := TRUE;
4321 ----------------------------------
4322 -- FUNCTION populate_new_record --
4323 ----------------------------------
4324 FUNCTION populate_new_record (
4325 p_subt_rec IN subt_rec_type,
4326 x_subt_rec OUT NOCOPY subt_rec_type
4327 ) RETURN VARCHAR2 IS
4328 l_subt_rec subt_rec_type;
4329 l_row_notfound BOOLEAN := TRUE;
4330 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4331 BEGIN
4332 x_subt_rec := p_subt_rec;
4333 -- Get current database values
4334 l_subt_rec := get_rec(p_subt_rec, l_return_status);
4335 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
4336 IF (x_subt_rec.id = OKL_API.G_MISS_NUM)
4337 THEN
4338 x_subt_rec.id := l_subt_rec.id;
4339 END IF;
4340 IF (x_subt_rec.short_description = OKL_API.G_MISS_CHAR)
4341 THEN
4342 x_subt_rec.short_description := l_subt_rec.short_description;
4343 END IF;
4344 IF (x_subt_rec.description = OKL_API.G_MISS_CHAR)
4345 THEN
4346 x_subt_rec.description := l_subt_rec.description;
4347 END IF;
4348 IF (x_subt_rec.language = OKL_API.G_MISS_CHAR)
4349 THEN
4350 x_subt_rec.language := l_subt_rec.language;
4351 END IF;
4352 IF (x_subt_rec.source_lang = OKL_API.G_MISS_CHAR)
4353 THEN
4354 x_subt_rec.source_lang := l_subt_rec.source_lang;
4355 END IF;
4356 IF (x_subt_rec.sfwt_flag = OKL_API.G_MISS_CHAR)
4357 THEN
4358 x_subt_rec.sfwt_flag := l_subt_rec.sfwt_flag;
4359 END IF;
4360 IF (x_subt_rec.created_by = OKL_API.G_MISS_NUM)
4361 THEN
4362 x_subt_rec.created_by := l_subt_rec.created_by;
4363 END IF;
4364 IF (x_subt_rec.creation_date = OKL_API.G_MISS_DATE)
4365 THEN
4366 x_subt_rec.creation_date := l_subt_rec.creation_date;
4367 END IF;
4368 IF (x_subt_rec.last_updated_by = OKL_API.G_MISS_NUM)
4369 THEN
4370 x_subt_rec.last_updated_by := l_subt_rec.last_updated_by;
4371 END IF;
4372 IF (x_subt_rec.last_update_date = OKL_API.G_MISS_DATE)
4373 THEN
4374 x_subt_rec.last_update_date := l_subt_rec.last_update_date;
4375 END IF;
4376 IF (x_subt_rec.last_update_login = OKL_API.G_MISS_NUM)
4377 THEN
4378 x_subt_rec.last_update_login := l_subt_rec.last_update_login;
4379 END IF;
4380 END IF;
4381 RETURN(l_return_status);
4382 END populate_new_record;
4383 -----------------------------------------
4384 -- Set_Attributes for:OKL_SUBSIDIES_TL --
4385 -----------------------------------------
4386 FUNCTION Set_Attributes (
4387 p_subt_rec IN subt_rec_type,
4388 x_subt_rec OUT NOCOPY subt_rec_type
4389 ) RETURN VARCHAR2 IS
4390 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4391 BEGIN
4392 x_subt_rec := p_subt_rec;
4393 x_subt_rec.LANGUAGE := USERENV('LANG');
4394 x_subt_rec.LANGUAGE := USERENV('LANG');
4395 RETURN(l_return_status);
4396 END Set_Attributes;
4397 BEGIN
4398 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
4399 p_init_msg_list,
4400 '_PVT',
4401 x_return_status);
4402 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4403 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4404 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4405 RAISE OKL_API.G_EXCEPTION_ERROR;
4406 END IF;
4407 --- Setting item attributes
4408 l_return_status := Set_Attributes(
4409 p_subt_rec, -- IN
4410 l_subt_rec); -- OUT
4411 --- If any errors happen abort API
4412 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4413 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4414 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4415 RAISE OKL_API.G_EXCEPTION_ERROR;
4416 END IF;
4417 l_return_status := populate_new_record(l_subt_rec, l_def_subt_rec);
4418 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4419 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4420 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4421 RAISE OKL_API.G_EXCEPTION_ERROR;
4422 END IF;
4423 UPDATE OKL_SUBSIDIES_TL
4424 SET SHORT_DESCRIPTION = l_def_subt_rec.short_description,
4425 DESCRIPTION = l_def_subt_rec.description,
4426 --Bug# 3641933 :
4427 SOURCE_LANG = l_def_subt_rec.source_lang,
4428 CREATED_BY = l_def_subt_rec.created_by,
4429 CREATION_DATE = l_def_subt_rec.creation_date,
4430 LAST_UPDATED_BY = l_def_subt_rec.last_updated_by,
4431 LAST_UPDATE_DATE = l_def_subt_rec.last_update_date,
4432 LAST_UPDATE_LOGIN = l_def_subt_rec.last_update_login
4433 WHERE ID = l_def_subt_rec.id
4434 --Bug# 3641933 :
4435 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);
4436 --AND SOURCE_LANG = USERENV('LANG');
4437
4438 UPDATE OKL_SUBSIDIES_TL
4439 SET SFWT_FLAG = 'Y'
4440 WHERE ID = l_def_subt_rec.id
4441 AND SOURCE_LANG <> USERENV('LANG');
4442
4443 x_subt_rec := l_subt_rec;
4444 x_return_status := l_return_status;
4445 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4446 EXCEPTION
4447 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4448 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4449 (
4450 l_api_name,
4451 G_PKG_NAME,
4452 'OKL_API.G_RET_STS_ERROR',
4453 x_msg_count,
4454 x_msg_data,
4455 '_PVT'
4456 );
4457 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4458 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4459 (
4460 l_api_name,
4461 G_PKG_NAME,
4462 'OKL_API.G_RET_STS_UNEXP_ERROR',
4463 x_msg_count,
4464 x_msg_data,
4465 '_PVT'
4466 );
4467 WHEN OTHERS THEN
4468 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4469 (
4470 l_api_name,
4471 G_PKG_NAME,
4472 'OTHERS',
4473 x_msg_count,
4474 x_msg_data,
4475 '_PVT'
4476 );
4477 END update_row;
4478 ------------------------------------
4479 -- update_row for:OKL_SUBSIDIES_V --
4480 ------------------------------------
4481 PROCEDURE update_row(
4482 p_api_version IN NUMBER,
4483 p_init_msg_list IN VARCHAR2,
4484 x_return_status OUT NOCOPY VARCHAR2,
4485 x_msg_count OUT NOCOPY NUMBER,
4486 x_msg_data OUT NOCOPY VARCHAR2,
4487 p_subv_rec IN subv_rec_type,
4488 x_subv_rec OUT NOCOPY subv_rec_type) IS
4489
4490 l_api_version CONSTANT NUMBER := 1;
4491 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
4492 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4493 l_subv_rec subv_rec_type := p_subv_rec;
4494 l_def_subv_rec subv_rec_type;
4495 l_db_subv_rec subv_rec_type;
4496 l_subb_rec subb_rec_type;
4497 lx_subb_rec subb_rec_type;
4498 l_subt_rec subt_rec_type;
4499 lx_subt_rec subt_rec_type;
4500 -------------------------------
4501 -- FUNCTION fill_who_columns --
4502 -------------------------------
4503 FUNCTION fill_who_columns (
4504 p_subv_rec IN subv_rec_type
4505 ) RETURN subv_rec_type IS
4506 l_subv_rec subv_rec_type := p_subv_rec;
4507 BEGIN
4508 l_subv_rec.LAST_UPDATE_DATE := SYSDATE;
4509 l_subv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4510 l_subv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
4511 RETURN(l_subv_rec);
4512 END fill_who_columns;
4513 ----------------------------------
4514 -- FUNCTION populate_new_record --
4515 ----------------------------------
4516 FUNCTION populate_new_record (
4517 p_subv_rec IN subv_rec_type,
4518 x_subv_rec OUT NOCOPY subv_rec_type
4519 ) RETURN VARCHAR2 IS
4520 l_row_notfound BOOLEAN := TRUE;
4521 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4522 BEGIN
4523 x_subv_rec := p_subv_rec;
4524 -- Get current database values
4525 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
4526 -- so it may be verified through LOCK_ROW.
4527 l_db_subv_rec := get_rec(p_subv_rec, l_return_status);
4528 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
4529 IF (x_subv_rec.id = OKL_API.G_MISS_NUM)
4530 THEN
4531 x_subv_rec.id := l_db_subv_rec.id;
4532 END IF;
4533 IF (x_subv_rec.sfwt_flag = OKL_API.G_MISS_CHAR)
4534 THEN
4535 x_subv_rec.sfwt_flag := l_db_subv_rec.sfwt_flag;
4536 END IF;
4537 IF (x_subv_rec.org_id = OKL_API.G_MISS_NUM)
4538 THEN
4539 x_subv_rec.org_id := l_db_subv_rec.org_id;
4540 END IF;
4541 IF (x_subv_rec.name = OKL_API.G_MISS_CHAR)
4542 THEN
4543 x_subv_rec.name := l_db_subv_rec.name;
4544 END IF;
4545 IF (x_subv_rec.short_description = OKL_API.G_MISS_CHAR)
4546 THEN
4547 x_subv_rec.short_description := l_db_subv_rec.short_description;
4548 END IF;
4549 IF (x_subv_rec.description = OKL_API.G_MISS_CHAR)
4550 THEN
4551 x_subv_rec.description := l_db_subv_rec.description;
4552 END IF;
4553 IF (x_subv_rec.effective_from_date = OKL_API.G_MISS_DATE)
4554 THEN
4555 x_subv_rec.effective_from_date := l_db_subv_rec.effective_from_date;
4556 END IF;
4557 IF (x_subv_rec.effective_to_date = OKL_API.G_MISS_DATE)
4558 THEN
4559 x_subv_rec.effective_to_date := l_db_subv_rec.effective_to_date;
4560 END IF;
4561 IF (x_subv_rec.expire_after_days = OKL_API.G_MISS_NUM)
4562 THEN
4563 x_subv_rec.expire_after_days := l_db_subv_rec.expire_after_days;
4564 END IF;
4565 IF (x_subv_rec.currency_code = OKL_API.G_MISS_CHAR)
4566 THEN
4567 x_subv_rec.currency_code := l_db_subv_rec.currency_code;
4568 END IF;
4569 IF (x_subv_rec.exclusive_yn = OKL_API.G_MISS_CHAR)
4570 THEN
4571 x_subv_rec.exclusive_yn := l_db_subv_rec.exclusive_yn;
4572 END IF;
4573 IF (x_subv_rec.applicable_to_release_yn = OKL_API.G_MISS_CHAR)
4574 THEN
4575 x_subv_rec.applicable_to_release_yn := l_db_subv_rec.applicable_to_release_yn;
4576 END IF;
4577 IF (x_subv_rec.subsidy_calc_basis = OKL_API.G_MISS_CHAR)
4578 THEN
4579 x_subv_rec.subsidy_calc_basis := l_db_subv_rec.subsidy_calc_basis;
4580 END IF;
4581 IF (x_subv_rec.amount = OKL_API.G_MISS_NUM)
4582 THEN
4583 x_subv_rec.amount := l_db_subv_rec.amount;
4584 END IF;
4585 IF (x_subv_rec.percent = OKL_API.G_MISS_NUM)
4586 THEN
4587 x_subv_rec.percent := l_db_subv_rec.percent;
4588 END IF;
4589 IF (x_subv_rec.formula_id = OKL_API.G_MISS_NUM)
4590 THEN
4591 x_subv_rec.formula_id := l_db_subv_rec.formula_id;
4592 END IF;
4593 IF (x_subv_rec.rate_points = OKL_API.G_MISS_NUM)
4594 THEN
4595 x_subv_rec.rate_points := l_db_subv_rec.rate_points;
4596 END IF;
4597 IF (x_subv_rec.maximum_term = OKL_API.G_MISS_NUM)
4598 THEN
4599 x_subv_rec.maximum_term := l_db_subv_rec.maximum_term;
4600 END IF;
4601 IF (x_subv_rec.vendor_id = OKL_API.G_MISS_NUM)
4602 THEN
4603 x_subv_rec.vendor_id := l_db_subv_rec.vendor_id;
4604 END IF;
4605 IF (x_subv_rec.accounting_method_code = OKL_API.G_MISS_CHAR)
4606 THEN
4607 x_subv_rec.accounting_method_code := l_db_subv_rec.accounting_method_code;
4608 END IF;
4609 IF (x_subv_rec.recourse_yn = OKL_API.G_MISS_CHAR)
4610 THEN
4611 x_subv_rec.recourse_yn := l_db_subv_rec.recourse_yn;
4612 END IF;
4613 IF (x_subv_rec.termination_refund_basis = OKL_API.G_MISS_CHAR)
4614 THEN
4615 x_subv_rec.termination_refund_basis := l_db_subv_rec.termination_refund_basis;
4616 END IF;
4617 IF (x_subv_rec.refund_formula_id = OKL_API.G_MISS_NUM)
4618 THEN
4619 x_subv_rec.refund_formula_id := l_db_subv_rec.refund_formula_id;
4620 END IF;
4621 IF (x_subv_rec.stream_type_id = OKL_API.G_MISS_NUM)
4622 THEN
4623 x_subv_rec.stream_type_id := l_db_subv_rec.stream_type_id;
4624 END IF;
4625 IF (x_subv_rec.receipt_method_code = OKL_API.G_MISS_CHAR)
4626 THEN
4627 x_subv_rec.receipt_method_code := l_db_subv_rec.receipt_method_code;
4628 END IF;
4629 IF (x_subv_rec.customer_visible_yn = OKL_API.G_MISS_CHAR)
4630 THEN
4631 x_subv_rec.customer_visible_yn := l_db_subv_rec.customer_visible_yn;
4632 END IF;
4633 IF (x_subv_rec.maximum_financed_amount = OKL_API.G_MISS_NUM)
4634 THEN
4635 x_subv_rec.maximum_financed_amount := l_db_subv_rec.maximum_financed_amount;
4636 END IF;
4637 IF (x_subv_rec.maximum_subsidy_amount = OKL_API.G_MISS_NUM)
4638 THEN
4639 x_subv_rec.maximum_subsidy_amount := l_db_subv_rec.maximum_subsidy_amount;
4640 END IF;
4641 --Start code changes for Subsidy by fmiao on 10/25/2004--
4642 IF (x_subv_rec.transfer_basis_code = OKL_API.G_MISS_CHAR)
4643 THEN
4644 x_subv_rec.transfer_basis_code := l_db_subv_rec.transfer_basis_code;
4645 END IF;
4646 --End code changes for Subsidy by fmiao on 10/25/2004--
4647 IF (x_subv_rec.attribute_category = OKL_API.G_MISS_CHAR)
4648 THEN
4649 x_subv_rec.attribute_category := l_db_subv_rec.attribute_category;
4650 END IF;
4651 IF (x_subv_rec.attribute1 = OKL_API.G_MISS_CHAR)
4652 THEN
4653 x_subv_rec.attribute1 := l_db_subv_rec.attribute1;
4654 END IF;
4655 IF (x_subv_rec.attribute2 = OKL_API.G_MISS_CHAR)
4656 THEN
4657 x_subv_rec.attribute2 := l_db_subv_rec.attribute2;
4658 END IF;
4659 IF (x_subv_rec.attribute3 = OKL_API.G_MISS_CHAR)
4660 THEN
4661 x_subv_rec.attribute3 := l_db_subv_rec.attribute3;
4662 END IF;
4663 IF (x_subv_rec.attribute4 = OKL_API.G_MISS_CHAR)
4664 THEN
4665 x_subv_rec.attribute4 := l_db_subv_rec.attribute4;
4666 END IF;
4667 IF (x_subv_rec.attribute5 = OKL_API.G_MISS_CHAR)
4668 THEN
4669 x_subv_rec.attribute5 := l_db_subv_rec.attribute5;
4670 END IF;
4671 IF (x_subv_rec.attribute6 = OKL_API.G_MISS_CHAR)
4672 THEN
4673 x_subv_rec.attribute6 := l_db_subv_rec.attribute6;
4674 END IF;
4675 IF (x_subv_rec.attribute7 = OKL_API.G_MISS_CHAR)
4676 THEN
4677 x_subv_rec.attribute7 := l_db_subv_rec.attribute7;
4678 END IF;
4679 IF (x_subv_rec.attribute8 = OKL_API.G_MISS_CHAR)
4680 THEN
4681 x_subv_rec.attribute8 := l_db_subv_rec.attribute8;
4682 END IF;
4683 IF (x_subv_rec.attribute9 = OKL_API.G_MISS_CHAR)
4684 THEN
4685 x_subv_rec.attribute9 := l_db_subv_rec.attribute9;
4686 END IF;
4687 IF (x_subv_rec.attribute10 = OKL_API.G_MISS_CHAR)
4688 THEN
4689 x_subv_rec.attribute10 := l_db_subv_rec.attribute10;
4690 END IF;
4691 IF (x_subv_rec.attribute11 = OKL_API.G_MISS_CHAR)
4692 THEN
4693 x_subv_rec.attribute11 := l_db_subv_rec.attribute11;
4694 END IF;
4695 IF (x_subv_rec.attribute12 = OKL_API.G_MISS_CHAR)
4696 THEN
4697 x_subv_rec.attribute12 := l_db_subv_rec.attribute12;
4698 END IF;
4699 IF (x_subv_rec.attribute13 = OKL_API.G_MISS_CHAR)
4700 THEN
4701 x_subv_rec.attribute13 := l_db_subv_rec.attribute13;
4702 END IF;
4703 IF (x_subv_rec.attribute14 = OKL_API.G_MISS_CHAR)
4704 THEN
4705 x_subv_rec.attribute14 := l_db_subv_rec.attribute14;
4706 END IF;
4707 IF (x_subv_rec.attribute15 = OKL_API.G_MISS_CHAR)
4708 THEN
4709 x_subv_rec.attribute15 := l_db_subv_rec.attribute15;
4710 END IF;
4711 IF (x_subv_rec.created_by = OKL_API.G_MISS_NUM)
4712 THEN
4713 x_subv_rec.created_by := l_db_subv_rec.created_by;
4714 END IF;
4715 IF (x_subv_rec.creation_date = OKL_API.G_MISS_DATE)
4716 THEN
4717 x_subv_rec.creation_date := l_db_subv_rec.creation_date;
4718 END IF;
4719 IF (x_subv_rec.last_updated_by = OKL_API.G_MISS_NUM)
4720 THEN
4721 x_subv_rec.last_updated_by := l_db_subv_rec.last_updated_by;
4722 END IF;
4723 IF (x_subv_rec.last_update_date = OKL_API.G_MISS_DATE)
4724 THEN
4725 x_subv_rec.last_update_date := l_db_subv_rec.last_update_date;
4726 END IF;
4727 IF (x_subv_rec.last_update_login = OKL_API.G_MISS_NUM)
4728 THEN
4729 x_subv_rec.last_update_login := l_db_subv_rec.last_update_login;
4730 END IF;
4731 -- sjalasut added new column for subsidy pools enhancement. start
4732 IF (x_subv_rec.subsidy_pool_id = OKL_API.G_MISS_NUM)
4733 THEN
4734 x_subv_rec.subsidy_pool_id := l_db_subv_rec.subsidy_pool_id;
4735 END IF;
4736 -- sjalasut added new column for subsidy pools enhancement. end
4737 END IF;
4738 RETURN(l_return_status);
4739 END populate_new_record;
4740 ----------------------------------------
4741 -- Set_Attributes for:OKL_SUBSIDIES_V --
4742 ----------------------------------------
4743 FUNCTION Set_Attributes (
4744 p_subv_rec IN subv_rec_type,
4745 x_subv_rec OUT NOCOPY subv_rec_type
4746 ) RETURN VARCHAR2 IS
4747 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4748 BEGIN
4749 x_subv_rec := p_subv_rec;
4750 RETURN(l_return_status);
4751 END Set_Attributes;
4752 BEGIN
4753 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
4754 G_PKG_NAME,
4755 p_init_msg_list,
4756 l_api_version,
4757 p_api_version,
4758 '_PVT',
4759 x_return_status);
4760 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4761 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4762 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4763 RAISE OKL_API.G_EXCEPTION_ERROR;
4764 END IF;
4765 --- Setting item attributes
4766 l_return_status := Set_Attributes(
4767 p_subv_rec, -- IN
4768 x_subv_rec); -- OUT
4769 --- If any errors happen abort API
4770 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4771 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4772 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4773 RAISE OKL_API.G_EXCEPTION_ERROR;
4774 END IF;
4775 l_return_status := populate_new_record(l_subv_rec, l_def_subv_rec);
4776 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4777 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4778 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4779 RAISE OKL_API.G_EXCEPTION_ERROR;
4780 END IF;
4781 l_def_subv_rec := fill_who_columns(l_def_subv_rec);
4782 --- Validate all non-missing attributes (Item Level Validation)
4783 l_return_status := Validate_Attributes(l_def_subv_rec);
4784 --- If any errors happen abort API
4785 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4786 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4787 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4788 RAISE OKL_API.G_EXCEPTION_ERROR;
4789 END IF;
4790 l_return_status := Validate_Record(l_def_subv_rec, l_db_subv_rec);
4791 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4792 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4793 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4794 RAISE OKL_API.G_EXCEPTION_ERROR;
4795 END IF;
4796 /****Commented**********
4797 --avsingh
4798 -- Lock the Record
4799 lock_row(
4800 p_api_version => p_api_version,
4801 p_init_msg_list => p_init_msg_list,
4802 x_return_status => l_return_status,
4803 x_msg_count => x_msg_count,
4804 x_msg_data => x_msg_data,
4805 p_subv_rec => p_subv_rec);
4806 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4807 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4808 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4809 RAISE OKL_API.G_EXCEPTION_ERROR;
4810 END IF;
4811 ***********************/
4812
4813 -----------------------------------------
4814 -- Move VIEW record to "Child" records --
4815 -----------------------------------------
4816 migrate(l_def_subv_rec, l_subb_rec);
4817 migrate(l_def_subv_rec, l_subt_rec);
4818 -----------------------------------------------
4819 -- Call the UPDATE_ROW for each child record --
4820 -----------------------------------------------
4821 update_row(
4822 p_init_msg_list,
4823 l_return_status,
4824 x_msg_count,
4825 x_msg_data,
4826 l_subb_rec,
4827 lx_subb_rec
4828 );
4829 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4831 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4832 RAISE OKL_API.G_EXCEPTION_ERROR;
4833 END IF;
4834 migrate(lx_subb_rec, l_def_subv_rec);
4835 update_row(
4836 p_init_msg_list,
4837 l_return_status,
4838 x_msg_count,
4839 x_msg_data,
4840 l_subt_rec,
4841 lx_subt_rec
4842 );
4843 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4844 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4845 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4846 RAISE OKL_API.G_EXCEPTION_ERROR;
4847 END IF;
4848 migrate(lx_subt_rec, l_def_subv_rec);
4849 x_subv_rec := l_def_subv_rec;
4850 x_return_status := l_return_status;
4851 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4852 EXCEPTION
4853 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4854 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4855 (
4856 l_api_name,
4857 G_PKG_NAME,
4858 'OKL_API.G_RET_STS_ERROR',
4859 x_msg_count,
4860 x_msg_data,
4861 '_PVT'
4862 );
4863 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4864 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4865 (
4866 l_api_name,
4867 G_PKG_NAME,
4868 'OKL_API.G_RET_STS_UNEXP_ERROR',
4869 x_msg_count,
4870 x_msg_data,
4871 '_PVT'
4872 );
4873 WHEN OTHERS THEN
4874 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4875 (
4876 l_api_name,
4877 G_PKG_NAME,
4878 'OTHERS',
4879 x_msg_count,
4880 x_msg_data,
4881 '_PVT'
4882 );
4883 END update_row;
4884 ----------------------------------------
4885 -- PL/SQL TBL update_row for:subv_tbl --
4886 ----------------------------------------
4887 PROCEDURE update_row(
4888 p_api_version IN NUMBER,
4889 p_init_msg_list IN VARCHAR2,
4890 x_return_status OUT NOCOPY VARCHAR2,
4891 x_msg_count OUT NOCOPY NUMBER,
4892 x_msg_data OUT NOCOPY VARCHAR2,
4893 p_subv_tbl IN subv_tbl_type,
4894 x_subv_tbl OUT NOCOPY subv_tbl_type,
4895 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
4896
4897 l_api_version CONSTANT NUMBER := 1;
4898 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
4899 i NUMBER := 0;
4900 BEGIN
4901 OKL_API.init_msg_list(p_init_msg_list);
4902 -- Make sure PL/SQL table has records in it before passing
4903 IF (p_subv_tbl.COUNT > 0) THEN
4904 i := p_subv_tbl.FIRST;
4905 LOOP
4906 DECLARE
4907 l_error_rec OKL_API.ERROR_REC_TYPE;
4908 BEGIN
4909 l_error_rec.api_name := l_api_name;
4910 l_error_rec.api_package := G_PKG_NAME;
4911 l_error_rec.idx := i;
4912 update_row (
4913 p_api_version => p_api_version,
4914 p_init_msg_list => OKL_API.G_FALSE,
4915 x_return_status => l_error_rec.error_type,
4916 x_msg_count => l_error_rec.msg_count,
4917 x_msg_data => l_error_rec.msg_data,
4918 p_subv_rec => p_subv_tbl(i),
4919 x_subv_rec => x_subv_tbl(i));
4920 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
4921 l_error_rec.sqlcode := SQLCODE;
4922 load_error_tbl(l_error_rec, px_error_tbl);
4923 ELSE
4924 x_msg_count := l_error_rec.msg_count;
4925 x_msg_data := l_error_rec.msg_data;
4926 END IF;
4927 EXCEPTION
4928 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4929 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
4930 l_error_rec.sqlcode := SQLCODE;
4931 load_error_tbl(l_error_rec, px_error_tbl);
4932 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4933 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
4934 l_error_rec.sqlcode := SQLCODE;
4935 load_error_tbl(l_error_rec, px_error_tbl);
4936 WHEN OTHERS THEN
4937 l_error_rec.error_type := 'OTHERS';
4938 l_error_rec.sqlcode := SQLCODE;
4939 load_error_tbl(l_error_rec, px_error_tbl);
4940 END;
4941 EXIT WHEN (i = p_subv_tbl.LAST);
4942 i := p_subv_tbl.NEXT(i);
4943 END LOOP;
4944 END IF;
4945 -- Loop through the error_tbl to find the error with the highest severity
4946 -- and return it.
4947 x_return_status := find_highest_exception(px_error_tbl);
4948 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4949 EXCEPTION
4950 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4951 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4952 (
4953 l_api_name,
4954 G_PKG_NAME,
4955 'OKL_API.G_RET_STS_ERROR',
4956 x_msg_count,
4957 x_msg_data,
4958 '_PVT'
4959 );
4960 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4961 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4962 (
4963 l_api_name,
4964 G_PKG_NAME,
4965 'OKL_API.G_RET_STS_UNEXP_ERROR',
4966 x_msg_count,
4967 x_msg_data,
4968 '_PVT'
4969 );
4970 WHEN OTHERS THEN
4971 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4972 (
4973 l_api_name,
4974 G_PKG_NAME,
4975 'OTHERS',
4976 x_msg_count,
4977 x_msg_data,
4978 '_PVT'
4979 );
4980 END update_row;
4981
4982 ----------------------------------------
4983 -- PL/SQL TBL update_row for:SUBV_TBL --
4984 ----------------------------------------
4985 PROCEDURE update_row(
4986 p_api_version IN NUMBER,
4987 p_init_msg_list IN VARCHAR2,
4988 x_return_status OUT NOCOPY VARCHAR2,
4989 x_msg_count OUT NOCOPY NUMBER,
4990 x_msg_data OUT NOCOPY VARCHAR2,
4991 p_subv_tbl IN subv_tbl_type,
4992 x_subv_tbl OUT NOCOPY subv_tbl_type) IS
4993
4994 l_api_version CONSTANT NUMBER := 1;
4995 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
4996 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4997 l_error_tbl OKL_API.ERROR_TBL_TYPE;
4998 BEGIN
4999 OKL_API.init_msg_list(p_init_msg_list);
5000 -- Make sure PL/SQL table has records in it before passing
5001 IF (p_subv_tbl.COUNT > 0) THEN
5002 update_row (
5003 p_api_version => p_api_version,
5004 p_init_msg_list => OKL_API.G_FALSE,
5005 x_return_status => x_return_status,
5006 x_msg_count => x_msg_count,
5007 x_msg_data => x_msg_data,
5008 p_subv_tbl => p_subv_tbl,
5009 x_subv_tbl => x_subv_tbl,
5010 px_error_tbl => l_error_tbl);
5011 END IF;
5012 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5013 EXCEPTION
5014 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5015 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5016 (
5017 l_api_name,
5018 G_PKG_NAME,
5019 'OKL_API.G_RET_STS_ERROR',
5020 x_msg_count,
5021 x_msg_data,
5022 '_PVT'
5023 );
5024 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5025 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5026 (
5027 l_api_name,
5028 G_PKG_NAME,
5029 'OKL_API.G_RET_STS_UNEXP_ERROR',
5030 x_msg_count,
5031 x_msg_data,
5032 '_PVT'
5033 );
5034 WHEN OTHERS THEN
5035 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5036 (
5037 l_api_name,
5038 G_PKG_NAME,
5039 'OTHERS',
5040 x_msg_count,
5041 x_msg_data,
5042 '_PVT'
5043 );
5044 END update_row;
5045
5046 ---------------------------------------------------------------------------
5047 -- PROCEDURE delete_row
5048 ---------------------------------------------------------------------------
5049 ------------------------------------
5050 -- delete_row for:OKL_SUBSIDIES_B --
5051 ------------------------------------
5052 PROCEDURE delete_row(
5053 p_init_msg_list IN VARCHAR2,
5054 x_return_status OUT NOCOPY VARCHAR2,
5055 x_msg_count OUT NOCOPY NUMBER,
5056 x_msg_data OUT NOCOPY VARCHAR2,
5057 p_subb_rec IN subb_rec_type) IS
5058
5059 l_api_version CONSTANT NUMBER := 1;
5060 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
5061 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5062 l_subb_rec subb_rec_type := p_subb_rec;
5063 l_row_notfound BOOLEAN := TRUE;
5064 BEGIN
5065 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
5066 p_init_msg_list,
5067 '_PVT',
5068 x_return_status);
5069 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5070 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5071 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5072 RAISE OKL_API.G_EXCEPTION_ERROR;
5073 END IF;
5074
5075 DELETE FROM OKL_SUBSIDIES_B
5076 WHERE ID = p_subb_rec.id;
5077
5078 x_return_status := l_return_status;
5079 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5080 EXCEPTION
5081 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5082 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5083 (
5084 l_api_name,
5085 G_PKG_NAME,
5086 'OKL_API.G_RET_STS_ERROR',
5087 x_msg_count,
5088 x_msg_data,
5089 '_PVT'
5090 );
5091 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5092 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5093 (
5094 l_api_name,
5095 G_PKG_NAME,
5096 'OKL_API.G_RET_STS_UNEXP_ERROR',
5097 x_msg_count,
5098 x_msg_data,
5099 '_PVT'
5100 );
5101 WHEN OTHERS THEN
5102 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5103 (
5104 l_api_name,
5105 G_PKG_NAME,
5106 'OTHERS',
5107 x_msg_count,
5108 x_msg_data,
5109 '_PVT'
5110 );
5111 END delete_row;
5112 -------------------------------------
5113 -- delete_row for:OKL_SUBSIDIES_TL --
5114 -------------------------------------
5115 PROCEDURE delete_row(
5116 p_init_msg_list IN VARCHAR2,
5117 x_return_status OUT NOCOPY VARCHAR2,
5118 x_msg_count OUT NOCOPY NUMBER,
5119 x_msg_data OUT NOCOPY VARCHAR2,
5120 p_subt_rec IN subt_rec_type) IS
5121
5122 l_api_version CONSTANT NUMBER := 1;
5123 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
5124 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5125 l_subt_rec subt_rec_type := p_subt_rec;
5126 l_row_notfound BOOLEAN := TRUE;
5127 BEGIN
5128 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
5129 p_init_msg_list,
5130 '_PVT',
5131 x_return_status);
5132 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5133 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5134 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5135 RAISE OKL_API.G_EXCEPTION_ERROR;
5136 END IF;
5137
5138 DELETE FROM OKL_SUBSIDIES_TL
5139 WHERE ID = p_subt_rec.id;
5140
5141 x_return_status := l_return_status;
5142 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5143 EXCEPTION
5144 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5145 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5146 (
5147 l_api_name,
5148 G_PKG_NAME,
5149 'OKL_API.G_RET_STS_ERROR',
5150 x_msg_count,
5151 x_msg_data,
5152 '_PVT'
5153 );
5154 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5155 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5156 (
5157 l_api_name,
5158 G_PKG_NAME,
5159 'OKL_API.G_RET_STS_UNEXP_ERROR',
5160 x_msg_count,
5161 x_msg_data,
5162 '_PVT'
5163 );
5164 WHEN OTHERS THEN
5165 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5166 (
5167 l_api_name,
5168 G_PKG_NAME,
5169 'OTHERS',
5170 x_msg_count,
5171 x_msg_data,
5172 '_PVT'
5173 );
5174 END delete_row;
5175 ------------------------------------
5176 -- delete_row for:OKL_SUBSIDIES_V --
5177 ------------------------------------
5178 PROCEDURE delete_row(
5179 p_api_version IN NUMBER,
5180 p_init_msg_list IN VARCHAR2,
5181 x_return_status OUT NOCOPY VARCHAR2,
5182 x_msg_count OUT NOCOPY NUMBER,
5183 x_msg_data OUT NOCOPY VARCHAR2,
5184 p_subv_rec IN subv_rec_type) IS
5185
5186 l_api_version CONSTANT NUMBER := 1;
5187 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
5188 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5189 l_subv_rec subv_rec_type := p_subv_rec;
5190 l_subt_rec subt_rec_type;
5191 l_subb_rec subb_rec_type;
5192 BEGIN
5193 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
5194 G_PKG_NAME,
5195 p_init_msg_list,
5196 l_api_version,
5197 p_api_version,
5198 '_PVT',
5199 x_return_status);
5200 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5201 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5202 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5203 RAISE OKL_API.G_EXCEPTION_ERROR;
5204 END IF;
5205 -----------------------------------------
5206 -- Move VIEW record to "Child" records --
5207 -----------------------------------------
5208 migrate(l_subv_rec, l_subt_rec);
5209 migrate(l_subv_rec, l_subb_rec);
5210 -----------------------------------------------
5211 -- Call the DELETE_ROW for each child record --
5212 -----------------------------------------------
5213 delete_row(
5214 p_init_msg_list,
5215 l_return_status,
5216 x_msg_count,
5217 x_msg_data,
5218 l_subt_rec
5219 );
5220 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5221 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5222 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5223 RAISE OKL_API.G_EXCEPTION_ERROR;
5224 END IF;
5225 delete_row(
5226 p_init_msg_list,
5227 l_return_status,
5228 x_msg_count,
5229 x_msg_data,
5230 l_subb_rec
5231 );
5232 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5233 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5234 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5235 RAISE OKL_API.G_EXCEPTION_ERROR;
5236 END IF;
5237 x_return_status := l_return_status;
5238 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5239 EXCEPTION
5240 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5241 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5242 (
5243 l_api_name,
5244 G_PKG_NAME,
5245 'OKL_API.G_RET_STS_ERROR',
5246 x_msg_count,
5247 x_msg_data,
5248 '_PVT'
5249 );
5250 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5251 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5252 (
5253 l_api_name,
5254 G_PKG_NAME,
5255 'OKL_API.G_RET_STS_UNEXP_ERROR',
5256 x_msg_count,
5257 x_msg_data,
5258 '_PVT'
5259 );
5260 WHEN OTHERS THEN
5261 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5262 (
5263 l_api_name,
5264 G_PKG_NAME,
5265 'OTHERS',
5266 x_msg_count,
5267 x_msg_data,
5268 '_PVT'
5269 );
5270 END delete_row;
5271 -----------------------------------------------
5272 -- PL/SQL TBL delete_row for:OKL_SUBSIDIES_V --
5273 -----------------------------------------------
5274 PROCEDURE delete_row(
5275 p_api_version IN NUMBER,
5276 p_init_msg_list IN VARCHAR2,
5277 x_return_status OUT NOCOPY VARCHAR2,
5278 x_msg_count OUT NOCOPY NUMBER,
5279 x_msg_data OUT NOCOPY VARCHAR2,
5280 p_subv_tbl IN subv_tbl_type,
5281 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
5282
5283 l_api_version CONSTANT NUMBER := 1;
5284 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
5285 i NUMBER := 0;
5286 BEGIN
5287 OKL_API.init_msg_list(p_init_msg_list);
5288 -- Make sure PL/SQL table has records in it before passing
5289 IF (p_subv_tbl.COUNT > 0) THEN
5290 i := p_subv_tbl.FIRST;
5291 LOOP
5292 DECLARE
5293 l_error_rec OKL_API.ERROR_REC_TYPE;
5294 BEGIN
5295 l_error_rec.api_name := l_api_name;
5296 l_error_rec.api_package := G_PKG_NAME;
5297 l_error_rec.idx := i;
5298 delete_row (
5299 p_api_version => p_api_version,
5300 p_init_msg_list => OKL_API.G_FALSE,
5301 x_return_status => l_error_rec.error_type,
5302 x_msg_count => l_error_rec.msg_count,
5303 x_msg_data => l_error_rec.msg_data,
5304 p_subv_rec => p_subv_tbl(i));
5305 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
5306 l_error_rec.sqlcode := SQLCODE;
5307 load_error_tbl(l_error_rec, px_error_tbl);
5308 ELSE
5309 x_msg_count := l_error_rec.msg_count;
5310 x_msg_data := l_error_rec.msg_data;
5311 END IF;
5312 EXCEPTION
5313 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5314 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
5315 l_error_rec.sqlcode := SQLCODE;
5316 load_error_tbl(l_error_rec, px_error_tbl);
5317 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5318 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
5319 l_error_rec.sqlcode := SQLCODE;
5320 load_error_tbl(l_error_rec, px_error_tbl);
5321 WHEN OTHERS THEN
5322 l_error_rec.error_type := 'OTHERS';
5323 l_error_rec.sqlcode := SQLCODE;
5324 load_error_tbl(l_error_rec, px_error_tbl);
5325 END;
5326 EXIT WHEN (i = p_subv_tbl.LAST);
5327 i := p_subv_tbl.NEXT(i);
5328 END LOOP;
5329 END IF;
5330 -- Loop through the error_tbl to find the error with the highest severity
5331 -- and return it.
5332 x_return_status := find_highest_exception(px_error_tbl);
5333 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5334 EXCEPTION
5335 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5336 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5337 (
5338 l_api_name,
5339 G_PKG_NAME,
5340 'OKL_API.G_RET_STS_ERROR',
5341 x_msg_count,
5342 x_msg_data,
5343 '_PVT'
5344 );
5345 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5346 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5347 (
5348 l_api_name,
5349 G_PKG_NAME,
5350 'OKL_API.G_RET_STS_UNEXP_ERROR',
5351 x_msg_count,
5352 x_msg_data,
5353 '_PVT'
5354 );
5355 WHEN OTHERS THEN
5356 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5357 (
5358 l_api_name,
5359 G_PKG_NAME,
5360 'OTHERS',
5361 x_msg_count,
5362 x_msg_data,
5363 '_PVT'
5364 );
5365 END delete_row;
5366
5367 -----------------------------------------------
5368 -- PL/SQL TBL delete_row for:OKL_SUBSIDIES_V --
5369 -----------------------------------------------
5370 PROCEDURE delete_row(
5371 p_api_version IN NUMBER,
5372 p_init_msg_list IN VARCHAR2,
5373 x_return_status OUT NOCOPY VARCHAR2,
5374 x_msg_count OUT NOCOPY NUMBER,
5375 x_msg_data OUT NOCOPY VARCHAR2,
5376 p_subv_tbl IN subv_tbl_type) IS
5377
5378 l_api_version CONSTANT NUMBER := 1;
5379 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
5380 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5381 l_error_tbl OKL_API.ERROR_TBL_TYPE;
5382 BEGIN
5383 OKL_API.init_msg_list(p_init_msg_list);
5384 -- Make sure PL/SQL table has records in it before passing
5385 IF (p_subv_tbl.COUNT > 0) THEN
5386 delete_row (
5387 p_api_version => p_api_version,
5388 p_init_msg_list => OKL_API.G_FALSE,
5389 x_return_status => x_return_status,
5390 x_msg_count => x_msg_count,
5391 x_msg_data => x_msg_data,
5392 p_subv_tbl => p_subv_tbl,
5393 px_error_tbl => l_error_tbl);
5394 END IF;
5395 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5396 EXCEPTION
5397 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5398 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5399 (
5400 l_api_name,
5401 G_PKG_NAME,
5402 'OKL_API.G_RET_STS_ERROR',
5403 x_msg_count,
5404 x_msg_data,
5405 '_PVT'
5406 );
5407 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5408 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5409 (
5410 l_api_name,
5411 G_PKG_NAME,
5412 'OKL_API.G_RET_STS_UNEXP_ERROR',
5413 x_msg_count,
5414 x_msg_data,
5415 '_PVT'
5416 );
5417 WHEN OTHERS THEN
5418 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5419 (
5420 l_api_name,
5421 G_PKG_NAME,
5422 'OTHERS',
5423 x_msg_count,
5424 x_msg_data,
5425 '_PVT'
5426 );
5427 END delete_row;
5428
5429 END OKL_SUB_PVT;