[Home] [Help]
PACKAGE BODY: APPS.OKS_PMA_PVT
Source
1 PACKAGE BODY OKS_PMA_PVT AS
2 /* $Header: OKSSPMAB.pls 120.1 2005/07/15 09:21:35 parkumar noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKC_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKS_PM_ACTIVITIES_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_pmav_rec IN pmav_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN pmav_rec_type IS
100 CURSOR oks_pmav_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 CLE_ID,
104 DNZ_CHR_ID,
105 ACTIVITY_ID,
106 SELECT_YN,
107 CONF_REQ_YN,
108 SCH_EXISTS_YN,
109 PROGRAM_APPLICATION_ID,
110 PROGRAM_ID,
111 PROGRAM_UPDATE_DATE,
112 OBJECT_VERSION_NUMBER,
113 SECURITY_GROUP_ID,
114 REQUEST_ID,
115 CREATED_BY,
116 CREATION_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_DATE,
119 LAST_UPDATE_LOGIN,
120 -- R12 Data Model Changes 4485150 Start
121 ORIG_SYSTEM_ID1,
122 ORIG_SYSTEM_REFERENCE1,
123 ORIG_SYSTEM_SOURCE_CODE
124 -- R12 Data Model Changes 4485150 End
125 FROM Oks_Pm_Activities_V
126 WHERE oks_pm_activities_v.id = p_id;
127 l_oks_pmav_pk oks_pmav_pk_csr%ROWTYPE;
128 l_pmav_rec pmav_rec_type;
129 BEGIN
130 x_no_data_found := TRUE;
131 -- Get current database values
132 OPEN oks_pmav_pk_csr (p_pmav_rec.id);
133 FETCH oks_pmav_pk_csr INTO
134 l_pmav_rec.id,
135 l_pmav_rec.cle_id,
136 l_pmav_rec.dnz_chr_id,
137 l_pmav_rec.activity_id,
138 l_pmav_rec.select_yn,
139 l_pmav_rec.conf_req_yn,
140 l_pmav_rec.sch_exists_yn,
141 l_pmav_rec.program_application_id,
142 l_pmav_rec.program_id,
143 l_pmav_rec.program_update_date,
144 l_pmav_rec.object_version_number,
145 l_pmav_rec.security_group_id,
146 l_pmav_rec.request_id,
147 l_pmav_rec.created_by,
148 l_pmav_rec.creation_date,
149 l_pmav_rec.last_updated_by,
150 l_pmav_rec.last_update_date,
151 l_pmav_rec.last_update_login,
152 -- R12 Data Model Changes 4485150 Start
153 l_pmav_rec.orig_system_id1,
154 l_pmav_rec.orig_system_reference1,
155 l_pmav_rec.orig_system_source_code
156 -- R12 Data Model Changes 4485150 End
157 ;
158 x_no_data_found := oks_pmav_pk_csr%NOTFOUND;
159 CLOSE oks_pmav_pk_csr;
160 RETURN(l_pmav_rec);
161 END get_rec;
162
163 ------------------------------------------------------------------
164 -- This version of get_rec sets error messages if no data found --
165 ------------------------------------------------------------------
166 FUNCTION get_rec (
167 p_pmav_rec IN pmav_rec_type,
168 x_return_status OUT NOCOPY VARCHAR2
169 ) RETURN pmav_rec_type IS
170 l_pmav_rec pmav_rec_type;
171 l_row_notfound BOOLEAN := TRUE;
172 BEGIN
173 x_return_status := OKC_API.G_RET_STS_SUCCESS;
174 l_pmav_rec := get_rec(p_pmav_rec, l_row_notfound);
175 IF (l_row_notfound) THEN
176 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
177 x_return_status := OKC_API.G_RET_STS_ERROR;
178 END IF;
179 RETURN(l_pmav_rec);
180 END get_rec;
181 -----------------------------------------------------------
182 -- So we don't have to pass an "l_row_notfound" variable --
183 -----------------------------------------------------------
184 FUNCTION get_rec (
185 p_pmav_rec IN pmav_rec_type
186 ) RETURN pmav_rec_type IS
187 l_row_not_found BOOLEAN := TRUE;
188 BEGIN
189 RETURN(get_rec(p_pmav_rec, l_row_not_found));
190 END get_rec;
191 ---------------------------------------------------------------------------
192 -- FUNCTION get_rec for: OKS_PM_ACTIVITIES
193 ---------------------------------------------------------------------------
194 FUNCTION get_rec (
195 p_pma_rec IN pma_rec_type,
196 x_no_data_found OUT NOCOPY BOOLEAN
197 ) RETURN pma_rec_type IS
198 CURSOR oks_pm_activities_pk_csr (p_id IN NUMBER) IS
199 SELECT
200 ID,
201 CLE_ID,
202 DNZ_CHR_ID,
203 ACTIVITY_ID,
204 SELECT_YN,
205 CONF_REQ_YN,
206 SCH_EXISTS_YN,
207 PROGRAM_APPLICATION_ID,
208 PROGRAM_ID,
209 PROGRAM_UPDATE_DATE,
210 OBJECT_VERSION_NUMBER,
211 REQUEST_ID,
212 CREATED_BY,
213 CREATION_DATE,
214 LAST_UPDATED_BY,
215 LAST_UPDATE_DATE,
216 LAST_UPDATE_LOGIN,
217 -- R12 Data Model Changes 4485150 Start
218 ORIG_SYSTEM_ID1,
219 ORIG_SYSTEM_REFERENCE1,
220 ORIG_SYSTEM_SOURCE_CODE
221 -- R12 Data Model Changes 4485150 End
222 FROM Oks_Pm_Activities
223 WHERE oks_pm_activities.id = p_id;
224 l_oks_pm_activities_pk oks_pm_activities_pk_csr%ROWTYPE;
225 l_pma_rec pma_rec_type;
226 BEGIN
227 x_no_data_found := TRUE;
228 -- Get current database values
229 OPEN oks_pm_activities_pk_csr (p_pma_rec.id);
230 FETCH oks_pm_activities_pk_csr INTO
231 l_pma_rec.id,
232 l_pma_rec.cle_id,
233 l_pma_rec.dnz_chr_id,
234 l_pma_rec.activity_id,
235 l_pma_rec.select_yn,
236 l_pma_rec.conf_req_yn,
237 l_pma_rec.sch_exists_yn,
238 l_pma_rec.program_application_id,
239 l_pma_rec.program_id,
240 l_pma_rec.program_update_date,
241 l_pma_rec.object_version_number,
242 l_pma_rec.request_id,
243 l_pma_rec.created_by,
244 l_pma_rec.creation_date,
245 l_pma_rec.last_updated_by,
246 l_pma_rec.last_update_date,
247 l_pma_rec.last_update_login,
248 -- R12 Data Model Changes 4485150 Start
249 l_pma_rec.orig_system_id1,
250 l_pma_rec.orig_system_reference1,
251 l_pma_rec.orig_system_source_code
252 -- R12 Data Model Changes 4485150 End
253 ;
254 x_no_data_found := oks_pm_activities_pk_csr%NOTFOUND;
255 CLOSE oks_pm_activities_pk_csr;
256 RETURN(l_pma_rec);
257 END get_rec;
258
259 ------------------------------------------------------------------
260 -- This version of get_rec sets error messages if no data found --
261 ------------------------------------------------------------------
262 FUNCTION get_rec (
263 p_pma_rec IN pma_rec_type,
264 x_return_status OUT NOCOPY VARCHAR2
265 ) RETURN pma_rec_type IS
266 l_pma_rec pma_rec_type;
267 l_row_notfound BOOLEAN := TRUE;
268 BEGIN
269 x_return_status := OKC_API.G_RET_STS_SUCCESS;
270 l_pma_rec := get_rec(p_pma_rec, l_row_notfound);
271 IF (l_row_notfound) THEN
272 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
273 x_return_status := OKC_API.G_RET_STS_ERROR;
274 END IF;
275 RETURN(l_pma_rec);
276 END get_rec;
277 -----------------------------------------------------------
278 -- So we don't have to pass an "l_row_notfound" variable --
279 -----------------------------------------------------------
280 FUNCTION get_rec (
281 p_pma_rec IN pma_rec_type
282 ) RETURN pma_rec_type IS
283 l_row_not_found BOOLEAN := TRUE;
284 BEGIN
285 RETURN(get_rec(p_pma_rec, l_row_not_found));
286 END get_rec;
287 ---------------------------------------------------------------------------
288 -- FUNCTION null_out_defaults for: OKS_PM_ACTIVITIES_V
289 ---------------------------------------------------------------------------
290 FUNCTION null_out_defaults (
291 p_pmav_rec IN pmav_rec_type
292 ) RETURN pmav_rec_type IS
293 l_pmav_rec pmav_rec_type := p_pmav_rec;
294 BEGIN
295 IF (l_pmav_rec.id = OKC_API.G_MISS_NUM ) THEN
296 l_pmav_rec.id := NULL;
297 END IF;
298 IF (l_pmav_rec.cle_id = OKC_API.G_MISS_NUM ) THEN
299 l_pmav_rec.cle_id := NULL;
300 END IF;
301 IF (l_pmav_rec.dnz_chr_id = OKC_API.G_MISS_NUM ) THEN
302 l_pmav_rec.dnz_chr_id := NULL;
303 END IF;
304 IF (l_pmav_rec.activity_id = OKC_API.G_MISS_NUM ) THEN
305 l_pmav_rec.activity_id := NULL;
306 END IF;
307 IF (l_pmav_rec.select_yn = OKC_API.G_MISS_CHAR ) THEN
308 l_pmav_rec.select_yn := NULL;
309 END IF;
310 IF (l_pmav_rec.conf_req_yn = OKC_API.G_MISS_CHAR ) THEN
311 l_pmav_rec.conf_req_yn := NULL;
312 END IF;
313 IF (l_pmav_rec.sch_exists_yn = OKC_API.G_MISS_CHAR ) THEN
314 l_pmav_rec.sch_exists_yn := NULL;
315 END IF;
316 IF (l_pmav_rec.program_application_id = OKC_API.G_MISS_NUM ) THEN
317 l_pmav_rec.program_application_id := NULL;
318 END IF;
319 IF (l_pmav_rec.program_id = OKC_API.G_MISS_NUM ) THEN
320 l_pmav_rec.program_id := NULL;
321 END IF;
322 IF (l_pmav_rec.program_update_date = OKC_API.G_MISS_DATE ) THEN
323 l_pmav_rec.program_update_date := NULL;
324 END IF;
325 IF (l_pmav_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
326 l_pmav_rec.object_version_number := NULL;
327 END IF;
328 IF (l_pmav_rec.security_group_id = OKC_API.G_MISS_NUM ) THEN
329 l_pmav_rec.security_group_id := NULL;
330 END IF;
331 IF (l_pmav_rec.request_id = OKC_API.G_MISS_NUM ) THEN
332 l_pmav_rec.request_id := NULL;
333 END IF;
334 IF (l_pmav_rec.created_by = OKC_API.G_MISS_NUM ) THEN
335 l_pmav_rec.created_by := NULL;
336 END IF;
337 IF (l_pmav_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
338 l_pmav_rec.creation_date := NULL;
339 END IF;
340 IF (l_pmav_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
341 l_pmav_rec.last_updated_by := NULL;
342 END IF;
343 IF (l_pmav_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
344 l_pmav_rec.last_update_date := NULL;
345 END IF;
346 IF (l_pmav_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
347 l_pmav_rec.last_update_login := NULL;
348 END IF;
349
350 -- R12 Data Model Changes 4485150 Start
351 IF (l_pmav_rec.orig_system_id1 = OKC_API.G_MISS_NUM ) THEN
352 l_pmav_rec.orig_system_id1 := NULL;
353 END IF;
354 IF (l_pmav_rec.orig_system_reference1 = OKC_API.G_MISS_CHAR) THEN
355 l_pmav_rec.orig_system_reference1 := NULL;
356 END IF;
357 IF (l_pmav_rec.orig_system_source_code = OKC_API.G_MISS_CHAR ) THEN
358 l_pmav_rec.orig_system_source_code := NULL;
359 END IF;
360 -- R12 Data Model Changes 4485150 End
361
362
363 RETURN(l_pmav_rec);
364 END null_out_defaults;
365 ---------------------------------
366 -- Validate_Attributes for: ID --
367 ---------------------------------
368 PROCEDURE validate_id(
369 x_return_status OUT NOCOPY VARCHAR2,
370 p_id IN NUMBER) IS
371 BEGIN
372 x_return_status := OKC_API.G_RET_STS_SUCCESS;
373 IF (p_id = OKC_API.G_MISS_NUM OR
374 p_id IS NULL)
375 THEN
376 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
377 x_return_status := OKC_API.G_RET_STS_ERROR;
378 RAISE G_EXCEPTION_HALT_VALIDATION;
379 END IF;
380 EXCEPTION
381 WHEN G_EXCEPTION_HALT_VALIDATION THEN
382 null;
383 WHEN OTHERS THEN
384 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
385 ,p_msg_name => G_UNEXPECTED_ERROR
386 ,p_token1 => G_SQLCODE_TOKEN
387 ,p_token1_value => SQLCODE
388 ,p_token2 => G_SQLERRM_TOKEN
389 ,p_token2_value => SQLERRM);
390 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
391 END validate_id;
392 ---------------------------------------------------------------------------
393 -- FUNCTION Validate_Attributes
394 ---------------------------------------------------------------------------
395 -------------------------------------------------
396 -- Validate_Attributes for:OKS_PM_ACTIVITIES_V --
397 -------------------------------------------------
398 FUNCTION Validate_Attributes (
399 p_pmav_rec IN pmav_rec_type
400 ) RETURN VARCHAR2 IS
401 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
402 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
403 BEGIN
404 -----------------------------
405 -- Column Level Validation --
406 -----------------------------
407 -- ***
408 -- id
409 -- ***
410 validate_id(x_return_status, p_pmav_rec.id);
411 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
412 l_return_status := x_return_status;
413 RAISE G_EXCEPTION_HALT_VALIDATION;
414 END IF;
415
416 RETURN(l_return_status);
417 EXCEPTION
418 WHEN G_EXCEPTION_HALT_VALIDATION THEN
419 RETURN(l_return_status);
420 WHEN OTHERS THEN
421 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
422 ,p_msg_name => G_UNEXPECTED_ERROR
423 ,p_token1 => G_SQLCODE_TOKEN
424 ,p_token1_value => SQLCODE
425 ,p_token2 => G_SQLERRM_TOKEN
426 ,p_token2_value => SQLERRM);
427 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
428 RETURN(l_return_status);
429 END Validate_Attributes;
430 ---------------------------------------------------------------------------
431 -- PROCEDURE Validate_Record
432 ---------------------------------------------------------------------------
433 ---------------------------------------------
434 -- Validate Record for:OKS_PM_ACTIVITIES_V --
435 ---------------------------------------------
436 FUNCTION Validate_Record (
437 p_pmav_rec IN pmav_rec_type,
438 p_db_pmav_rec IN pmav_rec_type
439 ) RETURN VARCHAR2 IS
440 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
441 BEGIN
442 RETURN (l_return_status);
443 END Validate_Record;
444 FUNCTION Validate_Record (
445 p_pmav_rec IN pmav_rec_type
446 ) RETURN VARCHAR2 IS
447 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
448 l_db_pmav_rec pmav_rec_type := get_rec(p_pmav_rec);
449 BEGIN
450 l_return_status := Validate_Record(p_pmav_rec => p_pmav_rec,
451 p_db_pmav_rec => l_db_pmav_rec);
452 RETURN (l_return_status);
453 END Validate_Record;
454
455 ---------------------------------------------------------------------------
456 -- PROCEDURE Migrate
457 ---------------------------------------------------------------------------
458 PROCEDURE migrate (
459 p_from IN pmav_rec_type,
460 p_to IN OUT NOCOPY pma_rec_type
461 ) IS
462 BEGIN
463 p_to.id := p_from.id;
464 p_to.cle_id := p_from.cle_id;
465 p_to.dnz_chr_id := p_from.dnz_chr_id;
466 p_to.activity_id := p_from.activity_id;
467 p_to.select_yn := p_from.select_yn;
468 p_to.conf_req_yn := p_from.conf_req_yn;
469 p_to.sch_exists_yn := p_from.sch_exists_yn;
470 p_to.program_application_id := p_from.program_application_id;
471 p_to.program_id := p_from.program_id;
472 p_to.program_update_date := p_from.program_update_date;
473 p_to.object_version_number := p_from.object_version_number;
474 p_to.request_id := p_from.request_id;
475 p_to.created_by := p_from.created_by;
476 p_to.creation_date := p_from.creation_date;
477 p_to.last_updated_by := p_from.last_updated_by;
478 p_to.last_update_date := p_from.last_update_date;
479 p_to.last_update_login := p_from.last_update_login;
480 -- R12 Data Model Changes 4485150 Start
481 p_to.orig_system_id1 := p_from.orig_system_id1;
482 p_to.orig_system_reference1 := p_from.orig_system_reference1;
483 p_to.orig_system_source_code := p_from.orig_system_source_code;
484 -- R12 Data Model Changes 4485150 End
485 END migrate;
486 PROCEDURE migrate (
487 p_from IN pma_rec_type,
488 p_to IN OUT NOCOPY pmav_rec_type
489 ) IS
490 BEGIN
491 p_to.id := p_from.id;
492 p_to.cle_id := p_from.cle_id;
493 p_to.dnz_chr_id := p_from.dnz_chr_id;
494 p_to.activity_id := p_from.activity_id;
495 p_to.select_yn := p_from.select_yn;
496 p_to.conf_req_yn := p_from.conf_req_yn;
497 p_to.sch_exists_yn := p_from.sch_exists_yn;
498 p_to.program_application_id := p_from.program_application_id;
499 p_to.program_id := p_from.program_id;
500 p_to.program_update_date := p_from.program_update_date;
501 p_to.object_version_number := p_from.object_version_number;
502 p_to.request_id := p_from.request_id;
503 p_to.created_by := p_from.created_by;
504 p_to.creation_date := p_from.creation_date;
505 p_to.last_updated_by := p_from.last_updated_by;
506 p_to.last_update_date := p_from.last_update_date;
507 p_to.last_update_login := p_from.last_update_login;
508 -- R12 Data Model Changes 4485150 Start
509 p_to.orig_system_id1 := p_from.orig_system_id1;
510 p_to.orig_system_reference1 := p_from.orig_system_reference1;
511 p_to.orig_system_source_code := p_from.orig_system_source_code;
512 -- R12 Data Model Changes 4485150 End
513 END migrate;
514 ---------------------------------------------------------------------------
515 -- PROCEDURE validate_row
516 ---------------------------------------------------------------------------
517 ------------------------------------------
518 -- validate_row for:OKS_PM_ACTIVITIES_V --
519 ------------------------------------------
520 PROCEDURE validate_row(
521 p_api_version IN NUMBER,
522 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
523 x_return_status OUT NOCOPY VARCHAR2,
524 x_msg_count OUT NOCOPY NUMBER,
525 x_msg_data OUT NOCOPY VARCHAR2,
526 p_pmav_rec IN pmav_rec_type) IS
527
528 l_api_version CONSTANT NUMBER := 1;
529 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
530 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
531 l_pmav_rec pmav_rec_type := p_pmav_rec;
532 l_pma_rec pma_rec_type;
533 l_pma_rec pma_rec_type;
534 BEGIN
535 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
536 G_PKG_NAME,
537 p_init_msg_list,
538 l_api_version,
539 p_api_version,
540 '_PVT',
541 x_return_status);
542 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
543 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
544 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
545 RAISE OKC_API.G_EXCEPTION_ERROR;
546 END IF;
547 --- Validate all non-missing attributes (Item Level Validation)
548 l_return_status := Validate_Attributes(l_pmav_rec);
549 --- If any errors happen abort API
550 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
551 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
552 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
553 RAISE OKC_API.G_EXCEPTION_ERROR;
554 END IF;
555 l_return_status := Validate_Record(l_pmav_rec);
556 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
557 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
558 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
559 RAISE OKC_API.G_EXCEPTION_ERROR;
560 END IF;
561 x_return_status := l_return_status;
562 EXCEPTION
563 WHEN OKC_API.G_EXCEPTION_ERROR THEN
564 x_return_status := OKC_API.HANDLE_EXCEPTIONS
565 (
566 l_api_name,
567 G_PKG_NAME,
568 'OKC_API.G_RET_STS_ERROR',
569 x_msg_count,
570 x_msg_data,
571 '_PVT'
572 );
573 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
574 x_return_status := OKC_API.HANDLE_EXCEPTIONS
575 (
576 l_api_name,
577 G_PKG_NAME,
578 'OKC_API.G_RET_STS_UNEXP_ERROR',
579 x_msg_count,
580 x_msg_data,
581 '_PVT'
582 );
583 WHEN OTHERS THEN
584 x_return_status := OKC_API.HANDLE_EXCEPTIONS
585 (
586 l_api_name,
587 G_PKG_NAME,
588 'OTHERS',
589 x_msg_count,
590 x_msg_data,
591 '_PVT'
592 );
593 END validate_row;
594 -----------------------------------------------------
595 -- PL/SQL TBL validate_row for:OKS_PM_ACTIVITIES_V --
596 -----------------------------------------------------
597 PROCEDURE validate_row(
598 p_api_version IN NUMBER,
599 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
600 x_return_status OUT NOCOPY VARCHAR2,
601 x_msg_count OUT NOCOPY NUMBER,
602 x_msg_data OUT NOCOPY VARCHAR2,
603 p_pmav_tbl IN pmav_tbl_type,
604 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
605
606 l_api_version CONSTANT NUMBER := 1;
607 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
608 i NUMBER := 0;
609 BEGIN
610 OKC_API.init_msg_list(p_init_msg_list);
611 -- Make sure PL/SQL table has records in it before passing
612 IF (p_pmav_tbl.COUNT > 0) THEN
613 i := p_pmav_tbl.FIRST;
614 LOOP
615 DECLARE
616 l_error_rec OKC_API.ERROR_REC_TYPE;
617 BEGIN
618 l_error_rec.api_name := l_api_name;
619 l_error_rec.api_package := G_PKG_NAME;
620 l_error_rec.idx := i;
621 validate_row (
622 p_api_version => p_api_version,
623 p_init_msg_list => OKC_API.G_FALSE,
624 x_return_status => l_error_rec.error_type,
625 x_msg_count => l_error_rec.msg_count,
626 x_msg_data => l_error_rec.msg_data,
627 p_pmav_rec => p_pmav_tbl(i));
628 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
629 l_error_rec.sqlcode := SQLCODE;
630 load_error_tbl(l_error_rec, px_error_tbl);
631 ELSE
632 x_msg_count := l_error_rec.msg_count;
633 x_msg_data := l_error_rec.msg_data;
634 END IF;
635 EXCEPTION
636 WHEN OKC_API.G_EXCEPTION_ERROR THEN
637 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
638 l_error_rec.sqlcode := SQLCODE;
639 load_error_tbl(l_error_rec, px_error_tbl);
640 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
641 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
642 l_error_rec.sqlcode := SQLCODE;
643 load_error_tbl(l_error_rec, px_error_tbl);
644 WHEN OTHERS THEN
645 l_error_rec.error_type := 'OTHERS';
646 l_error_rec.sqlcode := SQLCODE;
647 load_error_tbl(l_error_rec, px_error_tbl);
648 END;
649 EXIT WHEN (i = p_pmav_tbl.LAST);
650 i := p_pmav_tbl.NEXT(i);
651 END LOOP;
652 END IF;
653 -- Loop through the error_tbl to find the error with the highest severity
654 -- and return it.
655 x_return_status := find_highest_exception(px_error_tbl);
656 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
657 EXCEPTION
658 WHEN OKC_API.G_EXCEPTION_ERROR THEN
659 x_return_status := OKC_API.HANDLE_EXCEPTIONS
660 (
661 l_api_name,
662 G_PKG_NAME,
663 'OKC_API.G_RET_STS_ERROR',
664 x_msg_count,
665 x_msg_data,
666 '_PVT'
667 );
668 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
669 x_return_status := OKC_API.HANDLE_EXCEPTIONS
670 (
671 l_api_name,
672 G_PKG_NAME,
673 'OKC_API.G_RET_STS_UNEXP_ERROR',
674 x_msg_count,
675 x_msg_data,
676 '_PVT'
677 );
678 WHEN OTHERS THEN
679 x_return_status := OKC_API.HANDLE_EXCEPTIONS
680 (
681 l_api_name,
682 G_PKG_NAME,
683 'OTHERS',
684 x_msg_count,
685 x_msg_data,
686 '_PVT'
687 );
688 END validate_row;
689
690 -----------------------------------------------------
691 -- PL/SQL TBL validate_row for:OKS_PM_ACTIVITIES_V --
692 -----------------------------------------------------
693 PROCEDURE validate_row(
694 p_api_version IN NUMBER,
695 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2,
699 p_pmav_tbl IN pmav_tbl_type) IS
700
701 l_api_version CONSTANT NUMBER := 1;
702 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
703 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
704 l_error_tbl OKC_API.ERROR_TBL_TYPE;
705 BEGIN
706 OKC_API.init_msg_list(p_init_msg_list);
707 -- Make sure PL/SQL table has records in it before passing
708 IF (p_pmav_tbl.COUNT > 0) THEN
709 validate_row (
710 p_api_version => p_api_version,
711 p_init_msg_list => OKC_API.G_FALSE,
712 x_return_status => x_return_status,
713 x_msg_count => x_msg_count,
714 x_msg_data => x_msg_data,
715 p_pmav_tbl => p_pmav_tbl,
716 px_error_tbl => l_error_tbl);
717 END IF;
718 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
719 EXCEPTION
720 WHEN OKC_API.G_EXCEPTION_ERROR THEN
721 x_return_status := OKC_API.HANDLE_EXCEPTIONS
722 (
723 l_api_name,
724 G_PKG_NAME,
725 'OKC_API.G_RET_STS_ERROR',
726 x_msg_count,
727 x_msg_data,
728 '_PVT'
729 );
730 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
731 x_return_status := OKC_API.HANDLE_EXCEPTIONS
732 (
733 l_api_name,
734 G_PKG_NAME,
735 'OKC_API.G_RET_STS_UNEXP_ERROR',
736 x_msg_count,
737 x_msg_data,
738 '_PVT'
739 );
740 WHEN OTHERS THEN
741 x_return_status := OKC_API.HANDLE_EXCEPTIONS
742 (
743 l_api_name,
744 G_PKG_NAME,
745 'OTHERS',
746 x_msg_count,
747 x_msg_data,
748 '_PVT'
749 );
750 END validate_row;
751
752 ---------------------------------------------------------------------------
753 -- PROCEDURE insert_row
754 ---------------------------------------------------------------------------
755 --------------------------------------
756 -- insert_row for:OKS_PM_ACTIVITIES --
757 --------------------------------------
758 PROCEDURE insert_row(
759 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
760 x_return_status OUT NOCOPY VARCHAR2,
761 x_msg_count OUT NOCOPY NUMBER,
762 x_msg_data OUT NOCOPY VARCHAR2,
763 p_pma_rec IN pma_rec_type,
764 x_pma_rec OUT NOCOPY pma_rec_type) IS
765
766 l_api_version CONSTANT NUMBER := 1;
767 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
768 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
769 l_pma_rec pma_rec_type := p_pma_rec;
770 l_def_pma_rec pma_rec_type;
771 ------------------------------------------
772 -- Set_Attributes for:OKS_PM_ACTIVITIES --
773 ------------------------------------------
774 FUNCTION Set_Attributes (
775 p_pma_rec IN pma_rec_type,
776 x_pma_rec OUT NOCOPY pma_rec_type
777 ) RETURN VARCHAR2 IS
778 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
779 BEGIN
780 x_pma_rec := p_pma_rec;
781 RETURN(l_return_status);
782 END Set_Attributes;
783 BEGIN
784 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
785 p_init_msg_list,
786 '_PVT',
787 x_return_status);
788 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
789 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
790 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
791 RAISE OKC_API.G_EXCEPTION_ERROR;
792 END IF;
793 --- Setting item atributes
794 l_return_status := Set_Attributes(
795 p_pma_rec, -- IN
796 l_pma_rec); -- OUT
797 --- If any errors happen abort API
798 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
799 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
800 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
801 RAISE OKC_API.G_EXCEPTION_ERROR;
802 END IF;
803 INSERT INTO OKS_PM_ACTIVITIES(
804 id,
805 cle_id,
806 dnz_chr_id,
807 activity_id,
808 select_yn,
809 conf_req_yn,
810 sch_exists_yn,
811 program_application_id,
812 program_id,
813 program_update_date,
814 object_version_number,
815 request_id,
816 created_by,
817 creation_date,
818 last_updated_by,
819 last_update_date,
820 last_update_login,
821 -- R12 Data Model Changes 4485150 Start
822 orig_system_id1,
823 orig_system_reference1,
824 orig_system_source_code
825 -- R12 Data Model Changes 4485150 End
826 )
827 VALUES (
828 l_pma_rec.id,
829 l_pma_rec.cle_id,
830 l_pma_rec.dnz_chr_id,
831 l_pma_rec.activity_id,
832 l_pma_rec.select_yn,
833 l_pma_rec.conf_req_yn,
834 l_pma_rec.sch_exists_yn,
835 l_pma_rec.program_application_id,
836 l_pma_rec.program_id,
837 l_pma_rec.program_update_date,
838 l_pma_rec.object_version_number,
839 l_pma_rec.request_id,
840 l_pma_rec.created_by,
841 l_pma_rec.creation_date,
842 l_pma_rec.last_updated_by,
843 l_pma_rec.last_update_date,
844 l_pma_rec.last_update_login,
845 -- R12 Data Model Changes 4485150 Start
846 l_pma_rec.orig_system_id1,
847 l_pma_rec.orig_system_reference1,
848 l_pma_rec.orig_system_source_code
849 -- R12 Data Model Changes 4485150 End
850 );
851 -- Set OUT values
852 x_pma_rec := l_pma_rec;
853 x_return_status := l_return_status;
854 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
855 EXCEPTION
856 WHEN OKC_API.G_EXCEPTION_ERROR THEN
857 x_return_status := OKC_API.HANDLE_EXCEPTIONS
858 (
859 l_api_name,
860 G_PKG_NAME,
861 'OKC_API.G_RET_STS_ERROR',
862 x_msg_count,
863 x_msg_data,
864 '_PVT'
865 );
866 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
867 x_return_status := OKC_API.HANDLE_EXCEPTIONS
868 (
869 l_api_name,
870 G_PKG_NAME,
871 'OKC_API.G_RET_STS_UNEXP_ERROR',
872 x_msg_count,
873 x_msg_data,
874 '_PVT'
875 );
876 WHEN OTHERS THEN
877 x_return_status := OKC_API.HANDLE_EXCEPTIONS
878 (
879 l_api_name,
880 G_PKG_NAME,
881 'OTHERS',
882 x_msg_count,
883 x_msg_data,
884 '_PVT'
885 );
886 END insert_row;
887 -----------------------------------------
888 -- insert_row for :OKS_PM_ACTIVITIES_V --
889 -----------------------------------------
890 PROCEDURE insert_row(
891 p_api_version IN NUMBER,
892 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
893 x_return_status OUT NOCOPY VARCHAR2,
894 x_msg_count OUT NOCOPY NUMBER,
895 x_msg_data OUT NOCOPY VARCHAR2,
896 p_pmav_rec IN pmav_rec_type,
897 x_pmav_rec OUT NOCOPY pmav_rec_type) IS
898
899 l_api_version CONSTANT NUMBER := 1;
900 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
901 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
902 l_pmav_rec pmav_rec_type := p_pmav_rec;
903 l_def_pmav_rec pmav_rec_type;
904 l_pma_rec pma_rec_type;
905 lx_pma_rec pma_rec_type;
906 -------------------------------
907 -- FUNCTION fill_who_columns --
908 -------------------------------
909 FUNCTION fill_who_columns (
910 p_pmav_rec IN pmav_rec_type
911 ) RETURN pmav_rec_type IS
912 l_pmav_rec pmav_rec_type := p_pmav_rec;
913 BEGIN
914 l_pmav_rec.CREATION_DATE := SYSDATE;
915 l_pmav_rec.CREATED_BY := FND_GLOBAL.USER_ID;
916 l_pmav_rec.LAST_UPDATE_DATE := l_pmav_rec.CREATION_DATE;
917 l_pmav_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
918 l_pmav_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
919 RETURN(l_pmav_rec);
920 END fill_who_columns;
921 --------------------------------------------
922 -- Set_Attributes for:OKS_PM_ACTIVITIES_V --
923 --------------------------------------------
924 FUNCTION Set_Attributes (
925 p_pmav_rec IN pmav_rec_type,
926 x_pmav_rec OUT NOCOPY pmav_rec_type
927 ) RETURN VARCHAR2 IS
928 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
929 BEGIN
930 x_pmav_rec := p_pmav_rec;
931 x_pmav_rec.OBJECT_VERSION_NUMBER := 1;
932 RETURN(l_return_status);
933 END Set_Attributes;
934 BEGIN
935 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
936 G_PKG_NAME,
937 p_init_msg_list,
938 l_api_version,
939 p_api_version,
940 '_PVT',
941 x_return_status);
942 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
943 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
944 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
945 RAISE OKC_API.G_EXCEPTION_ERROR;
946 END IF;
947 l_pmav_rec := null_out_defaults(p_pmav_rec);
948 -- Set primary key value
949 l_pmav_rec.ID := get_seq_id;
950 -- Setting item attributes
951 l_return_Status := Set_Attributes(
952 l_pmav_rec, -- IN
953 l_def_pmav_rec); -- OUT
954 --- If any errors happen abort API
955 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
956 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
957 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
958 RAISE OKC_API.G_EXCEPTION_ERROR;
959 END IF;
960 l_def_pmav_rec := fill_who_columns(l_def_pmav_rec);
961 --- Validate all non-missing attributes (Item Level Validation)
962 l_return_status := Validate_Attributes(l_def_pmav_rec);
963 --- If any errors happen abort API
964 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
965 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
966 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
967 RAISE OKC_API.G_EXCEPTION_ERROR;
968 END IF;
969 l_return_status := Validate_Record(l_def_pmav_rec);
970 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
971 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
972 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
973 RAISE OKC_API.G_EXCEPTION_ERROR;
974 END IF;
975 -----------------------------------------
976 -- Move VIEW record to "Child" records --
977 -----------------------------------------
978 migrate(l_def_pmav_rec, l_pma_rec);
979 -----------------------------------------------
980 -- Call the INSERT_ROW for each child record --
981 -----------------------------------------------
982 insert_row(
983 p_init_msg_list,
984 l_return_status,
985 x_msg_count,
986 x_msg_data,
987 l_pma_rec,
988 lx_pma_rec
989 );
990 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
991 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
992 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
993 RAISE OKC_API.G_EXCEPTION_ERROR;
994 END IF;
995 migrate(lx_pma_rec, l_def_pmav_rec);
996 -- Set OUT values
997 x_pmav_rec := l_def_pmav_rec;
998 x_return_status := l_return_status;
999 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1000 EXCEPTION
1001 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1002 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1003 (
1004 l_api_name,
1005 G_PKG_NAME,
1006 'OKC_API.G_RET_STS_ERROR',
1007 x_msg_count,
1008 x_msg_data,
1009 '_PVT'
1010 );
1011 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1012 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1013 (
1014 l_api_name,
1015 G_PKG_NAME,
1016 'OKC_API.G_RET_STS_UNEXP_ERROR',
1017 x_msg_count,
1018 x_msg_data,
1019 '_PVT'
1020 );
1021 WHEN OTHERS THEN
1022 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1023 (
1024 l_api_name,
1025 G_PKG_NAME,
1026 'OTHERS',
1027 x_msg_count,
1028 x_msg_data,
1029 '_PVT'
1030 );
1031 END insert_row;
1032 ----------------------------------------
1033 -- PL/SQL TBL insert_row for:PMAV_TBL --
1034 ----------------------------------------
1035 PROCEDURE insert_row(
1036 p_api_version IN NUMBER,
1037 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1038 x_return_status OUT NOCOPY VARCHAR2,
1039 x_msg_count OUT NOCOPY NUMBER,
1040 x_msg_data OUT NOCOPY VARCHAR2,
1041 p_pmav_tbl IN pmav_tbl_type,
1042 x_pmav_tbl OUT NOCOPY pmav_tbl_type,
1043 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1044
1045 l_api_version CONSTANT NUMBER := 1;
1046 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1047 i NUMBER := 0;
1048 BEGIN
1049 OKC_API.init_msg_list(p_init_msg_list);
1050 -- Make sure PL/SQL table has records in it before passing
1051 IF (p_pmav_tbl.COUNT > 0) THEN
1052 i := p_pmav_tbl.FIRST;
1053 LOOP
1054 DECLARE
1055 l_error_rec OKC_API.ERROR_REC_TYPE;
1056 BEGIN
1057 l_error_rec.api_name := l_api_name;
1058 l_error_rec.api_package := G_PKG_NAME;
1059 l_error_rec.idx := i;
1060 insert_row (
1061 p_api_version => p_api_version,
1062 p_init_msg_list => OKC_API.G_FALSE,
1063 x_return_status => l_error_rec.error_type,
1064 x_msg_count => l_error_rec.msg_count,
1065 x_msg_data => l_error_rec.msg_data,
1066 p_pmav_rec => p_pmav_tbl(i),
1067 x_pmav_rec => x_pmav_tbl(i));
1068 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1069 l_error_rec.sqlcode := SQLCODE;
1070 load_error_tbl(l_error_rec, px_error_tbl);
1071 ELSE
1072 x_msg_count := l_error_rec.msg_count;
1073 x_msg_data := l_error_rec.msg_data;
1074 END IF;
1075 EXCEPTION
1076 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1077 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1078 l_error_rec.sqlcode := SQLCODE;
1079 load_error_tbl(l_error_rec, px_error_tbl);
1080 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1081 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1082 l_error_rec.sqlcode := SQLCODE;
1083 load_error_tbl(l_error_rec, px_error_tbl);
1084 WHEN OTHERS THEN
1085 l_error_rec.error_type := 'OTHERS';
1086 l_error_rec.sqlcode := SQLCODE;
1087 load_error_tbl(l_error_rec, px_error_tbl);
1088 END;
1089 EXIT WHEN (i = p_pmav_tbl.LAST);
1090 i := p_pmav_tbl.NEXT(i);
1091 END LOOP;
1092 END IF;
1093 -- Loop through the error_tbl to find the error with the highest severity
1094 -- and return it.
1095 x_return_status := find_highest_exception(px_error_tbl);
1096 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1097 EXCEPTION
1098 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1099 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1100 (
1101 l_api_name,
1102 G_PKG_NAME,
1103 'OKC_API.G_RET_STS_ERROR',
1104 x_msg_count,
1105 x_msg_data,
1106 '_PVT'
1107 );
1108 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1109 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1110 (
1111 l_api_name,
1112 G_PKG_NAME,
1113 'OKC_API.G_RET_STS_UNEXP_ERROR',
1114 x_msg_count,
1115 x_msg_data,
1116 '_PVT'
1117 );
1118 WHEN OTHERS THEN
1119 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1120 (
1121 l_api_name,
1122 G_PKG_NAME,
1123 'OTHERS',
1124 x_msg_count,
1125 x_msg_data,
1126 '_PVT'
1127 );
1128 END insert_row;
1129
1130 ----------------------------------------
1131 -- PL/SQL TBL insert_row for:PMAV_TBL --
1132 ----------------------------------------
1133 PROCEDURE insert_row(
1134 p_api_version IN NUMBER,
1135 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1136 x_return_status OUT NOCOPY VARCHAR2,
1137 x_msg_count OUT NOCOPY NUMBER,
1138 x_msg_data OUT NOCOPY VARCHAR2,
1139 p_pmav_tbl IN pmav_tbl_type,
1140 x_pmav_tbl OUT NOCOPY pmav_tbl_type) IS
1141
1142 l_api_version CONSTANT NUMBER := 1;
1143 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1144 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1145 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1146 BEGIN
1147 OKC_API.init_msg_list(p_init_msg_list);
1148 -- Make sure PL/SQL table has records in it before passing
1149 IF (p_pmav_tbl.COUNT > 0) THEN
1150 insert_row (
1151 p_api_version => p_api_version,
1152 p_init_msg_list => OKC_API.G_FALSE,
1153 x_return_status => x_return_status,
1154 x_msg_count => x_msg_count,
1155 x_msg_data => x_msg_data,
1156 p_pmav_tbl => p_pmav_tbl,
1157 x_pmav_tbl => x_pmav_tbl,
1158 px_error_tbl => l_error_tbl);
1159 END IF;
1160 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1161 EXCEPTION
1162 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1163 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1164 (
1165 l_api_name,
1166 G_PKG_NAME,
1167 'OKC_API.G_RET_STS_ERROR',
1168 x_msg_count,
1169 x_msg_data,
1170 '_PVT'
1171 );
1172 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1173 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1174 (
1175 l_api_name,
1176 G_PKG_NAME,
1177 'OKC_API.G_RET_STS_UNEXP_ERROR',
1178 x_msg_count,
1179 x_msg_data,
1180 '_PVT'
1181 );
1182 WHEN OTHERS THEN
1183 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1184 (
1185 l_api_name,
1186 G_PKG_NAME,
1187 'OTHERS',
1188 x_msg_count,
1189 x_msg_data,
1190 '_PVT'
1191 );
1192 END insert_row;
1193
1194 ---------------------------------------------------------------------------
1195 -- PROCEDURE lock_row
1196 ---------------------------------------------------------------------------
1197 ------------------------------------
1198 -- lock_row for:OKS_PM_ACTIVITIES --
1199 ------------------------------------
1200 PROCEDURE lock_row(
1201 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1202 x_return_status OUT NOCOPY VARCHAR2,
1203 x_msg_count OUT NOCOPY NUMBER,
1204 x_msg_data OUT NOCOPY VARCHAR2,
1205 p_pma_rec IN pma_rec_type) IS
1206
1207 E_Resource_Busy EXCEPTION;
1208 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1209 CURSOR lock_csr (p_pma_rec IN pma_rec_type) IS
1210 SELECT OBJECT_VERSION_NUMBER
1211 FROM OKS_PM_ACTIVITIES
1212 WHERE ID = p_pma_rec.id
1213 AND OBJECT_VERSION_NUMBER = p_pma_rec.object_version_number
1214 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1215
1216 CURSOR lchk_csr (p_pma_rec IN pma_rec_type) IS
1217 SELECT OBJECT_VERSION_NUMBER
1218 FROM OKS_PM_ACTIVITIES
1219 WHERE ID = p_pma_rec.id;
1220 l_api_version CONSTANT NUMBER := 1;
1221 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1222 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1223 l_object_version_number OKS_PM_ACTIVITIES.OBJECT_VERSION_NUMBER%TYPE;
1224 lc_object_version_number OKS_PM_ACTIVITIES.OBJECT_VERSION_NUMBER%TYPE;
1225 l_row_notfound BOOLEAN := FALSE;
1226 lc_row_notfound BOOLEAN := FALSE;
1227 BEGIN
1228 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1229 p_init_msg_list,
1230 '_PVT',
1231 x_return_status);
1232 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1233 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1234 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1235 RAISE OKC_API.G_EXCEPTION_ERROR;
1236 END IF;
1237 BEGIN
1238 OPEN lock_csr(p_pma_rec);
1239 FETCH lock_csr INTO l_object_version_number;
1240 l_row_notfound := lock_csr%NOTFOUND;
1241 CLOSE lock_csr;
1242 EXCEPTION
1243 WHEN E_Resource_Busy THEN
1244 IF (lock_csr%ISOPEN) THEN
1245 CLOSE lock_csr;
1246 END IF;
1247 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1248 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1249 END;
1250
1251 IF ( l_row_notfound ) THEN
1252 OPEN lchk_csr(p_pma_rec);
1253 FETCH lchk_csr INTO lc_object_version_number;
1254 lc_row_notfound := lchk_csr%NOTFOUND;
1255 CLOSE lchk_csr;
1256 END IF;
1257 IF (lc_row_notfound) THEN
1258 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1259 RAISE OKC_API.G_EXCEPTION_ERROR;
1260 ELSIF lc_object_version_number > p_pma_rec.object_version_number THEN
1261 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1262 RAISE OKC_API.G_EXCEPTION_ERROR;
1263 ELSIF lc_object_version_number <> p_pma_rec.object_version_number THEN
1264 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1265 RAISE OKC_API.G_EXCEPTION_ERROR;
1266 ELSIF lc_object_version_number = -1 THEN
1267 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1268 RAISE OKC_API.G_EXCEPTION_ERROR;
1269 END IF;
1270 x_return_status := l_return_status;
1271 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1272 EXCEPTION
1273 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1274 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1275 (
1276 l_api_name,
1277 G_PKG_NAME,
1278 'OKC_API.G_RET_STS_ERROR',
1279 x_msg_count,
1280 x_msg_data,
1281 '_PVT'
1282 );
1283 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1284 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1285 (
1286 l_api_name,
1287 G_PKG_NAME,
1288 'OKC_API.G_RET_STS_UNEXP_ERROR',
1289 x_msg_count,
1290 x_msg_data,
1291 '_PVT'
1292 );
1293 WHEN OTHERS THEN
1294 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1295 (
1296 l_api_name,
1297 G_PKG_NAME,
1298 'OTHERS',
1299 x_msg_count,
1300 x_msg_data,
1301 '_PVT'
1302 );
1303 END lock_row;
1304 ---------------------------------------
1305 -- lock_row for: OKS_PM_ACTIVITIES_V --
1306 ---------------------------------------
1307 PROCEDURE lock_row(
1308 p_api_version IN NUMBER,
1309 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1310 x_return_status OUT NOCOPY VARCHAR2,
1311 x_msg_count OUT NOCOPY NUMBER,
1312 x_msg_data OUT NOCOPY VARCHAR2,
1313 p_pmav_rec IN pmav_rec_type) IS
1314
1315 l_api_version CONSTANT NUMBER := 1;
1316 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1317 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1318 l_pma_rec pma_rec_type;
1319 BEGIN
1320 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1321 G_PKG_NAME,
1322 p_init_msg_list,
1323 l_api_version,
1324 p_api_version,
1325 '_PVT',
1326 x_return_status);
1327 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1328 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1329 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1330 RAISE OKC_API.G_EXCEPTION_ERROR;
1331 END IF;
1332 -----------------------------------------
1333 -- Move VIEW record to "Child" records --
1334 -----------------------------------------
1335 migrate(p_pmav_rec, l_pma_rec);
1336 ---------------------------------------------
1337 -- Call the LOCK_ROW for each child record --
1338 ---------------------------------------------
1339 lock_row(
1340 p_init_msg_list,
1341 l_return_status,
1342 x_msg_count,
1343 x_msg_data,
1344 l_pma_rec
1345 );
1346 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1347 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1348 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1349 RAISE OKC_API.G_EXCEPTION_ERROR;
1350 END IF;
1351 x_return_status := l_return_status;
1352 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1353 EXCEPTION
1354 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1355 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1356 (
1357 l_api_name,
1358 G_PKG_NAME,
1359 'OKC_API.G_RET_STS_ERROR',
1360 x_msg_count,
1361 x_msg_data,
1362 '_PVT'
1363 );
1364 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1365 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1366 (
1367 l_api_name,
1368 G_PKG_NAME,
1369 'OKC_API.G_RET_STS_UNEXP_ERROR',
1370 x_msg_count,
1371 x_msg_data,
1372 '_PVT'
1373 );
1374 WHEN OTHERS THEN
1375 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1376 (
1377 l_api_name,
1378 G_PKG_NAME,
1379 'OTHERS',
1380 x_msg_count,
1381 x_msg_data,
1382 '_PVT'
1383 );
1384 END lock_row;
1385 --------------------------------------
1386 -- PL/SQL TBL lock_row for:PMAV_TBL --
1387 --------------------------------------
1388 PROCEDURE lock_row(
1389 p_api_version IN NUMBER,
1390 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1391 x_return_status OUT NOCOPY VARCHAR2,
1392 x_msg_count OUT NOCOPY NUMBER,
1393 x_msg_data OUT NOCOPY VARCHAR2,
1394 p_pmav_tbl IN pmav_tbl_type,
1395 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1396
1397 l_api_version CONSTANT NUMBER := 1;
1398 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1399 i NUMBER := 0;
1400 BEGIN
1401 OKC_API.init_msg_list(p_init_msg_list);
1402 -- Make sure PL/SQL table has recrods in it before passing
1403 IF (p_pmav_tbl.COUNT > 0) THEN
1404 i := p_pmav_tbl.FIRST;
1405 LOOP
1406 DECLARE
1407 l_error_rec OKC_API.ERROR_REC_TYPE;
1408 BEGIN
1409 l_error_rec.api_name := l_api_name;
1410 l_error_rec.api_package := G_PKG_NAME;
1411 l_error_rec.idx := i;
1412 lock_row(
1413 p_api_version => p_api_version,
1414 p_init_msg_list => OKC_API.G_FALSE,
1415 x_return_status => l_error_rec.error_type,
1416 x_msg_count => l_error_rec.msg_count,
1417 x_msg_data => l_error_rec.msg_data,
1418 p_pmav_rec => p_pmav_tbl(i));
1419 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1420 l_error_rec.sqlcode := SQLCODE;
1421 load_error_tbl(l_error_rec, px_error_tbl);
1422 ELSE
1423 x_msg_count := l_error_rec.msg_count;
1424 x_msg_data := l_error_rec.msg_data;
1425 END IF;
1426 EXCEPTION
1427 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1428 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1429 l_error_rec.sqlcode := SQLCODE;
1430 load_error_tbl(l_error_rec, px_error_tbl);
1431 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1432 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1433 l_error_rec.sqlcode := SQLCODE;
1434 load_error_tbl(l_error_rec, px_error_tbl);
1435 WHEN OTHERS THEN
1436 l_error_rec.error_type := 'OTHERS';
1437 l_error_rec.sqlcode := SQLCODE;
1438 load_error_tbl(l_error_rec, px_error_tbl);
1439 END;
1440 EXIT WHEN (i = p_pmav_tbl.LAST);
1441 i := p_pmav_tbl.NEXT(i);
1442 END LOOP;
1443 END IF;
1444 -- Loop through the error_tbl to find the error with the highest severity
1445 -- and return it.
1446 x_return_status := find_highest_exception(px_error_tbl);
1447 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1448 EXCEPTION
1449 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1450 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1451 (
1452 l_api_name,
1453 G_PKG_NAME,
1454 'OKC_API.G_RET_STS_ERROR',
1455 x_msg_count,
1456 x_msg_data,
1457 '_PVT'
1458 );
1459 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1460 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1461 (
1462 l_api_name,
1463 G_PKG_NAME,
1464 'OKC_API.G_RET_STS_UNEXP_ERROR',
1465 x_msg_count,
1466 x_msg_data,
1467 '_PVT'
1468 );
1469 WHEN OTHERS THEN
1470 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1471 (
1472 l_api_name,
1473 G_PKG_NAME,
1474 'OTHERS',
1475 x_msg_count,
1476 x_msg_data,
1477 '_PVT'
1478 );
1479 END lock_row;
1480 --------------------------------------
1481 -- PL/SQL TBL lock_row for:PMAV_TBL --
1482 --------------------------------------
1483 PROCEDURE lock_row(
1484 p_api_version IN NUMBER,
1485 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1486 x_return_status OUT NOCOPY VARCHAR2,
1487 x_msg_count OUT NOCOPY NUMBER,
1488 x_msg_data OUT NOCOPY VARCHAR2,
1489 p_pmav_tbl IN pmav_tbl_type) IS
1490
1491 l_api_version CONSTANT NUMBER := 1;
1492 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1493 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1494 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1495 BEGIN
1496 OKC_API.init_msg_list(p_init_msg_list);
1497 -- Make sure PL/SQL table has recrods in it before passing
1498 IF (p_pmav_tbl.COUNT > 0) THEN
1499 lock_row(
1500 p_api_version => p_api_version,
1501 p_init_msg_list => OKC_API.G_FALSE,
1502 x_return_status => x_return_status,
1503 x_msg_count => x_msg_count,
1504 x_msg_data => x_msg_data,
1505 p_pmav_tbl => p_pmav_tbl,
1506 px_error_tbl => l_error_tbl);
1507 END IF;
1508 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1509 EXCEPTION
1510 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1511 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1512 (
1513 l_api_name,
1514 G_PKG_NAME,
1515 'OKC_API.G_RET_STS_ERROR',
1516 x_msg_count,
1517 x_msg_data,
1518 '_PVT'
1519 );
1520 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1521 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1522 (
1523 l_api_name,
1524 G_PKG_NAME,
1525 'OKC_API.G_RET_STS_UNEXP_ERROR',
1526 x_msg_count,
1527 x_msg_data,
1528 '_PVT'
1529 );
1530 WHEN OTHERS THEN
1531 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1532 (
1533 l_api_name,
1534 G_PKG_NAME,
1535 'OTHERS',
1536 x_msg_count,
1537 x_msg_data,
1538 '_PVT'
1539 );
1540 END lock_row;
1541 ---------------------------------------------------------------------------
1542 -- PROCEDURE update_row
1543 ---------------------------------------------------------------------------
1544 --------------------------------------
1545 -- update_row for:OKS_PM_ACTIVITIES --
1546 --------------------------------------
1547 PROCEDURE update_row(
1548 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1549 x_return_status OUT NOCOPY VARCHAR2,
1550 x_msg_count OUT NOCOPY NUMBER,
1551 x_msg_data OUT NOCOPY VARCHAR2,
1552 p_pma_rec IN pma_rec_type,
1553 x_pma_rec OUT NOCOPY pma_rec_type) IS
1554
1555 l_api_version CONSTANT NUMBER := 1;
1556 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1557 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1558 l_pma_rec pma_rec_type := p_pma_rec;
1559 l_def_pma_rec pma_rec_type;
1560 l_row_notfound BOOLEAN := TRUE;
1561 ----------------------------------
1562 -- FUNCTION populate_new_record --
1563 ----------------------------------
1564 FUNCTION populate_new_record (
1565 p_pma_rec IN pma_rec_type,
1566 x_pma_rec OUT NOCOPY pma_rec_type
1567 ) RETURN VARCHAR2 IS
1568 l_pma_rec pma_rec_type;
1569 l_row_notfound BOOLEAN := TRUE;
1570 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1571 BEGIN
1572 x_pma_rec := p_pma_rec;
1573 -- Get current database values
1574 l_pma_rec := get_rec(p_pma_rec, l_return_status);
1575 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1576 IF (x_pma_rec.id = OKC_API.G_MISS_NUM)
1577 THEN
1578 x_pma_rec.id := l_pma_rec.id;
1579 END IF;
1580 IF (x_pma_rec.cle_id = OKC_API.G_MISS_NUM)
1581 THEN
1582 x_pma_rec.cle_id := l_pma_rec.cle_id;
1583 END IF;
1584 IF (x_pma_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1585 THEN
1586 x_pma_rec.dnz_chr_id := l_pma_rec.dnz_chr_id;
1587 END IF;
1588 IF (x_pma_rec.activity_id = OKC_API.G_MISS_NUM)
1589 THEN
1590 x_pma_rec.activity_id := l_pma_rec.activity_id;
1591 END IF;
1592 IF (x_pma_rec.select_yn = OKC_API.G_MISS_CHAR)
1593 THEN
1594 x_pma_rec.select_yn := l_pma_rec.select_yn;
1595 END IF;
1596 IF (x_pma_rec.conf_req_yn = OKC_API.G_MISS_CHAR)
1597 THEN
1598 x_pma_rec.conf_req_yn := l_pma_rec.conf_req_yn;
1599 END IF;
1600 IF (x_pma_rec.sch_exists_yn = OKC_API.G_MISS_CHAR)
1601 THEN
1602 x_pma_rec.sch_exists_yn := l_pma_rec.sch_exists_yn;
1603 END IF;
1604 IF (x_pma_rec.program_application_id = OKC_API.G_MISS_NUM)
1605 THEN
1606 x_pma_rec.program_application_id := l_pma_rec.program_application_id;
1607 END IF;
1608 IF (x_pma_rec.program_id = OKC_API.G_MISS_NUM)
1609 THEN
1610 x_pma_rec.program_id := l_pma_rec.program_id;
1611 END IF;
1612 IF (x_pma_rec.program_update_date = OKC_API.G_MISS_DATE)
1613 THEN
1614 x_pma_rec.program_update_date := l_pma_rec.program_update_date;
1615 END IF;
1616 IF (x_pma_rec.object_version_number = OKC_API.G_MISS_NUM)
1617 THEN
1618 x_pma_rec.object_version_number := l_pma_rec.object_version_number;
1619 END IF;
1620 IF (x_pma_rec.request_id = OKC_API.G_MISS_NUM)
1621 THEN
1622 x_pma_rec.request_id := l_pma_rec.request_id;
1623 END IF;
1624 IF (x_pma_rec.created_by = OKC_API.G_MISS_NUM)
1625 THEN
1626 x_pma_rec.created_by := l_pma_rec.created_by;
1627 END IF;
1628 IF (x_pma_rec.creation_date = OKC_API.G_MISS_DATE)
1629 THEN
1630 x_pma_rec.creation_date := l_pma_rec.creation_date;
1631 END IF;
1632 IF (x_pma_rec.last_updated_by = OKC_API.G_MISS_NUM)
1633 THEN
1634 x_pma_rec.last_updated_by := l_pma_rec.last_updated_by;
1635 END IF;
1636 IF (x_pma_rec.last_update_date = OKC_API.G_MISS_DATE)
1637 THEN
1638 x_pma_rec.last_update_date := l_pma_rec.last_update_date;
1639 END IF;
1640 IF (x_pma_rec.last_update_login = OKC_API.G_MISS_NUM)
1641 THEN
1642 x_pma_rec.last_update_login := l_pma_rec.last_update_login;
1643 END IF;
1644 -- R12 Data Model Changes 4485150 Start
1645 IF (x_pma_rec.orig_system_id1 = OKC_API.G_MISS_NUM)
1646 THEN
1647 x_pma_rec.orig_system_id1 := l_pma_rec.orig_system_id1;
1648 END IF;
1649 IF (x_pma_rec.orig_system_reference1 = OKC_API.G_MISS_CHAR)
1650 THEN
1651 x_pma_rec.orig_system_reference1 := l_pma_rec.orig_system_reference1;
1652 END IF;
1653 IF (x_pma_rec.orig_system_source_code = OKC_API.G_MISS_CHAR)
1654 THEN
1655 x_pma_rec.orig_system_source_code := l_pma_rec.orig_system_source_code;
1656 END IF;
1657 -- R12 Data Model Changes 4485150 End
1658
1659 END IF;
1660 RETURN(l_return_status);
1661 END populate_new_record;
1662 ------------------------------------------
1663 -- Set_Attributes for:OKS_PM_ACTIVITIES --
1664 ------------------------------------------
1665 FUNCTION Set_Attributes (
1666 p_pma_rec IN pma_rec_type,
1667 x_pma_rec OUT NOCOPY pma_rec_type
1668 ) RETURN VARCHAR2 IS
1669 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1670 BEGIN
1671 x_pma_rec := p_pma_rec;
1672 x_pma_rec.OBJECT_VERSION_NUMBER := p_pma_rec.OBJECT_VERSION_NUMBER + 1;
1673 RETURN(l_return_status);
1674 END Set_Attributes;
1675 BEGIN
1676 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1677 p_init_msg_list,
1678 '_PVT',
1679 x_return_status);
1680 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1681 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1682 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1683 RAISE OKC_API.G_EXCEPTION_ERROR;
1684 END IF;
1685 --- Setting item attributes
1686 l_return_status := Set_Attributes(
1687 p_pma_rec, -- IN
1688 l_pma_rec); -- OUT
1689 --- If any errors happen abort API
1690 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1691 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1692 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1693 RAISE OKC_API.G_EXCEPTION_ERROR;
1694 END IF;
1695 l_return_status := populate_new_record(l_pma_rec, l_def_pma_rec);
1696 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1697 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1698 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1699 RAISE OKC_API.G_EXCEPTION_ERROR;
1700 END IF;
1701 UPDATE OKS_PM_ACTIVITIES
1702 SET CLE_ID = l_def_pma_rec.cle_id,
1703 DNZ_CHR_ID = l_def_pma_rec.dnz_chr_id,
1704 ACTIVITY_ID = l_def_pma_rec.activity_id,
1705 SELECT_YN = l_def_pma_rec.select_yn,
1706 CONF_REQ_YN = l_def_pma_rec.conf_req_yn,
1707 SCH_EXISTS_YN = l_def_pma_rec.sch_exists_yn,
1708 PROGRAM_APPLICATION_ID = l_def_pma_rec.program_application_id,
1709 PROGRAM_ID = l_def_pma_rec.program_id,
1710 PROGRAM_UPDATE_DATE = l_def_pma_rec.program_update_date,
1711 OBJECT_VERSION_NUMBER = l_def_pma_rec.object_version_number,
1712 REQUEST_ID = l_def_pma_rec.request_id,
1713 CREATED_BY = l_def_pma_rec.created_by,
1714 CREATION_DATE = l_def_pma_rec.creation_date,
1715 LAST_UPDATED_BY = l_def_pma_rec.last_updated_by,
1716 LAST_UPDATE_DATE = l_def_pma_rec.last_update_date,
1717 LAST_UPDATE_LOGIN = l_def_pma_rec.last_update_login,
1718 -- R12 Data Model Changes 4485150 Start
1719 ORIG_SYSTEM_ID1 = l_def_pma_rec.orig_system_id1,
1720 ORIG_SYSTEM_REFERENCE1 = l_def_pma_rec.orig_system_reference1,
1721 ORIG_SYSTEM_SOURCE_CODE = l_def_pma_rec.orig_system_source_code
1722 -- R12 Data Model Changes 4485150 End
1723 WHERE ID = l_def_pma_rec.id;
1724
1725 x_pma_rec := l_pma_rec;
1726 x_return_status := l_return_status;
1727 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1728 EXCEPTION
1729 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1730 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1731 (
1732 l_api_name,
1733 G_PKG_NAME,
1734 'OKC_API.G_RET_STS_ERROR',
1735 x_msg_count,
1736 x_msg_data,
1737 '_PVT'
1738 );
1739 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1740 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1741 (
1742 l_api_name,
1743 G_PKG_NAME,
1744 'OKC_API.G_RET_STS_UNEXP_ERROR',
1745 x_msg_count,
1746 x_msg_data,
1747 '_PVT'
1748 );
1749 WHEN OTHERS THEN
1750 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1751 (
1752 l_api_name,
1753 G_PKG_NAME,
1754 'OTHERS',
1755 x_msg_count,
1756 x_msg_data,
1757 '_PVT'
1758 );
1759 END update_row;
1760 ----------------------------------------
1761 -- update_row for:OKS_PM_ACTIVITIES_V --
1762 ----------------------------------------
1763 PROCEDURE update_row(
1764 p_api_version IN NUMBER,
1765 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1766 x_return_status OUT NOCOPY VARCHAR2,
1767 x_msg_count OUT NOCOPY NUMBER,
1768 x_msg_data OUT NOCOPY VARCHAR2,
1769 p_pmav_rec IN pmav_rec_type,
1770 x_pmav_rec OUT NOCOPY pmav_rec_type) IS
1771
1772 l_api_version CONSTANT NUMBER := 1;
1773 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1774 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1775 l_pmav_rec pmav_rec_type := p_pmav_rec;
1776 l_def_pmav_rec pmav_rec_type;
1777 l_db_pmav_rec pmav_rec_type;
1778 l_pma_rec pma_rec_type;
1779 lx_pma_rec pma_rec_type;
1780 -------------------------------
1781 -- FUNCTION fill_who_columns --
1782 -------------------------------
1783 FUNCTION fill_who_columns (
1784 p_pmav_rec IN pmav_rec_type
1785 ) RETURN pmav_rec_type IS
1786 l_pmav_rec pmav_rec_type := p_pmav_rec;
1787 BEGIN
1788 l_pmav_rec.LAST_UPDATE_DATE := SYSDATE;
1789 l_pmav_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1790 l_pmav_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1791 RETURN(l_pmav_rec);
1792 END fill_who_columns;
1793 ----------------------------------
1794 -- FUNCTION populate_new_record --
1795 ----------------------------------
1796 FUNCTION populate_new_record (
1797 p_pmav_rec IN pmav_rec_type,
1798 x_pmav_rec OUT NOCOPY pmav_rec_type
1799 ) RETURN VARCHAR2 IS
1800 l_row_notfound BOOLEAN := TRUE;
1801 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1802 BEGIN
1803 x_pmav_rec := p_pmav_rec;
1804 -- Get current database values
1805 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
1806 -- so it may be verified through LOCK_ROW.
1807 l_db_pmav_rec := get_rec(p_pmav_rec, l_return_status);
1808 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1809 IF (x_pmav_rec.id = OKC_API.G_MISS_NUM)
1810 THEN
1811 x_pmav_rec.id := l_db_pmav_rec.id;
1812 END IF;
1813 IF (x_pmav_rec.cle_id = OKC_API.G_MISS_NUM)
1814 THEN
1815 x_pmav_rec.cle_id := l_db_pmav_rec.cle_id;
1816 END IF;
1817 IF (x_pmav_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1818 THEN
1819 x_pmav_rec.dnz_chr_id := l_db_pmav_rec.dnz_chr_id;
1820 END IF;
1821 IF (x_pmav_rec.activity_id = OKC_API.G_MISS_NUM)
1822 THEN
1823 x_pmav_rec.activity_id := l_db_pmav_rec.activity_id;
1824 END IF;
1825 IF (x_pmav_rec.select_yn = OKC_API.G_MISS_CHAR)
1826 THEN
1827 x_pmav_rec.select_yn := l_db_pmav_rec.select_yn;
1828 END IF;
1829 IF (x_pmav_rec.conf_req_yn = OKC_API.G_MISS_CHAR)
1830 THEN
1831 x_pmav_rec.conf_req_yn := l_db_pmav_rec.conf_req_yn;
1832 END IF;
1833 IF (x_pmav_rec.sch_exists_yn = OKC_API.G_MISS_CHAR)
1834 THEN
1835 x_pmav_rec.sch_exists_yn := l_db_pmav_rec.sch_exists_yn;
1836 END IF;
1837 IF (x_pmav_rec.program_application_id = OKC_API.G_MISS_NUM)
1838 THEN
1839 x_pmav_rec.program_application_id := l_db_pmav_rec.program_application_id;
1840 END IF;
1841 IF (x_pmav_rec.program_id = OKC_API.G_MISS_NUM)
1842 THEN
1843 x_pmav_rec.program_id := l_db_pmav_rec.program_id;
1844 END IF;
1845 IF (x_pmav_rec.program_update_date = OKC_API.G_MISS_DATE)
1846 THEN
1847 x_pmav_rec.program_update_date := l_db_pmav_rec.program_update_date;
1848 END IF;
1849 IF (x_pmav_rec.security_group_id = OKC_API.G_MISS_NUM)
1850 THEN
1851 x_pmav_rec.security_group_id := l_db_pmav_rec.security_group_id;
1852 END IF;
1853 IF (x_pmav_rec.request_id = OKC_API.G_MISS_NUM)
1854 THEN
1855 x_pmav_rec.request_id := l_db_pmav_rec.request_id;
1856 END IF;
1857 IF (x_pmav_rec.created_by = OKC_API.G_MISS_NUM)
1858 THEN
1859 x_pmav_rec.created_by := l_db_pmav_rec.created_by;
1860 END IF;
1861 IF (x_pmav_rec.creation_date = OKC_API.G_MISS_DATE)
1862 THEN
1863 x_pmav_rec.creation_date := l_db_pmav_rec.creation_date;
1864 END IF;
1865 IF (x_pmav_rec.last_updated_by = OKC_API.G_MISS_NUM)
1866 THEN
1867 x_pmav_rec.last_updated_by := l_db_pmav_rec.last_updated_by;
1868 END IF;
1869 IF (x_pmav_rec.last_update_date = OKC_API.G_MISS_DATE)
1870 THEN
1871 x_pmav_rec.last_update_date := l_db_pmav_rec.last_update_date;
1872 END IF;
1873 IF (x_pmav_rec.last_update_login = OKC_API.G_MISS_NUM)
1874 THEN
1875 x_pmav_rec.last_update_login := l_db_pmav_rec.last_update_login;
1876 END IF;
1877
1878 -- R12 Data Model Changes 4485150 Start
1879 IF (x_pmav_rec.orig_system_id1 = OKC_API.G_MISS_NUM)
1880 THEN
1881 x_pmav_rec.orig_system_id1 := l_db_pmav_rec.orig_system_id1;
1882 END IF;
1883 IF (x_pmav_rec.orig_system_reference1 = OKC_API.G_MISS_CHAR)
1884 THEN
1885 x_pmav_rec.orig_system_reference1 := l_db_pmav_rec.orig_system_reference1;
1886 END IF;
1887 IF (x_pmav_rec.orig_system_source_code = OKC_API.G_MISS_CHAR)
1888 THEN
1889 x_pmav_rec.orig_system_source_code:= l_db_pmav_rec.orig_system_source_code;
1890 END IF;
1891 -- R12 Data Model Changes 4485150 End
1892
1893 END IF;
1894 RETURN(l_return_status);
1895 END populate_new_record;
1896 --------------------------------------------
1897 -- Set_Attributes for:OKS_PM_ACTIVITIES_V --
1898 --------------------------------------------
1899 FUNCTION Set_Attributes (
1900 p_pmav_rec IN pmav_rec_type,
1901 x_pmav_rec OUT NOCOPY pmav_rec_type
1902 ) RETURN VARCHAR2 IS
1903 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1904 BEGIN
1905 x_pmav_rec := p_pmav_rec;
1906 RETURN(l_return_status);
1907 END Set_Attributes;
1908 BEGIN
1909 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1910 G_PKG_NAME,
1911 p_init_msg_list,
1912 l_api_version,
1913 p_api_version,
1914 '_PVT',
1915 x_return_status);
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 --- Setting item attributes
1922 l_return_status := Set_Attributes(
1923 p_pmav_rec, -- IN
1924 x_pmav_rec); -- OUT
1925 --- If any errors happen abort API
1926 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1927 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1928 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1929 RAISE OKC_API.G_EXCEPTION_ERROR;
1930 END IF;
1931 l_return_status := populate_new_record(l_pmav_rec, l_def_pmav_rec);
1932 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1933 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1934 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1935 RAISE OKC_API.G_EXCEPTION_ERROR;
1936 END IF;
1937 l_def_pmav_rec := fill_who_columns(l_def_pmav_rec);
1938 --- Validate all non-missing attributes (Item Level Validation)
1939 l_return_status := Validate_Attributes(l_def_pmav_rec);
1940 --- If any errors happen abort API
1941 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1942 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1943 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1944 RAISE OKC_API.G_EXCEPTION_ERROR;
1945 END IF;
1946 l_return_status := Validate_Record(l_def_pmav_rec, l_db_pmav_rec);
1947 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1948 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1949 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1950 RAISE OKC_API.G_EXCEPTION_ERROR;
1951 END IF;
1952
1953 -- Lock the Record
1954 lock_row(
1955 p_api_version => p_api_version,
1956 p_init_msg_list => p_init_msg_list,
1957 x_return_status => l_return_status,
1958 x_msg_count => x_msg_count,
1959 x_msg_data => x_msg_data,
1960 p_pmav_rec => p_pmav_rec);
1961 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1962 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1963 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1964 RAISE OKC_API.G_EXCEPTION_ERROR;
1965 END IF;
1966
1967 -----------------------------------------
1968 -- Move VIEW record to "Child" records --
1969 -----------------------------------------
1970 migrate(l_def_pmav_rec, l_pma_rec);
1971 -----------------------------------------------
1972 -- Call the UPDATE_ROW for each child record --
1973 -----------------------------------------------
1974 update_row(
1975 p_init_msg_list,
1976 l_return_status,
1977 x_msg_count,
1978 x_msg_data,
1979 l_pma_rec,
1980 lx_pma_rec
1981 );
1982 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1983 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1984 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1985 RAISE OKC_API.G_EXCEPTION_ERROR;
1986 END IF;
1987 migrate(lx_pma_rec, l_def_pmav_rec);
1988 x_pmav_rec := l_def_pmav_rec;
1989 x_return_status := l_return_status;
1990 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1991 EXCEPTION
1992 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1993 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1994 (
1995 l_api_name,
1996 G_PKG_NAME,
1997 'OKC_API.G_RET_STS_ERROR',
1998 x_msg_count,
1999 x_msg_data,
2000 '_PVT'
2001 );
2002 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2003 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2004 (
2005 l_api_name,
2006 G_PKG_NAME,
2007 'OKC_API.G_RET_STS_UNEXP_ERROR',
2008 x_msg_count,
2009 x_msg_data,
2010 '_PVT'
2011 );
2012 WHEN OTHERS THEN
2013 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2014 (
2015 l_api_name,
2016 G_PKG_NAME,
2017 'OTHERS',
2018 x_msg_count,
2019 x_msg_data,
2020 '_PVT'
2021 );
2022 END update_row;
2023 ----------------------------------------
2024 -- PL/SQL TBL update_row for:pmav_tbl --
2025 ----------------------------------------
2026 PROCEDURE update_row(
2027 p_api_version IN NUMBER,
2028 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2029 x_return_status OUT NOCOPY VARCHAR2,
2030 x_msg_count OUT NOCOPY NUMBER,
2031 x_msg_data OUT NOCOPY VARCHAR2,
2032 p_pmav_tbl IN pmav_tbl_type,
2033 x_pmav_tbl OUT NOCOPY pmav_tbl_type,
2034 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2035
2036 l_api_version CONSTANT NUMBER := 1;
2037 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2038 i NUMBER := 0;
2039 BEGIN
2040 OKC_API.init_msg_list(p_init_msg_list);
2041 -- Make sure PL/SQL table has records in it before passing
2042 IF (p_pmav_tbl.COUNT > 0) THEN
2043 i := p_pmav_tbl.FIRST;
2044 LOOP
2045 DECLARE
2046 l_error_rec OKC_API.ERROR_REC_TYPE;
2047 BEGIN
2048 l_error_rec.api_name := l_api_name;
2049 l_error_rec.api_package := G_PKG_NAME;
2050 l_error_rec.idx := i;
2051 update_row (
2052 p_api_version => p_api_version,
2053 p_init_msg_list => OKC_API.G_FALSE,
2054 x_return_status => l_error_rec.error_type,
2055 x_msg_count => l_error_rec.msg_count,
2056 x_msg_data => l_error_rec.msg_data,
2057 p_pmav_rec => p_pmav_tbl(i),
2058 x_pmav_rec => x_pmav_tbl(i));
2059 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2060 l_error_rec.sqlcode := SQLCODE;
2061 load_error_tbl(l_error_rec, px_error_tbl);
2062 ELSE
2063 x_msg_count := l_error_rec.msg_count;
2064 x_msg_data := l_error_rec.msg_data;
2065 END IF;
2066 EXCEPTION
2067 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2068 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2069 l_error_rec.sqlcode := SQLCODE;
2070 load_error_tbl(l_error_rec, px_error_tbl);
2071 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2072 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2073 l_error_rec.sqlcode := SQLCODE;
2074 load_error_tbl(l_error_rec, px_error_tbl);
2075 WHEN OTHERS THEN
2076 l_error_rec.error_type := 'OTHERS';
2077 l_error_rec.sqlcode := SQLCODE;
2078 load_error_tbl(l_error_rec, px_error_tbl);
2079 END;
2080 EXIT WHEN (i = p_pmav_tbl.LAST);
2081 i := p_pmav_tbl.NEXT(i);
2082 END LOOP;
2083 END IF;
2084 -- Loop through the error_tbl to find the error with the highest severity
2085 -- and return it.
2086 x_return_status := find_highest_exception(px_error_tbl);
2087 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2088 EXCEPTION
2089 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2090 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2091 (
2092 l_api_name,
2093 G_PKG_NAME,
2094 'OKC_API.G_RET_STS_ERROR',
2095 x_msg_count,
2096 x_msg_data,
2097 '_PVT'
2098 );
2099 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2100 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2101 (
2102 l_api_name,
2103 G_PKG_NAME,
2104 'OKC_API.G_RET_STS_UNEXP_ERROR',
2105 x_msg_count,
2106 x_msg_data,
2107 '_PVT'
2108 );
2109 WHEN OTHERS THEN
2110 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2111 (
2112 l_api_name,
2113 G_PKG_NAME,
2114 'OTHERS',
2115 x_msg_count,
2116 x_msg_data,
2117 '_PVT'
2118 );
2119 END update_row;
2120
2121 ----------------------------------------
2122 -- PL/SQL TBL update_row for:PMAV_TBL --
2123 ----------------------------------------
2124 PROCEDURE update_row(
2125 p_api_version IN NUMBER,
2126 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2127 x_return_status OUT NOCOPY VARCHAR2,
2128 x_msg_count OUT NOCOPY NUMBER,
2129 x_msg_data OUT NOCOPY VARCHAR2,
2130 p_pmav_tbl IN pmav_tbl_type,
2131 x_pmav_tbl OUT NOCOPY pmav_tbl_type) IS
2132
2133 l_api_version CONSTANT NUMBER := 1;
2134 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2135 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2136 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2137 BEGIN
2138 OKC_API.init_msg_list(p_init_msg_list);
2139 -- Make sure PL/SQL table has records in it before passing
2140 IF (p_pmav_tbl.COUNT > 0) THEN
2141 update_row (
2142 p_api_version => p_api_version,
2143 p_init_msg_list => OKC_API.G_FALSE,
2144 x_return_status => x_return_status,
2145 x_msg_count => x_msg_count,
2146 x_msg_data => x_msg_data,
2147 p_pmav_tbl => p_pmav_tbl,
2148 x_pmav_tbl => x_pmav_tbl,
2149 px_error_tbl => l_error_tbl);
2150 END IF;
2151 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2152 EXCEPTION
2153 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2154 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2155 (
2156 l_api_name,
2157 G_PKG_NAME,
2158 'OKC_API.G_RET_STS_ERROR',
2159 x_msg_count,
2160 x_msg_data,
2161 '_PVT'
2162 );
2163 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2164 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2165 (
2166 l_api_name,
2167 G_PKG_NAME,
2168 'OKC_API.G_RET_STS_UNEXP_ERROR',
2169 x_msg_count,
2170 x_msg_data,
2171 '_PVT'
2172 );
2173 WHEN OTHERS THEN
2174 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2175 (
2176 l_api_name,
2177 G_PKG_NAME,
2178 'OTHERS',
2179 x_msg_count,
2180 x_msg_data,
2181 '_PVT'
2182 );
2183 END update_row;
2184
2185 ---------------------------------------------------------------------------
2186 -- PROCEDURE delete_row
2187 ---------------------------------------------------------------------------
2188 --------------------------------------
2189 -- delete_row for:OKS_PM_ACTIVITIES --
2190 --------------------------------------
2191 PROCEDURE delete_row(
2192 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2193 x_return_status OUT NOCOPY VARCHAR2,
2194 x_msg_count OUT NOCOPY NUMBER,
2195 x_msg_data OUT NOCOPY VARCHAR2,
2196 p_pma_rec IN pma_rec_type) IS
2197
2198 l_api_version CONSTANT NUMBER := 1;
2199 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2200 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2201 l_pma_rec pma_rec_type := p_pma_rec;
2202 l_row_notfound BOOLEAN := TRUE;
2203 BEGIN
2204 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2205 p_init_msg_list,
2206 '_PVT',
2207 x_return_status);
2208 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2209 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2210 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2211 RAISE OKC_API.G_EXCEPTION_ERROR;
2212 END IF;
2213
2214 DELETE FROM OKS_PM_ACTIVITIES
2215 WHERE ID = p_pma_rec.id;
2216
2217 x_return_status := l_return_status;
2218 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2219 EXCEPTION
2220 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2221 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2222 (
2223 l_api_name,
2224 G_PKG_NAME,
2225 'OKC_API.G_RET_STS_ERROR',
2226 x_msg_count,
2227 x_msg_data,
2228 '_PVT'
2229 );
2230 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2231 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2232 (
2233 l_api_name,
2234 G_PKG_NAME,
2235 'OKC_API.G_RET_STS_UNEXP_ERROR',
2236 x_msg_count,
2237 x_msg_data,
2238 '_PVT'
2239 );
2240 WHEN OTHERS THEN
2241 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2242 (
2243 l_api_name,
2244 G_PKG_NAME,
2245 'OTHERS',
2246 x_msg_count,
2247 x_msg_data,
2248 '_PVT'
2249 );
2250 END delete_row;
2251 ----------------------------------------
2252 -- delete_row for:OKS_PM_ACTIVITIES_V --
2253 ----------------------------------------
2254 PROCEDURE delete_row(
2255 p_api_version IN NUMBER,
2256 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2257 x_return_status OUT NOCOPY VARCHAR2,
2258 x_msg_count OUT NOCOPY NUMBER,
2259 x_msg_data OUT NOCOPY VARCHAR2,
2260 p_pmav_rec IN pmav_rec_type) IS
2261
2262 l_api_version CONSTANT NUMBER := 1;
2263 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2264 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2265 l_pmav_rec pmav_rec_type := p_pmav_rec;
2266 l_pma_rec pma_rec_type;
2267 BEGIN
2268 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2269 G_PKG_NAME,
2270 p_init_msg_list,
2271 l_api_version,
2272 p_api_version,
2273 '_PVT',
2274 x_return_status);
2275 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2276 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2277 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2278 RAISE OKC_API.G_EXCEPTION_ERROR;
2279 END IF;
2280 -----------------------------------------
2281 -- Move VIEW record to "Child" records --
2282 -----------------------------------------
2283 migrate(l_pmav_rec, l_pma_rec);
2284 -----------------------------------------------
2285 -- Call the DELETE_ROW for each child record --
2286 -----------------------------------------------
2287 delete_row(
2288 p_init_msg_list,
2289 l_return_status,
2290 x_msg_count,
2291 x_msg_data,
2292 l_pma_rec
2293 );
2294 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2295 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2296 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2297 RAISE OKC_API.G_EXCEPTION_ERROR;
2298 END IF;
2299 x_return_status := l_return_status;
2300 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2301 EXCEPTION
2302 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2303 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2304 (
2305 l_api_name,
2306 G_PKG_NAME,
2307 'OKC_API.G_RET_STS_ERROR',
2308 x_msg_count,
2309 x_msg_data,
2310 '_PVT'
2311 );
2312 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2313 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2314 (
2315 l_api_name,
2316 G_PKG_NAME,
2317 'OKC_API.G_RET_STS_UNEXP_ERROR',
2318 x_msg_count,
2319 x_msg_data,
2320 '_PVT'
2321 );
2322 WHEN OTHERS THEN
2323 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2324 (
2325 l_api_name,
2326 G_PKG_NAME,
2327 'OTHERS',
2328 x_msg_count,
2329 x_msg_data,
2330 '_PVT'
2331 );
2332 END delete_row;
2333 ---------------------------------------------------
2334 -- PL/SQL TBL delete_row for:OKS_PM_ACTIVITIES_V --
2335 ---------------------------------------------------
2336 PROCEDURE delete_row(
2337 p_api_version IN NUMBER,
2338 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2339 x_return_status OUT NOCOPY VARCHAR2,
2340 x_msg_count OUT NOCOPY NUMBER,
2341 x_msg_data OUT NOCOPY VARCHAR2,
2342 p_pmav_tbl IN pmav_tbl_type,
2343 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2344
2345 l_api_version CONSTANT NUMBER := 1;
2346 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2347 i NUMBER := 0;
2348 BEGIN
2349 OKC_API.init_msg_list(p_init_msg_list);
2350 -- Make sure PL/SQL table has records in it before passing
2351 IF (p_pmav_tbl.COUNT > 0) THEN
2352 i := p_pmav_tbl.FIRST;
2353 LOOP
2354 DECLARE
2355 l_error_rec OKC_API.ERROR_REC_TYPE;
2356 BEGIN
2357 l_error_rec.api_name := l_api_name;
2358 l_error_rec.api_package := G_PKG_NAME;
2359 l_error_rec.idx := i;
2360 delete_row (
2361 p_api_version => p_api_version,
2362 p_init_msg_list => OKC_API.G_FALSE,
2363 x_return_status => l_error_rec.error_type,
2364 x_msg_count => l_error_rec.msg_count,
2365 x_msg_data => l_error_rec.msg_data,
2366 p_pmav_rec => p_pmav_tbl(i));
2367 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2368 l_error_rec.sqlcode := SQLCODE;
2369 load_error_tbl(l_error_rec, px_error_tbl);
2370 ELSE
2371 x_msg_count := l_error_rec.msg_count;
2372 x_msg_data := l_error_rec.msg_data;
2373 END IF;
2374 EXCEPTION
2375 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2376 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2377 l_error_rec.sqlcode := SQLCODE;
2378 load_error_tbl(l_error_rec, px_error_tbl);
2379 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2380 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2381 l_error_rec.sqlcode := SQLCODE;
2382 load_error_tbl(l_error_rec, px_error_tbl);
2383 WHEN OTHERS THEN
2384 l_error_rec.error_type := 'OTHERS';
2385 l_error_rec.sqlcode := SQLCODE;
2386 load_error_tbl(l_error_rec, px_error_tbl);
2387 END;
2388 EXIT WHEN (i = p_pmav_tbl.LAST);
2389 i := p_pmav_tbl.NEXT(i);
2390 END LOOP;
2391 END IF;
2392 -- Loop through the error_tbl to find the error with the highest severity
2393 -- and return it.
2394 x_return_status := find_highest_exception(px_error_tbl);
2395 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2396 EXCEPTION
2397 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2398 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2399 (
2400 l_api_name,
2401 G_PKG_NAME,
2402 'OKC_API.G_RET_STS_ERROR',
2403 x_msg_count,
2404 x_msg_data,
2405 '_PVT'
2406 );
2407 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2408 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2409 (
2410 l_api_name,
2411 G_PKG_NAME,
2412 'OKC_API.G_RET_STS_UNEXP_ERROR',
2413 x_msg_count,
2414 x_msg_data,
2415 '_PVT'
2416 );
2417 WHEN OTHERS THEN
2418 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2419 (
2420 l_api_name,
2421 G_PKG_NAME,
2422 'OTHERS',
2423 x_msg_count,
2424 x_msg_data,
2425 '_PVT'
2426 );
2427 END delete_row;
2428
2429 ---------------------------------------------------
2430 -- PL/SQL TBL delete_row for:OKS_PM_ACTIVITIES_V --
2431 ---------------------------------------------------
2432 PROCEDURE delete_row(
2433 p_api_version IN NUMBER,
2434 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2435 x_return_status OUT NOCOPY VARCHAR2,
2436 x_msg_count OUT NOCOPY NUMBER,
2437 x_msg_data OUT NOCOPY VARCHAR2,
2438 p_pmav_tbl IN pmav_tbl_type) IS
2439
2440 l_api_version CONSTANT NUMBER := 1;
2441 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2442 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2443 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2444 BEGIN
2445 OKC_API.init_msg_list(p_init_msg_list);
2446 -- Make sure PL/SQL table has records in it before passing
2447 IF (p_pmav_tbl.COUNT > 0) THEN
2448 delete_row (
2449 p_api_version => p_api_version,
2450 p_init_msg_list => OKC_API.G_FALSE,
2451 x_return_status => x_return_status,
2452 x_msg_count => x_msg_count,
2453 x_msg_data => x_msg_data,
2454 p_pmav_tbl => p_pmav_tbl,
2455 px_error_tbl => l_error_tbl);
2456 END IF;
2457 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2458 EXCEPTION
2459 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2460 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2461 (
2462 l_api_name,
2463 G_PKG_NAME,
2464 'OKC_API.G_RET_STS_ERROR',
2465 x_msg_count,
2466 x_msg_data,
2467 '_PVT'
2468 );
2469 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2470 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2471 (
2472 l_api_name,
2473 G_PKG_NAME,
2474 'OKC_API.G_RET_STS_UNEXP_ERROR',
2475 x_msg_count,
2476 x_msg_data,
2477 '_PVT'
2478 );
2479 WHEN OTHERS THEN
2480 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2481 (
2482 l_api_name,
2483 G_PKG_NAME,
2484 'OTHERS',
2485 x_msg_count,
2486 x_msg_data,
2487 '_PVT'
2488 );
2489 END delete_row;
2490
2491 FUNCTION Create_Version(
2492 p_id IN NUMBER,
2493 p_major_version IN NUMBER) RETURN VARCHAR2 IS
2494
2495 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2496
2497 BEGIN
2498
2499 INSERT INTO OKS_PM_ACTIVITIES_H(
2500 id,
2501 cle_id,
2502 dnz_chr_id,
2503 activity_id,
2504 select_yn,
2505 conf_req_yn,
2506 sch_exists_yn,
2507 program_application_id,
2508 program_id,
2509 program_update_date,
2510 object_version_number,
2511 request_id,
2512 created_by,
2513 creation_date,
2514 last_updated_by,
2515 last_update_date,
2516 last_update_login,
2517 MAJOR_VERSION
2518 )
2519 SELECT
2520 id,
2521 cle_id,
2522 dnz_chr_id,
2523 activity_id,
2524 select_yn,
2525 conf_req_yn,
2526 sch_exists_yn,
2527 program_application_id,
2528 program_id,
2529 program_update_date,
2530 object_version_number,
2531 request_id,
2532 created_by,
2533 creation_date,
2534 last_updated_by,
2535 last_update_date,
2536 last_update_login,
2537 p_major_version
2538 FROM OKS_PM_ACTIVITIES
2539 WHERE DNZ_CHR_ID = P_Id;
2540
2541 RETURN l_return_status;
2542
2543
2544 EXCEPTION
2545
2546
2547 WHEN OTHERS THEN
2548
2549
2550 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2551 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2552 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2553 p_token1_value => sqlcode,
2554 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2555 p_token2_value => sqlerrm);
2556
2557 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2558
2559 return l_return_status;
2560 END Create_Version;
2561
2562 FUNCTION Restore_Version(
2563 p_id IN NUMBER,
2564 p_major_version IN NUMBER) RETURN VARCHAR2 IS
2565
2566 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2567
2568 BEGIN
2569
2570 DELETE FROM OKS_PM_ACTIVITIES
2571 WHERE DNZ_CHR_ID = p_id;
2572
2573 INSERT INTO OKS_PM_ACTIVITIES(
2574 id,
2575 cle_id,
2576 dnz_chr_id,
2577 activity_id,
2578 select_yn,
2579 conf_req_yn,
2580 sch_exists_yn,
2581 program_application_id,
2582 program_id,
2583 program_update_date,
2584 object_version_number,
2585 request_id,
2586 created_by,
2587 creation_date,
2588 last_updated_by,
2589 last_update_date,
2590 last_update_login
2591 -- R12 Data Model Changes 4485150 End
2592 )
2593 SELECT
2594 id,
2595 cle_id,
2596 dnz_chr_id,
2597 activity_id,
2598 select_yn,
2599 conf_req_yn,
2600 sch_exists_yn,
2601 program_application_id,
2602 program_id,
2603 program_update_date,
2604 object_version_number,
2605 request_id,
2606 created_by,
2607 creation_date,
2608 last_updated_by,
2609 last_update_date,
2610 last_update_login
2611 FROM OKS_PM_ACTIVITIES_H
2612 WHERE DNZ_CHR_ID = P_Id
2613 AND major_version=p_major_version;
2614
2615 RETURN l_return_status;
2616
2617
2618 EXCEPTION
2619
2620 WHEN OTHERS THEN
2621
2622 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2623 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2624 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2625 p_token1_value => sqlcode,
2626 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2627 p_token2_value => sqlerrm);
2628
2629 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2630
2631 return l_return_status;
2632
2633 END Restore_Version;
2634 END OKS_PMA_PVT;