[Home] [Help]
PACKAGE BODY: APPS.CN_FORMULA_INPUTS_PKG
Source
1 package body CN_FORMULA_INPUTS_PKG as
2 /* $Header: cntfinpb.pls 120.1 2005/06/22 16:38:51 appldev ship $ */
3
4 procedure INSERT_ROW
5 (X_FORMULA_INPUT_ID IN OUT NOCOPY CN_FORMULA_INPUTS.FORMULA_INPUT_ID%TYPE,
6 X_CALC_FORMULA_ID IN CN_FORMULA_INPUTS.CALC_FORMULA_ID%TYPE,
7 X_CALC_SQL_EXP_ID IN CN_FORMULA_INPUTS.CALC_SQL_EXP_ID%TYPE,
8 X_F_CALC_SQL_EXP_ID IN CN_FORMULA_INPUTS.F_CALC_SQL_EXP_ID%TYPE := NULL,
9 X_RATE_DIM_SEQUENCE IN CN_FORMULA_INPUTS.RATE_DIM_SEQUENCE%TYPE,
10 X_CUMULATIVE_FLAG IN CN_FORMULA_INPUTS.CUMULATIVE_FLAG%TYPE,
11 X_SPLIT_FLAG IN CN_FORMULA_INPUTS.SPLIT_FLAG%TYPE,
12 --R12 MOAC Changes--Start
13 X_ORG_ID IN CN_FORMULA_INPUTS.ORG_ID%TYPE,
14 --R12 MOAC Changes--End
15 X_ATTRIBUTE_CATEGORY IN CN_FORMULA_INPUTS.ATTRIBUTE_CATEGORY%TYPE := NULL,
16 X_ATTRIBUTE1 IN CN_FORMULA_INPUTS.ATTRIBUTE1%TYPE := NULL,
17 X_ATTRIBUTE2 IN CN_FORMULA_INPUTS.ATTRIBUTE2%TYPE := NULL,
18 X_ATTRIBUTE3 IN CN_FORMULA_INPUTS.ATTRIBUTE3%TYPE := NULL,
19 X_ATTRIBUTE4 IN CN_FORMULA_INPUTS.ATTRIBUTE4%TYPE := NULL,
20 X_ATTRIBUTE5 IN CN_FORMULA_INPUTS.ATTRIBUTE5%TYPE := NULL,
21 X_ATTRIBUTE6 IN CN_FORMULA_INPUTS.ATTRIBUTE6%TYPE := NULL,
22 X_ATTRIBUTE7 IN CN_FORMULA_INPUTS.ATTRIBUTE7%TYPE := NULL,
23 X_ATTRIBUTE8 IN CN_FORMULA_INPUTS.ATTRIBUTE8%TYPE := NULL,
24 X_ATTRIBUTE9 IN CN_FORMULA_INPUTS.ATTRIBUTE9%TYPE := NULL,
25 X_ATTRIBUTE10 IN CN_FORMULA_INPUTS.ATTRIBUTE10%TYPE := NULL,
26 X_ATTRIBUTE11 IN CN_FORMULA_INPUTS.ATTRIBUTE11%TYPE := NULL,
27 X_ATTRIBUTE12 IN CN_FORMULA_INPUTS.ATTRIBUTE12%TYPE := NULL,
28 X_ATTRIBUTE13 IN CN_FORMULA_INPUTS.ATTRIBUTE13%TYPE := NULL,
29 X_ATTRIBUTE14 IN CN_FORMULA_INPUTS.ATTRIBUTE14%TYPE := NULL,
30 X_ATTRIBUTE15 IN CN_FORMULA_INPUTS.ATTRIBUTE15%TYPE := NULL,
31 X_CREATION_DATE IN CN_FORMULA_INPUTS.CREATION_DATE%TYPE := SYSDATE,
32 X_CREATED_BY IN CN_FORMULA_INPUTS.CREATED_BY%TYPE := FND_GLOBAL.USER_ID,
33 X_LAST_UPDATE_DATE IN CN_FORMULA_INPUTS.LAST_UPDATE_DATE%TYPE := SYSDATE,
34 X_LAST_UPDATED_BY IN CN_FORMULA_INPUTS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
35 X_LAST_UPDATE_LOGIN IN CN_FORMULA_INPUTS.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.LOGIN_ID) IS
36
37 cursor C is select formula_input_id from CN_FORMULA_INPUTS
38 where FORMULA_INPUT_ID = x_formula_input_id;
39
40 CURSOR id IS SELECT cn_formula_inputs_s.NEXTVAL FROM dual;
41 BEGIN
42 IF (x_formula_input_id IS NULL) THEN
43 OPEN id;
44 FETCH id INTO x_formula_input_id;
45 IF (id%notfound) THEN
46 CLOSE id;
47 RAISE no_data_found;
48 END IF;
49 CLOSE id;
50 END IF;
51 insert into CN_FORMULA_INPUTS
52 (FORMULA_INPUT_ID,
53 CALC_FORMULA_ID,
54 CALC_SQL_EXP_ID,
55 F_CALC_SQL_EXP_ID,
56 RATE_DIM_SEQUENCE,
57 CUMULATIVE_FLAG,
58 SPLIT_FLAG,
59 --R12 MOAC Changes--Start
60 ORG_ID,
61 --R12 MOAC Changes--End
62 ATTRIBUTE_CATEGORY,
63 ATTRIBUTE1,
64 ATTRIBUTE2,
65 ATTRIBUTE3,
66 ATTRIBUTE4,
67 ATTRIBUTE5,
68 ATTRIBUTE6,
69 ATTRIBUTE7,
70 ATTRIBUTE8,
71 ATTRIBUTE9,
72 ATTRIBUTE10,
73 ATTRIBUTE11,
74 ATTRIBUTE12,
75 ATTRIBUTE13,
76 ATTRIBUTE14,
77 ATTRIBUTE15,
78 CREATED_BY,
79 CREATION_DATE,
80 LAST_UPDATE_LOGIN,
81 LAST_UPDATE_DATE,
82 LAST_UPDATED_BY,
83 OBJECT_VERSION_NUMBER
84 ) VALUES (
85 X_FORMULA_INPUT_ID,
86 X_CALC_FORMULA_ID,
87 X_CALC_SQL_EXP_ID,
88 X_F_CALC_SQL_EXP_ID,
89 X_RATE_DIM_SEQUENCE,
90 X_CUMULATIVE_FLAG,
91 X_SPLIT_FLAG,
92 --R12 MOAC Changes--Start
93 X_ORG_ID,
94 --R12 MOAC Changes--End
95 X_ATTRIBUTE_CATEGORY,
96 X_ATTRIBUTE1,
97 X_ATTRIBUTE2,
98 X_ATTRIBUTE3,
99 X_ATTRIBUTE4,
100 X_ATTRIBUTE5,
101 X_ATTRIBUTE6,
102 X_ATTRIBUTE7,
103 X_ATTRIBUTE8,
104 X_ATTRIBUTE9,
105 X_ATTRIBUTE10,
106 X_ATTRIBUTE11,
107 X_ATTRIBUTE12,
108 X_ATTRIBUTE13,
109 X_ATTRIBUTE14,
110 X_ATTRIBUTE15,
111 X_CREATED_BY,
112 X_CREATION_DATE,
113 X_LAST_UPDATE_LOGIN,
114 X_LAST_UPDATE_DATE,
115 X_LAST_UPDATED_BY,
116 0);
117
118 open c;
119 fetch c into x_formula_input_id;
120 if (c%notfound) then
121 close c;
122 raise no_data_found;
123 end if;
124 close c;
125
126 end INSERT_ROW;
127
128 procedure LOCK_ROW
129 (X_FORMULA_INPUT_ID IN CN_FORMULA_INPUTS.FORMULA_INPUT_ID%TYPE,
130 X_OBJECT_VERSION_NUMBER IN CN_FORMULA_INPUTS.OBJECT_VERSION_NUMBER%TYPE) IS
131
132 cursor c is
133 select object_version_number
134 from cn_formula_inputs
135 where formula_input_id = x_formula_input_id
136 for update of formula_input_id nowait;
137 tlinfo c%rowtype ;
138 BEGIN
139 open c;
140 fetch c into tlinfo;
141 if (c%notfound) then
142 close c;
143 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
144 fnd_msg_pub.add;
145 raise fnd_api.g_exc_unexpected_error;
146 end if;
147 close c;
148
149 if (tlinfo.object_version_number <> x_object_version_number) then
150 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
151 fnd_msg_pub.add;
152 raise fnd_api.g_exc_unexpected_error;
153 end if;
154 end LOCK_ROW;
155
156 procedure UPDATE_ROW
157 (X_FORMULA_INPUT_ID IN CN_FORMULA_INPUTS.FORMULA_INPUT_ID%TYPE,
158 X_CALC_FORMULA_ID IN CN_FORMULA_INPUTS.CALC_FORMULA_ID%TYPE,
159 X_CALC_SQL_EXP_ID IN CN_FORMULA_INPUTS.CALC_SQL_EXP_ID%TYPE,
160 X_F_CALC_SQL_EXP_ID IN CN_FORMULA_INPUTS.F_CALC_SQL_EXP_ID%TYPE := CN_API.G_MISS_NUM,
161 X_RATE_DIM_SEQUENCE IN CN_FORMULA_INPUTS.RATE_DIM_SEQUENCE%TYPE,
162 X_CUMULATIVE_FLAG IN CN_FORMULA_INPUTS.CUMULATIVE_FLAG%TYPE,
163 X_SPLIT_FLAG IN CN_FORMULA_INPUTS.SPLIT_FLAG%TYPE,
164 X_ATTRIBUTE_CATEGORY IN CN_FORMULA_INPUTS.ATTRIBUTE_CATEGORY%TYPE := CN_API.G_MISS_CHAR,
165 X_ATTRIBUTE1 IN CN_FORMULA_INPUTS.ATTRIBUTE1%TYPE := CN_API.G_MISS_CHAR,
166 X_ATTRIBUTE2 IN CN_FORMULA_INPUTS.ATTRIBUTE2%TYPE := CN_API.G_MISS_CHAR,
167 X_ATTRIBUTE3 IN CN_FORMULA_INPUTS.ATTRIBUTE3%TYPE := CN_API.G_MISS_CHAR,
168 X_ATTRIBUTE4 IN CN_FORMULA_INPUTS.ATTRIBUTE4%TYPE := CN_API.G_MISS_CHAR,
169 X_ATTRIBUTE5 IN CN_FORMULA_INPUTS.ATTRIBUTE5%TYPE := CN_API.G_MISS_CHAR,
170 X_ATTRIBUTE6 IN CN_FORMULA_INPUTS.ATTRIBUTE6%TYPE := CN_API.G_MISS_CHAR,
171 X_ATTRIBUTE7 IN CN_FORMULA_INPUTS.ATTRIBUTE7%TYPE := CN_API.G_MISS_CHAR,
172 X_ATTRIBUTE8 IN CN_FORMULA_INPUTS.ATTRIBUTE8%TYPE := CN_API.G_MISS_CHAR,
173 X_ATTRIBUTE9 IN CN_FORMULA_INPUTS.ATTRIBUTE9%TYPE := CN_API.G_MISS_CHAR,
174 X_ATTRIBUTE10 IN CN_FORMULA_INPUTS.ATTRIBUTE10%TYPE := CN_API.G_MISS_CHAR,
175 X_ATTRIBUTE11 IN CN_FORMULA_INPUTS.ATTRIBUTE11%TYPE := CN_API.G_MISS_CHAR,
176 X_ATTRIBUTE12 IN CN_FORMULA_INPUTS.ATTRIBUTE12%TYPE := CN_API.G_MISS_CHAR,
177 X_ATTRIBUTE13 IN CN_FORMULA_INPUTS.ATTRIBUTE13%TYPE := CN_API.G_MISS_CHAR,
178 X_ATTRIBUTE14 IN CN_FORMULA_INPUTS.ATTRIBUTE14%TYPE := CN_API.G_MISS_CHAR,
179 X_ATTRIBUTE15 IN CN_FORMULA_INPUTS.ATTRIBUTE15%TYPE := CN_API.G_MISS_CHAR,
180 X_OBJECT_VERSION_NUMBER IN CN_FORMULA_INPUTS.OBJECT_VERSION_NUMBER%TYPE,
181 X_LAST_UPDATE_DATE IN CN_FORMULA_INPUTS.LAST_UPDATE_DATE%TYPE := SYSDATE,
182 X_LAST_UPDATED_BY IN CN_FORMULA_INPUTS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
183 X_LAST_UPDATE_LOGIN IN CN_FORMULA_INPUTS.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.LOGIN_ID) IS
184
185 BEGIN
186 update CN_FORMULA_INPUTS set
187 CALC_SQL_EXP_ID = Decode(X_CALC_SQL_EXP_ID, fnd_api.g_miss_num, calc_sql_exp_id, x_calc_sql_exp_id),
188 F_CALC_SQL_EXP_ID = Decode(X_F_CALC_SQL_EXP_ID, fnd_api.g_miss_num, f_calc_sql_exp_id, x_f_calc_sql_exp_id),
189 RATE_DIM_SEQUENCE = Decode(X_RATE_DIM_SEQUENCE, fnd_api.g_miss_num, rate_dim_sequence, x_rate_dim_sequence),
190 CUMULATIVE_FLAG = Decode(X_CUMULATIVE_FLAG, fnd_api.g_miss_char, CUMULATIVE_FLAG, X_CUMULATIVE_FLAG),
191 SPLIT_FLAG = Decode(X_SPLIT_FLAG, fnd_api.g_miss_char, SPLIT_FLAG, X_SPLIT_FLAG),
192 ATTRIBUTE_CATEGORY = Decode(X_ATTRIBUTE_CATEGORY, fnd_api.g_miss_char, attribute_category, x_attribute_category),
193 ATTRIBUTE1 = Decode(X_ATTRIBUTE1, fnd_api.g_miss_char, attribute1, x_attribute1),
194 ATTRIBUTE2 = Decode(X_ATTRIBUTE2, fnd_api.g_miss_char, attribute2, x_attribute2),
195 ATTRIBUTE3 = Decode(X_ATTRIBUTE3, fnd_api.g_miss_char, attribute3, x_attribute3),
196 ATTRIBUTE4 = Decode(X_ATTRIBUTE4, fnd_api.g_miss_char, attribute4, x_attribute4),
197 ATTRIBUTE5 = Decode(X_ATTRIBUTE5, fnd_api.g_miss_char, attribute5, x_attribute5),
198 ATTRIBUTE6 = Decode(X_ATTRIBUTE6, fnd_api.g_miss_char, attribute6, x_attribute6),
199 ATTRIBUTE7 = Decode(X_ATTRIBUTE7, fnd_api.g_miss_char, attribute7, x_attribute7),
200 ATTRIBUTE8 = Decode(X_ATTRIBUTE8, fnd_api.g_miss_char, attribute8, x_attribute8),
201 ATTRIBUTE9 = Decode(X_ATTRIBUTE9, fnd_api.g_miss_char, attribute9, x_attribute9),
202 ATTRIBUTE10 = Decode(X_ATTRIBUTE10, fnd_api.g_miss_char, attribute10, x_attribute10),
203 ATTRIBUTE11 = Decode(X_ATTRIBUTE11, fnd_api.g_miss_char, attribute11, x_attribute11),
204 ATTRIBUTE12 = Decode(X_ATTRIBUTE12, fnd_api.g_miss_char, attribute12, x_attribute12),
205 ATTRIBUTE13 = Decode(X_ATTRIBUTE13, fnd_api.g_miss_char, attribute13, x_attribute13),
206 ATTRIBUTE14 = Decode(X_ATTRIBUTE14, fnd_api.g_miss_char, attribute14, x_attribute14),
207 ATTRIBUTE15 = Decode(X_ATTRIBUTE15, fnd_api.g_miss_char, attribute15, x_attribute15),
208 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
209 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
211 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1
212 where FORMULA_INPUT_ID = x_formula_input_id;
213
214 if (sql%notfound) then
215 raise no_data_found;
216 end if;
217 end UPDATE_ROW;
218
219 procedure DELETE_ROW
220 (X_FORMULA_INPUT_ID IN CN_FORMULA_INPUTS.FORMULA_INPUT_ID%TYPE) IS
221 begin
222 delete from CN_FORMULA_INPUTS
223 where FORMULA_INPUT_ID = X_FORMULA_INPUT_ID;
224
225 if (sql%notfound) then
226 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
227 fnd_msg_pub.add;
228 raise fnd_api.g_exc_error;
229 end if;
230
231 end DELETE_ROW;
232
233 end CN_FORMULA_INPUTS_PKG;