[Home] [Help]
PACKAGE BODY: APPS.OKL_PTC_PVT
Source
1 PACKAGE BODY OKL_PTC_PVT AS
2 /* $Header: OKLSPTCB.pls 120.4 2007/08/08 12:48:24 arajagop ship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE load_error_tbl
6 ---------------------------------------------------------------------------
7 PROCEDURE load_error_tbl (
8 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
9 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
10
11 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
12 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
13 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
14 BEGIN
15 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
16 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
17 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
18 -- automatically increments the index by 1, (making it 2), however, when the GET function
19 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
20 -- message 2. To circumvent this problem, check the amount of messages and compensate.
21 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
22 -- will only update the index variable when 1 and only 1 message is on the stack.
23 IF (last_msg_idx = 1) THEN
24 l_msg_idx := FND_MSG_PUB.G_FIRST;
25 END IF;
26 LOOP
27 fnd_msg_pub.get(
28 p_msg_index => l_msg_idx,
29 p_encoded => fnd_api.g_false,
30 p_data => px_error_rec.msg_data,
31 p_msg_index_out => px_error_rec.msg_count);
32 px_error_tbl(j) := px_error_rec;
33 j := j + 1;
34 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
35 END LOOP;
36 END load_error_tbl;
37 ---------------------------------------------------------------------------
38 -- FUNCTION find_highest_exception
39 ---------------------------------------------------------------------------
40 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
41 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
42 FUNCTION find_highest_exception(
43 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
44 ) RETURN VARCHAR2 IS
45 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
46 i INTEGER := 1;
47 BEGIN
48 IF (p_error_tbl.COUNT > 0) THEN
49 i := p_error_tbl.FIRST;
50 LOOP
51 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
52 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
53 l_return_status := p_error_tbl(i).error_type;
54 END IF;
55 END IF;
56 EXIT WHEN (i = p_error_tbl.LAST);
57 i := p_error_tbl.NEXT(i);
58 END LOOP;
59 END IF;
60 RETURN(l_return_status);
61 END find_highest_exception;
62 ---------------------------------------------------------------------------
63 -- FUNCTION get_seq_id
64 ---------------------------------------------------------------------------
65 FUNCTION get_seq_id RETURN NUMBER IS
66 BEGIN
67 RETURN(okc_p_util.raw_to_number(sys_guid()));
68 END get_seq_id;
69
70 ---------------------------------------------------------------------------
71 -- PROCEDURE qc
72 ---------------------------------------------------------------------------
73 PROCEDURE qc IS
74 BEGIN
75 null;
76 END qc;
77
78 ---------------------------------------------------------------------------
79 -- PROCEDURE change_version
80 ---------------------------------------------------------------------------
81 PROCEDURE change_version IS
82 BEGIN
83 null;
84 END change_version;
85
86 ---------------------------------------------------------------------------
87 -- PROCEDURE api_copy
88 ---------------------------------------------------------------------------
89 PROCEDURE api_copy IS
90 BEGIN
91 null;
92 END api_copy;
93
94 ---------------------------------------------------------------------------
95 -- PROCEDURE add_language
96 ---------------------------------------------------------------------------
97 PROCEDURE add_language IS
98 BEGIN
99 DELETE FROM OKL_PROPERTY_TAX_TL T
100 WHERE NOT EXISTS (
101 SELECT NULL
102 FROM OKL_PROPERTY_TAX_ALL_B B --Changed _TL to _B by rvaduri for MLS compliance
103 WHERE B.ID =T.ID
104 );
105
106 UPDATE OKL_PROPERTY_TAX_TL T SET(
107 ASSET_DESCRIPTION) = (SELECT
108 B.ASSET_DESCRIPTION
109 FROM OKL_PROPERTY_TAX_TL B
110 WHERE B.ID = T.ID
111 AND B.LANGUAGE = T.SOURCE_LANG)
112 WHERE ( T.ID, T.LANGUAGE)
113 IN (SELECT
114 SUBT.ID
115 ,SUBT.LANGUAGE
116 FROM OKL_PROPERTY_TAX_TL SUBB, OKL_PROPERTY_TAX_TL SUBT
117 WHERE SUBB.ID = SUBT.ID
118 AND SUBB.LANGUAGE = SUBT.LANGUAGE
119 AND ( SUBB.ASSET_DESCRIPTION <> SUBT.ASSET_DESCRIPTION
120 OR (SUBB.LANGUAGE IS NOT NULL AND SUBT.LANGUAGE IS NULL)
121 OR (SUBB.ASSET_DESCRIPTION IS NULL AND SUBT.ASSET_DESCRIPTION IS NOT NULL)
122 ));
123
124 INSERT INTO OKL_PROPERTY_TAX_TL (
125 ID,
126 LANGUAGE,
127 SOURCE_LANG,
128 SFWT_FLAG,
129 ASSET_DESCRIPTION,
130 CREATED_BY,
131 CREATION_DATE,
132 LAST_UPDATED_BY,
133 LAST_UPDATE_DATE,
134 LAST_UPDATE_LOGIN)
135 SELECT
136 B.ID,
137 L.LANGUAGE_CODE,
138 B.SOURCE_LANG,
139 B.SFWT_FLAG,
140 B.ASSET_DESCRIPTION,
141 B.CREATED_BY,
142 B.CREATION_DATE,
143 B.LAST_UPDATED_BY,
144 B.LAST_UPDATE_DATE,
145 B.LAST_UPDATE_LOGIN
146 FROM OKL_PROPERTY_TAX_TL B, FND_LANGUAGES L
147 WHERE L.INSTALLED_FLAG IN ('I', 'B')
148 AND B.LANGUAGE = USERENV('LANG')
149 AND NOT EXISTS (
150 SELECT NULL
151 FROM OKL_PROPERTY_TAX_TL T
152 WHERE T.ID = B.ID
153 -- Added as per Bug 2876076 by rvaduri
154 AND T.LANGUAGE = L.LANGUAGE_CODE
155 );
156 END add_language;
157
158 ---------------------------------------------------------------------------
159 -- FUNCTION get_rec for: OKL_PROPERTY_TAX_V
160 ---------------------------------------------------------------------------
161 FUNCTION get_rec (
162 p_ptcv_rec IN ptcv_rec_type,
163 x_no_data_found OUT NOCOPY BOOLEAN
164 ) RETURN ptcv_rec_type IS
165 CURSOR okl_ptcv_pk_csr (p_id IN NUMBER) IS
166 SELECT
167 ID,
168 SEQUENCE_NUMBER,
169 ASSET_ID,
170 ASSET_NUMBER,
171 ASSET_DESCRIPTION,
172 KHR_ID,
173 KLE_ID,
174 ASSET_UNITS,
175 LANGUAGE,
176 SOURCE_LANG,
177 SFWT_FLAG,
178 ASSET_ADDRESS_1,
179 ASSET_ADDRESS_2,
180 ASSET_ADDRESS_3,
181 ASSET_ADDRESS_4,
182 ASSET_CITY,
183 ASSET_STATE,
184 ASSET_COUNTRY,
185 TAX_ASSESSMENT_AMOUNT,
186 TAX_JURISDICTION_CITY,
187 TAX_JURISDICTION_CITY_RATE,
188 TAX_JURISDICTION_COUNTY,
189 TAX_JURISDICTION_COUNTY_RATE,
190 TAX_JURISDICTION_STATE,
191 TAX_JURISDICTION_STATE_RATE,
192 TAX_JURISDICTION_SCHOOL,
193 TAX_JURISDICTION_SCHOOL_RATE,
194 TAX_JURISDICTION_COUNTRY,
195 TAX_JURISDICTION_COUNTRY_RATE,
196 TAX_ASSESSMENT_DATE,
197 MILRATE,
198 PROPERTY_TAX_AMOUNT,
199 OEC,
200 CREATED_BY,
201 CREATION_DATE,
202 LAST_UPDATED_BY,
203 LAST_UPDATE_DATE,
204 LAST_UPDATE_LOGIN
205 ,contract_number
206 ,sty_name
207 ,sty_id
208 ,invoice_date
209 ,amount
210 ,org_id
211 ,JURSDCTN_TYPE
212 ,JURSDCTN_NAME
213 ,MLRT_TAX
214 ,TAX_VENDOR_ID
215 ,TAX_VENDOR_NAME
216 ,TAX_VENDOR_SITE_ID
217 ,TAX_VENDOR_SITE_NAME
218 FROM OKL_PROPERTY_TAX_V
219 WHERE OKL_PROPERTY_TAX_V.id = p_id;
220 l_okl_ptcv_pk okl_ptcv_pk_csr%ROWTYPE;
221 l_ptcv_rec ptcv_rec_type;
222 BEGIN
223 x_no_data_found := TRUE;
224 -- Get current database values
225 OPEN okl_ptcv_pk_csr (p_ptcv_rec.id);
226 FETCH okl_ptcv_pk_csr INTO
227 l_ptcv_rec.id,
228 l_ptcv_rec.sequence_number,
229 l_ptcv_rec.asset_id,
230 l_ptcv_rec.asset_number,
231 l_ptcv_rec.asset_description,
232 l_ptcv_rec.khr_id,
233 l_ptcv_rec.kle_id,
234 l_ptcv_rec.asset_units,
235 l_ptcv_rec.language,
236 l_ptcv_rec.source_lang,
237 l_ptcv_rec.sfwt_flag,
238 l_ptcv_rec.asset_address_1,
239 l_ptcv_rec.asset_address_2,
240 l_ptcv_rec.asset_address_3,
241 l_ptcv_rec.asset_address_4,
242 l_ptcv_rec.asset_city,
243 l_ptcv_rec.asset_state,
244 l_ptcv_rec.asset_country,
245 l_ptcv_rec.tax_assessment_amount,
246 l_ptcv_rec.tax_jurisdiction_city,
247 l_ptcv_rec.tax_jurisdiction_city_rate,
248 l_ptcv_rec.tax_jurisdiction_county,
249 l_ptcv_rec.tax_jurisdiction_county_rate,
250 l_ptcv_rec.tax_jurisdiction_state,
251 l_ptcv_rec.tax_jurisdiction_state_rate,
252 l_ptcv_rec.tax_jurisdiction_school,
253 l_ptcv_rec.tax_jurisdiction_school_rate,
254 l_ptcv_rec.tax_jurisdiction_country,
255 l_ptcv_rec.tax_jurisdiction_country_rate,
256 l_ptcv_rec.tax_assessment_date,
257 l_ptcv_rec.milrate,
258 l_ptcv_rec.property_tax_amount,
259 l_ptcv_rec.oec,
260 l_ptcv_rec.created_by,
261 l_ptcv_rec.creation_date,
262 l_ptcv_rec.last_updated_by,
263 l_ptcv_rec.last_update_date,
264 l_ptcv_rec.last_update_login
265 ,l_ptcv_rec.contract_number
266 ,l_ptcv_rec.sty_name
267 ,l_ptcv_rec.sty_id
268 ,l_ptcv_rec.invoice_date
269 ,l_ptcv_rec.amount
270 ,l_ptcv_rec.org_id
271 ,l_ptcv_rec.JURSDCTN_TYPE
272 ,l_ptcv_rec.JURSDCTN_NAME
273 ,l_ptcv_rec.MLRT_TAX
274 ,l_ptcv_rec.TAX_VENDOR_ID
275 ,l_ptcv_rec.TAX_VENDOR_NAME
276 ,l_ptcv_rec.TAX_VENDOR_SITE_ID
277 ,l_ptcv_rec.TAX_VENDOR_SITE_NAME;
278 x_no_data_found := okl_ptcv_pk_csr%NOTFOUND;
279 CLOSE okl_ptcv_pk_csr;
280 RETURN(l_ptcv_rec);
281 END get_rec;
282
283 ------------------------------------------------------------------
284 -- This version of get_rec sets error messages if no data found --
285 ------------------------------------------------------------------
286 FUNCTION get_rec (
287 p_ptcv_rec IN ptcv_rec_type,
288 x_return_status OUT NOCOPY VARCHAR2
289 ) RETURN ptcv_rec_type IS
290 l_ptcv_rec ptcv_rec_type;
291 l_row_notfound BOOLEAN := TRUE;
292 BEGIN
293 x_return_status := OKC_API.G_RET_STS_SUCCESS;
294 l_ptcv_rec := get_rec(p_ptcv_rec, l_row_notfound);
295 IF (l_row_notfound) THEN
296 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
297 x_return_status := OKC_API.G_RET_STS_ERROR;
298 END IF;
299 RETURN(l_ptcv_rec);
300 END get_rec;
301 -----------------------------------------------------------
302 -- So we don't have to pass an "l_row_notfound" variable --
303 -----------------------------------------------------------
304 FUNCTION get_rec (
305 p_ptcv_rec IN ptcv_rec_type
306 ) RETURN ptcv_rec_type IS
307 l_row_not_found BOOLEAN := TRUE;
308 BEGIN
309 RETURN(get_rec(p_ptcv_rec, l_row_not_found));
310 END get_rec;
311 ---------------------------------------------------------------------------
312 -- FUNCTION get_rec for: OKL_PROPERTY_TAX_B
313 ---------------------------------------------------------------------------
314 FUNCTION get_rec (
315 p_ptc_rec IN ptc_rec_type,
316 x_no_data_found OUT NOCOPY BOOLEAN
317 ) RETURN ptc_rec_type IS
318 CURSOR okl_ptc_pk_csr (p_id IN NUMBER) IS
319 SELECT
320 ID,
321 SEQUENCE_NUMBER,
322 ASSET_ID,
323 ASSET_NUMBER,
324 KHR_ID,
325 KLE_ID,
326 ASSET_UNITS,
327 ASSET_ADDRESS_1,
328 ASSET_ADDRESS_2,
329 ASSET_ADDRESS_3,
330 ASSET_ADDRESS_4,
331 ASSET_CITY,
332 ASSET_STATE,
333 ASSET_COUNTRY,
334 TAX_ASSESSMENT_AMOUNT,
335 TAX_JURISDICTION_CITY,
336 TAX_JURISDICTION_CITY_RATE,
337 TAX_JURISDICTION_COUNTY,
338 TAX_JURISDICTION_COUNTY_RATE,
339 TAX_JURISDICTION_STATE,
340 TAX_JURISDICTION_STATE_RATE,
341 TAX_JURISDICTION_SCHOOL,
342 TAX_JURISDICTION_SCHOOL_RATE,
343 TAX_JURISDICTION_COUNTRY,
344 TAX_JURISDICTION_COUNTRY_RATE,
345 TAX_ASSESSMENT_DATE,
346 MILRATE,
347 PROPERTY_TAX_AMOUNT,
348 OEC,
349 CREATED_BY,
350 CREATION_DATE,
351 LAST_UPDATED_BY,
352 LAST_UPDATE_DATE,
353 LAST_UPDATE_LOGIN
354 ,contract_number
355 ,sty_name
356 ,sty_id
357 ,invoice_date
358 ,amount
359 ,org_id
360 ,JURSDCTN_TYPE
361 ,JURSDCTN_NAME
362 ,MLRT_TAX
363 ,TAX_VENDOR_ID
364 ,TAX_VENDOR_NAME
365 ,TAX_VENDOR_SITE_ID
366 ,TAX_VENDOR_SITE_NAME
367 FROM OKL_PROPERTY_TAX_B
368 WHERE okl_property_tax_b.id = p_id;
369 l_okl_ptc_pk okl_ptc_pk_csr%ROWTYPE;
370 l_ptc_rec ptc_rec_type;
371 BEGIN
372 x_no_data_found := TRUE;
373 -- Get current database values
374 OPEN okl_ptc_pk_csr (p_ptc_rec.id);
375 FETCH okl_ptc_pk_csr INTO
376 l_ptc_rec.id,
377 l_ptc_rec.sequence_number,
378 l_ptc_rec.asset_id,
379 l_ptc_rec.asset_number,
380 l_ptc_rec.khr_id,
381 l_ptc_rec.kle_id,
382 l_ptc_rec.asset_units,
383 l_ptc_rec.asset_address_1,
384 l_ptc_rec.asset_address_2,
385 l_ptc_rec.asset_address_3,
386 l_ptc_rec.asset_address_4,
387 l_ptc_rec.asset_city,
388 l_ptc_rec.asset_state,
389 l_ptc_rec.asset_country,
390 l_ptc_rec.tax_assessment_amount,
391 l_ptc_rec.tax_jurisdiction_city,
392 l_ptc_rec.tax_jurisdiction_city_rate,
393 l_ptc_rec.tax_jurisdiction_county,
394 l_ptc_rec.tax_jurisdiction_county_rate,
395 l_ptc_rec.tax_jurisdiction_state,
396 l_ptc_rec.tax_jurisdiction_state_rate,
397 l_ptc_rec.tax_jurisdiction_school,
398 l_ptc_rec.tax_jurisdiction_school_rate,
399 l_ptc_rec.tax_jurisdiction_country,
400 l_ptc_rec.tax_jurisdiction_country_rate,
401 l_ptc_rec.tax_assessment_date,
402 l_ptc_rec.milrate,
403 l_ptc_rec.property_tax_amount,
404 l_ptc_rec.oec,
405 l_ptc_rec.created_by,
406 l_ptc_rec.creation_date,
407 l_ptc_rec.last_updated_by,
408 l_ptc_rec.last_update_date,
409 l_ptc_rec.last_update_login
410 ,l_ptc_rec.contract_number
411 ,l_ptc_rec.sty_name
412 ,l_ptc_rec.sty_id
413 ,l_ptc_rec.invoice_date
414 ,l_ptc_rec.amount
415 ,l_ptc_rec.org_id
416 ,l_ptc_rec.JURSDCTN_TYPE
417 ,l_ptc_rec.JURSDCTN_NAME
418 ,l_ptc_rec.MLRT_TAX
419 ,l_ptc_rec.TAX_VENDOR_ID
420 ,l_ptc_rec.TAX_VENDOR_NAME
421 ,l_ptc_rec.TAX_VENDOR_SITE_ID
422 ,l_ptc_rec.TAX_VENDOR_SITE_NAME;
423 x_no_data_found := okl_ptc_pk_csr%NOTFOUND;
424 CLOSE okl_ptc_pk_csr;
425 RETURN(l_ptc_rec);
426 END get_rec;
427
428 ------------------------------------------------------------------
429 -- This version of get_rec sets error messages if no data found --
430 ------------------------------------------------------------------
431 FUNCTION get_rec (
432 p_ptc_rec IN ptc_rec_type,
433 x_return_status OUT NOCOPY VARCHAR2
434 ) RETURN ptc_rec_type IS
435 l_ptc_rec ptc_rec_type;
436 l_row_notfound BOOLEAN := TRUE;
437 BEGIN
438 x_return_status := OKC_API.G_RET_STS_SUCCESS;
439 l_ptc_rec := get_rec(p_ptc_rec, l_row_notfound);
440 IF (l_row_notfound) THEN
441 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
442 x_return_status := OKC_API.G_RET_STS_ERROR;
443 END IF;
444 RETURN(l_ptc_rec);
445 END get_rec;
446 -----------------------------------------------------------
447 -- So we don't have to pass an "l_row_notfound" variable --
448 -----------------------------------------------------------
449 FUNCTION get_rec (
450 p_ptc_rec IN ptc_rec_type
451 ) RETURN ptc_rec_type IS
452 l_row_not_found BOOLEAN := TRUE;
453 BEGIN
454 RETURN(get_rec(p_ptc_rec, l_row_not_found));
455 END get_rec;
456 ---------------------------------------------------------------------------
457 -- FUNCTION get_rec for: OKL_PROPERTY_TAX_TL
458 ---------------------------------------------------------------------------
459 FUNCTION get_rec (
460 p_ptct_rec IN ptct_rec_type,
461 x_no_data_found OUT NOCOPY BOOLEAN
462 ) RETURN ptct_rec_type IS
463 CURSOR okl_ptct_pk_csr (p_id IN NUMBER) IS
464 SELECT
465 ID,
466 LANGUAGE,
467 SOURCE_LANG,
468 SFWT_FLAG,
469 ASSET_DESCRIPTION,
470 CREATED_BY,
471 CREATION_DATE,
472 LAST_UPDATED_BY,
473 LAST_UPDATE_DATE,
474 LAST_UPDATE_LOGIN
475 FROM Okl_Property_Tax_Tl
476 WHERE Okl_Property_Tax_Tl.id = p_id;
477 l_okl_ptct_pk okl_ptct_pk_csr%ROWTYPE;
478 l_ptct_rec ptct_rec_type;
479 BEGIN
480 x_no_data_found := TRUE;
481 -- Get current database values
482 OPEN okl_ptct_pk_csr (p_ptct_rec.id);
483 FETCH okl_ptct_pk_csr INTO
484 l_ptct_rec.id,
485 l_ptct_rec.language,
486 l_ptct_rec.source_lang,
487 l_ptct_rec.sfwt_flag,
488 l_ptct_rec.asset_description,
489 l_ptct_rec.created_by,
490 l_ptct_rec.creation_date,
491 l_ptct_rec.last_updated_by,
492 l_ptct_rec.last_update_date,
493 l_ptct_rec.last_update_login;
494 x_no_data_found := okl_ptct_pk_csr%NOTFOUND;
495 CLOSE okl_ptct_pk_csr;
496 RETURN(l_ptct_rec);
497 END get_rec;
498
499 ------------------------------------------------------------------
500 -- This version of get_rec sets error messages if no data found --
501 ------------------------------------------------------------------
502 FUNCTION get_rec (
503 p_ptct_rec IN ptct_rec_type,
504 x_return_status OUT NOCOPY VARCHAR2
505 ) RETURN ptct_rec_type IS
506 l_ptct_rec ptct_rec_type;
507 l_row_notfound BOOLEAN := TRUE;
508 BEGIN
509 x_return_status := OKC_API.G_RET_STS_SUCCESS;
510 l_ptct_rec := get_rec(p_ptct_rec, l_row_notfound);
511 IF (l_row_notfound) THEN
512 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
513 x_return_status := OKC_API.G_RET_STS_ERROR;
514 END IF;
515 RETURN(l_ptct_rec);
516 END get_rec;
517 -----------------------------------------------------------
518 -- So we don't have to pass an "l_row_notfound" variable --
519 -----------------------------------------------------------
520 FUNCTION get_rec (
521 p_ptct_rec IN ptct_rec_type
522 ) RETURN ptct_rec_type IS
523 l_row_not_found BOOLEAN := TRUE;
524 BEGIN
525 RETURN(get_rec(p_ptct_rec, l_row_not_found));
526 END get_rec;
527 ---------------------------------------------------------------------------
528 -- FUNCTION null_out_defaults for: OKL_PROPERTY_TAX_V
529 ---------------------------------------------------------------------------
530 FUNCTION null_out_defaults (
531 p_ptcv_rec IN ptcv_rec_type
532 ) RETURN ptcv_rec_type IS
533 l_ptcv_rec ptcv_rec_type := p_ptcv_rec;
534 BEGIN
535 IF (l_ptcv_rec.id = OKC_API.G_MISS_NUM ) THEN
536 l_ptcv_rec.id := NULL;
537 END IF;
538 IF (l_ptcv_rec.sequence_number = OKC_API.G_MISS_NUM ) THEN
539 l_ptcv_rec.sequence_number := NULL;
540 END IF;
541 IF (l_ptcv_rec.asset_id = OKC_API.G_MISS_NUM ) THEN
542 l_ptcv_rec.asset_id := NULL;
543 END IF;
544 IF (l_ptcv_rec.asset_number = OKC_API.G_MISS_CHAR ) THEN
545 l_ptcv_rec.asset_number := NULL;
546 END IF;
547 IF (l_ptcv_rec.asset_description = OKC_API.G_MISS_CHAR ) THEN
548 l_ptcv_rec.asset_description := NULL;
549 END IF;
550 IF (l_ptcv_rec.khr_id = OKC_API.G_MISS_NUM ) THEN
551 l_ptcv_rec.khr_id := NULL;
552 END IF;
553 IF (l_ptcv_rec.kle_id = OKC_API.G_MISS_NUM ) THEN
554 l_ptcv_rec.kle_id := NULL;
555 END IF;
556 IF (l_ptcv_rec.asset_units = OKC_API.G_MISS_NUM ) THEN
557 l_ptcv_rec.asset_units := NULL;
558 END IF;
559 IF (l_ptcv_rec.language = OKC_API.G_MISS_CHAR ) THEN
560 l_ptcv_rec.language := NULL;
561 END IF;
562 IF (l_ptcv_rec.source_lang = OKC_API.G_MISS_CHAR ) THEN
563 l_ptcv_rec.source_lang := NULL;
564 END IF;
565 IF (l_ptcv_rec.sfwt_flag = OKC_API.G_MISS_CHAR ) THEN
566 l_ptcv_rec.sfwt_flag := NULL;
567 END IF;
568 IF (l_ptcv_rec.asset_address_1 = OKC_API.G_MISS_CHAR ) THEN
569 l_ptcv_rec.asset_address_1 := NULL;
570 END IF;
571 IF (l_ptcv_rec.asset_address_2 = OKC_API.G_MISS_CHAR ) THEN
572 l_ptcv_rec.asset_address_2 := NULL;
573 END IF;
574 IF (l_ptcv_rec.asset_address_3 = OKC_API.G_MISS_CHAR ) THEN
575 l_ptcv_rec.asset_address_3 := NULL;
576 END IF;
577 IF (l_ptcv_rec.asset_address_4 = OKC_API.G_MISS_CHAR ) THEN
578 l_ptcv_rec.asset_address_4 := NULL;
579 END IF;
580 IF (l_ptcv_rec.asset_city = OKC_API.G_MISS_CHAR ) THEN
581 l_ptcv_rec.asset_city := NULL;
582 END IF;
583 IF (l_ptcv_rec.asset_state = OKC_API.G_MISS_CHAR ) THEN
584 l_ptcv_rec.asset_state := NULL;
585 END IF;
586 IF (l_ptcv_rec.asset_country = OKC_API.G_MISS_CHAR ) THEN
587 l_ptcv_rec.asset_country := NULL;
588 END IF;
589 IF (l_ptcv_rec.tax_assessment_amount = OKC_API.G_MISS_NUM ) THEN
590 l_ptcv_rec.tax_assessment_amount := NULL;
591 END IF;
592 IF (l_ptcv_rec.tax_jurisdiction_city = OKC_API.G_MISS_CHAR ) THEN
593 l_ptcv_rec.tax_jurisdiction_city := NULL;
594 END IF;
595 IF (l_ptcv_rec.tax_jurisdiction_city_rate = OKC_API.G_MISS_NUM ) THEN
596 l_ptcv_rec.tax_jurisdiction_city_rate := NULL;
597 END IF;
598 IF (l_ptcv_rec.tax_jurisdiction_county = OKC_API.G_MISS_CHAR ) THEN
599 l_ptcv_rec.tax_jurisdiction_county := NULL;
600 END IF;
601 IF (l_ptcv_rec.tax_jurisdiction_county_rate = OKC_API.G_MISS_NUM ) THEN
602 l_ptcv_rec.tax_jurisdiction_county_rate := NULL;
603 END IF;
604 IF (l_ptcv_rec.tax_jurisdiction_state = OKC_API.G_MISS_CHAR ) THEN
605 l_ptcv_rec.tax_jurisdiction_state := NULL;
606 END IF;
607 IF (l_ptcv_rec.tax_jurisdiction_state_rate = OKC_API.G_MISS_NUM ) THEN
608 l_ptcv_rec.tax_jurisdiction_state_rate := NULL;
609 END IF;
610 IF (l_ptcv_rec.tax_jurisdiction_school = OKC_API.G_MISS_CHAR ) THEN
611 l_ptcv_rec.tax_jurisdiction_school := NULL;
612 END IF;
613 IF (l_ptcv_rec.tax_jurisdiction_school_rate = OKC_API.G_MISS_NUM ) THEN
614 l_ptcv_rec.tax_jurisdiction_school_rate := NULL;
615 END IF;
616 IF (l_ptcv_rec.tax_jurisdiction_country = OKC_API.G_MISS_CHAR ) THEN
617 l_ptcv_rec.tax_jurisdiction_country := NULL;
618 END IF;
619 IF (l_ptcv_rec.tax_jurisdiction_country_rate = OKC_API.G_MISS_NUM ) THEN
620 l_ptcv_rec.tax_jurisdiction_country_rate := NULL;
621 END IF;
622 IF (l_ptcv_rec.tax_assessment_date = OKC_API.G_MISS_DATE ) THEN
623 l_ptcv_rec.tax_assessment_date := NULL;
624 END IF;
625 IF (l_ptcv_rec.milrate = OKC_API.G_MISS_NUM ) THEN
626 l_ptcv_rec.milrate := NULL;
627 END IF;
628 IF (l_ptcv_rec.property_tax_amount = OKC_API.G_MISS_NUM ) THEN
629 l_ptcv_rec.property_tax_amount := NULL;
630 END IF;
631 IF (l_ptcv_rec.oec = OKC_API.G_MISS_NUM ) THEN
632 l_ptcv_rec.oec := NULL;
633 END IF;
634 IF (l_ptcv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
635 l_ptcv_rec.created_by := NULL;
636 END IF;
637 IF (l_ptcv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
638 l_ptcv_rec.creation_date := NULL;
639 END IF;
640 IF (l_ptcv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
641 l_ptcv_rec.last_updated_by := NULL;
642 END IF;
643 IF (l_ptcv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
644 l_ptcv_rec.last_update_date := NULL;
645 END IF;
646 IF (l_ptcv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
647 l_ptcv_rec.last_update_login := NULL;
648 END IF;
649 IF (l_ptcv_rec.contract_number = OKC_API.G_MISS_CHAR ) THEN
650 l_ptcv_rec.contract_number := NULL;
651 END IF;
652 IF (l_ptcv_rec.sty_name = OKC_API.G_MISS_CHAR ) THEN
653 l_ptcv_rec.sty_name := NULL;
654 END IF;
655 IF (l_ptcv_rec.sty_id = OKC_API.G_MISS_NUM ) THEN
656 l_ptcv_rec.sty_id := NULL;
657 END IF;
658 IF (l_ptcv_rec.invoice_date = OKC_API.G_MISS_DATE ) THEN
659 l_ptcv_rec.invoice_date := NULL;
660 END IF;
661 IF (l_ptcv_rec.amount = OKC_API.G_MISS_NUM ) THEN
662 l_ptcv_rec.amount := NULL;
663 END IF;
664 IF (l_ptcv_rec.org_id = OKC_API.G_MISS_NUM ) THEN
665 l_ptcv_rec.org_id := NULL;
666 END IF;
667
668 -- Addition for Est Property Tax
669 IF (l_ptcv_rec.JURSDCTN_TYPE = OKC_API.G_MISS_CHAR ) THEN
670 l_ptcv_rec.JURSDCTN_TYPE := NULL;
671 END IF;
672 IF (l_ptcv_rec.JURSDCTN_NAME = OKC_API.G_MISS_CHAR ) THEN
673 l_ptcv_rec.JURSDCTN_NAME := NULL;
674 END IF;
675 IF (l_ptcv_rec.MLRT_TAX = OKC_API.G_MISS_NUM ) THEN
676 l_ptcv_rec.MLRT_TAX := NULL;
677 END IF;
678 IF (l_ptcv_rec.TAX_VENDOR_ID = OKC_API.G_MISS_NUM ) THEN
679 l_ptcv_rec.TAX_VENDOR_ID := NULL;
680 END IF;
681 IF (l_ptcv_rec.TAX_VENDOR_NAME = OKC_API.G_MISS_CHAR ) THEN
682 l_ptcv_rec.TAX_VENDOR_NAME := NULL;
683 END IF;
684 IF (l_ptcv_rec.TAX_VENDOR_SITE_ID = OKC_API.G_MISS_NUM ) THEN
685 l_ptcv_rec.TAX_VENDOR_SITE_ID := NULL;
686 END IF;
687 IF (l_ptcv_rec.TAX_VENDOR_SITE_NAME = OKC_API.G_MISS_CHAR ) THEN
688 l_ptcv_rec.TAX_VENDOR_SITE_NAME := NULL;
689 END IF;
690 -- End Addition for Est Property Tax
691
692 RETURN(l_ptcv_rec);
693 END null_out_defaults;
694 ---------------------------------
695 -- Validate_Attributes for: ID --
696 ---------------------------------
697 PROCEDURE validate_id(
698 x_return_status OUT NOCOPY VARCHAR2,
699 p_ptcv_rec IN ptcv_rec_type) IS
700 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
701 BEGIN
702 IF (p_ptcv_rec.id = OKC_API.G_MISS_NUM OR
703 p_ptcv_rec.id IS NULL)
704 THEN
705 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
706 l_return_status := OKC_API.G_RET_STS_ERROR;
707 END IF;
708
709 x_return_status := l_return_status;
710 EXCEPTION
711 WHEN OTHERS THEN
712 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
713 ,p_msg_name => G_UNEXPECTED_ERROR
714 ,p_token1 => G_SQLCODE_TOKEN
715 ,p_token1_value => SQLCODE
716 ,p_token2 => G_SQLERRM_TOKEN
717 ,p_token2_value => SQLERRM);
718 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
719 END validate_id;
720
721 ----------------------------------------------
722 -- Validate_Attributes for: SEQUENCE_NUMBER --
723 ----------------------------------------------
724 /* PROCEDURE validate_sequence_number(
725 x_return_status OUT NOCOPY VARCHAR2,
726 p_sequence_number IN ptcv_rec_type) IS
727 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
728 BEGIN
729 IF (p_sequence_number.sequence_number = OKC_API.G_MISS_NUM OR
730 p_sequence_number.sequence_number IS NULL)
731 THEN
732 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sequence_number');
733 l_return_status := OKC_API.G_RET_STS_ERROR;
734 END IF;
735
736 -- verify that length is within allowed limits
737 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
738 l_return_status := OKC_API.G_RET_STS_ERROR;
739 END IF;
740 x_return_status := l_return_status;
741 EXCEPTION
742 WHEN OTHERS THEN
743 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
744 ,p_msg_name => G_UNEXPECTED_ERROR
745 ,p_token1 => G_SQLCODE_TOKEN
746 ,p_token1_value => SQLCODE
747 ,p_token2 => G_SQLERRM_TOKEN
748 ,p_token2_value => SQLERRM);
749 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
750 END validate_sequence_number;
751 */
752 -------------------------------------
753 -- Validate_Attributes for: STY_ID --
754 -------------------------------------
755
756 PROCEDURE validate_sty_id(
757 x_return_status OUT NOCOPY VARCHAR2,
758 p_ptcv_rec IN ptcv_rec_type) IS
759 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
760 BEGIN
761 IF (p_ptcv_rec.sty_id = OKC_API.G_MISS_NUM OR
762 p_ptcv_rec.sty_id IS NULL)
763 THEN
764 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sty_id');
765 l_return_status := OKC_API.G_RET_STS_ERROR;
766 END IF;
767
768 x_return_status := l_return_status;
769 EXCEPTION
770 WHEN OTHERS THEN
771 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
772 ,p_msg_name => G_UNEXPECTED_ERROR
773 ,p_token1 => G_SQLCODE_TOKEN
774 ,p_token1_value => SQLCODE
775 ,p_token2 => G_SQLERRM_TOKEN
776 ,p_token2_value => SQLERRM);
777 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
778 END validate_sty_id;
779 -------------------------------------
780 -- Validate_Attributes for: INVOICE_DATE --
781 -------------------------------------
782
783 PROCEDURE validate_invoice_date(
784 x_return_status OUT NOCOPY VARCHAR2,
785 p_ptcv_rec IN ptcv_rec_type) IS
786 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
787 BEGIN
788 IF (p_ptcv_rec.invoice_date = OKC_API.G_MISS_DATE OR
789 p_ptcv_rec.invoice_date IS NULL)
790 THEN
791 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Invoice Date');
792 l_return_status := OKC_API.G_RET_STS_ERROR;
793 END IF;
794
795 x_return_status := l_return_status;
796 EXCEPTION
797 WHEN OTHERS THEN
798 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
799 ,p_msg_name => G_UNEXPECTED_ERROR
800 ,p_token1 => G_SQLCODE_TOKEN
801 ,p_token1_value => SQLCODE
802 ,p_token2 => G_SQLERRM_TOKEN
803 ,p_token2_value => SQLERRM);
804 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
805 END validate_invoice_date;
806 ---------------------------------------
807 -- Validate_Attributes for: LANGUAGE --
808 ---------------------------------------
809 PROCEDURE validate_language(
810 x_return_status OUT NOCOPY VARCHAR2,
811 p_language IN ptcv_rec_type) IS
812 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
813 BEGIN
814 IF (p_language.language = OKC_API.G_MISS_CHAR OR
815 p_language.language IS NULL)
816 THEN
817 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'language');
818 l_return_status := OKC_API.G_RET_STS_ERROR;
819 END IF;
820
821 -- verify that length is within allowed limits
822 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
823 l_return_status := OKC_API.G_RET_STS_ERROR;
824 END IF;
825 x_return_status := l_return_status;
826 EXCEPTION
827 WHEN OTHERS THEN
828 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
829 ,p_msg_name => G_UNEXPECTED_ERROR
830 ,p_token1 => G_SQLCODE_TOKEN
831 ,p_token1_value => SQLCODE
832 ,p_token2 => G_SQLERRM_TOKEN
833 ,p_token2_value => SQLERRM);
834 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
835 END validate_language;
836 ------------------------------------------
837 -- Validate_Attributes for: SOURCE_LANG --
838 ------------------------------------------
839 PROCEDURE validate_source_lang(
840 x_return_status OUT NOCOPY VARCHAR2,
841 p_source_lang IN ptcv_rec_type) IS
842 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
843 BEGIN
844 IF (p_source_lang.source_lang = OKC_API.G_MISS_CHAR OR
845 p_source_lang.source_lang IS NULL)
846 THEN
847 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'source_lang');
848 l_return_status := OKC_API.G_RET_STS_ERROR;
849 END IF;
850
851 -- verify that length is within allowed limits
852 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
853 l_return_status := OKC_API.G_RET_STS_ERROR;
854 END IF;
855 x_return_status := l_return_status;
856 EXCEPTION
857 WHEN OTHERS THEN
858 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
859 ,p_msg_name => G_UNEXPECTED_ERROR
860 ,p_token1 => G_SQLCODE_TOKEN
861 ,p_token1_value => SQLCODE
862 ,p_token2 => G_SQLERRM_TOKEN
863 ,p_token2_value => SQLERRM);
864 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
865 END validate_source_lang;
866 ----------------------------------------
867 -- Validate_Attributes for: SFWT_FLAG --
868 ----------------------------------------
869 PROCEDURE validate_sfwt_flag(
870 x_return_status OUT NOCOPY VARCHAR2,
871 p_sfwt_flag IN ptcv_rec_type) IS
872 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
873 BEGIN
874 IF (p_sfwt_flag.sfwt_flag = OKC_API.G_MISS_CHAR OR
875 p_sfwt_flag.sfwt_flag IS NULL)
876 THEN
877 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sfwt_flag');
878 l_return_status := OKC_API.G_RET_STS_ERROR;
879 END IF;
880
881 -- verify that length is within allowed limits
882 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
883 l_return_status := OKC_API.G_RET_STS_ERROR;
884 END IF;
885 x_return_status := l_return_status;
886 EXCEPTION
887 WHEN OTHERS THEN
888 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
889 ,p_msg_name => G_UNEXPECTED_ERROR
890 ,p_token1 => G_SQLCODE_TOKEN
891 ,p_token1_value => SQLCODE
892 ,p_token2 => G_SQLERRM_TOKEN
893 ,p_token2_value => SQLERRM);
894 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
895 END validate_sfwt_flag;
896 ---------------------------------------------------------------------------
897 -- FUNCTION Validate_Attributes
898 ---------------------------------------------------------------------------
899 -------------------------------------------------------
900 -- Validate_Attributes for:OKL_PROPERTY_TAX_V --
901 -------------------------------------------------------
902 FUNCTION Validate_Attributes (
903 p_ptcv_rec IN ptcv_rec_type
904 ) RETURN VARCHAR2 IS
905 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
906 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
907 BEGIN
908 -----------------------------
909 -- Column Level Validation --
910 -----------------------------
911 -- ***
912 -- id
913 -- ***
914 validate_id(l_return_status, p_ptcv_rec);
915 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
916 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
917 x_return_status := l_return_status;
918 RAISE G_EXCEPTION_HALT_VALIDATION;
919 ELSE
920 x_return_status := l_return_status;
921 END IF;
922 END IF;
923
924 -- ***
925 -- sequence_number
926 -- ***
927 /* validate_sequence_number(l_return_status, p_ptcv_rec);
928 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
929 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
930 x_return_status := l_return_status;
931 RAISE G_EXCEPTION_HALT_VALIDATION;
932 ELSE
933 x_return_status := l_return_status;
934 END IF;
935 END IF;
936 */
937 -- ***
938 -- sty_id
939 -- ***
940 validate_sty_id(l_return_status, p_ptcv_rec);
941 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
942 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
943 x_return_status := l_return_status;
944 RAISE G_EXCEPTION_HALT_VALIDATION;
945 ELSE
946 x_return_status := l_return_status;
947 END IF;
948 END IF;
949
950 -- ***
951 -- invoice_date
952 -- ***
953 validate_invoice_date(l_return_status, p_ptcv_rec);
954 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
955 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
956 x_return_status := l_return_status;
957 RAISE G_EXCEPTION_HALT_VALIDATION;
958 ELSE
959 x_return_status := l_return_status;
960 END IF;
961 END IF;
962
963 -- ***
964 -- language
965 -- ***
966 validate_language(l_return_status, p_ptcv_rec);
967 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
968 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
969 x_return_status := l_return_status;
970 RAISE G_EXCEPTION_HALT_VALIDATION;
971 ELSE
972 x_return_status := l_return_status;
973 END IF;
974 END IF;
975
976 -- ***
977 -- source_lang
978 -- ***
979 validate_source_lang(l_return_status, p_ptcv_rec);
980 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
981 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
982 x_return_status := l_return_status;
983 RAISE G_EXCEPTION_HALT_VALIDATION;
984 ELSE
985 x_return_status := l_return_status;
986 END IF;
987 END IF;
988
989 -- ***
990 -- sfwt_flag
991 -- ***
992 validate_sfwt_flag(l_return_status, p_ptcv_rec);
993 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
994 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
995 x_return_status := l_return_status;
996 RAISE G_EXCEPTION_HALT_VALIDATION;
997 ELSE
998 x_return_status := l_return_status;
999 END IF;
1000 END IF;
1001
1002 RETURN(x_return_status);
1003 EXCEPTION
1004 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1005 RETURN(l_return_status);
1006 WHEN OTHERS THEN
1007 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1008 ,p_msg_name => G_UNEXPECTED_ERROR
1009 ,p_token1 => G_SQLCODE_TOKEN
1010 ,p_token1_value => SQLCODE
1011 ,p_token2 => G_SQLERRM_TOKEN
1012 ,p_token2_value => SQLERRM);
1013 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1014 RETURN(l_return_status);
1015 END Validate_Attributes;
1016 ---------------------------------------------------------------------------
1017 -- PROCEDURE Validate_Record
1018 ---------------------------------------------------------------------------
1019 ---------------------------------------------------
1020 -- Validate Record for:OKL_PROPERTY_TAX_V --
1021 ---------------------------------------------------
1022 FUNCTION Validate_Record (
1023 p_ptcv_rec IN ptcv_rec_type,
1024 p_db_ptcv_rec IN ptcv_rec_type
1025 ) RETURN VARCHAR2 IS
1026 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1027 ------------------------------------
1028 -- FUNCTION validate_foreign_keys --
1029 ------------------------------------
1030 FUNCTION validate_foreign_keys (
1031 p_ptcv_rec IN ptcv_rec_type,
1032 p_db_ptcv_rec IN ptcv_rec_type
1033 ) RETURN VARCHAR2 IS
1034 item_not_found_error EXCEPTION;
1035 CURSOR okl_khrv_pk_csr (p_id IN NUMBER) IS
1036 SELECT 'x'
1037 FROM Okl_K_Headers_V
1038 WHERE okl_k_headers_v.id = p_id;
1039 l_okl_khrv_pk okl_khrv_pk_csr%ROWTYPE;
1040
1041 CURSOR okl_klev_pk_csr (p_id IN NUMBER) IS
1042 SELECT 'x'
1043 FROM Okl_K_Lines_V
1044 WHERE okl_k_lines_v.id = p_id;
1045 l_okl_klev_pk okl_klev_pk_csr%ROWTYPE;
1046
1047 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1048 l_row_notfound BOOLEAN := TRUE;
1049 BEGIN
1050 IF ((p_ptcv_rec.KHR_ID IS NOT NULL)
1051 AND
1052 (p_ptcv_rec.KHR_ID <> p_db_ptcv_rec.KHR_ID))
1053 THEN
1054 OPEN okl_khrv_pk_csr (p_ptcv_rec.KHR_ID);
1055 FETCH okl_khrv_pk_csr INTO l_okl_khrv_pk;
1056 l_row_notfound := okl_khrv_pk_csr%NOTFOUND;
1057 CLOSE okl_khrv_pk_csr;
1058 IF (l_row_notfound) THEN
1059 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'KHR_ID');
1060 RAISE item_not_found_error;
1061 END IF;
1062 END IF;
1063 IF ((p_ptcv_rec.KLE_ID IS NOT NULL)
1064 AND
1065 (p_ptcv_rec.KLE_ID <> p_db_ptcv_rec.KLE_ID))
1066 THEN
1067 OPEN okl_klev_pk_csr (p_ptcv_rec.KLE_ID);
1068 FETCH okl_klev_pk_csr INTO l_okl_klev_pk;
1069 l_row_notfound := okl_klev_pk_csr%NOTFOUND;
1070 CLOSE okl_klev_pk_csr;
1071 IF (l_row_notfound) THEN
1072 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'KLE_ID');
1073 RAISE item_not_found_error;
1074 END IF;
1075 END IF;
1076 RETURN (l_return_status);
1077 EXCEPTION
1078 WHEN item_not_found_error THEN
1079 l_return_status := OKC_API.G_RET_STS_ERROR;
1080 RETURN (l_return_status);
1081 END validate_foreign_keys;
1082 BEGIN
1083 l_return_status := validate_foreign_keys(p_ptcv_rec, p_db_ptcv_rec);
1084 RETURN (l_return_status);
1085 END Validate_Record;
1086 FUNCTION Validate_Record (
1087 p_ptcv_rec IN ptcv_rec_type
1088 ) RETURN VARCHAR2 IS
1089 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1090 l_db_ptcv_rec ptcv_rec_type := get_rec(p_ptcv_rec);
1091 BEGIN
1092 l_return_status := Validate_Record(p_ptcv_rec => p_ptcv_rec,
1093 p_db_ptcv_rec => l_db_ptcv_rec);
1094 RETURN (l_return_status);
1095 END Validate_Record;
1096
1097 ---------------------------------------------------------------------------
1098 -- PROCEDURE Migrate
1099 ---------------------------------------------------------------------------
1100 PROCEDURE migrate (
1101 p_from IN ptcv_rec_type,
1102 p_to IN OUT NOCOPY ptc_rec_type
1103 ) IS
1104 BEGIN
1105 p_to.id := p_from.id;
1106 p_to.sequence_number := p_from.sequence_number;
1107 p_to.asset_id := p_from.asset_id;
1108 p_to.asset_number := p_from.asset_number;
1109 p_to.khr_id := p_from.khr_id;
1110 p_to.kle_id := p_from.kle_id;
1111 p_to.asset_units := p_from.asset_units;
1112 p_to.asset_address_1 := p_from.asset_address_1;
1113 p_to.asset_address_2 := p_from.asset_address_2;
1114 p_to.asset_address_3 := p_from.asset_address_3;
1115 p_to.asset_address_4 := p_from.asset_address_4;
1116 p_to.asset_city := p_from.asset_city;
1117 p_to.asset_state := p_from.asset_state;
1118 p_to.asset_country := p_from.asset_country;
1119 p_to.tax_assessment_amount := p_from.tax_assessment_amount;
1120 p_to.tax_jurisdiction_city := p_from.tax_jurisdiction_city;
1121 p_to.tax_jurisdiction_city_rate := p_from.tax_jurisdiction_city_rate;
1122 p_to.tax_jurisdiction_county := p_from.tax_jurisdiction_county;
1123 p_to.tax_jurisdiction_county_rate := p_from.tax_jurisdiction_county_rate;
1124 p_to.tax_jurisdiction_state := p_from.tax_jurisdiction_state;
1125 p_to.tax_jurisdiction_state_rate := p_from.tax_jurisdiction_state_rate;
1126 p_to.tax_jurisdiction_school := p_from.tax_jurisdiction_school;
1127 p_to.tax_jurisdiction_school_rate := p_from.tax_jurisdiction_school_rate;
1128 p_to.tax_jurisdiction_country := p_from.tax_jurisdiction_country;
1129 p_to.tax_jurisdiction_country_rate := p_from.tax_jurisdiction_country_rate;
1130 p_to.tax_assessment_date := p_from.tax_assessment_date;
1131 p_to.milrate := p_from.milrate;
1132 p_to.property_tax_amount := p_from.property_tax_amount;
1133 p_to.oec := p_from.oec;
1134 p_to.created_by := p_from.created_by;
1135 p_to.creation_date := p_from.creation_date;
1136 p_to.last_updated_by := p_from.last_updated_by;
1137 p_to.last_update_date := p_from.last_update_date;
1138 p_to.last_update_login := p_from.last_update_login;
1139 p_to.contract_number := p_from.contract_number;
1140 p_to.sty_name := p_from.sty_name;
1141 p_to.sty_id := p_from.sty_id;
1142 p_to.invoice_date := p_from.invoice_date;
1143 p_to.amount := p_from.amount;
1144 p_to.org_id := p_from.org_id;
1145 -- Addition for Est Property Tax
1146 p_to.JURSDCTN_TYPE := p_from.JURSDCTN_TYPE;
1147 p_to.JURSDCTN_NAME := p_from.JURSDCTN_NAME;
1148 p_to.MLRT_TAX := p_from.MLRT_TAX;
1149 p_to.TAX_VENDOR_ID := p_from.TAX_VENDOR_ID;
1150 p_to.TAX_VENDOR_NAME := p_from.TAX_VENDOR_NAME;
1151 p_to.TAX_VENDOR_SITE_ID := p_from.TAX_VENDOR_SITE_ID;
1152 p_to.TAX_VENDOR_SITE_NAME := p_from.TAX_VENDOR_SITE_NAME;
1153 -- End Addition for Est Property Tax
1154 END migrate;
1155 PROCEDURE migrate (
1156 p_from IN ptc_rec_type,
1157 p_to IN OUT NOCOPY ptcv_rec_type
1158 ) IS
1159 BEGIN
1160 p_to.id := p_from.id;
1161 p_to.sequence_number := p_from.sequence_number;
1162 p_to.asset_id := p_from.asset_id;
1163 p_to.asset_number := p_from.asset_number;
1164 p_to.khr_id := p_from.khr_id;
1165 p_to.kle_id := p_from.kle_id;
1166 p_to.asset_units := p_from.asset_units;
1167 p_to.asset_address_1 := p_from.asset_address_1;
1168 p_to.asset_address_2 := p_from.asset_address_2;
1169 p_to.asset_address_3 := p_from.asset_address_3;
1170 p_to.asset_address_4 := p_from.asset_address_4;
1171 p_to.asset_city := p_from.asset_city;
1172 p_to.asset_state := p_from.asset_state;
1173 p_to.asset_country := p_from.asset_country;
1174 p_to.tax_assessment_amount := p_from.tax_assessment_amount;
1175 p_to.tax_jurisdiction_city := p_from.tax_jurisdiction_city;
1176 p_to.tax_jurisdiction_city_rate := p_from.tax_jurisdiction_city_rate;
1177 p_to.tax_jurisdiction_county := p_from.tax_jurisdiction_county;
1178 p_to.tax_jurisdiction_county_rate := p_from.tax_jurisdiction_county_rate;
1179 p_to.tax_jurisdiction_state := p_from.tax_jurisdiction_state;
1180 p_to.tax_jurisdiction_state_rate := p_from.tax_jurisdiction_state_rate;
1181 p_to.tax_jurisdiction_school := p_from.tax_jurisdiction_school;
1182 p_to.tax_jurisdiction_school_rate := p_from.tax_jurisdiction_school_rate;
1183 p_to.tax_jurisdiction_country := p_from.tax_jurisdiction_country;
1184 p_to.tax_jurisdiction_country_rate := p_from.tax_jurisdiction_country_rate;
1185 p_to.tax_assessment_date := p_from.tax_assessment_date;
1186 p_to.milrate := p_from.milrate;
1187 p_to.property_tax_amount := p_from.property_tax_amount;
1188 p_to.oec := p_from.oec;
1189 p_to.created_by := p_from.created_by;
1190 p_to.creation_date := p_from.creation_date;
1191 p_to.last_updated_by := p_from.last_updated_by;
1192 p_to.last_update_date := p_from.last_update_date;
1193 p_to.last_update_login := p_from.last_update_login;
1194 p_to.contract_number := p_from.contract_number;
1195 p_to.sty_name := p_from.sty_name;
1196 p_to.sty_id := p_from.sty_id;
1197 p_to.invoice_date := p_from.invoice_date;
1198 p_to.amount := p_from.amount;
1199 p_to.org_id := p_from.org_id;
1200
1201 -- Addition for Est Property tax
1202 p_to.JURSDCTN_TYPE := p_from.JURSDCTN_TYPE;
1203 p_to.JURSDCTN_NAME := p_from.JURSDCTN_NAME;
1204 p_to.MLRT_TAX := p_from.MLRT_TAX;
1205 p_to.TAX_VENDOR_ID := p_from.TAX_VENDOR_ID;
1206 p_to.TAX_VENDOR_NAME := p_from.TAX_VENDOR_NAME;
1207 p_to.TAX_VENDOR_SITE_ID := p_from.TAX_VENDOR_SITE_ID;
1208 p_to.TAX_VENDOR_SITE_NAME := p_from.TAX_VENDOR_SITE_NAME;
1209 -- End addition for Est Property tax
1210 END migrate;
1211 PROCEDURE migrate (
1212 p_from IN ptcv_rec_type,
1213 p_to IN OUT NOCOPY ptct_rec_type
1214 ) IS
1215 BEGIN
1216 p_to.id := p_from.id;
1217 p_to.language := p_from.language;
1218 p_to.source_lang := p_from.source_lang;
1219 p_to.sfwt_flag := p_from.sfwt_flag;
1220 p_to.asset_description := p_from.asset_description;
1221 p_to.created_by := p_from.created_by;
1222 p_to.creation_date := p_from.creation_date;
1223 p_to.last_updated_by := p_from.last_updated_by;
1224 p_to.last_update_date := p_from.last_update_date;
1225 p_to.last_update_login := p_from.last_update_login;
1226 END migrate;
1227 PROCEDURE migrate (
1228 p_from IN ptct_rec_type,
1229 p_to IN OUT NOCOPY ptcv_rec_type
1230 ) IS
1231 BEGIN
1232 p_to.id := p_from.id;
1233 p_to.asset_description := p_from.asset_description;
1234 p_to.language := p_from.language;
1235 p_to.source_lang := p_from.source_lang;
1236 p_to.sfwt_flag := p_from.sfwt_flag;
1237 p_to.created_by := p_from.created_by;
1238 p_to.creation_date := p_from.creation_date;
1239 p_to.last_updated_by := p_from.last_updated_by;
1240 p_to.last_update_date := p_from.last_update_date;
1241 p_to.last_update_login := p_from.last_update_login;
1242 END migrate;
1243 ---------------------------------------------------------------------------
1244 -- PROCEDURE validate_row
1245 ---------------------------------------------------------------------------
1246 ------------------------------------------------
1247 -- validate_row for:OKL_PROPERTY_TAX_V --
1248 ------------------------------------------------
1249 PROCEDURE validate_row(
1250 p_api_version IN NUMBER,
1251 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1252 x_return_status OUT NOCOPY VARCHAR2,
1253 x_msg_count OUT NOCOPY NUMBER,
1254 x_msg_data OUT NOCOPY VARCHAR2,
1255 p_ptcv_rec IN ptcv_rec_type) IS
1256
1257 l_api_version CONSTANT NUMBER := 1;
1258 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1259 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1260 l_ptcv_rec ptcv_rec_type := p_ptcv_rec;
1261 l_ptc_rec ptc_rec_type;
1262 l_ptct_rec ptct_rec_type;
1263 BEGIN
1264 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1265 G_PKG_NAME,
1266 p_init_msg_list,
1267 l_api_version,
1268 p_api_version,
1269 '_PVT',
1270 x_return_status);
1271 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1272 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1273 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1274 RAISE OKC_API.G_EXCEPTION_ERROR;
1275 END IF;
1276 --- Validate all non-missing attributes (Item Level Validation)
1277 l_return_status := Validate_Attributes(l_ptcv_rec);
1278 --- If any errors happen abort API
1279 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1280 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1281 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1282 RAISE OKC_API.G_EXCEPTION_ERROR;
1283 END IF;
1284 l_return_status := Validate_Record(l_ptcv_rec);
1285 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1286 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1287 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1288 RAISE OKC_API.G_EXCEPTION_ERROR;
1289 END IF;
1290 x_return_status := l_return_status;
1291 EXCEPTION
1292 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1293 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1294 (
1295 l_api_name,
1296 G_PKG_NAME,
1297 'OKC_API.G_RET_STS_ERROR',
1298 x_msg_count,
1299 x_msg_data,
1300 '_PVT'
1301 );
1302 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1303 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1304 (
1305 l_api_name,
1306 G_PKG_NAME,
1307 'OKC_API.G_RET_STS_UNEXP_ERROR',
1308 x_msg_count,
1309 x_msg_data,
1310 '_PVT'
1311 );
1312 WHEN OTHERS THEN
1313 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1314 (
1315 l_api_name,
1316 G_PKG_NAME,
1317 'OTHERS',
1318 x_msg_count,
1319 x_msg_data,
1320 '_PVT'
1321 );
1322 END validate_row;
1323 -----------------------------------------------------------
1324 -- PL/SQL TBL validate_row for:OKL_PROPERTY_TAX_V --
1325 -----------------------------------------------------------
1326 PROCEDURE validate_row(
1327 p_api_version IN NUMBER,
1328 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1329 x_return_status OUT NOCOPY VARCHAR2,
1330 x_msg_count OUT NOCOPY NUMBER,
1331 x_msg_data OUT NOCOPY VARCHAR2,
1332 p_ptcv_tbl IN ptcv_tbl_type,
1333 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1334
1335 l_api_version CONSTANT NUMBER := 1;
1336 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
1337 i NUMBER := 0;
1338 BEGIN
1339 OKC_API.init_msg_list(p_init_msg_list);
1340 -- Make sure PL/SQL table has records in it before passing
1341 IF (p_ptcv_tbl.COUNT > 0) THEN
1342 i := p_ptcv_tbl.FIRST;
1343 LOOP
1344 DECLARE
1345 l_error_rec OKL_API.ERROR_REC_TYPE;
1346 BEGIN
1347 l_error_rec.api_name := l_api_name;
1348 l_error_rec.api_package := G_PKG_NAME;
1349 l_error_rec.idx := i;
1350 validate_row (
1351 p_api_version => p_api_version,
1352 p_init_msg_list => OKC_API.G_FALSE,
1353 x_return_status => l_error_rec.error_type,
1354 x_msg_count => l_error_rec.msg_count,
1355 x_msg_data => l_error_rec.msg_data,
1356 p_ptcv_rec => p_ptcv_tbl(i));
1357 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1358 l_error_rec.sqlcode := SQLCODE;
1359 load_error_tbl(l_error_rec, px_error_tbl);
1360 ELSE
1361 x_msg_count := l_error_rec.msg_count;
1362 x_msg_data := l_error_rec.msg_data;
1363 END IF;
1364 EXCEPTION
1365 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1366 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1367 l_error_rec.sqlcode := SQLCODE;
1368 load_error_tbl(l_error_rec, px_error_tbl);
1369 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1370 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1371 l_error_rec.sqlcode := SQLCODE;
1372 load_error_tbl(l_error_rec, px_error_tbl);
1373 WHEN OTHERS THEN
1374 l_error_rec.error_type := 'OTHERS';
1375 l_error_rec.sqlcode := SQLCODE;
1376 load_error_tbl(l_error_rec, px_error_tbl);
1377 END;
1378 EXIT WHEN (i = p_ptcv_tbl.LAST);
1379 i := p_ptcv_tbl.NEXT(i);
1380 END LOOP;
1381 END IF;
1382 -- Loop through the error_tbl to find the error with the highest severity
1383 -- and return it.
1384 x_return_status := find_highest_exception(px_error_tbl);
1385 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1386 EXCEPTION
1387 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1388 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1389 (
1390 l_api_name,
1391 G_PKG_NAME,
1392 'OKC_API.G_RET_STS_ERROR',
1393 x_msg_count,
1394 x_msg_data,
1395 '_PVT'
1396 );
1397 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1398 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1399 (
1400 l_api_name,
1401 G_PKG_NAME,
1402 'OKC_API.G_RET_STS_UNEXP_ERROR',
1403 x_msg_count,
1404 x_msg_data,
1405 '_PVT'
1406 );
1407 WHEN OTHERS THEN
1408 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1409 (
1410 l_api_name,
1411 G_PKG_NAME,
1412 'OTHERS',
1413 x_msg_count,
1414 x_msg_data,
1415 '_PVT'
1416 );
1417 END validate_row;
1418
1419 -----------------------------------------------------------
1420 -- PL/SQL TBL validate_row for:OKL_PROPERTY_TAX_V --
1421 -----------------------------------------------------------
1422 PROCEDURE validate_row(
1423 p_api_version IN NUMBER,
1424 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1425 x_return_status OUT NOCOPY VARCHAR2,
1426 x_msg_count OUT NOCOPY NUMBER,
1427 x_msg_data OUT NOCOPY VARCHAR2,
1428 p_ptcv_tbl IN ptcv_tbl_type) IS
1429
1430 l_api_version CONSTANT NUMBER := 1;
1431 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1432 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1433 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1434 BEGIN
1435 OKC_API.init_msg_list(p_init_msg_list);
1436 -- Make sure PL/SQL table has records in it before passing
1437 IF (p_ptcv_tbl.COUNT > 0) THEN
1438 validate_row (
1439 p_api_version => p_api_version,
1440 p_init_msg_list => OKC_API.G_FALSE,
1441 x_return_status => x_return_status,
1442 x_msg_count => x_msg_count,
1443 x_msg_data => x_msg_data,
1444 p_ptcv_tbl => p_ptcv_tbl,
1445 px_error_tbl => l_error_tbl);
1446 END IF;
1447 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1448 EXCEPTION
1449 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1450 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1451 (
1452 l_api_name,
1453 G_PKG_NAME,
1454 'OKC_API.G_RET_STS_ERROR',
1455 x_msg_count,
1456 x_msg_data,
1457 '_PVT'
1458 );
1459 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1460 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1461 (
1462 l_api_name,
1463 G_PKG_NAME,
1464 'OKC_API.G_RET_STS_UNEXP_ERROR',
1465 x_msg_count,
1466 x_msg_data,
1467 '_PVT'
1468 );
1469 WHEN OTHERS THEN
1470 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1471 (
1472 l_api_name,
1473 G_PKG_NAME,
1474 'OTHERS',
1475 x_msg_count,
1476 x_msg_data,
1477 '_PVT'
1478 );
1479 END validate_row;
1480
1481 ---------------------------------------------------------------------------
1482 -- PROCEDURE insert_row
1483 ---------------------------------------------------------------------------
1484 ----------------------------------------------
1485 -- insert_row for:OKL_PROPERTY_TAX_B --
1486 ----------------------------------------------
1487 PROCEDURE insert_row(
1488 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1489 x_return_status OUT NOCOPY VARCHAR2,
1490 x_msg_count OUT NOCOPY NUMBER,
1491 x_msg_data OUT NOCOPY VARCHAR2,
1492 p_ptc_rec IN ptc_rec_type,
1493 x_ptc_rec OUT NOCOPY ptc_rec_type) IS
1494
1495 l_api_version CONSTANT NUMBER := 1;
1496 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1497 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1498 l_ptc_rec ptc_rec_type := p_ptc_rec;
1499 l_def_ptc_rec ptc_rec_type;
1500 --------------------------------------------------
1501 -- Set_Attributes for:OKL_PROPERTY_TAX_B --
1502 --------------------------------------------------
1503 FUNCTION Set_Attributes (
1504 p_ptc_rec IN ptc_rec_type,
1505 x_ptc_rec OUT NOCOPY ptc_rec_type
1506 ) RETURN VARCHAR2 IS
1507 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1508 BEGIN
1509 x_ptc_rec := p_ptc_rec;
1510 RETURN(l_return_status);
1511 END Set_Attributes;
1512 BEGIN
1513 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1514 p_init_msg_list,
1515 '_PVT',
1516 x_return_status);
1517 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1518 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1519 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1520 RAISE OKC_API.G_EXCEPTION_ERROR;
1521 END IF;
1522 --- Setting item atributes
1523 l_return_status := Set_Attributes(
1524 p_ptc_rec, -- IN
1525 l_ptc_rec); -- OUT
1526 --- If any errors happen abort API
1527 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1528 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1529 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1530 RAISE OKC_API.G_EXCEPTION_ERROR;
1531 END IF;
1532 INSERT INTO OKL_PROPERTY_TAX_B(
1533 id,
1534 sequence_number,
1535 asset_id,
1536 asset_number,
1537 khr_id,
1538 kle_id,
1539 asset_units,
1540 asset_address_1,
1541 asset_address_2,
1542 asset_address_3,
1543 asset_address_4,
1544 asset_city,
1545 asset_state,
1546 asset_country,
1547 tax_assessment_amount,
1548 tax_jurisdiction_city,
1549 tax_jurisdiction_city_rate,
1550 tax_jurisdiction_county,
1551 tax_jurisdiction_county_rate,
1552 tax_jurisdiction_state,
1553 tax_jurisdiction_state_rate,
1554 tax_jurisdiction_school,
1555 tax_jurisdiction_school_rate,
1556 tax_jurisdiction_country,
1557 tax_jurisdiction_country_rate,
1558 tax_assessment_date,
1559 milrate,
1560 property_tax_amount,
1561 oec,
1562 created_by,
1563 creation_date,
1564 last_updated_by,
1565 last_update_date,
1566 last_update_login
1567 ,contract_number
1568 ,sty_name
1569 ,sty_id
1570 ,invoice_date
1571 ,amount
1572 ,org_id
1573 ,JURSDCTN_TYPE
1574 ,JURSDCTN_NAME
1575 ,MLRT_TAX
1576 ,TAX_VENDOR_ID
1577 ,TAX_VENDOR_NAME
1578 ,TAX_VENDOR_SITE_ID
1579 ,TAX_VENDOR_SITE_NAME
1580 )
1581 VALUES (
1582 l_ptc_rec.id,
1583 l_ptc_rec.sequence_number,
1584 l_ptc_rec.asset_id,
1585 l_ptc_rec.asset_number,
1586 l_ptc_rec.khr_id,
1587 l_ptc_rec.kle_id,
1588 l_ptc_rec.asset_units,
1589 l_ptc_rec.asset_address_1,
1590 l_ptc_rec.asset_address_2,
1591 l_ptc_rec.asset_address_3,
1592 l_ptc_rec.asset_address_4,
1593 l_ptc_rec.asset_city,
1594 l_ptc_rec.asset_state,
1595 l_ptc_rec.asset_country,
1596 l_ptc_rec.tax_assessment_amount,
1597 l_ptc_rec.tax_jurisdiction_city,
1598 l_ptc_rec.tax_jurisdiction_city_rate,
1599 l_ptc_rec.tax_jurisdiction_county,
1600 l_ptc_rec.tax_jurisdiction_county_rate,
1601 l_ptc_rec.tax_jurisdiction_state,
1602 l_ptc_rec.tax_jurisdiction_state_rate,
1603 l_ptc_rec.tax_jurisdiction_school,
1604 l_ptc_rec.tax_jurisdiction_school_rate,
1605 l_ptc_rec.tax_jurisdiction_country,
1606 l_ptc_rec.tax_jurisdiction_country_rate,
1607 l_ptc_rec.tax_assessment_date,
1608 l_ptc_rec.milrate,
1609 l_ptc_rec.property_tax_amount,
1610 l_ptc_rec.oec,
1611 l_ptc_rec.created_by,
1612 l_ptc_rec.creation_date,
1613 l_ptc_rec.last_updated_by,
1614 l_ptc_rec.last_update_date,
1615 l_ptc_rec.last_update_login
1616 ,l_ptc_rec.contract_number
1617 ,l_ptc_rec.sty_name
1618 ,l_ptc_rec.sty_id
1619 ,l_ptc_rec.invoice_date
1620 ,l_ptc_rec.amount
1621 ,l_ptc_rec.org_id
1622 -- Addition for Est Property Tax
1623 ,l_ptc_rec.JURSDCTN_TYPE
1624 ,l_ptc_rec.JURSDCTN_NAME
1625 ,l_ptc_rec.MLRT_TAX
1626 ,l_ptc_rec.TAX_VENDOR_ID
1627 ,l_ptc_rec.TAX_VENDOR_NAME
1628 ,l_ptc_rec.TAX_VENDOR_SITE_ID
1629 ,l_ptc_rec.TAX_VENDOR_SITE_NAME
1630 -- End Addition for Est Property Tax
1631 );
1632 -- Set OUT values
1633 x_ptc_rec := l_ptc_rec;
1634 x_return_status := l_return_status;
1635 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1636 EXCEPTION
1637 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1638 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1639 (
1640 l_api_name,
1641 G_PKG_NAME,
1642 'OKC_API.G_RET_STS_ERROR',
1643 x_msg_count,
1644 x_msg_data,
1645 '_PVT'
1646 );
1647 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1648 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1649 (
1650 l_api_name,
1651 G_PKG_NAME,
1652 'OKC_API.G_RET_STS_UNEXP_ERROR',
1653 x_msg_count,
1654 x_msg_data,
1655 '_PVT'
1656 );
1657 WHEN OTHERS THEN
1658 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1659 (
1660 l_api_name,
1661 G_PKG_NAME,
1662 'OTHERS',
1663 x_msg_count,
1664 x_msg_data,
1665 '_PVT'
1666 );
1667 END insert_row;
1668 -----------------------------------------------
1669 -- insert_row for:OKL_PROPERTY_TAX_TL --
1670 -----------------------------------------------
1671 PROCEDURE insert_row(
1672 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1673 x_return_status OUT NOCOPY VARCHAR2,
1674 x_msg_count OUT NOCOPY NUMBER,
1675 x_msg_data OUT NOCOPY VARCHAR2,
1676 p_ptct_rec IN ptct_rec_type,
1677 x_ptct_rec OUT NOCOPY ptct_rec_type) IS
1678
1679 l_api_version CONSTANT NUMBER := 1;
1680 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1681 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1682 l_ptct_rec ptct_rec_type := p_ptct_rec;
1683 l_def_ptct_rec ptct_rec_type;
1684 CURSOR get_languages IS
1685 SELECT *
1686 FROM FND_LANGUAGES
1687 WHERE INSTALLED_FLAG IN ('I', 'B');
1688 ---------------------------------------------------
1689 -- Set_Attributes for:OKL_PROPERTY_TAX_TL --
1690 ---------------------------------------------------
1691 FUNCTION Set_Attributes (
1692 p_ptct_rec IN ptct_rec_type,
1693 x_ptct_rec OUT NOCOPY ptct_rec_type
1694 ) RETURN VARCHAR2 IS
1695 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1696 BEGIN
1697 x_ptct_rec := p_ptct_rec;
1698 x_ptct_rec.LANGUAGE := USERENV('LANG');
1699 x_ptct_rec.SOURCE_LANG := USERENV('LANG');
1700 RETURN(l_return_status);
1701 END Set_Attributes;
1702 BEGIN
1703 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1704 p_init_msg_list,
1705 '_PVT',
1706 x_return_status);
1707 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1708 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1709 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1710 RAISE OKC_API.G_EXCEPTION_ERROR;
1711 END IF;
1712 --- Setting item attributes
1713 l_return_status := Set_Attributes(
1714 p_ptct_rec, -- IN
1715 l_ptct_rec); -- OUT
1716 --- If any errors happen abort API
1717 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1718 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1719 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1720 RAISE OKC_API.G_EXCEPTION_ERROR;
1721 END IF;
1722 FOR l_lang_rec IN get_languages LOOP
1723 l_ptct_rec.language := l_lang_rec.language_code;
1724 INSERT INTO OKL_PROPERTY_TAX_TL(
1725 id,
1726 language,
1727 source_lang,
1728 sfwt_flag,
1729 asset_description,
1730 created_by,
1731 creation_date,
1732 last_updated_by,
1733 last_update_date,
1734 last_update_login)
1735 VALUES (
1736 l_ptct_rec.id,
1737 l_ptct_rec.language,
1738 l_ptct_rec.source_lang,
1739 l_ptct_rec.sfwt_flag,
1740 l_ptct_rec.asset_description,
1741 l_ptct_rec.created_by,
1742 l_ptct_rec.creation_date,
1743 l_ptct_rec.last_updated_by,
1744 l_ptct_rec.last_update_date,
1745 l_ptct_rec.last_update_login);
1746 END LOOP;
1747 -- Set OUT values
1748 x_ptct_rec := l_ptct_rec;
1749 x_return_status := l_return_status;
1750 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1751 EXCEPTION
1752 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1753 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1754 (
1755 l_api_name,
1756 G_PKG_NAME,
1757 'OKC_API.G_RET_STS_ERROR',
1758 x_msg_count,
1759 x_msg_data,
1760 '_PVT'
1761 );
1762 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1763 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1764 (
1765 l_api_name,
1766 G_PKG_NAME,
1767 'OKC_API.G_RET_STS_UNEXP_ERROR',
1768 x_msg_count,
1769 x_msg_data,
1770 '_PVT'
1771 );
1772 WHEN OTHERS THEN
1773 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1774 (
1775 l_api_name,
1776 G_PKG_NAME,
1777 'OTHERS',
1778 x_msg_count,
1779 x_msg_data,
1780 '_PVT'
1781 );
1782 END insert_row;
1783 -----------------------------------------------
1784 -- insert_row for :OKL_PROPERTY_TAX_V --
1785 -----------------------------------------------
1786 PROCEDURE insert_row(
1787 p_api_version IN NUMBER,
1788 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1789 x_return_status OUT NOCOPY VARCHAR2,
1790 x_msg_count OUT NOCOPY NUMBER,
1791 x_msg_data OUT NOCOPY VARCHAR2,
1792 p_ptcv_rec IN ptcv_rec_type,
1793 x_ptcv_rec OUT NOCOPY ptcv_rec_type) IS
1794
1795 l_api_version CONSTANT NUMBER := 1;
1796 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1797 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1798 l_ptcv_rec ptcv_rec_type := p_ptcv_rec;
1799 l_def_ptcv_rec ptcv_rec_type;
1800 l_ptc_rec ptc_rec_type;
1801 lx_ptc_rec ptc_rec_type;
1802 l_ptct_rec ptct_rec_type;
1803 lx_ptct_rec ptct_rec_type;
1804 -------------------------------
1805 -- FUNCTION fill_who_columns --
1806 -------------------------------
1807 FUNCTION fill_who_columns (
1808 p_ptcv_rec IN ptcv_rec_type
1809 ) RETURN ptcv_rec_type IS
1810 l_ptcv_rec ptcv_rec_type := p_ptcv_rec;
1811 BEGIN
1812 l_ptcv_rec.CREATION_DATE := SYSDATE;
1813 l_ptcv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1814 l_ptcv_rec.LAST_UPDATE_DATE := l_ptcv_rec.CREATION_DATE;
1815 l_ptcv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1816 l_ptcv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1817 RETURN(l_ptcv_rec);
1818 END fill_who_columns;
1819 --------------------------------------------------
1820 -- Set_Attributes for:OKL_PROPERTY_TAX_V --
1821 --------------------------------------------------
1822 FUNCTION Set_Attributes (
1823 p_ptcv_rec IN ptcv_rec_type,
1824 x_ptcv_rec OUT NOCOPY ptcv_rec_type
1825 ) RETURN VARCHAR2 IS
1826 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1827 BEGIN
1828 x_ptcv_rec := p_ptcv_rec;
1829 x_ptcv_rec.SFWT_FLAG := 'N';
1830 RETURN(l_return_status);
1831 END Set_Attributes;
1832 BEGIN
1833 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1834 G_PKG_NAME,
1835 p_init_msg_list,
1836 l_api_version,
1837 p_api_version,
1838 '_PVT',
1839 x_return_status);
1840 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1841 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1842 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1843 RAISE OKC_API.G_EXCEPTION_ERROR;
1844 END IF;
1845 l_ptcv_rec := null_out_defaults(p_ptcv_rec);
1846 -- Set primary key value
1847 l_ptcv_rec.ID := get_seq_id;
1848 -- Setting item attributes
1849 l_return_Status := Set_Attributes(
1850 l_ptcv_rec, -- IN
1851 l_def_ptcv_rec); -- OUT
1852 --- If any errors happen abort API
1853 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1854 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1855 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1856 RAISE OKC_API.G_EXCEPTION_ERROR;
1857 END IF;
1858 l_def_ptcv_rec := fill_who_columns(l_def_ptcv_rec);
1859 --- Validate all non-missing attributes (Item Level Validation)
1860 l_return_status := Validate_Attributes(l_def_ptcv_rec);
1861 --- If any errors happen abort API
1862 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1863 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1864 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1865 RAISE OKC_API.G_EXCEPTION_ERROR;
1866 END IF;
1867 l_return_status := Validate_Record(l_def_ptcv_rec);
1868 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1869 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1870 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1871 RAISE OKC_API.G_EXCEPTION_ERROR;
1872 END IF;
1873 -----------------------------------------
1874 -- Move VIEW record to "Child" records --
1875 -----------------------------------------
1876 migrate(l_def_ptcv_rec, l_ptc_rec);
1877 migrate(l_def_ptcv_rec, l_ptct_rec);
1878 -----------------------------------------------
1879 -- Call the INSERT_ROW for each child record --
1880 -----------------------------------------------
1881 insert_row(
1882 p_init_msg_list,
1883 l_return_status,
1884 x_msg_count,
1885 x_msg_data,
1886 l_ptc_rec,
1887 lx_ptc_rec
1888 );
1889 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1890 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1891 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1892 RAISE OKC_API.G_EXCEPTION_ERROR;
1893 END IF;
1894 migrate(lx_ptc_rec, l_def_ptcv_rec);
1895 insert_row(
1896 p_init_msg_list,
1897 l_return_status,
1898 x_msg_count,
1899 x_msg_data,
1900 l_ptct_rec,
1901 lx_ptct_rec
1902 );
1903 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1904 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1905 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1906 RAISE OKC_API.G_EXCEPTION_ERROR;
1907 END IF;
1908 migrate(lx_ptct_rec, l_def_ptcv_rec);
1909 -- Set OUT values
1910 x_ptcv_rec := l_def_ptcv_rec;
1911 x_return_status := l_return_status;
1912 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1913 EXCEPTION
1914 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1915 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1916 (
1917 l_api_name,
1918 G_PKG_NAME,
1919 'OKC_API.G_RET_STS_ERROR',
1920 x_msg_count,
1921 x_msg_data,
1922 '_PVT'
1923 );
1924 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1925 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1926 (
1927 l_api_name,
1928 G_PKG_NAME,
1929 'OKC_API.G_RET_STS_UNEXP_ERROR',
1930 x_msg_count,
1931 x_msg_data,
1932 '_PVT'
1933 );
1934 WHEN OTHERS THEN
1935 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1936 (
1937 l_api_name,
1938 G_PKG_NAME,
1939 'OTHERS',
1940 x_msg_count,
1941 x_msg_data,
1942 '_PVT'
1943 );
1944 END insert_row;
1945 ----------------------------------------
1946 -- PL/SQL TBL insert_row for:PTCV_TBL --
1947 ----------------------------------------
1948 PROCEDURE insert_row(
1949 p_api_version IN NUMBER,
1950 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1951 x_return_status OUT NOCOPY VARCHAR2,
1952 x_msg_count OUT NOCOPY NUMBER,
1953 x_msg_data OUT NOCOPY VARCHAR2,
1954 p_ptcv_tbl IN ptcv_tbl_type,
1955 x_ptcv_tbl OUT NOCOPY ptcv_tbl_type,
1956 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1957
1958 l_api_version CONSTANT NUMBER := 1;
1959 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1960 i NUMBER := 0;
1961 BEGIN
1962 OKC_API.init_msg_list(p_init_msg_list);
1963 -- Make sure PL/SQL table has records in it before passing
1964 IF (p_ptcv_tbl.COUNT > 0) THEN
1965 i := p_ptcv_tbl.FIRST;
1966 LOOP
1967 DECLARE
1968 l_error_rec OKL_API.ERROR_REC_TYPE;
1969 BEGIN
1970 l_error_rec.api_name := l_api_name;
1971 l_error_rec.api_package := G_PKG_NAME;
1972 l_error_rec.idx := i;
1973 insert_row (
1974 p_api_version => p_api_version,
1975 p_init_msg_list => OKC_API.G_FALSE,
1976 x_return_status => l_error_rec.error_type,
1977 x_msg_count => l_error_rec.msg_count,
1978 x_msg_data => l_error_rec.msg_data,
1979 p_ptcv_rec => p_ptcv_tbl(i),
1980 x_ptcv_rec => x_ptcv_tbl(i));
1981 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1982 l_error_rec.sqlcode := SQLCODE;
1983 load_error_tbl(l_error_rec, px_error_tbl);
1984 ELSE
1985 x_msg_count := l_error_rec.msg_count;
1986 x_msg_data := l_error_rec.msg_data;
1987 END IF;
1988 EXCEPTION
1989 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1990 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1991 l_error_rec.sqlcode := SQLCODE;
1992 load_error_tbl(l_error_rec, px_error_tbl);
1993 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1994 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1995 l_error_rec.sqlcode := SQLCODE;
1996 load_error_tbl(l_error_rec, px_error_tbl);
1997 WHEN OTHERS THEN
1998 l_error_rec.error_type := 'OTHERS';
1999 l_error_rec.sqlcode := SQLCODE;
2000 load_error_tbl(l_error_rec, px_error_tbl);
2001 END;
2002 EXIT WHEN (i = p_ptcv_tbl.LAST);
2003 i := p_ptcv_tbl.NEXT(i);
2004 END LOOP;
2005 END IF;
2006 -- Loop through the error_tbl to find the error with the highest severity
2007 -- and return it.
2008 x_return_status := find_highest_exception(px_error_tbl);
2009 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2010 EXCEPTION
2011 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2012 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2013 (
2014 l_api_name,
2015 G_PKG_NAME,
2016 'OKC_API.G_RET_STS_ERROR',
2017 x_msg_count,
2018 x_msg_data,
2019 '_PVT'
2020 );
2021 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2022 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2023 (
2024 l_api_name,
2025 G_PKG_NAME,
2026 'OKC_API.G_RET_STS_UNEXP_ERROR',
2027 x_msg_count,
2028 x_msg_data,
2029 '_PVT'
2030 );
2031 WHEN OTHERS THEN
2032 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2033 (
2034 l_api_name,
2035 G_PKG_NAME,
2036 'OTHERS',
2037 x_msg_count,
2038 x_msg_data,
2039 '_PVT'
2040 );
2041 END insert_row;
2042
2043 ----------------------------------------
2044 -- PL/SQL TBL insert_row for:PTCV_TBL --
2045 ----------------------------------------
2046 PROCEDURE insert_row(
2047 p_api_version IN NUMBER,
2048 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2049 x_return_status OUT NOCOPY VARCHAR2,
2050 x_msg_count OUT NOCOPY NUMBER,
2051 x_msg_data OUT NOCOPY VARCHAR2,
2052 p_ptcv_tbl IN ptcv_tbl_type,
2053 x_ptcv_tbl OUT NOCOPY ptcv_tbl_type) IS
2054
2055 l_api_version CONSTANT NUMBER := 1;
2056 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2057 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2058 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2059 BEGIN
2060 OKC_API.init_msg_list(p_init_msg_list);
2061 -- Make sure PL/SQL table has records in it before passing
2062 IF (p_ptcv_tbl.COUNT > 0) THEN
2063 insert_row (
2064 p_api_version => p_api_version,
2065 p_init_msg_list => OKC_API.G_FALSE,
2066 x_return_status => x_return_status,
2067 x_msg_count => x_msg_count,
2068 x_msg_data => x_msg_data,
2069 p_ptcv_tbl => p_ptcv_tbl,
2070 x_ptcv_tbl => x_ptcv_tbl,
2071 px_error_tbl => l_error_tbl);
2072 END IF;
2073 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2074 EXCEPTION
2075 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2076 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2077 (
2078 l_api_name,
2079 G_PKG_NAME,
2080 'OKC_API.G_RET_STS_ERROR',
2081 x_msg_count,
2082 x_msg_data,
2083 '_PVT'
2084 );
2085 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2086 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2087 (
2088 l_api_name,
2089 G_PKG_NAME,
2090 'OKC_API.G_RET_STS_UNEXP_ERROR',
2091 x_msg_count,
2092 x_msg_data,
2093 '_PVT'
2094 );
2095 WHEN OTHERS THEN
2096 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2097 (
2098 l_api_name,
2099 G_PKG_NAME,
2100 'OTHERS',
2101 x_msg_count,
2102 x_msg_data,
2103 '_PVT'
2104 );
2105 END insert_row;
2106
2107 ---------------------------------------------------------------------------
2108 -- PROCEDURE lock_row
2109 ---------------------------------------------------------------------------
2110 --------------------------------------------
2111 -- lock_row for:OKL_PROPERTY_TAX_B --
2112 --------------------------------------------
2113 PROCEDURE lock_row(
2114 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2115 x_return_status OUT NOCOPY VARCHAR2,
2116 x_msg_count OUT NOCOPY NUMBER,
2117 x_msg_data OUT NOCOPY VARCHAR2,
2118 p_ptc_rec IN ptc_rec_type) IS
2119
2120 E_Resource_Busy EXCEPTION;
2121 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2122 CURSOR lock_csr (p_ptc_rec IN ptc_rec_type) IS
2123 SELECT *
2124 FROM OKL_PROPERTY_TAX_B
2125 WHERE ID = p_ptc_rec.id
2126 FOR UPDATE NOWAIT;
2127
2128 l_api_version CONSTANT NUMBER := 1;
2129 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2130 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2131 l_lock_var lock_csr%ROWTYPE;
2132 l_row_notfound BOOLEAN := FALSE;
2133 lc_row_notfound BOOLEAN := FALSE;
2134 BEGIN
2135 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2136 p_init_msg_list,
2137 '_PVT',
2138 x_return_status);
2139 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2140 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2141 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2142 RAISE OKC_API.G_EXCEPTION_ERROR;
2143 END IF;
2144 BEGIN
2145 OPEN lock_csr(p_ptc_rec);
2146 FETCH lock_csr INTO l_lock_var;
2147 l_row_notfound := lock_csr%NOTFOUND;
2148 CLOSE lock_csr;
2149 EXCEPTION
2150 WHEN E_Resource_Busy THEN
2151 IF (lock_csr%ISOPEN) THEN
2152 CLOSE lock_csr;
2153 END IF;
2154 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2155 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2156 END;
2157
2158 IF ( l_row_notfound ) THEN
2159 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2160 RAISE OKC_API.G_EXCEPTION_ERROR;
2161 --Commented by rvaduri
2162 -- This is not correct, we should go by object version number
2163 -- but this is not present in the table for now.
2164 /*
2165 ELSE
2166 IF (l_lock_var.id <> p_ptc_rec.id) THEN
2167 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2168 RAISE OKC_API.G_EXCEPTION_ERROR;
2169 END IF;
2170 IF (l_lock_var.sequence_number <> p_ptc_rec.sequence_number) THEN
2171 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2172 RAISE OKC_API.G_EXCEPTION_ERROR;
2173 END IF;
2174 IF (l_lock_var.asset_id <> p_ptc_rec.asset_id) THEN
2175 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2176 RAISE OKC_API.G_EXCEPTION_ERROR;
2177 END IF;
2178 IF (l_lock_var.asset_number <> p_ptc_rec.asset_number) THEN
2179 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2180 RAISE OKC_API.G_EXCEPTION_ERROR;
2181 END IF;
2182 IF (l_lock_var.khr_id <> p_ptc_rec.khr_id) THEN
2183 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2184 RAISE OKC_API.G_EXCEPTION_ERROR;
2185 END IF;
2186 IF (l_lock_var.kle_id <> p_ptc_rec.kle_id) THEN
2187 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2188 RAISE OKC_API.G_EXCEPTION_ERROR;
2189 END IF;
2190 IF (l_lock_var.asset_units <> p_ptc_rec.asset_units) THEN
2191 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2192 RAISE OKC_API.G_EXCEPTION_ERROR;
2193 END IF;
2194 IF (l_lock_var.asset_address_1 <> p_ptc_rec.asset_address_1) THEN
2195 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2196 RAISE OKC_API.G_EXCEPTION_ERROR;
2197 END IF;
2198 IF (l_lock_var.asset_address_2 <> p_ptc_rec.asset_address_2) THEN
2199 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2200 RAISE OKC_API.G_EXCEPTION_ERROR;
2201 END IF;
2202 IF (l_lock_var.asset_address_3 <> p_ptc_rec.asset_address_3) THEN
2203 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2204 RAISE OKC_API.G_EXCEPTION_ERROR;
2205 END IF;
2206 IF (l_lock_var.asset_address_4 <> p_ptc_rec.asset_address_4) THEN
2207 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2208 RAISE OKC_API.G_EXCEPTION_ERROR;
2209 END IF;
2210 IF (l_lock_var.asset_city <> p_ptc_rec.asset_city) THEN
2211 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2212 RAISE OKC_API.G_EXCEPTION_ERROR;
2213 END IF;
2214 IF (l_lock_var.asset_state <> p_ptc_rec.asset_state) THEN
2215 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2216 RAISE OKC_API.G_EXCEPTION_ERROR;
2217 END IF;
2218 IF (l_lock_var.asset_country <> p_ptc_rec.asset_country) THEN
2219 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2220 RAISE OKC_API.G_EXCEPTION_ERROR;
2221 END IF;
2222 IF (l_lock_var.tax_assessment_amount <> p_ptc_rec.tax_assessment_amount) THEN
2223 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2224 RAISE OKC_API.G_EXCEPTION_ERROR;
2225 END IF;
2226 IF (l_lock_var.tax_jurisdiction_city <> p_ptc_rec.tax_jurisdiction_city) THEN
2227 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2228 RAISE OKC_API.G_EXCEPTION_ERROR;
2229 END IF;
2230 IF (l_lock_var.tax_jurisdiction_city_rate <> p_ptc_rec.tax_jurisdiction_city_rate) THEN
2231 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2232 RAISE OKC_API.G_EXCEPTION_ERROR;
2233 END IF;
2234 IF (l_lock_var.tax_jurisdiction_county <> p_ptc_rec.tax_jurisdiction_county) THEN
2235 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2236 RAISE OKC_API.G_EXCEPTION_ERROR;
2237 END IF;
2238 IF (l_lock_var.tax_jurisdiction_county_rate <> p_ptc_rec.tax_jurisdiction_county_rate) THEN
2239 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2240 RAISE OKC_API.G_EXCEPTION_ERROR;
2241 END IF;
2242 IF (l_lock_var.tax_jurisdiction_state <> p_ptc_rec.tax_jurisdiction_state) THEN
2243 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2244 RAISE OKC_API.G_EXCEPTION_ERROR;
2245 END IF;
2246 IF (l_lock_var.tax_jurisdiction_state_rate <> p_ptc_rec.tax_jurisdiction_state_rate) THEN
2247 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2248 RAISE OKC_API.G_EXCEPTION_ERROR;
2249 END IF;
2250 IF (l_lock_var.tax_jurisdiction_school <> p_ptc_rec.tax_jurisdiction_school) THEN
2251 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2252 RAISE OKC_API.G_EXCEPTION_ERROR;
2253 END IF;
2254 IF (l_lock_var.tax_jurisdiction_school_rate <> p_ptc_rec.tax_jurisdiction_school_rate) THEN
2255 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2256 RAISE OKC_API.G_EXCEPTION_ERROR;
2257 END IF;
2258 IF (l_lock_var.tax_jurisdiction_country <> p_ptc_rec.tax_jurisdiction_country) THEN
2259 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2260 RAISE OKC_API.G_EXCEPTION_ERROR;
2261 END IF;
2262 IF (l_lock_var.tax_jurisdiction_country_rate <> p_ptc_rec.tax_jurisdiction_country_rate) THEN
2263 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2264 RAISE OKC_API.G_EXCEPTION_ERROR;
2265 END IF;
2266 IF (l_lock_var.tax_assessment_date <> p_ptc_rec.tax_assessment_date) THEN
2267 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2268 RAISE OKC_API.G_EXCEPTION_ERROR;
2269 END IF;
2270 IF (l_lock_var.milrate <> p_ptc_rec.milrate) THEN
2271 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2272 RAISE OKC_API.G_EXCEPTION_ERROR;
2273 END IF;
2274 IF (l_lock_var.property_tax_amount <> p_ptc_rec.property_tax_amount) THEN
2275 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2276 RAISE OKC_API.G_EXCEPTION_ERROR;
2277 END IF;
2278 IF (l_lock_var.oec <> p_ptc_rec.oec) THEN
2279 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2280 RAISE OKC_API.G_EXCEPTION_ERROR;
2281 END IF;
2282 IF (l_lock_var.created_by <> p_ptc_rec.created_by) THEN
2283 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2284 RAISE OKC_API.G_EXCEPTION_ERROR;
2285 END IF;
2286 IF (l_lock_var.creation_date <> p_ptc_rec.creation_date) THEN
2287 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2288 RAISE OKC_API.G_EXCEPTION_ERROR;
2289 END IF;
2290 IF (l_lock_var.last_updated_by <> p_ptc_rec.last_updated_by) THEN
2291 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2292 RAISE OKC_API.G_EXCEPTION_ERROR;
2293 END IF;
2294 IF (l_lock_var.last_update_date <> p_ptc_rec.last_update_date) THEN
2295 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2296 RAISE OKC_API.G_EXCEPTION_ERROR;
2297 END IF;
2298 IF (l_lock_var.last_update_login <> p_ptc_rec.last_update_login) THEN
2299 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2300 RAISE OKC_API.G_EXCEPTION_ERROR;
2301 END IF;
2302 IF (l_lock_var.contract_number <> p_ptc_rec.contract_number) THEN
2303 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2304 RAISE OKC_API.G_EXCEPTION_ERROR;
2305 END IF;
2306 IF (l_lock_var.sty_name <> p_ptc_rec.sty_name) THEN
2307 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2308 RAISE OKC_API.G_EXCEPTION_ERROR;
2309 END IF;
2310 IF (l_lock_var.sty_id <> p_ptc_rec.sty_id) THEN
2311 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2312 RAISE OKC_API.G_EXCEPTION_ERROR;
2313 END IF;
2314 IF (l_lock_var.invoice_date <> p_ptc_rec.invoice_date) THEN
2315 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2316 RAISE OKC_API.G_EXCEPTION_ERROR;
2317 END IF;
2318 IF (l_lock_var.amount <> p_ptc_rec.amount) THEN
2319 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2320 RAISE OKC_API.G_EXCEPTION_ERROR;
2321 END IF;
2322 IF (l_lock_var.org_id <> p_ptc_rec.org_id) THEN
2323 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2324 RAISE OKC_API.G_EXCEPTION_ERROR;
2325 END IF;
2326 */ --end comment by rvaduri
2327 END IF;
2328 x_return_status := l_return_status;
2329 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2330 EXCEPTION
2331 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2332 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2333 (
2334 l_api_name,
2335 G_PKG_NAME,
2336 'OKC_API.G_RET_STS_ERROR',
2337 x_msg_count,
2338 x_msg_data,
2339 '_PVT'
2340 );
2341 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2342 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2343 (
2344 l_api_name,
2345 G_PKG_NAME,
2346 'OKC_API.G_RET_STS_UNEXP_ERROR',
2347 x_msg_count,
2348 x_msg_data,
2349 '_PVT'
2350 );
2351 WHEN OTHERS THEN
2352 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2353 (
2354 l_api_name,
2355 G_PKG_NAME,
2356 'OTHERS',
2357 x_msg_count,
2358 x_msg_data,
2359 '_PVT'
2360 );
2361 END lock_row;
2362 ---------------------------------------------
2363 -- lock_row for:OKL_PROPERTY_TAX_TL --
2364 ---------------------------------------------
2365 PROCEDURE lock_row(
2366 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2367 x_return_status OUT NOCOPY VARCHAR2,
2368 x_msg_count OUT NOCOPY NUMBER,
2369 x_msg_data OUT NOCOPY VARCHAR2,
2370 p_ptct_rec IN ptct_rec_type) IS
2371
2372 E_Resource_Busy EXCEPTION;
2373 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2374 CURSOR lock_csr (p_ptct_rec IN ptct_rec_type) IS
2375 SELECT *
2376 FROM OKL_PROPERTY_TAX_TL
2377 WHERE ID = p_ptct_rec.id
2378 FOR UPDATE NOWAIT;
2379
2380 l_api_version CONSTANT NUMBER := 1;
2381 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2382 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2383 l_lock_var lock_csr%ROWTYPE;
2384 l_row_notfound BOOLEAN := FALSE;
2385 lc_row_notfound BOOLEAN := FALSE;
2386 BEGIN
2387 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2388 p_init_msg_list,
2389 '_PVT',
2390 x_return_status);
2391 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2392 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2393 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2394 RAISE OKC_API.G_EXCEPTION_ERROR;
2395 END IF;
2396 BEGIN
2397 OPEN lock_csr(p_ptct_rec);
2398 FETCH lock_csr INTO l_lock_var;
2399 l_row_notfound := lock_csr%NOTFOUND;
2400 CLOSE lock_csr;
2401 EXCEPTION
2402 WHEN E_Resource_Busy THEN
2403 IF (lock_csr%ISOPEN) THEN
2404 CLOSE lock_csr;
2405 END IF;
2406 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2407 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2408 END;
2409
2410 IF ( l_row_notfound ) THEN
2411 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2412 RAISE OKC_API.G_EXCEPTION_ERROR;
2413 END IF;
2414 x_return_status := l_return_status;
2415 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2416 EXCEPTION
2417 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2418 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2419 (
2420 l_api_name,
2421 G_PKG_NAME,
2422 'OKC_API.G_RET_STS_ERROR',
2423 x_msg_count,
2424 x_msg_data,
2425 '_PVT'
2426 );
2427 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2428 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2429 (
2430 l_api_name,
2431 G_PKG_NAME,
2432 'OKC_API.G_RET_STS_UNEXP_ERROR',
2433 x_msg_count,
2434 x_msg_data,
2435 '_PVT'
2436 );
2437 WHEN OTHERS THEN
2438 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2439 (
2440 l_api_name,
2441 G_PKG_NAME,
2442 'OTHERS',
2443 x_msg_count,
2444 x_msg_data,
2445 '_PVT'
2446 );
2447 END lock_row;
2448 ---------------------------------------------
2449 -- lock_row for: OKL_PROPERTY_TAX_V --
2450 ---------------------------------------------
2451 PROCEDURE lock_row(
2452 p_api_version IN NUMBER,
2453 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2454 x_return_status OUT NOCOPY VARCHAR2,
2455 x_msg_count OUT NOCOPY NUMBER,
2456 x_msg_data OUT NOCOPY VARCHAR2,
2457 p_ptcv_rec IN ptcv_rec_type) IS
2458
2459 l_api_version CONSTANT NUMBER := 1;
2460 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2461 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2462 l_ptc_rec ptc_rec_type;
2463 l_ptct_rec ptct_rec_type;
2464 BEGIN
2465 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2466 G_PKG_NAME,
2467 p_init_msg_list,
2468 l_api_version,
2469 p_api_version,
2470 '_PVT',
2471 x_return_status);
2472 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2473 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2474 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2475 RAISE OKC_API.G_EXCEPTION_ERROR;
2476 END IF;
2477 -----------------------------------------
2478 -- Move VIEW record to "Child" records --
2479 -----------------------------------------
2480 migrate(p_ptcv_rec, l_ptc_rec);
2481 migrate(p_ptcv_rec, l_ptct_rec);
2482 ---------------------------------------------
2483 -- Call the LOCK_ROW for each child record --
2484 ---------------------------------------------
2485 lock_row(
2486 p_init_msg_list,
2487 l_return_status,
2488 x_msg_count,
2489 x_msg_data,
2490 l_ptc_rec
2491 );
2492 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2493 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2494 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2495 RAISE OKC_API.G_EXCEPTION_ERROR;
2496 END IF;
2497 lock_row(
2498 p_init_msg_list,
2499 l_return_status,
2500 x_msg_count,
2501 x_msg_data,
2502 l_ptct_rec
2503 );
2504 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2505 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2506 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2507 RAISE OKC_API.G_EXCEPTION_ERROR;
2508 END IF;
2509 x_return_status := l_return_status;
2510 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2511 EXCEPTION
2512 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2513 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2514 (
2515 l_api_name,
2516 G_PKG_NAME,
2517 'OKC_API.G_RET_STS_ERROR',
2518 x_msg_count,
2519 x_msg_data,
2520 '_PVT'
2521 );
2522 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2523 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2524 (
2525 l_api_name,
2526 G_PKG_NAME,
2527 'OKC_API.G_RET_STS_UNEXP_ERROR',
2528 x_msg_count,
2529 x_msg_data,
2530 '_PVT'
2531 );
2532 WHEN OTHERS THEN
2533 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2534 (
2535 l_api_name,
2536 G_PKG_NAME,
2537 'OTHERS',
2538 x_msg_count,
2539 x_msg_data,
2540 '_PVT'
2541 );
2542 END lock_row;
2543 --------------------------------------
2544 -- PL/SQL TBL lock_row for:PTCV_TBL --
2545 --------------------------------------
2546 PROCEDURE lock_row(
2547 p_api_version IN NUMBER,
2548 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2549 x_return_status OUT NOCOPY VARCHAR2,
2550 x_msg_count OUT NOCOPY NUMBER,
2551 x_msg_data OUT NOCOPY VARCHAR2,
2552 p_ptcv_tbl IN ptcv_tbl_type,
2553 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2554
2555 l_api_version CONSTANT NUMBER := 1;
2556 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
2557 i NUMBER := 0;
2558 BEGIN
2559 OKC_API.init_msg_list(p_init_msg_list);
2560 -- Make sure PL/SQL table has recrods in it before passing
2561 IF (p_ptcv_tbl.COUNT > 0) THEN
2562 i := p_ptcv_tbl.FIRST;
2563 LOOP
2564 DECLARE
2565 l_error_rec OKL_API.ERROR_REC_TYPE;
2566 BEGIN
2567 l_error_rec.api_name := l_api_name;
2568 l_error_rec.api_package := G_PKG_NAME;
2569 l_error_rec.idx := i;
2570 lock_row(
2571 p_api_version => p_api_version,
2572 p_init_msg_list => OKC_API.G_FALSE,
2573 x_return_status => l_error_rec.error_type,
2574 x_msg_count => l_error_rec.msg_count,
2575 x_msg_data => l_error_rec.msg_data,
2576 p_ptcv_rec => p_ptcv_tbl(i));
2577 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2578 l_error_rec.sqlcode := SQLCODE;
2579 load_error_tbl(l_error_rec, px_error_tbl);
2580 ELSE
2581 x_msg_count := l_error_rec.msg_count;
2582 x_msg_data := l_error_rec.msg_data;
2583 END IF;
2584 EXCEPTION
2585 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2586 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2587 l_error_rec.sqlcode := SQLCODE;
2588 load_error_tbl(l_error_rec, px_error_tbl);
2589 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2590 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2591 l_error_rec.sqlcode := SQLCODE;
2592 load_error_tbl(l_error_rec, px_error_tbl);
2593 WHEN OTHERS THEN
2594 l_error_rec.error_type := 'OTHERS';
2595 l_error_rec.sqlcode := SQLCODE;
2596 load_error_tbl(l_error_rec, px_error_tbl);
2597 END;
2598 EXIT WHEN (i = p_ptcv_tbl.LAST);
2599 i := p_ptcv_tbl.NEXT(i);
2600 END LOOP;
2601 END IF;
2602 -- Loop through the error_tbl to find the error with the highest severity
2603 -- and return it.
2604 x_return_status := find_highest_exception(px_error_tbl);
2605 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2606 EXCEPTION
2607 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2608 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2609 (
2610 l_api_name,
2611 G_PKG_NAME,
2612 'OKC_API.G_RET_STS_ERROR',
2613 x_msg_count,
2614 x_msg_data,
2615 '_PVT'
2616 );
2617 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2618 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2619 (
2620 l_api_name,
2621 G_PKG_NAME,
2622 'OKC_API.G_RET_STS_UNEXP_ERROR',
2623 x_msg_count,
2624 x_msg_data,
2625 '_PVT'
2626 );
2627 WHEN OTHERS THEN
2628 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2629 (
2630 l_api_name,
2631 G_PKG_NAME,
2632 'OTHERS',
2633 x_msg_count,
2634 x_msg_data,
2635 '_PVT'
2636 );
2637 END lock_row;
2638 --------------------------------------
2639 -- PL/SQL TBL lock_row for:PTCV_TBL --
2640 --------------------------------------
2641 PROCEDURE lock_row(
2642 p_api_version IN NUMBER,
2643 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2644 x_return_status OUT NOCOPY VARCHAR2,
2645 x_msg_count OUT NOCOPY NUMBER,
2646 x_msg_data OUT NOCOPY VARCHAR2,
2647 p_ptcv_tbl IN ptcv_tbl_type) IS
2648
2649 l_api_version CONSTANT NUMBER := 1;
2650 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2651 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2652 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2653 BEGIN
2654 OKC_API.init_msg_list(p_init_msg_list);
2655 -- Make sure PL/SQL table has recrods in it before passing
2656 IF (p_ptcv_tbl.COUNT > 0) THEN
2657 lock_row(
2658 p_api_version => p_api_version,
2659 p_init_msg_list => OKC_API.G_FALSE,
2660 x_return_status => x_return_status,
2661 x_msg_count => x_msg_count,
2662 x_msg_data => x_msg_data,
2663 p_ptcv_tbl => p_ptcv_tbl,
2664 px_error_tbl => l_error_tbl);
2665 END IF;
2666 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2667 EXCEPTION
2668 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2669 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2670 (
2671 l_api_name,
2672 G_PKG_NAME,
2673 'OKC_API.G_RET_STS_ERROR',
2674 x_msg_count,
2675 x_msg_data,
2676 '_PVT'
2677 );
2678 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2679 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2680 (
2681 l_api_name,
2682 G_PKG_NAME,
2683 'OKC_API.G_RET_STS_UNEXP_ERROR',
2684 x_msg_count,
2685 x_msg_data,
2686 '_PVT'
2687 );
2688 WHEN OTHERS THEN
2689 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2690 (
2691 l_api_name,
2692 G_PKG_NAME,
2693 'OTHERS',
2694 x_msg_count,
2695 x_msg_data,
2696 '_PVT'
2697 );
2698 END lock_row;
2699 ---------------------------------------------------------------------------
2700 -- PROCEDURE update_row
2701 ---------------------------------------------------------------------------
2702 ----------------------------------------------
2703 -- update_row for:OKL_PROPERTY_TAX_B --
2704 ----------------------------------------------
2705 PROCEDURE update_row(
2706 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2707 x_return_status OUT NOCOPY VARCHAR2,
2708 x_msg_count OUT NOCOPY NUMBER,
2709 x_msg_data OUT NOCOPY VARCHAR2,
2710 p_ptc_rec IN ptc_rec_type,
2711 x_ptc_rec OUT NOCOPY ptc_rec_type) IS
2712
2713 l_api_version CONSTANT NUMBER := 1;
2714 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2715 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2716 l_ptc_rec ptc_rec_type := p_ptc_rec;
2717 l_def_ptc_rec ptc_rec_type;
2718 l_row_notfound BOOLEAN := TRUE;
2719 ----------------------------------
2720 -- FUNCTION populate_new_record --
2721 ----------------------------------
2722 FUNCTION populate_new_record (
2723 p_ptc_rec IN ptc_rec_type,
2724 x_ptc_rec OUT NOCOPY ptc_rec_type
2725 ) RETURN VARCHAR2 IS
2726 l_ptc_rec ptc_rec_type;
2727 l_row_notfound BOOLEAN := TRUE;
2728 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2729 BEGIN
2730 x_ptc_rec := p_ptc_rec;
2731 -- Get current database values
2732 l_ptc_rec := get_rec(p_ptc_rec, l_return_status);
2733 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2734 IF (x_ptc_rec.id = OKC_API.G_MISS_NUM)
2735 THEN
2736 x_ptc_rec.id := l_ptc_rec.id;
2737 END IF;
2738 IF (x_ptc_rec.sequence_number = OKC_API.G_MISS_NUM)
2739 THEN
2740 x_ptc_rec.sequence_number := l_ptc_rec.sequence_number;
2741 END IF;
2742 IF (x_ptc_rec.asset_id = OKC_API.G_MISS_NUM)
2743 THEN
2744 x_ptc_rec.asset_id := l_ptc_rec.asset_id;
2745 END IF;
2746 IF (x_ptc_rec.asset_number = OKC_API.G_MISS_CHAR)
2747 THEN
2748 x_ptc_rec.asset_number := l_ptc_rec.asset_number;
2749 END IF;
2750 IF (x_ptc_rec.khr_id = OKC_API.G_MISS_NUM)
2751 THEN
2752 x_ptc_rec.khr_id := l_ptc_rec.khr_id;
2753 END IF;
2754 IF (x_ptc_rec.kle_id = OKC_API.G_MISS_NUM)
2755 THEN
2756 x_ptc_rec.kle_id := l_ptc_rec.kle_id;
2757 END IF;
2758 IF (x_ptc_rec.asset_units = OKC_API.G_MISS_NUM)
2759 THEN
2760 x_ptc_rec.asset_units := l_ptc_rec.asset_units;
2761 END IF;
2762 IF (x_ptc_rec.asset_address_1 = OKC_API.G_MISS_CHAR)
2763 THEN
2764 x_ptc_rec.asset_address_1 := l_ptc_rec.asset_address_1;
2765 END IF;
2766 IF (x_ptc_rec.asset_address_2 = OKC_API.G_MISS_CHAR)
2767 THEN
2768 x_ptc_rec.asset_address_2 := l_ptc_rec.asset_address_2;
2769 END IF;
2770 IF (x_ptc_rec.asset_address_3 = OKC_API.G_MISS_CHAR)
2771 THEN
2772 x_ptc_rec.asset_address_3 := l_ptc_rec.asset_address_3;
2773 END IF;
2774 IF (x_ptc_rec.asset_address_4 = OKC_API.G_MISS_CHAR)
2775 THEN
2776 x_ptc_rec.asset_address_4 := l_ptc_rec.asset_address_4;
2777 END IF;
2778 IF (x_ptc_rec.asset_city = OKC_API.G_MISS_CHAR)
2779 THEN
2780 x_ptc_rec.asset_city := l_ptc_rec.asset_city;
2781 END IF;
2782 IF (x_ptc_rec.asset_state = OKC_API.G_MISS_CHAR)
2783 THEN
2784 x_ptc_rec.asset_state := l_ptc_rec.asset_state;
2785 END IF;
2786 IF (x_ptc_rec.asset_country = OKC_API.G_MISS_CHAR)
2787 THEN
2788 x_ptc_rec.asset_country := l_ptc_rec.asset_country;
2789 END IF;
2790 IF (x_ptc_rec.tax_assessment_amount = OKC_API.G_MISS_NUM)
2791 THEN
2792 x_ptc_rec.tax_assessment_amount := l_ptc_rec.tax_assessment_amount;
2793 END IF;
2794 IF (x_ptc_rec.tax_jurisdiction_city = OKC_API.G_MISS_CHAR)
2795 THEN
2796 x_ptc_rec.tax_jurisdiction_city := l_ptc_rec.tax_jurisdiction_city;
2797 END IF;
2798 IF (x_ptc_rec.tax_jurisdiction_city_rate = OKC_API.G_MISS_NUM)
2799 THEN
2800 x_ptc_rec.tax_jurisdiction_city_rate := l_ptc_rec.tax_jurisdiction_city_rate;
2801 END IF;
2802 IF (x_ptc_rec.tax_jurisdiction_county = OKC_API.G_MISS_CHAR)
2803 THEN
2804 x_ptc_rec.tax_jurisdiction_county := l_ptc_rec.tax_jurisdiction_county;
2805 END IF;
2806 IF (x_ptc_rec.tax_jurisdiction_county_rate = OKC_API.G_MISS_NUM)
2807 THEN
2808 x_ptc_rec.tax_jurisdiction_county_rate := l_ptc_rec.tax_jurisdiction_county_rate;
2809 END IF;
2810 IF (x_ptc_rec.tax_jurisdiction_state = OKC_API.G_MISS_CHAR)
2811 THEN
2812 x_ptc_rec.tax_jurisdiction_state := l_ptc_rec.tax_jurisdiction_state;
2813 END IF;
2814 IF (x_ptc_rec.tax_jurisdiction_state_rate = OKC_API.G_MISS_NUM)
2815 THEN
2816 x_ptc_rec.tax_jurisdiction_state_rate := l_ptc_rec.tax_jurisdiction_state_rate;
2817 END IF;
2818 IF (x_ptc_rec.tax_jurisdiction_school = OKC_API.G_MISS_CHAR)
2819 THEN
2820 x_ptc_rec.tax_jurisdiction_school := l_ptc_rec.tax_jurisdiction_school;
2821 END IF;
2822 IF (x_ptc_rec.tax_jurisdiction_school_rate = OKC_API.G_MISS_NUM)
2823 THEN
2824 x_ptc_rec.tax_jurisdiction_school_rate := l_ptc_rec.tax_jurisdiction_school_rate;
2825 END IF;
2826 IF (x_ptc_rec.tax_jurisdiction_country = OKC_API.G_MISS_CHAR)
2827 THEN
2828 x_ptc_rec.tax_jurisdiction_country := l_ptc_rec.tax_jurisdiction_country;
2829 END IF;
2830 IF (x_ptc_rec.tax_jurisdiction_country_rate = OKC_API.G_MISS_NUM)
2831 THEN
2832 x_ptc_rec.tax_jurisdiction_country_rate := l_ptc_rec.tax_jurisdiction_country_rate;
2833 END IF;
2834 IF (x_ptc_rec.tax_assessment_date = OKC_API.G_MISS_DATE)
2835 THEN
2836 x_ptc_rec.tax_assessment_date := l_ptc_rec.tax_assessment_date;
2837 END IF;
2838 IF (x_ptc_rec.milrate = OKC_API.G_MISS_NUM)
2839 THEN
2840 x_ptc_rec.milrate := l_ptc_rec.milrate;
2841 END IF;
2842 IF (x_ptc_rec.property_tax_amount = OKC_API.G_MISS_NUM)
2843 THEN
2844 x_ptc_rec.property_tax_amount := l_ptc_rec.property_tax_amount;
2845 END IF;
2846 IF (x_ptc_rec.oec = OKC_API.G_MISS_NUM)
2847 THEN
2848 x_ptc_rec.oec := l_ptc_rec.oec;
2849 END IF;
2850 IF (x_ptc_rec.created_by = OKC_API.G_MISS_NUM)
2851 THEN
2852 x_ptc_rec.created_by := l_ptc_rec.created_by;
2853 END IF;
2854 IF (x_ptc_rec.creation_date = OKC_API.G_MISS_DATE)
2855 THEN
2856 x_ptc_rec.creation_date := l_ptc_rec.creation_date;
2857 END IF;
2858 IF (x_ptc_rec.last_updated_by = OKC_API.G_MISS_NUM)
2859 THEN
2860 x_ptc_rec.last_updated_by := l_ptc_rec.last_updated_by;
2861 END IF;
2862 IF (x_ptc_rec.last_update_date = OKC_API.G_MISS_DATE)
2863 THEN
2864 x_ptc_rec.last_update_date := l_ptc_rec.last_update_date;
2865 END IF;
2866 IF (x_ptc_rec.last_update_login = OKC_API.G_MISS_NUM)
2867 THEN
2868 x_ptc_rec.last_update_login := l_ptc_rec.last_update_login;
2869 END IF;
2870 IF (x_ptc_rec.contract_number = OKC_API.G_MISS_CHAR)
2871 THEN
2872 x_ptc_rec.contract_number := l_ptc_rec.contract_number;
2873 END IF;
2874 IF (x_ptc_rec.sty_name = OKC_API.G_MISS_CHAR)
2875 THEN
2876 x_ptc_rec.sty_name := l_ptc_rec.sty_name;
2877 END IF;
2878 IF (x_ptc_rec.sty_id = OKC_API.G_MISS_NUM)
2879 THEN
2880 x_ptc_rec.sty_id := l_ptc_rec.sty_id;
2881 END IF;
2882 IF (x_ptc_rec.invoice_date = OKC_API.G_MISS_DATE)
2883 THEN
2884 x_ptc_rec.invoice_date := l_ptc_rec.invoice_date;
2885 END IF;
2886 IF (x_ptc_rec.amount = OKC_API.G_MISS_NUM)
2887 THEN
2888 x_ptc_rec.amount := l_ptc_rec.amount;
2889 END IF;
2890 IF (x_ptc_rec.org_id = OKC_API.G_MISS_NUM)
2891 THEN
2892 x_ptc_rec.org_id := l_ptc_rec.org_id;
2893 END IF;
2894
2895 -- Addition for Est Property Tax
2896 IF (x_ptc_rec.JURSDCTN_TYPE = OKC_API.G_MISS_CHAR)
2897 THEN
2898 x_ptc_rec.JURSDCTN_TYPE := l_ptc_rec.JURSDCTN_TYPE;
2899 END IF;
2900
2901 IF (x_ptc_rec.JURSDCTN_NAME = OKC_API.G_MISS_CHAR)
2902 THEN
2903 x_ptc_rec.JURSDCTN_NAME := l_ptc_rec.JURSDCTN_NAME;
2904 END IF;
2905
2906 IF (x_ptc_rec.MLRT_TAX = OKC_API.G_MISS_NUM)
2907 THEN
2908 x_ptc_rec.MLRT_TAX := l_ptc_rec.MLRT_TAX;
2909 END IF;
2910
2911 IF (x_ptc_rec.TAX_VENDOR_ID = OKC_API.G_MISS_NUM)
2912 THEN
2913 x_ptc_rec.TAX_VENDOR_ID := l_ptc_rec.TAX_VENDOR_ID;
2914 END IF;
2915
2916 IF (x_ptc_rec.TAX_VENDOR_NAME = OKC_API.G_MISS_CHAR)
2917 THEN
2918 x_ptc_rec.TAX_VENDOR_NAME := l_ptc_rec.TAX_VENDOR_NAME;
2919 END IF;
2920
2921 IF (x_ptc_rec.TAX_VENDOR_SITE_ID = OKC_API.G_MISS_NUM)
2922 THEN
2923 x_ptc_rec.TAX_VENDOR_SITE_ID := l_ptc_rec.TAX_VENDOR_SITE_ID;
2924 END IF;
2925
2926 IF (x_ptc_rec.TAX_VENDOR_SITE_NAME = OKC_API.G_MISS_CHAR)
2927 THEN
2928 x_ptc_rec.TAX_VENDOR_SITE_NAME := l_ptc_rec.TAX_VENDOR_SITE_NAME;
2929 END IF;
2930 -- End Addition for Est Property Tax
2931
2932 END IF;
2933 RETURN(l_return_status);
2934 END populate_new_record;
2935 --------------------------------------------------
2936 -- Set_Attributes for:OKL_PROPERTY_TAX_B --
2937 --------------------------------------------------
2938 FUNCTION Set_Attributes (
2939 p_ptc_rec IN ptc_rec_type,
2940 x_ptc_rec OUT NOCOPY ptc_rec_type
2941 ) RETURN VARCHAR2 IS
2942 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2943 BEGIN
2944 x_ptc_rec := p_ptc_rec;
2945 RETURN(l_return_status);
2946 END Set_Attributes;
2947 BEGIN
2948 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2949 p_init_msg_list,
2950 '_PVT',
2951 x_return_status);
2952 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2953 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2954 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2955 RAISE OKC_API.G_EXCEPTION_ERROR;
2956 END IF;
2957 --- Setting item attributes
2958 l_return_status := Set_Attributes(
2959 p_ptc_rec, -- IN
2960 l_ptc_rec); -- OUT
2961 --- If any errors happen abort API
2962 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2963 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2964 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2965 RAISE OKC_API.G_EXCEPTION_ERROR;
2966 END IF;
2967 l_return_status := populate_new_record(l_ptc_rec, l_def_ptc_rec);
2968 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2969 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2970 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2971 RAISE OKC_API.G_EXCEPTION_ERROR;
2972 END IF;
2973 UPDATE OKL_PROPERTY_TAX_B
2974 SET SEQUENCE_NUMBER = l_def_ptc_rec.sequence_number,
2975 ASSET_ID = l_def_ptc_rec.asset_id,
2976 ASSET_NUMBER = l_def_ptc_rec.asset_number,
2977 KHR_ID = l_def_ptc_rec.khr_id,
2978 KLE_ID = l_def_ptc_rec.kle_id,
2979 ASSET_UNITS = l_def_ptc_rec.asset_units,
2980 ASSET_ADDRESS_1 = l_def_ptc_rec.asset_address_1,
2981 ASSET_ADDRESS_2 = l_def_ptc_rec.asset_address_2,
2982 ASSET_ADDRESS_3 = l_def_ptc_rec.asset_address_3,
2983 ASSET_ADDRESS_4 = l_def_ptc_rec.asset_address_4,
2984 ASSET_CITY = l_def_ptc_rec.asset_city,
2985 ASSET_STATE = l_def_ptc_rec.asset_state,
2986 ASSET_COUNTRY = l_def_ptc_rec.asset_country,
2987 TAX_ASSESSMENT_AMOUNT = l_def_ptc_rec.tax_assessment_amount,
2988 TAX_JURISDICTION_CITY = l_def_ptc_rec.tax_jurisdiction_city,
2989 TAX_JURISDICTION_CITY_RATE = l_def_ptc_rec.tax_jurisdiction_city_rate,
2990 TAX_JURISDICTION_COUNTY = l_def_ptc_rec.tax_jurisdiction_county,
2991 TAX_JURISDICTION_COUNTY_RATE = l_def_ptc_rec.tax_jurisdiction_county_rate,
2992 TAX_JURISDICTION_STATE = l_def_ptc_rec.tax_jurisdiction_state,
2993 TAX_JURISDICTION_STATE_RATE = l_def_ptc_rec.tax_jurisdiction_state_rate,
2994 TAX_JURISDICTION_SCHOOL = l_def_ptc_rec.tax_jurisdiction_school,
2995 TAX_JURISDICTION_SCHOOL_RATE = l_def_ptc_rec.tax_jurisdiction_school_rate,
2996 TAX_JURISDICTION_COUNTRY = l_def_ptc_rec.tax_jurisdiction_country,
2997 TAX_JURISDICTION_COUNTRY_RATE = l_def_ptc_rec.tax_jurisdiction_country_rate,
2998 TAX_ASSESSMENT_DATE = l_def_ptc_rec.tax_assessment_date,
2999 MILRATE = l_def_ptc_rec.milrate,
3000 PROPERTY_TAX_AMOUNT = l_def_ptc_rec.property_tax_amount,
3001 OEC = l_def_ptc_rec.oec,
3002 CREATED_BY = l_def_ptc_rec.created_by,
3003 CREATION_DATE = l_def_ptc_rec.creation_date,
3004 LAST_UPDATED_BY = l_def_ptc_rec.last_updated_by,
3005 LAST_UPDATE_DATE = l_def_ptc_rec.last_update_date,
3006 LAST_UPDATE_LOGIN = l_def_ptc_rec.last_update_login,
3007 contract_number = l_def_ptc_rec.contract_number,
3008 sty_name = l_def_ptc_rec.sty_name,
3009 sty_id = l_def_ptc_rec.sty_id,
3010 invoice_date = l_def_ptc_rec.invoice_date,
3011 amount = l_def_ptc_rec.amount,
3012 org_id = l_def_ptc_rec.org_id,
3013 -- Addition for Est Property Tax
3014 JURSDCTN_TYPE = l_def_ptc_rec.JURSDCTN_TYPE,
3015 JURSDCTN_NAME = l_def_ptc_rec.JURSDCTN_NAME,
3016 MLRT_TAX = l_def_ptc_rec.MLRT_TAX,
3017 TAX_VENDOR_ID = l_def_ptc_rec.TAX_VENDOR_ID,
3018 TAX_VENDOR_NAME = l_def_ptc_rec.TAX_VENDOR_NAME,
3019 TAX_VENDOR_SITE_ID = l_def_ptc_rec.TAX_VENDOR_SITE_ID,
3020 TAX_VENDOR_SITE_NAME = l_def_ptc_rec.TAX_VENDOR_SITE_NAME
3021 -- End Addition for Est Property Tax
3022 WHERE ID = l_def_ptc_rec.id;
3023
3024 x_ptc_rec := l_ptc_rec;
3025 x_return_status := l_return_status;
3026 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3027 EXCEPTION
3028 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3029 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3030 (
3031 l_api_name,
3032 G_PKG_NAME,
3033 'OKC_API.G_RET_STS_ERROR',
3034 x_msg_count,
3035 x_msg_data,
3036 '_PVT'
3037 );
3038 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3039 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3040 (
3041 l_api_name,
3042 G_PKG_NAME,
3043 'OKC_API.G_RET_STS_UNEXP_ERROR',
3044 x_msg_count,
3045 x_msg_data,
3046 '_PVT'
3047 );
3048 WHEN OTHERS THEN
3049 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3050 (
3051 l_api_name,
3052 G_PKG_NAME,
3053 'OTHERS',
3054 x_msg_count,
3055 x_msg_data,
3056 '_PVT'
3057 );
3058 END update_row;
3059 -----------------------------------------------
3060 -- update_row for:OKL_PROPERTY_TAX_TL --
3061 -----------------------------------------------
3062 PROCEDURE update_row(
3063 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3064 x_return_status OUT NOCOPY VARCHAR2,
3065 x_msg_count OUT NOCOPY NUMBER,
3066 x_msg_data OUT NOCOPY VARCHAR2,
3067 p_ptct_rec IN ptct_rec_type,
3068 x_ptct_rec OUT NOCOPY ptct_rec_type) IS
3069
3070 l_api_version CONSTANT NUMBER := 1;
3071 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
3072 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3073 l_ptct_rec ptct_rec_type := p_ptct_rec;
3074 l_def_ptct_rec ptct_rec_type;
3075 l_row_notfound BOOLEAN := TRUE;
3076 ----------------------------------
3077 -- FUNCTION populate_new_record --
3078 ----------------------------------
3079 FUNCTION populate_new_record (
3080 p_ptct_rec IN ptct_rec_type,
3081 x_ptct_rec OUT NOCOPY ptct_rec_type
3082 ) RETURN VARCHAR2 IS
3083 l_ptct_rec ptct_rec_type;
3084 l_row_notfound BOOLEAN := TRUE;
3085 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3086 BEGIN
3087 x_ptct_rec := p_ptct_rec;
3088 -- Get current database values
3089 l_ptct_rec := get_rec(p_ptct_rec, l_return_status);
3090 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3091 IF (x_ptct_rec.id = OKC_API.G_MISS_NUM)
3092 THEN
3093 x_ptct_rec.id := l_ptct_rec.id;
3094 END IF;
3095 IF (x_ptct_rec.language = OKC_API.G_MISS_CHAR)
3096 THEN
3097 x_ptct_rec.language := l_ptct_rec.language;
3098 END IF;
3099 IF (x_ptct_rec.source_lang = OKC_API.G_MISS_CHAR)
3100 THEN
3101 x_ptct_rec.source_lang := l_ptct_rec.source_lang;
3102 END IF;
3103 IF (x_ptct_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3104 THEN
3105 x_ptct_rec.sfwt_flag := l_ptct_rec.sfwt_flag;
3106 END IF;
3107 IF (x_ptct_rec.asset_description = OKC_API.G_MISS_CHAR)
3108 THEN
3109 x_ptct_rec.asset_description := l_ptct_rec.asset_description;
3110 END IF;
3111 IF (x_ptct_rec.created_by = OKC_API.G_MISS_NUM)
3112 THEN
3113 x_ptct_rec.created_by := l_ptct_rec.created_by;
3114 END IF;
3115 IF (x_ptct_rec.creation_date = OKC_API.G_MISS_DATE)
3116 THEN
3117 x_ptct_rec.creation_date := l_ptct_rec.creation_date;
3118 END IF;
3119 IF (x_ptct_rec.last_updated_by = OKC_API.G_MISS_NUM)
3120 THEN
3121 x_ptct_rec.last_updated_by := l_ptct_rec.last_updated_by;
3122 END IF;
3123 IF (x_ptct_rec.last_update_date = OKC_API.G_MISS_DATE)
3124 THEN
3125 x_ptct_rec.last_update_date := l_ptct_rec.last_update_date;
3126 END IF;
3127 IF (x_ptct_rec.last_update_login = OKC_API.G_MISS_NUM)
3128 THEN
3129 x_ptct_rec.last_update_login := l_ptct_rec.last_update_login;
3130 END IF;
3131 END IF;
3132 RETURN(l_return_status);
3133 END populate_new_record;
3134 ---------------------------------------------------
3135 -- Set_Attributes for:OKL_PROPERTY_TAX_TL --
3136 ---------------------------------------------------
3137 FUNCTION Set_Attributes (
3138 p_ptct_rec IN ptct_rec_type,
3139 x_ptct_rec OUT NOCOPY ptct_rec_type
3140 ) RETURN VARCHAR2 IS
3141 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3142 BEGIN
3143 x_ptct_rec := p_ptct_rec;
3144 x_ptct_rec.LANGUAGE := USERENV('LANG');
3145 x_ptct_rec.LANGUAGE := USERENV('LANG');
3146 RETURN(l_return_status);
3147 END Set_Attributes;
3148 BEGIN
3149 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3150 p_init_msg_list,
3151 '_PVT',
3152 x_return_status);
3153 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3154 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3155 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3156 RAISE OKC_API.G_EXCEPTION_ERROR;
3157 END IF;
3158 --- Setting item attributes
3159 l_return_status := Set_Attributes(
3160 p_ptct_rec, -- IN
3161 l_ptct_rec); -- OUT
3162 --- If any errors happen abort API
3163 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3164 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3165 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3166 RAISE OKC_API.G_EXCEPTION_ERROR;
3167 END IF;
3168 l_return_status := populate_new_record(l_ptct_rec, l_def_ptct_rec);
3169 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3170 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3171 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3172 RAISE OKC_API.G_EXCEPTION_ERROR;
3173 END IF;
3174 UPDATE OKL_PROPERTY_TAX_TL
3175 SET ASSET_DESCRIPTION = l_def_ptct_rec.asset_description,
3176 SOURCE_LANG = l_def_ptct_rec.source_lang,-- Fix for bug 3637102
3177 CREATED_BY = l_def_ptct_rec.created_by,
3178 CREATION_DATE = l_def_ptct_rec.creation_date,
3179 LAST_UPDATED_BY = l_def_ptct_rec.last_updated_by,
3180 LAST_UPDATE_DATE = l_def_ptct_rec.last_update_date,
3181 LAST_UPDATE_LOGIN = l_def_ptct_rec.last_update_login
3182 WHERE ID = l_def_ptct_rec.id
3183 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);--Fix for bug 3637102
3184 --AND SOURCE_LANG = USERENV('LANG');
3185
3186 UPDATE OKL_PROPERTY_TAX_TL
3187 SET SFWT_FLAG = 'Y'
3188 WHERE ID = l_def_ptct_rec.id
3189 AND SOURCE_LANG <> USERENV('LANG');
3190
3191 x_ptct_rec := l_ptct_rec;
3192 x_return_status := l_return_status;
3193 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3194 EXCEPTION
3195 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3196 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3197 (
3198 l_api_name,
3199 G_PKG_NAME,
3200 'OKC_API.G_RET_STS_ERROR',
3201 x_msg_count,
3202 x_msg_data,
3203 '_PVT'
3204 );
3205 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3206 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3207 (
3208 l_api_name,
3209 G_PKG_NAME,
3210 'OKC_API.G_RET_STS_UNEXP_ERROR',
3211 x_msg_count,
3212 x_msg_data,
3213 '_PVT'
3214 );
3215 WHEN OTHERS THEN
3216 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3217 (
3218 l_api_name,
3219 G_PKG_NAME,
3220 'OTHERS',
3221 x_msg_count,
3222 x_msg_data,
3223 '_PVT'
3224 );
3225 END update_row;
3226 ----------------------------------------------
3227 -- update_row for:OKL_PROPERTY_TAX_V --
3228 ----------------------------------------------
3229 PROCEDURE update_row(
3230 p_api_version IN NUMBER,
3231 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3232 x_return_status OUT NOCOPY VARCHAR2,
3233 x_msg_count OUT NOCOPY NUMBER,
3234 x_msg_data OUT NOCOPY VARCHAR2,
3235 p_ptcv_rec IN ptcv_rec_type,
3236 x_ptcv_rec OUT NOCOPY ptcv_rec_type) IS
3237
3238 l_api_version CONSTANT NUMBER := 1;
3239 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3240 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3241 l_ptcv_rec ptcv_rec_type := p_ptcv_rec;
3242 l_def_ptcv_rec ptcv_rec_type;
3243 l_db_ptcv_rec ptcv_rec_type;
3244 l_ptc_rec ptc_rec_type;
3245 lx_ptc_rec ptc_rec_type;
3246 l_ptct_rec ptct_rec_type;
3247 lx_ptct_rec ptct_rec_type;
3248 -------------------------------
3249 -- FUNCTION fill_who_columns --
3250 -------------------------------
3251 FUNCTION fill_who_columns (
3252 p_ptcv_rec IN ptcv_rec_type
3253 ) RETURN ptcv_rec_type IS
3254 l_ptcv_rec ptcv_rec_type := p_ptcv_rec;
3255 BEGIN
3256 l_ptcv_rec.LAST_UPDATE_DATE := SYSDATE;
3257 l_ptcv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3258 l_ptcv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3259 RETURN(l_ptcv_rec);
3260 END fill_who_columns;
3261 ----------------------------------
3262 -- FUNCTION populate_new_record --
3263 ----------------------------------
3264 FUNCTION populate_new_record (
3265 p_ptcv_rec IN ptcv_rec_type,
3266 x_ptcv_rec OUT NOCOPY ptcv_rec_type
3267 ) RETURN VARCHAR2 IS
3268 l_row_notfound BOOLEAN := TRUE;
3269 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3270 BEGIN
3271 x_ptcv_rec := p_ptcv_rec;
3272 -- Get current database values
3273 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
3274 -- so it may be verified through LOCK_ROW.
3275 l_db_ptcv_rec := get_rec(p_ptcv_rec, l_return_status);
3276 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3277 IF (x_ptcv_rec.id = OKC_API.G_MISS_NUM)
3278 THEN
3279 x_ptcv_rec.id := l_db_ptcv_rec.id;
3280 END IF;
3281 IF (x_ptcv_rec.sequence_number = OKC_API.G_MISS_NUM)
3282 THEN
3283 x_ptcv_rec.sequence_number := l_db_ptcv_rec.sequence_number;
3284 END IF;
3285 IF (x_ptcv_rec.asset_id = OKC_API.G_MISS_NUM)
3286 THEN
3287 x_ptcv_rec.asset_id := l_db_ptcv_rec.asset_id;
3288 END IF;
3289 IF (x_ptcv_rec.asset_number = OKC_API.G_MISS_CHAR)
3290 THEN
3291 x_ptcv_rec.asset_number := l_db_ptcv_rec.asset_number;
3292 END IF;
3293 IF (x_ptcv_rec.asset_description = OKC_API.G_MISS_CHAR)
3294 THEN
3295 x_ptcv_rec.asset_description := l_db_ptcv_rec.asset_description;
3296 END IF;
3297 IF (x_ptcv_rec.khr_id = OKC_API.G_MISS_NUM)
3298 THEN
3299 x_ptcv_rec.khr_id := l_db_ptcv_rec.khr_id;
3300 END IF;
3301 IF (x_ptcv_rec.kle_id = OKC_API.G_MISS_NUM)
3302 THEN
3303 x_ptcv_rec.kle_id := l_db_ptcv_rec.kle_id;
3304 END IF;
3305 IF (x_ptcv_rec.asset_units = OKC_API.G_MISS_NUM)
3306 THEN
3307 x_ptcv_rec.asset_units := l_db_ptcv_rec.asset_units;
3308 END IF;
3309 IF (x_ptcv_rec.language = OKC_API.G_MISS_CHAR)
3310 THEN
3311 x_ptcv_rec.language := l_db_ptcv_rec.language;
3312 END IF;
3313 IF (x_ptcv_rec.source_lang = OKC_API.G_MISS_CHAR)
3314 THEN
3315 x_ptcv_rec.source_lang := l_db_ptcv_rec.source_lang;
3316 END IF;
3317 IF (x_ptcv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3318 THEN
3319 x_ptcv_rec.sfwt_flag := l_db_ptcv_rec.sfwt_flag;
3320 END IF;
3321 IF (x_ptcv_rec.asset_address_1 = OKC_API.G_MISS_CHAR)
3322 THEN
3323 x_ptcv_rec.asset_address_1 := l_db_ptcv_rec.asset_address_1;
3324 END IF;
3325 IF (x_ptcv_rec.asset_address_2 = OKC_API.G_MISS_CHAR)
3326 THEN
3327 x_ptcv_rec.asset_address_2 := l_db_ptcv_rec.asset_address_2;
3328 END IF;
3329 IF (x_ptcv_rec.asset_address_3 = OKC_API.G_MISS_CHAR)
3330 THEN
3331 x_ptcv_rec.asset_address_3 := l_db_ptcv_rec.asset_address_3;
3332 END IF;
3333 IF (x_ptcv_rec.asset_address_4 = OKC_API.G_MISS_CHAR)
3334 THEN
3335 x_ptcv_rec.asset_address_4 := l_db_ptcv_rec.asset_address_4;
3336 END IF;
3337 IF (x_ptcv_rec.asset_city = OKC_API.G_MISS_CHAR)
3338 THEN
3339 x_ptcv_rec.asset_city := l_db_ptcv_rec.asset_city;
3340 END IF;
3341 IF (x_ptcv_rec.asset_state = OKC_API.G_MISS_CHAR)
3342 THEN
3343 x_ptcv_rec.asset_state := l_db_ptcv_rec.asset_state;
3344 END IF;
3345 IF (x_ptcv_rec.asset_country = OKC_API.G_MISS_CHAR)
3346 THEN
3347 x_ptcv_rec.asset_country := l_db_ptcv_rec.asset_country;
3348 END IF;
3349 IF (x_ptcv_rec.tax_assessment_amount = OKC_API.G_MISS_NUM)
3350 THEN
3351 x_ptcv_rec.tax_assessment_amount := l_db_ptcv_rec.tax_assessment_amount;
3352 END IF;
3353 IF (x_ptcv_rec.tax_jurisdiction_city = OKC_API.G_MISS_CHAR)
3354 THEN
3355 x_ptcv_rec.tax_jurisdiction_city := l_db_ptcv_rec.tax_jurisdiction_city;
3356 END IF;
3357 IF (x_ptcv_rec.tax_jurisdiction_city_rate = OKC_API.G_MISS_NUM)
3358 THEN
3359 x_ptcv_rec.tax_jurisdiction_city_rate := l_db_ptcv_rec.tax_jurisdiction_city_rate;
3360 END IF;
3361 IF (x_ptcv_rec.tax_jurisdiction_county = OKC_API.G_MISS_CHAR)
3362 THEN
3363 x_ptcv_rec.tax_jurisdiction_county := l_db_ptcv_rec.tax_jurisdiction_county;
3364 END IF;
3365 IF (x_ptcv_rec.tax_jurisdiction_county_rate = OKC_API.G_MISS_NUM)
3366 THEN
3367 x_ptcv_rec.tax_jurisdiction_county_rate := l_db_ptcv_rec.tax_jurisdiction_county_rate;
3368 END IF;
3369 IF (x_ptcv_rec.tax_jurisdiction_state = OKC_API.G_MISS_CHAR)
3370 THEN
3371 x_ptcv_rec.tax_jurisdiction_state := l_db_ptcv_rec.tax_jurisdiction_state;
3372 END IF;
3373 IF (x_ptcv_rec.tax_jurisdiction_state_rate = OKC_API.G_MISS_NUM)
3374 THEN
3375 x_ptcv_rec.tax_jurisdiction_state_rate := l_db_ptcv_rec.tax_jurisdiction_state_rate;
3376 END IF;
3377 IF (x_ptcv_rec.tax_jurisdiction_school = OKC_API.G_MISS_CHAR)
3378 THEN
3379 x_ptcv_rec.tax_jurisdiction_school := l_db_ptcv_rec.tax_jurisdiction_school;
3380 END IF;
3381 IF (x_ptcv_rec.tax_jurisdiction_school_rate = OKC_API.G_MISS_NUM)
3382 THEN
3383 x_ptcv_rec.tax_jurisdiction_school_rate := l_db_ptcv_rec.tax_jurisdiction_school_rate;
3384 END IF;
3385 IF (x_ptcv_rec.tax_jurisdiction_country = OKC_API.G_MISS_CHAR)
3386 THEN
3387 x_ptcv_rec.tax_jurisdiction_country := l_db_ptcv_rec.tax_jurisdiction_country;
3388 END IF;
3389 IF (x_ptcv_rec.tax_jurisdiction_country_rate = OKC_API.G_MISS_NUM)
3390 THEN
3391 x_ptcv_rec.tax_jurisdiction_country_rate := l_db_ptcv_rec.tax_jurisdiction_country_rate;
3392 END IF;
3393 IF (x_ptcv_rec.tax_assessment_date = OKC_API.G_MISS_DATE)
3394 THEN
3395 x_ptcv_rec.tax_assessment_date := l_db_ptcv_rec.tax_assessment_date;
3396 END IF;
3397 IF (x_ptcv_rec.milrate = OKC_API.G_MISS_NUM)
3398 THEN
3399 x_ptcv_rec.milrate := l_db_ptcv_rec.milrate;
3400 END IF;
3401 IF (x_ptcv_rec.property_tax_amount = OKC_API.G_MISS_NUM)
3402 THEN
3403 x_ptcv_rec.property_tax_amount := l_db_ptcv_rec.property_tax_amount;
3404 END IF;
3405 IF (x_ptcv_rec.oec = OKC_API.G_MISS_NUM)
3406 THEN
3407 x_ptcv_rec.oec := l_db_ptcv_rec.oec;
3408 END IF;
3409 IF (x_ptcv_rec.created_by = OKC_API.G_MISS_NUM)
3410 THEN
3411 x_ptcv_rec.created_by := l_db_ptcv_rec.created_by;
3412 END IF;
3413 IF (x_ptcv_rec.creation_date = OKC_API.G_MISS_DATE)
3414 THEN
3415 x_ptcv_rec.creation_date := l_db_ptcv_rec.creation_date;
3416 END IF;
3417 IF (x_ptcv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3418 THEN
3419 x_ptcv_rec.last_updated_by := l_db_ptcv_rec.last_updated_by;
3420 END IF;
3421 IF (x_ptcv_rec.last_update_date = OKC_API.G_MISS_DATE)
3422 THEN
3423 x_ptcv_rec.last_update_date := l_db_ptcv_rec.last_update_date;
3424 END IF;
3425 IF (x_ptcv_rec.last_update_login = OKC_API.G_MISS_NUM)
3426 THEN
3427 x_ptcv_rec.last_update_login := l_db_ptcv_rec.last_update_login;
3428 END IF;
3429 IF (x_ptcv_rec.contract_number = OKC_API.G_MISS_CHAR)
3430 THEN
3431 x_ptcv_rec.contract_number := l_db_ptcv_rec.contract_number;
3432 END IF;
3433 IF (x_ptcv_rec.sty_name = OKC_API.G_MISS_CHAR)
3434 THEN
3435 x_ptcv_rec.sty_name := l_db_ptcv_rec.sty_name;
3436 END IF;
3437 IF (x_ptcv_rec.sty_id = OKC_API.G_MISS_NUM)
3438 THEN
3439 x_ptcv_rec.sty_id := l_db_ptcv_rec.sty_id;
3440 END IF;
3441 IF (x_ptcv_rec.invoice_date = OKC_API.G_MISS_DATE)
3442 THEN
3443 x_ptcv_rec.invoice_date := l_db_ptcv_rec.invoice_date;
3444 END IF;
3445 IF (x_ptcv_rec.amount = OKC_API.G_MISS_NUM)
3446 THEN
3447 x_ptcv_rec.amount := l_db_ptcv_rec.amount;
3448 END IF;
3449 IF (x_ptcv_rec.org_id = OKC_API.G_MISS_NUM)
3450 THEN
3451 x_ptcv_rec.org_id := l_db_ptcv_rec.org_id;
3452 END IF;
3453
3454 -- Addition for Est property Tax
3455 IF (x_ptcv_rec.JURSDCTN_TYPE = OKC_API.G_MISS_CHAR)
3456 THEN
3457 x_ptcv_rec.JURSDCTN_TYPE := l_db_ptcv_rec.JURSDCTN_TYPE;
3458 END IF;
3459
3460 IF (x_ptcv_rec.JURSDCTN_NAME = OKC_API.G_MISS_CHAR)
3461 THEN
3462 x_ptcv_rec.JURSDCTN_NAME := l_db_ptcv_rec.JURSDCTN_NAME;
3463 END IF;
3464
3465 IF (x_ptcv_rec.MLRT_TAX = OKC_API.G_MISS_NUM)
3466 THEN
3467 x_ptcv_rec.MLRT_TAX := l_db_ptcv_rec.MLRT_TAX;
3468 END IF;
3469
3470 IF (x_ptcv_rec.TAX_VENDOR_ID = OKC_API.G_MISS_NUM)
3471 THEN
3472 x_ptcv_rec.TAX_VENDOR_ID := l_db_ptcv_rec.TAX_VENDOR_ID;
3473 END IF;
3474
3475 IF (x_ptcv_rec.TAX_VENDOR_NAME = OKC_API.G_MISS_CHAR)
3476 THEN
3477 x_ptcv_rec.TAX_VENDOR_NAME := l_db_ptcv_rec.TAX_VENDOR_NAME;
3478 END IF;
3479
3480 IF (x_ptcv_rec.TAX_VENDOR_SITE_ID = OKC_API.G_MISS_NUM)
3481 THEN
3482 x_ptcv_rec.TAX_VENDOR_SITE_ID := l_db_ptcv_rec.TAX_VENDOR_SITE_ID;
3483 END IF;
3484
3485 IF (x_ptcv_rec.TAX_VENDOR_SITE_NAME = OKC_API.G_MISS_CHAR)
3486 THEN
3487 x_ptcv_rec.TAX_VENDOR_SITE_NAME := l_db_ptcv_rec.TAX_VENDOR_SITE_NAME;
3488 END IF;
3489 -- End addition for Est Property Tax
3490
3491 END IF;
3492 RETURN(l_return_status);
3493 END populate_new_record;
3494 --------------------------------------------------
3495 -- Set_Attributes for:OKL_PROPERTY_TAX_V --
3496 --------------------------------------------------
3497 FUNCTION Set_Attributes (
3498 p_ptcv_rec IN ptcv_rec_type,
3499 x_ptcv_rec OUT NOCOPY ptcv_rec_type
3500 ) RETURN VARCHAR2 IS
3501 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3502 BEGIN
3503 x_ptcv_rec := p_ptcv_rec;
3504 RETURN(l_return_status);
3505 END Set_Attributes;
3506 BEGIN
3507 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3508 G_PKG_NAME,
3509 p_init_msg_list,
3510 l_api_version,
3511 p_api_version,
3512 '_PVT',
3513 x_return_status);
3514 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3515 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3516 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3517 RAISE OKC_API.G_EXCEPTION_ERROR;
3518 END IF;
3519 --- Setting item attributes
3520 l_return_status := Set_Attributes(
3521 p_ptcv_rec, -- IN
3522 x_ptcv_rec); -- OUT
3523 --- If any errors happen abort API
3524 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3525 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3526 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3527 RAISE OKC_API.G_EXCEPTION_ERROR;
3528 END IF;
3529 l_return_status := populate_new_record(l_ptcv_rec, l_def_ptcv_rec);
3530 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3531 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3532 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3533 RAISE OKC_API.G_EXCEPTION_ERROR;
3534 END IF;
3535 l_def_ptcv_rec := fill_who_columns(l_def_ptcv_rec);
3536 --- Validate all non-missing attributes (Item Level Validation)
3537 l_return_status := Validate_Attributes(l_def_ptcv_rec);
3538 --- If any errors happen abort API
3539 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3540 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3541 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3542 RAISE OKC_API.G_EXCEPTION_ERROR;
3543 END IF;
3544 l_return_status := Validate_Record(l_def_ptcv_rec, l_db_ptcv_rec);
3545 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3546 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3547 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3548 RAISE OKC_API.G_EXCEPTION_ERROR;
3549 END IF;
3550
3551 -- Lock the Record
3552 lock_row(
3553 p_api_version => p_api_version,
3554 p_init_msg_list => p_init_msg_list,
3555 x_return_status => l_return_status,
3556 x_msg_count => x_msg_count,
3557 x_msg_data => x_msg_data,
3558 p_ptcv_rec => p_ptcv_rec);
3559 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3560 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3561 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3562 RAISE OKC_API.G_EXCEPTION_ERROR;
3563 END IF;
3564
3565 -----------------------------------------
3566 -- Move VIEW record to "Child" records --
3567 -----------------------------------------
3568 migrate(l_def_ptcv_rec, l_ptc_rec);
3569 migrate(l_def_ptcv_rec, l_ptct_rec);
3570 -----------------------------------------------
3571 -- Call the UPDATE_ROW for each child record --
3572 -----------------------------------------------
3573 update_row(
3574 p_init_msg_list,
3575 l_return_status,
3576 x_msg_count,
3577 x_msg_data,
3578 l_ptc_rec,
3579 lx_ptc_rec
3580 );
3581 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3582 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3583 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3584 RAISE OKC_API.G_EXCEPTION_ERROR;
3585 END IF;
3586 migrate(lx_ptc_rec, l_def_ptcv_rec);
3587 update_row(
3588 p_init_msg_list,
3589 l_return_status,
3590 x_msg_count,
3591 x_msg_data,
3592 l_ptct_rec,
3593 lx_ptct_rec
3594 );
3595 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3596 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3597 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3598 RAISE OKC_API.G_EXCEPTION_ERROR;
3599 END IF;
3600 migrate(lx_ptct_rec, l_def_ptcv_rec);
3601 x_ptcv_rec := l_def_ptcv_rec;
3602 x_return_status := l_return_status;
3603 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3604 EXCEPTION
3605 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3606 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3607 (
3608 l_api_name,
3609 G_PKG_NAME,
3610 'OKC_API.G_RET_STS_ERROR',
3611 x_msg_count,
3612 x_msg_data,
3613 '_PVT'
3614 );
3615 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3616 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3617 (
3618 l_api_name,
3619 G_PKG_NAME,
3620 'OKC_API.G_RET_STS_UNEXP_ERROR',
3621 x_msg_count,
3622 x_msg_data,
3623 '_PVT'
3624 );
3625 WHEN OTHERS THEN
3626 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3627 (
3628 l_api_name,
3629 G_PKG_NAME,
3630 'OTHERS',
3631 x_msg_count,
3632 x_msg_data,
3633 '_PVT'
3634 );
3635 END update_row;
3636 ----------------------------------------
3637 -- PL/SQL TBL update_row for:ptcv_tbl --
3638 ----------------------------------------
3639 PROCEDURE update_row(
3640 p_api_version IN NUMBER,
3641 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3642 x_return_status OUT NOCOPY VARCHAR2,
3643 x_msg_count OUT NOCOPY NUMBER,
3644 x_msg_data OUT NOCOPY VARCHAR2,
3645 p_ptcv_tbl IN ptcv_tbl_type,
3646 x_ptcv_tbl OUT NOCOPY ptcv_tbl_type,
3647 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3648
3649 l_api_version CONSTANT NUMBER := 1;
3650 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
3651 i NUMBER := 0;
3652 BEGIN
3653 OKC_API.init_msg_list(p_init_msg_list);
3654 -- Make sure PL/SQL table has records in it before passing
3655 IF (p_ptcv_tbl.COUNT > 0) THEN
3656 i := p_ptcv_tbl.FIRST;
3657 LOOP
3658 DECLARE
3659 l_error_rec OKL_API.ERROR_REC_TYPE;
3660 BEGIN
3661 l_error_rec.api_name := l_api_name;
3662 l_error_rec.api_package := G_PKG_NAME;
3663 l_error_rec.idx := i;
3664 update_row (
3665 p_api_version => p_api_version,
3666 p_init_msg_list => OKC_API.G_FALSE,
3667 x_return_status => l_error_rec.error_type,
3668 x_msg_count => l_error_rec.msg_count,
3669 x_msg_data => l_error_rec.msg_data,
3670 p_ptcv_rec => p_ptcv_tbl(i),
3671 x_ptcv_rec => x_ptcv_tbl(i));
3672 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
3673 l_error_rec.sqlcode := SQLCODE;
3674 load_error_tbl(l_error_rec, px_error_tbl);
3675 ELSE
3676 x_msg_count := l_error_rec.msg_count;
3677 x_msg_data := l_error_rec.msg_data;
3678 END IF;
3679 EXCEPTION
3680 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3681 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
3682 l_error_rec.sqlcode := SQLCODE;
3683 load_error_tbl(l_error_rec, px_error_tbl);
3684 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3685 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
3686 l_error_rec.sqlcode := SQLCODE;
3687 load_error_tbl(l_error_rec, px_error_tbl);
3688 WHEN OTHERS THEN
3689 l_error_rec.error_type := 'OTHERS';
3690 l_error_rec.sqlcode := SQLCODE;
3691 load_error_tbl(l_error_rec, px_error_tbl);
3692 END;
3693 EXIT WHEN (i = p_ptcv_tbl.LAST);
3694 i := p_ptcv_tbl.NEXT(i);
3695 END LOOP;
3696 END IF;
3697 -- Loop through the error_tbl to find the error with the highest severity
3698 -- and return it.
3699 x_return_status := find_highest_exception(px_error_tbl);
3700 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3701 EXCEPTION
3702 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3703 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3704 (
3705 l_api_name,
3706 G_PKG_NAME,
3707 'OKC_API.G_RET_STS_ERROR',
3708 x_msg_count,
3709 x_msg_data,
3710 '_PVT'
3711 );
3712 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3713 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3714 (
3715 l_api_name,
3716 G_PKG_NAME,
3717 'OKC_API.G_RET_STS_UNEXP_ERROR',
3718 x_msg_count,
3719 x_msg_data,
3720 '_PVT'
3721 );
3722 WHEN OTHERS THEN
3723 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3724 (
3725 l_api_name,
3726 G_PKG_NAME,
3727 'OTHERS',
3728 x_msg_count,
3729 x_msg_data,
3730 '_PVT'
3731 );
3732 END update_row;
3733
3734 ----------------------------------------
3735 -- PL/SQL TBL update_row for:PTCV_TBL --
3736 ----------------------------------------
3737 PROCEDURE update_row(
3738 p_api_version IN NUMBER,
3739 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3740 x_return_status OUT NOCOPY VARCHAR2,
3741 x_msg_count OUT NOCOPY NUMBER,
3742 x_msg_data OUT NOCOPY VARCHAR2,
3743 p_ptcv_tbl IN ptcv_tbl_type,
3744 x_ptcv_tbl OUT NOCOPY ptcv_tbl_type) IS
3745
3746 l_api_version CONSTANT NUMBER := 1;
3747 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3748 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3749 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3750 BEGIN
3751 OKC_API.init_msg_list(p_init_msg_list);
3752 -- Make sure PL/SQL table has records in it before passing
3753 IF (p_ptcv_tbl.COUNT > 0) THEN
3754 update_row (
3755 p_api_version => p_api_version,
3756 p_init_msg_list => OKC_API.G_FALSE,
3757 x_return_status => x_return_status,
3758 x_msg_count => x_msg_count,
3759 x_msg_data => x_msg_data,
3760 p_ptcv_tbl => p_ptcv_tbl,
3761 x_ptcv_tbl => x_ptcv_tbl,
3762 px_error_tbl => l_error_tbl);
3763 END IF;
3764 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3765 EXCEPTION
3766 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3767 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3768 (
3769 l_api_name,
3770 G_PKG_NAME,
3771 'OKC_API.G_RET_STS_ERROR',
3772 x_msg_count,
3773 x_msg_data,
3774 '_PVT'
3775 );
3776 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3777 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3778 (
3779 l_api_name,
3780 G_PKG_NAME,
3781 'OKC_API.G_RET_STS_UNEXP_ERROR',
3782 x_msg_count,
3783 x_msg_data,
3784 '_PVT'
3785 );
3786 WHEN OTHERS THEN
3787 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3788 (
3789 l_api_name,
3790 G_PKG_NAME,
3791 'OTHERS',
3792 x_msg_count,
3793 x_msg_data,
3794 '_PVT'
3795 );
3796 END update_row;
3797
3798 ---------------------------------------------------------------------------
3799 -- PROCEDURE delete_row
3800 ---------------------------------------------------------------------------
3801 ----------------------------------------------
3802 -- delete_row for:OKL_PROPERTY_TAX_B --
3803 ----------------------------------------------
3804 PROCEDURE delete_row(
3805 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3806 x_return_status OUT NOCOPY VARCHAR2,
3807 x_msg_count OUT NOCOPY NUMBER,
3808 x_msg_data OUT NOCOPY VARCHAR2,
3809 p_ptc_rec IN ptc_rec_type) IS
3810
3811 l_api_version CONSTANT NUMBER := 1;
3812 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3813 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3814 l_ptc_rec ptc_rec_type := p_ptc_rec;
3815 l_row_notfound BOOLEAN := TRUE;
3816 BEGIN
3817 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3818 p_init_msg_list,
3819 '_PVT',
3820 x_return_status);
3821 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3822 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3823 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3824 RAISE OKC_API.G_EXCEPTION_ERROR;
3825 END IF;
3826
3827 DELETE FROM OKL_PROPERTY_TAX_B
3828 WHERE ID = p_ptc_rec.id;
3829
3830 x_return_status := l_return_status;
3831 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3832 EXCEPTION
3833 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3834 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3835 (
3836 l_api_name,
3837 G_PKG_NAME,
3838 'OKC_API.G_RET_STS_ERROR',
3839 x_msg_count,
3840 x_msg_data,
3841 '_PVT'
3842 );
3843 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3844 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3845 (
3846 l_api_name,
3847 G_PKG_NAME,
3848 'OKC_API.G_RET_STS_UNEXP_ERROR',
3849 x_msg_count,
3850 x_msg_data,
3851 '_PVT'
3852 );
3853 WHEN OTHERS THEN
3854 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3855 (
3856 l_api_name,
3857 G_PKG_NAME,
3858 'OTHERS',
3859 x_msg_count,
3860 x_msg_data,
3861 '_PVT'
3862 );
3863 END delete_row;
3864 -----------------------------------------------
3865 -- delete_row for:OKL_PROPERTY_TAX_TL --
3866 -----------------------------------------------
3867 PROCEDURE delete_row(
3868 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3869 x_return_status OUT NOCOPY VARCHAR2,
3870 x_msg_count OUT NOCOPY NUMBER,
3871 x_msg_data OUT NOCOPY VARCHAR2,
3872 p_ptct_rec IN ptct_rec_type) IS
3873
3874 l_api_version CONSTANT NUMBER := 1;
3875 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3876 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3877 l_ptct_rec ptct_rec_type := p_ptct_rec;
3878 l_row_notfound BOOLEAN := TRUE;
3879 BEGIN
3880 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3881 p_init_msg_list,
3882 '_PVT',
3883 x_return_status);
3884 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3885 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3886 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3887 RAISE OKC_API.G_EXCEPTION_ERROR;
3888 END IF;
3889
3890 DELETE FROM OKL_PROPERTY_TAX_TL
3891 WHERE ID = p_ptct_rec.id;
3892
3893 x_return_status := l_return_status;
3894 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3895 EXCEPTION
3896 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3897 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3898 (
3899 l_api_name,
3900 G_PKG_NAME,
3901 'OKC_API.G_RET_STS_ERROR',
3902 x_msg_count,
3903 x_msg_data,
3904 '_PVT'
3905 );
3906 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3907 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3908 (
3909 l_api_name,
3910 G_PKG_NAME,
3911 'OKC_API.G_RET_STS_UNEXP_ERROR',
3912 x_msg_count,
3913 x_msg_data,
3914 '_PVT'
3915 );
3916 WHEN OTHERS THEN
3917 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3918 (
3919 l_api_name,
3920 G_PKG_NAME,
3921 'OTHERS',
3922 x_msg_count,
3923 x_msg_data,
3924 '_PVT'
3925 );
3926 END delete_row;
3927 ----------------------------------------------
3928 -- delete_row for:OKL_PROPERTY_TAX_V --
3929 ----------------------------------------------
3930 PROCEDURE delete_row(
3931 p_api_version IN NUMBER,
3932 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3933 x_return_status OUT NOCOPY VARCHAR2,
3934 x_msg_count OUT NOCOPY NUMBER,
3935 x_msg_data OUT NOCOPY VARCHAR2,
3936 p_ptcv_rec IN ptcv_rec_type) IS
3937
3938 l_api_version CONSTANT NUMBER := 1;
3939 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3940 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3941 l_ptcv_rec ptcv_rec_type := p_ptcv_rec;
3942 l_ptct_rec ptct_rec_type;
3943 l_ptc_rec ptc_rec_type;
3944 BEGIN
3945 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3946 G_PKG_NAME,
3947 p_init_msg_list,
3948 l_api_version,
3949 p_api_version,
3950 '_PVT',
3951 x_return_status);
3952 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3953 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3954 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3955 RAISE OKC_API.G_EXCEPTION_ERROR;
3956 END IF;
3957 -----------------------------------------
3958 -- Move VIEW record to "Child" records --
3959 -----------------------------------------
3960 migrate(l_ptcv_rec, l_ptct_rec);
3961 migrate(l_ptcv_rec, l_ptc_rec);
3962 -----------------------------------------------
3963 -- Call the DELETE_ROW for each child record --
3964 -----------------------------------------------
3965 delete_row(
3966 p_init_msg_list,
3967 l_return_status,
3968 x_msg_count,
3969 x_msg_data,
3970 l_ptct_rec
3971 );
3972 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3973 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3974 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3975 RAISE OKC_API.G_EXCEPTION_ERROR;
3976 END IF;
3977 delete_row(
3978 p_init_msg_list,
3979 l_return_status,
3980 x_msg_count,
3981 x_msg_data,
3982 l_ptc_rec
3983 );
3984 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3985 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3986 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3987 RAISE OKC_API.G_EXCEPTION_ERROR;
3988 END IF;
3989 x_return_status := l_return_status;
3990 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3991 EXCEPTION
3992 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3993 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3994 (
3995 l_api_name,
3996 G_PKG_NAME,
3997 'OKC_API.G_RET_STS_ERROR',
3998 x_msg_count,
3999 x_msg_data,
4000 '_PVT'
4001 );
4002 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4003 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4004 (
4005 l_api_name,
4006 G_PKG_NAME,
4007 'OKC_API.G_RET_STS_UNEXP_ERROR',
4008 x_msg_count,
4009 x_msg_data,
4010 '_PVT'
4011 );
4012 WHEN OTHERS THEN
4013 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4014 (
4015 l_api_name,
4016 G_PKG_NAME,
4017 'OTHERS',
4018 x_msg_count,
4019 x_msg_data,
4020 '_PVT'
4021 );
4022 END delete_row;
4023 ---------------------------------------------------------
4024 -- PL/SQL TBL delete_row for:OKL_PROPERTY_TAX_V --
4025 ---------------------------------------------------------
4026 PROCEDURE delete_row(
4027 p_api_version IN NUMBER,
4028 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4029 x_return_status OUT NOCOPY VARCHAR2,
4030 x_msg_count OUT NOCOPY NUMBER,
4031 x_msg_data OUT NOCOPY VARCHAR2,
4032 p_ptcv_tbl IN ptcv_tbl_type,
4033 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
4034
4035 l_api_version CONSTANT NUMBER := 1;
4036 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
4037 i NUMBER := 0;
4038 BEGIN
4039 OKC_API.init_msg_list(p_init_msg_list);
4040 -- Make sure PL/SQL table has records in it before passing
4041 IF (p_ptcv_tbl.COUNT > 0) THEN
4042 i := p_ptcv_tbl.FIRST;
4043 LOOP
4044 DECLARE
4045 l_error_rec OKL_API.ERROR_REC_TYPE;
4046 BEGIN
4047 l_error_rec.api_name := l_api_name;
4048 l_error_rec.api_package := G_PKG_NAME;
4049 l_error_rec.idx := i;
4050 delete_row (
4051 p_api_version => p_api_version,
4052 p_init_msg_list => OKC_API.G_FALSE,
4053 x_return_status => l_error_rec.error_type,
4054 x_msg_count => l_error_rec.msg_count,
4055 x_msg_data => l_error_rec.msg_data,
4056 p_ptcv_rec => p_ptcv_tbl(i));
4057 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
4058 l_error_rec.sqlcode := SQLCODE;
4059 load_error_tbl(l_error_rec, px_error_tbl);
4060 ELSE
4061 x_msg_count := l_error_rec.msg_count;
4062 x_msg_data := l_error_rec.msg_data;
4063 END IF;
4064 EXCEPTION
4065 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4066 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
4067 l_error_rec.sqlcode := SQLCODE;
4068 load_error_tbl(l_error_rec, px_error_tbl);
4069 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4070 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
4071 l_error_rec.sqlcode := SQLCODE;
4072 load_error_tbl(l_error_rec, px_error_tbl);
4073 WHEN OTHERS THEN
4074 l_error_rec.error_type := 'OTHERS';
4075 l_error_rec.sqlcode := SQLCODE;
4076 load_error_tbl(l_error_rec, px_error_tbl);
4077 END;
4078 EXIT WHEN (i = p_ptcv_tbl.LAST);
4079 i := p_ptcv_tbl.NEXT(i);
4080 END LOOP;
4081 END IF;
4082 -- Loop through the error_tbl to find the error with the highest severity
4083 -- and return it.
4084 x_return_status := find_highest_exception(px_error_tbl);
4085 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4086 EXCEPTION
4087 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4088 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4089 (
4090 l_api_name,
4091 G_PKG_NAME,
4092 'OKC_API.G_RET_STS_ERROR',
4093 x_msg_count,
4094 x_msg_data,
4095 '_PVT'
4096 );
4097 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4098 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4099 (
4100 l_api_name,
4101 G_PKG_NAME,
4102 'OKC_API.G_RET_STS_UNEXP_ERROR',
4103 x_msg_count,
4104 x_msg_data,
4105 '_PVT'
4106 );
4107 WHEN OTHERS THEN
4108 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4109 (
4110 l_api_name,
4111 G_PKG_NAME,
4112 'OTHERS',
4113 x_msg_count,
4114 x_msg_data,
4115 '_PVT'
4116 );
4117 END delete_row;
4118
4119 ---------------------------------------------------------
4120 -- PL/SQL TBL delete_row for:OKL_PROPERTY_TAX_V --
4121 ---------------------------------------------------------
4122 PROCEDURE delete_row(
4123 p_api_version IN NUMBER,
4124 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4125 x_return_status OUT NOCOPY VARCHAR2,
4126 x_msg_count OUT NOCOPY NUMBER,
4127 x_msg_data OUT NOCOPY VARCHAR2,
4128 p_ptcv_tbl IN ptcv_tbl_type) IS
4129
4130 l_api_version CONSTANT NUMBER := 1;
4131 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
4132 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4133 l_error_tbl OKL_API.ERROR_TBL_TYPE;
4134 BEGIN
4135 OKC_API.init_msg_list(p_init_msg_list);
4136 -- Make sure PL/SQL table has records in it before passing
4137 IF (p_ptcv_tbl.COUNT > 0) THEN
4138 delete_row (
4139 p_api_version => p_api_version,
4140 p_init_msg_list => OKC_API.G_FALSE,
4141 x_return_status => x_return_status,
4142 x_msg_count => x_msg_count,
4143 x_msg_data => x_msg_data,
4144 p_ptcv_tbl => p_ptcv_tbl,
4145 px_error_tbl => l_error_tbl);
4146 END IF;
4147 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4148 EXCEPTION
4149 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4150 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4151 (
4152 l_api_name,
4153 G_PKG_NAME,
4154 'OKC_API.G_RET_STS_ERROR',
4155 x_msg_count,
4156 x_msg_data,
4157 '_PVT'
4158 );
4159 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4160 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4161 (
4162 l_api_name,
4163 G_PKG_NAME,
4164 'OKC_API.G_RET_STS_UNEXP_ERROR',
4165 x_msg_count,
4166 x_msg_data,
4167 '_PVT'
4168 );
4169 WHEN OTHERS THEN
4170 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4171 (
4172 l_api_name,
4173 G_PKG_NAME,
4174 'OTHERS',
4175 x_msg_count,
4176 x_msg_data,
4177 '_PVT'
4178 );
4179 END delete_row;
4180
4181 END OKL_PTC_PVT;