[Home] [Help]
PACKAGE BODY: APPS.ZX_SIM_CONDITIONS1_PKG
Source
1 PACKAGE BODY ZX_SIM_CONDITIONS1_PKG AS
2 /* $Header: zxrisimcondspkgb.pls 120.0 2004/06/16 17:44:33 opedrega ship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 PROCEDURE Insert_Row
11 (p_sim_condition_id NUMBER,
12 p_trx_id NUMBER,
13 p_trx_line_id NUMBER,
14 p_tax_line_number NUMBER,
15 p_det_factor_class_code VARCHAR2, --p_determining_factor_class_code
16 p_determining_factor_code VARCHAR2,
17 p_data_type_code VARCHAR2,
18 p_operator_code VARCHAR2,
19 p_tax_parameter_code VARCHAR2,
20 p_determining_factor_cq_code VARCHAR2,
21 p_numeric_value NUMBER,
22 p_date_value DATE,
23 p_alphanumeric_value VARCHAR2,
24 p_value_low VARCHAR2,
25 p_value_high VARCHAR2,
26 p_applicability_flag VARCHAR2,
27 p_status_determine_flag VARCHAR2,
28 p_default_status_code VARCHAR2,
29 p_rate_determine_flag VARCHAR2,
30 p_direct_rate_determine_flag VARCHAR2,
31 p_taxable_basis_determine_flag VARCHAR2,
32 p_calculate_tax_determine_flag VARCHAR2,
33 p_place_of_supply_det_flag VARCHAR2, --p_place_of_supply_determine_flag
34 p_registration_determine_flag VARCHAR2,
35 p_created_by NUMBER,
36 p_creation_date DATE,
37 p_last_updated_by NUMBER,
38 p_last_update_date DATE,
39 p_last_update_login NUMBER) IS
40
41 l_return_status VARCHAR2(1000);
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2(1000);
44
45 BEGIN
46 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
47
48 IF (g_level_procedure >= g_current_runtime_level ) THEN
49 FND_LOG.STRING(g_level_procedure,
50 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
51 'ZX_SIM_RULES_PKG: Insert_Row (+)');
52 END IF;
53
54 INSERT INTO ZX_SIM_CONDITIONS (sim_condition_id,
55 trx_id,
56 trx_line_id,
57 tax_line_number,
58 determining_factor_class_code,
59 determining_factor_code,
60 data_type_code,
61 operator_code,
62 tax_parameter_code,
63 determining_factor_cq_code,
64 numeric_value,
65 date_value,
66 alphanumeric_value,
67 value_low,
68 value_high,
69 applicability_flag,
70 status_determine_flag,
71 default_status_code,
72 rate_determine_flag,
73 direct_rate_determine_flag,
74 taxable_basis_determine_flag,
75 calculate_tax_determine_flag,
76 place_of_supply_determine_flag,
77 registration_determine_flag,
78 created_by,
79 creation_date,
80 last_updated_by,
81 last_update_date,
82 last_update_login)
83 VALUES (p_sim_condition_id,
84 p_trx_id,
85 p_trx_line_id,
86 p_tax_line_number,
87 p_det_factor_class_code,
88 p_determining_factor_code,
89 p_data_type_code,
90 p_operator_code,
91 p_tax_parameter_code,
92 p_determining_factor_cq_code,
93 p_numeric_value,
94 p_date_value,
95 p_alphanumeric_value,
96 p_value_low,
97 p_value_high,
98 p_applicability_flag,
99 p_status_determine_flag,
100 p_default_status_code,
101 p_rate_determine_flag,
102 p_direct_rate_determine_flag,
103 p_taxable_basis_determine_flag,
104 p_calculate_tax_determine_flag,
105 p_place_of_supply_det_flag,
106 p_registration_determine_flag,
107 p_created_by,
108 p_creation_date,
109 p_last_updated_by,
110 p_last_update_date,
111 p_last_update_login);
112
113 IF (g_level_procedure >= g_current_runtime_level ) THEN
114 FND_LOG.STRING(g_level_procedure,
115 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
116 'ZX_SIM_RULES_PKG: Insert_Row (-)');
117 END IF;
118 END Insert_Row;
119
120 PROCEDURE Update_Row
121 (p_sim_condition_id NUMBER,
122 p_trx_id NUMBER,
123 p_trx_line_id NUMBER,
124 p_tax_line_number NUMBER,
125 p_det_factor_class_code VARCHAR2, --p_determining_factor_class_code
126 p_determining_factor_code VARCHAR2,
127 p_data_type_code VARCHAR2,
128 p_operator_code VARCHAR2,
129 p_tax_parameter_code VARCHAR2,
130 p_determining_factor_cq_code VARCHAR2,
131 p_numeric_value NUMBER,
132 p_date_value DATE,
133 p_alphanumeric_value VARCHAR2,
134 p_value_low VARCHAR2,
135 p_value_high VARCHAR2,
136 p_applicability_flag VARCHAR2,
137 p_status_determine_flag VARCHAR2,
138 p_default_status_code VARCHAR2,
139 p_rate_determine_flag VARCHAR2,
140 p_direct_rate_determine_flag VARCHAR2,
141 p_taxable_basis_determine_flag VARCHAR2,
142 p_calculate_tax_determine_flag VARCHAR2,
143 p_place_of_supply_det_flag VARCHAR2, --p_place_of_supply_determine_flag
144 p_registration_determine_flag VARCHAR2,
145 p_created_by NUMBER,
146 p_creation_date DATE,
147 p_last_updated_by NUMBER,
148 p_last_update_date DATE,
149 p_last_update_login NUMBER) IS
150
151 l_return_status VARCHAR2(1000);
152 l_msg_count NUMBER;
153 l_msg_data VARCHAR2(1000);
154
155 BEGIN
156 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
157
158 IF (g_level_procedure >= g_current_runtime_level ) THEN
159 FND_LOG.STRING(g_level_procedure,
160 'ZX.PLSQL.ZX_SIM_RULES_PKG.Update_Row.BEGIN',
161 'ZX_SIM_RULES_PKG: Update_Row (+)');
162 END IF;
163
164 UPDATE ZX_SIM_CONDITIONS
165 SET sim_condition_id = p_sim_condition_id,
166 trx_id = p_trx_id,
167 trx_line_id = p_trx_line_id,
168 tax_line_number = p_tax_line_number,
169 determining_factor_class_code = p_det_factor_class_code,
170 determining_factor_code = p_determining_factor_code,
171 data_type_code = p_data_type_code,
172 operator_code = p_operator_code,
173 tax_parameter_code = p_tax_parameter_code,
174 determining_factor_cq_code = p_determining_factor_cq_code,
175 numeric_value = p_numeric_value,
176 date_value = p_date_value,
177 alphanumeric_value = p_alphanumeric_value,
178 value_low = p_value_low,
179 value_high = p_value_high,
180 applicability_flag = p_applicability_flag,
181 status_determine_flag = p_status_determine_flag,
182 default_status_code = p_default_status_code,
183 rate_determine_flag = p_rate_determine_flag,
184 direct_rate_determine_flag = p_direct_rate_determine_flag,
185 taxable_basis_determine_flag = p_taxable_basis_determine_flag,
186 calculate_tax_determine_flag = p_calculate_tax_determine_flag,
187 place_of_supply_determine_flag = p_place_of_supply_det_flag,
188 registration_determine_flag = p_registration_determine_flag,
189 created_by = p_created_by,
190 creation_date = p_creation_date,
191 last_updated_by = p_last_updated_by,
192 last_update_date = p_last_update_date,
193 last_update_login = p_last_update_login
194 WHERE SIM_CONDITION_ID = p_sim_condition_id
195 AND TRX_ID = p_trx_id
196 AND TRX_LINE_ID = p_trx_line_id;
197
198 IF (g_level_procedure >= g_current_runtime_level ) THEN
199 FND_LOG.STRING(g_level_procedure,
200 'ZX.PLSQL.ZX_SIM_RULES_PKG.Update_Row.BEGIN',
201 'ZX_SIM_RULES_PKG: Update_Row (-)');
202 END IF;
203 END Update_Row;
204
205 PROCEDURE Delete_Row
206 (p_sim_condition_id NUMBER,
207 p_trx_id NUMBER,
208 p_trx_line_id NUMBER,
209 p_tax_line_number NUMBER,
210 p_det_factor_class_code VARCHAR2, --p_determining_factor_class_code
211 p_determining_factor_code VARCHAR2,
212 p_data_type_code VARCHAR2,
213 p_operator_code VARCHAR2,
214 p_tax_parameter_code VARCHAR2,
215 p_determining_factor_cq_code VARCHAR2,
216 p_numeric_value NUMBER,
217 p_date_value DATE,
218 p_alphanumeric_value VARCHAR2,
219 p_value_low VARCHAR2,
220 p_value_high VARCHAR2,
221 p_applicability_flag VARCHAR2,
222 p_status_determine_flag VARCHAR2,
223 p_default_status_code VARCHAR2,
224 p_rate_determine_flag VARCHAR2,
225 p_direct_rate_determine_flag VARCHAR2,
226 p_taxable_basis_determine_flag VARCHAR2,
227 p_calculate_tax_determine_flag VARCHAR2,
228 p_place_of_supply_det_flag VARCHAR2, --p_place_of_supply_determine_flag
229 p_registration_determine_flag VARCHAR2,
230 p_created_by NUMBER,
231 p_creation_date DATE,
235
232 p_last_updated_by NUMBER,
233 p_last_update_date DATE,
234 p_last_update_login NUMBER) IS
236 l_return_status VARCHAR2(1000);
237 l_msg_count NUMBER;
238 l_msg_data VARCHAR2(1000);
239
240 BEGIN
241 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
242
243 IF (g_level_procedure >= g_current_runtime_level ) THEN
244 FND_LOG.STRING(g_level_procedure,
245 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
246 'ZX_SIM_RULES_PKG: Delete_Row (+)');
247 END IF;
248
249 IF (g_level_procedure >= g_current_runtime_level ) THEN
250 FND_LOG.STRING(g_level_procedure,
251 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
252 'ZX_SIM_RULES_PKG: Delete_Row (-)');
253 END IF;
254 END Delete_Row;
255
256 PROCEDURE Lock_Row
257 (p_sim_condition_id NUMBER,
258 p_trx_id NUMBER,
259 p_trx_line_id NUMBER,
260 p_tax_line_number NUMBER,
261 p_det_factor_class_code VARCHAR2, --p_determining_factor_class_code
262 p_determining_factor_code VARCHAR2,
263 p_data_type_code VARCHAR2,
264 p_operator_code VARCHAR2,
265 p_tax_parameter_code VARCHAR2,
266 p_determining_factor_cq_code VARCHAR2,
267 p_numeric_value NUMBER,
268 p_date_value DATE,
269 p_alphanumeric_value VARCHAR2,
270 p_value_low VARCHAR2,
271 p_value_high VARCHAR2,
272 p_applicability_flag VARCHAR2,
273 p_status_determine_flag VARCHAR2,
274 p_default_status_code VARCHAR2,
275 p_rate_determine_flag VARCHAR2,
276 p_direct_rate_determine_flag VARCHAR2,
277 p_taxable_basis_determine_flag VARCHAR2,
278 p_calculate_tax_determine_flag VARCHAR2,
279 p_place_of_supply_det_flag VARCHAR2, --p_place_of_supply_determine_flag
280 p_registration_determine_flag VARCHAR2,
281 p_created_by NUMBER,
282 p_creation_date DATE,
283 p_last_updated_by NUMBER,
284 p_last_update_date DATE,
285 p_last_update_login NUMBER) IS
286
287 l_return_status VARCHAR2(1000);
288 l_msg_count NUMBER;
289 l_msg_data VARCHAR2(1000);
290
291 CURSOR sim_conditions_csr IS
292 SELECT SIM_CONDITION_ID,
293 TRX_ID,
294 TRX_LINE_ID,
295 TAX_LINE_NUMBER,
296 DETERMINING_FACTOR_CLASS_CODE,
297 DETERMINING_FACTOR_CODE,
298 DATA_TYPE_CODE,
299 OPERATOR_CODE,
300 TAX_PARAMETER_CODE,
301 DETERMINING_FACTOR_CQ_CODE,
302 NUMERIC_VALUE,
303 DATE_VALUE,
304 ALPHANUMERIC_VALUE,
305 VALUE_LOW,
306 VALUE_HIGH,
307 APPLICABILITY_FLAG,
308 STATUS_DETERMINE_FLAG,
309 DEFAULT_STATUS_CODE,
310 RATE_DETERMINE_FLAG,
311 DIRECT_RATE_DETERMINE_FLAG,
312 TAXABLE_BASIS_DETERMINE_FLAG,
313 CALCULATE_TAX_DETERMINE_FLAG,
314 PLACE_OF_SUPPLY_DETERMINE_FLAG,
315 REGISTRATION_DETERMINE_FLAG,
316 CREATED_BY,
317 CREATION_DATE,
318 LAST_UPDATED_BY,
319 LAST_UPDATE_DATE,
320 LAST_UPDATE_LOGIN
321 FROM ZX_SIM_CONDITIONS
322 WHERE SIM_CONDITION_ID = p_sim_condition_id
323 AND TRX_ID = p_trx_id
324 AND TRX_LINE_ID = p_trx_line_id;
325
326 Recinfo sim_conditions_csr%ROWTYPE;
327
328 BEGIN
329 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
330
331 IF (g_level_procedure >= g_current_runtime_level ) THEN
332 FND_LOG.STRING(g_level_procedure,
333 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
334 'ZX_SIM_RULES_PKG: Lock_Row (+)');
335 END IF;
336
337 OPEN sim_conditions_csr;
338 FETCH sim_conditions_csr INTO Recinfo;
339
340 IF (sim_conditions_csr%NOTFOUND) THEN
341 CLOSE sim_conditions_csr;
342 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
343 APP_EXCEPTION.Raise_Exception;
344 END IF;
345 CLOSE sim_conditions_csr;
346
347 IF ((Recinfo.SIM_CONDITION_ID = p_SIM_CONDITION_ID) AND
348 (Recinfo.TRX_ID = p_TRX_ID) AND
349 (Recinfo.TRX_LINE_ID = p_TRX_LINE_ID) AND
350 (Recinfo.TAX_LINE_NUMBER = p_TAX_LINE_NUMBER) AND
351 (Recinfo.DETERMINING_FACTOR_CLASS_CODE = p_DET_FACTOR_CLASS_CODE) AND
352 (Recinfo.DETERMINING_FACTOR_CODE = p_DETERMINING_FACTOR_CODE) AND
353 (Recinfo.DATA_TYPE_CODE = p_DATA_TYPE_CODE) AND
354 (Recinfo.OPERATOR_CODE = p_OPERATOR_CODE) AND
355 ((Recinfo.TAX_PARAMETER_CODE = p_TAX_PARAMETER_CODE) OR
356 ((Recinfo.TAX_PARAMETER_CODE IS NULL) AND
357 (p_TAX_PARAMETER_CODE IS NULL))) AND
358 ((Recinfo.DETERMINING_FACTOR_CQ_CODE = p_DETERMINING_FACTOR_CQ_CODE) OR
359 ((Recinfo.DETERMINING_FACTOR_CQ_CODE IS NULL) AND
363 (p_NUMERIC_VALUE IS NULL))) AND
360 (p_DETERMINING_FACTOR_CQ_CODE IS NULL))) AND
361 ((Recinfo.NUMERIC_VALUE = p_NUMERIC_VALUE) OR
362 ((Recinfo.NUMERIC_VALUE IS NULL) AND
364 ((Recinfo.DATE_VALUE = p_DATE_VALUE) OR
365 ((Recinfo.DATE_VALUE IS NULL) AND
366 (p_DATE_VALUE IS NULL))) AND
367 ((Recinfo.ALPHANUMERIC_VALUE = p_ALPHANUMERIC_VALUE) OR
368 ((Recinfo.ALPHANUMERIC_VALUE IS NULL) AND
369 (p_ALPHANUMERIC_VALUE IS NULL))) AND
370 ((Recinfo.VALUE_LOW = p_VALUE_LOW) OR
371 ((Recinfo.VALUE_LOW IS NULL) AND
372 (p_VALUE_LOW IS NULL))) AND
373 ((Recinfo.VALUE_HIGH = p_VALUE_HIGH) OR
374 ((Recinfo.VALUE_HIGH IS NULL) AND
375 (p_VALUE_HIGH IS NULL))) AND
376 ((Recinfo.APPLICABILITY_FLAG = p_APPLICABILITY_FLAG) OR
377 ((Recinfo.APPLICABILITY_FLAG IS NULL) AND
378 (p_APPLICABILITY_FLAG IS NULL))) AND
379 ((Recinfo.STATUS_DETERMINE_FLAG = p_STATUS_DETERMINE_FLAG) OR
380 ((Recinfo.STATUS_DETERMINE_FLAG IS NULL) AND
381 (p_STATUS_DETERMINE_FLAG IS NULL))) AND
382 ((Recinfo.DEFAULT_STATUS_CODE = p_DEFAULT_STATUS_CODE) OR
383 ((Recinfo.DEFAULT_STATUS_CODE IS NULL) AND
384 (p_DEFAULT_STATUS_CODE IS NULL))) AND
385 ((Recinfo.RATE_DETERMINE_FLAG = p_RATE_DETERMINE_FLAG) OR
386 ((Recinfo.RATE_DETERMINE_FLAG IS NULL) AND
387 (p_RATE_DETERMINE_FLAG IS NULL))) AND
388 ((Recinfo.DIRECT_RATE_DETERMINE_FLAG = p_DIRECT_RATE_DETERMINE_FLAG) OR
389 ((Recinfo.DIRECT_RATE_DETERMINE_FLAG IS NULL) AND
390 (p_DIRECT_RATE_DETERMINE_FLAG IS NULL)))AND
391 ((Recinfo.TAXABLE_BASIS_DETERMINE_FLAG = p_TAXABLE_BASIS_DETERMINE_FLAG) OR
392 ((Recinfo.TAXABLE_BASIS_DETERMINE_FLAG IS NULL) AND
393 (p_TAXABLE_BASIS_DETERMINE_FLAG IS NULL)))AND
394 ((Recinfo.CALCULATE_TAX_DETERMINE_FLAG = p_CALCULATE_TAX_DETERMINE_FLAG) OR
395 ((Recinfo.CALCULATE_TAX_DETERMINE_FLAG IS NULL) AND
396 (p_CALCULATE_TAX_DETERMINE_FLAG IS NULL)))AND
397 ((Recinfo.PLACE_OF_SUPPLY_DETERMINE_FLAG = p_place_of_supply_det_flag) OR
398 ((Recinfo.PLACE_OF_SUPPLY_DETERMINE_FLAG IS NULL) AND
399 (p_place_of_supply_det_flag IS NULL)))AND
400 ((Recinfo.REGISTRATION_DETERMINE_FLAG = p_REGISTRATION_DETERMINE_FLAG) OR
401 ((Recinfo.REGISTRATION_DETERMINE_FLAG IS NULL) AND
402 (p_REGISTRATION_DETERMINE_FLAG IS NULL)))AND
403 (Recinfo.CREATED_BY = p_CREATED_BY) AND
404 (Recinfo.CREATION_DATE = p_CREATION_DATE) AND
405 (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY) AND
406 (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE) AND
407 ((Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN) OR
408 ((Recinfo.LAST_UPDATE_LOGIN IS NULL) AND
409 (p_LAST_UPDATE_LOGIN IS NULL))) ) THEN
410 RETURN;
411 ELSE
412 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
413 APP_EXCEPTION.Raise_Exception;
414 END IF;
415
416 IF (g_level_procedure >= g_current_runtime_level ) THEN
417 FND_LOG.STRING(g_level_procedure,
418 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
419 'ZX_SIM_RULES_PKG: Lock_Row (-)');
420 END IF;
421 END Lock_Row;
422
423 END ZX_SIM_CONDITIONS1_PKG;