[Home] [Help]
PACKAGE BODY: APPS.OKC_MRD_PVT
Source
1 PACKAGE BODY OKC_MRD_PVT AS
2 /* $Header: OKCSMRDB.pls 120.0 2005/05/26 09:42:38 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 G_EXCEPTION_HALT_VALIDATION exception;
7 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
8 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
9 G_VIEW CONSTANT VARCHAR2(200) := 'OKC_MASSCHANGE_REQ_DTLS_V';
10 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
11 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
12
13 -- Start of comments
14 --
15 -- Procedure Name : validate_oie_id
16 -- Description :
17 -- Business Rules :
18 -- Parameters :
19 -- Version : 1.0
20 -- End of comments
21 PROCEDURE validate_oie_id(x_return_status OUT NOCOPY VARCHAR2,
22 p_mrdv_rec IN mrdv_rec_type) is
23 l_dummy_var VARCHAR2(1) := '?';
24 Cursor l_oiev_csr Is
25 select 'x'
26 from OKC_OPERATION_INSTANCES
27 where id = p_mrdv_rec.oie_id;
28
29 Begin
30 -- initialize return status
31 x_return_status := OKC_API.G_RET_STS_SUCCESS;
32
33 -- check that data exists
34 If (p_mrdv_rec.oie_id <> OKC_API.G_MISS_NUM and
35 p_mrdv_rec.oie_id IS NOT NULL)
36 Then
37 Open l_oiev_csr;
38 Fetch l_oiev_csr Into l_dummy_var;
39 Close l_oiev_csr;
40 -- if l_dummy_var still set to default, data was not found
41 If (l_dummy_var = '?') Then
42 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
43 p_msg_name => g_no_parent_record,
44 p_token1 => g_col_name_token,
45 p_token1_value => 'oie_id',
46 p_token2 => g_child_table_token,
47 p_token2_value => G_VIEW,
48 p_token3 => g_parent_table_token,
49 p_token3_value => 'OKC_OPERATION_INSTANCES');
50 -- notify caller of an error
51 x_return_status := OKC_API.G_RET_STS_ERROR;
52 End If;
53 End If;
54
55 exception
56 when G_EXCEPTION_HALT_VALIDATION then
57 -- no processing necessary; validation can continue with next column
58 null;
59
60 when OTHERS then
61 -- store SQL error message on message stack
62 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
63 p_msg_name => g_unexpected_error,
64 p_token1 => g_sqlcode_token,
65 p_token1_value => sqlcode,
66 p_token2 => g_sqlerrm_token,
67 p_token2_value => sqlerrm);
68
69 -- notify caller of an error as UNEXPETED error
70 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
71 -- verify that cursor was closed
72 if l_oiev_csr%ISOPEN then
73 close l_oiev_csr;
74 end if;
75 End validate_oie_id;
76
77 -- Start of comments
78 --
79 -- Procedure Name : validate_ole_id
80 -- Description :
81 -- Business Rules :
82 -- Parameters :
83 -- Version : 1.0
84 -- End of comments
85 PROCEDURE validate_ole_id(x_return_status OUT NOCOPY VARCHAR2,
86 p_mrdv_rec IN mrdv_rec_type) is
87 l_dummy_var VARCHAR2(1) := '?';
88 Cursor l_olev_csr Is
89 select 'x'
90 from OKC_OPERATION_LINES
91 where id = p_mrdv_rec.ole_id;
92
93 Begin
94 -- initialize return status
95 x_return_status := OKC_API.G_RET_STS_SUCCESS;
96 /*
97 -- commented to reslove bug 1790999
98 -- this is a nullable column
99 --
100 -- check that data exists
101 If (p_mrdv_rec.ole_id = OKC_API.G_MISS_NUM or
102 p_mrdv_rec.ole_id IS NULL)
103 Then
104 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
105 p_msg_name => g_required_value,
106 p_token1 => g_col_name_token,
107 p_token1_value => 'ole_id');
108 -- notify caller of an error
109 x_return_status := OKC_API.G_RET_STS_ERROR;
110
111 -- halt validation
112 raise G_EXCEPTION_HALT_VALIDATION;
113 End If;
114 */
115 -- foreign key validation
116 If (p_mrdv_rec.ole_id <> OKC_API.G_MISS_NUM and
117 p_mrdv_rec.ole_id IS NOT NULL)
118 Then
119 Open l_olev_csr;
120 Fetch l_olev_csr Into l_dummy_var;
121 Close l_olev_csr;
122 -- if l_dummy_var still set to default, data was not found
123 If (l_dummy_var = '?') Then
124 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
125 p_msg_name => g_no_parent_record,
126 p_token1 => g_col_name_token,
127 p_token1_value => 'ole_id',
128 p_token2 => g_child_table_token,
129 p_token2_value => G_VIEW,
130 p_token3 => g_parent_table_token,
131 p_token3_value => 'OKC_OPERATION_LINES');
132 -- notify caller of an error
133 x_return_status := OKC_API.G_RET_STS_ERROR;
134 End If;
135 End If;
136
137 exception
138 when G_EXCEPTION_HALT_VALIDATION then
139 -- no processing necessary; validation can continue with next column
140 null;
141
142 when OTHERS then
143 -- store SQL error message on message stack
144 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
145 p_msg_name => g_unexpected_error,
146 p_token1 => g_sqlcode_token,
147 p_token1_value => sqlcode,
148 p_token2 => g_sqlerrm_token,
149 p_token2_value => sqlerrm);
150
151 -- notify caller of an error as UNEXPETED error
152 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
153 -- verify that cursor was closed
154 if l_olev_csr%ISOPEN then
155 close l_olev_csr;
156 end if;
157 End validate_ole_id;
158
159 -- Start of comments
160 --
161 -- Procedure Name : validate_attr_name
162 -- Description :
163 -- Business Rules :
164 -- Parameters :
165 -- Version : 1.0
166 -- End of comments
167 PROCEDURE validate_attr_name(x_return_status OUT NOCOPY VARCHAR2,
168 p_mrdv_rec IN mrdv_rec_type) is
169 Begin
170 -- initialize return status
171 x_return_status := OKC_API.G_RET_STS_SUCCESS;
172 If (p_mrdv_rec.attribute_name = OKC_API.G_MISS_CHAR or
173 p_mrdv_rec.attribute_name IS NULL)
174 Then
175 --set error message in message stack
176 OKC_API.SET_MESSAGE(
177 p_app_name => G_APP_NAME,
178 p_msg_name => G_REQUIRED_VALUE,
179 p_token1 => G_COL_NAME_TOKEN,
180 p_token1_value => 'Attribute Name');
181 -- notify caller of an error
182 x_return_status := OKC_API.G_RET_STS_ERROR;
183
184 -- halt validation
185 raise G_EXCEPTION_HALT_VALIDATION;
186 End If;
187
188 -- enforce foreign key if data exists
189 If (p_mrdv_rec.attribute_name <> OKC_API.G_MISS_CHAR and
190 p_mrdv_rec.attribute_name IS NOT NULL)
191 Then
192 -- Check if the value is a valid code from lookup table
193 x_return_status := OKC_UTIL.check_lookup_code('OKS_MASS_CHANGE_ATTRIBUTE',
194 p_mrdv_rec.attribute_name);
195 If (x_return_status = OKC_API.G_RET_STS_ERROR) Then
196 --set error message in message stack
197 OKC_API.SET_MESSAGE(
198 p_app_name => G_APP_NAME,
199 p_msg_name => G_REQUIRED_VALUE,
200 p_token1 => G_COL_NAME_TOKEN,
201 p_token1_value => 'Attribute Name');
202 raise G_EXCEPTION_HALT_VALIDATION;
203 Elsif (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
204 raise G_EXCEPTION_HALT_VALIDATION;
205 End If;
206 End If;
207 exception
208 when OTHERS then
209 -- store SQL error message on message stack
210 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
211 p_msg_name => g_unexpected_error,
212 p_token1 => g_sqlcode_token,
213 p_token1_value => sqlcode,
214 p_token2 => g_sqlerrm_token,
215 p_token2_value => sqlerrm);
216 -- notify caller of an error as UNEXPETED error
217 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
218 End validate_attr_name;
219
220 ---------------------------------------------------------------------------
221 -- FUNCTION get_seq_id
222 ---------------------------------------------------------------------------
223 FUNCTION get_seq_id RETURN NUMBER IS
224 BEGIN
225 RETURN(okc_p_util.raw_to_number(sys_guid()));
226 END get_seq_id;
227
228 ---------------------------------------------------------------------------
229 -- PROCEDURE qc
230 ---------------------------------------------------------------------------
231 PROCEDURE qc IS
232 BEGIN
233 null;
234 END qc;
235
236 ---------------------------------------------------------------------------
237 -- PROCEDURE change_version
238 ---------------------------------------------------------------------------
239 PROCEDURE change_version IS
240 BEGIN
241 null;
242 END change_version;
243
244 ---------------------------------------------------------------------------
245 -- PROCEDURE api_copy
246 ---------------------------------------------------------------------------
247 PROCEDURE api_copy IS
248 BEGIN
249 null;
250 END api_copy;
251
252 ---------------------------------------------------------------------------
253 -- FUNCTION get_rec for: OKC_MASSCHANGE_REQ_DTLS
254 ---------------------------------------------------------------------------
255 FUNCTION get_rec (
256 p_mrd_rec IN mrd_rec_type,
257 x_no_data_found OUT NOCOPY BOOLEAN
258 ) RETURN mrd_rec_type IS
259 CURSOR mrd_pk_csr (p_id IN NUMBER) IS
260 SELECT
261 ID,
262 OIE_ID,
263 OLE_ID,
264 ATTRIBUTE_NAME,
265 OLD_VALUE,
266 NEW_VALUE,
267 OBJECT_VERSION_NUMBER,
268 CREATED_BY,
269 CREATION_DATE,
270 LAST_UPDATED_BY,
271 LAST_UPDATE_DATE,
272 LAST_UPDATE_LOGIN
273 FROM Okc_Masschange_Req_Dtls
274 WHERE okc_masschange_req_dtls.id = p_id;
275 l_mrd_pk mrd_pk_csr%ROWTYPE;
276 l_mrd_rec mrd_rec_type;
277 BEGIN
278 x_no_data_found := TRUE;
279 -- Get current database values
280 OPEN mrd_pk_csr (p_mrd_rec.id);
281 FETCH mrd_pk_csr INTO
282 l_mrd_rec.ID,
283 l_mrd_rec.OIE_ID,
284 l_mrd_rec.OLE_ID,
285 l_mrd_rec.ATTRIBUTE_NAME,
286 l_mrd_rec.OLD_VALUE,
287 l_mrd_rec.NEW_VALUE,
288 l_mrd_rec.OBJECT_VERSION_NUMBER,
289 l_mrd_rec.CREATED_BY,
290 l_mrd_rec.CREATION_DATE,
291 l_mrd_rec.LAST_UPDATED_BY,
292 l_mrd_rec.LAST_UPDATE_DATE,
293 l_mrd_rec.LAST_UPDATE_LOGIN;
294 x_no_data_found := mrd_pk_csr%NOTFOUND;
295 CLOSE mrd_pk_csr;
296 RETURN(l_mrd_rec);
297 END get_rec;
298
299 FUNCTION get_rec (
300 p_mrd_rec IN mrd_rec_type
301 ) RETURN mrd_rec_type IS
302 l_row_notfound BOOLEAN := TRUE;
303 BEGIN
304 RETURN(get_rec(p_mrd_rec, l_row_notfound));
305 END get_rec;
306 ---------------------------------------------------------------------------
307 -- FUNCTION get_rec for: OKC_MASSCHANGE_REQ_DTLS_V
308 ---------------------------------------------------------------------------
309 FUNCTION get_rec (
310 p_mrdv_rec IN mrdv_rec_type,
311 x_no_data_found OUT NOCOPY BOOLEAN
312 ) RETURN mrdv_rec_type IS
313 CURSOR omrv_pk_csr (p_id IN VARCHAR2) IS
314 SELECT
315 ID,
316 OIE_ID,
317 OLE_ID,
318 ATTRIBUTE_NAME,
319 OLD_VALUE,
320 NEW_VALUE,
321 OBJECT_VERSION_NUMBER,
322 CREATED_BY,
323 CREATION_DATE,
324 LAST_UPDATED_BY,
325 LAST_UPDATE_DATE,
326 LAST_UPDATE_LOGIN
327 FROM Okc_Masschange_Req_Dtls_V
328 WHERE okc_masschange_req_dtls_v.id = p_id;
329 l_omrv_pk omrv_pk_csr%ROWTYPE;
330 l_mrdv_rec mrdv_rec_type;
331 BEGIN
332 x_no_data_found := TRUE;
333 -- Get current database values
334 OPEN omrv_pk_csr (p_mrdv_rec.id);
335 FETCH omrv_pk_csr INTO
336 l_mrdv_rec.ID,
337 l_mrdv_rec.OIE_ID,
338 l_mrdv_rec.OLE_ID,
339 l_mrdv_rec.ATTRIBUTE_NAME,
340 l_mrdv_rec.OLD_VALUE,
341 l_mrdv_rec.NEW_VALUE,
342 l_mrdv_rec.OBJECT_VERSION_NUMBER,
343 l_mrdv_rec.CREATED_BY,
344 l_mrdv_rec.CREATION_DATE,
345 l_mrdv_rec.LAST_UPDATED_BY,
346 l_mrdv_rec.LAST_UPDATE_DATE,
347 l_mrdv_rec.LAST_UPDATE_LOGIN;
348 x_no_data_found := omrv_pk_csr%NOTFOUND;
349 CLOSE omrv_pk_csr;
350 RETURN(l_mrdv_rec);
351 END get_rec;
352
353 FUNCTION get_rec (
354 p_mrdv_rec IN mrdv_rec_type
355 ) RETURN mrdv_rec_type IS
356 l_row_notfound BOOLEAN := TRUE;
357 BEGIN
358 RETURN(get_rec(p_mrdv_rec, l_row_notfound));
359 END get_rec;
360
361 ---------------------------------------------------------------
362 -- FUNCTION null_out_defaults for: OKC_MASSCHANGE_REQ_DTLS_V --
363 ---------------------------------------------------------------
364 FUNCTION null_out_defaults (
365 p_mrdv_rec IN mrdv_rec_type
366 ) RETURN mrdv_rec_type IS
367 l_mrdv_rec mrdv_rec_type := p_mrdv_rec;
368 BEGIN
372 IF (l_mrdv_rec.ole_id = OKC_API.G_MISS_NUM) THEN
369 IF (l_mrdv_rec.oie_id = OKC_API.G_MISS_NUM) THEN
370 l_mrdv_rec.oie_id := NULL;
371 END IF;
373 l_mrdv_rec.ole_id := NULL;
374 END IF;
375 IF (l_mrdv_rec.attribute_name = OKC_API.G_MISS_CHAR) THEN
376 l_mrdv_rec.attribute_name := NULL;
377 END IF;
378 IF (l_mrdv_rec.old_value = OKC_API.G_MISS_CHAR) THEN
379 l_mrdv_rec.old_value := NULL;
380 END IF;
381 IF (l_mrdv_rec.new_value = OKC_API.G_MISS_CHAR) THEN
382 l_mrdv_rec.new_value := NULL;
383 END IF;
384 IF (l_mrdv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
385 l_mrdv_rec.object_version_number := NULL;
386 END IF;
387 IF (l_mrdv_rec.created_by = OKC_API.G_MISS_NUM) THEN
388 l_mrdv_rec.created_by := NULL;
389 END IF;
390 IF (l_mrdv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
391 l_mrdv_rec.creation_date := NULL;
392 END IF;
393 IF (l_mrdv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
394 l_mrdv_rec.last_updated_by := NULL;
395 END IF;
396 IF (l_mrdv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
397 l_mrdv_rec.last_update_date := NULL;
398 END IF;
399 IF (l_mrdv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
400 l_mrdv_rec.last_update_login := NULL;
401 END IF;
402 RETURN(l_mrdv_rec);
403 END null_out_defaults;
404 ---------------------------------------------------------------------------
405 -- PROCEDURE Validate_Attributes
406 ---------------------------------------------------------------------------
407 -------------------------------------------------------
408 -- Validate_Attributes for:OKC_MASSCHANGE_REQ_DTLS_V --
409 -------------------------------------------------------
410 FUNCTION Validate_Attributes (
411 p_mrdv_rec IN mrdv_rec_type
412 ) RETURN VARCHAR2 IS
413 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
414 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
415 BEGIN
416 validate_oie_id
417 (x_return_status => l_return_status,
418 p_mrdv_rec => p_mrdv_rec);
419
420 -- store the highest degree of error
421 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
422 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
423 x_return_status := l_return_status;
424 End If;
425 End If;
426
427 validate_ole_id
428 (x_return_status => l_return_status,
429 p_mrdv_rec => p_mrdv_rec);
430
431 -- store the highest degree of error
432 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
433 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
434 x_return_status := l_return_status;
435 End If;
436 End If;
437
438 validate_attr_name
439 (x_return_status => l_return_status,
440 p_mrdv_rec => p_mrdv_rec);
441
442 -- store the highest degree of error
443 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
444 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
445 x_return_status := l_return_status;
446 End If;
447 End If;
448
449 RETURN(x_return_status);
450
451 exception
452 when OTHERS then
453 -- store SQL error message on message stack
454 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
455 p_msg_name => g_unexpected_error,
456 p_token1 => g_sqlcode_token,
457 p_token1_value => sqlcode,
458 p_token2 => g_sqlerrm_token,
459 p_token2_value => sqlerrm);
460
461 -- notify caller of an UNEXPECTED error
462 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
463
464 -- return status to caller
465 RETURN(x_return_status);
466 END Validate_Attributes;
467
468 ---------------------------------------------------------------------------
469 -- PROCEDURE Validate_Record
470 ---------------------------------------------------------------------------
471 ---------------------------------------------------
472 -- Validate_Record for:OKC_MASSCHANGE_REQ_DTLS_V --
473 ---------------------------------------------------
474 FUNCTION Validate_Record (
475 p_mrdv_rec IN mrdv_rec_type
476 ) RETURN VARCHAR2 IS
477 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
478 BEGIN
479 RETURN (l_return_status);
480 END Validate_Record;
481
482 ---------------------------------------------------------------------------
483 -- PROCEDURE Migrate
484 ---------------------------------------------------------------------------
485 PROCEDURE migrate (
486 p_from IN mrdv_rec_type,
487 p_to IN OUT NOCOPY mrd_rec_type
488 ) IS
489 BEGIN
490 p_to.id := p_from.id;
491 p_to.oie_id := p_from.oie_id;
492 p_to.ole_id := p_from.ole_id;
493 p_to.attribute_name := p_from.attribute_name;
494 p_to.old_value := p_from.old_value;
495 p_to.new_value := p_from.new_value;
496 p_to.object_version_number := p_from.object_version_number;
497 p_to.created_by := p_from.created_by;
498 p_to.creation_date := p_from.creation_date;
499 p_to.last_updated_by := p_from.last_updated_by;
500 p_to.last_update_date := p_from.last_update_date;
501 p_to.last_update_login := p_from.last_update_login;
502 END migrate;
506 ) IS
503 PROCEDURE migrate (
504 p_from IN mrd_rec_type,
505 p_to IN OUT NOCOPY mrdv_rec_type
507 BEGIN
508 p_to.id := p_from.id;
509 p_to.oie_id := p_from.oie_id;
510 p_to.ole_id := p_from.ole_id;
511 p_to.attribute_name := p_from.attribute_name;
512 p_to.old_value := p_from.old_value;
513 p_to.new_value := p_from.new_value;
514 p_to.object_version_number := p_from.object_version_number;
515 p_to.created_by := p_from.created_by;
516 p_to.creation_date := p_from.creation_date;
517 p_to.last_updated_by := p_from.last_updated_by;
518 p_to.last_update_date := p_from.last_update_date;
519 p_to.last_update_login := p_from.last_update_login;
520 END migrate;
521
522 ---------------------------------------------------------------------------
523 -- PROCEDURE validate_row
524 ---------------------------------------------------------------------------
525 ------------------------------------------------
526 -- validate_row for:OKC_MASSCHANGE_REQ_DTLS_V --
527 ------------------------------------------------
528 PROCEDURE validate_row(
529 p_api_version IN NUMBER,
530 p_init_msg_list IN VARCHAR2 ,
531 x_return_status OUT NOCOPY VARCHAR2,
532 x_msg_count OUT NOCOPY NUMBER,
533 x_msg_data OUT NOCOPY VARCHAR2,
534 p_mrdv_rec IN mrdv_rec_type) IS
535
536 l_api_version CONSTANT NUMBER := 1;
537 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
538 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
539 l_mrdv_rec mrdv_rec_type := p_mrdv_rec;
540 l_mrd_rec mrd_rec_type;
541 BEGIN
542 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
543 G_PKG_NAME,
544 p_init_msg_list,
545 l_api_version,
546 p_api_version,
547 '_PVT',
548 x_return_status);
549 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
550 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
551 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
552 RAISE OKC_API.G_EXCEPTION_ERROR;
553 END IF;
554 --- Validate all non-missing attributes (Item Level Validation)
555 l_return_status := Validate_Attributes(l_mrdv_rec);
556 --- If any errors happen abort API
557 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
558 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
559 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
560 RAISE OKC_API.G_EXCEPTION_ERROR;
561 END IF;
562 l_return_status := Validate_Record(l_mrdv_rec);
563 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
564 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
565 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
566 RAISE OKC_API.G_EXCEPTION_ERROR;
567 END IF;
568 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
569 EXCEPTION
570 WHEN OKC_API.G_EXCEPTION_ERROR THEN
571 x_return_status := OKC_API.HANDLE_EXCEPTIONS
572 (
573 l_api_name,
574 G_PKG_NAME,
575 'OKC_API.G_RET_STS_ERROR',
576 x_msg_count,
577 x_msg_data,
578 '_PVT'
579 );
580 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
581 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
582 (
583 l_api_name,
584 G_PKG_NAME,
585 'OKC_API.G_RET_STS_UNEXP_ERROR',
586 x_msg_count,
587 x_msg_data,
588 '_PVT'
589 );
590 WHEN OTHERS THEN
591 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
592 (
593 l_api_name,
594 G_PKG_NAME,
595 'OTHERS',
596 x_msg_count,
597 x_msg_data,
598 '_PVT'
599 );
600 END validate_row;
601 ------------------------------------------
602 -- PL/SQL TBL validate_row for:MRDV_TBL --
603 ------------------------------------------
604 PROCEDURE validate_row(
605 p_api_version IN NUMBER,
606 p_init_msg_list IN VARCHAR2 ,
607 x_return_status OUT NOCOPY VARCHAR2,
608 x_msg_count OUT NOCOPY NUMBER,
609 x_msg_data OUT NOCOPY VARCHAR2,
610 p_mrdv_tbl IN mrdv_tbl_type) IS
611
612 l_api_version CONSTANT NUMBER := 1;
613 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
614 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
615 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
616 i NUMBER := 0;
617 BEGIN
618 OKC_API.init_msg_list(p_init_msg_list);
619 -- Make sure PL/SQL table has records in it before passing
620 IF (p_mrdv_tbl.COUNT > 0) THEN
621 i := p_mrdv_tbl.FIRST;
622 LOOP
623 validate_row (
624 p_api_version => p_api_version,
625 p_init_msg_list => OKC_API.G_FALSE,
629 p_mrdv_rec => p_mrdv_tbl(i));
626 x_return_status => x_return_status,
627 x_msg_count => x_msg_count,
628 x_msg_data => x_msg_data,
630 -- store the highest degree of error
631 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
632 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
633 l_overall_status := x_return_status;
634 End If;
635 End If;
636
637 EXIT WHEN (i = p_mrdv_tbl.LAST);
638 i := p_mrdv_tbl.NEXT(i);
639 END LOOP;
640 -- return overall status
641 x_return_status := l_overall_status;
642 END IF;
643 EXCEPTION
644 WHEN OKC_API.G_EXCEPTION_ERROR THEN
645 x_return_status := OKC_API.HANDLE_EXCEPTIONS
646 (
647 l_api_name,
648 G_PKG_NAME,
649 'OKC_API.G_RET_STS_ERROR',
650 x_msg_count,
651 x_msg_data,
652 '_PVT'
653 );
654 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
655 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
656 (
657 l_api_name,
658 G_PKG_NAME,
659 'OKC_API.G_RET_STS_UNEXP_ERROR',
660 x_msg_count,
661 x_msg_data,
662 '_PVT'
663 );
664 WHEN OTHERS THEN
665 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
666 (
667 l_api_name,
668 G_PKG_NAME,
669 'OTHERS',
670 x_msg_count,
671 x_msg_data,
672 '_PVT'
673 );
674 END validate_row;
675
676 ---------------------------------------------------------------------------
677 -- PROCEDURE insert_row
678 ---------------------------------------------------------------------------
679 --------------------------------------------
680 -- insert_row for:OKC_MASSCHANGE_REQ_DTLS --
681 --------------------------------------------
682 PROCEDURE insert_row(
683 p_init_msg_list IN VARCHAR2 ,
684 x_return_status OUT NOCOPY VARCHAR2,
685 x_msg_count OUT NOCOPY NUMBER,
686 x_msg_data OUT NOCOPY VARCHAR2,
687 p_mrd_rec IN mrd_rec_type,
688 x_mrd_rec OUT NOCOPY mrd_rec_type) IS
689
690 l_api_version CONSTANT NUMBER := 1;
691 l_api_name CONSTANT VARCHAR2(30) := 'DTLS_insert_row';
692 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
693 l_mrd_rec mrd_rec_type := p_mrd_rec;
694 l_def_mrd_rec mrd_rec_type;
695 ------------------------------------------------
696 -- Set_Attributes for:OKC_MASSCHANGE_REQ_DTLS --
697 ------------------------------------------------
698 FUNCTION Set_Attributes (
699 p_mrd_rec IN mrd_rec_type,
700 x_mrd_rec OUT NOCOPY mrd_rec_type
701 ) RETURN VARCHAR2 IS
702 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
703 BEGIN
704 x_mrd_rec := p_mrd_rec;
705 RETURN(l_return_status);
706 END Set_Attributes;
707 BEGIN
708 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
709 p_init_msg_list,
710 '_PVT',
711 x_return_status);
712 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
713 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
714 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
715 RAISE OKC_API.G_EXCEPTION_ERROR;
716 END IF;
717 --- Setting item attributes
718 l_return_status := Set_Attributes(
719 p_mrd_rec, -- IN
720 l_mrd_rec); -- OUT
721 --- If any errors happen abort API
722 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
723 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
724 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
725 RAISE OKC_API.G_EXCEPTION_ERROR;
726 END IF;
727 INSERT INTO OKC_MASSCHANGE_REQ_DTLS(
728 id,
729 oie_id,
730 ole_id,
731 attribute_name,
732 old_value,
733 new_value,
734 object_version_number,
735 created_by,
736 creation_date,
737 last_updated_by,
738 last_update_date,
739 last_update_login)
740 VALUES (
741 l_mrd_rec.id,
742 l_mrd_rec.oie_id,
743 l_mrd_rec.ole_id,
744 l_mrd_rec.attribute_name,
745 l_mrd_rec.old_value,
746 l_mrd_rec.new_value,
747 l_mrd_rec.object_version_number,
748 l_mrd_rec.created_by,
749 l_mrd_rec.creation_date,
750 l_mrd_rec.last_updated_by,
751 l_mrd_rec.last_update_date,
752 l_mrd_rec.last_update_login);
753 -- Set OUT values
754 x_mrd_rec := l_mrd_rec;
755 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
756 EXCEPTION
757 WHEN OKC_API.G_EXCEPTION_ERROR THEN
758 x_return_status := OKC_API.HANDLE_EXCEPTIONS
759 (
760 l_api_name,
761 G_PKG_NAME,
762 'OKC_API.G_RET_STS_ERROR',
766 );
763 x_msg_count,
764 x_msg_data,
765 '_PVT'
767 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
768 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
769 (
770 l_api_name,
771 G_PKG_NAME,
772 'OKC_API.G_RET_STS_UNEXP_ERROR',
773 x_msg_count,
774 x_msg_data,
775 '_PVT'
776 );
777 WHEN OTHERS THEN
778 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
779 (
780 l_api_name,
781 G_PKG_NAME,
782 'OTHERS',
783 x_msg_count,
784 x_msg_data,
785 '_PVT'
786 );
787 END insert_row;
788 ----------------------------------------------
789 -- insert_row for:OKC_MASSCHANGE_REQ_DTLS_V --
790 ----------------------------------------------
791 PROCEDURE insert_row(
792 p_api_version IN NUMBER,
793 p_init_msg_list IN VARCHAR2 ,
794 x_return_status OUT NOCOPY VARCHAR2,
795 x_msg_count OUT NOCOPY NUMBER,
796 x_msg_data OUT NOCOPY VARCHAR2,
797 p_mrdv_rec IN mrdv_rec_type,
798 x_mrdv_rec OUT NOCOPY mrdv_rec_type) IS
799
800 l_api_version CONSTANT NUMBER := 1;
801 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
802 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
803 l_mrdv_rec mrdv_rec_type;
804 l_def_mrdv_rec mrdv_rec_type;
805 l_mrd_rec mrd_rec_type;
806 lx_mrd_rec mrd_rec_type;
807 -------------------------------
808 -- FUNCTION fill_who_columns --
809 -------------------------------
810 FUNCTION fill_who_columns (
811 p_mrdv_rec IN mrdv_rec_type
812 ) RETURN mrdv_rec_type IS
813 l_mrdv_rec mrdv_rec_type := p_mrdv_rec;
814 BEGIN
815 l_mrdv_rec.CREATION_DATE := SYSDATE;
816 l_mrdv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
817 l_mrdv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
818 l_mrdv_rec.LAST_UPDATE_DATE := l_mrdv_rec.CREATION_DATE;
819 l_mrdv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
820 RETURN(l_mrdv_rec);
821 END fill_who_columns;
822 --------------------------------------------------
823 -- Set_Attributes for:OKC_MASSCHANGE_REQ_DTLS_V --
824 --------------------------------------------------
825 FUNCTION Set_Attributes (
826 p_mrdv_rec IN mrdv_rec_type,
827 x_mrdv_rec OUT NOCOPY mrdv_rec_type
828 ) RETURN VARCHAR2 IS
829 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
830 BEGIN
831 x_mrdv_rec := p_mrdv_rec;
832 x_mrdv_rec.OBJECT_VERSION_NUMBER := 1;
833 RETURN(l_return_status);
834 END Set_Attributes;
835 BEGIN
836 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
837 G_PKG_NAME,
838 p_init_msg_list,
839 l_api_version,
840 p_api_version,
841 '_PVT',
842 x_return_status);
843 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
844 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
845 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
846 RAISE OKC_API.G_EXCEPTION_ERROR;
847 END IF;
848 l_mrdv_rec := null_out_defaults(p_mrdv_rec);
849 -- Set primary key value
850 l_mrdv_rec.ID := get_seq_id;
851 --- Setting item attributes
852 l_return_status := Set_Attributes(
853 l_mrdv_rec, -- IN
854 l_def_mrdv_rec); -- OUT
855 --- If any errors happen abort API
856 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
857 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
858 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
859 RAISE OKC_API.G_EXCEPTION_ERROR;
860 END IF;
861 l_def_mrdv_rec := fill_who_columns(l_def_mrdv_rec);
862 --- Validate all non-missing attributes (Item Level Validation)
863 l_return_status := Validate_Attributes(l_def_mrdv_rec);
864 --- If any errors happen abort API
865 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
866 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
867 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
868 RAISE OKC_API.G_EXCEPTION_ERROR;
869 END IF;
870 l_return_status := Validate_Record(l_def_mrdv_rec);
871 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
872 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
873 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
874 RAISE OKC_API.G_EXCEPTION_ERROR;
875 END IF;
876 --------------------------------------
877 -- Move VIEW record to "Child" records
878 --------------------------------------
879 migrate(l_def_mrdv_rec, l_mrd_rec);
880 --------------------------------------------
881 -- Call the INSERT_ROW for each child record
882 --------------------------------------------
883 insert_row(
884 p_init_msg_list,
885 x_return_status,
889 lx_mrd_rec
886 x_msg_count,
887 x_msg_data,
888 l_mrd_rec,
890 );
891 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
892 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
893 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
894 RAISE OKC_API.G_EXCEPTION_ERROR;
895 END IF;
896 migrate(lx_mrd_rec, l_def_mrdv_rec);
897 -- Set OUT values
898 x_mrdv_rec := l_def_mrdv_rec;
899 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
900 EXCEPTION
901 WHEN OKC_API.G_EXCEPTION_ERROR THEN
902 x_return_status := OKC_API.HANDLE_EXCEPTIONS
903 (
904 l_api_name,
905 G_PKG_NAME,
906 'OKC_API.G_RET_STS_ERROR',
907 x_msg_count,
908 x_msg_data,
909 '_PVT'
910 );
911 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
912 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
913 (
914 l_api_name,
915 G_PKG_NAME,
916 'OKC_API.G_RET_STS_UNEXP_ERROR',
917 x_msg_count,
918 x_msg_data,
919 '_PVT'
920 );
921 WHEN OTHERS THEN
922 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
923 (
924 l_api_name,
925 G_PKG_NAME,
926 'OTHERS',
927 x_msg_count,
928 x_msg_data,
929 '_PVT'
930 );
931 END insert_row;
932 ----------------------------------------
933 -- PL/SQL TBL insert_row for:MRDV_TBL --
934 ----------------------------------------
935 PROCEDURE insert_row(
936 p_api_version IN NUMBER,
937 p_init_msg_list IN VARCHAR2 ,
938 x_return_status OUT NOCOPY VARCHAR2,
939 x_msg_count OUT NOCOPY NUMBER,
940 x_msg_data OUT NOCOPY VARCHAR2,
941 p_mrdv_tbl IN mrdv_tbl_type,
942 x_mrdv_tbl OUT NOCOPY mrdv_tbl_type) IS
943
944 l_api_version CONSTANT NUMBER := 1;
945 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
946 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
947 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
948 i NUMBER := 0;
949 BEGIN
950 OKC_API.init_msg_list(p_init_msg_list);
951 -- Make sure PL/SQL table has records in it before passing
952 IF (p_mrdv_tbl.COUNT > 0) THEN
953 i := p_mrdv_tbl.FIRST;
954 LOOP
955 insert_row (
956 p_api_version => p_api_version,
957 p_init_msg_list => OKC_API.G_FALSE,
958 x_return_status => x_return_status,
959 x_msg_count => x_msg_count,
960 x_msg_data => x_msg_data,
961 p_mrdv_rec => p_mrdv_tbl(i),
962 x_mrdv_rec => x_mrdv_tbl(i));
963 -- store the highest degree of error
964 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
965 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
966 l_overall_status := x_return_status;
967 End If;
968 End If;
969 EXIT WHEN (i = p_mrdv_tbl.LAST);
970 i := p_mrdv_tbl.NEXT(i);
971 END LOOP;
972 -- return overall status
973 x_return_status := l_overall_status;
974
975 END IF;
976 EXCEPTION
977 WHEN OKC_API.G_EXCEPTION_ERROR THEN
978 x_return_status := OKC_API.HANDLE_EXCEPTIONS
979 (
980 l_api_name,
981 G_PKG_NAME,
982 'OKC_API.G_RET_STS_ERROR',
983 x_msg_count,
984 x_msg_data,
985 '_PVT'
986 );
987 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
988 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
989 (
990 l_api_name,
991 G_PKG_NAME,
992 'OKC_API.G_RET_STS_UNEXP_ERROR',
993 x_msg_count,
994 x_msg_data,
995 '_PVT'
996 );
997 WHEN OTHERS THEN
998 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
999 (
1000 l_api_name,
1001 G_PKG_NAME,
1002 'OTHERS',
1003 x_msg_count,
1004 x_msg_data,
1005 '_PVT'
1006 );
1007 END insert_row;
1008
1009 ---------------------------------------------------------------------------
1010 -- PROCEDURE lock_row
1011 ---------------------------------------------------------------------------
1012 ------------------------------------------
1013 -- lock_row for:OKC_MASSCHANGE_REQ_DTLS --
1014 ------------------------------------------
1015 PROCEDURE lock_row(
1016 p_init_msg_list IN VARCHAR2 ,
1017 x_return_status OUT NOCOPY VARCHAR2,
1018 x_msg_count OUT NOCOPY NUMBER,
1019 x_msg_data OUT NOCOPY VARCHAR2,
1020 p_mrd_rec IN mrd_rec_type) IS
1021
1022 E_Resource_Busy EXCEPTION;
1023 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1024 CURSOR lock_csr (p_mrd_rec IN mrd_rec_type) IS
1025 SELECT OBJECT_VERSION_NUMBER
1026 FROM OKC_MASSCHANGE_REQ_DTLS
1027 WHERE ID = p_mrd_rec.id
1031 CURSOR lchk_csr (p_mrd_rec IN mrd_rec_type) IS
1028 AND OBJECT_VERSION_NUMBER = p_mrd_rec.object_version_number
1029 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1030
1032 SELECT OBJECT_VERSION_NUMBER
1033 FROM OKC_MASSCHANGE_REQ_DTLS
1034 WHERE ID = p_mrd_rec.id;
1035 l_api_version CONSTANT NUMBER := 1;
1036 l_api_name CONSTANT VARCHAR2(30) := 'DTLS_lock_row';
1037 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1038 l_object_version_number OKC_MASSCHANGE_REQ_DTLS.OBJECT_VERSION_NUMBER%TYPE;
1039 lc_object_version_number OKC_MASSCHANGE_REQ_DTLS.OBJECT_VERSION_NUMBER%TYPE;
1040 l_row_notfound BOOLEAN := FALSE;
1041 lc_row_notfound BOOLEAN := FALSE;
1042 BEGIN
1043 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1044 p_init_msg_list,
1045 '_PVT',
1046 x_return_status);
1047 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1048 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1049 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1050 RAISE OKC_API.G_EXCEPTION_ERROR;
1051 END IF;
1052 BEGIN
1053 OPEN lock_csr(p_mrd_rec);
1054 FETCH lock_csr INTO l_object_version_number;
1055 l_row_notfound := lock_csr%NOTFOUND;
1056 CLOSE lock_csr;
1057 EXCEPTION
1058 WHEN E_Resource_Busy THEN
1059 IF (lock_csr%ISOPEN) THEN
1060 CLOSE lock_csr;
1061 END IF;
1062 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1063 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1064 END;
1065
1066 IF ( l_row_notfound ) THEN
1067 OPEN lchk_csr(p_mrd_rec);
1068 FETCH lchk_csr INTO lc_object_version_number;
1069 lc_row_notfound := lchk_csr%NOTFOUND;
1070 CLOSE lchk_csr;
1071 END IF;
1072 IF (lc_row_notfound) THEN
1073 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1074 RAISE OKC_API.G_EXCEPTION_ERROR;
1075 ELSIF lc_object_version_number > p_mrd_rec.object_version_number THEN
1076 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1077 RAISE OKC_API.G_EXCEPTION_ERROR;
1078 ELSIF lc_object_version_number <> p_mrd_rec.object_version_number THEN
1079 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1080 RAISE OKC_API.G_EXCEPTION_ERROR;
1081 ELSIF lc_object_version_number = -1 THEN
1082 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1083 RAISE OKC_API.G_EXCEPTION_ERROR;
1084 END IF;
1085 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1086 EXCEPTION
1087 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1088 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1089 (
1090 l_api_name,
1091 G_PKG_NAME,
1092 'OKC_API.G_RET_STS_ERROR',
1093 x_msg_count,
1094 x_msg_data,
1095 '_PVT'
1096 );
1097 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1098 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1099 (
1100 l_api_name,
1101 G_PKG_NAME,
1102 'OKC_API.G_RET_STS_UNEXP_ERROR',
1103 x_msg_count,
1104 x_msg_data,
1105 '_PVT'
1106 );
1107 WHEN OTHERS THEN
1108 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1109 (
1110 l_api_name,
1111 G_PKG_NAME,
1112 'OTHERS',
1113 x_msg_count,
1114 x_msg_data,
1115 '_PVT'
1116 );
1117 END lock_row;
1118 --------------------------------------------
1119 -- lock_row for:OKC_MASSCHANGE_REQ_DTLS_V --
1120 --------------------------------------------
1121 PROCEDURE lock_row(
1122 p_api_version IN NUMBER,
1123 p_init_msg_list IN VARCHAR2 ,
1124 x_return_status OUT NOCOPY VARCHAR2,
1125 x_msg_count OUT NOCOPY NUMBER,
1126 x_msg_data OUT NOCOPY VARCHAR2,
1127 p_mrdv_rec IN mrdv_rec_type) IS
1128
1129 l_api_version CONSTANT NUMBER := 1;
1130 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1131 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1132 l_mrd_rec mrd_rec_type;
1133 BEGIN
1134 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1135 G_PKG_NAME,
1136 p_init_msg_list,
1137 l_api_version,
1138 p_api_version,
1139 '_PVT',
1140 x_return_status);
1141 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1142 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1143 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1144 RAISE OKC_API.G_EXCEPTION_ERROR;
1145 END IF;
1146 --------------------------------------
1147 -- Move VIEW record to "Child" records
1148 --------------------------------------
1149 migrate(p_mrdv_rec, l_mrd_rec);
1150 --------------------------------------------
1151 -- Call the LOCK_ROW for each child record
1155 x_return_status,
1152 --------------------------------------------
1153 lock_row(
1154 p_init_msg_list,
1156 x_msg_count,
1157 x_msg_data,
1158 l_mrd_rec
1159 );
1160 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1161 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1162 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1163 RAISE OKC_API.G_EXCEPTION_ERROR;
1164 END IF;
1165 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1166 EXCEPTION
1167 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1168 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1169 (
1170 l_api_name,
1171 G_PKG_NAME,
1172 'OKC_API.G_RET_STS_ERROR',
1173 x_msg_count,
1174 x_msg_data,
1175 '_PVT'
1176 );
1177 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1178 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1179 (
1180 l_api_name,
1181 G_PKG_NAME,
1182 'OKC_API.G_RET_STS_UNEXP_ERROR',
1183 x_msg_count,
1184 x_msg_data,
1185 '_PVT'
1186 );
1187 WHEN OTHERS THEN
1188 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1189 (
1190 l_api_name,
1191 G_PKG_NAME,
1192 'OTHERS',
1193 x_msg_count,
1194 x_msg_data,
1195 '_PVT'
1196 );
1197 END lock_row;
1198 --------------------------------------
1199 -- PL/SQL TBL lock_row for:MRDV_TBL --
1200 --------------------------------------
1201 PROCEDURE lock_row(
1202 p_api_version IN NUMBER,
1203 p_init_msg_list IN VARCHAR2 ,
1204 x_return_status OUT NOCOPY VARCHAR2,
1205 x_msg_count OUT NOCOPY NUMBER,
1206 x_msg_data OUT NOCOPY VARCHAR2,
1207 p_mrdv_tbl IN mrdv_tbl_type) IS
1208
1209 l_api_version CONSTANT NUMBER := 1;
1210 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1211 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1212 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1213 i NUMBER := 0;
1214 BEGIN
1215 OKC_API.init_msg_list(p_init_msg_list);
1216 -- Make sure PL/SQL table has records in it before passing
1217 IF (p_mrdv_tbl.COUNT > 0) THEN
1218 i := p_mrdv_tbl.FIRST;
1219 LOOP
1220 lock_row (
1221 p_api_version => p_api_version,
1222 p_init_msg_list => OKC_API.G_FALSE,
1223 x_return_status => x_return_status,
1224 x_msg_count => x_msg_count,
1225 x_msg_data => x_msg_data,
1226 p_mrdv_rec => p_mrdv_tbl(i));
1227 -- store the highest degree of error
1228 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1229 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1230 l_overall_status := x_return_status;
1231 End If;
1232 End If;
1233 EXIT WHEN (i = p_mrdv_tbl.LAST);
1234 i := p_mrdv_tbl.NEXT(i);
1235 END LOOP;
1236 -- return overall status
1237 x_return_status := l_overall_status;
1238 END IF;
1239 EXCEPTION
1240 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1241 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1242 (
1243 l_api_name,
1244 G_PKG_NAME,
1245 'OKC_API.G_RET_STS_ERROR',
1246 x_msg_count,
1247 x_msg_data,
1248 '_PVT'
1249 );
1250 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1251 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1252 (
1253 l_api_name,
1254 G_PKG_NAME,
1255 'OKC_API.G_RET_STS_UNEXP_ERROR',
1256 x_msg_count,
1257 x_msg_data,
1258 '_PVT'
1259 );
1260 WHEN OTHERS THEN
1261 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1262 (
1263 l_api_name,
1264 G_PKG_NAME,
1265 'OTHERS',
1266 x_msg_count,
1267 x_msg_data,
1268 '_PVT'
1269 );
1270 END lock_row;
1271
1272 ---------------------------------------------------------------------------
1273 -- PROCEDURE update_row
1274 ---------------------------------------------------------------------------
1275 --------------------------------------------
1276 -- update_row for:OKC_MASSCHANGE_REQ_DTLS --
1277 --------------------------------------------
1278 PROCEDURE update_row(
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_mrd_rec IN mrd_rec_type,
1284 x_mrd_rec OUT NOCOPY mrd_rec_type) IS
1285
1286 l_api_version CONSTANT NUMBER := 1;
1287 l_api_name CONSTANT VARCHAR2(30) := 'DTLS_update_row';
1288 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1289 l_mrd_rec mrd_rec_type := p_mrd_rec;
1290 l_def_mrd_rec mrd_rec_type;
1294 ----------------------------------
1291 l_row_notfound BOOLEAN := TRUE;
1292 ----------------------------------
1293 -- FUNCTION populate_new_record --
1295 FUNCTION populate_new_record (
1296 p_mrd_rec IN mrd_rec_type,
1297 x_mrd_rec OUT NOCOPY mrd_rec_type
1298 ) RETURN VARCHAR2 IS
1299 l_mrd_rec mrd_rec_type;
1300 l_row_notfound BOOLEAN := TRUE;
1301 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1302 BEGIN
1303 x_mrd_rec := p_mrd_rec;
1304 -- Get current database values
1305 l_mrd_rec := get_rec(p_mrd_rec, l_row_notfound);
1306 IF (l_row_notfound) THEN
1307 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1308 END IF;
1309 IF (x_mrd_rec.id = OKC_API.G_MISS_NUM)
1310 THEN
1311 x_mrd_rec.id := l_mrd_rec.id;
1312 END IF;
1313 IF (x_mrd_rec.oie_id = OKC_API.G_MISS_NUM)
1314 THEN
1315 x_mrd_rec.oie_id := l_mrd_rec.oie_id;
1316 END IF;
1317 IF (x_mrd_rec.ole_id = OKC_API.G_MISS_NUM)
1318 THEN
1319 x_mrd_rec.ole_id := l_mrd_rec.ole_id;
1320 END IF;
1321 IF (x_mrd_rec.attribute_name = OKC_API.G_MISS_CHAR)
1322 THEN
1323 x_mrd_rec.attribute_name := l_mrd_rec.attribute_name;
1324 END IF;
1325 IF (x_mrd_rec.old_value = OKC_API.G_MISS_CHAR)
1326 THEN
1327 x_mrd_rec.old_value := l_mrd_rec.old_value;
1328 END IF;
1329 IF (x_mrd_rec.new_value = OKC_API.G_MISS_CHAR)
1330 THEN
1331 x_mrd_rec.new_value := l_mrd_rec.new_value;
1332 END IF;
1333 IF (x_mrd_rec.object_version_number = OKC_API.G_MISS_NUM)
1334 THEN
1335 x_mrd_rec.object_version_number := l_mrd_rec.object_version_number;
1336 END IF;
1337 IF (x_mrd_rec.created_by = OKC_API.G_MISS_NUM)
1338 THEN
1339 x_mrd_rec.created_by := l_mrd_rec.created_by;
1340 END IF;
1341 IF (x_mrd_rec.creation_date = OKC_API.G_MISS_DATE)
1342 THEN
1343 x_mrd_rec.creation_date := l_mrd_rec.creation_date;
1344 END IF;
1345 IF (x_mrd_rec.last_updated_by = OKC_API.G_MISS_NUM)
1346 THEN
1347 x_mrd_rec.last_updated_by := l_mrd_rec.last_updated_by;
1348 END IF;
1349 IF (x_mrd_rec.last_update_date = OKC_API.G_MISS_DATE)
1350 THEN
1351 x_mrd_rec.last_update_date := l_mrd_rec.last_update_date;
1352 END IF;
1353 IF (x_mrd_rec.last_update_login = OKC_API.G_MISS_NUM)
1354 THEN
1355 x_mrd_rec.last_update_login := l_mrd_rec.last_update_login;
1356 END IF;
1357 RETURN(l_return_status);
1358 END populate_new_record;
1359 ------------------------------------------------
1360 -- Set_Attributes for:OKC_MASSCHANGE_REQ_DTLS --
1361 ------------------------------------------------
1362 FUNCTION Set_Attributes (
1363 p_mrd_rec IN mrd_rec_type,
1364 x_mrd_rec OUT NOCOPY mrd_rec_type
1365 ) RETURN VARCHAR2 IS
1366 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1367 BEGIN
1368 x_mrd_rec := p_mrd_rec;
1369 RETURN(l_return_status);
1370 END Set_Attributes;
1371 BEGIN
1372 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1373 p_init_msg_list,
1374 '_PVT',
1375 x_return_status);
1376 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1377 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1378 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1379 RAISE OKC_API.G_EXCEPTION_ERROR;
1380 END IF;
1381 --- Setting item attributes
1382 l_return_status := Set_Attributes(
1383 p_mrd_rec, -- IN
1384 l_mrd_rec); -- OUT
1385 --- If any errors happen abort API
1386 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1387 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1388 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1389 RAISE OKC_API.G_EXCEPTION_ERROR;
1390 END IF;
1391 l_return_status := populate_new_record(l_mrd_rec, l_def_mrd_rec);
1392 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1393 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1394 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1395 RAISE OKC_API.G_EXCEPTION_ERROR;
1396 END IF;
1397 UPDATE OKC_MASSCHANGE_REQ_DTLS
1398 SET OIE_ID = l_def_mrd_rec.oie_id,
1399 OLE_ID = l_def_mrd_rec.ole_id,
1400 ATTRIBUTE_NAME = l_def_mrd_rec.attribute_name,
1401 OLD_VALUE = l_def_mrd_rec.old_value,
1402 NEW_VALUE = l_def_mrd_rec.new_value,
1403 OBJECT_VERSION_NUMBER = l_def_mrd_rec.object_version_number,
1404 CREATED_BY = l_def_mrd_rec.created_by,
1405 CREATION_DATE = l_def_mrd_rec.creation_date,
1406 LAST_UPDATED_BY = l_def_mrd_rec.last_updated_by,
1407 LAST_UPDATE_DATE = l_def_mrd_rec.last_update_date,
1408 LAST_UPDATE_LOGIN = l_def_mrd_rec.last_update_login
1409 WHERE ID = l_def_mrd_rec.id;
1410
1411 x_mrd_rec := l_def_mrd_rec;
1412 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1413 EXCEPTION
1414 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1418 G_PKG_NAME,
1415 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1416 (
1417 l_api_name,
1419 'OKC_API.G_RET_STS_ERROR',
1420 x_msg_count,
1421 x_msg_data,
1422 '_PVT'
1423 );
1424 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1425 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1426 (
1427 l_api_name,
1428 G_PKG_NAME,
1429 'OKC_API.G_RET_STS_UNEXP_ERROR',
1430 x_msg_count,
1431 x_msg_data,
1432 '_PVT'
1433 );
1434 WHEN OTHERS THEN
1435 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1436 (
1437 l_api_name,
1438 G_PKG_NAME,
1439 'OTHERS',
1440 x_msg_count,
1441 x_msg_data,
1442 '_PVT'
1443 );
1444 END update_row;
1445 ----------------------------------------------
1446 -- update_row for:OKC_MASSCHANGE_REQ_DTLS_V --
1447 ----------------------------------------------
1448 PROCEDURE update_row(
1449 p_api_version IN NUMBER,
1450 p_init_msg_list IN VARCHAR2 ,
1451 x_return_status OUT NOCOPY VARCHAR2,
1452 x_msg_count OUT NOCOPY NUMBER,
1453 x_msg_data OUT NOCOPY VARCHAR2,
1454 p_mrdv_rec IN mrdv_rec_type,
1455 x_mrdv_rec OUT NOCOPY mrdv_rec_type) IS
1456
1457 l_api_version CONSTANT NUMBER := 1;
1458 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1459 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1460 l_mrdv_rec mrdv_rec_type := p_mrdv_rec;
1461 l_def_mrdv_rec mrdv_rec_type;
1462 l_mrd_rec mrd_rec_type;
1463 lx_mrd_rec mrd_rec_type;
1464 -------------------------------
1465 -- FUNCTION fill_who_columns --
1466 -------------------------------
1467 FUNCTION fill_who_columns (
1468 p_mrdv_rec IN mrdv_rec_type
1469 ) RETURN mrdv_rec_type IS
1470 l_mrdv_rec mrdv_rec_type := p_mrdv_rec;
1471 BEGIN
1472 l_mrdv_rec.LAST_UPDATE_DATE := SYSDATE;
1473 l_mrdv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1474 RETURN(l_mrdv_rec);
1475 END fill_who_columns;
1476 ----------------------------------
1477 -- FUNCTION populate_new_record --
1478 ----------------------------------
1479 FUNCTION populate_new_record (
1480 p_mrdv_rec IN mrdv_rec_type,
1481 x_mrdv_rec OUT NOCOPY mrdv_rec_type
1482 ) RETURN VARCHAR2 IS
1483 l_mrdv_rec mrdv_rec_type;
1484 l_row_notfound BOOLEAN := TRUE;
1485 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1486 BEGIN
1487 x_mrdv_rec := p_mrdv_rec;
1488 -- Get current database values
1489 l_mrdv_rec := get_rec(p_mrdv_rec, l_row_notfound);
1490 IF (l_row_notfound) THEN
1491 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1492 END IF;
1493 IF (x_mrdv_rec.id = OKC_API.G_MISS_NUM)
1494 THEN
1495 x_mrdv_rec.id := l_mrdv_rec.id;
1496 END IF;
1497 IF (x_mrdv_rec.oie_id = OKC_API.G_MISS_NUM)
1498 THEN
1499 x_mrdv_rec.oie_id := l_mrdv_rec.oie_id;
1500 END IF;
1501 IF (x_mrdv_rec.ole_id = OKC_API.G_MISS_NUM)
1502 THEN
1503 x_mrdv_rec.ole_id := l_mrdv_rec.ole_id;
1504 END IF;
1505 IF (x_mrdv_rec.attribute_name = OKC_API.G_MISS_CHAR)
1506 THEN
1507 x_mrdv_rec.attribute_name := l_mrdv_rec.attribute_name;
1508 END IF;
1509 IF (x_mrdv_rec.old_value = OKC_API.G_MISS_CHAR)
1510 THEN
1511 x_mrdv_rec.old_value := l_mrdv_rec.old_value;
1512 END IF;
1513 IF (x_mrdv_rec.new_value = OKC_API.G_MISS_CHAR)
1514 THEN
1515 x_mrdv_rec.new_value := l_mrdv_rec.new_value;
1516 END IF;
1517 IF (x_mrdv_rec.object_version_number = OKC_API.G_MISS_NUM)
1518 THEN
1519 x_mrdv_rec.object_version_number := l_mrdv_rec.object_version_number;
1520 END IF;
1521 IF (x_mrdv_rec.created_by = OKC_API.G_MISS_NUM)
1522 THEN
1523 x_mrdv_rec.created_by := l_mrdv_rec.created_by;
1524 END IF;
1525 IF (x_mrdv_rec.creation_date = OKC_API.G_MISS_DATE)
1526 THEN
1527 x_mrdv_rec.creation_date := l_mrdv_rec.creation_date;
1528 END IF;
1529 IF (x_mrdv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1530 THEN
1531 x_mrdv_rec.last_updated_by := l_mrdv_rec.last_updated_by;
1532 END IF;
1533 IF (x_mrdv_rec.last_update_date = OKC_API.G_MISS_DATE)
1534 THEN
1535 x_mrdv_rec.last_update_date := l_mrdv_rec.last_update_date;
1536 END IF;
1537 IF (x_mrdv_rec.last_update_login = OKC_API.G_MISS_NUM)
1538 THEN
1539 x_mrdv_rec.last_update_login := l_mrdv_rec.last_update_login;
1540 END IF;
1541 RETURN(l_return_status);
1542 END populate_new_record;
1543 --------------------------------------------------
1544 -- Set_Attributes for:OKC_MASSCHANGE_REQ_DTLS_V --
1545 --------------------------------------------------
1546 FUNCTION Set_Attributes (
1547 p_mrdv_rec IN mrdv_rec_type,
1548 x_mrdv_rec OUT NOCOPY mrdv_rec_type
1552 x_mrdv_rec := p_mrdv_rec;
1549 ) RETURN VARCHAR2 IS
1550 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1551 BEGIN
1553 x_mrdv_rec.OBJECT_VERSION_NUMBER := NVL(x_mrdv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1554 RETURN(l_return_status);
1555 END Set_Attributes;
1556 BEGIN
1557 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1558 G_PKG_NAME,
1559 p_init_msg_list,
1560 l_api_version,
1561 p_api_version,
1562 '_PVT',
1563 x_return_status);
1564 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1565 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1566 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1567 RAISE OKC_API.G_EXCEPTION_ERROR;
1568 END IF;
1569 --- Setting item attributes
1570 l_return_status := Set_Attributes(
1571 p_mrdv_rec, -- IN
1572 l_mrdv_rec); -- OUT
1573 --- If any errors happen abort API
1574 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1575 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1576 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1577 RAISE OKC_API.G_EXCEPTION_ERROR;
1578 END IF;
1579 l_return_status := populate_new_record(l_mrdv_rec, l_def_mrdv_rec);
1580 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1581 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1582 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1583 RAISE OKC_API.G_EXCEPTION_ERROR;
1584 END IF;
1585 l_def_mrdv_rec := fill_who_columns(l_def_mrdv_rec);
1586 --- Validate all non-missing attributes (Item Level Validation)
1587 l_return_status := Validate_Attributes(l_def_mrdv_rec);
1588 --- If any errors happen abort API
1589 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1590 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1591 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1592 RAISE OKC_API.G_EXCEPTION_ERROR;
1593 END IF;
1594 l_return_status := Validate_Record(l_def_mrdv_rec);
1595 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1596 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1597 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1598 RAISE OKC_API.G_EXCEPTION_ERROR;
1599 END IF;
1600
1601 --------------------------------------
1602 -- Move VIEW record to "Child" records
1603 --------------------------------------
1604 migrate(l_def_mrdv_rec, l_mrd_rec);
1605 --------------------------------------------
1606 -- Call the UPDATE_ROW for each child record
1607 --------------------------------------------
1608 update_row(
1609 p_init_msg_list,
1610 x_return_status,
1611 x_msg_count,
1612 x_msg_data,
1613 l_mrd_rec,
1614 lx_mrd_rec
1615 );
1616 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1617 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1618 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1619 RAISE OKC_API.G_EXCEPTION_ERROR;
1620 END IF;
1621 migrate(lx_mrd_rec, l_def_mrdv_rec);
1622 x_mrdv_rec := l_def_mrdv_rec;
1623 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1624 EXCEPTION
1625 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1626 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1627 (
1628 l_api_name,
1629 G_PKG_NAME,
1630 'OKC_API.G_RET_STS_ERROR',
1631 x_msg_count,
1632 x_msg_data,
1633 '_PVT'
1634 );
1635 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1636 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1637 (
1638 l_api_name,
1639 G_PKG_NAME,
1640 'OKC_API.G_RET_STS_UNEXP_ERROR',
1641 x_msg_count,
1642 x_msg_data,
1643 '_PVT'
1644 );
1645 WHEN OTHERS THEN
1646 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1647 (
1648 l_api_name,
1649 G_PKG_NAME,
1650 'OTHERS',
1651 x_msg_count,
1652 x_msg_data,
1653 '_PVT'
1654 );
1655 END update_row;
1656 ----------------------------------------
1657 -- PL/SQL TBL update_row for:MRDV_TBL --
1658 ----------------------------------------
1659 PROCEDURE update_row(
1660 p_api_version IN NUMBER,
1661 p_init_msg_list IN VARCHAR2 ,
1662 x_return_status OUT NOCOPY VARCHAR2,
1663 x_msg_count OUT NOCOPY NUMBER,
1664 x_msg_data OUT NOCOPY VARCHAR2,
1665 p_mrdv_tbl IN mrdv_tbl_type,
1666 x_mrdv_tbl OUT NOCOPY mrdv_tbl_type) IS
1667
1668 l_api_version CONSTANT NUMBER := 1;
1669 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1670 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1671 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1672 i NUMBER := 0;
1673 BEGIN
1674 OKC_API.init_msg_list(p_init_msg_list);
1678 LOOP
1675 -- Make sure PL/SQL table has records in it before passing
1676 IF (p_mrdv_tbl.COUNT > 0) THEN
1677 i := p_mrdv_tbl.FIRST;
1679 update_row (
1680 p_api_version => p_api_version,
1681 p_init_msg_list => OKC_API.G_FALSE,
1682 x_return_status => x_return_status,
1683 x_msg_count => x_msg_count,
1684 x_msg_data => x_msg_data,
1685 p_mrdv_rec => p_mrdv_tbl(i),
1686 x_mrdv_rec => x_mrdv_tbl(i));
1687 -- store the highest degree of error
1688 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1689 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1690 l_overall_status := x_return_status;
1691 End If;
1692 End If;
1693 EXIT WHEN (i = p_mrdv_tbl.LAST);
1694 i := p_mrdv_tbl.NEXT(i);
1695 END LOOP;
1696 -- return overall status
1697 x_return_status := l_overall_status;
1698 END IF;
1699 EXCEPTION
1700 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1701 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1702 (
1703 l_api_name,
1704 G_PKG_NAME,
1705 'OKC_API.G_RET_STS_ERROR',
1706 x_msg_count,
1707 x_msg_data,
1708 '_PVT'
1709 );
1710 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1711 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1712 (
1713 l_api_name,
1714 G_PKG_NAME,
1715 'OKC_API.G_RET_STS_UNEXP_ERROR',
1716 x_msg_count,
1717 x_msg_data,
1718 '_PVT'
1719 );
1720 WHEN OTHERS THEN
1721 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1722 (
1723 l_api_name,
1724 G_PKG_NAME,
1725 'OTHERS',
1726 x_msg_count,
1727 x_msg_data,
1728 '_PVT'
1729 );
1730 END update_row;
1731
1732 ---------------------------------------------------------------------------
1733 -- PROCEDURE delete_row
1734 ---------------------------------------------------------------------------
1735 --------------------------------------------
1736 -- delete_row for:OKC_MASSCHANGE_REQ_DTLS --
1737 --------------------------------------------
1738 PROCEDURE delete_row(
1739 p_init_msg_list IN VARCHAR2 ,
1740 x_return_status OUT NOCOPY VARCHAR2,
1741 x_msg_count OUT NOCOPY NUMBER,
1742 x_msg_data OUT NOCOPY VARCHAR2,
1743 p_mrd_rec IN mrd_rec_type) IS
1744
1745 l_api_version CONSTANT NUMBER := 1;
1746 l_api_name CONSTANT VARCHAR2(30) := 'DTLS_delete_row';
1747 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1748 l_mrd_rec mrd_rec_type:= p_mrd_rec;
1749 l_row_notfound BOOLEAN := TRUE;
1750 BEGIN
1751 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1752 p_init_msg_list,
1753 '_PVT',
1754 x_return_status);
1755 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1756 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1757 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1758 RAISE OKC_API.G_EXCEPTION_ERROR;
1759 END IF;
1760 DELETE FROM OKC_MASSCHANGE_REQ_DTLS
1761 WHERE ID = l_mrd_rec.id;
1762
1763 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1764 EXCEPTION
1765 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1766 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1767 (
1768 l_api_name,
1769 G_PKG_NAME,
1770 'OKC_API.G_RET_STS_ERROR',
1771 x_msg_count,
1772 x_msg_data,
1773 '_PVT'
1774 );
1775 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1776 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1777 (
1778 l_api_name,
1779 G_PKG_NAME,
1780 'OKC_API.G_RET_STS_UNEXP_ERROR',
1781 x_msg_count,
1782 x_msg_data,
1783 '_PVT'
1784 );
1785 WHEN OTHERS THEN
1786 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1787 (
1788 l_api_name,
1789 G_PKG_NAME,
1790 'OTHERS',
1791 x_msg_count,
1792 x_msg_data,
1793 '_PVT'
1794 );
1795 END delete_row;
1796 ----------------------------------------------
1797 -- delete_row for:OKC_MASSCHANGE_REQ_DTLS_V --
1798 ----------------------------------------------
1799 PROCEDURE delete_row(
1800 p_api_version IN NUMBER,
1801 p_init_msg_list IN VARCHAR2 ,
1802 x_return_status OUT NOCOPY VARCHAR2,
1803 x_msg_count OUT NOCOPY NUMBER,
1804 x_msg_data OUT NOCOPY VARCHAR2,
1805 p_mrdv_rec IN mrdv_rec_type) IS
1806
1807 l_api_version CONSTANT NUMBER := 1;
1808 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1809 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1813 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1810 l_mrdv_rec mrdv_rec_type := p_mrdv_rec;
1811 l_mrd_rec mrd_rec_type;
1812 BEGIN
1814 G_PKG_NAME,
1815 p_init_msg_list,
1816 l_api_version,
1817 p_api_version,
1818 '_PVT',
1819 x_return_status);
1820 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1821 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1822 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1823 RAISE OKC_API.G_EXCEPTION_ERROR;
1824 END IF;
1825 --------------------------------------
1826 -- Move VIEW record to "Child" records
1827 --------------------------------------
1828 migrate(l_mrdv_rec, l_mrd_rec);
1829 --------------------------------------------
1830 -- Call the DELETE_ROW for each child record
1831 --------------------------------------------
1832 delete_row(
1833 p_init_msg_list,
1834 x_return_status,
1835 x_msg_count,
1836 x_msg_data,
1837 l_mrd_rec
1838 );
1839 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1840 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1841 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1842 RAISE OKC_API.G_EXCEPTION_ERROR;
1843 END IF;
1844 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1845 EXCEPTION
1846 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1847 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1848 (
1849 l_api_name,
1850 G_PKG_NAME,
1851 'OKC_API.G_RET_STS_ERROR',
1852 x_msg_count,
1853 x_msg_data,
1854 '_PVT'
1855 );
1856 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1857 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1858 (
1859 l_api_name,
1860 G_PKG_NAME,
1861 'OKC_API.G_RET_STS_UNEXP_ERROR',
1862 x_msg_count,
1863 x_msg_data,
1864 '_PVT'
1865 );
1866 WHEN OTHERS THEN
1867 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1868 (
1869 l_api_name,
1870 G_PKG_NAME,
1871 'OTHERS',
1872 x_msg_count,
1873 x_msg_data,
1874 '_PVT'
1875 );
1876 END delete_row;
1877 ----------------------------------------
1878 -- PL/SQL TBL delete_row for:MRDV_TBL --
1879 ----------------------------------------
1880 PROCEDURE delete_row(
1881 p_api_version IN NUMBER,
1882 p_init_msg_list IN VARCHAR2 ,
1883 x_return_status OUT NOCOPY VARCHAR2,
1884 x_msg_count OUT NOCOPY NUMBER,
1885 x_msg_data OUT NOCOPY VARCHAR2,
1886 p_mrdv_tbl IN mrdv_tbl_type) IS
1887
1888 l_api_version CONSTANT NUMBER := 1;
1889 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1890 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1891 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1892 i NUMBER := 0;
1893 BEGIN
1894 OKC_API.init_msg_list(p_init_msg_list);
1895 -- Make sure PL/SQL table has records in it before passing
1896 IF (p_mrdv_tbl.COUNT > 0) THEN
1897 i := p_mrdv_tbl.FIRST;
1898 LOOP
1899 delete_row (
1900 p_api_version => p_api_version,
1901 p_init_msg_list => OKC_API.G_FALSE,
1902 x_return_status => x_return_status,
1903 x_msg_count => x_msg_count,
1904 x_msg_data => x_msg_data,
1905 p_mrdv_rec => p_mrdv_tbl(i));
1906 -- store the highest degree of error
1907 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1908 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1909 l_overall_status := x_return_status;
1910 End If;
1911 End If;
1912 EXIT WHEN (i = p_mrdv_tbl.LAST);
1913 i := p_mrdv_tbl.NEXT(i);
1914 END LOOP;
1915 -- return overall status
1916 x_return_status := l_overall_status;
1917 END IF;
1918 EXCEPTION
1919 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1920 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1921 (
1922 l_api_name,
1923 G_PKG_NAME,
1924 'OKC_API.G_RET_STS_ERROR',
1925 x_msg_count,
1926 x_msg_data,
1927 '_PVT'
1928 );
1929 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1930 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1931 (
1932 l_api_name,
1933 G_PKG_NAME,
1934 'OKC_API.G_RET_STS_UNEXP_ERROR',
1935 x_msg_count,
1936 x_msg_data,
1937 '_PVT'
1938 );
1939 WHEN OTHERS THEN
1940 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1941 (
1942 l_api_name,
1943 G_PKG_NAME,
1944 'OTHERS',
1945 x_msg_count,
1946 x_msg_data,
1947 '_PVT'
1948 );
1949 END delete_row;
1950 END OKC_MRD_PVT;