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