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