[Home] [Help]
PACKAGE BODY: APPS.ZX_FORMULA_PKG
Source
1 package body ZX_FORMULA_PKG as
2 /* $Header: zxdformulab.pls 120.9 2005/10/21 22:06:41 rsanthan ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_FORMULA_ID in NUMBER,
7 X_Formula_Type_Code in VARCHAR2,
8 X_FORMULA_CODE in VARCHAR2,
9 X_TAX_REGIME_CODE in VARCHAR2,
10 X_TAX in VARCHAR2,
11 X_EFFECTIVE_FROM in DATE,
12 X_EFFECTIVE_TO in DATE,
13 X_Taxable_Basis_Type_Code in VARCHAR2,
14 X_Record_Type_Code in VARCHAR2,
15 X_BASE_RATE_MODIFIER in NUMBER,
16 X_Cash_Discount_Appl_Flag in VARCHAR2,
17 X_Volume_Discount_Appl_Flag in VARCHAR2,
18 X_Trading_Discount_Appl_Flag in VARCHAR2,
19 X_Transfer_Charge_Appl_Flag in VARCHAR2,
20 X_TRANSPORT_CHARGE_APPL_FLAG in VARCHAR2,
21 X_Insurance_Charge_Appl_Flag in VARCHAR2,
22 X_Other_Charge_Appl_Flag in VARCHAR2,
23 X_ATTRIBUTE_CATEGORY in VARCHAR2,
24 X_ATTRIBUTE1 in VARCHAR2,
25 X_ATTRIBUTE2 in VARCHAR2,
26 X_ATTRIBUTE3 in VARCHAR2,
27 X_ATTRIBUTE4 in VARCHAR2,
28 X_ATTRIBUTE5 in VARCHAR2,
29 X_ATTRIBUTE6 in VARCHAR2,
30 X_ATTRIBUTE7 in VARCHAR2,
31 X_ATTRIBUTE8 in VARCHAR2,
32 X_ATTRIBUTE9 in VARCHAR2,
33 X_ATTRIBUTE10 in VARCHAR2,
34 X_ATTRIBUTE11 in VARCHAR2,
35 X_ATTRIBUTE12 in VARCHAR2,
36 X_ATTRIBUTE13 in VARCHAR2,
37 X_ATTRIBUTE14 in VARCHAR2,
38 X_ATTRIBUTE15 in VARCHAR2,
39 X_ATTRIBUTE16 in VARCHAR2,
40 X_ATTRIBUTE17 in VARCHAR2,
41 X_ATTRIBUTE18 in VARCHAR2,
42 X_ATTRIBUTE19 in VARCHAR2,
43 X_ATTRIBUTE20 in VARCHAR2,
44 X_REQUEST_ID in NUMBER,
45 X_FORMULA_NAME in VARCHAR2,
46 X_FORMULA_DESCRIPTION in VARCHAR2,
47 X_CREATION_DATE in DATE,
48 X_CREATED_BY in NUMBER,
49 X_LAST_UPDATE_DATE in DATE,
50 X_LAST_UPDATED_BY in NUMBER,
51 X_LAST_UPDATE_LOGIN in NUMBER,
52 X_PROGRAM_APPLICATION_ID in NUMBER,
53 X_PROGRAM_ID in NUMBER,
54 X_PROGRAM_LOGIN_ID in NUMBER,
55 X_Enabled_Flag in VARCHAR2,
56 X_CONTENT_OWNER_ID in NUMBER,
57 X_OBJECT_VERSION_NUMBER in NUMBER) is
58
59 cursor C is select ROWID from ZX_FORMULA_B
60 where FORMULA_ID = X_FORMULA_ID ;
61 begin
62 insert into ZX_FORMULA_B (
63 FORMULA_ID,
64 Formula_Type_Code,
65 FORMULA_CODE,
66 TAX_REGIME_CODE,
67 TAX,
68 EFFECTIVE_FROM,
69 EFFECTIVE_TO,
70 Taxable_Basis_Type_Code,
71 Record_Type_Code,
72 BASE_RATE_MODIFIER,
73 Cash_Discount_Appl_Flag,
74 Volume_Discount_Appl_Flag,
75 Trading_Discount_Appl_Flag,
76 Transfer_Charge_Appl_Flag,
77 Transport_Charge_Appl_Flag,
78 Insurance_Charge_Appl_Flag,
79 Other_Charge_Appl_Flag,
80 ATTRIBUTE_CATEGORY,
81 ATTRIBUTE1,
82 ATTRIBUTE2,
83 ATTRIBUTE3,
84 ATTRIBUTE4,
85 ATTRIBUTE5,
86 ATTRIBUTE6,
87 ATTRIBUTE7,
88 ATTRIBUTE8,
89 ATTRIBUTE9,
90 ATTRIBUTE10,
91 ATTRIBUTE11,
92 ATTRIBUTE12,
93 ATTRIBUTE13,
94 ATTRIBUTE14,
95 ATTRIBUTE15,
96 ATTRIBUTE16,
97 ATTRIBUTE17,
98 ATTRIBUTE18,
99 ATTRIBUTE19,
100 ATTRIBUTE20,
101 REQUEST_ID,
102 CREATION_DATE,
103 CREATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_LOGIN,
107 PROGRAM_APPLICATION_ID,
108 PROGRAM_ID,
109 PROGRAM_LOGIN_ID,
110 Enabled_Flag,
111 CONTENT_OWNER_ID,
112 OBJECT_VERSION_NUMBER)
113 values (
114 X_FORMULA_ID,
115 X_Formula_Type_Code,
116 X_FORMULA_CODE,
117 X_TAX_REGIME_CODE,
118 X_TAX,
119 X_EFFECTIVE_FROM,
120 X_EFFECTIVE_TO,
121 X_Taxable_Basis_Type_Code,
122 X_Record_Type_Code,
123 X_BASE_RATE_MODIFIER,
124 NVL(X_Cash_Discount_Appl_Flag,'N'),
125 NVL(X_Volume_Discount_Appl_Flag,'N'),
126 NVL(X_Trading_Discount_Appl_Flag,'N'),
127 NVL(X_Transfer_Charge_Appl_Flag,'N'),
128 NVL(X_TRANSPORT_CHARGE_APPL_FLAG,'N'),
129 NVL(X_Insurance_Charge_Appl_Flag,'N'),
130 NVL(X_Other_Charge_Appl_Flag,'N'),
131 X_ATTRIBUTE_CATEGORY,
132 X_ATTRIBUTE1,
133 X_ATTRIBUTE2,
134 X_ATTRIBUTE3,
135 X_ATTRIBUTE4,
136 X_ATTRIBUTE5,
137 X_ATTRIBUTE6,
138 X_ATTRIBUTE7,
139 X_ATTRIBUTE8,
140 X_ATTRIBUTE9,
141 X_ATTRIBUTE10,
142 X_ATTRIBUTE11,
143 X_ATTRIBUTE12,
144 X_ATTRIBUTE13,
145 X_ATTRIBUTE14,
146 X_ATTRIBUTE15,
147 X_ATTRIBUTE16,
148 X_ATTRIBUTE17,
149 X_ATTRIBUTE18,
150 X_ATTRIBUTE19,
151 X_ATTRIBUTE20,
152 X_REQUEST_ID,
153 X_CREATION_DATE,
154 X_CREATED_BY,
155 X_LAST_UPDATE_DATE,
156 X_LAST_UPDATED_BY,
157 X_LAST_UPDATE_LOGIN,
158 X_PROGRAM_APPLICATION_ID,
159 X_PROGRAM_ID,
160 X_PROGRAM_LOGIN_ID,
161 NVL(X_ENABLED_FLAG,'N'),
162 X_CONTENT_OWNER_ID,
163 X_OBJECT_VERSION_NUMBER);
164
165 insert into ZX_FORMULA_TL (
166 FORMULA_ID,
167 FORMULA_NAME,
168 FORMULA_DESCRIPTION,
169 CREATION_DATE,
170 CREATED_BY,
171 LAST_UPDATE_DATE,
172 LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN,
174 LANGUAGE,
175 SOURCE_LANG)
176 select
177 X_FORMULA_ID,
178 X_FORMULA_NAME,
179 X_FORMULA_DESCRIPTION,
180 X_CREATION_DATE,
181 X_CREATED_BY,
182 X_LAST_UPDATE_DATE,
183 X_LAST_UPDATED_BY,
184 X_LAST_UPDATE_LOGIN,
185 L.LANGUAGE_CODE,
186 userenv('LANG')
187 from FND_LANGUAGES L
188 where L.INSTALLED_FLAG in ('I', 'B')
189 and not exists
190 (select NULL
191 from ZX_FORMULA_TL T
192 where T.FORMULA_ID = X_FORMULA_ID
193 and T.LANGUAGE = L.LANGUAGE_CODE);
194 open c;
195 fetch c into X_ROWID;
196 if (c%notfound) then
197 close c;
198 raise no_data_found;
199 end if;
200 close c;
201
202 EXCEPTION
203 WHEN OTHERS THEN
204 APP_EXCEPTION.RAISE_EXCEPTION;
205
206 end INSERT_ROW;
207
208 procedure LOCK_ROW (
209 X_FORMULA_ID in NUMBER,
210 X_Formula_Type_Code in VARCHAR2,
211 X_FORMULA_CODE in VARCHAR2,
212 X_TAX_REGIME_CODE in VARCHAR2,
213 X_TAX in VARCHAR2,
214 X_EFFECTIVE_FROM in DATE,
215 X_EFFECTIVE_TO in DATE,
216 X_Taxable_Basis_Type_Code in VARCHAR2,
217 X_Record_Type_Code in VARCHAR2,
218 X_BASE_RATE_MODIFIER in NUMBER,
219 X_Cash_Discount_Appl_Flag in VARCHAR2,
220 X_Volume_Discount_Appl_Flag in VARCHAR2,
221 X_Trading_Discount_Appl_Flag in VARCHAR2,
222 X_Transfer_Charge_Appl_Flag in VARCHAR2,
223 X_TRANSPORT_CHARGE_APPL_FLAG in VARCHAR2,
224 X_Insurance_Charge_Appl_Flag in VARCHAR2,
225 X_Other_Charge_Appl_Flag in VARCHAR2,
226 X_ATTRIBUTE_CATEGORY in VARCHAR2,
227 X_ATTRIBUTE1 in VARCHAR2,
228 X_ATTRIBUTE2 in VARCHAR2,
229 X_ATTRIBUTE3 in VARCHAR2,
230 X_ATTRIBUTE4 in VARCHAR2,
231 X_ATTRIBUTE5 in VARCHAR2,
232 X_ATTRIBUTE6 in VARCHAR2,
233 X_ATTRIBUTE7 in VARCHAR2,
234 X_ATTRIBUTE8 in VARCHAR2,
235 X_ATTRIBUTE9 in VARCHAR2,
236 X_ATTRIBUTE10 in VARCHAR2,
237 X_ATTRIBUTE11 in VARCHAR2,
238 X_ATTRIBUTE12 in VARCHAR2,
239 X_ATTRIBUTE13 in VARCHAR2,
240 X_ATTRIBUTE14 in VARCHAR2,
241 X_ATTRIBUTE15 in VARCHAR2,
242 X_ATTRIBUTE16 in VARCHAR2,
243 X_ATTRIBUTE17 in VARCHAR2,
244 X_ATTRIBUTE18 in VARCHAR2,
245 X_ATTRIBUTE19 in VARCHAR2,
246 X_ATTRIBUTE20 in VARCHAR2,
247 X_REQUEST_ID in NUMBER,
248 X_FORMULA_NAME in VARCHAR2,
249 X_FORMULA_DESCRIPTION in VARCHAR2,
250 X_PROGRAM_APPLICATION_ID in NUMBER,
251 X_PROGRAM_ID in NUMBER,
252 X_PROGRAM_LOGIN_ID in NUMBER,
253 X_Enabled_Flag in VARCHAR2,
254 X_CONTENT_OWNER_ID in NUMBER,
255 X_OBJECT_VERSION_NUMBER in NUMBER) is
256
257 cursor c is select
258 Formula_Type_Code,
259 FORMULA_CODE,
260 TAX_REGIME_CODE,
261 TAX,
262 EFFECTIVE_FROM,
263 EFFECTIVE_TO,
264 Taxable_Basis_Type_Code,
265 Record_Type_Code,
266 BASE_RATE_MODIFIER,
267 Cash_Discount_Appl_Flag,
268 Volume_Discount_Appl_Flag,
269 Trading_Discount_Appl_Flag,
270 Transfer_Charge_Appl_Flag,
271 Transport_Charge_Appl_Flag,
272 Insurance_Charge_Appl_Flag,
273 Other_Charge_Appl_Flag,
274 ATTRIBUTE_CATEGORY,
275 ATTRIBUTE1,
276 ATTRIBUTE2,
277 ATTRIBUTE3,
278 ATTRIBUTE4,
279 ATTRIBUTE5,
280 ATTRIBUTE6,
281 ATTRIBUTE7,
282 ATTRIBUTE8,
283 ATTRIBUTE9,
284 ATTRIBUTE10,
285 ATTRIBUTE11,
286 ATTRIBUTE12,
287 ATTRIBUTE13,
288 ATTRIBUTE14,
289 ATTRIBUTE15,
290 ATTRIBUTE16,
291 ATTRIBUTE17,
292 ATTRIBUTE18,
293 ATTRIBUTE19,
294 ATTRIBUTE20,
295 REQUEST_ID,
296 PROGRAM_APPLICATION_ID,
297 PROGRAM_ID,
298 PROGRAM_LOGIN_ID,
299 Enabled_Flag,
300 CONTENT_OWNER_ID,
301 OBJECT_VERSION_NUMBER
302 from ZX_FORMULA_B
303 where FORMULA_ID = X_FORMULA_ID
304 for update of FORMULA_ID nowait;
305
306 recinfo c%rowtype;
307
308 cursor c1 is select FORMULA_NAME,
309 FORMULA_DESCRIPTION,
310 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
311 from ZX_FORMULA_TL
312 where FORMULA_ID = X_FORMULA_ID
313 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
314 for update of FORMULA_ID nowait;
315 begin
316
317 open c;
318 fetch c into recinfo;
319 if (c%notfound) then
320 close c;
321 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
322 app_exception.raise_exception;
323 end if;
324 close c;
325
326 if ( (recinfo.Formula_Type_Code = X_Formula_Type_Code)
327 AND (recinfo.FORMULA_CODE = X_FORMULA_CODE)
328 AND ((recinfo.TAX_REGIME_CODE = X_TAX_REGIME_CODE)
329 OR ((recinfo.TAX_REGIME_CODE is null) AND (X_TAX_REGIME_CODE is null)))
330 AND ((recinfo.TAX = X_TAX)
331 OR ((recinfo.TAX is null) AND (X_TAX is null)))
332 AND (recinfo.EFFECTIVE_FROM = X_EFFECTIVE_FROM)
333 AND ((recinfo.EFFECTIVE_TO = X_EFFECTIVE_TO)
334 OR ((recinfo.EFFECTIVE_TO is null) AND (X_EFFECTIVE_TO is null)))
335 AND ((recinfo.Taxable_Basis_Type_Code = X_Taxable_Basis_Type_Code)
336 OR ((recinfo.Taxable_Basis_Type_Code is null) AND (X_Taxable_Basis_Type_Code is null)))
337 AND (recinfo.Record_Type_Code = X_Record_Type_Code)
338 AND ((recinfo.BASE_RATE_MODIFIER = X_BASE_RATE_MODIFIER)
339 OR ((recinfo.BASE_RATE_MODIFIER is null) AND (X_BASE_RATE_MODIFIER is null)))
340 AND ((recinfo.Cash_Discount_Appl_Flag = X_Cash_Discount_Appl_Flag)
341 OR ((recinfo.Cash_Discount_Appl_Flag is null) AND (X_Cash_Discount_Appl_Flag is null)))
342 AND ((recinfo.Volume_Discount_Appl_Flag = X_Volume_Discount_Appl_Flag)
343 OR ((recinfo.Volume_Discount_Appl_Flag is null) AND (X_Volume_Discount_Appl_Flag is null)))
344 AND ((recinfo.Trading_Discount_Appl_Flag = X_Trading_Discount_Appl_Flag)
345 OR ((recinfo.Trading_Discount_Appl_Flag is null) AND (X_Trading_Discount_Appl_Flag is null)
346 ))
347 AND ((recinfo.Transfer_Charge_Appl_Flag = X_Transfer_Charge_Appl_Flag)
348 OR ((recinfo.Transfer_Charge_Appl_Flag is null) AND (X_Transfer_Charge_Appl_Flag is null)))
352 AND ((recinfo.Insurance_Charge_Appl_Flag = X_Insurance_Charge_Appl_Flag)
349 AND ((recinfo.Transport_Charge_Appl_Flag = X_TRANSPORT_CHARGE_APPL_FLAG)
350 OR ((recinfo.Transport_Charge_Appl_Flag is null) AND (X_TRANSPORT_CHARGE_APPL_FLAG
351 is null)))
353 OR ((recinfo.Insurance_Charge_Appl_Flag is null) AND (X_Insurance_Charge_Appl_Flag is null)
354 ))
355 AND ((recinfo.Other_Charge_Appl_Flag = X_Other_Charge_Appl_Flag)
356 OR ((recinfo.Other_Charge_Appl_Flag is null) AND (X_Other_Charge_Appl_Flag is null)))
357 AND ((recinfo.Enabled_Flag = X_Enabled_Flag)
358 OR ((recinfo.Enabled_Flag is null) AND (X_Enabled_Flag is null)))
359 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
360 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
361 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
362 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
363 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
364 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
365 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
366 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
367 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
368 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
369 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
370 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
371 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
372 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
373 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
374 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
375 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
376 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
377 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
378 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
379 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
380 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
381 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
382 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
383 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
384 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
385 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
386 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
387 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
388 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
389 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
390 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
391 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
392 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
393 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
394 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
395 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
396 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
397 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
398 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
399 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
400 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
401 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
402 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
403 AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
404 OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
405 AND ((recinfo. PROGRAM_ID = X_PROGRAM_ID)
406 OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
407 AND ((recinfo.PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID)
408 OR ((recinfo.PROGRAM_LOGIN_ID is null) AND (X_PROGRAM_LOGIN_ID is null)))
409 AND ((recinfo.CONTENT_OWNER_ID = X_CONTENT_OWNER_ID)
410 OR ((recinfo.CONTENT_OWNER_ID is null) AND (X_CONTENT_OWNER_ID is null)))
411 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
412 ) then
413 null;
414 else
415 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
416 app_exception.raise_exception;
417 end if;
418 for tlinfo in c1 loop
419 if (tlinfo.BASELANG = 'Y') then
420 if ( (tlinfo.FORMULA_NAME = X_FORMULA_NAME)
421 AND ((tlinfo.FORMULA_DESCRIPTION = X_FORMULA_DESCRIPTION)
422 OR ((tlinfo.FORMULA_DESCRIPTION is null) AND (X_FORMULA_DESCRIPTION is null)))
423 ) then
424 null;
425 else
426 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
427 app_exception.raise_exception;
428 end if;
429 end if;
430 end loop;
431 return;
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 APP_EXCEPTION.RAISE_EXCEPTION;
436
437 end LOCK_ROW;
438
439 procedure UPDATE_ROW (
440 X_FORMULA_ID in NUMBER,
441 X_Formula_Type_Code in VARCHAR2,
442 X_FORMULA_CODE in VARCHAR2,
443 X_TAX_REGIME_CODE in VARCHAR2,
444 X_TAX in VARCHAR2,
445 X_EFFECTIVE_FROM in DATE,
446 X_EFFECTIVE_TO in DATE,
447 X_Taxable_Basis_Type_Code in VARCHAR2,
448 X_Record_Type_Code in VARCHAR2,
449 X_BASE_RATE_MODIFIER in NUMBER,
450 X_Cash_Discount_Appl_Flag in VARCHAR2,
451 X_Volume_Discount_Appl_Flag in VARCHAR2,
452 X_Trading_Discount_Appl_Flag in VARCHAR2,
453 X_Transfer_Charge_Appl_Flag in VARCHAR2,
454 X_TRANSPORT_CHARGE_APPL_FLAG in VARCHAR2,
455 X_Insurance_Charge_Appl_Flag in VARCHAR2,
456 X_Other_Charge_Appl_Flag in VARCHAR2,
457 X_ATTRIBUTE_CATEGORY in VARCHAR2,
458 X_ATTRIBUTE1 in VARCHAR2,
459 X_ATTRIBUTE2 in VARCHAR2,
460 X_ATTRIBUTE3 in VARCHAR2,
461 X_ATTRIBUTE4 in VARCHAR2,
462 X_ATTRIBUTE5 in VARCHAR2,
463 X_ATTRIBUTE6 in VARCHAR2,
467 X_ATTRIBUTE10 in VARCHAR2,
464 X_ATTRIBUTE7 in VARCHAR2,
465 X_ATTRIBUTE8 in VARCHAR2,
466 X_ATTRIBUTE9 in VARCHAR2,
468 X_ATTRIBUTE11 in VARCHAR2,
469 X_ATTRIBUTE12 in VARCHAR2,
470 X_ATTRIBUTE13 in VARCHAR2,
471 X_ATTRIBUTE14 in VARCHAR2,
472 X_ATTRIBUTE15 in VARCHAR2,
473 X_ATTRIBUTE16 in VARCHAR2,
474 X_ATTRIBUTE17 in VARCHAR2,
475 X_ATTRIBUTE18 in VARCHAR2,
476 X_ATTRIBUTE19 in VARCHAR2,
477 X_ATTRIBUTE20 in VARCHAR2,
478 X_REQUEST_ID in NUMBER,
479 X_FORMULA_NAME in VARCHAR2,
480 X_FORMULA_DESCRIPTION in VARCHAR2,
481 X_LAST_UPDATE_DATE in DATE,
482 X_LAST_UPDATED_BY in NUMBER,
483 X_LAST_UPDATE_LOGIN in NUMBER,
484 X_PROGRAM_APPLICATION_ID in NUMBER,
485 X_PROGRAM_ID in NUMBER,
486 X_PROGRAM_LOGIN_ID in NUMBER,
487 X_Enabled_Flag in VARCHAR2,
488 X_CONTENT_OWNER_ID in NUMBER,
489 X_OBJECT_VERSION_NUMBER in NUMBER) is
490
491 CURSOR C1 is select Enabled_Flag
492 from ZX_FORMULA_VL
493 WHERE FORMULA_ID = X_FORMULA_ID;
494
495 CURSOR C_PROC_RES is SELECT TAX_RULE_ID,
496 RESULT_ID
497 FROM ZX_PROCESS_RESULTS
498 WHERE Result_Type_Code = 'FORMULA'
499 AND Enabled_Flag = 'Y'
500 AND NUMERIC_RESULT = X_FORMULA_ID;
501
502 CURSOR C_RES_RULE_ID(p_cur_rule_id NUMBER, p_cur_result_id NUMBER) is
503 SELECT TAX_RULE_ID
504 FROM ZX_PROCESS_RESULTS
505 WHERE TAX_RULE_ID = p_cur_rule_id
506 and Enabled_Flag = 'Y'
507 and RESULT_ID <> p_cur_result_id;
508
509 CURSOR C_RULE_REC (p_cur_rule_id1 NUMBER) is
510 SELECT *
511 FROM ZX_RULES_VL
512 WHERE TAX_RULE_ID = p_cur_rule_id1;
513
514 p_rule_rec ZX_RULES_VL%ROWTYPE;
515 p_rule_id1 NUMBER;
516 p_Enabled_Flag VARCHAR2(1);
517 p_rule_id NUMBER;
518 p_result_id NUMBER;
519 ctr NUMBER;
520
521 begin
522
523 -- Logic to update ZX_PROCESS_RESULTS and ZX_RULES tables,
524 -- to disable the associated results and rules of this formula*/
525 OPEN C1;
526 FETCH C1 INTO p_Enabled_Flag;
527 CLOSE C1;
528 if p_Enabled_Flag = 'Y' and X_Enabled_Flag = 'N'then
529 OPEN C_PROC_RES;
530 LOOP
531 FETCH C_PROC_RES into p_rule_id,p_result_id;
532 EXIT WHEN C_PROC_RES%NOTFOUND;
533 OPEN C_RES_RULE_ID(p_rule_id,p_result_id);
534 FETCH C_RES_RULE_ID into p_rule_id1;
535
536 if nvl(p_rule_id1,0) = 0 THEN
537 OPEN C_RULE_REC(p_rule_id);
538 LOOP
539 FETCH C_RULE_REC INTO p_rule_rec;
540 EXIT WHEN C_RULE_REC%NOTFOUND;
541 ZX_RULES_PKG.UPDATE_ROW(
542 p_rule_id,
543 p_rule_rec.TAX_RULE_CODE ,
544 p_rule_rec.TAX,
545 p_rule_rec.TAX_REGIME_CODE ,
546 p_rule_rec.SERVICE_TYPE_CODE ,
547 p_rule_rec.RECOVERY_TYPE_CODE ,
548 p_rule_rec.PRIORITY ,
549 p_rule_rec.System_Default_Flag ,
550 p_rule_rec.EFFECTIVE_FROM ,
551 p_rule_rec.EFFECTIVE_TO ,
552 p_rule_rec.Record_Type_Code ,
553 p_rule_rec.REQUEST_ID ,
554 p_rule_rec.TAX_RULE_NAME ,
555 p_rule_rec.TAX_RULE_DESC ,
556 p_rule_rec.LAST_UPDATE_DATE ,
557 p_rule_rec.LAST_UPDATED_BY ,
558 p_rule_rec.LAST_UPDATE_LOGIN ,
559 p_rule_rec.PROGRAM_APPLICATION_ID ,
560 p_rule_rec.PROGRAM_ID ,
561 p_rule_rec.PROGRAM_LOGIN_ID ,
562 'N',
563 p_rule_rec.APPLICATION_ID ,
564 p_rule_rec.CONTENT_OWNER_ID ,
565 p_rule_rec.DET_FACTOR_TEMPL_CODE,
566 p_rule_rec.EVENT_CLASS_MAPPING_ID,
567 p_rule_rec.TAX_EVENT_CLASS_CODE,
568 p_rule_rec.OBJECT_VERSION_NUMBER,
569 p_rule_rec.DETERMINING_FACTOR_CQ_CODE,
570 p_rule_rec.GEOGRAPHY_TYPE ,
571 p_rule_rec.GEOGRAPHY_ID ,
572 p_rule_rec.TAX_LAW_REF_CODE ,
573 p_rule_rec.TAX_LAW_REF_DESC ,
574 p_rule_rec.LAST_UPDATE_MODE_FLAG ,
575 p_rule_rec.NEVER_BEEN_ENABLED_FLAG );
576 end loop;
577 CLOSE C_RULE_REC;
578 END IF;
579 CLOSE C_RES_RULE_ID;
580 END LOOP;
581 CLOSE C_PROC_RES;
582
583 UPDATE ZX_PROCESS_RESULTS
584 SET Enabled_Flag = 'N'
585 WHERE Result_Type_Code = 'FORMULA'
586 AND Enabled_Flag = 'Y'
587 AND NUMERIC_RESULT = X_FORMULA_ID;
588 end if;
589
590 update ZX_FORMULA_B
591 set Formula_Type_Code = X_Formula_Type_Code,
592 FORMULA_CODE = X_FORMULA_CODE,
593 TAX_REGIME_CODE = X_TAX_REGIME_CODE,
594 TAX = X_TAX,
595 EFFECTIVE_FROM = X_EFFECTIVE_FROM,
596 EFFECTIVE_TO = X_EFFECTIVE_TO,
597 Taxable_Basis_Type_Code = X_Taxable_Basis_Type_Code,
598 Record_Type_Code = X_Record_Type_Code,
599 BASE_RATE_MODIFIER = X_BASE_RATE_MODIFIER,
600 Cash_Discount_Appl_Flag = NVL(X_Cash_Discount_Appl_Flag,'N'),
604 Transport_Charge_Appl_Flag = NVL(X_TRANSPORT_CHARGE_APPL_FLAG,'N'),
601 Volume_Discount_Appl_Flag = NVL(X_Volume_Discount_Appl_Flag,'N'),
602 Trading_Discount_Appl_Flag = NVL(X_Trading_Discount_Appl_Flag,'N'),
603 Transfer_Charge_Appl_Flag = NVL(X_Transfer_Charge_Appl_Flag,'N'),
605 Insurance_Charge_Appl_Flag = NVL(X_Insurance_Charge_Appl_Flag,'N'),
606 Other_Charge_Appl_Flag = NVL(X_Other_Charge_Appl_Flag,'N'),
607 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
608 ATTRIBUTE1 = X_ATTRIBUTE1,
609 ATTRIBUTE2 = X_ATTRIBUTE2,
610 ATTRIBUTE3 = X_ATTRIBUTE3,
611 ATTRIBUTE4 = X_ATTRIBUTE4,
612 ATTRIBUTE5 = X_ATTRIBUTE5,
613 ATTRIBUTE6 = X_ATTRIBUTE6,
614 ATTRIBUTE7 = X_ATTRIBUTE7,
615 ATTRIBUTE8 = X_ATTRIBUTE8,
616 ATTRIBUTE9 = X_ATTRIBUTE9,
617 ATTRIBUTE10 = X_ATTRIBUTE10,
618 ATTRIBUTE11 = X_ATTRIBUTE11,
619 ATTRIBUTE12 = X_ATTRIBUTE12,
620 ATTRIBUTE13 = X_ATTRIBUTE13,
621 ATTRIBUTE14 = X_ATTRIBUTE14,
622 ATTRIBUTE15 = X_ATTRIBUTE15,
623 ATTRIBUTE16 = X_ATTRIBUTE16,
624 ATTRIBUTE17 = X_ATTRIBUTE17,
625 ATTRIBUTE18 = X_ATTRIBUTE18,
626 ATTRIBUTE19 = X_ATTRIBUTE19,
627 ATTRIBUTE20 = X_ATTRIBUTE20,
628 REQUEST_ID = X_REQUEST_ID,
629 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
630 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
631 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
632 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
633 PROGRAM_ID = X_PROGRAM_ID,
634 PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID,
635 Enabled_Flag = NVL(X_ENABLED_FLAG,'N'),
636 CONTENT_OWNER_ID = X_CONTENT_OWNER_ID,
637 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
638 where FORMULA_ID = X_FORMULA_ID;
639
640 if (sql%notfound) then
641 raise no_data_found;
642 end if;
643
644 update ZX_FORMULA_TL
645 set FORMULA_NAME = X_FORMULA_NAME,
646 FORMULA_DESCRIPTION = X_FORMULA_DESCRIPTION,
647 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
648 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
649 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
650 SOURCE_LANG = userenv('LANG')
651 where FORMULA_ID = X_FORMULA_ID
652 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
653
654 if (sql%notfound) then
655 raise no_data_found;
656 end if;
657
658 EXCEPTION
659 WHEN OTHERS THEN
660 APP_EXCEPTION.RAISE_EXCEPTION;
661
662 end UPDATE_ROW;
663
664 procedure DELETE_ROW (
665 X_FORMULA_ID in NUMBER) is
666
667 begin
668
669 delete from ZX_FORMULA_TL
670 where FORMULA_ID = X_FORMULA_ID;
671
672 if (sql%notfound) then
673 raise no_data_found;
674 end if;
675
676 delete from ZX_FORMULA_B
677 where FORMULA_ID = X_FORMULA_ID;
678
679 if (sql%notfound) then
680 raise no_data_found;
681 end if;
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 APP_EXCEPTION.RAISE_EXCEPTION;
686
687 end DELETE_ROW;
688
689 procedure ADD_LANGUAGE
690 is
691 begin
692 delete from ZX_FORMULA_TL T
693 where not exists (select NULL
694 from ZX_FORMULA_B B
695 where B.FORMULA_ID = T.FORMULA_ID);
696
697 update ZX_FORMULA_TL T
698 set (FORMULA_NAME, FORMULA_DESCRIPTION) =
699 (select B.FORMULA_NAME,
700 B.FORMULA_DESCRIPTION
701 from ZX_FORMULA_TL B
702 where B.FORMULA_ID = T.FORMULA_ID
703 and B.LANGUAGE = T.SOURCE_LANG)
704 where (T.FORMULA_ID,T.LANGUAGE) in
705 (select SUBT.FORMULA_ID,
706 SUBT.LANGUAGE
707 from ZX_FORMULA_TL SUBB, ZX_FORMULA_TL SUBT
708 where SUBB.FORMULA_ID = SUBT.FORMULA_ID
709 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
710 and (SUBB.FORMULA_NAME <> SUBT.FORMULA_NAME
711 or SUBB.FORMULA_DESCRIPTION <> SUBT.FORMULA_DESCRIPTION
712 or (SUBB.FORMULA_DESCRIPTION is null
713 and SUBT.FORMULA_DESCRIPTION is not null)
714 or (SUBB.FORMULA_DESCRIPTION is not null
715 and SUBT.FORMULA_DESCRIPTION is null)));
716
717 insert into ZX_FORMULA_TL (FORMULA_ID,
718 FORMULA_NAME,
719 FORMULA_DESCRIPTION,
720 CREATION_DATE,
721 CREATED_BY,
722 LAST_UPDATE_DATE,
723 LAST_UPDATED_BY,
724 LAST_UPDATE_LOGIN,
725 LANGUAGE,
726 SOURCE_LANG)
727 select B.FORMULA_ID,
728 B.FORMULA_NAME,
729 B.FORMULA_DESCRIPTION,
730 B.CREATION_DATE,
731 B.CREATED_BY,
732 B.LAST_UPDATE_DATE,
733 B.LAST_UPDATED_BY,
734 B.LAST_UPDATE_LOGIN,
735 L.LANGUAGE_CODE,
736 B.SOURCE_LANG
737 from ZX_FORMULA_TL B, FND_LANGUAGES L
738 where L.INSTALLED_FLAG in ('I', 'B')
739 and B.LANGUAGE = userenv('LANG')
740 and not exists (select NULL
744
741 from ZX_FORMULA_TL T
742 where T.FORMULA_ID = B.FORMULA_ID
743 and T.LANGUAGE = L.LANGUAGE_CODE);
745 EXCEPTION
746 WHEN OTHERS THEN
747 APP_EXCEPTION.RAISE_EXCEPTION;
748
749 end ADD_LANGUAGE;
750
751
752 procedure bulk_insert_formula (
753 X_FORMULA_ID IN t_formula_id,
754 X_Formula_Type_Code IN t_formula_type,
755 X_FORMULA_CODE IN t_formula_code,
756 X_TAX_REGIME_CODE IN t_tax_regime_code,
757 X_TAX IN t_tax,
758 X_EFFECTIVE_FROM IN t_effective_from,
759 X_EFFECTIVE_TO IN t_effective_to,
760 X_Taxable_Basis_Type_Code IN t_taxable_basis_type,
761 X_Record_Type_Code IN t_record_type,
762 X_BASE_RATE_MODIFIER IN t_base_rate_modifier,
763 X_Cash_Discount_Appl_Flag IN t_cash_discount_appl_flg,
764 X_Volume_Discount_Appl_Flag IN t_volume_discount_appl_flg,
765 X_Trading_Discount_Appl_Flag IN t_trading_discount_appl_flg,
766 X_Transfer_Charge_Appl_Flag IN t_transfer_charge_appl_flg,
767 X_TRANSPORT_CHARGE_APPL_FLG IN t_transport_charge_appl_flg,
768 X_Insurance_Charge_Appl_Flag IN t_insurance_charge_appl_flg,
769 X_Other_Charge_Appl_Flag IN t_other_charge_appl_flg,
770 X_ATTRIBUTE_CATEGORY IN t_attribute_category,
771 X_ATTRIBUTE1 IN t_attribute1,
772 X_ATTRIBUTE2 IN t_attribute2,
773 X_ATTRIBUTE3 IN t_attribute3,
774 X_ATTRIBUTE4 IN t_attribute4,
775 X_ATTRIBUTE5 IN t_attribute5,
776 X_ATTRIBUTE6 IN t_attribute6,
777 X_ATTRIBUTE7 IN t_attribute7,
778 X_ATTRIBUTE8 IN t_attribute8,
779 X_ATTRIBUTE9 IN t_attribute9,
780 X_ATTRIBUTE10 IN t_attribute10,
781 X_ATTRIBUTE11 IN t_attribute11,
782 X_ATTRIBUTE12 IN t_attribute12,
783 X_ATTRIBUTE13 IN t_attribute13,
784 X_ATTRIBUTE14 IN t_attribute14,
785 X_ATTRIBUTE15 IN t_attribute15,
786 X_ATTRIBUTE16 IN t_attribute16,
787 X_ATTRIBUTE17 IN t_attribute17,
788 X_ATTRIBUTE18 IN t_attribute18,
789 X_ATTRIBUTE19 IN t_attribute19,
790 X_ATTRIBUTE20 IN t_attribute20,
791 X_FORMULA_NAME IN t_formula_name,
792 X_FORMULA_DESCRIPTION IN t_formula_description,
793 X_Enabled_Flag IN t_enabled_flg,
794 X_CONTENT_OWNER_ID IN t_content_owner_id) is
795
796 begin
797
798 if x_formula_id.count <> 0 then
799 forall i in x_formula_id.first..x_formula_id.last
800 INSERT INTO ZX_FORMULA_B (FORMULA_ID,
801 Formula_Type_Code,
802 FORMULA_CODE,
803 TAX_REGIME_CODE,
804 TAX,
805 EFFECTIVE_FROM,
806 EFFECTIVE_TO,
807 Taxable_Basis_Type_Code,
808 Record_Type_Code,
809 BASE_RATE_MODIFIER,
810 Cash_Discount_Appl_Flag,
811 Volume_Discount_Appl_Flag,
812 Trading_Discount_Appl_Flag,
813 Transfer_Charge_Appl_Flag,
814 Transport_Charge_Appl_Flag,
815 Insurance_Charge_Appl_Flag,
816 Other_Charge_Appl_Flag,
817 ATTRIBUTE_CATEGORY,
818 ATTRIBUTE1,
819 ATTRIBUTE2,
820 ATTRIBUTE3,
821 ATTRIBUTE4,
822 ATTRIBUTE5,
823 ATTRIBUTE6,
824 ATTRIBUTE7,
825 ATTRIBUTE8,
826 ATTRIBUTE9,
827 ATTRIBUTE10,
828 ATTRIBUTE11,
829 ATTRIBUTE12,
830 ATTRIBUTE13,
831 ATTRIBUTE14,
832 ATTRIBUTE15,
833 ATTRIBUTE16,
834 ATTRIBUTE17,
835 ATTRIBUTE18,
836 ATTRIBUTE19,
837 ATTRIBUTE20,
838 Enabled_Flag,
839 CONTENT_OWNER_ID,
840 CREATED_BY ,
841 CREATION_DATE ,
842 LAST_UPDATED_BY ,
843 LAST_UPDATE_DATE ,
844 LAST_UPDATE_LOGIN ,
845 REQUEST_ID ,
846 PROGRAM_APPLICATION_ID ,
847 PROGRAM_ID ,
848 PROGRAM_LOGIN_ID)
849 values ( X_FORMULA_ID(i),
850 X_Formula_Type_Code(i),
851 X_FORMULA_CODE(i),
852 X_TAX_REGIME_CODE(i),
853 X_TAX(i),
857 X_Record_Type_Code(i),
854 X_EFFECTIVE_FROM(i),
855 X_EFFECTIVE_TO(i),
856 X_Taxable_Basis_Type_Code(i),
858 X_BASE_RATE_MODIFIER(i),
859 NVL(X_Cash_Discount_Appl_Flag(i),'N'),
860 NVL(X_Volume_Discount_Appl_Flag(i),'N'),
861 NVL(X_Trading_Discount_Appl_Flag(i),'N'),
862 NVL(X_Transfer_Charge_Appl_Flag(i),'N'),
863 NVL(X_TRANSPORT_CHARGE_APPL_FLG(i),'N'),
864 NVL(X_Insurance_Charge_Appl_Flag(i),'N'),
865 NVL(X_Other_Charge_Appl_Flag(i),'N'),
866 X_ATTRIBUTE_CATEGORY(i),
867 X_ATTRIBUTE1(i),
868 X_ATTRIBUTE2(i),
869 X_ATTRIBUTE3(i),
870 X_ATTRIBUTE4(i),
871 X_ATTRIBUTE5(i),
872 X_ATTRIBUTE6(i),
873 X_ATTRIBUTE7(i),
874 X_ATTRIBUTE8(i),
875 X_ATTRIBUTE9(i),
876 X_ATTRIBUTE10(i),
877 X_ATTRIBUTE11(i),
878 X_ATTRIBUTE12(i),
879 X_ATTRIBUTE13(i),
880 X_ATTRIBUTE14(i),
881 X_ATTRIBUTE15(i),
882 X_ATTRIBUTE16(i),
883 X_ATTRIBUTE17(i),
884 X_ATTRIBUTE18(i),
885 X_ATTRIBUTE19(i),
886 X_ATTRIBUTE20(i),
887 NVL(X_Enabled_Flag(i),'N'),
888 X_CONTENT_OWNER_ID(i),
889 fnd_global.user_id ,
890 sysdate ,
891 fnd_global.user_id ,
892 sysdate ,
893 fnd_global.conc_login_id ,
894 fnd_global.conc_request_id ,
895 fnd_global.prog_appl_id ,
896 fnd_global.conc_program_id ,
897 fnd_global.conc_login_id
898 );
899
900 forall i in x_formula_id.first..x_formula_id.last
901 insert into ZX_FORMULA_TL (FORMULA_ID,
902 FORMULA_NAME,
903 FORMULA_DESCRIPTION,
904 LANGUAGE,
905 SOURCE_LANG,
906 CREATED_BY ,
907 CREATION_DATE ,
908 LAST_UPDATED_BY ,
909 LAST_UPDATE_DATE ,
910 LAST_UPDATE_LOGIN)
911 select X_FORMULA_ID(i),
912 X_FORMULA_NAME(i),
913 X_FORMULA_DESCRIPTION(i),
914 L.LANGUAGE_CODE,
915 userenv('LANG'),
916 fnd_global.user_id ,
917 sysdate ,
918 fnd_global.user_id ,
919 sysdate ,
920 fnd_global.conc_login_id
921 from FND_LANGUAGES L
922 where L.INSTALLED_FLAG in ('I', 'B')
923 and not exists
924 (select NULL
925 from ZX_FORMULA_TL T
926 where T.FORMULA_ID = X_FORMULA_ID(i)
927 and T.LANGUAGE = L.LANGUAGE_CODE);
928 end if;
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 APP_EXCEPTION.RAISE_EXCEPTION;
933
934 end bulk_insert_formula;
935
936 end ZX_FORMULA_PKG;