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