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