[Home] [Help]
PACKAGE BODY: APPS.ZX_SIM_CONDITIONS_PKG
Source
1 PACKAGE BODY ZX_SIM_CONDITIONS_PKG AS
2 /* $Header: zxrisimrulespvtb.pls 120.8 2005/06/29 17:53:54 lxzhang ship $ */
3
4 Cursor c_sim_cond (c_trxline_id NUMBER,
5 c_taxline_number NUMBER) Is
6 select count(*)
7 from zx_sim_conditions
8 where trx_line_id = c_trxline_id
9 and tax_line_number = c_taxline_number;
10
11 Cursor c_trxhdr Is
12 select *
13 from zx_trx_headers_gt;
14
15 Cursor c_trxlines(c_trx_id IN NUMBER) Is
16 select *
17 from zx_transaction_lines_gt
18 where trx_id = c_trx_id
19 and trx_level_type <> 'TAX';
20
21 TYPE tab_num_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
22 TYPE tab_var_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
23 TYPE tab_var1_type IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
24 TYPE tab_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
25
26 l_cond_cnt NUMBER;
27 l_taxlines_cnt NUMBER;
28 l_trxlines_cnt NUMBER;
29
30 g_current_runtime_level NUMBER;
31 g_level_statement NUMBER;
32 g_level_unexpected NUMBER;
33
34 PROCEDURE create_from_existing_rules (p_tax_regime_code IN VARCHAR2,
35 p_tax IN VARCHAR2,
36 p_content_owner_id IN NUMBER,
37 p_application_id IN NUMBER,
38 p_return_status OUT NOCOPY VARCHAR2,
39 p_error_buffer OUT NOCOPY VARCHAR2) AS
40 BEGIN
41 g_level_statement := FND_LOG.LEVEL_STATEMENT;
42 g_level_unexpected := FND_LOG.LEVEL_UNEXPECTED;
43
44 IF (g_level_statement >= g_current_runtime_level ) THEN
45 FND_LOG.STRING(g_level_statement,
46 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_from_existing_rules.BEGIN',
47 'ZX_SIM_CONDITIONS_PKG: create_from_existing_rules(+)');
48
49 FND_LOG.STRING(g_level_statement,
50 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_from_existing_rules',
51 'Simulate Rules');
52 END IF;
53
54 p_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 INSERT INTO zx_sim_rules_b (
57 sim_tax_rule_id,
58 content_owner_id,
59 tax_rule_code,
60 tax,
61 tax_regime_code,
62 service_type_code,
63 priority,
64 det_factor_templ_code,
65 effective_from,
66 simulated_flag,
67 created_by,
68 creation_date,
69 last_updated_by,
70 last_update_date,
71 last_update_login,
72 effective_to,
73 application_id,
74 recovery_type_code)
75 SELECT tax_rule_id,
76 content_owner_id,
77 tax_rule_code,
78 tax,
79 tax_regime_code,
80 service_type_code,
81 priority,
82 det_factor_templ_code,
83 effective_from,
84 'N' simulated_flag,
85 fnd_global.user_id created_by,
86 sysdate creation_date,
87 fnd_global.user_id last_updated_by,
88 sysdate last_update_date,
89 last_update_login,
90 effective_to,
91 application_id,
92 recovery_type_code
93 FROM zx_rules_b
94 WHERE tax_regime_code = p_tax_regime_code
95 and tax = p_tax
96 and content_owner_id = p_content_owner_id
97 and NVL(application_id, p_application_id) = p_application_id
98 -- The service type code restriction should be removed when
99 -- simulate rules is supported for other processes.
100 and service_type_code IN ('DET_APPLICABLE_TAXES',
101 'DET_TAX_STATUS',
102 'DET_TAX_RATE');
103
104 IF (g_level_statement >= g_current_runtime_level ) THEN
105 FND_LOG.STRING(g_level_statement,
106 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_from_existing_rules',
107 'Simulate Translation of Rules');
108 END IF;
109
110 INSERT INTO zx_sim_rules_tl (
111 sim_tax_rule_id,
112 language,
113 source_lang,
114 tax_rule_name,
115 created_by,
116 creation_date,
117 last_updated_by,
118 last_update_date,
119 last_update_login)
120 SELECT tax_rule_id,
121 language,
122 source_lang,
123 tax_rule_name,
124 fnd_global.user_id created_by,
125 sysdate creation_date,
126 fnd_global.user_id last_updated_by,
127 sysdate last_update_date,
128 last_update_login
129 FROM zx_rules_tl
130 WHERE tax_rule_id IN (select sim_tax_rule_id
131 from zx_sim_rules_b);
132
133 IF (g_level_statement >= g_current_runtime_level ) THEN
134 FND_LOG.STRING(g_level_statement,
135 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_from_existing_rules',
136 'Simulate Process Results');
137 END IF;
138
139 INSERT INTO zx_sim_process_results (
140 sim_result_id,
141 sim_tax_rule_id,
142 condition_group_code,
143 priority,
144 simulated_flag,
145 created_by,
146 creation_date,
147 last_updated_by,
148 last_update_date,
149 last_update_login,
150 result_type_code,
151 tax_status_code,
152 numeric_result,
153 alphanumeric_result)
154 SELECT result_id,
155 tax_rule_id,
156 condition_group_code,
157 priority,
158 'N' simulated_flag,
159 fnd_global.user_id created_by,
160 sysdate creation_date,
161 fnd_global.user_id last_updated_by,
162 sysdate last_update_date,
163 last_update_login,
164 result_type_code,
165 tax_status_code,
166 numeric_result,
167 alphanumeric_result
168 FROM zx_process_results
169 WHERE tax_rule_id IN (select sim_tax_rule_id
170 from zx_sim_rules_b);
171
172 IF (g_level_statement >= g_current_runtime_level ) THEN
173 FND_LOG.STRING(g_level_statement,
174 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_from_existing_rules',
175 'Simulate Conditions');
176 END IF;
177
178 INSERT INTO zx_sim_rule_conditions (
179 simrule_condition_id,
180 condition_group_code,
181 determining_factor_class_code,
182 determining_factor_code,
183 data_type_code,
184 operator_code,
185 ignore_flag,
186 simulated_flag,
187 created_by,
188 creation_date,
189 last_updated_by,
190 last_update_date,
191 last_update_login,
192 tax_parameter_code,
193 determining_factor_cq_code,
194 numeric_value,
195 date_value,
196 alphanumeric_value,
197 value_low,
198 value_high)
199 SELECT condition_id,
200 condition_group_code,
201 determining_factor_class_code,
202 determining_factor_code,
203 data_type_code,
204 operator_code,
205 ignore_flag,
206 'N' simulated_flag,
207 fnd_global.user_id created_by,
208 sysdate creation_date,
209 fnd_global.user_id last_updated_by,
210 sysdate last_update_date,
211 last_update_login,
212 tax_parameter_code,
213 determining_factor_cq_code,
214 numeric_value,
215 date_value,
216 alphanumeric_value,
217 value_low,
218 value_high
219 FROM zx_conditions
220 WHERE condition_group_code IN (select condition_group_code
221 from zx_sim_process_results
222 group by condition_group_code);
223
224 IF (g_level_statement >= g_current_runtime_level ) THEN
225 FND_LOG.STRING(g_level_statement,
226 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_from_existing_rules.END',
227 'ZX_SIM_CONDITIONS_PKG: create_from_existing_rules(-)');
228 END IF;
229
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
235
236 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
237 FND_MSG_PUB.Add;
238
239 IF (g_level_unexpected >= g_current_runtime_level ) THEN
240 FND_LOG.STRING(g_level_unexpected,
241 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_from_existing_rules',
242 p_error_buffer);
243 END IF;
244
245 END create_from_existing_rules;
246
247 -- This procedure creates Simulated Conditions based on ALL determining factors that are
248 -- enabled for Rules creation.
249 PROCEDURE create_sim_conditions (p_return_status OUT NOCOPY varchar2,
250 p_error_buffer OUT NOCOPY varchar2) AS
251
252 Cursor c_taxlines Is
253 select *
254 from zx_import_tax_lines_gt;
255
256 Cursor c_detfactors Is
257 select determining_factor_class_code,
258 NULL determining_factor_cq_code,
259 determining_factor_code,
260 tax_parameter_code,
261 '=' operator,
262 data_type_code,
263 NULL numeric_value,
264 NULL alphanum_value,
265 NULL date_value,
266 NULL value_low,
267 NULL value_high
268 from zx_determining_factors_b
269 where tax_rules_flag = 'Y'
270 and determining_factor_class_code in ('TRX_INPUT_FACTOR');
271
272 pr_detfactor_class_tab tab_var_type;
273 pr_detfactor_cq_tab tab_var_type;
274 pr_detfactor_code_tab tab_var_type;
275 pr_parameter_code_tab tab_var_type;
276 pr_operator_tab tab_var_type;
277 pr_datatype_tab tab_var_type;
278 pr_numeric_value_tab tab_num_type;
279 pr_alpha_value_tab tab_var1_type;
280 pr_value_low_tab tab_var1_type;
281 pr_value_high_tab tab_var1_type;
282 pr_date_value_tab tab_date_type;
283
284 l_chart_of_accounts_id number;
285 l_fsc_cat_rec ZX_TCM_CONTROL_PKG.ZX_CATEGORY_CODE_INFO_REC;
286 l_fsc_rec ZX_TCM_CONTROL_PKG.ZX_FISCAL_CLASS_INFO_REC;
287 l_tax_profile_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
288 l_zx_registration_rec ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC;
289 l_jurisdiction_code ZX_JURISDICTIONS_B.TAX_JURISDICTION_CODE%TYPE;
290 l_ret_record_level VARCHAR2(30);
291 l_location_id number;
292 l_zone_id NUMBER;
293 l_zone_name VARCHAR2(360);
294 l_msg_count NUMBER;
295 j NUMBER;
296
297 BEGIN
298 g_level_statement := FND_LOG.LEVEL_STATEMENT;
299 g_level_unexpected := FND_LOG.LEVEL_UNEXPECTED;
300 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
301
302 IF (g_level_statement >= g_current_runtime_level ) THEN
303 FND_LOG.STRING(g_level_statement,
304 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_conditions.BEGIN',
305 'ZX_SIM_CONDITIONS_PKG: create_sim_conditions(+)');
306 END IF;
307
308 p_return_status := FND_API.G_RET_STS_SUCCESS;
309
310 FOR l_trxhdr_rec IN c_trxhdr
311 Loop
312 FOR l_trxlines_rec IN c_trxlines(l_trxhdr_rec.trx_id)
313 Loop
314 FOR l_taxlines_rec IN c_taxlines
315 Loop
316 -- If the conditions are already simulated (or customized using UI), exit
317 open c_sim_cond (l_trxlines_rec.trx_line_id,
318 l_taxlines_rec.summary_tax_line_number);
319 fetch c_sim_cond into l_cond_cnt;
320 close c_sim_cond;
321 If nvl(l_cond_cnt,0) > 0 then
322 IF (g_level_statement >= g_current_runtime_level ) THEN
323 FND_LOG.STRING(g_level_statement,
324 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_conditions',
325 'Conditions are already simulated');
326 END IF;
327
328 exit;
329 End If;
330
331 -- Fetch All Determining Factors which are enabled to be defined in Rules
332 -- and insert them into simulate conditions.
333 open c_detfactors;
334 fetch c_detfactors bulk collect into
335 pr_detfactor_class_tab,
336 pr_detfactor_cq_tab,
337 pr_detfactor_code_tab,
338 pr_parameter_code_tab,
339 pr_operator_tab,
340 pr_datatype_tab,
341 pr_numeric_value_tab,
342 pr_alpha_value_tab,
343 pr_date_value_tab,
344 pr_value_low_tab,
345 pr_value_high_tab;
346
347 FOR i in 1..nvl(pr_detfactor_class_tab.last,0)
348 Loop
349 -- Assign value derived to following structure based on data type:
350 -- Number - pr_numeric_value_tab, Alphanumeric - pr_alpha_value_tab
351 -- Date - pr_date_value_tab, Low Value - pr_value_low_tab
352 -- High Value - pr_value_high_tab
353
354 If pr_detfactor_class_tab(i) = 'TRX_INPUT_FACTOR' Then
355
356 IF (g_level_statement >= g_current_runtime_level ) THEN
357 FND_LOG.STRING(g_level_statement,
358 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_conditions',
359 'Fetching and Assigning Values for Transaction Input Factors');
360 END IF;
361
362 If pr_detfactor_code_tab(i) = 'INTENDED_USE' Then
363 pr_alpha_value_tab(i) := l_trxlines_rec.line_intended_use;
364 ElsIf pr_detfactor_code_tab(i) = 'PRODUCT_FISCAL_CLASSIFICATION' Then
365 pr_alpha_value_tab(i) := l_trxlines_rec.product_fisc_classification;
366 ElsIf pr_detfactor_code_tab(i) = 'USER_DEFINED_FISC_CLASS' Then
367 pr_alpha_value_tab(i) := l_trxlines_rec.user_defined_fisc_class;
368 ElsIf pr_detfactor_code_tab(i) = 'INPUT_TAX_CLASSIFICATION_CODE' Then
369 pr_alpha_value_tab(i) := l_trxlines_rec.input_tax_classification_code;
370 ElsIf pr_detfactor_code_tab(i) = 'OUTPUT_TAX_CLASSIFICATION_CODE' Then
371 pr_alpha_value_tab(i) := l_trxlines_rec.output_tax_classification_code;
372 ElsIf pr_detfactor_code_tab(i) = 'REF_DOC_EVENT_CLASS_CODE' Then
373 pr_alpha_value_tab(i) := l_trxlines_rec.ref_doc_event_class_code;
374 End If;
375
376 If pr_alpha_value_tab(i) IS NULL AND
377 pr_numeric_value_tab(i) IS NULL AND
378 pr_date_value_tab(i) IS NULL AND
379 pr_value_low_tab(i) IS NULL THEN
380 pr_detfactor_class_tab(i) := NULL;
381 End If;
382 End If;
383 End Loop;
384
385 IF (g_level_statement >= g_current_runtime_level ) THEN
386 FND_LOG.STRING(g_level_statement,
387 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_conditions',
388 'Insert All Simulated Conditions containing derived values');
389 END IF;
390
391 FORALL j IN pr_detfactor_class_tab.first..pr_detfactor_class_tab.last
392 INSERT ALL
393 WHEN pr_detfactor_class_tab(j) IS NOT NULL Then
394 INTO zx_sim_conditions (
395 sim_condition_id,
396 determining_factor_class_code,
397 determining_factor_cq_code,
398 determining_factor_code,
399 tax_parameter_code,
400 operator_code,
401 data_type_code,
402 numeric_value,
403 alphanumeric_value,
404 date_value,
405 value_low,
406 value_high,
407 trx_line_id,
408 trx_id,
409 tax_line_number,
410 created_by,
411 creation_date,
412 last_updated_by,
413 last_update_date)
414 VALUES (zx_sim_conditions_s.nextval,
415 determining_factor_class_code,
416 determining_factor_cq_code,
417 determining_factor_code,
418 tax_parameter_code,
419 operator_code,
420 data_type_code,
421 numeric_value,
422 alphanumeric_value,
423 date_value,
424 value_low,
425 value_high,
426 trx_line_id,
427 trx_id,
428 tax_line_number,
429 fnd_global.user_id,
430 sysdate,
431 fnd_global.user_id,
432 sysdate)
433 Select pr_detfactor_class_tab(j) determining_factor_class_code,
434 pr_detfactor_cq_tab(j) determining_factor_cq_code,
435 pr_detfactor_code_tab(j) determining_factor_code,
436 pr_parameter_code_tab(j) tax_parameter_code,
437 pr_operator_tab(j) operator_code,
438 pr_datatype_tab(j) data_type_code,
439 pr_numeric_value_tab(j) numeric_value,
440 pr_alpha_value_tab(j) alphanumeric_value,
441 pr_date_value_tab(j) date_value,
442 pr_value_low_tab(j) value_low,
443 pr_value_high_tab(j) value_high,
444 l_trxlines_rec.trx_line_id trx_line_id,
445 l_trxlines_rec.trx_id trx_id,
446 l_taxlines_rec.summary_tax_line_number tax_line_number
447 From dual;
448
449 If c_detfactors%isopen Then
450 close c_detfactors;
451 End If;
452 End Loop; -- Loop for Tax Lines
453 End Loop; -- Loop for Trx Lines
454 End Loop; -- Loop for Trx Header
455
456 IF (g_level_statement >= g_current_runtime_level ) THEN
457 FND_LOG.STRING(g_level_statement,
458 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_conditions.END',
459 'ZX_SIM_CONDITIONS_PKG: create_sim_conditions(-)');
460 END IF;
461
462 commit;
463
464 EXCEPTION
465 WHEN OTHERS THEN
466 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
468
469 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
470 FND_MSG_PUB.Add;
471
472 IF (g_level_unexpected >= g_current_runtime_level ) THEN
473 FND_LOG.STRING(g_level_unexpected,
474 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_conditions',
475 p_error_buffer);
476 END IF;
477
478 END create_sim_conditions;
479
480 -- This procedure creates simulated Rules based on Simulated Conditions.
481 PROCEDURE create_sim_rules (p_trx_id IN number,
482 p_trxline_id IN number,
483 p_taxline_number IN number,
484 p_content_owner_id IN number,
485 p_application_id IN number,
486 p_tax_regime_code IN varchar2,
487 p_tax IN varchar2,
488 p_tax_status_code IN varchar2,
489 p_rate_code IN varchar2,
490 p_return_status OUT NOCOPY varchar2,
491 p_error_buffer OUT NOCOPY varchar2) AS
492
493 l_rule_id NUMBER;
494 l_cg_id NUMBER;
495
496 BEGIN
497 g_level_statement := FND_LOG.LEVEL_STATEMENT;
498 g_level_unexpected := FND_LOG.LEVEL_UNEXPECTED;
499 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
500
501 IF (g_level_statement >= g_current_runtime_level ) THEN
502 FND_LOG.STRING(g_level_statement,
503 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules.BEGIN',
504 'ZX_SIM_CONDITIONS_PKG: create_sim_rules(+)');
505 END IF;
506
507 p_return_status := FND_API.G_RET_STS_SUCCESS;
508
512 'Delete any existing Simulated Rules, Process Results and Conditions');
509 IF (g_level_statement >= g_current_runtime_level ) THEN
510 FND_LOG.STRING(g_level_statement,
511 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
513 END IF;
514 DELETE zx_sim_rules_b;
515 DELETE zx_sim_rules_tl;
516 DELETE zx_sim_process_results;
517 DELETE zx_sim_rule_conditions;
518
519 IF (g_level_statement >= g_current_runtime_level ) THEN
520 FND_LOG.STRING(g_level_statement,
521 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
522 'Simulate Rules, Process Results and Conditions from existing Setup');
523 END IF;
524 create_from_existing_rules (p_tax_regime_code,
525 p_tax,
526 p_content_owner_id,
527 p_application_id,
528 p_return_status,
529 p_error_buffer);
530
531 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
532 IF (g_level_statement >= g_current_runtime_level ) THEN
533 FND_LOG.STRING(g_level_statement,
534 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
535 'Unable to Create Simulated Rules from Existing Setup');
536 p_return_status := FND_API.G_RET_STS_ERROR;
537 p_error_buffer := 'Unable to Create Simulated Rules from Existing Setup';
538 END IF;
539
540 return;
541 END IF;
542
543 IF (g_level_statement >= g_current_runtime_level ) THEN
544 FND_LOG.STRING(g_level_statement,
545 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
546 'Simulate Rules and Process Results for Applicability');
547 END IF;
548
549 Select zx_rules_b_s.nextval,
550 zx_condition_groups_b_s.nextval
551 Into l_rule_id,
552 l_cg_id
553 From Dual;
554 INSERT ALL
555 WHEN (1=1) Then
556 INTO zx_sim_rules_b (
557 sim_tax_rule_id,
558 content_owner_id,
559 tax_rule_code,
560 tax,
561 tax_regime_code,
562 service_type_code,
563 priority,
564 det_factor_templ_code,
565 effective_from,
566 simulated_flag,
567 created_by,
568 creation_date,
569 last_updated_by,
570 last_update_date,
571 application_id)
572 VALUES (sim_tax_rule_id,
573 p_content_owner_id,
574 'R_SIMAP_' || to_char(l_rule_id),
575 p_tax,
576 p_tax_regime_code,
577 'DET_APPLICABLE_TAXES',
578 l_rule_id,
579 'T_SIMAP_' || to_char(zx_det_factor_templ_b_s.nextval),
580 sysdate,
581 'Y',
582 created_by,
583 creation_date,
584 last_updated_by,
585 last_update_date,
586 p_application_id)
587 WHEN (1=1) Then
588 INTO zx_sim_process_results (
589 sim_result_id,
590 sim_tax_rule_id,
591 condition_group_code,
592 priority,
593 result_type_code,
594 alphanumeric_result,
595 created_by,
596 creation_date,
597 last_updated_by,
598 last_update_date)
599 VALUES (zx_process_results_s.nextval,
600 sim_tax_rule_id,
601 'G_SIMAP_' || to_char(l_cg_id),
602 1,
603 'CODE',
604 'APPLICABLE',
605 created_by,
606 creation_date,
607 last_updated_by,
608 last_update_date)
609 SELECT l_rule_id sim_tax_rule_id,
610 fnd_global.user_id created_by,
611 sysdate creation_date,
612 fnd_global.user_id last_updated_by,
613 sysdate last_update_date
614 FROM dual
615 WHERE exists (Select 1
616 from zx_sim_conditions
617 where applicability_flag='Y');
618
619 IF (g_level_statement >= g_current_runtime_level ) THEN
620 FND_LOG.STRING(g_level_statement,
621 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
622 'Simulate Rule Conditions for Applicability');
623 END IF;
624
625 INSERT INTO zx_sim_rule_conditions (
629 determining_factor_cq_code,
626 simrule_condition_id,
627 condition_group_code,
628 determining_factor_class_code,
630 determining_factor_code,
631 tax_parameter_code,
632 operator_code,
633 data_type_code,
634 numeric_value,
635 alphanumeric_value,
636 date_value,
637 value_low,
638 value_high,
639 ignore_flag,
640 simulated_flag,
641 created_by,
642 creation_date,
643 last_updated_by,
644 last_update_date)
645 SELECT zx_conditions_s.nextval,
646 'G_SIMAP_' || to_char(l_cg_id) condition_group_code,
647 determining_factor_class_code,
648 determining_factor_cq_code,
649 determining_factor_code,
650 tax_parameter_code,
651 operator_code,
652 data_type_code,
653 numeric_value,
654 alphanumeric_value,
655 date_value,
656 value_low,
657 value_high,
658 'N' ignore_flag,
659 'Y' simulated_flag,
660 fnd_global.user_id created_by,
661 sysdate creation_date,
662 fnd_global.user_id last_updated_by,
663 sysdate last_update_date
664 FROM zx_sim_conditions
665 WHERE trx_id = p_trx_id
666 and trx_line_id = p_trxline_id
667 and tax_line_number = p_taxline_number
668 and NVL(applicability_flag,'N') = 'Y';
669
670 IF (g_level_statement >= g_current_runtime_level ) THEN
671 FND_LOG.STRING(g_level_statement,
672 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
673 'Simulate Rules and Process Results for Status Determination');
674 END IF;
675 Select zx_rules_b_s.nextval,
676 zx_condition_groups_b_s.nextval
677 Into l_rule_id,
678 l_cg_id
679 From Dual;
680
681 INSERT ALL
682 WHEN (1=1) Then
683 INTO zx_sim_rules_b (
684 sim_tax_rule_id,
685 content_owner_id,
686 tax_rule_code,
687 tax,
688 tax_regime_code,
689 service_type_code,
690 priority,
691 det_factor_templ_code,
692 effective_from,
693 simulated_flag,
694 created_by,
695 creation_date,
696 last_updated_by,
697 last_update_date,
698 application_id)
699 VALUES (sim_tax_rule_id,
700 p_content_owner_id,
701 'R_SIMST_' || to_char(l_rule_id),
702 p_tax,
703 p_tax_regime_code,
704 'DET_TAX_STATUS',
705 l_rule_id,
709 created_by,
706 'T_SIMST_' || to_char(zx_det_factor_templ_b_s.nextval),
707 sysdate,
708 'Y',
710 creation_date,
711 last_updated_by,
712 last_update_date,
713 p_application_id)
714 WHEN (1=1) Then
715 INTO zx_sim_process_results (
716 sim_result_id,
717 sim_tax_rule_id,
718 condition_group_code,
719 priority,
720 result_type_code,
721 alphanumeric_result,
722 created_by,
723 creation_date,
724 last_updated_by,
725 last_update_date)
726 VALUES (zx_process_results_s.nextval,
727 sim_tax_rule_id,
728 'G_SIMST_' || to_char(l_cg_id),
729 1,
730 'CODE',
731 p_tax_status_code,
732 created_by,
733 creation_date,
734 last_updated_by,
735 last_update_date)
736 SELECT l_rule_id sim_tax_rule_id,
737 fnd_global.user_id created_by,
738 sysdate creation_date,
739 fnd_global.user_id last_updated_by,
740 sysdate last_update_date
741 FROM dual
742 WHERE exists (Select 1
743 from zx_sim_conditions
744 where status_determine_flag='Y');
745
746 IF (g_level_statement >= g_current_runtime_level ) THEN
747 FND_LOG.STRING(g_level_statement,
748 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
749 'Simulate Rule Conditions for Status Determination');
750 END IF;
751
752 INSERT INTO zx_sim_rule_conditions (
753 simrule_condition_id,
754 condition_group_code,
755 determining_factor_class_code,
756 determining_factor_cq_code,
757 determining_factor_code,
758 tax_parameter_code,
759 operator_code,
760 data_type_code,
761 numeric_value,
762 alphanumeric_value,
763 date_value,
764 value_low,
765 value_high,
766 ignore_flag,
767 simulated_flag)
768 SELECT zx_conditions_s.nextval,
769 'G_SIMST_' || to_char(l_cg_id) condition_group_code,
770 determining_factor_class_code,
771 determining_factor_cq_code,
772 determining_factor_code,
773 tax_parameter_code,
774 operator_code,
775 data_type_code,
776 numeric_value,
777 alphanumeric_value,
778 date_value,
779 value_low,
780 value_high,
781 'N' ignore_flag,
782 'Y' simulated_flag
783 FROM zx_sim_conditions
784 WHERE trx_id = p_trx_id
785 and trx_line_id = p_trxline_id
786 and tax_line_number = p_taxline_number
787 and NVL(status_determine_flag,'N') = 'Y';
788
789 IF (g_level_statement >= g_current_runtime_level ) THEN
790 FND_LOG.STRING(g_level_statement,
791 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
792 'Simulate Rules and Process Results for Rate Determination');
793 END IF;
794
795 Select zx_rules_b_s.nextval,
796 zx_condition_groups_b_s.nextval
797 Into l_rule_id,
798 l_cg_id
799 From Dual;
800 INSERT ALL
801 WHEN (1=1) Then
802 INTO zx_sim_rules_b (
803 sim_tax_rule_id,
804 content_owner_id,
805 tax_rule_code,
806 tax,
807 tax_regime_code,
808 service_type_code,
809 priority,
810 det_factor_templ_code,
811 effective_from,
812 simulated_flag,
813 created_by,
814 creation_date,
815 last_updated_by,
816 last_update_date,
817 application_id)
818 VALUES (sim_tax_rule_id,
819 p_content_owner_id,
820 'R_SIMRT_' || to_char(l_rule_id),
821 p_tax,
822 p_tax_regime_code,
823 'DET_TAX_RATE',
824 l_rule_id,
825 'T_SIMRT_' || to_char(zx_det_factor_templ_b_s.nextval),
826 sysdate,
827 'Y',
828 created_by,
829 creation_date,
830 last_updated_by,
831 last_update_date,
835 sim_result_id,
832 p_application_id)
833 WHEN (1=1) Then
834 INTO zx_sim_process_results (
836 sim_tax_rule_id,
837 condition_group_code,
838 priority,
839 tax_status_code,
840 result_type_code,
841 alphanumeric_result,
842 created_by,
843 creation_date,
844 last_updated_by,
845 last_update_date)
846 VALUES (zx_process_results_s.nextval,
847 sim_tax_rule_id,
848 'G_SIMRT_' || to_char(l_cg_id),
849 1,
850 p_tax_status_code,
851 'CODE',
852 p_rate_code,
853 created_by,
854 creation_date,
855 last_updated_by,
856 last_update_date)
857 SELECT l_rule_id sim_tax_rule_id,
858 fnd_global.user_id created_by,
859 sysdate creation_date,
860 fnd_global.user_id last_updated_by,
861 sysdate last_update_date
862 FROM dual
863 WHERE exists (Select 1
864 from zx_sim_conditions
865 where rate_determine_flag='Y');
866
867 IF (g_level_statement >= g_current_runtime_level ) THEN
868 FND_LOG.STRING(g_level_statement,
869 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
870 'Simulate Rule Conditions for Rate Determination');
871 END IF;
872
873 INSERT INTO zx_sim_rule_conditions (
874 simrule_condition_id,
875 condition_group_code,
876 determining_factor_class_code,
877 determining_factor_cq_code,
878 determining_factor_code,
879 tax_parameter_code,
880 operator_code,
881 data_type_code,
882 numeric_value,
883 alphanumeric_value,
884 date_value,
885 value_low,
886 value_high,
887 ignore_flag,
888 simulated_flag)
889 SELECT zx_conditions_s.nextval,
890 'G_SIMRT_' || to_char(l_cg_id) condition_group_code,
891 determining_factor_class_code,
892 determining_factor_cq_code,
893 determining_factor_code,
894 tax_parameter_code,
895 operator_code,
896 data_type_code,
897 numeric_value,
898 alphanumeric_value,
899 date_value,
900 value_low,
901 value_high,
902 'N' ignore_flag,
903 'Y' simulated_flag
904 FROM zx_sim_conditions
905 WHERE trx_id = p_trx_id
906 and trx_line_id = p_trxline_id
907 and tax_line_number = p_taxline_number
908 and NVL(rate_determine_flag,'N') = 'Y';
909
910 IF (g_level_statement >= g_current_runtime_level ) THEN
911 FND_LOG.STRING(g_level_statement,
912 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
913 'Simulate Translation of Rules');
914 END IF;
915
916 INSERT INTO zx_sim_rules_tl (
917 language,
918 source_lang,
919 tax_rule_name,
920 sim_tax_rule_id,
921 creation_date,
922 created_by,
923 last_update_date,
924 last_updated_by)
925 select l.language_code,
926 userenv('LANG'),
927 b.tax_rule_code,
928 b.sim_tax_rule_id,
929 sysdate creation_date,
930 fnd_global.user_id created_by,
931 sysdate last_update_date,
932 fnd_global.user_id last_updated_by
933 from fnd_languages l,
934 zx_sim_rules_b b
935 where l.installed_flag in ('I', 'B')
936 and not exists
937 (select NULL
938 from zx_sim_rules_tl t
939 where t.sim_tax_rule_id = b.sim_tax_rule_id
940 and t.language = l.language_code);
941
942 IF (g_level_statement >= g_current_runtime_level ) THEN
943 FND_LOG.STRING(g_level_statement,
944 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules.END',
945 'ZX_SIM_CONDITIONS_PKG: create_sim_rules(-)');
946 END IF;
947
948 commit;
949
950 EXCEPTION
951 WHEN OTHERS THEN
952 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
953 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
954
955 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
956 FND_MSG_PUB.Add;
957
958 IF (g_level_unexpected >= g_current_runtime_level ) THEN
959 FND_LOG.STRING(g_level_unexpected,
960 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
961 p_error_buffer);
962 END IF;
963
964 END create_sim_rules;
965
966 PROCEDURE create_rules (p_return_status OUT NOCOPY VARCHAR2,
967 p_error_buffer OUT NOCOPY VARCHAR2) AS
968
969 CURSOR c_condgroups IS
973 GROUP BY condition_group_code
970 SELECT condition_group_code
971 FROM zx_sim_rule_conditions
972 WHERE simulated_flag = 'Y'
974 HAVING count(*) <= 10;
975
976 CURSOR c_conditions(c_group_code varchar2) IS
977 SELECT determining_factor_class_code,
978 determining_factor_cq_code,
979 determining_factor_code,
980 data_type_code,
981 operator_code,
982 tax_parameter_code,
983 numeric_value,
984 date_value,
985 alphanumeric_value,
986 value_low,
987 value_high
988 FROM zx_sim_rule_conditions
989 WHERE condition_group_code = c_group_code
990 AND ignore_flag='N';
991
992 l_cond_cnt NUMBER;
993
994 BEGIN
995 g_level_statement := FND_LOG.LEVEL_STATEMENT;
996 g_level_unexpected := FND_LOG.LEVEL_UNEXPECTED;
997 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
998
999 IF (g_level_statement >= g_current_runtime_level ) THEN
1000 FND_LOG.STRING(g_level_statement,
1001 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_rules.BEGIN',
1002 'ZX_SIM_CONDITIONS_PKG: create_rules(+)');
1003 END IF;
1004
1005 p_return_status := FND_API.G_RET_STS_SUCCESS;
1006
1007 IF (g_level_statement >= g_current_runtime_level ) THEN
1008 FND_LOG.STRING(g_level_statement,
1009 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1010 'Create Template Header Setup');
1011 END IF;
1012
1013 INSERT ALL WHEN (1=1) Then
1014 INTO zx_det_factor_templ_b (
1015 det_factor_templ_id,
1016 det_factor_templ_code,
1017 template_usage_code,
1018 record_type_code,
1019 created_by,
1020 creation_date,
1021 last_updated_by,
1022 last_update_date)
1023 VALUES (to_number(substr(det_factor_templ_code,9)),
1024 det_factor_templ_code,
1025 'TAX_RULES',
1026 'USER_DEFINED',
1027 fnd_global.user_id,
1028 sysdate,
1029 fnd_global.user_id,
1030 sysdate)
1031 SELECT det_factor_templ_code
1032 FROM zx_sim_rules_b
1033 WHERE simulated_flag = 'Y'
1034 GROUP BY det_factor_templ_code;
1035
1036 IF (g_level_statement >= g_current_runtime_level ) THEN
1037 FND_LOG.STRING(g_level_statement,
1038 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1039 'Create Translation for Template Header Setup');
1040 END IF;
1041
1042 INSERT INTO zx_det_factor_templ_tl (
1043 language,
1044 source_lang,
1045 det_factor_templ_name,
1046 det_factor_templ_id,
1047 creation_date,
1048 created_by,
1049 last_update_date,
1050 last_updated_by)
1051 select l.language_code,
1052 userenv('LANG'),
1053 b.det_factor_templ_code,
1054 b.det_factor_templ_id,
1055 sysdate creation_date,
1056 fnd_global.user_id created_by,
1057 sysdate last_update_date,
1058 fnd_global.user_id last_updated_by
1059 from fnd_languages l,
1060 zx_det_factor_templ_b b
1061 where l.installed_flag in ('I', 'B')
1062 and exists (Select NULL
1063 From zx_sim_rules_b s
1064 Where s.det_factor_templ_code = b.det_factor_templ_code
1065 And s.simulated_flag = 'Y')
1066 and not exists (Select NULL
1067 From zx_det_factor_templ_tl t
1068 Where t.det_factor_templ_id = b.det_factor_templ_id
1069 And t.language = l.language_code);
1070
1071 IF (g_level_statement >= g_current_runtime_level ) THEN
1072 FND_LOG.STRING(g_level_statement,
1073 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1074 'Create Template Details Setup');
1075 END IF;
1076
1077 INSERT ALL WHEN (1=1) Then
1078 INTO zx_det_factor_templ_dtl (
1079 det_factor_templ_dtl_id,
1080 det_factor_templ_id,
1081 determining_factor_class_code,
1082 determining_factor_cq_code,
1083 determining_factor_code,
1084 required_flag,
1085 record_type_code,
1086 created_by,
1087 creation_date,
1088 last_updated_by,
1089 last_update_date)
1090 VALUES (zx_det_factor_templ_dtl_s.nextval,
1091 det_factor_templ_id,
1092 determining_factor_class_code,
1093 determining_factor_cq_code,
1094 determining_factor_code,
1095 required_flag,
1096 'USER_DEFINED',
1097 fnd_global.user_id,
1098 sysdate,
1099 fnd_global.user_id,
1100 sysdate)
1101 SELECT to_number(substr(rule.det_factor_templ_code,9)) det_factor_templ_id,
1102 determining_factor_class_code,
1103 determining_factor_cq_code,
1104 determining_factor_code,
1105 decode(ignore_flag,'N','Y','N') required_flag
1106 FROM zx_sim_rule_conditions cond,
1107 (select det_factor_templ_code,
1108 condition_group_code
1109 from zx_sim_process_results p,
1110 zx_sim_rules_b r
1111 where p.sim_tax_rule_id = r.sim_tax_rule_id
1112 group by condition_group_code, det_factor_templ_code) rule
1113 WHERE cond.condition_group_code = rule.condition_group_code
1114 AND simulated_flag = 'Y';
1115
1116 IF (g_level_statement >= g_current_runtime_level ) THEN
1117 FND_LOG.STRING(g_level_statement,
1118 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1119 'Create Condition Groups Setup without Conditions');
1120 END IF;
1121
1122 INSERT ALL WHEN (1=1) Then
1123 INTO zx_condition_groups_b (
1124 condition_group_id,
1125 condition_group_code,
1126 det_factor_templ_code,
1127 record_type_code,
1128 more_than_max_cond_flag,
1129 enabled_flag,
1130 created_by,
1131 creation_date,
1132 last_updated_by,
1133 last_update_date)
1134 VALUES (to_number(substr(condition_group_code,9)),
1135 condition_group_code,
1136 det_factor_templ_code,
1137 'USER_DEFINED',
1138 'Y',
1139 'Y',
1140 fnd_global.user_id,
1141 sysdate,
1142 fnd_global.user_id,
1143 sysdate)
1144 SELECT condition_group_code,
1145 det_factor_templ_code
1146 FROM zx_sim_rules_b rule,
1147 zx_sim_process_results result
1148 WHERE result.sim_tax_rule_id = rule.sim_tax_rule_id
1149 AND result.simulated_flag = 'Y'
1150 GROUP BY condition_group_code, det_factor_templ_code;
1154 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1151
1152 IF (g_level_statement >= g_current_runtime_level ) THEN
1153 FND_LOG.STRING(g_level_statement,
1155 'Create Translation for Condition Groups Setup');
1156 END IF;
1157
1158 INSERT INTO zx_condition_groups_tl (
1159 language,
1160 source_lang,
1161 condition_group_name,
1162 condition_group_id,
1163 creation_date,
1164 created_by,
1165 last_update_date,
1166 last_updated_by)
1167 select l.language_code,
1168 userenv('LANG'),
1169 b.condition_group_code,
1170 b.condition_group_id,
1171 sysdate creation_date,
1172 fnd_global.user_id created_by,
1173 sysdate last_update_date,
1174 fnd_global.user_id last_updated_by
1175 from fnd_languages l,
1176 zx_condition_groups_b b
1177 where l.installed_flag in ('I', 'B')
1178 and exists (Select NULL
1179 From zx_sim_process_results s
1180 Where s.condition_group_code = b.condition_group_code
1181 And s.simulated_flag = 'Y')
1182 and not exists (Select NULL
1183 From zx_condition_groups_tl t
1184 Where t.condition_group_id = b.condition_group_id
1185 And t.language = l.language_code);
1186
1187 IF (g_level_statement >= g_current_runtime_level ) THEN
1188 FND_LOG.STRING(g_level_statement,
1189 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1190 'Update Condition Groups Setup with conditions having 10 or fewer Conditions');
1191 END IF;
1192
1193 FOR i IN c_condgroups
1194 LOOP
1195 l_cond_cnt := 1;
1196 FOR j IN c_conditions (i.condition_group_code)
1197 LOOP
1198 IF l_cond_cnt = 1 THEN
1199 UPDATE zx_condition_groups_b
1200 SET more_than_max_cond_flag = 'N',
1201 determining_factor_class1_code = j.determining_factor_class_code,
1202 determining_factor_cq1_code = j.determining_factor_cq_code,
1203 determining_factor_code1 = j.determining_factor_code,
1204 tax_parameter_code1 = j.tax_parameter_code,
1205 data_type1_code = j.data_type_code,
1206 operator1_code = j.operator_code,
1207 numeric_value1 = j.numeric_value,
1208 date_value1 = j.date_value,
1209 alphanumeric_value1 = j.alphanumeric_value,
1210 value_low1 = j.value_low,
1211 value_high1 = j.value_high
1212 WHERE condition_group_code = i.condition_group_code;
1213 l_cond_cnt := l_cond_cnt + 1;
1214 ELSIF l_cond_cnt = 2 THEN
1215 UPDATE zx_condition_groups_b
1216 SET determining_factor_class2_code = j.determining_factor_class_code,
1217 determining_factor_cq2_code = j.determining_factor_cq_code,
1218 determining_factor_code2 = j.determining_factor_code,
1219 tax_parameter_code2 = j.tax_parameter_code,
1220 data_type2_code = j.data_type_code,
1221 operator2_code = j.operator_code,
1222 numeric_value2 = j.numeric_value,
1223 date_value2 = j.date_value,
1224 alphanumeric_value2 = j.alphanumeric_value,
1225 value_low2 = j.value_low,
1226 value_high2 = j.value_high
1227 WHERE condition_group_code = i.condition_group_code;
1228 l_cond_cnt := l_cond_cnt + 1;
1229 ELSIF l_cond_cnt = 3 THEN
1230 UPDATE zx_condition_groups_b
1231 SET determining_factor_class3_code = j.determining_factor_class_code,
1232 determining_factor_cq3_code = j.determining_factor_cq_code,
1233 determining_factor_code3 = j.determining_factor_code,
1234 tax_parameter_code3 = j.tax_parameter_code,
1235 data_type3_code = j.data_type_code,
1236 operator3_code = j.operator_code,
1237 numeric_value3 = j.numeric_value,
1238 date_value3 = j.date_value,
1239 alphanumeric_value3 = j.alphanumeric_value,
1240 value_low3 = j.value_low,
1241 value_high3 = j.value_high
1242 WHERE condition_group_code = i.condition_group_code;
1243 l_cond_cnt := l_cond_cnt + 1;
1244 ELSIF l_cond_cnt = 4 THEN
1245 UPDATE zx_condition_groups_b
1246 SET determining_factor_class4_code = j.determining_factor_class_code,
1247 determining_factor_cq4_code = j.determining_factor_cq_code,
1248 determining_factor_code4 = j.determining_factor_code,
1249 tax_parameter_code4 = j.tax_parameter_code,
1250 data_type4_code = j.data_type_code,
1251 operator4_code = j.operator_code,
1252 numeric_value4 = j.numeric_value,
1253 date_value4 = j.date_value,
1254 alphanumeric_value4 = j.alphanumeric_value,
1260 UPDATE zx_condition_groups_b
1255 value_low4 = j.value_low,
1256 value_high4 = j.value_high
1257 WHERE condition_group_code = i.condition_group_code;
1258 l_cond_cnt := l_cond_cnt + 1;
1259 ELSIF l_cond_cnt = 5 THEN
1261 SET determining_factor_class5_code = j.determining_factor_class_code,
1262 determining_factor_cq5_code = j.determining_factor_cq_code,
1263 determining_factor_code5 = j.determining_factor_code,
1264 tax_parameter_code5 = j.tax_parameter_code,
1265 data_type5_code = j.data_type_code,
1266 operator5_code = j.operator_code,
1267 numeric_value5 = j.numeric_value,
1268 date_value5 = j.date_value,
1269 alphanumeric_value5 = j.alphanumeric_value,
1270 value_low5 = j.value_low,
1271 value_high5 = j.value_high
1272 WHERE condition_group_code = i.condition_group_code;
1273 l_cond_cnt := l_cond_cnt + 1;
1274 ELSIF l_cond_cnt = 6 THEN
1275 UPDATE zx_condition_groups_b
1276 SET determining_factor_class6_code = j.determining_factor_class_code,
1277 determining_factor_cq6_code = j.determining_factor_cq_code,
1278 determining_factor_code6 = j.determining_factor_code,
1279 tax_parameter_code6 = j.tax_parameter_code,
1280 data_type6_code = j.data_type_code,
1281 operator6_code = j.operator_code,
1282 numeric_value6 = j.numeric_value,
1283 date_value6 = j.date_value,
1284 alphanumeric_value6 = j.alphanumeric_value,
1285 value_low6 = j.value_low,
1286 value_high6 = j.value_high
1287 WHERE condition_group_code = i.condition_group_code;
1288 l_cond_cnt := l_cond_cnt + 1;
1289 ELSIF l_cond_cnt = 7 THEN
1290 UPDATE zx_condition_groups_b
1291 SET determining_factor_class7_code = j.determining_factor_class_code,
1292 determining_factor_cq7_code = j.determining_factor_cq_code,
1293 determining_factor_code7 = j.determining_factor_code,
1294 tax_parameter_code7 = j.tax_parameter_code,
1295 data_type7_code = j.data_type_code,
1296 operator7_code = j.operator_code,
1297 numeric_value7 = j.numeric_value,
1298 date_value7 = j.date_value,
1299 alphanumeric_value7 = j.alphanumeric_value,
1300 value_low7 = j.value_low,
1301 value_high7 = j.value_high
1305 UPDATE zx_condition_groups_b
1302 WHERE condition_group_code = i.condition_group_code;
1303 l_cond_cnt := l_cond_cnt + 1;
1304 ELSIF l_cond_cnt = 8 THEN
1306 SET determining_factor_class8_code = j.determining_factor_class_code,
1307 determining_factor_cq8_code = j.determining_factor_cq_code,
1308 determining_factor_code8 = j.determining_factor_code,
1309 tax_parameter_code8 = j.tax_parameter_code,
1310 data_type8_code = j.data_type_code,
1311 operator8_code = j.operator_code,
1312 numeric_value8 = j.numeric_value,
1313 date_value8 = j.date_value,
1314 alphanumeric_value8 = j.alphanumeric_value,
1315 value_low8 = j.value_low,
1316 value_high8 = j.value_high
1317 WHERE condition_group_code = i.condition_group_code;
1318 l_cond_cnt := l_cond_cnt + 1;
1319 ELSIF l_cond_cnt = 9 THEN
1320 UPDATE zx_condition_groups_b
1321 SET determining_factor_class9_code = j.determining_factor_class_code,
1322 determining_factor_cq9_code = j.determining_factor_cq_code,
1323 determining_factor_code9 = j.determining_factor_code,
1324 tax_parameter_code9 = j.tax_parameter_code,
1325 data_type9_code = j.data_type_code,
1326 operator9_code = j.operator_code,
1327 numeric_value9 = j.numeric_value,
1328 date_value9 = j.date_value,
1329 alphanumeric_value9 = j.alphanumeric_value,
1330 value_low9 = j.value_low,
1331 value_high9 = j.value_high
1332 WHERE condition_group_code = i.condition_group_code;
1333 l_cond_cnt := l_cond_cnt + 1;
1334 ELSIF l_cond_cnt = 10 THEN
1335 UPDATE zx_condition_groups_b
1336 SET determining_factor_clas10_code = j.determining_factor_class_code,
1337 determining_factor_cq10_code = j.determining_factor_cq_code,
1338 determining_factor_code10 = j.determining_factor_code,
1339 tax_parameter_code10 = j.tax_parameter_code,
1340 data_type10_code = j.data_type_code,
1341 operator10_code = j.operator_code,
1342 numeric_value10 = j.numeric_value,
1343 date_value10 = j.date_value,
1344 alphanumeric_value10 = j.alphanumeric_value,
1345 value_low10 = j.value_low,
1346 value_high10 = j.value_high
1347 WHERE condition_group_code = i.condition_group_code;
1348 l_cond_cnt := l_cond_cnt + 1;
1349 END IF;
1350 END LOOP;
1351 END LOOP;
1352
1353 IF (g_level_statement >= g_current_runtime_level ) THEN
1354 FND_LOG.STRING(g_level_statement,
1355 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1356 'Create Conditions Setup');
1357 END IF;
1358
1359 INSERT INTO zx_conditions (
1360 condition_id,
1361 condition_group_code,
1362 determining_factor_class_code,
1363 determining_factor_code,
1364 data_type_code,
1365 operator_code,
1366 ignore_flag,
1367 created_by,
1368 creation_date,
1369 last_updated_by,
1370 last_update_date,
1371 tax_parameter_code,
1372 determining_factor_cq_code,
1373 record_type_code,
1374 numeric_value,
1375 date_value,
1376 alphanumeric_value,
1377 value_low,
1378 value_high)
1379 SELECT simrule_condition_id,
1380 condition_group_code,
1381 determining_factor_class_code,
1382 determining_factor_code,
1383 data_type_code,
1384 operator_code,
1385 ignore_flag,
1386 fnd_global.user_id created_by,
1387 sysdate creation_date,
1388 fnd_global.user_id last_updated_by,
1389 sysdate last_update_date,
1390 tax_parameter_code,
1391 determining_factor_cq_code,
1392 'USER_DEFINED',
1393 numeric_value,
1394 date_value,
1398 FROM zx_sim_rule_conditions
1395 alphanumeric_value,
1396 value_low,
1397 value_high
1399 WHERE simulated_flag = 'Y';
1400
1401 IF (g_level_statement >= g_current_runtime_level ) THEN
1402 FND_LOG.STRING(g_level_statement,
1403 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1404 'Create Rules Setup');
1405 END IF;
1406
1407 INSERT INTO zx_rules_b (
1408 tax_rule_id,
1409 content_owner_id,
1410 tax_rule_code,
1411 tax,
1412 tax_regime_code,
1413 service_type_code,
1414 application_id,
1415 priority,
1416 det_factor_templ_code,
1417 effective_from,
1418 enabled_flag,
1419 record_type_code,
1420 created_by,
1421 creation_date,
1422 last_updated_by,
1423 last_update_date)
1424 SELECT sim_tax_rule_id,
1425 content_owner_id,
1426 tax_rule_code,
1427 tax,
1428 tax_regime_code,
1429 service_type_code,
1430 application_id,
1431 priority,
1432 det_factor_templ_code,
1433 effective_from,
1434 'Y',
1435 'USER_DEFINED',
1436 fnd_global.user_id created_by,
1437 sysdate creation_date,
1438 fnd_global.user_id last_updated_by,
1439 sysdate last_update_date
1440 FROM zx_sim_rules_b
1441 WHERE simulated_flag = 'Y';
1442
1443 IF (g_level_statement >= g_current_runtime_level ) THEN
1444 FND_LOG.STRING(g_level_statement,
1445 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1446 'Create Translation for Rules Setup');
1447 END IF;
1448
1449 INSERT INTO zx_rules_tl (
1450 language,
1451 source_lang,
1452 tax_rule_name,
1453 tax_rule_id,
1454 creation_date,
1455 created_by,
1456 last_update_date,
1457 last_updated_by)
1458 select l.language_code,
1459 userenv('LANG'),
1460 b.tax_rule_code,
1461 b.tax_rule_id,
1462 sysdate creation_date,
1463 fnd_global.user_id created_by,
1464 sysdate last_update_date,
1465 fnd_global.user_id last_updated_by
1466 from fnd_languages l,
1467 zx_rules_b b
1468 where l.installed_flag in ('I', 'B')
1469 and exists (Select NULL
1470 From zx_sim_rules_b s
1471 Where s.sim_tax_rule_id = b.tax_rule_id
1472 And s.simulated_flag = 'Y')
1473 and not exists (Select NULL
1474 From zx_rules_tl t
1475 Where t.tax_rule_id = b.tax_rule_id
1476 And t.language = l.language_code);
1477
1478 IF (g_level_statement >= g_current_runtime_level ) THEN
1479 FND_LOG.STRING(g_level_statement,
1480 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1481 'Update Rules Setup for which Priority has been changed in Simulator');
1482 END IF;
1483
1484 UPDATE zx_rules_b
1485 SET priority = (Select sim.priority
1486 From zx_sim_rules_b sim
1487 Where sim.sim_tax_rule_id = zx_rules_b.tax_rule_id)
1488 WHERE tax_rule_id IN (Select sim_tax_rule_id
1489 From zx_sim_rules_b sim
1490 Where sim.priority <> zx_rules_b.priority
1491 And sim.simulated_flag <> 'Y');
1492
1493 IF (g_level_statement >= g_current_runtime_level ) THEN
1494 FND_LOG.STRING(g_level_statement,
1495 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1496 'Create Process Results Setup');
1497 END IF;
1498
1499 INSERT INTO zx_process_results (
1500 result_id,
1501 content_owner_id,
1502 condition_group_id,
1503 condition_group_code,
1504 tax_rule_id,
1505 priority,
1506 result_type_code,
1507 tax_status_code,
1508 numeric_result,
1509 alphanumeric_result,
1510 enabled_flag,
1511 record_type_code,
1512 created_by,
1513 creation_date,
1514 last_updated_by,
1515 last_update_date)
1516 SElECT result.sim_result_id,
1517 rule.content_owner_id,
1518 to_number(substr(result.condition_group_code,9)),
1519 result.condition_group_code,
1520 result.sim_tax_rule_id,
1521 result.priority,
1522 result.result_type_code,
1523 result.tax_status_code,
1524 result.numeric_result,
1525 result.alphanumeric_result,
1526 'Y',
1527 'USER_DEFINED',
1528 fnd_global.user_id created_by,
1529 sysdate creation_date,
1530 fnd_global.user_id last_updated_by,
1531 sysdate last_update_date
1532 FROM zx_sim_rules_b rule,
1533 zx_sim_process_results result
1534 WHERE result.sim_tax_rule_id = rule.sim_tax_rule_id
1535 AND result.simulated_flag = 'Y';
1536
1537 IF (g_level_statement >= g_current_runtime_level ) THEN
1538 FND_LOG.STRING(g_level_statement,
1539 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_sim_rules',
1540 'Update Process Results for which Priority has been changed in Simulator');
1541 END IF;
1542
1543 UPDATE zx_process_results
1544 SET priority = (Select sim.priority
1545 From zx_sim_process_results sim
1546 Where sim.sim_result_id = zx_process_results.result_id)
1547 WHERE result_id IN (Select sim_result_id
1548 From zx_sim_process_results sim
1549 Where sim.priority <> zx_process_results.priority
1550 And sim.simulated_flag <> 'Y');
1551
1552 IF (g_level_statement >= g_current_runtime_level ) THEN
1553 FND_LOG.STRING(g_level_statement,
1554 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_rules.END',
1555 'ZX_SIM_CONDITIONS_PKG: create_rules(-)');
1556 END IF;
1557
1558 commit;
1559
1560
1561 EXCEPTION
1562 WHEN OTHERS THEN
1563 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1564 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1565
1566 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
1567 FND_MSG_PUB.Add;
1568
1569 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1570 FND_LOG.STRING(g_level_unexpected,
1571 'ZX.PLSQL.ZX_SIM_CONDITIONS_PKG.create_rules',
1572 p_error_buffer);
1573 END IF;
1574
1575 END create_rules;
1576
1577 END ZX_SIM_CONDITIONS_PKG;