[Home] [Help]
PACKAGE BODY: APPS.ZX_CONDITIONS_PKG
Source
1 package body ZX_CONDITIONS_PKG as
2 /* $Header: zxdconditionsb.pls 120.5.12020000.2 2013/01/31 06:43:40 srajapar ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.PLSQL.ZX_CONDITIONS_PKG.';
13 -- Logging Infra
14
15 procedure bulk_insert_conditions (
16 X_DETERMINING_FACTOR_CODE IN t_determining_factor_code,
17 X_CONDITION_GROUP_CODE IN t_condition_group_code,
18 X_TAX_PARAMETER_CODE IN t_tax_parameter_code,
19 X_DATA_TYPE_CODE IN t_data_type,
20 X_DETERMINING_FACTOR_CLASS_COD IN t_determining_factor_class,
21 X_DETERMINING_FACTOR_CQ_CODE IN t_determining_factor_cq,
22 X_OPERATOR_CODE IN t_operator,
23 X_RECORD_TYPE_CODE IN t_record_type,
24 X_IGNORE_FLAG IN t_ignore_flg,
25 X_NUMERIC_VALUE IN t_numeric_value,
26 X_DATE_VALUE IN t_date_value,
27 X_ALPHANUMERIC_VALUE IN t_alphanumeric_value,
28 X_VALUE_LOW IN t_value_low,
29 X_VALUE_HIGH IN t_value_high) is
30
31 l_user_id NUMBER;
32 l_conc_login_id NUMBER;
33 l_request_id NUMBER;
34 l_prog_appl_id NUMBER;
35 l_conc_program_id NUMBER;
36
37 begin
38
39 l_user_id := FND_GLOBAL.user_id;
40 l_conc_login_id := FND_GLOBAL.CONC_LOGIN_ID;
41 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
42 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
43 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
44
45 If x_determining_factor_code.count <> 0 then
46 forall i in x_determining_factor_code.first..x_determining_factor_code.last
47 INSERT INTO ZX_CONDITIONS
48 (condition_id,
49 determining_factor_code,
50 condition_group_code,
51 tax_parameter_code,
52 data_type_code,
53 determining_factor_class_code,
54 determining_factor_cq_code,
55 operator_code,
56 record_type_code,
57 ignore_flag,
58 numeric_value,
59 date_value,
60 alphanumeric_value,
61 value_low,
62 value_high,
63 created_by,
64 creation_date,
65 last_updated_by,
66 last_update_date,
67 last_update_login,
68 request_id,
69 program_application_id,
70 program_id,
71 program_login_id)
72 VALUES
73 (zx_conditions_s.nextval,
74 X_DETERMINING_FACTOR_CODE(i),
75 X_CONDITION_GROUP_CODE(i),
76 X_TAX_PARAMETER_CODE(i),
77 X_DATA_TYPE_CODE(i),
78 X_DETERMINING_FACTOR_CLASS_COD(i),
79 X_DETERMINING_FACTOR_CQ_CODE(i),
80 X_OPERATOR_CODE(i),
81 X_RECORD_TYPE_CODE(i),
82 X_IGNORE_FLAG(i),
83 X_NUMERIC_VALUE(i),
84 X_DATE_VALUE(i),
85 X_ALPHANUMERIC_VALUE(i),
86 X_VALUE_LOW(i),
87 X_VALUE_HIGH(i),
88 l_user_id,
89 sysdate,
90 l_user_id,
91 sysdate,
92 l_conc_login_id,
93 l_request_id,
94 l_prog_appl_id,
95 l_conc_program_id,
96 l_conc_login_id
97 );
98
99 end if;
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 APP_EXCEPTION.RAISE_EXCEPTION;
104
105 END BULK_INSERT_CONDITIONS;
106
107
108 PROCEDURE check_dup_tax_conditions
109 (p_det_factor_templ_code IN VARCHAR2
110 ,p_condition_group_id IN NUMBER
111 ,p_condition_id IN NUMBER
112 ,p_det_factor_class_code IN VARCHAR2
113 ,p_det_factor_code IN VARCHAR2
114 ,p_det_factor_cq_code IN VARCHAR2
115 ,p_operator_code IN VARCHAR2
116 ,p_alphanumeric_value IN VARCHAR2
117 ,p_numeric_value IN NUMBER
118 ,p_date_value IN DATE
119 ,p_value_low IN VARCHAR2
120 ,p_value_high IN VARCHAR2
121 ,x_return_status OUT NOCOPY VARCHAR2
122 ,x_msg_data OUT NOCOPY VARCHAR2
123 ,x_condition_group_code OUT NOCOPY VARCHAR2
124 ) IS
125
126 l_miss_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
127 l_miss_number NUMBER := FND_API.G_MISS_NUM;
128 l_miss_date DATE := FND_API.G_MISS_DATE;
129 l_procedure_name VARCHAR2(30) := 'CHECK_DUP_TAX_CONDITIONS';
130
131 BEGIN
132
133 -- Logging Infra: Setting up runtime level
134 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
135 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(G_LEVEL_PROCEDURE,
137 G_MODULE_NAME || l_procedure_name,
138 'Started for '||
139 ': det_factor_templ_code :'||p_det_factor_templ_code ||
140 ': condition_group_id :'||p_condition_group_id ||
141 ': condition_id :'||p_condition_id
142 );
143 END IF;
144 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
145 FND_LOG.STRING(G_LEVEL_PROCEDURE,
146 G_MODULE_NAME || l_procedure_name,
147 ': det_factor_class_code :'||p_det_factor_class_code ||
148 ': det_factor_code :'||p_det_factor_code ||
149 ': det_factor_cq_code :'||p_det_factor_cq_code ||
150 ': operator_code :'||p_operator_code
151 );
152 END IF;
153 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
154 FND_LOG.STRING(G_LEVEL_PROCEDURE,
155 G_MODULE_NAME || l_procedure_name,
156 ': alphanumeric_value :'||p_alphanumeric_value ||
157 ': numeric_value :'||p_numeric_value ||
158 ': date_value :'||p_date_value ||
159 ': value_low :'||p_value_low ||
160 ': value_high :'||p_value_high
161 );
162 END IF;
163 -- bug 16211363 adding determining_factor_cq_code as well for duplicate check
164 -- repeating the query under two clauses to do complete index scan on ZX_CONDITIONS_N1
165 IF p_det_factor_cq_code IS NULL THEN
166 SELECT a.condition_group_code
167 INTO x_condition_group_code
168 FROM zx_condition_groups_b a, zx_conditions b
169 WHERE a.det_factor_templ_code = p_det_factor_templ_code
170 AND a.condition_group_id <> p_condition_group_id
171 AND b.condition_id <> p_condition_id
172 AND b.condition_group_code = a.condition_group_code
173 AND b.determining_factor_code = p_det_factor_code
174 AND b.determining_factor_class_code = p_det_factor_class_code
175 AND b.determining_factor_cq_code IS NULL
176 AND b.operator_code = p_operator_code
177 AND NVL(b.ignore_flag,'N') = 'N'
178 AND NVL(b.ALPHANUMERIC_VALUE,l_miss_char) = NVL(p_alphanumeric_value,l_miss_char)
179 AND NVL(b.NUMERIC_VALUE,l_miss_number) = NVL(p_numeric_value,l_miss_number)
180 AND NVL(b.DATE_VALUE,l_miss_date) = NVL(p_date_value,l_miss_date)
181 AND NVL(b.VALUE_LOW,l_miss_char) = NVL(p_value_low,l_miss_char)
182 AND NVL(b.VALUE_HIGH,l_miss_char) = NVL(p_value_high,l_miss_char)
183 AND rownum = 1;
184 ELSE
185 SELECT a.condition_group_code
186 INTO x_condition_group_code
187 FROM zx_condition_groups_b a, zx_conditions b
188 WHERE a.det_factor_templ_code = p_det_factor_templ_code
189 AND a.condition_group_id <> p_condition_group_id
190 AND b.condition_id <> p_condition_id
191 AND b.condition_group_code = a.condition_group_code
192 AND b.determining_factor_code = p_det_factor_code
193 AND b.determining_factor_class_code = p_det_factor_class_code
194 AND b.determining_factor_cq_code = p_det_factor_cq_code
195 AND b.operator_code = p_operator_code
196 AND NVL(b.ignore_flag,'N') = 'N'
197 AND NVL(b.ALPHANUMERIC_VALUE,l_miss_char) = NVL(p_alphanumeric_value,l_miss_char)
198 AND NVL(b.NUMERIC_VALUE,l_miss_number) = NVL(p_numeric_value,l_miss_number)
199 AND NVL(b.DATE_VALUE,l_miss_date) = NVL(p_date_value,l_miss_date)
200 AND NVL(b.VALUE_LOW,l_miss_char) = NVL(p_value_low,l_miss_char)
201 AND NVL(b.VALUE_HIGH,l_miss_char) = NVL(p_value_high,l_miss_char)
202 AND rownum = 1;
203 END IF;
204 x_return_status := FND_API.G_RET_STS_ERROR;
205 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
206 FND_LOG.STRING(G_LEVEL_PROCEDURE,
207 G_MODULE_NAME || l_procedure_name,
208 'Ended with duplicate code existing at : '||x_condition_group_code);
209 END IF;
210
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 -- success case for us
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
216 FND_LOG.STRING(G_LEVEL_PROCEDURE,
217 G_MODULE_NAME || l_procedure_name,
218 'Ended with NO duplicate code -- VALID record');
219 END IF;
220 WHEN OTHERS THEN
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 x_msg_data := SQLERRM;
223 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
224 FND_LOG.STRING(G_LEVEL_PROCEDURE,
225 G_MODULE_NAME || l_procedure_name,
226 'Unexpected Error '||SQLERRM);
227 END IF;
228
229 END CHECK_DUP_TAX_CONDITIONS;
230
231 END ZX_CONDITIONS_PKG;