[Home] [Help]
PACKAGE BODY: APPS.OKC_LSS_PVT
Source
1 PACKAGE BODY OKC_LSS_PVT AS
2 /* $Header: OKCSLSSB.pls 120.0 2005/05/25 19:31:33 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 G_TOO_MANY_SOURCES CONSTANT VARCHAR2(20):='OKC_TOO_MANY_SOURCES';
7 ---------------------------------------------------------------------------
8 -- FUNCTION get_seq_id
9 ---------------------------------------------------------------------------
10 FUNCTION get_seq_id RETURN NUMBER IS
11 BEGIN
12 RETURN(okc_p_util.raw_to_number(sys_guid()));
13 END get_seq_id;
14
15 ---------------------------------------------------------------------------
16 -- PROCEDURE qc
17 ---------------------------------------------------------------------------
18 PROCEDURE qc IS
19 BEGIN
20 null;
21 END qc;
22
23 ---------------------------------------------------------------------------
24 -- PROCEDURE change_version
25 ---------------------------------------------------------------------------
26 PROCEDURE change_version IS
27 BEGIN
28 null;
29 END change_version;
30
31 ---------------------------------------------------------------------------
32 -- PROCEDURE api_copy
33 ---------------------------------------------------------------------------
34 PROCEDURE api_copy IS
35 BEGIN
36 null;
37 END api_copy;
38
39 ---------------------------------------------------------------------------
40 -- FUNCTION get_rec for: OKC_LINE_STYLE_SOURCES
41 ---------------------------------------------------------------------------
42 FUNCTION get_rec (
43 p_lss_rec IN lss_rec_type,
44 x_no_data_found OUT NOCOPY BOOLEAN
45 ) RETURN lss_rec_type IS
46 CURSOR lss_pk_csr (p_lse_id IN NUMBER,
47 p_jtot_object_code IN VARCHAR2) IS
48 SELECT
49 LSE_ID,
50 JTOT_OBJECT_CODE,
51 START_DATE,
52 OBJECT_VERSION_NUMBER,
53 ACCESS_LEVEL,
54 CREATED_BY,
55 CREATION_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_DATE,
58 END_DATE,
59 LAST_UPDATE_LOGIN
60 FROM Okc_Line_Style_Sources
61 WHERE okc_line_style_sources.lse_id = p_lse_id
62 AND okc_line_style_sources.jtot_object_code = p_jtot_object_code;
63 l_lss_pk lss_pk_csr%ROWTYPE;
64 l_lss_rec lss_rec_type;
65 BEGIN
66 x_no_data_found := TRUE;
67 -- Get current database values
68 OPEN lss_pk_csr (p_lss_rec.lse_id,
69 p_lss_rec.jtot_object_code);
70 FETCH lss_pk_csr INTO
71 l_lss_rec.LSE_ID,
72 l_lss_rec.JTOT_OBJECT_CODE,
73 l_lss_rec.START_DATE,
74 l_lss_rec.OBJECT_VERSION_NUMBER,
75 l_lss_rec.ACCESS_LEVEL,
76 l_lss_rec.CREATED_BY,
77 l_lss_rec.CREATION_DATE,
78 l_lss_rec.LAST_UPDATED_BY,
79 l_lss_rec.LAST_UPDATE_DATE,
80 l_lss_rec.END_DATE,
81 l_lss_rec.LAST_UPDATE_LOGIN;
82 x_no_data_found := lss_pk_csr%NOTFOUND;
83 CLOSE lss_pk_csr;
84 RETURN(l_lss_rec);
85 END get_rec;
86
87 FUNCTION get_rec (
88 p_lss_rec IN lss_rec_type
89 ) RETURN lss_rec_type IS
90 l_row_notfound BOOLEAN := TRUE;
91 BEGIN
92 RETURN(get_rec(p_lss_rec, l_row_notfound));
93 END get_rec;
94 ---------------------------------------------------------------------------
95 -- FUNCTION get_rec for: OKC_LINE_STYLE_SOURCES_V
96 ---------------------------------------------------------------------------
97 FUNCTION get_rec (
98 p_lssv_rec IN lssv_rec_type,
99 x_no_data_found OUT NOCOPY BOOLEAN
100 ) RETURN lssv_rec_type IS
101 CURSOR okc_lssv_pk_csr (p_lse_id IN NUMBER,
102 p_jtot_object_code IN VARCHAR2) IS
103 SELECT
104 LSE_ID,
105 JTOT_OBJECT_CODE,
106 OBJECT_VERSION_NUMBER,
107 ACCESS_LEVEL,
108 START_DATE,
109 END_DATE,
110 CREATED_BY,
111 CREATION_DATE,
112 LAST_UPDATED_BY,
113 LAST_UPDATE_DATE,
114 LAST_UPDATE_LOGIN
115 FROM Okc_Line_Style_Sources
116 WHERE okc_line_style_sources.lse_id = p_lse_id
117 AND okc_line_style_sources.jtot_object_code = p_jtot_object_code;
118 l_okc_lssv_pk okc_lssv_pk_csr%ROWTYPE;
119 l_lssv_rec lssv_rec_type;
120 BEGIN
121 x_no_data_found := TRUE;
122 -- Get current database values
123 OPEN okc_lssv_pk_csr (p_lssv_rec.lse_id,
124 p_lssv_rec.jtot_object_code);
125 FETCH okc_lssv_pk_csr INTO
126 l_lssv_rec.LSE_ID,
127 l_lssv_rec.JTOT_OBJECT_CODE,
128 l_lssv_rec.OBJECT_VERSION_NUMBER,
129 l_lssv_rec.ACCESS_LEVEL,
130 l_lssv_rec.START_DATE,
131 l_lssv_rec.END_DATE,
132 l_lssv_rec.CREATED_BY,
133 l_lssv_rec.CREATION_DATE,
134 l_lssv_rec.LAST_UPDATED_BY,
135 l_lssv_rec.LAST_UPDATE_DATE,
136 l_lssv_rec.LAST_UPDATE_LOGIN;
137 x_no_data_found := okc_lssv_pk_csr%NOTFOUND;
138 CLOSE okc_lssv_pk_csr;
139 RETURN(l_lssv_rec);
140 END get_rec;
141
142 FUNCTION get_rec (
143 p_lssv_rec IN lssv_rec_type
144 ) RETURN lssv_rec_type IS
145 l_row_notfound BOOLEAN := TRUE;
146 BEGIN
147 RETURN(get_rec(p_lssv_rec, l_row_notfound));
148 END get_rec;
149
150 --------------------------------------------------------------
151 -- FUNCTION null_out_defaults for: OKC_LINE_STYLE_SOURCES_V --
152 --------------------------------------------------------------
153 FUNCTION null_out_defaults (
154 p_lssv_rec IN lssv_rec_type
155 ) RETURN lssv_rec_type IS
156 l_lssv_rec lssv_rec_type := p_lssv_rec;
157 BEGIN
158 IF (l_lssv_rec.lse_id = OKC_API.G_MISS_NUM) THEN
159 l_lssv_rec.lse_id := NULL;
160 END IF;
161 IF (l_lssv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
162 l_lssv_rec.object_version_number := NULL;
163 END IF;
164 IF (l_lssv_rec.start_date = OKC_API.G_MISS_DATE) THEN
165 l_lssv_rec.start_date := NULL;
166 END IF;
167 IF (l_lssv_rec.end_date = OKC_API.G_MISS_DATE) THEN
168 l_lssv_rec.end_date := NULL;
169 END IF;
170 IF (l_lssv_rec.created_by = OKC_API.G_MISS_NUM) THEN
171 l_lssv_rec.created_by := NULL;
172 END IF;
173 IF (l_lssv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
174 l_lssv_rec.creation_date := NULL;
175 END IF;
176 IF (l_lssv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
177 l_lssv_rec.last_updated_by := NULL;
178 END IF;
179 IF (l_lssv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
180 l_lssv_rec.last_update_date := NULL;
181 END IF;
182 IF (l_lssv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
183 l_lssv_rec.last_update_login := NULL;
184 END IF;
185 IF (l_lssv_rec.access_level = OKC_API.G_MISS_CHAR) THEN
186 l_lssv_rec.access_level := NULL;
187 END IF;
188 RETURN(l_lssv_rec);
189 END null_out_defaults;
190 ---------------------------------------------------------------------------
191 -- PROCEDURE Validate_Attributes
192 ---------------------------------------------------------------------------
193 ------------------------------------------------------
194 -- Validate_Attributes for:OKC_LINE_STYLE_SOURCES_V --
195 ------------------------------------------------------
196 --**** Change from TAPI Code---follow till end of change---------------
197
198
199 -- This function checks if the new range does not overlap any existing range for the given sources
200 -- of the given line_style i'e only one source is active at a time for a line style
201 --
202 -- Date: 08/10/2001 08:20am
203 -- The body fo following procedure was commented out to avoid an inherent bug.
204 -- The check will always fail!!!!!
205 --
206 -- Problem Corrected on 08/11/2001
207 --
208 FUNCTION Validate_Date_RANGE (
209 p_lssv_rec IN lssv_rec_type)
210 RETURN VARCHAR2 IS
211 l_date date:=OKC_API.G_MISS_DATE;
212 l_end_date date:=OKC_API.G_MISS_DATE;
213 x_return_status VARCHAR2(1);
214 l_excp_error EXCEPTION;
215 l_row_found BOOLEAN:= FALSE;
216
217 Cursor C1 is select start_date from okc_line_style_sources where lse_id=p_lssv_rec.lse_id
218 and end_date is null and jtot_object_code<>p_lssv_rec.jtot_object_code;
219
220 Cursor C2(p_date IN date) is select start_date,nvl(end_date,p_date+1)
221 from OKC_LINE_STYLE_SOURCES where
222 lse_id=p_lssv_rec.lse_id and jtot_object_code<>p_lssv_rec.jtot_object_code;
223
224 BEGIN
225 IF (l_debug = 'Y') THEN
226 okc_debug.set_indentation('Validate_Date_RANGE');
227 okc_debug.Log('100:entering Validate_Date_RANGE',1);
228 END IF;
229
230 x_return_status := OKC_API.G_RET_STS_SUCCESS;
231
232 If p_lssv_rec.end_date is null then
233 IF (l_debug = 'Y') THEN
234 okc_debug.Log('200:validate_date_range',1);
235 END IF;
236
237 OPEN C1;
238 FETCH C1 into l_date;
239 l_row_found := C1%FOUND;
240 CLOSE C1;
241
242 --- this is replaced by the if statement below
243 /* IF l_date = OKC_API.G_MISS_DATE then
244 IF (l_debug = 'Y') THEN
245 okc_debug.Log('300:validate_date_range',1);
246 END IF;
247 -- RAISE l_excp_error; -- This is where it should have been!!!!
248
249 ELSE IF p_lssv_rec.start_date>=l_date then --this has been changed to check for the same date aswell
250 --for BUG #1932165
251 l_date:=p_lssv_rec.start_date;
252 IF (l_debug = 'Y') THEN
253 okc_debug.Log('400:validate_date_range',1);
254 END IF;
255 RAISE l_excp_error;
256 end if;
257
258 -- RAISE l_excp_error; --misplaced command!!!!!!
259 END IF;
260 */
261 -- when the current record's end date is null , if there is an existing source with end date as null then ..error
262 IF(l_row_found) then
263 RAISE l_excp_error;
264 END IF;
265 END IF;
266 OPEN C2(p_lssv_rec.end_date);
267 LOOP
268 FETCH C2 into l_date,l_end_date;
269 EXIT WHEN C2%NOTFOUND;
270 If trunc(p_lssv_rec.start_date)>=trunc(l_date) and trunc(p_lssv_rec.start_date)<=trunc(l_end_date) then
271 l_date:=p_lssv_rec.start_date;
272 IF (l_debug = 'Y') THEN
273 okc_debug.Log('500:8validate_date_range',1);
274 END IF;
275 RAISE l_excp_error;
276 Elsif trunc(nvl(p_lssv_rec.end_date,l_end_date+1))>=trunc(l_date)
277 and trunc(nvl(p_lssv_rec.end_date,l_end_date+1))<=trunc(l_end_date) then
278 l_date:=nvl(p_lssv_rec.end_date,l_end_date);
279 IF (l_debug = 'Y') THEN
280 okc_debug.Log('500:validate_date_range',1);
281 END IF;
282 RAISE l_excp_error;
283 ElsIf trunc(l_date)>=trunc(p_lssv_rec.start_date)
284 and trunc(l_date)<=trunc(nvl(p_lssv_rec.end_date,l_end_date+1)) then
285 IF (l_debug = 'Y') THEN
286 okc_debug.Log('600:validate_date_range',1);
287 END IF;
288 RAISE l_excp_error;
289 ElsIf trunc(l_end_date)>=trunc(p_lssv_rec.start_date)
290 and trunc(l_end_date)<=trunc(nvl(p_lssv_rec.end_date,l_end_date+1)) then
291 l_date:=l_end_date;
292 IF (l_debug = 'Y') THEN
293 okc_debug.Log('700:validate_date_range',1);
294 END IF;
295 RAISE l_excp_error;
296 END IF;
297 END LOOP;
298 CLOSE C2;
299 return x_return_status;
300 EXCEPTION
301 WHEN l_excp_error then
302 IF (l_debug = 'Y') THEN
303 okc_debug.Log('800:validate_date_range',1);
304 END IF;
305 x_return_status:=OKC_API.G_RET_STS_ERROR;
306 IF C2%ISOPEN then
307 CLOSE C2;
308 END IF;
309 OKC_API.set_message(G_APP_NAME, G_TOO_MANY_SOURCES,'DATE',
310 fnd_date.date_to_chardate(l_date)
311 ,'SOURCE','OKC_LINE_STYLE_SOURCES');
312 return x_return_status;
313
314 WHEN OTHERS THEN
315 -- store SQL error message on message stack for caller
316 IF (l_debug = 'Y') THEN
317 okc_debug.Log('800:validate_date_range',1);
318 END IF;
319 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
320 p_msg_name => g_unexpected_error,
321 p_token1 => g_sqlcode_token,
322 p_token1_value => sqlcode,
323 p_token2 => g_col_name_token,
324 p_token2_value => 'START_DATE',
325 p_token3 => g_sqlerrm_token,
326 p_token3_value => sqlerrm);
327 -- notify caller of an UNEXPECTED error
328 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
329 IF C2%ISOPEN then
330 CLOSE C2;
331 END IF;
332 return x_return_status;
333 IF (l_debug = 'Y') THEN
334 okc_debug.reset_indentation;
335 END IF;
336 END ValidAte_DATE_RANGE;
337
338 --this is the old style range check. Was failing in case given start date and end date were the one
339 --not covered in any range individually. Changed with the above code
340 FUNCTION Validate_Date_RANGE1 (
341 p_lssv_rec IN lssv_rec_type)
342 RETURN VARCHAR2 IS
343 l_date date:=OKC_API.G_MISS_DATE;
344 x_return_status VARCHAR2(1);
345 l_excp_error EXCEPTION;
346 Cursor C1 is select start_date from okc_line_style_sources where lse_id=p_lssv_rec.lse_id
347 and end_date is null;
348
349 Cursor C2(p_date IN date) is select p_date from OKC_LINE_STYLE_SOURCES where
350 lse_id=p_lssv_rec.lse_id and jtot_object_code<>p_lssv_rec.jtot_object_code and
351 trunc(p_date) between trunc(start_date) and trunc(nvl(end_date,p_date));
352
353 Cursor C3(p_date IN date) is select start_date from OKC_LINE_STYLE_SOURCES where
354 lse_id=p_lssv_rec.lse_id and jtot_object_code<>p_lssv_rec.jtot_object_code and
355 trunc(start_date) between trunc(p_date) and trunc(end_date);
356 BEGIN
357
358 x_return_status := OKC_API.G_RET_STS_SUCCESS;
359 If p_lssv_rec.end_date is null then
360 OPEN C1;
361 FETCH C1 into l_date;
362 CLOSE C1;
363 IF l_date<>OKC_API.G_MISS_DATE then
364 IF p_lssv_rec.start_date>l_date then
365 l_date:=p_lssv_rec.start_date;
366 end if;
367 RAISE l_excp_error;
368 END IF;
369 END IF;
370 OPEN C2(p_lssv_rec.start_date);
371 FETCH C2 into l_date;
372 CLOSE C2;
373 IF l_date<>OKC_API.G_MISS_DATE then
374 RAISE l_excp_error;
375 END IF;
376 IF p_lssv_rec.end_date is null then
377 OPEN C3(p_lssv_rec.start_date);
378 FETCH C3 into l_date;
379 CLOSE C3;
380 IF l_date<>OKC_API.G_MISS_DATE then
381 RAISE l_excp_error;
382 END IF;
383 ELSE
384 OPEN C2(p_lssv_rec.end_date);
385 FETCH C2 into l_date;
386 CLOSE C2;
387 IF l_date<>OKC_API.G_MISS_DATE then
388 RAISE l_excp_error;
389 END IF;
390 END IF;
391 return x_return_status;
392 --return 'E';
393 EXCEPTION
394 WHEN l_excp_error then
395 x_return_status:=OKC_API.G_RET_STS_ERROR;
396 OKC_API.set_message(G_APP_NAME, G_TOO_MANY_SOURCES,'DATE',
397 fnd_date.date_to_chardate(l_date)
398 ,'SOURCE','OKC_LINE_STYLE_SOURCES');
399 return x_return_status;
400
401 WHEN OTHERS THEN
402 -- store SQL error message on message stack for caller
403 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
404 p_msg_name => g_unexpected_error,
405 p_token1 => g_sqlcode_token,
406 p_token1_value => sqlcode,
407 p_token2 => g_col_name_token,
408 p_token2_value => 'START_DATE',
409 p_token3 => g_sqlerrm_token,
410 p_token3_value => sqlerrm);
411 -- notify caller of an UNEXPECTED error
412 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
413 return x_return_status;
414 END Validate_DATE_RANGE1;
415
416 PROCEDURE Validate_Start_Date (
417 x_return_status OUT NOCOPY VARCHAR2,
418 p_lssv_rec IN lssv_rec_type) IS
419 BEGIN
420 x_return_status := OKC_API.G_RET_STS_SUCCESS;
421 IF (p_lssv_rec.start_date = OKC_API.G_MISS_DATE OR
422 p_lssv_rec.start_date IS NULL) THEN
423 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'start_date');
424 x_return_status := OKC_API.G_RET_STS_ERROR;
425 END IF;
426 EXCEPTION
427 WHEN OTHERS THEN
428 -- store SQL error message on message stack for caller
429 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
430 p_msg_name => g_unexpected_error,
431 p_token1 => g_sqlcode_token,
432 p_token1_value => sqlcode,
433 p_token2 => g_col_name_token,
434 p_token2_value => 'START_DATE',
435 p_token3 => g_sqlerrm_token,
436 p_token3_value => sqlerrm);
437 -- notify caller of an UNEXPECTED error
438 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
439 END Validate_Start_Date;
440 --
441 ---------------------------------------------------------------------------
442 -- PROCEDURE Validate_ACCESS_LEVEL
443 ---------------------------------------------------------------------------
444 PROCEDURE validate_access_level(
445 p_lssv_rec IN lssv_rec_type,
446 x_return_status OUT NOCOPY VARCHAR2) IS
447 Begin
448
449 -- initialize return status
450 x_return_status := OKC_API.G_RET_STS_SUCCESS;
451
452 -- check that data exists
453 If (p_lssv_rec.access_level <> OKC_API.G_MISS_CHAR and
454 p_lssv_rec.access_level IS NOT NULL)
455 Then
456 If p_lssv_rec.access_level NOT IN ('S','E', 'U') Then
457 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
458 p_msg_name => g_invalid_value,
459 p_token1 => g_col_name_token,
460 p_token1_value => 'access_level');
461 -- notify caller of an error
462 x_return_status := OKC_API.G_RET_STS_ERROR;
463
464 -- halt validation
465 raise G_EXCEPTION_HALT_VALIDATION;
466 end if;
467 End If;
468 exception
469 when G_EXCEPTION_HALT_VALIDATION then
470 -- no processing necessary; validation can continue with next column
471 null;
472
473 when OTHERS then
474 -- store SQL error message on message stack
475 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
476 p_msg_name => g_unexpected_error,
477 p_token1 => g_sqlcode_token,
478 p_token1_value => sqlcode,
479 p_token2 => g_sqlerrm_token,
480 p_token2_value => sqlerrm);
481 -- notify caller of an error as UNEXPETED error
482 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
483 End validate_access_level;
484 --
485 --
486 PROCEDURE Validate_lse_id (
487 x_return_status OUT NOCOPY VARCHAR2,
488 p_lssv_rec IN lssv_rec_type) IS
489 l_excp_halt_validation EXCEPTION;
490 l_row_notfound BOOLEAN := TRUE;
491 CURSOR lse_pk_csr (p_lse_id IN number) IS
492 SELECT lty_code
493 FROM OKC_LINE_STYLES_B
494 WHERE id = p_lse_id;
495 l_lse_pk lse_pk_csr%ROWTYPE;
496 BEGIN
497 x_return_status := OKC_API.G_RET_STS_SUCCESS;
498 IF (p_lssv_rec.lse_id IS NOT NULL AND
499 p_lssv_rec.lse_id <> OKC_API.G_MISS_NUM)
500 THEN
501 OPEN lse_pk_csr(p_lssv_rec.lse_id);
502 FETCH lse_pk_csr INTO l_lse_pk;
503 l_row_notfound := lse_pk_csr%NOTFOUND;
504 CLOSE lse_pk_csr;
505 IF (l_row_notfound) THEN
506 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'lse_id');
507 RAISE l_excp_halt_validation;
508 ELSE
509 IF l_lse_pk.lty_code='FREE_FORM' then
510 OKC_API.set_message(G_APP_NAME,'OKC_NO_SRC_FOR_FREE');
511 RAISE l_excp_halt_validation;
512 END IF;
513 END IF;
514 ELSE
515 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'lse_id');
516 x_return_status := OKC_API.G_RET_STS_ERROR;
517 END IF;
518 EXCEPTION
519 WHEN l_excp_halt_validation 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 => 'lse_id',
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_lse_id ;
533
534 PROCEDURE Validate_JTOT_Object_Code(
535 x_return_status OUT NOCOPY VARCHAR2,
536 p_lssv_rec IN lssv_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 CURSOR jtot_pk_csr (p_jtot_object_code JTF_OBJECTS_B.object_code%type) IS
541 SELECT '1'
542 FROM JTF_OBJECTS_B a,jtf_object_usages b
543 WHERE a.object_code = p_jtot_object_code
544 and sysdate between nvl(a.start_date_active,sysdate) and nvl(a.end_date_active,sysdate)
545 and a.object_code=b.object_code and b.object_user_code='OKX_LINES';
546 l_jtot_pk jtot_pk_csr%ROWTYPE;
547 BEGIN
548 x_return_status := OKC_API.G_RET_STS_SUCCESS;
549 IF (p_lssv_rec.JTOT_OBJECT_CODE IS NOT NULL AND
550 p_lssv_rec.JTOT_OBJECT_CODE <> OKC_API.G_MISS_CHAR)
551 THEN
552 OPEN jtot_pk_csr(p_lssv_rec.jtot_object_code);
553 FETCH jtot_pk_csr INTO l_jtot_pk;
554 l_row_notfound := jtot_pk_csr%NOTFOUND;
555 CLOSE jtot_pk_csr;
556 IF (l_row_notfound) THEN
557 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'jtot_object_code');
558 RAISE item_not_found_error;
559 END IF;
560 ELSE
561 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'jtot_object_code');
562 x_return_status := OKC_API.G_RET_STS_ERROR;
563 END IF;
564 EXCEPTION
565 WHEN item_not_found_error THEN
566 x_return_status := OKC_API.G_RET_STS_ERROR;
567 WHEN OTHERS THEN
568 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
569 p_msg_name => g_unexpected_error,
570 p_token1 => g_sqlcode_token,
571 p_token1_value => sqlcode,
572 p_token2 => g_col_name_token,
573 p_token2_value => 'JTOT_OBJECT_CODE',
574 p_token3 => g_sqlerrm_token,
575 p_token3_value => sqlerrm);
576 -- notify caller of an UNEXPECTED error
577 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
578 END Validate_JTOT_Object_Code;
579
580
581 PROCEDURE Validate_Object_Code_item_t_pr(
582 x_return_status OUT NOCOPY VARCHAR2,
583 p_lssv_rec IN lssv_rec_type) IS
584 l_item_to_price_yn VARCHAR2(3):=NULL;
585 l_object_user_code VARCHAR2(30):=NULL;
586 l_mtl_found BOOLEAN := FALSE;
587 CURSOR loc_source_csr is
588 select OBJECT_USER_CODE
589 from JTF_OBJECT_USAGES where
590 OBJECT_CODE = p_lssv_rec.JTOT_OBJECT_CODE
591 AND OBJECT_USER_CODE = 'OKX_MTL_SYSTEM_ITEM';
592
593 BEGIN
594 Select item_to_price_yn into l_item_to_price_yn from OKC_LINE_STYLES_B
595 where id = p_lssv_rec.lse_id;
596 IF sysdate between p_lssv_rec.START_DATE and nvl( p_lssv_rec.END_DATE,sysdate) THEN
597 IF l_item_to_price_yn = 'Y'
598 THEN
599 open loc_source_csr;
600 Fetch loc_source_csr into l_object_user_code;
601 l_mtl_found := loc_source_csr%FOUND;
602 Close loc_source_csr;
603
604 IF NOT l_mtl_found THEN
605 x_return_status := OKC_API.G_RET_STS_ERROR;
606 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
607 p_msg_name => 'OKC_ITEM_TO_PRICE_SOURCE');
608
609 END IF;
610 IF l_mtl_found THEN
611 x_return_status := OKC_API.G_RET_STS_SUCCESS;
612 END IF;
613 ELSE
614 x_return_status := OKC_API.G_RET_STS_SUCCESS;
615 END IF;
616 ELSE
617 x_return_status := OKC_API.G_RET_STS_SUCCESS;
618 END IF;
619 EXCEPTION
620 WHEN NO_DATA_FOUND THEN
621 x_return_status := OKC_API.G_RET_STS_SUCCESS;
622
623 WHEN OTHERS THEN
624 OKC_API.set_message(p_app_name => g_app_name,
625 p_msg_name => g_unexpected_error,
626 p_token1 => g_sqlcode_token,
627 p_token1_value => sqlcode,
628 p_token2 => g_sqlerrm_token,
629 p_token2_value => sqlerrm);
630 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
631 END Validate_Object_Code_item_t_pr;
632 FUNCTION Validate_Attributes (
633 p_lssv_rec IN lssv_rec_type
634 ) RETURN VARCHAR2 IS
635 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
636 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
637 BEGIN
638 IF p_lssv_rec.object_version_number = OKC_API.G_MISS_NUM OR
639 p_lssv_rec.object_version_number IS NULL
640 THEN
641 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
642 x_return_status := OKC_API.G_RET_STS_ERROR;
643 END IF;
644 Validate_jtot_object_code (l_return_status,
645 p_lssv_rec);
646 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
647 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
648 x_return_status := l_return_status;
649 RAISE G_EXCEPTION_HALT_VALIDATION;
650 ELSE
651 x_return_status := l_return_status; -- record that there was an error
652 END IF;
653 END IF;
654 Validate_lse_id (l_return_status,
655 p_lssv_rec);
656 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
657 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
658 x_return_status := l_return_status;
659 RAISE G_EXCEPTION_HALT_VALIDATION;
660 ELSE
661 x_return_status := l_return_status; -- record that there was an error
662 END IF;
663 END IF;
664
665 Validate_object_code_item_t_pr (l_return_status,
666 p_lssv_rec);
667 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
668 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
669 x_return_status := l_return_status;
670 RAISE G_EXCEPTION_HALT_VALIDATION;
671 ELSE
672 x_return_status := l_return_status; -- record that there was an error
673 END IF;
674 END IF;
675 --
676 Validate_access_level (p_lssv_rec,
677 l_return_status);
678 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
679 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
680 x_return_status := l_return_status;
681 RAISE G_EXCEPTION_HALT_VALIDATION;
682 ELSE
683 x_return_status := l_return_status; -- record that there was an error
684 END IF;
685 END IF;
686
687 --
688 Validate_start_date (l_return_status,
689 p_lssv_rec);
690 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
691 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
692 x_return_status := l_return_status;
693 RAISE G_EXCEPTION_HALT_VALIDATION;
694 ELSE
695 x_return_status := l_return_status; -- record that there was an error
696 END IF;
697 END IF;
698
699 RETURN(x_return_status);
700
701 EXCEPTION
702 When G_EXCEPTION_HALT_VALIDATION then
703 --just come out with return status
704 RETURN(x_return_status);
705 -- other appropriate handlers
706 When others then
707 -- store SQL error message on message stack
708 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
709 p_msg_name => G_UNEXPECTED_ERROR,
710 p_token1 => G_SQLCODE_TOKEN,
711 p_token1_value => sqlcode,
712 p_token2 => G_SQLERRM_TOKEN,
713 p_token2_value => sqlerrm);
714 --notify UNEXPECTED error
715 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
716 RETURN(x_return_status);
717
718 END Validate_Attributes;
719
720 ---------------------------------------------------------------------------
721 -- PROCEDURE Validate_Record
722 ---------------------------------------------------------------------------
723 -----------------------------------------------
724 -- Validate_Record for:OKC_LINE_STYLE_SOURCES_V --
725 -----------------------------------------------
726 FUNCTION Validate_Record (
727 p_lssv_rec IN lssv_rec_type
728 ) RETURN VARCHAR2 IS
729
730 -- ------------------------------------------------------
731 -- To check for any matching row, for unique combination.
732 -- ------------------------------------------------------
733 CURSOR cur_lss IS
734 SELECT 'x'
735 FROM okc_line_style_sources
736 WHERE lse_id = p_lssv_rec.LSE_ID
737 AND jtot_object_code = p_lssv_rec.JTOT_OBJECT_CODE;
738
739
740 CURSOR cur_lss_1 IS
741 SELECT 'x'
742 FROM okc_line_style_sources
743 WHERE lse_id = p_lssv_rec.LSE_ID;
744
745
746 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
747 l_row_found BOOLEAN := FALSE;
748 l_dummy VARCHAR2(1);
749 BEGIN
750
751 IF (l_debug = 'Y') THEN
752 okc_debug.set_indentation('Validate_Record');
753 okc_debug.log('in validate_record',1);
754 END IF;
755 -- ---------------------------------------------------------------------
756 -- Bug 1636056 related changes - Shyam
757 -- OKC_UTIL.check_comp_unique call is replaced with
758 -- the explicit cursors above, for identical function to check
759 -- uniqueness for LSE_ID + JTOT_OBJECT_CODE in OKC_LINE_STYLE_SOURCES_V
760 -- ---------------------------------------------------------------------
761 IF G_RECORD_STATUS = 'I' THEN
762 IF (l_debug = 'Y') THEN
763 okc_debug.log('100:invalidate_record',1);
764 END IF;
765 IF ( (p_lssv_rec.LSE_ID IS NOT NULL)
766 AND (p_lssv_rec.LSE_ID <> OKC_API.G_MISS_NUM) )
767 AND
768 ( (p_lssv_rec.JTOT_OBJECT_CODE IS NOT NULL)
769 AND (p_lssv_rec.JTOT_OBJECT_CODE <> OKC_API.G_MISS_CHAR) )
770 THEN
771 IF (l_debug = 'Y') THEN
772 okc_debug.log('200:invalidate_record',1);
773 END IF;
774 OPEN cur_lss;
775 FETCH cur_lss INTO l_dummy;
776 l_row_found := cur_lss%FOUND;
777 CLOSE cur_lss;
778
779 IF (l_row_found)
780 THEN
781
782 IF (l_debug = 'Y') THEN
783 okc_debug.log('300:invalidate_record',1);
784 END IF;
785
786 -- Display the newly defined error message
787 OKC_API.set_message(G_APP_NAME,
788 'OKC_DUP_LINE_STYLE_SOURCE');
789 l_return_status := OKC_API.G_RET_STS_ERROR;
790
791
792 -- the following code added for BUG#1932165 to call the validate_date_range when there is an
793 -- existing line style source with end date = null
794 ELSE
795 IF (l_debug = 'Y') THEN
796 okc_debug.log('005:invalidate_record',1);
797 END IF;
798 OPEN cur_lss_1;
799 FETCH cur_lss_1 INTO l_dummy;
800 l_row_found := cur_lss_1%FOUND;
801 CLOSE cur_lss_1;
802 IF (l_row_found)
803 THEN
804 IF (l_debug = 'Y') THEN
805 okc_debug.log('006:invalidate_record',1);
806 END IF;
807 l_return_status:=VALIDATE_DATE_RANGE(p_lssv_rec);
808 END IF;
809 END IF;
810
811 return (l_return_status);
812 END IF;
813 END IF;
814
815 IF ( (p_lssv_rec.start_date > p_lssv_rec.end_date)
816 AND (p_lssv_rec.end_date IS NOT NULL
817 AND p_lssv_rec.end_date <> OKC_API.G_MISS_DATE ))
818 THEN
819 IF (l_debug = 'Y') THEN
820 okc_debug.log('400:invalidate_record',1);
821 END IF;
822
823 OKC_API.set_message(G_APP_NAME,
824 G_INVALID_VALUE,
825 G_COL_NAME_TOKEN,
826 'Start Date > End Date');
827 l_return_status := OKC_API.G_RET_STS_ERROR;
828 ELSE
829 IF (l_debug = 'Y') THEN
830 okc_debug.log('500:invalidate_record',1);
831 END IF;
832
833 l_return_status:=VALIDATE_DATE_RANGE(p_lssv_rec);
834 END IF;
835 RETURN (l_return_status);
836 IF (l_debug = 'Y') THEN
837 okc_debug.reset_indentation;
838 END IF;
839 END Validate_Record;
840
841 --**** end of change-------------------------------------------------------
842 ---------------------------------------------------------------------------
843 -- PROCEDURE Migrate
844 ---------------------------------------------------------------------------
845 PROCEDURE migrate (
846 p_from IN lssv_rec_type,
847 p_to IN OUT NOCOPY lss_rec_type
848 ) IS
849 BEGIN
850 p_to.lse_id := p_from.lse_id;
851 p_to.jtot_object_code := p_from.jtot_object_code;
852 p_to.start_date := p_from.start_date;
853 p_to.object_version_number := p_from.object_version_number;
854 p_to.created_by := p_from.created_by;
855 p_to.creation_date := p_from.creation_date;
856 p_to.last_updated_by := p_from.last_updated_by;
857 p_to.last_update_date := p_from.last_update_date;
858 p_to.end_date := p_from.end_date;
859 p_to.last_update_login := p_from.last_update_login;
860 p_to.access_level := p_from.access_level;
861 END migrate;
862 PROCEDURE migrate (
863 p_from IN lss_rec_type,
864 p_to IN OUT NOCOPY lssv_rec_type
865 ) IS
866 BEGIN
867 p_to.lse_id := p_from.lse_id;
868 p_to.jtot_object_code := p_from.jtot_object_code;
869 p_to.start_date := p_from.start_date;
870 p_to.object_version_number := p_from.object_version_number;
871 p_to.created_by := p_from.created_by;
872 p_to.creation_date := p_from.creation_date;
873 p_to.last_updated_by := p_from.last_updated_by;
874 p_to.last_update_date := p_from.last_update_date;
875 p_to.end_date := p_from.end_date;
876 p_to.last_update_login := p_from.last_update_login;
877 p_to.access_level := p_from.access_level;
878 END migrate;
879
880 ---------------------------------------------------------------------------
881 -- PROCEDURE validate_row
882 ---------------------------------------------------------------------------
883 -----------------------------------------------
884 -- validate_row for:OKC_LINE_STYLE_SOURCES_V --
885 -----------------------------------------------
886 PROCEDURE validate_row(
887 p_api_version IN NUMBER,
888 p_init_msg_list IN VARCHAR2 ,
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_msg_count OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2,
892 p_lssv_rec IN lssv_rec_type) IS
893
894 l_api_version CONSTANT NUMBER := 1;
895 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
896 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
897 l_lssv_rec lssv_rec_type := p_lssv_rec;
898 l_lss_rec lss_rec_type;
899 BEGIN
900 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
901 G_PKG_NAME,
902 p_init_msg_list,
903 l_api_version,
904 p_api_version,
905 '_PVT',
906 x_return_status);
907 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
908 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
909 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
910 RAISE OKC_API.G_EXCEPTION_ERROR;
911 END IF;
912 --- Validate all non-missing attributes (Item Level Validation)
913 l_return_status := Validate_Attributes(l_lssv_rec);
914 --- If any errors happen abort API
915 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
916 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
917 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
918 RAISE OKC_API.G_EXCEPTION_ERROR;
919 END IF;
920 l_return_status := Validate_Record(l_lssv_rec);
921 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
922 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
923 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
924 RAISE OKC_API.G_EXCEPTION_ERROR;
925 END IF;
926 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
927 EXCEPTION
928 WHEN OKC_API.G_EXCEPTION_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_ERROR',
934 x_msg_count,
935 x_msg_data,
936 '_PVT'
937 );
938 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
939 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
940 (
941 l_api_name,
942 G_PKG_NAME,
943 'OKC_API.G_RET_STS_UNEXP_ERROR',
944 x_msg_count,
945 x_msg_data,
946 '_PVT'
947 );
948 WHEN OTHERS THEN
949 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
950 (
951 l_api_name,
952 G_PKG_NAME,
953 'OTHERS',
954 x_msg_count,
955 x_msg_data,
956 '_PVT'
957 );
958 END validate_row;
959 ------------------------------------------
960 -- PL/SQL TBL validate_row for:LSSV_TBL --
961 ------------------------------------------
962 PROCEDURE validate_row(
963 p_api_version IN NUMBER,
964 p_init_msg_list IN VARCHAR2 ,
965 x_return_status OUT NOCOPY VARCHAR2,
966 x_msg_count OUT NOCOPY NUMBER,
967 x_msg_data OUT NOCOPY VARCHAR2,
968 p_lssv_tbl IN lssv_tbl_type) IS
969
970 l_api_version CONSTANT NUMBER := 1;
971 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
972 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
973 i NUMBER := 0;
974 BEGIN
975 OKC_API.init_msg_list(p_init_msg_list);
976 -- Make sure PL/SQL table has records in it before passing
977 IF (p_lssv_tbl.COUNT > 0) THEN
978 i := p_lssv_tbl.FIRST;
979 LOOP
980 validate_row (
981 p_api_version => p_api_version,
982 p_init_msg_list => OKC_API.G_FALSE,
983 x_return_status => x_return_status,
984 x_msg_count => x_msg_count,
985 x_msg_data => x_msg_data,
986 p_lssv_rec => p_lssv_tbl(i));
987 EXIT WHEN (i = p_lssv_tbl.LAST);
988 i := p_lssv_tbl.NEXT(i);
989 END LOOP;
990 END IF;
991 EXCEPTION
992 WHEN OKC_API.G_EXCEPTION_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_ERROR',
998 x_msg_count,
999 x_msg_data,
1000 '_PVT'
1001 );
1002 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1003 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1004 (
1005 l_api_name,
1006 G_PKG_NAME,
1007 'OKC_API.G_RET_STS_UNEXP_ERROR',
1008 x_msg_count,
1009 x_msg_data,
1010 '_PVT'
1011 );
1012 WHEN OTHERS THEN
1013 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1014 (
1015 l_api_name,
1016 G_PKG_NAME,
1017 'OTHERS',
1018 x_msg_count,
1019 x_msg_data,
1020 '_PVT'
1021 );
1022 END validate_row;
1023
1024 ---------------------------------------------------------------------------
1025 -- PROCEDURE insert_row
1026 ---------------------------------------------------------------------------
1027 -------------------------------------------
1028 -- insert_row for:OKC_LINE_STYLE_SOURCES --
1029 -------------------------------------------
1030 PROCEDURE insert_row(
1031 p_init_msg_list IN VARCHAR2 ,
1032 x_return_status OUT NOCOPY VARCHAR2,
1033 x_msg_count OUT NOCOPY NUMBER,
1034 x_msg_data OUT NOCOPY VARCHAR2,
1035 p_lss_rec IN lss_rec_type,
1036 x_lss_rec OUT NOCOPY lss_rec_type) IS
1037
1038 l_api_version CONSTANT NUMBER := 1;
1039 l_api_name CONSTANT VARCHAR2(30) := 'SOURCES_insert_row';
1040 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1041 l_lss_rec lss_rec_type := p_lss_rec;
1042 l_def_lss_rec lss_rec_type;
1043 -----------------------------------------------
1044 -- Set_Attributes for:OKC_LINE_STYLE_SOURCES --
1045 -----------------------------------------------
1046 FUNCTION Set_Attributes (
1047 p_lss_rec IN lss_rec_type,
1048 x_lss_rec OUT NOCOPY lss_rec_type
1049 ) RETURN VARCHAR2 IS
1050 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1051 BEGIN
1052 x_lss_rec := p_lss_rec;
1053 RETURN(l_return_status);
1054 END Set_Attributes;
1055 BEGIN
1056 IF (l_debug = 'Y') THEN
1057 okc_debug.set_indentation('insert_row');
1058 okc_debug.log('100:inside insert row',1 );
1059 END IF;
1060 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1061 p_init_msg_list,
1062 '_PVT',
1063 x_return_status);
1064 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1065 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1066 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1067 RAISE OKC_API.G_EXCEPTION_ERROR;
1068 END IF;
1069 --- Setting item attributes
1070 l_return_status := Set_Attributes(
1071 p_lss_rec, -- IN
1072 l_lss_rec); -- OUT
1073 --- If any errors happen abort API
1074 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1075 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1076 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1077 RAISE OKC_API.G_EXCEPTION_ERROR;
1078 END IF;
1079 INSERT INTO OKC_LINE_STYLE_SOURCES(
1080 lse_id,
1081 jtot_object_code,
1082 start_date,
1083 object_version_number,
1084 access_level,
1085 created_by,
1086 creation_date,
1087 last_updated_by,
1088 last_update_date,
1089 end_date,
1090 last_update_login)
1091 VALUES (
1092 l_lss_rec.lse_id,
1093 l_lss_rec.jtot_object_code,
1094 l_lss_rec.start_date,
1095 l_lss_rec.object_version_number,
1096 l_lss_rec.access_level,
1097 l_lss_rec.created_by,
1098 l_lss_rec.creation_date,
1099 l_lss_rec.last_updated_by,
1100 l_lss_rec.last_update_date,
1101 l_lss_rec.end_date,
1102 l_lss_rec.last_update_login);
1103 -- Set OUT values
1104 x_lss_rec := l_lss_rec;
1105 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1106 EXCEPTION
1107 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1108 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1109 (
1110 l_api_name,
1111 G_PKG_NAME,
1112 'OKC_API.G_RET_STS_ERROR',
1113 x_msg_count,
1114 x_msg_data,
1115 '_PVT'
1116 );
1117 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1118 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1119 (
1120 l_api_name,
1121 G_PKG_NAME,
1122 'OKC_API.G_RET_STS_UNEXP_ERROR',
1123 x_msg_count,
1124 x_msg_data,
1125 '_PVT'
1126 );
1127 WHEN OTHERS THEN
1128 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1129 (
1130 l_api_name,
1131 G_PKG_NAME,
1132 'OTHERS',
1133 x_msg_count,
1134 x_msg_data,
1135 '_PVT'
1136 );
1137 IF (l_debug = 'Y') THEN
1138 okc_debug.reset_indentation;
1139 END IF;
1140 END insert_row;
1141 ---------------------------------------------
1142 -- insert_row for:OKC_LINE_STYLE_SOURCES_V --
1143 ---------------------------------------------
1144 PROCEDURE insert_row(
1145 p_api_version IN NUMBER,
1146 p_init_msg_list IN VARCHAR2 ,
1147 x_return_status OUT NOCOPY VARCHAR2,
1148 x_msg_count OUT NOCOPY NUMBER,
1149 x_msg_data OUT NOCOPY VARCHAR2,
1150 p_lssv_rec IN lssv_rec_type,
1151 x_lssv_rec OUT NOCOPY lssv_rec_type) IS
1152
1153 l_api_version CONSTANT NUMBER := 1;
1154 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1155 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1156 l_lssv_rec lssv_rec_type;
1157 l_def_lssv_rec lssv_rec_type;
1158 l_lss_rec lss_rec_type;
1159 lx_lss_rec lss_rec_type;
1160 -------------------------------
1161 -- FUNCTION fill_who_columns --
1162 -------------------------------
1163 FUNCTION fill_who_columns (
1164 p_lssv_rec IN lssv_rec_type
1165 ) RETURN lssv_rec_type IS
1166 l_lssv_rec lssv_rec_type := p_lssv_rec;
1167 BEGIN
1168 l_lssv_rec.CREATION_DATE := SYSDATE;
1169 l_lssv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1170 l_lssv_rec.LAST_UPDATE_DATE := SYSDATE;
1171 l_lssv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1172 l_lssv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1173 RETURN(l_lssv_rec);
1174 END fill_who_columns;
1175 -------------------------------------------------
1176 -- Set_Attributes for:OKC_LINE_STYLE_SOURCES_V --
1177 -------------------------------------------------
1178 FUNCTION Set_Attributes (
1179 p_lssv_rec IN lssv_rec_type,
1180 x_lssv_rec OUT NOCOPY lssv_rec_type
1181 ) RETURN VARCHAR2 IS
1182 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1183 BEGIN
1184 x_lssv_rec := p_lssv_rec;
1185 x_lssv_rec.OBJECT_VERSION_NUMBER := 1;
1186 RETURN(l_return_status);
1187 END Set_Attributes;
1188 BEGIN
1189 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1190 G_PKG_NAME,
1191 p_init_msg_list,
1192 l_api_version,
1193 p_api_version,
1194 '_PVT',
1195 x_return_status);
1196 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1197 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1198 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1199 RAISE OKC_API.G_EXCEPTION_ERROR;
1200 END IF;
1201 l_lssv_rec := null_out_defaults(p_lssv_rec);
1202 --- Setting item attributes
1203 l_return_status := Set_Attributes(
1204 l_lssv_rec, -- IN
1205 l_def_lssv_rec); -- OUT
1206 --- If any errors happen abort API
1207 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1208 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1209 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1210 RAISE OKC_API.G_EXCEPTION_ERROR;
1211 END IF;
1212 l_def_lssv_rec := fill_who_columns(l_def_lssv_rec);
1213 --- Validate all non-missing attributes (Item Level Validation)
1214 l_return_status := Validate_Attributes(l_def_lssv_rec);
1215 --- If any errors happen abort API
1216 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1217 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1218 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1219 RAISE OKC_API.G_EXCEPTION_ERROR;
1220 END IF;
1221 --**** uniqueness is checked only in insert cases--------------------
1222 G_RECORD_STATUS := 'I';
1223 l_return_status := Validate_Record(l_def_lssv_rec);
1224 G_RECORD_STATUS := OKC_API.G_MISS_CHAR;
1225 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1226 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1227 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1228 RAISE OKC_API.G_EXCEPTION_ERROR;
1229 END IF;
1230 --------------------------------------
1231 -- Move VIEW record to "Child" records
1232 --------------------------------------
1233 migrate(l_def_lssv_rec, l_lss_rec);
1234 --------------------------------------------
1235 -- Call the INSERT_ROW for each child record
1236 --------------------------------------------
1237 insert_row(
1238 p_init_msg_list,
1239 x_return_status,
1240 x_msg_count,
1241 x_msg_data,
1242 l_lss_rec,
1243 lx_lss_rec
1244 );
1245 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1246 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1247 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1248 RAISE OKC_API.G_EXCEPTION_ERROR;
1249 END IF;
1250 migrate(lx_lss_rec, l_def_lssv_rec);
1251 -- Set OUT values
1252 x_lssv_rec := l_def_lssv_rec;
1253 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1254 EXCEPTION
1255 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1256 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1257 (
1258 l_api_name,
1259 G_PKG_NAME,
1260 'OKC_API.G_RET_STS_ERROR',
1261 x_msg_count,
1262 x_msg_data,
1263 '_PVT'
1264 );
1265 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1266 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1267 (
1268 l_api_name,
1269 G_PKG_NAME,
1270 'OKC_API.G_RET_STS_UNEXP_ERROR',
1271 x_msg_count,
1272 x_msg_data,
1273 '_PVT'
1274 );
1275 WHEN OTHERS THEN
1276 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1277 (
1278 l_api_name,
1279 G_PKG_NAME,
1280 'OTHERS',
1281 x_msg_count,
1282 x_msg_data,
1283 '_PVT'
1284 );
1285 END insert_row;
1286 ----------------------------------------
1287 -- PL/SQL TBL insert_row for:LSSV_TBL --
1288 ----------------------------------------
1289 PROCEDURE insert_row(
1290 p_api_version IN NUMBER,
1291 p_init_msg_list IN VARCHAR2 ,
1292 x_return_status OUT NOCOPY VARCHAR2,
1293 x_msg_count OUT NOCOPY NUMBER,
1294 x_msg_data OUT NOCOPY VARCHAR2,
1295 p_lssv_tbl IN lssv_tbl_type,
1296 x_lssv_tbl OUT NOCOPY lssv_tbl_type) IS
1297
1298 l_api_version CONSTANT NUMBER := 1;
1299 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1300 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1301 i NUMBER := 0;
1302 BEGIN
1303 OKC_API.init_msg_list(p_init_msg_list);
1304 -- Make sure PL/SQL table has records in it before passing
1305 IF (p_lssv_tbl.COUNT > 0) THEN
1306 i := p_lssv_tbl.FIRST;
1307 LOOP
1308 insert_row (
1309 p_api_version => p_api_version,
1310 p_init_msg_list => OKC_API.G_FALSE,
1311 x_return_status => x_return_status,
1312 x_msg_count => x_msg_count,
1313 x_msg_data => x_msg_data,
1314 p_lssv_rec => p_lssv_tbl(i),
1315 x_lssv_rec => x_lssv_tbl(i));
1316 EXIT WHEN (i = p_lssv_tbl.LAST);
1317 i := p_lssv_tbl.NEXT(i);
1318 END LOOP;
1319 END IF;
1320 EXCEPTION
1321 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1322 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1323 (
1324 l_api_name,
1325 G_PKG_NAME,
1326 'OKC_API.G_RET_STS_ERROR',
1327 x_msg_count,
1328 x_msg_data,
1329 '_PVT'
1330 );
1331 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1332 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1333 (
1334 l_api_name,
1335 G_PKG_NAME,
1336 'OKC_API.G_RET_STS_UNEXP_ERROR',
1337 x_msg_count,
1338 x_msg_data,
1339 '_PVT'
1340 );
1341 WHEN OTHERS THEN
1342 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1343 (
1344 l_api_name,
1345 G_PKG_NAME,
1346 'OTHERS',
1347 x_msg_count,
1348 x_msg_data,
1349 '_PVT'
1350 );
1351 END insert_row;
1352
1353 ---------------------------------------------------------------------------
1354 -- PROCEDURE lock_row
1355 ---------------------------------------------------------------------------
1356 -----------------------------------------
1357 -- lock_row for:OKC_LINE_STYLE_SOURCES --
1358 -----------------------------------------
1359 PROCEDURE lock_row(
1360 p_init_msg_list IN VARCHAR2 ,
1361 x_return_status OUT NOCOPY VARCHAR2,
1362 x_msg_count OUT NOCOPY NUMBER,
1363 x_msg_data OUT NOCOPY VARCHAR2,
1364 p_lss_rec IN lss_rec_type) IS
1365
1366 E_Resource_Busy EXCEPTION;
1367 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1368 CURSOR lock_csr (p_lss_rec IN lss_rec_type) IS
1369 SELECT OBJECT_VERSION_NUMBER
1370 FROM OKC_LINE_STYLE_SOURCES
1371 WHERE LSE_ID = p_lss_rec.lse_id
1372 AND JTOT_OBJECT_CODE = p_lss_rec.jtot_object_code
1373 AND OBJECT_VERSION_NUMBER = p_lss_rec.object_version_number
1374 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1375
1376 CURSOR lchk_csr (p_lss_rec IN lss_rec_type) IS
1377 SELECT OBJECT_VERSION_NUMBER
1378 FROM OKC_LINE_STYLE_SOURCES
1379 WHERE LSE_ID = p_lss_rec.lse_id
1380 AND JTOT_OBJECT_CODE = p_lss_rec.jtot_object_code;
1381 l_api_version CONSTANT NUMBER := 1;
1382 l_api_name CONSTANT VARCHAR2(30) := 'SOURCES_lock_row';
1383 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1384 l_object_version_number OKC_LINE_STYLE_SOURCES.OBJECT_VERSION_NUMBER%TYPE;
1385 lc_object_version_number OKC_LINE_STYLE_SOURCES.OBJECT_VERSION_NUMBER%TYPE;
1386 l_row_notfound BOOLEAN := FALSE;
1387 lc_row_notfound BOOLEAN := FALSE;
1388 BEGIN
1389 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1390 p_init_msg_list,
1391 '_PVT',
1392 x_return_status);
1393 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1394 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1395 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1396 RAISE OKC_API.G_EXCEPTION_ERROR;
1397 END IF;
1398 BEGIN
1399 OPEN lock_csr(p_lss_rec);
1400 FETCH lock_csr INTO l_object_version_number;
1401 l_row_notfound := lock_csr%NOTFOUND;
1402 CLOSE lock_csr;
1403 EXCEPTION
1404 WHEN E_Resource_Busy THEN
1405 IF (lock_csr%ISOPEN) THEN
1406 CLOSE lock_csr;
1407 END IF;
1408 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1409 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1410 END;
1411
1412 IF ( l_row_notfound ) THEN
1413 OPEN lchk_csr(p_lss_rec);
1414 FETCH lchk_csr INTO lc_object_version_number;
1415 lc_row_notfound := lchk_csr%NOTFOUND;
1416 CLOSE lchk_csr;
1417 END IF;
1418 IF (lc_row_notfound) THEN
1419 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1420 RAISE OKC_API.G_EXCEPTION_ERROR;
1421 ELSIF lc_object_version_number > p_lss_rec.object_version_number THEN
1422 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1423 RAISE OKC_API.G_EXCEPTION_ERROR;
1424 ELSIF lc_object_version_number <> p_lss_rec.object_version_number THEN
1425 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1426 RAISE OKC_API.G_EXCEPTION_ERROR;
1427 ELSIF lc_object_version_number = -1 THEN
1428 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1429 RAISE OKC_API.G_EXCEPTION_ERROR;
1430 END IF;
1431 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1432 EXCEPTION
1433 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1434 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1435 (
1436 l_api_name,
1437 G_PKG_NAME,
1438 'OKC_API.G_RET_STS_ERROR',
1439 x_msg_count,
1440 x_msg_data,
1441 '_PVT'
1442 );
1443 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1444 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1445 (
1446 l_api_name,
1447 G_PKG_NAME,
1448 'OKC_API.G_RET_STS_UNEXP_ERROR',
1449 x_msg_count,
1450 x_msg_data,
1451 '_PVT'
1452 );
1453 WHEN OTHERS THEN
1454 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1455 (
1456 l_api_name,
1457 G_PKG_NAME,
1458 'OTHERS',
1459 x_msg_count,
1460 x_msg_data,
1461 '_PVT'
1462 );
1463 END lock_row;
1464 -------------------------------------------
1465 -- lock_row for:OKC_LINE_STYLE_SOURCES_V --
1466 -------------------------------------------
1467 PROCEDURE lock_row(
1468 p_api_version IN NUMBER,
1469 p_init_msg_list IN VARCHAR2 ,
1470 x_return_status OUT NOCOPY VARCHAR2,
1471 x_msg_count OUT NOCOPY NUMBER,
1472 x_msg_data OUT NOCOPY VARCHAR2,
1473 p_lssv_rec IN lssv_rec_type) IS
1474
1475 l_api_version CONSTANT NUMBER := 1;
1476 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1477 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1478 l_lss_rec lss_rec_type;
1479 BEGIN
1480 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1481 G_PKG_NAME,
1482 p_init_msg_list,
1483 l_api_version,
1484 p_api_version,
1485 '_PVT',
1486 x_return_status);
1487 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1488 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1489 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1490 RAISE OKC_API.G_EXCEPTION_ERROR;
1491 END IF;
1492 --------------------------------------
1493 -- Move VIEW record to "Child" records
1494 --------------------------------------
1495 migrate(p_lssv_rec, l_lss_rec);
1496 --------------------------------------------
1497 -- Call the LOCK_ROW for each child record
1498 --------------------------------------------
1499 lock_row(
1500 p_init_msg_list,
1501 x_return_status,
1502 x_msg_count,
1503 x_msg_data,
1504 l_lss_rec
1505 );
1506 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1507 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1508 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1509 RAISE OKC_API.G_EXCEPTION_ERROR;
1510 END IF;
1511 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1512 EXCEPTION
1513 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1514 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1515 (
1516 l_api_name,
1517 G_PKG_NAME,
1518 'OKC_API.G_RET_STS_ERROR',
1519 x_msg_count,
1520 x_msg_data,
1521 '_PVT'
1522 );
1523 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1524 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1525 (
1526 l_api_name,
1527 G_PKG_NAME,
1528 'OKC_API.G_RET_STS_UNEXP_ERROR',
1529 x_msg_count,
1530 x_msg_data,
1531 '_PVT'
1532 );
1533 WHEN OTHERS THEN
1534 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1535 (
1536 l_api_name,
1537 G_PKG_NAME,
1538 'OTHERS',
1539 x_msg_count,
1540 x_msg_data,
1541 '_PVT'
1542 );
1543 END lock_row;
1544 --------------------------------------
1545 -- PL/SQL TBL lock_row for:LSSV_TBL --
1546 --------------------------------------
1547 PROCEDURE lock_row(
1548 p_api_version IN NUMBER,
1549 p_init_msg_list IN VARCHAR2 ,
1550 x_return_status OUT NOCOPY VARCHAR2,
1551 x_msg_count OUT NOCOPY NUMBER,
1552 x_msg_data OUT NOCOPY VARCHAR2,
1553 p_lssv_tbl IN lssv_tbl_type) IS
1554
1555 l_api_version CONSTANT NUMBER := 1;
1556 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1557 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1558 i NUMBER := 0;
1559 BEGIN
1560 OKC_API.init_msg_list(p_init_msg_list);
1561 -- Make sure PL/SQL table has records in it before passing
1562 IF (p_lssv_tbl.COUNT > 0) THEN
1563 i := p_lssv_tbl.FIRST;
1564 LOOP
1565 lock_row (
1566 p_api_version => p_api_version,
1567 p_init_msg_list => OKC_API.G_FALSE,
1568 x_return_status => x_return_status,
1569 x_msg_count => x_msg_count,
1570 x_msg_data => x_msg_data,
1571 p_lssv_rec => p_lssv_tbl(i));
1572 EXIT WHEN (i = p_lssv_tbl.LAST);
1573 i := p_lssv_tbl.NEXT(i);
1574 END LOOP;
1575 END IF;
1576 EXCEPTION
1577 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1578 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1579 (
1580 l_api_name,
1581 G_PKG_NAME,
1582 'OKC_API.G_RET_STS_ERROR',
1583 x_msg_count,
1584 x_msg_data,
1585 '_PVT'
1586 );
1587 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1588 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1589 (
1590 l_api_name,
1591 G_PKG_NAME,
1592 'OKC_API.G_RET_STS_UNEXP_ERROR',
1593 x_msg_count,
1594 x_msg_data,
1595 '_PVT'
1596 );
1597 WHEN OTHERS THEN
1598 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1599 (
1600 l_api_name,
1601 G_PKG_NAME,
1602 'OTHERS',
1603 x_msg_count,
1604 x_msg_data,
1605 '_PVT'
1606 );
1607 END lock_row;
1608
1609 ---------------------------------------------------------------------------
1610 -- PROCEDURE update_row
1611 ---------------------------------------------------------------------------
1612 -------------------------------------------
1613 -- update_row for:OKC_LINE_STYLE_SOURCES --
1614 -------------------------------------------
1615 PROCEDURE update_row(
1616 p_init_msg_list IN VARCHAR2 ,
1617 x_return_status OUT NOCOPY VARCHAR2,
1618 x_msg_count OUT NOCOPY NUMBER,
1619 x_msg_data OUT NOCOPY VARCHAR2,
1620 p_lss_rec IN lss_rec_type,
1621 x_lss_rec OUT NOCOPY lss_rec_type) IS
1622
1623 l_api_version CONSTANT NUMBER := 1;
1624 l_api_name CONSTANT VARCHAR2(30) := 'SOURCES_update_row';
1625 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1626 l_lss_rec lss_rec_type := p_lss_rec;
1627 l_def_lss_rec lss_rec_type;
1628 l_row_notfound BOOLEAN := TRUE;
1629 ----------------------------------
1630 -- FUNCTION populate_new_record --
1631 ----------------------------------
1632 FUNCTION populate_new_record (
1633 p_lss_rec IN lss_rec_type,
1634 x_lss_rec OUT NOCOPY lss_rec_type
1635 ) RETURN VARCHAR2 IS
1636 l_lss_rec lss_rec_type;
1637 l_row_notfound BOOLEAN := TRUE;
1638 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1639 BEGIN
1640 x_lss_rec := p_lss_rec;
1641 -- Get current database values
1642 l_lss_rec := get_rec(p_lss_rec, l_row_notfound);
1643 IF (l_row_notfound) THEN
1644 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1645 END IF;
1646 IF (x_lss_rec.lse_id = OKC_API.G_MISS_NUM)
1647 THEN
1648 x_lss_rec.lse_id := l_lss_rec.lse_id;
1649 END IF;
1650 IF (x_lss_rec.jtot_object_code = OKC_API.G_MISS_CHAR)
1651 THEN
1652 x_lss_rec.jtot_object_code := l_lss_rec.jtot_object_code;
1653 END IF;
1654 IF (x_lss_rec.start_date = OKC_API.G_MISS_DATE)
1655 THEN
1656 x_lss_rec.start_date := l_lss_rec.start_date;
1657 END IF;
1658 IF (x_lss_rec.object_version_number = OKC_API.G_MISS_NUM)
1659 THEN
1660 x_lss_rec.object_version_number := l_lss_rec.object_version_number;
1661 END IF;
1662 IF (x_lss_rec.created_by = OKC_API.G_MISS_NUM)
1663 THEN
1664 x_lss_rec.created_by := l_lss_rec.created_by;
1665 END IF;
1666 IF (x_lss_rec.creation_date = OKC_API.G_MISS_DATE)
1667 THEN
1668 x_lss_rec.creation_date := l_lss_rec.creation_date;
1669 END IF;
1670 IF (x_lss_rec.last_updated_by = OKC_API.G_MISS_NUM)
1671 THEN
1672 x_lss_rec.last_updated_by := l_lss_rec.last_updated_by;
1673 END IF;
1674 IF (x_lss_rec.last_update_date = OKC_API.G_MISS_DATE)
1675 THEN
1676 x_lss_rec.last_update_date := l_lss_rec.last_update_date;
1677 END IF;
1678 IF (x_lss_rec.end_date = OKC_API.G_MISS_DATE)
1679 THEN
1680 x_lss_rec.end_date := l_lss_rec.end_date;
1681 END IF;
1682 IF (x_lss_rec.last_update_login = OKC_API.G_MISS_NUM)
1683 THEN
1684 x_lss_rec.last_update_login := l_lss_rec.last_update_login;
1685 END IF;
1686 IF (x_lss_rec.access_level = OKC_API.G_MISS_CHAR)
1687 THEN
1688 x_lss_rec.access_level := l_lss_rec.access_level;
1689 END IF;
1690 RETURN(l_return_status);
1691 END populate_new_record;
1692 -----------------------------------------------
1693 -- Set_Attributes for:OKC_LINE_STYLE_SOURCES --
1694 -----------------------------------------------
1695 FUNCTION Set_Attributes (
1696 p_lss_rec IN lss_rec_type,
1697 x_lss_rec OUT NOCOPY lss_rec_type
1698 ) RETURN VARCHAR2 IS
1699 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1700 BEGIN
1701 x_lss_rec := p_lss_rec;
1702 RETURN(l_return_status);
1703 END Set_Attributes;
1704 BEGIN
1705 IF (l_debug = 'Y') THEN
1706 okc_debug.set_indentation('update_row');
1707 okc_debug.log('200:in update row',1);
1708 END IF;
1709 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1710 p_init_msg_list,
1711 '_PVT',
1712 x_return_status);
1713 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1714 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1715 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1716 RAISE OKC_API.G_EXCEPTION_ERROR;
1717 END IF;
1718 --- Setting item attributes
1719 l_return_status := Set_Attributes(
1720 p_lss_rec, -- IN
1721 l_lss_rec); -- OUT
1722 --- If any errors happen abort API
1723 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1724 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1725 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1726 RAISE OKC_API.G_EXCEPTION_ERROR;
1727 END IF;
1728 l_return_status := populate_new_record(l_lss_rec, l_def_lss_rec);
1729 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1730 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1731 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1732 RAISE OKC_API.G_EXCEPTION_ERROR;
1733 END IF;
1734 UPDATE OKC_LINE_STYLE_SOURCES
1735 SET START_DATE = l_def_lss_rec.start_date,
1736 OBJECT_VERSION_NUMBER = l_def_lss_rec.object_version_number,
1737 CREATED_BY = l_def_lss_rec.created_by,
1738 CREATION_DATE = l_def_lss_rec.creation_date,
1739 LAST_UPDATED_BY = l_def_lss_rec.last_updated_by,
1740 LAST_UPDATE_DATE = l_def_lss_rec.last_update_date,
1741 END_DATE = l_def_lss_rec.end_date,
1742 LAST_UPDATE_LOGIN = l_def_lss_rec.last_update_login,
1743 ACCESS_LEVEL = l_def_lss_rec.access_level
1744 WHERE LSE_ID = l_def_lss_rec.lse_id
1745 AND JTOT_OBJECT_CODE = l_def_lss_rec.jtot_object_code;
1746
1747 x_lss_rec := l_def_lss_rec;
1748 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1749 EXCEPTION
1750 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1751 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1752 (
1753 l_api_name,
1754 G_PKG_NAME,
1755 'OKC_API.G_RET_STS_ERROR',
1756 x_msg_count,
1757 x_msg_data,
1758 '_PVT'
1759 );
1760 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1761 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1762 (
1763 l_api_name,
1764 G_PKG_NAME,
1765 'OKC_API.G_RET_STS_UNEXP_ERROR',
1766 x_msg_count,
1767 x_msg_data,
1768 '_PVT'
1769 );
1770 WHEN OTHERS THEN
1771 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1772 (
1773 l_api_name,
1774 G_PKG_NAME,
1775 'OTHERS',
1776 x_msg_count,
1777 x_msg_data,
1778 '_PVT'
1779 );
1780 IF (l_debug = 'Y') THEN
1781 okc_debug.reset_indentation;
1782 END IF;
1783 END update_row;
1784 ---------------------------------------------
1785 -- update_row for:OKC_LINE_STYLE_SOURCES_V --
1786 ---------------------------------------------
1787 PROCEDURE update_row(
1788 p_api_version IN NUMBER,
1789 p_init_msg_list IN VARCHAR2 ,
1790 x_return_status OUT NOCOPY VARCHAR2,
1791 x_msg_count OUT NOCOPY NUMBER,
1792 x_msg_data OUT NOCOPY VARCHAR2,
1793 p_lssv_rec IN lssv_rec_type,
1794 x_lssv_rec OUT NOCOPY lssv_rec_type) IS
1795
1796 l_api_version CONSTANT NUMBER := 1;
1797 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1798 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1799 l_lssv_rec lssv_rec_type := p_lssv_rec;
1800 l_def_lssv_rec lssv_rec_type;
1801 l_lss_rec lss_rec_type;
1802 lx_lss_rec lss_rec_type;
1803 -------------------------------
1804 -- FUNCTION fill_who_columns --
1805 -------------------------------
1806 FUNCTION fill_who_columns (
1807 p_lssv_rec IN lssv_rec_type
1808 ) RETURN lssv_rec_type IS
1809 l_lssv_rec lssv_rec_type := p_lssv_rec;
1810 BEGIN
1811 l_lssv_rec.LAST_UPDATE_DATE := SYSDATE;
1812 l_lssv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1813 l_lssv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1814 RETURN(l_lssv_rec);
1815 END fill_who_columns;
1816 ----------------------------------
1817 -- FUNCTION populate_new_record --
1818 ----------------------------------
1819 FUNCTION populate_new_record (
1820 p_lssv_rec IN lssv_rec_type,
1821 x_lssv_rec OUT NOCOPY lssv_rec_type
1822 ) RETURN VARCHAR2 IS
1823 l_lssv_rec lssv_rec_type;
1824 l_row_notfound BOOLEAN := TRUE;
1825 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1826 BEGIN
1827 x_lssv_rec := p_lssv_rec;
1828 -- Get current database values
1829 l_lssv_rec := get_rec(p_lssv_rec, l_row_notfound);
1830 IF (l_row_notfound) THEN
1831 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1832 END IF;
1833 IF (x_lssv_rec.lse_id = OKC_API.G_MISS_NUM)
1834 THEN
1835 x_lssv_rec.lse_id := l_lssv_rec.lse_id;
1836 END IF;
1837 IF (x_lssv_rec.jtot_object_code = OKC_API.G_MISS_CHAR)
1838 THEN
1839 x_lssv_rec.jtot_object_code := l_lssv_rec.jtot_object_code;
1840 END IF;
1841 IF (x_lssv_rec.object_version_number = OKC_API.G_MISS_NUM)
1842 THEN
1843 x_lssv_rec.object_version_number := l_lssv_rec.object_version_number;
1844 END IF;
1845 IF (x_lssv_rec.start_date = OKC_API.G_MISS_DATE)
1846 THEN
1847 x_lssv_rec.start_date := l_lssv_rec.start_date;
1848 END IF;
1849 IF (x_lssv_rec.end_date = OKC_API.G_MISS_DATE)
1850 THEN
1851 x_lssv_rec.end_date := l_lssv_rec.end_date;
1852 END IF;
1853 IF (x_lssv_rec.created_by = OKC_API.G_MISS_NUM)
1854 THEN
1855 x_lssv_rec.created_by := l_lssv_rec.created_by;
1856 END IF;
1857 IF (x_lssv_rec.creation_date = OKC_API.G_MISS_DATE)
1858 THEN
1859 x_lssv_rec.creation_date := l_lssv_rec.creation_date;
1860 END IF;
1861 IF (x_lssv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1862 THEN
1863 x_lssv_rec.last_updated_by := l_lssv_rec.last_updated_by;
1864 END IF;
1865 IF (x_lssv_rec.last_update_date = OKC_API.G_MISS_DATE)
1866 THEN
1867 x_lssv_rec.last_update_date := l_lssv_rec.last_update_date;
1868 END IF;
1869 IF (x_lssv_rec.last_update_login = OKC_API.G_MISS_NUM)
1870 THEN
1871 x_lssv_rec.last_update_login := l_lssv_rec.last_update_login;
1872 END IF;
1873 IF (x_lssv_rec.access_level = OKC_API.G_MISS_CHAR)
1874 THEN
1875 x_lssv_rec.access_level := l_lssv_rec.access_level;
1876 END IF;
1877 RETURN(l_return_status);
1878 END populate_new_record;
1879 -------------------------------------------------
1880 -- Set_Attributes for:OKC_LINE_STYLE_SOURCES_V --
1881 -------------------------------------------------
1882 FUNCTION Set_Attributes (
1883 p_lssv_rec IN lssv_rec_type,
1884 x_lssv_rec OUT NOCOPY lssv_rec_type
1885 ) RETURN VARCHAR2 IS
1886 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1887 BEGIN
1888 x_lssv_rec := p_lssv_rec;
1889 x_lssv_rec.OBJECT_VERSION_NUMBER := NVL(x_lssv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1890 RETURN(l_return_status);
1891 END Set_Attributes;
1892 BEGIN
1893 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1894 G_PKG_NAME,
1895 p_init_msg_list,
1896 l_api_version,
1897 p_api_version,
1898 '_PVT',
1899 x_return_status);
1900 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1901 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1902 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1903 RAISE OKC_API.G_EXCEPTION_ERROR;
1904 END IF;
1905 --- Setting item attributes
1906 l_return_status := Set_Attributes(
1907 p_lssv_rec, -- IN
1908 l_lssv_rec); -- OUT
1909 --- If any errors happen abort API
1910 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1911 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1912 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1913 RAISE OKC_API.G_EXCEPTION_ERROR;
1914 END IF;
1915 l_return_status := populate_new_record(l_lssv_rec, l_def_lssv_rec);
1916 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1917 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1918 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1919 RAISE OKC_API.G_EXCEPTION_ERROR;
1920 END IF;
1921 l_def_lssv_rec := fill_who_columns(l_def_lssv_rec);
1922 --- Validate all non-missing attributes (Item Level Validation)
1923 l_return_status := Validate_Attributes(l_def_lssv_rec);
1924 --- If any errors happen abort API
1925 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1926 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1927 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1928 RAISE OKC_API.G_EXCEPTION_ERROR;
1929 END IF;
1930 l_return_status := Validate_Record(l_def_lssv_rec);
1931 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1932 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1933 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1934 RAISE OKC_API.G_EXCEPTION_ERROR;
1935 END IF;
1936
1937 --------------------------------------
1938 -- Move VIEW record to "Child" records
1939 --------------------------------------
1940 migrate(l_def_lssv_rec, l_lss_rec);
1941 --------------------------------------------
1942 -- Call the UPDATE_ROW for each child record
1943 --------------------------------------------
1944 update_row(
1945 p_init_msg_list,
1946 x_return_status,
1947 x_msg_count,
1948 x_msg_data,
1949 l_lss_rec,
1950 lx_lss_rec
1951 );
1952 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1953 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1954 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1955 RAISE OKC_API.G_EXCEPTION_ERROR;
1956 END IF;
1957 migrate(lx_lss_rec, l_def_lssv_rec);
1958 x_lssv_rec := l_def_lssv_rec;
1959 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1960 EXCEPTION
1961 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1962 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1963 (
1964 l_api_name,
1965 G_PKG_NAME,
1966 'OKC_API.G_RET_STS_ERROR',
1967 x_msg_count,
1968 x_msg_data,
1969 '_PVT'
1970 );
1971 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1972 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1973 (
1974 l_api_name,
1975 G_PKG_NAME,
1976 'OKC_API.G_RET_STS_UNEXP_ERROR',
1977 x_msg_count,
1978 x_msg_data,
1979 '_PVT'
1980 );
1981 WHEN OTHERS THEN
1982 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1983 (
1984 l_api_name,
1985 G_PKG_NAME,
1986 'OTHERS',
1987 x_msg_count,
1988 x_msg_data,
1989 '_PVT'
1990 );
1991 END update_row;
1992 ----------------------------------------
1993 -- PL/SQL TBL update_row for:LSSV_TBL --
1994 ----------------------------------------
1995 PROCEDURE update_row(
1996 p_api_version IN NUMBER,
1997 p_init_msg_list IN VARCHAR2 ,
1998 x_return_status OUT NOCOPY VARCHAR2,
1999 x_msg_count OUT NOCOPY NUMBER,
2000 x_msg_data OUT NOCOPY VARCHAR2,
2001 p_lssv_tbl IN lssv_tbl_type,
2002 x_lssv_tbl OUT NOCOPY lssv_tbl_type) IS
2003
2004 l_api_version CONSTANT NUMBER := 1;
2005 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2006 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2007 i NUMBER := 0;
2008 BEGIN
2009 OKC_API.init_msg_list(p_init_msg_list);
2010 -- Make sure PL/SQL table has records in it before passing
2011 IF (p_lssv_tbl.COUNT > 0) THEN
2012 i := p_lssv_tbl.FIRST;
2013 LOOP
2014 update_row (
2015 p_api_version => p_api_version,
2016 p_init_msg_list => OKC_API.G_FALSE,
2017 x_return_status => x_return_status,
2018 x_msg_count => x_msg_count,
2019 x_msg_data => x_msg_data,
2020 p_lssv_rec => p_lssv_tbl(i),
2021 x_lssv_rec => x_lssv_tbl(i));
2022 EXIT WHEN (i = p_lssv_tbl.LAST);
2023 i := p_lssv_tbl.NEXT(i);
2024 END LOOP;
2025 END IF;
2026 EXCEPTION
2027 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2028 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2029 (
2030 l_api_name,
2031 G_PKG_NAME,
2032 'OKC_API.G_RET_STS_ERROR',
2033 x_msg_count,
2034 x_msg_data,
2035 '_PVT'
2036 );
2037 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2038 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2039 (
2040 l_api_name,
2041 G_PKG_NAME,
2042 'OKC_API.G_RET_STS_UNEXP_ERROR',
2043 x_msg_count,
2044 x_msg_data,
2045 '_PVT'
2046 );
2047 WHEN OTHERS THEN
2048 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2049 (
2050 l_api_name,
2051 G_PKG_NAME,
2052 'OTHERS',
2053 x_msg_count,
2054 x_msg_data,
2055 '_PVT'
2056 );
2057 END update_row;
2058
2059 ---------------------------------------------------------------------------
2060 -- PROCEDURE delete_row
2061 ---------------------------------------------------------------------------
2062 -------------------------------------------
2063 -- delete_row for:OKC_LINE_STYLE_SOURCES --
2064 -------------------------------------------
2065 PROCEDURE delete_row(
2066 p_init_msg_list IN VARCHAR2 ,
2067 x_return_status OUT NOCOPY VARCHAR2,
2068 x_msg_count OUT NOCOPY NUMBER,
2069 x_msg_data OUT NOCOPY VARCHAR2,
2070 p_lss_rec IN lss_rec_type) IS
2071
2072 l_api_version CONSTANT NUMBER := 1;
2073 l_api_name CONSTANT VARCHAR2(30) := 'SOURCES_delete_row';
2074 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2075 l_lss_rec lss_rec_type:= p_lss_rec;
2076 l_row_notfound BOOLEAN := TRUE;
2077 BEGIN
2078 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2079 p_init_msg_list,
2080 '_PVT',
2081 x_return_status);
2082 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2083 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2084 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2085 RAISE OKC_API.G_EXCEPTION_ERROR;
2086 END IF;
2087 DELETE FROM OKC_LINE_STYLE_SOURCES
2088 WHERE LSE_ID = l_lss_rec.lse_id AND
2089 JTOT_OBJECT_CODE = l_lss_rec.jtot_object_code;
2090
2091 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2092 EXCEPTION
2093 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2094 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2095 (
2096 l_api_name,
2097 G_PKG_NAME,
2098 'OKC_API.G_RET_STS_ERROR',
2099 x_msg_count,
2100 x_msg_data,
2101 '_PVT'
2102 );
2103 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2104 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2105 (
2106 l_api_name,
2107 G_PKG_NAME,
2108 'OKC_API.G_RET_STS_UNEXP_ERROR',
2109 x_msg_count,
2110 x_msg_data,
2111 '_PVT'
2112 );
2113 WHEN OTHERS THEN
2114 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2115 (
2116 l_api_name,
2117 G_PKG_NAME,
2118 'OTHERS',
2119 x_msg_count,
2120 x_msg_data,
2121 '_PVT'
2122 );
2123 END delete_row;
2124 ---------------------------------------------
2125 -- delete_row for:OKC_LINE_STYLE_SOURCES_V --
2126 ---------------------------------------------
2127 PROCEDURE delete_row(
2128 p_api_version IN NUMBER,
2129 p_init_msg_list IN VARCHAR2 ,
2130 x_return_status OUT NOCOPY VARCHAR2,
2131 x_msg_count OUT NOCOPY NUMBER,
2132 x_msg_data OUT NOCOPY VARCHAR2,
2133 p_lssv_rec IN lssv_rec_type) IS
2134
2135 l_api_version CONSTANT NUMBER := 1;
2136 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2137 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2138 l_lssv_rec lssv_rec_type := p_lssv_rec;
2139 l_lss_rec lss_rec_type;
2140 BEGIN
2141 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2142 G_PKG_NAME,
2143 p_init_msg_list,
2144 l_api_version,
2145 p_api_version,
2146 '_PVT',
2147 x_return_status);
2148 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2149 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2150 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2151 RAISE OKC_API.G_EXCEPTION_ERROR;
2152 END IF;
2153 --------------------------------------
2154 -- Move VIEW record to "Child" records
2155 --------------------------------------
2156 migrate(l_lssv_rec, l_lss_rec);
2157 --------------------------------------------
2158 -- Call the DELETE_ROW for each child record
2159 --------------------------------------------
2160 delete_row(
2161 p_init_msg_list,
2162 x_return_status,
2163 x_msg_count,
2164 x_msg_data,
2165 l_lss_rec
2166 );
2167 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2168 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2169 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2170 RAISE OKC_API.G_EXCEPTION_ERROR;
2171 END IF;
2172 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2173 EXCEPTION
2174 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2175 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2176 (
2177 l_api_name,
2178 G_PKG_NAME,
2179 'OKC_API.G_RET_STS_ERROR',
2180 x_msg_count,
2181 x_msg_data,
2182 '_PVT'
2183 );
2184 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2185 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2186 (
2187 l_api_name,
2188 G_PKG_NAME,
2189 'OKC_API.G_RET_STS_UNEXP_ERROR',
2190 x_msg_count,
2191 x_msg_data,
2192 '_PVT'
2193 );
2194 WHEN OTHERS THEN
2195 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2196 (
2197 l_api_name,
2198 G_PKG_NAME,
2199 'OTHERS',
2200 x_msg_count,
2201 x_msg_data,
2202 '_PVT'
2203 );
2204 END delete_row;
2205 ----------------------------------------
2206 -- PL/SQL TBL delete_row for:LSSV_TBL --
2207 ----------------------------------------
2208 PROCEDURE delete_row(
2209 p_api_version IN NUMBER,
2210 p_init_msg_list IN VARCHAR2 ,
2211 x_return_status OUT NOCOPY VARCHAR2,
2212 x_msg_count OUT NOCOPY NUMBER,
2213 x_msg_data OUT NOCOPY VARCHAR2,
2214 p_lssv_tbl IN lssv_tbl_type) IS
2215
2216 l_api_version CONSTANT NUMBER := 1;
2217 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2218 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2219 i NUMBER := 0;
2220 BEGIN
2221 OKC_API.init_msg_list(p_init_msg_list);
2222 -- Make sure PL/SQL table has records in it before passing
2223 IF (p_lssv_tbl.COUNT > 0) THEN
2224 i := p_lssv_tbl.FIRST;
2225 LOOP
2226 delete_row (
2227 p_api_version => p_api_version,
2228 p_init_msg_list => OKC_API.G_FALSE,
2229 x_return_status => x_return_status,
2230 x_msg_count => x_msg_count,
2231 x_msg_data => x_msg_data,
2232 p_lssv_rec => p_lssv_tbl(i));
2233 EXIT WHEN (i = p_lssv_tbl.LAST);
2234 i := p_lssv_tbl.NEXT(i);
2235 END LOOP;
2236 END IF;
2237 EXCEPTION
2238 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2239 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2240 (
2241 l_api_name,
2242 G_PKG_NAME,
2243 'OKC_API.G_RET_STS_ERROR',
2244 x_msg_count,
2245 x_msg_data,
2246 '_PVT'
2247 );
2248 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2249 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2250 (
2251 l_api_name,
2252 G_PKG_NAME,
2253 'OKC_API.G_RET_STS_UNEXP_ERROR',
2254 x_msg_count,
2255 x_msg_data,
2256 '_PVT'
2257 );
2258 WHEN OTHERS THEN
2259 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2260 (
2261 l_api_name,
2262 G_PKG_NAME,
2263 'OTHERS',
2264 x_msg_count,
2265 x_msg_data,
2266 '_PVT'
2267 );
2268 END delete_row;
2269 END OKC_LSS_PVT;