[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUP_PRCPARAMS_PVT
Source
1 PACKAGE BODY OKL_SETUP_PRCPARAMS_PVT AS
2 /* $Header: OKLRPPRB.pls 115.1 2004/07/02 02:56:28 sgorantl noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_rec for: OKL_SIF_PRICE_PARMS_V
6 -- modified by smahapat 01-16-2002
7 ---------------------------------------------------------------------------
8 PROCEDURE get_rec (
9 p_sppv_rec IN sppv_rec_type,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_no_data_found OUT NOCOPY BOOLEAN,
12 x_sppv_rec OUT NOCOPY sppv_rec_type
13 ) IS
14 CURSOR okl_sppv_pk_csr (p_id IN NUMBER) IS
15 SELECT
16 ID,
17 OBJECT_VERSION_NUMBER,
18 NAME,
19 VERSION,
20 DATE_START,
21 NVL(DATE_END,OKL_API.G_MISS_DATE) DATE_END,
22 NVL(DESCRIPTION,G_MISS_CHAR) DESCRIPTION,
23 SPS_CODE,
24 DYP_CODE,
25 ARRAY_YN,
26 NVL(ATTRIBUTE_CATEGORY,G_MISS_CHAR) ATTRIBUTE_CATEGORY,
27 NVL(ATTRIBUTE1,G_MISS_CHAR) ATTRIBUTE1,
28 NVL(ATTRIBUTE2,G_MISS_CHAR) ATTRIBUTE2,
29 NVL(ATTRIBUTE3,G_MISS_CHAR) ATTRIBUTE3,
30 NVL(ATTRIBUTE4,G_MISS_CHAR) ATTRIBUTE4,
31 NVL(ATTRIBUTE5,G_MISS_CHAR) ATTRIBUTE5,
32 NVL(ATTRIBUTE6,G_MISS_CHAR) ATTRIBUTE6,
33 NVL(ATTRIBUTE7,G_MISS_CHAR) ATTRIBUTE7,
34 NVL(ATTRIBUTE8,G_MISS_CHAR) ATTRIBUTE8,
35 NVL(ATTRIBUTE9,G_MISS_CHAR) ATTRIBUTE9,
36 NVL(ATTRIBUTE10,G_MISS_CHAR) ATTRIBUTE10,
37 NVL(ATTRIBUTE11,G_MISS_CHAR) ATTRIBUTE11,
38 NVL(ATTRIBUTE12,G_MISS_CHAR) ATTRIBUTE12,
39 NVL(ATTRIBUTE13,G_MISS_CHAR) ATTRIBUTE13,
40 NVL(ATTRIBUTE14,G_MISS_CHAR) ATTRIBUTE14,
41 NVL(ATTRIBUTE15,G_MISS_CHAR) ATTRIBUTE15,
42 CREATED_BY,
43 LAST_UPDATED_BY,
44 CREATION_DATE,
45 LAST_UPDATE_DATE,
46 NVL(LAST_UPDATE_LOGIN,G_MISS_NUM) LAST_UPDATE_LOGIN
47 -- start change smahapat 01/11/02 - replace OKL_SIF_PRICE_PARMS_V by OKL_SIF_PRICE_PARMS
48 FROM OKL_SIF_PRICE_PARMS
49 WHERE OKL_SIF_PRICE_PARMS.id = p_id;
50 -- end change smahapat
51
52 l_okl_sppv_pk okl_sppv_pk_csr%ROWTYPE;
53 l_sppv_rec sppv_rec_type;
54 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
55 BEGIN
56 l_return_status := G_RET_STS_SUCCESS;
57 x_no_data_found := TRUE;
58
59 -- Get current database values
60 OPEN okl_sppv_pk_csr (p_sppv_rec.id);
61 FETCH okl_sppv_pk_csr INTO
62 l_sppv_rec.ID,
63 l_sppv_rec.OBJECT_VERSION_NUMBER,
64 l_sppv_rec.NAME,
65 l_sppv_rec.VERSION,
66 l_sppv_rec.DATE_START,
67 l_sppv_rec.DATE_END,
68 l_sppv_rec.DESCRIPTION,
69 l_sppv_rec.SPS_CODE,
70 l_sppv_rec.DYP_CODE,
71 l_sppv_rec.ARRAY_YN,
72 l_sppv_rec.ATTRIBUTE_CATEGORY,
73 l_sppv_rec.ATTRIBUTE1,
74 l_sppv_rec.ATTRIBUTE2,
75 l_sppv_rec.ATTRIBUTE3,
76 l_sppv_rec.ATTRIBUTE4,
77 l_sppv_rec.ATTRIBUTE5,
78 l_sppv_rec.ATTRIBUTE6,
79 l_sppv_rec.ATTRIBUTE7,
80 l_sppv_rec.ATTRIBUTE8,
81 l_sppv_rec.ATTRIBUTE9,
82 l_sppv_rec.ATTRIBUTE10,
83 l_sppv_rec.ATTRIBUTE11,
84 l_sppv_rec.ATTRIBUTE12,
85 l_sppv_rec.ATTRIBUTE13,
86 l_sppv_rec.ATTRIBUTE14,
87 l_sppv_rec.ATTRIBUTE15,
88 l_sppv_rec.CREATED_BY,
89 l_sppv_rec.LAST_UPDATED_BY,
90 l_sppv_rec.CREATION_DATE,
91 l_sppv_rec.LAST_UPDATE_DATE,
92 l_sppv_rec.LAST_UPDATE_LOGIN;
93 x_no_data_found := okl_sppv_pk_csr%NOTFOUND;
94 CLOSE okl_sppv_pk_csr;
95
96 x_sppv_rec := l_sppv_rec;
97 x_return_status := l_return_status;
98 EXCEPTION
99 WHEN OTHERS THEN
100
101 -- store SQL error message on message stack
102 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
103 p_msg_name => G_UNEXPECTED_ERROR,
104 p_token1 => G_SQLCODE_TOKEN,
105 p_token1_value => sqlcode,
106 p_token2 => G_SQLERRM_TOKEN,
107 p_token2_value => sqlerrm);
108 -- notify UNEXPECTED error for calling API.
109 x_return_status := G_RET_STS_UNEXP_ERROR;
110
111 IF (okl_sppv_pk_csr%ISOPEN) THEN
112 CLOSE okl_sppv_pk_csr;
113 END IF;
114
115 END get_rec;
116
117
118 ---------------------------------------------------------------------------
119 -- PROCEDURE get_changes_only for: OKL_SIF_PRICE_PARMS_V
120 -- To take care of the assumption that Everything except the Changed Fields
121 -- have G_MISS values in them
122 -- added by smahapat 01-16-2002
123 ---------------------------------------------------------------------------
124 PROCEDURE get_changes_only ( p_sppv_rec IN sppv_rec_type,
125 p_db_rec IN sppv_rec_type,
126 x_sppv_rec OUT NOCOPY sppv_rec_type )
127 IS
128 l_sppv_rec sppv_rec_type;
129 BEGIN
130 l_sppv_rec := p_sppv_rec;
131
132 IF p_db_rec.NAME = p_sppv_rec.NAME THEN
133 l_sppv_rec.NAME := G_MISS_CHAR;
134 END IF;
135
136 IF p_db_rec.VERSION = p_sppv_rec.VERSION THEN
137 l_sppv_rec.NAME := G_MISS_CHAR;
138 END IF;
139
140 IF p_db_rec.DATE_START = p_sppv_rec.DATE_START THEN
141 l_sppv_rec.DATE_START := G_MISS_DATE;
142 END IF;
143
144 IF p_db_rec.DATE_END IS NULL THEN
145 IF p_sppv_rec.DATE_END IS NULL THEN
146 l_sppv_rec.DATE_END := G_MISS_DATE;
147 END IF;
148 ELSIF p_db_rec.DATE_END = p_sppv_rec.DATE_END THEN
149 l_sppv_rec.DATE_END := G_MISS_DATE;
150 END IF;
151
152 IF p_db_rec.DESCRIPTION IS NULL THEN
153 IF p_sppv_rec.DESCRIPTION IS NULL THEN
154 l_sppv_rec.DESCRIPTION := G_MISS_CHAR;
155 END IF;
156 ELSIF p_db_rec.DESCRIPTION = p_sppv_rec.DESCRIPTION THEN
157 l_sppv_rec.DESCRIPTION := G_MISS_CHAR;
158 END IF;
159
160 IF p_db_rec.SPS_CODE = p_sppv_rec.SPS_CODE THEN
161 l_sppv_rec.SPS_CODE := G_MISS_CHAR;
162 END IF;
163
164 IF p_db_rec.DYP_CODE = p_sppv_rec.DYP_CODE THEN
165 l_sppv_rec.DYP_CODE := G_MISS_CHAR;
166 END IF;
167
168 IF p_db_rec.ARRAY_YN = p_sppv_rec.ARRAY_YN THEN
169 l_sppv_rec.ARRAY_YN := G_MISS_CHAR;
170 END IF;
171
172 IF p_db_rec.ATTRIBUTE_CATEGORY IS NULL THEN
173 IF p_sppv_rec.ATTRIBUTE_CATEGORY IS NULL THEN
174 l_sppv_rec.ATTRIBUTE_CATEGORY := G_MISS_CHAR;
175 END IF;
176 ELSIF p_db_rec.ATTRIBUTE_CATEGORY = p_sppv_rec.ATTRIBUTE_CATEGORY THEN
177 l_sppv_rec.ATTRIBUTE_CATEGORY := G_MISS_CHAR;
178 END IF;
179
180 IF p_db_rec.ATTRIBUTE1 IS NULL THEN
181 IF p_sppv_rec.ATTRIBUTE1 IS NULL THEN
182 l_sppv_rec.ATTRIBUTE1 := G_MISS_CHAR;
183 END IF;
184 ELSIF p_db_rec.ATTRIBUTE1 = p_sppv_rec.ATTRIBUTE1 THEN
185 l_sppv_rec.ATTRIBUTE1 := G_MISS_CHAR;
186 END IF;
187
188 IF p_db_rec.ATTRIBUTE2 IS NULL THEN
189 IF p_sppv_rec.ATTRIBUTE2 IS NULL THEN
190 l_sppv_rec.ATTRIBUTE2 := G_MISS_CHAR;
191 END IF;
192 ELSIF p_db_rec.ATTRIBUTE2 = p_sppv_rec.ATTRIBUTE2 THEN
193 l_sppv_rec.ATTRIBUTE2 := G_MISS_CHAR;
194 END IF;
195
196 IF p_db_rec.ATTRIBUTE3 IS NULL THEN
197 IF p_sppv_rec.ATTRIBUTE3 IS NULL THEN
198 l_sppv_rec.ATTRIBUTE3 := G_MISS_CHAR;
199 END IF;
200 ELSIF p_db_rec.ATTRIBUTE3 = p_sppv_rec.ATTRIBUTE3 THEN
201 l_sppv_rec.ATTRIBUTE3 := G_MISS_CHAR;
202 END IF;
203
204 IF p_db_rec.ATTRIBUTE4 IS NULL THEN
205 IF p_sppv_rec.ATTRIBUTE4 IS NULL THEN
206 l_sppv_rec.ATTRIBUTE4 := G_MISS_CHAR;
207 END IF;
208 ELSIF p_db_rec.ATTRIBUTE4 = p_sppv_rec.ATTRIBUTE4 THEN
209 l_sppv_rec.ATTRIBUTE4 := G_MISS_CHAR;
210 END IF;
211
212 IF p_db_rec.ATTRIBUTE5 IS NULL THEN
213 IF p_sppv_rec.ATTRIBUTE5 IS NULL THEN
214 l_sppv_rec.ATTRIBUTE5 := G_MISS_CHAR;
215 END IF;
216 ELSIF p_db_rec.ATTRIBUTE5 = p_sppv_rec.ATTRIBUTE5 THEN
217 l_sppv_rec.ATTRIBUTE5 := G_MISS_CHAR;
218 END IF;
219
220 IF p_db_rec.ATTRIBUTE6 IS NULL THEN
221 IF p_sppv_rec.ATTRIBUTE6 IS NULL THEN
222 l_sppv_rec.ATTRIBUTE6 := G_MISS_CHAR;
223 END IF;
224 ELSIF p_db_rec.ATTRIBUTE6 = p_sppv_rec.ATTRIBUTE6 THEN
225 l_sppv_rec.ATTRIBUTE6 := G_MISS_CHAR;
226 END IF;
227
228 IF p_db_rec.ATTRIBUTE7 IS NULL THEN
229 IF p_sppv_rec.ATTRIBUTE7 IS NULL THEN
230 l_sppv_rec.ATTRIBUTE7 := G_MISS_CHAR;
231 END IF;
232 ELSIF p_db_rec.ATTRIBUTE7 = p_sppv_rec.ATTRIBUTE7 THEN
233 l_sppv_rec.ATTRIBUTE7 := G_MISS_CHAR;
234 END IF;
235
236 IF p_db_rec.ATTRIBUTE8 IS NULL THEN
237 IF p_sppv_rec.ATTRIBUTE8 IS NULL THEN
238 l_sppv_rec.ATTRIBUTE8 := G_MISS_CHAR;
239 END IF;
240 ELSIF p_db_rec.ATTRIBUTE8 = p_sppv_rec.ATTRIBUTE8 THEN
241 l_sppv_rec.ATTRIBUTE8 := G_MISS_CHAR;
242 END IF;
243
244 IF p_db_rec.ATTRIBUTE9 IS NULL THEN
245 IF p_sppv_rec.ATTRIBUTE9 IS NULL THEN
246 l_sppv_rec.ATTRIBUTE9 := G_MISS_CHAR;
247 END IF;
248 ELSIF p_db_rec.ATTRIBUTE9 = p_sppv_rec.ATTRIBUTE9 THEN
249 l_sppv_rec.ATTRIBUTE9 := G_MISS_CHAR;
250 END IF;
251
252 IF p_db_rec.ATTRIBUTE10 IS NULL THEN
253 IF p_sppv_rec.ATTRIBUTE10 IS NULL THEN
254 l_sppv_rec.ATTRIBUTE10 := G_MISS_CHAR;
255 END IF;
256 ELSIF p_db_rec.ATTRIBUTE10 = p_sppv_rec.ATTRIBUTE10 THEN
257 l_sppv_rec.ATTRIBUTE10 := G_MISS_CHAR;
258 END IF;
259
260 IF p_db_rec.ATTRIBUTE11 IS NULL THEN
261 IF p_sppv_rec.ATTRIBUTE11 IS NULL THEN
262 l_sppv_rec.ATTRIBUTE11 := G_MISS_CHAR;
263 END IF;
264 ELSIF p_db_rec.ATTRIBUTE11 = p_sppv_rec.ATTRIBUTE11 THEN
265 l_sppv_rec.ATTRIBUTE11 := G_MISS_CHAR;
266 END IF;
267
268 IF p_db_rec.ATTRIBUTE12 IS NULL THEN
269 IF p_sppv_rec.ATTRIBUTE12 IS NULL THEN
270 l_sppv_rec.ATTRIBUTE12 := G_MISS_CHAR;
271 END IF;
272 ELSIF p_db_rec.ATTRIBUTE12 = p_sppv_rec.ATTRIBUTE12 THEN
273 l_sppv_rec.ATTRIBUTE12 := G_MISS_CHAR;
274 END IF;
275
276 IF p_db_rec.ATTRIBUTE13 IS NULL THEN
277 IF p_sppv_rec.ATTRIBUTE13 IS NULL THEN
278 l_sppv_rec.ATTRIBUTE13 := G_MISS_CHAR;
279 END IF;
280 ELSIF p_db_rec.ATTRIBUTE13 = p_sppv_rec.ATTRIBUTE13 THEN
281 l_sppv_rec.ATTRIBUTE13 := G_MISS_CHAR;
282 END IF;
283
284 IF p_db_rec.ATTRIBUTE5 IS NULL THEN
285 IF p_sppv_rec.ATTRIBUTE5 IS NULL THEN
286 l_sppv_rec.ATTRIBUTE5 := G_MISS_CHAR;
287 END IF;
288 ELSIF p_db_rec.ATTRIBUTE5 = p_sppv_rec.ATTRIBUTE5 THEN
289 l_sppv_rec.ATTRIBUTE5 := G_MISS_CHAR;
290 END IF;
291
292 IF p_db_rec.ATTRIBUTE15 IS NULL THEN
293 IF p_sppv_rec.ATTRIBUTE15 IS NULL THEN
294 l_sppv_rec.ATTRIBUTE15 := G_MISS_CHAR;
295 END IF;
296 ELSIF p_db_rec.ATTRIBUTE15 = p_sppv_rec.ATTRIBUTE15 THEN
297 l_sppv_rec.ATTRIBUTE15 := G_MISS_CHAR;
298 END IF;
299
300 x_sppv_rec := l_sppv_rec;
301 END get_changes_only;
302
303 ---------------------------------------------------------------------------
304 -- PROCEDURE determine_action for: OKL_SIF_PRICE_PARMS_V
305 -- This function helps in determining the various checks to be performed
306 -- for the new/updated record and also helps in determining whether a new
307 -- version is required or not
308 ---------------------------------------------------------------------------
309 FUNCTION determine_action (p_upd_sppv_rec IN sppv_rec_type,
310 p_db_sppv_rec IN sppv_rec_type,
311 p_date IN DATE
312 ) RETURN VARCHAR2 IS
313 l_action VARCHAR2(1);
314 l_sysdate DATE := trunc(SYSDATE);
315 BEGIN
316
317 /* Scenario 1: The Changed Field-Values can by-pass Validation */
318 IF p_upd_sppv_rec.date_start = G_MISS_DATE AND
319 p_upd_sppv_rec.date_end = G_MISS_DATE AND
320 p_upd_sppv_rec.sps_code = G_MISS_CHAR AND
321 p_upd_sppv_rec.dyp_code = G_MISS_CHAR AND
322 p_upd_sppv_rec.array_yn = G_MISS_CHAR THEN
323 l_action := '1';
324
325 /* Scenario 2: The Changed Field-Values include that needs Validation and Update
326 * but does not require a new vresion to be created
327 */
328 -- 1) Only End_Date is Changed
329 ELSIF (p_upd_sppv_rec.date_start = G_MISS_DATE AND
330 (p_upd_sppv_rec.date_end <> G_MISS_DATE OR
331 -- IS NULL Condition has been added in case end_date was updated to NULL
332 p_upd_sppv_rec.date_end IS NULL ) AND
333 p_upd_sppv_rec.sps_code = G_MISS_CHAR AND
334 p_upd_sppv_rec.dyp_code = G_MISS_CHAR AND
335 p_upd_sppv_rec.array_yn = G_MISS_CHAR) OR
336 -- 2) Critical Attributes are Changed but Start_Date is Today or Future
337 (p_upd_sppv_rec.date_start = G_MISS_DATE AND
338 p_db_sppv_rec.date_start >= p_date AND
339 (p_upd_sppv_rec.sps_code <> G_MISS_CHAR OR
340 p_upd_sppv_rec.dyp_code <> G_MISS_CHAR OR
341 p_upd_sppv_rec.array_yn <> G_MISS_CHAR)) OR
342 -- 3) Start_Date is Changed , but in Future
343 (p_upd_sppv_rec.date_start <> G_MISS_DATE AND
344 p_db_sppv_rec.date_start > p_date AND
345 p_upd_sppv_rec.date_start >= p_date) THEN
346 l_action := '2';
347 ELSE
348 /* Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record */
349 l_action := '3';
350 END IF;
351 RETURN(l_action);
352 END determine_action;
353
354 ---------------------------------------------------------------------------
355 -- PROCEDURE check_updates
356 -- To verify whether the requested changes from the screen are valid or not
357 ---------------------------------------------------------------------------
358 PROCEDURE check_updates (
359 p_sppv_rec IN sppv_rec_type,
360 x_return_status OUT NOCOPY VARCHAR2,
361 x_msg_data OUT NOCOPY VARCHAR2
362 ) IS
363 l_sppv_rec sppv_rec_type;
364 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
365 l_valid BOOLEAN;
366 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
367 BEGIN
368 l_return_status := G_RET_STS_SUCCESS;
369 l_sppv_rec := p_sppv_rec;
370
371 /* call check_overlaps */
372 l_attrib_tbl(1).attribute := 'name';
373 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_VARCHAR2;
374 l_attrib_tbl(1).value := l_sppv_rec.name;
375
376 okl_accounting_util.check_overlaps(p_id => l_sppv_rec.id,
377 p_attrib_tbl => l_attrib_tbl,
378 p_start_date_attribute_name => 'DATE_START',
379 p_start_date => l_sppv_rec.date_start,
380 p_end_date_attribute_name => 'DATE_END',
381 p_end_date => l_sppv_rec.date_end,
382 p_view => 'OKL_SIF_PRICE_PARMS_V',
383 x_return_status => l_return_status,
384 x_valid => l_valid);
385
386 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
387 x_return_status := G_RET_STS_UNEXP_ERROR;
388 RAISE G_EXCEPTION_HALT_PROCESSING;
389 ELSIF (l_return_status = G_RET_STS_ERROR) OR
390 (l_return_status = G_RET_STS_SUCCESS AND
391 l_valid <> TRUE) THEN
392 x_return_status := G_RET_STS_ERROR;
393 RAISE G_EXCEPTION_HALT_PROCESSING;
394 END IF;
395 x_return_status := l_return_status;
396 EXCEPTION
397 WHEN G_EXCEPTION_HALT_PROCESSING THEN
398 -- no processing necessary; validation can continue
399 -- with the next column
400 NULL;
401
402 WHEN OTHERS THEN
403 -- store SQL error message on message stack for caller
404 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
405 p_msg_name => G_UNEXPECTED_ERROR,
406 p_token1 => G_SQLCODE_TOKEN,
407 p_token1_value => sqlcode,
408 p_token2 => G_SQLERRM_TOKEN,
409 p_token2_value => sqlerrm );
410 -- notify caller of an UNEXPECTED error
411 x_return_status := G_RET_STS_UNEXP_ERROR;
412
413 END check_updates;
414
415 ---------------------------------------------------------------------------
416 -- PROCEDURE create_price_parm for: OKL_SIF_PRICE_PARMS_V
417 ---------------------------------------------------------------------------
418 PROCEDURE create_price_parm( p_api_version IN NUMBER,
419 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
420 p_sppv_rec IN sppv_rec_type,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_count OUT NOCOPY NUMBER,
423 x_msg_data OUT NOCOPY VARCHAR2,
424 x_sppv_rec OUT NOCOPY sppv_rec_type
425 ) IS
426 l_api_version CONSTANT NUMBER := 1;
427 l_api_name CONSTANT VARCHAR2(30) := 'create_price_parm';
428 l_no_data_found BOOLEAN := TRUE;
429 l_valid BOOLEAN := TRUE;
430 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
431 l_sppv_rec sppv_rec_type;
432 l_sysdate DATE := to_date(SYSDATE, 'DD/MM/YYYY');
433 BEGIN
434 l_return_status := G_RET_STS_SUCCESS;
435 l_sppv_rec := p_sppv_rec;
436
437 -- mvasudev -- 02/17/2002
438 -- Store NAME in UPPER CASE always
439 l_sppv_rec.NAME := UPPER(l_sppv_rec.NAME);
440 -- end, mvasudev -- 02/17/2002
441
442 /*
443 -- mvasudev COMMENTED , 06/13/2002
444 --check for the records with start and end dates less than sysdate
445 IF to_date(l_sppv_rec.date_start, 'DD/MM/YYYY') < l_sysdate OR
446 to_date(l_sppv_rec.date_end, 'DD/MM/YYYY') < l_sysdate THEN
447 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
448 p_msg_name => G_PAST_RECORDS);
449 RAISE G_EXCEPTION_ERROR;
450 END IF;
451 */
452
453 /* public api to insert_sif_price_parms */
454 okl_sif_price_parms_pub.insert_sif_price_parms(p_api_version => p_api_version,
455 p_init_msg_list => p_init_msg_list,
456 x_return_status => l_return_status,
457 x_msg_count => x_msg_count,
458 x_msg_data => x_msg_data,
459 p_sppv_rec => l_sppv_rec,
460 x_sppv_rec => x_sppv_rec);
461
462 IF l_return_status = G_RET_STS_ERROR THEN
463 RAISE G_EXCEPTION_ERROR;
464 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
465 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
466 END IF;
467
468 x_return_status := l_return_status;
469
470 EXCEPTION
471 WHEN G_EXCEPTION_ERROR THEN
472 x_return_status := G_RET_STS_ERROR;
473 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
474 x_return_status := G_RET_STS_UNEXP_ERROR;
475 WHEN OTHERS THEN
476 -- store SQL error message on message stack for caller
477 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
478 p_msg_name => G_UNEXPECTED_ERROR,
479 p_token1 => G_SQLCODE_TOKEN,
480 p_token1_value => sqlcode,
481 p_token2 => G_SQLERRM_TOKEN,
482 p_token2_value => sqlerrm );
483 -- notify caller of an UNEXPECTED error
484 x_return_status := G_RET_STS_UNEXP_ERROR;
485 END create_price_parm;
486
487 ---------------------------------------------------------------------------
488 -- PROCEDURE update_price_parm for: OKL_SIF_PRICE_PARMS_V
489 ---------------------------------------------------------------------------
490 PROCEDURE update_price_parm(p_api_version IN NUMBER,
491 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
492 p_sppv_rec IN sppv_rec_type,
493 x_return_status OUT NOCOPY VARCHAR2,
494 x_msg_count OUT NOCOPY NUMBER,
495 x_msg_data OUT NOCOPY VARCHAR2,
496 x_sppv_rec OUT NOCOPY sppv_rec_type
497 )
498 IS
499
500 CURSOR l_okl_sppv_pk_csr (p_id IN NUMBER) IS
501 SELECT
502 DATE_START,
503 DATE_END
504 FROM OKL_SIF_PRICE_PARMS
505 WHERE OKL_SIF_PRICE_PARMS.id = p_id;
506
507 l_api_version CONSTANT NUMBER := 1;
508 l_api_name CONSTANT VARCHAR2(30) := 'update_price_parm';
509 l_no_data_found BOOLEAN := TRUE;
510 l_valid BOOLEAN := TRUE;
511 l_oldversion_enddate DATE := to_date(SYSDATE, 'DD/MM/YYYY');
512 l_sysdate DATE := to_date(SYSDATE, 'DD/MM/YYYY');
513 l_db_sppv_rec sppv_rec_type; /* database copy */
514 l_upd_sppv_rec sppv_rec_type; /* input copy */
515 l_sppv_rec sppv_rec_type; /* latest with the retained changes */
516 l_tmp_sppv_rec sppv_rec_type; /* for any other purposes */
517 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
518 l_action VARCHAR2(1);
519 l_new_version VARCHAR2(100);
520 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
521 BEGIN
522 l_return_status := G_RET_STS_SUCCESS;
523 l_sppv_rec := p_sppv_rec;
524
525 -- END_DATE needs to be after START_DATE (sanity check)
526 -- and Cannot be less than SysDate
527 IF l_sppv_rec.date_end IS NOT NULL
528 AND TO_DATE(l_sppv_rec.date_end, 'DD/MM/YYYY') <> TO_DATE(G_MISS_DATE, 'DD/MM/YYYY')
529 AND
530 ( TO_DATE(l_sppv_rec.date_end, 'DD/MM/YYYY') < TO_DATE(l_sppv_rec.DATE_START, 'DD/MM/YYYY')
531 OR TO_DATE(l_sppv_rec.date_end, 'DD/MM/YYYY') < l_sysdate
532 )
533 THEN
534 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
535 p_msg_name => G_INVALID_VALUE,
536 p_token1 => G_COL_NAME_TOKEN,
537 p_token1_value => 'date_end' );
538 RAISE G_EXCEPTION_ERROR;
539 END IF;
540
541 -- Get current database values
542 OPEN l_okl_sppv_pk_csr (p_sppv_rec.id);
543 FETCH l_okl_sppv_pk_csr INTO
544 l_db_sppv_rec.DATE_START,
545 l_db_sppv_rec.DATE_END;
546 l_no_data_found := l_okl_sppv_pk_csr%NOTFOUND;
547 CLOSE l_okl_sppv_pk_csr;
548
549 IF l_no_data_found THEN
550 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
551 END IF;
552
553
554 -- start date can not be greater than old start date if the record is active
555 IF TO_DATE(l_db_sppv_rec.DATE_START,'DD/MM/YYYY') < l_sysdate
556 AND TO_DATE(l_sppv_rec.DATE_START, 'DD/MM/YYYY') > TO_DATE(l_db_sppv_rec.DATE_START, 'DD/MM/YYYY')
557 THEN
558 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
559 p_msg_name => G_INVALID_VALUE,
560 p_token1 => G_COL_NAME_TOKEN,
561 p_token1_value => 'DATE_START' );
562 RAISE G_EXCEPTION_ERROR;
563 END IF;
564
565
566 -- public api to update_price_parm
567 okl_sif_price_parms_pub.update_sif_price_parms(p_api_version => p_api_version,
568 p_init_msg_list => p_init_msg_list,
569 x_return_status => l_return_status,
570 x_msg_count => x_msg_count,
571 x_msg_data => x_msg_data,
572 p_sppv_rec => l_sppv_rec,
573 x_sppv_rec => x_sppv_rec);
574 IF l_return_status = G_RET_STS_ERROR THEN
575 RAISE G_EXCEPTION_ERROR;
576 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
577 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
578 END IF;
579
580 /*******************************************************************
581 * FOLLOWING CODE COMMENTED TO DISABLE MULTIPLE VERSIONING
582 * Jun-13-2002, mvasudev
583 *
584
585
586
587 -- mvasudev -- 02/17/2002
588 -- END_DATE needs to be after START_DATE (sanity check)
589 IF l_sppv_rec.date_end IS NOT NULL
590 AND to_date(l_sppv_rec.date_end, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY')
591 AND to_date(l_sppv_rec.date_end, 'DD/MM/YYYY') < to_date(l_sppv_rec.date_start, 'DD/MM/YYYY')
592 THEN
593 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
594 p_msg_name => G_INVALID_VALUE,
595 p_token1 => G_COL_NAME_TOKEN,
596 p_token1_value => 'END_DATE' );
597 END IF;
598 -- end, mvasudev -- 02/17/2002
599
600 -- fetch old details from the database
601 get_rec(p_sppv_rec => l_sppv_rec,
602 x_return_status => l_return_status,
603 x_no_data_found => l_no_data_found,
604 x_sppv_rec => l_db_sppv_rec);
605
606 IF l_return_status <> G_RET_STS_SUCCESS OR
607 l_no_data_found = TRUE THEN
608 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
609 END IF;
610
611 -- check for the records if start and end dates are in the past
612 IF to_date(l_db_sppv_rec.date_start,'DD/MM/YYYY') < l_sysdate AND
613 to_date(l_db_sppv_rec.date_end,'DD/MM/YYYY') < l_sysdate THEN
614 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
615 p_msg_name => G_PAST_RECORDS);
616 RAISE G_EXCEPTION_ERROR;
617 END IF;
618
619 -- retain the details that has been changed only
620 get_changes_only(p_sppv_rec => p_sppv_rec,
621 p_db_rec => l_db_sppv_rec,
622 x_sppv_rec => l_upd_sppv_rec);
623
624 /* mvasudev, 02/17/2002
625
626 -- check for start date greater than sysdate
627 IF to_date(l_upd_sppv_rec.date_start, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
628 to_date(l_upd_sppv_rec.date_start,'DD/MM/YYYY') < l_sysdate THEN
629 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
630 p_msg_name => G_START_DATE);
631 RAISE G_EXCEPTION_ERROR;
632 END IF;
633
634 -- check for end date greater than sysdate
635 IF to_date(l_upd_sppv_rec.date_end, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
636 to_date(l_upd_sppv_rec.date_end,'DD/MM/YYYY') < l_sysdate THEN
637 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
638 p_msg_name => G_END_DATE);
639 RAISE G_EXCEPTION_ERROR;
640 END IF;
641
642
643 -- START_DATE , if changed, can only be later than TODAY
644 IF to_date(l_upd_sppv_rec.date_start, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
645 to_date(l_upd_sppv_rec.date_start,'DD/MM/YYYY') <= l_sysdate THEN
646 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
647 p_msg_name => G_START_DATE);
648 RAISE G_EXCEPTION_ERROR;
649 END IF;
650
651 -- END_DATE, if changed, cannot be earlier than TODAY
652 IF to_date(l_upd_sppv_rec.date_end, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
653 to_date(l_upd_sppv_rec.date_end,'DD/MM/YYYY') < l_sysdate THEN
654 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
655 p_msg_name => G_END_DATE);
656 RAISE G_EXCEPTION_ERROR;
657 END IF;
658
659 -- end, mvasudev -- 02/17/2002
660
661
662 -- determine how the processing to be done
663 l_action := determine_action(p_upd_sppv_rec => l_upd_sppv_rec,
664 p_db_sppv_rec => l_db_sppv_rec,
665 p_date => l_sysdate);
666 -- Scenario 1: The Changed Field-Values can by-pass Validation *
667 IF l_action = '1' THEN
668 -- public api to update_price_parm *
669 okl_sif_price_parms_pub.update_sif_price_parms(p_api_version => p_api_version,
670 p_init_msg_list => p_init_msg_list,
671 x_return_status => l_return_status,
672 x_msg_count => x_msg_count,
673 x_msg_data => x_msg_data,
674 p_sppv_rec => l_upd_sppv_rec,
675 x_sppv_rec => x_sppv_rec);
676 IF l_return_status = G_RET_STS_ERROR THEN
677 RAISE G_EXCEPTION_ERROR;
678 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
679 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
680 END IF;
681
682 -- Scenario 2: The Changed Field-Values include that needs Validation and Update
683 ELSIF l_action = '2' THEN
684 -- create a temporary record with all relevant details from db and upd records
685 -- removed call to defaults_to_actuals() by smahapat 01-16-2002
686 l_sppv_rec := p_sppv_rec;
687
688 check_updates(p_sppv_rec => l_sppv_rec,
689 x_return_status => l_return_status,
690 x_msg_data => x_msg_data);
691 IF l_return_status = G_RET_STS_ERROR THEN
692 RAISE G_EXCEPTION_ERROR;
693 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
694 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
695 END IF;
696
697 -- public api to update price parms
698 okl_sif_price_parms_pub.update_sif_price_parms(p_api_version => p_api_version,
699 p_init_msg_list => p_init_msg_list,
700 x_return_status => l_return_status,
701 x_msg_count => x_msg_count,
702 x_msg_data => x_msg_data,
703 p_sppv_rec => l_upd_sppv_rec,
704 x_sppv_rec => x_sppv_rec);
705 IF l_return_status = G_RET_STS_ERROR THEN
706 RAISE G_EXCEPTION_ERROR;
707 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
708 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
709 END IF;
710
711 -- Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record
712 ELSIF l_action = '3' THEN
713
714 -- mvasudev -- 02/17/2002
715 -- DO NOT Update Old-record if new Start_Date is after Old End_Date
716 IF l_upd_sppv_rec.date_start <> G_MISS_DATE
717 AND l_db_sppv_rec.date_end IS NOT NULL
718 AND l_upd_sppv_rec.date_start > l_db_sppv_rec.date_end
719 THEN
720 NULL;
721 ELSE
722
723 -- for old version
724 IF l_upd_sppv_rec.date_start <> G_MISS_DATE THEN
725 l_oldversion_enddate := l_upd_sppv_rec.date_start - 1;
726 ELSE
727 --mvasudev , 02/17/2002
728 -- The earliest end_date, if changed , can be TODAY.
729
730 --l_oldversion_enddate := l_sysdate - 1;
731 l_oldversion_enddate := l_sysdate;
732
733 -- end, mvasudev -- 02/17/2002
734 END IF;
735
736 l_sppv_rec := l_db_sppv_rec;
737 l_sppv_rec.date_end := l_oldversion_enddate;
738
739 -- call verify changes to update the database
740 IF l_oldversion_enddate > l_db_sppv_rec.date_end THEN
741 check_updates(p_sppv_rec => l_sppv_rec,
742 x_return_status => l_return_status,
743 x_msg_data => x_msg_data);
744
745 IF l_return_status = G_RET_STS_ERROR THEN
746 RAISE G_EXCEPTION_ERROR;
747 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
748 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
749 END IF;
750 END IF;
751
752 -- public api to update formulae
753 okl_sif_price_parms_pub.update_sif_price_parms(p_api_version => p_api_version,
754 p_init_msg_list => p_init_msg_list,
755 x_return_status => l_return_status,
756 x_msg_count => x_msg_count,
757 x_msg_data => x_msg_data,
758 p_sppv_rec => l_sppv_rec,
759 x_sppv_rec => x_sppv_rec);
760
761 IF l_return_status = G_RET_STS_ERROR THEN
762 RAISE G_EXCEPTION_ERROR;
763 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
764 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
765 END IF;
766 END IF;
767 -- end,mvasudev -- 02/17/2002
768
769 -- for new version
770 -- create a temporary record with all relevant details from db and upd records
771 -- removed call to defaults_to_actuals() by smahapat 01-16-2002
772 l_sppv_rec := p_sppv_rec;
773
774 -- mvasudev , 02/17/2002
775 -- The earliest START_DATE, when Update, can be TOMORROW only
776 IF l_upd_sppv_rec.date_start = G_MISS_DATE THEN
777 --l_sppv_rec.date_start := l_sysdate ;
778 l_sppv_rec.date_start := l_sysdate + 1 ;
779 END IF;
780
781 l_attrib_tbl(1).attribute := 'name';
782 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_VARCHAR2;
783 l_attrib_tbl(1).value := l_sppv_rec.name;
784
785 okl_accounting_util.get_version(
786 p_attrib_tbl => l_attrib_tbl,
787 p_cur_version => l_sppv_rec.version,
788 p_end_date_attribute_name => 'DATE_END',
789 p_end_date => l_sppv_rec.date_end,
790 p_view => 'OKL_SIF_PRICE_PARMS_V',
791 x_return_status => l_return_status,
792 x_new_version => l_new_version);
793
794 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
795 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
796 ELSE
797 l_sppv_rec.version := l_new_version;
798 END IF;
799
800 l_sppv_rec.id := G_MISS_NUM;
801
802 -- call verify changes to update the database
803 IF l_sppv_rec.date_end > l_db_sppv_rec.date_end THEN
804 check_updates(p_sppv_rec => l_sppv_rec,
805 x_return_status => l_return_status,
806 x_msg_data => x_msg_data);
807 IF l_return_status = G_RET_STS_ERROR THEN
808 RAISE G_EXCEPTION_ERROR;
809 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
810 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
811 END IF;
812 END IF;
813
814 -- public api to insert price parms
815 okl_sif_price_parms_pub.insert_sif_price_parms(p_api_version => p_api_version,
816 p_init_msg_list => p_init_msg_list,
817 x_return_status => l_return_status,
818 x_msg_count => x_msg_count,
819 x_msg_data => x_msg_data,
820 p_sppv_rec => l_sppv_rec,
821 x_sppv_rec => x_sppv_rec);
822
823 IF l_return_status = G_RET_STS_ERROR THEN
824 RAISE G_EXCEPTION_ERROR;
825 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
826 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
827 END IF;
828
829 -- copy output to input structure to get the id
830 l_sppv_rec := x_sppv_rec;
831
832 END IF;
833 *******************************************************************/
834 -- end, 06/13/2002 , mvasudev
835
836 x_return_status := l_return_status;
837 EXCEPTION
838 WHEN G_EXCEPTION_ERROR THEN
839 x_return_status := G_RET_STS_ERROR;
840 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
841 x_return_status := G_RET_STS_UNEXP_ERROR;
842 WHEN OTHERS THEN
843 -- store SQL error message on message stack for caller
844 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
845 p_msg_name => G_UNEXPECTED_ERROR,
846 p_token1 => G_SQLCODE_TOKEN,
847 p_token1_value => sqlcode,
848 p_token2 => G_SQLERRM_TOKEN,
849 p_token2_value => sqlerrm );
850 -- notify caller of an UNEXPECTED error
851 x_return_status := G_RET_STS_UNEXP_ERROR;
852
853 END update_price_parm;
854
855 PROCEDURE create_price_parm(
856 p_api_version IN NUMBER,
857 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
858 p_sppv_tbl IN sppv_tbl_type,
859 x_return_status OUT NOCOPY VARCHAR2,
860 x_msg_count OUT NOCOPY NUMBER,
861 x_msg_data OUT NOCOPY VARCHAR2,
862 x_sppv_tbl OUT NOCOPY sppv_tbl_type)
863 IS
864 l_api_name CONSTANT VARCHAR2(30) := 'create_price_parm_tbl';
865 rec_num INTEGER := 0;
866 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
867 l_api_version CONSTANT NUMBER := 1;
868 BEGIN
869
870
871 FOR rec_num IN 1..p_sppv_tbl.COUNT
872 LOOP
873 create_price_parm(
874 p_api_version => p_api_version,
875 p_init_msg_list => p_init_msg_list,
876 x_return_status => x_return_status,
877 x_msg_count => x_msg_count,
878 x_msg_data => x_msg_data,
879 p_sppv_rec => p_sppv_tbl(rec_num),
880 x_sppv_rec => x_sppv_tbl(rec_num) );
881 END LOOP;
882
883 x_return_status := l_return_status;
884
885 EXCEPTION
886 WHEN G_EXCEPTION_ERROR THEN
887 x_return_status := G_RET_STS_ERROR;
888 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
889 x_return_status := G_RET_STS_UNEXP_ERROR;
890 WHEN OTHERS THEN
891 -- store SQL error message on message stack for caller
892 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
893 p_msg_name => G_UNEXPECTED_ERROR,
894 p_token1 => G_SQLCODE_TOKEN,
895 p_token1_value => sqlcode,
896 p_token2 => G_SQLERRM_TOKEN,
897 p_token2_value => sqlerrm );
898 -- notify caller of an UNEXPECTED error
899 x_return_status := G_RET_STS_UNEXP_ERROR;
900 END create_price_parm;
901
902
903 PROCEDURE update_price_parm(
904 p_api_version IN NUMBER,
905 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
906 p_sppv_tbl IN sppv_tbl_type,
907 x_return_status OUT NOCOPY VARCHAR2,
908 x_msg_count OUT NOCOPY NUMBER,
909 x_msg_data OUT NOCOPY VARCHAR2,
910 x_sppv_tbl OUT NOCOPY sppv_tbl_type)
911 IS
912 l_api_name CONSTANT VARCHAR2(30) := 'update_price_parm_tbl';
913 rec_num INTEGER := 0;
914 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
915 l_api_version CONSTANT NUMBER := 1;
916 BEGIN
917
918
919 FOR rec_num IN 1..p_sppv_tbl.COUNT
920 LOOP
921 update_price_parm(
922 p_api_version => p_api_version,
923 p_init_msg_list => p_init_msg_list,
924 x_return_status => x_return_status,
925 x_msg_count => x_msg_count,
926 x_msg_data => x_msg_data,
927 p_sppv_rec => p_sppv_tbl(rec_num),
928 x_sppv_rec => x_sppv_tbl(rec_num) );
929 END LOOP;
930
931 x_return_status := l_return_status;
932
933 EXCEPTION
934 WHEN G_EXCEPTION_ERROR THEN
935 x_return_status := G_RET_STS_ERROR;
936 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
937 x_return_status := G_RET_STS_UNEXP_ERROR;
938 WHEN OTHERS THEN
939 -- store SQL error message on message stack for caller
940 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
941 p_msg_name => G_UNEXPECTED_ERROR,
942 p_token1 => G_SQLCODE_TOKEN,
943 p_token1_value => sqlcode,
944 p_token2 => G_SQLERRM_TOKEN,
945 p_token2_value => sqlerrm );
946 -- notify caller of an UNEXPECTED error
947 x_return_status := G_RET_STS_UNEXP_ERROR;
948 END update_price_parm;
949
950
951 END OKL_SETUP_PRCPARAMS_PVT;