[Home] [Help]
PACKAGE BODY: APPS.OKC_INST_CND_PVT
Source
1 PACKAGE BODY OKC_INST_CND_PVT AS
2 /* $Header: OKCRINCB.pls 120.0 2005/05/25 19:36:52 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 /*********************** HAND-CODED ***************************************/
7 ----------------------------------------------------------------------------
8 -- PROCEDURE inst_condition
9 ----------------------------------------------------------------------------
10 PROCEDURE inst_condition(
11 p_api_version IN NUMBER,
12 p_init_msg_list IN VARCHAR2 ,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2,
16 p_instcnd_inp_rec IN instcnd_inp_rec) IS
17
18 l_api_name CONSTANT VARCHAR2(30) := 'inst_condition';
19 l_api_version CONSTANT NUMBER := 1.0;
20 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
21 l_msg_data VARCHAR2(1000) ;
22 l_msg_count NUMBER;
23 l_cnhv_rec OKC_CONDITIONS_PUB.CNHV_REC_TYPE;
24 x_cnhv_rec OKC_CONDITIONS_PUB.CNHV_REC_TYPE;
25 l_cnlv_rec OKC_CONDITIONS_PUB.CNLV_REC_TYPE;
26 x_cnlv_rec OKC_CONDITIONS_PUB.CNLV_REC_TYPE;
27 l_fepv_rec OKC_CONDITIONS_PUB.FEPV_REC_TYPE;
28 x_fepv_rec OKC_CONDITIONS_PUB.FEPV_REC_TYPE;
29 l_ocev_rec OKC_OUTCOME_PUB.OCEV_REC_TYPE;
30 x_ocev_rec OKC_OUTCOME_PUB.OCEV_REC_TYPE;
31 l_oatv_rec OKC_OUTCOME_PUB.OATV_REC_TYPE;
32 x_oatv_rec OKC_OUTCOME_PUB.OATV_REC_TYPE;
33
34 -- Cursor to get associated conditions related to that counter group id
35 CURSOR ctr_cnh_cur(b_tmp_ctr_grp_id IN NUMBER
36 ,b_inv_item_id IN NUMBER) IS
37 SELECT id
38 FROM okc_condition_headers_v
39 WHERE counter_group_id = b_tmp_ctr_grp_id
40 AND object_id = b_inv_item_id
41 AND jtot_object_code = 'OKX_SYSITEM'
42 AND template_yn = 'Y';
43
44 -- Cursor for Condition Header and related information
45 CURSOR cnh_cur(b_cnh_id IN NUMBER) IS SELECT
46 acn_id
47 ,counter_group_id
48 ,description
49 ,short_description
50 ,comments
51 ,one_time_yn
52 ,name
53 ,condition_valid_yn
54 ,before_after
55 ,tracked_yn
56 ,task_owner_id
57 ,cnh_variance
58 ,dnz_chr_id
59 ,template_yn
60 ,date_active
61 ,object_id
62 ,date_inactive
63 ,jtot_object_code
64 ,cnh_type
65 ,attribute_category
66 ,attribute1
67 ,attribute2
68 ,attribute3
69 ,attribute4
70 ,attribute5
71 ,attribute6
72 ,attribute7
73 ,attribute8
74 ,attribute9
75 ,attribute10
76 ,attribute11
77 ,attribute12
78 ,attribute13
79 ,attribute14
80 ,attribute15
81 FROM okc_condition_headers_v
82 WHERE id = b_cnh_id
83 AND template_yn = 'Y';
84
85
86 -- Cursor for Condition Lines and related information
87 CURSOR cnl_cur(b_cnh_id IN NUMBER) IS SELECT
88 cnl.id cnl_id
89 ,cnl.cnh_id
90 ,cnl.pdf_id
91 ,cnl.aae_id
92 ,cnl.left_ctr_master_id
93 ,cnl.right_ctr_master_id
94 ,cnl.left_counter_id
95 ,cnl.right_counter_id
96 ,cnl.dnz_chr_id
97 ,cnl.sortseq
98 ,cnl.cnl_type
99 ,cnl.description
100 ,cnl.left_parenthesis
101 ,cnl.relational_operator
102 ,cnl.right_parenthesis
103 ,cnl.logical_operator
104 ,cnl.start_at
105 ,cnl.tolerance
106 ,cnl.right_operand
107 ,cnl.attribute_category
108 ,cnl.attribute1
109 ,cnl.attribute2
110 ,cnl.attribute3
111 ,cnl.attribute4
112 ,cnl.attribute5
113 ,cnl.attribute6
114 ,cnl.attribute7
115 ,cnl.attribute8
116 ,cnl.attribute9
117 ,cnl.attribute10
118 ,cnl.attribute11
119 ,cnl.attribute12
120 ,cnl.attribute13
121 ,cnl.attribute14
122 ,cnl.attribute15
123 FROM okc_condition_headers_v cnh,okc_condition_lines_v cnl
124 WHERE cnh.id = cnl.cnh_id
125 AND cnh.id = b_cnh_id;
126
127 -- Cursor for Counter Group related information
128 CURSOR ctr_grp_cur(b_ctr_grp_id IN NUMBER) IS SELECT
129 counter_group_id
130 FROM okx_counter_groups_v
131 WHERE template_flag = 'N'
132 AND created_from_ctr_grp_tmpl_id = b_ctr_grp_id;
133
134 -- Cursor for Counter Template related information
135 CURSOR ctr_cur(b_ctr_grp_id IN NUMBER) IS SELECT
136 counter_id
137 FROM okx_counters_v
138 WHERE counter_group_id = b_ctr_grp_id
139 AND created_from_counter_tmpl_id IS NULL
140 AND source_counter_id IS NULL;
141
142 -- Cursor for Counter Instance related information
143 CURSOR ctr_ins_cur(b_ctr_grp_id IN NUMBER,b_ctr_id IN NUMBER) IS SELECT
144 counter_id
145 FROM okx_counters_v
146 WHERE counter_group_id = b_ctr_grp_id
147 AND created_from_counter_tmpl_id = b_ctr_id;
148
149 -- Cursor for Function Expression Parameters and related information
150
151 CURSOR fep_cur(b_cnh_id IN NUMBER) IS SELECT
152 fep.id fep_id
153 ,fep.cnl_id
154 ,fep.pdp_id
155 ,fep.aae_id
156 ,fep.dnz_chr_id
157 ,fep.value
158 FROM okc_function_expr_params_v fep,okc_condition_headers_v cnh,
159 okc_condition_lines_v cnl
160 WHERE fep.cnl_id = cnl.id
161 AND cnl.cnh_id = cnh.id
162 AND cnh.id = b_cnh_id;
163
164 -- Cursor for Outcomes and related information
165 CURSOR oce_cur(b_cnh_id IN NUMBER) IS SELECT
166 oce.id oce_id
167 ,oce.pdf_id
168 ,oce.cnh_id
169 ,oce.dnz_chr_id
170 ,oce.enabled_yn
171 ,oce.comments
172 ,oce.success_resource_id
173 ,oce.failure_resource_id
174 ,oce.attribute_category
175 ,oce.attribute1
176 ,oce.attribute2
177 ,oce.attribute3
178 ,oce.attribute4
179 ,oce.attribute5
180 ,oce.attribute6
181 ,oce.attribute7
182 ,oce.attribute8
183 ,oce.attribute9
184 ,oce.attribute10
185 ,oce.attribute11
186 ,oce.attribute12
187 ,oce.attribute13
188 ,oce.attribute14
189 ,oce.attribute15
190 FROM okc_outcomes_v oce,okc_condition_headers_v cnh
191 WHERE oce.cnh_id = cnh.id
192 AND cnh.id = b_cnh_id;
193
194 -- Cursor for Outcome parameters and related information
195 CURSOR oat_cur(b_cnh_id IN NUMBER) IS SELECT
196 oat.id oat_id
197 ,oat.pdp_id
198 ,oat.oce_id
199 ,oat.aae_id
200 ,oat.dnz_chr_id
201 ,oat.value
202 FROM okc_outcome_arguments_v oat,okc_outcomes_v oce,
203 okc_condition_headers_v cnh
204 WHERE oat.oce_id = oce.id
205 AND oce.cnh_id = cnh.id
206 AND cnh.id = b_cnh_id;
207
208 BEGIN
209
210 -- call start_activity to create savepoint, check comptability
211 -- and initialize message list
212 l_return_status := OKC_API.START_ACTIVITY(l_api_name
213 ,p_init_msg_list
214 ,'_PROCESS'
215 ,x_return_status
216 );
217 -- check if activity started successfully
218 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
219 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
220 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
221 RAISE OKC_API.G_EXCEPTION_ERROR;
222 END IF;
223 -- Put Functionality here regarding the gathering of extra information
224 -- related to the condition
225
226 -- Create Condition Header Instance
227
228 FOR ctr_cnh_cur_rec IN ctr_cnh_cur(p_instcnd_inp_rec.tmp_ctr_grp_id,p_instcnd_inp_rec.inv_item_id)LOOP
229 SELECT
230 id
231 ,acn_id
232 ,counter_group_id
233 ,description
234 ,short_description
235 ,comments
236 ,one_time_yn
237 ,name
238 ,condition_valid_yn
239 ,before_after
240 ,tracked_yn
241 ,task_owner_id
242 ,cnh_variance
243 ,dnz_chr_id
244 ,date_active
245 ,object_id
246 ,date_inactive
247 ,template_yn
248 ,jtot_object_code
249 ,cnh_type
250 ,attribute_category
251 ,attribute1
252 ,attribute2
253 ,attribute3
254 ,attribute4
255 ,attribute5
256 ,attribute6
257 ,attribute7
258 ,attribute8
259 ,attribute9
260 ,attribute10
261 ,attribute11
262 ,attribute12
263 ,attribute13
264 ,attribute14
265 ,attribute15
266 INTO
267 l_cnhv_rec.id
268 ,l_cnhv_rec.acn_id
269 ,l_cnhv_rec.counter_group_id
270 ,l_cnhv_rec.description
271 ,l_cnhv_rec.short_description
272 ,l_cnhv_rec.comments
273 ,l_cnhv_rec.one_time_yn
274 ,l_cnhv_rec.name
275 ,l_cnhv_rec.condition_valid_yn
276 ,l_cnhv_rec.before_after
277 ,l_cnhv_rec.tracked_yn
278 ,l_cnhv_rec.task_owner_id
279 ,l_cnhv_rec.cnh_variance
280 ,l_cnhv_rec.dnz_chr_id
281 ,l_cnhv_rec.date_active
282 ,l_cnhv_rec.object_id
283 ,l_cnhv_rec.date_inactive
284 ,l_cnhv_rec.template_yn
285 ,l_cnhv_rec.jtot_object_code
286 ,l_cnhv_rec.cnh_type
287 ,l_cnhv_rec.attribute_category
288 ,l_cnhv_rec.attribute1
289 ,l_cnhv_rec.attribute2
290 ,l_cnhv_rec.attribute3
291 ,l_cnhv_rec.attribute4
292 ,l_cnhv_rec.attribute5
293 ,l_cnhv_rec.attribute6
294 ,l_cnhv_rec.attribute7
295 ,l_cnhv_rec.attribute8
296 ,l_cnhv_rec.attribute9
297 ,l_cnhv_rec.attribute10
298 ,l_cnhv_rec.attribute11
299 ,l_cnhv_rec.attribute12
300 ,l_cnhv_rec.attribute13
301 ,l_cnhv_rec.attribute14
302 ,l_cnhv_rec.attribute15
303 FROM okc_condition_headers_v
304 WHERE id = ctr_cnh_cur_rec.id
305 AND template_yn = 'Y';
306
307 l_cnhv_rec.counter_group_id := p_instcnd_inp_rec.ins_ctr_grp_id;
308 l_cnhv_rec.object_id := p_instcnd_inp_rec.cle_id;
309 l_cnhv_rec.jtot_object_code := p_instcnd_inp_rec.jtot_object_code;
310 l_cnhv_rec.dnz_chr_id := p_instcnd_inp_rec.chr_id;
311 l_cnhv_rec.template_yn := 'N';
312
313 OKC_CONDITIONS_PUB.create_cond_hdrs(
314 p_api_version => 1.0,
315 p_init_msg_list => 'T',
316 x_return_status => x_return_status,
317 x_msg_count => x_msg_count,
318 x_msg_data => x_msg_data,
319 p_cnhv_rec => l_cnhv_rec,
320 x_cnhv_rec => x_cnhv_rec);
321
322 FOR cnl_cur_rec in cnl_cur(ctr_cnh_cur_rec.id) LOOP
323
324 l_cnlv_rec.cnh_id := x_cnhv_rec.id;
325 l_cnlv_rec.pdf_id := cnl_cur_rec.pdf_id;
326 l_cnlv_rec.aae_id := cnl_cur_rec.aae_id;
327 l_cnlv_rec.left_ctr_master_id := cnl_cur_rec.left_ctr_master_id;
328 l_cnlv_rec.right_ctr_master_id := cnl_cur_rec.right_ctr_master_id;
329 l_cnlv_rec.left_counter_id := cnl_cur_rec.left_counter_id;
330 l_cnlv_rec.right_counter_id := cnl_cur_rec.right_counter_id;
331 l_cnlv_rec.dnz_chr_id := cnl_cur_rec.dnz_chr_id;
332 l_cnlv_rec.sortseq := cnl_cur_rec.sortseq;
333 l_cnlv_rec.cnl_type := cnl_cur_rec.cnl_type;
334 l_cnlv_rec.description := cnl_cur_rec.description;
335 l_cnlv_rec.left_parenthesis := cnl_cur_rec.left_parenthesis;
336 l_cnlv_rec.right_parenthesis := cnl_cur_rec.right_parenthesis;
337 l_cnlv_rec.logical_operator := cnl_cur_rec.logical_operator;
338 l_cnlv_rec.relational_operator := cnl_cur_rec.relational_operator;
339 l_cnlv_rec.start_at := cnl_cur_rec.start_at;
340 l_cnlv_rec.tolerance := cnl_cur_rec.tolerance;
341 l_cnlv_rec.right_operand := cnl_cur_rec.right_operand;
342 l_cnlv_rec.attribute_category := cnl_cur_rec.attribute_category;
343 l_cnlv_rec.attribute1 := cnl_cur_rec.attribute1;
344 l_cnlv_rec.attribute2 := cnl_cur_rec.attribute2;
345 l_cnlv_rec.attribute3 := cnl_cur_rec.attribute3;
346 l_cnlv_rec.attribute4 := cnl_cur_rec.attribute4;
347 l_cnlv_rec.attribute5 := cnl_cur_rec.attribute5;
348 l_cnlv_rec.attribute6 := cnl_cur_rec.attribute6;
349 l_cnlv_rec.attribute7 := cnl_cur_rec.attribute7;
350 l_cnlv_rec.attribute8 := cnl_cur_rec.attribute8;
351 l_cnlv_rec.attribute9 := cnl_cur_rec.attribute9;
352 l_cnlv_rec.attribute10 := cnl_cur_rec.attribute10;
353 l_cnlv_rec.attribute11 := cnl_cur_rec.attribute11;
354 l_cnlv_rec.attribute12 := cnl_cur_rec.attribute12;
355 l_cnlv_rec.attribute13 := cnl_cur_rec.attribute13;
356 l_cnlv_rec.attribute14 := cnl_cur_rec.attribute14;
357 l_cnlv_rec.attribute15 := cnl_cur_rec.attribute15;
358
359 IF cnl_cur_rec.cnl_type = 'CEX'
360 THEN
361 IF cnl_cur_rec.left_ctr_master_id IS NOT NULL THEN
362 SELECT counter_id INTO l_cnlv_rec.left_counter_id
363 FROM okx_counters_v
364 WHERE counter_group_id = p_instcnd_inp_rec.ins_ctr_grp_id
365 AND created_from_counter_tmpl_id = cnl_cur_rec.left_ctr_master_id;
366 END IF;
367 IF cnl_cur_rec.right_ctr_master_id IS NOT NULL THEN
368 SELECT counter_id INTO l_cnlv_rec.right_counter_id
369 FROM okx_counters_v
370 WHERE counter_group_id = p_instcnd_inp_rec.ins_ctr_grp_id
371 AND created_from_counter_tmpl_id = cnl_cur_rec.right_ctr_master_id;
372 END IF;
373 IF cnl_cur_rec.left_counter_id IS NOT NULL THEN
374 SELECT counter_id INTO l_cnlv_rec.left_counter_id
375 FROM okx_counters_v
376 WHERE counter_group_id = p_instcnd_inp_rec.ins_ctr_grp_id
377 AND created_from_counter_tmpl_id = cnl_cur_rec.left_counter_id;
378 END IF;
379 IF cnl_cur_rec.right_counter_id IS NOT NULL THEN
380 SELECT counter_id INTO l_cnlv_rec.right_counter_id
381 FROM okx_counters_v
382 WHERE counter_group_id = p_instcnd_inp_rec.ins_ctr_grp_id
383 AND created_from_counter_tmpl_id = cnl_cur_rec.right_counter_id;
384 END IF;
385 END IF;
386
387 l_cnlv_rec.dnz_chr_id := p_instcnd_inp_rec.chr_id;
388
389 OKC_CONDITIONS_PUB.create_cond_lines(
390 p_api_version => 1.0,
391 p_init_msg_list => 'T',
392 x_return_status => x_return_status,
393 x_msg_count => x_msg_count,
394 x_msg_data => x_msg_data,
395 p_cnlv_rec => l_cnlv_rec,
396 x_cnlv_rec => x_cnlv_rec);
397
398 FOR fep_cur_rec in fep_cur(ctr_cnh_cur_rec.id) LOOP
399 l_fepv_rec.cnl_id := x_cnlv_rec.id;
400 l_fepv_rec.pdp_id := fep_cur_rec.pdp_id;
401 l_fepv_rec.aae_id := fep_cur_rec.aae_id;
402 l_fepv_rec.dnz_chr_id := fep_cur_rec.dnz_chr_id;
403 l_fepv_rec.value := fep_cur_rec.value;
404
405 l_fepv_rec.dnz_chr_id := p_instcnd_inp_rec.chr_id;
406
407 OKC_CONDITIONS_PUB.create_func_exprs(
408 p_api_version => 1.0,
409 p_init_msg_list => 'T',
410 x_return_status => x_return_status,
411 x_msg_count => x_msg_count,
412 x_msg_data => x_msg_data,
413 p_fepv_rec => l_fepv_rec,
414 x_fepv_rec => x_fepv_rec);
415
416 END LOOP;
417
418 END LOOP;
419
420 FOR oce_cur_rec in oce_cur(ctr_cnh_cur_rec.id) LOOP
421 l_ocev_rec.pdf_id := oce_cur_rec.pdf_id;
422 l_ocev_rec.cnh_id := x_cnhv_rec.id;
423 l_ocev_rec.dnz_chr_id := oce_cur_rec.dnz_chr_id;
424 l_ocev_rec.enabled_yn := oce_cur_rec.enabled_yn;
425 l_ocev_rec.comments := oce_cur_rec.comments;
426 l_ocev_rec.success_resource_id := oce_cur_rec.success_resource_id;
427 l_ocev_rec.failure_resource_id := oce_cur_rec.failure_resource_id;
428 l_ocev_rec.attribute_category := oce_cur_rec.attribute_category;
429 l_ocev_rec.attribute1 := oce_cur_rec.attribute1;
430 l_ocev_rec.attribute2 := oce_cur_rec.attribute2;
431 l_ocev_rec.attribute3 := oce_cur_rec.attribute3;
432 l_ocev_rec.attribute4 := oce_cur_rec.attribute4;
433 l_ocev_rec.attribute5 := oce_cur_rec.attribute5;
434 l_ocev_rec.attribute6 := oce_cur_rec.attribute6;
435 l_ocev_rec.attribute7 := oce_cur_rec.attribute7;
436 l_ocev_rec.attribute8 := oce_cur_rec.attribute8;
437 l_ocev_rec.attribute9 := oce_cur_rec.attribute9;
438 l_ocev_rec.attribute10 := oce_cur_rec.attribute10;
439 l_ocev_rec.attribute11 := oce_cur_rec.attribute11;
440 l_ocev_rec.attribute12 := oce_cur_rec.attribute12;
441 l_ocev_rec.attribute13 := oce_cur_rec.attribute13;
442 l_ocev_rec.attribute14 := oce_cur_rec.attribute14;
443 l_ocev_rec.attribute15 := oce_cur_rec.attribute15;
444
445 l_ocev_rec.dnz_chr_id := p_instcnd_inp_rec.chr_id;
446
447 OKC_OUTCOME_PUB.create_outcome(
448 p_api_version => 1.0,
449 p_init_msg_list => 'T',
450 x_return_status => x_return_status,
451 x_msg_count => x_msg_count,
452 x_msg_data => x_msg_data,
453 p_ocev_rec => l_ocev_rec,
454 x_ocev_rec => x_ocev_rec);
455
456 FOR oat_cur_rec in oat_cur(ctr_cnh_cur_rec.id) LOOP
457 l_oatv_rec.pdp_id := oat_cur_rec.pdp_id;
458 l_oatv_rec.oce_id := x_ocev_rec.id;
459 l_oatv_rec.aae_id := oat_cur_rec.aae_id;
460 l_oatv_rec.dnz_chr_id := oat_cur_rec.dnz_chr_id;
461 l_oatv_rec.value := oat_cur_rec.value;
462
463 l_oatv_rec.dnz_chr_id := p_instcnd_inp_rec.chr_id;
464
465 OKC_OUTCOME_PUB.create_out_arg(
466 p_api_version => 1.0,
467 p_init_msg_list => 'T',
468 x_return_status => x_return_status,
469 x_msg_count => x_msg_count,
470 x_msg_data => x_msg_data,
471 p_oatv_rec => l_oatv_rec,
472 x_oatv_rec => x_oatv_rec);
473
474 END LOOP;
475
476 END LOOP;
477
478 END LOOP;
479 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
480
481 EXCEPTION
482 WHEN NO_DATA_FOUND THEN
483 NULL;
484 WHEN OKC_API.G_EXCEPTION_ERROR THEN
485 x_return_status := OKC_API.HANDLE_EXCEPTIONS
486 (l_api_name,
487 G_PKG_NAME,
488 'OKC_API.G_RET_STS_ERROR',
489 x_msg_count,
490 x_msg_data,
491 '_PROCESS');
492 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
493 x_return_status := OKC_API.HANDLE_EXCEPTIONS
494 (l_api_name,
495 G_PKG_NAME,
496 'OKC_API.G_RET_STS_UNEXP_ERROR',
497 x_msg_count,
498 x_msg_data,
499 '_PROCESS');
500 WHEN OTHERS THEN
501 x_return_status := OKC_API.HANDLE_EXCEPTIONS
502 (l_api_name,
503 G_PKG_NAME,
504 'OTHERS',
505 x_msg_count,
506 x_msg_data,
507 '_PROCESS');
508
509 END INST_CONDITION;
510
511 END OKC_INST_CND_PVT;