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