[Home] [Help]
PACKAGE BODY: APPS.ZX_SIM_RULES_PKG
Source
1 PACKAGE BODY ZX_SIM_RULES_PKG AS
2 /* $Header: zxrisimrulespkgb.pls 120.1 2005/10/27 18:50:58 pla 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_tax_rule_id NUMBER,
12 p_content_owner_id NUMBER,
13 p_tax_rule_code VARCHAR2,
14 p_tax VARCHAR2,
15 p_tax_regime_code VARCHAR2,
16 p_service_type_code VARCHAR2,
17 p_priority NUMBER,
18 p_det_factor_templ_code VARCHAR2,
19 p_effective_from DATE,
20 p_simulated_flag VARCHAR2,
21 p_created_by NUMBER,
22 p_creation_date DATE,
23 p_last_updated_by NUMBER,
24 p_last_update_date DATE,
25 p_last_update_login NUMBER,
26 p_effective_to DATE,
27 p_application_id NUMBER,
28 p_recovery_type_code VARCHAR2,
29 p_request_id NUMBER,
30 p_program_application_id NUMBER,
31 p_program_id NUMBER,
32 p_program_login_id NUMBER) IS
33
34 l_return_status VARCHAR2(1000);
35 l_msg_count NUMBER;
36 l_msg_data VARCHAR2(1000);
37
38 BEGIN
39 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
40
41 IF (g_level_procedure >= g_current_runtime_level ) THEN
42 FND_LOG.STRING(g_level_procedure,
43 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
44 'ZX_SIM_RULES_PKG: Insert_Row (+)');
45 END IF;
46
47 INSERT INTO ZX_SIM_RULES_B (sim_tax_rule_id,
48 content_owner_id,
49 tax_rule_code,
50 tax,
51 tax_regime_code,
52 service_type_code,
53 priority,
54 det_factor_templ_code,
55 effective_from,
56 simulated_flag,
57 created_by,
58 creation_date,
59 last_updated_by,
60 last_update_date,
61 last_update_login,
62 effective_to,
63 application_id,
64 recovery_type_code,
65 request_id,
66 program_application_id,
67 program_id,
68 program_login_id)
69 VALUES (p_sim_tax_rule_id,
70 p_content_owner_id,
71 p_tax_rule_code,
72 p_tax,
73 p_tax_regime_code,
74 p_service_type_code,
75 p_priority,
76 p_det_factor_templ_code,
77 p_effective_from,
78 p_simulated_flag,
79 p_created_by,
80 p_creation_date,
81 p_last_updated_by,
82 p_last_update_date,
83 p_last_update_login,
84 p_effective_to,
85 p_application_id,
86 p_recovery_type_code,
87 p_request_id,
88 p_program_application_id,
89 p_program_id,
90 p_program_login_id);
91
92 IF (g_level_procedure >= g_current_runtime_level ) THEN
93 FND_LOG.STRING(g_level_procedure,
94 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
95 'ZX_SIM_RULES_PKG: Insert_Row (-)');
96 END IF;
97 END Insert_Row;
98
99 PROCEDURE Update_Row
100 (p_sim_tax_rule_id NUMBER,
101 p_content_owner_id NUMBER,
102 p_tax_rule_code VARCHAR2,
103 p_tax VARCHAR2,
104 p_tax_regime_code VARCHAR2,
105 p_service_type_code VARCHAR2,
106 p_priority NUMBER,
107 p_det_factor_templ_code VARCHAR2,
108 p_effective_from DATE,
109 p_simulated_flag VARCHAR2,
110 p_created_by NUMBER,
111 p_creation_date DATE,
112 p_last_updated_by NUMBER,
113 p_last_update_date DATE,
114 p_last_update_login NUMBER,
115 p_effective_to DATE,
116 p_application_id NUMBER,
117 p_recovery_type_code VARCHAR2,
118 p_request_id NUMBER,
119 p_program_application_id NUMBER,
120 p_program_id NUMBER,
121 p_program_login_id NUMBER) IS
122
123 l_return_status VARCHAR2(1000);
124 l_msg_count NUMBER;
125 l_msg_data VARCHAR2(1000);
126
127 BEGIN
128 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
129
130 IF (g_level_procedure >= g_current_runtime_level ) THEN
131 FND_LOG.STRING(g_level_procedure,
132 'ZX.PLSQL.ZX_SIM_RULES_PKG.Update_Row.BEGIN',
133 'ZX_SIM_RULES_PKG: Update_Row (+)');
134 END IF;
135
136 UPDATE ZX_SIM_RULES_B
137 SET sim_tax_rule_id = p_sim_tax_rule_id,
138 content_owner_id = p_content_owner_id,
139 tax_rule_code = p_tax_rule_code,
140 tax = p_tax,
141 tax_regime_code = p_tax_regime_code,
142 service_type_code = p_service_type_code,
143 priority = p_priority,
144 det_factor_templ_code = p_det_factor_templ_code,
145 effective_from = p_effective_from,
146 simulated_flag = p_simulated_flag,
147 created_by = p_created_by,
148 creation_date = p_creation_date,
149 last_updated_by = p_last_updated_by,
150 last_update_date = p_last_update_date,
151 last_update_login = p_last_update_login,
152 effective_to = p_effective_to,
153 application_id = p_application_id,
154 recovery_type_code = p_recovery_type_code,
155 request_id = p_request_id,
156 program_application_id = p_program_application_id,
157 program_id = p_program_id,
158 program_login_id = p_program_login_id
159 WHERE sim_tax_rule_id = p_sim_tax_rule_id
160 AND content_owner_id = p_content_owner_id
161 AND application_id = p_application_id;
162
163 IF (g_level_procedure >= g_current_runtime_level ) THEN
164 FND_LOG.STRING(g_level_procedure,
165 'ZX.PLSQL.ZX_SIM_RULES_PKG.Update_Row.BEGIN',
166 'ZX_SIM_RULES_PKG: Update_Row (-)');
167 END IF;
168 END Update_Row;
169
170 PROCEDURE Delete_Row
171 (p_sim_tax_rule_id NUMBER,
172 p_content_owner_id NUMBER,
173 p_tax_rule_code VARCHAR2,
174 p_tax VARCHAR2,
175 p_tax_regime_code VARCHAR2,
176 p_service_type_code VARCHAR2,
177 p_priority NUMBER,
178 p_det_factor_templ_code VARCHAR2,
179 p_effective_from DATE,
180 p_simulated_flag VARCHAR2,
181 p_created_by NUMBER,
182 p_creation_date DATE,
183 p_last_updated_by NUMBER,
184 p_last_update_date DATE,
185 p_last_update_login NUMBER,
186 p_effective_to DATE,
187 p_application_id NUMBER,
188 p_recovery_type_code VARCHAR2,
189 p_request_id NUMBER,
190 p_program_application_id NUMBER,
191 p_program_id NUMBER,
192 p_program_login_id NUMBER) IS
193
194 l_return_status VARCHAR2(1000);
195 l_msg_count NUMBER;
196 l_msg_data VARCHAR2(1000);
197
198 BEGIN
199 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
200
201 IF (g_level_procedure >= g_current_runtime_level ) THEN
202 FND_LOG.STRING(g_level_procedure,
203 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
204 'ZX_SIM_RULES_PKG: Delete_Row (+)');
205 END IF;
206
207 IF (g_level_procedure >= g_current_runtime_level ) THEN
208 FND_LOG.STRING(g_level_procedure,
209 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
210 'ZX_SIM_RULES_PKG: Delete_Row (-)');
211 END IF;
212 END Delete_Row;
213
214 PROCEDURE Lock_Row
215 (p_sim_tax_rule_id NUMBER,
216 p_content_owner_id NUMBER,
217 p_tax_rule_code VARCHAR2,
218 p_tax VARCHAR2,
219 p_tax_regime_code VARCHAR2,
220 p_service_type_code VARCHAR2,
221 p_priority NUMBER,
222 p_det_factor_templ_code VARCHAR2,
223 p_effective_from DATE,
224 p_simulated_flag VARCHAR2,
225 p_created_by NUMBER,
226 p_creation_date DATE,
227 p_last_updated_by NUMBER,
228 p_last_update_date DATE,
229 p_last_update_login NUMBER,
230 p_effective_to DATE,
231 p_application_id NUMBER,
232 p_recovery_type_code VARCHAR2,
233 p_request_id NUMBER,
234 p_program_application_id NUMBER,
235 p_program_id NUMBER,
236 p_program_login_id NUMBER) IS
237
238 l_return_status VARCHAR2(1000);
239 l_msg_count NUMBER;
240 l_msg_data VARCHAR2(1000);
241
242 CURSOR sim_rules_csr IS
243 SELECT sim_tax_rule_id,
244 content_owner_id,
245 tax_rule_code,
246 tax,
247 tax_regime_code,
248 service_type_code,
249 priority,
250 det_factor_templ_code,
251 effective_from,
252 simulated_flag,
253 created_by,
254 creation_date,
255 last_updated_by,
256 last_update_date,
257 last_update_login,
258 effective_to,
259 application_id,
260 recovery_type_code,
261 request_id,
262 program_application_id,
263 program_id,
264 program_login_id
265 FROM ZX_SIM_RULES_B
266 WHERE SIM_TAX_RULE_ID = p_sim_tax_rule_id
267 AND CONTENT_OWNER_ID = p_content_owner_id
268 AND APPLICATION_ID = p_application_id;
269
270 Recinfo sim_rules_csr%ROWTYPE;
271
272 BEGIN
273 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
274
275 IF (g_level_procedure >= g_current_runtime_level ) THEN
276 FND_LOG.STRING(g_level_procedure,
277 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
278 'ZX_SIM_RULES_PKG: Lock_Row (+)');
279 END IF;
280
281 OPEN sim_rules_csr;
282 FETCH sim_rules_csr INTO Recinfo;
283
284 IF (sim_rules_csr%NOTFOUND) THEN
285 CLOSE sim_rules_csr;
289 CLOSE sim_rules_csr;
286 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
287 APP_EXCEPTION.Raise_Exception;
288 END IF;
290
291 IF ((Recinfo.SIM_TAX_RULE_ID = p_SIM_TAX_RULE_ID) AND
292 (Recinfo.CONTENT_OWNER_ID = p_CONTENT_OWNER_ID) AND
293 (Recinfo.TAX_RULE_CODE = p_TAX_RULE_CODE) AND
294 (Recinfo.TAX = p_TAX) AND
295 (Recinfo.TAX_REGIME_CODE = p_TAX_REGIME_CODE) AND
296 (Recinfo.SERVICE_TYPE_CODE = p_SERVICE_TYPE_CODE) AND
297 (Recinfo.PRIORITY = p_PRIORITY) AND
298 (Recinfo.DET_FACTOR_TEMPL_CODE = p_DET_FACTOR_TEMPL_CODE) AND
299 (Recinfo.EFFECTIVE_FROM = p_EFFECTIVE_FROM) AND
300 (Recinfo.SIMULATED_FLAG = p_SIMULATED_FLAG) AND
301 (Recinfo.CREATED_BY = p_CREATED_BY) AND
302 (Recinfo.CREATION_DATE = p_CREATION_DATE) AND
303 (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY) AND
304 (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE) AND
305 ((Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN) OR
306 ((Recinfo.LAST_UPDATE_LOGIN IS NULL) AND
307 (p_LAST_UPDATE_LOGIN IS NULL))) AND
308 ((Recinfo.EFFECTIVE_TO = p_EFFECTIVE_TO) OR
309 ((Recinfo.EFFECTIVE_TO IS NULL) AND
310 (p_EFFECTIVE_TO IS NULL))) AND
311 ((Recinfo.APPLICATION_ID = p_APPLICATION_ID) OR
312 ((Recinfo.APPLICATION_ID IS NULL) AND
313 (p_APPLICATION_ID IS NULL))) AND
314 ((Recinfo.RECOVERY_TYPE_CODE = p_RECOVERY_TYPE_CODE) OR
315 ((Recinfo.RECOVERY_TYPE_CODE IS NULL) AND
316 (p_RECOVERY_TYPE_CODE IS NULL))) AND
317 ((Recinfo.REQUEST_ID = p_REQUEST_ID) OR
318 ((Recinfo.REQUEST_ID IS NULL) AND
319 (p_REQUEST_ID IS NULL))) AND
320 ((Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID) OR
321 ((Recinfo.PROGRAM_APPLICATION_ID IS NULL) AND
322 (p_PROGRAM_APPLICATION_ID IS NULL))) AND
323 ((Recinfo.PROGRAM_ID = p_PROGRAM_ID) OR
324 ((Recinfo.PROGRAM_ID IS NULL) AND
325 (p_PROGRAM_ID IS NULL))) AND
326 ((Recinfo.PROGRAM_LOGIN_ID = p_PROGRAM_LOGIN_ID) OR
327 ((Recinfo.PROGRAM_LOGIN_ID IS NULL) AND
328 (p_PROGRAM_LOGIN_ID IS NULL))) ) THEN
329 RETURN;
330 ELSE
331 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
332 APP_EXCEPTION.Raise_Exception;
333 END IF;
334
335 IF (g_level_procedure >= g_current_runtime_level ) THEN
336 FND_LOG.STRING(g_level_procedure,
337 'ZX.PLSQL.ZX_SIM_RULES_PKG.Insert_Row.BEGIN',
338 'ZX_SIM_RULES_PKG: Lock_Row (-)');
339 END IF;
340 END Lock_Row;
341
342 procedure ADD_LANGUAGE
343 is
344 begin
345
346 delete from ZX_SIM_RULES_TL T
347 where not exists
348 (select NULL
349 from ZX_SIM_RULES_B B
350 where B.SIM_TAX_RULE_ID = T.SIM_TAX_RULE_ID);
351 update ZX_SIM_RULES_TL T set (
352 TAX_RULE_NAME
353 ) = (select B.TAX_RULE_NAME
354 from ZX_SIM_RULES_TL B
355 where B.SIM_TAX_RULE_ID = T.SIM_TAX_RULE_ID
356 and B.LANGUAGE = T.SOURCE_LANG)
357 where (T.SIM_TAX_RULE_ID, T.LANGUAGE) in
358 (select SUBT.SIM_TAX_RULE_ID,
359 SUBT.LANGUAGE
360 from ZX_SIM_RULES_TL SUBB, ZX_SIM_RULES_TL SUBT
361 where SUBB.SIM_TAX_RULE_ID = SUBT.SIM_TAX_RULE_ID
362 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
363 and (SUBB.TAX_RULE_NAME <> SUBT.TAX_RULE_NAME
364 ));
365
366 insert into ZX_SIM_RULES_TL (
367 SIM_TAX_RULE_ID,
368 TAX_RULE_NAME,
369 CREATION_DATE,
370 CREATED_BY,
371 LAST_UPDATE_DATE,
372 LAST_UPDATED_BY,
373 LAST_UPDATE_LOGIN,
374 LANGUAGE,
375 SOURCE_LANG)
376 select
377 B.SIM_TAX_RULE_ID,
378 B.TAX_RULE_NAME,
379 B.CREATION_DATE,
380 B.CREATED_BY,
381 B.LAST_UPDATE_DATE,
382 B.LAST_UPDATED_BY,
383 B.LAST_UPDATE_LOGIN,
384 L.LANGUAGE_CODE,
385 B.SOURCE_LANG
386 from ZX_SIM_RULES_TL B, FND_LANGUAGES L
387 where L.INSTALLED_FLAG in ('I', 'B')
388 and B.LANGUAGE = userenv('LANG')
389 and not exists
390 (select NULL
391 from ZX_SIM_RULES_TL T
392 where T.SIM_TAX_RULE_ID = B.SIM_TAX_RULE_ID
393 and T.LANGUAGE = L.LANGUAGE_CODE);
394
395 EXCEPTION
396 WHEN OTHERS THEN
397 APP_EXCEPTION.RAISE_EXCEPTION;
398
399 end ADD_LANGUAGE;
400
401 END ZX_SIM_RULES_PKG;