[Home] [Help]
PACKAGE BODY: APPS.OKC_LINE_STYLES_PVT
Source
1 PACKAGE BODY OKC_LINE_STYLES_PVT AS
2 /* $Header: OKCCLSEB.pls 120.0 2005/05/25 19:34:48 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 G_YES VARCHAR2(3):='Y';
7 G_NO VARCHAR2(3):='N';
8 G_UPD_IN_K VARCHAR2(30):='OKC_UPD_IN_K_LINES';
9 G_UPD_IN_S VARCHAR2(30):='OKC_UPD_IN_SETUPS';
10 G_DEL_IN_K VARCHAR2(30):='OKC_DEL_IN_K_LINES';
11 G_DEL_IN_S VARCHAR2(30):='OKC_DEL_IN_SETUPS';
12
13
14 PROCEDURE add_language IS
15 BEGIN
16 OKC_LSE_PVT.add_language;
17 END add_language;
18
19 --------------------------------------------------------------------------
20 ---The following procedures cater to handling of OKC_LINE_STYLES
21 --------------------------------------------------------------------------
22
23 PROCEDURE CHANGE_PRICED_FOR_CHILDREN(
24 p_api_version IN NUMBER,
25 p_init_msg_list IN VARCHAR2 ,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2,
29 p_lsev_rec IN lsev_rec_type) IS
30
31 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
32 l_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
33 lx_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
34
35 CURSOR loc_bot_csr (p_id IN NUMBER) is
36 select id, priced_yn from okc_line_styles_b
37 Connect by prior id = lse_parent_id
38 start with id = p_id;
39
40 BEGIN
41 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
42 for l_rec in loc_bot_csr(p_lsev_rec.id) loop
43 IF l_rec.priced_yn=G_Yes and l_rec.id<>p_lsev_rec.id then
44 l_lsev_rec.id:=l_rec.id;
45 l_lsev_rec.priced_yn:=G_NO;
46 OKC_LINE_STYLES_PUB.UPDATE_LINE_STYLES(
47 p_api_version,
48 p_init_msg_list,
49 x_return_status,
50 x_msg_count,
51 x_msg_data,
52 l_lsev_rec,
53 lx_lsev_rec);
54 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
55 END IF;
56 END LOOP;
57
58 EXCEPTION
59
60 WHEN OTHERS THEN
61 OKC_API.set_message(p_app_name => g_app_name,
62 p_msg_name => g_unexpected_error,
63 p_token1 => g_sqlcode_token,
64 p_token1_value => sqlcode,
65 p_token2 => g_sqlerrm_token,
66 p_token2_value => sqlerrm);
67 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
68
69 END CHANGE_PRICED_FOR_CHILDREN;
70
71
72
73 PROCEDURE CHANGE_PRICED_FOR_PARENT(
74 p_api_version IN NUMBER,
75 p_init_msg_list IN VARCHAR2 ,
76 x_return_status OUT NOCOPY VARCHAR2,
77 x_msg_count OUT NOCOPY NUMBER,
78 x_msg_data OUT NOCOPY VARCHAR2,
79 p_lsev_rec IN lsev_rec_type) IS
80
81 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
82 l_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
83 lx_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
84 CURSOR loc_top_csr (p_id IN NUMBER) is
85 select id, priced_yn from okc_line_styles_b
86 connect by prior lse_parent_id = id
87 start with id = p_id;
88
89 BEGIN
90 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
91 for l_rec in loc_top_csr(p_lsev_rec.id) loop
92 IF l_rec.priced_yn=G_Yes and l_rec.id<>p_lsev_rec.id then
93 l_lsev_rec.id:=l_rec.id;
94 l_lsev_rec.priced_yn:=G_NO;
95 OKC_LINE_STYLES_PUB.UPDATE_LINE_STYLES(
96 p_api_version,
97 p_init_msg_list,
98 x_return_status,
99 x_msg_count,
100 x_msg_data,
101 l_lsev_rec,
102 lx_lsev_rec);
103 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
104 END IF;
105 END LOOP;
106
107 EXCEPTION
108
109 WHEN OTHERS THEN
110 OKC_API.set_message(p_app_name => g_app_name,
111 p_msg_name => g_unexpected_error,
112 p_token1 => g_sqlcode_token,
113 p_token1_value => sqlcode,
114 p_token2 => g_sqlerrm_token,
115 p_token2_value => sqlerrm);
116 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
117
118 END CHANGE_PRICED_FOR_PARENT;
119
120
121
122
123 PROCEDURE CHANGE_ITEM_TO_PRICE_CHILDREN(
124 p_api_version IN NUMBER,
125 p_init_msg_list IN VARCHAR2 ,
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2,
129 p_lsev_rec IN lsev_rec_type) IS
130
131 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
132 l_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
133 lx_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
134
135 CURSOR loc_bot_ip_csr (p_id IN NUMBER) is
136 select id, item_to_price_yn from okc_line_styles_b
137 Connect by prior id = lse_parent_id
138 start with id = p_id;
139
140 BEGIN
141 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
142 for l_rec in loc_bot_ip_csr(p_lsev_rec.id) loop
143 IF l_rec.item_to_price_yn=G_Yes and l_rec.id<>p_lsev_rec.id then
144 l_lsev_rec.id:=l_rec.id;
145 l_lsev_rec.item_to_price_yn:=G_NO;
146 OKC_LINE_STYLES_PUB.UPDATE_LINE_STYLES(
147 p_api_version,
148 p_init_msg_list,
149 x_return_status,
150 x_msg_count,
151 x_msg_data,
152 l_lsev_rec,
153 lx_lsev_rec);
154 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
155 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
156 END IF;
157 END LOOP;
158
159 EXCEPTION
160
161 WHEN OTHERS THEN
162 OKC_API.set_message(p_app_name => g_app_name,
163 p_msg_name => g_unexpected_error,
164 p_token1 => g_sqlcode_token,
165 p_token1_value => sqlcode,
166 p_token2 => g_sqlerrm_token,
167 p_token2_value => sqlerrm);
168 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
169
170 END CHANGE_ITEM_TO_PRICE_CHILDREN;
171
172
173
174 PROCEDURE CHANGE_ITEM_TO_PRICE_PARENT(
175 p_api_version IN NUMBER,
176 p_init_msg_list IN VARCHAR2 ,
177 x_return_status OUT NOCOPY VARCHAR2,
178 x_msg_count OUT NOCOPY NUMBER,
179 x_msg_data OUT NOCOPY VARCHAR2,
180 p_lsev_rec IN lsev_rec_type) IS
181
182 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
183 l_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
184 lx_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
185 CURSOR loc_top_ip_csr (p_id IN NUMBER) is
186 select id, item_to_price_yn from okc_line_styles_b
187 connect by prior lse_parent_id = id
188 start with id = p_id;
189
190 BEGIN
191 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
192 for l_rec in loc_top_ip_csr(p_lsev_rec.id) loop
193 IF l_rec.item_to_price_yn=G_Yes and l_rec.id<>p_lsev_rec.id then
194 l_lsev_rec.id:=l_rec.id;
195 l_lsev_rec.item_to_price_yn:=G_NO;
196 OKC_LINE_STYLES_PUB.UPDATE_LINE_STYLES(
197 p_api_version,
198 p_init_msg_list,
199 x_return_status,
200 x_msg_count,
201 x_msg_data,
202 l_lsev_rec,
203 lx_lsev_rec);
204 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
205 END IF;
206 END LOOP;
207
208 EXCEPTION
209
210 WHEN OTHERS THEN
211 OKC_API.set_message(p_app_name => g_app_name,
212 p_msg_name => g_unexpected_error,
213 p_token1 => g_sqlcode_token,
214 p_token1_value => sqlcode,
215 p_token2 => g_sqlerrm_token,
216 p_token2_value => sqlerrm);
217 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
218
219 END CHANGE_ITEM_TO_PRICE_PARENT;
220
221
222 PROCEDURE CHANGE_PRICE_BASIS_CHILDREN(
223 p_api_version IN NUMBER,
224 p_init_msg_list IN VARCHAR2 ,
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER,
227 x_msg_data OUT NOCOPY VARCHAR2,
228 p_lsev_rec IN lsev_rec_type) IS
229
230 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
231 l_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
232 lx_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
233
234 CURSOR loc_bot_pb_csr (p_id IN NUMBER) is
235 select id, price_basis_yn from okc_line_styles_b
236 Connect by prior id = lse_parent_id
237 start with id = p_id;
238
239 BEGIN
240 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
241 for l_rec in loc_bot_pb_csr(p_lsev_rec.id) loop
242 IF l_rec.price_basis_yn = G_Yes and l_rec.id<>p_lsev_rec.id then
243 l_lsev_rec.id:=l_rec.id;
244 l_lsev_rec.price_basis_yn:=G_NO;
245 OKC_LINE_STYLES_PUB.UPDATE_LINE_STYLES(
246 p_api_version,
247 p_init_msg_list,
248 x_return_status,
249 x_msg_count,
250 x_msg_data,
251 l_lsev_rec,
252 lx_lsev_rec);
253 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
254 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
255 END IF;
256 END LOOP;
257
258 EXCEPTION
259
260 WHEN OTHERS THEN
261 OKC_API.set_message(p_app_name => g_app_name,
262 p_msg_name => g_unexpected_error,
263 p_token1 => g_sqlcode_token,
264 p_token1_value => sqlcode,
265 p_token2 => g_sqlerrm_token,
266 p_token2_value => sqlerrm);
267 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
268
269 END CHANGE_PRICE_BASIS_CHILDREN;
270
271
272
273 PROCEDURE CHANGE_PRICE_BASIS_PARENT(
274 p_api_version IN NUMBER,
275 p_init_msg_list IN VARCHAR2 ,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2,
279 p_lsev_rec IN lsev_rec_type) IS
280
281 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
282 l_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
283 lx_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
284 CURSOR loc_top_pb_csr (p_id IN NUMBER) is
285 select id, price_basis_yn from okc_line_styles_b
286 connect by prior lse_parent_id = id
287 start with id = p_id;
288
289 BEGIN
290 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
291 for l_rec in loc_top_pb_csr(p_lsev_rec.id) loop
292 IF l_rec.price_basis_yn = G_Yes and l_rec.id<>p_lsev_rec.id then
293 l_lsev_rec.id:=l_rec.id;
294 l_lsev_rec.price_basis_yn :=G_NO;
295 OKC_LINE_STYLES_PUB.UPDATE_LINE_STYLES(
296 p_api_version,
297 p_init_msg_list,
298 x_return_status,
299 x_msg_count,
300 x_msg_data,
301 l_lsev_rec,
302 lx_lsev_rec);
303 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
304 END IF;
305 END LOOP;
306
307 EXCEPTION
308
309 WHEN OTHERS THEN
310 OKC_API.set_message(p_app_name => g_app_name,
311 p_msg_name => g_unexpected_error,
312 p_token1 => g_sqlcode_token,
313 p_token1_value => sqlcode,
314 p_token2 => g_sqlerrm_token,
315 p_token2_value => sqlerrm);
316 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
317
318 END CHANGE_PRICE_BASIS_PARENT;
319
320
321
322
323 FUNCTION USED_IN_K_LINES(p_id IN number)
324 RETURN VARCHAR2 IS
325
326 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
327
328 CURSOR c1 (p_id IN NUMBER) is
329 select OKC_API.G_RET_STS_ERROR from okc_k_lines_b where lse_id=p_id;
330
331 BEGIN
332 Open c1(p_id);
333 fetch c1 into l_return_status;
334 close c1;
335 return l_return_status;
336
337 EXCEPTION
338
339 WHEN OTHERS THEN
340 OKC_API.set_message(p_app_name => g_app_name,
341 p_msg_name => g_unexpected_error,
342 p_token1 => g_sqlcode_token,
343 p_token1_value => sqlcode,
344 p_token2 => g_sqlerrm_token,
345 p_token2_value => sqlerrm);
346 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
347
348 END USED_IN_K_LINES;
349
350 FUNCTION USED_IN_SETUPS(p_id IN number)
351 RETURN VARCHAR2 IS
352 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
353 l_excp_error EXCEPTION;
354 CURSOR c3 (p_id IN NUMBER) is
355 select OKC_API.G_RET_STS_ERROR from okc_line_style_roles where lse_id=p_id;
356 CURSOR c4 (p_id IN NUMBER) is
357 select OKC_API.G_RET_STS_ERROR from okc_lse_rule_groups where lse_id=p_id;
358 CURSOR c5 (p_id IN NUMBER) is
359 select OKC_API.G_RET_STS_ERROR from okc_subclass_top_line where lse_id=p_id;
360
361 BEGIN
362 Open c3(p_id);
363 fetch c3 into l_return_status;
364 close c3;
365 IF l_return_status=OKC_API.G_RET_STS_ERROR then
366 raise l_excp_error;
367 END IF;
368 Open c4(p_id);
369 fetch c4 into l_return_status;
370 close c4;
371 IF l_return_status=OKC_API.G_RET_STS_ERROR then
372 raise l_excp_error;
373 END IF;
374 Open c5(p_id);
375 fetch c5 into l_return_status;
376 close c5;
377 IF l_return_status=OKC_API.G_RET_STS_ERROR then
378 raise l_excp_error;
379 END IF;
380 return l_return_status;
381
382 EXCEPTION
383
384 WHEN l_excp_error then
385 return l_return_status;
386 WHEN OTHERS THEN
387 OKC_API.set_message(p_app_name => g_app_name,
388 p_msg_name => g_unexpected_error,
389 p_token1 => g_sqlcode_token,
390 p_token1_value => sqlcode,
391 p_token2 => g_sqlerrm_token,
392 p_token2_value => sqlerrm);
393 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
394
395 END USED_IN_SETUPS;
396
397
398 FUNCTION USED_IN_SRC_OPS(p_id IN number)
399 RETURN VARCHAR2 IS
400 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
401 l_excp_error EXCEPTION;
402 CURSOR c1 (p_id IN NUMBER) is
403 select OKC_API.G_RET_STS_ERROR from okc_line_style_sources where lse_id=p_id;
404 CURSOR c2 (p_id IN NUMBER) is
405 select OKC_API.G_RET_STS_ERROR from okc_val_line_operations where lse_id=p_id;
406
407 BEGIN
408 Open c1(p_id);
409 fetch c1 into l_return_status;
410 close c1;
411 IF l_return_status=OKC_API.G_RET_STS_ERROR then
412 raise l_excp_error;
413 END IF;
414 Open c2(p_id);
415 fetch c2 into l_return_status;
416 close c2;
417 IF l_return_status=OKC_API.G_RET_STS_ERROR then
418 raise l_excp_error;
419 END IF;
420 return l_return_status;
421
422 EXCEPTION
423
424 WHEN l_excp_error then
425 return l_return_status;
426 WHEN OTHERS THEN
427 OKC_API.set_message(p_app_name => g_app_name,
428 p_msg_name => g_unexpected_error,
429 p_token1 => g_sqlcode_token,
430 p_token1_value => sqlcode,
431 p_token2 => g_sqlerrm_token,
432 p_token2_value => sqlerrm);
433 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
434
435 END USED_IN_SRC_OPS;
436
437
438 PROCEDURE DELETE_SUB_LINES(
439 p_api_version IN NUMBER,
440 p_init_msg_list IN VARCHAR2 ,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_msg_count OUT NOCOPY NUMBER,
443 x_msg_data OUT NOCOPY VARCHAR2,
444 p_lsev_rec IN lsev_rec_type) IS
445
446 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
447 l_lsev_rec OKC_LINE_STYLES_PUB.lsev_rec_type;
448 CURSOR c1(p_id IN NUMBER) is
449 select id from okc_line_styles_b where lse_parent_id=p_id;
450
451 BEGIN
452 for l_rec in c1(p_lsev_rec.id) loop
453 l_lsev_rec.id:=l_rec.id;
454 OKC_LINE_STYLES_PUB.DELETE_LINE_STYLES(
455 p_api_version,
456 p_init_msg_list,
457 x_return_status,
458 x_msg_count,
459 x_msg_data,
460 l_lsev_rec);
461 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
462 END LOOP;
463
464 EXCEPTION
465
466 WHEN OTHERS THEN
467 OKC_API.set_message(p_app_name => g_app_name,
468 p_msg_name => g_unexpected_error,
469 p_token1 => g_sqlcode_token,
470 p_token1_value => sqlcode,
471 p_token2 => g_sqlerrm_token,
472 p_token2_value => sqlerrm);
473 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
474
475 END DELETE_SUB_LINES;
476
477
478 PROCEDURE DELETE_SOURCES(
479 p_api_version IN NUMBER,
480 p_init_msg_list IN VARCHAR2 ,
481 x_return_status OUT NOCOPY VARCHAR2,
482 x_msg_count OUT NOCOPY NUMBER,
483 x_msg_data OUT NOCOPY VARCHAR2,
484 p_lsev_rec IN lsev_rec_type) IS
485
486 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
487 l_lssv_rec OKC_LINE_STYLES_PUB.lssv_rec_type;
488 CURSOR c1(p_id IN NUMBER) is
489 select lse_id,jtot_object_code from okc_line_style_sources where lse_id=p_id;
490
491 BEGIN
492 for l_rec in c1(p_lsev_rec.id) loop
493 l_lssv_rec.lse_id:=l_rec.lse_id;
494 l_lssv_rec.jtot_object_code:=l_rec.jtot_object_code;
495 OKC_LINE_STYLES_PUB.DELETE_LINE_STYLE_SOURCES(
496 p_api_version,
497 p_init_msg_list,
498 x_return_status,
499 x_msg_count,
500 x_msg_data,
501 l_lssv_rec);
502 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
503 END LOOP;
504
505 EXCEPTION
506
507 WHEN OTHERS THEN
508 OKC_API.set_message(p_app_name => g_app_name,
509 p_msg_name => g_unexpected_error,
510 p_token1 => g_sqlcode_token,
511 p_token1_value => sqlcode,
512 p_token2 => g_sqlerrm_token,
513 p_token2_value => sqlerrm);
514 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
515
516 END DELETE_SOURCES;
517
518
519 PROCEDURE DELETE_VAL_OPS(
520 p_api_version IN NUMBER,
521 p_init_msg_list IN VARCHAR2 ,
522 x_return_status OUT NOCOPY VARCHAR2,
523 x_msg_count OUT NOCOPY NUMBER,
524 x_msg_data OUT NOCOPY VARCHAR2,
525 p_lsev_rec IN lsev_rec_type) IS
526
527 l_return_status VARCHAR2(1):=OKC_API.G_RET_STS_SUCCESS;
528 l_vlov_rec OKC_LINE_STYLES_PUB.vlov_rec_type;
529 CURSOR c1(p_id IN NUMBER) is
530 select lse_id from okc_val_line_operations where lse_id=p_id;
531
532 BEGIN
533 for l_rec in c1(p_lsev_rec.id) loop
534 l_vlov_rec.lse_id:=l_rec.lse_id;
535 OKC_LINE_STYLES_PUB.DELETE_VAL_LINE_OPERATION(
536 p_api_version,
537 p_init_msg_list,
538 x_return_status,
539 x_msg_count,
540 x_msg_data,
541 l_vlov_rec);
542 exit when x_return_status <> OKC_API.G_RET_STS_SUCCESS;
543 END LOOP;
544
545 EXCEPTION
546
547 WHEN OTHERS THEN
548 OKC_API.set_message(p_app_name => g_app_name,
549 p_msg_name => g_unexpected_error,
550 p_token1 => g_sqlcode_token,
551 p_token1_value => sqlcode,
552 p_token2 => g_sqlerrm_token,
553 p_token2_value => sqlerrm);
554 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
555
556 END DELETE_VAL_OPS;
557
558
559 PROCEDURE CREATE_LINE_STYLES(
560 p_api_version IN NUMBER,
561 p_init_msg_list IN VARCHAR2 ,
562 x_return_status OUT NOCOPY VARCHAR2,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_msg_data OUT NOCOPY VARCHAR2,
565 p_lsev_rec IN lsev_rec_type,
566 x_lsev_rec OUT NOCOPY lsev_rec_type) IS
567 BEGIN
568 okc_lse_pvt.insert_row(
569 p_api_version,
570 p_init_msg_list,
571 x_return_status,
572 x_msg_count,
573 x_msg_data,
574 p_lsev_rec,
575 x_lsev_rec);
576 --If the new line has priced_yn set to 'Y' then set its parent's to 'N'
577 IF x_return_status=OKC_API.G_RET_STS_SUCCESS and x_lsev_rec.priced_yn=G_YES then
578
579 CHANGE_PRICED_FOR_PARENT(
580 p_api_version,
581 p_init_msg_list,
582 x_return_status,
583 x_msg_count,
584 x_msg_data,
585 x_lsev_rec);
586 END IF;
587
588 IF x_return_status=OKC_API.G_RET_STS_SUCCESS and x_lsev_rec.item_to_price_yn=G_YES then
589
590 CHANGE_ITEM_TO_PRICE_PARENT(
591 p_api_version,
592 p_init_msg_list,
593 x_return_status,
594 x_msg_count,
595 x_msg_data,
596 x_lsev_rec);
597 END IF;
598
599 IF x_return_status=OKC_API.G_RET_STS_SUCCESS and x_lsev_rec.price_basis_yn=G_YES then
600
601 CHANGE_PRICE_BASIS_PARENT(
602 p_api_version,
603 p_init_msg_list,
604 x_return_status,
605 x_msg_count,
606 x_msg_data,
607 x_lsev_rec);
608 END IF;
609 EXCEPTION
610
611 WHEN OTHERS THEN
612 OKC_API.set_message(p_app_name => g_app_name,
613 p_msg_name => g_unexpected_error,
614 p_token1 => g_sqlcode_token,
615 p_token1_value => sqlcode,
616 p_token2 => g_sqlerrm_token,
617 p_token2_value => sqlerrm);
618 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
619
620 END CREATE_LINE_STYLES;
621
622 PROCEDURE UPDATE_LINE_STYLES(
623 p_api_version IN NUMBER,
624 p_init_msg_list IN VARCHAR2 ,
625 x_return_status OUT NOCOPY VARCHAR2,
626 x_msg_count OUT NOCOPY NUMBER,
627 x_msg_data OUT NOCOPY VARCHAR2,
628 p_lsev_rec IN lsev_rec_type,
629 x_lsev_rec OUT NOCOPY lsev_rec_type) IS
630 l_excp_error EXCEPTION;
631 l_lsev_rec lsev_rec_type;
632 BEGIN
633 IF p_lsev_rec.lty_code<>OKC_API.G_MISS_CHAR then
634 x_return_status:=USED_IN_K_LINES(p_lsev_rec.id);
635 IF x_return_status=OKC_API.G_RET_STS_ERROR then
636 OKC_API.set_message(p_app_name => g_app_name,
637 p_msg_name =>G_UPD_IN_K);
638 raise l_excp_error;
639 END IF;
640
641 IF p_lsev_rec.lty_code='FREE_FORM' then
642 DELETE_SOURCES(
643 p_api_version,
644 p_init_msg_list,
645 x_return_status,
646 x_msg_count,
647 x_msg_data,
648 p_lsev_rec);
649
650 IF x_return_status=OKC_API.G_RET_STS_ERROR then
651 raise l_excp_error;
652 END IF;
653 END IF;
654
655 END IF;
656 okc_lse_pvt.update_row(
657 p_api_version,
658 p_init_msg_list,
659 x_return_status,
660 x_msg_count,
661 x_msg_data,
662 p_lsev_rec,
663 x_lsev_rec);
664 --If the updated line has priced_yn set to 'Y' then set its children's to 'N'
665 IF x_return_status=OKC_API.G_RET_STS_SUCCESS and x_lsev_rec.priced_yn=G_YES then
666 CHANGE_PRICED_FOR_CHILDREN(
667 p_api_version,
668 p_init_msg_list,
669 x_return_status,
670 x_msg_count,
671 x_msg_data,
672 x_lsev_rec);
673 --If the updated line has priced_yn set to 'Y' then set its parent's to 'N'
674 CHANGE_PRICED_FOR_PARENT(
675 p_api_version,
676 p_init_msg_list,
677 x_return_status,
678 x_msg_count,
679 x_msg_data,
680 x_lsev_rec);
681 END IF;
682
683 IF x_return_status=OKC_API.G_RET_STS_SUCCESS and x_lsev_rec.item_to_price_yn=G_YES then
684 CHANGE_ITEM_TO_PRICE_CHILDREN(
685 p_api_version,
686 p_init_msg_list,
687 x_return_status,
688 x_msg_count,
689 x_msg_data,
690 x_lsev_rec);
691 --If the updated line has item_to_price_yn set to 'Y' then set its parent's to 'N'
692 CHANGE_ITEM_TO_PRICE_PARENT(
693 p_api_version,
694 p_init_msg_list,
695 x_return_status,
696 x_msg_count,
697 x_msg_data,
698 x_lsev_rec);
699 END IF;
700
701 IF x_return_status=OKC_API.G_RET_STS_SUCCESS and x_lsev_rec.price_basis_yn=G_YES then
702 CHANGE_PRICE_BASIS_CHILDREN(
703 p_api_version,
704 p_init_msg_list,
705 x_return_status,
706 x_msg_count,
707 x_msg_data,
708 x_lsev_rec);
709 --If the updated line has price_basis_yn set to 'Y' then set its parent's to 'N'
710 CHANGE_PRICE_BASIS_PARENT(
711 p_api_version,
712 p_init_msg_list,
713 x_return_status,
714 x_msg_count,
715 x_msg_data,
716 x_lsev_rec);
717 END IF;
718
719
720 EXCEPTION
721 WHEN l_excp_error then
722 x_return_status := OKC_API.G_RET_STS_ERROR;
723
724 WHEN OTHERS THEN
725 OKC_API.set_message(p_app_name => g_app_name,
726 p_msg_name => g_unexpected_error,
727 p_token1 => g_sqlcode_token,
728 p_token1_value => sqlcode,
729 p_token2 => g_sqlerrm_token,
730 p_token2_value => sqlerrm);
731 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
732
733 END UPDATE_LINE_STYLES;
734
735 PROCEDURE DELETE_LINE_STYLES(
736 p_api_version IN NUMBER,
737 p_init_msg_list IN VARCHAR2 ,
738 x_return_status OUT NOCOPY VARCHAR2,
739 x_msg_count OUT NOCOPY NUMBER,
740 x_msg_data OUT NOCOPY VARCHAR2,
741 p_lsev_rec IN lsev_rec_type) IS
742
743 l_excp_error EXCEPTION;
744 BEGIN
745 x_return_status:=USED_IN_K_LINES(p_lsev_rec.id);
746 IF x_return_status=OKC_API.G_RET_STS_ERROR then
747 OKC_API.set_message(p_app_name => g_app_name,
748 p_msg_name => G_DEL_IN_K);
749 raise l_excp_error;
750 END IF;
751 x_return_status:=USED_IN_SETUPS(p_lsev_rec.id);
752 IF x_return_status=OKC_API.G_RET_STS_ERROR then
753 OKC_API.set_message(p_app_name => g_app_name,
754 p_msg_name => G_DEL_IN_S);
755 raise l_excp_error;
756 END IF;
757
758 DELETE_SUB_LINES(
759 p_api_version,
760 p_init_msg_list,
761 x_return_status,
762 x_msg_count,
763 x_msg_data,
764 p_lsev_rec);
765 IF x_return_status=OKC_API.G_RET_STS_ERROR then
766 raise l_excp_error;
767 END IF;
768
769 DELETE_VAL_OPS(
770 p_api_version,
771 p_init_msg_list,
772 x_return_status,
773 x_msg_count,
774 x_msg_data,
775 p_lsev_rec);
776
777 IF x_return_status=OKC_API.G_RET_STS_ERROR then
778 raise l_excp_error;
779 END IF;
780
781 DELETE_SOURCES(
782 p_api_version,
783 p_init_msg_list,
784 x_return_status,
785 x_msg_count,
786 x_msg_data,
787 p_lsev_rec);
788
789 IF x_return_status=OKC_API.G_RET_STS_ERROR then
790 raise l_excp_error;
791 END IF;
792
793 okc_lse_pvt.delete_row(
794 p_api_version,
795 p_init_msg_list,
796 x_return_status,
797 x_msg_count,
798 x_msg_data,
799 p_lsev_rec);
800 EXCEPTION
801 WHEN l_excp_error then
802 x_return_status := OKC_API.G_RET_STS_ERROR;
803
804 WHEN OTHERS THEN
805 OKC_API.set_message(p_app_name => g_app_name,
806 p_msg_name => g_unexpected_error,
807 p_token1 => g_sqlcode_token,
808 p_token1_value => sqlcode,
809 p_token2 => g_sqlerrm_token,
810 p_token2_value => sqlerrm);
811 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
812
813 END DELETE_LINE_STYLES;
814
815 PROCEDURE LOCK_LINE_STYLES(
816 p_api_version IN NUMBER,
817 p_init_msg_list IN VARCHAR2 ,
818 x_return_status OUT NOCOPY VARCHAR2,
819 x_msg_count OUT NOCOPY NUMBER,
820 x_msg_data OUT NOCOPY VARCHAR2,
821 p_lsev_rec IN lsev_rec_type) IS
822 BEGIN
823 okc_lse_pvt.lock_row(
824 p_api_version,
825 p_init_msg_list,
826 x_return_status,
827 x_msg_count,
828 x_msg_data,
829 p_lsev_rec);
830 END LOCK_LINE_STYLES;
831
832 PROCEDURE VALID_LINE_STYLES(
833 p_api_version IN NUMBER,
834 p_init_msg_list IN VARCHAR2 ,
835 x_return_status OUT NOCOPY VARCHAR2,
836 x_msg_count OUT NOCOPY NUMBER,
837 x_msg_data OUT NOCOPY VARCHAR2,
838 p_lsev_rec IN lsev_rec_type) IS
839 BEGIN
840 okc_lse_pvt.validate_row(
841 p_api_version,
842 p_init_msg_list,
843 x_return_status,
844 x_msg_count,
845 x_msg_data,
846 p_lsev_rec);
847 END VALID_LINE_STYLES;
848
849 --------------------------------------------------------------------------
850 ---The following procedures cater to handling of OKC_LINE_STYLE_SOURCES
851 --------------------------------------------------------------------------
852
853 PROCEDURE CREATE_LINE_STYLE_SOURCES(
854 p_api_version IN NUMBER,
855 p_init_msg_list IN VARCHAR2 ,
856 x_return_status OUT NOCOPY VARCHAR2,
857 x_msg_count OUT NOCOPY NUMBER,
858 x_msg_data OUT NOCOPY VARCHAR2,
859 p_lssv_rec IN lssv_rec_type,
860 x_lssv_rec OUT NOCOPY lssv_rec_type) IS
861 BEGIN
862 okc_lss_pvt.insert_row(
863 p_api_version,
864 p_init_msg_list,
865 x_return_status,
866 x_msg_count,
867 x_msg_data,
868 p_lssv_rec,
869 x_lssv_rec);
870 END CREATE_LINE_STYLE_SOURCES;
871
872 PROCEDURE UPDATE_LINE_STYLE_SOURCES(
873 p_api_version IN NUMBER,
874 p_init_msg_list IN VARCHAR2 ,
875 x_return_status OUT NOCOPY VARCHAR2,
876 x_msg_count OUT NOCOPY NUMBER,
877 x_msg_data OUT NOCOPY VARCHAR2,
878 p_lssv_rec IN lssv_rec_type,
879 x_lssv_rec OUT NOCOPY lssv_rec_type) IS
880 BEGIN
881 okc_lss_pvt.update_row(
882 p_api_version,
883 p_init_msg_list,
884 x_return_status,
885 x_msg_count,
886 x_msg_data,
887 p_lssv_rec,
888 x_lssv_rec);
889 END UPDATE_LINE_STYLE_SOURCES;
890
891 PROCEDURE DELETE_LINE_STYLE_SOURCES(
892 p_api_version IN NUMBER,
893 p_init_msg_list IN VARCHAR2 ,
894 x_return_status OUT NOCOPY VARCHAR2,
895 x_msg_count OUT NOCOPY NUMBER,
896 x_msg_data OUT NOCOPY VARCHAR2,
897 p_lssv_rec IN lssv_rec_type) IS
898 BEGIN
899 okc_lss_pvt.delete_row(
900 p_api_version,
901 p_init_msg_list,
902 x_return_status,
903 x_msg_count,
904 x_msg_data,
905 p_lssv_rec);
906 END DELETE_LINE_STYLE_SOURCES;
907
908 PROCEDURE LOCK_LINE_STYLE_SOURCES(
909 p_api_version IN NUMBER,
910 p_init_msg_list IN VARCHAR2 ,
911 x_return_status OUT NOCOPY VARCHAR2,
912 x_msg_count OUT NOCOPY NUMBER,
913 x_msg_data OUT NOCOPY VARCHAR2,
914 p_lssv_rec IN lssv_rec_type) IS
915 BEGIN
916 okc_lss_pvt.lock_row(
917 p_api_version,
918 p_init_msg_list,
919 x_return_status,
920 x_msg_count,
921 x_msg_data,
922 p_lssv_rec);
923 END LOCK_LINE_STYLE_SOURCES;
924
925 PROCEDURE VALID_LINE_STYLE_SOURCES(
926 p_api_version IN NUMBER,
927 p_init_msg_list IN VARCHAR2 ,
928 x_return_status OUT NOCOPY VARCHAR2,
929 x_msg_count OUT NOCOPY NUMBER,
930 x_msg_data OUT NOCOPY VARCHAR2,
931 p_lssv_rec IN lssv_rec_type) IS
932 BEGIN
933 okc_lss_pvt.validate_row(
934 p_api_version,
935 p_init_msg_list,
936 x_return_status,
937 x_msg_count,
938 x_msg_data,
939 p_lssv_rec);
940 END VALID_LINE_STYLE_SOURCES;
941
942
943 --added by smhanda
944
945 --------------------------------------------------------------------------
946 ---The following procedures cater to handling of OKC_VAL_LINE_OPERATION
947 --------------------------------------------------------------------------
948
949 PROCEDURE CREATE_VAL_LINE_OPERATION(
950 p_api_version IN NUMBER,
951 p_init_msg_list IN VARCHAR2 ,
952 x_return_status OUT NOCOPY VARCHAR2,
953 x_msg_count OUT NOCOPY NUMBER,
954 x_msg_data OUT NOCOPY VARCHAR2,
955 p_vlov_rec IN vlov_rec_type,
956 x_vlov_rec OUT NOCOPY vlov_rec_type) IS
957 BEGIN
958 okc_vlo_pvt.insert_row(
959 p_api_version,
960 p_init_msg_list,
961 x_return_status,
962 x_msg_count,
963 x_msg_data,
964 p_vlov_rec,
965 x_vlov_rec);
966 END CREATE_VAL_LINE_OPERATION;
967
968 PROCEDURE UPDATE_VAL_LINE_OPERATION(
969 p_api_version IN NUMBER,
970 p_init_msg_list IN VARCHAR2 ,
971 x_return_status OUT NOCOPY VARCHAR2,
972 x_msg_count OUT NOCOPY NUMBER,
973 x_msg_data OUT NOCOPY VARCHAR2,
974 p_vlov_rec IN vlov_rec_type,
975 x_vlov_rec OUT NOCOPY vlov_rec_type) IS
976 BEGIN
977 okc_vlo_pvt.update_row(
978 p_api_version,
979 p_init_msg_list,
980 x_return_status,
981 x_msg_count,
982 x_msg_data,
983 p_vlov_rec,
984 x_vlov_rec);
985 END UPDATE_VAL_LINE_OPERATION;
986
987 PROCEDURE DELETE_VAL_LINE_OPERATION(
988 p_api_version IN NUMBER,
989 p_init_msg_list IN VARCHAR2 ,
990 x_return_status OUT NOCOPY VARCHAR2,
991 x_msg_count OUT NOCOPY NUMBER,
992 x_msg_data OUT NOCOPY VARCHAR2,
993 p_vlov_rec IN vlov_rec_type) IS
994 BEGIN
995 okc_vlo_pvt.delete_row(
996 p_api_version,
997 p_init_msg_list,
998 x_return_status,
999 x_msg_count,
1000 x_msg_data,
1001 p_vlov_rec);
1002 END DELETE_VAL_LINE_OPERATION;
1003
1004 PROCEDURE LOCK_VAL_LINE_OPERATION(
1005 p_api_version IN NUMBER,
1006 p_init_msg_list IN VARCHAR2 ,
1007 x_return_status OUT NOCOPY VARCHAR2,
1008 x_msg_count OUT NOCOPY NUMBER,
1009 x_msg_data OUT NOCOPY VARCHAR2,
1010 p_vlov_rec IN vlov_rec_type) IS
1011 BEGIN
1012 okc_vlo_pvt.lock_row(
1013 p_api_version,
1014 p_init_msg_list,
1015 x_return_status,
1016 x_msg_count,
1017 x_msg_data,
1018 p_vlov_rec);
1019 END LOCK_VAL_LINE_OPERATION;
1020
1021 PROCEDURE VALIDATE_VAL_LINE_OPERATION(
1022 p_api_version IN NUMBER,
1023 p_init_msg_list IN VARCHAR2 ,
1024 x_return_status OUT NOCOPY VARCHAR2,
1025 x_msg_count OUT NOCOPY NUMBER,
1026 x_msg_data OUT NOCOPY VARCHAR2,
1027 p_vlov_rec IN vlov_rec_type) IS
1028 BEGIN
1029 okc_vlo_pvt.validate_row(
1030 p_api_version,
1031 p_init_msg_list,
1032 x_return_status,
1033 x_msg_count,
1034 x_msg_data,
1035 p_vlov_rec);
1036 END VALIDATE_VAL_LINE_OPERATION;
1037
1038
1039 END OKC_LINE_STYLES_PVT;