[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPFORMULAE_PVT
Source
1 PACKAGE BODY OKL_SETUPFORMULAE_PVT AS
2 /* $Header: OKLRSFMB.pls 115.12 2003/07/23 19:05:30 sgorantl noship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.SETUP.FORMULAS';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9
10 -- get_version is not required as new version will not be created while updating
11
12 /*
13 ---------------------------------------------------------------------------
14 -- PROCEDURE get_version to calculate the new version number for the
15 -- formula to be created
16 ---------------------------------------------------------------------------
17 PROCEDURE get_version(p_fmav_rec IN fmav_rec_type,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_new_version OUT NOCOPY VARCHAR2) IS
20 CURSOR okl_fma_laterversionsexist_csr (p_name IN Okl_Formulae_V.NAME%TYPE,
21 p_date IN Okl_Formulae_V.END_DATE%TYPE) IS
22 SELECT '1'
23 FROM Okl_Formulae_V
24 WHERE name = p_name
25 AND NVL(end_date,p_date) > p_date;
26
27 l_check VARCHAR2(1) := '?';
28 l_row_not_found BOOLEAN := FALSE;
29 BEGIN
30 IF p_fmav_rec.version = OKL_API.G_MISS_CHAR THEN
31 x_new_version := G_INIT_VERSION;
32 ELSE
33 -- Check for future versions of the same formula
34 OPEN okl_fma_laterversionsexist_csr (p_fmav_rec.name,
35 p_fmav_rec.end_date);
36 FETCH okl_fma_laterversionsexist_csr INTO l_check;
37 l_row_not_found := okl_fma_laterversionsexist_csr%NOTFOUND;
38 CLOSE okl_fma_laterversionsexist_csr;
39
40 IF l_row_not_found = TRUE then
41 x_new_version := TO_CHAR(TO_NUMBER(p_fmav_rec.version, G_VERSION_FORMAT)
42 + G_VERSION_MAJOR_INCREMENT, G_VERSION_FORMAT);
43 ELSE
44 x_new_version := TO_CHAR(TO_NUMBER(p_fmav_rec.version, G_VERSION_FORMAT)
45 + G_VERSION_MINOR_INCREMENT, G_VERSION_FORMAT);
46 END IF;
47 END IF;
48
49 x_return_status := OKL_API.G_RET_STS_SUCCESS;
50 EXCEPTION
51 WHEN OTHERS THEN
52 -- store SQL error message on message stack
53 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
54 p_msg_name => G_UNEXPECTED_ERROR,
55 p_token1 => G_SQLCODE_TOKEN,
56 p_token1_value => sqlcode,
57 p_token2 => G_SQLERRM_TOKEN,
58 p_token2_value => sqlerrm);
59 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
60
61 IF (okl_fma_laterversionsexist_csr%ISOPEN) THEN
62 CLOSE okl_fma_laterversionsexist_csr;
63 END IF;
64
65 END get_version;
66
67 */
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE get_rec for: OKL_FORMULAE_V
71 ---------------------------------------------------------------------------
72 PROCEDURE get_rec (
73 p_fmav_rec IN fmav_rec_type,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_no_data_found OUT NOCOPY BOOLEAN,
76 x_fmav_rec OUT NOCOPY fmav_rec_type
77 ) IS
78 CURSOR okl_fmav_pk_csr (p_id IN NUMBER) IS
79 SELECT
80 ID,
81 OBJECT_VERSION_NUMBER,
82 SFWT_FLAG,
83 CGR_ID,
84 FYP_CODE,
85 NAME,
86 FORMULA_STRING,
87 NVL(DESCRIPTION,OKL_API.G_MISS_CHAR) DESCRIPTION,
88 VERSION,
89 START_DATE,
90 NVL(END_DATE,OKL_API.G_MISS_DATE) END_DATE,
91 NVL(ATTRIBUTE_CATEGORY, OKL_API.G_MISS_CHAR) ATTRIBUTE_CATEGORY,
92 NVL(ATTRIBUTE1, OKL_API.G_MISS_CHAR) ATTRIBUTE1,
93 NVL(ATTRIBUTE2, OKL_API.G_MISS_CHAR) ATTRIBUTE2,
94 NVL(ATTRIBUTE3, OKL_API.G_MISS_CHAR) ATTRIBUTE3,
95 NVL(ATTRIBUTE4, OKL_API.G_MISS_CHAR) ATTRIBUTE4,
96 NVL(ATTRIBUTE5, OKL_API.G_MISS_CHAR) ATTRIBUTE5,
97 NVL(ATTRIBUTE6, OKL_API.G_MISS_CHAR) ATTRIBUTE6,
98 NVL(ATTRIBUTE7, OKL_API.G_MISS_CHAR) ATTRIBUTE7,
99 NVL(ATTRIBUTE8, OKL_API.G_MISS_CHAR) ATTRIBUTE8,
100 NVL(ATTRIBUTE9, OKL_API.G_MISS_CHAR) ATTRIBUTE9,
101 NVL(ATTRIBUTE10, OKL_API.G_MISS_CHAR) ATTRIBUTE10,
102 NVL(ATTRIBUTE11, OKL_API.G_MISS_CHAR) ATTRIBUTE11,
103 NVL(ATTRIBUTE12, OKL_API.G_MISS_CHAR) ATTRIBUTE12,
104 NVL(ATTRIBUTE13, OKL_API.G_MISS_CHAR) ATTRIBUTE13,
105 NVL(ATTRIBUTE14, OKL_API.G_MISS_CHAR) ATTRIBUTE14,
106 NVL(ATTRIBUTE15, OKL_API.G_MISS_CHAR) ATTRIBUTE15,
107 NVL(ORG_ID, OKL_API.G_MISS_NUM) ORG_ID,
108 NVL(THERE_CAN_BE_ONLY_ONE_YN, OKL_API.G_MISS_CHAR) THERE_CAN_BE_ONLY_ONE_YN,
109 CREATED_BY,
110 CREATION_DATE,
111 LAST_UPDATED_BY,
112 LAST_UPDATE_DATE,
113 NVL(LAST_UPDATE_LOGIN, OKL_API.G_MISS_NUM) LAST_UPDATE_LOGIN
114 FROM Okl_Formulae_V
115 WHERE okl_formulae_v.id = p_id;
116 l_okl_fmav_pk okl_fmav_pk_csr%ROWTYPE;
117 l_fmav_rec fmav_rec_type;
118 BEGIN
119 x_return_status := OKL_API.G_RET_STS_SUCCESS;
120 x_no_data_found := TRUE;
121
122 -- Get current database values
123 OPEN okl_fmav_pk_csr (p_fmav_rec.id);
124 FETCH okl_fmav_pk_csr INTO
125 l_fmav_rec.ID,
126 l_fmav_rec.OBJECT_VERSION_NUMBER,
127 l_fmav_rec.SFWT_FLAG,
128 l_fmav_rec.CGR_ID,
129 l_fmav_rec.FYP_CODE,
130 l_fmav_rec.NAME,
131 l_fmav_rec.FORMULA_STRING,
132 l_fmav_rec.DESCRIPTION,
133 l_fmav_rec.VERSION,
134 l_fmav_rec.START_DATE,
135 l_fmav_rec.END_DATE,
136 l_fmav_rec.ATTRIBUTE_CATEGORY,
137 l_fmav_rec.ATTRIBUTE1,
138 l_fmav_rec.ATTRIBUTE2,
139 l_fmav_rec.ATTRIBUTE3,
140 l_fmav_rec.ATTRIBUTE4,
141 l_fmav_rec.ATTRIBUTE5,
142 l_fmav_rec.ATTRIBUTE6,
143 l_fmav_rec.ATTRIBUTE7,
144 l_fmav_rec.ATTRIBUTE8,
145 l_fmav_rec.ATTRIBUTE9,
146 l_fmav_rec.ATTRIBUTE10,
147 l_fmav_rec.ATTRIBUTE11,
148 l_fmav_rec.ATTRIBUTE12,
149 l_fmav_rec.ATTRIBUTE13,
150 l_fmav_rec.ATTRIBUTE14,
151 l_fmav_rec.ATTRIBUTE15,
152 l_fmav_rec.ORG_ID,
153 l_fmav_rec.THERE_CAN_BE_ONLY_ONE_YN,
154 l_fmav_rec.CREATED_BY,
155 l_fmav_rec.CREATION_DATE,
156 l_fmav_rec.LAST_UPDATED_BY,
157 l_fmav_rec.LAST_UPDATE_DATE,
158 l_fmav_rec.LAST_UPDATE_LOGIN;
159 x_no_data_found := okl_fmav_pk_csr%NOTFOUND;
160 CLOSE okl_fmav_pk_csr;
161 x_fmav_rec := l_fmav_rec;
162 EXCEPTION
163 WHEN OTHERS THEN
164 -- store SQL error message on message stack
165 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
166 p_msg_name => G_UNEXPECTED_ERROR,
167 p_token1 => G_SQLCODE_TOKEN,
168 p_token1_value => sqlcode,
169 p_token2 => G_SQLERRM_TOKEN,
170 p_token2_value => sqlerrm);
171 -- notify UNEXPECTED error for calling API.
172 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
173
174 IF (okl_fmav_pk_csr%ISOPEN) THEN
175 CLOSE okl_fmav_pk_csr;
176 END IF;
177
178 END get_rec;
179
180 -- check_overlaps is not required as new version will not be created
181 -- while updating
182
183 /*
184 ---------------------------------------------------------------------------
185 -- PROCEDURE check_overlaps for: OKL_FORMULAE_V
186 -- To avoid overlapping of dates with other versions of the same formula
187 ---------------------------------------------------------------------------
188 PROCEDURE check_overlaps (
189 p_fmav_rec IN fmav_rec_type,
190 x_return_status OUT NOCOPY VARCHAR2,
191 x_valid OUT NOCOPY BOOLEAN
192 ) IS
193 CURSOR okl_fma_overlaps_csr (p_id IN Okl_Formulae_V.ID%TYPE,
194 p_name IN Okl_Formulae_V.NAME%TYPE,
195 p_start_date IN Okl_Formulae_V.START_DATE%TYPE,
196 p_end_date IN Okl_Formulae_V.END_DATE%TYPE
197 ) IS
198 SELECT '1'
199 FROM Okl_Formulae_V
200 WHERE NAME = p_name
201 AND ID <> p_id
202 AND (p_start_date BETWEEN START_DATE AND NVL(END_DATE, OKL_API.G_MISS_DATE) OR
203 p_end_date BETWEEN START_DATE AND NVL(END_DATE, OKL_API.G_MISS_DATE))
204 UNION ALL
205 SELECT '2'
206 FROM Okl_Formulae_V
207 WHERE NAME = p_name
208 AND ID <> p_id
209 AND p_start_date <= START_DATE
210 AND p_end_date >= NVL(END_DATE, OKL_API.G_MISS_DATE);
211
212 l_check VARCHAR2(1) := '?';
213 l_row_not_found BOOLEAN := FALSE;
214 BEGIN
215 x_valid := TRUE;
216 x_return_status := OKL_API.G_RET_STS_SUCCESS;
217
218 -- Check for formulae overlaps
219 OPEN okl_fma_overlaps_csr (p_fmav_rec.id,
220 p_fmav_rec.name,
221 p_fmav_rec.start_date,
222 p_fmav_rec.end_date);
223 FETCH okl_fma_overlaps_csr INTO l_check;
224 l_row_not_found := okl_fma_overlaps_csr%NOTFOUND;
225 CLOSE okl_fma_overlaps_csr;
226
227 IF l_row_not_found = FALSE then
228 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
229 p_msg_name => G_FMA_VERSION_OVERLAPS);
230 x_valid := FALSE;
231 x_return_status := OKL_API.G_RET_STS_ERROR;
232 END IF;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 -- store SQL error message on message stack
237 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
238 p_msg_name => G_UNEXPECTED_ERROR,
239 p_token1 => G_SQLCODE_TOKEN,
240 p_token1_value => sqlcode,
241 p_token2 => G_SQLERRM_TOKEN,
242 p_token2_value => sqlerrm);
243 x_valid := FALSE;
244 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
245
246 IF (okl_fma_overlaps_csr%ISOPEN) THEN
247 CLOSE okl_fma_overlaps_csr;
248 END IF;
249
250
251 END check_overlaps;
252 */
253
254 /*
255 ---------------------------------------------------------------------------
256 -- PROCEDURE check_constraints for: OKL_FORMULAE_V
257 -- To verify whether the dates are valid for both formula and operands
258 -- attached to it
259 ---------------------------------------------------------------------------
260 PROCEDURE check_constraints (
261 p_upd_fmav_rec IN fmav_rec_type,
262 p_fmav_rec IN fmav_rec_type,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_valid OUT NOCOPY BOOLEAN
265 ) IS
266 CURSOR okl_fma_constraints_csr (p_fma_id IN Okl_Fmla_Oprnds_V.fma_id%TYPE,
267 p_start_date IN Okl_Operands_V.START_DATE%TYPE,
268 p_end_date IN Okl_Operands_V.END_DATE%TYPE
269
270 ) IS
271 SELECT '1'
272 FROM Okl_Fmla_Oprnds_V fod,
273 Okl_Operands_V opd
274 WHERE fod.FMA_ID = p_fma_id
275 AND opd.ID = fod.OPD_ID
276 AND (opd.START_DATE > p_start_date OR
277 NVL(opd.END_DATE, p_end_date) < p_end_date);
278
279
280 SELECT '1'
281 FROM Okl_Operands_V opd
282 WHERE OPD.FMA_ID = p_fma_id
283 AND ((opd.START_DATE < p_start_date) OR
284 (NVL(opd.END_DATE, to_date('31/12/9999', 'DD/MM/YYYY'))) >
285 (NVL(p_end_date, to_date('31/12/9999', 'DD/MM/YYYY'))));
286
287 l_fmav_rec fmav_rec_type;
288 l_check VARCHAR2(1) := '?';
289 l_row_not_found BOOLEAN := FALSE;
290 BEGIN
291 x_valid := TRUE;
292 x_return_status := OKL_API.G_RET_STS_SUCCESS;
293
294 -- Check for operand dates
295 OPEN okl_fma_constraints_csr (p_upd_fmav_rec.id,
296 p_upd_fmav_rec.start_date,
297 p_upd_fmav_rec.end_date);
298 FETCH okl_fma_constraints_csr INTO l_check;
299 l_row_not_found := okl_fma_constraints_csr%NOTFOUND;
300 CLOSE okl_fma_constraints_csr;
301
302 IF NOT l_row_not_found then
303 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
304 p_msg_name => G_DATES_MISMATCH,
305 p_token1 => G_PARENT_TABLE_TOKEN,
306 p_token1_value => 'Okl_Formulae_V',
307 p_token2 => G_CHILD_TABLE_TOKEN,
308 p_token2_value => 'Okl_Operands_V');
309 x_valid := FALSE;
310 x_return_status := OKL_API.G_RET_STS_ERROR;
311 END IF;
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 -- store SQL error message on message stack
316 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
317 p_msg_name => G_UNEXPECTED_ERROR,
318 p_token1 => G_SQLCODE_TOKEN,
319 p_token1_value => sqlcode,
320 p_token2 => G_SQLERRM_TOKEN,
321 p_token2_value => sqlerrm);
322 x_valid := FALSE;
323 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
324
325 IF (okl_fma_constraints_csr%ISOPEN) THEN
326 CLOSE okl_fma_constraints_csr;
327 END IF;
328
329
330 END check_constraints;
331 */
332
333 /*
334
335 ---------------------------------------------------------------------------
336 -- PROCEDURE check_dsf_opd_dates for: OKL_DATA_SRC_FNCTNS_V
337 -- To fetch the operands that are attached to the existing version of the
338 -- function and verify the dates for the both
339 ---------------------------------------------------------------------------
340 PROCEDURE check_constraints (p_upd_fmav_rec IN fmav_rec_type,
341 p_fmav_rec IN fmav_rec_type,
342 x_return_status OUT NOCOPY VARCHAR2
343 ) IS
344 CURSOR okl_fma_linkedopds_csr (p_fma_id IN Okl_Formulae_V.id%TYPE) IS
345 SELECT opd.ID ID,
346 opd.START_DATE START_DATE,
347 opd.END_DATE
348 FROM Okl_Operands_B opd
349 WHERE opd.fma_ID = p_fma_id;
350
351 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
352 l_min_start_date DATE := NULL;
353 l_max_end_date DATE := NULL;
354
355 BEGIN
356 x_return_status := OKL_API.G_RET_STS_SUCCESS;
357
358 -- Get current database values
359 FOR okl_fma_linkedopds_rec in okl_fma_linkedopds_csr(p_upd_fmav_rec.id)
360 LOOP
361 IF l_min_start_date IS NULL AND l_max_end_date IS NULL THEN
362 l_min_start_date := okl_fma_linkedopds_rec.START_DATE;
363 l_max_end_date := okl_fma_linkedopds_rec.END_DATE;
364 ELSE
365 IF l_min_start_date > okl_fma_linkedopds_rec.START_DATE THEN
366 l_min_start_date := okl_fma_linkedopds_rec.START_DATE;
367 END IF;
368
369 IF l_max_end_date < okl_fma_linkedopds_rec.END_DATE THEN
370 l_max_end_date := okl_fma_linkedopds_rec.END_DATE;
371 END IF;
372 END IF;
373 END LOOP;
374
375 IF p_upd_fmav_rec.start_date > l_min_start_date OR
376 (p_upd_fmav_rec.end_date IS NOT NULL AND
377 p_upd_fmav_rec.end_date <> OKL_API.G_MISS_DATE AND
378 p_upd_fmav_rec.end_date < NVL(l_max_end_date, to_date(to_char('31/12/9999','DD/MM/YYYY'), 'DD/MM/YYYY'))) THEN
379 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
380 p_msg_name => G_DATES_MISMATCH,
381 p_token1 => G_PARENT_TABLE_TOKEN,
382 p_token1_value => 'Okl_Formulae_V',
383 p_token2 => G_CHILD_TABLE_TOKEN,
384 p_token2_value => 'Okl_Operands_V');
385 RAISE G_EXCEPTION_HALT_PROCESSING;
386 END IF;
387
388 EXCEPTION
389 WHEN G_EXCEPTION_HALT_PROCESSING THEN
390 -- no processing necessary; validation can continue
391 -- with the next column
392 x_return_status := OKL_API.G_RET_STS_ERROR;
393
394 IF (okl_fma_linkedopds_csr%ISOPEN) THEN
395 CLOSE okl_fma_linkedopds_csr;
396 END IF;
397
398 WHEN OTHERS THEN
399 -- store SQL error message on message stack
400 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
401 p_msg_name => G_UNEXPECTED_ERROR,
402 p_token1 => G_SQLCODE_TOKEN,
403 p_token1_value => sqlcode,
404 p_token2 => G_SQLERRM_TOKEN,
405 p_token2_value => sqlerrm);
406 -- notify UNEXPECTED error for calling API.
407 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
408
409 IF (okl_fma_linkedopds_csr%ISOPEN) THEN
410 CLOSE okl_fma_linkedopds_csr;
411 END IF;
412
413 END check_constraints;
414
415 */
416
417 ---------------------------------------------------------------------------
418 -- PROCEDURE check_fma_opd_dates for: OKL_FORMULAE_V
419 -- To fetch the operands that are attached to the existing version of the
420 -- function and verify the dates for the both
421 ---------------------------------------------------------------------------
422 PROCEDURE check_fma_opd_dates (p_upd_fmav_rec IN fmav_rec_type,
423 p_fmav_rec IN fmav_rec_type,
424 x_return_status OUT NOCOPY VARCHAR2
425 ) IS
426
427 CURSOR okl_fma_linkedopds_csr (p_fma_id IN Okl_Operands_V.fma_id%TYPE,
428 p_start_date DATE, p_end_date DATE) IS
429 SELECT '1'
430 FROM Okl_Operands_B opd
431 WHERE opd.FMA_ID = p_fma_id
432 AND ((opd.start_date < p_start_date) OR
433 (NVL(opd.end_date, TO_DATE('31/12/9999', 'DD/MM/YYYY')) > p_end_date )) ;
434
435
436 l_check VARCHAR2(1);
437 l_not_found BOOLEAN;
438
439 BEGIN
440 x_return_status := OKL_API.G_RET_STS_SUCCESS;
441
442 OPEN okl_fma_linkedopds_csr (p_upd_fmav_rec.id, p_upd_fmav_rec.start_date, p_upd_fmav_rec.end_date);
443 FETCH okl_fma_linkedopds_csr INTO l_check;
444 l_not_found := okl_fma_linkedopds_csr%NOTFOUND;
445 CLOSE okl_fma_linkedopds_csr;
446
447 IF NOT l_not_found THEN
448 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
449 p_msg_name => G_DATES_MISMATCH,
450 p_token1 => G_PARENT_TABLE_TOKEN,
451 p_token1_value => 'Formulae',
452 p_token2 => G_CHILD_TABLE_TOKEN,
453 p_token2_value => 'Operands');
454 RAISE G_EXCEPTION_HALT_PROCESSING;
455 END IF;
456
457 EXCEPTION
458 WHEN G_EXCEPTION_HALT_PROCESSING THEN
459 -- no processing necessary; validation can continue
460 -- with the next column
461 x_return_status := OKL_API.G_RET_STS_ERROR;
462
463 IF (okl_fma_linkedopds_csr%ISOPEN) THEN
464 CLOSE okl_fma_linkedopds_csr;
465 END IF;
466
467 WHEN OTHERS THEN
468 -- store SQL error message on message stack
469 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
470 p_msg_name => G_UNEXPECTED_ERROR,
471 p_token1 => G_SQLCODE_TOKEN,
472 p_token1_value => sqlcode,
473 p_token2 => G_SQLERRM_TOKEN,
474 p_token2_value => sqlerrm);
475 -- notify UNEXPECTED error for calling API.
476 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
477
478 IF (okl_fma_linkedopds_csr%ISOPEN) THEN
479 CLOSE okl_fma_linkedopds_csr;
480 END IF;
481
482 END check_fma_opd_dates;
483
484
485 ---------------------------------------------------------------------------
486 -- FUNCTION defaults_to_actuals
487 -- This function creates an output record with changed information from the
488 -- input structure and unchanged details from the database
489 ---------------------------------------------------------------------------
490 FUNCTION defaults_to_actuals (
491 p_upd_fmav_rec IN fmav_rec_type,
492 p_db_fmav_rec IN fmav_rec_type
493 ) RETURN fmav_rec_type IS
494 l_fmav_rec fmav_rec_type;
495 BEGIN
496 /* create a temporary record with all relevant details from db and upd records */
497 l_fmav_rec := p_db_fmav_rec;
498
499 IF p_upd_fmav_rec.description <> OKL_API.G_MISS_CHAR THEN
500 l_fmav_rec.description := p_upd_fmav_rec.description;
501 END IF;
502
503 IF p_upd_fmav_rec.start_date <> OKL_API.G_MISS_DATE THEN
504 l_fmav_rec.start_date := p_upd_fmav_rec.start_date;
505 END IF;
506
507 IF p_upd_fmav_rec.end_date <> OKL_API.G_MISS_DATE THEN
508 l_fmav_rec.end_date := p_upd_fmav_rec.end_date;
509 END IF;
510
511 IF p_upd_fmav_rec.cgr_id <> OKL_API.G_MISS_NUM THEN
512 l_fmav_rec.cgr_id := p_upd_fmav_rec.cgr_id;
513 END IF;
514
515 IF p_upd_fmav_rec.formula_string <> OKL_API.G_MISS_CHAR THEN
516 l_fmav_rec.formula_string := p_upd_fmav_rec.formula_string;
517 END IF;
518
519 IF p_upd_fmav_rec.fyp_code <> OKL_API.G_MISS_CHAR THEN
520 l_fmav_rec.fyp_code := p_upd_fmav_rec.fyp_code;
521 END IF;
522
523 IF p_upd_fmav_rec.attribute_category <> OKL_API.G_MISS_CHAR THEN
524 l_fmav_rec.attribute_category := p_upd_fmav_rec.attribute_category;
525 END IF;
526
527 IF p_upd_fmav_rec.attribute1 <> OKL_API.G_MISS_CHAR THEN
528 l_fmav_rec.attribute1 := p_upd_fmav_rec.attribute1;
529 END IF;
530
531 IF p_upd_fmav_rec.attribute2 <> OKL_API.G_MISS_CHAR THEN
532 l_fmav_rec.attribute2 := p_upd_fmav_rec.attribute2;
533 END IF;
534
535 IF p_upd_fmav_rec.attribute3 <> OKL_API.G_MISS_CHAR THEN
536 l_fmav_rec.attribute3 := p_upd_fmav_rec.attribute3;
537 END IF;
538
539 IF p_upd_fmav_rec.attribute4 <> OKL_API.G_MISS_CHAR THEN
540 l_fmav_rec.attribute4 := p_upd_fmav_rec.attribute4;
541 END IF;
542
543 IF p_upd_fmav_rec.attribute5 <> OKL_API.G_MISS_CHAR THEN
544 l_fmav_rec.attribute5 := p_upd_fmav_rec.attribute5;
545 END IF;
546
547 IF p_upd_fmav_rec.attribute6 <> OKL_API.G_MISS_CHAR THEN
548 l_fmav_rec.attribute6 := p_upd_fmav_rec.attribute6;
549 END IF;
550
551 IF p_upd_fmav_rec.attribute7 <> OKL_API.G_MISS_CHAR THEN
552 l_fmav_rec.attribute7 := p_upd_fmav_rec.attribute7;
553 END IF;
554
555 IF p_upd_fmav_rec.attribute8 <> OKL_API.G_MISS_CHAR THEN
556 l_fmav_rec.attribute8 := p_upd_fmav_rec.attribute8;
557 END IF;
558
559 IF p_upd_fmav_rec.attribute9 <> OKL_API.G_MISS_CHAR THEN
560 l_fmav_rec.attribute9 := p_upd_fmav_rec.attribute9;
561 END IF;
562
563 IF p_upd_fmav_rec.attribute10 <> OKL_API.G_MISS_CHAR THEN
564 l_fmav_rec.attribute10 := p_upd_fmav_rec.attribute10;
565 END IF;
566
567 IF p_upd_fmav_rec.attribute11 <> OKL_API.G_MISS_CHAR THEN
568 l_fmav_rec.attribute11 := p_upd_fmav_rec.attribute11;
569 END IF;
570
571 IF p_upd_fmav_rec.attribute12 <> OKL_API.G_MISS_CHAR THEN
572 l_fmav_rec.attribute12 := p_upd_fmav_rec.attribute12;
573 END IF;
574
575 IF p_upd_fmav_rec.attribute13 <> OKL_API.G_MISS_CHAR THEN
576 l_fmav_rec.attribute13 := p_upd_fmav_rec.attribute13;
577 END IF;
578
579 IF p_upd_fmav_rec.attribute14 <> OKL_API.G_MISS_CHAR THEN
580 l_fmav_rec.attribute14 := p_upd_fmav_rec.attribute14;
581 END IF;
582
583 IF p_upd_fmav_rec.attribute15 <> OKL_API.G_MISS_CHAR THEN
584 l_fmav_rec.attribute15 := p_upd_fmav_rec.attribute15;
585 END IF;
586
587 IF p_upd_fmav_rec.org_id <> OKL_API.G_MISS_NUM THEN
588 l_fmav_rec.org_id := p_upd_fmav_rec.org_id;
589 END IF;
590
591 IF p_upd_fmav_rec.there_can_be_only_one_yn <> OKL_API.G_MISS_CHAR THEN
592 l_fmav_rec.there_can_be_only_one_yn := p_upd_fmav_rec.there_can_be_only_one_yn;
593 END IF;
594
595 RETURN l_fmav_rec;
596 END defaults_to_actuals;
597
598 ---------------------------------------------------------------------------
599 -- PROCEDURE reorganize_inputs
600 -- This procedure is to reset the attributes in the input structure based
601 -- on the data from database
602 ---------------------------------------------------------------------------
603 PROCEDURE reorganize_inputs (
604 p_upd_fmav_rec IN OUT NOCOPY fmav_rec_type,
605 p_db_fmav_rec IN fmav_rec_type
606 ) IS
607 l_upd_fmav_rec fmav_rec_type;
608 l_db_fmav_rec fmav_rec_type;
609 BEGIN
610 /* create a temporary record with all relevant details from db and upd records */
611 l_upd_fmav_rec := p_upd_fmav_rec;
612 l_db_fmav_rec := p_db_fmav_rec;
613
614 IF l_upd_fmav_rec.description = l_db_fmav_rec.description THEN
615 l_upd_fmav_rec.description := OKL_API.G_MISS_CHAR;
616 END IF;
617
618 IF to_date(to_char(l_upd_fmav_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_fmav_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
619 l_upd_fmav_rec.start_date := OKL_API.G_MISS_DATE;
620 END IF;
621
622 IF to_date(to_char(l_upd_fmav_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_fmav_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
623 l_upd_fmav_rec.end_date := OKL_API.G_MISS_DATE;
624 END IF;
625
626 IF l_upd_fmav_rec.fyp_code = l_db_fmav_rec.fyp_code THEN
627 l_upd_fmav_rec.fyp_code := OKL_API.G_MISS_CHAR;
628 END IF;
629
630 IF l_upd_fmav_rec.cgr_id = l_db_fmav_rec.cgr_id THEN
631 l_upd_fmav_rec.cgr_id := OKL_API.G_MISS_NUM;
632 END IF;
633
634 IF l_upd_fmav_rec.formula_string = l_db_fmav_rec.formula_string THEN
635 l_upd_fmav_rec.formula_string := OKL_API.G_MISS_CHAR;
636 END IF;
637
638 IF l_upd_fmav_rec.attribute_category = l_db_fmav_rec.attribute_category THEN
639 l_upd_fmav_rec.attribute_category := OKL_API.G_MISS_CHAR;
640 END IF;
641
642 IF l_upd_fmav_rec.attribute1 = l_db_fmav_rec.attribute1 THEN
643 l_upd_fmav_rec.attribute1 := OKL_API.G_MISS_CHAR;
644 END IF;
645
646 IF l_upd_fmav_rec.attribute2 = l_db_fmav_rec.attribute2 THEN
647 l_upd_fmav_rec.attribute2 := OKL_API.G_MISS_CHAR;
648 END IF;
649
650 IF l_upd_fmav_rec.attribute3 = l_db_fmav_rec.attribute3 THEN
651 l_upd_fmav_rec.attribute3 := OKL_API.G_MISS_CHAR;
652 END IF;
653
654 IF l_upd_fmav_rec.attribute4 = l_db_fmav_rec.attribute4 THEN
655 l_upd_fmav_rec.attribute4 := OKL_API.G_MISS_CHAR;
656 END IF;
657
658 IF l_upd_fmav_rec.attribute5 = l_db_fmav_rec.attribute5 THEN
659 l_upd_fmav_rec.attribute5 := OKL_API.G_MISS_CHAR;
660 END IF;
661
662 IF l_upd_fmav_rec.attribute6 = l_db_fmav_rec.attribute6 THEN
663 l_upd_fmav_rec.attribute6 := OKL_API.G_MISS_CHAR;
664 END IF;
665
666 IF l_upd_fmav_rec.attribute7 = l_db_fmav_rec.attribute7 THEN
667 l_upd_fmav_rec.attribute7 := OKL_API.G_MISS_CHAR;
668 END IF;
669
670 IF l_upd_fmav_rec.attribute8 = l_db_fmav_rec.attribute8 THEN
671 l_upd_fmav_rec.attribute8 := OKL_API.G_MISS_CHAR;
672 END IF;
673
674 IF l_upd_fmav_rec.attribute9 = l_db_fmav_rec.attribute9 THEN
675 l_upd_fmav_rec.attribute9 := OKL_API.G_MISS_CHAR;
676 END IF;
677
678 IF l_upd_fmav_rec.attribute10 = l_db_fmav_rec.attribute10 THEN
679 l_upd_fmav_rec.attribute10 := OKL_API.G_MISS_CHAR;
680 END IF;
681
682 IF l_upd_fmav_rec.attribute11 = l_db_fmav_rec.attribute11 THEN
683 l_upd_fmav_rec.attribute11 := OKL_API.G_MISS_CHAR;
684 END IF;
685
686 IF l_upd_fmav_rec.attribute12 = l_db_fmav_rec.attribute12 THEN
687 l_upd_fmav_rec.attribute12 := OKL_API.G_MISS_CHAR;
688 END IF;
689
690 IF l_upd_fmav_rec.attribute13 = l_db_fmav_rec.attribute13 THEN
691 l_upd_fmav_rec.attribute13 := OKL_API.G_MISS_CHAR;
692 END IF;
693
694 IF l_upd_fmav_rec.attribute14 = l_db_fmav_rec.attribute14 THEN
695 l_upd_fmav_rec.attribute14 := OKL_API.G_MISS_CHAR;
696 END IF;
697
698 IF l_upd_fmav_rec.attribute15 = l_db_fmav_rec.attribute15 THEN
699 l_upd_fmav_rec.attribute15 := OKL_API.G_MISS_CHAR;
700 END IF;
701
702 IF l_upd_fmav_rec.there_can_be_only_one_yn = l_db_fmav_rec.there_can_be_only_one_yn THEN
703 l_upd_fmav_rec.there_can_be_only_one_yn := OKL_API.G_MISS_CHAR;
704 END IF;
705
706 IF l_upd_fmav_rec.org_id = l_db_fmav_rec.org_id THEN
707 l_upd_fmav_rec.org_id := OKL_API.G_MISS_NUM;
708 END IF;
709
710 p_upd_fmav_rec := l_upd_fmav_rec;
711
712 END reorganize_inputs;
713
714 -- check_updates is not required as new version will not be created while updating
715 /*
716 ---------------------------------------------------------------------------
717 -- PROCEDURE check_updates
718 -- To verify whether the requested changes from the screen are valid or not
719 ---------------------------------------------------------------------------
720 PROCEDURE check_updates (
721 p_upd_fmav_rec IN fmav_rec_type,
722 p_db_fmav_rec IN fmav_rec_type,
723 p_fmav_rec IN fmav_rec_type,
724 x_return_status OUT NOCOPY VARCHAR2,
725 x_msg_data OUT NOCOPY VARCHAR2
726 ) IS
727 l_fmav_rec fmav_rec_type;
728 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
729 l_valid BOOLEAN;
730 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
731 BEGIN
732 x_return_status := OKL_API.G_RET_STS_SUCCESS;
733 l_fmav_rec := p_fmav_rec;
734
735 IF p_upd_fmav_rec.start_date <> OKL_API.G_MISS_DATE OR
736 p_upd_fmav_rec.end_date <> OKL_API.G_MISS_DATE THEN
737
738 */
739 /* call check_overlaps */
740 /* l_attrib_tbl(1).attribute := 'NAME';
741 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_VARCHAR2;
742 l_attrib_tbl(1).value := l_fmav_rec.name;
743
744 okl_accounting_util.check_overlaps (p_id => l_fmav_rec.id,
745 p_attrib_tbl => l_attrib_tbl,
746 p_start_date_attribute_name => 'START_DATE',
747 p_start_date => l_fmav_rec.start_date,
748 p_end_date_attribute_name => 'END_DATE',
749 p_end_date => l_fmav_rec.end_date,
750 p_view => 'Okl_Formulae_V',
751 x_return_status => l_return_status,
752 x_valid => l_valid);
753
754 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
755 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
756 RAISE G_EXCEPTION_HALT_PROCESSING;
757 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
758 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
759 l_valid <> TRUE) THEN
760 x_return_status := OKL_API.G_RET_STS_ERROR;
761 RAISE G_EXCEPTION_HALT_PROCESSING;
762 END IF;
763 */
764 /* call check_constraints */
765 /* check_constraints(p_upd_fmav_rec => p_upd_fmav_rec,
766 p_fmav_rec => l_fmav_rec,
767 x_return_status => l_return_status,
768 x_valid => l_valid);
769 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
770 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
771 RAISE G_EXCEPTION_HALT_PROCESSING;
772 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
773 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
774 l_valid <> TRUE) THEN
775 x_return_status := OKL_API.G_RET_STS_ERROR;
776 RAISE G_EXCEPTION_HALT_PROCESSING;
777 END IF;
778
779 END IF;
780
781 EXCEPTION
782 WHEN G_EXCEPTION_HALT_PROCESSING THEN
783 -- no processing necessary; validation can continue
784 -- with the next column
785 NULL;
786
787 WHEN OTHERS THEN
788 -- store SQL error message on message stack for caller
789 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
790 p_msg_name => G_UNEXPECTED_ERROR,
791 p_token1 => G_SQLCODE_TOKEN,
792 p_token1_value => sqlcode,
793 p_token2 => G_SQLERRM_TOKEN,
794 p_token2_value => sqlerrm );
795 x_msg_data := 'Unexpected Database Error';
796 -- notify caller of an UNEXPECTED error
797 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
798
799 END check_updates;
800 */
801
802 -- determine_action is not required as new version will not be created while updating
803 /*
804
805 ---------------------------------------------------------------------------
806 -- PROCEDURE determine_action for: OKL_FORMULAE_V
807 -- This function helps in determining the various checks to be performed
808 -- for the new/updated record and also helps in determining whether a new
809 -- version is required or not
810 ---------------------------------------------------------------------------
811 FUNCTION determine_action (
812 p_upd_fmav_rec IN fmav_rec_type,
813 p_db_fmav_rec IN fmav_rec_type,
814 p_date IN DATE
815 ) RETURN VARCHAR2 IS
816 l_action VARCHAR2(1);
817 l_sysdate DATE := trunc(SYSDATE);
818 BEGIN
819 */
820 /* Scenario 1: Only description and/or descriptive flexfield changes */
821 /* IF p_upd_fmav_rec.start_date = OKL_API.G_MISS_DATE AND
822 p_upd_fmav_rec.end_date = OKL_API.G_MISS_DATE AND
823 p_upd_fmav_rec.cgr_id = OKL_API.G_MISS_NUM AND
824 p_upd_fmav_rec.fyp_code = OKL_API.G_MISS_CHAR AND
825 p_upd_fmav_rec.formula_string = OKL_API.G_MISS_CHAR THEN
826 l_action := '1';
827 */
828 /* Scenario 2: only changing description/descriptive flexfield changes
829 and end date for all records or changing anything for a future record other
830 than start date or modified start date is less than existing start date */
831 /* ELSIF (p_upd_fmav_rec.start_date = OKL_API.G_MISS_DATE AND
832 (p_upd_fmav_rec.end_date <> OKL_API.G_MISS_DATE OR
833 p_upd_fmav_rec.end_date IS NULL) AND
834 p_upd_fmav_rec.cgr_id = OKL_API.G_MISS_NUM AND
835 p_upd_fmav_rec.fyp_code = OKL_API.G_MISS_CHAR AND
836 p_upd_fmav_rec.formula_string = OKL_API.G_MISS_CHAR) OR
837 (p_upd_fmav_rec.start_date = OKL_API.G_MISS_DATE AND
838 p_db_fmav_rec.start_date >= p_date AND
839 (p_upd_fmav_rec.cgr_id <> OKL_API.G_MISS_NUM OR
840 p_upd_fmav_rec.fyp_code <> OKL_API.G_MISS_CHAR OR
841 p_upd_fmav_rec.formula_string <> OKL_API.G_MISS_CHAR)) OR
842 (p_upd_fmav_rec.start_date <> OKL_API.G_MISS_DATE AND
843 p_db_fmav_rec.start_date > p_date AND
844 p_upd_fmav_rec.start_date < p_db_fmav_rec.start_date) THEN
845 l_action := '2';
846 ELSE
847 l_action := '3';
848 END IF;
849 RETURN(l_action);
850 END determine_action;
851 */
852 -- get_fma_operands is not required as new version will not be created while updating
853 /*
854 ---------------------------------------------------------------------------
855 -- PROCEDURE get_fma_operands for: OKL_FORMULAE_V
856 -- To fetch the operands that are attached to the existing version of the
857 -- formula
858 ---------------------------------------------------------------------------
859 PROCEDURE get_fma_operands (p_upd_fmav_rec IN fmav_rec_type,
860 p_fmav_rec IN fmav_rec_type,
861 x_return_status OUT NOCOPY VARCHAR2,
862 x_count OUT NOCOPY NUMBER,
863 x_fodv_tbl OUT NOCOPY fodv_tbl_type
864 ) IS
865 CURSOR okl_fodv_fk_csr (p_fma_id IN Okl_Fmla_Oprnds_V.fma_id%TYPE) IS
866 SELECT OPD_ID,
867 LABEL
868 FROM Okl_Fmla_Oprnds_V fod
869 WHERE fod.FMA_ID = p_fma_id;
870
871 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
872 l_count NUMBER := 0;
873 l_fodv_tbl fodv_tbl_type;
874
875 BEGIN
876 x_return_status := OKL_API.G_RET_STS_SUCCESS;
877
878 -- Get current database values
879 FOR okl_fod_rec in okl_fodv_fk_csr(p_upd_fmav_rec.id)
880 LOOP
881 l_fodv_tbl(l_count).FMA_ID := p_fmav_rec.ID;
882 l_fodv_tbl(l_count).OPD_ID := okl_fod_rec.OPD_ID;
883 l_fodv_tbl(l_count).LABEL := okl_fod_rec.LABEL;
884 l_count := l_count + 1;
885 END LOOP;
886
887 x_count := l_count;
888 x_fodv_tbl := l_fodv_tbl;
889
890 EXCEPTION
891 WHEN OTHERS THEN
892 -- store SQL error message on message stack
893 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
894 p_msg_name => G_UNEXPECTED_ERROR,
895 p_token1 => G_SQLCODE_TOKEN,
896 p_token1_value => sqlcode,
897 p_token2 => G_SQLERRM_TOKEN,
898 p_token2_value => sqlerrm );
899 -- notify UNEXPECTED error for calling API.
900 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
901
902 IF (okl_fodv_fk_csr%ISOPEN) THEN
903 CLOSE okl_fodv_fk_csr;
904 END IF;
905
906 END get_fma_operands;
907
908 */
909
910 ---------------------------------------------------------------------------
911 -- PROCEDURE insert_formulae for: OKL_FORMULAE_V
912 ---------------------------------------------------------------------------
913 PROCEDURE insert_formulae(p_api_version IN NUMBER,
914 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
915 x_return_status OUT NOCOPY VARCHAR2,
916 x_msg_count OUT NOCOPY NUMBER,
917 x_msg_data OUT NOCOPY VARCHAR2,
918 p_fmav_rec IN fmav_rec_type,
919 x_fmav_rec OUT NOCOPY fmav_rec_type
920 ) IS
921 l_api_version CONSTANT NUMBER := 1;
922 l_api_name CONSTANT VARCHAR2(30) := 'insert_formulae';
923 l_valid BOOLEAN := TRUE;
924 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
925 l_fmav_rec fmav_rec_type;
926 l_sysdate DATE := to_date(to_char(SYSDATE,'DD/MM/YYYY'), 'DD/MM/YYYY');
927 BEGIN
928 x_return_status := OKL_API.G_RET_STS_SUCCESS;
929 l_fmav_rec := p_fmav_rec;
930
931 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
932 p_pkg_name => G_PKG_NAME,
933 p_init_msg_list => p_init_msg_list,
934 l_api_version => l_api_version,
935 p_api_version => p_api_version,
936 p_api_type => '_PVT',
937 x_return_status => l_return_status);
938 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
939 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
940 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
941 RAISE OKL_API.G_EXCEPTION_ERROR;
942 END IF;
943
944 /* check for the records with start and end dates less than sysdate */
945 /* IF to_date(to_char(l_fmav_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate OR
946 to_date(to_char(l_fmav_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
947 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
948 p_msg_name => G_PAST_RECORDS);
949 RAISE OKL_API.G_EXCEPTION_ERROR;
950 END IF;
951 */
952
953 -- Added by Santonyr
954
955 IF to_date(to_char(l_fmav_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
956 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
957 p_msg_name => G_PAST_RECORDS);
958 RAISE OKL_API.G_EXCEPTION_ERROR;
959 END IF;
960
961 /* public api to insert formulae */
962 -- Start of wraper code generated automatically by Debug code generator for okl_formulae_pub.insert_formulae
963 IF(L_DEBUG_ENABLED='Y') THEN
964 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
965 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
966 END IF;
967 IF(IS_DEBUG_PROCEDURE_ON) THEN
968 BEGIN
969 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSFMB.pls call okl_formulae_pub.insert_formulae ');
970 END;
971 END IF;
972 okl_formulae_pub.insert_formulae(p_api_version => p_api_version,
973 p_init_msg_list => p_init_msg_list,
974 x_return_status => l_return_status,
975 x_msg_count => x_msg_count,
976 x_msg_data => x_msg_data,
977 p_fmav_rec => l_fmav_rec,
978 x_fmav_rec => x_fmav_rec);
979 IF(IS_DEBUG_PROCEDURE_ON) THEN
980 BEGIN
981 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSFMB.pls call okl_formulae_pub.insert_formulae ');
982 END;
983 END IF;
984 -- End of wraper code generated automatically by Debug code generator for okl_formulae_pub.insert_formulae
985
986 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
987 RAISE OKL_API.G_EXCEPTION_ERROR;
988 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
989 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
990 END IF;
991 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
992 x_msg_data => x_msg_data);
993 EXCEPTION
994 WHEN OKL_API.G_EXCEPTION_ERROR THEN
995 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
996 p_pkg_name => G_PKG_NAME,
997 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
998 x_msg_count => x_msg_count,
999 x_msg_data => x_msg_data,
1000 p_api_type => '_PVT');
1001 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1002 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1003 p_pkg_name => G_PKG_NAME,
1004 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1005 x_msg_count => x_msg_count,
1006 x_msg_data => x_msg_data,
1007 p_api_type => '_PVT');
1008 WHEN OTHERS THEN
1009 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1010 p_pkg_name => G_PKG_NAME,
1011 p_exc_name => 'OTHERS',
1012 x_msg_count => x_msg_count,
1013 x_msg_data => x_msg_data,
1014 p_api_type => '_PVT');
1015
1016 END insert_formulae;
1017
1018 ---------------------------------------------------------------------------
1019 -- PROCEDURE update_formulae for: OKL_FORMULAE_V
1020 ---------------------------------------------------------------------------
1021 PROCEDURE update_formulae(p_api_version IN NUMBER,
1022 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1023 x_return_status OUT NOCOPY VARCHAR2,
1024 x_msg_count OUT NOCOPY NUMBER,
1025 x_msg_data OUT NOCOPY VARCHAR2,
1026 p_fmav_rec IN fmav_rec_type,
1027 x_fmav_rec OUT NOCOPY fmav_rec_type
1028 ) IS
1029 l_api_version CONSTANT NUMBER := 1;
1030 l_api_name CONSTANT VARCHAR2(30) := 'update_formulae';
1031 l_no_data_found BOOLEAN := TRUE;
1032 l_valid BOOLEAN := TRUE;
1033 l_oldversion_enddate DATE := to_date(to_char(SYSDATE,'DD/MM/YYYY'), 'DD/MM/YYYY');
1034 l_sysdate DATE := to_date(to_char(SYSDATE,'DD/MM/YYYY'), 'DD/MM/YYYY');
1035 l_db_fmav_rec fmav_rec_type; /* database copy */
1036 l_upd_fmav_rec fmav_rec_type; /* input copy */
1037 l_fmav_rec fmav_rec_type; /* latest with the retained changes */
1038 l_tmp_fmav_rec fmav_rec_type; /* for any other purposes */
1039 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1040 l_action VARCHAR2(1);
1041 l_new_version VARCHAR2(100);
1042 l_fod_count NUMBER := 0;
1043 l_fodv_tbl fodv_tbl_type;
1044 l_out_fodv_tbl fodv_tbl_type;
1045 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
1046 BEGIN
1047 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1048 l_upd_fmav_rec := p_fmav_rec;
1049
1050 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1051 p_pkg_name => G_PKG_NAME,
1052 p_init_msg_list => p_init_msg_list,
1053 l_api_version => l_api_version,
1054 p_api_version => p_api_version,
1055 p_api_type => '_PVT',
1056 x_return_status => l_return_status);
1057 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1058 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1059 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1060 RAISE OKL_API.G_EXCEPTION_ERROR;
1061 END IF;
1062
1063 /* fetch old details from the database */
1064 get_rec(p_fmav_rec => l_upd_fmav_rec,
1065 x_return_status => l_return_status,
1066 x_no_data_found => l_no_data_found,
1067 x_fmav_rec => l_db_fmav_rec);
1068 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS OR
1069 l_no_data_found = TRUE THEN
1070 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1071 END IF;
1072
1073 /* to reorganize the input accordingly */
1074 reorganize_inputs(p_upd_fmav_rec => l_upd_fmav_rec,
1075 p_db_fmav_rec => l_db_fmav_rec);
1076
1077
1078 /* check for start date greater than sysdate */
1079 /* IF to_date(to_char(l_upd_fmav_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(OKL_API.G_MISS_DATE,'DD/MM/YYYY'), 'DD/MM/YYYY') AND
1080 to_date(to_char(l_upd_fmav_rec.start_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
1081 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1082 p_msg_name => G_START_DATE);
1083 RAISE OKL_API.G_EXCEPTION_ERROR;
1084 END IF;
1085 */
1086
1087 /* check for start date greater than sysdate */
1088 IF to_date(to_char(l_upd_fmav_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(OKL_API.G_MISS_DATE,'DD/MM/YYYY'), 'DD/MM/YYYY') AND
1089 to_date(to_char(l_upd_fmav_rec.end_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
1090 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1091 p_msg_name => 'OKL_END_DATE');
1092 RAISE OKL_API.G_EXCEPTION_ERROR;
1093 END IF;
1094
1095 /*
1096 -- check for start date greater than sysdate
1097 IF to_date(to_char(l_upd_fmav_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(OKL_API.G_MISS_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') AND
1098 to_date(to_char(l_db_fmav_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(l_upd_fmav_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') AND
1099 to_date(to_char(l_db_fmav_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
1100 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1101 p_msg_name => G_PAST_RECORDS);
1102 RAISE OKL_API.G_EXCEPTION_ERROR;
1103 END IF;
1104
1105
1106 -- check for the records with start and end dates less than sysdate
1107 IF to_date(to_char(l_db_fmav_rec.start_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate AND
1108 to_date(to_char(l_db_fmav_rec.end_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
1109 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1110 p_msg_name => G_PAST_RECORDS);
1111 RAISE OKL_API.G_EXCEPTION_ERROR;
1112 END IF;
1113 */
1114
1115
1116 /* check_constraints(p_upd_fmav_rec => l_upd_fmav_rec,
1117 p_fmav_rec => l_db_fmav_rec,
1118 x_return_status => l_return_status,
1119 x_valid => l_valid);
1120 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1121 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1122 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1123 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
1124 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
1125 l_valid <> TRUE) THEN
1126 x_return_status := OKL_API.G_RET_STS_ERROR;
1127 RAISE OKL_API.G_EXCEPTION_ERROR;
1128 END IF;
1129 */
1130
1131 -- Check if the linked operands are within the date range of function
1132
1133 check_fma_opd_dates (p_upd_fmav_rec => l_upd_fmav_rec,
1134 p_fmav_rec => l_db_fmav_rec,
1135 x_return_status => l_return_status );
1136
1137 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1138 RAISE OKL_API.G_EXCEPTION_ERROR;
1139 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1140 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1141 END IF;
1142
1143
1144 -- Start of wraper code generated automatically by Debug code generator for okl_formulae_pub.update_formulae
1145 IF(L_DEBUG_ENABLED='Y') THEN
1146 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
1147 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
1148 END IF;
1149 IF(IS_DEBUG_PROCEDURE_ON) THEN
1150 BEGIN
1151 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSFMB.pls call okl_formulae_pub.update_formulae ');
1152 END;
1153 END IF;
1154 okl_formulae_pub.update_formulae(p_api_version => p_api_version,
1155 p_init_msg_list => p_init_msg_list,
1156 x_return_status => l_return_status,
1157 x_msg_count => x_msg_count,
1158 x_msg_data => x_msg_data,
1159 p_fmav_rec => l_upd_fmav_rec,
1160 x_fmav_rec => x_fmav_rec);
1161 IF(IS_DEBUG_PROCEDURE_ON) THEN
1162 BEGIN
1163 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSFMB.pls call okl_formulae_pub.update_formulae ');
1164 END;
1165 END IF;
1166 -- End of wraper code generated automatically by Debug code generator for okl_formulae_pub.update_formulae
1167 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1168 RAISE OKL_API.G_EXCEPTION_ERROR;
1169 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1170 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1171 END IF;
1172
1173
1174 /* determine how the processing to be done */
1175
1176 -- This is not required as new version will not be created while updating
1177
1178 /* l_action := determine_action(p_upd_fmav_rec => l_upd_fmav_rec,
1179 p_db_fmav_rec => l_db_fmav_rec,
1180 p_date => l_sysdate);
1181 */
1182 /* Scenario 1: only changing description and descriptive flexfields */
1183 /* IF l_action = '1' THEN
1184
1185 -- public api to update formulae
1186
1187 okl_formulae_pub.update_formulae(p_api_version => p_api_version,
1188 p_init_msg_list => p_init_msg_list,
1189 x_return_status => l_return_status,
1190 x_msg_count => x_msg_count,
1191 x_msg_data => x_msg_data,
1192 p_fmav_rec => l_upd_fmav_rec,
1193 x_fmav_rec => x_fmav_rec);
1194 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1195 RAISE OKL_API.G_EXCEPTION_ERROR;
1196 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1197 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1198 END IF;
1199
1200 -- Scenario 2: only changing description/descriptive flexfield changes
1201 -- and end date for all records or changing anything for a future record other
1202 -- than start date or modified start date is less than existing start date
1203
1204 ELSIF l_action = '2' THEN
1205 -- create a temporary record with all relevant details from db and upd records
1206 l_fmav_rec := defaults_to_actuals(p_upd_fmav_rec => l_upd_fmav_rec,
1207 p_db_fmav_rec => l_db_fmav_rec);
1208
1209 check_updates(p_upd_fmav_rec => l_upd_fmav_rec,
1210 p_db_fmav_rec => l_db_fmav_rec,
1211 p_fmav_rec => l_fmav_rec,
1212 x_return_status => l_return_status,
1213 x_msg_data => x_msg_data);
1214 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1215 RAISE OKL_API.G_EXCEPTION_ERROR;
1216 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1217 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1218 END IF;
1219
1220 -- public api to update formulae
1221 okl_formulae_pub.update_formulae(p_api_version => p_api_version,
1222 p_init_msg_list => p_init_msg_list,
1223 x_return_status => l_return_status,
1224 x_msg_count => x_msg_count,
1225 x_msg_data => x_msg_data,
1226 p_fmav_rec => l_upd_fmav_rec,
1227 x_fmav_rec => x_fmav_rec);
1228 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1229 RAISE OKL_API.G_EXCEPTION_ERROR;
1230 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1231 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1232 END IF;
1233
1234 Scenario 3: changing anything else i.e., anything including start date for current
1235 records or anything + start date always greater than existing start date for
1236 future records
1237 ELSIF l_action = '3' THEN
1238
1239 -- for old version
1240 IF l_upd_fmav_rec.start_date <> OKL_API.G_MISS_DATE THEN
1241 l_oldversion_enddate := l_upd_fmav_rec.start_date - 1;
1242 ELSE
1243 l_oldversion_enddate := l_sysdate - 1;
1244 END IF;
1245
1246 l_fmav_rec := l_db_fmav_rec;
1247 l_fmav_rec.end_date := l_oldversion_enddate;
1248
1249 -- call verify changes to update the database
1250 IF l_oldversion_enddate > l_db_fmav_rec.end_date THEN
1251 check_updates(p_upd_fmav_rec => l_upd_fmav_rec,
1252 p_db_fmav_rec => l_db_fmav_rec,
1253 p_fmav_rec => l_fmav_rec,
1254 x_return_status => l_return_status,
1255 x_msg_data => x_msg_data);
1256 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1257 RAISE OKL_API.G_EXCEPTION_ERROR;
1258 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1259 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1260 END IF;
1261 END IF;
1262
1263 public api to update formulae
1264 okl_formulae_pub.update_formulae(p_api_version => p_api_version,
1265 p_init_msg_list => p_init_msg_list,
1266 x_return_status => l_return_status,
1267 x_msg_count => x_msg_count,
1268 x_msg_data => x_msg_data,
1269 p_fmav_rec => l_fmav_rec,
1270 x_fmav_rec => x_fmav_rec);
1271 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1272 RAISE OKL_API.G_EXCEPTION_ERROR;
1273 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1274 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1275 END IF;
1276
1277 -- for new version
1278 -- create a temporary record with all relevant details from db and upd records
1279 l_fmav_rec := defaults_to_actuals(p_upd_fmav_rec => l_upd_fmav_rec,
1280 p_db_fmav_rec => l_db_fmav_rec);
1281
1282 IF l_upd_fmav_rec.start_date = OKL_API.G_MISS_DATE THEN
1283 l_fmav_rec.start_date := l_sysdate;
1284 END IF;
1285
1286 l_attrib_tbl(1).attribute := 'NAME';
1287 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_VARCHAR2;
1288 l_attrib_tbl(1).value := l_fmav_rec.name;
1289
1290 okl_accounting_util.get_version(p_attrib_tbl => l_attrib_tbl,
1291 p_cur_version => l_fmav_rec.version,
1292 p_end_date_attribute_name => 'END_DATE',
1293 p_end_date => l_fmav_rec.end_date,
1294 p_view => 'Okl_Formulae_V',
1295 x_return_status => l_return_status,
1296 x_new_version => l_new_version);
1297
1298 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1299 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1300 ELSE
1301 l_fmav_rec.version := l_new_version;
1302 END IF;
1303
1304 l_fmav_rec.id := OKL_API.G_MISS_NUM;
1305
1306 -- call verify changes to update the database
1307 IF l_fmav_rec.end_date > l_db_fmav_rec.end_date THEN
1308 check_updates(p_upd_fmav_rec => l_upd_fmav_rec,
1309 p_db_fmav_rec => l_db_fmav_rec,
1310 p_fmav_rec => l_fmav_rec,
1311 x_return_status => l_return_status,
1312 x_msg_data => x_msg_data);
1313 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1314 RAISE OKL_API.G_EXCEPTION_ERROR;
1315 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1316 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1317 END IF;
1318 END IF;
1319
1320 -- public api to insert formulae
1321 okl_formulae_pub.insert_formulae(p_api_version => p_api_version,
1322 p_init_msg_list => p_init_msg_list,
1323 x_return_status => l_return_status,
1324 x_msg_count => x_msg_count,
1325 x_msg_data => x_msg_data,
1326 p_fmav_rec => l_fmav_rec,
1327 x_fmav_rec => x_fmav_rec);
1328 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1329 RAISE OKL_API.G_EXCEPTION_ERROR;
1330 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1331 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1332 END IF;
1333
1334 -- copy output to input structure to get the id
1335 l_fmav_rec := x_fmav_rec;
1336
1337 -- operands carryover
1338 get_fma_operands(p_upd_fmav_rec => l_upd_fmav_rec,
1339 p_fmav_rec => l_fmav_rec,
1340 x_return_status => l_return_status,
1341 x_count => l_fod_count,
1342 x_fodv_tbl => l_fodv_tbl);
1343 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1344 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1345 END IF;
1346
1347 IF l_fod_count > 0 THEN
1348 okl_fmla_oprnds_pub.insert_fmla_oprnds(p_api_version => p_api_version,
1349 p_init_msg_list => p_init_msg_list,
1350 x_return_status => l_return_status,
1351 x_msg_count => x_msg_count,
1352 x_msg_data => x_msg_data,
1353 p_fodv_tbl => l_fodv_tbl,
1354 x_fodv_tbl => l_out_fodv_tbl);
1355 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1356 RAISE OKL_API.G_EXCEPTION_ERROR;
1357 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1358 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1359 END IF;
1360
1361 END IF;
1362 END IF;
1363 */
1364
1365 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1366 x_msg_data => x_msg_data);
1367 EXCEPTION
1368 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1369 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1370 p_pkg_name => G_PKG_NAME,
1371 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1372 x_msg_count => x_msg_count,
1373 x_msg_data => x_msg_data,
1374 p_api_type => '_PVT');
1375 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1376 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1377 p_pkg_name => G_PKG_NAME,
1378 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1379 x_msg_count => x_msg_count,
1380 x_msg_data => x_msg_data,
1381 p_api_type => '_PVT');
1382 WHEN OTHERS THEN
1383 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1384 p_pkg_name => G_PKG_NAME,
1385 p_exc_name => 'OTHERS',
1386 x_msg_count => x_msg_count,
1387 x_msg_data => x_msg_data,
1388 p_api_type => '_PVT');
1389
1390 END update_formulae;
1391
1392
1393 END OKL_SETUPFORMULAE_PVT;