[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_FORMULAS_PKG
Source
1 package body CN_CALC_FORMULAS_PKG as
2 /* $Header: cntformb.pls 120.4 2006/05/26 00:49:04 jxsingh ship $ */
3
4 procedure INSERT_ROW (
5 X_CALC_FORMULA_ID IN OUT NOCOPY CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
6 X_NAME IN CN_CALC_FORMULAS.NAME%TYPE,
7 X_DESCRIPTION IN CN_CALC_FORMULAS.DESCRIPTION%TYPE := NULL,
8 X_FORMULA_STATUS IN CN_CALC_FORMULAS.FORMULA_STATUS%TYPE := 'INCOMPLETE',
9 X_FORMULA_TYPE IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE := NULL,
10 X_TRX_GROUP_CODE IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE := NULL,
11 X_NUMBER_DIM IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE := NULL,
12 X_CUMULATIVE_FLAG IN CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE := NULL,
13 X_ITD_FLAG IN CN_CALC_FORMULAS.ITD_FLAG%TYPE := NULL,
14 X_SPLIT_FLAG IN CN_CALC_FORMULAS.SPLIT_FLAG%TYPE := NULL,
15 X_THRESHOLD_ALL_TIER_FLAG IN CN_CALC_FORMULAS.THRESHOLD_ALL_TIER_FLAG%TYPE := NULL,
16 X_MODELING_FLAG IN CN_CALC_FORMULAS.MODELING_FLAG%TYPE := NULL,
17 X_PERF_MEASURE_ID IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE := NULL,
18 X_OUTPUT_EXP_ID IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE := NULL,
19 X_F_OUTPUT_EXP_ID IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE := NULL,
20 --R12 MOAC Changes--Start
21 X_ORG_ID IN CN_CALC_FORMULAS.ORG_ID%TYPE,
22 --R12 MOAC Changes--End
23 X_ATTRIBUTE_CATEGORY IN CN_CALC_FORMULAS.ATTRIBUTE_CATEGORY%TYPE := NULL,
24 X_ATTRIBUTE1 IN CN_CALC_FORMULAS.ATTRIBUTE1%TYPE := NULL,
25 X_ATTRIBUTE2 IN CN_CALC_FORMULAS.ATTRIBUTE2%TYPE := NULL,
26 X_ATTRIBUTE3 IN CN_CALC_FORMULAS.ATTRIBUTE3%TYPE := NULL,
27 X_ATTRIBUTE4 IN CN_CALC_FORMULAS.ATTRIBUTE4%TYPE := NULL,
28 X_ATTRIBUTE5 IN CN_CALC_FORMULAS.ATTRIBUTE5%TYPE := NULL,
29 X_ATTRIBUTE6 IN CN_CALC_FORMULAS.ATTRIBUTE6%TYPE := NULL,
30 X_ATTRIBUTE7 IN CN_CALC_FORMULAS.ATTRIBUTE7%TYPE := NULL,
31 X_ATTRIBUTE8 IN CN_CALC_FORMULAS.ATTRIBUTE8%TYPE := NULL,
32 X_ATTRIBUTE9 IN CN_CALC_FORMULAS.ATTRIBUTE9%TYPE := NULL,
33 X_ATTRIBUTE10 IN CN_CALC_FORMULAS.ATTRIBUTE10%TYPE := NULL,
34 X_ATTRIBUTE11 IN CN_CALC_FORMULAS.ATTRIBUTE11%TYPE := NULL,
35 X_ATTRIBUTE12 IN CN_CALC_FORMULAS.ATTRIBUTE12%TYPE := NULL,
36 X_ATTRIBUTE13 IN CN_CALC_FORMULAS.ATTRIBUTE13%TYPE := NULL,
37 X_ATTRIBUTE14 IN CN_CALC_FORMULAS.ATTRIBUTE14%TYPE := NULL,
38 X_ATTRIBUTE15 IN CN_CALC_FORMULAS.ATTRIBUTE15%TYPE := NULL,
39 X_CREATION_DATE IN CN_CALC_FORMULAS.CREATION_DATE%TYPE := SYSDATE,
40 X_CREATED_BY IN CN_CALC_FORMULAS.CREATED_BY%TYPE := FND_GLOBAL.USER_ID,
41 X_LAST_UPDATE_DATE IN CN_CALC_FORMULAS.LAST_UPDATE_DATE%TYPE := SYSDATE,
42 X_LAST_UPDATED_BY IN CN_CALC_FORMULAS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
43 X_LAST_UPDATE_LOGIN IN CN_CALC_FORMULAS.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.LOGIN_ID) IS
44
45 cursor C is select calc_formula_id from CN_CALC_FORMULAS
46 where CALC_FORMULA_ID = x_calc_formula_id;
47
48 CURSOR id IS SELECT cn_calc_formulas_s.NEXTVAL FROM dual;
49 BEGIN
50 IF (x_calc_formula_id IS NULL) THEN
51 OPEN id;
52 FETCH id INTO x_calc_formula_id;
53 IF (id%notfound) THEN
54 CLOSE id;
55 RAISE no_data_found;
56 END IF;
57 CLOSE id;
58 END IF;
59
60 insert into CN_CALC_FORMULAS (
61 CALC_FORMULA_ID,
62 NAME,
63 DESCRIPTION,
64 FORMULA_STATUS,
65 FORMULA_TYPE,
66 TRX_GROUP_CODE,
67 NUMBER_DIM,
68 CUMULATIVE_FLAG,
69 ITD_FLAG,
70 SPLIT_FLAG,
71 THRESHOLD_ALL_TIER_FLAG,
72 MODELING_FLAG,
73 PERF_MEASURE_ID,
74 OUTPUT_EXP_ID,
75 F_OUTPUT_EXP_ID,
76 --R12 MOAC Changes--Start
77 ORG_ID,
78 --R12 MOAC Changes--End
79 ATTRIBUTE_CATEGORY,
80 ATTRIBUTE1,
81 ATTRIBUTE2,
82 ATTRIBUTE3,
83 ATTRIBUTE4,
84 ATTRIBUTE5,
85 ATTRIBUTE6,
86 ATTRIBUTE7,
87 ATTRIBUTE8,
88 ATTRIBUTE9,
89 ATTRIBUTE10,
90 ATTRIBUTE11,
91 ATTRIBUTE12,
92 ATTRIBUTE13,
93 ATTRIBUTE14,
94 ATTRIBUTE15,
95 CREATED_BY,
96 CREATION_DATE,
97 LAST_UPDATE_LOGIN,
98 LAST_UPDATE_DATE,
99 LAST_UPDATED_BY,
100 OBJECT_VERSION_NUMBER
101 ) VALUES (
102 X_CALC_FORMULA_ID,
103 X_NAME,
104 X_DESCRIPTION,
105 X_FORMULA_STATUS,
106 X_FORMULA_TYPE,
107 X_TRX_GROUP_CODE,
108 X_NUMBER_DIM,
109 X_CUMULATIVE_FLAG,
110 X_ITD_FLAG,
111 X_SPLIT_FLAG,
112 X_THRESHOLD_ALL_TIER_FLAG,
113 X_MODELING_FLAG,
114 X_PERF_MEASURE_ID,
115 X_OUTPUT_EXP_ID,
116 X_F_OUTPUT_EXP_ID,
117 --R12 MOAC Changes--Start
118 X_ORG_ID,
119 --R12 MOAC Changes--End
120 X_ATTRIBUTE_CATEGORY,
121 X_ATTRIBUTE1,
122 X_ATTRIBUTE2,
123 X_ATTRIBUTE3,
124 X_ATTRIBUTE4,
125 X_ATTRIBUTE5,
126 X_ATTRIBUTE6,
127 X_ATTRIBUTE7,
128 X_ATTRIBUTE8,
129 X_ATTRIBUTE9,
130 X_ATTRIBUTE10,
131 X_ATTRIBUTE11,
132 X_ATTRIBUTE12,
133 X_ATTRIBUTE13,
134 X_ATTRIBUTE14,
135 X_ATTRIBUTE15,
136 X_CREATED_BY,
137 X_CREATION_DATE,
138 X_LAST_UPDATE_LOGIN,
139 X_LAST_UPDATE_DATE,
140 X_LAST_UPDATED_BY,
141 1);
142
143 open c;
144 fetch c into x_calc_formula_id;
145 if (c%notfound) then
146 close c;
147 raise no_data_found;
148 end if;
149 close c;
150
151 end INSERT_ROW;
152
153 procedure LOCK_ROW (
154 X_CALC_FORMULA_ID IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
155 X_OBJECT_VERSION_NUMBER IN CN_CALC_FORMULAS.OBJECT_VERSION_NUMBER%TYPE) IS
156
157 cursor c is
158 select object_version_number
159 from cn_calc_formulas
160 where calc_formula_id = x_calc_formula_id
161 for update of calc_formula_id nowait;
162 tlinfo c%rowtype ;
163 BEGIN
164 open c;
165 fetch c into tlinfo;
166 if (c%notfound) then
167 close c;
168 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
169 fnd_msg_pub.add;
170 raise fnd_api.g_exc_unexpected_error;
171 end if;
172 close c;
173
174 if (tlinfo.object_version_number <> x_object_version_number) then
175 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
176 fnd_msg_pub.add;
177 raise fnd_api.g_exc_unexpected_error;
178 end if;
179
180 END LOCK_ROW;
181
182 procedure UPDATE_ROW (
183 X_CALC_FORMULA_ID IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
184 X_NAME IN CN_CALC_FORMULAS.NAME%TYPE,
185 X_DESCRIPTION IN CN_CALC_FORMULAS.DESCRIPTION%TYPE := CN_API.G_MISS_CHAR,
186 X_FORMULA_STATUS IN CN_CALC_FORMULAS.FORMULA_STATUS%TYPE := 'INCOMPLETE',
187 X_FORMULA_TYPE IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE := CN_API.G_MISS_CHAR,
188 X_TRX_GROUP_CODE IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE := CN_API.G_MISS_CHAR,
189 X_NUMBER_DIM IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE := CN_API.G_MISS_NUM,
190 X_CUMULATIVE_FLAG IN CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE := CN_API.G_MISS_CHAR,
191 X_ITD_FLAG IN CN_CALC_FORMULAS.ITD_FLAG%TYPE := CN_API.G_MISS_CHAR,
192 X_SPLIT_FLAG IN CN_CALC_FORMULAS.SPLIT_FLAG%TYPE := CN_API.G_MISS_CHAR,
193 X_THRESHOLD_ALL_TIER_FLAG IN CN_CALC_FORMULAS.THRESHOLD_ALL_TIER_FLAG%TYPE := CN_API.G_MISS_CHAR,
194 X_MODELING_FLAG IN CN_CALC_FORMULAS.MODELING_FLAG%TYPE := CN_API.G_MISS_CHAR,
195 X_PERF_MEASURE_ID IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE := CN_API.G_MISS_NUM,
196 X_OUTPUT_EXP_ID IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE := CN_API.G_MISS_NUM,
197 X_F_OUTPUT_EXP_ID IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE := CN_API.G_MISS_NUM,
198 X_ATTRIBUTE_CATEGORY IN CN_CALC_FORMULAS.ATTRIBUTE_CATEGORY%TYPE := CN_API.G_MISS_CHAR,
199 X_ATTRIBUTE1 IN CN_CALC_FORMULAS.ATTRIBUTE1%TYPE := CN_API.G_MISS_CHAR,
200 X_ATTRIBUTE2 IN CN_CALC_FORMULAS.ATTRIBUTE2%TYPE := CN_API.G_MISS_CHAR,
201 X_ATTRIBUTE3 IN CN_CALC_FORMULAS.ATTRIBUTE3%TYPE := CN_API.G_MISS_CHAR,
202 X_ATTRIBUTE4 IN CN_CALC_FORMULAS.ATTRIBUTE4%TYPE := CN_API.G_MISS_CHAR,
203 X_ATTRIBUTE5 IN CN_CALC_FORMULAS.ATTRIBUTE5%TYPE := CN_API.G_MISS_CHAR,
204 X_ATTRIBUTE6 IN CN_CALC_FORMULAS.ATTRIBUTE6%TYPE := CN_API.G_MISS_CHAR,
205 X_ATTRIBUTE7 IN CN_CALC_FORMULAS.ATTRIBUTE7%TYPE := CN_API.G_MISS_CHAR,
206 X_ATTRIBUTE8 IN CN_CALC_FORMULAS.ATTRIBUTE8%TYPE := CN_API.G_MISS_CHAR,
207 X_ATTRIBUTE9 IN CN_CALC_FORMULAS.ATTRIBUTE9%TYPE := CN_API.G_MISS_CHAR,
208 X_ATTRIBUTE10 IN CN_CALC_FORMULAS.ATTRIBUTE10%TYPE := CN_API.G_MISS_CHAR,
209 X_ATTRIBUTE11 IN CN_CALC_FORMULAS.ATTRIBUTE11%TYPE := CN_API.G_MISS_CHAR,
210 X_ATTRIBUTE12 IN CN_CALC_FORMULAS.ATTRIBUTE12%TYPE := CN_API.G_MISS_CHAR,
211 X_ATTRIBUTE13 IN CN_CALC_FORMULAS.ATTRIBUTE13%TYPE := CN_API.G_MISS_CHAR,
212 X_ATTRIBUTE14 IN CN_CALC_FORMULAS.ATTRIBUTE14%TYPE := CN_API.G_MISS_CHAR,
213 X_ATTRIBUTE15 IN CN_CALC_FORMULAS.ATTRIBUTE15%TYPE := CN_API.G_MISS_CHAR,
214 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY CN_CALC_FORMULAS.OBJECT_VERSION_NUMBER%TYPE,
215 X_LAST_UPDATE_DATE IN CN_CALC_FORMULAS.LAST_UPDATE_DATE%TYPE := SYSDATE,
216 X_LAST_UPDATED_BY IN CN_CALC_FORMULAS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
217 X_LAST_UPDATE_LOGIN IN CN_CALC_FORMULAS.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.LOGIN_ID) IS
218 BEGIN
219
220 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
221
222 update CN_CALC_FORMULAS set
223 NAME = Decode(X_NAME, fnd_api.g_miss_char, name, x_name),
224 DESCRIPTION = Decode(X_DESCRIPTION, fnd_api.g_miss_char, description, x_description),
225 FORMULA_STATUS = Decode(X_FORMULA_STATUS, fnd_api.g_miss_char, formula_status, x_formula_status),
226 FORMULA_TYPE = Decode(X_FORMULA_TYPE, fnd_api.g_miss_char, formula_type, x_formula_type),
227 TRX_GROUP_CODE = Decode(X_TRX_GROUP_CODE, fnd_api.g_miss_char, trx_group_code, x_trx_group_code),
228 NUMBER_DIM = Decode(X_NUMBER_DIM, fnd_api.g_miss_char, number_dim, x_number_dim),
229 CUMULATIVE_FLAG = Decode(X_CUMULATIVE_FLAG, fnd_api.g_miss_char, cumulative_flag, x_cumulative_flag),
230 ITD_FLAG = Decode(X_ITD_FLAG, fnd_api.g_miss_char, itd_flag, x_itd_flag),
231 SPLIT_FLAG = Decode(X_SPLIT_FLAG, fnd_api.g_miss_char, split_flag, x_split_flag),
232 THRESHOLD_ALL_TIER_FLAG = Decode(X_THRESHOLD_ALL_TIER_FLAG, fnd_api.g_miss_char, threshold_all_tier_flag, x_threshold_all_tier_flag),
233 MODELING_FLAG = Decode(X_MODELING_FLAG, fnd_api.g_miss_char, modeling_flag, x_modeling_flag),
234 PERF_MEASURE_ID = Decode(X_PERF_MEASURE_ID, fnd_api.g_miss_char, perf_measure_id, x_perf_measure_id),
235 OUTPUT_EXP_ID = Decode(X_OUTPUT_EXP_ID, fnd_api.g_miss_char, output_exp_id, x_output_exp_id),
236 F_OUTPUT_EXP_ID = Decode(X_F_OUTPUT_EXP_ID, fnd_api.g_miss_char, f_output_exp_id, x_f_output_exp_id),
237 ATTRIBUTE_CATEGORY = Decode(X_ATTRIBUTE_CATEGORY, fnd_api.g_miss_char, attribute_category, x_attribute_category),
238 ATTRIBUTE1 = Decode(X_ATTRIBUTE1, fnd_api.g_miss_char, attribute1, x_attribute1),
239 ATTRIBUTE2 = Decode(X_ATTRIBUTE2, fnd_api.g_miss_char, attribute2, x_attribute2),
240 ATTRIBUTE3 = Decode(X_ATTRIBUTE3, fnd_api.g_miss_char, attribute3, x_attribute3),
241 ATTRIBUTE4 = Decode(X_ATTRIBUTE4, fnd_api.g_miss_char, attribute4, x_attribute4),
242 ATTRIBUTE5 = Decode(X_ATTRIBUTE5, fnd_api.g_miss_char, attribute5, x_attribute5),
243 ATTRIBUTE6 = Decode(X_ATTRIBUTE6, fnd_api.g_miss_char, attribute6, x_attribute6),
244 ATTRIBUTE7 = Decode(X_ATTRIBUTE7, fnd_api.g_miss_char, attribute7, x_attribute7),
245 ATTRIBUTE8 = Decode(X_ATTRIBUTE8, fnd_api.g_miss_char, attribute8, x_attribute8),
246 ATTRIBUTE9 = Decode(X_ATTRIBUTE9, fnd_api.g_miss_char, attribute9, x_attribute9),
247 ATTRIBUTE10 = Decode(X_ATTRIBUTE10, fnd_api.g_miss_char, attribute10, x_attribute10),
248 ATTRIBUTE11 = Decode(X_ATTRIBUTE11, fnd_api.g_miss_char, attribute11, x_attribute11),
249 ATTRIBUTE12 = Decode(X_ATTRIBUTE12, fnd_api.g_miss_char, attribute12, x_attribute12),
250 ATTRIBUTE13 = Decode(X_ATTRIBUTE13, fnd_api.g_miss_char, attribute13, x_attribute13),
251 ATTRIBUTE14 = Decode(X_ATTRIBUTE14, fnd_api.g_miss_char, attribute14, x_attribute14),
252 ATTRIBUTE15 = Decode(X_ATTRIBUTE15, fnd_api.g_miss_char, attribute15, x_attribute15),
253 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
254 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
255 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
256 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
257 where CALC_FORMULA_ID = x_calc_formula_id;
258
259 if (sql%notfound) then
260 raise no_data_found;
261 end if;
262 end UPDATE_ROW;
263
264 procedure DELETE_ROW (
265 X_CALC_FORMULA_ID IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
266 X_ORG_ID IN CN_CALC_FORMULAS.ORG_ID%TYPE) IS
267 begin
268 delete from CN_CALC_FORMULAS
269 where CALC_FORMULA_ID = X_CALC_FORMULA_ID
270 and ORG_ID = X_ORG_ID;
271
272 if (sql%notfound) then
273 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
274 fnd_msg_pub.add;
275 raise fnd_api.g_exc_error;
276 end if;
277
278 end DELETE_ROW;
279
280 end CN_CALC_FORMULAS_PKG;