[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUP_PRD_PRCTEMPL_PVT
Source
1 PACKAGE BODY OKL_SETUP_PRD_PRCTEMPL_PVT AS
2 /* $Header: OKLRPPEB.pls 120.2 2005/10/30 03:40:32 appldev noship $ */
3
4 ---------------------------------------------------------------------------
5 -- FUNCTION BOOLEAN_TO_CHAR
6 ---------------------------------------------------------------------------
7 FUNCTION BOOLEAN_TO_CHAR(p_flag IN BOOLEAN)
8 RETURN VARCHAR2
9 IS
10 l_boolean_char VARCHAR2(1);
11 BEGIN
12 IF (p_flag) THEN
13 l_boolean_char := G_TRUE;
14 ELSE
15 l_boolean_char := G_FALSE;
16 END IF;
17
18 RETURN l_boolean_char;
19 END BOOLEAN_TO_CHAR;
20
21 ---------------------------------------------------------------------------
22 -- PROCEDURE get_rec for: OKL_PRD_PRICE_TMPLS_V
23 ---------------------------------------------------------------------------
24 PROCEDURE get_rec (
25 p_pitv_rec IN pitv_rec_type,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_no_data_found OUT NOCOPY BOOLEAN,
28 x_pitv_rec OUT NOCOPY pitv_rec_type
29 ) IS
30 CURSOR okl_pit_pk_csr (p_id IN NUMBER) IS
31 SELECT
32 ID,
33 OBJECT_VERSION_NUMBER,
34 PDT_ID,
35 TEMPLATE_NAME,
36 TEMPLATE_PATH,
37 VERSION,
38 START_DATE,
39 END_DATE,
40 DESCRIPTION,
41 CREATED_BY,
42 CREATION_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATE_LOGIN
46 FROM OKL_PRD_PRICE_TMPLS
47 WHERE OKL_PRD_PRICE_TMPLS.id = p_id;
48
49 l_okl_pitv_pk okl_pit_pk_csr%ROWTYPE;
50 l_pitv_rec pitv_rec_type;
51 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
52 BEGIN
53 l_return_status := G_RET_STS_SUCCESS;
54 x_no_data_found := TRUE;
55
56 -- Get current database values
57 OPEN okl_pit_pk_csr (p_pitv_rec.id);
58 FETCH okl_pit_pk_csr INTO
59 l_pitv_rec.ID,
60 l_pitv_rec.OBJECT_VERSION_NUMBER,
61 l_pitv_rec.PDT_ID,
62 l_pitv_rec.TEMPLATE_NAME,
63 l_pitv_rec.TEMPLATE_PATH,
64 l_pitv_rec.VERSION,
65 l_pitv_rec.START_DATE,
66 l_pitv_rec.END_DATE,
67 l_pitv_rec.DESCRIPTION,
68 l_pitv_rec.CREATED_BY,
69 l_pitv_rec.CREATION_DATE,
70 l_pitv_rec.LAST_UPDATED_BY,
71 l_pitv_rec.LAST_UPDATE_DATE,
72 l_pitv_rec.LAST_UPDATE_LOGIN;
73 x_no_data_found := okl_pit_pk_csr%NOTFOUND;
74 CLOSE okl_pit_pk_csr;
75
76 x_pitv_rec := l_pitv_rec;
77 x_return_status := l_return_status;
78 EXCEPTION
79 WHEN OTHERS THEN
80
81 -- store SQL error message on message stack
82 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
83 p_msg_name => G_UNEXPECTED_ERROR,
84 p_token1 => G_SQLCODE_TOKEN,
85 p_token1_value => SQLCODE,
86 p_token2 => G_SQLERRM_TOKEN,
87 p_token2_value => SQLERRM);
88 -- notify UNEXPECTED error for calling API.
89 x_return_status := G_RET_STS_UNEXP_ERROR;
90
91 IF (okl_pit_pk_csr%ISOPEN) THEN
92 CLOSE okl_pit_pk_csr;
93 END IF;
94
95 END get_rec;
96
97 ---------------------------------------------------------------------------
98 -- PROCEDURE get_changes_only
99 -- To take care of the assumption that Everything
100 -- except the Changed Fields have G_MISS values in them
101 ---------------------------------------------------------------------------
102 PROCEDURE get_changes_only (
103 p_pitv_rec IN pitv_rec_type,
104 p_db_rec IN pitv_rec_type,
105 x_pitv_rec OUT NOCOPY pitv_rec_type )
106 IS
107 l_pitv_rec pitv_rec_type;
108 BEGIN
109 l_pitv_rec := p_pitv_rec;
110
111 IF p_db_rec.PDT_ID = p_pitv_rec.PDT_ID
112 THEN
113 l_pitv_rec.PDT_ID := G_MISS_NUM;
114 END IF;
115 IF p_db_rec.TEMPLATE_NAME = p_pitv_rec.TEMPLATE_NAME
116 THEN
117 l_pitv_rec.TEMPLATE_NAME := G_MISS_CHAR;
118 END IF;
119
120 IF p_db_rec.VERSION = p_pitv_rec.VERSION
121 THEN
122 l_pitv_rec.VERSION := G_MISS_CHAR;
123 END IF;
124
125 IF p_db_rec.START_DATE = p_pitv_rec.START_DATE
126 THEN
127 l_pitv_rec.START_DATE := G_MISS_DATE;
128 END IF;
129
130 IF p_db_rec.TEMPLATE_PATH IS NULL
131 THEN
132 IF p_pitv_rec.TEMPLATE_PATH IS NULL
133 THEN
134 l_pitv_rec.TEMPLATE_PATH := G_MISS_CHAR;
135 END IF;
136 ELSIF p_db_rec.TEMPLATE_PATH = p_pitv_rec.TEMPLATE_PATH
137 THEN
138 l_pitv_rec.TEMPLATE_PATH := G_MISS_CHAR;
139 END IF;
140
141 IF p_db_rec.END_DATE IS NULL
142 THEN
143 IF p_pitv_rec.END_DATE IS NULL
144 THEN
145 l_pitv_rec.END_DATE := G_MISS_DATE;
146 END IF;
147 ELSIF p_db_rec.END_DATE = p_pitv_rec.END_DATE
148 THEN
149 l_pitv_rec.END_DATE := G_MISS_DATE;
150 END IF;
151
152 IF p_db_rec.DESCRIPTION IS NULL
153 THEN
154 IF p_pitv_rec.DESCRIPTION IS NULL
155 THEN
156 l_pitv_rec.DESCRIPTION := G_MISS_CHAR;
157 END IF;
158 ELSIF p_db_rec.DESCRIPTION = p_pitv_rec.DESCRIPTION
159 THEN
160 l_pitv_rec.DESCRIPTION := G_MISS_CHAR;
161 END IF;
162
163 x_pitv_rec := l_pitv_rec;
164
165 END get_changes_only;
166
167 ---------------------------------------------------------------------------
168 -- PROCEDURE determine_action for: OKL_PRD_PRICE_TMPLS_V
169 -- This function helps in determining the various checks to be performed
170 -- for the new/updated record and also helps in determining whether a new
171 -- version is required or not
172 ---------------------------------------------------------------------------
173 FUNCTION determine_action (
174 p_upd_pitv_rec IN pitv_rec_type,
175 p_db_pitv_rec IN pitv_rec_type,
176 p_date IN DATE
177 ) RETURN VARCHAR2 IS
178 l_action VARCHAR2(1);
179 l_sysdate DATE := TRUNC(SYSDATE);
180 BEGIN
181
182 /* Scenario 1: The Changed Field-Values can by-pass Validation */
183 IF p_upd_pitv_rec.start_date = G_MISS_DATE AND
184 p_upd_pitv_rec.end_date = G_MISS_DATE AND
185 p_upd_pitv_rec.template_name = G_MISS_CHAR THEN
186 l_action := '1';
187 /* Scenario 2: The Changed Field-Values include that needs Validation and Update */
188
189 -- 1) End_Date is Changed
190 ELSIF (p_upd_pitv_rec.start_date = G_MISS_DATE AND
191 (p_upd_pitv_rec.end_date <> G_MISS_DATE
192 OR p_upd_pitv_rec.end_date IS NULL ) AND
193 p_upd_pitv_rec.template_name = G_MISS_CHAR ) OR
194 -- 2) Critical Attributes are Changed but does not mandate new version
195 -- as Start_Date is Not Changied
196 (p_upd_pitv_rec.start_date = G_MISS_DATE AND
197 p_db_pitv_rec.start_date >= p_date AND
198 (p_upd_pitv_rec.template_name <> G_MISS_CHAR)) OR
199 -- 3) Start_Date is Changed , but in Future
200 (p_upd_pitv_rec.start_date <> G_MISS_DATE AND
201 p_db_pitv_rec.start_date > p_date AND
202 p_upd_pitv_rec.start_date >= p_date) THEN
203 l_action := '2';
204 ELSE
205 /* Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record */
206 l_action := '3';
207 END IF;
208 RETURN(l_action);
209 END determine_action;
210
211 ---------------------------------------------------------------------------
212 -- PROCEDURE check_constraints for: OKL_PRD_PRICE_TMPLS_V
213 -- To verify whether the dates are valid for both Pricing Template and Products
214 -- attached to it
215 ---------------------------------------------------------------------------
216 PROCEDURE check_constraints (
217 p_pitv_rec IN pitv_rec_type,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_valid OUT NOCOPY BOOLEAN
220 )
221 IS
222 CURSOR okl_pit_constraints_csr (p_pit_rec IN pitv_rec_type)
223 IS
224 SELECT from_date,TO_DATE
225 FROM OKL_PRODUCTS
226 WHERE id = p_pit_rec.pdt_id;
227
228 l_pitv_rec pitv_rec_type;
229 l_valid BOOLEAN := FALSE;
230 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
231 l_token1_value VARCHAR2(100);
232 l_token2_value VARCHAR2(100);
233 BEGIN
234 l_valid := TRUE;
235 l_pitv_rec := p_pitv_rec;
236 l_return_status := G_RET_STS_SUCCESS;
237
238 FOR l_pdt_rec IN okl_pit_constraints_csr (l_pitv_rec)
239 LOOP
240 -- Check START_DATE
241 IF l_pitv_rec.start_date <> G_MISS_DATE
242 AND l_pdt_rec.from_date > l_pitv_rec.start_date THEN
243 l_valid := FALSE;
244 END IF;
245
246 --Check END_DATE
247 IF l_pdt_rec.TO_DATE IS NOT NULL THEN
248 IF l_pitv_rec.end_date IS NULL
249 OR l_pitv_rec.end_date = OKC_API.G_MISS_DATE
250 THEN
251 l_valid := FALSE;
252 ELSIF l_pitv_rec.end_date > l_pdt_rec.TO_DATE
253 THEN
254 l_valid := FALSE;
255 END IF;
256 END IF;
257
258 EXIT WHEN(l_valid <> TRUE);
259 END LOOP;
260
261 IF(l_valid <> TRUE) THEN
262
263 -- added akjain to fix bug # 2429053
264 -- Get the token value.
265 l_token2_value := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRCTEMPLAT_CRUPT',
266 p_attribute_code => 'OKL_PRODUCT_PRICING_TEMPLATE'
267 );
268
269 l_token1_value := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRCTEMPLAT_CRUPT',
270 p_attribute_code => 'OKL_PRODUCT'
271 );
272
273
274 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
275 p_msg_name => G_DATES_MISMATCH,
276 p_token1 => G_PARENT_TABLE_TOKEN,
277 p_token1_value => l_token1_value,
278 p_token2 => G_CHILD_TABLE_TOKEN,
279 p_token2_value => l_token2_value);
280 l_return_status := G_RET_STS_ERROR;
281 END IF;
282
283 x_return_status := l_return_status;
284 x_valid := l_valid;
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 -- store SQL error message on message stack
289 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
290 p_msg_name => G_UNEXPECTED_ERROR,
291 p_token1 => G_SQLCODE_TOKEN,
292 p_token1_value => SQLCODE,
293 p_token2 => G_SQLERRM_TOKEN,
294 p_token2_value => SQLERRM);
295 x_valid := FALSE;
296 x_return_status := G_RET_STS_UNEXP_ERROR;
297 IF (okl_pit_constraints_csr%ISOPEN) THEN
298 CLOSE okl_pit_constraints_csr;
299 END IF;
300
301 END check_constraints;
302
303
304 ---------------------------------------------------------------------------
305 -- PROCEDURE check_updates
306 -- To verify whether the requested changes from the screen are valid or not
307 ---------------------------------------------------------------------------
308 PROCEDURE check_updates (
309 p_pitv_rec IN pitv_rec_type,
310 x_return_status OUT NOCOPY VARCHAR2,
311 x_msg_data OUT NOCOPY VARCHAR2
312 ) IS
313 l_pitv_rec pitv_rec_type;
314 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
315 l_valid BOOLEAN;
316 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
317 BEGIN
318 l_return_status := G_RET_STS_SUCCESS;
319 l_pitv_rec := p_pitv_rec;
320
321 /* call check_overlaps */
322 l_attrib_tbl(1).attribute := 'pdt_id';
323 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_NUMBER;
324 l_attrib_tbl(1).value := l_pitv_rec.pdt_id;
325
326 okl_accounting_util.check_overlaps(p_id => l_pitv_rec.id,
327 p_attrib_tbl => l_attrib_tbl,
328 p_start_date_attribute_name => 'START_DATE',
329 p_start_date => l_pitv_rec.start_date,
330 p_end_date_attribute_name => 'END_DATE',
331 p_end_date => l_pitv_rec.end_date,
332 p_view => 'OKL_PRD_PRICE_TMPLS_V',
333 x_return_status => l_return_status,
334 x_valid => l_valid);
335 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
336 x_return_status := G_RET_STS_UNEXP_ERROR;
337 RAISE G_EXCEPTION_HALT_PROCESSING;
338 ELSIF (l_return_status = G_RET_STS_ERROR) OR
339 (l_return_status = G_RET_STS_SUCCESS AND
340 l_valid <> TRUE) THEN
341 x_return_status := G_RET_STS_ERROR;
342 RAISE G_EXCEPTION_HALT_PROCESSING;
343 END IF;
344
345 check_constraints (p_pitv_rec => l_pitv_rec,
346 x_return_status => l_return_status,
347 x_valid => l_valid);
348
349 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
350 x_return_status := G_RET_STS_UNEXP_ERROR;
351 RAISE G_EXCEPTION_HALT_PROCESSING;
352 ELSIF (l_return_status = G_RET_STS_ERROR) OR
353 (l_return_status = G_RET_STS_SUCCESS AND
354 l_valid <> TRUE) THEN
355
356 x_return_status := G_RET_STS_ERROR;
357 RAISE G_EXCEPTION_HALT_PROCESSING;
358 END IF;
359
360
361 x_return_status := l_return_status;
362 EXCEPTION
363 WHEN G_EXCEPTION_HALT_PROCESSING THEN
364 -- no processing necessary; validation can continue
365 -- with the next column
366 NULL;
367
368 WHEN OTHERS THEN
369 -- store SQL error message on message stack for caller
370 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
371 p_msg_name => G_UNEXPECTED_ERROR,
372 p_token1 => G_SQLCODE_TOKEN,
373 p_token1_value => SQLCODE,
374 p_token2 => G_SQLERRM_TOKEN,
375 p_token2_value => SQLERRM );
376 -- notify caller of an UNEXPECTED error
377 x_return_status := G_RET_STS_UNEXP_ERROR;
378
379 END check_updates;
380
381 ---------------------------------------------------------------------------
382 -- PROCEDURE insert_prd_price_tmpls for: OKL_PRD_PRICE_TMPLS_V
383 ---------------------------------------------------------------------------
384 PROCEDURE insert_prd_price_tmpls( p_api_version IN NUMBER,
385 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
386 x_return_status OUT NOCOPY VARCHAR2,
387 x_msg_count OUT NOCOPY NUMBER,
388 x_msg_data OUT NOCOPY VARCHAR2,
389 p_pitv_rec IN pitv_rec_type,
390 x_pitv_rec OUT NOCOPY pitv_rec_type
391 ) IS
392 l_api_version CONSTANT NUMBER := 1;
393 l_api_name CONSTANT VARCHAR2(30) := 'insert_prd_price_tmpls';
394 l_valid BOOLEAN := TRUE;
395 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
396 l_pitv_rec pitv_rec_type;
397 --25-Oct-2004 vthiruva. Fix for Bug#3944026
398 --Changed to_date() to trunc() for date comparisions.
399 l_sysdate DATE := TRUNC(SYSDATE);
400 BEGIN
401 l_return_status := G_RET_STS_SUCCESS;
402 l_pitv_rec := p_pitv_rec;
403
404 -- mvasudev -- 02/17/2002
405 -- Store NAME in UPPER CASE always
406 l_pitv_rec.TEMPLATE_NAME := UPPER(l_pitv_rec.TEMPLATE_NAME);
407 -- end, mvasudev -- 02/17/2002
408
409 -- mvasudev -- 06/13/2002
410 /*
411 -- mvasudev COMMENTED , 06/13/2002
412 --check for the records with start and end dates less than sysdate
413 IF TO_DATE(l_pitv_rec.start_date, 'DD/MM/YYYY') < l_sysdate OR
414 TO_DATE(l_pitv_rec.end_date, 'DD/MM/YYYY') < l_sysdate THEN
415 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
416 p_msg_name => G_PAST_RECORDS);
417 RAISE G_EXCEPTION_ERROR;
418 END IF;
419 */
420
421
422 /* Check if dates are consistent with Product Dates */
423 check_constraints (
424 p_pitv_rec => l_pitv_rec,
425 x_return_status => l_return_status,
426 x_valid => l_valid);
427
428 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
429 x_return_status := G_RET_STS_UNEXP_ERROR;
430 RAISE G_EXCEPTION_ERROR;
431 ELSIF (l_return_status = G_RET_STS_ERROR) OR
432 (l_return_status = G_RET_STS_SUCCESS AND
433 l_valid <> TRUE) THEN
434 x_return_status := G_RET_STS_ERROR;
435 RAISE G_EXCEPTION_ERROR;
436 END IF;
437
438 /* public api to insert pricing template*/
439 okl_prd_price_tmpls_pub.insert_prd_price_tmpls(p_api_version => p_api_version,
440 p_init_msg_list => p_init_msg_list,
441 x_return_status => l_return_status,
442 x_msg_count => x_msg_count,
443 x_msg_data => x_msg_data,
444 p_pitv_rec => l_pitv_rec,
445 x_pitv_rec => x_pitv_rec);
446
447 IF l_return_status = G_RET_STS_ERROR THEN
448 RAISE G_EXCEPTION_ERROR;
449 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
450 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
451 END IF;
452
453 x_return_status := l_return_status;
454 EXCEPTION
455 WHEN G_EXCEPTION_ERROR THEN
456 x_return_status := G_RET_STS_ERROR;
457 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
458 x_return_status := G_RET_STS_UNEXP_ERROR;
459 WHEN OTHERS THEN
460 -- store SQL error message on message stack for caller
461 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
462 p_msg_name => G_UNEXPECTED_ERROR,
463 p_token1 => G_SQLCODE_TOKEN,
464 p_token1_value => SQLCODE,
465 p_token2 => G_SQLERRM_TOKEN,
466 p_token2_value => SQLERRM );
467 -- notify caller of an UNEXPECTED error
468 x_return_status := G_RET_STS_UNEXP_ERROR;
469 END insert_prd_price_tmpls;
470
471 ---------------------------------------------------------------------------
472 -- PROCEDURE update_prd_price_tmpls for: OKL_PRD_PRICE_TMPLS_V
473 ---------------------------------------------------------------------------
474 PROCEDURE update_prd_price_tmpls(p_api_version IN NUMBER,
475 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
476 x_return_status OUT NOCOPY VARCHAR2,
477 x_msg_count OUT NOCOPY NUMBER,
478 x_msg_data OUT NOCOPY VARCHAR2,
479 p_pitv_rec IN pitv_rec_type,
480 x_pitv_rec OUT NOCOPY pitv_rec_type
481 )
482 IS
483 CURSOR l_okl_pitv_pk_csr (p_id IN NUMBER) IS
484 SELECT
485 START_DATE,
486 END_DATE
487 FROM OKL_PRD_PRICE_TMPLS
488 WHERE OKL_PRD_PRICE_TMPLS.id = p_id;
489
490 l_api_version CONSTANT NUMBER := 1;
491 l_api_name CONSTANT VARCHAR2(30) := 'update_stream_type';
492 l_no_data_found BOOLEAN := TRUE;
493 l_valid BOOLEAN := TRUE;
494 --25-Oct-2004 vthiruva. Fix for Bug#3944026
495 --Changed to_date() to trunc() for date comparisions.
496 l_oldversion_enddate DATE := TRUNC(SYSDATE);
497 l_sysdate DATE := TRUNC(SYSDATE);
498 l_db_pitv_rec pitv_rec_type; /* database copy */
499 l_upd_pitv_rec pitv_rec_type; /* input copy */
500 l_pitv_rec pitv_rec_type; /* latest with the retained changes */
501 l_tmp_pitv_rec pitv_rec_type; /* for any other purposes */
502 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
503 l_action VARCHAR2(1);
504 l_new_version VARCHAR2(100);
505 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
506 BEGIN
507 l_return_status := G_RET_STS_SUCCESS;
508
509 l_pitv_rec := p_pitv_rec;
510
511 -- END_DATE needs to be after START_DATE (sanity check)
512 -- and Cannot be less than SysDate
513 /*
514 ** 25-Oct-2004 vthiruva -- Fix for Bug#3944026 start
515 ** Changed to_date() to trunc() for date comparisions.
516 */
517 IF l_pitv_rec.end_date IS NOT NULL
518 AND l_pitv_rec.end_date <> G_MISS_DATE
519 AND
520 (TRUNC(l_pitv_rec.end_date) < TRUNC(l_pitv_rec.start_date)
521 OR TRUNC(l_pitv_rec.end_date) < l_sysdate
522 )
523 THEN
524 /*
525 ** 25-Oct-2004 vthiruva -- Fix for Bug#3944026 end
526 */
527 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
528 p_msg_name => G_INVALID_VALUE,
529 p_token1 => G_COL_NAME_TOKEN,
530 p_token1_value => 'END_DATE' );
531 RAISE G_EXCEPTION_ERROR;
532 END IF;
533
534 -- Get current database values
535 OPEN l_okl_pitv_pk_csr (p_pitv_rec.id);
536 FETCH l_okl_pitv_pk_csr INTO
537 l_db_pitv_rec.START_DATE,
538 l_db_pitv_rec.END_DATE;
539 l_no_data_found := l_okl_pitv_pk_csr%NOTFOUND;
540 CLOSE l_okl_pitv_pk_csr;
541
542 IF l_no_data_found THEN
543 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
544 END IF;
545
546
547 -- start date can not be greater than old start date if the record is active
548 /*
549 ** 25-Oct-2004 vthiruva -- Fix for Bug#3944026 start
550 ** Changed to_date() to trunc() for date comparisions.
551 */
552 IF TRUNC(l_db_pitv_rec.start_date) < l_sysdate
553 AND TRUNC(l_pitv_rec.start_date) > TRUNC(l_db_pitv_rec.start_date)
554 THEN
555 /*
556 ** 25-Oct-2004 vthiruva -- Fix for Bug#3944026 end
557 */
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 => 'START_DATE' );
562 RAISE G_EXCEPTION_ERROR;
563 END IF;
564
565 /* Check if dates are consistent with Product Dates */
566 check_constraints (
567 p_pitv_rec => l_pitv_rec,
568 x_return_status => l_return_status,
569 x_valid => l_valid);
570
571 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
572 x_return_status := G_RET_STS_UNEXP_ERROR;
573 RAISE G_EXCEPTION_ERROR;
574 ELSIF (l_return_status = G_RET_STS_ERROR) OR
575 (l_return_status = G_RET_STS_SUCCESS AND
576 l_valid <> TRUE) THEN
577 x_return_status := G_RET_STS_ERROR;
578 RAISE G_EXCEPTION_ERROR;
579 END IF;
580
581
582 -- public api to update_prd_price_tmpls
583 okl_prd_price_tmpls_pub.update_prd_price_tmpls(p_api_version => p_api_version,
584 p_init_msg_list => p_init_msg_list,
585 x_return_status => l_return_status,
586 x_msg_count => x_msg_count,
587 x_msg_data => x_msg_data,
588 p_pitv_rec => l_pitv_rec,
589 x_pitv_rec => x_pitv_rec);
590 IF l_return_status = G_RET_STS_ERROR THEN
591 RAISE G_EXCEPTION_ERROR;
592 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
593 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
594 END IF;
595
596 /*******************************************************************
597 * FOLLOWING CODE COMMENTED TO DISABLE MULTIPLE VERSIONING
598 * Jun-13-2002, mvasudev
599 *
600
601 -- mvasudev -- 02/17/2002
602 -- END_DATE needs to be after START_DATE (sanity check)
603 IF l_pitv_rec.end_date IS NOT NULL
604 AND TO_DATE(l_pitv_rec.end_date, 'DD/MM/YYYY') <> TO_DATE(G_MISS_DATE, 'DD/MM/YYYY')
605 AND TO_DATE(l_pitv_rec.end_date, 'DD/MM/YYYY') < TO_DATE(l_pitv_rec.start_date, 'DD/MM/YYYY')
606 THEN
607 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
608 p_msg_name => G_INVALID_VALUE,
609 p_token1 => G_COL_NAME_TOKEN,
610 p_token1_value => 'END_DATE' );
611 END IF;
612 -- end, mvasudev -- 02/17/2002
613
614 -- fetch old details from the database
615 get_rec(p_pitv_rec => p_pitv_rec,
616 x_return_status => l_return_status,
617 x_no_data_found => l_no_data_found,
618 x_pitv_rec => l_db_pitv_rec);
619 IF l_return_status <> G_RET_STS_SUCCESS OR
620 l_no_data_found = TRUE THEN
621 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
622 END IF;
623
624 -- check for the records if start and end dates are in the past
625 IF TO_DATE(l_db_pitv_rec.start_date,'DD/MM/YYYY') < l_sysdate AND
626 TO_DATE(l_db_pitv_rec.end_date,'DD/MM/YYYY') < l_sysdate THEN
627 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
628 p_msg_name => G_PAST_RECORDS);
629 RAISE G_EXCEPTION_ERROR;
630 END IF;
631
632 -- retain the details that has been changed only
633 get_changes_only(p_pitv_rec => p_pitv_rec,
634 p_db_rec => l_db_pitv_rec,
635 x_pitv_rec => l_upd_pitv_rec);
636 IF l_return_status <> G_RET_STS_SUCCESS OR
637 l_no_data_found = TRUE THEN
638 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
639 END IF;
640
641 -- mvasudev, 02/17/2002
642 -- check for start date greater than sysdate
643 IF to_date(l_upd_pitv_rec.start_date, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
644 to_date(l_upd_pitv_rec.start_date,'DD/MM/YYYY') < l_sysdate THEN
645 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
646 p_msg_name => G_START_DATE);
647 RAISE G_EXCEPTION_ERROR;
648 END IF;
649
650 -- check for end date greater than sysdate
651 IF to_date(l_upd_pitv_rec.end_date, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
652 to_date(l_upd_pitv_rec.end_date,'DD/MM/YYYY') < l_sysdate THEN
653 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
654 p_msg_name => G_END_DATE);
655 RAISE G_EXCEPTION_ERROR;
656 END IF;
657
658
659 -- START_DATE , if changed, can only be later than TODAY
660 IF TO_DATE(l_upd_pitv_rec.start_date, 'DD/MM/YYYY') <> TO_DATE(G_MISS_DATE, 'DD/MM/YYYY') AND
661 TO_DATE(l_upd_pitv_rec.start_date,'DD/MM/YYYY') <= l_sysdate THEN
662 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
663 p_msg_name => G_START_DATE);
664 RAISE G_EXCEPTION_ERROR;
665 END IF;
666
667 -- END_DATE, if changed, cannot be earlier than TODAY
668 IF TO_DATE(l_upd_pitv_rec.end_date, 'DD/MM/YYYY') <> TO_DATE(G_MISS_DATE, 'DD/MM/YYYY') AND
669 TO_DATE(l_upd_pitv_rec.end_date,'DD/MM/YYYY') < l_sysdate THEN
670 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
671 p_msg_name => G_END_DATE);
672 RAISE G_EXCEPTION_ERROR;
673 END IF;
674
675 -- end, mvasudev -- 02/17/2002
676
677 -- determine how the processing to be done
678 l_action := determine_action(p_upd_pitv_rec => l_upd_pitv_rec,
679 p_db_pitv_rec => l_db_pitv_rec,
680 p_date => l_sysdate);
681
682 -- Scenario 1: The Changed Field-Values can by-pass Validation *
683 IF l_action = '1' THEN
684 -- public api to update_stream_type *
685 okl_prd_price_tmpls_pub.update_prd_price_tmpls(p_api_version => p_api_version,
686 p_init_msg_list => p_init_msg_list,
687 x_return_status => l_return_status,
688 x_msg_count => x_msg_count,
689 x_msg_data => x_msg_data,
690 p_pitv_rec => l_upd_pitv_rec,
691 x_pitv_rec => x_pitv_rec);
692 IF l_return_status = G_RET_STS_ERROR THEN
693 RAISE G_EXCEPTION_ERROR;
694 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
695 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
696 END IF;
697
698 -- Scenario 2: The Changed Field-Values include that needs Validation and Update *
699 ELSIF l_action = '2' THEN
700
701 check_updates(p_pitv_rec => l_pitv_rec,
702 x_return_status => l_return_status,
703 x_msg_data => x_msg_data);
704
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 -- public api to update Pricing Template *
712 okl_prd_price_tmpls_pub.update_prd_price_tmpls(p_api_version => p_api_version,
713 p_init_msg_list => p_init_msg_list,
714 x_return_status => l_return_status,
715 x_msg_count => x_msg_count,
716 x_msg_data => x_msg_data,
717 p_pitv_rec => l_upd_pitv_rec,
718 x_pitv_rec => x_pitv_rec);
719
720 IF l_return_status = G_RET_STS_ERROR THEN
721 RAISE G_EXCEPTION_ERROR;
722 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
723 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
724 END IF;
725
726 -- Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record
727 ELSIF l_action = '3' THEN
728
729 -- mvasudev -- 02/17/2002
730 -- DO NOT Update Old-record if new Start_Date is after Old End_Date
731 IF l_upd_pitv_rec.start_date <> G_MISS_DATE
732 AND l_db_pitv_rec.end_date IS NOT NULL
733 AND l_upd_pitv_rec.start_date > l_db_pitv_rec.end_date
734 THEN
735 NULL;
736 ELSE
737 -- for old version
738 IF l_upd_pitv_rec.start_date <> G_MISS_DATE THEN
739 l_oldversion_enddate := l_upd_pitv_rec.start_date - 1;
740 ELSE
741 --mvasudev , 02/17/2002
742 -- The earliest end_date, if changed , can be TODAY.
743
744 --l_oldversion_enddate := l_sysdate - 1;
745 l_oldversion_enddate := l_sysdate;
746
747 -- end, mvasudev -- 02/17/2002
748 END IF;
749
750 l_pitv_rec := l_db_pitv_rec;
751 l_pitv_rec.end_date := l_oldversion_enddate;
752
753 -- call verify changes to update the database *
754 IF l_oldversion_enddate > l_db_pitv_rec.end_date THEN
755
756
757 check_updates(p_pitv_rec => l_pitv_rec,
758 x_return_status => l_return_status,
759 x_msg_data => x_msg_data);
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
768 -- public api to update formulae
769 okl_prd_price_tmpls_pub.update_prd_price_tmpls(p_api_version => p_api_version,
770 p_init_msg_list => p_init_msg_list,
771 x_return_status => l_return_status,
772 x_msg_count => x_msg_count,
773 x_msg_data => x_msg_data,
774 p_pitv_rec => l_pitv_rec,
775 x_pitv_rec => x_pitv_rec);
776
777 IF l_return_status = G_RET_STS_ERROR THEN
778 RAISE G_EXCEPTION_ERROR;
779 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
780 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
781 END IF;
782 END IF;
783 -- end,mvasudev -- 02/17/2002
784
785 -- for new version
786 -- mvasudev , 02/17/2002
787 -- The earliest START_DATE, when Update, can be TOMORROW only
788 IF l_upd_pitv_rec.start_date = G_MISS_DATE THEN
789 --l_pitv_rec.start_date := l_sysdate ;
790 l_pitv_rec.start_date := l_sysdate + 1 ;
791 END IF;
792
793 l_attrib_tbl(1).attribute := 'PDT_ID';
794 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_NUMBER;
795 l_attrib_tbl(1).value := l_pitv_rec.pdt_id;
796
797 okl_accounting_util.get_version(
798 p_attrib_tbl => l_attrib_tbl,
799 p_cur_version => l_pitv_rec.version,
800 p_end_date_attribute_name => 'END_DATE',
801 p_end_date => l_pitv_rec.end_date,
802 p_view => 'OKL_PRD_PRICE_TMPLS_V',
803 x_return_status => l_return_status,
804 x_new_version => l_new_version);
805
806 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
807 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
808 ELSE
809 l_pitv_rec.version := l_new_version;
810 END IF;
811
812 l_pitv_rec.id := G_MISS_NUM;
813
814 -- call verify changes to update the database
815 IF l_pitv_rec.end_date > l_db_pitv_rec.end_date THEN
816 check_updates(p_pitv_rec => l_pitv_rec,
817 x_return_status => l_return_status,
818 x_msg_data => x_msg_data);
819
820 IF l_return_status = G_RET_STS_ERROR THEN
821 RAISE G_EXCEPTION_ERROR;
822 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
823 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
824 END IF;
825 END IF;
826
827 -- public api to insert stream type
828 okl_prd_price_tmpls_pub.insert_prd_price_tmpls(p_api_version => p_api_version,
829 p_init_msg_list => p_init_msg_list,
830 x_return_status => l_return_status,
831 x_msg_count => x_msg_count,
832 x_msg_data => x_msg_data,
833 p_pitv_rec => l_pitv_rec,
834 x_pitv_rec => x_pitv_rec);
835
836 IF l_return_status = G_RET_STS_ERROR THEN
837 RAISE G_EXCEPTION_ERROR;
838 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
839 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
840 END IF;
841
842 -- copy output to input structure to get the id
843 l_pitv_rec := x_pitv_rec;
844
845 END IF;
846 *******************************************************************/
847 -- end, 06/13/2002 , mvasudev
848
849 x_return_status := l_return_status;
850 EXCEPTION
851 WHEN G_EXCEPTION_ERROR THEN
852 x_return_status := G_RET_STS_ERROR;
853 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
854 x_return_status := G_RET_STS_UNEXP_ERROR;
855 WHEN OTHERS THEN
856 -- store SQL error message on message stack for caller
857 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
858 p_msg_name => G_UNEXPECTED_ERROR,
859 p_token1 => G_SQLCODE_TOKEN,
860 p_token1_value => SQLCODE,
861 p_token2 => G_SQLERRM_TOKEN,
862 p_token2_value => SQLERRM );
863 -- notify caller of an UNEXPECTED error
864 x_return_status := G_RET_STS_UNEXP_ERROR;
865 END update_prd_price_tmpls;
866
867 PROCEDURE insert_prd_price_tmpls(
868 p_api_version IN NUMBER,
869 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
870 x_return_status OUT NOCOPY VARCHAR2,
871 x_msg_count OUT NOCOPY NUMBER,
872 x_msg_data OUT NOCOPY VARCHAR2,
873 p_pitv_tbl IN pitv_tbl_type,
874 x_pitv_tbl OUT NOCOPY pitv_tbl_type)
875 IS
876 l_api_name CONSTANT VARCHAR2(30) := 'insert_prd_price_tmpls_tbl';
877 rec_num INTEGER := 0;
878 BEGIN
879
880 FOR rec_num IN 1..p_pitv_tbl.COUNT
881 LOOP
882 insert_prd_price_tmpls(
883 p_api_version => p_api_version,
884 p_init_msg_list => p_init_msg_list,
885 x_return_status => x_return_status,
886 x_msg_count => x_msg_count,
887 x_msg_data => x_msg_data,
888 p_pitv_rec => p_pitv_tbl(rec_num),
889 x_pitv_rec => x_pitv_tbl(rec_num) );
890 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
891 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
892 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
893 RAISE G_EXCEPTION_ERROR;
894 END IF;
895 END LOOP;
896
897 EXCEPTION
898 WHEN G_EXCEPTION_ERROR THEN
899 x_return_status := G_RET_STS_ERROR;
900 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
901 x_return_status := G_RET_STS_UNEXP_ERROR;
902 WHEN OTHERS THEN
903 -- store SQL error message on message stack for caller
904 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
905 p_msg_name => G_UNEXPECTED_ERROR,
906 p_token1 => G_SQLCODE_TOKEN,
907 p_token1_value => SQLCODE,
908 p_token2 => G_SQLERRM_TOKEN,
909 p_token2_value => SQLERRM );
910 -- notify caller of an UNEXPECTED error
911 x_return_status := G_RET_STS_UNEXP_ERROR;
912 END insert_prd_price_tmpls;
913
914
915 PROCEDURE update_prd_price_tmpls(
916 p_api_version IN NUMBER,
917 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
918 x_return_status OUT NOCOPY VARCHAR2,
919 x_msg_count OUT NOCOPY NUMBER,
920 x_msg_data OUT NOCOPY VARCHAR2,
921 p_pitv_tbl IN pitv_tbl_type,
922 x_pitv_tbl OUT NOCOPY pitv_tbl_type)
923 IS
924 l_api_name CONSTANT VARCHAR2(30) := 'update_prd_price_tmpls_tbl';
925 rec_num INTEGER := 0;
926 BEGIN
927 FOR rec_num IN 1..p_pitv_tbl.COUNT
928 LOOP
929 update_prd_price_tmpls(
930 p_api_version => p_api_version,
931 p_init_msg_list => p_init_msg_list,
932 x_return_status => x_return_status,
933 x_msg_count => x_msg_count,
934 x_msg_data => x_msg_data,
935 p_pitv_rec => p_pitv_tbl(rec_num),
936 x_pitv_rec => x_pitv_tbl(rec_num) );
937 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
938 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
939 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
940 RAISE G_EXCEPTION_ERROR;
941 END IF;
942 END LOOP;
943 EXCEPTION
944 WHEN G_EXCEPTION_ERROR THEN
945 x_return_status := G_RET_STS_ERROR;
946 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
947 x_return_status := G_RET_STS_UNEXP_ERROR;
948 WHEN OTHERS THEN
949 -- store SQL error message on message stack for caller
950 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
951 p_msg_name => G_UNEXPECTED_ERROR,
952 p_token1 => G_SQLCODE_TOKEN,
953 p_token1_value => SQLCODE,
954 p_token2 => G_SQLERRM_TOKEN,
955 p_token2_value => SQLERRM );
956 -- notify caller of an UNEXPECTED error
957 x_return_status := G_RET_STS_UNEXP_ERROR;
958
959 END update_prd_price_tmpls;
960
961 PROCEDURE check_product_constraints(
962 p_api_version IN NUMBER,
963 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
964 x_return_status OUT NOCOPY VARCHAR2,
965 x_msg_count OUT NOCOPY NUMBER,
966 x_msg_data OUT NOCOPY VARCHAR2,
967 p_pdtv_rec IN pdtv_rec_type,
968 x_validated OUT NOCOPY VARCHAR2)
969 IS
970 CURSOR okl_pit_pdt_csr (p_pdt_id IN NUMBER) IS
971 SELECT
972 ID,
973 OBJECT_VERSION_NUMBER,
974 PDT_ID,
975 TEMPLATE_NAME,
976 TEMPLATE_PATH,
977 VERSION,
978 START_DATE,
979 NVL(END_DATE,G_MISS_DATE) END_DATE,
980 NVL(DESCRIPTION,G_MISS_CHAR) DESCRIPTION,
981 CREATED_BY,
982 CREATION_DATE,
983 LAST_UPDATED_BY,
984 LAST_UPDATE_DATE,
985 NVL(LAST_UPDATE_LOGIN,G_MISS_NUM) LAST_UPDATE_LOGIN
986 FROM OKL_PRD_PRICE_TMPLS
987 WHERE OKL_PRD_PRICE_TMPLS.pdt_id = p_pdt_id;
988
989 l_api_name CONSTANT VARCHAR2(30) := 'check_product_constraints';
990 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
991 l_valid BOOLEAN := TRUE;
992 l_pitv_rec pitv_rec_type;
993 BEGIN
994 l_return_status := G_RET_STS_SUCCESS;
995
996 FOR l_okl_pit_pdt_csr IN okl_pit_pdt_csr(p_pdtv_rec.id)
997 LOOP
998 l_pitv_rec.ID := l_okl_pit_pdt_csr.id;
999 l_pitv_rec.OBJECT_VERSION_NUMBER := l_okl_pit_pdt_csr.OBJECT_VERSION_NUMBER;
1000 l_pitv_rec.PDT_ID := l_okl_pit_pdt_csr.PDT_ID;
1001 l_pitv_rec.TEMPLATE_NAME := l_okl_pit_pdt_csr.TEMPLATE_NAME;
1002 l_pitv_rec.TEMPLATE_PATH := l_okl_pit_pdt_csr.TEMPLATE_PATH;
1003 l_pitv_rec.VERSION := l_okl_pit_pdt_csr.VERSION;
1004 l_pitv_rec.START_DATE := l_okl_pit_pdt_csr.START_DATE;
1005 l_pitv_rec.END_DATE := l_okl_pit_pdt_csr.END_DATE;
1006 l_pitv_rec.DESCRIPTION := l_okl_pit_pdt_csr.DESCRIPTION;
1007 l_pitv_rec.CREATED_BY := l_okl_pit_pdt_csr.CREATED_BY;
1008 l_pitv_rec.CREATION_DATE := l_okl_pit_pdt_csr.CREATION_DATE;
1009 l_pitv_rec.LAST_UPDATED_BY := l_okl_pit_pdt_csr.LAST_UPDATED_BY;
1010 l_pitv_rec.LAST_UPDATE_DATE := l_okl_pit_pdt_csr.LAST_UPDATE_DATE;
1011 l_pitv_rec.LAST_UPDATE_LOGIN := l_okl_pit_pdt_csr.LAST_UPDATE_LOGIN;
1012
1013 check_constraints (p_pitv_rec => l_pitv_rec,
1014 x_return_status => l_return_status,
1015 x_valid => l_valid);
1016 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1017 RAISE G_EXCEPTION_ERROR ;
1018 ELSIF (l_return_status = G_RET_STS_ERROR) OR
1019 (l_return_status = G_RET_STS_SUCCESS AND
1020 l_valid <> TRUE) THEN
1021 RAISE G_EXCEPTION_ERROR ;
1022 END IF;
1023 END LOOP;
1024
1025 x_validated := BOOLEAN_TO_CHAR(l_valid);
1026
1027 EXCEPTION
1028 WHEN G_EXCEPTION_ERROR THEN
1029 x_validated := G_FALSE;
1030 x_return_status := G_RET_STS_ERROR;
1031 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1032 x_validated := G_FALSE;
1033 x_return_status := G_RET_STS_UNEXP_ERROR;
1034 WHEN OTHERS THEN
1035 x_validated := G_FALSE;
1036 -- store SQL error message on message stack for caller
1037 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1038 p_msg_name => G_UNEXPECTED_ERROR,
1039 p_token1 => G_SQLCODE_TOKEN,
1040 p_token1_value => SQLCODE,
1041 p_token2 => G_SQLERRM_TOKEN,
1042 p_token2_value => SQLERRM );
1043 -- notify caller of an UNEXPECTED error
1044 x_return_status := G_RET_STS_UNEXP_ERROR;
1045
1046 END check_product_constraints;
1047
1048 PROCEDURE check_product_constraints(
1049 p_api_version IN NUMBER,
1050 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1051 x_return_status OUT NOCOPY VARCHAR2,
1052 x_msg_count OUT NOCOPY NUMBER,
1053 x_msg_data OUT NOCOPY VARCHAR2,
1054 p_pdtv_tbl IN pdtv_tbl_type,
1055 x_validated OUT NOCOPY VARCHAR2)
1056 IS
1057 l_api_name CONSTANT VARCHAR2(30) := 'check_product_constraints_tbl';
1058 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1059 l_validated VARCHAR2(1) := G_TRUE;
1060 l_valid BOOLEAN := TRUE;
1061 rec_num INTEGER := 0;
1062 BEGIN
1063 l_return_status := G_RET_STS_SUCCESS;
1064
1065 FOR rec_num IN 1..p_pdtv_tbl.COUNT
1066 LOOP
1067 check_product_constraints(
1068 p_api_version => p_api_version,
1069 p_init_msg_list => p_init_msg_list,
1070 x_return_status => x_return_status,
1071 x_msg_count => x_msg_count,
1072 x_msg_data => x_msg_data,
1073 p_pdtv_rec => p_pdtv_tbl(rec_num),
1074 x_validated => l_validated);
1075
1076 l_valid := FND_API.TO_BOOLEAN(l_validated);
1077 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1078 RAISE G_EXCEPTION_ERROR;
1079 ELSIF (l_return_status = G_RET_STS_ERROR) OR
1080 (l_return_status = G_RET_STS_SUCCESS AND
1081 l_valid <> TRUE) THEN
1082 RAISE G_EXCEPTION_ERROR;
1083 END IF;
1084 END LOOP;
1085
1086 x_validated := BOOLEAN_TO_CHAR(l_valid);
1087
1088 EXCEPTION
1089 WHEN G_EXCEPTION_ERROR THEN
1090 x_validated := G_FALSE;
1091 x_return_status := G_RET_STS_ERROR;
1092 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1093 x_validated := G_FALSE;
1094 x_return_status := G_RET_STS_UNEXP_ERROR;
1095 WHEN OTHERS THEN
1096 x_validated := G_FALSE;
1097 x_return_status := G_RET_STS_UNEXP_ERROR;
1098 -- store SQL error message on message stack for caller
1099 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1100 p_msg_name => G_UNEXPECTED_ERROR,
1101 p_token1 => G_SQLCODE_TOKEN,
1102 p_token1_value => SQLCODE,
1103 p_token2 => G_SQLERRM_TOKEN,
1104 p_token2_value => SQLERRM );
1105
1106 END check_product_constraints;
1107
1108 END OKL_SETUP_PRD_PRCTEMPL_PVT;