[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPPSTYPES_PVT
Source
1 PACKAGE BODY Okl_Setuppstypes_Pvt AS
2 /* $Header: OKLRSPSB.pls 115.7 2003/07/23 18:36:58 sgorantl noship $ */
3 G_ITEM_NOT_FOUND_ERROR EXCEPTION;
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_rec for: Okl_Prod_Strm_Types_V
6 ---------------------------------------------------------------------------
7 PROCEDURE get_rec (
8 p_psyv_rec IN psyv_rec_type,
9 x_no_data_found OUT NOCOPY BOOLEAN,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_psyv_rec OUT NOCOPY psyv_rec_type
12 ) IS
13 CURSOR okl_psyv_pk_csr (p_id IN NUMBER) IS
14 SELECT
15 ID,
16 OBJECT_VERSION_NUMBER,
17 STY_ID,
18 PDT_ID,
19 ACCRUAL_YN,
20 FROM_DATE,
21 TO_DATE,
22 CREATED_BY,
23 CREATION_DATE,
24 LAST_UPDATED_BY,
25 LAST_UPDATE_DATE,
26 LAST_UPDATE_LOGIN
27 FROM Okl_Prod_Strm_Types_V
28 WHERE Okl_Prod_Strm_Types_V.id = p_id;
29 l_okl_psyv_pk okl_psyv_pk_csr%ROWTYPE;
30 l_psyv_rec psyv_rec_type;
31 BEGIN
32 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
33 x_no_data_found := TRUE;
34 -- Get current database values
35 OPEN okl_psyv_pk_csr (p_psyv_rec.id);
36 FETCH okl_psyv_pk_csr INTO
37 l_psyv_rec.ID,
38 l_psyv_rec.OBJECT_VERSION_NUMBER,
39 l_psyv_rec.STY_ID,
40 l_psyv_rec.PDT_ID,
41 l_psyv_rec.ACCRUAL_YN,
42 l_psyv_rec.FROM_DATE,
43 l_psyv_rec.TO_DATE,
44 l_psyv_rec.CREATED_BY,
45 l_psyv_rec.CREATION_DATE,
46 l_psyv_rec.LAST_UPDATED_BY,
47 l_psyv_rec.LAST_UPDATE_DATE,
48 l_psyv_rec.LAST_UPDATE_LOGIN;
49 x_no_data_found := okl_psyv_pk_csr%NOTFOUND;
50 CLOSE okl_psyv_pk_csr;
51 x_psyv_rec := l_psyv_rec;
52 EXCEPTION
53 WHEN OTHERS THEN
54 -- store SQL error message on message stack
55 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
56 p_msg_name => G_UNEXPECTED_ERROR,
57 p_token1 => G_SQLCODE_TOKEN,
58 p_token1_value => SQLCODE,
59 p_token2 => G_SQLERRM_TOKEN,
60 p_token2_value => SQLERRM);
61 -- notify UNEXPECTED error for calling API.
62 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
63
64 IF (okl_psyv_pk_csr%ISOPEN) THEN
65 CLOSE okl_psyv_pk_csr;
66 END IF;
67
68 END get_rec;
69
70 ---------------------------------------------------------------------------
71 -- PROCEDURE get_parent_dates for: Okl_Prod_Strm_Types_V
72 ---------------------------------------------------------------------------
73
74 PROCEDURE get_parent_dates(
75 p_psyv_rec IN psyv_rec_type,
76 x_no_data_found OUT NOCOPY BOOLEAN,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_pdtv_rec OUT NOCOPY pdtv_rec_type
79 ) IS
80 CURSOR okl_pdt_pk_csr (p_pdt_id IN NUMBER) IS
81 SELECT FROM_DATE,
82 TO_DATE
83 FROM Okl_products_V pdtv
84 WHERE pdtv.id = p_pdt_id;
85 l_okl_pdtv_pk okl_pdt_pk_csr%ROWTYPE;
86 l_pdtv_rec pdtv_rec_type;
87 BEGIN
88 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
89 x_no_data_found := TRUE;
90 -- Get current database values
91 OPEN okl_pdt_pk_csr (p_psyv_rec.pdt_id);
92 FETCH okl_pdt_pk_csr INTO
93 l_pdtv_rec.FROM_DATE,
94 l_pdtv_rec.TO_DATE;
95 x_no_data_found := okl_pdt_pk_csr%NOTFOUND;
96 CLOSE okl_pdt_pk_csr;
97 x_pdtv_rec := l_pdtv_rec;
98 EXCEPTION
99 WHEN OTHERS THEN
100 -- store SQL error message on message stack
101 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
102 p_msg_name => G_UNEXPECTED_ERROR,
103 p_token1 => G_SQLCODE_TOKEN,
104 p_token1_value => SQLCODE,
105 p_token2 => G_SQLERRM_TOKEN,
106 p_token2_value => SQLERRM);
107 -- notify UNEXPECTED error for calling API.
108 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
109
110
111 IF (okl_pdt_pk_csr%ISOPEN) THEN
112 CLOSE okl_pdt_pk_csr;
113
114 END IF;
115
116 END get_parent_dates;
117
118
119 -----------------------------------------------------------------------------
120 -- PROCEDURE check_constraints for: Okl_Prod_Strm_Types_V
121 -----------------------------------------------------------------------------
122
123 PROCEDURE Check_Constraints (
124 p_api_version IN NUMBER,
125 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
126 p_psyv_rec IN psyv_rec_type,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2,
130 x_valid OUT NOCOPY BOOLEAN
131 ) IS
132 CURSOR okl_psyv_chk_upd(p_pdt_id NUMBER
133 ) IS
134 SELECT '1' FROM okl_k_headers_v khdr
135 WHERE khdr.pdt_id = p_pdt_id;
136
137 CURSOR okl_psy_pdt_fk_csr (p_pdt_id IN Okl_Products_V.ID%TYPE,
138 p_date IN Okl_Products_V.TO_DATE%TYPE
139 ) IS
140 SELECT '1'
141 FROM Okl_products_V pdt
142 WHERE pdt.ID = p_pdt_id
143 AND NVL(pdt.TO_DATE, p_date) < p_date;
144
145 CURSOR okl_psy_constraints_csr(p_sty_id IN Okl_Prod_Strm_Types_V.STY_ID%TYPE,
146 p_from_date IN Okl_Prod_Strm_Types_V.FROM_DATE%TYPE,
147 p_to_date IN Okl_Prod_Strm_Types_V.TO_DATE%TYPE
148 ) IS
149 SELECT '1'
150 FROM Okl_Strm_Type_V sty
151 WHERE sty.ID = p_sty_id
152 AND ((sty.START_DATE > p_from_date OR
153 p_from_date > NVL(sty.END_DATE,p_from_date)) OR
154 NVL(sty.END_DATE, p_to_date) < p_to_date);
155
156 CURSOR c1(p_pdt_id okl_prod_strm_types_v.pdt_id%TYPE,
157 p_sty_id okl_prod_strm_types_v.sty_id%TYPE) IS
158 SELECT '1'
159 FROM okl_prod_strm_types_v
160 WHERE pdt_id = p_pdt_id
161 AND sty_id = p_sty_id
162 AND id <> NVL(p_psyv_rec.id,-9999);
163
164 l_check VARCHAR2(1) := '?';
165 l_row_not_found BOOLEAN := FALSE;
166 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
167 l_unq_tbl Okc_Util.unq_tbl_type;
168 l_psy_status VARCHAR2(1);
169 l_row_found BOOLEAN := FALSE;
170 l_token_1 VARCHAR2(1999);
171
172 BEGIN
173 x_valid := TRUE;
174 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
175
176 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDTPSY_SERCH',
177 p_attribute_code => 'OKL_PRODUCT_STREAM_TYPES');
178
179 -- check for uniquness
180 IF p_psyv_rec.id = Okl_Api.G_MISS_NUM THEN
181 OPEN c1(p_psyv_rec.pdt_id,
182 p_psyv_rec.sty_id);
183 FETCH c1 INTO l_psy_status;
184 l_row_found := c1%FOUND;
185 CLOSE c1;
186 IF l_row_found THEN
187 Okl_Api.set_message('OKL',Okl_Psy_Pvt.G_UNQS,Okl_Psy_Pvt.G_TABLE_TOKEN, l_token_1); ---CHG001
188 x_valid := FALSE;
189 x_return_status := Okl_Api.G_RET_STS_ERROR;
190 RAISE G_EXCEPTION_HALT_PROCESSING;
191 END IF;
192 END IF;
193
194 -- Check for psyv valid dates
195 /* OPEN okl_psyv_chk_upd(p_psyv_rec.pdt_id);
196
197 FETCH okl_psyv_chk_upd INTO l_check;
198 l_row_not_found := okl_psyv_chk_upd%NOTFOUND;
199 CLOSE okl_psyv_chk_upd;
200
201 IF l_row_not_found = FALSE THEN
202 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
203 p_msg_name => G_IN_USE,
204 p_token1 => G_PARENT_TABLE_TOKEN,
205 p_token1_value => 'Okl_Prod_Strm_Types_V',
206 p_token2 => G_CHILD_TABLE_TOKEN,
207 p_token2_value => 'okl_k_headers_v');
208 x_valid := FALSE;
209 x_return_status := Okl_Api.G_RET_STS_ERROR;
210 RAISE G_EXCEPTION_HALT_PROCESSING;
211 END IF;
212 */
213 -- Check if the product to which the product stream types are attached is not
214 -- in the past
215 /*OPEN okl_psy_pdt_fk_csr (p_psyv_rec.pdt_id,
216 l_sysdate);
217 FETCH okl_psy_pdt_fk_csr INTO l_check;
218 l_row_not_found := okl_psy_pdt_fk_csr%NOTFOUND;
219 CLOSE okl_psy_pdt_fk_csr;
220
221 IF l_row_not_found = FALSE THEN
222 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
223 p_msg_name => G_PAST_RECORDS);
224 x_valid := FALSE;
225 x_return_status := Okl_Api.G_RET_STS_ERROR;
226 RAISE G_EXCEPTION_HALT_PROCESSING;
227 END IF;*/
228
229 /*-- Check for constraints dates
230 OPEN okl_psy_constraints_csr(p_psyv_rec.sty_id,
231 p_psyv_rec.from_date,
232 p_psyv_rec.TO_DATE);
233 FETCH okl_psy_constraints_csr INTO l_check;
234 l_row_not_found := okl_psy_constraints_csr%NOTFOUND;
235 CLOSE okl_psy_constraints_csr;
236
237 IF l_row_not_found = FALSE THEN
238 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
239 p_msg_name => G_DATES_MISMATCH,
240 p_token1 => G_PARENT_TABLE_TOKEN,
241 p_token1_value => 'Okl_Strm_Type_V',
242 p_token2 => G_CHILD_TABLE_TOKEN,
243 p_token2_value => 'Okl_Prod_Strm_Types_V,Okl_Products_V');
244 x_valid := FALSE;
245 x_return_status := OKL_API.G_RET_STS_ERROR;
246 RAISE G_EXCEPTION_HALT_PROCESSING;
247 END IF;*/
248
249 EXCEPTION
250 WHEN G_EXCEPTION_HALT_PROCESSING THEN
251 -- no processing necessary; validation can continue
252 -- with the next column
253 NULL;
254 WHEN OTHERS THEN
255 -- store SQL error message on message stack
256 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
257 p_msg_name => G_UNEXPECTED_ERROR,
258 p_token1 => G_SQLCODE_TOKEN,
259 p_token1_value => SQLCODE,
260 p_token2 => G_SQLERRM_TOKEN,
261 p_token2_value => SQLERRM);
262 x_valid := FALSE;
263 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
264
265 IF (okl_psyv_chk_upd%ISOPEN) THEN
266 CLOSE okl_psyv_chk_upd;
267 END IF;
268
269 IF (okl_psy_pdt_fk_csr%ISOPEN) THEN
270 CLOSE okl_psy_pdt_fk_csr;
271 END IF;
272
273 IF (okl_psy_constraints_csr%ISOPEN) THEN
274 CLOSE okl_psy_constraints_csr;
275 END IF;
276
277 IF (C1%ISOPEN) THEN
278 CLOSE C1;
279 END IF;
280
281 END Check_Constraints;
282
283 -------------------------------------
284 -- Validate_Attributes for: STY_ID --
285 -------------------------------------
286 PROCEDURE validate_sty_id(x_return_status OUT NOCOPY VARCHAR2,
287 p_sty_id IN NUMBER)
288 IS
289 CURSOR okl_styv_pk_csr (p_id IN NUMBER) IS
290 SELECT '1'
291 FROM okl_strm_type_v
292 WHERE okl_strm_type_v.id = p_id;
293
294 l_sty_status VARCHAR2(1);
295 l_row_notfound BOOLEAN := TRUE;
296 l_token_1 VARCHAR2(1999);
297
298 BEGIN
299 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
300
301 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDTPSY_CRUPD',
302 p_attribute_code => 'OKL_NAME');
303
304 IF (p_sty_id = Okl_Api.G_MISS_NUM OR
305 p_sty_id IS NULL)
306 THEN
307 Okl_Api.set_message(Okl_Psy_Pvt.G_APP_NAME, Okl_Psy_Pvt.G_REQUIRED_VALUE, Okl_Psy_Pvt.G_COL_NAME_TOKEN, l_token_1);
308 x_return_status := Okl_Api.G_RET_STS_ERROR;
309 RAISE G_EXCEPTION_HALT_PROCESSING;
310 END IF;
311
312 IF (p_sty_id IS NOT NULL)
313 THEN
314 OPEN okl_styv_pk_csr(p_sty_id);
315 FETCH okl_styv_pk_csr INTO l_sty_status;
316 l_row_notfound := okl_styv_pk_csr%NOTFOUND;
317 CLOSE okl_styv_pk_csr;
318 IF (l_row_notfound) THEN
319 Okl_Api.set_message(Okl_Psy_Pvt.G_APP_NAME, Okl_Psy_Pvt.G_INVALID_VALUE,Okl_Psy_Pvt.G_COL_NAME_TOKEN,l_token_1);
320 RAISE G_ITEM_NOT_FOUND_ERROR;
321 END IF;
322 END IF;
323
324 EXCEPTION
325 WHEN G_EXCEPTION_HALT_PROCESSING THEN
326 NULL;
327 WHEN OTHERS THEN
328 Okc_Api.SET_MESSAGE( p_app_name => Okl_Psy_Pvt.G_APP_NAME
329 ,p_msg_name => Okl_Psy_Pvt.G_UNEXPECTED_ERROR
330 ,p_token1 => Okl_Psy_Pvt.G_SQLCODE_TOKEN
331 ,p_token1_value => SQLCODE
332 ,p_token2 => Okl_Psy_Pvt.G_SQLERRM_TOKEN
333 ,p_token2_value => SQLERRM);
334 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
335 END validate_sty_id;
336
337 ---------------------------------------------------------------------------
338 -- FUNCTION Validate_Attributes
339 ---------------------------------------------------------------------------
340 ---------------------------------------------------
341 -- Validate_Attributes for:OKL_PROD_STRM_TYPES_V --
342 ---------------------------------------------------
343 FUNCTION Validate_Attributes (
344 p_psyv_rec IN psyv_rec_type
345 ) RETURN VARCHAR2 IS
346 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
347 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
348 BEGIN
349 -----------------------------
350 -- Column Level Validation --
351 -----------------------------
352 -- ***
353 -- sty_id
354 -- ***
355 validate_sty_id(x_return_status, p_psyv_rec.sty_id);
356 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
357 l_return_status := x_return_status;
358 RAISE G_EXCEPTION_HALT_PROCESSING;
359 END IF;
360
361 RETURN(l_return_status);
362 EXCEPTION
363 WHEN G_EXCEPTION_HALT_PROCESSING THEN
364 RETURN(l_return_status);
365 WHEN OTHERS THEN
366 Okl_Api.SET_MESSAGE( p_app_name => Okl_Psy_Pvt.G_APP_NAME
367 ,p_msg_name => Okl_Psy_Pvt.G_UNEXPECTED_ERROR
368 ,p_token1 => Okl_Psy_Pvt.G_SQLCODE_TOKEN
369 ,p_token1_value => SQLCODE
370 ,p_token2 => Okl_Psy_Pvt.G_SQLERRM_TOKEN
371 ,p_token2_value => SQLERRM);
372 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
373 RETURN(l_return_status);
374 END Validate_Attributes;
375
376 ---------------------------------------------------------------------------
377 -- PROCEDURE insert_pstypes for: Okl_Prod_Strm_Types_V
378 ---------------------------------------------------------------------------
379 PROCEDURE insert_pstypes(
380 p_api_version IN NUMBER,
381 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
382 x_return_status OUT NOCOPY VARCHAR2,
383 x_msg_count OUT NOCOPY NUMBER,
384 x_msg_data OUT NOCOPY VARCHAR2,
385 p_pdtv_rec IN pdtv_rec_type,
386 p_psyv_rec IN psyv_rec_type,
387 x_psyv_rec OUT NOCOPY psyv_rec_type
388 ) IS
389 l_api_version CONSTANT NUMBER := 1;
390 l_api_name CONSTANT VARCHAR2(30) := 'insert_pstypes';
391 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
392 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
393 l_valid BOOLEAN;
394 l_psyv_rec psyv_rec_type;
395 l_pdtv_rec pdtv_rec_type;
396 l_row_notfound BOOLEAN := TRUE;
397 BEGIN
398 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
399
400 l_psyv_rec := p_psyv_rec;
401
402 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
403 p_pkg_name => G_PKG_NAME,
404 p_init_msg_list => p_init_msg_list,
405 l_api_version => l_api_version,
406 p_api_version => p_api_version,
407 p_api_type => '_PVT',
408 x_return_status => l_return_status);
409
410 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
411 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
412 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
413 RAISE Okl_Api.G_EXCEPTION_ERROR;
414 END IF;
415
416 l_return_status := Validate_Attributes(l_psyv_rec);
417 --- If any errors happen abort API
418 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
419 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
420 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
421 RAISE Okl_Api.G_EXCEPTION_ERROR;
422 END IF;
423
424 get_parent_dates(p_psyv_rec => l_psyv_rec,
425 x_no_data_found => l_row_notfound,
426 x_return_status => l_return_status,
427 x_pdtv_rec => l_pdtv_rec);
428
429
430 IF (l_row_notfound) THEN
431 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
432 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
433 RAISE Okl_Api.G_EXCEPTION_ERROR;
434 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
435 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
436 END IF;
437
438 --assign parent dates.
439
440 l_psyv_rec.from_date := l_pdtv_rec.from_date;
441 l_psyv_rec.TO_DATE := l_pdtv_rec.TO_DATE;
442
443 /* call check_constraints to check the validity of this relationship */
444
445 Check_Constraints(p_api_version => p_api_version,
446 p_init_msg_list => p_init_msg_list,
447 p_psyv_rec => l_psyv_rec,
448 x_return_status => l_return_status,
449 x_msg_count => x_msg_count,
450 x_msg_data => x_msg_data,
451 x_valid => l_valid);
452
453 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
454 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
455 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
456 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
457 l_valid <> TRUE) THEN
458 x_return_status := Okl_Api.G_RET_STS_ERROR;
459 RAISE Okl_Api.G_EXCEPTION_ERROR;
460 END IF;
461
462 /* public api to insert pstypes */
463
464 Okl_Pdt_Stys_Pub.insert_pdt_stys(p_api_version => p_api_version,
465 p_init_msg_list => p_init_msg_list,
466 x_return_status => l_return_status,
467 x_msg_count => x_msg_count,
468 x_msg_data => x_msg_data,
469 p_psyv_rec => l_psyv_rec,
470 x_psyv_rec => x_psyv_rec);
471
472 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
473 RAISE Okl_Api.G_EXCEPTION_ERROR;
474 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
475 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
476 END IF;
477
478 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
479 x_msg_data => x_msg_data);
480 EXCEPTION
481 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
482 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
483 p_pkg_name => G_PKG_NAME,
484 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data,
487 p_api_type => '_PVT');
488 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
489 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
490 p_pkg_name => G_PKG_NAME,
491 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
492 x_msg_count => x_msg_count,
493 x_msg_data => x_msg_data,
494 p_api_type => '_PVT');
495 WHEN OTHERS THEN
496 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
497 p_pkg_name => G_PKG_NAME,
498 p_exc_name => 'OTHERS',
499 x_msg_count => x_msg_count,
500 x_msg_data => x_msg_data,
501 p_api_type => '_PVT');
502
503 END insert_pstypes;
504
505 ---------------------------------------------------------------------------
506 -- PROCEDURE update_pstypes for: Okl_Prod_Strm_Types_V
507 ---------------------------------------------------------------------------
508 PROCEDURE update_pstypes(
509 p_api_version IN NUMBER,
510 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
511 x_return_status OUT NOCOPY VARCHAR2,
512 x_msg_count OUT NOCOPY NUMBER,
513 x_msg_data OUT NOCOPY VARCHAR2,
514 p_pdtv_rec IN pdtv_rec_type,
515 p_psyv_rec IN psyv_rec_type,
516 x_psyv_rec OUT NOCOPY psyv_rec_type
517 ) IS
518 l_api_version CONSTANT NUMBER := 1;
519 l_api_name CONSTANT VARCHAR2(30) := 'update_pstypes';
520 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
521 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
522 l_valid BOOLEAN;
523 l_psyv_rec psyv_rec_type;
524 l_pdtv_rec pdtv_rec_type;
525 l_row_notfound BOOLEAN := TRUE;
526 BEGIN
527 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
528
529 l_psyv_rec := p_psyv_rec;
530
531 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
532 p_pkg_name => G_PKG_NAME,
533 p_init_msg_list => p_init_msg_list,
534 l_api_version => l_api_version,
535 p_api_version => p_api_version,
536 p_api_type => '_PVT',
537 x_return_status => l_return_status);
538
539 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
540 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
541 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
542 RAISE Okl_Api.G_EXCEPTION_ERROR;
543 END IF;
544
545 get_parent_dates(p_psyv_rec => l_psyv_rec,
546 x_no_data_found => l_row_notfound,
547 x_return_status => l_return_status,
548 x_pdtv_rec => l_pdtv_rec);
549
550 IF (l_row_notfound) THEN
551 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
552 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
553 RAISE Okl_Api.G_EXCEPTION_ERROR;
554 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
555 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
556 END IF;
557
558 --assign parent dates.
559
560 l_psyv_rec.from_date := l_pdtv_rec.from_date;
561 l_psyv_rec.TO_DATE := l_pdtv_rec.TO_DATE;
562
563
564 /* call check_constraints to check the validity of this relationship */
565
566 Check_Constraints(p_api_version => p_api_version,
567 p_init_msg_list => p_init_msg_list,
568 p_psyv_rec => l_psyv_rec,
569 x_return_status => l_return_status,
570 x_msg_count => x_msg_count,
571 x_msg_data => x_msg_data,
572 x_valid => l_valid);
573
574 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
575 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
576 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
577 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
578 l_valid <> TRUE) THEN
579 x_return_status := Okl_Api.G_RET_STS_ERROR;
580 RAISE Okl_Api.G_EXCEPTION_ERROR;
581 END IF;
582
583 /* public api to update pstypes */
584
585 Okl_Pdt_Stys_Pub.update_pdt_stys(p_api_version => p_api_version,
586 p_init_msg_list => p_init_msg_list,
587 x_return_status => l_return_status,
588 x_msg_count => x_msg_count,
589 x_msg_data => x_msg_data,
590 p_psyv_rec => l_psyv_rec,
591 x_psyv_rec => x_psyv_rec);
592
593 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
594 RAISE Okl_Api.G_EXCEPTION_ERROR;
595 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
596 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
597 END IF;
598
599 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
600 x_msg_data => x_msg_data);
601 EXCEPTION
602 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
603 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
604 p_pkg_name => G_PKG_NAME,
605 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
606 x_msg_count => x_msg_count,
607 x_msg_data => x_msg_data,
608 p_api_type => '_PVT');
609 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
610 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
611 p_pkg_name => G_PKG_NAME,
612 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
613 x_msg_count => x_msg_count,
614 x_msg_data => x_msg_data,
615 p_api_type => '_PVT');
616 WHEN OTHERS THEN
617 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
618 p_pkg_name => G_PKG_NAME,
619 p_exc_name => 'OTHERS',
620 x_msg_count => x_msg_count,
621 x_msg_data => x_msg_data,
622 p_api_type => '_PVT');
623
624 END update_pstypes;
625
626 END Okl_Setuppstypes_Pvt;