[Home] [Help]
PACKAGE BODY: APPS.OKL_CDJ_PVT
Source
1 PACKAGE BODY OKL_CDJ_PVT AS
2 /* $Header: OKLSCDJB.pls 120.3 2006/04/20 13:43:56 ssdeshpa noship $ */
3
4 -------------------------
5 -- PROCEDURE add_language
6 -------------------------
7 PROCEDURE add_language IS
8
9 BEGIN
10
11 DELETE FROM OKL_INSURANCE_ESTIMATES_TL T
12 WHERE NOT EXISTS (SELECT NULL FROM OKL_COST_ADJUSTMENTS_B B WHERE B.ID =T.ID);
13
14 UPDATE OKL_COST_ADJUSTMENTS_TL T
15 SET (SHORT_DESCRIPTION,
16 DESCRIPTION,
17 COMMENTS) =
18 (SELECT
19 B.SHORT_DESCRIPTION,
20 B.DESCRIPTION,
21 B.COMMENTS
22 FROM
23 OKL_COST_ADJUSTMENTS_TL B
24 WHERE
25 B.ID = T.ID
26 AND B.LANGUAGE = T.SOURCE_LANG)
27 WHERE (T.ID, T.LANGUAGE) IN (SELECT
28 SUBT.ID,
29 SUBT.LANGUAGE
30 FROM
31 OKL_COST_ADJUSTMENTS_TL SUBB,
32 OKL_COST_ADJUSTMENTS_TL SUBT
33 WHERE
34 SUBB.ID = SUBT.ID
35 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
36 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
37 OR (SUBB.DESCRIPTION <> SUBT.DESCRIPTION)
38 OR (SUBB.COMMENTS <> SUBT.COMMENTS)
39 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
40 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
41 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
42 )
43 );
44
45 INSERT INTO OKL_COST_ADJUSTMENTS_TL (
46 ID,
47 LANGUAGE,
48 SOURCE_LANG,
49 SFWT_FLAG,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 SHORT_DESCRIPTION,
56 DESCRIPTION,
57 COMMENTS)
58 SELECT
59 B.ID,
60 L.LANGUAGE_CODE,
61 B.SOURCE_LANG,
62 B.SFWT_FLAG,
63 B.CREATED_BY,
64 B.CREATION_DATE,
65 B.LAST_UPDATED_BY,
66 B.LAST_UPDATE_DATE,
67 B.LAST_UPDATE_LOGIN,
68 B.SHORT_DESCRIPTION,
69 B.DESCRIPTION,
70 B.COMMENTS
71 FROM OKL_COST_ADJUSTMENTS_TL B, FND_LANGUAGES L
72 WHERE L.INSTALLED_FLAG IN ('I', 'B')
73 AND B.LANGUAGE = USERENV('LANG')
74 AND NOT EXISTS (
75 SELECT NULL
76 FROM OKL_COST_ADJUSTMENTS_TL T
77 WHERE T.ID = B.ID
78 AND T.LANGUAGE = L.LANGUAGE_CODE
79 );
80
81 END add_language;
82
86 -----------------------------
83
84 -----------------------------
85 -- FUNCTION null_out_defaults
87 FUNCTION null_out_defaults (p_cdjv_rec IN cdjv_rec_type) RETURN cdjv_rec_type IS
88
89 l_cdjv_rec cdjv_rec_type;
90
91 BEGIN
92
93 l_cdjv_rec := p_cdjv_rec;
94
95 -- Not applicable to ID and OBJECT_VERSION_NUMBER
96
97 IF l_cdjv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
98 l_cdjv_rec.attribute_category := NULL;
99 END IF;
100 IF l_cdjv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
101 l_cdjv_rec.attribute1 := NULL;
102 END IF;
103 IF l_cdjv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
104 l_cdjv_rec.attribute2 := NULL;
105 END IF;
106 IF l_cdjv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
107 l_cdjv_rec.attribute3 := NULL;
108 END IF;
109 IF l_cdjv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
110 l_cdjv_rec.attribute4 := NULL;
111 END IF;
112 IF l_cdjv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
113 l_cdjv_rec.attribute5 := NULL;
114 END IF;
115 IF l_cdjv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
116 l_cdjv_rec.attribute6 := NULL;
117 END IF;
118 IF l_cdjv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
119 l_cdjv_rec.attribute7 := NULL;
120 END IF;
121 IF l_cdjv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
122 l_cdjv_rec.attribute8 := NULL;
123 END IF;
124 IF l_cdjv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
125 l_cdjv_rec.attribute9 := NULL;
126 END IF;
127 IF l_cdjv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
128 l_cdjv_rec.attribute10 := NULL;
129 END IF;
130 IF l_cdjv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
131 l_cdjv_rec.attribute11 := NULL;
132 END IF;
133 IF l_cdjv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
134 l_cdjv_rec.attribute12 := NULL;
135 END IF;
136 IF l_cdjv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
137 l_cdjv_rec.attribute13 := NULL;
138 END IF;
139 IF l_cdjv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
140 l_cdjv_rec.attribute14 := NULL;
141 END IF;
142 IF l_cdjv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
143 l_cdjv_rec.attribute15 := NULL;
144 END IF;
145 IF l_cdjv_rec.parent_object_code = FND_API.G_MISS_CHAR THEN
146 l_cdjv_rec.parent_object_code := NULL;
147 END IF;
148 IF l_cdjv_rec.parent_object_id = FND_API.G_MISS_NUM THEN
149 l_cdjv_rec.parent_object_id := NULL;
150 END IF;
151 IF l_cdjv_rec.adjustment_source_type = FND_API.G_MISS_CHAR THEN
152 l_cdjv_rec.adjustment_source_type := NULL;
153 END IF;
154 IF l_cdjv_rec.adjustment_source_id = FND_API.G_MISS_NUM THEN
155 l_cdjv_rec.adjustment_source_id := NULL;
156 END IF;
157 IF l_cdjv_rec.basis = FND_API.G_MISS_CHAR THEN
158 l_cdjv_rec.basis := NULL;
159 END IF;
160 IF l_cdjv_rec.value = FND_API.G_MISS_NUM THEN
161 l_cdjv_rec.value := NULL;
162 END IF;
163 IF l_cdjv_rec.processing_type = FND_API.G_MISS_CHAR THEN
164 l_cdjv_rec.processing_type := NULL;
165 END IF;
166 IF l_cdjv_rec.supplier_id = FND_API.G_MISS_NUM THEN
167 l_cdjv_rec.supplier_id := NULL;
168 END IF;
169 IF l_cdjv_rec.default_subsidy_amount = FND_API.G_MISS_NUM THEN
170 l_cdjv_rec.default_subsidy_amount := NULL;
171 END IF;
172 IF l_cdjv_rec.short_description = FND_API.G_MISS_CHAR THEN
173 l_cdjv_rec.short_description := NULL;
174 END IF;
175 IF l_cdjv_rec.description = FND_API.G_MISS_CHAR THEN
176 l_cdjv_rec.description := NULL;
177 END IF;
178 IF l_cdjv_rec.comments = FND_API.G_MISS_CHAR THEN
179 l_cdjv_rec.comments := NULL;
180 END IF;
181 IF l_cdjv_rec.percent_basis_value = FND_API.G_MISS_NUM THEN
182 l_cdjv_rec.percent_basis_value := NULL;
183 END IF;
184 --Bug # 5142940 ssdeshpa start
185 --added new column stream_type_id
186 IF l_cdjv_rec.stream_type_id = FND_API.G_MISS_NUM THEN
187 l_cdjv_rec.stream_type_id := NULL;
188 END IF;
189 --Bug # 5142940 ssdeshpa end;
190 RETURN l_cdjv_rec;
191
192 END null_out_defaults;
193
194
195 -------------------
196 -- FUNCTION get_rec
197 -------------------
198 FUNCTION get_rec (p_id IN NUMBER
199 ,x_return_status OUT NOCOPY VARCHAR2) RETURN cdjv_rec_type IS
200
201 l_cdjv_rec cdjv_rec_type;
202 l_prog_name VARCHAR2(61);
203
204 BEGIN
205
206 l_prog_name := G_PKG_NAME||'.get_rec';
207
208 SELECT
209 id
210 ,object_version_number
211 ,attribute_category
212 ,attribute1
213 ,attribute2
214 ,attribute3
215 ,attribute4
216 ,attribute5
217 ,attribute6
218 ,attribute7
219 ,attribute8
220 ,attribute9
221 ,attribute10
222 ,attribute11
223 ,attribute12
224 ,attribute13
225 ,attribute14
226 ,attribute15
227 ,parent_object_code
228 ,parent_object_id
229 ,adjustment_source_type
230 ,adjustment_source_id
231 ,basis
232 ,value
233 ,processing_type
234 ,supplier_id
235 ,default_subsidy_amount
236 ,short_description
237 ,description
238 ,comments
239 ,percent_basis_value
240 --Bug # 5142940 ssdeshpa start
244 l_cdjv_rec.id
241 ,stream_type_id
242 --Bug # 5142940 ssdeshpa end;
243 INTO
245 ,l_cdjv_rec.object_version_number
246 ,l_cdjv_rec.attribute_category
247 ,l_cdjv_rec.attribute1
248 ,l_cdjv_rec.attribute2
249 ,l_cdjv_rec.attribute3
250 ,l_cdjv_rec.attribute4
251 ,l_cdjv_rec.attribute5
252 ,l_cdjv_rec.attribute6
253 ,l_cdjv_rec.attribute7
254 ,l_cdjv_rec.attribute8
255 ,l_cdjv_rec.attribute9
256 ,l_cdjv_rec.attribute10
257 ,l_cdjv_rec.attribute11
258 ,l_cdjv_rec.attribute12
259 ,l_cdjv_rec.attribute13
260 ,l_cdjv_rec.attribute14
261 ,l_cdjv_rec.attribute15
262 ,l_cdjv_rec.parent_object_code
263 ,l_cdjv_rec.parent_object_id
264 ,l_cdjv_rec.adjustment_source_type
265 ,l_cdjv_rec.adjustment_source_id
266 ,l_cdjv_rec.basis
267 ,l_cdjv_rec.value
268 ,l_cdjv_rec.processing_type
269 ,l_cdjv_rec.supplier_id
270 ,l_cdjv_rec.default_subsidy_amount
271 ,l_cdjv_rec.short_description
272 ,l_cdjv_rec.description
273 ,l_cdjv_rec.comments
274 ,l_cdjv_rec.percent_basis_value
275 --Bug # 5142940 ssdeshpa start
276 ,l_cdjv_rec.stream_type_id
277 --Bug # 5142940 ssdeshpa end;
278 FROM OKL_COST_ADJUSTMENTS_V
279 WHERE id = p_id;
280
281 x_return_status := G_RET_STS_SUCCESS;
282 RETURN l_cdjv_rec;
283
284 EXCEPTION
285
286 WHEN OTHERS THEN
287
288 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
289 p_msg_name => G_DB_ERROR,
290 p_token1 => G_PROG_NAME_TOKEN,
291 p_token1_value => l_prog_name,
292 p_token2 => G_SQLCODE_TOKEN,
293 p_token2_value => sqlcode,
294 p_token3 => G_SQLERRM_TOKEN,
295 p_token3_value => sqlerrm);
296
297 x_return_status := G_RET_STS_UNEXP_ERROR;
298
299 END get_rec;
300
301
302 ------------------------
303 -- PROCEDURE validate_id
304 ------------------------
305 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2, p_id IN NUMBER) IS
306 BEGIN
307 IF p_id IS NULL THEN
308 OKL_API.set_message(p_app_name => G_APP_NAME,
309 p_msg_name => G_COL_ERROR,
310 p_token1 => G_COL_NAME_TOKEN,
311 p_token1_value => 'id',
312 p_token2 => G_PKG_NAME_TOKEN,
313 p_token2_value => G_PKG_NAME);
314 RAISE OKL_API.G_EXCEPTION_ERROR;
315 END IF;
316 x_return_status := G_RET_STS_SUCCESS;
317 END validate_id;
318
319
320 -------------------------------------------
321 -- PROCEDURE validate_object_version_number
322 -------------------------------------------
323 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2, p_object_version_number IN NUMBER) IS
324 BEGIN
325 IF p_object_version_number IS NULL THEN
326 OKL_API.set_message(p_app_name => G_APP_NAME,
327 p_msg_name => G_COL_ERROR,
328 p_token1 => G_COL_NAME_TOKEN,
329 p_token1_value => 'object_version_number',
330 p_token2 => G_PKG_NAME_TOKEN,
331 p_token2_value => G_PKG_NAME);
332 RAISE OKL_API.G_EXCEPTION_ERROR;
333 END IF;
334 x_return_status := G_RET_STS_SUCCESS;
335 END validate_object_version_number;
336
337
338 -----------------------------------------
339 -- PROCEDURE validate_parent_object_code
340 -----------------------------------------
341 PROCEDURE validate_parent_object_code (x_return_status OUT NOCOPY VARCHAR2, p_parent_object_code IN VARCHAR2) IS
342 BEGIN
343 IF p_parent_object_code IS NULL THEN
344 OKL_API.set_message(p_app_name => G_APP_NAME,
345 p_msg_name => G_COL_ERROR,
346 p_token1 => G_COL_NAME_TOKEN,
347 p_token1_value => 'parent_object_code',
348 p_token2 => G_PKG_NAME_TOKEN,
349 p_token2_value => G_PKG_NAME);
350 RAISE OKL_API.G_EXCEPTION_ERROR;
351 END IF;
352 x_return_status := G_RET_STS_SUCCESS;
353 END validate_parent_object_code;
354
355
356 -----------------------------------------
357 -- PROCEDURE validate_parent_object_id
358 -----------------------------------------
359 PROCEDURE validate_parent_object_id (x_return_status OUT NOCOPY VARCHAR2, p_parent_object_id IN NUMBER) IS
360 BEGIN
361 IF p_parent_object_id IS NULL THEN
362 OKL_API.set_message(p_app_name => G_APP_NAME,
363 p_msg_name => G_COL_ERROR,
364 p_token1 => G_COL_NAME_TOKEN,
365 p_token1_value => 'parent_object_id',
366 p_token2 => G_PKG_NAME_TOKEN,
367 p_token2_value => G_PKG_NAME);
368 RAISE OKL_API.G_EXCEPTION_ERROR;
369 END IF;
370 x_return_status := G_RET_STS_SUCCESS;
371 END validate_parent_object_id;
372
373
374 --------------------------------------
375 -- PROCEDURE validate_adj_source_type
376 --------------------------------------
377 PROCEDURE validate_adj_source_type (x_return_status OUT NOCOPY VARCHAR2, p_adj_source_type IN VARCHAR2) IS
378 BEGIN
379 IF p_adj_source_type IS NULL THEN
380 OKL_API.set_message(p_app_name => G_APP_NAME,
381 p_msg_name => G_COL_ERROR,
382 p_token1 => G_COL_NAME_TOKEN,
383 p_token1_value => 'adj_source_type',
384 p_token2 => G_PKG_NAME_TOKEN,
385 p_token2_value => G_PKG_NAME);
386 RAISE OKL_API.G_EXCEPTION_ERROR;
387 END IF;
388 x_return_status := G_RET_STS_SUCCESS;
389 END validate_adj_source_type;
390
391
392 --Bug # 5142940 ssdeshpa start
393 --------------------------------------
394 -- PROCEDURE validate_adj_stream_type_id
395 --------------------------------------
396 PROCEDURE validate_adj_stream_type (x_return_status OUT NOCOPY VARCHAR2, p_adj_rec IN cdjv_rec_type) IS
397 BEGIN
398 IF(p_adj_rec.processing_type IS NOT NULL) THEN
399 IF p_adj_rec.processing_type='BILL' AND p_adj_rec.stream_type_id IS NULL THEN
400 OKL_API.set_message(p_app_name => G_APP_NAME,
401 p_msg_name => 'OKL_STRM_TYPE_ID_REQUIRED');
402 RAISE OKL_API.G_EXCEPTION_ERROR;
403 END IF;
404 IF p_adj_rec.processing_type='CAPITALIZE' AND p_adj_rec.stream_type_id IS NOT NULL THEN
405 OKL_API.set_message(p_app_name => G_APP_NAME,
406 p_msg_name => 'OKL_STRM_TYPE_ID_NOT_REQUIRED');
407 RAISE OKL_API.G_EXCEPTION_ERROR;
408 END IF;
409 END IF;
410 x_return_status := G_RET_STS_SUCCESS;
411 END validate_adj_stream_type;
412 --Bug # 5142940 ssdeshpa start
413 -------------------------------
414 -- FUNCTION validate_attributes
415 -------------------------------
416 FUNCTION validate_attributes (p_cdjv_rec IN cdjv_rec_type) RETURN VARCHAR2 IS
417
418 l_return_status VARCHAR2(1);
419
420 BEGIN
421
422 validate_id (l_return_status, p_cdjv_rec.id);
423 validate_object_version_number (l_return_status, p_cdjv_rec.object_version_number);
424 validate_parent_object_code (l_return_status, p_cdjv_rec.parent_object_code);
425 validate_parent_object_id (l_return_status, p_cdjv_rec.parent_object_id);
426 validate_adj_source_type (l_return_status, p_cdjv_rec.adjustment_source_type);
427 --Bug # 5142940 ssdeshpa start
428 validate_adj_stream_type(l_return_status,p_cdjv_rec);
429 --Bug # 5142940 ssdeshpa start
430 RETURN l_return_status;
431
432 END validate_attributes;
433
434 ----------------------------
435 -- PROCEDURE validate_record
436 ----------------------------
437 FUNCTION validate_record (p_cdjv_rec IN cdjv_rec_type) RETURN VARCHAR2 IS
438 l_return_status VARCHAR2(1);
439 BEGIN
440 IF p_cdjv_rec.basis = 'ASSET_COST' AND ( p_cdjv_rec.percent_basis_value < 0 OR p_cdjv_rec.percent_basis_value > 100 )THEN
441 OKL_API.set_message(p_app_name => G_APP_NAME,
442 p_msg_name => 'OKL_PERCENT_BASIS_INVALID');
443 RAISE OKL_API.G_EXCEPTION_ERROR;
444 END IF;
445 RETURN G_RET_STS_SUCCESS;
446 END validate_record;
447
448
449 -----------------------------
450 -- PROECDURE migrate (V -> B)
451 -----------------------------
452 PROCEDURE migrate (p_from IN cdjv_rec_type, p_to IN OUT NOCOPY cdj_rec_type) IS
453
454 BEGIN
455
456 p_to.id := p_from.id;
457 p_to.object_version_number := p_from.object_version_number;
458 p_to.attribute_category := p_from.attribute_category;
459 p_to.attribute1 := p_from.attribute1;
460 p_to.attribute2 := p_from.attribute2;
461 p_to.attribute3 := p_from.attribute3;
462 p_to.attribute4 := p_from.attribute4;
466 p_to.attribute8 := p_from.attribute8;
463 p_to.attribute5 := p_from.attribute5;
464 p_to.attribute6 := p_from.attribute6;
465 p_to.attribute7 := p_from.attribute7;
467 p_to.attribute9 := p_from.attribute9;
468 p_to.attribute10 := p_from.attribute10;
469 p_to.attribute11 := p_from.attribute11;
470 p_to.attribute12 := p_from.attribute12;
471 p_to.attribute13 := p_from.attribute13;
472 p_to.attribute14 := p_from.attribute14;
473 p_to.attribute15 := p_from.attribute15;
474 p_to.parent_object_code := p_from.parent_object_code;
475 p_to.parent_object_id := p_from.parent_object_id;
476 p_to.adjustment_source_type := p_from.adjustment_source_type;
477 p_to.adjustment_source_id := p_from.adjustment_source_id;
478 p_to.basis := p_from.basis;
479 p_to.value := p_from.value;
480 p_to.processing_type := p_from.processing_type;
481 p_to.supplier_id := p_from.supplier_id;
482 p_to.default_subsidy_amount := p_from.default_subsidy_amount;
483 p_to.percent_basis_value := p_from.percent_basis_value;
484 --Bug # 5142940 ssdeshpa start
485 p_to.stream_type_id := p_from.stream_type_id;
486 --Bug # 5142940 ssdeshpa start
487 END migrate;
488
489
490 -----------------------------
491 -- PROCEDURE migrate (V -> TL)
492 -----------------------------
493 PROCEDURE migrate (p_from IN cdjv_rec_type, p_to IN OUT NOCOPY cdjtl_rec_type) IS
494 BEGIN
495 p_to.id := p_from.id;
496 p_to.short_description := p_from.short_description;
497 p_to.description := p_from.description;
498 p_to.comments := p_from.comments;
499 END migrate;
500
501
502 ---------------------------
503 -- PROCEDURE insert_row (B)
504 ---------------------------
505 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_cdj_rec IN cdj_rec_type) IS
506
507 l_prog_name VARCHAR2(61);
508
509 BEGIN
510
511 l_prog_name := G_PKG_NAME||'.insert_row (B)';
512
513 INSERT INTO okl_cost_adjustments_b (
514 id
515 ,object_version_number
516 ,attribute_category
517 ,attribute1
518 ,attribute2
519 ,attribute3
520 ,attribute4
521 ,attribute5
522 ,attribute6
523 ,attribute7
524 ,attribute8
525 ,attribute9
526 ,attribute10
527 ,attribute11
528 ,attribute12
529 ,attribute13
530 ,attribute14
531 ,attribute15
532 ,created_by
533 ,creation_date
534 ,last_updated_by
535 ,last_update_date
536 ,last_update_login
537 ,parent_object_code
538 ,parent_object_id
539 ,adjustment_source_type
540 ,adjustment_source_id
541 ,basis
542 ,value
543 ,processing_type
544 ,supplier_id
545 ,default_subsidy_amount
546 ,percent_basis_value
547 --Bug # 5142940 ssdeshpa start
548 ,stream_type_id
549 --Bug # 5142940 ssdeshpa start
550 )
551 VALUES
552 (
553 p_cdj_rec.id
554 ,p_cdj_rec.object_version_number
555 ,p_cdj_rec.attribute_category
556 ,p_cdj_rec.attribute1
557 ,p_cdj_rec.attribute2
558 ,p_cdj_rec.attribute3
559 ,p_cdj_rec.attribute4
560 ,p_cdj_rec.attribute5
561 ,p_cdj_rec.attribute6
562 ,p_cdj_rec.attribute7
563 ,p_cdj_rec.attribute8
564 ,p_cdj_rec.attribute9
565 ,p_cdj_rec.attribute10
566 ,p_cdj_rec.attribute11
567 ,p_cdj_rec.attribute12
568 ,p_cdj_rec.attribute13
569 ,p_cdj_rec.attribute14
570 ,p_cdj_rec.attribute15
571 ,G_USER_ID
572 ,SYSDATE
573 ,G_USER_ID
574 ,SYSDATE
575 ,G_LOGIN_ID
576 ,p_cdj_rec.parent_object_code
577 ,p_cdj_rec.parent_object_id
578 ,p_cdj_rec.adjustment_source_type
579 ,p_cdj_rec.adjustment_source_id
580 ,p_cdj_rec.basis
581 ,p_cdj_rec.value
582 ,p_cdj_rec.processing_type
583 ,p_cdj_rec.supplier_id
584 ,p_cdj_rec.default_subsidy_amount
585 ,p_cdj_rec.percent_basis_value
586 --Bug # 5142940 ssdeshpa start
587 ,p_cdj_rec.stream_type_id
588 --Bug # 5142940 ssdeshpa start
589 );
590
591 x_return_status := G_RET_STS_SUCCESS;
592
593 EXCEPTION
594
595 WHEN OTHERS THEN
596
597 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
598 p_msg_name => G_DB_ERROR,
599 p_token1 => G_PROG_NAME_TOKEN,
600 p_token1_value => l_prog_name,
601 p_token2 => G_SQLCODE_TOKEN,
602 p_token2_value => sqlcode,
603 p_token3 => G_SQLERRM_TOKEN,
604 p_token3_value => sqlerrm);
605
606 x_return_status := G_RET_STS_UNEXP_ERROR;
607
608 END insert_row;
609
610
611 ----------------------------
612 -- PROCEDURE insert_row (TL)
613 ----------------------------
614 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_cdjtl_rec IN cdjtl_rec_type) IS
615
616 CURSOR get_languages IS
617 SELECT language_code
621 l_sfwt_flag VARCHAR2(1);
618 FROM FND_LANGUAGES
619 WHERE INSTALLED_FLAG IN ('I', 'B');
620
622
623 l_prog_name VARCHAR2(61);
624
625 BEGIN
626
627 l_prog_name := G_PKG_NAME||'.insert_row (TL)';
628
629 FOR l_lang_rec IN get_languages LOOP
630
631 IF l_lang_rec.language_code = USERENV('LANG') THEN
632 l_sfwt_flag := 'N';
633 ELSE
634 l_sfwt_flag := 'Y';
635 END IF;
636
637 INSERT INTO OKL_COST_ADJUSTMENTS_TL (
638 id
639 ,language
640 ,source_lang
641 ,sfwt_flag
642 ,created_by
643 ,creation_date
644 ,last_updated_by
645 ,last_update_date
646 ,last_update_login
647 ,short_description
648 ,description
649 ,comments)
650 VALUES (
651 p_cdjtl_rec.id
652 ,l_lang_rec.language_code
653 ,USERENV('LANG')
654 ,l_sfwt_flag
655 ,G_USER_ID
656 ,SYSDATE
657 ,G_USER_ID
658 ,SYSDATE
659 ,G_LOGIN_ID
660 ,p_cdjtl_rec.short_description
661 ,p_cdjtl_rec.description
662 ,p_cdjtl_rec.comments);
663
664 END LOOP;
665
666 x_return_status := G_RET_STS_SUCCESS;
667
668 EXCEPTION
669
670 WHEN OTHERS THEN
671
672 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
673 p_msg_name => G_DB_ERROR,
674 p_token1 => G_PROG_NAME_TOKEN,
675 p_token1_value => l_prog_name,
676 p_token2 => G_SQLCODE_TOKEN,
677 p_token2_value => sqlcode,
678 p_token3 => G_SQLERRM_TOKEN,
679 p_token3_value => sqlerrm);
680
681 x_return_status := G_RET_STS_UNEXP_ERROR;
682
683 END insert_row;
684
685
686 ---------------------------
687 -- PROCEDURE insert_row (V)
688 ---------------------------
689 PROCEDURE insert_row (
690 x_return_status OUT NOCOPY VARCHAR2,
691 p_cdjv_rec IN cdjv_rec_type,
692 x_cdjv_rec OUT NOCOPY cdjv_rec_type) IS
693
694 l_return_status VARCHAR2(1);
695
696 l_cdjv_rec cdjv_rec_type;
697 l_cdj_rec cdj_rec_type;
698 l_cdjtl_rec cdjtl_rec_type;
699
700 l_prog_name VARCHAR2(61);
701
702 BEGIN
703
704 l_prog_name := G_PKG_NAME||'.insert_row (V)';
705
706 l_cdjv_rec := null_out_defaults (p_cdjv_rec);
707
708 SELECT okl_cdj_seq.nextval INTO l_cdjv_rec.ID FROM DUAL;
709
710 l_cdjv_rec.OBJECT_VERSION_NUMBER := 1;
711
712 l_return_status := validate_attributes(l_cdjv_rec);
713
714 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
715 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
716 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
717 RAISE OKL_API.G_EXCEPTION_ERROR;
718 END IF;
719
720 l_return_status := validate_record(l_cdjv_rec);
721
722 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
723 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
724 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
725 RAISE OKL_API.G_EXCEPTION_ERROR;
726 END IF;
727
728 migrate (l_cdjv_rec, l_cdj_rec);
729 migrate (l_cdjv_rec, l_cdjtl_rec);
730
731 insert_row (x_return_status => l_return_status, p_cdj_rec => l_cdj_rec);
732
733 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
734 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
735 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
736 RAISE OKL_API.G_EXCEPTION_ERROR;
737 END IF;
738
739 insert_row (x_return_status => l_return_status, p_cdjtl_rec => l_cdjtl_rec);
740
741 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
742 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
743 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
744 RAISE OKL_API.G_EXCEPTION_ERROR;
745 END IF;
746
747 x_cdjv_rec := l_cdjv_rec;
748 x_return_status := l_return_status;
749
750 EXCEPTION
751
752 WHEN OKL_API.G_EXCEPTION_ERROR THEN
753
754 x_return_status := G_RET_STS_ERROR;
755
756 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
757
758 x_return_status := G_RET_STS_UNEXP_ERROR;
759
760 WHEN OTHERS THEN
761
762 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
763 p_msg_name => G_DB_ERROR,
764 p_token1 => G_PROG_NAME_TOKEN,
765 p_token1_value => l_prog_name,
766 p_token2 => G_SQLCODE_TOKEN,
767 p_token2_value => sqlcode,
768 p_token3 => G_SQLERRM_TOKEN,
769 p_token3_value => sqlerrm);
770
771 x_return_status := G_RET_STS_UNEXP_ERROR;
772
773 END insert_row;
774
775
776 -----------------------------
777 -- PROCEDURE insert_row (REC)
778 -----------------------------
779 PROCEDURE insert_row(
780 p_api_version IN NUMBER,
781 p_init_msg_list IN VARCHAR2,
782 x_return_status OUT NOCOPY VARCHAR2,
783 x_msg_count OUT NOCOPY NUMBER,
787
784 x_msg_data OUT NOCOPY VARCHAR2,
785 p_cdjv_rec IN cdjv_rec_type,
786 x_cdjv_rec OUT NOCOPY cdjv_rec_type) IS
788 l_return_status VARCHAR2(1);
789
790 l_prog_name VARCHAR2(61);
791
792 BEGIN
793
794 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
795
796 IF p_init_msg_list = G_TRUE THEN
797 FND_MSG_PUB.initialize;
798 END IF;
799
800 insert_row (x_return_status => l_return_status,
801 p_cdjv_rec => p_cdjv_rec,
802 x_cdjv_rec => x_cdjv_rec);
803
804 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
805 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
806 ELSIF l_return_status = G_RET_STS_ERROR THEN
807 RAISE OKL_API.G_EXCEPTION_ERROR;
808 END IF;
809
810 x_return_status := l_return_status;
811
812 EXCEPTION
813
814 WHEN OKL_API.G_EXCEPTION_ERROR THEN
815
816 x_return_status := G_RET_STS_ERROR;
817
818 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
819
820 x_return_status := G_RET_STS_UNEXP_ERROR;
821
822 WHEN OTHERS THEN
823
824 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
825 p_msg_name => G_DB_ERROR,
826 p_token1 => G_PROG_NAME_TOKEN,
827 p_token1_value => l_prog_name,
828 p_token2 => G_SQLCODE_TOKEN,
829 p_token2_value => sqlcode,
830 p_token3 => G_SQLERRM_TOKEN,
831 p_token3_value => sqlerrm);
832
833 x_return_status := G_RET_STS_UNEXP_ERROR;
834
835 END insert_row;
836
837
838 -----------------------------
839 -- PROCEDURE insert_row (TBL)
840 -----------------------------
841 PROCEDURE insert_row(
842 p_api_version IN NUMBER,
843 p_init_msg_list IN VARCHAR2,
844 x_return_status OUT NOCOPY VARCHAR2,
845 x_msg_count OUT NOCOPY NUMBER,
846 x_msg_data OUT NOCOPY VARCHAR2,
847 p_cdjv_tbl IN cdjv_tbl_type,
848 x_cdjv_tbl OUT NOCOPY cdjv_tbl_type) IS
849
850 l_return_status VARCHAR2(1);
851 i BINARY_INTEGER;
852
853 l_prog_name VARCHAR2(61);
854
855 BEGIN
856
857 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
858
859 IF p_init_msg_list = G_TRUE THEN
860 FND_MSG_PUB.initialize;
861 END IF;
862
863 IF (p_cdjv_tbl.COUNT > 0) THEN
864 i := p_cdjv_tbl.FIRST;
865 LOOP
866 IF p_cdjv_tbl.EXISTS(i) THEN
867
868 insert_row (x_return_status => l_return_status,
869 p_cdjv_rec => p_cdjv_tbl(i),
870 x_cdjv_rec => x_cdjv_tbl(i));
871
872 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
873 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
874 ELSIF l_return_status = G_RET_STS_ERROR THEN
875 RAISE OKL_API.G_EXCEPTION_ERROR;
876 END IF;
877
878 EXIT WHEN (i = p_cdjv_tbl.LAST);
879 i := p_cdjv_tbl.NEXT(i);
880
881 END IF;
882
883 END LOOP;
884
885 ELSE
886
887 l_return_status := G_RET_STS_SUCCESS;
888
889 END IF;
890
891 x_return_status := l_return_status;
892
893 EXCEPTION
894
895 WHEN OKL_API.G_EXCEPTION_ERROR THEN
896
897 x_return_status := G_RET_STS_ERROR;
898
899 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
900
901 x_return_status := G_RET_STS_UNEXP_ERROR;
902
903 WHEN OTHERS THEN
904
905 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
906 p_msg_name => G_DB_ERROR,
907 p_token1 => G_PROG_NAME_TOKEN,
908 p_token1_value => l_prog_name,
909 p_token2 => G_SQLCODE_TOKEN,
910 p_token2_value => sqlcode,
911 p_token3 => G_SQLERRM_TOKEN,
912 p_token3_value => sqlerrm);
913
914 x_return_status := G_RET_STS_UNEXP_ERROR;
915
916 END insert_row;
917
918
919 ---------------------
920 -- PROCEDURE lock_row
921 ---------------------
922 PROCEDURE lock_row (x_return_status OUT NOCOPY VARCHAR2, p_cdj_rec IN cdj_rec_type) IS
923
924 E_Resource_Busy EXCEPTION;
925
926 PRAGMA EXCEPTION_INIT (E_Resource_Busy, -00054);
927
928 CURSOR lock_csr IS
929 SELECT OBJECT_VERSION_NUMBER
930 FROM OKL_COST_ADJUSTMENTS_B
931 WHERE ID = p_cdj_rec.id
932 AND OBJECT_VERSION_NUMBER = p_cdj_rec.object_version_number
933 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
934
935 CURSOR lchk_csr IS
936 SELECT OBJECT_VERSION_NUMBER
937 FROM OKL_COST_ADJUSTMENTS_B
938 WHERE ID = p_cdj_rec.id;
939
940 l_object_version_number NUMBER;
941 lc_object_version_number NUMBER;
942
943 l_prog_name VARCHAR2(61);
944
945 BEGIN
946
947 l_prog_name := G_PKG_NAME||'.lock_row';
948
949 BEGIN
950 OPEN lock_csr;
954 WHEN E_Resource_Busy THEN
951 FETCH lock_csr INTO l_object_version_number;
952 CLOSE lock_csr;
953 EXCEPTION
955
956 IF (lock_csr%ISOPEN) THEN
957 CLOSE lock_csr;
958 END IF;
959 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
960 p_msg_name => G_OVN_ERROR2,
961 p_token1 => G_PROG_NAME_TOKEN,
962 p_token1_value => l_prog_name);
963 RAISE OKL_API.G_EXCEPTION_ERROR;
964 END;
965
966 IF l_object_version_number IS NULL THEN
967
968 OPEN lchk_csr;
969 FETCH lchk_csr INTO lc_object_version_number;
970 CLOSE lchk_csr;
971
972 IF lc_object_version_number IS NULL THEN
973
974 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
975 p_msg_name => G_OVN_ERROR3,
976 p_token1 => G_PROG_NAME_TOKEN,
977 p_token1_value => l_prog_name);
978
979 ELSIF lc_object_version_number <> p_cdj_rec.object_version_number THEN
980
981 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
982 p_msg_name => G_OVN_ERROR,
983 p_token1 => G_PROG_NAME_TOKEN,
984 p_token1_value => l_prog_name);
985
986 END IF;
987
988 RAISE OKL_API.G_EXCEPTION_ERROR;
989
990 END IF;
991
992 x_return_status := G_RET_STS_SUCCESS;
993
994 EXCEPTION
995
996 WHEN OKL_API.G_EXCEPTION_ERROR THEN
997
998 x_return_status := G_RET_STS_ERROR;
999
1000 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1001
1002 x_return_status := G_RET_STS_UNEXP_ERROR;
1003
1004 WHEN OTHERS THEN
1005
1006 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1007 p_msg_name => G_DB_ERROR,
1008 p_token1 => G_PROG_NAME_TOKEN,
1009 p_token1_value => l_prog_name,
1010 p_token2 => G_SQLCODE_TOKEN,
1011 p_token2_value => sqlcode,
1012 p_token3 => G_SQLERRM_TOKEN,
1013 p_token3_value => sqlerrm);
1014
1015 x_return_status := G_RET_STS_UNEXP_ERROR;
1016
1017 END lock_row;
1018
1019
1020 ---------------------------
1021 -- PROCEDURE update_row (B)
1022 ---------------------------
1023 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_cdj_rec IN cdj_rec_type) IS
1024
1025 l_return_status VARCHAR2(1);
1026
1027 l_prog_name VARCHAR2(61);
1028
1029 BEGIN
1030
1031 l_prog_name := G_PKG_NAME||'.update_row (B)';
1032
1033 lock_row (x_return_status => l_return_status, p_cdj_rec => p_cdj_rec);
1034
1035 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1036 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1037 ELSIF l_return_status = G_RET_STS_ERROR THEN
1038 RAISE OKL_API.G_EXCEPTION_ERROR;
1039 END IF;
1040
1041 UPDATE okl_cost_adjustments_b
1042 SET
1043 object_version_number = p_cdj_rec.object_version_number+1
1044 ,attribute_category = p_cdj_rec.attribute_category
1045 ,attribute1 = p_cdj_rec.attribute1
1046 ,attribute2 = p_cdj_rec.attribute2
1047 ,attribute3 = p_cdj_rec.attribute3
1048 ,attribute4 = p_cdj_rec.attribute4
1049 ,attribute5 = p_cdj_rec.attribute5
1050 ,attribute6 = p_cdj_rec.attribute6
1051 ,attribute7 = p_cdj_rec.attribute7
1052 ,attribute8 = p_cdj_rec.attribute8
1053 ,attribute9 = p_cdj_rec.attribute9
1054 ,attribute10 = p_cdj_rec.attribute10
1055 ,attribute11 = p_cdj_rec.attribute11
1056 ,attribute12 = p_cdj_rec.attribute12
1057 ,attribute13 = p_cdj_rec.attribute13
1058 ,attribute14 = p_cdj_rec.attribute14
1059 ,attribute15 = p_cdj_rec.attribute15
1060 ,parent_object_code = p_cdj_rec.parent_object_code
1061 ,parent_object_id = p_cdj_rec.parent_object_id
1062 ,adjustment_source_type = p_cdj_rec.adjustment_source_type
1063 ,adjustment_source_id = p_cdj_rec.adjustment_source_id
1064 ,basis = p_cdj_rec.basis
1065 ,value = p_cdj_rec.value
1066 ,processing_type = p_cdj_rec.processing_type
1067 ,supplier_id = p_cdj_rec.supplier_id
1068 ,default_subsidy_amount = p_cdj_rec.default_subsidy_amount
1069 ,percent_basis_value = p_cdj_rec.percent_basis_value
1070 --Bug # 5142940 ssdeshpa start
1071 ,stream_type_id = p_cdj_rec.stream_type_id
1072 --Bug # 5142940 ssdeshpa start
1073
1074 WHERE id = p_cdj_rec.id;
1075
1076 x_return_status := l_return_status;
1077
1078 EXCEPTION
1079
1080 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1081 x_return_status := G_RET_STS_ERROR;
1082
1083 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1084 x_return_status := G_RET_STS_UNEXP_ERROR;
1085
1086 WHEN OTHERS THEN
1087 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1088 p_msg_name => G_DB_ERROR,
1089 p_token1 => G_PROG_NAME_TOKEN,
1090 p_token1_value => l_prog_name,
1091 p_token2 => G_SQLCODE_TOKEN,
1092 p_token2_value => sqlcode,
1093 p_token3 => G_SQLERRM_TOKEN,
1094 p_token3_value => sqlerrm);
1098 END update_row;
1095
1096 x_return_status := G_RET_STS_UNEXP_ERROR;
1097
1099
1100
1101 ----------------------------
1102 -- PROCEDURE update_row (TL)
1103 ----------------------------
1104 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_cdjtl_rec IN cdjtl_rec_type) IS
1105
1106 l_prog_name VARCHAR2(61);
1107
1108 BEGIN
1109
1110 l_prog_name := G_PKG_NAME||'.update_row (TL)';
1111
1112 UPDATE OKL_COST_ADJUSTMENTS_TL
1113 SET
1114 source_lang = USERENV('LANG')
1115 ,sfwt_flag = 'Y'
1116 ,last_updated_by = G_USER_ID
1117 ,last_update_date = SYSDATE
1118 ,last_update_login = G_LOGIN_ID
1119 ,short_description = p_cdjtl_rec.short_description
1120 ,description = p_cdjtl_rec.description
1121 ,comments = p_cdjtl_rec.comments
1122 WHERE ID = p_cdjtl_rec.id;
1123
1124 UPDATE OKL_COST_ADJUSTMENTS_TL
1125 SET SFWT_FLAG = 'N'
1126 WHERE ID = p_cdjtl_rec.id
1127 AND SOURCE_LANG = LANGUAGE;
1128
1129 x_return_status := G_RET_STS_SUCCESS;
1130
1131 EXCEPTION
1132
1133 WHEN OTHERS THEN
1134
1135 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1136 p_msg_name => G_DB_ERROR,
1137 p_token1 => G_PROG_NAME_TOKEN,
1138 p_token1_value => l_prog_name,
1139 p_token2 => G_SQLCODE_TOKEN,
1140 p_token2_value => sqlcode,
1141 p_token3 => G_SQLERRM_TOKEN,
1142 p_token3_value => sqlerrm);
1143
1144 x_return_status := G_RET_STS_UNEXP_ERROR;
1145
1146 END update_row;
1147
1148
1149 ---------------------------
1150 -- PROCEDURE update_row (V)
1151 ---------------------------
1152 PROCEDURE update_row (
1153 x_return_status OUT NOCOPY VARCHAR2,
1154 p_cdjv_rec IN cdjv_rec_type,
1155 x_cdjv_rec OUT NOCOPY cdjv_rec_type) IS
1156
1157 l_prog_name VARCHAR2(61);
1158
1159 l_return_status VARCHAR2(1);
1160 l_cdjv_rec cdjv_rec_type;
1161 l_cdj_rec cdj_rec_type;
1162 l_cdjtl_rec cdjtl_rec_type;
1163
1164 ----------------------
1165 -- populate_new_record
1166 ----------------------
1167 FUNCTION populate_new_record (p_cdjv_rec IN cdjv_rec_type,
1168 x_cdjv_rec OUT NOCOPY cdjv_rec_type) RETURN VARCHAR2 IS
1169
1170 l_prog_name VARCHAR2(61);
1171 l_return_status VARCHAR2(1);
1172 l_db_cdjv_rec cdjv_rec_type;
1173
1174 BEGIN
1175
1176 l_prog_name := G_PKG_NAME||'.populate_new_record';
1177
1178 x_cdjv_rec := p_cdjv_rec;
1179 l_db_cdjv_rec := get_rec (p_cdjv_rec.id, l_return_status);
1180
1181 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1182 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1183 ELSIF l_return_status = G_RET_STS_ERROR THEN
1184 RAISE OKL_API.G_EXCEPTION_ERROR;
1185 END IF;
1186
1187 IF x_cdjv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
1188 x_cdjv_rec.attribute_category := l_db_cdjv_rec.attribute_category;
1189 END IF;
1190 IF x_cdjv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1191 x_cdjv_rec.attribute1 := l_db_cdjv_rec.attribute1;
1192 END IF;
1193 IF x_cdjv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1194 x_cdjv_rec.attribute2 := l_db_cdjv_rec.attribute2;
1195 END IF;
1196 IF x_cdjv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1197 x_cdjv_rec.attribute3 := l_db_cdjv_rec.attribute3;
1198 END IF;
1199 IF x_cdjv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1200 x_cdjv_rec.attribute4 := l_db_cdjv_rec.attribute4;
1201 END IF;
1202 IF x_cdjv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1203 x_cdjv_rec.attribute5 := l_db_cdjv_rec.attribute5;
1204 END IF;
1205 IF x_cdjv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1206 x_cdjv_rec.attribute6 := l_db_cdjv_rec.attribute6;
1207 END IF;
1208 IF x_cdjv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1209 x_cdjv_rec.attribute7 := l_db_cdjv_rec.attribute7;
1210 END IF;
1211 IF x_cdjv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1212 x_cdjv_rec.attribute8 := l_db_cdjv_rec.attribute8;
1213 END IF;
1214 IF x_cdjv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1215 x_cdjv_rec.attribute9 := l_db_cdjv_rec.attribute9;
1216 END IF;
1217 IF x_cdjv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1218 x_cdjv_rec.attribute10 := l_db_cdjv_rec.attribute10;
1219 END IF;
1220 IF x_cdjv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1221 x_cdjv_rec.attribute11 := l_db_cdjv_rec.attribute11;
1222 END IF;
1223 IF x_cdjv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1224 x_cdjv_rec.attribute12 := l_db_cdjv_rec.attribute12;
1225 END IF;
1226 IF x_cdjv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1227 x_cdjv_rec.attribute13 := l_db_cdjv_rec.attribute13;
1228 END IF;
1229 IF x_cdjv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1230 x_cdjv_rec.attribute14 := l_db_cdjv_rec.attribute14;
1231 END IF;
1232 IF x_cdjv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1233 x_cdjv_rec.attribute15 := l_db_cdjv_rec.attribute15;
1234 END IF;
1235 IF x_cdjv_rec.parent_object_code = FND_API.G_MISS_CHAR THEN
1236 x_cdjv_rec.parent_object_code := l_db_cdjv_rec.parent_object_code;
1237 END IF;
1241 IF x_cdjv_rec.adjustment_source_type = FND_API.G_MISS_CHAR THEN
1238 IF x_cdjv_rec.parent_object_id = FND_API.G_MISS_NUM THEN
1239 x_cdjv_rec.parent_object_id := l_db_cdjv_rec.parent_object_id;
1240 END IF;
1242 x_cdjv_rec.adjustment_source_type := l_db_cdjv_rec.adjustment_source_type;
1243 END IF;
1244 IF x_cdjv_rec.adjustment_source_id = FND_API.G_MISS_NUM THEN
1245 x_cdjv_rec.adjustment_source_id := l_db_cdjv_rec.adjustment_source_id;
1246 END IF;
1247 IF x_cdjv_rec.basis = FND_API.G_MISS_CHAR THEN
1248 x_cdjv_rec.basis := l_db_cdjv_rec.basis;
1249 END IF;
1250 IF x_cdjv_rec.value = FND_API.G_MISS_NUM THEN
1251 x_cdjv_rec.value := l_db_cdjv_rec.value;
1252 END IF;
1253 IF x_cdjv_rec.processing_type = FND_API.G_MISS_CHAR THEN
1254 x_cdjv_rec.processing_type := l_db_cdjv_rec.processing_type;
1255 END IF;
1256 IF x_cdjv_rec.supplier_id = FND_API.G_MISS_NUM THEN
1257 x_cdjv_rec.supplier_id := l_db_cdjv_rec.supplier_id;
1258 END IF;
1259 IF x_cdjv_rec.default_subsidy_amount = FND_API.G_MISS_NUM THEN
1260 x_cdjv_rec.default_subsidy_amount := l_db_cdjv_rec.default_subsidy_amount;
1261 END IF;
1262 IF x_cdjv_rec.short_description = FND_API.G_MISS_CHAR THEN
1263 x_cdjv_rec.short_description := l_db_cdjv_rec.short_description;
1264 END IF;
1265 IF x_cdjv_rec.description = FND_API.G_MISS_CHAR THEN
1266 x_cdjv_rec.description := l_db_cdjv_rec.description;
1267 END IF;
1268 IF x_cdjv_rec.comments = FND_API.G_MISS_CHAR THEN
1269 x_cdjv_rec.comments := l_db_cdjv_rec.comments;
1270 END IF;
1271 IF x_cdjv_rec.percent_basis_value = FND_API.G_MISS_NUM THEN
1272 x_cdjv_rec.percent_basis_value := l_db_cdjv_rec.percent_basis_value;
1273 END IF;
1274 --Bug # 5142940 ssdeshpa start
1275 IF x_cdjv_rec.stream_type_id = FND_API.G_MISS_NUM THEN
1276 x_cdjv_rec.stream_type_id := l_db_cdjv_rec.stream_type_id;
1277 END IF;
1278 --Bug # 5142940 ssdeshpa end;
1279 RETURN l_return_status;
1280
1281 EXCEPTION
1282
1283 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1284
1285 x_return_status := G_RET_STS_ERROR;
1286
1287 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1288
1289 x_return_status := G_RET_STS_UNEXP_ERROR;
1290
1291 WHEN OTHERS THEN
1292
1293 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1294 p_msg_name => G_DB_ERROR,
1295 p_token1 => G_PROG_NAME_TOKEN,
1296 p_token1_value => l_prog_name,
1297 p_token2 => G_SQLCODE_TOKEN,
1298 p_token2_value => sqlcode,
1299 p_token3 => G_SQLERRM_TOKEN,
1300 p_token3_value => sqlerrm);
1301
1302 x_return_status := G_RET_STS_UNEXP_ERROR;
1303
1304 END populate_new_record;
1305
1306 BEGIN
1307
1308 l_prog_name := G_PKG_NAME||'.update_row (V)';
1309
1310 l_return_status := populate_new_record (p_cdjv_rec, l_cdjv_rec);
1311
1312 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1313 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1314 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1315 RAISE OKL_API.G_EXCEPTION_ERROR;
1316 END IF;
1317
1318 l_return_status := validate_attributes (l_cdjv_rec);
1319
1320 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1321 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1322 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1323 RAISE OKL_API.G_EXCEPTION_ERROR;
1324 END IF;
1325
1326 l_return_status := validate_record (l_cdjv_rec);
1327
1328 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1329 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1330 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1331 RAISE OKL_API.G_EXCEPTION_ERROR;
1332 END IF;
1333
1334 migrate (l_cdjv_rec, l_cdj_rec);
1335 migrate (l_cdjv_rec, l_cdjtl_rec);
1336
1337 update_row (x_return_status => l_return_status, p_cdj_rec => l_cdj_rec);
1338
1339 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1340 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1341 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1342 RAISE OKL_API.G_EXCEPTION_ERROR;
1343 END IF;
1344
1345 update_row (x_return_status => l_return_status, p_cdjtl_rec => l_cdjtl_rec);
1346
1347 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1348 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1349 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1350 RAISE OKL_API.G_EXCEPTION_ERROR;
1351 END IF;
1352
1353 x_return_status := l_return_status;
1354 x_cdjv_rec := l_cdjv_rec;
1355
1356 EXCEPTION
1357
1358 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1359
1360 x_return_status := G_RET_STS_ERROR;
1361
1362 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1363
1364 x_return_status := G_RET_STS_UNEXP_ERROR;
1365
1366 WHEN OTHERS THEN
1367
1368 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1369 p_msg_name => G_DB_ERROR,
1370 p_token1 => G_PROG_NAME_TOKEN,
1371 p_token1_value => l_prog_name,
1372 p_token2 => G_SQLCODE_TOKEN,
1373 p_token2_value => sqlcode,
1374 p_token3 => G_SQLERRM_TOKEN,
1375 p_token3_value => sqlerrm);
1376
1377 x_return_status := G_RET_STS_UNEXP_ERROR;
1378
1379 END update_row;
1380
1384 -----------------------------
1381
1382 -----------------------------
1383 -- PROCEDURE update_row (REC)
1385 PROCEDURE update_row(
1386 p_api_version IN NUMBER,
1387 p_init_msg_list IN VARCHAR2,
1388 x_return_status OUT NOCOPY VARCHAR2,
1389 x_msg_count OUT NOCOPY NUMBER,
1390 x_msg_data OUT NOCOPY VARCHAR2,
1391 p_cdjv_rec IN cdjv_rec_type,
1392 x_cdjv_rec OUT NOCOPY cdjv_rec_type) IS
1393
1394 l_return_status VARCHAR2(1);
1395
1396 l_prog_name VARCHAR2(61);
1397
1398 BEGIN
1399
1400 l_prog_name := G_PKG_NAME||'.update_row (REC)';
1401
1402 IF p_init_msg_list = G_TRUE THEN
1403 FND_MSG_PUB.initialize;
1404 END IF;
1405
1406 update_row (x_return_status => l_return_status,
1407 p_cdjv_rec => p_cdjv_rec,
1408 x_cdjv_rec => x_cdjv_rec);
1409
1410 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1411 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1412 ELSIF l_return_status = G_RET_STS_ERROR THEN
1413 RAISE OKL_API.G_EXCEPTION_ERROR;
1414 END IF;
1415
1416 x_return_status := l_return_status;
1417
1418 EXCEPTION
1419
1420 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1421
1422 x_return_status := G_RET_STS_ERROR;
1423
1424 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1425
1426 x_return_status := G_RET_STS_UNEXP_ERROR;
1427
1428 WHEN OTHERS THEN
1429
1430 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1431 p_msg_name => G_DB_ERROR,
1432 p_token1 => G_PROG_NAME_TOKEN,
1433 p_token1_value => l_prog_name,
1434 p_token2 => G_SQLCODE_TOKEN,
1435 p_token2_value => sqlcode,
1436 p_token3 => G_SQLERRM_TOKEN,
1437 p_token3_value => sqlerrm);
1438
1439 x_return_status := G_RET_STS_UNEXP_ERROR;
1440
1441 END update_row;
1442
1443
1444 -----------------------------
1445 -- PROCEDURE update_row (TBL)
1446 -----------------------------
1447 PROCEDURE update_row(
1448 p_api_version IN NUMBER,
1449 p_init_msg_list IN VARCHAR2,
1450 x_return_status OUT NOCOPY VARCHAR2,
1451 x_msg_count OUT NOCOPY NUMBER,
1452 x_msg_data OUT NOCOPY VARCHAR2,
1453 p_cdjv_tbl IN cdjv_tbl_type,
1454 x_cdjv_tbl OUT NOCOPY cdjv_tbl_type) IS
1455
1456 l_return_status VARCHAR2(1);
1457 i BINARY_INTEGER;
1458 l_prog_name VARCHAR2(61);
1459
1460 BEGIN
1461
1462 l_prog_name := G_PKG_NAME||'.update_row (TBL)';
1463
1464 IF p_init_msg_list = G_TRUE THEN
1465 FND_MSG_PUB.initialize;
1466 END IF;
1467
1468 x_cdjv_tbl := p_cdjv_tbl;
1469
1470 IF (p_cdjv_tbl.COUNT > 0) THEN
1471
1472 i := p_cdjv_tbl.FIRST;
1473
1474 LOOP
1475
1476 IF p_cdjv_tbl.EXISTS(i) THEN
1477 update_row (x_return_status => l_return_status,
1478 p_cdjv_rec => p_cdjv_tbl(i),
1479 x_cdjv_rec => x_cdjv_tbl(i));
1480
1481 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1482 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1483 ELSIF l_return_status = G_RET_STS_ERROR THEN
1484 RAISE OKL_API.G_EXCEPTION_ERROR;
1485 END IF;
1486
1487 EXIT WHEN (i = p_cdjv_tbl.LAST);
1488 i := p_cdjv_tbl.NEXT(i);
1489
1490 END IF;
1491
1492 END LOOP;
1493
1494 ELSE
1495
1496 l_return_status := G_RET_STS_SUCCESS;
1497
1498 END IF;
1499
1500 x_return_status := l_return_status;
1501
1502 EXCEPTION
1503
1504 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1505
1506 x_return_status := G_RET_STS_ERROR;
1507
1508 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1509
1510 x_return_status := G_RET_STS_UNEXP_ERROR;
1511
1512 WHEN OTHERS THEN
1513
1514 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1515 p_msg_name => G_DB_ERROR,
1516 p_token1 => G_PROG_NAME_TOKEN,
1517 p_token1_value => l_prog_name,
1518 p_token2 => G_SQLCODE_TOKEN,
1519 p_token2_value => sqlcode,
1520 p_token3 => G_SQLERRM_TOKEN,
1521 p_token3_value => sqlerrm);
1522
1523 x_return_status := G_RET_STS_UNEXP_ERROR;
1524
1525 END update_row;
1526
1527
1528 -----------------
1529 -- delete_row (V)
1530 -----------------
1531 PROCEDURE delete_row(
1532 x_return_status OUT NOCOPY VARCHAR2,
1533 p_id IN NUMBER) IS
1534
1535 l_prog_name VARCHAR2(61);
1536
1537 BEGIN
1538
1539 l_prog_name := G_PKG_NAME||'.delete_row (V)';
1540
1541 DELETE FROM OKL_COST_ADJUSTMENTS_B WHERE id = p_id;
1542 DELETE FROM OKL_COST_ADJUSTMENTS_TL WHERE id = p_id;
1543
1544 x_return_status := G_RET_STS_SUCCESS;
1545
1546 EXCEPTION
1547
1548 WHEN OTHERS THEN
1549
1550 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1551 p_msg_name => G_DB_ERROR,
1552 p_token1 => G_PROG_NAME_TOKEN,
1553 p_token1_value => l_prog_name,
1554 p_token2 => G_SQLCODE_TOKEN,
1555 p_token2_value => sqlcode,
1556 p_token3 => G_SQLERRM_TOKEN,
1557 p_token3_value => sqlerrm);
1558
1559 x_return_status := G_RET_STS_UNEXP_ERROR;
1560
1561 END delete_row;
1562
1563
1564 -----------------------------
1565 -- PROCEDURE delete_row (REC)
1566 -----------------------------
1567 PROCEDURE delete_row(
1568 p_api_version IN NUMBER,
1569 p_init_msg_list IN VARCHAR2,
1570 x_return_status OUT NOCOPY VARCHAR2,
1571 x_msg_count OUT NOCOPY NUMBER,
1572 x_msg_data OUT NOCOPY VARCHAR2,
1573 p_cdjv_rec IN cdjv_rec_type) IS
1574
1575 l_return_status VARCHAR2(1);
1576
1577 l_prog_name VARCHAR2(61);
1578
1579 BEGIN
1580
1581 l_prog_name := G_PKG_NAME||'.delete_row (REC)';
1582
1583 IF p_init_msg_list = G_TRUE THEN
1584 FND_MSG_PUB.initialize;
1585 END IF;
1586
1587 delete_row (x_return_status => l_return_status,
1588 p_id => p_cdjv_rec.id);
1589
1590 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1591 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1592 ELSIF l_return_status = G_RET_STS_ERROR THEN
1593 RAISE OKL_API.G_EXCEPTION_ERROR;
1594 END IF;
1595
1596 x_return_status := l_return_status;
1597
1598 EXCEPTION
1599
1600 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1601
1602 x_return_status := G_RET_STS_ERROR;
1603
1604 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1605
1606 x_return_status := G_RET_STS_UNEXP_ERROR;
1607
1608 WHEN OTHERS THEN
1609
1610 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1611 p_msg_name => G_DB_ERROR,
1612 p_token1 => G_PROG_NAME_TOKEN,
1613 p_token1_value => l_prog_name,
1614 p_token2 => G_SQLCODE_TOKEN,
1615 p_token2_value => sqlcode,
1616 p_token3 => G_SQLERRM_TOKEN,
1617 p_token3_value => sqlerrm);
1618
1619 x_return_status := G_RET_STS_UNEXP_ERROR;
1620
1621 END delete_row;
1622
1623
1624 -------------------
1625 -- delete_row (TBL)
1626 -------------------
1627 PROCEDURE delete_row(
1628 p_api_version IN NUMBER,
1629 p_init_msg_list IN VARCHAR2,
1630 x_return_status OUT NOCOPY VARCHAR2,
1631 x_msg_count OUT NOCOPY NUMBER,
1632 x_msg_data OUT NOCOPY VARCHAR2,
1633 p_cdjv_tbl IN cdjv_tbl_type) IS
1634
1635 l_return_status VARCHAR2(1);
1636 i BINARY_INTEGER;
1637
1638 l_prog_name VARCHAR2(61);
1639
1640 BEGIN
1641
1642 l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
1643
1644 IF p_init_msg_list = G_TRUE THEN
1645 FND_MSG_PUB.initialize;
1646 END IF;
1647
1648 IF (p_cdjv_tbl.COUNT > 0) THEN
1649
1650 i := p_cdjv_tbl.FIRST;
1651
1652 LOOP
1653
1654 IF p_cdjv_tbl.EXISTS(i) THEN
1655
1656 delete_row (x_return_status => l_return_status,
1657 p_id => p_cdjv_tbl(i).id);
1658
1659 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1660 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1661 ELSIF l_return_status = G_RET_STS_ERROR THEN
1662 RAISE OKL_API.G_EXCEPTION_ERROR;
1663 END IF;
1664
1665 EXIT WHEN (i = p_cdjv_tbl.LAST);
1666 i := p_cdjv_tbl.NEXT(i);
1667
1668 END IF;
1669
1670 END LOOP;
1671
1672 ELSE
1673
1674 l_return_status := G_RET_STS_SUCCESS;
1675
1676 END IF;
1677
1678 x_return_status := l_return_status;
1679
1680 EXCEPTION
1681
1682 WHEN OTHERS THEN
1683
1684 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1685 p_msg_name => G_DB_ERROR,
1686 p_token1 => G_PROG_NAME_TOKEN,
1687 p_token1_value => l_prog_name,
1688 p_token2 => G_SQLCODE_TOKEN,
1689 p_token2_value => sqlcode,
1690 p_token3 => G_SQLERRM_TOKEN,
1691 p_token3_value => sqlerrm);
1692
1693 x_return_status := G_RET_STS_UNEXP_ERROR;
1694
1695 END delete_row;
1696
1697
1698 END OKL_CDJ_PVT;